VIEW: APPS.PO_QUOTATIONS_TO_APPROVE

Source

View Text - Preformatted

SELECT POH.ROWID , POH.CREATION_DATE , POH.CREATED_BY , POH.LAST_UPDATE_LOGIN , POH.LAST_UPDATED_BY , POH.LAST_UPDATE_DATE , POH.PO_HEADER_ID , POH.SEGMENT1 QUOTATION_NUM , POH.AGENT_ID , POH.TYPE_LOOKUP_CODE , POH.VENDOR_ID , PV.VENDOR_NAME , POH.VENDOR_SITE_ID , PVS.VENDOR_SITE_CODE , POH.STATUS_LOOKUP_CODE , PLC.DISPLAYED_FIELD QUOTE_STATUS , POH.FROM_TYPE_LOOKUP_CODE , POH.COMMENTS , POH.NOTE_TO_VENDOR VENDOR_QUOTE_HEADER_NOTE , POH.REPLY_DATE , POH.REPLY_METHOD_LOOKUP_CODE , POH.QUOTE_TYPE_LOOKUP_CODE , POH.QUOTE_WARNING_DELAY_UNIT , POH.QUOTE_WARNING_DELAY , POH.QUOTE_VENDOR_QUOTE_NUMBER , POH.APPROVAL_REQUIRED_FLAG , POH.CURRENCY_CODE , POH.ORG_ID , POL.LINE_NUM , POL.PO_LINE_ID , POL.LINE_TYPE_ID , PLT.LINE_TYPE , POL.ITEM_ID , POL.ITEM_REVISION , POL.CATEGORY_ID , MC.DESCRIPTION CATEGORY_NAME , POL.ITEM_DESCRIPTION , POL.UNIT_PRICE PRICE , POL.NOTE_TO_VENDOR VENDOR_QUOTE_LINE_NOTE , POL.MIN_ORDER_QUANTITY , POL.MAX_ORDER_QUANTITY , POL.VENDOR_PRODUCT_NUM , PLL.LINE_LOCATION_ID , PLL.QUANTITY LINE_LOC_QUANTITY , mum.UNIT_OF_MEASURE_TL , PLL.SHIP_TO_LOCATION_ID , HL.LOCATION_CODE ,DECODE(POH.QUOTE_TYPE_LOOKUP_CODE,'BID',POH.SHIP_VIA_LOOKUP_CODE, PLL.SHIP_VIA_LOOKUP_CODE) , PLL.PRICE_OVERRIDE PRICE_BREAK ,DECODE(POH.QUOTE_TYPE_LOOKUP_CODE,'BID',POH.FOB_LOOKUP_CODE, PLL.FOB_LOOKUP_CODE) ,DECODE(POH.QUOTE_TYPE_LOOKUP_CODE,'BID',POH.FREIGHT_TERMS_LOOKUP_CODE, PLL.FREIGHT_TERMS_LOOKUP_CODE) , Pll.FROM_HEADER_ID , POH1.SEGMENT1 RFQ_NUM , PLL.FROM_LINE_ID , PLL.FROM_LINE_LOCATION_ID , NVL(PLL.START_DATE, POH.START_DATE) START_DATE , NVL(PLL.END_DATE, POH.END_DATE) END_DATE , PLL.LEAD_TIME , PLL.LEAD_TIME_UNIT , PLL.PRICE_DISCOUNT ,DECODE(POH.QUOTE_TYPE_LOOKUP_CODE,'BID',POH.TERMS_ID, PLL.TERMS_ID) ,DECODE(POH.QUOTE_TYPE_LOOKUP_CODE,'BID',AT1.NAME, AT.NAME) TERM_NAME , PLL.SHIPMENT_NUM , POL.PROJECT_ID , pjm_project.ALL_PROJ_IDTONUM(POL.PROJECT_ID) , POL.TASK_ID , pjm_project.ALL_TASK_IDTONUM(POL.TASK_ID) FROM PO_HEADERS POH, PO_HEADERS POH1, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, PO_LINE_TYPES PLT, HR_LOCATIONS_ALL_TL HL, AP_TERMS AT, AP_TERMS AT1, MTL_CATEGORIES MC, PO_LOOKUP_CODES PLC, mtl_units_of_measure mum WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND POH1.PO_HEADER_ID(+) = POH.FROM_HEADER_ID AND POL.PO_LINE_ID = PLL.PO_LINE_ID AND POH.TYPE_LOOKUP_CODE = 'QUOTATION' AND POH.STATUS_LOOKUP_CODE = 'A' AND POH.VENDOR_ID = PV.VENDOR_ID AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+) AND HL.LANGUAGE(+) = USERENV('LANG') AND PLL.TERMS_ID = AT.TERM_ID (+) AND POH.TERMS_ID = AT1.TERM_ID (+) AND POL.CATEGORY_ID = MC.CATEGORY_ID AND POH.STATUS_LOOKUP_CODE = PLC.LOOKUP_CODE AND PLC.LOOKUP_TYPE = 'RFQ/QUOTE STATUS' AND TRUNC(SYSDATE) <=TRUNC(NVL(POH.END_DATE,SYSDATE)) AND mum.unit_of_measure(+) = pll.unit_meas_lookup_code

View Text - HTML Formatted

SELECT POH.ROWID
, POH.CREATION_DATE
, POH.CREATED_BY
, POH.LAST_UPDATE_LOGIN
, POH.LAST_UPDATED_BY
, POH.LAST_UPDATE_DATE
, POH.PO_HEADER_ID
, POH.SEGMENT1 QUOTATION_NUM
, POH.AGENT_ID
, POH.TYPE_LOOKUP_CODE
, POH.VENDOR_ID
, PV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, POH.STATUS_LOOKUP_CODE
, PLC.DISPLAYED_FIELD QUOTE_STATUS
, POH.FROM_TYPE_LOOKUP_CODE
, POH.COMMENTS
, POH.NOTE_TO_VENDOR VENDOR_QUOTE_HEADER_NOTE
, POH.REPLY_DATE
, POH.REPLY_METHOD_LOOKUP_CODE
, POH.QUOTE_TYPE_LOOKUP_CODE
, POH.QUOTE_WARNING_DELAY_UNIT
, POH.QUOTE_WARNING_DELAY
, POH.QUOTE_VENDOR_QUOTE_NUMBER
, POH.APPROVAL_REQUIRED_FLAG
, POH.CURRENCY_CODE
, POH.ORG_ID
, POL.LINE_NUM
, POL.PO_LINE_ID
, POL.LINE_TYPE_ID
, PLT.LINE_TYPE
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.CATEGORY_ID
, MC.DESCRIPTION CATEGORY_NAME
, POL.ITEM_DESCRIPTION
, POL.UNIT_PRICE PRICE
, POL.NOTE_TO_VENDOR VENDOR_QUOTE_LINE_NOTE
, POL.MIN_ORDER_QUANTITY
, POL.MAX_ORDER_QUANTITY
, POL.VENDOR_PRODUCT_NUM
, PLL.LINE_LOCATION_ID
, PLL.QUANTITY LINE_LOC_QUANTITY
, MUM.UNIT_OF_MEASURE_TL
, PLL.SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE
, DECODE(POH.QUOTE_TYPE_LOOKUP_CODE
, 'BID'
, POH.SHIP_VIA_LOOKUP_CODE
, PLL.SHIP_VIA_LOOKUP_CODE)
, PLL.PRICE_OVERRIDE PRICE_BREAK
, DECODE(POH.QUOTE_TYPE_LOOKUP_CODE
, 'BID'
, POH.FOB_LOOKUP_CODE
, PLL.FOB_LOOKUP_CODE)
, DECODE(POH.QUOTE_TYPE_LOOKUP_CODE
, 'BID'
, POH.FREIGHT_TERMS_LOOKUP_CODE
, PLL.FREIGHT_TERMS_LOOKUP_CODE)
, PLL.FROM_HEADER_ID
, POH1.SEGMENT1 RFQ_NUM
, PLL.FROM_LINE_ID
, PLL.FROM_LINE_LOCATION_ID
, NVL(PLL.START_DATE
, POH.START_DATE) START_DATE
, NVL(PLL.END_DATE
, POH.END_DATE) END_DATE
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.PRICE_DISCOUNT
, DECODE(POH.QUOTE_TYPE_LOOKUP_CODE
, 'BID'
, POH.TERMS_ID
, PLL.TERMS_ID)
, DECODE(POH.QUOTE_TYPE_LOOKUP_CODE
, 'BID'
, AT1.NAME
, AT.NAME) TERM_NAME
, PLL.SHIPMENT_NUM
, POL.PROJECT_ID
, PJM_PROJECT.ALL_PROJ_IDTONUM(POL.PROJECT_ID)
, POL.TASK_ID
, PJM_PROJECT.ALL_TASK_IDTONUM(POL.TASK_ID)
FROM PO_HEADERS POH
, PO_HEADERS POH1
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PO_LINE_TYPES PLT
, HR_LOCATIONS_ALL_TL HL
, AP_TERMS AT
, AP_TERMS AT1
, MTL_CATEGORIES MC
, PO_LOOKUP_CODES PLC
, MTL_UNITS_OF_MEASURE MUM
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND POH1.PO_HEADER_ID(+) = POH.FROM_HEADER_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.TYPE_LOOKUP_CODE = 'QUOTATION'
AND POH.STATUS_LOOKUP_CODE = 'A'
AND POH.VENDOR_ID = PV.VENDOR_ID
AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+)
AND HL.LANGUAGE(+) = USERENV('LANG')
AND PLL.TERMS_ID = AT.TERM_ID (+)
AND POH.TERMS_ID = AT1.TERM_ID (+)
AND POL.CATEGORY_ID = MC.CATEGORY_ID
AND POH.STATUS_LOOKUP_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'RFQ/QUOTE STATUS'
AND TRUNC(SYSDATE) <=TRUNC(NVL(POH.END_DATE
, SYSDATE))
AND MUM.UNIT_OF_MEASURE(+) = PLL.UNIT_MEAS_LOOKUP_CODE