DBA Data[Home] [Help]

PACKAGE BODY: APPS.PA_RETENTION_PKG

Source


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