[Home] [Help]
PACKAGE BODY: APPS.ARP_REVENUE_ASSIGNMENTS
Source
1 PACKAGE BODY arp_revenue_assignments AS
2 /*$Header: ARREVUB.pls 120.3.12000000.2 2007/05/22 19:15:15 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 *===================================================================================*/
37
38 PROCEDURE build_for_credit(
39 p_session_id IN number,
40 p_period_set_name IN gl_periods.period_set_name%TYPE,
41 p_request_id IN ra_customer_trx_all.request_id%TYPE,
42 p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
43 p_customer_trx_line_id IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE)
44 IS
45
46 BEGIN
47
48 arp_standard.debug('arp_revenue_assignments.build_for_credit()+');
49
50 IF (p_request_id is not null)
51 THEN
52
53 arp_standard.debug('arp_revenue_assignments - using request_id');
54
55 INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
56 (SESSION_ID,
57 CUSTOMER_TRX_LINE_ID,
58 COMPLETE_FLAG,
59 ACCOUNT_CLASS,
60 LUMP_SUM_FLAG,
61 RULE_TYPE,
62 PERIOD_NUMBER,
63 PERCENT,
64 RULE_DATE,
65 SET_OF_BOOKS_ID,
66 PERIOD_TYPE,
67 MAX_REGULAR_PERIOD_LENGTH)
68 select distinct p_session_id,
69 tl.previous_customer_trx_line_id,
70 t.complete_flag,
71 ral.lookup_code account_class,
72 decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
73 rr.type,
74 rrs.period_number,
75 decode(rr.type, 'ACC_DUR',
76 decode(rrs_lump.rule_id, null,
77 (1/nvl(itl.accounting_rule_duration, 1)) ,
78 decode(rrs.period_number, 1, rrs_lump.percent / 100,
79 (1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
80 itl.accounting_rule_duration - 1)) *
81 (1 - rrs_lump.percent/100))) * 100,
82 rrs.percent) percent,
83 rrs.rule_date,
84 tl.set_of_books_id,
85 decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
86 decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
87 gsb.accounted_period_type)) period_type,
88 apt.max_regular_period_length
89 from
90 ra_customer_trx_lines tl,
91 ra_customer_trx_lines itl,
92 ra_customer_trx t,
93 ra_rules rr,
94 ra_rule_schedules rrs,
95 ra_rule_schedules rrs_lump,
96 ar_lookups ral,
97 gl_sets_of_books gsb,
98 ar_period_types apt
99 where
100 tl.customer_trx_id = t.customer_trx_id
101 and tl.accounting_rule_id = rr.rule_id
102 and tl.set_of_books_id = gsb.set_of_books_id
103 and tl.previous_customer_trx_line_id =
104 itl.customer_trx_line_id (+)
105 and gsb.accounted_period_type = apt.period_type
106 and ral.lookup_type = 'AUTOGL_TYPE'
107 and (ral.lookup_code = 'REV' or
108 ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
109 -3, 'UNBILL'))
110 and rrs.period_number <= DECODE(rr.type, 'PP_DR_PP', 1,
111 'PP_DR_ALL', 1,
112 nvl(itl.accounting_rule_duration, rr.occurrences))
113 and rrs_lump.rule_id (+) = rr.rule_id
114 and rrs_lump.period_number (+) = 1
115 and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id
116 and t.request_id = p_request_id
117 and t.previous_customer_trx_id is not null;
118
119 ELSIF (p_customer_trx_line_id is not null)
120 THEN
121
122 arp_standard.debug('arp_revenue_assignments - using line_id');
123
124 INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
125 (SESSION_ID,
126 CUSTOMER_TRX_LINE_ID,
127 COMPLETE_FLAG,
128 ACCOUNT_CLASS,
129 LUMP_SUM_FLAG,
130 RULE_TYPE,
131 PERIOD_NUMBER,
132 PERCENT,
133 RULE_DATE,
134 SET_OF_BOOKS_ID,
135 PERIOD_TYPE,
136 MAX_REGULAR_PERIOD_LENGTH)
137 select distinct p_session_id,
138 tl.previous_customer_trx_line_id,
139 t.complete_flag,
140 ral.lookup_code account_class,
141 decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
142 rr.type,
143 rrs.period_number,
144 decode(rr.type, 'ACC_DUR',
145 decode(rrs_lump.rule_id, null, 1/nvl(itl.accounting_rule_duration, 1),
146 decode(rrs.period_number, 1, rrs_lump.percent / 100,
147 (1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
148 itl.accounting_rule_duration - 1)) *
149 (1 - rrs_lump.percent/100))) * 100,
150 rrs.percent) percent,
151 rrs.rule_date,
152 tl.set_of_books_id,
153 decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
154 decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
155 gsb.accounted_period_type)) period_type,
156 apt.max_regular_period_length
157 from
158 ra_customer_trx_lines tl,
159 ra_customer_trx_lines itl,
160 ra_customer_trx t,
161 ra_rules rr,
162 ra_rule_schedules rrs,
163 ra_rule_schedules rrs_lump,
164 ar_lookups ral,
165 gl_sets_of_books gsb,
166 ar_period_types apt
167 where
168 tl.customer_trx_line_id = p_customer_trx_line_id
169 and tl.customer_trx_id = t.customer_trx_id
170 and tl.accounting_rule_id = rr.rule_id
171 and tl.set_of_books_id = gsb.set_of_books_id
172 and tl.previous_customer_trx_line_id =
173 itl.customer_trx_line_id (+)
174 and gsb.accounted_period_type = apt.period_type
175 and ral.lookup_type = 'AUTOGL_TYPE'
176 and (ral.lookup_code = 'REV' or
177 ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
178 -3, 'UNBILL'))
179 and rrs.period_number <= DECODE(rr.type, 'PP_DR_PP', 1,
180 'PP_DR_ALL', 1,
181 nvl(itl.accounting_rule_duration, rr.occurrences))
182 and rrs_lump.rule_id (+) = rr.rule_id
183 and rrs_lump.period_number (+) = 1
184 and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id;
185
186 ELSE
187
188 arp_standard.debug('arp_revenue_assignments - using trx_id');
189
190 INSERT INTO AR_REVENUE_ASSIGNMENTS_GT
191 (SESSION_ID,
192 CUSTOMER_TRX_LINE_ID,
193 COMPLETE_FLAG,
194 ACCOUNT_CLASS,
195 LUMP_SUM_FLAG,
196 RULE_TYPE,
197 PERIOD_NUMBER,
198 PERCENT,
199 RULE_DATE,
200 SET_OF_BOOKS_ID,
201 PERIOD_TYPE,
202 MAX_REGULAR_PERIOD_LENGTH)
203 select distinct p_session_id,
204 tl.previous_customer_trx_line_id,
205 t.complete_flag,
206 ral.lookup_code account_class,
207 decode(rr.type, 'ACC_DUR', decode(rrs_lump.rule_id, null, 'N', 'Y'), 'N') lump_sum_flag,
208 rr.type,
209 rrs.period_number,
210 decode(rr.type, 'ACC_DUR',
211 decode(rrs_lump.rule_id, null, 1/nvl(itl.accounting_rule_duration, 1),
212 decode(rrs.period_number, 1, rrs_lump.percent / 100,
213 (1 / decode(itl.accounting_rule_duration, 1, 1, null, 1,
214 itl.accounting_rule_duration - 1)) *
215 (1 - rrs_lump.percent/100))) * 100,
216 rrs.percent) percent,
217 rrs.rule_date,
218 tl.set_of_books_id,
219 decode(rr.frequency, 'SPECIFIC', gsb.accounted_period_type,
220 decode(tl.previous_customer_trx_line_id, NULL, rr.frequency,
221 gsb.accounted_period_type)) period_type,
222 apt.max_regular_period_length
223 from
224 ra_customer_trx_lines tl,
225 ra_customer_trx_lines itl,
226 ra_customer_trx t,
227 ra_rules rr,
228 ra_rule_schedules rrs,
229 ra_rule_schedules rrs_lump,
230 ar_lookups ral,
231 gl_sets_of_books gsb,
232 ar_period_types apt
233 where
234 t.customer_trx_id = p_customer_trx_id
235 and tl.customer_trx_id = t.customer_trx_id
236 and tl.accounting_rule_id = rr.rule_id
237 and tl.set_of_books_id = gsb.set_of_books_id
238 and tl.previous_customer_trx_line_id =
239 itl.customer_trx_line_id (+)
240 and gsb.accounted_period_type = apt.period_type
241 and ral.lookup_type = 'AUTOGL_TYPE'
242 and (ral.lookup_code = 'REV' or
243 ral.lookup_code = decode(t.invoicing_rule_id, -2, 'UNEARN',
244 -3, 'UNBILL'))
245 and rrs.period_number <= DECODE(rr.type, 'PP_DR_PP', 1,
246 'PP_DR_ALL', 1,
247 nvl(itl.accounting_rule_duration, rr.occurrences))
248 and rrs_lump.rule_id (+) = rr.rule_id
249 and rrs_lump.period_number (+) = 1
250 and decode(rr.type, 'A',rr.rule_id, -1) = rrs.rule_id;
251
252 END IF;
253 IF PG_DEBUG in ('Y', 'C') THEN
254 arp_standard.debug('ar_revenue_assignments_gt ***');
255 DECLARE
256 CURSOR c01 IS
257 SELECT
258 SESSION_ID,
259 CUSTOMER_TRX_LINE_ID,
260 COMPLETE_FLAG,
261 ACCOUNT_CLASS,
262 LUMP_SUM_FLAG,
263 RULE_TYPE,
264 PERIOD_NUMBER,
265 PERCENT,
266 RULE_DATE,
267 SET_OF_BOOKS_ID,
268 PERIOD_TYPE,
269 MAX_REGULAR_PERIOD_LENGTH
270 FROM AR_REVENUE_ASSIGNMENTS_GT
271 WHERE session_id = p_session_id
272 ORDER BY CUSTOMER_TRX_LINE_ID, RULE_date;
273 i NUMBER := 1;
274 BEGIN
275 FOR c01_rec IN c01 LOOP
276 arp_standard.debug('['|| i || ']: CUSTOMER_TRX_LINE_ID <' ||
277 c01_rec.CUSTOMER_TRX_LINE_ID || '>');
278 arp_standard.debug('> Complete? <' ||
279 c01_rec.COMPLETE_FLAG || '>');
280 arp_standard.debug('> ACCOUNT_CLASS <' ||
281 c01_rec.ACCOUNT_CLASS || '> ' );
282 arp_standard.debug('> LUMP_SUM_FLAG <' ||
283 c01_rec.LUMP_SUM_FLAG || '> ' );
284 arp_standard.debug('> RULE_TYPE <' ||
285 c01_rec.RULE_TYPE || '> ' );
286 arp_standard.debug('> PERIOD_NUMBER <' ||
287 c01_rec.PERIOD_NUMBER || '> ' );
288 arp_standard.debug('> PERCENT <' ||
289 c01_rec.PERCENT || '> ' );
290 arp_standard.debug('> RULE_DATE <' ||
291 c01_rec.RULE_DATE || '> ' );
292 arp_standard.debug('> SET_OF_BOOKS_ID <' ||
293 c01_rec.SET_OF_BOOKS_ID || '> ' );
294 arp_standard.debug('> PERIOD_TYPE <' ||
295 c01_rec.PERIOD_TYPE || '> ' );
296 arp_standard.debug('> MAX_REGULAR_PERIOD_LENGTH <' ||
297 c01_rec.MAX_REGULAR_PERIOD_LENGTH || '> ' );
298 i := i + 1;
299 END LOOP;
300 END;
301 END IF;
302 arp_standard.debug('arp_revenue_assignments.build_for_credit()-');
303
304
305 END;
306
307 END arp_revenue_assignments;