[Home] [Help]
69: l_pt VARCHAR2(2000);
70: begin
71:
72: FND_FILE.PUT_LINE(FND_FILE.LOG,'p_reporting_date =' || p_report_date);
73: l_report_date := fnd_date.canonical_to_date(p_report_date); --to_date(p_report_date,'YYYY/MM/DD HH24:MI:SS');
74: pqh_employment_category.fetch_empl_categories(p_business_group_id
75: ,l_fr
76: ,l_ft
77: ,l_pr
93: WHERE peo.person_id = ass.person_id
94: AND NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
95: WHERE peo.person_id = pei.person_id
96: AND pei.information_type = ''PER_US_VISA_DETAILS''
97: AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
98: AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
99: AND peo.current_employee_flag = ''Y''
100: AND hl.lookup_code = job.job_information8
101: AND pqh_employment_category.identify_empl_category(ass.employment_category,
139: WHERE peo.person_id = ass.person_id
140: AND NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
141: WHERE peo.person_id = pei.person_id
142: AND pei.information_type = ''PER_US_VISA_DETAILS''
143: AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
144: AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
145: AND peo.current_employee_flag = ''Y''
146: AND hl.lookup_code = job.job_information8
147: AND pqh_employment_category.identify_empl_category(ass.employment_category,
201: WHERE peo.person_id = ass.person_id
202: AND NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
203: WHERE peo.person_id = pei.person_id
204: AND pei.information_type = ''PER_US_VISA_DETAILS''
205: AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
206: AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
207: AND peo.current_employee_flag = ''Y''
208: AND hl.lookup_code = job.job_information8
209: AND pqh_employment_category.identify_empl_category(ass.employment_category,