[Home] [Help]
PACKAGE BODY: APPS.IGI_EXP_HOLDS
Source
1 PACKAGE BODY igi_exp_holds AS
2 -- $Header: igiexprb.pls 120.16.12020000.3 2012/12/12 19:57:45 sasukuma 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 l_approval_status_code VARCHAR2(100);
785 l_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE;
786
787 BEGIN
788
789 -- =============== START DEBUG LOG ================
790 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg1',
791 ' ** START IGI_EXP_AP_INV_LINE_T2 ** ');
792 -- =============== END DEBUG LOG ==================
793 -- Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
794 l_debug_loc := 'IGI_EXP_AP_INV_LINE_T2';
795 l_calling_sequence := 'AWAIT EXP APP';
796
797 -- Update the calling sequence --
798 l_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||
799 p_calling_sequence;
800
801 -- =============== START DEBUG LOG ================
802 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg2',
803 ' l_calling_sequence --> ' || l_calling_sequence);
804 -- =============== END DEBUG LOG ==================
805
806 FOR i IN 1 .. igi_exp_holds.l_LineTableRow LOOP
807
808 l_invoice_id := igi_exp_holds.l_InvoiceidLineTable(i);
809
810 -- =============== START DEBUG LOG ================
811 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg3',
812 ' l_invoice_id --> ' || l_invoice_id );
813 -- =============== END DEBUG LOG ==================
814
815 SELECT a.source,
816 a.cancelled_date,
817 a.temp_cancelled_Amount,
818 ap_invoices_pkg.get_approval_status (a.invoice_id, a.invoice_amount, a.payment_status_flag, a.invoice_type_lookup_code),
819 a.invoice_type_lookup_code
820 INTO l_source,
821 l_cancelled_date,
822 l_temp_cancelled_amount,
823 l_approval_status_code,
824 l_invoice_type_lookup_code
825 FROM AP_INVOICES_ALL a
826 WHERE a.invoice_id = l_invoice_id;
827
828 -- =============== START DEBUG LOG ================
829 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg4',
830 ' l_source --> ' || l_source );
831 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg5',
832 ' l_cancelled_date --> ' || l_cancelled_date );
833 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg6',
834 ' l_temp_cancelled_amount --> ' || l_temp_cancelled_amount );
835 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg7',
836 ' Calling igi_exp_holds.Place_Release_Hold ' );
837 -- =============== END DEBUG LOG ==================
838
839 IF (l_invoice_type_lookup_code = 'PREPAYMENT' AND l_approval_status_code IN ('AVAILABLE', 'FULL')) THEN
840 NULL;
841 ELSE
842 igi_exp_holds.Place_Release_Hold(l_invoice_id,
843 -- Bug 2469158
844 '', -- invoice amount
845 l_source,
846 l_cancelled_date,
847 'P',
848 l_hold_lookup_code,
849 l_calling_sequence,
850 l_temp_cancelled_amount
851 );
852 END IF;
853
854 -- =============== START DEBUG LOG ================
855 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg8',
856 ' Out of igi_exp_holds.Place_Release_Hold ' );
857 -- =============== END DEBUG LOG ==================
858 END LOOP;
859
860 -- =============== START DEBUG LOG ================
861 Debug_log_string (l_proc_level, 'Igi_Exp_Ap_Inv_Line_T2.Msg9',
862 ' ** END IGI_EXP_AP_INV_LINE_T2 ** ');
863 -- =============== END DEBUG LOG ==================
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 IF (SQLCODE <> -20001) THEN
868 -- =============== START DEBUG LOG ================
869 DEBUG_LOG_UNEXP_ERROR ('Igi_Exp_Ap_Inv_Line_T2.unexp1','DEFAULT');
870 -- =============== END DEBUG LOG ==================
871 END IF;
872 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
873 END Igi_Exp_Ap_Inv_Line_T2;
874 -- Bug 5905190 End
875
876 --============================================================================
877 -- PLACE_RELEASE_HOLD: Procedure that places or releases an EXP Hold if the
878 -- invoice status is APPROVED
879 --============================================================================
880 PROCEDURE Place_Release_Hold( p_invoice_id IN NUMBER
881 -- Bug 2469158.
882 , p_invoice_amt IN NUMBER
883 , p_source IN VARCHAR2
884 , p_cancelled_date IN DATE
885 , p_place_release IN VARCHAR2
886 , p_hold_lookup_code IN VARCHAR2
887 , p_calling_sequence IN VARCHAR2
888 -- Bug 3595853.
889 , p_temp_cancelled_amount IN NUMBER default NULL
890 )
891 IS
892 l_approval_status VARCHAR2(80) ;
893 l_system_user NUMBER;
894 l_debug_loc VARCHAR2(30);
895 l_debug_info VARCHAR2(250) ;
896 l_inv_hold_status VARCHAR2(240);
897 l_status VARCHAR2(20);
898 l_existing_hold_reason VARCHAR2(240);
899 l_calling_sequence VARCHAR2(1000);
900 -- Bug 2377571
901 l_inv_amt NUMBER;
902 l_inv_dist_amt NUMBER;
903 l_temp_cancelled_amount NUMBER;
904 l_inv_line_amt NUMBER;
905
906 CURSOR cur_get_SIA_Hold(p_inv_id ap_invoices_all.invoice_id%type)
907 IS
908 Select hold_lookup_code
909 From AP_HOLDS_ALL
910 Where invoice_id = p_inv_id
911 And hold_lookup_code = 'AWAIT_SEC_APP'
912 And release_lookup_code is not null;
913
914 l_Hold_Lookup_Code AP_HOLDS.Hold_Lookup_Code%TYPE;
915 l_SapStatusFlag VARCHAR2(1);
916 l_SapErrorNum NUMBER;
917
918 BEGIN
919
920 -- =============== START DEBUG LOG ================
921 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg1',
922 ' ** START PLACE_RELEASE_HOLD ** ');
923 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg1',
924 ' p_invoice_id --> ' || p_invoice_id);
925 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg2',
926 ' p_invoice_amt --> ' || p_invoice_amt);
927 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg3',
928 ' p_source --> ' || p_source);
929 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg4',
930 ' p_cancelled_date --> ' || p_cancelled_date);
931 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg5',
932 ' p_place_release --> ' || p_place_release);
933 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg6',
934 ' p_hold_lookup_code --> ' || p_hold_lookup_code);
935 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg7',
936 ' p_calling_sequence --> ' || p_calling_sequence);
937 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg8',
938 ' p_temp_cancelled_amount --> ' || p_temp_cancelled_amount);
939 -- =============== END DEBUG LOG ==================
940
941 --Initialize variables inside BEGIN bacause of GSCC Standard - File.sql.35
942 l_debug_loc := 'place_release_hold';
943 l_system_user := 5;
944
945 -- =============== START DEBUG LOG ================
946 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg1',
947 ' Checking SIA ');
948 -- =============== END DEBUG LOG ==================
949
950 -- Bug 3409394
951 IGI_GEN.get_option_status('SIA', l_SapStatusFlag, l_SapErrorNum);
952
953 -- =============== START DEBUG LOG ================
954 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg9',
955 ' l_SapStatusFlag --> ' || l_SapStatusFlag);
956 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg10',
957 ' l_SapErrorNum --> ' || l_SapErrorNum);
958 -- =============== END DEBUG LOG ==================
959
960 -- Update the calling sequence
961 l_calling_sequence := 'IGI_EXP_HOLDS.'||l_debug_loc||'<-'||p_calling_sequence;
962
963 -- =============== START DEBUG LOG ================
964 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg11',
965 ' l_calling_sequence --> ' || l_calling_sequence);
966 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg12',
967 'Invoice Cancelled Date --> '||to_char(p_cancelled_date));
968 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg13',
969 'Calling invoice_not_excluded ' );
970 -- =============== END DEBUG LOG ==================
971
972 IF invoice_not_excluded( p_invoice_id
973 , p_source
974 , l_calling_sequence )
975 THEN
976
977 -- =============== START DEBUG LOG ================
978 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg14',
979 ' invoice_not_excluded Inside if ');
980 -- =============== END DEBUG LOG ==================
981
982 IF p_cancelled_date IS NULL
983 THEN
984
985 -- =============== START DEBUG LOG ================
986 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg15',
987 ' p_cancelled_date IS NULL ');
988 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg16',
989 ' Calling get_hold_status ');
990 -- =============== END DEBUG LOG ==================
991
992 Get_Hold_Status(p_invoice_id,
993 p_hold_lookup_code,
994 l_status,
995 l_calling_sequence);
996
997 -- =============== START DEBUG LOG ================
998 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg17',
999 ' Calling get_approval_status ');
1000 -- =============== END DEBUG LOG ==================
1001
1002 l_approval_status := get_approval_status( p_invoice_id
1003 ,l_calling_sequence ) ;
1004
1005 -- =============== START DEBUG LOG ================
1006 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg18',
1007 ' l_approval_status --> ' || l_approval_status);
1008 -- =============== END DEBUG LOG ==================
1009
1010 -- Bug 2636989 sowsubra start (1)
1011 -- Moved the block below to this position as the l_inv_amt
1012 -- and l_inv_dist_amt are needed for the if condition for setting EXP hold
1013 --
1014 IF p_place_release = 'P' THEN
1015
1016 -- =============== START DEBUG LOG ================
1017 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg19',
1018 ' IF p_place_release = P ');
1019 -- =============== END DEBUG LOG ==================
1020
1021 IF p_invoice_amt IS NULL THEN
1022
1023 -- =============== START DEBUG LOG ================
1024 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg20',
1025 ' IF p_invoice_amt IS NULL ');
1026 -- =============== END DEBUG LOG ==================
1027
1028 -- Check if invoice amount different from distribution amount
1029 SELECT invoice_amount
1030 INTO l_inv_amt
1031 FROM ap_invoices
1032 WHERE invoice_id = p_invoice_id;
1033
1034 ELSE
1035 l_inv_amt := p_invoice_amt;
1036 END IF;
1037
1038 -- =============== START DEBUG LOG ================
1039 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg21',
1040 ' l_inv_amt --> ' || l_inv_amt);
1041 -- =============== END DEBUG LOG ==================
1042
1043 -- Bug 2505522
1044 -- Bug 2576238
1045 BEGIN
1046 SELECT SUM(NVL(amount,0)) INTO l_inv_dist_amt
1047 FROM ap_invoice_distributions
1048 WHERE invoice_id = p_invoice_id
1049 AND line_type_lookup_code NOT IN ('AWT')
1050 AND NVL(invoice_includes_prepay_flag, 'Y') = 'Y'
1051 AND prepay_tax_parent_id IS NULL
1052 GROUP BY invoice_id;
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 l_inv_dist_amt := 0;
1056 END;
1057 -- =============== START DEBUG LOG ================
1058 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg22',
1059 ' l_inv_dist_amt --> ' || l_inv_dist_amt);
1060 -- =============== END DEBUG LOG ==================
1061
1062 -- Bug 5905190
1063 BEGIN
1064 SELECT SUM(NVL(amount,0)) INTO l_inv_line_amt
1065 FROM ap_invoice_lines
1066 WHERE invoice_id = p_invoice_id
1067 AND line_type_lookup_code NOT IN ('AWT')
1068 AND NVL(invoice_includes_prepay_flag, 'Y') = 'Y';
1069 --AND prepay_tax_parent_id IS NULL;
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 l_inv_line_amt := 0;
1073 END;
1074 -- =============== START DEBUG LOG ================
1075 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg22',
1076 ' l_inv_line_amt --> ' || l_inv_line_amt);
1077 -- =============== END DEBUG LOG ==================
1078
1079
1080 END IF; -- 'P' to place hold
1081
1082
1083 -- Bug 2636989 sowsubra end(1)
1084 IF l_approval_status = 'APPROVED' THEN
1085
1086 -- =============== START DEBUG LOG ================
1087 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg23',
1088 ' IF l_approval_status = APPROVED' );
1089 -- =============== END DEBUG LOG ==================
1090
1091 IF p_place_release = 'P' THEN
1092
1093 -- =============== START DEBUG LOG ================
1094 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg24',
1095 ' IF p_place_release = P' );
1096 -- =============== END DEBUG LOG ==================
1097
1098 -- Bug 2636989
1099 -- Added the following if condition to chk before
1100 -- setting the EXP hold whether invoice amount and distn amt are equal .
1101 -- This is needed in case the usr changes the headers amt multiple times without approving invoice
1102
1103 /* Bug#5905190
1104 Added code to check if invoice amount is equal to line amount
1105 */
1106
1107 IF (l_inv_amt = l_inv_dist_amt) AND (l_inv_amt = l_inv_line_amt) THEN
1108
1109 -- =============== START DEBUG LOG ================
1110 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg25',
1111 ' IF l_inv_amt = l_inv_dist_amt ' );
1112 -- =============== END DEBUG LOG ==================
1113
1114 --Bug 3409394
1115 IF l_sapStatusFlag ='Y' then
1116 OPEN cur_get_SIA_Hold(p_invoice_id);
1117 FETCH cur_get_SIA_Hold INTO l_hold_lookup_code;
1118 IF cur_get_SIA_Hold%NOTFOUND THEN
1119 l_hold_lookup_code := null;
1120 END IF;
1121 CLOSE cur_get_SIA_Hold;
1122
1123 IF l_hold_lookup_code is NOT NULL then
1124 Set_Hold(p_invoice_id,l_calling_sequence);
1125 END IF;
1126 ELSE
1127 IF p_temp_cancelled_amount is null THEN
1128 -- Bug 3409394 End(2) --
1129 Set_Hold(p_invoice_id,l_calling_sequence);
1130 END IF;
1131 END IF;
1132 ELSE
1133 NULL;
1134 -- =============== START DEBUG LOG ================
1135 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg26',
1136 ' IF l_inv_amt <> l_inv_dist_amt ' );
1137 -- =============== END DEBUG LOG ==================
1138 END IF; -- l_inv_amt = l_inv_dist_amt
1139 ELSE
1140 -- =============== START DEBUG LOG ================
1141 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg27',
1142 ' IF p_place_release <> P ' );
1143 -- =============== END DEBUG LOG ==================
1144 END IF; -- p_place_release = 'P'
1145
1146 ELSIF l_approval_status = 'NEEDS REAPPROVAL' THEN
1147 -- =============== START DEBUG LOG ================
1148 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg28',
1149 ' l_approval_status = NEEDS REAPPROVAL ' );
1150 -- =============== END DEBUG LOG ==================
1151
1152 IF (l_status = 'ALREADY ON HOLD') THEN
1153
1154 -- =============== START DEBUG LOG ================
1155 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg29',
1156 ' l_status = ALREADY ON HOLD' );
1157 -- =============== END DEBUG LOG ==================
1158
1159 IF p_place_release = 'R' THEN
1160
1161 -- =============== START DEBUG LOG ================
1162 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg30',
1163 'IF p_place_release = R' );
1164 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg31',
1165 'Calling Release_Hold ' );
1166 -- =============== END DEBUG LOG ==================
1167
1168 Release_Hold(p_invoice_id,
1169 p_hold_lookup_code,
1170 l_calling_sequence);
1171
1172 -- =============== START DEBUG LOG ================
1173 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg32',
1174 'Out of Release_Hold ' );
1175 -- =============== END DEBUG LOG ==================
1176
1177 END IF; -- check p_place_release = 'R'
1178 ELSE
1179 -- =============== START DEBUG LOG ================
1180 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg33',
1181 'IF l_status <> ALREADY ON HOLD ' );
1182 -- =============== END DEBUG LOG ==================
1183 END IF; -- check invoice_status
1184 ELSE
1185 -- =============== START DEBUG LOG ================
1186 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg34',
1187 'IF l_approval_status <> NEEDS REAPPROVAL ' );
1188 -- =============== END DEBUG LOG ==================
1189 END IF; --check l_approval_status
1190
1191 -- Bug 2377571
1192 IF p_place_release = 'P' THEN
1193 -- =============== START DEBUG LOG ================
1194 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg35',
1195 'IF p_place_release = P ' );
1196 -- =============== END DEBUG LOG ==================
1197 -- For Bug 5905190, added statement (l_inv_amt <> l_inv_line_amt)
1198 IF (l_inv_amt <> l_inv_dist_amt) or (l_inv_amt <> l_inv_line_amt) THEN
1199 -- =============== START DEBUG LOG ================
1200 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg36',
1201 'IF l_inv_amt <> l_inv_dist_amt ' );
1202 -- =============== END DEBUG LOG ==================
1203
1204 IF (l_status = 'ALREADY ON HOLD') THEN
1205 -- =============== START DEBUG LOG ================
1206 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg37',
1207 'IF (l_status = ALREADY ON HOLD ' );
1208 -- =============== END DEBUG LOG ==================
1209
1210 Release_Hold(p_invoice_id,
1211 p_hold_lookup_code,
1212 l_calling_sequence);
1213
1214 -- =============== START DEBUG LOG ================
1215 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg38',
1216 'Deleting from AP_holds --> ' || SQL%ROWCOUNT );
1217 -- =============== END DEBUG LOG ==================
1218
1219 END IF; -- already on hold
1220
1221 END IF; -- amounts are different
1222 END IF; -- 'P' to place hold
1223
1224 /***********************************
1225 The following code snippet below was originally put in to fix bug 3595853.
1226 This was to delete the EXP hold from AP_HOLDS_ALL, so allow the invoice to
1227 be cancelled (by AP - in package AP_CANCEL_PKG Function: ap_cancel_single_invoice).
1228 However now that all EXP HOLDS are RELEASED, rather than deleted
1229 (requirement by AX - bug 3801520).
1230 This fix is no longer required as the above code snippet suffices; invoice amount
1231 is not equal to the distribution amount (ie l_inv_amt <> l_inv_dist_amt), so the
1232 hold is released anyway (Distribution amount has been changed in step 10. of
1233 ap_cancel_single_invoice, while temp_cancelled_amount is populated in step 11.).
1234 However the code snippet below will still be left in as a backup, incase AP change the
1235 function ap_cancel_single_invoice, so that the above code does not work for both cases.
1236 ***********************************/
1237
1238
1239 IF p_place_release = 'P' THEN
1240 -- =============== START DEBUG LOG ================
1241 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg39',
1242 'p_place_release = P ' );
1243 -- =============== END DEBUG LOG ==================
1244
1245 IF l_status = 'ALREADY ON HOLD' THEN
1246
1247 IF (l_inv_amt = 0 and l_inv_dist_amt = 0 and l_inv_line_amt = 0 ) -- Bug 5905190
1248 AND (p_temp_cancelled_amount is not null) THEN
1249
1250 -- =============== START DEBUG LOG ================
1251 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg40',
1252 '(l_inv_amt = 0 and l_inv_dist_amt = 0) ' ||
1253 ' AND (p_temp_cancelled_amount is not null)' );
1254 -- =============== END DEBUG LOG ==================
1255
1256 Release_Hold(p_invoice_id,
1257 p_hold_lookup_code,
1258 l_calling_sequence);
1259
1260 -- =============== START DEBUG LOG ================
1261 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg41',
1262 'Deleting from AP_holds --> ' || SQL%ROWCOUNT );
1263 -- =============== END DEBUG LOG ==================
1264
1265 END IF; -- amounts are different
1266 END IF;
1267 END IF; -- 'P' to place hold
1268
1269
1270 END IF ; --is cancelled date null
1271 END IF; -- is invoice excluded
1272
1273 -- =============== START DEBUG LOG ================
1274 Debug_log_string (l_proc_level, 'Place_Release_Hold.Msg42',
1275 ' ** END PLACE_RELEASE_HOLD ** ');
1276 -- =============== END DEBUG LOG ==================
1277
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280
1281 IF (SQLCODE <> -20001) THEN
1282 -- =============== START DEBUG LOG ================
1283 DEBUG_LOG_UNEXP_ERROR ('Place_Release_Hold.unexp1','DEFAULT');
1284 -- =============== END DEBUG LOG ==================
1285 END IF;
1286 RAISE_APPLICATION_ERROR(-20001, fnd_message.get);
1287 END Place_Release_Hold;
1288
1289 END igi_exp_holds;