[Home] [Help]
565: FROM fa_distribution_history fdh,
566: csi_i_assets cia,
567: fa_additions fa,
568: fa_books fb,
569: csi_item_instances cii
570: WHERE cii.instance_id = c_instance_id
571: AND cia.instance_id = cii.instance_id
572: AND cia.fa_asset_id = fdh.asset_id
573: AND cia.fa_book_type_code = fdh.book_type_code
1158: ct.transaction_date ,
1159: mmt.transaction_id inv_material_transaction_id ,
1160: ct.object_version_number,
1161: cii.operational_status_code
1162: FROM csi_item_instances cii,
1163: csi_item_instances_h ciih,
1164: csi_transactions ct,
1165: mtl_material_transactions mmt,
1166: csi_txn_types ctt
1159: mmt.transaction_id inv_material_transaction_id ,
1160: ct.object_version_number,
1161: cii.operational_status_code
1162: FROM csi_item_instances cii,
1163: csi_item_instances_h ciih,
1164: csi_transactions ct,
1165: mtl_material_transactions mmt,
1166: csi_txn_types ctt
1167: WHERE ct.transaction_id = p_transaction_id
1449: IF p_source_dest_flag = 'D' THEN
1450:
1451: SELECT creation_date
1452: INTO l_time_stamp
1453: FROM csi_item_instances_h
1454: WHERE transaction_id = l_transaction_id
1455: AND instance_id = p_instance_id;
1456:
1457: ELSIF p_source_dest_flag = 'S' THEN
1457: ELSIF p_source_dest_flag = 'S' THEN
1458:
1459: SELECT max(transaction_id)
1460: INTO l_transaction_id
1461: FROM csi_item_instances_h
1462: WHERE instance_id = p_instance_id
1463: AND transaction_id < l_transaction_id;
1464:
1465: SELECT creation_date
1463: AND transaction_id < l_transaction_id;
1464:
1465: SELECT creation_date
1466: INTO l_time_stamp
1467: FROM csi_item_instances_h
1468: WHERE transaction_id = l_transaction_id
1469: AND instance_id = p_instance_id;
1470:
1471: END IF;
1557: cii.inventory_item_id,
1558: cii.last_vld_organization_id,
1559: msi.serial_number_control_code,
1560: msi.primary_unit_of_measure
1561: FROM csi_item_instances_h ciih,
1562: csi_item_instances cii,
1563: mtl_system_items msi
1564: WHERE ciih.transaction_id = p_transaction_id
1565: AND cii.instance_id = ciih.instance_id
1558: cii.last_vld_organization_id,
1559: msi.serial_number_control_code,
1560: msi.primary_unit_of_measure
1561: FROM csi_item_instances_h ciih,
1562: csi_item_instances cii,
1563: mtl_system_items msi
1564: WHERE ciih.transaction_id = p_transaction_id
1565: AND cii.instance_id = ciih.instance_id
1566: AND msi.inventory_item_id = cii.inventory_item_id
1571: cii.serial_number,
1572: cii.instance_usage_code,
1573: nvl(ciih.old_quantity,0) old_quantity,
1574: nvl(ciih.new_quantity, 0) new_quantity
1575: FROM csi_item_instances_h ciih,
1576: csi_item_instances cii
1577: WHERE ciih.transaction_id = p_transaction_id
1578: AND cii.instance_id = ciih.instance_id
1579: AND cii.inventory_item_id = p_item_id;
1572: cii.instance_usage_code,
1573: nvl(ciih.old_quantity,0) old_quantity,
1574: nvl(ciih.new_quantity, 0) new_quantity
1575: FROM csi_item_instances_h ciih,
1576: csi_item_instances cii
1577: WHERE ciih.transaction_id = p_transaction_id
1578: AND cii.instance_id = ciih.instance_id
1579: AND cii.inventory_item_id = p_item_id;
1580:
1583: cii.serial_number,
1584: cii.instance_usage_code,
1585: cit.transaction_id,
1586: cit.transaction_type_id
1587: FROM csi_item_instances_h ciih,
1588: csi_item_instances cii,
1589: csi_transactions cit,
1590: csi_i_assets cia
1591: WHERE cit.transaction_id <= p_transaction_id
1584: cii.instance_usage_code,
1585: cit.transaction_id,
1586: cit.transaction_type_id
1587: FROM csi_item_instances_h ciih,
1588: csi_item_instances cii,
1589: csi_transactions cit,
1590: csi_i_assets cia
1591: WHERE cit.transaction_id <= p_transaction_id
1592: AND cii.inventory_item_id = p_item_id
1834: BEGIN
1835:
1836: SELECT a.instance_id , a.transaction_id
1837: INTO l_instance_id, l_transaction_id
1838: FROM csi_item_instances_h a,
1839: ( SELECT b.transaction_id, b.instance_id
1840: FROM csi_inst_txn_details_v b
1841: WHERE b.transaction_id > l_transaction_id
1842: AND b.instance_id = l_instance_id
2585: ,citdv.inv_material_transaction_id
2586: ,citdv.source_transaction_type
2587: ,citdv.object_version_number
2588: FROM csi_inst_txn_details_v citdv,
2589: csi_item_instances_h ciih
2590: WHERE citdv.transaction_id = ciih.transaction_id
2591: AND citdv.instance_id = ciih.instance_id
2592: AND citdv.transaction_status_code = cse_datastructures_pub.G_PENDING
2593: AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
2624: NVL(ciih.old_quantity,0)), 1) primary_units
2625: ,citdv.serial_number serial_number
2626: ,citdv.object_version_number
2627: FROM csi_inst_txn_details_v citdv ,
2628: csi_item_instances_h ciih
2629: WHERE citdv.transaction_id = c_src_transaction_id
2630: AND ciih.transaction_id = citdv.transaction_id
2631: AND ciih.instance_id = citdv.instance_id
2632: AND NVL(ciih.old_quantity,0) < NVL(ciih.new_quantity,0)
3230: ct.transaction_date,
3231: cii.inventory_revision,
3232: cii.instance_usage_code
3233: FROM csi_transactions ct,
3234: csi_item_instances_h ciih,
3235: csi_item_instances cii
3236: WHERE ct.transaction_id = p_transaction_id
3237: AND ciih.transaction_id = ct.transaction_id
3238: AND cii.instance_id = ciih.instance_id
3231: cii.inventory_revision,
3232: cii.instance_usage_code
3233: FROM csi_transactions ct,
3234: csi_item_instances_h ciih,
3235: csi_item_instances cii
3236: WHERE ct.transaction_id = p_transaction_id
3237: AND ciih.transaction_id = ct.transaction_id
3238: AND cii.instance_id = ciih.instance_id
3239: AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
3276: ,citdv.transaction_quantity
3277: ,citdv.source_transaction_type
3278: FROM csi_inst_txn_details_v citdv,
3279: mtl_material_transactions mmt,
3280: csi_item_instances_h ciih
3281: WHERE mmt.inventory_item_id = c_inv_item_id
3282: AND mmt.organization_id = c_inv_org_id
3283: AND mmt.shipment_number = c_shipment_number
3284: AND citdv.transaction_id = ciih.transaction_id
3308: ct.transaction_date,
3309: cii.instance_usage_code,
3310: ct.transaction_quantity
3311: FROM csi_transactions ct,
3312: csi_item_instances_h ciih ,
3313: csi_item_instances cii
3314: WHERE ct.transaction_id = p_transaction_id
3315: AND ciih.transaction_id = ct.transaction_id
3316: AND cii.instance_id = ciih.instance_id
3309: cii.instance_usage_code,
3310: ct.transaction_quantity
3311: FROM csi_transactions ct,
3312: csi_item_instances_h ciih ,
3313: csi_item_instances cii
3314: WHERE ct.transaction_id = p_transaction_id
3315: AND ciih.transaction_id = ct.transaction_id
3316: AND cii.instance_id = ciih.instance_id
3317: AND cii.serial_number is NOT NULL
3347: ,citdv.transaction_quantity
3348: ,citdv.source_transaction_type
3349: ,citdv.inventory_item_id
3350: FROM csi_inst_txn_details_v citdv,
3351: csi_item_instances_h ciih,
3352: mtl_material_transactions mmt
3353: WHERE mmt.inventory_item_id = c_inv_item_id
3354: AND citdv.inv_material_transaction_id = mmt.transaction_id
3355: AND mmt.organization_id = c_inv_org_id
3739: SELECT cii.inventory_item_id,
3740: cii.last_vld_organization_id
3741: INTO l_inventory_item_id,
3742: l_organization_id
3743: FROM csi_item_instances cii,
3744: csi_item_instances_h ciih
3745: WHERE ciih.transaction_id = p_csi_txn_rec.transaction_id
3746: AND cii.instance_id = ciih.instance_id
3747: AND rownum = 1;
3740: cii.last_vld_organization_id
3741: INTO l_inventory_item_id,
3742: l_organization_id
3743: FROM csi_item_instances cii,
3744: csi_item_instances_h ciih
3745: WHERE ciih.transaction_id = p_csi_txn_rec.transaction_id
3746: AND cii.instance_id = ciih.instance_id
3747: AND rownum = 1;
3748:
4012: ciih.old_location_type_code,
4013: ciih.old_location_id,
4014: ciih.new_location_type_code,
4015: ciih.new_location_id
4016: FROM csi_item_instances_h ciih,
4017: csi_item_instances cii
4018: WHERE ciih.transaction_id = p_csi_txn_id
4019: AND cii.instance_id = ciih.instance_id
4020: AND cii.inventory_item_id = p_inventory_item_id;
4013: ciih.old_location_id,
4014: ciih.new_location_type_code,
4015: ciih.new_location_id
4016: FROM csi_item_instances_h ciih,
4017: csi_item_instances cii
4018: WHERE ciih.transaction_id = p_csi_txn_id
4019: AND cii.instance_id = ciih.instance_id
4020: AND cii.inventory_item_id = p_inventory_item_id;
4021:
4040:
4041: CURSOR pend_txn_cur(p_instance_id IN number, p_csi_txn_id IN number, p_inv_item_id in NUMBER) IS
4042: SELECT ct.transaction_id
4043: FROM csi_transactions ct,
4044: csi_item_instances cii,
4045: csi_item_instances_h ciih
4046: WHERE ciih.instance_id = p_instance_id
4047: AND ciih.transaction_id < p_csi_txn_id
4048: AND cii.instance_id = ciih.instance_id
4041: CURSOR pend_txn_cur(p_instance_id IN number, p_csi_txn_id IN number, p_inv_item_id in NUMBER) IS
4042: SELECT ct.transaction_id
4043: FROM csi_transactions ct,
4044: csi_item_instances cii,
4045: csi_item_instances_h ciih
4046: WHERE ciih.instance_id = p_instance_id
4047: AND ciih.transaction_id < p_csi_txn_id
4048: AND cii.instance_id = ciih.instance_id
4049: AND cii.inventory_item_id = p_inv_item_id
4300: 326) -- PROJECT_CONTRACT_SHIPMENT
4301: AND ct.transaction_status_code = 'PENDING'
4302: AND EXISTS (
4303: SELECT 1
4304: FROM csi_item_instances_h ciih,
4305: csi_item_instances cii
4306: WHERE ciih.transaction_id = ct.transaction_id
4307: AND cii.instance_id = ciih.instance_id
4308: AND cii.inventory_item_id = nvl(p_inventory_item_id, cii.inventory_item_id))
4301: AND ct.transaction_status_code = 'PENDING'
4302: AND EXISTS (
4303: SELECT 1
4304: FROM csi_item_instances_h ciih,
4305: csi_item_instances cii
4306: WHERE ciih.transaction_id = ct.transaction_id
4307: AND cii.instance_id = ciih.instance_id
4308: AND cii.inventory_item_id = nvl(p_inventory_item_id, cii.inventory_item_id))
4309: ORDER BY ct.creation_date;