[Home] [Help]
PACKAGE BODY: APPS.ARP_PA_UTILS
Source
1 PACKAGE BODY ARP_PA_UTILS AS
2 /* $Header: ARXPAUTB.pls 120.2.12020000.3 2012/07/30 10:29:18 ashlkuma ship $ */
3
4 /*=======================================================================+
5 | Declare PUBLIC Data Types and Variables
6 +=======================================================================*/
7
8 /*=======================================================================+
9 | Declare PUBLIC Exceptions
10 +=======================================================================*/
11
12 /*========================================================================
13 | PUBLIC Procedure get_line_applied
14 |
15 | DESCRIPTION
16 | This function returns the total line amount applied and
17 | corresponding exchange rate gain and/or loss as of given date for the
18 | the given invoice.
19 |
20 | PSEUDO CODE/LOGIC
21 |
22 | PARAMETERS
23 | IN
24 | p_customer_trx_id
25 | p_as_of_date
26 | OUT NOCOPY
27 | x_line_applied - Line applied
28 | x_line_acctd_applied - Line applied in Functional Currency
29 | x_xchange_gain - Exchange Gain
30 | x_xchange_loss - Exchange Loss
31 | x_return_status - Standard return status
32 | x_msg_data - Standard msg data
33 | x_msg_count - Standard msg count
34 |
35 |
36 | RETURNS
37 | nothing
38 |
39 | KNOWN ISSUES
40 |
41 |
42 |
43 | NOTES
44 |
45 |
46 | MODIFICATION HISTORY
47 | Date Author Description of Changes
48 | 22-Jul-2002 Ramakant Alat Created
49 | 22-Aug-2002 MGOWDA Added MRC logic
50 |
51 *=======================================================================*/
52 PROCEDURE get_line_applied(
53 p_application_id IN
54 ar_receivable_applications.applied_customer_trx_id%TYPE,
55 p_customer_trx_id IN
56 ar_receivable_applications.applied_customer_trx_id%TYPE,
57 p_as_of_date IN ar_receivable_applications.apply_date%TYPE,
58 p_process_rsob IN VARCHAR2,
59 x_applied_amt_list OUT NOCOPY ARP_PA_UTILS.r_appl_amt_list,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2
63 ) AS
64 r_amount_applied num_arr := num_arr();
65 r_acctd_amount_applied num_arr := num_arr();
66 r_xchange_gain num_arr := num_arr();
67 r_xchange_loss num_arr := num_arr();
68 r_rsob_id num_arr := num_arr();
69 l_psob_id number;
70 l_total number;
71 l_amount_applied number;
72 l_acctd_amount_applied number;
73 l_xchange_gain number;
74 l_xchange_loss number;
75 l_rep_no_data BOOLEAN := FALSE;
76 /* Added for FP bug6673099 */
77 l_line_adjusted number;
78 l_acctd_line_adjusted number;
79 -- r_mrc_line_adjusted num_arr := num_arr();
80
81
82 BEGIN
83 l_rep_no_data := FALSE;
84 l_psob_id := arp_global.sysparam.set_of_books_id;
85
86 /* Get primary data */
87 /*** We need the line_applied and corresponding xchange gain or loss ***/
88
89 BEGIN
90 SELECT
91 NVL(SUM(NVL(line_applied,0) ), 0)
92 , NVL(SUM(arpcurr.currround((line_applied * acctd_amount_applied_to)/
93 amount_applied )), 0)
94 , NVL(SUM(DECODE(SIGN(acctd_amount_applied_from -
95 acctd_amount_applied_to),
96 1,
97 arpcurr.currround(((acctd_amount_applied_from -
98 acctd_amount_applied_to) *
99 line_applied)/amount_applied),0)), 0)
100 , NVL(SUM(DECODE(SIGN(acctd_amount_applied_from -
101 acctd_amount_applied_to),
102 -1,
103 arpcurr.currround(((acctd_amount_applied_from -
104 acctd_amount_applied_to) * -1 *
105 line_applied)/amount_applied),0)),
106 0)
107 INTO l_amount_applied
108 ,l_acctd_amount_applied
109 ,l_xchange_gain
110 ,l_xchange_loss
111 FROM ar_receivable_applications app,
112 ar_cash_receipts cr,
113 ra_customer_trx cm,
114 ra_customer_trx inv
115 WHERE
116 applied_customer_trx_id = p_customer_trx_id
117 AND applied_customer_trx_id = inv.customer_trx_id
118 AND app.status in ('APP')
119 AND app.cash_receipt_id = cr.cash_receipt_id (+)
120 AND app.customer_trx_id = cm.customer_trx_id (+)
121 AND nvl(app.confirmed_flag,'Y') = 'Y'
122 AND display = 'Y'
123 AND apply_date <= p_as_of_date
124 AND amount_applied <> 0
125 GROUP BY app.set_of_books_id;
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 l_amount_applied := 0;
129 l_acctd_amount_applied := 0;
130 l_xchange_gain := 0;
131 l_xchange_loss := 0;
132 WHEN others THEN
133 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
134 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
135 'ar_pa_utils.get_line_applied:' ||SQLERRM(SQLCODE));
136 FND_MSG_PUB.Add;
137
138 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
139 p_count => x_msg_count,
140 p_data => x_msg_data
141 );
142 x_return_status := FND_API.G_RET_STS_ERROR;
143
144 END;
145 /* Added for FP bug6673099 */
146 BEGIN
147
148 SELECT sum(adj.line_adjusted) ,
149 sum(adj.line_adjusted*ra.exchange_rate)
150 INTO l_line_adjusted ,
151 l_acctd_line_adjusted
152 FROM ar_adjustments_all adj ,
153 ra_customer_trx_all ra
154 WHERE adj.customer_trx_id = ra.customer_trx_id
155 AND adj.customer_trx_id = p_customer_trx_id
156 AND adj.status='A'
157 AND adj.apply_date <= p_as_of_date;
158
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 l_line_adjusted :=0;
162 l_acctd_line_adjusted := 0;
163
164 WHEN OTHERS THEN
165 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
166 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
167 'ar_pa_utils.get_line_applied:' ||SQLERRM(SQLCODE));
168 FND_MSG_PUB.ADD;
169
170 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
171 p_count => x_msg_count,
172 p_data => x_msg_data);
173 x_return_status := FND_API.G_RET_STS_ERROR;
174
175 END;
176 /* Added for FP bug6673099 */
177
178 /* Get reporting data */
179 --{BUG4301323
180 /*
181 IF NVL(p_process_rsob, 'N') = 'Y' AND
182 NVL(l_psob_id, -99) <> -99
183 THEN
184 BEGIN
185 SELECT amr.set_of_books_id
186 , NVL(SUM(NVL(app.line_applied,0) ), 0)
187 , NVL(SUM(arpcurr.currround((app.line_applied * amr.acctd_amount_applied_to)/
188 app.amount_applied )), 0)
189 , NVL(SUM(DECODE(SIGN(amr.acctd_amount_applied_from -
190 amr.acctd_amount_applied_to),
191 1,
192 GL_MC_CURRENCY_PKG.CURRROUND((((amr.acctd_amount_applied_from -
193 amr.acctd_amount_applied_to) *
194 app.line_applied)
195 /app.amount_applied),glr.TARGET_CURRENCY_CODE),0)),0)
196 , NVL(SUM(DECODE(SIGN(amr.acctd_amount_applied_from -
197 amr.acctd_amount_applied_to), -1,
198 GL_MC_CURRENCY_PKG.CURRROUND((((amr.acctd_amount_applied_from -
199 amr.acctd_amount_applied_to) * -1 *
200 app.line_applied)/app.amount_applied),glr.TARGET_CURRENCY_CODE),0)),0)
201 BULK COLLECT INTO
202 r_rsob_id
203 ,r_amount_applied
204 ,r_acctd_amount_applied
205 ,r_xchange_gain
206 ,r_xchange_loss
207 FROM ar_receivable_applications app,
208 ar_mc_receivable_apps amr,
209 ar_cash_receipts cr,
210 gl_ledger_relationships glr,
211 ra_customer_trx cm,
212 ra_customer_trx inv
213 WHERE applied_customer_trx_id = p_customer_trx_id
214 AND applied_customer_trx_id = inv.customer_trx_id
215 AND app.status in ('APP')
216 AND app.cash_receipt_id = cr.cash_receipt_id (+)
217 AND app.customer_trx_id = cm.customer_trx_id (+)
218 AND nvl(app.confirmed_flag,'Y') = 'Y'
219 AND app.display = 'Y'
220 AND apply_date <= p_as_of_date
221 AND glr.TARGET_LEDGER_ID = amr.set_of_books_id
222 AND glr.SOURCE_LEDGER_ID = l_psob_id
223 AND glr.RELATIONSHIP_ENABLED_FLAG = 'Y'
224 AND glr.application_id = p_application_id
225 AND target_ledger_category_code = 'ALC'
226 AND relationship_type_code = 'SUBLEDGER'
227 AND app.amount_applied <> 0
228 AND app.receivable_application_id = amr.receivable_application_id
229 GROUP BY amr.set_of_books_id;
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 l_rep_no_data := TRUE;
233 WHEN OTHERS THEN
234 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
235 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
236 'ar_public_utils.get_amount_applied:'||SQLERRM(SQLCODE));
237 FND_MSG_PUB.Add;
238
239 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
240 p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 END;
245
246 IF l_rep_no_data
247 THEN
248 BEGIN
249 SELECT target_ledger_id
250 ,0
251 ,0
252 ,0
253 ,0
254 BULK COLLECT INTO
255 r_rsob_id
256 ,r_amount_applied
257 ,r_acctd_amount_applied
258 ,r_xchange_gain
259 ,r_xchange_loss
260 FROM gl_ledger_relationships glr
261 WHERE glr.source_ledger_id = l_psob_id
262 AND target_ledger_category_code = 'ALC'
263 AND relationship_type_code = 'SUBLEDGER'
264 AND glr.RELATIONSHIP_ENABLED_FLAG = 'Y'
265 AND glr.application_id = p_application_id;
266 EXCEPTION
267 WHEN OTHERS THEN
268 FND_MESSAGE.SET_NAME ('AR','NO_RSOB_FOUND');
269 FND_MESSAGE.SET_TOKEN('PSOB_ID', l_psob_id);
270 END;
271 END IF;
272 END IF;
273 */
274
275
276 /* Store primary values in array of records */
277
278 IF NVL(l_psob_id, -99) <> -99
279 THEN
280 x_applied_amt_list(l_psob_id).sob_id := l_psob_id;
281 x_applied_amt_list(l_psob_id).amount_applied := l_amount_applied;
282 x_applied_amt_list(l_psob_id).acctd_amount_applied := l_acctd_amount_applied;
283 x_applied_amt_list(l_psob_id).exchange_gain := l_xchange_gain;
284 x_applied_amt_list(l_psob_id).exchange_loss := l_xchange_loss;
285 /* Added for FP bug6673099 */
286 x_applied_amt_list(l_psob_id).line_adjusted := l_line_adjusted ;
287 x_applied_amt_list(l_psob_id).acctd_line_adjusted := l_acctd_line_adjusted ;
288 --{BUG4301323
289 /*
290 IF r_amount_applied.count > 0
291 THEN
292 FOR i IN 1..r_amount_applied.count
293 LOOP
294 x_applied_amt_list(r_rsob_id(i)).sob_id := r_rsob_id(i);
295 x_applied_amt_list(r_rsob_id(i)).amount_applied := r_amount_applied(i);
296 x_applied_amt_list(r_rsob_id(i)).acctd_amount_applied := r_acctd_amount_applied(i);
297 x_applied_amt_list(r_rsob_id(i)).exchange_gain := r_xchange_gain(i);
298 x_applied_amt_list(r_rsob_id(i)).exchange_loss := r_xchange_loss(i);
299 END LOOP;
300 END IF;
301 */
302 END IF;
303 END get_line_applied;
304 END ARP_PA_UTILS;