[Home] [Help]
PACKAGE: APPS.PAY_US_MWR_REPORTING_PKG
Source
1 PACKAGE pay_us_mwr_reporting_pkg AS
2 /* $Header: pyusmwrp.pkh 120.0.12000000.1 2007/01/18 02:39:58 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_mwr_reporting_pkg
21
22 Description : Generate the multi worksite magnetic report.
23
24 Uses :
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ---- ---- ------ ------- -----------
30 08-FEB-2001 tclewis 115.0 Created.
31 24-DEC-2002 tclewis 115.9 Added NOCOPY
32 26-DEC-2003 tclewis 115.10 Added index hint to
33 *******************************************************************/
34
35 /******************************************************************
36 ** Global variables used by the file generation code.
37 ******************************************************************/
38 estab_count number := 0;
39 est_id number := -88888; -- setting the default value to -88888
40 state_abbrev varchar(2) := '00'; -- setting the default value to -88888
41
42
43 /******************************************************************
44 ** Driving Cursors file Generation
45 ******************************************************************/
46 cursor US_MWR_SETUP is
47 select 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id
48 from pay_payroll_actions ppa -- MWR payroll action
49 where ppa.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value(
50 'TRANSFER_PAYROLL_ACTION_ID'); -- MWR payroll action
51
52
53 cursor US_MWR_STATE_GRE is
54 select /*+ INDEX (HOI3 HR_ORGANIZATION_INFORMATIO_FK2)
55 ORDERED */
56 DISTINCT 'TRANSFER_FIPS_CODE=P', psr.fips_code, -- state code
57 'TRANSFER_STATE_ABBREV=P', psr.state_code,
58 'TRANSFER_EIN=P', NVL(hoi2.org_information1,'0'), -- Federal EIN
59 'TRANSFER_SUI_CODE=P', hoi3.org_information2, -- STATE SUI code.
60 'TRANSFER_REPORTING_YEAR=P', to_char(ppa1.effective_date,'yyyy'),
61 'TRANSFER_OUTPUT_QTR=P', to_char(ppa2.effective_date,'Q')
62 from
63 PAY_PAYROLL_ACTIONS PPA2, -- MWR payroll action
64 PAY_ASSIGNMENT_ACTIONS PAA2, -- MWR assignment action
65 PAY_ASSIGNMENT_ACTIONS PAA1, -- SQWL assignment action
66 PAY_PAYROLL_ACTIONS PPA1, -- SQWL payroll action
67 PAY_STATE_RULES PSR,
68 HR_ORGANIZATION_INFORMATION HOI1, -- gre / legal entity classification
69 HR_ORGANIZATION_INFORMATION HOI2, -- Federal employer Identification
70 HR_ORGANIZATION_INFORMATION HOI3 -- state SUI code.
71 where ppa2.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value(
72 'TRANSFER_PAYROLL_ACTION_ID') -- MWR payroll action
73 and ppa2.payroll_action_id = paa2.payroll_action_id
74 -- paa2.serial_number store sqwl locked action
75 and paa2.serial_number = paa1.assignment_action_id
76 and paa1.payroll_action_id = ppa1.payroll_action_id
77 and ppa1.report_type = 'SQWL'
78 and ppa1.business_group_id = ppa2.business_group_id
79 and ppa1.report_qualifier = psr.state_code
80 and paa1.tax_unit_id = hoi1.organization_id
81 and hoi1.org_information_context = 'CLASS'
82 and hoi1.org_information1 = 'HR_LEGAL'
83 and hoi1.org_information2 = 'Y' -- Gre Legal entity is active
84 and paa2.tax_unit_id = hoi2.organization_id
85 and hoi2.org_information_context = 'Employer Identification'
86 and paa2.tax_unit_id = hoi3.organization_id
87 and hoi3.org_information_context = 'State Tax Rules'
88 -- ppa1.report_qualifier is SQWL state code.
89 and hoi3.org_information1 = ppa1.report_qualifier
90 order by psr.state_code, NVL(hoi2.org_information1,'0');
91
92
93
94 cursor US_MWR_ESTABLISHMENT is
95 Select 'TRANSFER_ADDRESS=P' ,SUBSTR(loc.address_line_1 ||
96 decode(NVL(LENGTH(loc.address_line_2),0),0,' ',', '
97 || loc.address_line_2) ||
98 decode(NVL(LENGTH(loc.address_line_3),0),0,' ', ', '
99 || loc.address_line_3), 1, 35)
100 ,'TRANSFER_CITY=P' ,loc.town_or_city
101 ,'TRANSFER_STATE=P' ,loc.region_2
102 ,'TRANSFER_ZIP=P' ,loc.postal_code
103 ,'TRANSFER_LEGAL_NAME=P' ,NVL(hoi.org_information1,hou.name)
104 ,'TRANSFER_RPT_UNIT_NO=P' ,lei.lei_information1
105 ,'TRANSFER_TRADE_NAME=P' ,lei.lei_information2
106 ,'TRANSFER_WRKSIT_DESC=P' ,lei.lei_information3
107 ,'TRANSFER_COMNT_1=P' ,lei.lei_information4
108 ,'TRANSFER_COMNT_2=P' ,lei.lei_information5
109 ,'TRANSFER_COMNT_3=P' ,lei.lei_information6
110 ,'TRANSFER_COMMENTS=P' ,lei.lei_information7
111 ,'TRANSFER_EST_ID=P' ,pghn2.entity_id
112 ,'TRANSFER_REC_COUNT=P' ,pay_us_mwr_reporting_pkg.estab_count
113 ,'TRANSFER_HEADER=P' ,pay_us_mwr_reporting_pkg.est_id
114 from per_gen_hierarchy pgh
115 ,per_gen_hierarchy_versions pghv
116 ,per_gen_hierarchy_nodes pghn -- parent organization
117 ,per_gen_hierarchy_nodes pghn2 -- establishment organizations
118 ,hr_organization_information hoi
119 ,hr_organization_units hou
120 ,hr_locations loc
121 ,hr_location_extra_info lei
122 where pgh.hierarchy_id = Pay_Magtape_Generic.Get_Parameter_Value(
123 'TRANSFER_HIERARCHY_ID') --parameter p_hierarchy_id
124 and pghv.HIERARCHY_VERSION_id = Pay_Magtape_Generic.Get_Parameter_Value(
125 'TRANSFER_HIERARCHY_VERSION') --parameter p_hierarchy_verision_number
126 and pgh.hierarchy_id = pghv.hierarchy_id
127 and pghv.hierarchy_version_id = pghn.hierarchy_version_id
128 and pghn.node_type = 'PAR'
129 and pghn.entity_id = hou.organization_id
130 and hou.business_group_id = pgh.business_group_id
131 and hou.organization_id = hoi.organization_id
132 and hoi.org_information_context = 'MWR_Info'
133 and pghv.hierarchy_version_id = pghn2.hierarchy_version_id
134 and pghn.business_group_id = pghn2.business_group_id
135 and pghn2.node_type = 'EST'
136 and pghn2.entity_id = loc.location_id
137 and loc.region_2 = Pay_Magtape_Generic.Get_Parameter_Value(
138 'TRANSFER_STATE_ABBREV')
139 and loc.location_id = lei.location_id
140 and lei.information_type = 'Multi Work Site Information'
141 UNION ALL
142 Select 'TRANSFER_ADDRESS=P' ,NULL
143 ,'TRANSFER_CITY=P' ,NULL
144 ,'TRANSFER_STATE=P' ,NULL
145 ,'TRANSFER_ZIP=P' ,NULL
146 ,'TRANSFER_LEGAL_NAME=P' ,'No Worksite defined for earnings'
147 ,'TRANSFER_RPT_UNIT_NO=P' ,'99999'
148 ,'TRANSFER_TRADE_NAME=P' ,'No Worksite defined for earnings'
149 ,'TRANSFER_WRKSIT_DESC=P' ,'No Worksite defined for earnings'
150 ,'TRANSFER_COMNT_1=P' ,NULL
151 ,'TRANSFER_COMNT_2=P' ,NULL
152 ,'TRANSFER_COMNT_3=P' ,NULL
153 ,'TRANSFER_COMMENTS=P' ,NULL
154 ,'TRANSFER_EST_ID=P' ,'-99999'
155 ,'TRANSFER_REC_COUNT=P' ,pay_us_mwr_reporting_pkg.estab_count
156 ,'TRANSFER_HEADER=P' ,pay_us_mwr_reporting_pkg.est_id
157 From DUAL;
158 -- NEED TO ADD UNION ALL TO THIS CURSOR.
159
160 /*******************************************************************/
161 -- 'level_cnt' will allow the cursors to select function results,
162 -- whether it is a standard fuction such as to_char or a function
163 -- defined in a package (with the correct pragma restriction).
164 /*******************************************************************/
165 level_cnt NUMBER;
166
167
168
169 FUNCTION get_mwr_values(p_payroll_action_id number
170 ,p_fips_code in varchar2
171 ,p_sui_id in varchar2
172 ,p_est_id in varchar2
173 ,p_fed_ein in varchar2
174 )
175 RETURN varchar2;
176
177 FUNCTION derive_sui_id ( p_state_code in varchar2
178 ,p_sui_id in varchar2
179 )
180 RETURN varchar2;
181
182 FUNCTION REMOVE_RPT_TOTALS(p_payroll_action_id number)
183 RETURN NUMBER;
184
185
186 PROCEDURE range_cursor( p_payroll_action_id in number
187 ,p_sql_string out NOCOPY varchar2);
188
189 PROCEDURE action_creation( p_payroll_action_id in number
190 ,p_start_assignment in number
191 ,p_end_assignment in number
192 ,p_chunk in number);
193
194 FUNCTION LOAD_RPT_TOTALS( p_payroll_action_id number)
195 RETURN NUMBER;
196
197 FUNCTION update_global_values(p_estab_ID number,
198 p_state_abbrev varchar2)
199 RETURN NUMBER;
200
201
202 END pay_us_mwr_reporting_pkg;