rem  NOTE:  may need to add someday:  ptrbsdd (benefit statement display dates, not currently used) 
rem         and will need to add for life insurance: ptrbdfq (payroll frequency rules)
rem         For life insurance would need ptrprem, ptrlife, ptrlifi, etc...

rem  copy_bdca.sql
rem  May 2010
rem  Lisa Molmen
rem  Script to copy all data on a new benefit/deduction code from a test instance to production.
rem  Note:  skipping Combined Limits data for right now as it is completely unfamiliar (and unused).
rem  Note2:  Need to be sure and copy over all pieces of a chained deduction, then chain them in PTRDCHN.
rem  IMPORTANT:  be sure the database link (@inst) is where you want it, and that you're logged into
rem              the instance to which you are copying.

alter session set nls_date_format = 'DD-MON-YYYY';
undefine existing_bdca_code;
undefine new_bdca_code;
undefine eff_date;

rem  Basic Benefit Rules, Form PTRBDCA:

insert into ptrbdca
(select
upper('&&new_bdca_code'),
PTRBDCA_SHORT_DESC,
PTRBDCA_LONG_DESC,
PTRBDCA_PRIORITY,
PTRBDCA_CALC_RULE,
PTRBDCA_WEEK1_IND,
PTRBDCA_WEEK2_IND,
PTRBDCA_WEEK3_IND,
PTRBDCA_WEEK4_IND,
PTRBDCA_WEEK5_IND,
PTRBDCA_AMT1_IND,
PTRBDCA_AMT2_IND,
PTRBDCA_AMT3_IND,
PTRBDCA_AMT4_IND,
PTRBDCA_AMT1_TITLE,
PTRBDCA_AMT2_TITLE,
PTRBDCA_AMT3_TITLE,
PTRBDCA_AMT4_TITLE,
PTRBDCA_OPTION1_IND,
PTRBDCA_OPTION2_IND,
PTRBDCA_OPTION3_IND,
PTRBDCA_OPTION4_IND,
PTRBDCA_OPTION5_IND,
PTRBDCA_OPTION1_TITLE,
PTRBDCA_OPTION2_TITLE,
PTRBDCA_OPTION3_TITLE,
PTRBDCA_OPTION4_TITLE,
PTRBDCA_OPTION5_TITLE,
PTRBDCA_GROSS_REPORT_IND,
PTRBDCA_BENEFIT_IND,
PTRBDCA_TXCD_CODE,
PTRBDCA_TAX_TYPE_IND,
PTRBDCA_OVERLOAD_IND,
PTRBDCA_ARREAR_IND,
sysdate,
PTRBDCA_BDTY_CODE,
PTRBDCA_AMT1_DESC,
PTRBDCA_AMT2_DESC,
PTRBDCA_AMT3_DESC,
PTRBDCA_AMT4_DESC,
PTRBDCA_1099_CODE,
PTRBDCA_FLAT_AMT_IND,
PTRBDCA_YTD_START_MONTH,
PTRBDCA_WEB_DESC,
PTRBDCA_WEB_INFO_URL,
PTRBDCA_OPTION1_WEB_DESC,
PTRBDCA_OPTION2_WEB_DESC,
PTRBDCA_OPTION3_WEB_DESC,
PTRBDCA_OPTION4_WEB_DESC,
PTRBDCA_OPTION5_WEB_DESC,
PTRBDCA_AMT1_WEB_DESC,
PTRBDCA_AMT2_WEB_DESC,
PTRBDCA_AMT3_WEB_DESC,
PTRBDCA_AMT4_WEB_DESC,
PTRBDCA_BDCL_CODE
from ptrbdca@test 
where ptrbdca_code = upper('&&existing_bdca_code'));


insert into ptrbdpl
(select 
upper('&&new_bdca_code'),
PTRBDPL_CODE,
PTRBDPL_DESC,
PTRBDPL_AMT1,
PTRBDPL_AMT2,
PTRBDPL_AMT3,
PTRBDPL_AMT4,
sysdate,
'&&eff_date'
from ptrbdpl@test
where ptrbdpl_bdca_code = upper('&&existing_bdca_code'));


insert into ptrbdpd
(select 
upper('&&new_bdca_code'),
PTRBDPD_BDCA_CODE,
sysdate
from ptrbdpd@test
where ptrbdpd_code = upper('&&existing_bdca_code'));


insert into ptrbdas
(select 
upper('&&new_bdca_code'),
PTRBDAS_BDCA_CODE,
sysdate
from ptrbdas@test 
where ptrbdas_code = upper('&&existing_bdca_code'));


insert into ptrbdcg
(select 
upper('&&new_bdca_code'),
PTRBDCG_BDCA_CODE,
PTRBDCG_USER_ID,
sysdate
from ptrbdcg@test
where ptrbdcg_code = upper('&&existing_bdca_code'));


insert into ptrbdid
(select
upper('&&new_bdca_code'),
PTRBDID_BDCA_CODE,
PTRBDID_TAX_METHOD_IND,
sysdate,
PTRBDID_EMPL_EMPR_IND
from ptrbdid@test 
where ptrbdid_code = upper('&&existing_bdca_code'));


insert into ptrbdxd
(select
upper('&&new_bdca_code'),
PTRBDXD_BDCA_CODE,
sysdate
from ptrbdxd@test
where ptrbdxd_code = upper('&&existing_bdca_code'));


insert into ptrbdxe
(select
upper('&&new_bdca_code'),
PTRBDXE_EARN_CODE,
sysdate
from ptrbdxe@test
where ptrbdxe_code = upper('&&existing_bdca_code'));


insert into ptrbdld
(select
upper('&&new_bdca_code'),
PTRBDLD_ECLS_CODE,
PTRBDLD_COAS_CODE,
PTRBDLD_ACCI_CODE_EMP_LIB,
PTRBDLD_FUND_CODE_EMP_LIB,
PTRBDLD_ORGN_CODE_EMP_LIB,
PTRBDLD_ACCT_CODE_EMP_LIB,
PTRBDLD_PROG_CODE_EMP_LIB,
PTRBDLD_ACTV_CODE_EMP_LIB,
PTRBDLD_LOCN_CODE_EMP_LIB,
PTRBDLD_ACCI_CODE_EMR_EXP,
PTRBDLD_FUND_CODE_EMR_EXP,
PTRBDLD_ORGN_CODE_EMR_EXP,
PTRBDLD_ACCT_CODE_EMR_EXP,
PTRBDLD_PROG_CODE_EMR_EXP,
PTRBDLD_ACTV_CODE_EMR_EXP,
PTRBDLD_LOCN_CODE_EMR_EXP,
PTRBDLD_ACCI_CODE_EMR_LIB,
PTRBDLD_FUND_CODE_EMR_LIB,
PTRBDLD_ORGN_CODE_EMR_LIB,
PTRBDLD_ACCT_CODE_EMR_LIB,
PTRBDLD_PROG_CODE_EMR_LIB,
PTRBDLD_ACTV_CODE_EMR_LIB,
PTRBDLD_LOCN_CODE_EMR_LIB,
PTRBDLD_EMP_LIB_EXTERNAL,
PTRBDLD_EMR_EXP_EXTERNAL,
PTRBDLD_EMR_LIB_EXTERNAL,
sysdate
from ptrbdld@test
where ptrbdld_code = upper('&&existing_bdca_code'));



rem  Eligible Benefit Categories, PTRBCAT:
insert into ptrbcdn
(select 
PTRBCDN_BCAT_CODE,
upper('&&new_bdca_code'),
PTRBCDN_REQ_IND,
PTRBCDN_SETUP_IND,
sysdate,
PTRBCDN_SEQ_NO,
PTRBCDN_AMOUNT1,
PTRBCDN_AMOUNT2,
PTRBCDN_AMOUNT3,
PTRBCDN_AMOUNT4,
PTRBCDN_OPT_CODE1,
PTRBCDN_OPT_CODE2,
PTRBCDN_OPT_CODE3,
PTRBCDN_OPT_CODE4,
PTRBCDN_OPT_CODE5,
PTRBCDN_BEGIN_DATE_IND,
PTRBCDN_DEDN_STATUS,
'&&eff_date',
PTRBCDN_END_DATE,
PTRBCDN_COVERAGE_END_DATE_IND,
PTRBCDN_DISP_ON_STMT_IND
from ptrbcdn@test
where ptrbcdn_bdca_code = upper('&&existing_bdca_code'));



rem  Reciprocations with Other BDCA Codes:
insert into ptrbdpd
(select
PTRBDPD_CODE,
upper('&&new_bdca_code'),
sysdate
from ptrbdpd@test
where ptrbdpd_bdca_code = upper('&&existing_bdca_code'));


insert into ptrbdas
(select 
ptrbdas_code,
upper('&&new_bdca_code'),
sysdate
from ptrbdas@test 
where ptrbdas_bdca_code = upper('&&existing_bdca_code'));


insert into ptrbdcg
(select 
PTRBDCG_CODE,
upper('&&new_bdca_code'),
PTRBDCG_USER_ID,
sysdate
from ptrbdcg@test
where ptrbdcg_bdca_code = upper('&&existing_bdca_code'));

insert into ptrbdid
(select 
PTRBDID_CODE,
upper('&&new_bdca_code'),
PTRBDID_TAX_METHOD_IND,
sysdate,
PTRBDID_EMPL_EMPR_IND
from ptrbdid@test 
where ptrbdid_bdca_code = upper('&&existing_bdca_code'));

insert into ptrbdxd
(select
PTRBDXD_CODE,
upper('&&new_bdca_code'),
sysdate
from ptrbdxd@test 
where ptrbdxd_bdca_code = upper('&&existing_bdca_code'));

undefine new_bdca_code;
undefine existing_bdca_code;
undefine eff_date;