[Home] [Help]
PACKAGE BODY: APPS.PA_RETN_BILLING_PKG
Source
1 PACKAGE BODY pa_retn_billing_pkg AS
2 /* $Header: PAXIRTBB.pls 120.10 2010/03/31 15:27:10 dlella ship $ */
3
4 -- Function to get the Reten Invoice Format
5 -- Build the retention billing invoice format
6
7 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
8
9 Function Get_Retn_Billing_Inv_Format(p_project_id NUMBER)
10 RETURN pa_retn_billing_pkg.TabRetnBillingInvFmt IS
11
12 CURSOR cur_inv_group_columns IS SELECT grp.column_code column_code,
13 fmtdet.text text,
14 fmtdet.start_position start_position,
15 fmtdet.end_position end_position,
16 NVL(fmtdet.right_justify_flag,'N') right_justify_flag
17 FROM pa_invoice_group_columns grp,
18 pa_invoice_formats fmt,
19 pa_invoice_format_details fmtdet,
20 pa_projects_all pr
21 WHERE pr.retn_billing_inv_format_id = fmt.invoice_format_id
22 AND fmt.invoice_format_id = fmtdet.invoice_format_id
23 AND grp.invoice_group_column_id = fmtdet.invoice_group_column_id
24 and pr.project_id =p_project_id
25 ORDER BY fmtdet.start_position;
26
27 Cnt NUMBER :=0;
28
29 InvGroupColumnsRec cur_Inv_Group_columns%ROWTYPE;
30
31 TmpRetnLineFmt pa_retn_billing_pkg.TabRetnBillingInvFmt;
32
33 BEGIN
34 IF g1_debug_mode = 'Y' THEN
35 pa_retention_util.write_log('Entering pa_retn_billing_pkg.Get_Retn_Billing_Inv_Format');
36 END IF;
37
38 OPEN cur_inv_group_columns;
39 LOOP
40
41 FETCH cur_inv_group_columns INTO InvGroupColumnsRec;
42
43 EXIT WHEN cur_inv_group_columns%NOTFOUND;
44
45 cnt := cnt +1;
46
47 TmpRetnLineFmt(Cnt).column_code :=InvGroupColumnsRec.column_code;
48
49 TmpRetnLineFmt(Cnt).column_value :='N';
50
51 TmpRetnLineFmt(Cnt).usertext := InvGroupColumnsRec.text;
52
53 TmpRetnLineFmt(Cnt).start_position := InvGroupColumnsRec.start_position;
54 TmpRetnLineFmt(Cnt).end_position := InvGroupColumnsRec.end_position;
55 TmpRetnLineFmt(Cnt).right_justify_flag := InvGroupColumnsRec.right_justify_flag;
56
57 IF g1_debug_mode = 'Y' THEN
58 pa_retention_util.write_log('Get_Retn_Billing_Inv_Format: ' || 'Format Column : ' || InvGroupColumnsRec.column_code);
59 END IF;
60
61 END LOOP;
62
63 CLOSE cur_inv_group_columns;
64
65 IF g1_debug_mode = 'Y' THEN
66 pa_retention_util.write_log('Leaving pa_retn_billing_pkg.Get_Retn_Billing_Inv_Format');
67 END IF;
68
69 RETURN TmpRetnLineFmt;
70 EXCEPTION
71 WHEN OTHERS THEN
72 pa_retn_billing_pkg.G_ERROR_CODE :='E';
73 RETURN TmpRetnLineFmt;
74
75 END Get_Retn_Billing_Inv_Format;
76
77 FUNCTION CheckInvoiceExists( p_project_id IN NUMBER,
78 p_agreement_id IN NUMBER,
79 p_request_id IN VARCHAR2) RETURN VARCHAR2 IS
80
81 ExistsFlag VARCHAR2(1) := 'N';
82
83 BEGIN
84 IF g1_debug_mode = 'Y' THEN
85 pa_retention_util.write_log('Enterting pa_retn_billing_pkg.CheckInvoiceExists');
86 END IF;
87
88 BEGIN
89 SELECT 'Y'
90 INTO ExistsFlag
91 FROM DUAL
92 WHERE EXISTS(SELECT NULL
93 FROM pa_draft_invoices_all
94 WHERE project_id = p_project_id
95 AND agreement_id = p_agreement_id
96 AND request_id = p_request_id
97 AND NVL(retention_invoice_flag,'N') = 'Y');
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 ExistsFlag := 'N';
101 WHEN OTHERS THEN
102 pa_retn_billing_pkg.G_ERROR_CODE :='E';
103 RAISE;
104
105 END;
106 IF g1_debug_mode = 'Y' THEN
107 pa_retention_util.write_log('CheckInvoiceExists : ' || ExistsFlag);
108 pa_retention_util.write_log('Leaving pa_retn_billing_pkg.CheckInvoiceExists');
109 END IF;
110
111 RETURN(ExistsFlag);
112
113 END CheckInvoiceExists;
114
115 --- Procedure Build_Retn_Invoice_Header
116 --- Purpose This is used to Builed the invoice header
117 -- If the retention level is project level then it will create a
118 -- Invoice Header for each agreements.
119 -- If the retention level is Top Task, for an agreement, there will be
120 -- only one header
121
122 PROCEDURE Build_Retn_Invoice_Header(p_project_id IN NUMBER,
123 p_agreement_id IN NUMBER,
124 p_customer_id IN NUMBER,
125 p_request_id IN NUMBER,
126 x_draft_invoice_num OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
127 x_output_tax_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
128 X_Output_tax_exempt_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
129 X_Output_tax_exempt_number OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
130 X_Output_exempt_reason_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
131 ) IS
132
133 TmpCustBillSplit NUMBER;
134 TmpInvoiceDate DATE:= TO_DATE(pa_billing.globvars.InvoiceDate , 'YYYY/MM/DD');
135 TmpBillThruDate DATE:= TO_DATE(pa_billing.globvars.BillThruDate, 'YYYY/MM/DD');
136 TmpPADate DATE:= TO_DATE(pa_billing.globvars.PADate, 'YYYY/MM/DD');
137 TmpGLDate DATE:= TO_DATE(pa_billing.globvars.GLDate, 'YYYY/MM/DD');
138 TmpDraftInvoiceNUm NUMBER:=0;
139 TmpInvoiceSetId NUMBER;
140 TmpInvoiceComment VARCHAR2(240);
141 TmpInvCurrency VARCHAR2(15);
142 TmpInvCurrRateType VARCHAR2(30) :=NULL;
143 TmpInvCurrRateDate DATE :=null;
144 TmpInvCurrRate NUMBER := null;
145 TmpInvProcCurrency VARCHAR2(15);
146 TmpFundingCurrency VARCHAR2(15);
147 TmpBillToAddressID NUMBER;
148 TmpShipToAddressID NUMBER;
149 TmpRetnTaxCode VARCHAR2(30);
150 TmpLanguage VARCHAR2(30);
151 TmpCreditHold VARCHAR2(1);
152 TmpSiteUSeId1 NUMBER;
153 TmpSiteUSeId2 NUMBER;
154 TmpInvoiceNum NUMBER;
155
156
157 TmpProgId NUMBER:= fnd_global.conc_program_id;
158 TmpProgApplId NUMBER:= fnd_global.prog_appl_id;
159 l_program_update_date DATE := sysdate;
160 l_last_update_date DATE := sysdate;
161 l_last_updated_by NUMBER:= fnd_global.user_id;
162 l_last_update_login NUMBER:= fnd_global.login_id;
163 TmpUserId NUMBER:= fnd_global.user_id;
164
165 --Tmp_Output_vat_tax_id NUMBER;
166 Tmp_output_tax_code VARCHAR2(30);
167 Tmp_Output_tax_exempt_flag VARCHAR2(2);
168 --Tmp_Output_tax_exempt_number VARCHAR2(30); --Modified for Bug3128094
169 Tmp_Output_tax_exempt_number VARCHAR2(80);
170 Tmp_Output_exempt_reason_code VARCHAR2(30);
171 TmpSetofBooks NUMBER;
172
173 TmpWarningMsg VARCHAR2(80);
174 TmpWarningCode VARCHAR2(30);
175 TmpInvByBTC VARCHAR2(1);
176 TmpRetnBillInvFmtId NUMBER:=0;
177 TmpCustomerid NUMBER;
178 TmpBilltocustomerid NUMBER;
179 TmpShiptocustomerid NUMBER; /*Added for customer account relation enhancement 2760630*/
180 TmpBilltocontactid NUMBER;
181 TmpShiptocontactid NUMBER;
182 TmpPaymentSetid NUMBER; /*Federal Changes */
183
184 /* Shared services changes: local variable to store org ID from org context */
185 l_org_id NUMBER;
186 BEGIN
187 IF g1_debug_mode = 'Y' THEN
188 pa_retention_util.write_log('Entering pa_retn_billing_pkg.Build_Retn_Invoice_Header');
189 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'AgreementId : ' || p_agreement_id);
190 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Project Id : ' || p_project_id);
191 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Customer Id : ' || p_customer_id);
192 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Request Id : ' || p_request_id);
193 END IF;
194
195 /* TCA changes
196 select DISTINCT
197 nvl(cp1.credit_hold, cp.credit_hold),
198 to_char(c.customer_bill_split),
199 c.bill_to_address_id,
200 c.ship_to_address_id,
201 ras.site_use_id,
202 ras1.site_use_id,
203 addr.language,
204 a.agreement_currency_code,
205 pr.invoice_comment,
206 pr.retention_tax_code,
207 NVL(pr.inv_by_bill_trans_curr_flag ,'N'),
208 DECODE(pr.invproc_currency_type,
209 'PROJECT_CURRENCY',pr.project_currency_code,
210 'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
211 'FUNDING_CURRENCY', a.agreement_currency_code),
212 NVL(pr.retn_billing_inv_format_id,0),
213 c.customer_id,
214 c.bill_to_customer_id,
215 c.ship_to_customer_id
216 */
217 select DISTINCT
218 DECODE(hz_cp1.credit_hold,NULL,hz_cp.credit_hold,'N',hz_cp.credit_hold,hz_cp1.credit_hold), /* Modified for bug 9251471 */
219 to_char(c.customer_bill_split),
220 c.bill_to_address_id,
221 c.ship_to_address_id,
222 hz_site.site_use_id,
223 hz_site1.site_use_id,
224 addr.language,
225 a.agreement_currency_code,
226 pr.invoice_comment,
227 pr.retention_tax_code,
228 NVL(pr.inv_by_bill_trans_curr_flag ,'N'),
229 DECODE(pr.invproc_currency_type,
230 'PROJECT_CURRENCY',pr.project_currency_code,
231 'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
232 'FUNDING_CURRENCY', a.agreement_currency_code),
233 NVL(pr.retn_billing_inv_format_id,0),
234 c.customer_id,
235 c.bill_to_customer_id,
236 c.ship_to_customer_id,
237 a.payment_set_id
238 INTO
239 TmpCreditHold,
240 TmpCustBillSplit,
241 TmpBillToAddressID,
242 TmpShipToAddressID,
243 TmpSiteUSeId1,
244 TmpSiteUseId2,
245 TmpLanguage,
246 TmpFundingCurrency,
247 TmpInvoiceComment,
248 TmpRetnTaxCode,
249 TmpInvByBTC,
250 TmpInvProcCurrency,
251 TmpRetnBillInvFmtId,
252 TmpCustomerid,
253 TmpBilltocustomerid,
254 TmpShiptocustomerid,
255 TmpPaymentSetid
256 /* TCA changes
257 from ar_customer_profiles cp1,
258 ra_customers rc,
259 ra_customers rc1, --Added for customer account relation
260 ar_customer_profiles cp,
261 ra_site_uses ras,
262 pa_project_customers c,
263 pa_agreements_all a,
264 pa_projects pr,
265 ra_site_uses ras1,
266 ra_addresses addr
267 */
268 from hz_customer_profiles hz_cp1,
269 hz_cust_accounts hz_c,
270 hz_cust_accounts hz_c1,
271 hz_customer_profiles hz_cp,
272 hz_cust_site_uses hz_site,
273 pa_project_customers c,
274 pa_agreements_all a,
275 pa_projects pr,
276 hz_cust_site_uses hz_site1,
277 hz_cust_acct_sites addr
278 where a.agreement_id = p_agreement_id
279 and pr.project_id = p_project_id
280 and pr.project_id = c.project_id
281 and a.customer_id = c.customer_id
282 /* and c.customer_id = cp.customer_id commented for customer account relation enhancement*/
283 /* TCA changes
284 and c.bill_to_customer_id = cp.customer_id
285 and c.bill_to_customer_id = rc1.customer_id
286 and nvl(rc1.status,'A') = 'A'
287 */
288 and c.bill_to_customer_id = hz_cp.cust_account_id
289 and c.bill_to_customer_id = hz_c1.cust_account_id
290 and nvl(hz_c1.status,'A') = 'A'
291 /*End of change for customer account relation enhancement*/
292 /* TCA changes
293 and c.customer_id = rc.customer_id
294 */
295 and c.customer_id = hz_c.cust_account_id
296 -- and c.customer_bill_split <> 0 -- commented for FP_M Changes
297 and Decode( pr.Enable_Top_Task_Customer_Flag, 'Y', 100,
298 decode(pr.date_eff_funds_consumption, 'Y', 100, c.customer_bill_split )) <> 0 -- FP_M changes
299 /* TCA changes
300 and nvl(rc.status,'A') = 'A'
301 and cp.site_use_id is null
302 and ras.address_id = c.bill_to_address_id
303 and ras.site_use_code = 'BILL_TO'
304 and ras.status = 'A'
305 and ras1.address_id = c.ship_to_address_id
306 and ras1.site_use_code = 'SHIP_TO'
307 and ras1.status = 'A'
308 and addr.address_id = c.bill_to_address_id
309 and cp1.site_use_id(+) = ras.site_use_id
310 */
311 and nvl(hz_c.status,'A') = 'A'
312 and hz_cp.site_use_id is null
313 and hz_site.cust_acct_site_id = c.bill_to_address_id
314 and hz_site.site_use_code = 'BILL_TO'
315 and hz_site.status = 'A'
316 and hz_site1.cust_acct_site_id = c.ship_to_address_id
317 and hz_site1.site_use_code = 'SHIP_TO'
318 and hz_site1.status = 'A'
319 and addr.cust_acct_site_id = c.bill_to_address_id
320 and hz_cp1.site_use_id(+) = hz_site.site_use_id
321
322 /*Added for customer account relation enhancement bug no 2760630*/
323 and NOT EXISTS
324 (
325 /* Removed the existing code for perf bug 3607384 and added the below */
326 SELECT NULL
327 FROM PA_IMPLEMENTATIONS I
328 WHERE I.CUST_ACC_REL_CODE = 'Y'
329 AND exists ( select 1 from HZ_CUST_ACCT_RELATE HZ1,
330 PA_PROJECT_CUSTOMERS C
331 where C.PROJECT_ID=p_project_id
332 AND ( HZ1.CUST_ACCOUNT_ID(+) = C.CUSTOMER_ID
333 AND HZ1.RELATED_CUST_ACCOUNT_ID(+) = C.BILL_TO_CUSTOMER_ID
334 AND (NVL(HZ1.STATUS,'A') <>'A'
335 OR NVL(HZ1.BILL_TO_FLAG,'Y') <>'Y')
336 AND C.CUSTOMER_ID <> C.BILL_TO_CUSTOMER_ID
337 ))
338 UNION ALL
339 SELECT NULL
340 FROM PA_IMPLEMENTATIONS I
341 WHERE I.CUST_ACC_REL_CODE = 'Y'
342 AND exists ( select 1 from HZ_CUST_ACCT_RELATE HZ1,
343 PA_PROJECT_CUSTOMERS C
344 where C.PROJECT_ID=p_project_id
345 AND ( HZ1.CUST_ACCOUNT_ID(+) = C.CUSTOMER_ID
346 AND HZ1.RELATED_CUST_ACCOUNT_ID(+) = C.SHIP_TO_CUSTOMER_ID
347 AND (NVL(HZ1.STATUS,'A') <>'A'
348 OR NVL(HZ1.SHIP_TO_FLAG,'Y') <>'Y')
349 AND C.CUSTOMER_ID <> C.SHIP_TO_CUSTOMER_ID
350 ))
351 UNION ALL
352 SELECT NULL
353 FROM PA_IMPLEMENTATIONS I
354 WHERE I.cust_acc_rel_code = 'N'
355 AND exists (select 1 from PA_PROJECT_CUSTOMERS C
356 WHERE C.PROJECT_ID = p_project_id
357 AND ( C.CUSTOMER_ID <> C.BILL_TO_CUSTOMER_ID
358 OR C.CUSTOMER_ID<>C.SHIP_TO_CUSTOMER_ID))
359
360 );
361
362 SELECT MIN(PROJCON.Contact_ID),
363 decode(MAX(decode(ROLE.Primary_Flag, 'Y', CONT.Contact_ID, -1)),
364 -1, decode(MIN(CONT.Contact_ID), 0, NULL, MIN(CONT.Contact_ID)),
365 MAX(decode(ROLE.Primary_Flag, 'Y', CONT.Contact_ID, -1)))
366 INTO TmpBilltocontactid,
367 TmpShiptocontactid
368 FROM pa_project_contacts projcon,
369 pa_project_contacts cont,
370 pa_project_customers c,
371 hz_role_responsibility role,
372 /* TCA changes
373 ra_contact_roles role,
374 */
375 pa_agreements_all a /*Added for bug2984282*/
376 WHERE c.project_id=p_project_id
377 and a.agreement_id=p_agreement_id
378 and c.customer_id=p_customer_id/*Added for bug 2984282*/
379 and projcon.project_contact_type_code = 'BILLING'
380 and projcon.customer_id =c.customer_id
381 and projcon.project_ID = c.project_id
382 and cont.project_ID (+) = c.project_id
383 and cont.customer_ID (+) = c.customer_id
384 and cont.project_Contact_Type_Code (+) = 'SHIPPING'
385 /* TCA changes
386 and role.cust_account_role_id (+) = CONT.Contact_ID
387 and role.responsibility_type (+) = 'SHIP_TO'
388 */
389 and role.cust_account_role_id (+) = CONT.Contact_ID
390 and role.responsibility_type (+) = 'SHIP_TO'
391 and NOT EXISTS (SELECT NULL
392 FROM PA_PROJECT_CUSTOMERS c1
393 WHERE c1.project_id=p_project_id
394 AND NOT EXISTS
395 (
396 SELECT NULL
397 FROM pa_project_contacts projcon
398 WHERE projcon.project_contact_type_code = 'BILLING'
399 AND projcon.customer_id =c1.customer_id
400 AND projcon.project_ID = c1.project_id)
401 );
402
403 /* Bug#5689735 - Retention invoices are not generated if Billing Contact is not defined
404 Fix : Commented the existing code. Now retention invoice will get create if billing contact is not defined */
405
406 /*
407 IF TmpBilltocontactid IS NULL THEN
408 RAISE NO_DATA_FOUND;
409 END IF;
410 */
411
412 /*End of change for customer account relation enhancement bug no 2760630 */
413 PA_INVOICE_CURRENCY.get_proj_curr_info(p_project_id,
414 TmpInvCurrency);
415
416 pa_retn_billing_pkg.G_Inv_By_Bill_Trans_Currency := TmpInvByBtc;
417
418 IF pa_billing.Globvars.InvoiceSetId IS NULL THEN
419
420 -- Generate a set id, happens only user run the retention invoice generation
421
422 SELECT PA_DRAFT_INVOICES_S.NEXTVAL
423 INTO TmpInvoiceSetId
424 FROM DUAL;
425
426 ELSE
427 TmpInvoiceSetId := pa_billing.Globvars.InvoiceSetId;
428 END IF;
429
430 IF g1_debug_mode = 'Y' THEN
431 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'TmpInvoiceSetId : ' || TmpInvoiceSetId);
432 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Credit Hold : ' || TmpCreditHold);
433 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'TmpPaymentSetId : ' || TmpPaymentSetId);
434 END IF;
435
436
437 -- Get the new draft invoice num
438
439
440 SELECT NVL( MAX(p.draft_invoice_num) + 1, 1)
441 INTO TmpInvoiceNum
442 FROM pa_draft_invoices_all p
443 WHERE p.project_id = p_project_id;
444
445 IF g1_debug_mode = 'Y' THEN
446 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'New Invoice Num : ' || TmpInvoiceNum);
447 END IF;
448
449 --- Get the set of books id
450 SELECT imp.set_of_books_id
451 INTO TmpSetOfBooks
452 FROM pa_implementations imp;
453
454 IF g1_debug_mode = 'Y' THEN
455 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'TmpSetOfBooks : ' || TmpSetOfBooks);
456 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Call PA_OUTPUT_TAX.GET_DEFAULT_TAX_INFO');
457 END IF;
458
459 --- Call Tax Information
460
461 PA_OUTPUT_TAX.GET_DEFAULT_TAX_INFO
462 ( P_Project_Id=>p_project_id,
463 P_Draft_Inv_Num=>TmpInvoiceNum,
464 P_Customer_Id =>p_customer_id,
465 /* P_Bill_to_site_use_id=>TmpBillToAddressID,
466 P_Ship_to_site_use_id=>TmpShipToAddressID, commented for bug 2938422 */
467 P_Bill_to_site_use_id=>TmpSiteUSeId1,
468 P_Ship_to_site_use_id=>TmpSiteUSeId2,
469 P_Sets_of_books_id =>TmpSetOfBooks,
470 P_User_Id =>TmpUserId,
471 P_Request_id =>P_request_id,
472 -- X_Output_vat_tax_id=>Tmp_Output_vat_tax_id,
473 X_output_tax_code => Tmp_output_tax_code,
474 X_Output_tax_exempt_flag=>Tmp_Output_tax_exempt_flag ,
475 X_Output_tax_exempt_number =>Tmp_Output_tax_exempt_number,
476 X_Output_exempt_reason_code =>Tmp_Output_exempt_reason_code,
477 Pbill_to_customer_id =>TmpBilltocustomerid,
478 Pship_to_customer_id => TmpShiptocustomerid);
479 /*The last two parameters in the above call added for customer account relation
480 enhancement bug no 2760630*/
481
482 -- X_Output_vat_tax_id :=Tmp_Output_vat_tax_id;
483 X_output_tax_code := Tmp_Output_Tax_Code;
484 X_Output_tax_exempt_flag :=Tmp_Output_tax_exempt_flag ;
485 X_Output_tax_exempt_number :=Tmp_Output_tax_exempt_number;
486 X_Output_exempt_reason_code :=Tmp_Output_exempt_reason_code;
487
488 IF g1_debug_mode = 'Y' THEN
489 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Tax Information: ');
490 -- pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'X_Output_vat_tax_id : ' || X_Output_vat_tax_id);
491 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'X_Output_tax_code : ' || X_Output_tax_code);
492 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'X_Output_tax_exempt_flag : ' || X_Output_tax_exempt_flag);
493 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'X_Output_tax_exempt_number : ' || X_Output_tax_exempt_number);
494 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'X_Output_exempt_reason_code : ' || X_Output_exempt_reason_code);
495 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Invoice Currency : ' || TmpInvCurrency );
496 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Insert into Invoice Header ');
497 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Invoice Currency : ' || TmpInvCurrency );
498 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Bill By Bill Trans Currency : ' || TmpInvByBTC);
499 END IF;
500 IF TmpInvByBTC='Y' THEN
501 TmpInvCurrency := TmpInvProcCurrency;
502 IF g1_debug_mode = 'Y' THEN
503 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Assing IPC to Invoice Currency : ' || TmpInvCurrency);
504 END IF;
505 END IF;
506 IF g1_debug_mode = 'Y' THEN
507 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Invoice Currency : ' || TmpInvCurrency);
508 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Bill Thru Date : ' || pa_billing.GetBillThruDate);
509 END IF;
510
511 IF TmpCreditHold ='Y' OR NVL(TmpRetnBillInvFmtID,0) = 0 THEN
512
513 IF TmpCreditHold ='Y' THEN
514 -- If the customer is on credit-hold, insert a warning
515 -- TmpWarningMsg :='Customer has been put on billing hold.
516 -- Invoice cannot be generated.';
517
518 TmpWarningCode :='CREDIT_HOLD';
519
520
521 ELSIF TmpRetnBillInvFmtID = 0 THEN
522
523 TmpWarningCode :='NO_RETN_BILL_INVOICE_FORMAT';
524
525 END IF;
526
527 IF g1_debug_mode = 'Y' THEN
528 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Warning : ' || TmpWarningCode);
529 END IF;
530
531 SELECT lk.meaning
532 INTO TmpWarningMsg
533 FROM pa_lookups lk
534 WHERE lk.lookup_code = TmpWarningCode
535 AND lk.lookup_type = 'INVOICE DISTRIBUTION WARNING';
536
537 IF g1_debug_mode = 'Y' THEN
538 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Warning Code : ' || TmpWarningCode);
539 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Warning Mesg : ' || TmpWarningMsg);
540 END IF;
541 END IF;
542
543 /* Shared services changes: get org id from org context, and
544 insert it into table pa_draft_invoices as ORG_ID. */
545 l_org_id := MO_GLOBAL.get_current_org_id;
546
547 -- Insert a new invoice header
548 /*Last 5 columns added for customer account relation enhancement bug no 2760630*/
549 INSERT INTO PA_DRAFT_INVOICES (
550 DRAFT_INVOICE_NUM, PROJECT_ID,
551 AGREEMENT_ID, LAST_UPDATE_DATE,
552 LAST_UPDATED_BY, CREATION_DATE,
553 CREATED_BY, TRANSFER_STATUS_CODE,
554 GENERATION_ERROR_FLAG, PA_DATE,
555 REQUEST_ID, PROGRAM_APPLICATION_ID,
556 PROGRAM_ID, Program_Update_Date,
557 BILL_THROUGH_DATE, TRANSFER_REJECTION_REASON,
558 RETENTION_PERCENTAGE, Unearned_Revenue_CR,
559 Unbilled_Receivable_DR,
560 -- Invoice_Set_ID,
561 DRAFT_INVOICE_NUM_CREDITED, CUSTOMER_BILL_SPLIT,
562 INVOICE_COMMENT, INV_CURRENCY_CODE,
563 INV_RATE_TYPE,INV_RATE_DATE,INV_EXCHANGE_RATE,
564 BILL_TO_ADDRESS_ID,SHIP_TO_ADDRESS_ID,
565 LANGUAGE, INVPROC_CURRENCY_CODE,
566 INVOICE_DATE, GL_DATE,
567 PA_PERIOD_NAME,GL_PERIOD_NAME,
568 RETENTION_INVOICE_FLAG,
569 CUSTOMER_ID,BILL_TO_CUSTOMER_ID,SHIP_TO_CUSTOMER_ID,
570 BILL_TO_CONTACT_ID,SHIP_TO_CONTACT_ID,
571 ORG_ID, payment_set_id
572 ) VALUES
573 (TmpInvoiceNum, p_project_id,
574 p_agreement_id, SYSDATE,
575 TmpUserId, SYSDATE,
576 TmpUserId, 'P',
577 DECODE(TmpCreditHold,'Y','Y','N',DECODE(TmpRetnBillInvFmtId,0,'Y','N')),
578 pa_billing.GetPaDate,
579 p_request_id,
580 TmpProgApplId,
581 TmpProgId, SYSDATE,
582 TO_DATE(pa_billing.GetBillThruDate, 'YYYY/MM/DD'),
583 /*DECODE(TmpCreditHold,'Y','Y','N',DECODE(TmpRetnBillInvFmtId,0,
584 TmpWarningMsg,Null)),*/
585 DECODE(TmpCreditHold,'Y',TmpWarningMsg,'N',DECODE(TmpRetnBillInvFmtId,0, TmpWarningMsg,Null)),
586 null, NULL,
587 NULL,
588 -- TmpInvoiceSetId,
589 NULL, TmpCustBillSplit,
590 TmpInvoiceComment,
591 TmpInvCurrency,
592 TmpInvCurrRateType,TmpInvCurrRateDate,
593 TmpInvCurrRate,TmpBillToAddressID,
594 TmpShipToAddressID,
595 TmpLanguage,
596 NVL(TmpInvProcCurrency,TmpFundingCurrency),
597 TRUNC(TmpInvoiceDate),
598 --TRUNC(TO_DATE(TmpInvoiceDate, 'YYYY/MM/DD')),
599 pa_billing.GetGlDate,
600 pa_billing.getpaperiodname,
601 pa_billing.getglperiodname,
602 'Y',
603 TmpCustomerid,
604 TmpBilltocustomerid,
605 TmpShiptocustomerid,
606 TmpBilltocontactid,
607 TmpShiptocontactid,
608 l_org_id,
609 TmpPaymentSetid);
610
611 x_draft_invoice_num := TmpInvoiceNum;
612
613 IF TmpCreditHold ='Y' OR NVL(TmpRetnBillInvFmtID,0) = 0 THEN
614
615 IF g1_debug_mode = 'Y' THEN
616 pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Insert Warning ');
617 END IF;
618
619
620 INSERT INTO PA_DISTRIBUTION_WARNINGS (
621 DRAFT_INVOICE_NUM, PROJECT_ID,
622 LAST_UPDATE_DATE, LAST_UPDATED_BY,
623 CREATION_DATE, CREATED_BY,
624 REQUEST_ID, PROGRAM_APPLICATION_ID,
625 PROGRAM_ID, PROGRAM_UPDATE_DATE,
626 WARNING_MESSAGE, WARNING_MESSAGE_CODE)
627 VALUES
628 ( TmpInvoiceNum, p_project_id,
629 SYSDATE, TmpUserId, SYSDATE,
630 TmpUserId, p_request_id, TmpProgApplId,
631 TmpProgId, SYSDATE,
632 TmpWarningMsg, TmpWarningCode);
633
634 -- This insert is for project level rejection reason. This will be shown
635 -- in the invoice exception report
636 IF NVL(TmpRetnBillInvFmtID,0) = 0 THEN
637
638 INSERT INTO PA_DISTRIBUTION_WARNINGS (
639 DRAFT_INVOICE_NUM, PROJECT_ID,
640 LAST_UPDATE_DATE, LAST_UPDATED_BY,
641 CREATION_DATE, CREATED_BY,
642 REQUEST_ID, PROGRAM_APPLICATION_ID,
643 PROGRAM_ID, PROGRAM_UPDATE_DATE,
644 WARNING_MESSAGE, WARNING_MESSAGE_CODE)
645 VALUES
646 ( null, p_project_id,
647 SYSDATE, TmpUserId, SYSDATE,
648 TmpUserId, p_request_id, TmpProgApplId,
649 TmpProgId, SYSDATE,
650 TmpWarningMsg, TmpWarningCode);
651
652 END IF;
653
654
655 END IF;
656
657 IF g1_debug_mode = 'Y' THEN
658 pa_retention_util.write_log('Leaving Build_Retn_Invoice_Header ');
659 END IF;
660
661
662
663 EXCEPTION
664 /*Added for bug no 2760630 */
665 WHEN NO_DATA_FOUND THEN
666 x_output_tax_code := NULL; --NOCOPY
667 X_Output_tax_exempt_flag := NULL; --NOCOPY
668 X_Output_tax_exempt_number := NULL; --NOCOPY
669 X_Output_exempt_reason_code:= NULL; --NOCOPY
670 RAISE NO_DATA_FOUND;
671 /*End of change for bug no 2760630 */
672 WHEN OTHERS THEN
673 pa_retn_billing_pkg.G_ERROR_CODE :='E';
674 IF g1_debug_mode = 'Y' THEN
675 pa_retention_util.write_log('pa_retn_billing_pkg.Build_Retn_Invoice_Header' || ' Oracle Error : ' || sqlerrm);
676 END IF;
677 RAISE;
678
679 END Build_Retn_Invoice_Header;
680
681 PROCEDURE Update_ProjFunc_Attributes( p_project_id IN NUMBER,
682 p_draft_invoice_num IN NUMBER) IS
683 l_projfunc_invtrans_rate NUMBER:=0;
684 l_inv_amount NUMBER:=0;
685 l_pfc_amount NUMBER:=0;
686 BEGIN
687
688 SELECT NVL(sum(dii.inv_amount),0),
689 NVL(sum(dii.projfunc_bill_amount),0)
690 INTO l_inv_amount,
691 l_pfc_amount
692 FROM pa_draft_invoice_items dii
693 WHERE dii.project_id = p_project_id
694 AND dii.draft_invoice_num = p_draft_invoice_num;
695
696 IF NVL(l_inv_amount,0) <> 0 AND NVL(l_pfc_amount,0) <> 0 THEN
697 l_projfunc_invtrans_rate := NVL(l_inv_amount,0)/NVL(l_pfc_amount,0);
698 END IF;
699
700 UPDATE pa_draft_invoices_all
701 set inv_rate_date = NULL,
702 inv_exchange_rate = NULL,
703 projfunc_invtrans_rate_type = 'User',
704 projfunc_invtrans_rate_date = sysdate,
705 projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
706 WHERE project_id = P_Project_Id
707 AND draft_invoice_num = p_draft_invoice_num;
708 EXCEPTION
709 WHEN OTHERS THEN
710 pa_retn_billing_pkg.G_ERROR_CODE :='E';
711 IF g1_debug_mode = 'Y' THEN
712 pa_retention_util.write_log('pa_retn_billing_pkg.Update_ProjFunc_Attributes' || ' Oracle Error : ' || sqlerrm);
713 END IF;
714
715 END Update_ProjFunc_Attributes;
716
717 -- Procedure Create_Retn_Invoice_Lines
718 -- Purpose To Create a retention invoice lines.
719 -- For project level retention, there will be always one line
720 -- For Top Task level retention, it could be more than one
721
722 PROCEDURE Create_Retn_Invoice_Lines( p_project_id IN NUMBER,
723 p_customer_id IN NUMBER,
724 /*p_task_id IN NUMBER DEFAULT NULL, bug 2681003
725 removed the default value from body for GSCC warnings */
726 p_task_id IN NUMBER ,
727 p_agreement_id IN NUMBER,
728 p_draft_invoice_num IN NUMBER,
729 p_request_id IN NUMBER,
730 p_invproc_currency IN VARCHAR2,
731 p_projfunc_currency IN VARCHAR2,
732 p_project_currency IN VARCHAR2,
733 p_funding_currency IN VARCHAR2,
734 p_projfunc_amount IN NUMBER,
735 p_project_amount IN NUMBER,
736 p_funding_amount IN NUMBER,
737 p_invproc_amount IN NUMBER,
738 p_billing_method IN VARCHAR2,
739 p_billing_method_code IN VARCHAR2,
740 p_method_value IN VARCHAR2,
741 p_total_retained IN NUMBER,
742 p_billing_percentage IN NUMBER,
743 p_billing_amount IN NUMBER,
744 p_output_tax_code IN VARCHAR2,
745 p_Output_tax_exempt_flag IN VARCHAR2,
746 p_Output_tax_exempt_number IN VARCHAR2,
747 p_Output_exempt_reason_code IN VARCHAR2,
748 p_comp_percent IN NUMBER,
749 p_bill_cycle_id IN NUMBER,
750 p_TotRetenion IN NUMBER,
751 p_client_extn_flag IN VARCHAR2) IS
752 LastLineNum NUMBER:=0;
753
754 LastUpdatedBy NUMBER:= fnd_global.user_id;
755 l_created_by NUMBER:= fnd_global.user_id;
756 l_program_id NUMBER:= fnd_global.conc_program_id;
757 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
758 l_program_update_date DATE := sysdate;
759 l_last_update_date DATE := sysdate;
760 l_last_updated_by NUMBER:= fnd_global.user_id;
761 l_last_update_login NUMBER:= fnd_global.login_id;
762
763 RetnLineText VARCHAR2(500);
764 TempText VARCHAR2(80);
765 l_RetnInvLineFmt pa_retn_billing_pkg.TabRetnBillingInvFmt;
766 LastEndPosition NUMBER:=0;
767
768 l_task_name VARCHAR2(20);
769
770
771 BEGIN
772
773 IF g1_debug_mode = 'Y' THEN
774 pa_retention_util.write_log('Entering Create_Retn_Invoice_Lines ');
775 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Project Id : ' || p_project_id);
776 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Task Id : ' || p_task_id);
777 END IF;
778
779 -- Get task name
780 IF g1_debug_mode = 'Y' THEN
781 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Get task name');
782 END IF;
783
784 BEGIN
785
786 IF NVL(p_task_id,0) <> 0 THEN
787
788 SELECT LTRIM(RTRIM(task_name)) || ' ' INTO l_task_name FROM pa_tasks
789 WHERE task_id = p_task_id;
790
791 ELSE
792
793 l_task_name := NULL;
794
795 END IF;
796
797
798
799 EXCEPTION
800
801 WHEN OTHERS THEN
802
803 l_task_name := NULL;
804
805 END;
806 IF g1_debug_mode = 'Y' THEN
807 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Task Name : ' || l_task_name);
808 END IF;
809 -- Find out any lines are existing or not
810 IF g1_debug_mode = 'Y' THEN
811 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'CAll pa_retention_pkg.Get_Invoice_Max_Line');
812 END IF;
813
814 LastLineNum := pa_retention_pkg.Get_Invoice_Max_Line(p_project_id=>p_project_id,
815 p_draft_Invoice_num=>p_draft_invoice_num);
816
817 IF g1_debug_mode = 'Y' THEN
818 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'CAll Get_Retn_Billing_Inv_Format');
819 END IF;
820
821 l_RetnInvLineFmt := Get_Retn_Billing_Inv_Format(p_project_id=>p_project_id);
822
823
824 -- Building the retention line text
825 RetnLineText :=NULL;
826
827 IF l_RetnInvLineFmt.count <> 0 THEN
828
829 FOR i IN 1..l_RetnInvLineFmt.Count LOOP
830
831 -- Set the last end position
832 IF NVL(i,0) = 1 THEN
833 LastEndPosition := l_RetnInvLineFmt(i).end_position;
834 RetnLineText := RPAD(RetnLineText,
835 l_RetnInvLineFmt(i).start_position-1,' ');
836 ELSE
837 RetnLineText :=
838 RetnLineText ||
839 RPAD(' ',
840 l_RetnInvLineFmt(i).start_position-
841 LastEndPosition,' ');
842
843 LastEndPosition := l_RetnInvLineFmt(i).end_position;
844
845 END IF;
846
847 IF g1_debug_mode = 'Y' THEN
848 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || i || ': Fmt Text : ' || RetnLineText);
849 END IF;
850
851 IF l_RetnInvLineFmt(i).column_code = 'RETENTION BILLING PERCENTAGE' THEN
852
853 TempText := NULL;
854
855 IF NVL(p_billing_percentage,0) <> 0 THEN
856
857 TempText := TO_CHAR(p_billing_percentage);
858 ELSE
859 TempText := TO_CHAR(p_billing_amount);
860
861 END IF;
862
863 IF l_RetnInvLineFmt(i).right_justify_flag = 'Y' THEN
864 RetnLineText := RetnLineText ||
865 RPAD(TempText,
866 l_RetnInvLineFmt(i).end_position-
867 l_RetnInvLineFmt(i).start_position,' ');
868 ELSE
869 RetnLineText := RetnLineText ||
870 LPAD(TempText,
871 l_RetnInvLineFmt(i).end_position-
872 l_RetnInvLineFmt(i).start_position,' ');
873 END IF;
874
875 IF g1_debug_mode = 'Y' THEN
876 pa_retention_util.write_log('% Text : ' || NVL(p_billing_percentage,p_billing_amount));
877 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'After % Text : ' || RetnLineText);
878 END IF;
879
880 ELSIF l_RetnInvLineFmt(i).column_code = 'TEXT' THEN
881
882 IF l_RetnInvLineFmt(i).right_justify_flag = 'Y' THEN
883 RetnLineText := RetnLineText ||
884 RPAD(l_RetnInvLineFmt(i).UserText,
885 l_RetnInvLineFmt(i).end_position-
886 l_RetnInvLineFmt(i).start_position,' ');
887 ELSE
888 RetnLineText := RetnLineText ||
889 LPAD( l_RetnInvLineFmt(i).UserText,
890 l_RetnInvLineFmt(i).end_position-
891 l_RetnInvLineFmt(i).start_position,' ');
892 END IF;
893
894 IF g1_debug_mode = 'Y' THEN
895 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'User Text : ' || l_RetnInvLineFmt(i).UserText);
896 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'User Text : ' || RetnLineText);
897 END IF;
898
899 ELSIF l_RetnInvLineFmt(i).column_code = 'INVPROC CURRENCY CODE' THEN
900
901 IF l_RetnInvLineFmt(i).right_justify_flag = 'Y' THEN
902 RetnLineText := RetnLineText ||
903 RPAD(p_invproc_currency,
904 l_RetnInvLineFmt(i).end_position-
905 l_RetnInvLineFmt(i).start_position,' ');
906 ELSE
907 RetnLineText := RetnLineText ||
908 LPAD( p_invproc_currency,
909 l_RetnInvLineFmt(i).end_position-
910 l_RetnInvLineFmt(i).start_position,' ');
911 END IF;
912 IF g1_debug_mode = 'Y' THEN
913 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'IPC : ' || p_invproc_currency);
914 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'IPC Text : ' || RetnLineText);
915 END IF;
916
917 ELSIF l_RetnInvLineFmt(i).column_code = 'BILLING METHOD' THEN
918
919 IF l_RetnInvLineFmt(i).right_justify_flag = 'Y' THEN
920 RetnLineText := RetnLineText ||
921 RPAD(p_billing_method,
922 l_RetnInvLineFmt(i).end_position-
923 l_RetnInvLineFmt(i).start_position,' ');
924 ELSE
925 RetnLineText := RetnLineText ||
926 LPAD(p_billing_method,
927 l_RetnInvLineFmt(i).end_position-
928 l_RetnInvLineFmt(i).start_position,' ');
929 END IF;
930 IF g1_debug_mode = 'Y' THEN
931 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Billing Method : ' || p_billing_method);
932 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'After BM Text : ' || RetnLineText);
933 END IF;
934
935 ELSIF l_RetnInvLineFmt(i).column_code = 'METHOD VALUE' THEN
936
937 IF l_RetnInvLineFmt(i).right_justify_flag = 'Y' THEN
938 RetnLineText := RetnLineText ||
939 RPAD(p_method_value,
940 l_RetnInvLineFmt(i).end_position-
941 l_RetnInvLineFmt(i).start_position,' ');
942 ELSE
943 RetnLineText := RetnLineText ||
944 LPAD( p_method_value,
945 l_RetnInvLineFmt(i).end_position-
946 l_RetnInvLineFmt(i).start_position,' ');
947 END IF;
948 IF g1_debug_mode = 'Y' THEN
949 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Method Value : ' || p_method_value);
950 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'After MV Text : ' || RetnLineText);
951 END IF;
952
953
954 ELSIF l_RetnInvLineFmt(i).column_code = 'TOTAL RETAINED AMOUNT' THEN
955
956 IF l_RetnInvLineFmt(i).right_justify_flag = 'Y' THEN
957 RetnLineText := RetnLineText ||
958 RPAD(TO_CHAR(p_total_retained),
959 l_RetnInvLineFmt(i).end_position-
960 l_RetnInvLineFmt(i).start_position,' ');
961 ELSE
962 RetnLineText := RetnLineText ||
963 LPAD(TO_CHAR(p_total_retained),
964 l_RetnInvLineFmt(i).end_position-
965 l_RetnInvLineFmt(i).start_position,' ');
966 END IF;
967 IF g1_debug_mode = 'Y' THEN
968 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Total Retained : ' || p_total_retained);
969 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'After TR Text : ' || RetnLineText);
970 END IF;
971
972
973 END IF;
974
975 END LOOP;
976
977 END IF;
978
979 IF g1_debug_mode = 'Y' THEN
980 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'RetnLineText : ' || RetnLineText);
981 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Insert DII');
982 END IF;
983
984 INSERT INTO pa_draft_invoice_items
985 ( PROJECT_ID,
986 DRAFT_INVOICE_NUM,
987 LINE_NUM,
988 AMOUNT,
989 TEXT,
990 INVOICE_LINE_TYPE,
991 PROJFUNC_CURRENCY_CODE,
992 PROJFUNC_BILL_AMOUNT,
993 PROJECT_CURRENCY_CODE,
994 PROJECT_BILL_AMOUNT,
995 FUNDING_CURRENCY_CODE,
996 FUNDING_BILL_AMOUNT,
997 INVPROC_CURRENCY_CODE,
998 LAST_UPDATE_LOGIN,
999 LAST_UPDATE_DATE,
1000 LAST_UPDATED_BY,
1001 CREATION_DATE,
1002 CREATED_BY,
1003 REQUEST_ID,
1004 PROGRAM_APPLICATION_ID,
1005 PROGRAM_ID,
1006 PROGRAM_UPDATE_DATE,
1007 OUTPUT_TAX_CLASSIFICATION_CODE,
1008 OUTPUT_TAX_EXEMPT_FLAG,
1009 /* Bug 3087998 Code and number order is different in values list. Changing here to match the same
1010 OUTPUT_TAX_EXEMPT_REASON_CODE,
1011 OUTPUT_TAX_EXEMPT_NUMBER,
1012 */
1013 OUTPUT_TAX_EXEMPT_NUMBER,
1014 OUTPUT_TAX_EXEMPT_REASON_CODE,
1015 INV_AMOUNT,
1016 RETN_BILLING_METHOD,
1017 RETN_PERCENT_COMPLETE,
1018 RETN_TOTAL_RETENTION,
1019 RETN_CLIENT_EXTENSION_FLAG,
1020 RETN_BILLING_CYCLE_ID,
1021 RETN_BILLING_PERCENTAGE,
1022 RETN_BILLING_AMOUNT,
1023 task_id)
1024 VALUES (p_project_id,
1025 p_draft_invoice_num,
1026 LastLineNum,
1027 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_invproc_amount,p_invproc_currency),
1028 SUBSTR(l_task_name || SUBSTR(RetnLineText,1,LastEndPosition),1,240),
1029 'RETENTION',
1030 p_projfunc_currency,
1031 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_projfunc_amount,p_projfunc_currency),
1032 p_project_currency,
1033 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_project_amount,p_project_currency),
1034 p_funding_currency,
1035 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_funding_amount,p_funding_currency),
1036 p_invproc_currency,
1037 l_last_update_login,
1038 sysdate,
1039 l_last_updated_by,
1040 sysdate,
1041 l_created_by,
1042 p_request_id,
1043 l_program_application_id,
1044 l_program_id,
1045 sysdate,
1046 p_Output_tax_code,
1047 p_Output_tax_exempt_flag ,
1048 p_Output_tax_exempt_number ,
1049 p_Output_exempt_reason_code,
1050 DECODE(pa_retn_billing_pkg.G_Inv_By_Bill_Trans_Currency,
1051 'Y',
1052 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_invproc_amount,
1053 p_invproc_currency),
1054 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1055 p_projfunc_amount,p_projfunc_currency)),
1056 p_billing_method_code,
1057 p_comp_percent,
1058 p_TotRetenion,
1059 p_client_extn_flag,
1060 p_bill_cycle_id,
1061 p_billing_percentage,
1062 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1063 p_billing_amount,
1064 p_invproc_currency),
1065 p_task_id);
1066
1067 --- Create the retention invoice details
1068 IF g1_debug_mode = 'Y' THEN
1069 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Call Create_Retn_Invoice_Details');
1070 END IF;
1071
1072 Create_Retn_Invoice_Details ( p_project_id=>p_project_id,
1073 p_draft_invoice_num=>p_draft_invoice_num,
1074 p_task_id => p_task_id,
1075 p_line_num=>LastLineNum,
1076 p_agreement_id=>p_agreement_id,
1077 p_request_id=>p_request_id);
1078
1079 -- Update the balances
1080
1081 IF g1_debug_mode = 'Y' THEN
1082 pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Call pa_retention_pkg.Update_Retention_Balances');
1083 END IF;
1084
1085 pa_retention_pkg.Update_Retention_Balances(
1086 p_project_id=>p_project_id,
1087 p_agreement_id=>p_agreement_id,
1088 p_task_id=>p_task_id,
1089 p_customer_id=>p_customer_id,
1090 p_amount =>p_invproc_amount,
1091 p_change_type =>'BILLED',
1092 p_request_id =>p_request_id,
1093 p_invproc_currency=>p_invproc_currency,
1094 p_project_currency=>p_project_currency,
1095 p_project_amount =>p_project_amount,
1096 p_projfunc_currency =>p_projfunc_currency,
1097 p_projfunc_amount =>p_projfunc_amount,
1098 p_funding_currency =>p_funding_currency,
1099 p_funding_amount =>p_funding_amount);
1100
1101 IF g1_debug_mode = 'Y' THEN
1102 pa_retention_util.write_log('Leaving pa_retn_billing_pkg.Create_Retn_Invoice_Lines');
1103 END IF;
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 pa_retn_billing_pkg.G_ERROR_CODE :='E';
1107 IF g1_debug_mode = 'Y' THEN
1108 pa_retention_util.write_log('pa_retn_billing_pkg.Create_Retn_Invoice_Lines : Oracle Error : ' || sqlerrm);
1109 END IF;
1110
1111 END Create_Retn_Invoice_Lines;
1112
1113 --- Procedure Create_Retn_Invoice_Details
1114 -- Purpose This process will baseline the retained amount at rule level
1115 -- This detail will become a history of the retention invoice line
1116
1117 PROCEDURE Create_Retn_Invoice_Details (
1118 p_project_id IN NUMBER,
1119 /*p_task_id IN NUMBER DEFAULT NULL, bug 2681003
1120 removed the default value from body for GSCC warnings */
1121 p_task_id IN NUMBER,
1122 p_draft_invoice_num IN NUMBER,
1123 p_line_num IN NUMBER,
1124 p_agreement_id IN NUMBER,
1125 p_request_id IN NUMBER) IS
1126
1127 CURSOR cur_proj_retn IS
1128 SELECT
1129 project_retention_id,
1130 project_id,
1131 task_id,
1132 invproc_currency_code,
1133 total_retained,
1134 projfunc_currency_code,
1135 projfunc_total_retained,
1136 project_currency_code,
1137 project_total_retained,
1138 funding_currency_code,
1139 funding_total_retained
1140 FROM pa_project_retentions
1141 WHERE project_id = p_project_id
1142 AND agreement_id = p_agreement_id
1143 AND nvl(task_id,-99) = NVL(p_task_id,-99);
1144
1145 ProjRetnRec cur_proj_retn%ROWTYPE;
1146
1147 LastUpdatedBy NUMBER:= fnd_global.user_id;
1148 l_created_by NUMBER:= fnd_global.user_id;
1149 l_program_id NUMBER:= fnd_global.conc_program_id;
1150 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
1151 l_program_update_date DATE := sysdate;
1152 l_last_update_date DATE := sysdate;
1153 l_last_updated_by NUMBER:= fnd_global.user_id;
1154 l_last_update_login NUMBER:= fnd_global.login_id;
1155 l_detail_id NUMBER;
1156
1157
1158 BEGIN
1159 IF g1_debug_mode = 'Y' THEN
1160 pa_retention_util.write_log('Entering pa_retn_billing_pkg.Create_Retn_Invoice_Details');
1161 pa_retention_util.write_log('Create_Retn_Invoice_Details: ' || 'Project Id : ' || p_project_id);
1162 pa_retention_util.write_log('Create_Retn_Invoice_Details: ' || 'Task Id : ' || p_task_id);
1163 pa_retention_util.write_log('Create_Retn_Invoice_Details: ' || 'Agreement Id : ' || p_agreement_id);
1164 END IF;
1165
1166 OPEN cur_proj_retn;
1167
1168 LOOP
1169
1170 FETCH cur_proj_retn INTO ProjRetnRec;
1171
1172 EXIT WHEN cur_proj_retn%NOTFOUND;
1173
1174
1175 -- Select the sequence values
1176
1177 SELECT pa_retn_invoice_details_s.NEXTVAL
1178 INTO l_detail_id
1179 FROM DUAL;
1180
1181 IF g1_debug_mode = 'Y' THEN
1182 pa_retention_util.write_log('Create_Retn_Invoice_Details: ' || 'Insert pa_retn_invoice_details');
1183 END IF;
1184
1185 -- Insert into the Retention Invoice Detail table
1186 INSERT INTO pa_retn_invoice_details
1187 ( RETN_INVOICE_DETAIL_ID,
1188 PROJECT_ID,
1189 DRAFT_INVOICE_NUM,
1190 LINE_NUM,
1191 PROJECT_RETENTION_ID,
1192 TOTAL_RETAINED,
1193 INVPROC_CURRENCY_CODE,
1194 PROJFUNC_CURRENCY_CODE,
1195 PROJFUNC_TOTAL_RETAINED,
1196 PROJECT_CURRENCY_CODE,
1197 PROJECT_TOTAL_RETAINED,
1198 FUNDING_CURRENCY_CODE,
1199 FUNDING_TOTAL_RETAINED,
1200 PROGRAM_APPLICATION_ID,
1201 PROGRAM_ID,
1202 PROGRAM_UPDATE_DATE,
1203 REQUEST_ID,
1204 CREATION_DATE,
1205 CREATED_BY,
1206 LAST_UPDATE_DATE,
1207 LAST_UPDATED_BY)
1208 VALUES( l_detail_id,
1209 p_project_id,
1210 p_draft_invoice_num,
1211 p_line_num,
1212 ProjRetnRec.project_retention_id,
1213 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.total_retained,
1214 ProjRetnRec.invproc_currency_code),
1215 ProjRetnRec.invproc_currency_code,
1216 ProjRetnRec.projfunc_currency_code,
1217 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.projfunc_total_retained,
1218 ProjRetnRec.projfunc_currency_code),
1219 ProjRetnRec.project_currency_code,
1220 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.project_total_retained,
1221 ProjRetnRec.project_currency_code),
1222 ProjRetnRec.funding_currency_code,
1223 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.funding_total_retained,
1224 ProjRetnRec.funding_currency_code),
1225 l_program_application_id,
1226 l_program_id,
1227 l_program_update_date,
1228 p_request_id,
1229 sysdate,
1230 l_created_by,
1231 l_last_update_date,
1232 LastUpdatedBy);
1233
1234 END LOOP;
1235
1236 CLOSE cur_proj_retn;
1237
1238 IF g1_debug_mode = 'Y' THEN
1239 pa_retention_util.write_log('Leaving pa_retn_billing_pkg.Create_Retn_Invoice_Details');
1240 END IF;
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 pa_retn_billing_pkg.G_ERROR_CODE :='E';
1244 IF g1_debug_mode = 'Y' THEN
1245 pa_retention_util.write_log('pa_retn_billing_pkg.Create_Retn_Invoice_Details
1246 : Oracle Error : ' || sqlerrm);
1247 END IF;
1248
1249 END Create_Retn_Invoice_Details;
1250
1251 -- Procedure Retention_Billing_Processing
1252 -- Purpose This process will be called from paisql
1253 -- Process will list all the project which has to generate the retention
1254 -- invoice
1255
1256 Procedure Retention_Billing_Processing (p_request_id IN NUMBER,
1257 p_start_proj_number IN VARCHAR2,
1258 p_end_proj_number IN VARCHAR2,
1259 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1260 TmpCustomerId NUMBER;
1261 TmpProjectId NUMBER;
1262
1263 /* R12 : ATG changes : removed to_date function */
1264
1265 TmpInvoiceDate DATE:= TO_DATE(pa_billing.globvars.InvoiceDate, 'YYYY/MM/DD');
1266
1267 /* Bug 3258465 Added this temporary variable to fetch the Bill through date*/
1268 TmpBillThruDate DATE := TO_DATE(pa_billing.globvars.BillThruDate,'YYYY/MM/DD');
1269
1270 CurRetained NUMBER:=0;
1271 CurBillAmount NUMBER:=0;
1272 TmpBillAmount NUMBER:=0;
1273 Tmprelactive NUMBER:=0;
1274
1275 /* Misc bill enhan modified where clause based on p_project_type_id, p_project_org_id, p_agreement_id,
1276 p_customer_id, p_mcb_flag */
1277
1278 CURSOR cur_proj_cust_retn IS SELECT pc.project_id project_id,
1279 pc.customer_id customer_id,
1280 pc.retention_level_code retention_level,
1281 NVL(pr.RETN_BILLING_INV_FORMAT_ID,0) RETN_BILLING_INV_FORMAT_ID
1282 /* TCA changes
1283 FROM pa_project_customers pc, pa_projects_all pr, ra_customers c
1284 */
1285 FROM pa_project_customers pc, pa_projects_all pr, hz_parties hz_p, hz_cust_accounts hz_c
1286 WHERE EXISTS(
1287 SELECT NULL FROM pa_summary_project_retn spr,
1288 pa_proj_retn_bill_rules rt
1289 WHERE rt.project_id = spr.project_id
1290 AND rt.customer_id = spr.customer_id
1291 AND spr.customer_id = pc.customer_id
1292 AND spr.project_id = pc.project_id
1293 AND (NVL(spr.total_retained,0) - NVL(spr.total_billed,0) ) > 0)
1294 AND pr.project_id = pc.project_id
1295 /* TCA changes
1296 AND pc.customer_id = c.customer_id
1297 */
1298 AND pc.customer_id = hz_c.cust_account_id
1299 AND hz_p.party_id = hz_c.party_id
1300 AND pr.segment1 between p_start_proj_number and p_end_proj_number
1301 /*
1302 AND ( nvl (p_agreement_id,0) = 0
1303 OR EXISTS (select null
1304 from pa_summary_project_fundings SPF
1305 where spf.project_id = pr.project_id
1306 and spf.agreement_id = p_agreement_id
1307 and spf.total_baselined_amount > 0)
1308 )
1309 AND ( nvl(p_customer_id,0) = 0
1310 OR pc.customer_id = p_customer_id
1311 )
1312 AND (nvl(p_project_type_id, 0) = 0
1313 OR EXISTS ( select null
1314 from pa_project_types pt
1315 where pr.project_type = pt.project_type
1316 and pt.project_type_id = p_project_type_id)
1317 )
1318 AND ( nvl(p_project_org_id,0) = 0
1319 OR pr.carrying_out_organization_id = p_project_org_id
1320 )
1321 AND (p_mcb_flag = 'N'
1322 OR nvl(pr.multi_currency_billing_flag, 'N') = 'Y')
1323 */
1324 /* TCA changes
1325 order by pr.segment1, c.customer_name;
1326 */
1327 order by pr.segment1, hz_p.party_name;
1328
1329 ProjCustRec cur_proj_cust_retn%ROWTYPE;
1330
1331 CURSOR cur_retn_billing_rules IS SELECT retn1.billing_method_code billing_method,
1332 retn1.retn_billing_percentage bill_percentage,
1333 retn1.retn_billing_amount bill_amount,
1334 retn1.total_retention_amount total_retention_amount,
1335 retn1.retn_billing_cycle_id billing_cycle_id,
1336 retn1.completed_percentage completed_percentage,
1337 retn1.client_extension_flag client_extn_flag,
1338 cy.billing_cycle_name billing_cycle_name,
1339 lk.meaning billingMethodDesc,
1340 retn1.task_id task_id
1341 FROM pa_proj_retn_bill_rules retn1,
1342 pa_billing_cycles cy,
1343 pa_lookups lk
1344 WHERE retn1.project_id = TmpProjectId
1345 AND retn1.customer_id= TmpCustomerId
1346 AND lk.lookup_code = retn1.billing_method_code
1347 AND lk.lookup_type ='RETN_BILLING_METHOD'
1348 AND retn1.billing_method_code <> 'PERCENT_COMPLETE'
1349 AND retn1.retn_billing_cycle_id = cy.billing_cycle_id(+)
1350 UNION
1351 SELECT
1352 retn.billing_method_code billing_method,
1353 retn.retn_billing_percentage bill_percentage,
1354 retn.retn_billing_amount bill_amount,
1355 retn.total_retention_amount total_retention_amount ,
1356 retn.retn_billing_cycle_id billing_cycle_id,
1357 retn.completed_percentage completed_percentage,
1358 retn.client_extension_flag client_extn_flag,
1359 'NULL' billing_cycle_name,
1360 lk.meaning billingMethodDesc,
1361 retn.task_id task_id
1362 FROM pa_proj_retn_bill_rules retn,
1363 pa_lookups lk
1364 WHERE retn.project_id = TmpProjectId
1365 AND retn.customer_id= TmpCustomerId
1366 AND lk.lookup_code = retn.billing_method_code
1367 AND lk.lookup_type ='RETN_BILLING_METHOD'
1368 AND retn.billing_method_code = 'PERCENT_COMPLETE'
1369 AND
1370 retn.completed_percentage <=
1371 pa_bill_pct.GetPercentComplete(
1372 retn.project_id, retn.task_id,TmpBillThruDate) /*Bug 3258465 Modified the call by passing TmpBillThruDate instead of TmpInvoiceDate*/
1373 ORDER BY completed_percentage DESC;
1374
1375
1376 RetnRuleRec cur_retn_billing_rules%ROWTYPE;
1377
1378 OldTask NUMBER;
1379 NewTask Number;
1380 PC_done boolean := FALSE;
1381 TmpCompPercent NUMBER;
1382 TmpBillCycleId NUMBER;
1383 TmpTotRetenion NUMBER;
1384 TmpClientExt VARCHAR2(1);
1385
1386 CURSOR cur_agr IS SELECT
1387 spr.agreement_id,
1388 spr.project_id,
1389 spr.task_id,
1390 spr.invproc_currency_code,
1391 spr.total_retained,
1392 spr.total_billed,
1393 spr.projfunc_currency_code,
1394 spr.projfunc_total_retained,
1395 spr.projfunc_total_billed,
1396 spr.project_currency_code,
1397 spr.project_total_retained,
1398 spr.project_total_billed,
1399 spr.funding_currency_code,
1400 spr.funding_total_retained,
1401 (NVL(spr.total_retained,0)- NVL(spr.total_billed,0)) Remain_Retained_Amt,
1402 pr.inv_by_bill_trans_curr_flag inv_by_bill_trans_curr_flag
1403 FROM pa_summary_project_retn spr,
1404 pa_agreements_all agr,
1405 pa_projects_all pr
1406 WHERE agr.agreement_id = spr.agreement_id
1407 AND agr.customer_id = TmpCustomerID
1408 AND spr.project_id = TmpProjectId
1409 AND spr.project_id = pr.project_id
1410 AND NVL(spr.task_id,-99) = NVL(NewTask,-99)
1411 AND NVL(spr.total_retained,0) <> 0 /*For Bug 7612216*/
1412 ORDER BY DECODE(agr.invoice_limit_flag,'Y',1,2), agr.expiration_date;
1413
1414 SPRRec cur_agr%ROWTYPE;
1415
1416 TmpInvoiceNum NUMBER;
1417 TmpPFCBillAmount NUMBER;
1418 TmpPCBillAmount NUMBER;
1419 TmpFCBillAmount NUMBER;
1420 TmpMethodValue VARCHAR2(80);
1421
1422 --Tmp_Output_vat_tax_id NUMBER;
1423 Tmp_output_tax_code VARCHAR2(30);
1424 Tmp_Output_tax_exempt_flag VARCHAR2(2);
1425 --Tmp_Output_tax_exempt_number VARCHAR2(30); --Modified for Bug3128094
1426 Tmp_Output_tax_exempt_number VARCHAR2(80);
1427 Tmp_Output_exempt_reason_code VARCHAR2(30);
1428
1429 TmpTaskId NUMBER:=0;
1430
1431 TmpBillPercentage Number:=0;
1432 TmpBillingAmount NUMBER:=0;
1433 TmpBillFlag VARCHAR2(2);
1434 TmpReturnStatus VARCHAR2(2);
1435
1436 TmpIPC VARCHAR2(15);
1437 TmpPFC VARCHAR2(15);
1438 TmpInvByBTC VARCHAR2(1);
1439 TmpRetnBillInvFmt Number:=0;
1440
1441 BEGIN
1442 IF g1_debug_mode = 'Y' THEN
1443 pa_retention_util.write_log('Entering pa_retn_billing_pkg.Retention_Billing_Processing');
1444 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'From Project Number ' || p_start_proj_number );
1445 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'End Project Number ' || p_end_proj_number);
1446 END IF;
1447
1448
1449
1450 -- List the Project and Customers
1451
1452 OPEN cur_proj_cust_retn;
1453
1454 LOOP -- Project Customer Loop Starts
1455
1456 FETCH cur_proj_cust_retn INTO ProjCustRec;
1457
1458 EXIT WHEN cur_proj_cust_retn%NOTFOUND;
1459
1460 TmpCustomerId := ProjCustRec.Customer_id;
1461 TmpProjectId := ProjCustRec.project_id;
1462 TmpRetnBillInvFmt := ProjCustRec.RETN_BILLING_INV_FORMAT_ID;
1463 IF g1_debug_mode = 'Y' THEN
1464 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpCustomerId : ' || TmpCustomerId);
1465 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpProjectId : ' || TmpProjectId);
1466 END IF;
1467 TmpTaskId := 0;
1468 OldTask := 0;
1469 NewTask := 0;
1470 PC_done := FALSE;
1471 CurRetained :=0;
1472 CurBillAmount :=0;
1473 TmpBillAmount :=0;
1474
1475 -- Project Retention Billing
1476
1477 BEGIN
1478 OPEN cur_retn_billing_rules;
1479
1480 LOOP
1481 FETCH cur_retn_billing_rules INTO RetnRuleRec;
1482
1483 EXIT WHEN cur_retn_billing_rules%NOTFOUND;
1484 CurRetained:= 0;
1485 TmpTaskId := RetnRuleRec.Task_id;
1486 NewTask := TmpTaskId;
1487 CurRetained :=0;
1488 CurBillAmount :=0;
1489 TmpBillAmount :=0;
1490 TmpCompPercent :=RetnRuleRec.completed_percentage;
1491 TmpBillCycleId :=RetnRuleRec.billing_cycle_id;
1492 TmpTotRetenion :=RetnRuleRec.total_retention_amount;
1493 TmpClientExt :=RetnRuleRec.client_extn_flag;
1494
1495 IF g1_debug_mode = 'Y' THEN
1496 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'retention_level : ' || ProjCustRec.retention_level);
1497 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpProjectId : ' || TmpProjectId);
1498 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'NewTask : ' || NewTask);
1499 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpCompPercent : ' || TmpCompPercent);
1500 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpBillCycleId : ' || TmpBillCycleId);
1501 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpTotRetenion : ' || TmpTotRetenion);
1502 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpClientExt : ' || TmpClientExt);
1503 END IF;
1504
1505 -- to get current bill amount that is total retained amount - total billed
1506 -- For Project Level
1507
1508 IF ProjCustRec.retention_level = 'PROJECT' THEN
1509
1510 SELECT SUM(NVL(spr.total_retained,0)) -
1511 SUM(NVL(spr.total_billed,0))
1512 INTO CurRetained
1513 FROM pa_summary_project_retn spr
1514 WHERE spr.project_id= TmpProjectId
1515 AND spr.customer_id= TmpCustomerId; /*Added for bug 3234999*/
1516
1517 IF g1_debug_mode = 'Y' THEN
1518 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Sum of Retention Level : ' || ProjCustRec.retention_level);
1519 END IF;
1520
1521 ELSE
1522
1523 SELECT SUM(NVL(spr.total_retained,0)) -
1524 SUM(NVL(spr.total_billed,0))
1525 INTO CurRetained
1526 FROM pa_summary_project_retn spr
1527 WHERE spr.project_id= TmpProjectId
1528 AND spr.task_id = NewTask
1529 AND spr.customer_id= TmpCustomerId; /*Added for bug 3234999*/
1530
1531 IF g1_debug_mode = 'Y' THEN
1532 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Sum of Retention Level : ' || ProjCustRec.retention_level
1533 ||' : ' || NewTask);
1534 END IF;
1535 -- For Percent Complet Case
1536 -- Process should execute only once
1537 IF OldTask <> NewTask THEN
1538
1539 PC_Done := FALSE;
1540 OldTask := NewTask;
1541
1542 END IF;
1543
1544 END IF;
1545 IF g1_debug_mode = 'Y' THEN
1546 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CurRetained : ' || CurRetained);
1547 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Billing Method : ' || RetnRuleRec.Billing_Method);
1548 END IF;
1549
1550 IF NVL(CurRetained,0) <> 0 THEN
1551
1552 IF rtrim(RetnRuleRec.Billing_Method)='TOTAL_RETENTION_AMOUNT' THEN
1553 IF g1_debug_mode = 'Y' THEN
1554 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Processing Method : ' || RetnRuleRec.Billing_Method);
1555 END IF;
1556
1557 IF g1_debug_mode = 'Y' THEN
1558 pa_retention_util.write_log('Total Retn Amt : ' ||
1559 NVL(RetnRuleRec.total_retention_amount,0));
1560 END IF;
1561 TmpMethodValue := TO_CHAR(
1562 NVL(RetnRuleRec.total_retention_amount,
1563 0));
1564
1565 IF NVL(CurRetained,0) >= NVL(RetnRuleRec.total_retention_amount,0) THEN
1566
1567 RetnRuleRec.bill_percentage :=
1568 NVL(RetnRuleRec.bill_percentage,0);
1569
1570 RetnRuleRec.bill_amount :=
1571 NVL(RetnRuleRec.bill_amount,0);
1572 ELSE
1573
1574 RetnRuleRec.bill_percentage :=0;
1575 RetnRuleRec.bill_amount := 0;
1576
1577 END IF;
1578 IF g1_debug_mode = 'Y' THEN
1579 pa_retention_util.write_log('bill_percentage : ' ||
1580 NVL(RetnRuleRec.bill_percentage,0));
1581 pa_retention_util.write_log('bill_amount : ' ||
1582 NVL(RetnRuleRec.bill_amount,0));
1583 END IF;
1584
1585 ElsIF rtrim(RetnRuleRec.Billing_Method)='CLIENT_EXTENSION' THEN
1586 IF g1_debug_mode = 'Y' THEN
1587 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Processing Method : ' || RetnRuleRec.Billing_Method);
1588 END IF;
1589
1590 -- Call Client Extension to get the percent or amount
1591 IF g1_debug_mode = 'Y' THEN
1592 pa_retention_util.write_log('Total Retn Amt : ' ||
1593 NVL(RetnRuleRec.total_retention_amount,0));
1594 END IF;
1595
1596 TmpBillPercentage :=0;
1597 TmpBillingAmount :=0;
1598 TmpBillFlag :='N';
1599 TmpReturnStatus :='S';
1600
1601 pa_client_extn_retention.BILL_RETENTION(
1602 P_CUSTOMER_ID=>TmpCustomerId,
1603 P_PROJECT_ID =>TmpProjectId,
1604 P_TOP_TASK_ID=>TmpTaskId,
1605 X_BILL_RETENTION_FLAG=>TmpBillFlag,
1606 X_BILL_PERCENTAGE=>TmpBillPercentage,
1607 X_BILL_AMOUNT=>TmpBillingAmount,
1608 X_STATUS=>TmpReturnStatus);
1609
1610 IF TmpBillFlag = 'Y' THEN
1611
1612 IF TmpReturnStatus <> 'E' THEN
1613
1614 RetnRuleRec.bill_percentage :=
1615 NVL(TmpBillPercentage,0);
1616
1617 RetnRuleRec.bill_amount :=
1618 NVL(TmpBillingAmount,0);
1619
1620 ELSIF TmpReturnStatus = 'E' THEN
1621
1622 RetnRuleRec.bill_percentage :=0;
1623
1624 RetnRuleRec.bill_amount := 0;
1625
1626
1627 END IF;
1628
1629 ELSIF TmpBillFlag = 'N' THEN
1630
1631 RetnRuleRec.bill_percentage :=0;
1632
1633 RetnRuleRec.bill_amount := 0;
1634
1635 END IF;
1636 IF g1_debug_mode = 'Y' THEN
1637 pa_retention_util.write_log('bill_percentage : ' ||
1638 NVL(RetnRuleRec.bill_percentage,0));
1639 pa_retention_util.write_log('bill_amount : ' ||
1640 NVL(RetnRuleRec.bill_amount,0));
1641 END IF;
1642
1643 ElsIF rtrim(RetnRuleRec.Billing_Method) ='PERCENT_COMPLETE' THEN
1644
1645 IF g1_debug_mode = 'Y' THEN
1646 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Processing Method : ' || RetnRuleRec.Billing_Method);
1647 pa_retention_util.write_log('Total Retn Amt : ' ||
1648 NVL(RetnRuleRec.total_retention_amount,0));
1649 END IF;
1650
1651
1652 IF NOT PC_DONE THEN
1653
1654 TmpMethodValue := TO_CHAR(
1655 RetnRuleRec.completed_percentage);
1656 RetnRuleRec.bill_percentage :=
1657 NVL(RetnRuleRec.bill_percentage,0);
1658
1659 RetnRuleRec.bill_amount :=
1660 NVL(RetnRuleRec.bill_amount,0);
1661 pc_done := true;
1662 ELSE
1663 RetnRuleRec.bill_percentage :=0;
1664 RetnRuleRec.bill_amount := 0;
1665 END IF;
1666
1667 IF g1_debug_mode = 'Y' THEN
1668 pa_retention_util.write_log('bill_percentage : ' ||
1669 NVL(RetnRuleRec.bill_percentage,0));
1670 pa_retention_util.write_log('bill_amount : ' ||
1671 NVL(RetnRuleRec.bill_amount,0));
1672 END IF;
1673
1674 ElsIF RetnRuleRec.Billing_Method='RETENTION_BILLING_CYCLE' THEN
1675 TmpMethodValue :=
1676 RetnRuleRec.Billing_Cycle_Name;
1677
1678
1679 IF pa_retention_util.IsBillingCycleQualified(
1680 p_project_id => TmpProjectId,
1681 p_task_id => TmpTaskId,
1682 P_bill_thru_date =>
1683 TO_DATE(pa_billing.globvars.billthrudate,'YYYY/MM/DD'),
1684 p_billing_cycle_id =>
1685 RetnRuleRec.billing_cycle_id) ='Y'
1686 THEN
1687
1688 RetnRuleRec.bill_percentage :=
1689 NVL(RetnRuleRec.bill_percentage,0);
1690
1691 RetnRuleRec.bill_amount :=
1692 NVL(RetnRuleRec.bill_amount,0);
1693 ELSE
1694
1695 RetnRuleRec.bill_percentage :=0;
1696
1697 RetnRuleRec.bill_amount :=0;
1698
1699 END IF;
1700
1701 END IF;
1702
1703 IF g1_debug_mode = 'Y' THEN
1704 pa_retention_util.write_log('Billing Percentage : ' ||
1705 NVL(RetnRuleRec.bill_percentage,0));
1706
1707 pa_retention_util.write_log('Billing Amount : ' ||
1708 NVL(RetnRuleRec.bill_amount,0));
1709 END IF;
1710
1711 If NVL(RetnRuleRec.bill_percentage,0) <> 0 THEN
1712
1713 CurBillAmount := NVL(CurRetained,0) *
1714 (NVL(RetnRuleRec.bill_percentage,0)/100);
1715
1716 ElsIf NVL(RetnRuleRec.bill_amount,0) <> 0 THEN
1717
1718 CurBillAmount := NVL(RetnRuleRec.bill_amount,0);
1719
1720 END IF;
1721
1722 IF g1_debug_mode = 'Y' THEN
1723 pa_retention_util.write_log('CurrBillAmount : ' ||
1724 NVL(CurBillAmount,0));
1725 END IF;
1726
1727 IF NVL(CurBillAmount,0) <> 0 THEN
1728
1729 IF g1_debug_mode = 'Y' THEN
1730 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Open Cursor CurAgr ');
1731 END IF;
1732
1733 OPEN Cur_Agr;
1734
1735 LOOP
1736
1737 FETCH cur_agr INTO SPRRec;
1738
1739 EXIT WHEN cur_agr%NOTFOUND OR NVL(CurBillAmount,0)<=0;
1740
1741 TmpBillAmount :=0;
1742 TmpPFCBillAmount :=0;
1743 TmpPCBillAmount :=0;
1744 TmpFCBillAmount :=0;
1745 TmpIPC := SprRec.invproc_currency_code;
1746 TmpPFC := SprRec.projfunc_currency_code;
1747 TmpInvByBtc := SprRec.inv_by_bill_trans_curr_flag;
1748
1749 IF NVL(SprRec.Remain_Retained_Amt,0) >=
1750 NVL(CurBillAmount,0) THEN
1751
1752 TmpBillAmount := NVL(CurBillAmount,0);
1753
1754 /* Bug 2502373 : if the current bill amount is less
1755 than the to be billed amount, assign the full
1756 bill amount for billing and set the current
1757 bill amount is zero */
1758
1759 CurBillAmount := 0;
1760
1761 ELSIF NVL(SprRec.Remain_Retained_Amt,0) <
1762 NVL(CurBillAmount,0) THEN
1763
1764 TmpBillAmount :=
1765 NVL(SprRec.Remain_Retained_Amt,0);
1766
1767 CurBillAmount := NVL(CurBillAmount,0) -
1768 NVL(SprRec.Remain_Retained_Amt,0);
1769
1770 END IF;
1771
1772 IF g1_debug_mode = 'Y' THEN
1773 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpBillAmount : '
1774 || TmpBillAmount);
1775 END IF;
1776
1777 TmpPFCBillAmount :=
1778 (NVL(SprRec.projfunc_total_retained,0)/
1779 NVL(SprRec.total_retained,0))
1780 * NVL(TmpBillAmount,0);
1781
1782 TmpPCBillAmount :=
1783 (NVL(SprRec.project_total_retained,0)/
1784 NVL(SprRec.total_retained,0))
1785 * NVL(TmpBillAmount,0);
1786
1787 TmpFCBillAmount :=
1788 (NVL(SprRec.funding_total_retained,0)/
1789 NVL(SprRec.total_retained,0))
1790 * NVL(TmpBillAmount,0);
1791
1792 IF g1_debug_mode = 'Y' THEN
1793 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpPFCBillAmount : '
1794 || TmpPFCBillAmount);
1795 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpPCBillAmount : '
1796 || TmpPCBillAmount);
1797 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'TmpFCBillAmount : '
1798 || TmpFCBillAmount);
1799 END IF;
1800
1801 IF (( ProjCustRec.retention_level = 'TOP_TASK'
1802 AND CheckInvoiceExists(p_project_id=>TmpProjectId,
1803 p_agreement_id=>SprRec.Agreement_id,
1804 p_request_id=>p_request_id)='N' ) OR
1805 (ProjCustRec.retention_level = 'PROJECT')) THEN
1806
1807 IF g1_debug_mode = 'Y' THEN
1808 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Agreement Id : ' || SprRec.Agreement_id);
1809 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Customer Id : ' || TmpCustomerId);
1810 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Project Id : ' || TmpProjectId);
1811 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CAll Build_Retn_Invoice_Header');
1812 /*The exception block has been added for customer account relation change,bug no 2760630 */ END IF;
1813 BEGIN
1814 Tmprelactive :=0;
1815 IF NVL(TmpBillAmount,0) <> 0 THEN
1816
1817 Build_Retn_Invoice_Header(
1818 p_project_id=>TmpProjectId,
1819 p_agreement_id =>SprRec.agreement_id,
1820 p_customer_id =>TmpCustomerId,
1821 p_request_id =>p_request_id,
1822 x_draft_invoice_num=>TmpInvoiceNum,
1823 X_output_tax_code => Tmp_output_tax_code,
1824 X_Output_tax_exempt_flag=>Tmp_Output_tax_exempt_flag ,
1825 X_Output_tax_exempt_number =>Tmp_Output_tax_exempt_number,
1826 X_Output_exempt_reason_code =>Tmp_Output_exempt_reason_code);
1827 END IF;
1828 EXCEPTION WHEN NO_DATA_FOUND THEN
1829 Tmprelactive :=1;
1830 END;
1831
1832 END IF;
1833
1834 -- If the retention billing invoice format is defined
1835
1836 IF (TmpRetnBillInvFmt <> 0 AND Tmprelactive=0) THEN
1837
1838 IF NVL(TmpBillAmounT,0) <> 0 THEN
1839
1840 IF g1_debug_mode = 'Y' THEN
1841 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CAll Create_Retn_Invoice_Lines');
1842 END IF;
1843
1844 Create_Retn_Invoice_Lines(
1845 p_project_id=>TmpProjectId,
1846 p_customer_id=>TmpCustomerId,
1847 p_task_id=>NewTask,
1848 p_agreement_id=>SprRec.agreement_id,
1849 p_draft_invoice_num=>TmpInvoiceNum,
1850 p_request_id =>p_request_id,
1851 p_invproc_currency=>SprRec.invproc_currency_code,
1852 p_invproc_amount=>TmpBillAmount,
1853 p_projfunc_currency=> SprRec.projfunc_currency_code,
1854 p_project_currency=> SprRec.project_currency_code,
1855 p_funding_currency=>SprRec.funding_currency_code,
1856 p_projfunc_amount =>TmpPFCBillAmount,
1857 p_project_amount =>TmpPCBillAmount,
1858 p_funding_amount=>TmpFCBillAmount,
1859 p_billing_method=>RetnRuleRec.BillingMethodDesc,
1860 p_billing_method_code=>RetnRuleRec.billing_method,
1861 p_method_value=>TmpMethodValue,
1862 p_billing_percentage=>RetnRuleRec.bill_percentage,
1863 p_billing_amount=>RetnRuleRec.bill_amount,
1864 p_total_retained=>CurRetained,
1865 p_output_tax_code => Tmp_output_tax_code,
1866 p_Output_tax_exempt_flag=>Tmp_Output_tax_exempt_flag ,
1867 p_Output_tax_exempt_number =>Tmp_Output_tax_exempt_number,
1868 p_Output_exempt_reason_code =>Tmp_Output_exempt_reason_code,
1869 p_comp_percent=>TmpCompPercent,
1870 p_bill_cycle_id=>TmpBillCycleId,
1871 p_TotRetenion=>TmpTotRetenion,
1872 p_client_extn_flag=>TmpClientExt);
1873
1874 -- Call the MRC hook
1875
1876 /* MRC elimination bug 4941054
1877 IF g1_debug_mode = 'Y' THEN
1878 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CAll MRC Hook');
1879 END IF;
1880
1881 PA_MC_RETN_INV_DETAIL_PKG.Process_RetnInvDetails(
1882 p_project_id=>TmpProjectId,
1883 p_draft_invoice_num=>TmpInvoiceNum,
1884 p_action=>'INSERT',
1885 p_request_id=>p_request_id);
1886 */
1887
1888 -- Update only for invoice by bill transaction invoice
1889
1890 IF (SprRec.invproc_currency_code <> SprRec.projfunc_currency_code)
1891 AND (SprRec.inv_by_bill_trans_curr_flag ='Y') THEN
1892
1893 IF g1_debug_mode = 'Y' THEN
1894 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CAll Update_ProjFunc_Attributes');
1895 END IF;
1896
1897 Update_ProjFunc_Attributes(p_project_id=>TmpProjectId,
1898 p_draft_invoice_num=>TmpInvoiceNum);
1899
1900 END IF;
1901
1902 END IF;
1903
1904 END IF;
1905
1906
1907 END LOOP;
1908
1909 CLOSE cur_agr;
1910
1911 END IF;
1912
1913 END IF; -- no retained amount to bill
1914
1915 END LOOP; -- Retention Rule Ends
1916
1917 CLOSE cur_retn_billing_rules;
1918
1919 END; -- End of Retention Billing
1920
1921 END LOOP; -- Project Customer Loop Ends
1922
1923 CLOSE cur_proj_cust_retn;
1924
1925 -- Update the invoice currency attributes for retention invoices
1926
1927 IF g1_debug_mode = 'Y' THEN
1928 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CAll Update_Inv_Trans_Attributes');
1929 END IF;
1930
1931 Update_Inv_Trans_Attributes(p_request_id=>p_request_id);
1932
1933 -- create distribution warning if the project is not generated any retention invoices
1934
1935 IF g1_debug_mode = 'Y' THEN
1936 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Call Invoice_Generation_Exceptions');
1937 END IF;
1938
1939 Invoice_Generation_Exceptions (p_request_id =>p_request_id,
1940 p_start_proj_number=>p_start_proj_number ,
1941 p_end_proj_number=>p_end_proj_number) ;
1942
1943 IF g1_debug_mode = 'Y' THEN
1944 pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Update MRC for Retention Invoices');
1945 END IF;
1946
1947 UPDATE PA_DRAFT_INVOICES
1948 SET CREATION_DATE = sysdate
1949 WHERE REQUEST_ID = p_request_id
1950 AND NVL(retention_invoice_flag,'N') = 'Y';
1951
1952
1953 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1954 pa_retn_billing_pkg.G_ERROR_CODE :='S';
1955
1956 EXCEPTION
1957 WHEN OTHERS THEN
1958 x_return_status :='E';
1959 pa_retn_billing_pkg.G_ERROR_CODE :='E';
1960 IF g1_debug_mode = 'Y' THEN
1961 pa_retention_util.write_log('Retention_Billing_Processing: Oracle Error : ' || sqlerrm);
1962 END IF;
1963 END Retention_Billing_Processing;
1964
1965 PROCEDURE Update_Inv_Trans_Attributes (p_request_id IN NUMBER) IS
1966 CURSOR Retn_Inv_Project IS
1967 SELECT project_id
1968 FROM pa_draft_invoices
1969 WHERE request_id = p_request_id
1970 AND NVL(GENERATION_ERROR_FLAG,'N') ='N'
1971 AND NVL(retention_invoice_flag,'N') = 'Y'
1972 GROUP BY project_id;
1973
1974 TmpUserId NUMBER:= fnd_global.user_id;
1975
1976 BEGIN
1977 IF g1_debug_mode = 'Y' THEN
1978 pa_retention_util.write_log('Entering Update_Inv_Trans_Attributes');
1979 END IF;
1980
1981 FOR ProjectRec IN Retn_Inv_Project LOOP
1982 IF g1_debug_mode = 'Y' THEN
1983 pa_retention_util.write_log('Update_Inv_Trans_Attributes: ' || 'Calling PA_INVOICE_CURRENCY.Recalculate_Driver for project id : '
1984 || ProjectRec.project_id);
1985 END IF;
1986 PA_INVOICE_CURRENCY.Recalculate_Driver
1987 ( P_Request_ID =>p_request_id,
1988 P_User_ID =>TmpUserId,
1989 P_Project_ID =>ProjectRec.project_id,
1990 p_calling_process=>'RETENTION_INVOICES');
1991 END LOOP;
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 pa_retn_billing_pkg.G_ERROR_CODE :='E';
1995 END Update_Inv_Trans_Attributes;
1996
1997 -- Procedure Invoice_Generation_Exceptions
1998 -- p_request_id : Request Id
1999 -- p_start_proj_number : Start Project Number
2000 -- p_end_proj_number : End Project Number
2001 -- Purpose :
2002 -- This procedure will insert the distribution warning
2003 -- for the project which is not created invoices
2004 PROCEDURE Invoice_Generation_Exceptions (p_request_id IN NUMBER,
2005 p_start_proj_number IN VARCHAR2,
2006 p_end_proj_number IN VARCHAR2) IS
2007 CURSOR cur_select_projects IS
2008 SELECT pr.project_id project_id
2009 FROM pa_projects pr, pa_project_types t
2010 WHERE NOT EXISTS (SELECT null
2011 FROM pa_draft_invoices_all di
2012 WHERE di.request_id = p_request_id
2013 AND di.project_id = pr.project_id
2014 AND NVL(di.retention_invoice_flag,'N') = 'Y')
2015 AND EXISTS( SELECT NULL
2016 FROM pa_proj_retn_rules rt
2017 WHERE rt.project_id = pr.project_id)
2018 AND PA_Project_Utils.Check_prj_stus_action_allowed(pr.Project_Status_Code, 'GENERATE_INV') = 'Y'
2019 AND pr.project_type = t.project_type
2020 AND t.Project_type_class_code = 'CONTRACT'
2021 AND pr.segment1 between p_start_proj_number and p_end_proj_number
2022 ORDER BY pr.segment1;
2023
2024 RecSelectProjects cur_select_projects%ROWTYPE;
2025
2026 TmpProgId NUMBER:= fnd_global.conc_program_id;
2027 TmpProgApplId NUMBER:= fnd_global.prog_appl_id;
2028 l_program_update_date DATE := sysdate;
2029 l_last_update_date DATE := sysdate;
2030 l_last_updated_by NUMBER:= fnd_global.user_id;
2031 l_last_update_login NUMBER:= fnd_global.login_id;
2032 TmpUserId NUMBER:= fnd_global.user_id;
2033 TmpInvoiceNum NUMBER:=NULL;
2034 TmpWarningMsg VARCHAR2(80);
2035 TmpWarningCode VARCHAR2(30):= 'NO_RETN_BILLING_INVOICES';
2036
2037 BEGIN
2038 IF g1_debug_mode = 'Y' THEN
2039 pa_retention_util.write_log('Invoice_Generation_Exceptions: ' || 'Warning : ' || TmpWarningCode);
2040 END IF;
2041
2042 SELECT lk.meaning
2043 INTO TmpWarningMsg
2044 FROM pa_lookups lk
2045 WHERE lk.lookup_code = TmpWarningCode
2046 AND lk.lookup_type = 'INVOICE DISTRIBUTION WARNING';
2047
2048 IF g1_debug_mode = 'Y' THEN
2049 pa_retention_util.write_log('Invoice_Generation_Exceptions: ' || 'Warning Code : ' || TmpWarningCode);
2050 pa_retention_util.write_log('Invoice_Generation_Exceptions: ' || 'Warning Mesg : ' || TmpWarningMsg);
2051 END IF;
2052
2053
2054 OPEN cur_select_projects;
2055 LOOP
2056 FETCH cur_select_projects INTO RecSelectProjects;
2057 EXIT WHEN cur_select_projects%NOTFOUND;
2058
2059 IF g1_debug_mode = 'Y' THEN
2060 pa_retention_util.write_log('Invoice_Generation_Exceptions: ' || 'Insert Warning ');
2061 END IF;
2062
2063
2064 INSERT INTO PA_DISTRIBUTION_WARNINGS (
2065 DRAFT_INVOICE_NUM, PROJECT_ID,
2066 LAST_UPDATE_DATE, LAST_UPDATED_BY,
2067 CREATION_DATE, CREATED_BY,
2068 REQUEST_ID, PROGRAM_APPLICATION_ID,
2069 PROGRAM_ID, PROGRAM_UPDATE_DATE,
2070 WARNING_MESSAGE, WARNING_MESSAGE_CODE)
2071 VALUES
2072 ( TmpInvoiceNum, RecSelectProjects.project_id,
2073 SYSDATE, TmpUserId, SYSDATE,
2074 TmpUserId, p_request_id, TmpProgApplId,
2075 TmpProgId, SYSDATE,
2076 TmpWarningMsg, TmpWarningCode);
2077
2078
2079 END LOOP;
2080 CLOSE cur_select_projects;
2081 EXCEPTION
2082 WHEN OTHERS THEN
2083 RAISE;
2084 END Invoice_Generation_Exceptions;
2085
2086 END pa_retn_billing_pkg;