[Home] [Help]
651: , rti.item_id
652: , rti.to_organization_id
653: , rti.from_organization_id
654: , rti.transaction_type
655: FROM rcv_shipment_lines rsl, rcv_transactions_interface rti
656: WHERE rti.interface_transaction_id = p_interface_transaction_id
657: AND rti.shipment_line_id = rsl.shipment_line_id
658: AND NOT EXISTS(SELECT 1
659: FROM rcv_shipment_headers rsh
668: , rti.item_id
669: , rti.to_organization_id
670: , rti.from_organization_id
671: , rti.transaction_type
672: FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions_interface rti
673: WHERE rti.interface_transaction_id = p_interface_transaction_id
674: AND rti.shipment_line_id = rsl.shipment_line_id
675: AND rsh.shipment_header_id = rsl.shipment_header_id
676: AND rsh.receipt_source_code IN('INTERNAL ORDER', 'INVENTORY')
687: , rti.to_organization_id
688: , rti.from_organization_id
689: , rti.transaction_type
690: , oel.flow_status_code
691: FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
692: WHERE rti.interface_transaction_id = p_interface_transaction_id
693: AND rti.interface_transaction_id = rt.interface_transaction_id
694: AND oel.line_id = rt.oe_order_line_id
695: AND rti.receipt_source_code = 'CUSTOMER'
707: , rti.to_organization_id
708: , rti.from_organization_id
709: , rti.transaction_type
710: , oel.flow_status_code
711: FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
712: WHERE rti.interface_transaction_id = p_interface_transaction_id
713: AND rti.interface_transaction_id = rt.interface_transaction_id
714: AND oel.line_id = rt.oe_order_line_id
715: AND rti.receipt_source_code = 'CUSTOMER'
726: , msn.last_update_date
727: , msn.current_status
728: , wlpn.lpn_context
729: FROM mtl_serial_numbers msn,
730: rcv_transactions_interface rti,
731: mtl_serial_numbers_temp msnt,
732: wms_license_plate_numbers wlpn
733: WHERE rti.interface_transaction_id = p_interface_transaction_id
734: AND msnt.product_code = 'RCV'
748: , msn.last_update_date
749: , msn.current_status
750: , wlpn.lpn_context
751: FROM mtl_serial_numbers msn,
752: rcv_transactions rt,
753: mtl_unit_transactions mut,
754: wms_license_plate_numbers wlpn
755: WHERE rt.interface_transaction_id = p_interface_transaction_id
756: AND mut.product_code ='RCV'
761: AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
762: AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
763: AND NVL(msn.group_mark_id ,-1) <> -1
764: AND not exists
765: (select 1 from rcv_transactions rt1
766: where rt1.parent_transaction_id = rt.transaction_id);
767:
768: CURSOR c7 IS
769: SELECT 'MOL exists with invalid wms_process_flag'
771: , moh.header_id
772: , mol.inventory_item_id
773: , rt.organization_id
774: , rt.transaction_type
775: FROM mtl_txn_request_lines mol, mtl_txn_request_headers moh, rcv_transactions rt
776: WHERE rt.interface_transaction_id = p_interface_transaction_id
777: AND rt.transaction_type = 'RECEIVE'
778: AND (mol.lpn_id = rt.transfer_lpn_id
779: OR mol.lpn_id = rt.lpn_id)
1058: AND GROUP_ID = DECODE(x_group_id, 0, GROUP_ID, x_group_id);
1059:
1060: CURSOR dcp_cursor1(x_lpn_group_id NUMBER) IS
1061: SELECT distinct interface_transaction_id
1062: FROM rcv_transactions
1063: WHERE lpn_group_id = x_lpn_group_id;
1064:
1065: CURSOR dcp_cursor2(x_group_id NUMBER) IS
1066: SELECT distinct interface_transaction_id
1063: WHERE lpn_group_id = x_lpn_group_id;
1064:
1065: CURSOR dcp_cursor2(x_group_id NUMBER) IS
1066: SELECT distinct interface_transaction_id
1067: FROM rcv_transactions
1068: WHERE group_id = x_group_id
1069: AND lpn_group_id is null;
1070:
1071: BEGIN