[Home] [Help]
PACKAGE BODY: APPS.PA_ROLE_JOB_BG_PKG
Source
1 PACKAGE BODY pa_role_job_bg_pkg AS
2 /* $Header: PAXRJBTB.pls 115.2 2003/08/25 19:01:02 ramurthy ship $ */
3 -- INSERT ROW -----------------------------------------
4
5 PROCEDURE INSERT_ROW (
6 P_ROLE_JOB_BG_ID OUT NOCOPY NUMBER,
7 P_PROJECT_ROLE_ID IN NUMBER,
8 P_BUSINESS_GROUP_ID IN NUMBER,
9 P_JOB_ID IN NUMBER,
10 P_MIN_JOB_LEVEL IN NUMBER,
11 P_MAX_JOB_LEVEL IN NUMBER,
12 P_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
13 P_LAST_UPDATE_DATE IN DATE,
14 P_LAST_UPDATED_BY IN NUMBER,
15 P_CREATION_DATE IN DATE,
16 P_CREATED_BY IN NUMBER,
17 P_LAST_UPDATE_LOGIN IN NUMBER
18 ) IS
19
20 BEGIN
21
22 -- Initialize object version number.
23 p_object_version_number := 1;
24
25 -- Get the next sequence number for the primary key.
26 select PA_ROLE_JOB_BGS_S.nextval
27 into P_ROLE_JOB_BG_ID
28 from sys.dual;
29
30 -- hr_utility.trace('before insert');
31 -- hr_utility.trace('P_ROLE_JOB_BG_ID IS : ' || P_ROLE_JOB_BG_ID);
32 -- hr_utility.trace('P_PROJECT_ROLE_ID IS : ' || P_PROJECT_ROLE_ID);
33 -- hr_utility.trace('P_BUSINESS_GROUP_ID IS : ' || P_BUSINESS_GROUP_ID);
34 -- hr_utility.trace('P_JOB_ID IS : ' || P_JOB_ID);
35 -- hr_utility.trace('P_MIN_JOB_LEVEL IS : ' || P_MIN_JOB_LEVEL);
36 -- hr_utility.trace('P_MAX_JOB_LEVEL IS : ' || P_MAX_JOB_LEVEL);
37 -- hr_utility.trace('P_OBJECT_VERSION_NUMBER IS : ' || P_OBJECT_VERSION_NUMBER);
38
39 -- hr_utility.trace('P_CREATION_DATE IS : ' || P_CREATION_DATE);
40 -- hr_utility.trace('P_CREATED_BY IS : ' || P_CREATED_BY);
41 -- hr_utility.trace('P_LAST_UPDATE_DATE IS : ' || P_LAST_UPDATE_DATE);
42 -- hr_utility.trace('P_LAST_UPDATED_BY IS : ' || P_LAST_UPDATED_BY);
43 -- hr_utility.trace('P_LAST_UPDATE_LOGIN IS : ' || P_LAST_UPDATE_LOGIN);
44
45 insert into PA_ROLE_JOB_BGS (
46 ROLE_JOB_BG_ID,
47 PROJECT_ROLE_ID,
48 BUSINESS_GROUP_ID,
49 JOB_ID,
50 MIN_JOB_LEVEL,
51 MAX_JOB_LEVEL,
52 OBJECT_VERSION_NUMBER,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN
58 ) values (
59 P_ROLE_JOB_BG_ID,
60 P_PROJECT_ROLE_ID,
61 P_BUSINESS_GROUP_ID,
62 P_JOB_ID,
63 P_MIN_JOB_LEVEL,
64 P_MAX_JOB_LEVEL,
65 P_OBJECT_VERSION_NUMBER,
66 P_CREATION_DATE,
67 P_CREATED_BY,
68 P_LAST_UPDATE_DATE,
69 P_LAST_UPDATED_BY,
70 P_LAST_UPDATE_LOGIN
71 );
72
73 -- hr_utility.trace('after insert');
74 END INSERT_ROW;
75
76
77 -- LOCK ROW ------------------------------------------
78 PROCEDURE LOCK_ROW (
79 P_ROLE_JOB_BG_ID IN NUMBER,
80 P_OBJECT_VERSION_NUMBER IN NUMBER
81 ) IS
82
83 CURSOR c
84 IS
85 SELECT *
86 FROM PA_ROLE_JOB_BGS
87 WHERE ROLE_JOB_BG_ID = P_ROLE_JOB_BG_ID
88 FOR UPDATE NOWAIT;
89
90 Recinfo c%ROWTYPE;
91
92 BEGIN
93
94 OPEN c;
95 FETCH c INTO Recinfo;
96 IF (c%NOTFOUND)
97 THEN
98 CLOSE c;
99 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
100 APP_EXCEPTION.Raise_Exception;
101 END IF;
102 CLOSE c;
103
104
105 IF ( ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
106 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
107 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
108 )
109 THEN
110 RETURN;
111 ELSE
112 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
113 APP_EXCEPTION.Raise_Exception;
114 END IF;
115
116 END LOCK_ROW;
117
118 -- UPDATE ROW -----------------------------------------
119 PROCEDURE UPDATE_ROW (
120 P_ROLE_JOB_BG_ID IN NUMBER,
121 P_PROJECT_ROLE_ID IN NUMBER,
122 P_BUSINESS_GROUP_ID IN NUMBER,
123 P_JOB_ID IN NUMBER,
124 P_MIN_JOB_LEVEL IN NUMBER,
125 P_MAX_JOB_LEVEL IN NUMBER,
126 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
127 P_LAST_UPDATE_DATE IN DATE,
128 P_LAST_UPDATED_BY IN NUMBER,
129 P_CREATION_DATE IN DATE,
130 P_CREATED_BY IN NUMBER,
131 P_LAST_UPDATE_LOGIN IN NUMBER
132 ) IS
133
134
135 BEGIN
136 -- Lock the row for update.
137 LOCK_ROW (
138 P_ROLE_JOB_BG_ID,
139 P_OBJECT_VERSION_NUMBER
140 );
141
142 -- Increment the object version number.
143 p_object_version_number := p_object_version_number + 1;
144
145 update PA_ROLE_JOB_BGS
146 set
147 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID,
148 JOB_ID = P_JOB_ID,
149 MIN_JOB_LEVEL = P_MIN_JOB_LEVEL,
150 MAX_JOB_LEVEL = P_MAX_JOB_LEVEL,
151 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
152 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
153 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
154 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
155 WHERE role_job_bg_id = p_role_job_bg_id;
156
157 IF (SQL%NOTFOUND)
158 THEN
159 RAISE NO_DATA_FOUND;
160 END IF;
161 if (sql%notfound) then
162 raise no_data_found;
163 end if;
164
165 END UPDATE_ROW;
166
167
168 -- DELETE ROW -----------------------------------------
169 PROCEDURE DELETE_ROW (
170 P_ROLE_JOB_BG_ID IN NUMBER,
171 P_OBJECT_VERSION_NUMBER IN NUMBER) IS
172
173 BEGIN
174
175 LOCK_ROW (
176 P_ROLE_JOB_BG_ID,
177 P_OBJECT_VERSION_NUMBER
178 );
179
180 delete from PA_ROLE_JOB_BGS
181 where ROLE_JOB_BG_ID = P_ROLE_JOB_BG_ID;
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186
187 END Delete_Row;
188
189 END pa_role_job_bg_pkg;