[Home] [Help]
1: PACKAGE BODY Jtf_Cal_Resource_Assign_Pkg AS
2: /* $Header: jtfclrab.pls 115.33 2002/11/15 15:03:08 sukulkar ship $ */
3: PROCEDURE INSERT_ROW (
4: X_ERROR OUT NOCOPY VARCHAR2,
5: X_ROWID IN OUT NOCOPY VARCHAR2,
41:
42: -- Added by jawang on 06/05/2002 to fix bug 2180182
43: l_temp_fnd_end_date date := to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR');
44:
45: CURSOR C IS SELECT ROWID FROM JTF_CAL_RESOURCE_ASSIGN
46: WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID;
47:
48: v_error CHAR := 'N';
49: v_shift_id NUMBER;
49: v_shift_id NUMBER;
50: v_cal_resource_assign_id NUMBER;
51: BEGIN
52: Fnd_Msg_Pub.initialize;
53: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(TO_CHAR(X_RESOURCE_ID)) = FALSE THEN
54: Fnd_Message.set_name('JTF', 'JTF_CAL_REQUIRED');
55: Fnd_Message.set_token('P_NAME', X_RESOURCE_ID);
56: Fnd_Msg_Pub.ADD;
57: v_error := 'Y';
56: Fnd_Msg_Pub.ADD;
57: v_error := 'Y';
58: END IF;
59:
60: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(X_START_DATE_TIME) = FALSE THEN
61: Fnd_Message.set_name('JTF', 'JTF_CAL_START_DATE');
62: Fnd_Msg_Pub.ADD;
63:
64: v_error := 'Y';
63:
64: v_error := 'Y';
65: END IF;
66:
67: IF Jtf_Cal_Resource_Assign_Pkg.END_GREATER_THAN_BEGIN(X_START_DATE_TIME, X_END_DATE_TIME) = FALSE THEN
68: --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
69: --app_exception.raise_exception;
70: Fnd_Message.set_name('JTF', 'JTF_CAL_END_DATE');
71: Fnd_Message.set_token('P_Start_Date', X_START_DATE_TIME);
73: Fnd_Msg_Pub.ADD;
74: v_error := 'Y';
75: END IF;
76:
77: SELECT COUNT(*) INTO prime_flag_count FROM jtf_cal_resource_assign
78: WHERE resource_id = X_RESOURCE_ID
79: AND resource_type_code = X_RESOURCE_TYPE_CODE
80: AND primary_calendar_flag = 'Y'
81: -- Modified by jawang on 06/05/2002 to fix bug 2180182
115: Fnd_Msg_Pub.ADD;
116: v_error := 'Y';
117: END IF;
118:
119: SELECT COUNT(*) INTO v_count FROM jtf_cal_resource_assign
120: WHERE resource_id = X_RESOURCE_ID
121: AND resource_type_code = X_RESOURCE_TYPE_CODE
122: AND primary_calendar_flag = 'N'
123: AND (( X_START_DATE_TIME <= start_date_time AND NVL(X_END_DATE_TIME,Fnd_Api.g_miss_date)
149: IF v_error = 'Y' THEN
150: X_ERROR := 'Y';
151: RETURN;
152: ELSE
153: SELECT jtf_cal_resource_assign_s.NEXTVAL
154: INTO v_cal_resource_assign_id
155: FROM dual;
156:
157: X_CAL_RESOURCE_ASSIGN_ID := v_cal_resource_assign_id;
155: FROM dual;
156:
157: X_CAL_RESOURCE_ASSIGN_ID := v_cal_resource_assign_id;
158:
159: INSERT INTO JTF_CAL_RESOURCE_ASSIGN (
160: OBJECT_VERSION_NUMBER,
161: ATTRIBUTE5,
162: ATTRIBUTE6,
163: ATTRIBUTE7,
277: ATTRIBUTE2,
278: ATTRIBUTE3,
279: ATTRIBUTE4,
280: CAL_RESOURCE_ASSIGN_ID
281: FROM JTF_CAL_RESOURCE_ASSIGN
282: WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID
283: FOR UPDATE OF CAL_RESOURCE_ASSIGN_ID NOWAIT;
284: BEGIN
285: FOR tlinfo IN c1 LOOP
375: v_dup NUMBER := 1;
376: prime_flag_count NUMBER := 0;
377: BEGIN
378: Fnd_Msg_Pub.initialize;
379: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(TO_CHAR(X_RESOURCE_ID)) = FALSE THEN
380: Fnd_Message.set_name('JTF', 'JTF_CAL_REQUIRED');
381: Fnd_Message.set_token('P_NAME', X_RESOURCE_ID);
382: Fnd_Msg_Pub.ADD;
383: v_error := 'Y';
382: Fnd_Msg_Pub.ADD;
383: v_error := 'Y';
384: END IF;
385:
386: IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(X_START_DATE_TIME) = FALSE THEN
387: --fnd_message.set_name('JTF', 'START_DATE CANNOT BE NULL');
388: --app_exception.raise_exception;
389: Fnd_Message.set_name('JTF', 'JTF_CAL_START_DATE');
390: Fnd_Msg_Pub.ADD;
391:
392: v_error := 'Y';
393: END IF;
394:
395: IF Jtf_Cal_Resource_Assign_Pkg.END_GREATER_THAN_BEGIN(X_START_DATE_TIME, X_END_DATE_TIME) = FALSE THEN
396: --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
397: --app_exception.raise_exception;
398: Fnd_Message.set_name('JTF', 'JTF_CAL_END_DATE');
399: Fnd_Message.set_token('P_Start_Date', X_START_DATE_TIME);
401: Fnd_Msg_Pub.ADD;
402: v_error := 'Y';
403: END IF;
404:
405: SELECT COUNT(*) INTO prime_flag_count FROM jtf_cal_resource_assign
406: WHERE resource_id = X_RESOURCE_ID
407: AND resource_type_code = X_RESOURCE_TYPE_CODE
408: AND primary_calendar_flag = 'Y'
409: AND CAL_RESOURCE_ASSIGN_ID <> X_CAL_RESOURCE_ASSIGN_ID
421: Fnd_Msg_Pub.ADD;
422: v_error := 'Y';
423: END IF;
424:
425: SELECT COUNT(*) INTO v_count FROM jtf_cal_resource_assign
426: WHERE resource_id = X_RESOURCE_ID
427: AND resource_type_code = X_RESOURCE_TYPE_CODE
428: AND primary_calendar_flag = 'N'
429: AND CAL_RESOURCE_ASSIGN_ID <> X_CAL_RESOURCE_ASSIGN_ID
457: ELSE
458: X_ERROR := 'N';
459: X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
460:
461: UPDATE JTF_CAL_RESOURCE_ASSIGN SET
462: OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
463: ATTRIBUTE5 = X_ATTRIBUTE5,
464: ATTRIBUTE6 = X_ATTRIBUTE6,
465: ATTRIBUTE7 = X_ATTRIBUTE7,
496: PROCEDURE DELETE_ROW (
497: X_CAL_RESOURCE_ASSIGN_ID IN NUMBER
498: ) IS
499: BEGIN
500: DELETE FROM JTF_CAL_RESOURCE_ASSIGN
501: WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID;
502:
503: IF (SQL%NOTFOUND) THEN
504: RAISE NO_DATA_FOUND;
531:
532:
533: CURSOR DUP IS
534: SELECT resource_type_code,resource_id,start_date_time,end_date_time
535: FROM JTF_CAL_RESOURCE_ASSIGN
536: WHERE calendar_id = X_CALENDAR_ID
537: AND resource_id = X_RESOURCE_ID
538: AND RESOURCE_TYPE_CODE = X_RESOURCE_TYPE_CODE
539: ORDER BY start_date_time
547: v_start_date_time := TO_DATE(X_START_DATE_TIME,'DD/MM/YYYY HH24:MI');
548: v_end_date_time := TO_DATE(X_END_DATE_TIME,'DD/MM/YYYY HH24:MI');
549:
550: SELECT COUNT(*) INTO temp_count
551: FROM JTF_CAL_RESOURCE_ASSIGN
552: WHERE calendar_id = X_CALENDAR_ID
553: AND resource_id = X_RESOURCE_ID
554: AND RESOURCE_TYPE_CODE = X_RESOURCE_TYPE_CODE
555: AND TRUNC(start_date_time) = TRUNC(v_start_date_time)
646: END IF;
647: END;
648:
649:
650: END Jtf_Cal_Resource_Assign_Pkg;