[Home] [Help]
575: l_plan_id number;
576: l_wip_entity_id number;
577:
578: l_rm_inventory_item_id NUMBER;
579: l_rm_inst_number csi_item_instances.instance_number%TYPE;
580:
581: --To check if the unit config header is valid or not.
582: CURSOR ahl_uc_header_csr(p_uc_header_id number) IS
583: select 'x'
623: select cii.wip_job_id,
624: (select wip_entity_name
625: from wip_entities
626: where wip_entity_id = cii.wip_job_id) wip_job_name
627: from csi_item_instances cii
628: where cii.instance_id= l_item_instance_id
629: --and location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
630: and cii.location_type_code = 'WIP'
631: and trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
633:
634: -- to validate removal item instance.
635: CURSOR ahl_item_instance_csr(p_item_instance_id in number) IS
636: select inventory_item_id, instance_number
637: from csi_item_instances
638: where instance_id= p_item_instance_id
639: and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
640:
641: -- To validate if instance's item defined in job's org.
660: quantity,
661: unit_of_measure,
662: inventory_revision,
663: serial_number
664: FROM csi_item_instances
665: WHERE instance_id = p_instance_id;
666:
667: l_config_instance_rec get_instance_attrib_csr%ROWTYPE;
668: l_new_instance_rec get_instance_attrib_csr%ROWTYPE;
1131:
1132: --to get instance_id if instance_number is provided
1133: CURSOR ahl_instance_id_csr (p_instance_num in varchar2) IS
1134: select instance_id
1135: from csi_item_instances
1136: where instance_number= p_instance_num;
1137:
1138: /*
1139: --to get condition_id if condition meaning is provided
1340:
1341: CURSOR ahl_location_type_csr(p_item_instance_id In number,
1342: p_csi_location_type In Varchar2) IS
1343: select location_type_code, f.meaning
1344: from csi_item_instances csi, csi_lookups f
1345: where csi.location_type_code = f.lookup_code
1346:
1347:
1348:
1483: l_msg_data varchar2(2000);
1484:
1485: CURSOR check_inst_nonserial(p_instance_id IN NUMBER) IS
1486: SELECT 'X'
1487: FROM mtl_system_items_b mtl, csi_item_instances csi
1488: WHERE csi.instance_id = p_instance_id
1489: AND csi.inventory_item_id = mtl.inventory_item_id
1490: AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
1491: AND mtl.serial_number_control_code = 1;
1494: l_serialized VARCHAR2(1);
1495: l_move_item_instance_tbl move_item_instance_tbl_type;
1496:
1497: CURSOR get_curr_quantity_csr(p_instance_id IN NUMBER) IS
1498: SELECT QUANTITY from csi_item_instances
1499: WHERE INSTANCE_ID = p_instance_id;
1500:
1501: l_curr_config_qty NUMBER;
1502: l_curr_job_qty NUMBER;
1506: l_instance_rec csi_datastructures_pub.instance_rec;
1507:
1508: CURSOR csi_item_instance_csr(p_instance_id IN NUMBER) IS
1509: select instance_number,object_Version_number
1510: from csi_item_instances CII
1511: where CII.instance_id = p_instance_id;
1512:
1513: CURSOR get_wip_job_csr(p_workorder_id IN NUMBER) IS
1514: select wip_entity_id from ahl_workorders
1515: where workorder_id = p_workorder_id;
1516: l_wip_job_id NUMBER;
1517:
1518: CURSOR removal_instance_id(p_instance_id IN NUMBER, p_wip_job_id IN NUMBER) IS
1519: SELECT C1.instance_id FROM CSI_ITEM_INSTANCES C1, CSI_ITEM_INSTANCES C2
1520: WHERE C1.INV_MASTER_ORGANIZATION_ID= C2.INV_MASTER_ORGANIZATION_ID
1521: AND C1.INVENTORY_ITEM_ID = C2.INVENTORY_ITEM_ID
1522: AND NVL(C1.INVENTORY_REVISION,'x') = NVL(C2.INVENTORY_REVISION,'x')
1523: AND NVL(C1.LOT_NUMBER,'x') = NVL(C2.LOT_NUMBER,'x')
2130: l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
2131:
2132: CURSOR check_inst_nonserial(p_instance_id IN NUMBER) IS
2133: SELECT 'X'
2134: FROM mtl_system_items_b mtl, csi_item_instances csi
2135: WHERE csi.instance_id = p_instance_id
2136: AND csi.inventory_item_id = mtl.inventory_item_id
2137: AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
2138: AND mtl.serial_number_control_code = 1;
2141: l_serialized VARCHAR2(1);
2142: l_move_item_instance_tbl move_item_instance_tbl_type;
2143:
2144: CURSOR get_curr_quantity_csr(p_instance_id IN NUMBER) IS
2145: SELECT QUANTITY from csi_item_instances
2146: WHERE INSTANCE_ID = p_instance_id;
2147:
2148: l_curr_config_qty NUMBER;
2149: l_curr_job_qty NUMBER;
2153:
2154:
2155: CURSOR csi_item_instance_csr(p_instance_id IN NUMBER) IS
2156: select instance_number,object_Version_number
2157: from csi_item_instances CII
2158: where CII.instance_id = p_instance_id;
2159:
2160: CURSOR get_wip_job_csr(p_workorder_id IN NUMBER) IS
2161: select wip_entity_id from ahl_workorders
2162: where workorder_id = p_workorder_id;
2163: l_wip_job_id NUMBER;
2164:
2165: CURSOR removal_instance_id(p_instance_id IN NUMBER, p_wip_job_id IN NUMBER) IS
2166: SELECT C1.instance_id FROM CSI_ITEM_INSTANCES C1, CSI_ITEM_INSTANCES C2
2167: WHERE C1.INV_MASTER_ORGANIZATION_ID= C2.INV_MASTER_ORGANIZATION_ID
2168: AND C1.INVENTORY_ITEM_ID = C2.INVENTORY_ITEM_ID
2169: AND NVL(C1.INVENTORY_REVISION,'x') = NVL(C2.INVENTORY_REVISION,'x')
2170: AND NVL(C1.LOT_NUMBER,'x') = NVL(C2.LOT_NUMBER,'x')
2701:
2702: --For the updated object_version number from csi_item_isntances
2703: CURSOR ahl_obj_ver_csr(p_instance_id in Number) IS
2704: select object_Version_number
2705: from csi_item_instances
2706: where instance_id = p_instance_id;
2707:
2708: --to populate csi_transaction record
2709: CURSOR ahl_wip_location_csr IS
2796: CURSOR ahl_mtl_txn_param_csr (p_wo_id in number, p_instance_id number) is
2797: SELECT CSI.INVENTORY_ITEM_ID, AHL.organization_id
2798: , CSI.inventory_revision revision, CSI.quantity, CSI.unit_of_measure,
2799: CSI.serial_number, CSI.lot_number
2800: --FROM ahl_workorders_v AHL, csi_item_instances CSI
2801: FROM ahl_workorder_tasks_v AHL, csi_item_instances CSI
2802: Where CSI.instance_id = p_instance_id
2803: --and csi.inv_organization_id = ahl.organization_id
2804: And AHL.workorder_id = p_wo_id;
2797: SELECT CSI.INVENTORY_ITEM_ID, AHL.organization_id
2798: , CSI.inventory_revision revision, CSI.quantity, CSI.unit_of_measure,
2799: CSI.serial_number, CSI.lot_number
2800: --FROM ahl_workorders_v AHL, csi_item_instances CSI
2801: FROM ahl_workorder_tasks_v AHL, csi_item_instances CSI
2802: Where CSI.instance_id = p_instance_id
2803: --and csi.inv_organization_id = ahl.organization_id
2804: And AHL.workorder_id = p_wo_id;
2805: BEGIN
3142: DISP.QUANTITY, DISP.UOM, disp.WO_OPERATION_ID, disp.item_revision revision,
3143: disp.serial_number, disp.lot_number,
3144: csi.inventory_item_id, vst.organization_id, vst.inv_locator_id,
3145: loc.subinventory_code, awo.operation_sequence_num
3146: FROM ahl_prd_dispositions_b disp, csi_item_instances csi,
3147: ahl_workorders wo, ahl_visits_b vst,
3148: mtl_item_locations_kfv loc, ahl_workorder_operations awo
3149: WHERE disp.instance_id = csi.instance_id
3150: AND disp.part_change_id = p_part_change_id
3633:
3634: --For the updated object_version number from csi_item_isntances
3635: CURSOR csi_item_instance_id_csr(p_instance_id IN NUMBER) IS
3636: select instance_id,object_Version_number,serial_number,quantity,wip_job_id
3637: from csi_item_instances CII
3638: where CII.instance_id = p_instance_id
3639: AND CII.ACTIVE_START_DATE <= SYSDATE
3640: AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
3641: AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
3645: AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)));
3646:
3647: CURSOR csi_item_instance_num_csr(p_instance_number IN VARCHAR2) IS
3648: select instance_id, object_Version_number,serial_number,quantity,wip_job_id
3649: from csi_item_instances CII
3650: where CII.instance_number = p_instance_number
3651: AND CII.ACTIVE_START_DATE <= SYSDATE
3652: AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
3653: AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
3667:
3668:
3669: CURSOR check_inst_nonserial(p_instance_id IN NUMBER) IS
3670: SELECT 'X'
3671: FROM mtl_system_items_b mtl, csi_item_instances csi
3672: WHERE csi.instance_id = p_instance_id
3673: AND csi.inventory_item_id = mtl.inventory_item_id
3674: AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
3675: AND mtl.serial_number_control_code = 1;
3973: l_instance_rec csi_datastructures_pub.instance_rec;
3974: --to find an expired instance if there
3975: /*CURSOR dest_instance_csr(p_source_instance_id IN NUMBER ) IS
3976: SELECT 'x'
3977: FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
3978: WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
3979: AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
3980: AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
3981: AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
4111: inventory_revision,
4112: object_version_number,
4113: wip_job_id,
4114: location_id
4115: FROM csi_item_instances
4116: WHERE instance_id = p_instance_id;
4117:
4118: l_source_inst_rec get_instance_attrib_csr%ROWTYPE;
4119:
4134: install_date,
4135: inventory_revision,
4136: object_version_number,
4137: wip_job_id,
4138: location_id FROM CSI_ITEM_INSTANCES CII
4139: WHERE INV_MASTER_ORGANIZATION_ID= p_inv_master_org_id
4140: AND INVENTORY_ITEM_ID = p_inventory_item_id
4141: AND WIP_JOB_ID= p_wip_job_id
4142: AND instance_id <> p_source_instance_id
4157: l_dest_instance_id NUMBER;
4158:
4159: CURSOR dest_inactive_instance_csr(p_source_instance_id IN NUMBER,p_wip_job_id IN NUMBER ) IS
4160: SELECT 'x'
4161: FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
4162: WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
4163: AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
4164: AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
4165: AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
4519: INVENTORY_REVISION,
4520: LOT_NUMBER,
4521: LOCATION_ID,
4522: INSTANCE_STATUS_ID
4523: FROM csi_item_instances
4524: WHERE instance_id = p_instance_id;
4525:
4526: CURSOR dest_instance_csr(p_wip_job_id IN NUMBER,
4527: p_source_instance_id IN NUMBER ) IS
4525:
4526: CURSOR dest_instance_csr(p_wip_job_id IN NUMBER,
4527: p_source_instance_id IN NUMBER ) IS
4528: SELECT CI1.instance_id
4529: FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
4530: WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
4531: AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
4532: AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
4533: AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
4548:
4549: CURSOR dest_instance_csr1(p_wip_job_id IN NUMBER,
4550: p_source_instance_id IN NUMBER ) IS
4551: SELECT CI1.instance_id
4552: FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
4553: WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
4554: AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
4555: AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
4556: AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
4832: l_transaction_type_id number;
4833:
4834: l_return_val boolean;
4835: l_active_end_date date;
4836: l_serial_number csi_item_instances.serial_number%TYPE;
4837: l_instance_ovn number;
4838: l_inst_condn_id number;
4839:
4840: l_junk varchar2(1);
4874: -- validate instance.
4875: BEGIN
4876: SELECT active_end_date, serial_number, object_version_number, instance_condition_id
4877: INTO l_active_end_date, l_serial_number, l_instance_ovn, l_inst_condn_id
4878: FROM CSI_ITEM_INSTANCES
4879: WHERE instance_id = p_instance_id;
4880: IF (l_active_end_date <= sysdate) THEN
4881: FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_INST_EXPIRED');
4882: FND_MESSAGE.Set_Token('INST_ID', p_instance_id);