[Home] [Help]
709: l_commit_size NUMBER := 500;
710: l_msg_text VARCHAR2(2000);
711: l_mtl_trx_id NUMBER;
712: l_mtl_trx_date DATE;
713: l_mtl_serial_num mtl_unit_transactions.serial_number%type;
714: l_mtl_subinv mtl_material_transactions.subinventory_code%type;
715: l_SN_mismatch BOOLEAN := FALSE;
716:
717: l_ro_count NUMBER ; ---Added by vijay to put ro count in JC event
910: -- 2. Non-Serialized and Serial Control at sales order issue
911: --
912: -- If the item is pre-defined or at inventory receipt, the serial number
913: -- is entered while completing the job. The serial number is stored in
914: -- mtl_unit_transactions table. So the serial number can be matched to
915: -- the serial number on repair order and the repair order is updated
916: -- with the completed qty. But if the wip job is for upgrade, then the
917: -- repair order item is different then the item on the WIP job. So if
918: -- it is upgrade then the completion qty is updated based on the
932: -- Txn Id Job ID Txn Qty Completion Subinv
933: -- M1 1121 2 FGI
934: -- M2 1121 8 Stores
935: --
936: -- MTL_UNIT_TRANSACTIONS
937: --
938: -- Txn Id Serial Number Subinv
939: -- M1 SN1 FGI
940: -- M2 SN2 FGI
950: into l_mtl_trx_id,
951: l_mtl_trx_date,
952: l_mtl_subinv
953: from mtl_material_transactions mt,
954: mtl_unit_transactions mut
955: where mt.transaction_id = mut.transaction_id
956: and mt.transaction_source_id = JOB.wip_entity_id
957: and mt.transaction_source_type_id = 5 -- Job or Schedule
958: and mt.transaction_action_id = 31 -- Wip Assembly Completion
1062: l_mtl_trx_date,
1063: l_mtl_serial_num,
1064: l_mtl_subinv
1065: from mtl_material_transactions mt,
1066: mtl_unit_transactions mut
1067: where mt.transaction_id = mut.transaction_id
1068: and mt.transaction_source_id = JOB.wip_entity_id
1069: and mt.transaction_source_type_id = 5 -- Job or Schedule
1070: and mt.transaction_action_id = 31 -- Wip Assembly Completion
1200: l_mtl_trx_date,
1201: l_mtl_serial_num,
1202: l_mtl_subinv
1203: from mtl_material_transactions mt,
1204: mtl_unit_transactions mut
1205: where mt.transaction_id = mut.transaction_id
1206: and mt.transaction_source_id = JOB.wip_entity_id
1207: and mt.transaction_source_type_id = 5 -- Job or Schedule
1208: and mt.transaction_action_id = 31 -- Wip Assembly Completion
7419: mut.locator_id,
7420: mut.serial_number,
7421: mtl.transaction_id
7422: from mtl_material_transactions mtl,
7423: mtl_unit_transactions mut
7424: where mtl.transaction_id = mut.transaction_id
7425: and mtl.transaction_source_type_id = 8 -- Internal Order
7426: and mtl.transaction_type_id in (50, 62,54,34)
7427: and mtl.picking_line_id = p_del_line_id
7437: mut.locator_id,
7438: mut.serial_number,
7439: mtl.transaction_id
7440: from MTL_TRANSACTION_LOT_NUMBERS mln,
7441: mtl_unit_transactions mut,
7442: mtl_material_transactions mtl
7443: WHERE MLN.SERIAL_TRANSACTION_ID = mut.transaction_id
7444: and mln.transaction_id = mtl.transaction_id
7445: and mtl.transaction_source_type_id = 8 -- Internal Order
8174: Debug('DEL.LOT_CONTROL_CODE ='||DEL.LOT_CONTROL_CODE ,l_mod_name,1);
8175:
8176: -- In case of item serialized at sales order issue, the serial number is stored
8177: -- in the material unit transactions. Need to join the mtl_material_transactions
8178: -- with mtl_unit_transactions to get the serial numbers.
8179: -- Serial Number Control Code = 6 (Serialized at Sales order Issue)
8180: -- Delivery Lines
8181: -- Del Line Id Order Line Id Shipped Qty
8182: -- D1 L1 6
8331: and cra.serial_number not in
8332: (select
8333: mut.serial_number
8334: from mtl_material_transactions mtl,
8335: mtl_unit_transactions mut,
8336: wsh_delivery_details wdd
8337: where mtl.transaction_id = mut.transaction_id
8338: and mtl.transaction_source_type_id = 8 -- Internal Order
8339: and mtl.transaction_type_id in ( 50,62,54,34)
8587: and cra.serial_number not in
8588: (select
8589: mut.serial_number
8590: from mtl_material_transactions mtl,
8591: mtl_unit_transactions mut,
8592: wsh_delivery_details wdd
8593: where mtl.transaction_id = mut.transaction_id
8594: and mtl.transaction_source_type_id = 8 -- Internal Order
8595: and mtl.transaction_type_id in ( 50,62,54,34)