CREATE AN INVENTORY ITEM IN ORACLE APPS FROM BACKEND(ITEM IMPORT)

CREATE AN INVENTORY ITEM IN ORACLE APPS FROM BACKEND(ITEM IMPORT) INSERT SCRIPT


STEP1: Get the template id using the below query 

SELECT template_id
      ,template_name
      ,description
FROM   MTL_ITEM_TEMPLATES; 

STEP2: Check the choosed template's related item attribute values using the below query

SELECT template_id
      ,attribute_name
      ,enabled_flag
      ,report_user_value
FROM   MTL_ITEM_TEMPL_ATTRIBUTES
WHERE  template_id = 107; --finished_goods


STEP3:  Run the below insert script to create a record in the standard item interface table to create a item based on item template 

INSERT
INTO
  MTL_SYSTEM_ITEMS_INTERFACE
  (
    process_flag,
    set_process_id,
    transaction_type,
    organization_id,
    segment1,
    description,
    TEMPLATE_ID
  )
  VALUES
  (
    1,
    1,
    'CREATE',
    204,
    'PK123456',
    'Sample PK Item Testing',
    107
  );

STEP4: Run the wrapper script given in the below link to submit the "ITEM IMPORT" concurrent program from backend

DECLARE
v_sub_status BOOLEAN;

FUNCTION submit_item_import
RETURN BOOLEAN
IS
 v_organization_id   NUMBER := 0;
 v_request_id        NUMBER := 0;
 v_phase             VARCHAR2(240);
 v_status            VARCHAR2(240);
 v_request_phase     VARCHAR2(240);
 v_request_status    VARCHAR2(240);
 v_finished          BOOLEAN;
 v_message           VARCHAR2(240);
 v_sub_status        BOOLEAN := FALSE;

BEGIN
  FND_GLOBAL.APPS_INITIALIZE(1318,50583,401);
  MO_GLOBAL.SET_POLICY_CONTEXT('S','204');
  FND_GLOBAL.SET_NLS_CONTEXT('AMERICAN');  --This is for Language specific view
  MO_GLOBAL.INIT('INV');

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

  IF ( v_request_id = 0 ) THEN
    dbms_output.put_line( 'Item Import Program Not Submitted');
        v_sub_status := FALSE;
  ELSE
    v_finished := fnd_concurrent.wait_for_request
                            (
                             request_id => v_request_id,
                             interval   => 0,
                             max_wait   => 0,
                             phase      => v_phase,
                             status     => v_status,
                             dev_phase  => v_request_phase,
                             dev_status => v_request_status,
                             message    => v_message
                             );

    DBMS_OUTPUT.PUT_LINE('Request Phase  : '|| v_request_phase );
    DBMS_OUTPUT.PUT_LINE('Request Status : '|| v_request_status );
    DBMS_OUTPUT.PUT_LINE('Request id     : '|| v_request_id );

  --Testing end statusv_request_id
    IF ( UPPER(v_request_status) = 'NORMAL') THEN    
          v_sub_status := TRUE;
    END IF;
  END IF;
  RETURN (v_sub_status);
EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('Error in Submitting Item Import Program and error is '||SUBSTR(SQLERRM,1,200));
   RETURN (FALSE); 
END submit_item_import;

BEGIN
  v_sub_status  := submit_item_import();
  IF v_sub_status THEN
    DBMS_OUTPUT.PUT_LINE( 'Item Import Status: Sucessful');      
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'Item Import Status: Failed');
  END IF;

END;   
/
commit;


STEP5: Run the below query to verify the creation of the item 

SELECT *
FROM   mtl_system_items_b

WHERE  segment1 = 'PK123456';