[Home] [Help]
190: pji_utils.write2log('Entering PJI_FM_PLAN_EXTR.CLEANUP',TRUE, 3);
191:
192:
193: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_EXTR_PLNVER1', 'NORMAL',null);
194: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_EXTR_PLAN', 'NORMAL',null);
195: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_EXTR_PLNVER2', 'NORMAL',null);
196: PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_AGGR_PLN', 'NORMAL',null);
197:
198:
500: );
501: end if;
502:
503:
504: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- for OF slice
505: ( LINE_TYPE
506: , CALENDAR_TYPE_CODE
507: , WORKER_ID
508: , PROJECT_ID
695: return;
696: end if;
697:
698: -- spread the ENT amounts
699: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- OF slice for ENT
700: ( LINE_TYPE
701: , CALENDAR_TYPE_CODE
702: , WORKER_ID
703: , PROJECT_ID
815: , tmp.rate_dangling_flag rate_dangling_flag
816: , tmp.rate2_dangling_flag rate2_dangling_flag
817: from
818: PJI_ORG_EXTR_INFO orginfo
819: , PJI_FM_EXTR_PLAN tmp
820: , fii_time_ent_period ent
821: where tmp.worker_id = p_worker_id
822: and tmp.end_date >= ent.start_date
823: and tmp.start_date <= ent.end_date
906: decode(invert.rule,
907: 'F', tmp.start_date,
908: 'L', tmp.end_date) curr_date
909: from
910: PJI_FM_EXTR_PLAN tmp,
911: (
912: select 'F' rule from dual union all
913: select 'L' rule from dual
914: ) invert
943: return;
944: end if;
945:
946: -- convert to GLOBAL CURRENCY
947: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GLOBAL CURRENCY
948: ( LINE_TYPE
949: , CALENDAR_TYPE_CODE
950: , WORKER_ID
951: , PROJECT_ID
1021: ) end rate_dangling_flag
1022: , null rate2_dangling_flag
1023: FROM
1024: PJI_FM_AGGR_DLY_RATES rates
1025: , PJI_FM_EXTR_PLAN tmp
1026: where tmp.WORKER_ID = p_worker_id
1027: and tmp.LINE_TYPE = 'OF'
1028: and rates.worker_id = -1
1029: and tmp.pf_currency_code = rates.pf_currency_code
1083: return;
1084: end if;
1085:
1086: -- convert to GLOBAL CURRENCY
1087: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GLOBAL CURRENCY
1088: ( LINE_TYPE
1089: , CALENDAR_TYPE_CODE
1090: , WORKER_ID
1091: , PROJECT_ID
1161: , 'E')
1162: ) end rate2_dangling_flag
1163: FROM
1164: PJI_FM_AGGR_DLY_RATES rates
1165: , PJI_FM_EXTR_PLAN tmp
1166: where tmp.WORKER_ID = p_worker_id
1167: and tmp.LINE_TYPE = 'OF'
1168: and rates.worker_id = -1
1169: and tmp.pf_currency_code = rates.pf_currency_code
1227:
1228: if g_pa_period_flag = 'Y' then
1229:
1230: -- convert to PA periods
1231: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to PA periods
1232: ( LINE_TYPE
1233: , CALENDAR_TYPE_CODE
1234: , WORKER_ID
1235: , PROJECT_ID
1352: , tmp.rate_dangling_flag rate_dangling_flag
1353: , tmp.rate2_dangling_flag rate2_dangling_flag
1354: from
1355: PJI_ORG_EXTR_INFO orginfo
1356: , PJI_FM_EXTR_PLAN tmp
1357: , fii_time_cal_period cal_pa
1358: where tmp.worker_id = p_worker_id
1359: and tmp.end_date >= cal_pa.start_date
1360: and tmp.start_date <= cal_pa.end_date
1399:
1400: if g_gl_period_flag = 'Y' then
1401:
1402: -- convert to GL periods
1403: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GL periods
1404: ( LINE_TYPE
1405: , CALENDAR_TYPE_CODE
1406: , WORKER_ID
1407: , PROJECT_ID
1524: , tmp.rate_dangling_flag rate_dangling_flag
1525: , tmp.rate2_dangling_flag rate2_dangling_flag
1526: from
1527: PJI_ORG_EXTR_INFO orginfo
1528: , PJI_FM_EXTR_PLAN tmp
1529: , fii_time_cal_period cal_gl
1530: where tmp.worker_id = p_worker_id
1531: and tmp.end_date >= cal_gl.start_date
1532: and tmp.start_date <= cal_gl.end_date
1568: end if;
1569:
1570:
1571: -- convert to ENT periods
1572: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to ENT periods
1573: ( LINE_TYPE
1574: , CALENDAR_TYPE_CODE
1575: , WORKER_ID
1576: , PROJECT_ID
1692: , tmp.rate_dangling_flag rate_dangling_flag
1693: , tmp.rate2_dangling_flag rate2_dangling_flag
1694: from
1695: PJI_ORG_EXTR_INFO orginfo
1696: , PJI_FM_EXTR_PLAN tmp
1697: , fii_time_ent_period ent
1698: where tmp.worker_id = p_worker_id
1699: and tmp.end_date >= ent.start_date
1700: and tmp.start_date <= ent.end_date
1733: end if;
1734:
1735:
1736: -- convert to ENTW periods
1737: Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to ENTW periods
1738: ( LINE_TYPE
1739: , CALENDAR_TYPE_CODE
1740: , WORKER_ID
1741: , PROJECT_ID
1857: , tmp.rate_dangling_flag rate_dangling_flag
1858: , tmp.rate2_dangling_flag rate2_dangling_flag
1859: from
1860: PJI_ORG_EXTR_INFO orginfo
1861: , PJI_FM_EXTR_PLAN tmp
1862: , fii_time_week entw
1863: where tmp.worker_id = p_worker_id
1864: and tmp.end_date >= entw.start_date
1865: and tmp.start_date <= entw.end_date
1933: , 'PA', orginfo.PA_CALENDAR_ID
1934: , 'GL', orginfo.GL_CALENDAR_ID
1935: , null) CALENDAR_ID
1936: from PJI_ORG_EXTR_INFO orginfo
1937: , PJI_FM_EXTR_PLAN tmp
1938: where tmp.WORKER_ID = p_worker_id
1939: and ( tmp.TIME_DANGLING_FLAG is not null
1940: or tmp.RATE_DANGLING_FLAG is not null
1941: or tmp.RATE2_DANGLING_FLAG is not null)
2186: to_number(null)),
2187: to_number(null))) curr_forecast_labor_hrs
2188: from
2189: PJI_FM_EXTR_PLNVER1 vers,
2190: PJI_FM_EXTR_PLAN tmp,
2191: PJI_FM_EXTR_PLN_LOG log
2192: where
2193: tmp.WORKER_ID = p_worker_id and
2194: tmp.LINE_TYPE <> 'F1' and
2347: end REVENUE_FORECAST_C_VERSION
2348: from
2349: PJI_PJI_PROJ_BATCH_MAP map,
2350: PJI_FM_EXTR_PLNVER1 bvs,
2351: PJI_FM_EXTR_PLAN tmp,
2352: PJI_FM_EXTR_PLN_LOG log
2353: where
2354: map.worker_id = p_worker_id
2355: and map.project_id = bvs.project_id