[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
1849: , jsc.shikyu_component_id
1850: , sum(nvl(jsa.allocated_quantity,0))
1851: , max(nvl(wro.required_quantity,0))
1852: FROM jmf_shikyu_allocations jsa
1853: , jmf_shikyu_components jsc
1854: , jmf_subcontract_orders jso
1855: , wip_requirement_operations wro
1856: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1857: AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
1878: , sum(nvl(jsa.allocated_quantity,0))
1879: , max(nvl(wro.required_quantity,0))
1880: , max(jsc.replen_so_creation_failed)
1881: FROM jmf_shikyu_allocations jsa
1882: , jmf_shikyu_components jsc
1883: , jmf_subcontract_orders jso
1884: , wip_requirement_operations wro
1885: , po_line_locations_all plla
1886: , po_lines_all pla
2250: THEN
2251:
2252: l_osa_shipment_id := TO_NUMBER(SUBSTR(l_reference_num, 1, INSTR(l_reference_num, '-') - 1));
2253:
2254: UPDATE jmf_shikyu_components
2255: SET replen_so_creation_failed = 'Y'
2256: , last_update_date = sysdate
2257: , last_updated_by = FND_GLOBAL.user_id
2258: , last_update_login = FND_GLOBAL.login_id
2381: -- Reset the replen_so_creation_failed flag
2382: IF l_replen_so_creation_failed IS NOT NULL
2383: THEN
2384:
2385: UPDATE jmf_shikyu_components
2386: SET replen_so_creation_failed = NULL
2387: , last_update_date = sysdate
2388: , last_updated_by = FND_GLOBAL.user_id
2389: , last_update_login = FND_GLOBAL.login_id
2520: AND jso.interlock_status = 'N'
2521: AND poll.org_id = p_operating_unit
2522: AND NOT EXISTS
2523: (SELECT shikyu_component_id
2524: FROM jmf_shikyu_components
2525: WHERE subcontract_po_shipment_id = jso.subcontract_po_shipment_id);
2526:
2527: /*
2528: -- Once the BOM is exploded for the OSA item, the components
2826: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2827: , G_PKG_NAME
2828: , '>> ' || l_program || ': Shikyu Component '
2829: || l_comp_tbl(l_curr_index).item_name
2830: || ' is of Supply Type Phantom/Bulk/Supplier and will not be loaded into JMF_SHIKYU_COMPONENTS'
2831: );
2832: END IF;
2833:
2834: ELSE /* if Component is not a Phantom Assembly */
2833:
2834: ELSE /* if Component is not a Phantom Assembly */
2835:
2836: -- Checking to make sure that the same component would not be inserted into
2837: -- the jmf_shikyu_components table multiple times. The same component can be
2838: -- an immediate child of the assembly item, or the child of a phantom child
2839: -- (at any level) of the assembly.
2840:
2841: IF g_log_enabled AND
2853: l_count_seq := 0;
2854:
2855: SELECT COUNT(*)
2856: INTO l_count_seq
2857: FROM jmf_shikyu_components
2858: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
2859: AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
2860:
2861: IF g_log_enabled AND
2866: || ': l_count_seq = '
2867: || l_count_seq
2868: || ', Shikyu Component '
2869: || l_comp_tbl(l_curr_index).item_name
2870: || ' already exists in the JMF_SHIKYU_COMPONENTS table'
2871: );
2872: END IF;
2873:
2874: IF l_count_seq > 0
2971: -- Load the Components table.
2972:
2973: IF NVL(l_count_seq, 0) = 0
2974: THEN
2975: INSERT INTO JMF_SHIKYU_COMPONENTS
2976: ( subcontract_po_shipment_id
2977: , shikyu_component_id
2978: , oem_organization_id
2979: , primary_uom
3010: THEN
3011: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3012: , G_PKG_NAME
3013: , '>> ' || l_program
3014: || ': After insert into JMF_SHIKYU_COMPONENTS');
3015: END IF;
3016:
3017: ELSE
3018:
3021: THEN
3022: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3023: , G_PKG_NAME
3024: , '>> ' || l_program
3025: || ': JMF_SHIKYU_COMPONENTS record with ID = '
3026: || l_comp_tbl(l_curr_index).inventory_item_id
3027: || ' already inserted');
3028: END IF;
3029:
3026: || l_comp_tbl(l_curr_index).inventory_item_id
3027: || ' already inserted');
3028: END IF;
3029:
3030: UPDATE JMF_SHIKYU_COMPONENTS
3031: SET quantity = quantity + l_comp_tbl(l_curr_index).primary_quantity
3032: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
3033: AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
3034:
3035: END IF; /* IF NVL(l_count_seq, 0) = 0 */
3036:
3037: -- Invoke Process Replenishment SO with action of Quote.
3038: -- This will just populate the UOM code and price in
3039: -- the jmf_shikyu_components table without creating the
3040: -- order line. This information is required when creating
3041: -- the replenishment SO for the component.
3042:
3043: JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO
3095: , last_updated_by = FND_GLOBAL.user_id
3096: , last_update_login = FND_GLOBAL.login_id
3097: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
3098:
3099: DELETE FROM jmf_shikyu_components
3100: WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
3101:
3102: IF g_log_enabled AND
3103: (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3212: JMF_SUBCONTRACT_ORDERS.oem_organization_id%TYPE;
3213: l_tp_organization_id
3214: JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
3215: l_subcontract_po_shipment_id
3216: JMF_SHIKYU_COMPONENTS.subcontract_po_shipment_id%TYPE;
3217: l_shikyu_component_id
3218: JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
3219: l_quantity
3220: JMF_SHIKYU_COMPONENTS.quantity%TYPE;
3214: JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
3215: l_subcontract_po_shipment_id
3216: JMF_SHIKYU_COMPONENTS.subcontract_po_shipment_id%TYPE;
3217: l_shikyu_component_id
3218: JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
3219: l_quantity
3220: JMF_SHIKYU_COMPONENTS.quantity%TYPE;
3221: l_order_line_id
3222: OE_ORDER_LINES_ALL.line_id%TYPE;
3216: JMF_SHIKYU_COMPONENTS.subcontract_po_shipment_id%TYPE;
3217: l_shikyu_component_id
3218: JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
3219: l_quantity
3220: JMF_SHIKYU_COMPONENTS.quantity%TYPE;
3221: l_order_line_id
3222: OE_ORDER_LINES_ALL.line_id%TYPE;
3223:
3224: l_program CONSTANT VARCHAR2(30) := 'Stamp_Null_Shikyu_Comp_Prices';
3236: , jso.oem_organization_id
3237: , jso.tp_organization_id
3238: FROM
3239: jmf_subcontract_orders jso,
3240: jmf_shikyu_components jsc,
3241: po_line_locations_all plla
3242: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
3243: AND shikyu_component_price IS NULL
3244: AND plla.line_location_id = jso.subcontract_po_shipment_id
3278: END IF;
3279:
3280: -- Invoke Process Replenishment SO with action of Quote.
3281: -- This will just populate the UOM code and price in
3282: -- the jmf_shikyu_components table without creating the
3283: -- order line. This information is required when creating
3284: -- the replenishment SO for the component.
3285:
3286: IF g_log_enabled AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3428: AND interlock_status in ('N','U');
3429:
3430: CURSOR c_comp_rec IS
3431: SELECT *
3432: FROM jmf_shikyu_components
3433: WHERE subcontract_po_shipment_id = l_shipment_id;
3434:
3435: BEGIN
3436:
4012:
4013: -- Moved the call to Stamp_Null_Shikyu_Comp_Prices before the call
4014: -- to Load_Shikyu_Components, in order to avoid calling OM Process
4015: -- Order API again to get prices for the components that were newly
4016: -- inserted into the JMF_SHIKYU_COMPONENTS table and that failed
4017: -- price quoting the first time (typically because of wrong or
4018: -- missing pricing setup), which can seriously hurt the performance.
4019: Stamp_Null_Shikyu_Comp_Prices
4020: ( p_operating_unit => p_operating_unit