[Home] [Help]
PACKAGE BODY: APPS.OZF_AR_INTERFACE_PVT
Source
1 PACKAGE BODY OZF_Ar_Interface_PVT AS
2 /*$Header: ozfvarib.pls 120.12 2007/12/13 07:55:03 kpatro 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,
463 p_int_distributions_rec.segment25,
460 p_int_distributions_rec.segment22,
461 p_int_distributions_rec.segment23,
462 p_int_distributions_rec.segment24,
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
593 , DOCUMENT_NUMBER
590 , CREDIT_METHOD_FOR_ACCT_RULE
591 , CREDIT_METHOD_FOR_INSTALLMENTS
592 , CUSTOMER_BANK_ACCOUNT_ID
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 )
638 VALUES (
639 p_interface_line_rec.created_by
640 , p_interface_line_rec.creation_date
641 , p_interface_line_rec.last_updated_by
642 , p_interface_line_rec.last_update_date
643 , p_interface_line_rec.interface_line_attribute1
644 , p_interface_line_rec.interface_line_attribute2
645 , p_interface_line_rec.interface_line_attribute3
646 , p_interface_line_rec.interface_line_attribute4
647 , p_interface_line_rec.interface_line_attribute5
648 , p_interface_line_rec.interface_line_attribute6
649 , p_interface_line_rec.interface_line_attribute7
650 , p_interface_line_rec.interface_line_attribute8
651 , p_interface_line_rec.interface_line_attribute9
652 , p_interface_line_rec.interface_line_attribute10
653 , p_interface_line_rec.interface_line_attribute11
654 , p_interface_line_rec.interface_line_attribute12
655 , p_interface_line_rec.interface_line_attribute13
656 , p_interface_line_rec.interface_line_attribute14
657 , p_interface_line_rec.interface_line_attribute15
658 , p_interface_line_rec.interface_line_context
659 , p_interface_line_rec.batch_source_name
660 , p_interface_line_rec.gl_date
661 , p_interface_line_rec.set_of_books_id
662 , p_interface_line_rec.line_type
663 , p_interface_line_rec.description
664 , p_interface_line_rec.currency_code
665 , p_interface_line_rec.amount
666 , p_interface_line_rec.conversion_type
667 , p_interface_line_rec.conversion_date
668 , p_interface_line_rec.conversion_rate
669 , p_interface_line_rec.cust_trx_type_id
670 , p_interface_line_rec.term_id
671 , p_interface_line_rec.orig_system_bill_customer_id
672 , p_interface_line_rec.orig_system_bill_address_id
673 , p_interface_line_rec.orig_system_bill_contact_id
674 , p_interface_line_rec.orig_system_ship_customer_id
675 , p_interface_line_rec.orig_system_ship_address_id
676 , p_interface_line_rec.orig_system_ship_contact_id
677 , p_interface_line_rec.orig_system_sold_customer_id
678 , p_interface_line_rec.org_id
679 , p_interface_line_rec.agreement_id
680 , p_interface_line_rec.comments
681 , p_interface_line_rec.credit_method_for_acct_rule
682 , p_interface_line_rec.credit_method_for_installments
683 , p_interface_line_rec.customer_bank_account_id
684 , p_interface_line_rec.document_number
685 , p_interface_line_rec.document_number_sequence_id
686 , p_interface_line_rec.header_attribute_category
687 , p_interface_line_rec.header_attribute1
688 , p_interface_line_rec.header_attribute2
689 , p_interface_line_rec.header_attribute3
690 , p_interface_line_rec.header_attribute4
691 , p_interface_line_rec.header_attribute5
692 , p_interface_line_rec.header_attribute6
693 , p_interface_line_rec.header_attribute7
694 , p_interface_line_rec.header_attribute8
695 , p_interface_line_rec.header_attribute9
696 , p_interface_line_rec.header_attribute10
697 , p_interface_line_rec.header_attribute11
698 , p_interface_line_rec.header_attribute12
699 , p_interface_line_rec.header_attribute13
700 , p_interface_line_rec.header_attribute14
701 , p_interface_line_rec.header_attribute15
702 , p_interface_line_rec.initial_customer_trx_id
703 , p_interface_line_rec.internal_notes
704 , p_interface_line_rec.invoicing_rule_id
705 , p_interface_line_rec.orig_system_batch_name
706 , p_interface_line_rec.previous_customer_trx_id
707 , p_interface_line_rec.primary_salesrep_id
708 , p_interface_line_rec.printing_option
709 , p_interface_line_rec.purchase_order
710 , p_interface_line_rec.purchase_order_revision
711 , p_interface_line_rec.purchase_order_date
712 , p_interface_line_rec.reason_code
713 , p_interface_line_rec.receipt_method_id
717 , p_interface_line_rec.trx_number
714 , p_interface_line_rec.related_customer_trx_id
715 , p_interface_line_rec.territory_id
716 , p_interface_line_rec.trx_date
718 , p_interface_line_rec.memo_line_id
719 , p_interface_line_rec.tax_code
720 , p_interface_line_rec.inventory_item_id
721 , p_interface_line_rec.quantity
722 , p_interface_line_rec.uom_code
723 , p_interface_line_rec.unit_selling_price
724 , p_interface_line_rec.legal_entity_id
725 , 682
726 ,'OZF_CLAIMS'
727 , 'TRADE_MGT_RECEIVABLES'
728 );
729
730 x_return_status := FND_API.g_ret_sts_success;
731 EXCEPTION
732 WHEN OTHERS THEN
733 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
734 FND_MESSAGE.set_name('OZF', 'OZF_AR_INTERFACE_ERROR');
735 FND_MSG_PUB.add;
736 END IF;
737 IF OZF_DEBUG_LOW_ON THEN
738 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
739 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
740 FND_MSG_PUB.Add;
741 END IF;
742 x_return_status := FND_API.g_ret_sts_unexp_error;
743 END Insert_Interface_Line ;
744
745
746 PROCEDURE Insert_Interface_Tax
747 ( p_interface_tax_rec IN RA_Interface_Lines_Rec_Type
748 ,x_return_status OUT NOCOPY VARCHAR2
749 ) IS
750 BEGIN
751
752 INSERT INTO RA_INTERFACE_LINES_ALL
753 ( CREATED_BY
754 , CREATION_DATE
755 , LAST_UPDATED_BY
756 , LAST_UPDATE_DATE
757 , INTERFACE_LINE_ATTRIBUTE1
758 , INTERFACE_LINE_ATTRIBUTE2
759 , INTERFACE_LINE_ATTRIBUTE3
760 , INTERFACE_LINE_ATTRIBUTE4
761 , INTERFACE_LINE_ATTRIBUTE5
762 , INTERFACE_LINE_ATTRIBUTE6
763 , INTERFACE_LINE_ATTRIBUTE7
764 , INTERFACE_LINE_ATTRIBUTE8
765 , INTERFACE_LINE_ATTRIBUTE9
766 , INTERFACE_LINE_ATTRIBUTE10
767 , INTERFACE_LINE_ATTRIBUTE11
768 , INTERFACE_LINE_ATTRIBUTE12
769 , INTERFACE_LINE_ATTRIBUTE13
770 , INTERFACE_LINE_ATTRIBUTE14
771 , INTERFACE_LINE_ATTRIBUTE15
772 , INTERFACE_LINE_CONTEXT
773 , LINK_TO_LINE_ATTRIBUTE1
774 , LINK_TO_LINE_ATTRIBUTE2
775 , LINK_TO_LINE_ATTRIBUTE3
776 , LINK_TO_LINE_ATTRIBUTE4
777 , LINK_TO_LINE_ATTRIBUTE5
778 , LINK_TO_LINE_ATTRIBUTE6
779 , LINK_TO_LINE_ATTRIBUTE7
780 , LINK_TO_LINE_ATTRIBUTE8
781 , LINK_TO_LINE_ATTRIBUTE9
782 , LINK_TO_LINE_ATTRIBUTE10
783 , LINK_TO_LINE_ATTRIBUTE11
784 , LINK_TO_LINE_ATTRIBUTE12
785 , LINK_TO_LINE_ATTRIBUTE13
786 , LINK_TO_LINE_ATTRIBUTE14
787 , LINK_TO_LINE_ATTRIBUTE15
788 , LINK_TO_LINE_CONTEXT
789 , LINE_TYPE
790 , TAX_CODE
791 , TAX_RATE
792 , DESCRIPTION
793 , BATCH_SOURCE_NAME
794 , SET_OF_BOOKS_ID
795 , CUST_TRX_TYPE_ID
796 , GL_DATE
797 , CURRENCY_CODE
798 , CONVERSION_TYPE
799 , CONVERSION_RATE
800 , ORG_ID
801 ,LEGAL_ENTITY_ID
802 )
803 VALUES (
804 p_interface_tax_rec.created_by
805 , p_interface_tax_rec.creation_date
806 , p_interface_tax_rec.last_updated_by
807 , p_interface_tax_rec.last_update_date
808 , p_interface_tax_rec.interface_line_attribute1
809 , p_interface_tax_rec.interface_line_attribute2
810 , p_interface_tax_rec.interface_line_attribute3
811 , p_interface_tax_rec.interface_line_attribute4
812 , p_interface_tax_rec.interface_line_attribute5
813 , p_interface_tax_rec.interface_line_attribute6
814 , p_interface_tax_rec.interface_line_attribute7
815 , p_interface_tax_rec.interface_line_attribute8
816 , p_interface_tax_rec.interface_line_attribute9
817 , p_interface_tax_rec.interface_line_attribute10
818 , p_interface_tax_rec.interface_line_attribute11
819 , p_interface_tax_rec.interface_line_attribute12
820 , p_interface_tax_rec.interface_line_attribute13
821 , p_interface_tax_rec.interface_line_attribute14
822 , p_interface_tax_rec.interface_line_attribute15
823 , p_interface_tax_rec.interface_line_context
824 , p_interface_tax_rec.link_to_line_attribute1
825 , p_interface_tax_rec.link_to_line_attribute2
826 , p_interface_tax_rec.link_to_line_attribute3
827 , p_interface_tax_rec.link_to_line_attribute4
828 , p_interface_tax_rec.link_to_line_attribute5
829 , p_interface_tax_rec.link_to_line_attribute6
830 , p_interface_tax_rec.link_to_line_attribute7
831 , p_interface_tax_rec.link_to_line_attribute8
832 , p_interface_tax_rec.link_to_line_attribute9
833 , p_interface_tax_rec.link_to_line_attribute10
834 , p_interface_tax_rec.link_to_line_attribute11
835 , p_interface_tax_rec.link_to_line_attribute12
836 , p_interface_tax_rec.link_to_line_attribute13
837 , p_interface_tax_rec.link_to_line_attribute14
838 , p_interface_tax_rec.link_to_line_attribute15
839 , p_interface_tax_rec.link_to_line_context
840 , p_interface_tax_rec.line_type
841 , p_interface_tax_rec.tax_code
842 , p_interface_tax_rec.tax_rate
843 , p_interface_tax_rec.description
844 , p_interface_tax_rec.batch_source_name
848 , p_interface_tax_rec.currency_code
845 , p_interface_tax_rec.set_of_books_id
846 , p_interface_tax_rec.cust_trx_type_id
847 , p_interface_tax_rec.gl_date
849 , p_interface_tax_rec.conversion_type
850 , p_interface_tax_rec.conversion_rate
851 , p_interface_tax_rec.org_id
852 , p_interface_tax_rec.legal_entity_id
853 );
854
855 x_return_status := FND_API.g_ret_sts_success;
856 EXCEPTION
857 WHEN OTHERS THEN
858 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
859 FND_MESSAGE.set_name('OZF', 'OZF_AR_INTERFACE_ERROR');
860 FND_MSG_PUB.add;
861 END IF;
862 IF OZF_DEBUG_LOW_ON THEN
863 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
864 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
865 FND_MSG_PUB.Add;
866 END IF;
867 x_return_status := FND_API.g_ret_sts_unexp_error;
868 END Insert_Interface_Tax;
869
870
871 PROCEDURE Insert_Interface_Sales_Credits(
872 p_int_sales_credits_rec IN RA_Int_Sales_Credits_Rec_Type
873 ,x_return_status OUT NOCOPY VARCHAR2
874 )
875 IS
876 BEGIN
877 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL(
878 CREATED_BY ,
879 CREATION_DATE ,
880 LAST_UPDATED_BY ,
881 LAST_UPDATE_DATE ,
882 LAST_UPDATE_LOGIN ,
883 SALESREP_ID ,
884 SALES_CREDIT_TYPE_ID ,
885 SALES_CREDIT_PERCENT_SPLIT ,
886 ATTRIBUTE_CATEGORY ,
887 ATTRIBUTE1 ,
888 ATTRIBUTE2 ,
889 ATTRIBUTE3 ,
890 ATTRIBUTE4 ,
891 ATTRIBUTE5 ,
892 ATTRIBUTE6 ,
893 ATTRIBUTE7 ,
894 ATTRIBUTE8 ,
895 ATTRIBUTE9 ,
896 ATTRIBUTE10 ,
897 ATTRIBUTE11 ,
898 ATTRIBUTE12 ,
899 ATTRIBUTE13 ,
900 ATTRIBUTE14 ,
901 ATTRIBUTE15 ,
902 INTERFACE_LINE_CONTEXT ,
903 INTERFACE_LINE_ATTRIBUTE1 ,
904 INTERFACE_LINE_ATTRIBUTE2 ,
905 INTERFACE_LINE_ATTRIBUTE3 ,
906 INTERFACE_LINE_ATTRIBUTE4 ,
907 INTERFACE_LINE_ATTRIBUTE5 ,
908 INTERFACE_LINE_ATTRIBUTE6 ,
909 INTERFACE_LINE_ATTRIBUTE7 ,
910 INTERFACE_LINE_ATTRIBUTE8 ,
911 INTERFACE_LINE_ATTRIBUTE9 ,
912 INTERFACE_LINE_ATTRIBUTE10 ,
913 INTERFACE_LINE_ATTRIBUTE11 ,
914 INTERFACE_LINE_ATTRIBUTE12 ,
915 INTERFACE_LINE_ATTRIBUTE13 ,
916 INTERFACE_LINE_ATTRIBUTE14 ,
917 INTERFACE_LINE_ATTRIBUTE15 ,
918 ORG_ID
919 )
920 VALUES (
921 p_int_sales_credits_rec.CREATED_BY ,
922 p_int_sales_credits_rec.CREATION_DATE ,
923 p_int_sales_credits_rec.LAST_UPDATED_BY ,
924 p_int_sales_credits_rec.LAST_UPDATE_DATE ,
925 p_int_sales_credits_rec.LAST_UPDATE_LOGIN ,
926 p_int_sales_credits_rec.SALESREP_ID ,
927 p_int_sales_credits_rec.SALES_CREDIT_TYPE_ID ,
928 p_int_sales_credits_rec.SALES_CREDIT_PERCENT_SPLIT ,
929 p_int_sales_credits_rec.ATTRIBUTE_CATEGORY ,
930 p_int_sales_credits_rec.ATTRIBUTE1 ,
931 p_int_sales_credits_rec.ATTRIBUTE2 ,
932 p_int_sales_credits_rec.ATTRIBUTE3 ,
933 p_int_sales_credits_rec.ATTRIBUTE4 ,
934 p_int_sales_credits_rec.ATTRIBUTE5 ,
935 p_int_sales_credits_rec.ATTRIBUTE6 ,
936 p_int_sales_credits_rec.ATTRIBUTE7 ,
937 p_int_sales_credits_rec.ATTRIBUTE8 ,
938 p_int_sales_credits_rec.ATTRIBUTE9 ,
939 p_int_sales_credits_rec.ATTRIBUTE10 ,
940 p_int_sales_credits_rec.ATTRIBUTE11 ,
941 p_int_sales_credits_rec.ATTRIBUTE12 ,
942 p_int_sales_credits_rec.ATTRIBUTE13 ,
943 p_int_sales_credits_rec.ATTRIBUTE14 ,
944 p_int_sales_credits_rec.ATTRIBUTE15 ,
945 p_int_sales_credits_rec.INTERFACE_LINE_CONTEXT ,
946 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE1 ,
947 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE2 ,
948 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE3 ,
949 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE4 ,
950 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE5 ,
951 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE6 ,
952 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE7 ,
953 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE8 ,
954 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE9 ,
955 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE10 ,
956 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE11 ,
960 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE15 ,
957 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE12 ,
958 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE13 ,
959 p_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE14 ,
961 p_int_sales_credits_rec.ORG_ID
962 );
963
964 x_return_status := FND_API.g_ret_sts_success;
965 EXCEPTION
966 WHEN OTHERS THEN
967 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
968 FND_MESSAGE.set_name('OZF', 'OZF_AR_INTERFACE_ERROR');
969 FND_MSG_PUB.add;
970 END IF;
971 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
972 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
973 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
974 FND_MSG_PUB.Add;
975 END IF;
976 x_return_status := FND_API.g_ret_sts_unexp_error;
977 END Insert_Interface_Sales_Credits;
978
979
980 /* ---------------------------------------------- *
981 * Function that returns the memo_line_id
982 * for a given claim_line_id.
983 * ---------------------------------------------- */
984
985 FUNCTION Get_Memo_Line_Id(p_claim_line_id IN NUMBER)
986 RETURN NUMBER IS
987
988 CURSOR csr_memo_line(cv_claim_line_id IN NUMBER) IS
989 SELECT item_id
990 FROM ozf_claim_lines
991 WHERE claim_line_id = cv_claim_line_id
992 AND item_type = 'MEMO_LINE';--11.5.10 Enhancements.
993 l_memo_line_id NUMBER;
994
995 BEGIN
996 OPEN csr_memo_line (p_claim_line_id);
997 FETCH csr_memo_line INTO l_memo_line_id;
998 CLOSE csr_memo_line;
999
1000 IF (l_memo_line_id IS NULL) THEN
1001 -- Make sure that if no memo line is found, then
1002 -- always return 0..
1003 --l_memo_line_id := p_claim_id;
1004 l_memo_line_id := 0;
1005 END IF;
1006
1007 RETURN l_memo_line_id;
1008
1009 END Get_Memo_Line_Id;
1010
1011 /* ---------------------------------------------- *
1012 * Populate the Interface Line Record
1013 * ---------------------------------------------- */
1014 PROCEDURE Populate_Interface_Line_Rec
1015 ( p_claim_rec IN Claim_Rec_Type
1016 ,p_memo_line_id IN NUMBER
1017 ,p_claim_line_id IN NUMBER
1018 ,p_line_claim_curr_amt IN NUMBER
1019 ,p_line_tax_code IN VARCHAR2
1020 ,p_line_cc_id_flag IN VARCHAR2
1021 ,p_x_interface_line_rec IN OUT NOCOPY RA_Interface_Lines_Rec_Type
1022 ,x_return_status OUT NOCOPY VARCHAR2
1023 ) IS
1024
1025 CURSOR sys_param_csr IS
1026 SELECT sp.batch_source_id,
1027 sp.billback_trx_type_id,
1028 sp.cm_trx_type_id
1029 FROM ozf_sys_parameters sp ;
1030
1031 CURSOR batch_source_csr (p_id IN NUMBER) IS
1032 SELECT name
1033 FROM ra_batch_sources
1034 WHERE batch_source_id = p_id ;
1035 /*
1036 CURSOR claim_type_csr(p_id IN NUMBER) IS
1037 SELECT NVL(transaction_type,0)
1038 FROM ozf_claim_types
1039 WHERE claim_type_id = p_id ;
1040 */
1041 CURSOR claim_type_csr(cv_claim_type_id IN NUMBER) IS
1042 SELECT cm_trx_type_id
1043 , dm_trx_type_id
1044 , cb_trx_type_id
1045 FROM ozf_claim_types_all_b
1046 WHERE claim_type_id = cv_claim_type_id;
1047
1048 CURSOR memo_line_csr(p_id IN NUMBER) IS
1049 SELECT description
1050 FROM ar_memo_lines
1051 WHERE memo_line_id = p_id;
1052
1053 CURSOR party_site_csr(p_id IN NUMBER) IS
1054 SELECT cust_acct_site_id
1055 FROM hz_cust_site_uses
1056 WHERE site_use_id = p_id ;
1057
1058 CURSOR csr_shipto_site(p_shipto_site_id IN NUMBER) IS
1059 SELECT cas.cust_account_id
1060 , cas.cust_acct_site_id
1061 FROM hz_cust_acct_sites cas
1062 , hz_cust_site_uses csu
1063 WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
1064 AND csu.site_use_id = p_shipto_site_id;
1065
1066
1067 CURSOR reason_code_csr(p_id IN NUMBER) IS
1068 SELECT reason_code
1069 FROM ozf_reason_codes_all_b
1070 WHERE reason_code_id = p_id;
1071
1072 CURSOR invoice_reason_code_csr(p_id IN NUMBER) IS
1073 SELECT invoicing_reason_code
1074 FROM ozf_reason_codes_all_b
1075 WHERE reason_code_id = p_id;
1076
1077 CURSOR trx_type_gl_flag_csr(p_trx_type_id IN NUMBER) IS
1078 SELECT post_to_gl
1079 FROM ra_cust_trx_types
1080 WHERE cust_trx_type_id = p_trx_type_id;
1081
1082 CURSOR csr_trx_type_payment_term(cv_claim_type_id IN NUMBER) IS
1083 SELECT default_term
1084 FROM ra_cust_trx_types trx
1085 , ozf_claim_types_all_b ct
1086 WHERE trx.cust_trx_type_id = ct.dm_trx_type_id
1087 AND ct.claim_type_id = cv_claim_type_id;
1088
1089 CURSOR csr_cust_payment_term(cv_cust_account_id IN NUMBER) IS
1090 SELECT standard_terms
1091 FROM hz_customer_profiles
1092 WHERE cust_account_id = cv_cust_account_id
1093 AND site_use_id IS NULL;
1094 /*
1095 SELECT payment_term_id
1096 FROM hz_cust_accounts
1097 WHERE cust_account_id = cv_cust_account_id;
1098 */
1099
1100 CURSOR csr_cust_address_pay_term(cv_cust_account_id IN NUMBER, cv_site_use_id IN NUMBER) IS
1101 SELECT standard_terms
1102 FROM hz_customer_profiles
1103 WHERE cust_account_id = cv_cust_account_id
1104 AND site_use_id = cv_site_use_id;
1105
1109 , hz_cust_acct_sites site
1106 CURSOR csr_cust_site_pay_term(cv_cust_account_id IN NUMBER, cv_site_use_id IN NUMBER) IS
1107 SELECT use.payment_term_id
1108 FROM hz_cust_site_uses use
1110 WHERE site.cust_acct_site_id = use.cust_acct_site_id
1111 AND site.cust_account_id = cv_cust_account_id
1112 AND use.site_use_id = cv_site_use_id;
1113
1114 CURSOR csr_claim_line_product(cv_claim_line_id IN NUMBER) IS
1115 SELECT item_id
1116 , quantity
1117 , quantity_uom
1118 , rate
1119 , item_type
1120 , item_description
1121 , source_object_class -- added for bug 4716020
1122 FROM ozf_claim_lines
1123 WHERE claim_line_id = cv_claim_line_id;
1124
1125 l_batch_source_id NUMBER;
1126 l_billback_trx_type_id NUMBER;
1127 l_cm_trx_type_id NUMBER;
1128 l_post_gl_flag VARCHAR2(1);
1129 l_claim_cm_trx_type_id NUMBER;
1130 l_claim_dm_trx_type_id NUMBER;
1131 l_claim_cb_trx_type_id NUMBER;
1132 l_reason_code VARCHAR2(30);
1133 l_invoicing_reason_code VARCHAR2(30);
1134 l_inventory_item_id NUMBER; --11.5.10 Enhancements.
1135 l_item_type VARCHAR2(30); --11.5.10 Enhancements.
1136 l_claim_line_item_desc VARCHAR2(240);
1137 l_source_object_class VARCHAR2(30); -- added for bug 4716020
1138
1139 -- Cursor to get claim reason name -- 11.5.10 Enhancements.
1140 CURSOR csr_get_reason_name (cv_reason_code_id IN NUMBER) IS
1141 SELECT SUBSTRB(name,1,30) name
1142 FROM ozf_reason_codes_vl
1143 WHERE reason_code_id = cv_reason_code_id;
1144
1145 CURSOR csr_product_desc(cv_inventory_item_id IN NUMBER) IS
1146 SELECT SUBSTRB(description, 1, 240)
1147 FROM mtl_system_items_vl
1148 WHERE inventory_item_id = cv_inventory_item_id
1149 AND organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID');
1150
1151 CURSOR csr_offer_name(cv_claim_line_id IN NUMBER)IS
1152 SELECT SUBSTR(offer_code,1,50)
1153 FROM ozf_offers offer, ozf_claim_lines_all line
1154 WHERE qp_list_header_id = line.offer_id
1155 AND claim_line_id = cv_claim_line_id;
1156
1157 -- Introduced for Bug4348163
1158 CURSOR csr_category_desc(cv_category_id IN NUMBER) IS
1159 SELECT SUBSTRB(category_desc,1,240)
1160 FROM eni_prod_den_hrchy_parents_v
1161 WHERE category_id = cv_category_id;
1162 l_line_description VARCHAR2(240);
1163
1164 CURSOR csr_media_desc(cv_media_channel_id IN NUMBER) IS
1165 SELECT channel_name
1166 FROM ams_media_channels_vl
1167 WHERE channel_id = cv_media_channel_id;
1168
1169 BEGIN
1170 -- Start populating the interface record values
1171 x_return_status := FND_API.g_ret_sts_success;
1172
1173 /* -- Standard Who Columns ------------------------- */
1174 p_x_interface_line_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1175 p_x_interface_line_rec.CREATION_DATE := SYSDATE ;
1176 p_x_interface_line_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1177 p_x_interface_line_rec.LAST_UPDATE_DATE := SYSDATE;
1178
1179 /* -- Fixed Values from unchangeble profiles ------- */
1180 p_x_interface_line_rec.INTERFACE_LINE_CONTEXT := 'CLAIM';
1181
1182 /* -- Batch Source --------------------------------- */
1183 -- This is set at the system parameter level
1184 -- System parameter will have only one record for an org
1185 -- No need to handle when no data found.
1186 OPEN sys_param_csr;
1187 FETCH sys_param_csr INTO l_batch_source_id,
1188 l_billback_trx_type_id,
1189 l_cm_trx_type_id ;
1190 CLOSE sys_param_csr;
1191
1192 IF (l_batch_source_id IS NULL) THEN
1193 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1194 FND_MESSAGE.set_name('OZF','OZF_BATCH_SRC_REQ_FOR_INTF');
1195 FND_MSG_PUB.add;
1196 END IF;
1197 x_return_status := FND_API.g_ret_sts_error;
1198 ELSE
1199 OPEN batch_source_csr(l_batch_source_id) ;
1200 FETCH batch_source_csr INTO p_x_interface_line_rec.batch_source_name;
1201 CLOSE batch_source_csr;
1202 END IF;
1203
1204 /* -- All Interface line attributes enabled for the context CLAIM --- */
1205 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1 := p_claim_rec.claim_number ;
1206 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2 := TO_CHAR(p_claim_rec.claim_id) ;
1207 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3 := TO_CHAR(p_claim_line_id);
1208 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4 := p_line_cc_id_flag;
1209
1210 /* -- 11.5.10 Enhancements. TM should pass claim comments. */
1211 p_x_interface_line_rec.comments := SUBSTRB(p_claim_rec.comments, 1, 240);
1212
1213 /* -- 11.5.10 Enhancements. TM should pass. */
1214 OPEN csr_get_reason_name(p_claim_rec.reason_code_id);
1215 FETCH csr_get_reason_name INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7; --reason name
1216 CLOSE csr_get_reason_name;
1217 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5 := NVL(p_claim_rec.customer_ref_number, '-');
1218 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6 := NVL(p_claim_rec.customer_reason, '-') ;
1219
1220 /* -- Only four attributes used for now
1221 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5 := '0';
1222 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6 := '0';
1223 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7 := '0';
1224 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE8 := '0';
1225 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE9 := '0';
1229 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE13 := '0';
1226 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE10 := '0';
1227 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE11 := '0';
1228 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE12 := '0';
1230 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE14 := '0';
1231 p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE15 := '0';
1232 */
1233
1234 -- Bugfix 6115698: FP:11510-R12 5663890
1235 /* ------------------------------------------------- */
1236 -- Populate this mandatory attribute with the unique claim_id to group all
1237 -- lines from the claim into one Transaction in AR
1238 -- p_x_interface_line_rec.HEADER_ATTRIBUTE1 := TO_CHAR(p_claim_rec.claim_id);
1239
1240
1241 /* -- Claim Header Record Values ------------------- */
1242 p_x_interface_line_rec.SET_OF_BOOKS_ID := p_claim_rec.set_of_books_id;
1243 p_x_interface_line_rec.LINE_TYPE := 'LINE';
1244 p_x_interface_line_rec.CURRENCY_CODE := p_claim_rec.currency_code;
1245 IF p_claim_rec.exchange_rate_type IS NULL THEN
1246 p_x_interface_line_rec.CONVERSION_TYPE := 'User';
1247 p_x_interface_line_rec.CONVERSION_RATE := 1;
1248 ELSE
1249 p_x_interface_line_rec.CONVERSION_TYPE := p_claim_rec.exchange_rate_type;
1250 IF p_claim_rec.exchange_rate_type = 'User' THEN
1251 p_x_interface_line_rec.CONVERSION_RATE := p_claim_rec.exchange_rate;
1252 ELSE
1253 p_x_interface_line_rec.CONVERSION_RATE := NULL;
1254 END IF;
1255 END IF;
1256 p_x_interface_line_rec.CONVERSION_DATE := p_claim_rec.exchange_rate_date;
1257 -- 13-MAR-2002 mchang updated: assing value of conversion_rate is depending on conversion_rate
1258 --p_x_interface_line_rec.CONVERSION_RATE := p_claim_rec.exchange_rate;
1259 p_x_interface_line_rec.PRIMARY_SALESREP_ID := p_claim_rec.sales_rep_id;
1260
1261 p_x_interface_line_rec.ORG_ID := p_claim_rec.org_id;
1262 p_x_interface_line_rec.legal_entity_id := p_claim_rec.legal_entity_id;
1263
1264
1265 /* -- Populate Customer and Bill To site ------------------ */
1266 -- Figure out which customer/site to bill
1267 -- If pay_related_account_flag is T
1268 -- then
1269 -- customer_id will be related_cust_account_id and
1270 -- bill_to should be derived from related_site_use_id
1271 -- else
1272 -- customer_id will be cust_account_id and
1273 -- bill_to should be derived from cust_billto_acct_site_id
1274 -- ship_to should be derived from cust_shipto_acct_site_id
1275 --
1276 IF (p_claim_rec.pay_related_account_flag = 'T' ) THEN
1277 p_x_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID :=
1278 p_claim_rec.related_cust_account_id;
1279
1280 -- Get the bill_address_id
1281 OPEN party_site_csr(p_claim_rec.related_site_use_id);
1282 FETCH party_site_csr INTO
1283 p_x_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_ID ;
1284 CLOSE party_site_csr;
1285
1286
1287 -- Get the ship_to_address_id and ship to customer
1288 OPEN csr_shipto_site(p_claim_rec.cust_shipto_acct_site_id);
1289 FETCH csr_shipto_site into p_x_interface_line_rec.orig_system_ship_customer_id
1290 , p_x_interface_line_rec.orig_system_ship_address_id;
1291 CLOSE csr_shipto_site;
1292 p_x_interface_line_rec.orig_system_ship_customer_id := p_claim_rec.ship_to_cust_account_id;
1293 ELSE
1294 p_x_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID :=
1295 p_claim_rec.cust_account_id;
1296
1297 -- Get the bill_address_id
1298 OPEN party_site_csr(p_claim_rec.cust_billto_acct_site_id);
1299 FETCH party_site_csr INTO
1300 p_x_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_ID ;
1301 CLOSE party_site_csr;
1302
1303 -- Get the ship_to_address_id and ship to customer
1304 OPEN csr_shipto_site(p_claim_rec.cust_shipto_acct_site_id);
1305 FETCH csr_shipto_site into p_x_interface_line_rec.orig_system_ship_customer_id
1306 , p_x_interface_line_rec.orig_system_ship_address_id;
1307 CLOSE csr_shipto_site;
1308 END IF;
1309
1310
1311
1312 /* -- Get Reason Code ----------------------------------- */
1313 -- This is optional
1314 -- Derive it from ozf_reason_codes_all_b
1315 -- reason_code should be passed only for credit memos.
1316 -- It should not be passed for debit memos.
1317 IF p_claim_rec.payment_method = 'CREDIT_MEMO' AND
1318 p_claim_rec.reason_code_id IS NOT NULL THEN
1319 OPEN reason_code_csr(p_claim_rec.reason_code_id);
1320 FETCH reason_code_csr INTO l_reason_code;
1321 CLOSE reason_code_csr;
1322
1323 IF l_reason_code IS NOT NULL THEN
1324 p_x_interface_line_rec.REASON_CODE := l_reason_code;
1325 END IF;
1326 END IF;
1327
1328 /* -- Get Invoicing Reason Code ----------------------------------- */
1329 -- R12 Enhancements
1330 -- Invoicing Reason code should be passed only for debit memos.
1331
1332 IF p_claim_rec.payment_method = 'DEBIT_MEMO' AND
1333 p_claim_rec.reason_code_id IS NOT NULL THEN
1334 OPEN invoice_reason_code_csr(p_claim_rec.reason_code_id);
1335 FETCH invoice_reason_code_csr INTO l_invoicing_reason_code;
1336 CLOSE invoice_reason_code_csr;
1337
1338 IF l_invoicing_reason_code IS NOT NULL THEN
1342
1339 p_x_interface_line_rec.REASON_CODE := l_invoicing_reason_code;
1340 END IF;
1341 END IF;
1343
1344 /* -- Derive and Validate the transaction type ---------- */
1345 -- Check for Payment Method First
1346 IF ( p_claim_rec.payment_method IS NULL OR
1347 p_claim_rec.payment_method = FND_API.G_MISS_CHAR )
1348 THEN
1349 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1350 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_PAYMENT_METHOD');
1351 FND_MSG_PUB.add;
1352 END IF;
1353 ELSIF p_claim_rec.payment_method IS NOT NULL THEN
1354 -- Now get Trx_type from the claim type
1355 OPEN claim_type_csr(p_claim_rec.claim_type_id) ;
1356 --FETCH claim_type_csr INTO p_x_interface_line_rec.cust_trx_type_id ;
1357 FETCH claim_type_csr INTO l_claim_cm_trx_type_id
1358 , l_claim_dm_trx_type_id
1359 , l_claim_cb_trx_type_id;
1360 CLOSE claim_type_csr;
1361
1362 --Bug4249629: Effective date should be used as trx_date
1363 p_x_interface_line_rec.trx_date := p_claim_rec.effective_date;
1364 IF p_claim_rec.payment_method = 'CREDIT_MEMO' THEN
1365 p_x_interface_line_rec.cust_trx_type_id := l_claim_cm_trx_type_id;
1366
1367 -- amount sign is changed for on account credit memos
1368 p_x_interface_line_rec.AMOUNT := p_line_claim_curr_amt * -1;
1369
1370 ELSIF p_claim_rec.payment_method = 'DEBIT_MEMO' THEN
1371 p_x_interface_line_rec.cust_trx_type_id := l_claim_dm_trx_type_id;
1372
1373 -- For Regular and on-acc CM .. do not enter
1374 -- Others, its optional. ( In our case it is a DM )
1375 -- Receivable uses the following hierarchy to determine the default payment term,
1376 -- stopping when one is found
1377 -- 1. customer bill-to site level
1378 -- 2. customer address level
1379 -- 3. customer level
1380 -- 4. transaction type
1381
1382 OPEN csr_cust_site_pay_term(p_claim_rec.cust_account_id, p_claim_rec.cust_billto_acct_site_id);
1383 FETCH csr_cust_site_pay_term INTO p_x_interface_line_rec.TERM_ID;
1384 CLOSE csr_cust_site_pay_term;
1385
1386 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1387 OPEN csr_cust_address_pay_term(p_claim_rec.cust_account_id, p_claim_rec.cust_billto_acct_site_id);
1388 FETCH csr_cust_address_pay_term INTO p_x_interface_line_rec.TERM_ID;
1389 CLOSE csr_cust_address_pay_term;
1390 END IF;
1391
1392 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1393 OPEN csr_cust_payment_term(p_claim_rec.cust_account_id);
1394 FETCH csr_cust_payment_term INTO p_x_interface_line_rec.TERM_ID;
1395 CLOSE csr_cust_payment_term;
1396 END IF;
1397
1398 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1399 OPEN csr_trx_type_payment_term(p_claim_rec.claim_type_id);
1400 FETCH csr_trx_type_payment_term INTO p_x_interface_line_rec.TERM_ID;
1401 CLOSE csr_trx_type_payment_term;
1402 END IF;
1403
1404 IF p_x_interface_line_rec.TERM_ID IS NULL THEN
1405 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1406 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_PAYMENT_TERM');
1407 FND_MSG_PUB.add;
1408 END IF;
1409 x_return_status := FND_API.g_ret_sts_error;
1410 END IF;
1411
1412 -- Overpayment amount is negative; Creation sign for debit memo should be positive.
1413 p_x_interface_line_rec.AMOUNT := p_line_claim_curr_amt * -1;
1414
1415 ELSIF p_claim_rec.payment_method = 'CHARGEBACK' THEN
1416 p_x_interface_line_rec.cust_trx_type_id := l_claim_cb_trx_type_id;
1417
1418 p_x_interface_line_rec.AMOUNT := p_line_claim_curr_amt;
1419
1420 ELSE
1421 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1422 FND_MESSAGE.set_name('OZF', 'OZF_SETL_INT_ERR_PAYMETHOD');
1423 FND_MSG_PUB.add;
1424 END IF;
1425 x_return_status := FND_API.g_ret_sts_error;
1426 END IF;
1427
1428 -- If trx_type is not defined at claim type level, get it from
1429 -- system parameters
1430 IF p_x_interface_line_rec.cust_trx_type_id IS NULL THEN
1431 -- Verify the payment_method against the trx_type
1432 -- l_billback_trx_type_id and l_cm_trx_type_id are fetched
1433 -- from the sys_param_csr above
1434 IF ( p_claim_rec.payment_method IN ('CHARGEBACK','DEBIT_MEMO') )
1435 THEN
1436 IF l_billback_trx_type_id IS NULL THEN
1437 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1438 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_BILLBACK_TRX_TYPE');
1439 FND_MSG_PUB.add;
1440 END IF;
1441 x_return_status := FND_API.g_ret_sts_error;
1442 ELSE
1443 p_x_interface_line_rec.cust_trx_type_id :=l_billback_trx_type_id;
1444 END IF;
1445 ELSIF p_claim_rec.payment_method = 'CREDIT_MEMO' THEN
1446 IF l_cm_trx_type_id IS NULL THEN
1447 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1448 FND_MESSAGE.set_name('OZF', 'OZF_MISSING_CM_TRX_TYPE');
1449 FND_MSG_PUB.add;
1450 END IF;
1451 x_return_status := FND_API.g_ret_sts_error;
1455 END IF;
1452 ELSE
1453 p_x_interface_line_rec.cust_trx_type_id := l_cm_trx_type_id;
1454 END IF;
1456 END IF; -- End getting trx_type from system parameters
1457 END IF; -- End Trx_Type validation
1458
1459
1460
1461 /* ------------ Populate GL_DATE ------------------------------ */
1462 -- If the Post To Gl option on the transaction type is set to NO,
1463 -- the GL_DATE column should be NULL.
1464 OPEN trx_type_gl_flag_csr(p_x_interface_line_rec.cust_trx_type_id);
1465 FETCH trx_type_gl_flag_csr INTO l_post_gl_flag;
1466 CLOSE trx_type_gl_flag_csr;
1467
1468 IF l_post_gl_flag = 'Y' THEN
1469 IF OZF_CLAIM_SETTLEMENT_VAL_PVT.gl_date_in_open(222, p_claim_rec.claim_id) THEN
1470 p_x_interface_line_rec.GL_DATE := p_claim_rec.gl_date;
1471 END IF;
1472 END IF;
1473
1474 -- 11.5.10 Enhancements. AR should default
1475 IF p_claim_rec.payment_method IS NOT NULL
1476 AND p_claim_rec.payment_method = 'DEBIT_MEMO' THEN
1477 p_x_interface_line_rec.GL_DATE := NULL;
1478 END IF;
1479
1480
1481 /* ------ Tax Code ----------------------------*/
1482 p_x_interface_line_rec.tax_code := p_line_tax_code;
1483
1484
1485
1486 /* ------ Bug4348163: Populate Product Information ----------------------------*/
1487 OPEN csr_claim_line_product(p_claim_line_id);
1488 FETCH csr_claim_line_product INTO l_inventory_item_id
1489 , p_x_interface_line_rec.quantity
1490 , p_x_interface_line_rec.uom_code
1491 , p_x_interface_line_rec.unit_selling_price
1492 , l_item_type
1493 , l_claim_line_item_desc
1494 , l_source_object_class; -- added for bug 4716020
1495 CLOSE csr_claim_line_product;
1496 IF l_inventory_item_id IS NOT NULL THEN
1497 -- fix for bug 4716020
1498 IF l_source_object_class in ('INVOICE', 'CM', 'DM', 'CB', 'ORDER') THEN
1499 OPEN csr_product_desc(l_inventory_item_id);
1500 FETCH csr_product_desc INTO l_line_description;
1501 CLOSE csr_product_desc;
1502 p_x_interface_line_rec.inventory_item_id := l_inventory_item_id;
1503 END IF;
1504 -- end of fix for bug 4716020
1505
1506 IF l_item_type = 'PRODUCT' THEN
1507 OPEN csr_product_desc(l_inventory_item_id);
1508 FETCH csr_product_desc INTO l_line_description;
1509 CLOSE csr_product_desc;
1510 p_x_interface_line_rec.inventory_item_id := l_inventory_item_id;
1511
1512 ELSIF l_item_type = 'FAMILY' THEN
1513 OPEN csr_category_desc(l_inventory_item_id);
1514 FETCH csr_category_desc INTO l_line_description;
1515 CLOSE csr_category_desc;
1516
1517 ELSIF l_item_type = 'MEMO_LINE' THEN
1518 OPEN memo_line_csr(l_inventory_item_id);
1519 FETCH memo_line_csr INTO l_line_description;
1520 CLOSE memo_line_csr;
1521 p_x_interface_line_rec.memo_line_id := l_inventory_item_id;
1522
1523 ELSIF l_item_type = 'MEDIA' THEN
1524 OPEN csr_media_desc(l_inventory_item_id);
1525 FETCH csr_media_desc INTO l_line_description;
1526 CLOSE csr_media_desc;
1527
1528 END IF;
1529
1530 END IF;
1531 IF l_line_description IS NULL THEN
1532 l_line_description := NVL(l_claim_line_item_desc, p_claim_rec.claim_number) ;
1533 END IF;
1534 p_x_interface_line_rec.description := SUBSTRB(l_line_description,1,240) ;
1535 p_x_interface_line_rec.quantity := p_x_interface_line_rec.quantity * -1;
1536
1537
1538
1539 /* ------ Bug4348163: Populate Offer Information -------------------------------*/
1540 /*OPEN csr_offer_name(p_claim_line_id);
1541 FETCH csr_offer_name INTO p_x_interface_line_rec.purchase_order;
1542 CLOSE csr_offer_name;*/
1543
1544
1545 -- bug4436227: The below statement is the culprit, as it causes datatype mismatch
1546 --p_x_interface_line_rec.comments := p_claim_rec.comments;
1547
1548 EXCEPTION
1549 WHEN OTHERS THEN
1550 IF sys_param_csr%ISOPEN THEN
1551 CLOSE sys_param_csr ;
1552 END IF;
1553 IF batch_source_csr%ISOPEN THEN
1554 CLOSE batch_source_csr ;
1555 END IF;
1556 IF claim_type_csr%ISOPEN THEN
1557 CLOSE claim_type_csr ;
1558 END IF;
1559 IF memo_line_csr%ISOPEN THEN
1560 CLOSE memo_line_csr ;
1561 END IF;
1562 IF party_site_csr%ISOPEN THEN
1563 CLOSE party_site_csr ;
1564 END IF;
1565 IF reason_code_csr%ISOPEN THEN
1566 CLOSE reason_code_csr ;
1567 END IF;
1568 IF trx_type_gl_flag_csr%ISOPEN THEN
1569 CLOSE trx_type_gl_flag_csr ;
1570 END IF;
1571 IF csr_trx_type_payment_term%ISOPEN THEN
1572 CLOSE csr_trx_type_payment_term ;
1573 END IF;
1574 IF csr_cust_payment_term%ISOPEN THEN
1575 CLOSE csr_cust_payment_term ;
1576 END IF;
1577 IF csr_cust_address_pay_term%ISOPEN THEN
1578 CLOSE csr_cust_address_pay_term ;
1579 END IF;
1580 IF csr_cust_site_pay_term%ISOPEN THEN
1581 CLOSE csr_cust_site_pay_term ;
1582 END IF;
1586
1583 IF csr_claim_line_product%ISOPEN THEN
1584 CLOSE csr_claim_line_product ;
1585 END IF;
1587 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1588 FND_MESSAGE.set_name('OZF', 'OZF_POPULATE_INTF_ERROR');
1589 FND_MSG_PUB.add;
1590 END IF;
1591 IF OZF_DEBUG_LOW_ON THEN
1592 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1593 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1594 FND_MSG_PUB.Add;
1595 END IF;
1596 x_return_status := FND_API.g_ret_sts_unexp_error;
1597 END Populate_Interface_Line_Rec;
1598
1599
1600 /* ---------------------------------------------- *
1601 * Populate the Interface Tax Record
1602 * ---------------------------------------------- */
1603 PROCEDURE Populate_Interface_Tax_Rec
1604 ( p_line_tax_code IN VARCHAR2
1605 ,p_interface_line_rec IN RA_Interface_Lines_Rec_Type
1606 ,x_interface_tax_rec OUT NOCOPY RA_Interface_Lines_Rec_Type
1607 ,x_return_status OUT NOCOPY VARCHAR2
1608 ) IS
1609
1610 l_tax_rate NUMBER;
1611 l_tax_name VARCHAR2(60);
1612
1613 BEGIN
1614 -- Start populating the interface record values
1615 x_return_status := FND_API.g_ret_sts_success;
1616
1617 /* -- Standard Who Columns ------------------------- */
1618 x_interface_tax_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1619 x_interface_tax_rec.CREATION_DATE := SYSDATE ;
1620 x_interface_tax_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1621 x_interface_tax_rec.LAST_UPDATE_DATE := SYSDATE;
1622 /* ------------------------------------------------- */
1623
1624 /* -- required fields for tax lines -- */
1625
1626 x_interface_tax_rec.LINE_TYPE := 'TAX';
1627 x_interface_tax_rec.TAX_CODE := p_line_tax_code;
1628 x_interface_tax_rec.TAX_RATE := l_tax_rate;
1629
1630 /* -- use "claim_number || printed_tax_name" as a description for tax line. -- */
1631 x_interface_tax_rec.DESCRIPTION := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1 || l_tax_name;
1632
1633 /* -- some other required fields -- */
1634 x_interface_tax_rec.BATCH_SOURCE_NAME := p_interface_line_rec.BATCH_SOURCE_NAME;
1635 x_interface_tax_rec.SET_OF_BOOKS_ID := p_interface_line_rec.SET_OF_BOOKS_ID;
1636 x_interface_tax_rec.CUST_TRX_TYPE_ID := p_interface_line_rec.CUST_TRX_TYPE_ID;
1637 x_interface_tax_rec.GL_DATE := p_interface_line_rec.GL_DATE;
1638 x_interface_tax_rec.CURRENCY_CODE := p_interface_line_rec.CURRENCY_CODE;
1639 x_interface_tax_rec.CONVERSION_TYPE := p_interface_line_rec.CONVERSION_TYPE;
1640 x_interface_tax_rec.CONVERSION_RATE := p_interface_line_rec.CONVERSION_RATE;
1641 x_interface_tax_rec.ORG_ID := p_interface_line_rec.ORG_ID;
1642 x_interface_tax_rec.LEGAL_ENTITY_ID := p_interface_line_rec.LEGAL_ENTITY_ID;
1643
1644 /* -- Fixed Values from unchangeble profiles ------- */
1645 x_interface_tax_rec.INTERFACE_LINE_CONTEXT := 'CLAIM';
1646
1647 /* -- All Interface line attributes enabled for the context CLAIM --- */
1648 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE1 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1;
1649 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE2 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2;
1650 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE3 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3 || 'TAX';
1651 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE4 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4;
1652 /* -- Only four attributes used for now
1653 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE5 := '0';
1654 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE6 := '0';
1655 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE7 := '0';
1656 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE8 := '0';
1657 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE9 := '0';
1658 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE10 := '0';
1659 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE11 := '0';
1660 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE12 := '0';
1661 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE13 := '0';
1662 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE14 := '0';
1663 x_interface_tax_rec.INTERFACE_LINE_ATTRIBUTE15 := '0';
1664 */
1665
1666 /* -- Use Link_To Transaction flexfields to link transaction lines together -- */
1667 x_interface_tax_rec.LINK_TO_LINE_CONTEXT := p_interface_line_rec.INTERFACE_LINE_CONTEXT;
1668 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE1 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1;
1669 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE2 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2;
1670 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE3 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3;
1671 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE4 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4;
1672 /* -- Only four attributes used for now
1673 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE5 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5;
1674 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE6 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6;
1675 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE7 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7;
1676 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE8 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE8;
1677 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE9 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE9;
1678 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE10 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE10;
1679 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE11 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE11;
1680 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE12 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE12;
1684 */
1681 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE13 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE13;
1682 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE14 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE14;
1683 x_interface_tax_rec.LINK_TO_LINE_ATTRIBUTE15 := p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE15;
1685
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1689 FND_MESSAGE.set_name('OZF', 'OZF_POPULATE_INTF_ERROR');
1690 FND_MSG_PUB.add;
1691 END IF;
1692 IF OZF_DEBUG_LOW_ON THEN
1693 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1694 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1695 FND_MSG_PUB.Add;
1696 END IF;
1697 x_return_status := FND_API.g_ret_sts_unexp_error;
1698 END Populate_Interface_Tax_Rec;
1699
1700
1701 /* ---------------------------------------------- *
1702 * Populate the Interface Distributions Record
1703 * ---------------------------------------------- */
1704 PROCEDURE Populate_Distributions_Rec
1705 ( p_claim_rec IN Claim_Rec_Type
1706 ,p_claim_line_id IN NUMBER
1707 ,p_cc_id_rec IN OZF_GL_INTERFACE_PVT.CC_ID_REC
1708 ,p_x_int_distributions_rec IN OUT NOCOPY RA_Int_Distributions_Rec_Type
1709 ,x_return_status OUT NOCOPY VARCHAR2
1710 ) IS
1711 -- Cursor to get claim reason name -- 11.5.10 Enhancements.
1712 CURSOR csr_get_reason_name (cv_reason_code_id IN NUMBER) IS
1713 -- [BEGIN OF BUG 3500049 FIXING]
1714 -- SELECT substr(name,1,30) name
1715 SELECT SUBSTRB(name,1,30) name
1716 -- [END OF BUG 3500049 FIXING]
1717 FROM ozf_reason_codes_vl
1718 WHERE reason_code_id = cv_reason_code_id;
1719
1720 BEGIN
1721 -- Start populating the interface record values
1722 x_return_status := FND_API.g_ret_sts_success;
1723
1724 /* -- Standard Who Columns ------------------------- */
1725 p_x_int_distributions_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1726 p_x_int_distributions_rec.CREATION_DATE := SYSDATE ;
1727 p_x_int_distributions_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1728 p_x_int_distributions_rec.LAST_UPDATE_DATE := SYSDATE;
1729 /* ------------------------------------------------- */
1730
1731 /* -- Fixed Values from unchangeble profiles ------- */
1732 p_x_int_distributions_rec.INTERFACE_LINE_CONTEXT := 'CLAIM';
1733
1734 /* ------------------------------------------------- */
1735
1736 /* -- All Interface line attributes enabled for the context CLAIM --- */
1737 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE1 := p_claim_rec.claim_number ;
1738 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE2 := TO_CHAR(p_claim_rec.claim_id) ;
1739 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE3 := TO_CHAR(p_claim_line_id);
1740 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE4 := 'Y';
1741
1742 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE5 := NVL(p_claim_rec.customer_ref_number, '-');
1743 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE6 := NVL(p_claim_rec.customer_reason, '-') ;
1744
1745 OPEN csr_get_reason_name(p_claim_rec.reason_code_id);
1746 FETCH csr_get_reason_name INTO p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE7; --reason name
1747 CLOSE csr_get_reason_name;
1748
1749 /* -- Only four attributes used for now
1750 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE5 := '0';
1751 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE6 := '0';
1752 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE7 := '0';
1753 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE8 := '0';
1754 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE9 := '0';
1755 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE10 := '0';
1756 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE11 := '0';
1757 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE12 := '0';
1758 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE13 := '0';
1759 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE14 := '0';
1760 p_x_int_distributions_rec.INTERFACE_LINE_ATTRIBUTE15 := '0';
1761 */
1762
1763 p_x_int_distributions_rec.CODE_COMBINATION_ID := p_cc_id_rec.code_combination_id;
1764 p_x_int_distributions_rec.ACCOUNT_CLASS := 'REV';
1765
1766 /* -- Set Org Id ------------------- */
1767 p_x_int_distributions_rec.ORG_ID := p_claim_rec.org_id;
1768
1769 /* ---- Finally populate the Amount ---------------------------- */
1770 /*
1771 IF p_claim_rec.payment_method = 'CREDIT_MEMO' THEN
1772 -- amount signs are changed for on account credit memos
1773 p_x_int_distributions_rec.AMOUNT := p_cc_id_rec.amount * -1;
1774 p_x_int_distributions_rec.ACCTD_AMOUNT := p_cc_id_rec.acctd_amount * -1;
1775 ELSIF p_claim_rec.payment_method = 'DEBIT_MEMO' THEN
1776 -- creation sign for debit memo is positive
1777 p_x_int_distributions_rec.AMOUNT := p_cc_id_rec.amount;
1778 p_x_int_distributions_rec.ACCTD_AMOUNT := p_cc_id_rec.acctd_amount;
1779 ELSIF p_claim_rec.payment_method = 'CHARGEBACK' THEN
1780 -- amount signs are changed for on account credit memos
1781 p_x_int_distributions_rec.AMOUNT := p_cc_id_rec.amount;
1782 p_x_int_distributions_rec.ACCTD_AMOUNT := p_cc_id_rec.acctd_amount;
1783 END IF;
1784 */
1785
1786 -- set the percent for amount
1787 p_x_int_distributions_rec.PERCENT := 100;
1788 /* ------------------------------------------------------------- */
1789
1790 EXCEPTION
1791 WHEN OTHERS THEN
1795 END IF;
1792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1793 FND_MESSAGE.set_name('OZF', 'OZF_POPULATE_DIST_ERROR');
1794 FND_MSG_PUB.add;
1796 IF OZF_DEBUG_LOW_ON THEN
1797 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1798 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1799 FND_MSG_PUB.Add;
1800 END IF;
1801 x_return_status := FND_API.g_ret_sts_unexp_error;
1802 END Populate_Distributions_Rec;
1803
1804 PROCEDURE Interface_Claim_Line
1805 ( p_claim_id IN NUMBER
1806 ,p_memo_line_id IN NUMBER
1807 ,p_claim_line_id IN NUMBER
1808 ,p_line_claim_curr_amt IN NUMBER
1809 ,p_line_tax_code IN VARCHAR2
1810 ,p_line_cc_id_flag IN VARCHAR2
1811 ,x_return_status OUT NOCOPY VARCHAR2
1812 ) IS
1813
1814 l_interface_line_rec RA_Interface_Lines_Rec_Type;
1815 l_interface_tax_rec RA_Interface_Lines_Rec_Type;
1816 l_int_sales_credits_rec RA_Int_Sales_Credits_Rec_Type;
1817 l_claim_rec Claim_Rec_Type;
1818
1819 CURSOR csr_sales_credit_type(cv_salesrep_id IN NUMBER) IS
1820 SELECT sales_credit_type_id
1821 FROM ra_salesreps
1822 WHERE salesrep_id = cv_salesrep_id;
1823
1824 BEGIN
1825
1826 x_return_status := FND_API.G_RET_STS_SUCCESS;
1827
1828 Query_Claim ( p_claim_id => p_claim_id,
1829 x_claim_rec => l_claim_rec,
1830 x_return_status => x_return_status);
1831
1832 IF x_return_status = FND_API.g_ret_sts_error THEN
1833 RAISE FND_API.g_exc_error;
1834 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1835 RAISE FND_API.g_exc_unexpected_error;
1836 END IF;
1837
1838 --Populate the interface record with information from claim header and line
1839 Populate_Interface_Line_Rec(p_claim_rec => l_claim_rec,
1840 p_memo_line_id => p_memo_line_id,
1841 p_claim_line_id => p_claim_line_id,
1842 p_line_claim_curr_amt => p_line_claim_curr_amt,
1843 p_line_tax_code => p_line_tax_code,
1844 p_line_cc_id_flag => p_line_cc_id_flag,
1845 p_x_interface_line_rec => l_interface_line_rec,
1846 x_return_status => x_return_status
1847 );
1848 IF x_return_status = FND_API.g_ret_sts_error THEN
1849 RAISE FND_API.g_exc_error;
1850 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1851 RAISE FND_API.g_exc_unexpected_error;
1852 END IF;
1853
1854 --Insert the line into RA_INTERFACE_LINES_ALL
1855 Insert_Interface_Line(l_interface_line_rec,
1856 x_return_status);
1857
1858 IF x_return_status = FND_API.g_ret_sts_error THEN
1859 RAISE FND_API.g_exc_error;
1860 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1861 RAISE FND_API.g_exc_unexpected_error;
1862 END IF;
1863
1864 -------------------
1865 -- Sales Credits --
1866 -------------------
1867 IF l_claim_rec.sales_rep_id IS NOT NULL THEN
1868 l_int_sales_credits_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1869 l_int_sales_credits_rec.CREATION_DATE := SYSDATE ;
1870 l_int_sales_credits_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1871 l_int_sales_credits_rec.LAST_UPDATE_DATE := SYSDATE;
1872
1873 l_int_sales_credits_rec.INTERFACE_LINE_CONTEXT := l_interface_line_rec.INTERFACE_LINE_CONTEXT;
1874 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE1 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1;
1875 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE2 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2;
1876 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE3 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3;
1877 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE4 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4;
1878 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE5 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5;
1879 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE6 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6;
1880 l_int_sales_credits_rec.INTERFACE_LINE_ATTRIBUTE7 := l_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7;
1881
1882 l_int_sales_credits_rec.SALESREP_ID := l_claim_rec.sales_rep_id;
1883 OPEN csr_sales_credit_type(l_claim_rec.sales_rep_id);
1884 FETCH csr_sales_credit_type INTO l_int_sales_credits_rec.SALES_CREDIT_TYPE_ID;
1885 CLOSE csr_sales_credit_type;
1886 l_int_sales_credits_rec.SALES_CREDIT_PERCENT_SPLIT := 100;
1887 l_int_sales_credits_rec.ORG_ID := l_claim_rec.org_id;
1888
1889 Insert_Interface_Sales_Credits(
1890 l_int_sales_credits_rec,
1891 x_return_status
1892 );
1893 IF x_return_status = FND_API.g_ret_sts_error THEN
1894 RAISE FND_API.g_exc_error;
1895 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1896 RAISE FND_API.g_exc_unexpected_error;
1897 END IF;
1898 END IF;
1899
1900 /* passing tax lines through AutoInvoice if tax_code exists in claim line. */
1901 /*
1902 IF p_line_tax_code IS NOT NULL THEN
1903 --prepare tax line interface record
1904 Populate_Interface_Tax_Rec( p_line_tax_code => p_line_tax_code,
1908 );
1905 p_interface_line_rec => l_interface_line_rec,
1906 x_interface_tax_rec => l_interface_tax_rec,
1907 x_return_status => x_return_status
1909 IF x_return_status = FND_API.g_ret_sts_error THEN
1910 RAISE FND_API.g_exc_error;
1911 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1912 RAISE FND_API.g_exc_unexpected_error;
1913 END IF;
1914
1915 --Insert the tax into RA_INTERFACE_LINES_ALL
1916 Insert_Interface_Tax( l_interface_tax_rec,
1917 x_return_status
1918 );
1919 IF x_return_status = FND_API.g_ret_sts_error THEN
1920 RAISE FND_API.g_exc_error;
1921 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1922 RAISE FND_API.g_exc_unexpected_error;
1923 END IF;
1924 END IF; -- end of passing tax interface record.
1925 */
1926
1927 END Interface_Claim_Line;
1928
1929 PROCEDURE Distribute_Claim_Line
1930 ( p_claim_id IN NUMBER
1931 ,p_claim_line_id IN NUMBER
1932 ,p_cc_id_rec IN OZF_GL_INTERFACE_PVT.CC_ID_REC
1933 ,x_return_status OUT NOCOPY VARCHAR2
1934 ) IS
1935
1936 l_int_distributions_rec RA_Int_Distributions_Rec_Type;
1937 l_claim_rec Claim_Rec_Type;
1938
1939 BEGIN
1940
1941 x_return_status := FND_API.G_RET_STS_SUCCESS;
1942
1943 Query_Claim ( p_claim_id => p_claim_id,
1944 x_claim_rec => l_claim_rec,
1945 x_return_status => x_return_status);
1946
1947 IF x_return_status = FND_API.g_ret_sts_error THEN
1948 RAISE FND_API.g_exc_error;
1949 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1950 RAISE FND_API.g_exc_unexpected_error;
1951 END IF;
1952
1953 --Populate the distribution record with info from claim header and line
1954 Populate_Distributions_Rec(p_claim_rec => l_claim_rec,
1955 p_claim_line_id => p_claim_line_id,
1956 p_cc_id_rec => p_cc_id_rec,
1957 p_x_int_distributions_rec => l_int_distributions_rec,
1958 x_return_status => x_return_status
1959 );
1960 IF x_return_status = FND_API.g_ret_sts_error THEN
1961 RAISE FND_API.g_exc_error;
1962 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1963 RAISE FND_API.g_exc_unexpected_error;
1964 END IF;
1965
1966 -- Insert the line into RA_INTERFACE_DISTRIBUTIONS_ALL
1967 Insert_Int_Distributions(l_int_distributions_rec,
1968 x_return_status);
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 END Distribute_Claim_Line;
1976
1977
1978 PROCEDURE Interface_Claim
1979 ( p_api_version IN NUMBER
1980 ,p_init_msg_list IN VARCHAR2
1981 ,p_commit IN VARCHAR2
1982 ,p_validation_level IN VARCHAR2
1983 ,p_claim_id IN NUMBER
1984 ,x_return_status OUT NOCOPY VARCHAR2
1985 ,x_msg_data OUT NOCOPY VARCHAR2
1986 ,x_msg_count OUT NOCOPY NUMBER
1987 ) IS
1988
1989 --
1990 l_api_name CONSTANT VARCHAR2(30) := 'Interface_Claim' ;
1991 l_api_version CONSTANT NUMBER := 1.0;
1992 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1993 --
1994 l_resource_id NUMBER;
1995 l_user_id NUMBER;
1996 l_login_user_id NUMBER;
1997 l_login_user_status VARCHAR2(30);
1998 l_Error_Msg VARCHAR2(2000);
1999 l_Error_Token VARCHAR2(80);
2000 l_object_version_number NUMBER := 1;
2001 --x_msg_count NUMBER;
2002 --x_msg_data VARCHAR2(240);
2003 l_return_status VARCHAR2(1);
2004 l_result_out VARCHAR2(30);
2005
2006 /*
2007 CURSOR grouped_claim_line_csr IS
2008 SELECT SUM(NVL(cll.claim_currency_amount,0)),
2009 NVL(cll.claim_currency_amount,0)
2010 FROM ozf_claim_lines cll
2011 WHERE cll.claim_id = p_claim_id;
2012 GROUP BY Get_Memo_Line_Id(cll.claim_id);
2013 */
2014 --12.1 Price Protection Enhancement
2015 CURSOR claim_line_csr IS
2016 SELECT cll.claim_line_id
2017 , NVL(cll.claim_currency_amount,0)
2018 , cll.tax_code
2019 , Get_Memo_Line_Id(cll.claim_line_id)
2020 , cll.earnings_associated_flag
2021 , cll.source_object_class
2022 FROM ozf_claim_lines cll
2023 WHERE cll.claim_id = p_claim_id;
2024
2025 l_memo_line_id NUMBER;
2026 l_grpd_claim_curr_amt NUMBER;
2027 l_claim_line_id NUMBER;
2028 l_line_claim_curr_amt NUMBER;
2029 l_line_tax_code VARCHAR2(50);
2030 l_line_cc_id_flag VARCHAR2(1);
2031 l_earnings_asso_flag VARCHAR2(1);
2032 l_cc_id_tbl OZF_GL_INTERFACE_PVT.CC_ID_TBL;
2033
2034 --Bug3928503 - post_to_gl flag not considered for promotional claims
2035 l_post_to_gl varchar2(1);
2036 --12.1 Price Protection Enhancement
2040 SELECT osp.post_to_gl
2037 l_source_object_class varchar2(25);
2038
2039 CURSOR claim_gl_posting_csr(p_id in number) IS
2041 FROM ozf_sys_parameters_all osp
2042 , ozf_claims_all oc
2043 WHERE NVL(osp.org_id, -99) = NVL(oc.org_id, -99)
2044 AND oc.claim_id = p_id;
2045
2046
2047 BEGIN
2048
2049 /* ------- Begin Standard API Calls --------------------------- */
2050 -- Standard begin of API savepoint
2051 SAVEPOINT Interface_Claim_PVT ;
2052 -- Standard call to check for call compatibility.
2053 IF NOT FND_API.Compatible_API_Call (
2054 l_api_version,
2055 p_api_version,
2056 l_api_name,
2057 G_PKG_NAME)
2058 THEN
2059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2060 END IF;
2061 -- Debug Message
2062 IF OZF_DEBUG_LOW_ON THEN
2063 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2064 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
2065 FND_MSG_PUB.Add;
2066 END IF;
2067 --Initialize message list if p_init_msg_list is TRUE.
2068 IF FND_API.To_Boolean (p_init_msg_list) THEN
2069 FND_MSG_PUB.initialize;
2070 END IF;
2071 -- Initialize API return status to sucess
2072 x_return_status := FND_API.G_RET_STS_SUCCESS;
2073
2074 /* ------- End Standard API Calls ----------------------------- */
2075
2076 /* ------- Begin API Logic ------------------------------------ */
2077
2078 OPEN claim_gl_posting_csr(p_claim_id) ;
2079 FETCH claim_gl_posting_csr INTO l_post_to_gl;
2080 CLOSE claim_gl_posting_csr;
2081
2082
2083 OPEN claim_line_csr;
2084 LOOP
2085 l_line_cc_id_flag := NULL;
2086
2087 FETCH claim_line_csr INTO l_claim_line_id
2088 , l_line_claim_curr_amt
2089 , l_line_tax_code
2090 , l_memo_line_id
2091 , l_earnings_asso_flag
2092 , l_source_object_class;
2093 EXIT WHEN claim_line_csr%NOTFOUND;
2094 --12.1 Price Protection Enhancement
2095 IF ((l_earnings_asso_flag = 'T' AND l_post_to_gl = 'T') OR (l_source_object_class = 'PPCUSTOMER'))
2096 THEN
2097 OZF_GL_INTERFACE_PVT.Get_GL_Account(
2098 p_api_version => l_api_version
2099 ,p_init_msg_list => FND_API.g_false
2100 ,p_commit => FND_API.g_false
2101 ,p_validation_level => FND_API.g_valid_level_full
2102 ,x_return_status => l_return_status
2103 ,x_msg_data => x_msg_data
2104 ,x_msg_count => x_msg_count
2105 ,p_source_id => l_claim_line_id
2106 ,p_source_table => 'OZF_CLAIM_LINES_ALL'
2107 ,p_account_type => 'REC_CLEARING'
2108 ,x_cc_id_tbl => l_cc_id_tbl
2109 );
2110 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2111 RAISE FND_API.G_EXC_ERROR;
2112 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2114 END IF;
2115
2116 l_line_cc_id_flag := 'Y';
2117 ELSE
2118 l_line_cc_id_flag := 'N';
2119 END IF;
2120
2121 Interface_Claim_Line( p_claim_id,
2122 l_memo_line_id,
2123 l_claim_line_id,
2124 l_line_claim_curr_amt,
2125 l_line_tax_code,
2126 l_line_cc_id_flag,
2127 l_return_status);
2128
2129 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2130 RAISE FND_API.G_EXC_ERROR;
2131 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2133 END IF;
2134
2135 IF l_line_cc_id_flag = 'Y' THEN
2136 FOR i IN 1..(l_cc_id_tbl.COUNT) LOOP
2137 Distribute_Claim_Line(p_claim_id,
2138 l_claim_line_id,
2139 l_cc_id_tbl(i),
2140 l_return_status
2141 );
2142 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2143 RAISE FND_API.G_EXC_ERROR;
2144 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2146 END IF;
2147 END LOOP;
2148 END IF;
2149 END LOOP;
2150
2151 CLOSE claim_line_csr;
2152
2153 /* ------- End API Logic ------------------------------------- */
2154
2155 /* ------- Begin Update Claim Payment Status ----------------- */
2156 UPDATE ozf_claims_all
2157 SET payment_status = 'INTERFACED'
2158 WHERE claim_id = p_claim_id;
2159 /* ------- End Update Claim Payment Status ------------------- */
2160
2161 /* ------- Begin Standard API Calls --------------------------- */
2162
2163 --Standard check of commit
2164 IF FND_API.To_Boolean ( p_commit ) THEN
2165 COMMIT WORK;
2166 END IF;
2167 -- Debug Message
2168 IF OZF_DEBUG_LOW_ON THEN
2169 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2170 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
2171 FND_MSG_PUB.Add;
2172 END IF;
2173 --Standard call to get message count and if count=1, get the message
2174 FND_MSG_PUB.Count_And_Get (
2175 p_encoded => FND_API.G_FALSE,
2176 p_count => x_msg_count,
2177 p_data => x_msg_data
2178 );
2179
2180 /* ------- End Standard API Calls ----------------------------- */
2181 EXCEPTION
2182 WHEN FND_API.G_EXC_ERROR THEN
2183 IF (claim_line_csr%ISOPEN) THEN
2184 CLOSE claim_line_csr;
2185 END IF;
2186 ROLLBACK TO Interface_Claim_PVT;
2187 x_return_status := FND_API.G_RET_STS_ERROR;
2188 -- Standard call to get message count and if count=1, get the message
2189 FND_MSG_PUB.Count_And_Get (
2190 p_encoded => FND_API.G_FALSE,
2191 p_count => x_msg_count,
2192 p_data => x_msg_data
2193 );
2194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2195 IF (claim_line_csr%ISOPEN) THEN
2196 CLOSE claim_line_csr;
2197 END IF;
2198 ROLLBACK TO Interface_Claim_PVT;
2199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2200 -- Standard call to get message count and if count=1, get the message
2201 FND_MSG_PUB.Count_And_Get (
2202 p_encoded => FND_API.G_FALSE,
2203 p_count => x_msg_count,
2204 p_data => x_msg_data
2205 );
2206 WHEN OTHERS THEN
2207 IF (claim_line_csr%ISOPEN) THEN
2208 CLOSE claim_line_csr;
2209 END IF;
2210 ROLLBACK TO Interface_Claim_PVT;
2211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2212 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2213 THEN
2214 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2215 END IF;
2216 -- Standard call to get message count and if count=1, get the message
2217 FND_MSG_PUB.Count_And_Get (
2218 p_encoded => FND_API.G_FALSE,
2219 p_count => x_msg_count,
2220 p_data => x_msg_data
2221 );
2222
2223 END Interface_Claim ;
2224
2225 END OZF_Ar_Interface_PVT ;