Cancel Back orders

1. Code for Cancel Back orders [OM] with the help of standerd API(Ineer loop Cursor).
   - Cancel partial quntity and back order foe specific org.

CREATE

OR REPLACE PACKAGE BODY APPS.xxwms_oe_backorder

AS

PROCEDURE kvwms_cancel_orders (

errbuf_out
OUT VARCHAR2,

retcode_out
OUT VARCHAR2,

p_order_number
IN VARCHAR2,

p_org_id
IN NUMBER,

p_email_id
IN VARCHAR2
 
 

)

AS

v_qty

NUMBER DEFAULT 0;

l_user_id

NUMBER;

l_resp_id

NUMBER;

l_appl_id

NUMBER;

l_return_status

VARCHAR2 (1000);

 

--INPUT VARIABLES FOR PROCESS_ORDER API

l_header_rec_in

oe_order_pub.header_rec_type;

l_line_tbl_in

oe_order_pub.line_tbl_type;

 

 

--l_header_rec1:=OE_ORDER_PUB.G_MISS_HEADER_REC;

 

-- t_line_tbl1(1) := OE_ORDER_PUB.G_MISS_LINE_REC;

 

--l_msg_data VARCHAR2 (1000);

l_action_request_tbl_in

oe_order_pub.request_tbl_type;

 

--OUTPUT VARIABLES FOR PROCESS_ORDER API

l_header_rec_out

oe_order_pub.header_rec_type;

l_line_tbl_out

oe_order_pub.line_tbl_type;

l_header_val_rec_out

oe_order_pub.header_val_rec_type;

l_header_adj_tbl_out

oe_order_pub.header_adj_tbl_type;

l_header_adj_val_tbl_out

oe_order_pub.header_adj_val_tbl_type;

l_header_price_att_tbl_out

oe_order_pub.header_price_att_tbl_type;

l_header_adj_att_tbl_out

oe_order_pub.header_adj_att_tbl_type;

l_header_adj_assoc_tbl_out

oe_order_pub.header_adj_assoc_tbl_type;

l_header_scredit_tbl_out

oe_order_pub.header_scredit_tbl_type;

l_header_scredit_val_tbl_out

oe_order_pub.header_scredit_val_tbl_type;

l_line_val_tbl_out

oe_order_pub.line_val_tbl_type;

l_line_adj_tbl_out

oe_order_pub.line_adj_tbl_type;

l_line_adj_val_tbl_out

oe_order_pub.line_adj_val_tbl_type;

l_line_price_att_tbl_out

oe_order_pub.line_price_att_tbl_type;

l_line_adj_att_tbl_out

oe_order_pub.line_adj_att_tbl_type;

l_line_adj_assoc_tbl_out

oe_order_pub.line_adj_assoc_tbl_type;

l_line_scredit_tbl_out

oe_order_pub.line_scredit_tbl_type;

l_line_scredit_val_tbl_out

oe_order_pub.line_scredit_val_tbl_type;

l_lot_serial_tbl_out

oe_order_pub.lot_serial_tbl_type;

l_lot_serial_val_tbl_out

oe_order_pub.lot_serial_val_tbl_type;

l_action_request_tbl_out

oe_order_pub.request_tbl_type;

l_msg_index_out

NUMBER (10);

l_chr_program_unit_name

VARCHAR2 (100);

l_ret_status

VARCHAR2 (1000) := NULL;

l_msg_count

NUMBER := 0;

l_msg_data

VARCHAR2 (20000);

l_api_version

NUMBER := 1.0;

v_audit

NUMBER;

v_out_file_name

VARCHAR2 (400);

v_msg_text

VARCHAR2 (4000);

v_send_email

NUMBER;

v_error_msg

VARCHAR2 (4000);

 

CURSOR get_order_details (p_order_number IN VARCHAR2, p_org_id IN NUMBER

 

 

-- ,p_customer_number IN VARCHAR2

)

 

 

IS

 

SELECT DISTINCT oh.order_number order_number, ol.line_number,

ol

.line_id, wdd.released_status, hp.party_name,

hp

.party_number, wdd.organization_id

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND wdd.released_status in ('B','S') --changed added 'S' Status

AND oh.booked_flag = 'Y'

AND oh.order_number =

NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.source_line_id = ol.line_id

AND wdf.released_status NOT IN ('B', 'R', 'D')

--Added logic for Ready to release status

)

       

--Added logic for Ready to release status ie. wdf.RELEASED_STATUS = 'R'

 

UNION

 

SELECT DISTINCT oh.order_number order_number, ol.line_number,

ol

.line_id, wdd.released_status, hp.party_name,

hp

.party_number, wdd.organization_id

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND wdd.released_status = 'R'

AND oh.booked_flag = 'Y'

AND oh.order_number =

NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.released_status NOT IN ('B', 'R', 'D'));

CURSOR b (p_1 IN VARCHAR2, p_2 IN NUMBER)

IS

SELECT ol.line_id, oh.order_number

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

,

wsh_delivery_assignments wds

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND wds.delivery_detail_id = wdd.delivery_detail_id

AND ol.ship_tolerance_below = 100

AND released_status NOT IN ('R', 'S')

AND wdd.released_status = 'B'

AND oh.booked_flag = 'Y'

AND oh.order_number = p_1

-- i.order_number --10060173

-- NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_2

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.source_line_id = ol.line_id

AND wdf.released_status NOT IN ('B', 'R', 'D')

--Added logic for Ready to release status

)

--Aded logic for Ready to release status ie. wdf.RELEASED_STATUS = 'R'

UNION

SELECT ol.line_id, oh.order_number

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

,

wsh_delivery_assignments wds

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND wds.delivery_detail_id = wdd.delivery_detail_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND released_status NOT IN ('R', 'S')

--AND wdd.released_status = 'R'

AND oh.booked_flag = 'Y'

AND oh.order_number = p_1 --10060173

--NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_2 --p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.released_status NOT IN ('B', 'R', 'D'));

BEGIN

DBMS_OUTPUT.put_line

('start 1');

/* SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = 'RPADHI'; */

SELECT fcr.requested_by user_id

INTO l_user_id

--,fcr.responsibility_id,

--fcr.responsibility_application_id

FROM apps.fnd_concurrent_requests fcr

WHERE request_id = fnd_global.conc_request_id;

fnd_file.put_line (fnd_file.LOG, 'User ID : ' || l_user_id);

v_out_file_name

:= 'o' || fnd_global.conc_request_id || '.out';

SELECT responsibility_id, application_id

INTO l_resp_id, l_appl_id

FROM fnd_responsibility_vl

WHERE responsibility_name LIKE 'KV OM Inquiry';

--added responsibility name on 9-may by kamal

-- INITIALIZE ENVIRONMENT

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

fnd_file.put_line (fnd_file.LOG, 'Responsibility ID : ' || l_resp_id);

fnd_file.put_line (fnd_file.LOG, 'Application ID : ' || l_appl_id);

--v_msg_text := 'Order_number | Line_number | Line_id| Status | Message ';

--DBMS_OUTPUT.put_line ('User_id'||l_user_id);

v_msg_text

:=

RPAD ('Order_number', 15)

||

'|'

||

RPAD ('Line_number', 12)

||

'|'

||

RPAD ('Line_id', 10)

||

'|'

||

RPAD ('Status', 7)

||

'|'

||

'Message';

v_send_email

:= 0;

fnd_file.put_line (fnd_file.output, v_msg_text);

DBMS_OUTPUT.put_line

(v_msg_text);

FOR i IN get_order_details (p_order_number, p_org_id)

LOOP

--**************************************************************************************************************************

BEGIN

SELECT NVL (SUM (requested_quantity), 0)

INTO v_qty

FROM wsh_delivery_details wdd

WHERE wdd.released_status IN ('S', 'Y', 'C', 'I', 'L', 'N')

AND wdd.source_line_id = i.line_id

AND wdd.source_code = 'OE'; --added on 15 may

--DBMS_OUTPUT.put_line ('Stage2');

fnd_file.put_line (1, 'Ordered Quantity ' || '-' || v_qty);

EXCEPTION

WHEN OTHERS

THEN

v_qty

:= 0;

fnd_file.put_line (1, 'Quantity not Available' || SQLERRM);

END;

-- INITIALIZE LINE RECORD

l_line_tbl_in

(1) := oe_order_pub.g_miss_line_rec;

l_line_tbl_in

(1).ordered_quantity := v_qty;

IF v_qty = 0

THEN

l_line_tbl_in

(1).flow_status_code := 'CANCELLED';

ELSE

l_line_tbl_in

(1).flow_status_code := 'AWAITING_SHIPPING';

END IF;

/********** inner for loop starts here **********/

FOR j IN b (i.order_number, i.organization_id)

LOOP

BEGIN

BEGIN

SELECT NVL (SUM (requested_quantity), 0)

INTO v_qty

FROM wsh_delivery_details wdd

WHERE wdd.released_status IN

('S', 'Y', 'C', 'I', 'L', 'N')

AND wdd.source_line_id = j.line_id

AND wdd.source_code = 'OE'; --added on 15 may

fnd_file.put_line (1, 'Ordered Quantity ' || '-' || v_qty);

EXCEPTION

WHEN OTHERS

THEN

v_qty

:= 0;

fnd_file.put_line (1,

'Quantity not Available' || SQLERRM

);

END;

-- INITIALIZE LINE RECORD

l_line_tbl_in

(1) := oe_order_pub.g_miss_line_rec;

l_line_tbl_in

(1).ordered_quantity := v_qty;

IF v_qty = 0

THEN

l_line_tbl_in

(1).flow_status_code := 'CANCELLED';

ELSE

l_line_tbl_in

(1).flow_status_code := 'AWAITING_SHIPPING';

END IF;

l_line_tbl_in

(1).line_id := j.line_id;

-- l_line_tbl_in (1).FLOW_STATUS_CODE := 'CANCELLED';

l_line_tbl_in

(1).change_reason := 'KVBOCAN';

--Administrative Reason

l_line_tbl_in

(1).change_comments := 'Cancel Order Line';

l_line_tbl_in

(1).operation := oe_globals.g_opr_update;

-- Mandatory Operation to Pass

oe_msg_pub.delete_msg;

oe_order_pub

.process_order

(p_api_version_number => l_api_version,

p_init_msg_list

=> fnd_api.g_false,

p_return_values

=> fnd_api.g_false,

p_action_commit

=> fnd_api.g_false,

p_line_tbl

=> l_line_tbl_in,

x_header_rec

=> l_header_rec_out,

x_header_val_rec

=> l_header_val_rec_out,

x_header_adj_tbl

=> l_header_adj_tbl_out,

x_header_adj_val_tbl

=> l_header_adj_val_tbl_out,

x_header_price_att_tbl

=> l_header_price_att_tbl_out,

x_header_adj_att_tbl

=> l_header_adj_att_tbl_out,

x_header_adj_assoc_tbl

=> l_header_adj_assoc_tbl_out,

x_header_scredit_tbl

=> l_header_scredit_tbl_out,

x_header_scredit_val_tbl

=> l_header_scredit_val_tbl_out,

x_line_tbl

=> l_line_tbl_out,

x_line_val_tbl

=> l_line_val_tbl_out,

x_line_adj_tbl

=> l_line_adj_tbl_out,

x_line_adj_val_tbl

=> l_line_adj_val_tbl_out,

x_line_price_att_tbl

=> l_line_price_att_tbl_out,

x_line_adj_att_tbl

=> l_line_adj_att_tbl_out,

x_line_adj_assoc_tbl

=> l_line_adj_assoc_tbl_out,

x_line_scredit_tbl

=> l_line_scredit_tbl_out,

x_line_scredit_val_tbl

=> l_line_scredit_val_tbl_out,

x_lot_serial_tbl

=> l_lot_serial_tbl_out,

x_lot_serial_val_tbl

=> l_lot_serial_val_tbl_out,

x_action_request_tbl

=> l_action_request_tbl_out,

x_return_status

=> l_ret_status,

x_msg_count

=> l_msg_count,

x_msg_data

=> l_msg_data

);

l_msg_data

:= NULL;

v_error_msg

:= NULL;

IF l_ret_status <> 'S'

THEN

FOR iindx IN 1 .. l_msg_count

LOOP

-- l_msg_data := l_msg_data || ' ' || oe_msg_pub.get (iindx);

oe_msg_pub.get (p_msg_index => iindx,

p_encoded

=> fnd_api.g_false,

p_data

=> l_msg_data,

p_msg_index_out

=> l_msg_index_out

);

v_error_msg

:= l_msg_data || ' ' || l_msg_data;

fnd_file.put_line (fnd_file.LOG, 'in error loop ');

-- DBMS_OUTPUT.put_line ('in error loop ');

COMMIT;

END LOOP;

fnd_file.put_line (fnd_file.LOG,

'***** Backorder Failed *****'

);

--DBMS_OUTPUT.put_line ('Backorder Failed ');

fnd_file.put_line (fnd_file.LOG,

'Sales Order => ' || j.order_number

);

--DBMS_OUTPUT.put_line ('Sales Order => ' || j.order_number);

-- fnd_file.put_line (fnd_file.LOG,

-- 'Line Number => ' || j.line_number

-- );

fnd_file.put_line (fnd_file.LOG,

'Line ID=> '

|| j

.line_id

||

' Line Cancelation Failed'

);

--DBMS_OUTPUT.put_line ('Line ID=> ' || j.line_id);

--DBMS_OUTPUT.put_line ('Return Status: ' || l_ret_status);

fnd_file.put_line (fnd_file.LOG,

'Return Status: ' || l_ret_status

);

v_send_email

:= 1;

v_error_msg

:= REPLACE (v_error_msg, CHR (13), '-');

v_error_msg

:= REPLACE (v_error_msg, CHR (10), ' ');

v_msg_text

:=

RPAD (j.order_number, 15)

||

'|'

-- || RPAD (j.line_number, 12)

||

'|'

||

RPAD (j.line_id, 10)

||

'|'

||

RPAD (l_ret_status, 7)

||

'|'

|| v_error_msg

;

fnd_file.put_line (fnd_file.output, v_msg_text);

-- DBMS_OUTPUT.put_line (v_msg_text);

ELSE

fnd_file.put_line (fnd_file.LOG,

'***** Backorder Sucess *****'

);

fnd_file.put_line (fnd_file.LOG,

'Sales Order => ' || j.order_number

);

-- DBMS_OUTPUT.put_line

-- ( 'sucess back order:-Sales Order => '

-- || j.order_number

-- );

fnd_file.put_line (fnd_file.LOG,

'Line Number => ' || j.line_id

);

fnd_file.put_line (fnd_file.LOG,

'Line ID=> '

|| i

.line_id

||

' Line Cancelled Successfully'

);

-- DBMS_OUTPUT.put_line ('sucess:-Line ID=> ' || j.line_id);

fnd_file.put_line (fnd_file.LOG,

'Return Status: ' || l_ret_status

);

DBMS_OUTPUT.put_line

('Return Status: ' || l_ret_status);

-- fnd_file.put_line(fnd_file.log,'Error Message: ' || l_msg_data);

END IF;

COMMIT;

END;

COMMIT;

--**************************************************************************************************************************

END LOOP;

END LOOP;

END xxwms_cancel_orders;