[Home] [Help]
PACKAGE BODY: APPS.PA_ROLE_JOB_BG_PKG
Source
1 PACKAGE BODY pa_role_job_bg_pkg AS
2 /* $Header: PAXRJBTB.pls 120.0.12010000.2 2009/07/06 07:09:03 paljain 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 if P_JOB_ID is not null
46 then
47 insert into PA_ROLE_JOB_BGS (
48 ROLE_JOB_BG_ID,
49 PROJECT_ROLE_ID,
50 BUSINESS_GROUP_ID,
51 JOB_ID,
52 MIN_JOB_LEVEL,
53 MAX_JOB_LEVEL,
54 OBJECT_VERSION_NUMBER,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN
60 ) values (
61 P_ROLE_JOB_BG_ID,
62 P_PROJECT_ROLE_ID,
63 P_BUSINESS_GROUP_ID,
64 P_JOB_ID,
65 P_MIN_JOB_LEVEL,
66 P_MAX_JOB_LEVEL,
67 P_OBJECT_VERSION_NUMBER,
68 P_CREATION_DATE,
69 P_CREATED_BY,
70 P_LAST_UPDATE_DATE,
71 P_LAST_UPDATED_BY,
72 P_LAST_UPDATE_LOGIN
73 );
74 else
75 FND_MESSAGE.SET_NAME('PA','PA_DFLT_JB_GRP');
76 FND_MSG_PUB.ADD;
77 RAISE FND_API.G_EXC_ERROR;
78 end if;
79
80 -- hr_utility.trace('after insert');
81 END INSERT_ROW;
82
83
84 -- LOCK ROW ------------------------------------------
85 PROCEDURE LOCK_ROW (
86 P_ROLE_JOB_BG_ID IN NUMBER,
87 P_OBJECT_VERSION_NUMBER IN NUMBER
88 ) IS
89
90 CURSOR c
91 IS
92 SELECT *
93 FROM PA_ROLE_JOB_BGS
94 WHERE ROLE_JOB_BG_ID = P_ROLE_JOB_BG_ID
95 FOR UPDATE NOWAIT;
96
97 Recinfo c%ROWTYPE;
98
99 BEGIN
100
101 OPEN c;
102 FETCH c INTO Recinfo;
103 IF (c%NOTFOUND)
104 THEN
105 CLOSE c;
106 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
107 APP_EXCEPTION.Raise_Exception;
108 END IF;
109 CLOSE c;
110
111
112 IF ( ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
113 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
114 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
115 )
116 THEN
117 RETURN;
118 ELSE
119 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
120 APP_EXCEPTION.Raise_Exception;
121 END IF;
122
123 END LOCK_ROW;
124
125 -- UPDATE ROW -----------------------------------------
126 PROCEDURE UPDATE_ROW (
127 P_ROLE_JOB_BG_ID IN NUMBER,
128 P_PROJECT_ROLE_ID IN NUMBER,
129 P_BUSINESS_GROUP_ID IN NUMBER,
130 P_JOB_ID IN NUMBER,
131 P_MIN_JOB_LEVEL IN NUMBER,
132 P_MAX_JOB_LEVEL IN NUMBER,
133 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
134 P_LAST_UPDATE_DATE IN DATE,
135 P_LAST_UPDATED_BY IN NUMBER,
136 P_CREATION_DATE IN DATE,
137 P_CREATED_BY IN NUMBER,
138 P_LAST_UPDATE_LOGIN IN NUMBER
139 ) IS
140
141
142 BEGIN
143 -- Lock the row for update.
144 LOCK_ROW (
145 P_ROLE_JOB_BG_ID,
146 P_OBJECT_VERSION_NUMBER
147 );
148
149 -- Increment the object version number.
150 p_object_version_number := p_object_version_number + 1;
151
152 if P_JOB_ID is not null
153 then
154 update PA_ROLE_JOB_BGS
155 set
156 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID,
157 JOB_ID = P_JOB_ID,
158 MIN_JOB_LEVEL = P_MIN_JOB_LEVEL,
159 MAX_JOB_LEVEL = P_MAX_JOB_LEVEL,
160 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
161 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
162 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
164 WHERE role_job_bg_id = p_role_job_bg_id;
165 else
166 FND_MESSAGE.SET_NAME('PA','PA_DFLT_JB_GRP');
167 FND_MSG_PUB.ADD;
168 RAISE FND_API.G_EXC_ERROR;
169 end if;
170
171 IF (SQL%NOTFOUND)
172 THEN
173 RAISE NO_DATA_FOUND;
174 END IF;
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 END UPDATE_ROW;
180
181
182 -- DELETE ROW -----------------------------------------
183 PROCEDURE DELETE_ROW (
184 P_ROLE_JOB_BG_ID IN NUMBER,
185 P_OBJECT_VERSION_NUMBER IN NUMBER) IS
186
187 BEGIN
188
189 LOCK_ROW (
190 P_ROLE_JOB_BG_ID,
191 P_OBJECT_VERSION_NUMBER
192 );
193
194 delete from PA_ROLE_JOB_BGS
195 where ROLE_JOB_BG_ID = P_ROLE_JOB_BG_ID;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200
201 END Delete_Row;
202
203 END pa_role_job_bg_pkg;