View: PA_PROJ_COST_SUBLEDGER_V

Product:	PA - Projects
Description:	View of projects costs including manufacturing costs
Implementation/DBA Data:	APPS.PA_PROJ_COST_SUBLEDGER_V

View Text

SELECT EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CDL.LINE_NUM COST_DISTRIBUTION_LINE_NUM
, CDL.DR_CODE_COMBINATION_ID CODE_COMBINATION_ID
, GLC.DESCRIPTION ACCOUNT_DESCRIPTION
, CDL.GL_DATE GL_DATE
, SYS.PROJECT_MANUFACTURING_FLAG MFG_RELATED
, SYS.MEANING SYSTEM_LINKAGE_FUNCTION
, ET.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY
, ET.EXPENDITURE_TYPE EXPENDITURE_TYPE
, CDL.ORG_ID ORGANIZATION_ID
, PRJ.PROJECT_TYPE PROJECT_TYPE
, PRJ.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PRJ.NAME PROJECT_NAME
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, TASK.TASK_NAME TASK_NAME
, DECODE(NVL(CDL.ACCT_BURDENED_COST
, 0)
, 0
, CDL.ACCT_RAW_COST
, CDL.ACCT_BURDENED_COST) TOTAL_COST
, CDL.ACCT_RAW_COST RAW_COST
, NVL(NVL(CDL.ACCT_BURDENED_COST
, 0)
, CDL.ACCT_RAW_COST) - CDL.ACCT_RAW_COST BURDEN_COST
FROM PA_EXPENDITURE_TYPES ET
, PA_PROJECTS_ALL PRJ
, PA_TASKS TASK
, GL_CODE_COMBINATIONS GLC
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_SYSTEM_LINKAGES SYS
, PA_TRANSACTION_SOURCES TS
WHERE CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID AND CDL.LINE_TYPE = 'R' AND CDL.TRANSFER_STATUS_CODE || '' IN ('A'
, 'V')
AND CDL.ACCT_EVENT_ID IS NULL
AND NVL(EI.HISTORICAL_FLAG
, 'Y') = DECODE(NVL(TS.PREDEFINED_FLAG
, 'Y')
, 'N'
, NVL(EI.HISTORICAL_FLAG
, 'Y')
, 'Y')
AND EI.TRANSACTION_SOURCE = TS.TRANSACTION_SOURCE(+) AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE AND GLC.CODE_COMBINATION_ID = CDL.DR_CODE_COMBINATION_ID AND EI.TASK_ID = TASK.TASK_ID
AND TASK.PROJECT_ID = PRJ.PROJECT_ID
AND EI.SYSTEM_LINKAGE_FUNCTION = SYS.FUNCTION UNION ALL SELECT EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CDL.LINE_NUM COST_DISTRIBUTION_LINE_NUM
, AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GLC.DESCRIPTION ACCOUNT_DESCRIPTION
, CDL.GL_DATE GL_DATE
, SYS.PROJECT_MANUFACTURING_FLAG MFG_RELATED
, SYS.MEANING SYSTEM_LINKAGE_FUNCTION
, ET.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY
, ET.EXPENDITURE_TYPE EXPENDITURE_TYPE
, CDL.ORG_ID ORGANIZATION_ID
, PRJ.PROJECT_TYPE PROJECT_TYPE
, PRJ.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PRJ.NAME PROJECT_NAME
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, TASK.TASK_NAME TASK_NAME
, DECODE(NVL(CDL.ACCT_BURDENED_COST
, 0)
, 0
, CDL.ACCT_RAW_COST
, CDL.ACCT_BURDENED_COST) TOTAL_COST
, CDL.ACCT_RAW_COST RAW_COST
, NVL(NVL(CDL.ACCT_BURDENED_COST
, 0)
, CDL.ACCT_RAW_COST) - CDL.ACCT_RAW_COST BURDEN_COST
FROM PA_EXPENDITURE_TYPES ET
, PA_PROJECTS_ALL PRJ
, PA_TASKS TASK
, GL_CODE_COMBINATIONS GLC
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_SYSTEM_LINKAGES SYS
, PA_IMPLEMENTATIONS_ALL IMP
, PA_TRANSACTION_SOURCES TS
, XLA_DISTRIBUTION_LINKS XDL
, XLA_AE_HEADERS AEH
, XLA_AE_LINES AEL
, XLA_ACCT_CLASS_ASSGNS XACA
, XLA_ASSIGNMENT_DEFNS_B XAD
, XLA_POST_ACCT_PROGS_B XPAP
, GL_LEDGERS GL WHERE CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND ((NVL(EI.HISTORICAL_FLAG
, 'Y') = 'N') OR (CDL.ACCT_EVENT_ID IS NOT NULL))
AND TS.TRANSACTION_SOURCE (+) = EI.TRANSACTION_SOURCE AND CDL.LINE_TYPE = 'R'
AND CDL.TRANSFER_STATUS_CODE || '' IN ('A'
, 'V')
AND CDL.ORG_ID = IMP.ORG_ID
AND XDL.APPLICATION_ID = DECODE(CDL.TRANSFER_STATUS_CODE
, 'V'
, DECODE (TS.ACCT_SOURCE_CODE
, 'AP_INV'
, 200
, 'AP_PAY'
, 200
, 'AP_APP'
, 200
, 'INV'
, 707
, 'WIP'
, 707
, 'RCV'
, 707
, 0)
, 275 )
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DECODE(CDL.TRANSFER_STATUS_CODE
, 'V'
, DECODE (TS.ACCT_SOURCE_CODE
, 'AP_INV'
, DECODE ( EI.DOCUMENT_PAYMENT_ID
, NULL
, EI.DOCUMENT_DISTRIBUTION_ID
, CDL.SYSTEM_REFERENCE5 )
, 'AP_PAY'
, CDL.SYSTEM_REFERENCE5
, 'AP_APP'
, CDL.SYSTEM_REFERENCE5
, 'INV'
, CDL.SYSTEM_REFERENCE5
, 'WIP'
, CDL.SYSTEM_REFERENCE5
, 'RCV'
, CDL.SYSTEM_REFERENCE5 )
, EI.EXPENDITURE_ITEM_ID )
AND NVL(XDL.SOURCE_DISTRIBUTION_ID_NUM_2
, -99) = DECODE(CDL.TRANSFER_STATUS_CODE
, 'V'
, NVL(XDL.SOURCE_DISTRIBUTION_ID_NUM_2
, -99)
, CDL.LINE_NUM )
AND XDL.SOURCE_DISTRIBUTION_TYPE = DECODE(CDL.TRANSFER_STATUS_CODE
, 'V'
, DECODE (TS.ACCT_SOURCE_CODE
, 'AP_PAY'
, 'AP_PMT_DIST'
, 'AP_INV'
, DECODE ( EI.DOCUMENT_PAYMENT_ID
, NULL
, 'AP_INV_DIST'
, 'AP_PMT_DIST' )
, 'AP_APP'
, 'AP_PREPAY'
, 'RCV'
, 'RCV_RECEIVING_SUB_LEDGER'
, 'INV'
, 'MTL_TRANSACTION_ACCOUNTS'
, 'WIP'
, 'WIP_TRANSACTION_ACCOUNTS')
, CDL.LINE_TYPE )
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.APPLICATION_ID = AEL.APPLICATION_ID
AND AEL.APPLICATION_ID = XDL.APPLICATION_ID
AND AEH.BALANCE_TYPE_CODE = 'A'
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND AEH.LEDGER_ID = IMP.SET_OF_BOOKS_ID
AND AEL.ACCOUNTING_CLASS_CODE = XACA.ACCOUNTING_CLASS_CODE
AND XACA.PROGRAM_CODE = XAD.PROGRAM_CODE
AND XACA.PROGRAM_OWNER_CODE = XAD.PROGRAM_OWNER_CODE
AND XAD.PROGRAM_CODE = XPAP.PROGRAM_CODE
AND XPAP.PROGRAM_OWNER_CODE = 'S'
AND XACA.ASSIGNMENT_CODE = XAD.ASSIGNMENT_CODE
AND XACA.ASSIGNMENT_OWNER_CODE = XAD.ASSIGNMENT_OWNER_CODE
AND (XAD.LEDGER_ID IS NULL OR XAD.LEDGER_ID = IMP.SET_OF_BOOKS_ID)
AND XAD.ENABLED_FLAG = 'Y'
AND GL.LEDGER_ID = IMP.SET_OF_BOOKS_ID
AND DECODE ( XACA.ACCOUNTING_CLASS_CODE
, 'DISCOUNT'
, DECODE (GL.SLA_LEDGER_CASH_BASIS_FLAG
, 'Y'
, 2
, 1)
, 1 ) = 1
AND XPAP.PROGRAM_CODE = 'PA_POSTACCOUNTING_DEBIT'
AND XPAP.APPLICATION_ID = 275 AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE AND GLC.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID AND EI.TASK_ID = TASK.TASK_ID
AND TASK.PROJECT_ID = PRJ.PROJECT_ID
AND EI.SYSTEM_LINKAGE_FUNCTION = SYS.FUNCTION

Columns