1 PACKAGE BODY ar_match_rev_cogs_grp AS
2 /* $Header: ARCGSRVB.pls 120.41 2012/03/12 10:56:53 dgaurab 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 g_bulk_fetch_rows NUMBER := 1000;
18
19 /* 5664384 - This is a semi-temporary solution to a problem with
20 analytic functions for accumulating the total potential revenue
21 for a given so_line as of the specificed period.
22
23 NOTE: I expect to replace this with aggregate functions as soon
24 as we can determine the correct approach. */
25 FUNCTION potential_revenue(p_so_line_id IN NUMBER, p_period_number IN NUMBER)
26 RETURN NUMBER IS
27
28 BEGIN
29 IF p_so_line_id <> g_so_line_id OR
30 p_period_number <> g_period_number
31 THEN
32 SELECT sum(l.revenue_amount)
33 INTO g_potential_revenue
34 FROM ra_customer_trx_lines_all l
35 WHERE EXISTS
36 (SELECT /*+ INDEX (cogs AR_TRX_COGS_N1) */
37 'eligible transaction captured in GT table'
38 FROM ar_trx_cogs_gt cogs
39 WHERE cogs.so_line_id = p_so_line_id
40 AND cogs.period_number <= p_period_number
41 AND cogs.customer_trx_id = l.customer_trx_id
42 AND cogs.customer_trx_line_id = l.customer_trx_line_id);
43
44 g_so_line_id := p_so_line_id;
45 g_period_number := p_period_number;
46 END IF;
47
48 RETURN g_potential_revenue;
49
50 /* Removed EXCEPTION block. There is no known scenario where
51 a NDF or other failure would be acceptable or managable. */
52
53 END;
54
55
56 PROCEDURE period_status (
57 p_api_version IN NUMBER,
58 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
59 p_commit IN VARCHAR2 := fnd_api.g_false,
60 p_eff_period_num IN NUMBER,
61 p_sob_id IN NUMBER,
62 x_status OUT NOCOPY VARCHAR2,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2) IS
66
67 l_api_version CONSTANT NUMBER := 1.0;
68 l_api_name CONSTANT VARCHAR2(30) := 'period_status';
69
70 CURSOR status IS
71 SELECT closing_status
72 FROM gl_period_statuses ps
73 WHERE adjustment_period_flag = 'N'
74 AND application_id = 222
75 AND set_of_books_id = p_sob_id
76 AND effective_period_num = p_eff_period_num;
77
78 BEGIN
79
80 IF PG_DEBUG in ('Y', 'C') THEN
81 arp_debug.debug( 'ar_match_rev_cogs_grp.period_status()+ ');
82 END IF;
83
84 -- Standard Start of API savepoint
85 SAVEPOINT period_status_grp;
86
87 -- Standard call to check for call compatibility.
88 IF NOT FND_API.Compatible_API_Call (
89 p_current_version_number => l_api_version,
90 p_caller_version_number => p_api_version,
91 p_api_name => l_api_name,
92 p_pkg_name => g_pkg_name) THEN
93
94 RAISE fnd_api.g_exc_unexpected_error;
95
96 END IF;
97
98 -- Initialize message list if p_init_msg_list is set to TRUE.
99 IF FND_API.to_Boolean( p_init_msg_list ) THEN
100 fnd_msg_pub.initialize;
101 END IF;
102
103 -- Initialize API return status to success
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 OPEN status;
107 FETCH status INTO x_status;
108 CLOSE status;
109
110 IF PG_DEBUG in ('Y', 'C') THEN
111 arp_debug.debug( 'ar_match_rev_cogs_grp.period_status()- ');
112 END IF;
113
114 EXCEPTION
115 WHEN fnd_api.g_exc_error THEN
116 ROLLBACK TO period_status_grp;
117 x_return_status := FND_API.G_RET_STS_ERROR ;
118 fnd_msg_pub.count_and_get (
119 p_encoded => fnd_api.g_false,
120 p_count => x_msg_count,
121 p_data => x_msg_data);
122
123 WHEN fnd_api.g_exc_unexpected_error THEN
124 ROLLBACK TO period_status_grp;
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
126 fnd_msg_pub.count_and_get (
127 p_encoded => fnd_api.g_false,
128 p_count => x_msg_count,
129 p_data => x_msg_data);
130
131 WHEN OTHERS THEN
132 ROLLBACK TO period_status_grp;
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 fnd_msg_pub.count_and_get (
135 p_encoded => fnd_api.g_false,
136 p_count => x_msg_count,
137 p_data => x_msg_data);
138
139 END period_status;
140
141
142 FUNCTION get_costing_period_status (
143 p_period_name VARCHAR2)
144 RETURN VARCHAR2 IS
145
146 l_api_version NUMBER := 1.0;
147 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
148 l_eff_period_num NUMBER;
149 l_set_of_books_id NUMBER;
150 l_status VARCHAR2(30);
151 l_return_status VARCHAR2(30);
152 l_msg_count NUMBER;
153 l_msg_data VARCHAR2(150);
154
155 CURSOR epm IS
156 SELECT effective_period_num, sp.set_of_books_id
157 FROM gl_period_statuses ps, ar_system_parameters sp
158 WHERE ps.set_of_books_id = sp.set_of_books_id
159 AND adjustment_period_flag = 'N'
160 AND application_id = 222
161 AND period_name = p_period_name;
162
163 BEGIN
164
165 IF PG_DEBUG in ('Y', 'C') THEN
166 arp_debug.debug( 'ar_match_rev_cogs_grp.get_costing_period_status()+ ');
167 END IF;
168
169 OPEN epm;
170 FETCH epm INTO l_eff_period_num, l_set_of_books_id;
171 CLOSE epm;
172
173
174 cst_revenuecogsmatch_grp.return_periodstatuses(
175 p_api_version => l_api_version,
176 p_init_msg_list => l_init_msg_list,
177 p_commit => NULL,
178 p_validation_level => NULL,
179 x_return_status => l_return_status,
180 x_msg_count => l_msg_count,
181 x_msg_data => l_msg_data,
182 p_set_of_books_id => l_set_of_books_id,
183 p_effective_period_num => l_eff_period_num,
184 x_closed_cst_periods => l_status);
185
186 --l_status := 'C';
187
188 IF PG_DEBUG in ('Y', 'C') THEN
189 arp_debug.debug( 'ar_match_rev_cogs_grp.get_costing_period_status()- ');
190 END IF;
191
192 RETURN l_status;
193
194 END get_costing_period_status;
195
196
197 PROCEDURE populate_cst_tables (
198 p_api_version IN NUMBER,
199 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
200 p_commit IN VARCHAR2 := fnd_api.g_false,
201 p_from_gl_date DATE,
202 p_to_gl_date DATE,
203 p_ledger_id IN NUMBER DEFAULT NULL,
204 x_status OUT NOCOPY VARCHAR2,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2) IS
208
209 l_request_id NUMBER;
210 l_user_id NUMBER;
211 l_login_id NUMBER;
212 l_pgm_app_id NUMBER;
213 l_pgm_id NUMBER;
214 l_api_version CONSTANT NUMBER := 1.0;
215 l_api_name CONSTANT VARCHAR2(30) := 'populate_cst_tables';
216 l_rows NUMBER;
217 l_so_rows NUMBER;
218 l_so_rows_inserted NUMBER;
219 l_last_fetch BOOLEAN := FALSE;
220 l_original_org NUMBER;
221 l_original_context VARCHAR2(1);
222
223 /* 5664384 - table and cursor to fetch affected sales orders */
224 TYPE so_number_table_type IS
225 TABLE OF VARCHAR2(128)
226 INDEX BY BINARY_INTEGER;
227 so_numbers so_number_table_type;
228
229 TYPE so_org_table_type IS
230 TABLE OF NUMBER
231 INDEX BY BINARY_INTEGER;
232 so_orgs so_org_table_type;
233
234 TYPE rowid_table_type IS
235 TABLE OF VARCHAR2(128)
236 INDEX BY BINARY_INTEGER;
237 gld_rowids rowid_table_type;
238
239 TYPE cogs_request_id_type IS
240 TABLE OF NUMBER
241 INDEX BY BINARY_INTEGER;
242 gld_cogs_request_ids cogs_request_id_type;
243
244 /* 7463284 - New cursor to limit gl_dist rows by sob_id */
245 CURSOR so_numbers_c(p_request_id NUMBER,
246 p_org_id NUMBER,
247 p_sob_id NUMBER,
248 p_start_date DATE,
249 p_end_date DATE) IS
250 SELECT sales_order, org_id
251 FROM
252 (
253 SELECT l.sales_order,
254 l.org_id,
255 row_number() over
256 (partition by l.sales_order,l.org_id
257 order by l.sales_order, l.org_id) rn
258 FROM ra_customer_trx_lines_all l,
259 ra_cust_trx_line_gl_dist_all gld,
260 gl_date_period_map gl_map,
261 gl_sets_of_books gl_sob
262 WHERE gld.cogs_request_id = p_request_id
263 AND gld.org_id = p_org_id
264 AND gld.account_class = 'REV'
265 AND gld.latest_rec_flag IS NULL
266 AND gld.gl_date = gl_map.accounting_date
267 AND gld.customer_trx_line_id = l.customer_trx_line_id
268 AND gl_sob.set_of_books_id = p_sob_id
269 AND gl_sob.period_set_name = gl_map.period_set_name
270 AND gl_sob.accounted_period_type = gl_map.period_type
271 AND gl_map.accounting_date BETWEEN p_start_date
272 AND p_end_date
273 ) a
274 WHERE a.rn = 1;
275
276
277 CURSOR gld_rows_c(p_request_id NUMBER,
278 p_org_id NUMBER,
279 p_sob_id NUMBER,
280 p_start_date DATE,
281 p_end_date DATE) IS
282 SELECT gld.ROWID,
283 Decode(gld.account_set_flag, 'Y', -100,
284 Decode(l.interface_line_context, g_om_context,
285 Decode(l.sales_order, NULL, -98,
286 Decode(l.sales_order_line, NULL, -97,
287 Decode(l.interface_line_attribute6, NULL, -96,
288 p_request_id))),-100)) cogs_request_id
289 FROM ra_customer_trx_lines_all l,
290 ra_cust_trx_line_gl_dist_all gld,
291 gl_date_period_map gl_map,
292 gl_sets_of_books gl_sob
293 WHERE gld.cogs_request_id IS NULL
294 AND gld.org_id = p_org_id
295 AND gld.account_class = 'REV'
296 AND gld.latest_rec_flag IS NULL
297 AND gld.gl_date = gl_map.accounting_date
298 AND gld.customer_trx_line_id = l.customer_trx_line_id
299 AND gl_sob.set_of_books_id = p_sob_id
300 AND gl_sob.period_set_name = gl_map.period_set_name
301 AND gl_sob.accounted_period_type = gl_map.period_type
302 AND gl_map.accounting_date BETWEEN p_start_date
303 AND p_end_date;
304
305 /* 8334354 - changed from sob to org/sob */
306 /* 10029320 - org_id 0 is legit.. and not seeded. */
307 CURSOR ar_operations(p_sob_id NUMBER) IS
308 SELECT org_id, set_of_books_id
309 FROM ar_system_parameters_all
310 WHERE set_of_books_id = NVL(p_sob_id, set_of_books_id)
311 AND set_of_books_id > 0
312 AND org_id >= 0;
313
314 /* Diagnostic - dump of bad rows */
315 CURSOR bad_rows(p_org_id NUMBER,
316 p_from_date DATE,
317 p_to_date DATE) IS
318 SELECT cogs_request_id, count(*) error_count,
319 DECODE(cogs_request_id, -100, 'model or non-OM',
320 -99, 'corrupt or missing line',
321 -98, 'null sales_order',
322 -97, 'null sales_order_line',
323 -96, 'null int_attr6 col') meaning
324 FROM ra_cust_trx_line_gl_dist_all
325 WHERE org_id = p_org_id
326 AND cogs_request_id BETWEEN -98 AND -1
327 AND gl_date BETWEEN p_from_date AND p_to_date
328 AND account_class = 'REV'
329 AND latest_rec_flag IS NULL
330 GROUP BY cogs_request_id;
331
332 /* debug - dump content of GT table */
333 CURSOR trx_gt IS
334 SELECT *
335 FROM ar_trx_cogs_gt;
336
337 BEGIN
338
339 IF PG_DEBUG in ('Y', 'C') THEN
340 arp_debug.debug( 'ar_match_rev_cogs_grp.populate_cst_tables()+ ');
341 arp_debug.debug(' p_ledger_id = ' || p_ledger_id);
342 arp_debug.debug(' p_gl_date = ' || p_from_gl_date ||
343 ' to ' || p_to_gl_date);
344
345 END IF;
346
347 -- Standard Start of API savepoint
348 SAVEPOINT populate_cst_tables_grp;
349
350 -- Standard call to check for call compatibility.
351 IF NOT FND_API.Compatible_API_Call (
352 p_current_version_number => l_api_version,
353 p_caller_version_number => p_api_version,
354 p_api_name => l_api_name,
355 p_pkg_name => g_pkg_name) THEN
356
357 RAISE fnd_api.g_exc_unexpected_error;
358
359 END IF;
360
361 -- Initialize message list if p_init_msg_list is set to TRUE.
362 IF FND_API.to_Boolean( p_init_msg_list ) THEN
363 fnd_msg_pub.initialize;
364 END IF;
365
366 -- Initialize API return status to success
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
371 l_user_id := fnd_global.user_id;
368
369 -- Populate WHO column variables
370 l_request_id := fnd_global.conc_request_id;
372 l_login_id := fnd_global.login_id;
373 l_pgm_app_id := fnd_global.prog_appl_id;
374 l_pgm_id := fnd_global.conc_program_id;
375
376 /* 8821317 - store original values so we can restore them later */
377 l_original_org := mo_global.get_current_org_id;
378 l_original_context := mo_global.get_access_mode;
379
380 IF PG_DEBUG in ('Y', 'C') THEN
381 arp_debug.debug(' cogs_request_id = ' || l_request_id);
382 END IF;
383
384 /* PROGRAM FLOW
385 This program now executes in 4 steps or stages. They are:
386 step 1 - tag gl_dist rows with cogs_request_id
387 Only tags REV rows with request_id if they are for a sales order.
388 Non sales order REV lines get a specific negative int (see below).
389 Currently fetches only 1000 rows at a time.
390 step 2 - collect unique sales orders for processing
391 step 3 - populate ar_trx_cogs_gt table with summarization of data
392 step 4 - merge data in cst_revenue_recognition_lines
393
394 The detailed flow looks like this:
395
396 FOR org in orgs(by sob_id) LOOP
397 LOOP
398 Open/Fetch data from gl_dist table
399 - If no gl_dist rows, goto next_org
400 Exit loop if no rows
401 Update gl_dist table with cogs_request_id
402 END LOOP
403
404 LOOP
405 Open/Fetch unique sales orders
406 - If no orders, goto next_org
407
408 Insert into ar_trx_cogs_gt
409 END LOOP
410
411 Merge into cst_revenue_recognition_lines
412 <<next_org>>
413 END LOOP
414 */
415
416 FOR c_org IN ar_operations(p_ledger_id)
417 LOOP
418
419 IF PG_DEBUG in ('Y', 'C') THEN
420 arp_debug.debug('Processing org_id =' || c_org.org_id || ', sob_id = ' ||
421 c_org.set_of_books_id);
422 END IF;
423
424 /* Step 1 - Tag eligible gl_dist rows based on date range.
425 This results in all gl_dist rows for this ledger and date
426 range having a value stamped in cogs_request_id. The value
427 will be the request_id of this job for REV dists on OE lines,
428 or one of the following values:
429
430 +ve : request_id of the COGS job that identified this row
431 -100 : model distribution (account_set_flag = Y)
432 or non-OM transaction
433 -99 : REC, ROUND, or other misjoined distributions
434 -98 : OM line, but sales_order is NULL
435 -97 : OM line, but sales_order_line is NULL
436 -96 : OM line, but interface_line_attr6 is NULL
437
438 So -96, -97, and -98 values indicate some malformed OM data.
439 -99 could contain orphan dists or other mystery rows that did not
440 maintain normal AR data integrity.
441 */
442
443 /* Loop and bulk fetch blocks of records */
444 l_last_fetch := FALSE;
445 OPEN gld_rows_c(l_request_id, c_org.org_id,
446 c_org.set_of_books_id, p_from_gl_date, p_to_gl_date);
447 LOOP
448
449 /* fetch gld rows for updating cogs_request_id */
450 FETCH gld_rows_c BULK COLLECT INTO gld_rowids, gld_cogs_request_ids
451 LIMIT g_bulk_fetch_rows;
452
453 l_rows := gld_rows_c%ROWCOUNT;
454
455 IF gld_rows_c%NOTFOUND
456 THEN
457 l_last_fetch := TRUE;
458 END IF;
459
460 IF PG_DEBUG in ('Y', 'C')
461 THEN
462 arp_debug.debug(' fetched ' || gld_rowids.count ||
463 ' distinct gld row(s) for update.');
464 END IF;
465
466 /* If no rows at all, skip to next org */
467 IF l_rows = 0
468 THEN
469 IF PG_DEBUG in ('Y','C')
470 THEN
471 arp_debug.debug(' skipping to next org');
472 END IF;
473 CLOSE gld_rows_c; -- need to close it before we skip
474 goto next_org;
475 END IF;
476
477 /* If last fetch, then exit fetch loop */
478 IF gld_rowids.count = 0 AND l_last_fetch
479 THEN
480 IF PG_DEBUG in ('Y','C')
481 THEN
482 arp_debug.debug(' last fetch for this org. Exiting fetch loop');
483 END IF;
484 EXIT;
485 END IF;
486
487 /* 8821317 - ra_cust_trx_line_gl_dist_bri trigger raising
488 unexpected error during trigger execution. Setting org
489 avoids error */
490 mo_global.set_policy_context('S',c_org.org_id);
491
492 /* Now update the gld rows with cogs_request_ids */
493 FORALL i in 1 .. gld_rowids.count
494 UPDATE ra_cust_trx_line_gl_dist_all gld
495 SET cogs_request_id = gld_cogs_request_ids(i)
496 WHERE rowid = gld_rowids(i);
497
498 IF PG_DEBUG in ('Y', 'C')
499 THEN
500 l_rows := SQL%ROWCOUNT;
501 arp_debug.debug(' updated ' || l_rows ||
502 ' distinct gld row(s).');
503 END IF;
504
505 END LOOP;
506 /* End of loop for bulk fetch */
507
508 CLOSE gld_rows_c;
509
510 /* Step 2 - Identify the unique sales orders that affect or
511 are affected in the specified GL_DATE range. */
512
513 /* NOTE: The question has come up as to why we use sales_order..
514 The answer.. the original order and price adjustment lines will
515 bear the sales order number. Additionally, RMAs for that trx
516 will also come in with different values in ILA6, but the same
517 value for sales_order. So it was the obvious (and easiest)
518 way to group related transactions from OM */
519
520 /* 7291422 - we now drive from the flagged gl_dist rows in
521 step 1 to identify the sales orders. While I am not
522 certain it will make things better, it certainly simplifies the
523 sql for future tuning efforts. */
524
525 OPEN so_numbers_c(l_request_id, c_org.org_id,
526 c_org.set_of_books_id, p_from_gl_date, p_to_gl_date);
527
528 LOOP
529 FETCH so_numbers_c BULK COLLECT INTO so_numbers, so_orgs
530 LIMIT g_bulk_fetch_rows;
531
532 l_so_rows := so_numbers_c%ROWCOUNT;
533
534 /* If no rows found (last search), then exit loop */
535 IF so_numbers.count = 0 and
536 so_numbers_c%NOTFOUND
537 THEN
538 EXIT;
539 END IF;
540
541 /* Test total rows returned, if zero, then nothing to process
542 in this org.. go to next one */
543 IF l_so_rows = 0
544 THEN
545 IF PG_DEBUG in ('Y','C')
546 THEN
547 arp_debug.debug(' no sales orders to process - skip to next org');
548 END IF;
549 CLOSE so_numbers_c;
550 goto next_org;
551 END IF;
552
553 /* Rows found, insert into ar_trx_cogs_gt */
554
555 /* Step 3 - populate AR_TRX_COGS_GT with summarized data. Data is
556 recorded per invoice line and GL period. The potential revenue
557 (revenue_line_amount) is recorded in each period that a transaction
558 line effects but it must be considered only once in creating
559 the divisor for the final revenue percentage. This is currently
560 handled via a function call */
561
562 FORALL i in 1 .. so_numbers.count
563 INSERT INTO ar_trx_cogs_gt
564 ( customer_trx_id,
565 customer_trx_line_id,
566 previous_customer_trx_line_id,
567 so_line_id,
568 period_number,
569 revenue_dist_amount,
570 revenue_line_amount,
571 latest_gl_date,
572 org_id,
573 set_of_books_id
574 )
575 SELECT /*+ ORDERED */
576 tl.customer_trx_id,
577 tl.customer_trx_line_id,
578 tl.previous_customer_trx_line_id,
579 to_number(
580 decode(tl.previous_customer_trx_line_id, NULL,
581 tl.interface_line_attribute6,
582 tli.interface_line_attribute6)),
583 gps.effective_period_num,
584 sum(tlgld.amount), -- revenue_dist_amount
585 sum(tlgld.percent),-- revenue_line_amount (storing rev percent)
586 MAX(tlgld.gl_date),-- latest_gl_date
587 tl.org_id,
588 tl.set_of_books_id
589 FROM ra_customer_trx_lines_all tl,
590 ra_customer_trx_lines_all tli,
591 ra_cust_trx_line_gl_dist_all tlgld,
592 gl_period_statuses gps
593 WHERE
594 tl.sales_order = so_numbers(i)
595 AND tl.org_id = so_orgs(i)
596 AND tl.customer_trx_line_id = tlgld.customer_trx_line_id
597 AND tlgld.account_set_flag = 'N'
598 AND tlgld.account_class = 'REV'
599 AND tl.previous_customer_trx_line_id = tli.customer_trx_line_id (+)
600 AND tl.interface_line_context = g_om_context -- 7349970
601 AND NVL(tli.interface_line_context,tl.interface_line_context) =
602 g_om_context
603 AND NVL(tli.interface_line_attribute6, tl.interface_line_attribute6)
604 IS NOT NULL
605 AND NVL(tli.sales_order_line, tl.sales_order_line)
606 IS NOT NULL -- 7349970
607 AND gps.set_of_books_id = tl.set_of_books_id
608 AND gps.application_id = 222
609 AND gps.adjustment_period_flag = 'N'
610 AND tlgld.gl_date between gps.start_date and gps.end_date
611 AND NVL(LENGTH(REPLACE(TRANSLATE(
612 DECODE(tl.previous_customer_trx_line_id, NULL,
613 tl.interface_line_attribute6, tli.interface_line_attribute6),
614 '123456789','0000000000'),'0','')),0) = 0
615 GROUP BY
616 tl.customer_trx_id, tl.customer_trx_line_id,
617 tl.previous_customer_trx_line_id,
618 to_number(
619 decode(tl.previous_customer_trx_line_id, NULL,
620 tl.interface_line_attribute6,
621 tli.interface_line_attribute6)),
622 gps.effective_period_num, tl.revenue_amount,
623 tl.org_id, tl.set_of_books_id;
624
625 l_so_rows_inserted := SQL%ROWCOUNT;
626
627 IF PG_DEBUG in ('Y','C')
628 THEN
629 arp_debug.debug(' inserted ' || l_so_rows_inserted ||
630 ' row(s) into ar_trx_cogs_gt');
631 END IF;
632
633 END LOOP;
634
635 CLOSE so_numbers_c;
636
637 IF PG_DEBUG in ('Y', 'C')
638 THEN
639 arp_debug.debug(' processed ' || l_so_rows ||
640 ' distinct orders.');
641 END IF;
642
643 /* debug logic +/
644 IF PG_DEBUG in ('Y', 'C')
645 THEN
646 arp_debug.debug('start - dump of ar_trx_cogs_gt');
647 FOR gt IN trx_gt LOOP
648 arp_debug.debug(gt.customer_trx_id || '~' ||
649 gt.customer_trx_line_id || '~' ||
650 gt.previous_customer_trx_line_id || '~' ||
651 gt.so_line_id);
652 END LOOP;
653 arp_debug.debug('end - dump of ar_trx_cogs_gt');
654 END IF;
655 /+ end - debug logic */
656
657 IF PG_DEBUG in ('Y', 'C') THEN
658 arp_debug.debug(c_org.org_id ||
659 ': completed..' );
660 END IF;
661
662 /* 7291422 - Diagnostics to identify missed or bad orders */
663 IF PG_DEBUG in ('Y','C')
664 THEN
665 FOR c_problem_rows IN bad_rows(c_org.org_id,
666 p_from_gl_date, p_to_gl_date)
667 LOOP
668 arp_debug.debug(c_problem_rows.meaning || '(' ||
669 c_problem_rows.cogs_request_id || ') count=' ||
670 c_problem_rows.error_count);
671 END LOOP;
672 END IF;
673
674 <<next_org>>
675 NULL;
676 END LOOP; -- c_org
677
678 /* 8821317 - retore org and context to original values */
679 mo_global.set_policy_context(l_original_context, l_original_org);
680
681 /* Step 4 - Now merge the resulting data into cst_reve_rec_lines.
682 NOTE: We are using the function potential_revenue() to fetch
683 an accumulated total for the equation below. This can probably be
684 replaced with some sort of analytical function when time allows.
685
686 NOTE: The rev_percent calculation is actually a matrix
687 of return values depending on the zero or non-zero state
688 of the numerator and denominator in the actual calc
689
690 Num
691
692 0 !0
693 +
694 0 1 | 1
695 Den +----+---
696 !0 0 | Num/Den
697
698 What this means is that if the denominator (sum of line
699 revenue_amounts) is zero, we always return a 1 (100%).
700 If the denominator is not 0, then we do the calculation
701 of Numerator (sum of rev dist amounts) / denominator.
702 In cases where the numerator is zero and the denominator
703 is not zero, the calc would return zero so we skip it and
704 just return zero directly.
705
706 10230957 - Revised logic for zero amount lines. Users can
707 now earn and unearn zero lines. The trigger for whether these
708 are earned or not earned is the sum of the REV percents.
709 Technically, they will either be 100% or zero %. If zero,
710 we send zero to CST, and otherwise, 100%.
711
712 NOTE: The percent is stored temporarily in the
713 revenue_line_amount column of the GT table.
714 */
715
716 IF PG_DEBUG in ('Y','C')
717 THEN
718 arp_debug.debug(' Merging into cst_revenue_recognition_lines');
719 END IF;
720
721 MERGE INTO cst_revenue_recognition_lines crrl
722 USING
723 (
724 SELECT
725 rev.so_line_id,
726 max(rev.latest_gl_date) gl_date,
727 gps.effective_period_num period_number,
728 DECODE(ar_match_rev_cogs_grp.potential_revenue(
729 rev.so_line_id,gps.effective_period_num),0,
730 DECODE(SUM(rev.revenue_line_amount),0,0,1),
731 DECODE(SUM(rev.revenue_dist_amount),0,0,
732 ROUND(SUM(rev.revenue_dist_amount) /
733 ar_match_rev_cogs_grp.potential_revenue(rev.so_line_id,
734 gps.effective_period_num),4)))
735 rev_percent,
736 max(rev.org_id) org_id,
737 gps.set_of_books_id set_of_books_id
738 FROM ar_trx_cogs_gt rev,
739 gl_period_statuses gps
740 WHERE gps.application_id = 222
741 AND gps.set_of_books_id = rev.set_of_books_id
742 AND gps.ledger_id = p_ledger_id
743 AND gps.start_date <= p_to_gl_date
744 AND gps.adjustment_period_flag = 'N'
745 AND rev.period_number <= gps.effective_period_num
746 GROUP BY rev.so_line_id, gps.effective_period_num,
747 gps.set_of_books_id, gps.start_date, gps.end_date
748 HAVING max(rev.latest_gl_date) between
749 gps.start_date AND gps.end_date
750 ) Q
751 ON (Q.so_line_id = crrl.revenue_om_line_id AND
752 Q.period_number = crrl.acct_period_num)
753 WHEN MATCHED THEN
754 UPDATE SET
755 revenue_recognition_percent = Q.rev_percent,
756 last_event_date = Q.gl_date,
757 potentially_unmatched_flag =
758 DECODE(revenue_recognition_percent, Q.rev_percent,
759 potentially_unmatched_flag,'Y'),
760 request_id =
761 DECODE(revenue_recognition_percent, Q.rev_percent,
762 request_id,l_request_id)
763 WHEN NOT MATCHED THEN
764 INSERT (revenue_om_line_id,
765 acct_period_num,
766 revenue_recognition_percent,
767 last_event_date,
768 operating_unit_id,
769 ledger_id,
770 customer_trx_line_id,
771 potentially_unmatched_flag,
772 last_update_date,
773 last_updated_by,
774 creation_date,
775 created_by,
776 last_update_login,
777 request_id,
778 program_application_id,
779 program_id,
780 program_update_date)
781 VALUES (Q.so_line_id,
782 Q.period_number,
783 Q.rev_percent,
784 Q.gl_date,
785 Q.org_id,
786 Q.set_of_books_id,
787 NULL,
788 'Y',
789 sysdate,
790 l_user_id,
791 sysdate,
792 l_user_id,
793 l_login_id,
794 l_request_id,
795 l_pgm_app_id,
796 l_pgm_id,
797 sysdate
798 );
799
800 FND_MSG_PUB.count_and_get
801 ( p_count => x_msg_count
802 , p_data => x_msg_data
803 );
804
805 IF PG_DEBUG in ('Y', 'C') THEN
806 arp_debug.debug( 'ar_match_rev_cogs_grp.populate_cst_tables()- ');
807 END IF;
808
809 EXCEPTION
810 WHEN fnd_api.g_exc_error THEN
811 ROLLBACK TO populate_cst_tables_grp;
812 x_return_status := FND_API.G_RET_STS_ERROR ;
813 fnd_msg_pub.count_and_get (
814 p_encoded => fnd_api.g_false,
815 p_count => x_msg_count,
816 p_data => x_msg_data);
817
818 WHEN fnd_api.g_exc_unexpected_error THEN
819 ROLLBACK TO populate_cst_tables_grp;
820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
821 fnd_msg_pub.count_and_get (
822 p_encoded => fnd_api.g_false,
823 p_count => x_msg_count,
824 p_data => x_msg_data);
825
826 WHEN OTHERS THEN
827 IF (SQLCODE = -20001) THEN
828 ROLLBACK TO populate_cst_tables_grp;
829 x_return_status := FND_API.G_RET_STS_ERROR ;
830 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
831 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','ar_match_rev_cogs_grp.populate_cst_tables : '||SQLERRM);
832 FND_MSG_PUB.Add;
833
834 fnd_msg_pub.count_and_get (
835 p_encoded => fnd_api.g_false,
836 p_count => x_msg_count,
837 p_data => x_msg_data);
838 RETURN;
839 ELSE
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
841 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
842 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','ar_match_rev_cogs_grp.populate_cst_tables : '||SQLERRM);
843 FND_MSG_PUB.Add;
844 END IF;
845
846 IF PG_DEBUG in ('Y', 'C') THEN
847 arp_debug.debug( SQLCODE);
848 arp_debug.debug( SQLERRM);
849 END IF;
850
851 ROLLBACK TO populate_cst_tables_grp;
852 fnd_msg_pub.count_and_get (
853 p_encoded => fnd_api.g_false,
854 p_count => x_msg_count,
855 p_data => x_msg_data);
856
857 END populate_cst_tables;
858
859 BEGIN
860 g_om_context := NVL(fnd_profile.value('ONT_SOURCE_CODE'),'###NOT_SET###');
861 END ar_match_rev_cogs_grp;
[Home] [Help]