eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_CA_EE_EXTRACT_PKG dependencies on HR_LOOKUPS

Line 197: FROM hr_lookups hl

193: AND pppv.approved = 'Y'
194: AND pppv.change_date <= v_year_end)
195: AND EXISTS
196: (SELECT 1
197: FROM hr_lookups hl
198: WHERE pj.job_information1 = hl.lookup_code
199: AND hl.lookup_type = 'EEOG' );
200:
201: CURSOR cur_gre_naic IS

Line 466: hr_lookups hl

462: per_assignments_f paf,
463: per_person_types ppt,
464: hr_soft_coding_keyflex hsck,
465: per_jobs pj,
466: hr_lookups hl
467: where
468: ppf.person_type_id = ppt.person_type_id and
469: upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
470: decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between

Line 553: hr_lookups hl

549: per_assignments_f paf,
550: per_person_types ppt,
551: hr_soft_coding_keyflex hsck,
552: per_jobs pj,
553: hr_lookups hl
554: where
555: ppf.person_type_id = ppt.person_type_id and
556: upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
557: l_year_end between

Line 642: hr_lookups hl1,

638: hl1.meaning meaning
639: from
640: per_assignments_f paf,
641: hr_locations hl,
642: hr_lookups hl1,
643: per_people_f ppf ,
644: per_person_types ppt,
645: per_jobs pj,
646: hr_lookups hl2

Line 646: hr_lookups hl2

642: hr_lookups hl1,
643: per_people_f ppf ,
644: per_person_types ppt,
645: per_jobs pj,
646: hr_lookups hl2
647: where
648: upper(ltrim(rtrim(hl1.lookup_type)))=decode(pc,1,'CA_PROVINCE',
649: 2,'CA_CMA')and
650: upper(ltrim(rtrim(hl1.lookup_code)))

Line 684: hr_lookups hl

680: cursor cur_cma_notfound is
681: select
682: ltrim(rtrim(hl.meaning)) meaning
683: from
684: hr_lookups hl
685: where
686: hl.lookup_type='CA_CMA' and
687: upper(ltrim(rtrim(hl.meaning))) in
688: ('CALGARY','EDMONTON','HALIFAX','MONTREAL','REGINA','TORONTO',

Line 704: hr_lookups hl

700: cursor cur_province_notfound is
701: select
702: ltrim(rtrim(hl.meaning)) meaning
703: from
704: hr_lookups hl
705: where
706: hl.lookup_type='CA_PROVINCE'
707: minus
708: select

Line 722: hr_lookups hl,

718: select
719: count(distinct paf.person_id) count_naic_person,
720: hl.lookup_code lcode
721: from
722: hr_lookups hl,
723: hr_soft_coding_keyflex hsck ,
724: hr_organization_information hoi,
725: per_assignments_f paf,
726: per_people_f ppf,

Line 729: hr_lookups hl1

725: per_assignments_f paf,
726: per_people_f ppf,
727: per_person_types ppt,
728: per_jobs pj,
729: hr_lookups hl1
730: where
731: (
732: (
733: p_naic_code is not null and

Line 809: hr_lookups

805: cursor cur_hl_meaning(lc VARCHAR2) is
806: select
807: meaning
808: from
809: hr_lookups
810: where
811: lookup_type='NAIC' and
812: lookup_code=lc;
813:

Line 814: v_meaning hr_lookups.meaning%TYPE;

810: where
811: lookup_type='NAIC' and
812: lookup_code=lc;
813:
814: v_meaning hr_lookups.meaning%TYPE;
815: dummy varchar2(1);
816:
817: cursor cur_legislation_info(p_lookup_type varchar2) is
818: select

Line 1151: v_naic_code hr_lookups.lookup_code%TYPE;

1147: -- to_number(pert.segment3) >= to_number(v_leg_info)) and
1148: pert.context = 'FORM12' ;
1149:
1150: v_tot_number_emp per_ca_ee_report_lines.segment3%TYPE;
1151: v_naic_code hr_lookups.lookup_code%TYPE;
1152: v_max_naic_flag varchar2(1);
1153:
1154: cursor cur_min_max is select
1155: max(max_salary) max_salary,

Line 1169: hr_lookups hl,

1165: min_salary,
1166: hl.meaning meaning,
1167: substr(paf.employment_category,1,2) employment_category
1168: from
1169: hr_lookups hl,
1170: per_jobs pj,
1171: per_pay_proposals_v2 pppv,
1172: per_people_f ppf,
1173: per_assignments_f paf,

Line 1247: hr_lookups hl,

1243: min_salary,
1244: hl.meaning meaning,
1245: 'FR' employment_category
1246: from
1247: hr_lookups hl,
1248: per_jobs pj,
1249: per_pay_proposals_v2 pppv,
1250: per_people_f ppf,
1251: per_assignments_f paf,

Line 1321: v_meaning hr_lookups.meaning%TYPE;

1317: order by meaning,employment_category;
1318:
1319: v_max_salary number;
1320: v_min_salary number;
1321: v_meaning hr_lookups.meaning%TYPE;
1322: v_employment_category per_assignments_f.employment_category%TYPE;
1323:
1324: j_flag varchar2(1) := 'F';
1325:

Line 1345: hr_lookups hl,

1341: cursor cur_count_total(i_range number)is select
1342: count(distinct paf.person_id) count_total,
1343: ppf.sex
1344: from
1345: hr_lookups hl,
1346: per_jobs pj,
1347: per_assignments_f paf,
1348: per_people_f ppf,
1349: per_pay_proposals_v2 pppv,

Line 1436: prev_naic_code hr_lookups.lookup_code%TYPE;

1432: v_sex per_people_f.sex%TYPE;
1433: prev_employment_category per_assignments_f.employment_category%TYPE;
1434: prev_sex per_people_f.sex%TYPE;
1435: prev_j number := 0;
1436: prev_naic_code hr_lookups.lookup_code%TYPE;
1437: prev_meaning hr_lookups.meaning%TYPE;
1438:
1439: cursor cur_count(range number,
1440: desig number) is

Line 1437: prev_meaning hr_lookups.meaning%TYPE;

1433: prev_employment_category per_assignments_f.employment_category%TYPE;
1434: prev_sex per_people_f.sex%TYPE;
1435: prev_j number := 0;
1436: prev_naic_code hr_lookups.lookup_code%TYPE;
1437: prev_meaning hr_lookups.meaning%TYPE;
1438:
1439: cursor cur_count(range number,
1440: desig number) is
1441: select

Line 1445: hr_lookups hl,

1441: select
1442: count(distinct paf.person_id) count,
1443: ppf.sex
1444: from
1445: hr_lookups hl,
1446: per_jobs pj,
1447: per_assignments_f paf,
1448: per_people_f ppf,
1449: per_pay_proposals_v2 pppv,

Line 2150: v_naic_code hr_lookups.lookup_code%TYPE;

2146: -- to_number(pert.segment3) >= to_number(v_leg_info)) and
2147: pert.context = 'FORM12' ;
2148:
2149: v_tot_number_emp per_ca_ee_report_lines.segment3%TYPE;
2150: v_naic_code hr_lookups.lookup_code%TYPE;
2151: v_max_naic_flag varchar2(1);
2152:
2153: cursor cur_min_max(cma_province_count number) is
2154: select

Line 2171: hr_lookups hl,

2167: substr(paf.employment_category,1,2) employment_category,
2168: decode(cma_province_count,1,hl1.region_1
2169: ,2,hl1.region_2) cma_province
2170: from
2171: hr_lookups hl,
2172: per_jobs pj,
2173: per_pay_proposals_v2 pppv,
2174: per_people_f ppf,
2175: per_assignments_f paf,

Line 2179: hr_lookups hl2,

2175: per_assignments_f paf,
2176: hr_locations hl1,
2177: per_person_types ppt,
2178: per_ca_ee_report_lines pert,
2179: hr_lookups hl2,
2180: hr_soft_coding_keyflex hsck,
2181: per_pay_bases ppb
2182: where
2183: hl.lookup_type='EEOG' and

Line 2268: hr_lookups hl,

2264: 'FR' employment_category,
2265: decode(cma_province_count,1,hl1.region_1
2266: ,2,hl1.region_2) cma_province
2267: from
2268: hr_lookups hl,
2269: per_jobs pj,
2270: per_pay_proposals_v2 pppv,
2271: per_people_f ppf,
2272: per_assignments_f paf,

Line 2276: hr_lookups hl2,

2272: per_assignments_f paf,
2273: hr_locations hl1,
2274: per_person_types ppt,
2275: per_ca_ee_report_lines pert,
2276: hr_lookups hl2,
2277: hr_soft_coding_keyflex hsck,
2278: per_pay_bases ppb
2279: where
2280: hl.lookup_type='EEOG' and

Line 2359: v_meaning hr_lookups.meaning%TYPE;

2355: order by meaning,employment_category,cma_province;
2356:
2357: v_max_salary number;
2358: v_min_salary number;
2359: v_meaning hr_lookups.meaning%TYPE;
2360: v_employment_category per_assignments_f.employment_category%TYPE;
2361: v_cma_province hr_locations.region_1%TYPE;
2362:
2363: j_flag varchar2(1) := 'F';

Line 2369: hr_lookups

2365: cursor cur_meaning(cp number) is
2366: select
2367: meaning
2368: from
2369: hr_lookups
2370: where
2371: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
2372: ,2,'CA_CMA') and
2373: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));

Line 2375: v_meaning1 hr_lookups.meaning%TYPE;

2371: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
2372: ,2,'CA_CMA') and
2373: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
2374:
2375: v_meaning1 hr_lookups.meaning%TYPE;
2376: v_range number;
2377: v_q1_min number;
2378: v_q1_max number;
2379: v_q2_min number;

Line 2397: hr_lookups hl,

2393: select
2394: count(distinct paf.person_id) count_total,
2395: ppf.sex --sex
2396: from
2397: hr_lookups hl,
2398: per_jobs pj,
2399: per_assignments_f paf,
2400: per_people_f ppf,
2401: per_pay_proposals_v2 pppv,

Line 2494: prev_naic_code hr_lookups.lookup_code%TYPE;

2490: prev_employment_category per_assignments_f.employment_category%TYPE;
2491: prev_sex per_people_f.sex%TYPE;
2492: prev_j number := 0;
2493: prev_cma_province hr_locations.region_1%TYPE;
2494: prev_naic_code hr_lookups.lookup_code%TYPE;
2495: prev_meaning hr_lookups.meaning%TYPE;
2496:
2497: cursor cur_count(range number,
2498: desig number,

Line 2495: prev_meaning hr_lookups.meaning%TYPE;

2491: prev_sex per_people_f.sex%TYPE;
2492: prev_j number := 0;
2493: prev_cma_province hr_locations.region_1%TYPE;
2494: prev_naic_code hr_lookups.lookup_code%TYPE;
2495: prev_meaning hr_lookups.meaning%TYPE;
2496:
2497: cursor cur_count(range number,
2498: desig number,
2499: i_y number) is

Line 2504: hr_lookups hl,

2500: select
2501: count(distinct paf.person_id) count,
2502: ppf.sex --sex
2503: from
2504: hr_lookups hl,
2505: per_jobs pj,
2506: per_assignments_f paf,
2507: per_people_f ppf,
2508: per_pay_proposals_v2 pppv,

Line 3211: v_naic_code hr_lookups.lookup_code%TYPE;

3207: where
3208: pert.request_id = p_request_id and
3209: pert.context = 'FORM12';
3210:
3211: v_naic_code hr_lookups.lookup_code%TYPE;
3212:
3213: v_min_range number;
3214: v_max_range number;
3215: v_fr_min_range number;

Line 3240: hr_lookups hl2,

3236: per_pay_proposals_v2 pppv,
3237: per_person_types ppt,
3238: hr_locations hl1,
3239: per_ca_ee_report_lines pert,
3240: hr_lookups hl2,
3241: hr_soft_coding_keyflex hsck,
3242: per_pay_bases ppb
3243: where
3244: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 3320: hr_lookups hl2,

3316: per_pay_proposals_v2 pppv,
3317: per_person_types ppt,
3318: hr_locations hl1,
3319: per_ca_ee_report_lines pert,
3320: hr_lookups hl2,
3321: hr_soft_coding_keyflex hsck,
3322: per_pay_bases ppb
3323: where
3324: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 3395: prev_naic_code hr_lookups.lookup_code%TYPE;

3391: prev_employment_category per_assignments_f.employment_category%TYPE;
3392: prev_sex per_people_f.sex%TYPE;
3393: prev_x number := 0;
3394: v_cma_province hr_locations.region_1%TYPE;
3395: prev_naic_code hr_lookups.lookup_code%TYPE;
3396:
3397:
3398: cursor cur_meaning(cp number) is
3399: select

Line 3400: meaning from hr_lookups

3396:
3397:
3398: cursor cur_meaning(cp number) is
3399: select
3400: meaning from hr_lookups
3401: where
3402: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
3403: ,2,'CA_CMA') and
3404: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));

Line 3406: v_meaning hr_lookups.meaning%TYPE;

3402: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
3403: ,2,'CA_CMA') and
3404: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
3405:
3406: v_meaning hr_lookups.meaning%TYPE;
3407: prev_meaning hr_lookups.meaning%TYPE;
3408:
3409: cursor cur_count(cma_province_ct number,
3410: count number,

Line 3407: prev_meaning hr_lookups.meaning%TYPE;

3403: ,2,'CA_CMA') and
3404: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
3405:
3406: v_meaning hr_lookups.meaning%TYPE;
3407: prev_meaning hr_lookups.meaning%TYPE;
3408:
3409: cursor cur_count(cma_province_ct number,
3410: count number,
3411: desig number) is

Line 3432: hr_lookups hl2,

3428: per_pay_proposals_v2 pppv,
3429: per_person_types ppt,
3430: hr_locations hl1,
3431: per_ca_ee_report_lines pert,
3432: hr_lookups hl2,
3433: hr_soft_coding_keyflex hsck,
3434: per_pay_bases ppb
3435: where
3436: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 3516: hr_lookups hl2,

3512: per_pay_proposals_v2 pppv,
3513: per_person_types ppt,
3514: hr_locations hl1,
3515: per_ca_ee_report_lines pert,
3516: hr_lookups hl2,
3517: hr_soft_coding_keyflex hsck,
3518: per_pay_bases ppb
3519: where
3520: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 4336: v_naic_code hr_lookups.lookup_code%TYPE;

4332: where
4333: pert.request_id = p_request_id and
4334: pert.context = 'FORM12';
4335:
4336: v_naic_code hr_lookups.lookup_code%TYPE;
4337:
4338: cursor cur_hired_total is
4339: select
4340: count(distinct count_total) count_total,

Line 4354: hr_lookups hl,

4350: ppf.sex sex,
4351: substr(employment_category,1,2) employment_category,
4352: hl1.region_1 province
4353: from
4354: hr_lookups hl,
4355: per_jobs pj,
4356: per_assignments_f paf,
4357: per_people_f ppf,
4358: per_person_types ppt,

Line 4361: hr_lookups hl2,

4357: per_people_f ppf,
4358: per_person_types ppt,
4359: hr_locations hl1,
4360: per_ca_ee_report_lines pert,
4361: hr_lookups hl2,
4362: hr_soft_coding_keyflex hsck
4363: where
4364: hl.lookup_type='EEOG' and
4365: upper(ltrim(rtrim(hl.lookup_code)))

Line 4434: hr_lookups hl,

4430: ppf.sex sex,
4431: 'FR' employment_category,
4432: hl1.region_1 province
4433: from
4434: hr_lookups hl,
4435: per_jobs pj,
4436: per_assignments_f paf,
4437: per_people_f ppf,
4438: per_person_types ppt,

Line 4441: hr_lookups hl2,

4437: per_people_f ppf,
4438: per_person_types ppt,
4439: hr_locations hl1,
4440: per_ca_ee_report_lines pert,
4441: hr_lookups hl2,
4442: hr_soft_coding_keyflex hsck
4443: where
4444: hl.lookup_type='EEOG' and
4445: upper(ltrim(rtrim(hl.lookup_code)))

Line 4511: v_meaning hr_lookups.meaning%TYPE;

4507: group by province,meaning,employment_category,sex
4508: order by province,meaning,employment_category,sex;
4509:
4510: v_count number(10);
4511: v_meaning hr_lookups.meaning%TYPE;
4512: v_employment_category per_assignments_f.employment_category%TYPE;
4513: v_sex per_people_f.sex%TYPE;
4514: v_province hr_locations.region_1%TYPE;
4515: prev_meaning hr_lookups.meaning%TYPE := 'test';

Line 4515: prev_meaning hr_lookups.meaning%TYPE := 'test';

4511: v_meaning hr_lookups.meaning%TYPE;
4512: v_employment_category per_assignments_f.employment_category%TYPE;
4513: v_sex per_people_f.sex%TYPE;
4514: v_province hr_locations.region_1%TYPE;
4515: prev_meaning hr_lookups.meaning%TYPE := 'test';
4516: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
4517: prev_sex per_people_f.sex%TYPE := 'test';
4518: prev_naic_code hr_lookups.lookup_code%TYPE;
4519:

Line 4518: prev_naic_code hr_lookups.lookup_code%TYPE;

4514: v_province hr_locations.region_1%TYPE;
4515: prev_meaning hr_lookups.meaning%TYPE := 'test';
4516: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
4517: prev_sex per_people_f.sex%TYPE := 'test';
4518: prev_naic_code hr_lookups.lookup_code%TYPE;
4519:
4520: cursor cur_meaning is
4521: select
4522: meaning

Line 4524: hr_lookups

4520: cursor cur_meaning is
4521: select
4522: meaning
4523: from
4524: hr_lookups
4525: where
4526: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
4527: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
4528:

Line 4529: v_province_name hr_lookups.meaning%TYPE;

4525: where
4526: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
4527: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
4528:
4529: v_province_name hr_lookups.meaning%TYPE;
4530: prev_province_name hr_lookups.meaning%TYPE;
4531:
4532: cursor cur_hired(desig NUMBER) is
4533: select

Line 4530: prev_province_name hr_lookups.meaning%TYPE;

4526: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
4527: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
4528:
4529: v_province_name hr_lookups.meaning%TYPE;
4530: prev_province_name hr_lookups.meaning%TYPE;
4531:
4532: cursor cur_hired(desig NUMBER) is
4533: select
4534: count(distinct person_id) count,

Line 4548: hr_lookups hl,

4544: substr(paf.employment_category,1,2) employment_category,
4545: ppf.sex sex,
4546: hl1.region_1 province
4547: from
4548: hr_lookups hl,
4549: per_jobs pj,
4550: per_assignments_f paf,
4551: per_people_f ppf,
4552: per_person_types ppt,

Line 4555: hr_lookups hl2,

4551: per_people_f ppf,
4552: per_person_types ppt,
4553: hr_locations hl1,
4554: per_ca_ee_report_lines pert,
4555: hr_lookups hl2,
4556: hr_soft_coding_keyflex hsck
4557: where
4558: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4559: upper(ltrim(rtrim(hl.lookup_code)))=

Line 4631: hr_lookups hl,

4627: 'FR' employment_category,
4628: ppf.sex sex,
4629: hl1.region_1 province
4630: from
4631: hr_lookups hl,
4632: per_jobs pj,
4633: per_assignments_f paf,
4634: per_people_f ppf,
4635: per_person_types ppt,

Line 4638: hr_lookups hl2,

4634: per_people_f ppf,
4635: per_person_types ppt,
4636: hr_locations hl1,
4637: per_ca_ee_report_lines pert,
4638: hr_lookups hl2,
4639: hr_soft_coding_keyflex hsck
4640: where
4641: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4642: upper(ltrim(rtrim(hl.lookup_code)))

Line 4714: hr_lookups

4710: cursor cur_eeog is
4711: select
4712: meaning
4713: from
4714: hr_lookups
4715: where
4716: lookup_type='EEOG';
4717:
4718: cursor cur_notfound(p_emp_cat number) is

Line 5126: v_naic_code hr_lookups.lookup_code%TYPE;

5122: where
5123: pert.request_id = p_request_id and
5124: pert.context = 'FORM12';
5125:
5126: v_naic_code hr_lookups.lookup_code%TYPE;
5127:
5128: cursor cur_promoted_total is
5129: select
5130: count(distinct count_total) count_total,

Line 5144: hr_lookups hl,

5140: ppf.sex sex,
5141: substr(paf.employment_category,1,2) employment_category,
5142: hl1.region_1 province
5143: from
5144: hr_lookups hl,
5145: per_jobs pj,
5146: per_assignments_f paf,
5147: per_people_f ppf,
5148: per_person_types ppt,

Line 5151: hr_lookups hl2,

5147: per_people_f ppf,
5148: per_person_types ppt,
5149: hr_locations hl1,
5150: per_ca_ee_report_lines pert,
5151: hr_lookups hl2,
5152: hr_soft_coding_keyflex hsck
5153: where
5154: hl.lookup_type='EEOG' and
5155: upper(ltrim(rtrim(hl.lookup_code)))

Line 5215: from hr_lookups

5211: l_year_end and
5212: pppv.proposal_reason =
5213: (
5214: select lookup_code
5215: from hr_lookups
5216: where lookup_type = 'PROPOSAL_REASON' and
5217: upper(meaning) = 'PROMOTION'
5218: )
5219: )

Line 5228: hr_lookups hl,

5224: ppf.sex sex,
5225: 'FR' employment_category,
5226: hl1.region_1 province
5227: from
5228: hr_lookups hl,
5229: per_jobs pj,
5230: per_assignments_f paf,
5231: per_people_f ppf,
5232: per_person_types ppt,

Line 5235: hr_lookups hl2,

5231: per_people_f ppf,
5232: per_person_types ppt,
5233: hr_locations hl1,
5234: per_ca_ee_report_lines pert,
5235: hr_lookups hl2,
5236: hr_soft_coding_keyflex hsck
5237: where
5238: hl.lookup_type='EEOG' and
5239: upper(ltrim(rtrim(hl.lookup_code)))

Line 5299: from hr_lookups

5295: l_year_end and
5296: pppv.proposal_reason =
5297: (
5298: select lookup_code
5299: from hr_lookups
5300: where lookup_type = 'PROPOSAL_REASON' and
5301: upper(meaning) = 'PROMOTION'
5302: )
5303: )

Line 5309: v_meaning hr_lookups.meaning%TYPE;

5305: group by province,meaning,employment_category,sex
5306: order by province,meaning,employment_category,sex;
5307:
5308: v_count number(10);
5309: v_meaning hr_lookups.meaning%TYPE;
5310: v_employment_category per_assignments_f.employment_category%TYPE;
5311: v_sex per_people_f.sex%TYPE;
5312: v_province hr_locations.region_1%TYPE;
5313: prev_meaning hr_lookups.meaning%TYPE := 'test';

Line 5313: prev_meaning hr_lookups.meaning%TYPE := 'test';

5309: v_meaning hr_lookups.meaning%TYPE;
5310: v_employment_category per_assignments_f.employment_category%TYPE;
5311: v_sex per_people_f.sex%TYPE;
5312: v_province hr_locations.region_1%TYPE;
5313: prev_meaning hr_lookups.meaning%TYPE := 'test';
5314: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
5315: prev_sex per_people_f.sex%TYPE := 'test';
5316: prev_naic_code hr_lookups.lookup_code%TYPE;
5317:

Line 5316: prev_naic_code hr_lookups.lookup_code%TYPE;

5312: v_province hr_locations.region_1%TYPE;
5313: prev_meaning hr_lookups.meaning%TYPE := 'test';
5314: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
5315: prev_sex per_people_f.sex%TYPE := 'test';
5316: prev_naic_code hr_lookups.lookup_code%TYPE;
5317:
5318: cursor cur_meaning is select
5319: meaning
5320: from

Line 5321: hr_lookups

5317:
5318: cursor cur_meaning is select
5319: meaning
5320: from
5321: hr_lookups
5322: where
5323: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
5324: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
5325:

Line 5326: v_province_name hr_lookups.meaning%TYPE;

5322: where
5323: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
5324: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
5325:
5326: v_province_name hr_lookups.meaning%TYPE;
5327: prev_province_name hr_lookups.meaning%TYPE;
5328:
5329: cursor cur_promoted(desig NUMBER) is
5330: select

Line 5327: prev_province_name hr_lookups.meaning%TYPE;

5323: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
5324: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
5325:
5326: v_province_name hr_lookups.meaning%TYPE;
5327: prev_province_name hr_lookups.meaning%TYPE;
5328:
5329: cursor cur_promoted(desig NUMBER) is
5330: select
5331: count(distinct person_id) count,

Line 5345: hr_lookups hl,

5341: substr(paf.employment_category,1,2) employment_category,
5342: ppf.sex sex,
5343: hl1.region_1 province
5344: from
5345: hr_lookups hl,
5346: per_jobs pj,
5347: per_assignments_f paf,
5348: per_people_f ppf,
5349: per_person_types ppt,

Line 5352: hr_lookups hl2,

5348: per_people_f ppf,
5349: per_person_types ppt,
5350: hr_locations hl1,
5351: per_ca_ee_report_lines pert,
5352: hr_lookups hl2,
5353: hr_soft_coding_keyflex hsck
5354: where
5355: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5356: upper(ltrim(rtrim(hl.lookup_code)))=upper(ltrim(ltrim(pj.job_information1))) and

Line 5418: from hr_lookups

5414: pppv.approved = 'Y' and
5415: pppv.proposal_reason =
5416: (
5417: select lookup_code
5418: from hr_lookups
5419: where lookup_type = 'PROPOSAL_REASON' and
5420: upper(meaning) = 'PROMOTION'
5421: )
5422: )

Line 5431: hr_lookups hl,

5427: 'FR' employment_category,
5428: ppf.sex sex,
5429: hl1.region_1 province
5430: from
5431: hr_lookups hl,
5432: per_jobs pj,
5433: per_assignments_f paf,
5434: per_people_f ppf,
5435: per_person_types ppt,

Line 5438: hr_lookups hl2,

5434: per_people_f ppf,
5435: per_person_types ppt,
5436: hr_locations hl1,
5437: per_ca_ee_report_lines pert,
5438: hr_lookups hl2,
5439: hr_soft_coding_keyflex hsck
5440: where
5441: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5442: upper(ltrim(rtrim(hl.lookup_code)))=upper(ltrim(ltrim(pj.job_information1))) and

Line 5504: from hr_lookups

5500: l_year_end and
5501: pppv.proposal_reason =
5502: (
5503: select lookup_code
5504: from hr_lookups
5505: where lookup_type = 'PROPOSAL_REASON' and
5506: upper(meaning) = 'PROMOTION'
5507: )
5508: )

Line 5517: hr_lookups

5513: cursor cur_eeog is
5514: select
5515: meaning
5516: from
5517: hr_lookups
5518: where
5519: lookup_type='EEOG';
5520:
5521: cursor cur_notfound(p_emp_cat number) is

Line 5584: hr_lookups hl,

5580: ppf.sex sex,
5581: substr(paf.employment_category,1,2) employment_category,
5582: hl1.region_1 province
5583: from
5584: hr_lookups hl,
5585: per_jobs pj,
5586: per_assignments_f paf,
5587: per_people_f ppf,
5588: per_person_types ppt,

Line 5591: hr_lookups hl2,

5587: per_people_f ppf,
5588: per_person_types ppt,
5589: hr_locations hl1,
5590: per_ca_ee_report_lines pert,
5591: hr_lookups hl2,
5592: hr_soft_coding_keyflex hsck,
5593: per_pay_proposals_v2 pppv
5594: where
5595: hl.lookup_type='EEOG' and

Line 5651: from hr_lookups

5647: l_year_end and
5648: pppv.proposal_reason =
5649: (
5650: select lookup_code
5651: from hr_lookups
5652: where lookup_type = 'PROPOSAL_REASON' and
5653: upper(meaning) = 'PROMOTION'
5654: )
5655: union all

Line 5662: hr_lookups hl,

5658: ppf.sex sex,
5659: 'FR' employment_category,
5660: hl1.region_1 province
5661: from
5662: hr_lookups hl,
5663: per_jobs pj,
5664: per_assignments_f paf,
5665: per_people_f ppf,
5666: per_person_types ppt,

Line 5669: hr_lookups hl2,

5665: per_people_f ppf,
5666: per_person_types ppt,
5667: hr_locations hl1,
5668: per_ca_ee_report_lines pert,
5669: hr_lookups hl2,
5670: hr_soft_coding_keyflex hsck,
5671: per_pay_proposals_v2 pppv
5672: where
5673: hl.lookup_type='EEOG' and

Line 5729: from hr_lookups

5725: l_year_end and
5726: pppv.proposal_reason =
5727: (
5728: select lookup_code
5729: from hr_lookups
5730: where lookup_type = 'PROPOSAL_REASON' and
5731: upper(meaning) = 'PROMOTION'
5732: )
5733: )

Line 5752: hr_lookups hl,

5748: ppf.sex sex,
5749: substr(paf.employment_category,1,2) employment_category,
5750: hl1.region_1 province
5751: from
5752: hr_lookups hl,
5753: per_jobs pj,
5754: per_assignments_f paf,
5755: per_people_f ppf,
5756: per_person_types ppt,

Line 5759: hr_lookups hl2,

5755: per_people_f ppf,
5756: per_person_types ppt,
5757: hr_locations hl1,
5758: per_ca_ee_report_lines pert,
5759: hr_lookups hl2,
5760: hr_soft_coding_keyflex hsck,
5761: per_pay_proposals_v2 pppv
5762: where
5763: hl.lookup_type='EEOG' and

Line 5819: from hr_lookups

5815: l_year_end and
5816: pppv.proposal_reason =
5817: (
5818: select lookup_code
5819: from hr_lookups
5820: where lookup_type = 'PROPOSAL_REASON' and
5821: upper(meaning) = 'PROMOTION'
5822: )
5823: union all

Line 5830: hr_lookups hl,

5826: ppf.sex sex,
5827: 'FR' employment_category,
5828: hl1.region_1 province
5829: from
5830: hr_lookups hl,
5831: per_jobs pj,
5832: per_assignments_f paf,
5833: per_people_f ppf,
5834: per_person_types ppt,

Line 5837: hr_lookups hl2,

5833: per_people_f ppf,
5834: per_person_types ppt,
5835: hr_locations hl1,
5836: per_ca_ee_report_lines pert,
5837: hr_lookups hl2,
5838: hr_soft_coding_keyflex hsck,
5839: per_pay_proposals_v2 pppv
5840: where
5841: hl.lookup_type='EEOG' and

Line 5897: from hr_lookups

5893: l_year_end and
5894: pppv.proposal_reason =
5895: (
5896: select lookup_code
5897: from hr_lookups
5898: where lookup_type = 'PROPOSAL_REASON' and
5899: upper(meaning) = 'PROMOTION'
5900: )
5901: )

Line 5919: hr_lookups hl,

5915: substr(paf.employment_category,1,2) employment_category,
5916: ppf.sex sex,
5917: hl1.region_1 province
5918: from
5919: hr_lookups hl,
5920: per_jobs pj,
5921: per_assignments_f paf,
5922: per_people_f ppf,
5923: per_person_types ppt,

Line 5926: hr_lookups hl2,

5922: per_people_f ppf,
5923: per_person_types ppt,
5924: hr_locations hl1,
5925: per_ca_ee_report_lines pert,
5926: hr_lookups hl2,
5927: hr_soft_coding_keyflex hsck,
5928: per_pay_proposals_v2 pppv
5929: where
5930: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and

Line 5989: from hr_lookups

5985: pppv.approved = 'Y' and
5986: pppv.proposal_reason =
5987: (
5988: select lookup_code
5989: from hr_lookups
5990: where lookup_type = 'PROPOSAL_REASON' and
5991: upper(meaning) = 'PROMOTION'
5992: )
5993: union all

Line 6000: hr_lookups hl,

5996: 'FR' employment_category,
5997: ppf.sex sex,
5998: hl1.region_1 province
5999: from
6000: hr_lookups hl,
6001: per_jobs pj,
6002: per_assignments_f paf,
6003: per_people_f ppf,
6004: per_person_types ppt,

Line 6007: hr_lookups hl2,

6003: per_people_f ppf,
6004: per_person_types ppt,
6005: hr_locations hl1,
6006: per_ca_ee_report_lines pert,
6007: hr_lookups hl2,
6008: hr_soft_coding_keyflex hsck,
6009: per_pay_proposals_v2 pppv
6010: where
6011: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and

Line 6070: from hr_lookups

6066: l_year_end and
6067: pppv.proposal_reason =
6068: (
6069: select lookup_code
6070: from hr_lookups
6071: where lookup_type = 'PROPOSAL_REASON' and
6072: upper(meaning) = 'PROMOTION'
6073: )
6074: )

Line 6812: v_naic_code hr_lookups.lookup_code%TYPE;

6808: from per_ca_ee_report_lines pert
6809: where pert.request_id = p_request_id
6810: and pert.context = 'FORM12';
6811:
6812: v_naic_code hr_lookups.lookup_code%TYPE;
6813:
6814: cursor cur_jobs is
6815: select job_id,
6816: meaning

Line 6818: hr_lookups

6814: cursor cur_jobs is
6815: select job_id,
6816: meaning
6817: from per_jobs,
6818: hr_lookups
6819: where lookup_type = 'EEOG'
6820: and upper(ltrim(rtrim(lookup_code)))
6821: =upper(ltrim(rtrim(job_information1)))
6822: and upper(ltrim(rtrim(job_information_category))) = 'CA'

Line 6857: hr_lookups hl2,

6853: per_assignments_f paf,
6854: per_people_f ppf,
6855: hr_locations hl1,
6856: per_ca_ee_report_lines pert,
6857: hr_lookups hl2,
6858: hr_soft_coding_keyflex hsck
6859: where job_exists(paf.job_id) is not null
6860: and paf.primary_flag = 'Y'
6861: and paf.assignment_id =

Line 6941: hr_lookups hl2,

6937: per_assignments_f paf,
6938: per_people_f ppf,
6939: hr_locations hl1,
6940: per_ca_ee_report_lines pert,
6941: hr_lookups hl2,
6942: hr_soft_coding_keyflex hsck
6943: where
6944: job_exists(paf.job_id) is not null
6945: and paf.primary_flag = 'Y'

Line 7016: v_meaning hr_lookups.meaning%TYPE;

7012: group by province,meaning,employment_category,sex
7013: order by province,meaning,employment_category,sex;
7014:
7015: v_count number(10);
7016: v_meaning hr_lookups.meaning%TYPE;
7017: v_employment_category per_assignments_f.employment_category%TYPE;
7018: v_sex per_people_f.sex%TYPE;
7019: v_province hr_locations.region_1%TYPE;
7020: prev_meaning hr_lookups.meaning%TYPE := 'test';

Line 7020: prev_meaning hr_lookups.meaning%TYPE := 'test';

7016: v_meaning hr_lookups.meaning%TYPE;
7017: v_employment_category per_assignments_f.employment_category%TYPE;
7018: v_sex per_people_f.sex%TYPE;
7019: v_province hr_locations.region_1%TYPE;
7020: prev_meaning hr_lookups.meaning%TYPE := 'test';
7021: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
7022: prev_sex per_people_f.sex%TYPE := 'test';
7023: prev_naic_code hr_lookups.lookup_code%TYPE;
7024:

Line 7023: prev_naic_code hr_lookups.lookup_code%TYPE;

7019: v_province hr_locations.region_1%TYPE;
7020: prev_meaning hr_lookups.meaning%TYPE := 'test';
7021: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
7022: prev_sex per_people_f.sex%TYPE := 'test';
7023: prev_naic_code hr_lookups.lookup_code%TYPE;
7024:
7025: cursor cur_meaning is
7026: select meaning
7027: from hr_lookups

Line 7027: from hr_lookups

7023: prev_naic_code hr_lookups.lookup_code%TYPE;
7024:
7025: cursor cur_meaning is
7026: select meaning
7027: from hr_lookups
7028: where upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE'
7029: and upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
7030:
7031: v_province_name hr_lookups.meaning%TYPE;

Line 7031: v_province_name hr_lookups.meaning%TYPE;

7027: from hr_lookups
7028: where upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE'
7029: and upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
7030:
7031: v_province_name hr_lookups.meaning%TYPE;
7032: prev_province_name hr_lookups.meaning%TYPE;
7033:
7034: cursor cur_eeog is
7035: select meaning

Line 7032: prev_province_name hr_lookups.meaning%TYPE;

7028: where upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE'
7029: and upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
7030:
7031: v_province_name hr_lookups.meaning%TYPE;
7032: prev_province_name hr_lookups.meaning%TYPE;
7033:
7034: cursor cur_eeog is
7035: select meaning
7036: from hr_lookups

Line 7036: from hr_lookups

7032: prev_province_name hr_lookups.meaning%TYPE;
7033:
7034: cursor cur_eeog is
7035: select meaning
7036: from hr_lookups
7037: where lookup_type='EEOG';
7038:
7039: cursor cur_notfound(p_emp_cat number) is
7040: select

Line 7111: hr_lookups hl2,

7107: per_assignments_f paf,
7108: per_people_f ppf,
7109: hr_locations hl1,
7110: per_ca_ee_report_lines pert,
7111: hr_lookups hl2,
7112: hr_soft_coding_keyflex hsck
7113: where
7114: job_exists(paf.job_id) is not null
7115: and paf.primary_flag = 'Y'

Line 7195: hr_lookups hl2,

7191: per_assignments_f paf,
7192: per_people_f ppf,
7193: hr_locations hl1,
7194: per_ca_ee_report_lines pert,
7195: hr_lookups hl2,
7196: hr_soft_coding_keyflex hsck
7197: where
7198: job_exists(paf.job_id) is not null
7199: and paf.primary_flag = 'Y'

Line 8011: v_naic_code hr_lookups.lookup_code%TYPE;

8007: where
8008: pert.request_id = p_request_id and
8009: pert.context = 'FORM12';
8010:
8011: v_naic_code hr_lookups.lookup_code%TYPE;
8012:
8013: cursor cur_terminated_total is
8014: select
8015: count(distinct count_total) count_total,

Line 8029: hr_lookups hl,

8025: ppf.sex sex,
8026: substr(paf.employment_category,1,2) employment_category,
8027: hl1.region_1 region_1
8028: from
8029: hr_lookups hl,
8030: per_jobs pj,
8031: per_assignments_f paf,
8032: per_people_f ppf,
8033: per_person_types ppt,

Line 8098: hr_lookups hl,

8094: ppf.sex sex,
8095: 'FR' employment_category,
8096: hl1.region_1 region_1
8097: from
8098: hr_lookups hl,
8099: per_jobs pj,
8100: per_assignments_f paf,
8101: per_people_f ppf,
8102: per_person_types ppt,

Line 8164: v_meaning hr_lookups.meaning%TYPE;

8160: group by region_1,meaning,employment_category,sex
8161: order by region_1,meaning,employment_category,sex;
8162:
8163: v_count number(10);
8164: v_meaning hr_lookups.meaning%TYPE;
8165: v_employment_category per_assignments_f.employment_category%TYPE;
8166: v_sex per_people_f.sex%TYPE;
8167: v_region_1 hr_locations.region_1%TYPE;
8168: prev_meaning hr_lookups.meaning%TYPE := 'test';

Line 8168: prev_meaning hr_lookups.meaning%TYPE := 'test';

8164: v_meaning hr_lookups.meaning%TYPE;
8165: v_employment_category per_assignments_f.employment_category%TYPE;
8166: v_sex per_people_f.sex%TYPE;
8167: v_region_1 hr_locations.region_1%TYPE;
8168: prev_meaning hr_lookups.meaning%TYPE := 'test';
8169: prev_employment_category per_assignments_f.employment_category%TYPE := '
8170: ';
8171: prev_naic_code hr_lookups.lookup_code%TYPE;
8172: prev_sex per_people_f.sex%TYPE := '';

Line 8171: prev_naic_code hr_lookups.lookup_code%TYPE;

8167: v_region_1 hr_locations.region_1%TYPE;
8168: prev_meaning hr_lookups.meaning%TYPE := 'test';
8169: prev_employment_category per_assignments_f.employment_category%TYPE := '
8170: ';
8171: prev_naic_code hr_lookups.lookup_code%TYPE;
8172: prev_sex per_people_f.sex%TYPE := '';
8173: prev_region_1 hr_locations.region_1%TYPE;
8174:
8175: cursor cur_hr_lookups is

Line 8175: cursor cur_hr_lookups is

8171: prev_naic_code hr_lookups.lookup_code%TYPE;
8172: prev_sex per_people_f.sex%TYPE := '';
8173: prev_region_1 hr_locations.region_1%TYPE;
8174:
8175: cursor cur_hr_lookups is
8176: select
8177: meaning
8178: from
8179: hr_lookups

Line 8179: hr_lookups

8175: cursor cur_hr_lookups is
8176: select
8177: meaning
8178: from
8179: hr_lookups
8180: where
8181: lookup_type='CA_PROVINCE' and
8182: lookup_code=v_region_1;
8183:

Line 8184: v_province_name hr_lookups.meaning%TYPE;

8180: where
8181: lookup_type='CA_PROVINCE' and
8182: lookup_code=v_region_1;
8183:
8184: v_province_name hr_lookups.meaning%TYPE;
8185:
8186: cursor cur_terminated(desig NUMBER) is
8187: select
8188: count(distinct person_id) count,

Line 8202: hr_lookups hl,

8198: substr(paf.employment_category,1,2) employment_category,
8199: ppf.sex sex,
8200: hl1.region_1 region_1
8201: from
8202: hr_lookups hl,
8203: per_jobs pj,
8204: per_assignments_f paf,
8205: per_people_f ppf,
8206: per_person_types ppt,

Line 8274: hr_lookups hl,

8270: 'FR' employment_category,
8271: ppf.sex sex,
8272: hl1.region_1 region_1
8273: from
8274: hr_lookups hl,
8275: per_jobs pj,
8276: per_assignments_f paf,
8277: per_people_f ppf,
8278: per_person_types ppt,

Line 8346: hr_lookups

8342: cursor cur_eeog is
8343: select
8344: meaning
8345: from
8346: hr_lookups
8347: where
8348: lookup_type='EEOG';
8349:
8350: cursor cur_notfound(p_emp_cat number) is

Line 8418: open cur_hr_lookups;

8414: v_employment_category := j.employment_category;
8415: v_sex := j.sex;
8416: v_region_1 := j.region_1;
8417:
8418: open cur_hr_lookups;
8419: fetch cur_hr_lookups into v_province_name;
8420: close cur_hr_lookups;
8421:
8422: if ((prev_region_1 <> v_region_1) or

Line 8419: fetch cur_hr_lookups into v_province_name;

8415: v_sex := j.sex;
8416: v_region_1 := j.region_1;
8417:
8418: open cur_hr_lookups;
8419: fetch cur_hr_lookups into v_province_name;
8420: close cur_hr_lookups;
8421:
8422: if ((prev_region_1 <> v_region_1) or
8423: (ltrim(rtrim(prev_meaning)) <> ltrim(rtrim(v_meaning))) or

Line 8420: close cur_hr_lookups;

8416: v_region_1 := j.region_1;
8417:
8418: open cur_hr_lookups;
8419: fetch cur_hr_lookups into v_province_name;
8420: close cur_hr_lookups;
8421:
8422: if ((prev_region_1 <> v_region_1) or
8423: (ltrim(rtrim(prev_meaning)) <> ltrim(rtrim(v_meaning))) or
8424: (ltrim(rtrim(prev_naic_code)) <> ltrim(rtrim(v_naic_code))) or

Line 8535: open cur_hr_lookups;

8531: v_meaning := j.meaning;
8532: v_count := j.count;
8533: v_region_1 := j.region_1;
8534:
8535: open cur_hr_lookups;
8536: fetch cur_hr_lookups into v_province_name;
8537: close cur_hr_lookups;
8538:
8539: if i = 1 then

Line 8536: fetch cur_hr_lookups into v_province_name;

8532: v_count := j.count;
8533: v_region_1 := j.region_1;
8534:
8535: open cur_hr_lookups;
8536: fetch cur_hr_lookups into v_province_name;
8537: close cur_hr_lookups;
8538:
8539: if i = 1 then
8540:

Line 8537: close cur_hr_lookups;

8533: v_region_1 := j.region_1;
8534:
8535: open cur_hr_lookups;
8536: fetch cur_hr_lookups into v_province_name;
8537: close cur_hr_lookups;
8538:
8539: if i = 1 then
8540:
8541: if v_sex = 'M' then

Line 8797: v_max_naic_code hr_lookups.lookup_code%TYPE;

8793: context = 'FORM12' and
8794: segment1 = 'NAIC'
8795: group by segment4;
8796:
8797: v_max_naic_code hr_lookups.lookup_code%TYPE;
8798: v_max_naic_count number;
8799:
8800: cursor cur_less_than_max_naic is
8801: select

Line 8812: v_not_max_naic hr_lookups.lookup_code%TYPE;

8808: where lookup_type = 'EER1')
8809: and context = 'FORM12' and
8810: segment4 <> v_max_naic_code;
8811:
8812: v_not_max_naic hr_lookups.lookup_code%TYPE;
8813:
8814: cursor cur_not_max_naic_data is
8815: select
8816: context,