[Home] [Help]
PACKAGE BODY: APPS.PA_RETENTION_PKG
Source
1 PACKAGE BODY pa_retention_pkg AS
2 /* $Header: PAXIRTNB.pls 120.6.12000000.2 2007/07/24 11:36:25 jjgeorge ship $ */
3
4
5 -- Function Get_Invoice_Max_Line
6 -- Purpose Get the Maximum Invoice line Number for a given project, invoice number
7
8 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
9
10 FUNCTION Get_Invoice_Max_Line(p_project_id IN NUMBER,
11 p_draft_invoice_num IN NUMBER) RETURN NUMBER IS
12 last_line_num NUMBER;
13 BEGIN
14
15 IF g1_debug_mode = 'Y' THEN
16 pa_retention_util.write_log('Entering pa_retention_pkg.Get_Invoice_Max_Line');
17 END IF;
18
19 SELECT NVL(MAX(line_num),0) +1
20 INTO last_line_num
21 FROM pa_draft_invoice_items
22 WHERE project_id = p_project_id
23 AND draft_invoice_num = p_draft_invoice_num;
24
25 IF g1_debug_mode = 'Y' THEN
26 pa_retention_util.write_log('Get_Invoice_Max_Line : ' || last_line_num);
27 pa_retention_util.write_log('Leaving pa_retention_pkg.Get_Invoice_Max_Line');
28 END IF;
29 RETURN (last_line_num);
30
31 END Get_Invoice_Max_Line;
32
33 /* This function is added as netzero lines are coming up before retention lines in project invoices
34 net zero line num is cached. It is updated to its negative value */
35 FUNCTION Get_NetZero_Line(p_project_id IN NUMBER,
36 p_draft_invoice_num IN NUMBER) RETURN NUMBER IS
37 NetZero_line_num NUMBER;
38 BEGIN
39
40 IF g1_debug_mode = 'Y' THEN
41 pa_retention_util.write_log('Entering pa_retention_pkg.Get_NetZero_Line');
42 END IF;
43
44 SELECT line_num
45 INTO NetZero_line_num
46 FROM pa_draft_invoice_items
47 WHERE project_id = p_project_id
48 AND draft_invoice_num = p_draft_invoice_num
49 AND invoice_line_type = 'NET ZERO ADJUSTMENT';
50
51 IF g1_debug_mode = 'Y' THEN
52 pa_retention_util.write_log('Get_NetZero_Line : ' || NetZero_line_num);
53 END IF;
54
55 Update pa_draft_invoice_items
56 set line_num = NetZero_line_num * (-1)
57 WHERE project_id = p_project_id
58 AND draft_invoice_num = p_draft_invoice_num
59 AND invoice_line_type = 'NET ZERO ADJUSTMENT';
60
61
62 RETURN (NetZero_line_num);
63
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 RETURN (0);
67
68 END Get_NetZero_Line;
69
70 -- Function Get_Proj_Inv_Retn_Format
71 -- Purpose Function to return the project invoice retention line format
72
73 Function Get_Proj_Inv_Retn_Format(p_project_id NUMBER) RETURN pa_retention_pkg.TabInvRetnLineFormat IS
74
75 CURSOR cur_inv_group_columns IS SELECT grp.column_code column_code,
76 fmtdet.text text,
77 fmtdet.start_position start_position,
78 fmtdet.end_position end_position,
79 NVL(fmtdet.right_justify_flag,'N') right_justify_flag
80 FROM pa_invoice_group_columns grp,
81 pa_invoice_formats fmt,
82 pa_invoice_format_details fmtdet,
83 pa_projects_all pr
84 WHERE pr.retention_invoice_format_id = fmt.invoice_format_id
85 AND fmt.invoice_format_id = fmtdet.invoice_format_id
86 AND grp.invoice_group_column_id = fmtdet.invoice_group_column_id
87 and pr.project_id =p_project_id
88 ORDER BY fmtdet.start_position;
89
90 Cnt NUMBER :=0;
91
92 InvGroupColumnsRec cur_Inv_Group_columns%ROWTYPE;
93
94 TmpRetnLineFmt pa_retention_pkg.TabInvRetnLineFormat;
95
96 BEGIN
97 IF g1_debug_mode = 'Y' THEN
98 pa_retention_util.write_log('Entering pa_retention_pkg.Get_Proj_Inv_Retn_Format');
99 END IF;
100
101 OPEN cur_inv_group_columns;
102 LOOP
103 FETCH cur_inv_group_columns INTO InvGroupColumnsRec;
104 EXIT WHEN cur_inv_group_columns%NOTFOUND;
105 cnt := cnt +1;
106 TmpRetnLineFmt(Cnt).column_code :=InvGroupColumnsRec.column_code;
107 TmpRetnLineFmt(Cnt).usertext := InvGroupColumnsRec.text;
108 TmpRetnLineFmt(Cnt).start_position := InvGroupColumnsRec.start_position;
109 TmpRetnLineFmt(Cnt).end_position := InvGroupColumnsRec.end_position;
110 TmpRetnLineFmt(Cnt).right_justify_flag := InvGroupColumnsRec.right_justify_flag;
111
112 IF g1_debug_mode = 'Y' THEN
113 pa_retention_util.write_log('Get_Proj_Inv_Retn_Format: ' || 'Format Column : ' || InvGroupColumnsRec.column_code);
114 pa_retention_util.write_log('Get_Proj_Inv_Retn_Format: ' || 'User TExt : ' || InvGroupColumnsRec.text);
115 END IF;
116
117 END LOOP;
118 CLOSE cur_inv_group_columns;
119
120 IF g1_debug_mode = 'Y' THEN
121 pa_retention_util.write_log('Leaving pa_retention_pkg.Get_Proj_Inv_Retn_Format');
122 END IF;
123
124 RETURN TmpRetnLineFmt;
125
126 END Get_Proj_Inv_Retn_Format;
127
128 -- Procedure to Update the retention balances
129 -- Update the pa_project_retentions, pa_proj_retn_rules, pa_summary_project_retn
130
131 /*PROCEDURE Update_Retention_Balances( p_retention_rule_id IN NUMBER DEFAULT NULL, bug 2681003,
132 removed the default values to ensure GSCC complaince */
133 PROCEDURE Update_Retention_Balances( p_retention_rule_id IN NUMBER ,
134 p_project_id IN NUMBER ,
135 /*p_task_id IN NUMBER DEFAULT NULL,
136 removed the default values to ensure GSCC complaince */
137 p_task_id IN NUMBER ,
138 p_agreement_id IN NUMBER,
139 p_customer_id IN NUMBER,
140 p_amount IN NUMBER,
141 p_change_type IN VARCHAR2,
142 p_request_id IN NUMBER ,
143 p_invproc_currency IN VARCHAR2,
144 p_project_currency IN VARCHAR2,
145 p_project_amount IN NUMBER,
146 p_projfunc_currency IN VARCHAR2,
147 p_projfunc_amount IN NUMBER,
148 p_funding_currency IN VARCHAR2,
149 p_funding_amount IN NUMBER) IS
150
151 TmpFlag VARCHAR2(1):='N';
152 l_program_id NUMBER:= fnd_global.conc_program_id;
153 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
154 l_program_update_date DATE := sysdate;
155 l_last_update_date DATE := sysdate;
156 l_last_updated_by NUMBER:= fnd_global.user_id;
157 l_last_update_login NUMBER:= fnd_global.login_id;
158 l_project_retn_id NUMBER;
159
160 ok_found EXCEPTION;
161 spr_found EXCEPTION;
162
163 BEGIN
164 IF g1_debug_mode = 'Y' THEN
165 pa_retention_util.write_log('Entering pa_retention_pkg.Update_Retention_Balances');
166 END IF;
167
168 IF p_change_type = 'RETAINED' THEN
169
170 IF g1_debug_mode = 'Y' THEN
171 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Retained Amount');
172 END IF;
173
174 -- For Old Invoice Credit memo retention rule id will be null, so no action is needed
175
176 IF p_retention_rule_id IS NOT NULL THEN
177
178 IF g1_debug_mode = 'Y' THEN
179 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Retained Amount');
180 END IF;
181
182 -- Before update the retained amount, make sure there is a record for this agreement
183 -- , rule, project id.
184
185 BEGIN
186 SELECT 'Y' INTO TmpFlag
187 FROM pa_project_retentions
188 WHERE project_id = p_project_id
189 AND retention_rule_id = p_retention_rule_id
190 AND agreement_id = p_agreement_id
191 AND NVL(task_id,-99) = NVL(p_task_id,-99);
192
193 IF sql%FOUND THEN
194
195 RAISE ok_found;
196
197 END IF;
198
199 EXCEPTION
200
201 WHEN NO_DATA_FOUND THEN
202
203 SELECT pa_project_retentions_s.NEXTVAL
204 INTO l_project_retn_id
205 FROM DUAL;
206
207 IF g1_debug_mode = 'Y' THEN
208 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert NEW Record Project Retentions');
209 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
210 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
211 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
212 END IF;
213
214 INSERT INTO pa_project_retentions
215 ( PROJECT_RETENTION_ID,
216 PROJECT_ID,
217 TASK_ID,
218 AGREEMENT_ID,
219 RETENTION_RULE_ID,
220 INVPROC_CURRENCY_CODE,
221 TOTAL_RETAINED,
222 PROJFUNC_CURRENCY_CODE,
223 PROJFUNC_TOTAL_RETAINED,
224 PROJECT_CURRENCY_CODE,
225 PROJECT_TOTAL_RETAINED,
226 FUNDING_CURRENCY_CODE,
227 FUNDING_TOTAL_RETAINED,
228 PROGRAM_APPLICATION_ID,
229 PROGRAM_UPDATE_DATE,
230 REQUEST_ID,
231 CREATION_DATE,
232 CREATED_BY,
233 LAST_UPDATE_DATE,
234 LAST_UPDATED_BY)
235 VALUES(l_project_retn_id,
236 p_project_id,
237 p_task_id,
238 p_agreement_id,
239 p_retention_rule_id,
240 p_invproc_currency,
241 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_amount, p_invproc_currency),
242 p_projfunc_currency,
243 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_projfunc_amount,p_projfunc_currency),
244 p_project_currency,
245 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_project_amount,p_project_currency),
246 p_funding_currency,
247 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_funding_amount,p_funding_currency),
248 l_program_application_id,
249 l_program_update_date,
250 p_request_id,
251 sysdate,
252 l_last_updated_by,
253 l_last_update_date,
254 l_last_updated_by);
255
256 WHEN ok_found THEN
257
258 IF g1_debug_mode = 'Y' THEN
259 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Project Retentions');
260 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
261 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
262 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
263 END IF;
264
265 UPDATE pa_project_retentions
266 SET total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
267 NVL(total_retained,0) + NVL(p_amount,0), invproc_currency_code),
268 project_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
269 NVL(project_total_retained,0) + NVL(p_project_amount,0),project_currency_code),
270 projfunc_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
271 NVL(projfunc_total_retained,0) + NVL(p_projfunc_amount,0),projfunc_currency_code),
272 funding_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
273 NVL(funding_total_retained,0) + NVL(p_funding_amount,0), funding_currency_code)
274 WHERE project_id = p_project_id
275 AND agreement_id = p_agreement_id
276 AND retention_rule_id = p_retention_rule_id;
277
278 END;
279
280 -- Update the project,agreement and task level balance
281
282 BEGIN
283 IF g1_debug_mode = 'Y' THEN
284 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
285 END IF;
286
287 SELECT 'Y' INTO TmpFlag
288 FROM pa_summary_project_retn
289 WHERE project_id = p_project_id
290 AND nvl(task_id,-99) = NVL(p_task_id,-99)
291 AND agreement_id = p_agreement_id;
292
293 IF sql%FOUND THEN
294
295 RAISE spr_found;
296
297 END IF;
298
299 EXCEPTION
300
301 WHEN NO_DATA_FOUND THEN
302
303 IF g1_debug_mode = 'Y' THEN
304 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert New Record SPR ');
305 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
306 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
307 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
308 END IF;
309
310 INSERT INTO pa_summary_project_retn
311 (
312 PROJECT_ID,
313 TASK_ID,
314 AGREEMENT_ID,
315 CUSTOMER_ID,
316 INVPROC_CURRENCY_CODE,
317 TOTAL_RETAINED,
318 PROJFUNC_CURRENCY_CODE,
319 PROJFUNC_TOTAL_RETAINED,
320 PROJECT_CURRENCY_CODE,
321 PROJECT_TOTAL_RETAINED,
322 FUNDING_CURRENCY_CODE,
323 FUNDING_TOTAL_RETAINED,
324 PROGRAM_APPLICATION_ID,
325 PROGRAM_UPDATE_DATE,
326 REQUEST_ID,
327 CREATION_DATE,
328 CREATED_BY,
329 LAST_UPDATE_DATE,
330 LAST_UPDATED_BY)
331 VALUES(
332 p_project_id,
333 p_task_id,
334 p_agreement_id,
335 p_customer_id,
336 p_invproc_currency,
337 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_amount,p_invproc_currency),
338 p_projfunc_currency,
339 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_projfunc_amount,p_projfunc_currency),
340 p_project_currency,
341 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_project_amount,p_project_currency),
342 p_funding_currency,
343 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_funding_amount, p_funding_currency),
344 l_program_application_id,
345 l_program_update_date,
346 p_request_id,
347 sysdate,
348 l_last_updated_by,
349 l_last_update_date,
350 l_last_updated_by);
351
352 WHEN spr_found THEN
353
354 IF g1_debug_mode = 'Y' THEN
355 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
356 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
357 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
358 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
359 END IF;
360
361 UPDATE pa_summary_project_retn
362 SET total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
363 NVL(total_retained,0) + NVL(p_amount,0), invproc_currency_code),
364 project_total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
365 NVL(project_total_retained,0) + NVL(p_project_amount,0),project_currency_code),
366 projfunc_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
367 NVL(projfunc_total_retained,0) + NVL(p_projfunc_amount,0),projfunc_currency_code),
368 funding_total_retained =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
369 NVL(funding_total_retained,0) + NVL(p_funding_amount,0),funding_currency_code)
370 WHERE project_id = p_project_id
371 AND NVL(task_id,-99) = NVL(p_task_id,-99)
372 AND agreement_id = p_agreement_id;
373
374 END;
375
376 IF g1_debug_mode = 'Y' THEN
377 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Rule level Balance ');
378 END IF;
379 -- Update the rule level balance
380 IF g1_debug_mode = 'Y' THEN
381 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
382 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
383 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
384 END IF;
385
386
387 UPDATE pa_proj_retn_rules
388 SET total_retained = NVL(total_retained,0) +
389 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( NVL(p_amount,0),p_invproc_currency),
390 projfunc_total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
391 NVL(projfunc_total_retained,0) + NVL(p_projfunc_amount,0), p_projfunc_currency),
392 project_total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
393 NVL(project_total_retained,0) + NVL(p_project_amount,0), p_project_currency)
394 WHERE retention_rule_id = p_retention_rule_id;
395
396 END IF; -- Handle the old Credit memos
397
398 ELSIF p_change_type = 'BILLED' THEN
399
400 -- Update project or top task, agreement level
401 IF g1_debug_mode = 'Y' THEN
402 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR for Billed Amount ');
403 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
404 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
409 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
410 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
411 END IF;
412
413 UPDATE pa_summary_project_retn
414 SET total_billed = NVL(total_billed,0) +
415 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( NVL(p_amount,0),p_invproc_currency),
416 project_total_billed = NVL(project_total_billed,0) +
417 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( NVL(p_project_amount,0),p_project_currency),
418 projfunc_total_billed = NVL(projfunc_total_billed,0) +
419 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(NVL(p_projfunc_amount,0),p_projfunc_currency),
420 funding_total_billed = NVL(funding_total_billed,0) +
421 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(NVL(p_funding_amount,0), p_funding_currency)
422 WHERE project_id = p_project_id
423 AND NVL(task_id,-99) = NVL(p_task_id,-99)
424 AND agreement_id = p_agreement_id;
425
426 IF g1_debug_mode = 'Y' THEN
427 pa_retention_util.write_log('No of Records are Updated : ' || sql%rowcount);
428 END IF;
429
430 END IF;
431
432
433
434 /*
435 Bug: 2385742.
436 Since the billing invoice will refer this table to find the retention rule id
437 it should not be deleted
438
439 Delete the 0 Amount Summary Retn Records and project_retention records
440 IF g1_debug_mode = 'Y' THEN
441 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Delete from pa_project_retentions ');
442 END IF;
443
444 DELETE FROM pa_project_retentions
445 WHERE project_id = p_project_id
446 AND NVL(task_id,-99) = NVL(p_task_id,-99)
447 AND agreement_id = p_agreement_id
448 AND retention_rule_id = p_retention_rule_id
449 AND NVL(total_retained,0) = 0
450 AND NVL(project_total_retained,0) = 0
451 AND NVL(projfunc_total_retained,0) = 0
452 AND NVL(funding_total_retained,0) = 0;
453
454
455 IF g1_debug_mode = 'Y' THEN
456 pa_retention_util.write_log('No of Records are deleted : ' || sql%rowcount);
457 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Delete from pa_summary_project_retn ');
458 END IF;
459
460 DELETE FROM pa_summary_project_retn
461 WHERE project_id = p_project_id
462 AND NVL(task_id,-99) = NVL(p_task_id,-99)
463 AND agreement_id = p_agreement_id
464 AND NVL(total_retained,0) = 0
465 AND NVL(project_total_retained,0) = 0
466 AND NVL(projfunc_total_retained,0) = 0
467 AND NVL(funding_total_retained,0) = 0
468 AND NVL(total_billed,0) = 0
469 AND NVL(project_total_billed,0) = 0
470 AND NVL(projfunc_total_billed,0) = 0
471 AND NVL(funding_total_billed,0) = 0;
472
473 IF g1_debug_mode = 'Y' THEN
474 pa_retention_util.write_log('No of Records are deleted : ' || sql%rowcount);
475 END IF;
476
477 Bug: 2385742 end of the code changes */
478
479 IF g1_debug_mode = 'Y' THEN
480 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Leaving from Update Retention Balances ');
481 END IF;
482 EXCEPTION
483 WHEN OTHERS THEN
484 IF g1_debug_mode = 'Y' THEN
485 pa_retention_util.write_log('Update_Retention_Balances: ' || 'Oracle Error ' || sqlerrm);
486 END IF;
487 RAISE;
488
489 END Update_Retention_Balances;
490 -- Procedure Update_Retn_Bill_Trans_Amount
491 -- Purpose Bill trans amount should be updated only if the project is invoice by bill trans currency
492
493 PROCEDURE Update_Retn_Bill_Trans_Amount(p_project_id IN NUMBER,
494 p_draft_invoice_num IN NUMBER,
495 p_bill_trans_currency IN VARCHAR2,
496 p_request_id IN NUMBER) IS
497
498 BEGIN
499 UPDATE pa_draft_invoice_items
500 SET bill_trans_currency_code = p_bill_trans_currency,
501 inv_amount =PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(bill_trans_bill_amount, trim(p_bill_trans_currency)),
502 request_id = p_request_id
503 WHERE DRAFT_invoice_num = p_draft_invoice_num
504 AND invoice_line_type = 'RETENTION'
505 AND project_id = p_project_id;
506
507 EXCEPTION
508 WHEN OTHERS THEN
509 IF g1_debug_mode = 'Y' THEN
510 pa_retention_util.write_log('Update_Retn_Bill_Trans_Amount: ' || 'Oracle Error ' || sqlerrm);
511 END IF;
512 RAISE;
513
514 END Update_Retn_Bill_Trans_Amount;
515
516
517 --- Procedure Create_Proj_Inv_Retn_lines
518 --- Create Project Invoice Retention Lines
519
520 PROCEDURE Create_Proj_Inv_Retn_Lines( p_project_id IN NUMBER,
521 p_customer_id IN NUMBER,
522 p_agreement_id IN NUMBER,
523 p_draft_invoice_num IN NUMBER,
524 p_cust_retn_level IN VARCHAR2,
525 p_request_id IN NUMBER,
526 p_output_tax_code IN VARCHAR2,
527 p_Output_tax_exempt_flag IN VARCHAR2,
528 p_Output_tax_exempt_number IN VARCHAR2,
529 p_Output_exempt_reason_code IN VARCHAR2) IS
530
531 TYPE RuleAndInvoiced IS RECORD
532 (retention_rule_id NUMBER,
533 source_type VARCHAR2(10),
534 Invoice_Amount NUMBER,
535 PFC_Invoice_Amount NUMBER,
536 PC_Invoice_Amount NUMBER,
537 FC_Invoice_Amount NUMBER,
538 BTC_Invoice_Amount NUMBER);
539
540 TYPE TabRuleAndInvoiced IS TABLE OF RuleAndInvoiced
541 INDEX BY BINARY_INTEGER;
542
543 TmpRuleAndInvoiced TabRuleAndInvoiced;
544
545 AgreementId NUMBER:=p_agreement_id;
546
547 TmpRetainAmount NUMBER:=0;
548 RetentionRuleId NUMBER:=0;
549 NewRetentionRuleId NUMBER:=0;
550 InvoiceAmount NUMBER:=0;
551 PFCInvoiceAmount NUMBER:=0;
552 PCInvoiceAmount NUMBER:=0;
553 FCInvoiceAmount NUMBER:=0;
554 BTCInvoiceAmount NUMBER:=0;
555
556 CurRetainAmount NUMBER:=0;
557 PFCCurRetainAmount NUMBER:=0;
558 PCCurRetainAmount NUMBER:=0;
559 FCCurRetainAmount NUMBER:=0;
560 BTCCurRetainAmount NUMBER:=0;
561
562 LastUpdatedBy NUMBER:= fnd_global.user_id;
563 l_created_by NUMBER:= fnd_global.user_id;
564 l_program_id NUMBER:= fnd_global.conc_program_id;
565 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
566 l_program_update_date DATE := sysdate;
567 l_last_update_date DATE := sysdate;
568 l_last_updated_by NUMBER:= fnd_global.user_id;
569 l_last_update_login NUMBER:= fnd_global.login_id;
570
571 ProjectCurrency VARCHAR2(15);
572 ProjFuncCurrency VARCHAR2(15);
573 FundingCurrency VARCHAR2(15);
574 InvProcCurrency VARCHAR2(15);
575 InvProcCurrType VARCHAR2(30); --Added for Bug3604143
576 BillTransCurrency VARCHAR2(15);
577
578 InvRetnLineFmt Pa_Retention_Pkg.TabInvRetnLineFormat;
579
580 TmpCnt BINARY_INTEGER:=0;
581 UpdateRDL BOOLEAN := FALSE;
582 UpdateERDL BOOLEAN := FALSE;
583 UpdateDII BOOLEAN := FALSE;
584 LastLineNum NUMBER:=0;
585 RetnRemainAmount NUMBER:=0;
586 RetnLineText VARCHAR(500);
587 RetnRuleText VARCHAR(300); /** Increased Length to 300 from 120 bug 2318898 **/
588 LastEndPosition NUMBER :=0;
589
590
591 CURSOR cur_invoice IS
592 SELECT AMT.retention_rule_id retention_rule_id,
593 AMT.source_type source_type,
594 AMT.invoice_amount invoice_amount,
595 AMT.pfc_invoice_amount pfc_invoice_amount,
596 AMT.pc_invoice_amount pc_invoice_amount,
597 AMT.fc_invoice_amount fc_invoice_amount,
598 AMT.btc_invoice_amount btc_invoice_amount
599 FROM ( SELECT rdl.retention_rule_id retention_rule_id,'RDL' source_type,
600 SUM(rdl.bill_amount) invoice_amount,
601 SUM(rdl.projfunc_bill_amount) pfc_invoice_amount,
602 SUM(rdl.project_bill_amount) pc_invoice_amount,
603 SUM(rdl.funding_bill_amount) fc_invoice_amount,
604 SUM(rdl.bill_trans_bill_amount) btc_invoice_amount
605 FROM pa_cust_rev_dist_lines_all rdl
606 WHERE rdl.project_id = p_project_id
607 AND rdl.request_id = p_request_id
608 AND rdl.draft_invoice_num = p_draft_invoice_num
609 GROUP BY rdl.retention_rule_id
610 UNION
611 SELECT erdl.retention_rule_id retention_rule_id,
612 'ERDL' source_type,
613 --SUM(erdl.amount) invoice_amount, --Modified for Bug3604143
614 decode(InvProcCurrType, 'PROJECT_CURRENCY', SUM(erdl.project_bill_amount),
615 'PROJFUNC_CURRENCY', SUM(erdl.projfunc_bill_amount),
616 'FUNDING_CURRENCY', SUM(erdl.funding_bill_amount)) invoice_amount,
617 SUM(erdl.projfunc_bill_amount) pfc_invoice_amount,
618 SUM(erdl.project_bill_amount) pc_invoice_amount,
619 SUM(erdl.funding_bill_amount) fc_invoice_amount,
620 SUM(erdl.bill_trans_amount) btc_invoice_amount
621 FROM pa_cust_event_rdl_all erdl
622 WHERE erdl.project_id = p_project_id
623 AND erdl.request_id = p_request_id
624 AND erdl.draft_invoice_num = p_draft_invoice_num
625 GROUP BY erdl.retention_rule_id
626 UNION
627 SELECT dii.retention_rule_id retention_rule_id,
628 'EVENT' source_type,
629 SUM(dii.amount) invoice_amount,
630 SUM(dii.projfunc_bill_amount) pfc_invoice_amount,
631 SUM(dii.project_bill_amount) pc_invoice_amount,
632 SUM(dii.funding_bill_amount) fc_invoice_amount,
633 SUM(dii.bill_trans_bill_amount) btc_invoice_amount
634 FROM pa_draft_invoice_items dii
635 WHERE dii.project_id = p_project_id
636 AND dii.request_id = p_request_id
637 AND dii.draft_invoice_num = p_draft_invoice_num
638 AND dii.event_num IS NOT NULL
639 GROUP BY dii.retention_rule_id ) AMT,
640 PA_PROJ_RETN_RULES RT
641 where amt.retention_rule_id = RT.retention_rule_id
642 ORDER BY RT.task_id, RT.expenditure_category, RT.expenditure_type, RT.NON_LABOR_RESOURCE,
643 RT.REVENUE_CATEGORY_CODE, RT.EVENT_TYPE, RT.EFFECTIVE_START_DATE, RT.EFFECTIVE_END_DATE;
644
645 invoice_rec cur_invoice%ROWTYPE;
646
647 CURSOR cur_retn_rule IS
648 SELECT rt.retention_rule_id retention_rule_id,
649 rt.retention_percentage retention_percentage,
650 rt.retention_amount retention_amount,
651 rt.threshold_amount threshold_amount ,
652 rt.total_retained total_retained,
653 rt.retention_level_code retention_level_code,
654 rt.non_labor_resource non_labor_resource,
655 rt.expenditure_type expenditure_type,
656 rt.expenditure_category expenditure_category,
657 rt.event_type event_type,
658 rt.revenue_category_code revenue_category_code,
659 rt.effective_start_date effective_start_date,
660 rt.effective_end_date effective_end_date ,
661 tsk.task_number task_number,
662 tsk.task_name task_name,
663 rt.task_id task_id
664 FROM pa_proj_retn_rules rt, pa_tasks tsk
665 WHERE rt.retention_rule_id = RetentionRuleID
666 AND rt.task_id = tsk.task_id(+);
667
668 retn_rule_rec cur_retn_rule%ROWTYPE;
669
670 BEGIN
671
672 IF g1_debug_mode = 'Y' THEN
673 pa_retention_util.write_log('Entering Create_Proj_Inv_Retn_Lines');
674 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : ' || p_project_id);
675 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Request Id : ' || p_request_id);
676 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Num : ' || p_draft_invoice_num);
677 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Agrrement Id : ' || AgreementId);
678 END IF;
679
680 --Moved the following code here for Bug3604143
681 -- Select the Funding Currency
682
683 SELECT agreement_currency_code
684 INTO FundingCurrency
685 FROM pa_agreements_all agr,pa_draft_invoices_all di
686 WHERE agr.agreement_id = di.agreement_id
687 AND di.project_id = p_project_id
688 AND di.draft_invoice_num = p_draft_invoice_num;
689
690 SELECT pr.project_currency_code,
691 pr.projfunc_currency_code,
692 decode(pr.invproc_currency_type,'PROJECT_CURRENCY', pr.project_currency_code,
693 'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
694 'FUNDING_CURRENCY', FundingCurrency) Invproc_currency, pr.invproc_currency_type
695 INTO ProjectCurrency, ProjFuncCUrrency, InvProcCurrency, InvProcCurrType
696 FROM pa_projects_all pr
697 WHERE pr.project_id = p_project_id;
698 --till here for Bug3604143
699
700 OPEN cur_invoice;
701 IF g1_debug_mode = 'Y' THEN
702 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Build PL/SQL Table for Rules ');
703 END IF;
704 LOOP
705 FETCH cur_invoice INTO invoice_rec;
706 EXIT WHEN cur_invoice%NOTFOUND;
707 TmpCnt := NVL(TmpCnt,0) +1;
708 IF g1_debug_mode = 'Y' THEN
709 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule Table for ' || TmpCnt);
710 END IF;
711
712 TmpRuleAndInvoiced(TmpCnt).retention_rule_id := invoice_rec.retention_rule_id;
713 TmpRuleAndInvoiced(TmpCnt).source_type := invoice_rec.source_type;
714 TmpRuleAndInvoiced(TmpCnt).invoice_amount := invoice_rec.invoice_amount;
715 TmpRuleAndInvoiced(TmpCnt).pfc_invoice_amount := invoice_rec.pfc_invoice_amount;
716 TmpRuleAndInvoiced(TmpCnt).pc_invoice_amount := invoice_rec.pc_invoice_amount;
717 TmpRuleAndInvoiced(TmpCnt).fc_invoice_amount := invoice_rec.fc_invoice_amount;
718 TmpRuleAndInvoiced(TmpCnt).btc_invoice_amount := invoice_rec.btc_invoice_amount;
719
720 IF g1_debug_mode = 'Y' THEN
721 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule ID : ' || invoice_rec.retention_rule_id);
722 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || invoice_rec.source_type);
723 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
726 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'fc_invoice_amount : ' || invoice_rec.fc_invoice_amount);
727 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'btc_invoice_amount : ' || invoice_rec.btc_invoice_amount);
728 END IF;
729
730 END LOOP;
731
732 CLOSE cur_invoice;
733
734 IF NVL(TmpCnt,0) <> 0 THEN -- Count is not equal zero
735
736 IF NVL(TmpCnt,0) >= 2 THEN
737
738 IF TmpRuleAndInvoiced(TmpCnt).retention_rule_id <>
739 TmpRuleAndInvoiced(TmpCnt-1).retention_rule_id THEN
740
741 TmpCnt := NVL(TmpCnt,0)+1;
742
743 TmpRuleAndInvoiced(TmpCnt).retention_rule_id := TmpRuleAndInvoiced(TmpCnt-1).retention_rule_id ;
744 TmpRuleAndInvoiced(TmpCnt).source_type := TmpRuleAndInvoiced(TmpCnt-1).source_type;
745 TmpRuleAndInvoiced(TmpCnt).invoice_amount := 0;
746 TmpRuleAndInvoiced(TmpCnt).pfc_invoice_amount := 0;
747 TmpRuleAndInvoiced(TmpCnt).pc_invoice_amount := 0;
748 TmpRuleAndInvoiced(TmpCnt).fc_invoice_amount := 0;
749 TmpRuleAndInvoiced(TmpCnt).btc_invoice_amount := 0;
750
751 END IF;
752
753 END IF;
754
755
756 IF g1_debug_mode = 'Y' THEN
757 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Select Funding Currency ');
758 END IF;
759
760 /* Commented and moved this code in the beginning for Bug3604143
761 -- Select the Funding Currency
762
763 SELECT agreement_currency_code
764 INTO FundingCurrency
765 FROM pa_agreements_all agr,pa_draft_invoices_all di
766 WHERE agr.agreement_id = di.agreement_id
767 AND di.project_id = p_project_id
768 AND di.draft_invoice_num = p_draft_invoice_num;
769 */
770
771 IF g1_debug_mode = 'Y' THEN
772 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Funding Currency : ' || FundingCurrency);
773 END IF;
774
775 -- Get all the currency code for this project
776
777 IF g1_debug_mode = 'Y' THEN
778 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Finding Invoice Processing Currency');
779 END IF;
780
781 /* Commented and moved this code in the beginning for Bug3604143
782 SELECT pr.project_currency_code,
783 pr.projfunc_currency_code,
784 decode(pr.invproc_currency_type,'PROJECT_CURRENCY', pr.project_currency_code,
785 'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
786 'FUNDING_CURRENCY', FundingCurrency) Invproc_currency
787 INTO ProjectCurrency, ProjFuncCUrrency, InvProcCurrency
788 FROM pa_projects_all pr
789 WHERE pr.project_id = p_project_id;
790 */
791
792 IF g1_debug_mode = 'Y' THEN
793 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Processing Currency : ' || InvProcCurrency);
794 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Calling Get_Proj_Inv_Retn_Format');
795 END IF;
796
797 InvRetnLineFmt := Get_Proj_Inv_Retn_Format(p_project_id =>p_project_id);
798
799
800 IF g1_debug_mode = 'Y' THEN
801 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Processing Rules');
802 END IF;
803
804 FOR j IN 1..TmpCnt LOOP -- For loop starts for Retention Rules
805
806 IF g1_debug_mode = 'Y' THEN
807 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Row Num : ' || j);
808 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Current RetentionRuleId : ' || TmpRuleAndInvoiced(j).retention_rule_id);
809 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetentionRuleId : ' || RetentionRuleId);
810 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'NewRetentionRuleId : ' || NewRetentionRuleId);
811 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || TmpRuleAndInvoiced(j).source_type);
812 END IF;
813
814 IF NVL(RetentionRuleId,0) = 0 and NVL(NewRetentionRuleID,0) =0 THEN -- If it is a first run
815
816 IF g1_debug_mode = 'Y' THEN
817 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'For First Rule ');
818 END IF;
819
820 NewRetentionRuleId := TmpRuleAndInvoiced(j).retention_rule_id;
821 RetentionRuleId := TmpRuleAndInvoiced(j).retention_rule_id;
822 InvoiceAmount :=0;
823 PFCInvoiceAmount:=0;
824 PCInvoiceAmount :=0;
825 FCInvoiceAmount :=0;
826 BTCInvoiceAmount :=0; --For Bug 5194917
827
828 IF g1_debug_mode = 'Y' THEN
829 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run RetentionRuleId : ' || RetentionRuleId);
830 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run NewRetentionRuleId : ' || NewRetentionRuleId);
831 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run Source Type : ' || TmpRuleAndInvoiced(j).source_type);
832 END IF;
833
834 IF RTRIM(TmpRuleAndInvoiced(j).source_type) ='RDL' THEN
835
836 UpdateRDL := TRUE;
837 IF g1_debug_mode = 'Y' THEN
838 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateRDL= True');
839 END IF;
840
841 ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='ERDL' THEN
842
843 UpdateERDL := TRUE;
844
845 IF g1_debug_mode = 'Y' THEN
846 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateERDL= True');
847 END IF;
848
849 ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='EVENT' THEN
850
851 UpdateDII := TRUE;
852
853 IF g1_debug_mode = 'Y' THEN
854 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateDII= True');
855 END IF;
856
857 END IF;
858
859 ELSE -- for rule is different
860 NewRetentionRuleId := TmpRuleAndInvoiced(j).retention_rule_id;
861
862 END IF;
863
864 IF NVL(RetentionRuleID,0) = NVL(NewRetentionRuleId,0) THEN -- if the rule is same
865
866 InvoiceAmount := InvoiceAmount + NVL(TmpRuleAndInvoiced(j).Invoice_Amount,0);
867 PFCInvoiceAmount:= PFCInvoiceAmount + NVL(TmpRuleAndInvoiced(j).PFC_Invoice_Amount,0);
868 PCInvoiceAmount := PCInvoiceAmount + NVL(TmpRuleAndInvoiced(j).PC_Invoice_Amount,0);
869 FCInvoiceAmount := FCInvoiceAmount + NVL(TmpRuleAndInvoiced(j).FC_Invoice_Amount,0);
870 BTCInvoiceAmount:= BTCInvoiceAmount + NVL(TmpRuleAndInvoiced(j).BTC_Invoice_Amount,0);
871
872 IF RTRIM(TmpRuleAndInvoiced(j).source_type) ='RDL' THEN
873
874 UpdateRDL := TRUE;
875
876 IF g1_debug_mode = 'Y' THEN
877 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateRDL= True');
878 END IF;
879
880 ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='ERDL' THEN
881
882 UpdateERDL := TRUE;
883 IF g1_debug_mode = 'Y' THEN
884 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateERDL= True');
885 END IF;
886
887 ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='EVENT' THEN
888
889 UpdateDII := TRUE;
890 IF g1_debug_mode = 'Y' THEN
891 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateDII= True');
892 END IF;
893
894 END IF;
895
896 END IF;
897
898 IF (RetentionRuleID <> NewRetentionRuleId) OR (j=TmpCnt) THEN -- rule changes
899
900 IF g1_debug_mode = 'Y' THEN
901 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Create Retention Line ');
902 END IF;
903
904 BEGIN
905 OPEN cur_retn_rule;
906 FETCH cur_retn_rule INTO retn_rule_rec;
907
908 -- Find the whethoer any amounts to be retained or not
909
910 IF g1_debug_mode = 'Y' THEN
911 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Check Threshold');
912 END IF;
913
914 IF ( ( NVL(retn_rule_rec.threshold_amount,0) >
915 NVL(retn_rule_rec.total_retained,0) )
916 OR (NVL(retn_rule_rec.threshold_amount,0)=0) ) THEN
917
918 IF g1_debug_mode = 'Y' THEN
919 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || ' If NVL(retn_rule_rec.threshold_amount,0) >
920 NVL(retn_rule_rec.total_retained,0)');
921 END IF;
922
923 -- Remaining amount to retain
924
925 IF NVL(retn_rule_rec.threshold_amount,0)<> 0 THEN
926
927 IF g1_debug_mode = 'Y' THEN
928 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || ' IF NVL(retn_rule_rec.threshold_amount,0)<> 0 ');
929 END IF;
930
931 RetnRemainAmount :=NVL(retn_rule_rec.threshold_amount,0) -
932 NVL(retn_rule_rec.total_retained,0);
933 IF g1_debug_mode = 'Y' THEN
934 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.threshold_amount: ' || retn_rule_rec.threshold_amount);
935 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.total_retained: ' || retn_rule_rec.total_retained);
936 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetnRemainAmount: ' || RetnRemainAmount);
937 END IF;
938
939 END IF;
940
941 IF g1_debug_mode = 'Y' THEN
942 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetnRemainAmount: ' || RetnRemainAmount);
943 END IF;
944
945 -- Check if the retention percentage is not equal to zero
946
947 IF NVL(retn_rule_rec.retention_percentage,0) <> 0 THEN
948 IF g1_debug_mode = 'Y' THEN
949 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the percentage is not equal to zero' );
950 END IF;
951
952 TmpRetainAmount := NVL(InvoiceAmount,0) *
953 (NVL(retn_rule_rec.retention_percentage,0)/100);
954 IF g1_debug_mode = 'Y' THEN
955 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
956 END IF;
957 --For Bug 5194917
958 /* ELSIF NVL(retn_rule_rec.retention_percentage,0) = 0 THEN
959 IF g1_debug_mode = 'Y' THEN
960 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the percentage is equal to zero' );
961 END IF;
962
963 TmpRetainAmount := 0;
964 IF g1_debug_mode = 'Y' THEN
965 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
966 END IF; Commented for Bug 6152291*/
967 --End of changes for Bug 5194917
968 ELSIF NVL(retn_rule_rec.retention_amount,0) <> 0 THEN
969
970 -- Check if the retention amount is not equal to zero
971 IF g1_debug_mode = 'Y' THEN
972 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the retention amount is not equal to zero' );
973 END IF;
974
975 TmpRetainAmount := NVL(retn_rule_rec.retention_amount,0);
976 IF g1_debug_mode = 'Y' THEN
977 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
978 END IF;
979
980 IF NVL(retn_rule_rec.retention_amount,0) > NVL(InvoiceAmount,0) THEN
981 IF g1_debug_mode = 'Y' THEN
982 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Retention Amount > InvoiceAmount');
983 END IF;
984 TmpRetainAmount := NVL(InvoiceAmount,0);
985 IF g1_debug_mode = 'Y' THEN
986 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
987 END IF;
988 END IF;
989 --For Bug 5194917
990 ELSIF NVL(retn_rule_rec.retention_amount,0) = 0 and NVL(retn_rule_rec.retention_percentage,0) = 0 THEN /* Added and condition for bug 6152291*/
991
992 -- Check if the retention amount is equal to zero
993 IF g1_debug_mode = 'Y' THEN
994 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the retention amount is equal to zero' );
995 END IF;
996
997 TmpRetainAmount := 0;
998 IF g1_debug_mode = 'Y' THEN
999 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
1000 END IF;
1001 --End of Bug 5194917
1002 END IF;
1003
1004 -- There is no threshold,retain full amount
1005
1006 IF NVL(retn_rule_rec.threshold_amount,0)<> 0 THEN
1007
1008 -- If the Current Retain amount is less or equal
1009 IF g1_debug_mode = 'Y' THEN
1010 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If threshold is not equal to zero ');
1011 END IF;
1012
1013 IF NVL(TmpRetainAmount,0) <= NVL(RetnRemainAmount,0) THEN
1014 IF g1_debug_mode = 'Y' THEN
1015 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If TmpRetainAmount <= RetnRemainAmount ');
1016 END IF;
1017
1018 CurRetainAmount := NVL(TmpRetainAmount,0);
1019
1020 IF g1_debug_mode = 'Y' THEN
1021 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount :' || CurRetainAmount );
1022 END IF;
1023
1024
1025 ELSIF NVL(TmpRetainAmount,0) > NVL(RetnRemainAmount,0) THEN
1026
1027 -- If the Current Retain amount is less or equal
1028 IF g1_debug_mode = 'Y' THEN
1029 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If TmpRetainAmount > RetnRemainAmount ');
1030 END IF;
1031
1032 CurRetainAmount := NVL(RetnRemainAmount,0);
1033 IF g1_debug_mode = 'Y' THEN
1034 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount :' || CurRetainAmount );
1035 END IF;
1036
1037 END IF;
1038 ELSE
1039 IF g1_debug_mode = 'Y' THEN
1040 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'No threshold');
1041 END IF;
1042 CurRetainAmount := NVL(TmpRetainAmount,0);
1043 IF g1_debug_mode = 'Y' THEN
1044 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount :' || CurRetainAmount );
1045 END IF;
1046 END IF;
1047
1048
1049 CurRetainAmount := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1050 NVL(CurRetainAmount,0),InvProcCurrency);
1051
1052 /* Changed for bug 3132449 .This is done to handle invoices with 0 amount */
1053
1054 IF NVL(CurRetainAmount,0) <>0 THEN
1055
1056 PFCCurRetainAmount := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1057 (NVL(CurRetainAmount,0)/
1058 NVL(InvoiceAmount,0)) * NVL(PFCInvoiceAmount,0),ProjfuncCurrency);
1059
1060 PCCurRetainAmount := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1061 ( NVL(CurRetainAmount,0)/
1062 NVL(InvoiceAmount,0)) * NVL(PCInvoiceAmount,0),ProjectCurrency);
1063
1064 FCCurRetainAmount := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1065 ( NVL(CurRetainAmount,0)/
1066 NVL(InvoiceAmount,0)) * NVL(FCInvoiceAmount,0), FundingCurrency);
1067
1068 BTCCurRetainAmount :=( NVL(CurRetainAmount,0)/
1069 NVL(InvoiceAmount,0)) * NVL(BTCInvoiceAmount,0);
1070
1071 ELSE
1072
1073 PFCCurRetainAmount :=0;
1074 PCCurRetainAmount :=0;
1075 FCCurRetainAmount :=0;
1076 BTCCurRetainAmount :=0;
1077 END IF; /*CurRetainAmount */
1078
1079 IF g1_debug_mode = 'Y' THEN
1080 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvoiceAmount : ' || InvoiceAmount);
1081 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount : ' || CurRetainAmount
1082 ||' ' || InvProcCurrency);
1083 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PFCCurRetainAmount : ' || PFCCurRetainAmount
1084 ||' ' || ProjFuncCUrrency);
1085 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PCCurRetainAmount : ' || PCCurRetainAmount
1086 ||' ' || ProjectCurrency);
1087 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'FCCurRetainAmount : ' || FCCurRetainAmount
1088 ||' ' || FundingCurrency);
1089 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'BTCCurRetainAmount : ' || BTCCurRetainAmount);
1090 END IF;
1091
1092 END IF;
1093
1094 IF NVL(CurRetainAmount,0) <>0 THEN
1095
1096 -- Building the Invoice Retention Line Format
1097 IF g1_debug_mode = 'Y' THEN
1098 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'IF NVL(CurRetainAmount,0) <>0 ');
1099 END IF;
1100
1101 IF InvRetnLineFmt.count <> 0 THEN
1102 IF g1_debug_mode = 'Y' THEN
1103 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt.count : ' ||
1104 InvRetnLineFmt.count);
1105 END IF;
1106
1107 FOR k IN 1..InvRetnLineFmt.Count LOOP
1108
1109 -- Set the last end position
1110 IF NVL(k,0) = 1 THEN
1111 LastEndPosition := InvRetnLineFmt(k).end_position;
1112 RetnLineText := RPAD(RetnLineText,
1113 InvRetnLineFmt(k).start_position
1114 -1,' ');
1115 ELSE
1116 RetnLineText :=
1117 RetnLineText ||
1118 RPAD(' ',
1119 InvRetnLineFmt(k).start_position
1120 -LastEndPosition,' ');
1121 LastEndPosition := InvRetnLineFmt(k).end_position;
1122
1123 END IF;
1124 IF g1_debug_mode = 'Y' THEN
1125 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt Row : ' || k);
1126 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt Column : ' || InvRetnLineFmt(k).column_code);
1127 END IF;
1128
1129 IF InvRetnLineFmt(k).column_code = 'RETENTION PERCENTAGE' THEN
1130
1131 IF InvRetnLineFmt(k).right_justify_flag = 'N' THEN
1132 RetnLineText := RetnLineText ||
1133 RPAD(TO_CHAR(NVL(retn_rule_rec.retention_percentage,
1134 retn_rule_rec.retention_amount)),
1135 InvRetnLineFmt(k).end_position-
1136 InvRetnLineFmt(k).start_position,' ');
1137 ELSE
1138 RetnLineText := RetnLineText ||
1139 LPAD(TO_CHAR(NVL(retn_rule_rec.retention_percentage,
1140 retn_rule_rec.retention_amount)),
1141 InvRetnLineFmt(k).end_position-
1142 InvRetnLineFmt(k).start_position,' ');
1143 END IF;
1144 IF g1_debug_mode = 'Y' THEN
1145 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Fmt Text : ' || RetnLineText);
1146 END IF;
1147
1148 ELSIF InvRetnLineFmt(k).column_code = 'TEXT' THEN
1149
1150 IF InvRetnLineFmt(k).right_justify_flag = 'Y' THEN
1151 RetnLineText := RetnLineText ||
1152 RPAD(InvRetnLineFmt(k).UserText,
1153 InvRetnLineFmt(k).end_position-
1154 InvRetnLineFmt(k).start_position,' ');
1155 ELSE
1156 RetnLineText := RetnLineText ||
1157 LPAD( InvRetnLineFmt(k).UserText,
1158 InvRetnLineFmt(k).end_position-
1159 InvRetnLineFmt(k).start_position,' ');
1160 END IF;
1161
1162 ELSIF InvRetnLineFmt(k).column_code = 'RETENTION BASIS AMOUNT' THEN
1163
1164 IF InvRetnLineFmt(k).right_justify_flag = 'Y' THEN
1165
1166 RetnLineText := RetnLineText ||
1167 RPAD(TO_CHAR(InvoiceAmount),
1168 InvRetnLineFmt(k).end_position-
1169 InvRetnLineFmt(k).start_position,' ');
1170 ELSE
1171 RetnLineText := RetnLineText ||
1172 LPAD(TO_CHAR(InvoiceAmount),
1173 InvRetnLineFmt(k).end_position-
1174 InvRetnLineFmt(k).start_position,' ');
1175
1176 END IF;
1177
1178 ELSIF InvRetnLineFmt(k).column_code = 'INVPROC CURRENCY CODE' THEN
1179
1180 IF InvRetnLineFmt(k).right_justify_flag = 'Y' THEN
1181
1182 RetnLineText := RetnLineText ||
1183 RPAD(InvProcCurrency,
1184 InvRetnLineFmt(k).end_position-
1185 InvRetnLineFmt(k).start_position,' ');
1186
1187 ELSE
1188 RetnLineText := RetnLineText ||
1189 LPAD(InvProcCurrency,
1190 InvRetnLineFmt(k).end_position-
1191 InvRetnLineFmt(k).start_position,' ');
1192
1193 END IF;
1194
1195 ELSIF InvRetnLineFmt(k).column_code = 'RETENTION RULE' THEN
1196
1197 RetnRuleText :=NULL;
1198
1199 IF p_cust_retn_level ='TOP_TASK' THEN
1200
1201 RetnRuleText := retn_rule_rec.task_name
1202 || ', ';
1203 END IF;
1204
1205 IF retn_rule_rec.retention_level_code ='PROJECT'
1206 OR retn_rule_rec.retention_level_code ='TOP_TASK' THEN
1207
1208 /* Release 12 : ATG Changes : adding date format for to_char */
1209
1210 RetnRuleText := RetnRuleText ||
1211 TO_CHAR(retn_rule_rec.effective_start_date, 'YYYY/MM/DD') || ' - ' ||
1212 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ;
1213
1214
1215 ELSIF retn_rule_rec.retention_level_code ='NON_LABOR' THEN
1216
1217 RetnRuleText := RetnRuleText ||
1218 retn_rule_rec.expenditure_category|| ', ' ||
1219 retn_rule_rec.expenditure_type|| ', ' ||
1220 retn_rule_rec.non_labor_resource || ',' ||
1221 TO_CHAR(retn_rule_rec.effective_start_date,'YYYY/MM/DD')
1222 || ' - ' ||
1223 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ;
1224
1225 ELSIF retn_rule_rec.retention_level_code ='EXPENDITURE_TYPE' THEN
1226
1227 RetnRuleText := RetnRuleText ||
1228 retn_rule_rec.expenditure_category|| ', ' ||
1229 retn_rule_rec.expenditure_type|| ', ' ||
1230 TO_CHAR(retn_rule_rec.effective_start_date,'YYYY/MM/DD')
1231 || ' - ' ||
1232 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ;
1233
1234
1235 ELSIF retn_rule_rec.retention_level_code ='EXPENDITURE_CATEGORY' THEN
1236
1237 RetnRuleText := RetnRuleText ||
1238 retn_rule_rec.expenditure_category|| ', ' ||
1239 TO_CHAR(retn_rule_rec.effective_start_date,'YYYY/MM/DD') || ' - ' ||
1240 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ;
1241
1242
1243 ELSIF retn_rule_rec.retention_level_code ='EVENT_TYPE' THEN
1244 /*The following code has been added to fix bug 3168266 */
1245 DECLARE
1246 l_revenue_category_meaning pa_lookups.meaning%TYPE;
1247 BEGIN
1248 SELECT meaning
1249 INTO l_revenue_category_meaning
1250 FROM pa_lookups
1251 WHERE lookup_code =retn_rule_rec.revenue_category_code
1252 AND lookup_type = 'REVENUE CATEGORY';
1253 RetnRuleText := RetnRuleText ||
1254 l_revenue_category_meaning|| ', ' ||
1255 retn_rule_rec.event_type|| ', ' ||
1256 TO_CHAR(retn_rule_rec.effective_start_date,'YYYY/MM/DD')
1257 || ' - ' ||
1258 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ;
1259 EXCEPTION
1260 WHEN OTHERS THEN
1261 NULL;
1262 END;
1263 /* RetnRuleText := RetnRuleText ||
1264 retn_rule_rec.revenue_category_code|| ', ' ||
1265 retn_rule_rec.event_type|| ', ' ||
1266 TO_CHAR(retn_rule_rec.effective_start_date,'YYYY/MM/DD')
1267 || ' - ' ||
1268 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ; commented for 3168266 */
1269
1270 ELSIF retn_rule_rec.retention_level_code ='REVENUE_CATEGORY'
1271 THEN
1272 /*The following code has been added to fix bug 3168266 */
1273 DECLARE
1274 l_revenue_category_meaning1 pa_lookups.meaning%TYPE;
1275 begin
1276 SELECT meaning
1277 INTO l_revenue_category_meaning1
1278 FROM pa_lookups
1279 WHERE lookup_code =retn_rule_rec.revenue_category_code
1280 AND lookup_type = 'REVENUE CATEGORY';
1281 RetnRuleText := RetnRuleText ||
1282 l_revenue_category_meaning1|| ', ' ||
1283 TO_CHAR(retn_rule_rec.effective_start_date,'YYYY/MM/DD')
1284 || ' - ' ||
1285 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ;
1286
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 NULL;
1290 END;
1291
1292 /* RetnRuleText := RetnRuleText ||
1293 retn_rule_rec.revenue_category_code|| ', ' ||
1294 TO_CHAR(retn_rule_rec.effective_start_date,'YYYY/MM/DD')
1295 || ' - ' ||
1296 TO_CHAR(retn_rule_rec.effective_end_date,'YYYY/MM/DD') ; commented for bug 3168266 */
1297
1298 END IF;
1299
1300 IF InvRetnLineFmt(k).right_justify_flag = 'Y' THEN
1301 RetnRuleText := RPAD(RetnRuleText,
1302 InvRetnLineFmt(k).end_position-
1303 InvRetnLineFmt(k).start_position,' ');
1304 ELSE
1305 RetnRuleText := LPAD( RetnRuleText,
1306 InvRetnLineFmt(k).end_position-
1307 InvRetnLineFmt(k).start_position,' ');
1308 END IF;
1309
1310 RetnLineText := RetnLineText ||
1311 RetnRuleText;
1312
1313 END IF;
1314
1315 END LOOP;
1316
1317 END IF;
1318
1319
1320 IF g1_debug_mode = 'Y' THEN
1321 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Line Text : ' || RetnLineText);
1322 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Calling Get_Invoice_Max_Line');
1323 END IF;
1324 -- Get the Last Invoice Line Number
1325 LastLineNum := Get_Invoice_Max_Line(p_project_id=>p_project_id,
1326 p_draft_invoice_num=>p_draft_invoice_num);
1327
1328 IF g1_debug_mode = 'Y' THEN
1329 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice_Max_Line : ' || LastLineNum);
1330 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : '||p_project_id);
1331 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Nun : '||p_draft_invoice_num);
1332 END IF;
1333
1334 -- Insert a Retention Line
1335
1336 INSERT INTO pa_draft_invoice_items
1337 ( PROJECT_ID,
1338 DRAFT_INVOICE_NUM,
1339 LINE_NUM,
1340 AMOUNT,
1341 TEXT,
1342 INVOICE_LINE_TYPE,
1343 TASK_ID,
1344 PROJFUNC_CURRENCY_CODE,
1345 PROJFUNC_BILL_AMOUNT,
1346 PROJECT_CURRENCY_CODE,
1347 PROJECT_BILL_AMOUNT,
1348 FUNDING_CURRENCY_CODE,
1349 FUNDING_BILL_AMOUNT,
1350 BILL_TRANS_BILL_AMOUNT,
1351 INVPROC_CURRENCY_CODE,
1352 RETENTION_RULE_ID,
1353 LAST_UPDATE_LOGIN,
1354 LAST_UPDATE_DATE,
1355 LAST_UPDATED_BY,
1356 CREATION_DATE,
1357 CREATED_BY,
1358 REQUEST_ID,
1359 PROGRAM_APPLICATION_ID,
1360 PROGRAM_ID,
1361 PROGRAM_UPDATE_DATE,
1362 OUTPUT_TAX_CLASSIFICATION_CODE,
1363 OUTPUT_TAX_EXEMPT_FLAG,
1364 /* Bug 3087998 Code and number order is different in values list. Changing here to match the same
1365 OUTPUT_TAX_EXEMPT_REASON_CODE,
1366 OUTPUT_TAX_EXEMPT_NUMBER
1367 */
1368 OUTPUT_TAX_EXEMPT_NUMBER,
1369 OUTPUT_TAX_EXEMPT_REASON_CODE
1370 )
1371 VALUES (p_project_id,
1372 p_draft_invoice_num,
1373 LastLineNum,
1374 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1375 (-1) * CurRetainAmount, InvProcCurrency),
1376 SUBSTR(RetnLineText,1,240),
1377 'RETENTION',
1378 retn_rule_rec.task_id,
1379 ProjFuncCUrrency,
1380 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1381 (-1) * PFCCurRetainAmount,ProjfuncCurrency),
1382 ProjectCurrency,
1383 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1384 (-1) * PCCurRetainAmount,ProjectCurrency),
1385 FundingCurrency,
1386 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1387 (-1) * FCCurRetainAmount,FundingCurrency),
1388 (-1) * BTCCurRetainAmount,
1389 InvProcCurrency,
1390 RetentionRuleId,
1391 l_last_update_login,
1392 sysdate,
1393 l_last_updated_by,
1394 sysdate,
1395 l_created_by,
1396 p_request_id,
1397 l_program_application_id,
1398 l_program_id,
1399 sysdate,
1400 p_Output_tax_code,
1401 p_Output_tax_exempt_flag,
1402 p_Output_tax_exempt_number,
1403 p_Output_exempt_reason_code);
1404 IF g1_debug_mode = 'Y' THEN
1405 pa_retention_util.write_log('Rows Inserted : '||sql%rowcount);
1406 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Call Update_Retention_Balances');
1407 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Retention Rule Id ' || retn_rule_rec.retention_rule_id);
1408 END IF;
1409
1410 Update_Retention_Balances(p_retention_rule_id=>retn_rule_rec.retention_rule_id,
1411 p_project_id =>p_project_id,
1412 p_task_id =>retn_rule_rec.task_id,
1413 p_agreement_id =>AgreementId,
1414 p_customer_id =>p_customer_id,
1415 p_amount =>CurRetainAmount,
1416 p_change_type =>'RETAINED',
1417 p_request_id =>p_request_id,
1418 p_invproc_currency =>InvProcCurrency,
1419 p_project_currency =>ProjectCurrency,
1420 p_project_amount => PCCurRetainAmount,
1421 p_projfunc_currency =>ProjFuncCurrency,
1422 p_projfunc_amount =>PFCCurRetainAmount,
1423 p_funding_currency =>FundingCurrency,
1424 p_funding_amount =>FCCurRetainAmount);
1425
1426 -- UPDATE RDL
1427
1428 IF (UpdateRDL) THEN
1429
1430 IF g1_debug_mode = 'Y' THEN
1431 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update RDLs ');
1432 END IF;
1433
1434 UPDATE pa_cust_rev_dist_lines_all
1435 SET retn_draft_invoice_num = p_draft_invoice_num,
1436 retn_draft_invoice_line_num = LastLineNum,
1437 retained_amount =
1438 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1439 ((NVL(bill_amount,0)/NVL(InvoiceAmount,0))
1440 * NVL(CurRetainAmount,0)),invproc_currency_code)
1441 WHERE retention_rule_id = retn_rule_rec.retention_rule_id
1442 AND draft_invoice_num = p_draft_invoice_num
1443 AND request_id = p_request_id
1444 AND project_id = p_project_id;
1445
1446 END IF;
1447
1448 -- Update ERDL
1449
1450 IF (UpdateERDL) THEN
1451 IF g1_debug_mode = 'Y' THEN
1452 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update ERDLs ');
1453 END IF;
1454
1455 UPDATE pa_cust_event_rdl_all
1456 SET retn_draft_invoice_num = p_draft_invoice_num,
1457 retn_draft_invoice_line_num = LastLineNum,
1458 retained_amount =
1459 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1460 ((NVL(amount,0)/NVL(InvoiceAmount,0))
1461 * NVL(CurRetainAmount,0)),invproc_currency_code)
1462 WHERE retention_rule_id = retn_rule_rec.retention_rule_id
1463 AND draft_invoice_num = p_draft_invoice_num
1464 AND request_id = p_request_id
1465 AND project_id = p_project_id;
1466
1467 END IF;
1468
1469 -- Update DII
1470
1471 IF (UpdateDII) THEN
1472
1473 IF g1_debug_mode = 'Y' THEN
1474 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update DII ');
1475 END IF;
1476
1477 UPDATE pa_draft_invoice_items
1478 SET retn_draft_invoice_num = p_draft_invoice_num,
1479 retn_draft_invoice_line_num = LastLineNum,
1480 retained_amount =
1481 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
1482 ((NVL(amount,0)/NVL(InvoiceAmount,0))
1483 * NVL(CurRetainAmount,0)), invproc_currency_code)
1484 WHERE retention_rule_id = retn_rule_rec.retention_rule_id
1485 AND draft_invoice_num = p_draft_invoice_num
1486 AND request_id = p_request_id
1487 AND project_id = p_project_id
1488 AND invoice_line_type <> 'RETENTION';
1489
1490 END IF;
1491
1492 END IF;
1493
1494 NewRetentionRuleId := TmpRuleAndInvoiced(j).retention_rule_id;
1495 RetentionRuleId := TmpRuleAndInvoiced(j).retention_rule_id;
1496 InvoiceAmount := NVL(TmpRuleAndInvoiced(j).Invoice_Amount,0);
1497 PFCInvoiceAmount := NVL(TmpRuleAndInvoiced(j).PFC_Invoice_Amount,0);
1498 PCInvoiceAmount := NVL(TmpRuleAndInvoiced(j).PC_Invoice_Amount,0);
1499 FCInvoiceAmount := NVL(TmpRuleAndInvoiced(j).FC_Invoice_Amount,0);
1500 BTCInvoiceAmount := NVL(TmpRuleAndInvoiced(j).BTC_Invoice_Amount,0); /* Bug 4947076: Invoice amounts are incorrect when
1501 Invoice by BTC option is enabled */
1502 UpdateRDL := FALSE;
1503 UpdateERDL := FALSE;
1504 UpdateDII := FALSE;
1505
1506 IF RTRIM(TmpRuleAndInvoiced(j).source_type) ='RDL' THEN
1507
1508 UpdateRDL := TRUE;
1509
1510 ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='ERDL' THEN
1511
1512 UpdateERDL:= TRUE;
1513
1514 ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='EVENT' THEN
1515
1516 UpdateDII := TRUE;
1517
1518 END IF;
1519
1520 CLOSE cur_retn_rule;
1521
1522 END;
1523
1524 END IF; -- new is not equal to current
1525
1526 IF g1_debug_mode = 'Y' THEN
1527 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Continue Row Num : ' || j);
1528 END IF;
1529
1530 END LOOP; -- For loop ends for Retention Rules
1531
1532 END IF; -- Count is not equal to zero
1533 IF g1_debug_mode = 'Y' THEN
1534 pa_retention_util.write_log('Leaving Create_Proj_Inv_Retn_Lines ');
1535 END IF;
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 IF g1_debug_mode = 'Y' THEN
1539 pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Oracle Error ' || sqlerrm);
1540 END IF;
1541 RAISE;
1542
1543 END Create_Proj_Inv_Retn_Lines;
1544 -- Procdure Proj_Invoice_Retn_PRocessing
1545 -- Purpose To retain the retention amount.
1546 -- This will be called after project invoice generation
1547
1548 PROCEDURE Proj_Invoice_Retn_Processing( p_project_id IN NUMBER,
1549 p_request_id IN NUMBER,
1550 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1551
1552 -- Cursor to select to projects for retention
1553 CURSOR cur_proj_cust IS SELECT pc.project_id project_id,
1554 pc.customer_id customer_id,
1555 pc.retention_level_code retention_level,
1556 pc.bill_to_address_id bill_to_address_id,
1557 pc.ship_to_address_id ship_to_address_id,
1558 imp.set_of_books_id set_of_books_id,
1559 pc.bill_to_customer_id bill_to_customer_id,
1560 pc.ship_to_customer_id ship_to_customer_id,/*Added for customer account relation*/
1561 /* TCA changes
1562 ras.site_use_id bill_site_use_id,
1563 ras1.site_use_id ship_site_use_id
1564 */
1565 hz_site.site_use_id bill_site_use_id,
1566 hz_site1.site_use_id ship_site_use_id
1567 FROM pa_project_customers pc,
1568 pa_projects_all pr,
1569 pa_implementations_all imp,
1570 /* TCA changes
1571 ra_site_uses ras,
1572 ra_site_uses ras1
1573 */
1574 hz_cust_site_uses hz_site,
1575 hz_cust_site_uses hz_site1
1576 WHERE EXISTS (SELECT NULL
1577 FROM pa_draft_invoices_all di,
1578 pa_agreements_all agr, pa_proj_retn_rules rt
1579 WHERE di.project_id = p_project_id
1580 AND di.request_id = p_request_id
1581 AND di.agreement_id = agr.agreement_id
1582 AND agr.customer_id = pc.customer_id
1583 AND rt.project_id = pc.project_id
1584 AND rt.customer_id = pc.customer_id
1585 AND ( (NVL(rt.threshold_amount,0) - NVL(rt.total_retained,0)) > 0
1586 OR NVL(threshold_amount,0) =0) )
1587 AND pc.project_id = p_project_id
1588 AND pc.project_id = pr.project_id
1589 /* Shared services changes: removed NVL from the org_id join.*/
1590 AND pr.org_id = imp.org_id
1591 /*Added for bug 2938422*/
1592 /* TCA changes
1593 and ras.address_id = pc.bill_to_address_id
1594 and ras.site_use_code = 'BILL_TO'
1595 and ras.status = 'A'
1596 and ras1.address_id = pc.ship_to_address_id
1597 and ras1.site_use_code = 'SHIP_TO'
1598 and ras1.status = 'A';
1599 */
1600 and hz_site.cust_acct_site_id = pc.bill_to_address_id
1601 and hz_site.site_use_code = 'BILL_TO'
1602 and hz_site.status = 'A'
1603 and hz_site1.cust_acct_site_id = pc.ship_to_address_id
1604 and hz_site1.site_use_code = 'SHIP_TO'
1605 and hz_site1.status = 'A';
1606
1607 /*End of change for bug 2938422*/
1608
1609 ProjCustRec cur_proj_cust%ROWTYPE;
1610
1611 CURSOR cur_proj_inv IS SELECT di.project_id,
1612 di.draft_invoice_num,
1613 agr.agreement_id agreement_id,
1614 NVL(pr.inv_by_bill_trans_curr_flag,'N') inv_by_bill_trans_curr_flag,
1615 di.inv_currency_code invoice_currency_code
1616 FROM pa_draft_invoices_all di, pa_agreements_all agr,
1617 pa_projects_all pr
1618 WHERE NVL(di.canceled_flag,'N') ='N'
1619 AND NVL(di.cancel_credit_memo_flag ,'N') ='N'
1620 AND NVL(di.draft_invoice_num_credited,0) = 0
1621 AND di.request_id = p_request_id
1622 AND di.project_id = p_project_id
1623 AND agr.agreement_id = di.agreement_id
1624 AND agr.customer_id = ProjCustRec.customer_id
1625 and di.project_id = pr.project_id;
1626
1627 ProjInvRec cur_proj_inv%ROWTYPE;
1628
1629 CustomerID NUMBER;
1630 DraftInvoiceNum number;
1631
1632 --Tmp_Output_vat_tax_id NUMBER;
1633 Tmp_output_Tax_code VARCHAR2(30);
1634 Tmp_Output_tax_exempt_flag VARCHAR2(2);
1635 -- Tmp_Output_tax_exempt_number VARCHAR2(30); --Modified for Bug3128094
1636 Tmp_Output_tax_exempt_number VARCHAR2(80);
1637 Tmp_Output_exempt_reason_code VARCHAR2(30);
1638 TmpSetOfBooks NUMBER;
1639 TmpBillToAddressID NUMBER;
1640 TmpShipToAddressID NUMBER;
1641 TmpUserId NUMBER:= fnd_global.user_id;
1642 TmpBillToCustomerId NUMBER;
1643 TmpShipToCustomerId NUMBER;/*Added for customer account relation*/
1644 TmpSiteUSeId1 NUMBER;
1645 TmpSiteUSeId2 NUMBER;/*Added for bug 2938422*/
1646
1647
1648 NetZeroLineNum NUMBER:=0;
1649 LastLineNum NUMBER:=0;
1650
1651 BEGIN
1652 IF g1_debug_mode = 'Y' THEN
1653 pa_retention_util.write_log('Entering Proj_Invoice_Retn_Processing ');
1654 END IF;
1655
1656 OPEN cur_proj_cust;
1657 LOOP
1658 FETCH cur_proj_cust INTO ProjCustRec;
1659 EXIT WHEN cur_proj_cust%NOTFOUND;
1660
1661 CustomerId := ProjCustRec.customer_id;
1662 TmpBillToAddressID := ProjCustRec.bill_to_address_id;
1663 TmpShipToAddressID := ProjCustRec.ship_to_address_id;
1664 TmpSetOfBooks := ProjCustRec.set_of_books_id;
1665 TmpBillToCustomerId :=ProjCustRec.Bill_to_customer_id;
1666 TmpShipToCustomerId :=ProjCustRec.Ship_to_customer_id;/*Added for customer account relation*/
1667 TmpSiteUSeId1 :=ProjCustRec.Bill_site_use_id;
1668 TmpSiteUSeId2 :=ProjCustRec.Ship_site_use_id;
1669 IF g1_debug_mode = 'Y' THEN
1670 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Customer ID : ' || CustomerId);
1671 END IF;
1672
1673 OPEN cur_proj_inv;
1674 LOOP
1675 FETCH cur_proj_inv INTO ProjInvRec;
1676 EXIT WHEN cur_proj_inv%NOTFOUND;
1677
1678 DraftInvoiceNum := ProjInvRec.draft_invoice_num;
1679
1680 IF g1_debug_mode = 'Y' THEN
1681 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Invoice Number : ' || DraftInvoiceNum);
1682 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Bill To Addres Id : ' || TmpBillToAddressID);
1683 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Ship To Addres Id : ' || TmpShipToAddressID);
1684 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Set Of Boooks Id : ' || TmpSetOfBooks);
1685 END IF;
1686
1687 --- Call Tax Information
1688 /*Last two parameters added for customer account relation enhancement*/
1689 /*The siteuseid is passed instead of addressid for bug 2938422*/
1690 PA_OUTPUT_TAX.GET_DEFAULT_TAX_INFO
1691 ( P_Project_Id=>p_project_id,
1692 P_Draft_Inv_Num=>DraftInvoiceNum,
1693 P_Customer_Id =>CustomerId,
1694 P_Bill_to_site_use_id=>TmpSiteUSeId1,
1695 P_Ship_to_site_use_id=>TmpSiteUSeId2,
1696 P_Sets_of_books_id =>TmpSetOfBooks,
1697 P_User_Id =>TmpUserId,
1698 P_Request_id =>P_request_id,
1699 -- X_Output_vat_tax_id=>Tmp_Output_vat_tax_id,
1700 X_output_Tax_code => Tmp_Output_tax_code,
1701 X_Output_tax_exempt_flag=>Tmp_Output_tax_exempt_flag ,
1702 X_Output_tax_exempt_number =>Tmp_Output_tax_exempt_number,
1703 X_Output_exempt_reason_code =>Tmp_Output_exempt_reason_code,
1704 Pbill_to_customer_id => TmpBillToCustomerId,
1705 Pship_to_customer_id => TmpShipToCustomerId);
1706
1707 IF g1_debug_mode = 'Y' THEN
1708 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tax Information: ');
1709 -- pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_vat_tax_id : ' || Tmp_Output_vat_tax_id);
1710 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_code : ' || Tmp_Output_tax_code);
1711 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_flag : ' || Tmp_Output_tax_exempt_flag);
1712 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_number : ' || Tmp_Output_tax_exempt_number);
1713 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_exempt_reason_code : ' || Tmp_Output_exempt_reason_code);
1714 END IF;
1715
1716
1717 IF ProjCustRec.retention_level ='PROJECT' THEN
1718
1719 IF g1_debug_mode = 'Y' THEN
1720 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'PROJECT Level ');
1721 END IF;
1722
1723 -- 1. Update the Non Labor Resource Override in RDLs
1724
1725 UPDATE pa_cust_rev_dist_lines_all rdl
1726 SET rdl.retention_rule_id=
1727 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
1728 rt.retention_rule_id,
1729 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
1730 -1,rt.retention_rule_id,-99))
1731 FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
1732 WHERE rt.customer_id = ProjCustRec.customer_id
1733 AND rt.project_id = p_project_id
1734 AND rt.retention_level_code='NON_LABOR'
1735 AND rdl.expenditure_item_id = ei.expenditure_item_id
1736 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1737 AND ei.non_labor_resource = rt.non_labor_resource
1738 AND trunc(ei.expenditure_item_date) BETWEEN
1739 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
1740 WHERE rdl.project_id = p_project_id
1741 AND rdl.request_id = p_request_id
1742 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1743 AND exists (select null FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
1744 WHERE rt.customer_id = ProjCustRec.customer_id
1745 AND rt.project_id = p_project_id
1746 AND rt.retention_level_code='NON_LABOR'
1747 AND rdl.expenditure_item_id = ei.expenditure_item_id
1748 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1749 AND ei.non_labor_resource = rt.non_labor_resource
1750 AND trunc(ei.expenditure_item_date) BETWEEN
1751 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1)) ;
1752
1753 IF g1_debug_mode = 'Y' THEN
1754 pa_retention_util.write_log('RDL: No Records Update At Non Labor Level ' ||sql%rowcount);
1755 END IF;
1756
1757
1758 -- 2. Update the Expenditure Type Level Override in RDLs
1759
1760 UPDATE pa_cust_rev_dist_lines_all rdl
1761 SET rdl.retention_rule_id=
1762 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
1763 rt.retention_rule_id,
1764 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
1765 -1,rt.retention_rule_id,-99))
1766 FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
1767 WHERE rt.customer_id = ProjCustRec.customer_id
1768 AND rt.project_id = p_project_id
1769 AND rt.expenditure_type = ei.expenditure_type
1770 AND rt.retention_level_code='EXPENDITURE_TYPE'
1771 AND rdl.expenditure_item_id = ei.expenditure_item_id
1772 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1773 AND trunc(ei.expenditure_item_date) BETWEEN
1774 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
1775 WHERE rdl.project_id = p_project_id
1776 AND rdl.request_id = p_request_id
1777 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1778 AND rdl.retention_rule_id IS NULL
1779 AND EXISTS( select null
1780 FROM pa_proj_retn_rules rt, pa_expenditure_items_all ei
1781 WHERE rt.customer_id = ProjCustRec.customer_id
1782 AND rt.project_id = p_project_id
1783 AND rt.expenditure_type = ei.expenditure_type
1784 AND rt.retention_level_code='EXPENDITURE_TYPE'
1785 AND rdl.expenditure_item_id = ei.expenditure_item_id
1786 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1787 AND trunc(ei.expenditure_item_date) BETWEEN
1788 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
1789 IF g1_debug_mode = 'Y' THEN
1790 pa_retention_util.write_log('RDL: No Records Update At Expenditure Type Level ' ||sql%rowcount);
1791 END IF;
1792
1793 -- 3. Update the Expenditure Category Level Override in RDLs
1794
1795 UPDATE pa_cust_rev_dist_lines_all rdl
1796 SET rdl.retention_rule_id=
1797 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
1798 rt.retention_rule_id,
1799 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
1800 -1,rt.retention_rule_id,-99) )
1801 FROM pa_proj_retn_rules rt,
1802 pa_expenditure_items_all ei,
1803 pa_expenditure_types et
1804 WHERE rt.customer_id = ProjCustRec.customer_id
1805 AND rt.project_id = p_project_id
1806 -- AND rt.expenditure_type = ei.expenditure_type
1807 AND ei.expenditure_type = et.expenditure_type
1808 AND et.expenditure_category = rt.expenditure_category
1809 AND rt.retention_level_code='EXPENDITURE_CATEGORY'
1810 AND rdl.expenditure_item_id = ei.expenditure_item_id
1811 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1812 AND trunc(ei.expenditure_item_date) BETWEEN
1813 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date +1))
1814 WHERE rdl.project_id = p_project_id
1815 AND rdl.request_id = p_request_id
1816 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1817 AND rdl.retention_rule_id IS NULL
1818 AND EXISTS (SELECT NULL
1819 FROM pa_proj_retn_rules rt,
1820 pa_expenditure_items_all ei,
1821 pa_expenditure_types et
1822 WHERE rt.customer_id = ProjCustRec.customer_id
1823 AND rt.project_id = p_project_id
1824 -- AND rt.expenditure_type = ei.expenditure_type
1825 AND ei.expenditure_type = et.expenditure_type
1826 AND et.expenditure_category = rt.expenditure_category
1827 AND rt.retention_level_code='EXPENDITURE_CATEGORY'
1828 AND rdl.expenditure_item_id = ei.expenditure_item_id
1829 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1830 AND trunc(ei.expenditure_item_date) BETWEEN
1831 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date +1));
1832 IF g1_debug_mode = 'Y' THEN
1833 pa_retention_util.write_log('RDL: No Records Update At Expenditure Category Level ' ||sql%rowcount);
1834 END IF;
1835
1836
1837 -- 4. Update the Project Level Retention Setup in RDLs
1838
1839 UPDATE pa_cust_rev_dist_lines_all rdl
1840 SET rdl.retention_rule_id=
1841 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
1842 rt.retention_rule_id,
1843 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
1844 -1,rt.retention_rule_id,-99))
1845 FROM pa_proj_retn_rules rt,
1846 pa_expenditure_items_all ei
1847 WHERE rt.customer_id = ProjCustRec.customer_id
1848 AND rt.project_id = p_project_id
1849 AND rt.retention_level_code='PROJECT'
1850 AND rdl.expenditure_item_id = ei.expenditure_item_id
1851 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1852 AND trunc(ei.expenditure_item_date) BETWEEN
1853 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date))
1854 WHERE rdl.project_id = p_project_id
1855 AND rdl.request_id = p_request_id
1856 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1857 AND rdl.retention_rule_id IS NULL
1858 AND EXISTS( SELECT NULL
1859 FROM pa_proj_retn_rules rt,
1860 pa_expenditure_items_all ei
1861 WHERE rt.customer_id = ProjCustRec.customer_id
1862 AND rt.project_id = p_project_id
1863 AND rt.retention_level_code='PROJECT'
1864 AND rdl.expenditure_item_id = ei.expenditure_item_id
1865 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1866 AND trunc(ei.expenditure_item_date) BETWEEN
1867 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date));
1868
1869 IF g1_debug_mode = 'Y' THEN
1870 pa_retention_util.write_log('RDL: No Records Update At Project Level ' ||sql%rowcount);
1871 END IF;
1872
1873
1874 -- 1. Update the Event Type Level Retention Setup in ERDLs
1875
1876 UPDATE pa_cust_event_rdl_all erdl
1877 SET erdl.retention_rule_id=
1878 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
1879 rt.retention_rule_id,
1880 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
1881 -1,rt.retention_rule_id,-99) )
1882 FROM pa_proj_retn_rules rt,
1883 pa_events evt,
1884 pa_event_types evttyp
1885 WHERE rt.customer_id = ProjCustRec.customer_id
1886 AND rt.project_id = p_project_id
1887 AND rt.retention_level_code='EVENT_TYPE'
1888 AND erdl.event_num = evt.event_num
1889 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
1890 AND erdl.project_id = evt.project_id
1891 AND trunc(evt.completion_date) BETWEEN
1892 rt.effective_start_date and NVL(rt.effective_end_date, evt.completion_date+1)
1893 AND evt.event_type = evttyp.event_type
1894 AND evt.event_type = rt.event_type
1895 and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1896 AND evttyp.revenue_category_code= rt.revenue_category_code)
1897 WHERE erdl.project_id = p_project_id
1898 AND erdl.request_id = p_request_id
1899 AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1900 AND erdl.retention_rule_id IS NULL
1901 AND EXISTS(SELECT NULL
1902 FROM pa_proj_retn_rules rt,
1903 pa_events evt,
1904 pa_event_types evttyp
1905 WHERE rt.customer_id = ProjCustRec.customer_id
1906 AND rt.project_id = p_project_id
1907 AND rt.retention_level_code='EVENT_TYPE'
1908 AND erdl.event_num = evt.event_num
1909 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
1910 AND erdl.project_id = evt.project_id
1911 AND trunc(evt.completion_date) BETWEEN
1912 rt.effective_start_date and NVL(rt.effective_end_date, evt.completion_date+1)
1913 AND evt.event_type = evttyp.event_type
1914 AND evt.event_type = rt.event_type
1915 and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1916 AND evttyp.revenue_category_code= rt.revenue_category_code);
1917
1918 IF g1_debug_mode = 'Y' THEN
1919 pa_retention_util.write_log('ERDL: No Records Update At Event Type Level ' ||sql%rowcount);
1920 END IF;
1921 -- 2. Update the Revenue Category Level Retention Setup in ERDLs
1922
1923 UPDATE pa_cust_event_rdl_all erdl
1924 SET erdl.retention_rule_id=
1925 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
1926 rt.retention_rule_id,
1927 DECODE(SIGN(NVL(rt.total_retained,0) -NVL(rt.threshold_amount,0)),
1928 -1,rt.retention_rule_id,-99))
1929 FROM pa_proj_retn_rules rt,
1930 pa_events evt,
1931 pa_event_types evttyp
1932 WHERE rt.customer_id = ProjCustRec.customer_id
1933 AND rt.project_id = p_project_id
1934 AND rt.retention_level_code='REVENUE_CATEGORY'
1935 AND erdl.event_num = evt.event_num
1936 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
1937 AND erdl.project_id = evt.project_id
1938 AND trunc(evt.completion_date) BETWEEN
1939 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
1940 AND evt.event_type = evttyp.event_type
1941 AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
1942 AND evttyp.revenue_category_code= rt.revenue_category_code)
1943 WHERE erdl.project_id = p_project_id
1944 AND erdl.request_id = p_request_id
1945 AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1946 AND erdl.retention_rule_id IS NULL
1947 AND EXISTS (SELECT NULL
1948 FROM pa_proj_retn_rules rt,
1949 pa_events evt,
1950 pa_event_types evttyp
1951 WHERE rt.customer_id = ProjCustRec.customer_id
1952 AND rt.project_id = p_project_id
1953 AND rt.retention_level_code='REVENUE_CATEGORY'
1954 AND erdl.event_num = evt.event_num
1955 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
1956 AND erdl.project_id = evt.project_id
1957 AND trunc(evt.completion_date) BETWEEN
1958 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
1959 AND evt.event_type = evttyp.event_type
1960 AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
1961 AND evttyp.revenue_category_code= rt.revenue_category_code);
1962
1963 IF g1_debug_mode = 'Y' THEN
1964 pa_retention_util.write_log('ERDL: No Records Update At Revenue Category Level ' ||sql%rowcount);
1965 END IF;
1966 -- 3. Update the Project Level Retention Setup in ERDLs
1967
1968 UPDATE pa_cust_event_rdl_all erdl
1969 SET erdl.retention_rule_id=
1970 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
1971 rt.retention_rule_id,
1972 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
1973 -1,rt.retention_rule_id,-99))
1974 FROM pa_proj_retn_rules rt,
1975 pa_events evt
1976 WHERE rt.customer_id = ProjCustRec.customer_id
1977 AND rt.project_id = p_project_id
1978 AND rt.retention_level_code='PROJECT'
1979 AND erdl.event_num = evt.event_num
1980 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
1981 AND erdl.project_id = evt.project_id
1982 AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
1983 AND TRUNC(evt.completion_date) BETWEEN
1984 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1))
1985 WHERE erdl.project_id = p_project_id
1986 AND erdl.request_id = p_request_id
1987 AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1988 AND erdl.retention_rule_id IS NULL
1989 AND EXISTS(SELECT NULL
1990 FROM pa_proj_retn_rules rt,
1991 pa_events evt
1992 WHERE rt.customer_id = ProjCustRec.customer_id
1993 AND rt.project_id = p_project_id
1994 AND rt.retention_level_code='PROJECT'
1995 AND erdl.event_num = evt.event_num
1996 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
1997 AND erdl.project_id = evt.project_id
1998 AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
1999 AND trunc(evt.completion_date) BETWEEN
2000 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1));
2001 IF g1_debug_mode = 'Y' THEN
2002 pa_retention_util.write_log('ERDL: No Records Update At Project Level ' ||sql%rowcount);
2003 END IF;
2004 -- 1. Update the Event Type Level Retention Setup in DII only for EVENTS
2005 /* Bug 3258414: The update statements below are done only for Events of type MANUAL or AUTOMATIC.
2006 The WRITE ON events are already updated using the ERDL table above. */
2007
2008 UPDATE pa_draft_invoice_items dii
2009 SET dii.retention_rule_id=
2010 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2011 rt.retention_rule_id,
2012 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2013 -1,rt.retention_rule_id,-99))
2014 FROM pa_proj_retn_rules rt,
2015 pa_events evt,
2016 pa_event_types evttyp
2017 WHERE rt.customer_id = ProjCustRec.customer_id
2018 AND rt.project_id = p_project_id
2019 AND rt.retention_level_code='EVENT_TYPE'
2020 AND dii.event_num = evt.event_num
2021 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2022 AND dii.project_id = evt.project_id
2023 AND trunc(evt.completion_date) BETWEEN
2024 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2025 AND evt.event_type = evttyp.event_type
2026 AND evt.event_type = rt.event_type
2027 AND evttyp.revenue_category_code= rt.revenue_category_code
2028 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2029 AND evttyp.event_type_classification <> 'WRITE ON'
2030 AND dii.event_num is NOT NULL
2031 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2032 WHERE dii.project_id = p_project_id
2033 AND dii.request_id = p_request_id
2034 AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
2035 AND dii.event_num IS NOT NULL
2036 AND dii.retention_rule_id IS NULL
2037 AND EXISTS( SELECT NULL
2038 FROM pa_proj_retn_rules rt,
2039 pa_events evt,
2040 pa_event_types evttyp
2041 WHERE rt.customer_id = ProjCustRec.customer_id
2042 AND rt.project_id = p_project_id
2043 AND rt.retention_level_code='EVENT_TYPE'
2044 AND dii.event_num = evt.event_num
2045 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2046 AND dii.project_id = evt.project_id
2047 AND trunc(evt.completion_date) BETWEEN
2048 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date +1)
2049 AND evt.event_type = evttyp.event_type
2050 AND evt.event_type = rt.event_type
2051 AND evttyp.revenue_category_code= rt.revenue_category_code
2052 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2053 AND evttyp.event_type_classification <> 'WRITE ON'
2054 AND dii.event_num is NOT NULL
2055 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2056
2057 IF g1_debug_mode = 'Y' THEN
2058 pa_retention_util.write_log('DII: No Records Update At Event Type Level ' ||sql%rowcount);
2059 END IF;
2060
2061 -- 2. Update the Revenue Category Level Retention Setup in DII for events
2062
2063 UPDATE pa_draft_invoice_items dii
2064 SET dii.retention_rule_id=
2065 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2066 rt.retention_rule_id,
2067 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2068 -1,rt.retention_rule_id,-99))
2069 FROM pa_proj_retn_rules rt,
2070 pa_events evt,
2071 pa_event_types evttyp
2072 WHERE rt.customer_id = ProjCustRec.customer_id
2073 AND rt.project_id = p_project_id
2074 AND rt.retention_level_code='REVENUE_CATEGORY'
2075 AND dii.event_num = evt.event_num
2076 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2077 AND dii.project_id = evt.project_id
2078 AND trunc(evt.completion_date) BETWEEN
2079 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2080 AND evt.event_type = evttyp.event_type
2081 AND evttyp.revenue_category_code= rt.revenue_category_code
2082 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2083 AND evttyp.event_type_classification <> 'WRITE ON'
2084 AND dii.event_num IS NOT NULL
2085 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2086 WHERE dii.project_id = p_project_id
2087 AND dii.request_id = p_request_id
2088 AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
2089 AND dii.event_num IS NOT NULL
2090 AND dii.retention_rule_id IS NULL
2091 AND exists (SELECT NULL
2092 FROM pa_proj_retn_rules rt,
2093 pa_events evt,
2094 pa_event_types evttyp
2095 WHERE rt.customer_id = ProjCustRec.customer_id
2096 AND rt.project_id = p_project_id
2097 AND rt.retention_level_code='REVENUE_CATEGORY'
2098 AND dii.event_num = evt.event_num
2099 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2100 AND dii.project_id = evt.project_id
2101 AND trunc(evt.completion_date) BETWEEN
2102 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2103 AND evt.event_type = evttyp.event_type
2104 AND evttyp.revenue_category_code= rt.revenue_category_code
2105 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2106 AND evttyp.event_type_classification <> 'WRITE ON'
2107 AND dii.event_num IS NOT NULL
2108 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2109
2110 IF g1_debug_mode = 'Y' THEN
2111 pa_retention_util.write_log('DII: No Records Update At Revenue Category Level ' ||sql%rowcount);
2112 END IF;
2113
2114 -- 3. Update the Project Level Retention Setup in DIIs for Events
2115 IF g1_debug_mode = 'Y' THEN
2116 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'PROJECT Defaults Level ');
2117 END IF;
2118
2119 UPDATE pa_draft_invoice_items dii
2120 SET dii.retention_rule_id=
2121 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2122 rt.retention_rule_id,
2123 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2124 -1,rt.retention_rule_id,-99))
2125 FROM pa_proj_retn_rules rt,
2126 pa_events evt ,
2127 pa_event_types evttyp /* Bug 3258414 */
2128 WHERE rt.customer_id = ProjCustRec.customer_id
2129 AND rt.project_id = p_project_id
2130 AND rt.retention_level_code='PROJECT'
2131 AND dii.event_num = evt.event_num
2132 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2133 AND dii.project_id = evt.project_id
2134 AND trunc(evt.completion_date) BETWEEN
2135 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2136 AND dii.event_num IS NOT NULL
2137 AND evt.event_type = evttyp.event_type /* Bug 3258414 */
2138 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2139 AND evttyp.event_type_classification <> 'WRITE ON'
2140 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2141 WHERE dii.project_id = p_project_id
2142 AND dii.request_id = p_request_id
2143 AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
2144 AND dii.event_num IS NOT NULL
2145 AND dii.retention_rule_id IS NULL
2146 AND EXISTS(SELECT NULL
2147 FROM pa_proj_retn_rules rt,
2148 pa_events evt,
2149 pa_event_types evttyp /* Bug 3258414 */
2150 WHERE rt.customer_id = ProjCustRec.customer_id
2151 AND rt.project_id = p_project_id
2152 AND rt.retention_level_code='PROJECT'
2153 AND dii.event_num = evt.event_num
2154 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2155 AND dii.project_id = evt.project_id
2156 AND trunc(evt.completion_date) BETWEEN
2157 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2158 AND dii.event_num IS NOT NULL
2159 AND evt.event_type = evttyp.event_type /* Bug 3258414 */
2160 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2161 AND evttyp.event_type_classification <> 'WRITE ON'
2162 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2163
2164 IF g1_debug_mode = 'Y' THEN
2165 pa_retention_util.write_log('DII: No Records Update At Project Level ' ||sql%rowcount);
2166 END IF;
2167
2168 ELSIF ProjCustRec.retention_level ='TOP_TASK' THEN
2169
2170 IF g1_debug_mode = 'Y' THEN
2171 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'TOP_TASK Level ');
2172 END IF;
2173
2174 -- 1. Update the Non Labor Resource Override in RDLs
2175
2176 UPDATE pa_cust_rev_dist_lines_all rdl
2177 SET rdl.retention_rule_id=
2178 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2179 rt.retention_rule_id,
2180 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2181 -1,rt.retention_rule_id,-99))
2182 FROM pa_proj_retn_rules rt,
2183 pa_expenditure_items_all ei,
2184 pa_tasks tsk
2185 WHERE tsk.task_id = ei.task_id
2186 AND tsk.top_task_id = rt.task_id
2187 AND rt.customer_id = ProjCustRec.customer_id
2188 AND rt.project_id = p_project_id
2189 AND rt.retention_level_code='NON_LABOR'
2190 AND rdl.expenditure_item_id = ei.expenditure_item_id
2191 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2192 AND ei.non_labor_resource = rt.non_labor_resource
2193 AND trunc(ei.expenditure_item_date) BETWEEN
2194 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
2195 WHERE rdl.project_id = p_project_id
2196 AND rdl.request_id = p_request_id
2197 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2198 AND EXISTS(
2199 SELECT NULL
2200 FROM pa_proj_retn_rules rt,
2201 pa_expenditure_items_all ei,
2202 pa_tasks tsk
2203 WHERE tsk.task_id = ei.task_id
2204 AND tsk.top_task_id = rt.task_id
2205 AND rt.customer_id = ProjCustRec.customer_id
2206 AND rt.project_id = p_project_id
2207 AND rt.retention_level_code='NON_LABOR'
2208 AND rdl.expenditure_item_id = ei.expenditure_item_id
2209 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2210 AND ei.non_labor_resource = rt.non_labor_resource
2211 AND trunc(ei.expenditure_item_date) BETWEEN
2212 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2213 IF g1_debug_mode = 'Y' THEN
2214 pa_retention_util.write_log('RDL: No Records Update At Non Labor Level ' ||sql%rowcount);
2215 END IF;
2216 -- 2. Update the Expenditure Type Level Override in RDLs
2217
2218 UPDATE pa_cust_rev_dist_lines_all rdl
2219 SET rdl.retention_rule_id=
2220 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2221 rt.retention_rule_id,
2222 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2223 -1,rt.retention_rule_id,-99))
2224 FROM pa_proj_retn_rules rt,
2225 pa_expenditure_items_all ei,
2226 pa_tasks tsk
2227 WHERE tsk.task_id = ei.task_id
2228 AND tsk.top_task_id = rt.task_id
2229 AND rt.customer_id = ProjCustRec.customer_id
2230 AND rt.project_id = p_project_id
2231 AND rt.expenditure_type = ei.expenditure_type
2232 AND rt.retention_level_code='EXPENDITURE_TYPE'
2233 AND rdl.expenditure_item_id = ei.expenditure_item_id
2234 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2235 AND trunc(ei.expenditure_item_date) BETWEEN
2236 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
2237 WHERE rdl.project_id = p_project_id
2238 AND rdl.request_id = p_request_id
2239 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2240 AND rdl.retention_rule_id IS NULL
2241 AND EXISTS(
2242 SELECT NULL
2243 FROM pa_proj_retn_rules rt,
2244 pa_expenditure_items_all ei,
2245 pa_tasks tsk
2246 WHERE tsk.task_id = ei.task_id
2247 AND tsk.top_task_id = rt.task_id
2248 AND rt.customer_id = ProjCustRec.customer_id
2249 AND rt.project_id = p_project_id
2250 AND rt.expenditure_type = ei.expenditure_type
2251 AND rt.retention_level_code='EXPENDITURE_TYPE'
2252 AND rdl.expenditure_item_id = ei.expenditure_item_id
2253 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2254 AND trunc(ei.expenditure_item_date) BETWEEN
2255 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2256 IF g1_debug_mode = 'Y' THEN
2257 pa_retention_util.write_log('RDL: No Records Update At Expenditure Type Level ' ||sql%rowcount);
2258 END IF;
2259
2260 -- 3. Update the Expenditure Category Level Override in RDLs
2261
2262 UPDATE pa_cust_rev_dist_lines_all rdl
2263 SET rdl.retention_rule_id=
2264 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2265 rt.retention_rule_id,
2266 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2267 -1,rt.retention_rule_id,-99))
2268 FROM pa_proj_retn_rules rt,
2269 pa_expenditure_items_all ei,
2270 pa_expenditure_types et,
2271 pa_tasks tsk
2272 WHERE tsk.task_id = ei.task_id
2273 AND tsk.top_task_id = rt.task_id
2274 AND rt.customer_id = ProjCustRec.customer_id
2275 AND rt.project_id = p_project_id
2276 -- AND rt.expenditure_type = ei.expenditure_type
2277 AND ei.expenditure_type = et.expenditure_type
2278 AND et.expenditure_category = rt.expenditure_category
2279 AND rt.retention_level_code='EXPENDITURE_CATEGORY'
2280 AND rdl.expenditure_item_id = ei.expenditure_item_id
2281 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2282 AND trunc(ei.expenditure_item_date) BETWEEN
2283 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
2284 WHERE rdl.project_id = p_project_id
2285 AND rdl.request_id = p_request_id
2286 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2287 AND rdl.retention_rule_id IS NULL
2288 AND EXISTS(
2289 SELECT NULL
2290 FROM pa_proj_retn_rules rt,
2291 pa_expenditure_items_all ei,
2292 pa_expenditure_types et,
2293 pa_tasks tsk
2294 WHERE tsk.task_id = ei.task_id
2295 AND tsk.top_task_id = rt.task_id
2296 AND rt.customer_id = ProjCustRec.customer_id
2297 AND rt.project_id = p_project_id
2298 -- AND rt.expenditure_type = ei.expenditure_type
2299 AND ei.expenditure_type = et.expenditure_type
2300 AND et.expenditure_category = rt.expenditure_category
2301 AND rt.retention_level_code='EXPENDITURE_CATEGORY'
2302 AND rdl.expenditure_item_id = ei.expenditure_item_id
2303 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2304 AND trunc(ei.expenditure_item_date) BETWEEN
2305 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2306 IF g1_debug_mode = 'Y' THEN
2307 pa_retention_util.write_log('RDL: No Records Update At Expenditure Category Level ' ||sql%rowcount);
2308 END IF;
2309 -- 4. Update the Project Level Retention Setup in RDLs
2310
2311 UPDATE pa_cust_rev_dist_lines_all rdl
2312 SET rdl.retention_rule_id=
2313 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2314 rt.retention_rule_id,
2315 DECODE(SIGN(NVL(rt.total_retained,0) -NVL(rt.threshold_amount,0)),
2316 -1,rt.retention_rule_id,-99))
2317 FROM pa_proj_retn_rules rt,
2318 pa_expenditure_items_all ei,
2319 pa_tasks tsk
2320 WHERE tsk.task_id = ei.task_id
2321 AND tsk.top_task_id = rt.task_id
2322 AND rt.customer_id = ProjCustRec.customer_id
2323 AND rt.project_id = p_project_id
2324 AND rt.retention_level_code='TOP_TASK'
2325 AND rdl.expenditure_item_id = ei.expenditure_item_id
2326 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2327 AND trunc(ei.expenditure_item_date) BETWEEN
2328 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1))
2329 WHERE rdl.project_id = p_project_id
2330 AND rdl.request_id = p_request_id
2331 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2332 AND rdl.retention_rule_id IS NULL
2333 AND EXISTS(SELECT NULL
2334 FROM pa_proj_retn_rules rt,
2335 pa_expenditure_items_all ei,
2336 pa_tasks tsk
2337 WHERE tsk.task_id = ei.task_id
2338 AND tsk.top_task_id = rt.task_id
2339 AND rt.customer_id = ProjCustRec.customer_id
2340 AND rt.project_id = p_project_id
2341 AND rt.retention_level_code='TOP_TASK'
2342 AND rdl.expenditure_item_id = ei.expenditure_item_id
2343 AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2344 AND trunc(ei.expenditure_item_date) BETWEEN
2345 rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2346
2347 IF g1_debug_mode = 'Y' THEN
2348 pa_retention_util.write_log('RDL: No Records Update At Project Level ' ||sql%rowcount);
2349 END IF;
2350
2351 -- 1. Update the Event Type Level Retention Setup in ERDLs
2352
2353 UPDATE pa_cust_event_rdl_all erdl
2354 SET erdl.retention_rule_id=
2355 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2356 rt.retention_rule_id,
2357 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2358 -1,rt.retention_rule_id,-99))
2359 FROM pa_proj_retn_rules rt,
2360 pa_events evt,
2361 pa_event_types evttyp,
2362 pa_tasks tsk
2363 WHERE tsk.task_id = evt.task_id
2364 AND tsk.top_task_id = rt.task_id
2365 AND rt.customer_id = ProjCustRec.customer_id
2366 AND rt.project_id = p_project_id
2367 AND rt.retention_level_code='EVENT_TYPE'
2368 AND erdl.event_num = evt.event_num
2369 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
2370 AND erdl.project_id = evt.project_id
2371 AND trunc(evt.completion_date) BETWEEN
2372 rt.effective_start_date and nvl(rt.effective_end_date,evt.completion_date+1)
2373 AND evt.event_type = evttyp.event_type
2374 AND evt.event_type = rt.event_type
2375 and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2376 AND evttyp.revenue_category_code= rt.revenue_category_code)
2377 WHERE erdl.project_id = p_project_id
2378 AND erdl.request_id = p_request_id
2379 AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2380 AND erdl.retention_rule_id IS NULL
2381 AND EXISTS
2382 (SELECT NULL
2383 FROM pa_proj_retn_rules rt,
2384 pa_events evt,
2385 pa_event_types evttyp,
2386 pa_tasks tsk
2387 WHERE tsk.task_id = evt.task_id
2388 AND tsk.top_task_id = rt.task_id
2389 AND rt.customer_id = ProjCustRec.customer_id
2390 AND rt.project_id = p_project_id
2391 AND rt.retention_level_code='EVENT_TYPE'
2392 AND erdl.event_num = evt.event_num
2393 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
2394 AND erdl.project_id = evt.project_id
2395 AND trunc(evt.completion_date) BETWEEN
2396 rt.effective_start_date and nvl(rt.effective_end_date,evt.completion_date+1)
2397 AND evt.event_type = evttyp.event_type
2398 AND evt.event_type = rt.event_type
2399 and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2400 AND evttyp.revenue_category_code= rt.revenue_category_code);
2401
2402 IF g1_debug_mode = 'Y' THEN
2403 pa_retention_util.write_log('ERDL: No Records Update At Event Type Level ' ||sql%rowcount);
2404 END IF;
2405
2406 -- 2. Update the Revenue Category Level Retention Setup in ERDLs
2407
2408 UPDATE pa_cust_event_rdl_all erdl
2409 SET erdl.retention_rule_id=
2410 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2411 rt.retention_rule_id,
2412 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2413 -1,rt.retention_rule_id,-99))
2414 FROM pa_proj_retn_rules rt,
2415 pa_events evt,
2416 pa_event_types evttyp,
2417 pa_tasks tsk
2418 WHERE tsk.task_id = evt.task_id
2419 AND tsk.top_task_id = rt.task_id
2420 AND rt.customer_id = ProjCustRec.customer_id
2421 AND rt.project_id = p_project_id
2422 AND rt.retention_level_code='REVENUE_CATEGORY'
2423 AND erdl.event_num = evt.event_num
2424 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
2425 AND erdl.project_id = evt.project_id
2426 AND trunc(evt.completion_date) BETWEEN
2427 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2428 AND evt.event_type = evttyp.event_type
2429 AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
2430 AND evttyp.revenue_category_code= rt.revenue_category_code)
2431 WHERE erdl.project_id = p_project_id
2432 AND erdl.request_id = p_request_id
2433 AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2434 AND erdl.retention_rule_id IS NULL
2435 AND EXISTS (SELECT NULL
2436 FROM pa_proj_retn_rules rt,
2437 pa_events evt,
2438 pa_event_types evttyp,
2439 pa_tasks tsk
2440 WHERE tsk.task_id = evt.task_id
2441 AND tsk.top_task_id = rt.task_id
2442 AND rt.customer_id = ProjCustRec.customer_id
2443 AND rt.project_id = p_project_id
2444 AND rt.retention_level_code='REVENUE_CATEGORY'
2445 AND erdl.event_num = evt.event_num
2446 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
2447 AND erdl.project_id = evt.project_id
2448 AND trunc(evt.completion_date) BETWEEN
2449 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2450 AND evt.event_type = evttyp.event_type
2451 AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
2452 AND evttyp.revenue_category_code= rt.revenue_category_code);
2453
2454
2455 IF g1_debug_mode = 'Y' THEN
2456 pa_retention_util.write_log('ERDL: No Records Update At Revenue Category Level ' ||sql%rowcount);
2457 END IF;
2458
2459 -- 3. Update the Top Task Level Retention Setup in ERDLs
2460
2461 UPDATE pa_cust_event_rdl_all erdl
2462 SET erdl.retention_rule_id=
2463 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2464 rt.retention_rule_id,
2465 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2466 -1,rt.retention_rule_id,-99))
2467 FROM pa_proj_retn_rules rt,
2468 pa_events evt,
2469 pa_tasks tsk
2470 WHERE tsk.task_id = evt.task_id
2471 AND tsk.top_task_id = rt.task_id
2472 AND rt.customer_id = ProjCustRec.customer_id
2473 AND rt.project_id = p_project_id
2474 AND rt.retention_level_code='TOP_TASK'
2475 AND erdl.event_num = evt.event_num
2476 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
2477 AND erdl.project_id = evt.project_id
2478 AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
2479 AND trunc(evt.completion_date) BETWEEN
2480 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1))
2481 WHERE erdl.project_id = p_project_id
2482 AND erdl.request_id = p_request_id
2483 AND erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2484 AND erdl.retention_rule_id IS NULL
2485 AND EXISTS( SELECT NULL
2486 FROM pa_proj_retn_rules rt,
2487 pa_events evt,
2488 pa_tasks tsk
2489 WHERE tsk.task_id = evt.task_id
2490 AND tsk.top_task_id = rt.task_id
2491 AND rt.customer_id = ProjCustRec.customer_id
2492 AND rt.project_id = p_project_id
2493 AND rt.retention_level_code='TOP_TASK'
2494 AND erdl.event_num = evt.event_num
2495 AND nvl(erdl.task_id,-99) = nvl(evt.task_id,-99)
2496 AND erdl.project_id = evt.project_id
2497 AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
2498 AND trunc(evt.completion_date) BETWEEN
2499 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1));
2500
2501 IF g1_debug_mode = 'Y' THEN
2502 pa_retention_util.write_log('ERDL: No Records Update At Project Level ' ||sql%rowcount);
2503 END IF;
2504
2505
2506 -- 1. Update the Event Type Level Retention Setup in DII only for EVENTS
2507
2508 UPDATE pa_draft_invoice_items dii
2509 SET dii.retention_rule_id=
2510 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2511 rt.retention_rule_id,
2512 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2513 -1,rt.retention_rule_id,-99))
2514 FROM pa_proj_retn_rules rt,
2515 pa_events evt,
2516 pa_event_types evttyp,
2517 pa_tasks tsk
2518 WHERE tsk.task_id = evt.task_id
2519 AND tsk.top_task_id = rt.task_id
2520 AND rt.customer_id = ProjCustRec.customer_id
2521 AND rt.project_id = p_project_id
2522 AND rt.retention_level_code='EVENT_TYPE'
2523 AND dii.event_num = evt.event_num
2524 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2525 AND dii.project_id = evt.project_id
2526 AND trunc(evt.completion_date) BETWEEN
2527 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2528 AND evt.event_type = evttyp.event_type
2529 AND evt.event_type = rt.event_type
2530 AND evttyp.revenue_category_code= rt.revenue_category_code
2531 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2532 AND evttyp.event_type_classification <> 'WRITE ON'
2533 AND dii.event_num is NOT NULL
2534 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2535 WHERE dii.project_id = p_project_id
2536 AND dii.request_id = p_request_id
2537 AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
2538 AND dii.event_num IS NOT NULL
2539 AND dii.retention_rule_id IS NULL
2540 AND EXISTS( SELECT NULL
2541 FROM pa_proj_retn_rules rt,
2542 pa_events evt,
2543 pa_event_types evttyp,
2544 pa_tasks tsk
2545 WHERE tsk.task_id = evt.task_id
2546 AND tsk.top_task_id = rt.task_id
2547 AND rt.customer_id = ProjCustRec.customer_id
2548 AND rt.project_id = p_project_id
2549 AND rt.retention_level_code='EVENT_TYPE'
2550 AND dii.event_num = evt.event_num
2551 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2552 AND dii.project_id = evt.project_id
2553 AND trunc(evt.completion_date) BETWEEN
2554 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2555 AND evt.event_type = evttyp.event_type
2556 AND evt.event_type = rt.event_type
2557 AND evttyp.revenue_category_code= rt.revenue_category_code
2558 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2559 AND evttyp.event_type_classification <> 'WRITE ON'
2560 AND dii.event_num is NOT NULL
2561 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2562 ;
2563 IF g1_debug_mode = 'Y' THEN
2564 pa_retention_util.write_log('DII: No Records Update At Event Type Level ' ||sql%rowcount);
2565 END IF;
2566
2567 -- 2. Update the Revenue Category Level Retention Setup in DII for events
2568
2569 UPDATE pa_draft_invoice_items dii
2570 SET dii.retention_rule_id=
2571 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2572 rt.retention_rule_id,
2573 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2574 -1,rt.retention_rule_id,-99))
2575 FROM pa_proj_retn_rules rt,
2576 pa_events evt,
2577 pa_event_types evttyp,
2578 pa_tasks tsk
2579 WHERE tsk.task_id = evt.task_id
2580 AND tsk.top_task_id = rt.task_id
2581 AND rt.customer_id = ProjCustRec.customer_id
2582 AND rt.project_id = p_project_id
2583 AND rt.retention_level_code='REVENUE_CATEGORY'
2584 AND dii.event_num = evt.event_num
2585 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2586 AND dii.project_id = evt.project_id
2587 AND trunc(evt.completion_date) BETWEEN
2588 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2589 AND evt.event_type = evttyp.event_type
2590 AND evttyp.revenue_category_code= rt.revenue_category_code
2591 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2592 AND evttyp.event_type_classification <> 'WRITE ON'
2593 AND dii.event_num IS NOT NULL
2594 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2595 WHERE dii.project_id = p_project_id
2596 AND dii.request_id = p_request_id
2597 AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
2598 AND dii.event_num IS NOT NULL
2599 AND dii.retention_rule_id IS NULL
2600 AND EXISTS( SELECT NULL
2601 FROM pa_proj_retn_rules rt,
2602 pa_events evt,
2603 pa_event_types evttyp,
2604 pa_tasks tsk
2605 WHERE tsk.task_id = evt.task_id
2606 AND tsk.top_task_id = rt.task_id
2607 AND rt.customer_id = ProjCustRec.customer_id
2608 AND rt.project_id = p_project_id
2609 AND rt.retention_level_code='REVENUE_CATEGORY'
2610 AND dii.event_num = evt.event_num
2611 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2612 AND dii.project_id = evt.project_id
2613 AND trunc(evt.completion_date) BETWEEN
2614 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1)
2615 AND evt.event_type = evttyp.event_type
2616 AND evttyp.revenue_category_code= rt.revenue_category_code
2617 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2618 AND evttyp.event_type_classification <> 'WRITE ON'
2619 AND dii.event_num IS NOT NULL
2620 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2621
2622 IF g1_debug_mode = 'Y' THEN
2623 pa_retention_util.write_log('DII: No Records Update At Revenue Category Level ' ||sql%rowcount);
2624 END IF;
2625
2626
2627 -- 3. Update the Top Task Level Retention Setup in DIIs for Events
2628
2629 UPDATE pa_draft_invoice_items dii
2630 SET dii.retention_rule_id=
2631 ( SELECT DECODE(NVL(rt.threshold_amount,0),0,
2632 rt.retention_rule_id,
2633 DECODE(SIGN(NVL(rt.total_retained,0) - NVL(rt.threshold_amount,0)),
2634 -1,rt.retention_rule_id,-99))
2635 FROM pa_proj_retn_rules rt,
2636 pa_events evt,
2637 pa_event_types evttyp, /* Bug 3258414 */
2638 pa_tasks tsk
2639 WHERE tsk.task_id = evt.task_id
2640 AND tsk.top_task_id = rt.task_id
2641 AND rt.customer_id = ProjCustRec.customer_id
2642 AND rt.project_id = p_project_id
2643 AND rt.retention_level_code='TOP_TASK'
2644 AND dii.event_num = evt.event_num
2645 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2646 AND dii.project_id = evt.project_id
2647 AND trunc(evt.completion_date) BETWEEN
2648 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date +1)
2649 AND dii.event_num IS NOT NULL
2650 AND evttyp.event_type=evt.event_type /* Bug 3258414 */
2651 /* AND evttyp.event_type_classification in ('MANUAL', 'AUTOMATIC') Bug 3258414 - Changed for bug3478802*/
2652 AND evttyp.event_type_classification <> 'WRITE ON'
2653 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2654 WHERE dii.project_id = p_project_id
2655 AND dii.request_id = p_request_id
2656 AND dii.draft_invoice_num = ProjInvRec.draft_invoice_num
2657 AND dii.event_num IS NOT NULL
2658 AND dii.retention_rule_id IS NULL
2659 AND EXISTS
2660 (SELECT NULL FROM pa_proj_retn_rules rt,
2661 pa_events evt,
2662 pa_event_types evttyp, /* Bug 3258414 */
2663 pa_tasks tsk
2664 WHERE tsk.task_id = evt.task_id
2665 AND tsk.top_task_id = rt.task_id
2666 AND rt.customer_id = ProjCustRec.customer_id
2667 AND rt.project_id = p_project_id
2668 AND rt.retention_level_code='TOP_TASK'
2669 AND dii.event_num = evt.event_num
2670 AND nvl(dii.event_task_id,-99) = nvl(evt.task_id,-99)
2671 AND dii.project_id = evt.project_id
2672 AND trunc(evt.completion_date) BETWEEN
2673 rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date +1)
2674 AND dii.event_num IS NOT NULL
2675 AND evttyp.event_type=evt.event_type /* Bug 3258414 */
2676 /* AND evttyp.event_type_classification in ('MANUAL','AUTOMATIC') Bug 3258414 - changed for bug 3478802*/
2677 AND evttyp.event_type_classification <> 'WRITE ON'
2678 AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2679
2680 IF g1_debug_mode = 'Y' THEN
2681 pa_retention_util.write_log('DII: No Records Update At Project Level ' ||sql%rowcount);
2682 END IF;
2683
2684 END IF;
2685
2686 IF g1_debug_mode = 'Y' THEN
2687 pa_retention_util.write_log('proj_invoice_retn_processing: ' || 'Calling Get_NetZero_Line');
2688 END IF;
2689
2690 /* Since net zero line comes up before retention line, net zero line num is cached here.
2691 After retention lines are generated , last line num is updated onto this cached line */
2692
2693 NetZeroLineNum:= Get_NetZero_Line(p_project_id => p_project_id,
2694 p_draft_invoice_num=>draftinvoicenum);
2695
2696 IF g1_debug_mode = 'Y' THEN
2697 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'NetZero_Line : ' || NetZeroLineNum);
2698 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Call Create_Proj_Inv_Retn_Lines ');
2699 END IF;
2700 Create_Proj_Inv_Retn_Lines(p_project_id=>p_project_id,
2701 p_customer_id=>CustomerId,
2702 p_agreement_id=>ProjInvRec.agreement_id,
2703 p_draft_invoice_num=>DraftInvoiceNum,
2704 p_cust_retn_level =>ProjCustRec.retention_level,
2705 p_request_id =>p_request_id,
2706 -- p_Output_vat_tax_id =>Tmp_Output_vat_tax_id ,
2707 p_Output_tax_code =>Tmp_Output_tax_code ,
2708 p_Output_tax_exempt_flag=>Tmp_Output_tax_exempt_flag,
2709 p_Output_tax_exempt_number=>Tmp_Output_tax_exempt_number ,
2710 p_Output_exempt_reason_code=>Tmp_Output_exempt_reason_code );
2711
2712 If NetZeroLineNum <> 0 THEN
2713
2714 LastLineNum := Get_Invoice_Max_Line(p_project_id=>p_project_id,
2715 p_draft_invoice_num=>DraftInvoiceNum);
2716
2717 /* Update net zero line to the last line after retention lines */
2718
2719 UPDATE PA_DRAFT_INVOICE_ITEMS
2720 SET LINE_NUM = LastLineNum
2721 WHERE PROJECT_ID = p_project_id
2722 AND DRAFT_INVOICE_NUM = draftinvoicenum
2723 AND LINE_NUM = NetZeroLineNum * (-1);
2724
2725 UPDATE PA_CUST_REV_DIST_LINES
2726 SET DRAFT_INVOICE_ITEM_LINE_NUM = LastLineNum
2727 WHERE PROJECT_ID = p_project_id
2728 AND DRAFT_INVOICE_NUM = draftinvoicenum
2729 AND DRAFT_INVOICE_ITEM_LINE_NUM = NetZeroLineNum;
2730
2731 UPDATE PA_CUST_EVENT_REV_DIST_LINES
2732 SET DRAFT_INVOICE_ITEM_LINE_NUM = LastLineNum
2733 WHERE PROJECT_ID = p_project_id
2734 AND DRAFT_INVOICE_NUM = draftinvoicenum
2735 AND DRAFT_INVOICE_ITEM_LINE_NUM = NetZeroLineNum;
2736 end If;
2737
2738
2739
2740 IF ProjInvRec.inv_by_bill_trans_curr_flag ='Y' THEN
2741
2742 IF g1_debug_mode = 'Y' THEN
2743 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Call Update_Retn_Bill_Trans_Amount ');
2744 END IF;
2745
2746 Update_Retn_Bill_Trans_Amount(p_project_id=>p_project_id,
2747 p_draft_invoice_num =>DraftInvoiceNum,
2748 p_bill_trans_currency =>ProjInvRec.invoice_currency_code,
2749 p_request_id =>p_request_id);
2750
2751 END IF;
2752
2753 END LOOP;
2754
2755 CLOSE cur_proj_inv;
2756
2757 END LOOP;
2758
2759 IF g1_debug_mode = 'Y' THEN
2760 pa_retention_util.write_log('Leaving Proj_Invoice_Retn_Processing ');
2761 END IF;
2762
2763 CLOSE cur_proj_cust;
2764 EXCEPTION
2765 WHEN OTHERS THEN
2766 IF g1_debug_mode = 'Y' THEN
2767 pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Oracle Error ' || sqlerrm);
2768 END IF;
2769 RAISE;
2770
2771 END Proj_Invoice_Retn_Processing;
2772 --- Procedure proj_invoice_credit_memo
2773 --- Purpose to build the credit memo retention lines
2774
2775 PROCEDURE Proj_Invoice_Credit_Memo(p_request_id IN NUMBER,
2776 p_project_id IN NUMBER,
2777 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2778 LastLineNum NUMBER:=0;
2779 LastUpdatedBy NUMBER:= fnd_global.user_id;
2780 l_created_by NUMBER:= fnd_global.user_id;
2781 l_program_id NUMBER:= fnd_global.conc_program_id;
2782 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
2783 l_program_update_date DATE := sysdate;
2784 l_last_update_date DATE := sysdate;
2785 l_last_updated_by NUMBER:= fnd_global.user_id;
2786 l_last_update_login NUMBER:= fnd_global.login_id;
2787
2788 ProjectCurrency VARCHAR2(15);
2789 l_credit_invoice_num NUMBER:=0;
2790 l_org_invoice_num NUMBER:=0;
2791
2792 /* Select all the creditmemo invoices */
2793
2794 CURSOR cur_cm_invoice IS
2795 SELECT draft_invoice_num credit_invoice_num,
2796 draft_invoice_num_credited org_invoice_num
2797 FROM pa_draft_invoices_all
2798 WHERE project_id = p_project_id
2799 AND request_id = p_request_id
2800 AND draft_invoice_num_credited IS NOT NULL
2801 ORDER BY draft_invoice_num;
2802
2803 --AND (CANCEL_CREDIT_MEMO_FLAG,'N') ='Y';
2804
2805 CmInvoiceRec cur_cm_invoice%ROWTYPE;
2806
2807 -- Select Credit RDLS. For old RDLS, process will use the old percentage
2808
2809 /* Commented and rewritten for bug 3958970
2810 CURSOR cur_credit_memo IS
2811 SELECT NVL(rdl.retention_rule_id,-1) retention_rule_id,
2812 SUM(DECODE(NVL(rdl.retention_rule_id,-1) ,-1,
2813 NVL(rdl.bill_amount,0) ,NVL(rdl.retained_amount,0) ) ) invoice_amount
2814 FROM pa_cust_rev_dist_lines_all rdl
2815 WHERE rdl.project_id = p_project_id
2816 -- AND rdl.request_id = p_request_id
2817 AND rdl.draft_invoice_num = l_credit_invoice_num
2818 GROUP BY NVL(rdl.retention_rule_id,-1); */
2819
2820 CURSOR cur_credit_memo IS
2821 SELECT retention_rule_id, sum(invoice_amount) invoice_amount
2822 FROM
2823 (SELECT NVL(rdl.retention_rule_id,-1) retention_rule_id,
2824 SUM(DECODE(NVL(rdl.retention_rule_id,-1) ,-1,
2825 NVL(rdl.bill_amount,0) ,NVL(rdl.retained_amount,0) ) ) invoice_amount
2826 FROM pa_cust_rev_dist_lines_all rdl
2827 WHERE rdl.project_id = p_project_id
2828 AND rdl.draft_invoice_num = l_credit_invoice_num
2829 GROUP BY NVL(rdl.retention_rule_id,-1)
2830 UNION
2831 SELECT NVL(di.retention_rule_id,-1) retention_rule_id,
2832 SUM(DECODE(NVL(di.retention_rule_id,-1) ,-1,
2833 NVL(di.amount,0) ,NVL(di.retained_amount,0) ) ) invoice_amount
2834 FROM pa_draft_invoice_items di
2835 WHERE di.project_id = p_project_id
2836 AND di.draft_invoice_num = l_credit_invoice_num
2837 AND di.event_num is not null
2838 GROUP BY NVL(di.retention_rule_id,-1)) CR_RET
2839 GROUP BY retention_rule_id;
2840
2841 CreditMemoRec cur_credit_memo%ROWTYPE;
2842 l_retention_percentage NUMBER:=0;
2843
2844 BEGIN
2845 IF g1_debug_mode = 'Y' THEN
2846 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || ' Processing Credit Memos ');
2847 END IF;
2848
2849 OPEN cur_cm_invoice;
2850 LOOP
2851 FETCH cur_cm_invoice INTO CmInvoiceRec;
2852 EXIT WHEN cur_cm_invoice%NOTFOUND;
2853
2854 l_credit_invoice_num := CmInvoiceRec.credit_invoice_num;
2855 l_org_invoice_num := CmInvoiceRec.org_invoice_num;
2856
2857 IF g1_debug_mode = 'Y' THEN
2858 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Invoice :' || l_credit_invoice_num);
2859 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Old Orginal Invoice :' || l_org_invoice_num);
2860 END IF;
2861
2862 -- This percentage will be used for old Rdls
2863
2864 SELECT NVL(retention_percentage,0)/100
2865 INTO l_retention_percentage
2866 FROM pa_draft_invoices
2867 WHERE project_id = p_project_id
2868 AND draft_invoice_num = l_org_invoice_num;
2869
2870 OPEN cur_credit_memo;
2871 LOOP
2872 FETCH cur_credit_memo INTO CreditMemoRec;
2873 EXIT WHEN cur_credit_memo%NOTFOUND;
2874
2875 LastLineNum := Get_Invoice_Max_Line(p_project_id=>p_project_id,
2876 p_draft_invoice_num=>l_credit_invoice_num);
2877
2878 IF g1_debug_mode = 'Y' THEN
2879 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line :' || LastLineNum);
2880 END IF;
2881
2882 IF CreditMemoRec.retention_rule_id = -1 THEN
2883
2884 IF g1_debug_mode = 'Y' THEN
2885 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || ' For Old RDLS use the old retn pct :' || l_retention_percentage);
2886 END IF;
2887
2888 CreditMemoRec.Invoice_amount := NVL( CreditMemoRec.Invoice_amount,0) * nvl(l_retention_percentage,0);
2889
2890 END IF;
2891
2892 FOR InvoiceLines IN
2893 (
2894 SELECT CreditMemoRec.invoice_amount invoice_amount,
2895 dii.text text,
2896 dii.invoice_line_type invoice_line_type,
2897 dii.task_id task_id,
2898 dii.event_task_id event_task_id,
2899 dii.event_num event_num,
2900 dii.ship_to_address_id ship_to_address_id,
2901 dii.taxable_flag taxable_flag,
2902 -- dii.output_vat_tax_id output_vat_tax_id,
2903 dii.output_tax_classification_code output_tax_code,
2904 dii.output_tax_exempt_flag output_tax_exempt_flag,
2905 dii.output_tax_exempt_reason_code output_tax_exempt_reason_code,
2906 dii.output_tax_exempt_number output_tax_exempt_number,
2907 dii.translated_text translated_text,
2908 dii.projfunc_currency_code projfunc_currency_code,
2909 ((dii.projfunc_bill_amount/dii.amount) *
2910 CreditMemoRec.invoice_amount) projfunc_bill_amount,
2911 dii.project_currency_code project_currency_code,
2912 ((dii.project_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) project_bill_amount,
2913 dii.funding_currency_code funding_currency_code,
2914 ((dii.funding_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) funding_bill_amount,
2915 dii.funding_rate_date funding_rate_date, dii.funding_exchange_rate funding_exchange_rate,
2916 dii.funding_rate_type funding_rate_type,dii.invproc_currency_code invproc_currency_code ,
2917 dii.bill_trans_currency_code bill_trans_currency_code,
2918 --dii.bill_trans_bill_amount bill_trans_bill_amount, --Modified for Bug3558364
2919 ((dii.bill_trans_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) bill_trans_bill_amount,
2920 dii.retention_rule_id retention_rule_id,
2921 di.agreement_id agreement_id,
2922 agr.customer_id customer_id,
2923 rtn.task_id rtn_task_id,
2924 dii.line_num line_num_credited
2925 FROM pa_draft_invoice_items dii, pa_draft_invoices_all di,
2926 pa_agreements_all agr,
2927 pa_proj_retn_rules rtn
2928 WHERE dii.project_id = p_project_id
2929 AND dii.draft_invoice_num = l_org_invoice_num
2930 AND dii.project_id = di.project_id
2931 AND dii.draft_invoice_num = di.draft_invoice_num
2932 AND di.agreement_id = agr.agreement_id
2933 and dii.retention_rule_id = rtn.retention_rule_id
2934 AND dii.retention_rule_id = CreditMemoRec.Retention_rule_id
2935 AND dii.invoice_line_type ='RETENTION'
2936 UNION
2937 SELECT CreditMemoRec.invoice_amount invoice_amount,
2938 dii.text text,
2939 dii.invoice_line_type invoice_line_type,
2940 dii.task_id task_id,
2941 dii.event_task_id event_task_id,
2942 dii.event_num event_num,
2943 dii.ship_to_address_id ship_to_address_id,
2944 dii.taxable_flag taxable_flag,
2945 -- dii.output_vat_tax_id output_vat_tax_id,
2946 dii.output_tax_classification_code output_tax_code,
2947 dii.output_tax_exempt_flag output_tax_exempt_flag,
2948 dii.output_tax_exempt_reason_code output_tax_exempt_reason_code,
2949 dii.output_tax_exempt_number output_tax_exempt_number,
2950 dii.translated_text translated_text,
2951 dii.projfunc_currency_code projfunc_currency_code,
2952 ((dii.projfunc_bill_amount/dii.amount) *
2953 CreditMemoRec.invoice_amount) projfunc_bill_amount,
2954 dii.project_currency_code project_currency_code,
2955 ((dii.project_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) project_bill_amount,
2956 dii.funding_currency_code funding_currency_code,
2957 ((dii.funding_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) funding_bill_amount,
2958 dii.funding_rate_date funding_rate_date, dii.funding_exchange_rate funding_exchange_rate,
2959 dii.funding_rate_type funding_rate_type,dii.invproc_currency_code invproc_currency_code ,
2960 dii.bill_trans_currency_code bill_trans_currency_code,
2961 --dii.bill_trans_bill_amount bill_trans_bill_amount, --Modified for Bug3558364
2962 ((dii.bill_trans_bill_amount/dii.amount) * CreditMemoRec.invoice_amount) bill_trans_bill_amount,
2963 dii.retention_rule_id retention_rule_id,
2964 di.agreement_id agreement_id,
2965 agr.customer_id customer_id,
2966 dii.retention_rule_id rtn_task_id, -- To get the retention lines
2967 dii.line_num line_num_credited
2968 FROM pa_draft_invoice_items dii, pa_draft_invoices_all di,
2969 pa_agreements_all agr
2970 WHERE dii.project_id = p_project_id
2971 AND dii.draft_invoice_num = l_org_invoice_num
2972 AND dii.project_id = di.project_id
2973 AND dii.draft_invoice_num = di.draft_invoice_num
2974 AND di.agreement_id = agr.agreement_id
2975 AND NVL(dii.retention_rule_id,-1) = -1
2976 AND CreditMemoRec.Retention_rule_id = -1
2977 AND dii.invoice_line_type ='RETENTION'
2978 ) LOOP
2979
2980 IF g1_debug_mode = 'Y' THEN
2981 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line RuleId :' || CreditMemoRec.Retention_rule_id);
2982 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Orgional Invoice :' || l_org_invoice_num);
2983 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || InvoiceLines.line_num_credited);
2984 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Invoice :' || l_credit_invoice_num);
2985 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || lastlinenum);
2986 END IF;
2987
2988 INSERT INTO pa_draft_invoice_items
2989 ( PROJECT_ID, DRAFT_INVOICE_NUM, LINE_NUM,
2990 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
2991 CREATED_BY, AMOUNT, TEXT,
2992 INVOICE_LINE_TYPE, REQUEST_ID, PROGRAM_APPLICATION_ID,
2993 PROGRAM_ID, PROGRAM_UPDATE_DATE, TASK_ID,
2994 EVENT_TASK_ID, EVENT_NUM, SHIP_TO_ADDRESS_ID,
2995 TAXABLE_FLAG, LAST_UPDATE_LOGIN,
2996 INV_AMOUNT, OUTPUT_TAX_CLASSIFICATION_CODE, OUTPUT_TAX_EXEMPT_FLAG,
2997 OUTPUT_TAX_EXEMPT_REASON_CODE, OUTPUT_TAX_EXEMPT_NUMBER,
2998 TRANSLATED_TEXT, PROJFUNC_CURRENCY_CODE, PROJFUNC_BILL_AMOUNT,
2999 PROJECT_CURRENCY_CODE, PROJECT_BILL_AMOUNT, FUNDING_CURRENCY_CODE,
3000 FUNDING_BILL_AMOUNT, FUNDING_RATE_DATE, FUNDING_EXCHANGE_RATE,
3001 FUNDING_RATE_TYPE , INVPROC_CURRENCY_CODE, BILL_TRANS_CURRENCY_CODE,
3002 BILL_TRANS_BILL_AMOUNT, RETENTION_RULE_ID,
3003 DRAFT_INV_LINE_NUM_CREDITED)
3004 VALUES(
3005 p_project_id, l_credit_invoice_num, lastlinenum,
3006 sysdate, LASTUPDATEDBY, SYSDATE,
3007 l_created_by,
3008 (-1) * InvoiceLines.invoice_amount,
3009 InvoiceLines.text,
3010 InvoiceLines.invoice_line_type, p_request_id, l_program_application_id,
3011 l_program_id, sysdate, InvoiceLines.task_id,
3012 InvoiceLines.event_task_id, InvoiceLines.event_num, InvoiceLines.ship_to_address_id,
3013 InvoiceLines.taxable_flag, l_last_update_login,
3014 null, InvoiceLines.output_tax_code, InvoiceLines.output_tax_exempt_flag,
3015 InvoiceLines.output_tax_exempt_reason_code, InvoiceLines.output_tax_exempt_number,
3016 InvoiceLines.translated_text, InvoiceLines.projfunc_currency_code,
3017 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT( (-1) * InvoiceLines.projfunc_bill_amount,
3018 invoicelines.projfunc_currency_code),
3019 invoicelines.project_currency_code,
3020 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((-1) * invoicelines.project_bill_amount,
3021 invoicelines.project_currency_code),
3022 InvoiceLines.funding_currency_code,
3023 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((-1) * InvoiceLines.funding_bill_amount,
3024 InvoiceLines.funding_currency_code),
3025 InvoiceLines.funding_rate_date, InvoiceLines.funding_exchange_rate,
3026 InvoiceLines.funding_rate_type ,InvoiceLines.invproc_currency_code,
3027 InvoiceLines.bill_trans_currency_code,
3028 DECODE(NVL(InvoiceLines.bill_trans_bill_amount,0),0,0,
3029 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((-1) * InvoiceLines.bill_trans_bill_amount,
3030 InvoiceLines.bill_trans_currency_code)),
3031 InvoiceLines.retention_rule_id,
3032 InvoiceLines.line_num_credited);
3033
3034 IF NVL(InvoiceLines.retention_rule_id,-1) <> -1 THEN
3035
3036 IF g1_debug_mode = 'Y' THEN
3037 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Calling Update_Retention_Balances ');
3038 END IF;
3039
3040 Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
3041 p_project_id =>p_project_id ,
3042 p_task_id =>InvoiceLines.rtn_Task_id,
3043 p_agreement_id =>InvoiceLines.agreement_id,
3044 p_customer_id =>InvoiceLines.customer_id,
3045 p_amount => InvoiceLines.invoice_amount,
3046 p_change_type => 'RETAINED' ,
3047 p_request_id =>p_request_id,
3048 p_invproc_currency =>InvoiceLines.invproc_currency_code ,
3049 p_project_currency =>InvoiceLines.project_currency_code,
3050 p_project_amount => InvoiceLines.project_bill_amount,
3051 p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
3052 p_projfunc_amount =>InvoiceLines.projfunc_bill_amount,
3053 p_funding_currency =>InvoiceLines.funding_currency_code ,
3054 p_funding_amount =>InvoiceLines.funding_bill_amount);
3055
3056
3057 IF g1_debug_mode = 'Y' THEN
3058 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Update RDL with new Retention Line Number');
3059 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Invoice Number : ' || l_credit_invoice_num);
3060 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Rule Id : ' ||
3061 InvoiceLines.Retention_rule_id);
3062 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Project Id : ' || p_project_id);
3063 END IF;
3064
3065 -- Update the RDL
3066
3067 UPDATE pa_cust_rev_dist_lines_all
3068 SET retn_draft_invoice_num = l_credit_invoice_num,
3069 retn_draft_invoice_line_num = LastLineNum
3070 WHERE retention_rule_id = InvoiceLines.Retention_rule_id
3071 AND draft_invoice_num = l_credit_invoice_num
3072 AND project_id = p_project_id;
3073
3074 IF g1_debug_mode = 'Y' THEN
3075 pa_retention_util.write_log('RDL Number of rows updated : ' || sql%rowcount);
3076 END IF;
3077
3078 -- Update the DII - Added for bug 3958970
3079
3080 UPDATE pa_draft_invoice_items
3081 SET retn_draft_invoice_num = l_credit_invoice_num,
3082 retn_draft_invoice_line_num = LastLineNum
3083 WHERE retention_rule_id = InvoiceLines.Retention_rule_id
3084 AND draft_invoice_num = l_credit_invoice_num
3085 AND project_id = p_project_id
3086 AND invoice_line_type <> 'RETENTION';
3087
3088 IF g1_debug_mode = 'Y' THEN
3089 pa_retention_util.write_log('DII Number of rows updated : ' || sql%rowcount);
3090 END IF;
3091 END IF;
3092
3093
3094 END LOOP;
3095
3096
3097 END LOOP;
3098
3099 CLOSE cur_credit_memo;
3100
3101 END LOOP;
3102
3103 CLOSE cur_cm_invoice;
3104 x_return_status :=FND_API.G_RET_STS_SUCCESS;
3105 EXCEPTION
3106 WHEN OTHERS THEN
3107 x_return_status :='E';
3108 IF g1_debug_mode = 'Y' THEN
3109 pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Oracle Error ' || sqlerrm);
3110 END IF;
3111 RAISE;
3112
3113 END Proj_Invoice_Credit_Memo;
3114 -- Procedure Retn_Invoice_Cancel
3115 -- Purpose Cancel the retention invoice
3116 PROCEDURE Invoice_Cancel_Action(p_request_id IN NUMBER,
3117 p_invoice_type IN VARCHAR2,
3118 p_project_id IN NUMBER,
3119 p_draft_invoice_num IN NUMBER,
3120 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
3121 TmpInvoiceNum NUMBER:=0;
3122 LastUpdatedBy NUMBER:= fnd_global.user_id;
3123 l_created_by NUMBER:= fnd_global.user_id;
3124 l_program_id NUMBER:= fnd_global.conc_program_id;
3125 l_program_application_id NUMBER:= fnd_global.prog_appl_id;
3126 l_program_update_date DATE := sysdate;
3127 l_last_update_date DATE := sysdate;
3128 l_last_updated_by NUMBER:= fnd_global.user_id;
3129 l_last_update_login NUMBER:= fnd_global.login_id;
3130 TmpInvoiceDate DATE:= TO_DATE(pa_billing.globvars.InvoiceDate,'YYYY/MM/DD');
3131 TmpBillThruDate DATE:= TO_DATE(pa_billing.globvars.BillThruDate,'YYYY/MM/DD');
3132 TmpPADate DATE:= TO_DATE(pa_billing.globvars.PADate,'YYYY/MM/DD');
3133 TmpGLDate DATE:= TO_DATE(pa_billing.globvars.GLDate,'YYYY/MM/DD');
3134
3135
3136 BEGIN
3137 IF p_invoice_type ='RETENTION' THEN
3138 -- Cancel the retention invoice
3139 -- Reverse the retention invoice
3140 -- Update the balances
3141 -- Setting the cancel invoice
3142
3143 UPDATE PA_DRAFT_INVOICES
3144 SET CANCELED_FLAG = 'Y',
3145 INVOICE_COMMENT =
3146 (select rtrim(upper(l.meaning)||' '||
3147 rtrim(SUBSTRB(i.invoice_comment,1,232)))
3148 from pa_lookups l,
3149 pa_draft_invoices i
3150 where i.project_Id = p_project_id
3151 and i.draft_invoice_num = p_draft_invoice_num
3152 and l.lookup_type = 'INVOICE_CREDIT_TYPE'
3153 and l.lookup_code = 'CANCEL')
3154 WHERE PROJECT_ID = p_project_id
3155 AND DRAFT_INVOICE_NUM = p_draft_invoice_num
3156 AND nvl(CANCELED_FLAG, 'N') <> 'Y';
3157
3158 -- Get the new draft invoice num
3159
3160 SELECT NVL( MAX(p.draft_invoice_num) + 1, 1)
3161 INTO TmpInvoiceNum
3162 FROM pa_draft_invoices_all p
3163 WHERE p.project_id = p_project_id;
3164
3165 IF g1_debug_mode = 'Y' THEN
3166 pa_retention_util.write_log('Invoice_Cancel_Action: ' || 'New Invoice Num : ' || TmpInvoiceNum);
3167 END IF;
3168
3169 -- Copy the Header as it is
3170 INSERT INTO pa_draft_invoices_all
3171 (
3172 PROJECT_ID ,
3173 DRAFT_INVOICE_NUM ,
3174 LAST_UPDATE_DATE ,
3175 LAST_UPDATED_BY ,
3176 CREATION_DATE ,
3177 CREATED_BY ,
3178 TRANSFER_STATUS_CODE ,
3179 GENERATION_ERROR_FLAG ,
3180 AGREEMENT_ID ,
3181 PA_DATE ,
3182 REQUEST_ID ,
3183 PROGRAM_APPLICATION_ID ,
3184 PROGRAM_ID ,
3185 PROGRAM_UPDATE_DATE ,
3186 CUSTOMER_BILL_SPLIT ,
3187 BILL_THROUGH_DATE ,
3188 INVOICE_COMMENT ,
3189 INVOICE_DATE ,
3190 GL_DATE ,
3191 CANCELED_FLAG ,
3192 LAST_UPDATE_LOGIN ,
3193 ATTRIBUTE_CATEGORY ,
3194 ATTRIBUTE1 ,
3195 ATTRIBUTE2 ,
3196 ATTRIBUTE3 ,
3197 ATTRIBUTE4 ,
3198 ATTRIBUTE5 ,
3199 ATTRIBUTE6 ,
3200 ATTRIBUTE7 ,
3201 ATTRIBUTE8 ,
3202 ATTRIBUTE9 ,
3203 ATTRIBUTE10 ,
3204 RETENTION_PERCENTAGE ,
3205 --INVOICE_SET_ID , /* Commented for Bug 2448872 */
3206 ORG_ID ,
3207 INV_CURRENCY_CODE ,
3208 INV_RATE_TYPE ,
3209 INV_RATE_DATE ,
3210 INV_EXCHANGE_RATE ,
3211 BILL_TO_ADDRESS_ID ,
3212 SHIP_TO_ADDRESS_ID ,
3213 ACCTD_CURR_CODE ,
3214 ACCTD_RATE_TYPE ,
3215 ACCTD_RATE_DATE ,
3216 ACCTD_EXCHG_RATE ,
3217 LANGUAGE ,
3218 INVPROC_CURRENCY_CODE ,
3219 PROJFUNC_INVTRANS_RATE_TYPE ,
3220 PROJFUNC_INVTRANS_RATE_DATE ,
3221 PROJFUNC_INVTRANS_EX_RATE ,
3222 PA_PERIOD_NAME ,
3223 GL_PERIOD_NAME ,
3224 RETENTION_INVOICE_FLAG ,
3225 DRAFT_INVOICE_NUM_CREDITED ,
3226 CANCEL_CREDIT_MEMO_FLAG , -- Added for Bug 2448872
3227 APPROVED_BY_PERSON_ID , -- Added for Bug 2448872
3228 APPROVED_DATE , -- Added for Bug 2448872
3229 CUSTOMER_ID ,
3230 BILL_TO_CUSTOMER_ID ,
3231 SHIP_TO_CUSTOMER_ID ,
3232 BILL_TO_CONTACT_ID ,
3233 SHIP_TO_CONTACT_ID )
3234 SELECT
3235 p_project_id ,
3236 TmpInvoiceNum,
3237 SYSDATE,
3238 LastUpdatedBy,
3239 sysdate,
3240 l_created_by,
3241 'P',
3242 'N',
3243 di.agreement_id,
3244 TmpPaDate,
3245 p_request_id,
3246 l_program_application_id,
3247 l_program_id,
3248 sysdate,
3249 di.customer_bill_split,
3250 di.bill_through_date,
3251 di.INVOICE_COMMENT,
3252 TmpInvoiceDate,
3253 TmpGlDate,
3254 'N' ,
3255 l_last_update_login,
3256 di.ATTRIBUTE_CATEGORY,
3257 di.ATTRIBUTE1,
3258 di.ATTRIBUTE2,
3259 di.ATTRIBUTE3,
3260 di.ATTRIBUTE4,
3261 di.ATTRIBUTE5,
3262 di.ATTRIBUTE6,
3263 di.ATTRIBUTE7,
3264 di.ATTRIBUTE8,
3265 di.ATTRIBUTE9,
3266 di.ATTRIBUTE10,
3267 di.RETENTION_PERCENTAGE,
3268 -- di.INVOICE_SET_ID, /* Commented for Bug 2448872 */
3269 di.ORG_ID,
3270 di.INV_CURRENCY_CODE,
3271 di.INV_RATE_TYPE,
3272 di.INV_RATE_DATE,
3273 di.INV_EXCHANGE_RATE,
3274 di.BILL_TO_ADDRESS_ID,
3275 di.SHIP_TO_ADDRESS_ID,
3276 di.ACCTD_CURR_CODE,
3277 di.ACCTD_RATE_TYPE,
3278 di.ACCTD_RATE_DATE,
3279 di.ACCTD_EXCHG_RATE,
3280 di.LANGUAGE,
3281 di.INVPROC_CURRENCY_CODE ,
3282 di.PROJFUNC_INVTRANS_RATE_TYPE ,
3283 di.PROJFUNC_INVTRANS_RATE_DATE ,
3284 di.PROJFUNC_INVTRANS_EX_RATE ,
3285 pa_billing.getpaperiodname,
3286 pa_billing.getglperiodname ,
3287 di.RETENTION_INVOICE_FLAG,
3288 p_draft_invoice_num,
3289 'Y', -- Added for Bug 2448872
3290 di.approved_by_person_id, -- Added for Bug 2448872
3291 di.approved_date, -- Added for Bug 2448872
3292 di.customer_id,
3293 di.bill_to_customer_id,
3294 di.ship_to_customer_id,
3295 di.bill_to_contact_id,
3296 di.ship_to_contact_id /*last 3 columns added for
3297 customer account relation enhancement*/
3298 FROM pa_draft_invoices_all di
3299 WHERE di.project_id = p_project_id
3300 AND di.draft_invoice_num = p_draft_invoice_num;
3301
3302 FOR InvoiceLines IN (SELECT dii.line_num line_Num ,
3303 dii. invproc_currency_code invproc_currency_code,
3304 dii.amount amount,
3305 dii.projfunc_currency_code projfunc_currency_code,
3306 dii.projfunc_bill_amount projfunc_bill_amount,
3307 dii.project_currency_code project_currency_code,
3308 dii.project_bill_amount project_bill_amount,
3309 dii.funding_currency_code funding_currency_code,
3310 dii.funding_bill_amount funding_bill_amount,
3311 dii.event_task_id event_task_id,
3312 dii.taxable_flag taxable_flag,
3313 --dii.output_vat_tax_id output_vat_tax_id,
3314 dii.output_tax_classification_code,
3315 dii.funding_rate_date funding_rate_date,
3316 dii.funding_rate_type funding_rate_type,
3317 dii.funding_exchange_rate funding_exchange_rate,
3318 dii.invoice_line_type invoice_line_type,
3319 dii.output_tax_exempt_flag output_tax_exempt_flag,
3320 dii.output_tax_exempt_reason_code output_tax_exempt_reason_code,
3321 dii.output_tax_exempt_number output_tax_exempt_number,
3322 dii.translated_text translated_text,
3323 dii.text text,
3324 dii.event_num event_num,
3325 dii.task_id task_id,
3326 dii.retention_rule_id retention_rule_id,
3327 dii.ship_to_address_id ship_to_address_id,
3328 dii.bill_trans_currency_code bill_trans_currency_code,
3329 dii.bill_trans_bill_amount bill_trans_bill_amount,
3330 di.agreement_id agreement_id,
3331 agr.customer_id customer_id,
3332 nvl(rtn.task_id, dii.task_id) rtn_task_id,
3333 dii.inv_amount inv_amount
3334 FROM pa_draft_invoice_items dii, pa_draft_invoices_all di,
3335 pa_agreements_all agr,
3336 pa_proj_retn_rules rtn
3337 WHERE di.project_id = p_project_id
3338 AND di.draft_invoice_num = p_draft_invoice_num
3339 AND di.agreement_id = agr.agreement_id
3340 AND di.project_id = dii.project_id
3341 AND di.draft_invoice_num = dii.draft_invoice_num
3342 AND dii.retention_rule_id = rtn.retention_rule_id(+)) LOOP
3343
3344 INSERT INTO pa_draft_invoice_items
3345 ( PROJECT_ID, DRAFT_INVOICE_NUM, LINE_NUM,
3346 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
3347 CREATED_BY, AMOUNT, TEXT,
3348 INVOICE_LINE_TYPE, REQUEST_ID, PROGRAM_APPLICATION_ID,
3349 PROGRAM_ID, PROGRAM_UPDATE_DATE, TASK_ID,
3350 EVENT_TASK_ID, EVENT_NUM, SHIP_TO_ADDRESS_ID,
3351 TAXABLE_FLAG, LAST_UPDATE_LOGIN,
3352 INV_AMOUNT, OUTPUT_TAX_CLASSIFICATION_CODE, OUTPUT_TAX_EXEMPT_FLAG,
3353 OUTPUT_TAX_EXEMPT_REASON_CODE, OUTPUT_TAX_EXEMPT_NUMBER,
3354 TRANSLATED_TEXT, PROJFUNC_CURRENCY_CODE, PROJFUNC_BILL_AMOUNT,
3355 PROJECT_CURRENCY_CODE, PROJECT_BILL_AMOUNT, FUNDING_CURRENCY_CODE,
3356 FUNDING_BILL_AMOUNT, FUNDING_RATE_DATE, FUNDING_EXCHANGE_RATE,
3357 FUNDING_RATE_TYPE , INVPROC_CURRENCY_CODE, BILL_TRANS_CURRENCY_CODE,
3358 BILL_TRANS_BILL_AMOUNT, RETENTION_RULE_ID,
3359 DRAFT_INV_LINE_NUM_CREDITED
3360 )
3361 VALUES(
3362 p_project_id, TmpInvoiceNum, InvoiceLines.line_num,
3363 sysdate, LastUpdatedBy, SYSDATE,
3364 l_created_by, (-1) * InvoiceLines.amount, InvoiceLines.text,
3365 InvoiceLines.invoice_line_type, p_request_id, l_program_application_id,
3366 l_program_id, sysdate, InvoiceLines.task_id,
3367 InvoiceLines.event_task_id, InvoiceLines.event_num,
3368 InvoiceLines.ship_to_address_id,
3369 InvoiceLines.taxable_flag, l_last_update_login,
3370 (-1) * InvoiceLines.inv_amount, InvoiceLines.output_tax_classification_code,
3371 InvoiceLines.output_tax_exempt_flag,
3372 InvoiceLines.output_tax_exempt_reason_code, InvoiceLines.output_tax_exempt_number,
3373 InvoiceLines.translated_text, InvoiceLines.projfunc_currency_code,
3374 (-1) * InvoiceLines.projfunc_bill_amount,
3375 InvoiceLines.project_currency_code, (-1) * InvoiceLines.project_bill_amount,
3376 InvoiceLines.funding_currency_code,
3377 (-1) * InvoiceLines.funding_bill_amount,
3378 InvoiceLines.funding_rate_date, InvoiceLines.funding_exchange_rate,
3379 InvoiceLines.funding_rate_type ,InvoiceLines.invproc_currency_code,
3380 InvoiceLines.bill_trans_currency_code,
3381 InvoiceLines.bill_trans_bill_amount, InvoiceLines.retention_rule_id,
3382 InvoiceLines.line_num
3383 );
3384
3385 pa_mc_currency_pkg.invoice_action := 'CANCEL';
3386
3387 Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
3388 p_project_id =>p_project_id ,
3389 p_task_id =>InvoiceLines.rtn_Task_id,
3390 p_agreement_id =>InvoiceLines.agreement_id,
3391 p_customer_id =>InvoiceLines.customer_id,
3392 p_amount => (-1) * InvoiceLines.amount,
3393 p_change_type => 'BILLED' ,
3394 p_request_id =>p_request_id,
3395 p_invproc_currency =>InvoiceLines.invproc_currency_code ,
3396 p_project_currency =>InvoiceLines.project_currency_code,
3397 p_project_amount => (-1) * InvoiceLines.project_bill_amount,
3398 p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
3399 p_projfunc_amount => (-1) * InvoiceLines.projfunc_bill_amount,
3400 p_funding_currency => InvoiceLines.funding_currency_code ,
3401 p_funding_amount => (-1) * InvoiceLines.funding_bill_amount);
3402
3403 END LOOP;
3404
3405 -- Reverse Retention Invoice Details
3406
3407 INSERT INTO pa_retn_invoice_details
3408 ( RETN_INVOICE_DETAIL_ID, PROJECT_ID, DRAFT_INVOICE_NUM,
3409 LINE_NUM , PROJECT_RETENTION_ID, TOTAL_RETAINED,
3410 INVPROC_CURRENCY_CODE, PROJFUNC_CURRENCY_CODE,
3411 PROJFUNC_TOTAL_RETAINED, PROJECT_CURRENCY_CODE,
3412 PROJECT_TOTAL_RETAINED , FUNDING_CURRENCY_CODE,
3413 FUNDING_TOTAL_RETAINED, PROGRAM_APPLICATION_ID ,
3414 PROGRAM_UPDATE_DATE, REQUEST_ID,
3415 CREATION_DATE, CREATED_BY,
3416 LAST_UPDATE_DATE,
3417 LAST_UPDATED_BY,
3418 PROGRAM_ID )
3419 SELECT
3420 pa_retn_invoice_details_s.nextval,
3421 p_project_id,
3422 TmpInvoiceNum,
3423 rtndet.line_num,
3424 rtndet.project_retention_id,
3425 rtndet.total_retained,
3426 rtndet.invproc_currency_code,
3427 rtndet.projfunc_currency_code,
3428 rtndet.projfunc_total_retained,
3429 rtndet.project_currency_code,
3430 rtndet.project_total_retained,
3431 rtndet.funding_currency_code,
3432 rtndet.funding_total_retained,
3433 l_program_application_id,
3434 sysdate, p_request_id,
3435 sysdate, l_created_by,
3436 sysdate, LastUpdatedBy,
3437 l_program_id
3438 FROM pa_retn_invoice_details rtndet
3439 WHERE rtndet.project_id = p_project_id
3440 AND rtndet.draft_invoice_num = p_draft_invoice_num;
3441
3442 ELSIF p_invoice_type ='PROJECT_INVOICE' THEN
3443
3444 FOR InvoiceLines IN (SELECT
3445 dii. invproc_currency_code invproc_currency_code,
3446 dii.amount amount, dii.projfunc_currency_code projfunc_currency_code,
3447 dii.projfunc_bill_amount projfunc_bill_amount,
3448 dii.project_currency_code project_currency_code,
3449 dii.project_bill_amount project_bill_amount,
3450 dii.funding_currency_code funding_currency_code,
3451 dii.funding_bill_amount funding_bill_amount,
3452 dii.task_id task_id,
3453 dii.retention_rule_id retention_rule_id,
3454 di.agreement_id agreement_id,
3455 agr.customer_id customer_id,
3456 rtn.task_id rtn_task_id
3457 FROM pa_draft_invoice_items dii,
3458 pa_draft_invoices_all di,
3459 pa_agreements_all agr,
3460 pa_proj_retn_rules rtn
3461 WHERE di.project_id = p_project_id
3462 AND di.draft_invoice_num = p_draft_invoice_num
3463 AND di.agreement_id = agr.agreement_id
3464 AND di.project_id = dii.project_id
3465 AND di.draft_invoice_num = dii.draft_invoice_num
3466 AND dii.invoice_line_type = 'RETENTION'
3467 -- # Fix for 2366314
3468 AND dii.retention_rule_id = rtn.retention_rule_id
3469 AND dii.retention_rule_id is not null) LOOP
3470
3471 Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
3472 p_project_id =>p_project_id ,
3473 p_task_id =>InvoiceLines.rtn_Task_id,
3474 p_agreement_id =>InvoiceLines.agreement_id,
3475 p_customer_id =>InvoiceLines.customer_id,
3476 p_amount => InvoiceLines.amount,
3477 -- # Fix for 2366314 (-1) * InvoiceLines.amount,
3478 p_change_type => 'RETAINED' ,
3479 p_request_id =>p_request_id,
3480 p_invproc_currency =>InvoiceLines.invproc_currency_code ,
3481 p_project_currency =>InvoiceLines.project_currency_code,
3482 p_project_amount => InvoiceLines.project_bill_amount,
3483 -- # Fix for 2366314 (-1) * InvoiceLines.project_bill_amount,
3484 p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
3485 p_projfunc_amount => InvoiceLines.projfunc_bill_amount,
3486 -- # Fix for 2366314 (-1) * InvoiceLines.projfunc_bill_amount,
3487 p_funding_currency => InvoiceLines.funding_currency_code ,
3488 p_funding_amount => InvoiceLines.funding_bill_amount);
3489 -- # Fix for 2366314 (-1) * InvoiceLines.funding_bill_amount);
3490
3491 END LOOP;
3492 END IF;
3493
3494 END Invoice_Cancel_Action;
3495 -- Procedure Invoice_Delete_Action
3496 PROCEDURE Invoice_Delete_Action(p_request_id IN NUMBER,
3497 p_invoice_type IN VARCHAR2,
3498 p_project_id IN NUMBER,
3499 p_draft_invoice_num IN NUMBER,
3500 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
3501 TmpChangeType VARCHAR2(15);
3502
3503 BEGIN
3504 IF g1_debug_mode = 'Y' THEN
3505 pa_retention_util.write_log('Invoice_Delete_Action ');
3506 END IF;
3507
3508 IF p_invoice_type ='PROJECT_INVOICE' THEN
3509
3510 tmpChangeType := 'RETAINED';
3511
3512 ELSIF p_invoice_type ='RETENTION' THEN
3513
3514 tmpChangeType := 'BILLED';
3515
3516 END IF;
3517
3518 IF g1_debug_mode = 'Y' THEN
3519 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Change Type : ' || tmpChangeType);
3520 END IF;
3521
3522 FOR InvoiceLines IN (SELECT
3523 decode(tmpChangeType,'BILLED', -1 * dii.amount ,
3524 dii.amount ) amount,
3525 dii.task_id task_id,
3526 dii.invproc_currency_code invproc_currency_code,
3527 decode(tmpChangeType,'BILLED', -1 * dii.projfunc_bill_amount,
3528 dii.projfunc_bill_amount) projfunc_bill_amount,
3529 dii.projfunc_currency_code projfunc_currency_code,
3530 decode(tmpChangeType,'BILLED', -1 * dii.project_bill_amount,
3531 dii.project_bill_amount) project_bill_amount,
3532 dii.project_currency_code project_currency_code,
3533 dii.funding_currency_code funding_currency_code,
3534 decode( tmpChangeType,'BILLED', -1 * dii.funding_bill_amount,
3535 dii.funding_bill_amount) funding_bill_amount,
3536 dii.retention_rule_id retention_rule_id,
3537 di.agreement_id agreement_id,
3538 agr.customer_id customer_id,
3539 NVL(rtn.task_id,dii.task_id) rtn_task_id
3540 FROM pa_draft_invoice_items dii,
3541 pa_draft_invoices_all di,
3542 pa_agreements_all agr,
3543 pa_proj_retn_rules rtn
3544 WHERE di.project_id = p_project_id
3545 AND di.draft_invoice_num = p_draft_invoice_num
3546 AND di.agreement_id = agr.agreement_id
3547 AND di.project_id = dii.project_id
3548 AND di.draft_invoice_num = dii.draft_invoice_num
3549 AND dii.retention_rule_id = rtn.retention_rule_id (+)
3550 AND dii.invoice_line_type='RETENTION') LOOP
3551
3552 IF g1_debug_mode = 'Y' THEN
3553 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Number : ' || p_draft_invoice_num);
3554 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
3555 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3558 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
3559 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'FC Invoice Amount : ' || InvoiceLines.funding_bill_amount);
3560 END IF;
3561
3562
3563 Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id,
3564 p_project_id =>p_project_id,
3565 p_task_id =>InvoiceLines.rtn_Task_id,
3566 p_agreement_id =>InvoiceLines.agreement_id,
3567 p_customer_id =>InvoiceLines.customer_id,
3568 p_amount =>InvoiceLines.amount,
3569 p_change_type => TmpChangeType ,
3570 p_request_id =>p_request_id,
3571 p_invproc_currency =>InvoiceLines.invproc_currency_code,
3572 p_project_currency =>InvoiceLines.project_currency_code,
3573 p_project_amount => InvoiceLines.project_bill_amount,
3574 p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
3575 p_projfunc_amount => InvoiceLines.projfunc_bill_amount,
3576 p_funding_currency => InvoiceLines.funding_currency_code ,
3577 p_funding_amount => InvoiceLines.funding_bill_amount);
3578
3579 END LOOP;
3580
3581 IF TmpChangeType ='BILLED' THEN
3582
3583 -- Delete the retention invoice details
3584
3585 IF g1_debug_mode = 'Y' THEN
3586 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Delete Retention Invoice Details ');
3587 END IF;
3588
3589 DELETE FROM pa_retn_invoice_details
3590 WHERE project_id = p_project_id
3591 AND draft_invoice_num = p_draft_invoice_num;
3592
3593 -- Call to delete the mc records
3594 IF g1_debug_mode = 'Y' THEN
3595 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Delete MRC Retention Invoice Details ');
3596 END IF;
3597 PA_MC_RETN_INV_DETAIL_PKG.Process_RetnInvDetails(
3598 p_project_id=>p_project_id,
3599 p_draft_invoice_num=>p_draft_invoice_num,
3600 p_action=>'DELETE',
3601 p_request_id=>p_request_id);
3602
3603
3604 END IF;
3605
3606 x_return_status :=FND_API.G_RET_STS_SUCCESS;
3607
3608 EXCEPTION
3609 WHEN OTHERS THEN
3610 x_return_status := 'E';
3611 IF g1_debug_mode = 'Y' THEN
3612 pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Oracle Error ' || sqlerrm);
3613 END IF;
3614 RAISE;
3615
3616 END Invoice_Delete_Action;
3617
3618 /* Added for bug 2770738 */
3619 /* Renamed the procedure from invoice_write_off to update_credit_retn_balances for Bug3525910 */
3620 PROCEDURE update_credit_retn_balances(p_request_id IN NUMBER,
3621 p_invoice_type IN VARCHAR2,
3622 p_credit_action IN VARCHAR2, --Added this parameter for Bug3525910
3623 p_project_id IN NUMBER,
3624 p_draft_invoice_num IN NUMBER,
3625 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
3626
3627 BEGIN
3628
3629 IF p_credit_action = 'WRITE_OFF' OR p_credit_action = 'CONCESSION' THEN --Added this IF condition for Bug3525910,
3630 --Added concession check for 4290823
3631
3632 IF p_invoice_type ='PROJECT_INVOICE' THEN
3633
3634 FOR InvoiceLines IN (SELECT
3635 dii. invproc_currency_code invproc_currency_code,
3636 dii.amount amount, dii.projfunc_currency_code projfunc_currency_code,
3637 dii.projfunc_bill_amount projfunc_bill_amount,
3638 dii.project_currency_code project_currency_code,
3639 dii.project_bill_amount project_bill_amount,
3640 dii.funding_currency_code funding_currency_code,
3641 dii.funding_bill_amount funding_bill_amount,
3642 dii.task_id task_id,
3643 dii.retention_rule_id retention_rule_id,
3644 di.agreement_id agreement_id,
3645 agr.customer_id customer_id,
3646 rtn.task_id rtn_task_id
3647 FROM pa_draft_invoice_items dii,
3648 pa_draft_invoices_all di,
3649 pa_agreements_all agr,
3650 pa_proj_retn_rules rtn
3651 WHERE di.project_id = p_project_id
3652 AND di.draft_invoice_num = p_draft_invoice_num
3653 AND di.agreement_id = agr.agreement_id
3654 AND di.project_id = dii.project_id
3655 AND di.draft_invoice_num = dii.draft_invoice_num
3656 AND dii.invoice_line_type = 'RETENTION'
3657 -- # Fix for 2366314
3658 AND dii.retention_rule_id = rtn.retention_rule_id
3659 AND dii.retention_rule_id is not null) LOOP
3660
3661 Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
3662 p_project_id =>p_project_id ,
3663 p_task_id =>InvoiceLines.rtn_Task_id,
3664 p_agreement_id =>InvoiceLines.agreement_id,
3665 p_customer_id =>InvoiceLines.customer_id,
3666 p_amount => (-1) * InvoiceLines.amount,
3667 p_change_type => 'RETAINED' ,
3668 p_request_id =>p_request_id,
3669 p_invproc_currency =>InvoiceLines.invproc_currency_code ,
3670 p_project_currency =>InvoiceLines.project_currency_code,
3671 p_project_amount => (-1) * InvoiceLines.project_bill_amount,
3672 p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
3673 p_projfunc_amount => (-1) * InvoiceLines.projfunc_bill_amount,
3674 p_funding_currency => InvoiceLines.funding_currency_code ,
3675 p_funding_amount => (-1) * InvoiceLines.funding_bill_amount);
3676
3677 END LOOP;
3678 END IF;
3679
3680 ELSIF p_credit_action = 'CONCESSION' THEN --Added this condition for Bug3525910
3681
3682 IF p_invoice_type ='RETENTION' THEN
3683
3684 FOR InvoiceLines IN (SELECT
3685 dii. invproc_currency_code invproc_currency_code,
3686 dii.amount amount, dii.projfunc_currency_code projfunc_currency_code,
3687 dii.projfunc_bill_amount projfunc_bill_amount,
3688 dii.project_currency_code project_currency_code,
3689 dii.project_bill_amount project_bill_amount,
3690 dii.funding_currency_code funding_currency_code,
3691 dii.funding_bill_amount funding_bill_amount,
3692 dii.task_id task_id,
3693 dii.retention_rule_id retention_rule_id,
3694 di.agreement_id agreement_id,
3695 agr.customer_id customer_id,
3696 dii.task_id dii_task_id
3697 FROM pa_draft_invoice_items dii,
3698 pa_draft_invoices_all di,
3699 pa_agreements_all agr
3700 WHERE di.project_id = p_project_id
3701 AND di.draft_invoice_num = p_draft_invoice_num
3702 AND di.agreement_id = agr.agreement_id
3703 AND di.project_id = dii.project_id
3704 AND di.draft_invoice_num = dii.draft_invoice_num
3705 AND dii.invoice_line_type = 'RETENTION') LOOP
3706
3707 Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
3708 p_project_id =>p_project_id ,
3709 p_task_id =>InvoiceLines.dii_Task_id,
3710 p_agreement_id =>InvoiceLines.agreement_id,
3711 p_customer_id =>InvoiceLines.customer_id,
3712 p_amount =>InvoiceLines.amount,
3713 p_change_type => 'BILLED' ,
3714 p_request_id =>p_request_id,
3715 p_invproc_currency =>InvoiceLines.invproc_currency_code ,
3716 p_project_currency =>InvoiceLines.project_currency_code,
3717 p_project_amount => InvoiceLines.project_bill_amount,
3718 p_projfunc_currency =>InvoiceLines.projfunc_currency_code,
3719 p_projfunc_amount =>InvoiceLines.projfunc_bill_amount,
3720 p_funding_currency =>InvoiceLines.funding_currency_code ,
3721 p_funding_amount =>InvoiceLines.funding_bill_amount);
3722
3723 END LOOP;
3724 END IF;
3725
3726 END IF;
3727
3728 END update_credit_retn_balances;
3729
3730 -- Procedure added for bug 3889175
3731 PROCEDURE Delete_Unused_Retention_Lines(
3732 P_Project_ID IN NUMBER,
3733 P_Task_ID IN NUMBER,
3734 X_Return_Status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3735 IS
3736 l_Cust_Top_Task_Flag VARCHAR2(1);
3737 l_Inv_Method_Top_Task_Flag VARCHAR2(1);
3738 BEGIN
3739 Select ENABLE_TOP_TASK_CUSTOMER_FLAG, ENABLE_TOP_TASK_INV_MTH_FLAG
3740 Into l_Cust_Top_Task_Flag, l_Inv_Method_Top_Task_Flag
3741 From PA_Projects_All
3742 Where Project_ID = P_Project_ID;
3743
3744 -- Dbms_Output.Put_Line('Flag value 1 : '|| l_Cust_Top_Task_Flag);
3745 -- Dbms_Output.Put_Line('Flag value 2 : '|| l_Inv_Method_Top_Task_Flag);
3746
3747 If l_Cust_Top_Task_Flag = 'Y' OR l_Inv_Method_Top_Task_Flag = 'Y' Then
3748 Delete From PA_Proj_Retn_Rules -- PA_Project_Retentions
3749 Where Project_ID = P_Project_ID
3750 And Task_ID = P_Task_ID;
3751 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
3752 End If;
3753
3754 EXCEPTION
3755 WHEN OTHERS THEN
3756 x_return_status := 'E';
3757 IF g1_debug_mode = 'Y' THEN
3758 pa_retention_util.write_log('Retention_Delete_Action: ' || 'Oracle Error ' || sqlerrm);
3759 END IF;
3760 RAISE;
3761
3762 END Delete_Unused_Retention_Lines;
3763
3764 END pa_retention_pkg;