[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
816: MAX(time.END_DATE) MAX_END_DATE
817: FROM
818: pa_res_list_map_tmp4 tmp4,
819: pji_fm_aggr_fin8 accum,
820: pji_time_cal_period_v time,
821: pji_org_extr_info info
822: WHERE
823: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
824: accum.PROJECT_ID = p_project_ids(i) AND
891: MAX(time.END_DATE) MAX_END_DATE
892: FROM
893: pa_res_list_map_tmp4 tmp4,
894: pji_fm_aggr_fin8 accum ,
895: pji_time_cal_period_v time --, pji_org_extr_info info
896: WHERE
897: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
898: accum.PROJECT_ID = p_project_ids(i) AND
899: accum.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
996: MAX(time.END_DATE) MAX_END_DATE
997: FROM
998: pa_res_list_map_tmp4 tmp4,
999: pji_fm_aggr_fin8 fin,
1000: pji_time_cal_period_v time,
1001: pji_org_extr_info info
1002: WHERE
1003: tmp4.TXN_SOURCE_ID = fin.TXN_ACCUM_HEADER_ID AND
1004: fin.PROJECT_ID = p_project_ids(i) AND
1071: MAX(time.END_DATE) MAX_END_DATE
1072: FROM
1073: pa_res_list_map_tmp4 tmp4,
1074: pji_fm_aggr_fin8 fin ,
1075: pji_time_cal_period_v time --, pji_org_extr_info info
1076: WHERE
1077: tmp4.TXN_SOURCE_ID = fin.TXN_ACCUM_HEADER_ID AND
1078: fin.PROJECT_ID = p_project_ids(i) AND
1079: fin.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
1215: time.END_DATE
1216: FROM
1217: pa_res_list_map_tmp4 tmp4,
1218: pji_fp_txn_accum accum,
1219: pji_time_cal_period_v time,
1220: pji_org_extr_info info
1221: WHERE
1222: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
1223: accum.PROJECT_ID = p_project_ids(i) AND
1328: time.END_DATE
1329: FROM
1330: pa_res_list_map_tmp4 tmp4,
1331: pji_fp_txn_accum accum,
1332: pji_time_cal_period_v time
1333: WHERE
1334: tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
1335: accum.PROJECT_ID = p_project_ids(i) AND
1336: l_calendar_type(i) = 'A' AND
1726:
1727: IF L_CAL_TYPE IN ('P', 'G') THEN -- retrieve CAL_PERIOD_ID only if calander_type is 'P' or 'G'
1728: SELECT cal.CAL_PERIOD_ID
1729: INTO l_end_period_id
1730: FROM pji_time_cal_period_v cal, pji_org_extr_info info
1731: WHERE TRUNC(p_as_of_date)
1732: BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE)
1733: AND info.ORG_ID = l_org_id
1734: AND DECODE(l_cal_type, 'P', info.PA_CALENDAR_ID, info.GL_CALENDAR_ID) = cal.CALENDAR_ID;
2499: UPDATE pji_plan_extr_tmp TMP
2500: SET (END_PERIOD_ID,PERIOD_ID)=
2501: (
2502: SELECT cal.CAL_PERIOD_ID ,DECODE(tmp.cal_type, 'A', 2048, 32) PERIOD_TYPE_ID
2503: FROM pji_time_cal_period_v cal,
2504: pji_org_extr_info info
2505: WHERE TRUNC(tmp.AS_OF_DATE)
2506: BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE)
2507: AND info.ORG_ID = tmp.ORG_ID