The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p_amb_context_code||'_INSTALL_'||application_short_name
INTO l_staging_context_code
FROM fnd_application
WHERE application_id = p_application_id;
DELETE FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = l_staging_context_code;
xla_aad_loader_util_pvt.insert_errors
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_request_code => 'IMPORT');
xla_aad_loader_util_pvt.insert_errors
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_request_code => 'IMPORT');
SELECT p_amb_context_code||'_INSTALL_'||application_short_name
INTO l_staging_context_code
FROM fnd_application
WHERE application_id = p_application_id;
DELETE FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = l_staging_context_code;
/*UPDATE xla_product_rules_b
SET compile_status_code = 'R'
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code;*/
UPDATE xla_product_rules_b prd
SET compile_status_code = 'R'
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND EXISTS (select 1
from xla_aads_gt a,
XLA_PAD_INQ_LINES_FVL b
where (a.product_rule_code is null or a.product_rule_code = b.product_rule_code)
and (a.event_class_code is null or a.event_class_code = b.event_class_code)
and (a.event_type_code is null or a.event_type_code = b.event_type_code)
and (a.line_definition_code is null or a.line_definition_code = b.line_definition_code)
and (a.accounting_line_code is null or a.accounting_line_code = b.accounting_line_code)
and (a.accounting_class_code is null or a.accounting_class_code = b.accounting_class_code)
and (a.segment_rule_code is null or a.segment_rule_code = b.segment_rule_code)
and (a.description_code is null or a.description_code = b.description_code)
and (a.analytical_criterion_code is null or a.analytical_criterion_code = b.analytical_criterion_code)
and prd.product_rule_code = b.product_rule_code
)
AND EXISTS
( SELECT 1
FROM xla_acctg_method_rules amr,
gl_ledgers gl
WHERE prd.product_rule_code = amr.product_rule_code
AND prd.product_rule_type_code = amr.product_rule_type_code
AND prd.application_id = amr.application_id
AND prd.amb_context_code = amr.amb_context_code
AND amr.accounting_method_code = gl.sla_accounting_method_code
AND amr.application_id = p_application_id
AND amr.end_date_active IS NULL OR amr.end_date_active >= sysdate
);
UPDATE xla_product_rules_b prd
SET compile_status_code = 'N'
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND EXISTS
( select 1
from xla_aads_gt a,
XLA_PAD_INQ_LINES_FVL b
where (a.product_rule_code is null or a.product_rule_code = b.product_rule_code)
and (a.event_class_code is null or a.event_class_code = b.event_class_code)
and (a.event_type_code is null or a.event_type_code = b.event_type_code)
and (a.line_definition_code is null or a.line_definition_code = b.line_definition_code)
and (a.accounting_line_code is null or a.accounting_line_code = b.accounting_line_code)
and (a.accounting_class_code is null or a.accounting_class_code = b.accounting_class_code)
and (a.segment_rule_code is null or a.segment_rule_code = b.segment_rule_code)
and (a.description_code is null or a.description_code = b.description_code)
and (a.analytical_criterion_code is null or a.analytical_criterion_code = b.analytical_criterion_code)
and prd.product_rule_code = b.product_rule_code
)
AND NOT EXISTS
( SELECT 1
FROM xla_acctg_method_rules amr,
gl_ledgers gl
WHERE prd.product_rule_code = amr.product_rule_code
AND prd.product_rule_type_code = amr.product_rule_type_code
AND prd.application_id = amr.application_id
AND prd.amb_context_code = amr.amb_context_code
AND amr.accounting_method_code = gl.sla_accounting_method_code
AND amr.application_id = p_application_id
AND amr.end_date_active IS NULL OR amr.end_date_active >= sysdate
);
trace(p_msg => 'Number of Rows updated to R in product_rules_b is :'||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_PROCEDURE);
FOR l IN (SELECT * FROM xla_product_rules_b
WHERE application_id = p_application_id
AND amb_context_code = p_amb_context_code
AND compile_status_code = 'R') LOOP -- Added extra condition on compile status code
xla_amb_aad_pkg.validate_and_compile_aad
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_product_rule_type_code => l.product_rule_type_code
,p_product_rule_code => l.product_rule_code
,x_validation_status_code => l_validation_status_code
,x_compilation_status_code => l_compilation_status_code
,x_hash_id => l_hash_id);
xla_aad_loader_util_pvt.insert_errors
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_request_code => 'IMPORT');
xla_aad_loader_util_pvt.insert_errors
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_request_code => 'IMPORT');
xla_aad_loader_util_pvt.insert_errors
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_request_code => 'EXPORT');
xla_aad_loader_util_pvt.insert_errors
(p_application_id => p_application_id
,p_amb_context_code => p_amb_context_code
,p_request_code => 'EXPORT');