VIEW: APPS.POFV_APPROVED_SUPPLIER_LISTS

Source

View Text - Preformatted

SELECT AL.ASL_ID, AL.USING_ORGANIZATION_ID, UC.ORGANIZATION_CODE, UO.NAME, ST.STATUS, ST.STATUS_DESCRIPTION, AL.COMMENTS, AL.REVIEW_BY_DATE, '_LA:al.VENDOR_BUSINESS_TYPE:PO_LOOKUP_CODES:ASL_VENDOR_BUSINESS_TYPE:DISPLAYED_FIELD', AL.PRIMARY_VENDOR_ITEM, '_LA:at.DOCUMENT_SOURCING_METHOD:PO_LOOKUP_CODES:DOCUMENT_SOURCING_METHOD:DISPLAYED_FIELD', '_LA:at.RELEASE_GENERATION_METHOD:PO_LOOKUP_CODES:DOC GENERATION METHOD:DISPLAYED_FIELD', '_LA:at.ENABLE_PLAN_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:at.ENABLE_SHIP_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:at.ENABLE_AUTOSCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:at.PLAN_SCHEDULE_TYPE:PO_LOOKUP_CODES:PLAN_SCHEDULE_SUBTYPE:DISPLAYED_FIELD', '_LA:at.SHIP_SCHEDULE_TYPE:PO_LOOKUP_CODES:SHIP_SCHEDULE_SUBTYPE:DISPLAYED_FIELD', '_DF:PO:PO_APPROVED_SUPPLIER_LIST:al', AL.OWNING_ORGANIZATION_ID, OC.ORGANIZATION_CODE, OO.NAME, AL.MANUFACTURER_ID, MU.MANUFACTURER_NAME, AL.VENDOR_ID, VE.VENDOR_NAME, AL.VENDOR_SITE_ID, VS.VENDOR_SITE_CODE, VS.ORG_ID, OP.NAME, AL.ITEM_ID, '_KF:INV:MSTK:si', SI.DESCRIPTION, AL.CATEGORY_ID, '_KF:INV:MCAT:ct', AL.MANUFACTURER_ASL_ID, AT.PURCHASING_UNIT_OF_MEASURE, UM.UOM_CLASS, UM.DESCRIPTION, AT.SCHEDULER_ID, SC.EMPLOYEE_NUMBER, SC.FULL_NAME, AL.CREATION_DATE, AL.CREATED_BY, AL.LAST_UPDATE_DATE, AL.LAST_UPDATED_BY, AT.PRICE_UPDATE_TOLERANCE, AT.PROCESSING_LEAD_TIME, AT.MIN_ORDER_QTY, AT.DELIVERY_CALENDAR, AT.COUNTRY_OF_ORIGIN_CODE, '_LA:AT.ENABLE_VMI_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', AT.VMI_MIN_QTY, AT.VMI_MAX_QTY, '_LA:AT.ENABLE_VMI_AUTO_REPLENISH_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', AT.VMI_REPLENISHMENT_APPROVAL, '_LA:AT.CONSIGNED_FROM_SUPPLIER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', AT.LAST_BILLING_DATE, AT.CONSIGNED_BILLING_CYCLE FROM PO_APPROVED_SUPPLIER_LIST AL, PO_ASL_ATTRIBUTES AT, HR_ALL_ORGANIZATION_UNITS UO, MTL_PARAMETERS UC, HR_ALL_ORGANIZATION_UNITS OO, MTL_PARAMETERS OC, PO_ASL_STATUSES ST, MTL_MANUFACTURERS MU, PO_VENDORS VE, PO_VENDOR_SITES_ALL VS, HR_ALL_ORGANIZATION_UNITS OP, MTL_SYSTEM_ITEMS SI, MTL_CATEGORIES CT, PO_APPROVED_SUPPLIER_LIST ML, MTL_UNITS_OF_MEASURE UM, PER_PEOPLE_F SC WHERE AL.ASL_ID = AT.ASL_ID AND AL.USING_ORGANIZATION_ID = AT.USING_ORGANIZATION_ID AND AL.USING_ORGANIZATION_ID = UO.ORGANIZATION_ID(+) AND AL.USING_ORGANIZATION_ID = UC.ORGANIZATION_ID(+) AND AL.OWNING_ORGANIZATION_ID = OO.ORGANIZATION_ID(+) AND AL.OWNING_ORGANIZATION_ID = OC.ORGANIZATION_ID(+) AND AL.ASL_STATUS_ID = ST.STATUS_ID AND AL.MANUFACTURER_ID = MU.MANUFACTURER_ID(+) AND AL.VENDOR_ID = VE.VENDOR_ID AND AL.VENDOR_SITE_ID = VS.VENDOR_SITE_ID AND VS.ORG_ID = OP.ORGANIZATION_ID AND AL.ITEM_ID = SI.INVENTORY_ITEM_ID AND AL.OWNING_ORGANIZATION_ID = SI.ORGANIZATION_ID AND AL.CATEGORY_ID = CT.CATEGORY_ID(+) AND AL.MANUFACTURER_ASL_ID = ML.ASL_ID(+) AND AT.PURCHASING_UNIT_OF_MEASURE = UM.UNIT_OF_MEASURE(+) AND AT.SCHEDULER_ID = SC.PERSON_ID(+) AND SC.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND SC.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) UNION SELECT AL.ASL_ID, AL.USING_ORGANIZATION_ID, UC.ORGANIZATION_CODE, UO.NAME, ST.STATUS, ST.STATUS_DESCRIPTION, AL.COMMENTS, AL.REVIEW_BY_DATE, '_LA:al.VENDOR_BUSINESS_TYPE:PO_LOOKUP_CODES:ASL_VENDOR_BUSINESS_TYPE:DISPLAYED_FIELD', AL.PRIMARY_VENDOR_ITEM, '_LA:at.DOCUMENT_SOURCING_METHOD:PO_LOOKUP_CODES:DOCUMENT_SOURCING_METHOD:DISPLAYED_FIELD', '_LA:at.RELEASE_GENERATION_METHOD:PO_LOOKUP_CODES:DOC GENERATION METHOD:DISPLAYED_FIELD', '_LA:at.ENABLE_PLAN_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:at.ENABLE_SHIP_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:at.ENABLE_AUTOSCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:at.PLAN_SCHEDULE_TYPE:PO_LOOKUP_CODES:PLAN_SCHEDULE_SUBTYPE:DISPLAYED_FIELD', '_LA:at.SHIP_SCHEDULE_TYPE:PO_LOOKUP_CODES:SHIP_SCHEDULE_SUBTYPE:DISPLAYED_FIELD', '_DF:PO:PO_APPROVED_SUPPLIER_LIST:al', AL.OWNING_ORGANIZATION_ID, OC.ORGANIZATION_CODE, OO.NAME, AL.MANUFACTURER_ID, MU.MANUFACTURER_NAME, AL.VENDOR_ID, VE.VENDOR_NAME, AL.VENDOR_SITE_ID, NULL, TO_NUMBER(NULL), NULL, AL.ITEM_ID, '_KF:INV:MSTK:si', SI.DESCRIPTION, AL.CATEGORY_ID, '_KF:INV:MCAT:ct', AL.MANUFACTURER_ASL_ID, AT.PURCHASING_UNIT_OF_MEASURE, UM.UOM_CLASS, UM.DESCRIPTION, AT.SCHEDULER_ID, SC.EMPLOYEE_NUMBER, SC.FULL_NAME, AL.CREATION_DATE, AL.CREATED_BY, AL.LAST_UPDATE_DATE, AL.LAST_UPDATED_BY, AT.PRICE_UPDATE_TOLERANCE, AT.PROCESSING_LEAD_TIME, AT.MIN_ORDER_QTY, AT.DELIVERY_CALENDAR, AT.COUNTRY_OF_ORIGIN_CODE, '_LA:AT.ENABLE_VMI_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', AT.VMI_MIN_QTY, AT.VMI_MAX_QTY, '_LA:AT.ENABLE_VMI_AUTO_REPLENISH_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', AT.VMI_REPLENISHMENT_APPROVAL, '_LA:AT.CONSIGNED_FROM_SUPPLIER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', AT.LAST_BILLING_DATE, AT.CONSIGNED_BILLING_CYCLE FROM PO_APPROVED_SUPPLIER_LIST AL, PO_ASL_ATTRIBUTES AT, HR_ALL_ORGANIZATION_UNITS UO, MTL_PARAMETERS UC, HR_ALL_ORGANIZATION_UNITS OO, MTL_PARAMETERS OC, PO_ASL_STATUSES ST, MTL_MANUFACTURERS MU, PO_VENDORS VE, MTL_SYSTEM_ITEMS SI, MTL_CATEGORIES CT, PO_APPROVED_SUPPLIER_LIST ML, MTL_UNITS_OF_MEASURE UM, PER_PEOPLE_F SC WHERE AL.ASL_ID = AT.ASL_ID AND AL.USING_ORGANIZATION_ID = AT.USING_ORGANIZATION_ID AND AL.USING_ORGANIZATION_ID = UO.ORGANIZATION_ID(+) AND AL.USING_ORGANIZATION_ID = UC.ORGANIZATION_ID(+) AND AL.OWNING_ORGANIZATION_ID = OO.ORGANIZATION_ID(+) AND AL.OWNING_ORGANIZATION_ID = OC.ORGANIZATION_ID(+) AND AL.ASL_STATUS_ID = ST.STATUS_ID AND AL.MANUFACTURER_ID = MU.MANUFACTURER_ID(+) AND AL.VENDOR_ID = VE.VENDOR_ID AND AL.VENDOR_SITE_ID IS NULL AND AL.ITEM_ID = SI.INVENTORY_ITEM_ID(+) AND AL.OWNING_ORGANIZATION_ID = SI.ORGANIZATION_ID(+) AND AL.CATEGORY_ID = CT.CATEGORY_ID(+) AND AL.MANUFACTURER_ASL_ID = ML.ASL_ID(+) AND AT.PURCHASING_UNIT_OF_MEASURE = UM.UNIT_OF_MEASURE(+) AND AT.SCHEDULER_ID = SC.PERSON_ID(+) AND SC.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND SC.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) WITH READ ONLY

View Text - HTML Formatted

SELECT AL.ASL_ID
, AL.USING_ORGANIZATION_ID
, UC.ORGANIZATION_CODE
, UO.NAME
, ST.STATUS
, ST.STATUS_DESCRIPTION
, AL.COMMENTS
, AL.REVIEW_BY_DATE
, '_LA:AL.VENDOR_BUSINESS_TYPE:PO_LOOKUP_CODES:ASL_VENDOR_BUSINESS_TYPE:DISPLAYED_FIELD'
, AL.PRIMARY_VENDOR_ITEM
, '_LA:AT.DOCUMENT_SOURCING_METHOD:PO_LOOKUP_CODES:DOCUMENT_SOURCING_METHOD:DISPLAYED_FIELD'
, '_LA:AT.RELEASE_GENERATION_METHOD:PO_LOOKUP_CODES:DOC GENERATION METHOD:DISPLAYED_FIELD'
, '_LA:AT.ENABLE_PLAN_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:AT.ENABLE_SHIP_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:AT.ENABLE_AUTOSCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:AT.PLAN_SCHEDULE_TYPE:PO_LOOKUP_CODES:PLAN_SCHEDULE_SUBTYPE:DISPLAYED_FIELD'
, '_LA:AT.SHIP_SCHEDULE_TYPE:PO_LOOKUP_CODES:SHIP_SCHEDULE_SUBTYPE:DISPLAYED_FIELD'
, '_DF:PO:PO_APPROVED_SUPPLIER_LIST:AL'
, AL.OWNING_ORGANIZATION_ID
, OC.ORGANIZATION_CODE
, OO.NAME
, AL.MANUFACTURER_ID
, MU.MANUFACTURER_NAME
, AL.VENDOR_ID
, VE.VENDOR_NAME
, AL.VENDOR_SITE_ID
, VS.VENDOR_SITE_CODE
, VS.ORG_ID
, OP.NAME
, AL.ITEM_ID
, '_KF:INV:MSTK:SI'
, SI.DESCRIPTION
, AL.CATEGORY_ID
, '_KF:INV:MCAT:CT'
, AL.MANUFACTURER_ASL_ID
, AT.PURCHASING_UNIT_OF_MEASURE
, UM.UOM_CLASS
, UM.DESCRIPTION
, AT.SCHEDULER_ID
, SC.EMPLOYEE_NUMBER
, SC.FULL_NAME
, AL.CREATION_DATE
, AL.CREATED_BY
, AL.LAST_UPDATE_DATE
, AL.LAST_UPDATED_BY
, AT.PRICE_UPDATE_TOLERANCE
, AT.PROCESSING_LEAD_TIME
, AT.MIN_ORDER_QTY
, AT.DELIVERY_CALENDAR
, AT.COUNTRY_OF_ORIGIN_CODE
, '_LA:AT.ENABLE_VMI_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, AT.VMI_MIN_QTY
, AT.VMI_MAX_QTY
, '_LA:AT.ENABLE_VMI_AUTO_REPLENISH_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, AT.VMI_REPLENISHMENT_APPROVAL
, '_LA:AT.CONSIGNED_FROM_SUPPLIER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, AT.LAST_BILLING_DATE
, AT.CONSIGNED_BILLING_CYCLE
FROM PO_APPROVED_SUPPLIER_LIST AL
, PO_ASL_ATTRIBUTES AT
, HR_ALL_ORGANIZATION_UNITS UO
, MTL_PARAMETERS UC
, HR_ALL_ORGANIZATION_UNITS OO
, MTL_PARAMETERS OC
, PO_ASL_STATUSES ST
, MTL_MANUFACTURERS MU
, PO_VENDORS VE
, PO_VENDOR_SITES_ALL VS
, HR_ALL_ORGANIZATION_UNITS OP
, MTL_SYSTEM_ITEMS SI
, MTL_CATEGORIES CT
, PO_APPROVED_SUPPLIER_LIST ML
, MTL_UNITS_OF_MEASURE UM
, PER_PEOPLE_F SC
WHERE AL.ASL_ID = AT.ASL_ID
AND AL.USING_ORGANIZATION_ID = AT.USING_ORGANIZATION_ID
AND AL.USING_ORGANIZATION_ID = UO.ORGANIZATION_ID(+)
AND AL.USING_ORGANIZATION_ID = UC.ORGANIZATION_ID(+)
AND AL.OWNING_ORGANIZATION_ID = OO.ORGANIZATION_ID(+)
AND AL.OWNING_ORGANIZATION_ID = OC.ORGANIZATION_ID(+)
AND AL.ASL_STATUS_ID = ST.STATUS_ID
AND AL.MANUFACTURER_ID = MU.MANUFACTURER_ID(+)
AND AL.VENDOR_ID = VE.VENDOR_ID
AND AL.VENDOR_SITE_ID = VS.VENDOR_SITE_ID
AND VS.ORG_ID = OP.ORGANIZATION_ID
AND AL.ITEM_ID = SI.INVENTORY_ITEM_ID
AND AL.OWNING_ORGANIZATION_ID = SI.ORGANIZATION_ID
AND AL.CATEGORY_ID = CT.CATEGORY_ID(+)
AND AL.MANUFACTURER_ASL_ID = ML.ASL_ID(+)
AND AT.PURCHASING_UNIT_OF_MEASURE = UM.UNIT_OF_MEASURE(+)
AND AT.SCHEDULER_ID = SC.PERSON_ID(+)
AND SC.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND SC.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) UNION SELECT AL.ASL_ID
, AL.USING_ORGANIZATION_ID
, UC.ORGANIZATION_CODE
, UO.NAME
, ST.STATUS
, ST.STATUS_DESCRIPTION
, AL.COMMENTS
, AL.REVIEW_BY_DATE
, '_LA:AL.VENDOR_BUSINESS_TYPE:PO_LOOKUP_CODES:ASL_VENDOR_BUSINESS_TYPE:DISPLAYED_FIELD'
, AL.PRIMARY_VENDOR_ITEM
, '_LA:AT.DOCUMENT_SOURCING_METHOD:PO_LOOKUP_CODES:DOCUMENT_SOURCING_METHOD:DISPLAYED_FIELD'
, '_LA:AT.RELEASE_GENERATION_METHOD:PO_LOOKUP_CODES:DOC GENERATION METHOD:DISPLAYED_FIELD'
, '_LA:AT.ENABLE_PLAN_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:AT.ENABLE_SHIP_SCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:AT.ENABLE_AUTOSCHEDULE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:AT.PLAN_SCHEDULE_TYPE:PO_LOOKUP_CODES:PLAN_SCHEDULE_SUBTYPE:DISPLAYED_FIELD'
, '_LA:AT.SHIP_SCHEDULE_TYPE:PO_LOOKUP_CODES:SHIP_SCHEDULE_SUBTYPE:DISPLAYED_FIELD'
, '_DF:PO:PO_APPROVED_SUPPLIER_LIST:AL'
, AL.OWNING_ORGANIZATION_ID
, OC.ORGANIZATION_CODE
, OO.NAME
, AL.MANUFACTURER_ID
, MU.MANUFACTURER_NAME
, AL.VENDOR_ID
, VE.VENDOR_NAME
, AL.VENDOR_SITE_ID
, NULL
, TO_NUMBER(NULL)
, NULL
, AL.ITEM_ID
, '_KF:INV:MSTK:SI'
, SI.DESCRIPTION
, AL.CATEGORY_ID
, '_KF:INV:MCAT:CT'
, AL.MANUFACTURER_ASL_ID
, AT.PURCHASING_UNIT_OF_MEASURE
, UM.UOM_CLASS
, UM.DESCRIPTION
, AT.SCHEDULER_ID
, SC.EMPLOYEE_NUMBER
, SC.FULL_NAME
, AL.CREATION_DATE
, AL.CREATED_BY
, AL.LAST_UPDATE_DATE
, AL.LAST_UPDATED_BY
, AT.PRICE_UPDATE_TOLERANCE
, AT.PROCESSING_LEAD_TIME
, AT.MIN_ORDER_QTY
, AT.DELIVERY_CALENDAR
, AT.COUNTRY_OF_ORIGIN_CODE
, '_LA:AT.ENABLE_VMI_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, AT.VMI_MIN_QTY
, AT.VMI_MAX_QTY
, '_LA:AT.ENABLE_VMI_AUTO_REPLENISH_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, AT.VMI_REPLENISHMENT_APPROVAL
, '_LA:AT.CONSIGNED_FROM_SUPPLIER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, AT.LAST_BILLING_DATE
, AT.CONSIGNED_BILLING_CYCLE
FROM PO_APPROVED_SUPPLIER_LIST AL
, PO_ASL_ATTRIBUTES AT
, HR_ALL_ORGANIZATION_UNITS UO
, MTL_PARAMETERS UC
, HR_ALL_ORGANIZATION_UNITS OO
, MTL_PARAMETERS OC
, PO_ASL_STATUSES ST
, MTL_MANUFACTURERS MU
, PO_VENDORS VE
, MTL_SYSTEM_ITEMS SI
, MTL_CATEGORIES CT
, PO_APPROVED_SUPPLIER_LIST ML
, MTL_UNITS_OF_MEASURE UM
, PER_PEOPLE_F SC
WHERE AL.ASL_ID = AT.ASL_ID
AND AL.USING_ORGANIZATION_ID = AT.USING_ORGANIZATION_ID
AND AL.USING_ORGANIZATION_ID = UO.ORGANIZATION_ID(+)
AND AL.USING_ORGANIZATION_ID = UC.ORGANIZATION_ID(+)
AND AL.OWNING_ORGANIZATION_ID = OO.ORGANIZATION_ID(+)
AND AL.OWNING_ORGANIZATION_ID = OC.ORGANIZATION_ID(+)
AND AL.ASL_STATUS_ID = ST.STATUS_ID
AND AL.MANUFACTURER_ID = MU.MANUFACTURER_ID(+)
AND AL.VENDOR_ID = VE.VENDOR_ID
AND AL.VENDOR_SITE_ID IS NULL
AND AL.ITEM_ID = SI.INVENTORY_ITEM_ID(+)
AND AL.OWNING_ORGANIZATION_ID = SI.ORGANIZATION_ID(+)
AND AL.CATEGORY_ID = CT.CATEGORY_ID(+)
AND AL.MANUFACTURER_ASL_ID = ML.ASL_ID(+)
AND AT.PURCHASING_UNIT_OF_MEASURE = UM.UNIT_OF_MEASURE(+)
AND AT.SCHEDULER_ID = SC.PERSON_ID(+)
AND SC.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND SC.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) WITH READ ONLY