1 package PAY_NL_CBS_FILE as
2 /* $Header: pynlcbsf.pkh 120.0.12000000.1 2007/01/17 22:54:40 appldev noship $ */
3
4 -- +--------------------------------------------------------------------+
5 -- | PUBLIC FUNCTIONS |
6 -- +--------------------------------------------------------------------+
7 level_cnt NUMBER;
8 hr_formula_error EXCEPTION;
9 /********************************************************************************
10 |Name : RANGE_CODE |
11 |Type : Procedure |
12 |Description : This procedure returns a sql string to select a range of |
13 | assignments eligible for archival |
14 *********************************************************************************/
15
16 Procedure RANGE_CODE (pactid IN NUMBER
17 ,sqlstr OUT NOCOPY VARCHAR2);
18
19 /*******************************************************************************|
20 |Name : ASSIGNMENT_ACTION_CODE |
21 |Type : Procedure |
22 |Description : This procedure further restricts the assignment id's returned |
23 | by the range code. |
24 *********************************************************************************/
25
26 Procedure ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
27 ,p_start_person_id in number
28 ,p_end_person_id in number
29 ,p_chunk in number);
30
31 /********************************************************************************
32 |Name : ARCHIVE_INIT |
33 |Type : Procedure |
34 |Description : Procedure sets the global tables g_statutory_balance_table, |
35 | g_stat_element_table,g_user_balance_table,g_element_table. |
36 *********************************************************************************/
37
38 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER);
39
40 /********************************************************************************
41 |Name : ARCHIVE_CODE |
42 |Type : Procedure |
43 |Description : This is the main procedure which calls the several procedures |
44 | to archive the data. |
45 *********************************************************************************/
46 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
47 ,p_effective_date IN DATE);
48
49 /********************************************************************************
50 |Name : GET_ALL_PARAMETERS |
51 |Type : Procedure |
52 |Description : Procedure which returns all the parameters of the archive |
53 | process |
54 *********************************************************************************/
55 PROCEDURE get_all_parameters (
56 p_payroll_action_id IN NUMBER
57 ,p_business_group_id OUT NOCOPY NUMBER
58 ,p_reporting_date OUT NOCOPY DATE
59 ,p_effective_date OUT NOCOPY DATE
60 ,p_employer OUT NOCOPY NUMBER
61 ,p_si_provider OUT NOCOPY NUMBER
62 ,p_org_struct_id OUT NOCOPY NUMBER
63 ,p_medium_code OUT NOCOPY NUMBER
64 ,p_density OUT NOCOPY NUMBER
65 ) ;
66
67 /********************************************************************************
68 |Name : Mandatory_Check |
69 |Type : Procedure |
70 |Description : Procedure to check if the specified Mandatory Field is NULL |
71 | if so flag a Error message to the Log File |
72 *********************************************************************************/
73 Procedure Mandatory_Check(p_message_name IN varchar2
74 ,p_field IN varchar2
75 ,p_value IN varchar2);
76
77 /********************************************************************************
78 |Name : get_loc_extra_info |
79 |Type : Function |
80 |Description : This Function returns extra information like Contact and |
81 | Telephone No for Employer details |
82 *********************************************************************************/
83 Function get_loc_extra_info(p_org_id NUMBER
84 ,p_contact OUT NOCOPY VARCHAR2
85 ,p_telephone OUT NOCOPY VARCHAR2
86 ) return number;
87
88 /********************************************************************************
89 |Name : get_er_sequence |
90 |Type : Function |
91 |Description : This Function returns the next sequence number for |
92 | employer |
93 *********************************************************************************/
94 Function Get_er_sequence(p_employer_id NUMBER
95 ,p_si_provider_id NUMBER
96 ,p_reporting_date DATE
97 ,p_sequence OUT NOCOPY NUMBER ) return number;
98
99 /*******************************************************************************
100 |Name : Get_Balances1 |
101 |Type : Function |
102 |Description: Function which returns all the balances required for CBS File |
103 *******************************************************************************/
104 function get_balances1( p_frequency varchar2
105 ,p_assgt_act_id number
106 ,l_holiday_hours out nocopy number
107 ,l_adv_hours out nocopy number
108 ,l_si_wage out nocopy number
109 ,l_unique_payments out nocopy number
110 ,l_pre_tax_deductions out nocopy number
111 ,l_saving_scheme out nocopy number
112 ,l_sickness_days out nocopy number
113 ,l_unpaid_hours out nocopy number
114 ,l_sickness_pay out nocopy number
115 ,l_overtime_hours out nocopy number
116 )
117 return number;
118
119 /*******************************************************************************
120 |Name : Get_Balances2 |
121 |Type : Function |
122 |Description: Function which returns all the balances required for CBS File |
123 *******************************************************************************/
124 function get_balances2 ( p_frequency varchar2
125 ,p_assgt_act_id number
126 ,l_wage_agreed_by_contract out nocopy number
127 ,l_number_of_days out nocopy number
128 ,l_si_days_quarter out nocopy number
129 ,l_paid_gross_wage out nocopy number
130 ,l_wage_for_overtime out nocopy number
131 ) return number;
132
133 /*******************************************************************************
134 |Name : Get_health_insurance |
135 |Type : Function |
136 |Description: Function used to pick the si status of the assignment for ZFW |
137 | insurance. If health insurance is from a Private Insurance |
138 | provider,the provider type of the Private Health Insurance |
139 | Provider is returned. |
140 *******************************************************************************/
141 Function get_health_insurance(p_assignment_id IN number
142 ,p_date IN date)
143 RETURN VARCHAR2;
144
145 /*******************************************************************************
146 |Name : Get_working_schedule |
147 |Type : Function |
148 |Description: Function to get the postal code from the employee primary address|
149 *******************************************************************************/
150 PROCEDURE Get_working_schedule (p_assignment_id IN NUMBER,
151 p_working_schedule OUT NOCOPY NUMBER,
152 p_reporting_start_date IN DATE,
153 p_reporting_end_date IN DATE );
154
155 /*******************************************************************************
156 |Name : Get_tax_details |
157 |Type : Function |
158 |Description: Function to get the postal code from the employee primary address|
159 *******************************************************************************/
160 Function Get_tax_details(p_max_assgt_act_id number
161 ,p_wage_tax_discount OUT NOCOPY varchar2
162 ,p_tax_code OUT NOCOPY VARCHAR2
163 ,p_labour_relation_code OUT NOCOPY VARCHAR2)
164 RETURN NUMBER;
165
166 /********************************************************************************
167 |Name : Get_dev_work_hours |
168 |Type : Function |
169 |Description: Function to get the Deviating Working Hours for the longest period|
170 *********************************************************************************/
171 PROCEDURE Get_dev_work_hours (p_assignment_id IN NUMBER,
172 p_dev_work_hours OUT NOCOPY NUMBER,
173 p_reporting_start_date IN DATE,
174 p_reporting_end_date IN DATE );
175
176 /********************************************************************************
177 |Name : Get_Employment_Code |
178 |Type : Function |
179 |Description: Function to get the Employment Code |
180 *********************************************************************************/
181 Function Get_Employment_Code (p_assignment_id IN NUMBER,
182 p_employment_code IN OUT NOCOPY NUMBER,
183 p_reporting_start_date IN DATE,
184 p_reporting_end_date IN DATE ) RETURN NUMBER;
185
186 /********************************************************************************
187 |Name : Get_Working_Hours |
188 |Type : Function |
189 |Description: Function to get the Working Hours |
190 *********************************************************************************/
191 FUNCTION Get_cbs_Working_Hours(p_business_group_id IN NUMBER,
192 p_assignment_id IN NUMBER ,
193 p_reporting_date IN DATE
194 ) RETURN NUMBER ;
195
196 /*******************************************************************************
197 |Name : Check_Asg_si_provider |
198 |Type : Function |
199 |Description: Function to check whether the given employer is subscribed with |
200 | given si provider. |
201 *******************************************************************************/
202 FUNCTION Check_Asg_si_provider(p_organization_id IN NUMBER
203 ,p_si_provider_id IN NUMBER
204 ,p_assignment_id IN NUMBER )
205 RETURN NUMBER;
206
207 /*******************************************************************************
208 |Name : GET_CAO_CODE |
209 |Type : Procedure |
210 |Description: Function to get the collective agreement for the given assignmnent|
211 *******************************************************************************/
212 PROCEDURE GET_CAO_CODE (p_assignment_id in number,
213 p_cao_code in out nocopy number,
214 p_rep_date in DATE);
215
216 /********************************************************************************
217 |Name : get_grade_salary_number |
218 |Type : Function |
219 |Description: Function to get the grade and salary numbers |
220 ********************************************************************************/
221 Function get_grade_salary_number(p_assignment_id in number,
222 P_business_group_id in number,
223 p_org_id in number,
224 P_grade_id in number,
225 P_reporting_date in date,
226 P_public_sector in varchar2,
227 P_grade_number out nocopy varchar2,
228 P_salary_number out nocopy varchar2) return number;
229
230
231 /*******************************************************************************|
232 |Name : check_asg_terminate |
233 |Type : FUNCTION |
234 |Description : This FUNCTION checks whether the given assignment terminated |
235 | : on the given date |
236 *********************************************************************************/
237
238 Function check_asg_terminate ( p_assignment_id NUMBER,
239 p_rep_date DATE) return NUMBER ;
240
241
242 /********************************************************
243 * Cursor to fetch header record information *
244 ********************************************************/
245 Cursor Csr_NL_CBS_Header IS
246 SELECT
247 'ORG_STRUCT_ID=P',pay_magtape_generic.get_parameter_value('ORG_STRUCT_ID'),
248 'BUSINESS_GROUP_ID=P',ppa.business_group_id,
249 'EMPLOYER_ID=P',pay_magtape_generic.get_parameter_value('EMPLOYER_ID'),
250 'SI_PROVIDER_ID=P',pay_magtape_generic.get_parameter_value('SI_PROVIDER_ID'),
251 'REPORTING_DATE=P',TO_CHAR(fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('REPORTING_DATE')),'DDMMYYYY'),
252 'MEDIUM_CODE=P',pay_magtape_generic.get_parameter_value('MEDIUM_CODE'),
253 'DENSITY=P',pay_magtape_generic.get_parameter_value('DENSITY'),
254 'ORG_STRUCT_VERSION_ID=P',posv.organization_structure_id,
255 'REPORTING_SEQUENCE=P',pai.action_information4,
256 'PERIODIC_REP_START_DATE=P' ,TO_CHAR(fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('REPORTING_DATE'))-28,'DDMMYYYY')
257 FROM pay_payroll_actions ppa,
258 per_org_structure_versions posv,
259 pay_action_information pai
260 WHERE ppa.payroll_action_id
261 =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
262 and fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('REPORTING_DATE')) between posv.date_from
263 and nvl(posv.date_to,hr_general.end_of_time)
264 and posv.organization_structure_id = pay_magtape_generic.get_parameter_value('ORG_STRUCT_ID')
265 and pai.action_context_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
266 AND EXISTS
267 (select * from pay_assignment_actions paa
268 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
269
270 /********************************************************
271 * Cursor to fetch Employee record information *
272 ********************************************************/
273 Cursor Csr_NL_CBS_Body IS
274 SELECT
275 'CBS_EMPLOYER_ID=P' ,cbs_pai.action_information1 ,
276 'CBS_SIP_ID=P' ,cbs_pai.action_information2 ,
277 'CBS_ASSIGNMENT_ID=P' ,cbs_pai.action_information3 ,
278 'CBS_PERSON_ID=P' ,cbs_pai.action_information4 ,
279 'CBS_EMPLOYEE_NUMBER=P' ,pap.employee_number,
280 'CBS_SEX=P' ,decode(pap.sex,'M','1','F','2'),
281 'CBS_DATE_OF_BIRTH=P' ,TO_CHAR(pap.date_of_birth,'DDMMYYYY'),
282 'CBS_SOFI_NUMBER=P' ,pap.National_Identifier,
283 'CBS_PAYROLL_TYPE=P' ,decode(ppf.period_type,'Calendar Month','M','Quarter','K','Week','W','Lunar Month','P'),
284 'CBS_GRADE_NUMBER=P' ,substr(cbs_pai.action_information5,1,3),
285 'CBS_SALARY_NUMBER=P' ,substr(cbs_pai.action_information5,4,3),
286 'CBS_WAGE_TAX_DISCOUNT=P' ,SUBSTR(cbs_pai.action_information6,1,2),
287 'CBS_TAX_TABLE_CODE=P' ,SUBSTR(cbs_pai.action_information6,3,3),
288 'CBS_INCOME_CODE=P' ,SUBSTR(cbs_pai.action_information6,6,2),
289 'CBS_LABOUR_RELATION_CODE=P' ,SUBSTR(cbs_pai.action_information6,8,2),
290 'CBS_HEALTH_INSURANCE=P' ,SUBSTR(cbs_pai.action_information7,1,1),
291 'CBS_EMPLOYMENT=P' ,SUBSTR(cbs_pai.action_information8,1,1),
292 'CBS_WORKING_SCHEDULE=P' ,SUBSTR(cbs_pai.action_information9,1,1),
293 'CBS_DEVIATING_WORKING_HOURS=P' ,SUBSTR(cbs_pai.action_information9,2,1),
294 'CBS_HOLIDAY_HOURS=P' ,SUBSTR(cbs_pai.action_information9,3,3),
295 'CBS_ADV_HOURS=P' ,SUBSTR(cbs_pai.action_information9,6,3),
296 'CBS_CAO_CODE=P' ,cbs_pai.action_information10,
297 'CBS_NOD=P' ,cbs_pai.action_information11,
298 'CBS_UNPAID_HOURS=P' ,cbs_pai.action_information12,
299 'CBS_OVERTIME_HOURS=P' ,cbs_pai.action_information13,
300 'CBS_WAGE_AGREED_BY_CONTRACT=P' ,cbs_pai.action_information14,
301 'CBS_PAID_GROSS_WAGE=P' ,cbs_pai.action_information15,
302 'CBS_WAGE_FOR_OVERTIME=P' ,cbs_pai.action_information16,
303 'CBS_SI_WAGE=P' ,cbs_pai.action_information17,
304 'CBS_UNIQUE_PAYMENTS=P' ,cbs_pai.action_information18,
305 'CBS_PRE_TAX_DEDUCTIONS=P' ,cbs_pai.action_information19,
306 'CBS_SAVING_SCHEME=P' ,cbs_pai.action_information20,
307 'CBS_SICKNESS_PAY=P' ,cbs_pai.action_information21,
308 'CBS_SICKNESS_DAYS=P' ,cbs_pai.action_information22,
309 'CBS_SI_DAYS_QUARTER=P' ,cbs_pai.action_information23,
310 'CBS_FOREIGN_WORK=P' ,pap.per_information14
311 FROM
312 pay_assignment_actions pay_act,
313 pay_action_information cbs_pai,
314 per_all_people_f pap,
315 per_all_assignments_f paa,
316 pay_payrolls_f ppf
317 WHERE pay_act.payroll_action_id
318 =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
319 AND pay_act.assignment_action_id = cbs_pai.action_context_id
320 AND cbs_pai.action_context_type='AAP'
321 AND cbs_pai.action_information_category = 'NL CBS EMPLOYEE DETAILS'
322 AND cbs_pai.action_information4 = pap.person_id
323 AND cbs_pai.action_information3 = paa.assignment_id
324 AND paa.person_id = pap.person_id
325 AND ppf.payroll_id = paa.payroll_id
326 AND cbs_pai.effective_date between pap.effective_start_date and pap.effective_end_date
327 AND cbs_pai.effective_date between ppf.effective_start_date and ppf.effective_end_date
328 AND paa.effective_end_date in(select max(asg.effective_end_date)
329 from per_all_assignments_f asg
330 where asg.effective_end_date >= cbs_pai.effective_date
331 and asg.assignment_id=paa.assignment_id)
332 ORDER BY ppf.period_type;
333 END PAY_NL_CBS_FILE;
[Home] [Help]