1 PACKAGE pay_us_payslip_simulation_main AUTHID CURRENT_USER AS
2 /* $Header: pyuspssm.pkh 120.0.12020000.6 2013/03/07 16:26:52 emunisek noship $ */
3 /*
4
5 Change List
6 -----------
7 Date Name Vers Bug No Description
8 ----------- ---------- ----- ------- -------------------------------
9 04-FEB-2013 emunisek 120.0 Created. Bug#16082307
10 04-FEB-2013 emunisek 120.1 After enabling Dual Checkin,
11 arcsing in file to generate
12 Checkfile Equivalence
13 17-FEB-2013 emunisek 120.2 Corrected cursor get_action_details
14 25-FEB-2013 emunisek 120.4 Corrected cursor csr_get_sim_archived_info
15 to retrieve Payroll Action details as
16 required. Added Type conversion in cursor
17 csr_get_archived_regions to remove invalid
18 number error.
19 07-FEB-2013 emunisek 120.5 16281614 Created procedure simulation_generate. This
20 was delivered earlier through package
21 pay_payroll_xml_extract_pkg.
22 */
23
24 emp_elements_tab pay_ac_action_arch.emp_element_table;
25 ltr_summary_labels pay_ac_action_arch.labels_tbl;
26
27 lrr_act_tab pay_emp_action_arch.action_info_table ;
28
29 ele_input_tab pay_emp_action_arch.action_info_table ;
30
31 g_action_ctx_id NUMBER;
32 g_custom_context pay_action_information.action_information_category%TYPE;
33 g_debug BOOLEAN;
34 g_install_leg_check CHAR(1) := 'Y';
35
36 gv_reporting_level VARCHAR2(30) := 'GRE';
37 /* gv_reporting_level will be assigned with 'TAXGRP' for Canada as
38 necessary */
39 gv_person_lang VARCHAR2(30) := 'US';
40 gv_ytd_balance_dimension VARCHAR2(80) := '_ASG_GRE_YTD';
41 gv_ptd_balance_dimension VARCHAR2(80) := '_ASG_GRE_PTD';
42 gv_run_balance_dimension VARCHAR2(80) := '_ASG_GRE_RUN';
43
44 /*Below cursors are used by PAY_PAYROLL_XML_EXTRACT_PKG Package to
45 retrieve the information from PAY_SIMULATION_INFORMATION table*/
46
47 /* Action Context Type "ACTION INFO" is used in the Payroll Simulator
48 Code to designate the records that hold the information related to
49 Payroll and Assignment Actions */
50
51 CURSOR get_leg_code(cp_action_context_id NUMBER)
52 IS
53 SELECT psi.action_information4
54 FROM pay_simulation_information psi
55 WHERE psi.action_information2 = TO_CHAR(cp_action_context_id)
56 AND psi.action_context_type = 'ACTION INFO'
57 AND psi.action_information_category = 'SIMULATION_ACTION_INFORMATION';
58
59 CURSOR c_bgid (cp_assgn_action_id NUMBER)
60 IS
61 SELECT psi.action_information3
62 FROM pay_simulation_information psi
63 WHERE psi.action_information2 = TO_CHAR(cp_assgn_action_id)
64 AND psi.action_context_type = 'ACTION INFO'
65 AND psi.action_information_category = 'SIMULATION_ACTION_INFORMATION';
66
67 CURSOR csr_get_archived_regions(cp_action_context_id NUMBER,
68 cp_action_information_id NUMBER)
69 IS
70 SELECT DISTINCT action_information_category
71 FROM pay_simulation_information
72 WHERE ((action_context_type = 'AAP'
73 AND action_context_id = cp_action_context_id)
74 OR (action_context_type = 'PA'
75 AND action_context_id =
76 (SELECT action_context_id
77 FROM pay_simulation_information
78 WHERE action_information2 = TO_CHAR(cp_action_context_id)
79 AND action_context_type = 'ACTION INFO')))
80 OR (action_information_id = cp_action_information_id
81 AND cp_action_information_id IS NOT NULL)
82 ORDER BY decode (action_information_category,'EMPLOYEE DETAILS', 1, 2);
83
84 CURSOR csr_get_sim_archived_info (p_action_context_id NUMBER,
85 p_category VARCHAR2,
86 p_category_filter VARCHAR2,
87 p_action_information_id NUMBER)
88 IS
89 SELECT effective_date,
90 action_information1,
91 action_information2,
92 action_information3,
93 action_information4,
94 action_information5,
95 action_information6,
96 action_information7,
97 action_information8,
98 action_information9,
99 action_information10,
100 action_information11,
101 action_information12,
102 action_information13,
103 action_information14,
104 action_information15,
105 action_information16,
106 action_information17,
107 action_information18,
108 action_information19,
109 action_information20,
110 action_information21,
111 action_information22,
112 action_information23,
113 action_information24,
114 action_information25,
115 action_information26,
116 action_information27,
117 action_information28,
118 action_information29,
119 action_information30
120 FROM pay_simulation_information
121 WHERE ((action_context_id = p_action_context_id AND action_context_type = 'AAP') OR
122 (action_context_id = (SELECT action_context_id
123 FROM pay_simulation_information
124 WHERE action_information2 = TO_CHAR(p_action_context_id)
125 AND action_context_type = 'ACTION INFO')
126 AND action_context_type = 'PA'
127 AND ((action_information1 = p_category_filter AND
128 p_category IN ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code || ' EMPLOYER DETAILS')) OR
129 (p_category NOT IN ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code || ' EMPLOYER DETAILS')))))
130 AND action_information_category = p_category;
131
132 /*Below cursor is used by PAY_US_SIMULATION Package */
133
134 CURSOR get_action_details(cp_assignment_action_id NUMBER)
135 IS
136 SELECT psi.action_information3 business_group_id,
137 psi.tax_unit_id,
138 psi.effective_date,
139 psi.action_information1 payroll_action_id,
140 psi.assignment_id
141 FROM pay_simulation_information psi
142 WHERE psi.action_information2 = TO_CHAR(cp_assignment_action_id)
143 AND psi.action_information1 = TO_CHAR(psi.action_context_id)
144 AND psi.action_context_type = 'ACTION INFO'
145 AND psi.action_information_category = 'SIMULATION_ACTION_INFORMATION' ;
146
147 CURSOR get_us_employer_addr (cp_organization_id NUMBER,cp_payroll_action_id NUMBER)
148 IS
149 SELECT DISTINCT action_information5,
150 action_information6,
151 action_information7,
152 action_information8,
153 action_information10,
154 action_information12,
155 action_information13
156 FROM pay_simulation_information psi
157 WHERE psi.action_context_type = 'PA'
158 AND psi.action_information_category = 'ADDRESS DETAILS'
159 AND psi.action_information14 = 'Employer Address'
160 AND psi.action_context_id=cp_payroll_action_id
161 AND psi.action_information1 = cp_organization_id;
162
163 /* In Regular Payslip, the Net Pay details are retrieved from the
164 Payments related Dimensions. In Payroll Simulator, Payments is not
165 run. Hence the Net Pay details are computed along with Summary
166 Details during Archival and stored to ACTION_INFORMATION15 column
167 of Information Category "AC SUMMARY CURRENT" and "AC SUMMARY YTD"*/
168
169 CURSOR get_net_pay (cp_assignment_action_id NUMBER,
170 cp_dimension VARCHAR2)
171 IS
172 SELECT psi.action_information15
173 FROM pay_simulation_information psi
174 WHERE psi.action_context_id = cp_assignment_action_id
175 AND psi.action_context_type = 'AAP'
176 AND psi.action_information_category = 'AC SUMMARY '||cp_dimension;
177
178 /* Procedure : update_asg_data
179 Purpose : This procedure is to update the Assignment related data as
180 per the modifications specified on Payroll Simulator page
181 so that Payroll calculations happen accordingly. The
182 details specified on Payroll Simulator page are stored to
183 table PER_ASSIGNMENT_EXTRA_INFO by Core Payroll. This
184 procedure determines the necessary updates to Assignment
185 data and carries them as required.
186 Important : The changes made in this procedure are on the actual data.
187 But since the entire Payroll simulation process is rolled
188 back at Database level, none of these changes will get
189 saved to the database.
190 */
191
192 PROCEDURE update_asg_data(p_source_action_id NUMBER,
193 p_effective_date DATE DEFAULT NULL);
194
195 /* Procedure : archive_data
196 Purpose : This procedure captures the results of Payroll Simulator
197 Run to the table PAY_SIMULATION_INFORMATION. This
198 procedure is similar to the Payroll Archiver procedure
199 PAY_US_ACTION_ARCH.ACTION_ARCHIVE_DATA. The regular
200 Payroll Archiver procedure is based on Prepayments where
201 as the current procedure is based on actual Payroll Run
202 itself.
203 Important : The Data archival process is similar to the regular
204 Payroll Archiver except the Payment related data will be
205 skipped here as there are no Prepayments executed as part
206 of Payroll Simulation. All the data gathered during this
207 procedure is inserted into PAY_SIMULATION_INFORMATION
208 table autonomously so that the data can be used to
209 generate the Output, post Database level rollback. Any
210 information required for generating Output, that will not
211 be available due to rollback should be captured through
212 archive_data procedure
213 */
214
215 PROCEDURE archive_data(p_source_action_id NUMBER,
216 p_effective_date DATE DEFAULT NULL);
217
218 /* Procedure : simulation_generate
219 Purpose : This procedure interprets archived information, converts it to
220 XML and prints it to a BLOB. This is a private procedure. This
221 is created based on the procedure pay_payroll_xml_extract_pkg.
222 generate_internal which is used by Regular Payslip
223 Important :
224 */
225
226 PROCEDURE simulation_generate (
227 p_action_information_id NUMBER DEFAULT NULL,
228 p_action_context_id NUMBER,
229 p_custom_action_info_cat VARCHAR2 DEFAULT NULL,
230 p_custom_xml_procedure VARCHAR2,
231 p_generate_header_flag VARCHAR2, -- {Y/N}
232 p_root_tag VARCHAR2,
233 p_document_type VARCHAR2,
234 p_xml OUT NOCOPY BLOB);
235
236 /* Procedure : generate_xml
237 Purpose : This procedure is used to generate the XML Data necessary
238 to generate the Payroll Simulation Output. This procedure
239 is similar to the current XML generation procedure
240 PAY_PAYROLL_XML_EXTRACT_PKG.GENERATE.
241 Important : This procedure will be executed by Core Payroll post the
242 Database level rollback of changes made during Payroll
243 Simulation run. Hence any information required for
244 generating Output, that will not be available due to
245 rollback should be captured through archive_data procedure
246 and saved autonomously to PAY_SIMULATION_INFORMATION table
247 Parameter p_xml_code is to allow the Customers the ability
248 to use Custom XML Code. This is hidden from Customers as
249 of now and will be enabled if required in future. The
250 entire code to support Custom XML Code is already in
251 place. We need to add the Segment to input Custom XML Code
252 details at Business Group and Organization Level in "Self
253 Service Preference". The Segment name should be "Payroll
254 Simulator XML Code".
255 */
256
257 PROCEDURE generate_xml(p_assignment_id IN NUMBER,
258 p_xml_code IN VARCHAR2 DEFAULT NULL,
259 p_xml OUT NOCOPY BLOB);
260
261 /* Procedure : pre_processing
262 Purpose : This procedure is to execute any Pre-Processing tasks that
263 need to be carried before Payroll Simulation page is made
264 available to the user. For US Localization, we are using
265 this procedure to determine all those "Element Name -
266 Input Value" combinations applicable to the current
267 Assignment. These combinations are stored to table
268 PAY_SIMULATION_INFORMATION with ACTION_CONTEXT_TYPE as
269 "INPUTSLOV". This data is used by the LOV Queries related
270 to Earnings and Deductions regions of Payroll Simulator.
271 Important : This procedure is initiated from Payroll Simulator Page
272 and the data gathered by this procedure will not be
273 committed to the database. As of now, we are using it to
274 determine "Element Name-Input Value" combinations for
275 Value Set Queries as this can improve the performance of
276 LOVs. In future, if required, additional tasks can be
277 added as required.
278 */
279
280 PROCEDURE pre_processing(p_assignment_id NUMBER,
281 p_business_group_id NUMBER DEFAULT NULL,
282 p_legislation_code VARCHAR2 DEFAULT NULL,
283 p_effective_date DATE DEFAULT NULL);
284
285 END pay_us_payslip_simulation_main;
[Home] [Help]