1 PACKAGE BODY PER_FR_TERMINATION_UPG_PKG AS
2 /* $Header: pefrtmup.pkb 115.2 2002/09/27 15:24:44 jrhodes noship $ */
3
4 CURSOR csr_pds(p_business_group_id number)
5 IS
6 SELECT pds.PERIOD_OF_SERVICE_ID,
7 pds.pds_information2,
8 l.lookup_code,
9 p.employee_number,
10 p.full_name,
11 pds.date_start
12 FROM per_periods_of_service pds
13 , per_all_people_f p
14 , hr_lookups l
15 WHERE pds.business_group_id = p_business_group_id
16 AND pds.pds_information2 is not null
17 AND pds.leaving_reason is null
18 and l.lookup_type(+) = 'LEAV_REAS'
19 and l.lookup_code(+) = pds.pds_information2
20 and pds.person_id = p.person_id
21 AND pds.date_start between
22 p.effective_start_date and p.effective_end_date
23 order by p.full_name,pds.date_start;
24
25 g_package varchar2(30) := 'per_fr_termination_upg_pkg';
26
27 /********************************************************************************
28 * Procedure that writes out the termination information to the log *
29 * this allows users to mannually enter this information where it could not be *
30 * created by the process *
31 ********************************************************************************/
32 procedure write_pds_to_log(p_pds in csr_pds%ROWTYPE)
33 IS
34 BEGIN
35 per_fr_upgrade_data_pkg.write_log(p_pds.employee_number);
36 per_fr_upgrade_data_pkg.write_log(p_pds.full_name);
37 per_fr_upgrade_data_pkg.write_log(p_pds.pds_information2);
38 END write_pds_to_log;
39
40 /***********************************************************************
41 * function TRANSFER_DATA *
42 * This fucntion must be called from run_upgrade *
43 * Return = 0 means upgrade completed OK. *
44 * Return = 1 means warnings *
45 * Return = 2 means upgrade failed *
46 ***********************************************************************/
47 function transfer_data(p_business_group_id IN NUMBER) return number
48 IS
49 l_pds csr_pds%ROWTYPE;
50 l_run_status number :=0; /* Status of the whole run */
51 l_record_status number;
52 l_proc varchar2(72) := g_package||'.transfer_data';
53 --
54 begin
55 --
56 hr_utility.set_location('Entered '||l_proc,5);
57
58 OPEN csr_pds(p_business_group_id);
59 FETCH csr_pds INTO l_pds;
60
61 WHILE csr_pds%FOUND LOOP
62
63 l_record_status :=0;
64
65 if l_pds.lookup_code is null then
66 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75010_MISSING_LEAVING_REAS');
67 l_record_status := 1;
68 end if;
69
70 If l_record_status = 1 THEN
71 -- We could not create the record because the dates were invalid.
72 -- Write out termination information to log for mannual user entry.
73 write_pds_to_log(l_pds);
74 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
75 l_run_status := 1; -- Set status of run to warning.
76 end if;
77 else
78
79 BEGIN -- Update termination record
80 hr_utility.set_location(l_proc,10);
81 hr_utility.trace(l_pds.period_of_service_id);
82
83 SAVEPOINT start_insert;
84
85 update per_periods_of_service
86 set leaving_reason = pds_information2
87 , pds_information2 = null
88 where period_of_service_id = l_pds.period_of_service_id;
89
90 exception when others then
91 rollback to start_insert;
92 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75011_TERM_UPG_FATAL'
93 ,p_token1 => 'STEP:10');
94 write_pds_to_log(l_pds);
95 per_fr_upgrade_data_pkg.write_log(sqlcode);
96 per_fr_upgrade_data_pkg.write_log(sqlerrm);
97 l_run_status := 2; /* Fatal Error */
98 END; -- end of section updating termination
99
100 /* Commit every record to ensure conc log corresponds to records in DB */
101 commit;
102
103 END IF;
104
105 FETCH csr_pds INTO l_pds;
106 END LOOP;
107 CLOSE csr_pds;
108
109 return l_run_status;
110
111 exception when others then
112 rollback;
113 CLOSE csr_pds;
114 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75011_TERM_UPG_FATAL'
115 ,p_token1 => 'STEP:50');
116 per_fr_upgrade_data_pkg.write_log(sqlcode);
117 per_fr_upgrade_data_pkg.write_log(sqlerrm);
118 return 2; /* Fatal Error */
119 END transfer_data;
120
121
122 /***********************************************************************
123 * function RUN_UPGRADE *
124 * This fucntion must be called from *
125 * per_fr_upgrade_data_pkg.run_upgrade *
126 * return = 0 for Status Normal *
127 * return = 1 for Status Warning *
128 * return = 2 for Status Error *
129 ***********************************************************************/
130 function run_upgrade(p_business_group_id number) return number
131 IS
132 l_status number :=0;
133 l_error_status number :=0;
134 l_proc varchar2(72) := g_package||'.run_upgrade';
135 --
136 begin
137 --
138 hr_utility.set_location('Entered '||l_proc,5);
139 l_status := transfer_data(p_business_group_id => p_business_group_id);
140 return l_status;
141 /* Allow exceptions to be handled by calling unit do not trap here. */
142 end run_upgrade;
143
144
145 END PER_FR_TERMINATION_UPG_PKG;
[Home] [Help]