VIEW: APPS.PA_ALLOC_TXN_ACCUM_V

Source

View Text - Preformatted

SELECT accum.TXN_ACCUM_ID, accum.PROJECT_ID, accum.TASK_ID, accum.PA_PERIOD, accum.GL_PERIOD, accum.TOT_REVENUE, accum.TOT_RAW_COST, accum.TOT_BURDENED_COST, accum.TOT_QUANTITY, accum.TOT_LABOR_HOURS, accum.TOT_BILLABLE_RAW_COST, accum.TOT_BILLABLE_BURDENED_COST, accum.TOT_BILLABLE_QUANTITY, accum.TOT_BILLABLE_LABOR_HOURS, accum.I_TOT_REVENUE, accum.I_TOT_RAW_COST, accum.I_TOT_BURDENED_COST, accum.I_TOT_QUANTITY, accum.I_TOT_LABOR_HOURS, accum.I_TOT_BILLABLE_RAW_COST, accum.I_TOT_BILLABLE_BURDENED_COST, accum.I_TOT_BILLABLE_QUANTITY, accum.I_TOT_BILLABLE_LABOR_HOURS, 'PA' RECORD_SOURCE FROM pa_txn_accum accum, pa_projects_all prj WHERE prj.PJI_SOURCE_FLAG IS NULL AND accum.PROJECT_ID = prj.PROJECT_ID UNION ALL SELECT -1 Txn_accum_id, fact.PROJECT_ID, fact.PROJECT_ELEMENT_ID task_id, fact.pa_period, fact.gl_period, SUM(fact.REVENUE) tot_revenue, SUM(fact.RAW_COST) tot_raw_cost, SUM(fact.BRDN_COST) tot_burdened_cost, SUM(fact.LABOR_HRS) tot_quantity, SUM(fact.LABOR_HRS) tot_labor_hours, SUM(fact.BILL_RAW_COST) tot_billable_raw_cost, SUM(fact.BILL_BRDN_COST) tot_billable_burdened_cost, SUM(fact.BILL_LABOR_HRS) tot_billable_quantity, SUM(fact.BILL_LABOR_HRS) tot_billable_labor_hours, SUM(0) I_tot_revenue, SUM(0) I_tot_raw_cost, SUM(0) I_tot_burdened_cost, SUM(0) I_tot_quantity, SUM(0) I_tot_labor_hours, SUM(0) I_tot_billable_raw_cost, SUM(0) I_tot_billable_burdened_cost, SUM(0) I_tot_billable_quantity, SUM(0) I_tot_billable_labor_hours, 'PJI' record_source FROM ( SELECT -1 Txn_accum_id, fact.PROJECT_ID, fact.PROJECT_ELEMENT_ID, pacal.NAME pa_period, NULL gl_period, fact.REVENUE, fact.RAW_COST, fact.BRDN_COST, fact.LABOR_HRS, fact.BILL_RAW_COST, fact.BILL_BRDN_COST, fact.BILL_LABOR_HRS FROM pji_fp_xbs_accum_f fact, pa_projects_all proj, pji_org_extr_info org_info, pji_time_cal_period_v pacal WHERE fact.PROJECT_ID = proj.PROJECT_ID AND proj.PJI_SOURCE_FLAG = 'Y' AND proj.ORG_ID = org_info.ORG_ID AND pacal.CALENDAR_ID = org_info.PA_CALENDAR_ID AND fact.CALENDAR_TYPE = 'P' AND fact.PRG_ROLLUP_FLAG = 'N' AND BITAND(fact.CURR_RECORD_TYPE_ID,4) = 4 AND fact.PLAN_VERSION_ID = -1 AND fact.RBS_AGGR_LEVEL = 'T' AND fact.time_id = pacal.cal_period_id UNION ALL SELECT -1 Txn_accum_id, fact.PROJECT_ID, fact.PROJECT_ELEMENT_ID, NULL pa_period, gl.NAME gl_period, fact.REVENUE, fact.RAW_COST, fact.BRDN_COST, fact.LABOR_HRS, fact.BILL_RAW_COST, fact.BILL_BRDN_COST, fact.BILL_LABOR_HRS FROM pji_fp_xbs_accum_f fact, pa_projects_all proj, pji_org_extr_info org_info, pji_time_cal_period_v gl WHERE fact.PROJECT_ID = proj.PROJECT_ID AND proj.PJI_SOURCE_FLAG = 'Y' AND proj.ORG_ID = org_info.ORG_ID AND gl.CALENDAR_ID = org_info.GL_CALENDAR_ID AND fact.CALENDAR_TYPE = 'G' AND fact.PRG_ROLLUP_FLAG = 'N' AND BITAND(fact.CURR_RECORD_TYPE_ID,4) = 4 AND fact.PLAN_VERSION_ID = -1 AND fact.RBS_AGGR_LEVEL = 'T' AND fact.time_id = gl.cal_period_id ) fact GROUP BY PROJECT_ID, PROJECT_ELEMENT_ID, pa_period, gl_period

View Text - HTML Formatted

SELECT ACCUM.TXN_ACCUM_ID
, ACCUM.PROJECT_ID
, ACCUM.TASK_ID
, ACCUM.PA_PERIOD
, ACCUM.GL_PERIOD
, ACCUM.TOT_REVENUE
, ACCUM.TOT_RAW_COST
, ACCUM.TOT_BURDENED_COST
, ACCUM.TOT_QUANTITY
, ACCUM.TOT_LABOR_HOURS
, ACCUM.TOT_BILLABLE_RAW_COST
, ACCUM.TOT_BILLABLE_BURDENED_COST
, ACCUM.TOT_BILLABLE_QUANTITY
, ACCUM.TOT_BILLABLE_LABOR_HOURS
, ACCUM.I_TOT_REVENUE
, ACCUM.I_TOT_RAW_COST
, ACCUM.I_TOT_BURDENED_COST
, ACCUM.I_TOT_QUANTITY
, ACCUM.I_TOT_LABOR_HOURS
, ACCUM.I_TOT_BILLABLE_RAW_COST
, ACCUM.I_TOT_BILLABLE_BURDENED_COST
, ACCUM.I_TOT_BILLABLE_QUANTITY
, ACCUM.I_TOT_BILLABLE_LABOR_HOURS
, 'PA' RECORD_SOURCE
FROM PA_TXN_ACCUM ACCUM
, PA_PROJECTS_ALL PRJ
WHERE PRJ.PJI_SOURCE_FLAG IS NULL
AND ACCUM.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT -1 TXN_ACCUM_ID
, FACT.PROJECT_ID
, FACT.PROJECT_ELEMENT_ID TASK_ID
, FACT.PA_PERIOD
, FACT.GL_PERIOD
, SUM(FACT.REVENUE) TOT_REVENUE
, SUM(FACT.RAW_COST) TOT_RAW_COST
, SUM(FACT.BRDN_COST) TOT_BURDENED_COST
, SUM(FACT.LABOR_HRS) TOT_QUANTITY
, SUM(FACT.LABOR_HRS) TOT_LABOR_HOURS
, SUM(FACT.BILL_RAW_COST) TOT_BILLABLE_RAW_COST
, SUM(FACT.BILL_BRDN_COST) TOT_BILLABLE_BURDENED_COST
, SUM(FACT.BILL_LABOR_HRS) TOT_BILLABLE_QUANTITY
, SUM(FACT.BILL_LABOR_HRS) TOT_BILLABLE_LABOR_HOURS
, SUM(0) I_TOT_REVENUE
, SUM(0) I_TOT_RAW_COST
, SUM(0) I_TOT_BURDENED_COST
, SUM(0) I_TOT_QUANTITY
, SUM(0) I_TOT_LABOR_HOURS
, SUM(0) I_TOT_BILLABLE_RAW_COST
, SUM(0) I_TOT_BILLABLE_BURDENED_COST
, SUM(0) I_TOT_BILLABLE_QUANTITY
, SUM(0) I_TOT_BILLABLE_LABOR_HOURS
, 'PJI' RECORD_SOURCE
FROM ( SELECT -1 TXN_ACCUM_ID
, FACT.PROJECT_ID
, FACT.PROJECT_ELEMENT_ID
, PACAL.NAME PA_PERIOD
, NULL GL_PERIOD
, FACT.REVENUE
, FACT.RAW_COST
, FACT.BRDN_COST
, FACT.LABOR_HRS
, FACT.BILL_RAW_COST
, FACT.BILL_BRDN_COST
, FACT.BILL_LABOR_HRS
FROM PJI_FP_XBS_ACCUM_F FACT
, PA_PROJECTS_ALL PROJ
, PJI_ORG_EXTR_INFO ORG_INFO
, PJI_TIME_CAL_PERIOD_V PACAL
WHERE FACT.PROJECT_ID = PROJ.PROJECT_ID
AND PROJ.PJI_SOURCE_FLAG = 'Y'
AND PROJ.ORG_ID = ORG_INFO.ORG_ID
AND PACAL.CALENDAR_ID = ORG_INFO.PA_CALENDAR_ID
AND FACT.CALENDAR_TYPE = 'P'
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 4) = 4
AND FACT.PLAN_VERSION_ID = -1
AND FACT.RBS_AGGR_LEVEL = 'T'
AND FACT.TIME_ID = PACAL.CAL_PERIOD_ID UNION ALL SELECT -1 TXN_ACCUM_ID
, FACT.PROJECT_ID
, FACT.PROJECT_ELEMENT_ID
, NULL PA_PERIOD
, GL.NAME GL_PERIOD
, FACT.REVENUE
, FACT.RAW_COST
, FACT.BRDN_COST
, FACT.LABOR_HRS
, FACT.BILL_RAW_COST
, FACT.BILL_BRDN_COST
, FACT.BILL_LABOR_HRS
FROM PJI_FP_XBS_ACCUM_F FACT
, PA_PROJECTS_ALL PROJ
, PJI_ORG_EXTR_INFO ORG_INFO
, PJI_TIME_CAL_PERIOD_V GL
WHERE FACT.PROJECT_ID = PROJ.PROJECT_ID
AND PROJ.PJI_SOURCE_FLAG = 'Y'
AND PROJ.ORG_ID = ORG_INFO.ORG_ID
AND GL.CALENDAR_ID = ORG_INFO.GL_CALENDAR_ID
AND FACT.CALENDAR_TYPE = 'G'
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 4) = 4
AND FACT.PLAN_VERSION_ID = -1
AND FACT.RBS_AGGR_LEVEL = 'T'
AND FACT.TIME_ID = GL.CAL_PERIOD_ID ) FACT GROUP BY PROJECT_ID
, PROJECT_ELEMENT_ID
, PA_PERIOD
, GL_PERIOD