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;