1 PACKAGE AP_RETRO_PRICING_UTIL_PKG AS
2 /* $Header: apretrus.pls 120.2.12000000.2 2007/03/20 07:20:06 gagrawal ship $ */
3
4 /*=============================================================================
5 | FUNCTION - Are_Original_Invoices_Valid()
6 |
7 | DESCRIPTION
8 | This function checks for a particular instruction if all the base
9 | matched Invoices(along with Price Corrections,Qty Corrections) for the
10 | retropriced shipments(Records in AP_INVOICE_LINES_INTERFACE) are valid
11 |
12 | PARAMETERS
13 | p_instruction_id
14 | p_org_id
15 } p_orig_invoices_valid --OUT
16 |
17 | MODIFICATION HISTORY
18 | Date Author Description of Change
19 | 29-JUL-2003 dgulraja Creation
20 |
21 *============================================================================*/
22 FUNCTION Are_Original_Invoices_Valid(
23 p_instruction_id IN NUMBER,
24 p_org_id IN NUMBER,
25 p_orig_invoices_valid OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
26
27
28 /*=============================================================================
29 | FUNCTION - Are_Holds_Ok()
30 |
31 | DESCRIPTION
32 | This function checks for a particular instruction if all the base
33 | matched Invoices(along with Price Corrections,Qty Corrections) for the
34 | retropriced shipments(Records in AP_INVOICE_LINES_INTERFACE) has any holds
35 | (other than Price Hold)
36 |
37 | PARAMETERS
38 | p_instruction_id
39 | p_org_id
40 } p_orig_invoices_valid --OUT
41 |
42 | MODIFICATION HISTORY
43 | Date Author Description of Change
44 | 29-JUL-2003 dgulraja Creation
45 |
46 *============================================================================*/
47 FUNCTION Are_Holds_Ok(
48 p_instruction_id IN NUMBER,
49 p_org_id IN NUMBER,
50 p_orig_invoices_valid OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
51
52
53 /*=============================================================================
54 | FUNCTION - Is_sequence_assigned
55 |
56 | DESCRIPTION
57 | This function checks whether or not a sequence is associated with
58 | a particular document category. Added for the bug5769161.
59 |
60 | PARAMETERS
61 |
62 | p_document_category_code
63 | p_set_of_books_id
64 } p_is_sequence_assigned --OUT
65 |
66 | MODIFICATION HISTORY
67 | Date Author Description of Change
68 | 12-MAR-2007 gagrawal Creation
69 |
70 *============================================================================*/
71 FUNCTION Is_sequence_assigned(
72 p_document_category_code IN VARCHAR2,
73 p_set_of_books_id IN NUMBER,
74 p_is_sequence_assigned OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
75
76
77
78 /*=============================================================================
79 | FUNCTION - Ppa_Already_Exists()
80 |
81 | DESCRIPTION
82 | This function checks if PPA document already exists for a base matched
83 | invoice line that needs to be retropriced. The Adjustment Corrections on the
84 | base matched Invoice doesn't guarentee the existence of a PPA document.
85 | In case multiple PPA document exist for the base matched Invoice then we
86 | select the last PPA document created for reversal.
87 | Note: MAX(invoice_id) insures that we reverse the latest PPA.
88 |
89 | PARAMETERS
90 | P_invoice_id
91 | P_line_number
92 | p_ppa_exists --OUT
93 | P_existing_ppa_inv_id --OUT
94 |
95 | MODIFICATION HISTORY
96 | Date Author Description of Change
97 | 29-JUL-2003 dgulraja Creation
98 |
99 *============================================================================*/
100 FUNCTION Ppa_Already_Exists(
101 P_invoice_id IN NUMBER,
102 P_line_number IN NUMBER,
103 p_ppa_exists OUT NOCOPY VARCHAR2,
104 P_existing_ppa_inv_id OUT NOCOPY NUMBER) RETURN BOOLEAN;
105
106
107
108 /*=============================================================================
109 | FUNCTION - Ipv_Dists_Exists()
110 |
111 | DESCRIPTION
112 | This function checks if IPV distributions exist for base matched
113 | Invoice Line(also Price Correction and Qty Correction Lines) for a
114 | retropriced shipment
115 |
116 | PARAMETERS
117 | P_invoice_id
118 | P_line_number
119 | p_ipv_dists_exist --OUT
120 |
121 | MODIFICATION HISTORY
122 | Date Author Description of Change
123 | 29-JUL-2003 dgulraja Creation
124 |
125 *============================================================================*/
126 FUNCTION Ipv_Dists_exists(
127 p_invoice_id IN NUMBER,
128 p_line_number IN NUMBER,
129 p_ipv_dists_exist OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
130
131
132 /*=============================================================================
133 | FUNCTION - Erv_Dists_Exists()
134 |
135 | DESCRIPTION
136 | This function checks if ERV distributions exist for base matched
137 | Invoice Line(also Price Correction and Qty Correction Lines) for a
138 | retropriced shipment. This function is called Compute_IPV_Adjustment_Corr
139 |
140 | PARAMETERS
141 | P_invoice_id
142 | P_line_number
143 | p_erv_dists_exist OUT
144 |
145 | MODIFICATION HISTORY
146 | Date Author Description of Change
147 | 29-JUL-2003 dgulraja Creation
148 |
149 *============================================================================*/
150 FUNCTION Erv_Dists_Exists(
151 p_invoice_id IN NUMBER,
152 p_line_number IN NUMBER,
153 p_erv_dists_exist OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
154
155
156 /*=============================================================================
157 | FUNCTION - Adj_Corr_Exists()
158 |
159 | DESCRIPTION
160 | This function checks if Adjustment Corrections exist for base matched
161 | Invoice Line(also Price Correction and Qty Correction Lines) for a
162 | retropriced shipment.
163 |
164 | PARAMETERS
165 | P_invoice_id
166 | P_line_number
167 | p_adj_corr_exists OUT
168 |
169 | MODIFICATION HISTORY
170 | Date Author Description of Change
171 | 29-JUL-2003 dgulraja Creation
172 |
173 *============================================================================*/
174 FUNCTION Adj_Corr_Exists(
175 p_invoice_id IN NUMBER,
176 p_line_number IN NUMBER,
177 p_adj_corr_exists OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
178
179 /*=============================================================================
180 | FUNCTION - Corrections_Exists()
181 |
182 | DESCRIPTION
183 | This function returns Price or Qty Corrections Lines for affected base
184 | matched Invoice Line depending upon the line_type_lookup_code passed to the
185 | function
186 |
187 | PARAMETERS
188 | P_invoice_id
189 | P_line_number
190 | p_adj_corr_exists OUT
191 |
192 | MODIFICATION HISTORY
193 | Date Author Description of Change
194 | 29-JUL-2003 dgulraja Creation
195 |
196 *============================================================================*/
197 FUNCTION Corrections_Exists(
198 p_invoice_id IN NUMBER,
199 p_line_number IN NUMBER,
200 p_line_type_lookup_code IN VARCHAR2,
201 p_lines_list OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_lines_list_type,
202 p_corrections_exist OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
203
204 /*=============================================================================
205 | FUNCTION - Tipv_Exists()
206 |
207 | DESCRIPTION
208 | This function returns all the Tax lines allocated to the base matched
209 | (or Price/Qty Correction) line that is affected by Retropricing. The function
210 | insures that the Tax line has TIPV distribtuions that need to be
211 | Retro-Adjusted.
212 | Note : Only EXCLUSIVE tax is supported for Po matched lines. TIPV distributions
213 | can only exist on the Tax line if the original invoce line(that the tax
214 | line is allocated to) has IPV distributions. Futhermore this check is
215 | only done if original invoice has IPV dists and the Original Invoice
216 | has not been retro-adjusted
217 |
218 |
219 | PARAMETERS
220 | P_invoice_id
221 | P_line_number
222 | p_tax_lines_list --OUT
223 | p_tipv_exist --OUT
224 | MODIFICATION HISTORY
225 | Date Author Description of Change
226 | 29-JUL-2003 dgulraja Creation
227 |
228 *============================================================================*/
229 FUNCTION Tipv_Exists(
230 p_invoice_id IN NUMBER,
231 p_invoice_line_number IN NUMBER,
232 p_tax_lines_list OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_lines_list_type,
233 p_tipv_exist OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
234
235
236 /*=============================================================================
237 | FUNCTION - Terv_Dists_Exists()
238 |
239 | DESCRIPTION
240 | This function is called from Compute_TIPV_Adjustment_Corr to check if TERV
241 | distributions exist for Tax line(allocated to a original line for a
242 | retropriced shipment). Furthermore check is only made if the allocated Tax lines
243 | have TIPV distributions.
244 |
245 |
246 |
247 | PARAMETERS
248 | P_invoice_id
249 | P_line_number
250 | p_terv_ccid OUT
251 | p_terv_dists_exist OUT
252 | MODIFICATION HISTORY
253 | Date Author Description of Change
254 | 29-JUL-2003 dgulraja Creation
255 |
256 *============================================================================*/
257 FUNCTION Terv_Dists_Exists(
258 p_invoice_id IN NUMBER,
259 p_line_number IN NUMBER,
260 p_terv_dists_exist OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
261
262 /*=============================================================================
263 | FUNCTION - Get_Invoice_distribution_id()
264 |
265 | DESCRIPTION
266 | This function returns the invoice_distribution_id
267 |
268 | PARAMETERS
269 | NONE
270 |
271 | MODIFICATION HISTORY
272 | Date Author Description of Change
273 | 29-JUL-2003 dgulraja Creation
274 |
275 *============================================================================*/
276 FUNCTION Get_Invoice_distribution_id RETURN NUMBER;
277
278
279
280 /*=============================================================================
281 | FUNCTION - Get_Ccid()
282 |
283 | DESCRIPTION
284 | This function returns the ccid depending on the Parameter
285 | p_invoice_distribution_id. This function is called in context
286 | of IPV distributions on the base matched line or Price Corrections.
287 | p_invoice_distribution_id
288 | = Related_dist_Id for the IPV distributions on the base matched line.
289 | = corrected_dist_id for the IPV distributions on the PC Line.
290 |
291 |
292 | PARAMETERS
293 | p_invoice_distribution_id
294 |
295 | MODIFICATION HISTORY
296 | Date Author Description of Change
297 | 29-JUL-2003 dgulraja Creation
298 |
299 *============================================================================*/
300 FUNCTION get_ccid(
301 p_invoice_distribution_id IN NUMBER) RETURN NUMBER;
302
303
304 /*=============================================================================
305 | FUNCTION - Get_Dist_Type_lookup_code()
306 |
307 | DESCRIPTION
308 | This function returns the Dist_Type_lookup_code depending on the
309 | parameter invoice_distribution_id. This function is called in context
310 | of IPV distributions on the base matched line or Price Corrections.
311 | p_invoice_distribution_id
312 | = Related_dist_Id for the IPV distributions on the base matched line.
313 | = corrected_dist_id for the IPV distributions on the PC Line.
314 |
315 |
316 | PARAMETERS
317 | p_invoice_distribution_id
318 |
319 | MODIFICATION HISTORY
320 | Date Author Description of Change
321 | 29-JUL-2003 dgulraja Creation
322 |
323 *============================================================================*/
324 FUNCTION Get_Dist_Type_lookup_code(
325 p_invoice_distribution_id IN NUMBER) RETURN VARCHAR2;
326
327
328 /*=============================================================================
329 | FUNCTION - get_max_ppa_line_num()
330 |
331 | DESCRIPTION
332 | This function is called to get the max line number for the PPA Document
333 | from the global temp table for a given PPA invoice_id.
334 |
335 | PARAMETERS
336 | P_invoice_id
337 |
338 | MODIFICATION HISTORY
339 | Date Author Description of Change
340 | 29-JUL-2003 dgulraja Creation
341 |
342 *============================================================================*/
343 FUNCTION get_max_ppa_line_num(
344 P_invoice_id IN NUMBER) RETURN NUMBER;
345
346
347 /*=============================================================================
348 | FUNCTION - Get_Exchange_Rate()
349 |
350 | DESCRIPTION
351 | This function returns the Exchange rate on the Receipt or PO depending
352 | on the P_match paramter.
353 |
354 | PARAMETERS
355 | P_match
356 | p_id
357 |
358 | MODIFICATION HISTORY
359 | Date Author Description of Change
360 | 29-JUL-2003 dgulraja Creation
361 |
362 *============================================================================*/
363 FUNCTION get_exchange_rate(
364 P_match IN VARCHAR2,
365 p_id IN NUMBER) RETURN NUMBER;
366
367
368 /*============================================================================
369 | FUNCTION - get_invoice_amount()
370 |
371 | DESCRIPTION
372 | This function sums the invoice line amounts for the PPA docs created
373 | in the Global temporary tables for a particular invoice.
374 |
375 | PARAMETERS
376 | NONE
377 |
378 | MODIFICATION HISTORY
379 | Date Author Description of Change
380 | 29-JUL-2003 dgulraja Creation
381 |
382 *==========================================================================*/
383 --Bugfix:4681253
384 FUNCTION get_invoice_amount(
385 P_invoice_id IN NUMBER,
386 p_invoice_currency_code IN VARCHAR2) RETURN NUMBER;
387
388
392 | DESCRIPTION
389 /*============================================================================
390 | FUNCTION - Get_corresponding_retro_DistId()
391 |
393 | This function returns the distribution_id of the corresponding Retro
394 | Expense/Accrual distribution.
395 |
396 | PARAMETERS
397 | NONE
398 |
399 | MODIFICATION HISTORY
400 | Date Author Description of Change
401 | 29-JUL-2003 dgulraja Creation
402 |
403 *==========================================================================*/
404 FUNCTION Get_corresponding_retro_DistId(
405 p_match_type IN VARCHAR2,
406 p_ccid IN NUMBER) RETURN NUMBER;
407
408
409 /*============================================================================
410 | FUNCTION - Create_Line()
411 |
412 | DESCRIPTION
413 | This function is called to create zero amount adjustments lines
414 | for IPV reversals, reversals for existing Po Price Adjustment PPA lines,
415 | and to create Po Price Adjsutment lines w.r.t the Retropriced Amount.
416 |
417 | PARAMETERS
418 | p_lines_rec
419 | P_calling_sequence
420 |
421 | MODIFICATION HISTORY
422 | Date Author Description of Change
423 | 29-JUL-2003 dgulraja Creation
424 |
425 *==========================================================================*/
426 FUNCTION Create_Line(
427 p_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
428 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
429
430
431 /*============================================================================
432 | FUNCTION - Get_Base_Match_Lines()
433 |
434 | DESCRIPTION
435 | This function returns the list of all base matched Invoice Lines
436 | for the Instruction that are candidate for retropricing.
437 | Note: Retro price Adjustments and Adjustment corrections may already
438 | exist for these base matched lines.
439 |
440 | PARAMETERS
441 | p_instruction_id
442 | p_instruction_line_id
443 | p_base_match_lines_list
444 | P_calling_sequence
445 |
446 | MODIFICATION HISTORY
447 | Date Author Description of Change
448 | 29-JUL-2003 dgulraja Creation
449 |
450 *==========================================================================*/
451 FUNCTION Get_Base_Match_Lines(
452 p_instruction_id IN NUMBER,
453 p_instruction_line_id IN NUMBER,
454 p_base_match_lines_list OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_lines_list_type,
455 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
456
457
458
459 /*============================================================================
460 | FUNCTION - Create_ppa_Invoice()
461 |
462 | DESCRIPTION
463 | This function inserts a temporary Ppa Invoice Header in the Global
464 | Temporary Tables.
465 |
466 | PARAMETERS
467 | p_instruction_id
468 | p_instruction_line_id
469 | p_base_match_lines_list
470 | P_calling_sequence
471 |
472 | MODIFICATION HISTORY
473 | Date Author Description of Change
474 | 29-JUL-2003 dgulraja Creation
475 |
476 *==========================================================================*/
477 FUNCTION Create_ppa_Invoice(
478 p_instruction_id IN NUMBER,
479 p_invoice_id IN NUMBER, --Base match line's invoice_id
480 p_line_number IN NUMBER, --Base match line's line number
481 p_batch_id IN NUMBER,
482 p_ppa_invoice_rec OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_rec_type,
483 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
484
485 /*============================================================================
486 | FUNCTION - get_invoice_num()
487 |
488 | DESCRIPTION
489 | This function is called from the APXIIMPT.rdf
490 |
491 | PARAMETERS
492 | p_invoice_id
493 |
494 | MODIFICATION HISTORY
495 | Date Author Description of Change
496 | 29-JUL-2003 dgulraja Creation
497 |
498 *==========================================================================*/
499 FUNCTION get_invoice_num(
503 /*============================================================================
500 p_invoice_id IN NUMBER) RETURN VARCHAR2;
501
502
504 | FUNCTION - get_corrected_pc_line_num()
505 |
506 | DESCRIPTION
507 | This function is called to get the corrected line number for the
508 | Ajustment Correction Lines on the PPA document.
509 | Note: These lines correct the Zero Line Adjustments Lines for a PC.
510 |
511 | PARAMETERS
512 | p_invoice_id
513 |
514 | MODIFICATION HISTORY
515 | Date Author Description of Change
516 | 29-JUL-2003 dgulraja Creation
517 |
518 *==========================================================================*/
519 FUNCTION get_corrected_pc_line_num(
520 p_invoice_id IN NUMBER,
521 p_line_number IN NUMBER) RETURN NUMBER;
522
523 /*=============================================================================
524 | FUNCTION - Get_Erv_Ccid()
525 |
526 | DESCRIPTION
527 | This function returns the ccid of the ERV distribution related to the
528 | IPV distribution on the Price Correction and (IPV+Item) distribution
529 | on the base match or qty correction.
530 |
531 |
532 | PARAMETERS
533 | p_invoice_distribution_id
534 |
535 | MODIFICATION HISTORY
536 | Date Author Description of Change
537 | 29-JUL-2003 dgulraja Creation
538 |
539 *============================================================================*/
540 FUNCTION Get_Erv_Ccid(
541 p_invoice_distribution_id IN NUMBER) RETURN NUMBER;
542
543 /*=============================================================================
544 | FUNCTION - Get_Terv_Ccid()
545 |
546 | DESCRIPTION
547 | This function returns the ccid of the TERV distribution related to the
548 | TIPV distribution.
549 |
550 | PARAMETERS
551 | p_invoice_distribution_id
552 |
553 | MODIFICATION HISTORY
554 | Date Author Description of Change
555 | 29-JUL-2003 dgulraja Creation
556 |
557 *============================================================================*/
558 FUNCTION Get_Terv_Ccid(
559 p_invoice_distribution_id IN NUMBER) RETURN NUMBER;
560
561
562
563 END AP_RETRO_PRICING_UTIL_PKG;
[Home] [Help]