VIEW: APPS.POS_PO_SUPPLIER_ITEMS_V

Source

View Text - Preformatted

SELECT DISTINCT MSI.CONCATENATED_SEGMENTS, MSI.DESCRIPTION, NVL(PAA.PURCHASING_UNIT_OF_MEASURE,MSI.PRIMARY_UNIT_OF_MEASURE), ASL.ITEM_ID, MSI.BUYER_ID, HRV.FULL_NAME, POV.VENDOR_NAME, POV.SEGMENT1 VENDOR_NUMBER, ASL.VENDOR_ID, PVS.VENDOR_SITE_CODE, ASL.VENDOR_SITE_ID, POV.ATTRIBUTE14, MSI.ATTRIBUTE14, MSI.ORGANIZATION_ID, HOU.NAME, MP.ORGANIZATION_CODE, ASL.PRIMARY_VENDOR_ITEM, PAA.CONSIGNED_FROM_SUPPLIER_FLAG, PAA.ENABLE_VMI_FLAG FROM MTL_SYSTEM_ITEMS_VL MSI, PO_VENDORS POV, PO_VENDOR_SITES_ALL PVS, PER_ALL_PEOPLE_F HRV, HR_ALL_ORGANIZATION_UNITS_TL HOU, PO_APPROVED_SUPPLIER_LIST ASL, PO_ASL_STATUS_RULES ASR, PO_ASL_ATTRIBUTES PAA, MTL_PARAMETERS MP WHERE ASL.ITEM_ID = MSI.INVENTORY_ITEM_ID AND ASL.VENDOR_ID = POV.VENDOR_ID AND ASL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND ASL.OWNING_ORGANIZATION_ID = MSI.ORGANIZATION_ID AND HRV.PERSON_ID (+) = MSI.BUYER_ID AND trunc(sysdate) BETWEEN HRV.EFFECTIVE_START_DATE (+) AND HRV.EFFECTIVE_END_DATE (+) AND HOU.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID AND HOU.LANGUAGE (+) = USERENV('LANG') AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID AND ASL.ASL_STATUS_ID = ASR.STATUS_ID AND ASR.BUSINESS_RULE = '1_PO_APPROVAL' AND ASL.ASL_ID = PAA.ASL_ID AND ASL.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID AND NVL(ASL.DISABLE_FLAG, 'N') = 'N'

View Text - HTML Formatted

SELECT DISTINCT MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, NVL(PAA.PURCHASING_UNIT_OF_MEASURE
, MSI.PRIMARY_UNIT_OF_MEASURE)
, ASL.ITEM_ID
, MSI.BUYER_ID
, HRV.FULL_NAME
, POV.VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, ASL.VENDOR_ID
, PVS.VENDOR_SITE_CODE
, ASL.VENDOR_SITE_ID
, POV.ATTRIBUTE14
, MSI.ATTRIBUTE14
, MSI.ORGANIZATION_ID
, HOU.NAME
, MP.ORGANIZATION_CODE
, ASL.PRIMARY_VENDOR_ITEM
, PAA.CONSIGNED_FROM_SUPPLIER_FLAG
, PAA.ENABLE_VMI_FLAG
FROM MTL_SYSTEM_ITEMS_VL MSI
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, PER_ALL_PEOPLE_F HRV
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PO_APPROVED_SUPPLIER_LIST ASL
, PO_ASL_STATUS_RULES ASR
, PO_ASL_ATTRIBUTES PAA
, MTL_PARAMETERS MP
WHERE ASL.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ASL.VENDOR_ID = POV.VENDOR_ID
AND ASL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND ASL.OWNING_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND HRV.PERSON_ID (+) = MSI.BUYER_ID
AND TRUNC(SYSDATE) BETWEEN HRV.EFFECTIVE_START_DATE (+)
AND HRV.EFFECTIVE_END_DATE (+)
AND HOU.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND HOU.LANGUAGE (+) = USERENV('LANG')
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ASL.ASL_STATUS_ID = ASR.STATUS_ID
AND ASR.BUSINESS_RULE = '1_PO_APPROVAL'
AND ASL.ASL_ID = PAA.ASL_ID
AND ASL.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID
AND NVL(ASL.DISABLE_FLAG
, 'N') = 'N'