[Home] [Help]
PACKAGE BODY: APPS.ARP_DISTRIBUTIONS_PKG
Source
1 PACKAGE BODY ARP_DISTRIBUTIONS_PKG AS
2 /* $Header: ARJIDSTB.pls 120.13 2010/07/23 03:01:14 nemani ship $*/
3 --
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PROCEDURE insert_p(
7 p_dist_rec IN ar_distributions%ROWTYPE,
8 p_line_id OUT NOCOPY ar_distributions.line_id%TYPE ) IS
9 l_line_id ar_distributions.line_id%TYPE;
10 exp_null_insert exception;
11 pragma exception_init(exp_null_insert, -1400);
12 BEGIN
13 IF PG_DEBUG in ('Y', 'C') THEN
14 arp_standard.debug( 'arp_distributions_pkg.insert_p()+' );
15 END IF;
16 --
17 SELECT ar_distributions_s.nextval
18 INTO l_line_id
19 FROM dual;
20 --
21 IF PG_DEBUG in ('Y', 'C') THEN
22 arp_standard.debug( 'LINE ID '|| l_line_id);
23 arp_standard.debug( 'SOURCE_ID ' || p_dist_rec.SOURCE_ID);
24 arp_standard.debug( 'SOURCE_TABLE ' || p_dist_rec.SOURCE_TABLE);
25 arp_standard.debug( 'SOURCE_TYPE ' || p_dist_rec.SOURCE_TYPE);
26 arp_standard.debug( 'SOURCE_TYPE_SECONDARY ' || p_dist_rec.SOURCE_TYPE_SECONDARY);
27 arp_standard.debug( 'CODE_COMBINATION_ID '||p_dist_rec.CODE_COMBINATION_ID);
28 arp_standard.debug( 'AMOUNT_DR ' || p_dist_rec.AMOUNT_DR);
29 arp_standard.debug( 'AMOUNT_CR ' || p_dist_rec.AMOUNT_CR);
30 arp_standard.debug( 'ACCTD_AMOUNT_DR ' || p_dist_rec.ACCTD_AMOUNT_DR);
31 arp_standard.debug( 'ACCTD_AMOUNT_CR ' ||p_dist_rec.ACCTD_AMOUNT_CR);
32 arp_standard.debug( 'SOURCE_TABLE_SECONDARY ' || p_dist_rec.SOURCE_TABLE_SECONDARY);
33 arp_standard.debug( 'SOURCE_ID_SECONDARY ' || p_dist_rec.SOURCE_ID_SECONDARY);
34 arp_standard.debug( 'CURRENCY_CODE ' || p_dist_rec.CURRENCY_CODE);
35 arp_standard.debug( 'CURRENCY_CONVERSION_RATE ' || p_dist_rec.CURRENCY_CONVERSION_RATE);
36 arp_standard.debug( 'CURRENCY_CONVERSION_TYPE ' || p_dist_rec.CURRENCY_CONVERSION_TYPE);
37 arp_standard.debug( 'CURRENCY_CONVERSION_DATE ' || p_dist_rec.CURRENCY_CONVERSION_DATE);
38 arp_standard.debug( 'TAXABLE_ENTERED_DR ' || p_dist_rec.TAXABLE_ENTERED_DR);
39 arp_standard.debug( 'TAXABLE_ENTERED_CR ' || p_dist_rec.TAXABLE_ENTERED_CR);
40 arp_standard.debug( 'TAXABLE_ACCOUNTED_DR ' || p_dist_rec.TAXABLE_ACCOUNTED_DR);
41 arp_standard.debug( 'TAXABLE_ACCOUNTED_CR ' || p_dist_rec.TAXABLE_ACCOUNTED_CR);
42 arp_standard.debug( 'TAX_LINK_ID ' || p_dist_rec.TAX_LINK_ID);
43 arp_standard.debug( 'THIRD_PARTY_ID ' || p_dist_rec.THIRD_PARTY_ID);
44 arp_standard.debug( 'THIRD_PARTY_SUB_ID ' || p_dist_rec.THIRD_PARTY_SUB_ID);
45 arp_standard.debug( 'REVERSED_SOURCE_ID ' || p_dist_rec.REVERSED_SOURCE_ID);
46 arp_standard.debug( 'TAX_GROUP_CODE_ID ' || p_dist_rec.TAX_GROUP_CODE_ID);
47 arp_standard.debug( 'TAX_CODE_ID ' || p_dist_rec.TAX_CODE_ID);
48 arp_standard.debug( 'LOCATION_SEGMENT_ID ' || p_dist_rec.LOCATION_SEGMENT_ID);
49 --HYU--{
50 arp_standard.debug( 'FROM_AMOUNT_DR ' || p_dist_rec.FROM_AMOUNT_DR);
51 arp_standard.debug( 'FROM_AMOUNT_CR ' || p_dist_rec.FROM_AMOUNT_CR);
52 arp_standard.debug( 'FROM_ACCTD_AMOUNT_DR ' || p_dist_rec.FROM_ACCTD_AMOUNT_DR);
53 arp_standard.debug( 'FROMACCTD_AMOUNT_CR ' || p_dist_rec.FROM_ACCTD_AMOUNT_CR);
54 arp_standard.debug( 'ref_customer_trx_line_id ' || p_dist_rec.ref_customer_trx_line_id);
55 arp_standard.debug( 'ref_prev_cust_trx_line_id ' || p_dist_rec.ref_prev_cust_trx_line_id);
56 arp_standard.debug( 'ref_cust_trx_line_gl_dist_id ' || p_dist_rec.ref_cust_trx_line_gl_dist_id);
57 arp_standard.debug( 'ref_account_class ' || p_dist_rec.ref_account_class);
58 arp_standard.debug( 'activity_bucket ' || p_dist_rec.activity_bucket);
59 arp_standard.debug( 'REF_DIST_CCID ' || p_dist_rec.ref_dist_ccid);
60 arp_standard.debug( 'REF_MF_DIST_FLAG ' || p_dist_rec.ref_mf_dist_flag);
61 --HYU--}
62 END IF;
63
64 INSERT INTO ar_distributions (
65 line_id,
66 source_id,
67 source_table,
68 source_type,
69 source_type_secondary,
70 code_combination_id,
71 amount_dr,
72 amount_cr,
73 acctd_amount_dr,
74 acctd_amount_cr,
75 created_by,
76 creation_date,
77 last_updated_by,
78 last_update_date,
79 last_update_login,
80 source_id_secondary,
81 source_table_secondary,
82 currency_code ,
83 currency_conversion_rate,
84 currency_conversion_type,
85 currency_conversion_date,
86 third_party_id,
87 third_party_sub_id,
88 tax_code_id,
89 location_segment_id,
90 taxable_entered_dr,
91 taxable_entered_cr,
92 taxable_accounted_dr,
93 taxable_accounted_cr,
94 tax_link_id,
95 reversed_source_id,
96 tax_group_code_id
97 ,org_id
98 --{BUG#2979254
99 ,ref_customer_trx_line_id
100 ,ref_prev_cust_trx_line_id
101 ,ref_cust_trx_line_gl_dist_id
102 ,ref_line_id
103 ,from_amount_dr
104 ,from_amount_cr
105 ,from_acctd_amount_dr
106 ,from_acctd_amount_cr
107 ,ref_account_class
108 ,activity_bucket
109 ,ref_dist_ccid
110 ,ref_mf_dist_flag
111 --}
112 )
113 VALUES ( l_line_id,
114 p_dist_rec.source_id,
115 p_dist_rec.source_table,
116 p_dist_rec.source_type,
117 p_dist_rec.source_type_secondary,
118 p_dist_rec.code_combination_id,
119 p_dist_rec.amount_dr,
120 p_dist_rec.amount_cr,
121 p_dist_rec.acctd_amount_dr,
122 p_dist_rec.acctd_amount_cr,
123 arp_standard.profile.user_id,
124 SYSDATE,
125 arp_standard.profile.user_id,
126 SYSDATE,
127 NVL( arp_standard.profile.last_update_login,
128 p_dist_rec.last_update_login ),
129 p_dist_rec.source_id_secondary,
130 p_dist_rec.source_table_secondary,
131 p_dist_rec.currency_code ,
132 p_dist_rec.currency_conversion_rate,
133 p_dist_rec.currency_conversion_type,
134 p_dist_rec.currency_conversion_date,
135 p_dist_rec.third_party_id,
136 p_dist_rec.third_party_sub_id,
137 p_dist_rec.tax_code_id,
138 p_dist_rec.location_segment_id,
139 p_dist_rec.taxable_entered_dr,
140 p_dist_rec.taxable_entered_cr,
141 p_dist_rec.taxable_accounted_dr,
142 p_dist_rec.taxable_accounted_cr,
143 p_dist_rec.tax_link_id,
144 p_dist_rec.reversed_source_id,
145 p_dist_rec.tax_group_code_id
146 ,arp_standard.sysparm.org_id /* SSA changes anuj */
147 --{BUG#2979254
148 ,p_dist_rec.ref_customer_trx_line_id
149 ,p_dist_rec.ref_prev_cust_trx_line_id
150 ,p_dist_rec.ref_cust_trx_line_gl_dist_id
151 ,p_dist_rec.ref_line_id
152 ,p_dist_rec.from_amount_dr
153 ,p_dist_rec.from_amount_cr
154 ,p_dist_rec.from_acctd_amount_dr
155 ,p_dist_rec.from_acctd_amount_cr
156 ,p_dist_rec.ref_account_class
157 ,p_dist_rec.activity_bucket
158 ,p_dist_rec.ref_dist_ccid
159 ,p_dist_rec.ref_mf_dist_flag
160 --}
161 );
162 --
163 p_line_id := l_line_id;
164 --
165 IF PG_DEBUG in ('Y', 'C') THEN
166 arp_standard.debug( 'arp_distributions_pkg.insert_p()-' );
167 END IF;
168 EXCEPTION
169 WHEN exp_null_insert THEN
170 FND_MESSAGE.SET_NAME('AR','AR_INS_NULL_INTO_NOTNULL');
171 APP_EXCEPTION.RAISE_EXCEPTION;
172 WHEN OTHERS THEN
173 IF PG_DEBUG in ('Y', 'C') THEN
174 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.insert_p' );
175 END IF;
176 RAISE;
177 END insert_p;
178 --
179 PROCEDURE update_p(
180 p_dist_rec IN ar_distributions%ROWTYPE ) IS
181 BEGIN
182 IF PG_DEBUG in ('Y', 'C') THEN
183 arp_standard.debug( 'arp_distributions_pkg.update_p()+' );
184 END IF;
185 --
186 UPDATE ar_distributions SET
187 source_id = p_dist_rec.source_id,
188 source_table = p_dist_rec.source_table,
189 source_type = p_dist_rec.source_type,
190 source_type_secondary = p_dist_rec.source_type_secondary,
191 code_combination_id = p_dist_rec.code_combination_id,
192 amount_dr = p_dist_rec.amount_dr,
196 last_updated_by = arp_standard.profile.user_id,
193 amount_cr = p_dist_rec.amount_cr,
194 acctd_amount_dr = p_dist_rec.acctd_amount_dr,
195 acctd_amount_cr = p_dist_rec.acctd_amount_cr,
197 last_update_date = SYSDATE,
198 last_update_login = NVL( arp_standard.profile.last_update_login,
199 p_dist_rec.last_update_login ),
200 source_id_secondary = p_dist_rec.source_id_secondary,
201 source_table_secondary = p_dist_rec.source_table_secondary,
202 currency_code = p_dist_rec.currency_code,
203 currency_conversion_rate = p_dist_rec.currency_conversion_rate,
204 currency_conversion_type = p_dist_rec.currency_conversion_type,
205 currency_conversion_date = p_dist_rec.currency_conversion_date,
206 third_party_id = p_dist_rec.third_party_id,
207 third_party_sub_id = p_dist_rec.third_party_sub_id,
208 tax_code_id = p_dist_rec.tax_code_id,
209 location_segment_id = p_dist_rec.location_segment_id,
210 taxable_entered_dr = p_dist_rec.taxable_entered_dr,
211 taxable_entered_cr = p_dist_rec.taxable_entered_cr,
212 taxable_accounted_dr = p_dist_rec.taxable_accounted_dr,
213 taxable_accounted_cr = p_dist_rec.taxable_accounted_cr,
214 tax_link_id = p_dist_rec.tax_link_id,
215 reversed_source_id = p_dist_rec.reversed_source_id,
216 tax_group_code_id = p_dist_rec.tax_group_code_id,
217 --{BUG#2979254
218 ref_customer_trx_line_id = p_dist_rec.ref_customer_trx_line_id,
219 ref_cust_trx_line_gl_dist_id = p_dist_rec.ref_cust_trx_line_gl_dist_id,
220 ref_line_id = p_dist_rec.ref_line_id,
221 from_amount_dr = p_dist_rec.from_amount_dr,
222 from_amount_cr = p_dist_rec.from_amount_cr,
223 from_acctd_amount_dr = p_dist_rec.from_acctd_amount_dr,
224 from_acctd_amount_cr = p_dist_rec.from_acctd_amount_cr,
225 ref_account_class = p_dist_rec.ref_account_class,
226 activity_bucket = p_dist_rec.activity_bucket,
227 ref_dist_ccid = p_dist_rec.ref_dist_ccid,
228 ref_mf_dist_flag = p_dist_rec.ref_mf_dist_flag
229 --}
230 WHERE line_id = p_dist_rec.line_id;
231 --
232 IF PG_DEBUG in ('Y', 'C') THEN
233 arp_standard.debug( 'arp_distributions_pkg.update_p()-' );
234 END IF;
235 EXCEPTION
236 WHEN OTHERS THEN
237 IF PG_DEBUG in ('Y', 'C') THEN
238 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.update_p' );
239 END IF;
240 RAISE;
241 END update_p;
242 --
243 PROCEDURE delete_p(
244 p_line_id IN ar_distributions.line_id%TYPE ) IS
245 BEGIN
246 IF PG_DEBUG in ('Y', 'C') THEN
247 arp_standard.debug( 'arp_distributions_pkg.delete_p()+' );
248 END IF;
249 --
250 DELETE FROM ar_distributions
251 WHERE line_id = p_line_id;
252 --
253 IF PG_DEBUG in ('Y', 'C') THEN
254 arp_standard.debug( 'arp_distributions_pkg.delete_p()-' );
255 END IF;
256 EXCEPTION
257 WHEN OTHERS THEN
258 IF PG_DEBUG in ('Y', 'C') THEN
259 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.delete_p' );
260 END IF;
261 RAISE;
262 END delete_p;
263 --
264 PROCEDURE lock_p(
265 p_line_id IN ar_distributions.line_id%TYPE ) IS
266 l_line_id ar_distributions.line_id%TYPE;
267 BEGIN
268 IF PG_DEBUG in ('Y', 'C') THEN
269 arp_standard.debug( 'arp_distributions_pkg.lock_p()+' );
270 END IF;
271 SELECT line_id
272 INTO l_line_id
273 FROM ar_distributions
274 WHERE line_id = p_line_id
275 FOR UPDATE OF line_id;
276 --
277 IF PG_DEBUG in ('Y', 'C') THEN
278 arp_standard.debug( 'arp_distributions_pkg.lock_p()-' );
279 END IF;
280 EXCEPTION
281 WHEN OTHERS THEN
282 IF PG_DEBUG in ('Y', 'C') THEN
283 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.lock_p' );
284 END IF;
285 RAISE;
286 END lock_p;
287 --
288 PROCEDURE fetch_p(
289 p_line_id IN ar_distributions.line_id%TYPE,
290 p_dist_rec OUT NOCOPY ar_distributions%ROWTYPE ) IS
291 BEGIN
292 IF PG_DEBUG in ('Y', 'C') THEN
293 arp_standard.debug( 'arp_distributions_pkg.fetch_p()+' );
294 END IF;
295 SELECT *
296 INTO p_dist_rec
297 FROM ar_distributions
298 WHERE line_id = p_line_id;
299 IF PG_DEBUG in ('Y', 'C') THEN
300 arp_standard.debug( 'arp_distributions_pkg.fetch_p()-' );
301 END IF;
302 --
303 EXCEPTION
304 --
305 WHEN OTHERS THEN
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.fetch_p' );
308 END IF;
309 RAISE;
310 END fetch_p;
311 --
312 PROCEDURE fetch_pk(
313 p_source_id IN ar_distributions.source_id%TYPE,
314 p_source_table IN ar_distributions.source_table%TYPE,
315 p_source_type IN ar_distributions.source_type%TYPE,
316 p_dist_rec OUT NOCOPY ar_distributions%ROWTYPE ) IS
317 BEGIN
318 IF PG_DEBUG in ('Y', 'C') THEN
319 arp_standard.debug( 'arp_distributions_pkg.fetch_pk()+' );
320 END IF;
321 SELECT *
322 INTO p_dist_rec
323 FROM ar_distributions
324 WHERE source_id = p_source_id
325 AND source_table = p_source_table
326 AND source_type = p_source_type;
327 IF PG_DEBUG in ('Y', 'C') THEN
328 arp_standard.debug( 'arp_distributions_pkg.fetch_pk()-' );
329 END IF;
330 --
331 EXCEPTION
332 --
333 WHEN OTHERS THEN
334 IF PG_DEBUG in ('Y', 'C') THEN
335 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.fetch_pk' );
336 END IF;
337 RAISE;
338 END fetch_pk;
339 --
340 PROCEDURE lock_fetch_pk(
341 p_source_id IN ar_distributions.source_id%TYPE,
342 p_source_table IN ar_distributions.source_table%TYPE,
343 p_source_type IN ar_distributions.source_type%TYPE,
344 p_dist_rec OUT NOCOPY ar_distributions%ROWTYPE ) IS
345 BEGIN
346 IF PG_DEBUG in ('Y', 'C') THEN
347 arp_standard.debug( 'arp_distributions_pkg.lock_fetch_pk()+' );
348 END IF;
349 SELECT *
350 INTO p_dist_rec
351 FROM ar_distributions
352 WHERE source_id = p_source_id
353 AND source_table = p_source_table
354 AND source_type = p_source_type
355 FOR UPDATE OF line_id;
356
357 IF PG_DEBUG in ('Y', 'C') THEN
358 arp_standard.debug( 'arp_distributions_pkg.lock_fetch_pk()-' );
359 END IF;
360 --
361 EXCEPTION
362 --
363 WHEN OTHERS THEN
364 IF PG_DEBUG in ('Y', 'C') THEN
365 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.lock_fetch_pk' );
366 END IF;
367 RAISE;
368 END lock_fetch_pk;
369 --
370 PROCEDURE nowaitlock_fetch_pk(
371 p_source_id IN ar_distributions.source_id%TYPE,
372 p_source_table IN ar_distributions.source_table%TYPE,
373 p_source_type IN ar_distributions.source_type%TYPE,
374 p_dist_rec OUT NOCOPY ar_distributions%ROWTYPE ) IS
375 BEGIN
376 IF PG_DEBUG in ('Y', 'C') THEN
377 arp_standard.debug( 'arp_distributions_pkg.nowaitlock_fetch_pk()+' );
378 END IF;
379 SELECT *
380 INTO p_dist_rec
381 FROM ar_distributions
382 WHERE source_id = p_source_id
383 AND source_table = p_source_table
384 AND source_type = p_source_type
385 FOR UPDATE OF line_id NOWAIT;
386
387 IF PG_DEBUG in ('Y', 'C') THEN
388 arp_standard.debug( 'arp_distributions_pkg.nowaitlock_fetch_pk()-' );
389 END IF;
390 --
391 EXCEPTION
392 --
393 WHEN OTHERS THEN
394 IF PG_DEBUG in ('Y', 'C') THEN
395 arp_standard.debug( 'EXCEPTION: arp_distributions_pkg.nowaitlock_fetch_pk' );
396 END IF;
397 RAISE;
398 END nowaitlock_fetch_pk;
399 --
400 END ARP_DISTRIBUTIONS_PKG;