[Home] [Help]
618: type assact_details is record
619: (assact_id pay_assignment_actions.assignment_action_id%type,
620: assignment_id pay_assignment_actions.assignment_id%type,
621: full_name per_all_people_f.full_name%type,
622: assignment_number per_all_assignments_f.assignment_number%type,
623: payroll_id per_all_assignments_f.payroll_id%type);
624: --
625: type pact_details is record
626: (pact_id pay_payroll_actions.payroll_action_id%type,
619: (assact_id pay_assignment_actions.assignment_action_id%type,
620: assignment_id pay_assignment_actions.assignment_id%type,
621: full_name per_all_people_f.full_name%type,
622: assignment_number per_all_assignments_f.assignment_number%type,
623: payroll_id per_all_assignments_f.payroll_id%type);
624: --
625: type pact_details is record
626: (pact_id pay_payroll_actions.payroll_action_id%type,
627: action_name hr_lookups.meaning%type,
887: l_person_id,
888: p_assact_rec.full_name,
889: p_assact_rec.assignment_number
890: from per_periods_of_service POS,
891: per_all_assignments_f ASS,
892: per_all_people_f PEO,
893: pay_assignment_actions ACT
894: where ACT.assignment_action_id = p_assact_rec.assact_id
895: and ASS.assignment_id = ACT.assignment_id
1036: from pay_action_classifications CLASS,
1037: pay_action_classifications CLAS2,
1038: pay_payroll_actions PACT,
1039: pay_assignment_actions ACT,
1040: per_all_assignments_f ASS,
1041: per_periods_of_service POS
1042: where POS.person_id = l_person_id
1043: and ASS.period_of_service_id = POS.period_of_service_id
1044: and ACT.assignment_id = ASS.assignment_id
1058: from pay_action_classifications CLASS,
1059: pay_action_classifications CLAS2,
1060: pay_payroll_actions PACT,
1061: pay_assignment_actions ACT,
1062: per_all_assignments_f ASS,
1063: per_periods_of_service POS
1064: where POS.person_id = l_person_id
1065: and ASS.period_of_service_id = POS.period_of_service_id
1066: and ACT.assignment_id = ASS.assignment_id
2907: --
2908: cursor albc (p_person_id number) is
2909: select /*+ ORDERED */ alb.latest_balance_id
2910: from per_periods_of_service pos,
2911: per_all_assignments_f asg,
2912: pay_assignment_actions act,
2913: pay_payroll_actions pac,
2914: pay_assignment_latest_balances alb
2915: where pos.person_id = p_person_id
2961: pay_input_values_f piv
2962: where act.assignment_id = alb.assignment_id
2963: and alb.assignment_id in
2964: (select distinct asg.assignment_id
2965: from per_all_assignments_f asg
2966: where asg.person_id = p_person_id)
2967: and alb.assignment_action_id = act.assignment_action_id
2968: and piv.element_type_id = p_eletyp_id
2969: and piv.input_value_id = pbf.input_value_id
3009: hr_utility.set_location('hrassact.del_latest_balances',10);
3010: end if;
3011: select asg.person_id
3012: into l_person_id
3013: from per_all_assignments_f asg
3014: where asg.assignment_id = p_assignment_id
3015: and p_effective_date between
3016: asg.effective_start_date and asg.effective_end_date;
3017: --
3240: ac2.assignment_action_id
3241: from pay_action_classifications acl,
3242: pay_assignment_actions ac2,
3243: pay_assignment_actions act,
3244: per_all_assignments_f as2,
3245: per_all_assignments_f asg,
3246: pay_payroll_actions pa2,
3247: pay_payroll_actions pac
3248: where act.assignment_action_id = aaid
3241: from pay_action_classifications acl,
3242: pay_assignment_actions ac2,
3243: pay_assignment_actions act,
3244: per_all_assignments_f as2,
3245: per_all_assignments_f asg,
3246: pay_payroll_actions pa2,
3247: pay_payroll_actions pac
3248: where act.assignment_action_id = aaid
3249: and pac.payroll_action_id = act.payroll_action_id
3254: and as2.effective_end_date > pac.effective_date
3255: and ac2.assignment_id = as2.assignment_id
3256: and pa2.payroll_action_id = ac2.payroll_action_id
3257: and as2.effective_start_date = (select max(paf3.effective_start_date)
3258: from per_all_assignments_f paf3
3259: where paf3.assignment_id = ac2.assignment_id
3260: and paf3.effective_start_date <= pa2.effective_date
3261: )
3262: and acl.classification_name = 'SEQUENCED'
3544: ac2.assignment_action_id,
3545: ac2.source_action_id,
3546: ac2.object_type
3547: from pay_payroll_actions pac,
3548: per_all_assignments_f asg,
3549: per_all_assignments_f as2,
3550: pay_assignment_actions act,
3551: pay_assignment_actions ac2,
3552: pay_payroll_actions pa2,
3545: ac2.source_action_id,
3546: ac2.object_type
3547: from pay_payroll_actions pac,
3548: per_all_assignments_f asg,
3549: per_all_assignments_f as2,
3550: pay_assignment_actions act,
3551: pay_assignment_actions ac2,
3552: pay_payroll_actions pa2,
3553: pay_action_classifications acl
3563: as2.effective_end_date) >= pac.effective_date
3564: and ac2.assignment_id = as2.assignment_id
3565: and pa2.payroll_action_id = ac2.payroll_action_id
3566: and as2.effective_start_date = (select max(paf3.effective_start_date)
3567: from per_all_assignments_f paf3
3568: where paf3.assignment_id = ac2.assignment_id
3569: )
3570: and acl.classification_name = 'SEQUENCED'
3571: and pa2.action_type = acl.action_type
4162: ac2.rowid,
4163: ac2.assignment_action_id
4164: from pay_payroll_actions pac,
4165: pay_assignment_actions act,
4166: per_all_assignments_f asg,
4167: per_all_assignments_f as2,
4168: pay_assignment_actions ac2,
4169: pay_payroll_actions pa2,
4170: pay_action_classifications acl
4163: ac2.assignment_action_id
4164: from pay_payroll_actions pac,
4165: pay_assignment_actions act,
4166: per_all_assignments_f asg,
4167: per_all_assignments_f as2,
4168: pay_assignment_actions ac2,
4169: pay_payroll_actions pa2,
4170: pay_action_classifications acl
4171: where pac.payroll_action_id = pactid
4263: for update of ac2.assignment_action_id;
4264: --
4265: cursor perasg (persid number) is
4266: select distinct paf.assignment_id
4267: from per_all_assignments_f paf
4268: where paf.person_id = persid;
4269: --
4270: cursor perobj (asgid number) is
4271: select distinct pog_grp.source_id assignment_id
4312: asg.person_id
4313: into effdate, actype, lgcode, persid
4314: from
4315: per_business_groups_perf pbg,
4316: per_all_assignments_f asg,
4317: pay_payroll_actions pac
4318: where pac.payroll_action_id = pactid
4319: and pbg.business_group_id = asg.business_group_id
4320: and asg.assignment_id = asgid
4529: -- We do not attempt a correlated update, since we
4530: -- need to aquire and hold locks on all the rows.
4531: -- NOTE - the update of sequences for 'X' actions will
4532: -- work correctly because the seqasg cursor does not
4533: -- join to per_all_assignments_f.
4534: if g_debug then
4535: hr_utility.set_location('hrassact.irbaact',30);
4536: end if;
4537: if(rmode = 'Y') then
4613: asg.business_group_id,
4614: asg.period_of_service_id
4615: into bgid, posid
4616: from pay_payroll_actions pac,
4617: per_all_assignments_f asg
4618: where pac.payroll_action_id = pactid
4619: and asg.assignment_id = asgid
4620: and pac.effective_date between
4621: asg.effective_start_date and asg.effective_end_date
4690: hr_dynsql.get_tax_unit(asg.assignment_id,
4691: pac.effective_date)
4692: from
4693: per_business_groups_perf pbg,
4694: per_all_assignments_f asg,
4695: per_all_assignments_f as2,
4696: pay_payroll_actions pac
4697: where pac.payroll_action_id = pactid
4698: and asg.payroll_id = pac.payroll_id
4691: pac.effective_date)
4692: from
4693: per_business_groups_perf pbg,
4694: per_all_assignments_f asg,
4695: per_all_assignments_f as2,
4696: pay_payroll_actions pac
4697: where pac.payroll_action_id = pactid
4698: and asg.payroll_id = pac.payroll_id
4699: and asg.assignment_id = asgid
4738: 1,
4739: hr_dynsql.get_tax_unit(asg.assignment_id,
4740: pac.effective_date)
4741: from per_business_groups_perf pbg,
4742: per_all_assignments_f asg,
4743: per_all_assignments_f as2,
4744: per_periods_of_service pos,
4745: pay_payroll_actions pac
4746: where pac.payroll_action_id = pactid
4739: hr_dynsql.get_tax_unit(asg.assignment_id,
4740: pac.effective_date)
4741: from per_business_groups_perf pbg,
4742: per_all_assignments_f asg,
4743: per_all_assignments_f as2,
4744: per_periods_of_service pos,
4745: pay_payroll_actions pac
4746: where pac.payroll_action_id = pactid
4747: and asg.payroll_id = pac.payroll_id
4757: select null
4758: from pay_action_classifications acl,
4759: pay_assignment_actions ac2,
4760: pay_payroll_actions pa2,
4761: per_all_assignments_f as2
4762: where as2.period_of_service_id = pos.period_of_service_id
4763: and ac2.assignment_id = as2.assignment_id
4764: and pa2.payroll_action_id = ac2.payroll_action_id
4765: and acl.classification_name = 'SEQUENCED'
4870: pay_assignment_actions act,
4871: pay_payroll_actions pac, -- prepay action.
4872: pay_payroll_actions pa2, -- the QuickPay action.
4873: per_periods_of_service pos,
4874: per_all_assignments_f asg
4875: where pac.payroll_action_id = pactid
4876: and pa2.payroll_action_id = pac.target_payroll_action_id
4877: and act.payroll_action_id = pa2.payroll_action_id
4878: and act.source_action_id is null /* master assignment action */
4891: and pa3.payroll_action_id = ac2.payroll_action_id
4892: and pa3.action_type in ('P', 'U'))
4893: and not exists (
4894: select null
4895: from per_all_assignments_f as3,
4896: pay_assignment_actions ac3
4897: where rmode <> 'Y'
4898: and ac3.payroll_action_id = pa2.payroll_action_id
4899: and ac3.action_status not in ('C', 'S')
6467: creatby,
6468: creatdate,
6469: l_original_entry_id
6470: from pay_element_entries_f pee,
6471: per_all_assignments_f asg,
6472: per_time_periods ptp,
6473: per_business_groups_perf pbg
6474: where pee.element_entry_id = eentryid
6475: and effdate between
6863: bal_dims bal_dims_cache;
6864:
6865: c_indent constant varchar2(30) := 'hrassact.maintain_lat_bal';
6866: pactid pay_payroll_actions.payroll_action_id%TYPE;
6867: asgid per_all_assignments_f.assignment_id%TYPE;
6868: bus_grp_id per_all_assignments_f.business_group_id%TYPE;
6869: l_person_id per_all_assignments_f.person_id%TYPE;
6870: cxt_id number;
6871: tax_unit_id number;
6864:
6865: c_indent constant varchar2(30) := 'hrassact.maintain_lat_bal';
6866: pactid pay_payroll_actions.payroll_action_id%TYPE;
6867: asgid per_all_assignments_f.assignment_id%TYPE;
6868: bus_grp_id per_all_assignments_f.business_group_id%TYPE;
6869: l_person_id per_all_assignments_f.person_id%TYPE;
6870: cxt_id number;
6871: tax_unit_id number;
6872: bal_dim_name pay_balance_dimensions.dimension_name%TYPE;
6865: c_indent constant varchar2(30) := 'hrassact.maintain_lat_bal';
6866: pactid pay_payroll_actions.payroll_action_id%TYPE;
6867: asgid per_all_assignments_f.assignment_id%TYPE;
6868: bus_grp_id per_all_assignments_f.business_group_id%TYPE;
6869: l_person_id per_all_assignments_f.person_id%TYPE;
6870: cxt_id number;
6871: tax_unit_id number;
6872: bal_dim_name pay_balance_dimensions.dimension_name%TYPE;
6873: balance_expiry_code pay_balance_dimensions.expiry_checking_code%TYPE;
8149: pactid,
8150: tax_unit_id,
8151: bus_grp_id
8152: from pay_assignment_actions paa,
8153: per_all_assignments_f pera
8154: where paa.assignment_action_id = assactid
8155: and pera.assignment_id = paa.assignment_id
8156: and effdate between pera.effective_start_date
8157: and pera.effective_end_date;