[Home] [Help]
PACKAGE BODY: APPS.CN_PAY_ELEMENT_INPUTS_PVT
Source
1 PACKAGE BODY CN_PAY_ELEMENT_INPUTS_PVT AS
2 /* $Header: cnvqpib.pls 115.7 2002/11/21 21:16:25 hlchen ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_QUOTA_PAY_ELEMENT_INPUT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvqpeb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := FND_GLOBAL.USER_ID;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := FND_GLOBAL.USER_ID;
10 G_LAST_UPDATE_LOGIN NUMBER := FND_GLOBAL.LOGIN_ID;
11
12 G_PROGRAM_TYPE VARCHAR2(30);
13
14 --|========================================================================
15 --| procedure : get object ID
16 --| Desc :
17 --|========================================================================
18 PROCEDURE get_object_id( p_object_name IN VARCHAR2,
19 p_object_type IN VARCHAR2,
20 p_table_id IN NUMBER,
21 x_tab_col_name OUT NOCOPY VARCHAR2,
22 x_object_id OUT NOCOPY NUMBER) IS
23 BEGIN
24
25 IF p_object_type = 'COL' THEN
26
27 SELECT object_id, NAME INTO x_object_id, x_tab_col_name
28 FROM cn_objects
29 WHERE user_name = p_object_name
30 AND object_type = p_object_type
31 AND table_id = p_table_id;
32
33 ELSE
34
35 SELECT object_id, name INTO x_object_id, x_tab_col_name
36 FROM cn_objects
37 WHERE user_name = p_object_name
38 AND object_type = p_object_type
39 AND name IN ( 'CN_PAYMENT_TRANSACTIONS', 'CN_PAYRUNS', 'CN_SALESREPS');
40 END IF;
41
42 EXCEPTION
43 WHEN no_data_found THEN
44 x_object_id := NULL;
45 END get_object_id;
46
47 --|========================================================================
48 --| Procedure : valid_pei_mapping
49 --| Desc : Procedure to validate quota pay element_input mapping
50 --|========================================================================
51 PROCEDURE valid_pei_mapping
52 (
53 x_return_status OUT NOCOPY VARCHAR2 ,
54 x_msg_count OUT NOCOPY NUMBER ,
55 x_msg_data OUT NOCOPY VARCHAR2 ,
56 p_pay_element_input_rec IN pay_element_input_rec_type
57 := G_MISS_PAY_ELEMENT_INPUT_REC,
58 p_table_name IN VARCHAR2,
59 p_column_name IN VARCHAR2,
60 p_action IN VARCHAR2,
61 p_loading_status IN VARCHAR2,
62 x_loading_status OUT NOCOPY VARCHAR2
63 )
64 IS
65 l_api_name CONSTANT VARCHAR2(30) := 'Valid_Pei_Mapping';
66 l_null_date CONSTANT DATE := to_date('31-12-4000','DD-MM-YYYY');
67
68 l_dummy NUMBER;
69 l_count NUMBER;
70
71 l_id NUMBER;
72
73 l_effective_start_date DATE;
74 l_effective_end_date DATE;
75
76
77 BEGIN
78 -- Initialize API return status to success
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80 x_loading_status := p_loading_status;
81 -- API body
82
83 --
84 -- Only One column is allowed from posting detail
85 --
86 IF p_table_name = 'CN_POSTING_DETAILS' and
87 p_column_name <> 'PAYMENT_AMOUNT' THEN
88 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
89 FND_MESSAGE.Set_Name('CN', 'CN_CANNOT_USE_OTHER_COL');
90 FND_MSG_PUB.Add;
91 END IF;
92 x_loading_status := 'CN_CANNOT_USE_OTHER_COL';
93 RAISE FND_API.G_EXC_ERROR ;
94 END IF;
95
96 --
97 -- check table name is not null
98 --
99 IF p_pay_element_input_rec.table_name IS NULL THEN
100 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
101 FND_MESSAGE.Set_Name('CN', 'CN_TABLE_NAME_NOT_NULL');
102 FND_MSG_PUB.Add;
103 END IF;
104 x_loading_status := 'CN_TABLE_NAME_NOT_NULL';
105 RAISE FND_API.G_EXC_ERROR ;
106 END IF;
107
108 --
109 -- check col name is not null
110 --
111 IF p_pay_element_input_rec.column_name IS NULL THEN
112 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
113 FND_MESSAGE.Set_Name('CN', 'CN_COL_NAME_NOT_NULL');
114 FND_MSG_PUB.Add;
115 END IF;
116 x_loading_status := 'CN_COLUMN_NAME_NOT_NULL';
117 RAISE FND_API.G_EXC_ERROR ;
118 END IF;
119
120 --
121 -- check input exists and line number exists
122 --
123 IF p_pay_element_input_rec.quota_pay_element_id IS NULL
124 THEN
125 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
126 FND_MESSAGE.Set_Name('CN', 'CN_PAY_ELEMENT_MAP_NOT_EXISTS');
127 FND_MSG_PUB.Add;
128 END IF;
129 x_loading_status := 'CN_PAY_ELEMENT_MAP_NOT_EXISTS';
130 RAISE FND_API.G_EXC_ERROR ;
131 END IF;
132
133
134 --
135 -- check element input_id and line number is not null
136 --
137 IF p_pay_element_input_rec.element_input_id IS NULL or
138 p_pay_element_input_rec.line_number IS NULL THEN
139 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
140 FND_MESSAGE.Set_Name('CN', 'CN_ELEMENT_INPUT_NOT_EXISTS');
141 FND_MSG_PUB.Add;
142 END IF;
143 x_loading_status := 'CN_ELEMENT_INPUT_NOT_EXISTS';
144 RAISE FND_API.G_EXC_ERROR ;
145 END IF;
146
147 -- check table id exists
148 IF p_pay_element_input_rec.table_name IS NOT NULL and
149 p_pay_element_input_rec.tab_object_id IS NULL THEN
150 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
151 FND_MESSAGE.Set_Name('CN', 'CN_TABLE_NAME_NOT_EXISTS');
152 FND_MSG_PUB.Add;
153 END IF;
154 x_loading_status := 'CN_TABLE_NAME_NOT_EXISTS';
155 RAISE FND_API.G_EXC_ERROR ;
156 END IF;
157
158 -- check column id is exists
159 IF p_pay_element_input_rec.column_name IS NOT NULL and
160 p_pay_element_input_rec.col_object_id IS NULL THEN
161 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
162 FND_MESSAGE.Set_Name('CN', 'CN_COL_NAME_NOT_EXISTS');
163 FND_MSG_PUB.Add;
164 END IF;
165 x_loading_status := 'CN_COL_NAME_NOT_EXISTS';
166 RAISE FND_API.G_EXC_ERROR ;
167 END IF;
168
169 --
170 -- duplication of payment Amount is not allowed
171 --
172 IF P_table_name = 'CN_POSTING_DETAILS' and
173 p_column_name = 'PAYMENT_AMOUNT' THEN
174
175 -- Check for duplicate
176 BEGIN
177 SELECT 1 INTO l_dummy FROM dual
178 WHERE NOT EXISTS
179 ( SELECT 1
180 FROM cn_pay_element_inputs
181 WHERE tab_object_id = p_pay_element_input_rec.tab_object_id
182 AND col_object_id = p_pay_element_input_rec.col_object_id
183 AND element_type_id = p_pay_element_input_rec.element_type_id
184 AND quota_pay_element_id = p_pay_element_input_rec.quota_pay_element_id
185 AND ((p_pay_element_input_rec.pay_element_input_id IS NOT NULL AND
186 pay_element_input_id <> p_pay_element_input_rec.pay_element_input_id)
187 OR
188 (p_pay_element_input_rec.pay_element_input_id IS NULL))
189 );
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
193 FND_MESSAGE.Set_Name('CN', 'CN_DUPLICATE_AMOUNT_INPUT');
194 FND_MSG_PUB.Add;
195 END IF;
196 x_loading_status := 'CN_DUPLICATE_AMOUNT_INPUT';
197 RAISE FND_API.G_EXC_ERROR ;
198 END;
199
200 END IF;
201
202 --
203 -- Check for Element input exist in Payroll
204 --
205 SELECT count(*) INTO l_count
206 FROM
207 pay_input_values_f piv,
208 pay_element_types_f pet,
209 cn_quota_pay_elements cqpe,
210 gl_sets_of_books glsob,
211 cn_repositories cnr
212 where
213 cnr.set_of_books_id = glsob.set_of_books_id
214 AND pet.input_currency_code = glsob.currency_code
215 AND cqpe.quota_pay_element_id = p_pay_element_input_rec.quota_pay_element_id
216 AND cqpe.pay_element_type_id = pet.element_type_id
217 AND cqpe.start_date >= pet.effective_start_date
218 AND cqpe.end_date <= pet.effective_end_date
219 AND pet.effective_start_date>= piv.effective_start_date
220 AND pet.effective_end_date <= piv.effective_end_date
221 AND pet.element_type_id = piv.element_type_id
222 AND piv.element_type_id = pet.element_type_id
223 AND piv.input_value_id = p_pay_element_input_rec.element_input_id
224 AND piv.display_sequence = p_pay_element_input_rec.line_number
225 AND pet.element_type_id = piv.element_type_id ;
226
227 -- If zero then input value not found
228 IF l_count = 0 THEN
229
230 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
231 FND_MESSAGE.Set_Name('CN', 'CN_INPUT_VALUES_NOT_FOUND');
232 FND_MSG_PUB.Add;
233 END IF;
234 x_loading_status := 'CN_INPUT_VALUES_NOT_FOUND';
235 RAISE FND_API.G_EXC_ERROR ;
236
237 END IF;
238
239 -- End of validation
240
241 EXCEPTION
242 WHEN FND_API.G_EXC_ERROR THEN
243 x_return_status := FND_API.G_RET_STS_ERROR ;
244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
246 x_loading_status := 'UNEXPECTED_ERR';
247 WHEN OTHERS THEN
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
249 x_loading_status := 'UNEXPECTED_ERR';
250 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
251 THEN
252 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
253 END IF;
254
255 END valid_pei_mapping;
256 --|========================================================================
257 --| Procedure : Create_pay_element_input
258 --| Desc : Procedure to create a new pay element_input input
259 --|========================================================================
260
261 PROCEDURE Create_pay_element_input
262 (
263 p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2,
265 p_commit IN VARCHAR2,
266 p_validation_level IN NUMBER,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2,
270 p_pay_element_input_rec IN pay_element_input_rec_type
271 := G_MISS_PAY_ELEMENT_INPUT_REC,
272 x_pay_element_input_id OUT NOCOPY NUMBER,
273 x_loading_status OUT NOCOPY VARCHAR2
274 ) IS
275
276 l_api_name CONSTANT VARCHAR2(30) := 'Create_Pay_Element_Input';
277 l_api_version CONSTANT NUMBER := 1.0;
278
279 l_pay_element_input_rec pay_element_input_rec_type := G_MISS_PAY_ELEMENT_INPUT_REC;
280 l_action VARCHAR2(30) := 'CREATE';
281
282 l_table_name cn_objects.name%TYPE;
283 l_column_name cn_objects.name%TYPE;
284
285
286 BEGIN
287 x_pay_element_input_id := 0;
288
289 -- Standard Start of API savepoint
290 SAVEPOINT Create_pay_element_input;
291 -- Standard call to check for call compatibility.
292 IF NOT FND_API.compatible_api_call
293 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
294 THEN
295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
296 END IF;
297
298 -- Initialize message list if p_init_msg_list is set to TRUE.
299 IF FND_API.to_Boolean( p_init_msg_list ) THEN
300 FND_MSG_PUB.initialize;
301 END IF;
302
303 -- Initialize API return status to success
304 x_return_status := FND_API.G_RET_STS_SUCCESS;
305 x_loading_status := 'CN_INSERTED';
306 -- Assign the parameter to a local variable
307
308 l_pay_element_input_rec := p_pay_element_input_rec;
309
310 -- Trim spaces before/after user input string, get Value-Id para assigned
311 SELECT p_pay_element_input_rec.quota_pay_element_id,
312 p_pay_element_input_rec.element_type_id,
313 p_pay_element_input_rec.line_number,
314 p_pay_element_input_rec.element_input_id,
315 Decode(p_pay_element_input_rec.table_name,
316 FND_API.G_MISS_CHAR, NULL ,
317 p_pay_element_input_rec.table_name),
318 Decode(p_pay_element_input_rec.column_name,
319 FND_API.G_MISS_CHAR, NULL ,
320 p_pay_element_input_rec.column_name)
321 INTO
322 l_pay_element_input_rec.quota_pay_element_id,
323 l_pay_element_input_rec.element_type_id,
324 l_pay_element_input_rec.line_number,
325 l_pay_element_input_rec.element_input_id,
326 l_pay_element_input_rec.table_name,
327 l_pay_element_input_rec.column_name
328 FROM dual;
329
330 -- get table
331
332 get_object_id(l_pay_element_input_rec.table_name,
333 'TBL',
334 null,
335 l_table_name,
336 l_pay_element_input_rec.tab_object_id);
337
338
339 -- get column
340 get_object_id(l_pay_element_input_rec.column_name,
341 'COL',
342 l_pay_element_input_rec.tab_object_id,
343 l_column_name,
344 l_pay_element_input_rec.col_object_id );
345
346 --
347 -- Valid payment plan assignment
348 --
349
350 valid_pei_mapping
351 ( x_return_status => x_return_status,
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data,
354 p_pay_element_input_rec => l_pay_element_input_rec,
355 p_table_name => l_table_name,
356 p_column_name => l_column_name,
357 p_action => l_action,
358 p_loading_status => x_loading_status,
359 x_loading_status => x_loading_status
360 );
361 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
362 RAISE FND_API.G_EXC_ERROR ;
363 ELSE
364 -- Create cn_pay_element_inputs
365
366 cn_pay_element_inputs_pkg.insert_row
367 (x_pay_element_input_id => l_pay_element_input_rec.pay_element_input_id
368 ,p_quota_pay_element_id => l_pay_element_input_rec.quota_pay_element_id
369 ,p_element_input_id => l_pay_element_input_rec.element_input_id
370 ,p_element_type_id => l_pay_element_input_rec.element_type_id
371 ,p_tab_object_id => l_pay_element_input_rec.tab_object_id
372 ,p_col_object_id => l_pay_element_input_rec.col_object_id
373 ,p_line_number => null -- we are not mainited here
374 ,p_start_date => null -- not using now
375 ,p_end_date => null -- not using now
376 ,p_last_update_date => G_LAST_UPDATE_DATE
377 ,p_last_updated_by => G_LAST_UPDATED_BY
378 ,p_creation_date => G_CREATION_DATE
379 ,p_created_by => G_CREATED_BY
380 ,p_last_update_login => G_LAST_UPDATE_LOGIN
381 );
382 END IF;
383 --
384 -- End of API body.
385 --
386
387 -- Standard check of p_commit.
388 IF FND_API.To_Boolean( p_commit ) THEN
389 COMMIT WORK;
390 END IF;
391 -- Standard call to get message count and if count is 1, get message info.
392 FND_MSG_PUB.Count_And_Get
393 (
394 p_count => x_msg_count ,
395 p_data => x_msg_data ,
396 p_encoded => FND_API.G_FALSE
397 );
398
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 ROLLBACK TO Create_pay_element_input;
402 x_return_status := FND_API.G_RET_STS_ERROR ;
403 FND_MSG_PUB.Count_And_Get
404 (
405 p_count => x_msg_count ,
406 p_data => x_msg_data ,
407 p_encoded => FND_API.G_FALSE
408 );
409
410 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
411 ROLLBACK TO Create_pay_element_input;
412 x_loading_status := 'UNEXPECTED_ERR';
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
414 FND_MSG_PUB.Count_And_Get
415 (
416 p_count => x_msg_count ,
417 p_data => x_msg_data ,
418 p_encoded => FND_API.G_FALSE
419 );
420 WHEN OTHERS THEN
421 ROLLBACK TO Create_pay_element_input;
422 x_loading_status := 'UNEXPECTED_ERR';
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
424 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
425 THEN
426 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
427 END IF;
428 FND_MSG_PUB.Count_And_Get
429 (
430 p_count => x_msg_count ,
431 p_data => x_msg_data ,
432 p_encoded => FND_API.G_FALSE
433 );
434
435 END create_pay_element_input;
436
437 --|========================================================================
438 --| Procedure : Update_pay_element_input
439 --| Desc :
440 --|========================================================================
441
442 PROCEDURE Update_pay_element_input
443 (
444 p_api_version IN NUMBER,
445 p_init_msg_list IN VARCHAR2,
446 p_commit IN VARCHAR2,
447 p_validation_level IN NUMBER,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_msg_count OUT NOCOPY NUMBER,
450 x_msg_data OUT NOCOPY VARCHAR2,
451 po_pay_element_input_rec IN pay_element_input_rec_type
452 := G_MISS_pay_element_input_rec,
453 p_pay_element_input_rec IN pay_element_input_rec_type:=G_MISS_PAY_ELEMENT_INPUT_REC,
454 x_loading_status OUT NOCOPY VARCHAR2
455 ) IS
456
457 l_api_name CONSTANT VARCHAR2(30) := 'Uupdate_Pay_Element_Input';
458 l_api_version CONSTANT NUMBER := 1.0;
459
460 l_pay_element_input_rec pay_element_input_rec_type := G_MISS_PAY_ELEMENT_INPUT_REC ;
461 l_action VARCHAR2(30) := 'UPDATE';
462
463 l_column_name cn_objects.name%TYPE;
464 l_table_name cn_objects.name%TYPE;
465
466 BEGIN
467
468 -- Standard Start of API savepoint
469 SAVEPOINT Update_pay_element_input;
470
471 -- Standard call to check for call compatibility.
472 IF NOT FND_API.compatible_api_call
473 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
474 THEN
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
476 END IF;
477 -- Initialize message list if p_init_msg_list is set to TRUE.
478 IF FND_API.to_Boolean( p_init_msg_list ) THEN
479 FND_MSG_PUB.initialize;
480 END IF;
481 -- Initialize API return status to success
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483 x_loading_status := 'CN_UPDATED';
484
485 -- Assign the parameter to a local variable
486 l_pay_element_input_rec := p_pay_element_input_rec;
487
488 -- Trim spaces before/after user input string (New record) if missing,
489 -- assign the old value into it
490
491 SELECT
492 Decode(p_pay_element_input_rec.pay_element_name,
493 FND_API.G_MISS_CHAR, NULL ,
494 p_pay_element_input_rec.pay_element_name),
495 Decode(p_pay_element_input_rec.table_name,
496 FND_API.G_MISS_CHAR, NULL ,
497 p_pay_element_input_rec.table_name),
498 Decode(p_pay_element_input_rec.column_name,
499 FND_API.G_MISS_CHAR, NULL ,
500 p_pay_element_input_rec.column_name),
501 Decode(p_pay_element_input_rec.pay_input_name,
502 FND_API.G_MISS_CHAR, NULL ,
503 p_pay_element_input_rec.pay_input_name),
504 Decode(p_pay_element_input_rec.line_number,
505 FND_API.G_MISS_NUM, NULL ,
506 p_pay_element_input_rec.line_number),
507 Decode(p_pay_element_input_rec.pay_element_input_id,
508 FND_API.G_MISS_NUM, NULL ,
509 p_pay_element_input_rec.pay_element_input_id),
510 Decode(p_pay_element_input_rec.element_input_id,
511 FND_API.G_MISS_NUM, NULL ,
512 p_pay_element_input_rec.element_input_id),
513 Decode(p_pay_element_input_rec.element_type_id,
514 FND_API.G_MISS_NUM, NULL ,
515 p_pay_element_input_rec.element_type_id),
516 Decode(p_pay_element_input_rec.tab_object_id,
517 FND_API.G_MISS_NUM, NULL ,
518 p_pay_element_input_rec.tab_object_id),
519 Decode(p_pay_element_input_rec.col_object_id,
520 FND_API.G_MISS_NUM, NULL ,
521 p_pay_element_input_rec.col_object_id),
522 Decode(p_pay_element_input_rec.start_date,
523 FND_API.G_MISS_DATE, NULL ,
524 p_pay_element_input_rec.start_date),
525 Decode(p_pay_element_input_rec.end_date,
526 FND_API.G_MISS_DATE, NULL ,
527 p_pay_element_input_rec.end_date),
528 Decode(p_pay_element_input_rec.quota_pay_element_id,
529 FND_API.G_MISS_NUM, NULL ,
530 p_pay_element_input_rec.quota_pay_element_id)
531 INTO
532 l_pay_element_input_rec.pay_element_name,
533 l_pay_element_input_rec.table_name,
534 l_pay_element_input_rec.column_name,
535 l_pay_element_input_rec.pay_input_name,
536 l_pay_element_input_rec.line_number,
537 l_pay_element_input_rec.pay_element_input_id,
538 l_pay_element_input_rec.element_input_id,
539 l_pay_element_input_rec.element_type_id,
540 l_pay_element_input_rec.tab_object_id,
541 l_pay_element_input_rec.col_object_id,
542 l_pay_element_input_rec.start_date,
543 l_pay_element_input_rec.end_date,
544 l_pay_element_input_rec.quota_pay_element_id
545 FROM dual;
546
547
548 get_object_id(l_pay_element_input_rec.table_name,
549 'TBL',
550 null,
551 l_table_name,
552 l_pay_element_input_rec.tab_object_id);
553
554
555 -- get column
556 get_object_id(l_pay_element_input_rec.column_name,
557 'COL',
558 l_pay_element_input_rec.tab_object_id,
559 l_column_name,
560 l_pay_element_input_rec.col_object_id );
561
562 --
563 -- Valid payment plan assignvnment
564 --
565 valid_pei_mapping
566 ( x_return_status => x_return_status,
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data,
569 p_pay_element_input_rec => l_pay_element_input_rec,
570 p_table_name => l_table_name,
571 p_column_name => l_column_name,
572 p_action => l_action,
573 p_loading_status => x_loading_status,
574 x_loading_status => x_loading_status
575 );
576
577 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
578 RAISE FND_API.G_EXC_ERROR ;
579 ELSE
580 -- Update
581
582 cn_pay_element_inputs_pkg.update_row
583 (p_pay_element_input_id => l_pay_element_input_rec.pay_element_input_id
584 ,p_quota_pay_element_id => l_pay_element_input_rec.quota_pay_element_id
585 ,p_element_input_id => l_pay_element_input_rec.element_input_id
586 ,p_element_type_id => l_pay_element_input_rec.element_type_id
587 ,p_tab_object_id => l_pay_element_input_rec.tab_object_id
588 ,p_col_object_id => l_pay_element_input_rec.col_object_id
589 ,p_line_number => null -- not using
590 ,p_last_update_date => G_LAST_UPDATE_DATE
591 ,p_last_updated_by => G_LAST_UPDATED_BY
592 ,p_last_update_login => G_LAST_UPDATE_LOGIN
593 );
594
595
596 END IF;
597 -- Standard check of p_commit.
598 IF FND_API.To_Boolean( p_commit ) THEN
599 COMMIT WORK;
600 END IF;
601 -- Standard call to get message count and if count is 1, get message info.
602 FND_MSG_PUB.Count_And_Get
603 (
604 p_count => x_msg_count ,
605 p_data => x_msg_data ,
606 p_encoded => FND_API.G_FALSE
607 );
608
609 EXCEPTION
610 WHEN FND_API.G_EXC_ERROR THEN
611 ROLLBACK TO Update_pay_element_input;
612 x_return_status := FND_API.G_RET_STS_ERROR ;
613 FND_MSG_PUB.Count_And_Get
614 (
615 p_count => x_msg_count ,
616 p_data => x_msg_data ,
617 p_encoded => FND_API.G_FALSE
618 );
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 ROLLBACK TO Update_pay_element_input;
621 x_loading_status := 'UNEXPECTED_ERR';
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
623 FND_MSG_PUB.Count_And_Get
624 (
625 p_count => x_msg_count ,
626 p_data => x_msg_data ,
627 p_encoded => FND_API.G_FALSE
628 );
629 WHEN OTHERS THEN
630 ROLLBACK TO Update_pay_element_input;
631 x_loading_status := 'UNEXPECTED_ERR';
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
633 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
634 THEN
635 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
636 END IF;
637 FND_MSG_PUB.Count_And_Get
638 (
639 p_count => x_msg_count ,
640 p_data => x_msg_data ,
641 p_encoded => FND_API.G_FALSE
642 );
643
644 END Update_pay_element_input;
645
646 --============================================================================
647 --| Procedure : Delete_pay_element_input
648 --|
649 --============================================================================
650 PROCEDURE Delete_pay_element_input
651 (
652 p_api_version IN NUMBER,
653 p_init_msg_list IN VARCHAR2 := CN_API.G_FALSE,
654 p_commit IN VARCHAR2 := CN_API.G_FALSE,
655 p_validation_level IN NUMBER := CN_API.G_VALID_LEVEL_FULL,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2,
659 p_pay_element_input_id IN NUMBER,
660 x_loading_status OUT NOCOPY VARCHAR2
661 ) IS
662
663 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Pay_Element_Input';
664 l_api_version CONSTANT NUMBER := 1.0;
665
666
667 BEGIN
668
669 -- Standard Start of API savepoint
670 SAVEPOINT Delete_Pay_Element_Input;
671 -- Standard call to check for call compatibility.
672 IF NOT FND_API.compatible_api_call
673 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
674 THEN
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676 END IF;
677
678 -- Initialize message list if p_init_msg_list is set to TRUE.
679 IF FND_API.to_Boolean( p_init_msg_list ) THEN
680 FND_MSG_PUB.initialize;
681 END IF;
682
683 -- Initialize API return status to success
684 x_return_status := FND_API.G_RET_STS_SUCCESS;
685 x_loading_status := 'CN_DELETED';
686
687 -- Delete record
688 cn_pay_element_inputs_pkg.delete_row
689 (p_pay_element_input_id =>p_pay_element_input_id);
690 --
691 -- End of API body.
692 --
693 -- Standard check of p_commit.
694 IF FND_API.To_Boolean( p_commit ) THEN
695 COMMIT WORK;
696 END IF;
697 -- Standard call to get message count and if count is 1, get message info.
698 FND_MSG_PUB.Count_And_Get
699 (
700 p_count => x_msg_count ,
701 p_data => x_msg_data ,
702 p_encoded => FND_API.G_FALSE
703 );
704
705 EXCEPTION
706 WHEN FND_API.G_EXC_ERROR THEN
707 ROLLBACK TO Delete_pay_element_input;
708 x_return_status := FND_API.G_RET_STS_ERROR ;
709 FND_MSG_PUB.Count_And_Get
710 (
711 p_count => x_msg_count ,
712 p_data => x_msg_data ,
713 p_encoded => FND_API.G_FALSE
714 );
715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
716 ROLLBACK TO Delete_Pay_Element_Input;
717 x_loading_status := 'UNEXPECTED_ERR';
718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
719 FND_MSG_PUB.Count_And_Get
720 (
721 p_count => x_msg_count ,
722 p_data => x_msg_data ,
723 p_encoded => FND_API.G_FALSE
724 );
725 WHEN OTHERS THEN
726 ROLLBACK TO Delete_pay_element_input;
727 x_loading_status := 'UNEXPECTED_ERR';
728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
729 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
730 THEN
731 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
732 END IF;
733 FND_MSG_PUB.Count_And_Get
734 (
735 p_count => x_msg_count ,
736 p_data => x_msg_data ,
737 p_encoded => FND_API.G_FALSE
738 );
739
740 END Delete_Pay_Element_Input;
741
742 --============================================================================
743 --| Procedure : Get_pay_element_input
744 --|
745 --============================================================================
746 PROCEDURE Get_pay_element_input
747 ( p_api_version IN NUMBER,
748 p_init_msg_list IN VARCHAR2,
749 p_commit IN VARCHAR2,
750 p_validation_level IN NUMBER,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2,
754 p_element_type_id IN cn_pay_element_inputs.element_type_id%TYPE,
755 p_start_record IN NUMBER,
756 p_increment_count IN NUMBER,
757 p_order_by IN VARCHAR2,
758 x_pay_element_input_tbl OUT NOCOPY pay_element_input_out_tbl_type,
759 x_total_records OUT NOCOPY NUMBER,
760 x_status OUT NOCOPY VARCHAR2,
761 x_loading_status OUT NOCOPY VARCHAR2
762 ) IS
763
764 TYPE quotacurtype IS ref CURSOR;
765
766 cur quotacurtype;
767
768
769 l_api_name CONSTANT VARCHAR2(30) := 'Get_pay_element_input';
770 l_api_version CONSTANT NUMBER := 1.0;
771
772 l_counter NUMBER;
773
774 l_pay_element_input_id cn_pay_element_inputs.pay_element_input_id%TYPE;
775 l_element_input_id cn_pay_element_inputs.element_input_id%TYPE;
776 l_element_type_id cn_pay_element_inputs.element_type_id%TYPE;
777 l_table_name cn_objects_all.name%TYPE;
778 l_column_name cn_objects_all.name%TYPE;
779 l_pay_element_name pay_element_types.element_name%TYPE;
780 l_pay_input_name pay_input_values_f.name%TYPE;
781 l_line_number cn_pay_element_inputs.line_number%TYPE;
782 l_quota_pay_element_id cn_pay_element_inputs.quota_pay_element_id%TYPE;
783
784
785 l_select Varchar2(32000):= ' SELECT cpei.pay_element_input_id pay_element_input_id , cpei.element_input_id element_input_id,
786 cpei.element_type_id element_type_id,
787 ct.user_name table_name,
788 cc.user_name column_name ,
789 pet.element_name element_name ,
790 piv.name pay_value_name,
791 piv.display_sequence line_number,
792 cpei.quota_pay_element_id quota_pay_element_id
793 FROM cn_pay_element_inputs cpei,
794 cn_quota_pay_elements cqpe,
795 pay_input_values_f piv,
796 pay_element_types_f pet,
797 cn_objects ct,
798 cn_objects cc,
799 gl_sets_of_books glsob,
800 cn_repositories cnr
801 where
802 cnr.set_of_books_id = glsob.set_of_books_id
803 AND pet.input_currency_code = glsob.currency_code
804 AND cpei.quota_pay_element_id = cqpe.quota_pay_element_id
805 AND cqpe.pay_element_type_id = pet.element_type_id
806 AND cqpe.start_date >= pet.effective_start_date
807 AND cqpe.end_date <= pet.effective_end_date
808 AND trunc(pet.effective_start_date) = trunc(piv.effective_start_date)
809 AND trunc(pet.effective_end_date) = trunc(piv.effective_end_date)
810 AND pet.element_type_id = piv.element_type_id
811 AND cpei.element_input_id = piv.input_value_id
812 AND cpei.tab_object_id = ct.object_id
813 AND cpei.col_object_id = cc.object_id
814 AND cqpe.quota_pay_element_id = :B1
815 UNION
816 SELECT 0 pay_element_input_id ,
817 piv.input_value_id element_input_id,
818 piv.element_type_id element_type_id,
819 NULL table_name,
820 NULL column_name ,
821 pet.element_name,
822 piv.name pay_value_name,
823 piv.display_sequence line_number,
824 0 quota_pay_element_id
825 FROM
826 pay_input_values_f piv,
827 pay_element_types_f pet,
828 cn_quota_pay_elements cqpe,
829 gl_sets_of_books glsob,
830 cn_repositories cnr
831 where
832 cnr.set_of_books_id = glsob.set_of_books_id
833 AND pet.input_currency_code = glsob.currency_code
834 AND cqpe.quota_pay_element_id = :B2
835 AND cqpe.pay_element_type_id = pet.element_type_id
836 AND cqpe.start_date >= pet.effective_start_date
837 AND cqpe.end_date <= pet.effective_end_date
838 AND trunc(pet.effective_start_date) = trunc(piv.effective_start_date)
839 AND trunc(pet.effective_end_date) = trunc(piv.effective_end_date)
840 AND pet.element_type_id = piv.element_type_id
841 AND not exists ( select 1 from cn_pay_element_inputs cpei
842 WHERE cpei.quota_pay_element_id = cqpe.quota_pay_element_id
843 AND cqpe.pay_element_type_id = piv.element_type_id
844 AND cpei.element_input_id = piv.input_value_id )
845 ORDER BY line_number, element_input_id ';
846
847 BEGIN
848
849 --
850 -- Standard call to check for call compatibility.
851 --
852 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
853 p_api_version ,
854 l_api_name ,
855 G_PKG_NAME )
856 THEN
857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
858 END IF;
859 --
860 -- Initialize message list if p_init_msg_list is set to TRUE.
861 --
862 IF FND_API.to_Boolean( p_init_msg_list ) THEN
863 FND_MSG_PUB.initialize;
864 END IF;
865 --
866 -- Initialize API return status to success
867 --
868 x_return_status := FND_API.G_RET_STS_SUCCESS;
869 x_loading_status := 'SELECTED';
870 --
871 -- API body
872 --
873 l_counter := 0;
874
875 x_total_records := 0;
876
877 OPEN cur FOR l_select using p_element_type_id, p_element_type_id;
878 LOOP
879
880 FETCH cur INTO
881 l_pay_element_input_id
882 ,l_element_input_id
883 ,l_element_type_id
884 ,l_table_name
885 ,l_column_name
886 ,l_pay_element_name
887 ,l_pay_input_name
888 ,l_line_number
889 ,l_quota_pay_element_id;
890
891 EXIT WHEN cur%notfound;
892 x_total_records := x_total_records + 1;
893
894 IF (l_counter + 1 BETWEEN p_start_record
895 AND (p_start_record + p_increment_count - 1))
896 THEN
897 x_pay_element_input_tbl(l_counter).pay_element_input_id
898 := l_pay_element_input_id;
899
900 x_pay_element_input_tbl(l_counter).element_input_id
901 := l_element_input_id;
902
903 x_pay_element_input_tbl(l_counter).element_type_id
904 := l_element_type_id;
905
906
907 x_pay_element_input_tbl(l_counter).table_name
908 := l_table_name;
909
910 x_pay_element_input_tbl(l_counter).column_name
911 := l_column_name;
912
913 x_pay_element_input_tbl(l_counter).pay_element_name
914 := l_pay_element_name;
915
916 x_pay_element_input_tbl(l_counter).pay_input_name
917 := l_pay_input_name;
918
919 x_pay_element_input_tbl(l_counter).line_number
920 := l_line_number;
921
922 x_pay_element_input_tbl(l_counter).quota_pay_element_id
923 := l_quota_pay_element_id;
924
925 END IF;
926
927 l_counter := l_counter + 1;
928
929 END LOOP;
930 CLOSE cur;
931
932 x_loading_status := 'SELECTED';
933
934 -- End of API body.
935
936 FND_MSG_PUB.Count_And_Get
937 (
938 p_count => x_msg_count ,
939 p_data => x_msg_data ,
940 p_encoded => FND_API.G_FALSE
941 );
942
943 EXCEPTION
944 WHEN FND_API.G_EXC_ERROR THEN
945
946 x_return_status := FND_API.G_RET_STS_ERROR ;
947 FND_MSG_PUB.Count_And_Get
948 (
949 p_count => x_msg_count ,
950 p_data => x_msg_data ,
951 p_encoded => FND_API.G_FALSE
952 );
953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
954
955 x_loading_status := 'UNEXPECTED_ERR';
956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
957 FND_MSG_PUB.Count_And_Get
958 (
959 p_count => x_msg_count ,
960 p_data => x_msg_data ,
961 p_encoded => FND_API.G_FALSE
962 );
963 WHEN OTHERS THEN
964
965 x_loading_status := 'UNEXPECTED_ERR';
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
967 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
968 THEN
969 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
970 END IF;
971 FND_MSG_PUB.Count_And_Get (
972 p_count => x_msg_count ,
973 p_data => x_msg_data ,
974 p_encoded => FND_API.G_FALSE
975 );
976 END Get_pay_element_input;
977
978 END CN_PAY_ELEMENT_INPUTS_PVT ;