1 PACKAGE BODY arrx_cogs_rep_inner AS
2 /* $Header: ARRXRCGB.pls 120.1 2005/10/30 04:45:55 appldev noship $ */
3
4
5 /*========================================================================
6 | Package Global Variables
7 +=======================================================================*/
8
9 pg_debug varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
10
11 TYPE number_table IS TABLE OF NUMBER
12 INDEX BY BINARY_INTEGER;
13
14 TYPE varchar_table IS TABLE OF VARCHAR2(240)
15 INDEX BY BINARY_INTEGER;
16
17 FUNCTION get_cost(
18 p_account_class VARCHAR2,
19 p_rec_offset_flag VARCHAR2,
20 p_line_id NUMBER,
21 p_base_transaction_value NUMBER) RETURN NUMBER IS
22
23 CURSOR rev_rows IS
24 SELECT count(*)
25 FROM ra_cust_trx_line_gl_dist_all
26 WHERE customer_trx_line_id = p_line_id
27 AND account_set_flag = 'N'
28 AND account_class = 'REV';
29
30 l_cost NUMBER DEFAULT 0;
31 l_count NUMBER DEFAULT 0;
32
33 BEGIN
34
35 -- This routine figures out how many REV dist lines exists
36 -- and depending on the count would figure out if the cost
37 -- should be added to the total or not.
38 -- case 1 - The account class for the current row is UNEARN and there
39 -- exists no REV rows then count the cost. Otherwise
40 -- the cost would show up as zero .
41 -- case 2 - There are rev rows and the accont class for the current
42 -- row is UNEARN then ignore it.
43 -- case 3 - For all other scenario go ahead and count the cost.
44
45 IF (p_account_class = 'UNEARN') THEN
46 IF (p_rec_offset_flag = 'Y') THEN
47 OPEN rev_rows;
48 FETCH rev_rows INTO l_count;
49 CLOSE rev_rows;
50 IF (l_count = 0) THEN
51 RETURN p_base_transaction_value;
52 END IF;
53 END IF;
54 ELSE -- 'REV' entry
55 RETURN p_base_transaction_value;
56 END IF;
57
58 RETURN 0;
59
60 END get_cost;
61
62
63 PROCEDURE populate_description IS
64
65 l_dummy BOOLEAN;
66 l_description ar_cogs_rev_itf.cogs_acct_description%TYPE;
67
68 -- Please note that now description has code_combination_id
69 --
70 CURSOR getdesc IS
71 SELECT cgs.cogs_acct_description code_combination_id,
72 gcc.chart_of_accounts_id
73 FROM ar_cogs_rev_itf cgs, gl_code_combinations gcc
74 WHERE cgs.cogs_acct_description = gcc.code_combination_id;
75
76 BEGIN
77
78 -- Bug # 3840430
79 -- The description in gl_code_combinations is not the right one.
80 -- To get the description we need to call the function
81 -- fnd_flex_keyval.concatenated_descriptions however this will only
82 -- work if the cc id was validated just before that. To do that
83 -- we need the code_combination_id which is not stored in the
84 -- ar_cogs_rev_itf table. So, we are overloading the description
85 -- column temporariliy to store the code_combination_id and here
86 -- we will update it with the corresponding description.
87
88 fnd_file.put_line(fnd_file.log, 'populate_description');
89
90 FOR rec IN getdesc LOOP
91
92 l_dummy := fnd_flex_keyval.validate_ccid(
93 'SQLGL',
94 'GL#',
95 rec.chart_of_accounts_id,
96 rec.code_combination_id);
97 l_description := fnd_flex_keyval.concatenated_descriptions;
98
99 fnd_file.put_line(fnd_file.log, 'CC ID: ' || rec.code_combination_id);
100 fnd_file.put_line(fnd_file.log, 'Description: ' || l_description);
101
102 UPDATE ar_cogs_rev_itf
103 SET cogs_acct_description = l_description
104 WHERE cogs_acct_description = rec.code_combination_id;
105
106 END LOOP;
107
108 END populate_description;
109
110
111 PROCEDURE populate_rows (
112 p_gl_date_low IN DATE,
113 p_gl_date_high IN DATE,
114 p_sales_order_low IN VARCHAR2 DEFAULT NULL,
115 p_sales_order_high IN VARCHAR2 DEFAULT NULL,
116 p_posted_lines_only IN VARCHAR2 DEFAULT NULL,
117 p_unmatched_items_only IN VARCHAR2 DEFAULT NULL,
118 p_user_id IN NUMBER,
119 p_request_id IN NUMBER,
120 x_retcode OUT NOCOPY NUMBER,
121 x_errbuf OUT NOCOPY VARCHAR2) IS
122
123 l_posting_control_id NUMBER DEFAULT NULL;
124 l_gl_batch_id NUMBER DEFAULT NULL;
125 l_precision fnd_currencies.precision%TYPE;
126
127 CURSOR precision IS
128 SELECT cur.precision
129 FROM gl_sets_of_books sob,
130 fnd_currencies cur
131 WHERE sob.currency_code = cur.currency_code
132 AND sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
133
134 BEGIN
135
136 -- This routine populate the interface table so the detail RXi report
137 -- can publish the report based on the data in the interface table.
138
139 IF pg_debug in ('Y', 'C') THEN
140 fnd_file.put_line(fnd_file.log, 'ARRX_COGS_REP_INNER.POPULATE_ROWS()+');
141 END IF;
142
143 fnd_file.put_line(fnd_file.log, 'request id: ' || p_request_id);
144 fnd_file.put_line(fnd_file.log, 'user id : ' || p_user_id);
145 fnd_file.put_line(fnd_file.log, 'low gl date: ' || p_gl_date_low);
146 fnd_file.put_line(fnd_file.log, 'high gl date: ' || p_gl_date_high);
147 fnd_file.put_line(fnd_file.log, 'low sales order: ' || p_sales_order_low);
148 fnd_file.put_line(fnd_file.log, 'high sales order: ' || p_sales_order_high);
149 fnd_file.put_line(fnd_file.log, 'posted lines only: ' ||
150 p_posted_lines_only);
151 fnd_file.put_line(fnd_file.log, 'unmatched items only: ' ||
152 p_posted_lines_only);
153
154 -- In order to round the amount columns we must figure how many places
155 -- we must round. That is being determined here by looking at the
156 -- currency precision of the set of books.
157
158 OPEN precision;
159 FETCH precision INTO l_precision;
160 CLOSE precision;
161
162 -- start out with a fresh table.
163 DELETE FROM ar_cogs_rev_itf;
164
165 -- respond to the user preference set in the program parameter.
166 -- if yes then exclude lines with posting control id of -3
167
168 IF p_posted_lines_only = 'Y' THEN
169 l_posting_control_id := -3;
170 l_gl_batch_id := -1;
171 END IF;
172
173 fnd_file.put_line(fnd_file.log, 'table being populated with selected rows');
174
175 -- the commented columns are populated later for better readability.
176
177 INSERT INTO ar_cogs_rev_itf
178 (
179 request_id,
180 created_by,
181 creation_date,
182 last_updated_by,
183 last_update_date,
184 last_update_login,
185 set_of_books_id,
186 cogs_gl_account,
187 cogs_acct_description,
188 customer_name,
189 sales_order_type,
190 sales_order,
191 sales_order_line,
192 trx_class,
193 trx_number,
194 trx_line_number,
195 order_amount_orig,
196 cogs_amount_orig,
197 cogs_amount_period,
198 rev_amount_period
199 )
200 SELECT
201 p_request_id request_id,
202 p_user_id create_by,
203 sysdate creation_date,
204 p_user_id last_udpated_by,
205 sysdate last_update_date,
206 p_user_id last_update_login,
207 arp_standard.sysparm.set_of_books_id,
208 MAX(fnd_flex_ext.get_segs(
209 'SQLGL',
210 'GL#',
211 gcc.chart_of_accounts_id,
212 gcc.code_combination_id)) cogs_account,
213 MAX(gcc.code_combination_id) description,
214 party.party_name customer,
215 lines.interface_line_attribute2 order_type,
216 lines.interface_line_attribute1 order_num,
217 lines.sales_order_line sales_order_line,
218 trx_type.type trx_class,
219 trx.trx_number trx_number,
220 lines.line_number trx_line_number,
221 ROUND((SUM(lines.revenue_amount)/
222 count(dist.cust_trx_line_gl_dist_id)), l_precision) orig_revenue,
223 SUM
224 ( arrx_cogs_rep_inner.get_cost(
225 dist.account_class,
226 dist.rec_offset_flag,
227 lines.customer_trx_line_id,
228 mta.base_transaction_value)) orig_cost,
229 ROUND((SUM
230 (
231 DECODE
232 (
233 (DECODE(sign(mmt.transaction_date -fnd_date.chardate_to_date(p_gl_date_low)), -1, 0, 1)
234 +
235 DECODE(sign(mmt.transaction_date -fnd_date.chardate_to_date(p_gl_date_high)), 1, 0, 1)
236 ),
237 2, arrx_cogs_rep_inner.get_cost(
238 dist.account_class,
239 dist.rec_offset_flag,
240 lines.customer_trx_line_id, mta.base_transaction_value), 0
241 )
242 )), l_precision) cost,
243 ROUND((SUM
244 (
245 DECODE
246 (
247 (
248 DECODE(sign(dist.gl_date - fnd_date.chardate_to_date(p_gl_date_low)), -1, 0, 1)
249 +
250 DECODE(sign(dist.gl_date - fnd_date.chardate_to_date(p_gl_date_high)), 1, 0, 1)
251 ),
252 2, DECODE(dist.account_class, 'UNEARN', 0, dist.acctd_amount), 0
253 )
254 ))/count(DISTINCT mmt.transaction_id), l_precision) revenue
255 FROM ra_cust_trx_line_gl_dist dist,
256 ra_customer_trx_lines lines,
257 mtl_material_transactions mmt,
258 mtl_transaction_accounts mta,
259 ra_customer_trx trx,
260 hz_cust_accounts acct,
261 hz_parties party,
262 mtl_system_items_b msi,
263 ra_cust_trx_types trx_type,
264 gl_code_combinations gcc,
265 cst_item_costs cic,
266 mtl_parameters mp
267 WHERE dist.customer_trx_line_id = lines.customer_trx_line_id
268 AND dist.account_set_flag = 'N'
269 AND dist.account_class IN ('REV', 'UNEARN')
270 AND lines.customer_trx_id = trx.customer_trx_id
271 AND lines.inventory_item_id = msi.inventory_item_id
272 AND lines.interface_line_attribute10 = msi.organization_id
273 AND lines.line_type = 'LINE'
274 AND lines.interface_line_context = 'ORDER ENTRY'
275 AND lines.interface_line_attribute6 = mmt.trx_source_line_id
276 AND mmt.transaction_source_type_id IN (2, 12)
277 AND mmt.transaction_action_id IN (1, 27)
278 AND mmt.costed_flag IS NULL
279 AND mmt.organization_id = msi.organization_id
280 AND mmt.inventory_item_id = msi.inventory_item_id
281 AND mmt.transaction_id = mta.transaction_id
282 AND mta.accounting_line_type <> 2
283 AND msi.inventory_item_id = cic.inventory_item_id
284 AND msi.organization_id = cic.organization_id
285 AND msi.organization_id = mp.organization_id
286 AND cic.cost_type_id = mp.primary_cost_method
287 AND msi.shippable_item_flag = 'Y'
288 AND msi.costing_enabled_flag = 'Y'
289 AND msi.invoiceable_item_flag = 'Y'
290 AND msi.invoice_enabled_flag = 'Y'
291 AND cic.inventory_asset_flag = 1
292 AND trx.cust_trx_type_id = trx_type.cust_trx_type_id
293 AND trx_type.type IN ('INV', 'CM')
294 AND trx.bill_to_customer_id = acct.cust_account_id
295 AND acct.party_id = party.party_id
296 AND mta.reference_account = gcc.code_combination_id
297 AND dist.gl_date
298 BETWEEN p_gl_date_low AND p_gl_date_high
299 AND lines.interface_line_attribute1
300 BETWEEN NVL(p_sales_order_low, lines.interface_line_attribute1) AND
301 NVL(p_sales_order_high, lines.interface_line_attribute1)
302 AND dist.posting_control_id <> NVL(l_posting_control_id, -99999999999)
303 AND mta.gl_batch_id <> NVL(l_gl_batch_id, -99999999999)
304 GROUP BY mta.reference_account,
305 party.party_name,
306 lines.interface_line_attribute2,
307 lines.interface_line_attribute1,
308 trx_type.type,
309 trx.trx_number,
310 lines.sales_order_line,
311 lines.interface_line_attribute6,
312 lines.line_number;
313
314 fnd_file.put_line(fnd_file.log, 'table populated with selected rows');
315
316 --
317 -- just for better readability I am computing the percentages
318 -- and actual adjustment needed separately here. the formula for
319 -- cogs adjustment is: (%rev - %cogs) * cogs_amount_orig
320 --
321
322 UPDATE ar_cogs_rev_itf
323 SET rev_percent_period = ROUND((((rev_amount_period/order_amount_orig) *
324 DECODE(trx_class, 'CM', -100, 100))),
325 l_precision),
326 cogs_percent_period = ROUND(((cogs_amount_period/cogs_amount_orig)
327 * DECODE(trx_class, 'CM', -100, 100)),
328 l_precision),
329 cogs_adjustment = ROUND(((((rev_amount_period/order_amount_orig))
330 - (cogs_amount_period/cogs_amount_orig)) *
331 cogs_amount_orig), l_precision)
332 WHERE cogs_amount_orig <> 0
333 AND order_amount_orig <> 0;
334
335 -- Bug # 3840467
336 -- take care of null columns
337
338 UPDATE ar_cogs_rev_itf
339 SET rev_percent_period = 0,
340 cogs_percent_period = 0,
341 cogs_adjustment = 0
342 WHERE cogs_amount_orig = 0
343 AND order_amount_orig = 0;
344
345 fnd_file.put_line(fnd_file.log, 'update done');
346
347 -- respond to the user preference set in the program parameter.
348 -- this too could have been done in the main SQL, but
349 -- preferred to do this here for better readability and maintainablity.
350
351 IF p_unmatched_items_only = 'Y' THEN
352
353 DELETE FROM ar_cogs_rev_itf
354 WHERE rev_percent_period = cogs_percent_period;
355
356 fnd_file.put_line(fnd_file.log, 'deleting matched items');
357
358 END IF;
359
360 -- Bug # 3840430
361 populate_description;
362
363 fnd_file.put_line(fnd_file.log, 'description populated');
364
365 IF pg_debug in ('Y', 'C') THEN
366 fnd_file.put_line(fnd_file.log, 'ARRX_COGS_REP_INNER.POPULATE_ROWS()-');
367 END IF;
368
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 IF pg_debug in ('Y', 'C') THEN
372 arp_standard.debug('EXCEPTION: arrx_cogs_rep_inner.populate_rows()');
373 END IF;
374 RAISE;
375
376 WHEN OTHERS THEN
377 IF pg_debug in ('Y', 'C') THEN
378 arp_standard.debug('EXCEPTION: arrx_cogs_rep_inner.populate_rows()');
379 END IF;
380 RAISE;
381
382 END populate_rows;
383
384
385 PROCEDURE populate_summary (
386 p_gl_date_low IN DATE,
387 p_gl_date_high IN DATE,
388 p_chart_of_accounts_id IN NUMBER,
389 p_gl_account_low IN VARCHAR2 DEFAULT NULL,
390 p_gl_account_high IN VARCHAR2 DEFAULT NULL,
391 p_posted_lines_only IN VARCHAR2 DEFAULT NULL,
392 p_user_id IN NUMBER,
393 p_request_id IN NUMBER,
394 x_retcode OUT NOCOPY NUMBER,
395 x_errbuf OUT NOCOPY VARCHAR2) IS
396
397 l_precision fnd_currencies.precision%TYPE;
398 l_cc_id_low gl_code_combinations.code_combination_id%TYPE;
399 l_cc_id_high gl_code_combinations.code_combination_id%TYPE;
400
401 l_gl_acct_tbl varchar_table;
402 l_gl_acct_desc_tbl varchar_table;
403 l_cogs_adjustment_tbl number_table;
404
405 CURSOR precision IS
406 SELECT cur.precision
407 FROM gl_sets_of_books sob,
408 fnd_currencies cur
409 WHERE sob.currency_code = cur.currency_code
410 AND sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
411
412 CURSOR summary_rows IS
413 SELECT cogs_gl_account,
414 cogs_acct_description,
415 ROUND(SUM(cogs_adjustment), l_precision)
416 FROM ar_cogs_rev_itf
417 GROUP BY cogs_gl_account, cogs_acct_description;
418
419 BEGIN
420
421 -- This routine populate the interface table so the summary RXi report
422 -- can publish the report based on the data in the interface table.
423
424 fnd_file.put_line(fnd_file.log, 'ARRX_C_COGS_REP_INNER.POPULATE_SUMMARY()+');
425
426 IF pg_debug in ('Y', 'C') THEN
427 fnd_file.put_line(fnd_file.log, 'POPULATE_SUMMARY()+');
428 END IF;
429
430 fnd_file.put_line(fnd_file.log, 'request id: ' || p_request_id);
431 fnd_file.put_line(fnd_file.log, 'user id : ' || p_user_id);
432 fnd_file.put_line(fnd_file.log, 'low gl date: ' || p_gl_date_low);
433 fnd_file.put_line(fnd_file.log, 'high gl date: ' || p_gl_date_high);
434 fnd_file.put_line(fnd_file.log, 'chart of account id : ' ||
435 p_chart_of_accounts_id);
436 fnd_file.put_line(fnd_file.log, 'low gl account: ' || p_gl_account_low);
437 fnd_file.put_line(fnd_file.log, 'high gl account: ' || p_gl_account_high);
438 fnd_file.put_line(fnd_file.log, 'posted lines only: ' ||
439 p_posted_lines_only);
440
441 -- In order to round the amount columns we must figure how many places
442 -- we must round. That is being determined here by looking at the
443 -- currency precision of the set of books.
444
445 OPEN precision;
446 FETCH precision INTO l_precision;
447 CLOSE precision;
448
449 -- let the populate_rows do the work as far as fetching the detail rows
450 -- are concerned. Once that is done we can sum it up at the cogs account
451 -- level.
452
453 fnd_file.put_line(fnd_file.log, 'calling populate_rows');
454
455 populate_rows
456 (
457 p_gl_date_low => p_gl_date_low,
458 p_gl_date_high => p_gl_date_high,
459 p_sales_order_low => NULL,
460 p_sales_order_high => NULL,
461 p_posted_lines_only => p_posted_lines_only,
462 p_unmatched_items_only => 'Y',
463 p_user_id => p_user_id,
464 p_request_id => p_request_id,
465 x_retcode => x_retcode,
466 x_errbuf => x_errbuf
467 );
468
469 fnd_file.put_line(fnd_file.log, 'returned from populate_rows');
470
471 OPEN summary_rows;
472 FETCH summary_rows BULK COLLECT INTO
473 l_gl_acct_tbl,
474 l_gl_acct_desc_tbl,
475 l_cogs_adjustment_tbl;
476 CLOSE summary_rows;
477
478 fnd_file.put_line(fnd_file.log, 'clearing detail rows from the table');
479
480 -- remove the details rows and then populate summary rows.
481 DELETE FROM ar_cogs_rev_itf;
482
483 fnd_file.put_line(fnd_file.log, 'summary rows count: ' ||
484 l_gl_acct_tbl.COUNT);
485
486 FORALL i IN 1..l_gl_acct_tbl.COUNT
487 INSERT INTO ar_cogs_rev_itf
488 (
489 request_id,
490 created_by,
491 creation_date,
492 last_updated_by,
493 last_update_date,
494 last_update_login,
495 set_of_books_id,
496 cogs_gl_account,
497 cogs_acct_description,
498 cogs_adjustment
499 )
500 VALUES
501 (
502 p_request_id,
503 p_user_id,
504 sysdate,
505 p_user_id,
506 sysdate,
507 p_user_id,
508 arp_standard.sysparm.set_of_books_id,
509 l_gl_acct_tbl(i),
510 l_gl_acct_desc_tbl(i),
511 l_cogs_adjustment_tbl(i)
512 );
513
514 fnd_file.put_line(fnd_file.log, 'table populated with summary rows');
515
516 IF ((p_gl_account_low IS NOT NULL) OR (p_gl_account_low IS NOT NULL)) THEN
517
518 fnd_file.put_line(fnd_file.log, 'keep only the rows within acct range');
519
520 DELETE FROM ar_cogs_rev_itf
521 WHERE COGS_GL_ACCOUNT
522 NOT BETWEEN p_gl_account_low AND p_gl_account_high;
523
524 END IF;
525
526 IF pg_debug in ('Y', 'C') THEN
527 fnd_file.put_line(fnd_file.log, 'ARRX_COGS_REP_INNER.POPULATE_SUMMARY()-');
528 END IF;
529
530 EXCEPTION
531 WHEN NO_DATA_FOUND THEN
532 IF pg_debug in ('Y', 'C') THEN
533 arp_standard.debug('EXCEPTION: arrx_cogs_rep_inner.populate_rows()');
534 END IF;
535 RAISE;
536
537 WHEN OTHERS THEN
538 IF pg_debug in ('Y', 'C') THEN
539 arp_standard.debug('EXCEPTION: arrx_cogs_rep_inner.populate_rows()');
540 END IF;
541 RAISE;
542
543 END populate_summary;
544
545
546 /*========================================================================
547 | INITIALIZATION SECTION
548 |
549 | DESCRIPTION
550 |
551 *=======================================================================*/
552
553 BEGIN
554
555 NULL;
556
557 EXCEPTION
558 WHEN NO_DATA_FOUND THEN
559 arp_standard.debug('exception: arrx_cogs_rep_inner.initialize()');
560 RAISE;
561
562 WHEN OTHERS THEN
563 arp_standard.debug('exception: arrx_cogs_rep_inner.initialize()');
564 RAISE;
565
566 END arrx_cogs_rep_inner;
[Home] [Help]