VIEW: APPS.RCV_AEL_SL_MRC_V

Source

View Text - Preformatted

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 , RSL.LINE_NUM , RSL.ITEM_ID , RSL.ITEM_REVISION , RSL.ITEM_DESCRIPTION , RCT.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 , 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 PO_LOOKUP_CODES PL1, PO_LOOKUP_CODES PL3, PO_LOOKUP_CODES PL4, PO_VENDORS POV, PO_VENDOR_SITES PVS, PO_HEADERS POH, PO_LINES POL, PO_RELEASES PR, PO_LINE_LOCATIONS PLL, PO_DISTRIBUTIONS POD, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_LOOKUP_CODES PL2, ORG_ORGANIZATION_DEFINITIONS OOD, GL_DAILY_CONVERSION_TYPES GLCT, RCV_TRANSACTIONS_MRC_V RCT, RCV_REC_SUB_LEDGER_MRC_V RRS, GL_JE_CATEGORIES GJC, GL_LEDGER_RELATIONSHIPS GLR 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 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 GLCT.CONVERSION_TYPE(+) = RCT.CURRENCY_CONVERSION_TYPE AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID AND GJC.JE_CATEGORY_NAME = RRS.JE_CATEGORY_NAME AND RRS.SET_OF_BOOKS_ID = TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),45,10)) AND GLR.TARGET_LEDGER_ID= RRS.SET_OF_BOOKS_ID AND OOD.SET_OF_BOOKS_ID = GLR.SOURCE_LEDGER_ID AND GLR.APPLICATION_ID = 201 AND GLR.ORG_ID = NVL(POH.ORG_ID, -99) AND GLR.TARGET_LEDGER_CATEGORY_CODE = 'ALC' AND GLR.RELATIONSHIP_TYPE_CODE = 'SUBLEDGER'

View Text - HTML Formatted

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
, RSL.LINE_NUM
, RSL.ITEM_ID
, RSL.ITEM_REVISION
, RSL.ITEM_DESCRIPTION
, RCT.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
, 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 PO_LOOKUP_CODES PL1
, PO_LOOKUP_CODES PL3
, PO_LOOKUP_CODES PL4
, PO_VENDORS POV
, PO_VENDOR_SITES PVS
, PO_HEADERS POH
, PO_LINES POL
, PO_RELEASES PR
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS POD
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_LOOKUP_CODES PL2
, ORG_ORGANIZATION_DEFINITIONS OOD
, GL_DAILY_CONVERSION_TYPES GLCT
, RCV_TRANSACTIONS_MRC_V RCT
, RCV_REC_SUB_LEDGER_MRC_V RRS
, GL_JE_CATEGORIES GJC
, GL_LEDGER_RELATIONSHIPS GLR
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 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 GLCT.CONVERSION_TYPE(+) = RCT.CURRENCY_CONVERSION_TYPE
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND GJC.JE_CATEGORY_NAME = RRS.JE_CATEGORY_NAME
AND RRS.SET_OF_BOOKS_ID = TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO')
, 45
, 10))
AND GLR.TARGET_LEDGER_ID= RRS.SET_OF_BOOKS_ID
AND OOD.SET_OF_BOOKS_ID = GLR.SOURCE_LEDGER_ID
AND GLR.APPLICATION_ID = 201
AND GLR.ORG_ID = NVL(POH.ORG_ID
, -99)
AND GLR.TARGET_LEDGER_CATEGORY_CODE = 'ALC'
AND GLR.RELATIONSHIP_TYPE_CODE = 'SUBLEDGER'