SET serveroutput on size 9000000;
DECLARE
p_line_tbl apps.oe_order_pub.line_tbl_type;
x_line_out_tbl apps.oe_order_pub.line_tbl_type;
p_header_rec apps.oe_order_pub.header_rec_type;
x_header_out_rec apps.oe_order_pub.header_rec_type;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_msg_dummy VARCHAR2 (4000);
l_header_id NUMBER;
l_org_id NUMBER;
l_index NUMBER := 0;
l_rowid ROWID;
l_seq_num NUMBER;
l_line_exist BOOLEAN := FALSE;
l_index_hdr NUMBER := 1;
l_close_cc NUMBER;
CURSOR cur_cancel_line (p_header_id IN NUMBER)
IS
SELECT oola.*
FROM apps.oe_order_lines_all oola
WHERE oola.header_id = p_header_id
AND oola.flow_status_code NOT IN ('CANCELLED', 'CLOSED')
AND ordered_item IN
('SP-511',
'SP-516A-R6',
'X3188A-R5',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'X1867-R6',
'X1563C-R5',
'X516B-R6',
'SP-3194-R6',
'X516B-R6',
'X6529-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X3280-R6',
'SP-516A-R6',
'SP-511',
'SP-3195-R5',
'SP-3195-R5',
'X-SFP-H10GB-CU5M-R6',
'X3187-R5',
'X3187-R5',
'SP-511',
'SP-516A-R6',
'X3188A-R5',
'X3188A-R5',
'X3187-R5',
'X8532-R5',
'SP-516A-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3187-R5',
'X8533-R5',
'X8533-R5',
'X3200A-R6',
'X516B-R6',
'X516B-R6',
'SP-3194-R6',
'X3201A-R6',
'X1848A-R5',
'X6519A-R6',
'X6519A-R6',
'X1845A-R6',
'X3148A-R6',
'X6553-R6',
'X6539-R6',
'SP-3123-R6',
'X6521-R6',
'X6529-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1845A-R6',
'SP-3194-R6',
'SP-3194-R6',
'X3194-R6',
'X3187-R5',
'X516A-R6',
'X8533-R5',
'X3194-R6',
'X3194-R6',
'X3187-R5',
'X3187-R5',
'SP-3194-R6',
'X516B-R6',
'X516B-R6',
'SP-751A',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X6517A',
'SP-3123-R6',
'X6539-R6',
'X6539-R6',
'X1849A-R5',
'X6539-R6',
'SP-6530-R6',
'X6529-R6',
'X1949A-R5',
'X1949A-R5',
'X6529-R6',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'X1848A-R5',
'X1848A-R5',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1846-R6',
'SP-6530-R6',
'SP-6530-R6',
'SP-6530-R6',
'SP-6530-R6',
'X6539-R6',
'X1846-R6',
'X1848A-R5',
'X6529-R6',
'X1848A-R5',
'X1848A-R5',
'X6529-R6',
'SP-516A-R6',
'X3280-R6',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'X8532-R5',
'X8532-R5',
'X8532-R5',
'X3194-R6',
'X3194-R6',
'X8531-R5',
'X516B-R6',
'SP-516A-R6',
'X8533-R5',
'X3197-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X3188A-R5',
'X1413A-R6',
'X3194-R6',
'X8531-R5',
'X516B-R6',
'X516B-R6',
'X6563-R6',
'X6563-R6',
'X6539-R6',
'SP-6530-R6',
'X6539-R6',
'SP-6536-R6',
'X6558-R6',
'X1845A-R6',
'X6557-R6',
'X6529-R6',
'X6539-R6',
'SP-1560A',
'SP-6530-R6',
'X1848A-R5',
'X6539-R6',
'X1848A-R5',
'X6524',
'X8533-R5',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'SP-3194-R6',
'X3280-R6',
'X3210-R5',
'X516B-R6',
'X3203A-R6',
'X516B-R6',
'X516B-R6',
'X6563-R6',
'X6563-R6',
'X6563-R6',
'X6563-R6',
'X516B-R6',
'X6529-R6',
'X6539-R6',
'X1845A-R6',
'X6529-R6',
'X6529-R6',
'X1846-R6',
'SP-6530-R6',
'SP-6524-R6',
'SP-6524-R6',
'X6539-R6',
'X6539-R6',
'X1945A-R6',
'X1945A-R6',
'X6539-R6',
'X6539-R6',
'SP-6536-R6',
'SP-6537-R6',
'X1845A-R6',
'X516A-R6',
'X8533-R5',
'X1837-R6',
'X516A-R6',
'X516A-R6',
'X3187-R5',
'X3178A-R6',
'SP-516A-R6',
'X3200A-R6',
'SP-3194-R6',
'X3178A-R6',
'X3188A-R5',
'X1848A-R5',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-511',
'SP-3194-R6',
'X-SFP-H10GB-CU5M-R6',
'X-SFP-H10GB-CU5M-R6',
'X3194-R6',
'X3194-R6',
'SP-3194-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'SP-516A-R6',
'X3188A-R5',
'X3280-R6',
'X3188A-R5',
'X3194-R6',
'X3188A-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'SP-511',
'X516B-R6',
'X3280-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X1848A-R5',
'X1848A-R5',
'SP-6536-R6',
'X1849A-R5',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'X1848A-R5',
'X6529-R6',
'X3148A-R6',
'SP-6536-R6',
'X6539-R6',
'X6539-R6',
'X1945A-R6',
'X1945A-R6',
'X1845A-R6',
'X6553-R6',
'X3187-R5',
'X3187-R5',
'X8533-R5',
'X516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-511',
'SP-516A-R6',
'X3188A-R5',
'X516B-R6',
'X8533-R5',
'X3195-R5',
'X3195-R5',
'SP-3195-R5',
'X3194-R6',
'X1868-R6',
'X1868-R6',
'X516B-R6',
'X3188A-R5',
'X3188A-R5',
'X1845A-R6',
'X1849A-R5',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X6559-R6',
'X6539-R6',
'SP-6530-R6',
'X1849A-R5',
'X6539-R6',
'X1845A-R6',
'X3148A-R6',
'SP-3123-R6',
'X6557-R6',
'X6557-R6',
'X6539-R6',
'X6553-R6',
'X1848A-R5',
'X1945A-R6',
'X1943A-R6',
'X1945A-R6',
'X1945A-R6',
'SP-6530-R6',
'SP-6530-R6',
'X516B-R6',
'X516A-R6',
'X516A-R6',
'SP-3194-R6',
'SP-516A-R6',
'SP-516A-R6',
'X3194-R6',
'X3194-R6',
'SP-516A-R6',
'SP-3194-R6',
'X3194-R6',
'SP-516A-R6',
'X516B-R6',
'X3188A-R5',
'X516A-R6',
'X3194-R6',
'X1423A-R5',
'X1418A-R6',
'X516B-R6',
'SP-511',
'X3194-R6',
'X1423B-R5',
'X516B-R6',
'X516B-R6',
'X8533-R5',
'X3201A-R6',
'X6539-R6',
'SP-6510A',
'X6539-R6',
'X1949A-R5',
'X1949A-R5',
'SP-6530-R6',
'X1849A-R5',
'X1849A-R5',
'X1848A-R5',
'X80096A-R6',
'X1849A-R5',
'X1847-R6',
'X1943A-R6',
'X6539-R6',
'SP-6536-R6',
'X6558-R6',
'X1946A-R6',
'X6543A-R6',
'X6543A-R6',
'SP-511',
'X3210-R5',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X3187-R5',
'SP-516A-R6',
'X8533-R5',
'X3280-R6',
'X3178A-R6',
'SP-516A-R6',
'X516A-R6',
'SP-516A-R6',
'SP-511',
'X8533-R5',
'X3194-R6',
'SP-511',
'SP-511',
'X1868-R6',
'X516B-R6',
'X1423A-R5',
'X8711-R6',
'X516B-R6',
'X6539-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'X6553-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1846-R6',
'X6539-R6',
'SP-6536-R6',
'X6558-R6',
'X6558-R6',
'X6558-R6',
'X6558-R6',
'X1849A-R5',
'SP-3123',
'X1845A-R6',
'X1845A-R6',
'X6539-R6',
'SP-6536-R6',
'X1949A-R5',
'X1945A-R6',
'X1943A-R6',
'X1848A-R5',
'X3148A-R6',
'X1845A-R6',
'X3200A-R6',
'X516A-R6',
'X516A-R6',
'X3194-R6',
'SP-516A-R6',
'X516B-R6',
'X3194-R6',
'X516B-R6',
'X1940A-R6',
'X6539-R6',
'SP-6530-R6',
'X6539-R6',
'X1845A-R6',
'X6543A-R6',
'X6543A-R6',
'X1849A-R5',
'X6519A',
'SP-6536-R6',
'X6539-R6',
'X6529-R6',
'X6529-R6',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'X1849A-R5',
'X1949A-R5',
'X1949A-R5',
'X6539-R6',
'X6524-R6',
'X1848A-R5',
'X1949A-R5',
'X1949A-R5',
'SP-6536-R6',
'X6539-R6',
'X1848A-R5',
'SP-516A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3188A-R5',
'X1423B-R5',
'X516B-R6',
'SP-6530-R6',
'X6539-R6',
'X1940A',
'X6539-R6',
'X6539-R6',
'SP-516A-R6',
'X1421',
'X1867-R6',
'X1423B-R5',
'X8532-R5',
'X516B-R6',
'X3194-R6',
'SP-6524-R6',
'X6529-R6',
'X1848A-R5',
'X6529-R6',
'X6539-R6',
'SP-516A-R6',
'X1866-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-511',
'X3194-R6',
'X3194-R6',
'X3200A-R6',
'SP-516A-R6',
'X8531-R5',
'X8532-R5',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X1423B-R5',
'X3188A-R5',
'SP-3146A-R5',
'X516B-R6',
'X1849A-R5',
'X6539-R6',
'X1848A-R5',
'X1949A-R5',
'X6532-R6',
'X6539-R6',
'X516B-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'SP-3194-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'SP-511',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X-SFP-H10GB-CU1M-R6',
'SP-511',
'X516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X1418A-R6',
'X1423A-R5',
'SP-751A-R6',
'SP-516A-R6',
'X516A-R6',
'X3187-R5',
'X516B-R6',
'X3194-R6',
'X516B-R6',
'X3194-R6',
'X1421-R5',
'X516B-R6',
'X3188A-R5',
'X3194-R6',
'X3188A-R5',
'X516B-R6',
'X6559-R6',
'SP-6530-R6',
'SP-6536-R6',
'SP-6536-R6',
'X6553-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1848A-R5',
'X6559-R6',
'X6529-R6',
'X-SFP-10G-SR-R6',
'X-SFP-10G-SR-R6',
'X-SFP-10G-SR-R6',
'X-SFP-10G-SR-R6',
'X1940A-R6',
'X1940A',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'X6521-R6',
'X6529-R6',
'X6529-R6',
'X6521-R6',
'X6558-R6',
'SP-8711',
'X1848A-R5',
'X6539-R6',
'X6539-R6',
'X1845A-R6',
'X516A-R6',
'X3187-R5',
'SP-516A-R6',
'SP-516A-R6',
'X8533-R5',
'X516A-R6',
'X3188A-R5',
'SP-511',
'X3203A-R6',
'SP-511',
'X516B-R6',
'X3195-R5',
'X3194-R6',
'X1670A-R5',
'X516B-R6',
'X3280-R6',
'X8533-R5',
'X516B-R6',
'X3195-R5',
'SP-1413A',
'X3194-R6',
'X3188A-R5',
'X8531-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X6529-R6',
'SP-6536-R6',
'X6559-R6',
'SP-6524-R6',
'X6532',
'X6539-R6',
'X6539-R6',
'X6521-R6',
'X6503B',
'X1849A-R5',
'X6532-R6',
'X1848A-R5',
'X3210-R5',
'X516A-R6',
'SP-516A-R6',
'X3188A-R5',
'SP-516A-R6',
'X516A-R6',
'X3280-R6',
'X1866-R6',
'X3194-R6',
'X1866-R6',
'X1867-R6',
'X1866-R6',
'X1866-R6',
'X516B-R6',
'X3280-R6',
'X516B-R6',
'X3187-R5',
'X1848A-R5',
'X1848A-R5',
'X1848A-R5',
'X6559-R6',
'X6529-R6',
'X6557-R6',
'X6558-R6',
'X6532-R6',
'X6532-R6',
'X6532-R6',
'X6532-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1848A-R5',
'X1848A-R5',
'X1849A-R5',
'SP-6530-R6',
'X1848A-R5',
'X6530-R6',
'X6532-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1848A-R5',
'X516A-R6',
'X516A-R6',
'SP-3195-R5',
'X516A-R6',
'X3194-R6',
'X516A-R6',
'X516B-R6',
'X1868-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X516B-R6',
'X1848A-R5',
'X6539-R6',
'X6539-R6',
'SP-6524-R6',
'X1845A-R6',
'X6529-R6',
'X6539-R6',
'X1845A-R6',
'X6554-R6',
'X6529-R6',
'X6539-R6',
'X6559-R6',
'X1949A-R5',
'SP-516A-R6',
'X3188A-R5',
'X6539-R6',
'X1845A-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'X1421-R5',
'X3194-R6',
'X1418A-R6',
'X3187-R5',
'X6529-R6',
'X6529-R6',
'SP-6530-R6',
'X6539-R6',
'X6524-R6',
'SP-1940A',
'X6530-R6',
'X3194-R6',
'X3178A-R6',
'X516A-R6',
'X3178A-R6',
'SP-3194-R6',
'X516B-R6',
'X516A-R6',
'X3194-R6',
'SP-3776A',
'X1866-R6',
'X1866-R6',
'X516B-R6',
'X-SFP-H10GB-CU1M-R6',
'X1845A-R6',
'X1845A-R6',
'X6539-R6',
'X6524-R6',
'X6529-R6',
'X6557-R6',
'X6557-R6',
'X6557-R6',
'X6539-R6',
'X1849A-R5',
'X6530-R6',
'X6529-R6',
'X1848A-R5',
'X1941A',
'X6539-R6',
'X6539-R6',
'X1949A-R5',
'SP-6530-R6',
'SP-6530-R6',
'SP-6530-R6',
'X6539-R6',
'SP-6530-R6',
'X6539-R6',
'SP-6530-R6',
'SP-6530-R6',
'X6539-R6',
'SP-6536-R6',
'X516A-R6',
'X3203A-R6',
'X3203A-R6',
'X1421-R5',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3201A-R6',
'X3187-R5',
'X516A-R6',
'SP-516A-R6',
'X3194-R6',
'X3188A-R5',
'X1868-R6',
'X516B-R6',
'X516B-R6',
'X1423A-R5',
'X3178A-R6',
'X3200A-R6',
'X6529-R6',
'X6529-R6',
'SP-6530-R6',
'SP-6530-R6',
'X1943A-R6',
'X6529-R6',
'X6529-R6',
'X6539-R6',
'SP-6536-R6',
'X6557-R6',
'X6557-R6',
'X6557-R6',
'X6557-R6',
'X6532-R6',
'SP-6536-R6',
'X6539-R6',
'X1848A-R5',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'SP-6536-R6',
'SP-6536-R6',
'X6553-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'X6559-R6',
'X3201A-R6',
'X-SFP-H10GB-CU1M-R6',
'X-SFP-H10GB-CU1M-R6',
'X516A-R6',
'X3187-R5',
'SP-3194-R6',
'X3200A-R6',
'X3195-R5',
'X3178A-R6',
'X3188A-R5',
'X3194-R6',
'X3187-R5',
'X3187-R5',
'X3210-R5',
'X3195-R5',
'X3210-R5',
'X3210-R5',
'X1868-R6',
'X516B-R6',
'X516B-R6',
'X3195-R5',
'X516B-R6',
'X3194-R6',
'X1845A-R6',
'X6529-R6',
'X1849A-R5',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X6529-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X6554-R6',
'X6530-R6',
'X1941A',
'X1413A-R6',
'X1413A-R6',
'X3178A-R6',
'X516A-R6',
'X516A-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-511',
'X516A-R6',
'X3187-R5',
'SP-511',
'X1866-R6',
'X1413A-R6',
'X6529-R6',
'X1845A-R6',
'X1848A-R5',
'X6529-R6',
'X1848A-R5',
'X1849A-R5',
'X1845A-R6',
'X1848A-R5',
'X6530-R6',
'X1845A-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X3201A-R6',
'X8533-R5',
'X3188A-R5',
'X1423A-R5',
'X3188A-R5',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'X516B-R6',
'X516B-R6',
'X3188A-R5',
'X3200A-R6',
'X1845A-R6',
'X6539-R6',
'SP-6530-R6',
'SP-6530-R6',
'X1848A-R5',
'X1949A-R5',
'X1848A-R5',
'X1848A-R5',
'X6558-R6',
'X1845A-R6',
'X1849A-R5',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'X6539-R6',
'SP-6536-R6',
'X1847-R6',
'X6530-R6',
'X6530-R6',
'X6529-R6',
'X1849A-R5',
'X3194-R6',
'X516B-R6',
'X6539-R6',
'X1845A-R6',
'X6529-R6',
'X1946A-R6',
'X1846-R6',
'X3187-R5',
'X8533-R5',
'X8533-R5',
'SP-516A-R6',
'X3178A-R6',
'X3195-R5',
'X3195-R5',
'X1940A',
'X1941A-R6',
'X1941A-R6',
'X1845A-R6',
'X6559-R6',
'SP-6536-R6',
'X3187-R5',
'X3187-R5',
'SP-516A-R6',
'SP-1563B',
'SP-516A-R6',
'SP-516A-R6',
'X1423B-R5',
'X3210-R5',
'X3187-R5',
'X516B-R6',
'SP-511',
'SP-511',
'X3203A-R6'
);
CURSOR cur_cancel_hdr
IS
SELECT *
FROM apps.oe_order_headers_all ooha
WHERE flow_status_code NOT IN ('CANCELLED', 'CLOSED')
AND order_number IN
(8000725561,
8000725838,
8000725956,
8000725998,
8000725999,
8000726009,
8000726015,
8000726015,
8000726023,
8000726076,
8000726107,
8000725595,
8000725742,
8000725727,
8000725814,
8000725778,
8000726005,
8000726110,
8000725688,
8000725688,
8000724397,
8000724969,
8000725389,
8000725419,
8000725419,
8000725437,
8000725474,
8000725474,
8000725493,
8000725524,
8000725560,
8000725772,
8000725799,
8000725880,
8000725896,
8000725909,
8000725909,
8000725909,
8000725909,
8000725974,
8000725981,
8000725981,
8000725992,
8000725750,
8000725330,
8000725338,
8000722082,
8000725283,
8000725581,
8000725581,
8000725759,
8000725886,
8000725890,
8000725890,
8000725591,
8000725663,
8000725663,
8000725719,
8000725719,
8000725719,
8000725719,
8000725858,
8000725115,
8000725115,
8000724575,
8000725252,
8000725360,
8000725423,
8000725425,
8000724983,
8000725127,
8000725129,
8000724912,
8000725092,
8000725094,
8000725258,
8000725385,
8000724769,
8000725148,
8000725148,
8000725189,
8000725114,
8000724956,
8000724939,
8000724858,
8000725040,
8000725239,
8000725299,
8000725324,
8000725324,
8000724982,
8000724982,
8000725000,
8000725077,
8000725077,
8000725077,
8000725168,
8000725221,
8000725039,
8000725039,
8000725047,
8000725047,
8000725047,
8000725075,
8000725088,
8000725088,
8000725088,
8000725090,
8000725090,
8000725100,
8000725100,
8000725100,
8000725120,
8000725120,
8000725123,
8000725123,
8000725273,
8000725289,
8000725289,
8000725289,
8000725289,
8000725195,
8000724608,
8000725255,
8000724868,
8000725241,
8000725312,
8000725194,
8000724162,
8000724196,
8000724264,
8000724296,
8000724347,
8000724347,
8000724347,
8000724375,
8000724394,
8000724394,
8000724394,
8000724420,
8000724420,
8000724490,
8000724786,
8000724802,
8000724900,
8000724902,
8000724462,
8000724463,
8000724410,
8000724558,
8000724660,
8000724282,
8000724442,
8000724605,
8000724667,
8000724667,
8000724326,
8000724326,
8000723253,
8000723468,
8000724343,
8000724343,
8000724371,
8000724377,
8000724510,
8000724716,
8000724856,
8000724857,
8000724470,
8000724423,
8000724416,
8000724683,
8000724326,
8000724170,
8000724174,
8000724195,
8000724257,
8000724299,
8000723059,
8000724075,
8000724046,
8000724159,
8000723957,
8000724056,
8000723020,
8000723020,
8000723020,
8000723020,
8000723475,
8000723465,
8000723995,
8000724217,
8000724261,
8000724262,
8000724263,
8000724320,
8000724345,
8000724345,
8000724345,
8000724345,
8000724034,
8000724034,
8000723886,
8000724171,
8000724090,
8000722979,
8000723341,
8000721500,
8000723681,
8000721780,
8000723033,
8000723052,
8000723160,
8000723169,
8000723181,
8000723215,
8000723275,
8000723451,
8000723131,
8000723080,
8000722403,
8000722403,
8000722403,
8000722476,
8000722507,
8000722524,
8000722524,
8000722584,
8000722665,
8000722694,
8000722740,
8000722743,
8000722744,
8000722745,
8000722821,
8000722889,
8000723014,
8000723162,
8000722631,
8000722654,
8000722731,
8000722732,
8000722394,
8000722723,
8000722604,
8000722837,
8000722834,
8000722838,
8000722896,
8000723015,
8000722839,
8000722927,
8000722557,
8000722558,
8000722666,
8000722770,
8000722959,
8000722959,
8000722959,
8000722959,
8000722959,
8000722959,
8000722959,
8000722959,
8000723057,
8000723082,
8000722648,
8000722725,
8000722774,
8000722794,
8000722892,
8000722893,
8000722490,
8000723019,
8000721993,
8000721993,
8000722145,
8000722150,
8000722271,
8000722405,
8000722465,
8000722519,
8000722224,
8000721815,
8000722218,
8000722088,
8000722088,
8000722088,
8000721957,
8000722345,
8000722466,
8000722471,
8000722576,
8000722036,
8000721256,
8000722194,
8000722199,
8000722402,
8000722472,
8000722590,
8000722594,
8000722594,
8000722614,
8000722642,
8000722288,
8000722341,
8000722151,
8000722175,
8000722175,
8000722356,
8000722356,
8000722311,
8000721905,
8000722361,
8000722423,
8000722501,
8000722321,
8000722321,
8000720867,
8000721503,
8000721518,
8000721527,
8000721561,
8000721581,
8000721610,
8000721743,
8000721746,
8000721808,
8000721920,
8000721950,
8000721960,
8000721988,
8000721990,
8000722001,
8000722034,
8000722130,
8000721667,
8000721402,
8000721395,
8000721629,
8000721690,
8000721862,
8000721867,
8000722074,
8000721994,
8000721994,
8000721994,
8000722004,
8000722004,
8000721603,
8000721298,
8000721298,
8000721658,
8000721682,
8000721723,
8000721395,
8000721848,
8000721646,
8000721646,
8000721760,
8000721729,
8000721914,
8000721914,
8000720895,
8000720896,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720935,
8000720962,
8000720962,
8000721041,
8000721098,
8000721150,
8000721252,
8000721361,
8000721390,
8000721458,
8000721496,
8000720796,
8000721070,
8000720795,
8000721197,
8000721197,
8000721147,
8000721074,
8000714401,
8000720964,
8000721193,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720931,
8000720933,
8000721495,
8000721495,
8000721542,
8000721542,
8000721542,
8000721542,
8000714944,
8000720993,
8000721158,
8000721179,
8000721093,
8000721094,
8000721286,
8000721286,
8000721264,
8000720884,
8000720712,
8000721220,
8000720465,
8000720710,
8000720938,
8000720939,
8000720965,
8000716113,
8000720505,
8000720685,
8000719662,
8000720491,
8000720797,
8000720797,
8000720959,
8000717671,
8000717671,
8000719994,
8000719927,
8000719927,
8000719941,
8000720585,
8000720585,
8000719580,
8000719580,
8000719580,
8000720713,
8000719572,
8000719572,
8000720652,
8000720652,
8000720709,
8000720588,
8000720595,
8000719695,
8000719695,
8000720656,
8000720345,
8000720349,
8000720349,
8000720374,
8000720374,
8000720374,
8000720375,
8000720375,
8000720375,
8000720419,
8000720360,
8000719749,
8000720165,
8000720418,
8000720174,
8000720299,
8000720299,
8000719507,
8000719654,
8000719694,
8000719785,
8000719795,
8000719944,
8000719735,
8000719575,
8000719575,
8000719658,
8000719694,
8000719735,
8000718713,
8000719183,
8000719188,
8000719200,
8000719336,
8000719409,
8000719409,
8000719510,
8000719700,
8000718935,
8000719265,
8000717723,
8000717918,
8000718971,
8000719445,
8000719499,
8000719278,
8000719635,
8000718824,
8000719269,
8000719509,
8000719419,
8000719451,
8000719102,
8000719102,
8000715161,
8000718392,
8000718491,
8000718491,
8000718594,
8000718598,
8000718654,
8000718666,
8000718666,
8000718670,
8000718690,
8000718720,
8000718724,
8000718725,
8000718727,
8000718728,
8000718730,
8000718731,
8000718755,
8000718918,
8000718988,
8000719012,
8000719031,
8000719050,
8000719051,
8000719109,
8000719118,
8000719118,
8000719131,
8000719156,
8000719199,
8000718279,
8000715470,
8000717923,
8000718525,
8000718583,
8000718904,
8000718921,
8000718948,
8000718962,
8000719027,
8000718945,
8000718332,
8000718764,
8000718764,
8000718764,
8000718764,
8000718764,
8000718764,
8000718764,
8000718776,
8000719103,
8000719119,
8000719141,
8000719141,
8000719141,
8000719141,
8000719180,
8000719185,
8000718812,
8000718812,
8000718812,
8000718852,
8000718852,
8000718853,
8000718853,
8000718949,
8000718522,
8000718891,
8000718926,
8000718926,
8000718936,
8000717909,
8000717912,
8000718050,
8000718073,
8000718091,
8000718104,
8000718156,
8000718282,
8000718468,
8000718497,
8000718285,
8000718106,
8000717835,
8000717862,
8000718277,
8000718360,
8000718403,
8000718180,
8000717750,
8000718240,
8000718289,
8000718361,
8000718411,
8000717989,
8000718011,
8000718011,
8000718014,
8000718396,
8000718542,
8000718542,
8000718130,
8000718394,
8000718394,
8000718394,
8000718394,
8000718251,
8000718270,
8000718333,
8000718167,
8000718418,
8000716226,
8000717364,
8000717733,
8000717865,
8000717993,
8000718040,
8000718056,
8000717826,
8000717721,
8000717776,
8000717776,
8000717790,
8000717790,
8000717824,
8000717853,
8000717822,
8000717732,
8000717880,
8000717348,
8000717353,
8000717463,
8000717494,
8000717571,
8000717571,
8000717596,
8000717596,
8000717598,
8000717617,
8000717674,
8000717674,
8000717674,
8000717675,
8000717685,
8000717784,
8000717934,
8000717944,
8000718051,
8000717267,
8000717803,
8000717889,
8000717699,
8000717699,
8000717763,
8000717385,
8000716917,
8000717158,
8000717418,
8000717470,
8000717535,
8000716022,
8000717198,
8000717205,
8000717206,
8000717207,
8000717208,
8000717209,
8000717241,
8000717241,
8000717242,
8000717243,
8000717244,
8000717246,
8000717316,
8000715349,
8000717085,
8000717224,
8000717224,
8000717362,
8000717533,
8000716946,
8000717138,
8000717198,
8000717211,
8000717278,
8000717388,
8000716136,
8000716684,
8000715561,
8000716712,
8000716926,
8000716932,
8000716955,
8000716701,
8000715521,
8000716099,
8000716493,
8000716512,
8000716280,
8000716325,
8000716325,
8000716326,
8000716326,
8000716490,
8000716463,
8000715488,
8000715773,
8000715780,
8000715799,
8000715815,
8000715893,
8000715909,
8000716239,
8000716006,
8000715943,
8000716047,
8000716089,
8000715996,
8000715361,
8000715679,
8000715818,
8000715877,
8000715877,
8000716054,
8000716054,
8000716054,
8000715811,
8000715930,
8000715787,
8000715787,
8000715802,
8000715892,
8000715921,
8000715923,
8000715503,
8000715981,
8000715981,
8000715981,
8000716033,
8000716052,
8000716040,
8000715523,
8000715523,
8000716096,
8000716096,
8000715610,
8000715722,
8000715723,
8000715021,
8000715066,
8000715145,
8000715215,
8000715311,
8000715344,
8000715600,
8000715695,
8000713414,
8000714919,
8000715452,
8000715560,
8000715560,
8000715534,
8000715015,
8000715417,
8000715246,
8000715246,
8000715256,
8000715256,
8000715257,
8000715274,
8000715274,
8000715547,
8000715579,
8000715663,
8000715663,
8000715667,
8000715667,
8000715694,
8000715694,
8000715282,
8000715287,
8000715299,
8000715299,
8000715299,
8000715355,
8000715422,
8000715452,
8000715513,
8000715513,
8000715165,
8000715386,
8000715047,
8000706498,
8000706498,
8000714282,
8000714348,
8000714532,
8000714576,
8000714765,
8000714929,
8000715002,
8000715064,
8000714372,
8000714372,
8000714710,
8000714732,
8000714833,
8000715000,
8000714918,
8000713801,
8000713801,
8000714915,
8000714992,
8000714332,
8000714386,
8000714547,
8000714761,
8000715034,
8000715035,
8000715108,
8000714874,
8000714355,
8000714355,
8000714355,
8000714355,
8000714355,
8000714918,
8000714881,
8000714450,
8000714184,
8000714184,
8000714022,
8000714079,
8000714079,
8000714117,
8000714117,
8000714182,
8000714188,
8000714255,
8000714529,
8000713936,
8000714090,
8000714194,
8000714508,
8000714510,
8000714582,
8000714302,
8000714201,
8000714286,
8000714462,
8000710991,
8000713999,
8000713099,
8000713099,
8000713099,
8000713099,
8000713262,
8000713266,
8000713767,
8000713843,
8000713926,
8000713958,
8000714057,
8000714101,
8000714102,
8000713678,
8000713702,
8000713954,
8000713757,
8000713182,
8000713602,
8000713896,
8000713896,
8000714020,
8000713907,
8000713746,
8000713724,
8000713763,
8000713779,
8000713884,
8000713885,
8000713885,
8000713885,
8000714007,
8000714007,
8000713787,
8000713835,
8000713835,
8000713883,
8000713980,
8000713365,
8000713244,
8000713324,
8000713404,
8000713604,
8000713274,
8000713372,
8000713021,
8000713030,
8000713030,
8000713046,
8000713049,
8000713066,
8000713066,
8000712233,
8000712897,
8000712897,
8000712951,
8000713011,
8000713166,
8000712965,
8000712965,
8000712738,
8000712789,
8000712877,
8000712879,
8000712923,
8000712615,
8000712699,
8000712734,
8000712700,
8000712700,
8000712577
);
BEGIN
--apps.fnd_client_info.set_org_context (153);
--apps.oe_msg_pub.initialize;
--fnd_global.apps_initialize(47866,50603,660);
FOR c_cur_cancel_hdr IN cur_cancel_hdr
LOOP
apps.fnd_client_info.set_org_context (c_cur_cancel_hdr.org_id);
apps.oe_msg_pub.initialize;
l_index := 0;
DBMS_OUTPUT.put_line ('Header id----' || c_cur_cancel_hdr.header_id);
BEGIN
FOR rec_cancel_line IN cur_cancel_line (c_cur_cancel_hdr.header_id)
LOOP
l_index := l_index + 1;
l_line_exist := TRUE;
p_line_tbl (l_index) := apps.oe_order_pub.g_miss_line_rec;
p_line_tbl (l_index).header_id := c_cur_cancel_hdr.header_id;
p_line_tbl (l_index).line_id := rec_cancel_line.line_id;
p_line_tbl (l_index).ordered_quantity := 0;
p_line_tbl (l_index).flow_status_code := 'CANCELLED';
p_line_tbl (l_index).change_reason := 'CANCEL';
p_line_tbl (l_index).change_comments :=
'Change in flow_status_code';
p_line_tbl (l_index).operation := oe_globals.g_opr_update;
p_line_tbl (l_index).last_updated_by := -1;
p_line_tbl (l_index).last_update_date := SYSDATE;
END LOOP;
DBMS_OUTPUT.put_line ('line index' || l_index);
IF l_line_exist
THEN
apps.oe_order_pub.process_line
(p_line_tbl => p_line_tbl,
x_line_out_tbl => x_line_out_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ( 'Lines x_return_status - x_msg_count '
|| x_return_status
|| ' ---- '
|| x_msg_count
);
IF (x_return_status <> 'S')
THEN
FOR j IN 1 .. x_msg_count
LOOP
apps.oe_msg_pub.get (j,
fnd_api.g_false,
x_msg_data,
x_msg_dummy
);
DBMS_OUTPUT.put_line ('Error' || x_msg_data);
END LOOP;
ELSE
COMMIT;
END IF;
END IF;
SELECT COUNT (*)
INTO l_close_cc
FROM apps.oe_order_lines_all
WHERE header_id = c_cur_cancel_hdr.header_id
AND flow_status_code = 'CLOSED';
p_header_rec := apps.oe_order_pub.g_miss_header_rec;
p_header_rec.header_id := c_cur_cancel_hdr.header_id;
IF l_close_cc >= 1
THEN
p_header_rec.flow_status_code := 'CLOSED';
DBMS_OUTPUT.put_line (' LINE STATUS-CLODE123');
ELSE
p_header_rec.flow_status_code := 'CANCELLED';
END IF;
p_header_rec.change_reason := 'CANCEL';
p_header_rec.change_comments := 'Change in flow_status_code';
p_header_rec.last_updated_by := -1;
p_header_rec.last_update_date := SYSDATE;
p_header_rec.operation := oe_globals.g_opr_update;
-- l_index_hdr := l_index_hdr + 1;
apps.oe_order_pub.process_header
(p_header_rec => p_header_rec,
x_header_out_rec => x_header_out_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ( 'HDR x_return_status - x_msg_count '
|| x_return_status
|| ' ---- '
|| x_msg_count
);
IF (x_return_status <> 'S')
THEN
FOR j IN 1 .. x_msg_count
LOOP
apps.oe_msg_pub.get (j,
fnd_api.g_false,
x_msg_data,
x_msg_dummy
);
DBMS_OUTPUT.put_line ('Error' || x_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error1' || x_return_status);
ELSE
DBMS_OUTPUT.put_line ('Commit' || x_return_status);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('Unexpected error in loop' || SQLERRM);
END;
l_line_exist := FALSE;
DBMS_OUTPUT.put_line ('HDR Commit' || x_return_status);
p_line_tbl.DELETE;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Unexpected error' || SQLERRM);
END;
/
DECLARE
p_line_tbl apps.oe_order_pub.line_tbl_type;
x_line_out_tbl apps.oe_order_pub.line_tbl_type;
p_header_rec apps.oe_order_pub.header_rec_type;
x_header_out_rec apps.oe_order_pub.header_rec_type;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_msg_dummy VARCHAR2 (4000);
l_header_id NUMBER;
l_org_id NUMBER;
l_index NUMBER := 0;
l_rowid ROWID;
l_seq_num NUMBER;
l_line_exist BOOLEAN := FALSE;
l_index_hdr NUMBER := 1;
l_close_cc NUMBER;
CURSOR cur_cancel_line (p_header_id IN NUMBER)
IS
SELECT oola.*
FROM apps.oe_order_lines_all oola
WHERE oola.header_id = p_header_id
AND oola.flow_status_code NOT IN ('CANCELLED', 'CLOSED')
AND ordered_item IN
('X3203A-R6',
'X3188A-R5',
'X6539-R6',
'X6532-R6',
'SP-6530-R6',
'SP-6536-R6',
'X80098-R6',
'X1848A-R5',
'X6539-R6',
'X1941A-R6',
'X1845A-R6',
'X6553-R6',
'X6530-R6',
'X6529-R6',
'X6537-R6',
'X6539-R6',
'X6530-R6',
'X6539-R6',
'X6539-R6',
'X6530-R6',
'X1848A-R5',
'X6530-R6',
'X6539-R6',
'X6539-R6',
'X3187-R5',
'X516A-R6',
'X1423A-R5',
'X3187-R5',
'SP-516A-R6',
'X516A-R6',
'X1866-R6',
'X516B-R6',
'X516B-R6',
'SP-511',
'X1868-R6',
'SP-6530-R6',
'X1846-R6',
'SP-6530-R6',
'X6559-R6',
'X6559-R6',
'SP-6530-R6',
'X1849A-R5',
'X1848A-R5',
'X1849A-R5',
'X6529-R6',
'X1845A-R6',
'X1845A-R6',
'X1953A-R6',
'X1949A-R5',
'X6530-R6',
'X6530-R6',
'X6553-R6',
'X1848A-R5',
'X6523-R6',
'X516A-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'X3210-R5',
'X1866-R6',
'X1866-R6',
'X1866-R6',
'X516A-R6',
'X1866-R6',
'X1866-R6',
'X1866-R6',
'X3194-R6',
'X1421-R5',
'X516B-R6',
'X1563C-R5',
'X1563C-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X6558-R6',
'X6558-R6',
'X6502A',
'X1849A-R5',
'X1848A-R5',
'X6529-R6',
'X6529-R6',
'SP-6524-R6',
'SP-6536-R6',
'X6556-R6',
'X6529-R6',
'X6539-R6',
'X1848A-R5',
'X1423A-R5',
'X1423A-R5',
'X516A-R6',
'SP-3194-R6',
'SP-516A-R6',
'SP-511',
'X3210-R5',
'X5190A-R5',
'X3188A-R5',
'X1866-R6',
'X3187-R5',
'X3187-R5',
'X1866-R6',
'X1866-R6',
'X1866-R6',
'X516B-R6',
'SP-6514A',
'X1848A-R5',
'X6524-R6',
'X6524-R6',
'SP-6536-R6',
'X1945A-R6',
'X6529-R6',
'X1949A-R5',
'SP-6536-R6',
'X6539-R6',
'X6539-R6',
'X1845A-R6',
'X1846-R6',
'SP-6536-R6',
'X1848A-R5',
'X6539-R6',
'X6539-R6',
'X6556-R6',
'X1848A-R5',
'X516A-R6',
'X3188A-R5',
'X1421-R5',
'X8531-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X1880-R6',
'X3194-R6',
'X516B-R6',
'X-SFP-H10GB-CU3M-R6',
'X6539-R6',
'X1849A-R5',
'X6539-R6',
'SP-6536-R6',
'X1847-R6',
'X1847-R6',
'X1949A-R5',
'X1849A-R5',
'X6529-R6',
'X6530-R6',
'X6530-R6',
'X1846-R6',
'SP-3123-R6',
'SP-3123-R6',
'X516A-R6',
'X516A-R6',
'X6529-R6',
'X1848A-R5',
'X8711-R6',
'X8533-R5',
'SP-3194-R6',
'X1423B-R5',
'X1845A-R6',
'X800-42U-R6',
'X800-42U-R6',
'X1847-R6',
'X8712-R6',
'X1423B-R5',
'X3188A-R5',
'SP-516A-R6',
'X3194-R6',
'X5190A-R5',
'X3203A-R6',
'X516B-R6',
'X516B-R6',
'SP-3146A-R5',
'X3203A-R6',
'X1849A-R5',
'X1941A-R6',
'X3148A-R6',
'X1845A-R6',
'X1845A-R6',
'X6539-R6',
'SP-6524-R6',
'X1848A-R5',
'X6529-R6',
'X6529-R6',
'X6539-R6',
'X1941A-R6',
'X6539-R6',
'X1848A-R5',
'X3188A-R5',
'X3210-R5',
'X3210-R5',
'X3194-R6',
'X3194-R6',
'X8533-R5',
'X516A-R6',
'X516A-R6',
'X1421-R5',
'X8531-R5',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3201A-R6',
'X516B-R6',
'SP-511',
'X3195-R5',
'X1563C-R5',
'X1868-R6',
'X1868-R6',
'X1845A-R6',
'X6558-R6',
'X6529-R6',
'X6524',
'X1849A-R5',
'X1849A-R5',
'SP-6530-R6',
'SP-6524-R6',
'X6539-R6',
'SP-6524-R6',
'X6539-R6',
'X1847-R6',
'X3196-R5',
'SP-511',
'SP-3194-R6',
'X8533-R5',
'X516A-R6',
'X3187-R5',
'SP-516A-R6',
'X3194-R6',
'X516B-R6',
'X3194-R6',
'X6529-R6',
'SP-6536-R6',
'X6529-R6',
'SP-6530-R6',
'X6529-R6',
'X6529-R6',
'SP-6536-R6',
'X6539-R6',
'X6539-R6',
'X1849A-R5',
'X1949A-R5',
'X6539-R6',
'X1848A-R5',
'X1849A-R5',
'X1941A-R6',
'X3203A-R6',
'X516B-R6',
'X1423B-R5',
'X516B-R6',
'X3194-R6',
'X3210-R5',
'SP-516A-R6',
'X3194-R6',
'X3210-R5',
'X8533-R5',
'X8532-R5',
'X3200A-R6',
'X1423A-R5',
'X3201A-R6',
'X3210-R5',
'X516B-R6',
'X-SFP-H10GB-CU1M-R6',
'SP-3194-R6',
'SP-6524-R6',
'X6539-R6',
'X6539-R6',
'X3148A-R6',
'X3148A-R6',
'X1848A-R5',
'X6539-R6',
'X6529-R6',
'X6529-R6',
'X6539-R6',
'X6539-R6',
'X1949A-R5',
'SP-6536-R6',
'X6539-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6536-R6',
'X6521-R6',
'X6529-R6',
'SP-516A-R6',
'X516A-R6',
'X3187-R5',
'X516A-R6',
'SP-3194-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'SP-516A-R6',
'X516B-R6',
'X3194-R6',
'X6558-R6',
'X6557-R6',
'SP-6536-R6',
'X6558-R6',
'X6539-R6',
'X1846-R6',
'X6539-R6',
'SP-6530-R6',
'X6529-R6',
'X6521-R6',
'X6539-R6',
'X6530-R6',
'X6539-R6',
'X6558-R6',
'X1847-R6',
'X3195-R5',
'X3195-R5',
'X3187-R5',
'X1848A-R5',
'X6539-R6',
'SP-516A-R6',
'X1866-R6',
'X1866-R6',
'X3195-R5',
'X3195-R5',
'X3187-R5',
'X3187-R5',
'X8533-R5',
'X881-R6',
'X881-R6',
'X1848A-R5',
'X1845A-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1866-R6',
'X1866-R6',
'X3194-R6',
'X-SFP-H10GB-CU1M-R6',
'X3187-R5',
'X3187-R5',
'X3188A-R5',
'X516B-R6',
'X3194-R6',
'X1866-R6',
'X8533-R5',
'X8533-R5',
'X6529-R6',
'SP-6530-R6',
'X6532-R6',
'SP-6536-R6',
'X6539-R6',
'X6539-R6',
'X6529-R6',
'SP-6530-R6',
'X1848A-R5',
'X1845A-R6',
'X6521',
'X6539-R6',
'X516B-R6',
'X1087A-R6',
'X516A-R6',
'SP-3195-R5',
'SP-3195-R5',
'SP-3195-R5',
'SP-3195-R5',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'SP-516A-R6',
'X3188A-R5',
'SP-3194-R6',
'X3194-R6',
'SP-516A-R6',
'X6539-R6',
'X6529-R6',
'SP-6536-R6',
'X6539-R6',
'X6532-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'X1845A-R6',
'X6539-R6',
'SP-6524-R6',
'X6539-R6',
'X3148A-R6',
'X1848A-R5',
'SP-3123-R6',
'SP-3123-R6',
'SP-6536-R6',
'X6539-R6',
'X6539-R6',
'X1949A-R5',
'X1949A-R5',
'X6539-R6',
'X6539-R6',
'X6556-R6',
'X6556-R6',
'X6539-R6',
'X6524',
'X6539-R6',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'X3210-R5',
'X516A-R6',
'X1866-R6',
'SP-516A-R6',
'SP-516A-R6',
'X1845A-R6',
'X1846-R6',
'X1846-R6',
'X80061A-R6',
'SP-6530-R6',
'X1848A-R5',
'X1848A-R5',
'X1845A-R6',
'SP-6530-R6',
'X6559-R6',
'SP-516A-R6',
'SP-516A-R6',
'X8533-R5',
'SP-516A-R6',
'X516A-R6',
'X3210-R5',
'X1880-R6',
'X3194-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X3187-R5',
'X3178A-R6',
'X516B-R6',
'X6539-R6',
'SP-6530-R6',
'SP-6530-R6',
'SP-6530-R6',
'X6529-R6',
'X6529-R6',
'X6529-R6',
'X6557-R6',
'X1845A-R6',
'X6539-R6',
'X6530-R6',
'X6539-R6',
'X6529-R6',
'X6553-R6',
'X6539-R6',
'X6539-R6',
'SP-6536-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X3201A-R6',
'SP-3195-R5',
'X1423B-R5',
'X1423B-R5',
'SP-516A-R6',
'X3187-R5',
'X516B-R6',
'X516B-R6',
'X3194-R6',
'X3194-R6',
'X1845A-R6',
'X6539-R6',
'X6539-R6',
'X6559-R6',
'SP-6530-R6',
'X6562-R6',
'X6537-R6',
'X6539-R6',
'SP-6524-R6',
'X6539-R6',
'SP-6530-R6',
'X1949A-R5',
'SP-770A-R6',
'X6539-R6',
'SP-6530-R6',
'SP-6536-R6',
'X3280-R6',
'X3187-R5',
'X6529-R6',
'SP-6530-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'X3195-R5',
'X3194-R6',
'X3195-R5',
'X516A-R6',
'X6529-R6',
'X1848A-R5',
'X1848A-R5',
'X6539-R6',
'X6539-R6',
'X6529-R6',
'SP-6530-R6',
'X6539-R6',
'X3187-R5',
'X3187-R5',
'X1866-R6',
'X1866-R6',
'SP-3194-R6',
'X516A-R6',
'X3194-R6',
'X3280-R6',
'X6563-R6',
'X6563-R6',
'X3194-R6',
'X3187-R5',
'X1670A-R5',
'X3194-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X3188A-R5',
'X3194-R6',
'X516B-R6',
'SP-516A-R6',
'X516B-R6',
'X1848A-R5',
'X1848A-R5',
'X6559-R6',
'X6529-R6',
'X1848A-R5',
'X6539-R6',
'X1846-R6',
'X6539-R6',
'SP-6536-R6',
'X1949A',
'X8531-R5',
'X8531-R5',
'X3194-R6',
'X3194-R6',
'X6563-R6',
'X6563-R6',
'X3200A-R6',
'X3188A-R5',
'X3194-R6',
'X516B-R6',
'SP-516A-R6',
'X1504-R6',
'X1504-R6',
'X516B-R6',
'X3280-R6',
'X6539-R6',
'X6539-R6',
'X1949A-R5',
'X1945A-R6',
'X6539-R6',
'X6523-R6',
'X6539-R6',
'SP-6536-R6',
'SP-6536-R6',
'X6557-R6',
'X6558-R6',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'X3200A-R6',
'X8531',
'SP-516A-R6',
'X1423B-R5',
'X1423A-R5',
'SP-516A-R6',
'X1423B-R5',
'X3195-R5',
'X3195-R5',
'X516A-R6',
'X3195-R5',
'X1418A-R6',
'X1423A-R5',
'X1866-R6',
'X1866-R6',
'X1866-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1867-R6',
'X1866-R6',
'X3203A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X3187-R5',
'SP-1848',
'X6539-R6',
'X1848A-R5',
'SP-6530-R6',
'SP-6530-R6',
'X1845A-R6',
'X6539-R6',
'X1848A-R5',
'X6518A-R6',
'X6519A-R6',
'X6519A-R6',
'X6518A-R6',
'X6518A-R6',
'X6518A-R6',
'X6519A-R6',
'X6519A-R6',
'X1848A-R5',
'X1848A-R5',
'SP-6536-R6',
'X1847-R6',
'X6559-R6',
'X1849A-R5',
'X6530-R6',
'X6530-R6',
'X6530-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X6518A',
'X6539-R6',
'X6518A',
'X6530-R6',
'X3195-R5',
'X1423B-R5',
'X1423B-R5',
'SP-3195-R5',
'X3187-R5',
'X1867-R6',
'SP-3195-R5',
'SP-511',
'X516A-R6',
'X1423B-R5',
'X1423B-R5',
'X3187-R5',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X516A-R6',
'X8533-R5',
'X8533-R5',
'X516A-R6',
'X3188A-R5',
'X3195-R5',
'X3201A-R6',
'X516B-R6',
'X3201A-R6',
'X6539-R6',
'X1845A-R6',
'X1849A-R5',
'X1845A-R6',
'X6539-R6',
'X6539-R6',
'SP-6530-R6',
'SP-6530-R6',
'X1949A-R5',
'X1848A-R5',
'X6521-R6',
'X6529',
'X1413A',
'X1563C-R5',
'X1563C-R5',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'SP-3776A',
'SP-3177A',
'X516B-R6',
'X3194-R6',
'X516B-R6',
'X3194-R6',
'X516B-R6',
'X3280-R6',
'X1423A-R5',
'X1848A-R5',
'X1941A-R6',
'X1941A-R6',
'SP-6536-R6',
'X6559-R6',
'X6559-R6',
'X6539-R6',
'X6539-R6',
'X1848A-R5',
'X1845A-R6',
'X1848A-R5',
'X1848A-R5',
'SP-6536-R6',
'X6529-R6',
'X6529-R6',
'X6529-R6',
'X6529-R6',
'X1949A-R5',
'SP-3116-R6',
'X6531-R6',
'SP-6524-R6',
'X6503B',
'X1848A-R5',
'X1418A-R6',
'X1418A-R6',
'X3187-R5',
'X1423A-R5',
'X1423A-R5',
'X1940A-R6',
'X1940A-R6',
'X6539-R6',
'X6539-R6',
'SP-516A-R6',
'X3187-R5',
'X3201A-R6',
'SP-3195-R5',
'X516A-R6',
'X6529-R6',
'X6536-R6',
'SP-770A-R6',
'X6539-R6',
'X6539-R6',
'X6531-R6',
'X6539-R6',
'SP-6530-R6',
'X3195-R5',
'SP-3195-R5',
'X516A-R6',
'X516A-R6',
'X3187-R5',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'SP-3194-R6',
'X3194-R6',
'X6539-R6',
'SP-6530-R6',
'SP-6536-R6',
'X6539-R6',
'X1031A',
'SP-6530-R6',
'X1845A-R6',
'SP-6530-R6',
'SP-6530-R6',
'SP-6536-R6',
'X6539-R6',
'SP-6524-R6',
'X6539-R6',
'X6539-R6',
'X1848A-R5',
'X6553-R6',
'X6539-R6',
'SP-6536-R6',
'X1949A-R5',
'X1562A',
'SP-6536-R6',
'X6539-R6',
'X1941A-R6',
'X8532-R5',
'X8532-R5',
'X8531-R5',
'SP-3194-R6',
'X3187-R5',
'X516A-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X3194-R6',
'X3194-R6',
'X1869-R6',
'X1847-R6',
'X1847-R6',
'X6532-R6',
'X6558-R6',
'X6539-R6',
'X6554-R6',
'SP-6530-R6',
'X1848A-R5',
'SP-6530-R6',
'SP-3123',
'X1845A-R6',
'SP-770A-R6',
'X6532',
'X6532-R6',
'X6532',
'X6532-R6',
'X1423B-R5',
'X3203A-R6',
'X516A-R6',
'X3194-R6',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'X1563C-R5',
'X1563C-R5',
'X516B-R6',
'X3194-R6',
'SP-6530-R6',
'X1848A-R5',
'X1848A-R5',
'X6539-R6',
'X1849A-R5',
'X6539-R6',
'X6539-R6',
'X1845A-R6',
'X1848A-R5',
'X6556-R6',
'SP-6536-R6',
'X6538-R6',
'X1848A-R5',
'X6529-R6',
'X6524-R6',
'X6529-R6',
'X8533-R5',
'SP-3280-R6',
'X1413A-R6',
'X-SFP-H10GB-CU1M-R6',
'X-SFP-H10GB-CU1M-R6',
'SP-511',
'SP-511',
'X1867-R6',
'X516A-R6',
'X1423B-R5',
'X6563-R6',
'X1423B-R5',
'X516A-R6',
'X8533-R5',
'X3194-R6',
'X516B-R6',
'X1881-R6',
'X1881-R6',
'X6539-R6',
'X1845A-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'SP-6524-R6',
'SP-6524-R6',
'SP-6524-R6',
'SP-6524-R6',
'SP-6536-R6',
'X1946A-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'X1845A-R6',
'X1423B-R5',
'X3195-R5',
'X1867-R6',
'X516A-R6',
'X3195-R5',
'X3203A-R6',
'X3203A-R6',
'X1423A-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X3188A-R5',
'X1413A-R6',
'X1848A-R5',
'X6539-R6',
'X1845A-R6',
'X3148A-R6',
'X6531',
'X6503C',
'X6521-R6',
'X6529-R6',
'X6521-R6',
'X1845A-R6',
'X6539-R6',
'X6553-R6',
'X6539-R6',
'SP-6536-R6',
'X6529-R6',
'X1848A-R5',
'X1949A-R5',
'X6530-R6',
'X1945A-R6',
'X1949A-R5',
'X1945A-R6',
'X1949A-R5',
'X6529-R6',
'SP-6518A-R6',
'X1849A-R5',
'SP-6536-R6',
'X3195-R5',
'X3195-R5',
'X6539-R6',
'X6539-R6',
'X516A-R6',
'X3187-R5',
'X3187-R5',
'X6563-R6',
'X6563-R6',
'X6563-R6',
'X6563-R6',
'X6563-R6',
'X6531-R6',
'X6539-R6',
'X6533',
'SP-6524-R6',
'X1849A-R5',
'X516A-R6',
'SP-3194-R6',
'X3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-516A-R6',
'X516A-R6',
'X8711-R6',
'X3203A-R6',
'X3194-R6',
'X3194-R6',
'X1848A-R5',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1845A-R6',
'X6529-R6',
'X6539-R6',
'X6532-R6',
'X6539-R6',
'SP-6530-R6',
'X6524-R6',
'SP-8711',
'X6530-R6',
'X6524-R6',
'X6529-R6',
'SP-1030A',
'X3194-R6',
'SP-516A-R6',
'X516A-R6',
'SP-516A-R6',
'X8531-R5',
'SP-516A-R6',
'X8533-R5',
'X1413A-R6',
'SP-516A-R6',
'X1563C-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X6539-R6',
'X1848A-R5',
'SP-6530-R6',
'X6539-R6',
'SP-6530-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'SP-6518A-R6',
'X1849A-R5',
'X3187-R5',
'X3187-R5',
'X3200A-R6',
'X1866-R6',
'X3200A-R6',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3210-R5',
'X3187-R5',
'X3210-R5',
'X3195-R5',
'X1423A-R5',
'X3195-R5',
'X3195-R5',
'X3195-R5',
'X3195-R5',
'X516B-R6',
'X8711-R6',
'X8711-R6',
'X1866-R6',
'X516B-R6',
'X516B-R6',
'X6539-R6',
'X6539-R6',
'X1845A-R6',
'X6559-R6',
'X1845A-R6',
'X1949A-R5',
'X1849A-R5',
'SP-6536-R6',
'SP-6530-R6',
'X6539-R6',
'X1845A-R6',
'X1845A-R6',
'X6539-R6',
'X6530-R6',
'X875B-R6'
);
CURSOR cur_cancel_hdr
IS
SELECT *
FROM apps.oe_order_headers_all ooha
WHERE flow_status_code NOT IN ('CANCELLED', 'CLOSED')
AND order_number IN
(8000712735,
8000712150,
8000712293,
8000712364,
8000712796,
8000712387,
8000712567,
8000712113,
8000712598,
8000712598,
8000712611,
8000712654,
8000712654,
8000712785,
8000712785,
8000712306,
8000712306,
8000712306,
8000712317,
8000712318,
8000712716,
8000712201,
8000712201,
8000712201,
8000711741,
8000711765,
8000711785,
8000711985,
8000712142,
8000712184,
8000712039,
8000712132,
8000711951,
8000711927,
8000710432,
8000712300,
8000711786,
8000711825,
8000711842,
8000711842,
8000711865,
8000711919,
8000711959,
8000712238,
8000711931,
8000711452,
8000711505,
8000711955,
8000711955,
8000711992,
8000712030,
8000712039,
8000711654,
8000712185,
8000711705,
8000711114,
8000711114,
8000711114,
8000711114,
8000711118,
8000711118,
8000711167,
8000711221,
8000711221,
8000711221,
8000711673,
8000711685,
8000711694,
8000711694,
8000711725,
8000711727,
8000711015,
8000711415,
8000711440,
8000711643,
8000711490,
8000711027,
8000711085,
8000711086,
8000711086,
8000711210,
8000711275,
8000710897,
8000711108,
8000711108,
8000711415,
8000711427,
8000711440,
8000711476,
8000711514,
8000711460,
8000711157,
8000711239,
8000709625,
8000710864,
8000710871,
8000711004,
8000711075,
8000710736,
8000710589,
8000710626,
8000710887,
8000710887,
8000710930,
8000710930,
8000710930,
8000710927,
8000711171,
8000710757,
8000710852,
8000710852,
8000710984,
8000711031,
8000711079,
8000711124,
8000711154,
8000711154,
8000711154,
8000711184,
8000710777,
8000710800,
8000711014,
8000703693,
8000710876,
8000710876,
8000710926,
8000709567,
8000710358,
8000710616,
8000710400,
8000710545,
8000710453,
8000710522,
8000710567,
8000710218,
8000710220,
8000710643,
8000710281,
8000710422,
8000710686,
8000710686,
8000710689,
8000710726,
8000710235,
8000710466,
8000710530,
8000710530,
8000709538,
8000710620,
8000710536,
8000710540,
8000710217,
8000710299,
8000710258,
8000710181,
8000709505,
8000709610,
8000709849,
8000709783,
8000709583,
8000709627,
8000709627,
8000709782,
8000709096,
8000709163,
8000709212,
8000709225,
8000709483,
8000709489,
8000709573,
8000709319,
8000709336,
8000709414,
8000708549,
8000708201,
8000708985,
8000709094,
8000709134,
8000709550,
8000709565,
8000709565,
8000709574,
8000709186,
8000709186,
8000709168,
8000709168,
8000709485,
8000709431,
8000706493,
8000708793,
8000708793,
8000708455,
8000708455,
8000708698,
8000708749,
8000708797,
8000708830,
8000708830,
8000708857,
8000708857,
8000709107,
8000708389,
8000708482,
8000708917,
8000707937,
8000709021,
8000709022,
8000709022,
8000709156,
8000708766,
8000708827,
8000708827,
8000708957,
8000709052,
8000709170,
8000709170,
8000709170,
8000709128,
8000709128,
8000708916,
8000704730,
8000707539,
8000707873,
8000708197,
8000708624,
8000708633,
8000708695,
8000708707,
8000708562,
8000707228,
8000708246,
8000708246,
8000708246,
8000708582,
8000708582,
8000708582,
8000708707,
8000708707,
8000708707,
8000708607,
8000708209,
8000707987,
8000708386,
8000708385,
8000707996,
8000706611,
8000706859,
8000707547,
8000707605,
8000707680,
8000707716,
8000707832,
8000707994,
8000708025,
8000708055,
8000708074,
8000707764,
8000707821,
8000707862,
8000707960,
8000707963,
8000708061,
8000706971,
8000708137,
8000708137,
8000708137,
8000706355,
8000706355,
8000707510,
8000707718,
8000707730,
8000707730,
8000707761,
8000707761,
8000707775,
8000707911,
8000708047,
8000708047,
8000708057,
8000708057,
8000708106,
8000707755,
8000707755,
8000707257,
8000707323,
8000707550,
8000707590,
8000707632,
8000707348,
8000705584,
8000706374,
8000707447,
8000707462,
8000707350,
8000707240,
8000707317,
8000707128,
8000707060,
8000707374,
8000707569,
8000707587,
8000707588,
8000707596,
8000706271,
8000707306,
8000707306,
8000706794,
8000707349,
8000707349,
8000707497,
8000707196,
8000707061,
8000707061,
8000707137,
8000706979,
8000707118,
8000706338,
8000706455,
8000706455,
8000706585,
8000706585,
8000706665,
8000706665,
8000706732,
8000706447,
8000706447,
8000706452,
8000706556,
8000706585,
8000706585,
8000706664,
8000704306,
8000704306,
8000706026,
8000706386,
8000706157,
8000706157,
8000706158,
8000706236,
8000706236,
8000706304,
8000705866,
8000705890,
8000706234,
8000706234,
8000706234,
8000706426,
8000706426,
8000706426,
8000705817,
8000705831,
8000705852,
8000706299,
8000706347,
8000706201,
8000704681,
8000705465,
8000705468,
8000705523,
8000705523,
8000705524,
8000705524,
8000705526,
8000705526,
8000705570,
8000705788,
8000705843,
8000706042,
8000706004,
8000705798,
8000705426,
8000705593,
8000705593,
8000705609,
8000705609,
8000705609,
8000705628,
8000705628,
8000705875,
8000705911,
8000705911,
8000705911,
8000705999,
8000705580,
8000705594,
8000705680,
8000705741,
8000705799,
8000705799,
8000705399,
8000705753,
8000705811,
8000705811,
8000705863,
8000705863,
8000705352,
8000705352,
8000705352,
8000705430,
8000705102,
8000705183,
8000705385,
8000705452,
8000705280,
8000703707,
8000703707,
8000704652,
8000704665,
8000704665,
8000704846,
8000704976,
8000705282,
8000705420,
8000705214,
8000705050,
8000704751,
8000704305,
8000704461,
8000704482,
8000704570,
8000704728,
8000704921,
8000704625,
8000704731,
8000704737,
8000704737,
8000704292,
8000704057,
8000704721,
8000704408,
8000704213,
8000704213,
8000704213,
8000704213,
8000704213,
8000704213,
8000704213,
8000704480,
8000704500,
8000704270,
8000704270,
8000704657,
8000704768,
8000704821,
8000704821,
8000704821,
8000704708,
8000702694,
8000702734,
8000702734,
8000702734,
8000703650,
8000703861,
8000704257,
8000704272,
8000704351,
8000704140,
8000704168,
8000704197,
8000704266,
8000704266,
8000702763,
8000703125,
8000703125,
8000703237,
8000703387,
8000704192,
8000704219,
8000704219,
8000704287,
8000704320,
8000704320,
8000703631,
8000699414,
8000703873,
8000703873,
8000703873,
8000703623,
8000703792,
8000703739,
8000703739,
8000703937,
8000703937,
8000703534,
8000703534,
8000703534,
8000703121,
8000703278,
8000703344,
8000703592,
8000703113,
8000703213,
8000703216,
8000703358,
8000703489,
8000703547,
8000703555,
8000703555,
8000703194,
8000703194,
8000702644,
8000702644,
8000702657,
8000702773,
8000702785,
8000702790,
8000702821,
8000702821,
8000702930,
8000703136,
8000703236,
8000702670,
8000703025,
8000703026,
8000703026,
8000702649,
8000699485,
8000702593,
8000702991,
8000702045,
8000702729,
8000702738,
8000702795,
8000702882,
8000703062,
8000703130,
8000703136,
8000703000,
8000703000,
8000702838,
8000702167,
8000702167,
8000702496,
8000702656,
8000702761,
8000702761,
8000702368,
8000702253,
8000702569,
8000702589,
8000702601,
8000702184,
8000702184,
8000701261,
8000702511,
8000702214,
8000702214,
8000702259,
8000702259,
8000702289,
8000702289,
8000702291,
8000702291,
8000702607,
8000702743,
8000702743,
8000702345,
8000702345,
8000702345,
8000702346,
8000702346,
8000702348,
8000702578,
8000702578,
8000702371,
8000694095,
8000701568,
8000701568,
8000701833,
8000701884,
8000701898,
8000701919,
8000702079,
8000702142,
8000702168,
8000702190,
8000702227,
8000702227,
8000702251,
8000701890,
8000701940,
8000701940,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000702063,
8000702074,
8000702074,
8000701996,
8000702027,
8000701189,
8000701812,
8000701818,
8000701858,
8000701858,
8000701916,
8000702085,
8000702231,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000701974,
8000702052,
8000702053,
8000702081,
8000701493,
8000702005,
8000701699,
8000701986,
8000701986,
8000701986,
8000701986,
8000701986,
8000701986,
8000701986,
8000701986,
8000701986,
8000701986,
8000701802,
8000699700,
8000699700,
8000700900,
8000701181,
8000701243,
8000701315,
8000701325,
8000701347,
8000701393,
8000701422,
8000701484,
8000701492,
8000701624,
8000701680,
8000701685,
8000701744,
8000701744,
8000701751,
8000701361,
8000701692,
8000701146,
8000701152,
8000701385,
8000701244,
8000701297,
8000701550,
8000701686,
8000701726,
8000701726,
8000701745,
8000701745,
8000701765,
8000701786,
8000699896,
8000701490,
8000700783,
8000701213,
8000701213,
8000701219,
8000701298,
8000701336,
8000699404,
8000699404,
8000700969,
8000700175,
8000701100,
8000701300,
8000701088,
8000701106,
8000700371,
8000700463,
8000697462,
8000697462,
8000700840,
8000700849,
8000700849,
8000701084,
8000701084,
8000701120,
8000701221,
8000701283,
8000701310,
8000699543,
8000699543,
8000699543,
8000699961,
8000699961,
8000701014,
8000701077,
8000700121,
8000700121,
8000700121,
8000701252,
8000700415,
8000700415,
8000700702,
8000700776,
8000700776,
8000700569,
8000700669,
8000700730,
8000700730,
8000700128,
8000700144,
8000700184,
8000700282,
8000700426,
8000700444,
8000700444,
8000700080,
8000700139,
8000700185,
8000700420,
8000700441,
8000700441,
8000699707,
8000699800,
8000699988,
8000700007,
8000700107,
8000700145,
8000700145,
8000700146,
8000699986,
8000699986,
8000700103,
8000700103,
8000700103,
8000700103,
8000700108,
8000700108,
8000699650,
8000699780,
8000699780,
8000699780,
8000699835,
8000699835,
8000700041,
8000700071,
8000700110,
8000700176,
8000699837,
8000699837,
8000699527,
8000699969,
8000699517,
8000699517,
8000699677,
8000698923,
8000698997,
8000699059,
8000699175,
8000699312,
8000699644,
8000699425,
8000699470,
8000699470,
8000699506,
8000699506,
8000699608,
8000698880,
8000699139,
8000699432,
8000699596,
8000699613,
8000699613,
8000699655,
8000699667,
8000699753,
8000699442,
8000699436,
8000699434,
8000699484,
8000699484,
8000699484,
8000699484,
8000698788,
8000698788,
8000698839,
8000699081,
8000699135,
8000699231,
8000699241,
8000698694,
8000698694,
8000698908,
8000699016,
8000699182,
8000698530,
8000698561,
8000698696,
8000698720,
8000698824,
8000698824,
8000698858,
8000698756,
8000698694,
8000698694,
8000699011,
8000698612,
8000698873,
8000698873,
8000698873,
8000692224,
8000697751,
8000698146,
8000698256,
8000698256,
8000698281,
8000698283,
8000698482,
8000698553,
8000698570,
8000698596,
8000698616,
8000698686,
8000698250,
8000698291,
8000698425,
8000697925,
8000697925,
8000698165,
8000698270,
8000698432,
8000698432,
8000698432,
8000698432,
8000698438,
8000698438,
8000698439,
8000698439,
8000698444,
8000698491,
8000698339,
8000698339,
8000698339,
8000698420,
8000697597,
8000697944,
8000698126,
8000698127,
8000698148,
8000697783,
8000697783,
8000695755,
8000697803,
8000697835,
8000697903,
8000697951,
8000698042,
8000698042,
8000698042,
8000697919,
8000696897,
8000697690,
8000697312,
8000697743,
8000697895,
8000698109,
8000698109,
8000697774,
8000697774,
8000697788,
8000697399,
8000691663,
8000691663,
8000691663,
8000694617,
8000696540,
8000697048,
8000697938,
8000697980,
8000697989,
8000697989,
8000697989,
8000697989,
8000697955,
8000697955,
8000697809,
8000697833,
8000697646,
8000697646,
8000697487,
8000697487,
8000696955,
8000697083,
8000697083,
8000697252,
8000697252,
8000697253,
8000697253,
8000697253,
8000697213,
8000697213,
8000697228,
8000697228,
8000697250,
8000696706,
8000696856,
8000696856,
8000696856,
8000696935,
8000697031,
8000697051,
8000695951,
8000696988,
8000695901,
8000695901,
8000696594,
8000696603,
8000696731,
8000696731,
8000696731,
8000696893,
8000696906,
8000696906,
8000696972,
8000696792,
8000696786,
8000696786,
8000696786,
8000693801,
8000693801,
8000695945,
8000696819,
8000696472,
8000696472,
8000693004,
8000695706,
8000696085,
8000696280,
8000696296,
8000696480,
8000696500,
8000696505,
8000696585,
8000696632,
8000693232,
8000696346,
8000696360,
8000696397,
8000696017,
8000696104,
8000696218,
8000696567,
8000696567,
8000696045,
8000696045,
8000696127,
8000696127,
8000695966,
8000695562,
8000695562,
8000695582,
8000695621,
8000695633,
8000695789,
8000695789,
8000695789,
8000695793,
8000695840,
8000696073,
8000696089,
8000695872,
8000695457,
8000695457,
8000695457,
8000695457,
8000695719,
8000695953,
8000695953,
8000695492,
8000695748,
8000695379,
8000695686,
8000695937,
8000695989,
8000696020,
8000696051,
8000696116,
8000695649,
8000695627,
8000695627,
8000695627,
8000695635,
8000695478,
8000695667,
8000695930,
8000695953
);
BEGIN
--apps.fnd_client_info.set_org_context (153);
--apps.oe_msg_pub.initialize;
--fnd_global.apps_initialize(47866,50603,660);
FOR c_cur_cancel_hdr IN cur_cancel_hdr
LOOP
apps.fnd_client_info.set_org_context (c_cur_cancel_hdr.org_id);
apps.oe_msg_pub.initialize;
l_index := 0;
DBMS_OUTPUT.put_line ('Header id----' || c_cur_cancel_hdr.header_id);
BEGIN
FOR rec_cancel_line IN cur_cancel_line (c_cur_cancel_hdr.header_id)
LOOP
l_index := l_index + 1;
l_line_exist := TRUE;
p_line_tbl (l_index) := apps.oe_order_pub.g_miss_line_rec;
p_line_tbl (l_index).header_id := c_cur_cancel_hdr.header_id;
p_line_tbl (l_index).line_id := rec_cancel_line.line_id;
p_line_tbl (l_index).ordered_quantity := 0;
p_line_tbl (l_index).flow_status_code := 'CANCELLED';
p_line_tbl (l_index).change_reason := 'CANCEL';
p_line_tbl (l_index).change_comments :=
'Change in flow_status_code';
p_line_tbl (l_index).operation := oe_globals.g_opr_update;
p_line_tbl (l_index).last_updated_by := -1;
p_line_tbl (l_index).last_update_date := SYSDATE;
END LOOP;
DBMS_OUTPUT.put_line ('line index' || l_index);
IF l_line_exist
THEN
apps.oe_order_pub.process_line
(p_line_tbl => p_line_tbl,
x_line_out_tbl => x_line_out_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ( 'Lines x_return_status - x_msg_count '
|| x_return_status
|| ' ---- '
|| x_msg_count
);
IF (x_return_status <> 'S')
THEN
FOR j IN 1 .. x_msg_count
LOOP
apps.oe_msg_pub.get (j,
fnd_api.g_false,
x_msg_data,
x_msg_dummy
);
DBMS_OUTPUT.put_line ('Error' || x_msg_data);
END LOOP;
ELSE
COMMIT;
END IF;
END IF;
SELECT COUNT (*)
INTO l_close_cc
FROM apps.oe_order_lines_all
WHERE header_id = c_cur_cancel_hdr.header_id
AND flow_status_code = 'CLOSED';
p_header_rec := apps.oe_order_pub.g_miss_header_rec;
p_header_rec.header_id := c_cur_cancel_hdr.header_id;
IF l_close_cc >= 1
THEN
p_header_rec.flow_status_code := 'CLOSED';
DBMS_OUTPUT.put_line (' LINE STATUS-CLODE123');
ELSE
p_header_rec.flow_status_code := 'CANCELLED';
END IF;
p_header_rec.change_reason := 'CANCEL';
p_header_rec.change_comments := 'Change in flow_status_code';
p_header_rec.last_updated_by := -1;
p_header_rec.last_update_date := SYSDATE;
p_header_rec.operation := oe_globals.g_opr_update;
-- l_index_hdr := l_index_hdr + 1;
apps.oe_order_pub.process_header
(p_header_rec => p_header_rec,
x_header_out_rec => x_header_out_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ( 'HDR x_return_status - x_msg_count '
|| x_return_status
|| ' ---- '
|| x_msg_count
);
IF (x_return_status <> 'S')
THEN
FOR j IN 1 .. x_msg_count
LOOP
apps.oe_msg_pub.get (j,
fnd_api.g_false,
x_msg_data,
x_msg_dummy
);
DBMS_OUTPUT.put_line ('Error' || x_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error1' || x_return_status);
ELSE
DBMS_OUTPUT.put_line ('Commit' || x_return_status);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('Unexpected error in loop' || SQLERRM);
END;
l_line_exist := FALSE;
DBMS_OUTPUT.put_line ('HDR Commit' || x_return_status);
p_line_tbl.DELETE;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Unexpected error' || SQLERRM);
END;
/
DECLARE
p_line_tbl apps.oe_order_pub.line_tbl_type;
x_line_out_tbl apps.oe_order_pub.line_tbl_type;
p_header_rec apps.oe_order_pub.header_rec_type;
x_header_out_rec apps.oe_order_pub.header_rec_type;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_msg_dummy VARCHAR2 (4000);
l_header_id NUMBER;
l_org_id NUMBER;
l_index NUMBER := 0;
l_rowid ROWID;
l_seq_num NUMBER;
l_line_exist BOOLEAN := FALSE;
l_index_hdr NUMBER := 1;
l_close_cc NUMBER;
CURSOR cur_cancel_line (p_header_id IN NUMBER)
IS
SELECT oola.*
FROM apps.oe_order_lines_all oola
WHERE oola.header_id = p_header_id
AND oola.flow_status_code NOT IN ('CANCELLED', 'CLOSED')
AND ordered_item IN
('X6539-R6',
'X1845A-R6',
'X1848A-R5',
'SP-6530-R6',
'X6530-R6',
'X881-R6',
'X516B-R6',
'X3194-R6',
'X3187-R5',
'X3187-R5',
'X8533-R5',
'X8533-R5',
'SP-511',
'X3194-R6',
'SP-BASE1025A-R5',
'X3187-R5',
'X3187-R5',
'X3195-R5',
'X516B-R6',
'X1866-R6',
'X3194-R6',
'SP-516A-R6',
'X6539-R6',
'X6530-R6',
'SP-6536-R6',
'X6558-R6',
'X6539-R6',
'X1849A-R5',
'X6529-R6',
'X6539-R6',
'X6518A-R6',
'X1849A-R5',
'X1942A-R6',
'X6556-R6',
'X1848A-R5',
'X6557-R6',
'X6557-R6',
'X6529-R6',
'X6539-R6',
'X1848A-R5',
'X1848A-R5',
'SP-6530-R6',
'X6526',
'X6526',
'X1848A-R5',
'X6539-R6',
'X6539-R6',
'X516A-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'SP-516A-R6',
'X516B-R6',
'X6529-R6',
'X6539-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'SP-6524-R6',
'SP-3123-R6',
'X1849A-R5',
'X6539-R6',
'X1849A-R5',
'X6536-R6',
'X1848A-R5',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X1423A-R5',
'X1418A-R6',
'X3280-R6',
'X3280-R6',
'X1849A-R5',
'X6529-R6',
'SP-511',
'X1867-R6',
'X1867-R6',
'X6539-R6',
'X6539-R6',
'SP-6536-R6',
'X516A-R6',
'X516A-R6',
'SP-3194-R6',
'X8711-R6',
'X516A-R6',
'X8533-R5',
'SP-516A-R6',
'X516B-R6',
'SP-6536-R6',
'SP-6530-R6',
'X1845A-R6',
'SP-6530-R6',
'SP-6536-R6',
'SP-6536-R6',
'SP-6530-R6',
'X1845A-R6',
'X1845A-R6',
'X6529-R6',
'SP-6524-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X6518A-R6',
'X6530',
'X6530-R6',
'X6530-R6',
'X1946A-R6',
'X1949A-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'SP-511',
'X3210-R5',
'X3210-R5',
'X516A-R6',
'SP-511',
'X1423A-R5',
'X8533-R5',
'X8533-R5',
'X8533-R5',
'X3194-R6',
'X3201A-R6',
'X516A-R6',
'X8711-R6',
'X516A-R6',
'X8533-R5',
'X1867-R6',
'X1423A-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X1866-R6',
'X516B-R6',
'X6529-R6',
'X6529-R6',
'X6539-R6',
'X6537-R6',
'X6539-R6',
'SP-6536-R6',
'X6539-R6',
'X1845A-R6',
'X6539-R6',
'X6529-R6',
'X6539-R6',
'SP-6530-R6',
'X6539-R6',
'X1848A-R5',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X1845A-R6',
'X6529-R6',
'SP-6530-R6',
'X1941A-R6',
'X6556-R6',
'X1949A-R5',
'X6530-R6',
'X6539-R6',
'X1846-R6',
'X1847-R6',
'X1949A-R5',
'X1940A-R6',
'X6530-R6',
'X6539-R6',
'X3210-R5',
'X1423B-R5',
'X1423B-R5',
'X516A-R6',
'SP-3194-R6',
'SP-3194-R6',
'X8534-R6',
'X3190-R5',
'SP-3194-R6',
'X516A-R6',
'X516A-R6',
'SP-3200A-R6',
'SP-516A-R6',
'X1423A-R5',
'X3194-R6',
'X3188A-R5',
'X3203A-R6',
'X1563C-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X1845A-R6',
'X6529-R6',
'X1849A-R5',
'SP-6530-R6',
'X6529-R6',
'X1848A-R5',
'X1849A-R5',
'SP-6530-R6',
'SP-6536-R6',
'X6539-R6',
'X1846-R6',
'X6503B',
'X6539-R6',
'X6530-R6',
'SP-751A-R6',
'X3195-R5',
'X8533-R5',
'SP-3195-R5',
'X516A-R6',
'X8533-R5',
'X8533-R5',
'X1413A-R6',
'X516B-R6',
'X3200A-R6',
'X3200A-R6',
'X516B-R6',
'SP-516A-R6',
'X516B-R6',
'X516B-R6',
'X1849A-R5',
'X1849A-R5',
'X1949A-R5',
'X6539-R6',
'X6523-R6',
'X6523',
'SP-3123-R6',
'X3148A-R6',
'X6539-R6',
'X6539-R6',
'X6539-R6',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'X6553-R6',
'X6539-R6',
'X6539-R6',
'X6529-R6',
'X6539-R6',
'X6539-R6',
'X6529',
'SP-6536-R6',
'X6530-R6',
'X1423B-R5',
'SP-3194-R6',
'X516A-R6',
'X516A-R6',
'X3280-R6',
'X516B-R6',
'X3210-R5',
'X516B-R6',
'X3194-R6',
'X1031A',
'X6539-R6',
'X6558-R6',
'X1949A-R5',
'X1945A-R6',
'X6510A-R6',
'X1849A-R5',
'X516A-R6',
'X516B-R6',
'X3210-R5',
'X6539-R6',
'SP-6530-R6',
'SP-516A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3188A-R5',
'X3188A-R5',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'SP-516A-R6',
'X516B-R6',
'X3178A-R6',
'X3178A-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X516B-R6',
'X3178A-R6',
'X516B-R6',
'SP-516A-R6',
'SP-516A-R6',
'X3178A-R6',
'X516A-R6',
'X3188A-R5',
'X3188A-R5',
'X516A-R6',
'X516A-R6',
'X3188A-R5',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'SP-516A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'SP-516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X3178A-R6',
'X3178A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X3178A-R6',
'X516A-R6',
'SP-516A-R6',
'X3178A-R6',
'X3178A-R6',
'X516A-R6',
'X516A-R6',
'X3188A-R5',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X3188A-R5',
'X516A-R6',
'X3178A-R6',
'SP-516A-R6',
'X3178A-R6',
'X3178A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'SP-516A-R6',
'X516A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X3178A-R6',
'X516A-R6',
'X3178A-R6',
'X1949A-R5',
'X3178A-R6',
'X516A-R6',
'X3178A-R6',
'SP-516A-R6',
'X1949A-R5',
'X516A-R6',
'X1949A-R5',
'X3178A-R6',
'X3188A-R5',
'X1949A-R5',
'SP-232A',
'X1949A-R5',
'X1949A-R5',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X1949A-R5',
'X1949A-R5',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'SP-511',
'X516A-R6',
'X516A-R6',
'X516A-R6',
'SP-266B',
'X3178A-R6',
'X3187-R5',
'X3187-R5',
'X3178A-R6',
'X516A-R6',
'SP-3194-R6',
'SP-3178A-R6',
'SP-3195-R5',
'SP-3195-R5',
'X516A-R6',
'X516A-R6',
'X3188A-R5',
'SP-3194-R6',
'X516A-R6',
'X3187-R5',
'X3200A-R6',
'X3200A-R6',
'X3187-R5',
'SP-3194-R6',
'SP-3194-R6',
'X516A-R6',
'X3194-R6',
'X3194-R6',
'X516A-R6',
'X516A-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3188A-R6',
'SP-3195-R5',
'X3194-R6',
'X3280-R6',
'X3280-R6',
'X3280-R6',
'X3280-R6',
'X1949A-R5',
'X1949A-R5',
'X516A-R6',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'X1949A-R5',
'X1949A-R5',
'X3201A-R6',
'X516A-R6',
'X516A-R6',
'X3194-R6',
'X516A-R6',
'SP-225B',
'X3195-R5',
'X3178A-R6',
'X3188A-R5',
'X3195-R5',
'X3194-R6',
'X516A-R6',
'X516A-R6',
'X3187-R5',
'X3187-R5',
'SP-3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'X516A-R6',
'X516A-R6',
'SP-3194-R6',
'SP-3194-R6',
'X3194-R6',
'X3187-R5',
'X3187-R5',
'X1949A-R5',
'X1949A-R5',
'SP-3195-R5',
'X3178A-R6',
'SP-3188A-R6',
'X3194-R6',
'SP-3194-R6',
'X516A-R6',
'SP-511',
'X516A-R6',
'SP-3195-R5',
'SP-3194-R6',
'X1086A-R6',
'SP-511',
'X516A-R6',
'X3188A-R5',
'X3200A-R6',
'X3200A-R6',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X3194-R6',
'SP-3195-R5',
'X516A-R6',
'SP-3187-R5',
'SP-3187-R5',
'SP-3187-R5',
'SP-3187-R5',
'SP-3187-R5',
'SP-511',
'X3194-R6',
'X3195-R5',
'SP-3200A-R6',
'X516A-R6',
'X3194-R6',
'SP-511',
'X516A-R6',
'X516A-R6',
'X3187-R5',
'X3187-R5',
'X516A-R6',
'X3195-R5',
'X3195-R5',
'SP-3194-R6',
'X516A-R6',
'X3187-R5',
'X3178A-R6',
'SP-721A',
'SP-3187-R5',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X3195-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3194-R6',
'X1949A-R5',
'X3195-R5',
'SP-3200A-R6',
'SP-3200A-R6',
'X3195-R5',
'X3195-R5',
'X3187-R5',
'X3187-R5',
'SP-3188A-R5',
'X3194-R6',
'X3194-R6',
'SP-3194-R6',
'SP-3194-R6',
'X1949A-R5',
'X1949A-R5',
'X3188A-R5',
'X3194-R6',
'X3194-R6',
'SP-511',
'SP-3280-R6',
'SP-3280-R6',
'SP-3200A-R6',
'X3200A-R6',
'X3187-R5',
'X3195-R5',
'X3194-R6',
'SP-511',
'X1846-R6',
'X3187-R5',
'X3187-R5',
'X3280-R6',
'X1949A-R5',
'X3187-R5',
'X3187-R5',
'SP-3188A-R6',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3194-R6',
'SP-511',
'X3187-R5',
'X3188A-R5',
'SP-511',
'X3194-R6',
'SP-3188',
'X3210-R5',
'X3210-R5',
'SP-3195-R5',
'X3195-R5',
'X266B-R5',
'X1949A-R5',
'X266B-R5',
'SP-266B-R5',
'X1949A-R5',
'SP-511',
'SP-3194-R6',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1949A-R5',
'X3194-R6',
'X3194-R6',
'SP-3194-R6',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'SP-266B',
'X3187-R5',
'X3187-R5',
'X3178A-R6',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X266B-R5',
'X266B-R5',
'X1846-R6',
'X1949A-R5',
'X266B-R5',
'SP-266B-R5',
'X266B-R5',
'X1949A-R5',
'X1949A-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X3188A-R5',
'SP-246A',
'X266B-R5',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3194-R6',
'X3187-R5',
'X3188A-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X266B-R5',
'X266B-R5',
'SP-266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1949A-R5',
'SP-266B-R5',
'X266B-R5',
'X266B-R5',
'SP-266B-R5',
'X266B-R5',
'X3201A-R6',
'X266B-R5',
'SP-3195-R5',
'X266B-R5',
'X3201A-R6',
'X3280-R6',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X3187-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1949A-R5',
'X1949A-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X3187-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X3194-R6',
'SP-266B-R5',
'X266B-R5',
'X266B-R5',
'SP-266B',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'SP-245A',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'SP-266B',
'X266B-R5',
'X266B-R5',
'X1945A-R6',
'X266B-R5',
'SP-3200A-R6',
'X3202A-R6',
'SP-3146A-R5',
'SP-266A',
'X3194-R6',
'X3195-R5',
'SP-511',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1945A-R6',
'X1949A-R5',
'X1949A-R5',
'SP-266A',
'X1846-R6',
'SP-266B',
'SP-266A',
'SP-232A',
'X1940A-R6',
'X1949A-R5',
'X1949A-R5',
'X266B-R5',
'X1940A-R6',
'X1940A-R6',
'SP-F87-RKBASE',
'X1949A-R5',
'X3188A-R5',
'X3187-R5',
'SP-266A',
'X3210-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3210-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1846-R6',
'X1945A-R6',
'SP-266A',
'X266B-R5',
'X3178A-R6',
'X1945A-R6',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1941A-R6',
'X266B-R5',
'SP-266A',
'SP-266A',
'X266B-R5',
'X3146A-R5',
'X1940A-R6',
'X266B-R5',
'SP-266B',
'X266B-R5',
'X1940A-R6',
'X266B-R5',
'X266B-R5',
'X1945A-R6',
'SP-266A',
'SP-511',
'X3194-R6',
'X266B-R5',
'SP-266A',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X3202A-R6',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X3187-R5',
'X3187-R5',
'X3188A-R5',
'X1941A-R6',
'X266B-R5',
'X3210-R5',
'X266B-R5',
'X1846-R6',
'X1941A-R6',
'X266B-R5',
'X266B-R5',
'X1941A-R6',
'X3188A-R5',
'SP-228A',
'X266B-R5',
'SP-266B',
'X266B-R5',
'X1945A-R6',
'X1949A-R5',
'X266B-R5',
'X266B-R5',
'X1941A-R6',
'X266B-R5',
'X266B-R5',
'SP-266A',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'SP-266A',
'X266B-R5',
'SP-3194-R6',
'X1846-R6',
'X3201A-R6',
'SP-266B-R5',
'X266B-R5',
'X270A',
'X266B-R5',
'X1846-R6',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'SP-3201A-R6',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1945A-R6',
'X266B-R5',
'SP-511',
'X1949A-R5',
'X1949A-R5',
'X266B-R5',
'X266B-R5',
'SP-266A',
'SP-266A',
'X266B-R5',
'X266B-R5',
'SP-266A',
'SP-225B',
'SP-3195-R5',
'X3210-R5',
'X3195-R5',
'X3195-R5',
'X3187-R5',
'X3187-R5',
'X3188A-R5',
'SP-266A',
'SP-266B',
'X266B-R5',
'X266B-R5',
'SP-3146A-R5',
'SP-266B',
'SP-266B-R5',
'X266B-R5',
'SP-3195-R5',
'X266B-R5',
'SP-FAS940-BASE',
'X1949A-R5',
'X1949A-R5',
'X1940A-R6',
'X266B-R5',
'X266B-R5',
'X1940A-R6',
'SP-3194-R6',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X3187-R5',
'X266B-R5',
'X1941A-R6',
'SP-8711',
'SP-3146A-R5',
'X1940A-R6',
'X8715',
'X3188A-R5',
'X3187-R5',
'X266B-R5',
'X3195-R5',
'X3195-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'SP-3194-R6',
'X266B-R5',
'X266B-R5',
'X3201A-R6',
'X266B-R5',
'X3194-R6',
'X266B-R5',
'X3195-R5',
'X3195-R5',
'X3195-R5',
'X3195-R5',
'SP-3195-R5',
'SP-3195-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X1949A-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'SP-270A',
'X270A',
'X266B-R5',
'X266B-R5',
'SP-266B',
'SP-228A',
'X3187-R5',
'X3195-R5',
'X3195-R5',
'X266B-R5',
'X266B-R5',
'X1940A-R6',
'SP-3195-R5',
'X266B-R5',
'X266B-R5',
'X3280-R6',
'X266B-R5',
'SP-246A',
'X270A',
'X266B-R5',
'X266B-R5',
'SP-1025D',
'X266B-R5',
'X1945A-R6',
'X1945A-R6',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'SP-266B',
'X266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'SP-266B-R5',
'X266B-R5',
'SP-266B',
'X266B-R5',
'SP-266B',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X266B-R5',
'X1949A-R5',
'SP-5601A',
'X1670A-R5',
'X266B-R5',
'SP-266A',
'X266B-R5'
);
CURSOR cur_cancel_hdr
IS
SELECT *
FROM apps.oe_order_headers_all ooha
WHERE flow_status_code NOT IN ('CANCELLED', 'CLOSED')
AND order_number IN
(8000695980,
8000695952,
8000695682,
8000695425,
8000695425,
8000695425,
8000693387,
8000694069,
8000694122,
8000694122,
8000694124,
8000694124,
8000695086,
8000695094,
8000694641,
8000695164,
8000695164,
8000695217,
8000695287,
8000695369,
8000695384,
8000695345,
8000695307,
8000695307,
8000695307,
8000694279,
8000695371,
8000695440,
8000695501,
8000695502,
8000695502,
8000695510,
8000695520,
8000695157,
8000695231,
8000695239,
8000695239,
8000695273,
8000695301,
8000695280,
8000695322,
8000695370,
8000695279,
8000695279,
8000695286,
8000695392,
8000695392,
8000695040,
8000693745,
8000694796,
8000694802,
8000694855,
8000694746,
8000694851,
8000694554,
8000694867,
8000694867,
8000694887,
8000694887,
8000694887,
8000694898,
8000694663,
8000694807,
8000694806,
8000693768,
8000694749,
8000694273,
8000694273,
8000694273,
8000694504,
8000694414,
8000694518,
8000694291,
8000694291,
8000694432,
8000694398,
8000693856,
8000693929,
8000693929,
8000694152,
8000694152,
8000694161,
8000693353,
8000693353,
8000693374,
8000693540,
8000693714,
8000693722,
8000693830,
8000693761,
8000693037,
8000693037,
8000693398,
8000693730,
8000693730,
8000693730,
8000693730,
8000693796,
8000693809,
8000693896,
8000693896,
8000692879,
8000693031,
8000693534,
8000693592,
8000693275,
8000693275,
8000693316,
8000693316,
8000693707,
8000693707,
8000689393,
8000692215,
8000692215,
8000692337,
8000693312,
8000692847,
8000692848,
8000692957,
8000692975,
8000692978,
8000693118,
8000693118,
8000693118,
8000693215,
8000693341,
8000693347,
8000693365,
8000693421,
8000693225,
8000692557,
8000692709,
8000692735,
8000692839,
8000693148,
8000693202,
8000685083,
8000692795,
8000692795,
8000692802,
8000692802,
8000692942,
8000692942,
8000692942,
8000693132,
8000693287,
8000693346,
8000693371,
8000693388,
8000693388,
8000693405,
8000693168,
8000692583,
8000692644,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693076,
8000693123,
8000692738,
8000693042,
8000693160,
8000693178,
8000693196,
8000693202,
8000693285,
8000693101,
8000693000,
8000693000,
8000693210,
8000693210,
8000690676,
8000690794,
8000690794,
8000692060,
8000692226,
8000692226,
8000692375,
8000692379,
8000692418,
8000692494,
8000692524,
8000692779,
8000692814,
8000692659,
8000692609,
8000692696,
8000692366,
8000692702,
8000692598,
8000692744,
8000692719,
8000691893,
8000692149,
8000692298,
8000692429,
8000692527,
8000692550,
8000692825,
8000692830,
8000692830,
8000692830,
8000692831,
8000692553,
8000692667,
8000692745,
8000690690,
8000691980,
8000691989,
8000691991,
8000692070,
8000692207,
8000692220,
8000692283,
8000692170,
8000692122,
8000692122,
8000692145,
8000692212,
8000691633,
8000691538,
8000691976,
8000692369,
8000691889,
8000691977,
8000691977,
8000691977,
8000692365,
8000692039,
8000691684,
8000692152,
8000692160,
8000692231,
8000692231,
8000692231,
8000692269,
8000692269,
8000692269,
8000691778,
8000691728,
8000691728,
8000692054,
8000692380,
8000692250,
8000691195,
8000691403,
8000691465,
8000691561,
8000691928,
8000691518,
8000690803,
8000691677,
8000691488,
8000691739,
8000691739,
8000691818,
8000691539,
8000691539,
8000691798,
8000691537,
8000691383,
8000691102,
8000691155,
8000691094,
8000691094,
8000690738,
8000690144,
8000686885,
8000685485,
8000682871,
8000682251,
8000680714,
8000678470,
8000679436,
8000680242,
8000679507,
8000679136,
8000679321,
8000679321,
8000679325,
8000679337,
8000679437,
8000678923,
8000678923,
8000678905,
8000678295,
8000678460,
8000677947,
8000678037,
8000677722,
8000675816,
8000675852,
8000676206,
8000675615,
8000675763,
8000675820,
8000675569,
8000672224,
8000672632,
8000672890,
8000671930,
8000671511,
8000671382,
8000671124,
8000669922,
8000668837,
8000668299,
8000668316,
8000668392,
8000668244,
8000665671,
8000665671,
8000665695,
8000665591,
8000664897,
8000663612,
8000662426,
8000662714,
8000661241,
8000659546,
8000660738,
8000661113,
8000661269,
8000661282,
8000661384,
8000661421,
8000659753,
8000658742,
8000658547,
8000658356,
8000657850,
8000656649,
8000656114,
8000654758,
8000654583,
8000653889,
8000654460,
8000651771,
8000651683,
8000651683,
8000649665,
8000646184,
8000647570,
8000646354,
8000647498,
8000647611,
8000646495,
8000646289,
8000646083,
8000645626,
8000644934,
8000644400,
8000642619,
8000640541,
8000641640,
8000640418,
8000640958,
8000640101,
8000639872,
8000638261,
8000634608,
8000630318,
8000628348,
8000627842,
8000627405,
8000624085,
8000624085,
8000620069,
8000620250,
8000619412,
8000619412,
8000617715,
8000616349,
8000616157,
8000612503,
8000607998,
8000606049,
8000606347,
8000605960,
8000605314,
8000410782,
8000601837,
8000601506,
8000601836,
8000601015,
8000599664,
8000600925,
8000601067,
8000601083,
8000601083,
8000600596,
8000599049,
8000600196,
8000599616,
8000598523,
8000598858,
8000598967,
8000598967,
8000597903,
8000598417,
8000598417,
8000598101,
8000597047,
8000596522,
8000594486,
8000594440,
8000593819,
8000593819,
8000593819,
8000593819,
8000593819,
8000593819,
8000593838,
8000593859,
8000593429,
8000593431,
8000593431,
8000593431,
8000593431,
8000593704,
8000593704,
8000591932,
8000592632,
8000592641,
8000592646,
8000592444,
8000592444,
8000590782,
8000589600,
8000589642,
8000589006,
8000588502,
8000588577,
8000587464,
8000586465,
8000585944,
8000585808,
8000584928,
8000582870,
8000582495,
8000581935,
8000581935,
8000581626,
8000581639,
8000581639,
8000581519,
8000579940,
8000580361,
8000580361,
8000580293,
8000579627,
8000579627,
8000579182,
8000579182,
8000579195,
8000578071,
8000578214,
8000577946,
8000577252,
8000577259,
8000577266,
8000577349,
8000576257,
8000576328,
8000576708,
8000576731,
8000575951,
8000576297,
8000573707,
8000574958,
8000574786,
8000574786,
8000574216,
8000574621,
8000574621,
8000573714,
8000573229,
8000571684,
8000570211,
8000570232,
8000570232,
8000570232,
8000570232,
8000570204,
8000570214,
8000569520,
8000568513,
8000568358,
8000568435,
8000567595,
8000567735,
8000567339,
8000566976,
8000566976,
8000566867,
8000566592,
8000566592,
8000565564,
8000565180,
8000564674,
8000564950,
8000563737,
8000561119,
8000560430,
8000560397,
8000560397,
8000559417,
8000558442,
8000558442,
8000558442,
8000558442,
8000558500,
8000558321,
8000557894,
8000545057,
8000545057,
8000557965,
8000557965,
8000556054,
8000556054,
8000557517,
8000556953,
8000556087,
8000556375,
8000555263,
8000555590,
8000555590,
8000552671,
8000549578,
8000549578,
8000549621,
8000548616,
8000547730,
8000544686,
8000544811,
8000543132,
8000541618,
8000541677,
8000541137,
8000540214,
8000539192,
8000539192,
8000538799,
8000539000,
8000538075,
8000538076,
8000538432,
8000538463,
8000538463,
8000538073,
8000538073,
8000536265,
8000536387,
8000535279,
8000534973,
8000534857,
8000534455,
8000533776,
8000531844,
8000531844,
8000532142,
8000530975,
8000531614,
8000531438,
8000531326,
8000531390,
8000531356,
8000531060,
8000531054,
8000530683,
8000530466,
8000530536,
8000530225,
8000530240,
8000530242,
8000529573,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529617,
8000529114,
8000529252,
8000529368,
8000529519,
8000529590,
8000529590,
8000529590,
8000529590,
8000529590,
8000529590,
8000529590,
8000529590,
8000529590,
8000529007,
8000528841,
8000528023,
8000528052,
8000527688,
8000527619,
8000527964,
8000527613,
8000527613,
8000525728,
8000527589,
8000527574,
8000527632,
8000527187,
8000526886,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526670,
8000526955,
8000526955,
8000526955,
8000526981,
8000527094,
8000527094,
8000527211,
8000527211,
8000527243,
8000527255,
8000527156,
8000526834,
8000526058,
8000526459,
8000526555,
8000526815,
8000526416,
8000526016,
8000526053,
8000523985,
8000525924,
8000525744,
8000525303,
8000524893,
8000524480,
8000524969,
8000523728,
8000524259,
8000524312,
8000523856,
8000524185,
8000523730,
8000523751,
8000523886,
8000523892,
8000523910,
8000523729,
8000523672,
8000523343,
8000523384,
8000523316,
8000523245,
8000523089,
8000522963,
8000523072,
8000523081,
8000523081,
8000522544,
8000522624,
8000521642,
8000522175,
8000521677,
8000521800,
8000521888,
8000521919,
8000519144,
8000521356,
8000521010,
8000520659,
8000520865,
8000520658,
8000520723,
8000519792,
8000519913,
8000519913,
8000520050,
8000375695,
8000518985,
8000519036,
8000517822,
8000516220,
8000518859,
8000517854,
8000517566,
8000517450,
8000517556,
8000512581,
8000515778,
8000515979,
8000516106,
8000516117,
8000515658,
8000515329,
8000515827,
8000515341,
8000515458,
8000515686,
8000515686,
8000514291,
8000515374,
8000515143,
8000514642,
8000514919,
8000514952,
8000515062,
8000515062,
8000514535,
8000514121,
8000514121,
8000513830,
8000514198,
8000513742,
8000513742,
8000513407,
8000513474,
8000513474,
8000513474,
8000513474,
8000513474,
8000513474,
8000513474,
8000513474,
8000513474,
8000513486,
8000513486,
8000513486,
8000513486,
8000513486,
8000513486,
8000513486,
8000513486,
8000513486,
8000513718,
8000513776,
8000513776,
8000513742,
8000513744,
8000511928,
8000511928,
8000512917,
8000513090,
8000512647,
8000512851,
8000512897,
8000512853,
8000509089,
8000512104,
8000512359,
8000512156,
8000510506,
8000512517,
8000512066,
8000512140,
8000512230,
8000511874,
8000506712,
8000511485,
8000511073,
8000510871,
8000510507,
8000510327,
8000510296,
8000510481,
8000510491,
8000510491,
8000510565,
8000510587,
8000509837,
8000509920,
8000509857,
8000509335,
8000509335,
8000509336,
8000509083,
8000508784,
8000508818,
8000508996,
8000508818,
8000509006,
8000508718,
8000508624,
8000508373,
8000508311,
8000507780,
8000375303,
8000365821,
8000508005,
8000507407,
8000507407,
8000505473,
8000507432,
8000507557,
8000507390,
8000507139,
8000507186,
8000507168,
8000507168,
8000507168,
8000507168,
8000506736,
8000505951,
8000505812,
8000506169,
8000503783,
8000505628,
8000505289,
8000505430,
8000505168,
8000505053,
8000504882,
8000503776,
8000504057,
8000503720,
8000502745,
8000502605,
8000502415,
8000502530,
8000501955,
8000502111,
8000502205,
8000502205,
8000501709,
8000501188,
8000501252,
8000501390,
8000501458,
8000500986,
8000500461,
8000500749,
8000501157,
8000501175,
8000500398,
8000500398,
8000500410,
8000500410,
8000500410,
8000500236,
8000500262,
8000500352,
8000498034,
8000499541,
8000499618,
8000498495,
8000499127,
8000498859,
8000499077,
8000498718,
8000499245,
8000499245,
8000498815,
8000497761,
8000497923,
8000497658,
8000497538,
8000497626,
8000497626,
8000497626,
8000497626,
8000497626,
8000497626,
8000497676,
8000497643,
8000497694,
8000497049,
8000497258,
8000497340,
8000494237,
8000494237,
8000496553,
8000496604,
8000496604,
8000496755,
8000496755,
8000496150,
8000496139,
8000496382,
8000496409,
8000496108,
8000496047,
8000496024,
8000495717,
8000495499,
8000494331,
8000494879,
8000495069,
8000495094,
8000495094,
8000495094,
8000495095,
8000495095,
8000494577,
8000493476,
8000494173,
8000494190,
8000494228,
8000494228,
8000494228,
8000494228,
8000493415,
8000493560,
8000493561,
8000493544,
8000493546,
8000493661,
8000493396,
8000493005,
8000493033,
8000493146,
8000492683,
8000492683,
8000492738,
8000492738,
8000492614,
8000492104,
8000492158,
8000491796,
8000491793,
8000491932,
8000491041,
8000491451,
8000491496,
8000491273,
8000491112,
8000490980,
8000490533,
8000490533,
8000456170,
8000456170,
8000447307,
8000447307,
8000447307,
8000437244,
8000437244,
8000437244,
8000431058,
8000431058,
8000431058,
8000419127,
8000419127,
8000419127,
8000419127,
8000419127,
8000406831,
8000406831,
8000406831,
8000406831,
8000398741,
8000384601,
8000384778,
8000259519,
8000153289,
8000158676
);
BEGIN
--apps.fnd_client_info.set_org_context (153);
--apps.oe_msg_pub.initialize;
--fnd_global.apps_initialize(47866,50603,660);
FOR c_cur_cancel_hdr IN cur_cancel_hdr
LOOP
apps.fnd_client_info.set_org_context (c_cur_cancel_hdr.org_id);
apps.oe_msg_pub.initialize;
l_index := 0;
DBMS_OUTPUT.put_line ('Header id----' || c_cur_cancel_hdr.header_id);
BEGIN
FOR rec_cancel_line IN cur_cancel_line (c_cur_cancel_hdr.header_id)
LOOP
l_index := l_index + 1;
l_line_exist := TRUE;
p_line_tbl (l_index) := apps.oe_order_pub.g_miss_line_rec;
p_line_tbl (l_index).header_id := c_cur_cancel_hdr.header_id;
p_line_tbl (l_index).line_id := rec_cancel_line.line_id;
p_line_tbl (l_index).ordered_quantity := 0;
p_line_tbl (l_index).flow_status_code := 'CANCELLED';
p_line_tbl (l_index).change_reason := 'CANCEL';
p_line_tbl (l_index).change_comments :=
'Change in flow_status_code';
p_line_tbl (l_index).operation := oe_globals.g_opr_update;
p_line_tbl (l_index).last_updated_by := -1;
p_line_tbl (l_index).last_update_date := SYSDATE;
END LOOP;
DBMS_OUTPUT.put_line ('line index' || l_index);
IF l_line_exist
THEN
apps.oe_order_pub.process_line
(p_line_tbl => p_line_tbl,
x_line_out_tbl => x_line_out_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ( 'Lines x_return_status - x_msg_count '
|| x_return_status
|| ' ---- '
|| x_msg_count
);
IF (x_return_status <> 'S')
THEN
FOR j IN 1 .. x_msg_count
LOOP
apps.oe_msg_pub.get (j,
fnd_api.g_false,
x_msg_data,
x_msg_dummy
);
DBMS_OUTPUT.put_line ('Error' || x_msg_data);
END LOOP;
ELSE
COMMIT;
END IF;
END IF;
SELECT COUNT (*)
INTO l_close_cc
FROM apps.oe_order_lines_all
WHERE header_id = c_cur_cancel_hdr.header_id
AND flow_status_code = 'CLOSED';
p_header_rec := apps.oe_order_pub.g_miss_header_rec;
p_header_rec.header_id := c_cur_cancel_hdr.header_id;
IF l_close_cc >= 1
THEN
p_header_rec.flow_status_code := 'CLOSED';
DBMS_OUTPUT.put_line (' LINE STATUS-CLODE123');
ELSE
p_header_rec.flow_status_code := 'CANCELLED';
END IF;
p_header_rec.change_reason := 'CANCEL';
p_header_rec.change_comments := 'Change in flow_status_code';
p_header_rec.last_updated_by := -1;
p_header_rec.last_update_date := SYSDATE;
p_header_rec.operation := oe_globals.g_opr_update;
-- l_index_hdr := l_index_hdr + 1;
apps.oe_order_pub.process_header
(p_header_rec => p_header_rec,
x_header_out_rec => x_header_out_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ( 'HDR x_return_status - x_msg_count '
|| x_return_status
|| ' ---- '
|| x_msg_count
);
IF (x_return_status <> 'S')
THEN
FOR j IN 1 .. x_msg_count
LOOP
apps.oe_msg_pub.get (j,
fnd_api.g_false,
x_msg_data,
x_msg_dummy
);
DBMS_OUTPUT.put_line ('Error' || x_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error1' || x_return_status);
ELSE
DBMS_OUTPUT.put_line ('Commit' || x_return_status);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('Unexpected error in loop' || SQLERRM);
END;
l_line_exist := FALSE;
DBMS_OUTPUT.put_line ('HDR Commit' || x_return_status);
p_line_tbl.DELETE;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Unexpected error' || SQLERRM);
END;
/
EXIT;
Saturday, September 3, 2011
IMPORTANT FND Queries
Concurrent Program Attached to which responsibility
----------------------------------------------------------------
select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
--and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper('concurrent program name');
--------
Long running program taking more than 30 mins
-------------------------------------------------
SELECT fr.RESPONSIBILITY_NAME,
fcp.concurrent_program_id,
DECODE(fcptl.user_concurrent_program_name,'Report Set','Request Set'||'-'||fcr.description,fcptl.user_concurrent_program_name) user_concurrent_program_name,
fcptl.DESCRIPTION,
DECODE(fcr.STATUS_CODE,'C','Completed','R','Running','W','Paused','E','Error','G','Warning', fcr.STATUS_CODE) STATUS_CODE,
DECODE(fcr.PHASE_CODE,'C','Completed','R','Running',fcr.PHASE_CODE) PHASE_CODE,
fcr.request_id,
DECODE(phase_code,'R',ROUND(((SYSDATE-ACTUAL_START_DATE)*60*24),0),ROUND(((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*60*24),0)) diffmin,
TO_CHAR(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') ACTUAL_START_DATE,
TO_CHAR(ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') ACTUAL_COMPLETION_DATE,
fcr.ARGUMENT1,fcr.ARGUMENT2,fcr.ARGUMENT_TEXT
FROM apps.fnd_responsibility_tL fr,
apps.fnd_executables fe,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcptl,
apps.fnd_user fu,
apps.fnd_concurrent_requests fcr
WHERE 1=1
AND fcr.requested_by + 0 = fu.user_id
AND TRUNC(ACTUAL_START_DATE) >= TRUNC(SYSDATE)-1
AND fcr.concurrent_program_id =fcp.concurrent_program_id
AND FCR.PROGRAM_APPLICATION_ID = fcptl.application_id
AND fcp.concurrent_program_id = fcptl.concurrent_program_id
AND fcp.executable_application_id = fe.application_id
AND fcp.executable_id = fe.executable_id
AND fcr.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID
AND fcr.responsibility_id = fr.responsibility_id
AND fcr.phase_code = 'R'
and DECODE(phase_code,'R',ROUND(((SYSDATE-ACTUAL_START_DATE)*60*24),0),ROUND(((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*60*24),0)) > 30
--AND fcptl.user_concurrent_program_name LIKE 'NAPP: CMAT Concurrent Program%'
ORDER BY fcr.request_id
Long running Query
-----------------------
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = 57550441--57550441
AND a.phase_code = 'R';
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE SID = 6547)--6584)
ORDER BY piece;
SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
DECODE( request, 0, 'NO', 'YES' ) WAITER, id1, id2, lmode /* 6 is exclusive lock. For others, refer to SV10 */
FROM v$lock
WHERE request > 0 OR block > 0
ORDER BY block DESC
SELECT * FROM v$access WHERE object = upper('napp_pt01_batch_pkg')
select * from v$lock where block <> 0 ;
--------
net
-------
SELECT
fu.user_name,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcpt.description,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.REQUEST_ID,
round((fcr.actual_completion_date – decode (trunc(fcr.request_date),fcr.requested_start_date,fcr.request_date,fcr.requested_start_date))*60*24) WaitTimeMIN,
DECODE(fcr.PHASE_CODE,'C','Completed','R','Running',fcr.PHASE_CODE) PHASE_CODE,
DECODE(fcr.STATUS_CODE,‘C’,‘Completed’,‘R’,‘Running’,‘W’,‘Paused’,‘E’,‘Error’,‘G’, ‘Warning’, fcr.STATUS_CODE) STATUS_CODE,
to_char(fcr.request_date,‘DD-MON-YYYY HH24:MI:SS’) request_date,
to_char(fcr.requested_start_date,‘DD-MON-YYYY HH24:MI:SS’) start_time,
to_char(fcr.actual_completion_date,‘DD-MON-YYYY HH24:MI:SS’) complete_time
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE 1=1
AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
AND fcpt.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND fcr.requested_by = fu.user_id
AND trunc(fcr.request_date) BETWEEN sysdate – 1 AND sysdate
AND fcr.status_code IN (‘E’,‘G’)
ORDER BY fcr.status_code,fcp.CONCURRENT_PROGRAM_NAME,fcr.REQUEST_ID;
--- Menu..............
select responsibility_name resp fm.menu_name
from fnd_responsibility_vl resp fnd_request_group_units rgu fnd_concurrent_programs_vl prog
fnd_menus fm
where resp.request_group_id = rgu.request_group_id
AND fm.menu_id = resp.menu_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = 'Financial Adjustments Report'
order by resp
how to find all indexes and their columns for tables, views and synonyms in oracle
**********************************************************************************
select b.uniqueness, a.index_name, a.table_name, a.column_name from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('table_name') order by a.table_name, a.index_name, a.column_position;
Query to find all columns for a table
****************************************************
Select * From All_Tab_Columns
WHERE TABLE_NAME='NAPP_OE_ACCESS_KEYS'
Query to find all the scheduled concurrent programs
**********************************************************
SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id;
----------------------------------------------------------------
select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
--and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper('concurrent program name');
--------
Long running program taking more than 30 mins
-------------------------------------------------
SELECT fr.RESPONSIBILITY_NAME,
fcp.concurrent_program_id,
DECODE(fcptl.user_concurrent_program_name,'Report Set','Request Set'||'-'||fcr.description,fcptl.user_concurrent_program_name) user_concurrent_program_name,
fcptl.DESCRIPTION,
DECODE(fcr.STATUS_CODE,'C','Completed','R','Running','W','Paused','E','Error','G','Warning', fcr.STATUS_CODE) STATUS_CODE,
DECODE(fcr.PHASE_CODE,'C','Completed','R','Running',fcr.PHASE_CODE) PHASE_CODE,
fcr.request_id,
DECODE(phase_code,'R',ROUND(((SYSDATE-ACTUAL_START_DATE)*60*24),0),ROUND(((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*60*24),0)) diffmin,
TO_CHAR(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') ACTUAL_START_DATE,
TO_CHAR(ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') ACTUAL_COMPLETION_DATE,
fcr.ARGUMENT1,fcr.ARGUMENT2,fcr.ARGUMENT_TEXT
FROM apps.fnd_responsibility_tL fr,
apps.fnd_executables fe,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcptl,
apps.fnd_user fu,
apps.fnd_concurrent_requests fcr
WHERE 1=1
AND fcr.requested_by + 0 = fu.user_id
AND TRUNC(ACTUAL_START_DATE) >= TRUNC(SYSDATE)-1
AND fcr.concurrent_program_id =fcp.concurrent_program_id
AND FCR.PROGRAM_APPLICATION_ID = fcptl.application_id
AND fcp.concurrent_program_id = fcptl.concurrent_program_id
AND fcp.executable_application_id = fe.application_id
AND fcp.executable_id = fe.executable_id
AND fcr.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID
AND fcr.responsibility_id = fr.responsibility_id
AND fcr.phase_code = 'R'
and DECODE(phase_code,'R',ROUND(((SYSDATE-ACTUAL_START_DATE)*60*24),0),ROUND(((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*60*24),0)) > 30
--AND fcptl.user_concurrent_program_name LIKE 'NAPP: CMAT Concurrent Program%'
ORDER BY fcr.request_id
Long running Query
-----------------------
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = 57550441--57550441
AND a.phase_code = 'R';
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE SID = 6547)--6584)
ORDER BY piece;
SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
DECODE( request, 0, 'NO', 'YES' ) WAITER, id1, id2, lmode /* 6 is exclusive lock. For others, refer to SV10 */
FROM v$lock
WHERE request > 0 OR block > 0
ORDER BY block DESC
SELECT * FROM v$access WHERE object = upper('napp_pt01_batch_pkg')
select * from v$lock where block <> 0 ;
--------
net
-------
SELECT
fu.user_name,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcpt.description,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.REQUEST_ID,
round((fcr.actual_completion_date – decode (trunc(fcr.request_date),fcr.requested_start_date,fcr.request_date,fcr.requested_start_date))*60*24) WaitTimeMIN,
DECODE(fcr.PHASE_CODE,'C','Completed','R','Running',fcr.PHASE_CODE) PHASE_CODE,
DECODE(fcr.STATUS_CODE,‘C’,‘Completed’,‘R’,‘Running’,‘W’,‘Paused’,‘E’,‘Error’,‘G’, ‘Warning’, fcr.STATUS_CODE) STATUS_CODE,
to_char(fcr.request_date,‘DD-MON-YYYY HH24:MI:SS’) request_date,
to_char(fcr.requested_start_date,‘DD-MON-YYYY HH24:MI:SS’) start_time,
to_char(fcr.actual_completion_date,‘DD-MON-YYYY HH24:MI:SS’) complete_time
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE 1=1
AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
AND fcpt.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND fcr.requested_by = fu.user_id
AND trunc(fcr.request_date) BETWEEN sysdate – 1 AND sysdate
AND fcr.status_code IN (‘E’,‘G’)
ORDER BY fcr.status_code,fcp.CONCURRENT_PROGRAM_NAME,fcr.REQUEST_ID;
--- Menu..............
select responsibility_name resp fm.menu_name
from fnd_responsibility_vl resp fnd_request_group_units rgu fnd_concurrent_programs_vl prog
fnd_menus fm
where resp.request_group_id = rgu.request_group_id
AND fm.menu_id = resp.menu_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = 'Financial Adjustments Report'
order by resp
how to find all indexes and their columns for tables, views and synonyms in oracle
**********************************************************************************
select b.uniqueness, a.index_name, a.table_name, a.column_name from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('table_name') order by a.table_name, a.index_name, a.column_position;
Query to find all columns for a table
****************************************************
Select * From All_Tab_Columns
WHERE TABLE_NAME='NAPP_OE_ACCESS_KEYS'
Query to find all the scheduled concurrent programs
**********************************************************
SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id;
Data fix to remove the responsibility end date from back end(Where data file is comma seprated .csv file
/*============================================================================+
File Name : NAPP_GLOBAL_RESP_FIX.sql
Object Name : NAPP_GLOBAL_RESP_FIX.sql
Description : One Time update script to update the resp end date
Revision History :
Ver Date Name Revision Description
=== ========= ============== ==========================================
1.0 03-DEC-2010 Neelima Created the program
+=============================================================================*/
SET PAGESIZE 0;
SET LINESIZE 10000;
SET SERVEROUTPUT ON SIZE 100000;
SET TRIMSPOOL ON;
SET DEFINE OFF;
DECLARE
l_file_handle UTL_FILE.FILE_TYPE;
l_eof CHAR;
l_user_name VARCHAR2 (150);
l_resp_name VARCHAR2 (500);
l_text VARCHAR2 (5000);
l_start_date DATE;
l_start_date_str VARCHAR2(50);
l_user_id number;
l_resp_id number;
l_resp_appl_id number;
l_resp_start_date date;
l_message varchar2(100);
l_dir_name varchar2(100);
BEGIN
BEGIN
select name into l_dir_name from v$database;
dbms_output.put_line('UTL file directory ''/usr/tmp/'l_dir_name);
END;
Fnd_Global.apps_initialize (1429,20420,1);
l_eof := 'N';
l_file_handle :=
UTL_FILE.FOPEN
('/usr/tmp/'l_dir_name
,'NAPP_GLOBAL_RESP_DATA.csv'
,'r'
);
LOOP
l_user_id := -1;
l_resp_id := -1;
l_resp_appl_id := -1;
l_message:='SUCCESS';
l_resp_start_date := null;
BEGIN
UTL_FILE.GET_LINE (l_file_handle, l_text);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_eof := 'Y';
END;
EXIT WHEN l_eof = 'Y';
l_user_name := SUBSTR (l_text, 1, INSTR (l_text, ',') - 1);
l_resp_name := REPLACE(SUBSTR (l_text
, INSTR (l_text, ',') + 1
, length(l_text)
),',','');
BEGIN
SELECT fu.user_id,frv.responsibility_id,frv.application_id,grp.start_date
-- INTO l_user_id,l_resp_id,l_resp_appl_id,l_resp_start_date
from fnd_responsibility_vl frv
,fnd_user_resp_groups_direct grp
,fnd_user fu
WHERE fu.user_name = UPPER ('ZOLLER')
AND upper(frv.responsibility_name)=upper('NAPP US PS Internet Expense (HTML)')
AND grp.user_id = fu.user_id
AND frv.responsibility_id=grp.responsibility_id
AND fu.end_date is null;
Exception when OTHERS then
-- write_log ('ERROR: User name ' l_user_name 'do not exist');
l_message:='FAILED:VALIDATION';
END;
IF l_user_id=-1 OR l_resp_id=-1 OR l_resp_appl_id=-1 OR l_resp_start_date is NULL then
l_message:='FAILED:DATA MISSING';
ELSE
BEGIN
fnd_user_resp_groups_api.update_assignment
(user_id => l_user_id
,responsibility_id => l_resp_id
,responsibility_application_id => l_resp_appl_id
,security_group_id => 0
,start_date => l_resp_start_date
,end_date => NULL
,description => l_resp_name
,update_who_columns => 'Y'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_message:='FAILED:API';
END ;
END IF;
dbms_output.put_line(l_user_name''l_resp_name''l_message);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('When others ' SQLERRM);
RAISE;
END;
/
File Name : NAPP_GLOBAL_RESP_FIX.sql
Object Name : NAPP_GLOBAL_RESP_FIX.sql
Description : One Time update script to update the resp end date
Revision History :
Ver Date Name Revision Description
=== ========= ============== ==========================================
1.0 03-DEC-2010 Neelima Created the program
+=============================================================================*/
SET PAGESIZE 0;
SET LINESIZE 10000;
SET SERVEROUTPUT ON SIZE 100000;
SET TRIMSPOOL ON;
SET DEFINE OFF;
DECLARE
l_file_handle UTL_FILE.FILE_TYPE;
l_eof CHAR;
l_user_name VARCHAR2 (150);
l_resp_name VARCHAR2 (500);
l_text VARCHAR2 (5000);
l_start_date DATE;
l_start_date_str VARCHAR2(50);
l_user_id number;
l_resp_id number;
l_resp_appl_id number;
l_resp_start_date date;
l_message varchar2(100);
l_dir_name varchar2(100);
BEGIN
BEGIN
select name into l_dir_name from v$database;
dbms_output.put_line('UTL file directory ''/usr/tmp/'l_dir_name);
END;
Fnd_Global.apps_initialize (1429,20420,1);
l_eof := 'N';
l_file_handle :=
UTL_FILE.FOPEN
('/usr/tmp/'l_dir_name
,'NAPP_GLOBAL_RESP_DATA.csv'
,'r'
);
LOOP
l_user_id := -1;
l_resp_id := -1;
l_resp_appl_id := -1;
l_message:='SUCCESS';
l_resp_start_date := null;
BEGIN
UTL_FILE.GET_LINE (l_file_handle, l_text);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_eof := 'Y';
END;
EXIT WHEN l_eof = 'Y';
l_user_name := SUBSTR (l_text, 1, INSTR (l_text, ',') - 1);
l_resp_name := REPLACE(SUBSTR (l_text
, INSTR (l_text, ',') + 1
, length(l_text)
),',','');
BEGIN
SELECT fu.user_id,frv.responsibility_id,frv.application_id,grp.start_date
-- INTO l_user_id,l_resp_id,l_resp_appl_id,l_resp_start_date
from fnd_responsibility_vl frv
,fnd_user_resp_groups_direct grp
,fnd_user fu
WHERE fu.user_name = UPPER ('ZOLLER')
AND upper(frv.responsibility_name)=upper('NAPP US PS Internet Expense (HTML)')
AND grp.user_id = fu.user_id
AND frv.responsibility_id=grp.responsibility_id
AND fu.end_date is null;
Exception when OTHERS then
-- write_log ('ERROR: User name ' l_user_name 'do not exist');
l_message:='FAILED:VALIDATION';
END;
IF l_user_id=-1 OR l_resp_id=-1 OR l_resp_appl_id=-1 OR l_resp_start_date is NULL then
l_message:='FAILED:DATA MISSING';
ELSE
BEGIN
fnd_user_resp_groups_api.update_assignment
(user_id => l_user_id
,responsibility_id => l_resp_id
,responsibility_application_id => l_resp_appl_id
,security_group_id => 0
,start_date => l_resp_start_date
,end_date => NULL
,description => l_resp_name
,update_who_columns => 'Y'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_message:='FAILED:API';
END ;
END IF;
dbms_output.put_line(l_user_name''l_resp_name''l_message);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('When others ' SQLERRM);
RAISE;
END;
/
Wednesday, October 20, 2010
Query to get Concurent Programs attached to given value set
SELECT fcpl.user_concurrent_program_name ,
fcp.concurrent_program_name ,
par.column_seq_num ,
par.end_user_column_name ,
par.form_left_prompt prompt ,
par.enabled_flag ,
par.required_flag ,
par.display_flag ,
par.flex_value_set_id ,
ffvs.flex_value_set_name ,
flv.meaning default_type ,
par.DEFAULT_VALUE
FROM APPS.fnd_concurrent_programs fcp ,
APPS.fnd_concurrent_programs_tl fcpl ,
APPS.fnd_descr_flex_col_usage_vl par ,
APPS.fnd_flex_value_sets ffvs ,
APPS.fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
AND ffvs.flex_value_set_id=102362
fcp.concurrent_program_name ,
par.column_seq_num ,
par.end_user_column_name ,
par.form_left_prompt prompt ,
par.enabled_flag ,
par.required_flag ,
par.display_flag ,
par.flex_value_set_id ,
ffvs.flex_value_set_name ,
flv.meaning default_type ,
par.DEFAULT_VALUE
FROM APPS.fnd_concurrent_programs fcp ,
APPS.fnd_concurrent_programs_tl fcpl ,
APPS.fnd_descr_flex_col_usage_vl par ,
APPS.fnd_flex_value_sets ffvs ,
APPS.fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
AND ffvs.flex_value_set_id=102362
Thursday, July 8, 2010
Oracle Applications: Key Flex Field Structures & How to Retrieve Information
http://www.notesbit.com/index.php/oracle-applications/11i-scripts/oracle-applications-key-flex-field-structures-how-to-retrieve-information/
Tuesday, July 6, 2010
This script is used to extract order header details from backend.
SELECT ra.customer_number,
hl.address1,
hl.address2,
hl.city,
hl.state,
hl.postal_code,
hl.country,
hl.province,
hl.county
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl,
ra_customers ra
WHERE 1 = 1
AND ooh.invoice_to_org_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcas.cust_account_id = ra.customer_id
AND order_number = &Order_number
hl.address1,
hl.address2,
hl.city,
hl.state,
hl.postal_code,
hl.country,
hl.province,
hl.county
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl,
ra_customers ra
WHERE 1 = 1
AND ooh.invoice_to_org_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcas.cust_account_id = ra.customer_id
AND order_number = &Order_number
Subscribe to:
Posts (Atom)