[Home] [Help]
244: when no_data_found then
245: raise l_missing_avl_setup;
246: end;
247:
248: insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
249: (
250: WORKER_ID,
251: PERSON_ID,
252: EXPENDITURE_ORG_ID,
1301: )) then
1302: return;
1303: end if;
1304:
1305: insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1306: (
1307: WORKER_ID,
1308: PERSON_ID,
1309: EXPENDITURE_ORG_ID,
1431: sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
1432: sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
1433: from
1434: FII_TIME_DAY time,
1435: PJI_RM_AGGR_RES2 tmp2
1436: where
1437: tmp2.WORKER_ID = p_worker_id and
1438: tmp2.PERIOD_TYPE_ID = 1 and
1439: tmp2.CALENDAR_TYPE = 'C' and
1506: )) then
1507: return;
1508: end if;
1509:
1510: insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1511: (
1512: WORKER_ID,
1513: PERSON_ID,
1514: EXPENDITURE_ORG_ID,
1636: sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
1637: sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
1638: from
1639: FII_TIME_CAL_DAY_MV time,
1640: PJI_RM_AGGR_RES2 tmp2
1641: where
1642: tmp2.WORKER_ID = p_worker_id and
1643: tmp2.PERIOD_TYPE_ID = 1 and
1644: tmp2.CALENDAR_TYPE = 'C' and
1712: )) then
1713: return;
1714: end if;
1715:
1716: insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1717: (
1718: WORKER_ID,
1719: PERSON_ID,
1720: EXPENDITURE_ORG_ID,
1842: sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
1843: sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
1844: from
1845: FII_TIME_CAL_DAY_MV time,
1846: PJI_RM_AGGR_RES2 tmp2
1847: where
1848: tmp2.WORKER_ID = p_worker_id and
1849: tmp2.PERIOD_TYPE_ID = 1 and
1850: tmp2.CALENDAR_TYPE = 'C' and
1908: )) then
1909: return;
1910: end if;
1911:
1912: insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1913: (
1914: WORKER_ID,
1915: PERSON_ID,
1916: EXPENDITURE_ORG_ID,
2014: sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
2015: sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
2016: from
2017: FII_TIME_DAY time,
2018: PJI_RM_AGGR_RES2 tmp2
2019: where
2020: tmp2.WORKER_ID = p_worker_id and
2021: tmp2.PERIOD_TYPE_ID = 1 and
2022: tmp2.CALENDAR_TYPE = 'C' and
2550: l_creation_date CREATION_DATE,
2551: l_created_by CREATED_BY,
2552: l_last_update_login LAST_UPDATE_LOGIN
2553: from
2554: PJI_RM_AGGR_RES2 res2
2555: where
2556: WORKER_ID = p_worker_id and
2557: EXPENDITURE_ORGANIZATION_ID is not null
2558: group by
2623: l_creation_date CREATION_DATE,
2624: l_created_by CREATED_BY,
2625: l_last_update_login LAST_UPDATE_LOGIN
2626: from
2627: PJI_RM_AGGR_RES2 res2
2628: where
2629: WORKER_ID = p_worker_id and
2630: EXPENDITURE_ORGANIZATION_ID is not null
2631: group by
3096: JOB_ID,
3097: TIME_ID,
3098: PERIOD_TYPE_ID,
3099: CALENDAR_TYPE
3100: from PJI_RM_AGGR_RES2 res2
3101: where WORKER_ID = p_worker_id) and
3102: nvl(CAPACITY_HRS, 0) = 0 and
3103: nvl(TOTAL_HRS_A, 0) = 0 and
3104: nvl(BILL_HRS_A, 0) = 0 and
3114: 'PJI_RM_SUM_ROLLUP_RES.CLEANUP_RMS(p_worker_id);'
3115: );
3116:
3117: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME,
3118: 'PJI_RM_AGGR_RES2','NORMAL',null);
3119:
3120: commit;
3121:
3122: end CLEANUP_RMS;
3604: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
3605: 'PJI_RM_AGGR_RES1','NORMAL',null);
3606:
3607: PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
3608: 'PJI_RM_AGGR_RES2','NORMAL',null);
3609:
3610: end CLEANUP;
3611:
3612: end PJI_RM_SUM_ROLLUP_RES;