[Home] [Help]
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;
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')
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:
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')
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
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');
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
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
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'
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
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
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
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;
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'
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'
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;
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
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)
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
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: */
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)
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))
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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'
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
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
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
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
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
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