[Home] [Help]
50: v_promotions_count NUMBER := 0;
51: BEGIN
52: SELECT count(*)
53: INTO v_promotions_count
54: FROM per_pay_proposals_v2
55: WHERE assignment_id = p_assignment_id
56: AND approved = 'Y'
57: AND change_date BETWEEN p_start_date
58: AND p_end_date
186: AND ppt.system_person_type='EMP'
187: AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
188: AND EXISTS
189: (SELECT 'X'
190: FROM per_pay_proposals_v2 pppv
191: WHERE pppv.business_group_id = p_business_group_id
192: AND pppv.assignment_id = paf.assignment_id
193: AND pppv.approved = 'Y'
194: AND pppv.change_date <= v_year_end)
531: ) and
532: exists
533: (
534: select 'X'
535: from per_pay_proposals_v2 pppv
536: where pppv.business_group_id = p_business_group_id and
537: pppv.assignment_id = paf.assignment_id and
538: pppv.approved = 'Y' and
539: pppv.change_date <=
619: ) and
620: exists
621: (
622: select 'X'
623: from per_pay_proposals_v2 pppv
624: where pppv.business_group_id = p_business_group_id and
625: pppv.assignment_id = paf.assignment_id and
626: pppv.approved = 'Y' and
627: pppv.change_date <=
668: ppt.system_person_type='EMP' and
669: exists
670: (
671: select 'X'
672: from per_pay_proposals_v2 pppv
673: where pppv.business_group_id = p_business_group_id and
674: pppv.assignment_id = paf.assignment_id and
675: pppv.approved = 'Y' and
676: pppv.change_date <= l_year_end
790: ppt.system_person_type='EMP' and
791: exists
792: (
793: select 'X'
794: from per_pay_proposals_v2 pppv
795: where pppv.business_group_id = p_business_group_id and
796: pppv.assignment_id = paf.assignment_id and
797: pppv.approved = 'Y' and
798: pppv.change_date <= l_year_end
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,
1174: per_person_types ppt,
1175: hr_soft_coding_keyflex hsck,
1187: ppb.business_group_id = p_business_group_id and
1188: paf.person_id=ppf.person_id and
1189: paf.assignment_id=pppv.assignment_id and
1190: pppv.change_date = (select max(pppv2.change_date)
1191: from per_pay_proposals_v2 pppv2
1192: where pppv2.assignment_id = paf.assignment_id
1193: and pppv2.change_date <=
1194: decode(substr(paf.employment_category,1,2),
1195: 'PT',p_date_tmp_emp,l_year_end)
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,
1252: per_person_types ppt,
1253: hr_soft_coding_keyflex hsck,
1265: ppb.business_group_id = p_business_group_id and
1266: paf.person_id=ppf.person_id and
1267: paf.assignment_id=pppv.assignment_id and
1268: pppv.change_date = (select max(pppv2.change_date)
1269: from per_pay_proposals_v2 pppv2
1270: where pppv2.assignment_id = paf.assignment_id
1271: and pppv2.change_date <= l_year_end
1272: ) and
1273: ppf.person_type_id=ppt.person_type_id and
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,
1352: per_pay_bases ppb
1353: where
1375: upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
1376: ppf.business_group_id=p_business_group_id and
1377: paf.assignment_id=pppv.assignment_id and
1378: pppv.change_date = (select max(pppv2.change_date)
1379: from per_pay_proposals_v2 pppv2
1380: where pppv2.assignment_id = paf.assignment_id
1381: and pppv2.change_date <=
1382: decode(substr(paf.employment_category,1,2),
1383: 'PT',p_date_tmp_emp,l_year_end)
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,
1452: per_pay_bases ppb
1453: where
1476: decode(substr(NVL(paf.employment_category,'FR'),1,2),
1477: 'FR','FR','PR','PR','PT','PT','FR') = v_employment_category and
1478: paf.assignment_id=pppv.assignment_id and
1479: pppv.change_date = (select max(pppv2.change_date)
1480: from per_pay_proposals_v2 pppv2
1481: where pppv2.assignment_id = paf.assignment_id
1482: and pppv2.change_date <=
1483: decode(substr(paf.employment_category,1,2),
1484: 'PT',p_date_tmp_emp,l_year_end)
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,
2178: hr_locations hl1,
2179: per_person_types ppt,
2197: paf.person_id=ppf.person_id
2198: and decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between ppf.effective_start_date and ppf.effective_end_date and
2199: paf.assignment_id=pppv.assignment_id
2200: and pppv.change_date = (select max(pppv2.change_date)
2201: from per_pay_proposals_v2 pppv2
2202: where pppv2.assignment_id = paf.assignment_id
2203: and paf.person_id=ppf.person_id
2204: and pppv2.change_date <=
2205: decode(substr(paf.employment_category,1,2),
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,
2278: hr_locations hl1,
2279: per_person_types ppt,
2297: ppb.business_group_id = p_business_group_id and
2298: paf.person_id=ppf.person_id and
2299: paf.assignment_id=pppv.assignment_id and
2300: pppv.change_date = (select max(pppv2.change_date)
2301: from per_pay_proposals_v2 pppv2
2302: where pppv2.assignment_id = paf.assignment_id
2303: and paf.person_id=ppf.person_id
2304: and pppv2.change_date <= l_year_end
2305: ) and
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,
2416: hr_soft_coding_keyflex hsck,
2417: per_pay_bases ppb
2444: decode(i_x,1,hl1.region_1,
2445: 2,hl1.region_2) = v_cma_province and
2446: paf.assignment_id=pppv.assignment_id and
2447: pppv.change_date = (select max(pppv2.change_date)
2448: from per_pay_proposals_v2 pppv2
2449: where pppv2.assignment_id = paf.assignment_id
2450: and pppv2.change_date <=
2451: decode(substr(paf.employment_category,1,2),
2452: 'PT',p_date_tmp_emp,l_year_end)
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,
2523: hr_soft_coding_keyflex hsck,
2524: per_pay_bases ppb
2553: 'FR','FR','PR','PR','PT','PT','FR')
2554: = ltrim(rtrim(v_employment_category)) and
2555: paf.assignment_id=pppv.assignment_id and
2556: pppv.change_date = (select max(pppv2.change_date)
2557: from per_pay_proposals_v2 pppv2
2558: where pppv2.assignment_id = paf.assignment_id
2559: and pppv2.change_date <=
2560: decode(substr(paf.employment_category,1,2),
2561: 'PT',p_date_tmp_emp,l_year_end)
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,
3251: per_ca_ee_report_lines pert,
3252: hr_lookups hl2,
3283: ,2,'CMA') and
3284: pert.segment2=hl2.meaning and
3285: paf.assignment_id=pppv.assignment_id and
3286: pppv.change_date = (select max(pppv2.change_date)
3287: from per_pay_proposals_v2 pppv2
3288: where pppv2.assignment_id = paf.assignment_id
3289: and pppv2.approved = 'Y'
3290: and pppv2.change_date <=
3291: decode(substr(paf.employment_category,1,2),
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,
3331: per_ca_ee_report_lines pert,
3332: hr_lookups hl2,
3363: ,2,'CMA') and
3364: pert.segment2=hl2.meaning and
3365: paf.assignment_id=pppv.assignment_id and
3366: pppv.change_date = (select max(pppv2.change_date)
3367: from per_pay_proposals_v2 pppv2
3368: where pppv2.assignment_id = paf.assignment_id
3369: and pppv2.approved = 'Y'
3370: and pppv2.change_date <= l_year_end
3371: ) and
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,
3443: per_ca_ee_report_lines pert,
3444: hr_lookups hl2,
3479: 3,per_information7)='Y' and
3480: substr(NVL(paf.employment_category,'FR'),1,2)=v_employment_category and
3481: paf.assignment_id=pppv.assignment_id and
3482: pppv.change_date = (select max(pppv2.change_date)
3483: from per_pay_proposals_v2 pppv2
3484: where pppv2.assignment_id = paf.assignment_id
3485: and pppv2.approved = 'Y'
3486: and pppv2.change_date <=
3487: decode(substr(paf.employment_category,1,2),
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,
3527: per_ca_ee_report_lines pert,
3528: hr_lookups hl2,
3563: paf.pay_basis_id = ppb.pay_basis_id and
3564: ppb.business_group_id = p_business_group_id and
3565: paf.assignment_id=pppv.assignment_id and
3566: pppv.change_date = (select max(pppv2.change_date)
3567: from per_pay_proposals_v2 pppv2
3568: where pppv2.assignment_id = paf.assignment_id
3569: and pppv2.approved = 'Y'
3570: and pppv2.change_date <= l_year_end
3571: ) and
4428: ) and
4429: exists
4430: (
4431: select 'X'
4432: from per_pay_proposals_v2 pppv
4433: where
4434: pppv.assignment_id = paf.assignment_id and
4435: pppv.approved = 'Y' and
4436: pppv.change_date <= l_year_end
4508: ) and
4509: exists
4510: (
4511: select 'X'
4512: from per_pay_proposals_v2 pppv
4513: where
4514: pppv.assignment_id = paf.assignment_id and
4515: pppv.approved = 'Y' and
4516: pppv.change_date <= l_year_end
4642: ) and
4643: exists
4644: (
4645: select 'X'
4646: from per_pay_proposals_v2 pppv
4647: where
4648: pppv.assignment_id = paf.assignment_id and
4649: pppv.approved = 'Y' and
4650: pppv.change_date <= l_year_end
4742: ) and
4743: exists
4744: (
4745: select 'X'
4746: from per_pay_proposals_v2 pppv
4747: where
4748: pppv.assignment_id = paf.assignment_id and
4749: pppv.approved = 'Y' and
4750: pppv.change_date <= l_year_end
5248: ) and
5249: exists
5250: (
5251: select 'X'
5252: from per_pay_proposals_v2 pppv
5253: where
5254: pppv.assignment_id = paf.assignment_id and
5255: pppv.approved = 'Y' and
5256: pppv.change_date between l_year_start and
5332: ) and
5333: exists
5334: (
5335: select 'X'
5336: from per_pay_proposals_v2 pppv
5337: where
5338: pppv.assignment_id = paf.assignment_id and
5339: pppv.approved = 'Y' and
5340: pppv.change_date between l_year_start and
5451: ) and
5452: exists
5453: (
5454: select 'X'
5455: from per_pay_proposals_v2 pppv
5456: where
5457: pppv.assignment_id = paf.assignment_id and
5458: pppv.change_date between l_year_start and
5459: l_year_end and
5537: ) and
5538: exists
5539: (
5540: select 'X'
5541: from per_pay_proposals_v2 pppv
5542: where
5543: pppv.assignment_id = paf.assignment_id and
5544: pppv.approved = 'Y'and
5545: pppv.change_date between l_year_start and
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
5642: upper(ltrim(rtrim(hl.lookup_code)))
5643: =upper(ltrim(rtrim(pj.job_information1))) and
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
5720: upper(ltrim(rtrim(hl.lookup_code)))
5721: =upper(ltrim(rtrim(pj.job_information1))) and
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
5810: upper(ltrim(rtrim(hl.lookup_code)))
5811: =upper(ltrim(rtrim(pj.job_information1))) and
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
5888: upper(ltrim(rtrim(hl.lookup_code)))
5889: =upper(ltrim(rtrim(pj.job_information1))) and
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
5977: upper(ltrim(rtrim(hl.lookup_code)))
5978: = upper(ltrim(ltrim(pj.job_information1))) and
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
6058: upper(ltrim(rtrim(hl.lookup_code)))
6059: = upper(ltrim(ltrim(pj.job_information1))) and
8126: ) and
8127: exists
8128: (
8129: select 'X'
8130: from per_pay_proposals_v2 pppv
8131: where
8132: pppv.assignment_id = paf.assignment_id and
8133: pppv.approved = 'Y' and
8134: pppv.change_date <= l_year_end
8195: ) and
8196: exists
8197: (
8198: select 'X'
8199: from per_pay_proposals_v2 pppv
8200: where
8201: pppv.assignment_id = paf.assignment_id and
8202: pppv.approved = 'Y' and
8203: pppv.change_date <= l_year_end
8302: ) and
8303: exists
8304: (
8305: select 'X'
8306: from per_pay_proposals_v2 pppv
8307: where
8308: pppv.assignment_id = paf.assignment_id and
8309: pppv.approved = 'Y' and
8310: pppv.change_date <= l_year_end
8374: ) and
8375: exists
8376: (
8377: select 'X'
8378: from per_pay_proposals_v2 pppv
8379: where
8380: pppv.assignment_id = paf.assignment_id and
8381: pppv.approved = 'Y' and
8382: pppv.change_date <= l_year_end