[Home] [Help]
509: execute immediate
510: 'truncate table '||p_schema||'.'||p_tablename||' drop storage';
511:
512: ELSIF (p_trunc_type = 'PARTITION') THEN
513: IF (p_tablename = 'PJI_FP_AGGR_PJP1' OR p_tablename = 'PJI_FP_AGGR_PJP2') THEN -- Added for bug 13897252
514: /* Added for Bug 13813361 Start */
515: FOR ind_lp IN (SELECT table_owner||'.'||index_name ind_name
516: , partitioned
517: FROM dba_indexes
7693: p_fpm_upgrade => l_fpm_upgrade);
7694: Else
7695: /* End bug 10338478 */
7696:
7697: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
7698: into PJI_FP_AGGR_PJP1 partition(P1)
7699: (
7700: WORKER_ID,
7701: RECORD_TYPE,
7694: Else
7695: /* End bug 10338478 */
7696:
7697: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
7698: into PJI_FP_AGGR_PJP1 partition(P1)
7699: (
7700: WORKER_ID,
7701: RECORD_TYPE,
7702: PRG_LEVEL,
8479: pjp1.CUSTOM13,
8480: pjp1.CUSTOM14,
8481: pjp1.CUSTOM15
8482: from
8483: PJI_FP_AGGR_PJP1 pjp1,
8484: PJI_PJP_WBS_HEADER wbs_hdr,
8485: PA_XBS_DENORM wbs,
8486: PJI_XBS_DENORM prg,
8487: (
8633: pjp1.CUSTOM13,
8634: pjp1.CUSTOM14,
8635: pjp1.CUSTOM15
8636: from
8637: PJI_FP_AGGR_PJP1 pjp1,
8638: PJI_PJP_WBS_HEADER wbs_hdr,
8639: PJI_XBS_DENORM prg,
8640: (
8641: select 'Y' CB_FLAG,
9014:
9015: elsif (p_worker_id =2) then
9016: -- Query for Partition 2
9017:
9018: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
9019: into PJI_FP_AGGR_PJP1 partition(P2)
9020: (
9021: WORKER_ID,
9022: RECORD_TYPE,
9015: elsif (p_worker_id =2) then
9016: -- Query for Partition 2
9017:
9018: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
9019: into PJI_FP_AGGR_PJP1 partition(P2)
9020: (
9021: WORKER_ID,
9022: RECORD_TYPE,
9023: PRG_LEVEL,
9800: pjp1.CUSTOM13,
9801: pjp1.CUSTOM14,
9802: pjp1.CUSTOM15
9803: from
9804: PJI_FP_AGGR_PJP1 pjp1,
9805: PJI_PJP_WBS_HEADER wbs_hdr,
9806: PA_XBS_DENORM wbs,
9807: PJI_XBS_DENORM prg,
9808: (
9954: pjp1.CUSTOM13,
9955: pjp1.CUSTOM14,
9956: pjp1.CUSTOM15
9957: from
9958: PJI_FP_AGGR_PJP1 pjp1,
9959: PJI_PJP_WBS_HEADER wbs_hdr,
9960: PJI_XBS_DENORM prg,
9961: (
9962: select 'Y' CB_FLAG,
10333:
10334: elsif (p_worker_id =3) then
10335: -- Query for Partition 3
10336:
10337: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
10338: into PJI_FP_AGGR_PJP1 partition(P3)
10339: (
10340: WORKER_ID,
10341: RECORD_TYPE,
10334: elsif (p_worker_id =3) then
10335: -- Query for Partition 3
10336:
10337: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
10338: into PJI_FP_AGGR_PJP1 partition(P3)
10339: (
10340: WORKER_ID,
10341: RECORD_TYPE,
10342: PRG_LEVEL,
11119: pjp1.CUSTOM13,
11120: pjp1.CUSTOM14,
11121: pjp1.CUSTOM15
11122: from
11123: PJI_FP_AGGR_PJP1 pjp1,
11124: PJI_PJP_WBS_HEADER wbs_hdr,
11125: PA_XBS_DENORM wbs,
11126: PJI_XBS_DENORM prg,
11127: (
11273: pjp1.CUSTOM13,
11274: pjp1.CUSTOM14,
11275: pjp1.CUSTOM15
11276: from
11277: PJI_FP_AGGR_PJP1 pjp1,
11278: PJI_PJP_WBS_HEADER wbs_hdr,
11279: PJI_XBS_DENORM prg,
11280: (
11281: select 'Y' CB_FLAG,
11651: sup_wpa.PROJ_ELEMENT_ID is not null)));
11652: elsif (p_worker_id =4) then
11653: -- Query for Partition 4
11654:
11655: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
11656: into PJI_FP_AGGR_PJP1 partition(P4)
11657: (
11658: WORKER_ID,
11659: RECORD_TYPE,
11652: elsif (p_worker_id =4) then
11653: -- Query for Partition 4
11654:
11655: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
11656: into PJI_FP_AGGR_PJP1 partition(P4)
11657: (
11658: WORKER_ID,
11659: RECORD_TYPE,
11660: PRG_LEVEL,
12437: pjp1.CUSTOM13,
12438: pjp1.CUSTOM14,
12439: pjp1.CUSTOM15
12440: from
12441: PJI_FP_AGGR_PJP1 pjp1,
12442: PJI_PJP_WBS_HEADER wbs_hdr,
12443: PA_XBS_DENORM wbs,
12444: PJI_XBS_DENORM prg,
12445: (
12591: pjp1.CUSTOM13,
12592: pjp1.CUSTOM14,
12593: pjp1.CUSTOM15
12594: from
12595: PJI_FP_AGGR_PJP1 pjp1,
12596: PJI_PJP_WBS_HEADER wbs_hdr,
12597: PJI_XBS_DENORM prg,
12598: (
12599: select 'Y' CB_FLAG,
12969: sup_wpa.PROJ_ELEMENT_ID is not null)));
12970: elsif (p_worker_id =5) then
12971: -- Query for Partition 5
12972:
12973: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
12974: into PJI_FP_AGGR_PJP1 partition(P5)
12975: (
12976: WORKER_ID,
12977: RECORD_TYPE,
12970: elsif (p_worker_id =5) then
12971: -- Query for Partition 5
12972:
12973: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
12974: into PJI_FP_AGGR_PJP1 partition(P5)
12975: (
12976: WORKER_ID,
12977: RECORD_TYPE,
12978: PRG_LEVEL,
13755: pjp1.CUSTOM13,
13756: pjp1.CUSTOM14,
13757: pjp1.CUSTOM15
13758: from
13759: PJI_FP_AGGR_PJP1 pjp1,
13760: PJI_PJP_WBS_HEADER wbs_hdr,
13761: PA_XBS_DENORM wbs,
13762: PJI_XBS_DENORM prg,
13763: (
13909: pjp1.CUSTOM13,
13910: pjp1.CUSTOM14,
13911: pjp1.CUSTOM15
13912: from
13913: PJI_FP_AGGR_PJP1 pjp1,
13914: PJI_PJP_WBS_HEADER wbs_hdr,
13915: PJI_XBS_DENORM prg,
13916: (
13917: select 'Y' CB_FLAG,
14287: sup_wpa.PROJ_ELEMENT_ID is not null)));
14288: elsif (p_worker_id =6) then
14289: -- Query for Partition 6
14290:
14291: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
14292: into PJI_FP_AGGR_PJP1 partition(P6)
14293: (
14294: WORKER_ID,
14295: RECORD_TYPE,
14288: elsif (p_worker_id =6) then
14289: -- Query for Partition 6
14290:
14291: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
14292: into PJI_FP_AGGR_PJP1 partition(P6)
14293: (
14294: WORKER_ID,
14295: RECORD_TYPE,
14296: PRG_LEVEL,
15073: pjp1.CUSTOM13,
15074: pjp1.CUSTOM14,
15075: pjp1.CUSTOM15
15076: from
15077: PJI_FP_AGGR_PJP1 pjp1,
15078: PJI_PJP_WBS_HEADER wbs_hdr,
15079: PA_XBS_DENORM wbs,
15080: PJI_XBS_DENORM prg,
15081: (
15227: pjp1.CUSTOM13,
15228: pjp1.CUSTOM14,
15229: pjp1.CUSTOM15
15230: from
15231: PJI_FP_AGGR_PJP1 pjp1,
15232: PJI_PJP_WBS_HEADER wbs_hdr,
15233: PJI_XBS_DENORM prg,
15234: (
15235: select 'Y' CB_FLAG,
15605: sup_wpa.PROJ_ELEMENT_ID is not null)));
15606: elsif (p_worker_id =7) then
15607: -- Query for Partition 7
15608:
15609: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
15610: into PJI_FP_AGGR_PJP1 partition(P7)
15611: (
15612: WORKER_ID,
15613: RECORD_TYPE,
15606: elsif (p_worker_id =7) then
15607: -- Query for Partition 7
15608:
15609: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
15610: into PJI_FP_AGGR_PJP1 partition(P7)
15611: (
15612: WORKER_ID,
15613: RECORD_TYPE,
15614: PRG_LEVEL,
16391: pjp1.CUSTOM13,
16392: pjp1.CUSTOM14,
16393: pjp1.CUSTOM15
16394: from
16395: PJI_FP_AGGR_PJP1 pjp1,
16396: PJI_PJP_WBS_HEADER wbs_hdr,
16397: PA_XBS_DENORM wbs,
16398: PJI_XBS_DENORM prg,
16399: (
16545: pjp1.CUSTOM13,
16546: pjp1.CUSTOM14,
16547: pjp1.CUSTOM15
16548: from
16549: PJI_FP_AGGR_PJP1 pjp1,
16550: PJI_PJP_WBS_HEADER wbs_hdr,
16551: PJI_XBS_DENORM prg,
16552: (
16553: select 'Y' CB_FLAG,
16923: sup_wpa.PROJ_ELEMENT_ID is not null)));
16924: elsif (p_worker_id =8) then
16925: -- Query for Partition 8
16926:
16927: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
16928: into PJI_FP_AGGR_PJP1 partition(P8)
16929: (
16930: WORKER_ID,
16931: RECORD_TYPE,
16924: elsif (p_worker_id =8) then
16925: -- Query for Partition 8
16926:
16927: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
16928: into PJI_FP_AGGR_PJP1 partition(P8)
16929: (
16930: WORKER_ID,
16931: RECORD_TYPE,
16932: PRG_LEVEL,
17709: pjp1.CUSTOM13,
17710: pjp1.CUSTOM14,
17711: pjp1.CUSTOM15
17712: from
17713: PJI_FP_AGGR_PJP1 pjp1,
17714: PJI_PJP_WBS_HEADER wbs_hdr,
17715: PA_XBS_DENORM wbs,
17716: PJI_XBS_DENORM prg,
17717: (
17863: pjp1.CUSTOM13,
17864: pjp1.CUSTOM14,
17865: pjp1.CUSTOM15
17866: from
17867: PJI_FP_AGGR_PJP1 pjp1,
17868: PJI_PJP_WBS_HEADER wbs_hdr,
17869: PJI_XBS_DENORM prg,
17870: (
17871: select 'Y' CB_FLAG,
18241: sup_wpa.PROJ_ELEMENT_ID is not null)));
18242: elsif (p_worker_id =9) then
18243: -- Query for Partition 9
18244:
18245: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
18246: into PJI_FP_AGGR_PJP1 partition(P9)
18247: (
18248: WORKER_ID,
18249: RECORD_TYPE,
18242: elsif (p_worker_id =9) then
18243: -- Query for Partition 9
18244:
18245: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
18246: into PJI_FP_AGGR_PJP1 partition(P9)
18247: (
18248: WORKER_ID,
18249: RECORD_TYPE,
18250: PRG_LEVEL,
19027: pjp1.CUSTOM13,
19028: pjp1.CUSTOM14,
19029: pjp1.CUSTOM15
19030: from
19031: PJI_FP_AGGR_PJP1 pjp1,
19032: PJI_PJP_WBS_HEADER wbs_hdr,
19033: PA_XBS_DENORM wbs,
19034: PJI_XBS_DENORM prg,
19035: (
19181: pjp1.CUSTOM13,
19182: pjp1.CUSTOM14,
19183: pjp1.CUSTOM15
19184: from
19185: PJI_FP_AGGR_PJP1 pjp1,
19186: PJI_PJP_WBS_HEADER wbs_hdr,
19187: PJI_XBS_DENORM prg,
19188: (
19189: select 'Y' CB_FLAG,
19559: sup_wpa.PROJ_ELEMENT_ID is not null)));
19560: elsif (p_worker_id =10) then
19561: -- Query for Partition 10
19562:
19563: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
19564: into PJI_FP_AGGR_PJP1 partition(P10)
19565: (
19566: WORKER_ID,
19567: RECORD_TYPE,
19560: elsif (p_worker_id =10) then
19561: -- Query for Partition 10
19562:
19563: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
19564: into PJI_FP_AGGR_PJP1 partition(P10)
19565: (
19566: WORKER_ID,
19567: RECORD_TYPE,
19568: PRG_LEVEL,
20345: pjp1.CUSTOM13,
20346: pjp1.CUSTOM14,
20347: pjp1.CUSTOM15
20348: from
20349: PJI_FP_AGGR_PJP1 pjp1,
20350: PJI_PJP_WBS_HEADER wbs_hdr,
20351: PA_XBS_DENORM wbs,
20352: PJI_XBS_DENORM prg,
20353: (
20499: pjp1.CUSTOM13,
20500: pjp1.CUSTOM14,
20501: pjp1.CUSTOM15
20502: from
20503: PJI_FP_AGGR_PJP1 pjp1,
20504: PJI_PJP_WBS_HEADER wbs_hdr,
20505: PJI_XBS_DENORM prg,
20506: (
20507: select 'Y' CB_FLAG,
26745:
26746: if (p_worker_id =1) then
26747: -- Query for Partition 1
26748:
26749: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
26750: into PJI_FP_AGGR_PJP1 partition(P1)
26751: (
26752: WORKER_ID,
26753: RECORD_TYPE,
26746: if (p_worker_id =1) then
26747: -- Query for Partition 1
26748:
26749: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
26750: into PJI_FP_AGGR_PJP1 partition(P1)
26751: (
26752: WORKER_ID,
26753: RECORD_TYPE,
26754: PRG_LEVEL,
27629: pjp1.CUSTOM13,
27630: pjp1.CUSTOM14,
27631: pjp1.CUSTOM15
27632: from
27633: PJI_FP_AGGR_PJP1 pjp1,
27634: PJI_PJP_WBS_HEADER wbs_hdr,
27635: PA_PROJ_ELEMENTS prj_emt,
27636: PA_XBS_DENORM prg,
27637: PJI_XBS_DENORM_DELTA prg_delta,
27790: pjp.PLAN_TYPE_CODE;
27791: elsif (p_worker_id =2) then
27792: -- Query for Partition 2
27793:
27794: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
27795: into PJI_FP_AGGR_PJP1 partition(P2)
27796: (
27797: WORKER_ID,
27798: RECORD_TYPE,
27791: elsif (p_worker_id =2) then
27792: -- Query for Partition 2
27793:
27794: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
27795: into PJI_FP_AGGR_PJP1 partition(P2)
27796: (
27797: WORKER_ID,
27798: RECORD_TYPE,
27799: PRG_LEVEL,
28674: pjp1.CUSTOM13,
28675: pjp1.CUSTOM14,
28676: pjp1.CUSTOM15
28677: from
28678: PJI_FP_AGGR_PJP1 pjp1,
28679: PJI_PJP_WBS_HEADER wbs_hdr,
28680: PA_PROJ_ELEMENTS prj_emt,
28681: PA_XBS_DENORM prg,
28682: PJI_XBS_DENORM_DELTA prg_delta,
28835: pjp.PLAN_TYPE_CODE;
28836: elsif (p_worker_id =3) then
28837: -- Query for Partition 3
28838:
28839: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
28840: into PJI_FP_AGGR_PJP1 partition(P3)
28841: (
28842: WORKER_ID,
28843: RECORD_TYPE,
28836: elsif (p_worker_id =3) then
28837: -- Query for Partition 3
28838:
28839: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
28840: into PJI_FP_AGGR_PJP1 partition(P3)
28841: (
28842: WORKER_ID,
28843: RECORD_TYPE,
28844: PRG_LEVEL,
29719: pjp1.CUSTOM13,
29720: pjp1.CUSTOM14,
29721: pjp1.CUSTOM15
29722: from
29723: PJI_FP_AGGR_PJP1 pjp1,
29724: PJI_PJP_WBS_HEADER wbs_hdr,
29725: PA_PROJ_ELEMENTS prj_emt,
29726: PA_XBS_DENORM prg,
29727: PJI_XBS_DENORM_DELTA prg_delta,
29880: pjp.PLAN_TYPE_CODE;
29881: elsif (p_worker_id =4) then
29882: -- Query for Partition 4
29883:
29884: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
29885: into PJI_FP_AGGR_PJP1 partition(P4)
29886: (
29887: WORKER_ID,
29888: RECORD_TYPE,
29881: elsif (p_worker_id =4) then
29882: -- Query for Partition 4
29883:
29884: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
29885: into PJI_FP_AGGR_PJP1 partition(P4)
29886: (
29887: WORKER_ID,
29888: RECORD_TYPE,
29889: PRG_LEVEL,
30764: pjp1.CUSTOM13,
30765: pjp1.CUSTOM14,
30766: pjp1.CUSTOM15
30767: from
30768: PJI_FP_AGGR_PJP1 pjp1,
30769: PJI_PJP_WBS_HEADER wbs_hdr,
30770: PA_PROJ_ELEMENTS prj_emt,
30771: PA_XBS_DENORM prg,
30772: PJI_XBS_DENORM_DELTA prg_delta,
30925: pjp.PLAN_TYPE_CODE;
30926: elsif (p_worker_id =5) then
30927: -- Query for Partition 5
30928:
30929: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
30930: into PJI_FP_AGGR_PJP1 partition(P5)
30931: (
30932: WORKER_ID,
30933: RECORD_TYPE,
30926: elsif (p_worker_id =5) then
30927: -- Query for Partition 5
30928:
30929: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
30930: into PJI_FP_AGGR_PJP1 partition(P5)
30931: (
30932: WORKER_ID,
30933: RECORD_TYPE,
30934: PRG_LEVEL,
31809: pjp1.CUSTOM13,
31810: pjp1.CUSTOM14,
31811: pjp1.CUSTOM15
31812: from
31813: PJI_FP_AGGR_PJP1 pjp1,
31814: PJI_PJP_WBS_HEADER wbs_hdr,
31815: PA_PROJ_ELEMENTS prj_emt,
31816: PA_XBS_DENORM prg,
31817: PJI_XBS_DENORM_DELTA prg_delta,
31970: pjp.PLAN_TYPE_CODE;
31971: elsif (p_worker_id =6) then
31972: -- Query for Partition 6
31973:
31974: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
31975: into PJI_FP_AGGR_PJP1 partition(P6)
31976: (
31977: WORKER_ID,
31978: RECORD_TYPE,
31971: elsif (p_worker_id =6) then
31972: -- Query for Partition 6
31973:
31974: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
31975: into PJI_FP_AGGR_PJP1 partition(P6)
31976: (
31977: WORKER_ID,
31978: RECORD_TYPE,
31979: PRG_LEVEL,
32854: pjp1.CUSTOM13,
32855: pjp1.CUSTOM14,
32856: pjp1.CUSTOM15
32857: from
32858: PJI_FP_AGGR_PJP1 pjp1,
32859: PJI_PJP_WBS_HEADER wbs_hdr,
32860: PA_PROJ_ELEMENTS prj_emt,
32861: PA_XBS_DENORM prg,
32862: PJI_XBS_DENORM_DELTA prg_delta,
33015: pjp.PLAN_TYPE_CODE;
33016: elsif (p_worker_id =7) then
33017: -- Query for Partition 7
33018:
33019: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
33020: into PJI_FP_AGGR_PJP1 partition(P7)
33021: (
33022: WORKER_ID,
33023: RECORD_TYPE,
33016: elsif (p_worker_id =7) then
33017: -- Query for Partition 7
33018:
33019: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
33020: into PJI_FP_AGGR_PJP1 partition(P7)
33021: (
33022: WORKER_ID,
33023: RECORD_TYPE,
33024: PRG_LEVEL,
33899: pjp1.CUSTOM13,
33900: pjp1.CUSTOM14,
33901: pjp1.CUSTOM15
33902: from
33903: PJI_FP_AGGR_PJP1 pjp1,
33904: PJI_PJP_WBS_HEADER wbs_hdr,
33905: PA_PROJ_ELEMENTS prj_emt,
33906: PA_XBS_DENORM prg,
33907: PJI_XBS_DENORM_DELTA prg_delta,
34060: pjp.PLAN_TYPE_CODE;
34061: elsif (p_worker_id =8) then
34062: -- Query for Partition 8
34063:
34064: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
34065: into PJI_FP_AGGR_PJP1 partition(P8)
34066: (
34067: WORKER_ID,
34068: RECORD_TYPE,
34061: elsif (p_worker_id =8) then
34062: -- Query for Partition 8
34063:
34064: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
34065: into PJI_FP_AGGR_PJP1 partition(P8)
34066: (
34067: WORKER_ID,
34068: RECORD_TYPE,
34069: PRG_LEVEL,
34944: pjp1.CUSTOM13,
34945: pjp1.CUSTOM14,
34946: pjp1.CUSTOM15
34947: from
34948: PJI_FP_AGGR_PJP1 pjp1,
34949: PJI_PJP_WBS_HEADER wbs_hdr,
34950: PA_PROJ_ELEMENTS prj_emt,
34951: PA_XBS_DENORM prg,
34952: PJI_XBS_DENORM_DELTA prg_delta,
35105: pjp.PLAN_TYPE_CODE;
35106: elsif (p_worker_id =9) then
35107: -- Query for Partition 9
35108:
35109: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
35110: into PJI_FP_AGGR_PJP1 partition(P9)
35111: (
35112: WORKER_ID,
35113: RECORD_TYPE,
35106: elsif (p_worker_id =9) then
35107: -- Query for Partition 9
35108:
35109: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
35110: into PJI_FP_AGGR_PJP1 partition(P9)
35111: (
35112: WORKER_ID,
35113: RECORD_TYPE,
35114: PRG_LEVEL,
35989: pjp1.CUSTOM13,
35990: pjp1.CUSTOM14,
35991: pjp1.CUSTOM15
35992: from
35993: PJI_FP_AGGR_PJP1 pjp1,
35994: PJI_PJP_WBS_HEADER wbs_hdr,
35995: PA_PROJ_ELEMENTS prj_emt,
35996: PA_XBS_DENORM prg,
35997: PJI_XBS_DENORM_DELTA prg_delta,
36150: pjp.PLAN_TYPE_CODE;
36151: elsif (p_worker_id =10) then
36152: -- Query for Partition 10
36153:
36154: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
36155: into PJI_FP_AGGR_PJP1 partition(P10)
36156: (
36157: WORKER_ID,
36158: RECORD_TYPE,
36151: elsif (p_worker_id =10) then
36152: -- Query for Partition 10
36153:
36154: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
36155: into PJI_FP_AGGR_PJP1 partition(P10)
36156: (
36157: WORKER_ID,
36158: RECORD_TYPE,
36159: PRG_LEVEL,
37034: pjp1.CUSTOM13,
37035: pjp1.CUSTOM14,
37036: pjp1.CUSTOM15
37037: from
37038: PJI_FP_AGGR_PJP1 pjp1,
37039: PJI_PJP_WBS_HEADER wbs_hdr,
37040: PA_PROJ_ELEMENTS prj_emt,
37041: PA_XBS_DENORM prg,
37042: PJI_XBS_DENORM_DELTA prg_delta,
40667:
40668: if (p_worker_id =1) then
40669: -- Query for Partition 1
40670:
40671: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
40672: into PJI_FP_AGGR_PJP1 partition(P1)
40673:
40674: (
40675: WORKER_ID,
40668: if (p_worker_id =1) then
40669: -- Query for Partition 1
40670:
40671: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
40672: into PJI_FP_AGGR_PJP1 partition(P1)
40673:
40674: (
40675: WORKER_ID,
40676: RECORD_TYPE,
40824: sum(pjp1.CUSTOM13) CUSTOM13,
40825: sum(pjp1.CUSTOM14) CUSTOM14,
40826: sum(pjp1.CUSTOM15) CUSTOM15
40827: from
40828: PJI_FP_AGGR_PJP1 pjp1
40829: where
40830: pjp1.WORKER_ID = p_worker_id
40831: group by
40832: pjp1.WORKER_ID,
41085:
41086: elsif (p_worker_id =2) then
41087: -- Query for Partition 2
41088:
41089: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
41090: into PJI_FP_AGGR_PJP1 partition(P2)
41091:
41092: (
41093: WORKER_ID,
41086: elsif (p_worker_id =2) then
41087: -- Query for Partition 2
41088:
41089: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
41090: into PJI_FP_AGGR_PJP1 partition(P2)
41091:
41092: (
41093: WORKER_ID,
41094: RECORD_TYPE,
41242: sum(pjp1.CUSTOM13) CUSTOM13,
41243: sum(pjp1.CUSTOM14) CUSTOM14,
41244: sum(pjp1.CUSTOM15) CUSTOM15
41245: from
41246: PJI_FP_AGGR_PJP1 pjp1
41247: where
41248: pjp1.WORKER_ID = p_worker_id
41249: group by
41250: pjp1.WORKER_ID,
41502: nvl(sum(CUSTOM15), 0) = 0);
41503: elsif (p_worker_id =3) then
41504: -- Query for Partition 3
41505:
41506: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
41507: into PJI_FP_AGGR_PJP1 partition(P3)
41508:
41509: (
41510: WORKER_ID,
41503: elsif (p_worker_id =3) then
41504: -- Query for Partition 3
41505:
41506: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
41507: into PJI_FP_AGGR_PJP1 partition(P3)
41508:
41509: (
41510: WORKER_ID,
41511: RECORD_TYPE,
41659: sum(pjp1.CUSTOM13) CUSTOM13,
41660: sum(pjp1.CUSTOM14) CUSTOM14,
41661: sum(pjp1.CUSTOM15) CUSTOM15
41662: from
41663: PJI_FP_AGGR_PJP1 pjp1
41664: where
41665: pjp1.WORKER_ID = p_worker_id
41666: group by
41667: pjp1.WORKER_ID,
41919: nvl(sum(CUSTOM15), 0) = 0);
41920: elsif (p_worker_id =4) then
41921: -- Query for Partition 4
41922:
41923: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
41924: into PJI_FP_AGGR_PJP1 partition(P4)
41925:
41926: (
41927: WORKER_ID,
41920: elsif (p_worker_id =4) then
41921: -- Query for Partition 4
41922:
41923: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
41924: into PJI_FP_AGGR_PJP1 partition(P4)
41925:
41926: (
41927: WORKER_ID,
41928: RECORD_TYPE,
42076: sum(pjp1.CUSTOM13) CUSTOM13,
42077: sum(pjp1.CUSTOM14) CUSTOM14,
42078: sum(pjp1.CUSTOM15) CUSTOM15
42079: from
42080: PJI_FP_AGGR_PJP1 pjp1
42081: where
42082: pjp1.WORKER_ID = p_worker_id
42083: group by
42084: pjp1.WORKER_ID,
42336: nvl(sum(CUSTOM15), 0) = 0);
42337: elsif (p_worker_id =5) then
42338: -- Query for Partition 5
42339:
42340: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
42341: into PJI_FP_AGGR_PJP1 partition(P5)
42342:
42343: (
42344: WORKER_ID,
42337: elsif (p_worker_id =5) then
42338: -- Query for Partition 5
42339:
42340: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
42341: into PJI_FP_AGGR_PJP1 partition(P5)
42342:
42343: (
42344: WORKER_ID,
42345: RECORD_TYPE,
42493: sum(pjp1.CUSTOM13) CUSTOM13,
42494: sum(pjp1.CUSTOM14) CUSTOM14,
42495: sum(pjp1.CUSTOM15) CUSTOM15
42496: from
42497: PJI_FP_AGGR_PJP1 pjp1
42498: where
42499: pjp1.WORKER_ID = p_worker_id
42500: group by
42501: pjp1.WORKER_ID,
42753: nvl(sum(CUSTOM15), 0) = 0);
42754: elsif (p_worker_id =6) then
42755: -- Query for Partition 6
42756:
42757: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
42758: into PJI_FP_AGGR_PJP1 partition(P6)
42759:
42760: (
42761: WORKER_ID,
42754: elsif (p_worker_id =6) then
42755: -- Query for Partition 6
42756:
42757: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
42758: into PJI_FP_AGGR_PJP1 partition(P6)
42759:
42760: (
42761: WORKER_ID,
42762: RECORD_TYPE,
42910: sum(pjp1.CUSTOM13) CUSTOM13,
42911: sum(pjp1.CUSTOM14) CUSTOM14,
42912: sum(pjp1.CUSTOM15) CUSTOM15
42913: from
42914: PJI_FP_AGGR_PJP1 pjp1
42915: where
42916: pjp1.WORKER_ID = p_worker_id
42917: group by
42918: pjp1.WORKER_ID,
43170: nvl(sum(CUSTOM15), 0) = 0);
43171: elsif (p_worker_id =7) then
43172: -- Query for Partition 7
43173:
43174: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
43175: into PJI_FP_AGGR_PJP1 partition(P7)
43176:
43177: (
43178: WORKER_ID,
43171: elsif (p_worker_id =7) then
43172: -- Query for Partition 7
43173:
43174: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
43175: into PJI_FP_AGGR_PJP1 partition(P7)
43176:
43177: (
43178: WORKER_ID,
43179: RECORD_TYPE,
43327: sum(pjp1.CUSTOM13) CUSTOM13,
43328: sum(pjp1.CUSTOM14) CUSTOM14,
43329: sum(pjp1.CUSTOM15) CUSTOM15
43330: from
43331: PJI_FP_AGGR_PJP1 pjp1
43332: where
43333: pjp1.WORKER_ID = p_worker_id
43334: group by
43335: pjp1.WORKER_ID,
43587: nvl(sum(CUSTOM15), 0) = 0);
43588: elsif (p_worker_id =8) then
43589: -- Query for Partition 8
43590:
43591: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
43592: into PJI_FP_AGGR_PJP1 partition(P8)
43593:
43594: (
43595: WORKER_ID,
43588: elsif (p_worker_id =8) then
43589: -- Query for Partition 8
43590:
43591: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
43592: into PJI_FP_AGGR_PJP1 partition(P8)
43593:
43594: (
43595: WORKER_ID,
43596: RECORD_TYPE,
43744: sum(pjp1.CUSTOM13) CUSTOM13,
43745: sum(pjp1.CUSTOM14) CUSTOM14,
43746: sum(pjp1.CUSTOM15) CUSTOM15
43747: from
43748: PJI_FP_AGGR_PJP1 pjp1
43749: where
43750: pjp1.WORKER_ID = p_worker_id
43751: group by
43752: pjp1.WORKER_ID,
44004: nvl(sum(CUSTOM15), 0) = 0);
44005: elsif (p_worker_id =9) then
44006: -- Query for Partition 9
44007:
44008: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
44009: into PJI_FP_AGGR_PJP1 partition(P9)
44010:
44011: (
44012: WORKER_ID,
44005: elsif (p_worker_id =9) then
44006: -- Query for Partition 9
44007:
44008: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
44009: into PJI_FP_AGGR_PJP1 partition(P9)
44010:
44011: (
44012: WORKER_ID,
44013: RECORD_TYPE,
44161: sum(pjp1.CUSTOM13) CUSTOM13,
44162: sum(pjp1.CUSTOM14) CUSTOM14,
44163: sum(pjp1.CUSTOM15) CUSTOM15
44164: from
44165: PJI_FP_AGGR_PJP1 pjp1
44166: where
44167: pjp1.WORKER_ID = p_worker_id
44168: group by
44169: pjp1.WORKER_ID,
44421: nvl(sum(CUSTOM15), 0) = 0);
44422: elsif (p_worker_id =10) then
44423: -- Query for Partition 10
44424:
44425: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
44426: into PJI_FP_AGGR_PJP1 partition(P10)
44427:
44428: (
44429: WORKER_ID,
44422: elsif (p_worker_id =10) then
44423: -- Query for Partition 10
44424:
44425: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
44426: into PJI_FP_AGGR_PJP1 partition(P10)
44427:
44428: (
44429: WORKER_ID,
44430: RECORD_TYPE,
44578: sum(pjp1.CUSTOM13) CUSTOM13,
44579: sum(pjp1.CUSTOM14) CUSTOM14,
44580: sum(pjp1.CUSTOM15) CUSTOM15
44581: from
44582: PJI_FP_AGGR_PJP1 pjp1
44583: where
44584: pjp1.WORKER_ID = p_worker_id
44585: group by
44586: pjp1.WORKER_ID,
45253: and not exists ( select 'x' from PJI_PJP_PROJ_BATCH_MAP c
45254: where c.worker_id = p_worker_id and c.project_id = a.sup_project_id );
45255:
45256:
45257: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
45258: into PJI_FP_AGGR_PJP1 partition(P1)
45259: (
45260: WORKER_ID,
45261: RECORD_TYPE,
45254: where c.worker_id = p_worker_id and c.project_id = a.sup_project_id );
45255:
45256:
45257: insert /*+ parallel(PJI_FP_AGGR_PJP1) */
45258: into PJI_FP_AGGR_PJP1 partition(P1)
45259: (
45260: WORKER_ID,
45261: RECORD_TYPE,
45262: PRG_LEVEL,
46039: pjp1.CUSTOM13,
46040: pjp1.CUSTOM14,
46041: pjp1.CUSTOM15
46042: from
46043: PJI_FP_AGGR_PJP1 pjp1,
46044: PJI_HELPER_420 helper,
46045: PJI_PJP_WBS_HEADER_T wbs_hdr,
46046: PA_XBS_DENORM wbs,
46047: PJI_XBS_DENORM_T prg,
46196: pjp1.CUSTOM13,
46197: pjp1.CUSTOM14,
46198: pjp1.CUSTOM15
46199: from
46200: PJI_FP_AGGR_PJP1 pjp1,
46201: PJI_HELPER_420 helper, -- order in the From clause should not be changed unless recommended by perf team
46202: PJI_PJP_WBS_HEADER_T wbs_hdr,
46203: PJI_XBS_DENORM_T prg,
46204: (