[Home] [Help]
PACKAGE BODY: APPS.IGI_EXP_HOLDS
Source
1 PACKAGE BODY igi_exp_holds AS
2 -- $Header: igiexprb.pls 120.16.12000000.1 2007/09/13 04:24:26 mbremkum ship $
3
4 /* ============== FND LOG VARIABLES ================== */
5 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
6 l_state_level number := FND_LOG.LEVEL_STATEMENT ;
7 l_proc_level number := FND_LOG.LEVEL_PROCEDURE ;
8 l_event_level number := FND_LOG.LEVEL_EVENT ;
9 l_excep_level number := FND_LOG.LEVEL_EXCEPTION ;
10 l_error_level number := FND_LOG.LEVEL_ERROR ;
11 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED ;
12
13 /* =================== DEBUG_LOG_UNEXP_ERROR =================== */
14 Procedure Debug_log_unexp_error (P_module IN VARCHAR2,
15 P_error_type IN VARCHAR2)
16 IS
17
18 BEGIN
19
20 IF (l_unexp_level >= l_debug_level) THEN
21
22 IF (P_error_type = 'DEFAULT') THEN
23 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
24 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
25 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
26 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igiexprb.igi_exp_holds.' || P_module ,TRUE);
27 ELSIF (P_error_type = 'USER') THEN
28 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igiexprb.igi_exp_holds.' || P_module ,TRUE);
29 END IF;
30
31 END IF;
32
33 END Debug_log_unexp_error;
34
35 /* =================== DEBUG_LOG_STRING =================== */
36 Procedure Debug_log_string (P_level IN NUMBER,
37 P_module IN VARCHAR2,
38 P_Message IN VARCHAR2)
39 IS
40
41 BEGIN
42
43 IF (P_level >= l_debug_level) THEN
44 FND_LOG.STRING(P_level, 'igi.plsql.igiexprb.igi_exp_holds.' || P_module, P_message) ;
45 END IF;
46
47 END Debug_log_string;
48
49 --============================================================================
50 -- SET_HOLD: Puts an EXP hold on the invoice
51 --============================================================================
52 PROCEDURE Set_Hold(p_invoice_id IN NUMBER,
53 p_calling_sequence IN OUT NOCOPY VARCHAR2)
54 IS
55 -- Bug No:2517124
56 -- using ap_lookup_codes table to get the hold reason
57 CURSOR c_get_hold_reason
58 IS
59 select displayed_field from ap_lookup_codes
60 where lookup_type = 'HOLD CODE'
61 and lookup_code = 'AWAIT EXP APP';
62
63 l_debug_loc VARCHAR2(30);
64 l_debug_info VARCHAR2(250);
65 -- Bug No:2517124
66 l_get_hold_reason c_get_hold_reason%rowtype;
67
68 BEGIN
69
70 -- =============== START DEBUG LOG ================
71 Debug_log_string (l_proc_level, 'Set_hold.Msg1',
72 ' ** BEGIN SET_HOLD ** ');
73 -- =============== END DEBUG LOG ==================
74
75 -- GSCC File.sql.35
76 l_debug_loc := 'Set_Hold';
77
78 -- Update the calling sequence
79 p_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'|| p_calling_sequence;
80
81 -- =============== START DEBUG LOG ================
82 Debug_log_string (l_proc_level, 'Set_hold.Msg2',
83 ' p_Calling_Sequence --> ' || p_calling_sequence);
84 -- =============== END DEBUG LOG ==================
85
86 -- Bug No:2517124
87 Open c_get_hold_reason;
88 fetch c_get_hold_reason into l_get_hold_reason;
89 close c_get_hold_reason;
90
91 -- =============== START DEBUG LOG ================
92 Debug_log_string (l_proc_level, 'Set_hold.Msg3',
93 ' l_get_hold_reason --> ' || l_get_hold_reason.displayed_field);
94 -- =============== END DEBUG LOG ==================
95
96 -- Bug#5905190 : Add Hold Id, org_id column while inserting
97 INSERT INTO ap_holds
98 (invoice_id,
99 hold_lookup_code,
100 last_update_date,
101 last_updated_by,
102 creation_date,
103 created_by,
104 held_by,
105 hold_date,
106 hold_reason,
107 status_flag,
108 hold_id,
109 org_id)
110 SELECT p_invoice_id,
111 'AWAIT EXP APP',
112 SYSDATE,
113 5,
114 SYSDATE,
115 5,
116 5,
117 SYSDATE,
118 -- Bug No:2517124
119 l_get_hold_reason.displayed_field, --'Exchange Protocol Hold',
120 'S',
121 AP_HOLDS_S.NEXTVAL,
122 mo_global.get_current_org_id()
123 FROM SYS.DUAL
124 WHERE NOT EXISTS(SELECT 'x'
125 FROM ap_holds_all ah2
126 WHERE ah2.invoice_id = p_invoice_id
127 AND ah2.hold_lookup_code = 'AWAIT EXP APP'
128 AND (NVL(ah2.release_lookup_code, 'NULL') <> 'HOLDS QUICK RELEASED'
129 AND NVL(ah2.release_lookup_code, 'NULL') <> 'EXP HOLD RELEASE'));
130
131 -- =============== START DEBUG LOG ================
132 Debug_log_string (l_proc_level, 'Set_hold.Msg4',
133 ' INSERT INTO ap_holds --> ' || SQL%ROWCOUNT);
134 Debug_log_string (l_proc_level, 'Set_hold.Msg1',
135 ' ** END SET_HOLD ** ');
136 -- =============== END DEBUG LOG ==================
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 IF (SQLCODE <> -20001) THEN
141 -- =============== START DEBUG LOG ================
142 DEBUG_LOG_UNEXP_ERROR ('Set_hold.unexp1','DEFAULT');
143 -- =============== END DEBUG LOG ==================
144 END IF;
145 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
146 END Set_Hold;
147
148 --============================================================================
149 -- RELEASE_HOLD: Procedure to release a hold from an invoice
150 --============================================================================
151
152 PROCEDURE Release_Hold(p_invoice_id IN NUMBER,
153 p_hold_lookup_code IN VARCHAR2,
154 p_calling_sequence IN OUT NOCOPY VARCHAR2)
155 IS
156 l_release_lookup_code VARCHAR2(30);
157 l_debug_loc VARCHAR2(30);
158 l_debug_info VARCHAR2(250);
159 BEGIN
160
161 -- =============== START DEBUG LOG ================
162 Debug_log_string (l_proc_level, 'Release_hold.Msg1',
163 ' ** START RELEASE_HOLD ** ');
164 -- =============== END DEBUG LOG ==================
165
166 --Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
167 l_release_lookup_code := 'EXP HOLD RELEASE';
168 l_debug_loc :='Release_Hold';
169
170 -- Update the calling sequence
171 p_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
172 p_calling_sequence;
173
174 -- =============== START DEBUG LOG ================
175 Debug_log_string (l_proc_level, 'Release_hold.Msg2',
176 ' p_calling_sequence --> ' || p_calling_sequence);
177 -- =============== END DEBUG LOG ==================
178
179 -- Bug No: 2517124 sowsubra changed the statement to select the
180 -- displayed_field column instead of the description column.
181 UPDATE ap_holds_all
182 SET release_lookup_code = l_release_lookup_code,
183 release_reason = (SELECT displayed_field
184 FROM ap_lookup_codes
185 WHERE lookup_code = l_release_lookup_code
186 AND lookup_type = 'HOLD CODE'),
187 last_update_date = SYSDATE,
188 last_updated_by = 5,
189 status_flag = 'R'
190 WHERE invoice_id = p_invoice_id
191 AND hold_lookup_code = p_hold_lookup_code;
192
193 -- =============== START DEBUG LOG ================
194 Debug_log_string (l_proc_level, 'Release_hold.Msg3',
195 ' UPDATE ap_holds_all --> ' || SQL%ROWCOUNT);
196 Debug_log_string (l_proc_level, 'Set_hold.Msg4',
197 ' ** END RELEASE_HOLD ** ');
198 -- =============== END DEBUG LOG ==================
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 IF (SQLCODE <> -20001) THEN
203 -- =============== START DEBUG LOG ================
204 DEBUG_LOG_UNEXP_ERROR ('Release_hold.unexp1','DEFAULT');
205 -- =============== END DEBUG LOG ==================
206 END IF;
207 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
208 END Release_Hold;
209
210 --===================================================================
211 -- GET_HOLD_STATUS: Gets the status of the hold as -
212 -- ALREADY ON HOLD, RELEASED BY USER or NOT ON HOLD.
213 --===================================================================
214
215 PROCEDURE Get_Hold_Status(p_invoice_id IN NUMBER,
216 p_hold_lookup_code IN VARCHAR2,
217 p_status IN OUT NOCOPY VARCHAR2,
218 p_calling_sequence IN OUT NOCOPY VARCHAR2)
219 IS
220 l_debug_loc VARCHAR2(30);
221 l_debug_info VARCHAR2(250);
222
223 CURSOR c_hold_status IS
224 SELECT DECODE(release_lookup_code,
225 NULL, 'ALREADY ON HOLD',
226 'RELEASED BY USER')
227 FROM ap_holds_all
228 WHERE invoice_id = p_invoice_id
229 AND hold_lookup_code = p_hold_lookup_code
230 AND release_lookup_code IS NULL;
231 BEGIN
232 -- =============== START DEBUG LOG ================
233 Debug_log_string (l_proc_level, 'Get_hold_status.Msg1',
234 ' ** START GET_HOLD_STATUS ** ');
235 -- =============== END DEBUG LOG ==================
236
237 --Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
238 l_debug_loc :='Get_Hold_Status';
239 -- Initialize to NOT ON HOLD in case the CURSOR retrieves no records.
240 p_status := 'NOT ON HOLD';
241 -- Update the calling sequence
242 p_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
243 p_calling_sequence;
244
245 -- =============== START DEBUG LOG ================
246 Debug_log_string (l_proc_level, 'Get_hold_status.Msg2',
247 ' p_calling_sequence --> ' || p_calling_sequence);
248 -- =============== END DEBUG LOG ==================
249
250 OPEN c_hold_status;
251 FETCH c_hold_status INTO p_status;
252 CLOSE c_hold_status;
253
254 -- =============== START DEBUG LOG ================
255 Debug_log_string (l_proc_level, 'Get_hold_status.Msg3',
256 ' p_status --> ' || p_status);
257 Debug_log_string (l_proc_level, 'Get_hold_status.Msg4',
258 ' ** END GET_HOLD_STATUS ** ');
259 -- =============== END DEBUG LOG ==================
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 IF (SQLCODE <> -20001) THEN
264 -- =============== START DEBUG LOG ================
265 DEBUG_LOG_UNEXP_ERROR ('Get_hold_status.unexp1','DEFAULT');
266 -- =============== END DEBUG LOG ==================
267 END IF;
268 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
269 END Get_Hold_Status;
270
271 --=====================================================================
272 -- Get_Approval_Status: Gets the invoice level approval status as -
273 -- 'APPROVED','NEEDS REAPPROVAL','NEVER APPROVED',
274 -- 'CANCELLED'
275 --====================================================================
276
277 FUNCTION Get_Approval_Status(p_invoice_id IN NUMBER,
278 p_calling_sequence IN OUT NOCOPY VARCHAR2)
279 RETURN VARCHAR2
280 IS
281 l_invoice_approval_status VARCHAR2(25);
282 l_invoice_approval_flag VARCHAR2(1);
283 l_distribution_approval_flag VARCHAR2(1);
284 l_encumbrance_flag VARCHAR2(1);
285 l_invoice_holds NUMBER;
286 l_cancelled_date DATE;
287 l_debug_loc VARCHAR2(30);
288 l_debug_info VARCHAR2(250) ;
289
290 CURSOR c_dist_approval_status
291 IS
292 SELECT match_status_flag
293 FROM ap_invoice_distributions_all
294 WHERE invoice_id = p_invoice_id
295 UNION
296 SELECT 'N'
297 FROM ap_invoice_distributions_all
298 WHERE invoice_id = p_invoice_id
299 AND match_status_flag IS NULL
300 AND EXISTS
301 (SELECT 'There are both untested and tested lines'
302 FROM ap_invoice_distributions_all
303 WHERE invoice_id = p_invoice_id
304 AND match_status_flag IN ('T','A'));
305
306
307 BEGIN
308
309 -- =============== START DEBUG LOG ================
310 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg1',
311 ' ** START GET_APPROVAL_STATUS ** ');
312 -- =============== END DEBUG LOG ==================
313
314 -- Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
315 l_debug_loc := 'get_approval_status';
316 p_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
317 p_calling_sequence;
318
319 -- =============== START DEBUG LOG ================
320 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg2',
321 ' p_calling_sequence --> ' || p_calling_sequence);
322 -- =============== END DEBUG LOG ==================
323
324 -- Get the encumbrance flag
325 SELECT NVL(purch_encumbrance_flag,'N')
326 INTO l_encumbrance_flag
327 FROM financials_system_parameters;
328
329 -- =============== START DEBUG LOG ================
330 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg3',
331 ' l_encumbrance_flag --> ' || l_encumbrance_flag);
332 -- =============== END DEBUG LOG ==================
333
334 -- Get the number of unreleased holds for the invoice
335 SELECT COUNT(*)
336 INTO l_invoice_holds
337 FROM ap_holds_all
338 WHERE invoice_id = p_invoice_id
339 AND release_lookup_code IS NULL;
340
341 -- =============== START DEBUG LOG ================
342 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg4',
343 ' l_invoice_holds --> ' || l_invoice_holds);
344 -- =============== END DEBUG LOG ==================
345
346 --
347 -- Establish the invoice-level approval flag
348 --
349 -- Use the following ordering sequence to determine the invoice-level
350 -- approval flag:
351 -- 'N' - Needs Reapproval
352 -- 'T' - Tested
353 -- 'A' - Approved
354 -- '' - Never Approved
355 --
356 -- Initialize invoice-level approval flag
357 --
358
359 l_invoice_approval_flag := '';
360
361 -- =============== START DEBUG LOG ================
362 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg5',
363 ' Setting l_invoice_approval_flag to null' );
364 -- =============== END DEBUG LOG ==================
365
366 OPEN c_dist_approval_status;
367 LOOP
368
369 FETCH c_dist_approval_status INTO l_distribution_approval_flag;
370
371 -- =============== START DEBUG LOG ================
372 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg6',
373 ' l_distribution_approval_flag -->' || l_distribution_approval_flag );
374 -- =============== END DEBUG LOG ==================
375
376 EXIT WHEN c_dist_approval_status%NOTFOUND;
377
378 IF (l_distribution_approval_flag = 'N')
379 THEN
380 l_invoice_approval_flag := 'N';
381
382 ELSIF (l_distribution_approval_flag = 'T' AND
383 (l_invoice_approval_flag <> 'N' OR l_invoice_approval_flag IS NULL))
384 THEN
385 l_invoice_approval_flag := 'T';
386
387 ELSIF (l_distribution_approval_flag = 'A' AND
388 (l_invoice_approval_flag NOT IN ('N','T') OR l_invoice_approval_flag IS NULL))
389 THEN
390
391 l_invoice_approval_flag := 'A';
392 -- BUG 3142049: Adding If condition to handle the scenario
393 -- when value of l_distribution_approval_flag is 'S'
394 ELSIF (l_distribution_approval_flag = 'S')
395 THEN
396 l_invoice_approval_flag := 'S';
397 END IF;
398
399 -- =============== START DEBUG LOG ================
400 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg7',
401 ' l_invoice_approval_flag -->' || l_invoice_approval_flag );
402 -- =============== END DEBUG LOG ==================
403 END LOOP;
404 CLOSE c_dist_approval_status;
405
406 -- =============== START DEBUG LOG ================
407 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg8',
408 ' end of loop c_dist_approval_status' );
409 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg9',
410 ' l_encumbrance_flag --> ' || l_encumbrance_flag );
411 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg10',
412 ' l_invoice_approval_flag --> ' || l_invoice_approval_flag );
413 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg11',
414 ' l_invoice_holds --> ' || l_invoice_holds );
415 -- =============== END DEBUG LOG ==================
416
417 -- Derive the translated approval status from the approval flag
418 IF (l_encumbrance_flag = 'Y') THEN
419 IF (l_invoice_approval_flag = 'A' AND l_invoice_holds = 0) THEN
420 l_invoice_approval_status := 'APPROVED';
421 ELSIF ((NVL(l_invoice_approval_flag,'A') = 'A' AND l_invoice_holds > 0)
422 OR (l_invoice_approval_flag IN ('T','N','S'))) THEN
423 l_invoice_approval_status := 'NEEDS REAPPROVAL';
424 ELSIF (l_invoice_approval_flag IS NULL) THEN
425 l_invoice_approval_status := 'NEVER APPROVED';
426 END IF;
427
428 ELSIF (l_encumbrance_flag = 'N') THEN
429 IF (l_invoice_approval_flag IN ('A','T') AND l_invoice_holds = 0) THEN
430 l_invoice_approval_status := 'APPROVED';
431 ELSIF ((nvl(l_invoice_approval_flag,'A') IN ('A','T') AND
432 l_invoice_holds > 0) OR (l_invoice_approval_flag = 'N')) THEN
433 l_invoice_approval_status := 'NEEDS REAPPROVAL';
434 ELSIF (l_invoice_approval_flag IS NULL) THEN
435 l_invoice_approval_status := 'NEVER APPROVED';
436 ELSIF (l_invoice_approval_flag IS NULL AND l_invoice_holds > 0 ) THEN
437 l_invoice_approval_status := 'NEEDS REAPPROVAL';
438 END IF;
439
440 END IF;
441
442 -- =============== START DEBUG LOG ================
443 Debug_log_string (l_proc_level, 'Get_Approval_status.Msg12',
444 ' RETURN l_invoice_approval_status --> ' || l_invoice_approval_status );
445 -- =============== END DEBUG LOG ==================
446 RETURN(l_invoice_approval_status);
447
448 EXCEPTION
449 WHEN OTHERS THEN
450 IF (SQLCODE <> -20001) THEN
451 -- =============== START DEBUG LOG ================
452 DEBUG_LOG_UNEXP_ERROR ('Get_approval_status.unexp1','DEFAULT');
453 -- =============== END DEBUG LOG ==================
454 END IF;
455 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
456
457 END get_approval_status;
458
459 --============================================================================
460 -- INVOICE_NOT_EXCLUDED: Determine if the source of the invoice excludes it
461 -- from EXP.
462 --============================================================================
463 FUNCTION Invoice_Not_Excluded( p_invoice_id NUMBER
464 , p_source VARCHAR2
465 , p_calling_sequence VARCHAR2)
466 RETURN BOOLEAN
467 IS
468 -- For the following CURSOR the ap_invoice_distributions table is used
469 -- instead of the ap_invoices table to avoid a mutating table problem.
470 -- This occurs when this package which is called from the trigger
471 -- igi_exp_hold_trx on the ap_invoices table queries the ap_invoices
472 -- table. to avoid this use the ap_invoices_distribution table.
473 -- also true for igi_exp_hold_t1 asmales
474
475 -- bug 2885976
476 CURSOR c_check_hold_exclusions ( pv_invoice_id NUMBER
477 , pv_source VARCHAR2) IS
478 select 1
479 from fnd_flex_values_vl ffv
480 , fnd_flex_value_sets ffvs
481 where ffv.flex_value = pv_source
482 and ffvs.flex_value_set_name ='IGI_EXP_SOURCE_EXCLUSION'
483 and ffvs.flex_value_set_id = ffv.flex_value_set_id
484 and ffv.enabled_flag = 'Y'
485 and SYSDATE BETWEEN NVL(ffv.start_date_active, SYSDATE)
486 and NVL(ffv.end_date_active, SYSDATE);
487
488 l_debug_loc VARCHAR2(30);
489 l_debug_info VARCHAR2(250) ;
490 l_curr_calling_sequence VARCHAR2(2000);
491 l_dummy NUMBER;
492
493 BEGIN
494
495 -- =============== START DEBUG LOG ================
496 Debug_log_string (l_proc_level, 'Invoice_not_included.Msg1',
497 ' ** START INVOCIE_NOT_INCLUDED ** ');
498 -- =============== END DEBUG LOG ==================
499
500 -- GSCC Standard - File.sql.35
501 l_debug_loc := 'invoice_not_excluded';
502 -- Update the calling sequence
503 l_curr_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
504 p_calling_sequence;
505
506 -- =============== START DEBUG LOG ================
507 Debug_log_string (l_proc_level, 'Invoice_not_included.Msg2',
508 ' l_curr_calling_sequence --> ' || l_curr_calling_sequence);
509 Debug_log_string (l_proc_level, 'Invoice_not_included.Msg3',
510 ' p_invoice_id --> ' || p_invoice_id);
511 Debug_log_string (l_proc_level, 'Invoice_not_included.Msg4',
512 ' p_source --> ' || p_source);
513
514 -- =============== END DEBUG LOG ==================
515
516 OPEN c_check_hold_exclusions ( p_invoice_id, p_source ) ;
517 FETCH c_check_hold_exclusions INTO l_dummy ;
518
519 IF c_check_hold_exclusions%NOTFOUND THEN
520 -- =============== START DEBUG LOG ================
521 Debug_log_string (l_proc_level, 'Invoice_not_included.Msg5',
522 ' RETURN TRUE --> ' || SQL%ROWCOUNT);
523 -- =============== END DEBUG LOG ==================
524 CLOSE c_check_hold_exclusions ;
525 RETURN TRUE ;
526 ELSE
527 -- =============== START DEBUG LOG ================
528 Debug_log_string (l_proc_level, 'Invoice_not_included.Msg6',
529 ' RETURN FALSE --> ' || SQL%ROWCOUNT);
530 -- =============== END DEBUG LOG ==================
531 CLOSE c_check_hold_exclusions ;
532 RETURN FALSE ;
533 END IF ;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 IF c_check_hold_exclusions%ISOPEN THEN
538 CLOSE c_check_hold_exclusions ;
539 END IF ;
540
541 IF (SQLCODE <> -20001) THEN
542 -- =============== START DEBUG LOG ================
543 DEBUG_LOG_UNEXP_ERROR ('Invoice_not_included.unexp1','DEFAULT');
544 -- =============== END DEBUG LOG ==================
545 END IF;
546 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
547 END Invoice_Not_Excluded ;
548
549 --========================================================================
550 -- PROCEDURE: Igi_Exp_Ap_Holds_T2
551 -- Called from Trigger IGI_EXP_AP_HOLDS_T2
552 --========================================================================
553
554 PROCEDURE Igi_Exp_Ap_Holds_T2(p_calling_sequence IN VARCHAR2)
555 IS
556 l_debug_loc VARCHAR2(30);
557 l_debug_info VARCHAR2(250);
558 l_invoice_id NUMBER;
559 l_source VARCHAR2(25);
560 l_cancelled_date DATE;
561 l_hold_lookup_code VARCHAR2(200);
562 l_calling_sequence VARCHAR2(1000);
563 l_temp_cancelled_amount NUMBER;
564 l_exp_hold_released VARCHAR2(1);
565
566 CURSOR c_exp_hold_released(p_invoice_id NUMBER)
567 IS
568 SELECT 'x'
569 FROM ap_holds_all ah
570 WHERE ah.invoice_id = p_invoice_id
571 AND ah.hold_lookup_code = 'AWAIT EXP APP'
572 AND ah.release_lookup_code = 'EXP HOLD RELEASE'
573 AND NOT EXISTS(SELECT 'x'
574 FROM ap_holds_all ah2
575 WHERE ah2.invoice_id = p_invoice_id
576 AND ah.hold_lookup_code = 'AWAIT EXP APP'
577 AND ah2.release_lookup_code IS NULL);
578
579
580 BEGIN
581 -- =============== START DEBUG LOG ================
582 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg1',
583 ' ** START IGI_EXP_AP_HOLDS_T2 ** ');
584 -- =============== END DEBUG LOG ==================
585
586 --Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
587 l_debug_loc := 'IGI_EXP_AP_HOLDS_T2';
588 l_calling_sequence := 'AWAIT EXP APP';
589 -- Bug 5905190 Start - Variable not initialised
590 l_hold_lookup_code := 'AWAIT EXP APP';
591 -- Bug 5905190 End
592 -- Update the calling sequence --
593 l_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
594 p_calling_sequence;
595
596 -- =============== START DEBUG LOG ================
597 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg2',
598 ' l_calling_sequence --> ' || l_calling_sequence);
599 -- =============== END DEBUG LOG ==================
600
601 FOR i IN 1 .. igi_exp_holds.l_TableRow
602 LOOP
603
604 l_invoice_id := igi_exp_holds.l_InvoiceidTable(i);
605
606 -- =============== START DEBUG LOG ================
607 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg3',
608 ' l_invoice_id --> ' || l_invoice_id);
609 -- =============== END DEBUG LOG ==================
610
611 OPEN c_exp_hold_released(l_invoice_id);
612 FETCH c_exp_hold_released INTO l_exp_hold_released;
613 IF c_exp_hold_released%NOTFOUND THEN
614
615
616 SELECT a.source,
617 a.cancelled_date,
618 a.temp_cancelled_amount
619 INTO l_source,
620 l_cancelled_date,
621 l_temp_cancelled_amount
622 FROM AP_INVOICES_ALL a
623 WHERE a.invoice_id = l_invoice_id;
624
625 -- =============== START DEBUG LOG ================
626 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg4',
627 ' l_source --> ' || l_source);
628 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg5',
629 ' l_cancelled_date --> ' || l_cancelled_date);
630 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg6',
631 ' l_temp_cancelled_amount --> ' || l_temp_cancelled_amount);
632 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg7',
633 ' Calling igi_exp_holds.Place_Release_Hold ');
634 -- =============== END DEBUG LOG ==================
635
636 igi_exp_holds.Place_Release_Hold(l_invoice_id,
637 -- Bug 2469158
638 '', -- invoice amount
639 l_source,
640 l_cancelled_date,
641 'P',
642 l_hold_lookup_code,
643 l_calling_sequence,
644 l_temp_cancelled_Amount);
645
646 -- =============== START DEBUG LOG ================
647 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg8',
648 ' out of igi_exp_holds.Place_Release_Hold');
649 -- =============== END DEBUG LOG ==================
650
651 END IF;
652 CLOSE c_exp_hold_released;
653 END LOOP;
654
655 -- =============== START DEBUG LOG ================
656 Debug_log_string (l_proc_level, 'Igi_exp_ap_holds_t2.Msg9',
657 ' ** END IGI_EXP_AP_HOLDS_T2 ** ');
658 -- =============== END DEBUG LOG ==================
659
660 EXCEPTION
661 WHEN OTHERS THEN
662 IF (SQLCODE <> -20001) THEN
663 -- =============== START DEBUG LOG ================
664 DEBUG_LOG_UNEXP_ERROR ('Invoice_not_included.unexp1','DEFAULT');
665 -- =============== END DEBUG LOG ==================
666 END IF;
667 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
668 END Igi_Exp_Ap_Holds_T2;
669
670 -- Bug 2438858 Start
671 --========================================================================
672 -- PROCEDURE: Igi_Exp_Ap_Inv_Dist_T2
673 -- Called from Trigger IGI_AP_Invoice_Dist_T2
674 --========================================================================
675 PROCEDURE Igi_Exp_Ap_Inv_Dist_T2(p_calling_sequence IN VARCHAR2)
676 IS
677 l_debug_loc VARCHAR2(30);
678 l_debug_info VARCHAR2(250);
679 l_invoice_id NUMBER;
680 l_source VARCHAR2(25);
681 l_cancelled_date DATE;
682 l_hold_lookup_code VARCHAR2(200) := 'AWAIT EXP APP';
683 l_calling_sequence VARCHAR2(1000);
684 l_temp_cancelled_amount NUMBER;
685
686 BEGIN
687
688 -- =============== START DEBUG LOG ================
689 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg1',
690 ' ** START IGI_EXP_AP_INV_DIST_T2 ** ');
691 -- =============== END DEBUG LOG ==================
692
693 -- Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
694 l_debug_loc := 'IGI_EXP_AP_INV_DIST_T2';
695 l_calling_sequence := 'AWAIT EXP APP';
696
697 -- Update the calling sequence --
698 l_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
699 p_calling_sequence;
700
701 -- =============== START DEBUG LOG ================
702 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg2',
703 ' l_calling_sequence --> ' || l_calling_sequence);
704 -- =============== END DEBUG LOG ==================
705
706 FOR i IN 1 .. igi_exp_holds.l_DistTableRow LOOP
707
708 l_invoice_id := igi_exp_holds.l_InvoiceidDistTable(i);
709
710 -- =============== START DEBUG LOG ================
711 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg3',
712 ' l_invoice_id --> ' || l_invoice_id );
713 -- =============== END DEBUG LOG ==================
714
715 SELECT a.source,
716 a.cancelled_date,
717 a.temp_cancelled_Amount
718 INTO l_source,
719 l_cancelled_date,
720 l_temp_cancelled_amount
721 FROM AP_INVOICES_ALL a
722 WHERE a.invoice_id = l_invoice_id;
723
724 -- =============== START DEBUG LOG ================
725 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg4',
726 ' l_source --> ' || l_source );
727 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg5',
728 ' l_cancelled_date --> ' || l_cancelled_date );
729 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg6',
730 ' l_temp_cancelled_amount --> ' || l_temp_cancelled_amount );
731 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg7',
732 ' Calling igi_exp_holds.Place_Release_Hold ' );
733 -- =============== END DEBUG LOG ==================
734
735 igi_exp_holds.Place_Release_Hold(l_invoice_id,
736 -- Bug 2469158
737 '', -- invoice amount
738 l_source,
739 l_cancelled_date,
740 'P',
741 l_hold_lookup_code,
742 l_calling_sequence,
743 l_temp_cancelled_amount
744 );
745
746 -- =============== START DEBUG LOG ================
747 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg8',
748 ' Out of igi_exp_holds.Place_Release_Hold ' );
749 -- =============== END DEBUG LOG ==================
750 END LOOP;
751
752 -- =============== START DEBUG LOG ================
753 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Dist_T2.Msg9',
754 ' ** END IGI_EXP_AP_INV_DIST_T2 ** ');
755 -- =============== END DEBUG LOG ==================
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 IF (SQLCODE <> -20001) THEN
760 -- =============== START DEBUG LOG ================
761 DEBUG_LOG_UNEXP_ERROR ('Igi_Exp_Ap_Inv_Dist_T2.unexp1','DEFAULT');
762 -- =============== END DEBUG LOG ==================
763 END IF;
764 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
765 END Igi_Exp_Ap_Inv_Dist_T2;
766
767
768
769 -- Bug 5905190 Start
770 --========================================================================
771 -- PROCEDURE: Igi_Exp_Ap_Inv_Line_T2
772 -- Called from Trigger IGI_AP_Invoice_Line_T2
773 --========================================================================
774 PROCEDURE Igi_Exp_Ap_Inv_Line_T2(p_calling_sequence IN VARCHAR2)
775 IS
776 l_debug_loc VARCHAR2(30);
777 l_debug_info VARCHAR2(250);
778 l_invoice_id NUMBER;
779 l_source VARCHAR2(25);
780 l_cancelled_date DATE;
781 l_hold_lookup_code VARCHAR2(200) := 'AWAIT EXP APP';
782 l_calling_sequence VARCHAR2(1000);
783 l_temp_cancelled_amount NUMBER;
784
785 BEGIN
786
787 -- =============== START DEBUG LOG ================
788 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg1',
789 ' ** START IGI_EXP_AP_INV_LINE_T2 ** ');
790 -- =============== END DEBUG LOG ==================
791 -- Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
792 l_debug_loc := 'IGI_EXP_AP_INV_LINE_T2';
793 l_calling_sequence := 'AWAIT EXP APP';
794
795 -- Update the calling sequence --
796 l_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
797 p_calling_sequence;
798
799 -- =============== START DEBUG LOG ================
800 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg2',
801 ' l_calling_sequence --> ' || l_calling_sequence);
802 -- =============== END DEBUG LOG ==================
803
804 FOR i IN 1 .. igi_exp_holds.l_LineTableRow LOOP
805
806 l_invoice_id := igi_exp_holds.l_InvoiceidLineTable(i);
807
808 -- =============== START DEBUG LOG ================
809 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg3',
810 ' l_invoice_id --> ' || l_invoice_id );
811 -- =============== END DEBUG LOG ==================
812
813 SELECT a.source,
814 a.cancelled_date,
815 a.temp_cancelled_Amount
816 INTO l_source,
817 l_cancelled_date,
818 l_temp_cancelled_amount
819 FROM AP_INVOICES_ALL a
820 WHERE a.invoice_id = l_invoice_id;
821
822 -- =============== START DEBUG LOG ================
823 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg4',
824 ' l_source --> ' || l_source );
825 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg5',
826 ' l_cancelled_date --> ' || l_cancelled_date );
827 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg6',
828 ' l_temp_cancelled_amount --> ' || l_temp_cancelled_amount );
829 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg7',
830 ' Calling igi_exp_holds.Place_Release_Hold ' );
831 -- =============== END DEBUG LOG ==================
832
833 igi_exp_holds.Place_Release_Hold(l_invoice_id,
834 -- Bug 2469158
835 '', -- invoice amount
836 l_source,
837 l_cancelled_date,
838 'P',
839 l_hold_lookup_code,
840 l_calling_sequence,
841 l_temp_cancelled_amount
842 );
843
844 -- =============== START DEBUG LOG ================
845 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg8',
846 ' Out of igi_exp_holds.Place_Release_Hold ' );
847 -- =============== END DEBUG LOG ==================
848 END LOOP;
849
850 -- =============== START DEBUG LOG ================
851 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg9',
852 ' ** END IGI_EXP_AP_INV_LINE_T2 ** ');
853 -- =============== END DEBUG LOG ==================
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 IF (SQLCODE <> -20001) THEN
858 -- =============== START DEBUG LOG ================
859 DEBUG_LOG_UNEXP_ERROR ('Igi_Exp_Ap_Inv_Line_T2.unexp1','DEFAULT');
860 -- =============== END DEBUG LOG ==================
861 END IF;
862 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
863 END Igi_Exp_Ap_Inv_Line_T2;
864 -- Bug 5905190 End
865
866 --============================================================================
867 -- PLACE_RELEASE_HOLD: Procedure that places or releases an EXP Hold if the
868 -- invoice status is APPROVED
869 --============================================================================
870 PROCEDURE Place_Release_Hold( p_invoice_id IN NUMBER
871 -- Bug 2469158.
872 , p_invoice_amt IN NUMBER
873 , p_source IN VARCHAR2
874 , p_cancelled_date IN DATE
875 , p_place_release IN VARCHAR2
876 , p_hold_lookup_code IN VARCHAR2
877 , p_calling_sequence IN VARCHAR2
878 -- Bug 3595853.
879 , p_temp_cancelled_amount IN NUMBER default NULL
880 )
881 IS
882 l_approval_status VARCHAR2(80) ;
883 l_system_user NUMBER;
884 l_debug_loc VARCHAR2(30);
885 l_debug_info VARCHAR2(250) ;
886 l_inv_hold_status VARCHAR2(240);
887 l_status VARCHAR2(20);
888 l_existing_hold_reason VARCHAR2(240);
889 l_calling_sequence VARCHAR2(1000);
890 -- Bug 2377571
891 l_inv_amt NUMBER;
892 l_inv_dist_amt NUMBER;
893 l_temp_cancelled_amount NUMBER;
894 l_inv_line_amt NUMBER;
895
896 CURSOR cur_get_SIA_Hold(p_inv_id ap_invoices_all.invoice_id%type)
897 IS
898 Select hold_lookup_code
899 From AP_HOLDS_ALL
900 Where invoice_id = p_inv_id
901 And hold_lookup_code = 'AWAIT_SEC_APP'
902 And release_lookup_code is not null;
903
904 l_Hold_Lookup_Code AP_HOLDS.Hold_Lookup_Code%TYPE;
905 l_SapStatusFlag VARCHAR2(1);
906 l_SapErrorNum NUMBER;
907
908 BEGIN
909
910 -- =============== START DEBUG LOG ================
911 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg1',
912 ' ** START PLACE_RELEASE_HOLD ** ');
913 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg1',
914 ' p_invoice_id --> ' || p_invoice_id);
915 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg2',
916 ' p_invoice_amt --> ' || p_invoice_amt);
917 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg3',
918 ' p_source --> ' || p_source);
919 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg4',
920 ' p_cancelled_date --> ' || p_cancelled_date);
921 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg5',
922 ' p_place_release --> ' || p_place_release);
923 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg6',
924 ' p_hold_lookup_code --> ' || p_hold_lookup_code);
925 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg7',
926 ' p_calling_sequence --> ' || p_calling_sequence);
927 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg8',
928 ' p_temp_cancelled_amount --> ' || p_temp_cancelled_amount);
929 -- =============== END DEBUG LOG ==================
930
931 --Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
932 l_debug_loc := 'place_release_hold';
933 l_system_user := 5;
934
935 -- =============== START DEBUG LOG ================
936 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg1',
937 ' Checking SIA ');
938 -- =============== END DEBUG LOG ==================
939
940 -- Bug 3409394
941 IGI_GEN.get_option_status('SIA', l_SapStatusFlag, l_SapErrorNum);
942
943 -- =============== START DEBUG LOG ================
944 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg9',
945 ' l_SapStatusFlag --> ' || l_SapStatusFlag);
946 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg10',
947 ' l_SapErrorNum --> ' || l_SapErrorNum);
948 -- =============== END DEBUG LOG ==================
949
950 -- Update the calling sequence
951 l_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||p_calling_sequence;
952
953 -- =============== START DEBUG LOG ================
954 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg11',
955 ' l_calling_sequence --> ' || l_calling_sequence);
956 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg12',
957 'Invoice Cancelled Date --> '||to_char(p_cancelled_date));
958 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg13',
959 'Calling invoice_not_excluded ' );
960 -- =============== END DEBUG LOG ==================
961
962 IF invoice_not_excluded( p_invoice_id
963 , p_source
964 , l_calling_sequence )
965 THEN
966
967 -- =============== START DEBUG LOG ================
968 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg14',
969 ' invoice_not_excluded Inside if ');
970 -- =============== END DEBUG LOG ==================
971
972 IF p_cancelled_date IS NULL
973 THEN
974
975 -- =============== START DEBUG LOG ================
976 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg15',
977 ' p_cancelled_date IS NULL ');
978 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg16',
979 ' Calling get_hold_status ');
980 -- =============== END DEBUG LOG ==================
981
982 Get_Hold_Status(p_invoice_id,
983 p_hold_lookup_code,
984 l_status,
985 l_calling_sequence);
986
987 -- =============== START DEBUG LOG ================
988 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg17',
989 ' Calling get_approval_status ');
990 -- =============== END DEBUG LOG ==================
991
992 l_approval_status := get_approval_status( p_invoice_id
993 ,l_calling_sequence ) ;
994
995 -- =============== START DEBUG LOG ================
996 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg18',
997 ' l_approval_status --> ' || l_approval_status);
998 -- =============== END DEBUG LOG ==================
999
1000 -- Bug 2636989 sowsubra start (1)
1001 -- Moved the block below to this position as the l_inv_amt
1002 -- and l_inv_dist_amt are needed for the if condition for setting EXP hold
1003 --
1004 IF p_place_release = 'P' THEN
1005
1006 -- =============== START DEBUG LOG ================
1007 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg19',
1008 ' IF p_place_release = P ');
1009 -- =============== END DEBUG LOG ==================
1010
1011 IF p_invoice_amt IS NULL THEN
1012
1013 -- =============== START DEBUG LOG ================
1014 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg20',
1015 ' IF p_invoice_amt IS NULL ');
1016 -- =============== END DEBUG LOG ==================
1017
1018 -- Check if invoice amount different from distribution amount
1019 SELECT invoice_amount
1020 INTO l_inv_amt
1021 FROM ap_invoices
1022 WHERE invoice_id = p_invoice_id;
1023
1024 ELSE
1025 l_inv_amt := p_invoice_amt;
1026 END IF;
1027
1028 -- =============== START DEBUG LOG ================
1029 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg21',
1030 ' l_inv_amt --> ' || l_inv_amt);
1031 -- =============== END DEBUG LOG ==================
1032
1033 -- Bug 2505522
1034 -- Bug 2576238
1035 BEGIN
1036 SELECT SUM(NVL(amount,0)) INTO l_inv_dist_amt
1037 FROM ap_invoice_distributions
1038 WHERE invoice_id = p_invoice_id
1039 AND line_type_lookup_code NOT IN ('AWT','PREPAY')
1040 AND prepay_tax_parent_id IS NULL
1041 GROUP BY invoice_id;
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 l_inv_dist_amt := 0;
1045 END;
1046 -- =============== START DEBUG LOG ================
1047 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg22',
1048 ' l_inv_dist_amt --> ' || l_inv_dist_amt);
1049 -- =============== END DEBUG LOG ==================
1050
1051 -- Bug 5905190
1052 BEGIN
1053 SELECT SUM(NVL(amount,0)) INTO l_inv_line_amt
1054 FROM ap_invoice_lines
1055 WHERE invoice_id = p_invoice_id
1056 AND line_type_lookup_code NOT IN ('AWT','PREPAY');
1057 --AND prepay_tax_parent_id IS NULL;
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 l_inv_line_amt := 0;
1061 END;
1062 -- =============== START DEBUG LOG ================
1063 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg22',
1064 ' l_inv_line_amt --> ' || l_inv_line_amt);
1065 -- =============== END DEBUG LOG ==================
1066
1067
1068 END IF; -- 'P' to place hold
1069
1070
1071 -- Bug 2636989 sowsubra end(1)
1072 IF l_approval_status = 'APPROVED' THEN
1073
1074 -- =============== START DEBUG LOG ================
1075 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg23',
1076 ' IF l_approval_status = APPROVED' );
1077 -- =============== END DEBUG LOG ==================
1078
1079 IF p_place_release = 'P' THEN
1080
1081 -- =============== START DEBUG LOG ================
1082 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg24',
1083 ' IF p_place_release = P' );
1084 -- =============== END DEBUG LOG ==================
1085
1086 -- Bug 2636989
1087 -- Added the following if condition to chk before
1088 -- setting the EXP hold whether invoice amount and distn amt are equal .
1089 -- This is needed in case the usr changes the headers amt multiple times without approving invoice
1090
1091 /* Bug#5905190
1092 Added code to check if invoice amount is equal to line amount
1093 */
1094
1095 IF (l_inv_amt = l_inv_dist_amt) AND (l_inv_amt = l_inv_line_amt) THEN
1096
1097 -- =============== START DEBUG LOG ================
1098 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg25',
1099 ' IF l_inv_amt = l_inv_dist_amt ' );
1100 -- =============== END DEBUG LOG ==================
1101
1102 --Bug 3409394
1103 IF l_sapStatusFlag ='Y' then
1104 OPEN cur_get_SIA_Hold(p_invoice_id);
1105 FETCH cur_get_SIA_Hold INTO l_hold_lookup_code;
1106 IF cur_get_SIA_Hold%NOTFOUND THEN
1107 l_hold_lookup_code := null;
1108 END IF;
1109 CLOSE cur_get_SIA_Hold;
1110
1111 IF l_hold_lookup_code is NOT NULL then
1112 Set_Hold(p_invoice_id,l_calling_sequence);
1113 END IF;
1114 ELSE
1115 IF p_temp_cancelled_amount is null THEN
1116 -- Bug 3409394 End(2) --
1117 Set_Hold(p_invoice_id,l_calling_sequence);
1118 END IF;
1119 END IF;
1120 ELSE
1121 NULL;
1122 -- =============== START DEBUG LOG ================
1123 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg26',
1124 ' IF l_inv_amt <> l_inv_dist_amt ' );
1125 -- =============== END DEBUG LOG ==================
1126 END IF; -- l_inv_amt = l_inv_dist_amt
1127 ELSE
1128 -- =============== START DEBUG LOG ================
1129 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg27',
1130 ' IF p_place_release <> P ' );
1131 -- =============== END DEBUG LOG ==================
1132 END IF; -- p_place_release = 'P'
1133
1134 ELSIF l_approval_status = 'NEEDS REAPPROVAL' THEN
1135 -- =============== START DEBUG LOG ================
1136 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg28',
1137 ' l_approval_status = NEEDS REAPPROVAL ' );
1138 -- =============== END DEBUG LOG ==================
1139
1140 IF (l_status = 'ALREADY ON HOLD') THEN
1141
1142 -- =============== START DEBUG LOG ================
1143 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg29',
1144 ' l_status = ALREADY ON HOLD' );
1145 -- =============== END DEBUG LOG ==================
1146
1147 IF p_place_release = 'R' THEN
1148
1149 -- =============== START DEBUG LOG ================
1150 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg30',
1151 'IF p_place_release = R' );
1152 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg31',
1153 'Calling Release_Hold ' );
1154 -- =============== END DEBUG LOG ==================
1155
1156 Release_Hold(p_invoice_id,
1157 p_hold_lookup_code,
1158 l_calling_sequence);
1159
1160 -- =============== START DEBUG LOG ================
1161 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg32',
1162 'Out of Release_Hold ' );
1163 -- =============== END DEBUG LOG ==================
1164
1165 END IF; -- check p_place_release = 'R'
1166 ELSE
1167 -- =============== START DEBUG LOG ================
1168 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg33',
1169 'IF l_status <> ALREADY ON HOLD ' );
1170 -- =============== END DEBUG LOG ==================
1171 END IF; -- check invoice_status
1172 ELSE
1173 -- =============== START DEBUG LOG ================
1174 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg34',
1175 'IF l_approval_status <> NEEDS REAPPROVAL ' );
1176 -- =============== END DEBUG LOG ==================
1177 END IF; --check l_approval_status
1178
1179 -- Bug 2377571
1180 IF p_place_release = 'P' THEN
1181 -- =============== START DEBUG LOG ================
1182 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg35',
1183 'IF p_place_release = P ' );
1184 -- =============== END DEBUG LOG ==================
1185 -- For Bug 5905190, added statement (l_inv_amt <> l_inv_line_amt)
1186 IF (l_inv_amt <> l_inv_dist_amt) or (l_inv_amt <> l_inv_line_amt) THEN
1187 -- =============== START DEBUG LOG ================
1188 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg36',
1189 'IF l_inv_amt <> l_inv_dist_amt ' );
1190 -- =============== END DEBUG LOG ==================
1191
1192 IF (l_status = 'ALREADY ON HOLD') THEN
1193 -- =============== START DEBUG LOG ================
1194 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg37',
1195 'IF (l_status = ALREADY ON HOLD ' );
1196 -- =============== END DEBUG LOG ==================
1197
1198 Release_Hold(p_invoice_id,
1199 p_hold_lookup_code,
1200 l_calling_sequence);
1201
1202 -- =============== START DEBUG LOG ================
1203 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg38',
1204 'Deleting from AP_holds --> ' || SQL%ROWCOUNT );
1205 -- =============== END DEBUG LOG ==================
1206
1207 END IF; -- already on hold
1208
1209 END IF; -- amounts are different
1210 END IF; -- 'P' to place hold
1211
1212 /***********************************
1213 The following code snippet below was originally put in to fix bug 3595853.
1214 This was to delete the EXP hold from AP_HOLDS_ALL, so allow the invoice to
1215 be cancelled (by AP - in package AP_CANCEL_PKG Function: ap_cancel_single_invoice).
1216 However now that all EXP HOLDS are RELEASED, rather than deleted
1217 (requirement by AX - bug 3801520).
1218 This fix is no longer required as the above code snippet suffices; invoice amount
1219 is not equal to the distribution amount (ie l_inv_amt <> l_inv_dist_amt), so the
1220 hold is released anyway (Distribution amount has been changed in step 10. of
1221 ap_cancel_single_invoice, while temp_cancelled_amount is populated in step 11.).
1222 However the code snippet below will still be left in as a backup, incase AP change the
1223 function ap_cancel_single_invoice, so that the above code does not work for both cases.
1224 ***********************************/
1225
1226
1227 IF p_place_release = 'P' THEN
1228 -- =============== START DEBUG LOG ================
1229 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg39',
1230 'p_place_release = P ' );
1231 -- =============== END DEBUG LOG ==================
1232
1233 IF l_status = 'ALREADY ON HOLD' THEN
1234
1235 IF (l_inv_amt = 0 and l_inv_dist_amt = 0 and l_inv_line_amt = 0 ) -- Bug 5905190
1236 AND (p_temp_cancelled_amount is not null) THEN
1237
1238 -- =============== START DEBUG LOG ================
1239 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg40',
1240 '(l_inv_amt = 0 and l_inv_dist_amt = 0) ' ||
1241 ' AND (p_temp_cancelled_amount is not null)' );
1242 -- =============== END DEBUG LOG ==================
1243
1244 Release_Hold(p_invoice_id,
1245 p_hold_lookup_code,
1246 l_calling_sequence);
1247
1248 -- =============== START DEBUG LOG ================
1249 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg41',
1250 'Deleting from AP_holds --> ' || SQL%ROWCOUNT );
1251 -- =============== END DEBUG LOG ==================
1252
1253 END IF; -- amounts are different
1254 END IF;
1255 END IF; -- 'P' to place hold
1256
1257
1258 END IF ; --is cancelled date null
1259 END IF; -- is invoice excluded
1260
1261 -- =============== START DEBUG LOG ================
1262 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg42',
1263 ' ** END PLACE_RELEASE_HOLD ** ');
1264 -- =============== END DEBUG LOG ==================
1265
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268
1269 IF (SQLCODE <> -20001) THEN
1270 -- =============== START DEBUG LOG ================
1271 DEBUG_LOG_UNEXP_ERROR ('Place_Release_Hold.unexp1','DEFAULT');
1272 -- =============== END DEBUG LOG ==================
1273 END IF;
1274 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
1275 END Place_Release_Hold;
1276
1277 END igi_exp_holds;