[Home] [Help]
157: latest paye details.
158: 16-SEP-2005 K.Thampan 115.51 4553334. Amended cursor agg_latest_action.
159: 03-OCT-2005 T.Kumar 115.52 GSCC Corrections : Bug 4646368
160: 14-OCT-2005 npershad 115.53 4428406. Removed reference to redundant index
161: PAY_ASSIGNMENT_ACTIONS_N1 used in hints.
162: 23-DEC-2005 K.Thampan 115.54 4904738. Amended cursor csr_run_result not
163: to multiply prev pay and prev tax by 100.
164: 30-JAN-2006 K.Thampan 115.55 4774622. Fix performance bug.
165: 14-MAR-2006 K.Thampan 115.56 5042824. Fix performance bug.
565: l_start_year date; -- Bug 7410767 to store the start of financial year
566:
567: -- Bug 6900025 added l_eff_date parameter to check proper financial year for fecting PAYE details.
568: CURSOR csr_max_run_result(l_element_id number,l_eff_date date) IS
569: SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
570: pact PAY_PAYROLL_ACTIONS_PK,
571: r2 PAY_RUN_RESULTS_N50)
572: USE_NL(assact2, pact, r2) */
573: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
571: r2 PAY_RUN_RESULTS_N50)
572: USE_NL(assact2, pact, r2) */
573: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
574: r2.run_result_id),17))
575: FROM pay_assignment_actions assact2,
576: pay_payroll_actions pact,
577: pay_run_results r2
578: WHERE assact2.assignment_id = p_assignment_id
579: AND r2.element_type_id+0 = l_element_id
590: and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
591: AND NOT EXISTS(
592: SELECT '1'
593: FROM pay_action_interlocks pai,
594: pay_assignment_actions assact3,
595: pay_payroll_actions pact3
596: WHERE pai.locked_action_id = assact2.assignment_action_id
597: AND pai.locking_action_id = assact3.assignment_action_id
598: AND pact3.payroll_action_id = assact3.payroll_action_id
714: cursor c_act is select act.assignment_action_id,
715: act.payroll_action_id,
716: pact.effective_date ,
717: pact.payroll_id
718: from pay_assignment_actions act,
719: pay_payroll_actions pact
720: where act.assignment_id = X_ASSIGNMENT_ID
721: and act.action_status = 'C'
722: and pact.payroll_action_id = act.payroll_action_id
723: and act.action_sequence = (
724: select /*+ ORDERED use_nl(a,pact2,t,r,v,f)
725: user_index(v, PAY_RUN_RESULT_VALUES_PK) */
726: max(a.action_sequence)
727: from pay_assignment_actions a
728: ,pay_payroll_actions pact2
729: ,pay_balance_types t
730: ,pay_balance_feeds_f f
731: ,pay_run_results r
764: fnd_number.canonical_to_number(substr(max(
765: lpad(paa.action_sequence,15,'0')||
766: paa.assignment_action_id),16)) assignment_action_id
767: from per_all_assignments_f a,
768: pay_assignment_actions paa,
769: pay_payroll_actions ppa,
770: pay_all_payrolls_f pay,
771: hr_soft_coding_keyflex flex
772: where a.person_id = c_person_id
801: c_effective_date IN DATE) is
802: SELECT /*+ USE_NL(paa, ppa) */
803: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
804: paa.assignment_action_id),16))
805: FROM pay_assignment_actions paa,
806: pay_payroll_actions ppa
807: WHERE
808: paa.assignment_id = c_assignment_id
809: AND ppa.payroll_action_id = paa.payroll_action_id
818: c_effective_date IN DATE) is
819: SELECT /*+ USE_NL(paa, ppa) */
820: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
821: paa.assignment_action_id),16))
822: FROM pay_assignment_actions paa,
823: pay_payroll_actions ppa
824: WHERE
825: paa.assignment_id = c_assignment_id
826: AND ppa.payroll_action_id = paa.payroll_action_id
849: cursor payroll_details(c_assignment_action_id in number) is
850: select paa.payroll_action_id,
851: ppa.effective_date, ppa.payroll_id
852: from pay_payroll_actions ppa,
853: pay_assignment_actions paa
854: where paa.assignment_action_id = c_assignment_action_id
855: and ppa.payroll_action_id = paa.payroll_action_id;
856: --
857: cursor agg_paye(c_person_id in number,
873: SELECT to_date('06-04-'||
874: to_char(fnd_number.canonical_to_number(to_char(ptp.regular_payment_date,'YYYY'))
875: + least(sign(ptp.regular_payment_date - to_date('06-04-'
876: || to_char(ptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
877: FROM per_time_periods ptp, pay_assignment_actions act, pay_payroll_actions pact
878: WHERE act.assignment_action_id = p_asg_action_id
879: AND act.payroll_Action_id = pact.payroll_action_id
880: AND pact.time_period_id = ptp.time_period_id;
881: --
1697: select
1698: max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
1699: ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
1700: from ff_archive_items fai, -- of P45 report type
1701: pay_assignment_actions act,
1702: pay_payroll_actions ppa
1703: where ppa.report_type='P45'
1704: and ppa.report_qualifier='GB'
1705: and ppa.report_category ='P45'
1716: from ff_archive_items fai
1717: where fai.user_entity_id in (g_tax_ref_transfer_eid,g_period_of_service_eid)
1718: and fai.context1 = (select max(act.assignment_action_id)
1719: from pay_payroll_actions ppa,
1720: pay_assignment_actions act
1721: where ppa.report_type='P45'
1722: and ppa.report_qualifier='GB'
1723: and ppa.report_category ='P45'
1724: and ppa.action_type = 'X'
1869: c_effective_end_date in date) is
1870: select 1 from dual where exists
1871: (select pa.effective_date
1872: from pay_payroll_actions pa,
1873: pay_assignment_actions aa
1874: where aa.assignment_id = c_assignment_id
1875: and aa.payroll_action_id = pa.payroll_action_id
1876: and pa.action_type in ('R','Q','V','I','B')
1877: and pa.date_earned >= c_effective_end_date);
1885: c_effective_end_date in date,
1886: c_assignment_id in number) is
1887: select 1 from dual where exists
1888: (select paa.assignment_action_id
1889: from pay_assignment_actions paa,
1890: pay_payroll_actions ppa,
1891: per_time_periods ptp
1892: where ptp.time_period_id = ppa.time_period_id
1893: and ppa.payroll_action_id = paa.payroll_action_id
1989: cursor csr_latest_action (c_assignment_id in number,
1990: c_effective_end_date in date) is
1991: select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0') ||
1992: paa.assignment_action_id),16))
1993: FROM pay_assignment_actions paa,
1994: pay_payroll_actions ppa
1995: WHERE
1996: paa.assignment_id = c_assignment_id
1997: AND ppa.payroll_action_id = paa.payroll_action_id
2055: );
2056:
2057: l_proc CONSTANT VARCHAR2(35):= g_package||'arch_act_creation';
2058: l_number number;
2059: l_actid pay_assignment_actions.assignment_action_id%TYPE;
2060: l_non_p45_message varchar2(50);
2061: l_transfer_flag varchar2(1);
2062: l_override_date date;
2063: l_archive boolean;
2532: -- Using regular payment date for final payment date.
2533: if NOT (tax_code_ni(rec_asg.assignment_id,rec_asg.regular_payment_date))
2534: then
2535: hr_utility.trace('ARCHIVING FOR '||to_char(rec_asg.assignment_id));
2536: SELECT pay_assignment_actions_s.nextval
2537: INTO l_actid
2538: FROM dual;
2539: --
2540: hr_utility.set_location(l_proc,110);
2793: l_town_or_city per_addresses.town_or_city%TYPE;
2794: l_county per_addresses.region_1%TYPE;
2795: l_postal_code per_addresses.postal_code%TYPE;
2796: l_country per_addresses.country%TYPE;
2797: l_last_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
2798: l_date_earned DATE;
2799: l_eff_date DATE;
2800: l_last_pay_action_id pay_assignment_actions.payroll_action_id%TYPE;
2801: --
2796: l_country per_addresses.country%TYPE;
2797: l_last_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
2798: l_date_earned DATE;
2799: l_eff_date DATE;
2800: l_last_pay_action_id pay_assignment_actions.payroll_action_id%TYPE;
2801: --
2802: l_student_loan_flag VARCHAR2(1);
2803: --
2804: l_period_no per_time_periods.period_num%TYPE;
2832: (act.assignment_action_id,
2833: g_termination_date_eid)) term_date,
2834: pay_gb_eoy_archive.get_arch_str(act.assignment_action_id,
2835: g_tax_ref_transfer_eid) tax_ref_transfer
2836: FROM pay_assignment_actions act
2837: WHERE act.assignment_action_id = p_asgactid;
2838: --
2839: cursor csr_basic_asg_info (p_assid NUMBER, p_term_date DATE) IS
2840: SELECT ass.assignment_number,
2910: WHERE run_result_id = l_max_run_result_id
2911: AND input_value_id = p_ipv_id;
2912: --
2913: CURSOR csr_max_run_result IS
2914: SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
2915: pact PAY_PAYROLL_ACTIONS_PK,
2916: r2 PAY_RUN_RESULTS_N50)
2917: USE_NL(assact2, pact, r2) */
2918: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
2916: r2 PAY_RUN_RESULTS_N50)
2917: USE_NL(assact2, pact, r2) */
2918: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
2919: r2.run_result_id),17))
2920: FROM pay_assignment_actions assact2,
2921: pay_payroll_actions pact,
2922: pay_run_results r2
2923: WHERE assact2.assignment_id = l_assignment_id
2924: AND r2.element_type_id+0 = l_paye_element_id
2932: and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY') + 1)),'DD-MM-YYYY')
2933: AND NOT EXISTS(
2934: SELECT '1'
2935: FROM pay_action_interlocks pai,
2936: pay_assignment_actions assact3,
2937: pay_payroll_actions pact3
2938: WHERE pai.locked_action_id = assact2.assignment_action_id
2939: AND pai.locking_action_id = assact3.assignment_action_id
2940: AND pact3.payroll_action_id = assact3.payroll_action_id
3387: --
3388: cursor get_errored_actions(c_payroll_action_id number) is
3389: select 1 from dual where exists
3390: (select action_status
3391: from pay_assignment_actions
3392: where payroll_action_id = c_payroll_action_id
3393: and action_status = 'E');
3394: --
3395: rec_print_options csr_get_print_options%ROWTYPE;
3491: endperson IN NUMBER,
3492: chunk IN NUMBER)
3493: IS
3494: l_proc CONSTANT VARCHAR2(35):= g_package||'edi_act_creation';
3495: l_actid pay_assignment_actions.assignment_action_id%TYPE;
3496: --
3497: cursor csr_user_entity(p_entity_name VARCHAR2) IS
3498: SELECT user_entity_id
3499: FROM ff_user_entities
3529: DISTINCT
3530: act.assignment_action_id archive_action,
3531: act.assignment_id
3532: FROM pay_payroll_actions pact,
3533: pay_assignment_actions act,
3534: per_assignments_f paf,
3535: pay_action_interlocks pai,
3536: ff_archive_items fai
3537: WHERE pact.report_type ='P45'
3585: --
3586: -- Performance fix
3587: SELECT /*+ ORDERED
3588: USE_NL(act,pai)
3589: INDEX(act PAY_ASSIGNMENT_ACTIONS_N50) */
3590: DISTINCT
3591: act.assignment_action_id archive_action,
3592: act.assignment_id
3593: from pay_payroll_actions pact,
3590: DISTINCT
3591: act.assignment_action_id archive_action,
3592: act.assignment_id
3593: from pay_payroll_actions pact,
3594: pay_assignment_actions act,
3595: per_assignments_f paf,
3596: pay_action_interlocks pai,
3597: pay_population_ranges ppr,
3598: ff_archive_items fai
3673: hr_utility.set_location(l_proc,20);
3674: FOR rec_asg IN csr_range_assignments LOOP
3675: --
3676: hr_utility.set_location(l_proc,25);
3677: SELECT pay_assignment_actions_s.nextval
3678: INTO l_actid
3679: FROM dual;
3680: --
3681: hr_utility.set_location(l_proc,27);
3692: --
3693: hr_utility.set_location(l_proc,30);
3694: FOR rec_asg IN csr_assignments LOOP
3695: --
3696: SELECT pay_assignment_actions_s.nextval
3697: INTO l_actid
3698: FROM dual;
3699: --
3700: hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
3915: ) IS
3916: --
3917: CURSOR csr_get_p45_action(c_assignment_id NUMBER) IS
3918: SELECT act.assignment_action_id, pact.effective_date, act.action_sequence
3919: FROM pay_assignment_actions act, pay_payroll_actions pact
3920: WHERE act.assignment_id = c_assignment_id
3921: AND act.payroll_action_id = pact.payroll_action_id
3922: AND pact.REPORT_QUALIFIER = 'GB'
3923: AND pact.ACTION_TYPE = 'X'
4016: and ai.context1 = c_asg_action_id
4017: and pact1.payroll_action_id =
4018: (
4019: select to_number(substr(max(lpad(to_char(act.action_sequence), 20, '0')||to_char(pact.payroll_action_id)),21)) -- just to be consistent with rest of the code to get highest action based on the action sequence
4020: from pay_assignment_actions act,
4021: pay_payroll_actions pact,
4022: per_time_periods ptp -- moved to subquery to make sure latest payroll action having period num matching the archive is fetched
4023: where pact.payroll_action_id = act.payroll_action_id
4024: and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
4022: per_time_periods ptp -- moved to subquery to make sure latest payroll action having period num matching the archive is fetched
4023: where pact.payroll_action_id = act.payroll_action_id
4024: and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
4025: and act.assignment_id = c_assignment_id
4026: and pact.action_sequence < c_action_sequence -- assuming you will write another sql to get p_p45_action_sequence, alternatively another join to pay_assignment_actions can get you this value in this sql
4027: -- and act.SOURCE_ACTION_ID is null -- no need to check for source_action_id being null to cover upgrade from R11 cases
4028: and ai.value = to_char(ptp.period_num)
4029: and pact.time_period_id = ptp.time_period_id
4030: );