[Home] [Help]
71: l_msg_count NUMBER;
72:
73: BEGIN
74:
75: INSERT INTO PJI_FP_AGGR_PJP1
76: (
77: WORKER_ID
78: , PROJECT_ID
79: , PROJECT_ORG_ID
591: l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
592:
593: BEGIN
594:
595: INSERT INTO pji_fp_aggr_pjp1
596: (
597: WORKER_ID
598: , PROJECT_ID
599: , PROJECT_ORG_ID
743: , SUM(fact1.CUSTOM15 )
744: , fact1.line_type
745: , g_default_prg_level
746: , fact1.plan_type_code
747: FROM pji_fp_aggr_pjp1 fact1
748: , pji_fm_extr_plnver4 ver3
749: WHERE
750: fact1.RBS_AGGR_LEVEL = g_lowest_level
751: AND fact1.worker_id = g_worker_id
784: p_extrn_type IN VARCHAR2 := NULL) IS
785:
786: BEGIN
787:
788: INSERT INTO PJI_FP_AGGR_PJP1 pjp1
789: (
790: WORKER_ID
791: , PRG_LEVEL
792: , PROJECT_ID
1216: l_return_status varchar2(1);
1217:
1218: BEGIN
1219:
1220: INSERT INTO PJI_FP_AGGR_PJP1 pjp1
1221: (
1222: WORKER_ID
1223: , PROJECT_ID
1224: , PROJECT_ORG_ID
2731:
2732: PROCEDURE ROLLUP_FPR_RBS_TOP (p_worker_id in number) IS
2733: BEGIN
2734:
2735: insert into PJI_FP_AGGR_PJP1 pjp1_i
2736: (
2737: WORKER_ID,
2738: RECORD_TYPE,
2739: PRG_LEVEL,
3037: sum(pjp1.CUSTOM13) CUSTOM13,
3038: sum(pjp1.CUSTOM14) CUSTOM14,
3039: sum(pjp1.CUSTOM15) CUSTOM15
3040: from
3041: PJI_FP_AGGR_PJP1 pjp1,
3042: (
3043: select
3044: distinct
3045: to_number(log.EVENT_OBJECT) RBS_VERSION_ID,
3131: l_partial_mode := p_partial_mode;
3132: l_fpm_upgrade := p_fpm_upgrade;
3133:
3134: FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3135: tabname => 'PJI_FP_AGGR_PJP1',
3136: percent => 10,
3137: degree => PJI_UTILS.
3138: GET_DEGREE_OF_PARALLELISM);
3139:
3145:
3146: if (p_worker_id =1) then
3147: -- Query for Partition 1
3148:
3149: insert into PJI_FP_AGGR_PJP1 partition(P1)
3150: (
3151: WORKER_ID,
3152: RECORD_TYPE,
3153: PRG_LEVEL,
3928: pjp1.CUSTOM13,
3929: pjp1.CUSTOM14,
3930: pjp1.CUSTOM15
3931: from
3932: PJI_FP_AGGR_PJP1 pjp1,
3933: PJI_PJP_WBS_HEADER wbs_hdr,
3934: PA_XBS_DENORM wbs,
3935: PJI_XBS_DENORM prg,
3936: (
4081: pjp1.CUSTOM13,
4082: pjp1.CUSTOM14,
4083: pjp1.CUSTOM15
4084: from
4085: PJI_FP_AGGR_PJP1 pjp1,
4086: PJI_PJP_WBS_HEADER wbs_hdr,
4087: PJI_XBS_DENORM prg,
4088: (
4089: select 'Y' CB_FLAG,
4460:
4461: elsif (p_worker_id =2) then
4462: -- Query for Partition 2
4463:
4464: insert into PJI_FP_AGGR_PJP1 partition(P2)
4465: (
4466: WORKER_ID,
4467: RECORD_TYPE,
4468: PRG_LEVEL,
5243: pjp1.CUSTOM13,
5244: pjp1.CUSTOM14,
5245: pjp1.CUSTOM15
5246: from
5247: PJI_FP_AGGR_PJP1 pjp1,
5248: PJI_PJP_WBS_HEADER wbs_hdr,
5249: PA_XBS_DENORM wbs,
5250: PJI_XBS_DENORM prg,
5251: (
5396: pjp1.CUSTOM13,
5397: pjp1.CUSTOM14,
5398: pjp1.CUSTOM15
5399: from
5400: PJI_FP_AGGR_PJP1 pjp1,
5401: PJI_PJP_WBS_HEADER wbs_hdr,
5402: PJI_XBS_DENORM prg,
5403: (
5404: select 'Y' CB_FLAG,
5775:
5776: elsif (p_worker_id =3) then
5777: -- Query for Partition 3
5778:
5779: insert into PJI_FP_AGGR_PJP1 partition(P3)
5780: (
5781: WORKER_ID,
5782: RECORD_TYPE,
5783: PRG_LEVEL,
6558: pjp1.CUSTOM13,
6559: pjp1.CUSTOM14,
6560: pjp1.CUSTOM15
6561: from
6562: PJI_FP_AGGR_PJP1 pjp1,
6563: PJI_PJP_WBS_HEADER wbs_hdr,
6564: PA_XBS_DENORM wbs,
6565: PJI_XBS_DENORM prg,
6566: (
6711: pjp1.CUSTOM13,
6712: pjp1.CUSTOM14,
6713: pjp1.CUSTOM15
6714: from
6715: PJI_FP_AGGR_PJP1 pjp1,
6716: PJI_PJP_WBS_HEADER wbs_hdr,
6717: PJI_XBS_DENORM prg,
6718: (
6719: select 'Y' CB_FLAG,
7089: sup_wpa.PROJ_ELEMENT_ID is not null)));
7090: elsif (p_worker_id =4) then
7091: -- Query for Partition 4
7092:
7093: insert into PJI_FP_AGGR_PJP1 partition(P4)
7094: (
7095: WORKER_ID,
7096: RECORD_TYPE,
7097: PRG_LEVEL,
7872: pjp1.CUSTOM13,
7873: pjp1.CUSTOM14,
7874: pjp1.CUSTOM15
7875: from
7876: PJI_FP_AGGR_PJP1 pjp1,
7877: PJI_PJP_WBS_HEADER wbs_hdr,
7878: PA_XBS_DENORM wbs,
7879: PJI_XBS_DENORM prg,
7880: (
8025: pjp1.CUSTOM13,
8026: pjp1.CUSTOM14,
8027: pjp1.CUSTOM15
8028: from
8029: PJI_FP_AGGR_PJP1 pjp1,
8030: PJI_PJP_WBS_HEADER wbs_hdr,
8031: PJI_XBS_DENORM prg,
8032: (
8033: select 'Y' CB_FLAG,
8403: sup_wpa.PROJ_ELEMENT_ID is not null)));
8404: elsif (p_worker_id =5) then
8405: -- Query for Partition 5
8406:
8407: insert into PJI_FP_AGGR_PJP1 partition(P5)
8408: (
8409: WORKER_ID,
8410: RECORD_TYPE,
8411: PRG_LEVEL,
9186: pjp1.CUSTOM13,
9187: pjp1.CUSTOM14,
9188: pjp1.CUSTOM15
9189: from
9190: PJI_FP_AGGR_PJP1 pjp1,
9191: PJI_PJP_WBS_HEADER wbs_hdr,
9192: PA_XBS_DENORM wbs,
9193: PJI_XBS_DENORM prg,
9194: (
9339: pjp1.CUSTOM13,
9340: pjp1.CUSTOM14,
9341: pjp1.CUSTOM15
9342: from
9343: PJI_FP_AGGR_PJP1 pjp1,
9344: PJI_PJP_WBS_HEADER wbs_hdr,
9345: PJI_XBS_DENORM prg,
9346: (
9347: select 'Y' CB_FLAG,
9717: sup_wpa.PROJ_ELEMENT_ID is not null)));
9718: elsif (p_worker_id =6) then
9719: -- Query for Partition 6
9720:
9721: insert into PJI_FP_AGGR_PJP1 partition(P6)
9722: (
9723: WORKER_ID,
9724: RECORD_TYPE,
9725: PRG_LEVEL,
10500: pjp1.CUSTOM13,
10501: pjp1.CUSTOM14,
10502: pjp1.CUSTOM15
10503: from
10504: PJI_FP_AGGR_PJP1 pjp1,
10505: PJI_PJP_WBS_HEADER wbs_hdr,
10506: PA_XBS_DENORM wbs,
10507: PJI_XBS_DENORM prg,
10508: (
10653: pjp1.CUSTOM13,
10654: pjp1.CUSTOM14,
10655: pjp1.CUSTOM15
10656: from
10657: PJI_FP_AGGR_PJP1 pjp1,
10658: PJI_PJP_WBS_HEADER wbs_hdr,
10659: PJI_XBS_DENORM prg,
10660: (
10661: select 'Y' CB_FLAG,
11031: sup_wpa.PROJ_ELEMENT_ID is not null)));
11032: elsif (p_worker_id =7) then
11033: -- Query for Partition 7
11034:
11035: insert into PJI_FP_AGGR_PJP1 partition(P7)
11036: (
11037: WORKER_ID,
11038: RECORD_TYPE,
11039: PRG_LEVEL,
11814: pjp1.CUSTOM13,
11815: pjp1.CUSTOM14,
11816: pjp1.CUSTOM15
11817: from
11818: PJI_FP_AGGR_PJP1 pjp1,
11819: PJI_PJP_WBS_HEADER wbs_hdr,
11820: PA_XBS_DENORM wbs,
11821: PJI_XBS_DENORM prg,
11822: (
11967: pjp1.CUSTOM13,
11968: pjp1.CUSTOM14,
11969: pjp1.CUSTOM15
11970: from
11971: PJI_FP_AGGR_PJP1 pjp1,
11972: PJI_PJP_WBS_HEADER wbs_hdr,
11973: PJI_XBS_DENORM prg,
11974: (
11975: select 'Y' CB_FLAG,
12345: sup_wpa.PROJ_ELEMENT_ID is not null)));
12346: elsif (p_worker_id =8) then
12347: -- Query for Partition 8
12348:
12349: insert into PJI_FP_AGGR_PJP1 partition(P8)
12350: (
12351: WORKER_ID,
12352: RECORD_TYPE,
12353: PRG_LEVEL,
13128: pjp1.CUSTOM13,
13129: pjp1.CUSTOM14,
13130: pjp1.CUSTOM15
13131: from
13132: PJI_FP_AGGR_PJP1 pjp1,
13133: PJI_PJP_WBS_HEADER wbs_hdr,
13134: PA_XBS_DENORM wbs,
13135: PJI_XBS_DENORM prg,
13136: (
13281: pjp1.CUSTOM13,
13282: pjp1.CUSTOM14,
13283: pjp1.CUSTOM15
13284: from
13285: PJI_FP_AGGR_PJP1 pjp1,
13286: PJI_PJP_WBS_HEADER wbs_hdr,
13287: PJI_XBS_DENORM prg,
13288: (
13289: select 'Y' CB_FLAG,
13659: sup_wpa.PROJ_ELEMENT_ID is not null)));
13660: elsif (p_worker_id =9) then
13661: -- Query for Partition 9
13662:
13663: insert into PJI_FP_AGGR_PJP1 partition(P9)
13664: (
13665: WORKER_ID,
13666: RECORD_TYPE,
13667: PRG_LEVEL,
14442: pjp1.CUSTOM13,
14443: pjp1.CUSTOM14,
14444: pjp1.CUSTOM15
14445: from
14446: PJI_FP_AGGR_PJP1 pjp1,
14447: PJI_PJP_WBS_HEADER wbs_hdr,
14448: PA_XBS_DENORM wbs,
14449: PJI_XBS_DENORM prg,
14450: (
14595: pjp1.CUSTOM13,
14596: pjp1.CUSTOM14,
14597: pjp1.CUSTOM15
14598: from
14599: PJI_FP_AGGR_PJP1 pjp1,
14600: PJI_PJP_WBS_HEADER wbs_hdr,
14601: PJI_XBS_DENORM prg,
14602: (
14603: select 'Y' CB_FLAG,
14973: sup_wpa.PROJ_ELEMENT_ID is not null)));
14974: elsif (p_worker_id =10) then
14975: -- Query for Partition 10
14976:
14977: insert into PJI_FP_AGGR_PJP1 partition(P10)
14978: (
14979: WORKER_ID,
14980: RECORD_TYPE,
14981: PRG_LEVEL,
15756: pjp1.CUSTOM13,
15757: pjp1.CUSTOM14,
15758: pjp1.CUSTOM15
15759: from
15760: PJI_FP_AGGR_PJP1 pjp1,
15761: PJI_PJP_WBS_HEADER wbs_hdr,
15762: PA_XBS_DENORM wbs,
15763: PJI_XBS_DENORM prg,
15764: (
15909: pjp1.CUSTOM13,
15910: pjp1.CUSTOM14,
15911: pjp1.CUSTOM15
15912: from
15913: PJI_FP_AGGR_PJP1 pjp1,
15914: PJI_PJP_WBS_HEADER wbs_hdr,
15915: PJI_XBS_DENORM prg,
15916: (
15917: select 'Y' CB_FLAG,