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