[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
1172: c_start_person_id in per_all_people_f.person_id%type,
1173: c_end_person_id in per_all_people_f.person_id%type)
1174: is
1175: select /*+ INDEX(p per_people_f_pk)
1176: INDEX(a per_assignments_f_fk1)
1177: INDEX(a per_assignments_f_N12)
1178: INDEX(pa pay_payroll_actions_pk)
1179: INDEX(pps per_periods_of_service_n3)
1180: */ distinct a.assignment_id
1173: c_end_person_id in per_all_people_f.person_id%type)
1174: is
1175: select /*+ INDEX(p per_people_f_pk)
1176: INDEX(a per_assignments_f_fk1)
1177: INDEX(a per_assignments_f_N12)
1178: INDEX(pa pay_payroll_actions_pk)
1179: INDEX(pps per_periods_of_service_n3)
1180: */ distinct a.assignment_id
1181: from per_people_f p /*Bug3043049*/
1178: INDEX(pa pay_payroll_actions_pk)
1179: INDEX(pps per_periods_of_service_n3)
1180: */ distinct a.assignment_id
1181: from per_people_f p /*Bug3043049*/
1182: ,per_assignments_f a /*Bug3043049*/
1183: ,pay_payroll_actions pa
1184: ,per_periods_of_service pps
1185: where pa.payroll_action_id = c_payroll_action_id
1186: and p.person_id between c_start_person_id and c_end_person_id
1193: and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
1194: and v_fin_year_end between p.effective_start_date and p.effective_end_date
1195: -- 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
1196: and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
1197: From per_assignments_f iipaf
1198: WHERE iipaf.assignment_id = a.assignment_id
1199: and iipaf.effective_end_date >= v_fbt_year_start
1200: and iipaf.effective_start_date <= v_fin_year_end
1201: AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
1202: and a.payroll_id like v_payroll_id -- Bug 3815301
1203: AND EXISTS
1204: (select /*+ ORDERED */''
1205: from
1206: per_assignments_f paaf
1207: ,pay_assignment_actions rpac
1208: ,pay_payroll_actions rppa
1209: where rppa.effective_date between v_fin_year_start and v_fin_year_end
1210: and rppa.action_type in ('R','Q','B','I')
1217: and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
1218: UNION
1219: select /*+ ORDERED */''
1220: from
1221: per_assignments_f paaf
1222: ,pay_assignment_actions rpac
1223: ,pay_payroll_actions rppa
1224: where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
1225: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
1273: c_end_person_id in per_all_people_f.person_id%type)
1274: is
1275: select distinct a.assignment_id
1276: from per_people_f p /*Bug3043049*/
1277: ,per_assignments_f a /*Bug3043049*/
1278: ,pay_payroll_actions pa
1279: ,per_periods_of_service pps
1280: where pa.payroll_action_id = c_payroll_action_id
1281: and p.person_id between c_start_person_id and c_end_person_id
1288: and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230 , Bug3048724, Bug 3263659
1289: and v_fin_year_end between p.effective_start_date and p.effective_end_date
1290: -- 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
1291: and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
1292: From per_assignments_f iipaf
1293: WHERE iipaf.assignment_id = a.assignment_id
1294: and iipaf.effective_end_date >= v_fbt_year_start
1295: and iipaf.effective_start_date <= v_fin_year_end
1296: AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
1297: and a.payroll_id like v_payroll_id -- Bug 3815301
1298: AND EXISTS
1299: (select /*+ ORDERED */''
1300: from
1301: per_assignments_f paaf
1302: ,pay_assignment_actions rpac
1303: ,pay_payroll_actions rppa
1304: where rppa.effective_date between v_fin_year_start and v_fin_year_end
1305: and rppa.action_type in ('R','Q','B','I')
1312: and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
1313: UNION
1314: select /*+ ORDERED */''
1315: from
1316: per_assignments_f paaf
1317: ,pay_assignment_actions rpac
1318: ,pay_payroll_actions rppa
1319: where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
1320: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
1376: c_chunk IN NUMBER)
1377: IS
1378: SELECT a.assignment_id
1379: FROM per_people_f p /*Bug3043049*/
1380: ,per_assignments_f a /*Bug3043049*/
1381: ,pay_payroll_actions pa
1382: ,per_periods_of_service pps
1383: ,pay_population_ranges ppr
1384: WHERE pa.payroll_action_id = c_payroll_action_id
1395: AND pps.person_id = p.person_id
1396: AND nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
1397: AND v_fin_year_end BETWEEN p.effective_start_date AND p.effective_end_date
1398: AND a.effective_end_date = (SELECT MAX(effective_end_date) /* 4377367 */
1399: FROM per_assignments_f iipaf
1400: WHERE iipaf.assignment_id = a.assignment_id
1401: AND iipaf.person_id = a.person_id
1402: AND iipaf.effective_end_date >= v_fbt_year_start
1403: AND iipaf.effective_start_date <= v_fin_year_end
1405: AND a.payroll_id LIKE v_payroll_id -- Bug 3815301
1406: AND EXISTS
1407: (select /*+ ORDERED */''
1408: from
1409: per_assignments_f paaf
1410: ,pay_assignment_actions rpac
1411: ,pay_payroll_actions rppa
1412: where rppa.effective_date between v_fin_year_start and v_fin_year_end
1413: and rppa.action_type in ('R','Q','B','I')
1420: and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
1421: UNION
1422: select /*+ ORDERED */''
1423: from
1424: per_assignments_f paaf
1425: ,pay_assignment_actions rpac
1426: ,pay_payroll_actions rppa
1427: where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
1428: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
1512: c_chunk IN NUMBER)
1513: IS
1514: SELECT a.assignment_id
1515: FROM per_people_f p
1516: ,per_assignments_f a
1517: ,pay_payroll_actions pa
1518: ,per_periods_of_service pps
1519: ,pay_population_ranges ppr
1520: ,hr_assignment_set_amendments hasa
1534: AND pps.person_id = p.person_id
1535: AND nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start
1536: AND v_fin_year_end BETWEEN p.effective_start_date AND p.effective_end_date
1537: AND a.effective_end_date = (SELECT MAX(effective_end_date) /* 4377367 */
1538: FROM per_assignments_f iipaf
1539: WHERE iipaf.assignment_id = a.assignment_id
1540: AND iipaf.person_id = a.person_id
1541: AND iipaf.effective_end_date >= v_fbt_year_start
1542: AND iipaf.effective_start_date <= v_fin_year_end
1544: AND a.payroll_id LIKE v_payroll_id
1545: AND EXISTS
1546: (select /*+ ORDERED */''
1547: from
1548: per_assignments_f paaf
1549: ,pay_assignment_actions rpac
1550: ,pay_payroll_actions rppa
1551: where rppa.effective_date between v_fin_year_start and v_fin_year_end
1552: and rppa.action_type in ('R','Q','B','I')
1559: and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
1560: UNION
1561: select /*+ ORDERED */''
1562: from
1563: per_assignments_f paaf
1564: ,pay_assignment_actions rpac
1565: ,pay_payroll_actions rppa
1566: where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end
1567: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
1608: c_start_person_id in per_all_people_f.person_id%type,
1609: c_end_person_id in per_all_people_f.person_id%type)
1610: is
1611: select /*+ INDEX(p per_people_f_pk)
1612: INDEX(a per_assignments_f_fk1)
1613: INDEX(a per_assignments_f_N12)
1614: INDEX(pa pay_payroll_actions_pk)
1615: INDEX(pps per_periods_of_service_n3)
1616: */ distinct a.assignment_id
1609: c_end_person_id in per_all_people_f.person_id%type)
1610: is
1611: select /*+ INDEX(p per_people_f_pk)
1612: INDEX(a per_assignments_f_fk1)
1613: INDEX(a per_assignments_f_N12)
1614: INDEX(pa pay_payroll_actions_pk)
1615: INDEX(pps per_periods_of_service_n3)
1616: */ distinct a.assignment_id
1617: from per_people_f p /*Bug3043049*/
1614: INDEX(pa pay_payroll_actions_pk)
1615: INDEX(pps per_periods_of_service_n3)
1616: */ distinct a.assignment_id
1617: from per_people_f p /*Bug3043049*/
1618: ,per_assignments_f a /*Bug3043049*/
1619: ,pay_payroll_actions pa
1620: ,per_periods_of_service pps
1621: ,hr_assignment_set_amendments hasa
1622: where pa.payroll_action_id = c_payroll_action_id
1632: and nvl(pps.actual_termination_date, v_lst_year_start) >= v_lst_year_start -- Bug3661230, Bug3048724 ,Bug 3263659
1633: and v_fin_year_end between p.effective_start_date and p.effective_end_date
1634: -- 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
1635: and a.effective_end_date = (select max(effective_end_date) /* 4377367 */
1636: From per_assignments_f iipaf
1637: WHERE iipaf.assignment_id = a.assignment_id
1638: and iipaf.effective_end_date >= v_fbt_year_start
1639: and iipaf.effective_start_date <= v_fin_year_end
1640: AND iipaf.payroll_id IS NOT NULL) /*Bug# 4653934*/
1641: and a.payroll_id like v_payroll_id -- Bug 3815301
1642: AND EXISTS
1643: (select /*+ ORDERED */''
1644: from
1645: per_assignments_f paaf
1646: ,pay_assignment_actions rpac
1647: ,pay_payroll_actions rppa
1648: where rppa.effective_date between v_fin_year_start and v_fin_year_end
1649: and rppa.action_type in ('R','Q','B','I')
1656: and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date /*Bug 3815301 */
1657: UNION
1658: select /*+ ORDERED */''
1659: from
1660: per_assignments_f paaf
1661: ,pay_assignment_actions rpac
1662: ,pay_payroll_actions rppa
1663: where pps.actual_termination_date between v_lst_fbt_year_start and v_fbt_year_end /*Bug3263659 */ --Bug#3661230
1664: and rppa.effective_date between v_fbt_year_start and v_fbt_year_end
3057: ,ptp.end_date
3058: ,MAX(paa.action_sequence) action_sequence
3059: FROM pay_assignment_actions paa,
3060: pay_payroll_actions ppa,
3061: per_assignments_f paf,
3062: per_time_periods ptp
3063: WHERE paa.assignment_id = paf.assignment_id
3064: AND paf.assignment_id = c_assignment_id
3065: AND ppa.payroll_action_id = paa.payroll_action_id
3076: ORDER BY ptp.start_date;
3077:
3078:
3079: CURSOR get_asg_dates
3080: (c_assignment_id per_assignments_f.assignment_id%TYPE
3081: ,c_tax_unit_id VARCHAR2
3082: ,c_start_date DATE
3083: ,c_end_date DATE
3084: ,c_f_status_id VARCHAR2
3088: ,paf.effective_start_date
3089: ,paf.effective_end_date
3090: ,paf.payroll_id
3091: ,hsc.segment1 tax_unit_id
3092: FROM per_assignments_f paf
3093: ,hr_soft_coding_keyflex hsc
3094: WHERE paf.assignment_id = c_assignment_id
3095: AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3096: AND hsc.segment1 LIKE c_tax_unit_id
4493:
4494: -- cursor to get payroll and assignment actions when termination payments are done
4495: -- through balance adjustment/balance initialization(Bug 2574186)
4496:
4497: /* Bug No: 3603495 - Performance Fix - Modified the following cursor by introducing per_assignments_f table and its joins */
4498:
4499: cursor etp_BA_or_BI
4500: (c_assignment_id per_all_assignments_f.assignment_id%type,
4501: c_year_start date ,
4501: c_year_start date ,
4502: c_year_end date )
4503: is select max(ppa.payroll_action_id) payroll_action_id
4504: ,max(pac.assignment_action_id) assignment_action_id
4505: from per_assignments_f paf
4506: ,pay_assignment_actions pac
4507: ,pay_payroll_actions ppa
4508: where pac.assignment_id = c_assignment_id
4509: and pac.tax_unit_id = p_registered_employer --2610141
6038: cursor c_BA_BI (c_lsC_payments_def_bal_id number) is
6039: select ppa.payroll_action_id
6040: ,pac.assignment_action_id
6041: ,to_char(ppa.effective_date,'DDMMYYYY') run_effective_date
6042: from per_assignments_f paf
6043: ,pay_assignment_actions pac
6044: ,pay_payroll_actions ppa
6045: where pac.assignment_id = p_assignment_id
6046: and pac.tax_unit_id = p_registered_employer
7378: FROM per_all_assignments_f paaf,
7379: pay_payrolls_f pay
7380: WHERE paaf.assignment_id = p_assignment_id
7381: and paaf.effective_end_date = (select max(effective_end_date)
7382: From per_assignments_f iipaf
7383: WHERE iipaf.assignment_id = p_assignment_id
7384: and iipaf.effective_end_date >= p_fbt_year_start
7385: and iipaf.effective_start_date <= p_year_end
7386: AND iipaf.payroll_id IS NOT NULL)
8187: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id,
8188: max(paa.action_sequence) action_sequence
8189: from pay_assignment_actions paa,
8190: pay_payroll_actions ppa,
8191: per_assignments_f paf
8192: where paa.assignment_id = paf.assignment_id
8193: and paf.assignment_id = c_assignment_id
8194: and paa.assignment_id = c_assignment_id
8195: and ppa.payroll_action_id = paa.payroll_action_id
8210: and it will be passed as an argument to archive_employee_details procedure for archiving
8211: employee details. This cursor should also execute only when the employee is not active in the current financial year for the submitted legal employer.
8212: */
8213: cursor csr_check_eff_le
8214: (c_assignment_id per_assignments_f.assignment_id%type
8215: ,c_legal_employer hr_organization_units.organization_id%type
8216: ,c_year_start pay_payroll_actions.effective_date%type
8217: ,c_year_end pay_payroll_actions.effective_date%type
8218: ) is
8216: ,c_year_start pay_payroll_actions.effective_date%type
8217: ,c_year_end pay_payroll_actions.effective_date%type
8218: ) is
8219: select paaf.assignment_id
8220: from per_assignments_f paaf,
8221: hr_soft_coding_keyflex hsck
8222: where paaf.assignment_id = c_assignment_id
8223: and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
8224: and paaf.effective_start_date <= c_year_end
8227: --
8228:
8229: /* Bug 5371102 - Added joins on Payroll ID for better performance */
8230: cursor csr_get_dates
8231: (c_assignment_id per_assignments_f.assignment_id%type
8232: ,c_year_start pay_payroll_actions.effective_date%type
8233: ,c_year_end pay_payroll_actions.effective_date%type
8234: ,c_legal_employer hr_organization_units.organization_id%type
8235: ) is
8235: ) is
8236: select to_char(min(ppa.effective_date),'DDMMYYYY'), to_char(max(ppa.effective_date),'DDMMYYYY')
8237: from pay_assignment_actions paa,
8238: pay_payroll_actions ppa,
8239: per_assignments_f paaf
8240: where paa.assignment_id = paaf.assignment_id
8241: and paaf.assignment_id = c_assignment_id
8242: and paa.payroll_action_id = ppa.payroll_action_id
8243: and ppa.effective_date between c_year_start and c_year_end
8250: -- 4387183 - This cursor has been modified. It will now pick the maximum effective record for the
8251: -- assignment between FBT year start and financial year end.
8252: --
8253: cursor csr_get_end_le
8254: (c_assignment_id per_assignments_f.assignment_id%type
8255: ,c_year_end pay_payroll_actions.effective_date%type
8256: ,c_year_start pay_payroll_actions.effective_date%type
8257: ) is
8258: select hsck.segment1
8255: ,c_year_end pay_payroll_actions.effective_date%type
8256: ,c_year_start pay_payroll_actions.effective_date%type
8257: ) is
8258: select hsck.segment1
8259: from per_assignments_f paaf,
8260: hr_soft_coding_keyflex hsck
8261: where paaf.assignment_id = c_assignment_id
8262: and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
8263: and paaf.effective_start_date <= c_year_end
8274: ) is
8275: SELECT max(ppa.effective_date) effective_date
8276: FROM pay_assignment_actions paa,
8277: pay_payroll_actions ppa,
8278: per_assignments_f paf,
8279: pay_element_types_f pet,
8280: pay_run_results prr
8281: WHERE paa.assignment_id = paf.assignment_id
8282: AND paf.assignment_id = c_assignment_id
9508: ,p_lump_sum_E_ytd in number
9509: ,p_adj_lump_sum_E_ptd out nocopy number
9510: ,p_adj_lump_sum_pre_tax out nocopy NUMBER) return number IS
9511:
9512: /* Bug No: 3603495 - Performance Fix in c_get_pay_effective_date - Introduced per_assignments_f and its joins */
9513: /* Bug 4363057 - Cursor has been modified so that the Lump Sum E Payments given to previous legal employers
9514: can be taken into account while calculating payment summary gross.*/
9515: /*Bug 8441044 - Cursor is modified to include action types 'B' and 'I', so that when Lump Sum E payments are
9516: fed through Balance adjustment and Balance initialization processes,Lump Sum E Payments are
9520: ,c_year_end in DATE)
9521: IS
9522: select /*+ USE_NL(ptp) */ -- Bug 4925650
9523: max(paa.assignment_action_id) -- Bug: 3095919, Bug 2610141
9524: from per_assignments_f paf,
9525: pay_payroll_Actions ppa,
9526: pay_assignment_Actions paa,
9527: per_time_periods ptp
9528: where ppa.payroll_Action_id = paa.payroll_Action_id
9627: end;
9628:
9629: /* bug9950136 FW retro adjustmnet */
9630: function adjust_retro_fw
9631: (p_assignment_id IN per_assignments_f.assignment_id%TYPE
9632: ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
9633: ,p_start_date in date
9634: ,p_end_date in date
9635: ,p_fw_type in varchar2