VIEW: APPS.ICX_PO_REQ_LINES_DIST_V

Source

View Text - Preformatted

SELECT PRL.REQUISITION_HEADER_ID , PRL.REQUISITION_LINE_ID , ICX_GET.GET_ACTION_HISTORY_DATE(PRL.REQUISITION_HEADER_ID, 'REQUISITION', PRH.TYPE_LOOKUP_CODE, 'APPROVE') , PRL.QUANTITY*PRL.UNIT_PRICE , PRD.DISTRIBUTION_ID , PRD.REQ_LINE_QUANTITY , PRD.CODE_COMBINATION_ID , PRD.ENCUMBERED_FLAG , PRD.GL_ENCUMBERED_DATE , PRD.GL_ENCUMBERED_PERIOD_NAME , PRD.GL_CANCELLED_DATE , PRD.FAILED_FUNDS_LOOKUP_CODE , PRD.ENCUMBERED_AMOUNT , PRD.BUDGET_ACCOUNT_ID , PRD.ACCRUAL_ACCOUNT_ID , PRD.VARIANCE_ACCOUNT_ID , PRD.PREVENT_ENCUMBRANCE_FLAG , PRD.DISTRIBUTION_NUM , PRD.PROJECT_ID , PRD.TASK_ID , PRD.EXPENDITURE_TYPE , PRD.PROJECT_ACCOUNTING_CONTEXT , PRD.EXPENDITURE_ORGANIZATION_ID , PRD.GL_CLOSED_DATE , PRD.SOURCE_REQ_DISTRIBUTION_ID , PRD.PROJECT_RELATED_FLAG , PRD.EXPENDITURE_ITEM_DATE , PRD.ORG_ID , PRD.ALLOCATION_TYPE , PRD.ALLOCATION_VALUE , GCC.CONCATENATED_SEGMENTS , GCC.SEGMENT1 , GCC.SEGMENT2 , GCC.SEGMENT3 , GCC.SEGMENT4 , GCC.SEGMENT5 , GCC.SEGMENT6 , GCC.SEGMENT7 , GCC.SEGMENT8 , GCC.SEGMENT9 , GCC.SEGMENT10 , GCC.SEGMENT11 , GCC.SEGMENT12 , GCC.SEGMENT13 , GCC.SEGMENT14 , GCC.SEGMENT15 , GCC.SEGMENT16 , GCC.SEGMENT17 , GCC.SEGMENT18 , GCC.SEGMENT19 , GCC.SEGMENT20 , GCC.SEGMENT21 , GCC.SEGMENT22 , GCC.SEGMENT23 , GCC.SEGMENT24 , GCC.SEGMENT25 , GCC.SEGMENT26 , GCC.SEGMENT27 , GCC.SEGMENT28 , GCC.SEGMENT29 , GCC.SEGMENT30 , ICX_GET.GET_GL_VALUE(101,'GL#',101,PRD.CODE_COMBINATION_ID, 'COST CENTER') , ICX_GET.GET_GL_VALUE(101,'GL#',101,PRD.CODE_COMBINATION_ID, 'ACCOUNT') , ICX_GET.GET_GL_VALUE(101,'GL#',101,PRD.CODE_COMBINATION_ID, 'COMPANY') , 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 , TO_CHAR ( PRL.UNIT_PRICE, FND_CURRENCY.GET_FORMAT_MASK( GSOB.CURRENCY_CODE, 30 ) ) DISPLAY_UNIT_PRICE , PRL.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.CANCEL_FLAG , PRL.MODIFIED_BY_AGENT_FLAG , PRL.REFERENCE_NUM , PRL.JUSTIFICATION , PRL.NOTE_TO_RECEIVER , OOD1.ORGANIZATION_NAME , HRL1.LOCATION_CODE , PRL.DESTINATION_SUBINVENTORY , PRL.SOURCE_SUBINVENTORY , 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.CURRENCY_CODE , PRL.RATE , 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 , ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID) , MCA.CONCATENATED_SEGMENTS , PRL.CANCEL_REASON , PRL.CANCEL_DATE , PRL.VENDOR_ID , PRL.CLOSED_REASON , PRL.CLOSED_DATE , PO_INQ_SV.GET_PERSON_NAME(PRL.PURCHASING_AGENT_ID) , GDT.USER_CONVERSION_TYPE , PLC_DEST.DISPLAYED_FIELD , PLC_STYPE.DISPLAYED_FIELD , PRH.SEGMENT1 , PRH.PREPARER_ID , PO_INQ_SV.GET_PERSON_NAME(PRH.PREPARER_ID) , PRH.AUTHORIZATION_STATUS , PLC_STTS.DISPLAYED_FIELD , PRH.TYPE_LOOKUP_CODE , PODT.TYPE_NAME , PRH.INTERFACE_SOURCE_CODE , 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 FROM GL_DAILY_CONVERSION_TYPES GDT , PO_LINE_TYPES PLT , PO_DOCUMENT_TYPES PODT , PO_LOOKUP_CODES PLC_STTS , PO_LOOKUP_CODES PLC_STYPE , PO_LOOKUP_CODES PLC_DEST , HR_LOCATIONS HRL1 , ORG_ORGANIZATION_DEFINITIONS OOD1 , MTL_SYSTEM_ITEMS MSI , MTL_CATEGORIES_KFV MCA , PO_VENDORS POV , PO_LINES POL , PO_REQUISITION_HEADERS PRH , PO_REQUISITION_LINES PRL , GL_SETS_OF_BOOKS GSOB , PO_REQ_DISTRIBUTIONS PRD , GL_CODE_COMBINATIONS_KFV GCC WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND NVL(PRH.CANCEL_FLAG, 'N') = 'N' AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID AND OOD1.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID AND HRL1.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND PRL.DESTINATION_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID,PRL.DESTINATION_ORGANIZATION_ID) AND MCA.CATEGORY_ID = PRL.CATEGORY_ID AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE AND PODT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE AND PODT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND PLC_STTS.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND PLC_STTS.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS, 'INCOMPLETE') 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 PRL.QUANTITY > PRL.QUANTITY_DELIVERED 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 AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND PRD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND NVL(PRD.ALLOCATION_TYPE, 'PERCENT') = 'PERCENT'

View Text - HTML Formatted

SELECT PRL.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, ICX_GET.GET_ACTION_HISTORY_DATE(PRL.REQUISITION_HEADER_ID
, 'REQUISITION'
, PRH.TYPE_LOOKUP_CODE
, 'APPROVE')
, PRL.QUANTITY*PRL.UNIT_PRICE
, PRD.DISTRIBUTION_ID
, PRD.REQ_LINE_QUANTITY
, PRD.CODE_COMBINATION_ID
, PRD.ENCUMBERED_FLAG
, PRD.GL_ENCUMBERED_DATE
, PRD.GL_ENCUMBERED_PERIOD_NAME
, PRD.GL_CANCELLED_DATE
, PRD.FAILED_FUNDS_LOOKUP_CODE
, PRD.ENCUMBERED_AMOUNT
, PRD.BUDGET_ACCOUNT_ID
, PRD.ACCRUAL_ACCOUNT_ID
, PRD.VARIANCE_ACCOUNT_ID
, PRD.PREVENT_ENCUMBRANCE_FLAG
, PRD.DISTRIBUTION_NUM
, PRD.PROJECT_ID
, PRD.TASK_ID
, PRD.EXPENDITURE_TYPE
, PRD.PROJECT_ACCOUNTING_CONTEXT
, PRD.EXPENDITURE_ORGANIZATION_ID
, PRD.GL_CLOSED_DATE
, PRD.SOURCE_REQ_DISTRIBUTION_ID
, PRD.PROJECT_RELATED_FLAG
, PRD.EXPENDITURE_ITEM_DATE
, PRD.ORG_ID
, PRD.ALLOCATION_TYPE
, PRD.ALLOCATION_VALUE
, GCC.CONCATENATED_SEGMENTS
, GCC.SEGMENT1
, GCC.SEGMENT2
, GCC.SEGMENT3
, GCC.SEGMENT4
, GCC.SEGMENT5
, GCC.SEGMENT6
, GCC.SEGMENT7
, GCC.SEGMENT8
, GCC.SEGMENT9
, GCC.SEGMENT10
, GCC.SEGMENT11
, GCC.SEGMENT12
, GCC.SEGMENT13
, GCC.SEGMENT14
, GCC.SEGMENT15
, GCC.SEGMENT16
, GCC.SEGMENT17
, GCC.SEGMENT18
, GCC.SEGMENT19
, GCC.SEGMENT20
, GCC.SEGMENT21
, GCC.SEGMENT22
, GCC.SEGMENT23
, GCC.SEGMENT24
, GCC.SEGMENT25
, GCC.SEGMENT26
, GCC.SEGMENT27
, GCC.SEGMENT28
, GCC.SEGMENT29
, GCC.SEGMENT30
, ICX_GET.GET_GL_VALUE(101
, 'GL#'
, 101
, PRD.CODE_COMBINATION_ID
, 'COST CENTER')
, ICX_GET.GET_GL_VALUE(101
, 'GL#'
, 101
, PRD.CODE_COMBINATION_ID
, 'ACCOUNT')
, ICX_GET.GET_GL_VALUE(101
, 'GL#'
, 101
, PRD.CODE_COMBINATION_ID
, 'COMPANY')
, 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
, TO_CHAR ( PRL.UNIT_PRICE
, FND_CURRENCY.GET_FORMAT_MASK( GSOB.CURRENCY_CODE
, 30 ) ) DISPLAY_UNIT_PRICE
, PRL.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.CANCEL_FLAG
, PRL.MODIFIED_BY_AGENT_FLAG
, PRL.REFERENCE_NUM
, PRL.JUSTIFICATION
, PRL.NOTE_TO_RECEIVER
, OOD1.ORGANIZATION_NAME
, HRL1.LOCATION_CODE
, PRL.DESTINATION_SUBINVENTORY
, PRL.SOURCE_SUBINVENTORY
, 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.CURRENCY_CODE
, PRL.RATE
, 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
, ICX_UTIL.ITEM_FLEX_SEG(MSI.ROWID)
, MCA.CONCATENATED_SEGMENTS
, PRL.CANCEL_REASON
, PRL.CANCEL_DATE
, PRL.VENDOR_ID
, PRL.CLOSED_REASON
, PRL.CLOSED_DATE
, PO_INQ_SV.GET_PERSON_NAME(PRL.PURCHASING_AGENT_ID)
, GDT.USER_CONVERSION_TYPE
, PLC_DEST.DISPLAYED_FIELD
, PLC_STYPE.DISPLAYED_FIELD
, PRH.SEGMENT1
, PRH.PREPARER_ID
, PO_INQ_SV.GET_PERSON_NAME(PRH.PREPARER_ID)
, PRH.AUTHORIZATION_STATUS
, PLC_STTS.DISPLAYED_FIELD
, PRH.TYPE_LOOKUP_CODE
, PODT.TYPE_NAME
, PRH.INTERFACE_SOURCE_CODE
, 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
FROM GL_DAILY_CONVERSION_TYPES GDT
, PO_LINE_TYPES PLT
, PO_DOCUMENT_TYPES PODT
, PO_LOOKUP_CODES PLC_STTS
, PO_LOOKUP_CODES PLC_STYPE
, PO_LOOKUP_CODES PLC_DEST
, HR_LOCATIONS HRL1
, ORG_ORGANIZATION_DEFINITIONS OOD1
, MTL_SYSTEM_ITEMS MSI
, MTL_CATEGORIES_KFV MCA
, PO_VENDORS POV
, PO_LINES POL
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, GL_SETS_OF_BOOKS GSOB
, PO_REQ_DISTRIBUTIONS PRD
, GL_CODE_COMBINATIONS_KFV GCC
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND NVL(PRH.CANCEL_FLAG
, 'N') = 'N'
AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID
AND OOD1.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PRL.DESTINATION_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, PRL.DESTINATION_ORGANIZATION_ID)
AND MCA.CATEGORY_ID = PRL.CATEGORY_ID
AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE
AND PODT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE
AND PODT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PLC_STTS.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PLC_STTS.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
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 PRL.QUANTITY > PRL.QUANTITY_DELIVERED
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
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND NVL(PRD.ALLOCATION_TYPE
, 'PERCENT') = 'PERCENT'