VIEW: APPS.PO_APPROVED_SUPPLIER_LIST_V

Source

View Text - Preformatted

SELECT PASL.ROWID , PASL.ASL_ID , PASL.OWNING_ORGANIZATION_ID , MP.ORGANIZATION_CODE , HOUT.NAME , PASL.USING_ORGANIZATION_ID , MP1.ORGANIZATION_CODE , HOUT1.NAME , PASL.VENDOR_BUSINESS_TYPE , PLC.DISPLAYED_FIELD , PASL.VENDOR_ID , PASL.MANUFACTURER_ID , DECODE(PASL.VENDOR_BUSINESS_TYPE, 'MANUFACTURER', MM.MANUFACTURER_NAME, PV.VENDOR_NAME) , PASL.ASL_STATUS_ID , PAST.STATUS , PASL.ITEM_ID , MSI.CONCATENATED_SEGMENTS , PASL.CATEGORY_ID , NULL , PASL.VENDOR_SITE_ID , PVS.VENDOR_SITE_CODE , PASL.PRIMARY_VENDOR_ITEM , PASL.MANUFACTURER_ASL_ID , MM1.MANUFACTURER_NAME , PASL.REVIEW_BY_DATE , PAA.DOCUMENT_SOURCING_METHOD , PLC2.DISPLAYED_FIELD , PAA.RELEASE_GENERATION_METHOD , PLC3.DISPLAYED_FIELD , PAA.PURCHASING_UNIT_OF_MEASURE , PAA.ENABLE_PLAN_SCHEDULE_FLAG , PAA.ENABLE_SHIP_SCHEDULE_FLAG , PAA.PLAN_SCHEDULE_TYPE , PLC4.DISPLAYED_FIELD , PAA.SHIP_SCHEDULE_TYPE , PLC5.DISPLAYED_FIELD , PAA.PLAN_BUCKET_PATTERN_ID , CBP.BUCKET_PATTERN_NAME , PAA.SHIP_BUCKET_PATTERN_ID , CBP1.BUCKET_PATTERN_NAME , PAA.ENABLE_AUTOSCHEDULE_FLAG , PAA.ENABLE_AUTHORIZATIONS_FLAG , PAA.SCHEDULER_ID , PPF.FULL_NAME , DECODE(PASL.USING_ORGANIZATION_ID, -1, 'Y','N') , DECODE(PAA.USING_ORGANIZATION_ID, -1, 'Y', 'N') , PAA.USING_ORGANIZATION_ID , MP2.ORGANIZATION_CODE , HOUT2.NAME , PASL.COMMENTS , PPF2.FULL_NAME , MSI.BUYER_ID , MSI.DESCRIPTION ,PASL.CREATION_DATE ,PASL.CREATED_BY ,PASL.LAST_UPDATE_LOGIN ,PASL.LAST_UPDATE_DATE ,PASL.LAST_UPDATED_BY ,PAA.PRICE_UPDATE_TOLERANCE ,PASL.DISABLE_FLAG ,PAA.PROCESSING_LEAD_TIME ,PAA.DELIVERY_CALENDAR ,PAA.MIN_ORDER_QTY ,PAA.FIXED_LOT_MULTIPLE ,PAA.COUNTRY_OF_ORIGIN_CODE ,PAA.ENABLE_VMI_FLAG , nvl(PVS.ORG_ID,po_moac_utils_pvt.get_current_org_id) org_id , WF_CORE.SUBSTITUTESPECIALCHARS(DECODE(PASL.VENDOR_BUSINESS_TYPE, 'MANUFACTURER', MM.MANUFACTURER_NAME, PV.VENDOR_NAME)) VENDOR_NAME_MASK ,WF_CORE.SUBSTITUTESPECIALCHARS(MSI.DESCRIPTION) ITEM_DESCRIPTION_MASK FROM PO_LOOKUP_CODES PLC , PO_LOOKUP_CODES PLC2 , PO_LOOKUP_CODES PLC3 , PO_LOOKUP_CODES PLC4 , PO_LOOKUP_CODES PLC5 , PER_PEOPLE_F PPF , PER_PEOPLE_F PPF2 , MTL_SYSTEM_ITEMS_KFV MSI , MTL_MANUFACTURERS MM , MTL_MANUFACTURERS MM1 , MTL_PARAMETERS MP , MTL_PARAMETERS MP1 , MTL_PARAMETERS MP2 , HR_ORG_UNITS_NO_JOIN HOU , HR_ORG_UNITS_NO_JOIN HOU1 , HR_ORG_UNITS_NO_JOIN HOU2 , HR_ALL_ORGANIZATION_UNITS_TL HOUT , HR_ALL_ORGANIZATION_UNITS_TL HOUT1 , HR_ALL_ORGANIZATION_UNITS_TL HOUT2 , PO_VENDOR_SITES PVS , PO_VENDORS PV , CHV_BUCKET_PATTERNS CBP1 , CHV_BUCKET_PATTERNS CBP , PO_ASL_STATUSES PAST , PO_APPROVED_SUPPLIER_LIST PASL1 , PO_ASL_ATTRIBUTES PAA , PO_APPROVED_SUPPLIER_LIST PASL WHERE PASL.ASL_ID = PAA.ASL_ID AND PASL.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID AND PASL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+) AND ((PASL.VENDOR_SITE_ID IS NOT NULL AND PVS.VENDOR_SITE_CODE IS NOT NULL) OR (PASL.VENDOR_SITE_ID IS NULL AND PVS.VENDOR_SITE_CODE IS NULL)) AND PASL.VENDOR_BUSINESS_TYPE = PLC.LOOKUP_CODE AND PLC.LOOKUP_TYPE = 'ASL_VENDOR_BUSINESS_TYPE' AND PASL.ASL_STATUS_ID = PAST.STATUS_ID AND PASL.VENDOR_ID = PV.VENDOR_ID(+) AND PASL.MANUFACTURER_ID = MM.MANUFACTURER_ID(+) AND PASL.MANUFACTURER_ASL_ID = PASL1.ASL_ID(+) AND PASL1.MANUFACTURER_ID = MM1.MANUFACTURER_ID(+) AND PASL.OWNING_ORGANIZATION_ID = MP.ORGANIZATION_ID AND PASL.OWNING_ORGANIZATION_ID = HOU.ORGANIZATION_ID AND PASL.USING_ORGANIZATION_ID = MP1.ORGANIZATION_ID(+) AND PASL.USING_ORGANIZATION_ID = HOU1.ORGANIZATION_ID(+) AND PAA.USING_ORGANIZATION_ID = MP2.ORGANIZATION_ID(+) AND PAA.USING_ORGANIZATION_ID = HOU2.ORGANIZATION_ID(+) AND PAA.PLAN_BUCKET_PATTERN_ID = CBP.BUCKET_PATTERN_ID(+) AND PAA.SHIP_BUCKET_PATTERN_ID = CBP1.BUCKET_PATTERN_ID(+) AND PAA.SCHEDULER_ID = PPF.PERSON_ID(+) AND ((PPF.PERSON_ID IS NULL) OR (TRUNC(SYSDATE) BETWEEN TRUNC(PPF.EFFECTIVE_START_DATE) AND TRUNC(PPF.EFFECTIVE_END_DATE))) AND PAA.SHIP_SCHEDULE_TYPE = PLC5.LOOKUP_CODE(+) AND PLC5.LOOKUP_TYPE(+) = 'SHIP_SCHEDULE_SUBTYPE' AND PAA.PLAN_SCHEDULE_TYPE = PLC4.LOOKUP_CODE(+) AND PLC4.LOOKUP_TYPE(+) = 'PLAN_SCHEDULE_SUBTYPE' AND PAA.RELEASE_GENERATION_METHOD = PLC3.LOOKUP_CODE(+) AND PLC3.LOOKUP_TYPE(+) = 'DOC GENERATION METHOD' AND PLC2.LOOKUP_TYPE(+) = 'DOCUMENT_SOURCING_METHOD' AND PAA.DOCUMENT_SOURCING_METHOD = PLC2.LOOKUP_CODE (+) AND PASL.OWNING_ORGANIZATION_ID = MSI.ORGANIZATION_ID AND PASL.ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.BUYER_ID = PPF2.PERSON_ID (+) AND ((PPF2.PERSON_ID IS NULL) OR (TRUNC(SYSDATE) BETWEEN TRUNC(PPF2.EFFECTIVE_START_DATE) AND TRUNC(PPF2.EFFECTIVE_END_DATE))) AND PASL.ITEM_ID IS NOT NULL AND hou.organization_id = hout.organization_id (+) AND DECODE(hout.organization_id, null, '1', hout.language) = DECODE(hout.organization_id, null, '1', USERENV('LANG')) AND hou1.organization_id = hout1.organization_id (+) AND DECODE(hout1.organization_id, null, '1', hout1.language) = DECODE(hout1.organization_id, null, '1', USERENV('LANG')) AND hou2.organization_id = hout2.organization_id (+) AND DECODE(hout2.organization_id, null, '1', hout2.language) = DECODE(hout2.organization_id, null, '1', USERENV('LANG')) UNION SELECT PASL.ROWID ,PASL.ASL_ID , PASL.OWNING_ORGANIZATION_ID , MP.ORGANIZATION_CODE , HOUT.NAME , PASL.USING_ORGANIZATION_ID , MP1.ORGANIZATION_CODE , HOUT1.NAME , PASL.VENDOR_BUSINESS_TYPE , PLC.DISPLAYED_FIELD , PASL.VENDOR_ID , PASL.MANUFACTURER_ID , DECODE(PASL.VENDOR_BUSINESS_TYPE, 'MANUFACTURER', MM.MANUFACTURER_NAME, PV.VENDOR_NAME) , PASL.ASL_STATUS_ID , PAST.STATUS , PASL.ITEM_ID , NULL , PASL.CATEGORY_ID , MC.CONCATENATED_SEGMENTS , PASL.VENDOR_SITE_ID , PVS.VENDOR_SITE_CODE , PASL.PRIMARY_VENDOR_ITEM , PASL.MANUFACTURER_ASL_ID , MM1.MANUFACTURER_NAME , PASL.REVIEW_BY_DATE , PAA.DOCUMENT_SOURCING_METHOD , PLC2.DISPLAYED_FIELD , PAA.RELEASE_GENERATION_METHOD , PLC3.DISPLAYED_FIELD , PAA.PURCHASING_UNIT_OF_MEASURE , PAA.ENABLE_PLAN_SCHEDULE_FLAG , PAA.ENABLE_SHIP_SCHEDULE_FLAG , PAA.PLAN_SCHEDULE_TYPE , PLC4.DISPLAYED_FIELD , PAA.SHIP_SCHEDULE_TYPE , PLC5.DISPLAYED_FIELD , PAA.PLAN_BUCKET_PATTERN_ID , CBP.BUCKET_PATTERN_NAME , PAA.SHIP_BUCKET_PATTERN_ID , CBP1.BUCKET_PATTERN_NAME , PAA.ENABLE_AUTOSCHEDULE_FLAG , PAA.ENABLE_AUTHORIZATIONS_FLAG , PAA.SCHEDULER_ID , PPF.FULL_NAME , DECODE(PASL.USING_ORGANIZATION_ID, -1, 'Y','N') , DECODE(PAA.USING_ORGANIZATION_ID, -1, 'Y', 'N') , PAA.USING_ORGANIZATION_ID , MP2.ORGANIZATION_CODE , HOUT2.NAME , PASL.COMMENTS , NULL , TO_NUMBER(NULL) , NULL ,PASL.CREATION_DATE ,PASL.CREATED_BY ,PASL.LAST_UPDATE_LOGIN ,PASL.LAST_UPDATE_DATE ,PASL.LAST_UPDATED_BY ,PAA.PRICE_UPDATE_TOLERANCE ,PASL.DISABLE_FLAG ,PAA.PROCESSING_LEAD_TIME ,PAA.DELIVERY_CALENDAR ,PAA.MIN_ORDER_QTY ,PAA.FIXED_LOT_MULTIPLE ,PAA.COUNTRY_OF_ORIGIN_CODE ,PAA.ENABLE_VMI_FLAG , nvl(PVS.ORG_ID,po_moac_utils_pvt.get_current_org_id) org_id ,WF_CORE.SUBSTITUTESPECIALCHARS(DECODE(PASL.VENDOR_BUSINESS_TYPE, 'MANUFACTURER', MM.MANUFACTURER_NAME, PV.VENDOR_NAME)) VENDOR_NAME_MASK ,NULL ITEM_DESCRIPTION_MASK FROM PO_LOOKUP_CODES PLC , PO_LOOKUP_CODES PLC2 , PO_LOOKUP_CODES PLC3 , PO_LOOKUP_CODES PLC4 , PO_LOOKUP_CODES PLC5 , PER_PEOPLE_F PPF , MTL_CATEGORIES_KFV MC , MTL_MANUFACTURERS MM , MTL_MANUFACTURERS MM1 , MTL_PARAMETERS MP , MTL_PARAMETERS MP1 , MTL_PARAMETERS MP2 , HR_ORG_UNITS_NO_JOIN HOU , HR_ORG_UNITS_NO_JOIN HOU1 , HR_ORG_UNITS_NO_JOIN HOU2 , HR_ALL_ORGANIZATION_UNITS_TL HOUT , HR_ALL_ORGANIZATION_UNITS_TL HOUT1 , HR_ALL_ORGANIZATION_UNITS_TL HOUT2 , PO_VENDOR_SITES PVS , PO_VENDORS PV , CHV_BUCKET_PATTERNS CBP1 , CHV_BUCKET_PATTERNS CBP , PO_ASL_STATUSES PAST , PO_APPROVED_SUPPLIER_LIST PASL1 , PO_ASL_ATTRIBUTES PAA , PO_APPROVED_SUPPLIER_LIST PASL WHERE PASL.ASL_ID = PAA.ASL_ID AND PASL.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID AND PASL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+) AND ((PASL.VENDOR_SITE_ID IS NOT NULL AND PVS.VENDOR_SITE_CODE IS NOT NULL) OR (PASL.VENDOR_SITE_ID IS NULL AND PVS.VENDOR_SITE_CODE IS NULL)) AND PASL.VENDOR_BUSINESS_TYPE = PLC.LOOKUP_CODE AND PLC.LOOKUP_TYPE = 'ASL_VENDOR_BUSINESS_TYPE' AND PASL.ASL_STATUS_ID = PAST.STATUS_ID AND PASL.VENDOR_ID = PV.VENDOR_ID(+) AND PASL.MANUFACTURER_ID = MM.MANUFACTURER_ID(+) AND PASL.MANUFACTURER_ASL_ID = PASL1.ASL_ID(+) AND PASL1.MANUFACTURER_ID = MM1.MANUFACTURER_ID(+) AND PASL.OWNING_ORGANIZATION_ID = MP.ORGANIZATION_ID AND PASL.OWNING_ORGANIZATION_ID = HOU.ORGANIZATION_ID AND PASL.USING_ORGANIZATION_ID = MP1.ORGANIZATION_ID(+) AND PASL.USING_ORGANIZATION_ID = HOU1.ORGANIZATION_ID(+) AND PAA.USING_ORGANIZATION_ID = MP2.ORGANIZATION_ID(+) AND PAA.USING_ORGANIZATION_ID = HOU2.ORGANIZATION_ID(+) AND PAA.PLAN_BUCKET_PATTERN_ID = CBP.BUCKET_PATTERN_ID(+) AND PAA.SHIP_BUCKET_PATTERN_ID = CBP1.BUCKET_PATTERN_ID(+) AND PAA.SCHEDULER_ID = PPF.PERSON_ID(+) AND ((PPF.PERSON_ID IS NULL) OR (TRUNC(SYSDATE) BETWEEN TRUNC(PPF.EFFECTIVE_START_DATE) AND TRUNC(PPF.EFFECTIVE_END_DATE))) AND PAA.SHIP_SCHEDULE_TYPE = PLC5.LOOKUP_CODE(+) AND PLC5.LOOKUP_TYPE(+) = 'SHIP_SCHEDULE_SUBTYPE' AND PAA.PLAN_SCHEDULE_TYPE = PLC4.LOOKUP_CODE(+) AND PLC4.LOOKUP_TYPE(+) = 'PLAN_SCHEDULE_SUBTYPE' AND PAA.RELEASE_GENERATION_METHOD = PLC3.LOOKUP_CODE(+) AND PLC3.LOOKUP_TYPE(+) = 'DOC GENERATION METHOD' AND PLC2.LOOKUP_TYPE(+) = 'DOCUMENT_SOURCING_METHOD' AND PAA.DOCUMENT_SOURCING_METHOD = PLC2.LOOKUP_CODE (+) AND PASL.CATEGORY_ID = MC.CATEGORY_ID AND PASL.CATEGORY_ID IS NOT NULL AND hou.organization_id = hout.organization_id (+) AND DECODE(hout.organization_id, null, '1', hout.language) = DECODE(hout.organization_id, null, '1', USERENV('LANG')) AND hou1.organization_id = hout1.organization_id (+) AND DECODE(hout1.organization_id, null, '1', hout1.language) = DECODE(hout1.organization_id, null, '1', USERENV('LANG')) AND hou2.organization_id = hout2.organization_id (+) AND DECODE(hout2.organization_id, null, '1', hout2.language) = DECODE(hout2.organization_id, null, '1', USERENV('LANG'))

View Text - HTML Formatted

SELECT PASL.ROWID
, PASL.ASL_ID
, PASL.OWNING_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOUT.NAME
, PASL.USING_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOUT1.NAME
, PASL.VENDOR_BUSINESS_TYPE
, PLC.DISPLAYED_FIELD
, PASL.VENDOR_ID
, PASL.MANUFACTURER_ID
, DECODE(PASL.VENDOR_BUSINESS_TYPE
, 'MANUFACTURER'
, MM.MANUFACTURER_NAME
, PV.VENDOR_NAME)
, PASL.ASL_STATUS_ID
, PAST.STATUS
, PASL.ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, PASL.CATEGORY_ID
, NULL
, PASL.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PASL.PRIMARY_VENDOR_ITEM
, PASL.MANUFACTURER_ASL_ID
, MM1.MANUFACTURER_NAME
, PASL.REVIEW_BY_DATE
, PAA.DOCUMENT_SOURCING_METHOD
, PLC2.DISPLAYED_FIELD
, PAA.RELEASE_GENERATION_METHOD
, PLC3.DISPLAYED_FIELD
, PAA.PURCHASING_UNIT_OF_MEASURE
, PAA.ENABLE_PLAN_SCHEDULE_FLAG
, PAA.ENABLE_SHIP_SCHEDULE_FLAG
, PAA.PLAN_SCHEDULE_TYPE
, PLC4.DISPLAYED_FIELD
, PAA.SHIP_SCHEDULE_TYPE
, PLC5.DISPLAYED_FIELD
, PAA.PLAN_BUCKET_PATTERN_ID
, CBP.BUCKET_PATTERN_NAME
, PAA.SHIP_BUCKET_PATTERN_ID
, CBP1.BUCKET_PATTERN_NAME
, PAA.ENABLE_AUTOSCHEDULE_FLAG
, PAA.ENABLE_AUTHORIZATIONS_FLAG
, PAA.SCHEDULER_ID
, PPF.FULL_NAME
, DECODE(PASL.USING_ORGANIZATION_ID
, -1
, 'Y'
, 'N')
, DECODE(PAA.USING_ORGANIZATION_ID
, -1
, 'Y'
, 'N')
, PAA.USING_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOUT2.NAME
, PASL.COMMENTS
, PPF2.FULL_NAME
, MSI.BUYER_ID
, MSI.DESCRIPTION
, PASL.CREATION_DATE
, PASL.CREATED_BY
, PASL.LAST_UPDATE_LOGIN
, PASL.LAST_UPDATE_DATE
, PASL.LAST_UPDATED_BY
, PAA.PRICE_UPDATE_TOLERANCE
, PASL.DISABLE_FLAG
, PAA.PROCESSING_LEAD_TIME
, PAA.DELIVERY_CALENDAR
, PAA.MIN_ORDER_QTY
, PAA.FIXED_LOT_MULTIPLE
, PAA.COUNTRY_OF_ORIGIN_CODE
, PAA.ENABLE_VMI_FLAG
, NVL(PVS.ORG_ID
, PO_MOAC_UTILS_PVT.GET_CURRENT_ORG_ID) ORG_ID
, WF_CORE.SUBSTITUTESPECIALCHARS(DECODE(PASL.VENDOR_BUSINESS_TYPE
, 'MANUFACTURER'
, MM.MANUFACTURER_NAME
, PV.VENDOR_NAME)) VENDOR_NAME_MASK
, WF_CORE.SUBSTITUTESPECIALCHARS(MSI.DESCRIPTION) ITEM_DESCRIPTION_MASK
FROM PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, PO_LOOKUP_CODES PLC4
, PO_LOOKUP_CODES PLC5
, PER_PEOPLE_F PPF
, PER_PEOPLE_F PPF2
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_MANUFACTURERS MM
, MTL_MANUFACTURERS MM1
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, HR_ORG_UNITS_NO_JOIN HOU
, HR_ORG_UNITS_NO_JOIN HOU1
, HR_ORG_UNITS_NO_JOIN HOU2
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, HR_ALL_ORGANIZATION_UNITS_TL HOUT1
, HR_ALL_ORGANIZATION_UNITS_TL HOUT2
, PO_VENDOR_SITES PVS
, PO_VENDORS PV
, CHV_BUCKET_PATTERNS CBP1
, CHV_BUCKET_PATTERNS CBP
, PO_ASL_STATUSES PAST
, PO_APPROVED_SUPPLIER_LIST PASL1
, PO_ASL_ATTRIBUTES PAA
, PO_APPROVED_SUPPLIER_LIST PASL
WHERE PASL.ASL_ID = PAA.ASL_ID
AND PASL.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID
AND PASL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND ((PASL.VENDOR_SITE_ID IS NOT NULL
AND PVS.VENDOR_SITE_CODE IS NOT NULL) OR (PASL.VENDOR_SITE_ID IS NULL
AND PVS.VENDOR_SITE_CODE IS NULL))
AND PASL.VENDOR_BUSINESS_TYPE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'ASL_VENDOR_BUSINESS_TYPE'
AND PASL.ASL_STATUS_ID = PAST.STATUS_ID
AND PASL.VENDOR_ID = PV.VENDOR_ID(+)
AND PASL.MANUFACTURER_ID = MM.MANUFACTURER_ID(+)
AND PASL.MANUFACTURER_ASL_ID = PASL1.ASL_ID(+)
AND PASL1.MANUFACTURER_ID = MM1.MANUFACTURER_ID(+)
AND PASL.OWNING_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PASL.OWNING_ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PASL.USING_ORGANIZATION_ID = MP1.ORGANIZATION_ID(+)
AND PASL.USING_ORGANIZATION_ID = HOU1.ORGANIZATION_ID(+)
AND PAA.USING_ORGANIZATION_ID = MP2.ORGANIZATION_ID(+)
AND PAA.USING_ORGANIZATION_ID = HOU2.ORGANIZATION_ID(+)
AND PAA.PLAN_BUCKET_PATTERN_ID = CBP.BUCKET_PATTERN_ID(+)
AND PAA.SHIP_BUCKET_PATTERN_ID = CBP1.BUCKET_PATTERN_ID(+)
AND PAA.SCHEDULER_ID = PPF.PERSON_ID(+)
AND ((PPF.PERSON_ID IS NULL) OR (TRUNC(SYSDATE) BETWEEN TRUNC(PPF.EFFECTIVE_START_DATE)
AND TRUNC(PPF.EFFECTIVE_END_DATE)))
AND PAA.SHIP_SCHEDULE_TYPE = PLC5.LOOKUP_CODE(+)
AND PLC5.LOOKUP_TYPE(+) = 'SHIP_SCHEDULE_SUBTYPE'
AND PAA.PLAN_SCHEDULE_TYPE = PLC4.LOOKUP_CODE(+)
AND PLC4.LOOKUP_TYPE(+) = 'PLAN_SCHEDULE_SUBTYPE'
AND PAA.RELEASE_GENERATION_METHOD = PLC3.LOOKUP_CODE(+)
AND PLC3.LOOKUP_TYPE(+) = 'DOC GENERATION METHOD'
AND PLC2.LOOKUP_TYPE(+) = 'DOCUMENT_SOURCING_METHOD'
AND PAA.DOCUMENT_SOURCING_METHOD = PLC2.LOOKUP_CODE (+)
AND PASL.OWNING_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND PASL.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.BUYER_ID = PPF2.PERSON_ID (+)
AND ((PPF2.PERSON_ID IS NULL) OR (TRUNC(SYSDATE) BETWEEN TRUNC(PPF2.EFFECTIVE_START_DATE)
AND TRUNC(PPF2.EFFECTIVE_END_DATE)))
AND PASL.ITEM_ID IS NOT NULL
AND HOU.ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+)
AND DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, HOUT.LANGUAGE) = DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND HOU1.ORGANIZATION_ID = HOUT1.ORGANIZATION_ID (+)
AND DECODE(HOUT1.ORGANIZATION_ID
, NULL
, '1'
, HOUT1.LANGUAGE) = DECODE(HOUT1.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND HOU2.ORGANIZATION_ID = HOUT2.ORGANIZATION_ID (+)
AND DECODE(HOUT2.ORGANIZATION_ID
, NULL
, '1'
, HOUT2.LANGUAGE) = DECODE(HOUT2.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG')) UNION SELECT PASL.ROWID
, PASL.ASL_ID
, PASL.OWNING_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOUT.NAME
, PASL.USING_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOUT1.NAME
, PASL.VENDOR_BUSINESS_TYPE
, PLC.DISPLAYED_FIELD
, PASL.VENDOR_ID
, PASL.MANUFACTURER_ID
, DECODE(PASL.VENDOR_BUSINESS_TYPE
, 'MANUFACTURER'
, MM.MANUFACTURER_NAME
, PV.VENDOR_NAME)
, PASL.ASL_STATUS_ID
, PAST.STATUS
, PASL.ITEM_ID
, NULL
, PASL.CATEGORY_ID
, MC.CONCATENATED_SEGMENTS
, PASL.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PASL.PRIMARY_VENDOR_ITEM
, PASL.MANUFACTURER_ASL_ID
, MM1.MANUFACTURER_NAME
, PASL.REVIEW_BY_DATE
, PAA.DOCUMENT_SOURCING_METHOD
, PLC2.DISPLAYED_FIELD
, PAA.RELEASE_GENERATION_METHOD
, PLC3.DISPLAYED_FIELD
, PAA.PURCHASING_UNIT_OF_MEASURE
, PAA.ENABLE_PLAN_SCHEDULE_FLAG
, PAA.ENABLE_SHIP_SCHEDULE_FLAG
, PAA.PLAN_SCHEDULE_TYPE
, PLC4.DISPLAYED_FIELD
, PAA.SHIP_SCHEDULE_TYPE
, PLC5.DISPLAYED_FIELD
, PAA.PLAN_BUCKET_PATTERN_ID
, CBP.BUCKET_PATTERN_NAME
, PAA.SHIP_BUCKET_PATTERN_ID
, CBP1.BUCKET_PATTERN_NAME
, PAA.ENABLE_AUTOSCHEDULE_FLAG
, PAA.ENABLE_AUTHORIZATIONS_FLAG
, PAA.SCHEDULER_ID
, PPF.FULL_NAME
, DECODE(PASL.USING_ORGANIZATION_ID
, -1
, 'Y'
, 'N')
, DECODE(PAA.USING_ORGANIZATION_ID
, -1
, 'Y'
, 'N')
, PAA.USING_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOUT2.NAME
, PASL.COMMENTS
, NULL
, TO_NUMBER(NULL)
, NULL
, PASL.CREATION_DATE
, PASL.CREATED_BY
, PASL.LAST_UPDATE_LOGIN
, PASL.LAST_UPDATE_DATE
, PASL.LAST_UPDATED_BY
, PAA.PRICE_UPDATE_TOLERANCE
, PASL.DISABLE_FLAG
, PAA.PROCESSING_LEAD_TIME
, PAA.DELIVERY_CALENDAR
, PAA.MIN_ORDER_QTY
, PAA.FIXED_LOT_MULTIPLE
, PAA.COUNTRY_OF_ORIGIN_CODE
, PAA.ENABLE_VMI_FLAG
, NVL(PVS.ORG_ID
, PO_MOAC_UTILS_PVT.GET_CURRENT_ORG_ID) ORG_ID
, WF_CORE.SUBSTITUTESPECIALCHARS(DECODE(PASL.VENDOR_BUSINESS_TYPE
, 'MANUFACTURER'
, MM.MANUFACTURER_NAME
, PV.VENDOR_NAME)) VENDOR_NAME_MASK
, NULL ITEM_DESCRIPTION_MASK
FROM PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, PO_LOOKUP_CODES PLC4
, PO_LOOKUP_CODES PLC5
, PER_PEOPLE_F PPF
, MTL_CATEGORIES_KFV MC
, MTL_MANUFACTURERS MM
, MTL_MANUFACTURERS MM1
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, HR_ORG_UNITS_NO_JOIN HOU
, HR_ORG_UNITS_NO_JOIN HOU1
, HR_ORG_UNITS_NO_JOIN HOU2
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, HR_ALL_ORGANIZATION_UNITS_TL HOUT1
, HR_ALL_ORGANIZATION_UNITS_TL HOUT2
, PO_VENDOR_SITES PVS
, PO_VENDORS PV
, CHV_BUCKET_PATTERNS CBP1
, CHV_BUCKET_PATTERNS CBP
, PO_ASL_STATUSES PAST
, PO_APPROVED_SUPPLIER_LIST PASL1
, PO_ASL_ATTRIBUTES PAA
, PO_APPROVED_SUPPLIER_LIST PASL
WHERE PASL.ASL_ID = PAA.ASL_ID
AND PASL.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID
AND PASL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND ((PASL.VENDOR_SITE_ID IS NOT NULL
AND PVS.VENDOR_SITE_CODE IS NOT NULL) OR (PASL.VENDOR_SITE_ID IS NULL
AND PVS.VENDOR_SITE_CODE IS NULL))
AND PASL.VENDOR_BUSINESS_TYPE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'ASL_VENDOR_BUSINESS_TYPE'
AND PASL.ASL_STATUS_ID = PAST.STATUS_ID
AND PASL.VENDOR_ID = PV.VENDOR_ID(+)
AND PASL.MANUFACTURER_ID = MM.MANUFACTURER_ID(+)
AND PASL.MANUFACTURER_ASL_ID = PASL1.ASL_ID(+)
AND PASL1.MANUFACTURER_ID = MM1.MANUFACTURER_ID(+)
AND PASL.OWNING_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PASL.OWNING_ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PASL.USING_ORGANIZATION_ID = MP1.ORGANIZATION_ID(+)
AND PASL.USING_ORGANIZATION_ID = HOU1.ORGANIZATION_ID(+)
AND PAA.USING_ORGANIZATION_ID = MP2.ORGANIZATION_ID(+)
AND PAA.USING_ORGANIZATION_ID = HOU2.ORGANIZATION_ID(+)
AND PAA.PLAN_BUCKET_PATTERN_ID = CBP.BUCKET_PATTERN_ID(+)
AND PAA.SHIP_BUCKET_PATTERN_ID = CBP1.BUCKET_PATTERN_ID(+)
AND PAA.SCHEDULER_ID = PPF.PERSON_ID(+)
AND ((PPF.PERSON_ID IS NULL) OR (TRUNC(SYSDATE) BETWEEN TRUNC(PPF.EFFECTIVE_START_DATE)
AND TRUNC(PPF.EFFECTIVE_END_DATE)))
AND PAA.SHIP_SCHEDULE_TYPE = PLC5.LOOKUP_CODE(+)
AND PLC5.LOOKUP_TYPE(+) = 'SHIP_SCHEDULE_SUBTYPE'
AND PAA.PLAN_SCHEDULE_TYPE = PLC4.LOOKUP_CODE(+)
AND PLC4.LOOKUP_TYPE(+) = 'PLAN_SCHEDULE_SUBTYPE'
AND PAA.RELEASE_GENERATION_METHOD = PLC3.LOOKUP_CODE(+)
AND PLC3.LOOKUP_TYPE(+) = 'DOC GENERATION METHOD'
AND PLC2.LOOKUP_TYPE(+) = 'DOCUMENT_SOURCING_METHOD'
AND PAA.DOCUMENT_SOURCING_METHOD = PLC2.LOOKUP_CODE (+)
AND PASL.CATEGORY_ID = MC.CATEGORY_ID
AND PASL.CATEGORY_ID IS NOT NULL
AND HOU.ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+)
AND DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, HOUT.LANGUAGE) = DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND HOU1.ORGANIZATION_ID = HOUT1.ORGANIZATION_ID (+)
AND DECODE(HOUT1.ORGANIZATION_ID
, NULL
, '1'
, HOUT1.LANGUAGE) = DECODE(HOUT1.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND HOU2.ORGANIZATION_ID = HOUT2.ORGANIZATION_ID (+)
AND DECODE(HOUT2.ORGANIZATION_ID
, NULL
, '1'
, HOUT2.LANGUAGE) = DECODE(HOUT2.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))