[Home] [Help]
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
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:
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;
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
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 */
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
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;
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;
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:
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:
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:
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.
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;
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:
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
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,
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;
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:
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
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(
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
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;
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:
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
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: (
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;
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:
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
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(
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);
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:
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:
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
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);
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);
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);
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);
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);
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;
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:
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
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) +
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:
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
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
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
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:
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
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;
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;
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);
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;
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:
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
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
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;
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;
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);
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);
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);
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;
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:
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;
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:
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
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:
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,
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);
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:
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:
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);
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;
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:
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
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:
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;
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:
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
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:
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:
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:
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:
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:
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:
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:
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;
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) )
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);
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:
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;
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:
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:
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);
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
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
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:
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
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
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);
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*/
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
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:
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
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:
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
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
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
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 );
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:
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);
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
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
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);
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:
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
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
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
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:
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:
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,
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);
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:
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
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:
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:
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,
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,
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,
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,
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,
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:
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
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;
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
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
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;
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:
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
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*/
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);
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);
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;
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:
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:
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
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:
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
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:
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
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
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
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
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. */
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:
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
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=
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:
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:
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
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:
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
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:
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:
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:
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
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:
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
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:
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 */
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,
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,
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 */
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:
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,
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
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;
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
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
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:
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:
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:
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);
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;
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:
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
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,
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 ,
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);
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;
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:
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:
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:
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:
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;
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
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:
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 ,
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);
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);
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);
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;
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:
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:
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,
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,
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;
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;