eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CTO_MATCH_CONFIG dependencies on BOM_CTO_ORDER_LINES_GT

Line 723: from bom_cto_order_lines_gt

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:

Line 732: from bom_cto_order_lines_gt

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);

Line 811: from bom_cto_order_lines_gt col1, /* model */

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

Line 829: from bom_cto_order_lines_gt col5

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,

Line 859: from bom_cto_order_lines_gt col9

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;

Line 1924: from bom_cto_order_lines_gt

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

Line 1972: from bom_cto_order_lines_gt

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:

Line 1987: update bom_cto_order_lines_gt

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

Line 2111: update bom_cto_order_lines_gt

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

Line 2122: update bom_cto_order_lines_gt

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

Line 2136: update bom_cto_order_lines_gt

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: --

Line 2186: update bom_cto_order_lines_gt

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

Line 2191: from 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
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'

Line 2348: FROM bom_cto_order_lines_gt

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;

Line 2359: FROM bom_cto_order_lines_gt

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:

Line 2373: FROM bom_cto_order_lines_gt

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

Line 2385: FROM bom_cto_order_lines_gt

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:

Line 2421: UPDATE bom_cto_order_lines_gt

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

Line 2438: FROM bom_cto_order_lines_gt

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:

Line 2477: UPDATE bom_cto_order_lines_gt

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:

Line 2487: FROM bom_cto_order_lines_gt

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;

Line 2510: bom_cto_order_lines_gt bcgt

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

Line 2545: UPDATE bom_cto_order_lines_gt bcol_gt

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

Line 2562: UPDATE bom_cto_order_lines_gt bcolt

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:

Line 2570: FROM bom_cto_order_lines_gt bcol_gt1,

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)

Line 2583: from bom_cto_order_lines_gt bcolt1

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,

Line 2606: from bom_cto_order_lines_gt bcolgt

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)

Line 2714: UPDATE bom_cto_order_lines_gt

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:

Line 2744: UPDATE /+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) /bom_cto_order_lines_gt bcolt

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

Line 2763: UPDATE /+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) /bom_cto_order_lines_gt bcolt

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

Line 2872: UPDATE bom_cto_order_lines_gt bcol

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

Line 2895: FROM bom_cto_order_lines_gt;

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;

Line 2982: UPDATE bom_cto_order_lines_gt

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);

Line 3077: delete from bom_cto_order_lines_gt;

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;

Line 3086: INSERT INTO bom_cto_order_lines_gt

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,

Line 3495: FROM bom_cto_order_lines_gt

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:

Line 3518: UPDATE /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N5) / bom_cto_order_lines_gt bcol

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

Line 3603: UPDATE bom_cto_order_lines_gt

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: