[Home] [Help]
PACKAGE BODY: APPS.ARP_LLCA_ADJUST_PKG
Source
1 PACKAGE BODY ARP_LLCA_ADJUST_PKG AS
2 /* $Header: ARLLADJB.pls 120.2.12010000.4 2009/03/17 06:53:42 nproddut ship $ */
3
4
5 /*=======================================================================+
6 | Package Globals
7 +=======================================================================*/
8
9 PG_DEBUG varchar2(1);
10
11 /* init accounting structure */
12 PROCEDURE init_ae_struct(
13 p_ae_sys_rec IN OUT NOCOPY arp_acct_main.ae_sys_rec_type) IS
14 BEGIN
15 SELECT sob.set_of_books_id,
16 sob.chart_of_accounts_id,
17 sob.currency_code,
18 c.precision,
19 c.minimum_accountable_unit,
20 sysp.code_combination_id_gain,
21 sysp.code_combination_id_loss,
22 sysp.code_combination_id_round
23 INTO p_ae_sys_rec.set_of_books_id,
24 p_ae_sys_rec.coa_id,
25 p_ae_sys_rec.base_currency,
26 p_ae_sys_rec.base_precision,
27 p_ae_sys_rec.base_min_acc_unit,
28 p_ae_sys_rec.gain_cc_id,
29 p_ae_sys_rec.loss_cc_id,
30 p_ae_sys_rec.round_cc_id
31 FROM ar_system_parameters sysp,
32 gl_sets_of_books sob,
33 fnd_currencies c
34 WHERE sob.set_of_books_id = sysp.set_of_books_id
35 AND sob.currency_code = c.currency_code;
36 END init_ae_struct;
37
38 /*=============================================================================
39 | PROCEDURE LLCA_Adjustments
40 |
41 | DESCRIPTION
42 | This procedure will populate the ar_activity_details for a line level
43 | adjustment and then populate the required GT tables for accting calls
44 | if required.
45 |
46 | PARAMETERS:
47 | IN :
48 | OUT :
49 |
50 | MODIFICATION HISTORY
51 | DATE Author Description of Changes
52 | 23-AUG-2005 Debbie Sue Jancis Created
53 |
54 *===========================================================================*/
55 PROCEDURE LLCA_Adjustments(
56 p_customer_trx_line_id IN NUMBER,
57 p_customer_trx_id IN NUMBER,
58 p_line_adjusted IN NUMBER,
59 p_tax_adjusted IN NUMBER,
60 p_adj_id IN NUMBER,
61 p_inv_currency_code IN VARCHAR2,
62 p_gt_id IN OUT NOCOPY NUMBER ) IS
63
64 l_apply_to NUMBER;
65 l_line_rem NUMBER;
66 l_tax_rem NUMBER;
67 l_rowid NUMBER;
68 l_gt_id NUMBER;
69 l_return_status_service VARCHAR2(4000);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(4000);
72 l_msg VARCHAR2(4000);
73
74 l_adj_rec ar_adjustments%ROWTYPE;
75 l_trx_rec ra_customer_trx%ROWTYPE;
76 l_ae_sys_rec arp_acct_main.ae_sys_rec_type;
77 l_line_id NUMBER;
78
79 -- Added for Line Level Adjustment
80 l_from_llca_call VARCHAR2(1);
81
82 BEGIN
83 arp_util.debug('ARP_LLCA_ADJUST_PKG.LLCA_Adjustments()+');
84 arp_util.debug('line adjusted : ' || to_char(p_line_adjusted));
85 arp_util.debug('tax adjusted : ' || to_char(p_tax_adjusted));
86
87 -- At the point this is called, it is assumed that the
88 -- adjustment record has been inserted and the payment
89 -- schedule of the invoice has been updated.
90 -- we now need to create the record in the ar_activity_details
91 -- table.
92
93 -- Commented for Line level adjustment, inserting into AD after insert of adjustment
94 /*
95 Select sum(DECODE( lines.line_type,
96 'TAX',0,
97 'FREIGHT', 0,
98 1) * lines.amount_due_remaining) l_line_rem,
99 sum(DECODE (lines.line_type,
100 'TAX', 1, 0) * lines.amount_due_remaining) l_tax_rem,
101 MAX(DECODE(lines.line_type, 'LINE',
102 lines.line_number, 0))
103 INTO
104 l_line_rem,
105 l_tax_rem,
106 l_apply_to
107 FROM ra_customer_trx ct,
108 ra_customer_trx_lines lines
109 WHERE (lines.customer_Trx_line_id = p_customer_trx_line_id or
110 lines.link_to_cust_trx_line_id = p_customer_trx_line_id)
111 AND ct.customer_Trx_id = lines.customer_trx_id
112 AND ct.customer_trx_id = p_customer_trx_id;
113
114
115 SELECT ar_activity_details_s.nextval
116 INTO l_line_id
117 FROM dual;
118
119 INSERT INTO AR_ACTIVITY_DETAILS (
120 LINE_ID,
121 APPLY_TO,
122 customer_trx_line_id,
123 CASH_RECEIPT_ID,
124 GROUP_ID,
125 AMOUNT,
126 TAX,
127 CREATED_BY,
128 CREATION_DATE,
129 LAST_UPDATE_LOGIN,
130 LAST_UPDATE_DATE,
131 LAST_UPDATED_BY,
132 OBJECT_VERSION_NUMBER,
133 CREATED_BY_MODULE,
134 SOURCE_ID,
135 SOURCE_TABLE
136 )
137
138 VALUES (
139 l_line_id, -- line_id
140 1, -- APPLY_TO
141 p_customer_trx_line_id, -- customer_Trx_line_id
142 NULL, -- cash_Receipt_id
143 NULL, -- Group_ID (ll grp adj not implem)
144 p_line_adjusted, -- Amount
145 p_tax_adjusted, -- TAX
146 NVL(FND_GLOBAL.user_id,-1), -- Created_by
147 SYSDATE, -- Creation_date
148 decode(FND_GLOBAL.conc_login_id,
149 null,FND_GLOBAL.login_id,
150 -1, FND_GLOBAL.login_id,
151 FND_GLOBAL.conc_login_id), -- Last_update_login
152 SYSDATE, -- Last_update_date
153 NVL(FND_GLOBAL.user_id,-1), -- last_updated_by
154 0, -- object_version_number
155 'ARXTWADJ', -- created_by_module
156 p_adj_id, -- source_id
157 'ADJ' -- source_table
158 );
159
160 */
161 /* if p_Gt_id is 0 then we have to populate the gt table */
162
163 IF ( p_gt_id = 0 ) THEN
164 arp_util.debug('LLCA_Adjustments: populating the GT table ');
165
166 /* Get sequence for line level distributions API */
167 arp_det_dist_pkg.get_gt_sequence (l_gt_id,
168 l_return_status_service,
169 l_msg_count,
170 l_msg_data);
171
172 p_gt_id := l_gt_id;
173
174 arp_util.debug('l_gt_id = ' || l_gt_id);
175
176 /* Insert lines into GT table for processing- if we have a line amt */
177 IF (p_line_adjusted <> 0) THEN
178 INSERT INTO AR_LINE_DIST_INTERFACE_GT
179 ( GT_ID,
180 SOURCE_ID,
181 SOURCE_TABLE,
182 CUSTOMER_TRX_ID,
183 CUSTOMER_TRX_LINE_ID,
184 LINE_TYPE,
185 LINE_AMOUNT,
186 ED_LINE_AMOUNT,
187 UNED_LINE_AMOUNT)
188 VALUES (
189 l_gt_id, -- gt_id
190 p_adj_id, -- source_id
191 'ADJ' , -- source_table
192 p_customer_trx_id, -- customer_trx_id
193 p_customer_trx_line_id, -- customer_Trx_line_id
194 'LINE', -- line_type
195 p_line_adjusted, -- line_amount
196 NULL, -- ed_line_amount
197 NULL -- uned_line_amount
198 );
199 END IF;
200
201 -- call to prorate the amount over the tax lines.
202 -- then insert the records into the GT table.
203 IF (p_tax_adjusted <> 0 ) then
204 arp_llca_adjust_pkg.prorate_tax_amount(
205 p_customer_trx_line_id => p_customer_trx_line_id,
206 p_customer_trx_id => p_customer_trx_id,
207 p_tax_adjusted => p_tax_adjusted,
208 p_adjustment_id => p_adj_id,
209 p_gt_id => p_gt_id,
210 p_inv_currency_code => p_inv_currency_code);
211 END IF;
212
213 SELECT *
214 INTO l_adj_rec
215 FROM ar_adjustments
216 WHERE adjustment_id = p_adj_id;
217
218 SELECT *
219 INTO l_trx_rec
220 FROM ra_customer_trx
221 WHERE customer_trx_id = p_customer_trx_id;
222
223 -- Now initialize the acct engine and
224 -- call the distribution routine (adjustments)
225 init_ae_struct(l_ae_sys_rec);
226
227 -- Added parameter for LIne Level Adjustment
228 l_from_llca_call := 'Y';
229
230 arp_det_dist_pkg.adjustment_with_interface(
231 p_customer_trx => l_trx_rec,
232 p_adj_rec => l_adj_rec,
233 p_ae_sys_rec => l_ae_sys_rec,
234 p_gt_id => l_gt_id,
235 p_line_flag => 'INTERFACE',
236 p_tax_flag => 'INTERFACE',
237 x_return_status=> l_return_status_service,
238 x_msg_count => l_msg_count,
239 x_msg_data => l_msg_data,
240 p_llca_from_call => l_from_llca_call,
241 p_customer_trx_line_id => p_customer_trx_line_id);
242
243
244 IF ( l_return_status_service <> FND_API.G_RET_STS_SUCCESS) THEN
245 /* Retrieve and log errors */
246 IF (l_msg_count = 1) THEN
247 arp_standard.debug(l_msg_data);
248 p_gt_id := 0;
249 ELSIF (l_msg_count > 1) THEN
250 LOOP
251 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
252 FND_API.G_FALSE);
253 IF (l_msg IS NULL) THEN
254 EXIT;
255 ELSE
256 arp_standard.debug(l_msg);
257 END IF;
258 END LOOP;
259 p_gt_id := 0;
260 END IF;
261 END IF;
262
263 END IF; -- if gt_id is 0
264
265 arp_util.debug('ARP_LLCA_ADJUST_PKG.LLCA_Adjustments()-');
266 END LLCA_Adjustments;
267
268 /*=============================================================================
269 | PROCEDURE Prorate_tax_Amount
270 |
271 | DESCRIPTION
272 | This procedure will prorate the tax adjusted amount (non-recoverable)
273 | over all tax lines which belong to a LINE
274 |
275 | PARAMETERS:
276 | IN :
277 | OUT :
278 |
279 | MODIFICATION HISTORY
280 | DATE Author Description of Changes
281 | 24-AUG-2005 Debbie Sue Jancis Created
282 |
283 *===========================================================================*/
284 PROCEDURE Prorate_tax_amount(
285 p_customer_trx_line_id IN NUMBER,
286 p_customer_trx_id IN NUMBER,
287 p_tax_adjusted IN NUMBER,
288 p_adjustment_id IN NUMBER,
289 p_gt_id IN NUMBER,
290 p_inv_currency_code IN VARCHAR2
291 ) IS
292
293 l_sum NUMBER;
294 l_total_tax_lines NUMBER;
295 l_rounding_rule VARCHAR2(30);
296 l_precision NUMBER;
297 l_extended_precision NUMBER;
298 l_min_acct_unit NUMBER;
299 l_tax_proration NUMBER;
300 l_total_proration NUMBER;
301 l_row NUMBER;
302
303 CURSOR tax_lines (p_customer_trx_line_id NUMBER) IS
304 SELECT lines.extended_amount tax_amt,
305 customer_trx_line_id
306 FROM ra_customer_trx_lines lines
307 WHERE lines.line_type = 'TAX'
308 AND lines.link_to_cust_trx_line_id = p_customer_trx_line_id;
309
310
311 BEGIN
312 arp_util.debug('ARP_LLCA_ADJUST_PKG.Prorate_tax_amount()+');
313
314 /* get the divisor for the proration equation */
315 SELECT
316 sum(tl.extended_amount),
317 Count(tl.customer_Trx_line_id)
318 INTO l_sum,
319 l_total_tax_lines
320 FROM ra_customer_trx_lines tl
321 WHERE tl.customer_trx_id = p_customer_trx_id
322 AND tl.link_to_cust_trx_line_id = p_customer_trx_line_id;
323
324 IF (PG_DEBUG in ('Y','C')) THEN
325 arp_standard.debug('sum (divisor) for proration calc = ' || l_sum);
326 arp_standard.debug('Number of tax lines = ' || l_total_tax_lines);
327 END IF;
328
329 -- get rounding rule
330 -- Bug 5514473 : Handled no data found so that tax_rounding_rule will be defaulted if there is no data in zx_product_options for the org
331 -- Bug 5514473 : When application tax options are not defined through tax manager for newly created orgs there will no data in zx_product_options
332 BEGIN
333 SELECT tax_rounding_rule
334 INTO l_rounding_rule
335 FROM zx_product_options
336 WHERE application_id = 222;
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 l_rounding_rule := NULL;
340 arp_util.debug('tax_rounding_rule will be defaulted because there is no row in zx_product_options');
341 arp_util.debug('Ideal Default Tax Rounding Rule will be : NEAREST');
342 END;
343
344 -- get currency information
345 fnd_currency.Get_info(p_inv_currency_code,
346 l_precision,
347 l_extended_precision,
348 l_min_acct_unit);
349
350 l_tax_proration := 0;
351 l_total_proration := 0;
352
353 FOR c_tl in tax_lines(p_customer_trx_line_id)
354 LOOP
355 l_row := l_row + 1;
356
357 /* calculate prorated adj for tax lines */
358 l_tax_proration := arp_etax_util.tax_curr_round(
359 (p_tax_adjusted * (c_tl.tax_amt / l_sum)),
360 p_inv_currency_code,
361 l_precision,
362 l_min_acct_unit,
363 l_rounding_rule);
364
365
366 l_total_proration := l_total_proration + l_tax_proration;
367
368 /* if l_row is the number of tax lines.. then we have to check
369 the rounding before inserting into the gt table. */
370 IF ( l_row = l_total_tax_lines) THEN
371 l_tax_proration := p_tax_adjusted - l_total_proration;
372 END IF;
373
374 /* now we have to populate the GT table for tax lines */
375 INSERT INTO AR_LINE_DIST_INTERFACE_GT
376 ( GT_ID,
377 SOURCE_ID,
378 SOURCE_TABLE,
379 CUSTOMER_TRX_ID,
380 CUSTOMER_TRX_LINE_ID,
381 LINE_TYPE,
382 TAX_AMOUNT,
383 ED_TAX_AMOUNT,
384 UNED_TAX_AMOUNT)
385 VALUES (
386 p_gt_id, -- gt_id
387 p_adjustment_id, -- source_id
388 'ADJ' , -- source_table
389 p_customer_trx_id, -- customer_trx_id
390 c_tl.customer_trx_line_id, -- customer_Trx_line_id
391 'TAX', -- line_type
392 l_tax_proration, -- tax_amount
393 NULL, -- ed_tax_amount
394 NULL -- uned_tax_amount
395 );
396
397 END LOOP;
398
399 arp_util.debug('ARP_LLCA_ADJUST_PKG.Prorate_tax_amount()-');
400 END Prorate_tax_amount;
401
402 END ARP_LLCA_ADJUST_PKG;
403