[Home] [Help]
164: SELECT DISTINCT paf.person_id,
165: paf.soft_coding_keyflex_id,
166: hsck.segment1,
167: hsck.segment6
168: FROM per_assignments_f paf,
169: per_people_f ppf,
170: per_person_types ppt,
171: per_jobs pj,
172: hr_soft_coding_keyflex hsck
458: distinct(paf.person_id) l_person_id,
459: substr(paf.employment_category,1,2) employment_category
460: from
461: per_people_f ppf,
462: per_assignments_f paf,
463: per_person_types ppt,
464: hr_soft_coding_keyflex hsck,
465: per_jobs pj,
466: hr_lookups hl
545: distinct(paf.person_id) l_person_id,
546: 'FR' employment_category
547: from
548: per_people_f ppf,
549: per_assignments_f paf,
550: per_person_types ppt,
551: hr_soft_coding_keyflex hsck,
552: per_jobs pj,
553: hr_lookups hl
636: select
637: count(distinct paf.person_id) count_province_cma,
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,
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,
727: per_person_types ppt,
728: per_jobs pj,
729: hr_lookups hl1
1169: hr_lookups hl,
1170: per_jobs pj,
1171: per_pay_proposals_v2 pppv,
1172: per_people_f ppf,
1173: per_assignments_f paf,
1174: per_person_types ppt,
1175: hr_soft_coding_keyflex hsck,
1176: per_pay_bases ppb
1177: where
1247: hr_lookups hl,
1248: per_jobs pj,
1249: per_pay_proposals_v2 pppv,
1250: per_people_f ppf,
1251: per_assignments_f paf,
1252: per_person_types ppt,
1253: hr_soft_coding_keyflex hsck,
1254: per_pay_bases ppb
1255: where
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:
1326: v_range number;
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,
1350: per_person_types ppt,
1351: hr_soft_coding_keyflex hsck,
1429: order by ppf.sex;
1430:
1431: v_count number(10);
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;
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,
1450: per_person_types ppt,
1451: hr_soft_coding_keyflex hsck,
2171: hr_lookups hl,
2172: per_jobs pj,
2173: per_pay_proposals_v2 pppv,
2174: per_people_f ppf,
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,
2268: hr_lookups hl,
2269: per_jobs pj,
2270: per_pay_proposals_v2 pppv,
2271: per_people_f ppf,
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,
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';
2364:
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,
2402: hr_locations hl1,
2403: per_person_types ppt,
2486: order by ppf.sex;
2487:
2488: v_count number(10);
2489: v_sex per_people_f.sex%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;
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,
2509: hr_locations hl1,
2510: per_person_types ppt,
3230: ppf.sex sex,
3231: decode(cma_province_count,1,hl1.region_1,2,hl1.region_2) cma_province
3232: from
3233: per_jobs pj,
3234: per_assignments_f paf,
3235: per_people_f ppf,
3236: per_pay_proposals_v2 pppv,
3237: per_person_types ppt,
3238: hr_locations hl1,
3310: ppf.sex sex,
3311: decode(cma_province_count,1,hl1.region_1,2,hl1.region_2) cma_province
3312: from
3313: per_jobs pj,
3314: per_assignments_f paf,
3315: per_people_f ppf,
3316: per_pay_proposals_v2 pppv,
3317: per_person_types ppt,
3318: hr_locations hl1,
3386: order by cma_province,employment_category,sex;
3387:
3388: v_count number(10);
3389: v_sex per_people_f.sex%TYPE;
3390: v_employment_category per_assignments_f.employment_category%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;
3387:
3388: v_count number(10);
3389: v_sex per_people_f.sex%TYPE;
3390: v_employment_category per_assignments_f.employment_category%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;
3422: ppf.sex sex,
3423: decode(cma_province_ct,1,hl1.region_1,2,hl1.region_2) cma_province
3424: from
3425: per_jobs pj,
3426: per_assignments_f paf,
3427: per_people_f ppf,
3428: per_pay_proposals_v2 pppv,
3429: per_person_types ppt,
3430: hr_locations hl1,
3506: ppf.sex sex,
3507: decode(cma_province_ct,1,hl1.region_1,2,hl1.region_2) cma_province
3508: from
3509: per_jobs pj,
3510: per_assignments_f paf,
3511: per_people_f ppf,
3512: per_pay_proposals_v2 pppv,
3513: per_person_types ppt,
3514: hr_locations hl1,
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,
4359: hr_locations hl1,
4360: per_ca_ee_report_lines pert,
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,
4439: hr_locations hl1,
4440: per_ca_ee_report_lines pert,
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';
4516: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
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:
4520: cursor cur_meaning is
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,
4553: hr_locations hl1,
4554: per_ca_ee_report_lines pert,
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,
4636: hr_locations hl1,
4637: per_ca_ee_report_lines pert,
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,
5149: hr_locations hl1,
5150: per_ca_ee_report_lines pert,
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,
5233: hr_locations hl1,
5234: per_ca_ee_report_lines pert,
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';
5314: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
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:
5318: cursor cur_meaning is select
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,
5350: hr_locations hl1,
5351: per_ca_ee_report_lines pert,
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,
5436: hr_locations hl1,
5437: per_ca_ee_report_lines pert,
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,
5589: hr_locations hl1,
5590: per_ca_ee_report_lines pert,
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,
5667: hr_locations hl1,
5668: per_ca_ee_report_lines pert,
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,
5757: hr_locations hl1,
5758: per_ca_ee_report_lines pert,
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,
5835: hr_locations hl1,
5836: per_ca_ee_report_lines pert,
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,
5924: hr_locations hl1,
5925: per_ca_ee_report_lines pert,
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,
6005: hr_locations hl1,
6006: per_ca_ee_report_lines pert,
6849: ppf.sex sex,
6850: substr(paf.employment_category,1,2) employment_category,
6851: hl1.region_1 province
6852: from
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,
6859: where job_exists(paf.job_id) is not null
6860: and paf.primary_flag = 'Y'
6861: and paf.assignment_id =
6862: (select max(pafm.assignment_id) -- This select ensures that
6863: from per_assignments_f pafm -- for rehires only the last
6864: where pafm.person_id = ppf.person_id -- assignment is used
6865: and pafm.primary_flag = 'Y'
6866: and pafm.business_group_id = p_business_group_id)
6867: -- and ppf.start_date between
6870: and paf.effective_start_date <= l_year_end
6871: and paf.effective_end_date >= l_year_start
6872: and paf.effective_start_date =
6873: (select max(paf_max.effective_start_date) -- The last assignment
6874: from per_assignments_f paf_max -- in the year
6875: where paf_max.assignment_id = paf.assignment_id
6876: and paf_max.primary_flag = 'Y'
6877: and paf_max.effective_start_date <= l_year_end
6878: and paf_max.effective_end_date >= l_year_start
6933: ppf.sex sex,
6934: 'FR' employment_category,
6935: hl1.region_1 province
6936: from
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,
6944: job_exists(paf.job_id) is not null
6945: and paf.primary_flag = 'Y'
6946: and paf.assignment_id =
6947: (select max(pafm.assignment_id) -- This select ensures that
6948: from per_assignments_f pafm -- for rehires only the last
6949: where pafm.person_id = ppf.person_id -- assignment is used
6950: and pafm.primary_flag = 'Y'
6951: and pafm.business_group_id = p_business_group_id)
6952: -- and ppf.start_date between
6955: and paf.effective_start_date <= l_year_end
6956: and paf.effective_end_date >= l_year_start
6957: and paf.effective_start_date =
6958: (select max(paf_max.effective_start_date)
6959: from per_assignments_f paf_max
6960: where paf_max.assignment_id = paf.assignment_id
6961: and paf_max.primary_flag = 'Y'
6962: and paf_max.effective_start_date <= l_year_end
6963: and paf_max.effective_end_date >= l_year_start
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';
7021: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
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:
7025: cursor cur_meaning is
7103: ppf.sex sex,
7104: substr(paf.employment_category,1,2) employment_category,
7105: hl1.region_1 province
7106: from
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,
7114: job_exists(paf.job_id) is not null
7115: and paf.primary_flag = 'Y'
7116: and paf.assignment_id =
7117: (select max(pafm.assignment_id) -- This select ensures that
7118: from per_assignments_f pafm -- for rehires only the last
7119: where pafm.person_id = ppf.person_id -- assignment is used
7120: and pafm.primary_flag = 'Y'
7121: and pafm.business_group_id = p_business_group_id)
7122: -- ppf.start_date between
7125: and paf.effective_start_date <= l_year_end
7126: and paf.effective_end_date >= l_year_start
7127: and paf.effective_start_date =
7128: (select max(paf_max.effective_start_date)
7129: from per_assignments_f paf_max
7130: where paf_max.assignment_id = paf.assignment_id
7131: and paf_max.primary_flag = 'Y'
7132: and paf_max.effective_start_date <= l_year_end
7133: and paf_max.effective_end_date >= l_year_start
7187: ppf.sex sex,
7188: 'FR' employment_category,
7189: hl1.region_1 province
7190: from
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,
7198: job_exists(paf.job_id) is not null
7199: and paf.primary_flag = 'Y'
7200: and paf.assignment_id =
7201: (select max(pafm.assignment_id) -- This select ensures that
7202: from per_assignments_f pafm -- for rehires only the last
7203: where pafm.person_id = ppf.person_id -- assignment is used
7204: and pafm.primary_flag = 'Y'
7205: and pafm.business_group_id = p_business_group_id)
7206: -- ppf.start_date between
7209: and paf.effective_start_date <= l_year_end
7210: and paf.effective_end_date >= l_year_start
7211: and paf.effective_start_date =
7212: (select max(paf_max.effective_start_date)
7213: from per_assignments_f paf_max
7214: where paf_max.assignment_id = paf.assignment_id
7215: and paf_max.primary_flag = 'Y'
7216: and paf_max.effective_start_date <= l_year_end
7217: and paf_max.effective_end_date >= l_year_start
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,
8034: per_periods_of_service ppos,
8035: hr_locations hl1,
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,
8103: per_periods_of_service ppos,
8104: hr_locations hl1,
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';
8169: prev_employment_category per_assignments_f.employment_category%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 := '';
8173: prev_region_1 hr_locations.region_1%TYPE;
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,
8207: per_periods_of_service ppos ,
8208: hr_locations hl1,
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,
8279: per_periods_of_service ppos ,
8280: hr_locations hl1,