[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
1443:
1444:
1445:
1446: 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)';
1447: hr_utility.set_location('r'||l_select_stmt_per,2001);
1448:
1449: select_stmt:=l_from_clause||l_where_clause;
1450: l_select_stmt_asg:='select per_asg.assignment_id '||l_from_clause;
1451: if not l_asg then
1460: l_select_stmt_asg:=l_select_stmt_asg||'and per_asg.assignment_type <> ''B''';-- Added for fix of #3286659
1461: l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=:1';
1462: l_select_stmt_per2:=l_select_stmt_per;
1463:
1464: hr_utility.set_location('r'||l_select_stmt_per,2000);
1465:
1466: while length(l_select_stmt_per2)>0 loop
1467:
1468: hr_utility.set_location(substr(l_select_stmt_per2,1,70),1);
1464: hr_utility.set_location('r'||l_select_stmt_per,2000);
1465:
1466: while length(l_select_stmt_per2)>0 loop
1467:
1468: hr_utility.set_location(substr(l_select_stmt_per2,1,70),1);
1469: l_select_stmt_per2:=substr(l_select_stmt_per2,71);
1470: end loop;
1471: --
1472:
1469: l_select_stmt_per2:=substr(l_select_stmt_per2,71);
1470: end loop;
1471: --
1472:
1473: hr_utility.set_location('hidd'||l_select_stmt_per,1000);
1474:
1475: open emp_cv for l_select_stmt_per;
1476: loop
1477: num_row:=num_row+1;
1754: end if;
1755: --
1756: if asg_rec.supervisor_assignment_id is not null then
1757:
1758: hr_utility.set_location('super_assgt_id is not null',990);
1759: hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1760: open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1761: fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1762: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1755: --
1756: if asg_rec.supervisor_assignment_id is not null then
1757:
1758: hr_utility.set_location('super_assgt_id is not null',990);
1759: hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1760: open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1761: fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1762: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1763: close csr_supervisor_assgt_number;
1758: hr_utility.set_location('super_assgt_id is not null',990);
1759: hr_utility.set_location('super assgt id is ' || to_char(asg_rec.supervisor_assignment_id),990);
1760: open csr_supervisor_assgt_number(asg_rec.supervisor_assignment_id,p_effective_date);
1761: fetch csr_supervisor_assgt_number into out_rec.supervisor_assignment_number;
1762: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1763: close csr_supervisor_assgt_number;
1764: else
1765: out_rec.supervisor_assignment_number:=null;
1766: hr_utility.set_location('set super_assgt_id to null',992);
1762: hr_utility.set_location('super_assgt_number is '|| out_rec.supervisor_assignment_number,991);
1763: close csr_supervisor_assgt_number;
1764: else
1765: out_rec.supervisor_assignment_number:=null;
1766: hr_utility.set_location('set super_assgt_id to null',992);
1767: end if;
1768: --
1769: if asg_rec.recruitment_activity_id is not null then
1770: open csr_rec_activity(asg_rec.recruitment_activity_id);
2023: l_other:=null;
2024: else
2025: l_other:='****';
2026: end if;
2027: hr_utility.set_location(l_proc,100);
2028: out_rec.assignment_id :=null;
2029: out_rec.asg_effective_start_date :=null;
2030: out_rec.asg_effective_end_date :=null;
2031: out_rec.recruiter :=l_other;
2132: out_rec.pgp_segment29_m :=l_other;
2133: out_rec.pgp_segment30 :=l_other;
2134: out_rec.pgp_segment30_v :=l_other;
2135: out_rec.pgp_segment30_m :=l_other;
2136: hr_utility.set_location(l_proc,110);
2137: out_rec.people_group_id :=null;
2138: out_rec.scl_segment1 :=l_other;
2139: out_rec.scl_segment1_v :=l_other;
2140: out_rec.scl_segment1_m :=l_other;
2225: out_rec.scl_segment30 :=l_other;
2226: out_rec.scl_segment30_v :=l_other;
2227: out_rec.scl_segment30_m :=l_other;
2228: out_rec.soft_coding_keyflex_id :=null;
2229: hr_utility.set_location(l_proc,120);
2230: out_rec.vacancy :=l_other;
2231: out_rec.requisition :=l_other;
2232: out_rec.salary_basis :=l_other;
2233: out_rec.pay_basis :=l_other;
2347: out_rec.ass_attribute30 :=l_other;
2348: out_rec.ass_attribute30_v :=l_other;
2349: out_rec.ass_attribute30_m :=l_other;
2350: */
2351: hr_utility.set_location(l_proc,130);
2352: out_rec.bargaining_unit_code_meaning :=l_other;
2353: out_rec.labour_union_member_flag :=l_other;
2354: out_rec.hourly_salaried_meaning :=l_other;
2355: out_rec.special_ceiling_step :=null;
2366: out_rec.collective_agreement :=l_other;
2367: out_rec.cagr_id_flex_name :=l_other;
2368: out_rec.cagr_grade :=null;
2369: out_rec.establishment :=l_other;
2370: hr_utility.set_location(l_proc,140);
2371: --
2372: end if;
2373: end if;
2374: resultset(num_row):=out_rec;
2373: end if;
2374: resultset(num_row):=out_rec;
2375: end loop;
2376:
2377: hr_utility.set_location('Leaving: '||l_proc||num_row,1000);
2378: close emp_cv;
2379: --
2380: end findquery;
2381: --
2554: l_proc varchar2(72):=g_package||'findsave';
2555: --
2556: --
2557: begin
2558: hr_utility.set_location('Entering '||l_proc,10);
2559: l_query_id:=query_id;
2560: --
2561: if business_group_id is not null then
2562: insert_number(l_query_id,'BUSINESS_GROUP_ID',business_group_id);
2836: if field5_value is not null then
2837: insert_varchar2(l_query_id,'FIELD5_VALUE',field5_value);
2838: end if;
2839: commit;
2840: hr_utility.set_location('Leaving '||l_proc,100);
2841: end findsave;
2842: --
2843: function get_varchar2(p_query_id number
2844: ,p_field varchar2)