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