Oracle Receivables - Version 12.0.1 and later
Oracle Payables - Version 12.0.0 and later
Oracle E-Business Tax - Version 12.0.1 and later
Information in this document applies to any platform.

Abstract

This note provides the information regarding various data corruption issues, identification queries and GDF/RCA patches to rectify the data corruption.

History

Author: ssohal,armitra
Create Date 25-Apr-2011
Update Date 29-Jul-2016

Details

Section 1: ZX_LINES_SUMMARY_U1 / ZX_SUMMARY_CONSTRAINT_VIOLATED / A mandatory constraint to generate unique summary tax line is violated

Please refer to Note 1152123.1 for complete details.

Section 2: Frozen Tax distributions deleted by EB-Tax / AP_FRZN_TAX_DIST_DEL / Frozen Tax distributions tax lines that need to be deleted are found on this invoice

Please refer to Note 1152029.1 for complete details.

Section 3: For Cross Currency Transactions, tax amount in functional currency is not populated for some tax distributions. This leads to incorrect accounting of the cross currency transactions.

Please refer to Note 1396732.1 for complete details.

Section 4: Summary Tax Line deleted by EB-Tax / AP_SUM_TAX_LINE_DEL / Summary tax lines that need to be deleted are found on this invoice

DESCRIPTION:
This error can occur because of following two reasons -

(1) There exists a summary tax line in AP but missing in ZX. Following query identifies the summary tax lines of an invoice that do not exist in ZX. If this query returns any record then extract the APList and log a bug against EB-Tax.

IDENTIFICATION SQL
------------------
SELECT ail.summary_tax_line_id
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = &INVOICE_ID
   AND ail.line_type_lookup_code = 'TAX'
   AND ail.summary_tax_line_id IS NOT NULL
   AND NOT EXISTS
         (SELECT 1 FROM zx_lines_summary zls
           WHERE zls.summary_tax_line_id = ail.summary_tax_line_id
             AND zls.trx_id = ail.invoice_id);

(2) During tax processing EB-Tax does not retain a summary tax line. If the above query does not return any record then that means the summary tax line is getting deleted during tax processing.

(2.1) Summary Tax Line gets deleted during tax processing because of mismatch in any summarization criteria column. Sometimes this cannot be predicted from the current state of data (APList). Please apply following code-fix patches and data-fixes.

RECOMMENDED SOLUTION:
Code-Fix:
For 12.0.X (R12.ZX.A) - Patch:8736358 , Patch:10296081 , Patch:10627713 , Patch:12558634 , Patch:13732606 , Patch:13946670 , Patch:14122718 , Patch:14198788
For 12.1.X (R12.ZX.B) - Patch:8877828 , Patch:10296081 , Patch:10627713 , Patch:12558634 , Patch:13732606 , Patch:13946670 , Patch:14122718 , Patch:14198788

Please note that some of the above patches are recommended to have the latest version of the source files.

Data-Fix: Patch:10095488 , Patch:12360358

For R12.2.X Please apply Patch:23728207

(2.2) Summary Tax Line gets deleted during tax processing if user does not have the latest code for handling zero amount reference tax line and cancels the zero amount reference tax line. To fix this issue customer has to first apply the data-fix to remove the tax data of the invoice and apply the following code-fix patches:

RECOMMENDED SOLUTION:
Code-Fix:
For 12.0.X (R12.ZX.A) - Patch:10184087 , Patch:13959784 , Patch:13883460
For 12.1.X (R12.ZX.B) - Patch:10184087 , Patch:13959784 , Patch:13883460

For 12.2.X (R12.ZX.C) - Patch:13883460

To get the data-fix for such problemtic invoices, please log a bug against EB-Tax (Product_Id 1087) with latest APList and FND Debug log file. OR if issue persists after applying these code-fix patches and data-fixes then extract the APList, FND Debug Log file of Online Invoice Validation and log a bug against EB-Tax.

(3) Confirm the value of the tax_amt_included_flag is not the same at the ZX_LINES level and temp table zx_detail_tax_lines_gt. You can check the values from the FND.
Try to change the Inclusive flag to NO for the specific Tax code, Otherwise please Log a service request attaching the FND debug and the tax diagnsotic setup.

Section 5: Missing Reversal Tax Distributions for Tax Distributions/ Tax lines for discarded or cancelled Transaction Lines are not marked as canceled causing Invoice to be placed on Hold / Error AP_ERR_TAX_DIST_SYNC during invoice cancellation

DESCRIPTION:

There was a code issue where the reversals of the tax distributions were not saved but the original tax distributions were marked as 'Reversed'. Because of this, DIST VARIANCE hold was placed on the invoice.
There was a code issue where when the transaction lines were discarded or transaction was cancelled, the tax amount on the tax lines without tax distributions does not change to zero and they were not marked as cancelled. Because of this, LINE VARIANCE hold was placed on the invoice.
If there is corruption at tax distributions level, cancellation of invoice fails with error AP_ERR_TAX_DIST_SYNC. In this case also, follow the under-mentioned recommended solution.

IDENTIFICATION SQLs
-------------------

-- 1.Invoices with tax lines of discarded or cancelled item lines that are not marked as canceled or do not have zero tax amount

SELECT /*+ leading(ai) parallel(ai) index(ail AP_INVOICE_LINES_U1)*/
         DISTINCT ai.invoice_id,
         ai.invoice_date,
         ai.org_id
    FROM ap_invoices_all ai,
         ap_invoice_lines_all ail,
         ap_holds_all ah
   WHERE NVL(ai.historical_flag,'N') <> 'Y'
     AND ai.cancelled_date IS NULL
     AND ah.invoice_id = ai.invoice_id
     AND ah.hold_lookup_code IN ('DIST VARIANCE','LINE VARIANCE')
     AND ah.release_lookup_code IS NULL
     AND ail.invoice_id = ai.invoice_id
     AND ail.line_type_lookup_code <> 'TAX'
     AND (NVL(ail.discarded_flag,'N') = 'Y' OR
          NVL(ail.cancelled_flag,'N') = 'Y')
     AND EXISTS
         (SELECT /*+ first_rows(1) index(zl ZX_LINES_U1) */
                 1
            FROM zx_lines zl
           WHERE zl.application_id = 200
             AND zl.entity_code = 'AP_INVOICES'
             AND zl.event_class_code IN
                  ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
             AND zl.trx_id = ai.invoice_id
             AND zl.trx_level_type = 'LINE'
             AND zl.trx_line_id = ail.line_number
             AND (NVL(zl.cancel_flag,'N') <> 'Y' OR zl.tax_amt <> 0));

-- 2.Invoices on Dist Variance or Line Variance hold where tax lines tax amount is not matching with the sum of amounts of their distributions

SELECT /*+ leading ah */ DISTINCT ai.invoice_id,

ai.invoice_date,

ai.org_id

FROM ap_invoices_all ai,

ap_holds_all ah

WHERE NVL(ai.historical_flag,'N') <> 'Y'

AND ai.cancelled_date IS NULL

AND ai.invoice_id = ah.invoice_id

AND ah.hold_lookup_code IN ('DIST VARIANCE','LINE VARIANCE')

AND ah.release_lookup_code IS NULL

AND EXISTS

(SELECT /*+ first_rows(1) leading zl index(zl ZX_LINES_N4) index(zd ZX_REC_NREC_DIST_U2) */

zd.tax_line_id,

zl.tax_amt,

SUM(zd.rec_nrec_tax_amt) tot_rec_nrec_amt

FROM zx_lines zl,

zx_rec_nrec_dist zd

WHERE zl.application_id = 200

AND zl.trx_id = ai.invoice_id

AND zd.tax_line_id = zl.tax_line_id

AND zd.application_id = zl.application_id

AND zd.entity_code = zl.entity_code

AND zd.event_class_code = zl.event_class_code

AND zd.trx_id = zl.trx_id

GROUP BY zd.tax_line_id, zl.tax_amt

HAVING zl.tax_amt <> SUM(zd.rec_nrec_tax_amt));

-- 3.Invoices on Dist Variance or Line Variance hold where amount of tax distrbutions in ZX is not matching with the amount of tax distributions in AP

SELECT /*+ leading ah */ DISTINCT ai.invoice_id,

ai.invoice_date,

ai.org_id

FROM ap_invoices_all ai,

ap_holds_all ah

WHERE NVL(ai.historical_flag,'N') <> 'Y'

AND ai.cancelled_date IS NULL

AND ai.invoice_id = ah.invoice_id

AND ah.hold_lookup_code IN ('DIST VARIANCE','LINE VARIANCE')

AND ah.release_lookup_code IS NULL

AND (EXISTS

(SELECT /*+ first_rows(1) leading aid index(zd ZX_REC_NREC_DIST_U1) */

aid.detail_tax_dist_id,

zd.rec_nrec_tax_amt,

SUM(aid.amount) ap_rec_nrec_amt

FROM ap_invoice_distributions_all aid,

zx_rec_nrec_dist zd

WHERE aid.invoice_id = ai.invoice_id

AND aid.line_type_lookup_code IN

('REC_TAX','NONREC_TAX','TRV','TIPV','TERV')

AND aid.detail_tax_dist_id IS NOT NULL

AND zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id

GROUP BY aid.detail_tax_dist_id, zd.rec_nrec_tax_amt

HAVING zd.rec_nrec_tax_amt <> SUM(aid.amount))

OR

EXISTS

(SELECT /*+ first_rows(1) leading asad index(zd ZX_REC_NREC_DIST_U1) */

1

FROM ap_self_assessed_tax_dist_all asad,

zx_rec_nrec_dist zd

WHERE asad.invoice_id = ai.invoice_id

AND asad.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')

AND asad.detail_tax_dist_id IS NOT NULL

AND zd.rec_nrec_tax_dist_id = asad.detail_tax_dist_id

AND zd.rec_nrec_tax_amt <> asad.amount));

RECOMMENDED SOLUTION:
If either of the above queries returns any record then please apply the GDF Patch 17603319:R12.ZX.A for 12.0.x or GDF Patch 17603319:R12.ZX.B for 12.1.x. For 12.2.x, please review Note 2089108.1 .

Also, it is mandatory to apply RCA Patch 19711877 to avoid this issue to re-occur for future invoices (specifically, Patch 19711877:R12.ZX.A for 12.0.x, Patch 19711877:R12.ZX.B for 12.1.x and Patch 19711877:R12.ZX.C for 12.2.x). This patch is the replacement patch of 16095365 and has the code-fix for both the issues mentioned above.

Section 6: AP_UNFROZEN_DIST_EXIST/Unfrozen Tax Distributions exist for this Invoice

DESCRIPTION:

This error occurs when there exists some unfrozen tax distributions for an Invoice. This error mostly occurs for Migrated invoices.
This error occurs with ORA-20001: APP-SQLAP-4667482: Not all tax distributions were frozen on this invoice.

IDENTIFICATION SQL
------------------
SELECT /*+parallel(ai)*/ ai.invoice_id
FROM ap_invoices_all ai
WHERE NVL(ai.historical_flag,'N') = 'Y'
   AND ai.cancelled_date IS NULL
   AND EXISTS
        (SELECT /*+first_rows(1)*/ 1
           FROM ap_invoice_distributions_all aid,
                zx_rec_nrec_dist zd

          WHERE aid.invoice_id = ai.invoice_id
            AND aid.line_type_lookup_code IN
                 ('REC_TAX','NONREC_TAX','TRV','TIPV','TERV')
            AND NVL(aid.historical_flag,'N') = 'Y'
            AND aid.accounting_event_id IS NOT NULL
            AND zd.trx_id = aid.invoice_id
            AND zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
            AND NVL(zd.historical_flag,'N') = 'Y'
            AND NVL(zd.freeze_flag,'N') <> 'Y'
            AND ROWNUM = 1);

RECOMMENDED SOLUTION:
If the above query returns any record then apply data-fix in Patch:12644252 .

To prevent the issue please apply the following Patch in Pre-Install Mode during next Upgrade

Patch:12648752 : RCA : POPULATE CORRECT VALUE OF FRZ_FLG AND ASSOC_CHILD_FRZN_FLG DURING UPGRADE

Section 7: Cancel_Flag not stamped on Summary Tax Line

DESCRIPTION:
Cancel_Flag is not stamped as 'Y' on the summary tax line that have all the canceled detail tax lines. The invoices with this issue get picked up for Invoice Validation during Bulk Invoice Validation Program.

IDENTIFICATION SQL
------------------
SELECT summary_tax_line_id
FROM zx_lines_summary zls
WHERE NVL(zls.cancel_flag,'N') <> 'Y'
   AND zls.trx_id = &INVOICE_ID
   AND zls.application_id = 200
   AND zls.entity_code = 'AP_INVOICES'
   AND zls.event_class_code IN ('STANDARD INVOICES',
        'PREPAYMENT INVOICES','EXPENSE REPORTS')
   AND NOT EXISTS
        (SELECT 1 FROM zx_lines zl
          WHERE zl.trx_id = zls.trx_id
            AND zl.summary_tax_line_id = zls.summary_tax_line_id
            AND zl.application_id = zls.application_id
            AND zl.entity_code = zls.entity_code
            AND zl.event_class_code = zls.event_class_code
            AND NVL(zl.cancel_flag,'N') = 'N')
   AND EXISTS
        (SELECT 1 FROM zx_lines zl
          WHERE zl.trx_id = zls.trx_id
            AND zl.summary_tax_line_id = zls.summary_tax_line_id
            AND zl.application_id = zls.application_id
            AND zl.entity_code = zls.entity_code
            AND zl.event_class_code = zls.event_class_code
            AND NVL(zl.cancel_flag,'N') = 'Y');

RECOMMENDED SOLUTION:
Code-Fix : Patch:9193069 :PAID INVOICES WITH DISCRADED ITEM/TAX LINES GET PICKED FOR INVOICE VALIDATION
Data-Fix : Patch:11703570

Section 8: Issue with the Output of Tax Classification Code LOV

DESCRIPTION:
(1) Tax Classification Code LOV shows duplicate tax classification codes OR (2) Description of Tax Classification Code in LOV is not correct.

IDENTIFICATION SQL
------------------
Select tcc_mapping_id,org_id,org_id,tax_class,tax_rate_code_id,tax_classification_code FROM zx_id_tcc_mapping_all tcc
   WHERE EXISTS (SELECT 1 FROM zx_rates_b rate
                  WHERE rate.tax_rate_id = tcc.tax_rate_code_id
                    AND rate.rate_type_code = 'RECOVERY');

RECOMMENDED SOLUTION:
If the above query either shows duplicate values or the value of the column DESCRIPTION is not same as in Tax Rate setup then apply Patch:10017044 to fix this issue.
For R12.1.x Customers if APXINWKB (Payables Invoice Worbench) version is less than 120.601.12010000.497 please apply RCA Patch 20454587:R12.AP.B

Section 9: Account based Tax Rules do not evaluate

DESCRIPTION:
Account based Tax Rules are not evaluated that are created with new conditions under migrated Determining Factor Set 'EX Acct String Range-Party FC'. This issue occurs because the column TAX_PARAMETER_CODE in table ZX_DET_FACTOR_TEMPL_DTL is NULL. This issue only occurs in R12.0.X codeline. Customerâs migrating from 11i to R12.1.X will not hit this issue.

IDENTIFICATION SQL
------------------
SELECT TAX_PARAMETER_CODE
FROM ZX_DET_FACTOR_TEMPL_DTL
WHERE DET_FACTOR_TEMPL_ID IN
         (SELECT DET_FACTOR_TEMPL_ID
            FROM ZX_DET_FACTOR_TEMPL_B
           WHERE DET_FACTOR_TEMPL_CODE = 'EX Acct String Range-Party FC'
             AND RECORD_TYPE_CODE = 'MIGRATED')
   AND DETERMINING_FACTOR_CLASS_CODE = 'ACCOUNTING_FLEXFIELD';

RECOMMENDED SOLUTION:
If the above query returns No value then apply Patch:9546116 to fix this issue. This patch is available for Release 12.0.x
The issue is not seen in later releases, but you can log SR with Oracle Support to get fix script zx_upd_acct_dft.sql

Section 10: Import fails with error 'ZX_TRX_BIZ_FC_CODE_NOT_EXIST'

DESCRIPTION:
Invoice Import concurrent program in some specific language fails with error 'ZX_TRX_BIZ_FC_CODE_NOT_EXIST'. This error occurs because there are some missing classification codes in table ZX_FC_CODES_DENORM_B for all the installed languages. Execute following query to identify the missing classification codes.

IDENTIFICATION SQL
------------------
SELECT fc_code_1.classification_code
FROM zx_fc_codes_denorm_b fc_code_1,
       (SELECT DISTINCT classification_code,
               classification_id,
               classification_type_code,
               classification_type_id
          FROM zx_fc_codes_denorm_b
         WHERE (classification_type_code,
                classification_type_id)
                 IN (SELECT DISTINCT classification_type_code,
                            classification_type_id
                       FROM zx_fc_types_b fc_typ)
       ) fc_code_2
WHERE fc_code_1.classification_type_code = fc_code_2.classification_type_code
   AND fc_code_1.classification_type_id = fc_code_2.classification_type_id
   AND fc_code_1.classification_code = fc_code_2.classification_code
   AND fc_code_1.classification_id = fc_code_2.classification_id
GROUP BY fc_code_1.classification_type_code,
       fc_code_1.classification_type_id,
       fc_code_1.classification_code,
       fc_code_1.classification_id
HAVING Count(*) <
         (SELECT Count(*) no_of_languages
            FROM fnd_languages
           WHERE installed_flag IN ('I','B'));

RECOMMENDED SOLUTION:
Apply GDF Patch:10093528 if the above query returns any record.

Section 11: Tax Setup not migrated

DESCRIPTION:
The tax setup not migrated correctly. Some or all Tax setup entities like Tax-Regime, Tax, Tax-Status etc. have missing data.

Identification SQL for missing tax status

------------------------------------------
SELECT tax_regime_code,
       tax,
       tax_status_code,
       min(effective_from) effective_from
FROM zx_update_criteria_results results
WHERE NOT EXISTS
         (SELECT 1 FROM zx_status_b
           WHERE tax_regime_code = results.tax_regime_code
             AND tax = results.tax
             AND tax_status_code = results.tax_status_code)
GROUP BY tax_regime_code, tax, tax_status_code;



Identification SQL for missing tax rate
---------------------------------------
SELECT tax_regime_code,
       tax,
       tax_status_code,
       tax_code tax_rate_code,
       tax_code_id tax_rate_id,
       tax_class,
       org_id
FROM zx_update_criteria_results results
WHERE NOT EXISTS
        (SELECT 1 FROM zx_rates_b
          WHERE tax_class in('OUTPUT','INPUT')
            AND tax_regime_code = results.tax_regime_code
            AND tax = results.tax
            AND tax_status_code = results.tax_status_code
            AND Nvl(source_id,tax_rate_id) = results.tax_code_id)
ORDER BY org_id, tax_code_id;

Identification SQL for missing tax
----------------------------------
SELECT tax_regime_code,
       tax,
       min(effective_from) effective_from
FROM zx_update_criteria_results results
WHERE NOT EXISTS
         (SELECT 1 FROM zx_taxes_b
           WHERE tax_regime_code = results.tax_regime_code
             AND tax = results.tax)
GROUP BY tax_regime_code, tax;

Identification SQL for missing tax regime
------------------------------------------
SELECT tax_regime_code,
       min(effective_from) effective_from
FROM zx_update_criteria_results results
WHERE NOT EXISTS
        (SELECT 1 FROM zx_regimes_b
          WHERE tax_regime_code = results.tax_regime_code)
GROUP BY tax_regime_code;

RECOMMENDED SOLUTION:

Data Fix: Log a Service Request to obtain Datafix from support .

Apply the following Pre-Install patches in the sequence mentioned below in your next upgrade to prevent the issue:
1. Pre-Install Patch:10029457 :TAX CODES WHICH ARE USED IN TAX GROUPS UPGRADED FROM 11I TO R12 UNDER DIFFERENT REGIME
2. Pre-Install Patch:10221534 :RCA: MISSING TAX CODE DESCRIPTION IN MIGRATED DATA TO R12(For R12.0.X)

3. Pre-Install Patch:13963357 :RCA: SUPPLIER TYPE RECOVERY RULES ARE NOT EVALUATED AFTER MIGRATING TO R12(For R12.1.X)
4. Pre-Install Patch:10250232 :The fiscal classification codes migration failing with U1 violation(For R12.0.X)
5. Pre-Install Patch:10395967 :11i LOCATION BASED ITEM EXEMPTIONS ARE NOT EVALUATED IN R12

Section 12: Tax not calculating after upgrading from 11.5.9 to 12.1.1

DESCRIPTION:
Tax not calculating after upgrading from 11.5.9 to 12.1.1, because 'TAXREGIME' is not available as a determining factor template code. Associated determining factor classes are also missing.

IDENTIFICATION SQL
------------------
SELECT det_factor_templ_code
FROM zx_det_factor_templ_b
WHERE det_factor_templ_code = 'TAXREGIME';

RECOMMENDED SOLUTION:
For 12.1 if the above query does not return any record then apply Code-fix in Patch:8747425 to fix this issue. If customer is in 12.2 Download patch 8747425:R12.ZX.B and apply the sql file manually to fix the missing record

Section 13: Default Rate Flag Incorrect For Non Recovery Based Rates

DESCRIPTION:
Atleast one rate should have this flag set to 'Y' for a given combination of regime,tax, status and content owner

IDENTIFICATION SQL
------------------
select * from zx_rates_b_tmp rates
where rates.tax_rate_code in ( select rates1.tax_rate_code from zx_rates_b rates1
            where rates.tax_regime_code = rates1.tax_regime_code
            and rates.tax = rates1.tax
            and rates.tax_status_code = rates1.tax_status_code
            and rates.content_owner_id = rates1.content_owner_id
            and rates1.record_type_code = 'MIGRATED'
            and rates1.rate_type_code <> 'RECOVERY'
                              and sysdate between rates1.effective_from
            and nvl(rates1.effective_to,sysdate)
            and rownum = 1)
/* Not Exists is to prevent the default_rate_flag to be updated to 'Y' for 2 rates under the same combination of regime,tax,status and Content owner */
and not exists (select 1 from zx_rates_b rates2
             where rates2.tax_regime_code = rates.tax_regime_code
            and rates2.tax = rates.tax
            and rates2.tax_status_code = rates.tax_status_code
            and rates2.content_owner_id = rates.content_owner_id
                  and rates2.rate_type_code <> 'RECOVERY'
            and rates2.default_rate_flag = 'Y' );

RECOMMENDED SOLUTION:
Data-Fix : Please re-enable the default flag from UI or create rules to get correct tax rate code

Section 14: Set Auto_Tax_Calc_Flag at Supplier Site Level for Supplier Type EMPLOYEE

DESCRIPTION:
Tax not calculating for the following supplier with supplier type EMPLOYEE because AUTO_TAX_CALC_FLAG is NO at supplier site level.

IDENTIFICATION SQL
------------------
SELECT APS.VENDOR_NAME,
       APS.VENDOR_ID,
       APSS.VENDOR_SITE_CODE,
       APSS.VENDOR_SITE_ID,
       APSS.AUTO_TAX_CALC_FLAG
FROM AP_SUPPLIER_SITES_ALL APSS,
       AP_SUPPLIERS APS
WHERE APS.VENDOR_ID = APSS.VENDOR_ID
AND APSS.AUTO_TAX_CALC_FLAG='N'
AND APSS.AUTO_TAX_CALC_FLAG IS NOT NULL
AND APS.EMPLOYEE_ID IS NOT NULL
AND APS.VENDOR_ID = &SUPPLIER_ID;

RECOMMENDED SOLUTION:
Data-Fix : Please apply the following Datafix. If you want to enable AUTO_TAX_CALC_FLAG to 'Y' for specific supplier you need to pass the vendor_id in the Update statement.

This is already approved by Development. So no need to log an SR for this.

UPDATE AP_SUPPLIER_SITES_ALL
SET    AUTO_TAX_CALC_FLAG = 'Y'
WHERE VENDOR_SITE_ID IN (SELECT APSS.VENDOR_SITE_ID
                            FROM AP_SUPPLIER_SITES_ALL APSS,
                                 AP_SUPPLIERS APS
                           WHERE APS.VENDOR_ID = APSS.VENDOR_ID
                             AND APS.EMPLOYEE_ID IS NOT NULL) AND VENDOR_ID=&Supplier_id(Specify the vendor_id for which you want Auto tax calculation)
AND    AUTO_TAX_CALC_FLAG = 'N';

Commit;

Section 15: Tax Tolerance is not upgraded to Configuration Owner Tax Options In R12

DESCRIPTION:
Customer is Unable to View Tax Tolerance in Configuration Owner Tax Option Page

IDENTIFICATION SQL
------------------
select * from zx_evnt_cls_options
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND record_type_code = 'MIGRATED'
AND NVL(allow_override_flag,'N') <> 'Y';

RECOMMENDED SOLUTION:
Data-Fix : Please apply the following Datafix.This is already approved by Development. So no need to log an SR for this.

Update zx_evnt_cls_options set allow_override_flag=’Y’

WHERE application_id = 200AND entity_code = 'AP_INVOICES'

AND record_type_code = 'MIGRATED'

AND NVL(allow_override_flag,'N') <> 'Y';

Commit;

Ask Customer to apply Preinstall Patch:13019385 to prevent the issue during next upgrade.

Section16: Supplier's Tax Code Description is Different in 11i and R12

DESCRIPTION:
Customer is Unable to View Tax Code Description which was present in 11i

Go to Tax Configuration->Tax Rates

Choose the Migrated Tax rate identified by the following query
Click on "View Rate Details"

Under "Reporting Details" you cannot see the value in Tax Rate Description field

User may not find the Tax Descriptions in Tax related reports as well which in turn may cause Tax Reporting issues

In Invoice workbench->Invoice Lines user will not be able to see the Tax Rate description in Tax Classification Code LOV

IDENTIFICATION SQL
------------------
SELECT distinct Tax_Rate_Name,Tax_Rate_Id,Description FROM ZX_RATES_TL ZRT
WHERE DESCRIPTION IS NULL
AND EXISTS (SELECT 1
FROM AP_TAX_CODES_ALL AP, ZX_RATES_B ZRB
WHERE AP.TAX_ID = ZRB.SOURCE_ID and AP.Description is Not Null
AND ZRB.TAX_CLASS = 'INPUT'
AND ZRB.TAX_RATE_ID = ZRT.TAX_RATE_ID)

RECOMMENDED SOLUTION:
Data-Fix : Please apply GDF Patch:13638987 to resolve the issue

Ask Customer to apply Preinstall Patch: 13963357 to prevent the issue during next upgrade.

For 12.2: If customer is performing a new upgrade, please request customer to uptake patch 21224981 in pre-install mode

For already upgraded environments, please requests customer to create a new SR to obtain script b21224981.sql to fix the issue.

NOTE:1594893.1 - Upgrade Fails On Script Zxmigratefc.sql Running Driver 6678700

Section 17: Migrated Tax Lines have NULL Regime-to-Rate information in ZX_LINES

DESCRIPTION:
Migrated invoices fail during validation because the tax lines do not have regime to rate information stamped on them. Invoice validation fails with errors like (1) Tax/Tax_Id cannot be NULL, (2) Tax does not exist, (3) Tax Rate Code does not exist, etc.

IDENTIFICATION SQL
------------------
S ELECT /*+parallel(zl)*/
       COUNT(DISTINCT trx_id)
FROM zx_lines zl
WHERE application_id = 200
   AND entity_code = 'AP_INVOICES'
   AND record_type_code = 'MIGRATED'
   AND historical_flag = 'Y'
   AND (tax_regime_code IS NULL OR
        tax_regime_id IS NULL OR
        tax IS NULL OR
        tax_id IS NULL OR
        tax_status_id IS NULL OR
        tax_status_code IS NULL OR
        tax_rate_id IS NULL OR
        tax_rate_code IS NULL OR
        tax_rate IS NULL);

RECOMMENDED SOLUTION:
If the above query returns count greater than 0, then Log a Service Request with Oracle Support to obtain Datafix.

Section 18: Event Class Options Are Not Migrated For Payables

DESCRIPTION:
Customer is Unable to see the migrated Event Class Options

IDENTIFICATION SQL
------------------
SELECT mapping.application_id,
       mapping.entity_code,
       mapping.event_class_code,
       sys.org_id,
       ptp.party_tax_profile_id
FROM ZX_PARTY_TAX_PROFILE ptp,
       AP_SYSTEM_PARAMETERS_ALL sys,
       ZX_EVNT_CLS_MAPPINGS mapping
WHERE mapping.application_id = 200
   AND ptp.party_id = sys.org_id
   AND ptp.PARTY_TYPE_CODE ='OU'
   AND ptp.record_type_code='MIGRATED'
   AND NOT EXISTS (SELECT 1
                     FROM ZX_EVNT_CLS_OPTIONS opt
                    WHERE opt.FIRST_PTY_ORG_ID = ptp.party_tax_profile_id
                      AND opt.APPLICATION_ID   = mapping.application_id
                      AND opt.ENTITY_CODE      = mapping.entity_code
                      AND opt.EVENT_CLASS_CODE = mapping.event_class_code
                  );

RECOMMENDED SOLUTION:
Data-Fix : Please execute $ZX_TOP/patch/115/sql/zxmigevntclsopt.sql manually

Ask Customer to apply Preinstall Patch: 8495719 to prevent the issue during next upgrade

Section 19: Payables Tax Codes are not Migrated

DESCRIPTION:
Customer is Unable to use the 11i Taxes after migration

IDENTIFICATION SQL
------------------
SELECT NAME, ORG_ID, ENABLED_FLAG, START_DATE, INACTIVE_DATE
FROM AP_TAX_CODES_ALL
WHERE TAX_TYPE NOT IN ('TAX_GROUP','AWT')
AND NOT EXISTS (SELECT 1 FROM ZX_RATES_B
WHERE SOURCE_ID = TAX_ID
AND TAX_CLASS = 'INPUT');

RECOMMENDED SOLUTION:
Data-Fix : Please check the file version of zxtaxdefmigb.pls and zxtaxhiermigb.pls

You can use the following query to get the file versions.

SELECT text FROM all_source
WHERE name in ('ZX_MIGRATE_TAX_DEF','ZX_MIGRATE_TAX_DEFAULT_HIER')
AND line = 2;

If the version of zxtaxdefmigb.pls is less than 120.124.12010000.20 and if the version of zxtaxhiermigb.pls is less than 120.40.12010000.7
apply following patches

zxtaxdefmigb.pls -> 13963357:R12.ZX.B
zxtaxhiermigb.pls -> 14588757:R12.ZX.B

Post patch application, run the setup upgrade script

If the versions are already higher, please run the setup upgrade script only(zx_mig_missing_setup.sql)

For all customers on 12.2 i.e., 12.2.0, 12.2.1, 12.2.2, 12.2.3, 12.2.4, 12.2.5
customers must uptake patch 22246052:R12.ZX.C and then run zx_mig_missing_setup.sql else, zx_mig_missing_setup.sql will fail

This patch will be the only additional step for 12.2 customers and no impact for 12.1 customers

.

For the next round of R12 upgrade, customer can apply the above mentioned patches in preinstall mode.

If still the issue is not resolved, please log an SR with Oracle Support.

Ask Customer to apply Preinstall Patch: 13963357 and Patch: 14588757 to prevent the issue during next upgrade

Section 20: Tax Groups are not Migrated

DESCRIPTION:
Customer is Unable to use the Migrated Taxes

IDENTIFICATION SQL for Payables Tax Group
-----------------------------------------
SELECT NAME, ORG_ID, START_DATE, INACTIVE_DATE, ENABLED_FLAG
FROM AP_TAX_CODES_ALL VAT
WHERE TAX_TYPE = 'TAX_GROUP'
AND NOT EXISTS (SELECT 1 FROM ZX_CONDITION_GROUPS_B GRP
        WHERE (NAME = CONDITION_GROUP_CODE
             OR NAME||'-'||'XIP' = CONDITION_GROUP_CODE
            )
        AND DET_FACTOR_TEMPL_CODE = 'STCC');

IDENTIFICATION SQL for Receivables Tax Group
-----------------------------------------

SELECT TAX_CODE, ORG_ID, START_DATE, END_DATE, ENABLED_FLAG
FROM AR_VAT_TAX_ALL VAT
WHERE TAX_TYPE = 'TAX_GROUP'
AND NOT EXISTS (SELECT 1 FROM ZX_CONDITION_GROUPS_B GRP
      WHERE (SUBSTR(VAT.TAX_CODE,1,40) = SUBSTR(CONDITION_GROUP_CODE,1,40)
       OR VAT.TAX_CODE||'-'||'XOP' = CONDITION_GROUP_CODE)
      AND DET_FACTOR_TEMPL_CODE = 'STCC')

RECOMMENDED SOLUTION:
Data-Fix : Please check the file version of zxtaxdefmigb.pls and zxtaxhiermigb.pls

You can use the following query to get the file versions.

SELECT text FROM all_source
WHERE name in ('ZX_MIGRATE_TAX_DEF','ZX_MIGRATE_TAX_DEFAULT_HIER')
AND line = 2;

If the version of zxtaxdefmigb.pls is less than 120.124.12010000.20 and if the version of zxtaxhiermigb.pls is less than 120.40.12010000.8
apply following patches

zxtaxdefmigb.pls -> 13963357:R12.ZX.B
zxtaxhiermigb.pls -> 14789335:R12.ZX.B

Post patch application, run the setup upgrade script

If the versions are already higher, please run the setup upgrade script only(zx_mig_missing_setup.sql).

For all customers on 12.2 i.e., 12.2.0, 12.2.1, 12.2.2, 12.2.3, 12.2.4, 12.2.5
customers must uptake patch 22246052:R12.ZX.C and then run zx_mig_missing_setup.sql else, zx_mig_missing_setup.sql will fail

This patch will be the only additional step for 12.2 customers and no impact for 12.1 customers

For the next round of R12 upgrade, customer can apply the above mentioned patches in preinstall mode.

If still the issue is not resolved, please log an SR with Oracle Support.

Ask Customer to apply Preinstall Patch: 13963357 and Patch:14789335 to prevent the issue during next upgrade

For R12.ZX.C Please check note 1594893.1

Section 21: Can Not Update Application Tax Options After Upgrading To R12 from 11i

DESCRIPTION:
Customer is Unable to update Application Tax Options for Migrated cases.This issue happens because no defaulting hierarchy setup was present in 11i for all integrated applications.

IDENTIFICATION SQL
------------------
select org_id,application_id,record_type_code,USE_TAX_CLASSIFICATION_FLAG from ZX_PRODUCT_OPTIONS_ALL
where nvl(use_tax_classification_flag,'N')!='Y'
and record_type_code='MIGRATED'
and application_id in ( 200,201,222,275,401,660);

RECOMMENDED SOLUTION:
Data-Fix : Please apply the following Data Fix.This is already approved by Development. So no need to log an SR for this.

update ZX_PRODUCT_OPTIONS_ALL
SET Use_Tax_Classification_Flag='Y'
WHERE Application_Id = &application_id--Provide the Application_id for which you want to correct the issue AND Record_Type_Code='MIGRATED'
AND Org_Id = &org_id;--Provide the Org_id for which you want to correct the issue

commit;

Please consider the following on your research:
If application tax option record is not active, the defaulting cannot work correctly.
If it is a migrated record and customer did not setup any hierarchy in 11i, it will get created as an inactive record in R12. In this case use_tax_classification_flag will be NULL.

Section 22: Verifying the Tax Reporting Codes Set up not Upgraded

DESCRIPTION:

Verify that all the reporting codes are created with the country codes corresponding to the European Union member states in which the suppliers are located whose invoices are to be reported in the Intra EU Audit Trail report along with the member state in which the organization is located.

IDENTIFICATION SQL
------------------
SELECT REPORTING_CODE,
       EFFECTIVE_FROM
FROM (
       SELECT DISTINCT FIN_SYS_PARAM.VAT_COUNTRY_CODE REPORTING_CODE,
                       REPORT_TYPES.EFFECTIVE_FROM EFFECTIVE_FROM,
                       REPORT_TYPES.REPORTING_TYPE_ID REPORTING_TYPE_ID
       FROM FINANCIALS_SYSTEM_PARAMS_ALL FIN_SYS_PARAM,
            ZX_REPORTING_TYPES_B REPORT_TYPES
       WHERE REPORT_TYPES.REPORTING_TYPE_CODE = 'MEMBER STATE'
       AND FIN_SYS_PARAM.VAT_COUNTRY_CODE IS NOT NULL
       AND NOT EXISTS (
                       SELECT 1
                       FROM ZX_REPORTING_CODES_B
                       WHERE REPORTING_TYPE_ID = REPORT_TYPES.REPORTING_TYPE_ID
                       AND FIN_SYS_PARAM.VAT_COUNTRY_CODE = REPORTING_CODE_CHAR_VALUE
                      )
     );

RECOMMENDED SOLUTION:
Data-Fix :

The above query should return no records which means that member states corresponding to each organization in European Union that Customer had in 11i instance have been created. Once Verified if there are still some member states missing for which Customer has invoices belong to this supplier location, then customer need to manually create the reporting codes. Ideally Customer should create Reporting Codes corresponding to all the Countries located in the European Union to not face any issue later when any new supplier invoice is created.

Steps to Create Reporting Codes manually:

Login into the application and follow the below Navigation >

Tax Managers Responsibility -> Defaults and Controls -> Tax Reporting Types

Search for the Reporting Type 'MEMBER STATE' and click on update icon

Click on ‘Add Another Row’ button in the Tax Reporting Codes section

Then Enter the Country Code in 'Reporting Code' and 'Description', Date in 'Effective From'

Click on Apply and Save.

commit;

Section 23: Unable to Validate Historical Invoices after R12 Upgrade due to Data mismatch of TIPV amount between AP and ZX Entities

DESCRIPTION:

Identify invoices where distribution amount in zx_rec_nrec_dist does not match with corresponding migrated tax distribution in ap_invoice_distributions_all. This also identifies invoice where tax amount on summary lines does not match with corresponding migrated tax line in ZX_Lines.

IDENTIFICATION SQL
------------------
SELECT /*+ leading(ap,zx,apd) parallel(ap) parallel(zx) parallel(apd)
               use_nl(zx,apd) index(apd AP_INVOICE_DISTRIBUTIONS_N29) */
               DISTINCT ap.invoice_Id TRX_ID
          FROM zx_rec_nrec_dist zx,
               ap_invoices_all ap,
               ap_invoice_distributions_all apd
         WHERE NVL(ap.historical_flag,'N') = 'Y'
           AND ap.cancelled_date IS NULL
           AND apd.invoice_id = ap.invoice_id
           AND apd.detail_tax_dist_id = zx.rec_nrec_tax_dist_id
           AND zx.trx_id = ap.invoice_id
           AND zx.application_id = 200
           AND zx.entity_code = 'AP_INVOICES'
           AND zx.event_class_code IN ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
           AND zx.internal_organization_id = ap.org_id
         GROUP BY ap.invoice_id, apd.detail_tax_dist_id, zx.rec_nrec_tax_amt
        HAVING zx.rec_nrec_tax_amt <> sum(apd.amount)
         UNION
        SELECT /*+ leading(ap,zxs,zxl) parallel(ap) parallel(zxs) parallel(zxl)
                use_nl(zxs,zxl) index(zxl,ZX_LINES_N2) */
               DISTINCT ap.invoice_Id TRX_ID
          FROM zx_lines_summary zxs,
               ap_invoices_all ap,
               zx_lines zxl
         WHERE NVL(ap.historical_flag,'N') = 'Y'
           AND ap.cancelled_date IS NULL
           AND zxs.trx_id = ap.invoice_id
           AND zxs.application_id = 200
           AND zxs.entity_code = 'AP_INVOICES'
           AND zxs.event_class_code IN ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
           AND zxs.internal_organization_id = ap.org_id
           AND zxl.application_id = zxs.application_id
           AND zxl.entity_code = zxs.entity_code
           AND zxl.event_class_code = zxs.event_class_code
           AND zxl.trx_id = zxs.trx_id
           AND zxl.summary_tax_line_id = zxs.summary_tax_line_id
         GROUP BY ap.invoice_Id, zxl.summary_tax_line_id, zxs.tax_amt
        HAVING zxs.tax_amt <> sum(zxl.tax_amt)

RECOMMENDED SOLUTION:

Data-Fix : Please apply the GDF Patch: 13802001 to resolve the Issue.

Section 24: Invoice validation/Tax Calculation fails with error ‘Cannot update AP_INVOICE_LINES_ALL.AMOUNT to NULL’

DESCRIPTION:
There exist a non-cancelled tax line with NULL value in TAX_AMT column of table ZX_LINES which is allocated to a trx line that either does not exist in ZX_LINES_DET_FACTORS or is discarded

IDENTIFICATION SQL
------------------
SELECT tax_line_id
    FROM zx_lines zl
   WHERE zl.trx_id = &INVOICE_ID--Provide the Invoice_id
     AND zl.application_id = 200
     AND zl.entity_code = 'AP_INVOICES'
     AND zl.event_class_code IN ('STANDARD INVOICES','EXPENSE REPORTS','PREPAYMENT INVOICES')
     AND NOT EXISTS (SELECT 1 FROM zx_lines_det_factors zldf
                      WHERE zldf.trx_id = zl.trx_id
                        AND zldf.application_id = zl.application_id
                        AND zldf.entity_code = zl.entity_code
                        AND zldf.event_class_code = zl.event_class_code
                        AND zldf.trx_line_id = zl.trx_line_id
                        AND NVL(zldf.trx_level_type,'X') = NVL(zl.trx_level_type,'X'))
     AND NOT EXISTS (SELECT 1 FROM zx_rec_nrec_dist zd
                      WHERE zd.trx_id = zl.trx_id
                        AND zd.application_id = zl.application_id
                        AND zd.entity_code = zl.entity_code
                        AND zd.event_class_code = zl.event_class_code
                        AND zd.tax_line_id = zl.tax_line_id)
   UNION
SELECT tax_line_id
    FROM zx_lines zl
   WHERE zl.trx_id = &INVOICE_ID--Provide the Invoice_id
     AND zl.application_id = 200
     AND zl.entity_code = 'AP_INVOICES'
     AND zl.event_class_code IN ('STANDARD INVOICES','EXPENSE REPORTS','PREPAYMENT INVOICES')
     AND zl.tax_amt IS NULL
     AND EXISTS (SELECT 1 FROM ap_invoice_lines_all ail
                  WHERE ail.invoice_id = zl.trx_id
                    AND ail.line_number = zl.trx_line_id
                    AND ail.line_type_lookup_code <> 'TAX'
                    AND (NVL(ail.discarded_flag,'N') = 'Y' OR
                         NVL(ail.cancelled_flag,'N') = 'Y'))
     AND NOT EXISTS (SELECT 1 FROM zx_rec_nrec_dist zd
                      WHERE zd.trx_id = zl.trx_id
                        AND zd.application_id = zl.application_id
                        AND zd.entity_code = zl.entity_code
                        AND zd.event_class_code = zl.event_class_code
                        AND zd.tax_line_id = zl.tax_line_id);

RECOMMENDED SOLUTION:
Data-Fix : Log a Service Request to obtain Datafix from support .

RCA Patch for this issue is Patch:9698016 :R12.AP.A or Patch:9698016 :R12.AP.B

Section 25: Associations created for Member State Set up at the party Tax profile level by user sometimes are not populated with Reporting Code unique identifier causing issues with Intra EU Audit trail report

DESCRIPTION:

Associations created for Member State Set up at the party Tax profile level by user sometimes are not populated with Reporting Code unique identifier causing issues with Intra EU Audit trail report

IDENTIFICATION SQL
------------------

Select * from ZX_REPORT_CODES_ASSOC ASSOC
WHERE ASSOC.ENTITY_CODE              = 'ZX_PARTY_TAX_PROFILE'
AND ASSOC.REPORTING_CODE_ID         IS NULL
AND ASSOC.REPORTING_CODE_CHAR_VALUE IS NOT NULL
AND ASSOC.REPORTING_TYPE_ID         IN
      (
      SELECT REPORTING_TYPE_ID
        FROM ZX_REPORTING_TYPES_B TYPES
       WHERE TYPES.REPORTING_TYPE_CODE = 'MEMBER STATE'
      );

RECOMMENDED SOLUTION:

Data-Fix : Please apply the following Data Fix.This is already approved by Development. So no need to log an SR for this.

UPDATE ZX_REPORT_CODES_ASSOC ASSOC

SET REPORTING_CODE_ID =

(

SELECT REPORTING_CODE_ID

FROM ZX_REPORTING_CODES_B CODES,

ZX_REPORTING_TYPES_B type

WHERE type.REPORTING_TYPE_CODE = 'MEMBER STATE'

AND CODES.REPORTING_TYPE_ID = type.REPORTING_TYPE_ID

AND CODES.REPORTING_CODE_CHAR_VALUE = ASSOC.REPORTING_CODE_CHAR_VALUE

)

WHERE ASSOC.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'

AND ASSOC.REPORTING_CODE_ID IS NULL

AND ASSOC.REPORTING_CODE_CHAR_VALUE IS NOT NULL

AND ASSOC.REPORTING_TYPE_ID IN

(

SELECT REPORTING_TYPE_ID

FROM ZX_REPORTING_TYPES_B TYPES

WHERE TYPES.REPORTING_TYPE_CODE = 'MEMBER STATE'

);

commit;

Section 26: Associations created for Member State Set up at the party Tax profile level during upgrade are not being visible in the Reporting Code tab under Party tax profile for the Legal Establishment causing issues with Intra EU Audit trail report.

DESCRIPTION:

Associations created for Member State Set up at the party Tax profile level during upgrade are not being visible in the Reporting Code tab under Party tax profile for the Legal Establishment causing issues with Intra EU Audit trail report.

IDENTIFICATION SQL
------------------
Select * from ZX_REPORT_CODES_ASSOC
WHERE ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
AND REPORTING_TYPE_ID IN (SELECT REPORTING_TYPE_ID
FROM ZX_REPORTING_TYPES_B
WHERE REPORTING_TYPE_CODE = 'MEMBER STATE')
AND REPORTING_CODE_CHAR_VALUE IS NULL
AND REPORTING_CODE_ID IS NOT NULL;

RECOMMENDED SOLUTION:

Data-Fix : Please apply the following Data Fix.This is already approved by Development. So no need to log an SR for this.

UPDATE ZX_REPORT_CODES_ASSOC ASSOC

SET REPORTING_CODE_CHAR_VALUE = (

SELECT REPORTING_CODE_CHAR_VALUE

FROM ZX_REPORTING_CODES_B CODE

WHERE CODE.REPORTING_TYPE_ID = ASSOC.REPORTING_TYPE_ID

AND CODE.REPORTING_CODE_ID = ASSOC.REPORTING_CODE_ID

)

WHERE ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'

AND REPORTING_TYPE_ID IN (SELECT REPORTING_TYPE_ID

FROM ZX_REPORTING_TYPES_B

WHERE REPORTING_TYPE_CODE = 'MEMBER STATE')

AND REPORTING_CODE_CHAR_VALUE IS NULL

AND REPORTING_CODE_ID IS NOT NULL;

Commit;

Ask Customer to apply Preinstall Patch: 14528148 to prevent the issue during next upgrade.

Section 27: Migrated Tax Rate cannot be manually entered in Tax Details Window

DESCRIPTION:
User is unable to manually enter the Tax Rate in Tax Details Window of Invoice Workbench

IDENTIFICATION SQL
------------------
select tax_status_code,tax_status_id,tax_regime_code,tax from zx_status_b
where record_type_code='MIGRATED'
And NVL(ALLOW_RATE_OVERRIDE_FLAG,'N') <> 'Y'
order by tax_status_id,tax_regime_code,tax

RECOMMENDED SOLUTION:
Data-Fix : Please apply the following Data Fix.This is already approved by Development. So no need to log an SR for this.

UPDATE ZX.ZX_STATUS_B
SET    ALLOW_RATE_OVERRIDE_FLAG = 'Y'
WHERE TAX_REGIME_CODE = '&Tax_Regime_Code'
AND    TAX=’&TAX’
AND Tax_status_id=&Tax_status_id
AND RECORD_TYPE_CODE='MIGRATED'
AND    NVL(ALLOW_RATE_OVERRIDE_FLAG,'N') <> 'Y';

commit;

Please Note that 'OFFSET' type Tax cannot be entered manually in Tax Details window.

If user is still not able to enter a manual tax line, verify the "Allow Entry of Manual Tax Lines" in TAX / Configuration Owner Tax Option Level

Section 28: User can not choose input tax classification codes in Supplier Site Tax Classifications LOV after updating Application Tax Options in R12

DESCRIPTION:
User is unable to choose input tax classification in supplier Site

IDENTIFICATION SQL
------------------
select org_id,application_id,record_type_code,tax_method_code,object_version_number,def_option_hier_1_code,def_option_hier_2_code,def_option_hier_3_code,
def_option_hier_4_code,def_option_hier_5_code,def_option_hier_6_code,def_option_hier_7_code
from zx_product_options_all
where tax_method_code = 'EBTAX'
AND application_id in (200, 201);

RECOMMENDED SOLUTION:

Data-Fix : Please apply the following Data Fix.This is already approved by Development. So no need to log an SR for this.

UPDATE ZX.ZX_PRODUCT_OPTIONS_ALL
SET TAX_METHOD_CODE=Null,
object_version_number=1684568900
WHERE Application_id=
AND org_id=
AND Tax_Method_Code is Not Null;

commit;

Please follow the below action plan before you update the "Application Tax Options" next time.

If the following file version of is less than mentioned apply patch: 16845689:R12.ZX.B
$JAVA_TOP/oracle/apps/taxintegration/taxevent/server/MaintainTaxOptionsVOImpl.class 120.5.12010000.4

If any of the following file versions is lower than mentioned apply patch: 24391640:R12.ZX.B
$JAVA_TOP/oracle/apps/zx/lov/webui/PtpTaxClassifLovCO.class 120.0.12010000.2
$JAVA_TOP/oracle/apps/zx/lov/server/GetInputClassifOptionsLovVO.xml120.7.12010000.3
$JAVA_TOP/oracle/apps/zx/lov/server/GetInputClassifOptionsLovVOImpl.class120.1.12010000.2

Section 29: Effective Dates of Jurisdictions are not in sync with Rates after Setup Migration

DESCRIPTION:

Identify Effective Dates which are not in sync between Jurisdictions, Regime to Rate and Subscriptions

IDENTIFICATION SQL
------------------

select RATES.TAX_RATE_CODE, RATES.CONTENT_OWNER_ID, RATES.ACTIVE_FLAG,
to_char(RATES.EFFECTIVE_FROM, 'dd-mm-yyyy'), RATES.TAX_JURISDICTION_CODE, RATES.TAX_CLASS,
RATES.RECOVERY_TYPE_CODE, to_char(jur.effective_from, 'dd-mm-yyyy')
from zx_jurisdictions_b jur, zx_rates_b_tmp rates
where jur.record_type_code = 'MIGRATED'
and rates.record_type_code = 'MIGRATED'
and rates.tax_jurisdiction_code = jur.tax_jurisdiction_code
and rates.tax_regime_code = jur.tax_regime_code
and rates.tax = jur.tax
and jur.effective_from > rates.effective_from
and rates.effective_from < TO_DATE('01-01-1952','DD-MM-YYYY')
and rates.content_owner_id = -99
order by rates.tax_regime_code, RATES.CONTENT_OWNER_ID, RATES.ACTIVE_FLAG,
RATES.EFFECTIVE_FROM, RATES.TAX_JURISDICTION_CODE, RATES.TAX_CLASS,
RATES.RECOVERY_TYPE_CODE;

RECOMMENDED SOLUTION:

Data-Fix : Please apply the GDF Patch: 16932979 to resolve the Issue.

Section 30: Set Auto_Tax_Calc_Flag at Supplier Site Level for Non Employee type Supplier

DESCRIPTION:
Tax not calculating for the following supplier with Non EMPLOYEE type supplier because AUTO_TAX_CALC_FLAG is NO at supplier site level.

IDENTIFICATION SQL
------------------
SELECT APS.VENDOR_NAME,
       APS.VENDOR_ID,
       APSS.VENDOR_SITE_CODE,
       APSS.VENDOR_SITE_ID,
       APSS.AUTO_TAX_CALC_FLAG
FROM AP_SUPPLIER_SITES_ALL APSS,
       AP_SUPPLIERS APS
WHERE APS.VENDOR_ID = APSS.VENDOR_ID
AND APSS.AUTO_TAX_CALC_FLAG='N'
AND APSS.AUTO_TAX_CALC_FLAG IS NOT NULL
AND APS.EMPLOYEE_ID IS NULL
AND APS.VENDOR_ID = &SUPPLIER_ID;

RECOMMENDED SOLUTION:
Please apply the following solutions.

Update Calculate Tax as "Yes" in Supplier Site.

Steps to enable tax calculation for Supplier Site

Responsibility = Payables
Navigation = Supplier > Inquiry
Enter Supplier name + Go
On Left side, select 'Terms and Control > Tax and Reporting'
On 'Supplier Sites' section, select the site and clicking on 'Update Transaction Tax' link
Look for the associate 'Operating Unit' set the drop down 'Calculate Tax' = "Yes". Apply the change

Section 31: DEFAULT_FLG_EFF_TO AND EFF_TO VALUES IN ZX_RATES_B TABLE HAVING INCORRECT VALUES

DESCRIPTION:
DEFAULT_FLG_EFF_TO AND EFF_TO VALUES IN ZX_RATES_B TABLE HAVING INCORRECT VALUES

1)
->Tax calculation results in below error on PO when NEED-BY-DATE falls on same Date as that of Effective TO Date
->Order Lines are stuck in awaiting shipping' status after deliveries are closed and rate is end dated due to below error
->When attempting to book a sales order the following error occurs.
->When attempting to cancel order line the following error occurs.
->When attempting to add a line to an existing order,

The system cannot determine the default tax rate for tax and tax ststus . Either specify a default tax rate code for this tax status and
date or to define appropriate rate determination rules

The system cannot find tax rate information for Tax regime: , Tax: , Tax status: , Tax rate code: and Tax Jurisdiction code: . Please contact your tax manager.
The System cannot determine the default tax rate for tax and tax status

2)
->Query a tax rate . The default rate flag is on, and default effective start date is also available Uncheck the rate flag and remove the start date from the field. Click Apply.
->Remove default effective end date and Click apply.The following error occurs.
An error message appear.
oracle.jbo.AttrSetValException: JBO-27020: Set method for attribute "DefaultFlgEffectiveTo" in RateTLEO could not be resolved.
Default Effective To - Set method for attribute \"DefaultFlgEffectiveTo\" in MaintainRatesAM.MaintainRatesVO could not be resolved.

3)
Customer is having some issues after the upgrade, for the Internet Expense tax codes. Previous IE tax codes are not enable to be listed, and if they create a new tax codes, they are also not listed from the LOV.

Identification SQLs for incorrect values in default_flg_effective_to and effective_to in zx_rates_b

------------------------------------------
A)
select * from zx_rates_b
WHERE (effective_to IS NOT NULL
AND effective_to = TRUNC(effective_to) AND effective_to <> effective_from )
OR (default_flg_effective_to IS NOT NULL
AND default_flg_effective_to = TRUNC(default_flg_effective_to)
AND default_flg_effective_to <> default_flg_effective_from)
AND ( (record_type_code = 'MIGRATED' and object_version_number <> 1) OR record_type_code != 'MIGRATED' );

B)
select * from zx_rules_b
WHERE ( effective_to IS NOT NULL
       AND effective_to = TRUNC(effective_to)
       AND effective_to <> effective_from
      )
AND ( (record_type_code = 'MIGRATED' and object_version_number <> 1) OR record_type_code != 'MIGRATED' );


C)
select * from zx_status_b
WHERE (    effective_to IS NOT NULL
       AND effective_to = TRUNC(effective_to)
       AND effective_to <> effective_from
      )
AND ( (record_type_code = 'MIGRATED' and object_version_number <> 1) OR record_type_code != 'MIGRATED' );


D)
select * from zx_jurisdictions_b
WHERE (    effective_to IS NOT NULL
       AND effective_to = TRUNC(effective_to)
       AND effective_to <> effective_from
      )
AND ( (record_type_code = 'MIGRATED' and object_version_number <> 1) OR record_type_code != 'MIGRATED' );

RECOMMENDED SOLUTION:

Data Fix: Data-Fix : Please apply the GDF Patch: 20551027 to resolve the Issue

RCA Patch for this issue is Patch 10006022: Rates, Rules, Statuses and Jurisdictions cannot be used on the day they are end dated in transactions

Section 32: OFFSET_TAX_RATE_CODE is wrongly populated in ZX_Lines

DESCRIPTION:

ZX_LINES.OFFSET_TAX_RATE_CODE was getting populated on upgraded tax lines even when supplier site had offset tax calculation disabled causing issues in EMEA
VAT Selection Based Reports and in Tax Calculation for upgraded invoices

1)

While Validating the Upgraded PO matched Invoice through Actions Button we get the following error:

Unexpected error during Invoice validation. Contact your System Administrator. Error as per the log: "-1: ORA-00001: unique constraint (ZX.ZX_DETAIL_TAX_LINES_GT_U1) "

2)

ECE Payables VAT Register shows Functional Amount as Taxable amount.For migrated invoices in JG_ZZ_VAT_TRX_DETAILS no offset line is present whereas offset_tax_rate_code is present.Because of which the report output is coming incorrect.

3)

An extra Offset tax line has been added to some of migrated invoices after migration, even though these invoices were fully validated and accounted before migration from 11i to R12. The line is of an offset tax rate that is not used after migration

IDENTIFICATION SQL
------------------

SELECT DISTINCT APPLICATION_ID,LEGAL_ENTITY_ID,SET_OF_BOOKS_ID,INVOICE_DATE,INVOICE_ID
                        FROM AP_INVOICES_ALL INV,AP_SUPPLIER_SITES_ALL APSUP
                        WHERE INV.VENDOR_SITE_ID = APSUP.VENDOR_SITE_ID
                        AND NVL(APSUP.OFFSET_TAX_FLAG,'Y') = 'N'
                        AND INV.HISTORICAL_FLAG            = 'Y'
                        AND INV.INVOICE_ID                     = p_invoice_id
                        AND EXISTS
                          (SELECT 1
                          FROM ZX_LINES ZX
                          WHERE ZX.APPLICATION_ID     = INV.APPLICATION_ID
                          AND ZX.LEGAL_ENTITY_ID      = INV.LEGAL_ENTITY_ID
                          AND ZX.LEDGER_ID            = INV.SET_OF_BOOKS_ID
                          AND ZX.TRX_DATE             = INV.INVOICE_DATE
                          AND ZX.ENTITY_CODE           = 'AP_INVOICES'
                          AND ZX.EVENT_CLASS_CODE     IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
                          AND ZX.TRX_ID               = INV.INVOICE_ID
                          AND ZX.RECORD_TYPE_CODE      = 'MIGRATED'
                          AND NVL(ZX.OFFSET_FLAG,'Y') = 'N'
                          AND ZX.OFFSET_TAX_RATE_CODE IS NOT NULL
                          )

RECOMMENDED SOLUTION:

Data-Fix : Please apply the GDF Patch: 19170382 to resolve the Issue.

RCA patch for this issue is Patch:12986875

Section 33: a.Customer is missing seeded tax classification codes in UI OR b. Customer is able to find some duplicate tax classification codes in UI

IDENTIFICATION SQL
------------------
a. Select tax_rate_code from zx_rates_b_tmp where record_type_code = 'SEEDED'
and not exists (select 1 from zx_input_classifications_v where lookup_code = tax_rate_code);

b. select tax_classification_code from zx_id_tcc_mapping_all where tax_rate_code_id in (select source_id from zx_rates_b_tmp where record_type_code = 'SEEDED')

RECOMMENDED SOLUTION:

Data-Fix : Please apply the following Development Approved Datafix for the Issue.

UPDATE zx_rates_b_tmp
SET source_id = NULL,
object_version_number = 1999309400,
last_update_date = SYSDATE
WHERE source_id IS NOT NULL
AND record_type_code = 'SEEDED';

Section 34: The HQ Establishment Registration Number populated with NULL or incorrect value on Tax Lines

IDENTIFICATION SQL
------------------
SELECT /*+ qb_name(appview) */
DISTINCT zl.application_id,
         zl.trx_id,
     ZL.TRX_NUMBER,
       zl.trx_date,
       zl.legal_entity_id
FROM ZX_LINES ZL,
   XLE_ETB_PROFILES ETB,
     ZX_PARTY_TAX_PROFILE PTP
WHERE zl.application_id IN (200,222)
AND zl.entity_code    IN ('AP_INVOICES','TRANSACTIONS')
AND zl.internal_organization_id = &p_org_Id
AND zl.trx_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
AND zl.tax_rate_id IS NOT NULL
AND ETB.legal_entity_id = zl.legal_entity_id
AND ETB.MAIN_ESTABLISHMENT_FLAG = 'Y'
AND zl.trx_date BETWEEN NVL(etb.main_effective_from, zl.trx_date)
                      AND NVL(etb.main_effective_to, zl.trx_date)
AND PTP.PARTY_ID = ETB.PARTY_ID
AND PTP.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
AND EXISTS
      ( SELECT 1
         FROM ZX_REGISTRATIONS REG
         WHERE REG.PARTY_TAX_PROFILE_ID = PTP.PARTY_TAX_PROFILE_ID
           AND ZL.tax_regime_code = reg.tax_regime_code
           AND (    NVL(zl.HQ_ESTB_REG_NUMBER,CHR(0)) <> NVL(REG.REGISTRATION_NUMBER, CHR(0))
                 OR NVL(zl.HQ_ESTB_PARTY_TAX_PROF_ID,-1) <> REG.PARTY_TAX_PROFILE_ID
               )
           AND NVL(reg.tax, zl.tax) = zl.tax
           AND NVL(reg.tax_jurisdiction_code, NVL(zl.tax_jurisdiction_code, CHR(0))) = NVL(zl.tax_jurisdiction_code, CHR(0))
           AND zl.trx_date BETWEEN reg.effective_from AND NVL(reg.effective_to, zl.trx_date)
      ) ;

RECOMMENDED SOLUTION:

Data-Fix : Please apply GDF Patch 27857755 to resolve the issue

Section 35: Invoice validation/Tax Calculation fails with error for wrong rounding rule code

DESCRIPTION:
The column ZX_PARTY_TAX_PROFILE.ROUNDING_RULE_CODE is having wrong values instead of 'UP','DOWN','NEAREST'

IDENTIFICATION SQL
------------------
SELECT /*+ parallel(zptp) */ zptp.*
FROM zx_party_tax_profile zptp
where zptp.rounding_rule_code in ('U','D','N')
and zptp.party_type_code in ('THIRD_PARTY_SITE')
and zptp.party_id in
(select assi.party_site_id
from ap_supplier_sites_all assi);

RECOMMENDED SOLUTION:
Data-Fix : Log a Service Request with Payables to obtain Datafix from support .

RCA Patch for this issue is Patch 18948930

Section 36: The VAT REGISTRATION NUMBER is Null for the following Customers and Customer Sites

DESCRIPTION:
The VAT Registration Number is not populated in Customer and Customer Sites for which Receivables Transaction is not coming in EMEA VAT Report

Identification Query For Customer:
----------------------------------

SELECT hzp.party_id "Party ID",
       hzp.party_name "Party Name"
FROM zx_party_tax_profile ptp,
hz_parties hzp,
(SELECT
    /*+ qb_name(appview) */
    reg.party_tax_profile_id,
    reg.registration_number
FROM zx_registrations reg
WHERE reg.EFFECTIVE_FROM <= to_date('&Start_Date')
AND reg.EFFECTIVE_TO     >= to_date('&End_Date')
) reg
WHERE ptp.party_id               = hzp.party_id
AND ptp.party_type_code          = 'THIRD_PARTY'
AND reg.party_tax_profile_id(+) = ptp.party_tax_profile_id
AND ptp.rep_registration_number IS NULL
AND reg.registration_number     IS NULL

Identification Query For Customer Sites:
----------------------------------------

SELECT hzs.party_site_id,
hzl.CITY,
hzl.COUNTY,
hzl.STATE,
hzl.PROVINCE,
hzl.ADDRESS1,
hzl.COUNTRY,
hzl.POSTAL_CODE
FROM zx_party_tax_profile ptp,
(SELECT
    /*+ qb_name(appview) */
    hzcs.party_site_id
FROM hz_cust_acct_sites_all hzcs
WHERE HZCS.org_id IN (&p_Org_Id)
) hzcs,
HZ_PARTY_SITES hzs,
HZ_LOCATIONS hzl,
(SELECT
    /*+ qb_name(appview) */
    reg.party_tax_profile_id,
    reg.registration_number
FROM zx_registrations reg
WHERE reg.EFFECTIVE_FROM <= to_date('&Start Date')
AND reg.EFFECTIVE_TO     >= to_date('&End date')
) reg
WHERE ptp.party_id               = hzcs.party_site_id
AND hzcs.party_site_id           = hzs.party_site_id
AND hzs.LOCATION_ID              = hzl.LOCATION_ID
AND ptp.party_type_code          = 'THIRD_PARTY_SITE'
AND reg.party_tax_profile_id(+) = ptp.party_tax_profile_id
AND ptp.rep_registration_number IS NULL
AND reg.registration_number     IS NULL;

RECOMMENDED SOLUTION:
Fix : RCA Patch for this issue is Patch 18249350

Section 37: The tax information(Tax Link ID) populated with NULL values on Receipt Application Distributions

DESCRIPTION:
The tax information(Tax Link ID) populated with NULL values on Receipt Application Distributions , thus showing wrong value in Receivables Tax Reports

Identification Query:
---------------------

SELECT DISTINCT ARCS.cash_receipt_id "Receipt ID",
ARCS.RECEIPT_NUMBER "Receipt Number",
zx_det.trx_date "Transaction Date"
FROM AR_DISTRIBUTIONS_ALL ARD,
AR_DISTRIBUTIONS_ALL ARDTAX,
RA_CUSTOMER_TRX_ALL ZX_DET,
AR_CASH_RECEIPTS_ALL ARCS,
(SELECT
    /*+ qb_name(appview) */
    app.receivable_application_id,
    app.applied_customer_trx_id,
    app.cash_receipt_id,
    APP.ORG_ID
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
WHERE app.org_id IN (&p_org_id)
AND app.status    = 'APP'
AND app.gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
AND APP.GL_POSTED_DATE IS NOT NULL
) APP
WHERE zx_det.customer_trx_id      = app.applied_customer_trx_id
AND ARcs.cash_receipt_id          = app.cash_receipt_id
AND NVL(ARcs.confirmed_flag, 'Y') = 'Y'
AND ((ard.source_type             = 'EDISC'
AND (ardtax.source_type_secondary = 'EDISC'
OR ardtax.source_type             = 'EDISC_NON_REC_TAX'))
OR (ard.source_type               = 'REC'
AND ardtax.source_type_secondary IN ('PAYMENT', 'ASSIGNMENT', 'RECONCILE', 'ASSIGNMENT_RECONCILE'))
OR (ard.source_type               = 'UNEDISC'
AND (ardtax.source_type_secondary = 'UNEDISC'
OR ardtax.source_type             = 'UNEDISC_NON_REC_TAX')))
AND ard.source_id                 = app.receivable_application_id
AND ard.source_table              = 'RA'
AND ard.source_type              IN ('EDISC', 'UNEDISC', 'REC')
AND ardtax.source_id              = app.receivable_application_id
AND ardtax.source_table           = 'RA'
AND ardtax.source_type           IN ('TAX', 'DEFERRED_TAX', 'EDISC_NON_REC_TAX', 'UNEDISC_NON_REC_TAX')
AND (ardtax.tax_link_id          IS NULL
OR ard.tax_link_id               IS NULL);

RECOMMENDED SOLUTION:
Data Fix : Log SR to get Data-Fix From AR development

Section 38: The tax information(Tax Link ID) populated with NULL values on Adjustment Distributions

DESCRIPTION:
The tax information(Tax Link ID) populated with NULL values on Adjustment Distributions , thus showing wrong value in Receivables Tax Reports

Identification Query:
---------------------

SELECT DISTINCT ADJ.adjustment_id "Adjustment ID",
ADJ.ADJUSTMENT_NUMBER "Adjustment Number",
ZX_DET.trx_date "Transaction Date"
FROM AR_DISTRIBUTIONS_ALL ARD,
AR_DISTRIBUTIONS_ALL ARDTAX,
ZX_RATES_VL ZX_RATE,
RA_CUSTOMER_TRX_ALL ZX_DET,
(SELECT
    /*+ qb_name(appview) */
    ADJ.ADJUSTMENT_NUMBER,
    ADJ.STATUS,
    ADJ.CUSTOMER_TRX_ID,
    ADJ.ADJUSTMENT_ID,
    ADJ.DOC_SEQUENCE_ID,
    ADJ.TYPE
FROM AR_ADJUSTMENTS_ALL ADJ
WHERE ADJ.STATUS = 'A'
AND ADJ.TYPE    IN ('INVOICE','CHARGES','LINE','TAX')
AND adj.org_id IN (&p_org_id)
AND adj.gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
) ADJ
WHERE ZX_DET.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND ZX_DET.COMPLETE_FLAG     = 'Y'
AND ARDTAX.SOURCE_ID         = ADJ.ADJUSTMENT_ID
AND ARDTAX.SOURCE_TABLE      = 'ADJ'
AND ARDTAX.SOURCE_TYPE      IN ('TAX','DEFERRED_TAX', 'ADJ_NON_REC_TAX','FINCHRG_NON_REC_TAX')
--AND ARDTAX.SOURCE_TYPE = 'TAX'
AND zx_rate.tax_rate_id             = ardtax.tax_code_id
AND ARD.SOURCE_ID(+)                = ARDTAX.SOURCE_ID
AND NVL(ARD.SOURCE_TABLE,'ADJ')     = 'ADJ'
AND NVL(ARD.SOURCE_TYPE,'ADJ')     IN ('ADJ','REC','FINCHRG')
AND NVL(ARD.REF_ACCOUNT_CLASS,'$') <> 'TAX'
AND ((ARDTAX.TAX_LINK_ID           IS NOT NULL
AND ARD.TAX_LINK_ID                IS NULL)
OR (ARDTAX.TAX_LINK_ID             IS NULL
AND ARD.TAX_LINK_ID                IS NOT NULL))
AND NOT EXISTS
(SELECT 1
FROM AR_DISTRIBUTIONS_ALL ARDSUB
WHERE ardsub.source_type                 = 'ADJ'
AND NVL(ardsub.REF_ACCOUNT_CLASS,'REV') <> 'TAX'
AND ardtax.REF_ACCOUNT_CLASS             = ardsub.REF_ACCOUNT_CLASS
AND ardsub.tax_link_id                   = ardtax.tax_link_id
AND ardsub.source_id                     = ardtax.source_id
AND ROWNUM                               =1
) ;

RECOMMENDED SOLUTION:
Data Fix : Log SR to get Data-Fix From AR development

Section 39: The tax information (Tax Reference IDS) populated with NULL on Accounting Tax Distributions for Payable Invoices

DESCRIPTION:
The tax information (Tax Reference IDS) populated with NULL on Accounting Tax Distributions for Payable Invoices, thus not showing correct values in Payables Tax Reports

Identification Query:
---------------------

SELECT DISTINCT zxd.trx_id "Invoice ID",
xla_ent.transaction_number "Invoice Number",
xla_event.transaction_date "Invoice Date",
gl.ledger_id "Ledger ID",
gl.name "Ledger Name"
FROM
(SELECT
    /*+ qb_name(appview) */
    DISTINCT zxd.application_id,
    zxd.trx_id
FROM zx_rec_nrec_dist zxd
WHERE zxd.application_id          = 200
AND zxd.ENTITY_CODE               = 'AP_INVOICES'
AND ZXD.EVENT_CLASS_CODE         IN ('STANDARD INVOICES', 'EXPENSE REPORTS', 'PREPAYMENT INVOICES')
AND zxd.posting_flag              = 'A'
AND zxd.internal_organization_id IN (&p_org_id)
AND zxd.gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
) zxd,
xla.XLA_TRANSACTION_ENTITIES xla_ent,
XLA_EVENTS xla_event,
XLA_AE_HEADERS xla_head,
XLA_AE_LINES xla_line,
XLA_DISTRIBUTION_LINKS xla_dist,
gl_ledgers gl
WHERE xla_ent.application_id            = zxd.application_id
AND xla_ent.entity_code                 = 'AP_INVOICES'
AND NVL(xla_ent.source_id_int_1,-99)    = zxd.trx_id
AND xla_event.entity_id                 = xla_ent.entity_id
AND xla_event.application_id            = xla_ent.application_id
AND xla_head.event_id                   = xla_event.event_id
AND xla_head.ledger_id                  = gl.ledger_id
AND xla_head.application_id             = xla_ent.application_id
AND xla_head.balance_type_code          = 'A'
AND xla_line.ae_header_id               = xla_head.ae_header_id
AND xla_line.application_id             = xla_head.application_id
AND XLA_LINE.ACCOUNTING_CLASS_CODE     IN ('RTAX','NRTAX','SELF_ASSESSED_RTAX','SELF_ASSESSED_TAX')
AND xla_dist.application_id             = xla_line.application_id
AND xla_dist.ae_header_id               = xla_line.ae_header_id
AND xla_dist.ae_line_num                = xla_line.ae_line_num
AND (( xla_dist.tax_line_ref_id        IS NULL)
OR ( XLA_DIST.TAX_REC_NREC_DIST_REF_ID IS NULL));

RECOMMENDED SOLUTION:
Data Fix : Log SR to get Data-Fix From AP development

Section 40: The Accounting Tax Distributions not created for Payable Invoices

DESCRIPTION:
The Accounting Tax Distributions not created for Payable Invoices, thus not showing correct values in Payables Tax Reports

Identification Query:
---------------------

SELECT DISTINCT xla_ent.source_id_int_1 "Invoice ID",
xla_ent.transaction_number "Invoice Number",
xla_event.transaction_date "Invoice Date",
gll.ledger_id "Ledger ID",
gll.name "Ledger Name"
FROM
( SELECT /*+ qb_name(appview) */
DISTINCT application_id, entity_code, posting_flag, trx_id
FROM zx_rec_nrec_dist
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND event_class_code IN ('STANDARD INVOICES', 'EXPENSE REPORTS', 'PREPAYMENT INVOICES')
AND posting_flag = 'A'
AND internal_organization_id IN (&p_org_id)
AND gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
) zxd,
xla.XLA_TRANSACTION_ENTITIES xla_ent,
XLA_EVENTS xla_event,
XLA_AE_HEADERS xla_head,
XLA_AE_LINES xla_line,
GL_LEDGERS gll
WHERE xla_ent.application_id = zxd.application_id
AND xla_ent.entity_code = zxd.entity_code
AND NVL(xla_ent.source_id_int_1,-99) = zxd.trx_id
AND xla_event.application_id = xla_ent.application_id
AND xla_event.entity_id = xla_ent.entity_id
AND xla_head.application_id = xla_event.application_id
AND xla_head.event_id = xla_event.event_id
AND xla_head.balance_type_code = 'A'
AND xla_line.application_id = xla_head.application_id
AND xla_line.ae_header_id = xla_head.ae_header_id
AND xla_line.accounting_class_code IN ('RTAX','NRTAX','SELF_ASSESSED_RTAX','SELF_ASSESSED_TAX')
AND gll.ledger_id = xla_head.ledger_id
AND NOT EXISTS
(SELECT 1
FROM xla_distribution_links xla_dist
WHERE xla_dist.application_id = xla_line.application_id
AND xla_dist.ae_header_id = xla_line.ae_header_id
AND xla_dist.ae_line_num = xla_line.ae_line_num
AND xla_dist.event_id = xla_head.event_id
AND xla_dist.tax_rec_nrec_dist_ref_id IN
( SELECT rec_nrec_tax_dist_id
FROM zx_rec_nrec_dist a
WHERE a.application_id = zxd.application_id
AND a.trx_id = zxd.trx_id
AND a.ENTITY_CODE = zxd.entity_code
AND a.EVENT_CLASS_CODE IN ('STANDARD INVOICES', 'EXPENSE REPORTS', 'PREPAYMENT INVOICES')
AND a.posting_flag = zxd.posting_flag
AND a.internal_organization_id IN (&p_org_id)
AND a.gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
)
);

RECOMMENDED SOLUTION:
Data Fix : Log SR to get Data-Fix From AP development

Section 41: The tax information (Tax Reference IDS) populated with NULL on Accounting Tax Distributions for Receivable Invoices

DESCRIPTION:
The tax information (Tax Reference IDS) populated with NULL on Accounting Tax Distributions for Receivable Invoices, thus not showing correct values in Receivables Tax Reports

Identification Query:
---------------------

SELECT DISTINCT ar_dist.customer_trx_id "Invoice ID",
xla_ent.transaction_number "Invoice Number",
xla_event.transaction_date "Invoice Date",
gl.ledger_id "Ledger ID",
gl.name "Ledger Name"
FROM
(SELECT
    /*+ qb_name(appview) */
    ar_dist.customer_trx_id,
    ar_dist.cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all ar_dist
WHERE ar_dist.account_class = 'TAX'
AND ar_dist.gl_posted_date IS NOT NULL
AND ar_dist.org_id         IN (&p_org_id)
AND ar_dist.gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
) ar_dist,
xla.xla_transaction_entities xla_ent,
xla_events xla_event,
xla_ae_headers xla_head,
xla_ae_lines xla_line,
xla_distribution_links xla_dist,
gl_ledgers gl
WHERE xla_ent.application_id              = 222
AND xla_ent.entity_code                   = 'TRANSACTIONS'
AND xla_ent.source_id_int_1               = ar_dist.customer_trx_id
AND xla_event.application_id              = xla_ent.application_id
AND xla_event.entity_id                   = xla_ent.entity_id
AND xla_head.application_id               = xla_event.application_id
AND xla_head.ledger_id                    = gl.ledger_id
AND xla_head.event_id                     = xla_event.event_id
AND xla_head.balance_type_code            = 'A'
AND xla_line.application_id               = xla_head.application_id
AND xla_line.ae_header_id                 = xla_head.ae_header_id
AND XLA_LINE.ACCOUNTING_CLASS_CODE       IN ('DEFERRED_TAX','TAX')
AND xla_dist.application_id               = xla_line.application_id
AND xla_dist.ae_header_id                 = xla_line.ae_header_id
AND xla_dist.ae_line_num                  = xla_line.ae_line_num
AND xla_dist.source_distribution_id_num_1 = ar_dist.cust_trx_line_gl_dist_id
AND XLA_DIST.TAX_LINE_REF_ID             IS NULL;

RECOMMENDED SOLUTION:
Data Fix : Log SR to get Data-Fix From AR development

Section 42: The Accounting Tax Distributions not created for Receivable Invoices

DESCRIPTION:
The Accounting Tax Distributions not created for Receivable Invoices, thus not showing correct values in Receivables Tax Reports

Identification Query:
---------------------

SELECT DISTINCT xla_ent.source_id_int_1 "Invoice ID",
xla_ent.transaction_number "Invoice Number",
xla_event.transaction_date "Invoice Date",
gll.ledger_id "Ledger ID",
gll.name "Ledger Name"
FROM
(SELECT /*+ qb_name(appview) */ DISTINCT customer_trx_id
FROM ra_cust_trx_line_gl_dist_all
WHERE account_class = 'TAX'
AND gl_posted_date IS NOT NULL
AND org_id IN (&p_org_id)
AND gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
) ar_dist,
xla.xla_transaction_entities xla_ent,
xla_events xla_event,
xla_ae_headers xla_head,
xla_ae_lines xla_line,
gl_ledgers gll
WHERE xla_ent.application_id = 222
AND xla_ent.entity_code = 'TRANSACTIONS'
AND xla_ent.source_id_int_1 = ar_dist.customer_trx_id
AND xla_event.application_id = xla_ent.application_id
AND xla_event.entity_id = xla_ent.entity_id
AND xla_head.application_id = xla_event.application_id
AND xla_head.event_id = xla_event.event_id
AND xla_head.balance_type_code = 'A'
AND xla_line.application_id = xla_head.application_id
AND xla_line.ae_header_id = xla_head.ae_header_id
AND xla_line.accounting_class_code IN ('DEFERRED_TAX','TAX')
AND gll.ledger_id = xla_head.ledger_id
AND NOT EXISTS
(SELECT 1
FROM xla_distribution_links xla_dist
WHERE xla_dist.ae_header_id = xla_line.ae_header_id
AND xla_dist.ae_line_num = xla_line.ae_line_num
AND xla_dist.application_id = xla_line.application_id
AND xla_dist.event_id = xla_head.event_id
AND xla_dist.source_distribution_id_num_1 IN
(SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all a
WHERE a.customer_trx_id = ar_dist.customer_trx_id
AND a.account_class = 'TAX'
AND a.gl_posted_date IS NOT NULL
AND a.org_id IN (&p_org_id)
AND a.gl_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
)
);

RECOMMENDED SOLUTION:
Data Fix : Log SR to get Data-Fix From AR development

Section 43: The Receivable Invoices with COMPLETED status are appearing with INCOMPLETE status on Tax tables

DESCRIPTION:
The Receivable Invoices with COMPLETED status are appearing with INCOMPLETE status on Tax tables, thus not showing correct values in Receivables Tax Reports

Identification Query:
---------------------
SELECT ZLDF.TRX_ID,
ZLDF.TRX_NUMBER,
zldf.trx_date,
ZLDF.EVENT_CLASS_CODE,
ZLDF.LEDGER_ID,
GL.NAME
FROM
(SELECT
    /*+ qb_name(appview) */
    zldf.trx_id,
    ZLDF.TRX_NUMBER,
    zldf.trx_date,
    zldf.entity_code,
    zldf.event_class_code,
    zldf.ledger_id,
    zldf.internal_organization_id,
    ZLDF.TAX_EVENT_TYPE_CODE,
    zldf.bill_third_pty_acct_id,
    zldf.bill_third_pty_acct_site_id,
    zldf.record_type_code
FROM zx_lines_det_factors zldf
WHERE ZLDF.APPLICATION_ID         = 222
AND ZLDF.ENTITY_CODE              = 'TRANSACTIONS'
AND ZLDF.EVENT_CLASS_CODE        IN ( 'INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zldf.internal_organization_id IN (&p_org_id)
AND zldf.trx_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
) ZLDF,
GL_LEDGERS GL
WHERE ZLDF.TAX_EVENT_TYPE_CODE NOT IN ('VALIDATE_FOR_TAX','FREEZE_FOR_TAX')
AND ZLDF.LEDGER_ID                = GL.LEDGER_ID
AND EXISTS
(SELECT 1
FROM RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.COMPLETE_FLAG = 'Y'
AND TRX.CUSTOMER_TRX_ID = ZLDF.TRX_ID
AND trx.org_id          = zldf.internal_organization_id
);

RECOMMENDED SOLUTION:
Data Fix : Apply generic data fix Patch 16726857

Section 44: The Third Party Account and Account Site information on Tax Data not matching with Receivable Invoices

DESCRIPTION:
The Third Party Account and Account Site information on Tax Data not matching with Receivable Invoices, thus not showing correct values in Receivables Tax Reports

Identification Query:
---------------------
SELECT DISTINCT ZLDF.TRX_ID,
ZLDF.TRX_NUMBER,
zldf.trx_date,
ZLDF.EVENT_CLASS_CODE,
ZLDF.LEDGER_ID,
GL.NAME
FROM RA_CUSTOMER_TRX_ALL trx,
(SELECT
    /*+ qb_name(appview) */
    zldf.trx_id,
    ZLDF.TRX_NUMBER,
    zldf.trx_date,
    zldf.entity_code,
    zldf.event_class_code,
    zldf.ledger_id,
    zldf.internal_organization_id,
    ZLDF.TAX_EVENT_TYPE_CODE,
    zldf.bill_third_pty_acct_id,
    zldf.bill_third_pty_acct_site_id,
    zldf.record_type_code
FROM zx_lines_det_factors zldf
WHERE ZLDF.APPLICATION_ID          = 222
AND ZLDF.ENTITY_CODE               = 'TRANSACTIONS'
AND ZLDF.EVENT_CLASS_CODE         IN ( 'INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zldf.internal_organization_id IN (&p_org_id)
AND zldf.trx_date BETWEEN to_date('&Start_Date') AND to_date('&End_Date')
) zldf,
GL_LEDGERS GL
WHERE ZLDF.RECORD_TYPE_CODE           = 'MIGRATED'
AND ZLDF.LEDGER_ID                    = GL.LEDGER_ID
AND trx.customer_trx_id               = zldf.trx_id
AND TRX.ORG_ID                        = ZLDF.INTERNAL_ORGANIZATION_ID
AND (trx.bill_to_customer_id         <> zldf.bill_third_pty_acct_id
OR ZLDF.BILL_THIRD_PTY_ACCT_ID       IS NULL
OR (zldf.bill_third_pty_acct_site_id IS NULL
AND TRX.BILL_TO_SITE_USE_ID          IS NOT NULL));

RECOMMENDED SOLUTION:
Data Fix : Apply generic data fix Patch 20804993

Section 45: Tax status effective_from is later than that of the rates

DESCRIPTION:
Tax status effective_from is later than that of the rates after Setup Migration

IDENTIFICATION SQL
------------------
SELECT tax_status_code,effective_from,tax,tax_regime_code
FROM ZX_STATUS_B_TMP ST
WHERE record_type_code = 'MIGRATED'
   AND EXISTS (SELECT 1
                 FROM ZX_RATES_B RATE2
                WHERE RATE2.TAX_REGIME_CODE = ST.TAX_REGIME_CODE
                  AND RATE2.TAX = ST.TAX
                  AND RATE2.TAX_STATUS_CODE = ST.TAX_STATUS_CODE
                  AND RATE2.RECORD_TYPE_CODE = ST.RECORD_TYPE_CODE
                  AND RATE2.CONTENT_OWNER_ID = ST.CONTENT_OWNER_ID
                  AND RATE2.EFFECTIVE_FROM < ST.EFFECTIVE_FROM);

RECOMMENDED SOLUTION:
Data-Fix : Log a Service Request with EBTax Team to obtain Datafix from support .

Section 46: City Tax will not getting calculated and instead gets calculated for Outside City Limits customers also after upgrade from 11i

DESCRIPTION:
City Tax will not getting calculated and instead gets calculated for Outside City Limits customers also after upgrade from 11i. This is due to wrongly populated in the ZX_JURISDICTIONS.INNER_CITY_JURISDICTION_FLAG

IDENTIFICATION SQL
------------------
SELECT TAX_JURISDICTION_ID ,
          TAX_REGIME_CODE,
          TAX,
          TAX_JURISDICTION_CODE,
          DECODE(INNER_CITY_JURISDICTION_FLAG,'Y','N','Y') INNER_CITY
     FROM ZX_JURISDICTIONS_B JUR
    WHERE TAX = 'CITY'
     AND EXISTS
          (SELECT 1
             FROM ZX_DATA_UPLOAD_INTERFACE INTER
            WHERE JUR.ZONE_GEOGRAPHY_ID   = INTER.ZONE_GEOGRAPHY_ID
             AND NVL(INNER_CITY_JURISDICTION_FLAG,'N') <>
                 DECODE(TO_CHAR(INTER.JURISDICTION_SERIAL_NUMBER),'1','Y','N')
         );

RECOMMENDED SOLUTION:
Data-Fix : Log a Service Request with EBTax Team to obtain Datafix from support .

Section 47: Incase tax is created through Latin Tax Engine,tax calculation for Intercompany invoice failing with error 'Tax rate code is not defined in Payables'

DESCRIPTION:
Incase tax is created through Latin Tax Engine,tax calculation for Intercompany invoice failing with error 'Tax rate code is not defined in Payables'.

IDENTIFICATION SQL
------------------
    SELECT tax_rate_code,
      tax_rate_id,
      TAX_REGIME_CODE,
      TAX,
      tax_class,
      record_type_code,
      creation_date
    FROM zx_rates_b
    WHERE tax_rate_code IN
      (SELECT DISTINCT tax_code
      FROM ar_vat_tax_all_b
      WHERE global_attribute_category IS NOT NULL
      AND tax_type                     = 'VAT'
      )
    AND tax_class        = 'OUTPUT'
    AND record_type_code = 'MIGRATED';

RECOMMENDED SOLUTION:
Data-Fix : Log a Service Request with EBTax Team to obtain Datafix from support .

Section 48 : Tax Calculation issues on Sales Order

Pattern 1 : User is not allowed to update the USP of the Sales order line, after the order line was ever mass updated with line(s) from other order(/s)

DESCRIPTION: User is not allowed to update the USP of the Sales order line, after the order line was ever mass updated with line(s) from other order(/s).

Tax Debug Log file shows the following messages.

"Error: A mandatory constraint to generate unique record in ZX_LINES_DET_FACTORS is violated with error code ORA-00001. Please generate FND Debug Log and APList of the invoice and log a Service Request

Error: An unexpected error has occurred. Please contact your system administrator.

Pattern 2 : Tax engine used is Latin Tax Engine and the tax is not calculated.

DESCRIPTION : Tax is not calculated when any Order line is added or Sales Order is updated but if User goes and Copies this Sales Order, the tax is correctly calculated without any issues.

Tax Debug Log file shows the following messages.

Transaction Condition Class => Condition Name: TRANSACTION_REASON and Value: COMERCIALIZACAO for Tax Category: ICMS_C does not match with values of Class Code: SALES_TRANSACTION/COMERCIALIZACAO

APP-JL-62589: There are no applicable tax categories for the tax group. Please set up Tax Category details for ICMS_C from SALES_TRANSACTION/COMERCIALIZACAO Transaction Condition Class, to match the Tax Group details for the PIS COFINS ICMS IPI Tax Category.

Exception while expanding Latin Tax Group

-20001: ORA-20001: APP-JL-62589: There are no applicable tax categories for the tax group. Please set up Tax Category details for ICMS_C from SALES_TRANSACTION/COMERCIALIZACAO Transaction Condition Class, to match the Tax Group details for the PIS COFINS ICMS IPI Tax Category.

ZX_API_PUB: ADD_MSG()+

ZX_API_PUB: ADD_MSG()-

ZX_PRODUCT_INTEGRATION_PKG.calculate_tax_lte(-)

Errored out when calculate tax.

ZX_PRODUCT_INTEGRATION_PKG: calculate_tax (-)

LTE calculate tax returned errors. Return_Status = E

IDENTIFICATION SQL
------------------
SELECT application_id, entity_code, event_class_code, trx_id, trx_line_id FROM zx_lines_det_factors
WHERE application_id = 660
AND entity_code = 'OE_ORDER_HEADERS'
AND event_class_code = 'SALES_TRANSACTION_TAX_QUOTE';

RECOMMENDED SOLUTION:
Data-Fix: Log a Service Request with EBTax Team to obtain Datafix from support.

Section 49: Unable to validate invoice due to any of the following errors from log

DESCRIPTION:
a. Total Recovery Rate is greater than 100
b. System is trying to delete self-assessed frozen tax distributions
c. System is trying to delete frozen tax distributions..

Identification Query:-
The Query is driven by invoice_id only and not generic.

SELECT application_id,entity_code,
event_class_code,TRX_ID,reverse_flag,
reversed_tax_dist_id,rec_nrec_tax_dist_id
FROM zx_rec_nrec_dist DIST
WHERE application_id = 200 AND entity_code = 'AP_INVOICES'
AND event_class_code IN ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
AND NVL(reverse_flag,'N') = 'N' AND reversed_tax_dist_id IS NOT NULL
AND EXISTS (
SELECT 1
from AP_INVOICES_ALL AI
WHERE AI.INVOICE_ID = &INVOICE_ID AND DIST.TRX_ID = AI.INVOICE_ID
AND AI.CREATION_DATE <
(SELECT MIN(pr.end_date)
FROM ad_appl_tops at,ad_applied_patches aap,ad_patch_drivers pd,ad_patch_runs pr,
ad_patch_run_bugs prb,ad_file_versions afv,ad_patch_run_bug_actions prba,
ad_files f
WHERE F.APP_SHORT_NAME = 'ZX' AND F.subdir = 'patch/115/sql'
AND F.FILENAME = 'zxifnewsrvcspubb.pls' AND f.file_id = prba.file_id
AND prba.executed_flag = 'Y' AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND pr.appl_top_id = at.appl_top_id AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id AND pd.applied_patch_id = aap.applied_patch_id
AND PRBA.PATCH_FILE_VERSION_ID = AFV.FILE_VERSION_ID AND TO_NUMBER(NVL(afv.VERSION_SEGMENT4,afv.VERSION_SEGMENT2)) >=
TO_NUMBER(DECODE(afv.VERSION_SEGMENT3,12020000,9,12010000,22,12000000,22,afv.VERSION_SEGMENT2)
)));

RECOMMENDED SOLUTION:
Data-Fix : Log a Service Request with EBTax Team to obtain Datafix from support.

Section 50: Create Accounting Ending in Error

DESCRIPTION:
"The accounted amount and entered amount for the subledger jounral entry line have different sign"

Identification Query

SELECT ai.invoice_num,ai.org_id,
ai.invoice_id,
dist.application_id,dist.entity_code,dist.event_class_code,
dist.rec_nrec_tax_dist_id,dist.reverse_flag,dist.reversed_tax_dist_id,
dist.historical_flag,dist.creation_date,dist.rec_nrec_tax_amt,dist.rec_nrec_tax_amt_tax_curr,dist.rec_nrec_tax_amt_funcl_curr
FROM ap_invoices_all ai,
(SELECT NVL(
(SELECT MIN(pr.end_date)
FROM ad_appl_tops at,
ad_applied_patches aap,ad_patch_drivers pd,ad_patch_runs pr,
ad_patch_run_bugs prb,ad_file_versions afv,ad_patch_run_bug_actions prba,
ad_files f
WHERE f.app_short_name = 'ZX'
AND f.subdir = 'patch/115/sql'
AND f.filename = 'zxdiroundtaxpkgb.pls'
AND f.file_id = prba.file_id AND prba.executed_flag = 'Y'
AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND pr.appl_top_id = at.appl_top_id
AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
AND pd.applied_patch_id = aap.applied_patch_id
AND prba.patch_file_version_id = afv.file_version_id
AND to_number(nvl(afv.version_segment4, afv.VERSION_SEGMENT2)) > =
to_number(
decode(afv.version_segment3,12020000, 13,12010000, 32,12000000, 50,afv.version_segment2)) ),sysdate) AS applied_date
FROM dual
) ptch,
zx_rec_nrec_dist dist
WHERE ai.invoice_id = &p_Invoice_Id
AND ai.creation_date < ptch.applied_date AND dist.trx_id = ai.invoice_id
AND dist.application_id = 200 AND dist.entity_code = 'AP_INVOICES'
AND dist.event_class_code IN ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
AND sign(dist.rec_nrec_tax_amt) <> sign(dist.rec_nrec_tax_amt_funcl_curr)

RECOMMENDED SOLUTION:
Data-Fix : Log a Service Request with EBTax Team to obtain Datafix from support.

Section 51: Disabling Intended Use classifications

DESCRIPTION:
"User wishes to disable the below migrated values for MEDICAL, COMMERCIAL and AGRICULTURAL from Intended Use classifications"

Identification Query

SELECT count(*)
FROM ZX_FC_CODES_B
WHERE classification_code IN ('MEDICAL', 'COMMERCIAL', 'AGRICULTURAL')
AND classification_type_code = 'INTENDED_USE'
AND effective_to IS NULL;

RECOMMENDED SOLUTION:

Data-Fix : Log a Service Request with EBTax Team to obtain Datafix from support.

Section 52: The date range of associations is out of sync with the parent entities

DESCRIPTION:

UI Error : (you get the below message in UI when updating rates)
'Effective From - Enter a date range that is within the date range of this component'
user can find the same in UI by navigating and observing that the effective_from of the reporting code association is greater than that of the parent entity

Identification Query

SELECT 'RATE' as entity, count(*) AS no_records
FROM ZX_REPORT_CODES_ASSOC rep, ZX_RATES_B_TMP rate
WHERE rep.entity_code = 'ZX_RATES'
AND rep.reporting_code_id IS NOT NULL
AND rep.entity_id = rate.tax_rate_id
AND rate.active_flag = 'Y'
AND ( rep.effective_from < rate.effective_from
OR rep.effective_from > NVL(rate.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY'))
OR NVL(rep.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY')) < rate.effective_from
OR NVL(rep.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY')) > NVL(rate.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY'))
)
UNION ALL
SELECT 'TAXES' as entity, count(*) AS no_records
FROM ZX_REPORT_CODES_ASSOC rep, ZX_TAXES_B_TMP tax
WHERE rep.entity_code = 'ZX_TAXES'
AND rep.reporting_code_id IS NOT NULL
AND rep.entity_id = tax.tax_id
AND ( rep.effective_from < tax.effective_from
OR rep.effective_from > NVL(tax.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY'))
OR NVL(rep.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY')) < tax.effective_from
OR NVL(rep.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY')) > NVL(tax.effective_to,TO_DATE('31-12-9999','DD-MM-YYYY'))
)

RECOMMENDED SOLUTION:

RCA fix: Customers on 12.1.x must uptake patch 16924194
Customers on 12.2.4 OR lower must uptake patch 16924194
The fix is already included into 12.2.5 and above versions

Data Fix: If the above query returns > 0 then log a Service Request with EBTax Team to obtain Datafix from support.

Section 53: Through UI, customer views multiple rates for the same tax rate code

DESCRIPTION:

Customer is able to see duplicate rates in tax classification LOV's where as multiple rates are not defined in the system

Identification Query

SELECT count(*)
FROM ZX_RATES_VL tl
WHERE (tax_rate_name IS NULL OR description IS NULL)
AND tl.rate_type_code <> 'RECOVERY'
AND NVL(tl.active_flag,'N') <> 'N'
AND EXISTS
(SELECT 1
FROM fnd_lookups a
WHERE a.lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
and a.lookup_code = tl.tax_rate_code
AND NVL(tl.tax_class, 'INPUT') = 'INPUT'
AND ( NVL(tl.tax_rate_name, chr(0)) <> NVL(a.meaning, chr(0))
OR
NVL(tl.description, chr(0)) <> NVL(a.description, chr(0))
)
UNION ALL
SELECT 1
FROM fnd_lookups b
WHERE b.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
and b.lookup_code = tl.tax_rate_code
AND NVL(tl.tax_class, 'INPUT') = 'OUTPUT'
AND ( NVL(tl.tax_rate_name, chr(0)) <> NVL(b.meaning, chr(0))
OR
NVL(tl.description, chr(0)) <> NVL(b.description, chr(0))
)
)

RECOMMENDED SOLUTION:

RCA fix: Customers must make sure that they enter tax rate name and description consistently
when the same rate is defined under various content owners / effectivities

Data Fix: If the above query returns > 0 then log a Service Request with EBTax Team to obtain Datafix from support.

Summary

References

NOTE:1299331.1 - R12: Invoice Validation Error: Summary Tax Line deleted by EB-Tax
NOTE:1594893.1 - Upgrade Fails On Script Zxmigratefc.sql Running Driver 6678700
BUG:21224981 - TAX RATE DESCRIPTION NOT POPULATED DURING MIGRATION TO 12.2
NOTE:1152029.1 - R12: E-Business Tax / Payables Cause and Action Plan For Frozen Tax Distributions Deleted Errors
NOTE:1396732.1 - R12: Functional Currency Issue in Tax Invoice Distributions For Cross Currency Transactions
BUG:17505118 - UPGRADE FAILS ON SCRIPT ZXMIGRATEFC.SQL RUNNING DRIVER 6678700
BUG:23475310 - PROD: NEED DATA FIX FOR SECTION 40 DOC ID 1316316.1
NOTE:1152123.1 - R12: E-Business Tax/Payables Data-Fixes: Cause and action to handle ZX_LINES_SUMMARY_U1 issue / ORA-00001

NOTE:1485465.1 - Intra EU Audit Trail Report Issues and Solutions

此文档是否有帮助?

是

否

文档详细信息


	类型:
	状态:
	上次主更新:
	上次更新:


	WHITE PAPER
	PUBLISHED
	2018-8-6
	2018-9-5

相关产品

Oracle Receivables

Oracle Payables

Oracle E-Business Tax

信息中心

Information Center: Overview of the Receipts Workbench in Oracle Receivables (AR) 11.5 and later [1362278.2]

Information Center: Month End Processing in Oracle Receivables (AR) [1370198.2]

Information Center: Overview of AutoInvoice in Oracle Receivables (AR) 11.5 and later [1137414.2]

Information Center: Overview of the Transactions Workbench in Oracle Receivables (AR) 11.5 and later [1320546.2]

Information Center: R12 Critical, Rollup and Recommended Patches for Oracle Receivables (AR) [1433375.2]

显示更多

文档引用

R12: Invoice Validation Error: Summary Tax Line deleted by EB-Tax [1299331.1]

Upgrade Fails On Script Zxmigratefc.sql Running Driver 6678700 [1594893.1]

R12: E-Business Tax / Payables Cause and Action Plan For Frozen Tax Distributions Deleted Errors [1152029.1]

R12: Functional Currency Issue in Tax Invoice Distributions For Cross Currency Transactions [1396732.1]

R12: E-Business Tax/Payables Data-Fixes: Cause and action to handle ZX_LINES_SUMMARY_U1 issue / ORA-00001 [1152123.1]

显示更多

最近查看

	R12: AP: Ebtax: How To Enter a Single or Multiple Manual Tax Line in Payables [818513.1]

	R12:EBTax: AP: Unexpected TAX Calculation Error While Invoice Validation (tax_amt from summary tax line is not 0, but the total line amount for the allocated trx lines is 0.Cannot do proration.) [2283109.1]

	Interfacing RMA/ Credit Memo while Applying to Invoice With VAT Tax Inclusive Tax Code Error - You can not apply more than the original line amount [1479785.1]

	Unable to Create Applied Credit Memo Due To Overapplication Error Even Though There Are No Applications On The Invoice. [1591407.1]

	AutoInvoice Error: You Can not Apply More Than The Original Line Amount or The total amount of your credit memo cannot exceed the balance of the debit item it is crediting [1122945.1]

显示更多

未找到您要查找的产品?

附件

b17328044 (5.48 KB)

b23130463 (9.27 KB)

zx_mig_missing_setup.sql (8.42 KB)

In this Document

Abstract

History

Details

Section 1: ZX_LINES_SUMMARY_U1 / ZX_SUMMARY_CONSTRAINT_VIOLATED / A mandatory constraint to generate unique summary tax line is violated

Section 2: Frozen Tax distributions deleted by EB-Tax / AP_FRZN_TAX_DIST_DEL / Frozen Tax distributions tax lines that need to be deleted are found on this invoice

Section 3: For Cross Currency Transactions, tax amount in functional currency is not populated for some tax distributions. This leads to incorrect accounting of the cross currency transactions.

Section 4: Summary Tax Line deleted by EB-Tax / AP_SUM_TAX_LINE_DEL / Summary tax lines that need to be deleted are found on this invoice

Section 5: Missing Reversal Tax Distributions for Tax Distributions/ Tax lines for discarded or cancelled Transaction Lines are not marked as canceled causing Invoice to be placed on Hold / Error AP_ERR_TAX_DIST_SYNC during invoice cancellation

Section 6: AP_UNFROZEN_DIST_EXIST/Unfrozen Tax Distributions exist for this Invoice

Section 7: Cancel_Flag not stamped on Summary Tax Line

Section 8: Issue with the Output of Tax Classification Code LOV

Section 9: Account based Tax Rules do not evaluate

Section 10: Import fails with error 'ZX_TRX_BIZ_FC_CODE_NOT_EXIST'

Section 11: Tax Setup not migrated

Section 12: Tax not calculating after upgrading from 11.5.9 to 12.1.1

Section 13: Default Rate Flag Incorrect For Non Recovery Based Rates

Section 14: Set Auto_Tax_Calc_Flag at Supplier Site Level for Supplier Type EMPLOYEE

Section 15: Tax Tolerance is not upgraded to Configuration Owner Tax Options In R12

Section16: Supplier's Tax Code Description is Different in 11i and R12

Section 17: Migrated Tax Lines have NULL Regime-to-Rate information in ZX_LINES

Section 18: Event Class Options Are Not Migrated For Payables

Section 19: Payables Tax Codes are not Migrated

Section 20: Tax Groups are not Migrated

Section 21: Can Not Update Application Tax Options After Upgrading To R12 from 11i

Section 22: Verifying the Tax Reporting Codes Set up not Upgraded

Section 23: Unable to Validate Historical Invoices after R12 Upgrade due to Data mismatch of TIPV amount between AP and ZX Entities

Section 24: Invoice validation/Tax Calculation fails with error ‘Cannot update AP_INVOICE_LINES_ALL.AMOUNT to NULL’

Section 25: Associations created for Member State Set up at the party Tax profile level by user sometimes are not populated with Reporting Code unique identifier causing issues with Intra EU Audit trail report

Section 26: Associations created for Member State Set up at the party Tax profile level during upgrade are not being visible in the Reporting Code tab under Party tax profile for the Legal Establishment causing issues with Intra EU Audit trail report.

Section 27: Migrated Tax Rate cannot be manually entered in Tax Details Window

Section 28: User can not choose input tax classification codes in Supplier Site Tax Classifications LOV after updating Application Tax Options in R12

Section 29: Effective Dates of Jurisdictions are not in sync with Rates after Setup Migration

Section 30: Set Auto_Tax_Calc_Flag at Supplier Site Level for Non Employee type Supplier

Section 31: DEFAULT_FLG_EFF_TO AND EFF_TO VALUES IN ZX_RATES_B TABLE HAVING INCORRECT VALUES

Section 32: OFFSET_TAX_RATE_CODE is wrongly populated in ZX_Lines

Section 33: a.Customer is missing seeded tax classification codes in UI OR b. Customer is able to find some duplicate tax classification codes in UI

Section 34: The HQ Establishment Registration Number populated with NULL or incorrect value on Tax Lines

Section 35: Invoice validation/Tax Calculation fails with error for wrong rounding rule code

Section 36: The VAT REGISTRATION NUMBER is Null for the following Customers and Customer Sites

Section 37: The tax information(Tax Link ID) populated with NULL values on Receipt Application Distributions

Section 38: The tax information(Tax Link ID) populated with NULL values on Adjustment Distributions

Section 39: The tax information (Tax Reference IDS) populated with NULL on Accounting Tax Distributions for Payable Invoices

Section 40: The Accounting Tax Distributions not created for Payable Invoices

Section 41: The tax information (Tax Reference IDS) populated with NULL on Accounting Tax Distributions for Receivable Invoices

Section 42: The Accounting Tax Distributions not created for Receivable Invoices

Section 43: The Receivable Invoices with COMPLETED status are appearing with INCOMPLETE status on Tax tables

Section 44: The Third Party Account and Account Site information on Tax Data not matching with Receivable Invoices

Section 45: Tax status effective_from is later than that of the rates

Section 46: City Tax will not getting calculated and instead gets calculated for Outside City Limits customers also after upgrade from 11i

Section 47: Incase tax is created through Latin Tax Engine,tax calculation for Intercompany invoice failing with error 'Tax rate code is not defined in Payables'

Section 48 : Tax Calculation issues on Sales Order

Section 49: Unable to validate invoice due to any of the following errors from log

Section 50: Create Accounting Ending in Error

Section 51: Disabling Intended Use classifications

Section 52: The date range of associations is out of sync with the parent entities

Section 53: Through UI, customer views multiple rates for the same tax rate code

Summary

References

Applies to:

Oracle Receivables - Version 12.0.1 and later
Oracle Payables - Version 12.0.0 and later
Oracle E-Business Tax - Version 12.0.1 and later
Information in this document applies to any platform.

Abstract

This note provides the information regarding various data corruption issues, identification queries and GDF/RCA patches to rectify the data corruption.

History

Author: ssohal,armitra
Create Date 25-Apr-2011
Update Date 29-Jul-2016

Details

Section 1: ZX_LINES_SUMMARY_U1 / ZX_SUMMARY_CONSTRAINT_VIOLATED / A mandatory constraint to generate unique summary tax line is violated

Please refer to Note 1152123.1 for complete details.

Section 2: Frozen Tax distributions deleted by EB-Tax / AP_FRZN_TAX_DIST_DEL / Frozen Tax distributions tax lines that need to be deleted are found on this invoice

Please refer to Note 1152029.1 for complete details.

Section 3: For Cross Currency Transactions, tax amount in functional currency is not populated for some tax distributions. This leads to incorrect accounting of the cross currency transactions.

Please refer to Note 1396732.1 for complete details.

Section 4: Summary Tax Line deleted by EB-Tax / AP_SUM_TAX_LINE_DEL / Summary tax lines that need to be deleted are found on this invoice

DESCRIPTION:
This error can occur because of following two reasons -

(1) There exists a summary tax line in AP but missing in ZX. Following query identifies the summary tax lines of an invoice that do not exist in ZX. If this query returns any record then extract the APList and log a bug against EB-Tax.

IDENTIFICATION SQL
------------------
SELECT ail.summary_tax_line_id
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = &INVOICE_ID
   AND ail.line_type_lookup_code = 'TAX'
   AND ail.summary_tax_line_id IS NOT NULL
   AND NOT EXISTS
         (SELECT 1 FROM zx_lines_summary zls
           WHERE zls.summary_tax_line_id = ail.summary_tax_line_id
             AND zls.trx_id = ail.invoice_id);

(2) During tax processing EB-Tax does not retain a summary tax line. If the above query does not return any record then that means the summary tax line is getting deleted during tax processing.

(2.1) Summary Tax Line gets deleted during tax processing because of mismatch in any summarization criteria column. Sometimes this cannot be predicted from the current state of data (APList). Please apply following code-fix patches and data-fixes.

RECOMMENDED SOLUTION:
Code-Fix:
For 12.0.X (R12.ZX.A) - Patch:8736358 , Patch:10296081 , Patch:10627713 , Patch:12558634 , Patch:13732606 , Patch:13946670 , Patch:14122718 , Patch:14198788
For 12.1.X (R12.ZX.B) - Patch:8877828 , Patch:10296081 , Patch:10627713 , Patch:12558634 , Patch:13732606 , Patch:13946670 , Patch:14122718 , Patch:14198788

Please note that some of the above patches are recommended to have the latest version of the source files.

Data-Fix: Patch:10095488 , Patch:12360358

For R12.2.X Please apply Patch:23728207

(2.2) Summary Tax Line gets deleted during tax processing if user does not have the latest code for handling zero amount reference tax line and cancels the zero amount reference tax line. To fix this issue customer has to first apply the data-fix to remove the tax data of the invoice and apply the following code-fix patches:

RECOMMENDED SOLUTION:
Code-Fix:
For 12.0.X (R12.ZX.A) - Patch:10184087 , Patch:13959784 , Patch:13883460
For 12.1.X (R12.ZX.B) - Patch:10184087 , Patch:13959784 , Patch:13883460

For 12.2.X (R12.ZX.C) - Patch:13883460

To get the data-fix for such problemtic invoices, please log a bug against EB-Tax (Product_Id 1087) with latest APList and FND Debug log file. OR if issue persists after applying these code-fix patches and data-fixes then extract the APList, FND Debug Log file of Online Invoice Validation and log a bug against EB-Tax.

(3) Confirm the value of the tax_amt_included_flag is not the same at the ZX_LINES level and temp table zx_detail_tax_lines_gt. You can check the values from the FND.
Try to change the Inclusive flag to NO for the specific Tax code, Otherwise please Log a service request attaching the FND debug and the tax diagnsotic setup.

Section 5: Missing Reversal Tax Distributions for Tax Distributions/ Tax lines for discarded or cancelled Transaction Lines are not marked as canceled causing Invoice to be placed on Hold / Error AP_ERR_TAX_DIST_SYNC during invoice cancellation

DESCRIPTION:

There was a code issue where the reversals of the tax distributions were not saved but the original tax distributions were marked as 'Reversed'. Because of this, DIST VARIANCE hold was placed on the invoice.
There was a code issue where when the transaction lines were discarded or transaction was cancelled, the tax amount on the tax lines without tax distributions does not change to zero and they were not marked as cancelled. Because of this, LINE VARIANCE hold was placed on the invoice.
If there is corruption at tax distributions level, cancellation of invoice fails with error AP_ERR_TAX_DIST_SYNC. In this case also, follow the under-mentioned recommended solution.

IDENTIFICATION SQLs
-------------------

-- 1.Invoices with tax lines of discarded or cancelled item lines that are not marked as canceled or do not have zero tax amount

SELECT /*+ leading(ai) parallel(ai) index(ail AP_INVOICE_LINES_U1)*/
         DISTINCT ai.invoice_id,
         ai.invoice_date,
         ai.org_id
    FROM ap_invoices_all ai,
         ap_invoice_lines_all ail,
         ap_holds_all ah
   WHERE NVL(ai.historical_flag,'N') <> 'Y'
     AND ai.cancelled_date IS NULL
     AND ah.invoice_id = ai.invoice_id
     AND ah.hold_lookup_code IN ('DIST VARIANCE','LINE VARIANCE')
     AND ah.release_lookup_code IS NULL
     AND ail.invoice_id = ai.invoice_id
     AND ail.line_type_lookup_code <> 'TAX'
     AND (NVL(ail.discarded_flag,'N') = 'Y' OR
          NVL(ail.cancelled_flag,'N') = 'Y')
     AND EXISTS
         (SELECT /*+ first_rows(1) index(zl ZX_LINES_U1) */
                 1
            FROM zx_lines zl
           WHERE zl.application_id = 200
             AND zl.entity_code = 'AP_INVOICES'
             AND zl.event_class_code IN
                  ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
             AND zl.trx_id = ai.invoice_id
             AND zl.trx_level_type = 'LINE'
             AND zl.trx_line_id = ail.line_number
             AND (NVL(zl.cancel_flag,'N') <> 'Y' OR zl.tax_amt <> 0));

-- 2.Invoices on Dist Variance or Line Variance hold where tax lines tax amount is not matching with the sum of amounts of their distributions

SELECT /*+ leading ah */ DISTINCT ai.invoice_id,

ai.invoice_date,

ai.org_id

FROM ap_invoices_all ai,

ap_holds_all ah

WHERE NVL(ai.historical_flag,'N') <> 'Y'

AND ai.cancelled_date IS NULL

AND ai.invoice_id = ah.invoice_id

AND ah.hold_lookup_code IN ('DIST VARIANCE','LINE VARIANCE')

AND ah.release_lookup_code IS NULL

AND EXISTS

(SELECT /*+ first_rows(1) leading zl index(zl ZX_LINES_N4) index(zd ZX_REC_NREC_DIST_U2) */

zd.tax_line_id,

zl.tax_amt,

SUM(zd.rec_nrec_tax_amt) tot_rec_nrec_amt

FROM zx_lines zl,

zx_rec_nrec_dist zd

WHERE zl.application_id = 200

AND zl.trx_id = ai.invoice_id

AND zd.tax_line_id = zl.tax_line_id

AND zd.application_id = zl.application_id

AND zd.entity_code = zl.entity_code

AND zd.event_class_code = zl.event_class_code

AND zd.trx_id = zl.trx_id

GROUP BY zd.tax_line_id, zl.tax_amt

HAVING zl.tax_amt <> SUM(zd.rec_nrec_tax_amt));

-- 3.Invoices on Dist Variance or Line Variance hold where amount of tax distrbutions in ZX is not matching with the amount of tax distributions in AP

SELECT /*+ leading ah */ DISTINCT ai.invoice_id,

ai.invoice_date,

ai.org_id

FROM ap_invoices_all ai,

ap_holds_all ah

WHERE NVL(ai.historical_flag,'N') <> 'Y'

AND ai.cancelled_date IS NULL

AND ai.invoice_id = ah.invoice_id

AND ah.hold_lookup_code IN ('DIST VARIANCE','LINE VARIANCE')

AND ah.release_lookup_code IS NULL

AND (EXISTS

(SELECT /*+ first_rows(1) leading aid index(zd ZX_REC_NREC_DIST_U1) */

aid.detail_tax_dist_id,

zd.rec_nrec_tax_amt,

SUM(aid.amount) ap_rec_nrec_amt

FROM ap_invoice_distributions_all aid,

zx_rec_nrec_dist zd

WHERE aid.invoice_id = ai.invoice_id

AND aid.line_type_lookup_code IN

('REC_TAX','NONREC_TAX','TRV','TIPV','TERV')

AND aid.detail_tax_dist_id IS NOT NULL

AND zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id

GROUP BY aid.detail_tax_dist_id, zd.rec_nrec_tax_amt

HAVING zd.rec_nrec_tax_amt <> SUM(aid.amount))

OR

EXISTS

(SELECT /*+ first_rows(1) leading asad index(zd ZX_REC_NREC_DIST_U1) */

1

FROM ap_self_assessed_tax_dist_all asad,

zx_rec_nrec_dist zd

WHERE asad.invoice_id = ai.invoice_id

AND asad.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')

AND asad.detail_tax_dist_id IS NOT NULL

AND zd.rec_nrec_tax_dist_id = asad.detail_tax_dist_id

AND zd.rec_nrec_tax_amt <> asad.amount));

RECOMMENDED SOLUTION:
If either of the above queries returns any record then please apply the GDF Patch 17603319:R12.ZX.A for 12.0.x or GDF Patch 17603319:R12.ZX.B for 12.1.x. For 12.2.x, please review Note 2089108.1 .

Also, it is mandatory to apply RCA Patch 19711877 to avoid this issue to re-occur for future invoices (specifically, Patch 19711877:R12.ZX.A for 12.0.x, Patch 19711877:R12.ZX.B for 12.1.x and Patch 19711877:R12.ZX.C for 12.2.x). This patch is the replacement patch of 16095365 and has the code-fix for both the issues mentioned above.

Section 6: AP_UNFROZEN_DIST_EXIST/Unfrozen Tax Distributions exist for this Invoice

DESCRIPTION:

This error occurs when there exists some unfrozen tax distributions for an Invoice. This error mostly occurs for Migrated invoices.
This error occurs with ORA-20001: APP-SQLAP-4667482: Not all tax distributions were frozen on this invoice.