1 PACKAGE BODY rcv_normalize_data_pkg AS
2 /* $Header: RCVNRMDB.pls 120.24.12020000.6 2013/03/14 07:11:57 zhlee ship $*/
3
4 g_fail_if_one_line_fails VARCHAR2(1) := nvl(fnd_profile.VALUE('RCV_FAIL_IF_LINE_FAILS'),'N'); /* lcm changes */
5 g_org_id NUMBER; -- Bug 14370850
6 g_ou_name rcv_headers_interface.operating_unit%TYPE; -- Bug 14370850
7
8 PROCEDURE handle_error(
9 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
10 ) IS
11 BEGIN
12 p_rti_row.processing_status_code := 'ERROR';
13 p_rti_row.processing_request_id := g_request_id;
14
15 IF (g_fail_all = 'Y') THEN
16 /* Update statement called in update_rti_error
17 UPDATE rcv_transactions_interface
18 SET processing_status_code = 'ERROR',
19 processing_request_id = g_request_id
20 WHERE header_interface_id = p_rti_row.header_interface_id
21 AND processing_status_code IN('PENDING', 'RUNNING')
22 AND transaction_status_code = 'PENDING';
23 */
24 /*BEGIN BUG: 5598140*/
25 RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
26 p_interface_id => NULL,
27 p_header_interface_id => p_rti_row.header_interface_id,
28 p_lpn_group_id => NULL
29 );
30 ELSIF (p_rti_row.lpn_group_id is not null) THEN
31 RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
32 p_interface_id => NULL,
33 p_header_interface_id => NULL,
34 p_lpn_group_id => p_rti_row.lpn_group_id
35 );
36
37 ELSE
38 RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
39 p_interface_id => p_rti_row.interface_transaction_id,
40 p_header_interface_id => NULL,
41 p_lpn_group_id => NULL
42 );
43 /*END BUG: 5598140*/
44 END IF;
45
46 rcv_table_functions.update_rti_row(p_rti_row);
47
48 IF (g_multiple_groups = FALSE) THEN
49 COMMIT; --We don't want to erase what we've done so far
50 --RAISE rcv_error_pkg.e_fatal_error;
51 END IF;
52 END handle_error;
53
54 PROCEDURE check_orphan_rhi(
55 p_rhi_row IN OUT NOCOPY rcv_headers_interface%ROWTYPE
56 ) IS
57 x_rti_count NUMBER;
58 proc_code rcv_headers_interface.processing_status_code%TYPE;
59 BEGIN
60 SELECT count(*)
61 INTO x_rti_count
62 FROM rcv_transactions_interface
63 WHERE header_interface_id = p_rhi_row.header_interface_id
64 AND processing_status_code in ('PENDING','RUNNING')
65 AND transaction_status_code = 'PENDING';
66
67 asn_debug.put_line('count of running rtis'|| x_rti_count);
68
69 IF x_rti_count = 0 THEN -- error out orphan rhi
70 asn_debug.put_line('Erroring out orphan RHI: ' ||
71 p_rhi_row.header_interface_id);
72
73 p_rhi_row.processing_status_code := 'ERROR';
74 p_rhi_row.processing_request_id := g_request_id;
75
76 rcv_table_functions.update_rhi_row(p_rhi_row);
77 COMMIT;
78 END IF;
79 EXCEPTION
80 WHEN OTHERS THEN
81 NULL;
82 END check_orphan_rhi;
83
84 PROCEDURE prepare_pending_rhi(
85 p_rhi_row IN OUT NOCOPY rcv_headers_interface%ROWTYPE
86 ) IS
87 x_org_id NUMBER;
88 BEGIN --update block
89 BEGIN --exception handling block
90 rcv_default_pkg.default_header(p_rhi_row);
91 EXCEPTION
92 WHEN OTHERS THEN
93 p_rhi_row.processing_status_code := 'ERROR';
94 p_rhi_row.processing_request_id := g_request_id;
95
96 UPDATE rcv_transactions_interface
97 SET processing_status_code = 'ERROR',
98 processing_request_id = g_request_id
99 WHERE header_interface_id = p_rhi_row.header_interface_id
100 AND processing_status_code IN('PENDING', 'RUNNING')
101 AND transaction_status_code = 'PENDING';
102 END; --exception handling block
103
104 rcv_table_functions.update_rhi_row(p_rhi_row);
105 END prepare_pending_rhi;
106
107 PROCEDURE prepare_pending_rti(
108 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
109 ) IS
110 BEGIN --this is the update block
111 BEGIN --this is the exception catching block
112 IF (p_rti_row.GROUP_ID IS NULL) THEN
113 p_rti_row.GROUP_ID := g_group_id;
114 END IF;
115
116 IF (p_rti_row.validation_flag = 'Y') THEN
117 rcv_default_pkg.default_transaction(p_rti_row);
118
119 /* Bug 7229164 Populating Order transaction id for desktop transactions also
120 so that the records from RTI are now picked and processed in the correct order. */
121 ELSE
122 p_rti_row.order_transaction_id := p_rti_row.interface_transaction_id;
123 END IF;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 handle_error(p_rti_row);
128 END; --exception handling block
129
130 rcv_table_functions.update_rti_row(p_rti_row);
131 END prepare_pending_rti;
132
133 PROCEDURE process_orphan_rti(
134 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
135 ) IS
136 BEGIN
137 rcv_error_pkg.set_error_message('RCV_IS_ORPHAN');
138 rcv_error_pkg.log_interface_error('PARENT_INTERFACE_TXN_ID');
139 EXCEPTION
140 WHEN OTHERS THEN
141 p_rti_row.processing_status_code := 'ERROR';
142 p_rti_row.processing_request_id := g_request_id;
143 rcv_table_functions.update_rti_row(p_rti_row);
144 END;
145
146 PROCEDURE process_row(
147 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
148 ) IS
149 x_rhi_row rcv_headers_interface%ROWTYPE;
150
151 -- Following 4 variables are added for BugFix 5078706
152 l_parent_transaction_type rcv_transactions.transaction_type%type;
153 l_parent_transaction_id rcv_transactions.transaction_id%type;
154 l_true boolean;
155 l_match_count number;
156
157 /* lcm changes */
158 l_lpn_group_rti_count number := 0;
159 x_header_record rcv_roi_preprocessor.header_rec_type;
160
161 BEGIN
162 l_true := TRUE; -- BugFix 5078706
163
164 BEGIN
165 asn_debug.put_line('Processing row INTERFACE_TRANSACTION_ID = ' || p_rti_row.interface_transaction_id);
166
167 -- Following code is added for BugFix 5078706
168 BEGIN
169 select transaction_id,
170 transaction_type
171 into l_parent_transaction_id,
172 l_parent_transaction_type
173 from rcv_transactions
174 where transaction_type = 'UNORDERED'
175 connect by prior
176 parent_transaction_id = transaction_id
177 start with transaction_id = p_rti_row.parent_transaction_id;
178
179 begin
180 select count(1)
181 into l_match_count
182 from rcv_transactions
183 where transaction_type = 'MATCH'
184 and parent_transaction_id = l_parent_transaction_id;
185 exception
186 when others
187 then
188 l_match_count := 0;
189 end;
190
191 IF (l_parent_transaction_type = 'UNORDERED' and l_match_count = 0)
192 THEN
193 l_true := FALSE;
194 ELSE
195 l_true := TRUE;
196 END IF;
197
198 EXCEPTION
199 WHEN OTHERS
200 THEN
201 l_true := TRUE;
202 END;
203 -- End of code for BugFix 5078706
204
205 /*
206 ** Bug#4641243 - Operating Unit information will not be available for
207 ** Unordered Receipts
208 */
209 --Bug 8679527 For Cross OU Receipts the RTI.ORG_ID is populated as Creation OU id
210 --But when MO:Security Profile is not set this causing issues. So, restricting
211 --this validation for ROI txns.
212
213 IF (p_rti_row.transaction_type <> 'UNORDERED' AND p_rti_row.source_document_code <> 'INVENTORY'
214 AND p_rti_row.validation_flag='Y' and p_rti_row.mobile_txn<>'Y') THEN -- bug 5147243
215 IF (l_true = TRUE) -- BugFix 5078706
216 THEN
217 IF (NVL(mo_global.check_access(p_rti_row.org_id),'N') = 'N') THEN
218 rcv_error_pkg.set_error_message('RCV_NOT_IN_ORG');
219 rcv_error_pkg.set_token('ORG_ID', p_rti_row.org_id);
220 rcv_error_pkg.set_token('OU', mo_global.get_current_org_id);
221 rcv_error_pkg.log_interface_error('ORG_ID');
222 END IF;
223 END IF; -- BugFix 5078706
224 END IF;
225
226 /* Bugfix : 5354379 : The defaulting should not be called from here since the parent row will be in
227 status "RUNNING" at this instance and which will therefore cause the rcv_default_pkg.default_from_parent
228 to raise an error thru rcv_default_pkg.default_rti_from_rti.
229 IF (p_rti_row.parent_interface_txn_id IS NOT NULL) THEN
230 rcv_default_pkg.default_from_parent(p_rti_row); -- we want to pull in any new information about the row
231 END IF;
232 */
233 EXCEPTION
234 WHEN OTHERS THEN
235 handle_error(p_rti_row);
236 END;
237
238 /* EXECUTE PROCESSOR on row */
239 IF (p_rti_row.processing_status_code <> 'ERROR') THEN
240
241 /* lcm chages : Start */
242 x_rhi_row := rcv_table_functions.get_rhi_row_from_id(p_rti_row.header_interface_id);
243
244 IF ( rcv_table_functions.is_lcm_org (p_rti_row.to_organization_id) = 'Y'
245 AND rcv_table_functions.is_pre_rcv_org (p_rti_row.to_organization_id) = 'N') Then
246 --
247 IF ( p_rti_row.source_document_code = 'PO'
248 AND (p_rti_row.transaction_type = 'RECEIVE'
249 OR (p_rti_row.transaction_type = 'SHIP'
250 AND p_rti_row.auto_transact_code in ('RECEIVE','DELIVER')))
251 AND rcv_table_functions.is_lcm_shipment (p_rti_row.po_line_location_id) = 'Y'
252 AND p_rti_row.lcm_shipment_line_id is null) THEN
253 --
254 asn_debug.put_line('Setting current row to status LC_PENDING');
255 p_rti_row.processing_status_code := 'LC_PENDING';
256 --
257 -- Bug 8343811: Start
258 IF (x_rhi_row.receipt_num IS NULL) THEN
259 x_header_record.header_record := x_rhi_row;
260 rcv_roi_header_common.default_receipt_info(x_header_record);
261 IF (x_header_record.error_record.error_status = 'E') THEN
262 x_rhi_row.processing_status_code := 'ERROR';
263 ELSE
264 x_rhi_row.processing_status_code := 'LC_PENDING';
265 x_rhi_row.receipt_num := x_header_record.header_record.receipt_num;
266 asn_debug.put_line('Generated new receipt_num for rhi : ' || x_rhi_row.receipt_num, null,14);
267 END IF;
268 rcv_table_functions.update_rhi_row(x_rhi_row);
269 END IF;
270 -- Bug 8343811: End
271 ELSE
272 /* If a non-lcm line and lcm line are tied to the same lpn_group_id, we
273 need to set the non-lcm line to 'WLC_PENDING' as these should be processed together. */
274 IF (p_rti_row.lpn_group_id IS NOT NULL) THEN
275 select count(1)
276 into l_lpn_group_rti_count
277 from rcv_transactions_interface rti
278 where rti.group_id = p_rti_row.group_id
279 and rti.lpn_group_id is not null
280 and rti.lpn_group_id = p_rti_row.lpn_group_id
281 and rti.interface_transaction_id <> p_rti_row.interface_transaction_id -- Bug 8343139
282 and (rti.transaction_type = 'RECEIVE'
283 or (rti.transaction_type = 'SHIP'
284 and rti.auto_transact_code in ('RECEIVE','DELIVER'))) -- Bug 8343139
285 and rti.source_document_code = 'PO'
286 and exists (select 'LCM Shipment' from po_line_locations_all pll
287 where pll.line_location_id = rti.po_line_location_id
288 and lcm_flag = 'Y')
289 and (rti.lcm_shipment_line_id is null or rti.unit_landed_cost is null);
290 --
291 asn_debug.put_line('LPN Group check : l_lpn_group_rti_count = ' ||l_lpn_group_rti_count, null,14);
292 --
293 END IF;
294 --
295 IF (x_rhi_row.asn_type = 'ASN'
296 AND g_fail_if_one_line_fails = 'Y'
297 AND l_lpn_group_rti_count = 0) THEN
298 select count(1)
299 into l_lpn_group_rti_count
300 from rcv_transactions_interface rti
301 where rti.group_id = x_rhi_row.group_id
302 and rti.header_interface_id = x_rhi_row.header_interface_id
303 and rti.interface_transaction_id <> p_rti_row.interface_transaction_id -- Bug 8343139
304 and (rti.transaction_type = 'RECEIVE'
305 or (rti.transaction_type = 'SHIP'
306 and rti.auto_transact_code in ('RECEIVE','DELIVER'))) -- Bug 8343139
307 and rti.source_document_code = 'PO'
308 and exists (select 'LCM Shipment' from po_line_locations_all pll
309 where pll.line_location_id = rti.po_line_location_id
310 and lcm_flag = 'Y')
311 and (rti.lcm_shipment_line_id is null or rti.unit_landed_cost is null);
312 --
313 asn_debug.put_line('ASN check : l_lpn_group_rti_count = ' ||l_lpn_group_rti_count, null,14);
314 --
315 END IF;
316 IF (l_lpn_group_rti_count > 0) THEN
317 asn_debug.put_line('Setting current row to status WLC_PENDING');
318 p_rti_row.processing_status_code := 'WLC_PENDING';
319 p_rti_row.processing_request_id := g_request_id;
320 IF (x_rhi_row.processing_status_code = 'ERROR') THEN
321 asn_debug.put_line('Setting header row status HEADER_INTERFACE_ID=' || x_rhi_row.header_interface_id);
322 x_rhi_row.processing_status_code := 'PENDING';
323 x_rhi_row.processing_request_id := g_request_id;
324 rcv_table_functions.update_rhi_row(x_rhi_row);
325 END IF;
326 ELSE
327 asn_debug.put_line('Setting current row to status RUNNING');
328 p_rti_row.processing_status_code := 'RUNNING';
329 p_rti_row.processing_request_id := g_request_id;
330 IF (x_rhi_row.processing_status_code IN ('PENDING', 'ERROR')) THEN
331 asn_debug.put_line('Setting header row status HEADER_INTERFACE_ID=' || x_rhi_row.header_interface_id);
332 x_rhi_row.processing_status_code := 'RUNNING';
333 x_rhi_row.processing_request_id := g_request_id;
334 rcv_table_functions.update_rhi_row(x_rhi_row);
335 END IF;
336 END IF;
337 --
338 END IF;
339 --
340 ELSE
341 asn_debug.put_line('Setting current row to status RUNNING');
342 p_rti_row.processing_status_code := 'RUNNING';
343 p_rti_row.processing_request_id := g_request_id;
344
345 IF (x_rhi_row.processing_status_code IN('PENDING', 'ERROR')) THEN
346 asn_debug.put_line('Setting header row status HEADER_INTERFACE_ID=' || x_rhi_row.header_interface_id);
347 x_rhi_row.processing_status_code := 'RUNNING';
348 x_rhi_row.processing_request_id := g_request_id;
349 rcv_table_functions.update_rhi_row(x_rhi_row);
350 END IF;
351 END IF;
352 /* lcm chages : End */
353
354 END IF;
355
356 rcv_table_functions.update_rti_row(p_rti_row);
357 END;
358
359 PROCEDURE explode_all_lpn IS
360 l_ret_status VARCHAR2(20);
361 l_msg_count NUMBER;
362 l_msg_data VARCHAR2(100);
363 BEGIN
364 asn_debug.put_line('in explode_all_lpn');
365
366 -- Bug 14370850
367 IF (g_org_id = -1) THEN
368 UPDATE rcv_transactions_interface
369 SET processing_status_code = 'RUNNING',
370 processing_request_id = g_request_id
371 WHERE processing_status_code = 'PENDING'
372 AND ( processing_request_id = g_request_id
373 OR processing_request_id IS NULL)
374 AND ( mo_global.check_access(org_id) = 'Y'
375 OR org_id IS NULL)
376 AND ( lpn_group_id IS NOT NULL
377 OR lpn_id IS NOT NULL
378 OR license_plate_number IS NOT NULL
379 OR interface_transaction_id IN (SELECT interface_transaction_id
380 FROM wms_lpn_contents_interface));
381 ELSE
382 UPDATE rcv_transactions_interface
383 SET processing_status_code = 'RUNNING',
384 processing_request_id = g_request_id
385 WHERE processing_status_code = 'PENDING'
386 AND ( processing_request_id = g_request_id
387 OR processing_request_id IS NULL)
388 AND org_id = g_org_id
389 AND ( lpn_group_id IS NOT NULL
390 OR lpn_id IS NOT NULL
391 OR license_plate_number IS NOT NULL
392 OR interface_transaction_id IN (SELECT interface_transaction_id
393 FROM wms_lpn_contents_interface));
394
395 END IF;
396
397
398 inv_rcv_integration_apis.explode_lpn(1.0,
399 fnd_api.g_true,
400 l_ret_status,
401 l_msg_count,
402 l_msg_data,
403 NULL,
404 g_request_id
405 );
406
407 -- Bug 14370850
408 IF (g_org_id = -1) THEN
409 UPDATE rcv_transactions_interface
410 SET processing_status_code = 'PENDING'
411 WHERE processing_status_code = 'RUNNING'
412 AND processing_request_id = g_request_id;
413 ELSE
414 UPDATE rcv_transactions_interface
415 SET processing_status_code = 'PENDING'
416 WHERE processing_status_code = 'RUNNING'
417 AND processing_request_id = g_request_id
418 AND org_id = g_org_id;
419 END IF;
420
421 asn_debug.put_line('finished explode_all_lpn');
422 EXCEPTION
423 WHEN OTHERS THEN
424 asn_debug.put_line('encountered an error in explode_all_lpn');
425 END explode_all_lpn;
426
427 PROCEDURE explode_lpn IS
428 l_ret_status VARCHAR2(20);
429 l_msg_count NUMBER;
430 l_msg_data VARCHAR2(100);
431 BEGIN
432 asn_debug.put_line('in explode_lpn');
433
434 -- Bug 14370850
435 IF (g_org_id = -1) THEN
436 UPDATE rcv_transactions_interface
437 SET processing_status_code = 'RUNNING',
438 processing_request_id = g_request_id,
439 group_id = g_group_id
440 WHERE processing_status_code = 'PENDING'
441 AND ( processing_request_id = g_request_id
442 OR processing_request_id IS NULL)
443 AND group_id = g_group_id
444 AND ( lpn_group_id IS NOT NULL
445 OR lpn_id IS NOT NULL
446 OR license_plate_number IS NOT NULL
447 OR interface_transaction_id IN (SELECT interface_transaction_id
448 FROM wms_lpn_contents_interface));
449 ELSE
450 UPDATE rcv_transactions_interface
451 SET processing_status_code = 'RUNNING',
452 processing_request_id = g_request_id,
453 group_id = g_group_id
454 WHERE processing_status_code = 'PENDING'
455 AND ( processing_request_id = g_request_id
456 OR processing_request_id IS NULL)
457 AND group_id = g_group_id
458 AND org_id = g_org_id
459 AND ( lpn_group_id IS NOT NULL
460 OR lpn_id IS NOT NULL
461 OR license_plate_number IS NOT NULL
462 OR interface_transaction_id IN (SELECT interface_transaction_id
463 FROM wms_lpn_contents_interface));
464
465 END IF;
466
467 inv_rcv_integration_apis.explode_lpn(1.0,
468 fnd_api.g_true,
469 l_ret_status,
470 l_msg_count,
471 l_msg_data,
472 g_group_id,
473 g_request_id
474 );
475
476 -- Bug 14370850
477 IF (g_org_id = -1) THEN
478 UPDATE rcv_transactions_interface
479 SET processing_status_code = 'PENDING'
480 WHERE processing_status_code = 'RUNNING'
481 AND processing_request_id = g_request_id
482 AND group_id = g_group_id;
483 ELSE
484 UPDATE rcv_transactions_interface
485 SET processing_status_code = 'PENDING'
486 WHERE processing_status_code = 'RUNNING'
487 AND processing_request_id = g_request_id
488 AND group_id = g_group_id
489 AND org_id = g_org_id;
490 END IF;
491
492 asn_debug.put_line('finished explode_lpn');
493 EXCEPTION
494 WHEN OTHERS THEN
495 asn_debug.put_line('encountered an error in explode_lpn');
496 END explode_lpn;
497
498 PROCEDURE process_pending_rows(
499 p_processing_mode IN VARCHAR2,
500 p_group_id IN NUMBER,
501 p_request_id IN NUMBER,
502 p_org_id IN NUMBER
503 ) IS
504 CURSOR c_get_group_id IS
505 SELECT rcv_interface_groups_s.NEXTVAL
506 FROM DUAL;
507
508 CURSOR c_get_all_pending_rhi_row IS
509 SELECT *
510 FROM rcv_headers_interface
511 WHERE processing_status_code = 'PENDING'
512 AND ( mo_global.check_access(org_id) = 'Y'
513 OR org_id IS NULL)
514 ORDER BY group_id, header_interface_id; -- added by bug 16393104 to avoid deadlock
515 -- FOR UPDATE NOWAIT; --Added by bug 13587955
516
517 CURSOR c_get_pending_rhi_row IS
518 SELECT *
519 FROM rcv_headers_interface
520 WHERE processing_status_code = 'PENDING'
521 AND GROUP_ID = g_group_id
522 ORDER BY header_interface_id; -- added by bug 16393104 to avoid deadlock
523 -- FOR UPDATE NOWAIT; --Added by bug 13587955
524
525 /* the order is important, which is the reason for the connect by string */
526 /* this ensures defaulting from a parent row that has already been populated */
527 /* however, because of this we will not collect 'orphan' rows - we need to
528 /* error out the orphans */
529 /* WDK - ADD INDEX ON PARENT_INTERFACE_TXN_ID */
530 CURSOR c_get_all_pending_rti_row IS
531 SELECT * FROM ( --Bug 12594135
532 SELECT *
533 FROM rcv_transactions_interface
534 WHERE processing_status_code = 'PENDING'
535 AND processing_mode_code = g_processing_mode -- Bug 6311798
536 AND ( mo_global.check_access(org_id) = 'Y'
537 OR org_id IS NULL)
538 ) rcv
539 CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
540 START WITH rcv.parent_interface_txn_id IS NULL --Reverted the initial fix of 12718851 and keeping the original code
541 ORDER BY group_id, interface_transaction_id; -- added by bug 16393104 to avoid deadlock
542 -- FOR UPDATE NOWAIT; --Added by bug 13587955
543
544 CURSOR c_get_pending_rti_row IS
545 SELECT * FROM ( --Bug 12594135
546 SELECT *
547 FROM rcv_transactions_interface
548 WHERE processing_status_code = 'PENDING'
549 AND processing_mode_code = g_processing_mode -- Bug 6311798
550 AND GROUP_ID = g_group_id
551 ) rcv
552 CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
553 START WITH rcv.parent_interface_txn_id IS NULL --Reverted the initial fix of 12718851 and keeping the original code
554 ORDER BY interface_transaction_id; -- added by bug 16393104 to avoid deadlock
555 -- FOR UPDATE NOWAIT; --Added by bug 13587955
556
557 /* because we needed to include parent/child order, we introduce the possibility */
558 /* of not processing 'orphan' children. these children need to be errored out */
559
560 /** Bug: 5473673
561 * There is possibility to have more than 1 orphan record exist in
562 * rcv_transactions_interface table. In that case inner query will
563 * multiple rows, but we are having the condition "=" in
564 * the clause "START WITH". So, we have to replace the contition
565 * "=" with "IN"
566 */
567 /* Bug 8745599 The cursors c_get_all_orphan_rti_row and c_get_orphan_rti_row were wrongly fetching
568 genuine RTI records that need to be processed, due to the way the CONNECT BY clause is handling
569 NULL values for parent_interface_txn_id. This was resulting in RTP errorring out with RCV_IS_ORPHAN
570 error, even though these are not orphan records. Added a condition in both the cursors to ensure
571 that rows are returned only when parent_transaction_id is not null, and parent record is absent.
572 */
573
574 CURSOR c_get_all_orphan_rti_row IS
575 SELECT *
576 FROM rcv_transactions_interface rti
577 WHERE rti.processing_status_code = 'PENDING'
578 AND rti.processing_mode_code = g_processing_mode -- Bug 6311798
579 AND ( mo_global.check_access(rti.org_id) = 'Y'
580 OR rti.org_id IS NULL)
581 CONNECT BY PRIOR rti.interface_transaction_id = parent_interface_txn_id
582 START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id --Bug:5473673
583 FROM rcv_transactions_interface rti1
584 WHERE rti1.parent_interface_txn_id IS NOT NULL -- Bug 8745599
585 AND rti1.PROCESSING_MODE_CODE = g_processing_mode --Bug 12594135
586 AND rti1. parent_interface_txn_id NOT IN(SELECT rti2.interface_transaction_id
587 FROM rcv_transactions_interface rti2))
588 ORDER BY rti.group_id, rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
589 -- FOR UPDATE NOWAIT; --Added by bug 13587955
590
591 CURSOR c_get_orphan_rti_row IS
592 SELECT *
593 FROM rcv_transactions_interface rti
594 WHERE rti.processing_status_code = 'PENDING'
595 AND rti.processing_mode_code = g_processing_mode -- Bug 6311798
596 AND rti.GROUP_ID = g_group_id
597 CONNECT BY PRIOR rti.interface_transaction_id = rti.parent_interface_txn_id
598 START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id --Bug: 5473673
599 FROM rcv_transactions_interface rti1
600 WHERE rti1.parent_interface_txn_id IS NOT NULL -- Bug 8745599
601 AND rti1.PROCESSING_MODE_CODE = g_processing_mode --Bug 12594135
602 AND rti1.GROUP_ID = g_group_id --Bug 12594135
603 AND rti1.parent_interface_txn_id NOT IN(SELECT rti2.interface_transaction_id
604 FROM rcv_transactions_interface rti2))
605 ORDER BY rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
606 -- FOR UPDATE NOWAIT; --Added by bug 13587955
607
608 -- Bug 14370850 : Start
609 CURSOR c_get_all_pending_rhi_row2 IS
610 SELECT *
611 FROM rcv_headers_interface
612 WHERE processing_status_code = 'PENDING'
613 AND org_id = p_org_id
614 ORDER BY group_id,header_interface_id; -- added by bug 16393104 to avoid deadlock
615 -- FOR UPDATE NOWAIT; --Added by bug 13587955
616
617 CURSOR c_get_all_pending_rti_row2 IS
618 SELECT * FROM (
619 SELECT *
620 FROM rcv_transactions_interface
621 WHERE processing_status_code = 'PENDING'
622 AND processing_mode_code = g_processing_mode
623 AND org_id = p_org_id
624 ) rcv
625 CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
626 START WITH rcv.parent_interface_txn_id IS NULL
627 ORDER BY group_id, interface_transaction_id; -- added by bug 16393104 to avoid deadlock
628 -- FOR UPDATE NOWAIT; --Added by bug 13587955
629
630 CURSOR c_get_all_orphan_rti_row2 IS
631 SELECT *
632 FROM rcv_transactions_interface rti
633 WHERE rti.processing_status_code = 'PENDING'
634 AND rti.processing_mode_code = g_processing_mode
635 AND rti.org_id = p_org_id
636 CONNECT BY PRIOR rti.interface_transaction_id = rti.parent_interface_txn_id
637 START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id
638 FROM rcv_transactions_interface rti1
639 WHERE rti1.parent_interface_txn_id IS NOT NULL
640 AND rti1.processing_mode_code = g_processing_mode
641 AND rti1.org_id = p_org_id
642 AND rti1.parent_interface_txn_id NOT IN (SELECT rti2.interface_transaction_id
643 FROM rcv_transactions_interface rti2))
644 ORDER BY rti.group_id, rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
645 -- FOR UPDATE NOWAIT; --Added by bug 13587955
646
647 CURSOR c_get_pending_rhi_row2 IS
648 SELECT *
649 FROM rcv_headers_interface
650 WHERE processing_status_code = 'PENDING'
651 AND group_id = g_group_id
652 AND org_id = p_org_id
653 ORDER BY header_interface_id; -- added by bug 16393104 to avoid deadlock
654 -- FOR UPDATE NOWAIT; --Added by bug 13587955
655
656 CURSOR c_get_pending_rti_row2 IS
657 SELECT * FROM (
658 SELECT *
659 FROM rcv_transactions_interface
660 WHERE processing_status_code = 'PENDING'
661 AND processing_mode_code = g_processing_mode
662 AND group_id = g_group_id
663 AND org_id = p_org_id
664 ) rcv
665 CONNECT BY PRIOR rcv.interface_transaction_id = rcv.parent_interface_txn_id
666 START WITH rcv.parent_interface_txn_id IS NULL
667 ORDER BY interface_transaction_id; -- added by bug 16393104 to avoid deadlock
668 -- FOR UPDATE NOWAIT; --Added by bug 13587955
669
670 CURSOR c_get_orphan_rti_row2 IS
671 SELECT *
672 FROM rcv_transactions_interface rti
673 WHERE rti.processing_status_code = 'PENDING'
674 AND rti.processing_mode_code = g_processing_mode
675 AND rti.org_id = p_org_id
676 CONNECT BY PRIOR rti.interface_transaction_id = rti.parent_interface_txn_id
677 START WITH rti.interface_transaction_id IN (SELECT rti1.interface_transaction_id
678 FROM rcv_transactions_interface rti1
679 WHERE rti1.parent_interface_txn_id IS NOT NULL
680 AND rti1.processing_mode_code = g_processing_mode
681 AND rti1.group_id = g_group_id
682 AND rti1.org_id = p_org_id
683 AND rti1.parent_interface_txn_id NOT IN (SELECT rti2.interface_transaction_id
684 FROM rcv_transactions_interface rti2))
685 ORDER BY rti.interface_transaction_id; -- added by bug 16393104 to avoid deadlock
686 -- FOR UPDATE NOWAIT; --Added by bug 13587955
687
688 -- Bug 14370850 : End
689
690 row_locked EXCEPTION; --Added by bug 13587955
691 PRAGMA EXCEPTION_INIT(row_locked, -54); --Added by bug 13587955
692 x_rti_row1 rcv_transactions_interface%ROWTYPE; -- added by bug 16393104
693 x_rhi_row1 rcv_headers_interface%ROWTYPE; -- added by bug 16393104
694
695 BEGIN
696 IF p_processing_mode = 'BATCH' THEN
697 IF p_group_id IS NULL
698 OR p_group_id = 0 THEN
699 g_multiple_groups := TRUE;
700 ELSE
701 g_group_id := p_group_id;
702 g_multiple_groups := FALSE;
703 END IF;
704 ELSE --p_processing = 'ONLINE' or 'IMMEDIATE'
705 g_multiple_groups := FALSE;
706 END IF;
707
708 g_group_id := NVL(p_group_id, 0);
709 g_request_id := NVL(p_request_id, 0);
710 g_processing_mode := p_processing_mode; -- Bug 6311798
711 rcv_table_functions.g_default_org_id := p_org_id;
712
713 -- Bug 14370850
714 g_org_id := p_org_id;
715
716 IF (g_org_id <> -1) THEN
717 IF (NVL(mo_global.check_access(g_org_id),'N') = 'N') THEN
718 asn_debug.put_line ('No access allowed. Returning');
719 RETURN;
720 END IF;
721
722 SELECT name
723 INTO g_ou_name
724 FROM hr_organization_units
725 WHERE organization_id = g_org_id;
726 END IF;
727
728 IF g_group_id = 0 THEN
729 OPEN c_get_group_id;
730 FETCH c_get_group_id INTO g_group_id;
731 CLOSE c_get_group_id;
732 END IF;
733
734 asn_debug.put_line('Process Pending Rows: p_processing_mode=' || p_processing_mode || ' g_group_id=' || g_group_id || ' g_request_id=' || g_request_id || ' p_org_id=' || p_org_id);
735
736 /* we cannot use bulk collects because we are using the tablespace as working memory */
737 -- Bug 14370850 : Start
738 IF (p_org_id <> -1) THEN
739 IF (g_multiple_groups = TRUE) THEN
740 asn_debug.put_line('Processing multiple groups when p_org_id is passed...');
741 explode_all_lpn;
742
743 UPDATE rcv_headers_interface
744 SET org_id = p_org_id
745 WHERE processing_status_code = 'PENDING'
746 AND org_id IS NULL
747 AND operating_unit = g_ou_name;
748
749 UPDATE rcv_transactions_interface
750 SET org_id = p_org_id
751 WHERE processing_status_code = 'PENDING'
752 AND validation_flag = 'Y'
753 AND org_id IS NULL
754 AND operating_unit = g_ou_name;
755
756 asn_debug.put_line('Derived org_id');
757
758 FOR x_rhi_row IN c_get_all_pending_rhi_row2 LOOP
759 -- bug 16393104 start
760 SELECT *
761 INTO x_rhi_row1
762 FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
763 FOR UPDATE NOWAIT;
764
765 prepare_pending_rhi(x_rhi_row1);
766 -- bug 16393104 end
767
768 END LOOP;
769
770 FOR x_rti_row IN c_get_all_pending_rti_row2 LOOP
771 -- bug 16393104 start
772 SELECT *
773 INTO x_rti_row1
774 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
775 FOR UPDATE NOWAIT;
776
777 prepare_pending_rti(x_rti_row1);
778 -- bug 16393104 end
779
780 END LOOP;
781
782 BEGIN -- Bug 13587955
783 FOR x_rti_row IN c_get_all_orphan_rti_row2 LOOP
784 -- bug 16393104 start
785 SELECT *
786 INTO x_rti_row1
787 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
788 FOR UPDATE NOWAIT;
789
790 process_orphan_rti(x_rti_row1);
791 -- bug 16393104 end
792
793 END LOOP;
794 -- Bug 13587955 New BEGIN
795 EXCEPTION
796 WHEN row_locked THEN
797 asn_debug.put_line('Orphan records are left untouched as locks could not be obtained.');
798 RETURN;
799 END;
800 -- Bug 13587955 New END
801 ELSE
802 asn_debug.put_line('Processing single group when p_org_id is passed...');
803 explode_lpn;
804
805 UPDATE rcv_headers_interface
806 SET org_id = p_org_id
807 WHERE processing_status_code = 'PENDING'
808 AND group_id = g_group_id
809 AND org_id IS NULL
810 AND operating_unit = g_ou_name;
811
812 UPDATE rcv_transactions_interface
813 SET org_id = p_org_id
814 WHERE processing_status_code = 'PENDING'
815 AND group_id = g_group_id
816 AND validation_flag = 'Y'
817 AND org_id IS NULL
818 AND operating_unit = g_ou_name;
819
820 asn_debug.put_line('Derived org_id');
821
822 FOR x_rhi_row IN c_get_pending_rhi_row2 LOOP
823 -- bug 16393104 start
824 SELECT *
825 INTO x_rhi_row1
826 FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
827 FOR UPDATE NOWAIT;
828
829 prepare_pending_rhi(x_rhi_row1);
830 -- bug 16393104 end
831
832 END LOOP;
833
834 FOR x_rti_row IN c_get_pending_rti_row2 LOOP
835 -- bug 16393104 start
836 SELECT *
837 INTO x_rti_row1
838 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
839 FOR UPDATE NOWAIT;
840
841 prepare_pending_rti(x_rti_row1);
842 -- bug 16393104 end
843
844 END LOOP;
845
846 BEGIN -- Bug 13587955
847 FOR x_rti_row IN c_get_orphan_rti_row2 LOOP
848 -- bug 16393104 start
849 SELECT *
850 INTO x_rti_row1
851 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
852 FOR UPDATE NOWAIT;
853
854 process_orphan_rti(x_rti_row1);
855 -- bug 16393104 end
856
857 END LOOP;
858 -- Bug 13587955 New BEGIN
859 EXCEPTION
860 WHEN row_locked THEN
861 asn_debug.put_line('Orphan records are left untouched as locks could not be obtained.');
862 RETURN;
863 END;
864 -- Bug 13587955 New END
865
866 END IF;
867
868 IF (g_multiple_groups = TRUE) THEN
869 FOR x_rti_row IN c_get_all_pending_rti_row2 LOOP
870 process_row(x_rti_row);
871 END LOOP;
872 ELSE
873 FOR x_rti_row IN c_get_pending_rti_row2 LOOP
874 process_row(x_rti_row);
875 END LOOP;
876 END IF;
877
878 asn_debug.put_line('Check orphan RHI');
879 IF (g_multiple_groups = TRUE) THEN
880 FOR x_rhi_row IN c_get_all_pending_rhi_row2 LOOP
881 check_orphan_rhi(x_rhi_row);
882 END LOOP;
883 ELSE
884 FOR x_rhi_row IN c_get_pending_rhi_row2 LOOP
885 check_orphan_rhi(x_rhi_row);
886 END LOOP;
887 END IF;
888
889 ELSE
890 IF (g_multiple_groups = TRUE) THEN
891 asn_debug.put_line('Processing multiple groups...');
892 explode_all_lpn;
893
894 FOR x_rhi_row IN c_get_all_pending_rhi_row LOOP
895 -- bug 16393104 start
896 SELECT *
897 INTO x_rhi_row1
898 FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
899 FOR UPDATE NOWAIT;
900
901 prepare_pending_rhi(x_rhi_row1);
902 -- bug 16393104 end
903
904 END LOOP;
905
906 FOR x_rti_row IN c_get_all_pending_rti_row LOOP
907 -- bug 16393104 start
908 SELECT *
909 INTO x_rti_row1
910 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
911 FOR UPDATE NOWAIT;
912
913 prepare_pending_rti(x_rti_row1);
914 -- bug 16393104 end
915
916 END LOOP;
917
918 BEGIN -- Bug 13587955
919 FOR x_rti_row IN c_get_all_orphan_rti_row LOOP
920 -- bug 16393104 start
921 SELECT *
922 INTO x_rti_row1
923 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
924 FOR UPDATE NOWAIT;
925
926 process_orphan_rti(x_rti_row1);
927 -- bug 16393104 end
928
929 END LOOP;
930 -- Bug 13587955 New BEGIN
931 EXCEPTION
932 WHEN row_locked THEN
933 asn_debug.put_line('Orphan records are left untouched as locks could not be obtained.');
934 RETURN;
935 END;
936 -- Bug 13587955 New END
937
938 ELSE
939 asn_debug.put_line('Processing single group...');
940 explode_lpn;
941
942 FOR x_rhi_row IN c_get_pending_rhi_row LOOP
943 -- bug 16393104 start
944 SELECT *
945 INTO x_rhi_row1
946 FROM rcv_headers_interface WHERE header_interface_id = x_rhi_row.header_interface_id
947 FOR UPDATE NOWAIT;
948
949 prepare_pending_rhi(x_rhi_row1);
950 -- bug 16393104 end
951
952 END LOOP;
953
954 FOR x_rti_row IN c_get_pending_rti_row LOOP
955 -- bug 16393104 start
956 SELECT *
957 INTO x_rti_row1
958 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
959 FOR UPDATE NOWAIT;
960
961 prepare_pending_rti(x_rti_row1);
962 -- bug 16393104 end
963
964 END LOOP;
965
966 BEGIN -- Bug 13587955
967 FOR x_rti_row IN c_get_orphan_rti_row LOOP
968 -- bug 16393104 start
969 SELECT *
970 INTO x_rti_row1
971 FROM rcv_transactions_interface WHERE interface_transaction_id = x_rti_row.interface_transaction_id
972 FOR UPDATE NOWAIT;
973
974 process_orphan_rti(x_rti_row1);
975 -- bug 16393104 end
976
977 END LOOP;
978 -- Bug 13587955 New BEGIN
979 EXCEPTION
980 WHEN row_locked THEN
981 asn_debug.put_line('Orphan records are left untouched as locks could not be obtained.');
982 RETURN;
983 END;
984 -- Bug 13587955 New END
985
986 END IF;
987
988 /* we now rerun the pending filter which will work this time because org_id has been populated */
989 IF (g_multiple_groups = TRUE) THEN
990 FOR x_rti_row IN c_get_all_pending_rti_row LOOP
991 process_row(x_rti_row);
992 END LOOP;
993 ELSE
994 FOR x_rti_row IN c_get_pending_rti_row LOOP
995 process_row(x_rti_row);
996 END LOOP;
997 END IF;
998
999 /* rhi could be without any pending rti at this point. */
1000 asn_debug.put_line('Check orphan RHI');
1001 IF (g_multiple_groups = TRUE) THEN
1002 FOR x_rhi_row IN c_get_all_pending_rhi_row LOOP
1003 check_orphan_rhi(x_rhi_row);
1004 END LOOP;
1005 ELSE
1006 FOR x_rhi_row IN c_get_pending_rhi_row LOOP
1007 check_orphan_rhi(x_rhi_row);
1008 END LOOP;
1009 END IF;
1010
1011 END IF;
1012 -- Bug 14370850 : End
1013
1014 /* lcm changes */
1015 asn_debug.put_line('Calling RCV_LCM_WEB_SERVICE.Get_Landed_Cost');
1016 RCV_LCM_WEB_SERVICE.Get_Landed_Cost (p_group_id, p_processing_mode);
1017
1018 -- Bug 13587955 New BEGIN
1019 EXCEPTION
1020 WHEN row_locked THEN
1021 asn_debug.put_line('ROI records to be processed are locked by another session. Failing current run.');
1022 raise_application_error(-20101, 'Records are found locked by another session.');
1023 -- Bug 13587955 New END
1024
1025 END process_pending_rows;
1026 END rcv_normalize_data_pkg;
[Home] [Help]