[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM pay_action_parameters
WHERE parameter_name = 'LOGGING';
SELECT distinct classification_id, legislation_code, classification_name
FROM hr_s_element_classifications pec
,hr_s_application_ownerships ao
,fnd_product_installations b
,fnd_application c
WHERE nvl(legislation_code,'X') = 'ZZ'
AND ao.key_name = 'CLASSIFICATION_ID'
AND TO_NUMBER(ao.key_value) = pec.classification_id
AND ao.product_name = c.application_short_name
AND c.application_id = b.application_id
AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
OR
(b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
SELECT distinct balance_type_id, currency_code, balance_name
FROM hr_s_balance_types pbt
,hr_s_application_ownerships ao
,fnd_product_installations b
,fnd_application c
WHERE pbt.legislation_code = 'ZZ'
AND ao.key_name = 'BALANCE_TYPE_ID'
AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
AND ao.product_name = c.application_short_name
AND c.application_id = b.application_id
AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
OR
(b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
SELECT distinct defined_balance_id, pbt.balance_name bname
FROM hr_s_defined_balances pdb
,hr_s_balance_types pbt
,hr_s_application_ownerships ao
,fnd_product_installations b
,fnd_application c
WHERE pdb.legislation_code ='ZZ'
AND ao.key_name = 'DEFINED_BALANCE_ID'
AND pbt.balance_type_id = pdb.balance_type_id
AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
AND ao.product_name = c.application_short_name
AND c.application_id = b.application_id
AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
OR
(b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
SELECT distinct balance_dimension_id, dimension_name
FROM hr_s_application_ownerships ao
,hr_s_balance_dimensions pbd
,fnd_product_installations b
,fnd_application c
WHERE pbd.legislation_code ='ZZ'
AND ao.key_name = 'BALANCE_DIMENSION_ID'
AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
AND ao.product_name = c.application_short_name
AND c.application_id = b.application_id
AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
OR
(b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
SELECT distinct fr.route_id, route_name
FROM hr_s_application_ownerships ao
,hr_s_routes fr
,hr_s_balance_dimensions pbd
,fnd_product_installations b
,fnd_application c
WHERE pbd.legislation_code ='ZZ'
AND ao.key_name = 'ROUTE_ID'
AND TO_NUMBER(ao.key_value) = fr.route_id
AND fr.route_id = pbd.route_id
AND ao.product_name = c.application_short_name
AND c.application_id = b.application_id
AND ((b.status = 'I' AND c.application_short_name <> 'PQP')
OR
(b.status in ('I', 'S') AND c.application_short_name = 'PQP'));
SELECT field_name
FROM hr_s_legislative_field_info
WHERE legislation_code = 'ZZ';
SELECT rule_type
FROM hr_s_legislation_rules
WHERE legislation_code = 'ZZ';
SELECT distinct pbc.balance_classification_id, pbt.balance_name bname
FROM hr_s_balance_classifications pbc
,hr_s_balance_types pbt
WHERE pbc.legislation_code ='ZZ'
AND pbc.balance_type_id = pbt.balance_type_id;
PROCEDURE insert_ownership(p_key_name IN VARCHAR2,
p_product_name IN VARCHAR2,
p_key_value IN VARCHAR2) AS
BEGIN
null;
/*hr_utility.set_location('--pay_ip_startup_util.insert_ownership',10);
INSERT INTO hr_s_application_ownerships
( key_name
,product_name
,key_value)
SELECT
p_key_name
,p_product_name
,p_key_value
FROM dual
WHERE NOT EXISTS (SELECT NULL
FROM hr_s_application_ownerships
WHERE product_name = p_product_name
AND key_name = p_key_name
AND key_value = p_key_value);
hr_utility.set_location('--pay_ip_startup_util.insert_ownership',20); */
END insert_ownership;
SELECT count(*)
INTO l_Installed
FROM hr_legislation_installations
WHERE application_short_name IN('PAY','PER')
AND legislation_code = p_legislation_code;
SELECT count(*)
INTO l_Installed
FROM hr_legislation_installations
WHERE action IS NOT NULL;
SELECT 1
INTO l_reference
FROM dual WHERE EXISTS (SELECT NULL FROM pay_element_classifications
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL);
DELETE hr_s_formula_types;
DELETE hr_s_ftype_context_usages;
DELETE hr_s_formulas_f;
DELETE hr_s_routes;
DELETE hr_s_route_context_usages;
DELETE hr_s_contexts;
DELETE hr_s_route_parameters;
DELETE hr_s_user_entities;
DELETE hr_s_database_items;
DELETE hr_s_route_parameter_values;
DELETE hr_s_functions;
DELETE hr_s_function_parameters;
DELETE hr_s_function_context_usages;
DELETE hr_s_assignment_status_types;
DELETE hr_s_element_classifications;
DELETE hr_s_element_types_f;
DELETE hr_s_input_values_f;
DELETE hr_s_status_processing_rules_f;
DELETE hr_s_formula_result_rules_f;
DELETE hr_s_sub_classn_rules_f;
DELETE hr_s_balance_types;
DELETE hr_s_balance_classifications;
DELETE hr_s_defined_balances;
DELETE hr_s_balance_feeds_f;
DELETE hr_s_balance_dimensions;
DELETE hr_s_element_sets;
DELETE hr_s_element_type_rules;
DELETE hr_s_ele_classn_rules;
DELETE hr_s_user_tables;
DELETE hr_s_user_columns;
DELETE hr_s_user_rows_f;
DELETE hr_s_user_column_instances_f;
DELETE hr_s_qp_reports;
DELETE hr_s_org_information_types;
DELETE hr_s_org_info_types_by_class;
DELETE hr_s_assignment_info_types;
DELETE hr_s_globals_f;
DELETE hr_s_legislative_field_info;
DELETE hr_s_legislation_subgroups;
DELETE hr_s_application_ownerships;
DELETE hr_s_payment_types;
DELETE hr_s_benefit_classifications;
DELETE hr_s_cobra_qfying_events_f;
DELETE hr_s_valid_dependent_types;
DELETE hr_s_history;
DELETE hr_s_state_rules;
DELETE hr_s_taxability_rules;
DELETE hr_s_monetary_units;
DELETE hr_s_wc_state_surcharges;
DELETE hr_s_legislation_rules;
DELETE HR_S_TAXABILITY_RULES_DATES;
DELETE HR_S_MAGNETIC_RECORDS;
DELETE HR_S_MAGNETIC_BLOCKS;
DELETE HR_S_REPORT_FORMAT_MAPPINGS_F;
DELETE HR_S_US_CITY_TAX_INFO_F;
DELETE HR_S_US_COUNTY_TAX_INFO_F;
DELETE HR_S_US_STATE_TAX_INFO_F;
DELETE HR_S_US_FEDERAL_TAX_INFO_F;
DELETE HR_S_US_GARN_EXEMPTION_RULES_F;
DELETE HR_S_US_GARN_LIMIT_RULES_F;
DELETE HR_S_US_GARN_FEE_RULES_F;
DELETE HR_S_REPORT_LOOKUPS;
DELETE HR_S_REPORT_FORMAT_ITEMS_F;
DELETE HR_S_STATE_RULES;
SELECT
classification_id,business_group_id,legislation_code,classification_name,description,
legislation_subgroup,costable_flag,default_high_priority,default_low_priority,
default_priority,distributable_over_flag,non_payments_flag,costing_debit_or_credit,
parent_classification_id,create_by_default_flag,last_update_date,last_updated_by,
last_update_login,created_by,creation_date,balance_initialization_flag,object_version_number
FROM pay_element_classifications
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL;
SELECT
balance_type_id, business_group_id, legislation_code, currency_code,
assignment_remuneration_flag, balance_name,balance_uom, NULL comments, jurisdiction_level,
legislation_subgroup, reporting_name, tax_type, attribute_category,
attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14,
attribute15, attribute16, attribute17, attribute18, attribute19, attribute20,
last_update_date, last_updated_by, last_update_login, created_by, creation_date
FROM pay_balance_types
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL;
SELECT
d.defined_balance_id, d.business_group_id, d.legislation_code, d.balance_type_id,
d.balance_dimension_id, d.force_latest_balance_flag, d.legislation_subgroup,
d.last_update_date, d.last_updated_by, d.last_update_login, d.created_by, d.creation_date,
d.object_version_number, d.grossup_allowed_flag, b.balance_name bname
FROM pay_defined_balances d, pay_balance_types b
WHERE d.balance_type_id = b.balance_type_id
AND EXISTS (SELECT NULL FROM hr_s_balance_types b
WHERE d.balance_type_id = b.balance_type_id);
SELECT
balance_dimension_id, business_group_id, legislation_code, route_id,
database_item_suffix, dimension_name, dimension_type, description,
feed_checking_code, feed_checking_type, legislation_subgroup, payments_flag,
expiry_checking_code, expiry_checking_level, dimension_level, period_type
FROM pay_balance_dimensions
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL;
SELECT
route_id, route_name, user_defined_flag, description, text, last_update_date,
last_updated_by, last_update_login, created_by, creation_date
FROM ff_routes a
WHERE EXISTS (SELECT NULL
FROM pay_balance_dimensions c
WHERE c.route_id = a.route_id
AND c.legislation_code = 'ZZ');
SELECT
balance_classification_id, business_group_id, legislation_code, balance_type_id,
classification_id, scale, legislation_subgroup, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, object_version_number
FROM pay_balance_classifications
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL;
INSERT INTO hr_s_application_ownerships
(key_name
,product_name
,key_value)
SELECT ao.key_name
,ao.product_name
,ao.key_value
FROM hr_application_ownerships ao
,pay_element_classifications pec
WHERE pec.legislation_code = 'ZZ'
AND ao.key_name = 'CLASSIFICATION_ID'
AND TO_NUMBER(ao.key_value) = pec.classification_id
UNION ALL
SELECT ao.key_name
,ao.product_name
,ao.key_value
FROM hr_application_ownerships ao
,pay_balance_types pbt
WHERE pbt.legislation_code = 'ZZ'
AND ao.key_name = 'BALANCE_TYPE_ID'
AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
UNION ALL
SELECT ao.key_name
,ao.product_name
,ao.key_value
FROM hr_application_ownerships ao
,pay_balance_dimensions pbd
WHERE pbd.legislation_code ='ZZ'
AND ao.key_name = 'BALANCE_DIMENSION_ID'
AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
UNION ALL
SELECT ao.key_name
,ao.product_name
,ao.key_value
FROM hr_application_ownerships ao
,pay_defined_balances pdb
WHERE pdb.legislation_code ='ZZ'
AND ao.key_name = 'DEFINED_BALANCE_ID'
AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
UNION ALL
SELECT ao.key_name
,ao.product_name
,ao.key_value
FROM hr_application_ownerships ao
,ff_routes fr
,pay_balance_dimensions pbd
WHERE pbd.legislation_code ='ZZ'
AND ao.key_name = 'ROUTE_ID'
AND TO_NUMBER(ao.key_value) = fr.route_id
AND fr.route_id = pbd.route_id;
INSERT INTO hr_s_element_classifications
( classification_id
,business_group_id
,legislation_code
,classification_name
,description
,legislation_subgroup
,costable_flag
,default_high_priority
,default_low_priority
,default_priority
,distributable_over_flag
,non_payments_flag
,costing_debit_or_credit
,parent_classification_id
,create_by_default_flag
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,balance_initialization_flag
,object_version_number
)
VALUES
( rec.classification_id
,rec.business_group_id
,rec.legislation_code
,rec.classification_name
,rec.description
,rec.legislation_subgroup
,rec.costable_flag
,rec.default_high_priority
,rec.default_low_priority
,rec.default_priority
,rec.distributable_over_flag
,rec.non_payments_flag
,rec.costing_debit_or_credit
,rec.parent_classification_id
,rec.create_by_default_flag
,rec.last_update_date
,rec.last_updated_by
,rec.last_update_login
,rec.created_by
,rec.creation_date
,rec.balance_initialization_flag
,rec.object_version_number
);
INSERT INTO hr_s_balance_types
( balance_type_id
,business_group_id
,legislation_code
,currency_code
,assignment_remuneration_flag
,balance_name
,balance_uom
,comments
,jurisdiction_level
,legislation_subgroup
,reporting_name
,tax_type
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
VALUES
( rec.balance_type_id
,rec.business_group_id
,rec.legislation_code
,rec.currency_code
,rec.assignment_remuneration_flag
,rec.balance_name
,rec.balance_uom
,rec.comments
,rec.jurisdiction_level
,rec.legislation_subgroup
,rec.reporting_name
,rec.tax_type
,rec.attribute_category
,rec.attribute1
,rec.attribute2
,rec.attribute3
,rec.attribute4
,rec.attribute5
,rec.attribute6
,rec.attribute7
,rec.attribute8
,rec.attribute9
,rec.attribute10
,rec.attribute11
,rec.attribute12
,rec.attribute13
,rec.attribute14
,rec.attribute15
,rec.attribute16
,rec.attribute17
,rec.attribute18
,rec.attribute19
,rec.attribute20
,rec.last_update_date
,rec.last_updated_by
,rec.last_update_login
,rec.created_by
,rec.creation_date
);
INSERT INTO hr_s_defined_balances
( defined_balance_id
,business_group_id
,legislation_code
,balance_type_id
,balance_dimension_id
,force_latest_balance_flag
,legislation_subgroup
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,object_version_number
,grossup_allowed_flag
)
VALUES
( rec.defined_balance_id
,rec.business_group_id
,rec.legislation_code
,rec.balance_type_id
,rec.balance_dimension_id
,rec.force_latest_balance_flag
,rec.legislation_subgroup
,rec.last_update_date
,rec.last_updated_by
,rec.last_update_login
,rec.created_by
,rec.creation_date
,rec.object_version_number
,rec.grossup_allowed_flag
);
INSERT INTO hr_s_balance_dimensions
( balance_dimension_id
,business_group_id
,legislation_code
,route_id
,database_item_suffix
,dimension_name
,dimension_type
,description
,feed_checking_code
,feed_checking_type
,legislation_subgroup
,payments_flag
,expiry_checking_code
,expiry_checking_level
,dimension_level
, period_type
)
VALUES
( rec.balance_dimension_id
,rec.business_group_id
,rec.legislation_code
,rec.route_id
,rec.database_item_suffix
,rec.dimension_name
,rec.dimension_type
,rec.description
,rec.feed_checking_code
,rec.feed_checking_type
,rec.legislation_subgroup
,rec.payments_flag
,rec.expiry_checking_code
,rec.expiry_checking_level
,rec.dimension_level
,rec.period_type
);
INSERT INTO hr_s_routes
( route_id
,route_name
,user_defined_flag
,description
,text
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
VALUES
( rec.route_id
,rec.route_name
,rec.user_defined_flag
,rec.description
,rec.text
,rec.last_update_date
,rec.last_updated_by
,rec.last_update_login
,rec.created_by
,rec.creation_date
);
DELETE FROM hr_s_application_ownerships
WHERE key_name = 'BALANCE_DIMENSION_ID'
AND TO_NUMBER(key_value) IN (SELECT balance_dimension_id
FROM hr_s_balance_dimensions
WHERE legislation_code = 'ZZ'
AND INSTR(database_item_suffix,'_TU_') > 0);
INSERT INTO hr_s_route_parameters
(SELECT
route_parameter_id
,route_id
,data_type
,parameter_name
,sequence_no
FROM ff_route_parameters a
WHERE EXISTS ( SELECT NULL
FROM hr_s_routes b
WHERE b.route_id = a.route_id));
INSERT INTO hr_s_route_context_usages
(SELECT
route_id
,context_id
,sequence_no
FROM ff_route_context_usages a
WHERE EXISTS ( SELECT NULL
FROM hr_s_routes b
WHERE b.route_id = a.route_id));
INSERT into hr_s_legislative_field_info
(FIELD_NAME,
LEGISLATION_CODE,
PROMPT,
VALIDATION_NAME,
VALIDATION_TYPE,
TARGET_LOCATION,
RULE_TYPE,
RULE_MODE)
(SELECT
FIELD_NAME,
LEGISLATION_CODE,
PROMPT,
VALIDATION_NAME,
VALIDATION_TYPE,
TARGET_LOCATION,
RULE_TYPE,
RULE_MODE
FROM pay_legislative_field_info
WHERE nvl(legislation_code,'X') = 'ZZ');
INSERT INTO hr_s_balance_classifications
( balance_classification_id
,business_group_id
,legislation_code
,balance_type_id
,classification_id
,scale
,legislation_subgroup
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,object_version_number
)
VALUES
( rec.balance_classification_id
,rec.business_group_id
,rec.legislation_code
,rec.balance_type_id
,rec.classification_id
,rec.scale
,rec.legislation_subgroup
,rec.last_update_date
,rec.last_updated_by
,rec.last_update_login
,rec.created_by
,rec.creation_date
,rec.object_version_number
);
p_dynamic_insert_flag IN VARCHAR2,
p_shorthand_enabled_flag IN VARCHAR2,
p_shorthand_prompt IN VARCHAR2,
p_shorthand_length IN NUMBER) RETURN NUMBER IS
l_flexfield fnd_flex_key_api.flexfield_type;
SELECT null
FROM fnd_id_flex_structures_vl
WHERE application_id = p_application_id
AND id_flex_code = p_flexfield_code
AND id_flex_structure_name = p_structure_title;
SELECT application_id
INTO l_application_id
FROM FND_APPLICATION
WHERE application_short_name = p_appl_Short_Name;
dynamic_insert_flag => p_dynamic_insert_flag,
shorthand_enabled_flag => p_shorthand_enabled_flag,
shorthand_prompt => p_shorthand_prompt,
shorthand_length => p_shorthand_length);
SELECT application_id
INTO l_application_id
FROM FND_APPLICATION
WHERE application_short_name = p_appl_short_name;
SELECT NVL(MAX(ifs.id_flex_num),0) + 1
INTO l_structure.structure_number
FROM fnd_id_flex_structures ifs
WHERE ifs.application_id = l_application_id
AND ifs.id_flex_code = p_flex_code
AND ifs.id_flex_num < 101;
INSERT INTO hr_s_legislation_rules
( legislation_code
,rule_type
,rule_mode)
SELECT
'ZZ'
,p_rule_type
,p_rule_mode
FROM dual
WHERE NOT EXISTS (SELECT NULL
FROM hr_s_legislation_rules
WHERE legislation_code = 'ZZ'
AND rule_type = p_rule_type);
UPDATE hr_s_legislation_rules SET
rule_mode = p_rule_mode
WHERE legislation_code = 'ZZ'
AND rule_type = p_rule_type;
PROCEDURE update_shadow_tables
(p_legislation_code IN VARCHAR2,
p_currency_code IN VARCHAR2) IS
BEGIN
hr_utility.set_location('pay_ip_startup_util.update_shadow_tables ',10);
UPDATE hr_s_element_classifications
SET legislation_code = p_legislation_code
WHERE legislation_code = 'ZZ';
UPDATE hr_s_BALANCE_TYPES
SET legislation_code = p_legislation_code,
currency_code = p_currency_code
WHERE legislation_code = 'ZZ';
UPDATE hr_s_defined_balances
SET legislation_code = p_legislation_code
WHERE legislation_code = 'ZZ';
UPDATE hr_s_balance_dimensions
SET legislation_code = p_legislation_code
WHERE legislation_code = 'ZZ';
UPDATE hr_s_legislation_rules
SET legislation_code = p_legislation_code
WHERE legislation_code = 'ZZ';
UPDATE hr_s_legislative_field_info
SET legislation_code = p_legislation_code
WHERE legislation_code = 'ZZ';
UPDATE hr_s_balance_classifications
SET legislation_code = p_legislation_code
WHERE legislation_code = 'ZZ';
hr_utility.set_location('pay_ip_startup_util.update_shadow_tables ',20);
END update_shadow_tables;
PROCEDURE insert_history_table
(p_legislation_code IN VARCHAR2) IS
BEGIN
hr_utility.set_location('pay_ip_startup_util.insert_history_table',10);
INSERT INTO hr_s_history
( package_name
,date_of_export
,date_of_import
,status
,legislation_code)
VALUES
( TO_CHAR(SYSDATE,'ddMonyyyy-hh:rr:ss') || '[' || p_legislation_code || ']'
,sysdate
,sysdate
,'HR_S tabes copied from reference account'
,p_legislation_code);
hr_utility.set_location('pay_ip_startup_util.insert_history_table',20);
END insert_history_table ;
PROCEDURE update_ele_class_tl
(p_legislation_code IN VARCHAR2) IS
CURSOR get_classid_btable_csr IS
SELECT b.classification_id bid , b.classification_name bname,
t.language, t.classification_name tname, t.description, t.source_lang
FROM pay_element_classifications_tl t, pay_element_classifications b
WHERE b.classification_id = t.classification_id
AND b.legislation_code = 'ZZ'
AND b.business_group_id IS NULL;
SELECT t.classification_id tlid
FROM pay_element_classifications_tl t, pay_element_classifications b
WHERE b.classification_name = l_name
AND b.legislation_code = l_legislation_code
AND b.business_group_id is NULL
AND t.classification_id = b.classification_id
AND t.language = l_language;
hr_utility.set_location('pay_ip_startup_util.update_ele_class_tl',10);
UPDATE pay_element_classifications_tl
SET classification_name = l_record.tname,
description = l_record.description,
source_lang = l_record.source_lang
WHERE classification_id = rec_tltable_csr.tlid
AND language = l_record.language;
hr_utility.set_location('pay_ip_startup_util.update_ele_class_tl',20);
END update_ele_class_tl;
PROCEDURE update_bal_type_tl
(p_legislation_code IN VARCHAR2) IS
--old reference data ids
CURSOR get_balid_btable_csr IS
SELECT b.balance_type_id bid , b.balance_name bname ,
t.language, t.balance_name tname, t.reporting_name, t.source_lang
FROM pay_balance_types_tl t, pay_balance_types b
WHERE t.balance_type_id = b.balance_type_id
AND b.legislation_code = 'ZZ'
AND b.business_group_id IS NULL ;
SELECT t.balance_type_id tlid
FROM pay_balance_types_tl t, pay_balance_types b
WHERE b.balance_name = l_name
AND b.legislation_code = l_legislation_code
AND b.business_group_id IS NULL
AND b.balance_type_id = t.balance_type_id
AND t.language = l_language;
hr_utility.set_location('pay_ip_startup_util.update_bal_type_tl',10);
UPDATE pay_balance_types_tl
SET balance_name = l_record.tname,
reporting_name = l_record.reporting_name,
source_lang = l_record.source_lang
WHERE balance_type_id = rec_tltable_csr.tlid
AND language = l_record.language;
hr_utility.set_location('pay_ip_startup_util.update_bal_type_tl',20);
END update_bal_type_tl;
SELECT
run_type_id, run_type_name, run_method, effective_start_date, effective_end_date,
business_group_id, legislation_code, shortname, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, object_version_number
FROM pay_run_types_f
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL and sysdate between effective_start_date and effective_end_date;
SELECT run_type_id parent_id
FROM pay_run_types_f
WHERE (run_type_name,shortname) IN (SELECT RUN_TYPE_NAME, shortname from pay_run_types_f
WHERE RUN_TYPE_ID IN (SELECT PARENT_RUN_TYPE_ID
FROM pay_run_type_usages_f
WHERE LEGISLATION_CODE = 'ZZ'
AND sysdate BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
AND business_group_id is null
)
AND sysdate BETWEEN effective_start_date AND effective_end_date
)
AND sysdate BETWEEN effective_start_date
AND effective_end_date
AND legislation_code = l_legislation_code
AND business_group_id is NULL;
SELECT prtf1.run_type_id child_id , prtuf.sequence sequence , prtf1.run_type_name run_type_name
FROM pay_run_types_f prtf1, pay_run_types_f prtf2 , pay_run_type_usages_f prtuf
WHERE (prtf1.run_type_name,prtf1.shortname) IN (SELECT RUN_TYPE_NAME, shortname
FROM pay_run_types_f
WHERE RUN_TYPE_ID IN (SELECT child_RUN_TYPE_ID
FROM pay_run_type_usages_f
WHERE parent_run_type_id in (SELECT distinct PARENT_RUN_TYPE_ID
FROM pay_run_type_usages_f
WHERE LEGISLATION_CODE = 'ZZ'
AND sysdate BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
AND business_group_id is null)
AND legislation_code = 'ZZ'
AND sysdate BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
AND business_group_id is null
)
AND sysdate BETWEEN effective_start_date AND effective_end_date
)
AND sysdate BETWEEN prtf1.effective_start_date
AND prtf1.effective_end_date
AND prtf1.legislation_code = l_legislation_code
AND prtf1.business_group_id is NULL
AND prtuf.legislation_code = 'ZZ'
AND prtf2.RUN_TYPE_NAME = prtf1.RUN_TYPE_NAME
AND prtf2.SHORTNAME = prtf1.SHORTNAME
AND prtf2.business_group_id is NULL
AND prtuf.legislation_code = prtf2.legislation_code
AND prtf2.run_type_id in (prtuf.parent_run_type_id, prtuf.child_run_type_id);
SELECT run_type_id, object_version_number
INTO l_rt_id, l_rt_ovn
FROM pay_run_types_f
WHERE run_type_name = rec.run_type_name
AND shortname = rec.shortname
AND legislation_code = p_legislation_code
AND sysdate between effective_start_date and effective_end_date
AND business_group_id IS NULL;
SELECT run_type_usage_id, object_version_number
INTO l_rtu_id, l_rtu_ovn
FROM pay_run_type_usages_f
WHERE parent_run_type_id = rec_parent.parent_id
AND child_run_type_id = rec_child.child_id
AND legislation_code = p_legislation_code
AND sysdate between effective_start_date and effective_end_date
AND business_group_id IS NULL;
pay_run_type_usage_api.update_run_type_usage (
p_effective_date => g_start_of_time
,p_datetrack_update_mode => 'CORRECTION'
,p_run_type_usage_id => l_rtu_id
,p_object_version_number => l_rtu_ovn
,p_sequence => rec_child.sequence
,p_business_group_id => NULL
,p_legislation_code => p_legislation_code
,p_effective_start_date => l_rtu_eff_start_date
,p_effective_end_date => l_rtu_eff_end_date
) ;
SELECT
attribute_name, alterable, user_attribute_name
FROM pay_bal_attribute_definitions
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL;
PROCEDURE update_run_type_tl
(p_legislation_code IN VARCHAR2) IS
CURSOR get_runid_btable_csr IS
SELECT b.run_type_id bid , b.run_type_name bname,
t.language, t.run_type_name tname, t.shortname, t.source_lang
FROM pay_run_types_f_tl t, pay_run_types_f b
WHERE t.run_type_id = b.run_type_id
AND b.legislation_code = 'ZZ'
AND b.business_group_id IS NULL
AND sysdate BETWEEN b.effective_start_date AND b.effective_end_date ;
SELECT t.run_type_id tlid
FROM pay_run_types_f_tl t, pay_run_types_f b
WHERE b.run_type_name = l_name
AND b.legislation_code = l_legislation_code
AND b.business_group_id IS NULL
AND t.run_type_id = b.run_type_id
AND t.language = l_language ;
hr_utility.set_location('pay_ip_startup_util.update_run_type_tl',10);
UPDATE pay_run_types_f_tl
SET run_type_name = l_record.tname,
shortname = l_record.shortname,
source_lang = l_record.source_lang
WHERE run_type_id = rec_tltable_csr.tlid
AND language = l_record.language;
hr_utility.set_location('pay_ip_startup_util.update_run_type_tl',20);
END update_run_type_tl;
select 1 from fnd_product_installations
where application_id = 801
and status = 'I';
select id_flex_structure_code
from fnd_id_flex_structures
where id_flex_code = 'SCL'
and id_flex_num = p_id_flex_num;
SELECT territory_short_name, territory_short_name || ' (' || territory_code || ')'
INTO l_territory, l_territory_with_code
FROM fnd_territories_vl
WHERE territory_code = p_legislation_code;
SELECT MIN(id_flex_num)
INTO l_id_flex_num
FROM fnd_id_flex_structures
WHERE id_flex_code = 'SCL'
AND id_flex_structure_code like
p_legislation_code||'_STATUTORY_INFO'||'%';
p_dynamic_insert_flag => 'Y',
p_shorthand_enabled_flag => 'N',
p_shorthand_prompt => '',
p_shorthand_length => 10);
select min(1)
into l_segment_used
FROM fnd_id_flex_segments
WHERE id_flex_num = l_id_flex_num
AND id_flex_code = 'SCL'
AND application_column_name = 'SEGMENT1';
p_dynamic_insert_flag => 'Y',
p_shorthand_enabled_flag => 'N',
p_shorthand_prompt => '',
p_shorthand_length => 10);
update_shadow_tables(p_legislation_code, p_currency_code);
insert_history_table(p_legislation_code);
update_ele_class_tl(p_legislation_code);
update_bal_type_tl(p_legislation_code);
update_run_type_tl(p_legislation_code);