View: PA_ALLOC_TXN_ACCUM_RBS_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_ALLOC_TXN_ACCUM_RBS_V

View Text

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

Columns