1 PACKAGE OZF_Claim_Line_PVT AS
2 /* $Header: ozfvclns.pls 120.4.12010000.2 2009/02/03 06:36:33 psomyaju 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 );
98
99 TYPE claim_line_tbl_type is TABLE OF claim_line_rec_type
100 INDEX BY BINARY_INTEGER;
101
102 ---------------------------------------------------------------------
103 -- PROCEDURE
104 -- Check_Create_Line_Hist
105 --
106 -- PURPOSE
107 -- Checking if there is a need to create claim line history.
108 --
109 -- PARAMETERS
110 -- p_mode : Data manipulating mode, it should be CREATE', 'UPDATE', or 'DELETE'.
111 -- p_claim_line_rec : The new record to be created, updated, or deleted.
112 -- p_object_attribute: The object_attribut of screen. It could be 'LINE' or 'LNDT'.
113 -- x_create_hist_flag: Returning flag indicating create claim line history or not.
114 --
115 -- NOTES
116 -- 1. p_mode should be 'CREATE', 'UPDATE', or 'DELETE'
117 -- 2. x_create_hist_flag will be set to FND_API.g_true/false
118 -- 3. p_object_attribute could be 'LINE' or 'LNDT'.
119 ---------------------------------------------------------------------
120 PROCEDURE Check_Create_Line_Hist(
121 p_api_version IN NUMBER
122 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
123
124 ,x_return_status OUT NOCOPY VARCHAR2
125 ,x_msg_count OUT NOCOPY NUMBER
126 ,x_msg_data OUT NOCOPY VARCHAR2
127
128 ,p_mode IN VARCHAR2
129 ,p_claim_line_rec IN claim_line_rec_type
130 ,p_object_attribute IN VARCHAR2
131 ,x_create_hist_flag OUT NOCOPY VARCHAR2
132 );
133
134 ---------------------------------------------------------------------
135 -- PROCEDURE
136 -- Update_Line_Fm_Claim
137 --
138 -- PURPOSE
139 -- Update claim lines from Claim package.
140 --
141 -- PARAMETERS
142 -- p_new_claim_rec: the update records without complete(with FND_API.g_miss_num/char/date value)
143 --
144 -- NOTES
145 ---------------------------------------------------------------------
146 PROCEDURE Update_Line_Fm_Claim(
147 p_api_version IN NUMBER
148 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
149 ,p_commit IN VARCHAR2 := FND_API.g_false
150 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
151
152 ,x_return_status OUT NOCOPY VARCHAR2
153 ,x_msg_data OUT NOCOPY VARCHAR2
154 ,x_msg_count OUT NOCOPY NUMBER
155
156 ,p_new_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
157 );
158
159
160 ---------------------------------------------------------------------
161 -- PROCEDURE
162 -- Create_Claim_Line_Tbl
163 --
164 -- PURPOSE
165 -- Create multiple records of claim lines.
166 --
167 -- PARAMETERS
168 -- p_claim_line_tbl: the new records to be inserted
169 -- x_error_index: return the index number in table where error happened.
170 --
171 -- NOTES
172 ---------------------------------------------------------------------
173 PROCEDURE Create_Claim_Line_Tbl(
174 p_api_version IN NUMBER
175 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
176 ,p_commit IN VARCHAR2 := FND_API.g_false
177 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
178
179 ,x_return_status OUT NOCOPY VARCHAR2
180 ,x_msg_data OUT NOCOPY VARCHAR2
181 ,x_msg_count OUT NOCOPY NUMBER
182
183 ,p_claim_line_tbl IN claim_line_tbl_type
184 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
185
186 ,x_error_index OUT NOCOPY NUMBER
187 );
188
189
190 ---------------------------------------------------------------------
191 -- PROCEDURE
192 -- Create_Claim_Line
193 --
194 -- PURPOSE
195 -- Create a new record of claim line.
196 --
197 -- PARAMETERS
198 -- p_claim_line_rec: the new record to be inserted
199 -- x_claim_line_id: return the claim_line_id of the new claim line record.
200 --
201 -- NOTES
202 -- 1. object_version_number will be set to 1.
203 -- 2. If claim_line_id is passed in, the uniqueness will be checked.
204 -- Raise exception in case of duplicates.
205 -- 3. If claim_line_id is not passed in, generate a unique one from
206 -- the sequence.
207 -- 4. If a flag column is passed in, check if it is FND_API.g_ture/false.
208 -- Raise exception for invalid flag.
209 -- 5. If valid_flag column is not passed in, default it to FND_API.g_false.
210 -- 6. Please don't pass in any FND_API.g_miss_char/num/date for claim_line record.
211 ---------------------------------------------------------------------
212 PROCEDURE Create_Claim_Line(
213 p_api_version IN NUMBER
214 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
215 ,p_commit IN VARCHAR2 := FND_API.g_false
216 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
217
218 ,x_return_status OUT NOCOPY VARCHAR2
219 ,x_msg_count OUT NOCOPY NUMBER
220 ,x_msg_data OUT NOCOPY VARCHAR2
221
222 ,p_claim_line_rec IN claim_line_rec_type
223 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
224
225 ,x_claim_line_id OUT NOCOPY NUMBER
226 );
227
228
229 ---------------------------------------------------------------------
230 -- PROCEDURE
231 -- Delete_Claim_Line_Tbl
232 --
233 -- PURPOSE
234 -- Delete multiple records of claim lines.
235 --
236 -- PARAMETERS
237 -- p_claim_line_tbl: the new records to be deleted
238 -- x_error_index: return the index number in table where error happened.
239 --
240 -- NOTES
241 ---------------------------------------------------------------------
242 PROCEDURE Delete_Claim_Line_Tbl(
243 p_api_version IN NUMBER
244 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
245 ,p_commit IN VARCHAR2 := FND_API.g_false
246 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
247
248 ,x_return_status OUT NOCOPY VARCHAR2
249 ,x_msg_data OUT NOCOPY VARCHAR2
250 ,x_msg_count OUT NOCOPY NUMBER
251
252 ,p_claim_line_tbl IN claim_line_tbl_type
253 ,p_change_object_version IN VARCHAR2 := FND_API.g_false
254 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
255
256 ,x_error_index OUT NOCOPY NUMBER
257 );
258
259
260 --------------------------------------------------------------------
261 -- PROCEDURE
262 -- Delete_Claim_Line
263 --
264 -- PURPOSE
265 -- Delete a record of claim line.
266 --
267 -- PARAMETERS
268 -- p_claim_line_id: the claim_line_id
269 -- p_object_version: the object_version_number
270 --
271 -- NOTES
272 -- 1. Raise exception if the object_version_number doesn't match.
273 --------------------------------------------------------------------
274 PROCEDURE Delete_Claim_Line(
275 p_api_version IN NUMBER
276 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
277 ,p_commit IN VARCHAR2 := FND_API.g_false
278
279 ,x_return_status OUT NOCOPY VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282
283 ,p_claim_line_id IN NUMBER
284 ,p_object_version IN NUMBER
285 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
286 );
287
288 -------------------------------------------------------------------
289 -- PROCEDURE
290 -- Lock_Claim_Line
291 --
292 -- PURPOSE
293 -- Lock a claim line record.
294 --
295 -- PARAMETERS
296 -- p_claim_line_id: the claim_line_id
297 -- p_object_version: the object_version_number
298 --
299 -- NOTES
300 -- 1. Raise exception if the object_version_number doesn't match.
301 --------------------------------------------------------------------
302 PROCEDURE Lock_Claim_Line(
303 p_api_version IN NUMBER
304 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
305
306 ,x_return_status OUT NOCOPY VARCHAR2
307 ,x_msg_count OUT NOCOPY NUMBER
308 ,x_msg_data OUT NOCOPY VARCHAR2
309
310 ,p_claim_line_id IN NUMBER
311 ,p_object_version IN NUMBER
312 );
313
314
315 ---------------------------------------------------------------------
316 -- PROCEDURE
317 -- Update_Claim_Line_Tbl
318 --
319 -- PURPOSE
320 -- Update multiple records of claim lines.
321 --
322 -- PARAMETERS
323 -- p_claim_line_tbl: the new records to be updated.
324 -- x_error_index: return the index number in table where error happened.
325 --
326 -- NOTES
327 ---------------------------------------------------------------------
328 PROCEDURE Update_Claim_Line_Tbl(
329 p_api_version IN NUMBER
330 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
331 ,p_commit IN VARCHAR2 := FND_API.g_false
332 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
333
334 ,x_return_status OUT NOCOPY VARCHAR2
335 ,x_msg_data OUT NOCOPY VARCHAR2
336 ,x_msg_count OUT NOCOPY NUMBER
337
338 ,p_claim_line_tbl IN claim_line_tbl_type
339 ,p_change_object_version IN VARCHAR2 := FND_API.g_false
340 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
341
342 ,x_error_index OUT NOCOPY NUMBER
343 );
344
345
346 ---------------------------------------------------------------------
347 -- PROCEDURE
348 -- Update_Claim_Line
349 --
350 -- PURPOSE
351 -- Update a claim line record.
352 --
353 -- PARAMETERS
354 -- p_claim_line_rec: the record with new items.
355 --
356 -- NOTES
357 -- 1. Raise exception if the object_version_number doesn't match.
358 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
359 -- that column won't be updated.
360 ----------------------------------------------------------------------
361 PROCEDURE Update_Claim_Line(
362 p_api_version IN NUMBER
363 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
364 ,p_commit IN VARCHAR2 := FND_API.g_false
365 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
366
367 ,x_return_status OUT NOCOPY VARCHAR2
368 ,x_msg_count OUT NOCOPY NUMBER
369 ,x_msg_data OUT NOCOPY VARCHAR2
370
371 ,p_claim_line_rec IN claim_line_rec_type
372 ,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
373
374 ,x_object_version OUT NOCOPY NUMBER
375 );
376
377
378 ---------------------------------------------------------------------
379 -- PROCEDURE
380 -- Validate_Claim_Line
381 --
382 -- PURPOSE
383 -- Validate a claim line record.
384 --
385 -- PARAMETERS
386 -- p_claim_line: the claim line record to be validated
387 --
388 -- NOTES
389 -- 1. p_claim_line_rec should be a complete record. There
390 -- should not be any FND_API.g_miss_char/num/date in it.
391 ----------------------------------------------------------------------
392 PROCEDURE Validate_Claim_Line(
393 p_api_version IN NUMBER
394 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
395 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
396
397 ,x_return_status OUT NOCOPY VARCHAR2
398 ,x_msg_count OUT NOCOPY NUMBER
399 ,x_msg_data OUT NOCOPY VARCHAR2
400
401 ,p_claim_line_rec IN claim_line_rec_type
402 );
403
404
405 ---------------------------------------------------------------------
406 -- PROCEDURE
407 -- Check_Claim_Line_Items
408 --
409 -- PURPOSE
410 -- Perform the item level checking including unique keys,
411 -- required columns, foreign keys, domain constraints.
412 --
413 -- PARAMETERS
414 -- p_claim_line_rec: the record to be validated
415 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
416 ---------------------------------------------------------------------
417 PROCEDURE Check_Claim_Line_Items(
418 p_claim_line_rec IN claim_line_rec_type
419 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
420 ,x_return_status OUT NOCOPY VARCHAR2
421 );
422
423
424 ---------------------------------------------------------------------
425 -- PROCEDURE
426 -- Check_Claim_Line_Record
427 --
428 -- PURPOSE
429 -- Check the record level business rules.
430 --
431 -- PARAMETERS
432 -- p_claim_line_rec: the record to be validated; may contain attributes
433 -- as FND_API.g_miss_char/num/date
434 -- p_complete_rec: the complete record after all "g_miss" items
435 -- have been replaced by current database values
436 ---------------------------------------------------------------------
437 PROCEDURE Check_Claim_Line_Record(
438 p_claim_line_rec IN claim_line_rec_type
439 ,p_complete_rec IN claim_line_rec_type := NULL
440 ,x_return_status OUT NOCOPY VARCHAR2
441 );
442
443
444 ---------------------------------------------------------------------
445 -- PROCEDURE
446 -- Init_Claim_Line_Rec
447 --
448 -- PURPOSE
449 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
450 ---------------------------------------------------------------------
451 PROCEDURE Init_Claim_Line_Rec(
452 x_claim_line_rec OUT NOCOPY claim_line_rec_type
453 );
454
455
456 ---------------------------------------------------------------------
457 -- PROCEDURE
458 -- Complete_Claim_Line_Rec
459 --
460 -- PURPOSE
461 -- For update_claim_line, some attributes may be passed in as
462 -- FND_API.g_miss_char/num/date if the user doesn't want to
463 -- update those attributes. This procedure will replace the
464 -- "g_miss" attributes with current database values.
465 --
466 -- PARAMETERS
467 -- p_claim_line_rec: the record which may contain attributes as
468 -- FND_API.g_miss_char/num/date
469 -- x_complete_rec: the complete record after all "g_miss" items
470 -- have been replaced by current database values
471 ---------------------------------------------------------------------
472 PROCEDURE Complete_Claim_Line_Rec(
473 p_claim_line_rec IN claim_line_rec_type
474 ,x_complete_rec OUT NOCOPY claim_line_rec_type
475 );
476
477 -- Created for Bug4348163:Split a given claim line so as to associate each claim
478 -- line with earnings from only one offer-product combination.
479 PROCEDURE split_claim_line(
480 p_api_version IN NUMBER
481 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
482 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
483 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
484
485 ,p_claim_line_id IN NUMBER
486 ,x_return_status OUT NOCOPY VARCHAR2
487 ,x_msg_count OUT NOCOPY NUMBER
488 ,x_msg_data OUT NOCOPY VARCHAR2
489 );
490
491 END OZF_Claim_Line_PVT;