1 PACKAGE ar_upgrade_cash_accrual AS
2 /* $Header: ARUPGLZS.pls 120.4.12010000.1 2008/07/24 16:58:52 appldev ship $ */
3
4 /*------------------------------------------------------+
5 | Procedure for insertion in ra_ar_gt all transaction |
6 | distributions usable in OLTP and BATCH |
7 +------------------------------------------------------*/
8 PROCEDURE get_direct_inv_dist
9 (p_mode IN VARCHAR2,
10 p_trx_id IN NUMBER DEFAULT NULL,
11 p_gt_id IN NUMBER DEFAULT NULL);
12
13 --MFAR invoice distributions
14 PROCEDURE get_direct_mf_inv_dist
15 (p_mode IN VARCHAR2 DEFAULT 'BATCH',
16 p_gt_id IN NUMBER DEFAULT NULL);
17
18 /*------------------------------------------------------+
19 | Procedure for insertion in ra_ar_gt all adjustment |
20 | distributions usable in OLTP and BATCH |
21 +------------------------------------------------------*/
22 PROCEDURE get_direct_adj_dist
23 (p_mode IN VARCHAR2,
24 p_trx_id IN NUMBER DEFAULT NULL,
25 p_gt_id IN NUMBER DEFAULT NULL);
26
27 PROCEDURE get_direct_inv_adj_dist
28 (p_mode IN VARCHAR2,
29 p_trx_id IN NUMBER DEFAULT NULL,
30 p_gt_id IN NUMBER DEFAULT NULL);
31
32 --MFAR adj distributions procedure only in batch mode
33 PROCEDURE get_direct_mf_adj_dist
34 (p_mode IN VARCHAR2,
35 p_gt_id IN NUMBER DEFAULT NULL);
36
37 /*------------------------------------------------------+
38 | Procedure for update the base proration only batch |
39 +------------------------------------------------------*/
40 PROCEDURE update_base
41 (p_gt_id IN NUMBER DEFAULT NULL);
42
43 /*------------------------------------------------------+
44 | Procedure for create distribution only batch |
45 +------------------------------------------------------*/
46 PROCEDURE create_distributions;
47
48 FUNCTION fct_acct_amt
49 (p_amt IN NUMBER,
50 p_base_amt IN NUMBER,
51 p_base_acctd_amt IN NUMBER,
52 p_currency_code IN VARCHAR2,
53 p_base_currency IN VARCHAR2,
54 p_ind_id IN NUMBER)
55 RETURN NUMBER;
56
57
58 ---------------------------------------
59 -- PROCEDURE COMPARE_RA_REM_AMT
60 ---------------------------------------
61 -- Arguments Input
62 -- p_app_rec IN ar_receivable_applications%ROWTYPE -- the application record initial
63 -- p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION' -- level of application
64 -- p_group_id IN VARCHAR2 DEFAULT NULL -- if level = GROUP then which group
65 -- p_ctl_id IN NUMBER DEFAULT NULL -- if level = LINE then which line
66 -- p_currency IN VARCHAR2 -- transactional currency
67 --------------
68 -- Outputs
69 -- x_app_rec OUT NOCOPY ar_receivable_applications%ROWTYPE -- after leasing the result app_rec
70 -- x_return_status IN OUT NOCOPY VARCHAR2
71 -- x_msg_data IN OUT NOCOPY VARCHAR2
72 -- x_msg_count IN OUT NOCOPY NUMBER
73 --------------
74 -- Objective:
75 -- When does a application on a 11i MFAR transaction, the amount allocated per bucket can in disconcordance
76 -- with the remaining amounts stamped in AR on the transaction because
77 -- AR tied the charges and freight adjusted to revenue line
78 -- but PSA tied the freight to freight line
79 -- prorate the charges on all lines
80 -- Therefore remaining amount calculated by AR can not the same from PSA
81 -- For legacy transaction originate by PSA, in the upgrade AR should ensure:
82 -- * the overall amount remaining all buckets and application all buckets are not incompatible
83 -- that is no overapplication
84 -- * the ED UNED bucket are not mixed with the application buckets
85 -- * but the disconcordance between the rem and the application amount per bucket will be
86 -- handled by the amount applied bucket
87 ----------------------------------------
88 PROCEDURE COMPARE_RA_REM_AMT
89 ( p_app_rec IN ar_receivable_applications%ROWTYPE,
90 x_app_rec OUT NOCOPY ar_receivable_applications%ROWTYPE,
91 p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
92 p_source_data_key1 IN VARCHAR2 DEFAULT NULL,
93 p_source_data_key2 IN VARCHAR2 DEFAULT NULL,
94 p_source_data_key3 IN VARCHAR2 DEFAULT NULL,
95 p_source_data_key4 IN VARCHAR2 DEFAULT NULL,
96 p_source_data_key5 IN VARCHAR2 DEFAULT NULL,
97 p_ctl_id IN NUMBER DEFAULT NULL,
98 p_currency IN VARCHAR2,
99 x_return_status IN OUT NOCOPY VARCHAR2,
100 x_msg_data IN OUT NOCOPY VARCHAR2,
101 x_msg_count IN OUT NOCOPY NUMBER);
102
103 ---------------------------------------
104 -- PROCEDURE portion_to_move
105 ---------------------------------------
106 -- Calculate the portion to move from the total to each bucket
107 -- based on the ratio argument
108 -- for example:
109 -- total to move = 15
110 -- freight ratio = 10
111 -- tax ratio = 20
112 -- line ratio = 40
113 -- chrg ratio = 80
114 -- ---
115 -- freight_portion to move = 1
116 -- tax_portion to move = 2
117 -- line_portion to move = 4
118 -- chrg_portion to move = 8
119 ----------------------------------------
120 PROCEDURE portion_to_move
121 (p_total_to_move IN NUMBER,
122 p_freight_ratio IN NUMBER DEFAULT 0,
123 p_tax_ratio IN NUMBER DEFAULT 0,
124 p_line_ratio IN NUMBER DEFAULT 0,
125 p_chrg_ratio IN NUMBER DEFAULT 0,
126 p_currency IN VARCHAR2,
127 x_freight_portion OUT NOCOPY NUMBER,
128 x_tax_portion OUT NOCOPY NUMBER,
129 x_line_portion OUT NOCOPY NUMBER,
130 x_chrg_portion OUT NOCOPY NUMBER);
131
132 ---------------------------------------
133 -- PROCEDURE move_bucket
134 ---------------------------------------
135 -- Determine the amount to move and
136 -- Does the movement of the bucket for bucket originate the movement
137 -- For example:
138 -- p_chrg_entire meaning Chrg (ED UNED APP) of an application
139 -- is greater then the Chrg remaining on the invoice to apply
140 -- we need to reconcile the surplus amount from the chrg to move
141 -- to other buckets
142 --------------
143 -- Consider we have a surplus of 15 usd of charge to move, so
144 -- if which bucket = 'CHRG' then 15 usd will be moved to line, tax, freight buckets
145 -- Consider we have a surplus of 10 usd of freight to move, so
146 -- if which bucket = 'FREIGHT' then 10 usd will be moved to line, tax buckets
147 -- Consider we have a surplus of 5 usd of tax to move, so
148 -- if which bucket = 'TAX' then 5 usd will be moved to line
149 -- No movement is allowed on LINE bucket the surplus stay in line buckets
150 ---------------
151 -- The new entire amount by bucket are returned in x_XXX_entire output argument
152 ----------------------------------------
153 PROCEDURE move_bucket
154 (p_line_entire IN NUMBER,
155 p_freight_entire IN NUMBER,
156 p_tax_entire IN NUMBER,
157 p_chrg_entire IN NUMBER,
158 --
159 p_line_rem IN NUMBER,
160 p_freight_rem IN NUMBER,
161 p_tax_rem IN NUMBER,
162 p_chrg_rem IN NUMBER,
163 --
164 p_which_bucket IN VARCHAR2,
165 p_currency IN VARCHAR2,
166 --
167 x_line_entire OUT NOCOPY NUMBER,
168 x_freight_entire OUT NOCOPY NUMBER,
169 x_tax_entire OUT NOCOPY NUMBER,
170 x_chrg_entire OUT NOCOPY NUMBER);
171
172 ---------------------------------------
173 -- PROCEDURE lease_app_bucket_amts
174 ---------------------------------------
175 -- This a wrapper which will lease the entire application amt buckets
176 -- based on the remaining of the transaction
177 --------------
178 -- For example :
179 -- The application has
180 -- ED + UNED + APP for line - x_line_entire => 100
181 -- ED + UNED + APP for freight - x_freight_entire=> 30
182 -- ED + UNED + APP for tax - x_tax_entire => 16
183 -- ED + UNED + APP for chrg - x_chrg_entire => 6
184 --------------
185 -- The transaction has remaining
186 -- on line p_line_rem => 200
187 -- on freight p_freight_rem => 30
188 -- on tax p_tax_rem => 15
189 -- on charges p_chrg_rem => 3
190 ----------------
191 -- sum all rem > sum all entire buckets ==> no over applications - OK
192 -- The result will be
193 -- x_line_entire => 104
194 -- x_freight_entire => 30
195 -- x_tax_entire => 15
196 -- x_chrg_entire => 3
197 -- Note in this example the surplus from tax and charges are absorbed by line buckets
198 ----------------------------------------
199 PROCEDURE lease_app_bucket_amts
200 (p_line_rem IN NUMBER,
201 p_tax_rem IN NUMBER,
202 p_freight_rem IN NUMBER,
203 p_chrg_rem IN NUMBER,
204 --
205 p_currency IN VARCHAR2,
206 --
207 x_line_entire IN OUT NOCOPY NUMBER,
208 x_tax_entire IN OUT NOCOPY NUMBER,
209 x_freight_entire IN OUT NOCOPY NUMBER,
210 x_chrg_entire IN OUT NOCOPY NUMBER);
211
212
213 PROCEDURE stamping_11i_mfar_app_post;
214
215 PROCEDURE stamping_11i_cash_app_post;
216
217 END;