[Home] [Help]
2058: grouping(period.ENT_QTR_ID) = 1 and
2059: grouping(period.ENT_PERIOD_ID) = 1)
2060: union all
2061: select /*+ ordered full(map) parallel(map)
2062: index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
2063: fpp.PROJECT_ID,
2064: fpp.PROJECT_ORG_ID,
2065: fpp.PROJECT_ORGANIZATION_ID,
2066: fpp.PROJECT_TYPE_CLASS,
2143: else to_number(null)
2144: end FORECAST_LABOR_HRS
2145: from
2146: PJI_PJI_PROJ_BATCH_MAP map,
2147: PJI_FP_PROJ_F fpp
2148: where
2149: l_extraction_type <> 'FULL' and
2150: map.WORKER_ID = p_worker_id and
2151: (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
2445: grouping(period.CAL_QTR_ID) = 1 and
2446: grouping(period.CAL_PERIOD_ID) = 1)
2447: union all
2448: select /*+ ordered full(map) parallel(map)
2449: index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
2450: fpp.PROJECT_ID,
2451: fpp.PROJECT_ORG_ID,
2452: fpp.PROJECT_ORGANIZATION_ID,
2453: fpp.PROJECT_TYPE_CLASS,
2530: else to_number(null)
2531: end FORECAST_LABOR_HRS
2532: from
2533: PJI_PJI_PROJ_BATCH_MAP map,
2534: PJI_FP_PROJ_F fpp
2535: where
2536: l_extraction_type <> 'FULL' and
2537: map.WORKER_ID = p_worker_id and
2538: (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
2835: grouping(period.CAL_QTR_ID) = 1 and
2836: grouping(period.CAL_PERIOD_ID) = 1)
2837: union all
2838: select /*+ ordered full(map) parallel(map)
2839: index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
2840: fpp.PROJECT_ID,
2841: fpp.PROJECT_ORG_ID,
2842: fpp.PROJECT_ORGANIZATION_ID,
2843: fpp.PROJECT_TYPE_CLASS,
2920: else to_number(null)
2921: end FORECAST_LABOR_HRS
2922: from
2923: PJI_PJI_PROJ_BATCH_MAP map,
2924: PJI_FP_PROJ_F fpp
2925: where
2926: l_extraction_type <> 'FULL' and
2927: map.WORKER_ID = p_worker_id and
2928: (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
3159: bitand(tmp1.CURR_RECORD_TYPE_ID, 247),
3160: tmp1.CURRENCY_CODE
3161: union all
3162: select /*+ ordered full(map) parallel(map)
3163: index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
3164: fpp.PROJECT_ID,
3165: fpp.PROJECT_ORG_ID,
3166: fpp.PROJECT_ORGANIZATION_ID,
3167: fpp.PROJECT_TYPE_CLASS,
3244: else to_number(null)
3245: end FORECAST_LABOR_HRS
3246: from
3247: PJI_PJI_PROJ_BATCH_MAP map,
3248: PJI_FP_PROJ_F fpp
3249: where
3250: l_extraction_type <> 'FULL' and
3251: map.WORKER_ID = p_worker_id and
3252: (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
4321: l_last_update_login := FND_GLOBAL.LOGIN_ID;
4322:
4323: if (l_extraction_type = 'FULL') then
4324:
4325: insert /*+ append parallel(fpp) */ into PJI_FP_PROJ_F fpp
4326: (
4327: PROJECT_ORG_ID,
4328: PROJECT_ORGANIZATION_ID,
4329: TIME_ID,
4447: nvl(fin3.FORECAST_LABOR_HRS , 0) <> 0);
4448:
4449: else -- not initial data load
4450:
4451: merge /*+ parallel(fpp) */ into PJI_FP_PROJ_F fpp
4452: using
4453: (
4454: select
4455: PROJECT_ORG_ID,
4584: where
4585: fin3.WORKER_ID = p_worker_id
4586: union all -- partial refresh
4587: select /*+ ordered full(map) parallel(map)
4588: index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */
4589: fpp.PROJECT_ORG_ID,
4590: fpp.PROJECT_ORGANIZATION_ID,
4591: fpp.TIME_ID,
4592: fpp.PROJECT_ID,
4623: to_number(null) FORECAST_BURDENED_COST,
4624: to_number(null) FORECAST_LABOR_HRS
4625: from
4626: PJI_PJI_PROJ_BATCH_MAP map,
4627: PJI_FP_PROJ_F fpp
4628: where
4629: l_extraction_type = 'PARTIAL' and
4630: map.WORKER_ID = p_worker_id and
4631: map.EXTRACTION_TYPE = 'P' and
5082: Where PROJECT_ID = L_PROJECT_TAB(I)
5083: ;
5084:
5085: FORALL I in L_PROJECT_TAB.FIRST..L_PROJECT_TAB.LAST
5086: Update PJI_FP_PROJ_F
5087: Set PROJECT_ORGANIZATION_ID = L_NEW_ORGZ_TAB(I)
5088: Where PROJECT_ID = L_PROJECT_TAB(I)
5089: ;
5090:
5556: 'C',
5557: 'N');
5558: else
5559: FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
5560: TABNAME => 'MLOG$_PJI_FP_PROJ_F',
5561: PERCENT => 10,
5562: DEGREE => l_p_degree);
5563: PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5564: l_retcode,