API to update Item Status

Oracle Applications

API to update Item Status

- A beginners guide :- API to update Item Status.

- Responsibility :- Inventory Manager.

- EGO_ITEM_PUB.Process_Items

Sample Code: 

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

DECLARE
  l_item_table       EGO_Item_PUB.Item_Tbl_Type;
  x_item_table      EGO_Item_PUB.Item_Tbl_Type;
  x_return_status  VARCHAR2(1);
  x_msg_count     NUMBER(10);
  x_msg_data       VARCHAR2(1000);
  x_message_list   Error_Handler.Error_Tbl_Type;

BEGIN

--Apps Initialize
fnd_global.apps_initialize

                            (user_id      => 57260,      --&userid,                             

                             resp_id      => 57122,      --&resp_id
                             resp_appl_id => 20063); --&resp_appl_id

  -- Item definition
  l_item_table(1).Transaction_Type := 'UPDATE';
  l_item_table(1).Segment1 := 'RCR24201 REV-7';
  l_item_table(1).Organization_id := 2990;
  l_item_table(1).attribute_category := 2990;

-- Calling procedure EGO_ITEM_PUB.Process_Items
  EGO_ITEM_PUB.Process_Items(

--Input Parameters

                             p_api_version   => 1.0,
                             p_init_msg_list => FND_API.g_TRUE,
                             p_commit        => FND_API.g_TRUE,
                             p_Item_Tbl      => l_item_table,

--Output Parameters
                             x_Item_Tbl      => x_item_table,
                             x_return_status => x_return_status,
                             x_msg_count     => x_msg_count);

  DBMS_OUTPUT.PUT_LINE('Items updated Status ==>' || x_return_status);

  IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
    FOR i IN 1 .. x_item_table.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Inventory Item Id :' ||
                           to_char(x_item_table(i).Inventory_Item_Id));
      DBMS_OUTPUT.PUT_LINE('Organization Id   :' ||
                           to_char(x_item_table(i).Organization_Id));
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error Messages :');
    Error_Handler.GET_MESSAGE_LIST(x_message_list => x_message_list);
    FOR i IN 1 .. x_message_list.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(x_message_list(i).message_text);
    END LOOP;
  END IF;

 END;

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

Sales Order Types

Oracle Applications

Drop Shipment Orders & Back To Back Sales Order

(Beginners Guides)

Drop Shipment Order  

-In the Drop shipment order types the customer receives the product directly from the manufacturer. 
-No need to store items in a warehouse and also expenses of hiring employees to ship the product are nullified.
- This method is commonly used by smaller retail shops and internet-only stores. 
- The source type in line information should be External.

Back to Back Sales Order

-Scenario: Often customers order products that you do not typically stock and also do not manufacture. You may want to purchase that item specifically for this order, have the supplier ship it to you, and then combine it with other items you may have purchased or stocked to create one shipment to the customer.

-This type of orders commonly used by Wholesale Distributors who use the Sell-Source-Ship (S3) business model.
-Also called as Procure-To-Order.

API to update customer in Oracle Applications

Oracle Applications

-Simple and easy steps to oracle API for customer

-A beginners guide for oracle customer updation using API

DECLARE        

   p_customer_profile_rec_type   hz_customer_profile_v2pub.customer_profile_rec_type;

   p_cust_account_profile_id              NUMBER;

   p_object_version_number               NUMBER;

   x_return_status                             VARCHAR2 (2000);

   x_msg_count                                NUMBER;

   X_Msg_Data                                Varchar2 (2000);

   g_org_id                                      NUMBER :=Fnd_Profile.Value('ORG_ID');

   G_User_Name                           Varchar2(200):=Fnd_Profile.Value('USERNAME');

   g_resp_name                              varchar2(200):=fnd_profile.value('RESP_NAME');

CURSOR c_data_rtvl --------+=data retrival cursor

Is

  Select Hcp.Object_Version_Number Obj_Ver_No,

    Hcp.Cust_Account_Profile_Id Account_Profile

  FROM Hz_Customer_Profiles Hcp,

    Hz_Parties Hp,

    Hz_Cust_Accounts Hca

  WHERE Hcp.Attribute5   IS NULL

  AND Hca.Party_Id        = Hp.Party_Id

  AND Hca.Cust_Account_Id = Hcp.Cust_Account_Id

  And Hcp.Attribute_Category Like 'ALL_CAT'

  And Hca.Account_Number Like 'C0001%';

CURSOR c_cntx_vlu --------+=context value cursor

Is

  Select Afu.User_Id User_Id,

    Aft.Application_Id Application_Id,

    Aft.Responsibility_Id resp_id

  FROM Applsys.Fnd_User Afu,

    applsys.fnd_responsibility_tl aft

  WHERE Afu.User_Name LIKE g_user_name

  AND aft.responsibility_name LIKE g_resp_name;

Cdr_Rec C_Data_Rtvl%Rowtype;

ccv_rec c_cntx_vlu%ROWTYPE;

BEGIN

-- Setting the Context --

  Open C_Cntx_Vlu;

  Fetch C_Cntx_Vlu Into ccv_rec;

  

   Mo_Global.Init ('AR');

   Fnd_Global.Apps_Initialize (User_Id           => Ccv_Rec.User_Id,

                               Resp_Id           => Ccv_Rec.Application_Id,

                               resp_appl_id      => ccv_rec.resp_id

                              );

   mo_global.set_policy_context ('S', g_org_id);

   fnd_global.set_nls_context ('AMERICAN');

                                                                          -- Initializing the Mandatory API parameters

  For Cdr_Rec In  C_Data_Rtvl

   Loop

   p_customer_profile_rec_type.cust_account_profile_id := Cdr_Rec.Account_Profile; --first value

   P_Customer_Profile_Rec_Type.Credit_Rating :=            Null;

   P_Customer_Profile_Rec_Type.Attribute5 :=                 'S';

   p_object_version_number :=                                         Cdr_Rec.Obj_Ver_No;          --always change this also

   DBMS_OUTPUT.put_line

          ('Calling the API hz_customer_profile_v2pub.update_customer_profile');

   hz_customer_profile_v2pub.update_customer_profile

                (p_init_msg_list                         => fnd_api.g_true,

                 p_customer_profile_rec             => p_customer_profile_rec_type,

                 p_object_version_number          => p_object_version_number,

                 x_return_status                        => x_return_status,

                 x_msg_count                           => x_msg_count,

                 x_msg_data                             => x_msg_data

                );

   IF x_return_status = fnd_api.g_ret_sts_success

   Then

      --COMMIT;

      DBMS_OUTPUT.put_line ('Updation of Customer Profile is Successful ');

      DBMS_OUTPUT.put_line ('Output information ....');

      DBMS_OUTPUT.put_line (   'Object Version Number = '

                            || TO_CHAR (p_object_version_number)

                           );

      DBMS_OUTPUT.put_line (   'CDE STMT DIST = '

                            || p_customer_profile_rec_type.attribute5

                           );

   ELSE

      DBMS_OUTPUT.put_line (   'Updation of Customer Profile got failed:'

                            || x_msg_data

                           );

      ROLLBACK;

      FOR i IN 1 .. x_msg_count

      LOOP

         x_msg_data := fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F');

         DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);

      END LOOP;

   End If;

   EXIT WHEN c_data_rtvl%NOTFOUND;-----------look up

end loop;

   DBMS_OUTPUT.put_line ('Completion of API');

END;

/

Set Serveroutput On;

/

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.