[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)
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,
2176: hr_locations hl1,
2177: per_person_types ppt,
2197: ppf.effective_start_date and
2198: ppf.effective_end_date and
2199: paf.assignment_id=pppv.assignment_id and
2200: 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 pppv2.change_date <=
2204: decode(substr(paf.employment_category,1,2),
2205: 'PT',p_date_tmp_emp,l_year_end)
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,
2273: hr_locations hl1,
2274: per_person_types ppt,
2292: ppb.business_group_id = p_business_group_id and
2293: paf.person_id=ppf.person_id and
2294: paf.assignment_id=pppv.assignment_id and
2295: pppv.change_date = (select max(pppv2.change_date)
2296: from per_pay_proposals_v2 pppv2
2297: where pppv2.assignment_id = paf.assignment_id
2298: and pppv2.change_date <= l_year_end
2299: ) and
2300: l_year_end between
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,
2404: hr_soft_coding_keyflex hsck,
2405: per_pay_bases ppb
2432: decode(i_x,1,hl1.region_1,
2433: 2,hl1.region_2) = v_cma_province and
2434: paf.assignment_id=pppv.assignment_id and
2435: pppv.change_date = (select max(pppv2.change_date)
2436: from per_pay_proposals_v2 pppv2
2437: where pppv2.assignment_id = paf.assignment_id
2438: and pppv2.change_date <=
2439: decode(substr(paf.employment_category,1,2),
2440: 'PT',p_date_tmp_emp,l_year_end)
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,
2511: hr_soft_coding_keyflex hsck,
2512: per_pay_bases ppb
2541: 'FR','FR','PR','PR','PT','PT','FR')
2542: = ltrim(rtrim(v_employment_category)) and
2543: paf.assignment_id=pppv.assignment_id and
2544: pppv.change_date = (select max(pppv2.change_date)
2545: from per_pay_proposals_v2 pppv2
2546: where pppv2.assignment_id = paf.assignment_id
2547: and pppv2.change_date <=
2548: decode(substr(paf.employment_category,1,2),
2549: 'PT',p_date_tmp_emp,l_year_end)
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,
3239: per_ca_ee_report_lines pert,
3240: hr_lookups hl2,
3271: ,2,'CMA') and
3272: pert.segment2=hl2.meaning and
3273: paf.assignment_id=pppv.assignment_id and
3274: pppv.change_date = (select max(pppv2.change_date)
3275: from per_pay_proposals_v2 pppv2
3276: where pppv2.assignment_id = paf.assignment_id
3277: and pppv2.approved = 'Y'
3278: and pppv2.change_date <=
3279: decode(substr(paf.employment_category,1,2),
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,
3319: per_ca_ee_report_lines pert,
3320: hr_lookups hl2,
3351: ,2,'CMA') and
3352: pert.segment2=hl2.meaning and
3353: paf.assignment_id=pppv.assignment_id and
3354: pppv.change_date = (select max(pppv2.change_date)
3355: from per_pay_proposals_v2 pppv2
3356: where pppv2.assignment_id = paf.assignment_id
3357: and pppv2.approved = 'Y'
3358: and pppv2.change_date <= l_year_end
3359: ) and
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,
3431: per_ca_ee_report_lines pert,
3432: hr_lookups hl2,
3467: 3,per_information7)='Y' and
3468: substr(NVL(paf.employment_category,'FR'),1,2)=v_employment_category and
3469: paf.assignment_id=pppv.assignment_id and
3470: pppv.change_date = (select max(pppv2.change_date)
3471: from per_pay_proposals_v2 pppv2
3472: where pppv2.assignment_id = paf.assignment_id
3473: and pppv2.approved = 'Y'
3474: and pppv2.change_date <=
3475: decode(substr(paf.employment_category,1,2),
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,
3515: per_ca_ee_report_lines pert,
3516: hr_lookups hl2,
3551: paf.pay_basis_id = ppb.pay_basis_id and
3552: ppb.business_group_id = p_business_group_id and
3553: paf.assignment_id=pppv.assignment_id and
3554: pppv.change_date = (select max(pppv2.change_date)
3555: from per_pay_proposals_v2 pppv2
3556: where pppv2.assignment_id = paf.assignment_id
3557: and pppv2.approved = 'Y'
3558: and pppv2.change_date <= l_year_end
3559: ) and
4416: ) and
4417: exists
4418: (
4419: select 'X'
4420: from per_pay_proposals_v2 pppv
4421: where
4422: pppv.assignment_id = paf.assignment_id and
4423: pppv.approved = 'Y' and
4424: pppv.change_date <= l_year_end
4496: ) and
4497: exists
4498: (
4499: select 'X'
4500: from per_pay_proposals_v2 pppv
4501: where
4502: pppv.assignment_id = paf.assignment_id and
4503: pppv.approved = 'Y' and
4504: pppv.change_date <= l_year_end
4613: ) and
4614: exists
4615: (
4616: select 'X'
4617: from per_pay_proposals_v2 pppv
4618: where
4619: pppv.assignment_id = paf.assignment_id and
4620: pppv.approved = 'Y' and
4621: pppv.change_date <= l_year_end
4696: ) and
4697: exists
4698: (
4699: select 'X'
4700: from per_pay_proposals_v2 pppv
4701: where
4702: pppv.assignment_id = paf.assignment_id and
4703: pppv.approved = 'Y' and
4704: pppv.change_date <= l_year_end
5202: ) and
5203: exists
5204: (
5205: select 'X'
5206: from per_pay_proposals_v2 pppv
5207: where
5208: pppv.assignment_id = paf.assignment_id and
5209: pppv.approved = 'Y' and
5210: pppv.change_date between l_year_start and
5286: ) and
5287: exists
5288: (
5289: select 'X'
5290: from per_pay_proposals_v2 pppv
5291: where
5292: pppv.assignment_id = paf.assignment_id and
5293: pppv.approved = 'Y' and
5294: pppv.change_date between l_year_start and
5405: ) and
5406: exists
5407: (
5408: select 'X'
5409: from per_pay_proposals_v2 pppv
5410: where
5411: pppv.assignment_id = paf.assignment_id and
5412: pppv.change_date between l_year_start and
5413: l_year_end and
5491: ) and
5492: exists
5493: (
5494: select 'X'
5495: from per_pay_proposals_v2 pppv
5496: where
5497: pppv.assignment_id = paf.assignment_id and
5498: pppv.approved = 'Y'and
5499: pppv.change_date between l_year_start and
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
5596: upper(ltrim(rtrim(hl.lookup_code)))
5597: =upper(ltrim(rtrim(pj.job_information1))) and
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
5674: upper(ltrim(rtrim(hl.lookup_code)))
5675: =upper(ltrim(rtrim(pj.job_information1))) and
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
5764: upper(ltrim(rtrim(hl.lookup_code)))
5765: =upper(ltrim(rtrim(pj.job_information1))) and
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
5842: upper(ltrim(rtrim(hl.lookup_code)))
5843: =upper(ltrim(rtrim(pj.job_information1))) and
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
5931: upper(ltrim(rtrim(hl.lookup_code)))
5932: = upper(ltrim(ltrim(pj.job_information1))) and
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
6012: upper(ltrim(rtrim(hl.lookup_code)))
6013: = upper(ltrim(ltrim(pj.job_information1))) and
8080: ) and
8081: exists
8082: (
8083: select 'X'
8084: from per_pay_proposals_v2 pppv
8085: where
8086: pppv.assignment_id = paf.assignment_id and
8087: pppv.approved = 'Y' and
8088: pppv.change_date <= l_year_end
8149: ) and
8150: exists
8151: (
8152: select 'X'
8153: from per_pay_proposals_v2 pppv
8154: where
8155: pppv.assignment_id = paf.assignment_id and
8156: pppv.approved = 'Y' and
8157: pppv.change_date <= l_year_end
8256: ) and
8257: exists
8258: (
8259: select 'X'
8260: from per_pay_proposals_v2 pppv
8261: where
8262: pppv.assignment_id = paf.assignment_id and
8263: pppv.approved = 'Y' and
8264: pppv.change_date <= l_year_end
8328: ) and
8329: exists
8330: (
8331: select 'X'
8332: from per_pay_proposals_v2 pppv
8333: where
8334: pppv.assignment_id = paf.assignment_id and
8335: pppv.approved = 'Y' and
8336: pppv.change_date <= l_year_end