[Home] [Help]
46: Report_14 to match the data of Location
47: being displayed with Header Record.
48: 14-Jul-2011 emunisek 115.14 10060041 Modified function Report_12 to display
49: the County Update Message based on the
50: data found in pay_us_geo_update table
51: unlike the earlier hard-coded message.
52: 18-Jul-2011 emunisek 115.15 AGEO2011 Modified the package to take the 30
53: characters of the city name unlike the
54: earlier 20 characters limit as it is
74: 18-Jul-2012 emunisek 115.19 AGEO2012 Under report_5, new_juri_code was shown as
75: Old Jurisdiction and old_juri_code as New
76: Jurisdiction. Corrected the issue
77: 19-Jul-2012 emunisek 115.20 14314081 Added changes to check Old City Name saved in
78: PAY_US_GEO_UPDATE table to give correct output
79: report
80: */
81:
82: /************************************************************
695: substr(pef.assignment_number,1,17) ,
696: substr(pgu.description,1,65)
697: from per_people_f ppf,
698: per_assignments_f pef,
699: pay_us_geo_update pgu,
700: pay_patch_status pps,
701: pay_us_emp_city_tax_rules_f pctrf
702: where pef.assignment_id = pgu.assignment_id
703: and pctrf.assignment_id = pef.assignment_id
907: pef.assignment_id ,
908: substr(pef.assignment_number,1,17)
909: from per_people_f ppf,
910: per_assignments_f pef,
911: pay_us_geo_update pgu,
912: pay_patch_status pps
913: where pgu.process_type = 'PERCENTAGE_OVER_100'
914: and pef.assignment_id = pgu.assignment_id
915: and ppf.person_id = pef.person_id
1092: pef.assignment_id ,
1093: substr(pef.assignment_number,1,17)
1094: from per_people_f ppf,
1095: per_assignments_f pef,
1096: pay_us_geo_update pgu,
1097: pay_patch_status pps
1098: where pgu.process_type = 'MISSING_COUNTY_RECORDS'
1099: and pef.assignment_id = pgu.assignment_id
1100: and ppf.person_id = pef.person_id
1279: pay_us_city_names pusc ,
1280: pay_patch_status pps,
1281: per_people_f ppf,
1282: per_assignments_f pef,
1283: pay_us_geo_update pgu
1284: where pgu.process_type = 'PU'
1285: and pef.assignment_id = pgu.assignment_id
1286: and ppf.person_id = pef.person_id
1287: and pgu.table_name is not null
1513: new_juri_code "New JD",
1514: substr(table_name,1,20)
1515: from per_people_f ppf,
1516: per_assignments_f pef,
1517: pay_us_geo_update pgu,
1518: pay_us_city_names pusc,
1519: pay_patch_status pps
1520: where pgu.process_type = 'UP'
1521: and pgu.table_name is not null
1730: old_juri_code ,
1731: new_juri_code
1732: from per_people_f ppf,
1733: per_assignments_f pef,
1734: pay_us_geo_update pgu,
1735: pay_us_city_names pusc,
1736: pay_patch_status pps ,
1737: pay_us_states pust,
1738: pay_us_counties puscn
2377: ,cp_geocode_patch_name in varchar
2378: ) is
2379:
2380: select /*+ ORDERED
2381: INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
2382: INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2383: distinct substr(ppf.full_name,1,40) ,
2384: pef.assignment_id ,
2385: substr(pef.assignment_number,1,17),
2385: substr(pef.assignment_number,1,17),
2386: substr(pusc.city_name,1,30),
2387: pgu.new_juri_code
2388: from pay_patch_status pps,
2389: pay_us_geo_update pgu,
2390: pay_us_city_names pusc,
2391: per_assignments_f pef,
2392: per_people_f ppf
2393: where pgu.process_type = 'NEW_CITY_RECORDS'
2581: ,cp_geocode_patch_name in varchar
2582: ) is
2583:
2584: select /*+ ORDERED
2585: INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
2586: INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2587: distinct substr(ppf.full_name,1,40) ,
2588: pef.assignment_id ,
2589: substr(pef.assignment_number,1,17),
2589: substr(pef.assignment_number,1,17),
2590: substr(pusc.city_name,1,30),
2591: pgu.new_juri_code
2592: from pay_patch_status pps,
2593: pay_us_geo_update pgu,
2594: pay_us_city_names pusc,
2595: per_assignments_f pef,
2596: per_people_f ppf
2597: where pgu.process_type = 'NEW_VERTEX_RECORDS'
2790: ,cp_geocode_patch_name in varchar
2791: ) is
2792:
2793: select /*+ ORDERED
2794: INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
2795: INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2796: distinct old_juri_code ,
2797: new_juri_code
2798: from pay_patch_status pps,
2795: INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2796: distinct old_juri_code ,
2797: new_juri_code
2798: from pay_patch_status pps,
2799: pay_us_geo_update pgu
2800: where pgu.process_type = 'TAX_RULES_CHANGE'
2801: and pgu.process_mode = cp_process_mode
2802: and pgu.id = pps.id
2803: and pps.patch_name = cp_geocode_patch_name;
2986:
2987: cursor c_county_name_change (cp_geocode_patch_name in varchar) is
2988:
2989: SELECT pugp.new_juri_code,pugp.description
2990: FROM pay_us_geo_update pugp,
2991: pay_patch_status pps
2992: WHERE pugp.table_name in ('PER_ADDRESSES','HR_LOCATIONS_ALL')
2993: AND pugp.process_type = 'CN'
2994: AND pugp.id = pps.id
4095: ,cp_geocode_patch_name in varchar
4096: ) is
4097:
4098: select /*+ ORDERED
4099: INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
4100: INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
4101: distinct substr(ppf.full_name,1,40) ,
4102: ppf.person_id ,
4103: ppf.employee_number,
4105: pumg1.city_name old_city_name,
4106: pumg2.city_name new_city_name,
4107: substr(pgu.description,1,instr(pgu.description,':')-1)
4108: from pay_patch_status pps,
4109: pay_us_geo_update pgu,
4110: per_people_f ppf,
4111: per_addresses pa,
4112: pay_us_modified_geocodes pumg1,
4113: pay_us_modified_geocodes pumg2
4142: ,cp_geocode_patch_name in varchar
4143: ) is
4144:
4145: select /*+ ORDERED
4146: INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
4147: INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
4148: distinct substr(hl.location_code,1,40) ,
4149: hl.location_id ,
4150: pgu.new_juri_code,
4151: pumg1.city_name old_city_name,
4152: pumg2.city_name new_city_name,
4153: substr(pgu.description,1,instr(pgu.description,':')-1)
4154: from pay_patch_status pps,
4155: pay_us_geo_update pgu,
4156: hr_locations_all hl,
4157: pay_us_modified_geocodes pumg1,
4158: pay_us_modified_geocodes pumg2
4159: where pgu.process_type = 'CY'
4185: cursor c_org_cursor ( cp_process_mode in varchar
4186: ,cp_geocode_patch_name in varchar
4187: ) is
4188: select /*+ ORDERED
4189: INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
4190: INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
4191: distinct substr(hou.name,1,40) ,
4192: hoi.organization_id ,
4193: pgu.new_juri_code,
4195: pumg2.city_name new_city_name,
4196: NVL(hoit.displayed_org_information_type,fdfv.descriptive_flex_context_name)||
4197: ', '||fdfc.form_left_prompt
4198: from pay_patch_status pps,
4199: pay_us_geo_update pgu,
4200: hr_organization_information hoi,
4201: hr_organization_units hou,
4202: fnd_descr_flex_contexts_vl fdfv,
4203: fnd_descr_flex_col_usage_vl fdfc,
4236:
4237: ln_full_name per_people_f.full_name%TYPE;
4238: ln_person_id per_people_f.person_id%TYPE;
4239: ln_employee_number per_people_f.employee_number%TYPE;
4240: ln_jurisdiction_code pay_us_geo_update.new_juri_code%TYPE;
4241: ln_old_city_name pay_us_modified_geocodes.city_name%TYPE;
4242: ln_new_city_name pay_us_modified_geocodes.city_name%TYPE;
4243: ln_update_description pay_us_geo_update.description%TYPE;
4244:
4239: ln_employee_number per_people_f.employee_number%TYPE;
4240: ln_jurisdiction_code pay_us_geo_update.new_juri_code%TYPE;
4241: ln_old_city_name pay_us_modified_geocodes.city_name%TYPE;
4242: ln_new_city_name pay_us_modified_geocodes.city_name%TYPE;
4243: ln_update_description pay_us_geo_update.description%TYPE;
4244:
4245: ln_location_name hr_locations_all.location_code%TYPE;
4246: ln_location_id hr_locations_all.location_id%TYPE;
4247:
4816: PROCEDURE city_name_change_report
4817: (p_call varchar2,
4818: p_patch_name varchar2,
4819: p_mode varchar2,
4820: p_geo_phase_id pay_us_geo_update.id%TYPE)
4821:
4822: IS
4823:
4824: lv_output_file_type varchar2(4);