create or replace
PACKAGE XXFTC_OM_ORDER_HOLDS AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_OM_ORDER_HOLDS.pks ***/
/*** AUTHOR : Prasad Potluri ***/
/*** VERSION : 1.0 ***/
/*** PURPOSE : procedure to apply hold for order ***/
/*** ***/
/*** PARAMETERS : order header id ***/
/*** DATE : 03/June/2009 ***/
/**************************************************************************************************/
PROCEDURE XXFTC_ORDER_APPLY_HOLD (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
);
PROCEDURE XXFTC_OM_APPLY_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
PROCEDURE XXFTC_OM_RELEASE_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
END XXFTC_OM_ORDER_HOLDS;
/
create or replace
PACKAGE BODY xxftc_om_order_holds
AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_OM_ORDER_HOLDS.pkb ***/
/*** AUTHOR : Prasad Potluri ***/
/*** VERSION : 1.0 ***/
/*** PURPOSE : procedure to apply hold for an order ***/
/*** ***/
/*** PARAMETERS : order header id ***/
/*** DATE : 03/June/2009 ***/
/**************************************************************************************************/
--+----------------------------------------------------------+
--+ Function Used to get the weight for the open order line +
--+----------------------------------------------------------+
FUNCTION open_orders_weight (p_line_id IN NUMBER)
RETURN NUMBER
IS
v_open_orders_weight NUMBER;
BEGIN
SELECT NVL (SUM (mtr.reservation_quantity * NVL (mtl.unit_weight, 0)),
0)
INTO v_open_orders_weight
FROM oe_order_lines oel, mtl_system_items_b mtl, mtl_reservations mtr
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = p_line_id;
RETURN v_open_orders_weight;
END open_orders_weight;
--+----------------------------------------------------------+
--+ Main Procedure used to apply the hold for an order +
--+----------------------------------------------------------+
PROCEDURE xxftc_order_apply_hold (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
)
IS
l_truckload_limit VARCHAR2 (100);
l_freight VARCHAR2 (100);
l_ship_method VARCHAR2 (100);
l_cust_id NUMBER;
l_order_header_id NUMBER;
l_ship_to_id NUMBER;
l_warehouse NUMBER;
l_total_weight NUMBER;
l_line_weight NUMBER;
l_oe_hold_id NUMBER;
l_hold_id NUMBER;
l_msg_count NUMBER;
l_freight_header_id NUMBER;
l_org NUMBER := fnd_profile.VALUE ('org_id');
l_msg_data VARCHAR2 (2000);
l_return_status VARCHAR2 (50);
l_debug_point VARCHAR2 (100);
l_error_message VARCHAR2 (2000);
l_program_name VARCHAR2 (256);
cp_application_id NUMBER;
l_application_id NUMBER;
l_alert_id NUMBER;
l_type VARCHAR2 (3);
l_err_msg VARCHAR2 (200);
p_request_id NUMBER;
l_party_id NUMBER;
l_release_hold_flag VARCHAR2 (1);
l_party_name VARCHAR2 (200);
l_customer_number VARCHAR2 (30);
l_customer_location VARCHAR2 (200);
l_warehouse_code VARCHAR2 (50);
l_order_weight NUMBER;
l_order_weight_1 NUMBER;
l_request_id NUMBER;
l_phase1 VARCHAR2 (200);
l_status1 VARCHAR2 (200);
l_dev_phase1 VARCHAR2 (200);
l_dev_status1 VARCHAR2 (200);
l_result1 BOOLEAN;
l_error_mesg1 VARCHAR2 (2000);
l_request_id1 NUMBER;
l_phase2 VARCHAR2 (200);
l_status2 VARCHAR2 (200);
l_dev_phase2 VARCHAR2 (200);
l_dev_status2 VARCHAR2 (200);
l_result2 BOOLEAN;
l_error_mesg2 VARCHAR2 (2000);
l_current_order NUMBER;
l_ship_hold_flag VARCHAR2 (1);
l_book_user NUMBER;
l_user_email VARCHAR2 (500);
l_send_email VARCHAR2 (1000);
l_order_tbl oe_holds_pvt.order_tbl_type;
l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
l_request_date VARCHAR2 (30);
l_ship_thresold VARCHAR2 (200);
l_update_user_name VARCHAR2 (100);
l_instance_name VARCHAR2 (100);
l_report_type VARCHAR2 (50);
l_ord_user_id NUMBER;
l_date DATE;
l_list_price NUMBER;
l_selling_price NUMBER;
l_diff NUMBER;
l_percentage NUMBER;
l_request1 BOOLEAN;
v_template VARCHAR2 (30);
l_crd_oe_hold_id NUMBER;
l_crd_oe_hold_sts VARCHAR2 (100);
v_date VARCHAR2(20):=TO_CHAR(SYSDATE,'MMDDYY');
l_hold_status VARCHAR2(200);
l_ORDERED_QUANTITY NUMBER;
l_reservation_quantity NUMBER;
l_shipping_instructions VARCHAR2(1000);
l_result BOOLEAN;
l_phase VARCHAR2(200);
l_status VARCHAR2(200);
l_dev_phase VARCHAR2(200);
l_dev_status VARCHAR2(200);
l_error_mesg VARCHAR2(2000);
l_item_number VARCHAR2(100);
l_line_number VARCHAR2(50);
l_cust_item_number VARCHAR2(200);
CURSOR c_ware_house (p_header_id IN NUMBER)
IS
SELECT DISTINCT sold_to_org_id, ship_to_org_id, ship_from_org_id
FROM oe_order_lines_all
WHERE header_id = p_header_id;
--+-----------------------------------------------------------------------------------------------------------------------+
--+ Cursor used to get the eligible open order lines against current order customer,ship to location and warehouse +
--+-----------------------------------------------------------------------------------------------------------------------+
CURSOR c_eligible_lines (
p_sold_to_org_id IN NUMBER,
p_ship_to_org_id IN NUMBER,
p_ship_from_org_id IN NUMBER
)
IS
SELECT DISTINCT oel.line_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oel.sold_to_org_id = p_sold_to_org_id
AND oel.ship_to_org_id = p_ship_to_org_id
AND oel.ship_from_org_id = p_ship_from_org_id
AND oeh.header_id = oel.header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
--+----------------------------------------------------------------------------------------------------------------------------+
--+ Cursor used to get the eligible open orders to apply holds against current order customer,ship to location and warehouse +
--+----------------------------------------------------------------------------------------------------------------------------+
CURSOR c_hold_order (
p_sold_to_org_id IN NUMBER,
p_ship_to_org_id IN NUMBER,
p_ship_from_org_id IN NUMBER
)
IS
SELECT DISTINCT oeh.header_id, oeh.order_number, oel.line_id,
oel.line_number
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oel.sold_to_org_id = p_sold_to_org_id
AND oel.ship_to_org_id = p_ship_to_org_id
AND oel.ship_from_org_id = p_ship_from_org_id
AND oeh.header_id = oel.header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
CURSOR c_curr_ord (p_header_id IN NUMBER)
IS
SELECT DISTINCT oel.line_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oeh.header_id = oel.header_id
AND oeh.header_id = p_header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
CURSOR c_cur_ord_ln(p_header_id IN NUMBER) IS
SELECT oel.header_id,oel.line_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oeh.header_id = oel.header_id
AND oeh.header_id = p_header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
BEGIN
--+---------------------------+
--+ Set the org id +
--+---------------------------+
mo_global.set_policy_context ('S', l_org);
l_debug_point := '010';
l_error_message := '';
l_truckload_limit := '';
l_freight_header_id := NULL;
l_order_header_id := TO_NUMBER (itemkey);
--+------------------------------------------+
--+ Calling Parcel Backorder Hold +
--+------------------------------------------+
xxftc_om_kewill_hold_pkg.xxftc_order_apply_hld (l_order_header_id);
FOR r_ware_house IN c_ware_house (l_order_header_id)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the party id +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hp.party_id
INTO l_party_id
FROM hz_parties hp, hz_cust_accounts hc
WHERE hc.cust_account_id = r_ware_house.sold_to_org_id
AND hc.party_id = hp.party_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_party_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT attribute6
INTO l_ship_thresold
FROM hz_cust_site_uses_all
WHERE site_use_id = r_ware_house.ship_to_org_id
AND site_use_code = 'SHIP_TO';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ship_thresold := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_ship_thresold IS NOT NULL
THEN
l_truckload_limit := l_ship_thresold;
ELSE
BEGIN
SELECT attribute2
INTO l_truckload_limit
FROM hz_parties
WHERE party_id = l_party_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_truckload_limit := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; -- IF l_ship_thresold IS NOT NULL THEN
--+---------------------------------------------------------------------+
--+ Check the truckload weight limit IS NULL OR NOT NULL +
--+ IF truckload weight limit IS NOT NULL then only proceed to further. +
--+---------------------------------------------------------------------+
IF l_truckload_limit IS NOT NULL
THEN
l_debug_point := '020';
--+---------------------------------------------------------------------+
--+ Get the order header id to check freight/ship method combination +
--+---------------------------------------------------------------------+
BEGIN
SELECT oeh.header_id
INTO l_freight_header_id
FROM oe_order_headers oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oeh.header_id = l_order_header_id
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y')
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_freight_header_id := NULL;
l_error_message :=
'l_freight_header_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
l_debug_point := '030';
--+-----------------------------------------------------------------------------------------------------------+
--+Check the freight term - ship method combination +
--+IF freight term - ship method combination +
--+doesn't exist in('Parcel-Prepay Add','Parcel-Third Party Billing','Parcel-Prepaid','Will Call-Prepaid' +
--+then proceed to further. +
--+-----------------------------------------------------------------------------------------------------------+
IF l_freight_header_id IS NOT NULL
THEN
l_debug_point := '040';
--+-----------------------------------------------------------------------------+
--+ Get the Current cancel order number +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT order_number, last_updated_by
INTO l_current_order, l_book_user
FROM oe_order_headers
WHERE header_id = l_order_header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_current_order := NULL;
l_book_user := NULL;
l_error_message :=
'l_cust_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------------------------------+
--+ Get the email address of booking order +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT email_address
INTO l_user_email
FROM fnd_user
WHERE user_id = l_book_user;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_user_email := '';
l_error_message :=
'l_cust_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_user_email IS NOT NULL
THEN
l_send_email :=
l_user_email || ',' || 'mmanier@falkentire.com';
ELSE
l_send_email := 'mmanier@falkentire.com';
END IF;
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the eligible order lines +
--+-----------------------------------------------------------------------------+
l_total_weight := 0;
FOR r_eligible_lines IN
c_eligible_lines (r_ware_house.sold_to_org_id,
r_ware_house.ship_to_org_id,
r_ware_house.ship_from_org_id
)
LOOP
l_debug_point := '050';
l_line_weight :=
open_orders_weight (r_eligible_lines.line_id);
--+-----------------------------------------------------------------------------+
--+ Sum up the weight for all eligible open order lines +
--+-----------------------------------------------------------------------------+
l_total_weight := l_total_weight + l_line_weight;
END LOOP;
--+-----------------------------------------------------------------------------+
--+ Check the total weight exceeds to truck load weight or not +
--+-----------------------------------------------------------------------------+
IF l_total_weight >= l_truckload_limit
THEN
BEGIN
SELECT attribute5
INTO l_ship_hold_flag
FROM hz_cust_site_uses_all
WHERE site_use_id = r_ware_house.ship_to_org_id
AND site_use_code = 'SHIP_TO';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ship_hold_flag := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_ship_hold_flag IS NOT NULL
THEN
l_release_hold_flag := l_ship_hold_flag;
ELSE
BEGIN
SELECT attribute10
INTO l_release_hold_flag
FROM hz_parties
WHERE party_id = l_party_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_release_hold_flag := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; -- IF l_ship_hold_flag IS NOT NULL THEN
l_debug_point := '060';
IF l_release_hold_flag IS NULL
THEN
l_release_hold_flag := 'N';
END IF;
--+----------------------------------------------------------------------------------+
--+ If the auto release TLD hold is N then apply the holds for all eligible orders + +
--+----------------------------------------------------------------------------------+
IF l_release_hold_flag = 'N'
THEN
FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = l_order_header_id
AND ohl.line_id = r_cur_ord_ln.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_oe_hold_id IS NULL THEN
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
UPDATE oe_order_headers
SET attribute10 = '',
last_updated_by =
fnd_profile.VALUE ('user_id'),
last_update_date = SYSDATE
WHERE header_id = l_order_header_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
xxftc_om_apply_hld
(p_hold_id => l_hold_id,
p_header_id => l_order_header_id,
p_line_id => r_cur_ord_ln.line_id
);
END IF; --- IF l_oe_hold_id IS NULL THEN
END LOOP; --FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the all eligible open orders +
--+-----------------------------------------------------------------------------+
FOR r_hold_order IN
c_hold_order (r_ware_house.sold_to_org_id,
r_ware_house.ship_to_org_id,
r_ware_house.ship_from_org_id
)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the party id +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hp.party_name, hc.account_number,
hu.LOCATION, org.organization_code,
oel.unit_list_price, oel.unit_selling_price
INTO l_party_name, l_customer_number,
l_customer_location, l_warehouse_code,
l_list_price, l_selling_price
FROM hz_parties hp,
hz_cust_accounts hc,
oe_order_lines oel,
hz_cust_acct_sites hzc,
hz_cust_site_uses hu,
org_organization_definitions org
WHERE hc.party_id = hp.party_id
AND hc.cust_account_id = oel.sold_to_org_id
AND oel.line_id = r_hold_order.line_id
AND hc.cust_account_id = hzc.cust_account_id
AND hzc.cust_acct_site_id = hu.cust_acct_site_id
AND hu.site_use_id = oel.ship_to_org_id
AND hu.site_use_code = 'SHIP_TO'
AND org.organization_id = oel.ship_from_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_party_name := '';
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),
0
)
INTO l_order_weight
FROM oe_order_lines oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id =
mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id =
mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight := NULL;
l_ord_user_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT fnd.user_id, fnd.user_name,
fnd.last_update_date
INTO l_ord_user_id, l_update_user_name,
l_date
FROM oe_order_headers_all oeh, fnd_user fnd
WHERE oeh.header_id = l_order_header_id
AND oeh.last_updated_by = fnd.user_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_update_user_name := '';
l_ord_user_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT shipping_instructions
INTO l_shipping_instructions
FROM oe_order_headers_all
WHERE header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_shipping_instructions := '';
l_ord_user_id := NULL;
l_error_message :=
'l_shipping_instructions is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(mtr.reservation_quantity,0)
INTO l_reservation_quantity
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_reservation_quantity:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(ORDERED_QUANTITY,0)
INTO l_ORDERED_QUANTITY
FROM oe_order_lines_all
WHERE line_id= r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ORDERED_QUANTITY:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------+
--+ get the credit check hold id for trailer load hold +
--+-----------------------------------------------------+
BEGIN
SELECT order_hold_id
INTO l_crd_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Credit Check Failure'
AND ohl.released_flag = 'N'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_crd_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_crd_oe_hold_id IS NOT NULL
THEN
l_crd_oe_hold_sts := 'On Credit Check Hold';
ELSE
l_crd_oe_hold_sts := 'No Credit Check Hold';
END IF;
l_diff := l_list_price - l_selling_price;
l_percentage := l_diff / l_list_price * 100;
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.line_id = r_hold_order.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_oe_hold_id IS NOT NULL THEN
l_hold_status:='On TLD Hold';
ELSE
l_hold_status:='';
END IF;
BEGIN
SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
INTO l_item_number,l_line_number
FROM oe_order_lines_all oel,mtl_system_items_b mtl
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND oel.line_id=r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_item_number := '';
l_error_message :=
'l_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_item_number'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),0)
INTO l_order_weight_1
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr,
oe_order_headers_all oeh
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND oeh.header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight_1 := 0;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mci.customer_item_number
INTO l_cust_item_number
FROM oe_order_lines_all oel,
mtl_customer_item_xrefs_v mci,
mtl_system_items_b mtl
WHERE mci.customer_id=oel.sold_to_org_id
AND oel.line_id=r_hold_order.line_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.segment1=mci.concatenated_segments
AND mtl.inventory_item_id=oel.inventory_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_cust_item_number :='';
l_error_message :=
'l_cust_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_order_weight_1 > 0 THEN
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT INTO xxftc_order_header_hold_tbl
VALUES (l_order_header_id,
TO_CHAR (r_hold_order.order_number),
l_line_number,
r_hold_order.header_id, l_party_name,
l_customer_number,
l_customer_location,
l_truckload_limit, l_order_weight,
l_total_weight, l_warehouse_code,
l_list_price, l_selling_price,
l_diff, ROUND (l_percentage,2),
l_hold_status, l_update_user_name,
''
, ''
, l_crd_oe_hold_sts,l_item_number
, l_ORDERED_QUANTITY
, l_reservation_quantity
, l_shipping_instructions
, l_cust_item_number);
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for inserting into xxftc_order_header_hold_tbl'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; --IF l_order_weight>0 THEN
END LOOP; --FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)
--+----------------------------------------------------------------------------------+
--+ If the auto release TLD hold is Y then release the holds for all eligible orders + +
--+----------------------------------------------------------------------------------+
ELSIF l_release_hold_flag = 'Y'
THEN
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
FOR r_curr_ord IN c_curr_ord (l_order_header_id)
LOOP
xxftc_om_apply_hld (p_hold_id => l_hold_id,
p_header_id => l_order_header_id,
p_line_id => r_curr_ord.line_id
);
END LOOP;
--FOR r_curr_ord IN c_curr_ord(l_order_header_id)
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the all eligible open orders +
--+-----------------------------------------------------------------------------+
FOR r_hold_order IN
c_hold_order (r_ware_house.sold_to_org_id,
r_ware_house.ship_to_org_id,
r_ware_house.ship_from_org_id
)
LOOP
BEGIN
SELECT hp.party_name, hc.account_number,
hu.LOCATION, org.organization_code,
oel.unit_list_price, oel.unit_selling_price
INTO l_party_name, l_customer_number,
l_customer_location, l_warehouse_code,
l_list_price, l_selling_price
FROM hz_parties hp,
hz_cust_accounts hc,
oe_order_lines oel,
hz_cust_acct_sites hzc,
hz_cust_site_uses hu,
org_organization_definitions org
WHERE hc.party_id = hp.party_id
AND hc.cust_account_id = oel.sold_to_org_id
AND oel.line_id = r_hold_order.line_id
AND hc.cust_account_id = hzc.cust_account_id
AND hzc.cust_acct_site_id = hu.cust_acct_site_id
AND hu.site_use_id = oel.ship_to_org_id
AND hu.site_use_code = 'SHIP_TO'
AND org.organization_id = oel.ship_from_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_party_name := '';
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),
0
)
INTO l_order_weight
FROM oe_order_lines oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id =
mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id =
mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight := NULL;
l_ord_user_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(mtr.reservation_quantity,0)
INTO l_reservation_quantity
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_reservation_quantity:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(ORDERED_QUANTITY,0)
INTO l_ORDERED_QUANTITY
FROM oe_order_lines_all
WHERE line_id= r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ORDERED_QUANTITY:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT fnd.user_name
INTO l_update_user_name
FROM oe_order_headers_all oeh, fnd_user fnd
WHERE oeh.header_id = l_order_header_id
AND oeh.last_updated_by = fnd.user_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_update_user_name := '';
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
INTO l_item_number,l_line_number
FROM oe_order_lines_all oel,mtl_system_items_b mtl
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND oel.line_id=r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_item_number := '';
l_error_message :=
'l_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_item_number'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------+
--+ get the credit check hold id for trailer load hold +
--+-----------------------------------------------------+
BEGIN
SELECT order_hold_id
INTO l_crd_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Credit Check Failure'
AND ohl.released_flag = 'N'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_crd_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT shipping_instructions
INTO l_shipping_instructions
FROM oe_order_headers_all
WHERE header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_shipping_instructions := '';
l_ord_user_id := NULL;
l_error_message :=
'l_shipping_instructions is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
INTO l_item_number,l_line_number
FROM oe_order_lines_all oel,mtl_system_items_b mtl
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND oel.line_id=r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_item_number := '';
l_error_message :=
'l_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_item_number'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_crd_oe_hold_id IS NOT NULL
THEN
l_crd_oe_hold_sts := 'On Credit Check Hold';
ELSE
l_crd_oe_hold_sts := 'No Credit Check Hold';
END IF;
l_diff := l_list_price - l_selling_price;
l_percentage := l_diff / l_list_price * 100;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),0)
INTO l_order_weight_1
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr,
oe_order_headers_all oeh
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND oeh.header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight_1 := 0;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mci.customer_item_number
INTO l_cust_item_number
FROM oe_order_lines_all oel,
mtl_customer_item_xrefs_v mci,
mtl_system_items_b mtl
WHERE mci.customer_id=oel.sold_to_org_id
AND oel.line_id=r_hold_order.line_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.segment1=mci.concatenated_segments
AND mtl.inventory_item_id=oel.inventory_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_cust_item_number :='';
l_error_message :=
'l_cust_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_order_weight_1 > 0 THEN
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT INTO xxftc_order_header_hold_tbl
VALUES (l_order_header_id,
TO_CHAR (r_hold_order.order_number),
l_line_number,
r_hold_order.header_id, l_party_name,
l_customer_number,
l_customer_location,
l_truckload_limit, l_order_weight,
l_total_weight, l_warehouse_code,
l_list_price, l_selling_price,
l_diff, ROUND (l_percentage,2),
'Released TLD Hold',
l_update_user_name, ''
, ''
, l_crd_oe_hold_sts
, l_item_number
, l_ORDERED_QUANTITY
, l_reservation_quantity
, l_shipping_instructions
, l_cust_item_number
);
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for inserting into xxftc_order_header_hold_tbl'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.line_id = r_hold_order.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; --IF l_order_weight_1 > 0 THEN
--+-----------------------------------------------------------------------------+
--+ Check the hold is there or not for eligible order +
--+-----------------------------------------------------------------------------+
IF l_oe_hold_id IS NOT NULL
THEN
l_debug_point := '080';
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
UPDATE oe_order_headers
SET attribute10 = '',
last_updated_by =
fnd_profile.VALUE ('user_id'),
last_update_date = SYSDATE
WHERE header_id = r_hold_order.header_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
xxftc_om_release_hld
(p_hold_id => l_hold_id,
p_header_id => r_hold_order.header_id,
p_line_id => r_hold_order.line_id
);
END IF; ---IF l_oe_hold_id IS NULL THEN
END LOOP;
END IF; --ELSIF l_release_hold_flag='Y' THEN
BEGIN
SELECT instance_name
INTO l_instance_name
FROM v$instance
WHERE ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
l_report_type := 'BOOK';
v_template := 'XXFTCALRTMAIL';
l_request1 :=
fnd_request.add_layout (template_appl_name => 'XXFTC',
template_code => v_template,
template_language => 'en',
template_territory => 'US',
output_format => 'EXCEL'
);
--+------------------------------------------------------------+
--+submit the request to get the mail content +
--+------------------------------------------------------------+
l_request_id :=
fnd_request.submit_request
(application => 'XXFTC',
program => 'XXFTCALRTMAIL',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => l_order_header_id,
argument2 => l_report_type
);
COMMIT;
l_result := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id
,30
,0
,l_phase
,l_status
,l_dev_phase
,l_dev_status
,l_error_mesg);
COMMIT;
--+------------------------------------------------------------+
--+submit the request to send the mail through shell program +
--+------------------------------------------------------------+
--modified by vandana on 19th June
/*SELECT TO_CHAR (SYSDATE + 3 / 60 / 24,
'DD-MON-YYYY HH24:MI:SS'
)
INTO l_request_date
FROM DUAL;*/
l_request_id1 :=
fnd_request.submit_request
(application => 'XXFTC',
program => 'XXFTCXMLMAIL',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => l_request_id,
argument2 => l_send_email,
argument3 => 'Threshold Limit Exceeded'||'-'|| l_instance_name,
argument4 => 'BOOK_ORDER'||v_date
);
COMMIT;
END;
--+-----------------------------------------------------------------------------+
--+ If total weight doesn't exceed to truck load weight +
--+-----------------------------------------------------------------------------+
ELSE -- IF l_total_weight>l_truckload_limit THEN
l_debug_point := '0100';
FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = l_order_header_id
AND ohl.line_id = r_cur_ord_ln.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_oe_hold_id IS NULL THEN
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
UPDATE oe_order_headers
SET attribute10 = '',
last_updated_by =
fnd_profile.VALUE ('user_id'),
last_update_date = SYSDATE
WHERE header_id = l_order_header_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
xxftc_om_apply_hld
(p_hold_id => l_hold_id,
p_header_id => l_order_header_id,
p_line_id => r_cur_ord_ln.line_id
);
END IF; --- IF l_oe_hold_id IS NULL THEN
END LOOP; --FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
END IF; --IF l_total_weight>l_truckload_limit THEN
END IF; --- IF l_freight_header_id IS NOT NULL THEN
END IF; --IF l_truckload_limit IS NOT NULL THEN
END LOOP; --FOR r_ware_house IN c_ware_house(l_order_header_id)
resultout := wf_engine.eng_completed;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'Other error in XXFTC_ORDER_APPLY_HOLD'
|| SUBSTR (SQLERRM, 1, 50);
END xxftc_order_apply_hold;
PROCEDURE xxftc_om_apply_hld (
p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER
)
IS
l_msg_data VARCHAR2 (2000);
l_return_status VARCHAR2 (50);
l_msg_count NUMBER;
l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
l_debug_point VARCHAR2 (100);
l_error_message VARCHAR2 (2000);
l_attr VARCHAR2 (10);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
l_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
l_hold_source_rec.hold_id := p_hold_id; -- hold_id
l_hold_source_rec.hold_entity_code := 'O'; -- order level hold
l_hold_source_rec.hold_entity_id := p_header_id;
-- header_id of the order
l_hold_source_rec.header_id := p_header_id; -- header_id of the order
l_hold_source_rec.line_id := p_line_id;
l_return_status := NULL;
l_msg_data := NULL;
l_msg_count := NULL;
l_debug_point := '090';
oe_holds_pub.apply_holds (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_hold_source_rec => l_hold_source_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line (l_return_status);
IF l_return_status <> fnd_api.g_ret_sts_success
THEN
l_error_message :=
'error for order header id while calling holds API'
|| p_header_id
|| l_msg_data;
END IF; -- IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'Other error in XXFTC_OM_APPLY_HLD' || SUBSTR (SQLERRM, 1, 50);
END xxftc_om_apply_hld;
PROCEDURE xxftc_om_release_hld (
p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER
)
IS
l_return_status VARCHAR2 (30);
l_release_reason_code oe_hold_releases.release_reason_code%TYPE;
l_release_comment oe_hold_releases.release_comment%TYPE;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_order_tbl oe_holds_pvt.order_tbl_type;
errm VARCHAR2 (2000);
l_error_message VARCHAR2 (2000);
l_reason_code VARCHAR2 (100);
l_comment VARCHAR2 (100);
l_debug_point VARCHAR2 (100);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
SELECT lookup_code, meaning
INTO l_reason_code, l_comment
FROM fnd_lookup_values
WHERE lookup_type = 'RELEASE_REASON'
AND meaning = 'TLD Threshold Reached';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for l_reason_code,l_comment'
|| SUBSTR (SQLERRM, 1, 100);
END;
l_order_tbl (1).line_id := p_line_id; --p_line_id;
l_order_tbl (1).header_id := p_header_id; --p_header_id;
l_release_reason_code := l_reason_code;
l_release_comment := l_comment;
l_debug_point := '0100';
oe_holds_pub.release_holds
(p_order_tbl => l_order_tbl,
p_hold_id => p_hold_id,
p_release_reason_code => l_release_reason_code,
p_release_comment => l_release_comment,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line (l_return_status);
IF l_return_status <> fnd_api.g_ret_sts_success
THEN
l_error_message := l_msg_data;
--dbms_output.put_line('Hold is released');
END IF; -- IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'Other error in XXFTC_OM_RELEASE_HLD' || SUBSTR (SQLERRM, 1, 50);
END xxftc_om_release_hld;
END xxftc_om_order_holds;
/
PACKAGE XXFTC_OM_ORDER_HOLDS AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_OM_ORDER_HOLDS.pks ***/
/*** AUTHOR : Prasad Potluri ***/
/*** VERSION : 1.0 ***/
/*** PURPOSE : procedure to apply hold for order ***/
/*** ***/
/*** PARAMETERS : order header id ***/
/*** DATE : 03/June/2009 ***/
/**************************************************************************************************/
PROCEDURE XXFTC_ORDER_APPLY_HOLD (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
);
PROCEDURE XXFTC_OM_APPLY_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
PROCEDURE XXFTC_OM_RELEASE_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
END XXFTC_OM_ORDER_HOLDS;
/
create or replace
PACKAGE BODY xxftc_om_order_holds
AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_OM_ORDER_HOLDS.pkb ***/
/*** AUTHOR : Prasad Potluri ***/
/*** VERSION : 1.0 ***/
/*** PURPOSE : procedure to apply hold for an order ***/
/*** ***/
/*** PARAMETERS : order header id ***/
/*** DATE : 03/June/2009 ***/
/**************************************************************************************************/
--+----------------------------------------------------------+
--+ Function Used to get the weight for the open order line +
--+----------------------------------------------------------+
FUNCTION open_orders_weight (p_line_id IN NUMBER)
RETURN NUMBER
IS
v_open_orders_weight NUMBER;
BEGIN
SELECT NVL (SUM (mtr.reservation_quantity * NVL (mtl.unit_weight, 0)),
0)
INTO v_open_orders_weight
FROM oe_order_lines oel, mtl_system_items_b mtl, mtl_reservations mtr
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = p_line_id;
RETURN v_open_orders_weight;
END open_orders_weight;
--+----------------------------------------------------------+
--+ Main Procedure used to apply the hold for an order +
--+----------------------------------------------------------+
PROCEDURE xxftc_order_apply_hold (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
)
IS
l_truckload_limit VARCHAR2 (100);
l_freight VARCHAR2 (100);
l_ship_method VARCHAR2 (100);
l_cust_id NUMBER;
l_order_header_id NUMBER;
l_ship_to_id NUMBER;
l_warehouse NUMBER;
l_total_weight NUMBER;
l_line_weight NUMBER;
l_oe_hold_id NUMBER;
l_hold_id NUMBER;
l_msg_count NUMBER;
l_freight_header_id NUMBER;
l_org NUMBER := fnd_profile.VALUE ('org_id');
l_msg_data VARCHAR2 (2000);
l_return_status VARCHAR2 (50);
l_debug_point VARCHAR2 (100);
l_error_message VARCHAR2 (2000);
l_program_name VARCHAR2 (256);
cp_application_id NUMBER;
l_application_id NUMBER;
l_alert_id NUMBER;
l_type VARCHAR2 (3);
l_err_msg VARCHAR2 (200);
p_request_id NUMBER;
l_party_id NUMBER;
l_release_hold_flag VARCHAR2 (1);
l_party_name VARCHAR2 (200);
l_customer_number VARCHAR2 (30);
l_customer_location VARCHAR2 (200);
l_warehouse_code VARCHAR2 (50);
l_order_weight NUMBER;
l_order_weight_1 NUMBER;
l_request_id NUMBER;
l_phase1 VARCHAR2 (200);
l_status1 VARCHAR2 (200);
l_dev_phase1 VARCHAR2 (200);
l_dev_status1 VARCHAR2 (200);
l_result1 BOOLEAN;
l_error_mesg1 VARCHAR2 (2000);
l_request_id1 NUMBER;
l_phase2 VARCHAR2 (200);
l_status2 VARCHAR2 (200);
l_dev_phase2 VARCHAR2 (200);
l_dev_status2 VARCHAR2 (200);
l_result2 BOOLEAN;
l_error_mesg2 VARCHAR2 (2000);
l_current_order NUMBER;
l_ship_hold_flag VARCHAR2 (1);
l_book_user NUMBER;
l_user_email VARCHAR2 (500);
l_send_email VARCHAR2 (1000);
l_order_tbl oe_holds_pvt.order_tbl_type;
l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
l_request_date VARCHAR2 (30);
l_ship_thresold VARCHAR2 (200);
l_update_user_name VARCHAR2 (100);
l_instance_name VARCHAR2 (100);
l_report_type VARCHAR2 (50);
l_ord_user_id NUMBER;
l_date DATE;
l_list_price NUMBER;
l_selling_price NUMBER;
l_diff NUMBER;
l_percentage NUMBER;
l_request1 BOOLEAN;
v_template VARCHAR2 (30);
l_crd_oe_hold_id NUMBER;
l_crd_oe_hold_sts VARCHAR2 (100);
v_date VARCHAR2(20):=TO_CHAR(SYSDATE,'MMDDYY');
l_hold_status VARCHAR2(200);
l_ORDERED_QUANTITY NUMBER;
l_reservation_quantity NUMBER;
l_shipping_instructions VARCHAR2(1000);
l_result BOOLEAN;
l_phase VARCHAR2(200);
l_status VARCHAR2(200);
l_dev_phase VARCHAR2(200);
l_dev_status VARCHAR2(200);
l_error_mesg VARCHAR2(2000);
l_item_number VARCHAR2(100);
l_line_number VARCHAR2(50);
l_cust_item_number VARCHAR2(200);
CURSOR c_ware_house (p_header_id IN NUMBER)
IS
SELECT DISTINCT sold_to_org_id, ship_to_org_id, ship_from_org_id
FROM oe_order_lines_all
WHERE header_id = p_header_id;
--+-----------------------------------------------------------------------------------------------------------------------+
--+ Cursor used to get the eligible open order lines against current order customer,ship to location and warehouse +
--+-----------------------------------------------------------------------------------------------------------------------+
CURSOR c_eligible_lines (
p_sold_to_org_id IN NUMBER,
p_ship_to_org_id IN NUMBER,
p_ship_from_org_id IN NUMBER
)
IS
SELECT DISTINCT oel.line_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oel.sold_to_org_id = p_sold_to_org_id
AND oel.ship_to_org_id = p_ship_to_org_id
AND oel.ship_from_org_id = p_ship_from_org_id
AND oeh.header_id = oel.header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
--+----------------------------------------------------------------------------------------------------------------------------+
--+ Cursor used to get the eligible open orders to apply holds against current order customer,ship to location and warehouse +
--+----------------------------------------------------------------------------------------------------------------------------+
CURSOR c_hold_order (
p_sold_to_org_id IN NUMBER,
p_ship_to_org_id IN NUMBER,
p_ship_from_org_id IN NUMBER
)
IS
SELECT DISTINCT oeh.header_id, oeh.order_number, oel.line_id,
oel.line_number
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oel.sold_to_org_id = p_sold_to_org_id
AND oel.ship_to_org_id = p_ship_to_org_id
AND oel.ship_from_org_id = p_ship_from_org_id
AND oeh.header_id = oel.header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
CURSOR c_curr_ord (p_header_id IN NUMBER)
IS
SELECT DISTINCT oel.line_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oeh.header_id = oel.header_id
AND oeh.header_id = p_header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
CURSOR c_cur_ord_ln(p_header_id IN NUMBER) IS
SELECT oel.header_id,oel.line_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oeh.header_id = oel.header_id
AND oeh.header_id = p_header_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
BEGIN
--+---------------------------+
--+ Set the org id +
--+---------------------------+
mo_global.set_policy_context ('S', l_org);
l_debug_point := '010';
l_error_message := '';
l_truckload_limit := '';
l_freight_header_id := NULL;
l_order_header_id := TO_NUMBER (itemkey);
--+------------------------------------------+
--+ Calling Parcel Backorder Hold +
--+------------------------------------------+
xxftc_om_kewill_hold_pkg.xxftc_order_apply_hld (l_order_header_id);
FOR r_ware_house IN c_ware_house (l_order_header_id)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the party id +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hp.party_id
INTO l_party_id
FROM hz_parties hp, hz_cust_accounts hc
WHERE hc.cust_account_id = r_ware_house.sold_to_org_id
AND hc.party_id = hp.party_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_party_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT attribute6
INTO l_ship_thresold
FROM hz_cust_site_uses_all
WHERE site_use_id = r_ware_house.ship_to_org_id
AND site_use_code = 'SHIP_TO';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ship_thresold := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_ship_thresold IS NOT NULL
THEN
l_truckload_limit := l_ship_thresold;
ELSE
BEGIN
SELECT attribute2
INTO l_truckload_limit
FROM hz_parties
WHERE party_id = l_party_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_truckload_limit := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; -- IF l_ship_thresold IS NOT NULL THEN
--+---------------------------------------------------------------------+
--+ Check the truckload weight limit IS NULL OR NOT NULL +
--+ IF truckload weight limit IS NOT NULL then only proceed to further. +
--+---------------------------------------------------------------------+
IF l_truckload_limit IS NOT NULL
THEN
l_debug_point := '020';
--+---------------------------------------------------------------------+
--+ Get the order header id to check freight/ship method combination +
--+---------------------------------------------------------------------+
BEGIN
SELECT oeh.header_id
INTO l_freight_header_id
FROM oe_order_headers oeh,
fnd_lookup_values fl,
wsh_carrier_services wc
WHERE oeh.header_id = l_order_header_id
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y')
AND fl.lookup_code = oeh.freight_terms_code
AND wc.ship_method_code = oeh.shipping_method_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_freight_header_id := NULL;
l_error_message :=
'l_freight_header_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
l_debug_point := '030';
--+-----------------------------------------------------------------------------------------------------------+
--+Check the freight term - ship method combination +
--+IF freight term - ship method combination +
--+doesn't exist in('Parcel-Prepay Add','Parcel-Third Party Billing','Parcel-Prepaid','Will Call-Prepaid' +
--+then proceed to further. +
--+-----------------------------------------------------------------------------------------------------------+
IF l_freight_header_id IS NOT NULL
THEN
l_debug_point := '040';
--+-----------------------------------------------------------------------------+
--+ Get the Current cancel order number +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT order_number, last_updated_by
INTO l_current_order, l_book_user
FROM oe_order_headers
WHERE header_id = l_order_header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_current_order := NULL;
l_book_user := NULL;
l_error_message :=
'l_cust_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------------------------------+
--+ Get the email address of booking order +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT email_address
INTO l_user_email
FROM fnd_user
WHERE user_id = l_book_user;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_user_email := '';
l_error_message :=
'l_cust_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_user_email IS NOT NULL
THEN
l_send_email :=
l_user_email || ',' || 'mmanier@falkentire.com';
ELSE
l_send_email := 'mmanier@falkentire.com';
END IF;
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the eligible order lines +
--+-----------------------------------------------------------------------------+
l_total_weight := 0;
FOR r_eligible_lines IN
c_eligible_lines (r_ware_house.sold_to_org_id,
r_ware_house.ship_to_org_id,
r_ware_house.ship_from_org_id
)
LOOP
l_debug_point := '050';
l_line_weight :=
open_orders_weight (r_eligible_lines.line_id);
--+-----------------------------------------------------------------------------+
--+ Sum up the weight for all eligible open order lines +
--+-----------------------------------------------------------------------------+
l_total_weight := l_total_weight + l_line_weight;
END LOOP;
--+-----------------------------------------------------------------------------+
--+ Check the total weight exceeds to truck load weight or not +
--+-----------------------------------------------------------------------------+
IF l_total_weight >= l_truckload_limit
THEN
BEGIN
SELECT attribute5
INTO l_ship_hold_flag
FROM hz_cust_site_uses_all
WHERE site_use_id = r_ware_house.ship_to_org_id
AND site_use_code = 'SHIP_TO';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ship_hold_flag := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_ship_hold_flag IS NOT NULL
THEN
l_release_hold_flag := l_ship_hold_flag;
ELSE
BEGIN
SELECT attribute10
INTO l_release_hold_flag
FROM hz_parties
WHERE party_id = l_party_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_release_hold_flag := '';
l_error_message :=
'l_release_hold_flag is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_release_hold_flag'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; -- IF l_ship_hold_flag IS NOT NULL THEN
l_debug_point := '060';
IF l_release_hold_flag IS NULL
THEN
l_release_hold_flag := 'N';
END IF;
--+----------------------------------------------------------------------------------+
--+ If the auto release TLD hold is N then apply the holds for all eligible orders + +
--+----------------------------------------------------------------------------------+
IF l_release_hold_flag = 'N'
THEN
FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = l_order_header_id
AND ohl.line_id = r_cur_ord_ln.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_oe_hold_id IS NULL THEN
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
UPDATE oe_order_headers
SET attribute10 = '',
last_updated_by =
fnd_profile.VALUE ('user_id'),
last_update_date = SYSDATE
WHERE header_id = l_order_header_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
xxftc_om_apply_hld
(p_hold_id => l_hold_id,
p_header_id => l_order_header_id,
p_line_id => r_cur_ord_ln.line_id
);
END IF; --- IF l_oe_hold_id IS NULL THEN
END LOOP; --FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the all eligible open orders +
--+-----------------------------------------------------------------------------+
FOR r_hold_order IN
c_hold_order (r_ware_house.sold_to_org_id,
r_ware_house.ship_to_org_id,
r_ware_house.ship_from_org_id
)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the party id +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hp.party_name, hc.account_number,
hu.LOCATION, org.organization_code,
oel.unit_list_price, oel.unit_selling_price
INTO l_party_name, l_customer_number,
l_customer_location, l_warehouse_code,
l_list_price, l_selling_price
FROM hz_parties hp,
hz_cust_accounts hc,
oe_order_lines oel,
hz_cust_acct_sites hzc,
hz_cust_site_uses hu,
org_organization_definitions org
WHERE hc.party_id = hp.party_id
AND hc.cust_account_id = oel.sold_to_org_id
AND oel.line_id = r_hold_order.line_id
AND hc.cust_account_id = hzc.cust_account_id
AND hzc.cust_acct_site_id = hu.cust_acct_site_id
AND hu.site_use_id = oel.ship_to_org_id
AND hu.site_use_code = 'SHIP_TO'
AND org.organization_id = oel.ship_from_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_party_name := '';
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),
0
)
INTO l_order_weight
FROM oe_order_lines oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id =
mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id =
mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight := NULL;
l_ord_user_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT fnd.user_id, fnd.user_name,
fnd.last_update_date
INTO l_ord_user_id, l_update_user_name,
l_date
FROM oe_order_headers_all oeh, fnd_user fnd
WHERE oeh.header_id = l_order_header_id
AND oeh.last_updated_by = fnd.user_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_update_user_name := '';
l_ord_user_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT shipping_instructions
INTO l_shipping_instructions
FROM oe_order_headers_all
WHERE header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_shipping_instructions := '';
l_ord_user_id := NULL;
l_error_message :=
'l_shipping_instructions is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(mtr.reservation_quantity,0)
INTO l_reservation_quantity
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_reservation_quantity:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(ORDERED_QUANTITY,0)
INTO l_ORDERED_QUANTITY
FROM oe_order_lines_all
WHERE line_id= r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ORDERED_QUANTITY:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------+
--+ get the credit check hold id for trailer load hold +
--+-----------------------------------------------------+
BEGIN
SELECT order_hold_id
INTO l_crd_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Credit Check Failure'
AND ohl.released_flag = 'N'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_crd_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_crd_oe_hold_id IS NOT NULL
THEN
l_crd_oe_hold_sts := 'On Credit Check Hold';
ELSE
l_crd_oe_hold_sts := 'No Credit Check Hold';
END IF;
l_diff := l_list_price - l_selling_price;
l_percentage := l_diff / l_list_price * 100;
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.line_id = r_hold_order.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_oe_hold_id IS NOT NULL THEN
l_hold_status:='On TLD Hold';
ELSE
l_hold_status:='';
END IF;
BEGIN
SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
INTO l_item_number,l_line_number
FROM oe_order_lines_all oel,mtl_system_items_b mtl
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND oel.line_id=r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_item_number := '';
l_error_message :=
'l_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_item_number'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),0)
INTO l_order_weight_1
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr,
oe_order_headers_all oeh
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND oeh.header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight_1 := 0;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mci.customer_item_number
INTO l_cust_item_number
FROM oe_order_lines_all oel,
mtl_customer_item_xrefs_v mci,
mtl_system_items_b mtl
WHERE mci.customer_id=oel.sold_to_org_id
AND oel.line_id=r_hold_order.line_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.segment1=mci.concatenated_segments
AND mtl.inventory_item_id=oel.inventory_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_cust_item_number :='';
l_error_message :=
'l_cust_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_order_weight_1 > 0 THEN
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT INTO xxftc_order_header_hold_tbl
VALUES (l_order_header_id,
TO_CHAR (r_hold_order.order_number),
l_line_number,
r_hold_order.header_id, l_party_name,
l_customer_number,
l_customer_location,
l_truckload_limit, l_order_weight,
l_total_weight, l_warehouse_code,
l_list_price, l_selling_price,
l_diff, ROUND (l_percentage,2),
l_hold_status, l_update_user_name,
''
, ''
, l_crd_oe_hold_sts,l_item_number
, l_ORDERED_QUANTITY
, l_reservation_quantity
, l_shipping_instructions
, l_cust_item_number);
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for inserting into xxftc_order_header_hold_tbl'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; --IF l_order_weight>0 THEN
END LOOP; --FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)
--+----------------------------------------------------------------------------------+
--+ If the auto release TLD hold is Y then release the holds for all eligible orders + +
--+----------------------------------------------------------------------------------+
ELSIF l_release_hold_flag = 'Y'
THEN
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
FOR r_curr_ord IN c_curr_ord (l_order_header_id)
LOOP
xxftc_om_apply_hld (p_hold_id => l_hold_id,
p_header_id => l_order_header_id,
p_line_id => r_curr_ord.line_id
);
END LOOP;
--FOR r_curr_ord IN c_curr_ord(l_order_header_id)
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the all eligible open orders +
--+-----------------------------------------------------------------------------+
FOR r_hold_order IN
c_hold_order (r_ware_house.sold_to_org_id,
r_ware_house.ship_to_org_id,
r_ware_house.ship_from_org_id
)
LOOP
BEGIN
SELECT hp.party_name, hc.account_number,
hu.LOCATION, org.organization_code,
oel.unit_list_price, oel.unit_selling_price
INTO l_party_name, l_customer_number,
l_customer_location, l_warehouse_code,
l_list_price, l_selling_price
FROM hz_parties hp,
hz_cust_accounts hc,
oe_order_lines oel,
hz_cust_acct_sites hzc,
hz_cust_site_uses hu,
org_organization_definitions org
WHERE hc.party_id = hp.party_id
AND hc.cust_account_id = oel.sold_to_org_id
AND oel.line_id = r_hold_order.line_id
AND hc.cust_account_id = hzc.cust_account_id
AND hzc.cust_acct_site_id = hu.cust_acct_site_id
AND hu.site_use_id = oel.ship_to_org_id
AND hu.site_use_code = 'SHIP_TO'
AND org.organization_id = oel.ship_from_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_party_name := '';
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),
0
)
INTO l_order_weight
FROM oe_order_lines oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id =
mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id =
mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight := NULL;
l_ord_user_id := NULL;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(mtr.reservation_quantity,0)
INTO l_reservation_quantity
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.line_id = r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_reservation_quantity:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT NVL(ORDERED_QUANTITY,0)
INTO l_ORDERED_QUANTITY
FROM oe_order_lines_all
WHERE line_id= r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ORDERED_QUANTITY:=0;
l_ord_user_id := NULL;
l_error_message :=
'l_ORDERED_QUANTITY is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT fnd.user_name
INTO l_update_user_name
FROM oe_order_headers_all oeh, fnd_user fnd
WHERE oeh.header_id = l_order_header_id
AND oeh.last_updated_by = fnd.user_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_update_user_name := '';
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
INTO l_item_number,l_line_number
FROM oe_order_lines_all oel,mtl_system_items_b mtl
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND oel.line_id=r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_item_number := '';
l_error_message :=
'l_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_item_number'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------+
--+ get the credit check hold id for trailer load hold +
--+-----------------------------------------------------+
BEGIN
SELECT order_hold_id
INTO l_crd_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Credit Check Failure'
AND ohl.released_flag = 'N'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_crd_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id';
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT shipping_instructions
INTO l_shipping_instructions
FROM oe_order_headers_all
WHERE header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_shipping_instructions := '';
l_ord_user_id := NULL;
l_error_message :=
'l_shipping_instructions is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
INTO l_item_number,l_line_number
FROM oe_order_lines_all oel,mtl_system_items_b mtl
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = oel.ship_from_org_id
AND oel.line_id=r_hold_order.line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_item_number := '';
l_error_message :=
'l_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_item_number'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_crd_oe_hold_id IS NOT NULL
THEN
l_crd_oe_hold_sts := 'On Credit Check Hold';
ELSE
l_crd_oe_hold_sts := 'No Credit Check Hold';
END IF;
l_diff := l_list_price - l_selling_price;
l_percentage := l_diff / l_list_price * 100;
BEGIN
SELECT NVL (SUM ( mtr.reservation_quantity
* NVL (mtl.unit_weight, 0)
),0)
INTO l_order_weight_1
FROM oe_order_lines_all oel,
mtl_system_items_b mtl,
mtl_reservations mtr,
oe_order_headers_all oeh
WHERE oel.inventory_item_id = mtl.inventory_item_id
AND oel.flow_status_code NOT IN
('CANCELLED',
'SHIPPED',
'CLOSED',
'AWAITING_RETURN',
'AWAITING_FULFILLMENT',
'AWAITING_RECEIPT'
)
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.organization_id = mtr.organization_id
AND mtl.inventory_item_id = mtr.inventory_item_id
AND mtr.demand_source_line_id = oel.line_id
AND oel.header_id = oeh.header_id
AND oeh.header_id = r_hold_order.header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight_1 := 0;
l_error_message :=
'l_party_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
SELECT mci.customer_item_number
INTO l_cust_item_number
FROM oe_order_lines_all oel,
mtl_customer_item_xrefs_v mci,
mtl_system_items_b mtl
WHERE mci.customer_id=oel.sold_to_org_id
AND oel.line_id=r_hold_order.line_id
AND mtl.organization_id = oel.ship_from_org_id
AND mtl.segment1=mci.concatenated_segments
AND mtl.inventory_item_id=oel.inventory_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_cust_item_number :='';
l_error_message :=
'l_cust_item_number is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order l_party_id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_order_weight_1 > 0 THEN
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT INTO xxftc_order_header_hold_tbl
VALUES (l_order_header_id,
TO_CHAR (r_hold_order.order_number),
l_line_number,
r_hold_order.header_id, l_party_name,
l_customer_number,
l_customer_location,
l_truckload_limit, l_order_weight,
l_total_weight, l_warehouse_code,
l_list_price, l_selling_price,
l_diff, ROUND (l_percentage,2),
'Released TLD Hold',
l_update_user_name, ''
, ''
, l_crd_oe_hold_sts
, l_item_number
, l_ORDERED_QUANTITY
, l_reservation_quantity
, l_shipping_instructions
, l_cust_item_number
);
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for inserting into xxftc_order_header_hold_tbl'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = r_hold_order.header_id
AND ohl.line_id = r_hold_order.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
END IF; --IF l_order_weight_1 > 0 THEN
--+-----------------------------------------------------------------------------+
--+ Check the hold is there or not for eligible order +
--+-----------------------------------------------------------------------------+
IF l_oe_hold_id IS NOT NULL
THEN
l_debug_point := '080';
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
UPDATE oe_order_headers
SET attribute10 = '',
last_updated_by =
fnd_profile.VALUE ('user_id'),
last_update_date = SYSDATE
WHERE header_id = r_hold_order.header_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
xxftc_om_release_hld
(p_hold_id => l_hold_id,
p_header_id => r_hold_order.header_id,
p_line_id => r_hold_order.line_id
);
END IF; ---IF l_oe_hold_id IS NULL THEN
END LOOP;
END IF; --ELSIF l_release_hold_flag='Y' THEN
BEGIN
SELECT instance_name
INTO l_instance_name
FROM v$instance
WHERE ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
l_report_type := 'BOOK';
v_template := 'XXFTCALRTMAIL';
l_request1 :=
fnd_request.add_layout (template_appl_name => 'XXFTC',
template_code => v_template,
template_language => 'en',
template_territory => 'US',
output_format => 'EXCEL'
);
--+------------------------------------------------------------+
--+submit the request to get the mail content +
--+------------------------------------------------------------+
l_request_id :=
fnd_request.submit_request
(application => 'XXFTC',
program => 'XXFTCALRTMAIL',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => l_order_header_id,
argument2 => l_report_type
);
COMMIT;
l_result := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id
,30
,0
,l_phase
,l_status
,l_dev_phase
,l_dev_status
,l_error_mesg);
COMMIT;
--+------------------------------------------------------------+
--+submit the request to send the mail through shell program +
--+------------------------------------------------------------+
--modified by vandana on 19th June
/*SELECT TO_CHAR (SYSDATE + 3 / 60 / 24,
'DD-MON-YYYY HH24:MI:SS'
)
INTO l_request_date
FROM DUAL;*/
l_request_id1 :=
fnd_request.submit_request
(application => 'XXFTC',
program => 'XXFTCXMLMAIL',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => l_request_id,
argument2 => l_send_email,
argument3 => 'Threshold Limit Exceeded'||'-'|| l_instance_name,
argument4 => 'BOOK_ORDER'||v_date
);
COMMIT;
END;
--+-----------------------------------------------------------------------------+
--+ If total weight doesn't exceed to truck load weight +
--+-----------------------------------------------------------------------------+
ELSE -- IF l_total_weight>l_truckload_limit THEN
l_debug_point := '0100';
FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point := '070';
BEGIN
SELECT order_hold_id
INTO l_oe_hold_id
FROM oe_order_holds_all ohl,
oe_hold_sources_all osl,
oe_hold_definitions od
WHERE ohl.header_id = l_order_header_id
AND ohl.line_id = r_cur_ord_ln.line_id
AND ohl.hold_source_id = osl.hold_source_id
AND osl.hold_id = od.hold_id
AND od.NAME = 'Trailer Load Line Hold'
AND ohl.released_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_oe_hold_id := NULL;
l_error_message :=
'l_oe_hold_id is null for an order header id'
|| l_order_header_id;
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
IF l_oe_hold_id IS NULL THEN
--+-----------------------------------------------------------------------------+
--+ Get the hold id for 'Trailer Load Hold' +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Trailer Load Line Hold';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| l_order_header_id
|| SUBSTR (SQLERRM, 1, 100);
END;
BEGIN
UPDATE oe_order_headers
SET attribute10 = '',
last_updated_by =
fnd_profile.VALUE ('user_id'),
last_update_date = SYSDATE
WHERE header_id = l_order_header_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for order header id'
|| SUBSTR (SQLERRM, 1, 100);
END;
xxftc_om_apply_hld
(p_hold_id => l_hold_id,
p_header_id => l_order_header_id,
p_line_id => r_cur_ord_ln.line_id
);
END IF; --- IF l_oe_hold_id IS NULL THEN
END LOOP; --FOR r_cur_ord_ln IN c_cur_ord_ln(l_order_header_id)
END IF; --IF l_total_weight>l_truckload_limit THEN
END IF; --- IF l_freight_header_id IS NOT NULL THEN
END IF; --IF l_truckload_limit IS NOT NULL THEN
END LOOP; --FOR r_ware_house IN c_ware_house(l_order_header_id)
resultout := wf_engine.eng_completed;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'Other error in XXFTC_ORDER_APPLY_HOLD'
|| SUBSTR (SQLERRM, 1, 50);
END xxftc_order_apply_hold;
PROCEDURE xxftc_om_apply_hld (
p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER
)
IS
l_msg_data VARCHAR2 (2000);
l_return_status VARCHAR2 (50);
l_msg_count NUMBER;
l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
l_debug_point VARCHAR2 (100);
l_error_message VARCHAR2 (2000);
l_attr VARCHAR2 (10);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
l_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
l_hold_source_rec.hold_id := p_hold_id; -- hold_id
l_hold_source_rec.hold_entity_code := 'O'; -- order level hold
l_hold_source_rec.hold_entity_id := p_header_id;
-- header_id of the order
l_hold_source_rec.header_id := p_header_id; -- header_id of the order
l_hold_source_rec.line_id := p_line_id;
l_return_status := NULL;
l_msg_data := NULL;
l_msg_count := NULL;
l_debug_point := '090';
oe_holds_pub.apply_holds (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_hold_source_rec => l_hold_source_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line (l_return_status);
IF l_return_status <> fnd_api.g_ret_sts_success
THEN
l_error_message :=
'error for order header id while calling holds API'
|| p_header_id
|| l_msg_data;
END IF; -- IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'Other error in XXFTC_OM_APPLY_HLD' || SUBSTR (SQLERRM, 1, 50);
END xxftc_om_apply_hld;
PROCEDURE xxftc_om_release_hld (
p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER
)
IS
l_return_status VARCHAR2 (30);
l_release_reason_code oe_hold_releases.release_reason_code%TYPE;
l_release_comment oe_hold_releases.release_comment%TYPE;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_order_tbl oe_holds_pvt.order_tbl_type;
errm VARCHAR2 (2000);
l_error_message VARCHAR2 (2000);
l_reason_code VARCHAR2 (100);
l_comment VARCHAR2 (100);
l_debug_point VARCHAR2 (100);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
SELECT lookup_code, meaning
INTO l_reason_code, l_comment
FROM fnd_lookup_values
WHERE lookup_type = 'RELEASE_REASON'
AND meaning = 'TLD Threshold Reached';
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'others error for l_reason_code,l_comment'
|| SUBSTR (SQLERRM, 1, 100);
END;
l_order_tbl (1).line_id := p_line_id; --p_line_id;
l_order_tbl (1).header_id := p_header_id; --p_header_id;
l_release_reason_code := l_reason_code;
l_release_comment := l_comment;
l_debug_point := '0100';
oe_holds_pub.release_holds
(p_order_tbl => l_order_tbl,
p_hold_id => p_hold_id,
p_release_reason_code => l_release_reason_code,
p_release_comment => l_release_comment,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line (l_return_status);
IF l_return_status <> fnd_api.g_ret_sts_success
THEN
l_error_message := l_msg_data;
--dbms_output.put_line('Hold is released');
END IF; -- IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
'Other error in XXFTC_OM_RELEASE_HLD' || SUBSTR (SQLERRM, 1, 50);
END xxftc_om_release_hld;
END xxftc_om_order_holds;
/
No comments:
Post a Comment