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: /* bug 10417732 : tuned the cursor to improve performance */

Line 2173: hr_lookups hl,

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

Line 2181: hr_lookups hl2,

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

Line 2273: hr_lookups hl,

2269: 'FR' employment_category,
2270: decode(cma_province_count,1,hl1.region_1
2271: ,2,hl1.region_2) cma_province
2272: from
2273: hr_lookups hl,
2274: per_jobs pj,
2275: per_pay_proposals_v2 pppv,
2276: per_people_f ppf,
2277: per_assignments_f paf,

Line 2281: hr_lookups hl2,

2277: per_assignments_f paf,
2278: hr_locations hl1,
2279: per_person_types ppt,
2280: per_ca_ee_report_lines pert,
2281: hr_lookups hl2,
2282: hr_soft_coding_keyflex hsck,
2283: per_pay_bases ppb
2284: where
2285: hl.lookup_type='EEOG' and

Line 2371: v_meaning hr_lookups.meaning%TYPE;

2367: /* bug 10417732 end */
2368:
2369: v_max_salary number;
2370: v_min_salary number;
2371: v_meaning hr_lookups.meaning%TYPE;
2372: v_employment_category per_assignments_f.employment_category%TYPE;
2373: v_cma_province hr_locations.region_1%TYPE;
2374:
2375: j_flag varchar2(1) := 'F';

Line 2381: hr_lookups

2377: cursor cur_meaning(cp number) is
2378: select
2379: meaning
2380: from
2381: hr_lookups
2382: where
2383: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
2384: ,2,'CA_CMA') and
2385: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));

Line 2387: v_meaning1 hr_lookups.meaning%TYPE;

2383: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
2384: ,2,'CA_CMA') and
2385: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
2386:
2387: v_meaning1 hr_lookups.meaning%TYPE;
2388: v_range number;
2389: v_q1_min number;
2390: v_q1_max number;
2391: v_q2_min number;

Line 2409: hr_lookups hl,

2405: select
2406: count(distinct paf.person_id) count_total,
2407: ppf.sex --sex
2408: from
2409: hr_lookups hl,
2410: per_jobs pj,
2411: per_assignments_f paf,
2412: per_people_f ppf,
2413: per_pay_proposals_v2 pppv,

Line 2506: prev_naic_code hr_lookups.lookup_code%TYPE;

2502: prev_employment_category per_assignments_f.employment_category%TYPE;
2503: prev_sex per_people_f.sex%TYPE;
2504: prev_j number := 0;
2505: prev_cma_province hr_locations.region_1%TYPE;
2506: prev_naic_code hr_lookups.lookup_code%TYPE;
2507: prev_meaning hr_lookups.meaning%TYPE;
2508:
2509: cursor cur_count(range number,
2510: desig number,

Line 2507: prev_meaning hr_lookups.meaning%TYPE;

2503: prev_sex per_people_f.sex%TYPE;
2504: prev_j number := 0;
2505: prev_cma_province hr_locations.region_1%TYPE;
2506: prev_naic_code hr_lookups.lookup_code%TYPE;
2507: prev_meaning hr_lookups.meaning%TYPE;
2508:
2509: cursor cur_count(range number,
2510: desig number,
2511: i_y number) is

Line 2516: hr_lookups hl,

2512: select
2513: count(distinct paf.person_id) count,
2514: ppf.sex --sex
2515: from
2516: hr_lookups hl,
2517: per_jobs pj,
2518: per_assignments_f paf,
2519: per_people_f ppf,
2520: per_pay_proposals_v2 pppv,

Line 3223: v_naic_code hr_lookups.lookup_code%TYPE;

3219: where
3220: pert.request_id = p_request_id and
3221: pert.context = 'FORM12';
3222:
3223: v_naic_code hr_lookups.lookup_code%TYPE;
3224:
3225: v_min_range number;
3226: v_max_range number;
3227: v_fr_min_range number;

Line 3252: hr_lookups hl2,

3248: per_pay_proposals_v2 pppv,
3249: per_person_types ppt,
3250: hr_locations hl1,
3251: per_ca_ee_report_lines pert,
3252: hr_lookups hl2,
3253: hr_soft_coding_keyflex hsck,
3254: per_pay_bases ppb
3255: where
3256: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 3332: hr_lookups hl2,

3328: per_pay_proposals_v2 pppv,
3329: per_person_types ppt,
3330: hr_locations hl1,
3331: per_ca_ee_report_lines pert,
3332: hr_lookups hl2,
3333: hr_soft_coding_keyflex hsck,
3334: per_pay_bases ppb
3335: where
3336: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 3407: prev_naic_code hr_lookups.lookup_code%TYPE;

3403: prev_employment_category per_assignments_f.employment_category%TYPE;
3404: prev_sex per_people_f.sex%TYPE;
3405: prev_x number := 0;
3406: v_cma_province hr_locations.region_1%TYPE;
3407: prev_naic_code hr_lookups.lookup_code%TYPE;
3408:
3409:
3410: cursor cur_meaning(cp number) is
3411: select

Line 3412: meaning from hr_lookups

3408:
3409:
3410: cursor cur_meaning(cp number) is
3411: select
3412: meaning from hr_lookups
3413: where
3414: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
3415: ,2,'CA_CMA') and
3416: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));

Line 3418: v_meaning hr_lookups.meaning%TYPE;

3414: upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
3415: ,2,'CA_CMA') and
3416: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
3417:
3418: v_meaning hr_lookups.meaning%TYPE;
3419: prev_meaning hr_lookups.meaning%TYPE;
3420:
3421: cursor cur_count(cma_province_ct number,
3422: count number,

Line 3419: prev_meaning hr_lookups.meaning%TYPE;

3415: ,2,'CA_CMA') and
3416: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
3417:
3418: v_meaning hr_lookups.meaning%TYPE;
3419: prev_meaning hr_lookups.meaning%TYPE;
3420:
3421: cursor cur_count(cma_province_ct number,
3422: count number,
3423: desig number) is

Line 3444: hr_lookups hl2,

3440: per_pay_proposals_v2 pppv,
3441: per_person_types ppt,
3442: hr_locations hl1,
3443: per_ca_ee_report_lines pert,
3444: hr_lookups hl2,
3445: hr_soft_coding_keyflex hsck,
3446: per_pay_bases ppb
3447: where
3448: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 3528: hr_lookups hl2,

3524: per_pay_proposals_v2 pppv,
3525: per_person_types ppt,
3526: hr_locations hl1,
3527: per_ca_ee_report_lines pert,
3528: hr_lookups hl2,
3529: hr_soft_coding_keyflex hsck,
3530: per_pay_bases ppb
3531: where
3532: upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and

Line 4348: v_naic_code hr_lookups.lookup_code%TYPE;

4344: where
4345: pert.request_id = p_request_id and
4346: pert.context = 'FORM12';
4347:
4348: v_naic_code hr_lookups.lookup_code%TYPE;
4349:
4350: cursor cur_hired_total is
4351: select
4352: count(distinct count_total) count_total,

Line 4366: hr_lookups hl,

4362: ppf.sex sex,
4363: substr(employment_category,1,2) employment_category,
4364: hl1.region_1 province
4365: from
4366: hr_lookups hl,
4367: per_jobs pj,
4368: per_assignments_f paf,
4369: per_people_f ppf,
4370: per_person_types ppt,

Line 4373: hr_lookups hl2,

4369: per_people_f ppf,
4370: per_person_types ppt,
4371: hr_locations hl1,
4372: per_ca_ee_report_lines pert,
4373: hr_lookups hl2,
4374: hr_soft_coding_keyflex hsck
4375: where
4376: hl.lookup_type='EEOG' and
4377: upper(ltrim(rtrim(hl.lookup_code)))

Line 4446: hr_lookups hl,

4442: ppf.sex sex,
4443: 'FR' employment_category,
4444: hl1.region_1 province
4445: from
4446: hr_lookups hl,
4447: per_jobs pj,
4448: per_assignments_f paf,
4449: per_people_f ppf,
4450: per_person_types ppt,

Line 4453: hr_lookups hl2,

4449: per_people_f ppf,
4450: per_person_types ppt,
4451: hr_locations hl1,
4452: per_ca_ee_report_lines pert,
4453: hr_lookups hl2,
4454: hr_soft_coding_keyflex hsck
4455: where
4456: hl.lookup_type='EEOG' and
4457: upper(ltrim(rtrim(hl.lookup_code)))

Line 4523: v_meaning hr_lookups.meaning%TYPE;

4519: group by province,meaning,employment_category,sex
4520: order by province,meaning,employment_category,sex;
4521:
4522: v_count number(10);
4523: v_meaning hr_lookups.meaning%TYPE;
4524: v_employment_category per_assignments_f.employment_category%TYPE;
4525: v_sex per_people_f.sex%TYPE;
4526: v_province hr_locations.region_1%TYPE;
4527: prev_meaning hr_lookups.meaning%TYPE := 'test';

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

4523: v_meaning hr_lookups.meaning%TYPE;
4524: v_employment_category per_assignments_f.employment_category%TYPE;
4525: v_sex per_people_f.sex%TYPE;
4526: v_province hr_locations.region_1%TYPE;
4527: prev_meaning hr_lookups.meaning%TYPE := 'test';
4528: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
4529: prev_sex per_people_f.sex%TYPE := 'test';
4530: prev_naic_code hr_lookups.lookup_code%TYPE;
4531:

Line 4530: prev_naic_code hr_lookups.lookup_code%TYPE;

4526: v_province hr_locations.region_1%TYPE;
4527: prev_meaning hr_lookups.meaning%TYPE := 'test';
4528: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
4529: prev_sex per_people_f.sex%TYPE := 'test';
4530: prev_naic_code hr_lookups.lookup_code%TYPE;
4531:
4532: cursor cur_meaning is
4533: select
4534: meaning

Line 4536: hr_lookups

4532: cursor cur_meaning is
4533: select
4534: meaning
4535: from
4536: hr_lookups
4537: where
4538: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
4539: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
4540:

Line 4541: v_province_name hr_lookups.meaning%TYPE;

4537: where
4538: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
4539: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
4540:
4541: v_province_name hr_lookups.meaning%TYPE;
4542: prev_province_name hr_lookups.meaning%TYPE;
4543:
4544: cursor cur_hired(desig NUMBER) is
4545: select

Line 4542: prev_province_name hr_lookups.meaning%TYPE;

4538: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
4539: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
4540:
4541: v_province_name hr_lookups.meaning%TYPE;
4542: prev_province_name hr_lookups.meaning%TYPE;
4543:
4544: cursor cur_hired(desig NUMBER) is
4545: select
4546: count(distinct person_id) count,

Line 4560: hr_lookups hl,

4556: substr(paf.employment_category,1,2) employment_category,
4557: ppf.sex sex,
4558: hl1.region_1 province
4559: from
4560: hr_lookups hl,
4561: per_jobs pj,
4562: per_assignments_f paf,
4563: per_people_f ppf,
4564: per_person_types ppt,

Line 4567: hr_lookups hl2,

4563: per_people_f ppf,
4564: per_person_types ppt,
4565: hr_locations hl1,
4566: per_ca_ee_report_lines pert,
4567: hr_lookups hl2,
4568: hr_soft_coding_keyflex hsck
4569: where
4570: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4571: upper(ltrim(rtrim(hl.lookup_code)))=

Line 4660: hr_lookups hl,

4656: 'FR' employment_category,
4657: ppf.sex sex,
4658: hl1.region_1 province
4659: from
4660: hr_lookups hl,
4661: per_jobs pj,
4662: per_assignments_f paf,
4663: per_people_f ppf,
4664: per_person_types ppt,

Line 4667: hr_lookups hl2,

4663: per_people_f ppf,
4664: per_person_types ppt,
4665: hr_locations hl1,
4666: per_ca_ee_report_lines pert,
4667: hr_lookups hl2,
4668: hr_soft_coding_keyflex hsck
4669: where
4670: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
4671: upper(ltrim(rtrim(hl.lookup_code)))

Line 4760: hr_lookups

4756: cursor cur_eeog is
4757: select
4758: meaning
4759: from
4760: hr_lookups
4761: where
4762: lookup_type='EEOG';
4763:
4764: cursor cur_notfound(p_emp_cat number) is

Line 5172: v_naic_code hr_lookups.lookup_code%TYPE;

5168: where
5169: pert.request_id = p_request_id and
5170: pert.context = 'FORM12';
5171:
5172: v_naic_code hr_lookups.lookup_code%TYPE;
5173:
5174: cursor cur_promoted_total is
5175: select
5176: count(distinct count_total) count_total,

Line 5190: hr_lookups hl,

5186: ppf.sex sex,
5187: substr(paf.employment_category,1,2) employment_category,
5188: hl1.region_1 province
5189: from
5190: hr_lookups hl,
5191: per_jobs pj,
5192: per_assignments_f paf,
5193: per_people_f ppf,
5194: per_person_types ppt,

Line 5197: hr_lookups hl2,

5193: per_people_f ppf,
5194: per_person_types ppt,
5195: hr_locations hl1,
5196: per_ca_ee_report_lines pert,
5197: hr_lookups hl2,
5198: hr_soft_coding_keyflex hsck
5199: where
5200: hl.lookup_type='EEOG' and
5201: upper(ltrim(rtrim(hl.lookup_code)))

Line 5261: from hr_lookups

5257: l_year_end and
5258: pppv.proposal_reason =
5259: (
5260: select lookup_code
5261: from hr_lookups
5262: where lookup_type = 'PROPOSAL_REASON' and
5263: upper(meaning) = 'PROMOTION'
5264: )
5265: )

Line 5274: hr_lookups hl,

5270: ppf.sex sex,
5271: 'FR' employment_category,
5272: hl1.region_1 province
5273: from
5274: hr_lookups hl,
5275: per_jobs pj,
5276: per_assignments_f paf,
5277: per_people_f ppf,
5278: per_person_types ppt,

Line 5281: hr_lookups hl2,

5277: per_people_f ppf,
5278: per_person_types ppt,
5279: hr_locations hl1,
5280: per_ca_ee_report_lines pert,
5281: hr_lookups hl2,
5282: hr_soft_coding_keyflex hsck
5283: where
5284: hl.lookup_type='EEOG' and
5285: upper(ltrim(rtrim(hl.lookup_code)))

Line 5345: from hr_lookups

5341: l_year_end and
5342: pppv.proposal_reason =
5343: (
5344: select lookup_code
5345: from hr_lookups
5346: where lookup_type = 'PROPOSAL_REASON' and
5347: upper(meaning) = 'PROMOTION'
5348: )
5349: )

Line 5355: v_meaning hr_lookups.meaning%TYPE;

5351: group by province,meaning,employment_category,sex
5352: order by province,meaning,employment_category,sex;
5353:
5354: v_count number(10);
5355: v_meaning hr_lookups.meaning%TYPE;
5356: v_employment_category per_assignments_f.employment_category%TYPE;
5357: v_sex per_people_f.sex%TYPE;
5358: v_province hr_locations.region_1%TYPE;
5359: prev_meaning hr_lookups.meaning%TYPE := 'test';

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

5355: v_meaning hr_lookups.meaning%TYPE;
5356: v_employment_category per_assignments_f.employment_category%TYPE;
5357: v_sex per_people_f.sex%TYPE;
5358: v_province hr_locations.region_1%TYPE;
5359: prev_meaning hr_lookups.meaning%TYPE := 'test';
5360: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
5361: prev_sex per_people_f.sex%TYPE := 'test';
5362: prev_naic_code hr_lookups.lookup_code%TYPE;
5363:

Line 5362: prev_naic_code hr_lookups.lookup_code%TYPE;

5358: v_province hr_locations.region_1%TYPE;
5359: prev_meaning hr_lookups.meaning%TYPE := 'test';
5360: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
5361: prev_sex per_people_f.sex%TYPE := 'test';
5362: prev_naic_code hr_lookups.lookup_code%TYPE;
5363:
5364: cursor cur_meaning is select
5365: meaning
5366: from

Line 5367: hr_lookups

5363:
5364: cursor cur_meaning is select
5365: meaning
5366: from
5367: hr_lookups
5368: where
5369: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
5370: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
5371:

Line 5372: v_province_name hr_lookups.meaning%TYPE;

5368: where
5369: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
5370: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
5371:
5372: v_province_name hr_lookups.meaning%TYPE;
5373: prev_province_name hr_lookups.meaning%TYPE;
5374:
5375: cursor cur_promoted(desig NUMBER) is
5376: select

Line 5373: prev_province_name hr_lookups.meaning%TYPE;

5369: upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
5370: upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
5371:
5372: v_province_name hr_lookups.meaning%TYPE;
5373: prev_province_name hr_lookups.meaning%TYPE;
5374:
5375: cursor cur_promoted(desig NUMBER) is
5376: select
5377: count(distinct person_id) count,

Line 5391: hr_lookups hl,

5387: substr(paf.employment_category,1,2) employment_category,
5388: ppf.sex sex,
5389: hl1.region_1 province
5390: from
5391: hr_lookups hl,
5392: per_jobs pj,
5393: per_assignments_f paf,
5394: per_people_f ppf,
5395: per_person_types ppt,

Line 5398: hr_lookups hl2,

5394: per_people_f ppf,
5395: per_person_types ppt,
5396: hr_locations hl1,
5397: per_ca_ee_report_lines pert,
5398: hr_lookups hl2,
5399: hr_soft_coding_keyflex hsck
5400: where
5401: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5402: upper(ltrim(rtrim(hl.lookup_code)))=upper(ltrim(ltrim(pj.job_information1))) and

Line 5464: from hr_lookups

5460: pppv.approved = 'Y' and
5461: pppv.proposal_reason =
5462: (
5463: select lookup_code
5464: from hr_lookups
5465: where lookup_type = 'PROPOSAL_REASON' and
5466: upper(meaning) = 'PROMOTION'
5467: )
5468: )

Line 5477: hr_lookups hl,

5473: 'FR' employment_category,
5474: ppf.sex sex,
5475: hl1.region_1 province
5476: from
5477: hr_lookups hl,
5478: per_jobs pj,
5479: per_assignments_f paf,
5480: per_people_f ppf,
5481: per_person_types ppt,

Line 5484: hr_lookups hl2,

5480: per_people_f ppf,
5481: per_person_types ppt,
5482: hr_locations hl1,
5483: per_ca_ee_report_lines pert,
5484: hr_lookups hl2,
5485: hr_soft_coding_keyflex hsck
5486: where
5487: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
5488: upper(ltrim(rtrim(hl.lookup_code)))=upper(ltrim(ltrim(pj.job_information1))) and

Line 5550: from hr_lookups

5546: l_year_end and
5547: pppv.proposal_reason =
5548: (
5549: select lookup_code
5550: from hr_lookups
5551: where lookup_type = 'PROPOSAL_REASON' and
5552: upper(meaning) = 'PROMOTION'
5553: )
5554: )

Line 5563: hr_lookups

5559: cursor cur_eeog is
5560: select
5561: meaning
5562: from
5563: hr_lookups
5564: where
5565: lookup_type='EEOG';
5566:
5567: cursor cur_notfound(p_emp_cat number) is

Line 5630: hr_lookups hl,

5626: ppf.sex sex,
5627: substr(paf.employment_category,1,2) employment_category,
5628: hl1.region_1 province
5629: from
5630: hr_lookups hl,
5631: per_jobs pj,
5632: per_assignments_f paf,
5633: per_people_f ppf,
5634: per_person_types ppt,

Line 5637: hr_lookups hl2,

5633: per_people_f ppf,
5634: per_person_types ppt,
5635: hr_locations hl1,
5636: per_ca_ee_report_lines pert,
5637: hr_lookups hl2,
5638: hr_soft_coding_keyflex hsck,
5639: per_pay_proposals_v2 pppv
5640: where
5641: hl.lookup_type='EEOG' and

Line 5697: from hr_lookups

5693: l_year_end and
5694: pppv.proposal_reason =
5695: (
5696: select lookup_code
5697: from hr_lookups
5698: where lookup_type = 'PROPOSAL_REASON' and
5699: upper(meaning) = 'PROMOTION'
5700: )
5701: union all

Line 5708: hr_lookups hl,

5704: ppf.sex sex,
5705: 'FR' employment_category,
5706: hl1.region_1 province
5707: from
5708: hr_lookups hl,
5709: per_jobs pj,
5710: per_assignments_f paf,
5711: per_people_f ppf,
5712: per_person_types ppt,

Line 5715: hr_lookups hl2,

5711: per_people_f ppf,
5712: per_person_types ppt,
5713: hr_locations hl1,
5714: per_ca_ee_report_lines pert,
5715: hr_lookups hl2,
5716: hr_soft_coding_keyflex hsck,
5717: per_pay_proposals_v2 pppv
5718: where
5719: hl.lookup_type='EEOG' and

Line 5775: from hr_lookups

5771: l_year_end and
5772: pppv.proposal_reason =
5773: (
5774: select lookup_code
5775: from hr_lookups
5776: where lookup_type = 'PROPOSAL_REASON' and
5777: upper(meaning) = 'PROMOTION'
5778: )
5779: )

Line 5798: hr_lookups hl,

5794: ppf.sex sex,
5795: substr(paf.employment_category,1,2) employment_category,
5796: hl1.region_1 province
5797: from
5798: hr_lookups hl,
5799: per_jobs pj,
5800: per_assignments_f paf,
5801: per_people_f ppf,
5802: per_person_types ppt,

Line 5805: hr_lookups hl2,

5801: per_people_f ppf,
5802: per_person_types ppt,
5803: hr_locations hl1,
5804: per_ca_ee_report_lines pert,
5805: hr_lookups hl2,
5806: hr_soft_coding_keyflex hsck,
5807: per_pay_proposals_v2 pppv
5808: where
5809: hl.lookup_type='EEOG' and

Line 5865: from hr_lookups

5861: l_year_end and
5862: pppv.proposal_reason =
5863: (
5864: select lookup_code
5865: from hr_lookups
5866: where lookup_type = 'PROPOSAL_REASON' and
5867: upper(meaning) = 'PROMOTION'
5868: )
5869: union all

Line 5876: hr_lookups hl,

5872: ppf.sex sex,
5873: 'FR' employment_category,
5874: hl1.region_1 province
5875: from
5876: hr_lookups hl,
5877: per_jobs pj,
5878: per_assignments_f paf,
5879: per_people_f ppf,
5880: per_person_types ppt,

Line 5883: hr_lookups hl2,

5879: per_people_f ppf,
5880: per_person_types ppt,
5881: hr_locations hl1,
5882: per_ca_ee_report_lines pert,
5883: hr_lookups hl2,
5884: hr_soft_coding_keyflex hsck,
5885: per_pay_proposals_v2 pppv
5886: where
5887: hl.lookup_type='EEOG' and

Line 5943: from hr_lookups

5939: l_year_end and
5940: pppv.proposal_reason =
5941: (
5942: select lookup_code
5943: from hr_lookups
5944: where lookup_type = 'PROPOSAL_REASON' and
5945: upper(meaning) = 'PROMOTION'
5946: )
5947: )

Line 5965: hr_lookups hl,

5961: substr(paf.employment_category,1,2) employment_category,
5962: ppf.sex sex,
5963: hl1.region_1 province
5964: from
5965: hr_lookups hl,
5966: per_jobs pj,
5967: per_assignments_f paf,
5968: per_people_f ppf,
5969: per_person_types ppt,

Line 5972: hr_lookups hl2,

5968: per_people_f ppf,
5969: per_person_types ppt,
5970: hr_locations hl1,
5971: per_ca_ee_report_lines pert,
5972: hr_lookups hl2,
5973: hr_soft_coding_keyflex hsck,
5974: per_pay_proposals_v2 pppv
5975: where
5976: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and

Line 6035: from hr_lookups

6031: pppv.approved = 'Y' and
6032: pppv.proposal_reason =
6033: (
6034: select lookup_code
6035: from hr_lookups
6036: where lookup_type = 'PROPOSAL_REASON' and
6037: upper(meaning) = 'PROMOTION'
6038: )
6039: union all

Line 6046: hr_lookups hl,

6042: 'FR' employment_category,
6043: ppf.sex sex,
6044: hl1.region_1 province
6045: from
6046: hr_lookups hl,
6047: per_jobs pj,
6048: per_assignments_f paf,
6049: per_people_f ppf,
6050: per_person_types ppt,

Line 6053: hr_lookups hl2,

6049: per_people_f ppf,
6050: per_person_types ppt,
6051: hr_locations hl1,
6052: per_ca_ee_report_lines pert,
6053: hr_lookups hl2,
6054: hr_soft_coding_keyflex hsck,
6055: per_pay_proposals_v2 pppv
6056: where
6057: upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and

Line 6116: from hr_lookups

6112: l_year_end and
6113: pppv.proposal_reason =
6114: (
6115: select lookup_code
6116: from hr_lookups
6117: where lookup_type = 'PROPOSAL_REASON' and
6118: upper(meaning) = 'PROMOTION'
6119: )
6120: )

Line 6858: v_naic_code hr_lookups.lookup_code%TYPE;

6854: from per_ca_ee_report_lines pert
6855: where pert.request_id = p_request_id
6856: and pert.context = 'FORM12';
6857:
6858: v_naic_code hr_lookups.lookup_code%TYPE;
6859:
6860: cursor cur_jobs is
6861: select job_id,
6862: meaning

Line 6864: hr_lookups

6860: cursor cur_jobs is
6861: select job_id,
6862: meaning
6863: from per_jobs,
6864: hr_lookups
6865: where lookup_type = 'EEOG'
6866: and upper(ltrim(rtrim(lookup_code)))
6867: =upper(ltrim(rtrim(job_information1)))
6868: and upper(ltrim(rtrim(job_information_category))) = 'CA'

Line 6903: hr_lookups hl2,

6899: per_assignments_f paf,
6900: per_people_f ppf,
6901: hr_locations hl1,
6902: per_ca_ee_report_lines pert,
6903: hr_lookups hl2,
6904: hr_soft_coding_keyflex hsck
6905: where job_exists(paf.job_id) is not null
6906: and paf.primary_flag = 'Y'
6907: and paf.assignment_id =

Line 6987: hr_lookups hl2,

6983: per_assignments_f paf,
6984: per_people_f ppf,
6985: hr_locations hl1,
6986: per_ca_ee_report_lines pert,
6987: hr_lookups hl2,
6988: hr_soft_coding_keyflex hsck
6989: where
6990: job_exists(paf.job_id) is not null
6991: and paf.primary_flag = 'Y'

Line 7062: v_meaning hr_lookups.meaning%TYPE;

7058: group by province,meaning,employment_category,sex
7059: order by province,meaning,employment_category,sex;
7060:
7061: v_count number(10);
7062: v_meaning hr_lookups.meaning%TYPE;
7063: v_employment_category per_assignments_f.employment_category%TYPE;
7064: v_sex per_people_f.sex%TYPE;
7065: v_province hr_locations.region_1%TYPE;
7066: prev_meaning hr_lookups.meaning%TYPE := 'test';

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

7062: v_meaning hr_lookups.meaning%TYPE;
7063: v_employment_category per_assignments_f.employment_category%TYPE;
7064: v_sex per_people_f.sex%TYPE;
7065: v_province hr_locations.region_1%TYPE;
7066: prev_meaning hr_lookups.meaning%TYPE := 'test';
7067: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
7068: prev_sex per_people_f.sex%TYPE := 'test';
7069: prev_naic_code hr_lookups.lookup_code%TYPE;
7070:

Line 7069: prev_naic_code hr_lookups.lookup_code%TYPE;

7065: v_province hr_locations.region_1%TYPE;
7066: prev_meaning hr_lookups.meaning%TYPE := 'test';
7067: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
7068: prev_sex per_people_f.sex%TYPE := 'test';
7069: prev_naic_code hr_lookups.lookup_code%TYPE;
7070:
7071: cursor cur_meaning is
7072: select meaning
7073: from hr_lookups

Line 7073: from hr_lookups

7069: prev_naic_code hr_lookups.lookup_code%TYPE;
7070:
7071: cursor cur_meaning is
7072: select meaning
7073: from hr_lookups
7074: where upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE'
7075: and upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
7076:
7077: v_province_name hr_lookups.meaning%TYPE;

Line 7077: v_province_name hr_lookups.meaning%TYPE;

7073: from hr_lookups
7074: where upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE'
7075: and upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
7076:
7077: v_province_name hr_lookups.meaning%TYPE;
7078: prev_province_name hr_lookups.meaning%TYPE;
7079:
7080: cursor cur_eeog is
7081: select meaning

Line 7078: prev_province_name hr_lookups.meaning%TYPE;

7074: where upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE'
7075: and upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
7076:
7077: v_province_name hr_lookups.meaning%TYPE;
7078: prev_province_name hr_lookups.meaning%TYPE;
7079:
7080: cursor cur_eeog is
7081: select meaning
7082: from hr_lookups

Line 7082: from hr_lookups

7078: prev_province_name hr_lookups.meaning%TYPE;
7079:
7080: cursor cur_eeog is
7081: select meaning
7082: from hr_lookups
7083: where lookup_type='EEOG';
7084:
7085: cursor cur_notfound(p_emp_cat number) is
7086: select

Line 7157: hr_lookups hl2,

7153: per_assignments_f paf,
7154: per_people_f ppf,
7155: hr_locations hl1,
7156: per_ca_ee_report_lines pert,
7157: hr_lookups hl2,
7158: hr_soft_coding_keyflex hsck
7159: where
7160: job_exists(paf.job_id) is not null
7161: and paf.primary_flag = 'Y'

Line 7241: hr_lookups hl2,

7237: per_assignments_f paf,
7238: per_people_f ppf,
7239: hr_locations hl1,
7240: per_ca_ee_report_lines pert,
7241: hr_lookups hl2,
7242: hr_soft_coding_keyflex hsck
7243: where
7244: job_exists(paf.job_id) is not null
7245: and paf.primary_flag = 'Y'

Line 8057: v_naic_code hr_lookups.lookup_code%TYPE;

8053: where
8054: pert.request_id = p_request_id and
8055: pert.context = 'FORM12';
8056:
8057: v_naic_code hr_lookups.lookup_code%TYPE;
8058:
8059: cursor cur_terminated_total is
8060: select
8061: count(distinct count_total) count_total,

Line 8075: hr_lookups hl,

8071: ppf.sex sex,
8072: substr(paf.employment_category,1,2) employment_category,
8073: hl1.region_1 region_1
8074: from
8075: hr_lookups hl,
8076: per_jobs pj,
8077: per_assignments_f paf,
8078: per_people_f ppf,
8079: per_person_types ppt,

Line 8144: hr_lookups hl,

8140: ppf.sex sex,
8141: 'FR' employment_category,
8142: hl1.region_1 region_1
8143: from
8144: hr_lookups hl,
8145: per_jobs pj,
8146: per_assignments_f paf,
8147: per_people_f ppf,
8148: per_person_types ppt,

Line 8210: v_meaning hr_lookups.meaning%TYPE;

8206: group by region_1,meaning,employment_category,sex
8207: order by region_1,meaning,employment_category,sex;
8208:
8209: v_count number(10);
8210: v_meaning hr_lookups.meaning%TYPE;
8211: v_employment_category per_assignments_f.employment_category%TYPE;
8212: v_sex per_people_f.sex%TYPE;
8213: v_region_1 hr_locations.region_1%TYPE;
8214: prev_meaning hr_lookups.meaning%TYPE := 'test';

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

8210: v_meaning hr_lookups.meaning%TYPE;
8211: v_employment_category per_assignments_f.employment_category%TYPE;
8212: v_sex per_people_f.sex%TYPE;
8213: v_region_1 hr_locations.region_1%TYPE;
8214: prev_meaning hr_lookups.meaning%TYPE := 'test';
8215: prev_employment_category per_assignments_f.employment_category%TYPE := '
8216: ';
8217: prev_naic_code hr_lookups.lookup_code%TYPE;
8218: prev_sex per_people_f.sex%TYPE := '';

Line 8217: prev_naic_code hr_lookups.lookup_code%TYPE;

8213: v_region_1 hr_locations.region_1%TYPE;
8214: prev_meaning hr_lookups.meaning%TYPE := 'test';
8215: prev_employment_category per_assignments_f.employment_category%TYPE := '
8216: ';
8217: prev_naic_code hr_lookups.lookup_code%TYPE;
8218: prev_sex per_people_f.sex%TYPE := '';
8219: prev_region_1 hr_locations.region_1%TYPE;
8220:
8221: cursor cur_hr_lookups is

Line 8221: cursor cur_hr_lookups is

8217: prev_naic_code hr_lookups.lookup_code%TYPE;
8218: prev_sex per_people_f.sex%TYPE := '';
8219: prev_region_1 hr_locations.region_1%TYPE;
8220:
8221: cursor cur_hr_lookups is
8222: select
8223: meaning
8224: from
8225: hr_lookups

Line 8225: hr_lookups

8221: cursor cur_hr_lookups is
8222: select
8223: meaning
8224: from
8225: hr_lookups
8226: where
8227: lookup_type='CA_PROVINCE' and
8228: lookup_code=v_region_1;
8229:

Line 8230: v_province_name hr_lookups.meaning%TYPE;

8226: where
8227: lookup_type='CA_PROVINCE' and
8228: lookup_code=v_region_1;
8229:
8230: v_province_name hr_lookups.meaning%TYPE;
8231:
8232: cursor cur_terminated(desig NUMBER) is
8233: select
8234: count(distinct person_id) count,

Line 8248: hr_lookups hl,

8244: substr(paf.employment_category,1,2) employment_category,
8245: ppf.sex sex,
8246: hl1.region_1 region_1
8247: from
8248: hr_lookups hl,
8249: per_jobs pj,
8250: per_assignments_f paf,
8251: per_people_f ppf,
8252: per_person_types ppt,

Line 8320: hr_lookups hl,

8316: 'FR' employment_category,
8317: ppf.sex sex,
8318: hl1.region_1 region_1
8319: from
8320: hr_lookups hl,
8321: per_jobs pj,
8322: per_assignments_f paf,
8323: per_people_f ppf,
8324: per_person_types ppt,

Line 8392: hr_lookups

8388: cursor cur_eeog is
8389: select
8390: meaning
8391: from
8392: hr_lookups
8393: where
8394: lookup_type='EEOG';
8395:
8396: cursor cur_notfound(p_emp_cat number) is

Line 8464: open cur_hr_lookups;

8460: v_employment_category := j.employment_category;
8461: v_sex := j.sex;
8462: v_region_1 := j.region_1;
8463:
8464: open cur_hr_lookups;
8465: fetch cur_hr_lookups into v_province_name;
8466: close cur_hr_lookups;
8467:
8468: if ((prev_region_1 <> v_region_1) or

Line 8465: fetch cur_hr_lookups into v_province_name;

8461: v_sex := j.sex;
8462: v_region_1 := j.region_1;
8463:
8464: open cur_hr_lookups;
8465: fetch cur_hr_lookups into v_province_name;
8466: close cur_hr_lookups;
8467:
8468: if ((prev_region_1 <> v_region_1) or
8469: (ltrim(rtrim(prev_meaning)) <> ltrim(rtrim(v_meaning))) or

Line 8466: close cur_hr_lookups;

8462: v_region_1 := j.region_1;
8463:
8464: open cur_hr_lookups;
8465: fetch cur_hr_lookups into v_province_name;
8466: close cur_hr_lookups;
8467:
8468: if ((prev_region_1 <> v_region_1) or
8469: (ltrim(rtrim(prev_meaning)) <> ltrim(rtrim(v_meaning))) or
8470: (ltrim(rtrim(prev_naic_code)) <> ltrim(rtrim(v_naic_code))) or

Line 8581: open cur_hr_lookups;

8577: v_meaning := j.meaning;
8578: v_count := j.count;
8579: v_region_1 := j.region_1;
8580:
8581: open cur_hr_lookups;
8582: fetch cur_hr_lookups into v_province_name;
8583: close cur_hr_lookups;
8584:
8585: if i = 1 then

Line 8582: fetch cur_hr_lookups into v_province_name;

8578: v_count := j.count;
8579: v_region_1 := j.region_1;
8580:
8581: open cur_hr_lookups;
8582: fetch cur_hr_lookups into v_province_name;
8583: close cur_hr_lookups;
8584:
8585: if i = 1 then
8586:

Line 8583: close cur_hr_lookups;

8579: v_region_1 := j.region_1;
8580:
8581: open cur_hr_lookups;
8582: fetch cur_hr_lookups into v_province_name;
8583: close cur_hr_lookups;
8584:
8585: if i = 1 then
8586:
8587: if v_sex = 'M' then

Line 8843: v_max_naic_code hr_lookups.lookup_code%TYPE;

8839: context = 'FORM12' and
8840: segment1 = 'NAIC'
8841: group by segment4;
8842:
8843: v_max_naic_code hr_lookups.lookup_code%TYPE;
8844: v_max_naic_count number;
8845:
8846: cursor cur_less_than_max_naic is
8847: select

Line 8858: v_not_max_naic hr_lookups.lookup_code%TYPE;

8854: where lookup_type = 'EER1')
8855: and context = 'FORM12' and
8856: segment4 <> v_max_naic_code;
8857:
8858: v_not_max_naic hr_lookups.lookup_code%TYPE;
8859:
8860: cursor cur_not_max_naic_data is
8861: select
8862: context,