[Home] [Help]
679: , rti.item_id
680: , rti.to_organization_id
681: , rti.from_organization_id
682: , rti.transaction_type
683: FROM rcv_shipment_lines rsl, rcv_transactions_interface rti
684: WHERE rti.interface_transaction_id = p_interface_transaction_id
685: AND rti.shipment_line_id = rsl.shipment_line_id
686: AND NOT EXISTS(SELECT 1
687: FROM rcv_shipment_headers rsh
696: , rti.item_id
697: , rti.to_organization_id
698: , rti.from_organization_id
699: , rti.transaction_type
700: FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions_interface rti
701: WHERE rti.interface_transaction_id = p_interface_transaction_id
702: AND rti.shipment_line_id = rsl.shipment_line_id
703: AND rsh.shipment_header_id = rsl.shipment_header_id
704: AND rsh.receipt_source_code IN('INTERNAL ORDER', 'INVENTORY')
715: , rti.to_organization_id
716: , rti.from_organization_id
717: , rti.transaction_type
718: , oel.flow_status_code
719: FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
720: WHERE rti.interface_transaction_id = p_interface_transaction_id
721: AND rti.interface_transaction_id = rt.interface_transaction_id
722: AND oel.line_id = rt.oe_order_line_id
723: AND rti.receipt_source_code = 'CUSTOMER'
735: , rti.to_organization_id
736: , rti.from_organization_id
737: , rti.transaction_type
738: , oel.flow_status_code
739: FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
740: WHERE rti.interface_transaction_id = p_interface_transaction_id
741: AND rti.interface_transaction_id = rt.interface_transaction_id
742: AND oel.line_id = rt.oe_order_line_id
743: AND rti.receipt_source_code = 'CUSTOMER'
754: , msn.last_update_date
755: , msn.current_status
756: , wlpn.lpn_context
757: FROM mtl_serial_numbers msn,
758: rcv_transactions_interface rti,
759: mtl_serial_numbers_temp msnt,
760: wms_license_plate_numbers wlpn
761: WHERE rti.interface_transaction_id = p_interface_transaction_id
762: AND msnt.product_code = 'RCV'
776: , msn.last_update_date
777: , msn.current_status
778: , wlpn.lpn_context
779: FROM mtl_serial_numbers msn,
780: rcv_transactions rt,
781: mtl_unit_transactions mut,
782: wms_license_plate_numbers wlpn
783: WHERE rt.interface_transaction_id = p_interface_transaction_id
784: AND mut.product_code ='RCV'
789: AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
790: AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
791: AND NVL(msn.group_mark_id ,-1) <> -1
792: AND not exists
793: (select 1 from rcv_transactions rt1
794: where rt1.parent_transaction_id = rt.transaction_id);
795:
796: CURSOR c7 IS
797: SELECT 'MOL exists with invalid wms_process_flag'
799: , moh.header_id
800: , mol.inventory_item_id
801: , rt.organization_id
802: , rt.transaction_type
803: FROM mtl_txn_request_lines mol, mtl_txn_request_headers moh, rcv_transactions rt
804: WHERE rt.interface_transaction_id = p_interface_transaction_id
805: AND rt.transaction_type = 'RECEIVE'
806: AND (mol.lpn_id = rt.transfer_lpn_id
807: OR mol.lpn_id = rt.lpn_id)
1086: AND GROUP_ID = DECODE(x_group_id, 0, GROUP_ID, x_group_id);
1087:
1088: CURSOR dcp_cursor1(x_lpn_group_id NUMBER) IS
1089: SELECT distinct interface_transaction_id
1090: FROM rcv_transactions
1091: WHERE lpn_group_id = x_lpn_group_id;
1092:
1093: CURSOR dcp_cursor2(x_group_id NUMBER) IS
1094: SELECT distinct interface_transaction_id
1091: WHERE lpn_group_id = x_lpn_group_id;
1092:
1093: CURSOR dcp_cursor2(x_group_id NUMBER) IS
1094: SELECT distinct interface_transaction_id
1095: FROM rcv_transactions
1096: WHERE group_id = x_group_id
1097: AND lpn_group_id is null;
1098:
1099: BEGIN