|
In this Document
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:
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:
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=<Provide application_id> AND org_id=<Provide 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 <Tax> and tax ststus <Tax Standard>. Either specify a default tax rate code for this tax status and date <Trx Date> or to define appropriate rate determination rules The system cannot find tax rate information for Tax regime: <Tax regime>, Tax: <Tax>, Tax status: <Tax status> , Tax rate code: <Tax rate code> and Tax Jurisdiction code: <Tax Jurisdiction code>. Please contact your tax manager. The System cannot determine the default tax rate for tax <Tax> and tax status <Tax Standard> 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. <Please note that the below values are just an example, which are copied from sample log file.> "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. <Please note that the below values are just an example, which are copied from sample log file.> 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 |
显示更多
显示更多
显示更多 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
未找到您要查找的产品?
|
|
In this Document
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:
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:
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=<Provide application_id> AND org_id=<Provide 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 <Tax> and tax ststus <Tax Standard>. Either specify a default tax rate code for this tax status and date <Trx Date> or to define appropriate rate determination rules The system cannot find tax rate information for Tax regime: <Tax regime>, Tax: <Tax>, Tax status: <Tax status> , Tax rate code: <Tax rate code> and Tax Jurisdiction code: <Tax Jurisdiction code>. Please contact your tax manager. The System cannot determine the default tax rate for tax <Tax> and tax status <Tax Standard> 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. <Please note that the below values are just an example, which are copied from sample log file.> "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. <Please note that the below values are just an example, which are copied from sample log file.> 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 |
显示更多
显示更多
显示更多 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
未找到您要查找的产品?
|
