[Home] [Help]
PACKAGE BODY: APPS.GML_MOLINE_DATA_FIX_PKG
Source
1 PACKAGE BODY GML_MOLINE_DATA_FIX_PKG AS
2 /* $Header: GMLDFIXB.pls 120.0 2005/05/25 16:51:01 appldev noship $ */
3
4 PROCEDURE MO_LINE_FIX IS
5
6 l_count NUMBER;
7 fname VARCHAR2(4000);
8
9 --BEGIN BUG#2736088 V. Ajay Kumar
10 --Removed the reference to "apps".
11 Cursor get_mo_line IS
12 SELECT mo.line_id mo_line_id,
13 h.order_number ,
14 d.line_id order_line_id,
15 mo.quantity_detailed,
16 mo.quantity_delivered,
17 NVL( (-1) * sum(itp.trans_qty), 0) trans_qty,
18 NVL( (-1) * sum(itp.trans_qty2), 0) trans_qty2
19 FROM ic_txn_request_lines mo,
20 oe_order_headers_all h,
21 oe_order_lines_all d,
22 ic_tran_pnd itp
23 WHERE itp.line_id(+) = mo.txn_source_line_id
24 AND mo.txn_source_line_id = d.line_id
25 AND d.header_id = h.header_id
26 AND itp.doc_type(+) = 'OMSO'
27 AND itp.staged_ind(+) = 1
28 AND itp.delete_mark(+) = 0
29 AND NVL(mo.quantity_delivered, 0) > NVL(mo.quantity_detailed, 0)
30 AND mo.line_status IN (3, 7)
31 GROUP BY mo.line_id, h.order_number, d.line_id, mo.quantity_detailed, mo.quantity_delivered
32 ORDER by h.order_number;
33
34 Cursor get_mo_line_for_status IS
35 SELECT mol.line_id mo_line_id,
36 soh.order_number ,
37 sol.line_id order_line_id,
38 moh.request_number
39 FROM ic_txn_request_lines mol,
40 ic_txn_request_headers moh,
41 oe_order_headers_all soh,
42 oe_order_lines_all sol
43 WHERE moh.header_id = mol.header_id
44 AND soh.header_id = sol.header_id
45 AND sol.line_id = mol.txn_source_line_id
46 AND NVL(mol.quantity_delivered, 0) = NVL(mol.quantity_detailed, 0)
47 AND NVL(mol.quantity_detailed, 0) = NVL(mol.quantity, 0)
48 AND mol.line_status IN (3, 7)
49 ORDER by moh.request_number;
50 --END BUG#2736088
51
52 BEGIN
53 --BEGIN BUG#2736088 V. Ajay Kumar
54 --Removed the reference to "apps".
55 OE_DEBUG_PUB.SETDEBUGLEVEL(5);
56 OE_DEBUG_PUB.DEBUG_ON;
57 fname := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');
58 -- DBMS_OUTPUT.PUT_LINE('debug file : '||fname);
59 --END BUG#2736088
60
61 --DBMS_OUTPut.disable;
62 --DBMS_OUTPut.enable(1000000);
63
64 l_count := 1;
65 FOR mo_line IN get_mo_line LOOP
66
67 --DBMS_OUTPUT.put_line(' ++ ' || l_count || ' ++++++++++++++++++++++++++++++++++++++++++ ');
68 --DBMS_OUTPUT.put_line(' order number is ' || mo_line.order_number );
69 --DBMS_OUTPUT.put_line(' Move order line_id is ' || mo_line.mo_line_id);
70 --DBMS_OUTPUT.put_line(' order line_id is ' || mo_line.order_line_id );
71
72 --DBMS_OUTPUT.put_line(' updating mo line quantity_delivered as ' || mo_line.trans_qty );
73
74 --BEGIN BUG#2736088 V. Ajay Kumar
75 --Removed the reference to "apps".
76 UPDATE ic_txn_request_lines
77 SET quantity_delivered = mo_line.trans_qty,
78 secondary_quantity_delivered = mo_line.trans_qty2,
79 quantity_detailed = mo_line.trans_qty,
80 secondary_quantity_detailed = mo_line.trans_qty2
81 WHERE line_id = mo_line.mo_line_id;
82 --END BUG#2736088
83
84 l_count := l_count + 1;
85 END LOOP;
86 --DBMS_OUTPUT.put_line(' ++++++++++++++++++++++++++++++++++++++++++++ ');
87 --DBMS_OUTPUT.put_line(' total lines updated is ' || (l_count -1) );
88
89 l_count := 1;
90 FOR mo_line_stat IN get_mo_line_for_status LOOP
91
92 --DBMS_OUTPUT.put_line(' ++ ' || l_count || ' ++++++++++++++++++++++++++++++++++++++++++ ');
93 --DBMS_OUTPUT.put_line(' request number = ' || mo_line_stat.request_number );
94 --DBMS_OUTPUT.put_line(' order number = ' || mo_line_stat.order_number );
95 --DBMS_OUTPUT.put_line(' Move order line_id = ' || mo_line_stat.mo_line_id);
96 --DBMS_OUTPUT.put_line(' order line_id is ' || mo_line_stat.order_line_id );
97
98 --DBMS_OUTPUT.put_line(' This Move Order has to be updated to status=CLOSED');
99
100 --BEGIN BUG#2736088 V. Ajay Kumar
101 --Removed the reference to "apps".
102 UPDATE ic_txn_request_lines
103 SET line_status = 5
104 WHERE line_id = mo_line_stat.mo_line_id;
105 --END BUG#2736088
106
107 l_count := l_count + 1;
108 END LOOP;
109
110 --DBMS_OUTPUT.put_line(' ++++++++++++++++++++++++++++++++++++++++++++ ');
111 --DBMS_OUTPUT.put_line(' total lines updated is ' || (l_count -1) );
112 END MO_LINE_FIX;
113
114 END GML_MOLINE_DATA_FIX_PKG;