Requisition Cancel API Script : Oracle EBS R12

Oracle has provided a standard API to cancel Requisition and Requisition Lines.

Below mentioned script is the custom pl/sql procedure which we can use to cancel Requisition and Requisition Lines.

PROCEDURE REQUISITION_CANCEL(
    P_REQ_NUM IN VARCHAR2,
    P_STATUS_MSG OUT VARCHAR2)
AS
  v_return_status VARCHAR2 (1000);
  v_msg_count     NUMBER;
  v_msg_data      VARCHAR2 (1000);
  v_header_id     NUMBER;
  v_line_id       NUMBER;
  v_req_line_id req_line_type;
  v_msg       NUMBER := NULL;
  v_msg_dummy VARCHAR2 (2000);
  v_output    VARCHAR2 (2000);
BEGIN
------Fetching Requisition Header id for corresponding Requisition Number-----
  BEGIN
    SELECT requisition_header_id
    into v_header_id
    FROM APPS.PO_REQUISITION_HEADERS_ALL
    WHERE segment1=p_req_num;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    P_STATUS_MSG:='ERROR(ND):Requisition not found for Requisition Number :'||P_REQ_NUM;
  WHEN OTHERS THEN
    P_STATUS_MSG:='ERROR(OTHERS) :'||SQLERRM;
  END;

  ------Bulk Collect all the Requisition Lines-------------

 SELECT requisition_line_id bulk collect
  INTO v_req_line_id
  FROM APPS.PO_REQUISITION_LINES_ALL
  WHERE requisition_header_id=v_header_id;

  --------------------------------------------------------
  FOR i IN v_req_line_id.first ..v_req_line_id.last
  LOOP
    BEGIN
      v_msg:=1;
      APPS.PO_REQ_DOCUMENT_CANCEL_GRP.CANCEL_REQUISITION (P_API_VERSION => 1.0,
                                                          P_REQ_HEADER_ID => APPS.PO_TBL_NUMBER(V_HEADER_ID),
                                                          p_req_line_id => apps.po_tbl_number(v_req_line_id(i)),
                                                          p_cancel_date => SYSDATE,
                                                          p_cancel_reason => 'Cancelled Requisition',
                                                          P_SOURCE => 'REQUISITION',
                                                          x_return_status => v_return_status,
                                                          X_MSG_COUNT => V_MSG_COUNT,
                                                          x_msg_data => v_msg_data );

      IF v_return_status <> 'S' THEN
        apps.fnd_msg_pub.get (v_msg, apps.fnd_api.g_false, v_msg_data, v_msg_dummy);
        v_output := (TO_CHAR (v_msg) || ': ' || v_msg_data);
        DBMS_OUTPUT.put_line (v_output);
        P_STATUS_MSG:=v_output;
      ELSE
        P_STATUS_MSG:=v_return_status;
      END IF;

    END;
  END LOOP;
END;

Anonymous Block to test the procedure:-

Let us know your valuable feedback.

Create Quataion,Requisition and Purchase Orders with mannual Alphanumeric values in EBS R12

By default the requisition number, Purchase Order number and quatation number is generated from a numeric sequence value in Oracle E-Business suite R12.

But in the setups user has given an option to override it and create the PO, Requisition and Quatations with manual numbers or alphanumeric values. Here is the setup mentioned below to do the same.

• Goto Purchasing, Vision Operations (USA) > Setups > Organizations > Purchasing options.

• on the Document numbering section we can change the Document, Entry, Type according to our requirement. The possible values are as mentioned below snapshot.

Happy Coding :)

Using API Load Currency In Oracle EBS R12

Below is the sample procedure to create Currency in Oracle EBS R12 using .

PROCEDURE currency_create(p_currency_code IN VARCHAR2,
                       p_teritory_code IN VARCHAR2,
                       p_precision IN NUMBER,
                       p_extended_precision IN NUMBER,
                       p_symbol IN VARCHAR2,
                       p_currency_name IN VARCHAR2
                       )
 BEGIN
     APPS.MO_GLOBAL.INIT ('PO');
     APPS.MO_GLOBAL.SET_POLICY_CONTEXT ('S', 204);
     APPS.FND_GLOBAL.APPS_INITIALIZE ( USER_ID => 1318, RESP_ID => 50578, RESP_APPL_ID => 201 );

apps.fnd_currencies_pkg.LOAD_ROW ( p_currency_code,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  'Y',
                                  'Y',
                                  p_teritory_code,
                                  p_precision,
                                  p_extended_precision,
                                  p_symbol,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  'Y',
                                  NULL,
                                  p_currency_name,
                                  NULL,
                                  'apps') ;
 commit;
 END currency_create;


Happy Coding. :)

Creating Custom TOP in Oracle EBS R12

This blog demonstrates creation of custom top in EBS R12. Before creating any custom top user    should be aware for certain values such as EBS Database SID, EBS Host name.
For now we are taking some dummy values such as:-

DB SID => EBS_DB_SID
EBS Host name => EBS_HOST_NAME

Note: All the red colored below script command part have to be modified as per your instance.

Step1: Log in to EBS server using putty(any Linux machine) as 'applmgr' user.

Step2: Source the EBS Apps environment variable by moving to the APPL_TOP directory as shown below.

Step3: Create directory structure for your custom application files

                   mkdir $APPL_TOP/xxcll
                   mkdir $APPL_TOP/xxcll/12.0.0
                   mkdir $APPL_TOP/xxcll/12.0.0/admin

                   mkdir $APPL_TOP/xxcll/12.0.0/admin/sql

                   mkdir $APPL_TOP/xxcll/12.0.0/admin/odf
                   mkdir $APPL_TOP/xxcll/12.0.0/sql
                   mkdir $APPL_TOP/xxcll/12.0.0/bin
                   mkdir $APPL_TOP/xxcll/12.0.0/reports
                   mkdir $APPL_TOP/xxcll/12.0.0/reports/US
                   mkdir $APPL_TOP/xxcll/12.0.0/forms
                   mkdir $APPL_TOP/xxcll/12.0.0/forms/US
                   mkdir $APPL_TOP/xxcll/12.0.0/lib
                   mkdir $APPL_TOP/xxcll/12.0.0/out
                   mkdir $APPL_TOP/xxcll/12.0.0/log

Step4: For adding custom module to the environment execute below command after modifying it accordingly.

                  cat <<EOF >/$APPL_TOP/customEBS_DB_SID'_'EBS_HOST_NAME.env

                  XXCLL_TOP="u01/applmgr/EBS_DB_SID/apps/apps_st/appl/xxcll/12.0.0"
                  export XXCLL_TOP

                  EOF

You can verify the successfull creation of this environment file by moving to APPL_TOP. And view your custom environment file created in above step.
Threre should be a customfile entry in it, which looks like as shown below.


                customfile=/u01/applmgr/EBS_DB_SID/apps/apps_st/appl/customEBS_DB_SID'_'EBS_HOST_NAME.env


Step5: Next step is to modify the context file, entering the path of our custom top. To know the location of context file execute :-

                echo $CONTEXT_FILE

This will give you the location of context file in your EBS instance.
Now move to the context file location and take the backup of the file. Tou can also take the bakup of contextfile by executing:-

                mv $CONTEXT_FILE $CONTEXT_FILE.bkup

Now edit your context file foe adding the details of your custom top. Its houd look like as shown below:

 <XXCLL_TOP oa_var="s_xxclltop" oa_type="PROD_TOP" oa_enabled="FALSE">/u01/applmgr/EBS_DB_SID/apps/apps_st/appl/xxcll/12.0.0</XXCLL_TOP>

After editing save the file.

Step6: In order to make the changes made in contextfile effective we need to run autoconfig file.
Follow the below steps sequentially to run autoconfig file:-

• Move to ADMIN_SCRIPTS_HOME by executing :- 

            cd $ADMIN_SCRIPTS_HOME 

• Stop all the services for the application by executing :- 

           ./adstpall.sh apps/apps 

          If you get any status other than 0 then you have to kill the services manually.

• In same directory you will find adautocfg.sh script which we have to run for autoconfig by executing:-

           sh adautocfg.sh

         It will prompt for apps password.
         It will now configure automatically to application tier. 

• Wait till it shows "Autoconfig Completed successfully"   

• Start all the services by executing:-

         ./adstrtal.sh apps/apps

Step7: To verify that custom top creation is successfull, move to APPL_TOP by executing :-

            cd $APPL_TOP

            And execute :- cd $XXCLL_TOP


EBS setups for Custom TOP:-

Step1: Register Custom Application

Navigation:- System Administrator > Application > Registe



Enter the specific details for Application,Short Name,Basepath, Description and save the record.

                  Application:    XXCLL Custom Application
                  Short Name:    XXCLL
                  Basepath:        XXCLL_TOP
                  Description:    XXCLL Custom Application

 


Step2: Register Oracle User

Navigation:- System Administrator > Security> ORACLE> Register

 

 Enter the specific values and save the record.

                    Database User Name:    XXCLL (Provide Data Base User Name)
                    Password:                      XXCLL (Provide Username Password)
                    Privilege:                       Enabled
                    Install Group:                0
                    Description:                  XXCLL Custom Application User

 


Step3: Creating Custom Data Group 

Navigation:- System Administrator > Security > ORACLE > DataGroup

 

Enter the Data Group Name and Description and Click on "Copy Application From" as shown in below snapshot. A pop up window will open. Select 'Standard' and click on 'ok'.
 




Click on 'New' button at the header of form and add your custom application created above and oracle id as 'APPS' as shown below and save the record.









Please give your feedback on this blog and post.

Happy Coding