create or replace
PACKAGE XXFTC_OM_ORDER_RLS_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_RLS_HOLD (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out varchar2
);
PROCEDURE XXFTC_OM_APL_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
END XXFTC_OM_ORDER_RLS_HOLDS;
/
create or replace
PACKAGE BODY XXFTC_OM_ORDER_RLS_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_rls_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_rls_weight;
--+----------------------------------------------------------+
--+ Main Procedure used to apply the hold for an order +
--+----------------------------------------------------------+
PROCEDURE XXFTC_ORDER_RLS_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_CAN_LINE_ID NUMBER;
L_STATUS_CODE VARCHAR2(50);
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_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_request_date varchar2(30);
l_current_order NUMBER;
l_cancel_reason VARCHAR2(1000);
l_book_user NUMBER;
l_user_email VARCHAR2(500);
l_send_email VARCHAR2(1000);
l_ship_thresold varchar2(200);
l_update_user_name varchar2(100);
l_instance_name varchar2(100);
l_notes varchar2(1000);
l_report_type varchar2(50);
l_can_line_num number;
l_list_price number;
l_selling_price number;
l_diff number;
l_percentage number;
l_request1 BOOLEAN;
v_template varchar2(30);
l_CANCELLED_FLAG varchar2(10);
l_crd_oe_hold_id number;
l_crd_oe_hold_sts varchar2(100);
v_date varchar2(20):=to_char(sysdate,'MMDDYY');
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_cancel_header_id number;
l_line_count number;
l_item_number varchar2(100);
l_line_number varchar2(50);
l_cust_item_number VARCHAR2(200);
l_order_tbl Oe_Holds_Pvt.order_tbl_type;
l_hold_source_rec OE_HOLDS_PVT.HOLD_SOURCE_REC_TYPE;
--+-----------------------------------------------------------------------------------------------------------------------+
--+ 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');
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_can_line_id:=to_number(itemkey);
BEGIN
SELECT flow_status_code,line_number
INTO l_status_code,l_can_line_num
FROM oe_order_lines
WHERE line_id=l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_status_code:='';
l_error_message:='l_truckload_limit is null for an order header id';
WHEN OTHERS THEN
l_error_message:='others error for status_code'||SUBSTR(SQLERRM,1,100);
END;
IF l_status_code='CANCELLED' THEN
--+-----------------------------------------------------------------------------+
--+ Get the Customer id,ship to location is and warehouse against current order + +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT SOLD_TO_ORG_ID,SHIP_TO_ORG_ID,SHIP_FROM_ORG_ID
INTO l_cust_id,l_ship_to_id,l_warehouse
FROM oe_order_lines
WHERE line_id=l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_cust_id:=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 party id +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hp.party_id
INTO l_party_id
FROM hz_parties hp, hz_cust_accounts hc
WHERE hc.cust_account_id = l_cust_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 = l_ship_to_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,oe_order_lines oel
WHERE 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')
AND oeh.header_id=oel.header_id
AND oel.line_id=l_can_line_id;
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';
WHEN OTHERS THEN
l_error_message:='others error for 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_freight_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;
ELSE
-- l_send_email:='mmanier@falkentire.com';
NULL;
END IF;
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the eligible order lines +
--+-----------------------------------------------------------------------------+
l_total_weight:=0;
FOR r_eligible_lines IN c_eligible_lines(l_cust_id,l_ship_to_id,l_warehouse)
LOOP
l_debug_point:='050';
l_line_weight:=open_orders_rls_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 reduced to truck load weight or not +
--+-----------------------------------------------------------------------------+
IF l_total_weight<l_truckload_limit THEN
IF l_total_weight >0 THEN
l_debug_point:='0100';
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the all eligible open orders +
--+-----------------------------------------------------------------------------+
/* FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point:='0110';
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'||SUBSTR(SQLERRM,1,100);
END;
--+-----------------------------------------------------------------------------+
--+ Check the hold is there or not for eligible order +
--+-----------------------------------------------------------------------------+
IF l_oe_hold_id IS NULL THEN
l_debug_point:='0120';
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'||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_freight_header_id;
EXCEPTION
WHEN OTHERS THEN
l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);
END;
--+-----------------------------------------------------------------------------+
--+ Call the API 'Oe_Holds_Pub.apply_holds' to apply hold to order +
--+-----------------------------------------------------------------------------+
XXFTC_OM_APL_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; ----FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)
*/
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 = l_can_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';
WHEN OTHERS THEN
l_error_message:='others error for order l_party_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 = l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight := 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 reason
INTO l_cancel_reason
FROM oe_reasons_v
WHERE entity_id=l_can_line_id
AND entity_code='LINE';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_cancel_reason:='';
l_error_message:='l_party_id is null for an order header id';
WHEN OTHERS THEN
l_error_message:='others error for order l_party_id'||SUBSTR(SQLERRM,1,100);
END;
BEGIN
SELECT fnd.user_name,oeh.attribute3
INTO l_update_user_name,l_notes
FROM oe_order_headers oeh,
fnd_user fnd
WHERE oeh.header_id=l_freight_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;
--+-----------------------------------------------------+
--+ 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 = l_freight_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 = l_freight_header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_shipping_instructions := '';
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 = l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_reservation_quantity:=0;
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= l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ORDERED_QUANTITY:=0;
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;
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 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=l_can_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 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=l_can_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;
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT
INTO xxftc_order_header_hold_tbl
VALUES(l_freight_header_id,to_char(l_current_order),l_line_number,l_freight_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_update_user_name,
l_notes,'',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'||SUBSTR(SQLERRM,1,100);
END;
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:='CANCEL';
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');
l_request_id := fnd_request.submit_request
(application => 'XXFTC',
program => 'XXFTCALRTMAIL',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => l_freight_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 +
--+------------------------------------------------------------+
/* 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 Reduced due to Cancel an order#'||l_current_order||'-'||'With Cancel Reason :'||l_cancel_reason||'-'||l_instance_name
,ARGUMENT4 => 'RELEASE_TLD_HOLD'||v_date
);
COMMIT;
END;
END IF; --IF l_total_weight >0 THEN
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 IF; --IF l_status_code='CANCELLED' THEN
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_RLS_HOLD;
PROCEDURE XXFTC_OM_APL_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_APL_HLD;
END XXFTC_OM_ORDER_RLS_HOLDS;
/
PACKAGE XXFTC_OM_ORDER_RLS_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_RLS_HOLD (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out varchar2
);
PROCEDURE XXFTC_OM_APL_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
END XXFTC_OM_ORDER_RLS_HOLDS;
/
create or replace
PACKAGE BODY XXFTC_OM_ORDER_RLS_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_rls_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_rls_weight;
--+----------------------------------------------------------+
--+ Main Procedure used to apply the hold for an order +
--+----------------------------------------------------------+
PROCEDURE XXFTC_ORDER_RLS_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_CAN_LINE_ID NUMBER;
L_STATUS_CODE VARCHAR2(50);
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_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_request_date varchar2(30);
l_current_order NUMBER;
l_cancel_reason VARCHAR2(1000);
l_book_user NUMBER;
l_user_email VARCHAR2(500);
l_send_email VARCHAR2(1000);
l_ship_thresold varchar2(200);
l_update_user_name varchar2(100);
l_instance_name varchar2(100);
l_notes varchar2(1000);
l_report_type varchar2(50);
l_can_line_num number;
l_list_price number;
l_selling_price number;
l_diff number;
l_percentage number;
l_request1 BOOLEAN;
v_template varchar2(30);
l_CANCELLED_FLAG varchar2(10);
l_crd_oe_hold_id number;
l_crd_oe_hold_sts varchar2(100);
v_date varchar2(20):=to_char(sysdate,'MMDDYY');
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_cancel_header_id number;
l_line_count number;
l_item_number varchar2(100);
l_line_number varchar2(50);
l_cust_item_number VARCHAR2(200);
l_order_tbl Oe_Holds_Pvt.order_tbl_type;
l_hold_source_rec OE_HOLDS_PVT.HOLD_SOURCE_REC_TYPE;
--+-----------------------------------------------------------------------------------------------------------------------+
--+ 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');
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_can_line_id:=to_number(itemkey);
BEGIN
SELECT flow_status_code,line_number
INTO l_status_code,l_can_line_num
FROM oe_order_lines
WHERE line_id=l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_status_code:='';
l_error_message:='l_truckload_limit is null for an order header id';
WHEN OTHERS THEN
l_error_message:='others error for status_code'||SUBSTR(SQLERRM,1,100);
END;
IF l_status_code='CANCELLED' THEN
--+-----------------------------------------------------------------------------+
--+ Get the Customer id,ship to location is and warehouse against current order + +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT SOLD_TO_ORG_ID,SHIP_TO_ORG_ID,SHIP_FROM_ORG_ID
INTO l_cust_id,l_ship_to_id,l_warehouse
FROM oe_order_lines
WHERE line_id=l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_cust_id:=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 party id +
--+-----------------------------------------------------------------------------+
BEGIN
SELECT hp.party_id
INTO l_party_id
FROM hz_parties hp, hz_cust_accounts hc
WHERE hc.cust_account_id = l_cust_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 = l_ship_to_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,oe_order_lines oel
WHERE 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')
AND oeh.header_id=oel.header_id
AND oel.line_id=l_can_line_id;
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';
WHEN OTHERS THEN
l_error_message:='others error for 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_freight_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;
ELSE
-- l_send_email:='mmanier@falkentire.com';
NULL;
END IF;
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the eligible order lines +
--+-----------------------------------------------------------------------------+
l_total_weight:=0;
FOR r_eligible_lines IN c_eligible_lines(l_cust_id,l_ship_to_id,l_warehouse)
LOOP
l_debug_point:='050';
l_line_weight:=open_orders_rls_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 reduced to truck load weight or not +
--+-----------------------------------------------------------------------------+
IF l_total_weight<l_truckload_limit THEN
IF l_total_weight >0 THEN
l_debug_point:='0100';
--+-----------------------------------------------------------------------------+
--+ Open the loop to get the all eligible open orders +
--+-----------------------------------------------------------------------------+
/* FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)
LOOP
--+-----------------------------------------------------------------------------+
--+ Get the hold id for an order +
--+-----------------------------------------------------------------------------+
l_debug_point:='0110';
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'||SUBSTR(SQLERRM,1,100);
END;
--+-----------------------------------------------------------------------------+
--+ Check the hold is there or not for eligible order +
--+-----------------------------------------------------------------------------+
IF l_oe_hold_id IS NULL THEN
l_debug_point:='0120';
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'||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_freight_header_id;
EXCEPTION
WHEN OTHERS THEN
l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);
END;
--+-----------------------------------------------------------------------------+
--+ Call the API 'Oe_Holds_Pub.apply_holds' to apply hold to order +
--+-----------------------------------------------------------------------------+
XXFTC_OM_APL_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; ----FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)
*/
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 = l_can_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';
WHEN OTHERS THEN
l_error_message:='others error for order l_party_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 = l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_order_weight := 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 reason
INTO l_cancel_reason
FROM oe_reasons_v
WHERE entity_id=l_can_line_id
AND entity_code='LINE';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_cancel_reason:='';
l_error_message:='l_party_id is null for an order header id';
WHEN OTHERS THEN
l_error_message:='others error for order l_party_id'||SUBSTR(SQLERRM,1,100);
END;
BEGIN
SELECT fnd.user_name,oeh.attribute3
INTO l_update_user_name,l_notes
FROM oe_order_headers oeh,
fnd_user fnd
WHERE oeh.header_id=l_freight_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;
--+-----------------------------------------------------+
--+ 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 = l_freight_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 = l_freight_header_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_shipping_instructions := '';
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 = l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_reservation_quantity:=0;
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= l_can_line_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_ORDERED_QUANTITY:=0;
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;
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 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=l_can_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 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=l_can_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;
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT
INTO xxftc_order_header_hold_tbl
VALUES(l_freight_header_id,to_char(l_current_order),l_line_number,l_freight_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_update_user_name,
l_notes,'',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'||SUBSTR(SQLERRM,1,100);
END;
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:='CANCEL';
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');
l_request_id := fnd_request.submit_request
(application => 'XXFTC',
program => 'XXFTCALRTMAIL',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => l_freight_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 +
--+------------------------------------------------------------+
/* 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 Reduced due to Cancel an order#'||l_current_order||'-'||'With Cancel Reason :'||l_cancel_reason||'-'||l_instance_name
,ARGUMENT4 => 'RELEASE_TLD_HOLD'||v_date
);
COMMIT;
END;
END IF; --IF l_total_weight >0 THEN
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 IF; --IF l_status_code='CANCELLED' THEN
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_RLS_HOLD;
PROCEDURE XXFTC_OM_APL_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_APL_HLD;
END XXFTC_OM_ORDER_RLS_HOLDS;
/
No comments:
Post a Comment