[Home] [Help]
188:
189: cursor c_model_lines is
190: --select perform_match, line_id, parent_ato_line_id, inventory_item_id
191: select line_id, parent_ato_line_id, inventory_item_id
192: from bom_cto_order_lines
193: where bom_item_type = 1
194: --and top_model_line_id = pModelLineId -- top model
195: and ato_line_id = pModelLineId
196: and nvl(wip_supply_type,0) <> 6
222: oe_debug_pub.add('match_and_create_all_items: ' || 'CUSTOM_MATCH: ' || l_custom_match_profile, 1);
223: END IF;
224:
225: /*-----------------------------------------------------+
226: This is the loop that traverses bom_cto_order_lines
227: to match each configured assembly. If an assembly
228: does not have a match, a new item is created. If
229: it does have a match, we make create that item
230: in all the sourcing organizations if it does not exist.
234: lXConfigId := NULL;
235:
236: select perform_match
237: into lPerformMatch
238: from bom_cto_order_lines
239: where line_id = lNextRec.line_id;
240:
241: IF PG_DEBUG <> 0 THEN
242: oe_debug_pub.add('match_and_create_all_items: ' || 'Match_and_create_all_items: Processing line_id '
327: if (lStatus = 1) then
328:
329: begin
330:
331: update bom_cto_order_lines
332: set perform_match = 'N'
333: where line_id = lNextRec.parent_ato_line_id
334: and perform_match = 'Y';
335: -- if the update fails, its not an error
474: end if;
475:
476: begin
477:
478: update bom_cto_order_lines
479: set perform_match = 'N'
480: where line_id = lNextRec.parent_ato_line_id
481: and perform_match = 'Y';
482: -- if the update fails, its not an error
564:
565: end if; -- end lNextRec.Perform_match
566:
567: lStmtNum := 160;
568: update bom_cto_order_lines
569: set config_item_id = lXConfigId
570: where line_id = lNextRec.line_id;
571:
572: end loop;
630:
631: Bugfix 1553467 : If an ATO model is part of a PTO model (see fig below),
632: then, the link_to_line_id of ATO model will be the line_id of
633: the PTO model. Since the PTO options are not inserted in
634: bom_cto_order_lines, the condition
635: "and colp.line_id = nvl(col1.link_to_line_id, col1.line_id)"
636: will fail.
637: Removed "colp" from the FROM clause and added a new condition after
638: commenting the old. Search on 1553467.
719: inventory_item_id
720: )
721: )
722: into l_component_count, l_component_sum
723: from bom_cto_order_lines_gt
724: where parent_ato_line_id = p_model_line_id
725: or line_id = p_model_line_id;
726:
727:
728:
729: l_stmt_num := 120;
730: select inventory_item_id
731: into l_base_model_id
732: from bom_cto_order_lines_gt
733: where line_id = p_model_line_id;
734:
735: IF PG_DEBUG <> 0 THEN
736: oe_debug_pub.add('l_component_count = '||l_component_count ||' l_component_sum = '||l_component_sum);
807: -- If the config item is INACTIVE in even one orgn, we will not consider that configuration for matching.
808:
809: select /*+ ordered */ bac1.config_item_id -- 6086540: added the ordered hint
810: into x_config_match_id
811: from bom_cto_order_lines_gt col1, /* model */
812: bom_ato_configs_temp bact1, --6086540: use the GTT for filtering based on approx match
813: bom_ato_configurations bac1 --6086540: matching will be done in main table
814: where col1.line_id = p_model_line_id
815: and bac1.base_model_id = col1.inventory_item_id
825: and msi.inventory_item_status_code = nvl(bp.bom_delete_status_code,'NULL')
826: )
827: and not exists
828: (select 'Extra Options in Order'
829: from bom_cto_order_lines_gt col5
830: where (col5.parent_ato_line_id = col1.line_id
831: or col5.line_id = col1.line_id) -- to pick up top model
832: and col5.ordered_quantity > 0
833: and nvl(decode(col5.line_id, col1.line_id, col5.inventory_item_id,
855: not in
856: ( select decode( col1.line_id , col9.line_id, col9.inventory_item_id ,
857: nvl( col9.config_item_id, col9.inventory_item_id )),
858: Round( nvl( col9.ordered_quantity, 0)/nvl( col1.ordered_quantity, 0 ), 7 ) /* Decimal-Qty Support for Option Items */
859: from bom_cto_order_lines_gt col9
860: where col9.parent_ato_line_id = col1.line_id or col9.line_id = col1.line_id
861: )
862: )
863: and rownum = 1;
974:
975: Bugfix 1553467 : If an ATO model is part of a PTO model (see fig below),
976: then, the link_to_line_id of ATO model will be the line_id of
977: the PTO model. Since the PTO options are not inserted in
978: bom_cto_order_lines, the condition
979: "and bcolParent.line_id = NVL(bcolModel.link_to_line_id, bcolModel.line_id); "
980: will fail.
981:
982: Removed "bcolParent" from the FROM clause and added a new condition after
1027: l_stmt_num := 10;
1028: BEGIN
1029: SELECT 'Y'
1030: INTO l_ato_flag
1031: FROM bom_cto_order_lines bcol,
1032: mtl_system_items msi
1033: WHERE bcol.line_id = p_model_line_id
1034: AND bcol.config_item_id = msi.inventory_item_id
1035: AND msi.replenish_to_order_flag = 'Y'
1099: prg_appid,
1100: prg_id,
1101: SYSDATE
1102: from
1103: -- bugfix 1553467: bom_cto_order_lines bcolParent, /* Parent of Model, if any */
1104: bom_cto_order_lines bcolModel, /* Model */
1105: bom_cto_order_lines bcolOptions /* Options */
1106: where bcolModel.line_id = p_model_line_id
1107: and (bcolOptions.parent_ato_line_id = bcolModel.line_id or
1100: prg_id,
1101: SYSDATE
1102: from
1103: -- bugfix 1553467: bom_cto_order_lines bcolParent, /* Parent of Model, if any */
1104: bom_cto_order_lines bcolModel, /* Model */
1105: bom_cto_order_lines bcolOptions /* Options */
1106: where bcolModel.line_id = p_model_line_id
1107: and (bcolOptions.parent_ato_line_id = bcolModel.line_id or
1108: bcolOptions.line_id = bcolModel.line_id);
1101: SYSDATE
1102: from
1103: -- bugfix 1553467: bom_cto_order_lines bcolParent, /* Parent of Model, if any */
1104: bom_cto_order_lines bcolModel, /* Model */
1105: bom_cto_order_lines bcolOptions /* Options */
1106: where bcolModel.line_id = p_model_line_id
1107: and (bcolOptions.parent_ato_line_id = bcolModel.line_id or
1108: bcolOptions.line_id = bcolModel.line_id);
1109: --and bcolOptions.ordered_quantity >
1920: IS
1921:
1922: cursor c_model_lines is
1923: select line_id, parent_ato_line_id, inventory_item_id
1924: from bom_cto_order_lines_gt
1925: where bom_item_type = '1'
1926: and ato_line_id = p_ato_line_id
1927: and nvl(wip_supply_type,0) <> 6
1928: and ato_line_id is not null -- could be a PTO
1968: lXConfigId := NULL;
1969: lStmtNum :=20;
1970: select perform_match
1971: into lPerformMatch
1972: from bom_cto_order_lines_gt
1973: where line_id = lNextRec.line_id;
1974:
1975:
1976:
1983:
1984: if (lPerformMatch = 'U') then
1985:
1986: lStmtNum:=30;
1987: update bom_cto_order_lines_gt
1988: set perform_match = 'U'
1989: where line_id = lNextRec.parent_ato_line_id
1990: and perform_match in ('Y','C');
1991: -- if the update fails, its not an error
2107: if (lStatus = 1 and lXConfigId is null) then
2108:
2109:
2110: lStmtNum:=60;
2111: update bom_cto_order_lines_gt
2112: set perform_match = 'U'
2113: where line_id = lNextRec.line_id
2114: and perform_match in ('Y','C');
2115: -- if the update fails, its not an error
2118:
2119: oe_debug_pub.add('perform_match: ' || 'rowcount : ' || to_char(sql%rowcount), 1);
2120:
2121: lStmtNum:=70;
2122: update bom_cto_order_lines_gt
2123: set perform_match = 'U'
2124: where line_id = lNextRec.parent_ato_line_id
2125: and perform_match in ( 'Y','C');
2126: -- if the update fails, its not an error
2132: elsif (lStatus = 1 and lXConfigId is not null) then
2133:
2134:
2135: lStmtNum:=80;
2136: update bom_cto_order_lines_gt
2137: set config_item_id = lXConfigId
2138: where line_id = lNextRec.line_id;
2139:
2140: --
2182:
2183:
2184: /* Fix for bug 3533192 */
2185:
2186: update bom_cto_order_lines_gt
2187: set perform_match = 'Y'
2188: where ato_line_id = p_ato_line_id
2189: and inventory_item_id in
2190: ( select inventory_item_id
2187: set perform_match = 'Y'
2188: where ato_line_id = p_ato_line_id
2189: and inventory_item_id in
2190: ( select inventory_item_id
2191: from bom_cto_order_lines_gt
2192: where ato_line_id = p_ato_line_id
2193: and bom_item_type = '1'
2194: and wip_supply_type <> 6
2195: and perform_match = 'U'
2344: CURSOR c_single_ato is
2345: SELECT line_id,
2346: parent_ato_line_id,
2347: reuse_config
2348: FROM bom_cto_order_lines_gt
2349: --added nvl, bugfix 3530054
2350: WHERE nvl(wip_supply_type,1) <>6 --non phantom ato models
2351: AND bom_item_type = '1' --used inverted commas to use index N5
2352: AND ato_line_id = p_ato_line_id;
2355: CURSOR c_bulk is
2356: SELECT line_id,
2357: parent_ato_line_id,
2358: reuse_config
2359: FROM bom_cto_order_lines_gt
2360: ----added nvl, bugfix 3530054
2361: WHERE nvl(wip_supply_type,1) <>6 --non phantom
2362: AND bom_item_type = '1' ; --'1' for using idx_N5 --ato models
2363:
2369: wip_supply_type,
2370: bom_item_type,
2371: qty_per_parent_model,
2372: reuse_config
2373: FROM bom_cto_order_lines_gt
2374: WHERE ato_line_id = p_ato_line_id;
2375:
2376: CURSOR c_debug is
2377: SELECT
2381: qty_per_parent_model,
2382: config_creation,
2383: ship_from_org_id,
2384: validation_org
2385: FROM bom_cto_order_lines_gt
2386: WHERE reuse_config is not null;
2387:
2388:
2389:
2417: lStmtNum := 20;
2418:
2419:
2420:
2421: UPDATE bom_cto_order_lines_gt
2422: SET reuse_config = 'Y'
2423: WHERE ato_line_id = p_ato_line_id
2424: AND bom_item_type = '1' --'1' uses idx_n5 --identifies non-phantom
2425: --need a nvl as for top most ato model there is no value
2434: qty_per_parent_model
2435: BULK COLLECT INTO
2436: l_temp_line_id,
2437: l_qty_per_parent_model
2438: FROM bom_cto_order_lines_gt
2439: WHERE ato_line_id = p_ato_line_id;
2440:
2441: oe_debug_pub.add('LINE_ID >>QTY_PER_PARENT_MODEL',5);
2442:
2473: END IF;
2474:
2475:
2476: lStmtNum := 40;
2477: UPDATE bom_cto_order_lines_gt
2478: SET reuse_config = 'Y'
2479: WHERE bom_item_type = '1' --used idx_n5 --identifies non-phantom
2480: AND nvl(WIP_SUPPLY_TYPE,1) <> 6;
2481:
2483: --returning into cluase doesnot supoort distinct
2484: lStmtNum:=50;
2485: SELECT distinct(ato_line_id)
2486: BULK COLLECT INTO l_ato_line_tbl
2487: FROM bom_cto_order_lines_gt
2488: WHERE top_model_line_id is not null;
2489: END IF;
2490:
2491: l_ato_last_index := l_ato_line_tbl.count;
2505: -- rkaza. 12/06/2005. bug 4520992. Fp'ed bug 4493512.
2506: -- Added hint as per Perf team so that bcolgt drives the query
2507: SELECT /*+ leading(BCGT) use_nl(BCGT BCOL) */ distinct(bcol.ato_line_id)
2508: BULK COLLECT INTO l_bcol_ato_line_tbl
2509: FROM bom_cto_order_lines bcol,
2510: bom_cto_order_lines_gt bcgt
2511: WHERE bcgt.line_id = bcol.line_id
2512: AND bcol.qty_per_parent_model is null;
2513:
2506: -- Added hint as per Perf team so that bcolgt drives the query
2507: SELECT /*+ leading(BCGT) use_nl(BCGT BCOL) */ distinct(bcol.ato_line_id)
2508: BULK COLLECT INTO l_bcol_ato_line_tbl
2509: FROM bom_cto_order_lines bcol,
2510: bom_cto_order_lines_gt bcgt
2511: WHERE bcgt.line_id = bcol.line_id
2512: AND bcol.qty_per_parent_model is null;
2513:
2514: EXCEPTION
2524:
2525: END IF;
2526:
2527: FORALL j IN 1..l_bcol_ato_line_tbl.last
2528: UPDATE bom_cto_order_lines child
2529: SET qty_per_parent_model =
2530: --used round to be consistent with can_configuration code
2531: ( SELECT ROUND(child.ordered_quantity/parent.ordered_quantity,7)
2532: FROM bom_cto_order_lines parent
2528: UPDATE bom_cto_order_lines child
2529: SET qty_per_parent_model =
2530: --used round to be consistent with can_configuration code
2531: ( SELECT ROUND(child.ordered_quantity/parent.ordered_quantity,7)
2532: FROM bom_cto_order_lines parent
2533: WHERE child.parent_ato_line_id= parent.line_id
2534: )
2535: --to filter out ato item order lines
2536: WHERE top_model_line_id is not null
2541:
2542: --bugfix 3503764
2543: --need config_creation as part of fix 3503764
2544: --in core reuse sql
2545: UPDATE bom_cto_order_lines_gt bcol_gt
2546: SET config_creation =
2547: --used round to be consistent with can_configuration code
2548: ( SELECT nvl(mtl.config_orgs,1)--3555026
2549: FROM mtl_system_items mtl
2558:
2559:
2560: lStmtNum := 60;
2561: FORALL i in 1..l_ato_last_index
2562: UPDATE bom_cto_order_lines_gt bcolt
2563: SET bcolt.reuse_config = 'N'
2564: WHERE
2565: line_id in (
2566:
2566:
2567: --bugfix start 3503764
2568: --if ware house is different then reuse = N
2569: (SELECT bcol_gt1.line_id
2570: FROM bom_cto_order_lines_gt bcol_gt1,
2571: bom_cto_order_lines bcol
2572: WHERE bcol.line_id = l_ato_line_tbl(i)
2573: AND bcol_gt1.config_creation in (1,2)
2574: AND bcol_gt1.ato_line_id = l_ato_line_tbl(i)
2567: --bugfix start 3503764
2568: --if ware house is different then reuse = N
2569: (SELECT bcol_gt1.line_id
2570: FROM bom_cto_order_lines_gt bcol_gt1,
2571: bom_cto_order_lines bcol
2572: WHERE bcol.line_id = l_ato_line_tbl(i)
2573: AND bcol_gt1.config_creation in (1,2)
2574: AND bcol_gt1.ato_line_id = l_ato_line_tbl(i)
2575: AND bcol_gt1.ship_from_org_id <> bcol.ship_from_org_id
2579: --end bugfix 3503764
2580:
2581: UNION
2582: ( Select parent_ato_line_id
2583: from bom_cto_order_lines_gt bcolt1
2584: Where (bcolt1.line_id,
2585: bcolt1.qty_per_parent_model,
2586: bcolt1.inventory_item_id)
2587: not in ( Select line_id,
2586: bcolt1.inventory_item_id)
2587: not in ( Select line_id,
2588: qty_per_parent_model,
2589: inventory_item_id
2590: from bom_cto_order_lines
2591: where ato_line_id = l_ato_line_tbl(i) )
2592: --filters out pure ato item lines
2593: AND bcolt1.top_model_line_id is not null
2594: AND bcolt1.ato_line_id = l_ato_line_tbl(i)
2595: )
2596: -- bugfix 3381658 start
2597: UNION
2598: (Select parent_ato_line_id
2599: from bom_cto_order_lines bcol2
2600: Where (bcol2.line_id,
2601: bcol2.qty_per_parent_model,
2602: bcol2.inventory_item_id)
2603: not in ( Select bcolgt.line_id,
2602: bcol2.inventory_item_id)
2603: not in ( Select bcolgt.line_id,
2604: bcolgt.qty_per_parent_model,
2605: bcolgt.inventory_item_id
2606: from bom_cto_order_lines_gt bcolgt
2607: where ato_line_id = l_ato_line_tbl(i) )
2608: --filters out pure ato item lines
2609: AND bcol2.top_model_line_id is not null
2610: AND bcol2.ato_line_id = l_ato_line_tbl(i)
2710:
2711: END LOOP;
2712:
2713: FORALL i in g_model_line_tbl.FIRST..g_model_line_tbl.LAST
2714: UPDATE bom_cto_order_lines_gt
2715: SET reuse_config = 'N'
2716: WHERE line_id = g_model_line_tbl(i);
2717: END IF;--g_model_line exists
2718:
2740: --SQL statement which accesses other tables.
2741: --In such cases, the join order may not be optimal due to the lack
2742: --of stats on the temp table, hence hints should be used to ensure the optimal join order.
2743:
2744: UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2745: SET bcolt.config_item_id =
2746: (SELECT bcol.config_item_id
2747: FROM bom_cto_order_lines bcol
2748: WHERE bcolt.line_id = bcol.line_id
2743:
2744: UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2745: SET bcolt.config_item_id =
2746: (SELECT bcol.config_item_id
2747: FROM bom_cto_order_lines bcol
2748: WHERE bcolt.line_id = bcol.line_id
2749: )
2750: WHERE bcolt.bom_item_type = '1'--used idx_n5
2751: AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
2759: --SQL statement which accesses other tables.
2760: --In such cases, the join order may not be optimal due to the lack
2761: --of stats on the temp table, hence hints should be used to ensure the optimal join order.
2762:
2763: UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2764: SET bcolt.config_item_id =
2765: (SELECT bcol.config_item_id
2766: FROM bom_cto_order_lines bcol
2767: WHERE bcolt.line_id = bcol.line_id
2762:
2763: UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2764: SET bcolt.config_item_id =
2765: (SELECT bcol.config_item_id
2766: FROM bom_cto_order_lines bcol
2767: WHERE bcolt.line_id = bcol.line_id
2768: )
2769: WHERE bcolt.bom_item_type = '1' --used inverted commas, so that index is used
2770: AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
2868:
2869:
2870:
2871: lStmtNum:=20;
2872: UPDATE bom_cto_order_lines_gt bcol
2873: SET (bcol.wip_supply_type,
2874: bcol.bom_item_type )=
2875: (SELECT wip_supply_type,
2876: bom_item_type
2891: wip_supply_type
2892: BULK COLLECT INTO
2893: p_match_rec_of_tab.bom_item_type,
2894: p_match_rec_of_tab.wip_supply_type
2895: FROM bom_cto_order_lines_gt;
2896:
2897: oe_debug_pub.add('rowcount after select for BIT,WST=>'||sql%rowcount,5);
2898:
2899: --rowcount of bom_itemtype,wip_supply_typ after select l_rowcount;
2978: lStmtNum:=80;
2979: --update the remaining columns into bcol_temp
2980:
2981: FORALL i IN 1..l_last_index
2982: UPDATE bom_cto_order_lines_gt
2983: SET PARENT_ATO_LINE_ID = p_match_rec_of_tab.PARENT_ATO_LINE_ID(i),
2984: GOP_PARENT_ATO_LINE_ID = p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID(i),
2985: PLAN_LEVEL = p_match_rec_of_tab.PLAN_LEVEL (i)
2986: WHERE line_id = p_match_rec_of_tab.LINE_ID(i);
3073: -- bcolgt into the pl/sql table. But since bcol gt has more rows than
3074: -- the pl/sql table, incorrect configs are matched to models.
3075: -- So executing a complete delete here from bcol gt.
3076:
3077: delete from bom_cto_order_lines_gt;
3078:
3079: IF PG_DEBUG <> 0 THEN
3080: oe_debug_pub.add('insert into bcol_gt',5);
3081: END IF;
3082:
3083:
3084: lStmtNum:=20;
3085: FORALL i in 1..l_last_index
3086: INSERT INTO bom_cto_order_lines_gt
3087: (
3088: ATO_LINE_ID,
3089: BOM_ITEM_TYPE,
3090: COMPONENT_CODE,
3491: SELECT line_id,
3492: parent_ato_line_id,
3493: ato_line_id,
3494: perform_match
3495: FROM bom_cto_order_lines_gt
3496: WHERE bom_item_type = '1' -- put in inverted commas to use hint
3497: AND nvl(wip_supply_type,1)<> 6;
3498:
3499:
3514: --SQL statement which accesses other tables.
3515: --In such cases, the join order may not be optimal due to the lack
3516: --of stats on the temp table, hence hints should be used to ensure the optimal join order.
3517:
3518: UPDATE /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt bcol
3519: SET bcol.perform_match=
3520: (SELECT config_match
3521: FROM mtl_system_items_b mtl
3522: WHERE mtl.inventory_item_id = bcol.inventory_item_id
3599: END IF;
3600:
3601: lStmtNum := 50;
3602: FORALL i IN 1..l_match_flag_rec_of_tab.line_id.count
3603: UPDATE bom_cto_order_lines_gt
3604: SET perform_match = l_match_flag_rec_of_tab.match_flag(i)
3605: WHERE line_id = l_match_flag_rec_of_tab.line_id (i);
3606:
3607: