[Home] [Help]
282: type t_wsli_locator_id is table of wsm_starting_lots_interface.locator_id%type;
283: type t_wsli_revision is table of wsm_starting_lots_interface.revision%type;
284: type t_wsli_last_updated_by is table of wsm_starting_lots_interface.last_updated_by%type;
285: type t_wsli_created_by is table of wsm_starting_lots_interface.created_by%type;
286: type t_wsli_primary_uom_code is table of mtl_system_items.primary_uom_code%type;
287: type t_wsli_comp_issue_qty is table of wsm_starting_lots_interface.component_issue_quantity%type;
288:
289: v_wsli_hdr_id t_wsli_header_id := t_wsli_header_id();
290: v_wsli_lot_number t_wsli_lot_number := t_wsli_lot_number();
312: locator_id wsm_starting_lots_interface.locator_id%type,
313: revision wsm_starting_lots_interface.revision%type,
314: last_updated_by wsm_starting_lots_interface.last_updated_by%type,
315: created_by wsm_starting_lots_interface.created_by%type,
316: primary_uom_code mtl_system_items.primary_uom_code%type,
317: comp_issue_quantity wsm_starting_lots_interface.component_issue_quantity%type
318: );
319:
320: v_rec_wsli rec_wsli; -- Not used
1973: l_stmt_num := 50;
1974: begin
1975: select unique(inventory_item_id)
1976: into v_wljdi_inventory_item_id_old(l_d)
1977: from mtl_system_items_kfv
1978: where concatenated_segments = v_wljdi_inventory_item_old(l_d)
1979: and organization_id = p_org_id;
1980: exception
1981: when others then
1985: else
1986: begin
1987: select concatenated_segments
1988: into v_wljdi_inventory_item_old(l_d)
1989: from mtl_system_items_kfv
1990: where inventory_item_id = v_wljdi_inventory_item_id_old(l_d)
1991: and organization_id = p_org_id;
1992: exception
1993: when others then
2018: else
2019: begin
2020: select unique(inventory_item_id)
2021: into v_wljdi_inventory_item_id_new(l_d)
2022: from mtl_system_items_kfv
2023: where concatenated_segments = v_wljdi_inventory_item_new(l_d)
2024: and organization_id = p_org_id;
2025: exception
2026: when others then
2032: else
2033: begin
2034: select concatenated_segments
2035: into v_wljdi_inventory_item_new(l_d)
2036: from mtl_system_items_kfv
2037: where inventory_item_id = v_wljdi_inventory_item_id_new(l_d)
2038: and organization_id = p_org_id;
2039: exception
2040: when others then
2063: else
2064: begin
2065: select unique(inventory_item_id)
2066: into v_wljdi_primary_item_id(l_d)
2067: from mtl_system_items_kfv
2068: where concatenated_segments = v_wljdi_primary_item(l_d)
2069: and organization_id = p_org_id;
2070: exception
2071: when others then
2090: else
2091: begin
2092: select unique(inventory_item_id)
2093: into v_wljdi_src_phantom_item_id(l_d)
2094: from mtl_system_items_kfv
2095: where concatenated_segments = v_wljdi_src_phantom_item(l_d)
2096: and organization_id = p_org_id;
2097: exception
2098: when others then
2104: else
2105: begin
2106: select concatenated_segments
2107: into v_wljdi_src_phantom_item(l_d)
2108: from mtl_system_items_kfv
2109: where inventory_item_id = v_wljdi_src_phantom_item_id(l_d)
2110: and organization_id = p_org_id;
2111: exception
2112: when others then
5698: /*
5699: begin
5700: select 1
5701: into l_comp_exist
5702: from mtl_system_items_kfv
5703: where organization_id = p_org_id
5704: and inventory_item_id=l_item_old_id;
5705: exception
5706: when others then
5767: --check if the inventory item id old is valid
5768: begin
5769: select 1
5770: into l_comp_exist
5771: from mtl_system_items_kfv
5772: where organization_id = p_org_id
5773: and inventory_item_id=l_item_old_id;
5774: exception
5775: when others then
5821:
5822: /*begin
5823: select 1
5824: into l_comp_exist
5825: from mtl_system_items_kfv
5826: where inventory_item_id=l_item_id
5827: and organization_id=p_org_id;
5828: exception
5829: when others then
5908: l_supply_subinventory IS NULL) then
5909:
5910: select wip_supply_subinventory, wip_supply_locator_id
5911: into l_supply_subinventory, l_supply_locator_id
5912: from mtl_system_items
5913: where inventory_item_id = l_item_id
5914: and organization_id = p_org_id;
5915:
5916: if l_supply_subinventory is null then
5934:
5935: /*begin
5936: select 1
5937: into l_comp_exist
5938: from mtl_system_items_kfv
5939: where inventory_item_id=l_item_id
5940: and organization_id=p_org_id;
5941: exception
5942: when others then
6034: l_supply_subinventory IS NULL) then
6035:
6036: select wip_supply_subinventory, wip_supply_locator_id
6037: into l_supply_subinventory, l_supply_locator_id
6038: from mtl_system_items
6039: where inventory_item_id = l_item_id
6040: and organization_id = p_org_id;
6041:
6042: if l_supply_subinventory is null then
8344: l_num_records := 0;
8345: begin
8346: select 1
8347: into l_num_records
8348: from mtl_system_items_kfv msi
8349: where msi.inventory_item_id = v_wlji_item_id(v_idx)
8350: and msi.organization_id = v_wlji_org_id(v_idx)
8351: and msi.lot_control_code = 2;
8352: exception
8362: l_num_records := 0;
8363: begin
8364: select 1
8365: into l_num_records
8366: from mtl_system_items_kfv msi
8367: where msi.inventory_item_id = v_wlji_item_id(v_idx)
8368: and msi.organization_id = v_wlji_org_id(v_idx)
8369: -- ST : Serial Support Project -----------------------------
8370: -- In OSFM we do support Serial controlled assemblies.......
8438:
8439: BEGIN
8440: select msi.serial_number_control_code
8441: into l_serial_ctrl_code
8442: from mtl_system_items_kfv msi
8443: where msi.inventory_item_id = v_wlji_item_id(v_idx)
8444: and msi.organization_id = v_wlji_org_id(v_idx);
8445:
8446: IF l_serial_ctrl_code = 2 THEN -- Predefined serial control...
8650: into l_sub_loc_control,
8651: l_org_loc_control,
8652: l_restrict_locators_code,
8653: l_item_loc_control
8654: FROM mtl_system_items MS,
8655: mtl_secondary_inventories MSUB,
8656: mtl_parameters MP
8657: WHERE MP.organization_id = v_wlji_org_id(v_idx)
8658: AND MS.organization_id = v_wlji_org_id(v_idx)
9241: into l_sub_loc_control,
9242: l_org_loc_control,
9243: l_restrict_locators_code,
9244: l_item_loc_control
9245: FROM mtl_system_items MS,
9246: mtl_secondary_inventories MSUB,
9247: mtl_parameters MP
9248: WHERE MP.organization_id = v_wlji_org_id(v_idx)
9249: AND MS.organization_id = v_wlji_org_id(v_idx)
9387: into l_sub_loc_control,
9388: l_org_loc_control,
9389: l_restrict_locators_code,
9390: l_item_loc_control
9391: FROM mtl_system_items MS,
9392: mtl_secondary_inventories MSUB,
9393: mtl_parameters MP
9394: WHERE MP.organization_id = v_wlji_org_id(v_idx)
9395: AND MS.organization_id = v_wlji_org_id(v_idx)
10084:
10085: BEGIN
10086: select msi.serial_number_control_code
10087: into l_serial_ctrl_code
10088: from mtl_system_items_kfv msi,wip_discrete_jobs wdj
10089: where msi.inventory_item_id = wdj.primary_item_id
10090: and msi.organization_id = v_wlji_org_id(v_idx)
10091: and wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
10092: and wdj.organization_id = v_wlji_org_id(v_idx);
10483: select SERIAL_NUMBER_CONTROL_CODE,
10484: revision_qty_control_code -- Add bug 2963225
10485: into dummy_number,
10486: l_rev_control_code -- Add bug 2963225
10487: from mtl_system_items_kfv msi
10488: where msi.inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
10489: and msi.organization_id = v_wlji_org_id(v_idx);
10490:
10491: if(dummy_number <> 1) then
10837: l_segment5,l_segment6,l_segment7,l_segment8,
10838: l_segment9,l_segment10,l_segment11,l_segment12,
10839: l_segment13,l_segment14,l_segment15,l_segment16,
10840: l_segment17,l_segment18,l_segment19,l_segment20
10841: FROM mtl_system_items
10842: WHERE inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
10843: AND organization_id=v_wlji_org_id(v_idx);
10844:
10845: EXCEPTION
11787: l_stmt_num := 1040.3;
11788: begin
11789: select unique(concatenated_segments)
11790: into l_assembly_name
11791: from mtl_system_items_kfv
11792: where inventory_item_id = v_wlji_item_id(v_idx)
11793: and organization_id = v_wlji_org_id(v_idx);
11794: exception
11795: when others then
19554: null,
19555: null,
19556: round(l_cur_job_qty*l_v_comp(l_in).qpa, 6)
19557: from wip_operations wo,
19558: mtl_system_items msi
19559: where wo.operation_seq_num = l_v_comp(l_in).job_op_seq_num
19560: and wo.wip_entity_id= p_wip_entity_id
19561: and msi.inventory_item_id = l_v_comp(l_in).item_id
19562: and msi.organization_id = wo.organization_id
20107: msi.primary_uom_code,
20108: wsli.component_issue_quantity
20109: from wsm_starting_lots_interface wsli,
20110: wsm_lot_job_interface wlji,
20111: mtl_system_items msi
20112: where wsli.header_id = wlji.source_line_id
20113: and wlji.group_id = p_group_id
20114: and wlji.process_status = 2 -- WIP_CONSTANTS.running
20115: and wlji.mode_flag = 2