[Home] [Help]
321: ELSE
322: BEGIN
323: SELECT cal.CAL_PERIOD_ID
324: INTO l_end_period_id
325: FROM pji_time_cal_period_v cal,
326: pji_org_extr_info info
327: WHERE TRUNC(p_end_date) BETWEEN
328: TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE) AND
329: info.ORG_ID = l_org_id AND
488: -------------------------------
489: /* Commented for workplan progress
490: SELECT cal.CAL_PERIOD_ID
491: INTO l_end_period_id
492: FROM pji_time_cal_period_v cal,
493: pji_org_extr_info info
494: WHERE TRUNC(p_end_date) BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE) AND
495: info.ORG_ID = l_org_id AND
496: DECODE(l_calendar_type, 'P',
502: ELSE
503: BEGIN
504: SELECT cal.CAL_PERIOD_ID
505: INTO l_end_period_id(i)
506: FROM pji_time_cal_period_v cal,
507: pji_org_extr_info info
508: WHERE TRUNC(p_end_date(i)) BETWEEN
509: TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE) AND
510: info.ORG_ID = l_org_id(i) AND
656: SELECT /* + index(hr, per_assignments_f_n12) index(prd, pji_time_cal_period_u1) */
657: distinct T.TXN_SOURCE_ID,HR.VENDOR_ID,HR.PERSON_ID
658: FROM pa_res_list_map_tmp1 t,
659: per_all_assignments_f hr, --Bug#5356978
660: pji_time_cal_period_v prd,
661: ( SELECT txn_accum_header_id,max(recvr_period_id) recvr_period_id -- Bug#5262851
662: FROM
663: (
664: SELECT
825: -- End OLAP
826: FROM
827: pa_res_list_map_tmp4 tmp4,
828: pji_fm_aggr_fin8 accum,
829: pji_time_cal_period_v time,
830: pji_org_extr_info info
831: WHERE
832: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
833: accum.PROJECT_ID = p_project_ids(i) AND
907: -- End OLAP
908: FROM
909: pa_res_list_map_tmp4 tmp4,
910: pji_fm_aggr_fin8 accum ,
911: pji_time_cal_period_v time --, pji_org_extr_info info
912: WHERE
913: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
914: accum.PROJECT_ID = p_project_ids(i) AND
915: accum.NAMED_ROLE = NVL(tmp4.NAMED_ROLE, 'PJI$NULL') AND -- Bug 8294762
1025: -- End OLAP
1026: FROM
1027: pa_res_list_map_tmp4 tmp4,
1028: pji_fm_aggr_fin8 fin,
1029: pji_time_cal_period_v time,
1030: pji_org_extr_info info
1031: WHERE
1032: tmp4.TXN_SOURCE_ID = fin.TXN_ACCUM_HEADER_ID AND
1033: fin.PROJECT_ID = p_project_ids(i) AND
1107: -- End OLAP
1108: FROM
1109: pa_res_list_map_tmp4 tmp4,
1110: pji_fm_aggr_fin8 fin ,
1111: pji_time_cal_period_v time --, pji_org_extr_info info
1112: WHERE
1113: tmp4.TXN_SOURCE_ID = fin.TXN_ACCUM_HEADER_ID AND
1114: fin.PROJECT_ID = p_project_ids(i) AND
1115: fin.NAMED_ROLE = NVL(tmp4.NAMED_ROLE, 'PJI$NULL') AND -- Bug 8294762
1268: --End OLAP
1269: FROM
1270: pa_res_list_map_tmp4 tmp4,
1271: pji_fp_txn_accum accum,
1272: pji_time_cal_period_v time,
1273: pji_org_extr_info info
1274: WHERE
1275: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
1276: accum.PROJECT_ID = p_project_ids(i) AND
1389: -- End OLAP
1390: FROM
1391: pa_res_list_map_tmp4 tmp4,
1392: pji_fp_txn_accum accum,
1393: pji_time_cal_period_v time
1394: WHERE
1395: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
1396: accum.PROJECT_ID = p_project_ids(i) AND
1397: accum.NAMED_ROLE = NVL(tmp4.NAMED_ROLE, 'PJI$NULL') AND -- Bug 8294762
1908:
1909: IF L_CAL_TYPE IN ('P', 'G') THEN -- retrieve CAL_PERIOD_ID only if calander_type is 'P' or 'G'
1910: SELECT cal.CAL_PERIOD_ID
1911: INTO l_end_period_id
1912: FROM pji_time_cal_period_v cal, pji_org_extr_info info
1913: WHERE TRUNC(p_as_of_date)
1914: BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE)
1915: AND info.ORG_ID = l_org_id
1916: AND DECODE(l_cal_type, 'P', info.PA_CALENDAR_ID, info.GL_CALENDAR_ID) = cal.CALENDAR_ID;
3607: UPDATE pji_plan_extr_tmp TMP
3608: SET (END_PERIOD_ID,PERIOD_ID)=
3609: (
3610: SELECT cal.CAL_PERIOD_ID ,DECODE(tmp.cal_type, 'A', 2048, 32) PERIOD_TYPE_ID
3611: FROM pji_time_cal_period_v cal,
3612: pji_org_extr_info info
3613: WHERE TRUNC(tmp.AS_OF_DATE)
3614: BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE)
3615: AND info.ORG_ID = tmp.ORG_ID