[Home] [Help]
643:
644: /*cursor c_get_jurisdiction (p_assignment_id number
645: ,p_tax_unit_id number) is
646: select puar.jurisdiction_code
647: from pay_us_asg_reporting puar
648: where puar.assignment_id = p_assignment_id
649: and puar.tax_unit_id = p_tax_unit_id
650: and length(puar.jurisdiction_code)=16;*/
651:
845: -- Cursor to get all the GREs belonging to the given business group
846:
847: CURSOR c_get_gre IS
848: SELECT DISTINCT puar.tax_unit_id, hou.name
849: FROM pay_us_asg_reporting puar,
850: hr_all_organization_units hou
851: WHERE length(puar.jurisdiction_code)=16 and
852: puar.tax_unit_id = hou.organization_id and
853: hou.business_group_id = p_business_group_id and
891: -- Cursor to get all the psd codes under a gre for which PSD balances exist
892:
893: CURSOR c_get_psd_codes_for_gre(l_tax_unit_id number) IS
894: SELECT puar.assignment_id, substr(puar.jurisdiction_code,11,6)
895: FROM pay_us_asg_reporting puar
896: WHERE puar.tax_unit_id =l_tax_unit_id and
897: length(puar.jurisdiction_code)=16 and
898: substr(puar.jurisdiction_code, 11,6) not in ('880000','990000') and
899: substr(puar.jurisdiction_code, 11,2)=nvl(p_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2))
950: ,'TRANSFER_TRANS_LEGAL_CO_ID') into l_transmitter
951: from dual;
952:
953: IF l_period_type <> 'Yearly' then
954: --Get a list of all GREs under pay_us_asg_reporting which have assignments with PSD Tax deductions.
955: open c_get_gre;
956: loop
957: fetch c_get_gre into l_tax_unit_id, l_gre_name;
958: exit when c_get_gre%NOTFOUND;
1032: pay_core_utils.push_token('description',message_text);
1033:
1034: end if; --PSD Reporting Rules exist or not
1035: close c_check_psd_rules_for_gre;
1036: end loop; -- Ending the loop for list of all GREs under pay_us_asg_reporting which have assignments with PSD Tax deductions.
1037: close c_get_gre;
1038:
1039: ELSIF l_period_type = 'Yearly' then
1040:
1112:
1113: END IF;
1114:
1115: CLOSE c_gre_payroll_action;
1116: end loop; -- Ending the loop for list of all GREs under pay_us_asg_reporting which have assignments with PSD Tax deductions.
1117: close c_get_gre;
1118:
1119:
1120: IF l_package_error_status = 'Y' THEN
1201:
1202:
1203: CURSOR c_get_gre IS
1204: SELECT DISTINCT puar.tax_unit_id
1205: FROM pay_us_asg_reporting puar,
1206: hr_all_organization_units hou,
1207: hr_organization_information hoi
1208: WHERE length(puar.jurisdiction_code)=16 and
1209: substr(puar.jurisdiction_code, 11,2)=nvl(l_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2)) and
1318:
1319: p_sqlstr := 'SELECT distinct ASG.person_id
1320: FROM hr_organization_information HOI,
1321: per_all_assignments_f ASG,
1322: pay_us_asg_reporting puar,
1323: pay_state_rules SR
1324: WHERE SR.state_code = '''||l_state_abbrev||'''
1325: AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
1326: AND substr(puar.jurisdiction_code, 11,2)=nvl('''||l_tax_coll_dist_code||''',substr(puar.jurisdiction_code, 11,2))
1346: FROM pay_assignment_actions paa,
1347: pay_payroll_actions ppa,
1348: per_all_assignments_f paf,
1349: pay_payroll_actions ppa1,
1350: pay_us_asg_reporting puar,
1351: pay_state_rules SR
1352: WHERE ppa1.payroll_action_id = :payroll_action_id
1353: AND ppa.business_group_id+0 = ppa1.business_group_id
1354: AND ppa1.effective_date = ppa.effective_date
1581: and ASG.assignment_type = 'E'
1582: and ASG.primary_flag = 'Y'
1583: and ASG.business_group_id = ppa.business_group_id -- 5152728
1584: and exists (select '1'
1585: from pay_us_asg_reporting puar,
1586: pay_state_rules SR
1587: where SR.state_code = l_state_abbrev
1588: and substr(SR.jurisdiction_code ,1,2) =
1589: substr(puar.jurisdiction_code,1,2)
1619: and ASG.assignment_type = 'E'
1620: and ASG.primary_flag = 'Y'
1621: and ASG.business_group_id = ppa.business_group_id -- 5152728
1622: and exists (select '1'
1623: from pay_us_asg_reporting puar,
1624: pay_state_rules SR
1625: where SR.state_code = l_state_abbrev
1626: and substr(SR.jurisdiction_code ,1,2) =
1627: substr(puar.jurisdiction_code,1,2)
1739: -- Modified for Bug 14640336
1740: cursor c_get_jurisdiction (cp_person_id number
1741: ,cp_tax_unit_id number) is
1742: select distinct puar.jurisdiction_code
1743: from pay_us_asg_reporting puar,
1744: per_all_Assignments_f paf
1745: where puar.assignment_id = paf.assignment_id
1746: and paf.person_id = cp_person_id
1747: and puar.tax_unit_id = cp_tax_unit_id
1877: hr_utility.trace('l_aaid in action creation '||to_char(l_aaid));
1878: close c_get_latest_asg;
1879:
1880: open c_get_jurisdiction( l_person_id, l_tax_unit_id ); -- Modified for Bug 14640336
1881: loop -- To consider all the jurisdiction codes for given asg_id in the table pay_us_asg_reporting
1882: fetch c_get_jurisdiction into l_jurisdiction_code;
1883: exit when c_get_jurisdiction%notfound;
1884:
1885:
2853: -- Modified for Bug 14640336
2854: cursor c_get_jurisdiction (cp_person_id number
2855: ,cp_tax_unit_id number) is
2856: select distinct puar.jurisdiction_code
2857: from pay_us_asg_reporting puar,
2858: per_all_Assignments_f paf
2859: where puar.assignment_id = paf.assignment_id
2860: and paf.person_id=cp_person_id
2861: and puar.tax_unit_id = cp_tax_unit_id