[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select STATE_CODE c_true_key
, FIPS_CODE
, NAME
, JURISDICTION_CODE
, HEAD_TAX_PERIOD
, WC_EXECUTIVE_WEEKLY_MAX
, FS_LOOKUP_TYPE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, rowid
from hr_s_state_rules;
delete from hr_s_state_rules
where rowid = stu_rec.rowid;
-- Check if a delivered row is needed and insert into the
-- live tables if it is
BEGIN
IF p_phase = 1 THEN
return;
-- #353225. See if the state information exists. If so then update it.
-- This is new code put in at the request of
-- US Pay - previously the code wouldn't handle updates - it
-- only inserted new rows. RMF 27-Mar-96.
--
update pay_state_rules
set FIPS_CODE = stu_rec.FIPS_CODE
, NAME = stu_rec.NAME
, JURISDICTION_CODE = stu_rec.JURISDICTION_CODE
, HEAD_TAX_PERIOD = stu_rec.HEAD_TAX_PERIOD
, FS_LOOKUP_TYPE = stu_rec.FS_LOOKUP_TYPE
, LAST_UPDATE_DATE = stu_rec.LAST_UPDATE_DATE
, LAST_UPDATED_BY = stu_rec.LAST_UPDATED_BY
, LAST_UPDATE_LOGIN = stu_rec.LAST_UPDATE_LOGIN
, CREATED_BY = stu_rec.CREATED_BY
, CREATION_DATE = stu_rec.CREATION_DATE
where state_code = stu_rec.c_true_key;
-- Row does not exist so insert
insert into pay_state_rules
(STATE_CODE
,FIPS_CODE
,NAME
,JURISDICTION_CODE
,HEAD_TAX_PERIOD
,WC_EXECUTIVE_WEEKLY_MAX
,FS_LOOKUP_TYPE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
)
values
(stu_rec.c_true_key
,stu_rec.FIPS_CODE
,stu_rec.NAME
,stu_rec.JURISDICTION_CODE
,stu_rec.HEAD_TAX_PERIOD
,stu_rec.WC_EXECUTIVE_WEEKLY_MAX
,stu_rec.FS_LOOKUP_TYPE
,stu_rec.LAST_UPDATE_DATE
,stu_rec.LAST_UPDATED_BY
,stu_rec.LAST_UPDATE_LOGIN
,stu_rec.CREATED_BY
,stu_rec.CREATION_DATE
);
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select jurisdiction_code
, tax_type
, tax_category
, classification_id
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
, legislation_code
, taxability_rules_date_id
from hr_s_taxability_rules;
select legislation_code
from hr_s_history;
delete from hr_s_taxability_rules
where rowid = stu_rec.rowid;
-- Check if a delivered row is needed and insert into the
-- live tables if it is
BEGIN
-- See if the taxability information exists. If so then remove from the
-- delivery tables, otherwise insert into the live table.
/* *** Added by RAMURTHY *** */
BEGIN
select taxability_rules_date_id
into l_trd_id
from pay_taxability_rules_dates
where legislation_code = stu_rec.legislation_code
and trunc(valid_date_from) = trunc(to_date('0001/01/01', 'YYYY/MM/DD'))
and trunc(valid_date_to) = trunc(to_date('4712/12/31', 'YYYY/MM/DD'));
select pay_taxability_rules_dates_s.nextval
into l_trd_id
from dual;
insert into pay_taxability_rules_dates
( taxability_rules_date_id,
valid_date_from,
valid_date_to,
legislation_code)
Values
( l_trd_id,
to_date('0001/01/01', 'YYYY/MM/DD'),
to_date('4712/12/31', 'YYYY/MM/DD'),
stu_rec.legislation_code);
select null
into l_null_return
from pay_taxability_rules
where jurisdiction_code = stu_rec.jurisdiction_code
and tax_type = stu_rec.tax_type
and classification_id = stu_rec.classification_id
and tax_category = stu_rec.tax_category;
-- Needs to be inserted
IF p_phase = 1 THEN
return;
insert into pay_taxability_rules
(jurisdiction_code
,tax_type
,tax_category
,classification_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
/* *** */ ,legislation_code
/* *** */ ,taxability_rules_date_id
)
values
(stu_rec.jurisdiction_code
,stu_rec.tax_type
,stu_rec.tax_category
,stu_rec.classification_id
,stu_rec.last_update_date
,stu_rec.last_updated_by
,stu_rec.last_update_login
,stu_rec.created_by
,stu_rec.creation_date
/* *** */ ,stu_rec.legislation_code
/* *** */ ,l_trd_id
);
select count(*)
into l_num_rules
from pay_taxability_rules
where legislation_code = l_leg_code;
delete from hr_s_taxability_rules
where legislation_code = l_leg_code;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select SURCHARGE_ID
, STATE_CODE
, ADD_TO_RT
, NAME
, POSITION
, RATE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, rowid
from hr_s_wc_state_surcharges;
delete from hr_s_wc_state_surcharges
where rowid = stu_rec.rowid;
FUNCTION update_uid RETURN boolean
----------------------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
BEGIN
BEGIN
IF p_phase = 2 THEN
RETURN true;
select surcharge_id
, add_to_rt
, rate
, name
into l_new_surrogate_key
, l_add_to_rt
, l_rate
, l_name
from pay_wc_state_surcharges
where state_code = stu_rec.state_code
and position = stu_rec.position;
-- Delete delivered row
remove;
select pay_wc_state_surcharges_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_wc_state_surcharges
set surcharge_id = l_new_surrogate_key
where surcharge_id = stu_rec.surcharge_id;
END update_uid;
-- Attempt update first
update pay_wc_state_surcharges
set SURCHARGE_ID = stu_rec.SURCHARGE_ID
, STATE_CODE = stu_rec.STATE_CODE
, ADD_TO_RT = stu_rec.ADD_TO_RT
, NAME = stu_rec.NAME
, POSITION = stu_rec.POSITION
, RATE = stu_rec.RATE
, LAST_UPDATE_DATE = stu_rec.LAST_UPDATE_DATE
, LAST_UPDATED_BY = stu_rec.LAST_UPDATED_BY
, LAST_UPDATE_LOGIN = stu_rec.LAST_UPDATE_LOGIN
, CREATED_BY = stu_rec.CREATED_BY
, CREATION_DATE = stu_rec.CREATION_DATE
where surcharge_id = stu_rec.surcharge_id;
-- Row does not exist so insert
insert into pay_wc_state_surcharges
(SURCHARGE_ID
,STATE_CODE
,ADD_TO_RT
,NAME
,POSITION
,RATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
)
values
(pay_wc_state_surcharges_s.nextval -- changes for nextval
,stu_rec.STATE_CODE
,stu_rec.ADD_TO_RT
,stu_rec.NAME
,stu_rec.POSITION
,stu_rec.RATE
,stu_rec.LAST_UPDATE_DATE
,stu_rec.LAST_UPDATED_BY
,stu_rec.LAST_UPDATE_LOGIN
,stu_rec.CREATED_BY
,stu_rec.CREATION_DATE
);
-- Delete delivered row now it has been installed
remove;
IF update_uid THEN
transfer_row;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- selects all rows from startup entity
IS
select legislation_code
, rule_type
, rule_mode
, rowid
from hr_s_legislation_rules;
delete from hr_s_legislation_rules
where rowid = stu_rec.rowid;
-- Check if a delivered row is needed and insert into the
-- live tables if it is
-- The logic performed is simple. If the rule exists, compare the
-- value of the rule_mode. Update the rule mode if the the values
-- are different.
-- If the rule does not exist then insert the row into the live tables.
-- Updates and inserts only take place in phase 2.
BEGIN
BEGIN
-- Translate the structure_code to a id_flex_num value if the
-- rule_mode has been passed in as such
select distinct id_flex_num
into l_hrs_rule_mode
from fnd_id_flex_structures fifs
where fifs.id_flex_structure_code = stu_rec.rule_mode
and stu_rec.rule_type in ('E', 'S', 'CWK_S')
and decode(stu_rec.rule_type, 'E', 'BANK',
'S', 'SCL',
'CWK_S', 'SCL',
'X') = fifs.id_flex_code;
select rule_mode
into l_rule_mode
from pay_legislation_rules
where rule_type = stu_rec.rule_type
and legislation_code = stu_rec.legislation_code;
IF p_phase = 1 THEN --only update in phase 2
return;
update pay_legislation_rules
set rule_mode = l_hrs_rule_mode
where rule_type = stu_rec.rule_type
and legislation_code = stu_rec.legislation_code;
insert into pay_legislation_rules
(legislation_code
,rule_mode
,rule_type
)
values
(stu_rec.legislation_code
,l_hrs_rule_mode
,stu_rec.rule_type
);
insert_jit_data varchar2(1) := 'N';
cursor cti is select * from HR_S_US_CITY_TAX_INFO_F;
cursor coti is select * from HR_S_US_COUNTY_TAX_INFO_F;
cursor sti is select * from HR_S_US_STATE_TAX_INFO_F;
cursor fti is select * from HR_S_US_FEDERAL_TAX_INFO_F;
cursor ger is select * from HR_S_US_GARN_EXEMPTION_RULES_F;
cursor gfr is select * from HR_S_US_GARN_FEE_RULES_F
order by fee_rule_id;
cursor pgfr is select creator_type, garn_category, state_code
FROM PAY_US_GARN_FEE_RULES_F
WHERE sysdate between effective_start_date
and effective_end_date;
cursor glr is select * from HR_S_US_GARN_LIMIT_RULES_F;
cursor hrl is select * from HR_S_REPORT_LOOKUPS;
select count(*)
into l_patch_count
from pay_patch_status
where patch_name like 'JIT%';
DELETE FROM PAY_US_GARN_FEE_RULES_F
WHERE creator_type = 'SYSTEM'
AND 0 <> (SELECT COUNT(*) FROM
HR_S_US_GARN_FEE_RULES_F);
sqlstr := 'delete from '|| pay_tab(lc_cnt) || ' where ';
sqlstr := sqlstr || 'select count(*) from '|| hr_s_tab(lc_cnt)||' )';
insert into PAY_US_GARN_EXEMPTION_RULES_F
(
MIN_WAGE_FACTOR,
PRORATION_RULE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
EXEMPTION_RULE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
GARN_CATEGORY,
STATE_CODE,
ADDL_DEP_AMOUNT_VALUE,
AMOUNT_VALUE,
CALC_RULE,
CREATOR_TYPE,
DEPENDENTS_CALC_RULE,
DEPENDENT_AMOUNT_VALUE,
DI_PCT,
DI_PCT_DEPENDENTS,
DI_PCT_DEPENDENTS_IN_ARREARS,
DI_PCT_IN_ARREARS,
EXEMPTION_BALANCE,
EXEMPTION_BALANCE_MAX_PCT,
EXEMPTION_BALANCE_MIN_PCT,
MARITAL_STATUS)values
(
ger_rec.MIN_WAGE_FACTOR,
ger_rec.PRORATION_RULE,
ger_rec.LAST_UPDATE_DATE,
ger_rec.LAST_UPDATED_BY,
ger_rec.LAST_UPDATE_LOGIN,
ger_rec.CREATED_BY,
ger_rec.CREATION_DATE,
ger_rec.EXEMPTION_RULE_ID,
/* Since we have already deleted all rows from
pay_us_garn_exemption_rules_f, there cannot be any conflict of ids. It is
therefore safe to use the ids from the hr_s table. This will help in
maintainig the correct ids for date-tracked rows. After inserting
all the rows we will set the sequence to start at a value higher than the
max value of the id in pay_us_garn_exemption_rules_f.
PAY_US_GARN_EXEMPTION_RULES_S.nextval, -- changes for nextval */
ger_rec.EFFECTIVE_START_DATE,
ger_rec.EFFECTIVE_END_DATE,
ger_rec.GARN_CATEGORY,
ger_rec.STATE_CODE,
ger_rec.ADDL_DEP_AMOUNT_VALUE,
ger_rec.AMOUNT_VALUE,
ger_rec.CALC_RULE,
ger_rec.CREATOR_TYPE,
ger_rec.DEPENDENTS_CALC_RULE,
ger_rec.DEPENDENT_AMOUNT_VALUE,
ger_rec.DI_PCT,
ger_rec.DI_PCT_DEPENDENTS,
ger_rec.DI_PCT_DEPENDENTS_IN_ARREARS,
ger_rec.DI_PCT_IN_ARREARS,
ger_rec.EXEMPTION_BALANCE,
ger_rec.EXEMPTION_BALANCE_MAX_PCT,
ger_rec.EXEMPTION_BALANCE_MIN_PCT,
ger_rec.MARITAL_STATUS);
select max(exemption_rule_id)
into l_max_val
from pay_us_garn_exemption_rules_f;
select PAY_US_GARN_EXEMPTION_RULES_S.nextval
into l_next_val
from dual;
and to also avoid any conflict of ids with user update rows
( creator_type <> 'SYSTEM'), we need to compare the current
fee_rule_id with the row processed in the previous iteration.
If the ids are the same we will use currval from the sequence as
the new fee rule id else we will use nextval. */
if l_prev_seq = gfr_rec.fee_rule_id
then
select PAY_US_GARN_FEE_RULES_S.currval
into l_next_val
from dual;
select PAY_US_GARN_FEE_RULES_S.nextval
into l_next_val
from dual;
insert into PAY_US_GARN_FEE_RULES_F
(
FEE_RULE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
GARN_CATEGORY,
STATE_CODE,
ADDL_GARN_FEE_AMOUNT,
CORRESPONDENCE_FEE,
CREATOR_TYPE,
FEE_AMOUNT,
FEE_RULE,
MAX_FEE_AMOUNT,
PCT_CURRENT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE) values
(
/* use the l_next_val generated
in the previous step to populte the
fee_rule_id
pay_us_garn_fee_rules_s.nextval,*/
l_next_val,
to_date('01/01/0001', 'DD/MM/YYYY'),
to_date('31/12/4712', 'DD/MM/YYYY'),
gfr_rec.GARN_CATEGORY,
gfr_rec.STATE_CODE,
gfr_rec.ADDL_GARN_FEE_AMOUNT,
gfr_rec.CORRESPONDENCE_FEE,
'SYSTEM',
gfr_rec.FEE_AMOUNT,
gfr_rec.FEE_RULE,
gfr_rec.MAX_FEE_AMOUNT,
gfr_rec.PCT_CURRENT,
gfr_rec.LAST_UPDATE_DATE,
gfr_rec.LAST_UPDATED_BY,
gfr_rec.LAST_UPDATE_LOGIN,
gfr_rec.CREATED_BY,
gfr_rec.CREATION_DATE);
select max(fee_rule_id)
into l_max_val
from pay_us_garn_fee_rules_f;
select PAY_US_GARN_FEE_RULES_S.nextval
into l_next_val
from dual;
insert into PAY_US_GARN_LIMIT_RULES_F
(
LIMIT_RULE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
GARN_CATEGORY,
STATE_CODE,
MAX_WITHHOLDING_AMOUNT,
MAX_WITHHOLDING_DURATION_DAYS,
MIN_WITHHOLDING_AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE) values
(
glr_rec.LIMIT_RULE_ID,
/* Since we have already deleted all rows from
pay_us_garn_limit_rules_f, there cannot be any conflict of ids. It is
therefore safe to use the ids from the hr_s table. This will help in
maintainig the correct ids for date-tracked rows. After inserting
all the rows we will set the sequence to start at a value higher than the
max value of the id in pay_us_garn_limit_rules_f.
PAY_US_GARN_LIMIT_RULES_S.nextval, -- cahnges for nextval */
glr_rec.EFFECTIVE_START_DATE,
glr_rec.EFFECTIVE_END_DATE,
glr_rec.GARN_CATEGORY,
glr_rec.STATE_CODE,
glr_rec.MAX_WITHHOLDING_AMOUNT,
glr_rec.MAX_WITHHOLDING_DURATION_DAYS,
glr_rec.MIN_WITHHOLDING_AMOUNT,
glr_rec.LAST_UPDATE_DATE,
glr_rec.LAST_UPDATED_BY,
glr_rec.LAST_UPDATE_LOGIN,
glr_rec.CREATED_BY,
glr_rec.CREATION_DATE);
select max(limit_rule_id)
into l_max_val
from pay_us_garn_limit_rules_f;
select PAY_US_GARN_LIMIT_RULES_S.nextval
into l_next_val
from dual;
insert into hr_report_lookups (
REPORT_NAME,
REPORT_LOOKUP_TYPE,
LOOKUP_CODE,
ENABLED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN )
values (
hrl_rec.REPORT_NAME,
hrl_rec.REPORT_LOOKUP_TYPE,
hrl_rec.LOOKUP_CODE,
hrl_rec.ENABLED_FLAG,
hrl_rec.CREATED_BY,
hrl_rec.CREATION_DATE,
hrl_rec.LAST_UPDATED_BY,
hrl_rec.LAST_UPDATE_DATE,
hrl_rec.LAST_UPDATE_LOGIN );
delete HR_STU_EXCEPTIONS
where table_name = 'HR_S_NEW';
hr_legislation.insert_hr_stu_exceptions('HR_S_NEW',
1000,
'Error in new US tables',
null);
SELECT element_name
FROM hr_s_element_types_f
WHERE element_type_id = p_code;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_meaning
AND legislation_code = p_legislation_code;
SELECT balance_name
FROM hr_s_balance_types
WHERE balance_type_id = p_code;
SELECT balance_type_id
FROM pay_balance_types
WHERE balance_name = p_meaning
AND legislation_code = p_legislation_code;
SELECT element_type_id,
element_information_category,
element_information10
FROM hr_s_element_types_f
WHERE legislation_code = 'US'
AND element_information_category IS NOT NULL
FOR UPDATE OF element_information10 NOWAIT;
SELECT element_type_id,
element_information_category,
element_information10
FROM pay_element_types_f
WHERE legislation_code = 'US'
AND element_information_category IS NOT NULL
FOR UPDATE OF element_information10 NOWAIT;
** VMehta - Commented out the update for element_tyupe ids for the time being
** as this runs into mutating table condition in the 'NAME_TO_ID' condition
** We can get away with this for 11i as we are not delivering any element ids.
** in the Element Developer DF fort the time being.
**
*/
BEGIN
IF p_mode = 'ID_TO_NAME' THEN
FOR hr_s_rec IN csr_hr_s_element_details
LOOP
IF hr_s_rec.element_information_category = 'US_EARNINGS'
THEN
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information18, NULL),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information19, NULL),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information20, NULL),
-- element_information20)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information18, NULL),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information19, NULL),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information20, NULL),
-- element_information20)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET
-- element_information5 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information5, NULL),
-- element_information5),
element_information8 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information8, NULL),
element_information8),
element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information11, NULL),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information13, NULL),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information14, NULL),
element_information14),
element_information15 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information15, NULL),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information18, NULL),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information19, NULL),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information20, NULL),
-- element_information20)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information18, NULL),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information19, NULL),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information20, NULL),
-- element_information20)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information11, NULL),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information13, NULL),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information14, NULL),
element_information14),
element_information15 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information15, NULL),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information18, NULL),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information19, NULL),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information20, NULL),
-- element_information20)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information18, NULL),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information19, NULL),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information20, NULL),
-- element_information20)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information11, NULL),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information13, NULL),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information14, NULL),
element_information14),
element_information15 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information15, NULL),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17),
element_information18 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information18, NULL),
element_information18),
element_information19 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information19, NULL),
element_information19)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information11, NULL),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information13, NULL),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information14, NULL),
element_information14),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, element_information17, NULL),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information18, NULL),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information19, NULL),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , element_information20, NULL),
-- element_information20)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE', NULL, element_information10),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE', NULL, element_information12),
element_information12),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information18),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information19),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information20),
-- element_information20)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information18),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information19),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information20),
-- element_information20)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET
--element_information5 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information5),
-- element_information5),
element_information8 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information8),
element_information8),
element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information11),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information13),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information14),
element_information14),
element_information15 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information15),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information18),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information19),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information20),
-- element_information20)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information18),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information19),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information20),
-- element_information20)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information11),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information13),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information14),
element_information14),
element_information15 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information15),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information18),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information19),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information20),
-- element_information20)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information18),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information19),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information20),
-- element_information20)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information11),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information13),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information14),
element_information14),
element_information15 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information15),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17),
element_information18 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information18),
element_information18),
element_information19 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information19),
element_information19)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information11),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information13),
element_information13),
element_information14 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information14),
element_information14),
element_information16 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('US',
'BALANCE'
, NULL, element_information17),
element_information17)--,
--element_information18 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information18),
-- element_information18),
--element_information19 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information19),
-- element_information19),
--element_information20 = NVL(decode_elmnt_bal_information('US',
-- 'ELEMENT'
-- , NULL, element_information20),
-- element_information20)
WHERE element_type_id = pay_rec.element_type_id;
SELECT element_type_id,
element_information_category,
element_information10
FROM hr_s_element_types_f
WHERE legislation_code = 'CA'
AND element_information_category IS NOT NULL
FOR UPDATE OF element_information10 NOWAIT;
SELECT element_type_id,
element_information_category,
element_information10
FROM pay_element_types_f
WHERE legislation_code = 'CA'
AND element_information_category IS NOT NULL
FOR UPDATE OF element_information10 NOWAIT;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information12, NULL),
element_information12)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information12, NULL),
element_information12)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET
element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information11, NULL),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information13, NULL),
element_information13),
element_information15 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information15, NULL),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information16, NULL),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information17, NULL),
element_information17)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information10, NULL),
element_information10)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information11, NULL),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information13, NULL),
element_information13)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information12, NULL),
element_information12)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE hr_s_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information10, NULL),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information11, NULL),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information12, NULL),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, element_information13, NULL),
element_information13)
WHERE element_type_id = hr_s_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE', NULL, element_information10),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE', NULL, element_information12),
element_information12)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information12),
element_information12)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET
element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information11),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information13),
element_information13),
element_information15 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information15),
element_information15),
element_information16 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information16),
element_information16),
element_information17 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information17),
element_information17)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information10),
element_information10)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information11),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information13),
element_information13)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information12),
element_information12)
WHERE element_type_id = pay_rec.element_type_id;
UPDATE pay_element_types_f
SET element_information10 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information10),
element_information10),
element_information11 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information11),
element_information11),
element_information12 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information12),
element_information12),
element_information13 = NVL(decode_elmnt_bal_information('CA',
'BALANCE'
, NULL, element_information13),
element_information13)
WHERE element_type_id = pay_rec.element_type_id;
select legislation_code
from hr_s_history;
hr_utility.trace('start gb scl flex delete: ' || to_char(p_phase));
delete from pay_legislation_rules
where legislation_code = 'GB'
and rule_type = 'S'
and not exists
(select null
from fnd_product_installations
where application_id = 801
and status = 'I'
);
delete from hr_org_info_types_by_class
where org_information_type = 'Tax Details References'
and ORG_CLASSIFICATION = 'HR_BG'
and not exists
(select null
from fnd_product_installations
where application_id = 801
and status = 'I'
);