[Home] [Help]
122: *** 18-Feb-04 jkarouza 115.08 Bug#3172963 - Use of BBR to retrieve balances wherever possible.
123: *** 02-Apr-04 punmehta 115.109 Bug#3549553 - Modified Union cursor and FBT cursor for balances.
124: *** 24-Apr-04 puchil 115.110 Bug#3586388 - Changed the cursor etp_code and removed gscc warnings.
125: *** 07-May-04 avenkatk 115.111 Bug#3580487 - Modified Union Cursor,removed call to hr_aubal.calc_all_balances.
126: *** 28-MAY-04 punmehta 115.112 Bug#3642409 - Added new index INDEX(a per_assignments_f_N12) to cursor process_assignments.
127: *** 28-MAY-04 punmehta 115.112 Bug#3642409 - Removed Rownum from cursor process_assignments.
128: *** 03-JUN-04 abhkumar 115.113 Bug#3661230 - Modified the process assignment cursor to take all the employees for archiving purpose.
129: *** 21-JUN-04 srrajago 115.118 Bug#3701869 - Modified the cursor csr_get_bbr_aseq to fetch max action_sequence instead of
130: *** assignment_action_id. Introduced cursor csr_get_bbr_assignment_action to pass the
133: *** 21-JUN-04 srrajago 115.119 Bug#3701869 - Modified cursors 'csr_get_bbr_aseq' and 'csr_get_bbr_asg_actions'. Handled the parameter
134: *** Last Year Termination value setting if it is not enabled in another place which was
135: *** missed in the previous fix.
136: *** 25-JUN-04 srrajago 115.120 Bug#3603495 - Performance Fix - Modified cursors 'c_get_pay_effective_date' and 'etp_BA_or_BI'.
137: *** Introduced per_assignments_f table and its joins.
138: *** 03-JUL-04 srrajago 115.122 Bug#3743010 - Reverted back the fix in the previous version fixed for Bug: 3728357. This is same as
139: *** the version 115.20
140: *** 05-JUL-04 srrajago 115.123 Bug#3603495 - Performance Fix - Modified cursor 'c_get_pay_effective_date'.
141: *** 05-JUL-04 punmehta 115.124 Bug#3744930 - Modified for Re-hire
687: c_start_person_id in per_all_people_f.person_id%type,
688: c_end_person_id in per_all_people_f.person_id%type)
689: is
690: select /*+ INDEX(p per_people_f_pk)
691: INDEX(a per_assignments_f_fk1)
692: INDEX(a per_assignments_f_N12)
693: INDEX(pa pay_payroll_actions_pk)
694: INDEX(pps per_periods_of_service_n3)
695: */ distinct a.assignment_id
688: c_end_person_id in per_all_people_f.person_id%type)
689: is
690: select /*+ INDEX(p per_people_f_pk)
691: INDEX(a per_assignments_f_fk1)
692: INDEX(a per_assignments_f_N12)
693: INDEX(pa pay_payroll_actions_pk)
694: INDEX(pps per_periods_of_service_n3)
695: */ distinct a.assignment_id
696: from per_people_f p /*Bug3043049*/
693: INDEX(pa pay_payroll_actions_pk)
694: INDEX(pps per_periods_of_service_n3)
695: */ distinct a.assignment_id
696: from per_people_f p /*Bug3043049*/
697: ,per_assignments_f a /*Bug3043049*/
698: ,pay_payroll_actions pa
699: ,per_periods_of_service pps
700: where pa.payroll_action_id = c_payroll_action_id
701: and p.person_id between c_start_person_id and c_end_person_id
708: and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
709: and v_fin_year_end between p.effective_start_date and p.effective_end_date
710: -- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date -- Bug 3815301
711: and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
712: From per_assignments_f iipaf
713: WHERE iipaf.assignment_id = a.assignment_id
714: and iipaf.effective_end_date >= v_fbt_year_start
715: and iipaf.effective_start_date <= v_fin_year_end
716: AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
720: INDEX(rppa PAY_PAYROLL_ACTIONS_N51*/ ''
721: from
722: pay_payroll_actions rppa
723: ,pay_assignment_actions rpac /*Bug3048962 */
724: ,per_assignments_f paaf /*Bug 3815301 */
725: where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
726: or ( pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
727: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
728: and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
775: c_end_person_id in per_all_people_f.person_id%type)
776: is
777: select distinct a.assignment_id
778: from per_people_f p /*Bug3043049*/
779: ,per_assignments_f a /*Bug3043049*/
780: ,pay_payroll_actions pa
781: ,per_periods_of_service pps
782: where pa.payroll_action_id = c_payroll_action_id
783: and p.person_id between c_start_person_id and c_end_person_id
790: and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230 , Bug3048724, Bug 3263659
791: and v_fin_year_end between p.effective_start_date and p.effective_end_date
792: -- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date -- Bug 3815301
793: and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
794: From per_assignments_f iipaf
795: WHERE iipaf.assignment_id = a.assignment_id
796: and iipaf.effective_end_date >= v_fbt_year_start
797: and iipaf.effective_start_date <= v_fin_year_end
798: AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
801: (select /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
802: INDEX(rppa PAY_PAYROLL_ACTIONS_N51 */ ''
803: from pay_payroll_actions rppa
804: ,pay_assignment_actions rpac/*Bug3048962 */
805: ,per_assignments_f paaf /*Bug 3815301 */
806: where ( rppa.effective_date between v_fin_year_start and v_fin_year_end
807: or ( pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
808: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
809: and pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
865: IS
866: SELECT /*+ INDEX(pap per_people_f_pk)
867: INDEX(rppa pay_payroll_actions_pk)
868: INDEX(ppr PAY_POPULATION_RANGES_N4)
869: INDEX(paa per_assignments_f_N12)
870: INDEX(pps per_periods_of_service_PK)
871: */ a.assignment_id
872: FROM per_people_f p /*Bug3043049*/
873: ,per_assignments_f a /*Bug3043049*/
869: INDEX(paa per_assignments_f_N12)
870: INDEX(pps per_periods_of_service_PK)
871: */ a.assignment_id
872: FROM per_people_f p /*Bug3043049*/
873: ,per_assignments_f a /*Bug3043049*/
874: ,pay_payroll_actions pa
875: ,per_periods_of_service pps
876: ,pay_population_ranges ppr
877: WHERE pa.payroll_action_id = c_payroll_action_id
886: AND pps.person_id = p.person_id
887: AND nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
888: AND v_fin_year_end BETWEEN p.effective_start_date AND p.effective_end_date
889: AND a.effective_end_date = (SELECT MAX(effective_end_date) /* 4377367 */
890: FROM per_assignments_f iipaf
891: WHERE iipaf.assignment_id = a.assignment_id
892: AND iipaf.effective_end_date >= v_fbt_year_start
893: AND iipaf.effective_start_date <= v_fin_year_end
894: AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
898: INDEX(rppa PAY_PAYROLL_ACTIONS_N51*/ ''
899: FROM
900: pay_payroll_actions rppa
901: ,pay_assignment_actions rpac /*Bug3048962 */
902: ,per_assignments_f paaf /*Bug 3815301 */
903: WHERE ( rppa.effective_date BETWEEN v_fin_year_start AND v_fin_year_end
904: OR ( pps.actual_termination_date BETWEEN v_lst_fbt_year_start AND v_fbt_year_end /*Bug3263659 */ --Bug#3661230
905: AND rppa.effective_date BETWEEN v_fbt_year_start AND v_fbt_year_end
906: AND pay_balance_pkg.get_value(g_fbt_defined_balance_id,rpac.assignment_action_id
1403: AND pbt.balance_type_id = pdb.balance_type_id
1404: AND pbd.balance_dimension_id = pdb.balance_dimension_id
1405: AND pbd.dimension_name = '_ASG_LE_PTD';
1406:
1407: /* Bug No: 3603495 - Performance Fix in c_get_pay_effective_date - Introduced per_assignments_f and its joins */
1408: /* Bug 4363057 - Cursor has been modified so that the Lump Sum E Payments given to previous legal employers
1409: can be taken into account while calculating payment summary gross.*/
1410:
1411: CURSOR c_get_pay_effective_date(c_assignment_id pay_assignment_actions.assignment_id%type
1413: ,c_year_end in DATE)
1414: IS
1415: select /*+ USE_NL(ptp) */ -- Bug 4925650
1416: max(paa.assignment_action_id) -- Bug: 3095919, Bug 2610141
1417: from per_assignments_f paf,
1418: pay_payroll_Actions ppa,
1419: pay_assignment_Actions paa,
1420: per_time_periods ptp
1421: where ppa.payroll_Action_id = paa.payroll_Action_id
2314:
2315: -- cursor to get payroll and assignment actions when termination payments are done
2316: -- through balance adjustment/balance initialization(Bug 2574186)
2317:
2318: /* Bug No: 3603495 - Performance Fix - Modified the following cursor by introducing per_assignments_f table and its joins */
2319:
2320: cursor etp_BA_or_BI
2321: (c_assignment_id per_all_assignments_f.assignment_id%type,
2322: c_year_start date ,
2322: c_year_start date ,
2323: c_year_end date )
2324: is select max(ppa.payroll_action_id) payroll_action_id
2325: ,max(pac.assignment_action_id) assignment_action_id
2326: from per_assignments_f paf
2327: ,pay_assignment_actions pac
2328: ,pay_payroll_actions ppa
2329: where pac.assignment_id = c_assignment_id
2330: and pac.tax_unit_id = p_registered_employer --2610141
3933: FROM per_all_assignments_f paaf,
3934: pay_payrolls_f pay
3935: WHERE paaf.assignment_id = p_assignment_id
3936: and paaf.effective_end_date = (select max(effective_end_date)
3937: From per_assignments_f iipaf
3938: WHERE iipaf.assignment_id = p_assignment_id
3939: and iipaf.effective_end_date >= p_fbt_year_start
3940: and iipaf.effective_start_date <= p_year_end
3941: AND iipaf.payroll_id IS NOT NULL)
4717: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id,
4718: max(paa.action_sequence) action_sequence
4719: from pay_assignment_actions paa,
4720: pay_payroll_actions ppa,
4721: per_assignments_f paf
4722: where paa.assignment_id = paf.assignment_id
4723: and paf.assignment_id = c_assignment_id
4724: and ppa.payroll_action_id = paa.payroll_action_id
4725: and ppa.effective_date between c_year_start and c_year_end
4739: and it will be passed as an argument to archive_employee_details procedure for archiving
4740: employee details. This cursor should also execute only when the employee is not active in the current financial year for the submitted legal employer.
4741: */
4742: cursor csr_check_eff_le
4743: (c_assignment_id per_assignments_f.assignment_id%type
4744: ,c_legal_employer hr_organization_units.organization_id%type
4745: ,c_year_start pay_payroll_actions.effective_date%type
4746: ,c_year_end pay_payroll_actions.effective_date%type
4747: ) is
4745: ,c_year_start pay_payroll_actions.effective_date%type
4746: ,c_year_end pay_payroll_actions.effective_date%type
4747: ) is
4748: select paaf.assignment_id
4749: from per_assignments_f paaf,
4750: hr_soft_coding_keyflex hsck
4751: where paaf.assignment_id = c_assignment_id
4752: and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
4753: and paaf.effective_start_date <= c_year_end
4756: --
4757:
4758: /* Bug 5371102 - Added joins on Payroll ID for better performance */
4759: cursor csr_get_dates
4760: (c_assignment_id per_assignments_f.assignment_id%type
4761: ,c_year_start pay_payroll_actions.effective_date%type
4762: ,c_year_end pay_payroll_actions.effective_date%type
4763: ,c_legal_employer hr_organization_units.organization_id%type
4764: ) is
4764: ) is
4765: select to_char(min(ppa.effective_date),'DDMMYYYY'), to_char(max(ppa.effective_date),'DDMMYYYY')
4766: from pay_assignment_actions paa,
4767: pay_payroll_actions ppa,
4768: per_assignments_f paaf
4769: where paa.assignment_id = paaf.assignment_id
4770: and paaf.assignment_id = c_assignment_id
4771: and paa.payroll_action_id = ppa.payroll_action_id
4772: and ppa.effective_date between c_year_start and c_year_end
4779: -- 4387183 - This cursor has been modified. It will now pick the maximum effective record for the
4780: -- assignment between FBT year start and financial year end.
4781: --
4782: cursor csr_get_end_le
4783: (c_assignment_id per_assignments_f.assignment_id%type
4784: ,c_year_end pay_payroll_actions.effective_date%type
4785: ,c_year_start pay_payroll_actions.effective_date%type
4786: ) is
4787: select hsck.segment1
4784: ,c_year_end pay_payroll_actions.effective_date%type
4785: ,c_year_start pay_payroll_actions.effective_date%type
4786: ) is
4787: select hsck.segment1
4788: from per_assignments_f paaf,
4789: hr_soft_coding_keyflex hsck
4790: where paaf.assignment_id = c_assignment_id
4791: and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
4792: and paaf.effective_start_date <= c_year_end