[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING_SETUP_PVT
Source
1 PACKAGE BODY PA_BILLING_SETUP_PVT AS
2 /* $Header: PABLSTVB.pls 120.3 2005/08/19 16:17:01 mwasowic noship $ */
3
4
5 -- API name : update_revenue_and_billing
6 -- Type : Private procedure
10 -- p_commit IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_FALSE
7 -- Pre-reqs : None
8 -- Return Value : N/A
9 -- Prameters
11 -- p_validate_only IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_TRUE
12 -- p_validation_level IN NUMBER OPTIONAL DEFAULT=FND_API.G_VALID_LEVEL_FULL
13 -- p_calling_module IN VARCHAR2 OPTIONAL DEFAULT='SELF_SERVICE'
14 -- p_debug_mode IN VARCHAR2 OPTIONAL DEFAULT='N'
15 -- p_max_msg_count IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
16 -- p_project_id IN NUMBER REQUIRED
17 -- p_distribution_rule IN VARCHAR2 REQUIRED
18 -- p_billing_cycle_id IN NUMBER REQUIRED
19 -- p_first_bill_offset IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
20 -- p_next_billing_date IN DATE OPTIONAL DEFAULT=FND_API.G_MISS_DATE
21 -- p_output_tax_code IN VARCHAR2 OPTIONAL DEFAULT=FND_API.G_MISS_CHAR
22 -- p_billing_job_group_id IN NUMBER REQUIRED
23 -- p_invoice_comment IN VARCHAR2 OPTIONAL DEFAULT=FND_API.G_MISS_CHAR
24 -- p_labor_id IN NUMBER REQUIRED
25 -- p_non_labor_id IN NUMBER OPTIONAL
26 -- p_retention_inv_format_id IN VARCHAR2 OPTIONAL
27 -- p_retention_percent IN NUMBER OPTIONAL
28 -- p_retention_output_tax_code IN VARCHAR2 OPTIONAL
29 -- p_record_version_number IN NUMBER REQUIRED DEFAULT=1
30 -- x_return_status OUT VARCHAR2 REQUIRED
31 -- x_msg_count OUT VARCHAR2 REQUIRED
32 -- x_msg_data OUT VARCHAR2 REQUIRED
33 --
34 -- History
35 --
36 -- 25-MAY-01 Majid Ansari -Created
37 --
38 --
39
40 PROCEDURE update_revenue_and_billing(
41 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
42 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
43 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
44 p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
45 p_debug_mode IN VARCHAR2 DEFAULT 'N',
46 p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
47 p_project_id IN NUMBER ,
48 p_distribution_rule IN VARCHAR2 ,
49 p_billing_cycle_id IN NUMBER ,
50 p_first_bill_offset IN NUMBER DEFAULT FND_API.G_MISS_NUM,
51 p_next_billing_date OUT NOCOPY DATE , --File.Sql.39 bug 4440895
52 p_output_tax_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
53 p_billing_job_group_id IN NUMBER ,
54 p_invoice_comment IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
55 p_labor_id IN NUMBER ,
56 p_non_labor_id IN NUMBER ,
57 p_retention_inv_format_id IN VARCHAR2 ,
58 p_retention_percent IN NUMBER ,
59 p_retention_output_tax_code IN VARCHAR2 ,
60 p_record_version_number IN NUMBER DEFAULT 1,
61 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
62 x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
63 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
64 ) IS
65
66 l_return_status VARCHAR2(1);
67 l_msg_data VARCHAR2(250);
68 l_msg_count NUMBER;
69
70 l_dummy_char VARCHAR2(1);
71 l_error_msg_code VARCHAR2(250);
72 l_data VARCHAR2(250);
73 l_msg_index_out NUMBER;
74
75 l_task_id NUMBER;
76 l_first_bill_offset NUMBER;
77 l_next_billing_date DATE;
78 l_output_tax_code VARCHAR2(50);
79 l_invoice_comment VARCHAR2(240);
80 l_retention_inv_format_id NUMBER(15);
81 l_retention_percent PA_PROJECTS_ALL.RETENTION_PERCENTAGE%TYPE;
82 l_retention_output_tax_code VARCHAR2(50);
83 l_project_type_class_code PA_PROJECT_TYPES.PROJECT_TYPE_CLASS_CODE%TYPE;
84
85
86 BEGIN
87 IF p_commit = FND_API.G_TRUE
88 THEN
89 SAVEPOINT revenue_and_billing;
90 END IF;
91
92 x_return_status := 'S';
93
94 IF p_validate_only = FND_API.G_TRUE AND p_validation_level > 0
95 THEN
96 --Assign null to the variables that are passed as G_MISS....
97
98 IF p_first_bill_offset = FND_API.G_MISS_NUM
99 THEN
100 l_first_bill_offset := null;
101 ELSE
102 l_first_bill_offset := p_first_bill_offset;
103 END IF;
104
105 IF p_next_billing_date = FND_API.G_MISS_DATE
106 THEN
107 l_next_billing_date := null;
108 ELSE
109 l_next_billing_date := p_next_billing_date;
110 END IF;
111
112 IF p_output_tax_code = FND_API.G_MISS_CHAR
113 THEN
114 l_output_tax_code := null;
115 ELSE
116 l_output_tax_code := p_output_tax_code;
117 END IF;
118
119 IF p_invoice_comment = FND_API.G_MISS_CHAR
120 THEN
121 l_invoice_comment := null;
122 ELSE
123 l_invoice_comment := p_invoice_comment;
124 END IF;
125
126 IF p_retention_percent = FND_API.G_MISS_NUM
127 THEN
128 l_retention_percent := null;
129 ELSE
130 l_retention_percent := p_retention_percent;
131 END IF;
132
133 IF p_retention_output_tax_code = FND_API.G_MISS_CHAR
134 THEN
135 l_retention_output_tax_code := null;
136 ELSE
137 l_retention_output_tax_code := p_retention_output_tax_code;
138 END IF;
139
140 END IF;
141
142 -- Mandatory parameters check
143
144 --Get the project type class code and pass to procedure to validate mandaory columns
145 l_project_type_class_code := PA_BILLING_SCHEDULE_UTILS.Get_Project_Type_Class(
146 p_project_id,
147 l_return_status );
148
149 PA_BILLING_SETUP_UTILS.REV_BILL_INF_REQ_CHECK(
150 p_project_type_class_code => l_project_type_class_code,
151 p_distribution_rule => p_distribution_rule,
152 p_billing_cycle_id => p_billing_cycle_id,
153 p_first_bill_offset => p_first_bill_offset,
154 p_billing_job_group_id => p_billing_job_group_id,
155 p_labor_id => p_labor_id,
156 p_non_labor_id => p_non_labor_id,
157 x_return_status => l_return_status,
158 x_error_msg_code => l_error_msg_code );
159
160 IF l_return_status = FND_API.G_RET_STS_ERROR
161 THEN
162 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
163 p_msg_name => l_error_msg_code);
164
165 x_msg_data := l_error_msg_code;
166 x_return_status := 'E';
167 RAISE FND_API.G_EXC_ERROR;
168 END IF;
169
170
171 IF (p_debug_mode = 'Y')
172 THEN
173 pa_debug.debug('Update_reve_and_billing PVT: Checking Lock on record');
174 END IF;
175
176 /* Lock pa_projects table and update */
177 IF p_validate_only <> FND_API.G_TRUE
178 THEN
179 BEGIN
180 SELECT 'x' INTO l_dummy_char
181 FROM pa_projects
182 WHERE project_id = p_project_id
183 AND record_version_number = p_record_version_number
184 FOR UPDATE OF record_version_number NOWAIT;
185 EXCEPTION
186 WHEN TIMEOUT_ON_RESOURCE THEN
187 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
188 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
189 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
190 x_return_status := 'E' ;
191 WHEN NO_DATA_FOUND THEN
192 if p_calling_module = 'FORM' then
193 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
194 p_msg_name => 'FORM_RECORD_CHANGED');
195 x_msg_data := 'FORM_RECORD_CHANGED';
196 else
197 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
198 p_msg_name => 'PA_XC_RECORD_CHANGED');
199 x_msg_data := 'PA_XC_RECORD_CHANGED';
200 end if;
201 x_return_status := 'E' ;
202 WHEN OTHERS THEN
203 IF SQLCODE = -54 THEN
204 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
205 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
206 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
207 x_return_status := 'E' ;
208 ELSE
209 raise;
210 END IF;
211 END;
212 ELSE
213 BEGIN
214 SELECT 'x' INTO l_dummy_char
215 FROM pa_projects
216 WHERE project_id = p_project_id
217 AND record_version_number = p_record_version_number;
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 if p_calling_module = 'FORM' then
221 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
222 p_msg_name => 'FORM_RECORD_CHANGED');
223 x_msg_data := 'FORM_RECORD_CHANGED';
224 else
225 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
226 p_msg_name => 'PA_XC_RECORD_CHANGED');
227 x_msg_data := 'PA_XC_RECORD_CHANGED';
228 end if;
229 x_return_status := 'E' ;
230 WHEN OTHERS THEN
231 IF SQLCODE = -54 THEN
232 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
233 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
234 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
235 x_return_status := 'E' ;
236 END IF;
237 END;
238 END IF;
239 l_msg_count := FND_MSG_PUB.count_msg;
240
241 IF l_msg_count > 0 THEN
242 x_msg_count := l_msg_count;
243 x_return_status := 'E';
244 RAISE FND_API.G_EXC_ERROR;
245 END IF;
246
247
248 UPDATE pa_projects
249 SET DISTRIBUTION_RULE = p_distribution_rule,
250 BILLING_CYCLE_ID = p_billing_cycle_id,
251 BILLING_OFFSET = l_first_bill_offset,
252 OUTPUT_TAX_CODE = l_output_tax_code,
253 BILL_JOB_GROUP_ID = p_billing_job_group_id ,
254 INVOICE_COMMENT = l_invoice_comment,
255 LABOR_INVOICE_FORMAT_ID = p_labor_id,
256 NON_LABOR_INVOICE_FORMAT_ID = p_non_labor_id,
257 RETENTION_INVOICE_FORMAT_ID = p_retention_inv_format_id,
258 RETENTION_PERCENTAGE = l_retention_percent,
259 RETENTION_TAX_CODE = l_retention_output_tax_code,
260 LAST_UPDATE_DATE = SYSDATE ,
261 LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
262 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
263
264 /* If job group bill id is changed then update
265 the JOB_BILL_RATE_SCHEDULE_ID to null */
266
267 JOB_BILL_RATE_SCHEDULE_ID = DECODE( p_billing_job_group_id, BILL_JOB_GROUP_ID, JOB_BILL_RATE_SCHEDULE_ID ),
268 RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 0 ) + 1
269 WHERE project_id = p_project_id
270 AND record_version_number = p_record_version_number;
271
272
273 EXCEPTION
274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275 IF p_commit = FND_API.G_TRUE
276 THEN
277 ROLLBACK TO revenue_and_billing;
278 END IF;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
281 p_procedure_name => 'update_rev_and_billing',
282 p_error_text => SUBSTRB(SQLERRM,1,240));
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284
285 WHEN FND_API.G_EXC_ERROR THEN
286 IF p_commit = FND_API.G_TRUE
287 THEN
288 ROLLBACK TO revenue_and_billing;
289 END IF;
290 x_return_status := 'E';
291
292 WHEN OTHERS THEN
293 IF p_commit = FND_API.G_TRUE
294 THEN
295 ROLLBACK TO revenue_and_billing;
296 END IF;
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
299 p_procedure_name => 'update_rev_and_billing',
300 p_error_text => SUBSTRB(SQLERRM,1,240));
301 RAISE;
302
303 END update_revenue_and_billing;
304
305 -- API name : create_credit_receivers
306 -- Type : Private procedure
307 -- Pre-reqs : None
308 -- Return Value : N/A
309 -- Prameters
310 -- p_commit IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_FALSE
311 -- p_validate_only IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_TRUE
312 -- p_validation_level IN NUMBER OPTIONAL DEFAULT=FND_API.G_VALID_LEVEL_FULL
313 -- p_calling_module IN VARCHAR2 OPTIONAL DEFAULT='SELF_SERVICE'
314 -- p_debug_mode IN VARCHAR2 OPTIONAL DEFAULT='N'
315 -- p_max_msg_count IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
316 -- p_project_id IN NUMBER REQUIRED
317 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
318 -- p_credit_type IN VARCHAR2 REQUIRED
319 -- p_person_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
320 -- p_credit_percentage IN NUMBER REQUIRED
321 -- p_transfer_to_AR IN VARCHAR2 REQUIRED
322 -- p_effective_from_date IN DATE REQUIRED
326 -- x_msg_count OUT VARCHAR2 REQUIRED
323 -- p_effective_to_date IN DATE OPTIONAL DEFAULT=FND_API.G_MISS_DATE
324 -- x_credit_receiver_id OUT NUMBER REQUIRED
325 -- x_return_status OUT VARCHAR2 REQUIRED
327 -- x_msg_data OUT VARCHAR2 REQUIRED
328 --
329 -- History
330 --
331 -- 25-MAY-01 Majid Ansari -Created
332 --
333 --
334
335 PROCEDURE create_credit_receivers(
336 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
337 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
338 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
339 p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
340 p_debug_mode IN VARCHAR2 DEFAULT 'N',
341 p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
342 p_project_id IN NUMBER ,
343 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
344 p_credit_type IN VARCHAR2 ,
345 p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
346 p_credit_percentage IN NUMBER ,
347 p_transfer_to_AR IN VARCHAR2 ,
348 p_effective_from_date IN DATE ,
349 p_effective_to_date IN DATE DEFAULT FND_API.G_MISS_DATE,
350 x_credit_receiver_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
351 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
352 x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
353 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
354 ) IS
355
356 l_return_status VARCHAR2(1);
357 l_error_msg_code VARCHAR2(250);
358 l_dummy_char VARCHAR2(1);
359 l_msg_count NUMBER;
360
361
362 l_task_id NUMBER;
363 l_credit_percentage NUMBER;
364 l_effective_to_date DATE;
365 l_person_id NUMBER;
366 l_credit_receiver_id NUMBER;
367 BEGIN
368 IF p_commit = FND_API.G_TRUE
369 THEN
370 SAVEPOINT credit_receivers;
371 END IF;
372
373 x_return_status := 'S';
374
375 IF p_validate_only = FND_API.G_TRUE AND
376 p_validation_level > 0
377 THEN
378 IF p_task_id = FND_API.G_MISS_NUM
379 THEN
380 l_task_id := null;
381 ELSE
382 l_task_id := p_task_id;
383 END IF;
384
385 IF p_credit_percentage = FND_API.G_MISS_NUM
386 THEN
387 l_credit_percentage := null;
388 ELSE
389 l_credit_percentage := p_credit_percentage;
390 END IF;
391
392 IF p_effective_to_date = FND_API.G_MISS_DATE
393 THEN
394 l_effective_to_date := null;
395 ELSE
396 l_effective_to_date := p_effective_to_date;
397 END IF;
398
399 --Mandatory check for parmeters
400 PA_BILLING_SETUP_UTILS.CREDIT_REC_REQ_CHECK(
401 p_credit_type => p_credit_type,
402 p_person_id => p_person_id,
403 p_transfer_to_AR => p_transfer_to_AR,
404 p_effective_from_date => p_effective_from_date,
405 x_return_status => l_return_status,
406 x_error_msg_code => l_error_msg_code );
407
408 IF l_return_status = FND_API.G_RET_STS_ERROR
409 THEN
410 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
411 p_msg_name => l_error_msg_code);
412
413 x_msg_data := l_error_msg_code;
414 x_return_status := 'E';
415 RAISE FND_API.G_EXC_ERROR;
416 END IF;
417
418 ---Start end date check
419 PA_BILLING_SCHEDULE_UTILS.CHECK_START_END_DATE(
420 p_effective_from_date => p_effective_from_date,
421 p_effective_to_date => l_effective_to_date,
422 x_error_msg_code => l_error_msg_code,
423 x_return_status => l_return_status );
424
425 IF l_return_status = FND_API.G_RET_STS_ERROR
426 THEN
427 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
428 p_msg_name => l_error_msg_code);
429 x_msg_data := l_error_msg_code;
430 x_return_status := 'E';
431 RAISE FND_API.G_EXC_ERROR;
432 END IF;
433
434
435 IF NOT PA_BILLING_SETUP_UTILS.Duplicate_credit_receivers(
436 p_project_id => p_project_id,
437 p_task_id => l_task_id,
438 p_credit_type => p_credit_type,
439 p_person_id => p_person_id,
440 p_effective_from_date => p_effective_from_date,
441 p_effective_to_date => l_effective_to_date,
442 p_credit_receiver_id => l_credit_receiver_id,
443 x_return_status => l_return_status
444 )
445 THEN
446 SELECT pa_credit_receivers_s.NEXTVAL INTO l_credit_receiver_id
447 FROM dual;
448 x_credit_receiver_id := l_credit_receiver_id;
449 INSERT INTO pa_credit_receivers(
450 credit_receiver_id,
454 LAST_UPDATE_DATE ,
451 PERSON_ID,
452 CREDIT_TYPE_CODE ,
453 PROJECT_ID ,
455 LAST_UPDATED_BY ,
456 CREATION_DATE ,
457 CREATED_BY,
458 LAST_UPDATE_LOGIN ,
459 START_DATE_ACTIVE ,
460 CREDIT_PERCENTAGE ,
461 TASK_ID ,
462 END_DATE_ACTIVE ,
463 TRANSFER_TO_AR_FLAG ,
464 SALESREP_ID ,
465 BUDGET_TYPE_CODE ,
466 RECORD_VERSION_NUMBER
467 )
468 VALUES (
469 l_credit_receiver_id,
470 p_person_id,
471 p_credit_type,
472 p_project_id,
473 sysdate,
474 FND_GLOBAL.USER_ID,
475 sysdate,
476 FND_GLOBAL.USER_ID,
477 FND_GLOBAL.LOGIN_ID,
478 p_effective_from_date,
479 p_credit_percentage,
480 l_task_id,
481 p_effective_to_date,
482 p_transfer_to_AR,
483 null,
484 null,
485 1
486 );
487
488 ELSE
489 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
490 p_msg_name => 'PA_PRJ_CR_REC_EXISTS' );
491 x_msg_data := 'PA_PRJ_CR_REC_EXISTS';
492 x_return_status := 'E';
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495 END IF;
496 EXCEPTION
497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
498 IF p_commit = FND_API.G_TRUE
499 THEN
500 ROLLBACK TO credit_receivers;
501 END IF;
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
504 p_procedure_name => 'CREATE_CREDIT_RECEIVERS',
505 p_error_text => SUBSTRB(SQLERRM,1,240));
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507
508 x_credit_receiver_id := null;
509
510 WHEN FND_API.G_EXC_ERROR THEN
511 IF p_commit = FND_API.G_TRUE
512 THEN
513 ROLLBACK TO credit_receivers;
514 END IF;
515 x_return_status := 'E';
516
517 x_credit_receiver_id := null;
518
519 WHEN OTHERS THEN
520 IF p_commit = FND_API.G_TRUE
521 THEN
522 ROLLBACK TO credit_receivers;
523 END IF;
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
525 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
526 p_procedure_name => 'CREATE_CREDIT_RECEIVERS',
527 p_error_text => SUBSTRB(SQLERRM,1,240));
528
529 x_credit_receiver_id := null;
530
531 RAISE;
532
533 END create_credit_receivers;
534
535 -- API name : update_credit_receivers
536 -- Type : Private procedure
537 -- Pre-reqs : None
538 -- Return Value : N/A
539 -- Prameters
540 -- p_commit IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_FALSE
541 -- p_validate_only IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_TRUE
542 -- p_validation_level IN NUMBER OPTIONAL DEFAULT=FND_API.G_VALID_LEVEL_FULL
543 -- p_calling_module IN VARCHAR2 OPTIONAL DEFAULT='SELF_SERVICE'
544 -- p_debug_mode IN VARCHAR2 OPTIONAL DEFAULT='N'
545 -- p_max_msg_count IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
546 -- p_credit_receiver_id IN NUMBER REQUIRED
547 -- p_project_id IN NUMBER REQUIRED
548 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
549 -- p_credit_type IN VARCHAR2 REQUIRED
550 -- p_person_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
551 -- p_credit_percentage IN NUMBER REQUIRED
552 -- p_transfer_to_AR IN VARCHAR2 REQUIRED
553 -- p_effective_from_date IN DATE REQUIRED
554 -- p_effective_to_date IN DATE OPTIONAL DEFAULT=FND_API.G_MISS_DATE
555 -- p_record_version_number IN NUMBER REQUIRED DEFAULT=1
556 -- x_return_status OUT VARCHAR2 REQUIRED
557 -- x_msg_count OUT VARCHAR2 REQUIRED
558 -- x_msg_data OUT VARCHAR2 REQUIRED
559 --
560 -- History
561 --
562 -- 25-MAY-01 Majid Ansari -Created
563 --
564 --
565
566 PROCEDURE update_credit_receivers(
567 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
568 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
569 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
570 p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
571 p_debug_mode IN VARCHAR2 DEFAULT 'N',
572 p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
573 p_credit_receiver_id IN NUMBER ,
574 p_project_id IN NUMBER ,
575 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
576 p_credit_type IN VARCHAR2 ,
577 p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
578 p_credit_percentage IN NUMBER DEFAULT FND_API.G_MISS_NUM,
579 p_transfer_to_AR IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
580 p_effective_from_date IN DATE ,
581 p_effective_to_date IN DATE DEFAULT FND_API.G_MISS_DATE,
582 p_record_version_number IN NUMBER DEFAULT 1,
583 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
584 x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
585 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
586 ) IS
587
588 l_return_status VARCHAR2(1);
589 l_error_msg_code VARCHAR2(250);
590 l_dummy_char VARCHAR2(1);
591 l_msg_count NUMBER;
592
593 l_effective_to_date DATE;
594
595 l_task_id NUMBER;
596 l_credit_percentage NUMBER;
597 l_person_id NUMBER;
598 BEGIN
599 IF p_commit = FND_API.G_TRUE
600 THEN
601 SAVEPOINT credit_receivers;
602 END IF;
603
604 x_return_status := 'S';
605
606 IF p_validate_only = FND_API.G_TRUE AND
607 p_validation_level > 0
608 THEN
609 IF p_task_id = FND_API.G_MISS_NUM
610 THEN
611 l_task_id := null;
612 ELSE
613 l_task_id := p_task_id;
614 END IF;
615
616 IF p_credit_percentage = FND_API.G_MISS_NUM
617 THEN
618 l_credit_percentage := null;
619 ELSE
620 l_credit_percentage := p_credit_percentage;
621 END IF;
622
623 IF l_effective_to_date = FND_API.G_MISS_DATE
624 THEN
625 l_effective_to_date := null;
626 ELSE
627 l_effective_to_date := p_effective_to_date;
628 END IF;
629
630 --Mandatory check for parmeters
631 PA_BILLING_SETUP_UTILS.CREDIT_REC_REQ_CHECK(
632 p_credit_type => p_credit_type,
633 p_person_id => p_person_id,
634 p_transfer_to_AR => p_transfer_to_AR,
635 p_effective_from_date => p_effective_from_date,
636 x_return_status => l_return_status,
637 x_error_msg_code => l_error_msg_code );
638
639
640 IF l_return_status = FND_API.G_RET_STS_ERROR
641 THEN
642 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
643 p_msg_name => l_error_msg_code);
644
645 x_msg_data := l_error_msg_code;
646 x_return_status := 'E';
647 RAISE FND_API.G_EXC_ERROR;
648 END IF;
649
650
651 ---Start end date check
652 PA_BILLING_SCHEDULE_UTILS.CHECK_START_END_DATE(
653 p_effective_from_date => p_effective_from_date,
654 p_effective_to_date => l_effective_to_date,
655 x_error_msg_code => l_error_msg_code,
656 x_return_status => l_return_status );
657
658 IF l_return_status = FND_API.G_RET_STS_ERROR
659 THEN
660 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
661 p_msg_name => l_error_msg_code);
662 x_msg_data := l_error_msg_code;
663 x_return_status := 'E';
664 RAISE FND_API.G_EXC_ERROR;
665 END IF;
666
667 IF (p_debug_mode = 'Y')
668 THEN
669 pa_debug.debug('Update_Credit_Receivers PVT: Checking Lock on record');
670 END IF;
671
672 IF p_validate_only <> FND_API.G_TRUE
673 THEN
674 BEGIN
675 SELECT 'x' INTO l_dummy_char
676 FROM pa_credit_receivers
677 WHERE credit_receiver_id = p_credit_receiver_id
678 AND record_version_number = p_record_version_number
679 FOR UPDATE OF record_version_number NOWAIT;
680 EXCEPTION
681 WHEN TIMEOUT_ON_RESOURCE THEN
682 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
683 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
684 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
685 x_return_status := 'E' ;
686 WHEN NO_DATA_FOUND THEN
687 if p_calling_module = 'FORM' then
688 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
689 p_msg_name => 'FORM_RECORD_CHANGED');
690 x_msg_data := 'FORM_RECORD_CHANGED';
691 else
692 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
693 p_msg_name => 'PA_XC_RECORD_CHANGED');
694 x_msg_data := 'PA_XC_RECORD_CHANGED';
695 end if;
696 x_return_status := 'E' ;
697 WHEN OTHERS THEN
698 IF SQLCODE = -54 THEN
699 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
700 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
701 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
702 x_return_status := 'E' ;
703 ELSE
704 raise;
705 END IF;
706 END;
707 ELSE
708 BEGIN
709 SELECT 'x' INTO l_dummy_char
710 FROM pa_credit_receivers
711 WHERE credit_receiver_id = p_credit_receiver_id
712 AND record_version_number = p_record_version_number;
713 EXCEPTION
714 WHEN NO_DATA_FOUND THEN
715 if p_calling_module = 'FORM' then
716 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
717 p_msg_name => 'FORM_RECORD_CHANGED');
718 x_msg_data := 'FORM_RECORD_CHANGED';
719 else
720 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
721 p_msg_name => 'PA_XC_RECORD_CHANGED');
722 x_msg_data := 'PA_XC_RECORD_CHANGED';
723 end if;
724 x_return_status := 'E' ;
725 WHEN OTHERS THEN
726 IF SQLCODE = -54 THEN
727 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
728 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
729 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
730 x_return_status := 'E' ;
731 END IF;
732 END;
733 end if;
734 l_msg_count := FND_MSG_PUB.count_msg;
735
736 IF l_msg_count > 0 THEN
737 x_msg_count := l_msg_count;
738 x_return_status := 'E';
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741
742 IF NOT PA_BILLING_SETUP_UTILS.Duplicate_credit_receivers(
743 p_project_id => p_project_id,
744 p_task_id => l_task_id,
745 p_credit_type => p_credit_type,
746 p_person_id => p_person_id,
747 p_effective_from_date => p_effective_from_date,
748 p_effective_to_date => l_effective_to_date,
749 p_credit_receiver_id => p_credit_receiver_id,
750 x_return_status => l_return_status
751 )
752 THEN
753
754 UPDATE pa_credit_receivers
755 SET CREDIT_PERCENTAGE = l_credit_percentage,
756 start_date_active = p_effective_from_date,
757 TRANSFER_TO_AR_FLAG = p_TRANSFER_TO_AR,
758 end_date_active = l_effective_to_date,
759 credit_type_code = p_credit_type,
760 person_id = p_person_id,
761 LAST_UPDATE_DATE = SYSDATE ,
762 LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
763 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
764 RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 0 ) + 1
765 WHERE credit_receiver_id = p_credit_receiver_id
766 AND RECORD_VERSION_NUMBER = p_RECORD_VERSION_NUMBER;
767 ELSE
768 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
769 p_msg_name => 'PA_PRJ_CR_REC_EXISTS' );
770 x_msg_data := 'PA_PRJ_CR_REC_EXISTS';
771 x_return_status := 'E';
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774
775 END IF;
776 EXCEPTION
777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778 IF p_commit = FND_API.G_TRUE
779 THEN
780 ROLLBACK TO credit_receivers;
781 END IF;
782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
783 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
784 p_procedure_name => 'UPDATE_CREDIT_RECEIVERS',
785 p_error_text => SUBSTRB(SQLERRM,1,240));
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787
788 WHEN FND_API.G_EXC_ERROR THEN
789 IF p_commit = FND_API.G_TRUE
790 THEN
791 ROLLBACK TO credit_receivers;
792 END IF;
793 x_return_status := 'E';
794
795 WHEN OTHERS THEN
796 IF p_commit = FND_API.G_TRUE
797 THEN
798 ROLLBACK TO credit_receivers;
799 END IF;
800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
801 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
802 p_procedure_name => 'UPDATE_CREDIT_RECEIVERS',
803 p_error_text => SUBSTRB(SQLERRM,1,240));
804 RAISE;
805
806 END update_credit_receivers;
807
808
809
810 -- API name : delete_credit_receivers
811 -- Type : Private procedure
812 -- Pre-reqs : None
813 -- Return Value : N/A
814 -- Prameters
815 -- p_commit IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_FALSE
816 -- p_validate_only IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_TRUE
817 -- p_validation_level IN NUMBER OPTIONAL DEFAULT=FND_API.G_VALID_LEVEL_FULL
818 -- p_calling_module IN VARCHAR2 OPTIONAL DEFAULT='SELF_SERVICE'
819 -- p_debug_mode IN VARCHAR2 OPTIONAL DEFAULT='N'
820 -- p_max_msg_count IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
821 -- p_credit_receiver_id IN NUMBER REQUIRED
822 -- p_project_id IN NUMBER REQUIRED
823 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
824 -- p_credit_type IN VARCHAR2 REQUIRED
825 -- p_person_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
826 -- p_effective_from_date IN DATE REQUIRED
827 -- p_record_version_number IN NUMBER REQUIRED DEFAULT=1
828 -- x_return_status OUT VARCHAR2 REQUIRED
829 -- x_msg_count OUT VARCHAR2 REQUIRED
830 -- x_msg_data OUT VARCHAR2 REQUIRED
831 --
832 -- History
833 --
834 -- 25-MAY-01 Majid Ansari -Created
835 --
836 --
837
838 PROCEDURE delete_credit_receivers(
839 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
840 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
841 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
842 p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
843 p_debug_mode IN VARCHAR2 DEFAULT 'N',
844 p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
845 p_credit_receiver_id IN NUMBER ,
846 p_project_id IN NUMBER ,
847 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
848 p_credit_type IN VARCHAR2 ,
849 p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
850 p_effective_from_date IN DATE ,
851 p_record_version_number IN NUMBER DEFAULT 1,
852 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
853 x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
854 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
855 ) IS
856
857 l_return_status VARCHAR2(1);
858 l_error_msg_code VARCHAR2(250);
859 l_dummy_char VARCHAR2(1);
860 l_msg_count NUMBER;
861
862 l_effective_to_date DATE;
863
864 l_task_id NUMBER;
865 l_credit_percentage NUMBER;
866 l_person_id NUMBER;
867 BEGIN
868 IF p_commit = FND_API.G_TRUE
869 THEN
870 SAVEPOINT credit_receivers;
871 END IF;
872
873 x_return_status := 'S';
874
875 IF p_validate_only = FND_API.G_TRUE AND
876 p_validation_level > 0
877 THEN
878 IF p_task_id = FND_API.G_MISS_NUM
879 THEN
880 l_task_id := null;
881 ELSE
882 l_task_id := p_task_id;
883 END IF;
884 END IF;
885
886 IF (p_debug_mode = 'Y')
887 THEN
888 pa_debug.debug('Delete_Credit_Receivers PVT: Checking Lock on record');
889 END IF;
890
891 IF p_validate_only <> FND_API.G_TRUE
892 THEN
893 BEGIN
894 SELECT 'x' INTO l_dummy_char
895 FROM pa_credit_receivers
896 WHERE credit_receiver_id = p_credit_receiver_id
897 AND record_version_number = p_record_version_number
898 FOR UPDATE OF record_version_number NOWAIT;
899 EXCEPTION
900 WHEN TIMEOUT_ON_RESOURCE THEN
901 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
902 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
903 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
904 x_return_status := 'E' ;
905 WHEN NO_DATA_FOUND THEN
906 if p_calling_module = 'FORM' then
907 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
908 p_msg_name => 'FORM_RECORD_CHANGED');
909 x_msg_data := 'FORM_RECORD_CHANGED';
910 else
911 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
912 p_msg_name => 'PA_XC_RECORD_CHANGED');
913 x_msg_data := 'PA_XC_RECORD_CHANGED';
914 end if;
915 x_return_status := 'E' ;
916 WHEN OTHERS THEN
917 IF SQLCODE = -54 THEN
918 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
919 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
920 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
921 x_return_status := 'E' ;
922 ELSE
923 raise;
924 END IF;
925 END;
926 ELSE
927 BEGIN
928 SELECT 'x' INTO l_dummy_char
929 FROM pa_credit_receivers
930 WHERE credit_receiver_id = p_credit_receiver_id
931 AND record_version_number = p_record_version_number;
932 EXCEPTION
933 WHEN NO_DATA_FOUND THEN
934 if p_calling_module = 'FORM' then
935 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
936 p_msg_name => 'FORM_RECORD_CHANGED');
937 x_msg_data := 'FORM_RECORD_CHANGED';
938 else
939 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
940 p_msg_name => 'PA_XC_RECORD_CHANGED');
941 x_msg_data := 'PA_XC_RECORD_CHANGED';
942 end if;
943 x_return_status := 'E' ;
944 WHEN OTHERS THEN
945 IF SQLCODE = -54 THEN
946 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
947 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
948 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
949 x_return_status := 'E' ;
950 END IF;
951 END;
952 end if;
953 l_msg_count := FND_MSG_PUB.count_msg;
954
955 IF l_msg_count > 0 THEN
956 x_msg_count := l_msg_count;
957 x_return_status := 'E';
958 RAISE FND_API.G_EXC_ERROR;
959 END IF;
960
961 DELETE FROM pa_credit_receivers
962 WHERE credit_receiver_id = p_credit_receiver_id
963 AND RECORD_VERSION_NUMBER = p_RECORD_VERSION_NUMBER;
964
965 EXCEPTION
966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
967 IF p_commit = FND_API.G_TRUE
968 THEN
969 ROLLBACK TO credit_receivers;
970 END IF;
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
972 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
973 p_procedure_name => 'delete_CREDIT_RECEIVERS',
974 p_error_text => SUBSTRB(SQLERRM,1,240));
975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
976
977 WHEN FND_API.G_EXC_ERROR THEN
978 IF p_commit = FND_API.G_TRUE
979 THEN
980 ROLLBACK TO credit_receivers;
981 END IF;
982 x_return_status := 'E';
983
984 WHEN OTHERS THEN
985 IF p_commit = FND_API.G_TRUE
986 THEN
987 ROLLBACK TO credit_receivers;
988 END IF;
989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
991 p_procedure_name => 'delete_CREDIT_RECEIVERS',
992 p_error_text => SUBSTRB(SQLERRM,1,240));
993 RAISE;
994
995 END delete_credit_receivers;
996
997
998 -- API name : create_billing_assignments
999 -- Type : Private procedure
1003 -- p_commit IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_FALSE
1000 -- Pre-reqs : None
1001 -- Return Value : N/A
1002 -- Prameters
1004 -- p_validate_only IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_TRUE
1005 -- p_validation_level IN NUMBER OPTIONAL DEFAULT=FND_API.G_VALID_LEVEL_FULL
1006 -- p_calling_module IN VARCHAR2 OPTIONAL DEFAULT='SELF_SERVICE'
1007 -- p_debug_mode IN VARCHAR2 OPTIONAL DEFAULT='N'
1008 -- p_max_msg_count IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
1009 -- p_project_id IN NUMBER REQUIRED
1010 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
1011 -- p_billing_extension_id IN NUMBER REQUIRED
1012 -- p_amount IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
1013 -- p_percent IN NUMBER OPTIONAl DEFAULT=FND_API.G_MISS_NUM
1014 -- p_active IN VARCHAR2 REQUIRED
1015 -- x_billing_assignment_id OUT NUMBER REQUIRED
1016 -- x_return_status OUT VARCHAR2 REQUIRED
1017 -- x_msg_count OUT VARCHAR2 REQUIRED
1018 -- x_msg_data OUT VARCHAR2 REQUIRED
1019 --
1020 -- History
1021 --
1022 -- 25-MAY-01 Majid Ansari -Created
1023 --
1024 --
1025
1026 PROCEDURE create_billing_assignments(
1027 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1028 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1029 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1030 p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
1031 p_debug_mode IN VARCHAR2 DEFAULT 'N',
1032 p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1033 p_project_id IN NUMBER ,
1034 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1035 p_billing_extension_id IN NUMBER ,
1036 p_amount IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1037 p_percent IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1038 p_active IN VARCHAR2 ,
1039 x_billing_assignment_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1040 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1041 x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1042 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1043 ) IS
1044
1045 CURSOR cur_proj_all
1046 IS
1047 SELECT org_id, distribution_rule, project_type
1048 FROM pa_projects_all
1049 WHERE project_id = p_project_id;
1050
1051 l_return_status VARCHAR2(1);
1052 l_error_msg_code VARCHAR2(250);
1053 l_dummy_char VARCHAR2(1);
1054 l_msg_count NUMBER;
1055
1056 l_effective_to_date DATE;
1057
1058 l_task_id NUMBER;
1059 l_amount NUMBER;
1060 l_percent NUMBER;
1061
1062 l_rec_proj_all cur_proj_all%ROWTYPE;
1063
1064 l_billing_assignment_id NUMBER;
1065
1066 BEGIN
1067 IF p_commit = FND_API.G_TRUE
1068 THEN
1069 SAVEPOINT billing_assignments;
1070 END IF;
1071
1072 x_return_status := 'S';
1073
1074 IF p_validate_only = FND_API.G_TRUE AND
1075 p_validation_level > 0
1076 THEN
1077 IF p_task_id = FND_API.G_MISS_NUM
1078 THEN
1079 l_task_id := null;
1080 ELSE
1081 l_task_id := p_task_id;
1082 END IF;
1083
1084 IF p_amount = FND_API.G_MISS_NUM
1085 THEN
1086 l_amount := null;
1087 ELSE
1088 l_amount := p_amount;
1089 END IF;
1090
1091 IF p_percent = FND_API.G_MISS_NUM
1092 THEN
1093 l_percent := null;
1094 ELSE
1095 l_percent := p_percent;
1096 END IF;
1097
1098 --Mandatory parameters check
1099 PA_BILLING_SETUP_UTILS.BILL_XTENSION_REQ_CHECK(
1100 p_billing_extension_id => p_billing_extension_id,
1101 x_return_status => l_return_status,
1102 x_error_msg_code => l_error_msg_code );
1103
1104 IF l_return_status = FND_API.G_RET_STS_ERROR
1105 THEN
1106 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1107 p_msg_name => l_error_msg_code);
1108
1109 x_msg_data := l_error_msg_code;
1110 x_return_status := 'E';
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113
1114
1115 IF NOT PA_BILLING_SETUP_UTILS.Duplicate_billing_assignments(
1116 p_project_id => p_project_id,
1117 p_task_id => l_task_id,
1118 p_billing_extension_id => p_billing_extension_id,
1119 p_billing_assignment_id => l_billing_assignment_id,
1120 p_active_flag => p_active,
1121 x_return_status => l_return_status
1122 )
1123 THEN
1124
1125 OPEN cur_proj_all;
1126 FETCH cur_proj_all INTO l_rec_proj_all;
1127 CLOSE cur_proj_all;
1128
1129
1130 SELECT PA_BILLING_ASSIGNMENTS_S.NEXTVAL INTO l_billing_assignment_id
1131 FROM dual;
1132
1133 x_billing_assignment_id := l_billing_assignment_id;
1134 INSERT INTO pa_billing_assignments_all(
1138 PROJECT_ID ,
1135 BILLING_ASSIGNMENT_ID ,
1136 BILLING_EXTENSION_ID ,
1137 PROJECT_TYPE ,
1139 TOP_TASK_ID ,
1140 AMOUNT ,
1141 PERCENTAGE ,
1142 ACTIVE_FLAG ,
1143 CREATION_DATE ,
1144 CREATED_BY ,
1145 LAST_UPDATE_DATE ,
1146 LAST_UPDATED_BY ,
1147 LAST_UPDATE_LOGIN ,
1148 ATTRIBUTE_CATEGORY ,
1149 DISTRIBUTION_RULE ,
1150 ORG_ID ,
1151 RECORD_VERSION_NUMBER )
1152 VALUES ( l_billing_assignment_id,
1153 p_billing_extension_id ,
1154 l_rec_proj_all.project_type ,
1155 p_project_id ,
1156 l_task_id ,
1157 l_amount ,
1158 l_percent ,
1159 p_active ,
1160 sysdate ,
1161 FND_GLOBAL.USER_ID ,
1162 sysdate ,
1163 FND_GLOBAL.USER_ID ,
1164 FND_GLOBAL.LOGIN_ID ,
1165 null ,
1166 l_rec_proj_all.distribution_rule ,
1167 l_rec_proj_all.org_id ,
1168 1
1169 );
1170
1171
1172 ELSE
1173 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1174 p_msg_name => 'PA_PRJ_BILL_ASSGN_EXISTS' );
1175 x_msg_data := 'PA_PRJ_BILL_ASSGN_EXISTS';
1176 x_return_status := 'E';
1177 RAISE FND_API.G_EXC_ERROR;
1178 END IF;
1179 END IF;
1180 EXCEPTION
1181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1182 IF p_commit = FND_API.G_TRUE
1183 THEN
1184 ROLLBACK TO billing_assignments;
1185 END IF;
1186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1187 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
1188 p_procedure_name => 'CREATE_BILLING_ASSIGNMENTS',
1189 p_error_text => SUBSTRB(SQLERRM,1,240));
1190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1191
1192 x_billing_assignment_id := null;
1193
1194 WHEN FND_API.G_EXC_ERROR THEN
1195 IF p_commit = FND_API.G_TRUE
1196 THEN
1197 ROLLBACK TO billing_assignments;
1198 END IF;
1199 x_return_status := 'E';
1200
1201 x_billing_assignment_id := null;
1202
1203 WHEN OTHERS THEN
1204 IF p_commit = FND_API.G_TRUE
1205 THEN
1206 ROLLBACK TO billing_assignments;
1207 END IF;
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1209 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
1210 p_procedure_name => 'CREATE_BILLING_ASSIGNMENTS',
1211 p_error_text => SUBSTRB(SQLERRM,1,240));
1212
1213 x_billing_assignment_id := null;
1214
1215 RAISE;
1216
1217 END create_billing_assignments;
1218
1219
1220
1221 -- API name : update_billing_assignments
1222 -- Type : Private procedure
1223 -- Pre-reqs : None
1224 -- Return Value : N/A
1225 -- Prameters
1226 -- p_commit IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_FALSE
1227 -- p_validate_only IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_TRUE
1228 -- p_validation_level IN NUMBER OPTIONAL DEFAULT=FND_API.G_VALID_LEVEL_FULL
1229 -- p_calling_module IN VARCHAR2 OPTIONAL DEFAULT='SELF_SERVICE'
1230 -- p_debug_mode IN VARCHAR2 OPTIONAL DEFAULT='N'
1231 -- p_max_msg_count IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
1232 -- p_project_id IN NUMBER REQUIRED
1233 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
1234 -- p_billing_extension_id IN number REQUIRED
1235 -- p_amount IN NUMBER REQUIRED
1236 -- p_percent IN NUMBER REQUIRED
1237 -- p_active IN VARCHAR2 OPTIONAL DEFAULT=FND_API.G_MISS_CHAR
1238 -- p_record_version_number IN NUMBER REQUIRED DEFAULT=1
1239 -- x_return_status OUT VARCHAR2 REQUIRED
1240 -- x_msg_count OUT VARCHAR2 REQUIRED
1241 -- x_msg_data OUT VARCHAR2 REQUIRED
1242 --
1243 -- History
1244 --
1245 -- 25-MAY-01 Majid Ansari -Created
1246 --
1247 --
1248
1249 PROCEDURE update_billing_assignments(
1250 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1251 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1252 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1253 p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
1254 p_debug_mode IN VARCHAR2 DEFAULT 'N',
1255 p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1256 p_project_id IN NUMBER ,
1257 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1258 p_billing_extension_id IN NUMBER ,
1259 p_billing_assignment_id IN NUMBER ,
1260 p_amount IN NUMBER ,
1261 p_percent IN NUMBER ,
1262 p_active IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1263 p_record_version_number IN NUMBER DEFAULT 1,
1264 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1265 x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1266 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1267 ) IS
1268
1269 l_return_status VARCHAR2(1);
1270 l_error_msg_code VARCHAR2(250);
1271 l_dummy_char VARCHAR2(1);
1272 l_msg_count NUMBER;
1273
1274 l_effective_to_date DATE;
1275
1276 l_task_id NUMBER;
1277 l_amount NUMBER;
1278 l_percent NUMBER;
1279
1280 BEGIN
1281 IF p_commit = FND_API.G_TRUE
1282 THEN
1283 SAVEPOINT billing_assignments;
1284 END IF;
1285
1286 x_return_status := 'S';
1287
1288 IF p_validate_only = FND_API.G_TRUE AND
1289 p_validation_level > 0
1290 THEN
1291 IF p_task_id = FND_API.G_MISS_NUM
1292 THEN
1293 l_task_id := null;
1294 ELSE
1295 l_task_id := p_task_id;
1296 END IF;
1297
1298 IF p_amount = FND_API.G_MISS_NUM
1299 THEN
1300 l_amount := null;
1301 ELSE
1302 l_amount := p_amount;
1303 END IF;
1304
1305 IF p_percent = FND_API.G_MISS_NUM
1306 THEN
1307 l_percent := null;
1308 ELSE
1309 l_percent := p_percent;
1310 END IF;
1311
1312 --Mandatory parameters check
1313 PA_BILLING_SETUP_UTILS.BILL_XTENSION_REQ_CHECK(
1314 p_billing_extension_id => p_billing_extension_id,
1315 x_return_status => l_return_status,
1316 x_error_msg_code => l_error_msg_code );
1317
1318 IF l_return_status = FND_API.G_RET_STS_ERROR
1319 THEN
1320 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1321 p_msg_name => l_error_msg_code);
1322
1323 x_msg_data := l_error_msg_code;
1324 x_return_status := 'E';
1325 RAISE FND_API.G_EXC_ERROR;
1326 END IF;
1327
1328 IF (p_debug_mode = 'Y')
1329 THEN
1330 pa_debug.debug('Update Billing Assignments PVT: Checking Lock on record');
1331 END IF;
1332
1333 IF p_validate_only <> FND_API.G_TRUE
1334 THEN
1335 BEGIN
1336 SELECT 'x' INTO l_dummy_char
1337 FROM pa_billing_assignments_all
1338 WHERE billing_assignment_id = p_billing_assignment_id
1339 AND record_version_number = p_record_version_number
1340 FOR UPDATE OF record_version_number NOWAIT;
1341 EXCEPTION
1342 WHEN TIMEOUT_ON_RESOURCE THEN
1343 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1344 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1345 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1346 x_return_status := 'E' ;
1347 WHEN NO_DATA_FOUND THEN
1348 if p_calling_module = 'FORM' then
1349 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1350 p_msg_name => 'FORM_RECORD_CHANGED');
1351 x_msg_data := 'FORM_RECORD_CHANGED';
1352 else
1353 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1354 p_msg_name => 'PA_XC_RECORD_CHANGED');
1355 x_msg_data := 'PA_XC_RECORD_CHANGED';
1356 end if;
1357 x_return_status := 'E' ;
1358 WHEN OTHERS THEN
1359 IF SQLCODE = -54 THEN
1360 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1361 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1362 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1363 x_return_status := 'E' ;
1364 ELSE
1365 raise;
1366 END IF;
1367 END;
1368 ELSE
1369 BEGIN
1370 SELECT 'x' INTO l_dummy_char
1371 FROM pa_billing_assignments_all
1372 WHERE billing_assignment_id = p_billing_assignment_id
1373 AND record_version_number = p_record_version_number;
1374 EXCEPTION
1375 WHEN NO_DATA_FOUND THEN
1376 if p_calling_module = 'FORM' then
1377 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1378 p_msg_name => 'FORM_RECORD_CHANGED');
1379 x_msg_data := 'FORM_RECORD_CHANGED';
1380 else
1381 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1382 p_msg_name => 'PA_XC_RECORD_CHANGED');
1383 x_msg_data := 'PA_XC_RECORD_CHANGED';
1384 end if;
1385 x_return_status := 'E' ;
1386 WHEN OTHERS THEN
1387 IF SQLCODE = -54 THEN
1388 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1389 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1390 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1391 x_return_status := 'E' ;
1392 END IF;
1393 END;
1394 end if;
1395 l_msg_count := FND_MSG_PUB.count_msg;
1396
1397 IF l_msg_count > 0 THEN
1398 x_msg_count := l_msg_count;
1399 x_return_status := 'E';
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402
1403 IF NOT PA_BILLING_SETUP_UTILS.Duplicate_billing_assignments(
1404 p_project_id => p_project_id,
1405 p_task_id => l_task_id,
1406 p_billing_extension_id => p_billing_extension_id,
1407 p_billing_assignment_id => p_billing_assignment_id,
1408 p_active_flag => p_active,
1409 x_return_status => l_return_status
1410 )
1411 THEN
1412 UPDATE pa_billing_assignments_all
1413 SET
1414 billing_extension_id = p_billing_extension_id,
1415 AMOUNT = l_amount ,
1416 PERCENTAGE = l_percent ,
1417 ACTIVE_FLAG = p_active ,
1418 LAST_UPDATE_DATE = SYSDATE ,
1419 LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
1420 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1421 RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 0 ) + 1
1422 WHERE billing_assignment_id = p_billing_assignment_id
1423 AND RECORD_VERSION_NUMBER = p_record_version_number;
1424 ELSE
1425 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1426 p_msg_name => 'PA_PRJ_BILL_ASSGN_EXISTS' );
1427 x_msg_data := 'PA_PRJ_BILL_ASSGN_EXISTS';
1428 x_return_status := 'E';
1429 RAISE FND_API.G_EXC_ERROR;
1430 END IF;
1431
1432 END IF;
1433 EXCEPTION
1434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435 IF p_commit = FND_API.G_TRUE
1436 THEN
1437 ROLLBACK TO billing_assignments;
1438 END IF;
1439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1440 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
1441 p_procedure_name => 'UPDATE_BILLING_ASSIGNMENTS',
1442 p_error_text => SUBSTRB(SQLERRM,1,240));
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1444
1445 WHEN FND_API.G_EXC_ERROR THEN
1446 IF p_commit = FND_API.G_TRUE
1447 THEN
1448 ROLLBACK TO billing_assignments;
1449 END IF;
1450 x_return_status := 'E';
1451
1452 WHEN OTHERS THEN
1453 IF p_commit = FND_API.G_TRUE
1454 THEN
1455 ROLLBACK TO billing_assignments;
1456 END IF;
1457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1458 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
1459 p_procedure_name => 'UPDATE_BILLING_ASSIGNMENTS',
1460 p_error_text => SUBSTRB(SQLERRM,1,240));
1461 RAISE;
1462
1463 END update_billing_assignments;
1464
1465
1466 -- API name : delete_billing_assignments
1467 -- Type : Private procedure
1468 -- Pre-reqs : None
1469 -- Return Value : N/A
1470 -- Prameters
1471 -- p_commit IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_FALSE
1472 -- p_validate_only IN VARCHAR2 REQUIRED DEFAULT=FND_API.G_TRUE
1473 -- p_validation_level IN NUMBER OPTIONAL DEFAULT=FND_API.G_VALID_LEVEL_FULL
1474 -- p_calling_module IN VARCHAR2 OPTIONAL DEFAULT='SELF_SERVICE'
1475 -- p_debug_mode IN VARCHAR2 OPTIONAL DEFAULT='N'
1476 -- p_max_msg_count IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
1477 -- p_project_id IN NUMBER REQUIRED
1478 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
1479 -- p_billing_extension_id IN NUMBER REQUIRED
1480 -- p_billing_assignment_id IN NUMBER ,
1481 -- p_record_version_number IN NUMBER REQUIRED DEFAULT=1
1482 -- x_return_status OUT VARCHAR2 REQUIRED
1483 -- x_msg_count OUT VARCHAR2 REQUIRED
1484 -- x_msg_data OUT VARCHAR2 REQUIRED
1485 --
1486 -- History
1487 --
1488 -- 25-MAY-01 Majid Ansari -Created
1489 --
1490 --
1491
1492 PROCEDURE delete_billing_assignments(
1493 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1494 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1495 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1496 p_calling_module IN VARCHAR2 DEFAULT 'SELF_SERVICE',
1497 p_debug_mode IN VARCHAR2 DEFAULT 'N',
1498 p_max_msg_count IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1499 p_project_id IN NUMBER ,
1500 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1501 p_billing_extension_id IN NUMBER ,
1502 p_billing_assignment_id IN NUMBER ,
1503 p_record_version_number IN NUMBER DEFAULT 1,
1504 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1505 x_msg_count OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1506 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1507 ) IS
1508
1509 l_return_status VARCHAR2(1);
1510 l_error_msg_code VARCHAR2(250);
1511 l_dummy_char VARCHAR2(1);
1512 l_msg_count NUMBER;
1513
1514 l_effective_to_date DATE;
1515
1516 l_task_id NUMBER;
1517 l_amount NUMBER;
1518 l_percent NUMBER;
1519
1520 BEGIN
1521 IF p_commit = FND_API.G_TRUE
1522 THEN
1523 SAVEPOINT billing_assignments;
1524 END IF;
1525
1526 x_return_status := 'S';
1527
1528 IF p_validate_only = FND_API.G_TRUE AND
1529 p_validation_level > 0
1530 THEN
1531 IF p_task_id = FND_API.G_MISS_NUM
1532 THEN
1533 l_task_id := null;
1534 ELSE
1535 l_task_id := p_task_id;
1536 END IF;
1537
1538 IF (p_debug_mode = 'Y')
1539 THEN
1540 pa_debug.debug('Delete Billing Assignments PVT: Checking Lock on record');
1541 END IF;
1542
1543 IF p_validate_only <> FND_API.G_TRUE
1544 THEN
1545 BEGIN
1546 SELECT 'x' INTO l_dummy_char
1547 FROM pa_billing_assignments_all
1548 WHERE billing_extension_id = p_billing_extension_id
1549 AND record_version_number = p_record_version_number
1550 FOR UPDATE OF record_version_number NOWAIT;
1551 EXCEPTION
1552 WHEN TIMEOUT_ON_RESOURCE THEN
1553 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1554 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1555 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1556 x_return_status := 'E' ;
1557 WHEN NO_DATA_FOUND THEN
1558 if p_calling_module = 'FORM' then
1559 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1560 p_msg_name => 'FORM_RECORD_CHANGED');
1561 x_msg_data := 'FORM_RECORD_CHANGED';
1562 else
1563 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1564 p_msg_name => 'PA_XC_RECORD_CHANGED');
1565 x_msg_data := 'PA_XC_RECORD_CHANGED';
1566 end if;
1567 x_return_status := 'E' ;
1568 WHEN OTHERS THEN
1569 IF SQLCODE = -54 THEN
1570 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1571 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1572 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1573 x_return_status := 'E' ;
1574 ELSE
1575 raise;
1576 END IF;
1577 END;
1578 ELSE
1579 BEGIN
1580 SELECT 'x' INTO l_dummy_char
1581 FROM pa_billing_assignments_all
1582 WHERE billing_extension_id = p_billing_extension_id
1583 AND record_version_number = p_record_version_number;
1584 EXCEPTION
1585 WHEN NO_DATA_FOUND THEN
1586 if p_calling_module = 'FORM' then
1587 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1588 p_msg_name => 'FORM_RECORD_CHANGED');
1589 x_msg_data := 'FORM_RECORD_CHANGED';
1590 else
1591 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1592 p_msg_name => 'PA_XC_RECORD_CHANGED');
1593 x_msg_data := 'PA_XC_RECORD_CHANGED';
1594 end if;
1595 x_return_status := 'E' ;
1596 WHEN OTHERS THEN
1597 IF SQLCODE = -54 THEN
1598 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1599 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1600 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1601 x_return_status := 'E' ;
1602 END IF;
1603 END;
1604 end if;
1605 l_msg_count := FND_MSG_PUB.count_msg;
1606
1607 IF l_msg_count > 0 THEN
1608 x_msg_count := l_msg_count;
1609 x_return_status := 'E';
1610 RAISE FND_API.G_EXC_ERROR;
1611 END IF;
1612
1613
1614 DELETE FROM pa_billing_assignments_all
1615 WHERE billing_extension_id = p_billing_extension_id
1616 AND RECORD_VERSION_NUMBER = p_record_version_number;
1617
1618 END IF;
1619 EXCEPTION
1620 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1621 IF p_commit = FND_API.G_TRUE
1622 THEN
1623 ROLLBACK TO billing_assignments;
1624 END IF;
1625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
1627 p_procedure_name => 'DELETE_BILLING_ASSIGNMENTS',
1628 p_error_text => SUBSTRB(SQLERRM,1,240));
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630
1631 WHEN FND_API.G_EXC_ERROR THEN
1632 IF p_commit = FND_API.G_TRUE
1633 THEN
1634 ROLLBACK TO billing_assignments;
1635 END IF;
1636 x_return_status := 'E';
1637
1638 WHEN OTHERS THEN
1639 IF p_commit = FND_API.G_TRUE
1640 THEN
1641 ROLLBACK TO billing_assignments;
1642 END IF;
1643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1644 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_BILLING_SETUP_PVT',
1645 p_procedure_name => 'DELETE_BILLING_ASSIGNMENTS',
1646 p_error_text => SUBSTRB(SQLERRM,1,240));
1647 RAISE;
1648
1649 END delete_billing_assignments;
1650
1651 END PA_BILLING_SETUP_PVT;