1 PACKAGE AP_MATCHING_UTILS_PKG AS
2 /* $Header: apmtutls.pls 120.13 2006/09/29 01:46:06 dgulraja noship $ */
3
4 Procedure Initialize (
5 P_invoice_id IN NUMBER,
6 P_quick_po_id IN NUMBER DEFAULT NULL, -- 5386827
7 P_invoice_num IN OUT NOCOPY VARCHAR2,
8 P_invoice_amount IN OUT NOCOPY NUMBER,
9 P_invoice_date IN OUT NOCOPY DATE,
10 P_vendor_id IN OUT NOCOPY NUMBER,
11 P_vendor_site_id IN OUT NOCOPY NUMBER,
12 P_vendor_name IN OUT NOCOPY VARCHAR2,
13 P_vendor_number IN OUT NOCOPY VARCHAR2,
14 P_vendor_site_code IN OUT NOCOPY VARCHAR2,
15 P_vat_registration_num IN OUT NOCOPY VARCHAR2,
16 P_inv_curr_code IN OUT NOCOPY VARCHAR2,
17 P_inv_type_lookup_code IN OUT NOCOPY VARCHAR2,
18 P_inv_description IN OUT NOCOPY VARCHAR2,
19 P_income_tax_region IN OUT NOCOPY VARCHAR2,
20 -- P_ussgl_transaction_code IN OUT NOCOPY VARCHAR2, - Bug 4277744
21 P_awt_group_id IN OUT NOCOPY NUMBER,
22 P_batch_id IN OUT NOCOPY NUMBER,
23 P_gl_date IN OUT NOCOPY DATE,
24 P_po_number IN OUT NOCOPY VARCHAR2, --Bug 5386827
25 p_vendor_type_lookup_code IN OUT NOCOPY VARCHAR2,
26 P_item_structure_id IN OUT NOCOPY NUMBER,
27 P_payment_terms_id IN OUT NOCOPY NUMBER,
28 P_payment_terms_name IN OUT NOCOPY VARCHAR2,
29 P_period_name IN OUT NOCOPY VARCHAR2,
30 P_minimum_accountable_unit IN OUT NOCOPY NUMBER,
31 P_precision IN OUT NOCOPY NUMBER,
32 P_release_amount_net_of_tax IN OUT NOCOPY NUMBER);
33
34 Procedure Get_Num_PO_Dists (
35 P_line_location_id IN NUMBER,
36 P_num_po_dists IN OUT NOCOPY NUMBER,
37 P_po_distribution_id IN OUT NOCOPY NUMBER);
38
39 Procedure Get_Receipt_Quantities (
40 P_rcv_transaction_id IN NUMBER,
41 P_ordered_qty IN OUT NOCOPY NUMBER,
42 P_cancelled_qty IN OUT NOCOPY NUMBER,
43 P_received_qty IN OUT NOCOPY NUMBER,
44 P_corrected_qty IN OUT NOCOPY NUMBER,
45 P_delivered_qty IN OUT NOCOPY NUMBER,
46 P_transaction_qty IN OUT NOCOPY NUMBER,
47 P_billed_qty IN OUT NOCOPY NUMBER,
48 P_accepted_qty IN OUT NOCOPY NUMBER,
49 P_rejected_qty IN OUT NOCOPY NUMBER);
50
51 Procedure Get_Recpt_Dist_Qty_Billed (
52 P_rcv_transaction_id IN NUMBER,
53 P_po_distribution_id IN NUMBER,
54 P_billed_qty IN OUT NOCOPY NUMBER);
55
56 /*-------------------------------------------------------------------------
57 This procedure will be called by PO whenever the receipt is adjusted
58 The input parameters refer to
59 p_parent_rcv_txn_id : the original 'RECEIVE' transaction,
60 p_adjusted_rcv_txn_id : the 'ADJUST' or 'RETURN' transaction
61 p_adjusted_date : the transaction_date on ADJUST or RETURN transaction
62 p_user_id : WHO column information from the form
63 p_login_id : WHO column information from the form
64 --------------------------------------------------------------------------*/
65 Procedure Insert_Adjusted_Receipt_IDs (
66 p_parent_rcv_txn_id IN NUMBER,
67 p_adjusted_rcv_txn_id IN NUMBER,
68 p_adjusted_date IN DATE,
69 p_user_id IN NUMBER,
70 p_login_id IN NUMBER);
71
72 /*--------------------------------------------------------------------------
73 This Function will be used to get any existing correction quantity for a
74 PO or Receipt Macthed Invoice and will be used new 11ix Correction form
75 p_invoice_id : Invoice_id of the Invoice being corrected
76 p_line_number : Line Number of the Invoice being corrected
77 ---------------------------------------------------------------------------*/
78 Function Get_Correction_Quantity (
79 p_invoice_id IN NUMBER,
80 p_line_number IN NUMBER)
81 Return Number;
82
83 /*--------------------------------------------------------------------------
84 This Function will be used to get any existing unit price correction for a
85 PO or Receipt Macthed Invoice and will be used new 11ix Correction form
86 p_invoice_id : Invoice_id of the Invoice being corrected
87 p_line_number : Line Number of the Invoice being corrected
88 ---------------------------------------------------------------------------*/
89 Function Get_Correction_Unit_Price (
90 p_invoice_id IN NUMBER,
91 p_line_number IN NUMBER)
92 Return Number;
93
94 /*--------------------------------------------------------------------------
95 This Function will be used to get any existing correction amount for a
96 PO or Receipt Macthed Invoice and will be used new 11ix Correction form
97 p_invoice_id : Invoice_id of the Invoice being corrected
98 p_line_number : Line Number of the Invoice being corrected
99 ---------------------------------------------------------------------------*/
100 Function Get_Correction_Amount (
101 p_invoice_id IN NUMBER,
102 p_line_number IN NUMBER)
103 Return Number;
104
105 /*--------------------------------------------------------------------------
106 This Function will be used to get any existing correction quantity for a
107 PO or Receipt Macthed Invoice Dist and will be used new 11ix Correction form
108 p_invoice_dist_id : Invoice_distribution_id of the Invoice being corrected
109 ---------------------------------------------------------------------------*/
110 Function Get_Correction_Quantity_Dist (
111 p_invoice_dist_id IN NUMBER)
112 Return Number;
113
114 /*This procedure will get number of distributions per invoice_line */
115 Procedure Get_Num_Line_Dists (
116 P_invoice_id IN NUMBER,
117 P_invoice_line_number IN NUMBER,
118 P_num_line_dists IN OUT NOCOPY NUMBER,
119 P_inv_distribution_id IN OUT NOCOPY NUMBER);
120
121 --This procedure when provided with a invoice lines will call
122 --the appropriate matching api according to the information
123 --present on the line.
124 Procedure Match_Invoice_Line(
125 P_Invoice_Id IN NUMBER,
126 P_Invoice_Line_Number IN NUMBER,
127 P_Overbill_Flag IN VARCHAR2,
128 P_Calling_Sequence IN VARCHAR2);
129
130 --This procedure is added amount based matching in 11ix
131
132 Procedure Get_Recpt_Dist_Amt_Billed (
133 P_rcv_transaction_id IN NUMBER,
134 P_po_distribution_id IN NUMBER,
135 P_billed_amt IN OUT NOCOPY NUMBER);
136
137
138 /* This function is added for getting the avialable correction amount for a
139 invoice distribution for Invoice Line Correction */
140
141 Function Get_Avail_Dist_Corr_Amount (
142 P_invoice_dist_id IN NUMBER)
143 Return Number;
144
145 /* This function is added for getting the avialable correction amount for a
146 invoice line for Invoice Line Correction */
147
148 Function Get_Avail_Line_Corr_Amount (
149 P_invoice_id IN NUMBER,
150 P_line_number IN NUMBER)
151 Return Number;
152
153 /* This function is added for getting the avialable correction qty for a
154 invoice line for Invoice Line Correction */
155
156 Function Get_Avail_Line_Corr_Qty (
157 P_invoice_id IN NUMBER,
158 P_line_number IN NUMBER)
159 Return Number;
160
161 /* This function is added for getting the associated charged for a
162 invoice line for Invoice Line Correction */
163
164 Function Get_Line_Assoc_Charge (
165 P_invoice_id IN NUMBER,
166 P_line_number IN NUMBER)
167 Return Number;
168
169 /* This function is added for getting the avialable correction amount for a
170 invoice for Invoice Line Correction */
171
172 Function Get_Avail_Inv_Corr_Amount (
173 P_invoice_id IN NUMBER)
174 Return Number;
175
176
177 /*This procedure is used to do the upgrade of PO Shipment and its related
178 distributions, if the shipment was not already upgraded and it is a historical
179 shipment*/
180 /*=============================================================================
181 | FUNCTION - AP_Upgrade_Po_Shipment
182 |
183 | DESCRIPTION
184 | This public procedure is used to do the upgrade of PO Shipment and
185 | its related distributions, if the shipment was not already upgraded
186 | and it is a historical shipment.
187 |
188 |
189 | PARAMETERS
190 | P_Invoice_Type_Lookup_Code - Invoice Type
191 | P_Event_Class_Code - event class code
192 | P_error_code - Error code to be returned
193 | P_calling_sequence - Calling sequence
194 |
195 | MODIFICATION HISTORY
196 | DATE Author Action
197 | 28-MAY-2005 SMYADAM Created
198 |
199 *============================================================================*/
200
201 Procedure AP_Upgrade_Po_Shipment(P_Po_Line_Location_id IN NUMBER,
202 P_Calling_Sequence IN VARCHAR2);
203
204
205 /*API to Automatically recoup Prepayment invoice lines which are matched
206 to the same PO Line as the Item Line on the Standard invoice. */
207
208 /*=============================================================================
209 | FUNCTION - AP_Recoup_Invoice_Line
210 |
211 | DESCRIPTION
212 | Public function to Automatically recoup Prepayment invoice lines which
213 | are matched to the same PO Line as the Item Line on the Standard invoice.
214 |
215 | PARAMETERS
216 | P_Invoice_Id - Id of the STD invoice which is recouping from the
217 | Prepayment Invoice
218 | P_Invoice_Line_Number - Line_Number of the ITEM line which is recouping
219 | prepayment invoice
220 | P_Amount_To_Recoup - Amount to recoup
221 |
222 |
223 | MODIFICATION HISTORY
224 | DATE Author Action
225 | 28-MAY-2005 SMYADAM Created
226 |
227 *============================================================================*/
228
229 Function Ap_Recoup_Invoice_Line(P_Invoice_Id IN NUMBER,
230 P_Invoice_Line_Number IN NUMBER,
231 P_Amount_To_Recoup IN NUMBER,
232 P_Po_Line_Id IN NUMBER,
233 P_Vendor_Id IN NUMBER,
234 P_Vendor_Site_Id IN NUMBER,
235 P_Accounting_Date IN DATE,
236 P_Period_Name IN VARCHAR2,
237 P_User_Id IN NUMBER,
238 P_Last_Update_Login IN NUMBER,
239 P_Error_Message OUT NOCOPY VARCHAR2,
240 P_Calling_Sequence IN VARCHAR2)
241 RETURN BOOLEAN;
242 /*=============================================================================
243 | FUNCTION - Get_Inv_Line_Recouped_Amount
244 |
245 | DESCRIPTION
246 | Public function to get the total amount recouped by the ITEM line on the
247 | standard invoice.
248 |
249 | PARAMETERS
250 | P_Invoice_Id - Invoice id of the invoice which has recouped.
251 | P_Invoice_Line_Numbe - Line Number of the ITEM line which has recouped
252 | automatically from 1 or more prepayment invoices/lines
253 |
254 |
255 | MODIFICATION HISTORY
256 | DATE Author Action
257 | 28-MAY-2005 SMYADAM Created
258 |
259 *============================================================================*/
260 FUNCTION Get_Inv_Line_Recouped_Amount(P_Invoice_Id IN NUMBER,
261 P_Invoice_Line_Number IN NUMBER)
262 RETURN NUMBER;
263
264
265 /*=============================================================================
266 | FUNCTION - Get_Recoup_Amt_Per_Prepay_Line
267 |
268 | DESCRIPTION
269 | Public function to get the total amount recouped by the ITEM line on the
270 | standard invoice for a particular prepayment invoice line.This amount
271 | doesn't include the related tax amount applied.
272 |
273 | PARAMETERS
274 | P_Invoice_Id - Invoice id of the invoice which has recouped.
275 | P_Invoice_Line_Number - Line Number of the ITEM line which has recouped
276 | automatically from 1 or more prepayment invoices/lines
277 | P_Prepay_Invoice_Id - Invoice_Id of the prepayment invoice.
278 | P_Prepay_Line_Number - Prepayment invoice line number.
279 |
280 | MODIFICATION HISTORY
281 | DATE Author Action
282 | 28-MAY-2005 SMYADAM Created
283 |
284 *============================================================================*/
285 FUNCTION Get_Recoup_Amt_Per_Prepay_Line(P_Invoice_Id IN NUMBER,
286 P_Invoice_Line_Number IN NUMBER,
287 P_Prepay_Invoice_Id IN NUMBER,
288 P_Prepay_Line_Number IN NUMBER) RETURN NUMBER;
289
290
291 /*=============================================================================
292 | FUNCTION - Get_Recoup_Tax_Amt_Per_Ppay_Ln
293 |
294 | DESCRIPTION
295 | Public function to get the total TAX amount recouped by the ITEM line on the
296 | standard invoice for a particular prepayment invoice line.
297 |
298 | PARAMETERS
299 | P_Invoice_Id - Invoice id of the invoice which has recouped.
300 | P_Invoice_Line_Number - Line Number of the ITEM line which has recouped
301 | automatically from 1 or more prepayment invoices/lines
302 | P_Prepay_Invoice_Id - Invoice_Id of the prepayment invoice.
303 | P_Prepay_Line_Number - Prepayment invoice line number.
304 |
305 | MODIFICATION HISTORY
306 | DATE Author Action
307 | 28-MAY-2005 SMYADAM Created
308 |
309 *============================================================================*/
310 FUNCTION Get_Recoup_Tax_Amt_Per_Ppay_Ln(P_Invoice_Id IN NUMBER,
311 P_Invoice_Line_Number IN NUMBER,
312 P_Prepay_Invoice_Id IN NUMBER,
313 P_Prepay_Line_Number IN NUMBER) RETURN NUMBER;
314
315
316 /*=============================================================================
317 | FUNCTION - Match_To_Rcv_Shipment_Line
318 |
319 | DESCRIPTION
320 | Public api to match a invoice line to a rcv_shipment_line. This api will
321 | need the rcv_shipment_line_id along with the other needed matching details
322 | to be populated on the invoice line. The api will prorate the quantity/amount
323 | to be matched to the first available delivery rcv transactions of this
324 | rcv_shipment_line, and any remaining quantity/amount will be prorated to all
325 | rcv_transactions based on the ordered_billed quantity/amount. This wrapper
326 | calls the original receipt matching apis in a loop for each of the
327 | rcv transaction.
328 |
329 | PARAMETERS
330 | P_Invoice_Id - Invoice id of the invoice which need to be matched.
331 | P_Invoice_Line_Number - Line Number of the ITEM line which needs to
332 | matched to the rcv shipment line.
333 |
334 | MODIFICATION HISTORY
335 | DATE Author Action
336 | 27-JUL-2005 SMYADAM Created
337 |
338 *============================================================================*/
339
340 PROCEDURE Match_To_Rcv_Shipment_Line (P_Invoice_Id IN NUMBER,
341 P_Invoice_Line_Number IN NUMBER,
342 P_Calling_Sequence IN VARCHAR2);
343
344
345 --Bug 5524881 ISP Receipt Matching
346 /*=============================================================================
347 | FUNCTION - Get_rcv_ship_qty_amt
348 |
349 | DESCRIPTION
350 | This API is used by the SupplierPortal in the PO Search Page to display
351 | the quantity_recieved, quantity_billed, amount_recieved, amount_billed
352 | etc for a Reciept Shipment Line
353 | PARAMETERS
354 | p_rcv_shipment_line_id Receipt Shipment Line Id,
355 | p_matching_basis Qnantity or Amount
356 | p_returned_item This parameter can take six different values
357 | and the function returns value associated with
358 | this parameter for a Receipt Ship Line.
359 |
360 | MODIFICATION HISTORY
361 | DATE Author Action
362 | 09/27/06 dgulraja Created
363 |
364 *============================================================================*/
365 FUNCTION Get_rcv_ship_qty_amt(p_rcv_shipment_line_id IN NUMBER,
366 p_matching_basis IN VARCHAR2,
367 p_returned_item IN VARCHAR2)
368 RETURN NUMBER;
369
370
371 END AP_MATCHING_UTILS_PKG;
372