IFRS,International Financial Reporting Standards (IFRS),

                                                    What is  IFRS 

Sometime back, I was talking to one of my accountant friends who pointed me some recent changes in IFRS , and provided some link for awareness and to understand the impact in EBS suite. I am not going to put the details, rather will walk through some information that is good to have with ERP consultant who is dealing with Financial products.

What is IFRS?

IFRS, International Financial Reporting Standards (IFRS), together with International Accounting Standards (IAS), are a "principles-based " set of standards that establish broad rules rather than dictating specific accounting treatments. From 1973 to 2001, IAS was issued by the International Accounting Standards Committee (IASC). In April 2001 the International Accounting Standards Board (IASB) adopted all IAS and began developing new standards called IFRS.

IFRS are used in many parts of the world, including the European Union, Hong Kong, Australia, Russia, South Africa, Singapore and Pakistan. Nearly 100 countries currently require or permit the use of, or have a policy of convergence with, IFRSs. Here is list of countries that adopted IFRS.

What is meant IFRS for Oracle eBusiness consultant/ERP Consultant?

As we are quite aware GAAP which is so-called Generally Accepted Accounting Principles for Shareholder Reporting.The term is not drafted by a legislature.

and ,in term of Accounting Principles this can be best understood as:

IFRS = Financial Reporting standards = Balance Definitions < /FONT >

which internally means more in terms of "disclosure Requirements" and "Balance Definitions " for the finance controller.

IFRS - What it is for?

The Other things IFRS would do is:

• Providing bookkeeping rules rather many of us have the impression that they have some rule set for accounting
• It will do Shareholder Reporting, and this is important when :
  • A group of companies owned by a Public company
  • Not the individual companies
• It will also do external reporting
  • To your owners (investors, shareholders)
  • Through their stock markets
• Management Reporting
  • Maximize investors returns, measured only with IFRS
  • Segment Reporting disclosure

IFRS - What it is not for?

And more important it should be understood that IFRS is not meant for:

• Statutory Reporting
• Tax Reporting
• Regulatory reporting
• Subsidiary reporting

IFRS – How new is it?

• IASB has been around since 1980’s
• IAS/IFRS pretty much converged with the Americans
• If your ERP supports US GAAP, it supports IAS/IFRS
• Key premises (recognition, measurement):
  • Mark to Market when possible
  • The reality of the Balance Sheet – real assets, real liabilities
  • Income Statement analyses the change in wealth
  • Deep Disclosure: owners right to know

US GAAP vs. IFRS - A difference

While looking to some more information on internet ,I discovered a nice article from the site of Fulcrum Inquiry who pointed out the key differences(adopted) between the two standards in there one of the article .

Don’t worry about your ERP Side

• Maybe requires some reconfiguration, maybe requires some data analysis
• We have noticed there is some big change in R12 AR to meet revenue recognition, R12 is partially on track for IFRS fitness in the suite.
• The new R12 Global Architecture introduced the concept of ledgers which has the capability of providing differing accounting representations; one ledger for example could conform to IFRS, another to local GAAP.
• From the Currency area, we have already noticed some big changes like:
  • Balance level Translation or Remeasurement in Ledgers – within Ledger sets, run at one click
  • Balance & Activity level Translation or Remeasurement in Financial Consolidation Hub
  • Another is enhanced Revaluation [Functional Currency term dropped: conflicts with IAS 21 / FAS 52]

Oracle Purchasing Approval Hierarchies

 

Purchasing setup of approval hierarchies: There are two most commonly known methods to route documents for approval.

1. Approval Hierarchies (uses position hierarchies)
2. Employee/Supervisor Relationships (use employee/supervisor relationship)

* Third method of Advanced Approval Support for Requisitions (Release 12 uses integration with Oracle Approvals Management) . However, in this article, the focus here is set on the above two most commonly used methods.

1. Position Hierarchies: Position Hierarchies are hierarchies that have a position relationship. Purchasing utilizes positions as a roadmap to determine how and where documents will be routed once the approval process has been initiated. It is first necessary to have created all positions that are going to be used in the system.

Once all positions have been created, it is necessary to build the position hierarchy. Each position has approval limits, so when a purchase order exceeds the limits of the position, the purchase order is forwarded onto the next position in the Hierarchy. The hierarchy for positions is defined in the Position Hierarchy form.

When this is complete or is changed, the Fill Employee Hierarchy concurrent program must be run for the new hierarchy to come into effect. You must set up Positions if you plan to use either security or approval hierarchies. If you are using Shared HR navigate, Purchasing: Setup: Personnel: Position Hierarchy. Otherwise, if you are using a full install of HR then navigate Human Resources: Work Structures: Position: Hierarchy.

2. Employee/Supervisor Relationships: This type of hierarchy does not use the Approval Hierarchy form, but is defined by the employee/supervisor relationship. The supervisor of an employee is defined on the Assignment region of the Employee form. If the purchase order entered by the employee exceeds the approval limits, the purchase order is forwarded to the employees' supervisor, as defined on the Employee form.

To implement this form of approval routing, you need only to define jobs. The job will then serve as the tie to the Approval group, and based on the approval limits from the Approval Group, the Document will either be Approved or Forwarded to the Employees’ Supervisor.

If no Supervisor is able to be located and the job assigned to the employee does not have Approval Authority, then the Approving employee must enter a Forward-to person, or the Document will be returned to an Incomplete status and a notification will be sent to the Approving employee, stating - 'No Approver Found - Please Select a Forward-To Employee'.

 

Oracle R12 AR Auto Lockbox Process

Introduction:  AutoLockbox is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing. AutoLockbox eliminates manual data entry by automatically processing receipts that are sent directly to your bank. You can also use AutoLockbox for historical data conversion.

For example :  you can use AutoLockbox to transfer receipts from your previous accounting system into Receivables.

AutoLockbox ensures that the receipts are accurate and valid before transferring them into Receivables.

AutoLockbox is a three step process:

Import:  During this step, Lockbox reads and formats the data from your bank file into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader script.

Validation: The validation program checks data in this interface table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL). At this point, you can optionally query your receipts in the QuickCash window and change how they will be applied before submitting the final step, Post QuickCash.

Post QuickCash: This step applies the receipts and updates your customer balances. These steps can be submitted individually or at the same time from the submit Lockbox Processing window.

Navigation: AR Superuser/ AR Manage -> Interfaces > Lockbox

After you run Post QuickCash, Receivables treats the receipts like any other receipts, you can reverse and reapply them and apply any unapplied, unidentified, or on-account amounts.
AutoLockbox Process flow

Deferred COGS: How to use this feature of Oracle Apps R12

Deferred COGS: How to use this feature of Oracle Apps R12 

Finally relief for all the accounting users who had problems working with oracle apps due to period mismatch of revenue and cost recognition. Generally, the orders which are shipped on the last day of the month have this issue. As most of you know till release 11i, the value of goods shipped is expensed to COGS when the material in the ship is confirmed in shipping and revenue will get recognized after the invoices are generated in AR and revenue recognition is done. The problem that accountants face with this design is that on the last day of the month COGS gets recognized when we ship confirm the material but the invoice gets generated in next month and so revenue gets recognized in a different month. This does not look good from the matching principle which requires the revenue and cost should get recognized in the same period.


The deferred COGS account is the new feature introduced in Release 12. The key fundamental behind the feature is that the COGS is now directly matched to the Revenue. In simple terms, this means, COGS for an order line will be recognized only if the revenue is recognized for that line making sure that the revenue and COGS are posted in the same month. Matching percentage is also taken care which ensures that revenue and cost are always in sync.

Setup: Only one key setup involved for this functionality is to define the Defer COGS account.

Navigation: Inventory à Setup à Organization à Parameters à Other Accounts

11i Business Process:

In R11i When a Sales order is shipped and interface trip stop is completed. This make a call to COGS workflow to generate the COGS account. This then generate following accounting.

                               COGS Account  Dr $250
                                    Inventory Valuation account Cr $250

R12 Business Process:

In R12 when a sales order is shipped and interface trip stop is completed the following accounting entries gets generated.

                               Deferred COGS Account      Dr  $250
                                    Inventory Valuation account  Cr $250
                 
After the AR invoice is generated and the revenue is recognised (Considering you don’t have revenue recognition policies or specific accounting rules), following program will create COGS recognition transaction. This will reflect a change in the revenue recognition percentage for a sales order line.

Collect Revenue Recognition Information Program: This program will collect the change in revenue recognition percentage based on AR events within the user specified date range. It collects invoice information of the sale order line from RA_CUST_TRX_LINES_ALL and RA_CUST_TRX_LINE_GL_DIST_ALL after the revenue is recognized and check the percentage revenue recognized. It then insert information in CST_REVENUE_RECOGNITION_LINE.

Navigation: Cost > COGS Recognition > Collect Revenue Recognition Information

Generate COGS Recognition Events: This program will create the COGS recognition transaction for each sales order line where there is a mismatch between the latest revenue recognition percentage and the current COGS recognition percentage. This is the program which also makes the transactions costed by creating these accounting entries. So cost manager is not used to make these transactions as costed in R12.The COGS account in this entry is taken from the distribution_account in mtl_material_transactions table (which was generated earlier by COGS workflow).

                     COGS Account           Dr   $250 (Actual Revenue %)
                            Deferred account  Cr   $250 (Actual Revenue %)
         
Navigation: Cost > COGS Recognition > Generate COGS Recognition Events

This particular COGS recognition transaction actually correspond to a revenue recognition percentage change.

As we have seen above this new feature does help in resolving some of the key accounting issues but we need to be aware of the following also

1.With this feature the COGS recognition now requires few extra concurrent requests to be submitted.
2.AR revenue need to be recognized to have COGS recognized
3.This functionality is not optional as it is mandatory to be used in R12
4.To make the complete set of transactions visible in the Material Transaction screen, ‘Include Logical Transaction’ checkbox need to be checked.

This concludes our discussion about Deferred COGS.  Now I am planning to pick some topic from procurement as we have not touched that area till date.

Important Base tables all modules GL,AP,AR,PO,PA,INV

 

GL Journals
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_CODE_COMBINATIONS

AP Suppliers
PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
----------------
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

AP Invoices
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_TERMS
AP_HOLDS_ALL
PA_PROJECTS_ALL
PA_TASKS
PO_VENDORS

AR Customers
HZ_CUST_ACCOUNTS
HZ_PARTIES
HZ_CUST_ACCT_SITES_ALL
HZ_LOCATIONS
HZ_PARTY_SITES
HZ_CUST_SITE_USES_ALL

AR Customer Profiles
HZ_CUSTOMER_PROFILES
HZ_CUST_PROFILE_CLASSES
HZ_CUST_PROFILE_AMTS
AR_COLLECTORS
RA_TERMS
RA_GROUPING_RULES
AR_STATEMENT_CYCLES

AR Customer Contacts
RA_CONTACTS
RA_PHONES

AR Transactions
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_ALL
RA_CUST_TRX_TYPES_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_TERMS
HR_OPERATING_UNITS

AR Receipts
AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_All
AR_RECEIVABLE_APPLICATIONS_ALL
AR_RECEIPT_METHODS
AR_RECEIPT_CLASSES
AR_PAYMENT_SCHEDULES_ALL

PO Standard
PO_HEADERS_ALL
PO_LINES_V
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_V

PO Releases
PO_RELEASES_ALL

PO Requisitions
PO_REQUISITION_HEADERS_V
PO_REQUISITION_LINES_V

Project Data
PA_PROJECTS_ALL
PA_TASKS
PA_PROJECT_PARTIES
PA_PROJECT_ROLE_TYPES_B

Project Transaction Data
PA_EXPENDITURE_ITEMS_ALL
PA_COST_DISTRIBUTION_LINES_ALL
PA_EXPENDITURES_ALL
PA_EXPENDITURE_COMMENTS
PA_PROJECTS_ALL
PA_TASKS

INV Items
MTL_SYSTEM_ITEMS_B
ORG_ORGANIZATION_DEFINITIONS

INV Item Categories
MTL_CATEGORY_SETS_TL
MTL_CATEGORIES
MTL_ITEM_CATEGORIES
MTL_SYSTEM_ITEMS
ORG_ORGANIZATION_DEFINITIONS
MTL_ORGANIZATIONS

INV Item SubInventory
MTL_ITEM_SUB_INVENTORIES
ORG_ORGANIZATION_DEFINITIONS
MTL_SYSTEM_ITEMS_B
MTL_ITEM_LOCATIONS
MTL_SECONDARY_LOCATORS

Attachments
FND_DOCUMENTS_LONG_TEXT
FND_DOCUMENTS_SHORT_TEXT
FND_DOCUMENT_CATEGORIES_TL
FND_DOCUMENT_DATATYPES
FND_DOCUMENTS_TL
FND_DOCUMENTS
FND_ATTACHED_DOCUMENTS
FND_DOC_CATEGORY_USAGES

 

AP Payment Administrator Setup in R12

Data Definition = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0Code = IBY_FD_INSTRUCTION_1_0

3) Create Format of Type=Disbursement Payment Instruction and attach XML Template to itNote- If you want to get XML output, attach XML Template= Extract Identity to your Format and run through complete Payment process request. XML code comes as a single line in output file.

4) Create Payment Process Profile (PPP) and attach above Payment Instruction Format to it.

5) Now Navigate to Setup > Payment > Bank and Bank Branches and Create Bank and Branch

6) Finally Navigate to Setup > Payment > Bank Accounts and create Bank Account with above Bank and Branch defined. Then click on Manage Payment Documents Tab and attach Payment Instruction Format defined above to Bank Account.

1) Login to Payables Manager and Navigate to Setup > Payment > Payment Administrator

2) Click on XML Publisher format Template and create XML Template with below details. Attach required RTF Template for Check Printing

 

AP Payment Testing in R12

1) log in to Payables Manager and Navigate to Payments > Entry > Payments Manager

2) Click on the Payment Process Request Tab and then click submit single request button. Specify PPR Name fill all values and click on submit button. Alternatively, you can also create a template with all values such as payment attributes, payment priority, pay thru days..etc and attach it while creating PPR. Once the request is submitted, it submits program- Auto Select (Payment Process Request Program) in the SRS window.

3) Go back to the PPR tab and search for your request by name or date. Click on Refresh Status Button until Status=Invoices Pending Review and Start Action button turn green.

4) Click on Start Action, review payments, and click on submit button. It ran multiple programs in SRS like- Scheduled Payment Selection Report, Build Payments, and Format Payment Instructions

5) Go back to the PPR tab again and search for your request. Click on Refresh Status Button until Status=Formatted.

6) Review the output file for Format Payment Instructions. It will be XML template output attached to Data Definition=Oracle Payments Funds Disbursement Payment Instruction Extract 1.0

7) Finally Click on Start Action, choose the printer, and submit a check for printing. The status will become Printed

Note: AP Payment has been moved to a new module in R12 called as iPayments. All base tables start with IBY in R12. Below are the key tables in R12 for Payments
IBY_DOCS_PAYABLE_ALL

IBY_PAYMENTS_ALL

IBY_PAY_SERVICE_REQUESTS

IBY_PAY_INSTRUCTIONS_ALL
IBY_PMT_INSTR_USES_ALL

Query for find Concurrent Program Trace file Path Location in Oracle Apps

SELECT req.request_id,

       req.logfile_node_name node,

       req.oracle_Process_id,

       req.enable_trace,

          dest.VALUE

       || '/'

       || LOWER (dbnm.VALUE)

       || '_ora_'

       || oracle_process_id

       || '.trc'

          trace_filename,

       prog.user_concurrent_program_name,

       execname.execution_file_name,

       execname.subroutine_name,

       phase_code,

       status_code,

       ses.SID,

       ses.serial#,

       ses.module,

       ses.machine

  FROM fnd_concurrent_requests req,

       v$session ses,

       v$process proc,

       v$parameter dest,

       v$parameter dbnm,

       fnd_concurrent_programs_vl prog,

       fnd_executables execname

 WHERE     1 = 1

       AND req.request_id = &request      --Concurrent Request ID

       AND req.oracle_process_id = proc.spid(+)

       AND proc.addr = ses.paddr(+)

       AND dest.NAME = 'user_dump_dest'

       AND dbnm.NAME = 'db_name'

       AND req.concurrent_program_id = prog.concurrent_program_id

       AND req.program_application_id = prog.application_id

       AND prog.application_id = execname.application_id

       AND prog.executable_id = execname.executable_id 

Query TO list all purchase document types IN oracle apps

SELECT   haou.NAME operating_unit,
         flv1.meaning
                  || ' '
                  || flv2.meaning                                        document_type,
         pdtav.type_name                                                 NAME,
         xtv1.template_name                                              document_types_layout,
         xtv2.template_name                                              contract_terms_layout,
         Decode (pdtav.can_preparer_approve_flag, 'Y', 'Yes', 'N', 'No') can_preparer_approve,
         Initcap (pdtav.security_level_code)                             security_level,
         pdtav.document_type_code,
         pdtav.document_subtype
FROM     hr_all_organization_units haou,
         po_document_types_all_vl pdtav,
         fnd_lookup_values flv1,
         fnd_lookup_values flv2,
         xdo_templates_vl xtv1,
         xdo_templates_vl xtv2
WHERE    1 = 1
AND      haou.organization_id = pdtav.org_id
AND      pdtav.document_type_code = flv1.lookup_code(+)
AND      pdtav.document_subtype = flv2.lookup_code(+)
AND      flv1.lookup_type(+) = 'DOCUMENT TYPE'
AND      flv2.lookup_type(+) = decode (pdtav.document_type_code, 'REQUISITION', 'REQUISITION TYPE', 'RFQ', 'RFQ SUBTYPE', 'QUOTATION', 'QUOTATION SUBTYPE', 'DOCUMENT SUBTYPE')
AND      flv1.language(+) = userenv ('lang')
AND      flv2.language(+) = userenv ('lang')
AND      flv1.view_application_id(+) = 201
AND      flv2.view_application_id(+) = 201
AND      flv1.security_group_id(+) = 0
AND      flv2.security_group_id(+) = 0
AND      pdtav.document_template_code = xtv1.template_code(+)
AND      pdtav.contract_template_code = xtv2.template_code(+)
ORDER BY haou.NAME ,
         pdtav.document_type_code ,
         pdtav.type_name

Query to find all elements attached to employee in oracle apps

SELECT pbg.NAME business_group_name ,
       papf.employee_number ,
       papf.full_name ,
       petf.element_name ,
       peef.effective_start_date
FROM   per_all_people_f papf ,
       per_all_assignments_f paaf ,
       pay_element_entries_f peef ,
       pay_element_types_f petf ,
       per_business_groups pbg
WHERE  papf.person_id = paaf.person_id
AND    papf.business_group_id = pbg.business_group_id
AND    pbg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND    papf.current_employee_flag = 'Y'
AND    paaf.assignment_id = peef.assignment_id
AND    peef.element_type_id = petf.element_type_id
AND    Trunc (sysdate) BETWEEN papf.effective_start_date AND    papf.effective_end_date
AND    Trunc (sysdate) BETWEEN paaf.effective_start_date AND    paaf.effective_end_date
AND    Trunc (sysdate) BETWEEN peef.effective_start_date AND    peef.effective_end_date
AND    Trunc (sysdate) BETWEEN petf.effective_start_date AND    petf.effective_end_date
AND    petf.element_name LIKE 'PF%'

Query to get all menu names in oracle apps

SELECT frt.responsibility_name ,
       fm.menu_id ,
       fm.menu_name
FROM   fnd_menus fm ,
       fnd_responsibility fr ,
       fnd_responsibility_tl frt
WHERE  fm.menu_id = fr.menu_id
AND    fr.responsibility_id = frt.responsibility_id
AND    fr.responsibility_id = frt.responsibility_id
AND    frt.responsibility_name = 'Receivables Manager'

Query to find the Descriptive Flex Field (DFF) fields attached to a Context Code in Oracle APPS

SELECT fat.application_name

      ,desc_flex.descriptive_flexfield_name

      ,desc_flex.title

      ,desc_flex_col_usg.descriptive_flex_context_code

      ,desc_flex_col_usg.column_seq_num

      ,desc_flex_col_usg.end_user_column_name

      ,desc_flex_col_usg.application_column_name

      ,desc_flex_col_usg.form_left_prompt

      ,desc_flex_col_usg.form_above_prompt

      ,desc_flex_col_usg.flex_value_set_id value_set_id

      ,flex_vs.flex_value_set_name

      ,flex_vs.validation_type

      ,desc_flex_col_usg.enabled_flag

      ,desc_flex_col_usg.required_flag      

      ,desc_flex_col_usg.display_flag

      ,desc_flex_col_usg.display_size

  FROM fnd_descriptive_flexs_tl desc_flex

      ,fnd_descr_flex_col_usage_vl desc_flex_col_usg

      ,fnd_flex_value_sets flex_vs

      ,fnd_application_tl fat

 WHERE  1=1

   AND desc_flex.descriptive_flexfield_name = desc_flex_col_usg.descriptive_flexfield_name

   AND desc_flex.language = USERENV('LANG')

   AND desc_flex_col_usg.descriptive_flex_context_code = 'CONTRACTS'

   --AND desc_flex_col_usg.descriptive_flexfield_name = 'RA_INTERFACE_LINES'

   AND flex_vs.flex_value_set_id(+) = desc_flex_col_usg.flex_value_set_id

   AND desc_flex_col_usg.application_id = fat.application_id

   AND fat.language = USERENV('LANG')

   --AND fat.application_id = 222

   AND desc_flex.title = 'Line Transaction Flexfield'

ORDER BY desc_flex_col_usg.column_seq_num

Query to list Freight From Discount Excluded Records in Oracle APPS

SELECT supp.vendor_name

      ,supp.segment1 vendor_number

      ,supp.exclude_freight_from_discount vendor_freight_discount_excld

  ,supp_site.vendor_site_code

  ,supp_site.exclude_freight_from_discount vend_sit_freight_dis_excld

  FROM ap_suppliers supp

      ,ap_supplier_sites_all supp_site

 WHERE supp.vendor_id = supp_site.vendor_id

   AND supp.exclude_freight_from_discount  IS NULL

   AND supp_site.exclude_freight_from_discount IS NULL

ORDER BY supp.vendor_name 

Query to find list all geography's without Jurisdiction's in oracle apps eb-tax

SELECT                                            *
FROM    (
                SELECT geography_type ,
                       geography_element2_code state_code ,
                       geography_element3_code county_code ,
                       geography_element4_code city_code
                FROM   hz_geographies geography
                WHERE  geography.geography_type='STATE'
                AND    sysdate BETWEEN geography.start_date AND    geography.end_date
                AND    geography_element1_code='US'
                AND    NOT EXISTS
                       (
                              SELECT 1
                              FROM   zx_jurisdictions_b tax_juridiction
                              WHERE  tax_juridiction.zone_geography_id=geography.geography_id
                              AND    tax_juridiction.tax_regime_code = '<<TAX_REGIME_CODE>>'
                              AND    sysdate BETWEEN tax_juridiction.effective_from AND    Nvl(tax_juridiction.effective_to,'31-DEC-4999')
                              AND    tax_juridiction.tax=geography.geography_type )
                UNION
                SELECT geography_type ,
                       geography_element2_code state_code ,
                       geography_element3_code county_code ,
                       geography_element4_code city_code
                FROM   hz_geographies geography
                WHERE  geography.geography_type='COUNTY'
                AND    sysdate BETWEEN geography.start_date AND    geography.end_date
                AND    geography_element1_code='US'
                AND    NOT EXISTS
                       (
                              SELECT 1
                              FROM   zx_jurisdictions_b tax_juridiction
                              WHERE  tax_juridiction.zone_geography_id=geography.geography_id
                              AND    tax_juridiction.tax_regime_code='<<TAX_REGIME_CODE>>'
                              AND    sysdate BETWEEN tax_juridiction.effective_from AND    Nvl(tax_juridiction.effective_to,'31-DEC-4999')
                              AND    tax_juridiction.tax=geography.geography_type )
                UNION
                SELECT geography_type ,
                       geography_element2_code state_code ,
                       geography_element3_code county_code ,
                       geography_element4_code city_code
                FROM   hz_geographies geography
                WHERE  geography.geography_type='CITY'
                AND    sysdate BETWEEN geography.start_date AND    geography.end_date
                AND    geography_element1_code='US'
                AND    NOT EXISTS
                       (
                              SELECT 1
                              FROM   zx_jurisdictions_b tax_juridiction
                              WHERE  tax_juridiction.zone_geography_id = geography.geography_id
                              AND    tax_juridiction.tax_regime_code='_<<TAX_REGIME_CODE>>'
                              AND    sysdate BETWEEN tax_juridiction.effective_from AND    Nvl(tax_juridiction.effective_to,'31-DEC-4999')
                              AND    tax_juridiction.tax=geography.geography_type ) )
ORDER BY geography_type ,
         state_code ,
         county_code ,
         city_code

Query to find all jurisdiction's for which Tax Rates Has been defined in Oracle APPS

SELECT tax      
,tax_jurisdiction_code
	  ,geography_element2_code state_code
	  ,geography_element3_code county_code
	  ,geography_element4_code city_code  
FROM zx_jurisdictions_b tax_juridiction      
,hz_geographies geography 
WHERE tax_juridiction.tax_regime_code = '<<tax_regime_code>>'   
AND SYSDATE BETWEEN tax_juridiction.effective_from AND NVL(tax_juridiction.effective_to,'31-dec-4999')   
AND tax_juridiction.zone_geography_id=geography.geography_id   
AND tax_juridiction.tax = geography.geography_type   
AND SYSDATE BETWEEN geography.start_date AND geography.end_date   
AND NOT EXISTS (SELECT 1                     
FROM zx_rates_b zr                    
WHERE zr.tax_regime_code='<<tax_regime_code>>'                      
AND zr.tax_jurisdiction_code = tax_juridiction.tax_jurisdiction_code
				  )
ORDER BY tax        
,tax_jurisdiction_code
		,geography_element2_code
		,geography_element3_code 

  ,geography_element4_code 

EB-Tax Setup Tables in Oracle APPS

 

EB-Tax Setup Tables in Oracle APPS

Tax Setup	Table Name
Tax Regimes	ZX_REGIMES_B
Taxes	ZX_TAXES_B
Tax Status	ZX_STATUS_B
Tax Rates	ZX_RATES_B
Tax Jurisdictions	ZX_JURISDICTIONS_B
Tax Rules	ZX_RULES_B

 

TABLE_NAME

ZX_ACCOUNTS

ZX_ACCOUNT_RATES

ZX_ACCT_TX_CLS_DEFS_ALL

ZX_API_CODE_COMBINATIONS

ZX_API_OWNER_STATUSES

ZX_API_REGISTRATIONS

ZX_COMPOUND_ERRORS

ZX_COMPOUND_ERRORS_T

ZX_CONDITIONS

ZX_CONDITION_GROUPS_B

ZX_CONDITION_GROUPS_TL

ZX_CONTENT_CHOICES_TMP

ZX_CONTENT_SOURCES

ZX_DATA_UPLOAD_INTERFACE

ZX_DETAIL_TAX_LINES_GT

ZX_DETERMINING_FACTORS_B

ZX_DET_FACTORS_TL

ZX_DET_FACTOR_TEMPL_B

ZX_DET_FACTOR_TEMPL_DTL

ZX_DET_FACTOR_TEMPL_TL

ZX_ERRORS_GT

ZX_EVENT_CLASSES_B

ZX_EVENT_CLASSES_TL

ZX_EVENT_CLASS_PARAMS

ZX_EVNT_CLS_MAPPINGS

ZX_EVNT_CLS_OPTIONS

ZX_EVNT_CLS_TYPS

ZX_EVNT_TYP_MAPPINGS

ZX_EXCEPTIONS

ZX_EXEMPTIONS

ZX_FC_CODES_B

ZX_FC_CODES_CATEG_ASSOC

ZX_FC_CODES_DENORM_B

ZX_FC_CODES_TL

ZX_FC_COUNTRY_DEFAULTS

ZX_FC_TYPES_B

ZX_FC_TYPES_REG_ASSOC

ZX_FC_TYPES_TL

ZX_FORMULA_B

ZX_FORMULA_DETAILS

ZX_FORMULA_TL

ZX_ID_TCC_MAPPING_ALL

ZX_IMPORT_TAX_LINES_GT

ZX_ITM_DISTRIBUTIONS_GT

ZX_JURISDICTIONS_B

ZX_JURISDICTIONS_GT

ZX_JURISDICTIONS_TL

ZX_LINES

ZX_LINES_DET_FACTORS

ZX_LINES_SUMMARY

ZX_PARAMETERS_B

ZX_PARAMETERS_TL

ZX_PARAM_DETAILS

ZX_PARTY_TAX_PROFILE

ZX_PARTY_TYPES

ZX_PO_REC_DIST

ZX_PROCESS_RESULTS

ZX_PRODUCT_OPTIONS_ALL

ZX_PRVDR_HDR_EXTNS_GT

ZX_PRVDR_LINE_EXTNS_GT

ZX_PTNR_LOCATION_INFO_GT

ZX_PTNR_NEG_LINE_GT

ZX_PTNR_NEG_TAX_LINE_GT

ZX_PURGE_TRANSACTIONS_GT

ZX_RATES_B

ZX_RATES_TL

ZX_REGIMES_USAGES

ZX_REGIME_RELATIONS

ZX_REGISTRATIONS

ZX_REPORTING_CODES_B

ZX_REPORTING_CODES_TL

ZX_REPORTING_TYPES_B

ZX_REPORTING_TYPES_TL

ZX_REPORT_CODES_ASSOC

ZX_REPORT_TYPES_USAGES

ZX_REP_ACTG_EXT_T

ZX_REP_CONTEXT_T

ZX_REP_MATRIX_EXT_T

ZX_REP_TRX_DETAIL_T

ZX_REP_TRX_JX_EXT_T

ZX_REVERSE_DIST_GT

ZX_REVERSE_TRX_LINES_GT

ZX_RULES_B

ZX_RULES_TL

ZX_SERVICE_TYPES

ZX_SIM_CONDITIONS

ZX_SIM_PROCESS_RESULTS

ZX_SIM_PURGE

ZX_SIM_RULES_B

ZX_SIM_RULES_TL

ZX_SIM_RULE_CONDITIONS

ZX_SIM_TRX_DISTS

ZX_SRVC_SBSCRPTN_EXCLS

ZX_SRVC_SUBSCRIPTIONS

ZX_SRVC_TYP_PARAMS

ZX_STATUS_B

ZX_STATUS_TL

ZX_SUBSCRIPTION_DETAILS

ZX_SUBSCRIPTION_OPTIONS

ZX_TAXES_B

ZX_TAXES_TL

ZX_TAX_PRIORITIES_T

ZX_TAX_RELATIONS_T

ZX_TRANSACTION

ZX_TRANSACTION_LINES

ZX_TRANSACTION_LINES_GT

ZX_TRX_HEADERS_GT

ZX_TRX_LINE_APP_REGIMES

ZX_TRX_PRE_PROC_OPTIONS_GT

ZX_TRX_TAX_LINK_GT

ZX_UPDATE_CRITERIA_RESULTS

ZX_VALIDATION_ERRORS_GT