The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 01-Mar-05 W. Shen CHANGE THE FUNCTION insert_jlt_assignment |
| don't insert certain attributes for |
| gain/loss line TYPES |
| |
+======================================================================*/
-------------------------------------------------------------------------------
-- declaring private package variables
-------------------------------------------------------------------------------
g_creation_date DATE;
g_last_update_date DATE;
g_last_update_login INTEGER;
g_last_updated_by INTEGER;
SELECT xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.amb_context_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xld
WHERE xld.application_id = xal.application_id
AND xld.amb_context_code = xal.amb_context_code
AND xld.event_class_code = xal.event_class_code
AND xld.event_type_code = xal.event_type_code
AND xld.line_definition_owner_code = xal.line_definition_owner_code
AND xld.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code)
FOR UPDATE NOWAIT;
UPDATE xla_line_definitions_b xld
SET validation_status_code = 'N'
WHERE xld.application_id = p_application_id
AND xld.event_class_code = p_event_class_code
AND xld.validation_status_code <> 'N'
AND EXISTS (SELECT 'X'
FROM xla_line_defn_jlt_assgns xja
WHERE xld.application_id = xja.application_id
AND xld.amb_context_code = xja.amb_context_code
AND xld.event_class_code = xja.event_class_code
AND xld.event_type_code = xja.event_type_code
AND xld.line_definition_owner_code = xja.line_definition_owner_code
AND xld.line_definition_code = xja.line_definition_code);
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND xpa.validation_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xja
WHERE xja.application_id = xal.application_id
AND xja.amb_context_code = xal.amb_context_code
AND xja.event_class_code = xal.event_class_code
AND xja.event_type_code = xal.event_type_code
AND xja.line_definition_owner_code = xal.line_definition_owner_code
AND xja.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code);
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.application_id = p_application_id
AND xpr.compile_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xja
WHERE xja.application_id = xal.application_id
AND xja.amb_context_code = xal.amb_context_code
AND xja.event_class_code = xal.event_class_code
AND xja.event_type_code = xal.event_type_code
AND xja.line_definition_owner_code = xal.line_definition_owner_code
AND xja.line_definition_code = xal.line_definition_code
AND xal.application_id = xpr.application_id
AND xal.amb_context_code = xpr.amb_context_code
AND xal.product_rule_type_code = xpr.product_rule_type_code
AND xal.product_rule_code = xpr.product_rule_code);
SELECT xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.amb_context_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND xpa.accounting_required_flag = 'Y'
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xld
WHERE xld.application_id = xal.application_id
AND xld.amb_context_code = xal.amb_context_code
AND xld.event_class_code = xal.event_class_code
AND xld.event_type_code = xal.event_type_code
AND xld.line_definition_owner_code = xal.line_definition_owner_code
AND xld.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code)
FOR UPDATE NOWAIT;
UPDATE xla_line_definitions_b xld
SET validation_status_code = 'N'
WHERE xld.application_id = p_application_id
AND xld.event_class_code = p_event_class_code
AND xld.validation_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_prod_acct_headers xpa
, xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xja
WHERE xld.application_id = xja.application_id
AND xld.amb_context_code = xja.amb_context_code
AND xld.event_class_code = xja.event_class_code
AND xld.event_type_code = xja.event_type_code
AND xld.line_definition_owner_code = xja.line_definition_owner_code
AND xld.line_definition_code = xja.line_definition_code
AND xld.application_id = xal.application_id
AND xld.amb_context_code = xal.amb_context_code
AND xld.event_class_code = xal.event_class_code
AND xld.event_type_code = xal.event_type_code
AND xld.line_definition_owner_code = xal.line_definition_owner_code
AND xld.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code
AND xpa.accounting_required_flag = 'Y');
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND xpa.accounting_required_flag = 'Y'
AND xpa.validation_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xld
WHERE xld.application_id = xal.application_id
AND xld.amb_context_code = xal.amb_context_code
AND xld.event_class_code = xal.event_class_code
AND xld.event_type_code = xal.event_type_code
AND xld.line_definition_owner_code = xal.line_definition_owner_code
AND xld.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code);
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.application_id = p_application_id
AND xpr.compile_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_prod_acct_headers xpa
, xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xja
WHERE xja.application_id = xal.application_id
AND xja.amb_context_code = xal.amb_context_code
AND xja.event_class_code = xal.event_class_code
AND xja.event_type_code = xal.event_type_code
AND xja.line_definition_owner_code = xal.line_definition_owner_code
AND xja.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code
AND xpa.accounting_required_flag = 'Y'
AND xal.application_id = xpr.application_id
AND xal.amb_context_code = xpr.amb_context_code
AND xal.product_rule_type_code = xpr.product_rule_type_code
AND xal.product_rule_code = xpr.product_rule_code);
SELECT xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.amb_context_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xld
, xla_jlt_acct_attrs xja
WHERE xja.application_id = p_application_id
AND xja.event_class_code = p_event_class_code
AND xja.accounting_attribute_code = p_accounting_attribute_code
AND xja.event_class_default_flag = 'Y'
AND xja.application_id = xld.application_id
AND xja.amb_context_code = xld.amb_context_code
AND xja.event_class_code = xld.event_class_code
AND xja.accounting_line_type_code = xld.accounting_line_type_code
AND xja.accounting_line_code = xld.accounting_line_code
AND xld.application_id = xal.application_id
AND xld.amb_context_code = xal.amb_context_code
AND xld.event_class_code = xal.event_class_code
AND xld.event_type_code = xal.event_type_code
AND xld.line_definition_owner_code = xal.line_definition_owner_code
AND xld.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code)
FOR UPDATE NOWAIT;
UPDATE xla_line_definitions_b xpa
SET validation_status_code = 'N'
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND xpa.validation_status_code <> 'N'
AND EXISTS (SELECT 'X'
FROM xla_line_defn_jlt_assgns xld
, xla_jlt_acct_attrs xja
WHERE xja.application_id = p_application_id
AND xja.event_class_code = p_event_class_code
AND xja.accounting_attribute_code = p_accounting_attribute_code
AND xja.event_class_default_flag = 'Y'
AND xja.application_id = xld.application_id
AND xja.amb_context_code = xld.amb_context_code
AND xja.event_class_code = xld.event_class_code
AND xja.accounting_line_type_code = xld.accounting_line_type_code
AND xja.accounting_line_code = xld.accounting_line_code
AND xld.application_id = xpa.application_id
AND xld.amb_context_code = xpa.amb_context_code
AND xld.event_class_code = xpa.event_class_code
AND xld.event_type_code = xpa.event_type_code
AND xld.line_definition_owner_code = xpa.line_definition_owner_code
AND xld.line_definition_code = xpa.line_definition_code);
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND xpa.validation_status_code <> 'N'
AND EXISTS (SELECT 'X'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xld
, xla_jlt_acct_attrs xja
WHERE xja.application_id = p_application_id
AND xja.event_class_code = p_event_class_code
AND xja.accounting_attribute_code = p_accounting_attribute_code
AND xja.event_class_default_flag = 'Y'
AND xja.application_id = xld.application_id
AND xja.amb_context_code = xld.amb_context_code
AND xja.event_class_code = xld.event_class_code
AND xja.accounting_line_type_code = xld.accounting_line_type_code
AND xja.accounting_line_code = xld.accounting_line_code
AND xld.application_id = xal.application_id
AND xld.amb_context_code = xal.amb_context_code
AND xld.event_class_code = xal.event_class_code
AND xld.event_type_code = xal.event_type_code
AND xld.line_definition_owner_code = xal.line_definition_owner_code
AND xld.line_definition_code = xal.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code);
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.application_id = p_application_id
AND xpr.compile_status_code <> 'N'
AND EXISTS (SELECT 'X'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xld
, xla_jlt_acct_attrs xja
WHERE xja.application_id = p_application_id
AND xja.event_class_code = p_event_class_code
AND xja.accounting_attribute_code = p_accounting_attribute_code
AND xja.event_class_default_flag = 'Y'
AND xja.application_id = xld.application_id
AND xja.amb_context_code = xld.amb_context_code
AND xja.event_class_code = xld.event_class_code
AND xja.accounting_line_type_code = xld.accounting_line_type_code
AND xja.accounting_line_code = xld.accounting_line_code
AND xld.application_id = xal.application_id
AND xld.amb_context_code = xal.amb_context_code
AND xld.event_class_code = xal.event_class_code
AND xld.event_type_code = xal.event_type_code
AND xld.line_definition_owner_code = xal.line_definition_owner_code
AND xld.line_definition_code = xal.line_definition_code
AND xal.application_id = xpr.application_id
AND xal.amb_context_code = xpr.amb_context_code
AND xal.product_rule_type_code = xpr.product_rule_type_code
AND xal.product_rule_code = xpr.product_rule_code);
SELECT xpa.application_id
,xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.amb_context_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
, xla_aad_hdr_acct_attrs xah
WHERE xpa.application_id = xah.application_id
AND xpa.amb_context_code = xah.amb_context_code
AND xpa.event_class_code = xah.event_class_code
AND xpa.event_type_code = xah.event_type_code
AND xpa.product_rule_type_code = xah.product_rule_type_code
AND xpa.product_rule_code = xah.product_rule_code
AND xah.event_class_default_flag = 'Y'
AND xpa.accounting_required_flag = 'Y'
FOR UPDATE NOWAIT;
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.accounting_required_flag = 'Y'
AND EXISTS (SELECT 'x'
FROM xla_aad_hdr_acct_attrs xah
WHERE xpa.application_id = xah.application_id
AND xpa.amb_context_code = xah.amb_context_code
AND xpa.event_class_code = xah.event_class_code
AND xpa.event_type_code = xah.event_type_code
AND xpa.product_rule_type_code = xah.product_rule_type_code
AND xpa.product_rule_code = xah.product_rule_code
AND xah.event_class_default_flag = 'Y');
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.compile_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_prod_acct_headers xpa
, xla_aad_hdr_acct_attrs xah
WHERE xpa.application_id = xah.application_id
AND xpa.amb_context_code = xah.amb_context_code
AND xpa.event_class_code = xah.event_class_code
AND xpa.event_type_code = xah.event_type_code
AND xpa.product_rule_type_code = xah.product_rule_type_code
AND xpa.product_rule_code = xah.product_rule_code
AND xah.event_class_default_flag = 'Y'
AND xpr.application_id = xpa.application_id
AND xpr.amb_context_code = xpa.amb_context_code
AND xpr.product_rule_type_code = xpa.product_rule_type_code
AND xpr.product_rule_code = xpa.product_rule_code
AND xpa.accounting_required_flag = 'Y');
SELECT xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.amb_context_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xja
, xla_jlt_acct_attrs xaa
WHERE xaa.application_id = p_application_id
AND xaa.event_class_code = p_event_class_code
AND xaa.accounting_attribute_code = p_accounting_attribute_code
AND xaa.event_class_default_flag = NVL(p_event_class_default_flag,
xaa.event_class_default_flag)
AND xaa.source_application_id = p_source_application_id
AND xaa.source_type_code = p_source_type_code
AND xaa.source_code = p_source_code
AND xja.application_id = xaa.application_id
AND xja.amb_context_code = xaa.amb_context_code
AND xja.event_class_code = xaa.event_class_code
AND xja.accounting_line_type_code = xaa.accounting_line_type_code
AND xja.accounting_line_code = xaa.accounting_line_code
AND xal.application_id = xja.application_id
AND xal.amb_context_code = xja.amb_context_code
AND xal.event_class_code = xja.event_class_code
AND xal.event_type_code = xja.event_type_code
AND xal.line_definition_owner_code = xja.line_definition_owner_code
AND xal.line_definition_code = xja.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code)
FOR UPDATE NOWAIT;
UPDATE xla_line_definitions_b xld
SET validation_status_code = 'N'
WHERE xld.application_id = p_application_id
AND xld.event_class_code = p_event_class_code
AND xld.validation_status_code <> 'N'
AND EXISTS (SELECT 'X'
FROM xla_line_defn_jlt_assgns xja
, xla_jlt_acct_attrs xaa
WHERE xaa.application_id = p_application_id
AND xaa.event_class_code = p_event_class_code
AND xaa.accounting_attribute_code = p_accounting_attribute_code
AND xaa.event_class_default_flag = NVL(p_event_class_default_flag,
xaa.event_class_default_flag)
AND xaa.source_application_id = p_source_application_id
AND xaa.source_type_code = p_source_type_code
AND xaa.source_code = p_source_code
AND xja.application_id = xaa.application_id
AND xja.amb_context_code = xaa.amb_context_code
AND xja.event_class_code = xaa.event_class_code
AND xja.accounting_line_type_code = xaa.accounting_line_type_code
AND xja.accounting_line_code = xaa.accounting_line_code
AND xld.application_id = xja.application_id
AND xld.amb_context_code = xja.amb_context_code
AND xld.event_class_code = xja.event_class_code
AND xld.event_type_code = xja.event_type_code
AND xld.line_definition_owner_code = xja.line_definition_owner_code
AND xld.line_definition_code = xja.line_definition_code);
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND xpa.validation_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xja
, xla_jlt_acct_attrs xaa
WHERE xaa.application_id = p_application_id
AND xaa.event_class_code = p_event_class_code
AND xaa.accounting_attribute_code = p_accounting_attribute_code
AND xaa.event_class_default_flag = NVL(p_event_class_default_flag,
xaa.event_class_default_flag)
AND xaa.source_application_id = p_source_application_id
AND xaa.source_type_code = p_source_type_code
AND xaa.source_code = p_source_code
AND xja.application_id = xaa.application_id
AND xja.amb_context_code = xaa.amb_context_code
AND xja.event_class_code = xaa.event_class_code
AND xja.accounting_line_type_code = xaa.accounting_line_type_code
AND xja.accounting_line_code = xaa.accounting_line_code
AND xal.application_id = xja.application_id
AND xal.amb_context_code = xja.amb_context_code
AND xal.event_class_code = xja.event_class_code
AND xal.event_type_code = xja.event_type_code
AND xal.line_definition_owner_code = xja.line_definition_owner_code
AND xal.line_definition_code = xja.line_definition_code
AND xal.application_id = xpa.application_id
AND xal.amb_context_code = xpa.amb_context_code
AND xal.event_class_code = xpa.event_class_code
AND xal.event_type_code = xpa.event_type_code
AND xal.product_rule_type_code = xpa.product_rule_type_code
AND xal.product_rule_code = xpa.product_rule_code);
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.application_id = p_application_id
AND xpr.compile_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_aad_line_defn_assgns xal
, xla_line_defn_jlt_assgns xja
, xla_jlt_acct_attrs xaa
WHERE xaa.application_id = p_application_id
AND xaa.event_class_code = p_event_class_code
AND xaa.accounting_attribute_code = p_accounting_attribute_code
AND xaa.event_class_default_flag = NVL(p_event_class_default_flag,
xaa.event_class_default_flag)
AND xaa.source_application_id = p_source_application_id
AND xaa.source_type_code = p_source_type_code
AND xaa.source_code = p_source_code
AND xja.application_id = xaa.application_id
AND xja.amb_context_code = xaa.amb_context_code
AND xja.event_class_code = xaa.event_class_code
AND xja.accounting_line_type_code = xaa.accounting_line_type_code
AND xja.accounting_line_code = xaa.accounting_line_code
AND xal.application_id = xja.application_id
AND xal.amb_context_code = xja.amb_context_code
AND xal.event_class_code = xja.event_class_code
AND xal.event_type_code = xja.event_type_code
AND xal.line_definition_owner_code = xja.line_definition_owner_code
AND xal.line_definition_code = xja.line_definition_code
AND xal.application_id = xpr.application_id
AND xal.amb_context_code = xpr.amb_context_code
AND xal.product_rule_type_code = xpr.product_rule_type_code
AND xal.product_rule_code = xpr.product_rule_code);
SELECT xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.amb_context_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
WHERE xpa.application_id = p_application_id
AND xpa.event_class_code = p_event_class_code
AND (xpa.locking_status_flag = 'Y'
OR xpa.validation_status_code NOT IN ('E', 'Y', 'N'))
AND ROWNUM = 1;
UPDATE xla_prod_acct_headers pah
SET validation_status_code = 'N'
WHERE pah.application_id = p_application_id
AND pah.event_class_code = p_event_class_code
AND pah.validation_status_code <> 'N';
trace('Number of Application Accounting Defintion Headers updated = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_Log_module);
SELECT xpa.application_id
,xpa.amb_context_code
,xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
WHERE xpa.accounting_required_flag = 'Y'
FOR UPDATE NOWAIT;
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.accounting_required_flag = 'Y';
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.compile_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_prod_acct_headers xal
WHERE xal.application_id = xpr.application_id
AND xal.amb_context_code = xpr.amb_context_code
AND xal.product_rule_type_code = xpr.product_rule_type_code
AND xal.product_rule_code = xpr.product_rule_code
AND xal.accounting_required_flag = 'Y');
SELECT xpa.entity_code
,xpa.event_class_code
,xpa.event_type_code
,xpa.application_id
,xpa.amb_context_code
,xpa.product_rule_type_code
,xpa.product_rule_code
, xpa.validation_status_code
, xpa.locking_status_flag
FROM xla_prod_acct_headers xpa
, xla_aad_hdr_acct_attrs xah
WHERE xpa.application_id = xah.application_id
AND xpa.amb_context_code = xah.amb_context_code
AND xpa.product_rule_type_code = xah.product_rule_type_code
AND xpa.product_rule_code = xah.product_rule_code
AND xpa.event_class_code = xah.event_class_code
AND xpa.event_type_code = xah.event_type_code
AND xpa.accounting_required_flag = 'Y'
AND xah.event_class_default_flag = NVL(p_event_class_default_flag,
xah.event_class_default_flag)
AND xah.source_application_id = p_source_application_id
AND xah.source_type_code = p_source_type_code
AND xah.source_code = p_source_code
FOR UPDATE NOWAIT;
UPDATE xla_prod_acct_headers xpa
SET validation_status_code = 'N'
WHERE xpa.accounting_required_flag = 'Y'
AND EXISTS (SELECT 'X'
FROM xla_aad_hdr_acct_attrs xah
WHERE xpa.application_id = xah.application_id
AND xpa.amb_context_code = xah.amb_context_code
AND xpa.product_rule_type_code = xah.product_rule_type_code
AND xpa.product_rule_code = xah.product_rule_code
AND xpa.event_class_code = xah.event_class_code
AND xpa.event_type_code = xah.event_type_code
AND xah.event_class_default_flag = NVL(p_event_class_default_flag,
xah.event_class_default_flag)
AND xah.source_application_id = p_source_application_id
AND xah.source_type_code = p_source_type_code
AND xah.source_code = p_source_code);
UPDATE xla_product_rules_b xpr
SET compile_status_code = 'N'
WHERE xpr.compile_status_code <> 'N'
AND EXISTS (SELECT 'x'
FROM xla_prod_acct_headers xpa
, xla_aad_hdr_acct_attrs xah
WHERE xpa.application_id = xpr.application_id
AND xpa.amb_context_code = xpr.amb_context_code
AND xpa.product_rule_type_code = xpr.product_rule_type_code
AND xpa.product_rule_code = xpr.product_rule_code
AND xpa.accounting_required_flag = 'Y'
AND xpa.application_id = xah.application_id
AND xpa.amb_context_code = xah.amb_context_code
AND xpa.product_rule_type_code = xah.product_rule_type_code
AND xpa.product_rule_code = xah.product_rule_code
AND xpa.event_class_code = xah.event_class_code
AND xpa.event_type_code = xah.event_type_code
AND xah.event_class_default_flag = NVL(p_event_class_default_flag,
xah.event_class_default_flag)
AND xah.source_application_id = p_source_application_id
AND xah.source_type_code = p_source_type_code
AND xah.source_code = p_source_code);
| insert_jlt_assignments |
| |
| Inserts accounting accounting ATTRIBUTES |
| IN THE line TYPES FOR THE event CLASS |
| |
+======================================================================*/
FUNCTION insert_jlt_assignments
(p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2
,p_accounting_attribute_code IN VARCHAR2
,p_source_application_id IN NUMBER
,p_source_code IN VARCHAR2
,p_source_type_code IN VARCHAR2
,p_default_flag IN VARCHAR2
,p_product_rule_name IN OUT NOCOPY VARCHAR2
,p_product_rule_type IN OUT NOCOPY VARCHAR2
,p_event_class_name IN OUT NOCOPY VARCHAR2
,p_event_type_name IN OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
-- Array Declaration
l_arr_amb_context_code t_array_codes;
SELECT inherited_flag
FROM xla_acct_attributes_b
WHERE accounting_attribute_code = p_accounting_attribute_code;
SELECT 'x'
FROM xla_jlt_acct_attrs
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code;
SELECT amb_context_code, accounting_line_type_code, accounting_line_code
FROM xla_acct_line_types_b
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code;
SELECT amb_context_code, accounting_line_type_code, accounting_line_code
FROM xla_acct_line_types_b
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND natural_side_code <> 'G';
SELECT amb_context_code, accounting_line_type_code, accounting_line_code
FROM xla_acct_line_types_b b
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND business_method_code = 'PRIOR_ENTRY'
AND EXISTS (SELECT 'x'
FROM xla_jlt_acct_attrs a
WHERE a.application_id = b.application_id
AND a.event_class_code = b.event_class_code
AND a.amb_context_code = b.amb_context_code
AND a.accounting_line_type_code = b.accounting_line_type_code
AND a.accounting_line_code = b.accounting_line_code
AND a.accounting_attribute_code = p_accounting_attribute_code
AND a.source_code IS NOT NULL);
xla_utility_pkg.trace('> xla_evt_class_acct_attrs_pkg.insert_jlt_assignments' , 10);
INSERT INTO xla_jlt_acct_attrs
(application_id
,amb_context_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,accounting_attribute_code
,source_application_id
,source_code
,source_type_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_application_id
,l_arr_amb_context_code(i)
,p_event_class_code
,l_arr_acct_line_type_code(i)
,l_arr_acct_line_code(i)
,p_accounting_attribute_code
,p_source_application_id
,p_source_code
,p_source_type_code
,'Y'
,g_creation_date
,g_created_by
,g_last_update_date
,g_last_updated_by
,g_last_update_login);
UPDATE xla_jlt_acct_attrs
SET source_application_id = p_source_application_id
,source_type_code = p_source_type_code
,source_code = p_source_code
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND event_class_default_flag = 'Y';
INSERT INTO xla_jlt_acct_attrs
(application_id
,amb_context_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,accounting_attribute_code
,source_application_id
,source_code
,source_type_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_application_id
,l_arr_amb_context_code(i)
,p_event_class_code
,l_arr_acct_line_type_code(i)
,l_arr_acct_line_code(i)
,p_accounting_attribute_code
,NULL
,NULL
,NULL
,'Y'
,g_creation_date
,g_created_by
,g_last_update_date
,g_last_updated_by
,g_last_update_login);
UPDATE xla_jlt_acct_attrs
SET source_application_id = NULL
,source_type_code = NULL
,source_code = NULL
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND amb_context_code = l_prior_entry_line_types.amb_context_code
AND accounting_line_type_code = l_prior_entry_line_types.accounting_line_type_code
AND accounting_line_code = l_prior_entry_line_types.accounting_line_code;
xla_utility_pkg.trace('< xla_evt_class_acct_attrs_pkg.insert_jlt_assignments' , 10);
(p_location => 'xla_evt_class_acct_attrs_pkg.insert_jlt_assignments');
END insert_jlt_assignments;
| update_jlt_assignments |
| |
| Updates accounting accounting ATTRIBUTES |
| IN THE line TYPES FOR THE event CLASS |
| |
+======================================================================*/
FUNCTION update_jlt_assignments
(p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2
,p_accounting_attribute_code IN VARCHAR2
,p_source_application_id IN NUMBER
,p_source_code IN VARCHAR2
,p_source_type_code IN VARCHAR2
,p_default_flag IN VARCHAR2
,p_product_rule_name IN OUT NOCOPY VARCHAR2
,p_product_rule_type IN OUT NOCOPY VARCHAR2
,p_event_class_name IN OUT NOCOPY VARCHAR2
,p_event_type_name IN OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
--
-- Private variables
--
l_exist VARCHAR2(1);
SELECT inherited_flag
FROM xla_acct_attributes_b
WHERE accounting_attribute_code = p_accounting_attribute_code;
SELECT amb_context_code, accounting_line_type_code, accounting_line_code
FROM xla_acct_line_types_b b
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND EXISTS (SELECT 'x'
FROM xla_jlt_acct_attrs a
WHERE a.application_id = b.application_id
AND a.event_class_code = b.event_class_code
AND a.amb_context_code = b.amb_context_code
AND a.accounting_line_type_code = b.accounting_line_type_code
AND a.accounting_line_code = b.accounting_line_code
AND a.accounting_attribute_code = p_accounting_attribute_code);
SELECT amb_context_code, accounting_line_type_code, accounting_line_code
FROM xla_acct_line_types_b b
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND business_method_code <> 'PRIOR_ENTRY'
AND EXISTS (SELECT 'x'
FROM xla_jlt_acct_attrs a
WHERE a.application_id = b.application_id
AND a.event_class_code = b.event_class_code
AND a.amb_context_code = b.amb_context_code
AND a.accounting_line_type_code = b.accounting_line_type_code
AND a.accounting_line_code = b.accounting_line_code
AND a.accounting_attribute_code = p_accounting_attribute_code);
xla_utility_pkg.trace('> xla_evt_class_acct_attrs_pkg.update_jlt_assignments' , 10);
UPDATE xla_jlt_acct_attrs
SET source_application_id = p_source_application_id
,source_type_code = p_source_type_code
,source_code = p_source_code
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND event_class_default_flag = 'Y'
AND amb_context_code = l_non_pe_line_types.amb_context_code
AND accounting_line_type_code = l_non_pe_line_types.accounting_line_type_code
AND accounting_line_code = l_non_pe_line_types.accounting_line_code;
UPDATE xla_jlt_acct_attrs
SET source_application_id = p_source_application_id
,source_type_code = p_source_type_code
,source_code = p_source_code
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND event_class_default_flag = 'Y'
AND amb_context_code = l_line_types.amb_context_code
AND accounting_line_type_code = l_line_types.accounting_line_type_code
AND accounting_line_code = l_line_types.accounting_line_code;
UPDATE xla_jlt_acct_attrs
SET source_application_id = NULL
,source_type_code = NULL
,source_code = NULL
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND event_class_default_flag = 'Y'
AND source_application_id = p_source_application_id
AND source_type_code = p_source_type_code
AND source_code = p_source_code;
xla_utility_pkg.trace('< xla_evt_class_acct_attrs_pkg.update_jlt_assignments' , 10);
(p_location => 'xla_evt_class_acct_attrs_pkg.update_jlt_assignments');
END update_jlt_assignments;
| delete_jlt_assignments |
| |
| Deletes accounting accounting ATTRIBUTES |
| IN THE line TYPES FOR THE event CLASS |
| |
+======================================================================*/
FUNCTION delete_jlt_assignments
(p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2
,p_accounting_attribute_code IN VARCHAR2
,p_source_application_id IN NUMBER
,p_source_code IN VARCHAR2
,p_source_type_code IN VARCHAR2
,p_product_rule_name IN OUT NOCOPY VARCHAR2
,p_product_rule_type IN OUT NOCOPY VARCHAR2
,p_event_class_name IN OUT NOCOPY VARCHAR2
,p_event_type_name IN OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
--
-- Private variables
--
l_exist VARCHAR2(1);
SELECT count(1)
FROM xla_evt_class_acct_attrs
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code;
xla_utility_pkg.trace('> xla_evt_class_acct_attrs_pkg.delete_jlt_assignments' , 10);
DELETE
FROM xla_jlt_acct_attrs
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code;
UPDATE xla_jlt_acct_attrs
SET source_application_id = NULL
,source_type_code = NULL
,source_code = NULL
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND source_application_id = p_source_application_id
AND source_type_code = p_source_type_code
AND source_code = p_source_code;
xla_utility_pkg.trace('< xla_evt_class_acct_attrs_pkg.delete_jlt_assignments' , 10);
(p_location => 'xla_evt_class_acct_attrs_pkg.delete_jlt_assignments');
END delete_jlt_assignments;
| insert_aad_assignments |
| |
| Inserts accounting accounting ATTRIBUTES |
| IN THE AADs FOR THE event CLASS |
| |
+======================================================================*/
FUNCTION insert_aad_assignments
(p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2
,p_accounting_attribute_code IN VARCHAR2
,p_source_application_id IN NUMBER
,p_source_code IN VARCHAR2
,p_source_type_code IN VARCHAR2
,p_default_flag IN VARCHAR2
,p_product_rule_name IN OUT NOCOPY VARCHAR2
,p_product_rule_type IN OUT NOCOPY VARCHAR2
,p_event_class_name IN OUT NOCOPY VARCHAR2
,p_event_type_name IN OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
-- Array Declaration
l_arr_amb_context_code t_array_codes;
SELECT 'x'
FROM xla_aad_hdr_acct_attrs
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code;
SELECT amb_context_code, product_rule_type_code, product_rule_code,
event_type_code
FROM xla_prod_acct_headers
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code;
xla_utility_pkg.trace('> xla_evt_class_acct_attrs_pkg.insert_aad_assignments' , 10);
INSERT INTO xla_aad_hdr_acct_attrs
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,accounting_attribute_code
,source_application_id
,source_code
,source_type_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_application_id
,l_arr_amb_context_code(i)
,l_arr_product_rule_type_code(i)
,l_arr_product_rule_code(i)
,p_event_class_code
,l_arr_event_type_code(i)
,p_accounting_attribute_code
,p_source_application_id
,p_source_code
,p_source_type_code
,'Y'
,g_creation_date
,g_created_by
,g_last_update_date
,g_last_updated_by
,g_last_update_login);
UPDATE xla_aad_hdr_acct_attrs
SET source_application_id = p_source_application_id
,source_type_code = p_source_type_code
,source_code = p_source_code
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND event_class_default_flag = 'Y';
INSERT INTO xla_aad_hdr_acct_attrs
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,accounting_attribute_code
,source_application_id
,source_code
,source_type_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES
(p_application_id
,l_arr_amb_context_code(i)
,l_arr_product_rule_type_code(i)
,l_arr_product_rule_code(i)
,p_event_class_code
,l_arr_event_type_code(i)
,p_accounting_attribute_code
,NULL
,NULL
,NULL
,'Y'
,g_creation_date
,g_created_by
,g_last_update_date
,g_last_updated_by
,g_last_update_login);
xla_utility_pkg.trace('< xla_evt_class_acct_attrs_pkg.insert_aad_assignments' , 10);
(p_location => 'xla_evt_class_acct_attrs_pkg.insert_aad_assignments');
END insert_aad_assignments;
| update_aad_assignments |
| |
| Updates accounting accounting ATTRIBUTES |
| IN THE AADs FOR THE event CLASS |
| |
+======================================================================*/
FUNCTION update_aad_assignments
(p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2
,p_accounting_attribute_code IN VARCHAR2
,p_source_application_id IN NUMBER
,p_source_code IN VARCHAR2
,p_source_type_code IN VARCHAR2
,p_default_flag IN VARCHAR2
,p_product_rule_name IN OUT NOCOPY VARCHAR2
,p_product_rule_type IN OUT NOCOPY VARCHAR2
,p_event_class_name IN OUT NOCOPY VARCHAR2
,p_event_type_name IN OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
--
-- Private variables
--
l_exist VARCHAR2(1);
xla_utility_pkg.trace('> xla_evt_class_acct_attrs_pkg.update_aad_assignments' , 10);
UPDATE xla_aad_hdr_acct_attrs
SET source_application_id = p_source_application_id
,source_type_code = p_source_type_code
,source_code = p_source_code
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND event_class_default_flag = 'Y';
UPDATE xla_aad_hdr_acct_attrs
SET source_application_id = NULL
,source_type_code = NULL
,source_code = NULL
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND event_class_default_flag = 'Y'
AND source_application_id = p_source_application_id
AND source_type_code = p_source_type_code
AND source_code = p_source_code;
xla_utility_pkg.trace('< xla_evt_class_acct_attrs_pkg.update_aad_assignments' , 10);
(p_location => 'xla_evt_class_acct_attrs_pkg.update_aad_assignments');
END update_aad_assignments;
| delete_aad_assignments |
| |
| Deletes accounting accounting ATTRIBUTES |
| IN THE AADs FOR THE event CLASS |
| |
+======================================================================*/
FUNCTION delete_aad_assignments
(p_application_id IN NUMBER
,p_event_class_code IN VARCHAR2
,p_accounting_attribute_code IN VARCHAR2
,p_source_application_id IN NUMBER
,p_source_code IN VARCHAR2
,p_source_type_code IN VARCHAR2
,p_product_rule_name IN OUT NOCOPY VARCHAR2
,p_product_rule_type IN OUT NOCOPY VARCHAR2
,p_event_class_name IN OUT NOCOPY VARCHAR2
,p_event_type_name IN OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
--
-- Private variables
--
l_exist VARCHAR2(1);
SELECT count(1)
FROM xla_evt_class_acct_attrs
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code;
xla_utility_pkg.trace('> xla_evt_class_acct_attrs_pkg.delete_aad_assignments' , 10);
DELETE
FROM xla_aad_hdr_acct_attrs
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code;
UPDATE xla_aad_hdr_acct_attrs
SET source_application_id = NULL
,source_type_code = NULL
,source_code = NULL
WHERE application_id = p_application_id
AND event_class_code = p_event_class_code
AND accounting_attribute_code = p_accounting_attribute_code
AND source_application_id = p_source_application_id
AND source_type_code = p_source_type_code
AND source_code = p_source_code;
xla_utility_pkg.trace('< xla_evt_class_acct_attrs_pkg.delete_aad_assignments' , 10);
(p_location => 'xla_evt_class_acct_attrs_pkg.delete_aad_assignments');
END delete_aad_assignments;
g_last_update_date := sysdate;
g_last_update_login := xla_environment_pkg.g_login_id;
g_last_updated_by := xla_environment_pkg.g_usr_id;