[Home] [Help]
PACKAGE: APPS.PAY_US_MWR_REPORTING_PKG
Source
1 PACKAGE pay_us_mwr_reporting_pkg AUTHID CURRENT_USER AS
2 /* $Header: pyusmwrp.pkh 120.1 2011/08/08 08:44:28 nkjaladi ship $ */
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 08-AUG-2011 nkjaladi 115.12 11936382 Modified cursor
34 US_MWR_ESTABLISHMENT to
35 consider the override tax
36 state of the assignment
37 location.
38 *******************************************************************/
39
40 /******************************************************************
41 ** Global variables used by the file generation code.
42 ******************************************************************/
43 estab_count number := 0;
44 est_id number := -88888; -- setting the default value to -88888
45 state_abbrev varchar(2) := '00'; -- setting the default value to -88888
46
47
48 /******************************************************************
49 ** Driving Cursors file Generation
50 ******************************************************************/
51 cursor US_MWR_SETUP is
52 select 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id
53 from pay_payroll_actions ppa -- MWR payroll action
54 where ppa.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value(
55 'TRANSFER_PAYROLL_ACTION_ID'); -- MWR payroll action
56
57
58 cursor US_MWR_STATE_GRE is
59 select /*+ INDEX (HOI3 HR_ORGANIZATION_INFORMATIO_FK2)
60 ORDERED */
61 DISTINCT 'TRANSFER_FIPS_CODE=P', psr.fips_code, -- state code
62 'TRANSFER_STATE_ABBREV=P', psr.state_code,
63 'TRANSFER_EIN=P', NVL(hoi2.org_information1,'0'), -- Federal EIN
64 'TRANSFER_SUI_CODE=P', hoi3.org_information2, -- STATE SUI code.
65 'TRANSFER_REPORTING_YEAR=P', to_char(ppa1.effective_date,'yyyy'),
66 'TRANSFER_OUTPUT_QTR=P', to_char(ppa2.effective_date,'Q')
67 from
68 PAY_PAYROLL_ACTIONS PPA2, -- MWR payroll action
69 PAY_ASSIGNMENT_ACTIONS PAA2, -- MWR assignment action
70 PAY_ASSIGNMENT_ACTIONS PAA1, -- SQWL assignment action
71 PAY_PAYROLL_ACTIONS PPA1, -- SQWL payroll action
72 PAY_STATE_RULES PSR,
73 HR_ORGANIZATION_INFORMATION HOI1, -- gre / legal entity classification
74 HR_ORGANIZATION_INFORMATION HOI2, -- Federal employer Identification
75 HR_ORGANIZATION_INFORMATION HOI3 -- state SUI code.
76 where ppa2.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value(
77 'TRANSFER_PAYROLL_ACTION_ID') -- MWR payroll action
78 and ppa2.payroll_action_id = paa2.payroll_action_id
79 -- paa2.serial_number store sqwl locked action
80 and paa2.serial_number = paa1.assignment_action_id
81 and paa1.payroll_action_id = ppa1.payroll_action_id
82 and ppa1.report_type = 'SQWL'
83 and ppa1.business_group_id = ppa2.business_group_id
84 and ppa1.report_qualifier = psr.state_code
85 and paa1.tax_unit_id = hoi1.organization_id
86 and hoi1.org_information_context = 'CLASS'
87 and hoi1.org_information1 = 'HR_LEGAL'
88 and hoi1.org_information2 = 'Y' -- Gre Legal entity is active
89 and paa2.tax_unit_id = hoi2.organization_id
90 and hoi2.org_information_context = 'Employer Identification'
91 and paa2.tax_unit_id = hoi3.organization_id
92 and hoi3.org_information_context = 'State Tax Rules'
93 -- ppa1.report_qualifier is SQWL state code.
94 and hoi3.org_information1 = ppa1.report_qualifier
95 order by psr.state_code, NVL(hoi2.org_information1,'0');
96
97
98
99 cursor US_MWR_ESTABLISHMENT is
100 Select 'TRANSFER_ADDRESS=P' ,SUBSTR(loc.address_line_1 ||
101 decode(NVL(LENGTH(loc.address_line_2),0),0,' ',', '
102 || loc.address_line_2) ||
103 decode(NVL(LENGTH(loc.address_line_3),0),0,' ', ', '
104 || loc.address_line_3), 1, 35)
105 ,'TRANSFER_CITY=P' ,loc.town_or_city
106 ,'TRANSFER_STATE=P' ,loc.region_2
107 ,'TRANSFER_ZIP=P' ,loc.postal_code
108 ,'TRANSFER_LEGAL_NAME=P' ,NVL(hoi.org_information1,hou.name)
109 ,'TRANSFER_RPT_UNIT_NO=P' ,lei.lei_information1
110 ,'TRANSFER_TRADE_NAME=P' ,lei.lei_information2
111 ,'TRANSFER_WRKSIT_DESC=P' ,lei.lei_information3
112 ,'TRANSFER_COMNT_1=P' ,lei.lei_information4
113 ,'TRANSFER_COMNT_2=P' ,lei.lei_information5
114 ,'TRANSFER_COMNT_3=P' ,lei.lei_information6
115 ,'TRANSFER_COMMENTS=P' ,lei.lei_information7
116 ,'TRANSFER_EST_ID=P' ,pghn2.entity_id
117 ,'TRANSFER_REC_COUNT=P' ,pay_us_mwr_reporting_pkg.estab_count
118 ,'TRANSFER_HEADER=P' ,pay_us_mwr_reporting_pkg.est_id
119 from per_gen_hierarchy pgh
120 ,per_gen_hierarchy_versions pghv
121 ,per_gen_hierarchy_nodes pghn -- parent organization
122 ,per_gen_hierarchy_nodes pghn2 -- establishment organizations
123 ,hr_organization_information hoi
124 ,hr_organization_units hou
125 ,hr_locations loc
126 ,hr_location_extra_info lei
127 where pgh.hierarchy_id = Pay_Magtape_Generic.Get_Parameter_Value(
128 'TRANSFER_HIERARCHY_ID') --parameter p_hierarchy_id
129 and pghv.HIERARCHY_VERSION_id = Pay_Magtape_Generic.Get_Parameter_Value(
130 'TRANSFER_HIERARCHY_VERSION') --parameter p_hierarchy_verision_number
131 and pgh.hierarchy_id = pghv.hierarchy_id
132 and pghv.hierarchy_version_id = pghn.hierarchy_version_id
133 and pghn.node_type = 'PAR'
134 and pghn.entity_id = hou.organization_id
135 and hou.business_group_id = pgh.business_group_id
136 and hou.organization_id = hoi.organization_id
137 and hoi.org_information_context = 'MWR_Info'
138 and pghv.hierarchy_version_id = pghn2.hierarchy_version_id
139 and pghn.business_group_id = pghn2.business_group_id
140 and pghn2.node_type = 'EST'
141 and pghn2.entity_id = loc.location_id
142 and nvl(loc.loc_information17,loc.region_2) = Pay_Magtape_Generic.Get_Parameter_Value(
143 'TRANSFER_STATE_ABBREV') --#11936382 added nvl(loc.loc_information17)
144 and loc.location_id = lei.location_id
145 and lei.information_type = 'Multi Work Site Information'
146 UNION ALL
147 Select 'TRANSFER_ADDRESS=P' ,NULL
148 ,'TRANSFER_CITY=P' ,NULL
149 ,'TRANSFER_STATE=P' ,NULL
150 ,'TRANSFER_ZIP=P' ,NULL
151 ,'TRANSFER_LEGAL_NAME=P' ,'No Worksite defined for earnings'
152 ,'TRANSFER_RPT_UNIT_NO=P' ,'99999'
153 ,'TRANSFER_TRADE_NAME=P' ,'No Worksite defined for earnings'
154 ,'TRANSFER_WRKSIT_DESC=P' ,'No Worksite defined for earnings'
155 ,'TRANSFER_COMNT_1=P' ,NULL
156 ,'TRANSFER_COMNT_2=P' ,NULL
157 ,'TRANSFER_COMNT_3=P' ,NULL
158 ,'TRANSFER_COMMENTS=P' ,NULL
159 ,'TRANSFER_EST_ID=P' ,'-99999'
160 ,'TRANSFER_REC_COUNT=P' ,pay_us_mwr_reporting_pkg.estab_count
161 ,'TRANSFER_HEADER=P' ,pay_us_mwr_reporting_pkg.est_id
162 From DUAL;
163 -- NEED TO ADD UNION ALL TO THIS CURSOR.
164
165 /*******************************************************************/
166 -- 'level_cnt' will allow the cursors to select function results,
167 -- whether it is a standard fuction such as to_char or a function
168 -- defined in a package (with the correct pragma restriction).
169 /*******************************************************************/
170 level_cnt NUMBER;
171
172
173
174 FUNCTION get_mwr_values(p_payroll_action_id number
175 ,p_fips_code in varchar2
176 ,p_sui_id in varchar2
177 ,p_est_id in varchar2
178 ,p_fed_ein in varchar2
179 )
180 RETURN varchar2;
181
182 FUNCTION derive_sui_id ( p_state_code in varchar2
183 ,p_sui_id in varchar2
184 )
185 RETURN varchar2;
186
187 FUNCTION REMOVE_RPT_TOTALS(p_payroll_action_id number)
188 RETURN NUMBER;
189
190
191 PROCEDURE range_cursor( p_payroll_action_id in number
192 ,p_sql_string out NOCOPY varchar2);
193
194 PROCEDURE action_creation( p_payroll_action_id in number
195 ,p_start_assignment in number
196 ,p_end_assignment in number
197 ,p_chunk in number);
198
199 FUNCTION LOAD_RPT_TOTALS( p_payroll_action_id number)
200 RETURN NUMBER;
201
202 FUNCTION update_global_values(p_estab_ID number,
203 p_state_abbrev varchar2)
204 RETURN NUMBER;
205
206
207 END pay_us_mwr_reporting_pkg;