[Home] [Help]
PACKAGE BODY: APPS.PO_INVOICES_SV2
Source
1 PACKAGE BODY po_invoices_sv2 AS
2 /* $Header: POXIVRPB.pls 120.26.12010000.8 2009/01/22 13:28:40 sadibhat 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 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('RCV_DEBUG_MODE'),'N'); --Bug 5197828
7
8 create_invoice_error EXCEPTION; --SBI
9
10
11 /* <PAY ON USE FPI START> */
12 g_fetch_size CONSTANT NUMBER := 1000;
13 g_pkg_name CONSTANT VARCHAR2(20) := 'PO_INVOICES_SV2';
14
15 /* For caching values */
16 g_old_tax_code_id AP_TAX_CODES.tax_id%TYPE := NULL;
17 /* <PAY ON USE FPI END> */
18
19
20 /* =================================================================
21 FUNCTION NAME: get_ship_to_location_id(p_trx_id,p_entity_code)
22 p_po_line_location_id = po line location id for which we require the ship to
23 location.
24 Bug: 5125624
25 ==================================================================*/
26
27 FUNCTION get_ship_to_location_id (p_po_line_location_id IN NUMBER)
28 RETURN PO_LINE_LOCATIONS.SHIP_TO_LOCATION_ID%TYPE IS
29 l_ship_to_location_id PO_LINE_LOCATIONS.SHIP_TO_LOCATION_ID%TYPE;
30 BEGIN
31
32 SELECT
33 ship_to_location_id INTO l_ship_to_location_id
34 FROM
35 po_line_locations_all
36 WHERE
37 line_location_id = p_po_line_location_id;
38 IF (g_asn_debug = 'Y') THEN
39 asn_debug.put_line('po_line_location_id = ' || p_po_line_location_id || ' has ship to location id = ' || l_ship_to_location_id);
40 END IF;
41 RETURN l_ship_to_location_id;
42 EXCEPTION
43 WHEN NO_DATA_FOUND THEN
44 IF (g_asn_debug = 'Y') THEN
45 asn_debug.put_line('po_line_location_id = ' || p_po_line_location_id || ' has no ship to location id');
46 END IF;
47 RETURN NULL;
48 WHEN OTHERS THEN
49 IF (g_asn_debug = 'Y') THEN
50 asn_debug.put_line('po_line_location_id = ' || p_po_line_location_id || ' has some problem. This is not acceptable.');
51 END IF;
52 RETURN NULL;
53
54 END get_ship_to_location_id;
55
56 /* =================================================================
57
58 FUNCTION NAME: get_tax_classification_code(p_trx_id,p_entity_code)
59 p_trx_id = Is the id that is present in the zx tables. In case of
60 PO it is the po_header_id
61 p_trx_line_id = is the line location id of the PO.
62 entity_code= zx tables stores the trx_id and the entity code to avoid
63 multiple records with same trx_id. In case we are passing
64 po_header_id then the entity_code would be 'PURCHASE ORDER'
65
66 Bug: 5125624
67 ==================================================================*/
68
69 FUNCTION get_tax_classification_code (p_trx_id IN NUMBER,
70 p_trx_line_id IN NUMBER,
71 p_entity_code IN VARCHAR)
72 RETURN VARCHAR2 IS
73 l_tax_classification_code zx_lines_det_factors.input_tax_classification_code%TYPE;
74 BEGIN
75
76 SELECT
77 input_tax_classification_code INTO l_tax_classification_code
78 FROM
79 zx_lines_det_factors
80 WHERE
81 trx_id = p_trx_id
82 AND trx_line_id = p_trx_line_id
83 AND entity_code = p_entity_code;
84 IF (g_asn_debug = 'Y') THEN
85 asn_debug.put_line(p_entity_code || '=' || p_trx_id || ' has a tax classification code = ' || l_tax_classification_code);
86 END IF;
87 RETURN l_tax_classification_code;
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90 IF (g_asn_debug = 'Y') THEN
91 asn_debug.put_line(p_entity_code || '=' || p_trx_id || ' has no tax classification code');
92 END IF;
93 RETURN NULL;
94 WHEN OTHERS THEN
95 IF (g_asn_debug = 'Y') THEN
96 asn_debug.put_line(p_entity_code || '=' || p_trx_id || ' has some problem. This is not acceptable.');
97 END IF;
98 RETURN NULL;
99 END get_tax_classification_code;
100
101
102 /* =================================================================
103
104 FUNCTION NAME: create_receipt_invoices()
105
106 ==================================================================*/
107 FUNCTION create_receipt_invoices(X_commit_interval IN NUMBER,
108 X_rcv_shipment_header_id IN NUMBER,
109 X_receipt_event IN VARCHAR2,
110 X_aging_period IN NUMBER DEFAULT NULL)
111 RETURN BOOLEAN
112
113 IS
114
115 X_progress VARCHAR2(3) := NULL;
116
117
118 /* Cursor bind var for aging period */
119
120 X_profile VARCHAR2(20) := NULL;
121
122 l_aging_period NUMBER := NULL;
123
124 /* Actual quauntity for the invoice */
125
126 X_received_quantity NUMBER := 0;
127 X_received_amount NUMBER := 0;
128
129 /*Bug# 1539257 */
130 X_tmp_batch_id NUMBER;
131 X_batch_name ap_batches.batch_name%TYPE;
132
133
134 /* Bug:396027. gtummala. 10/17/96
135 * Now the cursor picks up previously REJECTED transactions as well
136 * as PENDING ones
137 */
138
139 /* Bug:551612. gtummala. 11/02/97
140 * Now we will only pick up those trnxs where rts.invoice_status_code is
141 * 'PENDING' or 'REJECTED'. We won't pick up where it is null.
142 * The enter receipts form will only set this to 'PENDING' if the
143 * supplier site is set up for pay on receipt.
144 */
145
146 /* Bug 1930776. We need to pick up pay_on_receipt_summary_code from the
147 * purchasing site even if it is defined in the alternate pay site.
148 */
149
150 /***** Cursor declaration ****/
151 /* R12 Complex Work.
152 * Added plls.shipment_type so that we create separate invoices
153 * for Prepayment shipments. For other valid shipments like
154 * Standard and Blanket continue as is and can be grouped
155 * in one invoice.
156 */
157
158 /* Bug 5388926 : Changed order by clause in both cursors.
159 ** Ordering by shipment_header_id first to prevent multiple invoices
160 ** being generated.
161 */
162
163 /* Bug#6649580
164 * As part of bug#5443196 fix to improve the performance of 'Pay on Receipt'
165 * concurrent request, index RCV_TRANSACTIONS_N16 was changed to function based
166 * index (SOURCE_DOCUMENT_CODE, NVL(INVOICE_STATUS_CODE,'NA'),TRANSACTION_TYPE).
167 * But in the following cursor C_receipt_txns, there is no nvl on
168 * rts.invoice_status_code and this index is not effectively used and results in
169 * performance issue.
170 * So, added nvl on rts.invoice_status_code. This nvl condition is available
171 * in 1159 bug#4732594 fix.
172 */
173
174 CURSOR C_receipt_txns IS
175 SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
176 rts.rowid rcv_txn_rowid,
177 rts.transaction_id,
178 rts.po_header_id,
179 rts.po_release_id,
180 rts.po_line_id,
181 rts.po_line_location_id,
182 rts.po_distribution_id,
183 rsh.vendor_id,
184 pvds.segment1 vendor_num,
185 NVL(pvss.default_pay_site_id,pvss.vendor_site_id) default_pay_site_id,
186 pvss2.vendor_site_code pay_site_code,
187 pvss.pay_on_receipt_summary_code, -- default pay site's summary code
188 rts.shipment_header_id,
189 /* Bug 3065403 - Taking rsl.packing slip if rsh.packing slip is null.
190 Also changed the alias packing_slip to pack_slip to avoid ambiguous
191 column error in the order by clause.*/
192 NVL(rsh.packing_slip, nvl(rsl.packing_slip,rsh.receipt_num)) pack_slip,
193 rsh.receipt_num,
194 rts.shipment_line_id,
195 rts.transaction_date,
196 rts.amount,
197 rts.quantity,
198 nvl(plls.price_override, pls.unit_price) po_unit_price, /* Bug: 4409887 */
199 rts.currency_code,
200 rts.currency_conversion_type,
201 /* Note that we must decode currency type because the receiving programs put in
202 a 1 for the currency rate if base currency is same as PO. Purchasing and
203 Payables expects that the rate be null if base currency=PO/Invoice currency. */
204 decode (rts.currency_conversion_type,null,null,rts.currency_conversion_rate) currency_conversion_rate,
205 rts.currency_conversion_date,
206 NVL(NVL(plls.terms_id, phs.terms_id) , pvss2.terms_id) payment_terms_id,
207 DECODE(plls.taxable_flag, 'Y', plls.tax_code_id, NULL) tax_code_id,
208 pls.item_description,
209 plls.matching_basis,
210 decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD') shipment_type,
211 NVL(rsl.osa_flag,'N') osa_flag, --Shikyu project
212 plls.match_option, -- 5100177
213 rts.unit_of_measure, -- 5100177
214 plls.unit_meas_lookup_code -- 5100177
215 FROM po_vendor_sites pvss,
216 po_vendor_sites pvss2,
217 po_vendors pvds,
218 --Bugfix 5407632 - Using _all tables instead of views.
219 po_headers_all phs,
220 -- po_releases_all prs, /*Bug 5443196*/
221 po_lines_all pls,
222 po_line_locations_all plls,
223 rcv_shipment_headers rsh,
224 rcv_shipment_lines rsl,
225 rcv_transactions rts
226 WHERE rts.shipment_header_id = rsh.shipment_header_id
227 AND rts.po_header_id = phs.po_header_id
228 --AND rts.po_release_id = prs.po_release_id(+) /*Bug 5443196*/
229 AND rts.po_line_location_id = plls.line_location_id
230 AND rts.po_line_id = pls.po_line_id
231 AND rts.shipment_header_id = rsl.shipment_header_id
232 AND rts.shipment_line_id = rsl.shipment_line_id
233 AND phs.vendor_id = pvds.vendor_id
234 AND phs.vendor_site_id = pvss.vendor_site_id
235 AND phs.pcard_id is null
236 AND rsh.receipt_source_code = 'VENDOR'
237 AND rts.source_document_code = 'PO'
238 AND nvl(rts.invoice_status_code,'NA') IN ('PENDING','REJECTED') /*Bug:551612 */ --Bug#6649580
239 AND rts.transaction_type = X_receipt_event
240 /* <PAY ON USE FPI START> */
241 AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
242 AND PHS.PAY_ON_CODE IN ('RECEIPT', 'RECEIPT_AND_USE') /*Bug 5443196*/
243 /*AND decode(nvl(rts.po_release_id, -999), -999, phs.pay_on_code,
244 prs.pay_on_code) IN ('RECEIPT', 'RECEIPT_AND_USE')*/
245 AND NVL(plls.consigned_flag,'N') <> 'Y'
246 /* <PAY ON USE FPI END> */
247 AND pvss2.vendor_site_id = NVL(pvss.default_pay_site_id,pvss.vendor_site_id)
248 AND nvl(rsh.asn_type, ' ') <> 'ASBN'
249 AND rts.transaction_date <= sysdate - l_aging_period
250 AND rts.po_release_id IS null -- Bug 5443196
251 AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
252 UNION
253 SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
254 rts.rowid rcv_txn_rowid,
255 rts.transaction_id,
256 rts.po_header_id,
257 rts.po_release_id,
258 rts.po_line_id,
259 rts.po_line_location_id,
260 rts.po_distribution_id,
261 rsh.vendor_id,
262 pvds.segment1 vendor_num,
263 NVL(pvss.default_pay_site_id,pvss.vendor_site_id) default_pay_site_id,
264 pvss2.vendor_site_code pay_site_code,
265 pvss.pay_on_receipt_summary_code, -- default pay site's summary code
266 rts.shipment_header_id,
267 /* Bug 3065403 - Taking rsl.packing slip if rsh.packing slip is null.
268 Also changed the alias packing_slip to pack_slip to avoid ambiguous
269 column error in the order by clause.*/
270 NVL(rsh.packing_slip, nvl(rsl.packing_slip,rsh.receipt_num)) pack_slip,
271 rsh.receipt_num,
272 rts.shipment_line_id,
273 rts.transaction_date,
274 rts.amount,
275 rts.quantity,
276 nvl(plls.price_override, pls.unit_price) po_unit_price, /* Bug: 4409887 */
277 rts.currency_code,
278 rts.currency_conversion_type,
279 /* Note that we must decode currency type because the receiving programs put in
280 a 1 for the currency rate if base currency is same as PO. Purchasing and
281 Payables expects that the rate be null if base currency=PO/Invoice currency. */
282 decode (rts.currency_conversion_type,null,null,rts.currency_conversion_rate) currency_conversion_rate,
283 rts.currency_conversion_date,
284 NVL(NVL(plls.terms_id, phs.terms_id) , pvss2.terms_id) payment_terms_id,
285 DECODE(plls.taxable_flag, 'Y', plls.tax_code_id, NULL) tax_code_id,
286 pls.item_description,
287 plls.matching_basis,
288 decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD') shipment_type,
289 NVL(rsl.osa_flag,'N') osa_flag, --Shikyu project
290 plls.match_option, -- 5100177
291 rts.unit_of_measure, -- 5100177
292 plls.unit_meas_lookup_code -- 5100177
293 FROM po_vendor_sites pvss,
294 po_vendor_sites pvss2,
295 po_vendors pvds,
296 --Bugfix 5407632 - Using _all tables instead of views.
297 po_headers_all phs,
298 po_releases_all prs,
299 po_lines_all pls,
300 po_line_locations_all plls,
301 rcv_shipment_headers rsh,
302 rcv_shipment_lines rsl,
303 rcv_transactions rts
304 WHERE rts.shipment_header_id = rsh.shipment_header_id
305 AND rts.po_header_id = phs.po_header_id
306 AND rts.po_release_id = prs.po_release_id
307 AND rts.po_line_location_id = plls.line_location_id
308 AND rts.po_line_id = pls.po_line_id
309 AND rts.shipment_header_id = rsl.shipment_header_id
310 AND rts.shipment_line_id = rsl.shipment_line_id
311 AND phs.vendor_id = pvds.vendor_id
312 AND phs.vendor_site_id = pvss.vendor_site_id
313 AND phs.pcard_id is null
314 AND rsh.receipt_source_code = 'VENDOR'
315 AND rts.source_document_code = 'PO'
316 AND nvl(rts.invoice_status_code,'NA') IN ('PENDING','REJECTED') /*Bug:551612 */ --Bug#6649580
317 AND rts.transaction_type = X_receipt_event
318 /* <PAY ON USE FPI START> */
319 AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
320 AND PRS.PAY_ON_CODE IN ('RECEIPT', 'RECEIPT_AND_USE') /*Bug 5443196*/
321 /*AND decode(nvl(rts.po_release_id, -999), -999, phs.pay_on_code,
322 prs.pay_on_code) IN ('RECEIPT', 'RECEIPT_AND_USE')*/
323 AND NVL(plls.consigned_flag,'N') <> 'Y'
324 /* <PAY ON USE FPI END> */
325 AND pvss2.vendor_site_id = NVL(pvss.default_pay_site_id,pvss.vendor_site_id)
326 AND nvl(rsh.asn_type, ' ') <> 'ASBN'
327 AND rts.transaction_date <= sysdate - l_aging_period
328 AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
329 ORDER BY 8,10,21,25,17,14,29,13,30; --Bug 5443196*/
330
331 /* Bug 6822389 Changed the sorting order, brought back rsh.shipment_header_id to its earlier place */
332
333 --Bug 5443196: Commented the following as order by is replaced by the column numbers
334 /*ORDER BY phs.vendor_id,
335 NVL(pvss.default_pay_site_id,pvss.vendor_site_id),
336 rts.currency_code,
337 payment_terms_id,
338 rts.transaction_date,
339 pack_slip,
340 decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD'),
341 rsh.shipment_header_id,
342 rsl.osa_flag; --Shikyu project*/
343
344 /* R12 Complex Work.
345 * Added plls.shipment_type so that we create separate invoices
346 * for Prepayment shipments. For other valid shipments like
347 * Standard and Blanket continue as is and can be grouped
348 * in one invoice.
349 */
350 /***** Cursor declaration ****/
351 CURSOR C_receipt_txns2 IS
352 SELECT rts.rowid rcv_txn_rowid,
353 rts.transaction_id,
354 rts.po_header_id,
355 rts.po_release_id,
356 rts.po_line_id,
357 rts.po_line_location_id,
358 rts.po_distribution_id,
359 rsh.vendor_id,
360 pvds.segment1 vendor_num,
361 NVL(pvss.default_pay_site_id,pvss.vendor_site_id) default_pay_site_id,
362 pvss2.vendor_site_code pay_site_code,
363 pvss.pay_on_receipt_summary_code, -- default pay site's summary code
364 rts.shipment_header_id,
365 /* Bug 3065403 - Taking rsl.packing slip if rsh.packing slip is null.
366 Also changed the alias packing_slip to pack_slip to avoid ambiguous
367 column error in the order by clause*/
368 NVL(rsh.packing_slip,nvl(rsl.packing_slip, rsh.receipt_num)) pack_slip,
369 rsh.receipt_num,
370 rts.shipment_line_id,
371 rts.transaction_date,
372 rts.amount,
373 rts.quantity,
374 nvl(plls.price_override, pls.unit_price) po_unit_price, /* Bug4409887 */
375 rts.currency_code,
376 rts.currency_conversion_type,
377 /* Note that we must decode currency type because the receiving programs put in
378 a 1 for the currency rate if base currency is same as PO. Purchasing and
379 Payables expects that the rate be null if base currency=PO/Invoice currency. */
380 decode (rts.currency_conversion_type,null,null,rts.currency_conversion_rate) currency_conversion_rate,
381 rts.currency_conversion_date,
382 NVL(NVL(plls.terms_id, phs.terms_id) , pvss2.terms_id) payment_terms_id,
383 DECODE(plls.taxable_flag, 'Y', plls.tax_code_id, NULL) tax_code_id,
384 pls.item_description,
385 plls.matching_basis,
386 decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD')shipment_type,
387 rsl.osa_flag, --Shikyu project
388 plls.match_option, -- 5100177
389 rts.unit_of_measure, -- 5100177
390 plls.unit_meas_lookup_code -- 5100177
391 FROM po_vendor_sites pvss,
392 po_vendor_sites pvss2,
393 po_vendors pvds,
394 po_headers phs,
395 po_releases prs,
396 po_lines pls,
397 po_line_locations plls,
398 rcv_shipment_headers rsh,
399 rcv_shipment_lines rsl,
400 rcv_transactions rts
401 WHERE rts.shipment_header_id = rsh.shipment_header_id
402 AND rts.po_header_id = phs.po_header_id
403 AND rts.po_release_id = prs.po_release_id(+)
404 AND rts.po_line_location_id = plls.line_location_id
405 AND rts.po_line_id = pls.po_line_id
406 AND rts.shipment_header_id = rsl.shipment_header_id
407 AND rts.shipment_line_id = rsl.shipment_line_id
408 AND phs.vendor_id = pvds.vendor_id
409 AND phs.vendor_site_id = pvss.vendor_site_id
410 AND phs.pcard_id is null
411 AND rsh.receipt_source_code = 'VENDOR'
412 AND rts.source_document_code = 'PO'
413 AND rts.invoice_status_code IN ('PENDING','REJECTED') /*Bug:551612 */
414 AND rts.transaction_type = X_receipt_event
415 /* <PAY ON USE FPI START> */
416 AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
417 AND decode(nvl(rts.po_release_id, -999), -999, phs.pay_on_code,
418 prs.pay_on_code) IN ('RECEIPT', 'RECEIPT_AND_USE')
419 AND NVL(plls.consigned_flag, 'N') <> 'Y'
420 /* <PAY ON USE FPI END> */
421 AND pvss2.vendor_site_id = NVL(pvss.default_pay_site_id,pvss.vendor_site_id)
422 AND rsh.shipment_header_id = X_rcv_shipment_header_id
423 AND nvl(rsh.asn_type, ' ') <> 'ASBN'
424 AND rts.transaction_date <= sysdate - l_aging_period
425 AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
426 ORDER BY
427 phs.vendor_id,
428 NVL(pvss.default_pay_site_id,pvss.vendor_site_id),
429 rts.currency_code,
430 payment_terms_id,
431 rts.transaction_date,
432 pack_slip,
433 decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD'),
434 rsh.shipment_header_id, --Bug 6822389
435 rsl.osa_flag; --Shikyu project
436
437
438 X_rcv_txns c_receipt_txns%ROWTYPE;
439 X_terms_date DATE;
440 X_invoice_count NUMBER; /** num of invoices created in this run ***/
441 X_invoice_running_total NUMBER; /** running total of invoice amount for
442 invoices created */
443 X_first_rcv_txn_flag VARCHAR2(1);
444
445 X_curr_inv_process_flag VARCHAR2(1) := 'Y';
446 /*** flag used to indicate whether the current invoice is processable,
447 i.e. indicate whether any application error has occurred during the
448 process of the invoice. If error occurs, this flag will be 'N'.
449 ***/
450 X_completion_status BOOLEAN := TRUE;
451 /*** This flag will be set to FALSE if at least one error occurred
452 during the run of this API. ***/
453
454 /*** The following set of curr_ variables are used to keep track of the
455 current values used to determine if a new invoice has to be created ***/
456 X_curr_invoice_amount NUMBER := 0;
457 X_curr_invoice_id NUMBER := NULL;
458 X_curr_invoice_num ap_invoices.invoice_num%TYPE;
459
460 X_curr_vendor_id NUMBER := NULL;
461 X_curr_pay_site_id NUMBER := NULL;
462 X_curr_currency_code rcv_transactions.currency_code%TYPE := NULL;
463 X_curr_payment_terms_id NUMBER := NULL;
464 X_curr_transaction_date DATE := NULL;
465 X_curr_le_transaction_date DATE := NULL; --LE time zone date (Bug: 5205516)
466 X_curr_packing_slip rcv_shipment_headers.receipt_num%TYPE := NULL;
467 X_curr_shipment_header_id NUMBER := NULL;
468 X_curr_osa_flag VARCHAR2(1) := NULL; --Shikyu project
469
470 X_curr_conversion_rate_type rcv_transactions.currency_conversion_type%TYPE;
471 X_curr_conversion_rate_date DATE;
472 X_curr_conversion_rate NUMBER;
473
474 /** Bug# 1176326 **/
475 X_curr_conversion_rate_date1 DATE;
476 X_curr_conversion_rate1 NUMBER;
477 X_def_base_currency_code ap_system_parameters.base_currency_code%TYPE;
478
479 X_curr_accounting_date DATE;
480 X_curr_period_name gl_periods.period_name%TYPE;
481 /** Bug 586895 **/
485 X_curr_pay_curr_code po_vendor_sites.payment_currency_code%TYPE;
482 X_curr_method_code IBY_PAYMENT_METHODS_VL.PAYMENT_METHOD_CODE%TYPE;
483
484 /* Bug 612979 **/
486 X_ap_pay_curr po_vendor_sites.payment_currency_code%TYPE;
487
488 /*** vendor, vendor-pay-site related varibles ***/
489
490 X_pay_group_lookup_code po_vendors.pay_group_lookup_code%TYPE;
491 X_payment_method_lookup_code IBY_PAYMENT_METHODS_VL.PAYMENT_METHOD_CODE%TYPE;
492 X_payment_priority po_vendors.payment_priority%TYPE;
493 X_terms_date_basis po_vendors.terms_date_basis%TYPE;
494 X_vendor_income_tax_region po_vendor_sites.state%TYPE;
495 X_type_1099 po_vendors.type_1099%TYPE;
496 X_awt_flag po_Vendor_sites.allow_awt_flag%TYPE;
497 X_awt_group_id po_vendor_sites.awt_group_id%TYPE;
498 X_exclude_freight_from_disc po_vendor_sites.exclude_freight_from_discount%TYPE;
499 X_unit_meas_lookup_code varchar2(25);
500
501 /* BUG 612979 */
502
503 X_payment_currency_code po_vendor_sites.payment_currency_code%TYPE := NULL;
504 X_pay_cross_rate NUMBER;
505
506 X_batch_id NUMBER;
507 X_discountable_amount NUMBER;
508 X_inv_event VARCHAR2(26);
509 X_invoice_description ap_invoices.description%TYPE;
510 l_user_id NUMBER;
511 v_req_id NUMBER;
512
513 /** this is the group id we insert into the
514 AP interface table to identify out batch **/
515
516 X_group_id VARCHAR2(80);
517 x_dist_count NUMBER;
518
519 /* Fix for bug 2943056.
520 Commenting the fix done in 2379414 at all places of the code.
521 */
522
523 x_org_id NUMBER; --Bug# 2492041
524
525 /* <PAY ON USE FPI START> */
526 l_error_msg VARCHAR2(2000);
527 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
528 /* <PAY ON USE FPI END> */
529
530 /* R12 Complex Work.*/
531 x_curr_shipment_type po_line_locations_all.shipment_type%type;
532
533 /*Bug: 5125624*/
534 l_ship_to_location_id PO_LINE_LOCATIONS.SHIP_TO_LOCATION_ID%TYPE;
535 l_tax_classification_code VARCHAR2(30);
536
537 X_curr_receipt_num rcv_shipment_headers.receipt_num%TYPE := NULL; /* bug 7512542 */
538 X_curr_por_summary_code po_vendor_sites.pay_on_receipt_summary_code%TYPE := NULL; /* bug 7512542 */
539 X_curr_org_id NUMBER :=NULL; /* bug 7512542 */
540
541
542 BEGIN
543 /**** BEGIN create_receipt_invoices ***/
544 IF (g_asn_debug = 'Y') THEN
545 asn_debug.put_line('Begin Create Receipt Invoices ... ');
546 END IF;
547
548 X_invoice_count := 0;
549 X_invoice_running_total := 0;
550 X_first_rcv_txn_flag := 'Y';
551
552 X_progress := '010';
553
554
555 /** bug 885111, allow user to supply aging period from the report **/
556
557 IF (x_aging_period IS NULL) THEN
558
559 /* Get Aging period */
560 FND_PROFILE.GET('AGING_PERIOD', X_profile);
561 l_aging_period := floor(to_number(X_profile));
562
563 IF l_aging_period < 0 THEN
564 l_aging_period := 0;
565 END IF;
566
567 ELSE
568 l_aging_period := x_aging_period;
569 END IF;
570
571
572 X_progress := '020';
573 IF (g_asn_debug = 'Y') THEN
574 asn_debug.put_line('Begin processing rcv txns ... [' || to_char(x_aging_period) || ']');
575 END IF;
576
577
578 IF (X_rcv_shipment_header_id IS NULL) THEN
579
580 IF (g_asn_debug = 'Y') THEN
581 asn_debug.put_line('opening c_receipt_txns');
582 END IF;
583 OPEN c_receipt_txns;
584 ELSE
585 IF (g_asn_debug = 'Y') THEN
586 asn_debug.put_line('opening c_receipt_txns2');
587 END IF;
588 OPEN c_receipt_txns2;
589
590 END IF;
591
592 LOOP
593
594 X_progress := '030';
595
596 IF (X_rcv_shipment_header_id IS NULL) THEN
597
598 IF (g_asn_debug = 'Y') THEN
599 asn_debug.put_line('fetching c_receipt_txns');
600 END IF;
601 FETCH c_receipt_txns INTO X_rcv_txns;
602
603 IF (c_receipt_txns%NOTFOUND) THEN
604 IF (g_asn_debug = 'Y') THEN
605 asn_debug.put_line('closing c_receipt_txns');
606 END IF;
607 CLOSE C_receipt_txns;
608 EXIT;
609 END IF;
610
611 ELSE
612 IF (g_asn_debug = 'Y') THEN
613 asn_debug.put_line('fetching c_receipt_txns2');
614 END IF;
615 FETCH c_receipt_txns2 INTO X_rcv_txns;
616
617 IF (c_receipt_txns2%NOTFOUND) THEN
618 IF (g_asn_debug = 'Y') THEN
619 asn_debug.put_line('closing c_receipt_txns2');
620 END IF;
621 CLOSE C_receipt_txns2;
622 EXIT;
623 END IF;
624
625 END IF;
626
627 IF (g_asn_debug = 'Y') THEN
628 asn_debug.put_line('IN processing rcv txns ... ');
629 END IF;
630
631 X_progress := '040';
632
636 --X_accts_pay_combination_id,
633 po_invoices_sv1.get_vendor_related_info(X_rcv_txns.vendor_id,
634 X_rcv_txns.default_pay_site_id,
635 X_pay_group_lookup_code,
637 X_payment_method_lookup_code,
638 X_payment_priority,
639 X_terms_date_basis,
640 X_vendor_income_tax_region,
641 X_type_1099,
642 X_awt_flag,
643 X_awt_group_id,
644 X_exclude_freight_from_disc,
645 X_payment_currency_code -- BUG 612979 add payment_currency_code of default pay site
646 );
647
648 X_progress := '045';
649
650 /*Bug#2492041 Get the Operating Unit for the PO */
651 select org_id
652 into x_org_id
653 from po_headers_all
654 where po_header_id = X_rcv_txns.po_header_id;
655
656 IF (x_payment_currency_code is NULL) THEN
657 x_payment_currency_code := X_rcv_txns.currency_code;
658 END IF;
659
660 /*Check to see if it is the first invoice to be created: */
661
662 IF (X_first_rcv_txn_flag = 'Y') THEN
663
664 /**** Logic for the first invoice created ***/
665 X_progress := '050';
666
667 X_first_rcv_txn_flag := 'N';
668
669 /*** if any application error occurs the creation
670 of an invoice, the program will rollback to this
671 savepoint. ***/
672
673 SAVEPOINT header_record_savepoint;
674 X_curr_inv_process_flag := 'Y';
675
676 /*The following variables will be used to determine if
677 a new invoice should be created: */
678 /** Bug 586895 **/
679 X_curr_method_code :=
680 X_payment_method_lookup_code;
681
682 /** Bug 612979 **/
683
684
685 X_curr_pay_curr_code := X_payment_currency_code;
686
687 X_curr_invoice_amount := 0;
688 X_curr_vendor_id := X_rcv_txns.vendor_id;
689 X_curr_pay_site_id :=
690 X_rcv_txns.default_pay_site_id;
691 X_curr_currency_code := X_rcv_txns.currency_code;
692 X_curr_conversion_rate_type :=
693 X_rcv_txns.currency_conversion_type;
694 /* R12 Complex Work.
695 * Get the shipment type.
696 */
697 X_curr_shipment_type := X_rcv_txns.shipment_type;
698
699 /* Bug# 1176326
700 ** We now take the rate corresponding to the date
701 ** on which the invoice was created rather than taking
702 ** the rate on the receipt date
703 */
704 select base_currency_code
705 into X_def_base_currency_code
706 from ap_system_parameters;
707
708 X_curr_conversion_rate_date :=
709 X_rcv_txns.transaction_date ;
710
711 X_curr_conversion_rate :=
712 ap_utilities_pkg.get_exchange_rate(
713 X_curr_currency_code,
714 X_def_base_currency_code,
715 X_curr_conversion_rate_type,
716 X_curr_conversion_rate_date,
717 'create_receipt_invoices');
718
719 if X_curr_conversion_rate is null then
720 X_curr_conversion_rate :=
721 X_rcv_txns.currency_conversion_rate;
722 X_curr_conversion_rate_date :=
723 X_rcv_txns.currency_conversion_date;
724 end if;
725 /* 3065403 - Changed packing slip to pack slip as the alias name is
726 changed in the cursor. */
727
728 X_curr_payment_terms_id :=
729 X_rcv_txns.payment_terms_id;
730 X_curr_packing_slip := X_rcv_txns.pack_slip;
731 X_curr_shipment_header_id :=
732 X_rcv_txns.shipment_header_id;
733 X_curr_osa_flag := X_rcv_txns.osa_flag; --Shikyu project
734 X_curr_transaction_date :=
735 X_rcv_txns.transaction_date;
736
737
738 /* Bug 7512542 Initialising the variables with current cursor values */
739
740 X_curr_org_id := x_org_id;
741 X_curr_por_summary_code :=
742 X_rcv_txns.pay_on_receipt_summary_code;
743 X_curr_receipt_num := X_rcv_txns.receipt_num;
744
745 /* End Bug 7512542 */
746
747 /*
748 AP requires the invoice_date, goods_received_date and
749 invoice_received_date converted in to the LE time zone.
750 Bug: 5205516
751 */
752 X_curr_le_transaction_date :=
753 INV_LE_TIMEZONE_PUB.GET_LE_DAY_TIME_FOR_OU(x_curr_transaction_date,x_org_id);
754 X_progress := '060';
755
756
757
758 /* Bug510160. gtummala. 8/4/97
759 * Need to set the approval status to NULL not
760 * UNAPPROVED.
761 */
762
763 /* added by nwang */
764
765 /* Bug 7512542 Commenting call to create_invoice_num */
766 /* IF (X_curr_inv_process_flag = 'Y') THEN
767 BEGIN
768 X_curr_invoice_num :=
769 po_invoices_sv2.create_invoice_num(
770 x_org_id, -- SBI ENH
771 X_rcv_txns.default_pay_site_id, -- SBI ENH
772 X_rcv_txns.pay_on_receipt_summary_code,
773 X_curr_le_transaction_date, --Bug: 5344040
774 X_rcv_txns.pack_slip,
775 X_rcv_txns.receipt_num);
776 EXCEPTION
777 WHEN others THEN
778 asn_debug.put_line('create_invoice_num raised error');
779 X_curr_inv_process_flag := 'N';
780 X_first_rcv_txn_flag := 'Y';
781 END;
782 END IF;*/
783 /* End bug 7512542 */
784
785 select ap_invoices_interface_s.nextval
786 into x_curr_invoice_id
787 from sys.dual;
788
789 x_group_id := substr('ERS-'||TO_CHAR(X_rcv_txns.transaction_id),1,80);
790
791
792 /* bug 612979 */ IF (gl_currency_api.is_fixed_rate(X_curr_pay_curr_code,
793 X_curr_currency_code, X_curr_transaction_date) = 'Y'
794 and X_curr_pay_curr_code <> X_curr_currency_code) THEN
795 X_ap_pay_curr := X_curr_pay_curr_code;
796 ELSE
797 X_ap_pay_curr := X_curr_currency_code;
798 END IF;
799
800 End IF; -- X_first_rcv_txn_flag
801
802 -- parameters to this API are NOT all the columns in AP_INVOICES, the
803 -- other columns are not used by create_receipt_invoices or create_notice_invoices.
804
805 /**** Check to see if there is a change in vendor,
806 pay_site,
807 currency or
808 txn_date
809 If so, we would first update the current invoice -- invoice amount, etc.
810 create payment schedule for the invoice and then
811 get ready to create a new invoice. ***/
812
813 /* Bug 586895 */
814
815 /* Bug 2536170 - We consider the transaction date also for
816 creating new invoice as it determines the conversion rate
817 between the purchasing currency and invoice currency.But when
818 the transaction date remaining the same except for the timestamp
819 we were creating a new invoice. This should not be the case.
820 So added a trunc on the date comparisons so that all the transactions
821 that have the same transaction date except for the timestamp will
822 have a single invoice provided these transactions can be grouped by
823 the invoice summary level(pay_on_summary_code). Also removed the
824 AND condition added in fix 1703833 as there will conversion issues
825 if we don't consider transaction dates for pay sites also.
826 */
827
828 /* Bug 1703833. If the receipt_date is different, then we create
829 * multiple invoices even if the pay_on_receipt_summary_code is
830 * PAY_SITE. Changed the code below to include the condition
831 * that if transaction_date is not the same and the summary code
832 * is not PAY_SITE, then go inside the if clause.
833 */
834
835 /* Bug 2531542 - The logic followed for creating invoices is to
836 insert records into ap_lines_interface first (distributions)
837 and then insert the records in ap_invoices_interface(Headers)
838 so the amount will be the total distribution amount.
839 For bug fix 1762305 , if the net received quantity is 0 then
840 distribuitions lines were not inserted. But the records were
841 inserted for the headers even for the received qty of 0.
842 Because of this Payables import program was erroring out with
843 'Atleast one invoice line is needed' error message. So
844 checking for the distribiution count before inserting the headers
845 and inserting only if the distribution count is >0. */
846
847 /* 3065403 - Changed packing slip to pack slip as the alias name is
848 changed in the cursor. */
849
850 /* R12 Complex Work.
851 * Compare shipment_types and if they are different then create
852 * a new invoice. Here we will have Standard for all the other
853 * shipment_types other than prepayment as we want to group
854 * them together.
855 */
856
857 IF (X_curr_vendor_id <> X_rcv_txns.vendor_id) OR
858 (X_curr_pay_site_id <> X_rcv_txns.default_pay_site_id) OR
859 (X_curr_currency_code <> X_rcv_txns.currency_code) OR
860 (X_curr_payment_terms_id <> X_rcv_txns.payment_terms_id) OR
861 (trunc(X_curr_transaction_date) <> trunc(X_rcv_txns.transaction_date)) OR
862 (X_curr_packing_slip <> X_rcv_txns.pack_slip AND
863 X_rcv_txns.pay_on_receipt_summary_code = 'PACKING_SLIP') OR
864 (X_curr_shipment_header_id <> X_rcv_txns.shipment_header_id AND
865 X_rcv_txns.pay_on_receipt_summary_code = 'RECEIPT') OR
866 (X_curr_method_code <> X_payment_method_lookup_code) OR
867 (X_curr_shipment_type <> x_rcv_txns.shipment_type) OR--Complex Work
868 (X_curr_osa_flag <> X_rcv_txns.osa_flag) --Shikyu project
869 THEN
870 /* 2531542 */
871 select count(*) into x_dist_count
872 from ap_invoice_lines_interface
873 where invoice_id = x_curr_invoice_id;
874
875
876
877 /* Bug# 1176326
878 ** We now take the rate corresponding to the date
879 ** on which the invoice was created rather than taking
880 ** the rate on the receipt date
881 */
882 select base_currency_code
883 into X_def_base_currency_code
884 from ap_system_parameters;
885
886 X_curr_conversion_rate_date1 :=
887 X_rcv_txns.transaction_date ;
888
889 X_curr_conversion_rate1 :=
890 ap_utilities_pkg.get_exchange_rate(
891 X_rcv_txns.currency_code,
892 X_def_base_currency_code,
893 X_rcv_txns.currency_conversion_type,
894 X_curr_conversion_rate_date1,
895 'create_receipt_invoices');
896
897 if X_curr_conversion_rate1 is null then
898 X_curr_conversion_rate1 :=
899 X_rcv_txns.currency_conversion_rate;
900 X_curr_conversion_rate_date1 :=
901 X_rcv_txns.currency_conversion_date;
902 end if;
903
904 /*** a new invoice needs to be created ... and we need to
905 update the current one before the new one can be created. ***/
906
907 X_progress := '090';
908
909 /** update invoice amounts and also running totals.
910 Also create payment schedules ***/
911 /*Bug 5382916: Date in the description should be in LE Time zone*/
912
913 fnd_message.set_name('PO', 'PO_INV_CR_ERS_INVOICE_DESC');
914
915 X_progress := '100';
916 fnd_message.set_token('RUN_DATE', x_curr_le_transaction_date);
917 X_progress := '110';
918 X_invoice_description := fnd_message.get;
919
920 IF (UPPER(x_curr_conversion_rate_type) <> 'USER') THEN
921 x_curr_conversion_rate := NULL;
922 END IF;
923
924 IF (g_asn_debug = 'Y') THEN
925 asn_debug.put_line('creating invoice headers');
926 END IF;
927 /* bug 1832024 : we need to insert terms id into the interface table
928 so that ap get the value */
929
930
931 if (x_curr_inv_process_flag = 'Y') THEN
932 if (x_dist_count > 0 ) then -- 2531542
933 asn_debug.put_line('x_curr_pay_site_id='||x_curr_pay_site_id||' and X_rcv_txns.default_pay_site_id='||X_rcv_txns.default_pay_site_id);
934
935 /* Bug 7512542 Calling create_invoice_num here to ensure
936 gapless invoice numbering and passing the parameters which
937 correspond to the RT record being invoiced */
938
939 BEGIN
940 X_curr_invoice_num :=
941 po_invoices_sv2.create_invoice_num(
942 X_curr_org_id,
943 X_curr_pay_site_id,
944 X_curr_por_summary_code,
945 X_curr_le_transaction_date,
946 X_curr_packing_slip,
947 X_curr_receipt_num);
948
949 asn_debug.put_line('Invoice Number = ' ||X_curr_invoice_num);
950
951 EXCEPTION
952 WHEN others THEN
953 asn_debug.put_line('create_invoice_num raised error');
954 X_curr_inv_process_flag := 'N';
955 X_first_rcv_txn_flag := 'Y';
956 END;
957 /* End bug 7512542 */
958
959 insert into AP_INVOICES_INTERFACE
960 (INVOICE_ID,
961 INVOICE_NUM,
962 VENDOR_ID,
963 VENDOR_SITE_ID,
964 INVOICE_AMOUNT,
965 INVOICE_CURRENCY_CODE,
966 INVOICE_DATE,
967 SOURCE,
968 DESCRIPTION,
969 GOODS_RECEIVED_DATE,
970 INVOICE_RECEIVED_DATE,
971 CREATION_DATE,
972 EXCHANGE_RATE,
973 EXCHANGE_RATE_TYPE,
974 EXCHANGE_DATE,
975 TERMS_ID,
976 GROUP_ID,
977 ORG_ID, -- Bug#2492041
978 -- GL_DATE, -- Bug#: 3418406
979 /* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
980 INVOICE_TYPE_LOOKUP_CODE, -- Complex Work
981 CALC_TAX_DURING_IMPORT_FLAG, -- TCA
982 ADD_TAX_TO_INV_AMT_FLAG -- bug 5499478
983 ) VALUES
984 (x_curr_invoice_id,
985 x_curr_invoice_num,
986 x_curr_vendor_id,
987 x_curr_pay_site_id,
988 x_curr_invoice_amount,
989 x_curr_currency_code,
990 x_curr_le_transaction_date, --Bug 5205516: INVOICE_DATE in LE Time zone
991 'ERS', -- debug, needs to change,
992 x_invoice_description,
996 x_curr_conversion_rate,
993 x_curr_le_transaction_date, --Bug 5205516: GOODS_RECEIVIED_DATE in LE Time zone
994 x_curr_le_transaction_date, --Bug 5205516: INVOICE_RECEIVIED_DATE in LE Time zone
995 sysdate,
997 x_curr_conversion_rate_type,
998 x_curr_conversion_rate_date,
999 X_curr_payment_terms_id,
1000 x_group_id,
1001 x_org_id,
1002 --inv_le_timezone_pub.get_le_day_for_ou(x_curr_transaction_date, x_org_id),
1003 /* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
1004 x_curr_shipment_type,
1005 'Y',
1006 'Y'
1007 );
1008 end if ; -- x_dist_count >0
1009 end if;
1010
1011 /* Bug#3277331 The correct values for Exchange Rate and Exchange Rate Date
1012 ** should be passed to the following procedure so that the Invoices will get
1013 ** created with the correct values.
1014 */
1015
1016 /* R12 Complex work.
1017 * Send the x_rcv_txns.shipment_type and
1018 * x_curr_shipment_type to wrap_up_current_invoice
1019 * procedure.
1020 */
1021 po_invoices_sv2.wrap_up_current_invoice(
1022 X_rcv_txns.vendor_id,
1023 X_rcv_txns.default_pay_site_id,
1024 X_rcv_txns.currency_code,
1025 X_rcv_txns.currency_conversion_type,
1026 /* Bug#3277331
1027 X_rcv_txns.currency_conversion_date,
1028 X_rcv_txns.currency_conversion_rate,
1029 */
1030 X_curr_conversion_rate_date1, /* Bug#3277331 */
1031 X_curr_conversion_rate1, /* Bug#3277331 */
1032 X_rcv_txns.payment_terms_id,
1033 X_rcv_txns.transaction_date,
1034 X_rcv_txns.pack_slip,
1035 X_rcv_txns.shipment_header_id,
1036 x_rcv_txns.osa_flag, --Shikyu project
1037 X_terms_date,
1038 X_payment_priority,
1039 /** Bug 586895 **/ X_payment_method_lookup_code,
1040 X_curr_method_code,
1041 /* Bug 612979 */ X_payment_currency_code,
1042 X_curr_pay_curr_code,
1043 X_batch_id,
1044 X_curr_invoice_amount,
1045 X_curr_invoice_id,
1046 X_curr_vendor_id,
1047 X_curr_pay_site_id,
1048 X_curr_currency_code,
1049 X_curr_conversion_rate_type,
1050 X_curr_conversion_rate_date,
1051 X_curr_conversion_rate,
1052 X_curr_payment_terms_id,
1053 X_curr_transaction_date,
1054 X_curr_packing_slip,
1055 X_curr_shipment_header_id,
1056 X_curr_osa_flag, --Shikyu project
1057 X_curr_inv_process_flag,
1058 X_invoice_count,
1059 X_invoice_running_total,
1060 X_rcv_txns.shipment_type,--Complex Work
1061 X_curr_shipment_type, --Complex Work
1062 X_org_id,
1063 X_curr_le_transaction_date
1064 );
1065
1066 /*** Ready to create the next invoice ***/
1067
1068 /* Bug 7512542 */
1069 X_curr_receipt_num := X_rcv_txns.receipt_num;
1070 x_curr_org_id := x_org_id;
1071 X_curr_por_summary_code := X_rcv_txns.pay_on_receipt_summary_code;
1072 /* Bug 7512542 end */
1073
1074 X_progress := '110';
1075
1076 IF (X_curr_inv_process_flag <> 'Y') THEN
1077 /** at least one error occurred **/
1078 X_completion_status := FALSE;
1079 ROLLBACK TO header_record_savepoint;
1080 END IF;
1081
1082 IF MOD(X_invoice_count , X_commit_interval) = 0
1083 AND x_invoice_count > 0 THEN
1084 X_progress := '100';
1085
1086 IF (g_asn_debug = 'Y') THEN
1087 asn_debug.put_line('Committing changes ... ');
1088 END IF;
1089 COMMIT;
1090 END IF;
1091
1092 SAVEPOINT header_record_savepoint;
1093 X_curr_inv_process_flag := 'Y';
1094
1095 /* Bug 7512542 Commenting call to create_invoice_num */
1096
1097 /* IF (X_curr_inv_process_flag = 'Y') THEN
1098 BEGIN
1099 X_curr_invoice_num :=
1100 po_invoices_sv2.create_invoice_num(
1101 x_org_id, -- SBI ENH
1102 X_rcv_txns.default_pay_site_id, -- SBI ENH
1103 X_rcv_txns.pay_on_receipt_summary_code,
1104 X_curr_le_transaction_date,--BUG 5344040: LE TIME ZONE
1105 X_rcv_txns.pack_slip,
1106 X_rcv_txns.receipt_num);
1107 EXCEPTION
1108 WHEN others THEN
1109 asn_debug.put_line('create_invoice_num raised error');
1110 X_curr_inv_process_flag := 'N';
1111 END;
1112 END IF;*/
1113 /* End bug 7512542 */
1114
1115 X_progress := '130';
1116
1117 /* Bug510160. gtummala. 8/4/97
1118 * Need to set the approval status to NULL not UNAPPROVED.
1119 */
1120 /* bug 612979 */
1124 and X_curr_pay_curr_code <> X_curr_currency_code) THEN
1121
1122 IF (gl_currency_api.is_fixed_rate(X_curr_pay_curr_code,
1123 X_curr_currency_code, X_curr_transaction_date) = 'Y'
1125 X_ap_pay_curr := X_curr_pay_curr_code;
1126 ELSE
1127 X_ap_pay_curr := X_curr_currency_code;
1128 END IF;
1129
1130 -- parameters to this API are NOT all the columns in
1131 -- AP_INVOICES, the other columns are not used by
1132 -- create_receipt_invoices or create_notice_invoices.
1133
1134 END IF; /** change in one of the curr_ variables **/
1135
1136 /**** Create invoice distribution(s) , tax distribution(s) and
1137 update rcv_txns, po_line_locations and po_distributions accordingly *****/
1138
1139
1140 X_progress := '140'; -- receipt_invoices
1141
1142 IF (X_curr_inv_process_flag = 'Y') THEN
1143 /** only create invoice and/or tax distr if the invoice is still
1144 processable. **/
1145
1146
1147 /* bug 660397 if there is a corresponding 'DELIVER' transaction for
1148 a 'RECEIVE' transaction, we want to pass in 'DELIVER' into
1149 create_invoice_distribution, so that only one invoice distribution is created.
1150 Otherwise, if there would be pro-ration done on every distribution,
1151 creating a total of n square invoice distributions */
1152
1153
1154 SELECT MIN(NVL(transaction_type, X_receipt_event))
1155 INTO X_inv_event
1156 FROM rcv_transactions
1157 WHERE shipment_line_id = X_rcv_txns.shipment_line_id
1158 AND po_distribution_id = NVL(X_rcv_txns.po_distribution_id,-1)
1159 AND parent_transaction_id = X_rcv_txns.transaction_id
1160 AND transaction_type = 'DELIVER';
1161
1162 /* Get the adjusted quantity for invoice creation */
1163
1164 IF X_rcv_txns.matching_basis = 'AMOUNT' THEN
1165 X_received_quantity := 0;
1166 po_invoices_sv2.get_received_amount(X_rcv_txns.transaction_id,
1167 X_rcv_txns.shipment_line_id,
1168 X_received_amount);
1169 ELSE
1170 X_received_amount := 0;
1171 asn_debug.put_line('pparthasmatch_option '||x_rcv_txns.match_option);
1172 po_invoices_sv2.get_received_quantity(X_rcv_txns.transaction_id,
1173 X_rcv_txns.shipment_line_id,
1174 X_received_quantity,
1175 X_rcv_txns.match_option ); --5100177
1176 END IF;
1177
1178
1179 /* Bug 1762305. Need not create an invoice line if
1180 * net quantity received is 0.
1181 */
1182
1183 /* Removed the fix of 2379414 as it is already commented */
1184
1185
1186 /* Bug 5100177.
1187 * According to AP, we need to populte unit_meas_lookup_code in
1188 * AP lines interface table with rt.unit_of_measure if match
1189 * option in PO shipment is Receipt and with unit_of_measure
1190 * from PO shipment if the match option is PO.
1191 */
1192 if X_rcv_txns.match_option = 'P' then
1193 X_unit_meas_lookup_code := X_rcv_txns.unit_meas_lookup_code;
1194 asn_debug.put_line('pparthas from shipment '||X_unit_meas_lookup_code);
1195 elsif X_rcv_txns.match_option = 'R' then
1196 X_unit_meas_lookup_code := X_rcv_txns.unit_of_measure;
1197 asn_debug.put_line('pparthas from rt '||X_unit_meas_lookup_code);
1198 end if;
1199
1200
1201 if (x_received_quantity <> 0 or x_received_amount <> 0) then
1202 po_invoices_sv2.create_invoice_distributions(
1203 X_curr_invoice_id,
1204 X_curr_currency_code,
1205 x_curr_currency_code,
1206 X_batch_id,
1207 X_curr_pay_site_id,
1208 X_rcv_txns.po_header_id,
1209 X_rcv_txns.po_line_id,
1210 X_rcv_txns.po_line_location_id,
1211 X_rcv_txns.po_release_id, -- bug 901039
1212 X_inv_event, -- bug 660397
1213 X_rcv_txns.po_distribution_id,
1214 X_rcv_txns.item_description,
1215 X_type_1099,
1216 X_rcv_txns.tax_code_id,
1217 X_received_quantity,
1218 X_rcv_txns.po_unit_price,
1219 X_curr_conversion_rate_type,
1220 X_curr_conversion_rate_date,
1221 X_curr_conversion_rate,
1222 X_curr_transaction_date,
1223 X_curr_transaction_date,
1224 X_vendor_income_tax_region,
1225 'ERS', -- reference_1
1226 TO_CHAR(X_rcv_txns.transaction_id),
1227 -- reference_2
1228 X_awt_flag,
1229 X_awt_group_id,
1230 X_curr_accounting_date,
1231 X_curr_period_name,
1232 'ERS', -- transaction_type
1233 X_rcv_txns.transaction_id,
1234 -- unique_id
1235 X_curr_invoice_amount,
1236 X_curr_inv_process_flag,
1237 X_rcv_txns.receipt_num,
1238 X_rcv_txns.transaction_id,
1239 /* Bug3493515 (2) - START */
1240 X_rcv_txns.match_option, -- NULL, -- Bug 6822594 Passing the match option
1241 X_received_amount,
1242 X_rcv_txns.matching_basis,
1243 X_unit_meas_lookup_code ); --5100177
1244 /* Bug3493515 (2) - END */
1245
1246 end if; -- end of if x_received_quantity <> 0
1247 END IF; -- X_curr_inv_process_flag
1248
1249 X_progress := '150';
1250
1251 /*** make sure to indicate this receipt transaction has been invoiced ***/
1252
1253 -- need to provide an API for AP instead
1254
1255 -- update invoice_status_code of 'RECEIVE', 'CORRECT' and
1256 -- 'RETURN TO VENDOR' transactions
1257
1258 UPDATE rcv_transactions
1259 SET invoice_status_code = DECODE(X_curr_inv_process_flag,'Y','INVOICED','REJECTED'), -- bug 3640106
1260 last_updated_by = FND_GLOBAL.user_id,
1261 last_update_date = sysdate,
1262 last_update_login = FND_GLOBAL.login_id
1263 WHERE transaction_id IN (
1264 SELECT
1265 transaction_id
1266 FROM
1267 rcv_transactions
1268 WHERE
1269 invoice_status_code <> 'INVOICED' AND
1270 transaction_type IN ('RECEIVE','CORRECT','RETURN TO VENDOR')
1271 START WITH transaction_id = X_rcv_txns.transaction_id
1272 CONNECT BY parent_transaction_id = PRIOR transaction_id
1273 );
1274
1275 END LOOP;
1276
1277 /*** Logic for the last invoice ***/
1278 X_progress := '160';
1279
1280 IF (X_first_rcv_txn_flag = 'N') AND
1281 (X_curr_inv_process_flag = 'Y')
1282 THEN
1283 /*Bug 5382916: Date in the description should be in LE Time zone*/
1284 fnd_message.set_name('PO', 'PO_INV_CR_ERS_INVOICE_DESC');
1285 X_progress := '100';
1286 fnd_message.set_token('RUN_DATE', x_curr_le_transaction_date);
1287 X_progress := '110';
1288 X_invoice_description := fnd_message.get;
1289
1290 IF (UPPER(x_curr_conversion_rate_type) <> 'USER') THEN
1291 x_curr_conversion_rate := NULL;
1292 END IF;
1293
1294 /* Removed the fix of 2379414 from here as it is already commented */
1295
1296 IF (g_asn_debug = 'Y') THEN
1297 asn_debug.put_line('creating invoice distributions');
1298 END IF;
1299
1300 /* Bug 7512542 Calling create_invoice_num here to ensure gapless invoicing */
1301
1302 IF (X_curr_inv_process_flag = 'Y') THEN
1303
1304 select count(*) into x_dist_count
1305 from ap_invoice_lines_interface
1306 where invoice_id = x_curr_invoice_id;
1307
1308 if (x_dist_count > 0 ) then
1309 BEGIN
1310
1311 X_curr_invoice_num :=
1312 po_invoices_sv2.create_invoice_num(
1313 X_curr_org_id,
1314 X_curr_pay_site_id,
1315 X_curr_por_summary_code,
1316 X_curr_le_transaction_date,
1317 X_curr_packing_slip,
1318 X_curr_receipt_num);
1319
1320 asn_debug.put_line('Invoice Number = ' ||X_curr_invoice_num);
1321
1322 EXCEPTION
1323 WHEN others THEN
1324 asn_debug.put_line('create_invoice_num raised error');
1325 X_curr_inv_process_flag := 'N';
1326 X_first_rcv_txn_flag := 'Y';
1327
1328 END;
1329 END IF;
1330
1331 END IF;
1332
1333 /* End Bug 7512542 */
1334
1335
1336 insert into AP_INVOICES_INTERFACE
1337 (INVOICE_ID,
1338 INVOICE_NUM,
1339 VENDOR_ID,
1340 VENDOR_SITE_ID,
1341 INVOICE_AMOUNT,
1342 INVOICE_CURRENCY_CODE,
1343 INVOICE_DATE,
1344 SOURCE,
1345 DESCRIPTION,
1346 GOODS_RECEIVED_DATE,
1347 INVOICE_RECEIVED_DATE,
1348 CREATION_DATE,
1349 EXCHANGE_RATE,
1350 EXCHANGE_RATE_TYPE,
1351 EXCHANGE_DATE,
1352 TERMS_ID,
1353 GROUP_ID,
1354 ORG_ID, -- Bug#2492041
1355 -- GL_DATE ,/* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
1356 INVOICE_TYPE_LOOKUP_CODE, --COMPLEX WORK
1357 CALC_TAX_DURING_IMPORT_FLAG,
1358 ADD_TAX_TO_INV_AMT_FLAG -- bug 5499478
1359 ) VALUES
1360 (x_curr_invoice_id,
1361 x_curr_invoice_num,
1362 x_curr_vendor_id,
1363 x_curr_pay_site_id,
1364 x_curr_invoice_amount,
1365 x_curr_currency_code,
1366 x_curr_le_transaction_date, --Bug 5205516: INVOICE_DATE in LE Time zone
1367 'ERS', -- debug, needs to change,
1368 x_invoice_description,
1369 x_curr_le_transaction_date,--Bug 5205516: GOODS_RECEIVIED_DATE in LE Time zone
1370 x_curr_le_transaction_date,--Bug 5205516: INVOICE_RECEIVIED_DATE in LE Time zone
1371 sysdate,
1372 x_curr_conversion_rate,
1373 x_curr_conversion_rate_type,
1374 x_curr_conversion_rate_date,
1375 X_curr_payment_terms_id,
1376 x_group_id,
1377 x_org_id,
1378 -- inv_le_timezone_pub.get_le_day_for_ou(x_curr_transaction_date, x_org_id),
1379 /* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
1380 x_curr_shipment_type,
1381 'Y',
1382 'Y'
1383 );
1384
1385 /*** We do not need to round our amounts here because
1386 the invoice amount and tax amount are calculated within
1387 the create_invoice_distributions and roundings are done
1388 there. ***/
1389
1390 /*** Update running totals ***/
1391 X_progress := '170';
1392
1393 X_invoice_count := X_invoice_count + 1;
1394 X_invoice_running_total := X_invoice_running_total + X_curr_invoice_amount;
1395
1396
1397 X_progress := '180';
1398
1399
1400 END IF; -- if X_rcv_first_flag
1401
1402 IF (g_asn_debug = 'Y') THEN
1403 asn_debug.put_line('Completed create receipt invoices program... ');
1404 END IF;
1405
1406 select count(*) into x_dist_count
1407 from ap_invoice_lines_interface
1408 where invoice_id = x_curr_invoice_id;
1409
1410 if (x_dist_count = 0 ) then
1411 x_invoice_count := x_invoice_count - 1;
1412 delete from ap_invoices_interface
1413 where invoice_id=x_curr_invoice_id;
1414 end if;
1415
1416
1417 /*
1418 ** if x_group_id is not null, then at least one record has been inserted.
1419 ** Then we need to run the AP import program
1420 */
1421
1422 IF (x_group_id is NOT NULL) THEN
1423 FND_PROFILE.GET('USER_ID', l_user_id);
1424
1425 /*Bug# 1539257 Building the batch name which was earlier NA */
1426 fnd_message.set_name('PO', 'PO_INV_CR_ERS_BATCH_DESC');
1427 X_batch_name := fnd_message.get;
1428
1429 SELECT ap_batches_s.nextval
1430 INTO X_tmp_batch_id
1431 FROM dual;
1432
1433 /* need to commit before we submit another conc request, since
1434 the request in another session */
1435
1436 COMMIT;
1437
1438
1439 /* <PAY ON USE FPI START> */
1440 /* fnd_request.submit_request has been replaced by
1441 PO_INVOICES_SV1.submit_invoice_import as a result of refactoring
1442 performed during FPI Consigned Inv project */
1443
1444 PO_INVOICES_SV1.submit_invoice_import(
1445 l_return_status,
1446 'ERS',
1447 x_group_id,
1448 x_batch_name || '/' || TO_CHAR(sysdate)
1449 || '/' || TO_CHAR(X_tmp_batch_id),
1450 l_user_id,
1451 0,
1452 v_req_id);
1453
1454 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1456 END IF;
1457
1458 /* <PAY ON USE FPI END> */
1459
1460 fnd_message.set_name('PO', 'PO_ERS_CONC_REQUEST_CHECK');
1461 fnd_message.set_token('REQUEST', TO_CHAR(v_req_id));
1462 fnd_message.set_token('BATCH', x_group_id);
1463
1464 IF (g_asn_debug = 'Y') THEN
1465 asn_debug.put_line(fnd_message.get);
1466 END IF;
1467 END IF;
1468
1469 RETURN (X_completion_status);
1470
1471 EXCEPTION
1472
1473 /* <PAY ON USE FPI START> */
1474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475 l_error_msg := FND_MSG_PUB.get(p_encoded => 'F');
1476 IF (g_asn_debug = 'Y') THEN
1477 ASN_DEBUG.put_line(l_error_msg);
1478 END IF;
1479 RAISE;
1480 /* <PAY ON USE FPI END> */
1481 WHEN others THEN
1482 IF (g_asn_debug = 'Y') THEN
1483 asn_debug.put_line('Error in Create Receipt Invoices ...');
1484 END IF;
1485 po_message_s.sql_error('create_receipt_invoices', X_progress, sqlcode);
1486 RAISE;
1487 END create_receipt_invoices;
1488
1489
1490 /*================================================================
1491
1492 PROCEDURE NAME: wrap_up_current_invoice()
1493
1494 ==================================================================*/
1495
1496
1497 /* R12 Complex work.
1498 * Add the x_new_shipment_type and
1499 * x_curr_shipment_type to wrap_up_current_invoice
1500 * procedure.
1501 */
1502 PROCEDURE WRAP_UP_CURRENT_INVOICE(X_new_vendor_id IN NUMBER,
1503 X_new_pay_site_id IN NUMBER,
1504 X_new_currency_code IN VARCHAR2,
1505 X_new_conversion_rate_type IN VARCHAR2,
1506 X_new_conversion_rate_date IN DATE,
1507 X_new_conversion_rate IN NUMBER,
1508 X_new_payment_terms_id IN NUMBER,
1509 X_new_transaction_date IN DATE,
1510 X_new_packing_slip IN VARCHAR2,
1511 X_new_shipment_header_id IN NUMBER,
1512 X_new_osa_flag IN VARCHAR2, --Shikyu project
1513 X_terms_date IN DATE,
1514 X_payment_priority IN VARCHAR2,
1515 /*Bug 586895*/ X_new_payment_code IN VARCHAR2,
1516 X_curr_method_code IN OUT NOCOPY VARCHAR2,
1520 X_curr_invoice_amount IN OUT NOCOPY NUMBER,
1517 /*Bug 612979*/ X_new_pay_curr_code IN VARCHAR2,
1518 X_curr_pay_curr_code IN OUT NOCOPY VARCHAR2,
1519 X_batch_id IN OUT NOCOPY NUMBER,
1521 X_curr_invoice_id IN OUT NOCOPY NUMBER,
1522 X_curr_vendor_id IN OUT NOCOPY NUMBER,
1523 X_curr_pay_site_id IN OUT NOCOPY NUMBER,
1524 X_curr_currency_code IN OUT NOCOPY VARCHAR2,
1525 X_curr_conversion_rate_type IN OUT NOCOPY VARCHAR2,
1526 X_curr_conversion_rate_date IN OUT NOCOPY DATE,
1527 X_curr_conversion_rate IN OUT NOCOPY NUMBER,
1528 X_curr_payment_terms_id IN OUT NOCOPY NUMBER,
1529 X_curr_transaction_date IN OUT NOCOPY DATE,
1530 X_curr_packing_slip IN OUT NOCOPY VARCHAR2,
1531 X_curr_shipment_header_id IN OUT NOCOPY NUMBER,
1532 X_curr_osa_flag IN OUT NOCOPY VARCHAR2, --Shikyu project
1533 X_curr_inv_process_flag IN OUT NOCOPY VARCHAR2,
1534 X_invoice_count IN OUT NOCOPY NUMBER,
1535 X_invoice_running_total IN OUT NOCOPY NUMBER ,
1536 X_new_shipment_type IN VARCHAR2,--Complex Work
1537 X_curr_shipment_type IN OUT NOCOPY VARCHAR2,--Complex Work
1538 X_org_id IN NUMBER, --Bug 5531203
1539 X_curr_le_transaction_date IN OUT NOCOPY DATE
1540 )
1541
1542 IS
1543
1544 X_progress VARCHAR2(3) := NULL;
1545 X_discountable_amount NUMBER;
1546 X_pay_cross_rate NUMBER;
1547 X_ap_pay_curr po_vendor_sites.payment_currency_code%TYPE;
1548
1549 BEGIN
1550 IF (g_asn_debug = 'Y') THEN
1551 asn_debug.put_line('Wrapping up the current invoice ... ');
1552 END IF;
1553
1554 IF (X_curr_inv_process_flag = 'Y') THEN
1555 X_progress := '010';
1556 /*** a new invoice needs to be created ... and we need to
1557 update the current one before the new one can be created. ***/
1558
1559 -- BUG 612979
1560
1561 IF (gl_currency_api.is_fixed_rate(X_curr_pay_curr_code, X_curr_currency_code, X_curr_transaction_date) = 'Y'
1562 and X_curr_pay_curr_code <> X_curr_currency_code) THEN
1563
1564 X_pay_cross_rate := gl_currency_api.get_rate(X_curr_currency_code,
1565 X_curr_pay_curr_code,
1566 X_curr_transaction_date,
1567 'EMU FIXED');
1568 ELSE
1569 X_pay_cross_rate := 1;
1570 END IF;
1571
1572 IF (X_pay_cross_rate = 1) THEN
1573 X_ap_pay_curr := X_curr_currency_code;
1574 ELSE
1575 X_ap_pay_curr := X_curr_pay_curr_code;
1576 END IF;
1577
1578 IF (g_asn_debug = 'Y') THEN
1579 asn_debug.put_line ('x_pay_cross_rate ='|| x_pay_cross_rate);
1580 asn_debug.put_line ('X_pay_curr_invoice_amount ='|| ap_utilities_pkg.ap_round_currency(X_curr_invoice_amount * X_pay_cross_rate, X_ap_pay_curr));
1581 END IF;
1582
1583 -- create invoice header here
1584
1585 /*** update the running totals ***/
1586 X_invoice_count := X_invoice_count + 1;
1587 X_invoice_running_total := X_invoice_running_total +
1588 X_curr_invoice_amount;
1589
1590 X_progress := '020';
1591
1592 END IF; -- curr_inv_process_flag
1593
1594 /*** assign the correct exchange rate info if currency changes ***/
1595 /*** remember the first occurrence of the exchange rate info will be
1596 used ***/
1597
1598
1599 /*** make sure the "current" variables are correct ***/
1600 X_progress := '080';
1601
1602 select ap_invoices_interface_s.nextval
1603 into x_curr_invoice_id
1604 from sys.dual;
1605
1606 X_curr_invoice_amount := 0;
1607 X_curr_vendor_id := X_new_vendor_id;
1608 X_curr_pay_site_id := X_new_pay_site_id;
1609 X_curr_currency_code := X_new_currency_code;
1610 X_curr_conversion_rate_type := X_new_conversion_rate_type;
1611 X_curr_conversion_rate := X_new_conversion_rate;
1612 X_curr_conversion_rate_date := X_new_conversion_rate_date;
1613 X_curr_payment_terms_id := X_new_payment_terms_id;
1614 X_curr_transaction_date := X_new_transaction_date;
1615 X_curr_packing_slip := X_new_packing_slip;
1616 X_curr_shipment_header_id := X_new_shipment_header_id;
1617 X_curr_osa_flag := X_new_osa_flag; --Shikyu project
1618 /** Bug 586895 **/
1619 X_curr_method_code := X_new_payment_code;
1620 X_curr_pay_curr_code := X_new_pay_curr_code;
1621 /* R12 Complex Work.
1622 * Populate the new shipment type to x_curr shipment_type.
1623 */
1624 X_curr_shipment_type := X_new_shipment_type;
1625
1626 --Bug 5531203
1627 X_curr_le_transaction_date :=
1628 INV_LE_TIMEZONE_PUB.GET_LE_DAY_TIME_FOR_OU(x_curr_transaction_date,x_org_id);
1629
1630
1631 EXCEPTION
1632 WHEN others THEN
1633 po_message_s.sql_error('wrap_up_current_invoice', x_progress,sqlcode);
1634 RAISE;
1635 END wrap_up_current_invoice;
1636
1637 /* =================================================================
1638
1639 FUNCTION NAME: create_invoice_num()
1640
1644 x_org_id IN NUMBER, -- SBI ENH
1641 ==================================================================*/
1642
1643 FUNCTION create_invoice_num (
1645 x_vendor_site_id IN NUMBER, -- SBI ENH
1646 x_pay_on_receipt_summary_code IN VARCHAR2,
1647 x_invoice_date IN DATE,
1648 x_packing_slip IN VARCHAR2,
1649 x_receipt_num IN VARCHAR2,
1650 p_source IN VARCHAR2 := NULL /* <PAY ON USE FPI> */
1651 )
1652 RETURN VARCHAR2
1653 IS
1654 x_progress VARCHAR2 (3) := NULL;
1655 x_tmp_sequence_id NUMBER;
1656 x_tmp_invoice_num ap_invoices.invoice_num%TYPE;
1657 x_prefix VARCHAR2 (20);
1658
1659 -- SBI ENH
1660 x_return_status VARCHAR2 (1);
1661 x_msg_data VARCHAR2 (2000);
1662 x_msg_count NUMBER;
1663 x_buying_company_identifier VARCHAR2 (10);
1664 x_selling_company_identifier VARCHAR2 (10);
1665 x_gapless_inv_num_flag_org VARCHAR2 (1);
1666 x_gapless_inv_num_flag_sup VARCHAR2 (1);
1667 x_invoice_num VARCHAR2 (45);
1668 -- SBI ENH
1669
1670 BEGIN
1671 IF (g_asn_debug = 'Y') THEN
1672 asn_debug.put_line ('Constructing Invoice Num for the invoice ... ');
1673 END IF;
1674
1675 x_progress := '001';
1676
1677 -- BugFix 5197828
1678 -- Above if condition is commented since we need to keep all the code realted to gapless number
1679 -- under one procedure. Let rcv_gapless_numbering.generate_invoice_number decide whether to
1680 -- create gapless numbers or not.
1681
1682 /*
1683 po_ap_integration_grp.get_invoice_numbering_options (1,
1684 x_org_id,
1685 x_return_status,
1686 x_msg_data,
1687 x_buying_company_identifier,
1688 x_gapless_inv_num_flag_org
1689 );
1690
1691 x_progress := '002';
1692
1693 AP_PO_GAPLESS_SBI_PKG.site_uses_gapless_num (x_vendor_site_id,
1694 x_gapless_inv_num_flag_sup,
1695 x_selling_company_identifier
1696 );
1697 x_progress := '003';
1698
1699 IF (x_gapless_inv_num_flag_org = 'Y' or x_gapless_inv_num_flag_sup = 'Y') THEN -- SBI ENH
1700 rcv_gapless_numbering.generate_invoice_number (1,
1701 x_buying_company_identifier,
1702 x_selling_company_identifier,
1703 'ERS',
1704 x_invoice_num,
1705 x_return_status,
1706 x_msg_count,
1707 x_msg_data
1708 );
1709
1710 x_progress := '004';
1711
1712 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1713 RETURN x_invoice_num;
1714 ELSE
1715 RAISE create_invoice_error;
1716 END IF;
1717 END IF;
1718 */
1719
1720 rcv_gapless_numbering.generate_invoice_number ( 1 ,
1721 x_org_id ,
1722 x_vendor_site_id,
1723 'ERS' ,
1724 x_invoice_num ,
1725 x_return_status ,
1726 x_msg_count ,
1727 x_msg_data
1728 );
1729 IF (g_asn_debug = 'Y') THEN
1730 asn_debug.put_line ('rcv_gapless_numbering.generate_invoice_number returned with status = ' || x_return_status );
1731 asn_debug.put_line ('x_invoice_num = ' || x_invoice_num );
1732 END IF;
1733
1734 IF (x_return_status = fnd_api.g_ret_sts_success and x_invoice_num is NOT NULL)
1735 THEN
1736 RETURN x_invoice_num;
1737 ELSIF (x_return_status <> fnd_api.g_ret_sts_success)
1738 THEN
1739 RAISE create_invoice_error;
1740 ELSE
1741 NULL;
1742 END IF;
1743
1744 -- End of code for BugFix 5197828
1745
1746
1747 x_progress := '010';
1748
1749 SELECT po_invoice_num_segment_s.NEXTVAL
1750 INTO x_tmp_sequence_id
1751 FROM SYS.DUAL;
1752
1753 x_progress := '020';
1754
1755 /* <PAY ON USE FPI START> */
1756 IF (p_source = 'USE') THEN
1757 x_progress := '025';
1758 x_tmp_invoice_num :=
1759 'USE-'
1760 || TO_CHAR (x_invoice_date)
1761 || '-'
1762 || TO_CHAR (x_tmp_sequence_id);
1763 ELSE
1764 /* <PAY ON USE FPI END> */
1765
1766 -- Use Profile option to determine prefix
1770 IF (x_pay_on_receipt_summary_code = 'PAY_SITE') THEN
1767 fnd_profile.get ('ERS_PREFIX', x_prefix);
1768 x_progress := '030';
1769
1771 x_progress := '040';
1772 x_tmp_invoice_num :=
1773 x_prefix
1774 || '-'
1775 || TO_CHAR (x_invoice_date)
1776 || '-'
1777 || TO_CHAR (x_tmp_sequence_id);
1778 ELSIF (x_pay_on_receipt_summary_code = 'PACKING_SLIP') THEN
1779 x_progress := '050';
1780 x_tmp_invoice_num :=
1781 x_prefix
1782 || '-'
1783 || x_packing_slip
1784 || '-'
1785 || TO_CHAR (x_tmp_sequence_id);
1786 ELSIF (x_pay_on_receipt_summary_code = 'RECEIPT') THEN
1787 x_progress := '060';
1788 x_tmp_invoice_num :=
1789 x_prefix
1790 || '-'
1791 || x_receipt_num
1792 || '-'
1793 || TO_CHAR (x_tmp_sequence_id);
1794 END IF; -- x_pay_on_receipt_summary_code
1795 END IF; -- p_source
1796
1797 RETURN (x_tmp_invoice_num);
1798 EXCEPTION
1799 WHEN create_invoice_error THEN
1800 FND_MESSAGE.SET_NAME('PO','RCV_CREATE_INVOICE_NUM_ERROR');
1801 FND_MESSAGE.SET_TOKEN('RECEIPT_NUM',x_receipt_num);
1802 FND_MESSAGE.SET_TOKEN('REASON',x_msg_data);
1803 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1804 RAISE create_invoice_error;
1805 WHEN OTHERS THEN
1806 po_message_s.sql_error ('create_invoice_num', x_progress, SQLCODE);
1807 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1808 RAISE;
1809 END create_invoice_num;
1810
1811 /* =====================================================================
1812
1813 PROCEDURE get_received_quantity
1814
1815 ======================================================================== */
1816
1817 PROCEDURE get_received_quantity( X_transaction_id IN NUMBER,
1818 X_shipment_line_id IN NUMBER,
1819 X_received_quantity IN OUT NOCOPY NUMBER,
1820 X_match_option IN VARCHAR2 DEFAULT NULL ) IS --5100177
1821
1822 X_current_quantity NUMBER := 0;
1823 X_primary_uom VARCHAR2(25) := '';
1824 X_po_uom VARCHAR2(25) := '';
1825 X_item_id NUMBER := 0;
1826
1827 v_primary_uom VARCHAR2(25) := '';
1828 v_po_uom VARCHAR2(25) := '';
1829 v_txn_id NUMBER := 0;
1830 v_primary_quantity NUMBER := 0;
1831 v_transaction_type VARCHAR2(25) := '';
1832 v_parent_id NUMBER := 0;
1833 v_parent_type VARCHAR2(25) := '';
1834 v_transaction_uom VARCHAR2(25) := ''; -- Added for bug 6822594
1835 v_to_uom VARCHAR2(25) := ''; -- Added for bug 6822594
1836 X_receipt_uom VARCHAR2(25) := ''; -- Added for bug 6822594
1837
1838 CURSOR c_txn_history (c_transaction_id NUMBER) IS
1839 select
1840 transaction_id,
1841 primary_quantity,
1842 primary_unit_of_measure,
1843 source_doc_unit_of_measure,
1844 transaction_type,
1845 parent_transaction_id,
1846 unit_of_measure -- Added for bug 6822594 : To get the transaction uom
1847 from
1848 rcv_transactions
1849 where
1850 invoice_status_code <> 'INVOICED'
1851 start with transaction_id = c_transaction_id
1852 connect by parent_transaction_id = prior transaction_id;
1853
1854 BEGIN
1855
1856 OPEN c_txn_history(X_transaction_id);
1857
1858 LOOP
1859 FETCH c_txn_history INTO v_txn_id,
1860 v_primary_quantity,
1861 v_primary_uom,
1862 v_po_uom,
1863 v_transaction_type,
1864 v_parent_id,
1865 v_transaction_uom; -- Added for bug 6822594
1866
1867 EXIT WHEN c_txn_history%NOTFOUND;
1868
1869 IF v_transaction_type = 'RECEIVE' THEN
1870
1871 select
1872 item_id
1873 into
1874 X_item_id
1875 from
1876 rcv_shipment_lines
1877 where
1878 shipment_line_id = X_shipment_line_id;
1879
1880 X_current_quantity := v_primary_quantity;
1881 X_primary_uom := v_primary_uom;
1882 X_po_uom := v_po_uom;
1883 X_receipt_uom := v_transaction_uom; -- Added for bug 6822594
1884
1885 ELSIF v_transaction_type = 'CORRECT' THEN
1886
1887 select
1888 transaction_type
1889 into
1890 v_parent_type
1891 from
1892 rcv_transactions
1893 where
1894 transaction_id = v_parent_id;
1895
1896 IF v_parent_type = 'RECEIVE' THEN
1897 X_current_quantity := X_current_quantity + v_primary_quantity;
1898 ELSIF v_parent_type = 'RETURN TO VENDOR' THEN
1899 X_current_quantity := X_current_quantity - v_primary_quantity;
1900 END IF;
1901
1902 ELSIF v_transaction_type = 'RETURN TO VENDOR' THEN
1903
1904 X_current_quantity := X_current_quantity - v_primary_quantity;
1905
1906 END IF;
1907
1908 END LOOP;
1909
1910 CLOSE c_txn_history;
1911
1912 /* Added debug messages to identify the uoms for which the uom convert function failed.
1913 For this enclosed the uom_convert function in a begin,exception and end block.
1914 Bug 2923345. */
1915
1916 /* Bug 5100177.
1917 * Convert the receipt quantity to PO quantity only when the
1918 * match option for the PO shipment is PO.
1919 */
1920
1921 /* Bug 6822594
1922 * Handling the conversion of received quantity from primary uom to
1923 * po uom or transaction uom depending upon the match option at PO shipment.
1924 * i.e If Match Option = 'PO' then into PO UOM else in TRANSACTION UOM
1925 */
1926
1927 IF (X_match_option = 'P') then
1928 v_to_uom := X_po_uom;
1929 ELSIF (X_match_option = 'R') then
1930 v_to_uom := X_receipt_uom;
1931 END IF;
1932
1933 IF (X_primary_uom <> v_to_uom) then
1934 BEGIN
1935
1936 po_uom_s.uom_convert(X_current_quantity,
1937 X_primary_uom,
1938 X_item_id,
1939 v_to_uom, --Changed from X_po_uom to v_to_uom
1940 X_received_quantity);
1941 exception
1942 WHEN NO_DATA_FOUND THEN
1943
1944 IF (g_asn_debug = 'Y') THEN
1945 asn_debug.put_line('conversion not defined between uoms '||x_primary_uom||' and '||X_po_uom);
1946
1947 END IF;
1948 RAISE;
1949
1950 WHEN OTHERS THEN
1951
1952 IF (g_asn_debug = 'Y') THEN
1953 asn_debug.put_line('Exception occured while converting from uom '||x_primary_uom||' to uom '||X_po_uom);
1954 asn_debug.put_line('Check if conversion exists between uoms '||x_primary_uom||' and '||X_po_uom);
1955 END IF;
1956 RAISE;
1957 END;
1958 ELSE
1959 X_received_quantity := X_current_quantity;
1960 END IF;
1961
1962
1963 END get_received_quantity;
1964
1965 /* =====================================================================
1966
1967 PROCEDURE get_received_amount
1968
1969 ======================================================================== */
1970
1971 PROCEDURE get_received_amount( X_transaction_id IN NUMBER,
1972 X_shipment_line_id IN NUMBER,
1973 X_received_amount IN OUT NOCOPY NUMBER) IS
1974
1975 l_current_amount NUMBER := 0;
1976 v_txn_id NUMBER := 0;
1977 v_amount NUMBER := 0;
1978 v_transaction_type VARCHAR2(25) := '';
1979 v_parent_id NUMBER := 0;
1980 v_parent_type VARCHAR2(25) := '';
1981
1982 CURSOR c_txn_history (c_transaction_id NUMBER) IS
1983 select
1984 transaction_id,
1985 amount,
1986 transaction_type,
1987 parent_transaction_id
1988 from
1989 rcv_transactions
1990 where
1991 invoice_status_code <> 'INVOICED'
1992 start with transaction_id = c_transaction_id
1993 connect by parent_transaction_id = prior transaction_id;
1994
1995 BEGIN
1996
1997 OPEN c_txn_history(X_transaction_id);
1998
1999 LOOP
2000 FETCH c_txn_history INTO v_txn_id,
2001 v_amount,
2002 v_transaction_type,
2003 v_parent_id;
2004
2005 EXIT WHEN c_txn_history%NOTFOUND;
2006
2007 IF v_transaction_type = 'RECEIVE' THEN
2008
2009 l_current_amount := v_amount;
2010
2011 ELSIF v_transaction_type = 'CORRECT' THEN
2012
2013 select
2014 transaction_type
2015 into
2016 v_parent_type
2017 from
2018 rcv_transactions
2019 where
2020 transaction_id = v_parent_id;
2021
2022 IF v_parent_type = 'RECEIVE' THEN
2023 l_current_amount := l_current_amount + v_amount;
2024 ELSIF v_parent_type = 'RETURN TO VENDOR' THEN
2025 l_current_amount := l_current_amount - v_amount;
2026 END IF;
2027
2028 ELSIF v_transaction_type = 'RETURN TO VENDOR' THEN
2029
2030 l_current_amount := l_current_amount - v_amount;
2031
2032 END IF;
2033
2034 END LOOP;
2035
2036 CLOSE c_txn_history;
2037
2038 X_received_amount := l_current_amount; /* Bug3493515 (1) */
2039
2040 END get_received_amount;
2041
2042 /********************************************************************/
2043 /* */
2044 /* PROCEDURE create_invoice_distributions */
2045 /* */
2046 /********************************************************************/
2047
2048
2049 PROCEDURE create_invoice_distributions(X_invoice_id IN NUMBER,
2050 X_invoice_currency_code IN VARCHAR2,
2051 X_base_currency_code IN VARCHAR2,
2052 X_batch_id IN NUMBER,
2053 X_pay_site_id IN NUMBER,
2054 X_po_header_id IN NUMBER,
2055 X_po_line_id IN NUMBER,
2059 X_po_distribution_id IN NUMBER,
2056 X_po_line_location_id IN NUMBER,
2057 X_po_release_id IN NUMBER, -- bug 901039
2058 X_receipt_event IN VARCHAR2,
2060
2061 /*X_receipt_event and X_po_distribution_id
2062 used only for DELIVER transactions*******/
2063
2064 X_item_description IN VARCHAR2,
2065 X_type_1099 IN VARCHAR2,
2066 X_tax_code_id IN NUMBER,
2067
2068 X_quantity IN NUMBER,
2069 X_unit_price IN NUMBER,
2070 X_exchange_rate_type IN VARCHAR2,
2071 X_exchange_date IN DATE,
2072 X_exchange_rate IN NUMBER,
2073 X_invoice_date IN DATE,
2074 X_receipt_date IN DATE,
2075 X_vendor_income_tax_region IN VARCHAR2,
2076 X_reference_1 IN VARCHAR2,
2077 X_reference_2 IN VARCHAR2,
2078 X_awt_flag IN VARCHAR2,
2079 X_awt_group_id IN NUMBER,
2080 X_accounting_date IN DATE,
2081 X_period_name IN VARCHAR2,
2082 X_transaction_type IN VARCHAR2,
2083 X_unique_id IN NUMBER,
2084 X_curr_invoice_amount IN OUT NOCOPY NUMBER,
2085 X_curr_inv_process_flag IN OUT NOCOPY VARCHAR2,
2086 X_receipt_num IN VARCHAR2 DEFAULT NULL,
2087 X_rcv_transaction_id IN NUMBER DEFAULT NULL,
2088 X_match_option IN VARCHAR2 DEFAULT NULL,
2089 X_amount IN NUMBER DEFAULT NULL,
2090 X_matching_basis IN VARCHAR2 DEFAULT 'QUANTITY',
2091 X_unit_meas_lookup_code IN VARCHAR2 DEFAULT NULL ) --5100177
2092 IS
2093
2094
2095 /*** Cursor Declaration ***/
2096 /* Bug 3338268 - removed X_receipt_event */
2097 CURSOR c_po_distributions(X_po_header_id NUMBER,
2098 X_po_line_id NUMBER,
2099 X_po_line_location_id NUMBER,
2100 X_po_distribution_id NUMBER
2101 )
2102 IS
2103 SELECT pod.po_distribution_id,
2104 pod.set_of_books_id,
2105 pod.code_combination_id,
2106 DECODE(gcc.account_type, 'A','Y','N') assets_tracking_flag,
2107 NVL(pod.quantity_ordered,0) quantity_remaining,
2108 NVL(pod.amount_ordered,0) amount_remaining,
2109 pod.rate,
2110 pod.rate_date,
2111 pod.variance_account_id,
2112 pod.attribute_category,
2113 pod.attribute1,
2114 pod.attribute2,
2115 pod.attribute3,
2116 pod.attribute4,
2117 pod.attribute5,
2118 pod.attribute6,
2119 pod.attribute7,
2120 pod.attribute8,
2121 pod.attribute9,
2122 pod.attribute10,
2123 pod.attribute11,
2124 pod.attribute12,
2125 pod.attribute13,
2126 pod.attribute14,
2127 pod.attribute15,
2128 pod.project_id, -- the following are PA related columns
2129 pod.task_id,
2130 pod.expenditure_item_date,
2131 pod.expenditure_type,
2132 pod.expenditure_organization_id,
2133 pod.project_accounting_context,
2134 pod.recovery_rate
2135 FROM gl_code_combinations gcc,
2136 po_distributions_ap_v pod
2137 WHERE pod.po_header_id = X_po_header_id
2138 AND pod.po_line_id = X_po_line_id
2139 AND pod.line_location_id = X_po_line_location_id
2140 AND pod.code_combination_id = gcc.code_combination_id
2141 AND pod.po_distribution_id = X_po_distribution_id
2142 ORDER BY pod.distribution_num;
2143
2144
2145 /**** Variable declarations ****/
2146
2147 /* Bug 3338268 */
2148 x_pod_distribution_id po_distributions.po_distribution_id%TYPE := NULL;
2149 x_pod_set_of_books_id po_distributions.set_of_books_id%TYPE := NULL;
2150 x_pod_code_combinations_id po_distributions.code_combination_id%TYPE := NULL;
2151 x_pod_assets_tracking_flag VARCHAR2(1) := NULL;
2152 x_pod_quantity_remaining po_distributions.quantity_ordered%TYPE := NULL;
2153 x_pod_amount_remaining po_distributions.amount_ordered%TYPE := NULL;
2154 x_pod_rate po_distributions.rate%TYPE := NULL;
2155 x_pod_rate_date po_distributions.rate_date%TYPE := NULL;
2156 x_pod_variance_account_id po_distributions.variance_account_id%TYPE := NULL;
2157 x_pod_attribute_category po_distributions.attribute_category%TYPE := NULL;
2158 x_pod_attribute1 po_distributions.attribute1%TYPE := NULL;
2159 x_pod_attribute2 po_distributions.attribute2%TYPE := NULL;
2160 x_pod_attribute3 po_distributions.attribute3%TYPE := NULL;
2161 x_pod_attribute4 po_distributions.attribute4%TYPE := NULL;
2162 x_pod_attribute5 po_distributions.attribute5%TYPE := NULL;
2163 x_pod_attribute6 po_distributions.attribute6%TYPE := NULL;
2164 x_pod_attribute7 po_distributions.attribute7%TYPE := NULL;
2165 x_pod_attribute8 po_distributions.attribute8%TYPE := NULL;
2166 x_pod_attribute9 po_distributions.attribute9%TYPE := NULL;
2170 x_pod_attribute13 po_distributions.attribute13%TYPE := NULL;
2167 x_pod_attribute10 po_distributions.attribute10%TYPE := NULL;
2168 x_pod_attribute11 po_distributions.attribute11%TYPE := NULL;
2169 x_pod_attribute12 po_distributions.attribute12%TYPE := NULL;
2171 x_pod_attribute14 po_distributions.attribute14%TYPE := NULL;
2172 x_pod_attribute15 po_distributions.attribute15%TYPE := NULL;
2173 x_pod_project_id po_distributions.project_id%TYPE := NULL;
2174 x_pod_task_id po_distributions.task_id%TYPE := NULL;
2175 x_pod_expenditure_item_date po_distributions.expenditure_item_date%TYPE := NULL;
2176 x_pod_expenditure_type po_distributions.expenditure_type%TYPE := NULL;
2177 x_pod_expenditure_org_id po_distributions.expenditure_organization_id%TYPE := NULL;
2178 x_pod_proj_accounting_context po_distributions.project_accounting_context%TYPE := NULL;
2179 /* End Bug 3338268 */
2180 /* Bug3875677 */
2181 x_pod_recovery_rate po_distributions.recovery_rate%TYPE := NULL;
2182
2183 X_rowid VARCHAR2(50);
2184 X_po_distributions c_po_distributions%ROWTYPE;
2185
2186 X_invoice_distribution_id NUMBER;
2187
2188 X_curr_qty NUMBER; /*Qty billed to a particular dist*/
2189 X_curr_amount NUMBER;
2190 X_sum_order_qty NUMBER; /*Used when proration is used*/
2191 X_sum_order_amt NUMBER;
2192
2193 /* nwang 5/13/1999 */
2194
2195 X_count NUMBER:=0; /*Num of distrs for that receive txn*/
2196 X_tmp_count NUMBER;
2197 X_total_amount NUMBER;
2198 X_amount_running_total NUMBER;
2199 X_assets_addition_flag VARCHAR2(1);
2200
2201 X_new_dist_line_number ap_invoice_distributions.distribution_line_number%TYPE;
2202 X_base_amount NUMBER;
2203
2204 X_conversion_rate NUMBER; -- This is the rate based of match option.
2205 X_conversion_rate_date DATE;
2206
2207 X_progress VARCHAR2(3) := '';
2208 x_invoice_line_id NUMBER;
2209 X_line_count NUMBER;
2210 x_org_id NUMBER; --Bug# 2492041
2211
2212 /*Bug: 5125624*/
2213 l_ship_to_location_id PO_LINE_LOCATIONS.SHIP_TO_LOCATION_ID%TYPE;
2214 l_tax_classification_code VARCHAR2(30);
2215 x_invoiced_quantity NUMBER :=0; -- Added for bug 6822594
2216 x_invoiced_unit_price NUMBER :=0; -- Added for bug 6822594
2217 X_invoiced_amount NUMBER :=0; -- Added for bug 6822594
2218 x_item_id po_lines_all.item_id%type ; -- Added for bug 6822594
2219 x_po_uom po_lines_all.unit_meas_lookup_code%type; -- Added for bug 6822594
2220 X_conversion_factor NUMBER :=0; -- Added for bug 6822594
2221
2222 BEGIN
2223 IF (g_asn_debug = 'Y') THEN
2224 asn_debug.put_line('Begin Create Invoice Distributions ');
2225 END IF;
2226
2227 /********************
2228 The algorithm for proration is as follows:
2229 Suppose there are 1..N distributions that need to be prorated.
2230 Sum of all the N distributions need to be prorated.
2231 Sum of all the N distribution qtys = X_total_qty
2232 Qty to be prorated = X_qty
2233
2234 Then for i = 1..N-1 prorated_qty(i) = X_qty*distribution_qty(i)/
2235 X_total_qty
2236 for i = N (the last distribution)prorated_qty(i) = X_qty-
2237 SUM(prorated_qty from q to N-1)
2238
2239 In this way, the last distribution will handle any rounding errors
2240 which might occur.
2241 *********************/
2242
2243 /***Find out how many distribution records and total ordered qty that
2244 need to be prorated.***/
2245
2246 X_progress := '010';
2247
2248 /* Fix for bug 3050752.
2249 Getting tax rounding rule from po_vendor_sites for the
2250 associated PO vendor site.
2251 */
2252
2253
2254 SELECT COUNT(*),
2255 SUM(NVL(quantity_ordered,0)),
2256 SUM(NVL(amount_ordered,0))
2257 /***Amount remaining for each po distribution***/
2258 INTO X_count,
2259 X_sum_order_qty,
2260 X_sum_order_amt
2261 FROM po_distributions
2262 WHERE po_header_id = X_po_header_id
2263 AND po_line_id = X_po_line_id
2264 AND line_location_id = X_po_line_location_id
2265 AND DECODE(X_receipt_event, 'DELIVER', po_distribution_id,1)=
2266 DECODE(X_receipt_event, 'DELIVER', X_po_distribution_id,1);
2267
2268 /* Removed the fix of 2379414 as it is already commented */
2269
2270 IF (X_count > 0) THEN
2271
2272 X_progress := '020';
2273
2274 IF X_matching_basis = 'AMOUNT' THEN
2275 X_total_amount := X_amount;
2276 ELSE
2277 X_total_amount := ap_utilities_pkg.ap_round_currency(
2278 X_quantity * X_unit_price,
2279 X_invoice_currency_code);
2280 END IF;
2281
2282 X_tmp_count := 0;
2283 X_amount_running_total := 0;
2284
2285 X_progress := '030';
2286
2287
2288 --Bug 3338268 call only if x_receipt_event='DELIVER', remove x_receipt_event, fetch only one record
2289 IF (x_receipt_event = 'DELIVER') THEN
2290 OPEN c_po_distributions(X_po_header_id,
2291 X_po_line_id,
2292 X_po_line_location_id,
2296 x_pod_distribution_id,
2293 X_po_distribution_id
2294 );
2295 FETCH c_po_distributions INTO
2297 x_pod_set_of_books_id,
2298 x_pod_code_combinations_id,
2299 x_pod_assets_tracking_flag,
2300 x_pod_quantity_remaining,
2301 x_pod_amount_remaining,
2302 x_pod_rate,
2303 x_pod_rate_date,
2304 x_pod_variance_account_id,
2305 x_pod_attribute_category,
2306 x_pod_attribute1,
2307 x_pod_attribute2,
2308 x_pod_attribute3,
2309 x_pod_attribute4,
2310 x_pod_attribute5,
2311 x_pod_attribute6,
2312 x_pod_attribute7,
2313 x_pod_attribute8,
2314 x_pod_attribute9,
2315 x_pod_attribute10,
2316 x_pod_attribute11,
2317 x_pod_attribute12,
2318 x_pod_attribute13,
2319 x_pod_attribute14,
2320 x_pod_attribute15,
2321 x_pod_project_id,
2322 x_pod_task_id,
2323 x_pod_expenditure_item_date,
2324 x_pod_expenditure_type,
2325 x_pod_expenditure_org_id,
2326 x_pod_proj_accounting_context,
2327 x_pod_recovery_rate;
2328 CLOSE c_po_distributions;
2329
2330 /* Bug3493515 (3) - Start */
2331 IF X_matching_basis = 'AMOUNT' THEN
2332 X_curr_amount:= (X_amount * X_pod_amount_remaining )/X_sum_order_amt;
2333 ELSE
2334 X_curr_qty := (X_quantity * X_pod_quantity_remaining)/X_sum_order_qty;
2335 END IF;
2336 /* Bug3493515 (3) - End */
2337
2338 ELSE
2339 x_curr_amount := x_amount;
2340 x_curr_qty := x_quantity;
2341 END IF;
2342
2343 X_progress := '085';
2344 /*Bug#2492041 Get the Operating Unit for the PO */
2345 select org_id
2346 into x_org_id
2347 from po_headers_all
2348 where po_header_id = X_po_header_id;
2349
2350 --Bug 3338268 - remove line item amt and qty calculations
2351 X_progress := '100';
2352
2353 IF X_matching_basis <> 'AMOUNT' THEN
2354 X_curr_amount := ap_utilities_pkg.ap_round_currency(
2355 X_curr_qty * X_unit_price,
2356 X_invoice_currency_code);
2357 END IF;
2358
2359 X_progress := '140';
2360
2361 IF (X_curr_inv_process_flag = 'Y') THEN
2362 /** continue only if invoice is still processable **/
2363
2364 X_progress := '160';
2365
2366 IF (X_invoice_currency_code = X_base_currency_code) THEN
2367 X_base_amount := NULL;
2368 ELSE
2369 X_base_amount := ap_utilities_pkg.ap_round_currency(
2370 X_curr_amount * X_conversion_rate,
2371 X_base_currency_code);
2372 END IF;
2373
2374 /*** call object handler to create the item distributions ***/
2375 X_progress := '140';
2376
2377 IF (g_asn_debug = 'Y') THEN
2378 asn_debug.put_line('Creating Item Distribution...');
2379 END IF;
2380
2381
2382 SELECT NVL(MAX(line_number), 0) + 1
2383 INTO X_line_count
2384 FROM ap_invoice_lines_interface
2385 WHERE invoice_id = x_invoice_id;
2386
2387 select ap_invoice_lines_interface_s.nextval
2388 into x_invoice_line_id
2389 from sys.dual;
2390
2391 /* Bug 1374789: We should not pass project_id and task_id
2392 ** to AP bas for some reason the AP import program fails
2393 ** with inconsistent distribution info error.
2394 ** This is being removed based on APs suggestion.
2395 ** PROJECT_ID, TASK_ID,
2396 ** x_po_distributions.project_id, x_po_distributions.task_id,
2397 */
2398
2399 /* Bug 2664078 - Since the accounting date is passed as the invoice date
2400 the Payables Open Interface import program is no considering the
2401 gl date basis. */
2402
2403 -- Bug 3338268 - Use variables instead of record
2404 /*Bug: 5125624*/
2405 l_ship_to_location_id := get_ship_to_location_id(x_po_line_location_id);
2406 l_tax_classification_code := get_tax_classification_code(x_po_header_id,x_po_line_location_id,'PURCHASE_ORDER');
2407
2408 /* Bug 6822594
2409 * Description: Passing the Received qty,unit price,amount in Receipt UOM
2410 ncase if match option = 'Receipt' on PO shipment and in PO UOM incase of
2411 match option = 'PO'.
2412 */
2413
2414
2415 IF X_match_option = 'R'
2416 THEN
2417
2418 SELECT item_id,UNIT_MEAS_LOOKUP_CODE
2419 INTO x_item_id , x_po_uom
2420 FROM po_lines_all
2421 WHERE PO_LINE_ID = X_po_line_id;
2422
2423 X_invoiced_quantity := X_quantity;
2424 -- Quantity in Receipt uom
2425 X_conversion_factor := po_uom_s.po_uom_convert(X_unit_meas_lookup_code,
2426 X_po_uom,
2427 X_item_id);
2428 -- UOM conversion factor
2429 X_invoiced_unit_price := X_conversion_factor * X_unit_price;
2430 -- PO unit price in receipt uom
2431 x_invoiced_amount := ap_utilities_pkg.ap_round_currency(
2432 X_invoiced_quantity * X_invoiced_unit_price,
2433 X_invoice_currency_code);
2434 -- Amount in Receipt uom.
2435
2439 X_invoiced_amount := X_curr_amount;
2436 ELSIF X_match_option = 'P'
2437 THEN
2438 X_invoiced_quantity := X_curr_qty;
2440 X_invoiced_unit_price := X_unit_price ;
2441 END IF;
2442
2443 insert into ap_invoice_lines_interface
2444 (INVOICE_ID,
2445 INVOICE_LINE_ID,
2446 LINE_NUMBER,
2447 LINE_TYPE_LOOKUP_CODE,
2448 AMOUNT,
2449 -- ACCOUNTING_DATE, Bug 2664078
2450 DESCRIPTION,
2451 TAX_CODE_ID,
2452 AMOUNT_INCLUDES_TAX_FLAG,
2453 -- DIST_CODE_COMBINATION_ID,
2454 PO_HEADER_ID,
2455 PO_LINE_ID,
2456 PO_LINE_LOCATION_ID,
2457 PO_DISTRIBUTION_ID,
2458 PO_RELEASE_ID,
2459 QUANTITY_INVOICED,
2460 EXPENDITURE_ITEM_DATE,
2461 EXPENDITURE_TYPE,
2462 EXPENDITURE_ORGANIZATION_ID,
2463 PROJECT_ACCOUNTING_CONTEXT,
2464 PA_QUANTITY,
2465 PA_ADDITION_FLAG,
2466 UNIT_PRICE,
2467 ASSETS_TRACKING_FLAG,
2468 ATTRIBUTE_CATEGORY,
2469 ATTRIBUTE1,
2470 ATTRIBUTE2,
2471 ATTRIBUTE3,
2472 ATTRIBUTE4,
2473 ATTRIBUTE5,
2474 ATTRIBUTE6,
2475 ATTRIBUTE7,
2476 ATTRIBUTE8,
2477 ATTRIBUTE9,
2478 ATTRIBUTE10,
2479 ATTRIBUTE11,
2480 ATTRIBUTE12,
2481 ATTRIBUTE13,
2482 ATTRIBUTE14,
2483 ATTRIBUTE15,
2484 MATCH_OPTION,
2485 RCV_TRANSACTION_ID,
2486 RECEIPT_NUMBER,
2487 TAX_CODE_OVERRIDE_FLAG, -- Bug 921579, PO needs to pass 'Y' for this
2488 ORG_ID, -- Bug#2492041
2489 TAX_RECOVERY_RATE, -- Bug 3875677
2490 UNIT_OF_MEAS_LOOKUP_CODE, -- 5100177
2491 SHIP_TO_LOCATION_ID, --Bug: 5125624
2492 TAX_CLASSIFICATION_CODE --Bug: 5125624
2493 ) VALUES
2494 (x_invoice_id,
2495 x_invoice_line_id,
2496 x_line_count,
2497 'ITEM',
2498 X_invoiced_amount, --X_curr_amount, for bug 6822594
2499 --x_invoice_date, Bug 2664078
2500 x_item_description,
2501 x_tax_code_id,
2502 NULL,
2503 -- x_po_distributions.code_combination_id,
2504 x_po_header_id,
2505 x_po_line_id,
2506 x_po_line_location_id,
2507 x_pod_distribution_id,
2508 x_po_release_id,
2509 X_invoiced_quantity, -- x_curr_qty,--X_curr_amount, for bug 6822594
2510 x_pod_expenditure_item_date,
2511 x_pod_expenditure_type,
2512 x_pod_expenditure_org_id,
2513 x_pod_proj_accounting_context,
2514 X_invoiced_quantity, -- x_curr_qty, for bug 6822594
2515 'N',
2516 X_invoiced_unit_price, --x_unit_price,bug 6822594
2517 x_pod_assets_tracking_flag,
2518 x_pod_attribute_CATEGORY,
2519 x_pod_attribute1,
2520 x_pod_attribute2,
2521 x_pod_attribute3,
2522 x_pod_attribute4,
2523 x_pod_attribute5,
2524 x_pod_attribute6,
2525 x_pod_attribute7,
2526 x_pod_attribute8,
2527 x_pod_attribute9,
2528 x_pod_attribute10,
2529 x_pod_attribute11,
2530 x_pod_attribute12,
2531 x_pod_attribute13,
2532 x_pod_attribute14,
2533 x_pod_attribute15,
2534 x_match_option,
2535 x_rcv_transaction_id,
2536 x_receipt_num,
2537 'Y', -- bug 921579, PO needs to pass 'Y' for this
2538 x_org_id,
2539 x_pod_recovery_rate,
2540 X_unit_meas_lookup_code, -- 5100177
2541 l_ship_to_location_id,
2542 l_tax_classification_code
2543 );
2544
2545
2546 /**UPDATE CURRENT INVOICE AMOUNT**/
2547 X_progress := '150';
2548 X_curr_invoice_amount:= X_curr_invoice_amount +
2549 X_invoiced_amount ; -- 6822594
2550
2551 END IF; -- X_curr_inv_process_flag
2552
2553 X_progress := '180';
2554
2555 ELSE /** X_count = 0, this should be an error we should have atleast one
2556 distribution for our rcv. txn.**/
2557
2558 IF (g_asn_debug = 'Y') THEN
2559 asn_debug.put_line('->Error: No distr available.');
2560 END IF;
2561 po_interface_errors_sv1.handle_interface_errors(
2562 X_transaction_type,
2563 'FATAL',
2564 X_batch_id,
2565 X_unique_id, -- header_id
2566 NULL, -- line_id
2567 'PO_INV_CR_NO_DISTR',
2568 'PO_DISTRIBUTIONS',
2569 NULL,
2570 NULL,
2571 NULL,
2572 NULL,
2573 NULL,
2574 NULL,
2575 NULL,
2576 NULL,
2577 NULL,
2578 NULL,
2579 NULL,
2580 NULL,
2581 NULL,
2582 X_curr_inv_process_flag);
2583 END IF;
2584
2585 EXCEPTION
2586 WHEN others THEN
2587 po_message_s.sql_error('create_invoice_distributions', X_progress,
2588 sqlcode);
2589 RAISE;
2590 END create_invoice_distributions;
2591
2592
2593 /* <PAY ON USE FPI START> */
2594
2595 /*******************************************************
2596 * PROCEDURE create_use_invoices
2597 *******************************************************/
2598 PROCEDURE create_use_invoices(
2599 p_api_version IN NUMBER,
2600 x_return_status OUT NOCOPY VARCHAR2,
2601 p_commit_interval IN NUMBER,
2602 p_aging_period IN NUMBER)
2603 IS
2604 l_api_version CONSTANT NUMBER := 1.0;
2605 l_api_name CONSTANT VARCHAR2(50) := 'create_use_invoices';
2606
2607 l_consumption PO_INVOICES_SV2.consump_rec_type;
2608 l_ap_inv_header PO_INVOICES_SV2.invoice_header_rec_type;
2609 l_curr PO_INVOICES_SV2.curr_condition_rec_type;
2610
2611 l_commit_lower NUMBER;
2612 l_commit_upper NUMBER;
2613 l_header_idx NUMBER := 0;
2614 l_invoice_count NUMBER := 0;
2615 l_distr_count NUMBER := 0;
2616 l_first_flag VARCHAR2(1) := FND_API.G_TRUE;
2617
2618 l_aging_period NUMBER;
2619 l_cutoff_date DATE;
2620 l_def_base_currency_code ap_system_parameters.base_currency_code%TYPE;
2621 l_org_id po_headers.org_id%TYPE;
2622 l_invoice_desc ap_invoices_interface.description%TYPE;
2623
2624 l_group_id ap_invoices_interface.group_id%TYPE;
2625 l_user_id NUMBER;
2626 l_login_id NUMBER;
2627 l_tmp_batch_id NUMBER;
2628 l_batch_name ap_batches.batch_name%TYPE;
2629 l_request_id ap_invoices_interface.request_id%TYPE;
2630
2631 l_error_msg VARCHAR2(2000);
2632 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2633 l_progress VARCHAR2(3);
2634 BEGIN
2635 l_progress := '000';
2636
2637 IF (g_asn_debug = 'Y') THEN
2638 ASN_DEBUG.put_line('Enter create use invoices');
2639 END IF;
2640
2641 x_return_status := FND_API.G_RET_STS_SUCCESS;
2642
2643 IF NOT FND_API.Compatible_API_Call (
2644 l_api_version,
2645 p_api_version,
2646 l_api_name,
2647 g_pkg_name)
2648 THEN
2649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2650 END IF; -- check api version compatibility
2651
2652 IF (g_asn_debug = 'Y') THEN
2653 ASN_DEBUG.put_line('API Version Check is passed');
2654 END IF;
2655
2656 l_progress := '010';
2657
2658 IF (g_asn_debug = 'Y') THEN
2659 ASN_DEBUG.put_line('Aging period passing in = ' || p_aging_period);
2660 END IF;
2661
2662 IF (p_aging_period IS NULL) THEN
2663
2664 IF (g_asn_debug = 'Y') THEN
2665 ASN_DEBUG.put_line('Getting aging period from profile');
2666 END IF;
2667
2668 l_aging_period :=
2669 NVL(FLOOR(TO_NUMBER(FND_PROFILE.VALUE('AGING_PERIOD'))), 0);
2670
2671 IF (g_asn_debug = 'Y') THEN
2672 ASN_DEBUG.put_line('After getting aging period from profile,
2673 aging period = ' || l_aging_period);
2674 END IF;
2675
2676 IF (l_aging_period < 0) THEN
2677 l_aging_period := 0;
2678 END IF;
2679 ELSE
2680 l_aging_period := p_aging_period;
2681 END IF; -- p_aging_period IS NULL
2682
2683 l_cutoff_date := TRUNC(SYSDATE) + 1 - l_aging_period;
2684
2685 IF (g_asn_debug = 'Y') THEN
2686 ASN_DEBUG.put_line('Aging Period = ' || l_aging_period ||
2687 ' Cutoff Date = ' || l_cutoff_date);
2688 END IF;
2689
2690 l_progress := '020';
2691
2692 -- get base currency
2693 SELECT base_currency_code
2694 INTO l_def_base_currency_code
2695 FROM ap_system_parameters;
2696
2697 IF (g_asn_debug = 'Y') THEN
2698 ASN_DEBUG.put_line('Base Currency Code = ' || l_def_base_currency_code);
2699 END IF;
2700
2701 OPEN PO_INVOICES_SV2.c_consumption(l_cutoff_date);
2702
2703 IF (g_asn_debug = 'Y') THEN
2704 ASN_DEBUG.put_line('Using Bulk Collect. Limit = ' || g_fetch_size);
2705 END IF;
2706
2707 LOOP
2708 l_progress := '030';
2709
2710 IF (g_asn_debug = 'Y') THEN
2711 ASN_DEBUG.put_line('In Outer Loop');
2712 END IF;
2713
2714 l_commit_lower := 1;
2715 l_commit_upper := 0;
2716
2717 FETCH c_consumption
2718 BULK COLLECT INTO l_consumption.po_header_id,
2719 l_consumption.po_release_id,
2720 l_consumption.po_line_id,
2721 l_consumption.line_location_id,
2722 l_consumption.po_distribution_id,
2723 l_consumption.vendor_id,
2724 l_consumption.pay_on_receipt_summary_code,
2725 l_consumption.vendor_site_id,
2726 l_consumption.default_pay_site_id,
2727 l_consumption.item_description,
2728 l_consumption.unit_price,
2729 l_consumption.quantity_ordered,
2730 l_consumption.quantity_billed,
2731 l_consumption.currency_code,
2732 l_consumption.currency_conversion_type,
2733 l_consumption.currency_conversion_rate,
2734 l_consumption.currency_conversion_date,
2738 l_consumption.tax_code_id,
2735 l_consumption.payment_currency_code,
2736 l_consumption.creation_date,
2737 l_consumption.payment_terms_id,
2739 l_consumption.org_id,
2740 l_consumption.unit_meas_lookup_code
2741 LIMIT g_fetch_size;
2742
2743 l_progress := '040';
2744 IF (g_asn_debug = 'Y') THEN
2745 ASN_DEBUG.put_line('After Bulk Collect. Fetched ' ||
2746 l_consumption.po_header_id.COUNT || ' records');
2747 END IF;
2748
2749 FOR i IN 1..l_consumption.po_header_id.COUNT LOOP
2750
2751 IF (g_asn_debug = 'Y') THEN
2752 ASN_DEBUG.put_line('In Inner Loop. i = ' || i);
2753 END IF;
2754
2755 IF (l_first_flag = FND_API.G_TRUE) THEN
2756 l_progress := '050';
2757
2758 l_first_flag := FND_API.G_FALSE;
2759
2760 IF (g_asn_debug = 'Y') THEN
2761 ASN_DEBUG.put_line('First Record.');
2762 END IF;
2763
2764 l_org_id := l_consumption.org_id(i);
2765
2766 -- get group id
2767 SELECT 'USE-' || ap_interface_groups_s.nextval
2768 INTO l_group_id
2769 FROM sys.dual;
2770
2771 IF (g_asn_debug = 'Y') THEN
2772 ASN_DEBUG.put_line('group_id = ' || l_group_id);
2773 END IF;
2774
2775 -- get invoice description
2776 FND_MESSAGE.set_name('PO', 'PO_INV_CR_USE_INVOICE_DESC');
2777 FND_MESSAGE.set_token('RUN_DATE', sysdate);
2778 l_invoice_desc := FND_MESSAGE.get;
2779
2780 IF (g_asn_debug = 'Y') THEN
2781 ASN_DEBUG.put_line('invoice_desc = ' || l_invoice_desc);
2782 END IF;
2783 PO_INVOICES_SV2.reset_header_values(
2784 l_return_status,
2785 l_consumption,
2786 i,
2787 l_curr);
2788
2789 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2791 END IF;
2792
2793 IF (g_asn_debug = 'Y') THEN
2794 ASN_DEBUG.put_line('Done Initializing Header variables.');
2795 END IF;
2796
2797 END IF; -- l_first_flag = FND_API.G_TRUE
2798
2799 IF (PO_INVOICES_SV2.need_new_invoice(
2800 l_return_status,
2801 l_consumption,
2802 i,
2803 l_curr,
2804 l_def_base_currency_code) = FND_API.G_TRUE)
2805 THEN
2806 l_progress := '060';
2807
2808 l_header_idx := l_header_idx + 1;
2809
2810 IF (g_asn_debug = 'Y') THEN
2811 ASN_DEBUG.put_line('Invoice header needs to be created for ' ||
2812 'previous records.');
2813 END IF;
2814
2815 IF (g_asn_debug = 'Y') THEN
2816 ASN_DEBUG.put_line('# of lines for this invoice = ' ||
2817 l_distr_count);
2818 END IF;
2819
2820 l_progress := '065';
2821
2822 PO_INVOICES_SV2.store_header_info(
2823 l_return_status,
2824 l_curr,
2825 l_invoice_desc,
2826 l_group_id,
2827 l_org_id,
2828 l_ap_inv_header,
2829 l_header_idx);
2830
2831 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2833 END IF;
2834
2835 IF (g_asn_debug = 'Y') THEN
2836 ASN_DEBUG.put_line('Stored Header Information into table');
2837 END IF;
2838
2839 l_distr_count := 0;
2840 l_invoice_count := l_invoice_count + 1;
2841
2842 PO_INVOICES_SV2.reset_header_values(
2843 l_return_status,
2844 l_consumption,
2845 i,
2846 l_curr);
2847
2848 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2850 END IF;
2851
2852 IF (g_asn_debug = 'Y') THEN
2853 ASN_DEBUG.put_line('Done Resetting Header Variables');
2854 ASN_DEBUG.put_line('# of headers created after last commit= ' ||
2855 l_header_idx || ' Commit interval= ' ||
2856 p_commit_interval);
2857 END IF;
2858
2859 IF (l_header_idx = p_commit_interval) THEN
2860 -- As we have reached the commit interval, all records
2861 -- created so far needs to be inserted and committed.
2862
2863 l_progress := '070';
2864
2865 IF (g_asn_debug = 'Y') THEN
2866 ASN_DEBUG.put_line('Bulk Insert into header interface');
2867 END IF;
2868
2869 PO_INVOICES_SV2.create_invoice_hdr(
2870 l_return_status,
2871 l_ap_inv_header,
2872 1,
2873 l_header_idx);
2874
2875 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2877 END IF;
2878
2879 l_progress := '073';
2880
2881 IF (g_asn_debug = 'Y') THEN
2882 ASN_DEBUG.put_line('Bulk Insert into line interface');
2883 END IF;
2884
2885 PO_INVOICES_SV2.create_invoice_distr(
2886 l_return_status,
2887 l_consumption,
2888 l_commit_lower,
2889 l_commit_upper);
2890
2891 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2893 END IF;
2894
2895 l_progress := '076';
2896
2897 l_commit_lower := l_commit_upper + 1;
2898 l_header_idx := 0;
2899 COMMIT;
2900
2901 l_progress := '080';
2902 IF (g_asn_debug = 'Y') THEN
2903 ASN_DEBUG.put_line('After commit');
2904 END IF;
2905
2906 END IF; -- l_header_idx = p_commit_interval
2907
2908 END IF; -- PO_INVOICES_SV2.need_new_invoice
2909
2910 -- This if is to check the return value of need_new_invoice
2911 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2913 END IF;
2914
2915 l_progress := '090';
2916 IF (g_asn_debug = 'Y') THEN
2917 ASN_DEBUG.put_line('Deriving more line information');
2918 END IF;
2919
2920 l_distr_count := l_distr_count + 1;
2921
2922 l_consumption.invoice_line_number(i) := l_distr_count;
2923 l_consumption.invoice_id(i) := l_curr.invoice_id;
2924
2925 l_consumption.quantity_invoiced(i) :=
2926 l_consumption.quantity_ordered(i) -
2927 l_consumption.quantity_billed(i);
2928
2929 IF (g_asn_debug = 'Y') THEN
2930 ASN_DEBUG.put_line('po_distribution_id = ' ||
2931 l_consumption.po_distribution_id(i) ||
2932 'Quantity to invoice = ' ||
2933 l_consumption.quantity_invoiced(i));
2934 END IF;
2935
2936 PO_INVOICES_SV2.calc_consumption_cost(
2937 l_return_status,
2938 l_consumption.quantity_invoiced(i),
2939 l_consumption.unit_price(i),
2940 l_consumption.tax_code_id(i),
2941 l_consumption.currency_code(i),
2942 l_consumption.invoice_line_amount(i),
2943 l_curr.invoice_amount);
2944
2945 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2947 END IF;
2948
2949 IF (g_asn_debug = 'Y') THEN
2950 ASN_DEBUG.put_line('line_amount = ' ||
2951 l_consumption.invoice_line_amount(i));
2952 ASN_DEBUG.put_line('Cumu. Invoive amt = '||l_curr.invoice_amount);
2953 END IF;
2954
2955 l_commit_upper := l_commit_upper + 1;
2956
2957 -- bug2786193
2958 -- We now use sysdate as the invoice date so no need to update
2959 -- it everytime
2960
2961 -- l_curr.invoice_date := l_consumption.creation_date(i);
2962
2963
2964 IF (g_asn_debug = 'Y') THEN
2965 ASN_DEBUG.put_line('-*-*-*-*-*- Done with one line -*-*-*-*-*-');
2966 END IF;
2967 END LOOP; -- for i in 1.. po_header_id.count
2968
2969 l_progress := '100';
2970
2971 IF (g_asn_debug = 'Y') THEN
2972 ASN_DEBUG.put_line('Exit inner loop');
2973 END IF;
2974
2975 IF (g_asn_debug = 'Y') THEN
2976 ASN_DEBUG.put_line('Insert remaining distributions from pl/sql table'
2977 || ' to lines interface table');
2978 END IF;
2979
2980 PO_INVOICES_SV2.create_invoice_distr(
2981 l_return_status,
2982 l_consumption,
2983 l_commit_lower,
2984 l_commit_upper);
2985
2986 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2988 END IF;
2989
2990 EXIT WHEN c_consumption%NOTFOUND;
2991
2992 END LOOP; -- loop for bulk fetching consumption advice
2993
2994 l_progress := '110';
2995 IF (g_asn_debug = 'Y') THEN
2996 ASN_DEBUG.put_line('Exit outer loop');
2997 END IF;
2998
2999 IF c_consumption%ISOPEN THEN
3003 IF (l_distr_count > 0) THEN
3000 CLOSE c_consumption;
3001 END IF;
3002
3004 IF (g_asn_debug = 'Y') THEN
3005 ASN_DEBUG.put_line('l_distr_count = ' || l_distr_count || '. Need to' ||
3006 ' perform some clean up work.');
3007 END IF;
3008
3009 l_header_idx := l_header_idx + 1;
3010
3011 IF (g_asn_debug = 'Y') THEN
3012 ASN_DEBUG.put_line('# of headers created after last commit= ' ||
3013 l_header_idx);
3014 END IF;
3015
3016 PO_INVOICES_SV2.store_header_info(
3017 l_return_status,
3018 l_curr,
3019 l_invoice_desc,
3020 l_group_id,
3021 l_org_id,
3022 l_ap_inv_header,
3023 l_header_idx);
3024
3025 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3027 END IF;
3028
3029 l_progress := '120';
3030 IF (g_asn_debug = 'Y') THEN
3031 ASN_DEBUG.put_line('Insert remaining invoice headers');
3032 END IF;
3033
3034 PO_INVOICES_SV2.create_invoice_hdr(
3035 l_return_status,
3036 l_ap_inv_header,
3037 1,
3038 l_header_idx);
3039
3040 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3042 END IF;
3043
3044 COMMIT;
3045
3046 l_progress := '130';
3047 IF (g_asn_debug = 'Y') THEN
3048 ASN_DEBUG.put_line('Call invoice import program');
3049 END IF;
3050
3051 SELECT ap_batches_s.nextval
3052 INTO l_tmp_batch_id
3053 FROM sys.dual;
3054
3055 FND_MESSAGE.set_name('PO', 'PO_INV_CR_USE_BATCH_DESC');
3056 l_batch_name := FND_MESSAGE.get || '/' || TO_CHAR(sysdate) ||
3057 '/' || TO_CHAR(l_tmp_batch_id);
3058
3059 IF (g_asn_debug = 'Y') THEN
3060 ASN_DEBUG.put_line('Batch name = ' || l_batch_name);
3061 END IF;
3062
3063 l_user_id := NULL;
3064 l_login_id := NULL;
3065
3066 PO_INVOICES_SV1.submit_invoice_import(
3067 l_return_status,
3068 'USE',
3069 l_group_id,
3070 l_batch_name,
3071 l_user_id,
3072 l_login_id,
3073 l_request_id);
3074
3075 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3077 END IF;
3078
3079 l_progress := '140';
3080
3081 FND_MESSAGE.set_name('PO', 'PO_ERS_CONC_REQUEST_CHECK');
3082 FND_MESSAGE.set_token('REQUEST', TO_CHAR(l_request_id));
3083 FND_MESSAGE.set_token('BATCH', l_batch_name);
3084 IF (g_asn_debug = 'Y') THEN
3085 ASN_DEBUG.put_line(FND_MESSAGE.get);
3086 END IF;
3087
3088 END IF; -- l_distr_count > 0
3089
3090 IF (g_asn_debug = 'Y') THEN
3091 ASN_DEBUG.put_line('Exit create_use_invoices');
3092 END IF;
3093
3094 EXCEPTION
3095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3097 l_error_msg := FND_MSG_PUB.get(p_encoded => 'F');
3098 IF (g_asn_debug = 'Y') THEN
3099 ASN_DEBUG.put_line(l_api_name || '-' || l_progress);
3100 ASN_DEBUG.put_line(l_error_msg);
3101 END IF;
3102
3103 IF c_consumption%ISOPEN THEN
3104 CLOSE c_consumption;
3105 END IF;
3106
3107 ROLLBACK;
3108
3109 PO_INVOICES_SV1.delete_interface_records(
3110 l_return_status,
3111 l_group_id);
3112 COMMIT;
3113
3114 WHEN OTHERS THEN
3115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3116 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3117 l_error_msg := FND_MSG_PUB.get(p_encoded => 'F');
3118 IF (g_asn_debug = 'Y') THEN
3119 ASN_DEBUG.put_line(l_api_name || '-' || l_progress);
3120 ASN_DEBUG.put_line(l_error_msg);
3121 END IF;
3122
3123 IF c_consumption%ISOPEN THEN
3124 CLOSE c_consumption;
3125 END IF;
3126
3127 ROLLBACK;
3128
3129 PO_INVOICES_SV1.delete_interface_records(
3130 l_return_status,
3131 l_group_id);
3132 COMMIT;
3133 END create_use_invoices;
3134
3135 /*******************************************************
3136 * FUNCTION need_new_invoice
3137 *******************************************************/
3138 FUNCTION need_new_invoice (
3139 x_return_status OUT NOCOPY VARCHAR2,
3140 p_consumption IN PO_INVOICES_SV2.consump_rec_type,
3141 p_index IN NUMBER,
3142 p_curr IN PO_INVOICES_SV2.curr_condition_rec_type,
3143 p_base_currency_code IN VARCHAR2) RETURN VARCHAR2
3144 IS
3145 l_api_name VARCHAR2(50) := 'need_new_invoice';
3146 BEGIN
3147 x_return_status := FND_API.G_RET_STS_SUCCESS;
3148
3149 -- bug2786193
3150 -- Use p_curr structure to reduce number of parameters passed
3151
3152 IF (p_curr.vendor_id <> p_consumption.vendor_id(p_index)
3156 p_curr.inv_summary_code <>
3153 OR
3154 p_curr.pay_site_id <> p_consumption.default_pay_site_id(p_index)
3155 OR
3157 p_consumption.pay_on_receipt_summary_code(p_index)
3158 OR
3159 p_curr.currency_code <> p_consumption.currency_code(p_index)
3160 OR
3161 -- bug2786193
3162 -- to group two lines under same invoice header, rate date and rate type
3163 -- has to match if we are talking about foreign currencies
3164 (p_consumption.currency_code(p_index) <> p_base_currency_code AND
3165 (TRUNC(p_curr.conversion_date) <>
3166 TRUNC(p_consumption.currency_conversion_date(p_index)) OR
3167 p_curr.conversion_type <>
3168 p_consumption.currency_conversion_type(p_index)))
3169 OR
3170 -- bug2786193
3171 -- if currency type is user, make sure that we do not group invoice lines
3172 -- together if they are using different conversion rate
3173 (p_consumption.currency_conversion_type(p_index) = 'User' AND
3174 NVL(p_curr.conversion_rate, -1) <>
3175 p_consumption.currency_conversion_rate(p_index))
3176 OR
3177 p_curr.payment_terms_id <> p_consumption.payment_terms_id(p_index)
3178 OR
3179 ((p_curr.po_header_id <> p_consumption.po_header_id(p_index) OR
3180 NVL(p_curr.po_release_id, -1) <>
3181 NVL(p_consumption.po_release_id(p_index), -1)) AND
3182 p_consumption.pay_on_receipt_summary_code(p_index) =
3183 'CONSUMPTION_ADVICE')
3184 ) THEN
3185
3186 RETURN FND_API.G_TRUE;
3187 END IF;
3188
3189 RETURN FND_API.G_FALSE;
3190 EXCEPTION
3191 WHEN OTHERS THEN
3192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3193 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3194 RETURN FND_API.G_FALSE;
3195 END need_new_invoice;
3196
3197 /*******************************************************
3198 * PROCEDURE store_header_info
3199 *******************************************************/
3200 PROCEDURE store_header_info(
3201 x_return_status OUT NOCOPY VARCHAR2,
3202 p_curr IN PO_INVOICES_SV2.curr_condition_rec_type,
3203 p_invoice_desc IN VARCHAR2,
3204 p_group_id IN VARCHAR2,
3205 p_org_id IN VARCHAR2,
3206 x_ap_inv_header IN OUT NOCOPY PO_INVOICES_SV2.invoice_header_rec_type,
3207 p_index IN NUMBER)
3208 IS
3209 l_api_name VARCHAR2(50) := 'store_header_info';
3210 BEGIN
3211 x_return_status := FND_API.G_RET_STS_SUCCESS;
3212
3213 IF (g_asn_debug = 'Y') THEN
3214 ASN_DEBUG.put_line('Storing header data into PL/SQL tables');
3215 END IF;
3216
3217 x_ap_inv_header.invoice_num(p_index) :=
3218 PO_INVOICES_SV2.create_invoice_num(
3219 p_org_id, -- SBI ENH
3220 p_curr.pay_site_id, -- SBI ENH
3221 p_curr.inv_summary_code,
3222 p_curr.invoice_date,
3223 NULL,
3224 NULL,
3225 'USE');
3226
3227 x_ap_inv_header.invoice_id(p_index) := p_curr.invoice_id;
3228 x_ap_inv_header.vendor_id(p_index) := p_curr.vendor_id;
3229 x_ap_inv_header.vendor_site_id(p_index) := p_curr.pay_site_id;
3230 x_ap_inv_header.invoice_amount(p_index) := p_curr.invoice_amount;
3231 x_ap_inv_header.invoice_currency_code(p_index) := p_curr.currency_code;
3232 x_ap_inv_header.invoice_date(p_index) := p_curr.invoice_date;
3233 x_ap_inv_header.source(p_index) := 'USE';
3234 x_ap_inv_header.description(p_index) := p_invoice_desc;
3235 x_ap_inv_header.creation_date(p_index) := sysdate;
3236 x_ap_inv_header.exchange_rate(p_index) := p_curr.conversion_rate;
3237 x_ap_inv_header.exchange_rate_type(p_index) := p_curr.conversion_type;
3238 x_ap_inv_header.exchange_date(p_index) := p_curr.conversion_date;
3239 x_ap_inv_header.payment_currency_code(p_index) := p_curr.pay_curr_code;
3240 x_ap_inv_header.terms_id(p_index) := p_curr.payment_terms_id;
3241 x_ap_inv_header.group_id(p_index) := p_group_id;
3242 x_ap_inv_header.org_id(p_index) := p_org_id;
3243
3244 IF (g_asn_debug = 'Y') THEN
3245 ASN_DEBUG.put_line('Invoice id = ' || x_ap_inv_header.invoice_id(p_index));
3246 END IF;
3247 EXCEPTION
3248 WHEN OTHERS THEN
3249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3250 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3251 END store_header_info;
3252
3253 /*******************************************************
3254 * PROCEDURE reset_header_values
3255 *******************************************************/
3256 PROCEDURE reset_header_values (
3257 x_return_status OUT NOCOPY VARCHAR2,
3258 p_next_consump IN PO_INVOICES_SV2.consump_rec_type,
3259 p_index IN NUMBER,
3260 x_curr OUT NOCOPY PO_INVOICES_SV2.curr_condition_rec_type)
3261 IS
3262 l_api_name VARCHAR2(50) := 'reset_header_values';
3263 BEGIN
3264 x_return_status := FND_API.G_RET_STS_SUCCESS;
3265
3266 -- bug2786193
3267 -- pass in currency_conversion_date instead of creation_date
3268
3269 IF (GL_CURRENCY_API.is_fixed_rate(
3270 p_next_consump.payment_currency_code(p_index),
3271 p_next_consump.currency_code(p_index),
3272 p_next_consump.currency_conversion_date(p_index)) = 'Y') THEN
3273 x_curr.pay_curr_code := p_next_consump.payment_currency_code(p_index);
3274 ELSE
3275 x_curr.pay_curr_code := p_next_consump.currency_code(p_index);
3276 END IF; -- GL_CURRENCY_API.is_fixed_rate(...)
3277
3278 x_curr.invoice_amount := 0;
3279
3280 SELECT AP_INVOICES_INTERFACE_S.NEXTVAL
3281 INTO x_curr.invoice_id
3282 FROM SYS.DUAL;
3283
3284 x_curr.vendor_id := p_next_consump.vendor_id(p_index);
3285 x_curr.pay_site_id := p_next_consump.default_pay_site_id(p_index);
3286 x_curr.inv_summary_code :=
3287 p_next_consump.pay_on_receipt_summary_code(p_index);
3288 x_curr.po_header_id := p_next_consump.po_header_id(p_index);
3289 x_curr.po_release_id := p_next_consump.po_release_id(p_index);
3290 x_curr.currency_code := p_next_consump.currency_code(p_index);
3291 x_curr.conversion_type := p_next_consump.currency_conversion_type(p_index);
3292 x_curr.conversion_date := p_next_consump.currency_conversion_date(p_index);
3293 x_curr.payment_terms_id := p_next_consump.payment_terms_id(p_index);
3294 x_curr.creation_date := p_next_consump.creation_date(p_index);
3295
3296 -- bug2786193
3297 -- use sysdate as invoice_date
3298
3299 x_curr.invoice_date := sysdate;
3300
3301 IF (p_next_consump.currency_conversion_type(p_index) <> 'User') THEN
3302 x_curr.conversion_rate := NULL;
3303
3304 -- bug2786193
3305 -- x_curr.conversion_date := x_curr.creation_date;
3306 ELSE
3307 x_curr.conversion_rate :=
3308 p_next_consump.currency_conversion_rate(p_index);
3309
3310 -- bug2786193
3311 -- x_curr.conversion_date :=
3312 -- p_next_consump.currency_conversion_date(p_index);
3313
3314 END IF; -- p_next_consump.currency_conversion_type(p_index) <> 'User'
3315 EXCEPTION
3316 WHEN OTHERS THEN
3317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3318 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3319 END reset_header_values;
3320
3321 /*******************************************************
3322 * PROCEDURE calc_consumption_cost
3323 *******************************************************/
3324 PROCEDURE calc_consumption_cost (
3325 x_return_status OUT NOCOPY VARCHAR2,
3326 p_quantity IN NUMBER,
3327 p_unit_price IN NUMBER,
3328 p_tax_code_id IN NUMBER,
3329 p_invoice_currency_code IN VARCHAR2,
3330 x_invoice_line_amount OUT NOCOPY NUMBER,
3331 x_curr_invoice_amount IN OUT NOCOPY NUMBER)
3332 IS
3333 l_api_name VARCHAR2(50) := 'calc_consumption_cost';
3334 BEGIN
3335 x_return_status := FND_API.G_RET_STS_SUCCESS;
3336
3337 x_invoice_line_amount := AP_UTILITIES_PKG.ap_round_currency(
3338 p_quantity * p_unit_price,
3339 p_invoice_currency_code);
3340
3341 IF (g_asn_debug = 'Y') THEN
3342 ASN_DEBUG.put_line('line amount = ' || x_invoice_line_amount);
3343 END IF;
3344
3345 x_curr_invoice_amount := x_curr_invoice_amount + x_invoice_line_amount;
3346 EXCEPTION
3347 WHEN OTHERS THEN
3348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3349 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3350 END calc_consumption_cost;
3351
3352 /*******************************************************
3353 * PROCEDURE create_invoice_hdr
3354 *******************************************************/
3355 PROCEDURE create_invoice_hdr(
3356 x_return_status OUT NOCOPY VARCHAR2,
3357 p_ap_inv_header IN PO_INVOICES_SV2.invoice_header_rec_type,
3358 p_from IN NUMBER,
3359 p_to IN NUMBER)
3360 IS
3361 l_api_name VARCHAR2(50) := 'create_invoice_hdr';
3362 BEGIN
3363 SAVEPOINT create_invoice_hdr_sp;
3364
3365 x_return_status := FND_API.G_RET_STS_SUCCESS;
3366
3367 FORALL i IN p_from..p_to
3368 INSERT INTO ap_invoices_interface(
3369 invoice_id,
3370 invoice_num,
3371 vendor_id,
3372 vendor_site_id,
3373 invoice_amount,
3374 invoice_currency_code,
3375 invoice_date,
3376 source,
3377 description,
3378 creation_date,
3379 exchange_rate,
3380 exchange_rate_type,
3381 exchange_date,
3382 payment_currency_code,
3383 terms_id,
3384 group_id,
3385 org_id)
3386 SELECT
3387 p_ap_inv_header.invoice_id(i),
3388 p_ap_inv_header.invoice_num(i),
3389 p_ap_inv_header.vendor_id(i),
3390 p_ap_inv_header.vendor_site_id(i),
3391 p_ap_inv_header.invoice_amount(i),
3392 p_ap_inv_header.invoice_currency_code(i),
3393 p_ap_inv_header.invoice_date(i),
3394 p_ap_inv_header.source(i),
3395 p_ap_inv_header.description(i),
3396 p_ap_inv_header.creation_date(i),
3397 p_ap_inv_header.exchange_rate(i),
3398 p_ap_inv_header.exchange_rate_type(i),
3399 p_ap_inv_header.exchange_date(i),
3400 p_ap_inv_header.payment_currency_code(i),
3401 p_ap_inv_header.terms_id(i),
3402 p_ap_inv_header.group_id(i),
3403 p_ap_inv_header.org_id(i)
3404 FROM
3405 sys.dual;
3406
3407 EXCEPTION
3408 WHEN OTHERS THEN
3409 ROLLBACK TO create_invoice_hdr_sp;
3410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3411 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3412 END create_invoice_hdr;
3413
3414 /*******************************************************
3415 * PROCEDURE create_invoice_distr
3416 *******************************************************/
3417 PROCEDURE create_invoice_distr(
3418 x_return_status OUT NOCOPY VARCHAR2,
3419 p_consumption IN PO_INVOICES_SV2.consump_rec_type,
3420 p_from IN NUMBER,
3421 p_to IN NUMBER)
3422 IS
3423 l_api_name VARCHAR2(50) := 'create_invoice_distr';
3424 i NUMBER;
3425
3426 /*Bug: 5125624*/
3427 l_ship_to_location_id PO_LINE_LOCATIONS.SHIP_TO_LOCATION_ID%TYPE;
3428 l_tax_classification_code VARCHAR2(30);
3429
3430 BEGIN
3431 SAVEPOINT create_invoice_distr_sp;
3432
3433 x_return_status := FND_API.G_RET_STS_SUCCESS;
3434
3435 /* Bug 5100177.
3436 * Match option is hard coded to P. So populate
3437 * unit_of_meas_lookup_code to poll.unit_meas_lookup_code.
3438 */
3439 FOR i IN p_from..p_to LOOP
3440
3441 /*Bug: 5125624*/
3442 l_ship_to_location_id := get_ship_to_location_id(p_consumption.line_location_id(i));
3443 l_tax_classification_code := get_tax_classification_code(p_consumption.po_header_id(i),
3444 p_consumption.line_location_id(i),
3445 'PURCHASE_ORDER');
3446 INSERT INTO ap_invoice_lines_interface(
3447 invoice_id,
3448 invoice_line_id,
3449 line_number,
3450 line_type_lookup_code,
3451 amount,
3452 accounting_date,
3453 description,
3454 tax_code_Id,
3455 amount_includes_tax_flag,
3456 --dist_code_combination_id,
3457 po_header_id,
3458 po_line_id,
3459 po_line_location_id,
3460 po_distribution_id,
3461 po_release_id,
3462 quantity_invoiced,
3463 expenditure_item_date,
3464 expenditure_type,
3465 expenditure_organization_id,
3466 project_accounting_context,
3467 pa_quantity,
3468 pa_addition_flag,
3469 unit_price,
3470 assets_tracking_flag,
3471 attribute_category,
3472 attribute1,
3473 attribute2,
3474 attribute3,
3475 attribute4,
3476 attribute5,
3477 attribute6,
3478 attribute7,
3479 attribute8,
3480 attribute9,
3481 attribute10,
3482 attribute11,
3483 attribute12,
3484 attribute13,
3485 attribute14,
3486 attribute15,
3487 match_option,
3488 tax_code_override_flag,
3489 org_id,
3490 unit_of_meas_lookup_code,
3491 SHIP_TO_LOCATION_ID, --Bug: 5125624
3492 TAX_CLASSIFICATION_CODE --Bug: 5125624
3493 )
3494 SELECT
3495 p_consumption.invoice_id(i),
3496 ap_invoice_lines_interface_s.nextval,
3497 p_consumption.invoice_line_number(i),
3498 'ITEM',
3499 p_consumption.invoice_line_amount(i),
3500 -- p_consumption.creation_date(i), -- bug2786193: use sysdate
3501 sysdate,
3502 p_consumption.item_description(i),
3503 p_consumption.tax_code_id(i),
3504 NULL,
3505 --pod.code_combination_id,
3506 p_consumption.po_header_id(i),
3507 p_consumption.po_line_id(i),
3508 p_consumption.line_location_id(i),
3509 p_consumption.po_distribution_id(i),
3510 p_consumption.po_release_id(i),
3511 p_consumption.quantity_invoiced(i),
3512 pod.expenditure_item_date,
3513 pod.expenditure_type,
3514 pod.expenditure_organization_id,
3515 pod.project_accounting_context,
3516 p_consumption.quantity_invoiced(i),
3517 'N',
3518 p_consumption.unit_price(i),
3519 DECODE(gcc.account_type, 'A','Y','N'),
3520 pod.attribute_category,
3521 pod.attribute1,
3522 pod.attribute2,
3523 pod.attribute3,
3524 pod.attribute4,
3525 pod.attribute5,
3526 pod.attribute6,
3527 pod.attribute7,
3528 pod.attribute8,
3529 pod.attribute9,
3530 pod.attribute10,
3531 pod.attribute11,
3532 pod.attribute12,
3533 pod.attribute13,
3534 pod.attribute14,
3535 pod.attribute15,
3536 'P', -- match option
3537 'Y',
3538 p_consumption.org_id(i),
3539 p_consumption.unit_meas_lookup_code(i), --5100177
3540 l_ship_to_location_id,
3541 l_tax_classification_code
3542 FROM
3543 po_distributions pod,
3544 gl_code_combinations gcc
3545 WHERE
3546 pod.po_distribution_id = p_consumption.po_distribution_id(i)
3547 AND pod.code_combination_id = gcc.code_combination_id;
3548
3549 END LOOP;
3550 EXCEPTION
3551 WHEN OTHERS THEN
3552 ROLLBACK TO create_invoice_distr_sp;
3553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3554 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3555 END create_invoice_distr;
3556
3557 /* <PAY ON USE FPI END> */
3558
3559 END PO_INVOICES_SV2;