Oracle R12 EB-Tax SQL Query

Following is a link for all EB-Tax related tables:

http://oracleappscommunity.com/oracle/blog/1181/e-business-tax-tables


I found another useful query at Oracle Apps Knowledge Sharing website, which I have updated for my own requirement.

http://www.shareoracleapps.com/2013/04/e-business-tax-query-taxrate-based-on-operating-unit-r12-oracleapps.html

SELECT hou.organization_id            org_id,

       led.name                       ledger,

       hou.name                       operating_unit,

       --

       zxr.tax_regime_code            tax_regime_code,

       zxr.tax                        tax_code,

       zxr.tax_status_code            tax_status_code,

       zxr.tax_rate_code              tax_rate_code,

       zxr.tax_jurisdiction_code      tax_jurisdiction_code,

       --

       zxr.rate_type_code             rate_type_code,

       zxr.percentage_rate            percentage_rate,

       zxr.effective_from             rate_effective_from,

       zxr.effective_to               rate_effective_to,

       --

       acc.tax_account_ccid           tax_account_ccid,

       gcc.concatenated_segments      tax_account

  FROM

       zx_rates_vl                  zxr,

       zx_accounts                  acc,

       hr_operating_units           hou,

       gl_ledgers                   led,

       xxrl_gl_code_combinations_v  gcc

 WHERE

       1=1

   --

   -- AND zxr.tax_regime_code = 'UK VAT'

   -- AND zxr.tax_rate_code = 'UK_AR_DOM'

   --

   AND acc.tax_account_entity_code = 'RATES'

   AND zxr.active_flag = 'Y'

   AND TRUNC (SYSDATE) BETWEEN

          TRUNC (zxr.effective_from) AND

          NVL (TRUNC (zxr.effective_to), TRUNC (SYSDATE) + 1)

   --

   AND led.ledger_id = hou.set_of_books_id

   AND gcc.code_combination_id = acc.tax_account_ccid

   AND hou.organization_id = acc.internal_organization_id

   AND acc.tax_account_entity_id = zxr.tax_rate_id

   --

   ;

  

Concurrent Programs Warning-Error: ERRBUFF and RETCODE

The ERRBUFF can be returned with any message.

The RETCODE can be returned with one of three values:

   0  -- Success
   1  -- Warning
   2  -- Error

Below is an example of a package where I can pass an employee number and it can return its full name. If no data found for the employee number passed, the concurrent program will turn YELLOW with the message 'No Employee Found'. If there is any other error occurs, it will turn RED with SQLERRM message.

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

-- Package Specification

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

CREATE OR REPLACE PACKAGE apps.emp_test_pkg

IS

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2;

    

END emp_test_pkg;

/

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

-- Package Body

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

CREATE OR REPLACE PACKAGE BODY apps.emp_test_pkg

IS

 

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2

   IS

      lv_emp_name   VARCHAR2(300)  DEFAULT  NULL;

    

   BEGIN

       

      SELECT (papf.first_name || ' ' || papf.last_name)

        INTO lv_emp_name

        FROM per_all_people_f  papf

       WHERE 1=1

         AND papf.employee_number = p_emp_number;

    

      RETURN (lv_emp_name);

    

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         errbuff := 'No employee found for ' || p_emp_number;

         retcode := '1';        -- warning

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

       

      WHEN OTHERS THEN

       

         errbuff := SQLERRM;

         retcode := '2';        -- error

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

 

   END emp_name;

 

END emp_test_pkg;

/

Oracle HR related API List

Recently I had to work on lot of HR related interfaces, which use Oracle APIs. While I was working on the interfaces, I came across with the following website by Puneet Rajkumar, which I thought a very good resource for all HRMS related APIs. He provided some easy good examples.

https://blogs.oracle.com/prajkumar/entry/oracle_hrms_apis


You can also refer to Oracle article for more information:
Note: 179243.1 - Application Programmatic Interfaces (APIs) in Oracle HRMS


Or, you can run the following query:

SELECT SUBSTR (ds.owner, 1, 20)   owner,

       SUBSTR (ds.name, 1, 30)    object_name,

       SUBSTR (ds.type, 1, 20)    object_type,

       SUBSTR (do.status, 1, 10)  status,

       do.last_ddl_time           last_ddl

  FROM dba_source  ds,

       dba_objects do

 WHERE 1 = 1

   --

   AND ds.name = do.object_name

   AND ds.type = do.object_type

   --

   AND ds.text LIKE '%Header%'

   AND ds.type = 'PACKAGE BODY'

   AND ds.name LIKE 'HR_%API%'

   --

 ORDER BY ds.owner, ds.name;

Active Responsibility List with Active User Count

If you need to create an ad-hoc report showing all the currently active Oracle responsibilities with all active users that are using them, below is a simple query that can help you.

SELECT

       fat.application_name         "Application Name",

       frv.responsibility_name      "Active Responsibility Name",

       COUNT(fu.user_name)          "Active User Count"

  FROM

       fnd_user                     fu,

       fnd_user_resp_groups_direct  furgd,

       fnd_responsibility_vl        frv,

       fnd_application_tl           fat

 WHERE

       1=1

   --

   AND furgd.end_date IS NULL

   --

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(furgd.start_date) AND TRUNC(NVL(furgd.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(frv.start_date)   AND TRUNC(NVL(frv.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(fu.start_date)    AND TRUNC(NVL(fu.end_date, SYSDATE+1))

   --

   AND fat.application_id       =  frv.application_id

   --

   AND furgd.responsibility_id  =  frv.responsibility_id

   AND furgd.user_id            =  fu.user_id

   --

 GROUP BY fat.application_name, frv.responsibility_name

 ORDER BY fat.application_name, frv.responsibility_name;

Delete Concurrent Program from the Back-End

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

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

-- delete concurrent program definition and executable from back-end

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

-- syntax:

--     delete_program    (program_short_name, application_short_name)

--     delete_executable (program_short_name, application_short_name)

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

DECLARE

  lv_prog_short_name    VARCHAR2(240);

  lv_appl_short_name    VARCHAR2(240);

BEGIN

   -- set the variables first

   lv_prog_short_name := 'XX_TEST';     -- concurrent program short name

   lv_appl_short_name := 'XX';          -- application short name

  

   -- see if the program exists. if found, delete the program

   IF fnd_program.program_exists    (lv_prog_short_name, lv_appl_short_name) AND

      fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)    

   THEN

     

      fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);

      fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);

     

      COMMIT;

  

      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');

  

   -- if the program does not exist in the system

   ELSE

      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');

   END IF;

  

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);

  

END;