[Home] [Help]
3467: P_PATCH_NAME IN VARCHAR2)
3468:
3469: IS
3470:
3471: --Retrieve all changed geocodes on pay_taxability_rules table.
3472:
3473:
3474: --Bug 3319878 -- Changed the cursor query to reduce cost.
3475: --Bug 5042715 -- Added hints to reduce cost.
3474: --Bug 3319878 -- Changed the cursor query to reduce cost.
3475: --Bug 5042715 -- Added hints to reduce cost.
3476: CURSOR ptax_cur IS
3477: SELECT /*+index( pmod PAY_US_MODIFIED_GEOCODES_N2 ,
3478: ptax PAY_TAXABILITY_RULES_UK)
3479: use_nl(pmod ptax)*/
3480: distinct ptax.jurisdiction_code
3481: FROM pay_us_modified_geocodes pmod,
3482: pay_taxability_rules ptax
3478: ptax PAY_TAXABILITY_RULES_UK)
3479: use_nl(pmod ptax)*/
3480: distinct ptax.jurisdiction_code
3481: FROM pay_us_modified_geocodes pmod,
3482: pay_taxability_rules ptax
3483: WHERE ptax.jurisdiction_code = pmod.state_code||'-000-'||pmod.old_city_code
3484: AND pmod.process_type in ('UP','RP')
3485: AND pmod.patch_name = p_patch_name
3486: AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3484: AND pmod.process_type in ('UP','RP')
3485: AND pmod.patch_name = p_patch_name
3486: AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3487: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3488: where pugu.table_name = 'PAY_TAXABILITY_RULES'
3489: and pugu.new_juri_code = ptax.jurisdiction_code
3490: and pugu.process_mode = g_mode
3491: and pugu.process_type = g_process_type
3492: and pugu.id = g_geo_phase_id
3496: ptax_rec ptax_cur%ROWTYPE;
3497:
3498: --Per bug 2996546
3499: --Added a cursor ptax_ca_cur to the procedure update_taxability_rules
3500: --Retrieve all changed jurisdiction_code on pay_taxability_rules table.
3501: --and update (for Canadian Legislation)
3502: --
3503:
3504: --Bug 3319878 -- Changed the query to improve performance
3505:
3506: CURSOR ptax_ca_cur IS
3507: SELECT distinct ptax.jurisdiction_code
3508: FROM pay_us_modified_geocodes pmod,
3509: pay_taxability_rules ptax
3510: WHERE pmod.state_code = 'CA'
3511: AND ptax.jurisdiction_code = pmod.county_code || '000-0000'
3512: AND pmod.patch_name = p_patch_name
3513: AND ptax.legislation_code = 'CA' ;
3518: ptax_ca_rec ptax_ca_cur%ROWTYPE;
3519:
3520:
3521:
3522: jd_code pay_taxability_rules.jurisdiction_code%TYPE;
3523: l_proc_type pay_us_modified_geocodes.process_type%TYPE;
3524: l_error_message_text varchar2(240);
3525: l_count number;
3526: BEGIN
3549:
3550: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',2);
3551:
3552: select count(*) into l_count
3553: from pay_taxability_rules ptax
3554: where ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4);
3555:
3556: IF l_count = 0 THEN
3557:
3556: IF l_count = 0 THEN
3557:
3558: IF G_MODE = 'UPGRADE' THEN
3559:
3560: UPDATE pay_taxability_rules ptax
3561: SET ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||
3562: substr(jd_code,8,4)
3563: WHERE ptax.jurisdiction_code = ptax_rec.jurisdiction_code;
3564:
3578: p_person_id => null,
3579: p_assign_id => null,
3580: p_old_juri_code => ptax_rec.jurisdiction_code,
3581: p_new_juri_code => substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4),
3582: p_location => 'PAY_TAXABILITY_RULES',
3583: p_id => null);
3584:
3585: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
3586:
3588: --
3589: --
3590: --
3591: --Per bug 2996546
3592: --Update of pay_taxability_rules . jurisdiction_code
3593: --(Canadian Legislation)
3594:
3595: OPEN ptax_ca_cur ;
3596: LOOP
3608: AND pmod.patch_name = p_patch_name;
3609:
3610: IF G_MODE = 'UPGRADE' THEN
3611:
3612: UPDATE pay_taxability_rules ptax
3613: SET ptax.jurisdiction_code = jd_code||'-000-'||'0000'
3614: WHERE ptax.jurisdiction_code = ptax_ca_rec.jurisdiction_code;
3615:
3616: -- COMMIT;
3626: p_person_id => null,
3627: p_assign_id => null,
3628: p_old_juri_code => ptax_ca_rec.jurisdiction_code,
3629: p_new_juri_code => jd_code||'-000-'||'0000',
3630: p_location => 'PAY_TAXABILITY_RULES',
3631: p_id => null);
3632:
3633: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
3634: