1 PACKAGE OZF_Claim_Line_PVT AUTHID CURRENT_USER AS
2 /* $Header: ozfvclns.pls 120.10 2011/03/24 16:14:57 kdass ship $ */
3
4 TYPE claim_line_rec_type IS RECORD
5 (
6 claim_line_id NUMBER,
7 object_version_number NUMBER,
8 last_update_date DATE,
9 last_updated_by NUMBER,
10 creation_date DATE,
11 created_by NUMBER,
12 last_update_login NUMBER,
13 request_id NUMBER,
14 program_application_id NUMBER,
15 program_update_date DATE,
16 program_id NUMBER,
17 created_from VARCHAR2(30),
18 claim_id NUMBER,
19 line_number NUMBER,
20 split_from_claim_line_id NUMBER,
21 amount NUMBER,
22 claim_currency_amount NUMBER,
23 acctd_amount NUMBER,
24 currency_code VARCHAR2(15),
25 exchange_rate_type VARCHAR2(30),
26 exchange_rate_date DATE,
27 exchange_rate NUMBER,
28 set_of_books_id NUMBER,
29 valid_flag VARCHAR2(1),
30 source_object_id NUMBER,
31 source_object_line_id NUMBER,
32 source_object_class VARCHAR2(15),
33 source_object_type_id NUMBER,
34 plan_id NUMBER,
35 offer_id NUMBER,
36 utilization_id NUMBER,
37 payment_method VARCHAR2(15),
38 payment_reference_id NUMBER,
39 payment_reference_number VARCHAR2(30),
40 payment_reference_date DATE,
41 voucher_id NUMBER,
42 voucher_number VARCHAR2(30),
43 payment_status VARCHAR2(10),
44 approved_flag VARCHAR2(1),
45 approved_date DATE,
46 approved_by NUMBER,
47 settled_date DATE,
48 settled_by NUMBER,
49 performance_complete_flag VARCHAR2(1),
50 performance_attached_flag VARCHAR2(1),
51 select_cust_children_flag VARCHAR2(1),
52 item_id NUMBER,
53 item_description VARCHAR2(240),
54 quantity NUMBER,
55 quantity_uom VARCHAR2(30),
56 rate NUMBER,
57 activity_type VARCHAR2(30),
58 activity_id NUMBER,
59 related_cust_account_id NUMBER,
60 buy_group_cust_account_id NUMBER,
61 relationship_type VARCHAR2(30),
62 earnings_associated_flag VARCHAR2(1),
63 comments VARCHAR2(2000),
64 tax_code VARCHAR2(50),
65 credit_to VARCHAR2(15),
66 attribute_category VARCHAR2(30),
67 attribute1 VARCHAR2(150),
68 attribute2 VARCHAR2(150),
69 attribute3 VARCHAR2(150),
70 attribute4 VARCHAR2(150),
71 attribute5 VARCHAR2(150),
72 attribute6 VARCHAR2(150),
73 attribute7 VARCHAR2(150),
74 attribute8 VARCHAR2(150),
75 attribute9 VARCHAR2(150),
76 attribute10 VARCHAR2(150),
77 attribute11 VARCHAR2(150),
78 attribute12 VARCHAR2(150),
79 attribute13 VARCHAR2(150),
80 attribute14 VARCHAR2(150),
81 attribute15 VARCHAR2(150),
82 org_id NUMBER,
83 update_from_tbl_flag VARCHAR2(1) := FND_API.g_false,
84 tax_action VARCHAR2(15),
85 sale_date DATE,
86 item_type VARCHAR2(30),
87 tax_amount NUMBER,
88 claim_curr_tax_amount NUMBER,
89 activity_line_id NUMBER,
90 offer_type VARCHAR2(30),
91 prorate_earnings_flag VARCHAR2(1),
92 earnings_end_date DATE,
93 buy_group_party_id NUMBER,
94 acctd_tax_amount NUMBER,
95 dpp_cust_account_id VARCHAR2(20), -- 12.1 Enhancement:Price Protection
96 batch_line_id NUMBER, --Bugfix 7811671
97 activity_name VARCHAR2(2000) --for the Bug #10164003
98 );
99
100 TYPE claim_line_tbl_type is TABLE OF claim_line_rec_type
101 INDEX BY BINARY_INTEGER;
102
103 ---------------------------------------------------------------------
104 -- PROCEDURE
105 -- Check_Create_Line_Hist
106 --
107 -- PURPOSE
108 -- Checking if there is a need to create claim line history.
109 --
110 -- PARAMETERS
111 -- p_mode : Data manipulating mode, it should be CREATE', 'UPDATE', or 'DELETE'.
112 -- p_claim_line_rec : The new record to be created, updated, or deleted.
113 -- p_object_attribute: The object_attribut of screen. It could be 'LINE' or 'LNDT'.
114 -- x_create_hist_flag: Returning flag indicating create claim line history or not.
115 --
116 -- NOTES
117 -- 1. p_mode should be 'CREATE', 'UPDATE', or 'DELETE'
118 -- 2. x_create_hist_flag will be set to FND_API.g_true/false
119 -- 3. p_object_attribute could be 'LINE' or 'LNDT'.
120 ---------------------------------------------------------------------
121 PROCEDURE Check_Create_Line_Hist(
122 p_api_version IN NUMBER
123 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
124
125 ,x_return_status OUT NOCOPY VARCHAR2
126 ,x_msg_count OUT NOCOPY NUMBER
127 ,x_msg_data OUT NOCOPY VARCHAR2
128
129 ,p_mode IN VARCHAR2
130 ,p_claim_line_rec IN claim_line_rec_type
131 ,p_object_attribute IN VARCHAR2
132 ,x_create_hist_flag OUT NOCOPY VARCHAR2
133 );
134
135 ---------------------------------------------------------------------
136 -- PROCEDURE
137 -- Update_Line_Fm_Claim
138 --
139 -- PURPOSE
140 -- Update claim lines from Claim package.
141 --
142 -- PARAMETERS
143 -- p_new_claim_rec: the update records without complete(with FND_API.g_miss_num/char/date value)
144 --
145 -- NOTES
146 ---------------------------------------------------------------------
147 PROCEDURE Update_Line_Fm_Claim(
148 p_api_version IN NUMBER
149 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
150 ,p_commit IN VARCHAR2 := FND_API.g_false
151 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
152
153 ,x_return_status OUT NOCOPY VARCHAR2
154 ,x_msg_data OUT NOCOPY VARCHAR2
155 ,x_msg_count OUT NOCOPY NUMBER
156
157 ,p_new_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
158 );
159
160
161 ---------------------------------------------------------------------
162 -- PROCEDURE
163 -- Create_Claim_Line_Tbl
164 --
165 -- PURPOSE
166 -- Create multiple records of claim lines.
167 --
168 -- PARAMETERS
169 -- p_claim_line_tbl: the new records to be inserted
170 -- x_error_index: return the index number in table where error happened.
171 --
172 -- NOTES
173 -- HISTORY
174 -- 21/02/2011 - Fix for Bug 11793070
175 ---------------------------------------------------------------------
176 PROCEDURE Create_Claim_Line_Tbl(
177 p_api_version IN NUMBER
178 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
179 ,p_commit IN VARCHAR2 := FND_API.g_false
180 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
181
182 ,x_return_status OUT NOCOPY VARCHAR2
183 ,x_msg_data OUT NOCOPY VARCHAR2
184 ,x_msg_count OUT NOCOPY NUMBER
185
186 ,p_claim_line_tbl IN OUT NOCOPY claim_line_tbl_type
187 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
188
189 ,x_error_index OUT NOCOPY NUMBER
190 );
191
192
193 ---------------------------------------------------------------------
194 -- PROCEDURE
195 -- Create_Claim_Line
196 --
197 -- PURPOSE
198 -- Create a new record of claim line.
199 --
200 -- PARAMETERS
201 -- p_claim_line_rec: the new record to be inserted
202 -- x_claim_line_id: return the claim_line_id of the new claim line record.
203 --
204 -- NOTES
205 -- 1. object_version_number will be set to 1.
206 -- 2. If claim_line_id is passed in, the uniqueness will be checked.
207 -- Raise exception in case of duplicates.
208 -- 3. If claim_line_id is not passed in, generate a unique one from
209 -- the sequence.
210 -- 4. If a flag column is passed in, check if it is FND_API.g_ture/false.
211 -- Raise exception for invalid flag.
212 -- 5. If valid_flag column is not passed in, default it to FND_API.g_false.
213 -- 6. Please don't pass in any FND_API.g_miss_char/num/date for claim_line record.
214 ---------------------------------------------------------------------
215 PROCEDURE Create_Claim_Line(
216 p_api_version IN NUMBER
217 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
218 ,p_commit IN VARCHAR2 := FND_API.g_false
219 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
220
221 ,x_return_status OUT NOCOPY VARCHAR2
222 ,x_msg_count OUT NOCOPY NUMBER
223 ,x_msg_data OUT NOCOPY VARCHAR2
224
225 ,p_claim_line_rec IN claim_line_rec_type
226 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
227
228 ,x_claim_line_id OUT NOCOPY NUMBER
229 );
230
231
232 ---------------------------------------------------------------------
233 -- PROCEDURE
234 -- Delete_Claim_Line_Tbl
235 --
236 -- PURPOSE
237 -- Delete multiple records of claim lines.
238 --
239 -- PARAMETERS
240 -- p_claim_line_tbl: the new records to be deleted
241 -- x_error_index: return the index number in table where error happened.
242 --
243 -- NOTES
244 ---------------------------------------------------------------------
245 PROCEDURE Delete_Claim_Line_Tbl(
246 p_api_version IN NUMBER
247 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
248 ,p_commit IN VARCHAR2 := FND_API.g_false
249 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
250
251 ,x_return_status OUT NOCOPY VARCHAR2
252 ,x_msg_data OUT NOCOPY VARCHAR2
253 ,x_msg_count OUT NOCOPY NUMBER
254
255 ,p_claim_line_tbl IN claim_line_tbl_type
256 ,p_change_object_version IN VARCHAR2 := FND_API.g_false
257 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
258
259 ,x_error_index OUT NOCOPY NUMBER
260 );
261
262
263 --------------------------------------------------------------------
264 -- PROCEDURE
265 -- Delete_Claim_Line
266 --
267 -- PURPOSE
268 -- Delete a record of claim line.
269 --
270 -- PARAMETERS
271 -- p_claim_line_id: the claim_line_id
272 -- p_object_version: the object_version_number
273 --
274 -- NOTES
275 -- 1. Raise exception if the object_version_number doesn't match.
276 --------------------------------------------------------------------
277 PROCEDURE Delete_Claim_Line(
278 p_api_version IN NUMBER
279 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
280 ,p_commit IN VARCHAR2 := FND_API.g_false
281
282 ,x_return_status OUT NOCOPY VARCHAR2
283 ,x_msg_count OUT NOCOPY NUMBER
284 ,x_msg_data OUT NOCOPY VARCHAR2
285
286 ,p_claim_line_id IN NUMBER
287 ,p_object_version IN NUMBER
288 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
289 );
290
291 -------------------------------------------------------------------
292 -- PROCEDURE
293 -- Lock_Claim_Line
294 --
295 -- PURPOSE
296 -- Lock a claim line record.
297 --
298 -- PARAMETERS
299 -- p_claim_line_id: the claim_line_id
300 -- p_object_version: the object_version_number
301 --
302 -- NOTES
303 -- 1. Raise exception if the object_version_number doesn't match.
304 --------------------------------------------------------------------
305 PROCEDURE Lock_Claim_Line(
306 p_api_version IN NUMBER
307 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
308
309 ,x_return_status OUT NOCOPY VARCHAR2
310 ,x_msg_count OUT NOCOPY NUMBER
311 ,x_msg_data OUT NOCOPY VARCHAR2
312
313 ,p_claim_line_id IN NUMBER
314 ,p_object_version IN NUMBER
315 );
316
317
318 ---------------------------------------------------------------------
319 -- PROCEDURE
320 -- Update_Claim_Line_Tbl
321 --
322 -- PURPOSE
323 -- Update multiple records of claim lines.
324 --
325 -- PARAMETERS
326 -- p_claim_line_tbl: the new records to be updated.
327 -- x_error_index: return the index number in table where error happened.
328 --
329 -- NOTES
330 ---------------------------------------------------------------------
331 PROCEDURE Update_Claim_Line_Tbl(
332 p_api_version IN NUMBER
333 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
334 ,p_commit IN VARCHAR2 := FND_API.g_false
335 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
336
337 ,x_return_status OUT NOCOPY VARCHAR2
338 ,x_msg_data OUT NOCOPY VARCHAR2
339 ,x_msg_count OUT NOCOPY NUMBER
340
341 ,p_claim_line_tbl IN claim_line_tbl_type
342 ,p_change_object_version IN VARCHAR2 := FND_API.g_false
343 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
344
345 ,x_error_index OUT NOCOPY NUMBER
346 );
347
348
349 ---------------------------------------------------------------------
350 -- PROCEDURE
351 -- Update_Claim_Line
352 --
353 -- PURPOSE
354 -- Update a claim line record.
355 --
356 -- PARAMETERS
357 -- p_claim_line_rec: the record with new items.
358 --
359 -- NOTES
360 -- 1. Raise exception if the object_version_number doesn't match.
361 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
362 -- that column won't be updated.
363 ----------------------------------------------------------------------
364 PROCEDURE Update_Claim_Line(
365 p_api_version IN NUMBER
366 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
367 ,p_commit IN VARCHAR2 := FND_API.g_false
368 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
369
370 ,x_return_status OUT NOCOPY VARCHAR2
371 ,x_msg_count OUT NOCOPY NUMBER
372 ,x_msg_data OUT NOCOPY VARCHAR2
373
374 ,p_claim_line_rec IN claim_line_rec_type
375 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
376
377 ,x_object_version OUT NOCOPY NUMBER
378 );
379
380
381 ---------------------------------------------------------------------
382 -- PROCEDURE
383 -- Validate_Claim_Line
384 --
385 -- PURPOSE
386 -- Validate a claim line record.
387 --
388 -- PARAMETERS
389 -- p_claim_line: the claim line record to be validated
390 --
391 -- NOTES
392 -- 1. p_claim_line_rec should be a complete record. There
393 -- should not be any FND_API.g_miss_char/num/date in it.
394 ----------------------------------------------------------------------
395 PROCEDURE Validate_Claim_Line(
396 p_api_version IN NUMBER
397 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
398 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
399
400 ,x_return_status OUT NOCOPY VARCHAR2
401 ,x_msg_count OUT NOCOPY NUMBER
402 ,x_msg_data OUT NOCOPY VARCHAR2
403
404 ,p_claim_line_rec IN claim_line_rec_type
405 );
406
407
408 ---------------------------------------------------------------------
409 -- PROCEDURE
410 -- Check_Claim_Line_Items
411 --
412 -- PURPOSE
413 -- Perform the item level checking including unique keys,
414 -- required columns, foreign keys, domain constraints.
415 --
416 -- PARAMETERS
417 -- p_claim_line_rec: the record to be validated
418 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
419 ---------------------------------------------------------------------
420 PROCEDURE Check_Claim_Line_Items(
421 p_claim_line_rec IN claim_line_rec_type
422 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
423 ,x_return_status OUT NOCOPY VARCHAR2
424 );
425
426
427 ---------------------------------------------------------------------
428 -- PROCEDURE
429 -- Check_Claim_Line_Record
430 --
431 -- PURPOSE
432 -- Check the record level business rules.
433 --
434 -- PARAMETERS
435 -- p_claim_line_rec: the record to be validated; may contain attributes
436 -- as FND_API.g_miss_char/num/date
437 -- p_complete_rec: the complete record after all "g_miss" items
438 -- have been replaced by current database values
439 ---------------------------------------------------------------------
440 PROCEDURE Check_Claim_Line_Record(
441 p_claim_line_rec IN claim_line_rec_type
442 ,p_complete_rec IN claim_line_rec_type := NULL
443 ,x_return_status OUT NOCOPY VARCHAR2
444 );
445
446
447 ---------------------------------------------------------------------
448 -- PROCEDURE
449 -- Init_Claim_Line_Rec
450 --
451 -- PURPOSE
452 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
453 ---------------------------------------------------------------------
454 PROCEDURE Init_Claim_Line_Rec(
455 x_claim_line_rec OUT NOCOPY claim_line_rec_type
456 );
457
458
459 ---------------------------------------------------------------------
460 -- PROCEDURE
461 -- Complete_Claim_Line_Rec
462 --
463 -- PURPOSE
464 -- For update_claim_line, some attributes may be passed in as
465 -- FND_API.g_miss_char/num/date if the user doesn't want to
466 -- update those attributes. This procedure will replace the
467 -- "g_miss" attributes with current database values.
468 --
469 -- PARAMETERS
470 -- p_claim_line_rec: the record which may contain attributes as
471 -- FND_API.g_miss_char/num/date
472 -- x_complete_rec: the complete record after all "g_miss" items
473 -- have been replaced by current database values
474 ---------------------------------------------------------------------
475 PROCEDURE Complete_Claim_Line_Rec(
476 p_claim_line_rec IN claim_line_rec_type
477 ,x_complete_rec OUT NOCOPY claim_line_rec_type
478 );
479
480 -- Created for Bug4348163:Split a given claim line so as to associate each claim
481 -- line with earnings from only one offer-product combination.
482 PROCEDURE split_claim_line(
483 p_api_version IN NUMBER
484 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
485 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
486 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
487
488 ,p_claim_line_id IN NUMBER
489 ,x_return_status OUT NOCOPY VARCHAR2
490 ,x_msg_count OUT NOCOPY NUMBER
491 ,x_msg_data OUT NOCOPY VARCHAR2
492 );
493
494 ---------------------------------------------------------------------
495 -- PROCEDURE
496 -- Delete_All_Association_AssoUI
497 --
498 -- PURPOSE
499 -- Delete the Claim lines and Association from promotional tab OA UI
500 --
501 -- PARAMETERS
502 -- p_claim_id: claim header ID
503 --
504 -- HISTORY
505 -- 04/03/2011 kpatro Create.
506 ---------------------------------------------------------------------
507 PROCEDURE Delete_All_Association_AssoUI(
508 p_api_version IN NUMBER
509 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
510 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
511 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
512
513 ,p_claim_id IN NUMBER
514 ,x_return_status OUT NOCOPY VARCHAR2
515 ,x_msg_count OUT NOCOPY NUMBER
516 ,x_msg_data OUT NOCOPY VARCHAR2
517 );
518
519 ---------------------------------------------------------------------
520 -- PROCEDURE
521 -- Update_Line_Fm_Claim
522 --
523 -- HISTORY
524 -- 03/14/2011 kdass Created. This API will be called for New Claim UI for Claim Line
525 -- update based on change of claim header columns
526 ---------------------------------------------------------------------
527 PROCEDURE Update_Line_Fm_Claim(
528 p_api_version IN NUMBER
529 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
530 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
531 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
532
533 ,x_return_status OUT NOCOPY VARCHAR2
534 ,x_msg_data OUT NOCOPY VARCHAR2
535 ,x_msg_count OUT NOCOPY NUMBER
536
537 ,p_claim_id IN NUMBER
538 ,p_tax_action IN VARCHAR2
539 ,p_remove_tax_code IN VARCHAR2 := FND_API.G_FALSE
540 ,p_remove_related_cust IN VARCHAR2 := FND_API.G_FALSE
541 ,p_amount_change_flag IN VARCHAR2 := FND_API.G_FALSE
542 );
543
544 END OZF_Claim_Line_PVT;