API to Create Employee in Oracle Applications

Oracle Application

API To Create Employee

A beginners guide: - API to create Employee

What is APIs? How to create an Employee using API?

– Application Programming Interfaces (APIs) are stored procedures that enable you to insert and update data in Oracle Applications.

– HR_EMPLOYEE_API.CREATE_EMPLOYEE is a name of API to create an Employee.

Sample Code:

/*--------------------------------------------------------------------------------------------------------------------*/

DECLARE
 

 l_person_id                           PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
 l_employee_number              PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE    := 'RRK_01';
 l_full_name                            PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
 l_assignment_id                     PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
 l_object_version_number      PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
 l_asg_object_version_number NUMBER;
 l_per_effective_start_date     PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
 l_per_effective_end_date      PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE;
 l_per_comment_id                PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
 l_assignment_seq                  PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
 l_assignment_number            PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
 l_name_combination_warning  BOOLEAN;
 l_assign_payroll_warning         BOOLEAN;
 l_orig_hire_warning                 BOOLEAN;

BEGIN
           hr_employee_api.create_employee
           (  

             --INPUT Parameter
               P_HIRE_DATE                      => TO_DATE('14-JUN-2012'),
               P_BUSINESS_GROUP_ID  => 101, --fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
               P_LAST_NAME                   => 'Kesarkar',
               p_first_name                           => 'Rachana',
               p_middle_names                     => 'Ramakant',
               P_SEX                                   => 'F',
               P_NATIONAL_IDENTIFIER=> '183-25-2525',
               p_date_of_birth                      => TO_DATE('25-FEB-1990'),
               p_known_as                           => 'Rachana',
              --OUTPUT Parameter
               P_EMPLOYEE_NUMBER   => l_employee_number,
               p_person_id                           => l_person_id,
               p_assignment_id                     => l_assignment_id,
               P_PER_OBJECT_VERSION_NUMBER => l_object_version_number,
               p_asg_object_version_number=> l_asg_object_version_number,
               p_per_effective_start_date      => l_per_effective_start_date  ,
               P_PER_EFFECTIVE_END_DATE=> l_per_effective_end_date,
               P_FULL_NAME                   => l_full_name,
               P_PER_COMMENT_ID       => l_per_comment_id,
               p_assignment_sequence          => l_assignment_seq,
               p_assignment_number             => l_assignment_number,
               P_NAME_COMBINATION_WARNING => l_name_combination_warning  ,
               P_ASSIGN_PAYROLL_WARNING  => l_assign_payroll_warning  ,
               p_orig_hire_warning                => l_orig_hire_warning
        );
      
        dbms_output.put_line('Employee with Employee Number: ' || l_employee_number || ' created sucessfully');
 
    COMMIT; 

END; 

/*--------------------------------------------------------------------------------------------------------------------*/

How to see the result?

– Navigate to HRMS Manage -> People -> Enter and Maintain.

 

– Enter the short character string to search an employee.

 

– Click on Find.

 

 

 

SQL Loader

Oracle Applications

-Simple and easy steps to oracle SQL LOADER

-A beginners guide for oracle  SQL LOADER

SQL loader introduction :-

wiki says - " SQL*Loader is a bulk loader utility used for moving (shifting) data from external files or flat files into the Oracle database. Its syntax is some what similar to that of the DB2 load utility . SQL*Loader supports various load formats(i.e. comma saprated,pipe saprated etc), selective loading, and multi-table loads." 

• how is become in simple and  easy steps

  so here is some sample of  SQL loaders .ctl file
  
  
  LOAD DATA <-----------"load data instruct computer to read data from flat file or .ctl file itself"
  INFILE *    <---------------"using * or one can give the path of file where the flat  file resides"
  APPEND INTO TABLE XX_TEMP_RCIA <----"An instruction to append i.e. to add in preexisted content"
  
  fields terminated by '|'  <--"this command specify from where the new coulum has started"
  TRAILING  NULLCOLS   <--"To trail the coulumns which had null coulumns"
    (
      ORIG_SYSTEM_CUSTOMER_REF          "TRIM(:ORIG_SYSTEM_CUSTOMER_REF)",
      CUSTOMER_NAME                                   "TRIM(:CUSTOMER_NAME)",
      ORIG_SYSTEM_ADDRESS_REF               "TRIM(:ORIG_SYSTEM_ADDRESS_REF)",
      ADDRESS1                                                   "TRIM(:ADDRESS1)",
      ADDRESS2                                                   "TRIM(:ADDRESS2)",
      ADDRESS3                                                   "TRIM(:ADDRESS3)",
      ADDRESS4                                                   "TRIM(:ADDRESS4)",
      CITY                                                           "TRIM(:CITY)",
      COUNTY                                                    "TRIM(:COUNTY)",
      STATE                                                         "TRIM(:STATE)",
      PROVINCE                                                 "TRIM(:PROVINCE)",
      COUNTRY                                                  "TRIM(:COUNTRY)",
      POSTAL_CODE                                          "TRIM(:POSTAL_CODE)",
      SITE_USE_CODE                                        "TRIM(:SITE_USE_CODE)",
      PRIMARY_SITE_USE_FLAG                     "TRIM(:PRIMARY_SITE_USE_FLAG)",
      CUSTOMER_STATUS                                "TRIM(:CUSTOMER_STATUS)",
      INSERT_UPDATE_FLAG                            "TRIM(:INSERT_UPDATE_FLAG)",
      LAST_UPDATED_BY                                 "TRIM(:LAST_UPDATED_BY)",
      LAST_UPDATE_DATE                                "TRIM(:LAST_UPDATE_DATE )",
      CREATED_BY                                              "TRIM(:CREATED_BY)",
      CREATION_DATE                                        "TRIM(:CREATION_DATE)",
      CUSTOMER_NUMBER                               "TRIM(:CUSTOMER_NUMBER)",
      CUSTOMER_NAME_PHONETIC               "TRIM(:CUSTOMER_NAME_PHONETIC)",
      CUSTOMER_CATEGORY_CODE               "TRIM(:CUSTOMER_CATEGORY_CODE)",
      LOCATION                                                    "TRIM(:LOCATION)",
      ORG_ID                                                          "TRIM(:ORG_ID)"
    )

Some points to avoid ERROR in SQL Loader :

1. The order of fields in SQL Loader must match with the order of fields in the data file.
2. If you are declaring any constant value to field, then it will be declared at the end and not at the start or in between fields otherwise the Loader will give Error. For example, in the above .ctl file, the constant will be declared after ORG_ID.
3. If there is not delimiter after last column, then the Loader will take last column value with Enter (ASCII value=13). So the value of the last column in table is not the same as the value in data file.

Oracle apps Account receivables Customer creation using interface

Oracle Applications

Simple and easy steps to oracle Account receivables

A beginners guide for oracle Account receivables

-Oracle apps Account receivables
-Oracle apps Account receivables Customer creation using interface

Introduction - 

 we can use customer interface to import recent, current or past information from other systems into our database. If customer information is imported from our source of information that is flat file,CSV,XML etc we can use CUTOMER INTERFACE to import additional data for that perticular customer.

Lets see via Simple diagram how it could be done -

Step 1 - create or get a flat file in which the data about customer is feeded. Here we need at least two flat files for updating two mandatory interface tables which is

RA_CUSTOMERS_INTERFACE_ALL
and
RA_CUSTOMER_PROFILES_INT_ALL

so for
RA_CUSTOMERS_INTERFACE_ALL flat file is

01-00-22|RRCUS011|01-00-22|3951 OXCXEAIC DR||||OXXNSIDE|SAN DIEGO|CA||US|92056|BILL_TO|N|A|I|99936||999036||||CUSTOMER|4999186|1000|

and for

RA_CUSTOMER_PROFILES_INT_ALL flat file is

01-00-22|I|DEFAULT|N|99936| |99936| |1000|

Step 2 - for loading this data into staging tables we need SQL * Loader so

for first Flat file SQL * Loader is


LOAD DATA
INFILE *
APPEND INTO TABLE XX_TEMP_RCIA
fields terminated by '|'
TRAILING  NULLCOLS
  (
    ORIG_SYSTEM_CUSTOMER_REF       "TRIM(:ORIG_SYSTEM_CUSTOMER_REF)",
    CUSTOMER_NAME                               "TRIM(:CUSTOMER_NAME)",
    ORIG_SYSTEM_ADDRESS_REF          "TRIM(:ORIG_SYSTEM_ADDRESS_REF)",
    ADDRESS1                                              "TRIM(:ADDRESS1)",
    ADDRESS2                                              "TRIM(:ADDRESS2)",
    ADDRESS3                                              "TRIM(:ADDRESS3)",
    ADDRESS4                                              "TRIM(:ADDRESS4)",
    CITY                                                         "TRIM(:CITY)",
    COUNTY                                                 "TRIM(:COUNTY)",
    STATE                                                      "TRIM(:STATE)",
    PROVINCE                                              "TRIM(:PROVINCE)",
    COUNTRY                                               "TRIM(:COUNTRY)",
    POSTAL_CODE                                       "TRIM(:POSTAL_CODE)",
    SITE_USE_CODE                                    "TRIM(:SITE_USE_CODE)",
    PRIMARY_SITE_USE_FLAG                 "TRIM(:PRIMARY_SITE_USE_FLAG)",
    CUSTOMER_STATUS                            "TRIM(:CUSTOMER_STATUS)",
    INSERT_UPDATE_FLAG                       "TRIM(:INSERT_UPDATE_FLAG)",
    LAST_UPDATED_BY                             "TRIM(:LAST_UPDATED_BY)",
    LAST_UPDATE_DATE                           "TRIM(:LAST_UPDATE_DATE )",
    CREATED_BY                                         "TRIM(:CREATED_BY)",
    CREATION_DATE                                  "TRIM(:CREATION_DATE)",
    CUSTOMER_NUMBER                          "TRIM(:CUSTOMER_NUMBER)",
    CUSTOMER_NAME_PHONETIC          "TRIM(:CUSTOMER_NAME_PHONETIC)",
    CUSTOMER_CATEGORY_CODE         "TRIM(:CUSTOMER_CATEGORY_CODE)",
    LOCATION                                              "TRIM(:LOCATION)",
    ORG_ID                                                     "TRIM(:ORG_ID)"
  )


and for second Flat file SQL * Loader is

 

LOAD DATA
INFILE *
APPEND INTO TABLE XX_TEMP_RCPIA
FIELDS TERMINATED
BY '|'
TRAILING  NULLCOLS
(
    ORIG_SYSTEM_CUSTOMER_REF      LOAD DATA
INFILE *
APPEND INTO TABLE XX_TEMP_RCPIA
FIELDS TERMINATED
BY '|'
TRAILING  NULLCOLS
(
    ORIG_SYSTEM_CUSTOMER_REF             "TRIM(:ORIG_SYSTEM_CUSTOMER_REF)",
    INSERT_UPDATE_FLAG                              "TRIM(:INSERT_UPDATE_FLAG)",
    CUSTOMER_PROFILE_CLASS_NAME     "TRIM(:CUSTOMER_PROFILE_CLASS_NAME)",
    CREDIT_HOLD                                             "TRIM(:CREDIT_HOLD)",
    LAST_UPDATED_BY                                   "TRIM(:LAST_UPDATED_BY)",
    LAST_UPDATE_DATE                       "TRIM(:LAST_UPDATE_DATE)",
    CREATED_BY                                              "TRIM(:CREATED_BY)",
    CREATION_DATE                                       "TRIM(:CREATION_DATE)",
    ORG_ID                                                    "TRIM(:ORG_ID )"
 )

Step 3 - Now register this SQL Loader or control file into the Oracle application in System Admin Responsibility and first register the executable then create a concurrent program for both of the SQL Loader.

Step 4 - Now we need a PL/SQL block to insert data from our staging tables to interface tables


DECLARE

  CURSOR CUR_RCIA           ---===++ FECTING DATA FOR ra_customers_interface_all;
  IS
    SELECT * FROM XX_TEMP_RCIA;
   
   
  CURSOR CUR_RCPIA          ----===+ FETCHING DATA FOR ra_customer_profiles_int_all
  IS
    SELECT * FROM XX_TEMP_RCPIA;
  -----------------
  --VARIBALE FOR CUR_RCIA
  -----------------
  RCIA_FLAG                   VARCHAR2(3);
  REC_RCIA XX_TEMP_RCIA%ROWTYPE;
  C_ORIG_SYSTEM_CUSTOMER_REF  VARCHAR2(240);
  C_CUSTOMER_NAME             VARCHAR2(360);
  C_ORIG_SYSTEM_ADDRESS_REF   VARCHAR2(240);
  C_ADDRESS1                  VARCHAR2(240);
  C_ADDRESS2                  VARCHAR2(240);
  C_ADDRESS3                  VARCHAR2(240);
  C_ADDRESS4                  VARCHAR2(240);
  C_CITY                      VARCHAR2(240);
  C_COUNTY                    VARCHAR2(240);
  C_STATE                     VARCHAR2(240);
  C_PROVINCE                  VARCHAR2(240);
  C_COUNTRY                   VARCHAR2(240);
  C_POSTAL_CODE               VARCHAR2(240);
  C_SITE_USE_CODE             VARCHAR2(240);
  C_PRIMARY_SITE_USE_FLAG     VARCHAR2(1);
  C_CUSTOMER_STATUS           VARCHAR2(1);
  C_INSERT_UPDATE_FLAG        VARCHAR2(1);
  C_LAST_UPDATED_BY           VARCHAR2(30);
  C_LAST_UPDATE_DATE          DATE;
  C_CREATED_BY                VARCHAR2(30);
  C_CREATION_DATE             DATE;
  C_CUSTOMER_NUMBER           NUMBER(15);
  C_CUSTOMER_NAME_PHONETIC    VARCHAR2(320);
  C_CUSTOMER_CATEGORY_CODE    VARCHAR2(30);
  C_LOCATION                  VARCHAR2(30);
  C_ORG_ID                    NUMBER(15);

  -----------------
  --VARIBALE FOR CUR_RCPIA
  -----------------
  RCPIA_FLAG                   VARCHAR2(3);
  REC_RCPIA XX_TEMP_RCPIA%ROWTYPE;    --TEMP VARIABLE FOR FOR LOOP
  CP_ORIG_SYSTEM_CUSTOMER_REF     VARCHAR2(240);
  CP_INSERT_UPDATE_FLAG           VARCHAR2(1);
  CP_CUSTOMER_PROFILE_CLASS_NAME  VARCHAR2(30);
  CP_CREDIT_HOLD                  VARCHAR2(1);
  CP_LAST_UPDATED_BY              NUMBER(15);
  CP_LAST_UPDATE_DATE             DATE;
  CP_CREATED_BY                   NUMBER(15);
  CP_CREATION_DATE                DATE;
  CP_ORG_ID                       NUMBER(15);




BEGIN

FOR REC_RCIA IN CUR_RCIA
LOOP
RCIA_FLAG := 'YES';
/*
================================================================================
SAMPLE VALIDATION======================================================================
================================================================================

*/
 IF REC_RCIA.orig_system_customer_ref IS NOT NULL THEN
      BEGIN
        select ORIG_SYSTEM_CUSTOMER_REF
        into C_ORIG_SYSTEM_CUSTOMER_REF
        FROM AR.RA_CUSTOMERS_INTERFACE_ALL;
      EXCEPTION
      when OTHERS then
        DBMS_OUTPUT.PUT_LINE('NO CUSTOMER REFERENCE');
        RCIA_FLAG :='N';
      END;
    else
        --if REC_RCIA.ORIG_SYSTEM_CUSTOMER_REF is null then
        RCIA_FLAG :='N';
        DBMS_OUTPUT.PUT_LINE('NO ITEM FOUND');
     -- END IF; --IF FOR THIS >>IF REC_RCIA.orig_system_customer_ref IS NULL
    END IF;   --IF FOR THIS >>IF REC_RCIA.orig_system_customer_ref IS NOT NULL
 
/*  
================================================================================
SAMPLE validation======================================================================
================================================================================  
*/

  IF REC_RCIA.CUSTOMER_NAME IS NOT NULL THEN
      BEGIN
        select CUSTOMER_NAME
        into C_CUSTOMER_NAME
        FROM AR.RA_CUSTOMERS_INTERFACE_ALL;
      EXCEPTION
      when OTHERS then
        DBMS_OUTPUT.PUT_LINE('NO CUSTOMER name');
        RCIA_FLAG :='N';
      END;
    else
        RCIA_FLAG :='N';
        DBMS_OUTPUT.PUT_LINE('NO ITEM FOUND');
     -- END IF; --IF FOR THIS >>IF REC_RCIA.CUSTOMER_NAME IS NULL
    END IF;   --IF FOR THIS >>IF REC_RCIA.CUSTOMER_NAME IS NOT NULL
 
/*  
================================================================================
SAMPLE  validation======================================================================
================================================================================  
*/

/*  
================================================================================
SAMPLE validation======================================================================
================================================================================  
*/

 IF REC_RCIA.ORIG_SYSTEM_ADDRESS_REF IS NOT NULL THEN
      BEGIN
        select ORIG_SYSTEM_ADDRESS_REF
        into C_ORIG_SYSTEM_ADDRESS_REF
        FROM AR.RA_CUSTOMERS_INTERFACE_ALL;
      EXCEPTION
      when OTHERS then
        DBMS_OUTPUT.PUT_LINE('ORIG_SYSTEM_ADDRESS_REF');
        RCIA_FLAG :='N';
      END;
    else
        RCIA_FLAG :='N';
        DBMS_OUTPUT.PUT_LINE('NO ITEM FOUND');
     -- END IF; --IF FOR THIS >>IF REC_RCIA.ORIG_SYSTEM_ADDRESS_REF IS NULL
    END IF;   --IF FOR THIS >>IF REC_RCIA.ORIG_SYSTEM_ADDRESS_REF IS NOT NULL
/*  
================================================================================
SAMPLE validation======================================================================
================================================================================  
*/


if RCIA_FLAG LIKE 'YES'
then
BEGIN
insert
INTO AR.RA_CUSTOMERS_INTERFACE_ALL
  (
    orig_system_customer_ref,
    customer_name,
    orig_system_address_ref,
    address1,
    address2,
    address3,
    address4,
    city,
    county,
    state,
    province,
    country,
    postal_code,
    site_use_code,
    primary_site_use_flag,
    customer_status,
    insert_update_flag,
    last_updated_by,
    last_update_date,
    created_by,
    creation_date,
    customer_number,
    customer_name_phonetic,
    customer_category_code,
    location,
    ORG_ID
  )
  VALUES
  (
    REC_RCIA.orig_system_customer_ref,
    REC_RCIA.customer_name,
    REC_RCIA.orig_system_address_ref,
    REC_RCIA.address1,
    REC_RCIA.address2,
    REC_RCIA.address3,
    REC_RCIA.address4,
    REC_RCIA.city,
    REC_RCIA.county,
    REC_RCIA.state,
    REC_RCIA.province,
    REC_RCIA.country,
    REC_RCIA.postal_code,
    REC_RCIA.site_use_code,
    REC_RCIA.primary_site_use_flag,
    REC_RCIA.customer_status,
    REC_RCIA.insert_update_flag,
    REC_RCIA.last_updated_by,
    REC_RCIA.last_update_date,
    REC_RCIA.created_by,
    REC_RCIA.creation_date,
    REC_RCIA.customer_number,
    REC_RCIA.customer_name_phonetic,
    REC_RCIA.customer_category_code,
    REC_RCIA.LOCATION,
    REC_RCIA.ORG_ID
  );
end;
end if;


END LOOP;



FOR REC_RCPIA IN CUR_RCPIA
LOOP


insert
INTO ar.RA_CUSTOMER_PROFILES_INT_ALL
  (
    orig_system_customer_ref,
    insert_update_flag,
    customer_profile_class_name,
    credit_hold,
    last_updated_by,
    last_update_date,
    created_by,
    creation_date,
    ORG_ID
  )
  VALUES
  (
    REC_RCPIA.orig_system_customer_ref,
    REC_RCPIA.INSERT_UPDATE_FLAG,
    REC_RCPIA.CUSTOMER_PROFILE_CLASS_NAME,
    REC_RCPIA.CREDIT_HOLD,
    REC_RCPIA.LAST_UPDATED_BY,
    REC_RCPIA.LAST_UPDATE_DATE,
    REC_RCPIA.CREATED_BY,
    REC_RCPIA.CREATION_DATE,
    REC_RCPIA.ORG_ID
  );



END LOOP;






END;

Inventory Management in Oracle Applications

Oracle Applications

Simple and easy steps to oracle Inventory management 

A beginners guide for oracle Inventory managment 

 - Oracle Inventory managment 

 - Oracle apps Item convertion using interface 

 - Oracle apps Item creation thru interface

 Introduction

You can import items from any source into Oracle Inventory using the Item Interface. When you import items via the Item Interface, you create new items in your Item Master organization or assign existing items to additional organizations. You can specify values for all the item attributes. Validation of imported items is done using the same rules as the item definition forms, so you are insured of valid items. The Item Interface reads data from two tables for importing items and item details. You use the MTL_SYSTEMS_ITEM_INTERFACE table for new item numbers and all item attributes. This is the main item interface table, and can be the only table you choose to use. MTL_ITEM_CATEGORIES_INTERFACE table is used to attach existing categories to the newly created Item. Table MTL_INTERFACE_ERRORS, is used for error tracking of all items that the Item Interface fails. Before you use the Item

Interface, you must insert the new Item’s information using SQL Loader or any other package. Oracle recommends you truncate all IOI interface tables before loading data inmtl_system_items_interface table:

MTL_SYSTEM_ITEMS_INTERFACE

MTL_INTERFACE_ERRORS

MTL_ITEM_REVISIONS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

Creation of flat files -

You need to create flat file for item which you want to inter via interface tables. Flat files which contains the mandatory attributes of MTL_SYSTEM_ITEMS_INTERFACE

And   MTL_ITEM_CATEGORIES_INTERFACE.

Minimum required columns of MTL_SYSTEM_ITEMS_INTERFACE when creating new items are:

·         PROCESS_FLAG = 1 {Changes to ‘7’ if the items importing is ‘Successful’}

·         TRANSACTION_TYPE = 'CREATE' {‘UPDATE’ – for updating existing item attributes}

·         SET_PROCESS_ID = 123 {any number can be chosen, here we will use 123}

·         ORGANIZATION_ID = Master Org

·         id.DESCRIPTION = 'Description of the item 'ITEM_NUMBER and/or SEGMENT (n)

For attaching Categories to new Items being created, upload the required data in table MTL_ITEM_CATEGORIES_INTERFACE. Minimum required columns when creating new items are:

·         PROCESS_FLAG = 1 { Changes to ‘7’ if the items importing is ‘Successful’}

·         TRANSACTION_TYPE = 'CREATE' {'UPDATE' or 'DELETE' is not supported}

·         ORGANIZATION_ID

·         ITEM_NUMBER

·         CATEGORY_SET_NAME { At NRB, can be either ‘Inventory’ or ‘Accounting’ }

·         CATEGORY_NAMESET_PROCESS_ID=123

Sample .CTL files used to import the data:

Sql  loader for adding data into staging table which is for item attributes

LOAD DATA

INFILE *

APPEND INTO TABLE XX_TEMP_ITEM02

FIELDS TERMINATED BY '|' TRAILING NULLCOLS

(PROCESS_FLAG                        INTEGER EXTERNAL,

TRANSACTION_TYPE               CHAR,

SET_PROCESS_ID                      INTEGER EXTERNAL,

ORGANIZATION_ID                  INTEGER EXTERNAL,

ITEM_NUMBER                          CHAR,

DESCRIPTION                            CHAR,

PRIMARY_UOM_CODE            CHAR,

ATTRIBUTE_CATEGORY          CHAR,

ATTRIBUTE1                               CHAR,

ATTRIBUTE2                               CHAR,

ATTRIBUTE3                               CHAR,

ATTRIBUTE15                             CHAR,

TEMPLATE_NAME                    CHAR)

This control file is for loading item cataegory

LOAD DATA
INFILE *

APPEND INTO XX_TEMP_CAT02

FIELDS TERMINATED BY '|' TRAILING NULLCOLS

(PROCESS_FLAG                  INTEGER EXTERNAL ,

TRANSACTION_TYPE         CHAR ,

SET_PROCESS_ID                INTEGER EXTERNAL ,

ORGANIZATION_ID             INTEGER EXTERNAL ,

ITEM_NUMBER                    CHAR ,

CATEGORY_SET_NAME     CHAR ,

CATEGORY_NAME              CHAR )

Register the concurrent program in application

·         After creating the .ctl  file you need to register the control file as executable in oracle application under the inventory manager responsibility  using Admin  responsibility 

·         After register the .ctl file you can create a concurrent program using this executable

• By using parameter button in the same window add one parameter for flat file path

·     After registering the concurrent to specific responsibility run program using SRS window and give the flat file path in file path parameter.

• After successful completion of concurrent program your data will be uploaded to the desired staging table.

• Then use the plsql block to insert data into the interface table.

DECLARE

  XTEMP                                       XX_TEMP_ITEM02%ROWTYPE;

  XTEMP01                                   XX_TEMP_CAT02%ROWTYPE;

  I_FLAG                                       VARCHAR2(3):= 'Y';--AFTER CHECK THIS VALUE DEFAULT SET OR NOT

  VAL_SEGMENT1                             VARCHAR2 (50);

  VAL_ORGANIZATION_ID              VARCHAR2(10);

  VAL_PROCESS_FLAG                     VARCHAR2(10);

  VAL_TRANSACTION_TYPE         VARCHAR2(10);

  CURSOR CUR_STAGE_TAB_ITEM

  IS

    SELECT * FROM XX_TEMP_ITEM02 ;

  CURSOR CUR_STAGE_TAB_CAT

  IS

    SELECT * FROM XX_TEMP_CAT02 ;

BEGIN

  FOR XTEMP IN CUR_STAGE_TAB_ITEM

  LOOP

    INSERT

    INTO MTL_SYSTEM_ITEMS_INTERFACE

      (

        PROCESS_FLAG,

        TRANSACTION_TYPE,

        SET_PROCESS_ID,

        ORGANIZATION_ID,

        ITEM_NUMBER,

        DESCRIPTION,

        PRIMARY_UOM_CODE,

        TEMPLATE_NAME,

        ATTRIBUTE_CATEGORY,

        ATTRIBUTE1,

        ATTRIBUTE2,

        ATTRIBUTE3,

        ATTRIBUTE15

      )

      VALUES

      (

        XTEMP.PROCESS_FLAG,

        XTEMP.TRANSACTION_TYPE,

        XTEMP.SET_PROCESS_ID,

        XTEMP.ORGANIZATION_ID,

        XTEMP.ITEM_NUMBER,

        XTEMP.DESCRIPTION,

        XTEMP.PRIMARY_UOM_CODE,

        XTEMP.TEMPLATE_NAME,

        XTEMP.ATTRIBUTE_CATEGORY,

        XTEMP.ATTRIBUTE1,

        XTEMP.ATTRIBUTE2,

        XTEMP.ATTRIBUTE3,

        XTEMP.ATTRIBUTE15

      );

  END LOOP;

  FOR XTEMP01 IN CUR_STAGE_TAB_CAT

  LOOP

    INSERT

    INTO MTL_ITEM_CATEGORIES_INTERFACE

      (

        PROCESS_FLAG,

        TRANSACTION_TYPE,

        SET_PROCESS_ID,

        ORGANIZATION_ID,

        ITEM_NUMBER,

        CATEGORY_SET_NAME,

        CATEGORY_NAME

      )

      VALUES

      (

        XTEMP01.PROCESS_FLAG,

        XTEMP01.TRANSACTION_TYPE,

        XTEMP01.SET_PROCESS_ID,

        XTEMP01.ORGANIZATION_ID,

        XTEMP01.ITEM_NUMBER,

        XTEMP01.CATEGORY_SET_NAME,

        XTEMP01.CATEGORY_NAME

      );

      commit;

  END LOOP;

  commit;

END;

• At the successful compilation of the PLSQL block data will move from staging tables to interface table.

• After this step you can run the ITEM IMPORT program from application.

Steps to import item from interface table 

• Log in to inventory management responsibility.

• Then use this path

• ITEM  -->  IMPORT  -->  IMPORT ITEMS

•            Click on ok then submit.
•              now you can see your item in Master item.