VIEW: APPS.ICX_PO_REQ_TEMPLATE_ITEMS_V

Source

View Text - Preformatted

SELECT PRH.EXPRESS_NAME , PRH.EXPRESS_NAME || TO_CHAR(PRL.SEQUENCE_NUM) LINE_ID , PRH.TYPE_LOOKUP_CODE , PDT.TYPE_NAME DOCUMENT_TYPE , PRH.DESCRIPTION , PRL.SEQUENCE_NUM ,TO_CHAR(PRL.PO_HEADER_ID) SOURCE_DOC_HEADER_ID , PH.SEGMENT1 SOURCE_DOC_NUM , PH.TYPE_LOOKUP_CODE SOURCE_DOC_TYPE_CODE , PDT1.TYPE_NAME SOURCE_DOC_TYPE , PH.CURRENCY_CODE SOURCE_DOC_CURRENCY , PH.RATE SOURCE_DOC_RATE , PH.RATE_TYPE SOURCE_DOC_RATE_TYPE , DCT.USER_CONVERSION_TYPE SOURCE_DOC_RATE_TYPE_DSP , PH.RATE_DATE SOURCE_DOC_RATE_DATE , GSB.CURRENCY_CODE SOURCE_DOC_FUNCTIONAL_CURRENCY , PRL.PO_LINE_ID SOURCE_DOC_LINE_ID , TO_CHAR(PL.LINE_NUM) SOURCE_DOC_LINE_NUM , PL.UNIT_PRICE SOURCE_DOC_CURRENCY_PRICE , ROUND(NVL(PL.UNIT_PRICE, 0) * NVL(PH.RATE, 1), 5) SOURCE_DOC_BASE_PRICE , TO_CHAR(PRL.ITEM_ID) ITEM_ID , PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION , DECODE(ROUND(NVL(PL.UNIT_PRICE, 0) * NVL(PH.RATE, 1), 5), 0, NVL(PRL.UNIT_PRICE, 0),ROUND(NVL(PL.UNIT_PRICE, 0) * NVL(PH.RATE, 1), 5)) PRICE , TO_CHAR(PRL.LINE_TYPE_ID) LINE_TYPE_ID , PLT.LINE_TYPE , PRL.ITEM_REVISION , TO_CHAR(PRL.CATEGORY_ID) CATEGORY_ID , PRL.UNIT_MEAS_LOOKUP_CODE UOM , NVL(PRL.UNIT_PRICE, 0) TEMPLATE_LINE_PRICE , PRL.SUGGESTED_VENDOR_ID SUGGESTED_SOURCE_ID , PV.VENDOR_NAME SOURCE_NAME , PRL.SUGGESTED_VENDOR_SITE_ID ,PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE , PRL.SUGGESTED_VENDOR_CONTACT_ID , DECODE(PRL.SUGGESTED_VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME|| ', '||PVC.FIRST_NAME) VENDOR_CONTACT_NAME , NVL(PRL.SUGGESTED_VENDOR_PRODUCT_CODE, '-') SUGGESTED_VENDOR_PRODUCT_CODE , PRL.SUGGESTED_BUYER_ID , HRE.FULL_NAME BUYER_NAME , PRL.RFQ_REQUIRED_FLAG , PRL.SOURCE_TYPE_CODE , PLT.OUTSIDE_OPERATION_FLAG , PLT.ORDER_TYPE_LOOKUP_CODE , PLC1.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP ,MSI.CONCATENATED_SEGMENTS ITEM_NUMBER , MSI.ORGANIZATION_ID , PV.ATTRIBUTE14 SUPPLIER_URL , NVL(PL.ATTRIBUTE14, MSI.ATTRIBUTE14) SUPPLIER_ITEM_URL , MSI.ATTRIBUTE14 ITEM_URL FROM PER_PEOPLE_F HRE , PO_DOCUMENT_TYPES PDT , MTL_SYSTEM_ITEMS_KFV MSI , PO_LINE_TYPES PLT , PO_VENDORS PV , GL_DAILY_CONVERSION_TYPES DCT , PO_VENDOR_SITES PVS , PO_VENDOR_CONTACTS PVC , PO_LOOKUP_CODES PLC1 , PO_HEADERS PH , PO_LINES PL , PO_DOCUMENT_TYPES PDT1 , PO_REQEXPRESS_LINES PRL , PO_REQEXPRESS_HEADERS PRH , GL_SETS_OF_BOOKS GSB , FINANCIALS_SYSTEM_PARAMETERS FSP WHERE PRH.EXPRESS_NAME = PRL.EXPRESS_NAME AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID(+) AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+) AND PRL.SUGGESTED_VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND PRL.SUGGESTED_BUYER_ID = HRE.PERSON_ID(+) AND SYSDATE BETWEEN NVL(HRE.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(HRE.EFFECTIVE_END_DATE, SYSDATE + 1) AND SYSDATE < NVL(PRH.INACTIVE_DATE, SYSDATE + 1) AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND NVL(PH.PO_HEADER_ID, -1) = NVL(PL.PO_HEADER_ID, -1) AND PRL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+) AND PRL.SOURCE_TYPE_CODE = 'VENDOR' AND PH.TYPE_LOOKUP_CODE = PDT1.DOCUMENT_SUBTYPE(+) AND PDT1.DOCUMENT_TYPE_CODE(+) = 'PA' AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC1.LOOKUP_CODE AND NVL(PLT.OUTSIDE_OPERATION_FLAG,'N') = 'N' AND PLC1.LOOKUP_TYPE = 'ORDER TYPE' AND PRL.ITEM_ID IS NOT NULL AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.PURCHASING_ENABLED_FLAG = 'Y' UNION ALL SELECT PRH.EXPRESS_NAME ,PRH.EXPRESS_NAME || TO_CHAR(PRL.SEQUENCE_NUM) LINE_ID , PRH.TYPE_LOOKUP_CODE , PDT.TYPE_NAME DOCUMENT_TYPE , PRH.DESCRIPTION , PRL.SEQUENCE_NUM ,TO_CHAR(PRL.PO_HEADER_ID) SOURCE_DOC_HEADER_ID , PH.SEGMENT1 SOURCE_DOC_NUM , PH.TYPE_LOOKUP_CODE SOURCE_DOC_TYPE_CODE , PDT1.TYPE_NAME SOURCE_DOC_TYPE , PH.CURRENCY_CODE SOURCE_DOC_CURRENCY , PH.RATE SOURCE_DOC_RATE , PH.RATE_TYPE SOURCE_DOC_RATE_TYPE , DCT.USER_CONVERSION_TYPE SOURCE_DOC_RATE_TYPE_DSP , PH.RATE_DATE SOURCE_DOC_RATE_DATE , GSB.CURRENCY_CODE SOURCE_DOC_FUNCTIONAL_CURRENCY , PRL.PO_LINE_ID SOURCE_DOC_LINE_ID ,TO_CHAR(PL.LINE_NUM) SOURCE_DOC_LINE_NUM , PL.UNIT_PRICE SOURCE_DOC_CURRENCY_PRICE , ROUND(NVL(PL.UNIT_PRICE, 0) * NVL(PH.RATE, 1), 5) SOURCE_DOC_BASE_PRICE ,TO_CHAR(PRL.ITEM_ID) ITEM_ID ,PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION ,DECODE(ROUND(NVL(PL.UNIT_PRICE, 0) * NVL(PH.RATE, 1), 5), 0, NVL(PRL.UNIT_PRICE, 0),ROUND(NVL(PL.UNIT_PRICE, 0) * NVL(PH.RATE, 1), 5)) PRICE ,TO_CHAR(PRL.LINE_TYPE_ID) LINE_TYPE_ID , PLT.LINE_TYPE , PRL.ITEM_REVISION ,TO_CHAR(PRL.CATEGORY_ID) CATEGORY_ID ,PRL.UNIT_MEAS_LOOKUP_CODE UOM , NVL(PRL.UNIT_PRICE, 0) TEMPLATE_LINE_PRICE , PRL.SUGGESTED_VENDOR_ID SUGGESTED_SOURCE_ID ,PV.VENDOR_NAME SOURCE_NAME , PRL.SUGGESTED_VENDOR_SITE_ID ,PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE , PRL.SUGGESTED_VENDOR_CONTACT_ID , DECODE(PRL.SUGGESTED_VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME||', '||PVC.FIRST_NAME) VENDOR_CONTACT_NAME ,NVL(SUGGESTED_VENDOR_PRODUCT_CODE, '-') SUGGESTED_VENDOR_PRODUCT_CODE , PRL.SUGGESTED_BUYER_ID , HRE.FULL_NAME BUYER_NAME , PRL.RFQ_REQUIRED_FLAG , PRL.SOURCE_TYPE_CODE , PLT.OUTSIDE_OPERATION_FLAG , PLT.ORDER_TYPE_LOOKUP_CODE , PLC1.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP , NULL ITEM_NUMBER , TO_NUMBER(NULL) ORGANIZATION_ID ,PV.ATTRIBUTE14 SUPPLIER_URL ,PL.ATTRIBUTE14 SUPPLIER_ITEM_URL , NULL ITEM_URL FROM PER_PEOPLE_F HRE , PO_DOCUMENT_TYPES PDT , PO_LINE_TYPES PLT , PO_VENDORS PV , GL_DAILY_CONVERSION_TYPES DCT , PO_VENDOR_SITES PVS , PO_VENDOR_CONTACTS PVC , PO_LOOKUP_CODES PLC1 , PO_HEADERS PH , PO_LINES PL , PO_DOCUMENT_TYPES PDT1 , PO_REQEXPRESS_LINES PRL , PO_REQEXPRESS_HEADERS PRH , GL_SETS_OF_BOOKS GSB , FINANCIALS_SYSTEM_PARAMETERS FSP WHERE PRH.EXPRESS_NAME = PRL.EXPRESS_NAME AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID(+) AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+) AND PRL.SUGGESTED_VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND PRL.SUGGESTED_BUYER_ID = HRE.PERSON_ID(+) AND SYSDATE BETWEEN NVL(HRE.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(HRE.EFFECTIVE_END_DATE, SYSDATE + 1) AND SYSDATE < NVL(PRH.INACTIVE_DATE, SYSDATE + 1) AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND NVL(PH.PO_HEADER_ID, -1) = NVL(PL.PO_HEADER_ID, -1) AND PRL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+) AND PRL.SOURCE_TYPE_CODE = 'VENDOR' AND PH.TYPE_LOOKUP_CODE = PDT1.DOCUMENT_SUBTYPE(+) AND PDT1.DOCUMENT_TYPE_CODE(+) = 'PA' AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC1.LOOKUP_CODE AND NVL(PLT.OUTSIDE_OPERATION_FLAG,'N') = 'N' AND PLC1.LOOKUP_TYPE = 'ORDER TYPE' AND PRL.ITEM_ID IS NULL

View Text - HTML Formatted

SELECT PRH.EXPRESS_NAME
, PRH.EXPRESS_NAME || TO_CHAR(PRL.SEQUENCE_NUM) LINE_ID
, PRH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME DOCUMENT_TYPE
, PRH.DESCRIPTION
, PRL.SEQUENCE_NUM
, TO_CHAR(PRL.PO_HEADER_ID) SOURCE_DOC_HEADER_ID
, PH.SEGMENT1 SOURCE_DOC_NUM
, PH.TYPE_LOOKUP_CODE SOURCE_DOC_TYPE_CODE
, PDT1.TYPE_NAME SOURCE_DOC_TYPE
, PH.CURRENCY_CODE SOURCE_DOC_CURRENCY
, PH.RATE SOURCE_DOC_RATE
, PH.RATE_TYPE SOURCE_DOC_RATE_TYPE
, DCT.USER_CONVERSION_TYPE SOURCE_DOC_RATE_TYPE_DSP
, PH.RATE_DATE SOURCE_DOC_RATE_DATE
, GSB.CURRENCY_CODE SOURCE_DOC_FUNCTIONAL_CURRENCY
, PRL.PO_LINE_ID SOURCE_DOC_LINE_ID
, TO_CHAR(PL.LINE_NUM) SOURCE_DOC_LINE_NUM
, PL.UNIT_PRICE SOURCE_DOC_CURRENCY_PRICE
, ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5) SOURCE_DOC_BASE_PRICE
, TO_CHAR(PRL.ITEM_ID) ITEM_ID
, PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, DECODE(ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5)
, 0
, NVL(PRL.UNIT_PRICE
, 0)
, ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5)) PRICE
, TO_CHAR(PRL.LINE_TYPE_ID) LINE_TYPE_ID
, PLT.LINE_TYPE
, PRL.ITEM_REVISION
, TO_CHAR(PRL.CATEGORY_ID) CATEGORY_ID
, PRL.UNIT_MEAS_LOOKUP_CODE UOM
, NVL(PRL.UNIT_PRICE
, 0) TEMPLATE_LINE_PRICE
, PRL.SUGGESTED_VENDOR_ID SUGGESTED_SOURCE_ID
, PV.VENDOR_NAME SOURCE_NAME
, PRL.SUGGESTED_VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, PRL.SUGGESTED_VENDOR_CONTACT_ID
, DECODE(PRL.SUGGESTED_VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME|| '
, '||PVC.FIRST_NAME) VENDOR_CONTACT_NAME
, NVL(PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, '-') SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.SUGGESTED_BUYER_ID
, HRE.FULL_NAME BUYER_NAME
, PRL.RFQ_REQUIRED_FLAG
, PRL.SOURCE_TYPE_CODE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.ORGANIZATION_ID
, PV.ATTRIBUTE14 SUPPLIER_URL
, NVL(PL.ATTRIBUTE14
, MSI.ATTRIBUTE14) SUPPLIER_ITEM_URL
, MSI.ATTRIBUTE14 ITEM_URL
FROM PER_PEOPLE_F HRE
, PO_DOCUMENT_TYPES PDT
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_LINE_TYPES PLT
, PO_VENDORS PV
, GL_DAILY_CONVERSION_TYPES DCT
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, PO_LOOKUP_CODES PLC1
, PO_HEADERS PH
, PO_LINES PL
, PO_DOCUMENT_TYPES PDT1
, PO_REQEXPRESS_LINES PRL
, PO_REQEXPRESS_HEADERS PRH
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PRH.EXPRESS_NAME = PRL.EXPRESS_NAME
AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID(+)
AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PRL.SUGGESTED_VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PRL.SUGGESTED_BUYER_ID = HRE.PERSON_ID(+)
AND SYSDATE BETWEEN NVL(HRE.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(HRE.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND SYSDATE < NVL(PRH.INACTIVE_DATE
, SYSDATE + 1)
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND NVL(PH.PO_HEADER_ID
, -1) = NVL(PL.PO_HEADER_ID
, -1)
AND PRL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PRL.SOURCE_TYPE_CODE = 'VENDOR'
AND PH.TYPE_LOOKUP_CODE = PDT1.DOCUMENT_SUBTYPE(+)
AND PDT1.DOCUMENT_TYPE_CODE(+) = 'PA'
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC1.LOOKUP_CODE
AND NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') = 'N'
AND PLC1.LOOKUP_TYPE = 'ORDER TYPE'
AND PRL.ITEM_ID IS NOT NULL
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.PURCHASING_ENABLED_FLAG = 'Y' UNION ALL SELECT PRH.EXPRESS_NAME
, PRH.EXPRESS_NAME || TO_CHAR(PRL.SEQUENCE_NUM) LINE_ID
, PRH.TYPE_LOOKUP_CODE
, PDT.TYPE_NAME DOCUMENT_TYPE
, PRH.DESCRIPTION
, PRL.SEQUENCE_NUM
, TO_CHAR(PRL.PO_HEADER_ID) SOURCE_DOC_HEADER_ID
, PH.SEGMENT1 SOURCE_DOC_NUM
, PH.TYPE_LOOKUP_CODE SOURCE_DOC_TYPE_CODE
, PDT1.TYPE_NAME SOURCE_DOC_TYPE
, PH.CURRENCY_CODE SOURCE_DOC_CURRENCY
, PH.RATE SOURCE_DOC_RATE
, PH.RATE_TYPE SOURCE_DOC_RATE_TYPE
, DCT.USER_CONVERSION_TYPE SOURCE_DOC_RATE_TYPE_DSP
, PH.RATE_DATE SOURCE_DOC_RATE_DATE
, GSB.CURRENCY_CODE SOURCE_DOC_FUNCTIONAL_CURRENCY
, PRL.PO_LINE_ID SOURCE_DOC_LINE_ID
, TO_CHAR(PL.LINE_NUM) SOURCE_DOC_LINE_NUM
, PL.UNIT_PRICE SOURCE_DOC_CURRENCY_PRICE
, ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5) SOURCE_DOC_BASE_PRICE
, TO_CHAR(PRL.ITEM_ID) ITEM_ID
, PRL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, DECODE(ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5)
, 0
, NVL(PRL.UNIT_PRICE
, 0)
, ROUND(NVL(PL.UNIT_PRICE
, 0) * NVL(PH.RATE
, 1)
, 5)) PRICE
, TO_CHAR(PRL.LINE_TYPE_ID) LINE_TYPE_ID
, PLT.LINE_TYPE
, PRL.ITEM_REVISION
, TO_CHAR(PRL.CATEGORY_ID) CATEGORY_ID
, PRL.UNIT_MEAS_LOOKUP_CODE UOM
, NVL(PRL.UNIT_PRICE
, 0) TEMPLATE_LINE_PRICE
, PRL.SUGGESTED_VENDOR_ID SUGGESTED_SOURCE_ID
, PV.VENDOR_NAME SOURCE_NAME
, PRL.SUGGESTED_VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, PRL.SUGGESTED_VENDOR_CONTACT_ID
, DECODE(PRL.SUGGESTED_VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME) VENDOR_CONTACT_NAME
, NVL(SUGGESTED_VENDOR_PRODUCT_CODE
, '-') SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.SUGGESTED_BUYER_ID
, HRE.FULL_NAME BUYER_NAME
, PRL.RFQ_REQUIRED_FLAG
, PRL.SOURCE_TYPE_CODE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD ORDER_TYPE_LOOKUP_DSP
, NULL ITEM_NUMBER
, TO_NUMBER(NULL) ORGANIZATION_ID
, PV.ATTRIBUTE14 SUPPLIER_URL
, PL.ATTRIBUTE14 SUPPLIER_ITEM_URL
, NULL ITEM_URL
FROM PER_PEOPLE_F HRE
, PO_DOCUMENT_TYPES PDT
, PO_LINE_TYPES PLT
, PO_VENDORS PV
, GL_DAILY_CONVERSION_TYPES DCT
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, PO_LOOKUP_CODES PLC1
, PO_HEADERS PH
, PO_LINES PL
, PO_DOCUMENT_TYPES PDT1
, PO_REQEXPRESS_LINES PRL
, PO_REQEXPRESS_HEADERS PRH
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PRH.EXPRESS_NAME = PRL.EXPRESS_NAME
AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID(+)
AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PRL.SUGGESTED_VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PRL.SUGGESTED_BUYER_ID = HRE.PERSON_ID(+)
AND SYSDATE BETWEEN NVL(HRE.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(HRE.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND SYSDATE < NVL(PRH.INACTIVE_DATE
, SYSDATE + 1)
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND NVL(PH.PO_HEADER_ID
, -1) = NVL(PL.PO_HEADER_ID
, -1)
AND PRL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PRL.SOURCE_TYPE_CODE = 'VENDOR'
AND PH.TYPE_LOOKUP_CODE = PDT1.DOCUMENT_SUBTYPE(+)
AND PDT1.DOCUMENT_TYPE_CODE(+) = 'PA'
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC1.LOOKUP_CODE
AND NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') = 'N'
AND PLC1.LOOKUP_TYPE = 'ORDER TYPE'
AND PRL.ITEM_ID IS NULL