[Home] [Help]
1324: --
1325: cursor csr_scl_idsel is
1326: select plr.rule_mode id_flex_num
1327: from pay_legislation_rules plr,
1328: per_business_groups_perf pgr
1329: where plr.legislation_code = pgr.legislation_code
1330: and pgr.business_group_id = p_business_group_id
1331: and plr.rule_type = 'S'
1332: and exists
2171: , asg.primary_flag
2172: , bus.business_group_id
2173: , bus.legislation_code
2174: from per_all_assignments_f asg
2175: , per_business_groups_perf bus
2176: where asg.assignment_id = p_assignment_id
2177: and l_actual_termination_date between asg.effective_start_date
2178: and asg.effective_end_date
2179: and bus.business_group_id+0 = asg.business_group_id;
3087: , asg.primary_flag
3088: , bus.business_group_id
3089: , bus.legislation_code
3090: from per_all_assignments_f asg
3091: , per_business_groups_perf bus
3092: where asg.assignment_id = p_assignment_id
3093: and l_actual_termination_date between asg.effective_start_date
3094: and asg.effective_end_date
3095: and bus.business_group_id+0 = asg.business_group_id;
5073: cursor csr_get_derived_details is
5074: select bus.business_group_id
5075: , bus.legislation_code
5076: from per_all_people_f per
5077: , per_business_groups_perf bus
5078: where per.person_id = p_person_id
5079: and l_effective_date between per.effective_start_date
5080: and per.effective_end_date
5081: and bus.business_group_id = per.business_group_id;
5097: -- (grp keyflex) for the specified business group
5098: --
5099: cursor csr_grp_idsel is
5100: select people_group_structure
5101: from per_business_groups_perf
5102: where business_group_id = l_business_group_id;
5103: --
5104: --
5105: -- the cursor csr_scl_idsel selects the valid id_flex_num
6500: cursor csr_get_derived_details is
6501: select bus.business_group_id
6502: , bus.legislation_code
6503: from per_all_people_f per
6504: , per_business_groups_perf bus
6505: where per.person_id = p_person_id
6506: and l_effective_date between per.effective_start_date
6507: and per.effective_end_date
6508: and bus.business_group_id = per.business_group_id;
6520: -- (grp keyflex) for the specified business group
6521: --
6522: cursor csr_grp_idsel is
6523: select people_group_structure
6524: from per_business_groups_perf
6525: where business_group_id = l_business_group_id;
6526: --
6527: --
6528: -- the cursor csr_scl_idsel selects the valid id_flex_num
7748: --
7749: cursor csr_legislation is
7750: select null
7751: from per_all_assignments_f paf,
7752: per_business_groups_perf pbg
7753: where paf.person_id = p_person_id
7754: and l_effective_date between paf.effective_start_date
7755: and paf.effective_end_date
7756: and pbg.business_group_id = paf.business_group_id
8057: --
8058: cursor csr_legislation is
8059: select null
8060: from per_all_assignments_f paf,
8061: per_business_groups_perf pbg
8062: where paf.person_id = p_person_id
8063: and l_effective_date between paf.effective_start_date
8064: and paf.effective_end_date
8065: and pbg.business_group_id = paf.business_group_id
8379: --
8380: cursor csr_legislation is
8381: select null
8382: from per_all_assignments_f paf,
8383: per_business_groups_perf pbg
8384: where paf.person_id = p_person_id
8385: and l_effective_date between paf.effective_start_date
8386: and paf.effective_end_date
8387: and pbg.business_group_id = paf.business_group_id
8712: --
8713: cursor csr_legislation is
8714: select null
8715: from per_all_assignments_f paf,
8716: per_business_groups_perf pbg
8717: where paf.person_id = p_person_id
8718: and l_effective_date between paf.effective_start_date
8719: and paf.effective_end_date
8720: and pbg.business_group_id = paf.business_group_id
10669: --
10670: cursor csr_scl_idsel is
10671: select plr.rule_mode id_flex_num
10672: from pay_legislation_rules plr,
10673: per_business_groups_perf pgr
10674: where plr.legislation_code = pgr.legislation_code
10675: and pgr.business_group_id = l_business_group_id
10676: and plr.rule_type = 'S'
10677: and exists
11942: --
11943: cursor csr_scl_idsel is
11944: select plr.rule_mode id_flex_num
11945: from pay_legislation_rules plr,
11946: per_business_groups_perf pgr
11947: where plr.legislation_code = pgr.legislation_code
11948: and pgr.business_group_id = l_business_group_id
11949: and plr.rule_type = 'CWK_S'
11950: and exists
12869: --
12870:
12871: cursor csr_get_legislation_code is
12872: select bus.legislation_code
12873: from per_business_groups_perf bus
12874: where bus.business_group_id = l_business_group_id;
12875: --
12876: cursor csr_get_salary is
12877: select element_entry_id
12882: effective_start_date and effective_end_date;
12883: --
12884: cursor csr_grp_idsel is
12885: select bus.people_group_structure
12886: from per_business_groups_perf bus
12887: where bus.business_group_id = l_business_group_id;
12888: --
12889: cursor get_sec_date_range is
12890: select asg.effective_start_date
13933: )
13934: is
13935: select bgp.legislation_code
13936: from per_all_assignments_f asg,
13937: per_business_groups_perf bgp
13938: where asg.business_group_id = bgp.business_group_id
13939: and asg.assignment_id = c_assignment_id
13940: and c_effective_date
13941: between effective_start_date and effective_end_date;
14170: )
14171: is
14172: select bgp.legislation_code
14173: from per_all_assignments_f asg,
14174: per_business_groups_perf bgp
14175: where asg.business_group_id = bgp.business_group_id
14176: and asg.assignment_id = c_assignment_id
14177: and c_effective_date
14178: between effective_start_date and effective_end_date;
14618: )
14619: is
14620: select bgp.legislation_code
14621: from per_all_assignments_f asg,
14622: per_business_groups_perf bgp
14623: where asg.business_group_id = bgp.business_group_id
14624: and asg.assignment_id = c_assignment_id
14625: and c_effective_date
14626: between effective_start_date and effective_end_date;
14884: )
14885: is
14886: select bgp.legislation_code
14887: from per_all_assignments_f asg,
14888: per_business_groups_perf bgp
14889: where asg.business_group_id = bgp.business_group_id
14890: and asg.assignment_id = c_assignment_id
14891: and c_effective_date
14892: between effective_start_date and effective_end_date;
15152: )
15153: is
15154: select bgp.legislation_code
15155: from per_all_assignments_f asg,
15156: per_business_groups_perf bgp
15157: where asg.business_group_id = bgp.business_group_id
15158: and asg.assignment_id = c_assignment_id
15159: and c_effective_date
15160: between effective_start_date and effective_end_date;
15979: where people_group_id = l_people_group_id;
15980: --
15981: cursor csr_get_legislation_code is
15982: select bus.legislation_code
15983: from per_business_groups_perf bus
15984: where bus.business_group_id = l_business_group_id;
15985: --
15986: cursor csr_get_salary is
15987: select element_entry_id
16004: -- end of fix for bug 4464072
16005: --
16006: cursor csr_grp_idsel is
16007: select bus.people_group_structure
16008: from per_business_groups_perf bus
16009: where bus.business_group_id = l_business_group_id;
16010: --
16011: cursor get_sec_date_range is
16012: select asg.effective_start_date
17690: --
17691: --
17692: cursor csr_grp_idsel is
17693: select bus.people_group_structure
17694: from per_business_groups_perf bus
17695: where bus.business_group_id = l_business_group_id;
17696: --
17697: cursor csr_scl_idsel is
17698: select plr.rule_mode id_flex_num
17696: --
17697: cursor csr_scl_idsel is
17698: select plr.rule_mode id_flex_num
17699: from pay_legislation_rules plr,
17700: per_business_groups_perf pgr
17701: where plr.legislation_code = pgr.legislation_code
17702: and pgr.business_group_id = l_business_group_id
17703: and plr.rule_type = 'S'
17704: and exists
18410: -- Added this additional check
18411: or p_scl_concat_segments <> hr_api.g_varchar2
18412: then
18413: -- gets flex num id from pay_legislation_rules and
18414: -- per_business_groups_perf
18415: --
18416: open csr_scl_idsel;
18417: fetch csr_scl_idsel into l_flex_num;
18418: --
20082: select bus.business_group_id
20083: , bus.legislation_code
20084: , per.applicant_number, per.object_version_number --3652025
20085: from per_all_people_f per
20086: , per_business_groups_perf bus
20087: where per.person_id = p_person_id
20088: and l_effective_date between per.effective_start_date
20089: and per.effective_end_date
20090: and bus.business_group_id = per.business_group_id;
20107: --
20108: --
20109: cursor csr_grp_idsel is
20110: select bus.people_group_structure
20111: from per_business_groups_perf bus
20112: where bus.business_group_id = l_business_group_id;
20113: --
20114: cursor csr_scl_idsel is
20115: select plr.rule_mode id_flex_num
20113: --
20114: cursor csr_scl_idsel is
20115: select plr.rule_mode id_flex_num
20116: from pay_legislation_rules plr,
20117: per_business_groups_perf pgr
20118: where plr.legislation_code = pgr.legislation_code
20119: and pgr.business_group_id = l_business_group_id
20120: and plr.rule_type = 'S'
20121: and exists
22419: ,person_id ,effective_end_date, application_id
22420: ,asg.assignment_status_type_id --7229710
22421: ,asg.effective_start_date -- Added for bug 9875744
22422: FROM per_all_assignments_f asg
22423: ,per_business_groups_perf bus
22424: WHERE asg.assignment_id = p_assignment_id
22425: AND bus.business_group_id = asg.business_group_id
22426: AND p_effective_date BETWEEN asg.effective_start_date
22427: AND asg.effective_end_date;