[Home] [Help]
PACKAGE BODY: APPS.PAAP_PWP_PKG
Source
1 PACKAGE BODY PAAP_PWP_PKG AS
2 -- /* $Header: PAAPPWPB.pls 120.3.12010000.11 2009/01/28 06:03:02 vchilla noship $
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
164 IF P_DEBUG_MODE = 'Y' THEN
165 log_message('[PA_CURR_CODE : '||PA_CURR_CODE||' ]'||'[P_project_id : '||p_project_id||' ]'
166 ||' [P_Task_Id : '||P_Task_Id||'] '||'[P_Exp_Item_Date : '||P_Exp_Item_Date||']',
167 'Derive_ProjCurr_Attribute');
168 END IF;
169
170 If PA_CURR_CODE IS NULL Then
171 PA_CURR_CODE := PA_Currency.get_Currency_code;
172
173 IF P_DEBUG_MODE = 'Y' THEN
174 log_message('From PA_Currency.get_Currency_code [PA_CURR_CODE : '||PA_CURR_CODE||' ]' ,
175 'Derive_ProjCurr_Attribute');
176 END IF;
177 END If;
178
179 IF P_DEBUG_MODE = 'Y' THEN
180 log_message('Before Calling pa_multi_Currency_txn.Get_Proj_curr_code_sql '||
181 '[G_project_id : '||G_Project_Id||' ]',
182 'Derive_ProjCurr_Attribute');
183 END IF;
184
185 If NVL(G_Project_Id, -99) <> P_Project_ID Then
186 Proj_Currency := pa_multi_Currency_txn.Get_Proj_curr_code_sql(p_project_id);
187 G_Project_Id := P_Project_Id;
188 END If;
189
190 IF P_DEBUG_MODE = 'Y' THEN
191 log_message('After Calling pa_multi_Currency_txn.Get_Proj_curr_code_sql '||
192 '[G_project_id : '||G_Project_Id||']'||
193 '[Proj_Currency : '||Proj_Currency||']' ,
194 'Derive_ProjCurr_Attribute');
195 END IF;
196
197 IF P_DEBUG_MODE = 'Y' THEN
198 log_message('Before Calling pa_multi_Currency_txn.get_def_ProjFunc_Cst_Rate_Type '||
199 '[G_Task_Id : '||G_Task_Id||']',
200 'Derive_ProjCurr_Attribute');
201 END IF;
202
203 If NVL(G_Task_Id,-99) <> P_Task_Id Then
204 pa_multi_Currency_txn.get_def_ProjFunc_Cst_Rate_Type(
205 P_task_id ,
206 ProjFunc_Currency,
207 ProjFunc_Cst_Rate_Type);
208 END If;
209
210 IF P_DEBUG_MODE = 'Y' THEN
211 log_message('After Calling pa_multi_Currency_txn.get_def_ProjFunc_Cst_Rate_Type '||
212 '[ProjFunc_Currency : '||ProjFunc_Currency||']'||' [ProjFunc_Cst_Rate_Type : '||
213 ProjFunc_Cst_Rate_Type||'] ',
214 'Derive_ProjCurr_Attribute');
215 END IF;
216
217 If NVL(G_Expenditure_Item_Date,SYSDate) <> P_Exp_Item_Date
218 OR NVL(G_Task_Id,-99) <> P_Task_Id
219 OR NVL(G_From_Curr,'XXXX') <> P_FromCurrency Then /* Added G_From_Curr for Bug# 7830751 */
220
221 IF P_DEBUG_MODE = 'Y' THEN
222 log_message('Before Calling pa_multi_Currency_txn.get_Currency_amounts',
223 'Derive_ProjCurr_Attribute');
224 END IF;
225
226 pa_multi_Currency_txn.get_Currency_amounts (
227 P_project_id =>p_Project_Id,
228 P_task_id =>P_task_id,
229 P_EI_Date =>P_Exp_Item_Date,
230 P_calling_module =>'GET_CURR_AMOUNTS',
231 P_denom_curr_code =>p_fromCurrency,
232 P_acct_curr_code =>PA_CURR_CODE,
233 P_accounted_flag =>'N',
234 P_acct_rate_Date =>l_acct_rate_Date,
235 P_acct_rate_type =>l_acct_rate_type,
236 P_acct_exch_rate =>l_acct_exch_rate,
237 P_project_curr_code =>Proj_Currency,
238 P_project_rate_type =>l_project_rate_type,
239 P_project_rate_Date =>l_project_rate_Date,
240 P_project_exch_rate =>l_project_exch_rate,
241 P_ProjFunc_curr_code =>ProjFunc_Currency,
242 P_ProjFunc_cost_rate_type =>l_ProjFunc_cost_rate_type,
243 P_ProjFunc_cost_rate_Date =>l_ProjFunc_cost_rate_Date,
244 P_ProjFunc_cost_exch_rate =>l_ProjFunc_cost_exch_rate,
245 P_denom_raw_cost => l_denom_raw_cost,
246 P_acct_raw_cost => l_acct_raw_cost,
247 P_project_raw_cost => l_project_raw_cost,
248 P_ProjFunc_raw_cost => l_ProjFunc_raw_cost,
249 P_system_linkage => 'VI',
250 P_status =>l_status,
251 P_stage =>l_stage);
252
253 ProjFunc_Cst_Rate_Date :=l_ProjFunc_cost_rate_Date;
254 ProjFunc_Cst_Rate_Type :=l_ProjFunc_cost_rate_type;
255 ProjFunc_CST_RATE :=nvl(l_ProjFunc_cost_exch_rate,1);
256
257 Proj_Cst_Rate_Date :=l_project_rate_Date;
258 Proj_Cst_Rate_Type :=l_project_rate_type;
259 Proj_CST_RATE :=nvl(l_project_exch_rate,1);
260
261 ACCT_Cst_Rate_Date :=l_acct_rate_Date;
262 ACCT_Cst_Rate_Type :=l_acct_rate_type;
263 ACCT_CST_RATE :=nvl(l_acct_exch_rate,1);
264
265 G_Expenditure_Item_Date := P_Exp_Item_Date;
266 G_Task_Id := P_Task_Id;
267 G_From_Curr := P_FromCurrency; /* For Bug# 7830751 */
268
269 IF P_DEBUG_MODE = 'Y' THEN
270 log_message('After Calling pa_multi_Currency_txn.get_Currency_amounts '||
271 '[G_Expenditure_Item_Date : '||G_Expenditure_Item_Date||']'||' [ProjFunc_Cst_Rate_Date : '||
272 ProjFunc_Cst_Rate_Date||'] '||' [ProjFunc_Cst_Rate_Type : '||
273 ProjFunc_Cst_Rate_Type||'] '||' [ProjFunc_CST_RATE : '||
274 ProjFunc_CST_RATE||'] '||' [Proj_Cst_Rate_Date : '||
275 Proj_Cst_Rate_Date||'] '||' [Proj_Cst_Rate_Type : '||
276 Proj_Cst_Rate_Type||'] '||' [Proj_CST_RATE : '||
277 Proj_CST_RATE||'] '||' [ACCT_Cst_Rate_Date : '||
278 ACCT_Cst_Rate_Date||'] '||' [ACCT_Cst_Rate_Type : '||
279 ACCT_Cst_Rate_Type||'] '||' [ACCT_CST_RATE : '||
280 ACCT_CST_RATE||'] ',
281 'Derive_ProjCurr_Attribute');
282 END IF;
283
284 END If;
285 EXCEPTION
286 WHEN OTHERS THEN
287 IF P_DEBUG_MODE = 'Y' THEN
288 log_message('In When Others Exception : '||SQLERRM,'Derive_ProjCurr_Attribute');
289 END IF;
290 RAISE;
291 END Derive_ProjCurr_Attribute;
292
293 ---------------------------------------------------------------------------------------------------------
294 -- This function derives the all the conversion attributes from any currency passed as a parameter to
295 -- Project Functional/Project/Acct Currencies. This function returns any of the Currency /Exchange Rate/
296 -- Exchange Rate Date/Exchange Rate Type/Amount values based on the parameter p_ret_atr value.
297 -- Input parameters
298 -- Parameters Type Required Description
299 -- p_project_id NUMBER YES It stores the project_id
300 -- p_task_id NUMBER YES It stores the task_Id
301 -- p_ei_date DATE YES It stores the expenditure item date
302 -- p_from_currency VARCHAR2 NO If not passed, this will be same as the Functional
303 -- Currency
304 -- p_ret_atr VARCHAR2 NO Default value is 'ProjFunc_Rate'
305 -- Valid Values are:
306 -- ProjFunc_Rate
307 -- ProjFunc_Rate_Type
308 -- ProjFunc_Rate_Date
309 -- ProjFunc_Amt
310 -- Proj_Rate
311 -- Proj_Rate_Type
312 -- Proj_Rate_Date
313 -- Proj_Amt
314 -- Proj_Curr
315 -- ProjFunc_Curr
316 -- p_amt NUMBER NO Amount to be converted
317 -- Out parameters
318 ----------------------------------------------------------------------------------------------------------
319 Function Get_Proj_Curr_Amt (
320 P_Project_Id IN Number,
321 P_Task_Id IN Number,
322 P_EI_Date IN Date := SYSDATE,
323 P_FromCurrency IN Varchar2 :='',
324 P_RET_ATR IN Varchar2 :='ProjFunc_Rate',
325 P_Amt IN Number :=0
326 ) Return Varchar2 Is
327 BEGIN
328
329 IF P_DEBUG_MODE = 'Y' THEN
330 log_message('Before Calling Derive_ProjCurr_Attribute '||
331 '[P_Project_Id : '||P_Project_Id||'] '||'[P_Task_Id : '||P_Task_Id||'] '
332 ||'[P_EI_Date : '||P_EI_Date||'] '||'[P_FromCurrency : '||P_FromCurrency||'] '
333 ||'[P_RET_ATR : '||P_RET_ATR||'] '||'[P_Amt : '||P_Amt||'] ',
334 'Get_Proj_Curr_Amt');
335 END IF;
336
337 Derive_ProjCurr_Attribute(P_Project_Id ,
338 P_Task_Id ,
339 P_EI_Date,
340 P_FromCurrency);
341 P_Trans_Amount := P_Amt;
342 ProjFunc_Curr_Amount := P_Trans_Amount * ProjFunc_Cst_Rate;
343 Proj_Curr_Amount := P_Trans_Amount * Proj_Cst_Rate;
344
345 If P_RET_ATR = 'ProjFunc_Rate' Then
346 Return Get_ProjFunc_Rate;
347 END If;
348
349 If P_RET_ATR = 'ProjFunc_Rate_Type' Then
350 Return get_ProjFunc_ratetype;
351 END If;
352
353 If P_RET_ATR = 'ProjFunc_Rate_Date' Then
354 Return get_ProjFunc_rateDate;
355 END If;
356
357 If P_RET_ATR = 'ProjFunc_Amt' Then
358 Return get_ProjFunc_amt;
359 END If;
360
361 If P_RET_ATR = 'Proj_Rate' Then
362 Return Get_Proj_Rate;
363 END If;
364
365 If P_RET_ATR = 'Proj_Rate_Type' Then
366 Return get_Proj_ratetype;
367 END If;
368
369 If P_RET_ATR = 'Proj_Rate_Date' Then
370 Return get_Proj_rateDate;
371 END If;
372
373 If P_RET_ATR = 'Proj_Amt' Then
374 Return get_Proj_amt;
375 END If;
376
377 If P_RET_ATR = 'Proj_Curr' Then
378 Return Get_Proj_curr;
379 END If;
380
381 If P_RET_ATR = 'ProjFunc_Curr' Then
382 Return Get_ProjFunc_curr;
383 END If;
384 EXCEPTION
385 WHEN OTHERS THEN
386 IF P_DEBUG_MODE = 'Y' THEN
387 log_message('In When Others Exception :'||SQLERRM,
388 'Get_Proj_Curr_Amt');
389 END IF;
390 return 0;
391 END Get_Proj_Curr_Amt;
392
393
394 Procedure init_global Is
395 BEGIN
396
397 NULL;
398
399 END;
400
401 ---------------------------------------------------------------------------------------------------------
402 -- This procedure releases PWP Hold, DLV Hold for the supplier invoices passed as a pl/sql table
403 -- Input parameters
404 -- Parameters Type Required Description
405 -- P_Inv_Tbl PL/SQL Tbl YES It stores a list of invoice_id's for which the
406 -- PWP/DLV Hold needs to be released.
407 -- Out parameters
408 -- Parameters Type Required Description
412 -- E (business rule violation error) and
409 -- X_return_status VARCHAR2 YES The return status of the APIs.
410 -- Valid values are:
411 -- S (API completed successfully),
413 -- U(Unexpected error, such as an Oracle error.
414 -- X_msg_count NUMBER YES Holds the number of messages in the global message
415 -- table. Calling programs should use this as the
416 -- basis to fetch all the stored messages.
417 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
418 -- one error/warning message Otherwise the column is
419 -- left blank.
420 ----------------------------------------------------------------------------------------------------------
421 Procedure paap_release_hold (P_Inv_Tbl IN InvoiceId
422 ,X_return_status OUT NOCOPY VARCHAR2
423 ,X_msg_count OUT NOCOPY NUMBER
424 ,X_msg_data OUT NOCOPY VARCHAR2) IS
425
426 l_line_location_id Number(15);
427 l_rcv_transaction_id Number;
428 l_hold_lookup_code Varchar2(25);
429 l_should_have_hold Varchar2(1):='N';
430 l_hold_reason Varchar2(240):='Project Manager Release';
431 l_calling_sequence Varchar2(240);
432
433 -- Cursor c1 is to fetch hold lookup code
434 -- for the invoice being passed if any of the PWP or DLV hold exists.
435 Cursor c1(p_invoice_id Number) Is
436 select hold_lookup_code from ap_holds_all
437 where invoice_id= p_invoice_id
438 and hold_lookup_code in ('Pay When Paid','PO Deliverable')
439 and release_reason IS NULL;
440
441 l_err_msg Varchar2(4000);
442
443 BEGIN
444 x_return_status := 'S';
445 X_msg_count :=0;
446
447 IF P_DEBUG_MODE = 'Y' THEN
448 log_message('Begin: paap_release_hold ', 'paap_release_hold');
449 END IF;
450
451 IF p_inv_tbl.count > 0 THEN
452
453 FOR Inv_RelHOld_rec in 1..p_inv_tbl.count LOOP
454 FOR HoldRec in c1(p_inv_tbl(Inv_RelHOld_rec)) LOOP
455 BEGIN
456 IF P_DEBUG_MODE = 'Y' THEN
457 log_message('Before calling AP_HOLDS_PKG.release_single_hold API '
458 ||'[Invoice_Id : '||p_inv_tbl(Inv_RelHOld_rec)||'] '
459 ||'[hold_lookup_code: '||HoldRec.hold_lookup_code||'] '
460 ||'[l_hold_reason: '||l_hold_reason||'] ', 'paap_release_hold');
461 END IF;
462
463 AP_HOLDS_PKG.release_single_hold
464 (X_invoice_id => p_inv_tbl(Inv_RelHOld_rec),
465 X_hold_lookup_code=> HoldRec.hold_lookup_code,
466 X_release_lookup_code =>l_hold_reason);
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 l_err_msg:= SQLERRM;
471
472 IF P_DEBUG_MODE = 'Y' THEN
473 log_message('In When Others Exception FORLOOP '||SQLERRM, 'paap_release_hold');
474 END IF;
475
476 Update PA_PWP_AP_INV_HDR Set RELHOLD_REJ_REASON = SubStr(l_err_msg,1,2000)
477 Where invoice_id = p_inv_tbl(Inv_RelHOld_rec);
478
479 x_msg_count := 1;
480 x_return_status :='E';
481 x_msg_data := SQLERRM;
482 END;
483 END LOOP;
484
485 IF P_DEBUG_MODE = 'Y' THEN
486 log_message('Setting hold reason to null for Invoice_Id:'
487 ||'[Invoice_Id : '||p_inv_tbl(Inv_RelHOld_rec)||' ]', 'paap_release_hold');
488 END IF;
489
490 Update PA_PWP_AP_INV_HDR Set HOLD_REASON = '',
491 PWP_HOLD_FLAG = 'N',
492 DLV_HOLD_FLAG = 'N'
493 Where Invoice_Id = p_inv_tbl(Inv_RelHOld_rec)
494 And RELHOLD_REJ_REASON Is Null;
495
496 END LOOP;
497
498 END IF;
499 COMMIT;
500
501 IF P_DEBUG_MODE = 'Y' THEN
502 log_message('[x_return_status : '||x_return_status||' ]', 'paap_release_hold');
503 END IF;
504
505 IF x_return_status = 'S' THEN
506 X_msg_data := 'PA_INV_HOLD_RELEASE';
507 END IF;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF P_DEBUG_MODE = 'Y' THEN
512 log_message('In When Others Exception :'||SQLERRM, 'paap_release_hold');
513 END IF;
514
515 x_msg_count:=1;
516 x_return_status := 'U';
517 X_msg_data:=SQLERRM;
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
519 END paap_release_hold;
520
521 /*---------------------------------------------------------------------------------------------------------
522 -- This procedure populates pa_pwp_ap_inv_hdr, pa_pwp_ap_inv_dtl tables by processing all the supplier
523 -- invoices pertaining to the project_id being passed. Returns Success/Failure to the calling module.
524 -- Input parameters
525 -- Parameters Type Required Description
526 -- p_project_id NUMBER YES It stores the project_id
527 -- Out parameters
528 -- Parameters Type Required Description
529 -- X_return_status VARCHAR2 YES The return status of the APIs.
530 Valid values are:
531 S (API completed successfully),
535 table. Calling programs should use this as the
532 E (business rule violation error) and
533 U(Unexpected error, such as an Oracle error.
534 -- X_msg_count NUMBER YES Holds the number of messages in the global message
536 basis to fetch all the stored messages.
537 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
538 one error/warning message Otherwise the column is
539 left blank.
540 ----------------------------------------------------------------------------------------------------------*/
541 Procedure Process_SuppInv_Dtls1 (P_Project_Id IN Number
542 ,X_return_status OUT NOCOPY VARCHAR2
543 ,X_msg_count OUT NOCOPY NUMBER
544 ,X_msg_data OUT NOCOPY VARCHAR2) IS
545
546 -- Cursor C1 is to Fetch Supplier wise Invoice details at Invoice Header Level for a project.
547 -- We pickup the payment details from ap_payment_schedules_all and vendor information from
548 -- po_vendors.
549
550 /****
551 Modified below cursor for Bug# 7833675:
552 Removed the discount calculation from here.
553 ****/
554 Cursor c1 Is
555 select apinv.invoice_id Invoice_Id,
556 apinv.invoice_num invoice_num,
557 vend.vendor_id vendor_Id,
558 vend.vendor_name Supplier_name,
559 vend.segment1 Supplier_Num,
560 apinv.Invoice_Date Invoice_Date,
561 P_project_id Project_Id,
562 apinv.invoice_Currency_code invoice_Currency,
563 apinv.payment_Currency_code Payment_Currency,
564 apinv.exchange_rate Exchange_Rate,
565 (select vendor_site_code from
566 po_vendor_sites_all
567 where vendor_id = apinv.vendor_id
568 and vendor_site_id = apinv.vendor_site_id) Supplier_Site,
569 Invoice_Amount,
570 decode(apinv.payment_Currency_code, apinv.invoice_Currency_code,
571 sum(amount_remaining),
572 sum(amount_remaining)/nvl(exchange_rate,1)) UnPaid_Inv_Amt,
573 (decode(apinv.payment_Currency_code,
574 apinv.invoice_Currency_code,
575 sum(gross_amount),
576 sum(gross_amount)/nvl(apinv.exchange_rate,1)) -
577 decode(apinv.payment_Currency_code,
578 apinv.invoice_Currency_code,
579 sum(amount_remaining),
580 sum(amount_remaining)/nvl(apinv.exchange_rate,1))) Paid_Inv_Amt
581 from ap_invoices_all apinv,
582 ap_payment_schedules_all appay,
583 po_vendors vend
584 where exIsts (select 1 from
585 ap_invoice_dIstributions_all apd
586 where apd.project_id = P_project_Id
587 and apd.posted_flag ='Y'
588 and apinv.invoice_id = apd.invoice_id)
589 and appay.invoice_id(+)=apinv.invoice_id
590 and vend.vendor_id = apinv.vendor_id
591 --and apinv.invoice_amount !=0 -- Bug# 7713608
592 and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
593 and apinv.invoice_id = NVL(G_Invoice_Id,apinv.invoice_id)
594 group by apinv.invoice_id, apinv.invoice_num,
595 vend.vendor_name,apinv.invoice_Date, apinv.invoice_Currency_code,
596 apinv.vendor_id,apinv.vendor_site_id, apinv.Invoice_Amount, vend.vendor_id,
597 apinv.payment_Currency_code,apinv.exchange_rate,vend.segment1
598 order by apinv.invoice_id;
599
600 -- This cursor is for calculating the invoice paid amount in current period
601 Cursor C_Cur_Per_Inv_Paid(p_invoice_id Number) IS
602 select nvl((decode(apinv.payment_Currency_code,
603 apinv.invoice_Currency_code,
604 sum(gross_amount),
605 sum(gross_amount)/nvl(apinv.exchange_rate,1)) -
606 decode(apinv.payment_Currency_code,
607 apinv.invoice_Currency_code,
608 sum(amount_remaining),
609 sum(amount_remaining)/nvl(apinv.exchange_rate,1))),0) Paid_Inv_Amt
610 from ap_invoices_all apinv,
611 ap_payment_schedules_all appay
612 where apinv.invoice_id = p_invoice_id and exIsts (select 1 from
613 ap_invoice_dIstributions_all apd
614 where apd.project_id = P_project_Id
615 and apd.posted_flag ='Y'
616 and apinv.invoice_id = apd.invoice_id)
617 and appay.invoice_id(+)=apinv.invoice_id
618 and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
619 and exists (
620 (SELECT 1
621 FROM ap_invoice_payments_all invpay ,
622 pa_projects_all proj,
623 pa_implementations_all imp,
624 GL_PERIOD_STATUSES glp
625 WHERE proj.project_id = p_project_id
626 and invpay.invoice_id = appay.invoice_id
627 and invpay.payment_num = appay.payment_num
628 and glp.application_id = 101
629 AND glp.adjustment_period_flag = 'N'
630 AND glp.set_of_books_id = imp.set_of_books_id --Bug# 7713608
631 AND glp.closing_status = 'O'
635 BETWEEN MAX(glp.start_date) AND MAX(glp.end_date)
632 and imp.org_id = proj.org_id
633 GROUP BY glp.application_id,glp.adjustment_period_flag,glp.closing_status
634 HAVING max(invpay.accounting_date)
636 )) group by apinv.payment_Currency_code,
637 apinv.invoice_Currency_code, apinv.exchange_rate;
638
639 -- Cursor C2 is to fetch the invoice details at distribution level.
640 -- This returns total invoice amount for specific project related distributions for an invoice.
641 Cursor C2(p_Invoice_Id Number, p_Project_Id Number) Is
642 select apd.invoice_id,
643 apd.project_id,
644 apd.task_id,
645 apd.Expenditure_Item_Date,
646 pod.po_header_id,
647 sum(apd.amount) ProjInvAmount,
648 sum(ap_pay_hd.amount) Disc_Taken_On_Invoice /* Bug# 7833675 */
649 from
650 ap_invoice_dIstributions_all apd,
651 po_dIstributions_all pod,
652 ap_payment_hist_dists ap_pay_hd
653 where apd.project_Id = p_project_Id
654 and apd.invoice_id = p_invoice_id
655 and apd.posted_flag = 'Y'
656 and pod.po_dIstribution_id(+) = apd.po_distribution_id
657 and ap_pay_hd.invoice_distribution_id(+) = apd.invoice_distribution_id /* Bug# 7833675 */
658 and ap_pay_hd.pay_dist_lookup_code(+) = 'DISCOUNT' /* Bug# 7833675 */
659 and apd.line_type_lookup_code <> 'RETAINAGE'
660 group by apd.invoice_id, apd.project_id, apd.task_id, apd.Expenditure_Item_Date,
661 pod.po_header_id
662 order by apd.project_id, apd.task_id, apd.Expenditure_Item_Date, apd.invoice_id,pod.po_header_id;
663
664 -- Cursor C_RTNG_AMT is to fetch the retainage amount at distribution level.
665 -- This returns retainage amount for specific project related distributions for an invoice.
666 -- Group by is added just to capture %NOTFOUND in case if there are no records
667 -- with retainage lookup code in the invoice distributions table.
668 Cursor C_RTNG_AMT(
669 p_Invoice_Id Number,
670 p_Project_Id Number,
671 p_task_id Number,
672 p_Expenditure_Item_Date Date,
673 p_po_header_id Number) Is
674 select sum(apd.amount) ProjRtngAmount from
675 ap_invoice_dIstributions_all apd,
676 po_dIstributions_all pod
677 where apd.project_Id = p_project_Id
678 and apd.invoice_id = p_invoice_id
679 and apd.posted_flag = 'Y'
680 and pod.po_dIstribution_id(+) = apd.po_distribution_id
681 and pod.po_header_id(+)=p_po_header_id
682 and apd.line_type_lookup_code = 'RETAINAGE'
683 and apd.task_id = p_task_id
684 and apd.expenditure_item_date = p_expenditure_item_date
685 group by apd.invoice_id, apd.project_Id, apd.task_id, apd.po_dIstribution_id, apd.expenditure_item_date;
686
687 -- To get the hold reason if any for an Invoice.
688 Cursor C3(P_Invoice_Id Number) Is
689 Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
690 Where invoice_id= P_Invoice_Id
691 And hold_lookup_code In ('Pay When Paid','PO Deliverable')
692 and RELEASE_REASON is null;
693
694 -- Cursor C4 is to identify the Manually/Automatically linked draft invoices to a supplier invoice.
695 Cursor C4(P_Invoice_Id Number, P_Project_Id Number) Is
696 Select distinct draft_invoice_num,link_type From (
697 Select draft_invoice_num, 'M' link_type From PA_PWP_LINKED_INVOICES PWP
698 Where PWP.AP_INVOICE_ID = p_invoice_id
699 And PWP.PROJECT_ID = p_project_id
700 UNION ALL
701 Select pdii.draft_invoice_num, 'A' From PA_DRAFT_INVOICE_ITEMS PDII ,
702 PA_CUST_REV_DIST_LINES CRDL ,
703 PA_EXPENDITURE_ITEMS EI
704 Where PDII.project_id = crdl.project_id
705 And pdii.draft_invoice_num = crdl.draft_invoice_num
706 And pdii.line_num = crdl.draft_invoice_item_line_num
707 And crdl.expenditure_item_id = ei.expenditure_item_id
708 And ei.system_linkage_function = 'VI'
709 And ei.document_header_id =p_invoice_id
710 And ei.transaction_source like 'AP%'
711 And ei.project_id =p_project_id);
712
713 -- Cursor C5 is to get all the PO number's matched to an Invoice.
714 Cursor C5(p_Invoice_Id Number) IS
715 Select Segment1 PO_NUMBER From po_headers_all
716 Where po_header_id in (Select distinct po_header_id
717 from pa_pwp_ap_inv_dtl where invoice_id = p_Invoice_id);
718
719 --Cursor C6 is to get all the linked invoices for a given Draft_Inv_Num
720 Cursor C6(P_Draft_Inv_Num Number) IS
721 Select distinct Invoice_Id From (
722 Select AP_Invoice_Id Invoice_Id From PA_PWP_LINKED_INVOICES PWP
723 Where PWP.draft_invoice_num = P_Draft_Inv_Num
724 And PWP.PROJECT_ID = p_project_id
725 And ap_Invoice_Id is not null
726 UNION ALL
727 Select ei.document_header_id From PA_DRAFT_INVOICE_ITEMS PDII ,
728 PA_CUST_REV_DIST_LINES CRDL ,
729 PA_EXPENDITURE_ITEMS EI
730 Where PDII.project_id = crdl.project_id
731 And pdii.draft_invoice_num = P_Draft_Inv_Num
732 AND pdii.draft_invoice_num = crdl.draft_invoice_num
733 And pdii.line_num = crdl.draft_invoice_item_line_num
734 And crdl.expenditure_item_id = ei.expenditure_item_id
735 And ei.system_linkage_function = 'VI'
736 And ei.transaction_source like 'AP%'
737 And ei.project_id =p_project_id);
738
739 l_PA_PWP_AP_HDR_ID Number;
740
741 ProjFunc_INVOICE_AMT Number;
742 ProjFunc_AMT_PAID Number;
746
743 ProjFunc_AMT_UNPAID Number;
744 ProjFunc_DISCOUNT_AMT Number;
745 ProjFunc_Retainage Number;
747 Proj_INVOICE_AMT Number;
748 Proj_AMT_PAID Number;
749 Proj_AMT_UNPAID Number;
750 Proj_DISCOUNT_AMT Number;
751 Proj_Retainage Number;
752
753 ACCT_INVOICE_AMT Number;
754 ACCT_AMT_PAID Number;
755 ACCT_AMT_UNPAID Number;
756 ACCT_DISCOUNT_AMT Number;
757 Acct_Retainage Number;
758
759 INVOICE_AMOUNT Number;
760 AMOUNT_PAID Number;
761 AMOUNT_UNPAID Number;
762 DISCOUNT_AMOUNT Number;
763 Retainage Number;
764
765 l_inv_paid Varchar2(1):='N';
766
767 l_hold_reason Varchar2(4000):='';
768 l_inv_pwp_hold Varchar2(1):='N';
769 l_inv_dlv_hold Varchar2(1):='N';
770
771 l_po_number Varchar2(2000):='';
772 l_draft_inv_number Varchar2(2000):='';
773 l_draft_inv_link_type Varchar2(2000):='';
774
775 l_status Varchar2(1);
776 l_stage Number :=0 ;
777
778 l_projfunc_cur_per_inv_paid Number :=0;
779 l_proj_cur_per_inv_paid Number :=0;
780
781 ProjFunc_Cur_Per_AMT_PAID Number;
782 Proj_Cur_Per_AMT_PAID Number;
783
784 L_Cur_Per_Inv_Paid Number;
785
786 l_inv_amount Number:=0;
787 BEGIN
788
789 l_stage :=0;
790 l_status :='S';
791
792 IF P_DEBUG_MODE = 'Y' THEN
793 log_message('Begin: Process_SuppInv_Dtls1'||
794 '[P_Project_Id : '||P_Project_Id||' ]',
795 'Process_SuppInv_Dtls1');
796 END IF;
797
798 IF P_DEBUG_MODE = 'Y' THEN
799 log_message('Before deleting data from PA_PWP_AP_INV_HDR and PA_PWP_AP_INV_DTL for'||
800 '[P_Project_Id : '||P_Project_Id||' ]',
801 'Process_SuppInv_Dtls1');
802 END IF;
803
804 IF G_Draft_Inv_Num IS NULL THEN
805 Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id;
806 Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id;
807 END IF;
808 IF G_Draft_Inv_Num IS NOT NULL THEN
809 OPEN C6(G_Draft_Inv_Num);
810 END IF;
811
812 LOOP
813 G_Invoice_Id :='';
814 IF G_Draft_Inv_Num IS NOT NULL AND C6%ISOPEN THEN
815 FETCH C6 INTO G_Invoice_Id;
816 IF C6%NOTFOUND THEN
817 CLOSE C6;
818 EXIT;
819 ELSE
820 Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
821 Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
822 END IF;
823 END IF;
824
825 l_stage :=10;
826 FOR INVREC IN C1 LOOP
827
828 IF P_DEBUG_MODE = 'Y' THEN
829 log_message('In For Loop for INVREC'||
830 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
831 '[l_stage : '||l_stage||']',
832 'Process_SuppInv_Dtls1');
833 END IF;
834
835 l_hold_reason :='';
836 l_inv_paid:='N';
837
838 l_inv_pwp_hold:='N';
839 l_inv_dlv_hold:='N';
840 l_draft_inv_number:='';
841 l_po_number := '';
842
843 IF INVREC.Invoice_Amount = 0 THEN
844 l_inv_amount :=1;
845 ELSE
846 l_inv_amount :=INVREC.Invoice_Amount;
847 END IF;
848
849 l_stage :=20;
850
851 IF P_DEBUG_MODE = 'Y' THEN
852 log_message('Before Opening C3 for INVREC_HOLD'||
853 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
854 '[l_stage : '||l_stage||'] ',
855 'Process_SuppInv_Dtls1');
856 END IF;
857
858 FOR INVREC_HOLD IN C3(INVREC.INVOICE_ID)
859 LOOP
860 IF l_hold_reason IS NULL THEN
861 l_hold_reason := INVREC_HOLD.hold_reason||'.';
862 ELSE
863 l_hold_reason := l_hold_reason||'<br>'||INVREC_HOLD.hold_reason||'.';
864 END IF;
865
866 If INVREC_HOLD.hold_lookup_code = 'Pay When Paid' Then
867 l_inv_pwp_hold := 'Y';
868 ElsIf INVREC_HOLD.hold_lookup_code = 'PO Deliverable' Then
869 l_inv_dlv_hold := 'Y';
870 End If;
871 END LOOP;
872
873 IF P_DEBUG_MODE = 'Y' THEN
874 log_message('INVREC_HOLD '||
875 '[l_hold_reason : '||l_hold_reason||'] '||
876 '[l_inv_pwp_hold : '||l_inv_pwp_hold||'] '||
877 '[l_inv_dlv_hold : '||l_inv_dlv_hold||'] ',
878 'Process_SuppInv_Dtls1');
879 END IF;
880
881 IF P_DEBUG_MODE = 'Y' THEN
882 log_message('Before Opening C4 for DRAFTINV_REC ',
883 'Process_SuppInv_Dtls1');
884 END IF;
885
886 FOR DRAFTINV_REC IN C4(INVREC.INVOICE_ID,INVREC.PROJECT_ID) LOOP
890 ELSE
887 IF l_draft_inv_number IS NULL THEN
888 l_draft_inv_number:=DRAFTINV_REC.draft_invoice_num;
889 l_draft_inv_link_type :=DRAFTINV_REC.link_type;
891 l_draft_inv_number:=l_draft_inv_number||','||DRAFTINV_REC.draft_invoice_num;
892 l_draft_inv_link_type := l_draft_inv_link_type||','||DRAFTINV_REC.link_type;
893 END IF;
894 END LOOP;
895
896 IF P_DEBUG_MODE = 'Y' THEN
897 log_message('After Opening C4 for DRAFTINV_REC '||
898 '[l_draft_inv_number : '||l_draft_inv_number||'] '||
899 '[l_draft_inv_link_type : '||l_draft_inv_link_type||'] ',
900 'Process_SuppInv_Dtls1');
901 END IF;
902
903 l_stage :=30;
904 IF P_DEBUG_MODE = 'Y' THEN
905 log_message('Before calculating invoice paid amount in current period ',
906 'Process_SuppInv_Dtls1');
907 END IF;
908 L_Cur_Per_Inv_Paid :=0; /* Initialization is done for Bug# 8203817 */
909
910 IF nvl(INVREC.Paid_Inv_Amt,0) > 0 THEN
911 OPEN C_Cur_Per_Inv_Paid(INVREC.INVOICE_ID);
912 FETCH C_Cur_Per_Inv_Paid Into L_Cur_Per_Inv_Paid;
913 CLOSE C_Cur_Per_Inv_Paid;
914 END IF;
915
916 IF P_DEBUG_MODE = 'Y' THEN
917 log_message('After calculating invoice paid amount in current period '||
918 '[L_Cur_Per_Inv_Paid : '||L_Cur_Per_Inv_Paid||'] ',
919 'Process_SuppInv_Dtls1');
920 END IF;
921
922 IF P_DEBUG_MODE = 'Y' THEN
923 log_message('Deriving paid flag for invoice '||
924 '[l_stage : '||l_stage||'] ',
925 'Process_SuppInv_Dtls1');
926 END IF;
927
928 /*****
929 1. If there is unpaid amount and paid amount,
930 then it is Partial Case.
931 2. If there is unpaid amount and no paid amount,
932 then it is Not Paid Case.
933 3. If there is no unpaid amount and paid amount,
934 then it is Fully Paid case.
935 4. If there is no unpaid amount and no paid amount,
936 then it is also Not Paid case.
937 *****/
938
939 If nvl(INVREC.Unpaid_Inv_Amt,0) <> 0 Then
940 If nvl(INVREC.Paid_Inv_Amt,0) <> 0 Then
941 l_inv_paid := 'P';
942 Else
943 l_inv_paid := 'N';
944 End If;
945 ElsIf nvl(INVREC.Paid_Inv_Amt,0) <> 0 Then /* For Bug# 7831141 */
946 l_inv_paid := 'Y';
947 Else
948 l_inv_paid := 'N';
949 End If;
950
951 IF P_DEBUG_MODE = 'Y' THEN
952 log_message('After deriving paid flag for invoice '||
953 '[l_inv_paid : '||l_inv_paid||'] ',
954 'Process_SuppInv_Dtls1');
955 END IF;
956
957 l_stage :=40;
958
959
960 -- L_PA_PWP_AP_HDR_ID:= PA_PWP_AP_INV_HDR_S.nextval; Changed for backward compatibility : Bug 7666516
961 SELECT PA_PWP_AP_INV_HDR_S.nextval
962 INTO L_PA_PWP_AP_HDR_ID
963 FROM dual;
964
965 IF P_DEBUG_MODE = 'Y' THEN
966 log_message('Before inserting record in PA_PWP_AP_INV_HDR'||
967 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
968 '[l_stage : '||l_stage||'] '||
969 '[L_PA_PWP_AP_HDR_ID : '||L_PA_PWP_AP_HDR_ID||'] ',
970 'Process_SuppInv_Dtls1');
971 END IF;
972
973 Insert Into PA_PWP_AP_INV_HDR(PA_PWP_AP_HDR_ID
974 ,PROJECT_ID
975 ,INVOICE_ID
976 ,INVOICE_NUM
977 ,vendOR_ID
978 ,SUPPLIER_NUM
979 ,SUPPLIER_NAME
980 ,SUPPLIER_SITE_CODE
981 ,INVOICE_Date
982 ,INVOICE_AMOUNT
983 ,INVOICE_Currency
984 ,HOLD_REASON
985 ,PWP_HOLD_FLAG
986 ,DLV_HOLD_FLAG
987 ,PAYMENT_STATUS
988 ,LINKED_DRAFT_INVOICE_NUM
989 ,LINKED_DRFAT_INV_TYPE
990 ) Values(
991 l_PA_PWP_AP_HDR_ID
992 ,INVREC.PROJECT_ID
993 ,INVREC.INVOICE_ID
994 ,INVREC.INVOICE_NUM
995 ,INVREC.vendOR_ID
996 ,INVREC.Supplier_Num
997 ,INVREC.SUPPLIER_NAME
998 ,INVREC.SUPPLIER_SITE
999 ,INVREC.INVOICE_Date
1000 ,INVREC.INVOICE_AMOUNT
1001 ,INVREC.INVOICE_Currency
1002 ,l_hold_reason
1003 ,l_inv_pwp_hold
1004 ,l_inv_dlv_hold
1005 ,l_inv_paid
1006 ,l_draft_inv_number
1007 ,l_draft_inv_link_type
1008 );
1009
1010 l_stage :=50;
1011
1012 IF P_DEBUG_MODE = 'Y' THEN
1013 log_message('Before opening the loop for inserting record in PA_PWP_AP_INV_DTL '||
1014 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1018
1015 '[l_stage : '||l_stage||'] ',
1016 'Process_SuppInv_Dtls1');
1017 END IF;
1019 FOR INVDTL IN C2(INVREC.INVOICE_ID, INVREC.PROJECT_ID) LOOP
1020
1021 l_stage :=60;
1022
1023 IF P_DEBUG_MODE = 'Y' THEN
1024 log_message('Before calling Derive_ProjCurr_Attribute '||'[l_stage : '||l_stage||'] ',
1025 'Process_SuppInv_Dtls1');
1026 END IF;
1027
1028 Derive_ProjCurr_Attribute(p_Project_id,
1029 INVDTL.tasK_id,
1030 INVDTL.Expenditure_Item_Date,
1031 INVREC.Invoice_Currency);
1032
1033 IF P_DEBUG_MODE = 'Y' THEN
1034 log_message('Before Calculating the retainage amount '||'[l_stage : '||l_stage||'] ',
1035 'Process_SuppInv_Dtls1');
1036 END IF;
1037
1038 OPEN C_RTNG_AMT(
1039 INVREC.Invoice_Id,
1040 p_Project_Id,
1041 INVDTL.task_id,
1042 INVDTL.Expenditure_Item_Date,
1043 INVDTL.po_header_id);
1044
1045 FETCH C_RTNG_AMT INTO Retainage;
1046 IF C_RTNG_AMT%NOTFOUND THEN
1047 Retainage := 0;
1048 END IF;
1049 CLOSE C_RTNG_AMT;
1050
1051 IF P_DEBUG_MODE = 'Y' THEN
1052 log_message('Before deriving all the amount columns',
1053 'Process_SuppInv_Dtls1');
1054 END IF;
1055
1056 ProjFunc_INVOICE_AMT :=INVDTL.ProjInvAmount* ProjFunc_CST_RATE;
1057
1058 ProjFunc_AMT_PAID :=(INVDTL.ProjInvAmount*INVREC.Paid_Inv_Amt/l_inv_amount)
1059 * ProjFunc_CST_RATE;
1060 ProjFunc_AMT_UNPAID :=(INVDTL.ProjInvAmount*INVREC.UnPaid_Inv_Amt/l_inv_amount)
1061 * ProjFunc_CST_RATE;
1062 IF nvl(L_Cur_Per_Inv_Paid,0) >0 THEN
1063 ProjFunc_Cur_Per_AMT_PAID := (INVDTL.ProjInvAmount*L_Cur_Per_Inv_Paid/l_inv_amount)
1064 * ProjFunc_CST_RATE;
1065 ELSE
1066 ProjFunc_Cur_Per_AMT_PAID := 0;
1067 END IF;
1068
1069 /* Modified the discount calculation for Bug# 7833675 as below.
1070 ProjFunc_DISCOUNT_AMT :=(INVDTL.ProjInvAmount*INVREC.DIsc_Taken_On_Inv/l_inv_amount)
1071 * ProjFunc_CST_RATE;
1072 */
1073
1074 ProjFunc_DISCOUNT_AMT :=INVDTL.DIsc_Taken_On_Invoice* ProjFunc_CST_RATE;
1075
1076 ProjFunc_Retainage := Retainage *ProjFunc_CST_RATE;
1077
1078 Proj_INVOICE_AMT :=INVDTL.ProjInvAmount*Proj_CST_RATE;
1079 Proj_AMT_PAID :=(INVDTL.ProjInvAmount*INVREC.Paid_Inv_Amt/l_inv_amount)
1080 *Proj_CST_RATE;
1081 Proj_AMT_UNPAID :=(INVDTL.ProjInvAmount*INVREC.UnPaid_Inv_Amt/l_inv_amount)
1082 *Proj_CST_RATE;
1083
1084 IF nvl(L_Cur_Per_Inv_Paid,0) >0 THEN
1085 Proj_Cur_Per_AMT_PAID := (INVDTL.ProjInvAmount*L_Cur_Per_Inv_Paid/l_inv_amount)
1086 * Proj_CST_RATE;
1087 ELSE
1088 Proj_Cur_Per_AMT_PAID := 0;
1089 END IF;
1090
1091 /* Modified the discount calculation for Bug# 7833675 as below.
1092 Proj_DISCOUNT_AMT :=(INVDTL.ProjInvAmount*INVREC.DIsc_Taken_On_Inv/l_inv_amount)
1093 *Proj_CST_RATE;
1094 */
1095 Proj_DISCOUNT_AMT :=INVDTL.DIsc_Taken_On_Invoice*Proj_CST_RATE;
1096
1097 Proj_Retainage := Retainage * Proj_CST_RATE;
1098
1099 ACCT_INVOICE_AMT :=INVDTL.ProjInvAmount*ACCT_CST_RATE;
1100 ACCT_AMT_PAID :=(INVDTL.ProjInvAmount*INVREC.Paid_Inv_Amt/l_inv_amount)
1101 *ACCT_CST_RATE;
1102 ACCT_AMT_UNPAID :=(INVDTL.ProjInvAmount*INVREC.UnPaid_Inv_Amt/l_inv_amount)
1103 *ACCT_CST_RATE;
1104
1105 /* Modified the discount calculation for Bug# 7833675 as below.
1106 ACCT_DISCOUNT_AMT :=(INVDTL.ProjInvAmount*INVREC.DIsc_Taken_On_Inv/l_inv_amount)
1107 *ACCT_CST_RATE;
1108 */
1109
1110 ACCT_DISCOUNT_AMT :=INVDTL.Disc_Taken_On_Invoice*ACCT_CST_RATE;
1111
1112 Acct_Retainage := Retainage *ACCT_CST_RATE;
1113
1114 INVOICE_AMOUNT :=INVDTL.ProjInvAmount;
1115 AMOUNT_PAID :=(INVDTL.ProjInvAmount*INVREC.Paid_Inv_Amt/l_inv_amount);
1116 AMOUNT_UNPAID :=(INVDTL.ProjInvAmount*INVREC.UnPaid_Inv_Amt/l_inv_amount);
1117
1118 /* Modified the discount calculation for Bug# 7833675 as below.
1119 DISCOUNT_AMOUNT :=(INVDTL.DIsc_Taken_On_Invoice/l_inv_amount);
1120 */
1121 DISCOUNT_AMOUNT :=INVDTL.DIsc_Taken_On_Invoice;
1122
1123 IF P_DEBUG_MODE = 'Y' THEN
1124 log_message('After deriving all amount columns '||
1125 '[ProjFunc_INVOICE_AMT : '||ProjFunc_INVOICE_AMT||'] '||
1126 '[ProjFunc_AMT_PAID : '||ProjFunc_AMT_PAID||'] '||
1127 '[ProjFunc_AMT_UNPAID : '||ProjFunc_AMT_UNPAID||'] '||
1128 '[ProjFunc_Cur_Per_AMT_PAID : '||ProjFunc_Cur_Per_AMT_PAID||'] '||
1129 '[ProjFunc_DISCOUNT_AMT : '||ProjFunc_DISCOUNT_AMT||'] '||
1130 '[Proj_INVOICE_AMT : '||Proj_INVOICE_AMT||'] '||
1131 '[Proj_AMT_PAID : '||Proj_AMT_PAID||'] '||
1132 '[Proj_AMT_UNPAID : '||Proj_AMT_UNPAID||'] '||
1136 '[ACCT_AMT_PAID : '||ACCT_AMT_PAID||'] '||
1133 '[Proj_Cur_Per_AMT_PAID : '||Proj_Cur_Per_AMT_PAID||'] '||
1134 '[Proj_DISCOUNT_AMT : '||Proj_DISCOUNT_AMT||'] '||
1135 '[ACCT_INVOICE_AMT : '||ACCT_INVOICE_AMT||'] '||
1137 '[ACCT_AMT_UNPAID : '||ACCT_AMT_UNPAID||'] '||
1138 '[ACCT_DISCOUNT_AMT : '||ACCT_DISCOUNT_AMT||'] '||
1139 '[INVOICE_AMOUNT : '||INVOICE_AMOUNT||'] '||
1140 '[AMOUNT_PAID : '||AMOUNT_PAID||'] '||
1141 '[AMOUNT_UNPAID : '||AMOUNT_UNPAID||'] '||
1142 '[DISCOUNT_AMOUNT : '||DISCOUNT_AMOUNT||'] ',
1143 'Process_SuppInv_Dtls1');
1144 END IF;
1145
1146 l_stage :=70;
1147
1148 IF P_DEBUG_MODE = 'Y' THEN
1149 log_message('Before inserting record in PA_PWP_AP_INV_DTL '||
1150 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1151 '[l_stage : '||l_stage||'] ',
1152 'Process_SuppInv_Dtls1');
1153 END IF;
1154
1155 Insert into PA_PWP_AP_INV_DTL(PA_PWP_AP_HDR_ID
1156 ,INVOICE_ID
1157 ,PROJECT_ID
1158 ,TASK_ID
1159 ,Expenditure_Item_Date
1160 ,PO_HEADER_ID
1161 ,INVOICE_Currency
1162 ,PROJINV_TOT_AMOUNT
1163 ,AMOUNT_PAID
1164 ,AMOUNT_UNPAID
1165 ,RETAINED_AMOUNT
1166 ,DISCOUNT_AMOUNT
1167 ,ProjFunc_Currency_CODE
1168 ,ProjFunc_INVOICE_AMOUNT
1169 ,ProjFunc_INV_PAID_AMOUNT
1170 ,ProjFunc_INV_UNPAID_AMOUNT
1171 ,ProjFunc_RETAINED_AMOUNT
1172 ,ProjFunc_DISCOUNT_AMOUNT
1173 ,Proj_Currency_CODE
1174 ,Proj_INVOICE_AMOUNT
1175 ,Proj_INV_PAID_AMOUNT
1176 ,Proj_INV_UNPAID_AMOUNT
1177 ,Proj_RETAINED_AMOUNT
1178 ,Proj_DISCOUNT_AMOUNT
1179 ,ACCT_Currency_CODE
1180 ,ACCT_INVOICE_AMOUNT
1181 ,ACCT_INV_PAID_AMOUNT
1182 ,ACCT_INV_UNPAID_AMOUNT
1183 ,ACCT_RETAINED_AMOUNT
1184 ,ACCT_DISCOUNT_AMOUNT
1185 ,PROJFUNC_CUR_PER_INV_PAID
1186 ,PROJ_CUR_PER_INV_PAID) VALUES (
1187 l_PA_PWP_AP_HDR_ID
1188 ,INVREC.INVOICE_ID
1189 ,INVREC.PROJECT_ID
1190 ,INVDTL.TASK_ID
1191 ,INVDTL.Expenditure_Item_Date
1192 ,INVDTL.PO_HEADER_ID
1193 ,INVREC.INVOICE_Currency
1194 ,INVOICE_AMOUNT
1195 ,AMOUNT_PAID
1196 ,AMOUNT_UNPAID
1197 ,Retainage
1198 ,DISCOUNT_AMOUNT
1199 ,ProjFunc_Currency
1200 ,ProjFunc_INVOICE_AMT
1201 ,ProjFunc_AMT_PAID
1202 ,ProjFunc_AMT_UNPAID
1203 ,ProjFunc_retainage
1204 ,ProjFunc_DISCOUNT_AMT
1205 ,Proj_Currency
1206 ,Proj_INVOICE_AMT
1207 ,Proj_AMT_PAID
1208 ,Proj_AMT_UNPAID
1209 ,Proj_Retainage
1210 ,Proj_DISCOUNT_AMT
1211 ,PA_CURR_CODE
1212 ,ACCT_INVOICE_AMT
1213 ,ACCT_AMT_PAID
1214 ,ACCT_AMT_UNPAID
1215 ,Acct_retainage
1216 ,ACCT_DISCOUNT_AMT
1217 ,ProjFunc_Cur_Per_AMT_PAID
1218 ,Proj_Cur_Per_AMT_PAID);
1219
1220 END LOOP;
1221 l_stage :=80;
1222
1223 IF P_DEBUG_MODE = 'Y' THEN
1224 log_message('Before deriving PO Number '||
1225 '[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
1226 '[l_stage : '||l_stage||'] ',
1227 'Process_SuppInv_Dtls1');
1228 END IF;
1229
1230 FOR PO_NUM IN C5(INVREC.INVOICE_ID) LOOP
1231 If l_po_number Is NULL Then
1232 l_po_number := l_po_number||nvl(PO_NUM.po_number,'');
1233 Else
1234 l_po_number := l_po_number||','||nvl(PO_NUM.po_number,'');
1235 End If;
1236 END LOOP;
1237
1238 IF P_DEBUG_MODE = 'Y' THEN
1239 log_message('After deriving PO Number '||
1240 '[l_po_number : '||l_po_number||'] '||
1241 '[l_stage : '||l_stage||'] ',
1242 'Process_SuppInv_Dtls1');
1243 END IF;
1244
1245 Update PA_PWP_AP_INV_HDR
1246 Set po_number = l_po_number
1247 Where pa_pwp_ap_hdr_id = l_pa_pwp_ap_hdr_id;
1248
1249 END LOOP;
1250
1251 IF G_Draft_Inv_Num IS NULL THEN
1252 EXIT;
1253 END IF;
1254
1255 END LOOP;
1256
1257 COMMIT;
1258 IF P_DEBUG_MODE = 'Y' THEN
1259 log_message('End of the procedure '||
1260 '[l_status : '||l_status||'] ',
1261 'Process_SuppInv_Dtls1');
1262 END IF;
1263
1264 X_return_status := l_status;
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 IF C1%ISOPEN THEN
1268 CLOSE C1;
1269 END IF;
1270
1271 IF C2%ISOPEN THEN
1272 CLOSE C2;
1273 END IF;
1274
1275 IF C3%ISOPEN THEN
1276 CLOSE C3;
1277 END IF;
1278
1279 IF C4%ISOPEN THEN
1280 CLOSE C4;
1281 END IF;
1282
1283 IF C5%ISOPEN THEN
1284 CLOSE C5;
1285 END IF;
1286
1287 IF C6%ISOPEN THEN
1288 CLOSE C6;
1289 END IF;
1290
1291 IF C_Cur_Per_Inv_Paid%ISOPEN THEN
1292 CLOSE C_Cur_Per_Inv_Paid;
1293 END IF;
1294
1295 IF C_RTNG_AMT%ISOPEN THEN
1296 CLOSE C_RTNG_AMT;
1297 END IF;
1298
1299 IF P_DEBUG_MODE = 'Y' THEN
1300 log_message('In When Others Exception : '||SQLERRM,
1301 'Process_SuppInv_Dtls1');
1302 END IF;
1303 X_return_status := 'U';
1304 X_msg_data := SQLERRM;
1305 END Process_SuppInv_Dtls1;
1306
1307 ---------------------------------------------------------------------------------------------------------
1308 -- This procedure in turn calls Process_SuppInv_Dtls1, to populate pa_pwp_ap_inv_hdr, pa_pwp_ap_inv_dtl
1309 -- tables by processing all the supplier invoices pertaining to the project_id being passed.
1310 -- This is being called from the Summary Page of Subcontractor tab.
1311 -- Input parameters
1312 -- Parameters Type Required Description
1313 -- p_project_id NUMBER YES It stores the project_id
1314 -- Out parameters
1315 ----------------------------------------------------------------------------------------------------------
1316 Procedure Process_SuppInv_Dtls (P_Project_Id IN Number, P_Draft_Inv_Num IN Number :='') IS
1317 X_return_status VARCHAR2(2000);
1318 X_msg_count NUMBER;
1319 X_msg_data VARCHAR2(4000);
1320 BEGIN
1321
1322 IF P_Draft_Inv_Num IS NOT NULL THEN
1323 IF NVL(G_Draft_Inv_Num,-99) <> NVL(P_Draft_Inv_Num,-99) THEN
1324 G_Draft_Inv_Num:= P_Draft_Inv_Num;
1325 END IF;
1326 ELSE
1327 G_Draft_Inv_Num:='';
1328 END IF;
1329
1330 IF P_DEBUG_MODE = 'Y' THEN
1331 log_message('Before calling Process_SuppInv_Dtls1 ',
1332 'Process_SuppInv_Dtls');
1333 END IF;
1334
1335 Process_SuppInv_Dtls1 (P_Project_Id
1336 ,X_return_status
1337 ,X_msg_count
1338 ,X_msg_data);
1339 IF P_DEBUG_MODE = 'Y' THEN
1340 log_message('After calling Process_SuppInv_Dtls1 ',
1341 'Process_SuppInv_Dtls');
1342 END IF;
1343
1344 END Process_SuppInv_Dtls;
1345
1346 END PAAP_PWP_PKG;