[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cln_generic_s.nextval INTO l_event_key FROM Dual;
SELECT etph.party_type, etpv.party_id, etpv.party_site_id, etpv.party_name
INTO l_tr_partner_type, l_tr_partner_id, l_tr_partner_site, l_party_name
FROM ecx_tp_headers_v etpv, ecx_tp_headers etph
WHERE etph.tp_header_id = p_inv_user
AND etph.tp_header_id = etpv.tp_header_id;
SELECT cln_generic_s.nextval INTO l_xmlg_document_id FROM Dual;
SELECT TO_CHAR(cln_sync_inv_doc_s.nextval) INTO l_doc_number FROM Dual;
SELECT 'x' INTO l_dummy_check FROM DUAL
WHERE EXISTS(
SELECT 'x'
FROM CLN_INVENTORY_REPORT_V
WHERE organization_id = p_inv_org
AND (concatenated_segments BETWEEN nvl(p_item_number_from,concatenated_segments) AND nvl(p_item_number_to,concatenated_segments))
AND (((p_item_revision_from IS NULL OR p_item_revision_to IS NULL) AND CLN_INVENTORY_REPORT_V.REVISION_QTY_CONTROL_CODE =1)or revision BETWEEN nvl(p_item_revision_from,revision) AND nvl(p_item_revision_to,revision))
AND EXISTS ( SELECT 'X' FROM MTL_ITEM_CATEGORIES MIC
WHERE (CLN_INVENTORY_REPORT_V.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID(+)
AND CLN_INVENTORY_REPORT_V.ORGANIZATION_ID = MIC.ORGANIZATION_ID(+))
AND MIC.category_id = nvl(p_item_category,MIC.category_id))
AND EXISTS (
SELECT 'X' FROM mtl_secondary_inventories msi, mtl_onhand_quantities_detail moqd
WHERE msi.organization_id = moqd.organization_id
AND msi.secondary_inventory_name = moqd.subinventory_code
AND msi.organization_id = CLN_INVENTORY_REPORT_V.organization_id
AND msi.secondary_inventory_name = NVL(p_sub_inv,msi.secondary_inventory_name)
AND moqd.inventory_item_id = CLN_INVENTORY_REPORT_V.inventory_item_id
AND (CLN_INVENTORY_REPORT_V.revision IS NULL OR moqd.revision IS NULL OR moqd.revision = CLN_INVENTORY_REPORT_V.revision)
)
AND ( (p_lot_number IS NULL AND CLN_INVENTORY_REPORT_V.LOT_CONTROL_CODE = 1) OR
EXISTS (
SELECT 'X' FROM MTL_LOT_NUMBERS MLN
WHERE CLN_INVENTORY_REPORT_V.inventory_item_id = mln.inventory_item_id(+)
AND CLN_INVENTORY_REPORT_V.organization_id = mln.organization_id(+)
AND mln.lot_number = NVL(p_lot_number,mln.lot_number)
)
)
);
FND_MESSAGE.SET_NAME('CLN','CLN_CH_NO_ROW_SELECTED');
SELECT mci.customer_item_number
INTO x_customer_item_number
FROM
mtl_customer_items mci ,
mtl_customer_item_xrefs mcix
WHERE mcix.master_organization_id = p_inv_org
AND mcix.inventory_item_id = p_item_number
AND mci.customer_id = p_tp_id
AND mci.customer_item_id(+) = mcix.customer_item_id;
SELECT nvl(sum(moqd.primary_transaction_quantity),0)
INTO x_quantity_blocked
FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
WHERE moqd.organization_id = p_inv_org
AND moqd.inventory_item_id = p_item_number
AND moqd.subinventory_code = msi.secondary_inventory_name
AND moqd.organization_id = msi.organization_id
AND msi.availability_type = 2
AND moqd.subinventory_code = nvl(p_sub_inv,moqd.subinventory_code)
AND (p_item_revision IS NULL OR moqd.revision IS NULL OR moqd.revision = p_item_revision)
AND (p_lot_number IS NULL OR moqd.lot_number = p_lot_number);
cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
SELECT nvl(sum(moqd.primary_transaction_quantity),0)
INTO x_quantity_blocked
FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
WHERE moqd.organization_id = p_inv_org
AND moqd.inventory_item_id = p_item_number
AND moqd.subinventory_code = msi.secondary_inventory_name
AND moqd.organization_id = msi.organization_id
AND msi.inventory_atp_code = 2
AND moqd.subinventory_code = nvl(p_sub_inv,moqd.subinventory_code)
AND (p_item_revision IS NULL OR moqd.revision IS NULL OR moqd.revision = p_item_revision)
AND (p_lot_number IS NULL OR moqd.lot_number = p_lot_number);
cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
SELECT nvl(sum(moqd.primary_transaction_quantity),0)
INTO x_quantity_blocked
FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
WHERE moqd.organization_id = p_inv_org
AND moqd.inventory_item_id = p_item_number
AND moqd.subinventory_code = msi.secondary_inventory_name
AND moqd.organization_id = msi.organization_id
AND (msi.inventory_atp_code = 2 OR msi.availability_type = 2)
AND moqd.subinventory_code = nvl(p_sub_inv,moqd.subinventory_code)
AND (p_item_revision IS NULL OR moqd.revision IS NULL OR moqd.revision = p_item_revision)
AND (p_lot_number IS NULL OR moqd.lot_number = p_lot_number);
cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);