4: -- ~ Global Variables ~
5: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
6:
7: g_proc_name varchar2(65):= 'PQP_US_SRS_Extracts.';
8: g_assignment_id per_all_assignments_f.assignment_id%Type;
9: g_business_group_id per_all_assignments_f.business_group_id%TYPE;
10: g_legislation_code varchar2(20);
11: g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
12: g_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
5: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
6:
7: g_proc_name varchar2(65):= 'PQP_US_SRS_Extracts.';
8: g_assignment_id per_all_assignments_f.assignment_id%Type;
9: g_business_group_id per_all_assignments_f.business_group_id%TYPE;
10: g_legislation_code varchar2(20);
11: g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
12: g_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
13: g_ext_dfn_id pqp_extract_attributes.ext_dfn_id%TYPE;
42: g_plan_end_date date;
43: --
44: Type g_srs_plan_rec Is Record
45: ( plan_name varchar2(150)
46: ,assignment_id per_all_assignments_f.assignment_id%TYPE);
47: Type t_srs_plan_type Is Table Of g_srs_plan_rec Index By Binary_Integer;
48: g_extract_plan_names t_srs_plan_type ;
49: g_extract_plan_name varchar2(150);
50: --
106: --
107: -- Get the Payroll Id for the given assignment id, used in case the extract is PTD type
108: --
109: Cursor csr_get_payroll_id
110: ( c_assignment_id In per_all_assignments_f.assignment_id%TYPE
111: ,c_effective_date In date) Is
112: Select paa.payroll_id
113: ,paa.effective_start_date
114: ,paa.effective_end_date
111: ,c_effective_date In date) Is
112: Select paa.payroll_id
113: ,paa.effective_start_date
114: ,paa.effective_end_date
115: from per_all_assignments_f paa
116: where paa.assignment_id = c_assignment_id
117: and ((c_effective_date between paa.effective_start_date
118: and paa.effective_end_date
119: )
118: and paa.effective_end_date
119: )
120: Or
121: (paa.effective_start_date =( select max(pax.effective_start_date)
122: from per_all_assignments_f pax
123: where pax.assignment_id = c_assignment_id)
124: )
125: );
126: --
125: );
126: --
127: -- Get the payroll_name and payroll frequency
128: --
129: Cursor csr_payroll_name (c_payroll_id In per_all_assignments_f.payroll_id%TYPE
130: ,c_effective_date In date ) Is
131: Select payroll_name, period_type
132: from pay_payrolls_f
133: Where c_effective_date Between effective_start_date
137: --
138: -- Cursor to get the SRS Plans assignment extra info
139: --
140: Cursor csr_assig_extra_info
141: ( c_assignment_id In per_all_assignments_f.assignment_id%TYPE
142: ) Is
143: Select *
144: From per_assignment_extra_info pae
145: Where pae.assignment_id = c_assignment_id
159: --
160: -- Cursor to get the primary assig. records active as of the given period
161: --
162: Cursor csr_assig_rec
163: ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE
164: ,c_business_group_id in per_all_assignments_f.business_group_id%TYPE
165: ,c_effective_date in date
166: ,c_extract_start_date in date
167: ,c_extract_end_date in date ) Is
160: -- Cursor to get the primary assig. records active as of the given period
161: --
162: Cursor csr_assig_rec
163: ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE
164: ,c_business_group_id in per_all_assignments_f.business_group_id%TYPE
165: ,c_effective_date in date
166: ,c_extract_start_date in date
167: ,c_extract_end_date in date ) Is
168: select *
165: ,c_effective_date in date
166: ,c_extract_start_date in date
167: ,c_extract_end_date in date ) Is
168: select *
169: from per_all_assignments_f paa
170: where paa.business_group_id = c_business_group_id
171: and paa.assignment_id = c_assignment_id
172: and paa.primary_flag = 'Y'
173: and ((c_effective_date between paa.effective_start_date
174: and paa.effective_end_date
175: )
176: Or
177: ( paa.effective_end_date = ( Select max(asx.effective_end_date)
178: from per_all_assignments_f asx
179: Where asx.assignment_id = paa.assignment_id
180: and asx.primary_flag = 'Y'
181: and ((asx.effective_end_date between c_extract_start_date
182: and c_extract_end_date
195: --
196: -- Cursor to get the secondary assig. records active as of the given period.
197: --
198: Cursor csr_sec_assignments
199: (c_primary_assignment_id In per_all_assignments_f.assignment_id%TYPE
200: ,c_person_id In per_all_people_f.person_id%TYPE
201: ,c_effective_date In date
202: ,c_extract_start_date In date
203: ,c_extract_end_date In Date ) Is
201: ,c_effective_date In date
202: ,c_extract_start_date In date
203: ,c_extract_end_date In Date ) Is
204: Select *
205: From per_all_assignments_f asg
206: Where asg.person_id = c_person_id
207: And asg.assignment_id <> c_primary_assignment_id
208: And asg.assignment_type ='E'
209: And (( c_effective_date Between asg.effective_start_date
210: And asg.effective_end_date
211: )
212: Or
213: ( asg.effective_end_date = ( Select max(asx.effective_end_date)
214: from per_all_assignments_f asx
215: Where asx.assignment_id = asg.assignment_id
216: and asx.person_id = c_person_id
217: and asx.assignment_type ='E'
218: and ((asx.effective_end_date between c_extract_start_date
240: Function Get_Payroll_Names
241: (p_effective_date In Date
242: ,p_payroll_name In varchar2) Return Varchar2;
243: Function Get_Payroll_Name
244: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
245: ,p_payroll_name In out nocopy varchar2) Return Number;
246:
247: Function Get_Payroll_Start_Date
248: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
244: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
245: ,p_payroll_name In out nocopy varchar2) Return Number;
246:
247: Function Get_Payroll_Start_Date
248: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
249: ,p_start_date In out nocopy Varchar2) Return Number;
250:
251: Function Get_Payroll_End_Date
252: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
248: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
249: ,p_start_date In out nocopy Varchar2) Return Number;
250:
251: Function Get_Payroll_End_Date
252: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
253: ,p_end_date In out nocopy Varchar2) Return Number;
254:
255: Function Get_Actual_Pay_Date
256: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
252: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
253: ,p_end_date In out nocopy Varchar2) Return Number;
254:
255: Function Get_Actual_Pay_Date
256: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
257: ,p_pay_date In out nocopy Varchar2) Return Number;
258:
259: Function Get_SRS_Plan_Name
260: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
256: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
257: ,p_pay_date In out nocopy Varchar2) Return Number;
258:
259: Function Get_SRS_Plan_Name
260: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
261: ,p_SRS_Plan_Name In out nocopy Varchar2) Return Number;
262:
263: Function Get_Separation_Date
264: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
260: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
261: ,p_SRS_Plan_Name In out nocopy Varchar2) Return Number;
262:
263: Function Get_Separation_Date
264: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
265: ,p_Separation_Date In out nocopy Varchar2) Return Number;
266:
267: Function Get_Assig_Status
268: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
264: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
265: ,p_Separation_Date In out nocopy Varchar2) Return Number;
266:
267: Function Get_Assig_Status
268: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
269: ,p_status_code In out nocopy Varchar2) Return Number;
270:
271: Function Get_Person_Indentifier
272: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE -- context
268: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
269: ,p_status_code In out nocopy Varchar2) Return Number;
270:
271: Function Get_Person_Indentifier
272: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE -- context
273: ,p_person_identifier In out nocopy Varchar2 ) Return Number;
274:
275: Function Get_SRS_Deduction_Balances
276: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
272: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE -- context
273: ,p_person_identifier In out nocopy Varchar2 ) Return Number;
274:
275: Function Get_SRS_Deduction_Balances
276: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
277: ,p_balance_name In pay_balance_types.balance_name%TYPE
278: ,p_balance_amount In out nocopy Number
279: ) Return Number;
280:
278: ,p_balance_amount In out nocopy Number
279: ) Return Number;
280:
281: Function Get_Balance_Value
282: (p_business_group_id In per_all_assignments_f.business_group_id%TYPE
283: ,p_assignment_id In per_all_assignments_f.assignment_id%TYPE
284: ,p_effective_date In date
285: ,p_balance_name In varchar2
286: ,p_dimension_name In varchar2
279: ) Return Number;
280:
281: Function Get_Balance_Value
282: (p_business_group_id In per_all_assignments_f.business_group_id%TYPE
283: ,p_assignment_id In per_all_assignments_f.assignment_id%TYPE
284: ,p_effective_date In date
285: ,p_balance_name In varchar2
286: ,p_dimension_name In varchar2
287: ) Return Number;
290: (p_effective_date In Date
291: ,p_extract_name In varchar2 ) Return Varchar2;
292:
293: Function Pay_US_SRS_Main_Criteria
294: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
295: ,p_effective_date In date
296: ,p_business_group_id In per_all_assignments_f.business_group_id%TYPE
297: ,p_extract_plan_name In Varchar2
298: ,p_extract_payroll_name In Varchar2 Default Null
292:
293: Function Pay_US_SRS_Main_Criteria
294: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
295: ,p_effective_date In date
296: ,p_business_group_id In per_all_assignments_f.business_group_id%TYPE
297: ,p_extract_plan_name In Varchar2
298: ,p_extract_payroll_name In Varchar2 Default Null
299: ) Return Varchar2;
300:
298: ,p_extract_payroll_name In Varchar2 Default Null
299: ) Return Varchar2;
300:
301: Function Create_Secondary_Assig_Lines
302: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
303: ) Return Varchar2;
304: Procedure Create_New_Lines
305: (p_pri_assignment_id In per_all_assignments_f.assignment_id%TYPE
306: ,p_sec_assignment_id In per_all_assignments_f.assignment_id%TYPE
301: Function Create_Secondary_Assig_Lines
302: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
303: ) Return Varchar2;
304: Procedure Create_New_Lines
305: (p_pri_assignment_id In per_all_assignments_f.assignment_id%TYPE
306: ,p_sec_assignment_id In per_all_assignments_f.assignment_id%TYPE
307: ,p_person_id In per_all_people_f.person_id%TYPE
308: ,p_record_name In Varchar2
309: );
302: (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
303: ) Return Varchar2;
304: Procedure Create_New_Lines
305: (p_pri_assignment_id In per_all_assignments_f.assignment_id%TYPE
306: ,p_sec_assignment_id In per_all_assignments_f.assignment_id%TYPE
307: ,p_person_id In per_all_people_f.person_id%TYPE
308: ,p_record_name In Varchar2
309: );
310:
308: ,p_record_name In Varchar2
309: );
310:
311: Function Get_Secondary_Assignments
312: (p_primary_assignment_id In per_all_assignments_f.assignment_id%TYPE
313: ,p_person_id In per_all_people_f.person_id%TYPE
314: ,p_effective_date In date
315: ,p_extract_start_date In date
316: ,p_extract_end_date In date ) Return Varchar2;
315: ,p_extract_start_date In date
316: ,p_extract_end_date In date ) Return Varchar2;
317:
318: Function Check_Assig_Extra_Info
319: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
320: ,p_extract_plan_name In varchar2
321: ,p_extract_start_date In date
322: ,p_extract_end_date In date
323: ) Return Varchar2 ;
343: ,p_ext_dtl_rec In out nocopy ben_ext_rslt_dtl%ROWTYPE
344: );
345:
346: Function Get_PTD_Start_End_Date
347: (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
348: ,p_effective_date In Date
349: ) Return Varchar2;
350:
351: Procedure Ins_Rslt_Dtl