eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_ALLOC_TXN_ACCUM_RBS_V

Source

View Text - Preformatted


SELECT -1 Txn_accum_id, fact.PROJECT_ID, fact.PROJECT_ELEMENT_ID, fact.PA_PERIOD, fact.GL_PERIOD, fact.RBS_VERSION_ID, fact.RBS_ELEMENT_ID, fact.UOM, 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, txn.project_id, txn.task_id PROJECT_ELEMENT_ID, cal.NAME pa_period, to_char(null) gl_period, nvl(rbs.STRUCT_VERSION_ID ,-1) RBS_VERSION_ID, nvl(rbs.ELEMENT_ID ,-1) RBS_ELEMENT_ID, et.unit_of_measure UOM, txn.TXN_RAW_COST RAW_COST, txn.TXN_BILL_RAW_COST BILL_RAW_COST, txn.TXN_BRDN_COST BRDN_COST, txn.TXN_BILL_BRDN_COST BILL_BRDN_COST, txn.TXN_REVENUE REVENUE, txn.quantity LABOR_HRS, txn.bill_quantity BILL_LABOR_HRS FROM pji_fp_txn_accum txn, pji_fp_txn_accum_header head, pa_expenditure_types et, pji_time_cal_period_v cal, pji_org_extr_info org, pa_rbs_txn_accum_map rbs, pa_rbs_prj_assignments ass WHERE txn.txn_accum_header_id = head.txn_accum_header_id and et.expenditure_type = head.expenditure_type and ass.project_id = txn.project_id and rbs.TXN_ACCUM_HEADER_ID = txn.TXN_ACCUM_HEADER_ID and rbs.struct_version_id = ass.rbs_version_id and txn.RECVR_PERIOD_TYPE = 'PA' and txn.project_org_id = org.ORG_ID and cal.CALENDAR_ID = org.PA_CALENDAR_ID and txn.recvr_period_id = cal.cal_period_id and head.inventory_item_id = -1 UNION ALL SELECT -1 Txn_accum_id, txn.project_id, txn.task_id PROJECT_ELEMENT_ID, cal.NAME pa_period, to_char(null) gl_period, nvl(rbs.STRUCT_VERSION_ID ,-1) RBS_VERSION_ID, nvl(rbs.ELEMENT_ID ,-1) RBS_ELEMENT_ID, mtl.primary_unit_of_measure UOM, txn.TXN_RAW_COST RAW_COST, txn.TXN_BILL_RAW_COST BILL_RAW_COST, txn.TXN_BRDN_COST BRDN_COST, txn.TXN_BILL_BRDN_COST BILL_BRDN_COST, txn.TXN_REVENUE REVENUE, txn.quantity LABOR_HRS, txn.bill_quantity BILL_LABOR_HRS FROM pji_fp_txn_accum txn, pji_fp_txn_accum_header head, pji_time_cal_period_v cal, pji_org_extr_info org, mtl_system_items_b mtl, pa_rbs_txn_accum_map rbs, pa_rbs_prj_assignments ass WHERE txn.txn_accum_header_id = head.txn_accum_header_id and rbs.TXN_ACCUM_HEADER_ID = txn.TXN_ACCUM_HEADER_ID and ass.project_id = txn.project_id and ass.rbs_version_id = rbs.struct_version_id and txn.RECVR_PERIOD_TYPE = 'PA' and txn.project_org_id = org.ORG_ID and cal.CALENDAR_ID = org.PA_CALENDAR_ID and txn.recvr_period_id = cal.cal_period_id and head.inventory_item_id >0 and mtl.inventory_item_id = head.inventory_item_id and mtl.organization_id = head.expenditure_organization_id UNION ALL SELECT -1 Txn_accum_id, txn.project_id, txn.task_id PROJECT_ELEMENT_ID, to_char(null) pa_period, cal.NAME gl_period, nvl(rbs.STRUCT_VERSION_ID ,-1) RBS_VERSION_ID, nvl(rbs.ELEMENT_ID ,-1) RBS_ELEMENT_ID, et.unit_of_measure UOM, txn.TXN_RAW_COST RAW_COST, txn.TXN_BILL_RAW_COST BILL_RAW_COST, txn.TXN_BRDN_COST BRDN_COST, txn.TXN_BILL_BRDN_COST BILL_BRDN_COST, txn.TXN_REVENUE REVENUE, txn.quantity LABOR_HRS, txn.bill_quantity BILL_LABOR_HRS FROM pji_fp_txn_accum txn, pji_fp_txn_accum_header head, pa_expenditure_types et, pji_time_cal_period_v cal, pji_org_extr_info org, pa_rbs_txn_accum_map rbs, pa_rbs_prj_assignments ass WHERE txn.txn_accum_header_id = head.txn_accum_header_id and et.expenditure_type = head.expenditure_type and rbs.TXN_ACCUM_HEADER_ID = txn.TXN_ACCUM_HEADER_ID and ass.project_id = txn.project_id and ass.rbs_version_id = rbs.struct_version_id and txn.RECVR_PERIOD_TYPE = 'GL' and txn.project_org_id = org.ORG_ID and cal.CALENDAR_ID = org.GL_CALENDAR_ID and txn.recvr_period_id = cal.cal_period_id and head.inventory_item_id = -1 UNION ALL SELECT -1 Txn_accum_id, txn.project_id, txn.task_id PROJECT_ELEMENT_ID, to_char(null) pa_period, cal.NAME gl_period, nvl(rbs.STRUCT_VERSION_ID ,-1) RBS_VERSION_ID, nvl(rbs.ELEMENT_ID ,-1) RBS_ELEMENT_ID, mtl.primary_unit_of_measure UOM, txn.TXN_RAW_COST RAW_COST, txn.TXN_BILL_RAW_COST BILL_RAW_COST, txn.TXN_BRDN_COST BRDN_COST, txn.TXN_BILL_BRDN_COST BILL_BRDN_COST, txn.TXN_REVENUE REVENUE, txn.quantity LABOR_HRS, txn.bill_quantity BILL_LABOR_HRS FROM pji_fp_txn_accum txn, pji_fp_txn_accum_header head, pji_time_cal_period_v cal, pji_org_extr_info org, mtl_system_items_b mtl, pa_rbs_txn_accum_map rbs, pa_rbs_prj_assignments ass WHERE txn.txn_accum_header_id = head.txn_accum_header_id and rbs.TXN_ACCUM_HEADER_ID = txn.TXN_ACCUM_HEADER_ID and ass.project_id = txn.project_id and ass.rbs_version_id = rbs.struct_version_id and txn.RECVR_PERIOD_TYPE = 'GL' and txn.project_org_id = org.ORG_ID and cal.CALENDAR_ID = org.GL_CALENDAR_ID and txn.recvr_period_id = cal.cal_period_id and head.inventory_item_id >0 and mtl.inventory_item_id = head.inventory_item_id and mtl.organization_id = head.expenditure_organization_id) fact GROUP BY PROJECT_ID, PROJECT_ELEMENT_ID, PA_PERIOD, GL_PERIOD, RBS_VERSION_ID, RBS_ELEMENT_ID, UOM

View Text - HTML Formatted

SELECT -1 TXN_ACCUM_ID
, FACT.PROJECT_ID
, FACT.PROJECT_ELEMENT_ID
, FACT.PA_PERIOD
, FACT.GL_PERIOD
, FACT.RBS_VERSION_ID
, FACT.RBS_ELEMENT_ID
, FACT.UOM
, 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
, TXN.PROJECT_ID
, TXN.TASK_ID PROJECT_ELEMENT_ID
, CAL.NAME PA_PERIOD
, TO_CHAR(NULL) GL_PERIOD
, NVL(RBS.STRUCT_VERSION_ID
, -1) RBS_VERSION_ID
, NVL(RBS.ELEMENT_ID
, -1) RBS_ELEMENT_ID
, ET.UNIT_OF_MEASURE UOM
, TXN.TXN_RAW_COST RAW_COST
, TXN.TXN_BILL_RAW_COST BILL_RAW_COST
, TXN.TXN_BRDN_COST BRDN_COST
, TXN.TXN_BILL_BRDN_COST BILL_BRDN_COST
, TXN.TXN_REVENUE REVENUE
, TXN.QUANTITY LABOR_HRS
, TXN.BILL_QUANTITY BILL_LABOR_HRS
FROM PJI_FP_TXN_ACCUM TXN
, PJI_FP_TXN_ACCUM_HEADER HEAD
, PA_EXPENDITURE_TYPES ET
, PJI_TIME_CAL_PERIOD_V CAL
, PJI_ORG_EXTR_INFO ORG
, PA_RBS_TXN_ACCUM_MAP RBS
, PA_RBS_PRJ_ASSIGNMENTS ASS
WHERE TXN.TXN_ACCUM_HEADER_ID = HEAD.TXN_ACCUM_HEADER_ID
AND ET.EXPENDITURE_TYPE = HEAD.EXPENDITURE_TYPE
AND ASS.PROJECT_ID = TXN.PROJECT_ID
AND RBS.TXN_ACCUM_HEADER_ID = TXN.TXN_ACCUM_HEADER_ID
AND RBS.STRUCT_VERSION_ID = ASS.RBS_VERSION_ID
AND TXN.RECVR_PERIOD_TYPE = 'PA'
AND TXN.PROJECT_ORG_ID = ORG.ORG_ID
AND CAL.CALENDAR_ID = ORG.PA_CALENDAR_ID
AND TXN.RECVR_PERIOD_ID = CAL.CAL_PERIOD_ID
AND HEAD.INVENTORY_ITEM_ID = -1 UNION ALL SELECT -1 TXN_ACCUM_ID
, TXN.PROJECT_ID
, TXN.TASK_ID PROJECT_ELEMENT_ID
, CAL.NAME PA_PERIOD
, TO_CHAR(NULL) GL_PERIOD
, NVL(RBS.STRUCT_VERSION_ID
, -1) RBS_VERSION_ID
, NVL(RBS.ELEMENT_ID
, -1) RBS_ELEMENT_ID
, MTL.PRIMARY_UNIT_OF_MEASURE UOM
, TXN.TXN_RAW_COST RAW_COST
, TXN.TXN_BILL_RAW_COST BILL_RAW_COST
, TXN.TXN_BRDN_COST BRDN_COST
, TXN.TXN_BILL_BRDN_COST BILL_BRDN_COST
, TXN.TXN_REVENUE REVENUE
, TXN.QUANTITY LABOR_HRS
, TXN.BILL_QUANTITY BILL_LABOR_HRS
FROM PJI_FP_TXN_ACCUM TXN
, PJI_FP_TXN_ACCUM_HEADER HEAD
, PJI_TIME_CAL_PERIOD_V CAL
, PJI_ORG_EXTR_INFO ORG
, MTL_SYSTEM_ITEMS_B MTL
, PA_RBS_TXN_ACCUM_MAP RBS
, PA_RBS_PRJ_ASSIGNMENTS ASS
WHERE TXN.TXN_ACCUM_HEADER_ID = HEAD.TXN_ACCUM_HEADER_ID
AND RBS.TXN_ACCUM_HEADER_ID = TXN.TXN_ACCUM_HEADER_ID
AND ASS.PROJECT_ID = TXN.PROJECT_ID
AND ASS.RBS_VERSION_ID = RBS.STRUCT_VERSION_ID
AND TXN.RECVR_PERIOD_TYPE = 'PA'
AND TXN.PROJECT_ORG_ID = ORG.ORG_ID
AND CAL.CALENDAR_ID = ORG.PA_CALENDAR_ID
AND TXN.RECVR_PERIOD_ID = CAL.CAL_PERIOD_ID
AND HEAD.INVENTORY_ITEM_ID >0
AND MTL.INVENTORY_ITEM_ID = HEAD.INVENTORY_ITEM_ID
AND MTL.ORGANIZATION_ID = HEAD.EXPENDITURE_ORGANIZATION_ID UNION ALL SELECT -1 TXN_ACCUM_ID
, TXN.PROJECT_ID
, TXN.TASK_ID PROJECT_ELEMENT_ID
, TO_CHAR(NULL) PA_PERIOD
, CAL.NAME GL_PERIOD
, NVL(RBS.STRUCT_VERSION_ID
, -1) RBS_VERSION_ID
, NVL(RBS.ELEMENT_ID
, -1) RBS_ELEMENT_ID
, ET.UNIT_OF_MEASURE UOM
, TXN.TXN_RAW_COST RAW_COST
, TXN.TXN_BILL_RAW_COST BILL_RAW_COST
, TXN.TXN_BRDN_COST BRDN_COST
, TXN.TXN_BILL_BRDN_COST BILL_BRDN_COST
, TXN.TXN_REVENUE REVENUE
, TXN.QUANTITY LABOR_HRS
, TXN.BILL_QUANTITY BILL_LABOR_HRS
FROM PJI_FP_TXN_ACCUM TXN
, PJI_FP_TXN_ACCUM_HEADER HEAD
, PA_EXPENDITURE_TYPES ET
, PJI_TIME_CAL_PERIOD_V CAL
, PJI_ORG_EXTR_INFO ORG
, PA_RBS_TXN_ACCUM_MAP RBS
, PA_RBS_PRJ_ASSIGNMENTS ASS
WHERE TXN.TXN_ACCUM_HEADER_ID = HEAD.TXN_ACCUM_HEADER_ID
AND ET.EXPENDITURE_TYPE = HEAD.EXPENDITURE_TYPE
AND RBS.TXN_ACCUM_HEADER_ID = TXN.TXN_ACCUM_HEADER_ID
AND ASS.PROJECT_ID = TXN.PROJECT_ID
AND ASS.RBS_VERSION_ID = RBS.STRUCT_VERSION_ID
AND TXN.RECVR_PERIOD_TYPE = 'GL'
AND TXN.PROJECT_ORG_ID = ORG.ORG_ID
AND CAL.CALENDAR_ID = ORG.GL_CALENDAR_ID
AND TXN.RECVR_PERIOD_ID = CAL.CAL_PERIOD_ID
AND HEAD.INVENTORY_ITEM_ID = -1 UNION ALL SELECT -1 TXN_ACCUM_ID
, TXN.PROJECT_ID
, TXN.TASK_ID PROJECT_ELEMENT_ID
, TO_CHAR(NULL) PA_PERIOD
, CAL.NAME GL_PERIOD
, NVL(RBS.STRUCT_VERSION_ID
, -1) RBS_VERSION_ID
, NVL(RBS.ELEMENT_ID
, -1) RBS_ELEMENT_ID
, MTL.PRIMARY_UNIT_OF_MEASURE UOM
, TXN.TXN_RAW_COST RAW_COST
, TXN.TXN_BILL_RAW_COST BILL_RAW_COST
, TXN.TXN_BRDN_COST BRDN_COST
, TXN.TXN_BILL_BRDN_COST BILL_BRDN_COST
, TXN.TXN_REVENUE REVENUE
, TXN.QUANTITY LABOR_HRS
, TXN.BILL_QUANTITY BILL_LABOR_HRS
FROM PJI_FP_TXN_ACCUM TXN
, PJI_FP_TXN_ACCUM_HEADER HEAD
, PJI_TIME_CAL_PERIOD_V CAL
, PJI_ORG_EXTR_INFO ORG
, MTL_SYSTEM_ITEMS_B MTL
, PA_RBS_TXN_ACCUM_MAP RBS
, PA_RBS_PRJ_ASSIGNMENTS ASS
WHERE TXN.TXN_ACCUM_HEADER_ID = HEAD.TXN_ACCUM_HEADER_ID
AND RBS.TXN_ACCUM_HEADER_ID = TXN.TXN_ACCUM_HEADER_ID
AND ASS.PROJECT_ID = TXN.PROJECT_ID
AND ASS.RBS_VERSION_ID = RBS.STRUCT_VERSION_ID
AND TXN.RECVR_PERIOD_TYPE = 'GL'
AND TXN.PROJECT_ORG_ID = ORG.ORG_ID
AND CAL.CALENDAR_ID = ORG.GL_CALENDAR_ID
AND TXN.RECVR_PERIOD_ID = CAL.CAL_PERIOD_ID
AND HEAD.INVENTORY_ITEM_ID >0
AND MTL.INVENTORY_ITEM_ID = HEAD.INVENTORY_ITEM_ID
AND MTL.ORGANIZATION_ID = HEAD.EXPENDITURE_ORGANIZATION_ID) FACT GROUP BY PROJECT_ID
, PROJECT_ELEMENT_ID
, PA_PERIOD
, GL_PERIOD
, RBS_VERSION_ID
, RBS_ELEMENT_ID
, UOM