[Home] [Help]
2278: l_new_csi_instance_ovn NUMBER;
2279: l_parent_uc_header_id NUMBER;
2280: l_parent_instance_id NUMBER;
2281: l_position_ref_meaning fnd_lookups.meaning%TYPE;
2282: l_interchange_type_code ahl_item_associations_b.interchange_type_code%TYPE;
2283: l_interchange_reason ahl_item_associations_tl.interchange_reason%TYPE;
2284:
2285: --Variables needed for CSI API call
2286: l_csi_instance_rec csi_datastructures_pub.instance_rec;
2402: CURSOR get_interchange_type (c_instance_id NUMBER, c_relationship_id NUMBER) IS
2403: SELECT i.interchange_type_code,
2404: itl.interchange_reason
2405: FROM csi_item_instances c,
2406: ahl_item_associations_b i,
2407: ahl_item_associations_tl itl,
2408: ahl_mc_relationships m
2409: WHERE m.relationship_id = c_relationship_id
2410: AND c.instance_id = c_instance_id
3349: l_instance_ou NUMBER;
3350: l_uc_status_code FND_LOOKUP_VALUES.lookup_code%TYPE;
3351: l_active_uc_status_code FND_LOOKUP_VALUES.lookup_code%TYPE;
3352: l_position_ref_meaning fnd_lookups.meaning%TYPE;
3353: l_interchange_type_code ahl_item_associations_b.interchange_type_code%TYPE;
3354: l_interchange_reason ahl_item_associations_tl.interchange_reason%TYPE;
3355:
3356: --Variables needed for CSI API call
3357: l_csi_party_rec csi_datastructures_pub.party_rec;
3603: CURSOR get_interchange_type (c_instance_id NUMBER, c_relationship_id NUMBER) IS
3604: SELECT i.interchange_type_code,
3605: itl.interchange_reason
3606: FROM csi_item_instances c,
3607: ahl_item_associations_b i,
3608: ahl_item_associations_tl itl,
3609: ahl_mc_relationships m
3610: WHERE m.relationship_id = c_relationship_id
3611: AND c.instance_id = c_instance_id
4668: to_number(NULL) uc_header_id
4669: FROM csi_item_instances C,
4670: mtl_system_items_kfv M,
4671: ahl_mc_relationships R,
4672: ahl_item_associations_b A
4673: WHERE C.inventory_item_id = M.inventory_item_id
4674: AND C.inv_master_organization_id = M.organization_id
4675: AND R.item_group_id = A.item_group_id
4676: AND C.inventory_item_id = A.inventory_item_id
5067: l_instance_details_rec ahl_instance_details%ROWTYPE;
5068:
5069: CURSOR get_priority (c_item_association_id IN NUMBER) IS
5070: SELECT priority
5071: FROM ahl_item_associations_b
5072: WHERE item_association_id = c_item_association_id;
5073:
5074: CURSOR get_csi_ii_relationship_ovn (c_instance_id NUMBER) IS
5075: SELECT object_version_number
5495: to_number(NULL) uc_header_id
5496: FROM csi_item_instances C,
5497: mtl_system_items_kfv M,
5498: ahl_mc_relationships R,
5499: ahl_item_associations_b A
5500: WHERE C.inventory_item_id = M.inventory_item_id
5501: AND C.inv_master_organization_id = M.organization_id
5502: AND R.item_group_id = A.item_group_id
5503: AND C.inventory_item_id = A.inventory_item_id
5715: WHERE config_status_code = 'COMPLETE');
5716:
5717: CURSOR get_priority (c_item_association_id IN NUMBER) IS
5718: SELECT priority
5719: FROM ahl_item_associations_b
5720: WHERE item_association_id = c_item_association_id;
5721:
5722: -- SATHAPLI Bug# 4912576 fix::SQLid 14402138
5723: -- The cursor definition below is changed so that reference
6743: CII.wip_job_id
6744: FROM csi_item_instances CII,
6745: mtl_system_items_kfv MSIK,
6746: ahl_mc_relationships AMR,
6747: ahl_item_associations_b AIA,
6748: hr_all_organization_units_tl HAOUT
6749: WHERE CII.inventory_item_id = NVL(c_inventory_item_id, CII.inventory_item_id)
6750: AND MSIK.inventory_item_id = CII.inventory_item_id
6751: AND MSIK.organization_id = CII.inv_master_organization_id
7117: SELECT CII.inventory_item_id
7118: FROM csi_item_instances CII,
7119: mtl_system_items_kfv MSIK,
7120: ahl_mc_relationships AMR,
7121: ahl_item_associations_b AIA
7122: WHERE CII.instance_id = c_instance_id
7123: AND MSIK.inventory_item_id = CII.inventory_item_id
7124: AND MSIK.organization_id = CII.inv_master_organization_id
7125: AND AMR.relationship_id = c_relationship_id