[Home] [Help]
97:
98: c_fixed_futa_rt CONSTANT NUMBER(10,4) := 6.2;
99:
100: -- define some global variables for temporary storage
101: G_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE := NULL;
102: G_payroll_id pay_payroll_actions.payroll_id%TYPE := NULL;
103: G_got_fed_rate BOOLEAN := FALSE;
104: G_ss_ee_rate NUMBER := NULL;
105: G_ss_er_rate NUMBER := NULL;
199: G_futa_override_rt NUMBER := 0;
200:
201: -- Cursor fetches the futa override rate based on the tax unit id passed.
202: CURSOR c_get_futa_override_rt(
203: IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE) IS
204: SELECT NVL(org_information7,0)/100
205: FROM hr_organization_information
206: WHERE organization_id = IN_tax_unit_id
207: AND org_information_context = 'Federal Tax Rules';
265: lv_sqlstr :=
266: 'SELECT /*+ ORDERED
267: INDEX (ppa PAY_PAYROLL_ACTIONS_PK)
268: INDEX (pa1 PAY_PAYROLL_ACTIONS_N5)
269: INDEX (act PAY_ASSIGNMENT_ACTIONS_N50)
270: INDEX (paf PER_ASSIGNMENTS_F_PK) */
271: DISTINCT paf.person_id
272: FROM pay_payroll_actions ppa,
273: pay_payroll_actions pa1,
270: INDEX (paf PER_ASSIGNMENTS_F_PK) */
271: DISTINCT paf.person_id
272: FROM pay_payroll_actions ppa,
273: pay_payroll_actions pa1,
274: pay_assignment_actions act,
275: per_assignments_f paf
276: WHERE ppa.payroll_action_id = :payroll_action_id
277: AND pa1.effective_date >= fnd_date.canonical_to_date('''|| lv_cur_date ||''')
278: AND pa1.effective_date <= ppa.effective_date
325: paf.person_id person_id
326: FROM per_all_assignments_f paf,
327: pay_all_payrolls_f PPY
328: WHERE exists
329: (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
330: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
331: 'x'
332: from pay_payroll_actions ppa,
333: pay_assignment_actions paa
329: (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
330: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
331: 'x'
332: from pay_payroll_actions ppa,
333: pay_assignment_actions paa
334: where ppa.effective_date between cp_period_start
335: and cp_period_end
336: and ppa.action_type in ('R','Q','V','B','I')
337: and ppa.action_status = 'C'
374: SELECT /*+ ORDERED */
375: to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
376: FROM per_all_assignments_f paf,
377: pay_payroll_actions ppa,
378: pay_assignment_actions paa,
379: pay_action_classifications pac
380: WHERE paf.person_id = cp_person_id
381: AND paf.payroll_id = ppa.payroll_id
382: AND (paf.organization_id = IN_org_id
410: SELECT paa.assignment_id ,
411: paf.location_id,
412: paf.organization_id,
413: paf.assignment_number
414: FROM pay_assignment_actions paa,
415: pay_payroll_actions ppa,
416: per_all_assignments_f paf
417: WHERE paa.assignment_action_id = cp_asg_act_id
418: AND paa.tax_unit_id = cp_tax_unit_id
430: L_greid NUMBER;
431: L_as_of_date DATE := NULL;
432: L_start_date DATE;
433: L_leg_param pay_payroll_actions.legislative_parameters%TYPE;
434: L_gre_id pay_assignment_actions.tax_unit_id%TYPE;
435: L_org_id per_all_assignments_f.organization_id%TYPE;
436: L_location_id per_all_assignments_f.location_id%TYPE;
437: L_business_id per_all_assignments_f.business_group_id%TYPE;
438: L_dimension VARCHAR2(20) := NULL;
539: hr_utility.trace('L_assignid '||to_char(L_assignid));
540: hr_utility.trace('L_assignid ' || L_assignid);
541: hr_utility.set_location('procpyr',3);
542:
543: SELECT pay_assignment_actions_s.NEXTVAL
544: INTO L_lockingactid
545: FROM dual;
546:
547: IF L_lockedactid is not null then
590: hr_organization_units hou1,
591: hr_locations loc,
592: per_people_f ppf,
593: per_all_assignments_f paf,
594: pay_assignment_actions paa1, /* PYUGEN assignment action */
595: pay_payroll_actions ppa1 /* PYUGEN payroll action id */
596: WHERE ppa1.payroll_action_id = :pactid
597: AND paa1.payroll_action_id = ppa1.payroll_action_id
598: AND paa1.assignment_id = paf.assignment_id
958:
959: -- New function to cache the sui and sdi override rates
960: -- Caches for the first time and returns the value later on
961:
962: FUNCTION fnc_sui_sdi_override ( IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
963: ,IN_state_code IN VARCHAR2
964: ,IN_ret_flag IN VARCHAR2) RETURN NUMBER IS
965:
966: L_return_val NUMBER;
964: ,IN_ret_flag IN VARCHAR2) RETURN NUMBER IS
965:
966: L_return_val NUMBER;
967:
968: CURSOR c_get_sui_sdi_overide_rt (IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE) IS
969: SELECT pus.state_code,org_information6/100, org_information7/100 , org_information14/100
970: FROM hr_organization_information org, pay_us_states pus
971: WHERE org.org_information1 = pus.state_abbrev
972: AND pus.state_code between 00 and 99
1033: PAY_US_RPT_TOTALS table. There are two types of records, 1 is
1034: header record and the other is detail record.
1035: column mapping specs for header record:
1036: SESSION_ID := payroll_action_id (PYUGEN Payroll Action)
1037: TAX_UNIT_ID := tax_unit_id (from pay_assignment_actions)
1038: ORGANIZATION_ID := organization_id (from per_assignments_f)
1039: LOCATION_ID := location_id (from per_assignments_f)
1040: BUSINESS_GROUP_ID := chunk number from PYUGEN process
1041: VALUE1 := person_id
1346: **************************************************************************/
1347: PROCEDURE prc_process_data(IN_pact_id IN pay_payroll_actions.payroll_action_id%TYPE,
1348: IN_chunk_no IN NUMBER,
1349: IN_commit_count IN NUMBER DEFAULT 1000,
1350: IN_prc_lockingactid IN pay_assignment_actions.assignment_action_id%TYPE,
1351: IN_prc_lockedactid IN pay_assignment_actions.assignment_action_id%TYPE,
1352: IN_prc_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1353: IN_prc_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1354: IN_prc_person_id IN per_all_assignments_f.person_id%TYPE,
1347: PROCEDURE prc_process_data(IN_pact_id IN pay_payroll_actions.payroll_action_id%TYPE,
1348: IN_chunk_no IN NUMBER,
1349: IN_commit_count IN NUMBER DEFAULT 1000,
1350: IN_prc_lockingactid IN pay_assignment_actions.assignment_action_id%TYPE,
1351: IN_prc_lockedactid IN pay_assignment_actions.assignment_action_id%TYPE,
1352: IN_prc_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1353: IN_prc_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1354: IN_prc_person_id IN per_all_assignments_f.person_id%TYPE,
1355: IN_prc_location_id IN per_all_assignments_f.location_id%TYPE,
1348: IN_chunk_no IN NUMBER,
1349: IN_commit_count IN NUMBER DEFAULT 1000,
1350: IN_prc_lockingactid IN pay_assignment_actions.assignment_action_id%TYPE,
1351: IN_prc_lockedactid IN pay_assignment_actions.assignment_action_id%TYPE,
1352: IN_prc_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1353: IN_prc_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1354: IN_prc_person_id IN per_all_assignments_f.person_id%TYPE,
1355: IN_prc_location_id IN per_all_assignments_f.location_id%TYPE,
1356: IN_prc_organization_id IN per_all_assignments_f.organization_id%TYPE,
1349: IN_commit_count IN NUMBER DEFAULT 1000,
1350: IN_prc_lockingactid IN pay_assignment_actions.assignment_action_id%TYPE,
1351: IN_prc_lockedactid IN pay_assignment_actions.assignment_action_id%TYPE,
1352: IN_prc_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1353: IN_prc_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1354: IN_prc_person_id IN per_all_assignments_f.person_id%TYPE,
1355: IN_prc_location_id IN per_all_assignments_f.location_id%TYPE,
1356: IN_prc_organization_id IN per_all_assignments_f.organization_id%TYPE,
1357: IN_prc_assignment_number IN per_all_assignments_f.assignment_number%TYPE ) IS
1441:
1442: -- get all city level jurisdiction codes for specified person
1443: CURSOR c_school_jurisdictions_valid(IN_person_id IN per_people_f.person_id%TYPE,
1444: IN_state_code IN VARCHAR2,
1445: IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1446: IN_start_date IN DATE,
1447: IN_as_of_date IN DATE) IS
1448: SELECT DISTINCT
1449: prb.jurisdiction_code,
1524: -- AND pus.state_code = NVL(IN_state_code, pus.state_code)
1525: AND EXISTS (
1526: SELECT 'X'
1527: FROM pay_payroll_actions ppa,
1528: pay_assignment_actions paa,
1529: pay_run_results prr
1530: WHERE action_type IN ('B','I','R','Q','V')
1531: AND ppa.action_status = 'C'
1532: AND ppa.effective_date BETWEEN IN_start_date
1569: --AND pus.state_code = NVL(IN_state_code, pus.state_code)
1570: AND EXISTS (
1571: SELECT 'X'
1572: FROM pay_payroll_actions ppa,
1573: pay_assignment_actions paa,
1574: pay_run_results prr
1575: WHERE action_type IN ('B','I','R','Q','V')
1576: AND ppa.action_status = 'C'
1577: AND ppa.effective_date BETWEEN IN_start_date
1616: -- AND pus.state_code = NVL(IN_state_code, pus.state_code)
1617: AND EXISTS (
1618: SELECT 'X'
1619: FROM pay_payroll_actions ppa,
1620: pay_assignment_actions paa,
1621: pay_run_results prr
1622: WHERE action_type IN ('B','I','R','Q','V')
1623: AND ppa.action_status = 'C'
1624: AND ppa.effective_date BETWEEN IN_start_date
1632:
1633: -- get all city level jurisdiction codes for specified person
1634: CURSOR c_school_jurisdictions(IN_person_id IN per_people_f.person_id%TYPE,
1635: IN_state_code IN VARCHAR2,
1636: IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1637: IN_start_date IN DATE,
1638: IN_as_of_date IN DATE) IS
1639: SELECT DISTINCT
1640: pes.state_code||'-'||pes.school_district_code jurisdiction_code,
1666: OR IN_state_code IS NULL)
1667: AND EXISTS (
1668: SELECT 'X'
1669: FROM pay_payroll_actions ppa,
1670: pay_assignment_actions paa,
1671: pay_run_results prr
1672: WHERE action_type IN ('B','I','R','Q','V')
1673: AND ppa.action_status = 'C'
1674: AND ppa.effective_date BETWEEN IN_start_date
1708: OR IN_state_code IS NULL)
1709: AND EXISTS (
1710: SELECT 'X'
1711: FROM pay_payroll_actions ppa,
1712: pay_assignment_actions paa,
1713: pay_run_results prr
1714: WHERE action_type IN ('B','I','R','Q','V')
1715: AND ppa.action_status = 'C'
1716: AND ppa.effective_date BETWEEN IN_start_date
1736:
1737: L_as_of_date DATE := NULL;
1738: L_start_date DATE ;
1739: L_leg_param pay_payroll_actions.legislative_parameters%TYPE;
1740: L_gre_id pay_assignment_actions.tax_unit_id%TYPE;
1741: L_org_id per_all_assignments_f.organization_id%TYPE;
1742: L_location_id per_all_assignments_f.location_id%TYPE;
1743: L_business_id per_all_assignments_f.business_group_id%TYPE;
1744: L_dimension VARCHAR2(20) := NULL;
1859: -----------------------------------------------
1860: --
1861: -- changes made be tmehra
1862: --
1863: FUNCTION f_check_medi_exempt(f_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1864: f_start_date IN DATE,
1865: f_as_of_date IN DATE) RETURN VARCHAR2 IS
1866:
1867: CURSOR c_chk_medi_exempt(IN_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1863: FUNCTION f_check_medi_exempt(f_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1864: f_start_date IN DATE,
1865: f_as_of_date IN DATE) RETURN VARCHAR2 IS
1866:
1867: CURSOR c_chk_medi_exempt(IN_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
1868: IN_start_date IN DATE,
1869: IN_as_of_date IN DATE) IS
1870: SELECT medicare_tax_exempt
1871: FROM pay_us_emp_fed_tax_rules_v