[Home] [Help]
PACKAGE BODY: APPS.ARP_PA_UTILS
Source
1 PACKAGE BODY ARP_PA_UTILS AS
2 /* $Header: ARXPAUTB.pls 120.1.12010000.2 2008/11/24 07:57:56 rsamanta 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 GROUP BY app.set_of_books_id;
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 l_amount_applied := 0;
128 l_acctd_amount_applied := 0;
129 l_xchange_gain := 0;
130 l_xchange_loss := 0;
131 WHEN others THEN
132 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
133 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
134 'ar_pa_utils.get_line_applied:' ||SQLERRM(SQLCODE));
135 FND_MSG_PUB.Add;
136
137 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
138 p_count => x_msg_count,
139 p_data => x_msg_data
140 );
141 x_return_status := FND_API.G_RET_STS_ERROR;
142
143 END;
144 /* Added for FP bug6673099 */
145 BEGIN
146
147 SELECT sum(adj.line_adjusted) ,
148 sum(adj.line_adjusted*ra.exchange_rate)
149 INTO l_line_adjusted ,
150 l_acctd_line_adjusted
151 FROM ar_adjustments_all adj ,
152 ra_customer_trx_all ra
153 WHERE adj.customer_trx_id = ra.customer_trx_id
154 AND adj.customer_trx_id = p_customer_trx_id
155 AND adj.status='A'
156 AND adj.apply_date <= p_as_of_date;
157
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 l_line_adjusted :=0;
161 l_acctd_line_adjusted := 0;
162
163 WHEN OTHERS THEN
164 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
165 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
166 'ar_pa_utils.get_line_applied:' ||SQLERRM(SQLCODE));
167 FND_MSG_PUB.ADD;
168
169 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
170 p_count => x_msg_count,
171 p_data => x_msg_data);
172 x_return_status := FND_API.G_RET_STS_ERROR;
173
174 END;
175 /* Added for FP bug6673099 */
176
177 /* Get reporting data */
178 --{BUG4301323
179 /*
180 IF NVL(p_process_rsob, 'N') = 'Y' AND
181 NVL(l_psob_id, -99) <> -99
182 THEN
183 BEGIN
184 SELECT amr.set_of_books_id
185 , NVL(SUM(NVL(app.line_applied,0) ), 0)
186 , NVL(SUM(arpcurr.currround((app.line_applied * amr.acctd_amount_applied_to)/
187 app.amount_applied )), 0)
188 , NVL(SUM(DECODE(SIGN(amr.acctd_amount_applied_from -
189 amr.acctd_amount_applied_to),
190 1,
191 GL_MC_CURRENCY_PKG.CURRROUND((((amr.acctd_amount_applied_from -
192 amr.acctd_amount_applied_to) *
193 app.line_applied)
194 /app.amount_applied),glr.TARGET_CURRENCY_CODE),0)),0)
195 , NVL(SUM(DECODE(SIGN(amr.acctd_amount_applied_from -
196 amr.acctd_amount_applied_to), -1,
197 GL_MC_CURRENCY_PKG.CURRROUND((((amr.acctd_amount_applied_from -
198 amr.acctd_amount_applied_to) * -1 *
199 app.line_applied)/app.amount_applied),glr.TARGET_CURRENCY_CODE),0)),0)
200 BULK COLLECT INTO
201 r_rsob_id
202 ,r_amount_applied
203 ,r_acctd_amount_applied
204 ,r_xchange_gain
205 ,r_xchange_loss
206 FROM ar_receivable_applications app,
207 ar_mc_receivable_apps amr,
208 ar_cash_receipts cr,
209 gl_ledger_relationships glr,
210 ra_customer_trx cm,
211 ra_customer_trx inv
212 WHERE applied_customer_trx_id = p_customer_trx_id
213 AND applied_customer_trx_id = inv.customer_trx_id
214 AND app.status in ('APP')
215 AND app.cash_receipt_id = cr.cash_receipt_id (+)
216 AND app.customer_trx_id = cm.customer_trx_id (+)
217 AND nvl(app.confirmed_flag,'Y') = 'Y'
218 AND app.display = 'Y'
219 AND apply_date <= p_as_of_date
220 AND glr.TARGET_LEDGER_ID = amr.set_of_books_id
221 AND glr.SOURCE_LEDGER_ID = l_psob_id
222 AND glr.RELATIONSHIP_ENABLED_FLAG = 'Y'
223 AND glr.application_id = p_application_id
224 AND target_ledger_category_code = 'ALC'
225 AND relationship_type_code = 'SUBLEDGER'
226 AND app.receivable_application_id = amr.receivable_application_id
227 GROUP BY amr.set_of_books_id;
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 l_rep_no_data := TRUE;
231 WHEN OTHERS THEN
232 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
233 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
234 'ar_public_utils.get_amount_applied:'||SQLERRM(SQLCODE));
235 FND_MSG_PUB.Add;
236
237 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
238 p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 END;
243
244 IF l_rep_no_data
245 THEN
246 BEGIN
247 SELECT target_ledger_id
248 ,0
249 ,0
250 ,0
251 ,0
252 BULK COLLECT INTO
253 r_rsob_id
254 ,r_amount_applied
255 ,r_acctd_amount_applied
256 ,r_xchange_gain
257 ,r_xchange_loss
258 FROM gl_ledger_relationships glr
259 WHERE glr.source_ledger_id = l_psob_id
260 AND target_ledger_category_code = 'ALC'
261 AND relationship_type_code = 'SUBLEDGER'
262 AND glr.RELATIONSHIP_ENABLED_FLAG = 'Y'
263 AND glr.application_id = p_application_id;
264 EXCEPTION
265 WHEN OTHERS THEN
266 FND_MESSAGE.SET_NAME ('AR','NO_RSOB_FOUND');
267 FND_MESSAGE.SET_TOKEN('PSOB_ID', l_psob_id);
268 END;
269 END IF;
270 END IF;
271 */
272
273
274 /* Store primary values in array of records */
275
276 IF NVL(l_psob_id, -99) <> -99
277 THEN
278 x_applied_amt_list(l_psob_id).sob_id := l_psob_id;
279 x_applied_amt_list(l_psob_id).amount_applied := l_amount_applied;
280 x_applied_amt_list(l_psob_id).acctd_amount_applied := l_acctd_amount_applied;
281 x_applied_amt_list(l_psob_id).exchange_gain := l_xchange_gain;
282 x_applied_amt_list(l_psob_id).exchange_loss := l_xchange_loss;
283 /* Added for FP bug6673099 */
284 x_applied_amt_list(l_psob_id).line_adjusted := l_line_adjusted ;
285 x_applied_amt_list(l_psob_id).acctd_line_adjusted := l_acctd_line_adjusted ;
286 --{BUG4301323
287 /*
288 IF r_amount_applied.count > 0
289 THEN
290 FOR i IN 1..r_amount_applied.count
291 LOOP
292 x_applied_amt_list(r_rsob_id(i)).sob_id := r_rsob_id(i);
293 x_applied_amt_list(r_rsob_id(i)).amount_applied := r_amount_applied(i);
294 x_applied_amt_list(r_rsob_id(i)).acctd_amount_applied := r_acctd_amount_applied(i);
295 x_applied_amt_list(r_rsob_id(i)).exchange_gain := r_xchange_gain(i);
296 x_applied_amt_list(r_rsob_id(i)).exchange_loss := r_xchange_loss(i);
297 END LOOP;
298 END IF;
299 */
300 END IF;
301 END get_line_applied;
302 END ARP_PA_UTILS;