1 PACKAGE PO_INVOICES_SV2 AUTHID CURRENT_USER AS
2 /* $Header: POXIVRPS.pls 120.12.12020000.2 2013/02/28 10:34:17 gke ship $ */
3
4 /* <PAY ON USE FPI START> */
5
6 /* Define structure for bulk processing */
7
8 TYPE po_header_id_tbl_type IS TABLE OF
9 po_headers.po_header_id%TYPE INDEX BY BINARY_INTEGER;
10
11 TYPE po_release_id_tbl_type IS TABLE OF
12 po_releases.po_release_id%TYPE INDEX BY BINARY_INTEGER;
13
14 TYPE po_line_id_tbl_type IS TABLE OF
15 po_lines.po_line_id%TYPE INDEX BY BINARY_INTEGER;
16
17 TYPE line_location_id_tbl_type IS TABLE OF
18 po_line_locations.line_location_id%TYPE INDEX BY BINARY_INTEGER;
19
20 TYPE po_distribution_id_tbl_type IS TABLE OF
21 po_distributions.po_distribution_id%TYPE INDEX BY BINARY_INTEGER;
22
23 TYPE vendor_id_tbl_type IS TABLE OF
24 po_vendors.vendor_id%TYPE INDEX BY BINARY_INTEGER;
25
26 TYPE pay_on_rec_sum_code_tbl_type IS TABLE OF
27 po_vendor_sites.pay_on_receipt_summary_code%TYPE INDEX BY BINARY_INTEGER;
28
29 TYPE vendor_site_id_tbl_type IS TABLE OF
30 po_vendor_sites.vendor_site_id%TYPE INDEX BY BINARY_INTEGER;
31
32 TYPE item_id_tbl_type IS TABLE OF
33 PO_LINES.item_id%TYPE INDEX BY BINARY_INTEGER;
34
35 TYPE item_description_tbl_type IS TABLE OF
36 PO_LINES.item_description%TYPE INDEX BY BINARY_INTEGER;
37
38 TYPE price_override_tbl_type IS TABLE OF
39 po_line_locations.price_override%TYPE INDEX BY BINARY_INTEGER;
40
41 TYPE quantity_tbl_type IS TABLE OF
42 po_distributions.quantity_ordered%TYPE INDEX BY BINARY_INTEGER;
43
44 TYPE currency_code_tbl_type IS TABLE OF
45 po_headers.currency_code%TYPE INDEX BY BINARY_INTEGER;
46
47 TYPE currency_conv_type_tbl_type IS TABLE OF
48 po_headers.rate_type%TYPE INDEX BY BINARY_INTEGER;
49
50 TYPE currency_conv_rate_tbl_type IS TABLE OF
51 po_headers.rate%TYPE INDEX BY BINARY_INTEGER;
52
53 TYPE date_tbl_type IS TABLE OF
54 DATE INDEX BY BINARY_INTEGER;
55
56 TYPE payment_terms_id_tbl_type IS TABLE OF
57 po_headers.terms_id%TYPE INDEX BY BINARY_INTEGER;
58
59 TYPE tax_code_id_tbl_type IS TABLE OF
60 po_line_locations.tax_code_id%TYPE INDEX BY BINARY_INTEGER;
61
62 TYPE invoice_amount_tbl_type IS TABLE OF
63 ap_invoices_interface.invoice_amount%TYPE INDEX BY BINARY_INTEGER;
64
65 TYPE org_id_tbl_type IS TABLE OF
66 po_headers.org_id%TYPE INDEX BY BINARY_INTEGER;
67
68 TYPE invoice_line_num_tbl_type IS TABLE OF
69 ap_invoice_lines_interface.line_number%TYPE INDEX BY BINARY_INTEGER;
70
71 TYPE invoice_id_tbl_type IS TABLE OF
75 ap_invoices_interface.invoice_num%TYPE INDEX BY BINARY_INTEGER;
72 ap_invoices_interface.invoice_id%TYPE INDEX BY BINARY_INTEGER;
73
74 TYPE invoice_num_tbl_type IS TABLE OF
76
77 TYPE source_tbl_type IS TABLE OF
78 ap_invoices_interface.source%TYPE INDEX BY BINARY_INTEGER;
79
80 TYPE description_tbl_type IS TABLE OF
81 ap_invoices_interface.description%TYPE INDEX BY BINARY_INTEGER;
82
83 TYPE pay_curr_code_tbl_type IS TABLE OF
84 po_vendor_sites.payment_currency_code%TYPE INDEX BY BINARY_INTEGER;
85
86 TYPE terms_id_tbl_type IS TABLE OF
87 ap_invoices_interface.terms_id%TYPE INDEX BY BINARY_INTEGER;
88
89 TYPE group_id_tbl_type IS TABLE OF
90 ap_invoices_interface.group_id%TYPE INDEX BY BINARY_INTEGER;
91
92 /* Bug 5100177. */
93 TYPE unit_of_meas_tbl_type IS TABLE OF
94 PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE INDEX BY BINARY_INTEGER;
95 TYPE consump_rec_type IS RECORD (
96 po_header_id po_header_id_tbl_type,
97 po_release_id po_release_id_tbl_type,
98 po_Line_id po_line_id_tbl_type,
99 line_location_id line_location_id_tbl_type,
100 po_distribution_id po_distribution_id_tbl_type,
101 vendor_id vendor_id_tbl_type,
102 pay_on_receipt_summary_code pay_on_rec_sum_code_tbl_type,
103 vendor_site_id vendor_site_id_tbl_type,
104 default_pay_site_id vendor_site_id_tbl_type,
105 item_id item_id_tbl_type,--bug 7614092
106 item_description item_description_tbl_type,
107 unit_price price_override_tbl_type,
108 quantity_ordered quantity_tbl_type,
109 quantity_billed quantity_tbl_type,
110 currency_code currency_code_tbl_type,
111 currency_conversion_type currency_conv_type_tbl_type,
112 currency_conversion_rate currency_conv_rate_tbl_type,
113 currency_conversion_date date_tbl_type,
114 payment_currency_code pay_curr_code_tbl_type,
115 payment_terms_id payment_terms_id_tbl_type,
116 tax_code_id tax_code_id_tbl_type,
117 invoice_line_amount invoice_amount_tbl_type,
118 creation_date date_tbl_type,
119 org_id org_id_tbl_type,
120 invoice_id invoice_id_tbl_type,
121 invoice_line_number invoice_line_num_tbl_type,
122 quantity_invoiced quantity_tbl_type,
123 unit_meas_lookup_code unit_of_meas_tbl_type);--5100177
124
125 TYPE invoice_header_rec_type IS RECORD (
126 invoice_id invoice_id_tbl_type,
127 invoice_num invoice_num_tbl_type,
128 vendor_id vendor_id_tbl_type,
129 vendor_site_id vendor_site_id_tbl_type,
130 invoice_amount invoice_amount_tbl_type,
131 invoice_currency_code currency_code_tbl_type,
132 invoice_date date_tbl_type,
133 source source_tbl_type,
134 description description_tbl_type,
135 creation_date date_tbl_type,
136 exchange_rate currency_conv_rate_tbl_type,
137 exchange_rate_type currency_conv_type_tbl_type,
138 exchange_date date_tbl_type,
139 payment_currency_code pay_curr_code_tbl_type,
140 terms_id terms_id_tbl_type,
141 group_id group_id_tbl_type,
142 org_id org_id_tbl_type);
143
144 TYPE curr_condition_rec_type IS RECORD (
145 pay_curr_code po_vendor_sites.payment_currency_code%TYPE,
146 invoice_amount ap_invoices_interface.invoice_amount%TYPE,
147 invoice_id ap_invoices_interface.invoice_id%TYPE,
148 invoice_num ap_invoices_interface.invoice_num%TYPE,
149 vendor_id po_vendors.vendor_id%TYPE,
150 pay_site_id po_vendor_sites.vendor_site_id%TYPE,
151 inv_summary_code po_vendor_sites.pay_on_receipt_summary_code%TYPE,
152 po_header_id po_headers.po_header_id%TYPE,
153 po_release_id po_releases.po_release_id%TYPE,
154 currency_code po_headers.currency_code%TYPE,
155 conversion_rate po_headers.rate%TYPE,
156 conversion_type po_headers.rate_type%TYPE,
157 conversion_date po_headers.rate_date%TYPE,
158 payment_terms_id po_headers.terms_id%TYPE,
159 creation_date po_headers.creation_date%TYPE,
160 invoice_date ap_invoices_interface.invoice_date%TYPE
161 );
162
163 /* Cursor for fetching consumption advice */
164
165 /* Bug 5138133 : Pay on receipt program was interfacing USE invoices multiple
166 ** times. Modified the cursor below to exclude Consumption Advice
167 ** lines that are already interfaced to AP.
168 */
169
170 CURSOR c_consumption (p_cutoff_date DATE) IS
171 -- std PO referencing Global Agreement
172 SELECT
173 poh.po_header_id PO_HEADER_ID,
174 TO_NUMBER(NULL) PO_RELEASE_ID, -- bug2840859
175 pol.po_line_id PO_LINE_ID,
176 poll.line_location_id LINE_LOCATION_ID,
177 pod.po_distribution_id PO_DISTRIBUTION_ID,
178 pv.vendor_id VENDOR_ID,
179 pvs.pay_on_receipt_summary_code PAY_ON_RECEIPT_SUMMARY_CODE,
180 poh.vendor_site_id VENDOR_SITE_ID,
181 NVL(pvs.default_pay_site_id, pvs.vendor_site_id) DEFAULT_PAY_SITE_ID,
182 pol.item_id ITEM_ID,--bug 7614092
186 NVL(pod.quantity_billed, 0) QUANTITY_BILLED,
183 nvl(poll.description, pol.item_description) ITEM_DESCRIPTION,--bug 7614092
184 poll.price_override UNIT_PRICE,
185 pod.quantity_ordered QUANTITY,
187 poh.currency_code CURRENCY_CODE,
188 poh.rate_type CURRENCY_CONVERSION_TYPE,
189 poh.rate CURRENCY_CONVERSION_RATE,
190 poh.rate_date CURRENCY_CONVERSION_DATE,
191 NVL(pvs.payment_currency_code,
192 NVL(pvs.invoice_currency_code,
193 poh.currency_code)) PAYMENT_CURRENCY_CODE,
194 poh.creation_date CREATION_DATE,
195 NVL(NVL(poll.terms_id, poh.terms_id), pvs2.terms_id) PAYMENT_TERMS_ID,
196 DECODE(poll.taxable_flag, 'Y', poll.tax_code_id, NULL) TAX_CODE_ID,
197 poh.org_id ORG_ID,
198 poll.unit_meas_lookup_code UNIT_MEAS_LOOKUP_CODE --5100177
199 FROM
200 PO_VENDORS pv,
201 PO_VENDOR_SITES pvs,
202 PO_VENDOR_SITES pvs2,
203 PO_HEADERS poh,
204 PO_LINES pol,
205 PO_LINE_LOCATIONS poll,
206 PO_DISTRIBUTIONS pod
207 WHERE
208 pv.vendor_id = poh.vendor_id
209 AND poh.vendor_site_id = pvs.vendor_site_id
210 AND NVL(pvs.default_pay_site_id, pvs.vendor_site_id) =
211 pvs2.vendor_site_id
212 AND poh.po_header_id = pol.po_header_id
213 AND pol.po_line_id = poll.po_line_id
214 AND poll.line_location_id = pod.line_location_id
215 AND poh.pay_on_code IN ('RECEIPT_AND_USE', 'USE')
216 AND DECODE (poh.consigned_consumption_flag, -- utilize PO_HEADERS_F1 idx
217 'Y',
218 DECODE(poh.closed_code,
219 'FINALLY CLOSED',
220 NULL,
221 'Y'),
222 NULL) = 'Y'
223 AND poh.type_lookup_code = 'STANDARD'
224 AND poh.creation_date <= p_cutoff_date
225 AND pvs.pay_on_code IN ('RECEIPT_AND_USE', 'USE')
226 AND pod.quantity_ordered > NVL(pod.quantity_billed,0)
227 AND poll.closed_code <> 'FINALLY CLOSED'
228 AND NOT EXISTS ( SELECT 'use invoice is interfaced'
229 FROM ap_invoices_interface aii,
230 ap_invoice_lines_interface aili
231 WHERE aii.invoice_id = aili.invoice_id
232 AND nvl(aii.status,'PENDING') <> 'PROCESSED'
233 AND aili.po_distribution_id = pod.po_distribution_id )
234 UNION ALL
235 -- blanket release
236 SELECT
237 poh.po_header_id PO_HEADER_ID,
238 por.po_release_id PO_RELEASE_ID,
239 pol.po_line_id PO_LINE_ID,
240 poll.line_location_id LINE_LOCATION_ID,
241 pod.po_distribution_id PO_DISTRIBUTION_ID,
242 pv.vendor_id VENDOR_ID,
243 pvs.pay_on_receipt_summary_code PAY_ON_RECEIPT_SUMMARY_CODE,
244 poh.vendor_site_id VENDOR_SITE_ID,
245 NVL(pvs.default_pay_site_id, pvs.vendor_site_id) DEFAULT_PAY_SITE_ID,
246 pol.item_id ITEM_ID,--bug 7614092
247 nvl(poll.description, pol.item_description) ITEM_DESCRIPTION,--bug 7614092
248 poll.price_override UNIT_PRICE,
249 pod.quantity_ordered QUANTITY,
250 NVL(pod.quantity_billed, 0) QUANTITY_BILLED,
251 poh.currency_code CURRENCY_CODE,
252 poh.rate_type CURRENCY_CONVERSION_TYPE,
253 poh.rate CURRENCY_CONVERSION_RATE,
254 poh.rate_date CURRENCY_CONVERSION_DATE,
255 NVL(pvs.payment_currency_code,
256 NVL(pvs.invoice_currency_code,
257 poh.currency_code)) PAYMENT_CURRENCY_CODE,
258 por.creation_date CREATION_DATE,
259 NVL(NVL(poll.terms_id, poh.terms_id), pvs2.terms_id) PAYMENT_TERMS_ID,
260 DECODE(poll.taxable_flag, 'Y', poll.tax_code_id, NULL) TAX_CODE_ID,
261 por.org_id ORG_ID,
262 poll.unit_meas_lookup_code UNIT_MEAS_LOOKUP_CODE --5100177
263 FROM
264 PO_VENDORS pv,
265 PO_VENDOR_SITES pvs,
266 PO_VENDOR_SITES pvs2,
267 PO_HEADERS poh,
268 PO_RELEASES por,
269 PO_LINES pol,
270 PO_LINE_LOCATIONS poll,
271 PO_DISTRIBUTIONS pod
272 WHERE
273 pv.vendor_id = poh.vendor_id
274 AND poh.vendor_site_id = pvs.vendor_site_id
275 AND NVL(pvs.default_pay_site_id, pvs.vendor_site_id) =
276 pvs2.vendor_site_id
277 AND poh.po_header_id = por.po_header_id
278 AND poh.po_header_id = pol.po_header_id
279 AND pol.po_line_id = poll.po_line_id
280 AND por.po_release_id = poll.po_release_id
281 AND poll.line_location_id = pod.line_location_id
282 AND por.pay_on_code IN ('RECEIPT_AND_USE', 'USE')
283 AND DECODE (por.consigned_consumption_flag, -- utilize PO_RELEASES_F1 idx
284 'Y',
285 DECODE(por.closed_code,
286 'FINALLY CLOSED',
287 NULL,
288 'Y'),
289 NULL) = 'Y'
290 AND por.release_type = 'BLANKET'
291 AND por.creation_date <= p_cutoff_date
292 AND pvs.pay_on_code IN ('RECEIPT_AND_USE', 'USE')
293 AND pod.quantity_ordered > NVL(pod.quantity_billed,0)
294 AND poll.closed_code <> 'FINALLY CLOSED'
298 WHERE aii.invoice_id = aili.invoice_id
295 AND NOT EXISTS ( SELECT 'use invoice is interfaced'
296 FROM ap_invoices_interface aii,
297 ap_invoice_lines_interface aili
299 AND nvl(aii.status,'PENDING') <> 'PROCESSED'
300 AND aili.po_distribution_id = pod.po_distribution_id )
301 ORDER BY 6, -- VENDOR_ID
302 9, -- DEFAULT_PAY_SITE_ID
303 7, -- PAY_ON_RECEIPT_SUMMARY_CODE
304 15, -- CURRENCY_CODE
305 18, -- CURRENCY_CONVERSION_DATE -- bug2786193
306 16, -- CURRENCY_CONVERSION_TYPE -- bug2786193
307 17, -- CURRENCY_CONVERSION_RATE -- bug2786193
308 20, -- PAYMENT_TERMS_ID
309 -- 19, -- CREATION_DATE -- bug2786193
310 1, -- PO_HEADER_ID
311 2, -- PO_RELEASE_ID
312 3, -- PO_LINE_ID
313 4, -- LINE_LOCATION_ID
314 5; -- DISTRIBUTION_ID
315
316
317 /* <PAY ON USE FPI END> */
318
319
320 /*==================================================================
321 FUNCTION NAME: create_receipt_invoices
322
323 DESCRIPTION: This is a batch layer API which will create standard invoices
324 in Oracle Payables based on purchase orders and receipt
325 transactions. Record(s) will be created in the following
326 entities by calling various process APIs:
327
328 PARAMETERS: X_commit_interval IN NUMBER,
329 X_rcv_shipment_header_id IN NUMBER,
330 X_receipt_event IN VARCHAR2
331
332 DESIGN
333 REFERENCES: 857proc.doc
334
335 CHANGE Created 19-March-96 SODAYAR
336 HISTORY:
337
338 =======================================================================*/
339 FUNCTION create_receipt_invoices(X_commit_interval IN NUMBER,
340 X_rcv_shipment_header_id IN NUMBER,
341 X_receipt_event IN VARCHAR2,
342 X_aging_period IN NUMBER DEFAULT NULL)
343 RETURN BOOLEAN;
344
345 /* =================================================================
346 FUNCTION NAME: get_ship_to_location_id(p_trx_id,p_entity_code)
347 p_po_line_location_id = po line location id for which we require the ship to
348 location.
349 Bug: 5125624
350 ==================================================================*/
351
352 FUNCTION get_ship_to_location_id (p_po_line_location_id IN NUMBER)
353 RETURN PO_LINE_LOCATIONS.SHIP_TO_LOCATION_ID%TYPE;
354
355
356 /* =================================================================
357 FUNCTION NAME: get_tax_classification_code(p_trx_id,p_entity_code)
358 p_trx_id = Is the id that is present in the zx tables. In case of
359 PO it is the po_header_id
360 entity_code= zx tables stores the trx_id and the entity code to avoid
361 multiple records with same trx_id. In case we are passing
362 po_header_id then the entity_code would be 'PURCHASE ORDER'
363
364 Bug: 5125624
365 ==================================================================*/
366
367 FUNCTION get_tax_classification_code (p_trx_id IN NUMBER,
368 p_trx_line_id IN NUMBER,
369 p_entity_code IN VARCHAR)
370 RETURN VARCHAR2;
371
372 /*==================================================================
373 FUNCTION NAME: create_invoice_num
374
375
376 DESCRIPTION: This Api is used to create invoice number according to the
377 summary level(input parameter) for ERS.
378 For pay on use, the invoice num always has the structure
379 'USE-<INVOICE_DATE>-<UNIQUE NUM FROM SEQUENCE>'
380
381
382 PARAMETERS: X_pay_on_receipt_summary_code IN VARCHAR2,
383 X_invoice_date IN DATE,
384 X_packing_slip IN VARCHAR2,
385 X_receipt_num IN VARCHAR2,
386 p_source IN VARCHAR2 := NULL
387
388 PARAMETER DESCRIPTIONS:
389 x_org_id: org id
390 x_vendor_site_id: vendor pay site id
391 X_pay_on_receipt_summary_code: invoice summary level from vendor site
392 X_invoice_date: invoice date
393 X_packing_slip: packing slip information
394 X_receipt_num: receipt number
395 p_source: what invoice it is creating invoice number for
396
397 DESIGN
398 REFERENCES: 857proc.doc
399
400 CHANGE Created 19-March-96 SODAYAR
401 HISTORY:
402
403 <PAY ON USE FPI> 10-October-2002 BAO
404 =======================================================================*/
405
406 FUNCTION create_invoice_num( X_org_id IN NUMBER,
407 X_vendor_site_id IN NUMBER,
408 X_pay_on_receipt_summary_code IN VARCHAR2,
409 X_invoice_date IN DATE,
410 X_packing_slip IN VARCHAR2,
411 X_receipt_num IN VARCHAR2,
412 /* <PAY ON USE FPI START> */
413 p_source IN VARCHAR2 := NULL)
414 /* <PAY ON USE FPI END> */
415 RETURN VARCHAR2;
416
417
418
419
420 /*==================================================================
421 PROCEDURE NAME: wrap_up_current_invoice
422
423 DESCRIPTION: This API is called whenever a new invoice is about to be
424 created. It performs wrap-up operations:
425 1) update the current invoice with the correct amounts;
426 2) update the grand totals
430 the next invoice.
427 3) create payment schedule for the current invoice
428 4) update ap_batches if required
429 5) setup all the necessary "current" variables for
431
432 PARAMETERS: X_new_vendor_id IN NUMBER,
433 X_new_pay_site_id IN NUMBER,
434 X_new_currency_code IN VARCHAR2,
435 X_new_conversion_rate_type IN VARCHAR2,
436 X_new_conversion_date IN DATE,
437 X_new_conversion_rate IN NUMBER,
438 X_new_payment_terms_id IN NUMBER,
439 X_new_transaction_date IN DATE,
440 X_new_packing_slip IN VARCHAR2,
441 X_new_shipment_header_id IN NUMBER,
442 X_new_osa_flag IN VARCHAR2, --Shikyu project
443 X_def_disc_is_inv_less_tax_flag IN VARCHAR2,
444 X_terms_date IN DATE,
445 X_payment_priority IN VARCHAR2,
446 X_payment_method_lookup_code IN VARCHAR2,
447 X_batch_id IN OUT NUMBER,
448 X_def_batch_control_flag IN VARCHAR2,
449 X_def_base_currency_code IN VARCHAR2,
450 X_curr_invoice_amount IN OUT NUMBER,
451 X_curr_tax_amount IN OUT NUMBER,
452 X_curr_invoice_id IN OUT NUMBER,
453 X_curr_vendor_id IN OUT NUMBER,
454 X_curr_pay_site_id IN OUT NUMBER,
455 X_curr_currency_code IN OUT VARCHAR2,
456 X_curr_conversion_rate_type IN OUT VARCHAR2,
457 X_curr_conversion_date IN OUT DATE,
458 X_curr_conversion_rate IN OUT NUMBER,
459 X_curr_payment_terms_id IN OUT NUMBER,
460 X_curr_transaction_date IN OUT DATE,
461 X_curr_packing_slip IN OUT VARCHAR2,
462 X_curr_shipment_header_id IN OUT NUMBER,
463 X_curr_osa_flag IN OUT VARCHAR2, --Shikyu project
464 X_curr_inv_process_flag IN OUT VARCHAR2,
465 X_invoice_count IN OUT NUMBER,
466 X_invoice_running_total IN OUT NUMBER
467
468
469 DESIGN
470 REFERENCES: 857proc.doc
471
472
473 CHANGE Created 19-March-96 SODAYAR
474 HISTORY: Modified 14-May-96 KKCHAN
475 added X_def_base_currency_code as a param.
476 Modified 03-Dec-97 NWANG
477 added X_curr_payment_code as param
478
479 =======================================================================*/
480 /* Bug 586895 */
481
482 PROCEDURE WRAP_UP_CURRENT_INVOICE(X_new_vendor_id IN NUMBER,
483 X_new_pay_site_id IN NUMBER,
484 X_new_currency_code IN VARCHAR2,
485 X_new_conversion_rate_type IN VARCHAR2,
486 X_new_conversion_rate_date IN DATE,
487 X_new_conversion_rate IN NUMBER,
488 X_new_payment_terms_id IN NUMBER,
489 X_new_transaction_date IN DATE,
490 X_new_packing_slip IN VARCHAR2,
491 X_new_shipment_header_id IN NUMBER,
492 X_new_osa_flag IN VARCHAR2, --Shikyu project
493 X_terms_date IN DATE,
494 X_payment_priority IN VARCHAR2,
495 X_new_payment_code IN VARCHAR2,
496 X_curr_method_code IN OUT NOCOPY VARCHAR2,
497 /*Bug 612979*/ X_new_pay_curr_code IN VARCHAR2,
498 X_curr_pay_curr_code IN OUT NOCOPY VARCHAR2,
499 X_batch_id IN OUT NOCOPY NUMBER,
500 X_curr_invoice_amount IN OUT NOCOPY NUMBER,
501 X_curr_invoice_id IN OUT NOCOPY NUMBER,
502 X_curr_vendor_id IN OUT NOCOPY NUMBER,
503 X_curr_pay_site_id IN OUT NOCOPY NUMBER,
504 X_curr_currency_code IN OUT NOCOPY VARCHAR2,
505 X_curr_conversion_rate_type IN OUT NOCOPY VARCHAR2,
506 X_curr_conversion_rate_date IN OUT NOCOPY DATE,
507 X_curr_conversion_rate IN OUT NOCOPY NUMBER,
508 X_curr_payment_terms_id IN OUT NOCOPY NUMBER,
509 X_curr_transaction_date IN OUT NOCOPY DATE,
510 X_curr_packing_slip IN OUT NOCOPY VARCHAR2,
511 X_curr_shipment_header_id IN OUT NOCOPY NUMBER,
512 X_curr_osa_flag IN OUT NOCOPY VARCHAR2, --Shikyu project
513 X_curr_inv_process_flag IN OUT NOCOPY VARCHAR2,
514 X_invoice_count IN OUT NOCOPY NUMBER,
515 X_invoice_running_total IN OUT NOCOPY NUMBER,
516 /* R12 complex work .
517 * Added new columns to create separate invoices
518 * for prepayment shipment lines.
519 */
520 X_new_shipment_type IN VARCHAR2 ,
521 X_curr_shipment_type IN OUT NOCOPY VARCHAR2,
522 X_org_id IN NUMBER,--Bug 5531203
523 X_curr_le_transaction_date IN OUT NOCOPY DATE );
524
525
526 /*==================================================================
527 PROCEDURE NAME: get_received_quantity
528
529 DESCRIPTION: This API calculates the actual received quantity of a
530 shipment after adjustment
531
532 PARAMETERS: X_transaction_id IN NUMBER,
533 X_received_quantity IN OUT NUMBER
534
535 CHANGE Created 01-DEC-98 DKFCHAN
536 HISTORY:
537
538 =======================================================================*/
539
540 PROCEDURE get_received_quantity( X_transaction_id IN NUMBER,
541 X_shipment_line_id IN NUMBER,
542 X_received_quantity IN OUT NOCOPY NUMBER,
543 X_match_option IN VARCHAR2 DEFAULT NULL) ;--5100177;
544
545 PROCEDURE get_received_amount( X_transaction_id IN NUMBER,
546 X_shipment_line_id IN NUMBER,
550 X_invoice_currency_code IN VARCHAR2,
547 X_received_amount IN OUT NOCOPY NUMBER);
548
549 PROCEDURE create_invoice_distributions(X_invoice_id IN NUMBER,
551 X_base_currency_code IN VARCHAR2,
552 X_batch_id IN NUMBER,
553 X_pay_site_id IN NUMBER,
554 X_po_header_id IN NUMBER,
555 X_po_line_id IN NUMBER,
556 X_po_line_location_id IN NUMBER,
557 X_po_release_id IN NUMBER,
558 X_receipt_event IN VARCHAR2,
559 X_po_distribution_id IN NUMBER,
560 X_item_description IN VARCHAR2,
561 X_type_1099 IN VARCHAR2,
562 X_tax_code_id IN NUMBER,
563 X_quantity IN NUMBER,
564 X_unit_price IN NUMBER,
565 X_exchange_rate_type IN VARCHAR2,
566 X_exchange_date IN DATE,
567 X_exchange_rate IN NUMBER,
568 X_invoice_date IN DATE,
569 X_receipt_date IN DATE,
570 X_vendor_income_tax_region IN VARCHAR2,
571 X_reference_1 IN VARCHAR2,
572 X_reference_2 IN VARCHAR2,
573 X_awt_flag IN VARCHAR2,
574 X_awt_group_id IN NUMBER,
575 X_accounting_date IN DATE,
576 X_period_name IN VARCHAR2,
577 X_transaction_type IN VARCHAR2,
578 X_unique_id IN NUMBER,
579 X_curr_invoice_amount IN OUT NOCOPY NUMBER,
580 X_curr_inv_process_flag IN OUT NOCOPY VARCHAR2,
581 X_receipt_num IN VARCHAR2 DEFAULT NULL,
582 X_rcv_transaction_id IN NUMBER DEFAULT NULL,
583 X_match_option IN VARCHAR2 DEFAULT NULL,
584 X_amount IN NUMBER DEFAULT NULL,
585 X_matching_basis IN VARCHAR2 DEFAULT 'QUANTITY',
586 X_unit_meas_lookup_code IN VARCHAR2 DEFAULT NULL, --5100177
587 X_lcm_shipment_line_id IN NUMBER DEFAULT NULL ); -- PoR with LCM project
588
589 /* <PAY ON USE FPI START> */
590
591 /*==================================================================
592 PROCEDURE NAME: create_use_invoice
593
594 DESCRIPTION: API for creating invoices for consumption advice.
595
596 PARAMETERS:
597 p_api_version : API version of this procedure the caller assumes
598 x_return_status : Return status of the procedure
599 p_commit_interval : Number of Invoices evaluated before a commit is issued
600 p_aging_period : days for a consumption advice to age before
601 it can be invoiced
602
603
604 DESIGN
605 REFERENCES:
606
607
608 CHANGE Created 09-October-02 BAO
609 HISTORY:
610
611 =======================================================================*/
612 PROCEDURE create_use_invoices(
613 p_api_version IN NUMBER,
614 x_return_status OUT NOCOPY VARCHAR2,
615 p_commit_interval IN NUMBER,
616 p_aging_period IN NUMBER);
617
618
619 /*==================================================================
620 PROCEDURE NAME: need_new_invoice
621
622 DESCRIPTION: A function to compare between current grouping
623 variables and the record just fetched to determine
624 whether the record belongs to the same invoice or not.
625 .
626
627 PARAMETERS:
628 x_return_status : return status of the procedure
629 p_consumption : record of tables to store invoice line info
630 p_index : index to identify a record in p_consumption
631 p_curr : structure that stores current invoice hdr info
632 p_base_currency_code : base currency code
633
634 RETURN VALUE DATATYPE: VARCHAR2
635 Possible Values: FND_API.G_TRUE or FND_API.G_FALSE
636
637
638 DESIGN
639 REFERENCES:
640
641 CHANGE Created 09-October-02 BAO
642 HISTORY:
643
644 Bug2786193 05-Feb-02 BAO
645 changed param list to use p_curr rec structure
646
647 =======================================================================*/
648 FUNCTION need_new_invoice (
649 x_return_status OUT NOCOPY VARCHAR2,
650 p_consumption IN PO_INVOICES_SV2.consump_rec_type,
651 p_index IN NUMBER,
652 p_curr IN PO_INVOICES_SV2.curr_condition_rec_type,
653 p_base_currency_code IN VARCHAR2) RETURN VARCHAR2;
654
655 /*==================================================================
656 PROCEDURE NAME: store_header_info
657
658 DESCRIPTION: Temporarily stores all header related information into
659 PL/SQL table structure for bulk insert later
660
661 PARAMETERS:
662 x_return_status : return status of the procedure
663 p_curr : record structure to store current invoice header info
664 p_invoice_desc : invoice description
665 p_group_id : group id when doing import
666 p_org_id : org id
667 x_ap_inv_header : record of tables to store invoice header info
668 p_index : index to identify a record in x_ap_inv_header
669
670 DESIGN
671 REFERENCES:
672
673
674 CHANGE Created 09-October-02 BAO
675 HISTORY:
676
677 =======================================================================*/
678 PROCEDURE store_header_info(
679 x_return_status OUT NOCOPY VARCHAR2,
680 p_curr IN PO_INVOICES_SV2.curr_condition_rec_type,
681 p_invoice_desc IN VARCHAR2,
682 p_group_id IN VARCHAR2,
683 p_org_id IN VARCHAR2,
684 x_ap_inv_header IN OUT NOCOPY PO_INVOICES_SV2.invoice_header_rec_type,
685 p_index IN NUMBER);
686
687
688 /*==================================================================
689 PROCEDURE NAME: reset_header_values
690
691 DESCRIPTION: reset all header grouping variables (curr_ variables) and
692 other variables for invoice headers
693
694
695
696 PARAMETERS:
697 x_return_status : return status of the procedure
698 p_next_consump : record of tables to store invoice line info
699 p_index : index to identify a recordd in p_next_consump
700 x_curr : record structure to store current invoice header info
701
702 DESIGN
703 REFERENCES:
704
705
706 CHANGE Created 09-October-02 BAO
707 HISTORY:
708
709 =======================================================================*/
710 PROCEDURE reset_header_values (
711 x_return_status OUT NOCOPY VARCHAR2,
712 p_next_consump IN PO_INVOICES_SV2.consump_rec_type,
713 p_index IN NUMBER,
714 x_curr OUT NOCOPY PO_INVOICES_SV2.curr_condition_rec_type);
715
716 /*==================================================================
717 PROCEDURE NAME: calc_consumption_cost
718
719 DESCRIPTION: calculate the invoice amount of the distribution and
720 the tax.
721
722
723 PARAMETERS:
724 x_return_status : return status of the procedure
725 p_quantity : quantity to be invoiced
726 p_unit_price : price
727 p_tax_code_id : tax code id
728 p_invoice_currency_code : invoice currency
729 x_invoice_line_amount : return amt invoiced (excluding tax) of the line
730 x_curr_invoice_amount : return amt invoiced(excluding tax) for invoice
731
732 DESIGN
733 REFERENCES:
734
735
736 CHANGE Created 09-October-02 BAO
737 HISTORY:
738
739 =======================================================================*/
740 PROCEDURE calc_consumption_cost (
741 x_return_status OUT NOCOPY VARCHAR2,
742 p_quantity IN NUMBER,
743 p_unit_price IN NUMBER,
744 p_tax_code_id IN NUMBER,
745 p_invoice_currency_code IN VARCHAR2,
746 x_invoice_line_amount OUT NOCOPY NUMBER,
747 x_curr_invoice_amount IN OUT NOCOPY NUMBER);
748
749 /*==================================================================
750 PROCEDURE NAME: create_invoice_hdr
751
752 DESCRIPTION: bulk insert records from p_ap_inv_header structure
753 into AP_INVOICES_INTERFACE
754
755
756 PARAMETERS:
757 x_return_status : return status of the procedure
758 p_ap_inv_header : record of tables that stores invoice header info
759 p_from : starting index to insert
760 p_to : ending index to insert
761
762 DESIGN
763 REFERENCES:
764
765
766 CHANGE Created 09-October-02 BAO
767 HISTORY:
768
769 =======================================================================*/
770 PROCEDURE create_invoice_hdr(
771 x_return_status OUT NOCOPY VARCHAR2,
772 p_ap_inv_header IN PO_INVOICES_SV2.invoice_header_rec_type,
773 p_from IN NUMBER,
774 p_to IN NUMBER);
775
776 /*==================================================================
777 PROCEDURE NAME: create_invoice_distr
778
779 DESCRIPTION: bulk insert records from p_consumption structure
780 into AP_INVOICE_LINES_INTERFACE
781
782
783 PARAMETERS:
784 x_return_status : return status of the procedure
785 p_consumption : record of tables that stores invoice line info
786 p_from : starting index to insert
787 p_to : ending index to insert
788
789 DESIGN
790 REFERENCES:
791
792
793 CHANGE Created 09-October-02 BAO
794 HISTORY:
795
796 =======================================================================*/
797 PROCEDURE create_invoice_distr(
798 x_return_status OUT NOCOPY VARCHAR2,
799 p_consumption IN PO_INVOICES_SV2.consump_rec_type,
800 p_from IN NUMBER,
801 p_to IN NUMBER);
802
803 /* <PAY ON USE FPI END> */
804
805 END PO_INVOICES_SV2;
[Home] [Help]