View: CST_PAC_AEL_GL_RCV_V

Product:	BOM - Bills of Material
Description:
Implementation/DBA Data:	APPS.CST_PAC_AEL_GL_RCV_V

View Text

SELECT CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CCG.ORGANIZATION_ID
, CLCT.PRIMARY_COST_METHOD
, CAH.AE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME
, 201
, CAH.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
, 'RCV'
, RCT.TRANSACTION_ID
, CAL.CODE_COMBINATION_ID
, CAL.CURRENCY_CODE
, CAL.ENTERED_DR
, CAL.ENTERED_CR
, CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR
, CAL.CURRENCY_CONVERSION_DATE
, CAL.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, CAL.CURRENCY_CONVERSION_RATE
, 'S' SELECT_FLAG
, POV.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, CAH.ACCOUNTING_DATE
, PL1.DISPLAYED_FIELD
, 'Y'
, 'RCV'
, 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(RCT.QUANTITY
, 0)
, CAL.RATE_OR_AMOUNT
, PR.RELEASE_NUM
, RCT.SUBINVENTORY
, R.JE_HEADER_ID
, R.JE_LINE_NUM
, CAL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CAL.LAST_UPDATED_BY LAST_UPDATED_BY
, CAL.CREATION_DATE CREATION_DATE
, CAL.CREATED_BY CREATED_BY
, CAL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CAL.REQUEST_ID REQUEST_ID
, CAL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CAL.PROGRAM_ID PROGRAM_ID
, CAL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, PLL.SHIPMENT_NUM
, POD.DISTRIBUTION_NUM
FROM GL_IMPORT_REFERENCES R
, 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
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
, CST_LE_COST_TYPES CLCT
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
, CST_COST_GROUP_ASSIGNMENTS CCGA
, GL_JE_CATEGORIES GJC
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 PVS.ORG_ID = POH.ORG_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 = CAL.PO_DISTRIBUTION_ID
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 CLCT.SET_OF_BOOKS_ID = CAH.SET_OF_BOOKS_ID
AND OOD.ORGANIZATION_ID = RCT.ORGANIZATION_ID
AND RCT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
AND GLCT.CONVERSION_TYPE(+) = RCT.CURRENCY_CONVERSION_TYPE
AND RCT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
AND CAL.GL_SL_LINK_ID = R.GL_SL_LINK_ID
AND CAH.ACCOUNTING_EVENT_ID = R.REFERENCE_3
AND RCT.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCG.COST_GROUP_TYPE= 2
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID
AND GJC.JE_CATEGORY_NAME = CAH.AE_CATEGORY
AND CAH.LEGAL_ENTITY_ID = CLCT.LEGAL_ENTITY
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCG.COST_GROUP_ID UNION ALL SELECT CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CCG.ORGANIZATION_ID
, CLCT.PRIMARY_COST_METHOD
, CAH.AE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME
, 201
, CAH.SET_OF_BOOKS_ID
, POH.ORG_ID
, COD.ORGANIZATION_CODE
, RAE.ORGANIZATION_ID
, PL3.DISPLAYED_FIELD
, 'RAE TRX'
, RAET.EVENT_TYPE_NAME
, RAET.EVENT_TYPE_NAME
, RAE.TRANSACTION_DATE
, RCT.COMMENTS
, 'RCV'
, RAE.ACCOUNTING_EVENT_ID
, CAL.CODE_COMBINATION_ID
, CAL.CURRENCY_CODE
, CAL.ENTERED_DR
, CAL.ENTERED_CR
, CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR
, CAL.CURRENCY_CONVERSION_DATE
, CAL.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, CAL.CURRENCY_CONVERSION_RATE
, 'S' SELECT_FLAG
, POV.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, CAH.ACCOUNTING_DATE
, PL1.DISPLAYED_FIELD
, 'Y'
, 'RAE'
, RAE.ACCOUNTING_EVENT_ID
, 'RSL'
, RAET.EVENT_TYPE_NAME
, RPAD(RAET.EVENT_TYPE_NAME
, 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
, RAE.TRANSACTION_UNIT_OF_MEASURE
, NVL(RAE.TRANSACTION_QUANTITY
, 0)
, CAL.RATE_OR_AMOUNT
, PR.RELEASE_NUM
, RCT.SUBINVENTORY
, R.JE_HEADER_ID
, R.JE_LINE_NUM
, CAL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CAL.LAST_UPDATED_BY LAST_UPDATED_BY
, CAL.CREATION_DATE CREATION_DATE
, CAL.CREATED_BY CREATED_BY
, CAL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CAL.REQUEST_ID REQUEST_ID
, CAL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CAL.PROGRAM_ID PROGRAM_ID
, CAL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, PLL.SHIPMENT_NUM
, POD.DISTRIBUTION_NUM
FROM GL_IMPORT_REFERENCES R
, RCV_TRANSACTIONS RCT
, RCV_ACCOUNTING_EVENTS RAE
, RCV_ACCOUNTING_EVENT_TYPES RAET
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
, CST_LE_COST_TYPES CLCT
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
, CST_COST_GROUP_ASSIGNMENTS CCGA
, PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, PO_RELEASES_ALL PR
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, CST_ORGANIZATION_DEFINITIONS COD
, PO_LOOKUP_CODES PL1
, PO_LOOKUP_CODES PL3
, PO_LOOKUP_CODES PL4
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 PVS.ORG_ID = POH.ORG_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 = CAL.PO_DISTRIBUTION_ID
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID
AND RCT.TRANSACTION_ID = RAE.RCV_TRANSACTION_ID
AND CLCT.SET_OF_BOOKS_ID = CAH.SET_OF_BOOKS_ID
AND COD.ORGANIZATION_ID = RAE.ORGANIZATION_ID
AND GLCT.CONVERSION_TYPE(+) = RCT.CURRENCY_CONVERSION_TYPE
AND RAE.ACCOUNTING_EVENT_ID = CAH.ACCOUNTING_EVENT_ID
AND CAL.GL_SL_LINK_ID = R.GL_SL_LINK_ID
AND CAH.ACCOUNTING_EVENT_ID = R.REFERENCE_3
AND RAET.EVENT_TYPE_ID = RAE.EVENT_TYPE_ID
AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID
AND RCT.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCG.COST_GROUP_TYPE= 2
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND GJC.JE_CATEGORY_NAME = CAH.AE_CATEGORY
AND CAH.LEGAL_ENTITY_ID = CLCT.LEGAL_ENTITY
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCG.COST_GROUP_ID

Columns