The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from per_business_groups pbg,
hr_legislation_installations hli
where business_group_id = cp_business_grp_id
and pbg.legislation_code = hli.legislation_code;
SELECT 'Y'
FROM hr_legislation_installations hli
WHERE hli.legislation_code =cp_legislation_code
AND APPLICATION_SHORT_NAME ='PAY'
AND STATUS='I';
select APPLICATION_ID
from FND_APPLICATION
where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;
SELECT balance_name
INTO lv_balance_name
FROM pay_balance_types
WHERE balance_type_id = p_balance_type_id;
select fdfc.application_id,
fdfc.DESCRIPTIVE_FLEXFIELD_NAME
,fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE
,fdfc.ENABLED_FLAG
,fdfc.GLOBAL_FLAG
--,fdfc.SECURITY_GROUP_ID
,fdfctl.DESCRIPTIVE_FLEX_CONTEXT_NAME
,fdfctl.DESCRIPTION
,fdfctl.language
from fnd_descr_flex_contexts fdfc,
fnd_descr_flex_contexts_tl fdfctl
where fdfc.APPLICATION_ID = fdfctl.APPLICATION_ID
AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE = fdfctl.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME = fdfctl.DESCRIPTIVE_FLEXFIELD_NAME
AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME = 'Element Developer DF'
AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE IN l_list_of_ctxt := '('ZZ_SUPPLEMENTAL EARNINGS','ZZ_EARNINGS','ZZ_TAXABLE BENEFITS'
,'ZZ_DIRECT PAYMENT','ZZ_TAX DEDUCTIONS','ZZ_PRE-TAX DEDUCTIONS'
,'ZZ_VOLUNTARY DEDUCTIONS','ZZ_INVOLUNTARY DEDUCTIONS')'*/
CURSOR c_get_segments(cp_context_code varchar2) IS
select b.APPLICATION_ID
,b.DESCRIPTIVE_FLEXFIELD_NAME
,b.DESCRIPTIVE_FLEX_CONTEXT_CODE
,b.APPLICATION_COLUMN_NAME
,b.END_USER_COLUMN_NAME
,b.COLUMN_SEQ_NUM
,b.ENABLED_FLAG
,b.REQUIRED_FLAG
,b.SECURITY_ENABLED_FLAG
,b.DISPLAY_FLAG
,b.DISPLAY_SIZE
,b.MAXIMUM_DESCRIPTION_LEN
,b.CONCATENATION_DESCRIPTION_LEN
,b.FLEX_VALUE_SET_ID
,b.RANGE_CODE
,b.DEFAULT_TYPE
,b.DEFAULT_VALUE
,b.SRW_PARAM
,t.FORM_LEFT_PROMPT
,t.FORM_ABOVE_PROMPT
,t.DESCRIPTION
,t.language
FROM fnd_descr_flex_col_usage_tl t
,fnd_descr_flex_column_usages b
WHERE b.application_id = t.application_id
AND b.descriptive_flexfield_name = t.descriptive_flexfield_name
AND b.descriptive_flex_context_code = t.descriptive_flex_context_code
AND b.application_column_name = t.application_column_name
AND t.language = userenv ('LANG')
AND b.descriptive_flexfield_name = p_dff_name --'Element Developer DF'
AND b.descriptive_flex_context_code = cp_context_code;
SELECT flex_value_set_name
FROM fnd_flex_value_sets
WHERE FLEX_VALUE_SET_ID = c_val_set_id;
lv_sql_stmt := 'select fdfc.application_id,
fdfc.DESCRIPTIVE_FLEXFIELD_NAME
,fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE
,fdfc.ENABLED_FLAG
,fdfc.GLOBAL_FLAG
--,fdfc.SECURITY_GROUP_ID
,fdfctl.DESCRIPTIVE_FLEX_CONTEXT_NAME
,fdfctl.DESCRIPTION
,fdfctl.language
from fnd_descr_flex_contexts fdfc,
fnd_descr_flex_contexts_tl fdfctl
where fdfc.APPLICATION_ID = fdfctl.APPLICATION_ID
AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE = fdfctl.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME = fdfctl.DESCRIPTIVE_FLEXFIELD_NAME
AND fdfctl.language = userenv (''LANG'')
AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME ='''||p_dff_name|| '''
AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE IN (' ||p_list_of_ctxt_string|| ')' ;
fnd_flex_dsc_api.update_context(
p_appl_short_name => p_appl_short_name
,p_flexfield_name => lv_desc_flexfield_name
,p_desc_flex_context_code =>lv_new_context_name
,p_desc_flex_context_name => lv_new_context_name
,p_description => lv_description
,p_enabled_flag => lv_enabled_flag
,p_language => lv_language );
update_flex_col_usages_tl(lv_desc_flex_context_code,lv_new_context_name,p_dff_name);
PROCEDURE update_flex_col_usages_tl(P_SOURCE_CONTEXT_CODE IN VARCHAR2
,P_DEST_CONTEXT_CODE IN VARCHAR2
,p_dff_name IN varchar2) IS
CURSOR c_get_segments_ol(cp_context_code IN VARCHAR2) IS
select b.APPLICATION_ID
,b.DESCRIPTIVE_FLEXFIELD_NAME
,b.DESCRIPTIVE_FLEX_CONTEXT_CODE
,b.APPLICATION_COLUMN_NAME
,t.FORM_LEFT_PROMPT
,t.FORM_ABOVE_PROMPT
,t.DESCRIPTION
,t.language
FROM fnd_descr_flex_col_usage_tl t
,fnd_descr_flex_column_usages b
WHERE b.application_id = t.application_id
AND b.descriptive_flexfield_name = t.descriptive_flexfield_name
AND b.descriptive_flex_context_code = t.descriptive_flex_context_code
AND b.application_column_name = t.application_column_name
AND t.language <> userenv ('LANG')
AND b.descriptive_flexfield_name = p_dff_name --'Element Developer DF'
AND b.descriptive_flex_context_code = cp_context_code;
hr_utility.trace('Entered pay_ip_utility.update_flex_col_usages_tl');
hr_utility.trace('Entered pay_ip_utility.update_flex_col_usages_tl --For Loop');
UPDATE fnd_descr_flex_col_usage_tl SET
FORM_LEFT_PROMPT = rec.FORM_LEFT_PROMPT
,FORM_ABOVE_PROMPT = rec.FORM_ABOVE_PROMPT
,DESCRIPTION = rec.DESCRIPTION
WHERE APPLICATION_ID = rec.APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = rec.DESCRIPTIVE_FLEXFIELD_NAME
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = P_DEST_CONTEXT_CODE
AND APPLICATION_COLUMN_NAME = rec.APPLICATION_COLUMN_NAME
AND LANGUAGE = rec.LANGUAGE;
hr_utility.trace('Updated fnd_descr_flex_col_usage_tl = '||SQL%ROWCOUNT);
END update_flex_col_usages_tl;
SELECT org_information_type FROM HR_ORG_INFORMATION_TYPES
WHERE org_information_type = cp_org_info_type
AND legislation_code = cp_legislation_code;
lv_LAST_UPDATE_DATE DATE;
lv_LAST_UPDATED_BY NUMBER;
lv_LAST_UPDATE_LOGIN NUMBER;
lv_LAST_UPDATED_BY := 0;
lv_LAST_UPDATE_DATE := sysdate;
lv_LAST_UPDATE_LOGIN := 0;
HR_ORG_INFORMATION_TYPES_PKG.UPDATE_ROW
( X_ORG_INFORMATION_TYPE => p_ORG_INFORMATION_TYPE
,X_DESTINATION => p_DESTINATION
,X_LEGISLATION_CODE => p_LEGISLATION_CODE
,X_NAVIGATION_METHOD => p_NAVIGATION_METHOD
,X_FND_APPLICATION_ID => lv_APPLICATION_ID
,X_DESCRIPTION => p_DESCRIPTION
,X_DISPLAYED_ORG_INFORMATION_TP => p_DISP_ORG_INFORMATION_TYPE
,X_LAST_UPDATE_DATE => lv_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY => lv_LAST_UPDATED_BY
,X_LAST_UPDATE_LOGIN => lv_LAST_UPDATE_LOGIN);
HR_ORG_INFORMATION_TYPES_PKG.INSERT_ROW
( X_ORG_INFORMATION_TYPE => p_ORG_INFORMATION_TYPE
,X_DESTINATION => p_DESTINATION
,X_LEGISLATION_CODE => p_LEGISLATION_CODE
,X_NAVIGATION_METHOD => p_NAVIGATION_METHOD
,X_FND_APPLICATION_ID => lv_APPLICATION_ID
,X_DESCRIPTION => p_DESCRIPTION
,X_DISPLAYED_ORG_INFORMATION_TP => p_DISP_ORG_INFORMATION_TYPE
,X_CREATION_DATE => lv_CREATION_DATE
,X_CREATED_BY => lv_CREATED_BY
,X_LAST_UPDATE_DATE => lv_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY => lv_LAST_UPDATED_BY
,X_LAST_UPDATE_LOGIN => lv_LAST_UPDATE_LOGIN);
PROCEDURE delete_org_info_type_by_class
(p_ORG_CLASSIFICATION VARCHAR2
,p_ORG_INFORMATION_TYPE VARCHAR2) IS
l_row_exists NUMBER := 0;
SELECT count(*)
INTO l_row_exists
FROM hr_org_info_types_by_class
WHERE org_classification = p_ORG_CLASSIFICATION
AND org_information_type = p_ORG_INFORMATION_TYPE;
hr_org_info_types_by_class_pkg.delete_row(p_ORG_CLASSIFICATION
,p_ORG_INFORMATION_TYPE);
END delete_org_info_type_by_class;
PROCEDURE delete_org_info_type
(p_ORG_INFORMATION_TYPE VARCHAR2) IS
l_row_exists NUMBER := 0;
SELECT count(*)
INTO l_row_exists
FROM hr_org_information_types
WHERE org_information_type = p_ORG_INFORMATION_TYPE;
hr_org_information_types_pkg.delete_row(p_ORG_INFORMATION_TYPE);
END delete_org_info_type;
SELECT database_item_suffix, legislation_code, exception_report_period,
last_updated_by, last_update_date, last_update_login,
created_by, creation_date, object_version_number, year_begin_date
FROM pqp_exception_report_suffix
WHERE legislation_code = 'ZZ'
AND ((cp_install_tax_unit='N' AND INSTR(database_item_suffix,'_TU_') = 0)
OR cp_install_tax_unit='Y');
UPDATE pqp_exception_report_suffix
SET exception_report_period = rec.exception_report_period
,last_updated_by = rec.last_updated_by
,last_update_date = sysdate
,last_update_login = rec.last_update_login
,year_begin_date = fnd_date.canonical_to_date(p_tax_year)
WHERE database_item_suffix = rec.database_item_suffix
AND legislation_code = p_legislation_code;
hr_utility.trace('Inserting Database Item Suffix :'||rec.database_item_suffix);
INSERT INTO PQP_EXCEPTION_REPORT_SUFFIX
(database_item_suffix
, legislation_code
, exception_report_period
, last_updated_by
, last_update_date
, last_update_login
, year_begin_date
)
VALUES ( rec.database_item_suffix
, p_legislation_code
, rec.exception_report_period
, rec.last_updated_by
, sysdate
, rec.last_update_login
, fnd_date.canonical_to_date(p_tax_year)
) ;
SELECT decode(ppa_pymt.action_type,
'M', 'Direct Deposit',
paa_pymt.serial_number),ppa_pymt.action_type
FROM pay_pre_payments ppp,
pay_assignment_actions paa_pymt,
pay_payroll_actions ppa_pymt,
pay_action_interlocks pai
WHERE pai.locked_action_id = cp_pre_payment_action
AND paa_pymt.assignment_action_id = pai.locking_action_id
AND ppa_pymt.payroll_action_id = paa_pymt.payroll_action_id
AND ppa_pymt.action_type in ('M','H', 'E')
AND paa_pymt.pre_payment_id = cp_pre_payment_id
AND ppp.pre_payment_id = paa_pymt.pre_payment_id
AND NOT EXISTS (
SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai_void
WHERE pai_void.locked_action_id = paa_pymt.assignment_action_id --Void
/* Check if the locking is that of Void Pymt */
AND pai_void.locking_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.action_status = 'C'
AND ppa.action_type = 'D');
PROCEDURE insert_fnd_lookup_val
( p_lookup_type in varchar2
, p_lookup_code in varchar2
, p_territory_code in varchar2
, p_enabled_flag in varchar2
, p_meaning in varchar2
, p_description in varchar2
, p_start_date_active in DATE
, p_end_date_active in DATE
)
IS
l_row_id varchar2(100);
hr_utility.set_location('Entering ... '||g_package_name||'INSERT_FND_LOOKUP_VAL',1);
SELECT count(*)
INTO l_count
FROM FND_LOOKUP_values
WHERE lookup_type = P_LOOKUP_TYPE
AND lookup_code = P_LOOKUP_CODE
AND security_group_id = fnd_global.lookup_security_group(P_LOOKUP_TYPE,3);
hr_utility.set_location('inside FND_LOOKUP_VAL_INSERT',5);
SELECT vsize(P_DESCRIPTION)
INTO lenb_desc
FROM dual;
SELECT vsize(L_DESCRIPTION)
INTO lenb_desc FROM dual;
FND_LOOKUP_VALUES_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_LOOKUP_TYPE => P_LOOKUP_TYPE,
X_SECURITY_GROUP_ID => fnd_global.lookup_security_group(P_LOOKUP_TYPE,3),
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => P_LOOKUP_CODE,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_TAG => null,
X_ENABLED_FLAG => P_ENABLED_FLAG,
X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
X_TERRITORY_CODE => P_TERRITORY_CODE,
X_MEANING => P_MEANING,
X_DESCRIPTION => L_DESCRIPTION,
X_CREATION_DATE => trunc(sysdate),
X_CREATED_BY => 1,
X_LAST_UPDATE_DATE => trunc(sysdate),
X_LAST_UPDATED_BY => 1,
X_LAST_UPDATE_LOGIN => 0);
hr_utility.set_location('After fnd_lookup_pkg.insert_row',110);
hr_utility.set_location('Leaving ... '||g_package_name||'INSERT_FND_LOOKUP_VAL',110);
END insert_fnd_lookup_val;
PROCEDURE insert_bal_lookup_val (p_legislation_code VARCHAR2)
IS
BEGIN
hr_utility.set_location('Entering ... '||g_package_name||'INSERT_BAL_LOOKUP_VAL',10);
INSERT_FND_LOOKUP_VAL
( P_LOOKUP_TYPE => 'LOCAL_BAL_FORM_NAME'
, P_LOOKUP_CODE => P_LEGISLATION_CODE||'_EMPVIEWBALSS'
, P_TERRITORY_CODE => P_LEGISLATION_CODE
, P_ENABLED_FLAG => 'Y'
, P_MEANING => 'View Employee Balances ('||P_LEGISLATION_CODE||')'
, P_DESCRIPTION => NULL
, P_START_DATE_ACTIVE => NULL
, P_END_DATE_ACTIVE => NULL
);
INSERT_FND_LOOKUP_VAL
( P_LOOKUP_TYPE => 'LOCAL_BAL_FORM_NAME'
, P_LOOKUP_CODE => P_LEGISLATION_CODE||'_STANDARD'
, P_TERRITORY_CODE => P_LEGISLATION_CODE
, P_ENABLED_FLAG => 'Y'
, P_MEANING => 'Standard Balances Window ('||P_LEGISLATION_CODE||')'
, P_DESCRIPTION => NULL
, P_START_DATE_ACTIVE => NULL
, P_END_DATE_ACTIVE => NULL
);
hr_utility.set_location('Leaving ... '||g_package_name||'INSERT_BAL_LOOKUP_VAL',40);
END insert_bal_lookup_val;
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name = p_application_short_name;
SELECT *
INTO x_dff_flx
FROM fnd_descriptive_flexs
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name;
SELECT *
INTO x_kff_str
FROM fnd_id_flex_structures
WHERE application_id = p_application_id
AND id_flex_code = p_id_kff_flx_code
AND id_flex_structure_code = p_id_flex_structure_code;
** Name : delete_compiled_data
** Purpose : This function deletes all the compiled data of DFF
KFF Structure.
**********************************************************************/
PROCEDURE delete_compiled_data(p_mode IN VARCHAR2,
p_application_id IN NUMBER,
p_descriptive_flexfield_name IN VARCHAR2 DEFAULT NULL,
p_id_flex_code IN VARCHAR2 DEFAULT NULL,
p_id_flex_num IN NUMBER DEFAULT NULL)
IS
l_func_name VARCHAR2(80);
l_func_name := g_package_name || 'delete_compiled_data()';
DELETE FROM fnd_compiled_descriptive_flexs fcdf
WHERE fcdf.application_id = p_application_id
AND fcdf.descriptive_flexfield_name = p_descriptive_flexfield_name;
DELETE FROM fnd_compiled_id_flex_structs fcifs
WHERE fcifs.application_id = p_application_id
AND fcifs.id_flex_code = p_id_flex_code
AND fcifs.id_flex_num = p_id_flex_num;
DELETE FROM fnd_compiled_id_flexs fcif
WHERE fcif.application_id = p_application_id
AND fcif.id_flex_code = p_id_flex_code;
END delete_compiled_data;
SELECT request_id
INTO l_request_id
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_application fa
WHERE fa.application_short_name = p_application_short_name
AND fcp.application_id = fa.application_id
AND fcp.concurrent_program_name = p_concurrent_program_name
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.status_code in ('I', -- ' Normal'
'Q', -- 'StandBy'
'R') -- ' Normal'
AND fcr.phase_code = 'P' -- 'Pending'
--
-- p_argument1 is mandatory and cannot be NULL
--
AND nvl(fcr.argument1, l_nvl_value) = p_argument1
--
-- Other arguments are optional and can be NULL
--
AND ((p_argument_count < 2) OR
(nvl(fcr.argument2, l_nvl_value) = nvl(l_argument2, l_nvl_value)))
AND ((p_argument_count < 3) OR
(nvl(fcr.argument3, l_nvl_value) = nvl(l_argument3, l_nvl_value)))
AND ((p_argument_count < 4) OR
(nvl(fcr.argument4, l_nvl_value) = nvl(l_argument4, l_nvl_value)))
AND ((p_argument_count < 5) OR
(nvl(fcr.argument5, l_nvl_value) = nvl(l_argument5, l_nvl_value)))
AND ROWNUM = 1;
delete_compiled_data(p_mode => p_mode,
p_application_id => l_dff_flx.application_id,
p_descriptive_flexfield_name => p_descriptive_flexfield_name);
delete_compiled_data(p_mode => p_mode,
p_application_id => l_kff_flx.application_id,
p_id_flex_code => p_id_flex_code,
p_id_flex_num => l_kff_str.id_flex_num);
PROCEDURE insert_ownership(p_key_name IN VARCHAR2
,p_product_name IN VARCHAR2
,p_key_value IN NUMBER
)
IS
BEGIN
INSERT INTO hr_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_application_ownerships
WHERE product_name = p_product_name
AND key_name = p_key_name
AND key_value = p_key_value
)
);
END insert_ownership;
SELECT classification_id
,classification_name
FROM pay_element_classifications
WHERE legislation_code = 'ZZ';
insert_ownership('CLASSIFICATION_ID','PAY',rec.classification_id);
insert_ownership('CLASSIFICATION_ID','PER',rec.classification_id);
SELECT balance_type_id,
balance_name
FROM pay_balance_types
WHERE legislation_code = 'ZZ';
insert_ownership('BALANCE_TYPE_ID','PAY',rec.balance_type_id);
insert_ownership('BALANCE_TYPE_ID','PER',rec.balance_type_id);
SELECT balance_dimension_id,
dimension_name
FROM pay_balance_dimensions
WHERE legislation_code = 'ZZ';
insert_ownership('BALANCE_DIMENSION_ID','PAY',rec.balance_dimension_id);
insert_ownership('BALANCE_DIMENSION_ID','PER',rec.balance_dimension_id);
SELECT pdb.defined_balance_id,
replace(upper(pbt.balance_name),' ','_') || pbd.database_item_suffix def_bal_name
FROM pay_defined_balances pdb
,pay_balance_dimensions pbd
,pay_balance_types pbt
WHERE pdb.legislation_code = 'ZZ'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.legislation_code = pbt.legislation_code
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.legislation_code = pbd.legislation_code;
insert_ownership('DEFINED_BALANCE_ID','PAY',rec.defined_balance_id);
insert_ownership('DEFINED_BALANCE_ID','PER',rec.defined_balance_id);
SELECT route.route_id,
route.route_name
FROM ff_routes route
,pay_balance_dimensions pbd
WHERE pbd.legislation_code = 'ZZ'
AND pbd.route_id = route.route_id
UNION
SELECT route.route_id,
route.route_name
FROM ff_routes route
,pay_balance_dimensions pbd,
pay_dimension_routes pdr
WHERE pbd.legislation_code = 'ZZ'
AND pbd.balance_dimension_id = pdr.balance_dimension_id
AND pdr.route_id = route.route_id;
insert_ownership('ROUTE_ID','PAY',rec.route_id);
insert_ownership('ROUTE_ID','PER',rec.route_id);
SELECT balance_category_id
,category_name
FROM pay_balance_categories_f
WHERE legislation_code = 'ZZ';
insert_ownership('BALANCE_CATEGORY_ID','PAY',rec.balance_category_id);
insert_ownership('BALANCE_CATEGORY_ID','PER',rec.balance_category_id);
PROCEDURE insert_all_ownerships IS
BEGIN
hr_utility.set_location('pay_ip_utility.insert_all_ownerships',10);
hr_utility.set_location('pay_ip_utility.insert_all_ownerships',20);
END insert_all_ownerships;
SELECT status
INTO lv_patch_status
FROM pay_patch_status
WHERE patch_name = p_patch_name
AND status = nvl(p_patch_status,status)
AND legislation_code = p_leg_code
AND ((p_appl_release = nvl(application_release,p_appl_release)) OR
(p_appl_release = 'ANY'));
** Name : insert_patch_status
** Purpose : This procedure inserts a given patch
** in table pay_patch_status
**********************************************************************/
PROCEDURE insert_patch_status
(p_patch_number IN VARCHAR2
,p_patch_name IN VARCHAR2
,p_patch_desc IN VARCHAR2
,p_patch_status IN VARCHAR2
,p_appl_release IN VARCHAR2
,p_leg_code IN VARCHAR2
)
IS
BEGIN
hr_utility.set_location('Entering... pay_ip_utility.insert_patch',10);
INSERT INTO pay_patch_status
(ID,
PATCH_NUMBER,
PATCH_NAME,
APPLIED_DATE,
STATUS,
DESCRIPTION,
APPLICATION_RELEASE,
LEGISLATION_CODE
)
VALUES
(PAY_PATCH_STATUS_S.nextval,
p_patch_number,
p_patch_name,
sysdate,
p_patch_status,
p_patch_desc,
decode(p_appl_release,'ANY',null,p_appl_release),
p_leg_code
);
hr_utility.set_location('Leaving... pay_ip_utility.insert_patch',20);
END insert_patch_status;
SELECT 'Y'
INTO lv_rbr_enabled
FROM pay_legislation_rules
WHERE legislation_code = 'ZZ'
AND rule_type = 'SAVE_ASG_RUN_BAL'
AND rule_mode = 'Y';