[Home] [Help]
PACKAGE BODY: APPS.PO_CREATE_ASBN_INVOICE
Source
1 PACKAGE BODY PO_CREATE_ASBN_INVOICE AS
2 /* $Header: POXBNIVB.pls 120.7.12020000.2 2012/07/19 08:16:12 asugandh ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
6
7 /** Local Procedure Definition **/
8
9 PROCEDURE create_invoice_header (
10 p_invoice_id IN NUMBER,
11 p_invoice_num IN VARCHAR2,
12 p_invoice_date IN DATE,
13 p_vendor_id IN NUMBER,
14 p_vendor_site_id IN NUMBER,
15 p_invoice_amount IN NUMBER,
16 p_invoice_currency_code IN VARCHAR2,
17 p_payment_terms_id IN NUMBER,
18 p_exchange_rate IN NUMBER,
19 p_exchange_rate_type IN VARCHAR2,
20 p_exchange_date IN DATE,
21 p_org_id IN NUMBER );
22
23 /* Bug 7004065
24 * Invoice creation fails due to rounding amount difference between Invoice headers
25 * and Invoice lines. Invoice Header amount is user entered amount in iSupplier portal/
26 * the value provided in the rcv_headers_interface. This value can be provided either in
27 * rounded value or unrounded value. But, Invoice lines amount is a derived value by
28 * the code and it is posted as unrounded value.
29 * Issue occurs, when user is populating the rounded value for Invoice header and the
30 * the value in Invoice lines is going as unrounded and results in AP rejection due to
31 * mismatch in Header invoice amount and Lines invoice amount.
32 * We have to post the rounded values(using AP api) to Invoice Headers and
33 * Invoice Lines.
34 */
35
36 PROCEDURE create_invoice_line (
37 p_invoice_id IN NUMBER,
38 p_line_type IN VARCHAR2,
39 p_amount IN NUMBER,
40 p_invoice_currency_code IN VARCHAR2, --Bug 7004065
41 p_invoice_date IN DATE,
42 p_po_header_id IN NUMBER,
43 p_po_line_id IN NUMBER,
44 p_po_line_location_id IN NUMBER,
45 p_po_release_id IN NUMBER,
46 p_uom IN VARCHAR2,
47 p_item_id IN NUMBER,
48 p_item_description IN VARCHAR2,
49 p_qty_invoiced IN NUMBER,
50 p_ship_to_location_id IN NUMBER,
51 p_unit_price IN NUMBER,
52 p_org_id IN NUMBER,
53 p_taxable_flag IN VARCHAR2,
54 p_tax_code IN VARCHAR2,
55 p_tax_classification_code IN VARCHAR2 );
56
57
58
59
60 FUNCTION create_asbn_invoice (
61 p_commit_interval IN NUMBER,
62 p_shipment_header_id IN NUMBER )
63 RETURN BOOLEAN IS
64
65 CURSOR c_asbn_header IS
66 select
67 rsh.invoice_num,
68 rsh.invoice_date,
69 rsh.vendor_id,
70 NVL(rsh.remit_to_site_id, NVL(pvss.default_pay_site_id, pvss.vendor_site_id)) default_pay_site_id,
71 rsh.invoice_amount,
72 rsh.tax_name,
73 rsh.tax_amount,
74 rsh.freight_amount,
75 NVL(rsh.currency_code, poh.currency_code) currency_code,
76 NVL(rsh.payment_terms_id, poh.terms_id) payment_terms_id,
77 NVL(rsh.conversion_rate_type, poh.rate_type) exchange_rate_type,
78 NVL(rsh.conversion_date, poh.rate_date) exchange_rate_date,
79 NVL(rsh.conversion_rate, poh.rate) exchange_rate,
80 poh.org_id
81 from
82 po_vendor_sites pvss,
83 po_headers poh,
84 rcv_shipment_headers rsh
85 where
86 poh.vendor_site_id = pvss.vendor_site_id and
87 poh.pcard_id is null and
88 rsh.receipt_source_code = 'VENDOR' and
89 rsh.asn_type = 'ASBN' and
90 NVL(rsh.invoice_status_code, 'PENDING') IN ('PENDING', 'REJECTED') and
91 rsh.shipment_header_id = p_shipment_header_id and
92 poh.po_header_id = (
93 select rsl.po_header_id
94 from rcv_shipment_lines rsl
95 where rsl.shipment_header_id = rsh.shipment_header_id
96 and rownum = 1 );
97
98
99 CURSOR c_asbn_line IS
100 select
101 rsl.po_header_id,
102 rsl.po_line_id,
103 rsl.po_line_location_id,
104 rsl.po_release_id,
105 rsl.shipment_line_id,
106 pol.item_id,
107 pol.item_description,
108 pol.unit_meas_lookup_code, --1890025
109 rsl.unit_of_measure,
110 rsl.quantity_shipped,
111 rsl.notice_unit_price,
112 NVL(rsl.notice_unit_price, pll.price_override) unit_price,
113 pll.taxable_flag,
114 rsl.tax_name tax_name,
115 DECODE(pll.taxable_flag, 'Y', pll.tax_code_id, NULL) tax_code_id,
116 rsl.tax_amount,
117 pll.match_option
118 from
119 po_line_locations pll,
120 po_lines pol,
121 rcv_shipment_lines rsl
122 where
123 rsl.shipment_header_id = p_shipment_header_id and
124 pll.line_location_id = rsl.po_line_location_id and
125 pol.po_line_id = rsl.po_line_id and
126 NVL(rsl.invoice_status_code, 'PENDING') IN ('PENDING','REJECTED');
127
128 X_asbn_header c_asbn_header%ROWTYPE;
129 X_asbn_line c_asbn_line%ROWTYPE;
130 l_invoice_id NUMBER;
131 l_converted_qty NUMBER;
132 l_po_amount NUMBER;
133 l_taxable BOOLEAN := false;
134 l_temp_result BOOLEAN := true;
135
136 x_group_id VARCHAR2(80);
137 x_batch_id NUMBER;
138 x_req_id NUMBER;
139
140 X_curr_inv_process_flag VARCHAR2(1);
141
142 /* <PAY ON USE FPI START> */
143 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
144 l_error_msg VARCHAR2(2000);
145 /* <PAY ON USE FPI END> */
146
147 l_ship_to_location_id PO_LINE_LOCATIONS.SHIP_TO_LOCATION_ID%TYPE;
148 l_tax_classification_code VARCHAR2(30);
149 BEGIN
150
151 OPEN c_asbn_header;
152 LOOP
153 FETCH c_asbn_header INTO X_asbn_header;
154 EXIT WHEN c_asbn_header%NOTFOUND;
155
156 if ((X_asbn_header.invoice_num is not null) and
157 (X_asbn_header.invoice_date is not null)) then
158
159 select ap_invoices_interface_s.nextval
160 into l_invoice_id
161 from sys.dual;
162
163 -- Bug 4723269 : Pass org_id to create_ap_batches
164
165 po_invoices_sv1.create_ap_batches( 'ASBN',
166 X_asbn_header.currency_code,
167 X_asbn_header.org_id,
168 X_batch_id);
169
170 create_invoice_header (
171 l_invoice_id,
172 X_asbn_header.invoice_num,
173 X_asbn_header.invoice_date,
174 X_asbn_header.vendor_id,
175 X_asbn_header.default_pay_site_id,
176 X_asbn_header.invoice_amount,
177 X_asbn_header.currency_code,
178 X_asbn_header.payment_terms_id,
179 X_asbn_header.exchange_rate,
180 X_asbn_header.exchange_rate_type,
181 X_asbn_header.exchange_rate_date,
182 X_asbn_header.org_id );
183
184 x_group_id := substr('ASBN-'||TO_CHAR(l_invoice_id),1,80);
185
186 OPEN c_asbn_line;
187 LOOP
188 FETCH c_asbn_line INTO X_asbn_line;
189 EXIT WHEN c_asbn_line%NOTFOUND;
190
191 If (X_asbn_line.match_option = 'R') then
192 po_interface_errors_sv1.handle_interface_errors(
193 'ASBN',
194 'FATAL',
195 X_batch_id, -- batch_id
196 p_shipment_header_id,
197 NULL, -- line_id
198 'PO_INV_CR_INVALID_MATCH_OPTION',
199 'PO_LINE_LOCATIONS', -- table_name
200 'MATCH_OPTION', -- column_name
201 null,
202 null, null, null, null, null,
203 null,
204 null, null, null, null, null,
205 X_curr_inv_process_flag);
206
207 l_temp_result := false;
208 end if;
209
210 -- Calculate converted PO amount
211 if (X_asbn_line.notice_unit_price is not null) then
212 l_po_amount := X_asbn_line.quantity_shipped *
213 X_asbn_line.notice_unit_price;
214 else
215 if (X_asbn_line.unit_meas_lookup_code <>
216 X_asbn_line.unit_of_measure) then
217 po_uom_s.uom_convert (
218 X_asbn_line.quantity_shipped,
219 X_asbn_line.unit_of_measure,
220 X_asbn_line.item_id,
221 X_asbn_line.unit_meas_lookup_code,
222 l_converted_qty );
223 else
224 l_converted_qty := X_asbn_line.quantity_shipped;
225 end if;
226
227 l_po_amount := l_converted_qty * X_asbn_line.unit_price;
228 end if;
229
230
231 if (l_temp_result) then
232 l_ship_to_location_id := PO_INVOICES_SV2.get_ship_to_location_id(
233 X_asbn_line.po_line_location_id);
234
235 l_tax_classification_code := PO_INVOICES_SV2.get_tax_classification_code(
236 X_asbn_line.po_header_id,
237 X_asbn_line.po_line_location_id,
238 'PURCHASE_ORDER');
239 create_invoice_line (
240 l_invoice_id,
241 'ITEM',
242 l_po_amount,
243 X_asbn_header.currency_code, --Bug 7004065
244 X_asbn_header.invoice_date,
245 X_asbn_line.po_header_id,
246 X_asbn_line.po_line_id,
247 X_asbn_line.po_line_location_id,
248 X_asbn_line.po_release_id,
249 X_asbn_line.unit_meas_lookup_code,
250 X_asbn_line.item_id,
251 X_asbn_line.item_description,
252 l_converted_qty,
253 l_ship_to_location_id,
254 X_asbn_line.unit_price,
255 X_asbn_header.org_id,
256 X_asbn_line.taxable_flag,
257 null, --tax_code
258 l_tax_classification_code );
259
260 if ((not l_taxable) and X_asbn_line.taxable_flag = 'Y') then
261 l_taxable := true;
262 end if;
263
264 -- After creating line interface, change result to true
265 l_temp_result := true;
266 end if;
267
268 END LOOP;
269 CLOSE c_asbn_line;
270
271 if (l_taxable and nvl(X_asbn_header.tax_amount, 0) > 0) then
272
273 /* Bug 5107497: As per eTax project, no need to populate Tax lines in
274 ap_invoice_lines_table. We have to populate control_amount
275 with tax amount specified in ASBN and set calc_tax_during_import_flag
276 to 'Y' in ap_invoices_interface table. */
277 UPDATE ap_invoices_interface
278 SET calc_tax_during_import_flag = 'Y',
279 control_amount = X_asbn_header.tax_amount
280 WHERE invoice_id = l_invoice_id;
281
282 end if;
283
284 if (nvl(X_asbn_header.freight_amount, 0) > 0) then
285 create_invoice_line (
286 l_invoice_id,
287 'FREIGHT',
288 X_asbn_header.freight_amount,
289 X_asbn_header.currency_code, --Bug 7004065
290 X_asbn_header.invoice_date,
291 null,
292 null,
293 null,
294 null,
295 null, -- uom
296 null, -- item_id
297 null, -- item_description,
298 null, -- qty_to_invoice,
299 null, -- ship_to_location_code,
300 null, -- unit_price,
301 X_asbn_header.org_id,
302 null, -- taxable_flag
303 null,
304 null --tax classification code
305 );
306 end if;
307
308 /* Bug 12631722: Upon ASBN creation, RSH and RSL have to be set as
309 * INVOICED */
310
311 update rcv_shipment_headers
312 set invoice_status_code = 'INVOICED'
313 where shipment_header_id = p_shipment_header_id;
314
315 update rcv_shipment_lines
316 set invoice_status_code = 'INVOICED'
317 where shipment_header_id = p_shipment_header_id;
318
319 end if;
320 END LOOP;
321 CLOSE c_asbn_header;
322
323 if (l_temp_result and p_commit_interval <> -1) then
324 COMMIT;
325 end if;
326
327 IF (l_temp_result and x_group_id is NOT NULL) THEN
328
329 /* <PAY ON USE FPI START> */
330 /* fnd request submit request has been replaced by
331 PO_INVOICES_SV1.submit_invoice_import as a result of refactoring
332 performed during FPI Consigned Inv project */
333
334 PO_INVOICES_SV1.submit_invoice_import(
335 l_return_status,
336 'ASBN',
337 x_group_id,
338 x_batch_id,
339 0,
340 0,
341 x_req_id);
342
343 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346 /* <PAY ON USE FPI END> */
347
348 END IF;
349
350 return l_temp_result;
351
352 EXCEPTION
353 /* <PAY ON USE FPI START> */
354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
355 l_error_msg := FND_MSG_PUB.get(p_encoded => 'F');
356 IF (g_asn_debug = 'Y') THEN
357 ASN_DEBUG.put_line(l_error_msg);
358 END IF;
359 IF c_asbn_line%ISOPEN THEN
360 CLOSE c_asbn_line;
361 END IF;
362 IF c_asbn_header%ISOPEN THEN
363 CLOSE c_asbn_header;
364 END IF;
365 RETURN FALSE;
366 /* <PAY ON USE FPI END> */
367
368 WHEN OTHERS THEN
369 if c_asbn_line%isopen then
370 close c_asbn_line;
371 end if;
372 if c_asbn_header%isopen then
373 close c_asbn_header;
374 end if;
375 return false;
376 END create_asbn_invoice;
377
378
379 PROCEDURE create_invoice_header (
380 p_invoice_id IN NUMBER,
381 p_invoice_num IN VARCHAR2,
382 p_invoice_date IN DATE,
383 p_vendor_id IN NUMBER,
384 p_vendor_site_id IN NUMBER,
385 p_invoice_amount IN NUMBER,
386 p_invoice_currency_code IN VARCHAR2,
387 p_payment_terms_id IN NUMBER,
388 p_exchange_rate IN NUMBER,
389 p_exchange_rate_type IN VARCHAR2,
390 p_exchange_date IN DATE,
391 p_org_id IN NUMBER )
392 IS
393
394 x_group_id VARCHAR2(80);
395 l_invoice_amount NUMBER; --Bug 7004065
396
397 BEGIN
398
399 x_group_id := substr('ASBN-'||TO_CHAR(p_invoice_id),1,80);
400
401 l_invoice_amount := ap_utilities_pkg.ap_round_currency(p_invoice_amount,p_invoice_currency_code); --Bug 7004065
402
403 insert into AP_INVOICES_INTERFACE (
404 INVOICE_ID,
405 INVOICE_NUM,
406 INVOICE_DATE,
407 VENDOR_ID,
408 VENDOR_SITE_ID,
409 INVOICE_AMOUNT,
410 INVOICE_CURRENCY_CODE,
411 --EXCHANGE_RATE,
412 --EXCHANGE_RATE_TYPE,
413 --EXCHANGE_DATE,
414 TERMS_ID,
415 GROUP_ID,
416 SOURCE,
417 INVOICE_RECEIVED_DATE,
418 CREATION_DATE,
419 ORG_ID )
420 VALUES (
421 p_invoice_id,
422 p_invoice_num,
423 p_invoice_date,
424 p_vendor_id,
425 p_vendor_site_id,
426 l_invoice_amount, --Bug 7004065
427 p_invoice_currency_code,
428 --p_exchange_rate,
429 --p_exchange_rate_type,
430 --p_exchange_date,
431 p_payment_terms_id,
432 x_group_id,
433 'ASBN',
434 sysdate,
435 sysdate,
436 p_org_id );
437
438 EXCEPTION
439 WHEN others THEN
440 raise;
441 END create_invoice_header;
442
443
444 PROCEDURE create_invoice_line (
445 p_invoice_id IN NUMBER,
446 p_line_type IN VARCHAR2,
447 p_amount IN NUMBER,
448 p_invoice_currency_code IN VARCHAR2, --Bug 7004065
449 p_invoice_date IN DATE,
450 p_po_header_id IN NUMBER,
451 p_po_line_id IN NUMBER,
452 p_po_line_location_id IN NUMBER,
453 p_po_release_id IN NUMBER,
454 p_uom IN VARCHAR2,
455 p_item_id IN NUMBER,
456 p_item_description IN VARCHAR2,
457 p_qty_invoiced IN NUMBER,
458 p_ship_to_location_id IN NUMBER,
459 p_unit_price IN NUMBER,
460 p_org_id IN NUMBER,
461 p_taxable_flag IN VARCHAR2,
462 p_tax_code IN VARCHAR2,
463 p_tax_classification_code IN VARCHAR2 )
464 IS
465
466 x_invoice_line_id NUMBER;
467 l_prorate_across_flag VARCHAR2(1) := null;
468 l_freight_ccid NUMBER := 0;
469 sqlstmt_old VARCHAR2(1000);
470 sqlstmt_new VARCHAR2(4000);
471 X_use_interface VARCHAR2(1) := null;
472 l_amount NUMBER; --Bug 7004065
473
474 BEGIN
475
476 select ap_invoice_lines_interface_s.nextval
477 into x_invoice_line_id
478 from sys.dual;
479
480 if (p_line_type = 'ITEM' or p_line_type = 'TAX') then
481 l_prorate_across_flag := 'N';
482
483 elsif (p_line_type = 'FREIGHT') then
484 select freight_code_combination_id
485 into l_freight_ccid
486 from ap_system_parameters_all
487 where org_id = p_org_id;
488
489 if (l_freight_ccid <> 0) then
490 l_prorate_across_flag := 'N';
491 else
492 l_prorate_across_flag := 'Y';
493 end if;
494
495 end if;
496
497 l_amount := ap_utilities_pkg.ap_round_currency(p_amount,p_invoice_currency_code); --Bug 7004065
498
499 insert into ap_invoice_lines_interface (
500 INVOICE_ID,
501 INVOICE_LINE_ID,
502 LINE_NUMBER,
503 LINE_TYPE_LOOKUP_CODE,
504 AMOUNT,
505 AMOUNT_INCLUDES_TAX_FLAG,
506 PRORATE_ACROSS_FLAG,
507 PO_HEADER_ID,
508 PO_LINE_ID,
509 PO_LINE_LOCATION_ID,
510 PO_RELEASE_ID,
511 UNIT_OF_MEAS_LOOKUP_CODE, --PO_UNIT_OF_MEASURE, bug 12370070, as ap team has obsoleted PO_UNIT_OF_MEASURE
512 INVENTORY_ITEM_ID,
513 ITEM_DESCRIPTION,
514 QUANTITY_INVOICED,
515 SHIP_TO_LOCATION_ID,
516 UNIT_PRICE,
517 LAST_UPDATE_DATE,
518 CREATION_DATE,
519 ORG_ID,
520 TAXABLE_FLAG,
521 TAX_CODE_OVERRIDE_FLAG,
522 TAX_CODE,
523 TAX_CLASSIFICATION_CODE
524 )
525 VALUES (
526 p_invoice_id,
527 x_invoice_line_id,
528 null,
529 p_line_type,
530 l_amount, -- Bug 7004065
531 null,
532 l_prorate_across_flag,
533 p_po_header_id,
534 p_po_line_id,
535 p_po_line_location_id,
536 p_po_release_id,
537 p_uom,
538 p_item_id,
539 p_item_description,
540 p_qty_invoiced,
541 p_ship_to_location_id,
542 p_unit_price,
543 sysdate,
544 sysdate,
545 p_org_id,
546 p_taxable_flag,
547 'N',
548 p_tax_code,
549 p_tax_classification_code
550 );
551
552 EXCEPTION
553 WHEN OTHERS THEN
554 raise;
555 END create_invoice_line;
556
557
558 END PO_CREATE_ASBN_INVOICE;