[Home] [Help]
287: type t_wsli_locator_id is table of wsm_starting_lots_interface.locator_id%type;
288: type t_wsli_revision is table of wsm_starting_lots_interface.revision%type;
289: type t_wsli_last_updated_by is table of wsm_starting_lots_interface.last_updated_by%type;
290: type t_wsli_created_by is table of wsm_starting_lots_interface.created_by%type;
291: type t_wsli_primary_uom_code is table of mtl_system_items.primary_uom_code%type;
292: type t_wsli_comp_issue_qty is table of wsm_starting_lots_interface.component_issue_quantity%type;
293:
294: v_wsli_hdr_id t_wsli_header_id := t_wsli_header_id();
295: v_wsli_lot_number t_wsli_lot_number := t_wsli_lot_number();
317: locator_id wsm_starting_lots_interface.locator_id%type,
318: revision wsm_starting_lots_interface.revision%type,
319: last_updated_by wsm_starting_lots_interface.last_updated_by%type,
320: created_by wsm_starting_lots_interface.created_by%type,
321: primary_uom_code mtl_system_items.primary_uom_code%type,
322: comp_issue_quantity wsm_starting_lots_interface.component_issue_quantity%type
323: );
324:
325: v_rec_wsli rec_wsli; -- Not used
1978: l_stmt_num := 50;
1979: begin
1980: select unique(inventory_item_id)
1981: into v_wljdi_inventory_item_id_old(l_d)
1982: from mtl_system_items_kfv
1983: where concatenated_segments = v_wljdi_inventory_item_old(l_d)
1984: and organization_id = p_org_id;
1985: exception
1986: when others then
1990: else
1991: begin
1992: select concatenated_segments
1993: into v_wljdi_inventory_item_old(l_d)
1994: from mtl_system_items_kfv
1995: where inventory_item_id = v_wljdi_inventory_item_id_old(l_d)
1996: and organization_id = p_org_id;
1997: exception
1998: when others then
2023: else
2024: begin
2025: select unique(inventory_item_id)
2026: into v_wljdi_inventory_item_id_new(l_d)
2027: from mtl_system_items_kfv
2028: where concatenated_segments = v_wljdi_inventory_item_new(l_d)
2029: and organization_id = p_org_id;
2030: exception
2031: when others then
2037: else
2038: begin
2039: select concatenated_segments
2040: into v_wljdi_inventory_item_new(l_d)
2041: from mtl_system_items_kfv
2042: where inventory_item_id = v_wljdi_inventory_item_id_new(l_d)
2043: and organization_id = p_org_id;
2044: exception
2045: when others then
2068: else
2069: begin
2070: select unique(inventory_item_id)
2071: into v_wljdi_primary_item_id(l_d)
2072: from mtl_system_items_kfv
2073: where concatenated_segments = v_wljdi_primary_item(l_d)
2074: and organization_id = p_org_id;
2075: exception
2076: when others then
2095: else
2096: begin
2097: select unique(inventory_item_id)
2098: into v_wljdi_src_phantom_item_id(l_d)
2099: from mtl_system_items_kfv
2100: where concatenated_segments = v_wljdi_src_phantom_item(l_d)
2101: and organization_id = p_org_id;
2102: exception
2103: when others then
2109: else
2110: begin
2111: select concatenated_segments
2112: into v_wljdi_src_phantom_item(l_d)
2113: from mtl_system_items_kfv
2114: where inventory_item_id = v_wljdi_src_phantom_item_id(l_d)
2115: and organization_id = p_org_id;
2116: exception
2117: when others then
5704: /*
5705: begin
5706: select 1
5707: into l_comp_exist
5708: from mtl_system_items_kfv
5709: where organization_id = p_org_id
5710: and inventory_item_id=l_item_old_id;
5711: exception
5712: when others then
5773: --check if the inventory item id old is valid
5774: begin
5775: select 1
5776: into l_comp_exist
5777: from mtl_system_items_kfv
5778: where organization_id = p_org_id
5779: and inventory_item_id=l_item_old_id;
5780: exception
5781: when others then
5827:
5828: /*begin
5829: select 1
5830: into l_comp_exist
5831: from mtl_system_items_kfv
5832: where inventory_item_id=l_item_id
5833: and organization_id=p_org_id;
5834: exception
5835: when others then
5919: l_supply_subinventory IS NULL) then
5920:
5921: select wip_supply_subinventory, wip_supply_locator_id
5922: into l_supply_subinventory, l_supply_locator_id
5923: from mtl_system_items
5924: where inventory_item_id = l_item_id
5925: and organization_id = p_org_id;
5926:
5927: if l_supply_subinventory is null then
5945:
5946: /*begin
5947: select 1
5948: into l_comp_exist
5949: from mtl_system_items_kfv
5950: where inventory_item_id=l_item_id
5951: and organization_id=p_org_id;
5952: exception
5953: when others then
6050: l_supply_subinventory IS NULL) then
6051:
6052: select wip_supply_subinventory, wip_supply_locator_id
6053: into l_supply_subinventory, l_supply_locator_id
6054: from mtl_system_items
6055: where inventory_item_id = l_item_id
6056: and organization_id = p_org_id;
6057:
6058: if l_supply_subinventory is null then
8442: l_num_records := 0;
8443: begin
8444: select 1
8445: into l_num_records
8446: from mtl_system_items_kfv msi
8447: where msi.inventory_item_id = v_wlji_item_id(v_idx)
8448: and msi.organization_id = v_wlji_org_id(v_idx)
8449: and msi.lot_control_code = 2;
8450: exception
8460: l_num_records := 0;
8461: begin
8462: select 1
8463: into l_num_records
8464: from mtl_system_items_kfv msi
8465: where msi.inventory_item_id = v_wlji_item_id(v_idx)
8466: and msi.organization_id = v_wlji_org_id(v_idx)
8467: -- ST : Serial Support Project -----------------------------
8468: -- In OSFM we do support Serial controlled assemblies.......
8536:
8537: BEGIN
8538: select msi.serial_number_control_code
8539: into l_serial_ctrl_code
8540: from mtl_system_items_kfv msi
8541: where msi.inventory_item_id = v_wlji_item_id(v_idx)
8542: and msi.organization_id = v_wlji_org_id(v_idx);
8543:
8544: IF l_serial_ctrl_code = 2 THEN -- Predefined serial control...
8748: into l_sub_loc_control,
8749: l_org_loc_control,
8750: l_restrict_locators_code,
8751: l_item_loc_control
8752: FROM mtl_system_items MS,
8753: mtl_secondary_inventories MSUB,
8754: mtl_parameters MP
8755: WHERE MP.organization_id = v_wlji_org_id(v_idx)
8756: AND MS.organization_id = v_wlji_org_id(v_idx)
9339: into l_sub_loc_control,
9340: l_org_loc_control,
9341: l_restrict_locators_code,
9342: l_item_loc_control
9343: FROM mtl_system_items MS,
9344: mtl_secondary_inventories MSUB,
9345: mtl_parameters MP
9346: WHERE MP.organization_id = v_wlji_org_id(v_idx)
9347: AND MS.organization_id = v_wlji_org_id(v_idx)
9485: into l_sub_loc_control,
9486: l_org_loc_control,
9487: l_restrict_locators_code,
9488: l_item_loc_control
9489: FROM mtl_system_items MS,
9490: mtl_secondary_inventories MSUB,
9491: mtl_parameters MP
9492: WHERE MP.organization_id = v_wlji_org_id(v_idx)
9493: AND MS.organization_id = v_wlji_org_id(v_idx)
10182:
10183: BEGIN
10184: select msi.serial_number_control_code
10185: into l_serial_ctrl_code
10186: from mtl_system_items_kfv msi,wip_discrete_jobs wdj
10187: where msi.inventory_item_id = wdj.primary_item_id
10188: and msi.organization_id = v_wlji_org_id(v_idx)
10189: and wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
10190: and wdj.organization_id = v_wlji_org_id(v_idx);
10581: select SERIAL_NUMBER_CONTROL_CODE,
10582: revision_qty_control_code -- Add bug 2963225
10583: into dummy_number,
10584: l_rev_control_code -- Add bug 2963225
10585: from mtl_system_items_kfv msi
10586: where msi.inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
10587: and msi.organization_id = v_wlji_org_id(v_idx);
10588:
10589: -- Added code to restrict serial tagged components also.
10939: l_segment5,l_segment6,l_segment7,l_segment8,
10940: l_segment9,l_segment10,l_segment11,l_segment12,
10941: l_segment13,l_segment14,l_segment15,l_segment16,
10942: l_segment17,l_segment18,l_segment19,l_segment20
10943: FROM mtl_system_items
10944: WHERE inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
10945: AND organization_id=v_wlji_org_id(v_idx);
10946:
10947: EXCEPTION
11891: l_stmt_num := 1040.3;
11892: begin
11893: select unique(concatenated_segments)
11894: into l_assembly_name
11895: from mtl_system_items_kfv
11896: where inventory_item_id = v_wlji_item_id(v_idx)
11897: and organization_id = v_wlji_org_id(v_idx);
11898: exception
11899: when others then
20146: null,
20147: null,
20148: round(l_cur_job_qty*l_v_comp(l_in).qpa, 6)
20149: from wip_operations wo,
20150: mtl_system_items msi
20151: where wo.operation_seq_num = l_v_comp(l_in).job_op_seq_num
20152: and wo.wip_entity_id= p_wip_entity_id
20153: and msi.inventory_item_id = l_v_comp(l_in).item_id
20154: and msi.organization_id = wo.organization_id
20763: msi.primary_uom_code,
20764: wsli.component_issue_quantity
20765: from wsm_starting_lots_interface wsli,
20766: wsm_lot_job_interface wlji,
20767: mtl_system_items msi
20768: where wsli.header_id = wlji.source_line_id
20769: and wlji.group_id = p_group_id
20770: and wlji.process_status = 2 -- WIP_CONSTANTS.running
20771: and wlji.mode_flag = 2