VIEW: APPS.ICX_PO_REQ_LINES_V

Source

View Text - Preformatted

SELECT PRL.ROWID , PRL.REQUISITION_HEADER_ID , PRL.REQUISITION_LINE_ID , PRL.LINE_NUM , PLT.LINE_TYPE , PLT.LINE_TYPE_ID , PRL.ITEM_ID , PRL.ITEM_REVISION , PRL.CATEGORY_ID , PRL.ITEM_DESCRIPTION , PRL.QUANTITY , NVL(PRL.QUANTITY_RECEIVED,0) , NVL(PRL.QUANTITY_DELIVERED,0) , NVL(PRL.QUANTITY_CANCELLED,0) , PRL.UNIT_MEAS_LOOKUP_CODE , PRL.UNIT_PRICE , TO_CHAR ( PRL.UNIT_PRICE, FND_CURRENCY.GET_FORMAT_MASK( GSOB.CURRENCY_CODE, 30 ) ) DISPLAY_UNIT_PRICE , PRL.NEED_BY_DATE , PRL.NOTE_TO_AGENT , PRL.SUGGESTED_VENDOR_NAME , PRL.SUGGESTED_VENDOR_LOCATION , PRL.SUGGESTED_VENDOR_CONTACT , PRL.SUGGESTED_VENDOR_PHONE , PRL.RFQ_REQUIRED_FLAG , PRL.ON_RFQ_FLAG , PRL.CANCEL_FLAG , PRL.MODIFIED_BY_AGENT_FLAG , POUN.UN_NUMBER , PHC.HAZARD_CLASS , PLC_TRSN.DISPLAYED_FIELD , PRL.REFERENCE_NUM , PRL.URGENT_FLAG , PRL.JUSTIFICATION , PRL.NOTE_TO_RECEIVER , PRL.ENCUMBERED_FLAG , PRL.DESTINATION_TYPE_CODE , PRL.SOURCE_TYPE_CODE , OOD1.ORGANIZATION_NAME , HRL1.LOCATION_CODE , PRL.DESTINATION_SUBINVENTORY , HOU.NAME , PRL.SOURCE_SUBINVENTORY , PRL.ATTRIBUTE1 , PRL.ATTRIBUTE2 , PRL.ATTRIBUTE3 , PRL.ATTRIBUTE4 , PRL.ATTRIBUTE5 , PRL.ATTRIBUTE6 , PRL.ATTRIBUTE7 , PRL.ATTRIBUTE8 , PRL.ATTRIBUTE9 , PRL.ATTRIBUTE10 , PRL.ATTRIBUTE11 , PRL.ATTRIBUTE12 , PRL.ATTRIBUTE13 , PRL.ATTRIBUTE14 , PRL.ATTRIBUTE15 , PRL.ATTRIBUTE_CATEGORY , NVL(PLT.OUTSIDE_OPERATION_FLAG, 'N') , PRL.DELIVER_TO_LOCATION_ID , PRL.TO_PERSON_ID , PO_INQ_SV.GET_PERSON_NAME(TO_PERSON_ID) , PRL.LAST_UPDATE_DATE , PRL.LAST_UPDATED_BY , PRL.LAST_UPDATE_LOGIN , PRL.CREATION_DATE , PRL.CREATED_BY , PRL.SUGGESTED_VENDOR_PRODUCT_CODE , PRL.UN_NUMBER_ID , PRL.HAZARD_CLASS_ID , PRL.CURRENCY_CODE , PRL.RATE , PRL.RATE_TYPE , PRL.RATE_DATE , PRL.CURRENCY_UNIT_PRICE , PRL.SUGGESTED_BUYER_ID , PO_INQ_SV.GET_PERSON_NAME(PRL.SUGGESTED_BUYER_ID) , PRL.CLOSED_CODE , PRL.LINE_LOCATION_ID , MSI.CONCATENATED_SEGMENTS , MCA.CONCATENATED_SEGMENTS , PRL.PARENT_REQ_LINE_ID , PRL.PURCHASING_AGENT_ID , PRL.DOCUMENT_TYPE_CODE , PRL.BLANKET_PO_HEADER_ID , PRL.BLANKET_PO_LINE_NUM , PRL.CANCEL_REASON , PRL.CANCEL_DATE , PRL.AGENT_RETURN_NOTE , PRL.CHANGED_AFTER_RESEARCH_FLAG , PRL.VENDOR_ID , PRL.VENDOR_SITE_ID , PRL.VENDOR_CONTACT_ID , PRL.RESEARCH_AGENT_ID , PRL.ON_LINE_FLAG , PRL.DESTINATION_CONTEXT , PRL.INVENTORY_SOURCE_CONTEXT , PRL.VENDOR_SOURCE_CONTEXT , PRL.BOM_RESOURCE_ID , PRL.USSGL_TRANSACTION_CODE , PRL.GOVERNMENT_CONTEXT , PRL.CLOSED_REASON , PRL.CLOSED_DATE , PRL.TRANSACTION_REASON_CODE , PRL.SOURCE_REQ_LINE_ID , PRL.DESTINATION_ORGANIZATION_ID , PRL.SOURCE_ORGANIZATION_ID , PO_INQ_SV.GET_PERSON_NAME(PRL.PURCHASING_AGENT_ID) , PLT.ORDER_TYPE_LOOKUP_CODE , GDT.USER_CONVERSION_TYPE , PLC_DEST.DISPLAYED_FIELD , PLC_STYPE.DISPLAYED_FIELD , PLC_SDOC.DISPLAYED_FIELD , POLA.CUSTOMER_ID , POLA.ADDRESS_ID , POLA.SITE_USE_ID , PO_REQS_INQ_SV.GET_PO_NUMBER(PRL.BLANKET_PO_HEADER_ID) , DECODE(PRL.SOURCE_TYPE_CODE, 'VENDOR', PO_INQ_SV.GET_PO_NUMBER(PRL.LINE_LOCATION_ID), PO_INQ_SV.GET_SO_NUMBER(PRH.SEGMENT1,PRL.LINE_NUM)) , NVL(POL.ATTRIBUTE14,MSI.ATTRIBUTE14) , POV.ATTRIBUTE14 , POV.VENDOR_NAME FROM GL_DAILY_CONVERSION_TYPES GDT , FINANCIALS_SYSTEM_PARAMETERS FSP , PO_LINE_TYPES PLT , PO_LOOKUP_CODES PLC_TRSN , PO_LOOKUP_CODES PLC_SDOC , PO_LOOKUP_CODES PLC_STYPE , PO_LOOKUP_CODES PLC_DEST , PO_UN_NUMBERS POUN , PO_HAZARD_CLASSES PHC , PO_LOCATION_ASSOCIATIONS POLA , HR_LOCATIONS HRL1 , ORG_ORGANIZATION_DEFINITIONS OOD1 , HR_ORGANIZATION_UNITS HOU , MTL_SYSTEM_ITEMS_KFV MSI , MTL_CATEGORIES_KFV MCA , PO_VENDORS POV , PO_LINES POL , PO_REQUISITION_HEADERS PRH , PO_REQUISITION_LINES PRL , GL_SETS_OF_BOOKS GSOB WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID AND POUN.UN_NUMBER_ID (+) = PRL.UN_NUMBER_ID AND PHC.HAZARD_CLASS_ID (+) = PRL.HAZARD_CLASS_ID AND OOD1.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID AND HRL1.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID AND PRL.ITEM_ID IS NOT NULL AND MSI.INVENTORY_ITEM_ID = PRL.ITEM_ID AND MSI.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID AND MCA.CATEGORY_ID = PRL.CATEGORY_ID AND POLA.LOCATION_ID (+) = PRL.DELIVER_TO_LOCATION_ID AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE AND HOU.ORGANIZATION_ID (+) = PRL.SOURCE_ORGANIZATION_ID AND PLC_TRSN.LOOKUP_TYPE (+) = 'TRANSACTION_REASON' AND PLC_TRSN.LOOKUP_CODE (+) = PRL.TRANSACTION_REASON_CODE AND PLC_SDOC.LOOKUP_TYPE (+) = 'SOURCE DOCUMENT TYPE' AND PLC_SDOC.LOOKUP_CODE (+) = PRL.DOCUMENT_TYPE_CODE AND PLC_STYPE.LOOKUP_TYPE = 'REQUISITION SOURCE TYPE' AND PLC_STYPE.LOOKUP_CODE = PRL.SOURCE_TYPE_CODE AND PLC_DEST.LOOKUP_TYPE = 'DESTINATION TYPE' AND PLC_DEST.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE AND POL.PO_HEADER_ID (+) = PRL.BLANKET_PO_HEADER_ID AND POL.LINE_NUM (+) = PRL.BLANKET_PO_LINE_NUM AND POV.VENDOR_ID (+) = PRL.VENDOR_ID AND GSOB.SET_OF_BOOKS_ID = OOD1.SET_OF_BOOKS_ID UNION SELECT PRL.ROWID , PRL.REQUISITION_HEADER_ID , PRL.REQUISITION_LINE_ID , PRL.LINE_NUM , PLT.LINE_TYPE , PLT.LINE_TYPE_ID , PRL.ITEM_ID , PRL.ITEM_REVISION , PRL.CATEGORY_ID , PRL.ITEM_DESCRIPTION , PRL.QUANTITY , NVL(PRL.QUANTITY_RECEIVED,0) , NVL(PRL.QUANTITY_DELIVERED,0) , NVL(PRL.QUANTITY_CANCELLED,0) , PRL.UNIT_MEAS_LOOKUP_CODE , PRL.UNIT_PRICE , TO_CHAR (PRL.UNIT_PRICE, FND_CURRENCY.GET_FORMAT_MASK(GSOB.CURRENCY_CODE, 30 ) ) DISPLAY_UNIT_PRICE , PRL.NEED_BY_DATE , PRL.NOTE_TO_AGENT , PRL.SUGGESTED_VENDOR_NAME , PRL.SUGGESTED_VENDOR_LOCATION , PRL.SUGGESTED_VENDOR_CONTACT , PRL.SUGGESTED_VENDOR_PHONE , PRL.RFQ_REQUIRED_FLAG , PRL.ON_RFQ_FLAG , PRL.CANCEL_FLAG , PRL.MODIFIED_BY_AGENT_FLAG , POUN.UN_NUMBER , PHC.HAZARD_CLASS , PLC_TRSN.DISPLAYED_FIELD , PRL.REFERENCE_NUM , PRL.URGENT_FLAG , PRL.JUSTIFICATION , PRL.NOTE_TO_RECEIVER , PRL.ENCUMBERED_FLAG , PRL.DESTINATION_TYPE_CODE , PRL.SOURCE_TYPE_CODE , OOD1.ORGANIZATION_NAME , HRL1.LOCATION_CODE , PRL.DESTINATION_SUBINVENTORY , HOU.NAME , PRL.SOURCE_SUBINVENTORY , PRL.ATTRIBUTE1 , PRL.ATTRIBUTE2 , PRL.ATTRIBUTE3 , PRL.ATTRIBUTE4 , PRL.ATTRIBUTE5 , PRL.ATTRIBUTE6 , PRL.ATTRIBUTE7 , PRL.ATTRIBUTE8 , PRL.ATTRIBUTE9 , PRL.ATTRIBUTE10 , PRL.ATTRIBUTE11 , PRL.ATTRIBUTE12 , PRL.ATTRIBUTE13 , PRL.ATTRIBUTE14 , PRL.ATTRIBUTE15 , PRL.ATTRIBUTE_CATEGORY , NVL(PLT.OUTSIDE_OPERATION_FLAG, 'N') , PRL.DELIVER_TO_LOCATION_ID , PRL.TO_PERSON_ID , PO_INQ_SV.GET_PERSON_NAME(TO_PERSON_ID) , PRL.LAST_UPDATE_DATE , PRL.LAST_UPDATED_BY , PRL.LAST_UPDATE_LOGIN , PRL.CREATION_DATE , PRL.CREATED_BY , PRL.SUGGESTED_VENDOR_PRODUCT_CODE , PRL.UN_NUMBER_ID , PRL.HAZARD_CLASS_ID , PRL.CURRENCY_CODE , PRL.RATE , PRL.RATE_TYPE , PRL.RATE_DATE , PRL.CURRENCY_UNIT_PRICE , PRL.SUGGESTED_BUYER_ID , PO_INQ_SV.GET_PERSON_NAME(PRL.SUGGESTED_BUYER_ID) , PRL.CLOSED_CODE , PRL.LINE_LOCATION_ID , NULL , MCA.CONCATENATED_SEGMENTS , PRL.PARENT_REQ_LINE_ID , PRL.PURCHASING_AGENT_ID , PRL.DOCUMENT_TYPE_CODE , PRL.BLANKET_PO_HEADER_ID , PRL.BLANKET_PO_LINE_NUM , PRL.CANCEL_REASON , PRL.CANCEL_DATE , PRL.AGENT_RETURN_NOTE , PRL.CHANGED_AFTER_RESEARCH_FLAG , PRL.VENDOR_ID , PRL.VENDOR_SITE_ID , PRL.VENDOR_CONTACT_ID , PRL.RESEARCH_AGENT_ID , PRL.ON_LINE_FLAG , PRL.DESTINATION_CONTEXT , PRL.INVENTORY_SOURCE_CONTEXT , PRL.VENDOR_SOURCE_CONTEXT , PRL.BOM_RESOURCE_ID , PRL.USSGL_TRANSACTION_CODE , PRL.GOVERNMENT_CONTEXT , PRL.CLOSED_REASON , PRL.CLOSED_DATE , PRL.TRANSACTION_REASON_CODE , PRL.SOURCE_REQ_LINE_ID , PRL.DESTINATION_ORGANIZATION_ID , PRL.SOURCE_ORGANIZATION_ID , PO_INQ_SV.GET_PERSON_NAME(PRL.PURCHASING_AGENT_ID) , PLT.ORDER_TYPE_LOOKUP_CODE , GDT.USER_CONVERSION_TYPE , PLC_DEST.DISPLAYED_FIELD , PLC_STYPE.DISPLAYED_FIELD , PLC_SDOC.DISPLAYED_FIELD , POLA.CUSTOMER_ID , POLA.ADDRESS_ID , POLA.SITE_USE_ID , PO_REQS_INQ_SV.GET_PO_NUMBER(PRL.BLANKET_PO_HEADER_ID) , DECODE(PRL.SOURCE_TYPE_CODE, 'VENDOR', PO_INQ_SV.GET_PO_NUMBER(PRL.LINE_LOCATION_ID), PO_INQ_SV.GET_SO_NUMBER(PRH.SEGMENT1,PRL.LINE_NUM)) , POL.ATTRIBUTE14 , POV.ATTRIBUTE14 , POV.VENDOR_NAME FROM GL_DAILY_CONVERSION_TYPES GDT , FINANCIALS_SYSTEM_PARAMETERS FSP , PO_LINE_TYPES PLT , PO_LOOKUP_CODES PLC_TRSN , PO_LOOKUP_CODES PLC_SDOC , PO_LOOKUP_CODES PLC_STYPE , PO_LOOKUP_CODES PLC_DEST , PO_UN_NUMBERS POUN , PO_HAZARD_CLASSES PHC , PO_LOCATION_ASSOCIATIONS POLA , HR_LOCATIONS HRL1 , ORG_ORGANIZATION_DEFINITIONS OOD1 , HR_ORGANIZATION_UNITS HOU , MTL_CATEGORIES_KFV MCA , PO_VENDORS POV , PO_LINES POL , PO_REQUISITION_HEADERS PRH , PO_REQUISITION_LINES PRL , GL_SETS_OF_BOOKS GSOB WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID AND POUN.UN_NUMBER_ID (+) = PRL.UN_NUMBER_ID AND PHC.HAZARD_CLASS_ID (+) = PRL.HAZARD_CLASS_ID AND OOD1.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID AND HRL1.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID AND PRL.ITEM_ID IS NULL AND MCA.CATEGORY_ID = PRL.CATEGORY_ID AND POLA.LOCATION_ID (+) = PRL.DELIVER_TO_LOCATION_ID AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE AND HOU.ORGANIZATION_ID (+) = PRL.SOURCE_ORGANIZATION_ID AND PLC_TRSN.LOOKUP_TYPE (+) = 'TRANSACTION_REASON' AND PLC_TRSN.LOOKUP_CODE (+) = PRL.TRANSACTION_REASON_CODE AND PLC_SDOC.LOOKUP_TYPE (+) = 'SOURCE DOCUMENT TYPE' AND PLC_SDOC.LOOKUP_CODE (+) = PRL.DOCUMENT_TYPE_CODE AND PLC_STYPE.LOOKUP_TYPE = 'REQUISITION SOURCE TYPE' AND PLC_STYPE.LOOKUP_CODE = PRL.SOURCE_TYPE_CODE AND PLC_DEST.LOOKUP_TYPE = 'DESTINATION TYPE' AND PLC_DEST.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE AND POL.PO_HEADER_ID (+) = PRL.BLANKET_PO_HEADER_ID AND POL.LINE_NUM (+) = PRL.BLANKET_PO_LINE_NUM AND POV.VENDOR_ID (+) = PRL.VENDOR_ID AND GSOB.SET_OF_BOOKS_ID = OOD1.SET_OF_BOOKS_ID

View Text - HTML Formatted

SELECT PRL.ROWID
, PRL.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PLT.LINE_TYPE
, PLT.LINE_TYPE_ID
, PRL.ITEM_ID
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, PRL.ITEM_DESCRIPTION
, PRL.QUANTITY
, NVL(PRL.QUANTITY_RECEIVED
, 0)
, NVL(PRL.QUANTITY_DELIVERED
, 0)
, NVL(PRL.QUANTITY_CANCELLED
, 0)
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.UNIT_PRICE
, TO_CHAR ( PRL.UNIT_PRICE
, FND_CURRENCY.GET_FORMAT_MASK( GSOB.CURRENCY_CODE
, 30 ) ) DISPLAY_UNIT_PRICE
, PRL.NEED_BY_DATE
, PRL.NOTE_TO_AGENT
, PRL.SUGGESTED_VENDOR_NAME
, PRL.SUGGESTED_VENDOR_LOCATION
, PRL.SUGGESTED_VENDOR_CONTACT
, PRL.SUGGESTED_VENDOR_PHONE
, PRL.RFQ_REQUIRED_FLAG
, PRL.ON_RFQ_FLAG
, PRL.CANCEL_FLAG
, PRL.MODIFIED_BY_AGENT_FLAG
, POUN.UN_NUMBER
, PHC.HAZARD_CLASS
, PLC_TRSN.DISPLAYED_FIELD
, PRL.REFERENCE_NUM
, PRL.URGENT_FLAG
, PRL.JUSTIFICATION
, PRL.NOTE_TO_RECEIVER
, PRL.ENCUMBERED_FLAG
, PRL.DESTINATION_TYPE_CODE
, PRL.SOURCE_TYPE_CODE
, OOD1.ORGANIZATION_NAME
, HRL1.LOCATION_CODE
, PRL.DESTINATION_SUBINVENTORY
, HOU.NAME
, PRL.SOURCE_SUBINVENTORY
, PRL.ATTRIBUTE1
, PRL.ATTRIBUTE2
, PRL.ATTRIBUTE3
, PRL.ATTRIBUTE4
, PRL.ATTRIBUTE5
, PRL.ATTRIBUTE6
, PRL.ATTRIBUTE7
, PRL.ATTRIBUTE8
, PRL.ATTRIBUTE9
, PRL.ATTRIBUTE10
, PRL.ATTRIBUTE11
, PRL.ATTRIBUTE12
, PRL.ATTRIBUTE13
, PRL.ATTRIBUTE14
, PRL.ATTRIBUTE15
, PRL.ATTRIBUTE_CATEGORY
, NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')
, PRL.DELIVER_TO_LOCATION_ID
, PRL.TO_PERSON_ID
, PO_INQ_SV.GET_PERSON_NAME(TO_PERSON_ID)
, PRL.LAST_UPDATE_DATE
, PRL.LAST_UPDATED_BY
, PRL.LAST_UPDATE_LOGIN
, PRL.CREATION_DATE
, PRL.CREATED_BY
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.UN_NUMBER_ID
, PRL.HAZARD_CLASS_ID
, PRL.CURRENCY_CODE
, PRL.RATE
, PRL.RATE_TYPE
, PRL.RATE_DATE
, PRL.CURRENCY_UNIT_PRICE
, PRL.SUGGESTED_BUYER_ID
, PO_INQ_SV.GET_PERSON_NAME(PRL.SUGGESTED_BUYER_ID)
, PRL.CLOSED_CODE
, PRL.LINE_LOCATION_ID
, MSI.CONCATENATED_SEGMENTS
, MCA.CONCATENATED_SEGMENTS
, PRL.PARENT_REQ_LINE_ID
, PRL.PURCHASING_AGENT_ID
, PRL.DOCUMENT_TYPE_CODE
, PRL.BLANKET_PO_HEADER_ID
, PRL.BLANKET_PO_LINE_NUM
, PRL.CANCEL_REASON
, PRL.CANCEL_DATE
, PRL.AGENT_RETURN_NOTE
, PRL.CHANGED_AFTER_RESEARCH_FLAG
, PRL.VENDOR_ID
, PRL.VENDOR_SITE_ID
, PRL.VENDOR_CONTACT_ID
, PRL.RESEARCH_AGENT_ID
, PRL.ON_LINE_FLAG
, PRL.DESTINATION_CONTEXT
, PRL.INVENTORY_SOURCE_CONTEXT
, PRL.VENDOR_SOURCE_CONTEXT
, PRL.BOM_RESOURCE_ID
, PRL.USSGL_TRANSACTION_CODE
, PRL.GOVERNMENT_CONTEXT
, PRL.CLOSED_REASON
, PRL.CLOSED_DATE
, PRL.TRANSACTION_REASON_CODE
, PRL.SOURCE_REQ_LINE_ID
, PRL.DESTINATION_ORGANIZATION_ID
, PRL.SOURCE_ORGANIZATION_ID
, PO_INQ_SV.GET_PERSON_NAME(PRL.PURCHASING_AGENT_ID)
, PLT.ORDER_TYPE_LOOKUP_CODE
, GDT.USER_CONVERSION_TYPE
, PLC_DEST.DISPLAYED_FIELD
, PLC_STYPE.DISPLAYED_FIELD
, PLC_SDOC.DISPLAYED_FIELD
, POLA.CUSTOMER_ID
, POLA.ADDRESS_ID
, POLA.SITE_USE_ID
, PO_REQS_INQ_SV.GET_PO_NUMBER(PRL.BLANKET_PO_HEADER_ID)
, DECODE(PRL.SOURCE_TYPE_CODE
, 'VENDOR'
, PO_INQ_SV.GET_PO_NUMBER(PRL.LINE_LOCATION_ID)
, PO_INQ_SV.GET_SO_NUMBER(PRH.SEGMENT1
, PRL.LINE_NUM))
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, POV.VENDOR_NAME
FROM GL_DAILY_CONVERSION_TYPES GDT
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LINE_TYPES PLT
, PO_LOOKUP_CODES PLC_TRSN
, PO_LOOKUP_CODES PLC_SDOC
, PO_LOOKUP_CODES PLC_STYPE
, PO_LOOKUP_CODES PLC_DEST
, PO_UN_NUMBERS POUN
, PO_HAZARD_CLASSES PHC
, PO_LOCATION_ASSOCIATIONS POLA
, HR_LOCATIONS HRL1
, ORG_ORGANIZATION_DEFINITIONS OOD1
, HR_ORGANIZATION_UNITS HOU
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_CATEGORIES_KFV MCA
, PO_VENDORS POV
, PO_LINES POL
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, GL_SETS_OF_BOOKS GSOB
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID
AND POUN.UN_NUMBER_ID (+) = PRL.UN_NUMBER_ID
AND PHC.HAZARD_CLASS_ID (+) = PRL.HAZARD_CLASS_ID
AND OOD1.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID
AND PRL.ITEM_ID IS NOT NULL
AND MSI.INVENTORY_ITEM_ID = PRL.ITEM_ID
AND MSI.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND MCA.CATEGORY_ID = PRL.CATEGORY_ID
AND POLA.LOCATION_ID (+) = PRL.DELIVER_TO_LOCATION_ID
AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE
AND HOU.ORGANIZATION_ID (+) = PRL.SOURCE_ORGANIZATION_ID
AND PLC_TRSN.LOOKUP_TYPE (+) = 'TRANSACTION_REASON'
AND PLC_TRSN.LOOKUP_CODE (+) = PRL.TRANSACTION_REASON_CODE
AND PLC_SDOC.LOOKUP_TYPE (+) = 'SOURCE DOCUMENT TYPE'
AND PLC_SDOC.LOOKUP_CODE (+) = PRL.DOCUMENT_TYPE_CODE
AND PLC_STYPE.LOOKUP_TYPE = 'REQUISITION SOURCE TYPE'
AND PLC_STYPE.LOOKUP_CODE = PRL.SOURCE_TYPE_CODE
AND PLC_DEST.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC_DEST.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE
AND POL.PO_HEADER_ID (+) = PRL.BLANKET_PO_HEADER_ID
AND POL.LINE_NUM (+) = PRL.BLANKET_PO_LINE_NUM
AND POV.VENDOR_ID (+) = PRL.VENDOR_ID
AND GSOB.SET_OF_BOOKS_ID = OOD1.SET_OF_BOOKS_ID UNION SELECT PRL.ROWID
, PRL.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PLT.LINE_TYPE
, PLT.LINE_TYPE_ID
, PRL.ITEM_ID
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, PRL.ITEM_DESCRIPTION
, PRL.QUANTITY
, NVL(PRL.QUANTITY_RECEIVED
, 0)
, NVL(PRL.QUANTITY_DELIVERED
, 0)
, NVL(PRL.QUANTITY_CANCELLED
, 0)
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.UNIT_PRICE
, TO_CHAR (PRL.UNIT_PRICE
, FND_CURRENCY.GET_FORMAT_MASK(GSOB.CURRENCY_CODE
, 30 ) ) DISPLAY_UNIT_PRICE
, PRL.NEED_BY_DATE
, PRL.NOTE_TO_AGENT
, PRL.SUGGESTED_VENDOR_NAME
, PRL.SUGGESTED_VENDOR_LOCATION
, PRL.SUGGESTED_VENDOR_CONTACT
, PRL.SUGGESTED_VENDOR_PHONE
, PRL.RFQ_REQUIRED_FLAG
, PRL.ON_RFQ_FLAG
, PRL.CANCEL_FLAG
, PRL.MODIFIED_BY_AGENT_FLAG
, POUN.UN_NUMBER
, PHC.HAZARD_CLASS
, PLC_TRSN.DISPLAYED_FIELD
, PRL.REFERENCE_NUM
, PRL.URGENT_FLAG
, PRL.JUSTIFICATION
, PRL.NOTE_TO_RECEIVER
, PRL.ENCUMBERED_FLAG
, PRL.DESTINATION_TYPE_CODE
, PRL.SOURCE_TYPE_CODE
, OOD1.ORGANIZATION_NAME
, HRL1.LOCATION_CODE
, PRL.DESTINATION_SUBINVENTORY
, HOU.NAME
, PRL.SOURCE_SUBINVENTORY
, PRL.ATTRIBUTE1
, PRL.ATTRIBUTE2
, PRL.ATTRIBUTE3
, PRL.ATTRIBUTE4
, PRL.ATTRIBUTE5
, PRL.ATTRIBUTE6
, PRL.ATTRIBUTE7
, PRL.ATTRIBUTE8
, PRL.ATTRIBUTE9
, PRL.ATTRIBUTE10
, PRL.ATTRIBUTE11
, PRL.ATTRIBUTE12
, PRL.ATTRIBUTE13
, PRL.ATTRIBUTE14
, PRL.ATTRIBUTE15
, PRL.ATTRIBUTE_CATEGORY
, NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')
, PRL.DELIVER_TO_LOCATION_ID
, PRL.TO_PERSON_ID
, PO_INQ_SV.GET_PERSON_NAME(TO_PERSON_ID)
, PRL.LAST_UPDATE_DATE
, PRL.LAST_UPDATED_BY
, PRL.LAST_UPDATE_LOGIN
, PRL.CREATION_DATE
, PRL.CREATED_BY
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.UN_NUMBER_ID
, PRL.HAZARD_CLASS_ID
, PRL.CURRENCY_CODE
, PRL.RATE
, PRL.RATE_TYPE
, PRL.RATE_DATE
, PRL.CURRENCY_UNIT_PRICE
, PRL.SUGGESTED_BUYER_ID
, PO_INQ_SV.GET_PERSON_NAME(PRL.SUGGESTED_BUYER_ID)
, PRL.CLOSED_CODE
, PRL.LINE_LOCATION_ID
, NULL
, MCA.CONCATENATED_SEGMENTS
, PRL.PARENT_REQ_LINE_ID
, PRL.PURCHASING_AGENT_ID
, PRL.DOCUMENT_TYPE_CODE
, PRL.BLANKET_PO_HEADER_ID
, PRL.BLANKET_PO_LINE_NUM
, PRL.CANCEL_REASON
, PRL.CANCEL_DATE
, PRL.AGENT_RETURN_NOTE
, PRL.CHANGED_AFTER_RESEARCH_FLAG
, PRL.VENDOR_ID
, PRL.VENDOR_SITE_ID
, PRL.VENDOR_CONTACT_ID
, PRL.RESEARCH_AGENT_ID
, PRL.ON_LINE_FLAG
, PRL.DESTINATION_CONTEXT
, PRL.INVENTORY_SOURCE_CONTEXT
, PRL.VENDOR_SOURCE_CONTEXT
, PRL.BOM_RESOURCE_ID
, PRL.USSGL_TRANSACTION_CODE
, PRL.GOVERNMENT_CONTEXT
, PRL.CLOSED_REASON
, PRL.CLOSED_DATE
, PRL.TRANSACTION_REASON_CODE
, PRL.SOURCE_REQ_LINE_ID
, PRL.DESTINATION_ORGANIZATION_ID
, PRL.SOURCE_ORGANIZATION_ID
, PO_INQ_SV.GET_PERSON_NAME(PRL.PURCHASING_AGENT_ID)
, PLT.ORDER_TYPE_LOOKUP_CODE
, GDT.USER_CONVERSION_TYPE
, PLC_DEST.DISPLAYED_FIELD
, PLC_STYPE.DISPLAYED_FIELD
, PLC_SDOC.DISPLAYED_FIELD
, POLA.CUSTOMER_ID
, POLA.ADDRESS_ID
, POLA.SITE_USE_ID
, PO_REQS_INQ_SV.GET_PO_NUMBER(PRL.BLANKET_PO_HEADER_ID)
, DECODE(PRL.SOURCE_TYPE_CODE
, 'VENDOR'
, PO_INQ_SV.GET_PO_NUMBER(PRL.LINE_LOCATION_ID)
, PO_INQ_SV.GET_SO_NUMBER(PRH.SEGMENT1
, PRL.LINE_NUM))
, POL.ATTRIBUTE14
, POV.ATTRIBUTE14
, POV.VENDOR_NAME
FROM GL_DAILY_CONVERSION_TYPES GDT
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LINE_TYPES PLT
, PO_LOOKUP_CODES PLC_TRSN
, PO_LOOKUP_CODES PLC_SDOC
, PO_LOOKUP_CODES PLC_STYPE
, PO_LOOKUP_CODES PLC_DEST
, PO_UN_NUMBERS POUN
, PO_HAZARD_CLASSES PHC
, PO_LOCATION_ASSOCIATIONS POLA
, HR_LOCATIONS HRL1
, ORG_ORGANIZATION_DEFINITIONS OOD1
, HR_ORGANIZATION_UNITS HOU
, MTL_CATEGORIES_KFV MCA
, PO_VENDORS POV
, PO_LINES POL
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, GL_SETS_OF_BOOKS GSOB
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID
AND POUN.UN_NUMBER_ID (+) = PRL.UN_NUMBER_ID
AND PHC.HAZARD_CLASS_ID (+) = PRL.HAZARD_CLASS_ID
AND OOD1.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID
AND PRL.ITEM_ID IS NULL
AND MCA.CATEGORY_ID = PRL.CATEGORY_ID
AND POLA.LOCATION_ID (+) = PRL.DELIVER_TO_LOCATION_ID
AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE
AND HOU.ORGANIZATION_ID (+) = PRL.SOURCE_ORGANIZATION_ID
AND PLC_TRSN.LOOKUP_TYPE (+) = 'TRANSACTION_REASON'
AND PLC_TRSN.LOOKUP_CODE (+) = PRL.TRANSACTION_REASON_CODE
AND PLC_SDOC.LOOKUP_TYPE (+) = 'SOURCE DOCUMENT TYPE'
AND PLC_SDOC.LOOKUP_CODE (+) = PRL.DOCUMENT_TYPE_CODE
AND PLC_STYPE.LOOKUP_TYPE = 'REQUISITION SOURCE TYPE'
AND PLC_STYPE.LOOKUP_CODE = PRL.SOURCE_TYPE_CODE
AND PLC_DEST.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC_DEST.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE
AND POL.PO_HEADER_ID (+) = PRL.BLANKET_PO_HEADER_ID
AND POL.LINE_NUM (+) = PRL.BLANKET_PO_LINE_NUM
AND POV.VENDOR_ID (+) = PRL.VENDOR_ID
AND GSOB.SET_OF_BOOKS_ID = OOD1.SET_OF_BOOKS_ID