1 package pay_sg_iras_amend_archive AUTHID CURRENT_USER as
2 /* $Header: pysgiraa.pkh 120.0.12010000.1 2009/01/29 06:42:33 jalin noship $ */
3 level_cnt number;
4
5 --
6 type t_balanceid_store_rec is record
7 ( user_entity_id ff_user_entities.user_entity_id%type,
8 defined_balance_id pay_defined_balances.defined_balance_id%type );
9 --
10 type t_ytd_balanceid_store_tab is table of t_balanceid_store_rec index by binary_integer;
11 t_ytd_balanceid_store t_ytd_balanceid_store_tab;
12 t_ytd_balanceid_store_ir8a t_ytd_balanceid_store_tab;
13 t_ytd_balanceid_store_ir8s t_ytd_balanceid_store_tab;
14 t_ytd_a8a_balanceid_store t_ytd_balanceid_store_tab;
15 --
16 type t_month_balanceid_store_tab is table of t_balanceid_store_rec index by binary_integer;
17 t_month_balanceid_store t_month_balanceid_store_tab;
18
19 --
20 ---------------------------------------------------------------------
21 -- 3435334 Record of person_ids with same national identifier
22 ---------------------------------------------------------------------
23 type t_person_id_tab is table of per_all_people_f.person_id%type index by binary_integer;
24 g_person_id_tab t_person_id_tab;
25 --
26 type t_rehire_same_person_record is record
27 (
28 person_id per_all_people_f.person_id%type
29 );
30 --------------------------------------------------------------------
31 -- Bug 4688761 Record of person_ids with archived person_id
32 --------------------------------------------------------------------
33 type t_archived_person_rec is record
34 (
35 person_id per_all_people_f.person_id%type
36 );
37 type t_archived_person_tab is table of t_archived_person_rec index by binary_integer;
38 t_archived_person t_archived_person_tab;
39
40 -------------------------------------------------------------------
41 -- 3956870 Defined to store the balance status details
42 -------------------------------------------------------------------
43 type balance_status_store_rec is record
44 ( business_group_id pay_balance_validation.business_group_id%type,
45 defined_balance_id pay_balance_validation.defined_balance_id%type,
46 run_balance_status pay_balance_validation.run_balance_status%type
47 );
48 --
49 type t_balance_status_store_tab is table of balance_status_store_rec index by binary_integer;
50 t_bal_stat_rec t_balance_status_store_tab;
51
52 --------------------------------------------------------------------
53 -- These are PUBLIC procedures are required by the Archive process.
54 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
55 -- the archive process knows what code to execute for each step of
56 -- the archive.
57 --------------------------------------------------------------------
58 procedure range_code
59 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
60 p_sql out nocopy varchar2 );
61 --
62 procedure assignment_action_code
63 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
64 p_start_person_id in per_all_people_f.person_id%type,
65 p_end_person_id in per_all_people_f.person_id%type,
66 p_chunk in number );
67 --
68 procedure initialization_code
69 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type );
70 --
71 procedure archive_code
72 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
73 p_effective_date in date );
74 --
75 procedure deinit_code
76 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type );
77 ----------------------------------------------------------------------------
78 -- These are PRIVATE procedures that required for the Archive
79 -- process.
80 --------------------------------------------------------------------
81 procedure archive_balances
82 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
83 p_person_id in per_all_people_f.person_id%type,
84 p_business_group_id in hr_organization_units.business_group_id%type,
85 p_tax_unit_id in ff_archive_item_contexts.context%type,
86 p_basis_year in varchar2 );
87 --
88 ---------------------------------------------------------------------------
89 -- Bug #4314453, added legal_entity_id
90 ---------------------------------------------------------------------------
91 procedure archive_shares_details
92 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
93 p_person_id in per_all_people_f.person_id%type,
94 p_tax_unit_id in ff_archive_item_contexts.context%type,
95 p_basis_start in date,
96 p_basis_end in date );
97 --
98 procedure archive_ir8s_c_details
99 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
100 p_person_id in per_all_people_f.person_id%type,
101 p_tax_unit_id in ff_archive_item_contexts.context%type,
102 p_business_group_id in per_assignments_f.business_group_id%type,
103 p_basis_start in date,
104 p_basis_end in date );
105 --
106 procedure archive_ir8s_c_detail_moas
107 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
108 p_1_person_id in per_all_people_f.person_id%type,
109 p_person_id in per_all_people_f.person_id%type,
110 p_tax_unit_id in ff_archive_item_contexts.context%type,
111 p_business_group_id in per_assignments_f.business_group_id%type,
112 p_basis_start in date,
113 p_basis_end in date );
114 --
115 procedure archive_item
116 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
117 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
118 p_archive_value in ff_archive_items.value%type );
119 --
120 procedure archive_item_2
121 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
122 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
123 p_archive_value in ff_archive_items.value%type,
124 p_context_value2 in ff_archive_item_contexts.context%type );
125 --
126 procedure archive_item_3
127 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
128 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
129 p_archive_value in ff_archive_items.value%type,
130 p_context_value2 in ff_archive_item_contexts.context%type,
131 p_context_value3 in ff_archive_item_contexts.context%type );
132 -------------------------------------------------------------------------------
133 -- Bug 3435334 - New function introduced for implementing duplicate person logic
134 -- with pre-processor
135 -------------------------------------------------------------------------------
136 function employee_if_latest
137 ( p_national_identifier in varchar2,
138 p_person_id in per_all_people_f.person_id%type,
139 p_setup_action_id in pay_payroll_actions.payroll_action_id%type,
140 p_report_type in varchar2 ) return boolean ;
141 --
142 ----------------------------------------------------------------------------
143 -- Bug 4688761 - New function to check if the person_id has been archived once
144 ----------------------------------------------------------------------------
145 function person_if_archived (p_person_id in per_all_people_f.person_id%type) return boolean;
146 --
147 ----------------------------------------------------------------------------
148 -- Bug 4890964 - The function to get the LE if it is with the latest primary
149 -- assignment for multi-LEs.
150 ----------------------------------------------------------------------------
151 function pri_if_latest
152 ( p_person_id in per_all_people_f.person_id%type
153 , p_tax_unit_id in ff_archive_item_contexts.context%type
154 , p_basis_start in date
155 , p_basis_end in date) return boolean;
156 --
157 ----------------------------------------------------------------------------
158 -- Bug 4890964 - The function to get the assignment with LE if its the
159 -- latest primary assignment
160 ----------------------------------------------------------------------------
161 function pri_LE_if_latest
162 ( p_person_id in per_all_people_f.person_id%type
163 , p_tax_unit_id in ff_archive_item_contexts.context%type
164 , p_basis_start in date
165 , p_basis_end in date) return number;
166 --
167 ----------------------------------------------------------------------------
168 -- Bug 4890964 - The function to get the assignment with LE if its the
169 -- latest assignment without primay flag defined.
170 ----------------------------------------------------------------------------
171 function id_LE_if_latest
172 ( p_person_id in per_all_people_f.person_id%type
173 , p_tax_unit_id in ff_archive_item_contexts.context%type
174 , p_basis_start in date
175 , p_basis_end in date) return number;
176 --
177 ---------------------------------------------------------------------
178 -- PUBLIC cursors used to retrieve data and pass it to IRAS formulae.
179 -- Passes parameters to SG_ARCHIVE_HEADER formula
180 -- Just need 1 context for Org Info
181 ---------------------------------------------------------------------
182 cursor archive_header is
183 select 'ASSIGNMENT_ACTION_ID=C',
184 pac.assignment_action_id,
185 'BASIS_YEAR=P',
186 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year,
187 'CREATION_DATE=P',
188 to_char(sysdate,'YYYYMMDD') creation_date
189 from pay_payroll_actions ppa,
190 pay_assignment_actions pac,
191 ff_archive_items ffi,
192 ff_database_items fdi
193 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
194 and ppa.payroll_action_id = pac.payroll_action_id
195 and pac.action_status = 'C'
196 and ffi.context1 = pac.assignment_action_id /* Added joins for bug:3232300 */
197 and fdi.user_name = 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME'
198 and ffi.user_entity_id = fdi.user_entity_id
199 and rownum=1;
200 ---------------------------------------------------------------------
201 -- Passes parameters to SG_ARCHIVE_DETAILS formula
202 ---------------------------------------------------------------------
203 cursor archive_details is
204 select distinct
205 'ASSIGNMENT_ACTION_ID=C',
206 pac.assignment_action_id,
207 'TAX_UNIT_ID=C',
208 to_number(ac2.context) tax_unit_id,
209 'BASIS_YEAR=P',
210 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year,
211 'P_ASSIGNMENT_ACTION_ID=P',
212 to_char(pac.assignment_action_id),
213 'PERSON_ID=P',
214 to_char(paa.person_id) person_id,
215 'ASSIGNMENT_NUMBER=P',
216 paa.assignment_number assignment_number
217 from per_all_assignments_f paa,
218 pay_payroll_actions ppa,
219 pay_assignment_actions pac,
220 ff_archive_items fai,
221 ff_archive_item_contexts ac2
222 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
223 and pac.action_status = 'C'
224 and ppa.payroll_action_id = pac.payroll_action_id
225 and paa.assignment_id = pac.assignment_id
226 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
227 and paa.business_group_id = ppa.business_group_id
228 and fai.context1 = pac.assignment_action_id
229 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
230 order by paa.person_id;
231 ---------------------------------------------------------------------
232 -- Passes parameters to SG_ARCHIVE_DETAILS formula
233 ---------------------------------------------------------------------
234 cursor archive_org_details is
235 select distinct
236 'TAX_UNIT_ID=C',
237 to_number(ac2.context) tax_unit_id,
238 'BASIS_YEAR=P',
239 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year
240 from per_all_assignments_f paa,
241 pay_payroll_actions ppa,
242 pay_assignment_actions pac,
243 ff_archive_items fai,
244 ff_archive_item_contexts ac2
245 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
246 and pac.action_status = 'C'
247 and ppa.payroll_action_id = pac.payroll_action_id
248 and paa.assignment_id = pac.assignment_id
249 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
250 AND paa.business_group_id = ppa.business_group_id
251 and fai.context1 = pac.assignment_action_id
252 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
253 order by paa.person_id;
254 ---------------------------------------------------------------------
255 end pay_sg_iras_amend_archive;
[Home] [Help]