[Home] [Help]
PACKAGE BODY: APPS.CSTPMRGL
Source
1 PACKAGE BODY CSTPMRGL AS
2 /* $Header: CSTMRGLB.pls 120.13.12010000.2 2008/08/08 12:31:08 smsasidh 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)
128 into l_from_date,
129 l_to_date
130 from dual;
131
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
296 ,parent_line_id
297 ,parent_line_number
298 ,parent_item_type_code
299 ,parent_ato_flag
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 AND sl_child.line_id = DECODE(rctl.INTERFACE_LINE_CONTEXT,
393 'ORDER ENTRY',
397 'ORDER ENTRY',
394 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE6,0)),
395 -99999)
396 AND sh.order_number = DECODE(rctl.INTERFACE_LINE_CONTEXT,
398 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE1,0)),
399 -99999)
400 AND sl_child.line_category_code = 'ORDER'
401 AND sl_parent.line_category_code = 'ORDER'
402 AND sl_parent.line_id = NVL(sl_child.top_model_line_id, sl_child.line_id)
403 AND sh.header_id = sl_child.header_id
404 AND sh.header_id = sl_parent.header_id
405 ------------------------------------
406 -- INVCONV umoogala 17-oct-2004
407 ------------------------------------
408 AND mp.organization_id = sl_parent.ship_from_org_id
409 AND NVL(mp.process_enabled_flag, 'N') = 'N';
410
411 /*---------------------------------------------------------------+
412 | Insert into CST_MARGIN_SUMMARY for IC-AR
413 +---------------------------------------------------------------*/
414
415 l_stmt_id := 35;
416
417 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
418 -- DBMS_OUTPUT.PUT_LINE('INSERT into TEMP.');
419 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
420
421 INSERT INTO CST_MARGIN_SUMMARY
422 (
423 build_id
424 ,source
425 ,row_type
426 ,origin
427 ,invoice_source
428 ,parent_rowid
429 ,order_number
430 ,header_id
431 ,legal_entity_id
432 ,org_id
433 ,order_type_id
434 ,customer_id
435 ,primary_salesrep_id
436 ,sales_channel_code
437 ,parent_inventory_item_id
438 ,parent_organization_id
439 ,parent_line_id
440 ,parent_line_number
441 ,parent_item_type_code
442 ,parent_ato_flag
443 ,inventory_item_id
444 ,organization_id
445 ,line_id
446 ,line_type_code
447 ,line_number
448 ,ship_to_site_use_id
449 ,invoice_to_site_use_id
450 ,customer_trx_id
451 ,customer_trx_line_id
452 ,original_gl_date
453 ,gl_date
454 ,invoice_line_quantity
455 ,invoice_quantity
456 ,invoiced_amount
457 ,sales_account
458 )
459 SELECT
460 l_build_id,
461 'IC-AR',
462 '7',
463 '1',
464 rctl.interface_line_context,
465 sl_parent.rowid,
466 sh.order_number,
467 sh.header_id,
468 l_le_id,
469 l_ou_id,
470 sh.order_type_id,
471 sh.sold_to_org_id,
472 sh.salesrep_id,
473 sh.sales_channel_code,
474 sl_parent.inventory_item_id,
475 sl_parent.ship_from_org_id,
476 sl_parent.line_id,
477 sl_parent.line_number,
478 sl_parent.item_type_code,
479 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
480 mmt.inventory_item_id,
481 mmt.organization_id,
482 sl_child.line_id,
483 sl_child.line_category_code,
484 sl_child.line_number,
485 sl_child.ship_to_org_id,
486 sh.invoice_to_org_id,
487 rct.customer_trx_id,
488 rctl.customer_trx_line_id,
489 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
490 rctlgd.gl_date,
491 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
492 rctl.quantity_invoiced, rctl.uom_code,
493 msi.primary_uom_code, TO_CHAR(NULL),
494 TO_CHAR(NULL)),
495 decode(rctl.inventory_item_id,
496 sl_parent.inventory_item_id,
497 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
498 rctl.quantity_invoiced,
499 rctl.uom_code,
500 msi.primary_uom_code,
501 TO_CHAR(NULL),
502 TO_CHAR(NULL))
503 * rctlgd.percent / 100,
504 0),
505 rctlgd.acctd_amount,
506 rctlgd.code_combination_id
507 FROM
508 cst_margin_build cr,
509 ra_cust_trx_line_gl_dist_all rctlgd,
510 ra_customer_trx_lines_all rctl,
511 oe_order_headers_all sh,
512 oe_order_lines_all sl_parent,
513 oe_order_lines_all sl_child,
514 mtl_system_items msi,
515 ra_customer_trx_all rct,
516 mtl_material_transactions mmt, -- dropship
517 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
521 AND rctlgd.org_id = l_ou_id
518 WHERE
519 cr.build_id = l_build_id
520 AND rct.org_id = l_ou_id
522 AND rctl.line_type = 'LINE'
523 AND rctl.customer_trx_id = rct.customer_trx_id
524 AND rct.batch_source_id = 8 /* Intercompany */
525 AND rct.complete_flag = 'Y'
526 AND rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
527 AND rctl.interface_line_context = 'INTERCOMPANY'
528 AND rctlgd.gl_date IS NOT NULL
529 AND rctlgd.gl_date BETWEEN cr.from_date AND cr.to_date
530 AND rctlgd.account_class = 'REV'
531 AND rctlgd.account_set_flag = 'N'
532 AND msi.inventory_item_id = sl_child.inventory_item_id
533 AND msi.organization_id = sl_child.ship_from_org_id
534 AND sl_child.line_id = DECODE(INTERFACE_LINE_CONTEXT,
535 'INTERCOMPANY',
536 DECODE(rctl.interface_line_attribute2,
537 '0',
538 0,
539 TO_NUMBER(rctl.interface_line_attribute6)),
540 -99999)
541 -----------------------------------------------------------------
542 -- Bug6502607 changes introduced to handle invalid number problem
543 -----------------------------------------------------------------
544 AND SH.ORDER_NUMBER = DECODE(INTERFACE_LINE_CONTEXT,
545 'INTERCOMPANY',
546 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE1),
547 -99999)
548 AND sl_parent.line_category_code IN ('ORDER','RETURN')
549 AND sl_parent.line_id = NVL(sl_child.top_model_line_id,sl_child.line_id)
550 AND sh.header_id = sl_child.header_id
551 AND sh.header_id = sl_parent.header_id
552 AND mmt.transaction_id = TO_NUMBER(rctl.interface_line_attribute7) -- dropship
553 --------------------------------
554 -- INVCONV umoogala 17-oct-2004
555 --------------------------------
556 AND mp.organization_id = sl_parent.ship_from_org_id
557 AND NVL(mp.process_enabled_flag, 'N')= 'N';
558
559 /*---------------------------------------------------------------+
560 | Insert in temp table for all the RMA Invoices
561 +---------------------------------------------------------------*/
562
563 l_stmt_id := 40;
564
565 -- Bug#2019804.Added to_char to fix Invalid number problem and also changed
566 -- where clause for performance viz . use of exist
567
568 INSERT INTO CST_MARGIN_SUMMARY
569 (
570 build_id
571 ,source
572 ,row_type
573 ,origin
574 ,invoice_source
575 ,parent_rowid
576 ,legal_entity_id
577 ,org_id
578 ,order_type_id
579 ,customer_id
580 ,primary_salesrep_id
581 ,sales_channel_code
582 ,parent_inventory_item_id
583 ,parent_organization_id
584 ,parent_line_id
585 ,parent_line_number
586 ,parent_item_type_code
587 ,parent_ato_flag
588 ,ship_to_site_use_id
589 ,invoice_to_site_use_id
590 ,customer_trx_id
591 ,customer_trx_line_id
592 ,original_gl_date
593 ,gl_date
594 ,order_number
595 ,rma_number
596 ,header_id
597 ,rma_header_id
598 ,rma_inventory_item_id
599 ,rma_line_id
600 ,rma_line_number
601 ,rma_ship_to_site_use_id
602 ,rma_line_type_code
603 ,link_to_line_id
604 ,invoice_line_quantity
605 ,invoice_quantity
606 ,invoiced_amount
607 ,sales_account
608 )
609 SELECT
610 l_build_id,
611 'RMA-INVOICE',
612 '3',
613 '2',
614 rctl.interface_line_context,
615 rma_line.rowid,
616 l_le_id,
617 NVL(l_ou_id, rma.org_id),
618 rma.order_type_id,
619 rma.sold_to_org_id,
620 rma.salesrep_id,
621 rma.sales_channel_code,
622 rma_line.inventory_item_id,
623 rma_line.ship_from_org_id,
624 rma_line.line_id,
625 rma_line.line_number,
626 rma_line.item_type_code,
627 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
628 rma_line.ship_to_org_id,
629 rma.invoice_to_org_id,
630 rctl.CUSTOMER_TRX_ID,
631 rctl.CUSTOMER_TRX_LINE_ID,
632 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
633 rctlgd.gl_date,
634 rma.order_number,
635 rma.order_number,
636 rma.header_id,
637 rma.header_id,
641 rma_line.ship_to_org_id,
638 rctl.inventory_item_id,
639 rma_line.line_id,
640 rma_line.line_number,
642 rma_line.line_category_code,
643 rma_line.link_to_line_id,
644 inv_convert.inv_um_convert (rma_line.inventory_item_id, 7,
645 rctl.quantity_credited, rctl.uom_code,
646 msi.primary_uom_code, TO_CHAR(NULL),
647 TO_CHAR(NULL)),
648 decode(nvl(rctl.interface_line_attribute11, '0'),
649 '0', inv_convert.inv_um_convert (rma_line.inventory_item_id, 7,
650 rctl.quantity_credited,
651 rctl.uom_code,
652 msi.primary_uom_code,
653 TO_CHAR(NULL),
654 TO_CHAR(NULL)) * rctlgd.percent / 100
655 , 0),
656 rctlgd.acctd_amount,
657 rctlgd.code_combination_id
658 FROM
659 cst_margin_build cr,
660 ra_cust_trx_line_gl_dist_all rctlgd,
661 ra_customer_trx_lines_all rctl,
662 ra_customer_trx_all rct,
663 oe_order_headers_all rma,
664 oe_order_lines_all rma_line,
665 mtl_system_items msi,
666 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
667 WHERE cr.build_id = l_build_id
668 AND rctl.org_id = l_ou_id
669 AND rctl.line_type = 'LINE'
670 AND rctl.customer_trx_id = rct.customer_trx_id
671 AND rct.complete_flag = 'Y'
672 AND rct.org_id = l_ou_id
673 AND rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
674 AND rctl.interface_line_context = 'ORDER ENTRY'
675 AND rctlgd.gl_date IS NOT NULL
676 AND rctlgd.gl_date BETWEEN cr.from_date AND cr.to_date
677 AND rma.org_id = l_ou_id
678 AND rctlgd.account_class = 'REV'
679 AND rctlgd.account_set_flag = 'N'
680 AND msi.inventory_item_id = rma_line.inventory_item_id
681 AND msi.organization_id = rma_line.ship_from_org_id
682 AND rma_line.org_id = l_ou_id
683 AND rma_line.line_id = DECODE(rctl.INTERFACE_LINE_CONTEXT,
684 'ORDER ENTRY',
685 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE6,0)),
686 -99999)
687 AND rma.order_number = DECODE(rctl.INTERFACE_LINE_CONTEXT,
688 'ORDER ENTRY',
689 TO_NUMBER(NVL(RCTL.INTERFACE_LINE_ATTRIBUTE1,0)),
690 -99999)
691 AND rma_line.line_category_code = 'RETURN'
692 AND rma.header_id = rma_line.header_id
693 /* INVCONV umoogala 17-oct-2004 */
694 AND mp.organization_id = rma_line.ship_from_org_id
695 AND NVL(mp.process_enabled_flag, 'N') = 'N';
696
697 /*---------------------------------------------------------------+
698 | Update all the rows with parent_line_id if link_to_line_id is
699 | not null
700 +---------------------------------------------------------------*/
701
702 l_stmt_id := 50;
703
704 UPDATE CST_MARGIN_SUMMARY rma
705 SET (
706 parent_rowid ,
707 order_number,
708 header_id,
709 order_type_id,
710 customer_id ,
711 primary_salesrep_id,
712 sales_channel_code,
713 parent_inventory_item_id,
714 parent_organization_id,
715 parent_line_id,
716 parent_line_number,
717 parent_ato_flag,
718 parent_item_type_code,
719 inventory_item_id,
720 organization_id,
721 line_id,
722 line_number,
723 line_type_code,
724 ship_to_site_use_id,
725 invoice_to_site_use_id,
726 invoice_quantity,
727 return_reference_type_code,
728 return_reference_id) =
729
730 (SELECT
731
732 sl_parent.rowid,
733 sh.order_number,
734 sh.header_id ,
735 sh.order_type_id,
736 sh.sold_to_org_id,
737 sh.salesrep_id,
738 sh.sales_channel_code,
739 sl_parent.inventory_item_id,
740 sl_parent.ship_from_org_id,
741 sl_parent.line_id,
742 sl_parent.line_number,
743 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
744 sl_parent.item_type_code,
745 sl_child.inventory_item_id,
746 sl_child.ship_from_org_id,
747 sl_child.line_id,
748 sl_child.line_number,
749 sl_child.line_category_code,
750 sl_child.ship_to_org_id,
754 sl_child.return_context,
751 sh.invoice_to_org_id,
752 decode (rma.rma_inventory_item_id, sl_parent.inventory_item_id,
753 rma.invoice_quantity, 0),
755 sl_child.reference_line_id
756 FROM
757 oe_order_lines_all sl_parent,
758 oe_order_lines_all sl_child,
759 oe_order_headers_all sh
760 WHERE
761 sl_parent.org_id = l_ou_id
762 and sl_child.org_id = l_ou_id
763 and sl_child.line_category_code = 'ORDER'
764 and sl_parent.line_category_code = 'ORDER'
765 and sl_parent.line_id = nvl(sl_child.top_model_line_id,
766 sl_child.line_id)
767 and sl_parent.line_id = rma.link_to_line_id
768 and sl_child.line_id = rma.link_to_line_id
769 and sh.org_id = l_ou_id
770 and sh.header_id = sl_child.header_id
771 and sh.header_id = sl_parent.header_id
772 )
773 WHERE
774 rma.link_to_line_id is not null
775 and rma.row_type = 3
776 and rma.source='RMA-INVOICE'
777 and rma.org_id = l_ou_id
778 and rma.gl_date between l_from_date and l_to_date
779 and rma.build_id = l_build_id;
780
781 /*---------------------------------------------------------------+
782 | Insert in temp table all data for CR-memos not related to any
783 | RMA but related to an invoice selected earlier
784 +---------------------------------------------------------------*/
785
786 l_stmt_id := 80;
787
788 INSERT INTO CST_MARGIN_SUMMARY
789 (
790 build_id
791 ,source
792 ,row_type
793 ,origin
794 ,invoice_source
795 ,parent_rowid
796 ,order_number
797 ,header_id
798 ,legal_entity_id
799 ,org_id
800 ,order_type_id
801 ,customer_id
802 ,primary_salesrep_id
803 ,sales_channel_code
804 ,parent_inventory_item_id
805 ,parent_organization_id
806 ,parent_line_id
807 ,parent_line_number
808 ,parent_item_type_code
809 ,parent_ato_flag
810 ,inventory_item_id
811 ,organization_id
812 ,line_id
813 ,line_type_code
814 ,line_number
815 ,ship_to_site_use_id
816 ,invoice_to_site_use_id
817 ,customer_trx_id
818 ,customer_trx_line_id
819 ,original_gl_date
820 ,gl_date
821 ,invoice_line_quantity
822 ,invoice_quantity
823 ,invoiced_amount
824 ,sales_account
825 ,cr_trx_id
826 ,cr_trx_line_id
827 )
828 SELECT
829 l_build_id,
830 'CR-INVOICE',
831 '5',
832 '3',
833 rctl.interface_line_context,
834 temp.parent_rowid,
835 temp.order_number,
836 temp.header_id,
837 l_le_id,
838 NVL(l_ou_id, rct.org_id),
839 temp.order_type_id,
840 temp.customer_id,
841 temp.primary_salesrep_id,
842 temp.sales_channel_code,
843 temp.parent_inventory_item_id,
844 temp.parent_organization_id,
845 temp.parent_line_id,
846 temp.parent_line_number,
847 temp.parent_item_type_code,
848 temp.parent_ato_flag,
849 temp.inventory_item_id,
850 temp.organization_id,
851 temp.line_id,
852 temp.line_type_code,
853 temp.line_number,
854 temp.ship_to_site_use_id,
855 temp.invoice_to_site_use_id,
856 temp.customer_trx_id,
857 temp.customer_trx_line_id,
858 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
859 rctlgd.gl_date,
860 0,
861 0,
862 rctlgd.acctd_amount,
863 rctlgd.code_combination_id,
864 rct.CUSTOMER_TRX_ID,
865 rctl.CUSTOMER_TRX_LINE_ID
866 FROM
867 (
868 /*+ no_merge index(temp, cst_margin_summary_n5 )... Hint suggested by ATANDON of the performance team
869 Removed the Hint for perf 6310641 */
870 select *
871 from
872 CST_MARGIN_SUMMARY temp
873 where
874 temp.source = 'INVOICE'
875 and temp.org_id = l_ou_id
876 and temp.rowid in (select max(rowid) from cst_margin_summary t1
877 where t1.build_id = temp.build_id
878 and t1.source = 'INVOICE'
879 and t1.org_id = l_ou_id
880 and t1.header_id = temp.header_id
881 and t1.line_id = temp.line_id
882 group by t1.order_number , t1.line_number )) temp,
886 WHERE
883 ra_customer_trx_all rct,
884 ra_customer_trx_lines_all rctl,
885 ra_cust_trx_line_gl_dist_all rctlgd
887 rctl.line_type = 'LINE'
888 and rct.org_id = l_ou_id
889 and rctl.customer_trx_id = rct.customer_trx_id
890 and rct.complete_flag = 'Y'
891 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
892 and EXISTS ( select '1' from ra_cust_trx_types rctt
893 where rct.cust_trx_type_id = rctt.cust_trx_type_id
894 and rctt.type = 'CM')
895 and rctlgd.org_id = l_ou_id
896 and rctlgd.gl_date is not null
897 and rctlgd.gl_date between l_from_date and l_to_date
898 and rctlgd.account_class = 'REV'
899 and rctlgd.account_set_flag = 'N'
900 and rctl.LINK_TO_CUST_TRX_LINE_ID is null
901 and rctl.previous_customer_trx_line_id = temp.CUSTOMER_TRX_LINE_ID
902 and rctl.previous_customer_trx_id = temp.customer_trx_id
903 and not exists
904 (select 'x'
905 from cst_margin_summary t2,
906 oe_order_lines_all oel
907 where t2.source = 'RMA-INVOICE'
908 and t2.build_id = temp.build_id
909 and t2.org_id = l_ou_id
910 and temp.header_id = oel.reference_header_id
911 and temp.line_id = oel.reference_line_id
912 and oel.header_id = t2.rma_header_id
913 and oel.line_id = t2.rma_line_id
914 );
915
916 /*---------------------------------------------------------------+
917 | Insert in temp table all data for Cost of Goods Sold for
918 | regular invoices
919 +---------------------------------------------------------------*/
920
921 l_stmt_id := 90;
922
923 INSERT INTO CST_MARGIN_SUMMARY
924 (
925 build_id
926 ,source
927 ,row_type
928 ,origin
929 ,parent_rowid
930 ,order_number
931 ,header_id
932 ,legal_entity_id
933 ,org_id
934 ,order_type_id
935 ,customer_id
936 ,primary_salesrep_id
937 ,sales_channel_code
938 ,parent_inventory_item_id
939 ,parent_organization_id
940 ,parent_line_id
941 ,parent_line_number
942 ,parent_item_type_code
943 ,parent_ato_flag
944 ,inventory_item_id
945 ,organization_id
946 ,line_id
947 ,line_type_code
948 ,line_number
949 ,ship_to_site_use_id
950 ,invoice_to_site_use_id
951 ,original_gl_date
952 ,gl_date
953 ,order_line_quantity
954 ,ship_quantity
955 ,cogs_amount
956 ,cogs_account
957 )
958 SELECT /*+ ORDERED */ /* asked by the performance team atandon */
959 l_build_id,
960 'COGS',
961 '2',
962 '1',
963 sl_parent.rowid,
964 sh.order_number,
965 sh.header_id,
966 l_le_id,
967 NVL(l_ou_id, sh.org_id),
968 sh.order_type_id,
969 sh.sold_to_org_id,
970 sh.salesrep_id,
971 sh.sales_channel_code,
972 sl_parent.inventory_item_id,
973 sl_parent.ship_from_org_id,
974 sl_parent.line_id,
975 sl_parent.line_number,
976 sl_parent.item_type_code,
977 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
978 mmt.inventory_item_id,
979 mmt.organization_id,
980 mmt.trx_source_line_id,
981 sl_child.line_category_code,
982 sl_child.line_number,
983 sl_child.ship_to_org_id,
984 sh.invoice_to_org_id,
985 mta.transaction_date,
986 mta.transaction_date,
987 sl_child.shipped_quantity,
988 --{BUG#7215820
989 CASE WHEN mmt.transaction_type_id = 10008 THEN
990 -- R12 COGS transactions
991 DECODE(
992 DECODE(sl_parent.ato_line_id, NULL, 'N', 'Y'),
993 'N',
994 DECODE(mmt.inventory_item_id,
995 sl_parent.inventory_item_id,
996 DECODE(NVL(mta.cost_element_id,-1),
997 1,
998 mmt.primary_quantity,
999 -1,
1000 mmt.primary_quantity,
1001 0),
1002 0),
1003 ----------------------------------------------------
1004 -- may need to decode MODEL and KIT, to be confirmed
1005 ----------------------------------------------------
1006 'Y',
1007 DECODE(sl_parent.item_type_code,
1008 'MODEL',
1009 DECODE(nvl(mta.cost_element_id,-1),
1010 1,
1011 mmt.primary_quantity,
1012 -1,
1016 sl_parent.inventory_item_id,
1013 mmt.primary_quantity,
1014 0),
1015 DECODE(mmt.inventory_item_id,
1017 DECODE(NVL(mta.cost_element_id,-1),
1018 1,
1019 mmt.primary_quantity,
1020 -1,mmt.primary_quantity,
1021 0),
1022 0)),
1023 DECODE(mmt.inventory_item_id,
1024 sl_parent.inventory_item_id,
1025 DECODE(NVL(mta.cost_element_id,-1),
1026 1,
1027 mmt.primary_quantity,
1028 -1,
1029 mmt.primary_quantity,
1030 0),
1031 0)
1032 )
1033 ELSE
1034 -- 11i transactions
1035 DECODE(decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1036 'N',decode(mmt.inventory_item_id, sl_parent.inventory_item_id,
1037 (-1) * decode(nvl(mta.cost_element_id,-1), 1,
1038 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1039 0),
1040 -- may need to decode MODEL and KIT, to be confirmed
1041 'Y',decode(sl_parent.item_type_code, 'MODEL',
1042 (-1) * decode(nvl(mta.cost_element_id,-1),1,
1043 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1044 decode(mmt.inventory_item_id,
1045 sl_parent.inventory_item_id,
1046 (-1) * decode(nvl(mta.cost_element_id,-1),1,
1047 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1048 0)),
1049 decode(mmt.inventory_item_id, sl_parent.inventory_item_id,
1050 (-1) * decode(nvl(mta.cost_element_id,-1),1,
1051 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1052 0)
1053 )
1054 END,
1055 mta.base_transaction_value,
1056 mta.reference_account
1057 FROM cst_margin_build cr,
1058 cst_acct_info_v ood,
1059 mtl_material_transactions mmt,
1060 mtl_transaction_accounts mta,
1061 oe_order_lines_all sl_child,
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
1094 /*- -------------------------------------------------------------+
1095 | Insert in temp table all data for IC-AP for
1096 | regular invoices
1097 +---------------------------------------------------------------*/
1098
1099 l_stmt_id := 95;
1100
1101 INSERT INTO CST_MARGIN_SUMMARY
1102 (
1103 build_id
1104 ,source
1105 ,row_type
1106 ,origin
1107 ,parent_rowid
1108 ,order_number
1109 ,header_id
1110 ,legal_entity_id
1111 ,org_id
1112 ,order_type_id
1113 ,customer_id
1114 ,primary_salesrep_id
1115 ,sales_channel_code
1116 ,parent_inventory_item_id
1117 ,parent_organization_id
1118 ,parent_line_id
1119 ,parent_line_number
1123 ,organization_id
1120 ,parent_item_type_code
1121 ,parent_ato_flag
1122 ,inventory_item_id
1124 ,line_id
1125 ,line_type_code
1126 ,line_number
1127 ,ship_to_site_use_id
1128 ,invoice_to_site_use_id
1129 ,original_gl_date
1130 ,gl_date
1131 ,order_line_quantity
1132 ,ship_quantity
1133 ,cogs_amount
1134 ,cogs_account
1135 )
1136 SELECT
1137 l_build_id,
1138 'IC-AP',
1139 '6',
1140 '1',
1141 sl_parent.rowid,
1142 sh.order_number,
1143 sh.header_id,
1144 l_le_id,
1145 NVL(l_ou_id, sh.org_id),
1146 sh.order_type_id,
1147 sh.sold_to_org_id,
1148 sh.salesrep_id,
1149 sh.sales_channel_code,
1150 sl_parent.inventory_item_id,
1151 sl_parent.ship_from_org_id,
1152 sl_parent.line_id,
1153 sl_parent.line_number,
1154 sl_parent.item_type_code,
1155 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1156 sl_child.inventory_item_id,
1157 sl_child.ship_from_org_id,
1158 sl_child.line_id,
1159 sl_child.line_category_code,
1160 sl_child.line_number,
1161 sl_child.ship_to_org_id,
1162 sh.invoice_to_org_id,
1163 aid.accounting_date,
1164 aid.accounting_date,
1165 sl_child.shipped_quantity,
1166 rcl.quantity_invoiced,
1167 NVL(aid.amount, 0),
1168 aid.dist_code_combination_id
1169 FROM
1170 cst_margin_build cr,
1171 ap_invoice_distributions_all aid,
1172 ap_invoices_all ai,
1173 oe_order_headers_all sh,
1174 oe_order_lines_all sl_parent,
1175 oe_order_lines_all sl_child,
1176 ra_customer_trx_lines_all rcl,
1177 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
1178 WHERE
1179 ai.invoice_id = aid.invoice_id
1180 and ai.source = 'Intercompany'
1181 and ai.org_id = aid.org_id
1182 and rcl.customer_trx_line_id = aid.reference_1
1183 and cr.build_id = l_build_id
1184 and sl_parent.org_id = decode(SH.SOURCE_DOCUMENT_TYPE_ID, 10, -1, l_ou_id) -- dropship
1185 and sl_child.line_id = rcl.interface_line_attribute6
1186 and sl_parent.line_category_code in ('ORDER' , 'RETURN')
1187 and sl_parent.line_id = nvl(sl_child.top_model_line_id,sl_child.line_id)
1188 and sh.header_id = sl_child.header_id
1189 and sh.header_id = sl_parent.header_id
1190 and aid.accounting_date between cr.from_date and cr.to_date
1191 and aid.line_type_lookup_code IN ('ITEM','ACCRUAL') --Invoice Lines Project
1192 AND LTRIM(AID.REFERENCE_1,'0123456789') IS NULL -- dropship <
1193 and NOT EXISTS
1194 (SELECT 'X'
1195 FROM mtl_material_transactions
1196 WHERE transaction_id = rcl.interface_line_attribute7
1197 AND transaction_source_type_id = 13) -- > dropship
1198 /* INVCONV umoogala 17-oct-2004 */
1199 and mp.organization_id = sl_parent.ship_to_org_id
1200 and NVL(mp.process_enabled_flag, 'N') = 'N';
1201
1202 /*---------------------------------------------------------------+
1203 | Insert in temp table all data for Cost of Goods Sold for
1204 | RMA transactions
1205 +---------------------------------------------------------------*/
1206
1207 l_stmt_id := 100;
1208
1209 INSERT INTO CST_MARGIN_SUMMARY
1210 (
1211 build_id
1212 ,source
1213 ,row_type
1214 ,origin
1215 ,parent_rowid
1216 ,legal_entity_id
1217 ,org_id
1218 ,order_type_id
1219 ,customer_id
1220 ,primary_salesrep_id
1221 ,sales_channel_code
1222 ,parent_inventory_item_id
1223 ,parent_organization_id
1224 ,parent_line_id
1225 ,parent_line_number
1226 ,parent_item_type_code
1227 ,parent_ato_flag
1228 ,ship_to_site_use_id
1229 ,invoice_to_site_use_id
1230 ,original_gl_date
1231 ,gl_date
1232 ,order_number
1233 ,rma_number
1234 ,header_id
1235 ,rma_header_id
1236 ,rma_inventory_item_id
1237 ,rma_organization_id
1238 ,rma_line_id
1239 ,rma_line_number
1240 ,rma_ship_to_site_use_id
1241 ,rma_line_type_code
1242 ,link_to_line_id
1243 ,ship_quantity
1244 ,cogs_amount
1245 ,cogs_account -- added for ER 3007482
1246 )
1247 SELECT
1248 l_build_id,
1249 'RMA-COGS',
1250 '4',
1251 '2',
1252 rma_line.rowid,
1253 l_le_id,
1254 NVL(l_ou_id, rma.org_id),
1255 rma.order_type_id,
1256 rma.sold_to_org_id,
1257 rma.salesrep_id,
1261 rma_line.line_id,
1258 rma.sales_channel_code,
1259 rma_line.inventory_item_id,
1260 rma_line.ship_from_org_id,
1262 rma_line.line_number,
1263 rma_line.item_type_code,
1264 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
1265 rma_line.ship_to_org_id,
1266 rma.invoice_to_org_id,
1267 mta.transaction_date,
1268 mta.transaction_date,
1269 rma.order_number,
1270 rma.order_number,
1271 rma.header_id,
1272 rma.header_id,
1273 mmt.inventory_item_id,
1274 mmt.organization_id,
1275 rma_line.line_id,
1276 rma_line.line_number,
1277 rma_line.ship_to_org_id,
1278 rma_line.line_category_code,
1279 rma_line.link_to_line_id,
1280 ---------------------------
1281 -- Comment seems there is no impact of COGS recognitiom transaction here
1282 -- as the transaction_type_id 10008 and action_id 36 have been filter out in the where clause
1283 ---------------------------
1284 (-1)* decode(nvl(mta.cost_element_id,-1),1,
1285 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1286 mta.base_transaction_value,
1287 mta.reference_account -- added for ER 3007482
1288 FROM
1289 cst_margin_build cr,
1290 oe_order_headers_all rma,
1291 oe_order_lines_all rma_line,
1292 mtl_material_transactions mmt,
1293 cst_organization_definitions cod,
1294 mtl_transaction_accounts mta,
1295 mtl_parameters mp /* INVCONV umoogala 17-oct-2004 */
1296 WHERE cr.build_id = l_build_id
1297 AND (mmt.transaction_source_type_id = 12 -- dropship <
1298 OR mmt.transaction_action_id = 14) -- logical I/C sales return
1299 AND mta.transaction_source_type_id = mmt.transaction_source_type_id -- > dropship
1300 AND mmt.transaction_id = mta.transaction_id
1301 AND mta.accounting_line_type <> 1
1302 -------------------------------------------------------------------
1303 -- and rma_line.org_id = l_ou_id -- comment out for dropshipments
1304 -------------------------------------------------------------------
1305 AND rma_line.line_id = mmt.trx_source_line_id
1306 AND rma_line.line_category_code IN ('RETURN')
1307 ---------------------------------------------------------------
1308 -- and rma.org_id = l_ou_id -- comment out for dropshipments
1309 ---------------------------------------------------------------
1310 AND rma.header_id = rma_line.header_id
1311 AND mmt.transaction_date BETWEEN cr.from_date AND cr.to_date
1312 AND cod.organization_id = mmt.organization_id
1313 AND cod.operating_unit = NVL(l_ou_id,NVL(rma_line.org_id, -999))
1314 --------------------------------
1315 -- INVCONV umoogala 17-oct-2004
1316 --------------------------------
1317 AND mp.organization_id = rma_line.ship_from_org_id
1318 AND NVL(mp.process_enabled_flag, 'N') = 'N';
1319
1320 /*---------------------------------------------------------------+
1321 | Update all the COGS rows with parent_line_id if link_to_line_id
1322 | is not null
1323 +---------------------------------------------------------------*/
1324
1325 l_stmt_id := 110;
1326
1327 UPDATE CST_MARGIN_SUMMARY rma
1328 SET (
1329 parent_rowid ,
1330 order_number,
1331 header_id,
1332 order_type_id,
1333 customer_id ,
1334 primary_salesrep_id,
1335 sales_channel_code,
1336 parent_inventory_item_id,
1337 parent_organization_id,
1338 parent_line_id,
1339 parent_line_number,
1340 parent_ato_flag,
1341 parent_item_type_code,
1342 inventory_item_id,
1343 organization_id,
1344 line_id,
1345 line_number,
1346 line_type_code,
1347 ship_to_site_use_id,
1348 invoice_to_site_use_id,
1349 ship_quantity,
1350 return_reference_type_code,
1351 return_reference_id) =
1352
1353 (SELECT
1354
1355 sl_parent.rowid,
1356 sh.order_number,
1357 sh.header_id ,
1358 sh.order_type_id,
1359 sh.sold_to_org_id,
1360 sh.salesrep_id,
1361 sh.sales_channel_code,
1362 sl_parent.inventory_item_id,
1363 sl_parent.ship_from_org_id,
1364 sl_parent.line_id,
1365 sl_parent.line_number,
1366 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1367 sl_parent.item_type_code,
1368 sl_child.inventory_item_id,
1369 sl_child.ship_from_org_id,
1370 sl_child.line_id,
1371 sl_child.line_number,
1375 decode(decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1372 sl_child.line_category_code,
1373 sl_child.ship_to_org_id,
1374 sh.invoice_to_org_id,
1376 'N',decode(rma.rma_inventory_item_id,
1377 sl_parent.inventory_item_id,
1378 rma.ship_quantity, 0),
1379 'Y',decode(sl_parent.item_type_code, 'MODEL',
1380 rma.ship_quantity,
1381 decode(rma.rma_inventory_item_id,
1382 sl_parent.inventory_item_id,
1383 rma.ship_quantity,
1384 0)),
1385 decode(rma.rma_inventory_item_id, sl_parent.inventory_item_id,
1386 rma.ship_quantity, 0)
1387 ),
1388 sl_child.return_context,
1389 sl_child.reference_line_id
1390 FROM
1391 oe_order_lines_all sl_parent,
1392 oe_order_lines_all sl_child,
1393 oe_order_headers_all sh
1394 WHERE
1395 NVL(sl_parent.org_id, -999) =
1396 NVL(l_ou_id, NVL(sl_parent.org_id, -999))
1397 and NVL(sl_child.org_id, -999) =
1398 NVL(l_ou_id, NVL(sl_child.org_id, -999))
1399 and sl_child.line_category_code = 'ORDER'
1400 and sl_parent.line_category_code = 'ORDER'
1401 and sl_parent.line_id = nvl(sl_child.top_model_line_id,
1402 sl_child.line_id)
1403 and sl_parent.line_id = rma.link_to_line_id
1404 and sl_child.line_id = rma.link_to_line_id
1405 and NVL(sh.org_id, -999) = NVL(l_ou_id,NVL(sh.org_id, -999))
1406 and sh.header_id = sl_child.header_id
1407 and sh.header_id = sl_parent.header_id
1408 )
1409 WHERE
1410 rma.link_to_line_id is not null
1411 and rma.row_type = 4
1412 and rma.source='RMA-COGS'
1413 and rma.gl_date between l_from_date and l_to_date
1414 and rma.build_id = l_build_id;
1415
1416 l_stmt_id := 110;
1417
1418
1419 END LOOP; /* Operating Unit Loop */
1420
1421 close all_ous;
1422
1423 /*---------------------------------------------------------------+
1424 | Update territory_id
1425 +---------------------------------------------------------------*/
1426
1427 l_stmt_id := 140;
1428
1429 UPDATE CST_MARGIN_SUMMARY temp
1430 SET territory_id =
1431 (SELECT territory_id
1432 FROM hz_cust_site_uses_all hcsua
1433 WHERE NVL(hcsua.org_id, -999) = NVL(l_ou_id, NVL(hcsua.org_id, -999))
1434 AND hcsua.site_use_id = temp.ship_to_site_use_id )
1435 WHERE
1436 ship_to_site_use_id is not null
1437 and gl_date between l_from_date and l_to_date
1438 and build_id = l_build_id;
1439
1440 /*---------------------------------------------------------------+
1441 | Update customer class code
1442 +---------------------------------------------------------------*/
1443
1444 l_stmt_id := 150;
1445
1446 UPDATE CST_MARGIN_SUMMARY temp
1447 SET customer_class_code =
1448 (SELECT customer_class_code
1449 FROM hz_cust_accounts
1450 WHERE cust_account_id = temp.customer_id)
1451 WHERE
1452 customer_id is not null
1453 and gl_date between l_from_date and l_to_date
1454 and build_id = l_build_id;
1455
1456 /*---------------------------------------------------------------+
1457 | Update sold to customer name
1458 +---------------------------------------------------------------*/
1459
1460 l_stmt_id := 160;
1461
1462 OPEN sold_to_cust(l_build_id, l_from_date , l_to_date);
1463 LOOP
1464 FETCH sold_to_cust into l_rowid , l_cust_id ;
1465 EXIT WHEN sold_to_cust%NOTFOUND;
1466 SELECT SUBSTRB(hp.party_name,1,50) into l_cust_name
1467 FROM hz_cust_accounts hca,
1468 hz_parties hp
1469 WHERE hca.cust_account_id = l_cust_id
1470 AND hp.party_id = hca.party_id ;
1471
1472 UPDATE CST_MARGIN_SUMMARY
1473 SET sold_to_customer_name = l_cust_name
1474 WHERE
1475 rowid = l_rowid ;
1476 END LOOP ;
1477 close sold_to_cust ;
1478
1479 /*---------------------------------------------------------------+
1480 | Update bill to customer name
1481 +---------------------------------------------------------------*/
1482
1483 l_stmt_id := 170;
1484
1485 UPDATE CST_MARGIN_SUMMARY temp
1486 SET bill_to_customer_name =
1487 (SELECT SUBSTRB(hp.party_name,1,50)
1488 FROM hz_cust_accounts hca,
1489 hz_cust_site_uses_all hcsua,
1490 hz_cust_acct_sites_all hcasa,
1491 hz_parties hp
1492 WHERE
1493 NVL(hca.org_id, -999) = NVL(l_ou_id,NVL(hca.org_id, -999))
1494 and hca.cust_account_id = hcasa.cust_account_id
1495 and hp.party_id = hca.party_id
1496 and NVL(hcsua.org_id, -999) = NVL(l_ou_id, NVL(hcsua.org_id, -999))
1497 and NVL(hcasa.org_id, -999) = NVL(l_ou_id,NVL(hcasa.org_id, -999))
1498 and hcsua.site_use_id = temp.invoice_to_site_use_id
1499 and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id)
1500 WHERE
1501 customer_id is not null
1502 and gl_date between l_from_date and l_to_date
1503 and build_id = l_build_id;
1504
1505 /*---------------------------------------------------------------+
1506 | Update ship to customer name
1507 +---------------------------------------------------------------*/
1508
1509 l_stmt_id := 180;
1510
1511 UPDATE CST_MARGIN_SUMMARY temp
1512 SET ship_to_customer_name =
1513 (SELECT SUBSTRB(hp.party_name,1,50)
1514 FROM hz_cust_accounts hca,
1515 hz_cust_site_uses_all hcsua,
1516 hz_cust_acct_sites_all hcasa,
1517 hz_parties hp
1518 WHERE
1519 NVL(hca.org_id, -999) = NVL(l_ou_id,NVL(hca.org_id, -999))
1520 and hca.cust_account_id = hcasa.cust_account_id
1521 and hp.party_id = hca.party_id
1522 and NVL(hcsua.org_id, -999) = NVL(l_ou_id, NVL(hcsua.org_id, -999))
1523 and NVL(hcasa.org_id, -999) = NVL(l_ou_id,NVL(hcasa.org_id, -999))
1524 and hcsua.site_use_id = temp.ship_to_site_use_id
1525 and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id)
1526 WHERE
1527 customer_id is not null
1528 and gl_date between l_from_date and l_to_date
1529 and build_id = l_build_id;
1530
1531 /*---------------------------------------------------------------+
1532 | Commit the changes and exit
1533 +---------------------------------------------------------------*/
1534
1535 COMMIT;
1536
1537 END LOOP; /* Legal Entity Loop */
1538
1539 close all_le;
1540
1541 /* Update the selling OUs for COGS incase where shipping OU is different from booking OU bug 2554225*/
1542
1543 For cogs_rec in upd_org_cogs LOOP
1544 update CST_MARGIN_SUMMARY
1545 set org_id = cogs_rec.org_id
1546 where rowid = cogs_rec.rowid ;
1547 End Loop ;
1548
1549 COMMIT;
1550
1551 EXCEPTION
1552
1553 WHEN OM_NOT_ACTIVE_ERROR THEN
1554
1555 raise_application_error(-20000, 'CSTPMRGL.load_om_margin_data(): Order Management is not active');
1556
1557 WHEN OTHERS THEN
1558
1559 ROLLBACK;
1560
1561 raise_application_error(-20001, 'CSTPMRGL.load_om_margin_data(' ||
1562 l_stmt_id || '): ' || SQLERRM || ' for OU:' || to_char(l_ou_id) || ' and LE:' || to_char(l_le_id));
1563
1564 END load_om_margin_data;
1565
1566 END CSTPMRGL;
1567