[Home] [Help]
266: g_log_enabled BOOLEAN;
267:
268: TYPE g_SubcontractTabTyp IS TABLE OF JMF_SUBCONTRACT_ORDERS_TEMP%ROWTYPE;
269: TYPE g_OsaTabTyp IS TABLE OF JMF_SUBCONTRACT_ORDERS%ROWTYPE;
270: TYPE g_Comp_TabTyp IS TABLE OF JMF_SHIKYU_COMPONENTS%ROWTYPE;
271: TYPE g_oem_tp_rec IS RECORD
272: ( oem_organization_id NUMBER
273: , tp_organization_id NUMBER
274: , vendor_id NUMBER
1678: , jsc.shikyu_component_id
1679: , sum(nvl(jsa.allocated_quantity,0))
1680: , max(nvl(wro.required_quantity,0))
1681: FROM jmf_shikyu_allocations jsa
1682: , jmf_shikyu_components jsc
1683: , jmf_subcontract_orders jso
1684: , wip_requirement_operations wro
1685: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1686: AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
1705: , sum(nvl(jsa.allocated_quantity,0))
1706: , max(nvl(wro.required_quantity,0))
1707: , max(jsc.replen_so_creation_failed)
1708: FROM jmf_shikyu_allocations jsa
1709: , jmf_shikyu_components jsc
1710: , jmf_subcontract_orders jso
1711: , wip_requirement_operations wro
1712: , po_line_locations_all plla
1713: , po_lines_all pla
2036: THEN
2037:
2038: l_osa_shipment_id := TO_NUMBER(SUBSTR(l_reference_num, 1, INSTR(l_reference_num, '-') - 1));
2039:
2040: UPDATE jmf_shikyu_components
2041: SET replen_so_creation_failed = 'Y'
2042: , last_update_date = sysdate
2043: , last_updated_by = FND_GLOBAL.user_id
2044: , last_update_login = FND_GLOBAL.login_id
2151: -- Reset the replen_so_creation_failed flag
2152: IF l_replen_so_creation_failed IS NOT NULL
2153: THEN
2154:
2155: UPDATE jmf_shikyu_components
2156: SET replen_so_creation_failed = NULL
2157: , last_update_date = sysdate
2158: , last_updated_by = FND_GLOBAL.user_id
2159: , last_update_login = FND_GLOBAL.login_id
2281: AND jso.interlock_status = 'N'
2282: AND poll.org_id = p_operating_unit
2283: AND NOT EXISTS
2284: (SELECT shikyu_component_id
2285: FROM jmf_shikyu_components
2286: WHERE subcontract_po_shipment_id = jso.subcontract_po_shipment_id);
2287:
2288: /*
2289: -- Once the BOM is exploded for the OSA item, the components
2554: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2555: , G_PKG_NAME
2556: , '>> ' || l_program || ': Shikyu Component '
2557: || l_comp_tbl(l_curr_index).item_name
2558: || ' is of Supply Type Phantom and will not be loaded into JMF_SHIKYU_COMPONENTS'
2559: );
2560: END IF;
2561:
2562: ELSE /* if Component is not a Phantom Assembly */
2561:
2562: ELSE /* if Component is not a Phantom Assembly */
2563:
2564: -- Checking to make sure that the same component would not be inserted into
2565: -- the jmf_shikyu_components table multiple times. The same component can be
2566: -- an immediate child of the assembly item, or the child of a phantom child
2567: -- (at any level) of the assembly.
2568:
2569: IF g_log_enabled AND
2581: l_count_seq := 0;
2582:
2583: SELECT COUNT(*)
2584: INTO l_count_seq
2585: FROM jmf_shikyu_components
2586: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
2587: AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
2588:
2589: IF g_log_enabled AND
2594: || ': l_count_seq = '
2595: || l_count_seq
2596: || ', Shikyu Component '
2597: || l_comp_tbl(l_curr_index).item_name
2598: || ' already exists in the JMF_SHIKYU_COMPONENTS table'
2599: );
2600: END IF;
2601:
2602: IF l_count_seq > 0
2699: -- Load the Components table.
2700:
2701: IF NVL(l_count_seq, 0) = 0
2702: THEN
2703: INSERT INTO JMF_SHIKYU_COMPONENTS
2704: ( subcontract_po_shipment_id
2705: , shikyu_component_id
2706: , oem_organization_id
2707: , primary_uom
2738: THEN
2739: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2740: , G_PKG_NAME
2741: , '>> ' || l_program
2742: || ': After insert into JMF_SHIKYU_COMPONENTS');
2743: END IF;
2744:
2745: ELSE
2746:
2749: THEN
2750: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2751: , G_PKG_NAME
2752: , '>> ' || l_program
2753: || ': JMF_SHIKYU_COMPONENTS record with ID = '
2754: || l_comp_tbl(l_curr_index).inventory_item_id
2755: || ' already inserted');
2756: END IF;
2757:
2754: || l_comp_tbl(l_curr_index).inventory_item_id
2755: || ' already inserted');
2756: END IF;
2757:
2758: UPDATE JMF_SHIKYU_COMPONENTS
2759: SET quantity = quantity + l_comp_tbl(l_curr_index).primary_quantity
2760: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
2761: AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
2762:
2763: END IF; /* IF NVL(l_count_seq, 0) = 0 */
2764:
2765: -- Invoke Process Replenishment SO with action of Quote.
2766: -- This will just populate the UOM code and price in
2767: -- the jmf_shikyu_components table without creating the
2768: -- order line. This information is required when creating
2769: -- the replenishment SO for the component.
2770:
2771: JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO
2823: , last_updated_by = FND_GLOBAL.user_id
2824: , last_update_login = FND_GLOBAL.login_id
2825: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
2826:
2827: DELETE FROM jmf_shikyu_components
2828: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
2829:
2830: IF g_log_enabled AND
2831: (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2940: JMF_SUBCONTRACT_ORDERS.oem_organization_id%TYPE;
2941: l_tp_organization_id
2942: JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
2943: l_subcontract_po_shipment_id
2944: JMF_SHIKYU_COMPONENTS.subcontract_po_shipment_id%TYPE;
2945: l_shikyu_component_id
2946: JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
2947: l_quantity
2948: JMF_SHIKYU_COMPONENTS.quantity%TYPE;
2942: JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
2943: l_subcontract_po_shipment_id
2944: JMF_SHIKYU_COMPONENTS.subcontract_po_shipment_id%TYPE;
2945: l_shikyu_component_id
2946: JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
2947: l_quantity
2948: JMF_SHIKYU_COMPONENTS.quantity%TYPE;
2949: l_order_line_id
2950: OE_ORDER_LINES_ALL.line_id%TYPE;
2944: JMF_SHIKYU_COMPONENTS.subcontract_po_shipment_id%TYPE;
2945: l_shikyu_component_id
2946: JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
2947: l_quantity
2948: JMF_SHIKYU_COMPONENTS.quantity%TYPE;
2949: l_order_line_id
2950: OE_ORDER_LINES_ALL.line_id%TYPE;
2951:
2952: l_program CONSTANT VARCHAR2(30) := 'Stamp_Null_Shikyu_Comp_Prices';
2964: , jso.oem_organization_id
2965: , jso.tp_organization_id
2966: FROM
2967: jmf_subcontract_orders jso,
2968: jmf_shikyu_components jsc,
2969: po_line_locations_all plla
2970: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
2971: AND shikyu_component_price IS NULL
2972: AND plla.line_location_id = jso.subcontract_po_shipment_id
3006: END IF;
3007:
3008: -- Invoke Process Replenishment SO with action of Quote.
3009: -- This will just populate the UOM code and price in
3010: -- the jmf_shikyu_components table without creating the
3011: -- order line. This information is required when creating
3012: -- the replenishment SO for the component.
3013:
3014: IF g_log_enabled AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3151: AND interlock_status in ('N','U');
3152:
3153: CURSOR c_comp_rec IS
3154: SELECT *
3155: FROM jmf_shikyu_components
3156: WHERE subcontract_po_shipment_id = l_shipment_id;
3157:
3158: BEGIN
3159:
3665:
3666: -- Moved the call to Stamp_Null_Shikyu_Comp_Prices before the call
3667: -- to Load_Shikyu_Components, in order to avoid calling OM Process
3668: -- Order API again to get prices for the components that were newly
3669: -- inserted into the JMF_SHIKYU_COMPONENTS table and that failed
3670: -- price quoting the first time (typically because of wrong or
3671: -- missing pricing setup), which can seriously hurt the performance.
3672: Stamp_Null_Shikyu_Comp_Prices
3673: ( p_operating_unit => p_operating_unit