VIEW: APPS.PA_COST_BURDEN_DETAILS_V

Source

View Text - Preformatted

SELECT p.segment1 , p.name , p.project_id , t.task_number , t.task_name , t.task_id , bdb.expenditure_organization , bdb.expenditure_organization_id , ei.expenditure_type , ei.expenditure_item_date , cdl.pa_date , cdl.gl_date , cdl.amount , cdl.burdened_cost , PA_CURRENCY.ROUND_CURRENCY_AMT(cdl.amount * bdb.compiled_multiplier) , bdb.cost_base , bdb.cb_sort_order , bdb.burden_cost_code , bdb.bcc_precedence , bdb.input_multiplier , bdb.compiled_multiplier , ei.expenditure_item_id , cdl.line_num , bdb.burden_schedule , bdb.burden_schedule_id , bdb.burden_schedule_revision , bdb.burden_schedule_revision_id , bdb.burden_structure , bdb.compiled_set_id , cdl.denom_currency_code , cdl.denom_raw_cost , PA_CURRENCY.ROUND_CURRENCY_AMT(cdl.denom_raw_cost * bdb.compiled_multiplier) , cdl.denom_burdened_cost , cdl.acct_currency_code , cdl.acct_raw_cost , PA_CURRENCY.ROUND_CURRENCY_AMT(cdl.acct_raw_cost * bdb.compiled_multiplier) , cdl.acct_burdened_cost FROM pa_burden_details_base_v bdb, pa_projects_all p, pa_tasks t, pa_expenditures e, pa_expenditure_items ei, pa_cost_distribution_lines cdl WHERE bdb.compiled_set_id = cdl.ind_compiled_set_id AND bdb.expenditure_type = ei.expenditure_type AND bdb.expenditure_organization_id = nvl(ei.override_to_organization_id, e.incurred_by_organization_id) AND ei.expenditure_item_id = cdl.expenditure_item_id AND cdl.line_type = 'R' AND e.expenditure_id = ei.expenditure_id AND t.task_id = ei.task_id AND p.project_id = t.project_id UNION SELECT p.segment1 , p.name , p.project_id , t.task_number , t.task_name , t.task_id , bdb.expenditure_organization , bdb.expenditure_organization_id , ei.expenditure_type , ei.expenditure_item_date , cdl.pa_date , cdl.gl_date , cdl.amount , cdl.burdened_cost , PA_CURRENCY.ROUND_CURRENCY_AMT(cdl4.amount * (bdb.compiled_multiplier-bdb2.compiled_multiplier)) , bdb.cost_base , bdb.cb_sort_order , bdb.burden_cost_code , bdb.bcc_precedence , bdb.input_multiplier , bdb.compiled_multiplier , ei.expenditure_item_id , cdl.line_num , bdb.burden_schedule , bdb.burden_schedule_id , bdb.burden_schedule_revision , bdb.burden_schedule_revision_id , bdb.burden_structure , bdb.compiled_set_id , cdl.denom_currency_code , cdl.denom_raw_cost , PA_CURRENCY.ROUND_CURRENCY_AMT(cdl4.denom_raw_cost * (bdb.compiled_multiplier-bdb2.compiled_multiplier)) , cdl.denom_burdened_cost , cdl.acct_currency_code , cdl.acct_raw_cost , PA_CURRENCY.ROUND_CURRENCY_AMT(cdl4.acct_raw_cost * (bdb.compiled_multiplier-bdb2.compiled_multiplier)) , cdl.acct_burdened_cost from pa_burden_details_base_v bdb, pa_projects_all p , pa_tasks t , pa_expenditures e , pa_expenditure_items ei , pa_cost_distribution_lines cdl, pa_cost_distribution_lines cdl2, pa_cost_distribution_lines cdl4, pa_burden_details_base_v bdb2 WHERE bdb.compiled_set_id = cdl.ind_compiled_set_id AND bdb.expenditure_type = ei.expenditure_type AND bdb.expenditure_organization_id = NVL(ei.override_to_organization_id, e.incurred_by_organization_id) AND bdb2.compiled_set_id = cdl2.ind_compiled_set_id AND bdb2.expenditure_type = ei.expenditure_type AND bdb2.expenditure_organization_id = NVL(ei.override_to_organization_id, e.incurred_by_organization_id) and bdb.burden_cost_code = bdb2.burden_cost_code AND ei.expenditure_item_id = cdl.expenditure_item_id AND cdl.line_type = 'I' AND e.expenditure_id = ei.expenditure_id AND t.task_id = ei.task_id AND p.project_id = t.project_id AND cdl2.expenditure_item_id = cdl.expenditure_item_id AND cdl4.expenditure_item_id = cdl.expenditure_item_id and cdl4.line_num = cdl.parent_line_num and cdl2.line_num = (select max(cdl3.line_num) from pa_cost_distribution_lines cdl3 where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_type in ('R','I') and cdl3.line_num < cdl.line_num)

View Text - HTML Formatted

SELECT P.SEGMENT1
, P.NAME
, P.PROJECT_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.TASK_ID
, BDB.EXPENDITURE_ORGANIZATION
, BDB.EXPENDITURE_ORGANIZATION_ID
, EI.EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE
, CDL.PA_DATE
, CDL.GL_DATE
, CDL.AMOUNT
, CDL.BURDENED_COST
, PA_CURRENCY.ROUND_CURRENCY_AMT(CDL.AMOUNT * BDB.COMPILED_MULTIPLIER)
, BDB.COST_BASE
, BDB.CB_SORT_ORDER
, BDB.BURDEN_COST_CODE
, BDB.BCC_PRECEDENCE
, BDB.INPUT_MULTIPLIER
, BDB.COMPILED_MULTIPLIER
, EI.EXPENDITURE_ITEM_ID
, CDL.LINE_NUM
, BDB.BURDEN_SCHEDULE
, BDB.BURDEN_SCHEDULE_ID
, BDB.BURDEN_SCHEDULE_REVISION
, BDB.BURDEN_SCHEDULE_REVISION_ID
, BDB.BURDEN_STRUCTURE
, BDB.COMPILED_SET_ID
, CDL.DENOM_CURRENCY_CODE
, CDL.DENOM_RAW_COST
, PA_CURRENCY.ROUND_CURRENCY_AMT(CDL.DENOM_RAW_COST * BDB.COMPILED_MULTIPLIER)
, CDL.DENOM_BURDENED_COST
, CDL.ACCT_CURRENCY_CODE
, CDL.ACCT_RAW_COST
, PA_CURRENCY.ROUND_CURRENCY_AMT(CDL.ACCT_RAW_COST * BDB.COMPILED_MULTIPLIER)
, CDL.ACCT_BURDENED_COST
FROM PA_BURDEN_DETAILS_BASE_V BDB
, PA_PROJECTS_ALL P
, PA_TASKS T
, PA_EXPENDITURES E
, PA_EXPENDITURE_ITEMS EI
, PA_COST_DISTRIBUTION_LINES CDL
WHERE BDB.COMPILED_SET_ID = CDL.IND_COMPILED_SET_ID
AND BDB.EXPENDITURE_TYPE = EI.EXPENDITURE_TYPE
AND BDB.EXPENDITURE_ORGANIZATION_ID = NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, E.INCURRED_BY_ORGANIZATION_ID)
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL.LINE_TYPE = 'R'
AND E.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND T.TASK_ID = EI.TASK_ID
AND P.PROJECT_ID = T.PROJECT_ID UNION SELECT P.SEGMENT1
, P.NAME
, P.PROJECT_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.TASK_ID
, BDB.EXPENDITURE_ORGANIZATION
, BDB.EXPENDITURE_ORGANIZATION_ID
, EI.EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE
, CDL.PA_DATE
, CDL.GL_DATE
, CDL.AMOUNT
, CDL.BURDENED_COST
, PA_CURRENCY.ROUND_CURRENCY_AMT(CDL4.AMOUNT * (BDB.COMPILED_MULTIPLIER-BDB2.COMPILED_MULTIPLIER))
, BDB.COST_BASE
, BDB.CB_SORT_ORDER
, BDB.BURDEN_COST_CODE
, BDB.BCC_PRECEDENCE
, BDB.INPUT_MULTIPLIER
, BDB.COMPILED_MULTIPLIER
, EI.EXPENDITURE_ITEM_ID
, CDL.LINE_NUM
, BDB.BURDEN_SCHEDULE
, BDB.BURDEN_SCHEDULE_ID
, BDB.BURDEN_SCHEDULE_REVISION
, BDB.BURDEN_SCHEDULE_REVISION_ID
, BDB.BURDEN_STRUCTURE
, BDB.COMPILED_SET_ID
, CDL.DENOM_CURRENCY_CODE
, CDL.DENOM_RAW_COST
, PA_CURRENCY.ROUND_CURRENCY_AMT(CDL4.DENOM_RAW_COST * (BDB.COMPILED_MULTIPLIER-BDB2.COMPILED_MULTIPLIER))
, CDL.DENOM_BURDENED_COST
, CDL.ACCT_CURRENCY_CODE
, CDL.ACCT_RAW_COST
, PA_CURRENCY.ROUND_CURRENCY_AMT(CDL4.ACCT_RAW_COST * (BDB.COMPILED_MULTIPLIER-BDB2.COMPILED_MULTIPLIER))
, CDL.ACCT_BURDENED_COST
FROM PA_BURDEN_DETAILS_BASE_V BDB
, PA_PROJECTS_ALL P
, PA_TASKS T
, PA_EXPENDITURES E
, PA_EXPENDITURE_ITEMS EI
, PA_COST_DISTRIBUTION_LINES CDL
, PA_COST_DISTRIBUTION_LINES CDL2
, PA_COST_DISTRIBUTION_LINES CDL4
, PA_BURDEN_DETAILS_BASE_V BDB2
WHERE BDB.COMPILED_SET_ID = CDL.IND_COMPILED_SET_ID
AND BDB.EXPENDITURE_TYPE = EI.EXPENDITURE_TYPE
AND BDB.EXPENDITURE_ORGANIZATION_ID = NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, E.INCURRED_BY_ORGANIZATION_ID)
AND BDB2.COMPILED_SET_ID = CDL2.IND_COMPILED_SET_ID
AND BDB2.EXPENDITURE_TYPE = EI.EXPENDITURE_TYPE
AND BDB2.EXPENDITURE_ORGANIZATION_ID = NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, E.INCURRED_BY_ORGANIZATION_ID)
AND BDB.BURDEN_COST_CODE = BDB2.BURDEN_COST_CODE
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL.LINE_TYPE = 'I'
AND E.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND T.TASK_ID = EI.TASK_ID
AND P.PROJECT_ID = T.PROJECT_ID
AND CDL2.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL4.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL4.LINE_NUM = CDL.PARENT_LINE_NUM
AND CDL2.LINE_NUM = (SELECT MAX(CDL3.LINE_NUM)
FROM PA_COST_DISTRIBUTION_LINES CDL3
WHERE CDL3.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL3.LINE_TYPE IN ('R'
, 'I')
AND CDL3.LINE_NUM < CDL.LINE_NUM)