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