[Home] [Help]
3174: P_PATCH_NAME IN VARCHAR2)
3175:
3176: IS
3177:
3178: --Retrieve all changed geocodes on pay_taxability_rules table.
3179:
3180:
3181: --Bug 3319878 -- Changed the cursor query to reduce cost.
3182: --Bug 5042715 -- Added hints to reduce cost.
3181: --Bug 3319878 -- Changed the cursor query to reduce cost.
3182: --Bug 5042715 -- Added hints to reduce cost.
3183: CURSOR ptax_cur IS
3184: SELECT /*+index( pmod PAY_US_MODIFIED_GEOCODES_N2 ,
3185: ptax PAY_TAXABILITY_RULES_UK)
3186: use_nl(pmod ptax)*/
3187: distinct ptax.jurisdiction_code
3188: FROM pay_us_modified_geocodes pmod,
3189: pay_taxability_rules ptax
3185: ptax PAY_TAXABILITY_RULES_UK)
3186: use_nl(pmod ptax)*/
3187: distinct ptax.jurisdiction_code
3188: FROM pay_us_modified_geocodes pmod,
3189: pay_taxability_rules ptax
3190: WHERE ptax.jurisdiction_code = pmod.state_code||'-000-'||pmod.old_city_code
3191: AND pmod.process_type in ('UP','RP')
3192: AND pmod.patch_name = p_patch_name
3193: AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3191: AND pmod.process_type in ('UP','RP')
3192: AND pmod.patch_name = p_patch_name
3193: AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3194: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3195: where pugu.table_name = 'PAY_TAXABILITY_RULES'
3196: and pugu.new_juri_code = ptax.jurisdiction_code
3197: and pugu.process_mode = g_mode
3198: and pugu.process_type = g_process_type
3199: and pugu.id = g_geo_phase_id
3203: ptax_rec ptax_cur%ROWTYPE;
3204:
3205: --Per bug 2996546
3206: --Added a cursor ptax_ca_cur to the procedure update_taxability_rules
3207: --Retrieve all changed jurisdiction_code on pay_taxability_rules table.
3208: --and update (for Canadian Legislation)
3209: --
3210:
3211: --Bug 3319878 -- Changed the query to improve performance
3212:
3213: CURSOR ptax_ca_cur IS
3214: SELECT distinct ptax.jurisdiction_code
3215: FROM pay_us_modified_geocodes pmod,
3216: pay_taxability_rules ptax
3217: WHERE pmod.state_code = 'CA'
3218: AND ptax.jurisdiction_code = pmod.county_code || '000-0000'
3219: AND pmod.patch_name = p_patch_name
3220: AND ptax.legislation_code = 'CA' ;
3225: ptax_ca_rec ptax_ca_cur%ROWTYPE;
3226:
3227:
3228:
3229: jd_code pay_taxability_rules.jurisdiction_code%TYPE;
3230: l_proc_type pay_us_modified_geocodes.process_type%TYPE;
3231: l_error_message_text varchar2(240);
3232: l_count number;
3233: BEGIN
3256:
3257: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',2);
3258:
3259: select count(*) into l_count
3260: from pay_taxability_rules ptax
3261: where ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4);
3262:
3263: IF l_count = 0 THEN
3264:
3263: IF l_count = 0 THEN
3264:
3265: IF G_MODE = 'UPGRADE' THEN
3266:
3267: UPDATE pay_taxability_rules ptax
3268: SET ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||
3269: substr(jd_code,8,4)
3270: WHERE ptax.jurisdiction_code = ptax_rec.jurisdiction_code;
3271:
3285: p_person_id => null,
3286: p_assign_id => null,
3287: p_old_juri_code => ptax_rec.jurisdiction_code,
3288: p_new_juri_code => substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4),
3289: p_location => 'PAY_TAXABILITY_RULES',
3290: p_id => null);
3291:
3292: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
3293:
3295: --
3296: --
3297: --
3298: --Per bug 2996546
3299: --Update of pay_taxability_rules . jurisdiction_code
3300: --(Canadian Legislation)
3301:
3302: OPEN ptax_ca_cur ;
3303: LOOP
3315: AND pmod.patch_name = p_patch_name;
3316:
3317: IF G_MODE = 'UPGRADE' THEN
3318:
3319: UPDATE pay_taxability_rules ptax
3320: SET ptax.jurisdiction_code = jd_code||'-000-'||'0000'
3321: WHERE ptax.jurisdiction_code = ptax_ca_rec.jurisdiction_code;
3322:
3323: -- COMMIT;
3333: p_person_id => null,
3334: p_assign_id => null,
3335: p_old_juri_code => ptax_ca_rec.jurisdiction_code,
3336: p_new_juri_code => jd_code||'-000-'||'0000',
3337: p_location => 'PAY_TAXABILITY_RULES',
3338: p_id => null);
3339:
3340: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
3341: