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