[Home] [Help]
785: return l_a_where_clause;
786: end advanced_where;
787: begin
788: --
789: hr_utility.set_location('Entering: '||l_proc,10);
790: l_effective_date:='to_date('''||to_char(p_effective_date,'DDMMYYYY')||''',''DDMMYYYY'')';
791: --
792: --for bug 2632619 conditionally switch the from clause
793: if secure='Y' then
1427:
1428:
1429:
1430: l_select_stmt_per:=l_select_stmt_per||' group by per.person_id,nvl(per.order_name,per.full_name) order by nvl(per.order_name,per.full_name)';
1431: hr_utility.set_location('r'||l_select_stmt_per,2001);
1432:
1433: select_stmt:=l_from_clause||l_where_clause;
1434: l_select_stmt_asg:='select per_asg.assignment_id '||l_from_clause;
1435: if not l_asg then
1444: l_select_stmt_asg:=l_select_stmt_asg||'and per_asg.assignment_type <> ''B''';-- Added for fix of #3286659
1445: l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=:1';
1446: l_select_stmt_per2:=l_select_stmt_per;
1447:
1448: hr_utility.set_location('r'||l_select_stmt_per,2000);
1449:
1450: while length(l_select_stmt_per2)>0 loop
1451:
1452: hr_utility.set_location(substr(l_select_stmt_per2,1,70),1);
1448: hr_utility.set_location('r'||l_select_stmt_per,2000);
1449:
1450: while length(l_select_stmt_per2)>0 loop
1451:
1452: hr_utility.set_location(substr(l_select_stmt_per2,1,70),1);
1453: l_select_stmt_per2:=substr(l_select_stmt_per2,71);
1454: end loop;
1455: --
1456:
1453: l_select_stmt_per2:=substr(l_select_stmt_per2,71);
1454: end loop;
1455: --
1456:
1457: hr_utility.set_location('hidd'||l_select_stmt_per,1000);
1458:
1459: open emp_cv for l_select_stmt_per;
1460: loop
1461: num_row:=num_row+1;
1738: end if;
1739: --
1740: if asg_rec.supervisor_assignment_id is not null then
1741:
1742: hr_utility.set_location('super_assgt_id is not null',990);
1743: hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1744: open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1745: fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1746: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1739: --
1740: if asg_rec.supervisor_assignment_id is not null then
1741:
1742: hr_utility.set_location('super_assgt_id is not null',990);
1743: hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1744: open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1745: fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1746: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1747: close csr_supervisor_assgt_number;
1742: hr_utility.set_location('super_assgt_id is not null',990);
1743: hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1744: open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1745: fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1746: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1747: close csr_supervisor_assgt_number;
1748: else
1749: out_rec.supervisor_assignment_number:=null;
1750: hr_utility.set_location('set super_assgt_id to null',992);
1746: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1747: close csr_supervisor_assgt_number;
1748: else
1749: out_rec.supervisor_assignment_number:=null;
1750: hr_utility.set_location('set super_assgt_id to null',992);
1751: end if;
1752: --
1753: if asg_rec.recruitment_activity_id is not null then
1754: open csr_rec_activity(asg_rec.recruitment_activity_id);
2007: l_other:=null;
2008: else
2009: l_other:='****';
2010: end if;
2011: hr_utility.set_location(l_proc,100);
2012: out_rec.assignment_id :=null;
2013: out_rec.asg_effective_start_date :=null;
2014: out_rec.asg_effective_end_date :=null;
2015: out_rec.recruiter :=l_other;
2116: out_rec.pgp_segment29_m :=l_other;
2117: out_rec.pgp_segment30 :=l_other;
2118: out_rec.pgp_segment30_v :=l_other;
2119: out_rec.pgp_segment30_m :=l_other;
2120: hr_utility.set_location(l_proc,110);
2121: out_rec.people_group_id :=null;
2122: out_rec.scl_segment1 :=l_other;
2123: out_rec.scl_segment1_v :=l_other;
2124: out_rec.scl_segment1_m :=l_other;
2209: out_rec.scl_segment30 :=l_other;
2210: out_rec.scl_segment30_v :=l_other;
2211: out_rec.scl_segment30_m :=l_other;
2212: out_rec.soft_coding_keyflex_id :=null;
2213: hr_utility.set_location(l_proc,120);
2214: out_rec.vacancy :=l_other;
2215: out_rec.requisition :=l_other;
2216: out_rec.salary_basis :=l_other;
2217: out_rec.pay_basis :=l_other;
2331: out_rec.ass_attribute30 :=l_other;
2332: out_rec.ass_attribute30_v :=l_other;
2333: out_rec.ass_attribute30_m :=l_other;
2334: */
2335: hr_utility.set_location(l_proc,130);
2336: out_rec.bargaining_unit_code_meaning :=l_other;
2337: out_rec.labour_union_member_flag :=l_other;
2338: out_rec.hourly_salaried_meaning :=l_other;
2339: out_rec.special_ceiling_step :=null;
2350: out_rec.collective_agreement :=l_other;
2351: out_rec.cagr_id_flex_name :=l_other;
2352: out_rec.cagr_grade :=null;
2353: out_rec.establishment :=l_other;
2354: hr_utility.set_location(l_proc,140);
2355: --
2356: end if;
2357: end if;
2358: resultset(num_row):=out_rec;
2357: end if;
2358: resultset(num_row):=out_rec;
2359: end loop;
2360:
2361: hr_utility.set_location('Leaving: '||l_proc||num_row,1000);
2362: close emp_cv;
2363: --
2364: end findquery;
2365: --
2538: l_proc varchar2(72):=g_package||'findsave';
2539: --
2540: --
2541: begin
2542: hr_utility.set_location('Entering '||l_proc,10);
2543: l_query_id:=query_id;
2544: --
2545: if business_group_id is not null then
2546: insert_number(l_query_id,'BUSINESS_GROUP_ID',business_group_id);
2820: if field5_value is not null then
2821: insert_varchar2(l_query_id,'FIELD5_VALUE',field5_value);
2822: end if;
2823: commit;
2824: hr_utility.set_location('Leaving '||l_proc,100);
2825: end findsave;
2826: --
2827: function get_varchar2(p_query_id number
2828: ,p_field varchar2)