PJS_FP_FACT

Details

Schema: FUSION
Object owner: PJS
Object type: VIEW

Columns

Name
SCENARIO TIME_VIEW_NAME CURRENCY_CODE PLAN_CLASS_CODE CALENDAR_TYPE PLAN_TYPE_ID PROJECT_ID TIME_ID PROJECT_ELEMENT_ID PROJECT_ELEMENT_ID_FLAG RBS_VERSION_ID RBS_ELEMENT_ID RBS_ELEMENT_ID_FLAG CURRENCY_TYPE PLAN_VERSION_ID PLAN_VERSION_ID_FLAG RAW_COST REVENUE BILL_BRDN_COST BILL_EQUIPMENT_HRS BILL_LABOR_BRDN_COST BILL_LABOR_HRS BILL_LABOR_RAW_COST BILL_RAW_COST BRDN_COST CAP_BRDN_COST CAP_RAW_COST EQUIPMENT_BRDN_COST EQUIPMENT_HRS EQUIPMENT_RAW_COST EXP_BRDN_COST EXP_RAW_COST LABOR_BRDN_COST LABOR_HRS LABOR_RAW_COST LABOR_REVENUE OTH_COMMITTED_COST PO_COMMITTED_COST PR_COMMITTED_COST SUP_INV_COMMITTED_COST INVOICE_AMT RETIRE_RAW_COST RETIRE_BRDN_COST CUSTOM1 CUSTOM2 CUSTOM3 CUSTOM4 CUSTOM5 CUSTOM6 CUSTOM7 CUSTOM8 CUSTOM9 CUSTOM10 CUSTOM11 CUSTOM12 CUSTOM13 CUSTOM14 CUSTOM15 TO_COMMITTED_COST

Name

SCENARIO

TIME_VIEW_NAME

CURRENCY_CODE

PLAN_CLASS_CODE

CALENDAR_TYPE

PLAN_TYPE_ID

PROJECT_ID

TIME_ID

PROJECT_ELEMENT_ID

PROJECT_ELEMENT_ID_FLAG

RBS_VERSION_ID

RBS_ELEMENT_ID

RBS_ELEMENT_ID_FLAG

CURRENCY_TYPE

PLAN_VERSION_ID

PLAN_VERSION_ID_FLAG

RAW_COST

REVENUE

BILL_BRDN_COST

BILL_EQUIPMENT_HRS

BILL_LABOR_BRDN_COST

BILL_LABOR_HRS

BILL_LABOR_RAW_COST

BILL_RAW_COST

BRDN_COST

CAP_BRDN_COST

CAP_RAW_COST

EQUIPMENT_BRDN_COST

EQUIPMENT_HRS

EQUIPMENT_RAW_COST

EXP_BRDN_COST

EXP_RAW_COST

LABOR_BRDN_COST

LABOR_HRS

LABOR_RAW_COST

LABOR_REVENUE

OTH_COMMITTED_COST

PO_COMMITTED_COST

PR_COMMITTED_COST

SUP_INV_COMMITTED_COST

INVOICE_AMT

RETIRE_RAW_COST

RETIRE_BRDN_COST

CUSTOM1

CUSTOM2

CUSTOM3

CUSTOM4

CUSTOM5

CUSTOM6

CUSTOM7

CUSTOM8

CUSTOM9

CUSTOM10

CUSTOM11

CUSTOM12

CUSTOM13

CUSTOM14

CUSTOM15

TO_COMMITTED_COST

Query

SQL_Statement
select f.SCENARIO, f.TIME_VIEW_NAME, f.CURRENCY_CODE, 'X' PLAN_CLASS_CODE, f.CALENDAR_TYPE, -1 PLAN_TYPE_ID, f.PROJECT_ID, f.TIME_ID, f.PROJECT_ELEMENT_ID, f.PROJECT_ELEMENT_ID_FLAG, f.RBS_VERSION_ID, f.RBS_ElEMENT_ID, f.RBS_ElEMENT_ID_FLAG, f.CURRENCY_TYPE, 1 PLAN_VERSION_ID, '0' PLAN_VERSION_ID_FLAG, sum(f.RAW_COST) RAW_COST, sum(f.REVENUE) REVENUE, sum(f.BILL_BRDN_COST) BILL_BRDN_COST, sum(f.BILL_EQUIPMENT_HRS) BILL_EQUIPMENT_HRS, sum(f.BILL_LABOR_BRDN_COST) BILL_LABOR_BRDN_COST, sum(f.BILL_LABOR_HRS) BILL_LABOR_HRS, sum(f.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST, sum(f.BILL_RAW_COST) BILL_RAW_COST, sum(f.BRDN_COST) BRDN_COST, sum(f.CAP_BRDN_COST) CAP_BRDN_COST, sum(f.CAP_RAW_COST) CAP_RAW_COST, sum(f.EQUIPMENT_BRDN_COST) EQUIPMENT_BRDN_COST, sum(f.EQUIPMENT_HRS) EQUIPMENT_HRS, sum(f.EQUIPMENT_RAW_COST) EQUIPMENT_RAW_COST, sum(f.EXP_BRDN_COST) EXP_BRDN_COST, sum(f.EXP_RAW_COST) EXP_RAW_COST, sum(f.LABOR_BRDN_COST) LABOR_BRDN_COST, sum(f.LABOR_HRS) LABOR_HRS, sum(f.LABOR_RAW_COST) LABOR_RAW_COST, sum(f.LABOR_REVENUE) LABOR_REVENUE, sum(f.OTH_COMMITTED_COST) OTH_COMMITTED_COST, sum(f.PO_COMMITTED_COST) PO_COMMITTED_COST, sum(f.PR_COMMITTED_COST) PR_COMMITTED_COST, sum(f.SUP_INV_COMMITTED_COST) SUP_INV_COMMITTED_COST, sum(f.INVOICE_AMT) INVOICE_AMT, sum(f.RETIRE_RAW_COST) RETIRE_RAW_COST, sum(f.RETIRE_BRDN_COST) RETIRE_BRDN_COST, sum(f.CUSTOM1) CUSTOM1, sum(f.CUSTOM2) CUSTOM2, sum(f.CUSTOM3) CUSTOM3, sum(f.CUSTOM4) CUSTOM4, sum(f.CUSTOM5) CUSTOM5, sum(f.CUSTOM6) CUSTOM6, sum(f.CUSTOM7) CUSTOM7, sum(f.CUSTOM8) CUSTOM8, sum(f.CUSTOM9) CUSTOM9, sum(f.CUSTOM10) CUSTOM10, sum(f.CUSTOM11) CUSTOM11, sum(f.CUSTOM12) CUSTOM12, sum(f.CUSTOM13) CUSTOM13, sum(f.CUSTOM14) CUSTOM14, sum(f.CUSTOM15) CUSTOM15, sum(f.TO_COMMITTED_COST) TO_COMMITTED_COST from ( select SCENARIO, 'B' TIME_VIEW_NAME, PROJECT_ID, 1 PLAN_VERSION_ID, '0' PLAN_VERSION_ID_FLAG, to_number(null) PLAN_TYPE_ID, null PLAN_CLASS_CODE, TIME_ID, CALENDAR_TYPE, PROJECT_ELEMENT_ID, PROJECT_ELEMENT_ID_FLAG, RBS_VERSION_ID, RBS_ElEMENT_ID, RBS_ElEMENT_ID_FLAG, CURRENCY_TYPE, CURRENCY_CODE, RAW_COST, REVENUE, BILL_BRDN_COST, BILL_EQUIPMENT_HRS, BILL_LABOR_BRDN_COST, BILL_LABOR_HRS, BILL_LABOR_RAW_COST, BILL_RAW_COST, BRDN_COST, CAP_BRDN_COST, CAP_RAW_COST, EQUIPMENT_BRDN_COST, EQUIPMENT_HRS, EQUIPMENT_RAW_COST, EXP_BRDN_COST, EXP_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_RAW_COST, LABOR_REVENUE, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST, INVOICE_AMT, RETIRE_RAW_COST, RETIRE_BRDN_COST, to_number(null) CUSTOM1, to_number(null) CUSTOM2, to_number(null) CUSTOM3, to_number(null) CUSTOM4, to_number(null) CUSTOM5, to_number(null) CUSTOM6, to_number(null) CUSTOM7, to_number(null) CUSTOM8, to_number(null) CUSTOM9, to_number(null) CUSTOM10, to_number(null) CUSTOM11, to_number(null) CUSTOM12, to_number(null) CUSTOM13, to_number(null) CUSTOM14, to_number(null) CUSTOM15, TO_COMMITTED_COST from PJS_OLAP_QUEUE olap, PJS_FP_SUM_FIN3_1 summ where 1=1 /* Bug 36263815 and olap.PARTITIONS = summ.WORKER_ID / and olap.STATUS = 'PROCESSING' and summ.project_id in (select project_id from PJS_SUM_SCOPE_PROJECT where worker_id = olap.PARTITIONS) and summ.WORKER_ID IN ( select DECODE(LTRIM(value, '0123456789'), NULL, to_number(value), NULL) from pjs_system_parameters where name like 'PJS_SUM'\|\|olap.PARTITIONS\|\|'$ESSBASE_DATA_%' union all select DECODE(LTRIM(olap.PARTITIONS, '0123456789'), NULL, to_number(olap.PARTITIONS), NULL) from dual ) union all select SCENARIO, 'B' TIME_VIEW_NAME, PROJECT_ID, PLAN_VERSION_ID, PLAN_VERSION_ID_FLAG, PLAN_TYPE_ID, PLAN_CLASS_CODE, TIME_ID, CALENDAR_TYPE, PROJECT_ELEMENT_ID, PROJECT_ELEMENT_ID_FLAG, RBS_VERSION_ID, RBS_ElEMENT_ID, RBS_ElEMENT_ID_FLAG, CURRENCY_TYPE, CURRENCY_CODE, RAW_COST, REVENUE, BILL_BRDN_COST, BILL_EQUIPMENT_HRS, BILL_LABOR_BRDN_COST, BILL_LABOR_HRS, BILL_LABOR_RAW_COST, BILL_RAW_COST, BRDN_COST, CAP_BRDN_COST, CAP_RAW_COST, EQUIPMENT_BRDN_COST, EQUIPMENT_HRS, EQUIPMENT_RAW_COST, EXP_BRDN_COST, EXP_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_RAW_COST, LABOR_REVENUE, to_number(null) SUP_INV_COMMITTED_COST, to_number(null) PO_COMMITED_COST, to_number(null) PR_COMMITED_COST, to_number(null) OTH_COMMITTED_COST, to_number(null) INVOICE_AMT, to_number(null) RETIRE_RAW_COST, to_number(null) RETIRE_BRDN_COST, to_number(null) CUSTOM1, to_number(null) CUSTOM2, to_number(null) CUSTOM3, to_number(null) CUSTOM4, to_number(null) CUSTOM5, to_number(null) CUSTOM6, to_number(null) CUSTOM7, to_number(null) CUSTOM8, to_number(null) CUSTOM9, to_number(null) CUSTOM10, to_number(null) CUSTOM11, to_number(null) CUSTOM12, to_number(null) CUSTOM13, to_number(null) CUSTOM14, to_number(null) CUSTOM15, to_number(null) TO_COMMITTED_COST from PJS_OLAP_QUEUE olap, PJS_FP_SUM_PLAN1_1 summ where 1=1 / Bug 36263815 and olap.PARTITIONS = summ.WORKER_ID / and olap.STATUS = 'PROCESSING' and summ.project_id in (select project_id from PJS_SUM_SCOPE_PROJECT where worker_id = olap.PARTITIONS) and summ.WORKER_ID IN ( select DECODE(LTRIM(value, '0123456789'), NULL, to_number(value), NULL) from pjs_system_parameters where name like 'PJS_SUM'\|\|olap.PARTITIONS\|\|'$ESSBASE_DATA_%' union all select DECODE(LTRIM(olap.PARTITIONS, '0123456789'), NULL, to_number(olap.PARTITIONS), NULL) from dual ) / union all select SCENARIO, 'B' TIME_VIEW_NAME, PROJECT_ID, 1 PLAN_VERSION_ID, '0' PLAN_VERSION_ID_FLAG, to_number(null) PLAN_TYPE_ID, null PLAN_CLASS_CODE, TIME_ID, CALENDAR_TYPE, PROJECT_ELEMENT_ID, PROJECT_ELEMENT_ID_FLAG, RBS_VERSION_ID, RBS_ElEMENT_ID, RBS_ElEMENT_ID_FLAG, CURRENCY_TYPE, CURRENCY_CODE, to_number(null) RAW_COST, to_number(null) REVENUE, to_number(null) BILL_BRDN_COST, to_number(null) BILL_EQUIPMENT_HRS, to_number(null) BILL_LABOR_BRDN_COST, to_number(null) BILL_LABOR_HRS, to_number(null) BILL_LABOR_RAW_COST, to_number(null) BILL_RAW_COST, to_number(null) BRDN_COST, to_number(null) CAP_BRDN_COST, to_number(null) CAP_RAW_COST, to_number(null) EQUIPMENT_BRDN_COST, to_number(null) EQUIPMENT_HRS, to_number(null) EQUIPMENT_RAW_COST, to_number(null) EXP_BRDN_COST, to_number(null) EXP_RAW_COST, to_number(null) LABOR_BRDN_COST, to_number(null) LABOR_HRS, to_number(null) LABOR_RAW_COST, to_number(null) LABOR_REVENUE, to_number(null) SUP_INV_COMMITTED_COST, to_number(null) PO_COMMITED_COST, to_number(null) PR_COMMITED_COST, to_number(null) OTH_COMMITTED_COST, to_number(null) INVOICE_AMT, to_number(null) RETIRE_RAW_COST, to_number(null) RETIRE_BRDN_COST, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6, CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 from PJS_OLAP_QUEUE olap, PJS_SUM_CUSTOM cust where olap.PARTITIONS = cust.WORKER_ID and olap.STATUS = 'PROCESSING' union all select SCENARIO, 'B' TIME_VIEW_NAME, PROJECT_ID, 1 PLAN_VERSION_ID, '0' PLAN_VERSION_ID_FLAG, to_number(null) PLAN_TYPE_ID, null PLAN_CLASS_CODE, TIME_ID, CALENDAR_TYPE, PROJECT_ELEMENT_ID, PROJECT_ELEMENT_ID_FLAG, RBS_VERSION_ID, RBS_ElEMENT_ID, RBS_ElEMENT_ID_FLAG, CURRENCY_TYPE, CURRENCY_CODE, to_number(null) RAW_COST, to_number(null) REVENUE, to_number(null) BILL_BRDN_COST, to_number(null) BILL_EQUIPMENT_HRS, to_number(null) BILL_LABOR_BRDN_COST, to_number(null) BILL_LABOR_HRS, to_number(null) BILL_LABOR_RAW_COST, to_number(null) BILL_RAW_COST, to_number(null) BRDN_COST, to_number(null) CAP_BRDN_COST, to_number(null) CAP_RAW_COST, to_number(null) EQUIPMENT_BRDN_COST, to_number(null) EQUIPMENT_HRS, to_number(null) EQUIPMENT_RAW_COST, to_number(null) EXP_BRDN_COST, to_number(null) EXP_RAW_COST, to_number(null) LABOR_BRDN_COST, to_number(null) LABOR_HRS, to_number(null) LABOR_RAW_COST, to_number(null) LABOR_REVENUE, to_number(null) SUP_INV_COMMITTED_COST, to_number(null) PO_COMMITED_COST, to_number(null) PR_COMMITED_COST, to_number(null) OTH_COMMITTED_COST, to_number(null) INVOICE_AMT, to_number(null) RETIRE_RAW_COST, to_number(null) RETIRE_BRDN_COST, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6, CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 from PJS_SYSTEM_PARAMETERS param, PJS_CUSTOM_F f where param.NAME = 'EXPOSE_EXISTING_SUMMARY_DATA' and param.VALUE = 'YES' */ ) f, PJF_PROJECTS_ALL_B prj, PJS_BU_INFO bu, PJF_PU_REPORTING_SETTINGS pu where f.CURRENCY_TYPE <> 'T' and f.PROJECT_ID = prj.PROJECT_ID and bu.BUSINESS_UNIT_ID = prj.ORG_ID and pu.PU_ID = prj.PROJECT_UNIT_ID and ((nvl(bu.GL_CALENDAR_ID, -1) <> nvl(bu.PA_CALENDAR_ID, -1)) or (bu.GL_CALENDAR_ID = bu.PA_CALENDAR_ID and ((pu.PA_CALENDAR_FLAG like '%Y' and pu.GL_CALENDAR_FLAG not like '%Y' and f.CALENDAR_TYPE = 'P') or (not (pu.PA_CALENDAR_FLAG like '%Y' and pu.GL_CALENDAR_FLAG not like '%Y') and f.CALENDAR_TYPE = 'G')))) group by f.SCENARIO, f.TIME_VIEW_NAME, f.CURRENCY_CODE, f.CALENDAR_TYPE, f.PROJECT_ID, f.TIME_ID, f.PROJECT_ELEMENT_ID, f.PROJECT_ELEMENT_ID_FLAG, f.RBS_VERSION_ID, f.RBS_ElEMENT_ID, f.RBS_ElEMENT_ID_FLAG, f.CURRENCY_TYPE

SQL_Statement

select

f.SCENARIO,

f.TIME_VIEW_NAME,

f.CURRENCY_CODE,

'X' PLAN_CLASS_CODE,

f.CALENDAR_TYPE,

-1 PLAN_TYPE_ID,

f.PROJECT_ID,

f.TIME_ID,

f.PROJECT_ELEMENT_ID,

f.PROJECT_ELEMENT_ID_FLAG,

f.RBS_VERSION_ID,

f.RBS_ElEMENT_ID,

f.RBS_ElEMENT_ID_FLAG,

f.CURRENCY_TYPE,

1 PLAN_VERSION_ID,

'0' PLAN_VERSION_ID_FLAG,

sum(f.RAW_COST) RAW_COST,

sum(f.REVENUE) REVENUE,

sum(f.BILL_BRDN_COST) BILL_BRDN_COST,

sum(f.BILL_EQUIPMENT_HRS) BILL_EQUIPMENT_HRS,

sum(f.BILL_LABOR_BRDN_COST) BILL_LABOR_BRDN_COST,

sum(f.BILL_LABOR_HRS) BILL_LABOR_HRS,

sum(f.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,

sum(f.BILL_RAW_COST) BILL_RAW_COST,

sum(f.BRDN_COST) BRDN_COST,

sum(f.CAP_BRDN_COST) CAP_BRDN_COST,

sum(f.CAP_RAW_COST) CAP_RAW_COST,

sum(f.EQUIPMENT_BRDN_COST) EQUIPMENT_BRDN_COST,

sum(f.EQUIPMENT_HRS) EQUIPMENT_HRS,

sum(f.EQUIPMENT_RAW_COST) EQUIPMENT_RAW_COST,

sum(f.EXP_BRDN_COST) EXP_BRDN_COST,

sum(f.EXP_RAW_COST) EXP_RAW_COST,

sum(f.LABOR_BRDN_COST) LABOR_BRDN_COST,

sum(f.LABOR_HRS) LABOR_HRS,

sum(f.LABOR_RAW_COST) LABOR_RAW_COST,

sum(f.LABOR_REVENUE) LABOR_REVENUE,

sum(f.OTH_COMMITTED_COST) OTH_COMMITTED_COST,

sum(f.PO_COMMITTED_COST) PO_COMMITTED_COST,

sum(f.PR_COMMITTED_COST) PR_COMMITTED_COST,

sum(f.SUP_INV_COMMITTED_COST) SUP_INV_COMMITTED_COST,

sum(f.INVOICE_AMT) INVOICE_AMT,

sum(f.RETIRE_RAW_COST) RETIRE_RAW_COST,

sum(f.RETIRE_BRDN_COST) RETIRE_BRDN_COST,

sum(f.CUSTOM1) CUSTOM1,

sum(f.CUSTOM2) CUSTOM2,

sum(f.CUSTOM3) CUSTOM3,

sum(f.CUSTOM4) CUSTOM4,

sum(f.CUSTOM5) CUSTOM5,

sum(f.CUSTOM6) CUSTOM6,

sum(f.CUSTOM7) CUSTOM7,

sum(f.CUSTOM8) CUSTOM8,

sum(f.CUSTOM9) CUSTOM9,

sum(f.CUSTOM10) CUSTOM10,

sum(f.CUSTOM11) CUSTOM11,

sum(f.CUSTOM12) CUSTOM12,

sum(f.CUSTOM13) CUSTOM13,

sum(f.CUSTOM14) CUSTOM14,

sum(f.CUSTOM15) CUSTOM15,

sum(f.TO_COMMITTED_COST) TO_COMMITTED_COST

from

(

select

SCENARIO,

'B' TIME_VIEW_NAME,

PROJECT_ID,

1 PLAN_VERSION_ID,

'0' PLAN_VERSION_ID_FLAG,

to_number(null) PLAN_TYPE_ID,

null PLAN_CLASS_CODE,

TIME_ID,

CALENDAR_TYPE,

PROJECT_ELEMENT_ID,

PROJECT_ELEMENT_ID_FLAG,

RBS_VERSION_ID,

RBS_ElEMENT_ID,

RBS_ElEMENT_ID_FLAG,

CURRENCY_TYPE,

CURRENCY_CODE,

RAW_COST,

REVENUE,

BILL_BRDN_COST,

BILL_EQUIPMENT_HRS,

BILL_LABOR_BRDN_COST,

BILL_LABOR_HRS,

BILL_LABOR_RAW_COST,

BILL_RAW_COST,

BRDN_COST,

CAP_BRDN_COST,

CAP_RAW_COST,

EQUIPMENT_BRDN_COST,

EQUIPMENT_HRS,

EQUIPMENT_RAW_COST,

EXP_BRDN_COST,

EXP_RAW_COST,

LABOR_BRDN_COST,

LABOR_HRS,

LABOR_RAW_COST,

LABOR_REVENUE,

SUP_INV_COMMITTED_COST,

PO_COMMITTED_COST,

PR_COMMITTED_COST,

OTH_COMMITTED_COST,

INVOICE_AMT,

RETIRE_RAW_COST,

RETIRE_BRDN_COST,

to_number(null) CUSTOM1,

to_number(null) CUSTOM2,

to_number(null) CUSTOM3,

to_number(null) CUSTOM4,

to_number(null) CUSTOM5,

to_number(null) CUSTOM6,

to_number(null) CUSTOM7,

to_number(null) CUSTOM8,

to_number(null) CUSTOM9,

to_number(null) CUSTOM10,

to_number(null) CUSTOM11,

to_number(null) CUSTOM12,

to_number(null) CUSTOM13,

to_number(null) CUSTOM14,

to_number(null) CUSTOM15,

TO_COMMITTED_COST

from

PJS_OLAP_QUEUE olap,

PJS_FP_SUM_FIN3_1 summ

where

1=1

/* Bug 36263815 and olap.PARTITIONS = summ.WORKER_ID */

and olap.STATUS = 'PROCESSING'

and summ.project_id in (select project_id from PJS_SUM_SCOPE_PROJECT where worker_id = olap.PARTITIONS)

and summ.WORKER_ID IN

( select DECODE(LTRIM(value, '0123456789'), NULL, to_number(value), NULL)

from pjs_system_parameters

where name like 'PJS_SUM'||olap.PARTITIONS||'$ESSBASE_DATA_%'

union all

select DECODE(LTRIM(olap.PARTITIONS, '0123456789'), NULL, to_number(olap.PARTITIONS), NULL)

from dual

)

union all

select

SCENARIO,

'B' TIME_VIEW_NAME,

PROJECT_ID,

PLAN_VERSION_ID,

PLAN_VERSION_ID_FLAG,

PLAN_TYPE_ID,

PLAN_CLASS_CODE,

TIME_ID,

CALENDAR_TYPE,

PROJECT_ELEMENT_ID,

PROJECT_ELEMENT_ID_FLAG,

RBS_VERSION_ID,

RBS_ElEMENT_ID,

RBS_ElEMENT_ID_FLAG,

CURRENCY_TYPE,

CURRENCY_CODE,

RAW_COST,

REVENUE,

BILL_BRDN_COST,

BILL_EQUIPMENT_HRS,

BILL_LABOR_BRDN_COST,

BILL_LABOR_HRS,

BILL_LABOR_RAW_COST,

BILL_RAW_COST,

BRDN_COST,

CAP_BRDN_COST,

CAP_RAW_COST,

EQUIPMENT_BRDN_COST,

EQUIPMENT_HRS,

EQUIPMENT_RAW_COST,

EXP_BRDN_COST,

EXP_RAW_COST,

LABOR_BRDN_COST,

LABOR_HRS,

LABOR_RAW_COST,

LABOR_REVENUE,

to_number(null) SUP_INV_COMMITTED_COST,

to_number(null) PO_COMMITED_COST,

to_number(null) PR_COMMITED_COST,

to_number(null) OTH_COMMITTED_COST,

to_number(null) INVOICE_AMT,

to_number(null) RETIRE_RAW_COST,

to_number(null) RETIRE_BRDN_COST,

to_number(null) CUSTOM1,

to_number(null) CUSTOM2,

to_number(null) CUSTOM3,

to_number(null) CUSTOM4,

to_number(null) CUSTOM5,

to_number(null) CUSTOM6,

to_number(null) CUSTOM7,

to_number(null) CUSTOM8,

to_number(null) CUSTOM9,

to_number(null) CUSTOM10,

to_number(null) CUSTOM11,

to_number(null) CUSTOM12,

to_number(null) CUSTOM13,

to_number(null) CUSTOM14,

to_number(null) CUSTOM15,

to_number(null) TO_COMMITTED_COST

from

PJS_OLAP_QUEUE olap,

PJS_FP_SUM_PLAN1_1 summ

where

1=1

/* Bug 36263815 and olap.PARTITIONS = summ.WORKER_ID */

and olap.STATUS = 'PROCESSING'

and summ.project_id in (select project_id from PJS_SUM_SCOPE_PROJECT where worker_id = olap.PARTITIONS)

and summ.WORKER_ID IN

( select DECODE(LTRIM(value, '0123456789'), NULL, to_number(value), NULL)

from pjs_system_parameters

where name like 'PJS_SUM'||olap.PARTITIONS||'$ESSBASE_DATA_%'

union all

select DECODE(LTRIM(olap.PARTITIONS, '0123456789'), NULL, to_number(olap.PARTITIONS), NULL)

from dual

)

/*

union all

select

SCENARIO,

'B' TIME_VIEW_NAME,

PROJECT_ID,

1 PLAN_VERSION_ID,

'0' PLAN_VERSION_ID_FLAG,

to_number(null) PLAN_TYPE_ID,

null PLAN_CLASS_CODE,

TIME_ID,

CALENDAR_TYPE,

PROJECT_ELEMENT_ID,

PROJECT_ELEMENT_ID_FLAG,

RBS_VERSION_ID,

RBS_ElEMENT_ID,

RBS_ElEMENT_ID_FLAG,

CURRENCY_TYPE,

CURRENCY_CODE,

to_number(null) RAW_COST,

to_number(null) REVENUE,

to_number(null) BILL_BRDN_COST,

to_number(null) BILL_EQUIPMENT_HRS,

to_number(null) BILL_LABOR_BRDN_COST,

to_number(null) BILL_LABOR_HRS,

to_number(null) BILL_LABOR_RAW_COST,

to_number(null) BILL_RAW_COST,

to_number(null) BRDN_COST,

to_number(null) CAP_BRDN_COST,

to_number(null) CAP_RAW_COST,

to_number(null) EQUIPMENT_BRDN_COST,

to_number(null) EQUIPMENT_HRS,

to_number(null) EQUIPMENT_RAW_COST,

to_number(null) EXP_BRDN_COST,

to_number(null) EXP_RAW_COST,

to_number(null) LABOR_BRDN_COST,

to_number(null) LABOR_HRS,

to_number(null) LABOR_RAW_COST,

to_number(null) LABOR_REVENUE,

to_number(null) SUP_INV_COMMITTED_COST,

to_number(null) PO_COMMITED_COST,

to_number(null) PR_COMMITED_COST,

to_number(null) OTH_COMMITTED_COST,

to_number(null) INVOICE_AMT,

to_number(null) RETIRE_RAW_COST,

to_number(null) RETIRE_BRDN_COST,

CUSTOM1,

CUSTOM2,

CUSTOM3,

CUSTOM4,

CUSTOM5,

CUSTOM6,

CUSTOM7,

CUSTOM8,

CUSTOM9,

CUSTOM10,

CUSTOM11,

CUSTOM12,

CUSTOM13,

CUSTOM14,

CUSTOM15

from

PJS_OLAP_QUEUE olap,

PJS_SUM_CUSTOM cust

where

olap.PARTITIONS = cust.WORKER_ID

and olap.STATUS = 'PROCESSING'

union all

select

SCENARIO,

'B' TIME_VIEW_NAME,

PROJECT_ID,

1 PLAN_VERSION_ID,

'0' PLAN_VERSION_ID_FLAG,

to_number(null) PLAN_TYPE_ID,

null PLAN_CLASS_CODE,

TIME_ID,

CALENDAR_TYPE,

PROJECT_ELEMENT_ID,

PROJECT_ELEMENT_ID_FLAG,

RBS_VERSION_ID,

RBS_ElEMENT_ID,

RBS_ElEMENT_ID_FLAG,

CURRENCY_TYPE,

CURRENCY_CODE,

to_number(null) RAW_COST,

to_number(null) REVENUE,

to_number(null) BILL_BRDN_COST,

to_number(null) BILL_EQUIPMENT_HRS,

to_number(null) BILL_LABOR_BRDN_COST,

to_number(null) BILL_LABOR_HRS,

to_number(null) BILL_LABOR_RAW_COST,

to_number(null) BILL_RAW_COST,

to_number(null) BRDN_COST,

to_number(null) CAP_BRDN_COST,

to_number(null) CAP_RAW_COST,

to_number(null) EQUIPMENT_BRDN_COST,

to_number(null) EQUIPMENT_HRS,

to_number(null) EQUIPMENT_RAW_COST,

to_number(null) EXP_BRDN_COST,

to_number(null) EXP_RAW_COST,

to_number(null) LABOR_BRDN_COST,

to_number(null) LABOR_HRS,

to_number(null) LABOR_RAW_COST,

to_number(null) LABOR_REVENUE,

to_number(null) SUP_INV_COMMITTED_COST,

to_number(null) PO_COMMITED_COST,

to_number(null) PR_COMMITED_COST,

to_number(null) OTH_COMMITTED_COST,

to_number(null) INVOICE_AMT,

to_number(null) RETIRE_RAW_COST,

to_number(null) RETIRE_BRDN_COST,

CUSTOM1,

CUSTOM2,

CUSTOM3,

CUSTOM4,

CUSTOM5,

CUSTOM6,

CUSTOM7,

CUSTOM8,

CUSTOM9,

CUSTOM10,

CUSTOM11,

CUSTOM12,

CUSTOM13,

CUSTOM14,

CUSTOM15

from

PJS_SYSTEM_PARAMETERS param,

PJS_CUSTOM_F f

where

param.NAME = 'EXPOSE_EXISTING_SUMMARY_DATA' and

param.VALUE = 'YES'

*/

) f,

PJF_PROJECTS_ALL_B prj,

PJS_BU_INFO bu,

PJF_PU_REPORTING_SETTINGS pu

where

f.CURRENCY_TYPE <> 'T' and

f.PROJECT_ID = prj.PROJECT_ID and

bu.BUSINESS_UNIT_ID = prj.ORG_ID and

pu.PU_ID = prj.PROJECT_UNIT_ID and

((nvl(bu.GL_CALENDAR_ID, -1) <> nvl(bu.PA_CALENDAR_ID, -1)) or

(bu.GL_CALENDAR_ID = bu.PA_CALENDAR_ID and

((pu.PA_CALENDAR_FLAG like '%Y' and

pu.GL_CALENDAR_FLAG not like '%Y' and

f.CALENDAR_TYPE = 'P') or

(not (pu.PA_CALENDAR_FLAG like '%Y' and

pu.GL_CALENDAR_FLAG not like '%Y') and

f.CALENDAR_TYPE = 'G'))))

group by f.SCENARIO,

f.TIME_VIEW_NAME,

f.CURRENCY_CODE,

f.CALENDAR_TYPE,

f.PROJECT_ID,

f.TIME_ID,

f.PROJECT_ELEMENT_ID,

f.PROJECT_ELEMENT_ID_FLAG,

f.RBS_VERSION_ID,

f.RBS_ElEMENT_ID,

f.RBS_ElEMENT_ID_FLAG,

f.CURRENCY_TYPE