[Home] [Help]
PACKAGE: APPS.ARP_DET_DIST_PKG
Source
1 PACKAGE ARP_DET_DIST_PKG AS
2 /* $Header: ARPDDS.pls 120.18.12010000.3 2008/11/13 14:50:47 dgaurab ship $ */
3
4 SUBTYPE ae_rule_rec_type IS ARP_ACCT_MAIN.ae_app_rule_rec_type;
5
6 /*------------------------------------------------------------------+
7 | the_concern_bucket |
8 +------------------------------------------------------------------+
9 | What is the bucket of allocation amount to use base on line type |
10 | This is line level proration not for distribution |
11 +------------------------------------------------------------------+
12 | Return the bucket based on the input parameters |
13 +------------------------------------------------------------------+
14 | p_pay_adj APP / ADJ / ED / UNED
15 | p_line_type LINE / TAX / FREIGHT
16 | p_line_adj Adj line bucket
17 | p_tax_adj Adj tax bucket
18 | p_frt_adj Adj freight bucket
19 | p_chrg_adj Adj charge bucket
20 | p_line_applied App line
21 | p_tax_applied App tax
22 | p_frt_applied App freight
23 | p_chrg_applied App charge
24 | p_line_ed ED line
25 | p_tax_ed ED tax
26 | p_frt_ed ED freight
27 | p_chrg_ed ED charge
28 | p_line_uned UNED line
29 | p_tax_uned UNED tax
30 | p_frt_uned UNED freight
31 | p_chrg_uned UNED charge
32 | p_acctd Y / N accouting bucket
33 | p_chrg_bucket Y / N charge bucket
34 | p_frt_bucket Y / N frt bucket
35 | p_base_currency function currency
36 +------------------------------------------------------------------*/
37 FUNCTION the_concern_bucket
38 (p_pay_adj IN VARCHAR2,
39 p_line_type IN VARCHAR2,
40 p_acctd IN VARCHAR2,
41 p_chrg_bucket IN VARCHAR2,
42 p_frt_bucket IN VARCHAR2)
43 RETURN NUMBER;
44
45
46 /*-------------------------------------------------------------------------+
47 | Trx_level_cash_apply |
48 +-------------------------------------------------------------------------+
49 | 1) get_invoice_line_info |
50 | 2) prepare_group_for_proration |
51 | 3) maj_group_line |
52 | 4) prepare_trx_line_proration |
53 | 5) maj_line |
54 | 6) update_ctl_rem_orig |
55 | 7) store_group_id |
56 +-------------------------------------------------------------------------+
57 | parameter: |
58 | p_customer_trx_id transaction id
59 | p_app_rec ar receivable application record
60 | p_ae_sys_rec ar system parameter
61 +-------------------------------------------------------------------------*/
62 PROCEDURE Trx_level_cash_apply
63 (p_customer_trx IN ra_customer_trx%ROWTYPE,
64 p_app_rec IN ar_receivable_applications%ROWTYPE,
65 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
66 p_gt_id IN VARCHAR2 DEFAULT NULL);
67
68
69 /*-------------------------------------------------------------------------+
70 | Trx_gp_level_cash_apply |
71 +-------------------------------------------------------------------------+
72 | 1) get_invoice_line_info_per_grp |
73 | 2) prepare_group_for_proration |
74 | 3) maj_group_line |
75 | 4) prepare_trx_line_proration |
76 | 5) maj_line |
77 | 6) update_ctl_rem_orig |
78 | 7) store_group_id |
79 +-------------------------------------------------------------------------+
80 | parameter: |
81 | p_customer_trx_id transaction id
82 | p_group_id source_data_key1
83 | p_app_rec ar receivable application record
84 | p_ae_sys_rec ar system parameter
85 +-------------------------------------------------------------------------*/
86 PROCEDURE Trx_gp_level_cash_apply
87 (p_customer_trx IN ra_customer_trx%ROWTYPE,
88 -- p_group_id IN VARCHAR2,
89 --{HYUBPAGP
90 p_source_data_key1 IN VARCHAR2,
91 p_source_data_key2 IN VARCHAR2,
92 p_source_data_key3 IN VARCHAR2,
93 p_source_data_key4 IN VARCHAR2,
94 p_source_data_key5 IN VARCHAR2,
95 --}
96 p_app_rec IN ar_receivable_applications%ROWTYPE,
97 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
98 p_gt_id IN VARCHAR2 DEFAULT NULL);
99
100
101 /*-------------------------------------------------------------------------+
102 | Trx_line_level_cash_apply |
103 +-------------------------------------------------------------------------+
104 | 1) get_invoice_line_info_per_line |
105 | 2) prepare_group_for_proration |
106 | 3) maj_group_line |
107 | 4) prepare_trx_line_proration |
108 | 5) maj_line |
109 | 6) update_ctl_rem_orig |
110 | 7) store_group_id |
111 +-------------------------------------------------------------------------+
112 | parameter: |
113 | p_customer_trx_id transaction id
114 | p_customer_trx_line_id customer_trx_line_id
115 | p_app_rec ar receivable application record
116 | p_ae_sys_rec ar system parameter
117 +-------------------------------------------------------------------------*/
118 PROCEDURE Trx_line_level_cash_apply
119 (p_customer_trx IN ra_customer_trx%ROWTYPE,
120 p_customer_trx_line_id IN VARCHAR2,
121 p_log_inv_line IN VARCHAR2 DEFAULT 'N',
122 p_app_rec IN ar_receivable_applications%ROWTYPE,
123 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
124 p_gt_id IN VARCHAR2 DEFAULT NULL );
125
126
127 /*-------------------------------------------------------------------------+
128 | Trx_level_direct_adjust |
129 +-------------------------------------------------------------------------+
130 | 1) get_invoice_line_info |
131 | 2) prepare_group_for_proration |
132 | 3) maj_group_line |
133 | 4) prepare_trx_line_proration |
134 | 5) maj_line |
135 | 6) update_ctl_rem_orig |
136 | 7) store_gt_id |
137 +-------------------------------------------------------------------------+
138 | parameter: |
139 | p_customer_trx_id transaction id
140 | p_adj_rec ar adjustment record
141 | p_ae_sys_rec ar system parameter
142 +-------------------------------------------------------------------------*/
143 PROCEDURE Trx_level_direct_adjust
144 (p_customer_trx IN ra_customer_trx%ROWTYPE,
145 p_adj_rec IN ar_adjustments%ROWTYPE,
146 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
147 p_gt_id IN NUMBER DEFAULT NULL);
148
149
150 /*-----------------------------------------------------------+
151 | PROCEDURE gt_initial |
152 +-----------------------------------------------------------+
153 | Note: procedure is to be called at commit point. |
154 | will create in ar_line_application_detail the detail |
155 | distribution for llca at all level |
156 +-----------------------------------------------------------*/
157 --PROCEDURE gt_initial;
158
159 /*-----------------------------------------------------------+
160 | PROCEDURE set_original_rem_amt |
161 +-----------------------------------------------------------+
162 | Note: procedure updates the balance on ctl line when trx |
163 | does not have any activities on it. It should be called |
164 | ideally on transaction completion point |
165 +-----------------------------------------------------------*/
166 PROCEDURE set_original_rem_amt
167 (p_customer_trx IN ra_customer_trx%ROWTYPE,
168 p_adj_id IN NUMBER DEFAULT NULL,
169 p_app_id IN NUMBER DEFAULT NULL,
170 --{HYUNLB
171 p_from_llca IN VARCHAR2 DEFAULT 'N');
172 --}
173 /*-----------------------------------------------------------+
174 | PROCEDURE copy_trx_lines |
175 +-----------------------------------------------------------+
176 | Note: procedure copy the invoice line from ra_customer |
177 | trx_lines into ra_customer_trx_lines_gt. |
178 +-----------------------------------------------------------*/
179 PROCEDURE copy_trx_lines
180 (p_customer_trx_id IN NUMBER,
181 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
182 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL);
183
184
185 /*-----------------------------------------------------------+
186 | PROCEDURE final_update_inv_ctl_rem_orig |
187 +-----------------------------------------------------------+
188 | Note: procedure updates the balance on ctl line once user |
189 | commit the activities on the transactions. |
190 +-----------------------------------------------------------*/
191 PROCEDURE final_update_inv_ctl_rem_orig
192 (p_customer_trx IN ra_customer_trx%ROWTYPE);
193
194
195 /*-----------------------------------------------------------+
196 | PROCEDURE create_final_split |
197 +-----------------------------------------------------------+
198 | Note: procedure creates the detail distributions in |
199 | ar_line_application_detail at commit point |
200 +-----------------------------------------------------------*/
201 PROCEDURE create_final_split
202 (p_customer_trx IN ra_customer_trx%ROWTYPE,
203 p_app_rec IN ar_receivable_applications%ROWTYPE,
204 p_adj_rec IN ar_adjustments%ROWTYPE,
205 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
206
207 /*-------------------------------------------------------------------------+
208 | update_dist |
209 +-------------------------------------------------------------------------+
210 | Read ra_ar_gt for proration info |
211 | Does the proration plsql_proration |
212 | update ra_ar_gt with the result |
213 | for distributions of all lines of a invoice |
214 +-------------------------------------------------------------------------+
215 | parameter: |
216 | p_customer_trx_id transaction id |
217 | p_gt_id global id |
218 +-------------------------------------------------------------------------*/
219 PROCEDURE update_dist
220 (p_gt_id IN VARCHAR2,
221 p_customer_trx_id IN NUMBER,
222 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
223
224 /*-------------------------------------------------------------------------+
225 | Trx_level_direct_cash_apply |
226 +-------------------------------------------------------------------------+
227 | 1) get_invoice_line_info |
228 | 2) prepare_group_for_proration |
229 | 3) update_group_line |
230 | 4) prepare_trx_line_proration |
231 | 5) update_line |
232 | 6) update_ctl_rem_orig |
233 | 7) store_gt_id |
234 +-------------------------------------------------------------------------+
235 | parameter: |
236 | p_customer_trx_id transaction id |
237 | p_app_rec ar receivable application record |
238 | p_ae_sys_rec ar system parameter |
239 +-------------------------------------------------------------------------*/
240 PROCEDURE Trx_level_direct_cash_apply
241 (p_customer_trx IN ra_customer_trx%ROWTYPE,
242 p_app_rec IN ar_receivable_applications%ROWTYPE,
243 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
244 p_gt_id IN NUMBER DEFAULT NULL,
245 p_inv_cm IN VARCHAR2 DEFAULT 'I');
246
247
248 /*-----------------------------------------------------------+
249 | PROCEDURE possible_adjust |
250 | check if a particular adjustment is possible. |
251 +-----------------------------------------------------------+
252 | Parameters: |
253 | ----------- |
254 | p_adj_rec the adjustment record. |
255 | p_ae_rule_rec containing accounting acitivity |
256 | and bucket info. |
257 | p_amt_rem containing amount kept at invoice |
258 | lines. |
259 | x_return_status value according to the possibility |
260 | of the adjustment |
261 | FND_API.G_RET_STS_SUCCESS if possible |
262 | FND_API.G_RET_STS_ERROR if imposssible |
263 | x_line_adj codification for line adjustment |
264 | x_tax_adj codification for tax adjustment |
265 | x_frt_adj codification for freight adjustment |
266 | x_chrg_adj codification for charge adjustment |
270 PROCEDURE possible_adjust(p_adj_rec IN ar_adjustments%rowtype,
267 +-----------------------------------------------------------+
268 | Created 26-OCT-03 Herve Yu |
269 +-----------------------------------------------------------*/
271 p_ae_rule_rec IN ae_rule_rec_type,
272 p_customer_trx_id IN NUMBER,
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_line_adj OUT NOCOPY VARCHAR2,
275 x_tax_adj OUT NOCOPY VARCHAR2,
276 x_frt_adj OUT NOCOPY VARCHAR2,
277 x_chrg_adj OUT NOCOPY VARCHAR2,
278 p_app_rec IN ar_receivable_applications%rowtype);
279
280
281 /*---------------------------------------------------------------------+
282 | FUNCTION Accting_Proration_Fct |
283 +---------------------------------------------------------------------+
284 | This function |
285 | does the proration and return the value in a row by row manner |
286 | usefull for function in SQL statement updation |
287 | |
288 | Parameter |
289 | p_temp_amt distribution amount template for proration |
290 | for example ae_pro_amt(i) |
291 | p_base_proration base for proration |
292 | for example sum ae_pro_amt(i) |
293 | p_alloc_amount The amount for which distribution need to be |
294 | computed. For example p_app_rec.from_amt_applied
295 | p_base_currency Base currency code |
296 | p_trx_currency Trx currency code |
297 | p_rec_currency Rec currency code |
298 | p_flag indication of which from distribution to compute
299 | p_flag = 'FROM_AMT' |
300 | p_flag = 'FROM_ACCTD_AMT' |
301 | p_flag = 'FROM_CHRG_AMT' |
302 | p_flag = 'FROM_CHRG_ACCTD_AMT' |
303 | History |
304 | 05-NOV-2004 H. Yu Created |
305 +---------------------------------------------------------------------*/
306 FUNCTION Accting_Proration_Fct
307 (p_temp_amt IN NUMBER,
308 p_base_proration IN NUMBER,
309 p_alloc_amount IN NUMBER,
310 p_base_currency IN VARCHAR2,
311 p_trx_currency IN VARCHAR2,
312 p_rec_currency IN VARCHAR2,
313 p_flag IN VARCHAR2)
314 RETURN NUMBER;
315
316 PROCEDURE exec_adj_api_if_required
317 (p_adj_rec IN ar_adjustments%ROWTYPE,
318 p_app_rec IN ar_receivable_applications%ROWTYPE,
319 p_ae_rule_rec IN ae_rule_rec_type,
320 p_cust_inv_rec IN ra_customer_trx%ROWTYPE);
321
322 PROCEDURE exec_revrec_if_required
323 ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
324 p_app_rec IN ar_receivable_applications%ROWTYPE,
325 p_adj_rec IN ar_adjustments%ROWTYPE);
326
327 PROCEDURE update_for_mrc_dist
328 (p_gt_id IN VARCHAR2,
329 p_customer_trx_id IN NUMBER,
330 p_app_rec IN ar_receivable_applications%ROWTYPE,
331 p_adj_rec IN ar_adjustments%ROWTYPE,
332 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
333
334
335 PROCEDURE prepare_for_ra
336 ( p_gt_id IN NUMBER,
337 p_app_rec IN ar_receivable_applications%ROWTYPE,
338 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
339 p_inv_cm IN VARCHAR2 DEFAULT 'I',
340 p_cash_mfar IN VARCHAR2 DEFAULT 'CASH');
341
342 --legacy lazy upgrade
343 PROCEDURE check_lazy_apply_req
344 (p_trx_id IN NUMBER,
345 x_out_res OUT NOCOPY VARCHAR2);
346
347 PROCEDURE check_mf_trx
348 ( p_cust_trx_type_id IN NUMBER,
349 x_out_res OUT NOCOPY VARCHAR2);
350
351
352 PROCEDURE online_lazy_apply
353 (p_customer_trx IN ra_customer_trx%ROWTYPE,
354 p_app_rec IN ar_receivable_applications%ROWTYPE,
355 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
356 p_gt_id IN NUMBER,
357 p_inv_cm IN VARCHAR2 DEFAULT 'I');
358
359 FUNCTION next_val(p_num IN NUMBER)
360 RETURN NUMBER;
361
362
363 /*-----------------------------------------------------------------------------+
364 | Procedure get_latest_amount_remaining |
365 +-----------------------------------------------------------------------------+
366 | Parameter : |
367 | p_customer_trx_id The invoice ID |
368 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
369 | p_group_id Group_id req when Application level is GROUP |
370 | p_ctl_id customer_trx_line_id required when the application level |
371 | is LINE |
375 | x_freight_rem The remaining freight amount for the level TRANSACTION only|
372 | OUT |
373 | x_line_rem The remaining revenue amount for the level |
374 | x_tax_rem The remaining tax amount for the level |
376 | x_charges_rem The remaining charges amount for the level TRANSACTION only|
377 +-----------------------------------------------------------------------------+
378 | Action : |
379 | Read the remaining amount on ra_customer_trx_lines_gt |
380 +-----------------------------------------------------------------------------*/
381 PROCEDURE get_latest_amount_remaining
382 (p_customer_trx_id IN NUMBER,
383 p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
384 p_source_data_key1 IN VARCHAR2 DEFAULT NULL,
385 p_source_data_key2 IN VARCHAR2 DEFAULT NULL,
386 p_source_data_key3 IN VARCHAR2 DEFAULT NULL,
387 p_source_data_key4 IN VARCHAR2 DEFAULT NULL,
388 p_source_data_key5 IN VARCHAR2 DEFAULT NULL,
389 p_ctl_id IN NUMBER DEFAULT NULL,
390 x_line_rem OUT NOCOPY NUMBER,
391 x_tax_rem OUT NOCOPY NUMBER,
392 x_freight_rem OUT NOCOPY NUMBER,
393 x_charges_rem OUT NOCOPY NUMBER,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_data OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER);
397
398 --BUG#44144391
399 PROCEDURE get_gt_sequence
400 (x_gt_id OUT NOCOPY NUMBER,
401 x_return_status IN OUT NOCOPY VARCHAR2,
402 x_msg_count IN OUT NOCOPY NUMBER,
403 x_msg_data IN OUT NOCOPY VARCHAR2);
404
405 PROCEDURE set_interface_flag
406 ( p_source_table IN VARCHAR2 DEFAULT NULL,
407 p_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
408 p_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
409 p_freight_flag IN VARCHAR2 DEFAULT 'NORMAL',
410 p_charges_flag IN VARCHAR2 DEFAULT 'NORMAL',
411 p_ed_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
412 p_ed_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
413 p_uned_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
414 p_uned_tax_flag IN VARCHAR2 DEFAULT 'NORMAL');
415
416 PROCEDURE adjustment_with_interface
417 (p_customer_trx IN ra_customer_trx%ROWTYPE,
418 p_adj_rec IN ar_adjustments%ROWTYPE,
419 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
420 p_gt_id IN NUMBER,
421 p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
422 p_tax_flag IN VARCHAR2 DEFAULT 'INTERFACE',
423 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
424 x_return_status IN OUT NOCOPY VARCHAR2,
425 x_msg_count IN OUT NOCOPY NUMBER,
426 x_msg_data IN OUT NOCOPY VARCHAR2,
427 p_llca_from_call IN VARCHAR2 DEFAULT NULL,
428 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE DEFAULT NULL);
429
430 PROCEDURE application_with_interface
431 (p_customer_trx IN ra_customer_trx%ROWTYPE,
432 p_app_rec IN ar_receivable_applications%ROWTYPE,
433 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
434 p_gt_id IN NUMBER,
435 p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
436 p_tax_flag IN VARCHAR2 DEFAULT 'INTERFACE',
437 p_ed_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
438 p_ed_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
439 p_uned_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
440 p_uned_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
441 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
442 x_return_status IN OUT NOCOPY VARCHAR2,
443 x_msg_count IN OUT NOCOPY NUMBER,
444 x_msg_data IN OUT NOCOPY VARCHAR2);
445 --}
446
447 --{For Cross Currency this procedure needs to be called externally
448 PROCEDURE update_from_gt
449 (p_from_amt IN NUMBER,
450 p_from_acctd_amt IN NUMBER,
451 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
452 p_app_rec IN ar_receivable_applications%ROWTYPE,
453 p_gt_id IN VARCHAR2 DEFAULT NULL,
454 p_inv_currency IN VARCHAr2 DEFAULT NULL);
455 --}
456
457
458 --{ Diagnostic
459 g_diag_flag VARCHAR2(30) := 'NOT_SET';
460 PROCEDURE get_diag_flag;
461 PROCEDURE diag_data(p_gt_id IN NUMBER DEFAULT NULL);
462
463 PROCEDURE Reconciliation
464 (p_app_rec IN ar_receivable_applications%ROWTYPE,
465 p_adj_rec IN ar_adjustments%ROWTYPE,
466 p_activity_type IN VARCHAR2,
467 p_gt_id IN VARCHAR2 DEFAULT NULL);
468 --}
469
470 --{BUG#5098099
471 PROCEDURE check_legacy_status
472 (p_trx_id IN NUMBER,
473 p_adj_id IN NUMBER DEFAULT NULL,
474 p_app_id IN NUMBER DEFAULT NULL,
475 x_11i_adj OUT NOCOPY VARCHAR2,
476 x_mfar_adj OUT NOCOPY VARCHAR2,
477 x_11i_app OUT NOCOPY VARCHAR2,
478 x_mfar_app OUT NOCOPY VARCHAR2);
479 --}
480
481 --{BUG#5412633
482 PROCEDURE exec_revrec_if_required
483 (p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE
484 ,p_mode IN VARCHAR2 DEFAULT 'TRANSACTION'
485 ,p_customer_trx_id IN NUMBER DEFAULT NULL
486 ,p_request_id IN NUMBER DEFAULT NULL
487 ,x_sum_dist OUT NOCOPY NUMBER
488 ,x_return_status OUT NOCOPY VARCHAR2
489 ,x_msg_count OUT NOCOPY NUMBER
490 ,x_msg_data OUT NOCOPY VARCHAR2);
491
492 PROCEDURE set_original_rem_amt_r12
493 (p_customer_trx IN ra_customer_trx%ROWTYPE,
494 x_return_status IN OUT NOCOPY VARCHAR2,
495 x_msg_count IN OUT NOCOPY NUMBER,
496 x_msg_data IN OUT NOCOPY VARCHAR2,
497 p_from_llca IN VARCHAR2 DEFAULT 'N');
498
499 PROCEDURE set_original_rem_amt_r12(p_request_id IN NUMBER);
500
501 END;