[Home] [Help]
81: l_last_update_login := FND_GLOBAL.LOGIN_ID;
82:
83: select count(*)
84: into l_count
85: from PJI_PJP_PROJ_EXTR_STATUS
86: where ROWNUM = 1;
87:
88: if (l_count > 0) then
89:
114: l_created_by,
115: l_last_update_login
116: from
117: PA_PROJECTS_ALL prj,
118: PJI_PJP_PROJ_EXTR_STATUS pjp_status
119: where
120: prj.TEMPLATE_FLAG = 'N' and
121: prj.PROJECT_ID = pjp_status.PROJECT_ID (+) and
122: pjp_status.PROJECT_ID is null;
121: prj.PROJECT_ID = pjp_status.PROJECT_ID (+) and
122: pjp_status.PROJECT_ID is null;
123:
124: delete
125: from PJI_PJP_PROJ_EXTR_STATUS pjp
126: where not exists (select 1
127: from PA_PROJECTS_ALL prj
128: where prj.PROJECT_ID = pjp.PROJECT_ID);
129:
126: where not exists (select 1
127: from PA_PROJECTS_ALL prj
128: where prj.PROJECT_ID = pjp.PROJECT_ID);
129:
130: update PJI_PJP_PROJ_EXTR_STATUS sts
131: set sts.PROJECT_ORGANIZATION_ID =
132: (
133: select prj.CARRYING_OUT_ORGANIZATION_ID
134: from PA_PROJECTS_ALL prj
142: prj.CARRYING_OUT_ORGANIZATION_ID <>
143: sts.PROJECT_ORGANIZATION_ID
144: );
145:
146: insert into PJI_PJP_PROJ_EXTR_STATUS
147: (
148: PROJECT_ID,
149: PROJECT_ORGANIZATION_ID,
150: PROJECT_NAME,
172: l_last_update_login
173: from
174: PA_PROJECTS_ALL prj,
175: PA_PROJECT_TYPES_ALL pt,
176: PJI_PJP_PROJ_EXTR_STATUs pjp_status
177: where
178: prj.TEMPLATE_FLAG = 'N' and
179: prj.ORG_ID = pt.ORG_ID and /*5377131*/
180: prj.PROJECT_TYPE = pt.PROJECT_TYPE and
233: where log.EVENT_TYPE = 'PRG_CHANGE' and
234: log.EVENT_OBJECT = -1 and
235: log.ATTRIBUTE1 = prj.PROJECT_ID);
236:
237: insert into PJI_PJP_PROJ_EXTR_STATUS pjp_i
238: (
239: PROJECT_ID,
240: PROJECT_ORGANIZATION_ID,
241: PROJECT_NAME,
391: FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_AC_RMAP_ACR', l_low_rows, l_low_blocks, 75, 'P' || p_worker_id);
392:
393: -- non-partitioned tables
394: FND_STATS.SET_TABLE_STATS(l_pa_schema, 'PA_RBS_TXN_ACCUM_MAP', l_med_rows, l_med_blocks, 150);
395: FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_PJP_PROJ_EXTR_STATUS', l_med_rows, l_med_blocks, 150);
396:
397: -- gather statistics for PJI metadata tables
398: FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
399: tabname => 'PJI_PJP_PROJ_EXTR_STATUS',
395: FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_PJP_PROJ_EXTR_STATUS', l_med_rows, l_med_blocks, 150);
396:
397: -- gather statistics for PJI metadata tables
398: FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
399: tabname => 'PJI_PJP_PROJ_EXTR_STATUS',
400: percent => 10,
401: degree => l_degree);
402:
403: commit;
574: NAME like 'PJI_FPM_UPGRADE' or
575: NAME like 'PJI_PTC_UPGRADE' or /*4882640 */
576: NAME like 'PJP_FPM_UPGRADE_DATE' or
577: NAME like 'LAST_PJP_EXTR_DATE%';
578: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_PROJ_EXTR_STATUS', 'NORMAL', null);
579:
580: -- PJP facts
581: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_XBS_ACCUM_F', 'NORMAL', null);
582: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_XBS_ACCUM_F', 'NORMAL', null);
596: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RBS_DENORM', 'NORMAL', null);
597: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RBS_DENORM_DELTA', 'NORMAL', null);
598: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pa_schema, 'PA_RBS_TXN_ACCUM_MAP', 'NORMAL', null);
599: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_RES_TYPES', 'NORMAL', null);
600: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_PROJ_EXTR_STATUS', 'NORMAL', null);
601: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_PROJ_BATCH_MAP', 'NORMAL', null);
602: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PA_PROJ_EVENTS_LOG', 'NORMAL', null);
603: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_AGGR_PJP0', 'NORMAL', null);
604: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_AGGR_PJP0', 'NORMAL', null);
690: begin
691:
692: select trunc(last_update_date)
693: into l_last_proj_extr_date
694: from PJI_PJP_PROJ_EXTR_STATUS
695: where project_id = p_project_id ;
696:
697: return l_last_proj_extr_date;
698: