VIEW: APPS.PO_AUTOSOURCE_DOCUMENTS_V

Source

View Text - Preformatted

SELECT PASL.ASL_ID , PASL.VENDOR_ID , PV.VENDOR_NAME , PAD.SEQUENCE_NUM , PAD.DOCUMENT_TYPE_CODE , PAD.DOCUMENT_HEADER_ID , PH.SEGMENT1 , PAD.DOCUMENT_LINE_ID , PL.LINE_NUM , PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM , PAA.RELEASE_GENERATION_METHOD , PLL.LINE_LOCATION_ID , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_ID, PVSA2.VENDOR_SITE_ID) , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_CODE, PVSA2.VENDOR_SITE_CODE) , PH.VENDOR_CONTACT_ID , DECODE(PH.VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME||', '|| PVC.FIRST_NAME) , DECODE(PAD.DOCUMENT_TYPE_CODE, 'QUOTATION', PH.STATUS_LOOKUP_CODE, 'BLANKET', PH.AUTHORIZATION_STATUS) , PH.AGENT_ID , PPF.FULL_NAME , PH.CURRENCY_CODE , PH.TERMS_ID , PH.SHIP_VIA_LOOKUP_CODE , PH.FREIGHT_TERMS_LOOKUP_CODE , PH.FOB_LOOKUP_CODE , PLL.TERMS_ID , PLL.SHIP_VIA_LOOKUP_CODE , PLL.FREIGHT_TERMS_LOOKUP_CODE , PLL.FOB_LOOKUP_CODE , PH.QUOTE_VENDOR_QUOTE_NUMBER , PH.FROM_HEADER_ID , PH1.SEGMENT1 , PH.START_DATE , PH.END_DATE , PL.ITEM_REVISION , PL.VENDOR_PRODUCT_NUM , PL.LINE_TYPE_ID , PLT.LINE_TYPE , PL.UNIT_MEAS_LOOKUP_CODE , PL.QUANTITY , PL.UNIT_PRICE , PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED, 0) , PLL.PRICE_OVERRIDE , PLL.UNIT_MEAS_LOOKUP_CODE , PLL.SHIP_TO_LOCATION_ID , PLL.SHIP_TO_ORGANIZATION_ID , MP.ORGANIZATION_CODE , HOUT.NAME , PLL.START_DATE , PLL.END_DATE , GSB.CURRENCY_CODE , PH.APPROVAL_REQUIRED_FLAG , PLT.OUTSIDE_OPERATION_FLAG , PL.ITEM_ID , PL.CATEGORY_ID , PLT.ORDER_TYPE_LOOKUP_CODE , 'Y' , PL.SECONDARY_UNIT_OF_MEASURE , PL.SECONDARY_QUANTITY , PLL.SECONDARY_UNIT_OF_MEASURE , PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED, 0) , PL.PREFERRED_GRADE , PH.GLOBAL_AGREEMENT_FLAG , PH.ORG_ID , PGOA.ORGANIZATION_ID , PGOA.PURCHASING_ORG_ID , PL.NEGOTIATED_BY_PREPARER_FLAG , PLT.PURCHASE_BASIS , PLT.MATCHING_BASIS , FSP.ORG_ID FROM PO_VENDORS PV , PO_VENDOR_SITES_ALL PVSA , PO_VENDOR_SITES_ALL PVSA2 , AP_SUPPLIER_CONTACTS PVC , FINANCIALS_SYSTEM_PARAMETERS FSP , GL_SETS_OF_BOOKS GSB , PO_HEADERS_ALL PH , PO_HEADERS_ALL PH1 , PO_ASL_DOCUMENTS PAD , PO_LINES_ALL PL , PER_ALL_PEOPLE_F PPF , HR_ALL_ORGANIZATION_UNITS_TL HOUT , MTL_PARAMETERS MP , PO_LINE_LOCATIONS_ALL PLL , PO_LINE_TYPES PLT , PO_APPROVED_SUPPLIER_LIS_VAL_V PASL , PO_ASL_ATTRIBUTES PAA , PO_GA_ORG_ASSIGNMENTS PGOA WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID) AND PAA.ASL_ID = PAD.ASL_ID AND PAA.USING_ORGANIZATION_ID = PAD.USING_ORGANIZATION_ID AND PASL.ASL_ID = PAA.ASL_ID AND PASL.VENDOR_ID = PV.VENDOR_ID AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID AND PAD.DOCUMENT_LINE_ID = PLL.PO_LINE_ID AND NVL(PLL.SHIPMENT_TYPE, 'PRICE BREAK') IN ('PRICE BREAK', 'QUOTATION') AND ((PH.AUTHORIZATION_STATUS = 'APPROVED' AND PH.APPROVED_DATE IS NOT NULL AND NVL(PH.CANCEL_FLAG, 'N') != 'Y' AND NVL(PH.FROZEN_FLAG, 'N') != 'Y' AND NVL(PH.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CANCEL_FLAG, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A')) AND (SYSDATE BETWEEN NVL(PH.START_DATE, SYSDATE - 1) AND NVL(PH.END_DATE, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE), TRUNC(SYSDATE))) AND (SYSDATE BETWEEN NVL(PLL.START_DATE, SYSDATE - 1) AND NVL(PLL.END_DATE, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE), TRUNC(SYSDATE))) AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+) AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+) AND PGOA.ENABLED_FLAG (+) = 'Y' AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+) AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE, SYSDATE+1)) ) ) AND PH.AGENT_ID = PPF.PERSON_ID AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(PPF.EFFECTIVE_END_DATE, SYSDATE + 1) AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+) AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND PLL.SHIP_TO_ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+) AND HOUT.LANGUAGE (+) = USERENV('LANG') AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+) AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID UNION ALL SELECT PASL.ASL_ID , PASL.VENDOR_ID , PV.VENDOR_NAME , PAD.SEQUENCE_NUM , PAD.DOCUMENT_TYPE_CODE , PAD.DOCUMENT_HEADER_ID , PH.SEGMENT1 , PAD.DOCUMENT_LINE_ID , PL.LINE_NUM , NULL , PAA.RELEASE_GENERATION_METHOD , TO_NUMBER(NULL) , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_ID, PVSA2.VENDOR_SITE_ID) , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_CODE, PVSA2.VENDOR_SITE_CODE) , PH.VENDOR_CONTACT_ID , DECODE(PH.VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME||', '||PVC.FIRST_NAME) , DECODE(PAD.DOCUMENT_TYPE_CODE, 'QUOTATION', PH.STATUS_LOOKUP_CODE, 'BLANKET', PH.AUTHORIZATION_STATUS) , PH.AGENT_ID , PPF.FULL_NAME , PH.CURRENCY_CODE , PH.TERMS_ID , PH.SHIP_VIA_LOOKUP_CODE , PH.FREIGHT_TERMS_LOOKUP_CODE , PH.FOB_LOOKUP_CODE , TO_NUMBER(NULL) , NULL , NULL , NULL , PH.QUOTE_VENDOR_QUOTE_NUMBER , PH.FROM_HEADER_ID , PH1.SEGMENT1 , PH.START_DATE , PH.END_DATE , PL.ITEM_REVISION , PL.VENDOR_PRODUCT_NUM , PL.LINE_TYPE_ID , PLT.LINE_TYPE , PL.UNIT_MEAS_LOOKUP_CODE , PL.QUANTITY , PL.UNIT_PRICE , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL , NULL , TO_DATE(NULL) , TO_DATE(NULL) , GSB.CURRENCY_CODE , PH.APPROVAL_REQUIRED_FLAG , PLT.OUTSIDE_OPERATION_FLAG , PL.ITEM_ID , PL.CATEGORY_ID , PLT.ORDER_TYPE_LOOKUP_CODE , 'Y' , PL.SECONDARY_UNIT_OF_MEASURE , PL.SECONDARY_QUANTITY , NULL , TO_NUMBER(NULL) , PL.PREFERRED_GRADE , PH.GLOBAL_AGREEMENT_FLAG , PH.ORG_ID , PGOA.ORGANIZATION_ID , PGOA.PURCHASING_ORG_ID , PL.NEGOTIATED_BY_PREPARER_FLAG , PLT.PURCHASE_BASIS , PLT.MATCHING_BASIS , FSP.ORG_ID FROM PO_VENDORS PV , PO_VENDOR_SITES_ALL PVSA , PO_VENDOR_SITES_ALL PVSA2 , AP_SUPPLIER_CONTACTS PVC , FINANCIALS_SYSTEM_PARAMETERS FSP , GL_SETS_OF_BOOKS GSB , PO_HEADERS_ALL PH , PO_HEADERS_ALL PH1 , PO_ASL_DOCUMENTS PAD , PO_APPROVED_SUPPLIER_LIS_VAL_V PASL , PO_ASL_ATTRIBUTES PAA , PO_LINES_ALL PL , PER_ALL_PEOPLE_F PPF , PO_LINE_TYPES PLT , PO_GA_ORG_ASSIGNMENTS PGOA WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID) AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID AND PAA.ASL_ID = PASL.ASL_ID AND PAD.ASL_ID = PAA.ASL_ID AND PAD.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID AND NOT EXISTS (SELECT 'price break' FROM PO_LINE_LOCATIONS_ALL PLL WHERE PLL.SHIPMENT_TYPE IN ('PRICE BREAK', 'QUOTATION') AND PL.PO_LINE_ID = PLL.PO_LINE_ID AND (SYSDATE BETWEEN NVL(PLL.START_DATE, SYSDATE - 1) AND NVL(PLL.END_DATE, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE), TRUNC(SYSDATE))) ) AND ((PH.AUTHORIZATION_STATUS = 'APPROVED' AND PH.APPROVED_DATE IS NOT NULL AND NVL(PH.CANCEL_FLAG, 'N') != 'Y' AND NVL(PH.FROZEN_FLAG, 'N') != 'Y' AND NVL(PH.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CANCEL_FLAG, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A')) AND PASL.VENDOR_ID = PV.VENDOR_ID AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+) AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+) AND PGOA.ENABLED_FLAG (+) = 'Y' AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+) AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE, SYSDATE+1)) ) ) AND PH.AGENT_ID = PPF.PERSON_ID AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(PPF.EFFECTIVE_END_DATE, SYSDATE + 1) AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+) AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID UNION ALL SELECT PAD.AUTOSOURCE_RULE_ID , PAD.VENDOR_ID , PV.VENDOR_NAME , PAD.SEQUENCE_NUM , PAD.DOCUMENT_TYPE_CODE , PAD.DOCUMENT_HEADER_ID , PH.SEGMENT1 , PAD.DOCUMENT_LINE_ID , PL.LINE_NUM , PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM , PAD.DOC_GENERATION_METHOD , PLL.LINE_LOCATION_ID , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_ID, PVSA2.VENDOR_SITE_ID) , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_CODE, PVSA2.VENDOR_SITE_CODE) , PH.VENDOR_CONTACT_ID , DECODE(PH.VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME||', '|| PVC.FIRST_NAME) , DECODE(PAD.DOCUMENT_TYPE_CODE, 'QUOTATION', PH.STATUS_LOOKUP_CODE, 'BLANKET', PH.AUTHORIZATION_STATUS) , PH.AGENT_ID , PPF.FULL_NAME , PH.CURRENCY_CODE , PH.TERMS_ID , PH.SHIP_VIA_LOOKUP_CODE , PH.FREIGHT_TERMS_LOOKUP_CODE , PH.FOB_LOOKUP_CODE , PLL.TERMS_ID , PLL.SHIP_VIA_LOOKUP_CODE , PLL.FREIGHT_TERMS_LOOKUP_CODE , PLL.FOB_LOOKUP_CODE , PH.QUOTE_VENDOR_QUOTE_NUMBER , PH.FROM_HEADER_ID , PH1.SEGMENT1 , PH.START_DATE , PH.END_DATE , PL.ITEM_REVISION , PL.VENDOR_PRODUCT_NUM , PL.LINE_TYPE_ID , PLT.LINE_TYPE , PL.UNIT_MEAS_LOOKUP_CODE , PL.QUANTITY , PL.UNIT_PRICE , PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED, 0) , PLL.PRICE_OVERRIDE , PLL.UNIT_MEAS_LOOKUP_CODE , PLL.SHIP_TO_LOCATION_ID , PLL.SHIP_TO_ORGANIZATION_ID , MP.ORGANIZATION_CODE , HOUT.NAME , PLL.START_DATE , PLL.END_DATE , GSB.CURRENCY_CODE , PH.APPROVAL_REQUIRED_FLAG , PLT.OUTSIDE_OPERATION_FLAG , PL.ITEM_ID , PL.CATEGORY_ID , PLT.ORDER_TYPE_LOOKUP_CODE , 'N' , PL.SECONDARY_UNIT_OF_MEASURE , PL.SECONDARY_QUANTITY , PLL.SECONDARY_UNIT_OF_MEASURE , PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED, 0) , PL.PREFERRED_GRADE , PH.GLOBAL_AGREEMENT_FLAG , PH.ORG_ID , PGOA.ORGANIZATION_ID , PGOA.PURCHASING_ORG_ID , PL.NEGOTIATED_BY_PREPARER_FLAG , PLT.PURCHASE_BASIS , PLT.MATCHING_BASIS ,FSP.ORG_ID FROM PO_VENDORS PV , PO_VENDOR_SITES_ALL PVSA , PO_VENDOR_SITES_ALL PVSA2 , AP_SUPPLIER_CONTACTS PVC , FINANCIALS_SYSTEM_PARAMETERS FSP , GL_SETS_OF_BOOKS GSB , PO_HEADERS_ALL PH , PO_HEADERS_ALL PH1 , PO_AUTOSOURCE_DOCUMENTS_ALL PAD , PO_LINES_ALL PL , PER_ALL_PEOPLE_F PPF , HR_ALL_ORGANIZATION_UNITS_TL HOUT , MTL_PARAMETERS MP , PO_LINE_LOCATIONS_ALL PLL , PO_LINE_TYPES PLT , PO_GA_ORG_ASSIGNMENTS PGOA WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID) AND PAD.VENDOR_ID = PV.VENDOR_ID AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID AND PAD.DOCUMENT_LINE_ID = PLL.PO_LINE_ID AND NVL(PLL.SHIPMENT_TYPE, 'PRICE BREAK') IN ('PRICE BREAK', 'QUOTATION') AND ((PH.AUTHORIZATION_STATUS = 'APPROVED' AND PH.APPROVED_DATE IS NOT NULL AND NVL(PH.CANCEL_FLAG, 'N') != 'Y' AND NVL(PH.FROZEN_FLAG, 'N') != 'Y' AND NVL(PH.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CANCEL_FLAG, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A')) AND (SYSDATE BETWEEN NVL(PH.START_DATE, SYSDATE - 1) AND NVL(PH.END_DATE, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE), TRUNC(SYSDATE))) AND (SYSDATE BETWEEN NVL(PLL.START_DATE, SYSDATE - 1) AND NVL(PLL.END_DATE, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE), TRUNC(SYSDATE))) AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+) AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+) AND PGOA.ENABLED_FLAG (+) = 'Y' AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+) AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE, SYSDATE+1)) ) ) AND PH.AGENT_ID = PPF.PERSON_ID AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(PPF.EFFECTIVE_END_DATE, SYSDATE + 1) AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+) AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND PLL.SHIP_TO_ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+) AND HOUT.LANGUAGE (+) = USERENV('LANG') AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+) AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID UNION ALL SELECT PAD.AUTOSOURCE_RULE_ID , PAD.VENDOR_ID , PV.VENDOR_NAME , PAD.SEQUENCE_NUM , PAD.DOCUMENT_TYPE_CODE , PAD.DOCUMENT_HEADER_ID , PH.SEGMENT1 , PAD.DOCUMENT_LINE_ID , PL.LINE_NUM , NULL , PAD.DOC_GENERATION_METHOD , TO_NUMBER(NULL) , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_ID, PVSA2.VENDOR_SITE_ID) , DECODE(PGOA.PO_HEADER_ID, NULL, PVSA.VENDOR_SITE_CODE, PVSA2.VENDOR_SITE_CODE) , PH.VENDOR_CONTACT_ID , DECODE(PH.VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME||', '||PVC.FIRST_NAME) , DECODE(PAD.DOCUMENT_TYPE_CODE, 'QUOTATION', PH.STATUS_LOOKUP_CODE, 'BLANKET', PH.AUTHORIZATION_STATUS) , PH.AGENT_ID , PPF.FULL_NAME , PH.CURRENCY_CODE , PH.TERMS_ID , PH.SHIP_VIA_LOOKUP_CODE , PH.FREIGHT_TERMS_LOOKUP_CODE , PH.FOB_LOOKUP_CODE , TO_NUMBER(NULL) , NULL , NULL , NULL , PH.QUOTE_VENDOR_QUOTE_NUMBER , PH.FROM_HEADER_ID , PH1.SEGMENT1 , PH.START_DATE , PH.END_DATE , PL.ITEM_REVISION , PL.VENDOR_PRODUCT_NUM , PL.LINE_TYPE_ID , PLT.LINE_TYPE , PL.UNIT_MEAS_LOOKUP_CODE , PL.QUANTITY , PL.UNIT_PRICE , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL , NULL , TO_DATE(NULL) , TO_DATE(NULL) , GSB.CURRENCY_CODE , PH.APPROVAL_REQUIRED_FLAG , PLT.OUTSIDE_OPERATION_FLAG , PL.ITEM_ID , PL.CATEGORY_ID , PLT.ORDER_TYPE_LOOKUP_CODE , 'N' , PL.SECONDARY_UNIT_OF_MEASURE , PL.SECONDARY_QUANTITY , NULL , TO_NUMBER(NULL) , PL.PREFERRED_GRADE , PH.GLOBAL_AGREEMENT_FLAG , PH.ORG_ID , PGOA.ORGANIZATION_ID , PGOA.PURCHASING_ORG_ID , PL.NEGOTIATED_BY_PREPARER_FLAG , PLT.PURCHASE_BASIS , PLT.MATCHING_BASIS , FSP.ORG_ID FROM PO_VENDORS PV , PO_VENDOR_SITES_ALL PVSA , PO_VENDOR_SITES_ALL PVSA2 , AP_SUPPLIER_CONTACTS PVC , FINANCIALS_SYSTEM_PARAMETERS FSP , GL_SETS_OF_BOOKS GSB , PO_HEADERS_ALL PH , PO_HEADERS_ALL PH1 , PO_AUTOSOURCE_DOCUMENTS_ALL PAD , PO_LINES_ALL PL , PER_ALL_PEOPLE_F PPF , PO_LINE_TYPES PLT , PO_GA_ORG_ASSIGNMENTS PGOA WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID) AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID AND NOT EXISTS (SELECT 'price break' FROM PO_LINE_LOCATIONS_ALL PLL WHERE PLL.SHIPMENT_TYPE IN ('PRICE BREAK', 'QUOTATION') AND PL.PO_LINE_ID = PLL.PO_LINE_ID AND (SYSDATE BETWEEN NVL(PLL.START_DATE, SYSDATE - 1) AND NVL(PLL.END_DATE, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE), TRUNC(SYSDATE))) ) AND ((PH.AUTHORIZATION_STATUS = 'APPROVED' AND PH.APPROVED_DATE IS NOT NULL AND NVL(PH.CANCEL_FLAG, 'N') != 'Y' AND NVL(PH.FROZEN_FLAG, 'N') != 'Y' AND NVL(PH.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CLOSED_CODE, 'OPEN') != 'FINALLY CLOSED' AND NVL(PL.CANCEL_FLAG, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A')) AND PAD.VENDOR_ID = PV.VENDOR_ID AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+) AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+) AND PGOA.ENABLED_FLAG (+) = 'Y' AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+) AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID AND NVL(PVSA.RFQ_ONLY_SITE_FLAG, 'N') = 'N' AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE, SYSDATE+1)) ) ) AND PH.AGENT_ID = PPF.PERSON_ID AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(PPF.EFFECTIVE_END_DATE, SYSDATE + 1) AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+) AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID

View Text - HTML Formatted

SELECT PASL.ASL_ID
, PASL.VENDOR_ID
, PV.VENDOR_NAME
, PAD.SEQUENCE_NUM
, PAD.DOCUMENT_TYPE_CODE
, PAD.DOCUMENT_HEADER_ID
, PH.SEGMENT1
, PAD.DOCUMENT_LINE_ID
, PL.LINE_NUM
, PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM
, PAA.RELEASE_GENERATION_METHOD
, PLL.LINE_LOCATION_ID
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_ID
, PVSA2.VENDOR_SITE_ID)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_CODE
, PVSA2.VENDOR_SITE_CODE)
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '|| PVC.FIRST_NAME)
, DECODE(PAD.DOCUMENT_TYPE_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS)
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.CURRENCY_CODE
, PH.TERMS_ID
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PLL.TERMS_ID
, PLL.SHIP_VIA_LOOKUP_CODE
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.FOB_LOOKUP_CODE
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.FROM_HEADER_ID
, PH1.SEGMENT1
, PH.START_DATE
, PH.END_DATE
, PL.ITEM_REVISION
, PL.VENDOR_PRODUCT_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)
, PLL.PRICE_OVERRIDE
, PLL.UNIT_MEAS_LOOKUP_CODE
, PLL.SHIP_TO_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOUT.NAME
, PLL.START_DATE
, PLL.END_DATE
, GSB.CURRENCY_CODE
, PH.APPROVAL_REQUIRED_FLAG
, PLT.OUTSIDE_OPERATION_FLAG
, PL.ITEM_ID
, PL.CATEGORY_ID
, PLT.ORDER_TYPE_LOOKUP_CODE
, 'Y'
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.SECONDARY_QUANTITY
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED
, 0)
, PL.PREFERRED_GRADE
, PH.GLOBAL_AGREEMENT_FLAG
, PH.ORG_ID
, PGOA.ORGANIZATION_ID
, PGOA.PURCHASING_ORG_ID
, PL.NEGOTIATED_BY_PREPARER_FLAG
, PLT.PURCHASE_BASIS
, PLT.MATCHING_BASIS
, FSP.ORG_ID
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVSA
, PO_VENDOR_SITES_ALL PVSA2
, AP_SUPPLIER_CONTACTS PVC
, FINANCIALS_SYSTEM_PARAMETERS FSP
, GL_SETS_OF_BOOKS GSB
, PO_HEADERS_ALL PH
, PO_HEADERS_ALL PH1
, PO_ASL_DOCUMENTS PAD
, PO_LINES_ALL PL
, PER_ALL_PEOPLE_F PPF
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, MTL_PARAMETERS MP
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINE_TYPES PLT
, PO_APPROVED_SUPPLIER_LIS_VAL_V PASL
, PO_ASL_ATTRIBUTES PAA
, PO_GA_ORG_ASSIGNMENTS PGOA
WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID
AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID)
AND PAA.ASL_ID = PAD.ASL_ID
AND PAA.USING_ORGANIZATION_ID = PAD.USING_ORGANIZATION_ID
AND PASL.ASL_ID = PAA.ASL_ID
AND PASL.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID
AND PAD.DOCUMENT_LINE_ID = PLL.PO_LINE_ID
AND NVL(PLL.SHIPMENT_TYPE
, 'PRICE BREAK') IN ('PRICE BREAK'
, 'QUOTATION')
AND ((PH.AUTHORIZATION_STATUS = 'APPROVED'
AND PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A'))
AND (SYSDATE BETWEEN NVL(PH.START_DATE
, SYSDATE - 1)
AND NVL(PH.END_DATE
, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE)
, TRUNC(SYSDATE)))
AND (SYSDATE BETWEEN NVL(PLL.START_DATE
, SYSDATE - 1)
AND NVL(PLL.END_DATE
, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE)
, TRUNC(SYSDATE)))
AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+)
AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+)
AND PGOA.ENABLED_FLAG (+) = 'Y'
AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+)
AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE
, SYSDATE+1)) ) )
AND PH.AGENT_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+)
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+)
AND HOUT.LANGUAGE (+) = USERENV('LANG')
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID UNION ALL SELECT PASL.ASL_ID
, PASL.VENDOR_ID
, PV.VENDOR_NAME
, PAD.SEQUENCE_NUM
, PAD.DOCUMENT_TYPE_CODE
, PAD.DOCUMENT_HEADER_ID
, PH.SEGMENT1
, PAD.DOCUMENT_LINE_ID
, PL.LINE_NUM
, NULL
, PAA.RELEASE_GENERATION_METHOD
, TO_NUMBER(NULL)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_ID
, PVSA2.VENDOR_SITE_ID)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_CODE
, PVSA2.VENDOR_SITE_CODE)
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME)
, DECODE(PAD.DOCUMENT_TYPE_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS)
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.CURRENCY_CODE
, PH.TERMS_ID
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.FROM_HEADER_ID
, PH1.SEGMENT1
, PH.START_DATE
, PH.END_DATE
, PL.ITEM_REVISION
, PL.VENDOR_PRODUCT_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, GSB.CURRENCY_CODE
, PH.APPROVAL_REQUIRED_FLAG
, PLT.OUTSIDE_OPERATION_FLAG
, PL.ITEM_ID
, PL.CATEGORY_ID
, PLT.ORDER_TYPE_LOOKUP_CODE
, 'Y'
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.SECONDARY_QUANTITY
, NULL
, TO_NUMBER(NULL)
, PL.PREFERRED_GRADE
, PH.GLOBAL_AGREEMENT_FLAG
, PH.ORG_ID
, PGOA.ORGANIZATION_ID
, PGOA.PURCHASING_ORG_ID
, PL.NEGOTIATED_BY_PREPARER_FLAG
, PLT.PURCHASE_BASIS
, PLT.MATCHING_BASIS
, FSP.ORG_ID
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVSA
, PO_VENDOR_SITES_ALL PVSA2
, AP_SUPPLIER_CONTACTS PVC
, FINANCIALS_SYSTEM_PARAMETERS FSP
, GL_SETS_OF_BOOKS GSB
, PO_HEADERS_ALL PH
, PO_HEADERS_ALL PH1
, PO_ASL_DOCUMENTS PAD
, PO_APPROVED_SUPPLIER_LIS_VAL_V PASL
, PO_ASL_ATTRIBUTES PAA
, PO_LINES_ALL PL
, PER_ALL_PEOPLE_F PPF
, PO_LINE_TYPES PLT
, PO_GA_ORG_ASSIGNMENTS PGOA
WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID
AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID)
AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID
AND PAA.ASL_ID = PASL.ASL_ID
AND PAD.ASL_ID = PAA.ASL_ID
AND PAD.USING_ORGANIZATION_ID = PAA.USING_ORGANIZATION_ID
AND NOT EXISTS (SELECT 'PRICE BREAK'
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.SHIPMENT_TYPE IN ('PRICE BREAK'
, 'QUOTATION')
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND (SYSDATE BETWEEN NVL(PLL.START_DATE
, SYSDATE - 1)
AND NVL(PLL.END_DATE
, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE)
, TRUNC(SYSDATE))) )
AND ((PH.AUTHORIZATION_STATUS = 'APPROVED'
AND PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A'))
AND PASL.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+)
AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+)
AND PGOA.ENABLED_FLAG (+) = 'Y'
AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+)
AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE
, SYSDATE+1)) ) )
AND PH.AGENT_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+)
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID UNION ALL SELECT PAD.AUTOSOURCE_RULE_ID
, PAD.VENDOR_ID
, PV.VENDOR_NAME
, PAD.SEQUENCE_NUM
, PAD.DOCUMENT_TYPE_CODE
, PAD.DOCUMENT_HEADER_ID
, PH.SEGMENT1
, PAD.DOCUMENT_LINE_ID
, PL.LINE_NUM
, PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM
, PAD.DOC_GENERATION_METHOD
, PLL.LINE_LOCATION_ID
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_ID
, PVSA2.VENDOR_SITE_ID)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_CODE
, PVSA2.VENDOR_SITE_CODE)
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '|| PVC.FIRST_NAME)
, DECODE(PAD.DOCUMENT_TYPE_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS)
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.CURRENCY_CODE
, PH.TERMS_ID
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PLL.TERMS_ID
, PLL.SHIP_VIA_LOOKUP_CODE
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.FOB_LOOKUP_CODE
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.FROM_HEADER_ID
, PH1.SEGMENT1
, PH.START_DATE
, PH.END_DATE
, PL.ITEM_REVISION
, PL.VENDOR_PRODUCT_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)
, PLL.PRICE_OVERRIDE
, PLL.UNIT_MEAS_LOOKUP_CODE
, PLL.SHIP_TO_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOUT.NAME
, PLL.START_DATE
, PLL.END_DATE
, GSB.CURRENCY_CODE
, PH.APPROVAL_REQUIRED_FLAG
, PLT.OUTSIDE_OPERATION_FLAG
, PL.ITEM_ID
, PL.CATEGORY_ID
, PLT.ORDER_TYPE_LOOKUP_CODE
, 'N'
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.SECONDARY_QUANTITY
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED
, 0)
, PL.PREFERRED_GRADE
, PH.GLOBAL_AGREEMENT_FLAG
, PH.ORG_ID
, PGOA.ORGANIZATION_ID
, PGOA.PURCHASING_ORG_ID
, PL.NEGOTIATED_BY_PREPARER_FLAG
, PLT.PURCHASE_BASIS
, PLT.MATCHING_BASIS
, FSP.ORG_ID
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVSA
, PO_VENDOR_SITES_ALL PVSA2
, AP_SUPPLIER_CONTACTS PVC
, FINANCIALS_SYSTEM_PARAMETERS FSP
, GL_SETS_OF_BOOKS GSB
, PO_HEADERS_ALL PH
, PO_HEADERS_ALL PH1
, PO_AUTOSOURCE_DOCUMENTS_ALL PAD
, PO_LINES_ALL PL
, PER_ALL_PEOPLE_F PPF
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, MTL_PARAMETERS MP
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINE_TYPES PLT
, PO_GA_ORG_ASSIGNMENTS PGOA
WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID
AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID)
AND PAD.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID
AND PAD.DOCUMENT_LINE_ID = PLL.PO_LINE_ID
AND NVL(PLL.SHIPMENT_TYPE
, 'PRICE BREAK') IN ('PRICE BREAK'
, 'QUOTATION')
AND ((PH.AUTHORIZATION_STATUS = 'APPROVED'
AND PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A'))
AND (SYSDATE BETWEEN NVL(PH.START_DATE
, SYSDATE - 1)
AND NVL(PH.END_DATE
, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE)
, TRUNC(SYSDATE)))
AND (SYSDATE BETWEEN NVL(PLL.START_DATE
, SYSDATE - 1)
AND NVL(PLL.END_DATE
, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE)
, TRUNC(SYSDATE)))
AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+)
AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+)
AND PGOA.ENABLED_FLAG (+) = 'Y'
AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+)
AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE
, SYSDATE+1)) ) )
AND PH.AGENT_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+)
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+)
AND HOUT.LANGUAGE (+) = USERENV('LANG')
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID UNION ALL SELECT PAD.AUTOSOURCE_RULE_ID
, PAD.VENDOR_ID
, PV.VENDOR_NAME
, PAD.SEQUENCE_NUM
, PAD.DOCUMENT_TYPE_CODE
, PAD.DOCUMENT_HEADER_ID
, PH.SEGMENT1
, PAD.DOCUMENT_LINE_ID
, PL.LINE_NUM
, NULL
, PAD.DOC_GENERATION_METHOD
, TO_NUMBER(NULL)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_ID
, PVSA2.VENDOR_SITE_ID)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_CODE
, PVSA2.VENDOR_SITE_CODE)
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME)
, DECODE(PAD.DOCUMENT_TYPE_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS)
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.CURRENCY_CODE
, PH.TERMS_ID
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.FROM_HEADER_ID
, PH1.SEGMENT1
, PH.START_DATE
, PH.END_DATE
, PL.ITEM_REVISION
, PL.VENDOR_PRODUCT_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, GSB.CURRENCY_CODE
, PH.APPROVAL_REQUIRED_FLAG
, PLT.OUTSIDE_OPERATION_FLAG
, PL.ITEM_ID
, PL.CATEGORY_ID
, PLT.ORDER_TYPE_LOOKUP_CODE
, 'N'
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.SECONDARY_QUANTITY
, NULL
, TO_NUMBER(NULL)
, PL.PREFERRED_GRADE
, PH.GLOBAL_AGREEMENT_FLAG
, PH.ORG_ID
, PGOA.ORGANIZATION_ID
, PGOA.PURCHASING_ORG_ID
, PL.NEGOTIATED_BY_PREPARER_FLAG
, PLT.PURCHASE_BASIS
, PLT.MATCHING_BASIS
, FSP.ORG_ID
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVSA
, PO_VENDOR_SITES_ALL PVSA2
, AP_SUPPLIER_CONTACTS PVC
, FINANCIALS_SYSTEM_PARAMETERS FSP
, GL_SETS_OF_BOOKS GSB
, PO_HEADERS_ALL PH
, PO_HEADERS_ALL PH1
, PO_AUTOSOURCE_DOCUMENTS_ALL PAD
, PO_LINES_ALL PL
, PER_ALL_PEOPLE_F PPF
, PO_LINE_TYPES PLT
, PO_GA_ORG_ASSIGNMENTS PGOA
WHERE PAD.DOCUMENT_HEADER_ID = PH.PO_HEADER_ID
AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = FSP.ORG_ID)
AND PAD.DOCUMENT_LINE_ID = PL.PO_LINE_ID
AND NOT EXISTS (SELECT 'PRICE BREAK'
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.SHIPMENT_TYPE IN ('PRICE BREAK'
, 'QUOTATION')
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND (SYSDATE BETWEEN NVL(PLL.START_DATE
, SYSDATE - 1)
AND NVL(PLL.END_DATE
, SYSDATE + 1) OR TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE)
, TRUNC(SYSDATE))) )
AND ((PH.AUTHORIZATION_STATUS = 'APPROVED'
AND PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR (PH.STATUS_LOOKUP_CODE = 'A'))
AND PAD.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID (+)
AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+)
AND PGOA.ENABLED_FLAG (+) = 'Y'
AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+)
AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE
, SYSDATE+1)) ) )
AND PH.AGENT_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+)
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID