[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: ,pay_action_information pai --12943478
155: where pp1.payroll_action_id = pactid
156: and asg.business_group_id = pp1.business_group_id
175: and pai.action_context_id=paa.assignment_action_id
176: and pai.action_information_category = 'IE EMPLOYEE DETAILS'
177: and pai.action_context_type='AAP'
178: --12943478
179: and not exists (select /*+ INDEX(payact PAY_PAYROLL_ACTIONS_PK) */ null
180: from pay_action_interlocks alock,
181: pay_assignment_actions assact,
182: pay_payroll_actions payact
183: where alock.locked_action_id = paa.assignment_action_id
178: --12943478
179: and not exists (select /*+ INDEX(payact PAY_PAYROLL_ACTIONS_PK) */ null
180: from pay_action_interlocks alock,
181: pay_assignment_actions assact,
182: pay_payroll_actions payact
183: where alock.locked_action_id = paa.assignment_action_id
184: and assact.assignment_action_id = alock.locking_action_id
185: and assact.payroll_action_id = payact.payroll_action_id
186: and payact.action_type = 'X'
260: paa.assignment_id,
261: paa.payroll_action_id,
262: ppa.date_earned,
263: paa.tax_unit_id
264: from pay_payroll_actions ppa,
265: pay_assignment_actions paa
266: -- per_assignments_f asg -- Bug Fix 4260031
267: where ppa.payroll_action_id = l_pay_action_id
268: -- and asg.business_group_id = ppa.business_group_id -- Bug Fix 4260031
280: and ppa.report_type = 'IEP30_PRGLOCK'
281: and ppa.report_category = 'ARCHIVE'
282: -- bug fix 5371061, added ordered join to remove merge join cartesian.
283: and not exists (select /*+ ORDERED */ null
284: from pay_payroll_actions payact,
285: pay_assignment_actions assact,
286: pay_action_interlocks alock
287: where alock.locked_action_id = paa.assignment_action_id
288: and assact.assignment_action_id = alock.locking_action_id
340: ,p_period_type in varchar2) IS
341:
342:
343: CURSOR cur_employer_info(
344: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
345: SELECT ppa_p30.payroll_action_id report_id,
346: paa_p30.assignment_id assignment_id,
347: pact_er.action_information5 employer_paye_number
348: FROM pay_payroll_actions ppa_p30,
344: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
345: SELECT ppa_p30.payroll_action_id report_id,
346: paa_p30.assignment_id assignment_id,
347: pact_er.action_information5 employer_paye_number
348: FROM pay_payroll_actions ppa_p30,
349: pay_assignment_actions paa_p30,
350: pay_action_interlocks pai_p30,
351: pay_assignment_actions paa_arc,
352: pay_action_interlocks pai_arc,
375: ,pact_er.action_information5
376: ORDER BY ppa_p30.payroll_action_id;
377: --
378: CURSOR cur_get_archive_pactid(
379: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
380: SELECT max(ppa_arc.payroll_action_id) archive_pactid
381: FROM pay_assignment_actions paa_p30,
382: pay_action_interlocks pai_p30,
383: pay_assignment_actions paa_arc,
380: SELECT max(ppa_arc.payroll_action_id) archive_pactid
381: FROM pay_assignment_actions paa_p30,
382: pay_action_interlocks pai_p30,
383: pay_assignment_actions paa_arc,
384: pay_payroll_actions ppa_arc
385: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
386: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
387: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
388: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
387: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
388: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
389: --
390: CURSOR cur_p30_start_date(
391: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
392: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
393: FROM pay_assignment_actions paa_p30,
394: pay_action_interlocks pai_p30,
395: pay_assignment_actions paa_arc,
392: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
393: FROM pay_assignment_actions paa_p30,
394: pay_action_interlocks pai_p30,
395: pay_assignment_actions paa_arc,
396: pay_payroll_actions ppa_arc
397: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
398: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
399: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
400: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
399: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
400: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
401: --
402: CURSOR cur_employer_address(
403: c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
404: SELECT substr(pai.action_information5,1,30) employer_tax_addr1
405: ,substr(pai.action_information6,1,30) employer_tax_addr2
406: ,substr(pai.action_information7,1,30) employer_tax_addr3
407: ,substr(pai.action_information26,1,30) employer_tax_contact
415: --
416: -- 4317512
417: /* YTD_Balances fill fetch that balance value whose source ids effective date is less than effective date
418: of P30 datalock */
419: CURSOR YTD_Balances (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
420: vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE,
421: vp_Balance_Name Pay_Balance_Types.Balance_Name%TYPE,
422: vp_Dimension_Name varchar2,
423: vp_action_context_id number,
430: pay_defined_balances pdb_ytdbal,
431: pay_balance_types pbt_ytdbal,
432: pay_balance_dimensions pbd_ytdbal,
433: pay_assignment_actions paa_src,
434: pay_payroll_actions ppa_src
435: WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
436: AND paa_p30.assignment_id = vp_Assignment_Id
437: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
438: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
453: ORDER BY pact_ytdbal.effective_date DESC -- Fix Tar 4033038.994
454: ,pact_ytdbal.ACTION_CONTEXT_ID DESC
455: ,nvl(pact_ytdbal.action_information5,0) DESC;
456: --
457: CURSOR cur_get_prev_p30_lock_id (vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
458: ,vp_assignment_id pay_assignment_actions.assignment_action_id%TYPE
459: ,vp_tax_start_date date
460: ,vp_report_end_date date) IS
461: SELECT ppa.payroll_action_id prev_data_lock_id,
459: ,vp_tax_start_date date
460: ,vp_report_end_date date) IS
461: SELECT ppa.payroll_action_id prev_data_lock_id,
462: to_date(substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
463: FROM pay_payroll_actions ppa
464: ,pay_assignment_actions paa
465: WHERE ppa.payroll_action_id = paa.payroll_action_id
466: AND ppa.report_type = 'IEP30_PRGLOCK'
467: AND paa.assignment_id = vp_assignment_id
467: AND paa.assignment_id = vp_assignment_id
468: AND paa.assignment_action_id = (
469: SELECT to_number(substr(max(lpad(paa2.action_sequence,15,'0')||
470: paa2.assignment_action_id),16))--Bug No 3318509
471: FROM pay_payroll_actions ppa2
472: ,pay_assignment_actions paa2
473: WHERE ppa2.payroll_action_id = paa2.payroll_action_id
474: AND ppa2.report_type = 'IEP30_PRGLOCK'
475: AND paa2.assignment_id = vp_assignment_id
478: BETWEEN vp_tax_start_date AND vp_report_end_date)
479: ORDER BY ppa.payroll_action_id DESC;
480:
481: -- Report End Date
482: CURSOR cur_end_date(vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
483: SELECT to_date(substr(ppa_p30.legislative_parameters,instr(ppa_p30.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') end_date
484: FROM pay_payroll_actions ppa_p30
485: WHERE ppa_p30.payroll_action_id=vp_payroll_action_id;
486:
480:
481: -- Report End Date
482: CURSOR cur_end_date(vp_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
483: SELECT to_date(substr(ppa_p30.legislative_parameters,instr(ppa_p30.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') end_date
484: FROM pay_payroll_actions ppa_p30
485: WHERE ppa_p30.payroll_action_id=vp_payroll_action_id;
486:
487: -- Start date of Tax Year
488: CURSOR cur_get_start_date (vp_report_end_date date) IS
493:
494: /* SR 17318286.6 rbhardwa changes start here */
495: --Bug Fix 4032212 This cursor is added to get the max action_context_id against the P30 Data Lock id
496: -- Changed to fetch correct action context if Archiver is run for Period 2 first and then for period1
497: CURSOR get_action_context (vp_Payroll_Action_Id Pay_Payroll_Actions.Payroll_Action_ID%TYPE,
498: vp_Assignment_Id Pay_Assignment_Actions.Assignment_Action_ID%TYPE) IS
499: SELECT fnd_number.canonical_to_number(substr(max(lpad(paa_run.action_sequence,15,'0')||pact_ytdbal.ACTION_CONTEXT_ID),16))
500: FROM pay_assignment_actions paa_p30,
501: pay_action_interlocks pai_p30,
502: pay_assignment_actions paa_arc,
503: pay_action_information pact_ytdbal,
504: pay_action_interlocks pai_arc,
505: pay_assignment_actions paa_run,
506: pay_payroll_actions ppa_run
507: -- ,pay_pre_payments ppp --Bug Fix 4049831 Added join with pay_pre_payments table
508: WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
509: AND paa_p30.assignment_id = vp_Assignment_Id
510: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
547: --
548: l_p30_start_tag varchar2(20);
549: l_p30_end_tag varchar2(20);
550: --
551: l_archive_pactid pay_payroll_actions.payroll_action_id%TYPE;
552: --
553: l_employer_paye_number varchar2(80);
554: l_employer_number varchar2(10);
555: l_employer_name varchar2(30);