[Home] [Help]
477: SELECT quantity + p_quantity,
478: object_version_number
479: INTO l_u_instance_rec.quantity,
480: l_u_instance_rec.object_version_number
481: FROM csi_item_instances
482: WHERE instance_id = p_instance_id;
483:
484: api_log(
485: p_pkg_name => 'csi_item_instance_pub',
550: SELECT quantity - p_quantity,
551: object_version_number
552: INTO l_u_instance_rec.quantity,
553: l_u_instance_rec.object_version_number
554: FROM csi_item_instances
555: WHERE instance_id = l_u_instance_rec.instance_id;
556:
557: api_log(
558: p_pkg_name => 'csi_item_instance_pub',
1658: l_u_instance_rec.vld_organization_id := p_organization_id;
1659:
1660: SELECT object_version_number
1661: INTO l_u_instance_rec.object_version_number
1662: FROM csi_item_instances
1663: WHERE instance_id = l_u_instance_rec.instance_id;
1664:
1665: api_log(
1666: p_pkg_name => 'csi_item_instance_pub',
1998: l_u_instance_rec.vld_organization_id := p_organization_id;
1999:
2000: SELECT object_version_number
2001: INTO l_u_instance_rec.object_version_number
2002: FROM csi_item_instances
2003: WHERE instance_id = l_u_instance_rec.instance_id;
2004:
2005: api_log(
2006: p_pkg_name => 'csi_item_instance_pub',
2222: BEGIN
2223:
2224: SELECT instance_id
2225: INTO l_child_instance_id
2226: FROM csi_item_instances
2227: WHERE inventory_item_id = mog_rec.child_item_id
2228: AND serial_number = mog_rec.child_serial_number;
2229:
2230: debug(' Instance ID :'||l_child_instance_id);
2364: BEGIN
2365: SELECT count(*)
2366: INTO l_alloc_count
2367: FROM csi_ii_relationships cir,
2368: csi_item_instances cii
2369: WHERE cir.object_id = p_object_id
2370: AND cir.relationship_type_code = 'COMPONENT-OF'
2371: AND sysdate BETWEEN nvl(cir.active_start_date, sysdate-1)
2372: AND nvl(cir.active_end_date, sysdate+1)
2550:
2551: CURSOR comp_cur (p_object_id in number, p_comp_item_id in number) IS
2552: SELECT cir.subject_id, cii.quantity
2553: FROM csi_ii_relationships cir,
2554: csi_item_instances cii
2555: WHERE cir.object_id = p_object_id
2556: AND cir.relationship_type_code = 'COMPONENT-OF'
2557: AND cii.instance_id = cir.subject_id
2558: AND cii.inventory_item_id = p_comp_item_id
2599: BEGIN
2600: SELECT sum(cii.quantity)
2601: INTO l_alloc_qty
2602: FROM csi_ii_relationships cir,
2603: csi_item_instances cii
2604: WHERE cir.object_id = p_object_id
2605: AND cir.relationship_type_code = 'COMPONENT-OF'
2606: AND cii.instance_id = cir.subject_id
2607: AND cii.inventory_item_id = p_comp_item_id;
3080:
3081: CURSOR rltns_cur(p_parent_instance_id IN number) IS
3082: SELECT cir.subject_id, cii.quantity
3083: FROM csi_ii_relationships cir,
3084: csi_item_instances cii
3085: WHERE cir.object_id = p_parent_instance_id
3086: AND cir.relationship_type_code = 'COMPONENT-OF'
3087: AND cii.instance_id = cir.subject_id
3088: AND cii.inventory_item_id = p_component_item_id
3180:
3181: BEGIN
3182: SELECT instance_id
3183: INTO l_parent_instance_id
3184: FROM csi_item_instances
3185: WHERE inventory_item_id = l_parent_item_id
3186: AND serial_number = l_parent_serial_number
3187: AND nvl(active_end_date,sysdate+1) > sysdate; --fix for bug 5393515
3188:
3876: AND msi.inventory_item_id = mmt.inventory_item_id
3877: AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
3878: --Added for bug#14374084
3879: AND EXISTS
3880: ( SELECT 1 FROM csi_item_instances
3881: WHERE inventory_item_id = mmt.inventory_item_id
3882: AND creation_date < mmt.creation_date) ;
3883: --Added for bug#14374084 ;
3884:
4944:
4945: x_return_status := fnd_api.g_ret_sts_success;
4946: api_log('get_issued_serial_instance');
4947:
4948: -- look for a serialized instance in csi_item_instances
4949: -- if it is in customer location then
4950:
4951: l_inst_query_rec.inventory_item_id := px_instance_rec.inventory_item_id;
4952: l_inst_query_rec.serial_number := px_instance_rec.serial_number;
5517: AND mmt.transaction_type_id = 17;
5518:
5519: CURSOR child_item_csr (p_inventory_item_id IN NUMBER, p_serial_number IN varchar2) IS
5520: select cir.subject_id,ci2.quantity
5521: from csi_item_instances ci1, csi_item_instances ci2, csi_ii_relationships cir
5522: where ci1.instance_id = cir.object_id
5523: and ci1.inventory_item_id = p_inventory_item_id
5524: and ci1.serial_number = p_serial_number
5525: and ci2.inventory_item_id = p_mmt_rec.inventory_item_id
5689:
5690: BEGIN
5691: select cir.subject_id,ci2.quantity
5692: into l_instance_id, l_quantity
5693: from csi_item_instances ci1, csi_item_instances ci2, csi_ii_relationships cir
5694: where ci1.instance_id = cir.object_id
5695: and ci1.inventory_item_id = l_inv_item_id
5696: and ci1.serial_number = l_serial_number
5697: and ci2.inventory_item_id = p_mmt_rec.inventory_item_id
6039: INTO l_comp_is_assy
6040: FROM sys.dual
6041: WHERE exists (
6042: SELECT 'X'
6043: FROM csi_item_instances
6044: WHERE last_vld_organization_id = p_mmt_rec.organization_id
6045: AND location_type_code = 'WIP'
6046: AND wip_job_id = p_mmt_rec.transaction_source_id
6047: AND serial_number = p_mmt_rec.serial_number
6622: SELECT quantity,
6623: last_vld_organization_id
6624: INTO l_sub_instance_qty,
6625: l_vld_organization_id
6626: FROM csi_item_instances
6627: WHERE instance_id = l_sub_instance_id;
6628:
6629: IF l_sub_instance_qty > l_sub_tld_qty THEN
6630:
6926: CURSOR wip_inst_cur(p_wip_entity_id IN number, p_instance_id IN number) IS
6927: SELECT instance_id,
6928: quantity,
6929: serial_number
6930: FROM csi_item_instances
6931: WHERE location_type_code = 'WIP'
6932: AND wip_job_id = p_wip_entity_id
6933: AND instance_id <> p_instance_id;
6934: