[Home] [Help]
PACKAGE BODY: APPS.PAAP_PWP_PKG
Source
1 PACKAGE BODY PAAP_PWP_PKG AS
2 -- /* $Header: PAAPPWPB.pls 120.37.12020000.2 2012/07/19 09:24:19 admarath ship $
3
4 ProjFunc_Curr_Amount Number;
5 Proj_Curr_Amount Number;
6 P_Trans_Amount Number;
7
8 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
9
10 ---------------------------------------------------------------------------------------------------------
11 -- This Function returns Project Functional Currency Code
12 ----------------------------------------------------------------------------------------------------------
13 Function Get_ProjFunc_Curr Return VARCHAR2;
14
15 ---------------------------------------------------------------------------------------------------------
16 -- This Function returns Exchange Rate Date for Project Functional Currency
17 ----------------------------------------------------------------------------------------------------------
18 Function Get_ProjFunc_RateDate Return DATE;
19
20 ---------------------------------------------------------------------------------------------------------
21 -- This Function returns Exchange Rate Type for Project Functional Currency
22 ----------------------------------------------------------------------------------------------------------
23 Function Get_ProjFunc_RateType Return VARCHAR2;
24
25 ---------------------------------------------------------------------------------------------------------
26 -- This Function returns Project Currency Code
27 ----------------------------------------------------------------------------------------------------------
28 Function Get_ProjFunc_Rate Return NUMBER;
29
30 ---------------------------------------------------------------------------------------------------------
31 -- This Function returns Amount in Project Functional Currency
32 ----------------------------------------------------------------------------------------------------------
33 Function Get_ProjFunc_Amt Return NUMBER;
34
35 ---------------------------------------------------------------------------------------------------------
36 -- This Function returns Project Currency Code
37 ----------------------------------------------------------------------------------------------------------
38 Function Get_Proj_Curr Return VARCHAR2;
39
40 ---------------------------------------------------------------------------------------------------------
41 -- This Function returns Exchange Rate Date for Project Currency
42 ----------------------------------------------------------------------------------------------------------
43 Function Get_Proj_RateDate Return DATE;
44
45 ---------------------------------------------------------------------------------------------------------
46 -- This Function returns Exchange Rate Type for Project Code
47 ----------------------------------------------------------------------------------------------------------
48 Function Get_Proj_RateType Return VARCHAR2;
49
50 ---------------------------------------------------------------------------------------------------------
51 -- This Function returns Exchange Rate for Project Currency
52 ----------------------------------------------------------------------------------------------------------
53 Function Get_Proj_Rate Return NUMBER;
54
55 ---------------------------------------------------------------------------------------------------------
56 -- This Function returns Amount in Project Currency
57 ----------------------------------------------------------------------------------------------------------
58 Function Get_Proj_Amt Return NUMBER;
59
60 ---------------------------------------------------------------------------------------------------------
61 -- This procedure prints the text which is being passed as the input
62 -- Input parameters
63 -- Parameters Type Required Description
64 -- p_log_msg VARCHAR2 YES It stores text which you want
65 -- to print on screen
66 -- Out parameters
67 ----------------------------------------------------------------------------------------------------------
68 PROCEDURE log_message (p_log_msg IN VARCHAR2,p_proc_name VARCHAR2)
69 IS
70 BEGIN
71 pa_debug.write('log_message: ' || p_proc_name, 'log: ' || p_log_msg, 3);
72 END log_message;
73
74 Function Get_ProjFunc_curr Return Varchar2 Is
75 BEGIN
76 Return ProjFunc_Currency;
77 END;
78
79 Function Get_ProjFunc_rateDate Return Date Is
80 BEGIN
81 Return ProjFunc_Cst_Rate_Date;
82 END;
83
84 Function Get_ProjFunc_ratetype Return Varchar2 Is
85 BEGIN
86 Return ProjFunc_Cst_Rate_Type;
87 END;
88
89 Function Get_ProjFunc_rate Return Number Is
90 BEGIN
91 Return ProjFunc_CST_RATE;
92 END;
93
94 Function Get_ProjFunc_amt Return Number Is
95 BEGIN
96 Return ProjFunc_Curr_Amount;
97 END;
98
99 Function Get_Proj_curr Return Varchar2 Is
100 BEGIN
101 Return Proj_Currency;
102 END;
103
104 Function Get_Proj_rateDate Return Date Is
105 BEGIN
106 Return Proj_Cst_Rate_Date;
107 END;
108
109 Function Get_Proj_ratetype Return Varchar2 Is
110 BEGIN
111 Return Proj_Cst_Rate_Type;
112 END;
113
114 Function Get_Proj_rate Return Number Is
115 BEGIN
116 Return Proj_CST_RATE;
117 END;
118
119 Function Get_Proj_amt Return Number Is
120 BEGIN
121 Return Proj_Curr_Amount;
122 END;
123
124 ---------------------------------------------------------------------------------------------------------
125 -- This Procedure derives the all the conversion attributes from any currency passed as a parameter to
126 -- Project Functional/Project/Acct Currencies. This procedure caches all the Currency /Exchange Rate/
127 -- Exchange Rate Date/Exchange Rate Type values in global variables.
128 -- Input parameters
129 -- Parameters Type Required Description
130 -- p_project_id NUMBER YES It stores the project_id
131 -- p_task_id NUMBER YES It stores the task_Id
132 -- p_ei_date DATE YES It stores the expenditure item date
133 -- p_from_currency VARCHAR2 NO From Currency
134 -- Out parameters
135 ----------------------------------------------------------------------------------------------------------
136 Procedure Derive_ProjCurr_Attribute(P_Project_Id IN Number,
137 P_Task_Id IN Number,
138 P_Exp_Item_Date IN Date,
139 P_FromCurrency IN Varchar2) Is
140 l_denom_raw_cost Number;
141 l_denom_curr_code Varchar2(30);
142 l_acct_curr_code Varchar2(30);
143
144 l_acct_rate_Date Date;
145 l_acct_rate_type Varchar2(30);
146 l_acct_exch_rate Number;
147 l_acct_raw_cost Number;
148
149 l_project_rate_type Varchar2(30);
150 l_project_rate_Date Date;
151 l_project_exch_rate Number;
152 l_project_raw_cost Number;
153
154 l_ProjFunc_cost_rate_type Varchar2(30);
155 l_ProjFunc_cost_rate_Date Date;
156 l_ProjFunc_cost_exch_rate Number;
157 l_ProjFunc_raw_cost Number;
158
159 l_status Varchar2(2000);
160 l_stage Number;
161
162 BEGIN
163 IF P_DEBUG_MODE = 'Y' THEN
164 log_message('[PA_CURR_CODE : '||PA_CURR_CODE||' ]'||'[P_project_id : '||p_project_id||' ]'
165 ||' [P_Task_Id : '||P_Task_Id||'] '||'[P_Exp_Item_Date : '||P_Exp_Item_Date||']',
166 'Derive_ProjCurr_Attribute');
167 END IF;
168
169 If PA_CURR_CODE IS NULL Then
170 PA_CURR_CODE := PA_Currency.get_Currency_code;
171
172 IF P_DEBUG_MODE = 'Y' THEN
173 log_message('From PA_Currency.get_Currency_code [PA_CURR_CODE : '||PA_CURR_CODE||' ]' ,
174 'Derive_ProjCurr_Attribute');
175 END IF;
176 END If;
177
178 IF P_DEBUG_MODE = 'Y' THEN
179 log_message('Before Calling pa_multi_Currency_txn.Get_Proj_curr_code_sql '||
180 '[G_project_id : '||G_Project_Id||' ]',
181 'Derive_ProjCurr_Attribute');
182 END IF;
183
184 If NVL(G_Project_Id, -99) <> P_Project_ID Then
185 Proj_Currency := pa_multi_Currency_txn.Get_Proj_curr_code_sql(p_project_id);
186 G_Project_Id := P_Project_Id;
187 END If;
188
189 IF P_DEBUG_MODE = 'Y' THEN
190 log_message('After Calling pa_multi_Currency_txn.Get_Proj_curr_code_sql '||
191 '[G_project_id : '||G_Project_Id||']'||
192 '[Proj_Currency : '||Proj_Currency||']' ,
193 'Derive_ProjCurr_Attribute');
194 END IF;
195
196 IF P_DEBUG_MODE = 'Y' THEN
197 log_message('Before Calling pa_multi_Currency_txn.get_def_ProjFunc_Cst_Rate_Type '||
198 '[G_Task_Id : '||G_Task_Id||']',
199 'Derive_ProjCurr_Attribute');
200 END IF;
201
202 If NVL(G_Task_Id,-99) <> P_Task_Id Then
203 pa_multi_Currency_txn.get_def_ProjFunc_Cst_Rate_Type(
204 P_task_id ,
205 ProjFunc_Currency,
206 ProjFunc_Cst_Rate_Type);
207 END If;
208
209 IF P_DEBUG_MODE = 'Y' THEN
210 log_message('After Calling pa_multi_Currency_txn.get_def_ProjFunc_Cst_Rate_Type '||
211 '[ProjFunc_Currency : '||ProjFunc_Currency||']'||' [ProjFunc_Cst_Rate_Type : '||
212 ProjFunc_Cst_Rate_Type||'] ',
213 'Derive_ProjCurr_Attribute');
214 END IF;
215
216 If NVL(G_Expenditure_Item_Date,SYSDate) <> P_Exp_Item_Date
217 OR NVL(G_Task_Id,-99) <> P_Task_Id
218 OR NVL(G_From_Curr,'XXXX') <> P_FromCurrency Then /* Added G_From_Curr for Bug# 7830751 */
219
220 IF P_DEBUG_MODE = 'Y' THEN
221 log_message('Before Calling pa_multi_Currency_txn.get_Currency_amounts',
222 'Derive_ProjCurr_Attribute');
223 END IF;
224
225 /* Bug# Added this for the bug 8849692 */
226 /* Bug#8897745 */
227 IF ACCT_CST_RATE IS NOT NULL THEN
228 l_acct_rate_Date:= ACCT_Cst_Rate_Date;
229 l_acct_rate_type:= ACCT_Cst_Rate_Type;
230 l_acct_exch_rate:= ACCT_CST_RATE;
231 END IF;
232 pa_multi_Currency_txn.get_Currency_amounts (
233 P_project_id =>p_Project_Id,
234 P_task_id =>P_task_id,
235 P_EI_Date =>P_Exp_Item_Date,
236 P_calling_module =>'GET_CURR_AMOUNTS',
237 P_denom_curr_code =>p_fromCurrency,
238 P_acct_curr_code =>PA_CURR_CODE,
239 P_accounted_flag =>'Y',
240 P_acct_rate_Date =>l_acct_rate_Date,
241 P_acct_rate_type =>l_acct_rate_type,
242 P_acct_exch_rate =>l_acct_exch_rate,
243 P_project_curr_code =>Proj_Currency,
244 P_project_rate_type =>l_project_rate_type,
245 P_project_rate_Date =>l_project_rate_Date,
246 P_project_exch_rate =>l_project_exch_rate,
247 P_ProjFunc_curr_code =>ProjFunc_Currency,
248 P_ProjFunc_cost_rate_type =>l_ProjFunc_cost_rate_type,
249 P_ProjFunc_cost_rate_Date =>l_ProjFunc_cost_rate_Date,
250 P_ProjFunc_cost_exch_rate =>l_ProjFunc_cost_exch_rate,
251 P_denom_raw_cost => l_denom_raw_cost,
252 P_acct_raw_cost => l_acct_raw_cost,
253 P_project_raw_cost => l_project_raw_cost,
254 P_ProjFunc_raw_cost => l_ProjFunc_raw_cost,
255 P_system_linkage => 'VI',
256 P_status =>l_status,
257 P_stage =>l_stage);
258
259 IF l_status IS NULL THEN /* Bug#8897745 */
260 ProjFunc_Cst_Rate_Date :=l_ProjFunc_cost_rate_Date;
261 ProjFunc_Cst_Rate_Type :=l_ProjFunc_cost_rate_type;
262 ProjFunc_CST_RATE :=nvl(l_ProjFunc_cost_exch_rate,1);
263
264 Proj_Cst_Rate_Date :=l_project_rate_Date;
265 Proj_Cst_Rate_Type :=l_project_rate_type;
266 Proj_CST_RATE :=nvl(l_project_exch_rate,1);
267
268 /* Bug# Added this for bug# 8849692 */
269 IF nvl(ACCT_Cst_Rate_Type,'XXX') <> 'User' THEN
270 ACCT_Cst_Rate_Date :=l_acct_rate_Date;
271 ACCT_Cst_Rate_Type :=l_acct_rate_type;
272 ACCT_CST_RATE :=nvl(l_acct_exch_rate,1);
273 END IF;
274 END IF;
275
276 G_Expenditure_Item_Date := P_Exp_Item_Date;
277 G_Task_Id := P_Task_Id;
278 G_From_Curr := P_FromCurrency; /* For Bug# 7830751 */
279
280 IF P_DEBUG_MODE = 'Y' THEN
281 log_message('After Calling pa_multi_Currency_txn.get_Currency_amounts '||
282 '[G_Expenditure_Item_Date : '||G_Expenditure_Item_Date||']'||' [ProjFunc_Cst_Rate_Date : '||
283 ProjFunc_Cst_Rate_Date||'] '||' [ProjFunc_Cst_Rate_Type : '||
284 ProjFunc_Cst_Rate_Type||'] '||' [ProjFunc_CST_RATE : '||
285 ProjFunc_CST_RATE||'] '||' [Proj_Cst_Rate_Date : '||
286 Proj_Cst_Rate_Date||'] '||' [Proj_Cst_Rate_Type : '||
287 Proj_Cst_Rate_Type||'] '||' [Proj_CST_RATE : '||
288 Proj_CST_RATE||'] '||' [ACCT_Cst_Rate_Date : '||
289 ACCT_Cst_Rate_Date||'] '||' [ACCT_Cst_Rate_Type : '||
290 ACCT_Cst_Rate_Type||'] '||' [ACCT_CST_RATE : '||
291 ACCT_CST_RATE||'] '||' l_status :'||
292 l_status,
293 'Derive_ProjCurr_Attribute');
294 END IF;
295
296 END If;
297 EXCEPTION
298 WHEN OTHERS THEN
299 IF P_DEBUG_MODE = 'Y' THEN
300 log_message('In When Others Exception : '||SQLERRM,'Derive_ProjCurr_Attribute');
301 END IF;
302 RAISE;
303 END Derive_ProjCurr_Attribute;
304
305 ---------------------------------------------------------------------------------------------------------
306 -- This function derives the all the conversion attributes from any currency passed as a parameter to
307 -- Project Functional/Project/Acct Currencies. This function returns any of the Currency /Exchange Rate/
308 -- Exchange Rate Date/Exchange Rate Type/Amount values based on the parameter p_ret_atr value.
309 -- Input parameters
310 -- Parameters Type Required Description
311 -- p_project_id NUMBER YES It stores the project_id
312 -- p_task_id NUMBER YES It stores the task_Id
313 -- p_ei_date DATE YES It stores the expenditure item date
314 -- p_from_currency VARCHAR2 NO If not passed, this will be same as the Functional
315 -- Currency
316 -- p_ret_atr VARCHAR2 NO Default value is 'ProjFunc_Rate'
317 -- Valid Values are:
318 -- ProjFunc_Rate
319 -- ProjFunc_Rate_Type
320 -- ProjFunc_Rate_Date
321 -- ProjFunc_Amt
322 -- Proj_Rate
323 -- Proj_Rate_Type
324 -- Proj_Rate_Date
325 -- Proj_Amt
326 -- Proj_Curr
327 -- ProjFunc_Curr
328 -- p_amt NUMBER NO Amount to be converted
329 -- Out parameters
330 ----------------------------------------------------------------------------------------------------------
331 Function Get_Proj_Curr_Amt (
332 P_Project_Id IN Number,
333 P_Task_Id IN Number,
334 P_EI_Date IN Date := SYSDATE,
335 P_FromCurrency IN Varchar2 :='',
336 P_RET_ATR IN Varchar2 :='ProjFunc_Rate',
337 P_Amt IN Number :=0
338 ) Return Varchar2 Is
339 BEGIN
340
341 IF P_DEBUG_MODE = 'Y' THEN
342 log_message('Before Calling Derive_ProjCurr_Attribute '||
343 '[P_Project_Id : '||P_Project_Id||'] '||'[P_Task_Id : '||P_Task_Id||'] '
344 ||'[P_EI_Date : '||P_EI_Date||'] '||'[P_FromCurrency : '||P_FromCurrency||'] '
345 ||'[P_RET_ATR : '||P_RET_ATR||'] '||'[P_Amt : '||P_Amt||'] ',
346 'Get_Proj_Curr_Amt');
347 END IF;
348
349 Derive_ProjCurr_Attribute(P_Project_Id ,
350 P_Task_Id ,
351 P_EI_Date,
352 P_FromCurrency);
353 P_Trans_Amount := P_Amt;
354 ProjFunc_Curr_Amount := P_Trans_Amount * ProjFunc_Cst_Rate;
355 Proj_Curr_Amount := P_Trans_Amount * Proj_Cst_Rate;
356
357 If P_RET_ATR = 'ProjFunc_Rate' Then
358 Return Get_ProjFunc_Rate;
359 END If;
360
361 If P_RET_ATR = 'ProjFunc_Rate_Type' Then
362 Return get_ProjFunc_ratetype;
363 END If;
364
365 If P_RET_ATR = 'ProjFunc_Rate_Date' Then
366 Return get_ProjFunc_rateDate;
367 END If;
368
369 If P_RET_ATR = 'ProjFunc_Amt' Then
370 Return get_ProjFunc_amt;
371 END If;
372
373 If P_RET_ATR = 'Proj_Rate' Then
374 Return Get_Proj_Rate;
375 END If;
376
377 If P_RET_ATR = 'Proj_Rate_Type' Then
378 Return get_Proj_ratetype;
379 END If;
380
381 If P_RET_ATR = 'Proj_Rate_Date' Then
382 Return get_Proj_rateDate;
383 END If;
384
385 If P_RET_ATR = 'Proj_Amt' Then
386 Return get_Proj_amt;
387 END If;
388
389 If P_RET_ATR = 'Proj_Curr' Then
390 Return Get_Proj_curr;
391 END If;
392
393 If P_RET_ATR = 'ProjFunc_Curr' Then
394 Return Get_ProjFunc_curr;
395 END If;
396 EXCEPTION
397 WHEN OTHERS THEN
398 IF P_DEBUG_MODE = 'Y' THEN
399 log_message('In When Others Exception :'||SQLERRM,
400 'Get_Proj_Curr_Amt');
401 END IF;
402 return 0;
403 END Get_Proj_Curr_Amt;
404
405
406 Procedure init_global Is
407 BEGIN
408
409 NULL;
410
411 END;
412
413 ---------------------------------------------------------------------------------------------------------
414 -- This procedure releases PWP Hold, DLV Hold for the supplier invoices passed as a pl/sql table
415 -- Input parameters
416 -- Parameters Type Required Description
417 -- P_Inv_Tbl PL/SQL Tbl YES It stores a list of invoice_id's for which the
418 -- PWP/DLV Hold needs to be released.
419 -- Out parameters
420 -- Parameters Type Required Description
421 -- X_return_status VARCHAR2 YES The return status of the APIs.
422 -- Valid values are:
423 -- S (API completed successfully),
424 -- E (business rule violation error) and
425 -- U(Unexpected error, such as an Oracle error.
426 -- X_msg_count NUMBER YES Holds the number of messages in the global message
427 -- table. Calling programs should use this as the
428 -- basis to fetch all the stored messages.
429 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
430 -- one error/warning message Otherwise the column is
431 -- left blank.
432 ----------------------------------------------------------------------------------------------------------
433 Procedure paap_release_hold (P_Inv_Tbl IN InvoiceId
434 ,p_rel_option IN VARCHAR2 := 'REL_ALL_HOLD'
435 ,X_return_status OUT NOCOPY VARCHAR2
436 ,X_msg_count OUT NOCOPY NUMBER
437 ,X_msg_data OUT NOCOPY VARCHAR2) IS
438
439 l_line_location_id Number(15);
440 l_rcv_transaction_id Number;
441 l_hold_lookup_code Varchar2(25);
442 l_should_have_hold Varchar2(1):='N';
443 l_hold_reason Varchar2(240):='Project Manager Release';
444 l_calling_sequence Varchar2(240);
445
446 -- Cursor c1 is to fetch hold lookup code
447 -- for the invoice being passed if any of the PWP or DLV hold exists.
448 Cursor c1(p_invoice_id Number) Is
449 select hold_lookup_code from ap_holds_all
450 where invoice_id= p_invoice_id
451 and hold_lookup_code in ('Pay When Paid','PO Deliverable','Project Hold') --bug 9525493
452 and release_reason IS NULL
453 and hold_lookup_code not in (select decode(FND_PROFILE.value('PA_PAY_WHEN_PAID'),'N','Pay When Paid','Y','#######','Pay When Paid') from dual);
454
455 Cursor c12(p_invoice_id Number, p_hold_type VARCHAR2) Is
456 select hold_lookup_code from ap_holds_all
457 where invoice_id= p_invoice_id
458 and hold_lookup_code in ('Pay When Paid','PO Deliverable','Project Hold') --bug 9525493
459 and release_reason IS NULL
460 and hold_lookup_code = decode(p_hold_type,
461 'REL_PWP_HOLD','Pay When Paid',
462 'REL_DEL_HOLD','PO Deliverable',
463 'REL_PROJ_HOLD','Project Hold', --bug 9525493
464 null)
465 ;
466
467 l_err_msg Varchar2(4000);
468
469 -- To get the hold reason if any for an Invoice.
470 Cursor C3(P_Invoice_Id Number) Is
471 Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
472 Where invoice_id= P_Invoice_Id
473 And hold_lookup_code In ('Pay When Paid','PO Deliverable')
474 and RELEASE_REASON is null
475 UNION ALL
476 Select hold_lookup_code, hold_reason, 'OTH' HoldType From ap_holds_all
477 Where invoice_id= P_Invoice_Id
478 And hold_lookup_code Not In ('Pay When Paid','PO Deliverable')
479 and RELEASE_REASON is null;
480
481 l_inv_pwp_hold Varchar2(1):='N';
482 l_inv_dlv_hold Varchar2(1):='N';
483 l_inv_hold Varchar2(1):='N';
484 l_hold_reason1 Varchar2(4000);
485 l_hold_applied_yn varchar2(1):= 'N';
486 BEGIN
487 x_return_status := 'S';
488 X_msg_count :=0;
489
490 IF P_DEBUG_MODE = 'Y' THEN
491 log_message('Begin: paap_release_hold ', 'paap_release_hold');
492 END IF;
493
494 IF p_inv_tbl.count > 0 THEN
495
496 FOR Inv_RelHOld_rec in 1..p_inv_tbl.count LOOP
497 --anuragag
498 if(p_rel_option = 'REL_ALL_HOLD') then
499 FOR HoldRec in c1(p_inv_tbl(Inv_RelHOld_rec)) LOOP
500 BEGIN
501 IF P_DEBUG_MODE = 'Y' THEN
502 log_message('Before calling AP_HOLDS_PKG.release_single_hold API '
503 ||'[Invoice_Id : '||p_inv_tbl(Inv_RelHOld_rec)||'] '
504 ||'[hold_lookup_code: '||HoldRec.hold_lookup_code||'] '
505 ||'[l_hold_reason: '||l_hold_reason||'] ', 'paap_release_hold');
506 END IF;
507
508 AP_HOLDS_PKG.release_single_hold
509 (X_invoice_id => p_inv_tbl(Inv_RelHOld_rec),
510 X_hold_lookup_code=> HoldRec.hold_lookup_code,
511 X_release_lookup_code =>l_hold_reason); /*Removed the parameter Held_By for bug 8916025 */
512
513 EXCEPTION
514 WHEN OTHERS THEN
515 l_err_msg:= SQLERRM;
516
517 IF P_DEBUG_MODE = 'Y' THEN
518 log_message('In When Others Exception FORLOOP '||SQLERRM, 'paap_release_hold');
519 END IF;
520
521 Update PA_PWP_AP_INV_HDR Set RELHOLD_REJ_REASON = SubStr(l_err_msg,1,2000)
522 Where invoice_id = p_inv_tbl(Inv_RelHOld_rec);
523
524 x_msg_count := 1;
525 x_return_status :='E';
526 x_msg_data := SQLERRM;
527 END;
528 END LOOP;
529 else
530 FOR HoldRec in c12(p_inv_tbl(Inv_RelHOld_rec),p_rel_option) LOOP
531 BEGIN
532 IF P_DEBUG_MODE = 'Y' THEN
533 log_message('Before calling AP_HOLDS_PKG.release_single_hold API '
534 ||'[Invoice_Id : '||p_inv_tbl(Inv_RelHOld_rec)||'] '
535 ||'[hold_lookup_code: '||HoldRec.hold_lookup_code||'] '
536 ||'[l_hold_reason: '||l_hold_reason||'] ', 'paap_release_hold');
537 END IF;
538
539 AP_HOLDS_PKG.release_single_hold
540 (X_invoice_id => p_inv_tbl(Inv_RelHOld_rec),
541 X_hold_lookup_code=> HoldRec.hold_lookup_code,
542 X_release_lookup_code =>l_hold_reason); /*Removed the parameter Held_By for bug 8916025 */
543
544 EXCEPTION
545 WHEN OTHERS THEN
546 l_err_msg:= SQLERRM;
547
548 IF P_DEBUG_MODE = 'Y' THEN
549 log_message('In When Others Exception FORLOOP '||SQLERRM, 'paap_release_hold');
550 END IF;
551
552 Update PA_PWP_AP_INV_HDR Set RELHOLD_REJ_REASON = SubStr(l_err_msg,1,2000)
553 Where invoice_id = p_inv_tbl(Inv_RelHOld_rec);
554
555 x_msg_count := 1;
556 x_return_status :='E';
557 x_msg_data := SQLERRM;
558 END;
559 END LOOP;
560 end if;
561
562 l_inv_pwp_hold :='N';
563 l_inv_dlv_hold :='N';
564 l_inv_hold :='N';
565 l_hold_reason1 :='';
566 l_hold_applied_yn := 'N';
567
568 FOR INVREC_HOLD IN C3(p_inv_tbl(Inv_RelHOld_rec))
569 LOOP
570 IF l_hold_reason1 IS NULL THEN
571 l_hold_reason1 := INVREC_HOLD.hold_reason||'.';
572 ELSE
573 l_hold_reason1 := l_hold_reason1||'<br>'||INVREC_HOLD.hold_reason||'.';
574 END IF;
575 l_inv_hold := 'Y';
576 If INVREC_HOLD.hold_lookup_code = 'Pay When Paid' Then
577 l_inv_pwp_hold := 'Y';
578 ElsIf INVREC_HOLD.hold_lookup_code = 'PO Deliverable' Then
579 l_inv_dlv_hold := 'Y';
580 ElsIf INVREC_HOLD.hold_lookup_code = 'Project Hold' Then --bug 9525493
581 l_hold_applied_yn := 'Y';
582 End If;
583 END LOOP;
584 IF P_DEBUG_MODE = 'Y' THEN
585 log_message('Setting hold reason to null for Invoice_Id:'
586 ||'[Invoice_Id : '||p_inv_tbl(Inv_RelHOld_rec)||' ]', 'paap_release_hold');
587 END IF;
588
589 Update PA_PWP_AP_INV_HDR Set HOLD_REASON = l_hold_reason1,
590 PWP_HOLD_FLAG = l_inv_pwp_hold,
591 DLV_HOLD_FLAG = l_inv_dlv_hold,
592 HOLD_FLAG = l_inv_hold,
593 HOLD_APPLIED_YN = l_hold_applied_yn
594 Where Invoice_Id = p_inv_tbl(Inv_RelHOld_rec)
595 And RELHOLD_REJ_REASON Is Null;
596
597 END LOOP;
598
599 END IF;
600 COMMIT;
601
602 IF P_DEBUG_MODE = 'Y' THEN
603 log_message('[x_return_status : '||x_return_status||' ]', 'paap_release_hold');
604 END IF;
605
606 IF x_return_status = 'S' THEN
607 X_msg_data := 'PA_INV_HOLD_RELEASE';
608 END IF;
609
610 EXCEPTION
611 WHEN OTHERS THEN
612 IF P_DEBUG_MODE = 'Y' THEN
613 log_message('In When Others Exception :'||SQLERRM, 'paap_release_hold');
614 END IF;
615
616 x_msg_count:=1;
617 x_return_status := 'U';
618 X_msg_data:=SQLERRM;
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END paap_release_hold;
621
622 /*---------------------------------------------------------------------------------------------------------
623 -- This procedure populates pa_pwp_ap_inv_hdr, pa_pwp_ap_inv_dtl tables by processing all the supplier
624 -- invoices pertaining to the project_id being passed. Returns Success/Failure to the calling module.
625 -- Input parameters
626 -- Parameters Type Required Description
627 -- p_project_id NUMBER YES It stores the project_id
628 -- Out parameters
629 -- Parameters Type Required Description
630 -- X_return_status VARCHAR2 YES The return status of the APIs.
631 Valid values are:
632 S (API completed successfully),
633 E (business rule violation error) and
634 U(Unexpected error, such as an Oracle error.
635 -- X_msg_count NUMBER YES Holds the number of messages in the global message
636 table. Calling programs should use this as the
637 basis to fetch all the stored messages.
638 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
639 one error/warning message Otherwise the column is
640 left blank.
641 ----------------------------------------------------------------------------------------------------------*/
642 Procedure Process_SuppInv_Dtls1 (P_Project_Id IN Number
643 ,X_return_status OUT NOCOPY VARCHAR2
644 ,X_msg_count OUT NOCOPY NUMBER
645 ,X_msg_data OUT NOCOPY VARCHAR2) IS
646
647 -- Cursor C1 is to Fetch Supplier wise Invoice details at Invoice Header Level for a project.
648 -- We pickup the payment details from ap_payment_schedules_all and vendor information from
649 -- po_vendors.
650
651 /****
652 Modified below cursor for Bug# 7833675:
653 Removed the discount calculation from here.
654 ****/
655 Cursor c1 Is
656 select apinv.invoice_id Invoice_Id,
657 apinv.invoice_num invoice_num,
658 vend.vendor_id vendor_Id,
659 vend.vendor_name Supplier_name,
660 vend.segment1 Supplier_Num,
661 apinv.Invoice_Date Invoice_Date,
662 P_project_id Project_Id,
663 apinv.invoice_Currency_code invoice_Currency,
664 apinv.payment_Currency_code Payment_Currency,
665 appay.payment_cross_rate Exchange_Rate, /* Bug# 8785535*/
666 (select vendor_site_code from
667 po_vendor_sites_all
668 where vendor_id = apinv.vendor_id
669 and vendor_site_id = apinv.vendor_site_id) Supplier_Site,
670 Invoice_Amount,
671 decode(apinv.payment_Currency_code, apinv.invoice_Currency_code,
672 sum(amount_remaining),
673 sum(amount_remaining)/nvl(appay.payment_cross_rate,1)) UnPaid_Inv_Amt, /* Bug# 8785535*/
674 (decode(apinv.payment_Currency_code,
675 apinv.invoice_Currency_code,
676 sum(gross_amount),
677 sum(gross_amount)/nvl(appay.payment_cross_rate,1)) - /* Bug# 8785535*/
678 decode(apinv.payment_Currency_code,
679 apinv.invoice_Currency_code,
680 sum(amount_remaining),
681 sum(amount_remaining)/nvl(appay.payment_cross_rate,1))) Paid_Inv_Amt, /* Bug# 8785535*/
682 apinv.invoice_Type_Lookup_code invoice_type, /*Added for bug 8293625 */
683 apinv.cancelled_date Cancelled_Date /*Added for bug 8293625 */
684 from ap_invoices_all apinv,
685 ap_payment_schedules_all appay,
686 po_vendors vend
687 where exIsts (select 1 from
688 ap_invoice_dIstributions_all apd
689 where apd.project_id = P_project_Id
690 and apd.posted_flag ='Y'
691 and apinv.invoice_id = apd.invoice_id)
692 and appay.invoice_id(+)=apinv.invoice_id
693 and vend.vendor_id = apinv.vendor_id
694 --and apinv.invoice_amount !=0 -- Bug# 7713608
695 and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
696 and apinv.invoice_id = NVL(G_Invoice_Id,apinv.invoice_id)
697 group by apinv.invoice_id, apinv.invoice_num,
698 vend.vendor_name,apinv.invoice_Date, apinv.invoice_Currency_code,
699 apinv.vendor_id,apinv.vendor_site_id, apinv.Invoice_Amount, vend.vendor_id,
700 apinv.payment_Currency_code,appay.payment_cross_rate,vend.segment1,
701 apinv.invoice_Type_Lookup_code, --Bug# 8717502
702 apinv.cancelled_date --Bug# 8717502
703 order by apinv.invoice_id;
704
705 -- This cursor is to reduce the retainage amount from invoice amount.
706 Cursor C1_Rtng(p_Invoice_Id Number) Is
707 select nvl(sum(apd.amount),0) InvRtngAmount from
708 ap_invoices_all aia,
709 ap_invoice_dIstributions_all apd
710 where aia.invoice_id = p_invoice_id
711 and nvl(aia.Cancelled_Date,Sysdate+1) = (Sysdate+1)
712 and apd.invoice_id = aia.invoice_id
713 and aia.invoice_type_lookup_code <> 'RETAINAGE RELEASE'
714 and apd.posted_flag = 'Y'
715 and apd.line_type_lookup_code = 'RETAINAGE'
716 group by apd.invoice_id;
717
718 -- This cursor is for calculating the invoice paid amount in current period
719 Cursor C_Cur_Per_Inv_Paid(p_invoice_id Number) IS
720 select nvl((decode(apinv.payment_Currency_code,
721 apinv.invoice_Currency_code,
722 sum(gross_amount),
723 sum(gross_amount)/nvl(appay.payment_cross_rate,1)) -
724 decode(apinv.payment_Currency_code,
725 apinv.invoice_Currency_code,
726 sum(amount_remaining),
727 sum(amount_remaining)/nvl(appay.payment_cross_rate,1))),0) Paid_Inv_Amt
728 from ap_invoices_all apinv,
729 ap_payment_schedules_all appay
730 where apinv.invoice_id = p_invoice_id and exIsts (select 1 from
731 ap_invoice_dIstributions_all apd
732 where apd.project_id = P_project_Id
733 and apd.posted_flag ='Y'
734 and apinv.invoice_id = apd.invoice_id)
735 and appay.invoice_id(+)=apinv.invoice_id
736 and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
737 and exists (
738 (SELECT 1
739 FROM ap_invoice_payments_all invpay ,
740 pa_projects_all proj,
741 pa_implementations_all imp,
742 GL_PERIOD_STATUSES glp
743 WHERE proj.project_id = p_project_id
744 and invpay.invoice_id = appay.invoice_id
745 and invpay.payment_num = appay.payment_num
746 and glp.application_id = 101
747 AND glp.adjustment_period_flag = 'N'
748 AND glp.set_of_books_id = imp.set_of_books_id --Bug# 7713608
749 AND glp.closing_status = 'O'
750 and imp.org_id = proj.org_id
751 GROUP BY glp.application_id,glp.adjustment_period_flag,glp.closing_status
752 HAVING max(invpay.accounting_date)
753 BETWEEN MAX(glp.start_date) AND MAX(glp.end_date)
754 )) group by apinv.payment_Currency_code,
755 apinv.invoice_Currency_code, appay.payment_cross_rate;
756
757 -- Cursor C2 is to fetch the invoice details at distribution level.
758 -- This returns total invoice amount for specific project related distributions for an invoice.
759 Cursor C2(p_Invoice_Id Number, p_Project_Id Number) Is
760 select apd.invoice_id,
761 apd.project_id,
762 apd.task_id,
763 apd.Expenditure_Item_Date,
764 pod.po_header_id,
765 --sum(apd.amount) ProjInvAmount,/*Commeneted this line for
766 /*bug#13614733*/
767 sum(nvl(rc_tax.amount,0)) rc_tax,
768 sum(ap_pay_hd.amount) Disc_Taken_On_Invoice /* Bug# 7833675 */
769 from
770 ap_invoice_dIstributions_all apd,
771 ap_invoice_dIstributions_all rc_tax,
772 po_dIstributions_all pod,
773 ap_payment_hist_dists ap_pay_hd
774 where apd.project_Id = p_project_Id
775 and rc_tax.line_type_lookup_code(+) = 'REC_TAX'
776 and rc_tax.tax_recoverable_flag(+) = 'Y'
777 and rc_tax.charge_applicable_to_dist_id(+)=apd.invoice_distribution_id
778 and rc_tax.posted_flag(+) = 'Y'
779 and apd.invoice_id = p_invoice_id
780 and rc_tax.invoice_id(+) = p_invoice_id
781 and apd.posted_flag = 'Y'
782 and pod.po_dIstribution_id(+) = apd.po_distribution_id
783 and nvl(rc_tax.reversal_flag(+),'N') = 'N' /* Additional scenario mentioned by UMA */
784 and ap_pay_hd.invoice_distribution_id(+) = apd.invoice_distribution_id /* Bug# 7833675 */
785 and ap_pay_hd.pay_dist_lookup_code(+) = 'DISCOUNT' /* Bug# 7833675 */
786 -- and apd.line_type_lookup_code <> 'RETAINAGE' /* Bug# 8310848 */
787 group by apd.invoice_id, apd.project_id, apd.task_id, apd.Expenditure_Item_Date,
788 pod.po_header_id
789 order by apd.project_id, apd.task_id, apd.Expenditure_Item_Date, apd.invoice_id,pod.po_header_id;
790
791 -- Cursor C_RTNG_AMT is to fetch the retainage amount at distribution level.
792 -- This returns retainage amount for specific project related distributions for an invoice.
793 -- Group by is added just to capture %NOTFOUND in case if there are no records
794 -- with retainage lookup code in the invoice distributions table.
795 Cursor C_RTNG_AMT(
796 p_Invoice_Id Number,
797 p_Project_Id Number,
798 p_task_id Number,
799 p_Expenditure_Item_Date Date,
800 p_po_header_id Number) Is
801 select sum(apd.amount) ProjRtngAmount,
802 -- sum(retained_amount_remaining) Outstanding_Retained
803 sum(apd1.amount) RtngReleaseAmount
804 from
805 ap_invoices_all aia,
806 ap_invoice_dIstributions_all apd,
807 ap_invoice_dIstributions_all apd1, /* Bug# 8310848 */
808 po_dIstributions_all pod
809 where aia.invoice_id = p_invoice_id
810 and nvl(aia.Cancelled_Date,Sysdate+1) = (Sysdate+1)
811 and aia.invoice_type_lookup_code <> 'RETAINAGE RELEASE'
812 and apd.invoice_id = aia.invoice_id
813 and apd.project_Id = p_project_id
814 and apd.posted_flag = 'Y'
815 and pod.po_dIstribution_id(+) = apd.po_distribution_id
816 and pod.po_header_id(+)=p_po_header_id
817 and apd.line_type_lookup_code = 'RETAINAGE'
818 and apd.task_id = p_task_id
819 and apd.expenditure_item_date = p_expenditure_item_date
820 and apd1.retained_invoice_dist_id(+) = apd.invoice_distribution_id /* Bug# 8310848 */
821 and apd1.project_id(+) = apd.project_id /* Bug# 8310848 */
822 and apd1.task_id(+) = apd.task_id /* Bug# 8310848 */
823 and apd1.expenditure_item_date(+) = apd.expenditure_item_date /* Bug# 8310848 */
824 and apd1.po_distribution_id(+) = apd.po_distribution_id /* Bug# 8310848 */
825 and nvl(apd1.reversal_flag(+),'N') = 'N' /* Bug# 8310848 */
826 and nvl(apd1.posted_flag(+), 'N') = 'Y' /* Bug# 8310848 */
827 group by apd.invoice_id, apd.project_Id, apd.task_id, apd.po_dIstribution_id, apd.expenditure_item_date;
828
829 /* Included the hold reasons other than the PWP and DLV */
830 -- To get the hold reason if any for an Invoice.
831 Cursor C3(P_Invoice_Id Number) Is
832 Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
833 Where invoice_id= P_Invoice_Id
834 And hold_lookup_code In ('Pay When Paid','PO Deliverable')
835 and RELEASE_REASON is null
836 UNION ALL
837 Select hold_lookup_code, hold_reason, 'OTH' HoldType From ap_holds_all
838 Where invoice_id= P_Invoice_Id
839 And hold_lookup_code Not In ('Pay When Paid','PO Deliverable')
840 and RELEASE_REASON is null;
841
842 -- Cursor C4 is to identify the Manually/Automatically linked draft invoices to a supplier invoice.
843 Cursor C4(P_Invoice_Id Number, P_Project_Id Number) Is
844 Select distinct draft_invoice_num,link_type From (
845 Select draft_invoice_num, 'M' link_type From PA_PWP_LINKED_INVOICES PWP
846 Where PWP.AP_INVOICE_ID = p_invoice_id
847 And PWP.PROJECT_ID = p_project_id
848 UNION ALL
849 Select pdii.draft_invoice_num, 'A' From PA_DRAFT_INVOICE_ITEMS PDII ,
850 PA_CUST_REV_DIST_LINES CRDL ,
851 PA_EXPENDITURE_ITEMS EI
852 Where PDII.project_id = crdl.project_id
853 And pdii.draft_invoice_num = crdl.draft_invoice_num
854 And pdii.line_num = crdl.draft_invoice_item_line_num
855 And crdl.expenditure_item_id = ei.expenditure_item_id
856 And ei.system_linkage_function = 'VI'
857 And ei.document_header_id =p_invoice_id
858 And ei.transaction_source like 'AP%'
859 And ei.project_id =p_project_id);
860
861 -- Cursor C5 is to get all the PO number's matched to an Invoice.
862 Cursor C5(p_Invoice_Id Number) IS
863 Select Segment1 PO_NUMBER From po_headers_all
864 Where po_header_id in (Select distinct po_header_id
865 from pa_pwp_ap_inv_dtl where invoice_id = p_Invoice_id);
866
867 --Cursor C6 is to get all the linked invoices for a given Draft_Inv_Num
868 Cursor C6(P_Draft_Inv_Num Number) IS
869 Select distinct Invoice_Id From (
870 Select AP_Invoice_Id Invoice_Id From PA_PWP_LINKED_INVOICES PWP
871 Where PWP.draft_invoice_num = P_Draft_Inv_Num
872 And PWP.PROJECT_ID = p_project_id
873 And ap_Invoice_Id is not null
874 UNION ALL
875 Select ei.document_header_id From PA_DRAFT_INVOICE_ITEMS PDII ,
876 PA_CUST_REV_DIST_LINES CRDL ,
877 PA_EXPENDITURE_ITEMS EI
878 Where PDII.project_id = crdl.project_id
879 And pdii.draft_invoice_num = P_Draft_Inv_Num
880 AND pdii.draft_invoice_num = crdl.draft_invoice_num
881 And pdii.line_num = crdl.draft_invoice_item_line_num
882 And crdl.expenditure_item_id = ei.expenditure_item_id
883 And ei.system_linkage_function = 'VI'
884 And ei.transaction_source like 'AP%'
885 And ei.project_id =p_project_id);
886 /*
887 -- Cursor C_RecTax is to fetch the invoice details at distribution level.
888 -- This returns total invoice amount for specific project related distributions for an invoice.
889 Cursor C_RecTax(p_Invoice_Id Number,
890 p_Project_Id Number,
891 p_task_id Number,
892 p_expenditure_item_date Date, p_po_header_id Number ) Is
893 select
894 sum(rc_tax.amount) ProjInv_RCTax_Amount
895 from
896 ap_invoice_dIstributions_all apd,
897 po_dIstributions_all pod,
898 ap_invoice_dIstributions_all rc_tax
899 where apd.project_Id = p_project_Id
900 and apd.invoice_id = p_invoice_id
901 and apd.task_id = p_task_id
902 and apd.expenditure_item_date = p_expenditure_item_date
903 and apd.posted_flag = 'Y'
904 and pod.po_dIstribution_id(+) = apd.po_distribution_id
905 and pod.po_header_id(+) = p_po_header_id
906 and rc_tax.line_type_lookup_code = 'REC_TAX'
907 and rc_tax.tax_recoverable_flag = 'Y'
908 and rc_tax.charge_applicable_to_dist_id=apd.invoice_distribution_id
909 and rc_tax.posted_flag = 'Y'
910 and apd.line_type_lookup_code <> 'RETAINAGE'
911 group by apd.invoice_id, apd.project_id, apd.task_id, apd.Expenditure_Item_Date,
912 pod.po_header_id;
913 */
914
915 /*Bug#:7834036 sosharma added for additional columns to be displayed in Supplier workbench changes*/
916 -- cursor to get header details gl_date, description,invoice type and exchange information
917 Cursor c_hdr_info(p_invoice_id Number) IS
918 select app.Description,
919 app.GL_date,
920 (select meaning from FND_LOOKUP_VALUES where lookup_type = 'INVOICE TYPE'
921 and view_application_id =200 and language = USERENV('LANG') and lookup_code=app.invoice_type_lookup_code) invoice_type,
922 (select min(psd.due_date) from ap_payment_schedules_all psd where psd.invoice_id=app.invoice_id) earliest_due_date,
923 app.exchange_rate_type,
924 (select user_conversion_type from GL_DAILY_CONVERSION_TYPES where conversion_type=app.exchange_rate_type) exchange_rate_type1,-- Bug 8904838
925 app.exchange_date,
926 app.exchange_rate
927 from ap_invoices_all app where app.invoice_id=p_invoice_id
928 and
929 exists (select 1 from
930 ap_invoice_dIstributions_all apd
931 where apd.project_id = P_project_Id
932 and apd.posted_flag ='Y'
933 and apd.invoice_id=p_invoice_id);
934
935
936 -- Cursor to get prepaid amount
937 Cursor c_prepay_amt(p_invoice_id Number) is
938 select sum(dist.amount) prepaid_amount from ap_prepay_app_dists dist, ap_invoice_dIstributions_all apd
939 where dist.invoice_distribution_id = apd.invoice_distribution_id
940 and apd.project_id=P_Project_Id
941 and apd.invoice_id=p_invoice_id
942 group by apd.invoice_id;
943
944 inv_prepay_amt Number;
945 inv_description VARCHAR2(4000);
946 inv_ex_rate Number;
947 inv_ex_date Date;
948 inv_ex_rtype VARCHAR2(200);
949 inv_ex_rtype1 VARCHAR2(200); -- Bug 8904838
950 inv_gl_date Date;
951 inv_type VARCHAR2(200);
952 inv_due_date Date;
953 /* sosharma end changes*/
954
955 l_PA_PWP_AP_HDR_ID Number;
956
957 ProjFunc_INVOICE_AMT Number;
958 ProjFunc_AMT_PAID Number;
959 ProjFunc_AMT_UNPAID Number;
960 ProjFunc_DISCOUNT_AMT Number;
961 ProjFunc_Retainage Number;
962
963 Proj_INVOICE_AMT Number;
964 Proj_AMT_PAID Number;
965 Proj_AMT_UNPAID Number;
966 Proj_DISCOUNT_AMT Number;
967 Proj_Retainage Number;
968
969 ACCT_INVOICE_AMT Number;
970 ACCT_AMT_PAID Number;
971 ACCT_AMT_UNPAID Number;
972 ACCT_DISCOUNT_AMT Number;
973 Acct_Retainage Number;
974
975 INVOICE_AMOUNT Number;
976 AMOUNT_PAID Number;
977 AMOUNT_UNPAID Number;
978 DISCOUNT_AMOUNT Number;
979 Retainage Number;
980 Outstanding_Retainage NUMBER :=0; --8310848
981 Rtng_Release NUMBER :=0; --8310848
982 Proj_Rc_Tax Number;
983 Acct_Rc_Tax Number;
984 Rc_Tax Number;
985 ProjFunc_Rc_Tax Number;
986
987 l_inv_paid Varchar2(1):='N';
988
989 l_inv_rtng_amt NUMBER; -- Bug#8310848
990
991 l_hold_reason Varchar2(4000):='';
992 l_inv_pwp_hold Varchar2(1):='N';
993 l_inv_dlv_hold Varchar2(1):='N';
994 l_inv_hold Varchar2(1):='N'; /* ForPayment control enhancement */
995
996 l_po_number Varchar2(2000):='';
997 l_draft_inv_number Varchar2(2000):='';
998 l_draft_inv_link_type Varchar2(2000):='';
999
1000 l_status Varchar2(1);
1001 l_stage Number :=0 ;
1002
1003 l_projfunc_cur_per_inv_paid Number :=0;
1004 l_proj_cur_per_inv_paid Number :=0;
1005
1006 ProjFunc_Cur_Per_AMT_PAID Number;
1007 Proj_Cur_Per_AMT_PAID Number;
1008
1009 L_Cur_Per_Inv_Paid Number;
1010
1011 l_inv_amount Number:=0;
1012 l_hold_applied_yn VARCHAR2(1);
1013
1014 l_invrec_amount Number:=0;
1015 BEGIN
1016
1017 l_stage :=0;
1018 l_status :='S';
1019
1020 IF P_DEBUG_MODE = 'Y' THEN
1021 log_message('Begin: Process_SuppInv_Dtls1'||
1022 '[P_Project_Id : '||P_Project_Id||' ]',
1023 'Process_SuppInv_Dtls1');
1024 END IF;
1025
1026 IF P_DEBUG_MODE = 'Y' THEN
1027 log_message('Before deleting data from PA_PWP_AP_INV_HDR and PA_PWP_AP_INV_DTL for'||
1028 '[P_Project_Id : '||P_Project_Id||' ]',
1029 'Process_SuppInv_Dtls1');
1030 END IF;
1031
1032 IF G_Draft_Inv_Num IS NULL THEN
1033 Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id;
1034 Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id;
1035 END IF;
1036 IF G_Draft_Inv_Num IS NOT NULL THEN
1037 OPEN C6(G_Draft_Inv_Num);
1038 END IF;
1039
1040 LOOP
1041 G_Invoice_Id :='';
1042 IF G_Draft_Inv_Num IS NOT NULL AND C6%ISOPEN THEN
1043 FETCH C6 INTO G_Invoice_Id;
1044 IF C6%NOTFOUND THEN
1045 CLOSE C6;
1046 EXIT;
1047 ELSE
1048 Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
1049 Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
1050 END IF;
1051 END IF;
1052
1053 l_stage :=10;
1054 FOR INVREC IN C1 LOOP
1055
1056 IF P_DEBUG_MODE = 'Y' THEN
1057 log_message('In For Loop for INVREC'||
1058 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1059 '[l_stage : '||l_stage||']',
1060 'Process_SuppInv_Dtls1');
1061 END IF;
1062
1063 l_hold_reason :='';
1064 l_inv_paid:='N';
1065 l_inv_hold:='N';
1066 l_inv_pwp_hold:='N';
1067 l_inv_dlv_hold:='N';
1068 l_hold_applied_yn := 'N';
1069 l_draft_inv_number:='';
1070 l_po_number := '';
1071 l_inv_rtng_amt :=0; /* Bug# 8310848 */
1072
1073 OPEN C1_Rtng(INVREC.invoice_id); /* Bug# 8310848 */
1074 FETCH C1_Rtng INTO l_inv_rtng_amt;
1075 CLOSE C1_Rtng;
1076
1077 IF INVREC.Invoice_Amount = 0 THEN
1078 l_inv_amount :=1;
1079 ELSE
1080 l_inv_amount :=INVREC.Invoice_Amount+nvl(l_inv_rtng_amt,0); /* Bug# 8310848 */
1081 END IF;
1082
1083 l_stage :=20;
1084
1085 IF P_DEBUG_MODE = 'Y' THEN
1086 log_message('Before Opening C3 for INVREC_HOLD'||
1087 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1088 '[l_stage : '||l_stage||'] ',
1089 'Process_SuppInv_Dtls1');
1090 END IF;
1091
1092 FOR INVREC_HOLD IN C3(INVREC.INVOICE_ID)
1093 LOOP
1094 IF l_hold_reason IS NULL THEN
1095 l_hold_reason := INVREC_HOLD.hold_reason||'.';
1096 ELSE
1097 l_hold_reason := l_hold_reason||'<br>'||INVREC_HOLD.hold_reason||'.';
1098 END IF;
1099
1100 l_inv_hold := 'Y';
1101 If INVREC_HOLD.hold_lookup_code = 'Pay When Paid' Then
1102 l_inv_pwp_hold := 'Y';
1103 ElsIf INVREC_HOLD.hold_lookup_code = 'PO Deliverable' Then
1104 l_inv_dlv_hold := 'Y';
1105 ElsIf INVREC_HOLD.hold_lookup_code = 'Project Hold' Then --bug 9525493
1106 l_hold_applied_yn := 'Y';
1107 End If;
1108 END LOOP;
1109
1110 IF P_DEBUG_MODE = 'Y' THEN
1111 log_message('INVREC_HOLD '||
1112 '[l_hold_reason : '||l_hold_reason||'] '||
1113 '[l_inv_pwp_hold : '||l_inv_pwp_hold||'] '||
1114 '[l_inv_dlv_hold : '||l_inv_dlv_hold||'] '||
1115 '[l_inv_hold : '||l_inv_hold||'] ',
1116 'Process_SuppInv_Dtls1');
1117 END IF;
1118
1119 IF P_DEBUG_MODE = 'Y' THEN
1120 log_message('Before Opening C4 for DRAFTINV_REC ',
1121 'Process_SuppInv_Dtls1');
1122 END IF;
1123
1124 FOR DRAFTINV_REC IN C4(INVREC.INVOICE_ID,INVREC.PROJECT_ID) LOOP
1125 IF l_draft_inv_number IS NULL THEN
1126 l_draft_inv_number:=DRAFTINV_REC.draft_invoice_num;
1127 l_draft_inv_link_type :=DRAFTINV_REC.link_type;
1128 ELSE
1129 l_draft_inv_number:=l_draft_inv_number||','||DRAFTINV_REC.draft_invoice_num;
1130 l_draft_inv_link_type := l_draft_inv_link_type||','||DRAFTINV_REC.link_type;
1131 END IF;
1132 END LOOP;
1133
1134 IF P_DEBUG_MODE = 'Y' THEN
1135 log_message('After Opening C4 for DRAFTINV_REC '||
1136 '[l_draft_inv_number : '||l_draft_inv_number||'] '||
1137 '[l_draft_inv_link_type : '||l_draft_inv_link_type||'] ',
1138 'Process_SuppInv_Dtls1');
1139 END IF;
1140
1141 l_stage :=30;
1142 IF P_DEBUG_MODE = 'Y' THEN
1143 log_message('Before calculating invoice paid amount in current period ',
1144 'Process_SuppInv_Dtls1');
1145 END IF;
1146 L_Cur_Per_Inv_Paid :=0; /* Initialization is done for Bug# 8203817 */
1147
1148 IF nvl(INVREC.Paid_Inv_Amt,0) > 0 THEN
1149 OPEN C_Cur_Per_Inv_Paid(INVREC.INVOICE_ID);
1150 FETCH C_Cur_Per_Inv_Paid Into L_Cur_Per_Inv_Paid;
1151 CLOSE C_Cur_Per_Inv_Paid;
1152 END IF;
1153
1154 IF P_DEBUG_MODE = 'Y' THEN
1155 log_message('After calculating invoice paid amount in current period '||
1156 '[L_Cur_Per_Inv_Paid : '||L_Cur_Per_Inv_Paid||'] ',
1157 'Process_SuppInv_Dtls1');
1158 END IF;
1159
1160 IF P_DEBUG_MODE = 'Y' THEN
1161 log_message('Deriving paid flag for invoice '||
1162 '[l_stage : '||l_stage||'] ',
1163 'Process_SuppInv_Dtls1');
1164 END IF;
1165
1166 /*****
1167 1. If there is unpaid amount and paid amount,
1168 then it is Partial Case.
1169 2. If there is unpaid amount and no paid amount,
1170 then it is Not Paid Case.
1171 3. If there is no unpaid amount and paid amount,
1172 then it is Fully Paid case.
1173 4. If there is no unpaid amount and no paid amount,
1174 then it is also Not Paid case.
1175 *****/
1176
1177 /* Added for bug 8293625 */
1178 if(INVREC.Invoice_Type = 'CREDIT' OR INVREC.Invoice_Type = 'DEBIT' OR nvl(INVREC.Cancelled_Date,Sysdate+1) <> Sysdate+1) Then
1179 l_inv_paid := 'G';
1180
1181 Else
1182
1183 If nvl(INVREC.Unpaid_Inv_Amt,0) <> 0 Then
1184 If nvl(INVREC.Paid_Inv_Amt,0) <> 0 Then
1185 l_inv_paid := 'P';
1186 Else
1187 l_inv_paid := 'N';
1188 End If;
1189 ElsIf nvl(INVREC.Paid_Inv_Amt,0) <> 0 Then /* For Bug# 7831141 */
1190 l_inv_paid := 'Y';
1191 Else
1192 l_inv_paid := 'N';
1193 End If;
1194
1195 IF P_DEBUG_MODE = 'Y' THEN
1196 log_message('After deriving paid flag for invoice '||
1197 '[l_inv_paid : '||l_inv_paid||'] ',
1198 'Process_SuppInv_Dtls1');
1199 END IF;
1200
1201 End if;
1202
1203 l_stage :=40;
1204
1205 /* Bug#:7834036 sosharma added for additional columns
1206 to be displayed in Supplier workbench changes
1207 */
1208
1209 FOR inv_dtls_rec IN c_hdr_info(INVREC.INVOICE_ID) LOOP
1210
1211 inv_description:=inv_dtls_rec.description;
1212 inv_ex_rate:=inv_dtls_rec.exchange_rate;
1213 inv_ex_date:=inv_dtls_rec.exchange_date;
1214 inv_ex_rtype:=inv_dtls_rec.exchange_rate_type;
1215 inv_ex_rtype1:=inv_dtls_rec.exchange_rate_type1;-- Bug 8904838
1216 inv_gl_date:=inv_dtls_rec.gl_date;
1217 inv_type :=inv_dtls_rec.invoice_type;
1218 inv_due_date :=inv_dtls_rec.earliest_due_date;
1219
1220 END LOOP;
1221
1222 /* Bug# Added this for bug# 8849692 */
1223 /* Bug#8897745 */
1224 IF inv_ex_rate IS NOT NULL THEN
1225 ACCT_Cst_Rate_Date :=inv_ex_date;
1226 ACCT_Cst_Rate_Type :=inv_ex_rtype;
1227 ACCT_CST_RATE :=inv_ex_rate;
1228 ELSE
1229 ACCT_Cst_Rate_Date :='';
1230 ACCT_Cst_Rate_Type :='';
1231 ACCT_CST_RATE :='';
1232 END IF;
1233
1234 FOR inv_prepay_rec IN c_prepay_amt(INVREC.INVOICE_ID) LOOP
1235
1236 inv_prepay_amt:=inv_prepay_rec.prepaid_amount;
1237 END LOOP;
1238 /* sosharma end changes*/
1239
1240 -- L_PA_PWP_AP_HDR_ID:= PA_PWP_AP_INV_HDR_S.nextval; Changed for backward compatibility : Bug 7666516
1241 SELECT PA_PWP_AP_INV_HDR_S.nextval
1242 INTO L_PA_PWP_AP_HDR_ID
1243 FROM dual;
1244
1245 IF P_DEBUG_MODE = 'Y' THEN
1246 log_message('Before inserting record in PA_PWP_AP_INV_HDR'||
1247 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1248 '[l_stage : '||l_stage||'] '||
1249 '[L_PA_PWP_AP_HDR_ID : '||L_PA_PWP_AP_HDR_ID||'] ',
1250 'Process_SuppInv_Dtls1');
1251 END IF;
1252
1253 Insert Into PA_PWP_AP_INV_HDR(PA_PWP_AP_HDR_ID
1254 ,PROJECT_ID
1255 ,INVOICE_ID
1256 ,INVOICE_NUM
1257 ,vendOR_ID
1258 ,SUPPLIER_NUM
1259 ,SUPPLIER_NAME
1260 ,SUPPLIER_SITE_CODE
1261 ,INVOICE_Date
1262 ,INVOICE_AMOUNT
1263 ,INVOICE_Currency
1264 ,HOLD_REASON
1265 ,PWP_HOLD_FLAG
1266 ,DLV_HOLD_FLAG
1267 ,HOLD_FLAG
1268 ,PAYMENT_STATUS
1269 ,LINKED_DRAFT_INVOICE_NUM
1270 ,LINKED_DRFAT_INV_TYPE
1271 ,hold_applied_yn
1272 ,description
1273 ,exchange_rate
1274 ,exchange_date
1275 ,exchange_rate_type
1276 ,gl_date
1277 ,invoice_type
1278 ,earliest_pay_due_date
1279 ,prepaid_amount
1280 ) Values(
1281 l_PA_PWP_AP_HDR_ID
1282 ,INVREC.PROJECT_ID
1283 ,INVREC.INVOICE_ID
1284 ,INVREC.INVOICE_NUM
1285 ,INVREC.vendOR_ID
1286 ,INVREC.Supplier_Num
1287 ,INVREC.SUPPLIER_NAME
1288 ,INVREC.SUPPLIER_SITE
1289 ,INVREC.INVOICE_Date
1290 ,INVREC.INVOICE_AMOUNT
1291 ,INVREC.INVOICE_Currency
1292 ,l_hold_reason
1293 ,l_inv_pwp_hold
1294 ,l_inv_dlv_hold
1295 ,l_inv_hold
1296 ,l_inv_paid
1297 ,l_draft_inv_number
1298 ,l_draft_inv_link_type
1299 ,l_hold_applied_yn
1300 , inv_description
1301 ,inv_ex_rate
1302 ,inv_ex_date
1303 ,inv_ex_rtype1
1304 ,inv_gl_date
1305 ,inv_type
1306 ,inv_due_date
1307 ,inv_prepay_amt
1308 );
1309
1310 l_stage :=50;
1311
1312 IF P_DEBUG_MODE = 'Y' THEN
1313 log_message('Before opening the loop for inserting record in PA_PWP_AP_INV_DTL '||
1314 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1315 '[l_stage : '||l_stage||'] ',
1316 'Process_SuppInv_Dtls1');
1317 END IF;
1318
1319 FOR INVDTL IN C2(INVREC.INVOICE_ID, INVREC.PROJECT_ID) LOOP
1320
1321 l_stage :=60;
1322
1323 IF P_DEBUG_MODE = 'Y' THEN
1324 log_message('Before calling Derive_ProjCurr_Attribute '||'[l_stage : '||l_stage||'] ',
1325 'Process_SuppInv_Dtls1');
1326 END IF;
1327
1328 Derive_ProjCurr_Attribute(p_Project_id,
1329 INVDTL.tasK_id,
1330 INVDTL.expenditure_item_date,
1331 INVREC.Invoice_Currency);
1332
1333 IF P_DEBUG_MODE = 'Y' THEN
1334 log_message('Before Calculating the retainage amount '||'[l_stage : '||l_stage||'] ',
1335 'Process_SuppInv_Dtls1');
1336 END IF;
1337
1338 OPEN C_RTNG_AMT(
1339 INVREC.Invoice_Id,
1340 p_Project_Id,
1341 INVDTL.task_id,
1342 INVDTL.Expenditure_Item_Date,
1343 INVDTL.po_header_id);
1344
1345 FETCH C_RTNG_AMT INTO Retainage, Rtng_Release; -- Added for 8310848
1346 IF C_RTNG_AMT%NOTFOUND THEN
1347 Retainage := 0;
1348 Rtng_Release :=0; -- Added for 8310848
1349 END IF;
1350 CLOSE C_RTNG_AMT;
1351
1352 Outstanding_Retainage := abs(nvl(Retainage,0)) - nvl(Rtng_Release,0); /* Bug# 8310848 */
1353
1354 /* Rtng_Release := abs(Retainage) - Outstanding_Retainage;*/ -- Added for 8310848
1355
1356 /*
1357 OPEN C_RecTax(
1358 INVREC.Invoice_Id,
1359 p_Project_Id,
1360 INVDTL.task_id,
1361 INVDTL.Expenditure_Item_Date,
1362 INVDTL.po_header_id);
1363
1364 FETCH C_RecTax INTO Rc_Tax;
1365 IF C_RecTax%NOTFOUND THEN
1366 Rc_Tax := 0;
1367 END IF;
1368 CLOSE C_RecTax;*/
1369 Rc_Tax := INVDTL.rc_tax;
1370
1371 /*Start changes for bug#13614733 */
1372 l_invrec_amount := 0;
1373
1374 begin
1375
1376 IF (INVDTL.po_header_id IS NOT NULL ) THEN
1377 --{
1378
1379 select sum(apd.amount) ProjInvAmount into l_invrec_amount
1380 from ap_invoice_dIstributions_all apd,
1381 ap_invoice_dIstributions_all rc_tax,
1382 po_dIstributions_all pod
1383 where
1384 apd.project_Id = INVDTL.project_Id
1385 and rc_tax.line_type_lookup_code(+) = 'REC_TAX'
1386 and rc_tax.tax_recoverable_flag(+) = 'Y'
1387 and rc_tax.charge_applicable_to_dist_id(+)=apd.invoice_distribution_id
1388 and pod.po_dIstribution_id(+) = apd.po_distribution_id
1389 and rc_tax.posted_flag(+) = 'Y'
1390 and apd.invoice_id = INVDTL.invoice_id
1391 and apd.task_id = INVDTL.task_id
1392 and apd.Expenditure_Item_Date = INVDTL.Expenditure_Item_Date
1393 and pod.po_header_id = INVDTL.po_header_id
1394 and rc_tax.invoice_id(+) = INVDTL.invoice_id
1395 and apd.posted_flag = 'Y'
1396 and pod.po_dIstribution_id(+) = apd.po_distribution_id
1397 and nvl(rc_tax.reversal_flag(+),'N') = 'N';
1398
1399 --}
1400 ELSE
1401 --{
1402
1403 select sum(apd.amount) ProjInvAmount into l_invrec_amount
1404 from ap_invoice_dIstributions_all apd,
1405 ap_invoice_dIstributions_all rc_tax,
1406 po_dIstributions_all pod
1407 where
1408 apd.project_Id = INVDTL.project_Id
1409 and rc_tax.line_type_lookup_code(+) = 'REC_TAX'
1410 and rc_tax.tax_recoverable_flag(+) = 'Y'
1411 and
1412 rc_tax.charge_applicable_to_dist_id(+)=apd.invoice_distribution_id
1413 and pod.po_dIstribution_id(+) = apd.po_distribution_id
1414 and rc_tax.posted_flag(+) = 'Y'
1415 and apd.invoice_id = INVDTL.invoice_id
1416 and apd.task_id = INVDTL.task_id
1417 and apd.Expenditure_Item_Date = INVDTL.Expenditure_Item_Date
1418 and rc_tax.invoice_id(+) = INVDTL.invoice_id
1419 and apd.po_distribution_id is null
1420 and apd.posted_flag = 'Y'
1421 and pod.po_dIstribution_id(+) = apd.po_distribution_id
1422 and nvl(rc_tax.reversal_flag(+),'N') = 'N';
1423
1424 --}
1425 END IF;
1426
1427 exception
1428 when others then
1429 l_invrec_amount := 0;
1430
1431 end;
1432
1433 l_invrec_amount := l_invrec_amount + INVDTL.rc_tax;
1434 -- l_invrec_amount := (INVDTL.ProjInvAmount+INVDTL.rc_tax);
1435
1436 /*End changes for bug#13614733 */
1437
1438
1439 IF P_DEBUG_MODE = 'Y' THEN
1440 log_message('Before deriving all the amount columns',
1441 'Process_SuppInv_Dtls1');
1442 END IF;
1443
1444 /* Bug# 8310848:
1445 */
1446 ProjFunc_INVOICE_AMT :=l_invrec_amount* ProjFunc_CST_RATE;
1447
1448 ProjFunc_AMT_PAID :=(l_invrec_amount*INVREC.Paid_Inv_Amt/l_inv_amount)
1449 * ProjFunc_CST_RATE;
1450 ProjFunc_AMT_UNPAID :=(l_invrec_amount*INVREC.UnPaid_Inv_Amt/l_inv_amount)
1451 * ProjFunc_CST_RATE;
1452 IF nvl(L_Cur_Per_Inv_Paid,0) >0 THEN
1453 ProjFunc_Cur_Per_AMT_PAID := (l_invrec_amount*L_Cur_Per_Inv_Paid/l_inv_amount)
1454 * ProjFunc_CST_RATE;
1455 ELSE
1456 ProjFunc_Cur_Per_AMT_PAID := 0;
1457 END IF;
1458
1459 /* Modified the discount calculation for Bug# 7833675 as below.
1460 ProjFunc_DISCOUNT_AMT :=(INVDTL.ProjInvAmount*INVREC.DIsc_Taken_On_Inv/l_inv_amount)
1461 * ProjFunc_CST_RATE;
1462 */
1463
1464 ProjFunc_DISCOUNT_AMT :=INVDTL.DIsc_Taken_On_Invoice* ProjFunc_CST_RATE;
1465
1466 ProjFunc_Retainage := Outstanding_Retainage *ProjFunc_CST_RATE;
1467 ProjFunc_Rc_Tax := Rc_Tax * ProjFunc_CST_RATE;
1468
1469 Proj_INVOICE_AMT :=l_invrec_amount*Proj_CST_RATE;
1470 Proj_AMT_PAID :=(l_invrec_amount*INVREC.Paid_Inv_Amt/l_inv_amount)
1471 *Proj_CST_RATE;
1472 Proj_AMT_UNPAID :=(l_invrec_amount*INVREC.UnPaid_Inv_Amt/l_inv_amount)
1473 *Proj_CST_RATE;
1474
1475 IF nvl(L_Cur_Per_Inv_Paid,0) >0 THEN
1476 Proj_Cur_Per_AMT_PAID := (l_invrec_amount*L_Cur_Per_Inv_Paid/l_inv_amount)
1477 * Proj_CST_RATE;
1478 ELSE
1479 Proj_Cur_Per_AMT_PAID := 0;
1480 END IF;
1481
1482 /* Modified the discount calculation for Bug# 7833675 as below.
1483 Proj_DISCOUNT_AMT :=(INVDTL.ProjInvAmount*INVREC.DIsc_Taken_On_Inv/l_inv_amount)
1484 *Proj_CST_RATE;
1485 */
1486 Proj_DISCOUNT_AMT :=INVDTL.DIsc_Taken_On_Invoice*Proj_CST_RATE;
1487
1488 Proj_Retainage := Outstanding_Retainage * Proj_CST_RATE;
1489
1490 Proj_Rc_Tax := Rc_Tax * Proj_CST_RATE;
1491 ACCT_INVOICE_AMT :=l_invrec_amount*ACCT_CST_RATE;
1492 ACCT_AMT_PAID :=(l_invrec_amount*INVREC.Paid_Inv_Amt/l_inv_amount)
1493 *ACCT_CST_RATE;
1494 ACCT_AMT_UNPAID :=(l_invrec_amount*INVREC.UnPaid_Inv_Amt/l_inv_amount)
1495 *ACCT_CST_RATE;
1496
1497 /* Modified the discount calculation for Bug# 7833675 as below.
1498 ACCT_DISCOUNT_AMT :=(INVDTL.ProjInvAmount*INVREC.DIsc_Taken_On_Inv/l_inv_amount)
1499 *ACCT_CST_RATE;
1500 */
1501
1502 ACCT_DISCOUNT_AMT :=(INVDTL.Disc_Taken_On_Invoice)*ACCT_CST_RATE;
1503
1504 Acct_Retainage := Outstanding_Retainage *ACCT_CST_RATE;
1505
1506 Acct_Rc_Tax := Rc_Tax * Acct_CST_RATE;
1507 INVOICE_AMOUNT :=l_invrec_amount;
1508 AMOUNT_PAID :=(l_invrec_amount*INVREC.Paid_Inv_Amt/l_inv_amount);
1509 AMOUNT_UNPAID :=(l_invrec_amount*INVREC.UnPaid_Inv_Amt/l_inv_amount);
1510
1511 /* Modified the discount calculation for Bug# 7833675 as below.
1512 DISCOUNT_AMOUNT :=(INVDTL.DIsc_Taken_On_Invoice/l_inv_amount);
1513 */
1514 DISCOUNT_AMOUNT :=INVDTL.DIsc_Taken_On_Invoice;
1515
1516 IF P_DEBUG_MODE = 'Y' THEN
1517 log_message('After deriving all amount columns '||
1518 '[ProjFunc_INVOICE_AMT : '||ProjFunc_INVOICE_AMT||'] '||
1519 '[ProjFunc_AMT_PAID : '||ProjFunc_AMT_PAID||'] '||
1520 '[ProjFunc_AMT_UNPAID : '||ProjFunc_AMT_UNPAID||'] '||
1521 '[ProjFunc_Cur_Per_AMT_PAID : '||ProjFunc_Cur_Per_AMT_PAID||'] '||
1522 '[ProjFunc_DISCOUNT_AMT : '||ProjFunc_DISCOUNT_AMT||'] '||
1523 '[Proj_INVOICE_AMT : '||Proj_INVOICE_AMT||'] '||
1524 '[Proj_AMT_PAID : '||Proj_AMT_PAID||'] '||
1525 '[Proj_AMT_UNPAID : '||Proj_AMT_UNPAID||'] '||
1526 '[Proj_Cur_Per_AMT_PAID : '||Proj_Cur_Per_AMT_PAID||'] '||
1527 '[Proj_DISCOUNT_AMT : '||Proj_DISCOUNT_AMT||'] '||
1528 '[ACCT_INVOICE_AMT : '||ACCT_INVOICE_AMT||'] '||
1529 '[ACCT_AMT_PAID : '||ACCT_AMT_PAID||'] '||
1530 '[ACCT_AMT_UNPAID : '||ACCT_AMT_UNPAID||'] '||
1531 '[ACCT_DISCOUNT_AMT : '||ACCT_DISCOUNT_AMT||'] '||
1532 '[INVOICE_AMOUNT : '||INVOICE_AMOUNT||'] '||
1533 '[AMOUNT_PAID : '||AMOUNT_PAID||'] '||
1534 '[AMOUNT_UNPAID : '||AMOUNT_UNPAID||'] '||
1535 '[DISCOUNT_AMOUNT : '||DISCOUNT_AMOUNT||'] ',
1536 'Process_SuppInv_Dtls1');
1537 END IF;
1538
1539 l_stage :=70;
1540
1541 IF P_DEBUG_MODE = 'Y' THEN
1542 log_message('Before inserting record in PA_PWP_AP_INV_DTL '||
1543 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1544 '[l_stage : '||l_stage||'] ',
1545 'Process_SuppInv_Dtls1');
1546 END IF;
1547
1548 Insert into PA_PWP_AP_INV_DTL(PA_PWP_AP_HDR_ID
1549 ,INVOICE_ID
1550 ,PROJECT_ID
1551 ,TASK_ID
1552 ,Expenditure_Item_Date
1553 ,PO_HEADER_ID
1554 ,INVOICE_Currency
1555 ,PROJINV_TOT_AMOUNT
1556 ,AMOUNT_PAID
1557 ,AMOUNT_UNPAID
1558 ,RETAINED_AMOUNT
1559 ,DISCOUNT_AMOUNT
1560 ,ProjFunc_Currency_CODE
1561 ,ProjFunc_INVOICE_AMOUNT
1562 ,ProjFunc_INV_PAID_AMOUNT
1563 ,ProjFunc_INV_UNPAID_AMOUNT
1564 ,ProjFunc_RETAINED_AMOUNT
1565 ,ProjFunc_DISCOUNT_AMOUNT
1566 ,Proj_Currency_CODE
1567 ,Proj_INVOICE_AMOUNT
1568 ,Proj_INV_PAID_AMOUNT
1569 ,Proj_INV_UNPAID_AMOUNT
1570 ,Proj_RETAINED_AMOUNT
1571 ,Proj_DISCOUNT_AMOUNT
1572 ,ACCT_Currency_CODE
1573 ,ACCT_INVOICE_AMOUNT
1574 ,ACCT_INV_PAID_AMOUNT
1575 ,ACCT_INV_UNPAID_AMOUNT
1576 ,ACCT_RETAINED_AMOUNT
1577 ,ACCT_DISCOUNT_AMOUNT
1578 ,PROJFUNC_CUR_PER_INV_PAID
1579 ,PROJ_CUR_PER_INV_PAID
1580 ,PROJFUNC_RTAX_AMOUNT
1581 ,PROJ_RTAX_AMOUNT
1582 ,ACCT_RTAX_AMOUNT
1583 ,RTAX_AMOUNT)
1584 VALUES (
1585 l_PA_PWP_AP_HDR_ID
1586 ,INVREC.INVOICE_ID
1587 ,INVREC.PROJECT_ID
1588 ,INVDTL.TASK_ID
1589 ,INVDTL.Expenditure_Item_Date
1590 ,INVDTL.PO_HEADER_ID
1591 ,INVREC.INVOICE_Currency
1592 ,INVOICE_AMOUNT
1593 ,AMOUNT_PAID
1594 ,AMOUNT_UNPAID
1595 ,Outstanding_Retainage -- 8310848
1596 ,DISCOUNT_AMOUNT
1597 ,ProjFunc_Currency
1598 ,ProjFunc_INVOICE_AMT
1599 ,ProjFunc_AMT_PAID
1600 ,ProjFunc_AMT_UNPAID
1601 ,ProjFunc_retainage
1602 ,ProjFunc_DISCOUNT_AMT
1603 ,Proj_Currency
1604 ,Proj_INVOICE_AMT
1605 ,Proj_AMT_PAID
1606 ,Proj_AMT_UNPAID
1607 ,Proj_Retainage
1608 ,Proj_DISCOUNT_AMT
1609 ,PA_CURR_CODE
1610 ,ACCT_INVOICE_AMT
1611 ,ACCT_AMT_PAID
1612 ,ACCT_AMT_UNPAID
1613 ,Acct_retainage
1614 ,ACCT_DISCOUNT_AMT
1615 ,ProjFunc_Cur_Per_AMT_PAID
1616 ,Proj_Cur_Per_AMT_PAID
1617 ,ProjFunc_Rc_Tax
1618 ,Proj_Rc_Tax
1619 ,Acct_Rc_Tax
1620 ,Rc_Tax);
1621
1622 END LOOP;
1623 l_stage :=80;
1624
1625 IF P_DEBUG_MODE = 'Y' THEN
1626 log_message('Before deriving PO Number '||
1627 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1628 '[l_stage : '||l_stage||'] ',
1629 'Process_SuppInv_Dtls1');
1630 END IF;
1631
1632 FOR PO_NUM IN C5(INVREC.INVOICE_ID) LOOP
1633 If l_po_number Is NULL Then
1634 l_po_number := l_po_number||nvl(PO_NUM.po_number,'');
1635 Else
1636 l_po_number := l_po_number||','||nvl(PO_NUM.po_number,'');
1637 End If;
1638 END LOOP;
1639
1640 IF P_DEBUG_MODE = 'Y' THEN
1641 log_message('After deriving PO Number '||
1642 '[l_po_number : '||l_po_number||'] '||
1643 '[l_stage : '||l_stage||'] ',
1644 'Process_SuppInv_Dtls1');
1645 END IF;
1646
1647 Update PA_PWP_AP_INV_HDR
1648 Set po_number = l_po_number
1649 Where pa_pwp_ap_hdr_id = l_pa_pwp_ap_hdr_id;
1650
1651 END LOOP;
1652
1653 IF G_Draft_Inv_Num IS NULL THEN
1654 EXIT;
1655 END IF;
1656
1657 END LOOP;
1658
1659 COMMIT;
1660 IF P_DEBUG_MODE = 'Y' THEN
1661 log_message('End of the procedure '||
1662 '[l_status : '||l_status||'] ',
1663 'Process_SuppInv_Dtls1');
1664 END IF;
1665
1666 X_return_status := l_status;
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 IF C1%ISOPEN THEN
1670 CLOSE C1;
1671 END IF;
1672
1673 IF C2%ISOPEN THEN
1674 CLOSE C2;
1675 END IF;
1676
1677 IF C3%ISOPEN THEN
1678 CLOSE C3;
1679 END IF;
1680
1681 IF C4%ISOPEN THEN
1682 CLOSE C4;
1683 END IF;
1684
1685 IF C5%ISOPEN THEN
1686 CLOSE C5;
1687 END IF;
1688
1689 IF C6%ISOPEN THEN
1690 CLOSE C6;
1691 END IF;
1692
1693 IF C_Cur_Per_Inv_Paid%ISOPEN THEN
1694 CLOSE C_Cur_Per_Inv_Paid;
1695 END IF;
1696
1697 IF C_RTNG_AMT%ISOPEN THEN
1698 CLOSE C_RTNG_AMT;
1699 END IF;
1700
1701 IF P_DEBUG_MODE = 'Y' THEN
1702 log_message('In When Others Exception : '||SQLERRM,
1703 'Process_SuppInv_Dtls1');
1704 END IF;
1705 X_return_status := 'U';
1706 X_msg_data := SQLERRM;
1707 END Process_SuppInv_Dtls1;
1708
1709 ---------------------------------------------------------------------------------------------------------
1710 -- This procedure in turn calls Process_SuppInv_Dtls1, to populate pa_pwp_ap_inv_hdr, pa_pwp_ap_inv_dtl
1711 -- tables by processing all the supplier invoices pertaining to the project_id being passed.
1712 -- This is being called from the Summary Page of Subcontractor tab.
1713 -- Input parameters
1714 -- Parameters Type Required Description
1715 -- p_project_id NUMBER YES It stores the project_id
1716 -- Out parameters
1717 ----------------------------------------------------------------------------------------------------------
1718 Procedure Process_SuppInv_Dtls (P_Project_Id IN Number, P_Draft_Inv_Num IN Number :='') IS
1719 X_return_status VARCHAR2(2000);
1720 X_msg_count NUMBER;
1721 X_msg_data VARCHAR2(4000);
1722 BEGIN
1723
1724 IF P_Draft_Inv_Num IS NOT NULL THEN
1725 IF NVL(G_Draft_Inv_Num,-99) <> NVL(P_Draft_Inv_Num,-99) THEN
1726 G_Draft_Inv_Num:= P_Draft_Inv_Num;
1727 END IF;
1728 ELSE
1729 G_Draft_Inv_Num:='';
1730 END IF;
1731
1732 IF P_DEBUG_MODE = 'Y' THEN
1733 log_message('Before calling Process_SuppInv_Dtls1 ',
1734 'Process_SuppInv_Dtls');
1735 END IF;
1736
1737 Process_SuppInv_Dtls1 (P_Project_Id
1738 ,X_return_status
1739 ,X_msg_count
1740 ,X_msg_data);
1741 IF P_DEBUG_MODE = 'Y' THEN
1742 log_message('After calling Process_SuppInv_Dtls1 ',
1743 'Process_SuppInv_Dtls');
1744 END IF;
1745
1746 END Process_SuppInv_Dtls;
1747 /*--------------------------------------------------------------------------------------------------------
1748 -- This procedure applies Project Hold for the supplier invoices passed as a pl/sql table
1749 -- Input parameters
1750 -- Parameters Type Required Description
1751 -- P_Inv_Tbl PL/SQL Tbl YES It stores a list of invoice_id's for which
1752 -- the PWP/DLV Hold needs to be released.
1753 -- Out parameters
1754 -- Parameters Type Required Description
1755 -- X_return_status VARCHAR2 YES The return status of the APIs.
1756 Valid values are:
1757 S (API completed successfully),
1758 E (business rule violation error) and
1759 U(Unexpected error, such as an Oracle error.
1760 -- X_msg_count NUMBER YES Holds the number of messages in the global message
1761 table. Calling programs should use this as the
1762 basis to fetch all the stored messages.
1763 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
1764 one error/warning message Otherwise the column is
1765 left blank.
1766 ----------------------------------------------------------------------------------------------------------*/
1767 Procedure Paap_Apply_Hold (P_Inv_Tbl IN InvoiceId
1768 ,X_return_status OUT NOCOPY VARCHAR2
1769 ,X_msg_count OUT NOCOPY NUMBER
1770 ,X_msg_data OUT NOCOPY VARCHAR2) IS
1771 -- To get the hold reason if any for an Invoice.
1772 Cursor C3(P_Invoice_Id Number) Is
1773 Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
1774 Where invoice_id= P_Invoice_Id
1775 And hold_lookup_code In ('Pay When Paid','PO Deliverable')
1776 and RELEASE_REASON is null
1777 UNION ALL
1778 Select hold_lookup_code, hold_reason, 'OTH' HoldType From ap_holds_all
1779 Where invoice_id= P_Invoice_Id
1780 And hold_lookup_code Not In ('Pay When Paid','PO Deliverable')
1781 and RELEASE_REASON is null;
1782
1783 l_inv_pwp_hold Varchar2(1):='N';
1784 l_inv_dlv_hold Varchar2(1):='N';
1785 l_inv_hold Varchar2(1):='N';
1786 l_hold_reason1 Varchar2(4000);
1787 l_hold_applied_yn varchar2(1):= 'N';
1788 Begin
1789 X_return_status :='S';
1790 FOR I in 1..P_Inv_Tbl.COUNT LOOP
1791 AP_HOLDS_PKG.insert_single_hold (X_invoice_id=>P_Inv_Tbl(i)
1792 ,X_hold_lookup_code=>'Project Hold' -- bug 9525493
1793 ,X_hold_reason=>'Project Managers Hold'
1794 ,X_held_by=>FND_GLOBAL.User_Id);
1795
1796 l_inv_pwp_hold :='N';
1797 l_inv_dlv_hold :='N';
1798 l_inv_hold :='N';
1799 l_hold_reason1 :='';
1800 l_hold_applied_yn := 'N';
1801
1802 FOR INVREC_HOLD IN C3(p_inv_tbl(i))
1803 LOOP
1804 IF l_hold_reason1 IS NULL THEN
1805 l_hold_reason1 := INVREC_HOLD.hold_reason||'.';
1806 ELSE
1807 l_hold_reason1 := l_hold_reason1||'<br>'||INVREC_HOLD.hold_reason||'.';
1808 END IF;
1809 l_inv_hold := 'Y';
1810 If INVREC_HOLD.hold_lookup_code = 'Pay When Paid' Then
1811 l_inv_pwp_hold := 'Y';
1812 ElsIf INVREC_HOLD.hold_lookup_code = 'PO Deliverable' Then
1813 l_inv_dlv_hold := 'Y';
1814 ElsIf INVREC_HOLD.hold_lookup_code = 'Project Hold' Then --bug 9525493
1815 l_hold_applied_yn := 'Y';
1816 End If;
1817 END LOOP;
1818
1819 Update PA_PWP_AP_INV_HDR Set HOLD_REASON = l_hold_reason1,
1820 PWP_HOLD_FLAG = l_inv_pwp_hold,
1821 DLV_HOLD_FLAG = l_inv_dlv_hold,
1822 HOLD_FLAG = l_inv_hold,
1823 HOLD_APPLIED_YN = l_hold_applied_yn
1824 Where Invoice_Id = p_inv_tbl(i)
1825 And RELHOLD_REJ_REASON Is Null;
1826
1827 END LOOP;
1828 End;
1829
1830 END PAAP_PWP_PKG;