create or replace
PACKAGE XXFTC_SHIPPING_ORDER_HOLDS AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_SHIPPING_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_SHIPPING_HOLD_PROC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_del_trip_id IN NUMBER,
p_del_org_id IN NUMBER,
p_del_ship_method IN VARCHAR2,
p_del_freight_term IN VARCHAR2);
PROCEDURE XXFTC_SHIP_OM_APPLY_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
END XXFTC_SHIPPING_ORDER_HOLDS;
/
create or replace
PACKAGE BODY XXFTC_SHIPPING_ORDER_HOLDS
AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_SHIPPING_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 xxftc_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 xxftc_open_orders_weight;
--+----------------------------------------------------------+
--+ Main Procedure used to apply the hold for an order +
--+----------------------------------------------------------+
PROCEDURE XXFTC_SHIPPING_HOLD_PROC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_del_trip_id IN NUMBER,
p_del_org_id IN NUMBER,
p_del_ship_method IN VARCHAR2,
p_del_freight_term IN 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_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_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_del_ship_method varchar2(200);
L_SEND_EMAIL varchar2(2000);
l_ship_thresold varchar2(200);
l_update_user_name varchar2(100);
l_instance_name varchar2(100);
l_report_type varchar2(50);
l_ord_id number;
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_ord_head_id number;
l_ORDERED_QUANTITY NUMBER;
l_reservation_quantity NUMBER;
l_shipping_instructions VARCHAR2(1000);
l_cust_item_number VARCHAR2(200);
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);
CURSOR c_trip_del IS
SELECT wdl.delivery_id
FROM wsh_trip_stops wt,
wsh_delivery_legs wdl
WHERE wdl.pick_up_stop_id=wt.stop_id
AND wt.trip_id=p_del_trip_id;
CURSOR c_ord_cust(p_del_id IN NUMBER) IS
SELECT distinct oeh.sold_to_org_id,oeh.ship_to_org_id,oeh.ship_from_org_id,oeh.header_id,oeh.order_number
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_delivery_details wdd1,
oe_order_headers_all oeh
WHERE wda.delivery_id=p_del_id
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wdd.source_header_id=wdd1.source_header_id
AND wdd1.released_status in('R','B')
AND oeh.header_id=wdd1.source_header_id
AND wdd.organization_id=p_del_org_id
AND NOT EXISTS (SELECT oel.header_id --If any 'Released to warehouse' line exist in a order then no need to apply hold
FROM oe_order_lines_all oel,
wsh_delivery_details wsh
WHERE wsh.source_line_id=oel.line_id
AND oel.header_id=oeh.header_id
AND wsh.released_status in('S'));
/* AND NOT EXISTS (SELECT wt.trip_id --If any other open trip exist for an order then no need to apply hold
FROM oe_order_lines_all oel1,
wsh_delivery_details wsh1,
wsh_trip_stops wt,
wsh_delivery_legs wdl,
wsh_delivery_assignments wda1,
wsh_delivery_details wsh2
WHERE wsh1.source_line_id=oel1.line_id
AND oel1.header_id=oeh.header_id
AND wdl.pick_up_stop_id=wt.stop_id
AND wt.trip_id<>p_del_trip_id
AND wda1.delivery_id=wdl.delivery_id
AND wda1.delivery_detail_id=wsh1.delivery_detail_id
AND wsh1.source_header_id=wsh2.source_header_id
AND wsh2.released_status in('S'));*/
--+-----------------------------------------------------------------------------------------------------------------------+
--+ 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
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_back_ord_lines(p_header_id IN NUMBER,
p_ship_to_org_id IN NUMBER,
p_ship_from_org_id IN NUMBER,
p_delivery_id IN NUMBER) IS
SELECT distinct oel.sold_to_org_id,oel.ship_to_org_id,oel.ship_from_org_id,oeh.header_id,oeh.order_number,oel.line_id,oel.line_number
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_delivery_details wdd1,
oe_order_headers_all oeh,
oe_order_lines_all oel
WHERE wda.delivery_id=p_delivery_id
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wdd.source_header_id=wdd1.source_header_id
AND wdd1.released_status in('R','B')
AND oeh.header_id=wdd1.source_header_id
and oel.line_id=wdd1.source_line_id
and oeh.header_id=oel.header_id
AND oel.ship_from_org_id=p_ship_from_org_id
AND oel.ship_to_org_id=p_ship_to_org_id
AND oeh.header_id=p_header_id;
BEGIN
--+---------------------------+
--+ Set the org id +
--+---------------------------+
mo_global.set_policy_context ('S', l_org);
l_debug_point := '010';
l_error_message := '';
-- l_freight_header_id := NULL;
-- l_order_header_id := p_order_header_id;
--+---------------------------------------------------------------------+
--+ Get the ship method id to check freight/ship method combination +
--+---------------------------------------------------------------------+
BEGIN
SELECT fl.lookup_code
INTO l_del_ship_method
FROM fnd_lookup_values fl,
wsh_carrier_services wc
WHERE wc.ship_method_code = p_del_ship_method
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.meaning = p_del_freight_term
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_del_ship_method := '';
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;
IF l_del_ship_method IS NOT NULL THEN
FOR r_trip_del IN c_trip_del
LOOP
l_truckload_limit := '';
FOR r_ord_cust IN c_ord_cust(r_trip_del.delivery_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_ord_cust.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_ord_cust.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';
l_debug_point := '030';
l_total_weight := 0;
FOR r_eligible_lines IN c_eligible_lines (r_ord_cust.sold_to_org_id,
r_ord_cust.ship_to_org_id,
r_ord_cust.ship_from_org_id
)
LOOP
l_debug_point := '050';
l_line_weight := xxftc_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;
FOR r_back_ord_lines IN c_back_ord_lines(r_ord_cust.header_id,
r_ord_cust.ship_to_org_id,
r_ord_cust.ship_from_org_id,
r_trip_del.delivery_id) LOOP
l_debug_point := '0100';
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_back_ord_lines.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 = r_back_ord_lines.line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_order_weight:=0;
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
INTO l_update_user_name
FROM oe_order_headers oeh,
fnd_user fnd
WHERE oeh.header_id=r_ord_cust.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 = r_back_ord_lines.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_back_ord_lines.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 = r_back_ord_lines.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= r_back_ord_lines.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;
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_back_ord_lines.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=r_back_ord_lines.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;
l_diff:=l_list_price-l_selling_price;
l_percentage:=l_diff/l_list_price*100;
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT
INTO xxftc_order_header_hold_tbl
VALUES(r_trip_del.delivery_id,to_char(r_back_ord_lines.order_number),l_line_number,r_back_ord_lines.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),
'On 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'||SUBSTR(SQLERRM,1,100);
END;
--+-----------------------------------------------------------------------------+
--+ 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_back_ord_lines.header_id
AND ohl.line_id=r_back_ord_lines.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;
--+-----------------------------------------------------------------------------+
--+ 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'
|| 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_ord_cust.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_SHIP_OM_APPLY_HLD (p_hold_id => l_hold_id,
p_header_id => r_back_ord_lines.header_id,
p_line_id => r_back_ord_lines.line_id
);
END IF; --IF l_oe_hold_id IS NULL THEN
BEGIN
SELECT ORDER_HEADER_ID
INTO l_ord_head_id
FROM xxftc_order_header_hold_tbl
WHERE ORDER_HEADER_ID=r_back_ord_lines.header_id
AND LINE_NUMBER=r_back_ord_lines.line_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ord_head_id:=null;
WHEN TOO_MANY_ROWS THEN
DELETE FROM xxftc_order_header_hold_tbl
WHERE ORDER_HEADER_ID=r_back_ord_lines.header_id
AND LINE_NUMBER=r_back_ord_lines.line_number
AND rownum=1;
END;
END LOOP; --FOR r_back_oord_lines IN c_back_ord_lines
BEGIN
SELECT CURRENT_ORDER_ID
INTO l_ord_id
FROM xxftc_order_header_hold_tbl
WHERE CURRENT_ORDER_ID=r_trip_del.delivery_id
AND rownum=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ord_id:=null;
END;
IF l_ord_id IS NOT NULL 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:='BACK ORDER';
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 => r_trip_del.delivery_id,
argument2 => l_report_type);
--+------------------------------------------------------------+
--+submit the request to send the mail through shell program +
--+------------------------------------------------------------+
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;
/* SELECT to_char(sysdate+3/60/24,'DD-MON-YYYY HH24:MI:SS') INTO l_request_date FROM dual;*/
--l_send_email:='mmanier@falkentire.com'||','||'barnold@osius.com';
l_send_email:='';
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 => 'TLD HOLD applied due to back order'||'-'||l_instance_name
,ARGUMENT4 => 'BACK_ORDER'||v_date
);
COMMIT;
END;
END IF; -- IF l_ord_id IS NOT NULL THEN
END IF; --IF l_truckload_limit IS NOT NULL THEN
END LOOP; --FOR r_ord_cust IN c_ord_cust(r_trip_del.delivery_id)
END LOOP; --FOR r_trip_del IN c_trip_del
END IF; --IF l_del_ship_method IS NOT NULL THEN
-- DELETE FROM xxftc_order_header_hold_tbl WHERE current_order_id=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_SHIPPING_HOLD_PROC;
PROCEDURE XXFTC_SHIP_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_SHIP_OM_APPLY_HLD;
END XXFTC_SHIPPING_ORDER_HOLDS;
PACKAGE XXFTC_SHIPPING_ORDER_HOLDS AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_SHIPPING_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_SHIPPING_HOLD_PROC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_del_trip_id IN NUMBER,
p_del_org_id IN NUMBER,
p_del_ship_method IN VARCHAR2,
p_del_freight_term IN VARCHAR2);
PROCEDURE XXFTC_SHIP_OM_APPLY_HLD(p_hold_id IN NUMBER,
p_header_id IN NUMBER,
p_line_id IN NUMBER);
END XXFTC_SHIPPING_ORDER_HOLDS;
/
create or replace
PACKAGE BODY XXFTC_SHIPPING_ORDER_HOLDS
AS
/************************************************************************************************/
/*** FILE NAME : XXFTC_SHIPPING_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 xxftc_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 xxftc_open_orders_weight;
--+----------------------------------------------------------+
--+ Main Procedure used to apply the hold for an order +
--+----------------------------------------------------------+
PROCEDURE XXFTC_SHIPPING_HOLD_PROC(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_del_trip_id IN NUMBER,
p_del_org_id IN NUMBER,
p_del_ship_method IN VARCHAR2,
p_del_freight_term IN 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_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_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_del_ship_method varchar2(200);
L_SEND_EMAIL varchar2(2000);
l_ship_thresold varchar2(200);
l_update_user_name varchar2(100);
l_instance_name varchar2(100);
l_report_type varchar2(50);
l_ord_id number;
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_ord_head_id number;
l_ORDERED_QUANTITY NUMBER;
l_reservation_quantity NUMBER;
l_shipping_instructions VARCHAR2(1000);
l_cust_item_number VARCHAR2(200);
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);
CURSOR c_trip_del IS
SELECT wdl.delivery_id
FROM wsh_trip_stops wt,
wsh_delivery_legs wdl
WHERE wdl.pick_up_stop_id=wt.stop_id
AND wt.trip_id=p_del_trip_id;
CURSOR c_ord_cust(p_del_id IN NUMBER) IS
SELECT distinct oeh.sold_to_org_id,oeh.ship_to_org_id,oeh.ship_from_org_id,oeh.header_id,oeh.order_number
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_delivery_details wdd1,
oe_order_headers_all oeh
WHERE wda.delivery_id=p_del_id
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wdd.source_header_id=wdd1.source_header_id
AND wdd1.released_status in('R','B')
AND oeh.header_id=wdd1.source_header_id
AND wdd.organization_id=p_del_org_id
AND NOT EXISTS (SELECT oel.header_id --If any 'Released to warehouse' line exist in a order then no need to apply hold
FROM oe_order_lines_all oel,
wsh_delivery_details wsh
WHERE wsh.source_line_id=oel.line_id
AND oel.header_id=oeh.header_id
AND wsh.released_status in('S'));
/* AND NOT EXISTS (SELECT wt.trip_id --If any other open trip exist for an order then no need to apply hold
FROM oe_order_lines_all oel1,
wsh_delivery_details wsh1,
wsh_trip_stops wt,
wsh_delivery_legs wdl,
wsh_delivery_assignments wda1,
wsh_delivery_details wsh2
WHERE wsh1.source_line_id=oel1.line_id
AND oel1.header_id=oeh.header_id
AND wdl.pick_up_stop_id=wt.stop_id
AND wt.trip_id<>p_del_trip_id
AND wda1.delivery_id=wdl.delivery_id
AND wda1.delivery_detail_id=wsh1.delivery_detail_id
AND wsh1.source_header_id=wsh2.source_header_id
AND wsh2.released_status in('S'));*/
--+-----------------------------------------------------------------------------------------------------------------------+
--+ 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
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_back_ord_lines(p_header_id IN NUMBER,
p_ship_to_org_id IN NUMBER,
p_ship_from_org_id IN NUMBER,
p_delivery_id IN NUMBER) IS
SELECT distinct oel.sold_to_org_id,oel.ship_to_org_id,oel.ship_from_org_id,oeh.header_id,oeh.order_number,oel.line_id,oel.line_number
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_delivery_details wdd1,
oe_order_headers_all oeh,
oe_order_lines_all oel
WHERE wda.delivery_id=p_delivery_id
AND wdd.delivery_detail_id=wda.delivery_detail_id
AND wdd.source_header_id=wdd1.source_header_id
AND wdd1.released_status in('R','B')
AND oeh.header_id=wdd1.source_header_id
and oel.line_id=wdd1.source_line_id
and oeh.header_id=oel.header_id
AND oel.ship_from_org_id=p_ship_from_org_id
AND oel.ship_to_org_id=p_ship_to_org_id
AND oeh.header_id=p_header_id;
BEGIN
--+---------------------------+
--+ Set the org id +
--+---------------------------+
mo_global.set_policy_context ('S', l_org);
l_debug_point := '010';
l_error_message := '';
-- l_freight_header_id := NULL;
-- l_order_header_id := p_order_header_id;
--+---------------------------------------------------------------------+
--+ Get the ship method id to check freight/ship method combination +
--+---------------------------------------------------------------------+
BEGIN
SELECT fl.lookup_code
INTO l_del_ship_method
FROM fnd_lookup_values fl,
wsh_carrier_services wc
WHERE wc.ship_method_code = p_del_ship_method
AND fl.lookup_type = 'FREIGHT_TERMS'
AND fl.meaning = p_del_freight_term
AND fl.attribute1 || '-' || wc.attribute3 NOT IN
('Y-Y', 'Y-N', 'N-Y');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_del_ship_method := '';
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;
IF l_del_ship_method IS NOT NULL THEN
FOR r_trip_del IN c_trip_del
LOOP
l_truckload_limit := '';
FOR r_ord_cust IN c_ord_cust(r_trip_del.delivery_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_ord_cust.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_ord_cust.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';
l_debug_point := '030';
l_total_weight := 0;
FOR r_eligible_lines IN c_eligible_lines (r_ord_cust.sold_to_org_id,
r_ord_cust.ship_to_org_id,
r_ord_cust.ship_from_org_id
)
LOOP
l_debug_point := '050';
l_line_weight := xxftc_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;
FOR r_back_ord_lines IN c_back_ord_lines(r_ord_cust.header_id,
r_ord_cust.ship_to_org_id,
r_ord_cust.ship_from_org_id,
r_trip_del.delivery_id) LOOP
l_debug_point := '0100';
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_back_ord_lines.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 = r_back_ord_lines.line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_order_weight:=0;
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
INTO l_update_user_name
FROM oe_order_headers oeh,
fnd_user fnd
WHERE oeh.header_id=r_ord_cust.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 = r_back_ord_lines.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_back_ord_lines.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 = r_back_ord_lines.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= r_back_ord_lines.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;
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_back_ord_lines.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=r_back_ord_lines.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;
l_diff:=l_list_price-l_selling_price;
l_percentage:=l_diff/l_list_price*100;
--+-----------------------------------------------------------------------------+
--+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert +
--+-----------------------------------------------------------------------------+
BEGIN
INSERT
INTO xxftc_order_header_hold_tbl
VALUES(r_trip_del.delivery_id,to_char(r_back_ord_lines.order_number),l_line_number,r_back_ord_lines.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),
'On 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'||SUBSTR(SQLERRM,1,100);
END;
--+-----------------------------------------------------------------------------+
--+ 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_back_ord_lines.header_id
AND ohl.line_id=r_back_ord_lines.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;
--+-----------------------------------------------------------------------------+
--+ 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'
|| 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_ord_cust.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_SHIP_OM_APPLY_HLD (p_hold_id => l_hold_id,
p_header_id => r_back_ord_lines.header_id,
p_line_id => r_back_ord_lines.line_id
);
END IF; --IF l_oe_hold_id IS NULL THEN
BEGIN
SELECT ORDER_HEADER_ID
INTO l_ord_head_id
FROM xxftc_order_header_hold_tbl
WHERE ORDER_HEADER_ID=r_back_ord_lines.header_id
AND LINE_NUMBER=r_back_ord_lines.line_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ord_head_id:=null;
WHEN TOO_MANY_ROWS THEN
DELETE FROM xxftc_order_header_hold_tbl
WHERE ORDER_HEADER_ID=r_back_ord_lines.header_id
AND LINE_NUMBER=r_back_ord_lines.line_number
AND rownum=1;
END;
END LOOP; --FOR r_back_oord_lines IN c_back_ord_lines
BEGIN
SELECT CURRENT_ORDER_ID
INTO l_ord_id
FROM xxftc_order_header_hold_tbl
WHERE CURRENT_ORDER_ID=r_trip_del.delivery_id
AND rownum=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ord_id:=null;
END;
IF l_ord_id IS NOT NULL 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:='BACK ORDER';
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 => r_trip_del.delivery_id,
argument2 => l_report_type);
--+------------------------------------------------------------+
--+submit the request to send the mail through shell program +
--+------------------------------------------------------------+
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;
/* SELECT to_char(sysdate+3/60/24,'DD-MON-YYYY HH24:MI:SS') INTO l_request_date FROM dual;*/
--l_send_email:='mmanier@falkentire.com'||','||'barnold@osius.com';
l_send_email:='';
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 => 'TLD HOLD applied due to back order'||'-'||l_instance_name
,ARGUMENT4 => 'BACK_ORDER'||v_date
);
COMMIT;
END;
END IF; -- IF l_ord_id IS NOT NULL THEN
END IF; --IF l_truckload_limit IS NOT NULL THEN
END LOOP; --FOR r_ord_cust IN c_ord_cust(r_trip_del.delivery_id)
END LOOP; --FOR r_trip_del IN c_trip_del
END IF; --IF l_del_ship_method IS NOT NULL THEN
-- DELETE FROM xxftc_order_header_hold_tbl WHERE current_order_id=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_SHIPPING_HOLD_PROC;
PROCEDURE XXFTC_SHIP_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_SHIP_OM_APPLY_HLD;
END XXFTC_SHIPPING_ORDER_HOLDS;
No comments:
Post a Comment