[Home] [Help]
PACKAGE BODY: APPS.OZF_AP_INTERFACE_PVT
Source
1 PACKAGE BODY OZF_AP_INTERFACE_PVT AS
2 /* $Header: ozfvapib.pls 120.9.12010000.2 2008/09/16 05:37:00 kpatro ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_AP_INTERFACE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvapib.pls';
6
7 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
9
10 ---------------------------------------------------------------------
11 -- PROCEDURE
12 -- Create_ap_invoice
13 --
14 -- PURPOSE
15 -- Create a payabels invoice into payables open interface table.
16 --
17 -- PARAMETERS
18 -- p_claim_id : Will be passed as Invoice id
19 -- p_claim_number : Will be passed as Invoice number
20 -- p_settled_date : Will be passed as Invoice_date
21 -- p_vendor_id : Supplier id
22 -- p_vendor_site_id : Supplier site id
23 -- p_amount_settled : Will be passed as Invoive amount
24 -- p_currency_code : Will be passed as Invoice currency code
25 -- p_exchange_rate : Invoice exchange rate
26 -- p_exchange_rate_type : Invoice exchange rate type
27 -- p_exchange_rate_date : Invoice exchange rate date
28 -- p_terms_id : Payment Term id
29 -- p_payment_method : Payment method type
30 -- p_gl_date : Gl date
31 --
32 -- NOTES
33 -- 1. creates an invoice header and invoice line in payables open
34 -- interface table.
35 -- 2. Passes the claim number and settled date to invoice number
36 -- and invoice date.
37 -- 3. Source = 'CLAIMS'
38
39 --- Sahana 20-Jul-2005 R12: Support for EFT, WIRE, AP_DEFAULR
40 --- and AP_DEBIT payment methods.
41 --- Handling of AP document cancellation.
42 ---------------------------------------------------------------------
43 PROCEDURE Query_Claim(
44 p_claim_id IN NUMBER
45 ,x_claim_rec OUT NOCOPY OZF_Claim_PVT.claim_rec_type
46 ,x_return_status OUT NOCOPY VARCHAR2
47 )
48 IS
49 BEGIN
50 SELECT
51 CLAIM_ID
52 ,CLAIM_NUMBER
53 ,CLAIM_TYPE_ID
54 ,CLAIM_CLASS
55 ,CLAIM_DATE
56 ,DUE_DATE
57 ,AMOUNT
58 ,AMOUNT_ADJUSTED
59 ,AMOUNT_REMAINING
60 ,AMOUNT_SETTLED
61 ,ACCTD_AMOUNT
62 ,ACCTD_AMOUNT_REMAINING
63 ,TAX_CODE
64 ,TAX_CALCULATION_FLAG
65 ,CURRENCY_CODE
66 ,EXCHANGE_RATE_TYPE
67 ,EXCHANGE_RATE_DATE
68 ,EXCHANGE_RATE
69 ,SET_OF_BOOKS_ID
70 ,CUST_ACCOUNT_ID
71 ,CUST_BILLTO_ACCT_SITE_ID
72 ,CUST_SHIPTO_ACCT_SITE_ID
73 ,LOCATION_ID
74 ,PAY_RELATED_ACCOUNT_FLAG
75 ,RELATED_CUST_ACCOUNT_ID
76 ,RELATED_SITE_USE_ID
77 ,RELATIONSHIP_TYPE
78 ,VENDOR_ID
79 ,VENDOR_SITE_ID
80 ,REASON_TYPE
81 ,REASON_CODE_ID
82 ,STATUS_CODE
83 ,CUSTOMER_REF_DATE
84 ,CUSTOMER_REF_NUMBER
85 ,GL_DATE
86 ,PAYMENT_METHOD
87 ,PAYMENT_REFERENCE_ID
88 ,PAYMENT_REFERENCE_NUMBER
89 ,PAYMENT_REFERENCE_DATE
90 ,PAYMENT_STATUS
91 ,SETTLED_DATE
92 ,EFFECTIVE_DATE
93 ,COMMENTS
94 ,ORG_ID
95 ,LEGAL_ENTITY_ID
96 INTO
97 x_claim_rec.claim_id
98 ,x_claim_rec.claim_number
99 ,x_claim_rec.claim_type_id
100 ,x_claim_rec.claim_class
101 ,x_claim_rec.claim_date
102 ,x_claim_rec.due_date
103 ,x_claim_rec.amount
104 ,x_claim_rec.amount_adjusted
105 ,x_claim_rec.amount_remaining
106 ,x_claim_rec.amount_settled
107 ,x_claim_rec.acctd_amount
108 ,x_claim_rec.acctd_amount_remaining
109 ,x_claim_rec.tax_code
110 ,x_claim_rec.tax_calculation_flag
111 ,x_claim_rec.currency_code
112 ,x_claim_rec.exchange_rate_type
113 ,x_claim_rec.exchange_rate_date
114 ,x_claim_rec.exchange_rate
115 ,x_claim_rec.set_of_books_id
116 ,x_claim_rec.cust_account_id
117 ,x_claim_rec.cust_billto_acct_site_id
118 ,x_claim_rec.cust_shipto_acct_site_id
119 ,x_claim_rec.location_id
120 ,x_claim_rec.pay_related_account_flag
121 ,x_claim_rec.related_cust_account_id
122 ,x_claim_rec.related_site_use_id
123 ,x_claim_rec.relationship_type
124 ,x_claim_rec.vendor_id
125 ,x_claim_rec.vendor_site_id
126 ,x_claim_rec.reason_type
127 ,x_claim_rec.reason_code_id
128 ,x_claim_rec.status_code
129 ,x_claim_rec.customer_ref_date
130 ,x_claim_rec.customer_ref_number
131 ,x_claim_rec.gl_date
132 ,x_claim_rec.payment_method
133 ,x_claim_rec.payment_reference_id
134 ,x_claim_rec.payment_reference_number
135 ,x_claim_rec.payment_reference_date
136 ,x_claim_rec.payment_status
137 ,x_claim_rec.settled_date
138 ,x_claim_rec.effective_date
139 ,x_claim_rec.comments
140 ,x_claim_rec.org_id
141 ,x_claim_rec.legal_entity_id
142 FROM ozf_claims_all
143 WHERE claim_id = p_claim_id ;
144
145 x_return_status := FND_API.g_ret_sts_success;
146 EXCEPTION
147 WHEN OTHERS THEN
148 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
149 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_QUERY_ERROR');
150 FND_MSG_PUB.add;
151 END IF;
152 x_return_status := FND_API.g_ret_sts_unexp_error;
153 END Query_Claim;
154
155 PROCEDURE Create_ap_invoice (
156 p_api_version IN NUMBER
157 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
158 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
159 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
160
161 ,x_return_status OUT NOCOPY VARCHAR2
162 ,x_msg_data OUT NOCOPY VARCHAR2
163 ,x_msg_count OUT NOCOPY NUMBER
164
165 ,p_claim_id IN NUMBER
166 )
167 IS
168 l_api_name CONSTANT VARCHAR2(30) := 'Create_ap_invoice';
169 l_api_version CONSTANT NUMBER := 1.0;
170 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
171 --
172 l_user_id NUMBER;
173 l_login_id NUMBER;
174 l_sys_date DATE;
175
176
177 l_Error_Msg varchar2(2000);
178 l_Error_Token varchar2(80);
179
180 l_invoice_line_num number := 1;
181 l_invoice_id number;
182 l_invoice_line_id number;
183
184 l_reference VARCHAR2(240);
185
186 CURSOR csr_claim_ref_detail(cv_claim_id IN NUMBER) IS
187 SELECT customer_ref_number
188 FROM ozf_claims
189 WHERE claim_id = cv_claim_id;
190
191 CURSOR invoice_int_seq_csr IS
192 select ap_invoices_interface_s.nextval
193 from dual;
194
195 CURSOR invoice_line_int_seq_csr IS
196 select ap_invoice_lines_interface_s.nextval
197 from dual;
198
199 CURSOR csr_system_param_details(cv_org_id IN NUMBER) IS
200 SELECT ap.accts_pay_code_combination_id,
201 ozf.gl_id_ded_clearing,
202 ozf.payables_source,
203 ozf.ap_payment_term_id
204 FROM ap_system_parameters_all ap,
205 ozf_sys_parameters_all ozf
206 WHERE ozf.set_of_books_id = ap.set_of_books_id
207 AND ozf.org_id = cv_org_id;
208 l_accts_pay_code_comb_id NUMBER;
209 l_dist_code_comb_id NUMBER;
210 l_source VARCHAR2(30);
211 l_term_id NUMBER;
212
213 -- kishore
214
215 CURSOR Get_Invoicenum_csr(cv_invoice_num IN VARCHAR2, cv_vendor_id IN NUMBER) IS
216 select invoice_num
217 from AP_INVOICES
218 where INVOICE_NUM = cv_invoice_num
219 and VENDOR_ID = cv_vendor_id;
220
221 CURSOR csr_get_claim_lines(cv_claim_id IN NUMBER) IS
222 SELECT claim_currency_amount
223 , acctd_amount
224 , tax_code
225 , claim_line_id
226 , item_type
227 , item_id
228 , item_description
229 , source_object_class
230 , source_object_id
231 , activity_type
232 , activity_id
233 FROM ozf_claim_lines_all
234 WHERE claim_id = cv_claim_id;
235 l_claim_line_rec csr_get_claim_lines%ROWTYPE;
236
237 l_cc_id_tbl OZF_Gl_Interface_PVT.cc_id_tbl;
238 l_vendor_clearing_account NUMBER;
239 l_source_object_name varchar2(30);
240 l_account_type varchar2(30);
241
242 l_claim_rec OZF_Claim_PVT.claim_rec_type;
243 l_amount_settled NUMBER;
244
245 l_payment_method VARCHAR2(30);
246 l_claim_number VARCHAR2(30);
247 l_last_count NUMBER;
248
249 -- To derive line description
250 l_line_description VARCHAR2(2000);
251 l_item_type VARCHAR2(30);
252 l_final_descr VARCHAR2(240);
253
254 -- kishore
255 l_x_claim_number VARCHAR2(50) := null;
256
257 CURSOR csr_item_desc(cv_item_id IN NUMBER) IS
258 SELECT description
259 FROM mtl_system_items_vl
260 WHERE inventory_item_id = cv_item_id;
261
262 CURSOR csr_category_desc(cv_category_id IN NUMBER) IS
263 SELECT SUBSTRB(category_desc,1,240)
264 FROM eni_prod_den_hrchy_parents_v
265 WHERE category_id = cv_category_id;
266
267 CURSOR csr_media_desc(cv_media_channel_id IN NUMBER) IS
268 SELECT channel_name
269 FROM ams_media_channels_vl
270 WHERE channel_id = cv_media_channel_id;
271
272 CURSOR csr_trx_number(cv_trx_id IN NUMBER) IS
273 SELECT trx_number
274 FROM ra_customer_trx_all
275 WHERE customer_trx_id = cv_trx_id;
276
277 CURSOR csr_order_number(cv_header_id IN NUMBER) IS
278 SELECT order_number
279 FROM oe_order_headers_all
280 WHERE header_id = cv_header_id;
281
282 CURSOR csr_request_number(cv_request_id IN NUMBER) IS
283 SELECT request_number
284 FROM ozf_request_headers_vl
285 WHERE request_header_id = cv_request_id;
286
287 CURSOR csr_offer_code(cv_list_id IN NUMBER) IS
288 SELECT offer_code
289 FROM ozf_offers
290 WHERE qp_list_header_id = cv_list_id;
291
292 CURSOR csr_type_meaning(cv_code IN VARCHAR2) IS
293 SELECT meaning
294 FROM ozf_lookups
295 WHERE lookup_type = 'OZF_LINE_OVER_TYPE'
296 AND lookup_code = cv_code;
297
298 CURSOR csr_item_type_meaning(cv_code IN VARCHAR2) IS
299 SELECT meaning
300 FROM ozf_lookups
301 WHERE lookup_type = 'OZF_CLAIM_ITEM_TYPE'
302 AND lookup_code = cv_code;
303
304 -- To derive terms id
305 /*CURSOR csr_fin_pay_method(cv_org_id IN NUMBER) IS
306 SELECT payment_method_lookup_code
307 FROM FINANCIALS_SYSTEM_PARAMS_ALL
308 WHERE org_id = cv_org_id;
309
310 CURSOR csr_vendor_pay_method(cv_vendor_id IN NUMBER) IS
311 SELECT payment_method_lookup_code
312 FROM po_vendors
313 WHERE vendor_id = cv_vendor_id;
314
315 CURSOR csr_vendor_site_pay_method(cv_vendor_site_id IN NUMBER) IS
316 SELECT payment_method_lookup_code
317 FROM po_vendor_sites
318 WHERE vendor_site_id = cv_vendor_site_id; */
319
320
321 --
322 BEGIN
323
324 -- Standard begin of API savepoint
325 SAVEPOINT Create_ap_invoice_PVT;
326
327
328 -- Standard call to check for call compatibility.
329 IF NOT FND_API.Compatible_API_Call (
330 l_api_version,
331 p_api_version,
332 l_api_name,
333 G_PKG_NAME)
334 THEN
335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336 END IF;
337
338
339 -- Debug Message
340 IF OZF_DEBUG_LOW_ON THEN
341 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
342 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
343 FND_MSG_PUB.Add;
344 END IF;
345
346
347 --Initialize message list if p_init_msg_list is TRUE.
348 IF FND_API.To_Boolean (p_init_msg_list) THEN
349 FND_MSG_PUB.initialize;
350 END IF;
351
352
353 -- Initialize API return status to sucess
354 x_return_status := FND_API.G_RET_STS_SUCCESS;
355
356 -- Query the claim
357 Query_Claim(
358 p_claim_id => p_claim_id
359 ,x_claim_rec => l_claim_rec
360 ,x_return_status => x_return_status);
361 IF x_return_status = FND_API.g_ret_sts_error THEN
362 RAISE FND_API.g_exc_error;
363 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
364 RAISE FND_API.g_exc_unexpected_error;
365 END IF;
366
367
368 l_user_id := NVL(FND_GLOBAL.user_id,-1);
369 l_login_id := NVL(FND_GLOBAL.conc_login_id,-1);
370 l_sys_date := SYSDATE;
371
372 -- Get Accts. Payables code combination id to populate Liability account
373 -- in Invoice Header.
374 OPEN csr_system_param_details( l_claim_rec.org_id);
375 FETCH csr_system_param_details INTO l_accts_pay_code_comb_id,
376 l_dist_code_comb_id,l_source,l_term_id;
377 CLOSE csr_system_param_details;
378
379 IF l_claim_rec.payment_reference_number IS NULL THEN
380 l_claim_number := l_claim_rec.claim_number;
381 ELSE -- This is a retry of settlement
382 BEGIN
383 l_last_count := TO_NUMBER(SUBSTRB(l_claim_rec.payment_reference_number,
384 INSTRB(l_claim_rec.payment_reference_number,'.',-1,1)+1,
385 LENGTHB(l_claim_rec.payment_reference_number)));
386 l_claim_number := l_claim_rec.claim_number ||'.'||(l_last_count+1);
387 EXCEPTION
388 WHEN OTHERS THEN
389 l_claim_number := l_claim_rec.payment_reference_number ||'.'||1;
390 END;
391 END IF;
392
393 -- BUG 4754076 BEGIN
394 OPEN Get_Invoicenum_csr(l_claim_number, l_claim_rec.vendor_id);
395 FETCH Get_Invoicenum_csr INTO l_x_claim_number;
396 CLOSE Get_Invoicenum_csr;
397
398 -- If already exists do not insert record into Interface table else
399 -- verify if the invoice has already been created for this claim.
400
401 IF l_x_claim_number is NULL THEN
402
403 -- get invoice id from sequence
404 OPEN invoice_int_seq_csr;
405 FETCH invoice_int_seq_csr INTO l_invoice_id;
406 CLOSE invoice_int_seq_csr;
407
408 -- exchange rate to be populated only when the type is User
409 IF l_claim_rec.exchange_rate_type <> 'User' THEN
410 l_claim_rec.exchange_rate := null;
411 END IF;
412
413 IF l_claim_rec.payment_method IN ( 'CHECK','EFT','WIRE') THEN
414 l_payment_method := l_claim_rec.payment_method;
415 ELSE
416 NULL;
417 -- Payment Method derived by Payables.
418 END IF;
419
420 -- populate the invoice interface line for the claim
421 OPEN csr_get_claim_lines(l_claim_rec.claim_id);
422 LOOP
423 FETCH csr_get_claim_lines INTO l_claim_line_rec;
424 EXIT WHEN csr_get_claim_lines%NOTFOUND;
425
426 IF l_claim_rec.payment_method = 'AP_DEBIT'
427 AND SIGN(l_claim_line_rec.claim_currency_Amount) <> -1 THEN
428 l_claim_line_rec.claim_currency_amount := l_claim_line_rec.claim_currency_amount * -1;
429 END IF;
430
431 l_amount_settled := NVL(l_amount_settled,0) + l_claim_line_rec.claim_currency_amount ;
432
433
434 -- Derive Line Description
435 IF l_claim_line_rec.item_type in ( 'PRODUCT', 'FAMILY','MEDIA','ITEM') THEN
436 OPEN csr_item_type_meaning(NVL(l_claim_line_rec.source_object_class,l_claim_line_rec.activity_type));
437 FETCH csr_item_type_meaning INTO l_item_type;
438 CLOSE csr_item_type_meaning;
439 l_final_descr := l_item_type || ':';
440 ELSIF l_claim_line_rec.source_object_class IS NOT NULL OR
441 l_claim_line_rec.activity_type IS NOT NULL THEN
442 OPEN csr_type_meaning(NVL(l_claim_line_rec.source_object_class,l_claim_line_rec.activity_type));
443 FETCH csr_type_meaning INTO l_item_type;
444 CLOSE csr_type_meaning;
445 l_final_descr := l_item_type || ':';
446 END IF;
447
448 IF l_claim_line_rec.item_type = 'PRODUCT' THEN
449 OPEN csr_item_desc(l_claim_line_rec.item_id);
450 FETCH csr_item_desc INTO l_line_description;
451 CLOSE csr_item_desc;
452 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
453 ELSIF l_claim_line_rec.item_type = 'FAMILY' THEN
454 OPEN csr_category_desc(l_claim_line_rec.item_id);
455 FETCH csr_category_desc INTO l_line_description;
456 CLOSE csr_category_desc;
457 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
458 ELSIF l_claim_line_rec.item_type = 'MEDIA' THEN
459 OPEN csr_media_desc(l_claim_line_rec.item_id);
460 FETCH csr_media_desc INTO l_line_description;
461 CLOSE csr_media_desc;
462 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
463 ELSIF l_claim_line_rec.source_object_class <> 'ORDER' THEN
464 OPEN csr_trx_number(l_claim_line_rec.source_object_id);
465 FETCH csr_trx_number INTO l_line_description;
466 CLOSE csr_trx_number;
467 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
468 ELSIF l_claim_line_rec.source_object_class = 'ORDER' THEN
469 OPEN csr_order_number(l_claim_line_rec.source_object_id);
470 FETCH csr_order_number INTO l_line_description;
471 CLOSE csr_order_number;
472 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
473 ELSIF l_claim_line_rec.activity_type = 'OFFR' THEN
474 OPEN csr_offer_code(l_claim_line_rec.activity_id);
475 FETCH csr_offer_code INTO l_line_description;
476 CLOSE csr_offer_code;
477 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
478 ELSIF l_claim_line_rec.activity_type in ('SOFT_FUND','SPECIAL_PRICE' ) THEN
479 OPEN csr_request_number(l_claim_line_rec.activity_id);
480 FETCH csr_request_number INTO l_line_description;
481 CLOSE csr_request_number;
482 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
483 ELSE
484 l_final_descr := NVL(l_claim_line_rec.item_description, l_claim_rec.claim_number);
485 END IF;
486
487
488 -- get the vendor clearing account from gl api
489 OZF_Gl_Interface_PVT.Get_GL_Account(
490 p_api_version => p_api_version
491 ,p_init_msg_list => FND_API.G_FALSE
492 ,p_commit => FND_API.G_FALSE
493 ,p_validation_level => p_validation_level
494 ,x_return_status => x_return_status
495 ,x_msg_data => x_msg_data
496 ,x_msg_count => x_msg_count
497 ,p_source_id => l_claim_line_rec.claim_line_id
498 ,p_source_table => 'OZF_CLAIM_LINES_ALL'
499 ,p_account_type => 'VEN_CLEARING'
500 ,x_cc_id_tbl => l_cc_id_tbl);
501 IF x_return_status = FND_API.g_ret_sts_error THEN
502 RAISE FND_API.g_exc_error;
503 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
504 RAISE FND_API.g_exc_unexpected_error;
505 END IF;
506
507 FOR i in 1..l_cc_id_tbl.count LOOP
508 l_vendor_clearing_account := l_cc_id_tbl(i).code_combination_id;
509 END LOOP;
510
511
512 -- get invoice line id from sequence
513 OPEN invoice_line_int_seq_csr;
514 FETCH invoice_line_int_seq_csr INTO l_invoice_line_id;
515 CLOSE invoice_line_int_seq_csr;
516
517 INSERT INTO AP_INVOICE_LINES_INTERFACE (
518 INVOICE_ID
519 ,INVOICE_LINE_ID
520 ,LINE_NUMBER
521 ,LINE_TYPE_LOOKUP_CODE
522 ,AMOUNT
523 ,ACCOUNTING_DATE
524 ,TAX_CLASSIFICATION_CODE
525 ,LAST_UPDATED_BY
526 ,LAST_UPDATE_DATE
527 ,LAST_UPDATE_LOGIN
528 ,CREATED_BY
529 ,CREATION_DATE
530 ,DIST_CODE_COMBINATION_ID
531 ,ORG_ID
532 ,DESCRIPTION
533 ,APPLICATION_ID
534 ,PRODUCT_TABLE
535 ,REFERENCE_KEY1
536 ,REFERENCE_KEY2
537 ,REFERENCE_KEY3
538 ,REFERENCE_KEY4
539 ,REFERENCE_KEY5
540 ,SOURCE_APPLICATION_ID
541 ,SOURCE_ENTITY_CODE
542 ,SOURCE_EVENT_CLASS_CODE
543 )
544 VALUES (
545 l_invoice_id
546 ,l_invoice_line_id
547 ,l_invoice_line_num
548 ,'ITEM'
549 ,l_claim_line_rec.claim_currency_amount
550 ,l_claim_rec.gl_date
551 ,l_claim_line_rec.tax_code
552 ,l_user_id
553 ,l_sys_date
554 ,l_login_id
555 ,l_user_id
556 ,l_sys_date
557 ,l_vendor_clearing_account
558 ,l_claim_rec.org_id
559 ,l_final_descr
560 ,682
561 ,'OZF_CLAIMS_ALL'
562 ,l_claim_rec.claim_id
563 ,l_claim_rec.customer_ref_date
564 ,l_claim_rec.customer_ref_number
565 ,l_claim_rec.customer_reason
566 ,l_claim_line_rec.claim_line_id
567 ,682
568 ,'OZF_CLAIMS'
569 , 'TRADE_MGT_PAYABLES'
570 );
571 l_invoice_line_num := l_invoice_line_num + 1;
572 END LOOP;
573 CLOSE csr_get_claim_lines;
574
575 -- Added For Bug 7384640
576 OPEN csr_claim_ref_detail(p_claim_id);
577 FETCH csr_claim_ref_detail INTO l_reference;
578 CLOSE csr_claim_ref_detail;
579
580 IF l_reference IS NOT NULL THEN
581 l_claim_number := l_reference;
582 END IF;
583
584 -- Inserting an Invoice header record into AP_INVOICE_INTERFACE table.
585 INSERT INTO AP_INVOICES_INTERFACE (
586 INVOICE_ID
587 , INVOICE_NUM
588 , INVOICE_DATE
589 , VENDOR_ID
590 , VENDOR_SITE_ID
591 , INVOICE_AMOUNT
592 , INVOICE_CURRENCY_CODE
593 , EXCHANGE_RATE
594 , EXCHANGE_RATE_TYPE
595 , EXCHANGE_DATE
596 , TERMS_ID
597 , DESCRIPTION
598 , LAST_UPDATE_DATE
599 , LAST_UPDATED_BY
600 , LAST_UPDATE_LOGIN
601 , CREATION_DATE
602 , CREATED_BY
603 , SOURCE
604 , GROUP_ID
605 --, WORKFLOW_FLAG
606 , PAYMENT_METHOD_CODE
607 , GL_DATE
608 , ACCTS_PAY_CODE_COMBINATION_ID
609 , ORG_ID
610 , LEGAL_ENTITY_ID
611 , APPLICATION_ID
612 , PRODUCT_TABLE
613 , REFERENCE_KEY1
614 , REFERENCE_KEY2
615 , REFERENCE_KEY3
616 , REFERENCE_KEY4
617 , REFERENCE_KEY5
618 ,CALC_TAX_DURING_IMPORT_FLAG
619 ,ADD_TAX_TO_INV_AMT_FLAG
620 )
621 VALUES (
622 l_invoice_id
623 ,l_claim_number
624 ,l_claim_rec.settled_date
625 ,l_claim_rec.vendor_id
626 ,l_claim_rec.vendor_site_id
627 ,l_amount_settled
628 ,l_claim_rec.currency_code
629 ,l_claim_rec.exchange_rate
630 ,l_claim_rec.exchange_rate_type
631 ,l_claim_rec.exchange_rate_date
632 ,l_term_id
633 ,l_claim_rec.customer_ref_number
634 ,l_sys_date
635 ,l_user_id
636 ,l_login_id
637 ,l_sys_date
638 ,l_user_id
639 ,l_source
640 ,l_source|| ' '||l_claim_rec.claim_id
641 --,'Y'
642 ,l_payment_method
643 ,l_claim_rec.gl_date
644 ,l_accts_pay_code_comb_id
645 ,l_claim_rec.org_id
646 ,l_claim_rec.legal_entity_id
647 ,682
648 ,'OZF_CLAIMS_ALL'
649 ,l_claim_rec.claim_id
650 ,l_claim_rec.customer_ref_date
651 ,l_claim_rec.customer_ref_number
652 ,l_claim_rec.customer_reason
653 ,NULL
654 , 'Y'
655 , 'Y'
656 );
657
658 --Update payment information in Claim after invoice is been created.
659 UPDATE ozf_claims_all
660 SET payment_reference_id = l_invoice_id
661 , payment_reference_number = l_claim_number
662 , payment_reference_date = l_claim_rec.settled_date
663 , payment_status = 'INTERFACED'
664 WHERE claim_id = p_claim_id;
665 -- if invoice is already created, update payment_status and payment_reference_number only
666 ELSE -- [ if l_x_claim_number is NOT NULL ]
667 UPDATE ozf_claims_all
668 SET payment_status = 'INTERFACED'
669 , payment_reference_number = l_claim_number
670 , payment_reference_date = l_claim_rec.settled_date
671 , payment_reference_id = l_invoice_id
672 WHERE claim_id = p_claim_id;
673 END IF; -- BUG 4754076 END.
674
675 --Standard check of commit
676 IF FND_API.To_Boolean ( p_commit ) THEN
677 COMMIT WORK;
678 END IF;
679
680
681 -- Debug Message
682 IF OZF_DEBUG_LOW_ON THEN
683 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
684 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
685 FND_MSG_PUB.Add;
686 END IF;
687
688 --Standard call to get message count and if count=1, get the message
689 FND_MSG_PUB.Count_And_Get (
690 p_encoded => FND_API.G_FALSE,
691 p_count => x_msg_count,
692 p_data => x_msg_data
693 );
694 EXCEPTION
695 WHEN FND_API.G_EXC_ERROR THEN
696 ROLLBACK TO Create_ap_invoice_PVT;
697 x_return_status := FND_API.G_RET_STS_ERROR;
698 -- Standard call to get message count and if count=1, get the message
699 FND_MSG_PUB.Count_And_Get (
700 p_encoded => FND_API.G_FALSE,
701 p_count => x_msg_count,
702 p_data => x_msg_data
703 );
704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
705 ROLLBACK TO Create_ap_invoice_PVT;
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 -- Standard call to get message count and if count=1, get the message
708 FND_MSG_PUB.Count_And_Get (
709 p_encoded => FND_API.G_FALSE,
710 p_count => x_msg_count,
711 p_data => x_msg_data
712 );
713 WHEN OTHERS THEN
714 ROLLBACK TO Create_ap_invoice_PVT;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
717 THEN
718 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
719 END IF;
720 -- Standard call to get message count and if count=1, get the message
721 FND_MSG_PUB.Count_And_Get (
722 p_encoded => FND_API.G_FALSE,
723 p_count => x_msg_count,
724 p_data => x_msg_data
725 );
726 END Create_ap_invoice;
727
728 END OZF_AP_INTERFACE_PVT;