[Home] [Help]
PACKAGE BODY: APPS.CSTPOMLD
Source
1 PACKAGE BODY CSTPOMLD AS
2 /* $Header: CSTOMLDB.pls 120.2 2006/06/09 05:10:57 srayadur noship $ */
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);
28 l_build_name VARCHAR2(255);
29 l_build_descr VARCHAR2(255);
30 app_col_name varchar2(50);
31 app_col_name1 varchar2(50);
32 sql_stmt varchar2(5000);
33 OM_NOT_ACTIVE_ERROR EXCEPTION;
34
35 /*---------------------------------------------------------------+
36 | Get all Legal Entities
37 +---------------------------------------------------------------*/
38
39 CURSOR all_le is
40 SELECT distinct organization_id,name
41 FROM hr_legal_entities;
42
43 /*---------------------------------------------------------------+
44 | Get all Operating Units for a given Legal Entity
45 +---------------------------------------------------------------*/
46
47 CURSOR all_ous(c_le_id NUMBER) is
48 SELECT distinct hoi.organization_id
49 FROM hr_organization_information hoi
50 WHERE hoi.org_information2 = to_char(c_le_id)
51 AND hoi.org_information_context = 'Operating Unit Information';
52
53 BEGIN
54 -- Initialize local variables
55
56 l_stmt_id := 0;
57 l_first_build := 0;
58 app_col_name := NULL;
59
60 begin
61
62 select application_column_name
63 into app_col_name
64 from bis_flex_mappings_v where
65 id_flex_code = 'RA_ADDRESSES' and level_id =
66 ( select dimension_level_id from bisbv_dimension_levels where
67 dimension_level_short_name = 'REGION');
68
69 exception
70 when others then
71 app_col_name := NULL;
72 end;
73
74 OPEN all_le;
75
76 LOOP
77
78 -- DBMS_OUTPUT.ENABLE(100000);
79 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
80 -- DBMS_OUTPUT.PUT_LINE('ENTER LEGAL ENTITY LOOP.');
81 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
82
83 FETCH all_le into l_le_id,l_le_name;
84
85 EXIT WHEN all_le%NOTFOUND;
86
87
88
89 /* Setting values for "from date", "to date", and "overlap days" */
90
91
92 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
93 -- DBMS_OUTPUT.PUT_LINE('Get last update date');
94 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
95
96
97 select MAX(last_update_date), NVL(MAX(0),1), NVL(MAX(build_id),0)
98 into l_last_load_date, l_first_build, l_build_id
99 from CST_BIS_MARGIN_BUILD
100 where legal_entity_id = l_le_id;
101
102
103 -- DBMS_OUTPUT.PUT_LINE('l_last_load_date = ' || to_char(l_last_load_date));
104 -- DBMS_OUTPUT.PUT_LINE('l_first_build = ' || to_char(l_first_build));
105
106 l_from_date := fnd_date.canonical_to_date(i_from_date);
107 l_to_date := NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE);
108
109
110 if (l_first_build = 1) then
111
112 select NVL(fnd_date.canonical_to_date(i_from_date),to_date('1900/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')),
113 NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE)
114 into l_from_date,
115 l_to_date
116 from dual;
117
118 elsif (i_from_date is NULL) then
119 if (i_load_option = 1) then
120 l_from_date := to_date('1900/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS');
121 l_to_date := NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE);
122 else
123 l_from_date := l_last_load_date - i_overlap_days;
124 l_to_date := NVL(fnd_date.canonical_to_date(i_to_date), SYSDATE);
125 end if;
126 end if;
127
128
129 -- DBMS_OUTPUT.PUT_LINE('l_le_name = ' || l_le_name);
130 -- DBMS_OUTPUT.PUT_LINE('l_from_date = ' || to_char(l_from_date));
131 -- DBMS_OUTPUT.PUT_LINE('l_to_date = ' || to_char(l_to_date));
132
133
134 /*---------------------------------------------------------------+
135 | Delete from CST_BIS_MARGIN_SUMMARY for the given Legal Entity
136 +---------------------------------------------------------------*/
137
138 BEGIN
139
140 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
141 -- DBMS_OUTPUT.PUT_LINE('DELETE from TEMP.');
142 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
143
144 DELETE from CST_BIS_MARGIN_SUMMARY
145 WHERE legal_entity_id = l_le_id
146 and gl_date between l_from_date and l_to_date;
147
148
149 EXCEPTION
150 WHEN NO_DATA_FOUND THEN
151 l_program_id := NULL;
152 l_request_id := NULL;
153 l_program_appl_id := NULL;
154 WHEN OTHERS THEN
155 raise;
156 END;
157
158
159
160 /*---------------------------------------------------------------+
161 | Insert into CST_BIS_MARGIN_BUILD, if required
162 +---------------------------------------------------------------*/
163
164 BEGIN
165
166 if l_first_build = 1 THEN
167 SELECT cst_margin_build_s.nextval
168 INTO l_build_id
169 FROM sys.dual;
170
171 l_build_name := to_char(l_build_id);
172 l_build_descr := l_le_name;
173 END IF;
174
175
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 NULL;
179 WHEN OTHERS THEN
180 raise;
181 END;
182
183
184 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
185 -- DBMS_OUTPUT.PUT_LINE('INSERT into BUILD.');
186 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
187
188 if l_first_build = 1 THEN
189
190 INSERT INTO CST_BIS_MARGIN_BUILD (
191 build_id,
192 build_name,
193 build_description,
194 legal_entity_id,
195 legal_entity_name,
196 header_id,
197 org_id,
198 organization_id,
199 from_date,
200 to_date,
201 cost_type_id,
202 creation_date,
203 created_by,
204 last_update_date,
205 last_updated_by,
206 last_update_login,
207 request_id,
208 program_application_id,
209 program_id,
210 program_update_date
211 )
212 SELECT l_build_id,
213 l_build_name,
214 l_build_descr,
215 l_le_id,
216 l_le_name,
217 NULL,
218 NULL,
219 l_le_id,
220 l_from_date,
221 l_to_date,
222 NULL,
223 SYSDATE,
224 i_user_id,
225 SYSDATE,
226 i_user_id,
227 i_user_id,
228 l_request_id,
229 l_program_appl_id,
230 l_program_id,
231 SYSDATE
232 FROM
233 sys.dual;
234
235 else
236
237 UPDATE CST_BIS_MARGIN_BUILD
238 SET FROM_DATE = l_from_date,
239 TO_DATE = l_to_date,
240 LAST_UPDATE_DATE = SYSDATE
241 WHERE legal_entity_id = l_le_id;
242 END IF;
243
244 /* Do for each Operating unit for the above legal entity */
245
246 OPEN all_ous(l_le_id);
247
248 LOOP
249
250 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
251 -- DBMS_OUTPUT.PUT_LINE('ENTER OPERATING UNIT LOOP.');
252 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
253
254 FETCH all_ous into l_ou_id;
255
256 EXIT WHEN all_ous%NOTFOUND;
257
258
259 /*---------------------------------------------------------------+
260 | Insert into CST_BIS_MARGIN_SUMMARY for all the invoices booked
261 | against regular orders
262 +---------------------------------------------------------------*/
263
264 l_stmt_id := 30;
265
266 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
267 -- DBMS_OUTPUT.PUT_LINE('INSERT into TEMP.');
268 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
269
270 INSERT INTO CST_BIS_MARGIN_SUMMARY
271 (
272 margin_pk,
273 build_id
274 ,source
275 ,row_type
276 ,origin
277 ,invoice_source
278 ,parent_rowid
279 ,order_number
280 ,header_id
281 ,legal_entity_id
282 ,org_id
283 ,order_type_id
284 ,customer_id
285 ,primary_salesrep_id
286 ,sales_channel_code
287 ,parent_inventory_item_id
288 ,parent_organization_id
289 ,parent_line_id
290 ,parent_line_number
291 ,parent_item_type_code
292 ,parent_ato_flag
293 ,inventory_item_id
294 ,organization_id
295 ,line_id
296 ,line_type_code
297 ,line_number
298 ,ship_to_site_use_id
299 ,invoice_to_site_use_id
300 ,customer_trx_id
301 ,customer_trx_line_id
302 ,original_gl_date
303 ,gl_date
304 ,invoice_line_quantity
305 ,invoice_quantity
306 ,invoiced_amount
307 ,sales_account
308 )
309 SELECT
310 'INV-'||rctlgd.CUST_TRX_LINE_GL_DIST_ID,
311 l_build_id,
312 'INVOICE',
313 '1',
314 '1',
315 rctl.interface_line_context,
316 sl_parent.rowid,
317 sh.order_number,
318 sh.header_id,
319 l_le_id,
320 NVL(l_ou_id, sl_parent.org_id),
321 sh.order_type_id,
322 sh.sold_to_org_id,
323 sh.salesrep_id,
324 sh.sales_channel_code,
325 sl_parent.inventory_item_id,
326 sl_parent.ship_from_org_id,
327 sl_parent.line_id,
328 sl_parent.line_number,
329 sl_parent.item_type_code,
330 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
331 sl_child.inventory_item_id,
332 sl_child.ship_from_org_id,
333 sl_child.line_id,
334 sl_child.line_category_code,
335 sl_child.line_number,
336 sl_child.ship_to_org_id,
337 sh.invoice_to_org_id,
338 rct.CUSTOMER_TRX_ID,
339 rctl.CUSTOMER_TRX_LINE_ID,
340 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
341 rctlgd.gl_date,
342 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
343 rctl.quantity_invoiced, rctl.uom_code,
344 msi.primary_uom_code, TO_CHAR(NULL),
345 TO_CHAR(NULL)),
346 decode(rctl.inventory_item_id,
347 sl_parent.inventory_item_id,
348 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
349 rctl.quantity_invoiced,
350 rctl.uom_code,
351 msi.primary_uom_code,
352 TO_CHAR(NULL),
353 TO_CHAR(NULL))
354 * rctlgd.percent / 100,
355 0),
356 rctlgd.acctd_amount,
357 rctlgd.code_combination_id
358 FROM
359 CST_BIS_MARGIN_BUILD cr,
360 ra_cust_trx_line_gl_dist_all rctlgd,
361 ra_customer_trx_lines_all rctl,
362 oe_order_headers_all sh,
363 oe_order_lines_all sl_parent,
364 oe_order_lines_all sl_child,
365 mtl_system_items msi,
366 ra_customer_trx_all rct,
367 mtl_parameters mp -- INVCONV umoogala 17-oct-2004
368 WHERE
369 cr.build_id = l_build_id
370 and rctl.org_id = l_ou_id
371 and rct.org_id = l_ou_id
372 and rctlgd.org_id = l_ou_id
373 and rctl.line_type = 'LINE'
374 and rctl.customer_trx_id = rct.customer_trx_id
375 and rct.complete_flag = 'Y'
376 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
377 and rctl.interface_line_context = 'ORDER ENTRY'
378 and rctlgd.gl_date is not null
379 and rctlgd.gl_date between cr.from_date and cr.to_date
380 and rctlgd.account_class = 'REV'
381 and rctlgd.account_set_flag = 'N'
382 and msi.inventory_item_id = sl_child.inventory_item_id
383 and sh.org_id = l_ou_id
384 and sl_parent.org_id = l_ou_id
385 and sl_child.org_id = l_ou_id
386 and msi.organization_id = sl_child.ship_from_org_id
387 and sl_child.line_id = DECODE(INTERFACE_LINE_CONTEXT,'ORDER ENTRY',
388 to_number(rctl.interface_line_attribute6), -99999)
389 AND SH.ORDER_NUMBER = DECODE(INTERFACE_LINE_CONTEXT,'ORDER ENTRY',
390 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE1), -99999)
391 and sl_child.line_category_code = 'ORDER'
392 and sl_parent.line_category_code = 'ORDER'
393 and sl_parent.line_id = nvl(sl_child.top_model_line_id, sl_child.line_id)
394 and sh.header_id = sl_child.header_id
395 and sh.header_id = sl_parent.header_id
396 /* INVCONV umoogala 17-oct-2004 */
397 and mp.organization_id = sl_child.ship_from_org_id
398 and mp.process_enabled_flag <> 'Y';
399 -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
400 -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
401
402 /*---------------------------------------------------------------+
403 | Insert into CST_BIS_MARGIN_SUMMARY for IC-AR
404 +---------------------------------------------------------------*/
405
406 l_stmt_id := 35;
407
408 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
409 -- DBMS_OUTPUT.PUT_LINE('INSERT into TEMP.');
410 -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
411
412
413 INSERT INTO CST_BIS_MARGIN_SUMMARY
414 (
415 margin_pk,
416 build_id
417 ,source
418 ,row_type
419 ,origin
420 ,invoice_source
421 ,parent_rowid
422 ,order_number
423 ,header_id
424 ,legal_entity_id
425 ,org_id
426 ,order_type_id
427 ,customer_id
428 ,primary_salesrep_id
429 ,sales_channel_code
430 ,parent_inventory_item_id
431 ,parent_organization_id
432 ,parent_line_id
433 ,parent_line_number
434 ,parent_item_type_code
435 ,parent_ato_flag
436 ,inventory_item_id
437 ,organization_id
438 ,line_id
439 ,line_type_code
440 ,line_number
441 ,ship_to_site_use_id
442 ,invoice_to_site_use_id
443 ,customer_trx_id
444 ,customer_trx_line_id
445 ,original_gl_date
446 ,gl_date
447 ,invoice_line_quantity
448 ,invoice_quantity
449 ,invoiced_amount
450 ,sales_account
451 )
452 SELECT
453 'ICAR-'||rctlgd.CUST_TRX_LINE_GL_DIST_ID,
454 l_build_id,
455 'IC-AR',
456 '7',
457 '1',
458 rctl.interface_line_context,
459 sl_parent.rowid,
460 sh.order_number,
461 sh.header_id,
462 l_le_id,
463 NVL(l_ou_id, sl_parent.org_id),
464 sh.order_type_id,
465 sh.sold_to_org_id,
466 sh.salesrep_id,
467 sh.sales_channel_code,
468 sl_parent.inventory_item_id,
469 sl_parent.ship_from_org_id,
470 sl_parent.line_id,
471 sl_parent.line_number,
472 sl_parent.item_type_code,
473 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
474 sl_child.inventory_item_id,
475 sl_child.ship_from_org_id,
476 sl_child.line_id,
477 sl_child.line_category_code,
478 sl_child.line_number,
479 sl_child.ship_to_org_id,
480 sh.invoice_to_org_id,
481 rct.customer_trx_id,
482 rctl.customer_trx_line_id,
483 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
484 rctlgd.gl_date,
485 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
486 rctl.quantity_invoiced, rctl.uom_code,
487 msi.primary_uom_code, TO_CHAR(NULL),
488 TO_CHAR(NULL)),
489 decode(rctl.inventory_item_id,
490 sl_parent.inventory_item_id,
491 inv_convert.inv_um_convert (sl_child.inventory_item_id, 7,
492 rctl.quantity_invoiced,
493 rctl.uom_code,
494 msi.primary_uom_code,
495 TO_CHAR(NULL),
496 TO_CHAR(NULL))
497 * rctlgd.percent / 100,
498 0),
499 rctlgd.acctd_amount,
500 rctlgd.code_combination_id
501 FROM
502 CST_BIS_MARGIN_BUILD cr,
503 ra_cust_trx_line_gl_dist_all rctlgd,
504 ra_customer_trx_lines_all rctl,
505 oe_order_headers_all sh,
506 oe_order_lines_all sl_parent,
507 oe_order_lines_all sl_child,
508 mtl_system_items msi,
509 ra_customer_trx_all rct,
510 mtl_parameters mp -- INVCONV umoogala 17-oct-2004
511 WHERE
512 cr.build_id = l_build_id
513 and rctl.org_id = l_ou_id
514 and rct.org_id = l_ou_id
515 and rctlgd.org_id = l_ou_id
516 and rctl.line_type = 'LINE'
517 and rctl.customer_trx_id = rct.customer_trx_id
518 and rct.batch_source_id = 8
519 and rct.complete_flag = 'Y'
520 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
521 and rctl.interface_line_context = 'INTERCOMPANY'
522 and rctlgd.gl_date is not null
523 and rctlgd.gl_date between cr.from_date and cr.to_date
524 and rctlgd.account_class = 'REV'
525 and rctlgd.account_set_flag = 'N'
529 to_number(rctl.interface_line_attribute6), -99999)
526 and msi.inventory_item_id = sl_child.inventory_item_id
527 and msi.organization_id = sl_child.ship_from_org_id
528 and sl_child.line_id = DECODE(INTERFACE_LINE_CONTEXT, 'INTERCOMPANY',
530 AND SH.ORDER_NUMBER = DECODE(INTERFACE_LINE_CONTEXT, 'INTERCOMPANY',
531 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE1), -99999)
532 and sl_child.line_category_code = 'ORDER'
533 AND ( sl_child.source_document_type_id IS NULL
534 OR sl_child.source_document_type_id <> 10 )
535 and sl_parent.line_category_code = 'ORDER'
536 and sl_parent.line_id = nvl(sl_child.top_model_line_id,sl_child.line_id)
537 and sh.header_id = sl_child.header_id
538 and sh.header_id = sl_parent.header_id
539 /* INVCONV umoogala 17-oct-2004 */
540 and mp.organization_id = sl_child.ship_from_org_id
541 and mp.process_enabled_flag <> 'Y';
542 -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
543 -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
544
545
546 /*---------------------------------------------------------------+
547 | Insert in temp table for all the RMA Invoices
548 +---------------------------------------------------------------*/
549
550 l_stmt_id := 40;
551
552 INSERT INTO CST_BIS_MARGIN_SUMMARY
553 (
554 margin_pk,
555 build_id
556 ,source
557 ,row_type
558 ,origin
559 ,invoice_source
560 ,parent_rowid
561 ,legal_entity_id
562 ,org_id
563 ,order_type_id
564 ,customer_id
565 ,primary_salesrep_id
566 ,sales_channel_code
567 ,parent_inventory_item_id
568 ,parent_organization_id
569 ,parent_line_id
570 ,parent_line_number
571 ,parent_item_type_code
572 ,parent_ato_flag
573 ,organization_id
574 ,ship_to_site_use_id
575 ,invoice_to_site_use_id
576 ,customer_trx_id
577 ,customer_trx_line_id
578 ,original_gl_date
579 ,gl_date
580 ,order_number
581 ,rma_number
582 ,header_id
583 ,rma_header_id
584 ,inventory_item_id
585 ,rma_inventory_item_id
586 ,line_id
587 ,rma_line_id
588 ,line_number
589 ,rma_line_number
590 ,rma_ship_to_site_use_id
591 ,line_type_code
592 ,rma_line_type_code
593 ,link_to_line_id
594 ,invoice_line_quantity
595 ,invoice_quantity
596 ,invoiced_amount
597 ,sales_account
598 )
599 SELECT
600 'RMA-INV_'||rctlgd.CUST_TRX_LINE_GL_DIST_ID,
601 l_build_id,
602 'RMA-INVOICE',
603 '3',
604 '2',
605 rctl.interface_line_context,
606 rma_line.rowid,
607 l_le_id,
608 NVL(l_ou_id, rma.org_id),
609 rma.order_type_id,
610 rma.sold_to_org_id,
611 rma.salesrep_id,
612 rma.sales_channel_code,
613 rma_line.inventory_item_id,
614 rma_line.ship_from_org_id,
615 rma_line.line_id,
616 rma_line.line_number,
617 rma_line.item_type_code,
618 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
619 rma.ship_from_org_id,
620 rma_line.ship_to_org_id,
621 rma.invoice_to_org_id,
622 rct.CUSTOMER_TRX_ID,
623 rctl.CUSTOMER_TRX_LINE_ID,
624 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
625 rctlgd.gl_date,
626 rma.order_number,
627 rma.order_number,
628 rma.header_id,
629 rma.header_id,
630 rctl.inventory_item_id,
631 rctl.inventory_item_id,
632 rma_line.line_id,
633 rma_line.line_id,
634 rma_line.line_number,
635 rma_line.line_number,
636 rma_line.ship_to_org_id,
637 rma_line.line_category_code,
638 rma_line.line_category_code,
639 rma_line.link_to_line_id,
640 (-1) * rma_line.SHIPPED_QUANTITY,
641 (-1) * rma_line.SHIPPED_QUANTITY * rctlgd.percent / 100,
642 rctlgd.acctd_amount,
643 rctlgd.code_combination_id
644 FROM
645 CST_BIS_MARGIN_BUILD cr,
646 ra_cust_trx_line_gl_dist_all rctlgd,
647 ra_customer_trx_lines_all rctl,
648 oe_order_headers_all rma,
649 oe_order_lines_all rma_line,
650 --hr_organization_information hoi,
651 ra_customer_trx_all rct,
652 mtl_parameters mp -- INVCONV umoogala 17-oct-2004
653 WHERE
654 cr.build_id = l_build_id
655 and rctl.org_id = l_ou_id
656 and rct.org_id = l_ou_id
657 and rctlgd.org_id = l_ou_id
658 and rctl.line_type = 'LINE'
659 and rctl.customer_trx_id = rct.customer_trx_id
660 and rct.complete_flag = 'Y'
664 and rctlgd.gl_date between cr.from_date and cr.to_date
661 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
662 and rctl.interface_line_context = 'ORDER ENTRY'
663 and rctlgd.gl_date is not null
665 and rma.org_id = l_ou_id
666 and rctlgd.account_class = 'REV'
667 and rctlgd.account_set_flag = 'N'
668 and rma_line.org_id = l_ou_id
669 and rma_line.line_id = DECODE(INTERFACE_LINE_CONTEXT, 'ORDER ENTRY',
670 TO_NUMBER(rctl.interface_line_attribute6), -99999)
671 AND rma.ORDER_NUMBER = DECODE(INTERFACE_LINE_CONTEXT, 'ORDER ENTRY',
672 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE1), -99999)
673 and rma_line.line_category_code = 'RETURN'
674 and rma.header_id = rma_line.header_id
675 /* INVCONV umoogala 17-oct-2004 */
676 and mp.organization_id = rma_line.ship_from_org_id
677 and mp.process_enabled_flag <> 'Y';
678 -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
679 -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
680 --and ( hoi.org_information_context || '') ='Accounting Information'
681 --and hoi.organization_id = rma_line.SHIP_FROM_ORG_ID
682 --and hoi.org_information3 = NVL(l_ou_id,NVL(rct.org_id, -999));
683
684 /*---------------------------------------------------------------+
685 | Insert RMA invoices for non-invenory items
686 +---------------------------------------------------------------*/
687
688 l_stmt_id := 45;
689 /*
690 INSERT INTO CST_BIS_MARGIN_SUMMARY
691 (
692 margin_pk,
693 build_id
694 ,source
695 ,row_type
696 ,origin
697 ,invoice_source
698 ,parent_rowid
699 ,legal_entity_id
700 ,org_id
701 ,order_type_id
702 ,customer_id
703 ,primary_salesrep_id
704 ,sales_channel_code
705 ,parent_inventory_item_id
706 ,parent_organization_id
707 ,parent_line_id
708 ,parent_line_number
709 ,parent_item_type_code
710 ,parent_ato_flag
711 ,ship_to_site_use_id
712 ,invoice_to_site_use_id
713 ,customer_trx_id
714 ,customer_trx_line_id
715 ,original_gl_date
716 ,gl_date
717 ,rma_number
718 ,rma_header_id
719 ,rma_inventory_item_id
720 ,rma_line_id
721 ,rma_line_number
722 ,rma_ship_to_site_use_id
723 ,rma_line_type_code
724 ,link_to_line_id
725 ,invoice_line_quantity
726 ,invoice_quantity
727 ,invoiced_amount
728 ,sales_account
729 )
730 SELECT
731 'RMA-INV-'||rctlgd.CUST_TRX_LINE_GL_DIST_ID,
732 l_build_id,
733 'RMA-INVOICE',
734 '3',
735 '2',
736 rctl.interface_line_context,
737 rma_line.rowid,
738 l_le_id,
739 NVL(l_ou_id, rma.org_id),
740 rma.order_type_id,
741 rma.sold_to_org_id,
742 rma.salesrep_id,
743 rma.sales_channel_code,
744 rma_line.inventory_item_id,
745 rma_line.ship_from_org_id,
746 rma_line.line_id,
747 rma_line.line_number,
748 rma_line.item_type_code,
749 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
750 rma_line.ship_to_org_id,
751 rma.invoice_to_org_id,
752 rct.CUSTOMER_TRX_ID,
753 rctl.CUSTOMER_TRX_LINE_ID,
754 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
755 rctlgd.gl_date,
756 rma.order_number,
757 rma.header_id,
758 rctl.inventory_item_id,
759 rma_line.line_id,
760 rma_line.line_number,
761 rma_line.ship_to_org_id,
762 rma_line.line_category_code,
763 rma_line.link_to_line_id,
764 (-1)*inv_convert.inv_um_convert (rma_line.inventory_item_id, 7,
765 rma_line.invoiced_quantity,
766 rctl.uom_code,
767 msi.primary_uom_code, TO_CHAR(NULL),
768 TO_CHAR(NULL)),
769 (-1)*inv_convert.inv_um_convert (rma_line.inventory_item_id, 7,
770 rma_line.invoiced_quantity,
771 rctl.uom_code, msi.primary_uom_code,
772 TO_CHAR(NULL), TO_CHAR(NULL))
773 * rctlgd.percent / 100,
774 rctlgd.acctd_amount,
775 rctlgd.code_combination_id
776 FROM
777 CST_BIS_MARGIN_BUILD cr,
778 ra_cust_trx_line_gl_dist_all rctlgd,
779 ra_customer_trx_lines_all rctl,
780 oe_order_headers_all rma,
781 oe_order_lines_all rma_line,
782 mtl_system_items msi,
783 -- new changes for intercompany invoicing
784 org_organization_definitions ood,
785 ra_customer_trx_all rct
789 and rct.org_id = l_ou_id
786 WHERE
787 cr.build_id = l_build_id
788 and rctl.org_id = l_ou_id
790 and rctlgd.org_id = l_ou_id
791 and rctl.line_type = 'LINE'
792 and rctl.customer_trx_id = rct.customer_trx_id
793 and rct.complete_flag = 'Y'
794 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
795 and rctlgd.gl_date is not null
796 and rctlgd.gl_date between cr.from_date and cr.to_date
797 and rctlgd.account_class = 'REV'
798 and rctlgd.account_set_flag = 'N'
799 and rctl.interface_line_context = 'ORDER ENTRY'
800 and rma.org_id = l_ou_id
801 and msi.inventory_item_id = rma_line.inventory_item_id
802 and msi.organization_id = rma_line.ship_from_org_id
803 and msi.inventory_item_flag = 'N'
804 and rma_line.org_id = l_ou_id
805 and rma_line.line_id = DECODE(INTERFACE_LINE_CONTEXT, 'ORDER ENTRY',
806 TO_NUMBER(rctl.interface_line_attribute6), -99999)
807 AND rma.ORDER_NUMBER = DECODE(INTERFACE_LINE_CONTEXT, 'ORDER ENTRY',
808 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE1), -99999)
809 and rma_line.line_category_code = 'RETURN'
810 -- and rma_line.s5+0 in (5,9)
811 -- work flow issue to be resolved with OM team
812 and rma.header_id = rma_line.header_id
813 -- new changes for intercompany invoicing
814 and ood.organization_id = msi.organization_id
815 and ood.operating_unit = NVL(l_ou_id,NVL(rct.org_id, -999));
816 */
817
818 /*---------------------------------------------------------------+
819 | Update all the rows with parent_line_id if link_to_line_id is
820 | not null
821 +---------------------------------------------------------------*/
822
823 l_stmt_id := 50;
824
825 UPDATE CST_BIS_MARGIN_SUMMARY rma
826 SET (
827 parent_rowid ,
828 order_number,
829 header_id,
830 order_type_id,
831 customer_id ,
832 primary_salesrep_id,
833 sales_channel_code,
834 parent_inventory_item_id,
835 parent_organization_id,
836 parent_line_id,
837 parent_line_number,
838 parent_ato_flag,
839 parent_item_type_code,
840 inventory_item_id,
841 organization_id,
842 line_id,
843 line_number,
844 line_type_code,
845 ship_to_site_use_id,
846 invoice_to_site_use_id,
847 invoice_quantity,
848 return_reference_type_code,
849 return_reference_id) =
850
851 (SELECT
852
853 sl_parent.rowid,
854 sh.order_number,
855 sh.header_id ,
856 sh.order_type_id,
857 sh.sold_to_org_id,
858 sh.salesrep_id,
859 sh.sales_channel_code,
860 sl_parent.inventory_item_id,
861 sl_parent.ship_from_org_id,
862 sl_parent.line_id,
863 sl_parent.line_number,
864 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
865 sl_parent.item_type_code,
866 sl_child.inventory_item_id,
867 sl_child.ship_from_org_id,
868 sl_child.line_id,
869 sl_child.line_number,
870 sl_child.line_category_code,
871 sl_child.ship_to_org_id,
872 sh.invoice_to_org_id,
873 decode (rma.rma_inventory_item_id, sl_parent.inventory_item_id,
874 rma.invoice_quantity, 0),
875 sl_child.return_context,
876 sl_child.reference_line_id
877 FROM
878 oe_order_lines_all sl_parent,
879 oe_order_lines_all sl_child,
880 oe_order_headers_all sh
881 WHERE
882 sl_parent.org_id = l_ou_id
883 and sl_child.org_id = l_ou_id
884 and sl_child.line_category_code = 'ORDER'
885 and sl_parent.line_category_code = 'ORDER'
886 and sl_parent.line_id = nvl(sl_child.top_model_line_id,
887 sl_child.line_id)
888 and sl_parent.line_id = rma.link_to_line_id
889 and sl_child.line_id = rma.link_to_line_id
890 and sh.org_id = l_ou_id
891 and sh.header_id = sl_child.header_id
892 and sh.header_id = sl_parent.header_id
893 )
894 WHERE
895 rma.link_to_line_id is not null
896 and rma.row_type = 3
897 and rma.source='RMA-INVOICE'
898 and rma.gl_date between l_from_date and l_to_date
899 and rma.build_id = l_build_id;
900
901
902 /*---------------------------------------------------------------+
903 | Insert in temp table for all the ICAR - RMA Invoices
904 +---------------------------------------------------------------*/
905
906 l_stmt_id := 60;
907
908 /*--------------------------------------------------------------+
909 | Date: 09-Mar-2004
913
910 | Developer: ADWAJAN
911 | Comments: Code for collecting AR for IC RMA transactions
912 +-------------------------------------------------------------*/
914 INSERT INTO CST_BIS_MARGIN_SUMMARY
915 (
916 margin_pk
917 ,build_id
918 ,source
919 ,row_type
920 ,origin
921 ,invoice_source
922 ,parent_rowid
923 ,legal_entity_id
924 ,org_id
925 ,order_type_id
926 ,customer_id
927 ,primary_salesrep_id
928 ,sales_channel_code
929 ,parent_inventory_item_id
930 ,parent_organization_id
931 ,parent_line_id
932 ,parent_line_number
933 ,parent_item_type_code
934 ,parent_ato_flag
935 ,ship_to_site_use_id
936 ,invoice_to_site_use_id
937 ,customer_trx_id
938 ,customer_trx_line_id
939 ,original_gl_date
940 ,gl_date
941 ,order_number
942 ,rma_number
943 ,header_id
944 ,rma_header_id
945 ,inventory_item_id
946 ,rma_inventory_item_id
947 ,line_id
948 ,rma_line_id
949 ,line_number
950 ,rma_line_number
951 ,rma_ship_to_site_use_id
952 ,line_type_code
953 ,rma_line_type_code
954 ,link_to_line_id
955 ,invoice_line_quantity
956 ,invoice_quantity
957 ,invoiced_amount
958 ,sales_account
959 )
960 SELECT
961 'RMA-ICAR_'||rctlgd.CUST_TRX_LINE_GL_DIST_ID,
962 l_build_id,
963 'RMA-ICAR',
964 '7',
965 '2',
966 rctl.interface_line_context,
967 rma_line.rowid,
968 l_le_id,
969 NVL(l_ou_id, rma.org_id),
970 rma.order_type_id,
971 rma.sold_to_org_id,
972 rma.salesrep_id,
973 rma.sales_channel_code,
974 rma_line.inventory_item_id,
975 rma_line.ship_from_org_id,
976 rma_line.line_id,
977 rma_line.line_number,
978 rma_line.item_type_code,
979 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
980 rma_line.ship_to_org_id,
981 rma.invoice_to_org_id,
982 rct.CUSTOMER_TRX_ID,
983 rctl.CUSTOMER_TRX_LINE_ID,
984 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
985 rctlgd.gl_date,
986 rma.order_number,
987 rma.order_number,
988 rma.header_id,
989 rma.header_id,
990 rctl.inventory_item_id,
991 rctl.inventory_item_id,
992 rma_line.line_id,
993 rma_line.line_id,
994 rma_line.line_number,
995 rma_line.line_number,
996 rma_line.ship_to_org_id,
997 rma_line.line_category_code,
998 rma_line.line_category_code,
999 rma_line.link_to_line_id,
1000 (-1) * rma_line.SHIPPED_QUANTITY,
1001 (-1) * rma_line.SHIPPED_QUANTITY * rctlgd.percent / 100,
1002 rctlgd.acctd_amount,
1003 rctlgd.code_combination_id
1004 FROM
1005 CST_BIS_MARGIN_BUILD cr,
1006 ra_cust_trx_line_gl_dist_all rctlgd,
1007 ra_customer_trx_lines_all rctl,
1008 oe_order_headers_all rma,
1009 oe_order_lines_all rma_line,
1010 --hr_organization_information hoi,
1011 ra_customer_trx_all rct,
1012 mtl_parameters mp -- INVCONV umoogala 17-oct-2004
1013 WHERE
1014 cr.build_id = l_build_id
1015 and rctl.org_id = l_ou_id
1016 and rct.org_id = l_ou_id
1017 and rctlgd.org_id = l_ou_id
1018 and rctl.line_type = 'LINE'
1019 and rctl.customer_trx_id = rct.customer_trx_id
1020 and rct.batch_source_id = 8
1021 and rct.complete_flag = 'Y'
1022 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
1023 and rctl.interface_line_context = 'INTERCOMPANY'
1024 and rctlgd.gl_date is not null
1025 and rctlgd.gl_date between cr.from_date and cr.to_date
1026 and rctlgd.account_class = 'REV'
1027 and rctlgd.account_set_flag = 'N'
1028 and rma_line.line_id = DECODE(INTERFACE_LINE_CONTEXT, 'INTERCOMPANY',
1029 TO_NUMBER(rctl.interface_line_attribute6), -99999)
1030 and rma.ORDER_NUMBER = DECODE(INTERFACE_LINE_CONTEXT, 'INTERCOMPANY',
1031 TO_NUMBER(RCTL.INTERFACE_LINE_ATTRIBUTE1), -99999)
1032 and (rma_line.source_document_type_id IS NULL
1033 OR rma_line.source_document_type_id <> 10)
1034 and rma_line.line_category_code = 'RETURN'
1035 and rma.header_id = rma_line.header_id
1036 /* INVCONV umoogala 17-oct-2004 */
1037 and mp.organization_id = rma_line.ship_from_org_id
1038 and mp.process_enabled_flag <> 'Y';
1039 -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
1040 -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
1041
1042
1046 +---------------------------------------------------------------*/
1043 /*---------------------------------------------------------------+
1044 | Insert in temp table all data for CR-memos not related to any
1045 | RMA but related to an invoice selected earlier
1047
1048 l_stmt_id := 80;
1049
1050 INSERT INTO CST_BIS_MARGIN_SUMMARY
1051 (
1052 margin_pk,
1053 build_id
1054 ,source
1055 ,row_type
1056 ,origin
1057 ,invoice_source
1058 ,parent_rowid
1059 ,order_number
1060 ,header_id
1061 ,legal_entity_id
1062 ,org_id
1063 ,order_type_id
1064 ,customer_id
1065 ,primary_salesrep_id
1066 ,sales_channel_code
1067 ,parent_inventory_item_id
1068 ,parent_organization_id
1069 ,parent_line_id
1070 ,parent_line_number
1071 ,parent_item_type_code
1072 ,parent_ato_flag
1073 ,inventory_item_id
1074 ,organization_id
1075 ,line_id
1076 ,line_type_code
1077 ,line_number
1078 ,ship_to_site_use_id
1079 ,invoice_to_site_use_id
1080 ,customer_trx_id
1081 ,customer_trx_line_id
1082 ,original_gl_date
1083 ,gl_date
1084 ,invoice_line_quantity
1085 ,invoice_quantity
1086 ,invoiced_amount
1087 ,sales_account
1088 ,cr_trx_id
1089 ,cr_trx_line_id
1090 )
1091 SELECT
1092 'CR_INV-'||rctlgd.CUST_TRX_LINE_GL_DIST_ID,
1093 l_build_id,
1094 'CR-INVOICE',
1095 '5',
1096 '3',
1097 rctl.interface_line_context,
1098 temp.parent_rowid,
1099 temp.order_number,
1100 temp.header_id,
1101 l_le_id,
1102 NVL(l_ou_id, rct.org_id),
1103 temp.order_type_id,
1104 temp.customer_id,
1105 temp.primary_salesrep_id,
1106 temp.sales_channel_code,
1107 temp.parent_inventory_item_id,
1108 temp.parent_organization_id,
1109 temp.parent_line_id,
1110 temp.parent_line_number,
1111 temp.parent_item_type_code,
1112 temp.parent_ato_flag,
1113 temp.inventory_item_id,
1114 temp.organization_id,
1115 temp.line_id,
1116 temp.line_type_code,
1117 temp.line_number,
1118 temp.ship_to_site_use_id,
1119 temp.invoice_to_site_use_id,
1120 temp.customer_trx_id,
1121 temp.customer_trx_line_id,
1122 decode(rctlgd.original_gl_date, null, rctlgd.gl_date),
1123 rctlgd.gl_date,
1124 0,
1125 0,
1126 rctlgd.acctd_amount,
1127 rctlgd.code_combination_id,
1128 rct.CUSTOMER_TRX_ID,
1129 rctl.CUSTOMER_TRX_LINE_ID
1130 FROM
1131 CST_BIS_MARGIN_SUMMARY temp,
1132 ra_customer_trx_all rct,
1133 ra_customer_trx_lines_all rctl,
1134 ra_cust_trx_line_gl_dist_all rctlgd
1135 WHERE
1136 temp.build_id = l_build_id
1137 and rctl.org_id = l_ou_id
1138 and rctl.line_type = 'LINE'
1139 and rct.org_id = l_ou_id
1140 and rctl.customer_trx_id = rct.customer_trx_id
1141 and rct.complete_flag = 'Y'
1142 and rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
1143 and EXISTS ( select '1' from ra_cust_trx_types rctt
1144 where rct.cust_trx_type_id = rctt.cust_trx_type_id
1145 and rctt.type = 'CM')
1146 and rctlgd.org_id = l_ou_id
1147 and rctlgd.gl_date is not NULL
1148 -- fix for bug 2609688
1149 and rctlgd.gl_date BETWEEN l_from_date AND l_to_date
1150 and rctlgd.account_class = 'REV'
1151 and rctlgd.account_set_flag = 'N'
1152 and rctl.LINK_TO_CUST_TRX_LINE_ID is null
1153 and rctl.previous_customer_trx_line_id = temp.CUSTOMER_TRX_LINE_ID
1154 and rctl.previous_customer_trx_id = temp.customer_trx_id
1155 /* new conditions added to improve performance bug 2554225 */
1156 and temp.SOURCE = 'INVOICE'
1157 /* added join to org_id bug 2554225 */
1158 and temp.org_id = l_ou_id
1159 /* bug 2397230 */
1160 and temp.rowid in (select max(rowid) from CST_BIS_MARGIN_SUMMARY t1
1161 where t1.build_id = temp.build_id
1162 and t1.source = 'INVOICE'
1163 /* added join to org_id bug 2554225 */
1164 and t1.org_id = l_ou_id
1165 and t1.header_id = temp.header_id
1166 and t1.line_id = temp.line_id
1167 and t1.CUSTOMER_TRX_LINE_ID =
1168 temp.CUSTOMER_TRX_LINE_ID
1169 group by t1.order_number ,
1170 t1.line_number,
1171 t1.CUSTOMER_TRX_LINE_ID )
1172 and not exists
1173 (select 'x'
1177 and t2.build_id = temp.build_id
1174 from CST_BIS_MARGIN_SUMMARY t2,
1175 oe_order_lines_all oel
1176 where t2.source = 'RMA-INVOICE'
1178 /* added join to org_id bug 2554225 */
1179 and t2.org_id = l_ou_id
1180 and temp.header_id = oel.reference_header_id
1181 and temp.line_id = oel.reference_line_id
1182 and oel.header_id = t2.rma_header_id
1183 and oel.line_id = t2.rma_line_id
1184 );
1185
1186 /*---------------------------------------------------------------+
1187 | Insert in temp table all data for Cost of Goods Sold for
1188 | regular invoices
1189 +---------------------------------------------------------------*/
1190
1191 l_stmt_id := 90;
1192
1193 /*--------------------------------------------------------------+
1194 | Date: 03-Nov-2003
1195 | Developer: ADWAJAN
1196 | Comments: Additional condition in the where clause to
1197 | calculate COGS for the logical txns in the
1198 | Drop Ship scenario - 11.5.10 Impact Analysis
1199 +-------------------------------------------------------------*/
1200
1201 /*
1202 Date Author Comments
1203 05/08/2006 Suhasini To enable deferred COGS accounting, 2 new accounting_line_types
1204 are being introduced in MTA. As this collection program collects
1205 only recognized revenue for COGS and RMA the COGS recognized should
1206 also be in proportion to trhe revenue realized. This is attained by
1207 the valuations with accounting_line_type as 35, 36. This collection
1208 would now collect for accounting_line_type = 2 (COGS valuation in the
1209 absence of deferred COGS accounting at customer) and accounting_line_type
1210 = 35 (Deferred COGS recognized, in the presence of deferred COGS acc)
1211 */
1212 INSERT INTO CST_BIS_MARGIN_SUMMARY
1213 (
1214 margin_pk,
1215 build_id
1216 ,source
1217 ,row_type
1218 ,origin
1219 ,parent_rowid
1220 ,order_number
1221 ,header_id
1222 ,legal_entity_id
1223 ,org_id
1224 ,order_type_id
1225 ,customer_id
1226 ,primary_salesrep_id
1227 ,sales_channel_code
1228 ,parent_inventory_item_id
1229 ,parent_organization_id
1230 ,parent_line_id
1231 ,parent_line_number
1232 ,parent_item_type_code
1233 ,parent_ato_flag
1234 ,inventory_item_id
1235 ,organization_id
1236 ,line_id
1237 ,line_type_code
1238 ,line_number
1239 ,ship_to_site_use_id
1240 ,invoice_to_site_use_id
1241 ,original_gl_date
1242 ,gl_date
1243 ,order_line_quantity
1244 ,ship_quantity
1245 ,cogs_amount
1246 ,cogs_account
1247 )
1248 SELECT
1249 'COGS-'||mta.TRANSACTION_ID||'-'||mta.REFERENCE_ACCOUNT||'-'||mta.COST_ELEMENT_ID||'-'||mta.GL_SL_LINK_ID,
1250 l_build_id,
1251 'COGS',
1252 '2',
1253 '1',
1254 sl_parent.rowid,
1255 sh.order_number,
1256 sh.header_id,
1257 l_le_id,
1258 ood.operating_unit, --NVL(l_ou_id, sh.org_id),
1259 sh.order_type_id,
1260 sh.sold_to_org_id,
1261 sh.salesrep_id,
1262 sh.sales_channel_code,
1263 sl_parent.inventory_item_id,
1264 sl_parent.ship_from_org_id,
1265 sl_parent.line_id,
1266 sl_parent.line_number,
1267 sl_parent.item_type_code,
1268 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1269 sl_child.inventory_item_id,
1270 -- sl_child.ship_from_org_id,
1271 mmt.organization_id,
1272 mmt.trx_source_line_id,
1273 sl_child.line_category_code,
1274 sl_child.line_number,
1275 sl_child.ship_to_org_id,
1276 sh.invoice_to_org_id,
1277 mta.transaction_date,
1278 mta.transaction_date,
1279 sl_child.shipped_quantity,
1280 decode(decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1281 'N',decode(mmt.inventory_item_id, sl_parent.inventory_item_id,
1282 -- (-1) * decode(nvl(mta.cost_element_id,-1), 1,
1283 decode(nvl(mta.cost_element_id,-1), 1,
1284 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1285 0),
1286 -- may need to decode MODEL and KIT, to be confirmed
1287 'Y',decode(sl_parent.item_type_code, 'MODEL',
1288 -- (-1) * decode(nvl(mta.cost_element_id,-1),1,
1289 decode(nvl(mta.cost_element_id,-1),1,
1290 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1291 decode(mmt.inventory_item_id,
1292 sl_parent.inventory_item_id,
1293 -- (-1) * decode(nvl(mta.cost_element_id,-1),1,
1294 decode(nvl(mta.cost_element_id,-1),1,
1295 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1296 0)),
1297 decode(mmt.inventory_item_id, sl_parent.inventory_item_id,
1301 0)
1298 -- (-1) * decode(nvl(mta.cost_element_id,-1),1,
1299 decode(nvl(mta.cost_element_id,-1),1,
1300 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1302 ),
1303 mta.base_transaction_value,
1304 mta.reference_account
1305 FROM
1306 oe_order_headers_all sh,
1307 oe_order_lines_all sl_parent,
1308 oe_order_lines_all sl_child,
1309 mtl_material_transactions mmt,
1310 mtl_transaction_accounts mta,
1311 -- new changes for intercompany invoicing
1312 org_organization_definitions ood,
1313 CST_BIS_MARGIN_BUILD cr,
1314 mtl_parameters mp -- INVCONV umoogala 17-oct-2004
1315 WHERE
1316 cr.build_id = l_build_id
1317 and (
1318 (mmt.transaction_source_type_id = 2 -- Regular Sales Orders
1319 and mta.transaction_source_type_id = 2)
1320 or
1321 (mmt.transaction_source_type_id = 13 -- Logical Intercompany Sales Issue
1322 and mmt.transaction_action_id = 9
1323 and mta.transaction_source_type_id = 13)
1324 )
1325 and mmt.transaction_id = mta.transaction_id
1326 and mta.accounting_line_type in (2,35) -- Added in R12. To collect COGS when recognized.
1327 -- and sl_parent.org_id = l_ou_id
1328 -- and sl_child.org_id = l_ou_id
1329 and sl_child.line_id = mmt.trx_source_line_id
1330 and sl_child.line_category_code = 'ORDER'
1331 and sl_parent.line_category_code = 'ORDER'
1332 and sl_parent.line_id = nvl(sl_child.top_model_line_id,
1333 sl_child.line_id)
1334 -- and sh.org_id = l_ou_id
1335 and sh.header_id = sl_child.header_id
1336 and mta.transaction_date between cr.from_date and cr.to_date
1337 -- new changes for intercompany invoicing
1338 and ood.organization_id = mmt.organization_id
1339 and ood.operating_unit = l_ou_id
1340 /* INVCONV umoogala 17-oct-2004 */
1341 and mp.organization_id = sl_child.ship_from_org_id
1342 and mp.process_enabled_flag <> 'Y';
1343 -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
1344 -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
1345
1346
1347 /*---------------------------------------------------------------+
1348 | Insert in temp table all data for IC-AP for
1349 | regular invoices
1350 +---------------------------------------------------------------*/
1351
1352 l_stmt_id := 95;
1353
1354 /*--------------------------------------------------------------+
1355 | Date: 28-Dec-2003
1356 | Developer: ADWAJAN
1357 | Comments: Additional condition in the where clause to
1358 | filter out the COGS value related to
1359 | the DropShip Scenarios (logical I/C txns)
1360 | The COGS from logical I/C txns are handled
1361 | in the regular Invoices part of the code
1362 | (l_stmt_id := 90).
1363 +-------------------------------------------------------------*/
1364
1365 INSERT INTO CST_BIS_MARGIN_SUMMARY
1366 (
1367 margin_pk,
1368 build_id
1369 ,source
1370 ,row_type
1371 ,origin
1372 ,parent_rowid
1373 ,order_number
1374 ,header_id
1375 ,legal_entity_id
1376 ,org_id
1377 ,order_type_id
1378 ,customer_id
1379 ,primary_salesrep_id
1380 ,sales_channel_code
1381 ,parent_inventory_item_id
1382 ,parent_organization_id
1383 ,parent_line_id
1384 ,parent_line_number
1385 ,parent_item_type_code
1386 ,parent_ato_flag
1387 ,inventory_item_id
1388 ,organization_id
1389 ,line_id
1390 ,line_type_code
1391 ,line_number
1392 ,ship_to_site_use_id
1393 ,invoice_to_site_use_id
1394 ,original_gl_date
1395 ,gl_date
1396 ,order_line_quantity
1397 ,ship_quantity
1398 ,cogs_amount
1399 ,cogs_account
1400 )
1401 SELECT
1402 'ICAP-'||aid.INVOICE_DISTRIBUTION_ID,
1403 l_build_id,
1404 'IC-AP',
1405 '6',
1406 '1',
1407 sl_parent.rowid,
1408 sh.order_number,
1409 sh.header_id,
1410 l_le_id,
1411 NVL(l_ou_id, sh.org_id),
1412 sh.order_type_id,
1413 sh.sold_to_org_id,
1414 sh.salesrep_id,
1415 sh.sales_channel_code,
1416 sl_parent.inventory_item_id,
1417 sl_parent.ship_from_org_id,
1418 sl_parent.line_id,
1419 sl_parent.line_number,
1420 sl_parent.item_type_code,
1421 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1422 sl_child.inventory_item_id,
1423 sl_child.ship_from_org_id,
1424 sl_child.line_id,
1425 sl_child.line_category_code,
1426 sl_child.line_number,
1427 sl_child.ship_to_org_id,
1428 sh.invoice_to_org_id,
1429 aid.accounting_date,
1433 NVL(aid.amount, 0),
1430 aid.accounting_date,
1431 sl_child.shipped_quantity,
1432 rcl.quantity_invoiced,
1434 aid.dist_code_combination_id
1435 FROM
1436 CST_BIS_MARGIN_BUILD cr,
1437 ap_invoice_distributions_all aid,
1438 ap_invoices_all ai,
1439 oe_order_headers_all sh,
1440 oe_order_lines_all sl_parent,
1441 oe_order_lines_all sl_child,
1442 ra_customer_trx_lines_all rcl,
1443 mtl_material_transactions mmt ,
1444 mtl_parameters mp -- INVCONV umoogala 17-oct-2004
1445 WHERE
1446 ai.invoice_id = aid.invoice_id
1447 and ai.source = 'Intercompany'
1448 and ai.org_id = aid.org_id
1449 and rcl.customer_trx_line_id = to_number(aid.reference_1)
1450 and cr.build_id = l_build_id
1451 and sl_parent.org_id = l_ou_id
1452 and sl_child.org_id = l_ou_id
1453 and sh.org_id = l_ou_id
1454 and sl_child.line_id = rcl.interface_line_attribute6
1455 and sl_child.line_category_code = 'ORDER'
1456 AND ( sl_child.source_document_type_id IS NULL
1457 OR sl_child.source_document_type_id <> 10 )
1458 and sl_parent.line_category_code = 'ORDER'
1459 and sl_parent.line_id = nvl(sl_child.top_model_line_id,sl_child.line_id)
1460 and sh.header_id = sl_child.header_id
1461 and sh.header_id = sl_parent.header_id
1462 and aid.accounting_date between cr.from_date and cr.to_date
1463 and aid.line_type_lookup_code IN ('ITEM','ACCRUAL') --Invoice Lines Project
1464 and mmt.transaction_id = rcl.interface_line_attribute7
1465 and nvl(mmt.logical_transaction,0) <> 1
1466 /* INVCONV umoogala 17-oct-2004 */
1467 and mp.organization_id = sl_child.ship_to_org_id
1468 and mp.process_enabled_flag <> 'Y';
1469 -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
1470 -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
1471
1472
1473 /*---------------------------------------------------------------+
1474 | Insert in temp table all data for Cost of Goods Sold for
1475 | RMA transactions
1476 +---------------------------------------------------------------*/
1477
1478 l_stmt_id := 100;
1479 /*--------------------------------------------------------------+
1480 | Date: 03-Nov-2003
1481 | Developer: ADWAJAN
1482 | Comments: Additional condition in the where clause to
1483 | calculate COGS for the logical txns in the
1484 | Drop Ship scenario - 11.5.10 Impact Analysis
1485 +-------------------------------------------------------------*/
1486
1487 INSERT INTO CST_BIS_MARGIN_SUMMARY
1488 (
1489 margin_pk,
1490 build_id
1491 ,source
1492 ,row_type
1493 ,origin
1494 ,parent_rowid
1495 ,legal_entity_id
1496 ,org_id
1497 ,order_type_id
1498 ,customer_id
1499 ,primary_salesrep_id
1500 ,sales_channel_code
1501 ,parent_inventory_item_id
1502 ,parent_organization_id
1503 ,parent_line_id
1504 ,parent_line_number
1505 ,parent_item_type_code
1506 ,parent_ato_flag
1507 ,ship_to_site_use_id
1508 ,invoice_to_site_use_id
1509 ,original_gl_date
1510 ,gl_date
1511 ,order_number
1512 ,rma_number
1513 ,header_id
1514 ,rma_header_id
1515 ,inventory_item_id
1516 ,rma_inventory_item_id
1517 ,organization_id
1518 ,rma_organization_id
1519 ,line_id
1520 ,rma_line_id
1521 ,line_number
1522 ,rma_line_number
1523 ,rma_ship_to_site_use_id
1524 ,line_type_code
1525 ,rma_line_type_code
1526 ,link_to_line_id
1527 ,ship_quantity
1528 ,cogs_amount
1529 )
1530 SELECT
1531 'RMA-COGS-'||mta.TRANSACTION_ID||'-'||mta.REFERENCE_ACCOUNT||'-'||mta.COST_ELEMENT_ID||'-'||mta.GL_SL_LINK_ID,
1532 l_build_id,
1533 'RMA-COGS',
1534 '4',
1535 '2',
1536 rma_line.rowid,
1537 l_le_id,
1538 ood.operating_unit, -- NVL(l_ou_id, rma.org_id),
1539 rma.order_type_id,
1540 rma.sold_to_org_id,
1541 rma.salesrep_id,
1542 rma.sales_channel_code,
1543 rma_line.inventory_item_id,
1544 rma_line.ship_from_org_id,
1545 rma_line.line_id,
1546 rma_line.line_number,
1547 rma_line.item_type_code,
1548 decode(rma_line.ato_line_id, NULL, 'N', 'Y'),
1549 rma_line.ship_to_org_id,
1550 rma.invoice_to_org_id,
1551 mta.transaction_date,
1552 mta.transaction_date,
1553 rma.order_number,
1554 rma.order_number,
1555 rma.header_id,
1556 rma.header_id,
1557 mmt.inventory_item_id,
1558 mmt.inventory_item_id,
1559 mmt.organization_id,
1563 rma_line.line_number,
1560 mmt.organization_id,
1561 rma_line.line_id,
1562 rma_line.line_id,
1564 rma_line.line_number,
1565 rma_line.ship_to_org_id,
1566 rma_line.line_category_code,
1567 rma_line.line_category_code,
1568 rma_line.link_to_line_id,
1569 (-1)* decode(nvl(mta.cost_element_id,-1),1,
1570 mmt.primary_quantity,-1,mmt.primary_quantity,0),
1571 mta.base_transaction_value
1572 FROM
1573 CST_BIS_MARGIN_BUILD cr,
1574 oe_order_headers_all rma,
1575 oe_order_lines_all rma_line,
1576 mtl_material_transactions mmt,
1577 -- new changes for intercompany invoicing
1578 org_organization_definitions ood,
1579 mtl_transaction_accounts mta,
1580 mtl_parameters mp -- INVCONV umoogala 17-oct-2004
1581 WHERE
1582 cr.build_id = l_build_id
1583 and (
1584 (mmt.transaction_source_type_id = 12 -- RMA
1585 and mta.transaction_source_type_id = 12)
1586 or
1587 (mmt.transaction_source_type_id = 13 -- Logical Intercompany Sales Return
1588 and mmt.transaction_action_id = 14
1589 and mta.transaction_source_type_id = 13)
1590 )
1591 and mmt.transaction_id = mta.transaction_id
1592 and mta.accounting_line_type in (2,35) -- To collect recognized COGS/RMA alone.
1593 -- and rma_line.org_id = l_ou_id
1594 and rma_line.line_id = mmt.trx_source_line_id
1595 and rma_line.line_category_code in ('RETURN')
1596 -- and rma.org_id = l_ou_id
1597 and rma.header_id = rma_line.header_id
1598 and mta.transaction_date between cr.from_date and cr.To_date
1599 -- new changes for intercompany invoicing
1600 and ood.organization_id = mmt.organization_id
1601 and ood.operating_unit = NVL(l_ou_id,NVL(rma_line.org_id, -999))
1602 /* INVCONV umoogala 17-oct-2004 */
1603 and mp.organization_id = rma_line.ship_from_org_id
1604 and mp.process_enabled_flag <> 'Y';
1605 -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
1606 -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
1607
1608
1609 /*---------------------------------------------------------------+
1610 | Update all the COGS rows with parent_line_id if link_to_line_id
1611 | is not null
1612 +---------------------------------------------------------------*/
1613
1614 l_stmt_id := 110;
1615
1616 UPDATE CST_BIS_MARGIN_SUMMARY rma
1617 SET (
1618 parent_rowid ,
1619 order_number,
1620 header_id,
1621 order_type_id,
1622 customer_id ,
1623 primary_salesrep_id,
1624 sales_channel_code,
1625 parent_inventory_item_id,
1626 parent_organization_id,
1627 parent_line_id,
1628 parent_line_number,
1629 parent_ato_flag,
1630 parent_item_type_code,
1631 inventory_item_id,
1632 organization_id,
1633 line_id,
1634 line_number,
1635 line_type_code,
1636 ship_to_site_use_id,
1637 invoice_to_site_use_id,
1638 ship_quantity,
1639 return_reference_type_code,
1640 return_reference_id) =
1641
1642 (SELECT
1643
1644 sl_parent.rowid,
1645 sh.order_number,
1646 sh.header_id ,
1647 sh.order_type_id,
1648 sh.sold_to_org_id,
1649 sh.salesrep_id,
1650 sh.sales_channel_code,
1651 sl_parent.inventory_item_id,
1652 sl_parent.ship_from_org_id,
1653 sl_parent.line_id,
1654 sl_parent.line_number,
1655 decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1656 sl_parent.item_type_code,
1657 sl_child.inventory_item_id,
1658 sl_child.ship_from_org_id,
1659 sl_child.line_id,
1660 sl_child.line_number,
1661 sl_child.line_category_code,
1662 sl_child.ship_to_org_id,
1663 sh.invoice_to_org_id,
1664 decode(decode(sl_parent.ato_line_id, NULL, 'N', 'Y'),
1665 'N',decode(rma.rma_inventory_item_id,
1666 sl_parent.inventory_item_id,
1667 rma.ship_quantity, 0),
1668 'Y',decode(sl_parent.item_type_code, 'MODEL',
1669 rma.ship_quantity,
1670 decode(rma.rma_inventory_item_id,
1671 sl_parent.inventory_item_id,
1672 rma.ship_quantity,
1673 0)),
1674 decode(rma.rma_inventory_item_id, sl_parent.inventory_item_id,
1675 rma.ship_quantity, 0)
1676 ),
1677 sl_child.return_context,
1678 sl_child.reference_line_id
1679 FROM
1680 oe_order_lines_all sl_parent,
1681 oe_order_lines_all sl_child,
1685 NVL(l_ou_id, NVL(sl_parent.org_id, -999))
1682 oe_order_headers_all sh
1683 WHERE
1684 NVL(sl_parent.org_id, -999) =
1686 and NVL(sl_child.org_id, -999) =
1687 NVL(l_ou_id, NVL(sl_child.org_id, -999))
1688 and sl_child.line_category_code = 'ORDER'
1689 and sl_parent.line_category_code = 'ORDER'
1690 and sl_parent.line_id = nvl(sl_child.top_model_line_id,
1691 sl_child.line_id)
1692 and sl_parent.line_id = rma.link_to_line_id
1693 and sl_child.line_id = rma.link_to_line_id
1694 and NVL(sh.org_id, -999) = NVL(l_ou_id,NVL(sh.org_id, -999))
1695 and sh.header_id = sl_child.header_id
1696 and sh.header_id = sl_parent.header_id
1697 )
1698 WHERE
1699 rma.link_to_line_id is not null
1700 and rma.row_type = 4
1701 and rma.source='RMA-COGS'
1702 and rma.gl_date between l_from_date and l_to_date
1703 and rma.build_id = l_build_id;
1704
1705 END LOOP; /* Operating Unit Loop */
1706
1707 close all_ous;
1708
1709 /*---------------------------------------------------------------+
1710 | Update territory_id
1711 +---------------------------------------------------------------*/
1712 -- Changed to use hz_cust_site_uses_all instead of ra_site_uses_all
1713 -- as part of Uptake for R12
1714
1715 l_stmt_id := 140;
1716
1717 UPDATE CST_BIS_MARGIN_SUMMARY temp
1718 SET territory_id =
1719 (SELECT territory_id
1720 FROM hz_cust_site_uses_all hsu -- Object ra_site_uses_all obsoleted in R12
1721 WHERE NVL(hsu.org_id, -999) = NVL(l_ou_id, NVL(hsu.org_id, -999))
1722 AND hsu.site_use_id = temp.ship_to_site_use_id )
1723 -- ra_site_uses_all.site_use_id migrated to hz_cust_site_uses_all.site_use_id
1724 WHERE
1725 ship_to_site_use_id is not null
1726 and gl_date between l_from_date and l_to_date
1727 and build_id = l_build_id;
1728
1729 /*---------------------------------------------------------------+
1730 | Update customer class code
1731 +---------------------------------------------------------------*/
1732 -- Changed to use hz_cust_accounts instead of ra_customers
1733 -- as part of Uptake for R12
1734
1735 l_stmt_id := 150;
1736
1737 UPDATE CST_BIS_MARGIN_SUMMARY temp
1738 SET customer_class_code =
1739 (SELECT customer_class_code
1740 FROM hz_cust_accounts -- Object ra_customers obsoleted in R12
1741 WHERE cust_account_id = temp.customer_id) -- ra_customers.customer_id migrated to hz_cust_accounts.cust_account_id
1742 WHERE
1743 customer_id is not null
1744 and gl_date between l_from_date and l_to_date
1745 and build_id = l_build_id;
1746
1747 /*---------------------------------------------------------------+
1748 | Update sold to customer name
1749 +---------------------------------------------------------------*/
1750 -- Changed to use hz_cust_accounts and hz_parties instead of ra_customers
1751 -- as part of Uptake for R12
1752
1753 l_stmt_id := 160;
1754
1755 UPDATE CST_BIS_MARGIN_SUMMARY temp
1756 SET sold_to_customer_name =
1757 (SELECT hp.party_name -- references ra_customers.customer_name
1758 FROM hz_cust_accounts hca, hz_parties hp -- Object ra_customers obsoleted in R12
1759 WHERE hca.party_id = hp.party_id
1760 AND hca.cust_account_id = temp.customer_id)
1761 WHERE
1762 customer_id is not null
1763 and gl_date between l_from_date and l_to_date
1764 and build_id = l_build_id;
1765
1766 /*---------------------------------------------------------------+
1767 | Update bill to customer name
1768 +---------------------------------------------------------------*/
1769 -- Changed to use hz_cust_accounts, hz_parties, hz_cust_site_uses_all,
1770 -- hz_cust_acct_sites_all as part of Uptake for R12
1771
1772 l_stmt_id := 170;
1773
1774 UPDATE CST_BIS_MARGIN_SUMMARY temp
1775 SET bill_to_customer_name =
1776 (SELECT hp.party_name
1777 FROM hz_cust_accounts hca
1778 , hz_parties hp
1779 , hz_cust_site_uses_all hsu
1780 , hz_cust_acct_sites_all ha
1781 WHERE
1782 NVL(ha.org_id, -999) = NVL(l_ou_id,NVL(ha.org_id, -999))
1783 and hca.party_id = hp.party_id
1784 and NVL(hsu.org_id, -999) = NVL(l_ou_id, NVL(hsu.org_id, -999))
1785 and hca.cust_account_id = ha.cust_account_id
1786 and ha.cust_acct_site_id = hsu.cust_acct_site_id
1787 and hsu.site_use_id = temp.invoice_to_site_use_id)
1788 WHERE
1789 customer_id is not null
1790 and gl_date between l_from_date and l_to_date
1791 and build_id = l_build_id;
1792
1793 /* UPDATE CST_BIS_MARGIN_SUMMARY temp
1794 SET bill_to_customer_name =
1795 (SELECT rc.customer_name
1796 FROM ra_customers rc,
1797 ra_site_uses_all rsu,
1798 ra_addresses_all ra
1802 and NVL(rsu.org_id, -999) = NVL(l_ou_id, NVL(rsu.org_id, -999))
1799 WHERE
1800 NVL(ra.org_id, -999) = NVL(l_ou_id,NVL(ra.org_id, -999))
1801 and rc.customer_id = ra.customer_id
1803 and ra.address_id = rsu.address_id
1804 and rsu.site_use_id = temp.invoice_to_site_use_id)
1805 WHERE
1806 customer_id is not null
1807 and gl_date between l_from_date and l_to_date
1808 and build_id = l_build_id;
1809 */
1810
1811 /*---------------------------------------------------------------+
1812 | Update ship to customer name
1813 +---------------------------------------------------------------*/
1814 -- Changed to use hz_cust_accounts, hz_parties, hz_cust_site_uses_all,
1815 -- hz_cust_acct_sites_all as part of Uptake for R12
1816
1817 l_stmt_id := 180;
1818
1819 UPDATE CST_BIS_MARGIN_SUMMARY temp
1820 SET ship_to_customer_name =
1821 (SELECT hp.party_name
1822 FROM hz_cust_accounts hca
1823 , hz_parties hp
1824 , hz_cust_site_uses_all hsu
1825 , hz_cust_acct_sites_all ha
1826 WHERE
1827 NVL(ha.org_id, -999) = NVL(l_ou_id,NVL(ha.org_id, -999))
1828 and hca.party_id = hp.party_id
1829 and NVL(hsu.org_id, -999) = NVL(l_ou_id, NVL(hsu.org_id, -999))
1830 and hca.cust_account_id = ha.cust_account_id
1831 and ha.cust_acct_site_id = hsu.cust_acct_site_id
1832 and hsu.site_use_id = temp.ship_to_site_use_id)
1833 WHERE
1834 customer_id is not null
1835 and gl_date between l_from_date and l_to_date
1836 and build_id = l_build_id;
1837
1838 /*
1839 UPDATE CST_BIS_MARGIN_SUMMARY temp
1840 SET ship_to_customer_name =
1841 (SELECT rc.customer_name
1842 FROM ra_customers rc,
1843 ra_site_uses_all rsu,
1844 ra_addresses_all ra
1845 WHERE
1846 NVL(ra.org_id, -999) = NVL(l_ou_id,NVL(ra.org_id, -999))
1847 and rc.customer_id = ra.customer_id
1848 and ra.address_id = rsu.address_id
1849 and NVL(rsu.org_id, -999) = NVL(l_ou_id, NVL(rsu.org_id, -999))
1850 and rsu.site_use_id = temp.ship_to_site_use_id)
1851 WHERE
1852 customer_id is not null
1853 and gl_date between l_from_date and l_to_date
1854 and build_id = l_build_id;
1855 */
1856
1857 /*---------------------------------------------------------------+
1858 | Update Period Year
1859 +---------------------------------------------------------------*/
1860
1861 l_stmt_id := 181;
1862
1863 update CST_BIS_MARGIN_SUMMARY cmt
1864 set (PERIOD_NAME_YEAR, PERIOD_NUM_YEAR) =
1865 (select gp.period_name, gp.PERIOD_YEAR
1866 from
1867 gl_periods gp,
1868 gl_sets_of_books gsob,
1869 hr_organization_information hoi
1870 where
1871 hoi.org_information1 = gsob.SET_OF_BOOKS_ID
1872 and hoi.org_information_context = 'Legal Entity Accounting'
1873 and gsob.period_set_name = gp.period_set_name
1874 and gp.ADJUSTMENT_PERIOD_FLAG = 'N'
1875 and cmt.legal_entity_id = hoi.organization_id
1876 and gp.PERIOD_TYPE = 'Year'
1877 and cmt.gl_date between gp.start_date and gp.end_date)
1878 where
1879 cmt.gl_date between l_from_date and l_to_date
1880 and cmt.build_id = l_build_id;
1881
1882
1883 /*---------------------------------------------------------------+
1884 | Update Period Quarter
1885 +---------------------------------------------------------------*/
1886
1887 l_stmt_id := 182;
1888 update CST_BIS_MARGIN_SUMMARY cmt
1889 set (PERIOD_NAME_QTR, PERIOD_NUM_QTR, PERIOD_SEQ_QTR) =
1890 (select gp.period_name, gp.period_num,
1891 gp.PERIOD_YEAR * 10 + gp.period_num
1892 from
1893 gl_periods gp,
1894 gl_sets_of_books gsob,
1895 hr_organization_information hoi
1896 where
1897 hoi.org_information1 = gsob.SET_OF_BOOKS_ID
1898 and hoi.org_information_context = 'Legal Entity Accounting'
1899 and gsob.period_set_name = gp.period_set_name
1900 and gp.ADJUSTMENT_PERIOD_FLAG = 'N'
1901 and cmt.legal_entity_id = hoi.organization_id
1902 and gp.PERIOD_TYPE = 'Quarter'
1903 and cmt.gl_date between gp.start_date and gp.end_date)
1904 where
1905 cmt.gl_date between l_from_date and l_to_date
1906 and cmt.build_id = l_build_id;
1907
1908 /*---------------------------------------------------------------+
1909 | Update Period Month
1910 +---------------------------------------------------------------*/
1911
1912 l_stmt_id := 183;
1913 update CST_BIS_MARGIN_SUMMARY cmt
1914 set (PERIOD_NAME_MONTH, PERIOD_NUM_MONTH, PERIOD_SEQ_MONTH) =
1915 (select gp.period_name, gp.period_num,
1916 gp.PERIOD_YEAR * 100 + gp.period_num
1917 from
1918 gl_periods gp,
1919 gl_sets_of_books gsob,
1920 hr_organization_information hoi
1921 where
1922 hoi.org_information1 = gsob.SET_OF_BOOKS_ID
1923 and hoi.org_information_context = 'Legal Entity Accounting'
1924 and gsob.period_set_name = gp.period_set_name
1925 and gp.ADJUSTMENT_PERIOD_FLAG = 'N'
1926 and cmt.legal_entity_id = hoi.organization_id
1927 and gp.PERIOD_TYPE = gsob.ACCOUNTED_PERIOD_TYPE
1928 and cmt.gl_date between gp.start_date and gp.end_date)
1929 where
1930 cmt.gl_date between l_from_date and l_to_date
1931 and cmt.build_id = l_build_id;
1932
1933 /*---------------------------------------------------------------+
1934 | Update Country level of Geography dimension
1935 +---------------------------------------------------------------*/
1939 l_stmt_id := 184;
1936 -- Changed to use hz_cust_accounts, hz_parties, hz_cust_site_uses_all,
1937 -- hz_cust_acct_sites_all as part of Uptake for R12
1938
1940
1941 update CST_BIS_MARGIN_SUMMARY cmt
1942 set COUNTRY_CODE =
1943 (select hl.country
1944 from hz_locations hl
1945 ,hz_cust_site_uses_all hcsu
1946 ,hz_cust_acct_sites_all hcas
1947 ,hz_party_sites hp
1948 where
1949 hcsu.org_id = cmt.org_id
1950 and hcsu.site_use_id = cmt.ship_to_site_use_id
1951 and hcsu.cust_acct_site_id = hcas.cust_acct_site_id
1952 and hcas.party_site_id = hp.party_site_id
1953 and hp.location_id = hl.location_id)
1954 where
1955 cmt.ship_to_site_use_id is not null
1956 and cmt.gl_date between l_from_date and l_to_date
1957 and cmt.build_id = l_build_id;
1958
1959 /*
1960 update CST_BIS_MARGIN_SUMMARY cmt
1961 set COUNTRY_CODE =
1962 (select raa.country
1963 from ra_site_uses_all rsua,
1964 ra_addresses_all raa
1965 where
1966 rsua.org_id = cmt.org_id
1967 and rsua.site_use_id = cmt.ship_to_site_use_id
1968 and rsua.address_id = raa.address_id)
1969 where
1970 cmt.ship_to_site_use_id is not null
1971 and cmt.gl_date between l_from_date and l_to_date
1972 and cmt.build_id = l_build_id;
1973 */
1974
1975 /*---------------------------------------------------------------+
1976 | Update Area level of Geography dimension
1977 +---------------------------------------------------------------*/
1978
1979
1980 l_stmt_id := 185;
1981 update CST_BIS_MARGIN_SUMMARY cmt
1982 set (AREA_CODE, COUNTRY_NAME) =
1983 (select bthv.PARENT_TERRITORY_CODE, bthv.CHILD_TERRITORY_NAME
1984 from bis_territory_hierarchies_v bthv
1985 where
1986 bthv.CHILD_TERRITORY_CODE = cmt.country_code)
1987 where
1988 cmt.country_code is not null
1989 and cmt.gl_date between l_from_date and l_to_date
1990 and cmt.build_id = l_build_id;
1991
1992 /*---------------------------------------------------------------+
1993 | Update Region level Code of Geography dimension
1994 +---------------------------------------------------------------*/
1995
1996 l_stmt_id := 186;
1997
1998 if (app_col_name is not null) then
1999 --app_col_name1 := '''' || app_col_name || '''';
2000
2001 sql_stmt := 'update CST_BIS_MARGIN_SUMMARY cmt set (REGION_CODE, region_name)= '
2002 || '(select :app_col_name , brv.name from RA_ADDRESSES ra,bis_regions_v brv '
2003 || 'where cmt.country_code = ra.country'
2004 || ' and ra.country = brv.COUNTRY_CODE'
2005 || ' and brv.REGION_CODE = :app_col_name ) where'
2006 || ' cmt.country_code is not null and'
2007 || ' cmt.gl_date between :l_from_date and :l_to_date'
2008 || ' and cmt.build_id = :l_build_id';
2009
2010 execute immediate sql_stmt using app_col_name, app_col_name,
2011 l_from_date, l_to_date, l_build_id;
2012
2013 end if;
2014
2015 /*---------------------------------------------------------------+
2016 | Update Area Name of Geography dimension
2017 +---------------------------------------------------------------*/
2018
2019
2020 l_stmt_id := 190;
2021 update CST_BIS_MARGIN_SUMMARY cmt
2022 set AREA_NAME =
2023 (select BAV.name
2024 from bis_areas_v BAV
2025 where
2026 cmt.area_code = BAV.area_code )
2027 where
2028 cmt.area_code is not null
2029 and cmt.gl_date between l_from_date and l_to_date
2030 and cmt.build_id = l_build_id;
2031
2032
2033
2034 /*---------------------------------------------------------------+
2035 | Update Category id for Items
2036 +---------------------------------------------------------------*/
2037
2038 l_stmt_id := 200;
2039 update CST_BIS_MARGIN_SUMMARY temp
2040 set OE_ITEM_CATEGORY_ID =
2041 (select max(MC.category_id)
2042 from
2043 mtl_categories MC
2044 , mtl_category_sets MCS
2045 , mtl_parameters MP
2046 , mtl_item_categories MIC
2047 , mtl_default_category_sets MDCS
2048 where
2049 temp.parent_organization_id = MP.organization_id
2050 AND MIC.inventory_item_id = temp.parent_inventory_item_id
2051 AND MIC.organization_id = MP.master_organization_id
2052 AND MC.category_id = MIC.category_id
2053 AND MCS.category_set_id = MIC.category_set_id
2054 AND MCS.category_set_id = MDCS.category_set_id
2055 AND MDCS.functional_area_id = 7
2056 AND temp.legal_entity_id is not null
2057 )
2058 where
2059 temp.legal_entity_id is not null
2060 and temp.gl_date between l_from_date and l_to_date
2061 and temp.build_id = l_build_id;
2062
2063
2064 /*---------------------------------------------------------------+
2065 | Update Operating Unit Name
2066 +---------------------------------------------------------------*/
2067
2068 l_stmt_id := 210;
2069 update CST_BIS_MARGIN_SUMMARY cmt
2070 set OPERATING_UNIT_NAME =
2071 (select HOU.name
2072 from hr_operating_units HOU
2073 where
2074 cmt.org_id = HOU.organization_id)
2075 where
2076 cmt.org_id is not null
2077 and cmt.gl_date between l_from_date and l_to_date
2078 and cmt.build_id = l_build_id;
2079
2080 /*---------------------------------------------------------------+
2081 | Call ICX package to insert into summary table for WEB inquiry
2082 | form
2083 +---------------------------------------------------------------*/
2084
2085 -- icx_margin_web_ana_pkg.build_icx_cst_margin_table;
2086
2087 /*---------------------------------------------------------------+
2088 | Commit the changes and exit
2089 +---------------------------------------------------------------*/
2090
2091 COMMIT;
2092
2093 END LOOP; /* Legal Entity Loop */
2094
2095 close all_le;
2096
2097 icx_margin_web_ana_pkg.build_icx_cst_margin_table;
2098
2099 EXCEPTION
2100
2101 WHEN OM_NOT_ACTIVE_ERROR THEN
2102
2103 raise_application_error(-20000, 'CSTPOMLD.load_om_margin_data(): Order Management is not active');
2104
2105 WHEN OTHERS THEN
2106
2107 ROLLBACK;
2108
2109 raise_application_error(-20001, 'CSTPOMLD.load_om_margin_data(' ||
2110 l_stmt_id || '): ' || SQLERRM);
2111
2112 END load_om_margin_data;
2113
2114 END CSTPOMLD;
2115