R12 - XLA / FAH - After running Validate AAD program, why the XLA dynamic package becomes invalid?

vamsi kasina's picture
AttachmentSize
Image icon invalid_jlt_conditions.JPG131.53 KB
articles: 

Subledger Accounting / Financials Accounting Hub uses dynamic package to generate the journal entries.
Depending on the rules (AMB Setup and Event Model) the dynamic package will be created, when the Validate Application Accounting Definition program has been run.
The package will be generated with the naming convension XLA_{5DIGIT_APPL_ID}_AAD_{SEED_CUSTOM}_{6DIGIT_AAD_HASH}_PKG.
AAD_HASH value will be found in xla_product_rules_b.product_rule_hash_id.

   SELECT 'XLA_' || lpad(application_id,5,'0') || '_AAD_' || 
          product_rule_type_code || '_' || lpad(product_rule_hash_id,6,'0') || '_PKG'
   FROM xla_product_rules_b
   WHERE application_id = &p_appl_id
   AND amb_context_code = NVL('&amb_context_code','DEFAULT')
   AND product_rule_code = '&aad_code';
Examples for dynamic pakcages:
XLA_00555_AAD_C_011117_PKG
XLA_00200_AAD_S_000012_PKG

If any of the dynamic packages is in error, first, try to run "Validate Application Accounting Definitions" program with the parameter "Uncompiled Status Only" as No.
If the program log / output is showing some Invalid AADs or still the package is in error, then check the errors in database.
   SELECT *
   FROM dba_errors
   WHERE name LIKE '&dynmc_pkg_name';
Examples for errors:
PLS-00103: Encountered the symbol "THEN" when expecting one of the following:
PLS-00103: Encountered the symbol "NVL" when expecting one of the following:

This error may occur mainly due to two reasons:
a) Missing source, which is used in the setup.

   SELECT xc.* 
   FROM xla_conditions xc
   WHERE xc.application_id = &appl_id
   AND xc.source_code IS NOT NULL
   AND NOT EXISTS (SELECT 1 
                   FROM xla_sources_b xsb
                   WHERE xc.source_application_id = xsb.application_id
                   AND xc.source_type_code = xsb.source_type_code
                   AND xc.source_code = xsb.source_code);
b) Wrong conditions in setup.
   SELECT application_id,amb_context_code,entity_code,event_class_code
         ,accounting_line_type_code,accounting_line_code
         ,segment_rule_detail_id,description_prio_id
   FROM xla_conditions xc
   WHERE application_id = &appl_id
   GROUP BY application_id,amb_context_code,entity_code,event_class_code
           ,accounting_line_type_code,accounting_line_code
           ,segment_rule_detail_id,description_prio_id
   HAVING SUM(NVL2(source_code,1,0)) <> SUM(NVL2(logical_operator_code,1,0)) + 1;
For Journal Line Types (JLT), accounting_line_code and accounting_line_typ_code are populated.
For Account Derivation Rules (ADR), segment_rule_detail_id is populated.
   SELECT xsrd.application_id, xsrd.amb_context_code
         ,xsrd.segment_rule_code, xsrd.segment_rule_type_code
   FROM xla_seg_rule_details xsrd
   WHERE xsrd.segment_rule_detail_id = &segment_rule_detail_id;
For Journal Enry Descriptions (JED), description_prio_id is populated.
   SELECT xdp.application_id, xdp.amb_context_code
         ,xdp.description_code, xdp.description_type_code
   FROM xla_desc_priorities xdp
   WHERE xdp.description_prio_id = &description_prio_id;
For (a), check any patch available in MOS for the missing source in JLT / ADR / JED conditions. Otherwise, contact Oracle Support.
For (b), check the conditions of the JLT / ADR / JED, whether anything is wrongly entered or not.
For Ex: AND / OR is missing in this JLT conditions.

Comments

I just want to thank you for this blog it was very helpful to resolve one error that I had.

Million thanks