[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.
622: l_start_year date; -- Bug 7410767 to store the start of financial year
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||
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))
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
647: and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
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
775: is select act.assignment_action_id,
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
829: fnd_number.canonical_to_number(substr((
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
879: c_effective_date IN DATE) is
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
896: c_effective_date IN DATE) is
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
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: --
935: cursor agg_paye(c_person_id in number,
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: --
1814: select
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'
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'
1841: and ppa.action_type = 'X'
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')
1999: and pa.date_earned >= c_effective_end_date);
2007: c_effective_end_date in date,
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
2111: cursor csr_latest_action (c_assignment_id in number) is
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
2182: );
2183:
2184: l_proc CONSTANT VARCHAR2(35):= g_package||'arch_act_creation';
2185: l_number number;
2186: l_actid pay_assignment_actions.assignment_action_id%TYPE;
2187: l_non_p45_message varchar2(50);
2188: l_transfer_flag varchar2(1);
2189: l_override_date date;
2190: l_archive boolean;
2668: -- Using regular payment date for final payment date.
2669: if NOT (tax_code_ni(rec_asg.assignment_id,rec_asg.regular_payment_date))
2670: then
2671: hr_utility.trace('ARCHIVING FOR '||to_char(rec_asg.assignment_id));
2672: SELECT pay_assignment_actions_s.nextval
2673: INTO l_actid
2674: FROM dual;
2675: --
2676: hr_utility.set_location(l_proc,110);
3149: l_town_or_city per_addresses.town_or_city%TYPE;
3150: l_county per_addresses.region_1%TYPE;
3151: l_postal_code per_addresses.postal_code%TYPE;
3152: l_country per_addresses.country%TYPE;
3153: l_last_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
3154: l_date_earned DATE;
3155: l_eff_date DATE;
3156: l_last_pay_action_id pay_assignment_actions.payroll_action_id%TYPE;
3157: --
3152: l_country per_addresses.country%TYPE;
3153: l_last_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
3154: l_date_earned DATE;
3155: l_eff_date DATE;
3156: l_last_pay_action_id pay_assignment_actions.payroll_action_id%TYPE;
3157: --
3158: l_student_loan_flag VARCHAR2(1);
3159: --
3160: l_period_no per_time_periods.period_num%TYPE;
3198: (act.assignment_action_id,
3199: g_termination_date_eid)) term_date,
3200: pay_gb_eoy_archive.get_arch_str(act.assignment_action_id,
3201: g_tax_ref_transfer_eid) tax_ref_transfer
3202: FROM pay_assignment_actions act
3203: WHERE act.assignment_action_id = p_asgactid;
3204: --
3205: cursor csr_basic_asg_info (p_assid NUMBER, p_term_date DATE) IS
3206: SELECT ass.assignment_number,
3295: WHERE run_result_id = l_max_run_result_id
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||
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))
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
3317: and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY') + 1)),'DD-MM-YYYY')
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
4084: --
4085: cursor get_errored_actions(c_payroll_action_id number) is
4086: select 1 from dual where exists
4087: (select action_status
4088: from pay_assignment_actions
4089: where payroll_action_id = c_payroll_action_id
4090: and action_status = 'E');
4091: --
4092: /*P45 A4 2008-09 changes */
4273: endperson IN NUMBER,
4274: chunk IN NUMBER)
4275: IS
4276: l_proc CONSTANT VARCHAR2(35):= g_package||'edi_act_creation';
4277: l_actid pay_assignment_actions.assignment_action_id%TYPE;
4278: --
4279: cursor csr_user_entity(p_entity_name VARCHAR2) IS
4280: SELECT user_entity_id
4281: FROM ff_user_entities
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
4319: WHERE pact.report_type ='P45'
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
4375: act.assignment_action_id archive_action,
4376: act.assignment_id
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
4382: WHERE pact.report_type ='P45'
4383: AND pact.report_qualifier ='GB'
4384: AND pact.report_category ='P45'
4455: hr_utility.set_location(l_proc,20);
4456: FOR rec_asg IN csr_range_assignments LOOP
4457: --
4458: hr_utility.set_location(l_proc,25);
4459: SELECT pay_assignment_actions_s.nextval
4460: INTO l_actid
4461: FROM dual;
4462: --
4463: hr_utility.set_location(l_proc,27);
4474: --
4475: hr_utility.set_location(l_proc,30);
4476: FOR rec_asg IN csr_assignments LOOP
4477: --
4478: SELECT pay_assignment_actions_s.nextval
4479: INTO l_actid
4480: FROM dual;
4481: --
4482: hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
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'
4823: and ai.context1 = c_asg_action_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')
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
4833: 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
4834: -- and act.SOURCE_ACTION_ID is null -- no need to check for source_action_id being null to cover upgrade from R11 cases
4835: and ai.value = to_char(ptp.period_num)
4836: and pact.time_period_id = ptp.time_period_id
4837: );