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