1 PACKAGE BODY ar_match_rev_cogs_grp AS
2 /* $Header: ARCGSRVB.pls 120.17.12010000.10 2008/11/05 14:22:39 mraymond ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'Y');
5
6 /*=======================================================================+
7 | Global Constants
8 +=======================================================================*/
9
10 g_pkg_name CONSTANT VARCHAR2(30):= 'ar_match_rev_cogs_grp';
11 g_om_context ra_interface_lines.interface_line_context%type;
12
13 g_so_line_id NUMBER := 0;
14 g_period_number NUMBER := 0;
15 g_potential_revenue NUMBER := 0;
16
17 /* 5664384 - This is a semi-temporary solution to a problem with
18 analytic functions for accumulating the total potential revenue
19 for a given so_line as of the specificed period.
20
21 NOTE: I expect to replace this with aggregate functions as soon
22 as we can determine the correct approach. */
23 FUNCTION potential_revenue(p_so_line_id IN NUMBER, p_period_number IN NUMBER)
24 RETURN NUMBER IS
25
26 BEGIN
27 IF p_so_line_id <> g_so_line_id OR
28 p_period_number <> g_period_number
29 THEN
30 SELECT sum(l.revenue_amount)
31 INTO g_potential_revenue
32 FROM ra_customer_trx_lines_all l
33 WHERE EXISTS
34 (SELECT 'eligible transaction captured in GT table'
35 FROM ar_trx_cogs_gt cogs
36 WHERE cogs.so_line_id = p_so_line_id
37 AND cogs.period_number <= p_period_number
38 AND cogs.customer_trx_id = l.customer_trx_id
39 AND cogs.customer_trx_line_id = l.customer_trx_line_id);
40
41 g_so_line_id := p_so_line_id;
42 g_period_number := p_period_number;
43 END IF;
44
45 RETURN g_potential_revenue;
46
47 /* Removed EXCEPTION block. There is no known scenario where
48 a NDF or other failure would be acceptable or managable. */
49
50 END;
51
52
53 PROCEDURE period_status (
54 p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
56 p_commit IN VARCHAR2 := fnd_api.g_false,
57 p_eff_period_num IN NUMBER,
58 p_sob_id IN NUMBER,
59 x_status OUT NOCOPY VARCHAR2,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2) IS
63
64 l_api_version CONSTANT NUMBER := 1.0;
65 l_api_name CONSTANT VARCHAR2(30) := 'period_status';
66
67 CURSOR status IS
68 SELECT closing_status
69 FROM gl_period_statuses ps
70 WHERE adjustment_period_flag = 'N'
71 AND application_id = 222
72 AND set_of_books_id = p_sob_id
73 AND effective_period_num = p_eff_period_num;
74
75 BEGIN
76
77 IF PG_DEBUG in ('Y', 'C') THEN
78 arp_util.debug( 'ar_match_rev_cogs_grp.period_status()+ ');
79 END IF;
80
81 -- Standard Start of API savepoint
82 SAVEPOINT period_status_grp;
83
84 -- Standard call to check for call compatibility.
85 IF NOT FND_API.Compatible_API_Call (
86 p_current_version_number => l_api_version,
87 p_caller_version_number => p_api_version,
88 p_api_name => l_api_name,
89 p_pkg_name => g_pkg_name) THEN
90
91 RAISE fnd_api.g_exc_unexpected_error;
92
93 END IF;
94
95 -- Initialize message list if p_init_msg_list is set to TRUE.
96 IF FND_API.to_Boolean( p_init_msg_list ) THEN
97 fnd_msg_pub.initialize;
98 END IF;
99
100 -- Initialize API return status to success
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102
103 OPEN status;
104 FETCH status INTO x_status;
105 CLOSE status;
106
107 IF PG_DEBUG in ('Y', 'C') THEN
108 arp_util.debug( 'ar_match_rev_cogs_grp.period_status()- ');
109 END IF;
110
111 EXCEPTION
112 WHEN fnd_api.g_exc_error THEN
113 ROLLBACK TO period_status_grp;
114 x_return_status := FND_API.G_RET_STS_ERROR ;
115 fnd_msg_pub.count_and_get (
116 p_encoded => fnd_api.g_false,
117 p_count => x_msg_count,
118 p_data => x_msg_data);
119
120 WHEN fnd_api.g_exc_unexpected_error THEN
121 ROLLBACK TO period_status_grp;
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 fnd_msg_pub.count_and_get (
124 p_encoded => fnd_api.g_false,
125 p_count => x_msg_count,
126 p_data => x_msg_data);
127
128 WHEN OTHERS THEN
129 ROLLBACK TO period_status_grp;
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131 fnd_msg_pub.count_and_get (
132 p_encoded => fnd_api.g_false,
133 p_count => x_msg_count,
134 p_data => x_msg_data);
135
136 END period_status;
137
138
139 FUNCTION get_costing_period_status (
140 p_period_name VARCHAR2)
141 RETURN VARCHAR2 IS
142
143 l_api_version NUMBER := 1.0;
144 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
145 l_eff_period_num NUMBER;
146 l_set_of_books_id NUMBER;
147 l_status VARCHAR2(30);
148 l_return_status VARCHAR2(30);
149 l_msg_count NUMBER;
150 l_msg_data VARCHAR2(150);
151
152 CURSOR epm IS
153 SELECT effective_period_num, sp.set_of_books_id
154 FROM gl_period_statuses ps, ar_system_parameters sp
155 WHERE ps.set_of_books_id = sp.set_of_books_id
156 AND adjustment_period_flag = 'N'
157 AND application_id = 222
158 AND period_name = p_period_name;
159
160 BEGIN
161
162 IF PG_DEBUG in ('Y', 'C') THEN
163 arp_util.debug( 'ar_match_rev_cogs_grp.get_costing_period_status()+ ');
164 END IF;
165
166 OPEN epm;
167 FETCH epm INTO l_eff_period_num, l_set_of_books_id;
168 CLOSE epm;
169
170
171 cst_revenuecogsmatch_grp.return_periodstatuses(
172 p_api_version => l_api_version,
173 p_init_msg_list => l_init_msg_list,
174 p_commit => NULL,
175 p_validation_level => NULL,
176 x_return_status => l_return_status,
177 x_msg_count => l_msg_count,
178 x_msg_data => l_msg_data,
179 p_set_of_books_id => l_set_of_books_id,
180 p_effective_period_num => l_eff_period_num,
181 x_closed_cst_periods => l_status);
182
183 --l_status := 'C';
184
185 IF PG_DEBUG in ('Y', 'C') THEN
186 arp_util.debug( 'ar_match_rev_cogs_grp.get_costing_period_status()- ');
187 END IF;
188
189 RETURN l_status;
190
191 END get_costing_period_status;
192
193
194 PROCEDURE populate_cst_tables (
195 p_api_version IN NUMBER,
196 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
197 p_commit IN VARCHAR2 := fnd_api.g_false,
198 p_from_gl_date DATE,
199 p_to_gl_date DATE,
200 p_ledger_id IN NUMBER DEFAULT NULL,
201 x_status OUT NOCOPY VARCHAR2,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2) IS
205
206 l_request_id NUMBER;
207 l_user_id NUMBER;
208 l_login_id NUMBER;
209 l_pgm_app_id NUMBER;
210 l_pgm_id NUMBER;
211 l_api_version CONSTANT NUMBER := 1.0;
212 l_api_name CONSTANT VARCHAR2(30) := 'populate_cst_tables';
213
214 /* 5664384 - table and cursor to fetch affected sales orders */
215 TYPE so_number_table_type IS
216 TABLE OF VARCHAR2(128)
217 INDEX BY BINARY_INTEGER;
218 so_numbers so_number_table_type;
219
220 TYPE so_org_table_type IS
221 TABLE OF NUMBER
222 INDEX BY BINARY_INTEGER;
223 so_orgs so_org_table_type;
224
225 /* 7463284 - New cursor to limit gl_dist rows by sob_id */
226 CURSOR so_number_by_sob_c(p_low_date DATE, p_hi_date DATE,
227 p_ledger_id NUMBER) IS
228 SELECT /*+ LEADING(p,gld,gld2)
229 INDEX(gld,RA_CUST_TRX_LINE_GL_DIST_N2)
230 USE_HASH(gld) USE_NL(gld2) ROWID(gld2) */
231 distinct l.sales_order, l.org_id
232 FROM ra_customer_trx_lines_all l,
233 ra_cust_trx_line_gl_dist_all gld,
234 ra_cust_trx_line_gl_dist_all gld2,
235 ar_system_parameters_all p
236 WHERE gld.gl_date between p_low_date and p_hi_date
237 AND gld2.account_set_flag = 'N'
238 AND gld.org_id = p.org_id
239 AND p.set_of_books_id = p_ledger_id
240 AND gld.ROWID = gld2.rowid
241 AND gld2.customer_trx_line_id = l.customer_trx_line_id
242 AND l.sales_order_line is NOT NULL
243 AND l.interface_line_context = g_om_context
244 AND l.interface_line_attribute6 IS NOT NULL;
245
246 /* original cursor (not limited by sob_id) */
247 CURSOR so_number_c(p_low_date DATE, p_hi_date DATE) IS
248 SELECT distinct l.sales_order, l.org_id
249 FROM ra_customer_trx_lines_all l,
250 ra_cust_trx_line_gl_dist_all gld
251 WHERE gld.gl_date between p_low_date and p_hi_date
252 AND gld.account_set_flag = 'N'
253 AND gld.customer_trx_line_id = l.customer_trx_line_id
254 AND l.sales_order_line is NOT NULL
255 AND l.interface_line_context = g_om_context
256 AND l.interface_line_attribute6 IS NOT NULL;
257
258 /* debug - dump content of GT table */
259 CURSOR trx_gt IS
260 SELECT *
261 FROM ar_trx_cogs_gt;
262
263 BEGIN
264
265 IF PG_DEBUG in ('Y', 'C') THEN
266 arp_debug.debug( 'ar_match_rev_cogs_grp.populate_cst_tables()+ ');
267 arp_debug.debug(' p_ledger_id = ' || p_ledger_id);
268 arp_debug.debug(' p_gl_date = ' || p_from_gl_date ||
269 ' to ' || p_to_gl_date);
270
271 END IF;
272
273 -- Standard Start of API savepoint
274 SAVEPOINT populate_cst_tables_grp;
275
276 -- Standard call to check for call compatibility.
277 IF NOT FND_API.Compatible_API_Call (
278 p_current_version_number => l_api_version,
279 p_caller_version_number => p_api_version,
280 p_api_name => l_api_name,
281 p_pkg_name => g_pkg_name) THEN
282
283 RAISE fnd_api.g_exc_unexpected_error;
284
285 END IF;
286
287 -- Initialize message list if p_init_msg_list is set to TRUE.
288 IF FND_API.to_Boolean( p_init_msg_list ) THEN
289 fnd_msg_pub.initialize;
290 END IF;
291
292 -- Initialize API return status to success
293 x_return_status := FND_API.G_RET_STS_SUCCESS;
294
295 -- Populate WHO column variables
296 l_request_id := fnd_global.conc_request_id;
297 l_user_id := fnd_global.user_id;
298 l_login_id := fnd_global.login_id;
299 l_pgm_app_id := fnd_global.prog_appl_id;
300 l_pgm_id := fnd_global.conc_program_id;
301
302 /* Step 1 - Identify the unique sales orders that affect or
303 are affected in the specified GL_DATE range. */
304
305 /* NOTE: The question has come up as to why we use sales_order..
306 The answer.. the original order and price adjustment lines will
307 bear the sales order number. Additionally, RMAs for that trx
308 will also come in with different values in ILA6, but the same
309 value for sales_order. So it was the obvious (and easiest)
310 way to group related transactions from OM */
311
312 /* 7463284 - execute separate cursors for p_ledger_id */
313 IF p_ledger_id IS NOT NULL
314 THEN
315
316 OPEN so_number_by_sob_c(p_from_gl_date, p_to_gl_date, p_ledger_id);
317 FETCH so_number_by_sob_c BULK COLLECT INTO so_numbers, so_orgs;
318 CLOSE so_number_by_sob_c;
319
320 ELSE
321 /* original (non sob_id) logic */
322 OPEN so_number_c(p_from_gl_date, p_to_gl_date);
323 FETCH so_number_c BULK COLLECT INTO so_numbers, so_orgs;
324 CLOSE so_number_c;
325
326 END IF;
327
328 /* Step 2 - populate AR_TRX_COGS_GT with summarized data. Data is
329 recorded per invoice line and GL period. The potential revenue
330 (revenue_line_amount) is recorded in each period that a transaction
331 line effects but it must be considered only once in creating
332 the divisor for the final revenue percentage. This is currently
333 handled via a function call */
334
335 FORALL i in 1 .. so_numbers.count
336 INSERT INTO ar_trx_cogs_gt
337 ( customer_trx_id,
338 customer_trx_line_id,
339 previous_customer_trx_line_id,
340 so_line_id,
341 period_number,
342 revenue_dist_amount,
346 set_of_books_id
343 revenue_line_amount,
344 latest_gl_date,
345 org_id,
347 )
348 SELECT /*+ ORDERED */
349 tl.customer_trx_id,
350 tl.customer_trx_line_id,
351 tl.previous_customer_trx_line_id,
352 to_number(
353 decode(tl.previous_customer_trx_line_id, NULL,
354 tl.interface_line_attribute6,
355 tli.interface_line_attribute6)),
356 gps.effective_period_num,
357 sum(tlgld.amount), -- revenue_dist_amount
358 tl.revenue_amount, -- revenue_line_amount (not currently used)
359 MAX(tlgld.gl_date),-- latest_gl_date
360 tl.org_id,
361 tl.set_of_books_id
362 FROM ra_customer_trx_lines_all tl,
363 ra_customer_trx_lines_all tli,
364 ra_cust_trx_line_gl_dist_all tlgld,
365 gl_period_statuses gps
366 WHERE
367 tl.sales_order = so_numbers(i)
368 AND tl.org_id = so_orgs(i)
369 AND tl.customer_trx_line_id = tlgld.customer_trx_line_id
370 AND tlgld.account_set_flag = 'N'
371 AND tlgld.account_class = 'REV'
372 AND tl.previous_customer_trx_line_id = tli.customer_trx_line_id (+)
373 AND tl.interface_line_context = g_om_context -- 7349970
374 AND NVL(tli.interface_line_context,tl.interface_line_context) =
375 g_om_context
376 AND NVL(tli.interface_line_attribute6, tl.interface_line_attribute6)
377 IS NOT NULL
378 AND NVL(tli.sales_order_line, tl.sales_order_line)
379 IS NOT NULL -- 7349970
380 AND gps.set_of_books_id = tl.set_of_books_id
381 AND gps.application_id = 222
382 AND gps.adjustment_period_flag = 'N'
383 AND tlgld.gl_date between gps.start_date and gps.end_date
384 AND NVL(LENGTH(REPLACE(TRANSLATE(
385 DECODE(tl.previous_customer_trx_line_id, NULL,
386 tl.interface_line_attribute6, tli.interface_line_attribute6),
387 '123456789','0000000000'),'0','')),0) = 0
388 GROUP BY
389 tl.customer_trx_id, tl.customer_trx_line_id,
390 tl.previous_customer_trx_line_id,
391 to_number(
392 decode(tl.previous_customer_trx_line_id, NULL,
393 tl.interface_line_attribute6,
394 tli.interface_line_attribute6)),
395 gps.effective_period_num, tl.revenue_amount,
396 tl.org_id, tl.set_of_books_id;
397
398 /* debug logic +/
399 IF PG_DEBUG in ('Y', 'C')
400 THEN
401 arp_util.debug('start - dump of ar_trx_cogs_gt');
402 FOR gt IN trx_gt LOOP
403 arp_util.debug(gt.customer_trx_id || '~' ||
404 gt.customer_trx_line_id || '~' ||
405 gt.previous_customer_trx_line_id || '~' ||
406 gt.so_line_id);
407 END LOOP;
408 arp_util.debug('end - dump of ar_trx_cogs_gt');
409 END IF;
410 /+ end - debug logic */
411
412 /* Step 3 - Now merge the resulting data into cst_reve_rec_lines.
413 NOTE: We are using the function potential_revenue() to fetch
414 an accumulated total for the equation below. This can probably be
415 replaced with some sort of analytical function when time allows.
416
417 NOTE: The rev_percent calculation is actually a matrix
418 of return values depending on the zero or non-zero state
419 of the numerator and denominator in the actual calc
420
421 Num
422
423 0 !0
424 +
425 0 1 | 1
426 Den +----+---
427 !0 0 | Num/Den
428
429 What this means is that if the denominator (sum of line
430 revenue_amounts) is zero, we always return a 1 (100%).
431 If the denominator is not 0, then we do the calculation
432 of Numerator (sum of rev dist amounts) / denominator.
433 In cases where the numerator is zero and the denominator
434 is not zero, the calc would return zero so we skip it and
435 just return zero directly.
436 */
437
438 MERGE INTO cst_revenue_recognition_lines crrl
439 USING
440 (
441 SELECT
442 rev.so_line_id,
443 max(rev.latest_gl_date) gl_date,
444 gps.effective_period_num period_number,
445 DECODE(ar_match_rev_cogs_grp.potential_revenue(
446 rev.so_line_id,gps.effective_period_num),0,1,
447 DECODE(SUM(rev.revenue_dist_amount),0,0,
448 ROUND(SUM(rev.revenue_dist_amount) /
449 ar_match_rev_cogs_grp.potential_revenue(rev.so_line_id,
450 gps.effective_period_num),4)))
451 rev_percent,
452 max(rev.org_id) org_id,
453 gps.set_of_books_id set_of_books_id
454 FROM ar_trx_cogs_gt rev,
455 gl_period_statuses gps
456 WHERE gps.application_id = 222
457 AND gps.set_of_books_id = rev.set_of_books_id
458 AND gps.start_date <= p_to_gl_date
459 AND gps.adjustment_period_flag = 'N'
460 AND rev.period_number <= gps.effective_period_num
461 GROUP BY rev.so_line_id, gps.effective_period_num,
462 gps.set_of_books_id, gps.start_date, gps.end_date
463 HAVING max(rev.latest_gl_date) between
464 gps.start_date AND gps.end_date
465 ) Q
466 ON (Q.so_line_id = crrl.revenue_om_line_id AND
467 Q.period_number = crrl.acct_period_num)
468 WHEN MATCHED THEN
469 UPDATE SET
470 revenue_recognition_percent = Q.rev_percent,
471 last_event_date = Q.gl_date,
472 potentially_unmatched_flag = 'Y'
473 WHEN NOT MATCHED THEN
474 INSERT (revenue_om_line_id,
475 acct_period_num,
476 revenue_recognition_percent,
477 last_event_date,
478 operating_unit_id,
479 ledger_id,
480 customer_trx_line_id,
481 potentially_unmatched_flag,
482 last_update_date,
483 last_updated_by,
484 creation_date,
485 created_by,
486 last_update_login,
487 request_id,
488 program_application_id,
489 program_id,
490 program_update_date)
491 VALUES (Q.so_line_id,
492 Q.period_number,
493 Q.rev_percent,
494 Q.gl_date,
495 Q.org_id,
496 Q.set_of_books_id,
497 NULL,
498 'Y',
499 sysdate,
500 l_user_id,
501 sysdate,
502 l_user_id,
503 l_login_id,
504 l_request_id,
505 l_pgm_app_id,
506 l_pgm_id,
507 sysdate
508 );
509
510 FND_MSG_PUB.count_and_get
511 ( p_count => x_msg_count
512 , p_data => x_msg_data
513 );
514
515 IF PG_DEBUG in ('Y', 'C') THEN
516 arp_util.debug( 'ar_match_rev_cogs_grp.populate_cst_tables()- ');
517 END IF;
518
519 EXCEPTION
520 WHEN fnd_api.g_exc_error THEN
521 ROLLBACK TO populate_cst_tables_grp;
522 x_return_status := FND_API.G_RET_STS_ERROR ;
523 fnd_msg_pub.count_and_get (
524 p_encoded => fnd_api.g_false,
525 p_count => x_msg_count,
526 p_data => x_msg_data);
527
528 WHEN fnd_api.g_exc_unexpected_error THEN
529 ROLLBACK TO populate_cst_tables_grp;
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
531 fnd_msg_pub.count_and_get (
532 p_encoded => fnd_api.g_false,
533 p_count => x_msg_count,
534 p_data => x_msg_data);
535
536 WHEN OTHERS THEN
537 IF (SQLCODE = -20001) THEN
538 ROLLBACK TO populate_cst_tables_grp;
539 x_return_status := FND_API.G_RET_STS_ERROR ;
540 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
541 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','ar_match_rev_cogs_grp.populate_cst_tables : '||SQLERRM);
542 FND_MSG_PUB.Add;
543
544 fnd_msg_pub.count_and_get (
545 p_encoded => fnd_api.g_false,
546 p_count => x_msg_count,
547 p_data => x_msg_data);
548 RETURN;
549 ELSE
550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
551 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
552 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','ar_match_rev_cogs_grp.populate_cst_tables : '||SQLERRM);
553 FND_MSG_PUB.Add;
554 END IF;
555
556 IF PG_DEBUG in ('Y', 'C') THEN
557 arp_util.debug( SQLCODE);
558 arp_util.debug( SQLERRM);
559 END IF;
560
561 ROLLBACK TO populate_cst_tables_grp;
562 fnd_msg_pub.count_and_get (
563 p_encoded => fnd_api.g_false,
564 p_count => x_msg_count,
565 p_data => x_msg_data);
566
567 END populate_cst_tables;
568
569 BEGIN
570 g_om_context := NVL(fnd_profile.value('ONT_SOURCE_CODE'),'###NOT_SET###');
571 END ar_match_rev_cogs_grp;
[Home] [Help]