[Home] [Help]
9177: /* spliited the SQL to remove unnecessasry hits based on extraction type */
9178: IF l_extraction_type='PARTIAL' THEN -- Bug#5453009 Performance Fix .
9179: -- Sql was split into 2 for Partial and Non Partial Flows
9180:
9181: insert into PJI_AC_AGGR_PJP1 pjp1_i
9182: (
9183: WORKER_ID,
9184: RECORD_TYPE,
9185: PRG_LEVEL,
9541: nvl(sum(CUSTOM14), 0) = 0 and
9542: nvl(sum(CUSTOM15), 0) = 0);
9543:
9544: else
9545: insert into PJI_AC_AGGR_PJP1 pjp1_i
9546: (
9547: WORKER_ID,
9548: RECORD_TYPE,
9549: PRG_LEVEL,
14270: l_level);
14271:
14272: /* Commented for bug 7551819 */
14273: -- insert /*+ parallel(pjp1_in)
14274: -- noappend(pjp1_in) */ into PJI_AC_AGGR_PJP1 pjp1_in -- changed for bug 5927368
14275: /* ( WORKER_ID,
14276: RECORD_TYPE,
14277: PRG_LEVEL,
14278: PROJECT_ID,
14531: pjp1.CUSTOM13,
14532: pjp1.CUSTOM14,
14533: pjp1.CUSTOM15
14534: from
14535: PJI_AC_AGGR_PJP1 pjp1,
14536: PJI_PJP_WBS_HEADER wbs_hdr,
14537: PA_XBS_DENORM wbs,
14538: PJI_XBS_DENORM prg,
14539: (
14627: pjp1.CUSTOM13,
14628: pjp1.CUSTOM14,
14629: pjp1.CUSTOM15
14630: from
14631: PJI_AC_AGGR_PJP1 pjp1,
14632: PJI_PJP_WBS_HEADER wbs_hdr,
14633: PJI_XBS_DENORM prg
14634: where
14635: prg.STRUCT_TYPE = 'PRG' and
14899: while (l_level > 0) loop
14900:
14901: -- rollup project hiearchy
14902:
14903: insert into PJI_AC_AGGR_PJP1_T
14904: (
14905: WORKER_ID,
14906: RECORD_TYPE,
14907: PRG_LEVEL,
15162: pjp1.CUSTOM13,
15163: pjp1.CUSTOM14,
15164: pjp1.CUSTOM15
15165: from
15166: PJI_AC_AGGR_PJP1_T pjp1,
15167: PJI_PJP_WBS_HEADER wbs_hdr,
15168: PA_XBS_DENORM wbs,
15169: PJI_XBS_DENORM prg,
15170: (
15258: pjp1.CUSTOM13,
15259: pjp1.CUSTOM14,
15260: pjp1.CUSTOM15
15261: from
15262: PJI_AC_AGGR_PJP1_T pjp1,
15263: PJI_PJP_WBS_HEADER wbs_hdr,
15264: PJI_XBS_DENORM prg
15265: where
15266: prg.STRUCT_TYPE = 'PRG' and
16888: l_level);
16889:
16890: /* Commented for bug 7551819 */
16891: -- insert /*+ parallel(pjp1_in)
16892: -- noappend(pjp1_in) */ into PJI_AC_AGGR_PJP1 pjp1_in -- changed for bug 5927368
16893: /* (
16894: WORKER_ID,
16895: RECORD_TYPE,
16896: PRG_LEVEL,
17145: pjp1.CUSTOM13,
17146: pjp1.CUSTOM14,
17147: pjp1.CUSTOM15
17148: from
17149: PJI_AC_AGGR_PJP1 pjp1,
17150: PJI_PJP_WBS_HEADER wbs_hdr,
17151: PA_PROJ_ELEMENTS prj_emt,
17152: PA_XBS_DENORM prg,
17153: PJI_XBS_DENORM_DELTA prg_delta
17873: nvl(sum(pjp1.CUSTOM13), 0) = 0 and
17874: nvl(sum(pjp1.CUSTOM14), 0) = 0 and
17875: nvl(sum(pjp1.CUSTOM15), 0) = 0);
17876:
17877: insert into PJI_AC_AGGR_PJP1 pjp1_i
17878: (
17879: WORKER_ID,
17880: RECORD_TYPE,
17881: PRG_LEVEL,
17990: sum(pjp1.CUSTOM13) CUSTOM13,
17991: sum(pjp1.CUSTOM14) CUSTOM14,
17992: sum(pjp1.CUSTOM15) CUSTOM15
17993: from
17994: PJI_AC_AGGR_PJP1 pjp1
17995: where
17996: pjp1.WORKER_ID = p_worker_id
17997: group by
17998: pjp1.WORKER_ID,
18139: where pjp1.WORKER_ID = p_worker_id and
18140: nvl(pjp1.LINE_TYPE, 'X') <> 'AGGR_PLAN';
18141: */
18142: delete
18143: from PJI_AC_AGGR_PJP1 pjp1
18144: where pjp1.WORKER_ID = p_worker_id and
18145: nvl(pjp1.RECORD_TYPE, 'X') <> 'A';
18146:
18147: end if;
20352: l_tab8 := 'Y';
20353: end if;
20354: if l_tab9 = 'N' then
20355: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
20356: 'PJI_AC_AGGR_PJP1',
20357: 'PARTITION',
20358: 'P' || p_worker_id);
20359: l_tab9 := 'Y';
20360: end if;
20459: delete from PJI_XBS_DENORM_DELTA_T;
20460:
20461: delete from PJI_FP_AGGR_PJP1_T;
20462:
20463: delete from PJI_AC_AGGR_PJP1_T;
20464:
20465: end if;
20466:
20467: end CLEANUP;