VIEW: APPS.PA_PROJ_COST_SUBLEDGER_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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