[Home] [Help]
PACKAGE BODY: APPS.AP_HOLDS_PKG
Source
1 PACKAGE BODY AP_HOLDS_PKG AS
2 /* $Header: apiholdb.pls 120.10.12010000.2 2008/11/12 10:46:14 kpasikan ship $ */
3
4
5 /* */
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_HOLDS_PKG';
7 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
8 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
9 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
10 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
11 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
12 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
13 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
14
15 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
18 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
19 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
20 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
21 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
22 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_HOLDS_PKG.';
23
24 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
25 x_hold_id in out nocopy number, --5128839
26 X_Invoice_Id NUMBER,
27 X_Line_Location_Id NUMBER,
28 X_Hold_Lookup_Code VARCHAR2,
29 X_Last_Update_Date DATE,
30 X_Last_Updated_By NUMBER,
31 X_Held_By NUMBER,
32 X_Hold_Date DATE,
33 X_Hold_Reason VARCHAR2,
34 X_Release_Lookup_Code VARCHAR2,
35 X_Release_Reason VARCHAR2,
36 X_Status_Flag VARCHAR2,
37 X_Last_Update_Login NUMBER,
38 X_Creation_Date DATE,
39 X_Created_By NUMBER,
40 X_Responsibility_Id NUMBER,
41 X_Attribute1 VARCHAR2,
42 X_Attribute2 VARCHAR2,
43 X_Attribute3 VARCHAR2,
44 X_Attribute4 VARCHAR2,
45 X_Attribute5 VARCHAR2,
46 X_Attribute6 VARCHAR2,
47 X_Attribute7 VARCHAR2,
48 X_Attribute8 VARCHAR2,
49 X_Attribute9 VARCHAR2,
50 X_Attribute10 VARCHAR2,
51 X_Attribute11 VARCHAR2,
52 X_Attribute12 VARCHAR2,
53 X_Attribute13 VARCHAR2,
54 X_Attribute14 VARCHAR2,
55 X_Attribute15 VARCHAR2,
56 X_Attribute_Category VARCHAR2,
57 X_Org_Id NUMBER,
58 X_calling_sequence IN VARCHAR2
59 ) IS
60 CURSOR C IS SELECT rowid FROM AP_HOLDS
61 WHERE invoice_id = X_Invoice_Id
62 AND ( (line_location_id = X_Line_Location_Id)
63 or (line_location_id is NULL and X_Line_Location_Id is NULL))
64 AND hold_lookup_code = X_Hold_Lookup_Code;
65 current_calling_sequence VARCHAR2(2000);
66 debug_info VARCHAR2(100);
67 l_user_releaseable_flag VARCHAR2(1);
68 l_initiate_workflow_flag VARCHAR2(1);
69
70 BEGIN
71
72 -- Update the calling sequence
73 --
74 current_calling_sequence := 'AP_HOLDS_PKG.INSERT_ROW<-' ||
75 X_calling_sequence;
76
77
78 select ap_holds_s.nextval
79 into x_hold_id
80 from dual;
81
82 debug_info := 'Insert into AP_HOLDS';
83 INSERT INTO AP_HOLDS(
84 hold_id,
85 invoice_id,
86 line_location_id,
87 hold_lookup_code,
88 last_update_date,
89 last_updated_by,
90 held_by,
91 hold_date,
92 hold_reason,
93 release_lookup_code,
94 release_reason,
95 status_flag,
96 last_update_login,
97 creation_date,
98 created_by,
99 responsibility_id,
100 attribute1,
101 attribute2,
102 attribute3,
103 attribute4,
104 attribute5,
105 attribute6,
106 attribute7,
107 attribute8,
108 attribute9,
109 attribute10,
110 attribute11,
111 attribute12,
112 attribute13,
113 attribute14,
114 attribute15,
115 attribute_category,
116 org_id
117 ) VALUES (
118 x_hold_id,
119 X_Invoice_Id,
120 X_Line_Location_Id,
121 X_Hold_Lookup_Code,
122 X_Last_Update_Date,
123 X_Last_Updated_By,
124 X_Held_By,
125 X_Hold_Date,
126 X_Hold_Reason,
127 X_Release_Lookup_Code,
128 X_Release_Reason,
129 X_Status_Flag,
130 X_Last_Update_Login,
131 X_Creation_Date,
132 X_Created_By,
133 X_Responsibility_Id,
134 X_Attribute1,
135 X_Attribute2,
136 X_Attribute3,
137 X_Attribute4,
138 X_Attribute5,
139 X_Attribute6,
140 X_Attribute7,
141 X_Attribute8,
142 X_Attribute9,
143 X_Attribute10,
144 X_Attribute11,
145 X_Attribute12,
146 X_Attribute13,
147 X_Attribute14,
148 X_Attribute15,
149 X_Attribute_Category,
150 X_Org_Id
151 );
152
153 -- See if this is user releaseable and
154 -- Workflow enabled hold. If so, start the
155 -- workflow.
156 /* bug 5206670. Hold Workflow */
157 SELECT nvl(user_releaseable_flag,'N'),
158 nvl(initiate_workflow_flag,'N')
159 INTO l_user_releaseable_flag,
160 l_initiate_workflow_flag
161 FROM ap_hold_codes
162 WHERE hold_lookup_code = X_Hold_Lookup_Code;
163
164 IF (l_user_releaseable_flag = 'Y' AND
165 l_initiate_workflow_flag = 'Y') THEN
166
167 AP_WORKFLOW_PKG.create_hold_wf_process(x_hold_id);
168
169 END IF;
170
171 --Bug 4539462 DBI logging
172 AP_DBI_PKG.Maintain_DBI_Summary
173 (p_table_name => 'AP_HOLDS',
174 p_operation => 'I',
175 p_key_value1 => X_invoice_id,
176 p_calling_sequence => current_calling_sequence);
177
178 debug_info := 'Open cursor C';
179 OPEN C;
180 debug_info := 'Fetch cursor C';
181 FETCH C INTO X_Rowid;
182 if (C%NOTFOUND) then
183 debug_info := 'Close cursor C - ROW NOTFOUND';
184 CLOSE C;
185 Raise NO_DATA_FOUND;
186 end if;
187 debug_info := 'Close cursor C';
188 CLOSE C;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 IF (SQLCODE <> -20001) THEN
193 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
194 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
195 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
196 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
197 ', INVOICE_ID == ' || TO_CHAR(X_Invoice_Id) ||
198 ', LINE_LOCATION_ID = ' || TO_CHAR(X_Line_Location_Id) ||
199 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
200 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
201 END IF;
202 APP_EXCEPTION.RAISE_EXCEPTION;
203
204
205 END Insert_Row;
206
207
208 /* */
209 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
210
211 X_Invoice_Id NUMBER,
212 X_Line_Location_Id NUMBER,
213 X_Hold_Lookup_Code VARCHAR2,
214 X_Held_By NUMBER,
215 X_Hold_Date DATE,
216 X_Hold_Reason VARCHAR2,
217 X_Release_Lookup_Code VARCHAR2,
218 X_Release_Reason VARCHAR2,
219 X_Status_Flag VARCHAR2,
220 X_Responsibility_Id NUMBER,
221 X_Attribute1 VARCHAR2,
222 X_Attribute2 VARCHAR2,
223 X_Attribute3 VARCHAR2,
224 X_Attribute4 VARCHAR2,
225 X_Attribute5 VARCHAR2,
226 X_Attribute6 VARCHAR2,
227 X_Attribute7 VARCHAR2,
228 X_Attribute8 VARCHAR2,
229 X_Attribute9 VARCHAR2,
230 X_Attribute10 VARCHAR2,
231 X_Attribute11 VARCHAR2,
232 X_Attribute12 VARCHAR2,
233 X_Attribute13 VARCHAR2,
234 X_Attribute14 VARCHAR2,
235 X_Attribute15 VARCHAR2,
236 X_Attribute_Category VARCHAR2,
237 X_Org_Id NUMBER,
238 X_calling_sequence IN VARCHAR2
239 ) IS
240 CURSOR C IS
241 SELECT *
242 FROM AP_HOLDS
243 WHERE rowid = X_Rowid
244 FOR UPDATE of Invoice_Id NOWAIT;
245 Recinfo C%ROWTYPE;
246 current_calling_sequence VARCHAR2(2000);
247 debug_info VARCHAR2(100);
248
249
250 BEGIN
251 -- Update the calling sequence
252 --
253 current_calling_sequence := 'AP_HOLDS_PKG.LOCK_ROW<-' ||
254 X_calling_sequence;
255 debug_info := 'Open cursor C';
256 OPEN C;
257 debug_info := 'Fetch cursor C';
258 FETCH C INTO Recinfo;
259 if (C%NOTFOUND) then
260 debug_info := 'Close cursor C - ROW NOTFOUND';
261 CLOSE C;
262 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
263 APP_EXCEPTION.Raise_Exception;
264 end if;
265 debug_info := 'Close cursor C';
266 CLOSE C;
267 if (
268
269 (Recinfo.invoice_id = X_Invoice_Id)
270 AND ( (Recinfo.line_location_id = X_Line_Location_Id)
271 OR ( (Recinfo.line_location_id IS NULL)
272 AND (X_Line_Location_Id IS NULL)))
273 AND (Recinfo.hold_lookup_code = X_Hold_Lookup_Code)
274 AND (Recinfo.held_by = X_Held_By)
275 AND (trunc(Recinfo.hold_date) = trunc(X_Hold_Date)) --Bug 2909797,6143486
276 AND ( (Recinfo.hold_reason = X_Hold_Reason)
277 OR ( (Recinfo.hold_reason IS NULL)
278 AND (X_Hold_Reason IS NULL)))
279 AND ( (Recinfo.release_lookup_code = X_Release_Lookup_Code)
280 OR ( (Recinfo.release_lookup_code IS NULL)
281 AND (X_Release_Lookup_Code IS NULL)))
282 AND ( (Recinfo.release_reason = X_Release_Reason)
283 OR ( (Recinfo.release_reason IS NULL)
284 AND (X_Release_Reason IS NULL)))
285 AND ( (Recinfo.status_flag = X_Status_Flag)
286 OR ( (Recinfo.status_flag IS NULL)
287 AND (X_Status_Flag IS NULL)))
288 AND ( (Recinfo.Responsibility_Id = X_Responsibility_Id)
289 OR ( (Recinfo.Responsibility_Id IS NULL)
290 AND (X_Responsibility_Id IS NULL)))
291 AND ( (Recinfo.attribute1 = X_Attribute1)
292 OR ( (Recinfo.attribute1 IS NULL)
293 AND (X_Attribute1 IS NULL)))
294 AND ( (Recinfo.attribute2 = X_Attribute2)
295 OR ( (Recinfo.attribute2 IS NULL)
296 AND (X_Attribute2 IS NULL)))
297 AND ( (Recinfo.attribute3 = X_Attribute3)
298 OR ( (Recinfo.attribute3 IS NULL)
299 AND (X_Attribute3 IS NULL)))
300 AND ( (Recinfo.attribute4 = X_Attribute4)
301 OR ( (Recinfo.attribute4 IS NULL)
302 AND (X_Attribute4 IS NULL)))
303 AND ( (Recinfo.attribute5 = X_Attribute5)
304 OR ( (Recinfo.attribute5 IS NULL)
305 AND (X_Attribute5 IS NULL)))
306 AND ( (Recinfo.attribute6 = X_Attribute6)
307 OR ( (Recinfo.attribute6 IS NULL)
308 AND (X_Attribute6 IS NULL)))
309 AND ( (Recinfo.attribute7 = X_Attribute7)
310 OR ( (Recinfo.attribute7 IS NULL)
311 AND (X_Attribute7 IS NULL)))
312 AND ( (Recinfo.attribute8 = X_Attribute8)
313 OR ( (Recinfo.attribute8 IS NULL)
314 AND (X_Attribute8 IS NULL)))
315 AND ( (Recinfo.attribute9 = X_Attribute9)
316 OR ( (Recinfo.attribute9 IS NULL)
317 AND (X_Attribute9 IS NULL)))
318 AND ( (Recinfo.attribute10 = X_Attribute10)
319 OR ( (Recinfo.attribute10 IS NULL)
320 AND (X_Attribute10 IS NULL)))
321 AND ( (Recinfo.attribute11 = X_Attribute11)
322 OR ( (Recinfo.attribute11 IS NULL)
323 AND (X_Attribute11 IS NULL)))
324 AND ( (Recinfo.attribute12 = X_Attribute12)
325 OR ( (Recinfo.attribute12 IS NULL)
326 AND (X_Attribute12 IS NULL)))
327 AND ( (Recinfo.attribute13 = X_Attribute13)
328 OR ( (Recinfo.attribute13 IS NULL)
329 AND (X_Attribute13 IS NULL)))
330 AND ( (Recinfo.attribute14 = X_Attribute14)
331 OR ( (Recinfo.attribute14 IS NULL)
332 AND (X_Attribute14 IS NULL)))
333 AND ( (Recinfo.attribute15 = X_Attribute15)
334 OR ( (Recinfo.attribute15 IS NULL)
335 AND (X_Attribute15 IS NULL)))
336 AND ( (Recinfo.attribute_category = X_Attribute_Category)
337 OR ( (Recinfo.attribute_category IS NULL)
338 AND (X_Attribute_Category IS NULL)))
339 AND ( (Recinfo.org_id = X_Org_Id)
340 OR ( (Recinfo.org_id IS NULL)
341 AND (X_Org_Id IS NULL)))
342 ) then
343 return;
344 else
345 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
346 APP_EXCEPTION.Raise_Exception;
347 end if;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 IF (SQLCODE <> -20001) THEN
352 IF (SQLCODE = -54) THEN
353 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
354 ELSE
355 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
356 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
357 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
358 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
359 ', INVOICE_ID == ' || TO_CHAR(X_Invoice_Id) ||
360 ', LINE_LOCATION_ID = ' || TO_CHAR(X_Line_Location_Id) ||
361 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
362 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
363 END IF;
364 END IF;
365 APP_EXCEPTION.RAISE_EXCEPTION;
366
367 END Lock_Row;
368
369
370 /* */
371 PROCEDURE Update_Row(X_Rowid VARCHAR2,
372
373 X_Invoice_Id NUMBER,
374 X_Line_Location_Id NUMBER,
375 X_Hold_Lookup_Code VARCHAR2,
376 X_Last_Update_Date DATE,
377 X_Last_Updated_By NUMBER,
378 X_Held_By NUMBER,
379 X_Hold_Date DATE,
380 X_Hold_Reason VARCHAR2,
381 X_Release_Lookup_Code VARCHAR2,
382 X_Release_Reason VARCHAR2,
383 X_Status_Flag VARCHAR2,
384 X_Last_Update_Login NUMBER,
385 X_Responsibility_Id NUMBER,
386 X_Attribute1 VARCHAR2,
387 X_Attribute2 VARCHAR2,
388 X_Attribute3 VARCHAR2,
389 X_Attribute4 VARCHAR2,
390 X_Attribute5 VARCHAR2,
391 X_Attribute6 VARCHAR2,
392 X_Attribute7 VARCHAR2,
393 X_Attribute8 VARCHAR2,
394 X_Attribute9 VARCHAR2,
395 X_Attribute10 VARCHAR2,
396 X_Attribute11 VARCHAR2,
397 X_Attribute12 VARCHAR2,
398 X_Attribute13 VARCHAR2,
399 X_Attribute14 VARCHAR2,
400 X_Attribute15 VARCHAR2,
401 X_Attribute_Category VARCHAR2,
402 X_Wf_Status VARCHAR2,
403 X_calling_sequence IN VARCHAR2
404
405 ) IS
406
407 l_invoice_amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
408 l_payment_status_flag AP_INVOICES_ALL.PAYMENT_STATUS_FLAG%TYPE;
409 l_invoice_type_lookup_code AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
410 l_tax_hold_codes AP_ETAX_SERVICES_PKG.Rel_Hold_Codes_Type;
411 l_approval_status VARCHAR2(100);
412 l_success BOOLEAN := TRUE;
413 l_error_code VARCHAR2(4000);
414 current_calling_sequence VARCHAR2(2000);
415 debug_info VARCHAR2(100);
416
417 BEGIN
418 -- Update the calling sequence
419 --
420 current_calling_sequence := 'AP_HOLDS_PKG.UPDATE_ROW<-' ||
421 X_calling_sequence;
422 debug_info := 'Update AP_HOLDS';
423 UPDATE AP_HOLDS
424 SET
425 invoice_id = X_Invoice_Id,
426 line_location_id = X_Line_Location_Id,
427 hold_lookup_code = X_Hold_Lookup_Code,
428 last_update_date = X_Last_Update_Date,
429 last_updated_by = X_Last_Updated_By,
430 held_by = X_Held_By,
431 hold_date = X_Hold_Date,
432 hold_reason = X_Hold_Reason,
433 release_lookup_code = X_Release_Lookup_Code,
434 release_reason = X_Release_Reason,
435 status_flag = X_Status_Flag,
436 last_update_login = X_Last_Update_Login,
437 Responsibility_Id = X_Responsibility_Id,
438 attribute1 = X_Attribute1,
439 attribute2 = X_Attribute2,
440 attribute3 = X_Attribute3,
441 attribute4 = X_Attribute4,
442 attribute5 = X_Attribute5,
443 attribute6 = X_Attribute6,
444 attribute7 = X_Attribute7,
445 attribute8 = X_Attribute8,
446 attribute9 = X_Attribute9,
447 attribute10 = X_Attribute10,
448 attribute11 = X_Attribute11,
449 attribute12 = X_Attribute12,
450 attribute13 = X_Attribute13,
451 attribute14 = X_Attribute14,
452 attribute15 = X_Attribute15,
453 attribute_category = X_Attribute_Category,
454 /* bug 5206670. Hold Workflow */
455 wf_status = Decode(X_Wf_Status, 'STARTED', 'MANUALLYRELEASED',
456 X_WF_Status)
457 WHERE rowid = X_Rowid;
458
459 --Bug 4539462 DBI logging
460 AP_DBI_PKG.Maintain_DBI_Summary
461 (p_table_name => 'AP_HOLDS',
462 p_operation => 'U',
463 p_key_value1 => X_invoice_id,
464 p_calling_sequence => current_calling_sequence);
465
466 --ETAX: Invwkb
467 --All the code below this comment is added for ETAX.
468 --Initialize the PL/SQL table
469 FOR num in 1..10 LOOP
470 l_tax_hold_codes(num) := NULL;
471 END LOOP;
472
473
474 --Unlike the code in quick_release we need to call this below api ,
475 --regardless of whether the invoice status
476 --after releasing this hold would go to 'APPROVED'or not, since this procedure
477 --is called record by record, there is no way to figure it whether the user is
478 --updating all the holds on the invoice, there by making the status 'APPROVED' or
479 --just releasing this particular hold.
480 IF (x_release_lookup_code IN ('TAX AMOUNT RANGE','TAX VARIANCE')) THEN
481
482 l_tax_hold_codes(1) := x_release_lookup_code;
483
484 l_success := ap_etax_services_pkg.release_tax_holds(
485 p_invoice_id => x_invoice_id,
486 p_calling_mode => 'RELEASE TAX HOLDS',
487 p_tax_hold_code => l_tax_hold_codes,
488 p_all_error_messages => 'N',
489 p_error_code => l_error_code,
490 p_calling_sequence => current_calling_sequence);
491
492 IF (NOT l_success) THEN
493 FND_MESSAGE.SET_NAME('SQLAP','AP_ETX_CANNOT_REL_TAX_HOLDS');
494 FND_MESSAGE.SET_TOKEN('REASON',l_error_code);
495 APP_EXCEPTION.RAISE_EXCEPTION;
496 END IF;
497
498 END IF;
499
500 SELECT invoice_amount,
501 payment_status_flag,
502 invoice_type_lookup_code
503 INTO l_invoice_amount,
504 l_payment_status_flag,
505 l_invoice_type_lookup_code
506 FROM ap_invoices
507 WHERE invoice_id = x_invoice_id;
508
509 l_approval_status := ap_invoices_pkg.get_approval_status(x_invoice_id,
510 l_invoice_amount,
511 l_payment_status_flag,
512 l_invoice_type_lookup_code);
513
514 IF (l_approval_status IN ('APPROVED','AVAILABLE','UNPAID','FULL'))THEN
515 IF (l_success) THEN
516
517 l_success := ap_etax_pkg.calling_etax(
518 p_invoice_id => x_invoice_id,
519 p_calling_mode => 'FREEZE INVOICE',
520 p_all_error_messages => 'N',
521 p_error_code => l_error_code,
522 p_calling_sequence => current_calling_sequence);
523
524 IF (not l_success) THEN
525
526 FND_MESSAGE.SET_NAME('SQLAP','AP_ETX_CANNOT_FRZ_INV');
527 FND_MESSAGE.SET_TOKEN('REASON',l_error_code);
528 APP_EXCEPTION.RAISE_EXCEPTION;
529
530 END IF;
531
532 END IF;
533 END IF;
534
535
536 --bugfix:4913913 commented out the code
537 /*
538 if (SQL%NOTFOUND) then
539 Raise NO_DATA_FOUND;
540 end if; */
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 IF (SQLCODE <> -20001) THEN
545 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
546 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
547 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
548 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
549 ', INVOICE_ID == ' || TO_CHAR(X_Invoice_Id) ||
550 ', LINE_LOCATION_ID = ' || TO_CHAR(X_Line_Location_Id) ||
551 ', HOLD_LOOKUP_CODE = ' || X_Hold_Lookup_Code);
552 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
553 END IF;
554 APP_EXCEPTION.RAISE_EXCEPTION;
555
556 END Update_Row;
557
558
559 /* */
560 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
561 X_calling_sequence IN VARCHAR2) IS
562
563 current_calling_sequence VARCHAR2(2000);
564 debug_info VARCHAR2(100);
565 l_invoice_id NUMBER;
566
567 BEGIN
568 -- Update the calling sequence
569 --
570 current_calling_sequence := 'AP_HOLDS_PKG.DELETE_ROW<-' ||
571 X_calling_sequence;
572
573 --Bug 4539462 Need the invoice_id
574 Select invoice_id
575 Into l_invoice_id
576 From ap_holds
577 Where rowid = X_Rowid;
578
579 debug_info := 'Delete from AP_HOLDS';
580 DELETE FROM AP_HOLDS
581 WHERE rowid = X_Rowid;
582
583 if (SQL%NOTFOUND) then
584 Raise NO_DATA_FOUND;
585 end if;
586
587 --Bug 4539462 DBI logging
588 AP_DBI_PKG.Maintain_DBI_Summary
589 (p_table_name => 'AP_HOLDS',
590 p_operation => 'D',
591 p_key_value1 => l_invoice_id,
592 p_calling_sequence => current_calling_sequence);
593
594 EXCEPTION
595 WHEN OTHERS THEN
596 IF (SQLCODE <> -20001) THEN
597 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
598 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
599 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
600 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid);
601 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
602 END IF;
603 APP_EXCEPTION.RAISE_EXCEPTION;
604
605 END Delete_Row;
606
607 -----------------------------------------------------------------------
608 -- PROCEDURE insert_single_hold inserts a single record into
609 -- the AP_HOLDS table given a record in ap_invoices.
610 -----------------------------------------------------------------------
611 PROCEDURE insert_single_hold (X_invoice_id IN number,
612 X_hold_lookup_code IN varchar2,
613 X_hold_type IN varchar2 DEFAULT NULL,
614 X_hold_reason IN varchar2 DEFAULT NULL,
615 X_held_by IN number DEFAULT NULL,
616 X_calling_sequence IN varchar2 DEFAULT NULL)
617 IS
618 current_calling_sequence VARCHAR2(2000);
619 debug_info VARCHAR2(100);
620 l_hold_reason ap_holds.hold_reason%TYPE; --bug 1188566
621
622 cursor hold_cursor is
623 select description
624 from ap_hold_codes
625 where hold_type = nvl(X_hold_type,hold_type)
626 and hold_lookup_code = X_hold_lookup_code;
627
628 l_api_name varchar2(50);
629 l_hold_id ap_holds_all.hold_id%type;
630
631 BEGIN
632
633 -- Update the calling sequence
634 --
635 current_calling_sequence :=
636 'AP_HOLDS_PKG.insert_single_hold<-'||
637 X_calling_sequence;
638 l_api_name := 'Insert_Single_Hold';
639
640 -- If no hold_code was passed to the procedure, abort the call
641 if (X_hold_lookup_code is null) then
642 return;
643 end if;
644
645 -- If a hold reason was passed to the function, then we do not
646 -- need to get a description from AP_HOLD_CODES. We don't want
647 -- to override the user-entered description
648
649 if (X_hold_reason is null) then
650 debug_info := 'Select from AP_HOLD_CODES';
651 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
652 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
653 END IF;
654
655 open hold_cursor;
656 fetch hold_cursor into l_hold_reason;
657 close hold_cursor;
658
659 debug_info := 'l_hold_reason is '||l_hold_reason;
660 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
661 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
662 END IF;
663
664 else
665 l_hold_reason := X_hold_reason;
666 end if;
667
668 debug_info := 'Insert into AP_HOLDS';
669 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
670 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
671 END IF;
672
673 --bugfix:5523240
674 SELECT ap_holds_s.nextval
675 INTO l_hold_id
676 FROM DUAL;
677
678 INSERT INTO AP_HOLDS
679 (INVOICE_ID, HOLD_LOOKUP_CODE, HOLD_DATE,
680 CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
681 LAST_UPDATE_DATE, LAST_UPDATED_BY,
682 HELD_BY, HOLD_REASON, ORG_ID, HOLD_ID)
683 SELECT
684 X_invoice_id, X_hold_lookup_code, SYSDATE,
685 FND_GLOBAL.user_id, -- 7299826
686 SYSDATE,
687 FND_GLOBAL.LOGIN_ID, -- 7299826
688 SYSDATE,
689 FND_GLOBAL.user_id, -- 7299826
690 nvl(X_held_by,FND_GLOBAL.user_id), -- 7299826
691 l_hold_reason,
692 ORG_ID,L_HOLD_ID
693 FROM ap_invoices
694 WHERE invoice_id = X_invoice_id
695 AND not exists
696 (SELECT 'Already on this hold'
697 FROM ap_holds
698 WHERE invoice_id = X_invoice_id
699 AND hold_lookup_code = X_hold_lookup_code
700 AND release_lookup_code IS NULL);
701
702 --Bug 4539462 DBI logging
703 AP_DBI_PKG.Maintain_DBI_Summary
704 (p_table_name => 'AP_HOLDS',
705 p_operation => 'I',
706 p_key_value1 => X_invoice_id,
707 p_calling_sequence => current_calling_sequence);
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 IF (SQLCODE <> -20001) THEN
712 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
713 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
714 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
715 current_calling_sequence);
716
717 FND_MESSAGE.SET_TOKEN('PARAMETERS',
718 'X_invoice_id = ' ||X_invoice_id
719 ||', X_hold_lookup_code = '||X_hold_lookup_code
720 ||', X_hold_type = ' ||X_hold_type
721 ||', X_hold_reason = ' ||X_hold_reason
722 ||', X_held_by = ' ||X_held_by
723 );
724
725 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
726 END IF;
727 APP_EXCEPTION.RAISE_EXCEPTION;
728
729 END insert_single_hold;
730
731 -----------------------------------------------------------------------
732 -- PROCEDURE release_single_hold releases a hold by updating a single
733 -- record in AP_HOLDS with a release_lookup_code
734 -----------------------------------------------------------------------
735 PROCEDURE release_single_hold (X_invoice_id IN number,
736 X_hold_lookup_code IN varchar2,
737 X_release_lookup_code IN varchar2,
738 X_held_by IN number DEFAULT NULL,
739 X_calling_sequence IN varchar2 DEFAULT NULL)
740 IS
741 current_calling_sequence VARCHAR2(2000);
742 debug_info VARCHAR2(100);
743 l_hold_reason ap_hold_codes.description%TYPE;
744 l_last_updated_by ap_invoices.last_updated_by%TYPE;
745 l_last_update_login ap_invoices.last_update_login%TYPE;
746
747 cursor invoice_who_cursor is
748 select last_updated_by,
749 last_update_login
750 from ap_invoices
751 where invoice_id = X_invoice_id;
752
753 BEGIN
754
755 -- Update the calling sequence
756 --
757 current_calling_sequence :=
758 'AP_HOLDS_PKG.release_single_hold<-'||
759 X_calling_sequence;
760
761 debug_info := 'Select from AP_INVOICES';
762
763 open invoice_who_cursor;
764 fetch invoice_who_cursor into l_last_updated_by, l_last_update_login;
765 close invoice_who_cursor;
766
767 debug_info := 'Update AP_HOLDS';
768
769 UPDATE ap_holds
770 SET release_lookup_code = X_release_lookup_code,
771 release_reason = (SELECT description
772 FROM ap_lookup_codes
773 WHERE lookup_type = 'HOLD CODE'
774 AND lookup_code = X_release_lookup_code),
775 last_updated_by = FND_GLOBAL.user_id, -- 7299826
776 last_update_date = SYSDATE,
777 last_update_login = FND_GLOBAL.login_id -- 7299826
778 WHERE invoice_id = X_invoice_id
779 AND held_by = nvl(X_held_by,held_by) -- 7299826
780 AND release_lookup_code IS NULL
781 AND hold_lookup_code = X_hold_lookup_code;
782
783 --Bug 4539462 DBI logging
784 AP_DBI_PKG.Maintain_DBI_Summary
785 (p_table_name => 'AP_HOLDS',
786 p_operation => 'U',
787 p_key_value1 => X_invoice_id,
788 p_calling_sequence => current_calling_sequence);
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 IF (SQLCODE <> -20001) THEN
793 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
794 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
795 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
796 current_calling_sequence);
797 FND_MESSAGE.SET_TOKEN('PARAMETERS',
798 'X_invoice_id = ' ||X_invoice_id
799 ||', X_hold_lookup_code = ' ||X_hold_lookup_code
800 ||', X_release_lookup_code= '||X_release_lookup_code
801 ||', X_held_by = ' ||X_held_by
802 );
803 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
804 END IF;
805 APP_EXCEPTION.RAISE_EXCEPTION;
806
807 END release_single_hold;
808
809
810 -----------------------------------------------------------------------
811 -- PROCEDURE quick_release release either ALL holds or one specificated
812 -- hold for each one invoice.
813 -- This procedure is called by Action window from APXINWKB
814 -----------------------------------------------------------------------
815 PROCEDURE quick_release (X_invoice_id IN NUMBER,
816 X_hold_lookup_code IN VARCHAR2,
817 X_release_lookup_code IN VARCHAR2,
818 X_release_reason IN VARCHAR2,
819 X_responsibility_id IN NUMBER,
820 X_last_updated_by IN NUMBER,
821 X_last_update_date IN DATE,
822 X_holds_count IN OUT NOCOPY NUMBER,
823 X_approval_status_lookup_code IN OUT NOCOPY VARCHAR2,
824 X_calling_sequence IN VARCHAR2)
825 IS
826 l_success BOOLEAN := TRUE;
827 l_error_code VARCHAR2(4000);
828 l_tax_hold_codes AP_ETAX_SERVICES_PKG.Rel_Hold_Codes_Type;
829 num BINARY_INTEGER := 1;
830 current_calling_sequence VARCHAR2(2000);
831 debug_info VARCHAR2(100);
832
833 cursor l_invoice_status_cursor is
834 select ap_invoices_pkg.get_holds_count(invoice_id),
835 ap_invoices_pkg.get_approval_status(
836 invoice_id,
837 invoice_amount,
838 payment_status_flag,
839 invoice_type_lookup_code)
840 from ap_invoices
841 where invoice_id = X_invoice_id;
842
843 cursor tax_holds_cursor is
844 select hold_lookup_code
845 from ap_holds
846 where invoice_id = x_invoice_id
847 and hold_lookup_code IN ('TAX AMOUNT RANGE','TAX VARIANCE')
848 and release_lookup_code IS NULL;
849
850 BEGIN
851
852 -- Update the calling sequence
853 --
854 current_calling_sequence :=
855 'AP_HOLDS_PKG.quick_release<-'||
856 X_calling_sequence;
857
858 FOR num IN 1..10 LOOP
859 l_tax_hold_codes(num) := NULL;
860 END LOOP;
861
862 ------------------------------------------------------------------------
863 -- Update final_match_flag to 'N' if try to release 'CANT CLOSE PO' hold
864 ------------------------------------------------------------------------
865 debug_info := 'Update AP_INVOICE_DISTRIBUTIONS';
866
867 UPDATE ap_invoice_distributions D
868 SET final_match_flag = 'N'
869 WHERE D.invoice_id = X_invoice_id
870 AND ((X_hold_lookup_code = 'CANT CLOSE PO') OR
871 ((X_hold_lookup_code = 'ALL')
872 AND EXISTS(SELECT 'X'
873 FROM AP_HOLDS H
874 WHERE H.invoice_id = X_invoice_id
875 AND H.hold_lookup_code = 'CANT CLOSE PO'
876 AND H.release_lookup_code IS NULL)));
877
878 OPEN tax_holds_cursor;
879 LOOP
880 FETCH tax_holds_cursor into l_tax_hold_codes(num);
881 EXIT when tax_holds_cursor%notfound;
882 num := num+1;
883 END LOOP;
884 CLOSE tax_holds_cursor;
885
886 ------------------------------------------------------------------------
887 -- Release single hold if pass hold_lookup_code or all holds if pass 'ALL'
888 -- in hold_lookup_code
889 ------------------------------------------------------------------------
890 debug_info := 'Update AP_HOLDS';
891
892 UPDATE ap_holds H
893 SET H.release_lookup_code = X_release_lookup_code,
894 H.release_reason = X_release_reason,
895 H.responsibility_id = X_responsibility_id,
896 H.last_update_date = X_last_update_date,
897 H.last_updated_by = X_last_updated_by
898 WHERE H.invoice_id = X_invoice_id
899 AND X_hold_lookup_code IN (H.hold_lookup_code, 'ALL')
900 AND H.hold_lookup_code not in ('DIST VARIANCE', 'NO RATE',
901 'CANT FUNDS CHECK', 'INSUFFICIENT FUNDS',
902 'FINAL MATCHING', 'FUTURE PERIOD', 'CANT TRY PO CLOSE',
903 'DIST ACCT INVALID', 'ERV ACCT INVALID', 'LIAB ACCT INVALID')
904 AND H.release_lookup_code is null;
905
906 --Bug 4539462 DBI logging
907 AP_DBI_PKG.Maintain_DBI_Summary
908 (p_table_name => 'AP_HOLDS',
909 p_operation => 'U',
910 p_key_value1 => X_invoice_id,
911 p_calling_sequence => current_calling_sequence);
912
913 ------------------------------------------------------------------------
914 -- Retrieve new invoice statuses
915 ------------------------------------------------------------------------
916 debug_info := 'Retrieving new invoice statuses';
917
918 open l_invoice_status_cursor;
919 fetch l_invoice_status_cursor into
920 X_holds_count,
921 X_approval_status_lookup_code;
922 close l_invoice_status_cursor;
923
924 --ETAX: Invwkb
925 --If the invoice goes to 'APPROVED' status outside the context of 'Invoice validation'
926 --process then we need to update ETAX with the same status, and also if we released
927 --TAX holds , we need to update ETAX of the same so that tax holds are released on
928 --detail tax lines in ETAX repository.
929 IF (x_approval_status_lookup_code IN ('APPROVED','AVAILABLE','FULL','UNPAID')) THEN
930
931 IF(l_tax_hold_codes.COUNT <> 0) THEN
932
933 l_success := ap_etax_services_pkg.release_tax_holds(
934 p_invoice_id => x_invoice_id,
935 p_calling_mode => 'RELEASE TAX HOLDS',
936 p_tax_hold_code => l_tax_hold_codes,
937 p_all_error_messages => 'N',
938 p_error_code => l_error_code,
939 p_calling_sequence => current_calling_sequence);
940
941 IF (not l_success) THEN
942 FND_MESSAGE.SET_NAME('SQLAP','AP_ETX_CANNOT_REL_TAX_HOLDS');
943 FND_MESSAGE.SET_TOKEN('REASON',l_error_code);
944 APP_EXCEPTION.RAISE_EXCEPTION;
945 END IF;
946
947 END IF;
948
949 IF (l_success) THEN
950
951 l_success := ap_etax_pkg.calling_etax(
952 p_invoice_id => x_invoice_id,
953 p_calling_mode => 'FREEZE INVOICE',
954 p_all_error_messages => 'N',
955 p_error_code => l_error_code,
956 p_calling_sequence => current_calling_sequence);
957
958 IF (not l_success) THEN
959
960 FND_MESSAGE.SET_NAME('SQLAP','AP_ETX_CANNOT_FRZ_INV');
961 FND_MESSAGE.SET_TOKEN('REASON',l_error_code);
962 APP_EXCEPTION.RAISE_EXCEPTION;
963
964 END IF;
965
966 END IF;
967
968 END IF; /* x_approval_status_ IN ... */
969
970 ------------------------------------------------------------------------
971 -- Commit changes to database
972 ------------------------------------------------------------------------
973 debug_info := 'Commit changes';
974
975 COMMIT;
976
977 EXCEPTION
978 WHEN OTHERS THEN
979 IF (SQLCODE <> -20001) THEN
980 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
981 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
982 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
983 current_calling_sequence);
984 FND_MESSAGE.SET_TOKEN('PARAMETERS',
985 'X_invoice_id = ' ||TO_CHAR(X_invoice_id)
986 ||', X_hold_lookup_code = ' ||X_hold_lookup_code
987 ||', X_release_lookup_code= '||X_release_lookup_code
988 ||', X_release_reason= '||X_release_reason
989 ||', X_responsibility_id= '||TO_CHAR(X_responsibility_id)
990 ||', X_last_updated_by= '||TO_CHAR(X_last_updated_by)
991 ||', X_last_update_date= '||TO_CHAR(X_last_update_date)
992 ||', X_holds_count= '||TO_CHAR(X_holds_count)
993 ||', X_approval_status_lookup_code= '||
994 X_approval_status_lookup_code
995 );
996 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
997 END IF;
998 APP_EXCEPTION.RAISE_EXCEPTION;
999
1000 END quick_release;
1001
1002
1003
1004
1005
1006
1007
1008
1009 END AP_HOLDS_PKG;
1010