[Home] [Help]
PACKAGE BODY: APPS.PA_TOP_TASK_CUST_INVOICE_PVT
Source
1 PACKAGE BODY PA_TOP_TASK_CUST_INVOICE_PVT AS
2 /* $Header: PATOPCIB.pls 120.3 2007/02/06 10:05:38 dthakker ship $ */
3
4 g_module_name VARCHAR2(100) := 'PA_TOP_TASK_CUST_INVOICE_PVT';
5 Invalid_Arg_Exc EXCEPTION;
6
7 -- Procedure : enbl_disbl_cust_at_top_task
8 -- Type : PRIVATE
9 -- Purpose : Includes logic for updating various tables depending on whether the
10 -- 'Customer at Top Task' flag is disabled or enabled
11 -- Note :
12 -- Assumptions :
13 -- Parameters Type Required Description and Purpose
14 -- --------------------------- ------ -------- --------------------------------------------------------
15 -- p_mode VARCHAR2 Y Describes whether flag is being disabled or enabled
16 -- p_project_id NUMBER Y Gives the Project id
17 -- p_def_top_task_cust NUMBER N The customer to be set as default top task customer
18 -- p_contr_update_cust NUMBER N The customer to be updated with 100% contribution
19 PROCEDURE enbl_disbl_cust_at_top_task(
20 p_api_version IN NUMBER := 1.0
21 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
22 , p_commit IN VARCHAR2 := FND_API.G_FALSE
23 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
24 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
25 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
26 , p_debug_mode IN VARCHAR2 := 'N'
27 , p_mode IN VARCHAR2
28 , p_project_id IN NUMBER
29 , p_def_top_task_cust IN NUMBER
30 , p_contr_update_cust IN NUMBER
31 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865 Added the nocopy hint
32 , x_msg_count OUT NOCOPY NUMBER -- 4537865 Added the nocopy hint
33 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865 Added the nocopy hint
34 ) IS
35
36 l_msg_count NUMBER := 0;
37 l_debug_mode VARCHAR2(1);
38 l_data VARCHAR2(2000);
39 l_msg_data VARCHAR2(2000);
40 l_msg_index_out NUMBER;
41
42 l_debug_level2 CONSTANT NUMBER := 2;
43 l_debug_level3 CONSTANT NUMBER := 3;
44 l_debug_level4 CONSTANT NUMBER := 4;
45 l_debug_level5 CONSTANT NUMBER := 5;
46
47
48 --sunkalya:federal Bug#5511353
49
50 l_orig_date_eff_funds_flag VARCHAR2(1);
51
52 CURSOR get_date_eff_funds_flag( c_project_id IN NUMBER )
53 IS
54 SELECT
55 nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
56 FROM
57 pa_projects_all
58 WHERE project_id = c_project_id ;
59
60 --sunkalya:federal Bug#5511353
61
62 BEGIN
63
64 x_msg_count := 0;
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
67
68 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72 IF (p_commit = FND_API.G_TRUE) THEN
73 savepoint en_db_cust_at_top_task_pub;
74 END IF;
75
76 IF l_debug_mode = 'Y' THEN
77 PA_DEBUG.set_curr_function( p_function => 'enbl_disbl_cust_at_top_task',
78 p_debug_mode => l_debug_mode );
79 END IF;
80
81 IF l_debug_mode = 'Y' THEN
82 Pa_Debug.g_err_stage:= 'Printing Input parameters';
83 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
84 l_debug_level3);
85
86 Pa_Debug.WRITE(g_module_name,'p_mode'||':'||p_mode,
87 l_debug_level3);
88
89 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
90 l_debug_level3);
91
92 Pa_Debug.WRITE(g_module_name,'p_def_top_task_cust'||':'||p_def_top_task_cust,
93 l_debug_level3);
94
95 Pa_Debug.WRITE(g_module_name,'p_contr_update_cust'||':'||p_contr_update_cust,
96 l_debug_level3);
97 END IF;
98
99 IF l_debug_mode = 'Y' THEN
100 Pa_Debug.g_err_stage:= 'Validating Input parameters';
101 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
102 l_debug_level3);
103 END IF;
104
105 IF ( ( p_mode IS NULL OR p_mode = FND_API.G_MISS_CHAR ) AND
106 ( p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM ) AND
107 ( p_def_top_task_cust IS NULL OR p_def_top_task_cust = FND_API.G_MISS_NUM ) AND
108 ( p_contr_update_cust IS NULL OR p_contr_update_cust = FND_API.G_MISS_NUM )
109 )
110 THEN
111 IF l_debug_mode = 'Y' THEN
112 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : enbl_disbl_cust_at_top_task :
113 p_mode, p_project_id, p_def_top_task_cust, p_contr_update_cust are NULL';
114 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
115 l_debug_level3);
116 END IF;
117 RAISE Invalid_Arg_Exc;
118 END IF;
119
120 IF ( (p_mode IS NOT NULL AND p_mode <> FND_API.G_MISS_CHAR) AND
121 (p_project_id IS NOT NULL AND p_project_id <> FND_API.G_MISS_NUM )
122 ) THEN
123 IF p_mode = 'DISABLE' THEN
124
125
126 UPDATE pa_tasks SET customer_id = null
127 WHERE project_id = p_project_id;
128
129 UPDATE pa_project_customers SET default_top_task_cust_flag = 'N'
130 WHERE customer_id = p_def_top_task_cust AND project_id = p_project_id;
131
132 --sunkalya:federal Bug#5511353
133 --added the below logic as changing customer bill split
134 --depends now on date_eff_fields_consumption flag also. Sunkalya federal Bug#5511353
135
136 OPEN get_date_eff_funds_flag(p_project_id);
137 FETCH get_date_eff_funds_flag INTO l_orig_date_eff_funds_flag;
138 CLOSE get_date_eff_funds_flag;
139
140 --sunkalya:federal Bug#5511353
141
142 IF l_orig_date_eff_funds_flag = 'N' THEN --sunkalya:federal Bug#5511353
143
144 UPDATE pa_project_customers SET customer_bill_split = 100
145 WHERE customer_id = p_contr_update_cust AND project_id = p_project_id;
146
147 UPDATE pa_project_customers SET customer_bill_split = 0
148 WHERE customer_id <> p_contr_update_cust AND project_id = p_project_id;
149
150 END IF;
151
152 ELSIF p_mode = 'ENABLE' THEN
153 UPDATE pa_tasks SET customer_id = p_def_top_task_cust
154 WHERE project_id = p_project_id;
155
156 UPDATE pa_project_customers SET default_top_task_cust_flag = 'Y'
157 WHERE customer_id = p_def_top_task_cust AND project_id = p_project_id;
158
159 UPDATE pa_project_customers SET customer_bill_split = null
160 WHERE project_id = p_project_id;
161
162 --Commented out call below for bug 3882790
163 --Project_funding_level_flag is now set in project form itself
164 /*IF l_debug_mode = 'Y' THEN
165 Pa_Debug.g_err_stage:= 'Going to set project funding flag';
166 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
167 l_debug_level3);
168 END IF;
169
170 PA_TOP_TASK_CUST_INVOICE_PVT.set_top_task_funding_level(
171 p_api_version => p_api_version
172 , p_init_msg_list => FND_API.G_FALSE
173 , p_commit => p_commit
174 , p_validate_only => p_validate_only
175 , p_validation_level => p_validation_level
176 , p_calling_module => p_calling_module
177 , p_project_id => p_project_id
178 , x_return_status => x_return_status
179 , x_msg_count => x_msg_count
180 , x_msg_data => x_msg_data
181 );
182
183 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;*/
186
187 END IF;
188 ELSE
189 IF l_debug_mode = 'Y' THEN
190 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : enbl_disbl_cust_at_top_task :
191 Mandatory parameters p_mode or p_project_id are NULL';
192 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
193 l_debug_level3);
194 END IF;
195 RAISE Invalid_Arg_Exc;
196 END IF;
197
198 IF (p_commit = FND_API.G_TRUE) THEN
199 COMMIT;
200 END IF;
201
202 EXCEPTION
203
204 WHEN FND_API.G_EXC_ERROR THEN
205
206 x_return_status := Fnd_Api.G_RET_STS_ERROR;
207
208 IF p_commit = FND_API.G_TRUE THEN
209 ROLLBACK TO en_db_cust_at_top_task_pub;
210 END IF;
211
212 l_msg_count := Fnd_Msg_Pub.count_msg;
213 IF l_msg_count = 1 AND x_msg_data IS NULL
214 THEN
215 Pa_Interface_Utils_Pub.get_messages
216 ( p_encoded => Fnd_Api.G_FALSE
217 , p_msg_index => 1
218 , p_msg_count => l_msg_count
219 , p_msg_data => l_msg_data
220 , p_data => l_data
221 , p_msg_index_out => l_msg_index_out);
222 x_msg_data := l_data;
223 x_msg_count := l_msg_count;
224 ELSE
225 x_msg_count := l_msg_count;
226 END IF;
227
228 IF l_debug_mode = 'Y' THEN
229 Pa_Debug.reset_curr_function;
230 END IF;
231
232 WHEN Invalid_Arg_Exc THEN
233 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
234 x_msg_count := 1;
235 x_msg_data := ' PA_TOP_TASK_CUST_INVOICE_PVT : enbl_disbl_cust_at_top_task : NULL parameters passed';
236
237 IF p_commit = FND_API.G_TRUE THEN
238 ROLLBACK TO en_db_cust_at_top_task_pub;
239 END IF;
240
241 Fnd_Msg_Pub.add_exc_msg
242 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
243 , p_procedure_name => 'enbl_disbl_cust_at_top_task'
244 , p_error_text => x_msg_data);
245
246 IF l_debug_mode = 'Y' THEN
247 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
248 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
249 l_debug_level5);
250 Pa_Debug.reset_curr_function;
251 END IF;
252 RAISE;
253
254 WHEN OTHERS THEN
255 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
256 x_msg_count := 1;
257 x_msg_data := SQLERRM;
258
259 IF p_commit = FND_API.G_TRUE THEN
260 ROLLBACK TO en_db_cust_at_top_task_pub;
261 END IF;
262
263 Fnd_Msg_Pub.add_exc_msg
264 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
265 , p_procedure_name => 'enbl_disbl_cust_at_top_task'
266 , p_error_text => x_msg_data);
267
268 IF l_debug_mode = 'Y' THEN
269 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
270 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
271 l_debug_level5);
272 Pa_Debug.reset_curr_function;
273 END IF;
274 RAISE;
275
276 END enbl_disbl_cust_at_top_task;
277
278
279
280 -- Procedure : Get_Highest_Contr_Cust
281 -- Type : PRIVATE
282 -- Purpose : Gets the highest contribution customer. If contribution is same,
283 -- then sorts on name and if names are also same, then sorts on
284 -- customer id. It will not return the customers that are included in the
285 -- p_exclude_cust_id_tbl PL/SQL table.
286 -- Note :
287 -- Assumptions :
288 -- Parameters Type Required Description and Purpose
289 -- --------------------------- ------ -------- --------------------------------------------------------
290 -- p_project_id NUMBER Y Project ID for which highest contribution customer is
291 -- to be returned
292 -- p_exclude_cust_id_tbl SYSTEM.PA_NUM_TBL_TYPE N Customer IDs to be excluded while fetching highest contrib
293 -- customer (Required during customer deletion)
294 -- x_highst_contr_cust_id NUMBER N Customer ID of the highest contribution customer
295 -- x_highst_contr_cust_name VARCHAR2 N Customer Name of the highest contribution customer
296 -- x_highst_contr_cust_num VARCHAR2 N Customer Number of the highest contribution customer
297 PROCEDURE Get_Highest_Contr_Cust(
298 p_api_version IN NUMBER := 1.0
299 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
300 , p_commit IN VARCHAR2 := FND_API.G_FALSE
301 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
302 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
303 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
304 , p_debug_mode IN VARCHAR2 := 'N'
305 , p_project_id IN NUMBER
306 , p_exclude_cust_id_tbl IN PA_PLSQL_DATATYPES.NumTabTyp
307 , x_highst_contr_cust_id OUT NOCOPY NUMBER -- 4537865 Added the nocopy hint
308 , x_highst_contr_cust_name OUT NOCOPY VARCHAR2 -- 4537865 Added the nocopy hint
309 , x_highst_contr_cust_num OUT NOCOPY VARCHAR2 -- 4537865 Added the nocopy hint
310 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865 Added the nocopy hint
311 , x_msg_count OUT NOCOPY NUMBER -- 4537865 Added the nocopy hint
312 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865 Added the nocopy hint
313 ) IS
314
315 l_msg_count NUMBER := 0;
316 l_debug_mode VARCHAR2(1);
317 l_data VARCHAR2(2000);
318 l_msg_data VARCHAR2(2000);
319 l_msg_index_out NUMBER;
320
321 l_debug_level2 CONSTANT NUMBER := 2;
322 l_debug_level3 CONSTANT NUMBER := 3;
323 l_debug_level4 CONSTANT NUMBER := 4;
324 l_debug_level5 CONSTANT NUMBER := 5;
325
326 l_return_cust_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
327 l_return_cust_name_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE;
328 l_return_cust_num_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
329 l_return_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE;
330
331 /* Changes for TCA
332 CURSOR cur_get_ordered_customers IS
333 SELECT ra_cust.customer_id , ra_cust.customer_name , ra_cust.customer_number, 'Y'
334 FROM pa_project_customers proj_cust, ra_customers ra_cust
335 WHERE proj_cust.project_id = p_project_id
336 AND proj_cust.customer_id = ra_cust.customer_id
337 ORDER BY proj_cust.customer_bill_split desc, ra_cust.customer_name, ra_cust.customer_number ;
338 */
339
340 CURSOR cur_get_ordered_customers IS
341 SELECT HZ_C.cust_account_id , HZ_P.party_name , HZ_P.party_number, 'Y'
342 FROM pa_project_customers proj_cust, hz_cust_accounts HZ_C, HZ_PARTIES HZ_P
343 WHERE proj_cust.project_id = p_project_id
344 AND proj_cust.customer_id = hz_c.cust_account_id
345 AND hz_c.party_id = hz_p.party_id
346 ORDER BY proj_cust.customer_bill_split desc, hz_p.party_name, HZ_P.party_number ;
347
348 BEGIN
349
350 x_highst_contr_cust_id := null;
351 x_highst_contr_cust_name := null;
352 x_highst_contr_cust_num := null;
353
354 x_msg_count := 0;
355 x_return_status := FND_API.G_RET_STS_SUCCESS;
356 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
357
358 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
359 FND_MSG_PUB.initialize;
360 END IF;
361
362 IF (p_commit = FND_API.G_TRUE) THEN
363 savepoint get_highest_cont_cust_svpt;
364 END IF;
365
366 IF l_debug_mode = 'Y' THEN
367 PA_DEBUG.set_curr_function( p_function => 'Get_Highest_Contr_Cust',
368 p_debug_mode => l_debug_mode );
369 END IF;
370
371 IF l_debug_mode = 'Y' THEN
372 Pa_Debug.g_err_stage:= 'Printing Input parameters';
373 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
374 l_debug_level3);
375
376 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
377 l_debug_level3);
378
379 END IF;
380
381 IF l_debug_mode = 'Y' THEN
382 Pa_Debug.g_err_stage:= 'Validating Input parameters';
383 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
384 l_debug_level3);
385 END IF;
386
387 IF ( p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM
388 )
389 THEN
390 IF l_debug_mode = 'Y' THEN
391 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : Get_Highest_Contr_Cust :
392 p_project_id is NULL';
393 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
394 l_debug_level3);
395 END IF;
396 RAISE Invalid_Arg_Exc;
397 END IF;
398
399 OPEN cur_get_ordered_customers;
400 FETCH cur_get_ordered_customers BULK COLLECT INTO l_return_cust_id_tbl, l_return_cust_name_tbl, l_return_cust_num_tbl, l_return_flag_tbl;
401 CLOSE cur_get_ordered_customers;
402
403 --If the return table is not NULL
404 IF nvl(l_return_flag_tbl.LAST,0) > 0 THEN
405 --If the exclude customer ids table passed is not NULL
406 IF nvl(p_exclude_cust_id_tbl.LAST,0) > 0 THEN
407 --Mark return flag as 'N' for all 'exclude customer ids' found in the return result set
408 FOR i IN p_exclude_cust_id_tbl.FIRST..p_exclude_cust_id_tbl.LAST LOOP
409 FOR j IN l_return_cust_id_tbl.FIRST..l_return_cust_id_tbl.LAST LOOP
410 IF p_exclude_cust_id_tbl(i) = l_return_cust_id_tbl(j) THEN
411 l_return_flag_tbl(j) := 'N';
412 END IF;
413 END LOOP;
414 END LOOP;
415 END IF;
416
417 --Return the first record that has return flag as 'Y'
418 FOR i IN l_return_flag_tbl.FIRST..l_return_flag_tbl.LAST LOOP
419 IF l_return_flag_tbl(i) = 'Y' THEN
420 x_highst_contr_cust_id := l_return_cust_id_tbl(i);
421 x_highst_contr_cust_name := l_return_cust_name_tbl(i);
422 x_highst_contr_cust_num := l_return_cust_num_tbl(i);
423 EXIT;
424 END IF;
425 END LOOP;
426 END IF;
427
428 EXCEPTION
429
430 WHEN FND_API.G_EXC_ERROR THEN
431
432 x_return_status := Fnd_Api.G_RET_STS_ERROR;
433
434 -- 4537865 RESET OUT PARAMS
435 x_highst_contr_cust_id := null;
436 x_highst_contr_cust_name := null;
437 x_highst_contr_cust_num := null;
438
439 IF p_commit = FND_API.G_TRUE THEN
440 ROLLBACK TO get_highest_cont_cust_svpt;
441 END IF;
442
443 l_msg_count := Fnd_Msg_Pub.count_msg;
444 IF l_msg_count = 1 AND x_msg_data IS NULL
445 THEN
446 Pa_Interface_Utils_Pub.get_messages
447 ( p_encoded => Fnd_Api.G_FALSE
448 , p_msg_index => 1
449 , p_msg_count => l_msg_count
450 , p_msg_data => l_msg_data
451 , p_data => l_data
452 , p_msg_index_out => l_msg_index_out);
453 x_msg_data := l_data;
454 x_msg_count := l_msg_count;
455 ELSE
456 x_msg_count := l_msg_count;
457 END IF;
458
459 IF l_debug_mode = 'Y' THEN
460 Pa_Debug.reset_curr_function;
461 END IF;
462
463 WHEN Invalid_Arg_Exc THEN
464 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
465 x_msg_count := 1;
466 x_msg_data := ' PA_TOP_TASK_CUST_INVOICE_PVT : Get_Highest_Contr_Cust : NULL parameters passed';
467
468 -- 4537865 RESET OUT PARAMS
469 x_highst_contr_cust_id := null;
470 x_highst_contr_cust_name := null;
471 x_highst_contr_cust_num := null;
472
473 IF p_commit = FND_API.G_TRUE THEN
474 ROLLBACK TO get_highest_cont_cust_svpt;
475 END IF;
476
477 Fnd_Msg_Pub.add_exc_msg
478 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
479 , p_procedure_name => 'Get_Highest_Contr_Cust'
480 , p_error_text => x_msg_data);
481
482 IF l_debug_mode = 'Y' THEN
483 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
484 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
485 l_debug_level5);
486 Pa_Debug.reset_curr_function;
487 END IF;
488 RAISE;
489
490 WHEN OTHERS THEN
491 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
492 x_msg_count := 1;
493 x_msg_data := SQLERRM;
494
495 -- 4537865 RESET OUT PARAMS
496 x_highst_contr_cust_id := null;
497 x_highst_contr_cust_name := null;
498 x_highst_contr_cust_num := null;
499
500 IF p_commit = FND_API.G_TRUE THEN
501 ROLLBACK TO get_highest_cont_cust_svpt;
502 END IF;
503
504 Fnd_Msg_Pub.add_exc_msg
505 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
506 , p_procedure_name => 'Get_Highest_Contr_Cust'
507 , p_error_text => x_msg_data);
508
509 IF l_debug_mode = 'Y' THEN
510 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
511 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
512 l_debug_level5);
513 Pa_Debug.reset_curr_function;
514 END IF;
515 RAISE;
516
517 END Get_Highest_Contr_Cust;
518
519
520
521 -- Procedure : Set_Rev_Acc_At_Top_Task
522 -- Type : PRIVATE
523 -- Purpose : To set the revenue accrual method in PA_TASKS table
524 -- Note :
525 -- Assumptions :
526 -- Parameters Type Required Description and Purpose
527 -- --------------------------- ------ -------- --------------------------------------------------------
528 -- p_project_id NUMBER Y Project ID for which revenue accrual method is to be set
529 -- p_rev_acc VARCHAR2 Y The revenue accrual method that is to be set
530 PROCEDURE Set_Rev_Acc_At_Top_Task(
531 p_api_version IN NUMBER := 1.0
532 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
533 , p_commit IN VARCHAR2 := FND_API.G_FALSE
534 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
535 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
536 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
537 , p_debug_mode IN VARCHAR2 := 'N'
538 , p_project_id IN NUMBER
539 , p_rev_acc IN VARCHAR2
540 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865
541 , x_msg_count OUT NOCOPY NUMBER -- 4537865
542 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
543 ) IS
544
545 l_msg_count NUMBER := 0;
546 l_debug_mode VARCHAR2(1);
547 l_data VARCHAR2(2000);
548 l_msg_data VARCHAR2(2000);
549 l_msg_index_out NUMBER;
550
551 l_debug_level2 CONSTANT NUMBER := 2;
552 l_debug_level3 CONSTANT NUMBER := 3;
553 l_debug_level4 CONSTANT NUMBER := 4;
554 l_debug_level5 CONSTANT NUMBER := 5;
555
556 BEGIN
557
558 x_msg_count := 0;
559 x_return_status := FND_API.G_RET_STS_SUCCESS;
560 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
561
562 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
563 FND_MSG_PUB.initialize;
564 END IF;
565
566 IF (p_commit = FND_API.G_TRUE) THEN
567 savepoint Set_Rev_Acc_At_Top_Task_svpt;
568 END IF;
569
570 IF l_debug_mode = 'Y' THEN
571 PA_DEBUG.set_curr_function( p_function => 'Set_Rev_Acc_At_Top_Task',
572 p_debug_mode => l_debug_mode );
573 END IF;
574
575 IF l_debug_mode = 'Y' THEN
576 Pa_Debug.g_err_stage:= 'Printing Input parameters';
577 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
578 l_debug_level3);
579
580 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
581 l_debug_level3);
582
583 Pa_Debug.WRITE(g_module_name,'p_rev_acc'||':'||p_rev_acc,
584 l_debug_level3);
585 END IF;
586
587 IF l_debug_mode = 'Y' THEN
588 Pa_Debug.g_err_stage:= 'Validating Input parameters';
589 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
590 l_debug_level3);
591 END IF;
592
593 IF ( (p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM) AND
594 (p_rev_acc IS NULL OR p_rev_acc = FND_API.G_MISS_CHAR)
595 )
596 THEN
597 IF l_debug_mode = 'Y' THEN
598 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : Set_Rev_Acc_At_Top_Task :
599 p_project_id and p_rev_acc are NULL';
600 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
601 l_debug_level3);
602 END IF;
603 RAISE Invalid_Arg_Exc;
604 END IF;
605
606 IF ( (p_project_id IS NOT NULL AND p_project_id <> FND_API.G_MISS_NUM) AND
607 (p_rev_acc IS NOT NULL AND p_rev_acc <> FND_API.G_MISS_CHAR)
608 ) THEN
609
610 UPDATE pa_tasks SET revenue_accrual_method = p_rev_acc
611 WHERE project_id = p_project_id;
612
613 ELSE
614 IF l_debug_mode = 'Y' THEN
615 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : Set_Rev_Acc_At_Top_Task :
616 Mandatory parameters p_project_id or p_rev_acc are NULL';
617 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
618 l_debug_level3);
619 END IF;
620 RAISE Invalid_Arg_Exc;
621 END IF;
622
623 IF (p_commit = FND_API.G_TRUE) THEN
624 COMMIT;
625 END IF;
626
627 EXCEPTION
628
629 WHEN FND_API.G_EXC_ERROR THEN
630
631 x_return_status := Fnd_Api.G_RET_STS_ERROR;
632
633 IF p_commit = FND_API.G_TRUE THEN
634 ROLLBACK TO Set_Rev_Acc_At_Top_Task_svpt;
635 END IF;
636
637 l_msg_count := Fnd_Msg_Pub.count_msg;
638 IF l_msg_count = 1 AND x_msg_data IS NULL
639 THEN
640 Pa_Interface_Utils_Pub.get_messages
641 ( p_encoded => Fnd_Api.G_FALSE
642 , p_msg_index => 1
643 , p_msg_count => l_msg_count
644 , p_msg_data => l_msg_data
645 , p_data => l_data
646 , p_msg_index_out => l_msg_index_out);
647 x_msg_data := l_data;
648 x_msg_count := l_msg_count;
649 ELSE
650 x_msg_count := l_msg_count;
651 END IF;
652
653 IF l_debug_mode = 'Y' THEN
654 Pa_Debug.reset_curr_function;
655 END IF;
656
657 WHEN Invalid_Arg_Exc THEN
658 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
659 x_msg_count := 1;
660 x_msg_data := ' PA_TOP_TASK_CUST_INVOICE_PVT : Set_Rev_Acc_At_Top_Task : NULL parameters passed';
661
662 IF p_commit = FND_API.G_TRUE THEN
663 ROLLBACK TO Set_Rev_Acc_At_Top_Task_svpt;
664 END IF;
665
666 Fnd_Msg_Pub.add_exc_msg
667 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
668 , p_procedure_name => 'Set_Rev_Acc_At_Top_Task'
669 , p_error_text => x_msg_data);
670
671 IF l_debug_mode = 'Y' THEN
672 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
673 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
674 l_debug_level5);
675 Pa_Debug.reset_curr_function;
676 END IF;
677 RAISE;
678
679 WHEN OTHERS THEN
680 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
681 x_msg_count := 1;
682 x_msg_data := SQLERRM;
683
684 IF p_commit = FND_API.G_TRUE THEN
685 ROLLBACK TO Set_Rev_Acc_At_Top_Task_svpt;
686 END IF;
687
688 Fnd_Msg_Pub.add_exc_msg
689 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
690 , p_procedure_name => 'Set_Rev_Acc_At_Top_Task'
691 , p_error_text => x_msg_data);
692
693 IF l_debug_mode = 'Y' THEN
694 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
695 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
696 l_debug_level5);
697 Pa_Debug.reset_curr_function;
698 END IF;
699 RAISE;
700
701 END Set_Rev_Acc_At_Top_Task;
702
703
704
705 -- Procedure : Set_Inv_Mth_At_Top_Task
706 -- Type : PRIVATE
707 -- Purpose : To set the invoice method in PA_TASKS table
708 -- Note :
709 -- Assumptions :
710 -- Parameters Type Required Description and Purpose
711 -- --------------------------- ------ -------- --------------------------------------------------------
712 -- p_project_id NUMBER Y Project ID for which invoice method is to be set
713 -- p_inv_mth VARCHAR2 Y The invoice method that is to be set
714 PROCEDURE Set_Inv_Mth_At_Top_Task(
715 p_api_version IN NUMBER := 1.0
716 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
717 , p_commit IN VARCHAR2 := FND_API.G_FALSE
718 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
719 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
720 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
721 , p_debug_mode IN VARCHAR2 := 'N'
722 , p_project_id IN NUMBER
723 , p_inv_mth IN VARCHAR2
724 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865
725 , x_msg_count OUT NOCOPY NUMBER -- 4537865
726 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
727 ) IS
728
729 l_msg_count NUMBER := 0;
730 l_debug_mode VARCHAR2(1);
731 l_data VARCHAR2(2000);
732 l_msg_data VARCHAR2(2000);
733 l_msg_index_out NUMBER;
734
735 l_debug_level2 CONSTANT NUMBER := 2;
736 l_debug_level3 CONSTANT NUMBER := 3;
737 l_debug_level4 CONSTANT NUMBER := 4;
738 l_debug_level5 CONSTANT NUMBER := 5;
739
740 BEGIN
741
742 x_msg_count := 0;
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
745
746 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
747 FND_MSG_PUB.initialize;
748 END IF;
749
750 IF (p_commit = FND_API.G_TRUE) THEN
751 savepoint Set_Inv_Mth_At_Top_Task_svpt;
752 END IF;
753
754 IF l_debug_mode = 'Y' THEN
755 PA_DEBUG.set_curr_function( p_function => 'Set_Inv_Mth_At_Top_Task',
756 p_debug_mode => l_debug_mode );
757 END IF;
758
759 IF l_debug_mode = 'Y' THEN
760 Pa_Debug.g_err_stage:= 'Printing Input parameters';
761 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
762 l_debug_level3);
763
764 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
765 l_debug_level3);
766
767 Pa_Debug.WRITE(g_module_name,'p_inv_mth'||':'||p_inv_mth,
768 l_debug_level3);
769 END IF;
770
771 IF l_debug_mode = 'Y' THEN
772 Pa_Debug.g_err_stage:= 'Validating Input parameters';
773 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
774 l_debug_level3);
775 END IF;
776
777 IF ( (p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM) AND
778 (p_inv_mth IS NULL OR p_inv_mth = FND_API.G_MISS_CHAR)
779 )
780 THEN
781 IF l_debug_mode = 'Y' THEN
782 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : Set_Inv_Mth_At_Top_Task :
783 p_project_id and p_inv_mth are NULL';
784 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
785 l_debug_level3);
786 END IF;
787 RAISE Invalid_Arg_Exc;
788 END IF;
789
790 IF ( (p_project_id IS NOT NULL AND p_project_id <> FND_API.G_MISS_NUM) AND
791 (p_inv_mth IS NOT NULL AND p_inv_mth <> FND_API.G_MISS_CHAR)
792 ) THEN
793
794 UPDATE pa_tasks SET invoice_method = p_inv_mth
795 WHERE project_id = p_project_id;
796 ELSE
797 IF l_debug_mode = 'Y' THEN
798 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : Set_Inv_Mth_At_Top_Task :
799 Mandatory parameters p_project_id or p_inv_mth are NULL';
800 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
801 l_debug_level3);
802 END IF;
803 RAISE Invalid_Arg_Exc;
804 END IF;
805
806 IF (p_commit = FND_API.G_TRUE) THEN
807 COMMIT;
808 END IF;
809
810 EXCEPTION
811
812 WHEN FND_API.G_EXC_ERROR THEN
813
814 x_return_status := Fnd_Api.G_RET_STS_ERROR;
815
816 IF p_commit = FND_API.G_TRUE THEN
817 ROLLBACK TO Set_Inv_Mth_At_Top_Task_svpt;
818 END IF;
819
820 l_msg_count := Fnd_Msg_Pub.count_msg;
821 IF l_msg_count = 1 AND x_msg_data IS NULL
822 THEN
823 Pa_Interface_Utils_Pub.get_messages
824 ( p_encoded => Fnd_Api.G_FALSE
825 , p_msg_index => 1
826 , p_msg_count => l_msg_count
827 , p_msg_data => l_msg_data
828 , p_data => l_data
829 , p_msg_index_out => l_msg_index_out);
830 x_msg_data := l_data;
831 x_msg_count := l_msg_count;
832 ELSE
833 x_msg_count := l_msg_count;
834 END IF;
835
836 IF l_debug_mode = 'Y' THEN
837 Pa_Debug.reset_curr_function;
838 END IF;
839
840 WHEN Invalid_Arg_Exc THEN
841 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
842 x_msg_count := 1;
843 x_msg_data := ' PA_TOP_TASK_CUST_INVOICE_PVT : Set_Inv_Mth_At_Top_Task : NULL parameters passed';
844
845 IF p_commit = FND_API.G_TRUE THEN
846 ROLLBACK TO Set_Inv_Mth_At_Top_Task_svpt;
847 END IF;
848
849 Fnd_Msg_Pub.add_exc_msg
850 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
851 , p_procedure_name => 'Set_Inv_Mth_At_Top_Task'
852 , p_error_text => x_msg_data);
853
854 IF l_debug_mode = 'Y' THEN
855 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
856 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
857 l_debug_level5);
858 Pa_Debug.reset_curr_function;
859 END IF;
860 RAISE;
861
862 WHEN OTHERS THEN
863 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
864 x_msg_count := 1;
865 x_msg_data := SQLERRM;
866
867 IF p_commit = FND_API.G_TRUE THEN
868 ROLLBACK TO Set_Inv_Mth_At_Top_Task_svpt;
869 END IF;
870
871 Fnd_Msg_Pub.add_exc_msg
872 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
873 , p_procedure_name => 'Set_Inv_Mth_At_Top_Task'
874 , p_error_text => x_msg_data);
875
876 IF l_debug_mode = 'Y' THEN
877 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
878 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
879 l_debug_level5);
880 Pa_Debug.reset_curr_function;
881 END IF;
882 RAISE;
883
884 END Set_Inv_Mth_At_Top_Task;
885
886
887
888 -- Procedure : set_top_task_funding_level
889 -- Type : PRIVATE
890 -- Purpose :
891 -- Note :
892 -- Assumptions :
893 -- Parameters Type Required Description and Purpose
894 -- --------------------------- ------ -------- --------------------------------------------------------
895 -- p_project_id NUMBER Y Project ID for which funding flag is to be set
896 PROCEDURE set_top_task_funding_level(
897 p_api_version IN NUMBER := 1.0
898 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
899 , p_commit IN VARCHAR2 := FND_API.G_FALSE
900 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
901 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
902 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
903 , p_debug_mode IN VARCHAR2 := 'N'
904 , p_project_id IN NUMBER
905 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865
906 , x_msg_count OUT NOCOPY NUMBER -- 4537865
907 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
908 ) IS
909
910 l_msg_count NUMBER := 0;
911 l_debug_mode VARCHAR2(1);
912 l_data VARCHAR2(2000);
913 l_msg_data VARCHAR2(2000);
914 l_msg_index_out NUMBER;
915
916 l_debug_level2 CONSTANT NUMBER := 2;
917 l_debug_level3 CONSTANT NUMBER := 3;
918 l_debug_level4 CONSTANT NUMBER := 4;
919 l_debug_level5 CONSTANT NUMBER := 5;
920
921 l_funding_level varchar2(1);
922 l_err_code number;
923 l_err_stage varchar2(100);
924 l_err_stack varchar2(700);
925
926 BEGIN
927
928 x_msg_count := 0;
929 x_return_status := FND_API.G_RET_STS_SUCCESS;
930 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
931
932 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
933 FND_MSG_PUB.initialize;
934 END IF;
935
936 IF (p_commit = FND_API.G_TRUE) THEN
937 savepoint set_top_tsk_funding_lvl_svpt;
938 END IF;
939
940 IF l_debug_mode = 'Y' THEN
941 PA_DEBUG.set_curr_function( p_function => 'set_top_task_funding_level',
942 p_debug_mode => l_debug_mode );
943 END IF;
944
945 IF l_debug_mode = 'Y' THEN
946 Pa_Debug.g_err_stage:= 'Printing Input parameters';
947 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
948 l_debug_level3);
949
950 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
951 l_debug_level3);
952 END IF;
953
954 IF l_debug_mode = 'Y' THEN
955 Pa_Debug.g_err_stage:= 'Validating Input parameters';
956 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
957 l_debug_level3);
958 END IF;
959
960 IF ( (p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM)
961 )
962 THEN
963 IF l_debug_mode = 'Y' THEN
964 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : set_top_task_funding_level :
965 p_project_id is NULL';
966 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
967 l_debug_level3);
968 END IF;
969 RAISE Invalid_Arg_Exc;
970 END IF;
971
972 IF l_debug_mode = 'Y' THEN
973 Pa_Debug.g_err_stage:= 'Going to set project funding flag to N';
974 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
975 l_debug_level3);
976 END IF;
977
978 --Commented out code below for bug 3882790
979 /*pa_billing_core.check_funding_level(p_project_id,
980 l_funding_level,
981 l_err_code,
982 l_err_stage,
983 l_err_stack);
984 IF l_funding_level = 'P' AND l_err_code = 0 THEN*/
985 UPDATE pa_projects_all SET project_level_funding_flag = 'N'
986 WHERE project_id = p_project_id;
987 --END IF;
988
989 IF (p_commit = FND_API.G_TRUE) THEN
990 COMMIT;
991 END IF;
992
993 EXCEPTION
994
995 WHEN FND_API.G_EXC_ERROR THEN
996
997 x_return_status := Fnd_Api.G_RET_STS_ERROR;
998
999 IF p_commit = FND_API.G_TRUE THEN
1000 ROLLBACK TO set_top_tsk_funding_lvl_svpt;
1001 END IF;
1002
1003 l_msg_count := Fnd_Msg_Pub.count_msg;
1004 IF l_msg_count = 1 AND x_msg_data IS NULL
1005 THEN
1006 Pa_Interface_Utils_Pub.get_messages
1007 ( p_encoded => Fnd_Api.G_FALSE
1008 , p_msg_index => 1
1009 , p_msg_count => l_msg_count
1010 , p_msg_data => l_msg_data
1011 , p_data => l_data
1012 , p_msg_index_out => l_msg_index_out);
1013 x_msg_data := l_data;
1014 x_msg_count := l_msg_count;
1015 ELSE
1016 x_msg_count := l_msg_count;
1017 END IF;
1018
1019 IF l_debug_mode = 'Y' THEN
1020 Pa_Debug.reset_curr_function;
1021 END IF;
1022
1023 WHEN Invalid_Arg_Exc THEN
1024 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1025 x_msg_count := 1;
1026 x_msg_data := ' PA_TOP_TASK_CUST_INVOICE_PVT : set_top_task_funding_level : NULL parameters passed';
1027
1028 IF p_commit = FND_API.G_TRUE THEN
1029 ROLLBACK TO set_top_tsk_funding_lvl_svpt;
1030 END IF;
1031
1032 Fnd_Msg_Pub.add_exc_msg
1033 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
1034 , p_procedure_name => 'set_top_task_funding_level'
1035 , p_error_text => x_msg_data);
1036
1037 IF l_debug_mode = 'Y' THEN
1038 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1039 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1040 l_debug_level5);
1041 Pa_Debug.reset_curr_function;
1042 END IF;
1043 RAISE;
1044
1045 WHEN OTHERS THEN
1046 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1047 x_msg_count := 1;
1048 x_msg_data := SQLERRM;
1049
1050 IF p_commit = FND_API.G_TRUE THEN
1051 ROLLBACK TO set_top_tsk_funding_lvl_svpt;
1052 END IF;
1053
1054 Fnd_Msg_Pub.add_exc_msg
1055 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
1056 , p_procedure_name => 'set_top_task_funding_level'
1057 , p_error_text => x_msg_data);
1058
1059 IF l_debug_mode = 'Y' THEN
1060 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1061 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1062 l_debug_level5);
1063 Pa_Debug.reset_curr_function;
1064 END IF;
1065 RAISE;
1066
1067 END set_top_task_funding_level;
1068
1069
1070
1071
1072 -- Procedure : check_customer_assoc
1073 -- Type : PRIVATE
1074 -- Purpose : Check for customer associations with tasks or whether the customer is default
1075 -- top task customer and update tables accordingly
1076 -- Note : This API should be called only if Customer_At_Top_Task is enabled for the project
1077 -- Assumptions : This API is called from self-service during customer deletion only when
1078 -- customer_at_top_task is enabled for the project
1079 -- Parameters Type Required Description and Purpose
1080 -- --------------------------- ------ -------- --------------------------------------------------------
1081 -- p_project_id NUMBER Y Project ID for which customer is being deleted
1082 -- p_customer_id NUMBER Y Customer ID which is being deleted
1083 -- x_cust_assoc VARCHAR2 "T" if customer is associated with tasks
1084 -- "D" if customer is default top task customer
1085 -- x_cust_id NUMBER Customer ID of (new) default top task customer
1086 -- x_cust_name VARCHAR2 Customer Name of (new) default top task customer
1087 -- x_cust_num VARCHAR2 Customer Number of the (new) default top task customer
1088 PROCEDURE check_delete_customer(
1089 p_api_version IN NUMBER := 1.0
1090 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1091 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1092 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1093 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1094 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1095 , p_debug_mode IN VARCHAR2 := 'N'
1096 , p_project_id IN NUMBER
1097 , p_customer_id IN NUMBER
1098 , x_cust_assoc OUT NOCOPY VARCHAR2 -- 4537865
1099 , x_cust_id OUT NOCOPY NUMBER -- 4537865
1100 , x_cust_name OUT NOCOPY VARCHAR2 -- 4537865
1101 , x_cust_num OUT NOCOPY VARCHAR2 -- 4537865
1102 , x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1103 , x_msg_count OUT NOCOPY NUMBER -- 4537865
1104 , x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
1105 ) IS
1106 CURSOR task_assoc_exists IS
1107 SELECT 'Y'
1108 FROM PA_TASKS
1109 WHERE project_id = p_project_id
1110 AND customer_id = p_customer_id;
1111
1112 /* Changes for TCA
1113 CURSOR cur_get_top_task_customer IS
1114 SELECT ppc.customer_id, rc.customer_name, rc.customer_number
1115 FROM pa_project_customers ppc, ra_customers rc
1116 WHERE ppc.project_id = p_project_id
1117 AND ppc.default_top_task_cust_flag = 'Y'
1118 AND ppc.customer_id = rc.customer_id ;
1119 */
1120
1121 CURSOR cur_get_top_task_customer IS
1122 SELECT ppc.customer_id, hz_p.party_name, hz_p.party_number
1123 FROM pa_project_customers ppc, hz_cust_accounts HZ_C, hz_parties HZ_P
1124 WHERE ppc.project_id = p_project_id
1125 AND ppc.default_top_task_cust_flag = 'Y'
1126 AND ppc.customer_id = hz_c.cust_account_id
1127 AND hz_c.party_id = hz_p.party_id;
1128
1129 l_msg_count NUMBER := 0;
1130 l_debug_mode VARCHAR2(1);
1131 l_data VARCHAR2(2000);
1132 l_msg_data VARCHAR2(2000);
1133 l_msg_index_out NUMBER;
1134
1135 l_debug_level2 CONSTANT NUMBER := 2;
1136 l_debug_level3 CONSTANT NUMBER := 3;
1137 l_debug_level4 CONSTANT NUMBER := 4;
1138 l_debug_level5 CONSTANT NUMBER := 5;
1139
1140 l_task_assoc_exists VARCHAR2(1) := 'N' ;
1141 l_def_top_task_cust_id NUMBER;
1142 l_def_top_task_cust_name VARCHAR2(50);
1143 l_def_top_task_cust_num VARCHAR2(30);
1144
1145 l_exclude_cust_id_tbl PA_PLSQL_DATATYPES.NumTabTyp;
1146
1147 BEGIN
1148
1149 x_msg_count := 0;
1150 x_return_status := FND_API.G_RET_STS_SUCCESS;
1151 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1152
1153 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1154 FND_MSG_PUB.initialize;
1155 END IF;
1156
1157 IF (p_commit = FND_API.G_TRUE) THEN
1158 savepoint check_delete_customer;
1159 END IF;
1160
1161 IF l_debug_mode = 'Y' THEN
1162 PA_DEBUG.set_curr_function( p_function => 'check_delete_customer',
1163 p_debug_mode => l_debug_mode );
1164 END IF;
1165
1166 IF l_debug_mode = 'Y' THEN
1167 Pa_Debug.g_err_stage:= 'Printing Input parameters';
1168 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1169 l_debug_level3);
1170
1171 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
1172 l_debug_level3);
1173 Pa_Debug.WRITE(g_module_name,'p_customer_id'||':'||p_customer_id,
1174 l_debug_level3);
1175 END IF;
1176
1177 IF l_debug_mode = 'Y' THEN
1178 Pa_Debug.g_err_stage:= 'Validating Input parameters';
1179 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1180 l_debug_level3);
1181 END IF;
1182
1183 IF ( (p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM) AND
1184 (p_customer_id IS NULL OR p_customer_id = FND_API.G_MISS_NUM)
1185 )
1186 THEN
1187 IF l_debug_mode = 'Y' THEN
1188 Pa_Debug.g_err_stage:= 'PA_TOP_TASK_CUST_INVOICE_PVT : check_delete_customer :
1189 p_project_id AND p_customer_id are NULL';
1190 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1191 l_debug_level3);
1192 END IF;
1193 RAISE Invalid_Arg_Exc;
1194 END IF;
1195
1196 OPEN task_assoc_exists;
1197 FETCH task_assoc_exists INTO l_task_assoc_exists;
1198 CLOSE task_assoc_exists;
1199
1200 OPEN cur_get_top_task_customer;
1201 FETCH cur_get_top_task_customer INTO l_def_top_task_cust_id, l_def_top_task_cust_name, l_def_top_task_cust_num;
1202 CLOSE cur_get_top_task_customer;
1203
1204 l_exclude_cust_id_tbl(1) := p_customer_id;
1205
1206 IF l_debug_mode = 'Y' THEN
1207 Pa_Debug.g_err_stage:= 'Checking for associations';
1208 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1209 l_debug_level3);
1210 END IF;
1211
1212 IF 'Y' = l_task_assoc_exists AND l_def_top_task_cust_id <> p_customer_id THEN
1213 x_cust_assoc := 'T';
1214 x_cust_id := l_def_top_task_cust_id;
1215 x_cust_name := l_def_top_task_cust_name;
1216 x_cust_num := l_def_top_task_cust_num;
1217
1218 UPDATE pa_tasks SET customer_id = l_def_top_task_cust_id
1219 WHERE project_id = p_project_id
1220 AND customer_id = p_customer_id ;
1221 ELSIF 'Y' = l_task_assoc_exists AND l_def_top_task_cust_id = p_customer_id THEN
1222 pa_top_task_cust_invoice_pvt.Get_Highest_Contr_Cust( P_API_VERSION => 1.0
1223 , P_INIT_MSG_LIST => 'T'
1224 , P_COMMIT => 'F'
1225 , P_VALIDATE_ONLY => 'F'
1226 , P_VALIDATION_LEVEL => 100
1227 , P_DEBUG_MODE => 'N'
1228 , p_project_id => p_project_id
1229 , p_exclude_cust_id_tbl => l_exclude_cust_id_tbl
1230 , x_highst_contr_cust_id => x_cust_id
1231 , x_highst_contr_cust_name => x_cust_name
1232 , x_highst_contr_cust_num => x_cust_num
1233 , x_return_status => x_return_status
1234 , x_msg_count => x_msg_count
1235 , x_msg_data => x_msg_data );
1236 IF 'S' = x_return_status THEN
1237
1238 UPDATE pa_tasks SET customer_id = x_cust_id
1239 WHERE project_id = p_project_id
1240 AND customer_id = p_customer_id ;
1241
1242 IF x_cust_id IS NOT NULL THEN
1243 x_cust_assoc := 'T';
1244 UPDATE pa_project_customers SET default_top_task_cust_flag = 'Y'
1245 WHERE project_id = p_project_id
1246 AND customer_id = x_cust_id;
1247 END IF;
1248
1249 END IF;
1250 ELSIF 'Y' <> l_task_assoc_exists AND l_def_top_task_cust_id = p_customer_id THEN
1251 pa_top_task_cust_invoice_pvt.Get_Highest_Contr_Cust( P_API_VERSION => 1.0
1252 , P_INIT_MSG_LIST => 'T'
1253 , P_COMMIT => 'F'
1254 , P_VALIDATE_ONLY => 'F'
1255 , P_VALIDATION_LEVEL => 100
1256 , P_DEBUG_MODE => 'N'
1257 , p_project_id => p_project_id
1258 , p_exclude_cust_id_tbl => l_exclude_cust_id_tbl
1259 , x_highst_contr_cust_id => x_cust_id
1260 , x_highst_contr_cust_name => x_cust_name
1261 , x_highst_contr_cust_num => x_cust_num
1262 , x_return_status => x_return_status
1263 , x_msg_count => x_msg_count
1264 , x_msg_data => x_msg_data );
1265 IF 'S' = x_return_status AND x_cust_id IS NOT NULL THEN
1266 x_cust_assoc := 'D';
1267 UPDATE pa_project_customers SET default_top_task_cust_flag = 'Y'
1268 WHERE project_id = p_project_id
1269 AND customer_id = x_cust_id;
1270 END IF;
1271 END IF;
1272
1273 IF (p_commit = FND_API.G_TRUE) THEN
1274 COMMIT;
1275 END IF;
1276 EXCEPTION
1277 WHEN FND_API.G_EXC_ERROR THEN
1278
1279 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1280
1281 -- 4537865 : RESET OUT PARAMS
1282 x_cust_assoc := NULL ;
1283 x_cust_id := NULL ;
1284 x_cust_name := NULL ;
1285 x_cust_num := NULL ;
1286
1287 IF p_commit = FND_API.G_TRUE THEN
1288 ROLLBACK TO check_customer_delete;
1289 END IF;
1290
1291 l_msg_count := Fnd_Msg_Pub.count_msg;
1292 IF l_msg_count = 1 AND x_msg_data IS NULL
1293 THEN
1294 Pa_Interface_Utils_Pub.get_messages
1295 ( p_encoded => Fnd_Api.G_FALSE
1296 , p_msg_index => 1
1297 , p_msg_count => l_msg_count
1298 , p_msg_data => l_msg_data
1299 , p_data => l_data
1300 , p_msg_index_out => l_msg_index_out);
1301 x_msg_data := l_data;
1302 x_msg_count := l_msg_count;
1303 ELSE
1304 x_msg_count := l_msg_count;
1305 END IF;
1306
1307 IF l_debug_mode = 'Y' THEN
1308 Pa_Debug.reset_curr_function;
1309 END IF;
1310
1311 WHEN Invalid_Arg_Exc THEN
1312 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1313 x_msg_count := 1;
1314 x_msg_data := ' PA_TOP_TASK_CUST_INVOICE_PVT : check_delete_customer : NULL parameters passed';
1315
1316 -- 4537865 : RESET OUT PARAMS
1317 x_cust_assoc := NULL ;
1318 x_cust_id := NULL ;
1319 x_cust_name := NULL ;
1320 x_cust_num := NULL ;
1321
1322 IF p_commit = FND_API.G_TRUE THEN
1323 ROLLBACK TO check_delete_customer;
1324 END IF;
1325
1326 Fnd_Msg_Pub.add_exc_msg
1327 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
1328 , p_procedure_name => 'check_delete_customer'
1329 , p_error_text => x_msg_data);
1330
1331 IF l_debug_mode = 'Y' THEN
1332 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1333 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1334 l_debug_level5);
1335 Pa_Debug.reset_curr_function;
1336 END IF;
1337 RAISE;
1338
1339 WHEN OTHERS THEN
1340 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1341 x_msg_count := 1;
1342 x_msg_data := SQLERRM;
1343
1344 -- 4537865 : RESET OUT PARAMS
1345 x_cust_assoc := NULL ;
1346 x_cust_id := NULL ;
1347 x_cust_name := NULL ;
1348 x_cust_num := NULL ;
1349
1350 IF p_commit = FND_API.G_TRUE THEN
1351 ROLLBACK TO check_delete_customer;
1352 END IF;
1353
1354 Fnd_Msg_Pub.add_exc_msg
1355 ( p_pkg_name => 'PA_TOP_TASK_CUST_INVOICE_PVT'
1356 , p_procedure_name => 'check_delete_customer'
1357 , p_error_text => x_msg_data);
1358
1359 IF l_debug_mode = 'Y' THEN
1360 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1361 Pa_Debug.WRITE( g_module_name,Pa_Debug.g_err_stage,
1362 l_debug_level5 );
1363 Pa_Debug.reset_curr_function;
1364 END IF;
1365 RAISE;
1366 END check_delete_customer;
1367
1368 END PA_TOP_TASK_CUST_INVOICE_PVT;