[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fspa.purch_encumbrance_flag,
fspa.req_encumbrance_flag,
fspa.set_of_books_id
INTO l_purch_flag ,
l_req_flag ,
l_sob_id
FROM FINANCIALS_SYSTEM_PARAMS_ALL fspa ,
po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = p_supplier_site_id
AND NVL(fspa.org_id,-99) = NVL(pvs.org_id,-99) ;
SELECT -- INVCONV imst.whse_code OPM Org code
msi.outside_operation_flag , -- OSP flag
msi.bom_item_type ,
msi.replenish_to_order_flag ,
msi.auto_created_config_flag ,
msi.base_item_id ,
msi.eam_item_type ,
msi.inventory_asset_flag ,
NVL(msi.mtl_transactions_enabled_flag, 'N'),
NVL(msi.stock_enabled_flag, 'N')
INTO -- INVCONV l_whse_code
l_OSP_flag ,
l_bom ,
l_replenish ,
l_autoconfig,
l_base ,
l_eam ,
l_asset ,
l_transactable,
l_stockable
FROM MTL_SYSTEM_ITEMS msi
-- INVCONV IC_WHSE_MST imst
WHERE -- INVCONV msi.organization_id = imst.mtl_organization_id (+)
msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_inventory_org_id ;
select hoi.organization_id ,
DECODE(HOI.ORG_INFORMATION_CONTEXT, 'Accounting Information',
TO_NUMBER(HOI.ORG_INFORMATION3), TO_NUMBER(NULL)) operating_unit ,
-- INVCONV imst.whse_code ,
msi.item_type ,
msi.outside_operation_flag ,
msi.eam_item_type ,
msi.base_item_id ,
msi.bom_item_type ,
msi.replenish_to_order_flag ,
msi.auto_created_config_flag ,
msi.inventory_asset_flag ,
msi.mtl_transactions_enabled_flag ,
msi.stock_enabled_flag
from gl_sets_of_books gsob ,
hr_organization_units hou ,
hr_organization_information hoi ,
mtl_parameters mp ,
hr_organization_information hoi2 ,
mtl_system_items msi
-- INVCONV ic_whse_mst imst
where HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
and HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
and HOI.ORG_INFORMATION_CONTEXT||'' ='Accounting Information'
and HOI.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
and hoi.organization_id = hoi2.organization_id
and hoi2.org_information_context = 'CLASS'
and hoi2.org_information1 = 'INV'
and msi.organization_id = hoi.organization_id
and msi.inventory_item_id = p_inventory_item_id
-- INVCONV and hoi.organization_id = imst.mtl_organization_id (+)
and GSOB.SET_OF_BOOKS_ID = (
select set_of_books_id
from po_vendor_sites_all pvsa ,
financials_system_params_all fspa
where pvsa.vendor_site_id = p_supplier_site_id
and NVL(fspa.org_id,-99)= NVL(pvsa.org_id,-99)
) ;
SELECT fspa.purch_encumbrance_flag,
fspa.req_encumbrance_flag,
fspa.set_of_books_id
INTO l_purch_flag ,
l_req_flag ,
l_sob_id
FROM FINANCIALS_SYSTEM_PARAMS_ALL fspa ,
po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = p_supplier_site_id
AND NVL(fspa.org_id,-99) = NVL(pvs.org_id,-99) ;
SELECT count('x')
INTO l_count_exist_TPS_ASL
FROM dual
WHERE exists
( SELECT 'X'
FROM po_approved_supplier_list pasl,
po_vendor_sites pvs ,
po_asl_status_rules_v pasr ,
po_asl_attributes paa
WHERE pasl.vendor_site_id = pvs.vendor_site_id
AND pasr.status_id = pasl.asl_status_id
AND pasr.business_rule like '2_SOURCING'
AND pasr.allow_action_flag like 'Y'
AND ( pasl.disable_flag = 'N'
OR pasl.disable_flag IS NULL)
AND paa.asl_id = pasl.asl_id
AND ( paa.enable_vmi_flag = 'Y'
OR paa.consigned_from_supplier_flag = 'Y')
) ;
SELECT COUNT('x')
INTO l_open
FROM dual
WHERE EXISTS
(SELECT 'x'
FROM po_headers_all
WHERE consigned_consumption_flag = 'Y'
AND type_lookup_code = 'STANDARD'
AND vendor_site_id = p_vendor_site_id
AND vendor_id = p_vendor_id --bug 3649022
AND NVL(closed_code, 'a') <> 'FINALLY CLOSED'
AND NVL(cancel_flag, 'N') = 'N')
OR EXISTS
(SELECT 'x'
FROM po_releases_all por, po_headers_all poh
WHERE por.consigned_consumption_flag = 'Y'
AND poh.vendor_site_id = p_vendor_site_id
AND poh.vendor_id = p_vendor_id --bug 3649022
AND poh.po_header_id = por.po_header_id
AND nvl(por.closed_code, 'a') <> 'FINALLY CLOSED'
AND nvl(por.cancel_flag, 'N') = 'N')
OR EXISTS
(SELECT 'x'
FROM po_line_locations_all pol, po_releases_all por,
po_headers_all poh
WHERE poh.vendor_site_id = p_vendor_site_id
AND poh.vendor_id = p_vendor_id --bug 3649022
AND por.po_header_id = poh.po_header_id
AND pol.po_release_id = por.po_release_id
AND pol.vmi_flag = 'Y'
AND NVL(pol.closed_code, 'a') <> 'FINALLY CLOSED'
AND NVL(pol.cancel_flag, 'N') = 'N')
OR EXISTS
(SELECT 'x'
FROM po_line_locations_all pol, po_headers_all poh
WHERE poh.vendor_site_id = p_vendor_site_id
AND poh.vendor_id = p_vendor_id --bug 3649022
AND poh.po_header_id = pol.po_header_id
AND pol.consigned_flag = 'Y'
AND nvl(pol.closed_code, 'a') <> 'FINALLY CLOSED'
AND nvl(pol.cancel_flag, 'N') = 'N');
SELECT NVL(inventory_asset_flag, 'N')
INTO x_inventory_asset_flag
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT DISTINCT --pll.line_location_id, --Bug 14664015
pl.item_id,
pll.ship_to_organization_id,
pll.consigned_flag
FROM po_line_locations_all pll,
po_lines_all pl
WHERE pll.po_header_id = p_po_header_id
AND pl.po_line_id = pll.po_line_id;
SELECT inventory_organization_id
INTO l_inventory_org_id
FROM financials_system_params_all
WHERE org_id = p_org_id;