[Home] [Help]
PACKAGE BODY: APPS.RCV_UPDATE_RTI_LC
Source
1 PACKAGE BODY RCV_UPDATE_RTI_LC AS
2 /* $Header: RCVUPLCB.pls 120.4.12020000.2 2013/03/18 03:34:16 yilali ship $ */
3
4 PROCEDURE update_rti (p_int_rec IN rcv_cost_table,
5 x_lcm_int OUT NOCOPY lcm_int_table) IS
6 PRAGMA AUTONOMOUS_TRANSACTION;
7
8 l_row_count NUMBER := 0;
9 l_group_id NUMBER;
10 l_req_id NUMBER := 0;
11 l_lpn_group_rti_count NUMBER;
12 callRTP BOOLEAN := FALSE;
13 l_lpn_group_id NUMBER;
14 g_fail_if_one_line_fails VARCHAR2(1);
15 l_asn_type VARCHAR2(500);
16 l_header_interface_id NUMBER;
17 l_rhi_group_id NUMBER;
18 l_lcm_int lcm_int_table := lcm_int_table();
19 k NUMBER := 0;
20
21 BEGIN
22
23 --
24 x_lcm_int := lcm_int_table();
25
26 asn_debug.put_line('Entering UPDATE_RTI_WITH_LC.update_rti' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS'));
27 asn_debug.put_line('no of records to be updated : ' || p_int_rec.COUNT);
28 --
29
30 g_fail_if_one_line_fails := nvl(fnd_profile.VALUE('RCV_FAIL_IF_LINE_FAILS'),'N');
31
32 asn_debug.put_line('profile RCV_FAIL_IF_LINE_FAILS: ' || g_fail_if_one_line_fails);
33
34 SELECT rcv_interface_groups_s.NEXTVAL
35 INTO l_group_id
36 FROM DUAL;
37
38 asn_debug.put_line('group_id to be updated with: ' || l_group_id);
39
40 for i in 1..p_int_rec.COUNT loop
41
42 if (p_int_rec(i).unit_landed_cost is not null
43 and p_int_rec(i).lcm_shipment_line_id is not null
44 and p_int_rec(i).interface_id is not null) then
45
46 UPDATE rcv_transactions_interface
47 SET lcm_shipment_line_id = p_int_rec(i).lcm_shipment_line_id,
48 unit_landed_cost = p_int_rec(i).unit_landed_cost
49 WHERE interface_transaction_id = p_int_rec(i).interface_id
50 AND processing_status_code = 'LC_INTERFACED'
51 AND lcm_shipment_line_id is NULL
52 AND unit_landed_cost is NULL;
53
54 l_row_count := l_row_count + SQL%ROWCOUNT;
55
56 asn_debug.put_line('updated interface id: ' || p_int_rec(i).interface_id);
57 asn_debug.put_line('Updated : '||SQL%ROWCOUNT||' rows in RTI');
58
59 if (SQL%ROWCOUNT > 0) then
60 k := k+1;
61 x_lcm_int.extend;
62 x_lcm_int(k) := p_int_rec(i).interface_id;
63 end if;
64
65 else
66 asn_debug.put_line('did not update interface id: ' || p_int_rec(i).interface_id);
67 asn_debug.put_line('unit_landed_cost: ' || p_int_rec(i).unit_landed_cost);
68 asn_debug.put_line('lcm_shipment_line_id: ' || p_int_rec(i).lcm_shipment_line_id);
69 asn_debug.put_line('interface_id: ' || p_int_rec(i).interface_id);
70
71 end if;
72
73 end loop;
74
75 asn_debug.put_line('Updated : '||l_row_count||' rows in RTI');
76
77 l_lpn_group_rti_count := 0;
78 for i in 1..p_int_rec.COUNT loop
79
80 begin
81 select lpn_group_id
82 into l_lpn_group_id
83 from rcv_transactions_interface
84 where interface_transaction_id = p_int_rec(i).interface_id;
85 exception
86 when others then
87 l_lpn_group_id := NULL;
88 end;
89
90 -- /* If a non-lcm line and lcm line are tied to the same lpn_group_id, we
91 -- need to set the non-lcm line to 'LC_PENDING' as these should be processed together.
92 -- */
93
94 asn_debug.put_line('lpn_group_id: '||l_lpn_group_id);
95
96 IF (l_lpn_group_id IS NOT NULL) THEN
97
98 select count(1)
99 into l_lpn_group_rti_count
100 from rcv_transactions_interface
101 where lpn_group_id = l_lpn_group_id
102 and (lcm_shipment_line_id is NULL OR unit_landed_cost is NULL)
103 and processing_status_code in ('LC_PENDING','LC_INTERFACED');
104
105 asn_debug.put_line('LPN Group check : l_lpn_group_rti_count = ' ||l_lpn_group_rti_count);
106
107 if (l_lpn_group_rti_count = 0) then
108
109 UPDATE rcv_transactions_interface
110 SET processing_status_code = 'PENDING',
111 group_id = l_group_id
112 WHERE lpn_group_id = l_lpn_group_id
113 and ( ( lcm_shipment_line_id is not NULL
114 and unit_landed_cost is not NULL
115 and processing_status_code = 'LC_INTERFACED'
116 )
117 OR processing_status_code = 'WLC_PENDING'
118 );
119
120 UPDATE rcv_headers_interface rhi
121 SET rhi.processing_status_code = 'PENDING',
122 group_id = l_group_id -- Bug 7677015
123 WHERE rhi.processing_status_code <> 'RUNNING'
124 and exists( select 'exists' from rcv_transactions_interface rti
125 WHERE rti.lpn_group_id = l_lpn_group_id
126 and rti.header_interface_id IS NOT NULL
127 and rti.header_interface_id = rhi.header_interface_id
128 and ( ( rti.lcm_shipment_line_id is not NULL
129 and rti.unit_landed_cost is not NULL
130 and rti.processing_status_code = 'PENDING' -- Bug 7677015
131 )
132 OR rti.processing_status_code = 'WLC_PENDING'
133
134 )
135 );
136
137 asn_debug.put_line('no of rtis updated: '||sql%rowcount||' for the lpn group id: '||l_lpn_group_id);
138 callRTP := TRUE;
139
140 end if;
141 else
142
143 begin
144 select rhi.asn_type, rhi.header_interface_id, rhi.group_id
145 into l_asn_type, l_header_interface_id, l_rhi_group_id
146 from rcv_transactions_interface rti, rcv_headers_interface rhi
147 where rhi.header_interface_id = rti.header_interface_id
148 and rti.interface_transaction_id = p_int_rec(i).interface_id;
149 exception
150 when others then
151 l_asn_type := 'NON-ASN';
152 l_header_interface_id := -9999;
153 l_rhi_group_id := -9999;
154 end;
155
156 asn_debug.put_line('asn_type = ' ||l_asn_type);
157 asn_debug.put_line('header_interface_id = ' ||l_header_interface_id);
158 asn_debug.put_line('rhi_group_id = ' ||l_rhi_group_id);
159
160 IF (l_asn_type = 'ASN'
161 AND g_fail_if_one_line_fails = 'Y') THEN
162 select count(1)
163 into l_lpn_group_rti_count
164 from rcv_transactions_interface
165 where header_interface_id = l_header_interface_id
166 and group_id = l_rhi_group_id
167 and (lcm_shipment_line_id is NULL OR unit_landed_cost is NULL)
168 and processing_status_code in ('LC_PENDING','LC_INTERFACED');
169
170 asn_debug.put_line('ASN check : l_lpn_group_rti_count = ' ||l_lpn_group_rti_count);
171
172
173 if (l_lpn_group_rti_count = 0) then
174
175 UPDATE rcv_transactions_interface
176 SET processing_status_code = 'PENDING',
177 group_id = l_group_id
178 WHERE header_interface_id = l_header_interface_id
179 AND group_id = l_rhi_group_id
180 and (( lcm_shipment_line_id is not NULL
181 and unit_landed_cost is not NULL
182 and processing_status_code = 'LC_INTERFACED'
183 )
184 OR processing_status_code = 'WLC_PENDING'
185 );
186
187 UPDATE rcv_headers_interface rhi
188 SET rhi.processing_status_code = 'PENDING',
189 group_id = l_group_id -- Bug 7677015
190 WHERE rhi.processing_status_code <> 'RUNNING'
191 and exists( select 'exists' from rcv_transactions_interface rti
192 WHERE rti.header_interface_id = rhi.header_interface_id
193 and rti.header_interface_id = l_header_interface_id
194 and group_id = l_rhi_group_id
195 and ( ( rti.lcm_shipment_line_id is not NULL
196 and rti.unit_landed_cost is not NULL
197 and rti.processing_status_code = 'PENDING' -- Bug 7677015
198 )
199 OR rti.processing_status_code = 'WLC_PENDING'
200
201 )
202 );
203
204 asn_debug.put_line('rtis updated: '||sql%rowcount||' for the header interface id: '||l_header_interface_id);
205 callRTP := TRUE;
206
207 end if;
208 ELSE
209 UPDATE rcv_transactions_interface
210 SET processing_status_code = 'PENDING',
211 group_id = l_group_id
212 WHERE interface_transaction_id = p_int_rec(i).interface_id
213 AND processing_status_code = 'LC_INTERFACED'
214 and lcm_shipment_line_id is not NULL
215 and unit_landed_cost is not NULL;
216
217
218 UPDATE rcv_headers_interface rhi
219 SET rhi.processing_status_code = 'PENDING',
220 group_id = l_group_id -- Bug 7677015
221 WHERE rhi.processing_status_code <> 'RUNNING'
222 and exists( select 'exists' from rcv_transactions_interface rti
223 WHERE rti.interface_transaction_id = p_int_rec(i).interface_id
224 and rti.header_interface_id IS NOT NULL
225 and rti.header_interface_id = rhi.header_interface_id
226 AND processing_status_code = 'PENDING' -- Bug 7677015
227 and lcm_shipment_line_id is not NULL
228 and unit_landed_cost is not NULL
229 );
230
231 asn_debug.put_line('rti updated for the interface id: '||p_int_rec(i).interface_id);
232 callRTP := TRUE;
233 END IF;
234
235 end if;
236
237 end loop;
238
239 if (callRTP) then
240
241 COMMIT;
242 asn_debug.put_line('calling RTP for group id: '||l_group_id);
243 l_req_id := fnd_request.submit_request('PO', 'RVCTP',null,null,false,'BATCH',l_group_id,NULL,NULL,
244 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
245 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
246 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
247 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
248 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
249 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
250 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
251 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
252 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
253 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
254 NULL, NULL, NULL, NULL, NULL, NULL);
255
256 asn_debug.put_line('request id: '||l_req_id, 'insertlcm', '9');
257
258 if (l_req_id <= 0 or l_req_id is null) then
259 raise fnd_api.g_exc_unexpected_error;
260 end if;
261
262 end if;
263
264 COMMIT;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268
269 asn_debug.put_line('encountered an error in update_rti: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
270 asn_debug.put_line('Updated : '||l_row_count||' rows in RTI');
271
272 COMMIT;
273
274 END update_rti;
275
276 END RCV_UPDATE_RTI_LC;
277