[Home] [Help]
PACKAGE BODY: APPS.OZF_AP_INTERFACE_PVT
Source
1 PACKAGE BODY OZF_AP_INTERFACE_PVT AS
2 /* $Header: ozfvapib.pls 120.17.12020000.4 2012/07/25 05:27:49 bkunjan 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 ,SOURCE_OBJECT_CLASS
97 INTO
98 x_claim_rec.claim_id
99 ,x_claim_rec.claim_number
100 ,x_claim_rec.claim_type_id
101 ,x_claim_rec.claim_class
102 ,x_claim_rec.claim_date
103 ,x_claim_rec.due_date
104 ,x_claim_rec.amount
105 ,x_claim_rec.amount_adjusted
106 ,x_claim_rec.amount_remaining
107 ,x_claim_rec.amount_settled
108 ,x_claim_rec.acctd_amount
109 ,x_claim_rec.acctd_amount_remaining
110 ,x_claim_rec.tax_code
111 ,x_claim_rec.tax_calculation_flag
112 ,x_claim_rec.currency_code
113 ,x_claim_rec.exchange_rate_type
114 ,x_claim_rec.exchange_rate_date
115 ,x_claim_rec.exchange_rate
116 ,x_claim_rec.set_of_books_id
117 ,x_claim_rec.cust_account_id
118 ,x_claim_rec.cust_billto_acct_site_id
119 ,x_claim_rec.cust_shipto_acct_site_id
120 ,x_claim_rec.location_id
121 ,x_claim_rec.pay_related_account_flag
122 ,x_claim_rec.related_cust_account_id
123 ,x_claim_rec.related_site_use_id
124 ,x_claim_rec.relationship_type
125 ,x_claim_rec.vendor_id
126 ,x_claim_rec.vendor_site_id
127 ,x_claim_rec.reason_type
128 ,x_claim_rec.reason_code_id
129 ,x_claim_rec.status_code
130 ,x_claim_rec.customer_ref_date
131 ,x_claim_rec.customer_ref_number
132 ,x_claim_rec.gl_date
133 ,x_claim_rec.payment_method
134 ,x_claim_rec.payment_reference_id
135 ,x_claim_rec.payment_reference_number
136 ,x_claim_rec.payment_reference_date
137 ,x_claim_rec.payment_status
138 ,x_claim_rec.settled_date
139 ,x_claim_rec.effective_date
140 ,x_claim_rec.comments
141 ,x_claim_rec.org_id
142 ,x_claim_rec.legal_entity_id
143 ,x_claim_rec.source_object_class
144 FROM ozf_claims_all
145 WHERE claim_id = p_claim_id ;
146
147 x_return_status := FND_API.g_ret_sts_success;
148 EXCEPTION
149 WHEN OTHERS THEN
150 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
151 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_QUERY_ERROR');
152 FND_MSG_PUB.add;
153 END IF;
154 x_return_status := FND_API.g_ret_sts_unexp_error;
155 END Query_Claim;
156
157 PROCEDURE Create_ap_invoice (
158 p_api_version IN NUMBER
159 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
160 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
161 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
162
163 ,x_return_status OUT NOCOPY VARCHAR2
164 ,x_msg_data OUT NOCOPY VARCHAR2
165 ,x_msg_count OUT NOCOPY NUMBER
166
167 ,p_claim_id IN NUMBER
168 )
169 IS
170 l_api_name CONSTANT VARCHAR2(30) := 'Create_ap_invoice';
171 l_api_version CONSTANT NUMBER := 1.0;
172 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
173 --
174 l_user_id NUMBER;
175 l_login_id NUMBER;
176 l_sys_date DATE;
177
178
179 l_Error_Msg varchar2(2000);
180 l_Error_Token varchar2(80);
181
182 l_invoice_line_num number := 1;
183 l_invoice_id number;
184 l_invoice_line_id number;
185
186 --Fix for Bug 14001492
187 /*
188 l_reference VARCHAR2(240);
189
190 CURSOR csr_claim_ref_detail(cv_claim_id IN NUMBER) IS
191 SELECT customer_ref_number
192 FROM ozf_claims
193 WHERE claim_id = cv_claim_id;
194 */
195
196 CURSOR invoice_int_seq_csr IS
197 select ap_invoices_interface_s.nextval
198 from dual;
199
200 CURSOR invoice_line_int_seq_csr IS
201 select ap_invoice_lines_interface_s.nextval
202 from dual;
203
204 --Bug8531963: Commented CCID
205 CURSOR csr_system_param_details(cv_org_id IN NUMBER) IS
206 SELECT --ap.accts_pay_code_combination_id,
207 ozf.gl_id_ded_clearing,
208 ozf.payables_source,
209 ozf.ap_payment_term_id
210 FROM ap_system_parameters_all ap,
211 ozf_sys_parameters_all ozf
212 WHERE ozf.set_of_books_id = ap.set_of_books_id
213 AND ozf.org_id = cv_org_id;
214 l_accts_pay_code_comb_id NUMBER;
215 l_dist_code_comb_id NUMBER;
216 l_source VARCHAR2(30);
217 l_term_id NUMBER;
218
219 -- kishore
220
221 CURSOR Get_Invoicenum_csr(cv_invoice_num IN VARCHAR2, cv_vendor_id IN NUMBER) IS
222 select invoice_num
223 from AP_INVOICES
224 where INVOICE_NUM = cv_invoice_num
225 and VENDOR_ID = cv_vendor_id;
226
227 CURSOR csr_get_claim_lines(cv_claim_id IN NUMBER) IS
228 SELECT claim_currency_amount
229 , acctd_amount
230 , tax_code
231 , claim_line_id
232 , item_type
233 , item_id
234 , item_description
235 , source_object_class
236 , source_object_id
237 , activity_type
238 , activity_id
239 FROM ozf_claim_lines_all
240 WHERE claim_id = cv_claim_id;
241 l_claim_line_rec csr_get_claim_lines%ROWTYPE;
242
243 l_cc_id_tbl OZF_Gl_Interface_PVT.cc_id_tbl;
244 l_vendor_clearing_account NUMBER;
245 l_source_object_name varchar2(30);
246 l_account_type varchar2(30);
247
248 l_claim_rec OZF_Claim_PVT.claim_rec_type;
249 l_amount_settled NUMBER;
250
251 l_payment_method VARCHAR2(30);
252 l_claim_number VARCHAR2(30);
253 l_last_count NUMBER;
254
255 -- To derive line description
256 l_line_description VARCHAR2(2000);
257 l_item_type VARCHAR2(30);
258 l_final_descr VARCHAR2(240);
259
260 l_item_id NUMBER;
261 l_item_desc VARCHAR(2000);
262
263 -- kishore
264 l_x_claim_number VARCHAR2(50) := null;
265
266 CURSOR csr_item_desc(cv_item_id IN NUMBER) IS
267 SELECT description
268 FROM mtl_system_items_vl
269 WHERE inventory_item_id = cv_item_id;
270
271 CURSOR csr_category_desc(cv_category_id IN NUMBER) IS
272 SELECT SUBSTRB(category_desc,1,240)
273 FROM eni_prod_den_hrchy_parents_v
274 WHERE category_id = cv_category_id;
275
276 CURSOR csr_media_desc(cv_media_channel_id IN NUMBER) IS
277 SELECT channel_name
278 FROM ams_media_channels_vl
279 WHERE channel_id = cv_media_channel_id;
280
281 CURSOR csr_trx_number(cv_trx_id IN NUMBER) IS
282 SELECT trx_number
283 FROM ra_customer_trx_all
284 WHERE customer_trx_id = cv_trx_id;
285
286 CURSOR csr_order_number(cv_header_id IN NUMBER) IS
287 SELECT order_number
288 FROM oe_order_headers_all
289 WHERE header_id = cv_header_id;
290
291 CURSOR csr_request_number(cv_request_id IN NUMBER) IS
292 SELECT request_number
293 FROM ozf_request_headers_vl
294 WHERE request_header_id = cv_request_id;
295
296 CURSOR csr_offer_code(cv_list_id IN NUMBER) IS
297 SELECT offer_code
298 FROM ozf_offers
299 WHERE qp_list_header_id = cv_list_id;
300
301 CURSOR csr_type_meaning(cv_code IN VARCHAR2) IS
302 SELECT meaning
303 FROM ozf_lookups
304 WHERE lookup_type = 'OZF_LINE_OVER_TYPE'
305 AND lookup_code = cv_code;
306
307 CURSOR csr_item_type_meaning(cv_code IN VARCHAR2) IS
308 SELECT meaning
309 FROM ozf_lookups
310 WHERE lookup_type = 'OZF_CLAIM_ITEM_TYPE'
311 AND lookup_code = cv_code;
312
313 --Bug8531963
314 CURSOR csr_ven_sites(cv_vendor_site_id IN NUMBER) IS
315 SELECT accts_pay_code_combination_id
316 FROM po_vendor_sites
317 WHERE vendor_site_id = cv_vendor_site_id;
318
319 --Bug8531963
320 CURSOR csr_fin_sys_param(cv_org_id IN NUMBER) IS
321 SELECT accts_pay_code_combination_id
322 FROM financials_system_params_all
323 WHERE org_id = cv_org_id;
324
325 -- To derive terms id
326 /*CURSOR csr_fin_pay_method(cv_org_id IN NUMBER) IS
327 SELECT payment_method_lookup_code
328 FROM FINANCIALS_SYSTEM_PARAMS_ALL
329 WHERE org_id = cv_org_id;
330
331 CURSOR csr_vendor_pay_method(cv_vendor_id IN NUMBER) IS
332 SELECT payment_method_lookup_code
333 FROM po_vendors
334 WHERE vendor_id = cv_vendor_id;
335
336 CURSOR csr_vendor_site_pay_method(cv_vendor_site_id IN NUMBER) IS
337 SELECT payment_method_lookup_code
338 FROM po_vendor_sites
339 WHERE vendor_site_id = cv_vendor_site_id; */
340
341 -- Added for Bug 10198552
342 l_lookup_type_code VARCHAR2(30) := null;
343
344
345 --
346 BEGIN
347
348 -- Standard begin of API savepoint
349 SAVEPOINT Create_ap_invoice_PVT;
350
351
352 -- Standard call to check for call compatibility.
353 IF NOT FND_API.Compatible_API_Call (
354 l_api_version,
355 p_api_version,
356 l_api_name,
357 G_PKG_NAME)
358 THEN
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 END IF;
361
362
363 -- Debug Message
364 IF OZF_DEBUG_LOW_ON THEN
365 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
366 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
367 FND_MSG_PUB.Add;
368 END IF;
369
370
371 --Initialize message list if p_init_msg_list is TRUE.
372 IF FND_API.To_Boolean (p_init_msg_list) THEN
373 FND_MSG_PUB.initialize;
374 END IF;
375
376
377 -- Initialize API return status to sucess
378 x_return_status := FND_API.G_RET_STS_SUCCESS;
379
380 -- Query the claim
381 Query_Claim(
382 p_claim_id => p_claim_id
383 ,x_claim_rec => l_claim_rec
384 ,x_return_status => x_return_status);
385 IF x_return_status = FND_API.g_ret_sts_error THEN
386 RAISE FND_API.g_exc_error;
387 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
388 RAISE FND_API.g_exc_unexpected_error;
389 END IF;
390
391
392 l_user_id := NVL(FND_GLOBAL.user_id,-1);
393 l_login_id := NVL(FND_GLOBAL.conc_login_id,-1);
394 l_sys_date := SYSDATE;
395
396 -- Get Accts. Payables code combination id to populate Liability account
397 -- in Invoice Header.
398 --Bug8531963
399 OPEN csr_system_param_details( l_claim_rec.org_id);
400 FETCH csr_system_param_details INTO -- l_accts_pay_code_comb_id
401 l_dist_code_comb_id
402 , l_source
403 , l_term_id;
404 CLOSE csr_system_param_details;
405
406 --Bug8531963
407 OPEN csr_ven_sites(l_claim_rec.vendor_site_id);
408 FETCH csr_ven_sites INTO l_accts_pay_code_comb_id;
409 CLOSE csr_ven_sites;
410
411 --Bug8531963
412 IF l_accts_pay_code_comb_id IS NULL THEN
413 OPEN csr_fin_sys_param(l_claim_rec.org_id);
414 FETCH csr_fin_sys_param INTO l_accts_pay_code_comb_id;
415 CLOSE csr_fin_sys_param;
416 END IF;
417
418
419 IF l_claim_rec.payment_reference_number IS NULL THEN
420 l_claim_number := l_claim_rec.claim_number;
421 ELSE -- This is a retry of settlement
422 BEGIN
423 l_last_count := TO_NUMBER(SUBSTRB(l_claim_rec.payment_reference_number,
424 INSTRB(l_claim_rec.payment_reference_number,'.',-1,1)+1,
425 LENGTHB(l_claim_rec.payment_reference_number)));
426 l_claim_number := l_claim_rec.claim_number ||'.'||(l_last_count+1);
427 EXCEPTION
428 WHEN OTHERS THEN
429 l_claim_number := l_claim_rec.payment_reference_number ||'.'||1;
430 END;
431 END IF;
432
433 -- BUG 4754076 BEGIN
434 OPEN Get_Invoicenum_csr(l_claim_number, l_claim_rec.vendor_id);
435 FETCH Get_Invoicenum_csr INTO l_x_claim_number;
436 CLOSE Get_Invoicenum_csr;
437
438 -- If already exists do not insert record into Interface table else
439 -- verify if the invoice has already been created for this claim.
440
441 IF l_x_claim_number is NULL THEN
442
443 -- get invoice id from sequence
444 OPEN invoice_int_seq_csr;
445 FETCH invoice_int_seq_csr INTO l_invoice_id;
446 CLOSE invoice_int_seq_csr;
447
448 -- exchange rate to be populated only when the type is User
449 IF l_claim_rec.exchange_rate_type <> 'User' THEN
450 l_claim_rec.exchange_rate := null;
451 END IF;
452
453 IF l_claim_rec.payment_method IN ( 'CHECK','EFT','WIRE') THEN
454 l_payment_method := l_claim_rec.payment_method;
455 ELSE
456 NULL;
457 -- Payment Method derived by Payables.
458 END IF;
459
460 -- populate the invoice interface line for the claim
461 OPEN csr_get_claim_lines(l_claim_rec.claim_id);
462 LOOP
463 FETCH csr_get_claim_lines INTO l_claim_line_rec;
464 EXIT WHEN csr_get_claim_lines%NOTFOUND;
465
466 -- For Bug#13081196 fix
467 IF (l_claim_rec.payment_method = 'AP_DEBIT' AND SIGN(l_claim_rec.amount) <> -1) OR
468 (l_claim_rec.payment_method = 'AP_DEFAULT' AND SIGN(l_claim_rec.amount) = -1)
469 THEN
470 l_claim_line_rec.claim_currency_amount := l_claim_line_rec.claim_currency_amount * -1;
471 END IF;
472
473 -- Added for Bug 10198552
474 -- For Bug#13081196 fix ,for supplier ship and debit debit claim,debit memo should be generated
475 IF l_claim_rec.payment_method = 'AP_DEBIT' THEN
476 l_lookup_type_code := 'DEBIT';
477 END IF;
478
479 l_amount_settled := NVL(l_amount_settled,0) + l_claim_line_rec.claim_currency_amount ;
480
481 l_line_description := ''; --ninarasi fix for bug 14071276
482 l_item_type := ''; --ninarasi fix for bug 14071276
483
484 -- Derive Line Description
485 IF l_claim_line_rec.item_type in ( 'PRODUCT', 'FAMILY','MEDIA','ITEM') THEN
486 OPEN csr_item_type_meaning(NVL(l_claim_line_rec.source_object_class,l_claim_line_rec.item_type)); --ninarasi fix for bug 14071276
487 FETCH csr_item_type_meaning INTO l_item_type;
488 CLOSE csr_item_type_meaning;
489 l_final_descr := l_item_type || ':';
490 ELSIF l_claim_line_rec.source_object_class IS NOT NULL OR
491 l_claim_line_rec.activity_type IS NOT NULL THEN
492 OPEN csr_type_meaning(NVL(l_claim_line_rec.source_object_class,l_claim_line_rec.activity_type));
493 FETCH csr_type_meaning INTO l_item_type;
494 CLOSE csr_type_meaning;
495 l_final_descr := l_item_type || ':';
496 END IF;
497
498 IF l_claim_line_rec.item_type = 'PRODUCT' THEN
499 OPEN csr_item_desc(l_claim_line_rec.item_id);
500 FETCH csr_item_desc INTO l_line_description;
501 CLOSE csr_item_desc;
502 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
503 ELSIF l_claim_line_rec.item_type = 'FAMILY' THEN
504 OPEN csr_category_desc(l_claim_line_rec.item_id);
505 FETCH csr_category_desc INTO l_line_description;
506 CLOSE csr_category_desc;
507 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
508 ELSIF l_claim_line_rec.item_type = 'MEDIA' THEN
509 OPEN csr_media_desc(l_claim_line_rec.item_id);
510 FETCH csr_media_desc INTO l_line_description;
511 CLOSE csr_media_desc;
512 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
513 ELSIF l_claim_line_rec.source_object_class <> 'ORDER' THEN
514 OPEN csr_trx_number(l_claim_line_rec.source_object_id);
515 FETCH csr_trx_number INTO l_line_description;
516 CLOSE csr_trx_number;
517 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
518 ELSIF l_claim_line_rec.source_object_class = 'ORDER' THEN
519 OPEN csr_order_number(l_claim_line_rec.source_object_id);
520 FETCH csr_order_number INTO l_line_description;
521 CLOSE csr_order_number;
522 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
523 ELSIF l_claim_line_rec.activity_type = 'OFFR' THEN
524 OPEN csr_offer_code(l_claim_line_rec.activity_id);
525 FETCH csr_offer_code INTO l_line_description;
526 CLOSE csr_offer_code;
527 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
528 ELSIF l_claim_line_rec.activity_type in ('SOFT_FUND','SPECIAL_PRICE' ) THEN
529 OPEN csr_request_number(l_claim_line_rec.activity_id);
530 FETCH csr_request_number INTO l_line_description;
531 CLOSE csr_request_number;
532 l_final_descr := SUBSTRB(l_final_descr || l_line_description,1,240);
533 ELSE
534 l_final_descr := NVL(l_claim_line_rec.item_description, l_claim_rec.claim_number);
535 END IF;
536
537
538 -- get the vendor clearing account from gl api
539 OZF_Gl_Interface_PVT.Get_GL_Account(
540 p_api_version => p_api_version
541 ,p_init_msg_list => FND_API.G_FALSE
542 ,p_commit => FND_API.G_FALSE
543 ,p_validation_level => p_validation_level
544 ,x_return_status => x_return_status
545 ,x_msg_data => x_msg_data
546 ,x_msg_count => x_msg_count
547 ,p_source_id => l_claim_line_rec.claim_line_id
548 ,p_source_table => 'OZF_CLAIM_LINES_ALL'
549 ,p_account_type => 'VEN_CLEARING'
550 ,x_cc_id_tbl => l_cc_id_tbl);
551 IF x_return_status = FND_API.g_ret_sts_error THEN
552 RAISE FND_API.g_exc_error;
553 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
554 RAISE FND_API.g_exc_unexpected_error;
555 END IF;
556
557 FOR i in 1..l_cc_id_tbl.count LOOP
558 l_vendor_clearing_account := l_cc_id_tbl(i).code_combination_id;
559 END LOOP;
560
561
562 -- get invoice line id from sequence
563 OPEN invoice_line_int_seq_csr;
564 FETCH invoice_line_int_seq_csr INTO l_invoice_line_id;
565 CLOSE invoice_line_int_seq_csr;
566
567 IF l_claim_line_rec.item_type <> 'PRODUCT' THEN --ninarasi fix for bug 14071276
568 l_item_id := NULL;
569 ELSE
570 l_item_id := l_claim_line_rec.item_id;
571 END IF;
572
573
574 INSERT INTO AP_INVOICE_LINES_INTERFACE (
575 INVOICE_ID
576 ,INVOICE_LINE_ID
577 ,LINE_NUMBER
578 ,LINE_TYPE_LOOKUP_CODE
579 ,AMOUNT
580 ,ACCOUNTING_DATE
581 ,TAX_CLASSIFICATION_CODE
582 ,LAST_UPDATED_BY
583 ,LAST_UPDATE_DATE
584 ,LAST_UPDATE_LOGIN
585 ,CREATED_BY
586 ,CREATION_DATE
587 ,DIST_CODE_COMBINATION_ID
588 ,ORG_ID
589 ,DESCRIPTION
590 ,APPLICATION_ID
591 ,PRODUCT_TABLE
592 ,REFERENCE_KEY1
593 ,REFERENCE_KEY2
594 ,REFERENCE_KEY3
595 ,REFERENCE_KEY4
596 ,REFERENCE_KEY5
597 ,SOURCE_APPLICATION_ID
598 ,SOURCE_ENTITY_CODE
599 ,SOURCE_EVENT_CLASS_CODE
600 ,INVENTORY_ITEM_ID --Fix for bug # 8576443
601 ,ITEM_DESCRIPTION -- Fix for Bug#8885844
602 )
603 VALUES (
604 l_invoice_id
605 ,l_invoice_line_id
606 ,l_invoice_line_num
607 ,'ITEM'
608 ,l_claim_line_rec.claim_currency_amount
609 ,l_claim_rec.gl_date
610 ,l_claim_line_rec.tax_code
611 ,l_user_id
612 ,l_sys_date
613 ,l_login_id
614 ,l_user_id
615 ,l_sys_date
616 ,l_vendor_clearing_account
617 ,l_claim_rec.org_id
618 ,l_final_descr
619 ,682
620 ,'OZF_CLAIMS_ALL'
621 ,l_claim_rec.claim_id
622 ,l_claim_rec.customer_ref_date
623 ,l_claim_rec.customer_ref_number
624 ,l_claim_rec.customer_reason
625 ,l_claim_line_rec.claim_line_id
626 ,682
627 ,'OZF_CLAIMS'
628 , 'TRADE_MGT_PAYABLES'
629 , l_item_id --Fix for bug # 8576443 --ninarasi fix for bug 14071276
630 , l_claim_line_rec.item_description -- Fix for Bug#8885844
631 );
632 l_invoice_line_num := l_invoice_line_num + 1;
633 END LOOP;
634 CLOSE csr_get_claim_lines;
635
636 --Fix for Bug 14001492
637 /*
638 -- Added For Bug 7384640
639 OPEN csr_claim_ref_detail(p_claim_id);
640 FETCH csr_claim_ref_detail INTO l_reference;
641 CLOSE csr_claim_ref_detail;
642
643 IF l_reference IS NOT NULL THEN
644 l_claim_number := l_reference;
645 END IF;
646 */
647
648 IF (l_claim_rec.customer_ref_number IS NOT NULL) THEN
649 IF l_claim_rec.payment_reference_number IS NULL THEN
650 l_claim_number := l_claim_rec.customer_ref_number;
651 ELSE -- This is a retry of settlement
652 BEGIN
653 l_last_count := TO_NUMBER(SUBSTRB(l_claim_rec.payment_reference_number,
654 INSTRB(l_claim_rec.payment_reference_number,'.',-1,1)+1,
655 LENGTHB(l_claim_rec.payment_reference_number)));
656 l_claim_number := l_claim_rec.customer_ref_number ||'.'||(l_last_count+1);
657 EXCEPTION
658 WHEN OTHERS THEN
659 l_claim_number := l_claim_rec.payment_reference_number ||'.'||1;
660 END;
661 END IF;
662 END IF;
663
664 -- Inserting an Invoice header record into AP_INVOICE_INTERFACE table.
665 INSERT INTO AP_INVOICES_INTERFACE (
666 INVOICE_ID
667 , INVOICE_NUM
668 , INVOICE_DATE
669 , VENDOR_ID
670 , VENDOR_SITE_ID
671 , INVOICE_AMOUNT
672 , INVOICE_CURRENCY_CODE
673 , EXCHANGE_RATE
674 , EXCHANGE_RATE_TYPE
675 , EXCHANGE_DATE
676 , TERMS_ID
677 , DESCRIPTION
678 , LAST_UPDATE_DATE
679 , LAST_UPDATED_BY
680 , LAST_UPDATE_LOGIN
681 , CREATION_DATE
682 , CREATED_BY
683 , SOURCE
684 , GROUP_ID
685 --, WORKFLOW_FLAG
686 , PAYMENT_METHOD_CODE
687 , GL_DATE
688 , ACCTS_PAY_CODE_COMBINATION_ID
689 , ORG_ID
690 , LEGAL_ENTITY_ID
691 , APPLICATION_ID
692 , PRODUCT_TABLE
693 , REFERENCE_KEY1
694 , REFERENCE_KEY2
695 , REFERENCE_KEY3
696 , REFERENCE_KEY4
697 , REFERENCE_KEY5
698 ,CALC_TAX_DURING_IMPORT_FLAG
699 ,ADD_TAX_TO_INV_AMT_FLAG
700 ,invoice_type_lookup_code
701 )
702 VALUES (
703 l_invoice_id
704 ,l_claim_number
705 ,l_claim_rec.settled_date
706 ,l_claim_rec.vendor_id
707 ,l_claim_rec.vendor_site_id
708 ,l_amount_settled
709 ,l_claim_rec.currency_code
710 ,l_claim_rec.exchange_rate
711 ,l_claim_rec.exchange_rate_type
712 ,l_claim_rec.exchange_rate_date
713 ,l_term_id
714 ,l_claim_rec.customer_ref_number
715 ,l_sys_date
716 ,l_user_id
717 ,l_login_id
718 ,l_sys_date
719 ,l_user_id
720 ,l_source
721 ,l_source|| ' '||l_claim_rec.claim_id
722 --,'Y'
723 ,l_payment_method
724 ,l_claim_rec.gl_date
725 ,l_accts_pay_code_comb_id
726 ,l_claim_rec.org_id
727 ,l_claim_rec.legal_entity_id
728 ,682
729 ,'OZF_CLAIMS_ALL'
730 ,l_claim_rec.claim_id
731 ,l_claim_rec.customer_ref_date
732 ,l_claim_rec.customer_ref_number
733 ,l_claim_rec.customer_reason
734 ,NULL
735 , 'Y'
736 , 'Y'
737 -- Added for Bug 10198552
738 , l_lookup_type_code
739 );
740
741 --Update payment information in Claim after invoice is been created.
742 UPDATE ozf_claims_all
743 SET payment_reference_id = l_invoice_id
744 , payment_reference_number = l_claim_number
745 , payment_reference_date = l_claim_rec.settled_date
746 , payment_status = 'INTERFACED'
747 WHERE claim_id = p_claim_id;
748 -- if invoice is already created, update payment_status and payment_reference_number only
749 ELSE -- [ if l_x_claim_number is NOT NULL ]
750 UPDATE ozf_claims_all
751 SET payment_status = 'INTERFACED'
752 , payment_reference_number = l_claim_number
753 , payment_reference_date = l_claim_rec.settled_date
754 , payment_reference_id = l_invoice_id
755 WHERE claim_id = p_claim_id;
756 END IF; -- BUG 4754076 END.
757
758 --Standard check of commit
759 IF FND_API.To_Boolean ( p_commit ) THEN
760 COMMIT WORK;
761 END IF;
762
763
764 -- Debug Message
765 IF OZF_DEBUG_LOW_ON THEN
766 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
767 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
768 FND_MSG_PUB.Add;
769 END IF;
770
771 --Standard call to get message count and if count=1, get the message
772 FND_MSG_PUB.Count_And_Get (
773 p_encoded => FND_API.G_FALSE,
774 p_count => x_msg_count,
775 p_data => x_msg_data
776 );
777 EXCEPTION
778 WHEN FND_API.G_EXC_ERROR THEN
779 ROLLBACK TO Create_ap_invoice_PVT;
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 -- Standard call to get message count and if count=1, get the message
782 FND_MSG_PUB.Count_And_Get (
783 p_encoded => FND_API.G_FALSE,
784 p_count => x_msg_count,
785 p_data => x_msg_data
786 );
787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
788 ROLLBACK TO Create_ap_invoice_PVT;
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 -- Standard call to get message count and if count=1, get the message
791 FND_MSG_PUB.Count_And_Get (
792 p_encoded => FND_API.G_FALSE,
793 p_count => x_msg_count,
794 p_data => x_msg_data
795 );
796 WHEN OTHERS THEN
797 ROLLBACK TO Create_ap_invoice_PVT;
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
800 THEN
801 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
802 END IF;
803 -- Standard call to get message count and if count=1, get the message
804 FND_MSG_PUB.Count_And_Get (
805 p_encoded => FND_API.G_FALSE,
806 p_count => x_msg_count,
807 p_data => x_msg_data
808 );
809 END Create_ap_invoice;
810
811 END OZF_AP_INTERFACE_PVT;