[Home] [Help]
PACKAGE BODY: APPS.OZF_AR_INTERFACE_PVT
Source
1 PACKAGE BODY OZF_Ar_Interface_PVT AS
2 /*$Header: ozfvarib.pls 120.16 2012/05/16 06:14:42 bkunjan ship $*/
3
4
5 -- Standard Stuff ------------------
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ozf_ar_interface_pvt';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvaris.pls';
9 G_OBJECT_TYPE CONSTANT VARCHAR2(10) := 'OZF_????';
10 G_OWNER_OBJECT CONSTANT VARCHAR2(30) := 'RS_EMPLOYEE';
11
12 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
13
14 --------------------------------------
15
16
17 /* ---------------------------------------------- *
18 * Populate the Claim Record
19 * ---------------------------------------------- */
20 PROCEDURE Query_Claim
21 ( p_claim_id IN NUMBER
22 ,x_claim_rec IN OUT NOCOPY Claim_Rec_Type
23 ,x_return_status OUT NOCOPY VARCHAR2
24 )
25 IS
26 BEGIN
27
28 SELECT
29 CLAIM_ID
30 ,OBJECT_VERSION_NUMBER
31 ,LAST_UPDATE_DATE
32 ,LAST_UPDATED_BY
33 ,CREATION_DATE
34 ,CREATED_BY
35 ,LAST_UPDATE_LOGIN
36 ,REQUEST_ID
37 ,PROGRAM_APPLICATION_ID
38 ,PROGRAM_UPDATE_DATE
39 ,PROGRAM_ID
40 ,CREATED_FROM
41 ,BATCH_ID
42 ,CLAIM_NUMBER
43 ,CLAIM_TYPE_ID
44 ,CLAIM_CLASS
45 ,CLAIM_DATE
46 ,DUE_DATE
47 ,OWNER_ID
48 ,HISTORY_EVENT
49 ,HISTORY_EVENT_DATE
50 ,HISTORY_EVENT_DESCRIPTION
51 ,SPLIT_FROM_CLAIM_ID
52 ,DUPLICATE_CLAIM_ID
53 ,SPLIT_DATE
54 ,ROOT_CLAIM_ID
55 ,AMOUNT
56 ,AMOUNT_ADJUSTED
57 ,AMOUNT_REMAINING
58 ,AMOUNT_SETTLED
59 ,ACCTD_AMOUNT
60 ,ACCTD_AMOUNT_REMAINING
61 ,TAX_AMOUNT
62 ,TAX_CODE
63 ,TAX_CALCULATION_FLAG
64 ,CURRENCY_CODE
65 ,EXCHANGE_RATE_TYPE
66 ,EXCHANGE_RATE_DATE
67 ,EXCHANGE_RATE
68 ,SET_OF_BOOKS_ID
69 ,ORIGINAL_CLAIM_DATE
70 ,SOURCE_OBJECT_ID
71 ,SOURCE_OBJECT_CLASS
72 ,SOURCE_OBJECT_TYPE_ID
73 ,SOURCE_OBJECT_NUMBER
74 ,CUST_ACCOUNT_ID
75 ,CUST_BILLTO_ACCT_SITE_ID
76 ,CUST_SHIPTO_ACCT_SITE_ID
77 ,LOCATION_ID
78 ,PAY_RELATED_ACCOUNT_FLAG
79 ,RELATED_CUST_ACCOUNT_ID
80 ,RELATED_SITE_USE_ID
81 ,RELATIONSHIP_TYPE
82 ,VENDOR_ID
83 ,VENDOR_SITE_ID
84 ,REASON_TYPE
85 ,REASON_CODE_ID
86 ,TASK_TEMPLATE_GROUP_ID
87 ,STATUS_CODE
88 ,USER_STATUS_ID
89 ,SALES_REP_ID
90 ,COLLECTOR_ID
91 ,CONTACT_ID
92 ,BROKER_ID
93 ,TERRITORY_ID
94 ,CUSTOMER_REF_DATE
95 ,CUSTOMER_REF_NUMBER
96 ,ASSIGNED_TO
97 ,RECEIPT_ID
98 ,RECEIPT_NUMBER
99 ,DOC_SEQUENCE_ID
100 ,DOC_SEQUENCE_VALUE
101 ,GL_DATE
102 ,PAYMENT_METHOD
103 ,VOUCHER_ID
104 ,VOUCHER_NUMBER
105 ,PAYMENT_REFERENCE_ID
106 ,PAYMENT_REFERENCE_NUMBER
107 ,PAYMENT_REFERENCE_DATE
108 ,PAYMENT_STATUS
109 ,APPROVED_FLAG
110 ,APPROVED_DATE
111 ,APPROVED_BY
112 ,SETTLED_DATE
113 ,SETTLED_BY
114 ,EFFECTIVE_DATE
115 ,CUSTOM_SETUP_ID
116 ,TASK_ID
117 ,COUNTRY_ID
118 ,COMMENTS
119 ,ATTRIBUTE_CATEGORY
120 ,ATTRIBUTE1
121 ,ATTRIBUTE2
122 ,ATTRIBUTE3
123 ,ATTRIBUTE4
124 ,ATTRIBUTE5
125 ,ATTRIBUTE6
126 ,ATTRIBUTE7
127 ,ATTRIBUTE8
128 ,ATTRIBUTE9
129 ,ATTRIBUTE10
130 ,ATTRIBUTE11
131 ,ATTRIBUTE12
132 ,ATTRIBUTE13
133 ,ATTRIBUTE14
134 ,ATTRIBUTE15
135 ,DEDUCTION_ATTRIBUTE_CATEGORY
136 ,DEDUCTION_ATTRIBUTE1
137 ,DEDUCTION_ATTRIBUTE2
138 ,DEDUCTION_ATTRIBUTE3
139 ,DEDUCTION_ATTRIBUTE4
140 ,DEDUCTION_ATTRIBUTE5
141 ,DEDUCTION_ATTRIBUTE6
142 ,DEDUCTION_ATTRIBUTE7
143 ,DEDUCTION_ATTRIBUTE8
144 ,DEDUCTION_ATTRIBUTE9
145 ,DEDUCTION_ATTRIBUTE10
146 ,DEDUCTION_ATTRIBUTE11
147 ,DEDUCTION_ATTRIBUTE12
148 ,DEDUCTION_ATTRIBUTE13
149 ,DEDUCTION_ATTRIBUTE14
150 ,DEDUCTION_ATTRIBUTE15
151 ,ORG_ID
152 ,CUSTOMER_REASON -- 11.5.10 Enhancements. TM should pass.
153 ,SHIP_TO_CUST_ACCOUNT_ID
154 ,LEGAL_ENTITY_ID
155 INTO
156 x_claim_rec.claim_id
157 ,x_claim_rec.object_version_number
158 ,x_claim_rec.last_update_date
159 ,x_claim_rec.last_updated_by
160 ,x_claim_rec.creation_date
161 ,x_claim_rec.created_by
162 ,x_claim_rec.last_update_login
163 ,x_claim_rec.request_id
164 ,x_claim_rec.program_application_id
165 ,x_claim_rec.program_update_date
166 ,x_claim_rec.program_id
167 ,x_claim_rec.created_from
168 ,x_claim_rec.batch_id
169 ,x_claim_rec.claim_number
170 ,x_claim_rec.claim_type_id
171 ,x_claim_rec.claim_class
172 ,x_claim_rec.claim_date
173 ,x_claim_rec.due_date
174 ,x_claim_rec.owner_id
175 ,x_claim_rec.history_event
176 ,x_claim_rec.history_event_date
177 ,x_claim_rec.history_event_description
178 ,x_claim_rec.split_from_claim_id
179 ,x_claim_rec.duplicate_claim_id
180 ,x_claim_rec.split_date
181 ,x_claim_rec.root_claim_id
182 ,x_claim_rec.amount
183 ,x_claim_rec.amount_adjusted
184 ,x_claim_rec.amount_remaining
185 ,x_claim_rec.amount_settled
186 ,x_claim_rec.acctd_amount
187 ,x_claim_rec.acctd_amount_remaining
188 ,x_claim_rec.tax_amount
189 ,x_claim_rec.tax_code
190 ,x_claim_rec.tax_calculation_flag
191 ,x_claim_rec.currency_code
192 ,x_claim_rec.exchange_rate_type
193 ,x_claim_rec.exchange_rate_date
194 ,x_claim_rec.exchange_rate
195 ,x_claim_rec.set_of_books_id
196 ,x_claim_rec.original_claim_date
197 ,x_claim_rec.source_object_id
198 ,x_claim_rec.source_object_class
199 ,x_claim_rec.source_object_type_id
200 ,x_claim_rec.source_object_number
201 ,x_claim_rec.cust_account_id
202 ,x_claim_rec.cust_billto_acct_site_id
203 ,x_claim_rec.cust_shipto_acct_site_id
204 ,x_claim_rec.location_id
205 ,x_claim_rec.pay_related_account_flag
206 ,x_claim_rec.related_cust_account_id
207 ,x_claim_rec.related_site_use_id
208 ,x_claim_rec.relationship_type
209 ,x_claim_rec.vendor_id
210 ,x_claim_rec.vendor_site_id
211 ,x_claim_rec.reason_type
212 ,x_claim_rec.reason_code_id
213 ,x_claim_rec.task_template_group_id
214 ,x_claim_rec.status_code
215 ,x_claim_rec.user_status_id
216 ,x_claim_rec.sales_rep_id
217 ,x_claim_rec.collector_id
218 ,x_claim_rec.contact_id
219 ,x_claim_rec.broker_id
220 ,x_claim_rec.territory_id
221 ,x_claim_rec.customer_ref_date
222 ,x_claim_rec.customer_ref_number
223 ,x_claim_rec.assigned_to
224 ,x_claim_rec.receipt_id
225 ,x_claim_rec.receipt_number
226 ,x_claim_rec.doc_sequence_id
227 ,x_claim_rec.doc_sequence_value
228 ,x_claim_rec.gl_date
229 ,x_claim_rec.payment_method
230 ,x_claim_rec.voucher_id
231 ,x_claim_rec.voucher_number
232 ,x_claim_rec.payment_reference_id
233 ,x_claim_rec.payment_reference_number
234 ,x_claim_rec.payment_reference_date
235 ,x_claim_rec.payment_status
236 ,x_claim_rec.approved_flag
237 ,x_claim_rec.approved_date
238 ,x_claim_rec.approved_by
239 ,x_claim_rec.settled_date
240 ,x_claim_rec.settled_by
241 ,x_claim_rec.effective_date
242 ,x_claim_rec.custom_setup_id
243 ,x_claim_rec.task_id
244 ,x_claim_rec.country_id
245 ,x_claim_rec.comments
246 ,x_claim_rec.attribute_category
247 ,x_claim_rec.attribute1
248 ,x_claim_rec.attribute2
249 ,x_claim_rec.attribute3
250 ,x_claim_rec.attribute4
251 ,x_claim_rec.attribute5
252 ,x_claim_rec.attribute6
253 ,x_claim_rec.attribute7
254 ,x_claim_rec.attribute8
255 ,x_claim_rec.attribute9
256 ,x_claim_rec.attribute10
257 ,x_claim_rec.attribute11
258 ,x_claim_rec.attribute12
259 ,x_claim_rec.attribute13
260 ,x_claim_rec.attribute14
261 ,x_claim_rec.attribute15
262 ,x_claim_rec.deduction_attribute_category
263 ,x_claim_rec.deduction_attribute1
264 ,x_claim_rec.deduction_attribute2
265 ,x_claim_rec.deduction_attribute3
266 ,x_claim_rec.deduction_attribute4
267 ,x_claim_rec.deduction_attribute5
268 ,x_claim_rec.deduction_attribute6
269 ,x_claim_rec.deduction_attribute7
270 ,x_claim_rec.deduction_attribute8
271 ,x_claim_rec.deduction_attribute9
272 ,x_claim_rec.deduction_attribute10
273 ,x_claim_rec.deduction_attribute11
274 ,x_claim_rec.deduction_attribute12
275 ,x_claim_rec.deduction_attribute13
276 ,x_claim_rec.deduction_attribute14
277 ,x_claim_rec.deduction_attribute15
278 ,x_claim_rec.org_id
279 ,x_claim_rec.customer_reason -- 11.5.10 Enhancements. TM should pass.
280 ,x_claim_rec.ship_to_cust_account_id
281 ,x_claim_rec.legal_entity_id
282 FROM ozf_claims_all
283 WHERE claim_id = p_claim_id ;
284
285 x_return_status := FND_API.g_ret_sts_success;
286 EXCEPTION
287 WHEN OTHERS THEN
288 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
289 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_QUERY_ERROR');
290 FND_MSG_PUB.add;
291 END IF;
292 x_return_status := FND_API.g_ret_sts_unexp_error;
293 END Query_Claim;
294
295 PROCEDURE Insert_Int_Distributions
296 ( p_int_distributions_rec IN RA_Int_Distributions_Rec_Type
297 ,x_return_status OUT NOCOPY VARCHAR2
298 ) IS
299 BEGIN
300 INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
301 (
302 INTERFACE_DISTRIBUTION_ID,
303 INTERFACE_LINE_ID,
304 INTERFACE_LINE_CONTEXT,
305 INTERFACE_LINE_ATTRIBUTE1,
306 INTERFACE_LINE_ATTRIBUTE2,
307 INTERFACE_LINE_ATTRIBUTE3,
308 INTERFACE_LINE_ATTRIBUTE4,
309 INTERFACE_LINE_ATTRIBUTE5,
310 INTERFACE_LINE_ATTRIBUTE6,
311 INTERFACE_LINE_ATTRIBUTE7,
312 INTERFACE_LINE_ATTRIBUTE8,
313 INTERFACE_LINE_ATTRIBUTE9,
314 INTERFACE_LINE_ATTRIBUTE10,
315 INTERFACE_LINE_ATTRIBUTE11,
316 INTERFACE_LINE_ATTRIBUTE12,
317 INTERFACE_LINE_ATTRIBUTE13,
318 INTERFACE_LINE_ATTRIBUTE14,
319 INTERFACE_LINE_ATTRIBUTE15,
320 ACCOUNT_CLASS,
321 AMOUNT,
322 ACCTD_AMOUNT,
323 PERCENT,
324 INTERFACE_STATUS,
325 REQUEST_ID,
326 CODE_COMBINATION_ID,
327 SEGMENT1,
328 SEGMENT2,
329 SEGMENT3,
330 SEGMENT4,
331 SEGMENT5,
332 SEGMENT6,
333 SEGMENT7,
334 SEGMENT8,
335 SEGMENT9,
336 SEGMENT10,
337 SEGMENT11,
338 SEGMENT12,
339 SEGMENT13,
340 SEGMENT14,
341 SEGMENT15,
342 SEGMENT16,
343 SEGMENT17,
344 SEGMENT18,
345 SEGMENT19,
346 SEGMENT20,
347 SEGMENT21,
348 SEGMENT22,
349 SEGMENT23,
350 SEGMENT24,
351 SEGMENT25,
352 SEGMENT26,
353 SEGMENT27,
354 SEGMENT28,
355 SEGMENT29,
356 SEGMENT30,
357 COMMENTS,
358 ATTRIBUTE_CATEGORY,
359 ATTRIBUTE1,
360 ATTRIBUTE2,
361 ATTRIBUTE3,
362 ATTRIBUTE4,
363 ATTRIBUTE5,
364 ATTRIBUTE6,
365 ATTRIBUTE7,
366 ATTRIBUTE8,
367 ATTRIBUTE9,
368 ATTRIBUTE10,
369 ATTRIBUTE11,
370 ATTRIBUTE12,
371 ATTRIBUTE13,
372 ATTRIBUTE14,
373 ATTRIBUTE15,
374 CREATED_BY,
375 CREATION_DATE,
376 LAST_UPDATED_BY,
377 LAST_UPDATE_DATE,
378 LAST_UPDATE_LOGIN,
379 ORG_ID,
380 INTERIM_TAX_CCID,
381 INTERIM_TAX_SEGMENT1,
382 INTERIM_TAX_SEGMENT2,
383 INTERIM_TAX_SEGMENT3,
384 INTERIM_TAX_SEGMENT4,
385 INTERIM_TAX_SEGMENT5,
386 INTERIM_TAX_SEGMENT6,
387 INTERIM_TAX_SEGMENT7,
388 INTERIM_TAX_SEGMENT8,
389 INTERIM_TAX_SEGMENT9,
390 INTERIM_TAX_SEGMENT10,
391 INTERIM_TAX_SEGMENT11,
392 INTERIM_TAX_SEGMENT12,
393 INTERIM_TAX_SEGMENT13,
394 INTERIM_TAX_SEGMENT14,
395 INTERIM_TAX_SEGMENT15,
396 INTERIM_TAX_SEGMENT16,
397 INTERIM_TAX_SEGMENT17,
398 INTERIM_TAX_SEGMENT18,
399 INTERIM_TAX_SEGMENT19,
400 INTERIM_TAX_SEGMENT20,
401 INTERIM_TAX_SEGMENT21,
402 INTERIM_TAX_SEGMENT22,
403 INTERIM_TAX_SEGMENT23,
404 INTERIM_TAX_SEGMENT24,
405 INTERIM_TAX_SEGMENT25,
406 INTERIM_TAX_SEGMENT26,
407 INTERIM_TAX_SEGMENT27,
408 INTERIM_TAX_SEGMENT28,
409 INTERIM_TAX_SEGMENT29,
410 INTERIM_TAX_SEGMENT30
411 )
412 VALUES
413 (
414 p_int_distributions_rec.interface_distribution_id,
415 p_int_distributions_rec.interface_line_id,
416 p_int_distributions_rec.interface_line_context,
417 p_int_distributions_rec.interface_line_attribute1,
418 p_int_distributions_rec.interface_line_attribute2,
419 p_int_distributions_rec.interface_line_attribute3,
420 p_int_distributions_rec.interface_line_attribute4,
421 p_int_distributions_rec.interface_line_attribute5,
422 p_int_distributions_rec.interface_line_attribute6,
423 p_int_distributions_rec.interface_line_attribute7,
424 p_int_distributions_rec.interface_line_attribute8,
425 p_int_distributions_rec.interface_line_attribute9,
426 p_int_distributions_rec.interface_line_attribute10,
427 p_int_distributions_rec.interface_line_attribute11,
428 p_int_distributions_rec.interface_line_attribute12,
429 p_int_distributions_rec.interface_line_attribute13,
430 p_int_distributions_rec.interface_line_attribute14,
431 p_int_distributions_rec.interface_line_attribute15,
432 p_int_distributions_rec.account_class,
433 p_int_distributions_rec.amount,
434 p_int_distributions_rec.acctd_amount,
435 p_int_distributions_rec.percent,
436 p_int_distributions_rec.interface_status,
437 p_int_distributions_rec.request_id,
438 p_int_distributions_rec.code_combination_id,
439 p_int_distributions_rec.segment1,
440 p_int_distributions_rec.segment2,
441 p_int_distributions_rec.segment3,
442 p_int_distributions_rec.segment4,
443 p_int_distributions_rec.segment5,
444 p_int_distributions_rec.segment6,
445 p_int_distributions_rec.segment7,
446 p_int_distributions_rec.segment8,
447 p_int_distributions_rec.segment9,
448 p_int_distributions_rec.segment10,
449 p_int_distributions_rec.segment11,
450 p_int_distributions_rec.segment12,
451 p_int_distributions_rec.segment13,
452 p_int_distributions_rec.segment14,
453 p_int_distributions_rec.segment15,
454 p_int_distributions_rec.segment16,
455 p_int_distributions_rec.segment17,
456 p_int_distributions_rec.segment18,
457 p_int_distributions_rec.segment19,
458 p_int_distributions_rec.segment20,
459 p_int_distributions_rec.segment21,
460 p_int_distributions_rec.segment22,
461 p_int_distributions_rec.segment23,
462 p_int_distributions_rec.segment24,
463 p_int_distributions_rec.segment25,
464 p_int_distributions_rec.segment26,
465 p_int_distributions_rec.segment27,
466 p_int_distributions_rec.segment28,
467 p_int_distributions_rec.segment29,
468 p_int_distributions_rec.segment30,
469 p_int_distributions_rec.comments,
470 p_int_distributions_rec.attribute_category,
471 p_int_distributions_rec.attribute1,
472 p_int_distributions_rec.attribute2,
473 p_int_distributions_rec.attribute3,
474 p_int_distributions_rec.attribute4,
475 p_int_distributions_rec.attribute5,
476 p_int_distributions_rec.attribute6,
477 p_int_distributions_rec.attribute7,
478 p_int_distributions_rec.attribute8,
479 p_int_distributions_rec.attribute9,
480 p_int_distributions_rec.attribute10,
481 p_int_distributions_rec.attribute11,
482 p_int_distributions_rec.attribute12,
483 p_int_distributions_rec.attribute13,
484 p_int_distributions_rec.attribute14,
485 p_int_distributions_rec.attribute15,
486 p_int_distributions_rec.created_by,
487 p_int_distributions_rec.creation_date,
488 p_int_distributions_rec.last_updated_by,
489 p_int_distributions_rec.last_update_date,
490 p_int_distributions_rec.last_update_login,
491 p_int_distributions_rec.org_id,
492 p_int_distributions_rec.interim_tax_ccid,
493 p_int_distributions_rec.interim_tax_segment1,
494 p_int_distributions_rec.interim_tax_segment2,
495 p_int_distributions_rec.interim_tax_segment3,
496 p_int_distributions_rec.interim_tax_segment4,
497 p_int_distributions_rec.interim_tax_segment5,
498 p_int_distributions_rec.interim_tax_segment6,
499 p_int_distributions_rec.interim_tax_segment7,
500 p_int_distributions_rec.interim_tax_segment8,
501 p_int_distributions_rec.interim_tax_segment9,
502 p_int_distributions_rec.interim_tax_segment10,
503 p_int_distributions_rec.interim_tax_segment11,
504 p_int_distributions_rec.interim_tax_segment12,
505 p_int_distributions_rec.interim_tax_segment13,
506 p_int_distributions_rec.interim_tax_segment14,
507 p_int_distributions_rec.interim_tax_segment15,
508 p_int_distributions_rec.interim_tax_segment16,
509 p_int_distributions_rec.interim_tax_segment17,
510 p_int_distributions_rec.interim_tax_segment18,
511 p_int_distributions_rec.interim_tax_segment19,
512 p_int_distributions_rec.interim_tax_segment20,
513 p_int_distributions_rec.interim_tax_segment21,
514 p_int_distributions_rec.interim_tax_segment22,
515 p_int_distributions_rec.interim_tax_segment23,
516 p_int_distributions_rec.interim_tax_segment24,
517 p_int_distributions_rec.interim_tax_segment25,
518 p_int_distributions_rec.interim_tax_segment26,
519 p_int_distributions_rec.interim_tax_segment27,
520 p_int_distributions_rec.interim_tax_segment28,
521 p_int_distributions_rec.interim_tax_segment29,
522 p_int_distributions_rec.interim_tax_segment30
523 );
524
525 x_return_status := FND_API.g_ret_sts_success;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
530 FND_MESSAGE.set_name('OZF', 'OZF_AR_DISTRIBUTE_ERROR');
531 FND_MSG_PUB.add;
532 END IF;
533 IF OZF_DEBUG_LOW_ON THEN
534 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
535 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
536 FND_MSG_PUB.Add;
537 END IF;
538 x_return_status := FND_API.g_ret_sts_unexp_error;
539 END Insert_Int_Distributions ;
540
541 PROCEDURE Insert_Interface_Line
542 ( p_interface_line_rec IN RA_Interface_Lines_Rec_Type
543 ,x_return_status OUT NOCOPY VARCHAR2
544 ) IS
545 BEGIN
546
547 INSERT INTO RA_INTERFACE_LINES_ALL
548 ( CREATED_BY
549 , CREATION_DATE
550 , LAST_UPDATED_BY
551 , LAST_UPDATE_DATE
552 , INTERFACE_LINE_ATTRIBUTE1
553 , INTERFACE_LINE_ATTRIBUTE2
554 , INTERFACE_LINE_ATTRIBUTE3
555 , INTERFACE_LINE_ATTRIBUTE4
556 , INTERFACE_LINE_ATTRIBUTE5
557 , INTERFACE_LINE_ATTRIBUTE6
558 , INTERFACE_LINE_ATTRIBUTE7
559 , INTERFACE_LINE_ATTRIBUTE8
560 , INTERFACE_LINE_ATTRIBUTE9
561 , INTERFACE_LINE_ATTRIBUTE10
562 , INTERFACE_LINE_ATTRIBUTE11
563 , INTERFACE_LINE_ATTRIBUTE12
564 , INTERFACE_LINE_ATTRIBUTE13
565 , INTERFACE_LINE_ATTRIBUTE14
566 , INTERFACE_LINE_ATTRIBUTE15
567 , INTERFACE_LINE_CONTEXT
568 , BATCH_SOURCE_NAME
569 , GL_DATE
570 , SET_OF_BOOKS_ID
571 , LINE_TYPE
572 , DESCRIPTION
573 , CURRENCY_CODE
574 , AMOUNT
575 , CONVERSION_TYPE
576 , CONVERSION_DATE
577 , CONVERSION_RATE
578 , CUST_TRX_TYPE_ID
579 , TERM_ID
580 , ORIG_SYSTEM_BILL_CUSTOMER_ID
581 , ORIG_SYSTEM_BILL_ADDRESS_ID
582 , ORIG_SYSTEM_BILL_CONTACT_ID
583 , ORIG_SYSTEM_SHIP_CUSTOMER_ID
584 , ORIG_SYSTEM_SHIP_ADDRESS_ID
585 , ORIG_SYSTEM_SHIP_CONTACT_ID
586 , ORIG_SYSTEM_SOLD_CUSTOMER_ID
587 , ORG_ID
588 , AGREEMENT_ID
589 , COMMENTS
590 , CREDIT_METHOD_FOR_ACCT_RULE
591 , CREDIT_METHOD_FOR_INSTALLMENTS
592 , CUSTOMER_BANK_ACCOUNT_ID
593 , DOCUMENT_NUMBER
594 , DOCUMENT_NUMBER_SEQUENCE_ID
595 , HEADER_ATTRIBUTE_CATEGORY
596 , HEADER_ATTRIBUTE1
597 , HEADER_ATTRIBUTE2
598 , HEADER_ATTRIBUTE3
599 , HEADER_ATTRIBUTE4
600 , HEADER_ATTRIBUTE5
601 , HEADER_ATTRIBUTE6
602 , HEADER_ATTRIBUTE7
603 , HEADER_ATTRIBUTE8
604 , HEADER_ATTRIBUTE9
605 , HEADER_ATTRIBUTE10
606 , HEADER_ATTRIBUTE11
607 , HEADER_ATTRIBUTE12
608 , HEADER_ATTRIBUTE13
609 , HEADER_ATTRIBUTE14
610 , HEADER_ATTRIBUTE15
611 , INITIAL_CUSTOMER_TRX_ID
612 , INTERNAL_NOTES
613 , INVOICING_RULE_ID
614 , ORIG_SYSTEM_BATCH_NAME
615 , PREVIOUS_CUSTOMER_TRX_ID
616 , PRIMARY_SALESREP_ID
617 , PRINTING_OPTION
618 , PURCHASE_ORDER
619 , PURCHASE_ORDER_REVISION
620 , PURCHASE_ORDER_DATE
621 , REASON_CODE
622 , RECEIPT_METHOD_ID
623 , RELATED_CUSTOMER_TRX_ID
624 , TERRITORY_ID
625 , TRX_DATE
626 , TRX_NUMBER
627 , MEMO_LINE_ID
628 , TAX_CODE
629 , INVENTORY_ITEM_ID
630 , QUANTITY
631 , UOM_CODE
632 , UNIT_SELLING_PRICE
633 , LEGAL_ENTITY_ID
634 , SOURCE_APPLICATION_ID
635 , SOURCE_ENTITY_CODE
636 ,SOURCE_EVENT_CLASS_CODE
637 -- Fix for Bug#13329321
638 ,TAX_EXEMPT_FLAG
639 )
640 VALUES (
641 p_interface_line_rec.created_by
642 , p_interface_line_rec.creation_date
643 , p_interface_line_rec.last_updated_by
644 , p_interface_line_rec.last_update_date
645 , p_interface_line_rec.interface_line_attribute1
646 , p_interface_line_rec.interface_line_attribute2
647 , p_interface_line_rec.interface_line_attribute3
648 , p_interface_line_rec.interface_line_attribute4
649 , p_interface_line_rec.interface_line_attribute5
650 , p_interface_line_rec.interface_line_attribute6
651 , p_interface_line_rec.interface_line_attribute7
652 , p_interface_line_rec.interface_line_attribute8
653 , p_interface_line_rec.interface_line_attribute9
654 , p_interface_line_rec.interface_line_attribute10
655 , p_interface_line_rec.interface_line_attribute11
656 , p_interface_line_rec.interface_line_attribute12
657 , p_interface_line_rec.interface_line_attribute13
658 , p_interface_line_rec.interface_line_attribute14
659 , p_interface_line_rec.interface_line_attribute15
660 , p_interface_line_rec.interface_line_context
661 , p_interface_line_rec.batch_source_name
662 , p_interface_line_rec.gl_date
663 , p_interface_line_rec.set_of_books_id
664 , p_interface_line_rec.line_type
665 , p_interface_line_rec.description
666 , p_interface_line_rec.currency_code
667 , p_interface_line_rec.amount
668 , p_interface_line_rec.conversion_type
669 , p_interface_line_rec.conversion_date
670 , p_interface_line_rec.conversion_rate
671 , p_interface_line_rec.cust_trx_type_id
672 , p_interface_line_rec.term_id
673 , p_interface_line_rec.orig_system_bill_customer_id
674 , p_interface_line_rec.orig_system_bill_address_id
675 , p_interface_line_rec.orig_system_bill_contact_id
676 , p_interface_line_rec.orig_system_ship_customer_id
677 , p_interface_line_rec.orig_system_ship_address_id
678 , p_interface_line_rec.orig_system_ship_contact_id
679 , p_interface_line_rec.orig_system_sold_customer_id
680 , p_interface_line_rec.org_id
681 , p_interface_line_rec.agreement_id
682 , p_interface_line_rec.comments
683 , p_interface_line_rec.credit_method_for_acct_rule
684 , p_interface_line_rec.credit_method_for_installments
685 , p_interface_line_rec.customer_bank_account_id
686 , p_interface_line_rec.document_number
687 , p_interface_line_rec.document_number_sequence_id
688 , p_interface_line_rec.header_attribute_category
689 , p_interface_line_rec.header_attribute1
690 , p_interface_line_rec.header_attribute2
691 , p_interface_line_rec.header_attribute3
692 , p_interface_line_rec.header_attribute4
693 , p_interface_line_rec.header_attribute5
694 , p_interface_line_rec.header_attribute6
695 , p_interface_line_rec.header_attribute7
696 , p_interface_line_rec.header_attribute8
697 , p_interface_line_rec.header_attribute9
698 , p_interface_line_rec.header_attribute10
699 , p_interface_line_rec.header_attribute11
700 , p_interface_line_rec.header_attribute12
701 , p_interface_line_rec.header_attribute13
702 , p_interface_line_rec.header_attribute14
703 , p_interface_line_rec.header_attribute15
704 , p_interface_line_rec.initial_customer_trx_id
705 , p_interface_line_rec.internal_notes
706 , p_interface_line_rec.invoicing_rule_id
707 , p_interface_line_rec.orig_system_batch_name
708 , p_interface_line_rec.previous_customer_trx_id
709 , p_interface_line_rec.primary_salesrep_id
710 , p_interface_line_rec.printing_option
711 , p_interface_line_rec.purchase_order
712 , p_interface_line_rec.purchase_order_revision
713 , p_interface_line_rec.purchase_order_date
714 , p_interface_line_rec.reason_code
715 , p_interface_line_rec.receipt_method_id
716 , p_interface_line_rec.related_customer_trx_id
717 , p_interface_line_rec.territory_id
718 , p_interface_line_rec.trx_date
719 , p_interface_line_rec.trx_number
720 , p_interface_line_rec.memo_line_id
721 , p_interface_line_rec.tax_code
722 , p_interface_line_rec.inventory_item_id
723 , p_interface_line_rec.quantity
724 , p_interface_line_rec.uom_code
725 , p_interface_line_rec.unit_selling_price
726 , p_interface_line_rec.legal_entity_id
727 , 682
728 ,'OZF_CLAIMS'
729 , 'TRADE_MGT_RECEIVABLES'
730 -- Fix for Bug#13329321
731 , 'S'
732 );
733
734 x_return_status := FND_API.g_ret_sts_success;
735 EXCEPTION
736 WHEN OTHERS THEN
737 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
738 FND_MESSAGE.set_name('OZF', 'OZF_AR_INTERFACE_ERROR');
739 FND_MSG_PUB.add;
740 END IF;
741 IF OZF_DEBUG_LOW_ON THEN
742 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
743 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
744 FND_MSG_PUB.Add;
745 END IF;
746 x_return_status := FND_API.g_ret_sts_unexp_error;
747 END Insert_Interface_Line ;
748
749
750 PROCEDURE Insert_Interface_Tax
751 ( p_interface_tax_rec IN RA_Interface_Lines_Rec_Type
752 ,x_return_status OUT NOCOPY VARCHAR2
753 ) IS
754 BEGIN
755
756 INSERT INTO RA_INTERFACE_LINES_ALL
757 ( CREATED_BY
758 , CREATION_DATE
759 , LAST_UPDATED_BY
760 , LAST_UPDATE_DATE
761 , INTERFACE_LINE_ATTRIBUTE1
762 , INTERFACE_LINE_ATTRIBUTE2
763 , INTERFACE_LINE_ATTRIBUTE3
764 , INTERFACE_LINE_ATTRIBUTE4
765 , INTERFACE_LINE_ATTRIBUTE5
766 , INTERFACE_LINE_ATTRIBUTE6
767 , INTERFACE_LINE_ATTRIBUTE7
768 , INTERFACE_LINE_ATTRIBUTE8
769 , INTERFACE_LINE_ATTRIBUTE9
770 , INTERFACE_LINE_ATTRIBUTE10
771 , INTERFACE_LINE_ATTRIBUTE11
772 , INTERFACE_LINE_ATTRIBUTE12
773 , INTERFACE_LINE_ATTRIBUTE13
774 , INTERFACE_LINE_ATTRIBUTE14
775 , INTERFACE_LINE_ATTRIBUTE15
776 , INTERFACE_LINE_CONTEXT
777 , LINK_TO_LINE_ATTRIBUTE1
778 , LINK_TO_LINE_ATTRIBUTE2
779 , LINK_TO_LINE_ATTRIBUTE3
780 , LINK_TO_LINE_ATTRIBUTE4
781 , LINK_TO_LINE_ATTRIBUTE5
782 , LINK_TO_LINE_ATTRIBUTE6
783 , LINK_TO_LINE_ATTRIBUTE7
784 , LINK_TO_LINE_ATTRIBUTE8
785 , LINK_TO_LINE_ATTRIBUTE9
786 , LINK_TO_LINE_ATTRIBUTE10
787 , LINK_TO_LINE_ATTRIBUTE11
788 , LINK_TO_LINE_ATTRIBUTE12
789 , LINK_TO_LINE_ATTRIBUTE13
790 , LINK_TO_LINE_ATTRIBUTE14
791 , LINK_TO_LINE_ATTRIBUTE15
792 , LINK_TO_LINE_CONTEXT
793 , LINE_TYPE
794 , TAX_CODE
795 , TAX_RATE
796 , DESCRIPTION
797 , BATCH_SOURCE_NAME
798 , SET_OF_BOOKS_ID
799 , CUST_TRX_TYPE_ID
800 , GL_DATE
801 , CURRENCY_CODE
802 , CONVERSION_TYPE
803 , CONVERSION_RATE
804 , ORG_ID
805 ,LEGAL_ENTITY_ID
806 )
807 VALUES (
808 p_interface_tax_rec.created_by
809 , p_interface_tax_rec.creation_date
810 , p_interface_tax_rec.last_updated_by
811 , p_interface_tax_rec.last_update_date
812 , p_interface_tax_rec.interface_line_attribute1
813 , p_interface_tax_rec.interface_line_attribute2
814 , p_interface_tax_rec.interface_line_attribute3
815 , p_interface_tax_rec.interface_line_attribute4
816 , p_interface_tax_rec.interface_line_attribute5
817 , p_interface_tax_rec.interface_line_attribute6
818 , p_interface_tax_rec.interface_line_attribute7
819 , p_interface_tax_rec.interface_line_attribute8
820 , p_interface_tax_rec.interface_line_attribute9
821 , p_interface_tax_rec.interface_line_attribute10
822 , p_interface_tax_rec.interface_line_attribute11
823 , p_interface_tax_rec.interface_line_attribute12
824 , p_interface_tax_rec.interface_line_attribute13
825 , p_interface_tax_rec.interface_line_attribute14
826 , p_interface_tax_rec.interface_line_attribute15
827 , p_interface_tax_rec.interface_line_context
828 , p_interface_tax_rec.link_to_line_attribute1
829 , p_interface_tax_rec.link_to_line_attribute2
830 , p_interface_tax_rec.link_to_line_attribute3
831 , p_interface_tax_rec.link_to_line_attribute4
832 , p_interface_tax_rec.link_to_line_attribute5
833 , p_interface_tax_rec.link_to_line_attribute6
834 , p_interface_tax_rec.link_to_line_attribute7
835 , p_interface_tax_rec.link_to_line_attribute8
836 , p_interface_tax_rec.link_to_line_attribute9
837 , p_interface_tax_rec.link_to_line_attribute10
838 , p_interface_tax_rec.link_to_line_attribute11
839 , p_interface_tax_rec.link_to_line_attribute12
840 , p_interface_tax_rec.link_to_line_attribute13
841 , p_interface_tax_rec.link_to_line_attribute14
842 , p_interface_tax_rec.link_to_line_attribute15
843 , p_interface_tax_rec.link_to_line_context
844 , p_interface_tax_rec.line_type
845 , p_interface_tax_rec.tax_code
846 , p_interface_tax_rec.tax_rate
847 , p_interface_tax_rec.description
848 , p_interface_tax_rec.batch_source_name
849 , p_interface_tax_rec.set_of_books_id
850 , p_interface_tax_rec.cust_trx_type_id
851 , p_interface_tax_rec.gl_date
852 , p_interface_tax_rec.currency_code
853 , p_interface_tax_rec.conversion_type
854 , p_interface_tax_rec.conversion_rate
855 , p_interface_tax_rec.org_id
856 , p_interface_tax_rec.legal_entity_id
857 );
858
859 x_return_status := FND_API.g_ret_sts_success;
860 EXCEPTION
861 WHEN OTHERS THEN
862 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
863 FND_MESSAGE.set_name('OZF', 'OZF_AR_INTERFACE_ERROR');
864 FND_MSG_PUB.add;
865 END IF;
866 IF OZF_DEBUG_LOW_ON THEN
867 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
868 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
869 FND_MSG_PUB.Add;
870 END IF;
871 x_return_status := FND_API.g_ret_sts_unexp_error;
872 END Insert_Interface_Tax;
873
874
875 PROCEDURE Insert_Interface_Sales_Credits(
876 p_int_sales_credits_rec IN RA_Int_Sales_Credits_Rec_Type
877 ,x_return_status OUT NOCOPY VARCHAR2
878 )
879 IS
880 BEGIN
881 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL(
882 CREATED_BY ,
883 CREATION_DATE ,
884 LAST_UPDATED_BY ,
885 LAST_UPDATE_DATE ,
886 LAST_UPDATE_LOGIN ,
887 SALESREP_ID ,
888 SALES_CREDIT_TYPE_ID ,
889 SALES_CREDIT_PERCENT_SPLIT ,
890 ATTRIBUTE_CATEGORY ,
891 ATTRIBUTE1 ,
892 ATTRIBUTE2 ,
893 ATTRIBUTE3 ,
894 ATTRIBUTE4 ,
895 ATTRIBUTE5 ,
896 ATTRIBUTE6 ,
897 ATTRIBUTE7 ,
898 ATTRIBUTE8 ,
899 ATTRIBUTE9 ,
900 ATTRIBUTE10 ,
901 ATTRIBUTE11 ,
902 ATTRIBUTE12 ,
903 ATTRIBUTE13 ,
904 ATTRIBUTE14 ,
905 ATTRIBUTE15 ,
906 INTERFACE_LINE_CONTEXT ,
907 INTERFACE_LINE_ATTRIBUTE1 ,
908 INTERFACE_LINE_ATTRIBUTE2 ,
909 INTERFACE_LINE_ATTRIBUTE3 ,
910 INTERFACE_LINE_ATTRIBUTE4 ,
911 INTERFACE_LINE_ATTRIBUTE5 ,
912 INTERFACE_LINE_ATTRIBUTE6 ,
913 INTERFACE_LINE_ATTRIBUTE7 ,
914 INTERFACE_LINE_ATTRIBUTE8 ,
915 INTERFACE_LINE_ATTRIBUTE9 ,
916 INTERFACE_LINE_ATTRIBUTE10 ,
917 INTERFACE_LINE_ATTRIBUTE11 ,
918 INTERFACE_LINE_ATTRIBUTE12 ,
919 INTERFACE_LINE_ATTRIBUTE13 ,
920 INTERFACE_LINE_ATTRIBUTE14 ,
921 INTERFACE_LINE_ATTRIBUTE15 ,
922 ORG_ID
923 )
924 VALUES (
925 p_int_sales_credits_rec.CREATED_BY ,
926 p_int_sales_credits_rec.CREATION_DATE ,
927 p_int_sales_credits_rec.LAST_UPDATED_BY ,
928 p_int_sales_credits_rec.LAST_UPDATE_DATE ,
929 p_int_sales_credits_rec.LAST_UPDATE_LOGIN ,
930 p_int_sales_credits_rec.SALESREP_ID ,
931 p_int_sales_credits_rec.SALES_CREDIT_TYPE_ID ,
932 p_int_sales_credits_rec.SALES_CREDIT_PERCENT_SPLIT ,
933 p_int_sales_credits_rec.ATTRIBUTE_CATEGORY ,
934 p_int_sales_credits_rec.ATTRIBUTE1 ,
935 p_int_sales_credits_rec.ATTRIBUTE2 ,
936 p_int_sales_credits_rec.ATTRIBUTE3 ,
937 p_int_sales_credits_rec.ATTRIBUTE4 ,
938 p_int_sales_credits_rec.ATTRIBUTE5 ,
939 p_int_sales_credits_rec.ATTRIBUTE6 ,
940 p_int_sales_credits_rec.ATTRIBUTE7 ,
941 p_int_sales_credits_rec.ATTRIBUTE8 ,
942 p_int_sales_credits_rec.ATTRIBUTE9 ,
943 p_int_sales_credits_rec.ATTRIBUTE10 ,
944 p_int_sales_credits_rec.ATTRIBUTE11 ,
945 p_int_sales_credits_rec.ATTRIBUTE12 ,
946 p_int_sales_credits_rec.ATTRIBUTE13 ,
947 p_int_sales_credits_rec.ATTRIBUTE14 ,
948 p_int_sales_credits_rec.ATTRIBUTE15 ,
949 p_int_sales_credits_rec.INTERFACE_LINE_CONTEXT ,
950 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE1 ,
951 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE2 ,
952 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE3 ,
953 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE4 ,
954 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE5 ,
955 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE6 ,
956 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE7 ,
957 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE8 ,
958 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE9 ,
959 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE10 ,
960 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE11 ,
961 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE12 ,
962 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE13 ,
963 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE14 ,
964 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE15 ,
965 p_int_sales_credits_rec.ORG_ID
966 );
967
968 x_return_status := FND_API.g_ret_sts_success;
969 EXCEPTION
970 WHEN OTHERS THEN
971 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
972 FND_MESSAGE.set_name('OZF', 'OZF_AR_INTERFACE_ERROR');
973 FND_MSG_PUB.add;
974 END IF;
975 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
976 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
977 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
978 FND_MSG_PUB.Add;
979 END IF;
980 x_return_status := FND_API.g_ret_sts_unexp_error;
981 END Insert_Interface_Sales_Credits;
982
983
984 /* ---------------------------------------------- *
985 * Function that returns the memo_line_id
986 * for a given claim_line_id.
987 * ---------------------------------------------- */
988
989 FUNCTION Get_Memo_Line_Id(p_claim_line_id IN NUMBER)
990 RETURN NUMBER IS
991
992 CURSOR csr_memo_line(cv_claim_line_id IN NUMBER) IS
993 SELECT item_id
994 FROM ozf_claim_lines
995 WHERE claim_line_id = cv_claim_line_id
996 AND item_type = 'MEMO_LINE';--11.5.10 Enhancements.
997 l_memo_line_id NUMBER;
998
999 BEGIN
1000 OPEN csr_memo_line (p_claim_line_id);
1001 FETCH csr_memo_line INTO l_memo_line_id;
1002 CLOSE csr_memo_line;
1003
1004 IF (l_memo_line_id IS NULL) THEN
1005 -- Make sure that if no memo line is found, then
1006 -- always return 0..
1007 --l_memo_line_id := p_claim_id;
1008 l_memo_line_id := 0;
1009 END IF;
1010
1011 RETURN l_memo_line_id;
1012
1013 END Get_Memo_Line_Id;
1014
1015 /* ---------------------------------------------- *
1016 * Populate the Interface Line Record
1017 * ---------------------------------------------- */
1018 PROCEDURE Populate_Interface_Line_Rec
1019 ( p_claim_rec IN Claim_Rec_Type
1020 ,p_memo_line_id IN NUMBER
1021 ,p_claim_line_id IN NUMBER
1022 ,p_line_claim_curr_amt IN NUMBER
1023 ,p_line_tax_code IN VARCHAR2
1024 ,p_line_cc_id_flag IN VARCHAR2
1025 ,p_x_interface_line_rec IN OUT NOCOPY RA_Interface_Lines_Rec_Type
1026 ,x_return_status OUT NOCOPY VARCHAR2
1027 ) IS
1028
1029 CURSOR sys_param_csr IS
1030 SELECT sp.batch_source_id,
1031 sp.billback_trx_type_id,
1032 sp.cm_trx_type_id
1033 FROM ozf_sys_parameters sp ;
1034
1035 CURSOR batch_source_csr (p_id IN NUMBER) IS
1036 SELECT name
1037 FROM ra_batch_sources
1038 WHERE batch_source_id = p_id ;
1039 /*
1040 CURSOR claim_type_csr(p_id IN NUMBER) IS
1041 SELECT NVL(transaction_type,0)
1042 FROM ozf_claim_types
1043 WHERE claim_type_id = p_id ;
1044 */
1045 CURSOR claim_type_csr(cv_claim_type_id IN NUMBER) IS
1046 SELECT cm_trx_type_id
1047 , dm_trx_type_id
1048 , cb_trx_type_id
1049 FROM ozf_claim_types_all_b
1050 WHERE claim_type_id = cv_claim_type_id;
1051
1052 CURSOR memo_line_csr(p_id IN NUMBER) IS
1053 SELECT description
1054 FROM ar_memo_lines
1055 WHERE memo_line_id = p_id;
1056
1057 CURSOR party_site_csr(p_id IN NUMBER) IS
1058 SELECT cust_acct_site_id
1059 FROM hz_cust_site_uses
1060 WHERE site_use_id = p_id ;
1061
1062 CURSOR csr_shipto_site(p_shipto_site_id IN NUMBER) IS
1063 SELECT cas.cust_account_id
1064 , cas.cust_acct_site_id
1065 FROM hz_cust_acct_sites cas
1066 , hz_cust_site_uses csu
1067 WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
1068 AND csu.site_use_id = p_shipto_site_id;
1069
1070
1071 CURSOR reason_code_csr(p_id IN NUMBER) IS
1072 SELECT reason_code
1073 FROM ozf_reason_codes_all_b
1074 WHERE reason_code_id = p_id;
1075
1076 CURSOR invoice_reason_code_csr(p_id IN NUMBER) IS
1077 SELECT invoicing_reason_code
1078 FROM ozf_reason_codes_all_b
1079 WHERE reason_code_id = p_id;
1080
1081 CURSOR trx_type_gl_flag_csr(p_trx_type_id IN NUMBER) IS
1082 SELECT post_to_gl
1083 FROM ra_cust_trx_types
1084 WHERE cust_trx_type_id = p_trx_type_id;
1085
1086 CURSOR csr_trx_type_payment_term(cv_claim_type_id IN NUMBER) IS
1087 SELECT default_term
1088 FROM ra_cust_trx_types trx
1089 , ozf_claim_types_all_b ct
1090 WHERE trx.cust_trx_type_id = ct.dm_trx_type_id
1091 AND ct.claim_type_id = cv_claim_type_id;
1092
1093 CURSOR csr_cust_payment_term(cv_cust_account_id IN NUMBER) IS
1094 SELECT standard_terms
1095 FROM hz_customer_profiles
1096 WHERE cust_account_id = cv_cust_account_id
1097 AND site_use_id IS NULL;
1098 /*
1099 SELECT payment_term_id
1100 FROM hz_cust_accounts
1101 WHERE cust_account_id = cv_cust_account_id;
1102 */
1103
1104 CURSOR csr_cust_address_pay_term(cv_cust_account_id IN NUMBER, cv_site_use_id IN NUMBER) IS
1105 SELECT standard_terms
1106 FROM hz_customer_profiles
1107 WHERE cust_account_id = cv_cust_account_id
1108 AND site_use_id = cv_site_use_id;
1109
1110 CURSOR csr_cust_site_pay_term(cv_cust_account_id IN NUMBER, cv_site_use_id IN NUMBER) IS
1111 SELECT use.payment_term_id
1112 FROM hz_cust_site_uses use
1113 , hz_cust_acct_sites site
1114 WHERE site.cust_acct_site_id = use.cust_acct_site_id
1115 AND site.cust_account_id = cv_cust_account_id
1116 AND use.site_use_id = cv_site_use_id;
1117
1118 CURSOR csr_claim_line_product(cv_claim_line_id IN NUMBER) IS
1119 SELECT item_id
1120 , quantity
1121 , quantity_uom
1122 , rate
1123 , item_type
1124 , item_description
1125 , source_object_class -- added for bug 4716020
1126 FROM ozf_claim_lines
1127 WHERE claim_line_id = cv_claim_line_id;
1128
1129 l_batch_source_id NUMBER;
1130 l_billback_trx_type_id NUMBER;
1131 l_cm_trx_type_id NUMBER;
1132 l_post_gl_flag VARCHAR2(1);
1133 l_claim_cm_trx_type_id NUMBER;
1134 l_claim_dm_trx_type_id NUMBER;
1135 l_claim_cb_trx_type_id NUMBER;
1136 l_reason_code VARCHAR2(30);
1137 l_invoicing_reason_code VARCHAR2(30);
1138 l_inventory_item_id NUMBER; --11.5.10 Enhancements.
1139 l_item_type VARCHAR2(30); --11.5.10 Enhancements.
1140 l_claim_line_item_desc VARCHAR2(240);
1141 l_source_object_class VARCHAR2(30); -- added for bug 4716020
1142
1143 -- Cursor to get claim reason name -- 11.5.10 Enhancements.
1144 CURSOR csr_get_reason_name (cv_reason_code_id IN NUMBER) IS
1145 SELECT SUBSTRB(name,1,30) name
1146 FROM ozf_reason_codes_vl
1147 WHERE reason_code_id = cv_reason_code_id;
1148
1149 CURSOR csr_product_desc(cv_inventory_item_id IN NUMBER) IS
1150 SELECT SUBSTRB(description, 1, 240)
1151 FROM mtl_system_items_vl
1152 WHERE inventory_item_id = cv_inventory_item_id
1153 AND organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID');
1154
1155 CURSOR csr_offer_name(cv_claim_line_id IN NUMBER)IS
1156 SELECT SUBSTR(offer_code,1,50)
1157 FROM ozf_offers offer, ozf_claim_lines_all line
1158 WHERE qp_list_header_id = line.offer_id
1159 AND claim_line_id = cv_claim_line_id;
1160
1161 -- Introduced for Bug4348163
1162 CURSOR csr_category_desc(cv_category_id IN NUMBER) IS
1163 SELECT SUBSTRB(category_desc,1,240)
1164 FROM eni_prod_den_hrchy_parents_v
1165 WHERE category_id = cv_category_id;
1166 l_line_description VARCHAR2(240);
1167
1168 CURSOR csr_media_desc(cv_media_channel_id IN NUMBER) IS
1169 SELECT channel_name
1170 FROM ams_media_channels_vl
1171 WHERE channel_id = cv_media_channel_id;
1172
1173 BEGIN
1174 -- Start populating the interface record values
1175 x_return_status := FND_API.g_ret_sts_success;
1176
1177 /* -- Standard Who Columns ------------------------- */
1178 p_x_interface_line_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1179 p_x_interface_line_rec.CREATION_DATE := SYSDATE ;
1180 p_x_interface_line_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1181 p_x_interface_line_rec.LAST_UPDATE_DATE := SYSDATE;
1182
1183 /* -- Fixed Values from unchangeble profiles ------- */
1184 p_x_interface_line_rec.INTERFACE_LINE_CONTEXT := 'CLAIM';
1185
1186 /* -- Batch Source --------------------------------- */
1187 -- This is set at the system parameter level
1188 -- System parameter will have only one record for an org
1189 -- No need to handle when no data found.
1190 OPEN sys_param_csr;
1191 FETCH sys_param_csr INTO l_batch_source_id,
1192 l_billback_trx_type_id,
1193 l_cm_trx_type_id ;
1194 CLOSE sys_param_csr;
1195
1196 IF (l_batch_source_id IS NULL) THEN
1197 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1198 FND_MESSAGE.set_name('OZF','OZF_BATCH_SRC_REQ_FOR_INTF');
1199 FND_MSG_PUB.add;
1200 END IF;
1201 x_return_status := FND_API.g_ret_sts_error;
1202 ELSE
1203 OPEN batch_source_csr(l_batch_source_id) ;
1204 FETCH batch_source_csr INTO p_x_interface_line_rec.batch_source_name;
1205 CLOSE batch_source_csr;
1206 END IF;
1207
1208 /* -- All Interface line attributes enabled for the context CLAIM --- */
1209 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1 := p_claim_rec.claim_number ;
1210 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2 := TO_CHAR(p_claim_rec.claim_id) ;
1211 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3 := TO_CHAR(p_claim_line_id);
1212 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4 := p_line_cc_id_flag;
1213
1214 /* -- 11.5.10 Enhancements. TM should pass claim comments. */
1215 p_x_interface_line_rec.comments := SUBSTRB(p_claim_rec.comments, 1, 240); -- Fix for bug 14052262
1216 --p_x_interface_line_rec.comments := SUBSTRB(p_claim_rec.comments, 1, 1760); -- fix for bug 6116037
1217
1218
1219 /* -- 11.5.10 Enhancements. TM should pass. */
1220 OPEN csr_get_reason_name(p_claim_rec.reason_code_id);
1221 FETCH csr_get_reason_name INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7; --reason name
1222 CLOSE csr_get_reason_name;
1223 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5 := NVL(p_claim_rec.customer_ref_number, '-');
1224 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6 := NVL(p_claim_rec.customer_reason, '-') ;
1225
1226 /* -- Only four attributes used for now
1227 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5 := '0';
1228 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6 := '0';
1229 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7 := '0';
1230 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE8 := '0';
1231 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE9 := '0';
1232 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE10 := '0';
1233 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE11 := '0';
1234 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE12 := '0';
1235 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE13 := '0';
1236 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE14 := '0';
1237 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE15 := '0';
1238 */
1239
1240 -- Bugfix 6115698: FP:11510-R12 5663890
1241 /* ------------------------------------------------- */
1242 -- Populate this mandatory attribute with the unique claim_id to group all
1243 -- lines from the claim into one Transaction in AR
1244 -- p_x_interface_line_rec.HEADER_ATTRIBUTE1 := TO_CHAR(p_claim_rec.claim_id);
1245
1246
1247 /* -- Claim Header Record Values ------------------- */
1248 p_x_interface_line_rec.SET_OF_BOOKS_ID := p_claim_rec.set_of_books_id;
1249 p_x_interface_line_rec.LINE_TYPE := 'LINE';
1250 p_x_interface_line_rec.CURRENCY_CODE := p_claim_rec.currency_code;
1251 IF p_claim_rec.exchange_rate_type IS NULL THEN
1252 p_x_interface_line_rec.CONVERSION_TYPE := 'User';
1253 p_x_interface_line_rec.CONVERSION_RATE := 1;
1254 ELSE
1255 p_x_interface_line_rec.CONVERSION_TYPE := p_claim_rec.exchange_rate_type;
1256 IF p_claim_rec.exchange_rate_type = 'User' THEN
1257 p_x_interface_line_rec.CONVERSION_RATE := p_claim_rec.exchange_rate;
1258 ELSE
1259 p_x_interface_line_rec.CONVERSION_RATE := NULL;
1260 END IF;
1261 END IF;
1262 p_x_interface_line_rec.CONVERSION_DATE := p_claim_rec.exchange_rate_date;
1263 -- 13-MAR-2002 mchang updated: assing value of conversion_rate is depending on conversion_rate
1264 --p_x_interface_line_rec.CONVERSION_RATE := p_claim_rec.exchange_rate;
1265 p_x_interface_line_rec.PRIMARY_SALESREP_ID := p_claim_rec.sales_rep_id;
1266
1267 p_x_interface_line_rec.ORG_ID := p_claim_rec.org_id;
1268 p_x_interface_line_rec.legal_entity_id := p_claim_rec.legal_entity_id;
1269
1270
1271 /* -- Populate Customer and Bill To site ------------------ */
1272 -- Figure out which customer/site to bill
1273 -- If pay_related_account_flag is T
1274 -- then
1275 -- customer_id will be related_cust_account_id and
1276 -- bill_to should be derived from related_site_use_id
1277 -- else
1278 -- customer_id will be cust_account_id and
1279 -- bill_to should be derived from cust_billto_acct_site_id
1280 -- ship_to should be derived from cust_shipto_acct_site_id
1281 --
1282 IF (p_claim_rec.pay_related_account_flag = 'T' ) THEN
1283 p_x_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID :=
1284 p_claim_rec.related_cust_account_id;
1285
1286 -- Get the bill_address_id
1287 OPEN party_site_csr(p_claim_rec.related_site_use_id);
1288 FETCH party_site_csr INTO
1289 p_x_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_ID ;
1290 CLOSE party_site_csr;
1291
1292
1293 -- Get the ship_to_address_id and ship to customer
1294 OPEN csr_shipto_site(p_claim_rec.cust_shipto_acct_site_id);
1295 FETCH csr_shipto_site into p_x_interface_line_rec.orig_system_ship_customer_id
1296 , p_x_interface_line_rec.orig_system_ship_address_id;
1297 CLOSE csr_shipto_site;
1298 p_x_interface_line_rec.orig_system_ship_customer_id := p_claim_rec.ship_to_cust_account_id;
1299 ELSE
1300 p_x_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID :=
1301 p_claim_rec.cust_account_id;
1302
1303 -- Get the bill_address_id
1304 OPEN party_site_csr(p_claim_rec.cust_billto_acct_site_id);
1305 FETCH party_site_csr INTO
1306 p_x_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_ID ;
1307 CLOSE party_site_csr;
1308
1309 -- Get the ship_to_address_id and ship to customer
1310 OPEN csr_shipto_site(p_claim_rec.cust_shipto_acct_site_id);
1311 FETCH csr_shipto_site into p_x_interface_line_rec.orig_system_ship_customer_id
1312 , p_x_interface_line_rec.orig_system_ship_address_id;
1313 CLOSE csr_shipto_site;
1314 END IF;
1315
1316
1317
1318 /* -- Get Reason Code ----------------------------------- */
1319 -- This is optional
1320 -- Derive it from ozf_reason_codes_all_b
1321 -- reason_code should be passed only for credit memos.
1322 -- It should not be passed for debit memos.
1323 IF p_claim_rec.payment_method = 'CREDIT_MEMO' AND
1324 p_claim_rec.reason_code_id IS NOT NULL THEN
1325 OPEN reason_code_csr(p_claim_rec.reason_code_id);
1326 FETCH reason_code_csr INTO l_reason_code;
1327 CLOSE reason_code_csr;
1328
1329 IF l_reason_code IS NOT NULL THEN
1330 p_x_interface_line_rec.REASON_CODE := l_reason_code;
1331 END IF;
1332 END IF;
1333
1334 /* -- Get Invoicing Reason Code ----------------------------------- */
1335 -- R12 Enhancements
1336 -- Invoicing Reason code should be passed only for debit memos.
1337
1338 IF p_claim_rec.payment_method = 'DEBIT_MEMO' AND
1339 p_claim_rec.reason_code_id IS NOT NULL THEN
1340 OPEN invoice_reason_code_csr(p_claim_rec.reason_code_id);
1341 FETCH invoice_reason_code_csr INTO l_invoicing_reason_code;
1342 CLOSE invoice_reason_code_csr;
1343
1344 IF l_invoicing_reason_code IS NOT NULL THEN
1345 p_x_interface_line_rec.REASON_CODE := l_invoicing_reason_code;
1346 END IF;
1347 END IF;
1348
1349
1350 /* -- Derive and Validate the transaction type ---------- */
1351 -- Check for Payment Method First
1352 IF ( p_claim_rec.payment_method IS NULL OR
1353 p_claim_rec.payment_method = FND_API.G_MISS_CHAR )
1354 THEN
1355 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1356 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_PAYMENT_METHOD');
1357 FND_MSG_PUB.add;
1358 END IF;
1359 ELSIF p_claim_rec.payment_method IS NOT NULL THEN
1360 -- Now get Trx_type from the claim type
1361 OPEN claim_type_csr(p_claim_rec.claim_type_id) ;
1362 --FETCH claim_type_csr INTO p_x_interface_line_rec.cust_trx_type_id ;
1363 FETCH claim_type_csr INTO l_claim_cm_trx_type_id
1364 , l_claim_dm_trx_type_id
1365 , l_claim_cb_trx_type_id;
1366 CLOSE claim_type_csr;
1367
1368 --Bug4249629: Effective date should be used as trx_date
1369 p_x_interface_line_rec.trx_date := p_claim_rec.effective_date;
1370 IF p_claim_rec.payment_method = 'CREDIT_MEMO' THEN
1371 p_x_interface_line_rec.cust_trx_type_id := l_claim_cm_trx_type_id;
1372
1373 -- amount sign is changed for on account credit memos
1374 p_x_interface_line_rec.AMOUNT := p_line_claim_curr_amt * -1;
1375
1376 ELSIF p_claim_rec.payment_method = 'DEBIT_MEMO' THEN
1377 p_x_interface_line_rec.cust_trx_type_id := l_claim_dm_trx_type_id;
1378
1379 -- For Regular and on-acc CM .. do not enter
1380 -- Others, its optional. ( In our case it is a DM )
1381 -- Receivable uses the following hierarchy to determine the default payment term,
1382 -- stopping when one is found
1383 -- 1. customer bill-to site level
1384 -- 2. customer address level
1385 -- 3. customer level
1386 -- 4. transaction type
1387
1388 OPEN csr_cust_site_pay_term(p_claim_rec.cust_account_id, p_claim_rec.cust_billto_acct_site_id);
1389 FETCH csr_cust_site_pay_term INTO p_x_interface_line_rec.TERM_ID;
1390 CLOSE csr_cust_site_pay_term;
1391
1392 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1393 OPEN csr_cust_address_pay_term(p_claim_rec.cust_account_id, p_claim_rec.cust_billto_acct_site_id);
1394 FETCH csr_cust_address_pay_term INTO p_x_interface_line_rec.TERM_ID;
1395 CLOSE csr_cust_address_pay_term;
1396 END IF;
1397
1398 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1399 OPEN csr_cust_payment_term(p_claim_rec.cust_account_id);
1400 FETCH csr_cust_payment_term INTO p_x_interface_line_rec.TERM_ID;
1401 CLOSE csr_cust_payment_term;
1402 END IF;
1403
1404 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1405 OPEN csr_trx_type_payment_term(p_claim_rec.claim_type_id);
1406 FETCH csr_trx_type_payment_term INTO p_x_interface_line_rec.TERM_ID;
1407 CLOSE csr_trx_type_payment_term;
1408 END IF;
1409
1410 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1411 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1412 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_PAYMENT_TERM');
1413 FND_MSG_PUB.add;
1414 END IF;
1415 x_return_status := FND_API.g_ret_sts_error;
1416 END IF;
1417
1418 -- Overpayment amount is negative; Creation sign for debit memo should be positive.
1419 p_x_interface_line_rec.AMOUNT := p_line_claim_curr_amt * -1;
1420
1421 ELSIF p_claim_rec.payment_method = 'CHARGEBACK' THEN
1422 p_x_interface_line_rec.cust_trx_type_id := l_claim_cb_trx_type_id;
1423
1424 p_x_interface_line_rec.AMOUNT := p_line_claim_curr_amt;
1425
1426 ELSE
1427 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1428 FND_MESSAGE.set_name('OZF', 'OZF_SETL_INT_ERR_PAYMETHOD');
1429 FND_MSG_PUB.add;
1430 END IF;
1431 x_return_status := FND_API.g_ret_sts_error;
1432 END IF;
1433
1434 -- If trx_type is not defined at claim type level, get it from
1435 -- system parameters
1436 IF p_x_interface_line_rec.cust_trx_type_id IS NULL THEN
1437 -- Verify the payment_method against the trx_type
1438 -- l_billback_trx_type_id and l_cm_trx_type_id are fetched
1439 -- from the sys_param_csr above
1440 IF ( p_claim_rec.payment_method IN ('CHARGEBACK','DEBIT_MEMO') )
1441 THEN
1442 IF l_billback_trx_type_id IS NULL THEN
1443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1444 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_BILLBACK_TRX_TYPE');
1445 FND_MSG_PUB.add;
1446 END IF;
1447 x_return_status := FND_API.g_ret_sts_error;
1448 ELSE
1449 p_x_interface_line_rec.cust_trx_type_id :=l_billback_trx_type_id;
1450 END IF;
1451 ELSIF p_claim_rec.payment_method = 'CREDIT_MEMO' THEN
1452 IF l_cm_trx_type_id IS NULL THEN
1453 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1454 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_CM_TRX_TYPE');
1455 FND_MSG_PUB.add;
1456 END IF;
1457 x_return_status := FND_API.g_ret_sts_error;
1458 ELSE
1459 p_x_interface_line_rec.cust_trx_type_id := l_cm_trx_type_id;
1460 END IF;
1461 END IF;
1462 END IF; -- End getting trx_type from system parameters
1463 END IF; -- End Trx_Type validation
1464
1465
1466
1467 /* ------------ Populate GL_DATE ------------------------------ */
1468 -- If the Post To Gl option on the transaction type is set to NO,
1469 -- the GL_DATE column should be NULL.
1470 OPEN trx_type_gl_flag_csr(p_x_interface_line_rec.cust_trx_type_id);
1471 FETCH trx_type_gl_flag_csr INTO l_post_gl_flag;
1472 CLOSE trx_type_gl_flag_csr;
1473
1474 IF l_post_gl_flag = 'Y' THEN
1475 IF OZF_CLAIM_SETTLEMENT_VAL_PVT.gl_date_in_open(222, p_claim_rec.claim_id) THEN
1476 p_x_interface_line_rec.GL_DATE := p_claim_rec.gl_date;
1477 END IF;
1478 END IF;
1479
1480 -- 11.5.10 Enhancements. AR should default
1481 IF p_claim_rec.payment_method IS NOT NULL
1482 AND p_claim_rec.payment_method = 'DEBIT_MEMO' THEN
1483 p_x_interface_line_rec.GL_DATE := NULL;
1484 END IF;
1485
1486
1487 /* ------ Tax Code ----------------------------*/
1488 p_x_interface_line_rec.tax_code := p_line_tax_code;
1489
1490
1491
1492 /* ------ Bug4348163: Populate Product Information ----------------------------*/
1493 OPEN csr_claim_line_product(p_claim_line_id);
1494 FETCH csr_claim_line_product INTO l_inventory_item_id
1495 , p_x_interface_line_rec.quantity
1496 , p_x_interface_line_rec.uom_code
1497 , p_x_interface_line_rec.unit_selling_price
1498 , l_item_type
1499 , l_claim_line_item_desc
1500 , l_source_object_class; -- added for bug 4716020
1501 CLOSE csr_claim_line_product;
1502 IF l_inventory_item_id IS NOT NULL THEN
1503 -- fix for bug 4716020
1504 IF l_source_object_class in ('INVOICE', 'CM', 'DM', 'CB', 'ORDER') THEN
1505 OPEN csr_product_desc(l_inventory_item_id);
1506 FETCH csr_product_desc INTO l_line_description;
1507 CLOSE csr_product_desc;
1508 p_x_interface_line_rec.inventory_item_id := l_inventory_item_id;
1509 END IF;
1510 -- end of fix for bug 4716020
1511
1512 IF l_item_type = 'PRODUCT' THEN
1513 OPEN csr_product_desc(l_inventory_item_id);
1514 FETCH csr_product_desc INTO l_line_description;
1515 CLOSE csr_product_desc;
1516 p_x_interface_line_rec.inventory_item_id := l_inventory_item_id;
1517
1518 ELSIF l_item_type = 'FAMILY' THEN
1519 OPEN csr_category_desc(l_inventory_item_id);
1520 FETCH csr_category_desc INTO l_line_description;
1521 CLOSE csr_category_desc;
1522
1523 ELSIF l_item_type = 'MEMO_LINE' THEN
1524 OPEN memo_line_csr(l_inventory_item_id);
1525 FETCH memo_line_csr INTO l_line_description;
1526 CLOSE memo_line_csr;
1527 p_x_interface_line_rec.memo_line_id := l_inventory_item_id;
1528 -- Fix for bug#8866818 - Start
1529 p_x_interface_line_rec.inventory_item_id := null;
1530 -- Fix for bug#8866818 - End
1531
1532 ELSIF l_item_type = 'MEDIA' THEN
1533 OPEN csr_media_desc(l_inventory_item_id);
1534 FETCH csr_media_desc INTO l_line_description;
1535 CLOSE csr_media_desc;
1536
1537 END IF;
1538
1539 END IF;
1540 IF l_line_description IS NULL THEN
1541 l_line_description := NVL(l_claim_line_item_desc, p_claim_rec.claim_number) ;
1542 END IF;
1543 p_x_interface_line_rec.description := SUBSTRB(l_line_description,1,240) ;
1544 p_x_interface_line_rec.quantity := p_x_interface_line_rec.quantity * -1;
1545
1546
1547
1548 /* ------ Bug4348163: Populate Offer Information -------------------------------*/
1549 /*OPEN csr_offer_name(p_claim_line_id);
1550 FETCH csr_offer_name INTO p_x_interface_line_rec.purchase_order;
1551 CLOSE csr_offer_name;*/
1552
1553
1554 -- bug4436227: The below statement is the culprit, as it causes datatype mismatch
1555 --p_x_interface_line_rec.comments := p_claim_rec.comments;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 IF sys_param_csr%ISOPEN THEN
1560 CLOSE sys_param_csr ;
1561 END IF;
1562 IF batch_source_csr%ISOPEN THEN
1563 CLOSE batch_source_csr ;
1564 END IF;
1565 IF claim_type_csr%ISOPEN THEN
1566 CLOSE claim_type_csr ;
1567 END IF;
1568 IF memo_line_csr%ISOPEN THEN
1569 CLOSE memo_line_csr ;
1570 END IF;
1571 IF party_site_csr%ISOPEN THEN
1572 CLOSE party_site_csr ;
1573 END IF;
1574 IF reason_code_csr%ISOPEN THEN
1575 CLOSE reason_code_csr ;
1576 END IF;
1577 IF trx_type_gl_flag_csr%ISOPEN THEN
1578 CLOSE trx_type_gl_flag_csr ;
1579 END IF;
1580 IF csr_trx_type_payment_term%ISOPEN THEN
1581 CLOSE csr_trx_type_payment_term ;
1582 END IF;
1583 IF csr_cust_payment_term%ISOPEN THEN
1584 CLOSE csr_cust_payment_term ;
1585 END IF;
1586 IF csr_cust_address_pay_term%ISOPEN THEN
1587 CLOSE csr_cust_address_pay_term ;
1588 END IF;
1589 IF csr_cust_site_pay_term%ISOPEN THEN
1590 CLOSE csr_cust_site_pay_term ;
1591 END IF;
1592 IF csr_claim_line_product%ISOPEN THEN
1593 CLOSE csr_claim_line_product ;
1594 END IF;
1595
1596 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1597 FND_MESSAGE.set_name('OZF', 'OZF_POPULATE_INTF_ERROR');
1598 FND_MSG_PUB.add;
1599 END IF;
1600 IF OZF_DEBUG_LOW_ON THEN
1601 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1602 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1603 FND_MSG_PUB.Add;
1604 END IF;
1605 x_return_status := FND_API.g_ret_sts_unexp_error;
1606 END Populate_Interface_Line_Rec;
1607
1608
1609 /* ---------------------------------------------- *
1610 * Populate the Interface Tax Record
1611 * ---------------------------------------------- */
1612 PROCEDURE Populate_Interface_Tax_Rec
1613 ( p_line_tax_code IN VARCHAR2
1614 ,p_interface_line_rec IN RA_Interface_Lines_Rec_Type
1615 ,x_interface_tax_rec OUT NOCOPY RA_Interface_Lines_Rec_Type
1616 ,x_return_status OUT NOCOPY VARCHAR2
1617 ) IS
1618
1619 l_tax_rate NUMBER;
1620 l_tax_name VARCHAR2(60);
1621
1622 BEGIN
1623 -- Start populating the interface record values
1624 x_return_status := FND_API.g_ret_sts_success;
1625
1626 /* -- Standard Who Columns ------------------------- */
1627 x_interface_tax_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1628 x_interface_tax_rec.CREATION_DATE := SYSDATE ;
1629 x_interface_tax_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1630 x_interface_tax_rec.LAST_UPDATE_DATE := SYSDATE;
1631 /* ------------------------------------------------- */
1632
1633 /* -- required fields for tax lines -- */
1634
1635 x_interface_tax_rec.LINE_TYPE := 'TAX';
1636 x_interface_tax_rec.TAX_CODE := p_line_tax_code;
1637 x_interface_tax_rec.TAX_RATE := l_tax_rate;
1638
1639 /* -- use "claim_number || printed_tax_name" as a description for tax line. -- */
1640 x_interface_tax_rec.DESCRIPTION := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1 || l_tax_name;
1641
1642 /* -- some other required fields -- */
1643 x_interface_tax_rec.BATCH_SOURCE_NAME := p_interface_line_rec.BATCH_SOURCE_NAME;
1644 x_interface_tax_rec.SET_OF_BOOKS_ID := p_interface_line_rec.SET_OF_BOOKS_ID;
1645 x_interface_tax_rec.CUST_TRX_TYPE_ID := p_interface_line_rec.CUST_TRX_TYPE_ID;
1646 x_interface_tax_rec.GL_DATE := p_interface_line_rec.GL_DATE;
1647 x_interface_tax_rec.CURRENCY_CODE := p_interface_line_rec.CURRENCY_CODE;
1648 x_interface_tax_rec.CONVERSION_TYPE := p_interface_line_rec.CONVERSION_TYPE;
1649 x_interface_tax_rec.CONVERSION_RATE := p_interface_line_rec.CONVERSION_RATE;
1650 x_interface_tax_rec.ORG_ID := p_interface_line_rec.ORG_ID;
1651 x_interface_tax_rec.LEGAL_ENTITY_ID := p_interface_line_rec.LEGAL_ENTITY_ID;
1652
1653 /* -- Fixed Values from unchangeble profiles ------- */
1654 x_interface_tax_rec.INTERFACE_LINE_CONTEXT := 'CLAIM';
1655
1656 /* -- All Interface line attributes enabled for the context CLAIM --- */
1657 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE1 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1;
1658 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE2 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2;
1659 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE3 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3 || 'TAX';
1660 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE4 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4;
1661 /* -- Only four attributes used for now
1662 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE5 := '0';
1663 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE6 := '0';
1664 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE7 := '0';
1665 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE8 := '0';
1666 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE9 := '0';
1667 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE10 := '0';
1668 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE11 := '0';
1669 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE12 := '0';
1670 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE13 := '0';
1671 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE14 := '0';
1672 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE15 := '0';
1673 */
1674
1675 /* -- Use Link_To Transaction flexfields to link transaction lines together -- */
1676 x_interface_tax_rec.LINK_TO_LINE_CONTEXT := p_interface_line_rec.INTERFACE_LINE_CONTEXT;
1677 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE1 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1;
1678 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE2 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2;
1679 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE3 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3;
1680 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE4 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4;
1681 /* -- Only four attributes used for now
1682 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE5 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5;
1683 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE6 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6;
1684 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE7 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7;
1685 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE8 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE8;
1686 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE9 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE9;
1687 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE10 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE10;
1688 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE11 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE11;
1689 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE12 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE12;
1690 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE13 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE13;
1691 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE14 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE14;
1692 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE15 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE15;
1693 */
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1698 FND_MESSAGE.set_name('OZF', 'OZF_POPULATE_INTF_ERROR');
1699 FND_MSG_PUB.add;
1700 END IF;
1701 IF OZF_DEBUG_LOW_ON THEN
1702 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1703 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1704 FND_MSG_PUB.Add;
1705 END IF;
1706 x_return_status := FND_API.g_ret_sts_unexp_error;
1707 END Populate_Interface_Tax_Rec;
1708
1709
1710 /* ---------------------------------------------- *
1711 * Populate the Interface Distributions Record
1712 * ---------------------------------------------- */
1713 PROCEDURE Populate_Distributions_Rec
1714 ( p_claim_rec IN Claim_Rec_Type
1715 ,p_claim_line_id IN NUMBER
1716 ,p_cc_id_rec IN OZF_GL_INTERFACE_PVT.CC_ID_REC
1717 ,p_x_int_distributions_rec IN OUT NOCOPY RA_Int_Distributions_Rec_Type
1718 ,x_return_status OUT NOCOPY VARCHAR2
1719 ) IS
1720 -- Cursor to get claim reason name -- 11.5.10 Enhancements.
1721 CURSOR csr_get_reason_name (cv_reason_code_id IN NUMBER) IS
1722 -- [BEGIN OF BUG 3500049 FIXING]
1723 -- SELECT substr(name,1,30) name
1724 SELECT SUBSTRB(name,1,30) name
1725 -- [END OF BUG 3500049 FIXING]
1726 FROM ozf_reason_codes_vl
1727 WHERE reason_code_id = cv_reason_code_id;
1728
1729 BEGIN
1730 -- Start populating the interface record values
1731 x_return_status := FND_API.g_ret_sts_success;
1732
1733 /* -- Standard Who Columns ------------------------- */
1734 p_x_int_distributions_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1735 p_x_int_distributions_rec.CREATION_DATE := SYSDATE ;
1736 p_x_int_distributions_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1737 p_x_int_distributions_rec.LAST_UPDATE_DATE := SYSDATE;
1738 /* ------------------------------------------------- */
1739
1740 /* -- Fixed Values from unchangeble profiles ------- */
1741 p_x_int_distributions_rec.INTERFACE_LINE_CONTEXT := 'CLAIM';
1742
1743 /* ------------------------------------------------- */
1744
1745 /* -- All Interface line attributes enabled for the context CLAIM --- */
1746 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE1 := p_claim_rec.claim_number ;
1747 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE2 := TO_CHAR(p_claim_rec.claim_id) ;
1748 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE3 := TO_CHAR(p_claim_line_id);
1749 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE4 := 'Y';
1750
1751 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE5 := NVL(p_claim_rec.customer_ref_number, '-');
1752 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE6 := NVL(p_claim_rec.customer_reason, '-') ;
1753
1754 OPEN csr_get_reason_name(p_claim_rec.reason_code_id);
1755 FETCH csr_get_reason_name INTO p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE7; --reason name
1756 CLOSE csr_get_reason_name;
1757
1758 /* -- Only four attributes used for now
1759 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE5 := '0';
1760 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE6 := '0';
1761 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE7 := '0';
1762 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE8 := '0';
1763 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE9 := '0';
1764 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE10 := '0';
1765 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE11 := '0';
1766 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE12 := '0';
1767 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE13 := '0';
1768 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE14 := '0';
1769 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE15 := '0';
1770 */
1771
1772 p_x_int_distributions_rec.CODE_COMBINATION_ID := p_cc_id_rec.code_combination_id;
1773 p_x_int_distributions_rec.ACCOUNT_CLASS := 'REV';
1774
1775 /* -- Set Org Id ------------------- */
1776 p_x_int_distributions_rec.ORG_ID := p_claim_rec.org_id;
1777
1778 /* ---- Finally populate the Amount ---------------------------- */
1779 /*
1780 IF p_claim_rec.payment_method = 'CREDIT_MEMO' THEN
1781 -- amount signs are changed for on account credit memos
1782 p_x_int_distributions_rec.AMOUNT := p_cc_id_rec.amount * -1;
1783 p_x_int_distributions_rec.ACCTD_AMOUNT := p_cc_id_rec.acctd_amount * -1;
1784 ELSIF p_claim_rec.payment_method = 'DEBIT_MEMO' THEN
1785 -- creation sign for debit memo is positive
1786 p_x_int_distributions_rec.AMOUNT := p_cc_id_rec.amount;
1787 p_x_int_distributions_rec.ACCTD_AMOUNT := p_cc_id_rec.acctd_amount;
1788 ELSIF p_claim_rec.payment_method = 'CHARGEBACK' THEN
1789 -- amount signs are changed for on account credit memos
1790 p_x_int_distributions_rec.AMOUNT := p_cc_id_rec.amount;
1791 p_x_int_distributions_rec.ACCTD_AMOUNT := p_cc_id_rec.acctd_amount;
1792 END IF;
1793 */
1794
1795 -- set the percent for amount
1796 p_x_int_distributions_rec.PERCENT := 100;
1797 /* ------------------------------------------------------------- */
1798
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1802 FND_MESSAGE.set_name('OZF', 'OZF_POPULATE_DIST_ERROR');
1803 FND_MSG_PUB.add;
1804 END IF;
1805 IF OZF_DEBUG_LOW_ON THEN
1806 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1807 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1808 FND_MSG_PUB.Add;
1809 END IF;
1810 x_return_status := FND_API.g_ret_sts_unexp_error;
1811 END Populate_Distributions_Rec;
1812
1813 PROCEDURE Interface_Claim_Line
1814 ( p_claim_id IN NUMBER
1815 ,p_memo_line_id IN NUMBER
1816 ,p_claim_line_id IN NUMBER
1817 ,p_line_claim_curr_amt IN NUMBER
1818 ,p_line_tax_code IN VARCHAR2
1819 ,p_line_cc_id_flag IN VARCHAR2
1820 ,x_return_status OUT NOCOPY VARCHAR2
1821 ) IS
1822
1823 l_interface_line_rec RA_Interface_Lines_Rec_Type;
1824 l_interface_tax_rec RA_Interface_Lines_Rec_Type;
1825 l_int_sales_credits_rec RA_Int_Sales_Credits_Rec_Type;
1826 l_claim_rec Claim_Rec_Type;
1827
1828 CURSOR csr_sales_credit_type(cv_salesrep_id IN NUMBER) IS
1829 SELECT sales_credit_type_id
1830 FROM ra_salesreps
1831 WHERE salesrep_id = cv_salesrep_id;
1832
1833 BEGIN
1834
1835 x_return_status := FND_API.G_RET_STS_SUCCESS;
1836
1837 Query_Claim ( p_claim_id => p_claim_id,
1838 x_claim_rec => l_claim_rec,
1839 x_return_status => x_return_status);
1840
1841 IF x_return_status = FND_API.g_ret_sts_error THEN
1842 RAISE FND_API.g_exc_error;
1843 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1844 RAISE FND_API.g_exc_unexpected_error;
1845 END IF;
1846
1847 --Populate the interface record with information from claim header and line
1848 Populate_Interface_Line_Rec(p_claim_rec => l_claim_rec,
1849 p_memo_line_id => p_memo_line_id,
1850 p_claim_line_id => p_claim_line_id,
1851 p_line_claim_curr_amt => p_line_claim_curr_amt,
1852 p_line_tax_code => p_line_tax_code,
1853 p_line_cc_id_flag => p_line_cc_id_flag,
1854 p_x_interface_line_rec => l_interface_line_rec,
1855 x_return_status => x_return_status
1856 );
1857 IF x_return_status = FND_API.g_ret_sts_error THEN
1858 RAISE FND_API.g_exc_error;
1859 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1860 RAISE FND_API.g_exc_unexpected_error;
1861 END IF;
1862
1863 --Insert the line into RA_INTERFACE_LINES_ALL
1864 Insert_Interface_Line(l_interface_line_rec,
1865 x_return_status);
1866
1867 IF x_return_status = FND_API.g_ret_sts_error THEN
1868 RAISE FND_API.g_exc_error;
1869 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1870 RAISE FND_API.g_exc_unexpected_error;
1871 END IF;
1872
1873 -------------------
1874 -- Sales Credits --
1875 -------------------
1876 IF l_claim_rec.sales_rep_id IS NOT NULL THEN
1877 l_int_sales_credits_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1878 l_int_sales_credits_rec.CREATION_DATE := SYSDATE ;
1879 l_int_sales_credits_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1880 l_int_sales_credits_rec.LAST_UPDATE_DATE := SYSDATE;
1881
1882 l_int_sales_credits_rec.INTERFACE_LINE_CONTEXT := l_interface_line_rec.INTERFACE_LINE_CONTEXT;
1883 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE1 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1;
1884 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE2 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2;
1885 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE3 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3;
1886 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE4 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4;
1887 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE5 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5;
1888 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE6 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6;
1889 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE7 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7;
1890
1891 l_int_sales_credits_rec.SALESREP_ID := l_claim_rec.sales_rep_id;
1892 OPEN csr_sales_credit_type(l_claim_rec.sales_rep_id);
1893 FETCH csr_sales_credit_type INTO l_int_sales_credits_rec.SALES_CREDIT_TYPE_ID;
1894 CLOSE csr_sales_credit_type;
1895 l_int_sales_credits_rec.SALES_CREDIT_PERCENT_SPLIT := 100;
1896 l_int_sales_credits_rec.ORG_ID := l_claim_rec.org_id;
1897
1898 Insert_Interface_Sales_Credits(
1899 l_int_sales_credits_rec,
1900 x_return_status
1901 );
1902 IF x_return_status = FND_API.g_ret_sts_error THEN
1903 RAISE FND_API.g_exc_error;
1904 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1905 RAISE FND_API.g_exc_unexpected_error;
1906 END IF;
1907 END IF;
1908
1909 /* passing tax lines through AutoInvoice if tax_code exists in claim line. */
1910 /*
1911 IF p_line_tax_code IS NOT NULL THEN
1912 --prepare tax line interface record
1913 Populate_Interface_Tax_Rec( p_line_tax_code => p_line_tax_code,
1914 p_interface_line_rec => l_interface_line_rec,
1915 x_interface_tax_rec => l_interface_tax_rec,
1916 x_return_status => x_return_status
1917 );
1918 IF x_return_status = FND_API.g_ret_sts_error THEN
1919 RAISE FND_API.g_exc_error;
1920 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1921 RAISE FND_API.g_exc_unexpected_error;
1922 END IF;
1923
1924 --Insert the tax into RA_INTERFACE_LINES_ALL
1925 Insert_Interface_Tax( l_interface_tax_rec,
1926 x_return_status
1927 );
1928 IF x_return_status = FND_API.g_ret_sts_error THEN
1929 RAISE FND_API.g_exc_error;
1930 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1931 RAISE FND_API.g_exc_unexpected_error;
1932 END IF;
1933 END IF; -- end of passing tax interface record.
1934 */
1935
1936 END Interface_Claim_Line;
1937
1938 PROCEDURE Distribute_Claim_Line
1939 ( p_claim_id IN NUMBER
1940 ,p_claim_line_id IN NUMBER
1941 ,p_cc_id_rec IN OZF_GL_INTERFACE_PVT.CC_ID_REC
1942 ,x_return_status OUT NOCOPY VARCHAR2
1943 ) IS
1944
1945 l_int_distributions_rec RA_Int_Distributions_Rec_Type;
1946 l_claim_rec Claim_Rec_Type;
1947
1948 BEGIN
1949
1950 x_return_status := FND_API.G_RET_STS_SUCCESS;
1951
1952 Query_Claim ( p_claim_id => p_claim_id,
1953 x_claim_rec => l_claim_rec,
1954 x_return_status => x_return_status);
1955
1956 IF x_return_status = FND_API.g_ret_sts_error THEN
1957 RAISE FND_API.g_exc_error;
1958 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1959 RAISE FND_API.g_exc_unexpected_error;
1960 END IF;
1961
1962 --Populate the distribution record with info from claim header and line
1963 Populate_Distributions_Rec(p_claim_rec => l_claim_rec,
1964 p_claim_line_id => p_claim_line_id,
1965 p_cc_id_rec => p_cc_id_rec,
1966 p_x_int_distributions_rec => l_int_distributions_rec,
1967 x_return_status => x_return_status
1968 );
1969 IF x_return_status = FND_API.g_ret_sts_error THEN
1970 RAISE FND_API.g_exc_error;
1971 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1972 RAISE FND_API.g_exc_unexpected_error;
1973 END IF;
1974
1975 -- Insert the line into RA_INTERFACE_DISTRIBUTIONS_ALL
1976 Insert_Int_Distributions(l_int_distributions_rec,
1977 x_return_status);
1978 IF x_return_status = FND_API.g_ret_sts_error THEN
1979 RAISE FND_API.g_exc_error;
1980 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1981 RAISE FND_API.g_exc_unexpected_error;
1982 END IF;
1983
1984 END Distribute_Claim_Line;
1985
1986
1987 PROCEDURE Interface_Claim
1988 ( p_api_version IN NUMBER
1989 ,p_init_msg_list IN VARCHAR2
1990 ,p_commit IN VARCHAR2
1991 ,p_validation_level IN VARCHAR2
1992 ,p_claim_id IN NUMBER
1993 ,x_return_status OUT NOCOPY VARCHAR2
1994 ,x_msg_data OUT NOCOPY VARCHAR2
1995 ,x_msg_count OUT NOCOPY NUMBER
1996 ) IS
1997
1998 --
1999 l_api_name CONSTANT VARCHAR2(30) := 'Interface_Claim' ;
2000 l_api_version CONSTANT NUMBER := 1.0;
2001 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2002 --
2003 l_resource_id NUMBER;
2004 l_user_id NUMBER;
2005 l_login_user_id NUMBER;
2006 l_login_user_status VARCHAR2(30);
2007 l_Error_Msg VARCHAR2(2000);
2008 l_Error_Token VARCHAR2(80);
2009 l_object_version_number NUMBER := 1;
2010 --x_msg_count NUMBER;
2011 --x_msg_data VARCHAR2(240);
2012 l_return_status VARCHAR2(1);
2013 l_result_out VARCHAR2(30);
2014
2015 /*
2016 CURSOR grouped_claim_line_csr IS
2017 SELECT SUM(NVL(cll.claim_currency_amount,0)),
2018 NVL(cll.claim_currency_amount,0)
2019 FROM ozf_claim_lines cll
2020 WHERE cll.claim_id = p_claim_id;
2021 GROUP BY Get_Memo_Line_Id(cll.claim_id);
2022 */
2023 --12.1 Price Protection Enhancement
2024 CURSOR claim_line_csr IS
2025 SELECT cll.claim_line_id
2026 , NVL(cll.claim_currency_amount,0)
2027 , cll.tax_code
2028 , Get_Memo_Line_Id(cll.claim_line_id)
2029 , cll.earnings_associated_flag
2030 , cll.source_object_class
2031 FROM ozf_claim_lines cll
2032 WHERE cll.claim_id = p_claim_id;
2033
2034 l_memo_line_id NUMBER;
2035 l_grpd_claim_curr_amt NUMBER;
2036 l_claim_line_id NUMBER;
2037 l_line_claim_curr_amt NUMBER;
2038 l_line_tax_code VARCHAR2(50);
2039 l_line_cc_id_flag VARCHAR2(1);
2040 l_earnings_asso_flag VARCHAR2(1);
2041 l_cc_id_tbl OZF_GL_INTERFACE_PVT.CC_ID_TBL;
2042
2043 --Bug3928503 - post_to_gl flag not considered for promotional claims
2044 l_post_to_gl varchar2(1);
2045 --12.1 Price Protection Enhancement
2046 l_source_object_class varchar2(25);
2047
2048 CURSOR claim_gl_posting_csr(p_id in number) IS
2049 SELECT osp.post_to_gl
2050 FROM ozf_sys_parameters_all osp
2051 , ozf_claims_all oc
2052 WHERE NVL(osp.org_id, -99) = NVL(oc.org_id, -99)
2053 AND oc.claim_id = p_id;
2054
2055
2056 BEGIN
2057
2058 /* ------- Begin Standard API Calls --------------------------- */
2059 -- Standard begin of API savepoint
2060 SAVEPOINT Interface_Claim_PVT ;
2061 -- Standard call to check for call compatibility.
2062 IF NOT FND_API.Compatible_API_Call (
2063 l_api_version,
2064 p_api_version,
2065 l_api_name,
2066 G_PKG_NAME)
2067 THEN
2068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2069 END IF;
2070 -- Debug Message
2071 IF OZF_DEBUG_LOW_ON THEN
2072 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2073 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
2074 FND_MSG_PUB.Add;
2075 END IF;
2076 --Initialize message list if p_init_msg_list is TRUE.
2077 IF FND_API.To_Boolean (p_init_msg_list) THEN
2078 FND_MSG_PUB.initialize;
2079 END IF;
2080 -- Initialize API return status to sucess
2081 x_return_status := FND_API.G_RET_STS_SUCCESS;
2082
2083 /* ------- End Standard API Calls ----------------------------- */
2084
2085 /* ------- Begin API Logic ------------------------------------ */
2086
2087 OPEN claim_gl_posting_csr(p_claim_id) ;
2088 FETCH claim_gl_posting_csr INTO l_post_to_gl;
2089 CLOSE claim_gl_posting_csr;
2090
2091
2092 OPEN claim_line_csr;
2093 LOOP
2094 l_line_cc_id_flag := NULL;
2095
2096 FETCH claim_line_csr INTO l_claim_line_id
2097 , l_line_claim_curr_amt
2098 , l_line_tax_code
2099 , l_memo_line_id
2100 , l_earnings_asso_flag
2101 , l_source_object_class;
2102 EXIT WHEN claim_line_csr%NOTFOUND;
2103 --12.1 Price Protection Enhancement
2104 IF ((l_earnings_asso_flag = 'T' AND l_post_to_gl = 'T') OR (l_source_object_class = 'PPCUSTOMER'))
2105 THEN
2106 OZF_GL_INTERFACE_PVT.Get_GL_Account(
2107 p_api_version => l_api_version
2108 ,p_init_msg_list => FND_API.g_false
2109 ,p_commit => FND_API.g_false
2110 ,p_validation_level => FND_API.g_valid_level_full
2111 ,x_return_status => l_return_status
2112 ,x_msg_data => x_msg_data
2113 ,x_msg_count => x_msg_count
2114 ,p_source_id => l_claim_line_id
2115 ,p_source_table => 'OZF_CLAIM_LINES_ALL'
2116 ,p_account_type => 'REC_CLEARING'
2117 ,x_cc_id_tbl => l_cc_id_tbl
2118 );
2119 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2120 RAISE FND_API.G_EXC_ERROR;
2121 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2123 END IF;
2124
2125 l_line_cc_id_flag := 'Y';
2126 ELSE
2127 l_line_cc_id_flag := 'N';
2128 END IF;
2129
2130 Interface_Claim_Line( p_claim_id,
2131 l_memo_line_id,
2132 l_claim_line_id,
2133 l_line_claim_curr_amt,
2134 l_line_tax_code,
2135 l_line_cc_id_flag,
2136 l_return_status);
2137
2138 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2139 RAISE FND_API.G_EXC_ERROR;
2140 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2142 END IF;
2143
2144 IF l_line_cc_id_flag = 'Y' THEN
2145 FOR i IN 1..(l_cc_id_tbl.COUNT) LOOP
2146 Distribute_Claim_Line(p_claim_id,
2147 l_claim_line_id,
2148 l_cc_id_tbl(i),
2149 l_return_status
2150 );
2151 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2152 RAISE FND_API.G_EXC_ERROR;
2153 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2155 END IF;
2156 END LOOP;
2157 END IF;
2158 END LOOP;
2159
2160 CLOSE claim_line_csr;
2161
2162 /* ------- End API Logic ------------------------------------- */
2163
2164 /* ------- Begin Update Claim Payment Status ----------------- */
2165 UPDATE ozf_claims_all
2166 SET payment_status = 'INTERFACED'
2167 WHERE claim_id = p_claim_id;
2168 /* ------- End Update Claim Payment Status ------------------- */
2169
2170 /* ------- Begin Standard API Calls --------------------------- */
2171
2172 --Standard check of commit
2173 IF FND_API.To_Boolean ( p_commit ) THEN
2174 COMMIT WORK;
2175 END IF;
2176 -- Debug Message
2177 IF OZF_DEBUG_LOW_ON THEN
2178 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2179 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
2180 FND_MSG_PUB.Add;
2181 END IF;
2182 --Standard call to get message count and if count=1, get the message
2183 FND_MSG_PUB.Count_And_Get (
2184 p_encoded => FND_API.G_FALSE,
2185 p_count => x_msg_count,
2186 p_data => x_msg_data
2187 );
2188
2189 /* ------- End Standard API Calls ----------------------------- */
2190 EXCEPTION
2191 WHEN FND_API.G_EXC_ERROR THEN
2192 IF (claim_line_csr%ISOPEN) THEN
2193 CLOSE claim_line_csr;
2194 END IF;
2195 ROLLBACK TO Interface_Claim_PVT;
2196 x_return_status := FND_API.G_RET_STS_ERROR;
2197 -- Standard call to get message count and if count=1, get the message
2198 FND_MSG_PUB.Count_And_Get (
2199 p_encoded => FND_API.G_FALSE,
2200 p_count => x_msg_count,
2201 p_data => x_msg_data
2202 );
2203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2204 IF (claim_line_csr%ISOPEN) THEN
2205 CLOSE claim_line_csr;
2206 END IF;
2207 ROLLBACK TO Interface_Claim_PVT;
2208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2209 -- Standard call to get message count and if count=1, get the message
2210 FND_MSG_PUB.Count_And_Get (
2211 p_encoded => FND_API.G_FALSE,
2212 p_count => x_msg_count,
2213 p_data => x_msg_data
2214 );
2215 WHEN OTHERS THEN
2216 IF (claim_line_csr%ISOPEN) THEN
2217 CLOSE claim_line_csr;
2218 END IF;
2219 ROLLBACK TO Interface_Claim_PVT;
2220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2221 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2222 THEN
2223 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2224 END IF;
2225 -- Standard call to get message count and if count=1, get the message
2226 FND_MSG_PUB.Count_And_Get (
2227 p_encoded => FND_API.G_FALSE,
2228 p_count => x_msg_count,
2229 p_data => x_msg_data
2230 );
2231
2232 END Interface_Claim ;
2233
2234 END OZF_Ar_Interface_PVT ;