Item creation using open interface


How to create inventory item using open interface

1. Prepare insert script and pump details into mtl_system_items_interface table
2. Run standard concurrent program (Import items) from inventory module

Code snippets

INSERT
INTO MTL_SYSTEM_ITEMS_INTERFACE
  (
    organization_id,
    set_process_id,
    process_flag,
    transaction_type,
    last_updated_by,
    last_update_date,
    last_update_login,
    creation_date,
    created_by,
    segment1,
    description,
    template_id,
    primary_uom_code,
    tracking_quantity_ind,
    secondary_uom_code,
    secondary_default_ind,
    inventory_item_status_code,
    inventory_item_flag,
    stock_enabled_flag,
    mtl_transactions_enabled_flag,
    reservable_type,
    shelf_life_code,
    shelf_life_days,
    lot_control_code,
    auto_lot_alpha_prefix,
    start_auto_lot_number,
    lot_split_enabled,
    lot_merge_enabled,
    lot_divisible_flag,
    bom_enabled_flag,
    bom_item_type,
    costing_enabled_flag,
    receipt_required_flag,
    inspection_required_flag,
    purchasing_item_flag,
    purchasing_enabled_flag,
    allow_item_desc_update_flag,
    rfq_required_flag,
    taxable_flag,
    receiving_routing_id,
    days_early_receipt_allowed,
    days_late_receipt_allowed,
    receipt_days_exception_code,
    qty_rcv_exception_code,
    qty_rcv_tolerance,
    inventory_planning_code,
    planning_make_buy_code,
    min_minmax_quantity,
    max_minmax_quantity,
    minimum_order_quantity,
    maximum_order_quantity,
    source_subinventory,
    mrp_safety_stock_code,
    mrp_planning_code,
    returnable_flag,
    internal_order_flag,
    internal_order_enabled_flag,
    customer_order_flag,
    customer_order_enabled_flag,
    shippable_item_flag,
    so_transactions_flag,
    default_so_source_type,
    invoiceable_item_flag,
    invoice_enabled_flag,
    sales_account,
    process_quality_enabled_flag,
    process_costing_enabled_flag,
    recipe_enabled_flag,
    process_execution_enabled_flag,
    hazardous_material_flag ,
    cas_number
  )
SELECT /*(select organization_id
      from hr_all_organization_units
      where upper(name) = organization_name )*/
      121 organization_id,
  1 set_process_id,
  1 process_flag,
  'CREATE' transaction_type,
  1 last_updated_by,
  sysdate last_update_date,
  1 last_update_login,
  sysdate creation_date,
  1 created_by,
  item_code segment1,
  item_description,
  (select template_id from mtl_item_templates_all_v  where template_name = i.template_code) template_id,
  --DECODE(primary_uom, 'EACH','EA') primary_uom,
  (select uom_code from MTL_UNITS_OF_MEASURE_VL where upper(unit_of_measure) =upper(i.primary_uom)) primary_uom,
  decode(primary_tracking, 'Primary','P','Primary & Secondary','PS','PS') p_tracking_indicator,
 (select uom_code from MTL_UNITS_OF_MEASURE_VL where upper(unit_of_measure) =upper(i.secondary_uom)) secondary_uom,
  decode(primary_tracking, 'Primary',null,'Primary & Secondary','D','D') s_tracking_indicator,
  'Active' Inventory_item_status_code,
  null /*decode (upper(inventory_item_flag), 'YES','Y','NO','N')*/ inventory_item_flag,
  null /*decode (upper(stockable_flag), 'YES','Y','NO','N')*/ stockable_flag,
   null material_txn_enable_flag,
   null reservable_type,
   null shelf_life_code,
    shelf_life_days,
    decode(trim(lot_control),'No Control',1,'Full Control',2) lot_control,
    lot_prefix,
    lot_start_number,
    null lot_split_enabled,
    null lot_merge_enabled,
    null lot_divisible_flag,
    null BOM_ENABLED_FLAG, --'Y'
    null BOM_ITEM_TYPE,
    null COSTING_ENABLED_FLAG,--'Y'
    null RECEIPT_REQUIRED_FLAG,--'Y'
    null INSPECTION_REQUIRED_FLAG,--'N'
    null PURCHASING_ITEM_FLAG,--'Y'
    null PURCHASING_ENABLED_FLAG,--'Y'
    null ALLOW_ITEM_DESC_UPDATE_FLAG, --'Y'
    null RFQ_REQUIRED_FLAG,   --'Y'
    null TAXABLE_FLAG,
    decode(initcap(ir_receipt_routing),'Standard',1,'Inspection',2,'Direct',3,1) RECEIVING_ROUTING_ID,
    null DAYS_EARLY_RECEIPT_ALLOWED,
    null DAYS_LATE_RECEIPT_ALLOWED,
    null RECEIPT_DAYS_EXCEPTION_CODE,
    null QTY_RCV_EXCEPTION_CODE,
    null QTY_RCV_TOLERANCE,
    null INVENTORY_PLANNING_CODE,
    null PLANNING_MAKE_BUY_CODE,
    null MIN_MINMAX_QUANTITY,
    null MAX_MINMAX_QUANTITY,
    null MINIMUM_ORDER_QUANTITY,
    null MAXIMUM_ORDER_QUANTITY,
    null SOURCE_SUBINVENTORY,
    null MRP_SAFETY_STOCK_CODE,
    null MRP_PLANNING_CODE,
    null RETURNABLE_FLAG,
    null INTERNAL_ORDER_FLAG,
    null INTERNAL_ORDER_ENABLED_FLAG,
    null CUSTOMER_ORDER_FLAG,   --'Y'
    null CUSTOMER_ORDER_ENABLED_FLAG, --'Y'
    null SHIPPABLE_ITEM_FLAG, --'Y'
    null SO_TRANSACTIONS_FLAG, --'Y'
    null DEFAULT_SO_SOURCE_TYPE,
    null /*DECODE(upper(invoicable_item), 'YES','Y','NO','N')*/ invoicable_item_flag,
    null /*DECODE(upper(INVOICE_ENABLED), 'YES','Y','NO','N')*/ invoice_enabled_flag,
   (select code_combination_id from gl_code_combinations_kfv where concatenated_segments = SALES_ACCOUNT ) sales_account,
    null /*DECODE(upper(process_quality_enabled), 'YES','Y','NO','N')*/ PROCESS_QUALITY_ENABLED_FLAG,
    null /*DECODE(upper(process_costing_enabled), 'YES','Y','NO','N')*/ PROCESS_COSTING_ENABLED_FLAG,
    null /*DECODE(upper(receipe_enabled), 'YES','Y','NO','N')*/ RECIPE_ENABLED_FLAG,
    null /*DECODE(upper(process_execution_enabled), 'YES','Y','NO','N')*/ PROCESS_EXECUTION_ENABLED_FLAG,
    'N' HAZARDOUS_MATERIAL_FLAG ,
    null CAS_NUMBER
FROM dcf_item_upload_stg i;

Standard concurrent program call using submit request

  v_request_id := Fnd_Request.submit_request
                       (
                         application => 'INV',
                         program     => 'INCOIN',
                         description => 'Item Open Interface',
                         start_time  => SYSDATE,
                         argument1 => l_org_id,  -- Organization id
                         argument2 => 1,    -- All organizations
                         argument3 => 1,    -- Validate Items
                         argument4 => 1,    -- Process Items
                         argument5 => 1,    -- Delete Processed Rows
                         argument6 => 1, -- Process Set (Null for All)
                         argument7 => 1,    -- Create or Update Items
                         argument8 => 1     -- Gather Statistics
                       );

Comments

Post a Comment

Popular posts from this blog

How to use lexical parameters in Data template (XML document) R12

Lexical parameters can be added in data definition using &variable name This is flexible variable string where you can execute runtime conditions based on user-inputs Example 1. Append &gc_journal_filter at the end of sql statement 2. Define beforereport function in package signature FUNCTION beforereport RETURN BOOLEAN; 3. Define all the report parameters as a package variable in package signature including lexical parameters 4. Implement beforereport function by building your runtime conditions to be added to SQL statements  FUNCTION beforereport RETURN BOOLEAN IS  BEGIN          IF p_journal_source IS NULL THEN       gc_journal_filter := ' AND jh.je_source IN (''AutoCopy'',''Manual'')';     END IF;          IF p_journal_category IS NULL THEN       gc_journal_filter := 'AND jh.je_category IN (''Other'') ';     END IF;    RETURN TRUE;  END;
Read more

Query to get FSG report details with row/column set

Below query is to fetch FSG report titles, names, and description along with will give a view to list of row sets and column sets associated with the report. -- FSG REPORTS -- SELECT fst.id_flex_structure_name , R.NAME , R.report_title , R.DESCRIPTION , R.column_set "COLUMN SET" , rw2.structure_id , rw2.DESCRIPTION "COL DESC" , R.row_set "ROW SET" , rw.DESCRIPTION "ROW DESC" , R.report_display_set "DISPLAY SET" , R.content_set "CONTENT SET" , R.row_order "ROW ORDER" , R.rounding_option "RND" , u.user_name , u.DESCRIPTION , R.creation_date FROM rg_reports_v R    , fnd_id_flex_structures_v fst    , fnd_user u    , rg_report_axis_sets_v rw    , rg_report_axis_sets_v rw2 WHERE r.structure_id = fst.id_flex_num AND R.row_set_id     = rw.axis_set_id AND r.column_set_id  = rw2.axis_set_id AND SUBSTR(fst.id_flex_structure_name,1,2) IN ('DC') AND R.created_by     = u.user_id OR
Read more

Sub Inventory Transfer API.

Image
Sub Inventory transfer is used to move materials from one sub-inventory to another within same organization. This is especially used to 1. Adjust on-hand quantities between sub-inventories 2. Transfer to another sub-inventories for specific business need like additional sales 3. Damages/expiry products can be moved to non-asset sub-inventories to discard it from inventory valuation report and so on., Key elements 1. From sub inventory 2. To sub inventory 3. Organization 4. Inventory item 5. Transact quantity 6. Transact unit of measure 7. Transaction type 8. Transaction action 9. Transact Reference 10. Transact date 11. Lot Number Open Interfaces mtl_transactions_interface - is used to populate sub inventory transfer details mtl_transaction_lots_interface - is used to populate lot details for particular sub inventory transfer Base Tables mtl_material_transactions - is used to get all type of material transactions like sub-inventory transfer, inter-org tra
Read more