[Home] [Help]
8: p_parameter_string in varchar2
9: ,p_token in varchar2
10: ,p_segment_number in number ) RETURN varchar2
11: IS
12: l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
13: l_start_pos NUMBER;
14: l_delimiter varchar2(1):=' ';
15: l_proc VARCHAR2(160):= g_package||'.get parameter ';
16: BEGIN
48:
49: CURSOR csr_parameter_info(p_payroll_action_id IN NUMBER) IS
50: SELECT pay_ie_p30lock.get_parameter(legislative_parameters, p_token)
51: ,business_group_id
52: FROM pay_payroll_actions
53: WHERE payroll_action_id = p_payroll_action_id;
54: l_proc VARCHAR2(160):= g_package||'.get_all_parameters ';
55:
56: BEGIN
104: -- Changed the cursor to reduce the cost (5042843)
105: sqlstr := 'SELECT distinct asg.person_id
106: FROM per_periods_of_service pos,
107: per_assignments_f asg,
108: pay_payroll_actions ppa
109: WHERE ppa.payroll_action_id = :payroll_action_id
110: AND pos.person_id = asg.person_id
111: AND pos.period_of_service_id = asg.period_of_service_id
112: AND pos.business_group_id = ppa.business_group_id
137:
138: --Bug 4557715
139: CURSOR csr_assact is
140: select /*+ ORDERED USE_NL(pp1 asg)
141: INDEX(ppa PAY_PAYROLL_ACTIONS_PK)*/ paa.assignment_action_id,
142: paa.assignment_id,
143: paa.payroll_action_id,
144: ppa.date_earned,
145: paa_run.tax_unit_id
143: paa.payroll_action_id,
144: ppa.date_earned,
145: paa_run.tax_unit_id
146: from per_assignments_f asg,
147: pay_payroll_actions pp1,
148: pay_assignment_actions paa,
149: pay_payroll_actions ppa,
150: pay_action_interlocks pai_pre,
151: pay_assignment_actions paa_run,
145: paa_run.tax_unit_id
146: from per_assignments_f asg,
147: pay_payroll_actions pp1,
148: pay_assignment_actions paa,
149: pay_payroll_actions ppa,
150: pay_action_interlocks pai_pre,
151: pay_assignment_actions paa_run,
152: pay_payroll_actions ppa_run,
153: pay_all_payrolls_f pap
148: pay_assignment_actions paa,
149: pay_payroll_actions ppa,
150: pay_action_interlocks pai_pre,
151: pay_assignment_actions paa_run,
152: pay_payroll_actions ppa_run,
153: pay_all_payrolls_f pap
154: where pp1.payroll_action_id = pactid
155: and asg.business_group_id = pp1.business_group_id
156: and asg.person_id between stperson and endperson
169: and paa_run.action_status = 'C'
170: and paa_run.payroll_action_id = ppa_run.payroll_action_id
171: and paa_run.source_action_id IS NULL
172: and ppa_run.action_type in ('Q','R')
173: and not exists (select /*+ INDEX(payact PAY_PAYROLL_ACTIONS_PK) */ null
174: from pay_action_interlocks alock,
175: pay_assignment_actions assact,
176: pay_payroll_actions payact
177: where alock.locked_action_id = paa.assignment_action_id
172: and ppa_run.action_type in ('Q','R')
173: and not exists (select /*+ INDEX(payact PAY_PAYROLL_ACTIONS_PK) */ null
174: from pay_action_interlocks alock,
175: pay_assignment_actions assact,
176: pay_payroll_actions payact
177: where alock.locked_action_id = paa.assignment_action_id
178: and assact.assignment_action_id = alock.locking_action_id
179: and assact.payroll_action_id = payact.payroll_action_id
180: and payact.action_type = 'X'
254: paa.assignment_id,
255: paa.payroll_action_id,
256: ppa.date_earned,
257: paa.tax_unit_id
258: from pay_payroll_actions ppa,
259: pay_assignment_actions paa
260: -- per_assignments_f asg -- Bug Fix 4260031
261: where ppa.payroll_action_id = l_pay_action_id
262: -- and asg.business_group_id = ppa.business_group_id -- Bug Fix 4260031
274: and ppa.report_type = 'IEP30_PRGLOCK'
275: and ppa.report_category = 'ARCHIVE'
276: -- bug fix 5371061, added ordered join to remove merge join cartesian.
277: and not exists (select /*+ ORDERED */ null
278: from pay_payroll_actions payact,
279: pay_assignment_actions assact,
280: pay_action_interlocks alock
281: where alock.locked_action_id = paa.assignment_action_id
282: and assact.assignment_action_id = alock.locking_action_id
334: ,p_period_type in varchar2) IS
335:
336:
337: CURSOR cur_employer_info(
338: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
339: SELECT ppa_p30.payroll_action_id report_id,
340: paa_p30.assignment_id assignment_id,
341: pact_er.action_information5 employer_paye_number
342: FROM pay_payroll_actions ppa_p30,
338: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
339: SELECT ppa_p30.payroll_action_id report_id,
340: paa_p30.assignment_id assignment_id,
341: pact_er.action_information5 employer_paye_number
342: FROM pay_payroll_actions ppa_p30,
343: pay_assignment_actions paa_p30,
344: pay_action_interlocks pai_p30,
345: pay_assignment_actions paa_arc,
346: pay_action_interlocks pai_arc,
369: ,pact_er.action_information5
370: ORDER BY ppa_p30.payroll_action_id;
371: --
372: CURSOR cur_get_archive_pactid(
373: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
374: SELECT max(ppa_arc.payroll_action_id) archive_pactid
375: FROM pay_assignment_actions paa_p30,
376: pay_action_interlocks pai_p30,
377: pay_assignment_actions paa_arc,
374: SELECT max(ppa_arc.payroll_action_id) archive_pactid
375: FROM pay_assignment_actions paa_p30,
376: pay_action_interlocks pai_p30,
377: pay_assignment_actions paa_arc,
378: pay_payroll_actions ppa_arc
379: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
380: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
381: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
382: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
381: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
382: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
383: --
384: CURSOR cur_p30_start_date(
385: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
386: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
387: FROM pay_assignment_actions paa_p30,
388: pay_action_interlocks pai_p30,
389: pay_assignment_actions paa_arc,
386: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
387: FROM pay_assignment_actions paa_p30,
388: pay_action_interlocks pai_p30,
389: pay_assignment_actions paa_arc,
390: pay_payroll_actions ppa_arc
391: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
392: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
393: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
394: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
393: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
394: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
395: --
396: CURSOR cur_employer_address(
397: c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
398: SELECT substr(pai.action_information5,1,30) employer_tax_addr1
399: ,substr(pai.action_information6,1,30) employer_tax_addr2
400: ,substr(pai.action_information7,1,30) employer_tax_addr3
401: ,substr(pai.action_information26,1,30) employer_tax_contact
409: --
410: -- 4317512
411: /* YTD_Balances fill fetch that balance value whose source ids effective date is less than effective date
412: of P30 datalock */
413: CURSOR YTD_Balances (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
414: vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE,
415: vp_Balance_Name Pay_Balance_Types.Balance_Name%TYPE,
416: vp_Dimension_Name varchar2,
417: vp_action_context_id number,
424: pay_defined_balances pdb_ytdbal,
425: pay_balance_types pbt_ytdbal,
426: pay_balance_dimensions pbd_ytdbal,
427: pay_assignment_actions paa_src,
428: pay_payroll_actions ppa_src
429: WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
430: AND paa_p30.assignment_id = vp_Assignment_Id
431: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
432: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
447: ORDER BY pact_ytdbal.effective_date DESC -- Fix Tar 4033038.994
448: ,pact_ytdbal.ACTION_CONTEXT_ID DESC
449: ,nvl(pact_ytdbal.action_information5,0) DESC;
450: --
451: CURSOR cur_get_prev_p30_lock_id (vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
452: ,vp_assignment_id pay_assignment_actions.assignment_action_id%TYPE
453: ,vp_tax_start_date date
454: ,vp_report_end_date date) IS
455: SELECT ppa.payroll_action_id prev_data_lock_id,
453: ,vp_tax_start_date date
454: ,vp_report_end_date date) IS
455: SELECT ppa.payroll_action_id prev_data_lock_id,
456: to_date(substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
457: FROM pay_payroll_actions ppa
458: ,pay_assignment_actions paa
459: WHERE ppa.payroll_action_id = paa.payroll_action_id
460: AND ppa.report_type = 'IEP30_PRGLOCK'
461: AND paa.assignment_id = vp_assignment_id
461: AND paa.assignment_id = vp_assignment_id
462: AND paa.assignment_action_id = (
463: SELECT to_number(substr(max(lpad(paa2.action_sequence,15,'0')||
464: paa2.assignment_action_id),16))--Bug No 3318509
465: FROM pay_payroll_actions ppa2
466: ,pay_assignment_actions paa2
467: WHERE ppa2.payroll_action_id = paa2.payroll_action_id
468: AND ppa2.report_type = 'IEP30_PRGLOCK'
469: AND paa2.assignment_id = vp_assignment_id
472: BETWEEN vp_tax_start_date AND vp_report_end_date)
473: ORDER BY ppa.payroll_action_id DESC;
474:
475: -- Report End Date
476: CURSOR cur_end_date(vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
477: SELECT to_date(substr(ppa_p30.legislative_parameters,instr(ppa_p30.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') end_date
478: FROM pay_payroll_actions ppa_p30
479: WHERE ppa_p30.payroll_action_id=vp_payroll_action_id;
480:
474:
475: -- Report End Date
476: CURSOR cur_end_date(vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
477: SELECT to_date(substr(ppa_p30.legislative_parameters,instr(ppa_p30.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') end_date
478: FROM pay_payroll_actions ppa_p30
479: WHERE ppa_p30.payroll_action_id=vp_payroll_action_id;
480:
481: -- Start date of Tax Year
482: CURSOR cur_get_start_date (vp_report_end_date date) IS
487:
488: /* SR 17318286.6 rbhardwa changes start here */
489: --Bug Fix 4032212 This cursor is added to get the max action_context_id against the P30 Data Lock id
490: -- Changed to fetch correct action context if Archiver is run for Period 2 first and then for period1
491: CURSOR get_action_context (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
492: vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE) IS
493: SELECT fnd_number.canonical_to_number(substr(max(lpad(paa_run.action_sequence,15,'0')||pact_ytdbal.ACTION_CONTEXT_ID),16))
494: FROM pay_assignment_actions paa_p30,
495: pay_action_interlocks pai_p30,
496: pay_assignment_actions paa_arc,
497: pay_action_information pact_ytdbal,
498: pay_action_interlocks pai_arc,
499: pay_assignment_actions paa_run,
500: pay_payroll_actions ppa_run
501: -- ,pay_pre_payments ppp --Bug Fix 4049831 Added join with pay_pre_payments table
502: WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
503: AND paa_p30.assignment_id = vp_Assignment_Id
504: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
541: --
542: l_p30_start_tag varchar2(20);
543: l_p30_end_tag varchar2(20);
544: --
545: l_archive_pactid pay_payroll_actions.payroll_action_id%TYPE;
546: --
547: l_employer_paye_number varchar2(80);
548: l_employer_number varchar2(10);
549: l_employer_name varchar2(30);