[Home] [Help]
PACKAGE BODY: APPS.AP_HOLD_CODES_PKG
Source
1 PACKAGE BODY AP_HOLD_CODES_PKG as
2 /* $Header: apihdcob.pls 120.5.12010000.2 2009/11/30 05:20:27 asansari ship $ */
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,
67 X_Reminder_Days 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,
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 --Bug9009032: Modified cursor C.
253 CURSOR C IS
254 SELECT AHC.HOLD_TYPE HOLD_TYPE
255 , AHC.HOLD_LOOKUP_CODE HOLD_LOOKUP_CODE
256 ,ALC.DESCRIPTION DESCRIPTION
257 , AHC.USER_RELEASEABLE_FLAG USER_RELEASEABLE_FLAG
258 , AHC.USER_UPDATEABLE_FLAG USER_UPDATEABLE_FLAG
259 , AHC.INACTIVE_DATE INACTIVE_DATE
260 , AHC.POSTABLE_FLAG POSTABLE_FLAG
261 , AHC.INITIATE_WORKFLOW_FLAG INITIATE_WORKFLOW_FLAG
262 , AHC.WAIT_BEFORE_NOTIFY_DAYS WAIT_BEFORE_NOTIFY_DAYS
263 , AHC.REMINDER_DAYS REMINDER_DAYS
264 , AHC.HOLD_INSTRUCTION HOLD_INSTRUCTION
265 FROM ap_hold_codes ahc,
266 ap_lookup_codes alc
267 WHERE ahc.rowid = X_Rowid
268 and ahc.hold_lookup_code = alc.lookup_code
269 and alc.LOOKUP_TYPE = 'HOLD CODE'
270 FOR UPDATE of ahc.hold_lookup_code NOWAIT;
271 Recinfo C%ROWTYPE;
272 current_calling_sequence VARCHAR2(2000);
273 debug_info VARCHAR2(100);
274
275 BEGIN
276 -- Update the calling sequence
277 --
278 current_calling_sequence := 'AP_HOLD_CODES_PKG.LOCK_ROW<-' ||
279 X_calling_sequence;
280
281 -- Open cursor on ap_hold_codes
282 --
283 debug_info := 'Open cursor C on ap_hold_codes';
284
285 OPEN C;
286
287 debug_info := 'Fetch cursor C';
288
289 FETCH C INTO Recinfo;
290 if (C%NOTFOUND) then
291 CLOSE C;
292 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
293 APP_EXCEPTION.Raise_Exception;
294 end if;
295
296 debug_info := 'Close cursor C';
297
298 CLOSE C;
299 if (
300 (Recinfo.hold_type = X_Hold_Type)
301 AND (Recinfo.hold_lookup_code = X_Hold_Lookup_Code)
302 AND ( (Recinfo.description = X_Description)
303 OR ( (Recinfo.description IS NULL)
304 AND (X_Description IS NULL)))
305 AND ( (Recinfo.user_releaseable_flag = X_User_Releaseable_Flag)
306 OR ( (Recinfo.user_releaseable_flag IS NULL)
307 AND (X_User_Releaseable_Flag IS NULL)))
308 AND ( (Recinfo.user_updateable_flag = X_User_Updateable_Flag)
309 OR ( (Recinfo.user_updateable_flag IS NULL)
310 AND (X_User_Updateable_Flag IS NULL)))
311 AND ( (Recinfo.inactive_date = X_Inactive_Date)
312 OR ( (Recinfo.inactive_date IS NULL)
313 AND (X_Inactive_Date IS NULL)))
314 AND ( (Recinfo.postable_flag = X_Postable_Flag)
315 OR ( (Recinfo.postable_flag IS NULL)
316 AND (X_Postable_Flag IS NULL)))
317 AND ( (Recinfo.initiate_workflow_flag = X_Initiate_Workflow_Flag)
318 OR ( (Recinfo.initiate_workflow_flag IS NULL)
319 AND (X_Initiate_Workflow_Flag IS NULL)))
320 AND ( (Recinfo.wait_before_notify_days = X_Wait_Before_Notify_Days)
321 OR ( (Recinfo.wait_before_notify_days IS NULL)
322 AND (X_Wait_Before_Notify_Days IS NULL)))
323 AND ( (Recinfo.reminder_days = X_reminder_Days)
324 OR ( (Recinfo.reminder_days IS NULL)
325 AND (X_Reminder_Days IS NULL)))
326 AND ( (Recinfo.hold_instruction = X_Hold_Instruction)
327 OR ( (Recinfo.hold_instruction IS NULL)
328 AND (X_hold_instruction IS NULL)))
329 ) then
330 return;
331 else
332 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
333 APP_EXCEPTION.Raise_Exception;
334 end if;
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 IF (SQLCODE <> -20001) THEN
339 IF (SQLCODE = -54) THEN
340 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
341 ELSE
342 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
343 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
344 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
345 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
346 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
347 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
348 END IF;
349 END IF;
350 APP_EXCEPTION.RAISE_EXCEPTION;
351
352 END Lock_Row;
353
354
355
356 PROCEDURE Update_Row(X_Rowid VARCHAR2,
357 X_Hold_Type VARCHAR2,
358 X_Hold_Lookup_Code VARCHAR2,
359 X_Description VARCHAR2 DEFAULT NULL,
360 X_Last_Update_Date DATE,
361 X_Last_Updated_By NUMBER,
362 X_User_Releaseable_Flag VARCHAR2,
363 X_User_Updateable_Flag VARCHAR2,
364 X_Inactive_Date DATE DEFAULT NULL,
365 X_Postable_Flag VARCHAR2,
366 X_Last_Update_Login NUMBER DEFAULT NULL,
367 X_Creation_Date DATE DEFAULT NULL,
368 X_Created_By NUMBER DEFAULT NULL,
369 /* Bug 5206670. Hold Workflow related change */
370 X_Initiate_Workflow_Flag VARCHAR2 DEFAULT NULL,
371 X_Wait_Before_Notify_Days NUMBER DEFAULT NULL,
372 X_Reminder_Days NUMBER DEFAULT NULL,
373 X_Hold_Instruction VARCHAR2 DEFAULT NULL,
374 X_calling_sequence IN VARCHAR2
375 ) IS
376
377 current_calling_sequence VARCHAR2(2000);
378 debug_info VARCHAR2(100);
379
380 BEGIN
381 -- Update the calling sequence
382 --
383 current_calling_sequence := 'AP_HOLD_CODES_PKG.UPDATE_ROW<-' ||
384 X_calling_sequence;
385
386 -- Updating ap_hold_codes
387 --
388 debug_info := 'Updating ap_hold_codes';
389
390 UPDATE ap_hold_codes
391 SET
392 hold_type = X_Hold_Type,
393 hold_lookup_code = X_Hold_Lookup_Code,
394 description = X_Description,
395 last_update_date = X_Last_Update_Date,
396 last_updated_by = X_Last_Updated_By,
397 user_releaseable_flag = X_User_Releaseable_Flag,
398 user_updateable_flag = X_User_Updateable_Flag,
399 inactive_date = X_Inactive_Date,
400 postable_flag = X_Postable_Flag,
401 last_update_login = X_Last_Update_Login,
402 creation_date = X_Creation_Date,
403 created_by = X_Created_By,
404 initiate_workflow_flag = X_initiate_workflow_flag,
405 wait_before_notify_days = X_wait_before_notify_days,
406 reminder_days = X_reminder_days,
407 hold_instruction = X_Hold_Instruction
408 WHERE rowid = X_Rowid;
409
410
411 -- Updating fnd_lookup_values
412 --
413 debug_info := 'Updating fnd_lookup_values';
414
415 UPDATE fnd_lookup_values
416 SET
417 description = X_Description,
418 last_update_date = X_Last_Update_Date,
419 last_updated_by = X_Last_Updated_By,
420 end_date_active = X_Inactive_Date,
421 last_update_login = X_Last_Update_Login,
422 creation_date = X_Creation_Date,
423 created_by = X_Created_By
424 WHERE lookup_code = X_Hold_Lookup_Code
425 AND lookup_type = 'HOLD CODE'
426 AND view_application_id = 200
427 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
428
429 if (SQL%NOTFOUND) then
430 Raise NO_DATA_FOUND;
431 end if;
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 IF (SQLCODE <> -20001) THEN
436 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
437 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
438 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
439 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
440 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
441 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
442 END IF;
443 APP_EXCEPTION.RAISE_EXCEPTION;
444
445 END Update_Row;
446
447 /*
448 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
449 BEGIN
450 DELETE FROM ap_hold_codes
451 WHERE rowid = X_Rowid;
452
453 if (SQL%NOTFOUND) then
454 Raise NO_DATA_FOUND;
455 end if;
456 END Delete_Row;
457 */
458
459 END AP_HOLD_CODES_PKG;