API to update AUTO_TAX_CALC_FLAG in Supplier Sites

SET serveroutput ON size 1000000;

DECLARE

   CURSOR CUR_SITES

   IS

        SELECT   asa.vendor_site_id, asa.org_id

          FROM   ap_suppliers aps, ap_supplier_sites_all asa

         WHERE   aps.VENDOR_ID = asa.VENDOR_ID

                 AND asa.vendor_site_id IN

                          (1427732, 107040, 648640, 1408728, 109163, 387011)

                 AND aps.END_DATE_ACTIVE IS NULL

                 AND asa.INACTIVE_DATE IS NULL

      ORDER BY   asa.auto_tax_calc_flag,

                 aps.VENDOR_NAME,

                 asa.vendor_site_code;

   L_VENDOR_SITE_ID        NUMBER;

   l_SITES_msg_count       NUMBER;

   l_SITES_msg_data        VARCHAR2 (4000);

   l_SITES_return_status   VARCHAR2 (10);

   l_vendor_site_rec       AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;

   l_resp_id               number;

BEGIN

   FOR SITES_REC IN CUR_SITES

   LOOP

        SELECT   frv.responsibility_ID

          INTO   l_resp_id

          FROM   apps.fnd_profile_options_vl fpo,

                 apps.fnd_responsibility_Vl frv,

                 apps.fnd_profile_option_values fpov,

                 apps.hr_organization_units hou

         WHERE       1 = 1

                 AND hou.ORGANIZATION_ID = SITES_REC.org_id

                 AND fpov.profile_option_value = TO_CHAR (hou.organization_id)

                 AND fpo.profile_option_id = fpov.profile_option_id

                 AND fpo.user_profile_option_name = 'MO: Operating Unit'

                 AND frv.responsibility_id = fpov.level_value

                 AND frv.APPLICATION_ID = 200

                 AND UPPER (frv.responsibility_name) LIKE 'AP%%SUPER%USER'

      ORDER BY   frv.responsibility_name;

      FND_GLOBAL.apps_initialize (0, l_resp_id, 200);

      l_vendor_site_id := SITES_REC.VENDOR_SITE_ID;

      l_vendor_site_rec.AUTO_TAX_CALC_FLAG := 'Y';

      AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE (

         p_api_version        => '1.0',

         p_init_msg_list      => FND_API.G_TRUE,

         p_commit             => FND_API.G_TRUE,

         p_validation_level   => FND_API.G_VALID_LEVEL_FULL,

         p_vendor_site_id     => l_vendor_site_id,

         p_vendor_site_rec    => l_vendor_site_rec,

         x_return_status      => l_SITES_return_status,

         x_msg_count          => l_SITES_msg_count,

         x_msg_data           => l_SITES_msg_data

      );

      IF l_SITES_return_status = 'S'

      THEN

         COMMIT;

         DBMS_OUTPUT.put_line(   'vendor Site ID : '

                              || l_vendor_site_id

                              || ' Status is : '

                              || l_SITES_return_status);

      ELSE

         ROLLBACK;

         DBMS_OUTPUT.put_line(   'vendor Site ID : '

                              || l_vendor_site_id

                              || ' Error Status is : '

                              || l_SITES_return_status);

      END IF;

      FOR I IN 1 .. l_SITES_msg_count

      LOOP

         l_SITES_msg_data :=

            SUBSTR (FND_MSG_PUB.GET (p_encoded => 'T'), 1, 255);

         DBMS_OUTPUT.put_line (l_SITES_msg_data);

      END LOOP;

      L_RESP_ID := NULL;

   END LOOP;

END;

API to Inactivate Supplier Sites which is not used for last two years

Table Script is used to capture the Vendor site and their  API updated return status

CREATE TABLE XX_TMP_VENDOR_SITES
(
   vendor_site_id      NUMBER,
   return_status       VARCHAR2 (10),
   msg_count           NUMBER,
   msg_data            VARCHAR2 (1000),
   LAST_UPDATE_DATE    DATE NOT NULL ENABLE,
   LAST_UPDATED_BY     NUMBER (15, 0) NOT NULL ENABLE,
   CREATION_DATE       DATE NOT NULL ENABLE,
   CREATED_BY          NUMBER (15, 0) NOT NULL ENABLE,
   LAST_UPDATE_LOGIN   NUMBER (15, 0)
)


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

set serveroutput on size 100000000;

DECLARE
   l_vendor_site_rec   AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
   l_vendor_rec        AP_VENDOR_PUB_PKG.r_vendor_rec_type;
   x_vendor_site_id    NUMBER;
   x_vendor_id         NUMBER;
   x_msg_data          VARCHAR2 (1000);
   x_msg_count         NUMBER;
   p_count             NUMBER;
   x_return_status     VARCHAR2 (10);
   V_MSG_INDEX_OUT     NUMBER;
   L_RESP_ID           NUMBER;
   L_CNT               NUMBER;
   Lf_CNT              NUMBER;
   P_DATE              VARCHAR2 (30) := '01-MAR-17';                 --:P_DATE

   CURSOR lcu_rec
   IS
        SELECT assa1.vendor_id,
               assa1.vendor_site_id,
               aps1.SEGMENT1,
               aps1.vendor_name,
               assa1.VENDOR_SITE_CODE,
               aps1.vendor_type_lookup_code,
               assa1.inactive_date,
               org_id
          FROM ap_suppliers aps1, ap_supplier_sites_all assa1
         WHERE     1 = 1
               --and aps1.segment1 = 216
               AND aps1.VENDOR_ID = assa1.VENDOR_ID
               AND aps1.END_DATE_ACTIVE IS NULL
               AND assa1.INACTIVE_DATE IS NULL
               AND aps1.vendor_type_lookup_code NOT IN
                        ('ACCEPTANCESLC',
                         'EL_DIVISION',
                         'EMPLOYEE',
                         'GOVT_DUES',
                         'GROUP_COMPANY',
                         'HUNDI',
                         'INDIA CUSTOMS AUTHORITY ',
                         'INDIA TAX AUTHORITY',
                         'INDIA TCS AUTHORITY',
                         'INDIA TDS AUTHORITY',
                         'INDIA EXCISE AUTHORITY',
                         'INSURANCE',
                         'SALARY',
                         'STATUTORY',
                         'SUBSCRIPTION')
               AND NOT EXISTS
                     (  SELECT RCT.VENDOR_ID, RCT.VENDOR_SITE_ID
                          FROM RCV_SHIPMENT_HEADERS RCT,
                               ap_suppliers aps,
                               ap_supplier_sites_all assa
                         WHERE              --RCT.TRANSACTION_TYPE = 'RECEIVE'
                               --AND TRUNC(RCT.TRANSACTION_DATE) >='01-APR-2017'
                               RCT.CREATION_DATE >=
                                  TO_DATE ('01-MAR-2017'              --P_DATE     --- Which is not used after 01-Mar-17   --Hard coded reason
                                                        || ' 00:00:00',
                                           'DD-MM-YYYY hh24:mi:ss')
                               --                                  TO_DATE (
                               --                                     TO_CHAR ('01-MAR-2017'--P_DATE
                               --                                     , 'DD-MON-YYYY')
                               --                                     || ' 00:00:00',
                               --                                     'dd-mm-yyyy hh24:mi:ss')
                               AND RCT.VENDOR_ID = ASSA.VENDOR_ID
                               AND RCT.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
                               AND aps.VENDOR_ID = assa.VENDOR_ID
                               --and aps.segment1 = 216
                               AND aps.vendor_id = aps1.vendor_id
                               AND ASSA.VENDOR_SITE_ID = ASSA1.VENDOR_SITE_ID
                      GROUP BY RCT.VENDOR_ID, RCT.VENDOR_SITE_ID)
      ORDER BY 1, 2;
BEGIN
   L_CNT := 0;
   Lf_CNT := 0;

   FOR i IN lcu_rec
   LOOP
 
   /*---------------------------------------------------------------------------------------
   MO Profile option was enabled. So Responsibility Id has been hard coded using this below query
 
    SELECT frv.responsibility_ID,
         frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_responsibility_Vl frv,
         apps.fnd_profile_option_values fpov,
         apps.hr_organization_units hou
   WHERE     1 = 1      --AND hou.ORGANIZATION_ID =84
         AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
         AND fpo.profile_option_id = fpov.profile_option_id
         AND fpo.user_profile_option_name = 'MO: Operating Unit'
         AND frv.responsibility_id = fpov.level_value
         AND frv.APPLICATION_ID = 200
         AND UPPER (frv.responsibility_name) LIKE '%PAYABLES%SUPER%USER'
ORDER BY frv.responsibility_name
 
 
   ----------------------------------------------------------------------------------------*/
 
 
      IF (i.ORG_ID = 83)
      THEN
         L_RESP_ID := 50653;
      ELSIF (i.ORG_ID = 84)
      THEN
         L_RESP_ID := 50654;
      ELSIF (i.ORG_ID = 86)
      THEN
         L_RESP_ID := 50656;
      ELSIF (i.ORG_ID = 85)
      THEN
         L_RESP_ID := 50655;
      ELSIF (i.ORG_ID = 82)
      THEN
         L_RESP_ID := 50657;
      ELSE
         L_RESP_ID := 55460;
      END IF;

      --      DBMS_OUTPUT.put_line ('L_RESP_ID = ' || L_RESP_ID);

      fnd_global.apps_initialize (12590, L_RESP_ID, 200);

      l_vendor_site_rec.vendor_id := i.vendor_id;               -- Supplier Id
      l_vendor_site_rec.org_id := i.org_id;               -- Operating Unit id
      x_vendor_site_id := i.vendor_site_id;           -- Site Id to be updated
      l_vendor_site_rec.last_update_date := SYSDATE;
      l_vendor_site_rec.last_updated_by := 12590;
      l_vendor_site_rec.inactive_date := SYSDATE;

      AP_VENDOR_PUB_PKG.Update_Vendor_Site (
         p_api_version       => 1,
         x_return_status     => x_return_status,
         x_msg_count         => x_msg_count,
         x_msg_data          => x_msg_data,
         p_vendor_site_rec   => l_vendor_site_rec,
         p_vendor_site_id    => x_vendor_site_id);
      COMMIT;

      --      DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
      --      DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
      --      DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

      INSERT INTO XX_TMP_VENDOR_SITES
          VALUES (i.vendor_site_id,
                  x_return_status,
                  x_msg_count,
                  x_msg_data,
                  SYSDATE,
                  12590,
                  SYSDATE,
                  12590,
                  -1);

      COMMIT;

      IF (x_return_status = 'S')
      THEN
         L_CNT := L_CNT + 1;
      ELSE
         Lf_CNT := Lf_CNT + 1;
      --         DBMS_OUTPUT.put_line ('vendor_site_id = ' || i.vendor_site_id);
      END IF;


      L_RESP_ID := NULL;
   END LOOP;
END;
/

show error;

Script to find JDR_UTILS for the OAF page from Back end

--------------------------------------------JDR_UTILS----------------------------------

/******************* JDR_UTILS.LISTDOCUMENTS *******************/       

Use this API to list of all OA Framework documents in the given path/module.
It provides list of all the pages/extensions/personalizations.

Parameters:
1. Full/Partial path of MDS repository
2. TRUE will direct the API to list all Child Documents underneath that tree path

Example:

BEGIN
   jdr_utils.listdocuments ('/oracle/apps/eam/workorder/', TRUE);
END;
/

/******************* JDR_UTILS.LISTCUSTOMIZATIONS  *******************/   
Use this API to list all personalizations/Extensions/COntents of a specific Object.

Example:

BEGIN
   jdr_utils.listcustomizations (
      p_document => '/xxprod/oracle/apps/eam/workorder/webui/CreateUpdateWOPG');
END;
/

 /*******************   JDR_UTILS.PRINTDOCUMENT    *******************/   

This procedure lists the contents of a specific object.
For example, using below code, we can get all the components(items and page properties) of the page -> TrackPG

BEGIN
   jdr_utils.printDocument (
      '/xxprod/oracle/apps/eam/workorder/webui/CreateUpdateWOPG');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

/******************* JDR_UTILS.DELETEDOCUMENT   *******************/

You can delete a page/personalization/extension by using command below.
For example to delete the Application Module substitution, use command

BEGIN
   jdr_utils.deletedocument (
      p_document => '/oracle/apps/eam/workorder/server/WOCreateUpdateAM');
END;
/

SQL to Identify/Remove Locked Objects in Oracle

The Following SQL Script is used to finds the locked Objects in Oracle.

---------------------- SQL to find SID and Serial# number ---------------
-----------------------------------------------------------------------------------
SELECT C.OWNER,
       C.OBJECT_NAME,
       C.OBJECT_TYPE,
       B.SID,
       B.SERIAL#,
       B.STATUS,
       B.OSUSER,
       B.MACHINE
  FROM V$LOCKED_OBJECT A, V$SESSION B, DBA_OBJECTS C
 WHERE B.SID = A.SESSION_ID AND A.OBJECT_ID = C.OBJECT_ID;

-----------------------------------------------------------------------------------

Once we have identified the locked objects, the below script will help us to removes the lock of the objects. Here we need to provide the SID and SERIAL# values from the above query.

---------------------------------- SQL to Kill session ------------------------
-----------------------------------------------------------------------------------
   ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

-----------------------------------------------------------------------------------

OAF: SQL Query to get all the Personalization on the OAF Pages in Oracle

Below Sql script is used to get all the Personliazations on the OAF Page in Oracle.

--------------------------------------------------------------------------------------------------------

SELECT PATH.path_docid perz_doc_id,

         jdr_mds_internal.getdocumentname (PATH.path_docid) perz_doc_path

    FROM jdr_paths PATH

   WHERE PATH.path_docid IN

               (SELECT DISTINCT comp_docid

                  FROM jdr_components

                 WHERE     comp_seq = 0

                       AND comp_element = 'customization'

                       AND comp_id IS NULL)

         AND UPPER (jdr_mds_internal.getdocumentname (PATH.path_docid)) LIKE

               UPPER ('%CustomPageNamePG%')

ORDER BY perz_doc_path;

--------------------------------------------------------------------------------------------------------

SQL to add System Administrator Responsibility to user from Back-end in Oracle

Below Sql script is used to add System Administrator Responsibility to user from back end in Oracle.

SET SERVEROUTPUT ON SIZE 10000000;

BEGIN
   fnd_user_pkg.addresp ('USER_NAME',
                         'SYSADMIN',
                         'SYSTEM_ADMINISTRATOR',
                         'STANDARD',
                         'Add Responsibility to USER using pl/sql',
                         SYSDATE,
                         SYSDATE + 100);
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   ' Responsibility is not added due to '
                           || SQLCODE
                           || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
END;

Query to List All User Roles in EBS as it shows in Application

The following query list all the Roles and responsibilities as they appears in the Application security screen

SELECT DISTINCT UR.USER_NAME, WR.DISPLAY_NAME, WR.description
  FROM APPLSYS.wf_user_role_assignments ur, apps.WF_ROLES wr
 WHERE     1 = 1
       AND ROLE_ORIG_SYSTEM IN ('FND_RESP', 'UMX')
       AND wr.name = ur.role_name
       AND ur.user_name LIKE '<USER_NAME>%';

PL/Sql script to add System Administrator responsibility

PL/Sql script to add System Administrator responsibility
--------------------------------------------------------------------------------------------------

BEGIN
   fnd_user_pkg.addresp (username         => UPPER ('ramprasath'),
                         resp_app         => 'SYSADMIN',
                         resp_key         => 'SYSTEM_ADMINISTRATOR',
                         security_group   => 'STANDARD',
                         description      => 'DESCRIPTION',
                         start_date       => SYSDATE,
                         end_date         => NULL);
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'Responsibility is not added due to'
                           || SQLCODE
                           || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
END;
/

Oracle Forms :- Steps to get record counts in Data block without saving the records

Steps to get record counts in Data block without saving the records in Oracle Forms :- 

While developing the Custom Oracle Forms, We will populate the records into the line level data blocks with using of Fetch Cursor. If we need to count the fetched records in the data block without saving the records, we will count the records in two different methods 

1. Query Hits - Oracle Forms Block Property. 

2. Manual Method using Go_block function.

In this below Screen shot shows the Line level fetched data in the Custom form in Oracle.

1. Query Hits - Oracle Forms Block Property. 

TOT_REC_COUNT := GET_BLOCK_PROPERTY('BLOCK_NAME', QUERY_HITS);

2. Manual Method using Go_block function.

GO_BLOCK('BLOCK_NAME');

First_record;

last_record;

TOT_REC_COUNT := GET_BLOCK_PROPERTY('BLOCK_NAME',current_record);