[Home] [Help]
PACKAGE BODY: APPS.ARP_REVENUE_ASSIGNMENTS
Source
1 PACKAGE BODY arp_revenue_assignments AS
2 /*$Header: ARREVUB.pls 120.7 2011/06/29 21:03:03 mraymond ship $*/
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'Y');
5
6 /* ==================================================================================
7 | PROCEDURE build_for_credit
8 |
9 | DESCRIPTION
10 | This procedure populates ar_revenue_assignments_gt (a global temporary
11 | table) with rows from ar_revenue_assignments (the view). In 11i apps,
12 | the CBO seems to have a lot of trouble with any sql containing this
13 | view. So this is an effort to offload that work to a separate
14 | sql step.
15 |
16 | SCOPE - PUBLIC
17 |
18 | PARAMETERS
19 | p_session_id IN number
20 | p_period_set_name IN Period set name
21 | p_request_id IN request_id (if coming from RAXTRX)
22 | p_customer_trx_id IN customer_trx_id
23 | p_customer_trx_line_id IN customer_trx_line_id
24 |
25 | DEV NOTE: customer_trx_line_id in the global temporary table actually
26 | refer to the invoice line_id (previous_customer_trx_line_id). That is because
27 | the view expected to join based on previous_customer_trx_line_id.
28 |
29 | session_id is an integer number that is assigned by the calling program each time
30 | it calls this package. It can be any number as long as it is unique within
31 | each sql session.
32 |
33 | 18-SEP-2005 M Raymond 4602892 Added 'distinct' to inserts to prevent the
34 | creation of multiple rows in _GT table and, ultimately
35 | the cartesian insert of extra gl_dist rows.
36 | 21-JAN-2011 M Raymond 10144015 - Changed the logic for setting pprr_amount
37 | from gl_date to original_gl_date to insure that
38 | pprr_amount is set when invoice gl_dates are bumped
39 | in revenue schedule.
40 | 29-JUN-2011 M Raymond 12684247 - Corrected problem with imported credits
41 | and PPRR rules. We were only generating one
42 | period of accounting entries by mistake.
43 *===================================================================================*/
44
45 PROCEDURE build_for_credit(
46 p_session_id IN number,
47 p_period_set_name IN gl_periods.period_set_name%TYPE,
48 p_use_inv_acctg IN varchar2,
49 p_request_id IN ra_customer_trx_all.request_id%TYPE,
50 p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
51 p_customer_trx_line_id IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE)
52 IS
53
54 BEGIN
55 IF PG_DEBUG IN ('Y','C')
56 THEN
57 arp_standard.debug('arp_revenue_assignments.build_for_credit()+');
58 arp_standard.debug(' p_session_id = ' || p_session_id);
59 arp_standard.debug(' p_period_set_name = ' || p_period_set_name);
60 arp_standard.debug(' p_use_inv_acctg = ' || p_use_inv_acctg);
61 arp_standard.debug(' p_request_id = ' || p_request_id);
62 arp_standard.debug(' p_customer_trx_id = ' || p_customer_trx_id);
63 arp_standard.debug(' p_customer_trx_line_id = ' || p_customer_trx_line_id);
64 END IF;
65
66 IF (p_request_id is not null)
67 THEN
68 IF PG_DEBUG IN ('Y','C')
69 THEN
70 arp_standard.debug('arp_revenue_assignments - using request_id');
71 END IF;
72
73 INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
74 (SESSION_ID,
75 CUSTOMER_TRX_LINE_ID,
76 COMPLETE_FLAG,
77 ACCOUNT_CLASS,
78 LUMP_SUM_FLAG,
79 RULE_TYPE,
80 PERIOD_NUMBER,
81 PERCENT,
82 RULE_DATE,
83 SET_OF_BOOKS_ID,
84 PERIOD_TYPE,
85 MAX_REGULAR_PERIOD_LENGTH)
86 select distinct p_session_id,
87 tl.previous_customer_trx_line_id,
88 t.complete_flag,
89 ral.lookup_code account_class,
90 decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
91 rr.type,
92 rrs.period_number,
93 decode(rr.type, 'ACC_DUR',
94 decode(rrs_lump.rule_id, null,
95 (1/nvl(itl.accounting_rule_duration, 1)) ,
96 decode(rrs.period_number, 1, rrs_lump.percent / 100,
97 (1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
98 itl.accounting_rule_duration - 1)) *
99 (1 - rrs_lump.percent/100))) * 100,
100 rrs.percent) percent,
101 rrs.rule_date,
102 tl.set_of_books_id,
103 decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
104 decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
105 gsb.accounted_period_type)) period_type,
106 apt.max_regular_period_length
107 from
108 ra_customer_trx_lines tl,
109 ra_customer_trx_lines itl,
113 ra_rule_schedules rrs_lump,
110 ra_customer_trx t,
111 ra_rules rr,
112 ra_rule_schedules rrs,
114 ar_lookups ral,
115 gl_sets_of_books gsb,
116 ar_period_types apt
117 where
118 tl.customer_trx_id = t.customer_trx_id
119 and tl.accounting_rule_id = rr.rule_id
120 and tl.set_of_books_id = gsb.set_of_books_id
121 and tl.previous_customer_trx_line_id =
122 itl.customer_trx_line_id (+)
123 and gsb.accounted_period_type = apt.period_type
124 and ral.lookup_type = 'AUTOGL_TYPE'
125 and (ral.lookup_code = 'REV' or
126 ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
127 -3, 'UNBILL'))
128 and rrs.period_number <=
129 nvl(itl.accounting_rule_duration, rr.occurrences)
130 and rrs_lump.rule_id (+) = rr.rule_id
131 and rrs_lump.period_number (+) = 1
132 and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id
133 and t.request_id = p_request_id
134 and t.previous_customer_trx_id is not null;
135
136 ELSIF (p_customer_trx_line_id is not null)
137 THEN
138 IF PG_DEBUG IN ('Y','C')
139 THEN
140 arp_standard.debug('arp_revenue_assignments - using line_id');
141 END IF;
142
143 INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
144 (SESSION_ID,
145 CUSTOMER_TRX_LINE_ID,
146 COMPLETE_FLAG,
147 ACCOUNT_CLASS,
148 LUMP_SUM_FLAG,
149 RULE_TYPE,
150 PERIOD_NUMBER,
151 PERCENT,
152 RULE_DATE,
153 SET_OF_BOOKS_ID,
154 PERIOD_TYPE,
155 MAX_REGULAR_PERIOD_LENGTH)
156 select distinct p_session_id,
157 tl.previous_customer_trx_line_id,
158 t.complete_flag,
159 ral.lookup_code account_class,
160 decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
161 rr.type,
162 rrs.period_number,
163 decode(rr.type, 'ACC_DUR',
164 decode(rrs_lump.rule_id, null, 1/nvl(itl.accounting_rule_duration, 1),
165 decode(rrs.period_number, 1, rrs_lump.percent / 100,
166 (1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
167 itl.accounting_rule_duration - 1)) *
168 (1 - rrs_lump.percent/100))) * 100,
169 rrs.percent) percent,
170 rrs.rule_date,
171 tl.set_of_books_id,
172 decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
173 decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
174 gsb.accounted_period_type)) period_type,
175 apt.max_regular_period_length
176 from
177 ra_customer_trx_lines tl,
178 ra_customer_trx_lines itl,
179 ra_customer_trx t,
180 ra_rules rr,
181 ra_rule_schedules rrs,
182 ra_rule_schedules rrs_lump,
183 ar_lookups ral,
184 gl_sets_of_books gsb,
185 ar_period_types apt
186 where
187 tl.customer_trx_line_id = p_customer_trx_line_id
188 and tl.customer_trx_id = t.customer_trx_id
189 and tl.accounting_rule_id = rr.rule_id
190 and tl.set_of_books_id = gsb.set_of_books_id
191 and tl.previous_customer_trx_line_id =
192 itl.customer_trx_line_id (+)
193 and gsb.accounted_period_type = apt.period_type
194 and ral.lookup_type = 'AUTOGL_TYPE'
195 and (ral.lookup_code = 'REV' or
196 ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
197 -3, 'UNBILL'))
198 and rrs.period_number <=
199 nvl(itl.accounting_rule_duration, rr.occurrences)
200 and rrs_lump.rule_id (+) = rr.rule_id
201 and rrs_lump.period_number (+) = 1
202 and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id;
203
204 ELSE
205 IF PG_DEBUG IN ('Y','C')
206 THEN
207 arp_standard.debug('arp_revenue_assignments - using trx_id');
208 END IF;
209
210 INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
211 (SESSION_ID,
212 CUSTOMER_TRX_LINE_ID,
213 COMPLETE_FLAG,
214 ACCOUNT_CLASS,
215 LUMP_SUM_FLAG,
216 RULE_TYPE,
217 PERIOD_NUMBER,
218 PERCENT,
219 RULE_DATE,
220 SET_OF_BOOKS_ID,
221 PERIOD_TYPE,
222 MAX_REGULAR_PERIOD_LENGTH)
223 select distinct p_session_id,
224 tl.previous_customer_trx_line_id,
225 t.complete_flag,
226 ral.lookup_code account_class,
227 decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
228 rr.type,
229 rrs.period_number,
230 decode(rr.type, 'ACC_DUR',
231 decode(rrs_lump.rule_id, null, 1/nvl(itl.accounting_rule_duration, 1),
232 decode(rrs.period_number, 1, rrs_lump.percent / 100,
233 (1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
234 itl.accounting_rule_duration - 1)) *
235 (1 - rrs_lump.percent/100))) * 100,
236 rrs.percent) percent,
237 rrs.rule_date,
238 tl.set_of_books_id,
239 decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
240 decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
241 gsb.accounted_period_type)) period_type,
242 apt.max_regular_period_length
243 from
244 ra_customer_trx_lines tl,
245 ra_customer_trx_lines itl,
246 ra_customer_trx t,
247 ra_rules rr,
248 ra_rule_schedules rrs,
249 ra_rule_schedules rrs_lump,
250 ar_lookups ral,
251 gl_sets_of_books gsb,
252 ar_period_types apt
253 where
254 t.customer_trx_id = p_customer_trx_id
255 and tl.customer_trx_id = t.customer_trx_id
256 and tl.accounting_rule_id = rr.rule_id
257 and tl.set_of_books_id = gsb.set_of_books_id
258 and tl.previous_customer_trx_line_id =
259 itl.customer_trx_line_id (+)
260 and gsb.accounted_period_type = apt.period_type
261 and ral.lookup_type = 'AUTOGL_TYPE'
262 and (ral.lookup_code = 'REV' or
263 ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
264 -3, 'UNBILL'))
265 and rrs.period_number <=
266 nvl(itl.accounting_rule_duration, rr.occurrences)
267 and rrs_lump.rule_id (+) = rr.rule_id
268 and rrs_lump.period_number (+) = 1
269 and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id;
270
271 END IF;
272
273 /* 7666667 - If we are processing any PPRR rules, set the PPRR_AMOUNT
274 column for use in prorating credits */
275 IF p_use_inv_acctg = 'Y'
276 THEN
277 /* 10144015 - changed from gl_date to original_gl_date */
278 UPDATE ar_revenue_assignments_gt ragt
279 SET pprr_amount =
280 (SELECT Sum(ilgd.amount)
281 FROM ra_customer_trx_lines il,
282 ra_cust_trx_line_gl_dist ilgd,
283 ar_periods arp1, -- first period
284 ar_periods arp2 -- current period
285 WHERE ragt.customer_trx_line_id = il.customer_trx_line_id
286 AND il.rule_start_date BETWEEN arp1.start_date AND arp1.end_date
287 AND arp2.new_period_num >= arp1.new_period_num
288 AND arp1.period_type = ragt.period_type
289 AND arp1.period_set_name = p_period_set_name
290 AND arp2.period_type = ragt.period_type
291 AND arp2.period_set_name = p_period_set_name
292 AND ragt.period_number = (arp2.new_period_num - arp1.new_period_num + 1)
293 AND il.customer_trx_line_id = ilgd.customer_trx_line_id
294 AND ilgd.account_class = ragt.account_class
295 AND ilgd.account_set_flag = 'N'
296 AND ilgd.rec_offset_flag IS NULL
297 AND ilgd.original_gl_date BETWEEN
298 arp2.start_date AND arp2.end_date)
299 WHERE ragt.rule_type in ('PP_DR_PP','PP_DR_ALL')
300 AND ragt.session_id = p_session_id;
301
302 IF PG_DEBUG IN ('Y','C')
303 THEN
304 arp_standard.debug(' row(s) updated for pprr = ' || SQL%ROWCOUNT);
305 END IF;
306 END IF;
307
308 IF PG_DEBUG in ('Y', 'C') THEN
309 arp_standard.debug('DUMP of ar_revenue_assignments_gt ');
310 DECLARE
311 CURSOR c01 IS
312 SELECT
313 SESSION_ID,
314 CUSTOMER_TRX_LINE_ID,
315 COMPLETE_FLAG,
316 ACCOUNT_CLASS,
317 LUMP_SUM_FLAG,
318 RULE_TYPE,
319 PERIOD_NUMBER,
320 PERCENT,
321 RULE_DATE,
322 SET_OF_BOOKS_ID,
323 PERIOD_TYPE,
324 MAX_REGULAR_PERIOD_LENGTH,
325 PPRR_AMOUNT
326 FROM AR_REVENUE_ASSIGNMENTS_GT
327 WHERE session_id = p_session_id
328 ORDER BY CUSTOMER_TRX_LINE_ID, PERIOD_NUMBER;
329 i NUMBER := 1;
330 BEGIN
331 FOR c01_rec IN c01 LOOP
332 arp_standard.debug('['|| i || ']:' ||
333 c01_rec.CUSTOMER_TRX_LINE_ID || '~' ||
334 c01_rec.COMPLETE_FLAG || '~' ||
335 c01_rec.ACCOUNT_CLASS || '~' ||
336 c01_rec.LUMP_SUM_FLAG || '~' ||
337 c01_rec.RULE_TYPE || '~' ||
338 c01_rec.PERIOD_NUMBER || '~' ||
339 c01_rec.PERCENT || '~' ||
340 c01_rec.RULE_DATE || '~' ||
341 c01_rec.SET_OF_BOOKS_ID || '~' ||
342 c01_rec.PERIOD_TYPE || '~' ||
343 c01_rec.MAX_REGULAR_PERIOD_LENGTH || '~' ||
344 c01_rec.PPRR_AMOUNT);
345 i := i + 1;
346 END LOOP;
347 END;
348
349 arp_standard.debug('arp_revenue_assignments.build_for_credit()-');
350
351 END IF;
352
353 END;
354
355 END arp_revenue_assignments;