1 package pay_mws_magtape_reporting AUTHID CURRENT_USER as
2 /* $Header: pymwsrep.pkh 115.0 99/07/17 06:17:21 porting ship $ */
3
4 /* 'level_cnt' will allow the cursors to select function results,
5 whether it is a standard fuction such as to_char or a function
6 defined in a package (with the correct pragma restriction).
7 */
8
9 level_cnt number;
10
11 TYPE numeric_data_table IS TABLE OF number(15)
12 INDEX BY BINARY_INTEGER;
13 TYPE character_data_table IS TABLE OF varchar2(240)
14 INDEX BY BINARY_INTEGER;
15
16 /* Sets up the tax unit context for the transmitter. */
17
18 cursor us_mws_transmitter is
19 select 'TAX_UNIT_ID=C' , htv.tax_unit_id,
20 'TRANSFER_CONTACT_TITLE=P', htv.mws_title,
21 'TRANSFER_CONTACT_TELEPHONE=P', htv.mws_telephone_no,
22 'TRANSFER_TRANS_NAME=P', htv.name,
23 'TRANSFER_TRANS_ADDR_LINE_1=P', htv.address_line_1,
24 'TRANSFER_TRANS_ADDR_LINE_2=P', htv.address_line_2,
25 'TRANSFER_TRANS_ADDR_LINE_3=P', htv.address_line_3,
26 'TRANSFER_TRANS_CITY=P', htv.town_or_city,
27 'TRANSFER_TRANS_STATE=P', htv.state_code,
28 'TRANSFER_TRANS_ZIP_CODE=P', htv.zip_code,
29 'TRANSFER_TAPE_MEDIUM=P', htv.mws_tape_medium,
30 'TRANSFER_TAPE_DENSITY=P', htv.mws_tape_density,
31 'TRANSFER_HEADER_LABEL=P', htv.mws_header_label
32 from hr_tax_units_v htv
33 where htv.tax_unit_id = pay_magtape_generic.get_parameter_value
34 ('TRANSFER_TRANS_LEGAL_CO_ID')
35 and htv.business_group_id = pay_magtape_generic.get_parameter_value
36 ('TRANSFER_BUSINESS_GROUP');
37
38
39 /* Sets up the State context for US_MWS_GET_STATE formula and the State
40 parameter for the us_mws_sui cursor */
41
42 cursor us_mws_state is
43 select distinct 'TRANSFER_MWS_STATE=P', hwv.mws_state ,
44 'TRANSFER_FIPS_CODE=P', psr.fips_code
45 from HR_WORKSITE_V hwv,
46 PAY_STATE_RULES psr
47 where hwv.mws_business_group_id = pay_magtape_generic.get_parameter_value
48 ('TRANSFER_BUSINESS_GROUP')
49 and psr.state_code = hwv.mws_state
50 order by hwv.mws_state;
51
52 /* Get the SUIs within the state and set up the SUI A/C and the Tax unit Id
53 as the parameter for us_mws_worksite cursor. Also set the context of the
54 TAX_UNIT_ID of the SUI A/C, for the database items */
55
56 cursor us_mws_sui is
57 select distinct 'TRANSFER_SUI_ACCOUNT_NO=P', hwv.mws_sui_account_no,
58 'MWS_TAX_UNIT_ID=P', hwv.mws_tax_unit_id,
59 'TAX_UNIT_ID=C', hwv.mws_tax_unit_id
60 from HR_WORKSITE_V hwv
61 where hwv.mws_state = pay_magtape_generic.get_parameter_value
62 ('TRANSFER_MWS_STATE')
63 and hwv.mws_business_group_id = pay_magtape_generic.get_parameter_value
64 ('TRANSFER_BUSINESS_GROUP')
65 order by hwv.mws_sui_account_no;
66
67
68 /* Get worksites within a SUI. Set up the context of Organization id (the
69 primary organization id for the worksite). Also set up the
70 TRANSFER_WORKSITE_RUN as a parameter for
71 us_mws_worksite_organization cursor */
72
73 cursor us_mws_worksite is
74 select 'TRANSFER_WORKSITE_RUN=P', hwv.mws_reporting_unit_no,
75 'TRANSFER_WORKSITE_TRADE_NAME=P', hwv.mws_trade_name,
76 'TRANSFER_WORKSITE_DESCRIPTION=P', hwv.mws_worksite_description,
77 'TRANSFER_WORKSITE_COMMENT_CODE1=P', hwv.mws_comment_code1,
78 'TRANSFER_WORKSITE_COMMENT_CODE2=P', hwv.mws_comment_code2,
79 'TRANSFER_WORKSITE_COMMENTS=P', hwv.mws_comments,
80 'TRANSFER_WORKSITE_ADDRESS_LINE_1=P', hwv.mws_address_line_1,
81 'TRANSFER_WORKSITE_ADDRESS_LINE_2=P', hwv.mws_address_line_2,
82 'TRANSFER_WORKSITE_ADDRESS_LINE_3=P', hwv.mws_address_line_3,
83 'TRANSFER_WORKSITE_CITY=P', hwv.mws_city,
84 'TRANSFER_WORKSITE_STATE=P', hwv.mws_state,
85 'TRANSFER_WORKSITE_ZIP_CODE=P', hwv.mws_zip_code
86 from HR_WORKSITE_V hwv
87 where hwv.mws_sui_account_no = pay_magtape_generic.get_parameter_value
88 ('TRANSFER_SUI_ACCOUNT_NO')
89 and hwv.mws_tax_unit_id = pay_magtape_generic.get_parameter_value
90 ('MWS_TAX_UNIT_ID')
91 and hwv.mws_state = pay_magtape_generic.get_parameter_value
92 ('TRANSFER_MWS_STATE')
93 and hwv.mws_business_group_id = pay_magtape_generic.get_parameter_value
94 ('TRANSFER_BUSINESS_GROUP')
95 order by hwv.mws_reporting_unit_no;
96
97
98 /* Get all the organizations belonging to the worksite . We will check
99 to see that the RUN i.e. org_information3 of the table if not null */
100
101 cursor us_mws_worksite_organization is
102 select 'ORGANIZATION_ID=C', hoi.organization_id,
103 'ORGANIZATION_ID=P', hoi.organization_id
104 from HR_ORGANIZATION_INFORMATION hoi
105 where hoi.org_information_context = 'Worksite Filing'
106 and hoi.org_information3 is not null
107 and hoi.org_information2 = pay_magtape_generic.get_parameter_value
108 ('TRANSFER_SUI_ACCOUNT_NO')
109 and hoi.org_information3 = pay_magtape_generic.get_parameter_value
110 ('TRANSFER_WORKSITE_RUN')
111 order by hoi.organization_id;
112
113
114
115 /* Get all the employees belonging to an organization of the worksite.
116 We will check for the primary flag of PER_ASSIGNMENTS_F to be Y, in
117 order to see that it is the primary assignment of the employee */
118
119 cursor us_mws_organization_employees is
120 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
121 'ASSIGNMENT_ID=C', paa.assignment_id,
122 'TRANSFER_WAGES_EARNED=P', ltrim(substr(paa.serial_number,1,20)),
123 'TRANSFER_ASG_START_DATE=P',to_char(paf.effective_start_date,'DD-MM-YYYY'),
124 'TRANSFER_ASG_END_DATE=P', to_char(paf.effective_end_date,'DD-MM-YYYY')
125 from PAY_PAYROLL_ACTIONS ppa,
126 PAY_ASSIGNMENT_ACTIONS paa,
127 PER_ASSIGNMENTS_F paf,
128 PER_PEOPLE_F ppf,
129 HR_SOFT_CODING_KEYFLEX scl
130 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value
131 ('TRANSFER_PAYROLL_ACTION_ID')
132 and paa.payroll_action_id = ppa.payroll_action_id
133 and paf.assignment_id = paa.assignment_id
134 and paf.organization_id = pay_magtape_generic.get_parameter_value
135 ('ORGANIZATION_ID')
136 and paf.effective_end_date = to_date((substr(paa.serial_number,21,10)),
137 'DD-MM-YYYY')
138 and scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
139 and scl.segment1 = paa.tax_unit_id
140 and paf.primary_flag = 'Y'
141 and ppf.person_id = paf.person_id;
142
143 procedure redo
144 (
145 errbuf out varchar2,
146 retcode out number,
147 p_payroll_action_id in varchar2
148 );
149
150 procedure Run_Magtape
151 (
152 p_effective_date date,
153 p_report_type varchar2,
154 p_payroll_action_id varchar2,
155 p_state varchar2,
156 p_reporting_year varchar2,
157 p_reporting_quarter varchar2,
158 p_trans_legal_co_id varchar2,
159 p_quarter_start date,
160 p_quarter_end date,
161 p_business_group_id varchar2
162 );
163
164 procedure run
165 (
166 errbuf out varchar2,
167 retcode out number,
168 p_business_group_id in number,
169 p_report_type in varchar2,
170 p_quarter in varchar2,
171 p_year in varchar2,
172 p_trans_legal_co_id in number
173 );
174
175 function generate_people_list
176 (
177 p_report_type varchar2,
178 p_state varchar2,
179 p_trans_legal_co_id varchar2,
180 p_business_group_id number,
181 p_period_end date,
182 p_quarter_start date,
183 p_quarter_end date
184 )return number;
185
186 end pay_mws_magtape_reporting;
187
[Home] [Help]