1 PACKAGE BODY RCV_GARBAGE_COLLECTOR_SV AS
2 /* $Header: RCVGARBB.pls 120.0.12010000.2 2010/01/25 22:40:03 vthevark ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
6
7 /*===========================================================================
8
9 PROCEDURE NAME: collect_garbage()
10
11 ===========================================================================*/
12
13 PROCEDURE collect_garbage (p_request_id IN NUMBER,
14 p_group_id IN NUMBER) IS
15
16 /* FPJ WMS CHANGE.
17 * We now support ROI for all the processing modes. Hence change
18 * the sql to support all modes. For online mode request id is null. Hence
19 * consider it as -999 if it is null for p_request_id.
20 */
21 -- Bug 3549318 added or condition to collect rows with no source id specified
22 CURSOR c1 IS
23 SELECT rhi.header_interface_id,
24 COUNT (rti.interface_transaction_id) error_count
25 FROM rcv_transactions_interface rti,
26 rcv_headers_interface rhi
27 WHERE rhi.header_interface_id = rti.header_interface_id
28 AND rhi.processing_status_code = 'PENDING'
29 AND (rti.document_num IS NOT NULL OR rti.oe_order_num IS NOT NULL)
30 AND NOT EXISTS (SELECT 'x'
31 FROM po_headers_all poha
32 WHERE poha.segment1 = rti.document_num)
33 AND rhi.GROUP_ID = DECODE (p_group_id, 0, rhi.GROUP_ID, p_group_id)
34 AND ( rhi.processing_request_id IS NULL
35 OR rhi.processing_request_id = p_request_id
36 )
37 GROUP BY rhi.header_interface_id;
38
39 /* Bug 2393443 - Modified the filter condition on segment1
40 in the above cursor from" poha.segment1 = nvl(rti.document_num,'!0')"
41 to "poha.segment1 = nvl(rti.document_num,poha.segment1)"
42 so that records from other operating units are not picked up .
43 */
44
45 -- Bug 2626270 - We will only consider the records that belong to
46 -- a specific group_id, if it is specified.
47
48
49 v_total_count number := 0;
50
51 begin
52 -- For Bug 2367174
53 -- Part 1
54
55 -- Mark all rcv_transactions_interface rows to RUNNING that have the
56 -- the header_interface_row set to RUNNING so all the
57 -- transaction_interface rows get picked up.
58
59 -- An ASN with multiple OU POs will thus be not supported.
60
61 /* FPJ WMS CHANGE.
62 * We now support ROI for all the processing modes. Hence change
63 * the sql to support all modes.
64 */
65 update rcv_transactions_interface rti
66 SET rti.processing_status_code = 'RUNNING',
67 rti.processing_request_id =decode(p_request_id,null,null,
68 p_request_id)
69 where exists (select 'x' from rcv_headers_interface rhi
70 where rhi.header_interface_id = rti.header_interface_id and
71 rhi.processing_status_code = 'RUNNING' and
72 (rhi.processing_request_id is null or
73 rhi.processing_request_id = p_request_id)) and
74 rti.group_id = decode(p_group_id, 0, rti.group_id, p_group_id) and
75 rti.processing_status_code = 'PENDING' ;
76
77 -- Part 2
78
79 -- select all header_interface rows that have either missing or
80 -- invalid po numbers (document_num)
81
82 -- if all the rows for an ASN are invalid from above then we need to
83 -- process this ASN in this session. Mark the ASN as RUNNING and
84 -- update the request_id so the pre-processor will pick up this ASN
85 -- This handles the case where we could have an ASN with all lines
86 -- invalid due to either missing po numbers or invalid po numbers
87
88 for c1_rec in c1 loop
89
90 select count(*)
91 into v_total_count
92 from rcv_transactions_interface rti
93 where
94 rti.header_interface_id = c1_rec.header_interface_id;
95
96 if c1_rec.error_count = v_total_count then
97
98 IF (g_asn_debug = 'Y') THEN
99 asn_debug.put_line('The ASN ' || to_char(c1_rec.header_interface_id) || ' has only errored rows');
100 asn_debug.put_line('Need to mark the ASN and all transactions to RUNNING');
101 END IF;
102
103 -- update the header to running with the right request_id
104
105 /* FPJ WMS CHANGE.
106 * We now support ROI for all the processing modes. Hence change
107 * the sql to support all modes.
108 */
109 update rcv_headers_interface
110 set processing_status_code = 'RUNNING',
111 processing_request_id = decode(p_request_id,null,null, p_request_id)
112 where processing_status_code = 'PENDING' and
113 header_interface_id = c1_rec.header_interface_id;
114
115 -- update the transactions to running with the right request_id
116
117 /* FPJ WMS CHANGE.
118 * We now support ROI for all the processing modes. Hence change
119 * the sql to support all modes.
120 */
121 update rcv_transactions_interface
122 SET processing_status_code = 'RUNNING',
123 processing_request_id = decode(p_request_id,null,null,
124 p_request_id)
125 WHERE processing_status_code = 'PENDING'
126 AND (processing_request_id is null OR
127 processing_request_id = p_request_id)
128 AND group_id = decode(p_group_id,0,group_id,p_group_id)
129 AND header_interface_id = c1_rec.header_interface_id;
130
131 else
132
133 IF (g_asn_debug = 'Y') THEN
134 asn_debug.put_line('Should be picked up in some other session');
135 asn_debug.put_line('The ASN ' || to_char(c1_rec.header_interface_id) || ' may have some valid rows');
136 asn_debug.put_line('We do nothing in this case');
137 END IF;
138
139 end if;
140
141 end loop;
142
143 END collect_garbage;
144
145 END RCV_GARBAGE_COLLECTOR_SV;
[Home] [Help]