[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT parameter_name
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(application_short_name)
INTO l_Installed
FROM hr_legislation_installations
WHERE application_short_name IN('PAY','PER')
AND legislation_code = p_legislation_code;
SELECT count(application_short_name)
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);
SELECT rule_mode
INTO v_install_tax_unit
FROM pay_legislation_rules
WHERE legislation_code = p_legislation_code
AND rule_type = 'TAX_UNIT';
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_categories_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_dimension_routes;
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,
save_run_balance_enabled, database_item_function
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, optimizer_hint
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'
UNION
SELECT NULL
FROM pay_balance_dimensions pbd,
pay_dimension_routes pdr
WHERE pbd.legislation_code = 'ZZ'
and pdr.balance_dimension_id = pbd.balance_dimension_id
and pdr.route_id = a.route_id);
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;
SELECT
balance_category_id,category_name ,effective_start_date ,effective_end_date ,legislation_code ,
business_group_id ,save_run_balance_enabled ,pbc_information_category ,pbc_information1 ,pbc_information2 ,
pbc_information3 ,pbc_information4 ,pbc_information5 ,pbc_information6 ,pbc_information7 ,pbc_information8 ,
pbc_information9 ,pbc_information10 ,pbc_information11 ,pbc_information12 ,pbc_information13 ,pbc_information14 ,
pbc_information15 ,pbc_information16 ,pbc_information17 ,pbc_information18 ,pbc_information19 ,pbc_information20 ,
pbc_information21 ,pbc_information22 ,pbc_information23 ,pbc_information24 ,pbc_information25 ,pbc_information26 ,
pbc_information27 ,pbc_information28 ,pbc_information29 ,pbc_information30 ,last_update_date ,last_updated_by ,
last_update_login ,created_by ,creation_date ,object_version_number ,user_category_name
FROM pay_balance_categories_f
WHERE nvl(legislation_code,'X') = 'ZZ'
AND business_group_id IS NULL;
SELECT report_type, report_qualifier, report_format, effective_start_date, effective_end_date, range_code,
assignment_action_code, initialization_code, archive_code, magnetic_code, report_category,
report_name, sort_code, updatable_flag, deinitialization_code, legislation_code, last_update_date,
last_updated_by, last_update_login, created_by, creation_date, temporary_action_flag
FROM pay_report_format_mappings_f
WHERE nvl(report_qualifier,'X') = 'ZZ'
AND business_group_id IS NULL;
SELECT
pdr.balance_dimension_id ,pdr.route_id ,pdr.route_type ,pdr.priority ,pdr.run_dimension_id ,
pdr.balance_type_column ,pdr.decode_required ,pdr.last_update_date ,pdr.last_updated_by ,
pdr.last_update_login ,pdr.created_by ,pdr.creation_date ,pdr.object_version_number ,
pbd.dimension_name
FROM pay_dimension_routes pdr
,pay_balance_dimensions pbd
WHERE nvl(pbd.legislation_code,'X') = 'ZZ'
AND pbd.business_group_id IS NULL
AND pbd.balance_dimension_id = pdr.balance_dimension_id;
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
UNION ALL
SELECT ao.key_name
,ao.product_name
,ao.key_value
FROM hr_application_ownerships ao
,ff_routes fr
,pay_balance_dimensions pbd
,pay_dimension_routes pdr
WHERE pbd.legislation_code ='ZZ'
AND pdr.balance_dimension_id = pbd.balance_dimension_id
AND ao.key_name = 'ROUTE_ID'
AND TO_NUMBER(ao.key_value) = fr.route_id
AND fr.route_id = pdr.route_id
UNION ALL
SELECT ao.key_name
,ao.product_name
,ao.key_value
FROM hr_application_ownerships ao,
pay_balance_categories pbc
WHERE pbc.legislation_code ='ZZ'
AND ao.key_name = 'BALANCE_CATEGORY_ID'
AND TO_NUMBER(ao.key_value) = pbc.balance_category_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
,save_run_balance_enabled
,database_item_function
)
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
,rec.save_run_balance_enabled
,rec.database_item_function
);
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
,optimizer_hint
)
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
,rec.optimizer_hint
);
INSERT INTO hr_s_dimension_routes
( balance_dimension_id
,route_id
,route_type
,priority
,run_dimension_id
,balance_type_column
,decode_required
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,object_version_number
)
VALUES
( rec.balance_dimension_id
,rec.route_id
,rec.route_type
,rec.priority
,rec.run_dimension_id
,rec.balance_type_column
,rec.decode_required
,rec.last_update_date
,rec.last_updated_by
,rec.last_update_login
,rec.created_by
,rec.creation_date
,rec.object_version_number
);
then update run dimension to _ASG_RUN
*/
UPDATE hr_s_dimension_routes
SET run_dimension_id =
(
SELECT balance_dimension_id
FROM hr_s_balance_dimensions
WHERE legislation_code = 'ZZ'
AND database_item_suffix = '_ASG_RUN'
);
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);
/* With tax unit not installed update
the SAVE_RUN_BALANCE_ENABLED flag
of _ASG_RUN dimension to 'Y'
*/
UPDATE hr_s_balance_dimensions
SET save_run_balance_enabled = 'Y'
WHERE legislation_code = 'ZZ'
AND database_item_suffix = '_ASG_RUN';
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
);
INSERT INTO hr_s_balance_categories_f
( BALANCE_CATEGORY_ID
,CATEGORY_NAME
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,LEGISLATION_CODE
,BUSINESS_GROUP_ID
,SAVE_RUN_BALANCE_ENABLED
,PBC_INFORMATION_CATEGORY
,PBC_INFORMATION1
,PBC_INFORMATION2
,PBC_INFORMATION3
,PBC_INFORMATION4
,PBC_INFORMATION5
,PBC_INFORMATION6
,PBC_INFORMATION7
,PBC_INFORMATION8
,PBC_INFORMATION9
,PBC_INFORMATION10
,PBC_INFORMATION11
,PBC_INFORMATION12
,PBC_INFORMATION13
,PBC_INFORMATION14
,PBC_INFORMATION15
,PBC_INFORMATION16
,PBC_INFORMATION17
,PBC_INFORMATION18
,PBC_INFORMATION19
,PBC_INFORMATION20
,PBC_INFORMATION21
,PBC_INFORMATION22
,PBC_INFORMATION23
,PBC_INFORMATION24
,PBC_INFORMATION25
,PBC_INFORMATION26
,PBC_INFORMATION27
,PBC_INFORMATION28
,PBC_INFORMATION29
,PBC_INFORMATION30
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,OBJECT_VERSION_NUMBER
,USER_CATEGORY_NAME
)
VALUES
( rec.balance_category_id
,rec.category_name
,rec.effective_start_date
,rec.effective_end_date
,rec.legislation_code
,rec.business_group_id
,rec.save_run_balance_enabled
,rec.pbc_information_category
,rec.pbc_information1
,rec.pbc_information2
,rec.pbc_information3
,rec.pbc_information4
,rec.pbc_information5
,rec.pbc_information6
,rec.pbc_information7
,rec.pbc_information8
,rec.pbc_information9
,rec.pbc_information10
,rec.pbc_information11
,rec.pbc_information12
,rec.pbc_information13
,rec.pbc_information14
,rec.pbc_information15
,rec.pbc_information16
,rec.pbc_information17
,rec.pbc_information18
,rec.pbc_information19
,rec.pbc_information20
,rec.pbc_information21
,rec.pbc_information22
,rec.pbc_information23
,rec.pbc_information24
,rec.pbc_information25
,rec.pbc_information26
,rec.pbc_information27
,rec.pbc_information28
,rec.pbc_information29
,rec.pbc_information30
,rec.last_update_date
,rec.last_updated_by
,rec.last_update_login
,rec.created_by
,rec.creation_date
,rec.object_version_number
,rec.user_category_name
);
INSERT INTO hr_s_report_format_mappings_f
( report_type ,
report_qualifier ,
report_format ,
effective_start_date ,
effective_end_date ,
range_code ,
assignment_action_code ,
initialization_code ,
archive_code ,
magnetic_code ,
report_category ,
report_name ,
sort_code ,
updatable_flag ,
deinitialization_code ,
legislation_code ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
temporary_action_flag
)
VALUES
( rec.report_type ,
p_legislation_code ,
rec.report_format ,
rec.effective_start_date ,
rec.effective_end_date ,
rec.range_code ,
rec.assignment_action_code ,
rec.initialization_code ,
rec.archive_code ,
rec.magnetic_code ,
rec.report_category ,
rec.report_name ,
rec.sort_code ,
rec.updatable_flag ,
rec.deinitialization_code ,
rec.legislation_code ,
rec.last_update_date ,
rec.last_updated_by ,
rec.last_update_login ,
rec.created_by ,
rec.creation_date ,
rec.temporary_action_flag
);
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';
UPDATE hr_s_report_format_mappings_f
SET report_qualifier = p_legislation_code
WHERE report_qualifier = 'ZZ';
UPDATE hr_s_balance_categories_f
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 ;
SELECT report_format_mapping_id
FROM pay_report_format_mappings_f
WHERE report_qualifier = p_legislation_code
AND report_type = 'IP_PAYROLL_ARCHIVE'
AND report_format = 'IP_PAYROLL_ARCHIVE'
AND sysdate BETWEEN effective_start_date AND effective_end_date;
SELECT prfp.parameter_name, prfp.parameter_value
FROM pay_report_format_parameters prfp, pay_report_format_mappings_f prfm
WHERE prfp.report_format_mapping_id = prfm.report_format_mapping_id
AND prfm.report_qualifier = 'ZZ'
AND prfm.business_group_id IS NULL;
SELECT PAY_REPORT_FORMAT_MAPPINGS_S.nextval
INTO ln_report_mapping_id FROM dual;
UPDATE pay_report_format_mappings_f
SET report_format_mapping_id = ln_report_mapping_id
WHERE report_qualifier = p_legislation_code
AND report_type = 'IP_PAYROLL_ARCHIVE'
AND report_format = 'IP_PAYROLL_ARCHIVE'
AND sysdate BETWEEN effective_start_date AND effective_end_date;
UPDATE pay_report_format_parameters
SET parameter_value = rec.parameter_value
WHERE report_format_mapping_id = ln_report_mapping_id
AND parameter_name = rec.parameter_name;
INSERT INTO pay_report_format_parameters
(report_format_mapping_id, parameter_name, parameter_value)
VALUES
(ln_report_mapping_id, rec.parameter_name, rec.parameter_value);
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_categories_tl
(p_legislation_code IN VARCHAR2) IS
CURSOR get_balcat_btable_csr IS
SELECT b.balance_category_id bid , t.user_category_name tname,b.user_category_name bname,
t.language, t.source_lang
FROM pay_balance_categories_f_tl t, pay_balance_categories_f b
WHERE b.balance_category_id = t.balance_category_id
AND b.legislation_code = 'ZZ'
AND b.business_group_id IS NULL;
SELECT t.balance_category_id tlid
FROM pay_balance_categories_f b, pay_balance_categories_f_tl t
WHERE b.user_category_name = l_name
AND b.legislation_code = l_legislation_code
AND b.business_group_id is NULL
AND t.balance_category_id = b.balance_category_id
AND t.language = l_language;
hr_utility.set_location('pay_ip_startup_util.update_bal_categories_tl',10);
UPDATE pay_balance_categories_f_tl
SET user_category_name = l_record.tname,
source_lang = l_record.source_lang
WHERE balance_category_id = rec_tltable_csr.tlid
AND language = l_record.language;
hr_utility.set_location('pay_ip_startup_util.update_bal_categories_tl',20);
END update_bal_categories_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 retro_definition_id
FROM pay_retro_definitions
WHERE short_name = p_short_name
AND legislation_code = p_legislation_code;
SELECT pay_retro_definitions_s.nextval
FROM dual;
INSERT INTO pay_retro_definitions
(retro_definition_id
,short_name
,definition_name
,legislation_code)
VALUES
(l_retro_definition_id
,p_short_name
,p_definition_name
,p_legislation_code);
SELECT retro_component_id
FROM pay_retro_components
WHERE short_name = p_short_name
AND legislation_code = p_legislation_code;
SELECT pay_retro_components_s.nextval
FROM dual;
INSERT INTO pay_retro_components
(retro_component_id
,short_name
,component_name
,retro_type
,legislation_code
,recalculation_style
,date_override_procedure)
VALUES
(l_retro_component_id
,p_short_name
,p_component_name
,p_retro_type
,p_legislation_code
,p_recalc_style
,p_date_override_proc);
UPDATE pay_retro_components
SET component_name = p_component_name
, retro_type = p_retro_type
, recalculation_style = p_recalc_style
, date_override_procedure = p_date_override_proc
WHERE retro_component_id = l_retro_component_id;
SELECT definition_component_id
FROM pay_retro_defn_components
WHERE retro_definition_id = p_retro_definition_id
AND retro_component_id = p_retro_component_id;
SELECT pay_retro_defn_components_s.nextval
from dual;
INSERT INTO pay_retro_defn_components
(definition_component_id
,retro_definition_id
,retro_component_id
,priority)
VALUES
(l_definition_component_id
,p_retro_definition_id
,p_retro_component_id
,p_priority);
UPDATE pay_retro_defn_components
SET priority = p_priority
WHERE definition_component_id = l_definition_component_id
AND retro_definition_id = p_retro_definition_id
AND retro_component_id = p_retro_component_id;
SELECT time_definition_id
FROM pay_time_definitions
WHERE short_name = p_short_name
AND period_type = p_period_type
AND legislation_code = p_legislation_code;
SELECT pay_time_definitions_s.nextval
from dual;
INSERT INTO pay_time_definitions
(time_definition_id
,short_name
,definition_name
,period_type
,period_unit
,day_adjustment
,dynamic_code
,business_group_id
,legislation_code)
VALUES
(l_time_definition_id
,p_short_name
,p_definition_name
,p_period_type
,p_period_unit
,p_day_adjustment
,p_dynamic_code
,null
,p_legislation_code);
UPDATE pay_time_definitions
SET definition_name = p_definition_name
, period_unit = p_period_unit
, day_adjustment = p_day_adjustment
, dynamic_code = p_dynamic_code
WHERE time_definition_id = l_time_definition_id;
SELECT time_span_id
FROM pay_time_spans
WHERE creator_id = p_creator_id
AND creator_type = p_creator_type
AND start_time_def_id = p_start_time_def_id
AND end_time_def_id = p_end_time_def_id;
select pay_time_spans_s.nextval
from dual;
INSERT INTO pay_time_spans
(time_span_id
,creator_id
,creator_type
,start_time_def_id
,end_time_def_id)
VALUES(l_time_span_id
, p_creator_id
, p_creator_type
, p_start_time_def_id
, p_end_time_def_id);
UPDATE pay_time_spans
SET start_time_def_id = p_start_time_def_id
, end_time_def_id = p_end_time_def_id
WHERE time_span_id = l_time_span_id;
hr_utility.trace('Error: While inserting time spans : ' || sqlerrm);
Insert new time definitions and time spans required for the new localisation
*/
l_start_time_id := create_time_definitions
(p_legislation_code => p_legislation_code
,p_short_name => 'START_OF_TIME'
,p_definition_name => 'Start of Time'
,p_period_type => 'START_OF_TIME'
,p_period_unit => '0'
,p_day_adjustment => 'CURRENT'
,p_dynamic_code => null);
SELECT 'Y'
FROM pay_upgrade_definitions pud
WHERE pud.short_name = p_short_name
AND pud.legislation_code = p_legislation_code;
SELECT pud.short_name,
pud.name,
pud.description,
pud.upgrade_level,
pud.criticality,
pud.threading_level,
pud.failure_point,
pud.legislatively_enabled,
pud.upgrade_method,
pud.upgrade_procedure,
pud.qualifying_procedure,
pud.owner_application_id
FROM pay_upgrade_definitions pud
WHERE pud.short_name = p_short_name
AND pud.legislation_code = 'ZZ';
pay_upgrade_definitions_pkg.insert_row(
P_SHORT_NAME => replace(lv_short_name,'ZZ',upper(p_legislation_code)),
P_NAME => lv_name,
P_DESCRIPTION => lv_description,
P_LEGISLATION_CODE => p_legislation_code,
P_UPGRADE_LEVEL => lv_upgrade_level,
P_CRITICALITY => lv_criticality,
P_FAILURE_POINT => lv_failure_point,
P_LEGISLATIVELY_ENABLED => lv_leg_enabled,
P_UPGRADE_PROCEDURE => lv_upgrade_proc,
P_THREADING_LEVEL => lv_threading_level,
P_UPGRADE_METHOD => lv_upgrade_method,
P_QUALIFYING_PROCEDURE => lv_qualifying_proc,
P_OWNER_APPL_ID => ln_own_appl_id,
P_FIRST_PATCHSET => null,
P_VALIDATE_CODE => null,
P_ADDITIONAL_INFO => null,
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATED_BY => 1,
P_LAST_UPDATE_LOGIN => 1,
P_CREATED_BY => 1,
P_CREATION_DATE => sysdate,
P_UPGRADE_DEFINITION_ID => ln_upg_id);
select 'Y' from
fnd_form_functions fff,
fnd_menu_entries fme,
fnd_menus fmu
where fff.FUNCTION_ID = fme.function_id
and fme.menu_id = fmu.menu_id
and fff.function_name = cp_function_name
and fmu.menu_name = cp_menu_name;
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;
DELETE FROM HR_STU_EXCEPTIONS;
pay_ip_utility.insert_all_ownerships;
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);
update_bal_categories_tl(p_legislation_code);
write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_IP_UTILITY.INSERT_BAL_LOOKUP_VAL',NULL);
pay_ip_utility.insert_bal_lookup_val(p_legislation_code);
write_log('LOG','PAY_34016_IP_CALL_PROC','PAY_IP_UTILITY.INSERT_PATCH_STATUS',NULL);
pay_ip_utility.insert_patch_status(
p_patch_name => lv_patch_name,
p_patch_number => ln_patch_number,
p_patch_desc => lv_patch_desc,
p_patch_status => lv_status,
p_leg_code => p_legislation_code,
p_appl_release => lv_appl_release);