[Home] [Help]
PACKAGE BODY: APPS.AP_HOLD_CODES_PKG
Source
1 PACKAGE BODY AP_HOLD_CODES_PKG as
2 /* $Header: apihdcob.pls 120.5 2006/05/05 00:18:10 bghose noship $ */
3
4
5
6
7 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
8 X_Hold_Lookup_Code VARCHAR2,
9 X_calling_sequence IN VARCHAR2
10 ) IS
11 Dummy NUMBER;
12 current_calling_sequence VARCHAR2(2000);
13 debug_info VARCHAR2(100);
14 BEGIN
15 -- Update the calling sequence
16 --
17 current_calling_sequence := 'AP_HOLD_CODES_PKG.CHECK_UNIQUE<-' ||
18 X_calling_sequence;
19
20 -- Check if hold_lookup_code is unique
21 --
22 debug_info := 'Checking hold_lookup_code uniqueness';
23
24 SELECT count(1)
25 INTO Dummy
26 FROM ap_hold_codes
27 WHERE upper(hold_lookup_code) = upper(X_Hold_Lookup_Code)
28 AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
29
30 IF (Dummy >= 1) then
31 FND_MESSAGE.SET_NAME('SQLAP', 'AP_ALL_DUPLICATE_VALUE');
32 APP_EXCEPTION.RAISE_EXCEPTION;
33 END IF;
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 IF (SQLCODE <> -20001) THEN
38 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
39 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
40 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
41 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
42 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
43 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
44 END IF;
45 APP_EXCEPTION.RAISE_EXCEPTION;
46
47 END CHECK_UNIQUE;
48
49
50
51 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
52 X_Hold_Type VARCHAR2,
53 X_Hold_Lookup_Code VARCHAR2,
54 X_Description VARCHAR2 DEFAULT NULL,
55 X_Last_Update_Date DATE,
56 X_Last_Updated_By NUMBER,
57 X_User_Releaseable_Flag VARCHAR2,
58 X_User_Updateable_Flag VARCHAR2,
59 X_Inactive_Date DATE DEFAULT NULL,
60 X_Postable_Flag VARCHAR2,
61 X_Last_Update_Login NUMBER DEFAULT NULL,
62 X_Creation_Date DATE DEFAULT NULL,
63 X_Created_By NUMBER DEFAULT NULL,
64 /* Bug 5206670. Hold Workflow related change */
65 X_Initiate_Workflow_Flag VARCHAR2 DEFAULT NULL,
66 X_Wait_Before_Notify_Days NUMBER DEFAULT NULL,
67 X_Reminder_Days NUMBER DEFAULT NULL,
68 X_Hold_Instruction VARCHAR2 DEFAULT NULL,
69 X_calling_sequence IN VARCHAR2
70 ) IS
71 CURSOR C IS SELECT rowid FROM ap_hold_codes
72 WHERE hold_lookup_code = X_Hold_Lookup_Code;
73 current_calling_sequence VARCHAR2(2000);
74 debug_info VARCHAR2(100);
75 BEGIN
76
77 -- Update the calling sequence
78 --
79 current_calling_sequence := 'AP_HOLD_CODES_PKG.INSERT_ROW<-' ||
80 X_calling_sequence;
81
82 -- Check if unique values
83 AP_HOLD_CODES_PKG.check_unique(X_Rowid, X_Hold_Lookup_Code, current_calling_sequence);
84
85 -- Insert values into ap_hold_codes
86 --
87 debug_info := 'Inserting in ap_hold_codes';
88
89 INSERT INTO ap_hold_codes(
90 hold_type,
91 hold_lookup_code,
92 description,
93 last_update_date,
94 last_updated_by,
95 user_releaseable_flag,
96 user_updateable_flag,
97 inactive_date,
98 postable_flag,
99 last_update_login,
100 creation_date,
101 created_by,
102 initiate_workflow_flag,
103 wait_before_notify_days,
104 reminder_days,
105 hold_instruction
106 ) VALUES (
107
108 X_Hold_Type,
109 X_Hold_Lookup_Code,
110 X_Description,
111 X_Last_Update_Date,
112 X_Last_Updated_By,
113 X_User_Releaseable_Flag,
114 X_User_Updateable_Flag,
115 X_Inactive_Date,
116 X_Postable_Flag,
117 X_Last_Update_Login,
118 X_Creation_Date,
119 X_Created_By,
120 X_initiate_workflow_flag,
121 X_wait_before_notify_days,
122 X_reminder_days,
123 X_Hold_Instruction
124 );
125
126
127 -- Insert values into fnd_lookup_values
128 --
129 debug_info := 'Inserting in fnd_lookup_values';
130
131 INSERT INTO fnd_lookup_values(
132 lookup_type,
133 security_group_id,
134 view_application_id,
135 language,
136 lookup_code,
137 meaning,
138 description,
139 enabled_flag,
140 end_date_active,
141 created_by,
142 creation_date,
143 last_updated_by,
144 last_update_login,
145 last_update_date,
146 source_lang,
147 attribute_category,
148 attribute1,
149 attribute2,
150 attribute3,
151 attribute4,
152 attribute5,
153 attribute6,
154 attribute7,
155 attribute8,
156 attribute9,
157 attribute10,
158 attribute11,
159 attribute12,
160 attribute13,
161 attribute14,
162 attribute15
163 ) select
164 'HOLD CODE',
165 0,
166 200,
167 L.LANGUAGE_CODE,
168 X_Hold_Lookup_Code,
169 X_Hold_Lookup_Code,
170 X_Description,
171 'Y',
172 X_Inactive_Date,
173 X_Created_By,
174 X_Creation_Date,
175 X_Last_Updated_By,
176 X_Last_Update_Login,
177 X_Last_Update_Date,
178 userenv('LANG'),
179 '',
180 '',
181 '',
182 '',
183 '',
184 '',
185 '',
186 '',
187 '',
188 '',
189 '',
190 '',
191 '',
192 '',
193 '',
194 ''
195 from FND_LANGUAGES L
196 where L.INSTALLED_FLAG in ('I', 'B')
197 and not exists
198 (select NULL
199 from fnd_lookup_values FLV
200 where FLV.lookup_type = 'HOLD CODE'
201 and FLV.lookup_code = X_Hold_Lookup_Code
202 and FLV.language = L.LANGUAGE_CODE);
203
204
205 -- Open cursor to check existence of hold_lookup_code in ap_hold_codes
206 --
207 debug_info := 'Open cursor C on ap_hold_codes';
208
209 OPEN C;
210
211 debug_info := 'Fetch cursor C';
212
213 FETCH C INTO X_Rowid;
214 if (C%NOTFOUND) then
215 CLOSE C;
216 Raise NO_DATA_FOUND;
217 end if;
218
219 debug_info := 'Close cursor C';
220 CLOSE C;
221
222 EXCEPTION
223 WHEN OTHERS THEN
224 IF (SQLCODE <> -20001) THEN
225 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
226 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
227 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
228 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
229 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
230 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
231 END IF;
232 APP_EXCEPTION.RAISE_EXCEPTION;
233
234 END Insert_Row;
235
236
237 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
238 X_Hold_Type VARCHAR2,
239 X_Hold_Lookup_Code VARCHAR2,
240 X_Description VARCHAR2 DEFAULT NULL,
241 X_User_Releaseable_Flag VARCHAR2,
242 X_User_Updateable_Flag VARCHAR2,
243 X_Inactive_Date DATE DEFAULT NULL,
244 X_Postable_Flag VARCHAR2,
245 /* Bug 5206670. Hold Workflow related change */
246 X_Initiate_Workflow_Flag VARCHAR2 DEFAULT NULL,
247 X_Wait_Before_Notify_Days NUMBER DEFAULT NULL,
248 X_Reminder_Days NUMBER DEFAULT NULL,
249 X_Hold_Instruction VARCHAR2 DEFAULT NULL,
250 X_calling_sequence IN VARCHAR2
251 ) IS
252 CURSOR C IS
253 SELECT *
254 FROM ap_hold_codes
255 WHERE rowid = X_Rowid
256 FOR UPDATE of hold_lookup_code NOWAIT;
257 Recinfo C%ROWTYPE;
258 current_calling_sequence VARCHAR2(2000);
259 debug_info VARCHAR2(100);
260
261 BEGIN
262 -- Update the calling sequence
263 --
264 current_calling_sequence := 'AP_HOLD_CODES_PKG.LOCK_ROW<-' ||
265 X_calling_sequence;
266
267 -- Open cursor on ap_hold_codes
268 --
269 debug_info := 'Open cursor C on ap_hold_codes';
270
271 OPEN C;
272
273 debug_info := 'Fetch cursor C';
274
275 FETCH C INTO Recinfo;
276 if (C%NOTFOUND) then
277 CLOSE C;
278 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
279 APP_EXCEPTION.Raise_Exception;
280 end if;
281
282 debug_info := 'Close cursor C';
283
284 CLOSE C;
285 if (
286 (Recinfo.hold_type = X_Hold_Type)
287 AND (Recinfo.hold_lookup_code = X_Hold_Lookup_Code)
288 AND ( (Recinfo.description = X_Description)
289 OR ( (Recinfo.description IS NULL)
290 AND (X_Description IS NULL)))
291 AND ( (Recinfo.user_releaseable_flag = X_User_Releaseable_Flag)
292 OR ( (Recinfo.user_releaseable_flag IS NULL)
293 AND (X_User_Releaseable_Flag IS NULL)))
294 AND ( (Recinfo.user_updateable_flag = X_User_Updateable_Flag)
295 OR ( (Recinfo.user_updateable_flag IS NULL)
296 AND (X_User_Updateable_Flag IS NULL)))
297 AND ( (Recinfo.inactive_date = X_Inactive_Date)
298 OR ( (Recinfo.inactive_date IS NULL)
299 AND (X_Inactive_Date IS NULL)))
300 AND ( (Recinfo.postable_flag = X_Postable_Flag)
301 OR ( (Recinfo.postable_flag IS NULL)
302 AND (X_Postable_Flag IS NULL)))
303 AND ( (Recinfo.initiate_workflow_flag = X_Initiate_Workflow_Flag)
304 OR ( (Recinfo.initiate_workflow_flag IS NULL)
305 AND (X_Initiate_Workflow_Flag IS NULL)))
306 AND ( (Recinfo.wait_before_notify_days = X_Wait_Before_Notify_Days)
307 OR ( (Recinfo.wait_before_notify_days IS NULL)
308 AND (X_Wait_Before_Notify_Days IS NULL)))
309 AND ( (Recinfo.reminder_days = X_reminder_Days)
310 OR ( (Recinfo.reminder_days IS NULL)
311 AND (X_Reminder_Days IS NULL)))
312 AND ( (Recinfo.hold_instruction = X_Hold_Instruction)
313 OR ( (Recinfo.hold_instruction IS NULL)
314 AND (X_hold_instruction IS NULL)))
315 ) then
316 return;
317 else
318 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
319 APP_EXCEPTION.Raise_Exception;
320 end if;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 IF (SQLCODE <> -20001) THEN
325 IF (SQLCODE = -54) THEN
326 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
327 ELSE
328 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
329 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
330 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
331 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
332 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
333 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
334 END IF;
335 END IF;
336 APP_EXCEPTION.RAISE_EXCEPTION;
337
338 END Lock_Row;
339
340
341
342 PROCEDURE Update_Row(X_Rowid VARCHAR2,
343 X_Hold_Type VARCHAR2,
344 X_Hold_Lookup_Code VARCHAR2,
345 X_Description VARCHAR2 DEFAULT NULL,
346 X_Last_Update_Date DATE,
350 X_Inactive_Date DATE DEFAULT NULL,
347 X_Last_Updated_By NUMBER,
348 X_User_Releaseable_Flag VARCHAR2,
349 X_User_Updateable_Flag VARCHAR2,
351 X_Postable_Flag VARCHAR2,
352 X_Last_Update_Login NUMBER DEFAULT NULL,
353 X_Creation_Date DATE DEFAULT NULL,
354 X_Created_By NUMBER DEFAULT NULL,
355 /* Bug 5206670. Hold Workflow related change */
356 X_Initiate_Workflow_Flag VARCHAR2 DEFAULT NULL,
357 X_Wait_Before_Notify_Days NUMBER DEFAULT NULL,
358 X_Reminder_Days NUMBER DEFAULT NULL,
359 X_Hold_Instruction VARCHAR2 DEFAULT NULL,
360 X_calling_sequence IN VARCHAR2
361 ) IS
362
363 current_calling_sequence VARCHAR2(2000);
364 debug_info VARCHAR2(100);
365
366 BEGIN
367 -- Update the calling sequence
368 --
369 current_calling_sequence := 'AP_HOLD_CODES_PKG.UPDATE_ROW<-' ||
370 X_calling_sequence;
371
372 -- Updating ap_hold_codes
373 --
374 debug_info := 'Updating ap_hold_codes';
375
376 UPDATE ap_hold_codes
377 SET
378 hold_type = X_Hold_Type,
379 hold_lookup_code = X_Hold_Lookup_Code,
380 description = X_Description,
381 last_update_date = X_Last_Update_Date,
382 last_updated_by = X_Last_Updated_By,
383 user_releaseable_flag = X_User_Releaseable_Flag,
384 user_updateable_flag = X_User_Updateable_Flag,
385 inactive_date = X_Inactive_Date,
386 postable_flag = X_Postable_Flag,
387 last_update_login = X_Last_Update_Login,
388 creation_date = X_Creation_Date,
389 created_by = X_Created_By,
390 initiate_workflow_flag = X_initiate_workflow_flag,
391 wait_before_notify_days = X_wait_before_notify_days,
392 reminder_days = X_reminder_days,
393 hold_instruction = X_Hold_Instruction
394 WHERE rowid = X_Rowid;
395
396
397 -- Updating fnd_lookup_values
398 --
399 debug_info := 'Updating fnd_lookup_values';
400
401 UPDATE fnd_lookup_values
402 SET
403 description = X_Description,
404 last_update_date = X_Last_Update_Date,
405 last_updated_by = X_Last_Updated_By,
406 end_date_active = X_Inactive_Date,
407 last_update_login = X_Last_Update_Login,
408 creation_date = X_Creation_Date,
409 created_by = X_Created_By
410 WHERE lookup_code = X_Hold_Lookup_Code
411 AND lookup_type = 'HOLD CODE'
412 AND view_application_id = 200
413 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
414
415 if (SQL%NOTFOUND) then
416 Raise NO_DATA_FOUND;
417 end if;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF (SQLCODE <> -20001) THEN
422 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
423 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
424 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
425 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
426 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
427 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
428 END IF;
429 APP_EXCEPTION.RAISE_EXCEPTION;
430
431 END Update_Row;
432
433 /*
434 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
435 BEGIN
436 DELETE FROM ap_hold_codes
437 WHERE rowid = X_Rowid;
438
439 if (SQL%NOTFOUND) then
440 Raise NO_DATA_FOUND;
441 end if;
442 END Delete_Row;
443 */
444
445 END AP_HOLD_CODES_PKG;