1 PACKAGE PAY_NL_TAXOFFICE_FILE AUTHID CURRENT_USER as
2 /* $Header: pynltosf.pkh 120.0.12000000.1 2007/01/17 23:04:58 appldev noship $ */
3 level_cnt NUMBER;
4 /*-------------------------------------------------------------------------------
5 |Name : RANGE_CODE |
6 |Type : Procedure |
7 |Description : This procedure returns a sql string to select a range of |
8 | assignments eligible for archival |
9 -------------------------------------------------------------------------------*/
10
11 Procedure RANGE_CODE (pactid IN NUMBER
12 ,sqlstr OUT NOCOPY VARCHAR2);
13
14
15
16 /*-------------------------------------------------------------------------------
17 |NAME : ASSIGNMENT_ACTION_CODE |
18 |TYPE : PROCEDURE |
19 |DESCRIPTION : THIS PROCEDURE FURTHER RESTRICTS THE ASSIGNMENT ID'S RETURNED |
20 | BY THE RANGE CODE. |
21 -------------------------------------------------------------------------------*/
22
23 Procedure ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
24 ,p_start_person_id in number
25 ,p_end_person_id in number
26 ,p_chunk in number);
27
28
29 /*----------------------------------------------------------------------------------
30 |Name : ARCHIVE_INIT |
31 |Type : Procedure |
32 |Description : Procedure sets the global tables g_statutory_balance_table, |
33 | g_stat_element_table,g_user_balance_table,g_element_table|
34 -----------------------------------------------------------------------------------*/
35
36 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER);
37
38 /*-------------------------------------------------------------------------------
39 |Name : ARCHIVE_CODE |
40 |Type : Procedure |
41 |Description : This is the main procedure which calls the several procedures |
42 | to archive the data. |
43 -------------------------------------------------------------------------------*/
44
45 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
46 ,p_effective_date IN DATE);
47
48
49
50 /*------------------------------------------------------------------------------
51 |Name : GET_PARAMETER |
52 |Type : Function |
53 |Description : Funtion to get the parameters of the archive process |
54 -------------------------------------------------------------------------------*/
55
56 Function get_parameter(
57 p_parameter_string in varchar2
58 ,p_token in varchar2
59 ,p_segment_number in number default null )RETURN varchar2;
60
61 /*-----------------------------------------------------------------------------
62 |Name : GET_ALL_PARAMETERS |
63 |Type : Procedure |
64 |Description: Procedure which returns all the parameters of the archive process|
65 -------------------------------------------------------------------------------*/
66
67 PROCEDURE get_all_parameters (
68 p_payroll_action_id IN NUMBER
69 ,p_business_group_id OUT NOCOPY NUMBER
70 ,p_effective_date OUT NOCOPY DATE
71 ,p_tax_year OUT NOCOPY date
72 ,p_employer OUT NOCOPY number
73 ,p_org_struct_id OUT NOCOPY number ) ;
74
75
76 /*-----------------------------------------------------------------------------
77 |Name : Get_TOS_Sender_Details |
78 |Type : Function |
79 |Description: Function which returns Tax office and Sender Details |
80 -------------------------------------------------------------------------------*/
81
82
83 FUNCTION Get_TOS_Sender_Details(P_Business_Group_Id in number
84 ,P_Employer_ID in number
85 ,P_Sender_Tax_Rep_Name out nocopy varchar2
86 ,P_Sender_Tax_Reg_Number out nocopy varchar2
87 ,P_Tax_Rep_Name out nocopy varchar2
88 ,P_Tax_Reg_Number out nocopy varchar2) return number;
89
90
91
92
93
94 /********************************************************
95 * Cursor to fetch header record information *
96 ********************************************************/
97 /* Cursor for driving the Header Formula - NL_TAXOFFICE_FILE_HEADER
98 and the Trailer Formula -- NL_TAXOFFICE_FILE_TRAILER
99 to generate the following records in the electronic file
100 Record Type -1 Sender Identification Record and
101 Record Type -3 Employer Identification Record and
102 Record Type -7 Employer Closing Record and
103 Record Type -9 Sender Closing Record
104 */
105
106 Cursor Csr_NL_TaxOff_Header IS
107 SELECT
108 'TAX_YEAR=P',pay_magtape_generic.get_parameter_value('REPORT_YEAR'),
109 'BUSINESS_GROUP_ID=P',ppa.business_group_id,
110 'EMPLOYER_ID=P',pay_magtape_generic.get_parameter_value('EMPLOYER_ID'),
111 'ORG_STRUCT_ID=P',pay_magtape_generic.get_parameter_value('ORG_HIERARCHY'),
112 'MEDIUM_CODE=P',pay_magtape_generic.get_parameter_value('MEDIUM_CODE'),
113 'DENSITY=P',pay_magtape_generic.get_parameter_value('DENSITY_CODE')
114 FROM pay_payroll_actions ppa
115 WHERE ppa.payroll_action_id
116 =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
117
118
119
120 /********************************************************
121 * Cursor to fetch batch/payment record information *
122 ********************************************************/
123
124 /* Cursor for driving the Body Formula - NL_TAXOFFICE_FILE_BODY
125 to generate the following records in the electronic file
126 Record Type -5 Employee Identification Record and
127 Record Type -7 Employee Fiscal Record and
128 */
129 Cursor Csr_NL_TaxOff_Body IS
130 SELECT
131 'ATS_EMPLOYER_ID=P' ,ee_ats.action_information1 ,
132 'ATS_PERSON_ID=P' ,ee_ats.action_information2 ,
133 'ATS_ASSIGNMENT_ID=P' ,ee_ats.action_information3 ,
134 'DATE_OF_BIRTH=P' ,TO_CHAR(pap.Date_of_Birth,'DDMMYYYY') ,
135 'SOFI_NUMBER=P' ,pap.National_Identifier,
136 'EMPLOYEE_NAME=P' ,pap.last_name,
137 'WAGE=P' ,ee_ats.action_information4 ,
138 'DEDUCT_WAGE_TAX_NI_CONT=P' ,ee_ats.action_information5 ,
139 'ASG_TAXYEAR_START_DATE=P' ,ee_ats.action_information6 ,
140 'ASG_TAXYEAR_END_DATE=P' ,ee_ats.action_information7 ,
141 'LABOUR_DISCOUNT=P' ,ee_ats.action_information8 ,
142 'WAGE_TAX_DISCOUNT=P' ,ee_ats.action_information9,
143 'WAGE_TAX_TABLE_CODE=P' ,ee_ats.action_information10 ,
144 'INCOME_CODE=P' ,ee_ats.action_information11 ,
145 'SPECIAL_INDICATOR=P' ,ee_ats.action_information12 ,
146 'AMOUNT_SPECIAL_INDICATOR=P' ,ee_ats.action_information13
147 FROM
148 pay_assignment_actions pay_act,
149 pay_action_interlocks arc_lck,
150 pay_action_information ee_ats,
151 per_all_people_f pap
152 WHERE pay_act.payroll_action_id
153 =pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
154 AND pay_act.assignment_action_id = arc_lck.locking_action_id
155 AND arc_lck.locked_action_id = ee_ats.action_context_id
156 AND ee_ats.action_context_type='AAP'
157 AND ee_ats.action_information_category = 'NL ATS EMPLOYEE DETAILS'
158 AND ee_ats.action_information2 = fnd_number.number_to_canonical(pap.person_id)
159 and ee_ats.effective_date between pap.effective_start_date and pap.effective_end_date;
160
161 END PAY_NL_TAXOFFICE_FILE ;
[Home] [Help]