1 PACKAGE BODY PER_LETTER_REQUESTS_PKG as
2 /* $Header: peltr01t.pkb 115.3 2003/01/22 12:24:59 asahay ship $ */
3 --
4 PROCEDURE check_request_unique(X_letter_request_id in number,
5 X_business_group_id in number,
6 X_vacancy_id in number,
7 X_event_id in number,
8 X_letter_type_id in number,
9 X_date_from in date,
10 X_request_status in varchar2)
11 --
12 is
13
14 cursor csr_request is select null
15 from per_letter_requests
16 where (X_letter_request_id is null
17 or (letter_request_id <> X_letter_request_id
18 and X_letter_request_id is not null))
19 and business_group_id + 0 = X_business_group_id
20 and letter_type_id = X_letter_type_id
21 and nvl(vacancy_id,-1) = nvl(X_Vacancy_ID,-1)
22 and date_from = X_date_from
23 and request_status = 'PENDING';
24 --
25
26 v_not_unique boolean := FALSE;
27 g_dummy_number number;
28 --
29 -- Check there are no requests with the same letter pending for this date
30 --
31 begin
32 --
33
34 open csr_request;
35 fetch csr_request into g_dummy_number;
36 v_not_unique := csr_request%found;
37 close csr_request;
38
39 --
40 if v_not_unique then
41 hr_utility.set_message (801,'PER_7350_OUT_LETTER_EXISTS');
42 hr_utility.raise_error;
43 end if;
44 --
45 end check_request_unique;
46 --
47 PROCEDURE check_request_lines (X_letter_request_id in NUMBER)
48 --
49 is cursor c is select null
50 from per_letter_request_lines
51 where letter_request_id = X_letter_request_id;
52 --
53 g_dummy_number number;
54 request_lines_not_exist boolean := FALSE;
55 --
56 -- Check that if the request status is changed to 'Requested' then there
57 -- are request lines for this letter request
58 --
59 begin
60 open c;
61 fetch c into g_dummy_number;
62 request_lines_not_exist := c%notfound;
63 close c;
64 --
65 if request_lines_not_exist then
66 hr_utility.set_message (801,'PER_7351_OUT_LETTER_NO_LINES');
67 hr_utility.raise_error;
68 end if;
69 --
70 hr_utility.set_location('PER_LETTER_REQUESTS_PKG.check_request_lines', 1);
71 --
72 end check_request_lines;
73 --
74 PROCEDURE confirm_delete_lines (X_letter_request_id in NUMBER,
75 X_business_group_id in NUMBER,
76 X_request_lines_exist in out nocopy BOOLEAN) is
77 --
78 cursor c is select null
79 from per_letter_request_lines
80 where letter_request_id = X_letter_request_id;
81 --
82 g_dummy_number number;
83 --
84 -- If request lines exist then ensure the user wishes to delete these
85 -- as well as the request letter itself
86 --
87 begin
88 open c;
89 fetch c into g_dummy_number;
90 --
91 if c%found then
92 X_request_lines_exist := TRUE;
93 end if;
94 close c;
95 --
96 hr_utility.set_location('PER_LETTER_REQUESTS_PKG.confirm_delete_lines', 1);
97 --
98 end confirm_delete_lines;
99 --
100 PROCEDURE Insert_Row(X_Rowid IN OUT nocopy VARCHAR2,
101 X_Letter_Request_Id IN OUT nocopy NUMBER,
102 X_Business_Group_Id NUMBER,
103 X_Letter_Type_Id NUMBER,
104 X_Date_From DATE,
105 X_Request_Status VARCHAR2,
106 X_Auto_Or_Manual VARCHAR2,
107 X_VACANCY_ID NUMBER,
108 X_EVENT_ID NUMBER
109 ) IS
110 CURSOR C IS SELECT rowid FROM per_letter_requests
111 WHERE letter_request_id = X_Letter_Request_Id;
112 --
113 CURSOR C2 IS SELECT per_letter_requests_s.nextval FROM sys.dual;
114 BEGIN
115 if (X_Letter_Request_Id is NULL) then
116 OPEN C2;
117 FETCH C2 INTO X_Letter_Request_Id;
118 CLOSE C2;
119 end if;
120 --
121 INSERT INTO per_letter_requests(
122 letter_request_id,
123 business_group_id,
124 letter_type_id,
125 date_from,
126 request_status,
127 auto_or_manual,
128 vacancy_id,
129 event_id
130 ) VALUES (
131 X_Letter_Request_Id,
132 X_Business_Group_Id,
133 X_Letter_Type_Id,
134 X_Date_From,
135 X_Request_Status,
136 X_Auto_Or_Manual,
137 X_Vacancy_ID,
138 X_Event_ID
139 );
140 --
141 OPEN C;
142 FETCH C INTO X_Rowid;
143 if (C%NOTFOUND) then
144 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
145 hr_utility.set_message_token('PROCEDURE','Insert_Row');
146 hr_utility.set_message_token('STEP','1');
147 hr_utility.raise_error;
148 end if;
149 CLOSE C;
150 END Insert_Row;
151 --
152 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
153 X_Letter_Request_Id NUMBER,
154 X_Business_Group_Id NUMBER,
155 X_Letter_Type_Id NUMBER,
156 X_Date_From DATE,
157 X_Request_Status VARCHAR2,
158 X_Auto_Or_Manual VARCHAR2,
159 X_VACANCY_ID NUMBER,
160 X_EVENT_ID NUMBER
161 ) IS
162 --
163 CURSOR C IS
164 SELECT *
165 FROM per_letter_requests
166 WHERE rowid = X_Rowid
167 FOR UPDATE of letter_request_id NOWAIT;
168 Recinfo C%ROWTYPE;
169 --
170 BEGIN
171 --
172 OPEN C;
173 FETCH C INTO Recinfo;
174 if (C%NOTFOUND) then
175 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
176 hr_utility.set_message_token('PROCEDURE','Lock_Row');
177 hr_utility.set_message_token('STEP','1');
178 hr_utility.raise_error;
179 end if;
180 CLOSE C;
181 --
182 Recinfo.request_status := rtrim(Recinfo.request_status);
183 Recinfo.auto_or_manual := rtrim(Recinfo.auto_or_manual);
184 if (
185 ( (Recinfo.letter_request_id = X_Letter_Request_Id)
186 OR ( (Recinfo.letter_request_id IS NULL)
187 AND (X_Letter_Request_Id IS NULL)))
188 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
189 OR ( (Recinfo.business_group_id IS NULL)
190 AND (X_Business_Group_Id IS NULL)))
191 AND ( (Recinfo.letter_type_id = X_Letter_Type_Id)
192 OR ( (Recinfo.letter_type_id IS NULL)
193 AND (X_Letter_Type_Id IS NULL)))
194 AND ( (Recinfo.date_from = X_Date_From)
195 OR ( (Recinfo.date_from IS NULL)
196 AND (X_Date_From IS NULL)))
197 AND ( (Recinfo.request_status = X_Request_Status)
198 OR ( (Recinfo.request_status IS NULL)
199 AND (X_Request_Status IS NULL)))
200 AND ( (Recinfo.auto_or_manual = X_Auto_Or_Manual)
201 OR ( (Recinfo.auto_or_manual IS NULL)
202 AND (X_Auto_Or_Manual IS NULL)))
203 ) then
204 return;
205 --
206 else
207 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
208 APP_EXCEPTION.RAISE_EXCEPTION;
209 end if;
210 END Lock_Row;
211 --
212 PROCEDURE Update_Row(X_Rowid VARCHAR2,
213 X_Letter_Request_Id NUMBER,
214 X_Business_Group_Id NUMBER,
215 X_Letter_Type_Id NUMBER,
216 X_Date_From DATE,
217 X_Request_Status VARCHAR2,
218 X_Auto_Or_Manual VARCHAR2,
219 X_VACANCY_ID NUMBER,
220 X_EVENT_ID NUMBER
221 ) IS
222 BEGIN
223 --
224 UPDATE per_letter_requests
225 SET
226 letter_request_id = X_Letter_Request_Id,
227 business_group_id = X_Business_Group_Id,
228 letter_type_id = X_Letter_Type_Id,
229 date_from = X_Date_From,
230 request_status = X_Request_Status,
231 auto_or_manual = X_Auto_Or_Manual,
232 vacancy_id = X_Vacancy_ID,
233 event_id = X_Event_ID
234 WHERE rowid = X_rowid;
235 --
236 if (SQL%NOTFOUND) then
237 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
238 hr_utility.set_message_token('PROCEDURE','Update_Row');
239 hr_utility.set_message_token('STEP','1');
240 hr_utility.raise_error;
241 end if;
242 --
243 END Update_Row;
244 --
245 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
246 X_Letter_Request_Id NUMBER) is
247 --
248 cursor csr_lines is select null
249 from per_letter_request_lines
250 where letter_request_id = X_letter_request_id;
251 --
252 g_dummy_number number;
253 v_lines_exist boolean := FALSE;
254 --
255 BEGIN
256 --
257 open csr_lines;
258 fetch csr_lines into g_dummy_number;
259 v_lines_exist := csr_lines%found;
260 close csr_lines;
261 --
262 if v_lines_exist then
263 --
264 DELETE FROM per_letter_request_lines
265 WHERE letter_Request_Id = X_letter_request_id;
266 --
267 if (SQL%NOTFOUND) then
268 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
269 hr_utility.set_message_token('PROCEDURE','Delete_Row');
270 hr_utility.set_message_token('STEP','1');
271 hr_utility.raise_error;
272 end if;
273 --
274 end if;
275 --
276 DELETE FROM per_letter_requests
277 WHERE rowid = X_Rowid;
278 --
279 if (SQL%NOTFOUND) then
280 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
281 hr_utility.set_message_token('PROCEDURE','Delete_Row');
282 hr_utility.set_message_token('STEP','1');
283 hr_utility.raise_error;
284 end if;
285 --
286 END Delete_Row;
287 --
288 PROCEDURE concurrent_program_call(p_application varchar2,
289 p_program varchar2,
290 p_argument1 varchar2,
291 p_argument2 varchar2,
292 p_request_id in out nocopy number) is
293 --
294 -- Submit the concurrent program request and return the request id
295 --
296 begin
297 --
298 p_request_id := FND_REQUEST.SUBMIT_REQUEST
299 (p_application,
300 p_program,
301 NULL,
302 NULL,
303 NULL,
304 p_argument1,
305 p_argument2);
306 --
307 end concurrent_program_call;
308 --
309 END PER_LETTER_REQUESTS_PKG;
[Home] [Help]