[Home] [Help]
295: 16-11-2009 jvaradra 115.106 Bug:9071978 End of time needs to be considered when FPD is NULL
296: 03-11-2009 rlingama 115.107 Bug 9170440 Changed l_printer_style variable declaration.
297: 08-02-2010 rlingama 115.108 Bug 9347169 Modified the code to ensure, fecth address based on the assignment
298: end date if address not exists on sysdate.
299: 23-01-2010 dwkrishn 115.109 update the payroll_id in the pay_payroll_actions table.
300: 04-03-2010 rlingama 115.110 Bug:8370481 Modifed the Total pay/tax to date and Pay/Tax in this Employment exceeds
301: 999999.99 to 999999999.99.
302: 25-03-2010 dwkrishn 115.111 Bug 9292092 Modified agg_latest_action. Introduced an inline view instead of a
303: corelated subquery
623:
624: -- Bug 6900025 added l_eff_date parameter to check proper financial year for fecting PAYE details.
625: CURSOR csr_max_run_result(l_element_id number,l_eff_date date) IS
626: SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
627: pact PAY_PAYROLL_ACTIONS_PK,
628: r2 PAY_RUN_RESULTS_N50)
629: USE_NL(assact2, pact, r2) */
630: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
631: r2.run_result_id),17))
629: USE_NL(assact2, pact, r2) */
630: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
631: r2.run_result_id),17))
632: FROM pay_assignment_actions assact2,
633: pay_payroll_actions pact,
634: pay_run_results r2
635: WHERE assact2.assignment_id = p_assignment_id
636: AND r2.element_type_id+0 = l_element_id
637: AND r2.assignment_action_id = assact2.assignment_action_id
648: AND NOT EXISTS(
649: SELECT '1'
650: FROM pay_action_interlocks pai,
651: pay_assignment_actions assact3,
652: pay_payroll_actions pact3
653: WHERE pai.locked_action_id = assact2.assignment_action_id
654: AND pai.locking_action_id = assact3.assignment_action_id
655: AND pact3.payroll_action_id = assact3.payroll_action_id
656: AND pact3.action_type = 'V'
776: act.payroll_action_id,
777: pact.effective_date ,
778: pact.payroll_id
779: from pay_assignment_actions act,
780: pay_payroll_actions pact
781: where act.assignment_id = X_ASSIGNMENT_ID
782: and act.action_status = 'C'
783: and pact.payroll_action_id = act.payroll_action_id
784: and act.action_sequence = (
785: select /*+ ORDERED use_nl(a,pact2,t,r,v,f)
786: user_index(v, PAY_RUN_RESULT_VALUES_PK) */
787: max(a.action_sequence)
788: from pay_assignment_actions a
789: ,pay_payroll_actions pact2
790: ,pay_balance_types t
791: ,pay_balance_feeds_f f
792: ,pay_run_results r
793: ,pay_run_result_values v
830: lpad(paa.action_sequence,15,'0')||
831: paa.assignment_action_id),16)) assignment_action_id
832: from per_all_assignments_f a,
833: pay_assignment_actions paa,
834: pay_payroll_actions ppa,
835: pay_all_payrolls_f pay,
836: hr_soft_coding_keyflex flex
837: ,(SELECT a2.assignment_id, max(a2.effective_start_date) max_effective_start_date -- Bug 9292092
838: FROM per_all_assignments_f a2,
880: SELECT /*+ USE_NL(paa, ppa) */
881: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
882: paa.assignment_action_id),16))
883: FROM pay_assignment_actions paa,
884: pay_payroll_actions ppa
885: WHERE
886: paa.assignment_id = c_assignment_id
887: AND ppa.payroll_action_id = paa.payroll_action_id
888: AND (paa.source_action_id is not null
897: SELECT /*+ USE_NL(paa, ppa) */
898: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
899: paa.assignment_action_id),16))
900: FROM pay_assignment_actions paa,
901: pay_payroll_actions ppa
902: WHERE
903: paa.assignment_id = c_assignment_id
904: AND ppa.payroll_action_id = paa.payroll_action_id
905: AND ppa.effective_date <= c_effective_date
926: --
927: cursor payroll_details(c_assignment_action_id in number) is
928: select paa.payroll_action_id,
929: ppa.effective_date, ppa.payroll_id
930: from pay_payroll_actions ppa,
931: pay_assignment_actions paa
932: where paa.assignment_action_id = c_assignment_action_id
933: and ppa.payroll_action_id = paa.payroll_action_id;
934: --
951: SELECT to_date('06-04-'||
952: to_char(fnd_number.canonical_to_number(to_char(ptp.regular_payment_date,'YYYY'))
953: + least(sign(ptp.regular_payment_date - to_date('06-04-'
954: || to_char(ptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
955: FROM per_time_periods ptp, pay_assignment_actions act, pay_payroll_actions pact
956: WHERE act.assignment_action_id = p_asg_action_id
957: AND act.payroll_Action_id = pact.payroll_action_id
958: AND pact.time_period_id = ptp.time_period_id;
959: --
1508: substr(org.org_information3,1,36) employers_name,
1509: substr(org.org_information4,1,60) employers_address_line,
1510: substr(org.org_information2 ,1,40) tax_district_name
1511: FROM
1512: pay_payroll_actions ppa,
1513: hr_organization_information org
1514: WHERE ppa.payroll_action_id = p_payroll_action_id
1515: AND org.org_information_context = 'Tax Details References'
1516: AND NVL(org.org_information10,'UK') = 'UK'
1530: substr(pay_core_utils.get_parameter('TEST',legislative_parameters),1,1) test_indicator,
1531: trim(substr(pay_core_utils.get_parameter('TEST_ID',legislative_parameters),1,8)) test_id,
1532: report_type,
1533: report_category
1534: from pay_payroll_actions ppa
1535: where ppa.payroll_action_id = pactid;
1536: --
1537: -- Start of BUG 5671777-11
1538: --
1543: start_date,
1544: fnd_date.canonical_to_date
1545: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1546: 'DATE_TO')) end_date
1547: FROM pay_payroll_actions
1548: WHERE payroll_action_id = p_payroll_action_id;
1549:
1550: --
1551: -- fetch start date and end date for the P45 EDI process
1558: 'DATE_FROM')) start_date,
1559: fnd_date.canonical_to_date
1560: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1561: 'END_DATE')) end_date
1562: FROM pay_payroll_actions
1563: WHERE payroll_action_id = p_payroll_action_id;
1564: -- End of BUG 5671777-11
1565: --
1566: rec_employer_details csr_employer_details%ROWTYPE;
1693: IF l_payroll_id is not null then
1694: -- Payroll ID has been used in param, restrict by this.
1695: hr_utility.set_location(l_proc,20);
1696: sqlstr := 'select distinct paaf.person_id '||
1697: 'from pay_payroll_actions ppa, '||
1698: 'per_all_assignments_f paaf '||
1699: 'where ppa.payroll_action_id = :payroll_action_id '||
1700: 'and paaf.business_group_id + 0 = ppa.business_group_id '||
1701: 'and paaf.payroll_id = '||to_char(l_payroll_id)||
1705: -- Normal range not restricting by payroll_id.
1706: hr_utility.set_location(l_proc,30);
1707: sqlstr := 'select distinct person_id '||
1708: 'from per_people_f ppf, '||
1709: 'pay_payroll_actions ppa '||
1710: 'where ppa.payroll_action_id = :payroll_action_id '||
1711: 'and ppa.business_group_id = ppf.business_group_id '||
1712: 'order by ppf.person_id';
1713: END IF;
1815: max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
1816: ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
1817: from ff_archive_items fai, -- of P45 report type
1818: pay_assignment_actions act,
1819: pay_payroll_actions ppa
1820: where ppa.report_type='P45'
1821: and ppa.report_qualifier='GB'
1822: and ppa.report_category ='P45'
1823: and ppa.action_type = 'X'
1832: ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
1833: from ff_archive_items fai
1834: where fai.user_entity_id in (g_tax_ref_transfer_eid,g_period_of_service_eid)
1835: and fai.context1 = (select max(act.assignment_action_id)
1836: from pay_payroll_actions ppa,
1837: pay_assignment_actions act
1838: where ppa.report_type='P45'
1839: and ppa.report_qualifier='GB'
1840: and ppa.report_category ='P45'
1990: cursor csr_payment (c_assignment_id in number,
1991: c_effective_end_date in date) is
1992: select 1 from dual where exists
1993: (select pa.effective_date
1994: from pay_payroll_actions pa,
1995: pay_assignment_actions aa
1996: where aa.assignment_id = c_assignment_id
1997: and aa.payroll_action_id = pa.payroll_action_id
1998: and pa.action_type in ('R','Q','V','I','B')
2008: c_assignment_id in number) is
2009: select 1 from dual where exists
2010: (select paa.assignment_action_id
2011: from pay_assignment_actions paa,
2012: pay_payroll_actions ppa,
2013: per_time_periods ptp
2014: where ptp.time_period_id = ppa.time_period_id
2015: and ppa.payroll_action_id = paa.payroll_action_id
2016: and paa.assignment_id = c_assignment_id
2112: -- c_effective_end_date in date) is --Bug 9036584
2113: select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0') ||
2114: paa.assignment_action_id),16))
2115: FROM pay_assignment_actions paa,
2116: pay_payroll_actions ppa
2117: WHERE
2118: paa.assignment_id = c_assignment_id
2119: AND ppa.payroll_action_id = paa.payroll_action_id
2120: AND (paa.source_action_id is not null
2215: fnd_date.canonical_to_date
2216: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
2217: 'DATE_TO')) end_date,
2218: business_group_id
2219: FROM pay_payroll_actions
2220: WHERE payroll_action_id = p_payroll_action_id;
2221: --
2222: cursor csr_user_entity(p_entity_name VARCHAR2) IS
2223: SELECT user_entity_id
2927: l_do_edi_validation VARCHAR2(6);
2928: l_effective_date DATE;
2929: l_edi_ver g_edi_ver%type;
2930: -- Bug 8815269
2931: l_ppa_payroll_id pay_payroll_actions.payroll_id%TYPE;
2932: l_payroll_id pay_payroll_actions.payroll_id%TYPE;
2933: -- Bug 8815269
2934:
2935: --
2928: l_effective_date DATE;
2929: l_edi_ver g_edi_ver%type;
2930: -- Bug 8815269
2931: l_ppa_payroll_id pay_payroll_actions.payroll_id%TYPE;
2932: l_payroll_id pay_payroll_actions.payroll_id%TYPE;
2933: -- Bug 8815269
2934:
2935: --
2936: cursor csr_user_entity(p_entity_name VARCHAR2) IS
2947: decode(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2948: 'CHAR_ERROR'),
2949: 'Y','TRUE','N','FALSE') check_chars,
2950: effective_date
2951: FROM pay_payroll_actions
2952: WHERE payroll_action_id = p_payroll_action_id;
2953: --
2954: cursor csr_version_info(p_payroll_action_id NUMBER) IS
2955: SELECT pay_gb_eoy_archive.get_parameter(legislative_parameters,'EDI_VER') edi_ver
2952: WHERE payroll_action_id = p_payroll_action_id;
2953: --
2954: cursor csr_version_info(p_payroll_action_id NUMBER) IS
2955: SELECT pay_gb_eoy_archive.get_parameter(legislative_parameters,'EDI_VER') edi_ver
2956: FROM pay_payroll_actions
2957: WHERE payroll_action_id = p_payroll_action_id;
2958:
2959: BEGIN
2960: hr_utility.set_location('Entering: '||l_proc,1);
3090: END IF; -- NOT g_asg_process_cache_populated
3091:
3092:
3093: --------------------------------------------------------------------------
3094: -- Code to update the payroll_id in the pay_payroll_actions table. -- Bug 8815269
3095: --------------------------------------------------------------------------
3096:
3097: SELECT payroll_id,pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID')
3098: INTO l_ppa_payroll_id,l_payroll_id
3095: --------------------------------------------------------------------------
3096:
3097: SELECT payroll_id,pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID')
3098: INTO l_ppa_payroll_id,l_payroll_id
3099: FROM pay_payroll_actions
3100: WHERE payroll_action_id = p_payroll_action_id ;
3101:
3102:
3103: -- Update the Payroll Action with the Payroll ID
3102:
3103: -- Update the Payroll Action with the Payroll ID
3104:
3105: IF l_ppa_payroll_id IS NULL and l_payroll_id is not null THEN
3106: UPDATE pay_payroll_actions
3107: SET payroll_id = l_payroll_id
3108: WHERE payroll_action_id = p_payroll_action_id;
3109: END IF;
3110:
3235: SELECT decode(target.basic_period_type, 'W', 'W', 'M')
3236: FROM per_time_period_rules target ,
3237: per_time_period_types ptpt ,
3238: pay_payrolls_f roll,
3239: pay_payroll_actions pact
3240: WHERE ptpt.period_type = roll.period_type
3241: AND target.number_per_fiscal_year = ptpt.number_per_fiscal_year
3242: AND roll.payroll_id = pact.payroll_id
3243: AND pact.effective_date BETWEEN roll.effective_start_date
3272: --
3273: cursor csr_period_number(p_payroll_action_id NUMBER) IS
3274: SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
3275: FROM per_time_periods ptp,
3276: pay_payroll_actions pact
3277: WHERE pact.payroll_action_id = p_payroll_action_id
3278: AND ptp.payroll_id = pact.payroll_id
3279: AND pact.date_earned BETWEEN ptp.start_date AND ptp.end_date;
3280: --
3296: AND input_value_id = p_ipv_id;
3297: --
3298: CURSOR csr_max_run_result IS
3299: SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
3300: pact PAY_PAYROLL_ACTIONS_PK,
3301: r2 PAY_RUN_RESULTS_N50)
3302: USE_NL(assact2, pact, r2) */
3303: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
3304: r2.run_result_id),17))
3302: USE_NL(assact2, pact, r2) */
3303: to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
3304: r2.run_result_id),17))
3305: FROM pay_assignment_actions assact2,
3306: pay_payroll_actions pact,
3307: pay_run_results r2
3308: WHERE assact2.assignment_id = l_assignment_id
3309: AND r2.element_type_id+0 = l_paye_element_id
3310: AND r2.assignment_action_id = assact2.assignment_action_id
3318: AND NOT EXISTS(
3319: SELECT '1'
3320: FROM pay_action_interlocks pai,
3321: pay_assignment_actions assact3,
3322: pay_payroll_actions pact3
3323: WHERE pai.locked_action_id = assact2.assignment_action_id
3324: AND pai.locking_action_id = assact3.assignment_action_id
3325: AND pact3.payroll_action_id = assact3.payroll_action_id
3326: AND pact3.action_type = 'V'
4049: l_count NUMBER := 0;
4050: l_dummy NUMBER;
4051: l_print_style VARCHAR2(2);
4052: l_report_short_name VARCHAR2(20);
4053: l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
4054: l_number_of_copies fnd_concurrent_requests.number_of_copies%TYPE;
4055: l_request_id NUMBER:=-1;
4056: l_formula_id ff_formulas_f.formula_id%TYPE;
4057: --
4076: cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
4077: SELECT printer,
4078: print_style,
4079: decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
4080: FROM pay_payroll_actions pact,
4081: fnd_concurrent_requests fcr
4082: WHERE fcr.request_id = pact.request_id
4083: AND pact.payroll_action_id = p_payroll_action_id;
4084: --
4096: pay_gb_eoy_archive.get_parameter(legislative_parameters,
4097: 'P45') stationary_type,
4098: pay_gb_eoy_archive.get_parameter(legislative_parameters,
4099: 'PDEF') defer_print
4100: from pay_payroll_actions
4101: where payroll_action_id = c_payroll_action_id;
4102: /*P45 A4 2008-09 changes */
4103: --
4104: rec_print_options csr_get_print_options%ROWTYPE;
4293: (pay_gb_eoy_archive.get_parameter(legislative_parameters,
4294: 'DATE_FROM')) start_date,
4295: effective_date end_date,
4296: business_group_id
4297: FROM pay_payroll_actions
4298: WHERE payroll_action_id = p_payroll_action_id;
4299: --
4300: cursor csr_assignments IS
4301: -- Do not restrict to the last P45 archive action for each employee - get
4310: SELECT /*+ ORDERED */
4311: DISTINCT
4312: act.assignment_action_id archive_action,
4313: act.assignment_id
4314: FROM pay_payroll_actions pact,
4315: pay_assignment_actions act,
4316: per_assignments_f paf,
4317: pay_action_interlocks pai,
4318: ff_archive_items fai
4366: -- population_ranges for performance enhancement.
4367: --
4368: -- Used Not Exists instead of using Outer Join : Bug :7442831
4369: SELECT /*+ordered
4370: index(pact PAY_PAYROLL_ACTIONS_N52)
4371: index(ppr PAY_POPULATION_RANGES_N4)
4372: index(act PAY_ASSIGNMENT_ACTIONS_N51)
4373: index(fai FF_ARCHIVE_ITEMS_N50)*/
4374: DISTINCT
4373: index(fai FF_ARCHIVE_ITEMS_N50)*/
4374: DISTINCT
4375: act.assignment_action_id archive_action,
4376: act.assignment_id
4377: from pay_payroll_actions pact,
4378: pay_population_ranges ppr,
4379: per_assignments_f paf,
4380: pay_assignment_actions act,
4381: ff_archive_items fai
4721: ) IS
4722: --
4723: CURSOR csr_get_p45_action(c_assignment_id NUMBER) IS
4724: SELECT act.assignment_action_id, pact.effective_date, act.action_sequence
4725: FROM pay_assignment_actions act, pay_payroll_actions pact
4726: WHERE act.assignment_id = c_assignment_id
4727: AND act.payroll_action_id = pact.payroll_action_id
4728: AND pact.REPORT_QUALIFIER = 'GB'
4729: AND pact.ACTION_TYPE = 'X'
4813: Cursor csr_get_final_payment(c_assignment_id NUMBER, c_asg_action_id NUMBER, c_action_sequence NUMBER) IS
4814: select pact1.payroll_action_id, pact1.effective_date final_payment_date
4815: from FF_ARCHIVE_ITEMS ai,
4816: ff_user_entities ue,
4817: pay_payroll_actions pact1
4818: WHERE ue.user_entity_name in ('X_MONTH_NUMBER', 'X_WEEK_NUMBER') -- for the weekly frequency (and multiples)
4819: AND ue.legislation_code = 'GB'
4820: AND ue.business_group_id IS NULL
4821: and ue.user_entity_id = ai.user_entity_id
4824: and pact1.payroll_action_id =
4825: (
4826: 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
4827: from pay_assignment_actions act,
4828: pay_payroll_actions pact,
4829: per_time_periods ptp -- moved to subquery to make sure latest payroll action having period num matching the archive is fetched
4830: where pact.payroll_action_id = act.payroll_action_id
4831: and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
4832: and act.assignment_id = c_assignment_id
5129:
5130: -- Cursor to fetch effective date (date earned + off set)
5131: Cursor csr_get_effective_date(c_payroll_action_id number) is
5132: select effective_date
5133: from pay_payroll_actions
5134: where payroll_action_id = c_payroll_action_id;
5135:
5136: l_assignment_action_id number;
5137: l_agg_assignment_id number;
5209:
5210: -- Cursor to fetch effective date (date earned + off set)
5211: /* Cursor csr_get_effective_date(c_payroll_action_id number) is
5212: select effective_date
5213: from pay_payroll_actions
5214: where payroll_action_id = c_payroll_action_id; */
5215:
5216: l_assignment_action_id number;
5217: l_agg_assignment_id number;