[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,
2173: hr_lookups hl,
2174: per_jobs pj,
2175: per_pay_proposals_v2 pppv,
2176: per_people_f ppf,
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,
2273: hr_lookups hl,
2274: per_jobs pj,
2275: per_pay_proposals_v2 pppv,
2276: per_people_f ppf,
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,
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';
2376:
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,
2414: hr_locations hl1,
2415: per_person_types ppt,
2498: order by ppf.sex;
2499:
2500: v_count number(10);
2501: v_sex per_people_f.sex%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;
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,
2521: hr_locations hl1,
2522: per_person_types ppt,
3242: ppf.sex sex,
3243: decode(cma_province_count,1,hl1.region_1,2,hl1.region_2) cma_province
3244: from
3245: per_jobs pj,
3246: per_assignments_f paf,
3247: per_people_f ppf,
3248: per_pay_proposals_v2 pppv,
3249: per_person_types ppt,
3250: hr_locations hl1,
3322: ppf.sex sex,
3323: decode(cma_province_count,1,hl1.region_1,2,hl1.region_2) cma_province
3324: from
3325: per_jobs pj,
3326: per_assignments_f paf,
3327: per_people_f ppf,
3328: per_pay_proposals_v2 pppv,
3329: per_person_types ppt,
3330: hr_locations hl1,
3398: order by cma_province,employment_category,sex;
3399:
3400: v_count number(10);
3401: v_sex per_people_f.sex%TYPE;
3402: v_employment_category per_assignments_f.employment_category%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;
3399:
3400: v_count number(10);
3401: v_sex per_people_f.sex%TYPE;
3402: v_employment_category per_assignments_f.employment_category%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;
3434: ppf.sex sex,
3435: decode(cma_province_ct,1,hl1.region_1,2,hl1.region_2) cma_province
3436: from
3437: per_jobs pj,
3438: per_assignments_f paf,
3439: per_people_f ppf,
3440: per_pay_proposals_v2 pppv,
3441: per_person_types ppt,
3442: hr_locations hl1,
3518: ppf.sex sex,
3519: decode(cma_province_ct,1,hl1.region_1,2,hl1.region_2) cma_province
3520: from
3521: per_jobs pj,
3522: per_assignments_f paf,
3523: per_people_f ppf,
3524: per_pay_proposals_v2 pppv,
3525: per_person_types ppt,
3526: hr_locations hl1,
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,
4371: hr_locations hl1,
4372: per_ca_ee_report_lines pert,
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,
4451: hr_locations hl1,
4452: per_ca_ee_report_lines pert,
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';
4528: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
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:
4532: cursor cur_meaning is
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,
4565: hr_locations hl1,
4566: per_ca_ee_report_lines pert,
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,
4665: hr_locations hl1,
4666: per_ca_ee_report_lines pert,
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,
5195: hr_locations hl1,
5196: per_ca_ee_report_lines pert,
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,
5279: hr_locations hl1,
5280: per_ca_ee_report_lines pert,
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';
5360: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
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:
5364: cursor cur_meaning is select
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,
5396: hr_locations hl1,
5397: per_ca_ee_report_lines pert,
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,
5482: hr_locations hl1,
5483: per_ca_ee_report_lines pert,
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,
5635: hr_locations hl1,
5636: per_ca_ee_report_lines pert,
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,
5713: hr_locations hl1,
5714: per_ca_ee_report_lines pert,
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,
5803: hr_locations hl1,
5804: per_ca_ee_report_lines pert,
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,
5881: hr_locations hl1,
5882: per_ca_ee_report_lines pert,
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,
5970: hr_locations hl1,
5971: per_ca_ee_report_lines pert,
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,
6051: hr_locations hl1,
6052: per_ca_ee_report_lines pert,
6895: ppf.sex sex,
6896: substr(paf.employment_category,1,2) employment_category,
6897: hl1.region_1 province
6898: from
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,
6905: where job_exists(paf.job_id) is not null
6906: and paf.primary_flag = 'Y'
6907: and paf.assignment_id =
6908: (select max(pafm.assignment_id) -- This select ensures that
6909: from per_assignments_f pafm -- for rehires only the last
6910: where pafm.person_id = ppf.person_id -- assignment is used
6911: and pafm.primary_flag = 'Y'
6912: and pafm.business_group_id = p_business_group_id)
6913: -- and ppf.start_date between
6916: and paf.effective_start_date <= l_year_end
6917: and paf.effective_end_date >= l_year_start
6918: and paf.effective_start_date =
6919: (select max(paf_max.effective_start_date) -- The last assignment
6920: from per_assignments_f paf_max -- in the year
6921: where paf_max.assignment_id = paf.assignment_id
6922: and paf_max.primary_flag = 'Y'
6923: and paf_max.effective_start_date <= l_year_end
6924: and paf_max.effective_end_date >= l_year_start
6979: ppf.sex sex,
6980: 'FR' employment_category,
6981: hl1.region_1 province
6982: from
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,
6990: job_exists(paf.job_id) is not null
6991: and paf.primary_flag = 'Y'
6992: and paf.assignment_id =
6993: (select max(pafm.assignment_id) -- This select ensures that
6994: from per_assignments_f pafm -- for rehires only the last
6995: where pafm.person_id = ppf.person_id -- assignment is used
6996: and pafm.primary_flag = 'Y'
6997: and pafm.business_group_id = p_business_group_id)
6998: -- and ppf.start_date between
7001: and paf.effective_start_date <= l_year_end
7002: and paf.effective_end_date >= l_year_start
7003: and paf.effective_start_date =
7004: (select max(paf_max.effective_start_date)
7005: from per_assignments_f paf_max
7006: where paf_max.assignment_id = paf.assignment_id
7007: and paf_max.primary_flag = 'Y'
7008: and paf_max.effective_start_date <= l_year_end
7009: and paf_max.effective_end_date >= l_year_start
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';
7067: prev_employment_category per_assignments_f.employment_category%TYPE := 'test';
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:
7071: cursor cur_meaning is
7149: ppf.sex sex,
7150: substr(paf.employment_category,1,2) employment_category,
7151: hl1.region_1 province
7152: from
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,
7160: job_exists(paf.job_id) is not null
7161: and paf.primary_flag = 'Y'
7162: and paf.assignment_id =
7163: (select max(pafm.assignment_id) -- This select ensures that
7164: from per_assignments_f pafm -- for rehires only the last
7165: where pafm.person_id = ppf.person_id -- assignment is used
7166: and pafm.primary_flag = 'Y'
7167: and pafm.business_group_id = p_business_group_id)
7168: -- ppf.start_date between
7171: and paf.effective_start_date <= l_year_end
7172: and paf.effective_end_date >= l_year_start
7173: and paf.effective_start_date =
7174: (select max(paf_max.effective_start_date)
7175: from per_assignments_f paf_max
7176: where paf_max.assignment_id = paf.assignment_id
7177: and paf_max.primary_flag = 'Y'
7178: and paf_max.effective_start_date <= l_year_end
7179: and paf_max.effective_end_date >= l_year_start
7233: ppf.sex sex,
7234: 'FR' employment_category,
7235: hl1.region_1 province
7236: from
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,
7244: job_exists(paf.job_id) is not null
7245: and paf.primary_flag = 'Y'
7246: and paf.assignment_id =
7247: (select max(pafm.assignment_id) -- This select ensures that
7248: from per_assignments_f pafm -- for rehires only the last
7249: where pafm.person_id = ppf.person_id -- assignment is used
7250: and pafm.primary_flag = 'Y'
7251: and pafm.business_group_id = p_business_group_id)
7252: -- ppf.start_date between
7255: and paf.effective_start_date <= l_year_end
7256: and paf.effective_end_date >= l_year_start
7257: and paf.effective_start_date =
7258: (select max(paf_max.effective_start_date)
7259: from per_assignments_f paf_max
7260: where paf_max.assignment_id = paf.assignment_id
7261: and paf_max.primary_flag = 'Y'
7262: and paf_max.effective_start_date <= l_year_end
7263: and paf_max.effective_end_date >= l_year_start
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,
8080: per_periods_of_service ppos,
8081: hr_locations hl1,
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,
8149: per_periods_of_service ppos,
8150: hr_locations hl1,
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';
8215: prev_employment_category per_assignments_f.employment_category%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 := '';
8219: prev_region_1 hr_locations.region_1%TYPE;
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,
8253: per_periods_of_service ppos ,
8254: hr_locations hl1,
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,
8325: per_periods_of_service ppos ,
8326: hr_locations hl1,