[Home] [Help]
57:
58: procedure commit_and_log(p_text in varchar2) is
59: begin
60:
61: insert into ben_transaction
62: (transaction_id
63: ,transaction_type
64: ,attribute1
65: ,attribute40)
63: ,transaction_type
64: ,attribute1
65: ,attribute40)
66: values
67: (ben_transaction_s.nextval
68: ,'CWBUPGRADE'
69: ,to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss')
70: ,p_text);
71:
1527:
1528: cursor c_epe_rsgn_appr is
1529: select pil.per_in_ler_id
1530: ,tx.transaction_id
1531: from ben_transaction tx
1532: ,ben_elig_per_elctbl_chc epe
1533: ,ben_per_in_ler pil
1534: where tx.transaction_type = 'CWBEMPRSGN'
1535: and tx.attribute1 = 'APPR'
1539:
1540: cursor c_pel_rsgn_appr is
1541: select pil.per_in_ler_id
1542: ,tx.transaction_id
1543: from ben_transaction tx
1544: ,ben_pil_elctbl_chc_popl pel
1545: ,ben_per_in_ler pil
1546: where tx.transaction_type = 'CWBEMPRSGN'
1547: and tx.attribute1 = 'APPR'
1554: ,curr_mgr_pil.per_in_ler_id
1555: ,prop_mgr_pil.per_in_ler_id
1556: ,emp.pl_id
1557: ,tx.transaction_id
1558: from ben_transaction tx
1559: ,ben_elig_per_elctbl_chc emp
1560: ,ben_per_in_ler emp_pil
1561: ,ben_elig_per_elctbl_chc curr_mgr
1562: ,ben_per_in_ler curr_mgr_pil
1581: ,curr_mgr_pil.per_in_ler_id
1582: ,prop_mgr_pil.per_in_ler_id
1583: ,emp.pl_id
1584: ,tx.transaction_id
1585: from ben_transaction tx
1586: ,ben_pil_elctbl_chc_popl emp
1587: ,ben_per_in_ler emp_pil
1588: ,ben_pil_elctbl_chc_popl curr_mgr
1589: ,ben_per_in_ler curr_mgr_pil
1661: end loop;
1662:
1663: commit_and_log('Rank Upgrade Complete');
1664:
1665: insert into ben_transaction
1666: (transaction_id
1667: ,transaction_type
1668: ,status
1669: ,attribute1
1674: ,tx.status
1675: ,tx.attribute1
1676: ,enp.emp_interview_type_cd
1677: ,tx.attribute3
1678: from ben_transaction tx
1679: ,ben_pil_elctbl_chc_popl popl
1680: ,ben_enrt_perd enp
1681: where tx.transaction_type = 'CWBWSASG'
1682: and tx.attribute1 is not null
1684: and tx.transaction_id = popl.pil_elctbl_chc_popl_id
1685: and popl.enrt_perd_id = enp.enrt_perd_id;
1686:
1687:
1688: insert into ben_transaction
1689: (transaction_id
1690: ,transaction_type
1691: ,status
1692: ,attribute1
1767: ,tx.attribute37
1768: ,tx.attribute38
1769: ,tx.attribute39
1770: ,tx.attribute40
1771: from ben_transaction tx
1772: ,ben_pil_elctbl_chc_popl popl
1773: where tx.transaction_type = 'CWBWSASG'
1774: and tx.attribute2 is not null
1775: and tx.transaction_id = popl.pil_elctbl_chc_popl_id;
1773: where tx.transaction_type = 'CWBWSASG'
1774: and tx.attribute2 is not null
1775: and tx.transaction_id = popl.pil_elctbl_chc_popl_id;
1776:
1777: delete ben_transaction
1778: where transaction_type = 'CWBWSASG';
1779:
1780: commit_and_log('Pending WS Transaction Upgrade Complete');
1781:
1788:
1789: if l_t_txn_id.count > 0 then
1790:
1791: forall i in l_t_txn_id.first .. l_t_txn_id.last
1792: update ben_transaction tx
1793: set tx.attribute10 = to_char(l_t_emp_pil_id(i))
1794: where tx.transaction_id = l_t_txn_id(i)
1795: and tx.transaction_type = 'CWBEMPRSGN';
1796:
1812:
1813: if l_t_txn_id.count > 0 then
1814:
1815: forall i in l_t_txn_id.first .. l_t_txn_id.last
1816: update ben_transaction tx
1817: set tx.attribute10 = to_char(l_t_emp_pil_id(i))
1818: where tx.transaction_id = l_t_txn_id(i)
1819: and tx.transaction_type = 'CWBEMPRSGN';
1820:
1839:
1840: if l_t_txn_id.count > 0 then
1841:
1842: forall i in l_t_txn_id.first .. l_t_txn_id.last
1843: update ben_transaction tx
1844: set tx.attribute3 = to_char(l_t_emp_pil_id(i))
1845: ,tx.attribute14 = to_char(l_t_prop_mgr_pil_id(i))
1846: ,tx.attribute16 = to_char(l_t_curr_mgr_pil_id(i))
1847: ,tx.attribute21 = to_char(l_t_grp_pl_id(i))
1869:
1870: if l_t_txn_id.count > 0 then
1871:
1872: forall i in l_t_txn_id.first .. l_t_txn_id.last
1873: update ben_transaction tx
1874: set tx.attribute3 = to_char(l_t_emp_pil_id(i))
1875: ,tx.attribute14 = to_char(l_t_prop_mgr_pil_id(i))
1876: ,tx.attribute16 = to_char(l_t_curr_mgr_pil_id(i))
1877: ,tx.attribute21 = to_char(l_t_grp_pl_id(i))