[Home] [Help]
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||
574: r2.run_result_id),17))
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
580: AND r2.assignment_action_id = assact2.assignment_action_id
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
599: AND pact3.action_type = 'V'
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
732: ,pay_run_result_values v
759: c_tax_reference in varchar2,
760: c_agg_active_start in date,
761: c_agg_active_end in date) is
762: select /*+ ORDERED use_nl(flex pay ppa paa a)
763: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
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,
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
773: and paa.assignment_id = a.assignment_id
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
810: AND (paa.source_action_id is not null
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
827: AND ppa.effective_date <= c_effective_date
848: --
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: --
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: --
1391: substr(org.org_information3,1,36) employers_name,
1392: substr(org.org_information4,1,60) employers_address_line,
1393: substr(org.org_information2 ,1,40) tax_district_name
1394: FROM
1395: pay_payroll_actions ppa,
1396: hr_organization_information org
1397: WHERE ppa.payroll_action_id = p_payroll_action_id
1398: AND org.org_information_context = 'Tax Details References'
1399: AND NVL(org.org_information10,'UK') = 'UK'
1413: substr(pay_core_utils.get_parameter('TEST',legislative_parameters),1,1) test_indicator,
1414: trim(substr(pay_core_utils.get_parameter('TEST_ID',legislative_parameters),1,8)) test_id,
1415: report_type,
1416: report_category
1417: from pay_payroll_actions ppa
1418: where ppa.payroll_action_id = pactid;
1419: --
1420: -- Start of BUG 5671777-11
1421: --
1426: start_date,
1427: fnd_date.canonical_to_date
1428: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1429: 'DATE_TO')) end_date
1430: FROM pay_payroll_actions
1431: WHERE payroll_action_id = p_payroll_action_id;
1432:
1433: --
1434: -- fetch start date and end date for the P45 EDI process
1441: 'DATE_FROM')) start_date,
1442: fnd_date.canonical_to_date
1443: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1444: 'END_DATE')) end_date
1445: FROM pay_payroll_actions
1446: WHERE payroll_action_id = p_payroll_action_id;
1447: -- End of BUG 5671777-11
1448: --
1449: rec_employer_details csr_employer_details%ROWTYPE;
1576: IF l_payroll_id is not null then
1577: -- Payroll ID has been used in param, restrict by this.
1578: hr_utility.set_location(l_proc,20);
1579: sqlstr := 'select distinct paaf.person_id '||
1580: 'from pay_payroll_actions ppa, '||
1581: 'per_all_assignments_f paaf '||
1582: 'where ppa.payroll_action_id = :payroll_action_id '||
1583: 'and paaf.business_group_id + 0 = ppa.business_group_id '||
1584: 'and paaf.payroll_id = '||to_char(l_payroll_id)||
1588: -- Normal range not restricting by payroll_id.
1589: hr_utility.set_location(l_proc,30);
1590: sqlstr := 'select distinct person_id '||
1591: 'from per_people_f ppf, '||
1592: 'pay_payroll_actions ppa '||
1593: 'where ppa.payroll_action_id = :payroll_action_id '||
1594: 'and ppa.business_group_id = ppf.business_group_id '||
1595: 'order by ppf.person_id';
1596: END IF;
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'
1706: and ppa.action_type = 'X'
1715: ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
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'
1868: cursor csr_payment (c_assignment_id in number,
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')
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
1894: and paa.assignment_id = c_assignment_id
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
1998: AND (paa.source_action_id is not null
2088: fnd_date.canonical_to_date
2089: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
2090: 'DATE_TO')) end_date,
2091: business_group_id
2092: FROM pay_payroll_actions
2093: WHERE payroll_action_id = p_payroll_action_id;
2094: --
2095: cursor csr_user_entity(p_entity_name VARCHAR2) IS
2096: SELECT user_entity_id
2622: decode(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2623: 'CHAR_ERROR'),
2624: 'Y','TRUE','N','FALSE') check_chars,
2625: effective_date
2626: FROM pay_payroll_actions
2627: WHERE payroll_action_id = p_payroll_action_id;
2628: --
2629: BEGIN
2630: hr_utility.set_location('Entering: '||l_proc,1);
2869: SELECT decode(target.basic_period_type, 'W', 'W', 'M')
2870: FROM per_time_period_rules target ,
2871: per_time_period_types ptpt ,
2872: pay_payrolls_f roll,
2873: pay_payroll_actions pact
2874: WHERE ptpt.period_type = roll.period_type
2875: AND target.number_per_fiscal_year = ptpt.number_per_fiscal_year
2876: AND roll.payroll_id = pact.payroll_id
2877: AND pact.effective_date BETWEEN roll.effective_start_date
2887: --
2888: cursor csr_period_number(p_payroll_action_id NUMBER) IS
2889: SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
2890: FROM per_time_periods ptp,
2891: pay_payroll_actions pact
2892: WHERE pact.payroll_action_id = p_payroll_action_id
2893: AND ptp.payroll_id = pact.payroll_id
2894: AND pact.date_earned BETWEEN ptp.start_date AND ptp.end_date;
2895: --
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||
2919: r2.run_result_id),17))
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
2925: AND r2.assignment_action_id = assact2.assignment_action_id
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
2941: AND pact3.action_type = 'V'
3357: l_count NUMBER := 0;
3358: l_dummy NUMBER;
3359: l_print_style VARCHAR2(2);
3360: l_report_short_name VARCHAR2(20);
3361: l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
3362: l_number_of_copies fnd_concurrent_requests.number_of_copies%TYPE;
3363: l_request_id NUMBER:=-1;
3364: l_formula_id ff_formulas_f.formula_id%TYPE;
3365: --
3379: cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3380: SELECT printer,
3381: print_style,
3382: decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3383: FROM pay_payroll_actions pact,
3384: fnd_concurrent_requests fcr
3385: WHERE fcr.request_id = pact.request_id
3386: AND pact.payroll_action_id = p_payroll_action_id;
3387: --
3511: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
3512: 'DATE_FROM')) start_date,
3513: effective_date end_date,
3514: business_group_id
3515: FROM pay_payroll_actions
3516: WHERE payroll_action_id = p_payroll_action_id;
3517: --
3518: cursor csr_assignments IS
3519: -- Do not restrict to the last P45 archive action for each employee - get
3528: SELECT /*+ ORDERED */
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
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,
3594: pay_assignment_actions act,
3595: per_assignments_f paf,
3596: pay_action_interlocks pai,
3597: pay_population_ranges ppr,
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'
4006: Cursor csr_get_final_payment(c_assignment_id NUMBER, c_asg_action_id NUMBER, c_action_sequence NUMBER) IS
4007: select pact1.payroll_action_id, pact1.effective_date final_payment_date
4008: from FF_ARCHIVE_ITEMS ai,
4009: ff_user_entities ue,
4010: pay_payroll_actions pact1
4011: WHERE ue.user_entity_name in ('X_MONTH_NUMBER', 'X_WEEK_NUMBER') -- for the weekly frequency (and multiples)
4012: AND ue.legislation_code = 'GB'
4013: AND ue.business_group_id IS NULL
4014: and ue.user_entity_id = ai.user_entity_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')
4025: and act.assignment_id = c_assignment_id
4322:
4323: -- Cursor to fetch effective date (date earned + off set)
4324: Cursor csr_get_effective_date(c_payroll_action_id number) is
4325: select effective_date
4326: from pay_payroll_actions
4327: where payroll_action_id = c_payroll_action_id;
4328:
4329: l_assignment_action_id number;
4330: l_agg_assignment_id number;