VIEW: APPS.RCV_AEL_GL_V

Source

View Text - Preformatted

SELECT /*+ ordered use_nl(R, RRS, POD, PLL, POL, POH, PR, POV, PVS, RCT, OOD, RSH, PL4, PL3, PL2, PL1, GLCT) */ 201 , RRS.SET_OF_BOOKS_ID , POH.ORG_ID , OOD.ORGANIZATION_CODE , RCT.ORGANIZATION_ID , PL3.DISPLAYED_FIELD , 'RCV TRX' , PL2.DISPLAYED_FIELD , RCT.TRANSACTION_TYPE , RCT.TRANSACTION_DATE , RCT.COMMENTS , 'RCT' , RCT.TRANSACTION_ID , RRS.CODE_COMBINATION_ID , RRS.CURRENCY_CODE , RRS.ENTERED_DR , RRS.ENTERED_CR , RRS.ACCOUNTED_DR , RRS.ACCOUNTED_CR , RRS.CURRENCY_CONVERSION_DATE , RCT.CURRENCY_CONVERSION_TYPE , GLCT.USER_CONVERSION_TYPE , RRS.CURRENCY_CONVERSION_RATE , 'S' , POV.VENDOR_ID , POV.SEGMENT1 , POV.VENDOR_NAME , POH.VENDOR_SITE_ID , PVS.VENDOR_SITE_CODE , RRS.ACCOUNTING_DATE , PL1.DISPLAYED_FIELD , 'Y' , 'RCT' , RCT.TRANSACTION_ID , 'RSL' , PL2.DISPLAYED_FIELD , RPAD(PL2.DISPLAYED_FIELD, 80) , RSH.RECEIPT_NUM , RSH.SHIPMENT_HEADER_ID , POH.SEGMENT1 , PL4.DISPLAYED_FIELD , POL.LINE_NUM , RSL.ITEM_ID , RSL.ITEM_REVISION , RSL.ITEM_DESCRIPTION , RCT.SOURCE_DOC_UNIT_OF_MEASURE , NVL(RRS.SOURCE_DOC_QUANTITY, NVL(RCT.SOURCE_DOC_QUANTITY, 0)) , NVL(PLL.PRICE_OVERRIDE, POL.UNIT_PRICE) , PR.RELEASE_NUM , RCT.SUBINVENTORY , R.JE_HEADER_ID , R.JE_LINE_NUM , RRS.LAST_UPDATE_DATE , RRS.LAST_UPDATED_BY , RRS.CREATION_DATE , RRS.CREATED_BY , RRS.LAST_UPDATE_LOGIN , RRS.REQUEST_ID , RRS.PROGRAM_APPLICATION_ID , RRS.PROGRAM_ID , RRS.PROGRAM_UPDATE_DATE , PLL.SHIPMENT_NUM , POD.DISTRIBUTION_NUM , RRS.JE_CATEGORY_NAME , GJC.USER_JE_CATEGORY_NAME FROM GL_IMPORT_REFERENCES R, RCV_RECEIVING_SUB_LEDGER RRS, RCV_TRANSACTIONS RCT, ORG_ORGANIZATION_DEFINITIONS OOD, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL POL, PO_HEADERS_ALL POH, PO_RELEASES_ALL PR, PO_VENDORS POV, PO_VENDOR_SITES_ALL PVS, PO_LOOKUP_CODES PL4, PO_LOOKUP_CODES PL3, PO_LOOKUP_CODES PL2, PO_LOOKUP_CODES PL1, GL_DAILY_CONVERSION_TYPES GLCT, GL_JE_CATEGORIES GJC, GL_JE_HEADERS GJH WHERE PL1.LOOKUP_CODE = 'Y' AND PL1.LOOKUP_TYPE = 'YES/NO' AND PL3.LOOKUP_CODE = 'RCV TRANSACTION' AND PL3.LOOKUP_TYPE = 'VIEW ACCOUNTING' AND PL4.LOOKUP_CODE = POH.TYPE_LOOKUP_CODE AND PL4.LOOKUP_TYPE = 'PO TYPE' AND POV.VENDOR_ID = POH.VENDOR_ID AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND NVL(PVS.ORG_ID, -99) = NVL(POH.ORG_ID, -99) AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = PLL.PO_LINE_ID AND PR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3 AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID AND PL2.LOOKUP_CODE = RCT.TRANSACTION_TYPE AND PL2.LOOKUP_TYPE = 'RCV TRANSACTION TYPE' AND OOD.ORGANIZATION_ID = RCT.ORGANIZATION_ID AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID AND GLCT.CONVERSION_TYPE(+) = RCT.CURRENCY_CONVERSION_TYPE AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID AND R.GL_SL_LINK_TABLE = 'RSL' AND RRS.GL_SL_LINK_ID = R.GL_SL_LINK_ID AND RRS.RCV_TRANSACTION_ID = R.REFERENCE_5 AND GJC.JE_CATEGORY_NAME = RRS.JE_CATEGORY_NAME AND GJH.JE_HEADER_ID= R.JE_HEADER_ID AND GJH.ACTUAL_FLAG = RRS.ACTUAL_FLAG AND RRS.SET_OF_BOOKS_ID = GJH.LEDGER_ID UNION ALL SELECT 201, RRS.SET_OF_BOOKS_ID, POH.ORG_ID, OOD.ORGANIZATION_CODE, RCT.ORGANIZATION_ID, PL3.DISPLAYED_FIELD, 'RCV       TRX' , PL2.DISPLAYED_FIELD, RCT.TRANSACTION_TYPE, RCT.TRANSACTION_DATE, RCT.COMMENTS, 'RCT', RCT.TRANSACTION_ID, RRS.CODE_COMBINATION_ID, RRS.CURRENCY_CODE, RRS.ENTERED_DR, RRS.ENTERED_CR, RRS.ACCOUNTED_DR, RRS.ACCOUNTED_CR, RRS.CURRENCY_CONVERSION_DATE, RCT.CURRENCY_CONVERSION_TYPE, GLCT.USER_CONVERSION_TYPE, RRS.CURRENCY_CONVERSION_RATE, 'S', POV.VENDOR_ID, POV.SEGMENT1, POV.VENDOR_NAME, POH.VENDOR_SITE_ID, PVS.VENDOR_SITE_CODE, RRS.ACCOUNTING_DATE, PL1.DISPLAYED_FIELD, 'Y', 'RCT', RCT.TRANSACTION_ID, 'RSL', PL2.DISPLAYED_FIELD, RPAD(PL2.DISPLAYED_FIELD,80), RSH.RECEIPT_NUM, RSH.SHIPMENT_HEADER_ID, POH.SEGMENT1, PL4.DISPLAYED_FIELD, POL.LINE_NUM, RSL.ITEM_ID, RSL.ITEM_REVISION, RSL.ITEM_DESCRIPTION, RCT.SOURCE_DOC_UNIT_OF_MEASURE, NVL(RRS.SOURCE_DOC_QUANTITY, NVL(RCT.SOURCE_DOC_QUANTITY, 0)), NVL(PLL.PRICE_OVERRIDE, POL.UNIT_PRICE), PR.RELEASE_NUM, RCT.SUBINVENTORY, R.JE_HEADER_ID, R.JE_LINE_NUM, RRS.LAST_UPDATE_DATE, RRS.LAST_UPDATED_BY, RRS.CREATION_DATE, RRS.CREATED_BY, RRS.LAST_UPDATE_LOGIN, RRS.REQUEST_ID, RRS.PROGRAM_APPLICATION_ID, RRS.PROGRAM_ID, RRS.PROGRAM_UPDATE_DATE, PLL.SHIPMENT_NUM, POD.DISTRIBUTION_NUM, RRS.JE_CATEGORY_NAME, GJC.USER_JE_CATEGORY_NAME FROM GL_IMPORT_REFERENCES R, RCV_MC_REC_SUB_LEDGER RRS, RCV_TRANSACTIONS RCT, ORG_ORGANIZATION_DEFINITIONS OOD, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL POL, PO_HEADERS_ALL POH, PO_RELEASES_ALL PR, PO_VENDORS POV, PO_VENDOR_SITES_ALL PVS, PO_LOOKUP_CODES PL4, PO_LOOKUP_CODES PL3, PO_LOOKUP_CODES PL2, PO_LOOKUP_CODES PL1, GL_DAILY_CONVERSION_TYPES GLCT, GL_JE_CATEGORIES GJC, GL_JE_HEADERS GJH WHERE PL1.LOOKUP_CODE = 'Y' AND PL1.LOOKUP_TYPE = 'YES/NO' AND PL3.LOOKUP_CODE = 'RCV TRANSACTION' AND PL3.LOOKUP_TYPE = 'VIEW ACCOUNTING' AND PL4.LOOKUP_CODE = POH.TYPE_LOOKUP_CODE AND PL4.LOOKUP_TYPE = 'PO TYPE' AND POV.VENDOR_ID = POH.VENDOR_ID AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND NVL(PVS.ORG_ID, -99) = NVL(POH.ORG_ID, -99) AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = PLL.PO_LINE_ID AND PR.PO_RELEASE_ID (+) = PLL.PO_RELEASE_ID AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3 AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID AND PL2.LOOKUP_CODE = RCT.TRANSACTION_TYPE AND PL2.LOOKUP_TYPE = 'RCV TRANSACTION TYPE' AND OOD.ORGANIZATION_ID = RCT.ORGANIZATION_ID AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID AND GLCT.CONVERSION_TYPE (+) = RCT.CURRENCY_CONVERSION_TYPE AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID AND R.GL_SL_LINK_TABLE = 'RSL' AND RRS.GL_SL_LINK_ID = R.GL_SL_LINK_ID AND RRS.RCV_TRANSACTION_ID = R.REFERENCE_5 AND GJC.JE_CATEGORY_NAME = RRS.JE_CATEGORY_NAME AND GJH.JE_HEADER_ID = R.JE_HEADER_ID AND GJH.ACTUAL_FLAG = RRS.ACTUAL_FLAG AND RRS.SET_OF_BOOKS_ID = GJH.LEDGER_ID

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(R
, RRS
, POD
, PLL
, POL
, POH
, PR
, POV
, PVS
, RCT
, OOD
, RSH
, PL4
, PL3
, PL2
, PL1
, GLCT) */ 201
, RRS.SET_OF_BOOKS_ID
, POH.ORG_ID
, OOD.ORGANIZATION_CODE
, RCT.ORGANIZATION_ID
, PL3.DISPLAYED_FIELD
, 'RCV TRX'
, PL2.DISPLAYED_FIELD
, RCT.TRANSACTION_TYPE
, RCT.TRANSACTION_DATE
, RCT.COMMENTS
, 'RCT'
, RCT.TRANSACTION_ID
, RRS.CODE_COMBINATION_ID
, RRS.CURRENCY_CODE
, RRS.ENTERED_DR
, RRS.ENTERED_CR
, RRS.ACCOUNTED_DR
, RRS.ACCOUNTED_CR
, RRS.CURRENCY_CONVERSION_DATE
, RCT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, RRS.CURRENCY_CONVERSION_RATE
, 'S'
, POV.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, RRS.ACCOUNTING_DATE
, PL1.DISPLAYED_FIELD
, 'Y'
, 'RCT'
, RCT.TRANSACTION_ID
, 'RSL'
, PL2.DISPLAYED_FIELD
, RPAD(PL2.DISPLAYED_FIELD
, 80)
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_HEADER_ID
, POH.SEGMENT1
, PL4.DISPLAYED_FIELD
, POL.LINE_NUM
, RSL.ITEM_ID
, RSL.ITEM_REVISION
, RSL.ITEM_DESCRIPTION
, RCT.SOURCE_DOC_UNIT_OF_MEASURE
, NVL(RRS.SOURCE_DOC_QUANTITY
, NVL(RCT.SOURCE_DOC_QUANTITY
, 0))
, NVL(PLL.PRICE_OVERRIDE
, POL.UNIT_PRICE)
, PR.RELEASE_NUM
, RCT.SUBINVENTORY
, R.JE_HEADER_ID
, R.JE_LINE_NUM
, RRS.LAST_UPDATE_DATE
, RRS.LAST_UPDATED_BY
, RRS.CREATION_DATE
, RRS.CREATED_BY
, RRS.LAST_UPDATE_LOGIN
, RRS.REQUEST_ID
, RRS.PROGRAM_APPLICATION_ID
, RRS.PROGRAM_ID
, RRS.PROGRAM_UPDATE_DATE
, PLL.SHIPMENT_NUM
, POD.DISTRIBUTION_NUM
, RRS.JE_CATEGORY_NAME
, GJC.USER_JE_CATEGORY_NAME
FROM GL_IMPORT_REFERENCES R
, RCV_RECEIVING_SUB_LEDGER RRS
, RCV_TRANSACTIONS RCT
, ORG_ORGANIZATION_DEFINITIONS OOD
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_RELEASES_ALL PR
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, PO_LOOKUP_CODES PL4
, PO_LOOKUP_CODES PL3
, PO_LOOKUP_CODES PL2
, PO_LOOKUP_CODES PL1
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_HEADERS GJH
WHERE PL1.LOOKUP_CODE = 'Y'
AND PL1.LOOKUP_TYPE = 'YES/NO'
AND PL3.LOOKUP_CODE = 'RCV TRANSACTION'
AND PL3.LOOKUP_TYPE = 'VIEW ACCOUNTING'
AND PL4.LOOKUP_CODE = POH.TYPE_LOOKUP_CODE
AND PL4.LOOKUP_TYPE = 'PO TYPE'
AND POV.VENDOR_ID = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND NVL(PVS.ORG_ID
, -99) = NVL(POH.ORG_ID
, -99)
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND PR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID
AND PL2.LOOKUP_CODE = RCT.TRANSACTION_TYPE
AND PL2.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND OOD.ORGANIZATION_ID = RCT.ORGANIZATION_ID
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND GLCT.CONVERSION_TYPE(+) = RCT.CURRENCY_CONVERSION_TYPE
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND R.GL_SL_LINK_TABLE = 'RSL'
AND RRS.GL_SL_LINK_ID = R.GL_SL_LINK_ID
AND RRS.RCV_TRANSACTION_ID = R.REFERENCE_5
AND GJC.JE_CATEGORY_NAME = RRS.JE_CATEGORY_NAME
AND GJH.JE_HEADER_ID= R.JE_HEADER_ID
AND GJH.ACTUAL_FLAG = RRS.ACTUAL_FLAG
AND RRS.SET_OF_BOOKS_ID = GJH.LEDGER_ID UNION ALL SELECT 201
, RRS.SET_OF_BOOKS_ID
, POH.ORG_ID
, OOD.ORGANIZATION_CODE
, RCT.ORGANIZATION_ID
, PL3.DISPLAYED_FIELD
, 'RCV TRX'
, PL2.DISPLAYED_FIELD
, RCT.TRANSACTION_TYPE
, RCT.TRANSACTION_DATE
, RCT.COMMENTS
, 'RCT'
, RCT.TRANSACTION_ID
, RRS.CODE_COMBINATION_ID
, RRS.CURRENCY_CODE
, RRS.ENTERED_DR
, RRS.ENTERED_CR
, RRS.ACCOUNTED_DR
, RRS.ACCOUNTED_CR
, RRS.CURRENCY_CONVERSION_DATE
, RCT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, RRS.CURRENCY_CONVERSION_RATE
, 'S'
, POV.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, RRS.ACCOUNTING_DATE
, PL1.DISPLAYED_FIELD
, 'Y'
, 'RCT'
, RCT.TRANSACTION_ID
, 'RSL'
, PL2.DISPLAYED_FIELD
, RPAD(PL2.DISPLAYED_FIELD
, 80)
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_HEADER_ID
, POH.SEGMENT1
, PL4.DISPLAYED_FIELD
, POL.LINE_NUM
, RSL.ITEM_ID
, RSL.ITEM_REVISION
, RSL.ITEM_DESCRIPTION
, RCT.SOURCE_DOC_UNIT_OF_MEASURE
, NVL(RRS.SOURCE_DOC_QUANTITY
, NVL(RCT.SOURCE_DOC_QUANTITY
, 0))
, NVL(PLL.PRICE_OVERRIDE
, POL.UNIT_PRICE)
, PR.RELEASE_NUM
, RCT.SUBINVENTORY
, R.JE_HEADER_ID
, R.JE_LINE_NUM
, RRS.LAST_UPDATE_DATE
, RRS.LAST_UPDATED_BY
, RRS.CREATION_DATE
, RRS.CREATED_BY
, RRS.LAST_UPDATE_LOGIN
, RRS.REQUEST_ID
, RRS.PROGRAM_APPLICATION_ID
, RRS.PROGRAM_ID
, RRS.PROGRAM_UPDATE_DATE
, PLL.SHIPMENT_NUM
, POD.DISTRIBUTION_NUM
, RRS.JE_CATEGORY_NAME
, GJC.USER_JE_CATEGORY_NAME
FROM GL_IMPORT_REFERENCES R
, RCV_MC_REC_SUB_LEDGER RRS
, RCV_TRANSACTIONS RCT
, ORG_ORGANIZATION_DEFINITIONS OOD
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_RELEASES_ALL PR
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, PO_LOOKUP_CODES PL4
, PO_LOOKUP_CODES PL3
, PO_LOOKUP_CODES PL2
, PO_LOOKUP_CODES PL1
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_HEADERS GJH
WHERE PL1.LOOKUP_CODE = 'Y'
AND PL1.LOOKUP_TYPE = 'YES/NO'
AND PL3.LOOKUP_CODE = 'RCV TRANSACTION'
AND PL3.LOOKUP_TYPE = 'VIEW ACCOUNTING'
AND PL4.LOOKUP_CODE = POH.TYPE_LOOKUP_CODE
AND PL4.LOOKUP_TYPE = 'PO TYPE'
AND POV.VENDOR_ID = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND NVL(PVS.ORG_ID
, -99) = NVL(POH.ORG_ID
, -99)
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND PR.PO_RELEASE_ID (+) = PLL.PO_RELEASE_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID
AND PL2.LOOKUP_CODE = RCT.TRANSACTION_TYPE
AND PL2.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND OOD.ORGANIZATION_ID = RCT.ORGANIZATION_ID
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND GLCT.CONVERSION_TYPE (+) = RCT.CURRENCY_CONVERSION_TYPE
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND R.GL_SL_LINK_TABLE = 'RSL'
AND RRS.GL_SL_LINK_ID = R.GL_SL_LINK_ID
AND RRS.RCV_TRANSACTION_ID = R.REFERENCE_5
AND GJC.JE_CATEGORY_NAME = RRS.JE_CATEGORY_NAME
AND GJH.JE_HEADER_ID = R.JE_HEADER_ID
AND GJH.ACTUAL_FLAG = RRS.ACTUAL_FLAG
AND RRS.SET_OF_BOOKS_ID = GJH.LEDGER_ID