eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.PAY_US_GEO_UPD_PKG dependencies on PAY_US_EMP_CITY_TAX_RULES_F

Line 233: pay_us_emp_city_tax_rules_f tr,

229: where ppa.payroll_action_id = pactid;
230:
231: sqlstr := ' select distinct paf.person_id
232: from pay_us_modified_geocodes mg,
233: pay_us_emp_city_tax_rules_f tr,
234: per_all_assignments_f paf,
235: pay_us_states pus
236: where mg.patch_name = '''||ln_upgrade_patch||'''
237: and mg.state_code = pus.state_code

Line 286: pay_us_emp_city_tax_rules_f ectr,

282: ) is
283:
284: SELECT distinct ectr.assignment_id
285: FROM per_all_assignments_f paf,
286: pay_us_emp_city_tax_rules_f ectr,
287: pay_us_modified_geocodes pmod
288: WHERE pmod.state_code = ectr.state_code
289: AND pmod.county_code = ectr.county_code
290: AND pmod.new_county_code is null

Line 929: --Retrieve all changed geocodes on pay_us_emp_city_tax_rules_f table.

925: AND paf.assignment_id = paei.assignment_id;
926:
927: paei_rec paei_cur%ROWTYPE; */
928:
929: --Retrieve all changed geocodes on pay_us_emp_city_tax_rules_f table.
930: --This will be our main 'driving' table.
931: --Added the ASSIGN START and ASSIGN END so that we can multi-thread the
932: --driving cursor
933: --

Line 946: pay_us_emp_city_tax_rules_f ectr,

942: SELECT distinct ectr.jurisdiction_code, ectr.assignment_id,
943: pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code jd_code,
944: paf.person_id, pmod.new_city_code, pmod.process_type, ectr.emp_city_tax_rule_id
945: FROM per_all_assignments_f paf,
946: pay_us_emp_city_tax_rules_f ectr,
947: pay_us_modified_geocodes pmod
948: WHERE pmod.state_code = ectr.state_code
949: AND pmod.county_code = ectr.county_code
950: AND pmod.new_county_code is null

Line 997: --Retrieve all affected rows in PAY_US_EMP_CITY_TAX_RULES_F

993: lv_update_prr varchar2(1);
994:
995: -- main_ ectr_cur%ROWTYPE;
996:
997: --Retrieve all affected rows in PAY_US_EMP_CITY_TAX_RULES_F
998: --This is decoupled from above because we still want the level of
999: --of granularity for city tax records that are changed.
1000: --We already have this information we just need to verify if it has
1001: --been processed already.

Line 1009: FROM pay_us_emp_city_tax_rules_f puecf

1005: cursor city_rec_cur (p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2,
1006: p_assign_id NUMBER, p_city_tax_record_id NUMBER)
1007: IS
1008: SELECT distinct 'Y'
1009: FROM pay_us_emp_city_tax_rules_f puecf
1010: WHERE puecf.jurisdiction_code = p_old_juri_code
1011: AND puecf.assignment_id = p_assign_id
1012: AND puecf.emp_city_tax_rule_id = p_city_tax_record_id
1013: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu

Line 1017: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'

1013: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1014: where pugu.assignment_id = p_assign_id
1015: and pugu.table_value_id = puecf.emp_city_tax_rule_id
1016: and pugu.old_juri_code = p_old_juri_code
1017: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1018: and pugu.process_mode = g_mode
1019: and pugu.process_type = g_process_type
1020: and pugu.id = g_geo_phase_id);
1021:

Line 1594: UPDATE pay_us_emp_city_tax_rules_f

1590: hr_utility.trace('Before update of city tax records for assignment id: '||to_char(p_assign_id));
1591:
1592: IF G_MODE = 'UPGRADE' THEN
1593:
1594: UPDATE pay_us_emp_city_tax_rules_f
1595: SET jurisdiction_code = p_new_juri_code,
1596: city_code = p_new_city_code
1597: WHERE jurisdiction_code = p_old_juri_code
1598: AND assignment_id = p_assign_id

Line 1604: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'

1600: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1601: where pugu.assignment_id = p_assign_id
1602: and pugu.table_value_id = p_city_tax_record_id
1603: and pugu.old_juri_code = p_old_juri_code
1604: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1605: and pugu.process_mode = g_mode
1606: and pugu.process_type = g_process_type
1607: and pugu.id = g_geo_phase_id);
1608:

Line 1621: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',

1617: p_person_id => p_person_id,
1618: p_assign_id => p_assign_id,
1619: p_old_juri_code => p_old_juri_code,
1620: p_new_juri_code => p_new_juri_code,
1621: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1622: p_id => p_city_tax_record_id);
1623:
1624: /*END IF;*/
1625: ELSE

Line 1636: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',

1632: p_person_id => p_person_id,
1633: p_assign_id => p_assign_id,
1634: p_old_juri_code => p_old_juri_code,
1635: p_new_juri_code => p_new_juri_code,
1636: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1637: p_id => p_city_tax_record_id);
1638:
1639: END IF;
1640:

Line 1646: -- rows in the pay_us_emp_city_tax_rules_f table created by geocode updates.

1642:
1643: PROCEDURE del_dup_city_tax_recs IS
1644:
1645: -- This cursor identifies assignment id/jurisdiction pairs that have multiple
1646: -- rows in the pay_us_emp_city_tax_rules_f table created by geocode updates.
1647: -- For example, prior to geocode patch 1105095, these geocodes were in place:
1648: -- Van Nuys, CA: 05-037-3880
1649: -- Woodland Hills, CA: 05-037-6080
1650: -- Los Angeles, CA: 05-037-1900

Line 1660: from pay_us_emp_city_tax_rules_f pect1,

1656:
1657: -- Bug 3319878 -- Changed the query to reduce the cost of the query
1658: CURSOR dup_city_tax_rows is
1659: select distinct pect1.assignment_id, pect1.jurisdiction_code
1660: from pay_us_emp_city_tax_rules_f pect1,
1661: pay_us_emp_city_tax_rules_f pect2
1662: where pect1.assignment_id = pect2.assignment_id
1663: and pect1.jurisdiction_code = pect2.jurisdiction_code
1664: and pect1.emp_city_tax_rule_id < pect2.emp_city_tax_rule_id

Line 1661: pay_us_emp_city_tax_rules_f pect2

1657: -- Bug 3319878 -- Changed the query to reduce the cost of the query
1658: CURSOR dup_city_tax_rows is
1659: select distinct pect1.assignment_id, pect1.jurisdiction_code
1660: from pay_us_emp_city_tax_rules_f pect1,
1661: pay_us_emp_city_tax_rules_f pect2
1662: where pect1.assignment_id = pect2.assignment_id
1663: and pect1.jurisdiction_code = pect2.jurisdiction_code
1664: and pect1.emp_city_tax_rule_id < pect2.emp_city_tax_rule_id
1665: and pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;

Line 1668: from pay_us_emp_city_tax_rules_f pect1

1664: and pect1.emp_city_tax_rule_id < pect2.emp_city_tax_rule_id
1665: and pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;
1666:
1667: /* select distinct pect1.assignment_id, pect1.jurisdiction_code
1668: from pay_us_emp_city_tax_rules_f pect1
1669: where pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END
1670: and pect1.emp_city_tax_rule_id <
1671: (select pect2.emp_city_tax_rule_id
1672: from pay_us_emp_city_tax_rules_f pect2

Line 1672: from pay_us_emp_city_tax_rules_f pect2

1668: from pay_us_emp_city_tax_rules_f pect1
1669: where pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END
1670: and pect1.emp_city_tax_rule_id <
1671: (select pect2.emp_city_tax_rule_id
1672: from pay_us_emp_city_tax_rules_f pect2
1673: where pect1.assignment_id = pect2.assignment_id
1674: and pect1.jurisdiction_code = pect2.jurisdiction_code
1675: );
1676: */

Line 1691: DELETE FROM pay_us_emp_city_tax_rules_f pecto

1687:
1688: hr_utility.trace('Deleting dups for Assign ID: ' || to_char(dup_rec.assignment_id) ||
1689: ' Geocode: ' || dup_rec.jurisdiction_code);
1690:
1691: DELETE FROM pay_us_emp_city_tax_rules_f pecto
1692: WHERE pecto.rowid < (SELECT max(pecti.rowid)
1693: FROM pay_us_emp_city_tax_rules_f pecti
1694: WHERE pecti.assignment_id = pecto.assignment_id
1695: AND pecti.assignment_id = dup_rec.assignment_id

Line 1693: FROM pay_us_emp_city_tax_rules_f pecti

1689: ' Geocode: ' || dup_rec.jurisdiction_code);
1690:
1691: DELETE FROM pay_us_emp_city_tax_rules_f pecto
1692: WHERE pecto.rowid < (SELECT max(pecti.rowid)
1693: FROM pay_us_emp_city_tax_rules_f pecti
1694: WHERE pecti.assignment_id = pecto.assignment_id
1695: AND pecti.assignment_id = dup_rec.assignment_id
1696: AND pecti.jurisdiction_code = pecto.jurisdiction_code
1697: AND pecti.jurisdiction_code = dup_rec.jurisdiction_code

Line 2452: from pay_us_emp_city_tax_rules_f pect

2448: p_state_code in varchar2,
2449: p_county_code in varchar2,
2450: p_city_code in varchar2) is
2451: select business_group_id
2452: from pay_us_emp_city_tax_rules_f pect
2453: where pect.assignment_id = p_assignment_id
2454: and pect.state_code = p_state_code
2455: and pect.county_code = p_county_code
2456: and pect.city_code = p_city_code;

Line 2463: from pay_us_emp_city_tax_rules_f peft

2459: cursor c_elig_date (p_assignment_id in number) is
2460: select min(peft.effective_start_date),
2461: max(peft.effective_end_date),
2462: peft.business_group_id
2463: from pay_us_emp_city_tax_rules_f peft
2464: where peft.assignment_id = p_assignment_id
2465: group by peft.business_group_id;
2466:
2467: ld_eff_start_date date;

Line 2471: ln_city_code pay_us_emp_city_tax_rules_f.city_code%TYPE;

2467: ld_eff_start_date date;
2468: ld_eff_end_date date;
2469: ln_state_code pay_us_emp_state_tax_rules_f.state_code%TYPE;
2470: ln_county_code pay_us_emp_county_tax_rules_f.county_code%TYPE;
2471: ln_city_code pay_us_emp_city_tax_rules_f.city_code%TYPE;
2472: ln_old_city_code pay_us_modified_geocodes.old_city_code%TYPE;
2473:
2474: ln_business_group_id number;
2475: ln_check number;

Line 2604: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',

2600: p_person_id => p_person_id,
2601: p_assign_id => p_assign_id,
2602: p_old_juri_code => null,
2603: p_new_juri_code => p_new_juri_code,
2604: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2605: p_id => ln_emp_city_tax_rule_id);
2606:
2607: else /* Modified for bug 6864396*/
2608:

Line 2615: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',

2611: p_person_id => p_person_id,
2612: p_assign_id => p_assign_id,
2613: p_old_juri_code => null,
2614: p_new_juri_code => p_new_juri_code,
2615: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2616: p_id => null);
2617:
2618: END IF;
2619:

Line 3274: --Update the pay_us_emp_city_tax_rules_f table.

3270:
3271: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',140);
3272:
3273:
3274: --Update the pay_us_emp_city_tax_rules_f table.
3275:
3276: OPEN city_rec_cur(main_new_juri_code, main_old_juri_code, main_assign_id, main_city_tax_rule_id);
3277: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',145);
3278: