[Home] [Help]
PACKAGE BODY: APPS.CSTPMRGL
Source
1 PACKAGE BODY CSTPMRGL AS
2 /* $Header: CSTMRGLB.pls 120.17.12020000.2 2013/03/25 06:46:14 pbasrani ship $ */
3
4 -- PROCEDURE
5 -- load_om_margin_data Loads Margin data for OM
6 --
7 procedure load_om_margin_data(
8 I_FROM_DATE IN VARCHAR2,
9 I_TO_DATE IN VARCHAR2,
10 I_OVERLAP_DAYS IN NUMBER,
11 I_LOAD_OPTION IN NUMBER,
12 I_USER_ID IN NUMBER,
13 I_TRACE_MODE IN VARCHAR2
14 ) IS
15 l_program_id NUMBER;
16 l_program_appl_id NUMBER;
17 l_request_id NUMBER;
18 l_stmt_id NUMBER;
19 l_build_id NUMBER;
20 l_first_build NUMBER;
21 l_from_date DATE;
22 l_to_date DATE;
23 l_last_load_date DATE;
24 errmsg VARCHAR2(2240);
25 l_le_id NUMBER;
26 l_ou_id NUMBER;
27 l_le_name VARCHAR2(240);-- ST bug 5202441
28 l_build_name VARCHAR2(255);
29 l_build_descr VARCHAR2(255);
30 app_col_name varchar2(50);
31 sql_stmt varchar2(5000);
32 OM_NOT_ACTIVE_ERROR EXCEPTION;
33 l_rowid rowid ;
34 l_cust_id NUMBER ;
35 l_cust_name VARCHAR2(240);
36 /*---------------------------------------------------------------+
37 | Get all Legal Entities
38 +---------------------------------------------------------------*/
39
40 CURSOR all_le is
41 SELECT distinct XFI.legal_entity_id,
42 XFI.name
43 FROM xle_firstparty_information_v XFI;
44
45 /*---------------------------------------------------------------+
46 | Get all Operating Units for a given Legal Entity
47 +---------------------------------------------------------------*/
48
49 CURSOR all_ous(c_le_id NUMBER) is
50 SELECT distinct hoi.organization_id
51 FROM hr_organization_information hoi
52 WHERE hoi.org_information2 = to_char(c_le_id)
53 AND hoi.org_information_context = 'Operating Unit Information';
54
55 /*-------------------------------------------------------------------+
56 | Bug#2383504.If Order is booked and shipped from two diff. OU belonging
57 | to the same LE then COGS to be reported against order OU. We need to
58 | update org_id for all COGS rows which have a different OU then the
59 | sales order's.
60 +--------------------------------------------------------------------*/
61
62 Cursor upd_org_cogs is
63 select distinct cms1.rowid , cms2.org_id
64 from CST_MARGIN_SUMMARY cms1 , CST_MARGIN_SUMMARY cms2
65 where cms2.source in ('INVOICE' , 'RMA-INVOICE')
66 and cms2.legal_entity_id = cms1.legal_entity_id
67 and cms2.header_id = cms1.header_id /* Added for bug# 5098340 */
68 and cms2.order_number = cms1.order_number
69 and cms2.line_number = cms1.line_number
70 and cms2.org_id <> cms1.org_id
71 and cms1.source in ('COGS' , 'RMA-COGS') -- dropship <
72 and NOT EXISTS
73 (SELECT 'X'
74 FROM mtl_intercompany_parameters
75 WHERE ship_organization_id = cms1.org_id
76 AND sell_organization_id = cms2.org_id
77 AND flow_type = 1); -- > dropship
78
79 CURSOR sold_to_cust(l_build_id NUMBER, l_from_date DATE , l_to_date DATE ) is
80 SELECT rowid , customer_id
81 FROM cst_margin_summary
82 WHERE build_id = l_build_id
83 AND gl_date between l_from_date and l_to_date
84 AND customer_id is not null ;
85
86
87 BEGIN
88 -- Initialize local variables
89
90 l_stmt_id := 0;
91 l_first_build := 0;
92 app_col_name := NULL;
93
94 OPEN all_le;
95
96 LOOP
97
98 -- DBMS_OUTPUT.ENABLE(100000);
99 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
100 -- DBMS_OUTPUT.PUT_LINE('ENTER LEGAL ENTITY LOOP.');
101 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
102
103 FETCH all_le into l_le_id,l_le_name;
104
105 EXIT WHEN all_le%NOTFOUND;
106
107 /* Setting values for "from date", "to date", and "overlap days" */
108
109 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
110 -- DBMS_OUTPUT.PUT_LINE('Get last update date');
111 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
112
113 select MAX(last_update_date), NVL(MAX(0),1), NVL(MAX(build_id),0)
114 into l_last_load_date, l_first_build, l_build_id
115 from CST_MARGIN_BUILD
116 where legal_entity_id = l_le_id;
117
118 -- DBMS_OUTPUT.PUT_LINE('l_last_load_date = ' || to_char(l_last_load_date));
119 -- DBMS_OUTPUT.PUT_LINE('l_first_build = ' || to_char(l_first_build));
120
121 l_from_date := fnd_date.canonical_to_date(i_from_date);
122 l_to_date := NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE);
123
124 if (l_first_build = 1) then
125
126 select NVL(fnd_date.canonical_to_date(i_from_date),to_date('1980/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')),
127 NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE)
131
128 into l_from_date,
129 l_to_date
130 from dual;
132 elsif (i_from_date is NULL) then
133 if (i_load_option = 1) then
134 l_from_date := to_date('1980/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS');
135 l_to_date := NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE);
136 else
137 l_from_date := l_last_load_date - i_overlap_days;
138 l_to_date := NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE);
139 end if;
140 end if;
141
142 Select trunc(l_from_date) , trunc(l_to_date)+ .99999
143 into l_from_date , l_to_date
144 from dual ;
145
146 -- DBMS_OUTPUT.PUT_LINE('l_le_name = ' || l_le_name);
147 -- DBMS_OUTPUT.PUT_LINE('l_from_date = ' || to_char(l_from_date));
148 -- DBMS_OUTPUT.PUT_LINE('l_to_date = ' || to_char(l_to_date));
149
150 /*---------------------------------------------------------------+
151 | Delete from CST_MARGIN_SUMMARY for the given Legal Entity
152 +---------------------------------------------------------------*/
153
154 BEGIN
155
156 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
157 -- DBMS_OUTPUT.PUT_LINE('DELETE from TEMP.');
158 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
159
160 DELETE from CST_MARGIN_SUMMARY
161 WHERE legal_entity_id = l_le_id
162 and gl_date between l_from_date and l_to_date;
163
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 l_program_id := NULL;
167 l_request_id := NULL;
168 l_program_appl_id := NULL;
169 WHEN OTHERS THEN
170 raise;
171 END;
172
173 /*---------------------------------------------------------------+
174 | Insert into CST_MARGIN_BUILD, if required
175 +---------------------------------------------------------------*/
176
177 BEGIN
178
179 if l_first_build = 1 THEN
180 SELECT cst_margin_build_s.nextval
181 INTO l_build_id
182 FROM sys.dual;
183
184 l_build_name := to_char(l_build_id);
185 l_build_descr := l_le_name;
186 END IF;
187
188
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 NULL;
192 WHEN OTHERS THEN
193 raise;
194 END;
195
196 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
197 -- DBMS_OUTPUT.PUT_LINE('INSERT into BUILD.');
198 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
199
200 if l_first_build = 1 THEN
201
202 INSERT INTO CST_MARGIN_BUILD (
203 build_id,
204 build_name,
205 build_description,
206 legal_entity_id,
207 legal_entity_name,
208 header_id,
209 org_id,
210 organization_id,
211 from_date,
212 to_date,
213 cost_type_id,
214 creation_date,
215 created_by,
216 last_update_date,
217 last_updated_by,
218 last_update_login,
219 request_id,
220 program_application_id,
221 program_id,
222 program_update_date
223 )
224 VALUES( l_build_id,
225 l_build_name,
226 l_build_descr,
227 l_le_id,
228 l_le_name,
229 NULL,
230 NULL,
231 l_le_id,
232 l_from_date,
233 l_to_date,
234 NULL,
235 SYSDATE,
236 i_user_id,
237 SYSDATE,
238 i_user_id,
239 i_user_id,
240 l_request_id,
241 l_program_appl_id,
242 l_program_id,
243 SYSDATE ) ;
244 else
245
246 UPDATE cst_margin_build
247 SET FROM_DATE = l_from_date,
248 TO_DATE = l_to_date,
249 LAST_UPDATE_DATE = SYSDATE
250 WHERE legal_entity_id = l_le_id;
251 END IF;
252
253 /* Do for each Operating unit for the above legal entity */
254
255 OPEN all_ous(l_le_id);
256
257 LOOP
258
259 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
260 -- DBMS_OUTPUT.PUT_LINE('ENTER OPERATING UNIT LOOP.');
261 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
262
263 FETCH all_ous into l_ou_id;
264
265 EXIT WHEN all_ous%NOTFOUND;
266
267 /*---------------------------------------------------------------+
268 | Insert into CST_MARGIN_SUMMARY for all the invoices booked
269 | against regular orders
270 +---------------------------------------------------------------*/
271
272 l_stmt_id := 30;
273
274 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
275 -- DBMS_OUTPUT.PUT_LINE('INSERT into TEMP.');
276 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
277
278 INSERT INTO CST_MARGIN_SUMMARY
279 (
280 build_id
281 ,source
282 ,row_type
283 ,origin
284 ,invoice_source
285 ,parent_rowid
286 ,order_number
287 ,header_id
288 ,legal_entity_id
289 ,org_id
290 ,order_type_id
291 ,customer_id
292 ,primary_salesrep_id
293 ,sales_channel_code
294 ,parent_inventory_item_id
295 ,parent_organization_id
299 ,parent_ato_flag
296 ,parent_line_id
297 ,parent_line_number
298 ,parent_item_type_code
300 ,inventory_item_id
301 ,organization_id
302 ,line_id
303 ,line_type_code
304 ,line_number
305 ,ship_to_site_use_id
306 ,invoice_to_site_use_id
307 ,customer_trx_id
308 ,customer_trx_line_id
309 ,original_gl_date
310 ,gl_date
311 ,invoice_line_quantity
312 ,invoice_quantity
313 ,invoiced_amount
314 ,sales_account
315 )
316 SELECT
317 l_build_id,
318 'INVOICE',
319 '1',
320 '1',
321 rctl.interface_line_context,
322 sl_parent.rowid,
323 sh.order_number,
324 sh.header_id,
325 l_le_id,
326 l_ou_id,
327 sh.order_type_id,
328 sh.sold_to_org_id,
329 sh.salesrep_id,
330 sh.sales_channel_code,
331 sl_parent.inventory_item_id,
332 sl_parent.ship_from_org_id,
333 sl_parent.line_id,
334 sl_parent.line_number,
335 sl_parent.item_type_code,
336 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
337 sl_child.inventory_item_id,
338 sl_child.ship_from_org_id,
339 sl_child.line_id,
340 sl_child.line_category_code,
341 sl_child.line_number,
342 sl_child.ship_to_org_id,
343 sh.invoice_to_org_id,
344 rct.CUSTOMER_TRX_ID,
345 rctl.CUSTOMER_TRX_LINE_ID,
346 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
347 rctlgd.gl_date,
348 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
349 rctl.quantity_invoiced, rctl.uom_code,
350 msi.primary_uom_code, TO_CHAR(NULL),
351 TO_CHAR(NULL)),
352 DECODE(NVL(rctl.interface_line_attribute11, '0'),
353 '0',
354 DECODE(rctl.inventory_item_id,
355 sl_parent.inventory_item_id,
356 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
357 rctl.quantity_invoiced,
358 rctl.uom_code,
359 msi.primary_uom_code,
360 TO_CHAR(NULL),
361 TO_CHAR(NULL)) * rctlgd.percent / 100,
362 0),
363 0),
364 rctlgd.acctd_amount,
365 rctlgd.code_combination_id
366 FROM
367 cst_margin_build cr,
368 ra_cust_trx_line_gl_dist_all rctlgd,
369 ra_customer_trx_lines_all rctl,
370 ra_customer_trx_all rct,
371 oe_order_lines_all sl_child,
372 oe_order_lines_all sl_parent,
373 mtl_system_items msi,
374 oe_order_headers_all sh,
375 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
376 WHERE
377 cr.build_id = l_build_id
378 AND rct.org_id = l_ou_id
379 AND rctlgd.org_id = l_ou_id
380 AND rctl.line_type = 'LINE'
381 AND rctl.customer_trx_id = rct.customer_trx_id
382 AND rct.complete_flag = 'Y'
383 AND rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
384 AND rctl.interface_line_context = 'ORDER ENTRY'
385 AND rctlgd.gl_date IS NOT NULL
386 AND rctlgd.gl_date BETWEEN cr.from_date AND cr.to_date
387 AND rctlgd.account_class = 'REV'
388 AND rctlgd.account_set_flag = 'N'
389 AND msi.inventory_item_id = sl_child.inventory_item_id
390 AND sl_parent.org_id = l_ou_id
391 AND msi.organization_id = sl_child.ship_from_org_id
392 /* Modifed for bug 7662078
393 AND sl_child.line_id = DECODE(rctl.INTERFACE_LINE_CONTEXT,
394 'ORDER ENTRY',
395 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE6,0)),
396 -99999)
397 AND sh.order_number = DECODE(rctl.INTERFACE_LINE_CONTEXT,
398 'ORDER ENTRY',
399 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE1,0)),
400 -99999) */
401 /*AND to_char(sl_child.line_id) = rctl.interface_line_attribute6
402 AND to_char(sh.order_number) = rctl.sales_order*/
403 /* Modified for bug 16018153 */
404 AND sl_child.line_id = Decode(decode(REGEXP_INSTR(rctl.interface_line_attribute6, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER'),'NUMBER',To_Number(rctl.interface_line_attribute6),To_Number(NULL))
405 AND sh.order_number = Decode(decode(REGEXP_INSTR(rctl.sales_order, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER'),'NUMBER',To_Number(rctl.sales_order),To_Number(NULL))
406 AND sl_child.line_category_code = 'ORDER'
407 AND sl_parent.line_category_code = 'ORDER'
408 AND sl_parent.line_id = NVL(sl_child.top_model_line_id, sl_child.line_id)
409 AND sh.header_id = sl_child.header_id
410 AND sh.header_id = sl_parent.header_id
414 AND mp.organization_id(+) = sl_parent.ship_from_org_id
411 ------------------------------------
412 -- INVCONV umoogala 17-oct-2004
413 ------------------------------------
415 AND NVL(mp.process_enabled_flag, 'N') = 'N';
416
417 /*---------------------------------------------------------------+
418 | Insert into CST_MARGIN_SUMMARY for IC-AR
419 +---------------------------------------------------------------*/
420
421 l_stmt_id := 35;
422
423 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
424 -- DBMS_OUTPUT.PUT_LINE('INSERT into TEMP.');
425 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
426
427 INSERT INTO CST_MARGIN_SUMMARY
428 (
429 build_id
430 ,source
431 ,row_type
432 ,origin
433 ,invoice_source
434 ,parent_rowid
435 ,order_number
436 ,header_id
437 ,legal_entity_id
438 ,org_id
439 ,order_type_id
440 ,customer_id
441 ,primary_salesrep_id
442 ,sales_channel_code
443 ,parent_inventory_item_id
444 ,parent_organization_id
445 ,parent_line_id
446 ,parent_line_number
447 ,parent_item_type_code
448 ,parent_ato_flag
449 ,inventory_item_id
450 ,organization_id
451 ,line_id
452 ,line_type_code
453 ,line_number
454 ,ship_to_site_use_id
455 ,invoice_to_site_use_id
456 ,customer_trx_id
457 ,customer_trx_line_id
458 ,original_gl_date
459 ,gl_date
460 ,invoice_line_quantity
461 ,invoice_quantity
462 ,invoiced_amount
463 ,sales_account
464 )
465 SELECT
466 l_build_id,
467 'IC-AR',
468 '7',
469 '1',
470 rctl.interface_line_context,
471 sl_parent.rowid,
472 sh.order_number,
473 sh.header_id,
474 l_le_id,
475 l_ou_id,
476 sh.order_type_id,
477 sh.sold_to_org_id,
478 sh.salesrep_id,
479 sh.sales_channel_code,
480 sl_parent.inventory_item_id,
481 sl_parent.ship_from_org_id,
482 sl_parent.line_id,
483 sl_parent.line_number,
484 sl_parent.item_type_code,
485 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
486 mmt.inventory_item_id,
487 mmt.organization_id,
488 sl_child.line_id,
489 sl_child.line_category_code,
490 sl_child.line_number,
491 sl_child.ship_to_org_id,
492 sh.invoice_to_org_id,
493 rct.customer_trx_id,
494 rctl.customer_trx_line_id,
495 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
496 rctlgd.gl_date,
497 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
498 rctl.quantity_invoiced, rctl.uom_code,
499 msi.primary_uom_code, TO_CHAR(NULL),
500 TO_CHAR(NULL)),
501 decode(rctl.inventory_item_id,
502 sl_parent.inventory_item_id,
503 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
504 rctl.quantity_invoiced,
505 rctl.uom_code,
506 msi.primary_uom_code,
507 TO_CHAR(NULL),
508 TO_CHAR(NULL))
509 * rctlgd.percent / 100,
510 0),
511 rctlgd.acctd_amount,
512 rctlgd.code_combination_id
513 FROM
514 cst_margin_build cr,
515 ra_cust_trx_line_gl_dist_all rctlgd,
516 ra_customer_trx_lines_all rctl,
517 oe_order_headers_all sh,
518 oe_order_lines_all sl_parent,
519 oe_order_lines_all sl_child,
520 mtl_system_items msi,
521 ra_customer_trx_all rct,
522 mtl_material_transactions mmt, -- dropship
523 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
524 WHERE
525 cr.build_id = l_build_id
526 AND rct.org_id = l_ou_id
527 AND rctlgd.org_id = l_ou_id
528 AND rctl.line_type = 'LINE'
529 AND rctl.customer_trx_id = rct.customer_trx_id
530 AND rct.batch_source_id = 8 /* Intercompany */
531 AND rct.complete_flag = 'Y'
532 AND rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
533 AND rctl.interface_line_context = 'INTERCOMPANY'
534 AND rctlgd.gl_date IS NOT NULL
535 AND rctlgd.gl_date BETWEEN cr.from_date AND cr.to_date
536 AND rctlgd.account_class = 'REV'
537 AND rctlgd.account_set_flag = 'N'
538 AND msi.inventory_item_id = sl_child.inventory_item_id
539 AND msi.organization_id = sl_child.ship_from_org_id
540 /* Modifed for bug 7662078
541 AND sl_child.line_id = DECODE(INTERFACE_LINE_CONTEXT,
545 0,
542 'INTERCOMPANY',
543 DECODE(rctl.interface_line_attribute2,
544 '0',
546 TO_NUMBER(rctl.interface_line_attribute6)),
547 -99999)
548 -----------------------------------------------------------------
549 -- Bug6502607 changes introduced to handle invalid number problem
550 -----------------------------------------------------------------
551 AND SH.ORDER_NUMBER = DECODE(INTERFACE_LINE_CONTEXT,
552 'INTERCOMPANY',
553 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE1),
554 -99999) */
555 AND to_char(sl_child.line_id) = DECODE(rctl.interface_line_attribute2,
556 '0',
557 '0',rctl.interface_line_attribute6)
558 /*Modified for bug 16018153*/
559 /*
560 AND to_char(sh.order_number) = rctl.sales_order*/
561 AND sh.order_number = Decode(decode(REGEXP_INSTR(rctl.sales_order, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER'),'NUMBER',To_Number(rctl.sales_order),To_Number(NULL))
562 AND sl_parent.line_category_code IN ('ORDER','RETURN')
563 AND sl_parent.line_id = NVL(sl_child.top_model_line_id,sl_child.line_id)
564 AND sh.header_id = sl_child.header_id
565 AND sh.header_id = sl_parent.header_id
566 AND mmt.transaction_id = TO_NUMBER(rctl.interface_line_attribute7) -- dropship
567 --------------------------------
568 -- INVCONV umoogala 17-oct-2004
569 --------------------------------
570 AND mp.organization_id(+) = sl_parent.ship_from_org_id
571 AND NVL(mp.process_enabled_flag, 'N')= 'N';
572
573 /*---------------------------------------------------------------+
574 | Insert in temp table for all the RMA Invoices
575 +---------------------------------------------------------------*/
576
577 l_stmt_id := 40;
578
579 -- Bug#2019804.Added to_char to fix Invalid number problem and also changed
580 -- where clause for performance viz . use of exist
581
582 INSERT INTO CST_MARGIN_SUMMARY
583 (
584 build_id
585 ,source
586 ,row_type
587 ,origin
588 ,invoice_source
589 ,parent_rowid
590 ,legal_entity_id
591 ,org_id
592 ,order_type_id
593 ,customer_id
594 ,primary_salesrep_id
595 ,sales_channel_code
596 ,parent_inventory_item_id
597 ,parent_organization_id
598 ,parent_line_id
599 ,parent_line_number
600 ,parent_item_type_code
601 ,parent_ato_flag
602 ,ship_to_site_use_id
603 ,invoice_to_site_use_id
604 ,customer_trx_id
605 ,customer_trx_line_id
606 ,original_gl_date
607 ,gl_date
608 ,order_number
609 ,rma_number
610 ,header_id
611 ,rma_header_id
612 ,rma_inventory_item_id
613 ,rma_line_id
614 ,rma_line_number
615 ,rma_ship_to_site_use_id
616 ,rma_line_type_code
617 ,link_to_line_id
618 ,invoice_line_quantity
619 ,invoice_quantity
620 ,invoiced_amount
621 ,sales_account
622 )
623 SELECT
624 l_build_id,
625 'RMA-INVOICE',
626 '3',
627 '2',
628 rctl.interface_line_context,
629 rma_line.rowid,
630 l_le_id,
631 NVL(l_ou_id, rma.org_id),
632 rma.order_type_id,
633 rma.sold_to_org_id,
634 rma.salesrep_id,
635 rma.sales_channel_code,
636 rma_line.inventory_item_id,
637 rma_line.ship_from_org_id,
638 rma_line.line_id,
639 rma_line.line_number,
640 rma_line.item_type_code,
641 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
642 rma_line.ship_to_org_id,
643 rma.invoice_to_org_id,
644 rctl.CUSTOMER_TRX_ID,
645 rctl.CUSTOMER_TRX_LINE_ID,
646 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
647 rctlgd.gl_date,
648 rma.order_number,
649 rma.order_number,
650 rma.header_id,
651 rma.header_id,
652 rctl.inventory_item_id,
653 rma_line.line_id,
654 rma_line.line_number,
655 rma_line.ship_to_org_id,
656 rma_line.line_category_code,
657 rma_line.link_to_line_id,
658 inv_convert.inv_um_convert (rma_line.inventory_item_id, 7,
659 rctl.quantity_credited, rctl.uom_code,
660 msi.primary_uom_code, TO_CHAR(NULL),
661 TO_CHAR(NULL)),
662 decode(nvl(rctl.interface_line_attribute11, '0'),
663 '0', inv_convert.inv_um_convert (rma_line.inventory_item_id, 7,
664 rctl.quantity_credited,
668 TO_CHAR(NULL)) * rctlgd.percent / 100
665 rctl.uom_code,
666 msi.primary_uom_code,
667 TO_CHAR(NULL),
669 , 0),
670 rctlgd.acctd_amount,
671 rctlgd.code_combination_id
672 FROM
673 cst_margin_build cr,
674 ra_cust_trx_line_gl_dist_all rctlgd,
675 ra_customer_trx_lines_all rctl,
676 ra_customer_trx_all rct,
677 oe_order_headers_all rma,
678 oe_order_lines_all rma_line,
679 mtl_system_items msi,
680 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
681 WHERE cr.build_id = l_build_id
682 AND rctl.org_id = l_ou_id
683 AND rctl.line_type = 'LINE'
684 AND rctl.customer_trx_id = rct.customer_trx_id
685 AND rct.complete_flag = 'Y'
686 AND rct.org_id = l_ou_id
687 AND rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
688 AND rctl.interface_line_context = 'ORDER ENTRY'
689 AND rctlgd.gl_date IS NOT NULL
690 AND rctlgd.gl_date BETWEEN cr.from_date AND cr.to_date
691 AND rma.org_id = l_ou_id
692 AND rctlgd.account_class = 'REV'
693 AND rctlgd.account_set_flag = 'N'
694 AND msi.inventory_item_id = rma_line.inventory_item_id
695 AND msi.organization_id = rma_line.ship_from_org_id
696 AND rma_line.org_id = l_ou_id
697 /* Modifed for bug 7662078
698 AND rma_line.line_id = DECODE(rctl.INTERFACE_LINE_CONTEXT,
699 'ORDER ENTRY',
700 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE6,0)),
701 -99999)
702 AND rma.order_number = DECODE(rctl.INTERFACE_LINE_CONTEXT,
703 'ORDER ENTRY',
704 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE1,0)),
705 -99999) */
706 AND to_char(rma_line.line_id) = rctl.interface_line_attribute6
707 --AND to_char(rma.order_number) = rctl.sales_order
708 /*AND to_char(rma.order_number) = Nvl(rctl.interface_line_attribute1,'0')*/
709 /*Modified for bug 16018153*/
710 AND rma.order_number = Decode(decode(REGEXP_INSTR(rctl.interface_line_attribute1, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER'),'NUMBER',To_Number(rctl.interface_line_attribute1),To_Number('0'))
711 AND rma_line.line_category_code = 'RETURN'
712 AND rma.header_id = rma_line.header_id
713 /* INVCONV umoogala 17-oct-2004 */
714 AND mp.organization_id(+) = rma_line.ship_from_org_id
715 AND NVL(mp.process_enabled_flag, 'N') = 'N';
716
717 /*---------------------------------------------------------------+
718 | Update all the rows with parent_line_id if link_to_line_id is
719 | not null
720 +---------------------------------------------------------------*/
721
722 l_stmt_id := 50;
723
724 UPDATE CST_MARGIN_SUMMARY rma
725 SET (
726 parent_rowid ,
727 order_number,
728 header_id,
729 order_type_id,
730 customer_id ,
731 primary_salesrep_id,
732 sales_channel_code,
733 parent_inventory_item_id,
734 parent_organization_id,
735 parent_line_id,
736 parent_line_number,
737 parent_ato_flag,
738 parent_item_type_code,
739 inventory_item_id,
740 organization_id,
741 line_id,
742 line_number,
743 line_type_code,
744 ship_to_site_use_id,
745 invoice_to_site_use_id,
746 invoice_quantity,
747 return_reference_type_code,
748 return_reference_id) =
749
750 (SELECT
751
752 sl_parent.rowid,
753 sh.order_number,
754 sh.header_id ,
755 sh.order_type_id,
756 sh.sold_to_org_id,
757 sh.salesrep_id,
758 sh.sales_channel_code,
759 sl_parent.inventory_item_id,
760 sl_parent.ship_from_org_id,
761 sl_parent.line_id,
762 sl_parent.line_number,
763 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
764 sl_parent.item_type_code,
765 sl_child.inventory_item_id,
766 sl_child.ship_from_org_id,
767 sl_child.line_id,
768 sl_child.line_number,
769 sl_child.line_category_code,
770 sl_child.ship_to_org_id,
771 sh.invoice_to_org_id,
772 decode (rma.rma_inventory_item_id, sl_parent.inventory_item_id,
773 rma.invoice_quantity, 0),
774 sl_child.return_context,
775 sl_child.reference_line_id
776 FROM
777 oe_order_lines_all sl_parent,
778 oe_order_lines_all sl_child,
779 oe_order_headers_all sh
783 and sl_child.line_category_code = 'ORDER'
780 WHERE
781 sl_parent.org_id = l_ou_id
782 and sl_child.org_id = l_ou_id
784 and sl_parent.line_category_code = 'ORDER'
785 and sl_parent.line_id = nvl(sl_child.top_model_line_id,
786 sl_child.line_id)
787 and sl_parent.line_id = rma.link_to_line_id
788 and sl_child.line_id = rma.link_to_line_id
789 and sh.org_id = l_ou_id
790 and sh.header_id = sl_child.header_id
791 and sh.header_id = sl_parent.header_id
792 )
793 WHERE
794 rma.link_to_line_id is not null
795 and rma.row_type = 3
796 and rma.source='RMA-INVOICE'
797 and rma.org_id = l_ou_id
798 and rma.gl_date between l_from_date and l_to_date
799 and rma.build_id = l_build_id;
800
801 /*---------------------------------------------------------------+
802 | Insert in temp table all data for CR-memos not related to any
803 | RMA but related to an invoice selected earlier
804 +---------------------------------------------------------------*/
805
806 l_stmt_id := 80;
807
808 INSERT INTO CST_MARGIN_SUMMARY
809 (
810 build_id
811 ,source
812 ,row_type
813 ,origin
814 ,invoice_source
815 ,parent_rowid
816 ,order_number
817 ,header_id
818 ,legal_entity_id
819 ,org_id
820 ,order_type_id
821 ,customer_id
822 ,primary_salesrep_id
823 ,sales_channel_code
824 ,parent_inventory_item_id
825 ,parent_organization_id
826 ,parent_line_id
827 ,parent_line_number
828 ,parent_item_type_code
829 ,parent_ato_flag
830 ,inventory_item_id
831 ,organization_id
832 ,line_id
833 ,line_type_code
834 ,line_number
835 ,ship_to_site_use_id
836 ,invoice_to_site_use_id
837 ,customer_trx_id
838 ,customer_trx_line_id
839 ,original_gl_date
840 ,gl_date
841 ,invoice_line_quantity
842 ,invoice_quantity
843 ,invoiced_amount
844 ,sales_account
845 ,cr_trx_id
846 ,cr_trx_line_id
847 )
848 SELECT
849 l_build_id,
850 'CR-INVOICE',
851 '5',
852 '3',
853 rctl.interface_line_context,
854 temp.parent_rowid,
855 temp.order_number,
856 temp.header_id,
857 l_le_id,
858 NVL(l_ou_id, rct.org_id),
859 temp.order_type_id,
860 temp.customer_id,
861 temp.primary_salesrep_id,
862 temp.sales_channel_code,
863 temp.parent_inventory_item_id,
864 temp.parent_organization_id,
865 temp.parent_line_id,
866 temp.parent_line_number,
867 temp.parent_item_type_code,
868 temp.parent_ato_flag,
869 temp.inventory_item_id,
870 temp.organization_id,
871 temp.line_id,
872 temp.line_type_code,
873 temp.line_number,
874 temp.ship_to_site_use_id,
875 temp.invoice_to_site_use_id,
876 temp.customer_trx_id,
877 temp.customer_trx_line_id,
878 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
879 rctlgd.gl_date,
880 0,
881 0,
882 rctlgd.acctd_amount,
883 rctlgd.code_combination_id,
884 rct.CUSTOMER_TRX_ID,
885 rctl.CUSTOMER_TRX_LINE_ID
886 FROM
887 (
888 /*+ no_merge index(temp, cst_margin_summary_n5 )... Hint suggested by ATANDON of the performance team
889 Removed the Hint for perf 6310641 */
890 select *
891 from
892 CST_MARGIN_SUMMARY temp
893 where
894 temp.source = 'INVOICE'
895 and temp.org_id = l_ou_id
896 and temp.rowid in (select max(rowid) from cst_margin_summary t1
897 where t1.build_id = temp.build_id
898 and t1.source = 'INVOICE'
899 and t1.org_id = l_ou_id
900 and t1.header_id = temp.header_id
901 and t1.line_id = temp.line_id
902 group by t1.order_number , t1.line_number )) temp,
903 ra_customer_trx_all rct,
904 ra_customer_trx_lines_all rctl,
905 ra_cust_trx_line_gl_dist_all rctlgd
906 WHERE
907 rctl.line_type = 'LINE'
908 and rct.org_id = l_ou_id
909 and rctl.customer_trx_id = rct.customer_trx_id
910 and rct.complete_flag = 'Y'
911 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
912 and EXISTS ( select '1' from ra_cust_trx_types rctt
913 where rct.cust_trx_type_id = rctt.cust_trx_type_id
914 and rctt.type = 'CM')
915 and rctlgd.org_id = l_ou_id
916 and rctlgd.gl_date is not null
917 and rctlgd.gl_date between l_from_date and l_to_date
918 and rctlgd.account_class = 'REV'
919 and rctlgd.account_set_flag = 'N'
923 and not exists
920 and rctl.LINK_TO_CUST_TRX_LINE_ID is null
921 and rctl.previous_customer_trx_line_id = temp.CUSTOMER_TRX_LINE_ID
922 and rctl.previous_customer_trx_id = temp.customer_trx_id
924 (select 'x'
925 from cst_margin_summary t2,
926 oe_order_lines_all oel
927 where t2.source = 'RMA-INVOICE'
928 and t2.build_id = temp.build_id
929 and t2.org_id = l_ou_id
930 and temp.header_id = oel.reference_header_id
931 and temp.line_id = oel.reference_line_id
932 and oel.header_id = t2.rma_header_id
933 and oel.line_id = t2.rma_line_id
934 );
935
936 /*---------------------------------------------------------------+
937 | Insert in temp table all data for Cost of Goods Sold for
938 | regular invoices
939 +---------------------------------------------------------------*/
940
941 l_stmt_id := 90;
942
943 INSERT INTO CST_MARGIN_SUMMARY
944 (
945 build_id
946 ,source
947 ,row_type
948 ,origin
949 ,parent_rowid
950 ,order_number
951 ,header_id
952 ,legal_entity_id
953 ,org_id
954 ,order_type_id
955 ,customer_id
956 ,primary_salesrep_id
957 ,sales_channel_code
958 ,parent_inventory_item_id
959 ,parent_organization_id
960 ,parent_line_id
961 ,parent_line_number
962 ,parent_item_type_code
963 ,parent_ato_flag
964 ,inventory_item_id
965 ,organization_id
966 ,line_id
967 ,line_type_code
968 ,line_number
969 ,ship_to_site_use_id
970 ,invoice_to_site_use_id
971 ,original_gl_date
972 ,gl_date
973 ,order_line_quantity
974 ,ship_quantity
975 ,cogs_amount
976 ,cogs_account
977 )
978 (SELECT /*+ ORDERED */ /* asked by the performance team atandon */
979 l_build_id,
980 'COGS',
981 '2',
982 '1',
983 sl_parent.rowid,
984 sh.order_number,
985 sh.header_id,
986 l_le_id,
987 NVL(l_ou_id, sh.org_id),
988 sh.order_type_id,
989 sh.sold_to_org_id,
990 sh.salesrep_id,
991 sh.sales_channel_code,
992 sl_parent.inventory_item_id,
993 sl_parent.ship_from_org_id,
994 sl_parent.line_id,
995 sl_parent.line_number,
996 sl_parent.item_type_code,
997 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
998 mmt.inventory_item_id,
999 mmt.organization_id,
1000 mmt.trx_source_line_id,
1001 sl_child.line_category_code,
1002 sl_child.line_number,
1003 sl_child.ship_to_org_id,
1004 sh.invoice_to_org_id,
1005 mta.transaction_date,
1006 mta.transaction_date,
1007 sl_child.shipped_quantity,
1008 --{BUG#7215820
1009 CASE
1010 WHEN mmt.transaction_type_id = 10008 THEN
1011 -- R12 COGS transactions
1012 DECODE(DECODE(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1013 'N',
1014 DECODE(mmt.inventory_item_id,
1015 sl_parent.inventory_item_id,
1016 mmt.primary_quantity,
1017 0),
1018 ----------------------------------------------------
1019 -- may need to decode MODEL and KIT, to be confirmed
1020 ----------------------------------------------------
1021 'Y',
1022 DECODE(sl_parent.item_type_code,
1023 'MODEL',
1024 mmt.primary_quantity,
1025 DECODE(mmt.inventory_item_id,
1026 sl_parent.inventory_item_id,
1027 mmt.primary_quantity,
1028 0)),
1029 DECODE(mmt.inventory_item_id,
1030 sl_parent.inventory_item_id,
1031 mmt.primary_quantity,
1032 0))
1033 ELSE
1034 -- 11i transactions
1035 DECODE(DECODE(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1036 'N',
1037 DECODE(mmt.inventory_item_id,
1038 sl_parent.inventory_item_id,
1039 (-1) * mmt.primary_quantity,
1040 0),
1041 -- may need to decode MODEL and KIT, to be confirmed
1042 'Y',
1043 DECODE(sl_parent.item_type_code,
1044 'MODEL',
1045 (-1) * mmt.primary_quantity,
1046 DECODE(mmt.inventory_item_id,
1047 sl_parent.inventory_item_id,
1048 (-1) * mmt.primary_quantity,
1049 0)),
1050 DECODE(mmt.inventory_item_id,
1051 sl_parent.inventory_item_id,
1052 (-1) * mmt.primary_quantity,
1053 0))
1054 END,
1055 SUM(mta.base_transaction_value),
1056 mta.reference_account
1057 FROM cst_margin_build cr,
1061 oe_order_lines_all sl_child,
1058 cst_acct_info_v ood,
1059 mtl_material_transactions mmt,
1060 mtl_transaction_accounts mta,
1062 oe_order_lines_all sl_parent,
1063 oe_order_headers_all sh,
1064 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
1065 WHERE cr.build_id = l_build_id
1066 AND (mmt.transaction_source_type_id in (2,8) -- dropship
1067 OR mmt.transaction_action_id = 9)
1068 AND transaction_action_id <> 28
1069 AND mta.transaction_source_type_id = mmt.transaction_source_type_id -- dropship
1070 AND mmt.transaction_id = mta.transaction_id
1071 AND mta.accounting_line_type IN (2,35)
1072 AND mta.organization_id = mmt.organization_id
1073 AND sl_parent.org_id = sl_child.org_id
1074 AND sl_child.line_id = mmt.trx_source_line_id
1075 AND sl_child.line_category_code = 'ORDER'
1076 AND sl_parent.line_category_code = 'ORDER'
1077 AND mmt.transaction_date BETWEEN cr.from_date AND cr.to_date
1078 AND sl_parent.line_id = NVL(sl_child.top_model_line_id,sl_child.line_id)
1079 AND sh.header_id = sl_child.header_id
1080 AND mmt.organization_id = ood.organization_id
1081 AND ood.operating_unit = l_ou_id
1082 AND NOT EXISTS -- for internal orders, cogs should be picked up only if src OU <> dest OU
1083 (SELECT 'X'
1084 FROM po_requisition_headers_all prh
1085 WHERE prh.org_id = l_ou_id
1086 AND prh.requisition_header_id = sh.source_document_id
1087 AND sh.source_document_type_id = 10)
1088 -------------------------------
1089 -- INVCONV umoogala 17-oct-2004
1090 -------------------------------
1091 AND mp.organization_id(+) = sl_parent.ship_from_org_id
1092 AND NVL(mp.process_enabled_flag, 'N') = 'N'
1093 GROUP BY
1094 l_build_id,
1095 'COGS',
1096 '2',
1097 '1',
1098 sl_parent.rowid,
1099 sh.order_number,
1100 sh.header_id,
1101 l_le_id,
1102 NVL(l_ou_id, sh.org_id),
1103 sh.order_type_id,
1104 sh.sold_to_org_id,
1105 sh.salesrep_id,
1106 sh.sales_channel_code,
1107 sl_parent.inventory_item_id,
1108 sl_parent.ship_from_org_id,
1109 sl_parent.line_id,
1110 sl_parent.line_number,
1111 sl_parent.item_type_code,
1112 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1113 mmt.inventory_item_id,
1114 mmt.organization_id,
1115 mmt.trx_source_line_id,
1116 sl_child.line_category_code,
1117 sl_child.line_number,
1118 sl_child.ship_to_org_id,
1119 sh.invoice_to_org_id,
1120 mta.transaction_date,
1121 mta.transaction_date,
1122 sl_child.shipped_quantity,
1123 --{BUG#7215820
1124 CASE
1125 WHEN mmt.transaction_type_id = 10008 THEN
1126 -- R12 COGS transactions
1127 DECODE(DECODE(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1128 'N',
1129 DECODE(mmt.inventory_item_id,
1130 sl_parent.inventory_item_id,
1131 mmt.primary_quantity,
1132 0),
1133 ----------------------------------------------------
1134 -- may need to decode MODEL and KIT, to be confirmed
1135 ----------------------------------------------------
1136 'Y',
1137 DECODE(sl_parent.item_type_code,
1138 'MODEL',
1139 mmt.primary_quantity,
1140 DECODE(mmt.inventory_item_id,
1141 sl_parent.inventory_item_id,
1142 mmt.primary_quantity,
1143 0)),
1144 DECODE(mmt.inventory_item_id,
1145 sl_parent.inventory_item_id,
1146 mmt.primary_quantity,
1147 0))
1148 ELSE
1149 -- 11i transactions
1150 DECODE(DECODE(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1151 'N',
1152 DECODE(mmt.inventory_item_id,
1153 sl_parent.inventory_item_id,
1154 (-1) * mmt.primary_quantity,
1155 0),
1156 -- may need to decode MODEL and KIT, to be confirmed
1157 'Y',
1158 DECODE(sl_parent.item_type_code,
1159 'MODEL',
1160 (-1) * mmt.primary_quantity,
1161 DECODE(mmt.inventory_item_id,
1162 sl_parent.inventory_item_id,
1163 (-1) * mmt.primary_quantity,
1164 0)),
1165 DECODE(mmt.inventory_item_id,
1166 sl_parent.inventory_item_id,
1167 (-1) * mmt.primary_quantity,
1168 0))
1169 END,
1170 mta.reference_account
1171 UNION
1172 SELECT /*+ ORDERED */ /* asked by the performance team atandon */
1173 l_build_id,
1174 'COGS',
1175 '2',
1176 '1',
1177 sl_parent.rowid,
1181 NVL(l_ou_id, sh.org_id),
1178 sh.order_number,
1179 sh.header_id,
1180 l_le_id,
1182 sh.order_type_id,
1183 sh.sold_to_org_id,
1184 sh.salesrep_id,
1185 sh.sales_channel_code,
1186 sl_parent.inventory_item_id,
1187 sl_parent.ship_from_org_id,
1188 sl_parent.line_id,
1189 sl_parent.line_number,
1190 sl_parent.item_type_code,
1191 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1192 mmt.inventory_item_id,
1193 mmt.organization_id,
1194 mmt.trx_source_line_id,
1195 sl_child.line_category_code,
1196 sl_child.line_number,
1197 sl_child.ship_to_org_id,
1198 sh.invoice_to_org_id,
1199 -- mta.transaction_date,
1200 -- mta.transaction_date,
1201 mmt.transaction_date,
1202 mmt.transaction_date,
1203 sl_child.shipped_quantity,
1204 --{BUG#7215820
1205 -- CASE WHEN mmt.transaction_type_id = 10008 THEN
1206 -- R12 COGS transactions
1207 DECODE(
1208 DECODE(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1209 'N',
1210 DECODE(mmt.inventory_item_id,
1211 sl_parent.inventory_item_id,
1212 mmt.primary_quantity,
1213 0),
1214 ----------------------------------------------------
1215 -- may need to decode MODEL and KIT, to be confirmed
1216 ----------------------------------------------------
1217 'Y',
1218 DECODE(sl_parent.item_type_code,
1219 'MODEL',
1220 mmt.primary_quantity,
1221 DECODE(mmt.inventory_item_id,
1222 sl_parent.inventory_item_id,
1223 mmt.primary_quantity,
1224 0)),
1225 DECODE(mmt.inventory_item_id,
1226 sl_parent.inventory_item_id,
1227 mmt.primary_quantity,
1228 0)
1229 ) ,
1230 -- END,
1231 -- mta.base_transaction_value,
1232 -- mta.reference_account
1233 0,
1234 crcml.COGS_ACCT_ID
1235 FROM cst_margin_build cr,
1236 cst_acct_info_v ood,
1237 mtl_material_transactions mmt,
1238 oe_order_lines_all sl_child,
1239 oe_order_lines_all sl_parent,
1240 oe_order_headers_all sh,
1241 mtl_parameters mp, /* INVCONV umoogala 17-oct-2004 */
1242 cst_revenue_cogs_match_lines crcml
1243 WHERE cr.build_id = l_build_id
1244 AND (mmt.transaction_source_type_id in (2,8) -- dropship
1245 OR mmt.transaction_action_id = 9)
1246 AND transaction_action_id <> 28
1247 AND sl_parent.org_id = sl_child.org_id
1248 AND sl_child.line_id = mmt.trx_source_line_id
1249 AND sl_child.line_category_code = 'ORDER'
1250 AND sl_parent.line_category_code = 'ORDER'
1251 AND mmt.transaction_date BETWEEN cr.from_date AND cr.to_date
1252 AND sl_parent.line_id = NVL(sl_child.top_model_line_id,sl_child.line_id)
1253 AND sh.header_id = sl_child.header_id
1254 AND mmt.organization_id = ood.organization_id
1255 AND ood.operating_unit = l_ou_id
1256 AND mmt.transaction_type_id = 10008
1257 AND NOT EXISTS -- for internal orders, cogs should be picked up only if src OU <> dest OU
1258 (SELECT 'X'
1259 FROM po_requisition_headers_all prh
1260 WHERE prh.org_id = l_ou_id
1261 AND prh.requisition_header_id = sh.source_document_id
1262 AND sh.source_document_type_id = 10)
1263 -------------------------------
1264 -- INVCONV umoogala 17-oct-2004
1265 -------------------------------
1266 AND mp.organization_id(+) = sl_parent.ship_from_org_id
1267 AND NVL(mp.process_enabled_flag, 'N') = 'N'
1268 AND crcml.cogs_om_line_id=mmt.trx_source_line_id
1269 AND mmt.costed_flag IS NULL
1270 AND (
1271 mmt.primary_quantity = 0
1272 OR crcml.unit_cost=0
1273 )
1274 AND NOT EXISTS
1275 (SELECT 1 FROM mtl_transaction_accounts mta
1276 WHERE mta.transaction_id=mmt.transaction_id)
1277 );
1278
1279 /*- -------------------------------------------------------------+
1280 | Insert in temp table all data for IC-AP for
1281 | regular invoices
1282 +---------------------------------------------------------------*/
1283
1284 l_stmt_id := 95;
1285
1286 INSERT INTO CST_MARGIN_SUMMARY
1287 (
1288 build_id
1289 ,source
1290 ,row_type
1291 ,origin
1292 ,parent_rowid
1293 ,order_number
1294 ,header_id
1295 ,legal_entity_id
1296 ,org_id
1297 ,order_type_id
1298 ,customer_id
1299 ,primary_salesrep_id
1300 ,sales_channel_code
1301 ,parent_inventory_item_id
1302 ,parent_organization_id
1303 ,parent_line_id
1304 ,parent_line_number
1305 ,parent_item_type_code
1306 ,parent_ato_flag
1307 ,inventory_item_id
1311 ,line_number
1308 ,organization_id
1309 ,line_id
1310 ,line_type_code
1312 ,ship_to_site_use_id
1313 ,invoice_to_site_use_id
1314 ,original_gl_date
1315 ,gl_date
1316 ,order_line_quantity
1317 ,ship_quantity
1318 ,cogs_amount
1319 ,cogs_account
1320 )
1321 SELECT
1322 l_build_id,
1323 'IC-AP',
1324 '6',
1325 '1',
1326 sl_parent.rowid,
1327 sh.order_number,
1328 sh.header_id,
1329 l_le_id,
1330 NVL(l_ou_id, sh.org_id),
1331 sh.order_type_id,
1332 sh.sold_to_org_id,
1333 sh.salesrep_id,
1334 sh.sales_channel_code,
1335 sl_parent.inventory_item_id,
1336 sl_parent.ship_from_org_id,
1337 sl_parent.line_id,
1338 sl_parent.line_number,
1339 sl_parent.item_type_code,
1340 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1341 sl_child.inventory_item_id,
1342 sl_child.ship_from_org_id,
1343 sl_child.line_id,
1344 sl_child.line_category_code,
1345 sl_child.line_number,
1346 sl_child.ship_to_org_id,
1347 sh.invoice_to_org_id,
1348 aid.accounting_date,
1349 aid.accounting_date,
1350 sl_child.shipped_quantity,
1351 rcl.quantity_invoiced,
1352 NVL(aid.amount, 0),
1353 aid.dist_code_combination_id
1354 FROM
1355 cst_margin_build cr,
1356 ap_invoice_distributions_all aid,
1357 ap_invoices_all ai,
1358 oe_order_headers_all sh,
1359 oe_order_lines_all sl_parent,
1360 oe_order_lines_all sl_child,
1361 ra_customer_trx_lines_all rcl,
1362 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
1363 WHERE
1364 ai.invoice_id = aid.invoice_id
1365 and ai.source = 'Intercompany'
1366 and ai.org_id = aid.org_id
1367 and rcl.customer_trx_line_id = aid.reference_1
1368 and cr.build_id = l_build_id
1369 and sl_parent.org_id = decode(SH.SOURCE_DOCUMENT_TYPE_ID, 10, -1, l_ou_id) -- dropship
1370 and sl_child.line_id = rcl.interface_line_attribute6
1371 and sl_parent.line_category_code in ('ORDER' , 'RETURN')
1372 and sl_parent.line_id = nvl(sl_child.top_model_line_id,sl_child.line_id)
1373 and sh.header_id = sl_child.header_id
1374 and sh.header_id = sl_parent.header_id
1375 and aid.accounting_date between cr.from_date and cr.to_date
1376 and aid.line_type_lookup_code IN ('ITEM','ACCRUAL') --Invoice Lines Project
1377 AND LTRIM(AID.REFERENCE_1,'0123456789') IS NULL -- dropship <
1378 and NOT EXISTS
1379 (SELECT 'X'
1380 FROM mtl_material_transactions
1381 WHERE transaction_id = rcl.interface_line_attribute7
1382 AND transaction_source_type_id = 13) -- > dropship
1383 /* INVCONV umoogala 17-oct-2004 */
1384 and mp.organization_id(+) = sl_parent.ship_to_org_id
1385 and NVL(mp.process_enabled_flag, 'N') = 'N';
1386
1387 /*---------------------------------------------------------------+
1388 | Insert in temp table all data for Cost of Goods Sold for
1389 | RMA transactions
1390 +---------------------------------------------------------------*/
1391
1392 l_stmt_id := 100;
1393
1394 INSERT INTO CST_MARGIN_SUMMARY
1395 (
1396 build_id
1397 ,source
1398 ,row_type
1399 ,origin
1400 ,parent_rowid
1401 ,legal_entity_id
1402 ,org_id
1403 ,order_type_id
1404 ,customer_id
1405 ,primary_salesrep_id
1406 ,sales_channel_code
1407 ,parent_inventory_item_id
1408 ,parent_organization_id
1409 ,parent_line_id
1410 ,parent_line_number
1411 ,parent_item_type_code
1412 ,parent_ato_flag
1413 ,ship_to_site_use_id
1414 ,invoice_to_site_use_id
1415 ,original_gl_date
1416 ,gl_date
1417 ,order_number
1418 ,rma_number
1419 ,header_id
1420 ,rma_header_id
1421 ,rma_inventory_item_id
1422 ,rma_organization_id
1423 ,rma_line_id
1424 ,rma_line_number
1425 ,rma_ship_to_site_use_id
1426 ,rma_line_type_code
1427 ,link_to_line_id
1428 ,ship_quantity
1429 ,cogs_amount
1430 ,cogs_account -- added for ER 3007482
1431 )
1432 SELECT
1433 l_build_id,
1434 'RMA-COGS',
1435 '4',
1436 '2',
1437 rma_line.rowid,
1438 l_le_id,
1439 NVL(l_ou_id, rma.org_id),
1440 rma.order_type_id,
1441 rma.sold_to_org_id,
1442 rma.salesrep_id,
1443 rma.sales_channel_code,
1444 rma_line.inventory_item_id,
1445 rma_line.ship_from_org_id,
1446 rma_line.line_id,
1447 rma_line.line_number,
1448 rma_line.item_type_code,
1449 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
1450 rma_line.ship_to_org_id,
1451 rma.invoice_to_org_id,
1455 rma.order_number,
1452 mta.transaction_date,
1453 mta.transaction_date,
1454 rma.order_number,
1456 rma.header_id,
1457 rma.header_id,
1458 mmt.inventory_item_id,
1459 mmt.organization_id,
1460 rma_line.line_id,
1461 rma_line.line_number,
1462 rma_line.ship_to_org_id,
1463 rma_line.line_category_code,
1464 rma_line.link_to_line_id,
1465 ---------------------------
1466 -- Comment seems there is no impact of COGS recognitiom transaction here
1467 -- as the transaction_type_id 10008 and action_id 36 have been filter out in the where clause
1468 ---------------------------
1469 (-1)* decode(nvl(mta.cost_element_id,-1),1,
1470 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1471 mta.base_transaction_value,
1472 mta.reference_account -- added for ER 3007482
1473 FROM
1474 cst_margin_build cr,
1475 oe_order_headers_all rma,
1476 oe_order_lines_all rma_line,
1477 mtl_material_transactions mmt,
1478 cst_organization_definitions cod,
1479 mtl_transaction_accounts mta,
1480 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
1481 WHERE cr.build_id = l_build_id
1482 AND (mmt.transaction_source_type_id = 12 -- dropship <
1483 OR mmt.transaction_action_id = 14) -- logical I/C sales return
1484 AND mta.transaction_source_type_id = mmt.transaction_source_type_id -- > dropship
1485 AND mmt.transaction_id = mta.transaction_id
1486 --AND mta.accounting_line_type <> 1
1487 AND mta.accounting_line_type in (2,35)
1488 -------------------------------------------------------------------
1489 -- and rma_line.org_id = l_ou_id -- comment out for dropshipments
1490 -------------------------------------------------------------------
1491 AND rma_line.line_id = mmt.trx_source_line_id
1492 AND rma_line.line_category_code IN ('RETURN')
1493 ---------------------------------------------------------------
1494 -- and rma.org_id = l_ou_id -- comment out for dropshipments
1495 ---------------------------------------------------------------
1496 AND rma.header_id = rma_line.header_id
1497 AND mmt.transaction_date BETWEEN cr.from_date AND cr.to_date
1498 AND cod.organization_id = mmt.organization_id
1499 AND cod.operating_unit = NVL(l_ou_id,NVL(rma_line.org_id, -999))
1500 --------------------------------
1501 -- INVCONV umoogala 17-oct-2004
1502 --------------------------------
1503 AND mp.organization_id(+) = rma_line.ship_from_org_id
1504 AND NVL(mp.process_enabled_flag, 'N') = 'N';
1505
1506 /*---------------------------------------------------------------+
1507 | Update all the COGS rows with parent_line_id if link_to_line_id
1508 | is not null
1509 +---------------------------------------------------------------*/
1510
1511 l_stmt_id := 110;
1512
1513 UPDATE CST_MARGIN_SUMMARY rma
1514 SET (
1515 parent_rowid ,
1516 order_number,
1517 header_id,
1518 order_type_id,
1519 customer_id ,
1520 primary_salesrep_id,
1521 sales_channel_code,
1522 parent_inventory_item_id,
1523 parent_organization_id,
1524 parent_line_id,
1525 parent_line_number,
1526 parent_ato_flag,
1527 parent_item_type_code,
1528 inventory_item_id,
1529 organization_id,
1530 line_id,
1531 line_number,
1532 line_type_code,
1533 ship_to_site_use_id,
1534 invoice_to_site_use_id,
1535 ship_quantity,
1536 return_reference_type_code,
1537 return_reference_id) =
1538
1539 (SELECT
1540
1541 sl_parent.rowid,
1542 sh.order_number,
1543 sh.header_id ,
1544 sh.order_type_id,
1545 sh.sold_to_org_id,
1546 sh.salesrep_id,
1547 sh.sales_channel_code,
1548 sl_parent.inventory_item_id,
1549 sl_parent.ship_from_org_id,
1550 sl_parent.line_id,
1551 sl_parent.line_number,
1552 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1553 sl_parent.item_type_code,
1554 sl_child.inventory_item_id,
1555 sl_child.ship_from_org_id,
1556 sl_child.line_id,
1557 sl_child.line_number,
1558 sl_child.line_category_code,
1559 sl_child.ship_to_org_id,
1560 sh.invoice_to_org_id,
1561 decode(decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1562 'N',decode(rma.rma_inventory_item_id,
1563 sl_parent.inventory_item_id,
1564 rma.ship_quantity, 0),
1565 'Y',decode(sl_parent.item_type_code, 'MODEL',
1566 rma.ship_quantity,
1567 decode(rma.rma_inventory_item_id,
1568 sl_parent.inventory_item_id,
1569 rma.ship_quantity,
1573 ),
1570 0)),
1571 decode(rma.rma_inventory_item_id, sl_parent.inventory_item_id,
1572 rma.ship_quantity, 0)
1574 sl_child.return_context,
1575 sl_child.reference_line_id
1576 FROM
1577 oe_order_lines_all sl_parent,
1578 oe_order_lines_all sl_child,
1579 oe_order_headers_all sh
1580 WHERE
1581 NVL(sl_parent.org_id, -999) =
1582 NVL(l_ou_id, NVL(sl_parent.org_id, -999))
1583 and NVL(sl_child.org_id, -999) =
1584 NVL(l_ou_id, NVL(sl_child.org_id, -999))
1585 and sl_child.line_category_code = 'ORDER'
1586 and sl_parent.line_category_code = 'ORDER'
1587 and sl_parent.line_id = nvl(sl_child.top_model_line_id,
1588 sl_child.line_id)
1589 and sl_parent.line_id = rma.link_to_line_id
1590 and sl_child.line_id = rma.link_to_line_id
1591 and NVL(sh.org_id, -999) = NVL(l_ou_id,NVL(sh.org_id, -999))
1592 and sh.header_id = sl_child.header_id
1593 and sh.header_id = sl_parent.header_id
1594 )
1595 WHERE
1596 rma.link_to_line_id is not null
1597 and rma.row_type = 4
1598 and rma.source='RMA-COGS'
1599 and rma.gl_date between l_from_date and l_to_date
1600 and rma.build_id = l_build_id;
1601
1602 l_stmt_id := 110;
1603
1604
1605 END LOOP; /* Operating Unit Loop */
1606
1607 close all_ous;
1608
1609 /*---------------------------------------------------------------+
1610 | Update territory_id
1611 +---------------------------------------------------------------*/
1612
1613 l_stmt_id := 140;
1614
1615 UPDATE CST_MARGIN_SUMMARY temp
1616 SET territory_id =
1617 (SELECT territory_id
1618 FROM hz_cust_site_uses_all hcsua
1619 WHERE NVL(hcsua.org_id, -999) = NVL(l_ou_id, NVL(hcsua.org_id, -999))
1620 AND hcsua.site_use_id = temp.ship_to_site_use_id )
1621 WHERE
1622 ship_to_site_use_id is not null
1623 and gl_date between l_from_date and l_to_date
1624 and build_id = l_build_id;
1625
1626 /*---------------------------------------------------------------+
1627 | Update customer class code
1628 +---------------------------------------------------------------*/
1629
1630 l_stmt_id := 150;
1631
1632 UPDATE CST_MARGIN_SUMMARY temp
1633 SET customer_class_code =
1634 (SELECT customer_class_code
1635 FROM hz_cust_accounts
1636 WHERE cust_account_id = temp.customer_id)
1637 WHERE
1638 customer_id is not null
1639 and gl_date between l_from_date and l_to_date
1640 and build_id = l_build_id;
1641
1642 /*---------------------------------------------------------------+
1643 | Update sold to customer name
1644 +---------------------------------------------------------------*/
1645
1646 l_stmt_id := 160;
1647
1648 OPEN sold_to_cust(l_build_id, l_from_date , l_to_date);
1649 LOOP
1650 FETCH sold_to_cust into l_rowid , l_cust_id ;
1651 EXIT WHEN sold_to_cust%NOTFOUND;
1652 SELECT SUBSTRB(hp.party_name,1,50) into l_cust_name
1653 FROM hz_cust_accounts hca,
1654 hz_parties hp
1655 WHERE hca.cust_account_id = l_cust_id
1656 AND hp.party_id = hca.party_id ;
1657
1658 UPDATE CST_MARGIN_SUMMARY
1659 SET sold_to_customer_name = l_cust_name
1660 WHERE
1661 rowid = l_rowid ;
1662 END LOOP ;
1663 close sold_to_cust ;
1664
1665 /*---------------------------------------------------------------+
1666 | Update bill to customer name
1667 +---------------------------------------------------------------*/
1668
1669 l_stmt_id := 170;
1670
1671 UPDATE CST_MARGIN_SUMMARY temp
1672 SET bill_to_customer_name =
1673 (SELECT SUBSTRB(hp.party_name,1,50)
1674 FROM hz_cust_accounts hca,
1675 hz_cust_site_uses_all hcsua,
1676 hz_cust_acct_sites_all hcasa,
1677 hz_parties hp
1678 WHERE
1679 NVL(hca.org_id, -999) = NVL(l_ou_id,NVL(hca.org_id, -999))
1680 and hca.cust_account_id = hcasa.cust_account_id
1681 and hp.party_id = hca.party_id
1682 and NVL(hcsua.org_id, -999) = NVL(l_ou_id, NVL(hcsua.org_id, -999))
1683 and NVL(hcasa.org_id, -999) = NVL(l_ou_id,NVL(hcasa.org_id, -999))
1684 and hcsua.site_use_id = temp.invoice_to_site_use_id
1685 and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id)
1686 WHERE
1687 customer_id is not null
1688 and gl_date between l_from_date and l_to_date
1689 and build_id = l_build_id;
1690
1691 /*---------------------------------------------------------------+
1692 | Update ship to customer name
1693 +---------------------------------------------------------------*/
1694
1695 l_stmt_id := 180;
1696
1697 UPDATE CST_MARGIN_SUMMARY temp
1698 SET ship_to_customer_name =
1699 (SELECT SUBSTRB(hp.party_name,1,50)
1700 FROM hz_cust_accounts hca,
1701 hz_cust_site_uses_all hcsua,
1702 hz_cust_acct_sites_all hcasa,
1703 hz_parties hp
1704 WHERE
1705 NVL(hca.org_id, -999) = NVL(l_ou_id,NVL(hca.org_id, -999))
1706 and hca.cust_account_id = hcasa.cust_account_id
1707 and hp.party_id = hca.party_id
1708 and NVL(hcsua.org_id, -999) = NVL(l_ou_id, NVL(hcsua.org_id, -999))
1709 and NVL(hcasa.org_id, -999) = NVL(l_ou_id,NVL(hcasa.org_id, -999))
1710 and hcsua.site_use_id = temp.ship_to_site_use_id
1711 and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id)
1712 WHERE
1713 customer_id is not null
1714 and gl_date between l_from_date and l_to_date
1715 and build_id = l_build_id;
1716
1717 /*---------------------------------------------------------------+
1718 | Commit the changes and exit
1719 +---------------------------------------------------------------*/
1720
1721 COMMIT;
1722
1723 END LOOP; /* Legal Entity Loop */
1724
1725 close all_le;
1726
1727 /* Update the selling OUs for COGS incase where shipping OU is different from booking OU bug 2554225*/
1728
1729 For cogs_rec in upd_org_cogs LOOP
1730 update CST_MARGIN_SUMMARY
1731 set org_id = cogs_rec.org_id
1732 where rowid = cogs_rec.rowid ;
1733 End Loop ;
1734
1735 COMMIT;
1736
1737 EXCEPTION
1738
1739 WHEN OM_NOT_ACTIVE_ERROR THEN
1740
1741 raise_application_error(-20000, 'CSTPMRGL.load_om_margin_data(): Order Management is not active');
1742
1743 WHEN OTHERS THEN
1744
1745 ROLLBACK;
1746
1747 raise_application_error(-20001, 'CSTPMRGL.load_om_margin_data(' ||
1748 l_stmt_id || '): ' || SQLERRM || ' for OU:' || to_char(l_ou_id) || ' and LE:' || to_char(l_le_id));
1749
1750 END load_om_margin_data;
1751
1752 END CSTPMRGL;
1753