[Home] [Help]
1: PACKAGE BODY Jtf_Cal_Resource_Assign_Pkg AS
2: /* $Header: jtfclrab.pls 120.5 2011/03/22 06:31:04 anangupt ship $ */
3: PROCEDURE INSERT_ROW (
4: X_ERROR OUT NOCOPY VARCHAR2,
5: X_ROWID IN OUT NOCOPY VARCHAR2,
44:
45: -- Added by jawang on 06/05/2002 to fix bug 2180182
46: l_temp_fnd_end_date date := to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR');
47:
48: CURSOR C IS SELECT ROWID FROM JTF_CAL_RESOURCE_ASSIGN
49: WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID;
50:
51: CURSOR C_RES_NAME IS SELECT RESOURCE_NAME
52: FROM JTF_TASK_RESOURCES_VL
58: WHERE CALENDAR_ID = p_calendar_id
59: AND LANGUAGE=USERENV('LANG');
60:
61: CURSOR C_CAL_ID(p_primary_calendar_flag VARCHAR2) IS SELECT CALENDAR_ID
62: FROM JTF_CAL_RESOURCE_ASSIGN
63: WHERE resource_id = X_RESOURCE_ID
64: AND resource_type_code = X_RESOURCE_TYPE_CODE
65: AND primary_calendar_flag = p_primary_calendar_flag
66: AND (( X_START_DATE_TIME <= start_date_time AND NVL(X_END_DATE_TIME,l_temp_fnd_end_date)
78: v_shift_id NUMBER;
79: v_cal_resource_assign_id NUMBER;
80: BEGIN
81: Fnd_Msg_Pub.initialize;
82: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(TO_CHAR(X_RESOURCE_ID)) = FALSE THEN
83: Fnd_Message.set_name('JTF', 'JTF_CAL_REQUIRED');
84: Fnd_Message.set_token('P_NAME', X_RESOURCE_ID);
85: Fnd_Msg_Pub.ADD;
86: v_error := 'Y';
85: Fnd_Msg_Pub.ADD;
86: v_error := 'Y';
87: END IF;
88:
89: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(X_START_DATE_TIME) = FALSE THEN
90: Fnd_Message.set_name('JTF', 'JTF_CAL_START_DATE');
91: Fnd_Msg_Pub.ADD;
92:
93: v_error := 'Y';
92:
93: v_error := 'Y';
94: END IF;
95:
96: IF Jtf_Cal_Resource_Assign_Pkg.END_GREATER_THAN_BEGIN(X_START_DATE_TIME, X_END_DATE_TIME) = FALSE THEN
97: --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
98: --app_exception.raise_exception;
99: Fnd_Message.set_name('JTF', 'JTF_CAL_END_DATE');
100: Fnd_Message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>X_START_DATE_TIME,calendar_aware=>fnd_date.calendar_aware));
139: IF v_error = 'Y' THEN
140: X_ERROR := 'Y';
141: RETURN;
142: ELSE
143: SELECT jtf_cal_resource_assign_s.NEXTVAL
144: INTO v_cal_resource_assign_id
145: FROM dual;
146:
147: X_CAL_RESOURCE_ASSIGN_ID := v_cal_resource_assign_id;
145: FROM dual;
146:
147: X_CAL_RESOURCE_ASSIGN_ID := v_cal_resource_assign_id;
148:
149: INSERT INTO JTF_CAL_RESOURCE_ASSIGN (
150: OBJECT_VERSION_NUMBER,
151: ATTRIBUTE5,
152: ATTRIBUTE6,
153: ATTRIBUTE7,
267: ATTRIBUTE2,
268: ATTRIBUTE3,
269: ATTRIBUTE4,
270: CAL_RESOURCE_ASSIGN_ID
271: FROM JTF_CAL_RESOURCE_ASSIGN
272: WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID
273: FOR UPDATE OF CAL_RESOURCE_ASSIGN_ID NOWAIT;
274: BEGIN
275: FOR tlinfo IN c1 LOOP
379: WHERE CALENDAR_ID = p_calendar_id
380: AND LANGUAGE=USERENV('LANG');
381:
382: CURSOR C_CAL_ID(p_primary_calendar_flag VARCHAR2) IS SELECT CALENDAR_ID
383: FROM JTF_CAL_RESOURCE_ASSIGN
384: WHERE resource_id = X_RESOURCE_ID
385: AND resource_type_code = X_RESOURCE_TYPE_CODE
386: AND primary_calendar_flag = p_primary_calendar_flag
387: AND cal_resource_assign_id <> X_CAL_RESOURCE_ASSIGN_ID
398: -- Added by abraina to fix bug 4200240
399:
400: BEGIN
401: Fnd_Msg_Pub.initialize;
402: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(TO_CHAR(X_RESOURCE_ID)) = FALSE THEN
403: Fnd_Message.set_name('JTF', 'JTF_CAL_REQUIRED');
404: Fnd_Message.set_token('P_NAME', X_RESOURCE_ID);
405: Fnd_Msg_Pub.ADD;
406: v_error := 'Y';
405: Fnd_Msg_Pub.ADD;
406: v_error := 'Y';
407: END IF;
408:
409: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(X_START_DATE_TIME) = FALSE THEN
410: --fnd_message.set_name('JTF', 'START_DATE CANNOT BE NULL');
411: --app_exception.raise_exception;
412: Fnd_Message.set_name('JTF', 'JTF_CAL_START_DATE');
413: Fnd_Msg_Pub.ADD;
414:
415: v_error := 'Y';
416: END IF;
417:
418: IF Jtf_Cal_Resource_Assign_Pkg.END_GREATER_THAN_BEGIN(X_START_DATE_TIME, X_END_DATE_TIME) = FALSE THEN
419: --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
420: --app_exception.raise_exception;
421: Fnd_Message.set_name('JTF', 'JTF_CAL_END_DATE');
422: Fnd_Message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>X_START_DATE_TIME,calendar_aware=>fnd_date.calendar_aware));
467: ELSE
468: X_ERROR := 'N';
469: X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
470:
471: UPDATE JTF_CAL_RESOURCE_ASSIGN SET
472: OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
473: ATTRIBUTE5 = X_ATTRIBUTE5,
474: ATTRIBUTE6 = X_ATTRIBUTE6,
475: ATTRIBUTE7 = X_ATTRIBUTE7,
506: PROCEDURE DELETE_ROW (
507: X_CAL_RESOURCE_ASSIGN_ID IN NUMBER
508: ) IS
509: BEGIN
510: DELETE FROM JTF_CAL_RESOURCE_ASSIGN
511: WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID;
512:
513: IF (SQL%NOTFOUND) THEN
514: RAISE NO_DATA_FOUND;
541:
542:
543: CURSOR DUP IS
544: SELECT resource_type_code,resource_id,start_date_time,end_date_time
545: FROM JTF_CAL_RESOURCE_ASSIGN
546: WHERE calendar_id = X_CALENDAR_ID
547: AND resource_id = X_RESOURCE_ID
548: AND RESOURCE_TYPE_CODE = X_RESOURCE_TYPE_CODE
549: ORDER BY start_date_time
557: v_start_date_time := TO_DATE(X_START_DATE_TIME,'DD/MM/YYYY HH24:MI');
558: v_end_date_time := TO_DATE(X_END_DATE_TIME,'DD/MM/YYYY HH24:MI');
559:
560: SELECT COUNT(*) INTO temp_count
561: FROM JTF_CAL_RESOURCE_ASSIGN
562: WHERE calendar_id = X_CALENDAR_ID
563: AND resource_id = X_RESOURCE_ID
564: AND RESOURCE_TYPE_CODE = X_RESOURCE_TYPE_CODE
565: AND TRUNC(start_date_time) = TRUNC(v_start_date_time)
656: END IF;
657: END;
658:
659:
660: END Jtf_Cal_Resource_Assign_Pkg;