[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;
37: NetZero_line_num NUMBER;
38: BEGIN
39:
40: IF g1_debug_mode = 'Y' THEN
41: pa_retention_util.write_log('Entering pa_retention_pkg.Get_NetZero_Line');
42: END IF;
43:
44: SELECT line_num
45: INTO NetZero_line_num
48: AND draft_invoice_num = p_draft_invoice_num
49: AND invoice_line_type = 'NET ZERO ADJUSTMENT';
50:
51: IF g1_debug_mode = 'Y' THEN
52: pa_retention_util.write_log('Get_NetZero_Line : ' || NetZero_line_num);
53: END IF;
54:
55: Update pa_draft_invoice_items
56: set line_num = NetZero_line_num * (-1)
94: TmpRetnLineFmt pa_retention_pkg.TabInvRetnLineFormat;
95:
96: BEGIN
97: IF g1_debug_mode = 'Y' THEN
98: pa_retention_util.write_log('Entering pa_retention_pkg.Get_Proj_Inv_Retn_Format');
99: END IF;
100:
101: OPEN cur_inv_group_columns;
102: LOOP
109: TmpRetnLineFmt(Cnt).end_position := InvGroupColumnsRec.end_position;
110: TmpRetnLineFmt(Cnt).right_justify_flag := InvGroupColumnsRec.right_justify_flag;
111:
112: IF g1_debug_mode = 'Y' THEN
113: pa_retention_util.write_log('Get_Proj_Inv_Retn_Format: ' || 'Format Column : ' || InvGroupColumnsRec.column_code);
114: pa_retention_util.write_log('Get_Proj_Inv_Retn_Format: ' || 'User TExt : ' || InvGroupColumnsRec.text);
115: END IF;
116:
117: END LOOP;
110: TmpRetnLineFmt(Cnt).right_justify_flag := InvGroupColumnsRec.right_justify_flag;
111:
112: IF g1_debug_mode = 'Y' THEN
113: pa_retention_util.write_log('Get_Proj_Inv_Retn_Format: ' || 'Format Column : ' || InvGroupColumnsRec.column_code);
114: pa_retention_util.write_log('Get_Proj_Inv_Retn_Format: ' || 'User TExt : ' || InvGroupColumnsRec.text);
115: END IF;
116:
117: END LOOP;
118: CLOSE cur_inv_group_columns;
117: END LOOP;
118: CLOSE cur_inv_group_columns;
119:
120: IF g1_debug_mode = 'Y' THEN
121: pa_retention_util.write_log('Leaving pa_retention_pkg.Get_Proj_Inv_Retn_Format');
122: END IF;
123:
124: RETURN TmpRetnLineFmt;
125:
161: spr_found EXCEPTION;
162:
163: BEGIN
164: IF g1_debug_mode = 'Y' THEN
165: pa_retention_util.write_log('Entering pa_retention_pkg.Update_Retention_Balances');
166: END IF;
167:
168: IF p_change_type = 'RETAINED' THEN
169:
167:
168: IF p_change_type = 'RETAINED' THEN
169:
170: IF g1_debug_mode = 'Y' THEN
171: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Retained Amount');
172: END IF;
173:
174: -- For Old Invoice Credit memo retention rule id will be null, so no action is needed
175:
175:
176: IF p_retention_rule_id IS NOT NULL THEN
177:
178: IF g1_debug_mode = 'Y' THEN
179: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Retained Amount');
180: END IF;
181:
182: -- Before update the retained amount, make sure there is a record for this agreement
183: -- , rule, project id.
204: INTO l_project_retn_id
205: FROM DUAL;
206:
207: IF g1_debug_mode = 'Y' THEN
208: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert NEW Record Project Retentions');
209: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
210: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
211: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
212: END IF;
205: FROM DUAL;
206:
207: IF g1_debug_mode = 'Y' THEN
208: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert NEW Record Project Retentions');
209: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
210: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
211: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
212: END IF;
213:
206:
207: IF g1_debug_mode = 'Y' THEN
208: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert NEW Record Project Retentions');
209: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
210: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
211: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
212: END IF;
213:
214: INSERT INTO pa_project_retentions
207: IF g1_debug_mode = 'Y' THEN
208: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert NEW Record Project Retentions');
209: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
210: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
211: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
212: END IF;
213:
214: INSERT INTO pa_project_retentions
215: ( PROJECT_RETENTION_ID,
255:
256: WHEN ok_found THEN
257:
258: IF g1_debug_mode = 'Y' THEN
259: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Project Retentions');
260: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
261: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
262: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
263: END IF;
256: WHEN ok_found THEN
257:
258: IF g1_debug_mode = 'Y' THEN
259: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Project Retentions');
260: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
261: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
262: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
263: END IF;
264:
257:
258: IF g1_debug_mode = 'Y' THEN
259: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Project Retentions');
260: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
261: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
262: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
263: END IF;
264:
265: UPDATE pa_project_retentions
258: IF g1_debug_mode = 'Y' THEN
259: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Project Retentions');
260: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
261: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
262: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
263: END IF;
264:
265: UPDATE pa_project_retentions
266: SET total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
280: -- Update the project,agreement and task level balance
281:
282: BEGIN
283: IF g1_debug_mode = 'Y' THEN
284: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
285: END IF;
286:
287: SELECT 'Y' INTO TmpFlag
288: FROM pa_summary_project_retn
300:
301: WHEN NO_DATA_FOUND THEN
302:
303: IF g1_debug_mode = 'Y' THEN
304: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert New Record SPR ');
305: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
306: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
307: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
308: END IF;
301: WHEN NO_DATA_FOUND THEN
302:
303: IF g1_debug_mode = 'Y' THEN
304: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert New Record SPR ');
305: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
306: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
307: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
308: END IF;
309:
302:
303: IF g1_debug_mode = 'Y' THEN
304: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert New Record SPR ');
305: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
306: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
307: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
308: END IF;
309:
310: INSERT INTO pa_summary_project_retn
303: IF g1_debug_mode = 'Y' THEN
304: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Insert New Record SPR ');
305: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
306: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
307: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
308: END IF;
309:
310: INSERT INTO pa_summary_project_retn
311: (
351:
352: WHEN spr_found THEN
353:
354: IF g1_debug_mode = 'Y' THEN
355: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
356: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
357: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
358: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
359: END IF;
352: WHEN spr_found THEN
353:
354: IF g1_debug_mode = 'Y' THEN
355: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
356: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
357: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
358: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
359: END IF;
360:
353:
354: IF g1_debug_mode = 'Y' THEN
355: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
356: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
357: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
358: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
359: END IF;
360:
361: UPDATE pa_summary_project_retn
354: IF g1_debug_mode = 'Y' THEN
355: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR ');
356: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
357: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
358: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
359: END IF;
360:
361: UPDATE pa_summary_project_retn
362: SET total_retained = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
373:
374: END;
375:
376: IF g1_debug_mode = 'Y' THEN
377: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Rule level Balance ');
378: END IF;
379: -- Update the rule level balance
380: IF g1_debug_mode = 'Y' THEN
381: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
377: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update Rule level Balance ');
378: END IF;
379: -- Update the rule level balance
380: IF g1_debug_mode = 'Y' THEN
381: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
382: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
383: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
384: END IF;
385:
378: END IF;
379: -- Update the rule level balance
380: IF g1_debug_mode = 'Y' THEN
381: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
382: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
383: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
384: END IF;
385:
386:
379: -- Update the rule level balance
380: IF g1_debug_mode = 'Y' THEN
381: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
382: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
383: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
384: END IF;
385:
386:
387: UPDATE pa_proj_retn_rules
398: ELSIF p_change_type = 'BILLED' THEN
399:
400: -- Update project or top task, agreement level
401: IF g1_debug_mode = 'Y' THEN
402: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR for Billed Amount ');
403: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
404: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
399:
400: -- Update project or top task, agreement level
401: IF g1_debug_mode = 'Y' THEN
402: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR for Billed Amount ');
403: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
404: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
400: -- Update project or top task, agreement level
401: IF g1_debug_mode = 'Y' THEN
402: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR for Billed Amount ');
403: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
404: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
401: IF g1_debug_mode = 'Y' THEN
402: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR for Billed Amount ');
403: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
404: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
409: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
402: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Update SPR for Billed Amount ');
403: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
404: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
409: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
410: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
403: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project_id : ' || p_project_id);
404: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
409: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
410: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
411: END IF;
404: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Agreement Id : ' || p_agreement_id);
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
409: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
410: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
411: END IF;
412:
405: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Task Id : ' || p_task_id);
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
409: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
410: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
411: END IF;
412:
413: UPDATE pa_summary_project_retn
406: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
407: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Projfunc Amount : ' || p_projfunc_amount);
408: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Funding Amount : ' || p_funding_amount);
409: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Project Amount : ' || p_project_amount);
410: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Invproc Amount : ' || p_amount);
411: END IF;
412:
413: UPDATE pa_summary_project_retn
414: SET total_billed = NVL(total_billed,0) +
423: AND NVL(task_id,-99) = NVL(p_task_id,-99)
424: AND agreement_id = p_agreement_id;
425:
426: IF g1_debug_mode = 'Y' THEN
427: pa_retention_util.write_log('No of Records are Updated : ' || sql%rowcount);
428: END IF;
429:
430: END IF;
431:
437: it should not be deleted
438:
439: Delete the 0 Amount Summary Retn Records and project_retention records
440: IF g1_debug_mode = 'Y' THEN
441: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Delete from pa_project_retentions ');
442: END IF;
443:
444: DELETE FROM pa_project_retentions
445: WHERE project_id = p_project_id
452: AND NVL(funding_total_retained,0) = 0;
453:
454:
455: IF g1_debug_mode = 'Y' THEN
456: pa_retention_util.write_log('No of Records are deleted : ' || sql%rowcount);
457: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Delete from pa_summary_project_retn ');
458: END IF;
459:
460: DELETE FROM pa_summary_project_retn
453:
454:
455: IF g1_debug_mode = 'Y' THEN
456: pa_retention_util.write_log('No of Records are deleted : ' || sql%rowcount);
457: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Delete from pa_summary_project_retn ');
458: END IF;
459:
460: DELETE FROM pa_summary_project_retn
461: WHERE project_id = p_project_id
470: AND NVL(projfunc_total_billed,0) = 0
471: AND NVL(funding_total_billed,0) = 0;
472:
473: IF g1_debug_mode = 'Y' THEN
474: pa_retention_util.write_log('No of Records are deleted : ' || sql%rowcount);
475: END IF;
476:
477: Bug: 2385742 end of the code changes */
478:
476:
477: Bug: 2385742 end of the code changes */
478:
479: IF g1_debug_mode = 'Y' THEN
480: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Leaving from Update Retention Balances ');
481: END IF;
482: EXCEPTION
483: WHEN OTHERS THEN
484: IF g1_debug_mode = 'Y' THEN
481: END IF;
482: EXCEPTION
483: WHEN OTHERS THEN
484: IF g1_debug_mode = 'Y' THEN
485: pa_retention_util.write_log('Update_Retention_Balances: ' || 'Oracle Error ' || sqlerrm);
486: END IF;
487: RAISE;
488:
489: END Update_Retention_Balances;
506:
507: EXCEPTION
508: WHEN OTHERS THEN
509: IF g1_debug_mode = 'Y' THEN
510: pa_retention_util.write_log('Update_Retn_Bill_Trans_Amount: ' || 'Oracle Error ' || sqlerrm);
511: END IF;
512: RAISE;
513:
514: END Update_Retn_Bill_Trans_Amount;
669:
670: BEGIN
671:
672: IF g1_debug_mode = 'Y' THEN
673: pa_retention_util.write_log('Entering Create_Proj_Inv_Retn_Lines');
674: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : ' || p_project_id);
675: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Request Id : ' || p_request_id);
676: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Num : ' || p_draft_invoice_num);
677: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Agrrement Id : ' || AgreementId);
670: BEGIN
671:
672: IF g1_debug_mode = 'Y' THEN
673: pa_retention_util.write_log('Entering Create_Proj_Inv_Retn_Lines');
674: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : ' || p_project_id);
675: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Request Id : ' || p_request_id);
676: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Num : ' || p_draft_invoice_num);
677: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Agrrement Id : ' || AgreementId);
678: END IF;
671:
672: IF g1_debug_mode = 'Y' THEN
673: pa_retention_util.write_log('Entering Create_Proj_Inv_Retn_Lines');
674: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : ' || p_project_id);
675: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Request Id : ' || p_request_id);
676: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Num : ' || p_draft_invoice_num);
677: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Agrrement Id : ' || AgreementId);
678: END IF;
679:
672: IF g1_debug_mode = 'Y' THEN
673: pa_retention_util.write_log('Entering Create_Proj_Inv_Retn_Lines');
674: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : ' || p_project_id);
675: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Request Id : ' || p_request_id);
676: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Num : ' || p_draft_invoice_num);
677: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Agrrement Id : ' || AgreementId);
678: END IF;
679:
680: --Moved the following code here for Bug3604143
673: pa_retention_util.write_log('Entering Create_Proj_Inv_Retn_Lines');
674: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : ' || p_project_id);
675: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Request Id : ' || p_request_id);
676: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Num : ' || p_draft_invoice_num);
677: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Agrrement Id : ' || AgreementId);
678: END IF;
679:
680: --Moved the following code here for Bug3604143
681: -- Select the Funding Currency
698: --till here for Bug3604143
699:
700: OPEN cur_invoice;
701: IF g1_debug_mode = 'Y' THEN
702: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Build PL/SQL Table for Rules ');
703: END IF;
704: LOOP
705: FETCH cur_invoice INTO invoice_rec;
706: EXIT WHEN cur_invoice%NOTFOUND;
705: FETCH cur_invoice INTO invoice_rec;
706: EXIT WHEN cur_invoice%NOTFOUND;
707: TmpCnt := NVL(TmpCnt,0) +1;
708: IF g1_debug_mode = 'Y' THEN
709: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule Table for ' || TmpCnt);
710: END IF;
711:
712: TmpRuleAndInvoiced(TmpCnt).retention_rule_id := invoice_rec.retention_rule_id;
713: TmpRuleAndInvoiced(TmpCnt).source_type := invoice_rec.source_type;
717: TmpRuleAndInvoiced(TmpCnt).fc_invoice_amount := invoice_rec.fc_invoice_amount;
718: TmpRuleAndInvoiced(TmpCnt).btc_invoice_amount := invoice_rec.btc_invoice_amount;
719:
720: IF g1_debug_mode = 'Y' THEN
721: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule ID : ' || invoice_rec.retention_rule_id);
722: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || invoice_rec.source_type);
723: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
718: TmpRuleAndInvoiced(TmpCnt).btc_invoice_amount := invoice_rec.btc_invoice_amount;
719:
720: IF g1_debug_mode = 'Y' THEN
721: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule ID : ' || invoice_rec.retention_rule_id);
722: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || invoice_rec.source_type);
723: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
726: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'fc_invoice_amount : ' || invoice_rec.fc_invoice_amount);
719:
720: IF g1_debug_mode = 'Y' THEN
721: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule ID : ' || invoice_rec.retention_rule_id);
722: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || invoice_rec.source_type);
723: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
726: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'fc_invoice_amount : ' || invoice_rec.fc_invoice_amount);
727: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'btc_invoice_amount : ' || invoice_rec.btc_invoice_amount);
720: IF g1_debug_mode = 'Y' THEN
721: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule ID : ' || invoice_rec.retention_rule_id);
722: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || invoice_rec.source_type);
723: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
726: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'fc_invoice_amount : ' || invoice_rec.fc_invoice_amount);
727: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'btc_invoice_amount : ' || invoice_rec.btc_invoice_amount);
728: END IF;
721: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Rule ID : ' || invoice_rec.retention_rule_id);
722: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || invoice_rec.source_type);
723: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
726: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'fc_invoice_amount : ' || invoice_rec.fc_invoice_amount);
727: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'btc_invoice_amount : ' || invoice_rec.btc_invoice_amount);
728: END IF;
729:
722: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || invoice_rec.source_type);
723: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
726: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'fc_invoice_amount : ' || invoice_rec.fc_invoice_amount);
727: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'btc_invoice_amount : ' || invoice_rec.btc_invoice_amount);
728: END IF;
729:
730: END LOOP;
723: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'invoice_amount : ' || invoice_rec.invoice_amount);
724: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pfc_invoice_amount : ' || invoice_rec.pfc_invoice_amount);
725: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'pc_invoice_amount : ' || invoice_rec.pc_invoice_amount);
726: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'fc_invoice_amount : ' || invoice_rec.fc_invoice_amount);
727: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'btc_invoice_amount : ' || invoice_rec.btc_invoice_amount);
728: END IF;
729:
730: END LOOP;
731:
753: END IF;
754:
755:
756: IF g1_debug_mode = 'Y' THEN
757: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Select Funding Currency ');
758: END IF;
759:
760: /* Commented and moved this code in the beginning for Bug3604143
761: -- Select the Funding Currency
768: AND di.draft_invoice_num = p_draft_invoice_num;
769: */
770:
771: IF g1_debug_mode = 'Y' THEN
772: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Funding Currency : ' || FundingCurrency);
773: END IF;
774:
775: -- Get all the currency code for this project
776:
774:
775: -- Get all the currency code for this project
776:
777: IF g1_debug_mode = 'Y' THEN
778: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Finding Invoice Processing Currency');
779: END IF;
780:
781: /* Commented and moved this code in the beginning for Bug3604143
782: SELECT pr.project_currency_code,
789: WHERE pr.project_id = p_project_id;
790: */
791:
792: IF g1_debug_mode = 'Y' THEN
793: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Processing Currency : ' || InvProcCurrency);
794: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Calling Get_Proj_Inv_Retn_Format');
795: END IF;
796:
797: InvRetnLineFmt := Get_Proj_Inv_Retn_Format(p_project_id =>p_project_id);
790: */
791:
792: IF g1_debug_mode = 'Y' THEN
793: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Processing Currency : ' || InvProcCurrency);
794: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Calling Get_Proj_Inv_Retn_Format');
795: END IF;
796:
797: InvRetnLineFmt := Get_Proj_Inv_Retn_Format(p_project_id =>p_project_id);
798:
797: InvRetnLineFmt := Get_Proj_Inv_Retn_Format(p_project_id =>p_project_id);
798:
799:
800: IF g1_debug_mode = 'Y' THEN
801: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Processing Rules');
802: END IF;
803:
804: FOR j IN 1..TmpCnt LOOP -- For loop starts for Retention Rules
805:
803:
804: FOR j IN 1..TmpCnt LOOP -- For loop starts for Retention Rules
805:
806: IF g1_debug_mode = 'Y' THEN
807: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Row Num : ' || j);
808: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Current RetentionRuleId : ' || TmpRuleAndInvoiced(j).retention_rule_id);
809: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetentionRuleId : ' || RetentionRuleId);
810: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'NewRetentionRuleId : ' || NewRetentionRuleId);
811: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || TmpRuleAndInvoiced(j).source_type);
804: FOR j IN 1..TmpCnt LOOP -- For loop starts for Retention Rules
805:
806: IF g1_debug_mode = 'Y' THEN
807: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Row Num : ' || j);
808: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Current RetentionRuleId : ' || TmpRuleAndInvoiced(j).retention_rule_id);
809: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetentionRuleId : ' || RetentionRuleId);
810: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'NewRetentionRuleId : ' || NewRetentionRuleId);
811: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || TmpRuleAndInvoiced(j).source_type);
812: END IF;
805:
806: IF g1_debug_mode = 'Y' THEN
807: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Row Num : ' || j);
808: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Current RetentionRuleId : ' || TmpRuleAndInvoiced(j).retention_rule_id);
809: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetentionRuleId : ' || RetentionRuleId);
810: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'NewRetentionRuleId : ' || NewRetentionRuleId);
811: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || TmpRuleAndInvoiced(j).source_type);
812: END IF;
813:
806: IF g1_debug_mode = 'Y' THEN
807: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Row Num : ' || j);
808: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Current RetentionRuleId : ' || TmpRuleAndInvoiced(j).retention_rule_id);
809: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetentionRuleId : ' || RetentionRuleId);
810: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'NewRetentionRuleId : ' || NewRetentionRuleId);
811: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || TmpRuleAndInvoiced(j).source_type);
812: END IF;
813:
814: IF NVL(RetentionRuleId,0) = 0 and NVL(NewRetentionRuleID,0) =0 THEN -- If it is a first run
807: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Row Num : ' || j);
808: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Current RetentionRuleId : ' || TmpRuleAndInvoiced(j).retention_rule_id);
809: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetentionRuleId : ' || RetentionRuleId);
810: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'NewRetentionRuleId : ' || NewRetentionRuleId);
811: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Source Type : ' || TmpRuleAndInvoiced(j).source_type);
812: END IF;
813:
814: IF NVL(RetentionRuleId,0) = 0 and NVL(NewRetentionRuleID,0) =0 THEN -- If it is a first run
815:
813:
814: IF NVL(RetentionRuleId,0) = 0 and NVL(NewRetentionRuleID,0) =0 THEN -- If it is a first run
815:
816: IF g1_debug_mode = 'Y' THEN
817: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'For First Rule ');
818: END IF;
819:
820: NewRetentionRuleId := TmpRuleAndInvoiced(j).retention_rule_id;
821: RetentionRuleId := TmpRuleAndInvoiced(j).retention_rule_id;
825: FCInvoiceAmount :=0;
826: BTCInvoiceAmount :=0; --For Bug 5194917
827:
828: IF g1_debug_mode = 'Y' THEN
829: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run RetentionRuleId : ' || RetentionRuleId);
830: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run NewRetentionRuleId : ' || NewRetentionRuleId);
831: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run Source Type : ' || TmpRuleAndInvoiced(j).source_type);
832: END IF;
833:
826: BTCInvoiceAmount :=0; --For Bug 5194917
827:
828: IF g1_debug_mode = 'Y' THEN
829: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run RetentionRuleId : ' || RetentionRuleId);
830: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run NewRetentionRuleId : ' || NewRetentionRuleId);
831: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run Source Type : ' || TmpRuleAndInvoiced(j).source_type);
832: END IF;
833:
834: IF RTRIM(TmpRuleAndInvoiced(j).source_type) ='RDL' THEN
827:
828: IF g1_debug_mode = 'Y' THEN
829: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run RetentionRuleId : ' || RetentionRuleId);
830: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run NewRetentionRuleId : ' || NewRetentionRuleId);
831: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || '1st run Source Type : ' || TmpRuleAndInvoiced(j).source_type);
832: END IF;
833:
834: IF RTRIM(TmpRuleAndInvoiced(j).source_type) ='RDL' THEN
835:
834: IF RTRIM(TmpRuleAndInvoiced(j).source_type) ='RDL' THEN
835:
836: UpdateRDL := TRUE;
837: IF g1_debug_mode = 'Y' THEN
838: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateRDL= True');
839: END IF;
840:
841: ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='ERDL' THEN
842:
842:
843: UpdateERDL := TRUE;
844:
845: IF g1_debug_mode = 'Y' THEN
846: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateERDL= True');
847: END IF;
848:
849: ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='EVENT' THEN
850:
850:
851: UpdateDII := TRUE;
852:
853: IF g1_debug_mode = 'Y' THEN
854: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateDII= True');
855: END IF;
856:
857: END IF;
858:
873:
874: UpdateRDL := TRUE;
875:
876: IF g1_debug_mode = 'Y' THEN
877: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateRDL= True');
878: END IF;
879:
880: ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='ERDL' THEN
881:
880: ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='ERDL' THEN
881:
882: UpdateERDL := TRUE;
883: IF g1_debug_mode = 'Y' THEN
884: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateERDL= True');
885: END IF;
886:
887: ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='EVENT' THEN
888:
887: ELSIF RTRIM(TmpRuleAndInvoiced(j).source_type) ='EVENT' THEN
888:
889: UpdateDII := TRUE;
890: IF g1_debug_mode = 'Y' THEN
891: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'UpdateDII= True');
892: END IF;
893:
894: END IF;
895:
897:
898: IF (RetentionRuleID <> NewRetentionRuleId) OR (j=TmpCnt) THEN -- rule changes
899:
900: IF g1_debug_mode = 'Y' THEN
901: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Create Retention Line ');
902: END IF;
903:
904: BEGIN
905: OPEN cur_retn_rule;
907:
908: -- Find the whethoer any amounts to be retained or not
909:
910: IF g1_debug_mode = 'Y' THEN
911: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Check Threshold');
912: END IF;
913:
914: IF ( ( NVL(retn_rule_rec.threshold_amount,0) >
915: NVL(retn_rule_rec.total_retained,0) )
915: NVL(retn_rule_rec.total_retained,0) )
916: OR (NVL(retn_rule_rec.threshold_amount,0)=0) ) THEN
917:
918: IF g1_debug_mode = 'Y' THEN
919: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || ' If NVL(retn_rule_rec.threshold_amount,0) >
920: NVL(retn_rule_rec.total_retained,0)');
921: END IF;
922:
923: -- Remaining amount to retain
924:
925: IF NVL(retn_rule_rec.threshold_amount,0)<> 0 THEN
926:
927: IF g1_debug_mode = 'Y' THEN
928: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || ' IF NVL(retn_rule_rec.threshold_amount,0)<> 0 ');
929: END IF;
930:
931: RetnRemainAmount :=NVL(retn_rule_rec.threshold_amount,0) -
932: NVL(retn_rule_rec.total_retained,0);
930:
931: RetnRemainAmount :=NVL(retn_rule_rec.threshold_amount,0) -
932: NVL(retn_rule_rec.total_retained,0);
933: IF g1_debug_mode = 'Y' THEN
934: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.threshold_amount: ' || retn_rule_rec.threshold_amount);
935: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.total_retained: ' || retn_rule_rec.total_retained);
936: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetnRemainAmount: ' || RetnRemainAmount);
937: END IF;
938:
931: RetnRemainAmount :=NVL(retn_rule_rec.threshold_amount,0) -
932: NVL(retn_rule_rec.total_retained,0);
933: IF g1_debug_mode = 'Y' THEN
934: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.threshold_amount: ' || retn_rule_rec.threshold_amount);
935: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.total_retained: ' || retn_rule_rec.total_retained);
936: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetnRemainAmount: ' || RetnRemainAmount);
937: END IF;
938:
939: END IF;
932: NVL(retn_rule_rec.total_retained,0);
933: IF g1_debug_mode = 'Y' THEN
934: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.threshold_amount: ' || retn_rule_rec.threshold_amount);
935: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'retn_rule_rec.total_retained: ' || retn_rule_rec.total_retained);
936: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetnRemainAmount: ' || RetnRemainAmount);
937: END IF;
938:
939: END IF;
940:
938:
939: END IF;
940:
941: IF g1_debug_mode = 'Y' THEN
942: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'RetnRemainAmount: ' || RetnRemainAmount);
943: END IF;
944:
945: -- Check if the retention percentage is not equal to zero
946:
945: -- Check if the retention percentage is not equal to zero
946:
947: IF NVL(retn_rule_rec.retention_percentage,0) <> 0 THEN
948: IF g1_debug_mode = 'Y' THEN
949: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the percentage is not equal to zero' );
950: END IF;
951:
952: TmpRetainAmount := NVL(InvoiceAmount,0) *
953: (NVL(retn_rule_rec.retention_percentage,0)/100);
951:
952: TmpRetainAmount := NVL(InvoiceAmount,0) *
953: (NVL(retn_rule_rec.retention_percentage,0)/100);
954: IF g1_debug_mode = 'Y' THEN
955: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
956: END IF;
957: --For Bug 5194917
958: /* ELSIF NVL(retn_rule_rec.retention_percentage,0) = 0 THEN
959: IF g1_debug_mode = 'Y' THEN
956: END IF;
957: --For Bug 5194917
958: /* ELSIF NVL(retn_rule_rec.retention_percentage,0) = 0 THEN
959: IF g1_debug_mode = 'Y' THEN
960: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the percentage is equal to zero' );
961: END IF;
962:
963: TmpRetainAmount := 0;
964: IF g1_debug_mode = 'Y' THEN
961: END IF;
962:
963: TmpRetainAmount := 0;
964: IF g1_debug_mode = 'Y' THEN
965: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
966: END IF; Commented for Bug 6152291*/
967: --End of changes for Bug 5194917
968: ELSIF NVL(retn_rule_rec.retention_amount,0) <> 0 THEN
969:
968: ELSIF NVL(retn_rule_rec.retention_amount,0) <> 0 THEN
969:
970: -- Check if the retention amount is not equal to zero
971: IF g1_debug_mode = 'Y' THEN
972: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the retention amount is not equal to zero' );
973: END IF;
974:
975: TmpRetainAmount := NVL(retn_rule_rec.retention_amount,0);
976: IF g1_debug_mode = 'Y' THEN
973: END IF;
974:
975: TmpRetainAmount := NVL(retn_rule_rec.retention_amount,0);
976: IF g1_debug_mode = 'Y' THEN
977: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
978: END IF;
979:
980: IF NVL(retn_rule_rec.retention_amount,0) > NVL(InvoiceAmount,0) THEN
981: IF g1_debug_mode = 'Y' THEN
978: END IF;
979:
980: IF NVL(retn_rule_rec.retention_amount,0) > NVL(InvoiceAmount,0) THEN
981: IF g1_debug_mode = 'Y' THEN
982: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Retention Amount > InvoiceAmount');
983: END IF;
984: TmpRetainAmount := NVL(InvoiceAmount,0);
985: IF g1_debug_mode = 'Y' THEN
986: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
982: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Retention Amount > InvoiceAmount');
983: END IF;
984: TmpRetainAmount := NVL(InvoiceAmount,0);
985: IF g1_debug_mode = 'Y' THEN
986: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
987: END IF;
988: END IF;
989: --For Bug 5194917
990: ELSIF NVL(retn_rule_rec.retention_amount,0) = 0 and NVL(retn_rule_rec.retention_percentage,0) = 0 THEN /* Added and condition for bug 6152291*/
990: ELSIF NVL(retn_rule_rec.retention_amount,0) = 0 and NVL(retn_rule_rec.retention_percentage,0) = 0 THEN /* Added and condition for bug 6152291*/
991:
992: -- Check if the retention amount is equal to zero
993: IF g1_debug_mode = 'Y' THEN
994: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If the retention amount is equal to zero' );
995: END IF;
996:
997: TmpRetainAmount := 0;
998: IF g1_debug_mode = 'Y' THEN
995: END IF;
996:
997: TmpRetainAmount := 0;
998: IF g1_debug_mode = 'Y' THEN
999: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'TmpRetainAmount: ' || TmpRetainAmount);
1000: END IF;
1001: --End of Bug 5194917
1002: END IF;
1003:
1006: IF NVL(retn_rule_rec.threshold_amount,0)<> 0 THEN
1007:
1008: -- If the Current Retain amount is less or equal
1009: IF g1_debug_mode = 'Y' THEN
1010: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If threshold is not equal to zero ');
1011: END IF;
1012:
1013: IF NVL(TmpRetainAmount,0) <= NVL(RetnRemainAmount,0) THEN
1014: IF g1_debug_mode = 'Y' THEN
1011: END IF;
1012:
1013: IF NVL(TmpRetainAmount,0) <= NVL(RetnRemainAmount,0) THEN
1014: IF g1_debug_mode = 'Y' THEN
1015: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If TmpRetainAmount <= RetnRemainAmount ');
1016: END IF;
1017:
1018: CurRetainAmount := NVL(TmpRetainAmount,0);
1019:
1017:
1018: CurRetainAmount := NVL(TmpRetainAmount,0);
1019:
1020: IF g1_debug_mode = 'Y' THEN
1021: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount :' || CurRetainAmount );
1022: END IF;
1023:
1024:
1025: ELSIF NVL(TmpRetainAmount,0) > NVL(RetnRemainAmount,0) THEN
1025: ELSIF NVL(TmpRetainAmount,0) > NVL(RetnRemainAmount,0) THEN
1026:
1027: -- If the Current Retain amount is less or equal
1028: IF g1_debug_mode = 'Y' THEN
1029: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'If TmpRetainAmount > RetnRemainAmount ');
1030: END IF;
1031:
1032: CurRetainAmount := NVL(RetnRemainAmount,0);
1033: IF g1_debug_mode = 'Y' THEN
1030: END IF;
1031:
1032: CurRetainAmount := NVL(RetnRemainAmount,0);
1033: IF g1_debug_mode = 'Y' THEN
1034: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount :' || CurRetainAmount );
1035: END IF;
1036:
1037: END IF;
1038: ELSE
1036:
1037: END IF;
1038: ELSE
1039: IF g1_debug_mode = 'Y' THEN
1040: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'No threshold');
1041: END IF;
1042: CurRetainAmount := NVL(TmpRetainAmount,0);
1043: IF g1_debug_mode = 'Y' THEN
1044: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount :' || CurRetainAmount );
1040: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'No threshold');
1041: END IF;
1042: CurRetainAmount := NVL(TmpRetainAmount,0);
1043: IF g1_debug_mode = 'Y' THEN
1044: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount :' || CurRetainAmount );
1045: END IF;
1046: END IF;
1047:
1048:
1076: BTCCurRetainAmount :=0;
1077: END IF; /*CurRetainAmount */
1078:
1079: IF g1_debug_mode = 'Y' THEN
1080: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvoiceAmount : ' || InvoiceAmount);
1081: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount : ' || CurRetainAmount
1082: ||' ' || InvProcCurrency);
1083: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PFCCurRetainAmount : ' || PFCCurRetainAmount
1084: ||' ' || ProjFuncCUrrency);
1077: END IF; /*CurRetainAmount */
1078:
1079: IF g1_debug_mode = 'Y' THEN
1080: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvoiceAmount : ' || InvoiceAmount);
1081: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount : ' || CurRetainAmount
1082: ||' ' || InvProcCurrency);
1083: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PFCCurRetainAmount : ' || PFCCurRetainAmount
1084: ||' ' || ProjFuncCUrrency);
1085: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PCCurRetainAmount : ' || PCCurRetainAmount
1079: IF g1_debug_mode = 'Y' THEN
1080: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvoiceAmount : ' || InvoiceAmount);
1081: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount : ' || CurRetainAmount
1082: ||' ' || InvProcCurrency);
1083: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PFCCurRetainAmount : ' || PFCCurRetainAmount
1084: ||' ' || ProjFuncCUrrency);
1085: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PCCurRetainAmount : ' || PCCurRetainAmount
1086: ||' ' || ProjectCurrency);
1087: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'FCCurRetainAmount : ' || FCCurRetainAmount
1081: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'CurRetainAmount : ' || CurRetainAmount
1082: ||' ' || InvProcCurrency);
1083: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PFCCurRetainAmount : ' || PFCCurRetainAmount
1084: ||' ' || ProjFuncCUrrency);
1085: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PCCurRetainAmount : ' || PCCurRetainAmount
1086: ||' ' || ProjectCurrency);
1087: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'FCCurRetainAmount : ' || FCCurRetainAmount
1088: ||' ' || FundingCurrency);
1089: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'BTCCurRetainAmount : ' || BTCCurRetainAmount);
1083: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PFCCurRetainAmount : ' || PFCCurRetainAmount
1084: ||' ' || ProjFuncCUrrency);
1085: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PCCurRetainAmount : ' || PCCurRetainAmount
1086: ||' ' || ProjectCurrency);
1087: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'FCCurRetainAmount : ' || FCCurRetainAmount
1088: ||' ' || FundingCurrency);
1089: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'BTCCurRetainAmount : ' || BTCCurRetainAmount);
1090: END IF;
1091:
1085: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'PCCurRetainAmount : ' || PCCurRetainAmount
1086: ||' ' || ProjectCurrency);
1087: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'FCCurRetainAmount : ' || FCCurRetainAmount
1088: ||' ' || FundingCurrency);
1089: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'BTCCurRetainAmount : ' || BTCCurRetainAmount);
1090: END IF;
1091:
1092: END IF;
1093:
1094: IF NVL(CurRetainAmount,0) <>0 THEN
1095:
1096: -- Building the Invoice Retention Line Format
1097: IF g1_debug_mode = 'Y' THEN
1098: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'IF NVL(CurRetainAmount,0) <>0 ');
1099: END IF;
1100:
1101: IF InvRetnLineFmt.count <> 0 THEN
1102: IF g1_debug_mode = 'Y' THEN
1099: END IF;
1100:
1101: IF InvRetnLineFmt.count <> 0 THEN
1102: IF g1_debug_mode = 'Y' THEN
1103: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt.count : ' ||
1104: InvRetnLineFmt.count);
1105: END IF;
1106:
1107: FOR k IN 1..InvRetnLineFmt.Count LOOP
1121: LastEndPosition := InvRetnLineFmt(k).end_position;
1122:
1123: END IF;
1124: IF g1_debug_mode = 'Y' THEN
1125: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt Row : ' || k);
1126: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt Column : ' || InvRetnLineFmt(k).column_code);
1127: END IF;
1128:
1129: IF InvRetnLineFmt(k).column_code = 'RETENTION PERCENTAGE' THEN
1122:
1123: END IF;
1124: IF g1_debug_mode = 'Y' THEN
1125: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt Row : ' || k);
1126: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'InvRetnLineFmt Column : ' || InvRetnLineFmt(k).column_code);
1127: END IF;
1128:
1129: IF InvRetnLineFmt(k).column_code = 'RETENTION PERCENTAGE' THEN
1130:
1141: InvRetnLineFmt(k).end_position-
1142: InvRetnLineFmt(k).start_position,' ');
1143: END IF;
1144: IF g1_debug_mode = 'Y' THEN
1145: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Fmt Text : ' || RetnLineText);
1146: END IF;
1147:
1148: ELSIF InvRetnLineFmt(k).column_code = 'TEXT' THEN
1149:
1317: END IF;
1318:
1319:
1320: IF g1_debug_mode = 'Y' THEN
1321: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Line Text : ' || RetnLineText);
1322: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Calling Get_Invoice_Max_Line');
1323: END IF;
1324: -- Get the Last Invoice Line Number
1325: LastLineNum := Get_Invoice_Max_Line(p_project_id=>p_project_id,
1318:
1319:
1320: IF g1_debug_mode = 'Y' THEN
1321: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Line Text : ' || RetnLineText);
1322: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Calling Get_Invoice_Max_Line');
1323: END IF;
1324: -- Get the Last Invoice Line Number
1325: LastLineNum := Get_Invoice_Max_Line(p_project_id=>p_project_id,
1326: p_draft_invoice_num=>p_draft_invoice_num);
1325: LastLineNum := Get_Invoice_Max_Line(p_project_id=>p_project_id,
1326: p_draft_invoice_num=>p_draft_invoice_num);
1327:
1328: IF g1_debug_mode = 'Y' THEN
1329: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice_Max_Line : ' || LastLineNum);
1330: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : '||p_project_id);
1331: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Nun : '||p_draft_invoice_num);
1332: END IF;
1333:
1326: p_draft_invoice_num=>p_draft_invoice_num);
1327:
1328: IF g1_debug_mode = 'Y' THEN
1329: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice_Max_Line : ' || LastLineNum);
1330: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : '||p_project_id);
1331: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Nun : '||p_draft_invoice_num);
1332: END IF;
1333:
1334: -- Insert a Retention Line
1327:
1328: IF g1_debug_mode = 'Y' THEN
1329: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice_Max_Line : ' || LastLineNum);
1330: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Project Id : '||p_project_id);
1331: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Invoice Nun : '||p_draft_invoice_num);
1332: END IF;
1333:
1334: -- Insert a Retention Line
1335:
1401: p_Output_tax_exempt_flag,
1402: p_Output_tax_exempt_number,
1403: p_Output_exempt_reason_code);
1404: IF g1_debug_mode = 'Y' THEN
1405: pa_retention_util.write_log('Rows Inserted : '||sql%rowcount);
1406: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Call Update_Retention_Balances');
1407: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Retention Rule Id ' || retn_rule_rec.retention_rule_id);
1408: END IF;
1409:
1402: p_Output_tax_exempt_number,
1403: p_Output_exempt_reason_code);
1404: IF g1_debug_mode = 'Y' THEN
1405: pa_retention_util.write_log('Rows Inserted : '||sql%rowcount);
1406: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Call Update_Retention_Balances');
1407: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Retention Rule Id ' || retn_rule_rec.retention_rule_id);
1408: END IF;
1409:
1410: Update_Retention_Balances(p_retention_rule_id=>retn_rule_rec.retention_rule_id,
1403: p_Output_exempt_reason_code);
1404: IF g1_debug_mode = 'Y' THEN
1405: pa_retention_util.write_log('Rows Inserted : '||sql%rowcount);
1406: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Call Update_Retention_Balances');
1407: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Retention Rule Id ' || retn_rule_rec.retention_rule_id);
1408: END IF;
1409:
1410: Update_Retention_Balances(p_retention_rule_id=>retn_rule_rec.retention_rule_id,
1411: p_project_id =>p_project_id,
1427:
1428: IF (UpdateRDL) THEN
1429:
1430: IF g1_debug_mode = 'Y' THEN
1431: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update RDLs ');
1432: END IF;
1433:
1434: UPDATE pa_cust_rev_dist_lines_all
1435: SET retn_draft_invoice_num = p_draft_invoice_num,
1448: -- Update ERDL
1449:
1450: IF (UpdateERDL) THEN
1451: IF g1_debug_mode = 'Y' THEN
1452: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update ERDLs ');
1453: END IF;
1454:
1455: UPDATE pa_cust_event_rdl_all
1456: SET retn_draft_invoice_num = p_draft_invoice_num,
1470:
1471: IF (UpdateDII) THEN
1472:
1473: IF g1_debug_mode = 'Y' THEN
1474: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Update DII ');
1475: END IF;
1476:
1477: UPDATE pa_draft_invoice_items
1478: SET retn_draft_invoice_num = p_draft_invoice_num,
1523:
1524: END IF; -- new is not equal to current
1525:
1526: IF g1_debug_mode = 'Y' THEN
1527: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Continue Row Num : ' || j);
1528: END IF;
1529:
1530: END LOOP; -- For loop ends for Retention Rules
1531:
1530: END LOOP; -- For loop ends for Retention Rules
1531:
1532: END IF; -- Count is not equal to zero
1533: IF g1_debug_mode = 'Y' THEN
1534: pa_retention_util.write_log('Leaving Create_Proj_Inv_Retn_Lines ');
1535: END IF;
1536: EXCEPTION
1537: WHEN OTHERS THEN
1538: IF g1_debug_mode = 'Y' THEN
1535: END IF;
1536: EXCEPTION
1537: WHEN OTHERS THEN
1538: IF g1_debug_mode = 'Y' THEN
1539: pa_retention_util.write_log('Create_Proj_Inv_Retn_Lines: ' || 'Oracle Error ' || sqlerrm);
1540: END IF;
1541: RAISE;
1542:
1543: END Create_Proj_Inv_Retn_Lines;
1649: LastLineNum NUMBER:=0;
1650:
1651: BEGIN
1652: IF g1_debug_mode = 'Y' THEN
1653: pa_retention_util.write_log('Entering Proj_Invoice_Retn_Processing ');
1654: END IF;
1655:
1656: OPEN cur_proj_cust;
1657: LOOP
1666: TmpShipToCustomerId :=ProjCustRec.Ship_to_customer_id;/*Added for customer account relation*/
1667: TmpSiteUSeId1 :=ProjCustRec.Bill_site_use_id;
1668: TmpSiteUSeId2 :=ProjCustRec.Ship_site_use_id;
1669: IF g1_debug_mode = 'Y' THEN
1670: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Customer ID : ' || CustomerId);
1671: END IF;
1672:
1673: OPEN cur_proj_inv;
1674: LOOP
1677:
1678: DraftInvoiceNum := ProjInvRec.draft_invoice_num;
1679:
1680: IF g1_debug_mode = 'Y' THEN
1681: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Invoice Number : ' || DraftInvoiceNum);
1682: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Bill To Addres Id : ' || TmpBillToAddressID);
1683: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Ship To Addres Id : ' || TmpShipToAddressID);
1684: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Set Of Boooks Id : ' || TmpSetOfBooks);
1685: END IF;
1678: DraftInvoiceNum := ProjInvRec.draft_invoice_num;
1679:
1680: IF g1_debug_mode = 'Y' THEN
1681: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Invoice Number : ' || DraftInvoiceNum);
1682: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Bill To Addres Id : ' || TmpBillToAddressID);
1683: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Ship To Addres Id : ' || TmpShipToAddressID);
1684: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Set Of Boooks Id : ' || TmpSetOfBooks);
1685: END IF;
1686:
1679:
1680: IF g1_debug_mode = 'Y' THEN
1681: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Invoice Number : ' || DraftInvoiceNum);
1682: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Bill To Addres Id : ' || TmpBillToAddressID);
1683: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Ship To Addres Id : ' || TmpShipToAddressID);
1684: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Set Of Boooks Id : ' || TmpSetOfBooks);
1685: END IF;
1686:
1687: --- Call Tax Information
1680: IF g1_debug_mode = 'Y' THEN
1681: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Invoice Number : ' || DraftInvoiceNum);
1682: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Bill To Addres Id : ' || TmpBillToAddressID);
1683: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Ship To Addres Id : ' || TmpShipToAddressID);
1684: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Set Of Boooks Id : ' || TmpSetOfBooks);
1685: END IF;
1686:
1687: --- Call Tax Information
1688: /*Last two parameters added for customer account relation enhancement*/
1704: Pbill_to_customer_id => TmpBillToCustomerId,
1705: Pship_to_customer_id => TmpShipToCustomerId);
1706:
1707: IF g1_debug_mode = 'Y' THEN
1708: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tax Information: ');
1709: -- pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_vat_tax_id : ' || Tmp_Output_vat_tax_id);
1710: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_code : ' || Tmp_Output_tax_code);
1711: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_flag : ' || Tmp_Output_tax_exempt_flag);
1712: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_number : ' || Tmp_Output_tax_exempt_number);
1705: Pship_to_customer_id => TmpShipToCustomerId);
1706:
1707: IF g1_debug_mode = 'Y' THEN
1708: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tax Information: ');
1709: -- pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_vat_tax_id : ' || Tmp_Output_vat_tax_id);
1710: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_code : ' || Tmp_Output_tax_code);
1711: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_flag : ' || Tmp_Output_tax_exempt_flag);
1712: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_number : ' || Tmp_Output_tax_exempt_number);
1713: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_exempt_reason_code : ' || Tmp_Output_exempt_reason_code);
1706:
1707: IF g1_debug_mode = 'Y' THEN
1708: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tax Information: ');
1709: -- pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_vat_tax_id : ' || Tmp_Output_vat_tax_id);
1710: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_code : ' || Tmp_Output_tax_code);
1711: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_flag : ' || Tmp_Output_tax_exempt_flag);
1712: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_number : ' || Tmp_Output_tax_exempt_number);
1713: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_exempt_reason_code : ' || Tmp_Output_exempt_reason_code);
1714: END IF;
1707: IF g1_debug_mode = 'Y' THEN
1708: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tax Information: ');
1709: -- pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_vat_tax_id : ' || Tmp_Output_vat_tax_id);
1710: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_code : ' || Tmp_Output_tax_code);
1711: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_flag : ' || Tmp_Output_tax_exempt_flag);
1712: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_number : ' || Tmp_Output_tax_exempt_number);
1713: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_exempt_reason_code : ' || Tmp_Output_exempt_reason_code);
1714: END IF;
1715:
1708: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tax Information: ');
1709: -- pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_vat_tax_id : ' || Tmp_Output_vat_tax_id);
1710: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_code : ' || Tmp_Output_tax_code);
1711: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_flag : ' || Tmp_Output_tax_exempt_flag);
1712: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_number : ' || Tmp_Output_tax_exempt_number);
1713: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_exempt_reason_code : ' || Tmp_Output_exempt_reason_code);
1714: END IF;
1715:
1716:
1709: -- pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_vat_tax_id : ' || Tmp_Output_vat_tax_id);
1710: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_code : ' || Tmp_Output_tax_code);
1711: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_flag : ' || Tmp_Output_tax_exempt_flag);
1712: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_tax_exempt_number : ' || Tmp_Output_tax_exempt_number);
1713: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Tmp_Output_exempt_reason_code : ' || Tmp_Output_exempt_reason_code);
1714: END IF;
1715:
1716:
1717: IF ProjCustRec.retention_level ='PROJECT' THEN
1716:
1717: IF ProjCustRec.retention_level ='PROJECT' THEN
1718:
1719: IF g1_debug_mode = 'Y' THEN
1720: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'PROJECT Level ');
1721: END IF;
1722:
1723: -- 1. Update the Non Labor Resource Override in RDLs
1724:
1750: AND trunc(ei.expenditure_item_date) BETWEEN
1751: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1)) ;
1752:
1753: IF g1_debug_mode = 'Y' THEN
1754: pa_retention_util.write_log('RDL: No Records Update At Non Labor Level ' ||sql%rowcount);
1755: END IF;
1756:
1757:
1758: -- 2. Update the Expenditure Type Level Override in RDLs
1786: AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1787: AND trunc(ei.expenditure_item_date) BETWEEN
1788: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
1789: IF g1_debug_mode = 'Y' THEN
1790: pa_retention_util.write_log('RDL: No Records Update At Expenditure Type Level ' ||sql%rowcount);
1791: END IF;
1792:
1793: -- 3. Update the Expenditure Category Level Override in RDLs
1794:
1829: AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1830: AND trunc(ei.expenditure_item_date) BETWEEN
1831: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date +1));
1832: IF g1_debug_mode = 'Y' THEN
1833: pa_retention_util.write_log('RDL: No Records Update At Expenditure Category Level ' ||sql%rowcount);
1834: END IF;
1835:
1836:
1837: -- 4. Update the Project Level Retention Setup in RDLs
1866: AND trunc(ei.expenditure_item_date) BETWEEN
1867: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date));
1868:
1869: IF g1_debug_mode = 'Y' THEN
1870: pa_retention_util.write_log('RDL: No Records Update At Project Level ' ||sql%rowcount);
1871: END IF;
1872:
1873:
1874: -- 1. Update the Event Type Level Retention Setup in ERDLs
1915: and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
1916: AND evttyp.revenue_category_code= rt.revenue_category_code);
1917:
1918: IF g1_debug_mode = 'Y' THEN
1919: pa_retention_util.write_log('ERDL: No Records Update At Event Type Level ' ||sql%rowcount);
1920: END IF;
1921: -- 2. Update the Revenue Category Level Retention Setup in ERDLs
1922:
1923: UPDATE pa_cust_event_rdl_all erdl
1960: AND erdl.draft_invoice_num= ProjInvRec.draft_invoice_num
1961: AND evttyp.revenue_category_code= rt.revenue_category_code);
1962:
1963: IF g1_debug_mode = 'Y' THEN
1964: pa_retention_util.write_log('ERDL: No Records Update At Revenue Category Level ' ||sql%rowcount);
1965: END IF;
1966: -- 3. Update the Project Level Retention Setup in ERDLs
1967:
1968: UPDATE pa_cust_event_rdl_all erdl
1998: AND erdl.draft_invoice_num=ProjInvRec.draft_invoice_num
1999: AND trunc(evt.completion_date) BETWEEN
2000: rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1));
2001: IF g1_debug_mode = 'Y' THEN
2002: pa_retention_util.write_log('ERDL: No Records Update At Project Level ' ||sql%rowcount);
2003: END IF;
2004: -- 1. Update the Event Type Level Retention Setup in DII only for EVENTS
2005: /* Bug 3258414: The update statements below are done only for Events of type MANUAL or AUTOMATIC.
2006: The WRITE ON events are already updated using the ERDL table above. */
2054: AND dii.event_num is NOT NULL
2055: AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2056:
2057: IF g1_debug_mode = 'Y' THEN
2058: pa_retention_util.write_log('DII: No Records Update At Event Type Level ' ||sql%rowcount);
2059: END IF;
2060:
2061: -- 2. Update the Revenue Category Level Retention Setup in DII for events
2062:
2107: AND dii.event_num IS NOT NULL
2108: AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2109:
2110: IF g1_debug_mode = 'Y' THEN
2111: pa_retention_util.write_log('DII: No Records Update At Revenue Category Level ' ||sql%rowcount);
2112: END IF;
2113:
2114: -- 3. Update the Project Level Retention Setup in DIIs for Events
2115: IF g1_debug_mode = 'Y' THEN
2112: END IF;
2113:
2114: -- 3. Update the Project Level Retention Setup in DIIs for Events
2115: IF g1_debug_mode = 'Y' THEN
2116: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'PROJECT Defaults Level ');
2117: END IF;
2118:
2119: UPDATE pa_draft_invoice_items dii
2120: SET dii.retention_rule_id=
2161: AND evttyp.event_type_classification <> 'WRITE ON'
2162: AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2163:
2164: IF g1_debug_mode = 'Y' THEN
2165: pa_retention_util.write_log('DII: No Records Update At Project Level ' ||sql%rowcount);
2166: END IF;
2167:
2168: ELSIF ProjCustRec.retention_level ='TOP_TASK' THEN
2169:
2167:
2168: ELSIF ProjCustRec.retention_level ='TOP_TASK' THEN
2169:
2170: IF g1_debug_mode = 'Y' THEN
2171: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'TOP_TASK Level ');
2172: END IF;
2173:
2174: -- 1. Update the Non Labor Resource Override in RDLs
2175:
2210: AND ei.non_labor_resource = rt.non_labor_resource
2211: AND trunc(ei.expenditure_item_date) BETWEEN
2212: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2213: IF g1_debug_mode = 'Y' THEN
2214: pa_retention_util.write_log('RDL: No Records Update At Non Labor Level ' ||sql%rowcount);
2215: END IF;
2216: -- 2. Update the Expenditure Type Level Override in RDLs
2217:
2218: UPDATE pa_cust_rev_dist_lines_all rdl
2253: AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2254: AND trunc(ei.expenditure_item_date) BETWEEN
2255: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2256: IF g1_debug_mode = 'Y' THEN
2257: pa_retention_util.write_log('RDL: No Records Update At Expenditure Type Level ' ||sql%rowcount);
2258: END IF;
2259:
2260: -- 3. Update the Expenditure Category Level Override in RDLs
2261:
2303: AND rdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2304: AND trunc(ei.expenditure_item_date) BETWEEN
2305: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2306: IF g1_debug_mode = 'Y' THEN
2307: pa_retention_util.write_log('RDL: No Records Update At Expenditure Category Level ' ||sql%rowcount);
2308: END IF;
2309: -- 4. Update the Project Level Retention Setup in RDLs
2310:
2311: UPDATE pa_cust_rev_dist_lines_all rdl
2344: AND trunc(ei.expenditure_item_date) BETWEEN
2345: rt.effective_start_date and NVL(rt.effective_end_date,ei.expenditure_item_date+1));
2346:
2347: IF g1_debug_mode = 'Y' THEN
2348: pa_retention_util.write_log('RDL: No Records Update At Project Level ' ||sql%rowcount);
2349: END IF;
2350:
2351: -- 1. Update the Event Type Level Retention Setup in ERDLs
2352:
2399: and erdl.draft_invoice_num = ProjInvRec.draft_invoice_num
2400: AND evttyp.revenue_category_code= rt.revenue_category_code);
2401:
2402: IF g1_debug_mode = 'Y' THEN
2403: pa_retention_util.write_log('ERDL: No Records Update At Event Type Level ' ||sql%rowcount);
2404: END IF;
2405:
2406: -- 2. Update the Revenue Category Level Retention Setup in ERDLs
2407:
2452: AND evttyp.revenue_category_code= rt.revenue_category_code);
2453:
2454:
2455: IF g1_debug_mode = 'Y' THEN
2456: pa_retention_util.write_log('ERDL: No Records Update At Revenue Category Level ' ||sql%rowcount);
2457: END IF;
2458:
2459: -- 3. Update the Top Task Level Retention Setup in ERDLs
2460:
2498: AND trunc(evt.completion_date) BETWEEN
2499: rt.effective_start_date and NVL(rt.effective_end_date,evt.completion_date+1));
2500:
2501: IF g1_debug_mode = 'Y' THEN
2502: pa_retention_util.write_log('ERDL: No Records Update At Project Level ' ||sql%rowcount);
2503: END IF;
2504:
2505:
2506: -- 1. Update the Event Type Level Retention Setup in DII only for EVENTS
2560: AND dii.event_num is NOT NULL
2561: AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num)
2562: ;
2563: IF g1_debug_mode = 'Y' THEN
2564: pa_retention_util.write_log('DII: No Records Update At Event Type Level ' ||sql%rowcount);
2565: END IF;
2566:
2567: -- 2. Update the Revenue Category Level Retention Setup in DII for events
2568:
2619: AND dii.event_num IS NOT NULL
2620: AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2621:
2622: IF g1_debug_mode = 'Y' THEN
2623: pa_retention_util.write_log('DII: No Records Update At Revenue Category Level ' ||sql%rowcount);
2624: END IF;
2625:
2626:
2627: -- 3. Update the Top Task Level Retention Setup in DIIs for Events
2677: AND evttyp.event_type_classification <> 'WRITE ON'
2678: AND dii.draft_invoice_num=ProjInvRec.draft_invoice_num);
2679:
2680: IF g1_debug_mode = 'Y' THEN
2681: pa_retention_util.write_log('DII: No Records Update At Project Level ' ||sql%rowcount);
2682: END IF;
2683:
2684: END IF;
2685:
2683:
2684: END IF;
2685:
2686: IF g1_debug_mode = 'Y' THEN
2687: pa_retention_util.write_log('proj_invoice_retn_processing: ' || 'Calling Get_NetZero_Line');
2688: END IF;
2689:
2690: /* Since net zero line comes up before retention line, net zero line num is cached here.
2691: After retention lines are generated , last line num is updated onto this cached line */
2693: NetZeroLineNum:= Get_NetZero_Line(p_project_id => p_project_id,
2694: p_draft_invoice_num=>draftinvoicenum);
2695:
2696: IF g1_debug_mode = 'Y' THEN
2697: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'NetZero_Line : ' || NetZeroLineNum);
2698: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Call Create_Proj_Inv_Retn_Lines ');
2699: END IF;
2700: Create_Proj_Inv_Retn_Lines(p_project_id=>p_project_id,
2701: p_customer_id=>CustomerId,
2694: p_draft_invoice_num=>draftinvoicenum);
2695:
2696: IF g1_debug_mode = 'Y' THEN
2697: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'NetZero_Line : ' || NetZeroLineNum);
2698: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Call Create_Proj_Inv_Retn_Lines ');
2699: END IF;
2700: Create_Proj_Inv_Retn_Lines(p_project_id=>p_project_id,
2701: p_customer_id=>CustomerId,
2702: p_agreement_id=>ProjInvRec.agreement_id,
2739:
2740: IF ProjInvRec.inv_by_bill_trans_curr_flag ='Y' THEN
2741:
2742: IF g1_debug_mode = 'Y' THEN
2743: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Call Update_Retn_Bill_Trans_Amount ');
2744: END IF;
2745:
2746: Update_Retn_Bill_Trans_Amount(p_project_id=>p_project_id,
2747: p_draft_invoice_num =>DraftInvoiceNum,
2756:
2757: END LOOP;
2758:
2759: IF g1_debug_mode = 'Y' THEN
2760: pa_retention_util.write_log('Leaving Proj_Invoice_Retn_Processing ');
2761: END IF;
2762:
2763: CLOSE cur_proj_cust;
2764: EXCEPTION
2763: CLOSE cur_proj_cust;
2764: EXCEPTION
2765: WHEN OTHERS THEN
2766: IF g1_debug_mode = 'Y' THEN
2767: pa_retention_util.write_log('Proj_Invoice_Retn_Processing: ' || 'Oracle Error ' || sqlerrm);
2768: END IF;
2769: RAISE;
2770:
2771: END Proj_Invoice_Retn_Processing;
2842: l_retention_percentage NUMBER:=0;
2843:
2844: BEGIN
2845: IF g1_debug_mode = 'Y' THEN
2846: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || ' Processing Credit Memos ');
2847: END IF;
2848:
2849: OPEN cur_cm_invoice;
2850: LOOP
2854: l_credit_invoice_num := CmInvoiceRec.credit_invoice_num;
2855: l_org_invoice_num := CmInvoiceRec.org_invoice_num;
2856:
2857: IF g1_debug_mode = 'Y' THEN
2858: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Invoice :' || l_credit_invoice_num);
2859: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Old Orginal Invoice :' || l_org_invoice_num);
2860: END IF;
2861:
2862: -- This percentage will be used for old Rdls
2855: l_org_invoice_num := CmInvoiceRec.org_invoice_num;
2856:
2857: IF g1_debug_mode = 'Y' THEN
2858: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Invoice :' || l_credit_invoice_num);
2859: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Old Orginal Invoice :' || l_org_invoice_num);
2860: END IF;
2861:
2862: -- This percentage will be used for old Rdls
2863:
2875: LastLineNum := Get_Invoice_Max_Line(p_project_id=>p_project_id,
2876: p_draft_invoice_num=>l_credit_invoice_num);
2877:
2878: IF g1_debug_mode = 'Y' THEN
2879: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line :' || LastLineNum);
2880: END IF;
2881:
2882: IF CreditMemoRec.retention_rule_id = -1 THEN
2883:
2881:
2882: IF CreditMemoRec.retention_rule_id = -1 THEN
2883:
2884: IF g1_debug_mode = 'Y' THEN
2885: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || ' For Old RDLS use the old retn pct :' || l_retention_percentage);
2886: END IF;
2887:
2888: CreditMemoRec.Invoice_amount := NVL( CreditMemoRec.Invoice_amount,0) * nvl(l_retention_percentage,0);
2889:
2977: AND dii.invoice_line_type ='RETENTION'
2978: ) LOOP
2979:
2980: IF g1_debug_mode = 'Y' THEN
2981: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line RuleId :' || CreditMemoRec.Retention_rule_id);
2982: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Orgional Invoice :' || l_org_invoice_num);
2983: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || InvoiceLines.line_num_credited);
2984: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Invoice :' || l_credit_invoice_num);
2985: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || lastlinenum);
2978: ) LOOP
2979:
2980: IF g1_debug_mode = 'Y' THEN
2981: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line RuleId :' || CreditMemoRec.Retention_rule_id);
2982: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Orgional Invoice :' || l_org_invoice_num);
2983: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || InvoiceLines.line_num_credited);
2984: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Invoice :' || l_credit_invoice_num);
2985: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || lastlinenum);
2986: END IF;
2979:
2980: IF g1_debug_mode = 'Y' THEN
2981: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line RuleId :' || CreditMemoRec.Retention_rule_id);
2982: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Orgional Invoice :' || l_org_invoice_num);
2983: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || InvoiceLines.line_num_credited);
2984: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Invoice :' || l_credit_invoice_num);
2985: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || lastlinenum);
2986: END IF;
2987:
2980: IF g1_debug_mode = 'Y' THEN
2981: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line RuleId :' || CreditMemoRec.Retention_rule_id);
2982: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Orgional Invoice :' || l_org_invoice_num);
2983: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || InvoiceLines.line_num_credited);
2984: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Invoice :' || l_credit_invoice_num);
2985: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || lastlinenum);
2986: END IF;
2987:
2988: INSERT INTO pa_draft_invoice_items
2981: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Credit Memo Retn Line RuleId :' || CreditMemoRec.Retention_rule_id);
2982: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Orgional Invoice :' || l_org_invoice_num);
2983: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || InvoiceLines.line_num_credited);
2984: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'New Invoice :' || l_credit_invoice_num);
2985: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Retn Line Credited :' || lastlinenum);
2986: END IF;
2987:
2988: INSERT INTO pa_draft_invoice_items
2989: ( PROJECT_ID, DRAFT_INVOICE_NUM, LINE_NUM,
3033:
3034: IF NVL(InvoiceLines.retention_rule_id,-1) <> -1 THEN
3035:
3036: IF g1_debug_mode = 'Y' THEN
3037: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Calling Update_Retention_Balances ');
3038: END IF;
3039:
3040: Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id ,
3041: p_project_id =>p_project_id ,
3054: p_funding_amount =>InvoiceLines.funding_bill_amount);
3055:
3056:
3057: IF g1_debug_mode = 'Y' THEN
3058: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Update RDL with new Retention Line Number');
3059: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Invoice Number : ' || l_credit_invoice_num);
3060: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Rule Id : ' ||
3061: InvoiceLines.Retention_rule_id);
3062: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Project Id : ' || p_project_id);
3055:
3056:
3057: IF g1_debug_mode = 'Y' THEN
3058: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Update RDL with new Retention Line Number');
3059: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Invoice Number : ' || l_credit_invoice_num);
3060: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Rule Id : ' ||
3061: InvoiceLines.Retention_rule_id);
3062: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Project Id : ' || p_project_id);
3063: END IF;
3056:
3057: IF g1_debug_mode = 'Y' THEN
3058: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Update RDL with new Retention Line Number');
3059: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Invoice Number : ' || l_credit_invoice_num);
3060: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Rule Id : ' ||
3061: InvoiceLines.Retention_rule_id);
3062: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Project Id : ' || p_project_id);
3063: END IF;
3064:
3058: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Update RDL with new Retention Line Number');
3059: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Invoice Number : ' || l_credit_invoice_num);
3060: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Rule Id : ' ||
3061: InvoiceLines.Retention_rule_id);
3062: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Project Id : ' || p_project_id);
3063: END IF;
3064:
3065: -- Update the RDL
3066:
3071: AND draft_invoice_num = l_credit_invoice_num
3072: AND project_id = p_project_id;
3073:
3074: IF g1_debug_mode = 'Y' THEN
3075: pa_retention_util.write_log('RDL Number of rows updated : ' || sql%rowcount);
3076: END IF;
3077:
3078: -- Update the DII - Added for bug 3958970
3079:
3085: AND project_id = p_project_id
3086: AND invoice_line_type <> 'RETENTION';
3087:
3088: IF g1_debug_mode = 'Y' THEN
3089: pa_retention_util.write_log('DII Number of rows updated : ' || sql%rowcount);
3090: END IF;
3091: END IF;
3092:
3093:
3105: EXCEPTION
3106: WHEN OTHERS THEN
3107: x_return_status :='E';
3108: IF g1_debug_mode = 'Y' THEN
3109: pa_retention_util.write_log('Proj_Invoice_Credit_Memo: ' || 'Oracle Error ' || sqlerrm);
3110: END IF;
3111: RAISE;
3112:
3113: END Proj_Invoice_Credit_Memo;
3162: FROM pa_draft_invoices_all p
3163: WHERE p.project_id = p_project_id;
3164:
3165: IF g1_debug_mode = 'Y' THEN
3166: pa_retention_util.write_log('Invoice_Cancel_Action: ' || 'New Invoice Num : ' || TmpInvoiceNum);
3167: END IF;
3168:
3169: -- Copy the Header as it is
3170: INSERT INTO pa_draft_invoices_all
3501: TmpChangeType VARCHAR2(15);
3502:
3503: BEGIN
3504: IF g1_debug_mode = 'Y' THEN
3505: pa_retention_util.write_log('Invoice_Delete_Action ');
3506: END IF;
3507:
3508: IF p_invoice_type ='PROJECT_INVOICE' THEN
3509:
3515:
3516: END IF;
3517:
3518: IF g1_debug_mode = 'Y' THEN
3519: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Change Type : ' || tmpChangeType);
3520: END IF;
3521:
3522: FOR InvoiceLines IN (SELECT
3523: decode(tmpChangeType,'BILLED', -1 * dii.amount ,
3549: AND dii.retention_rule_id = rtn.retention_rule_id (+)
3550: AND dii.invoice_line_type='RETENTION') LOOP
3551:
3552: IF g1_debug_mode = 'Y' THEN
3553: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Number : ' || p_draft_invoice_num);
3554: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
3555: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3550: AND dii.invoice_line_type='RETENTION') LOOP
3551:
3552: IF g1_debug_mode = 'Y' THEN
3553: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Number : ' || p_draft_invoice_num);
3554: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
3555: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3558: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
3551:
3552: IF g1_debug_mode = 'Y' THEN
3553: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Number : ' || p_draft_invoice_num);
3554: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
3555: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3558: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
3559: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'FC Invoice Amount : ' || InvoiceLines.funding_bill_amount);
3552: IF g1_debug_mode = 'Y' THEN
3553: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Number : ' || p_draft_invoice_num);
3554: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
3555: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3558: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
3559: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'FC Invoice Amount : ' || InvoiceLines.funding_bill_amount);
3560: END IF;
3553: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Number : ' || p_draft_invoice_num);
3554: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
3555: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3558: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
3559: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'FC Invoice Amount : ' || InvoiceLines.funding_bill_amount);
3560: END IF;
3561:
3554: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Project Id : ' || p_project_id);
3555: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3558: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
3559: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'FC Invoice Amount : ' || InvoiceLines.funding_bill_amount);
3560: END IF;
3561:
3562:
3555: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Retention Rule : ' || InvoiceLines.Retention_rule_id);
3556: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Invoice Amount : ' || InvoiceLines.amount);
3557: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PFC Invoice Amount : ' || InvoiceLines.projfunc_bill_amount);
3558: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'PC Invoice Amount : ' || InvoiceLines.project_bill_amount);
3559: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'FC Invoice Amount : ' || InvoiceLines.funding_bill_amount);
3560: END IF;
3561:
3562:
3563: Update_Retention_Balances(p_retention_rule_id =>InvoiceLines.Retention_rule_id,
3582:
3583: -- Delete the retention invoice details
3584:
3585: IF g1_debug_mode = 'Y' THEN
3586: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Delete Retention Invoice Details ');
3587: END IF;
3588:
3589: DELETE FROM pa_retn_invoice_details
3590: WHERE project_id = p_project_id
3591: AND draft_invoice_num = p_draft_invoice_num;
3592:
3593: -- Call to delete the mc records
3594: IF g1_debug_mode = 'Y' THEN
3595: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Delete MRC Retention Invoice Details ');
3596: END IF;
3597: PA_MC_RETN_INV_DETAIL_PKG.Process_RetnInvDetails(
3598: p_project_id=>p_project_id,
3599: p_draft_invoice_num=>p_draft_invoice_num,
3608: EXCEPTION
3609: WHEN OTHERS THEN
3610: x_return_status := 'E';
3611: IF g1_debug_mode = 'Y' THEN
3612: pa_retention_util.write_log('Invoice_Delete_Action: ' || 'Oracle Error ' || sqlerrm);
3613: END IF;
3614: RAISE;
3615:
3616: END Invoice_Delete_Action;
3754: EXCEPTION
3755: WHEN OTHERS THEN
3756: x_return_status := 'E';
3757: IF g1_debug_mode = 'Y' THEN
3758: pa_retention_util.write_log('Retention_Delete_Action: ' || 'Oracle Error ' || sqlerrm);
3759: END IF;
3760: RAISE;
3761:
3762: END Delete_Unused_Retention_Lines;