[Home] [Help]
751: where pp.assignment_id = p_assignment_id
752: and pp.pay_proposal_id = p_pay_proposal_id
753: and not exists
754: (select null
755: from pay_element_entries_f ee
756: where ee.assignment_id = pp.assignment_id
757: and ee.creator_type = 'SP'
758: and ee.creator_id = pp.pay_proposal_id);
759: end remove_pay_proposals;
1241: ee.entry_information28,
1242: ee.entry_information29,
1243: ee.entry_information30
1244: into v_ele_entry_rec
1245: from pay_element_entries_f ee
1246: where ee.element_entry_id = p_element_entry_id
1247: and p_date between ee.effective_start_date
1248: and ee.effective_end_date;
1249: exception
1396: into v_entry_overridden
1397: from sys.dual
1398: where exists
1399: (select null
1400: from pay_element_entries_f ee
1401: where ee.assignment_id = p_assignment_id
1402: and ee.entry_type in ('A','R')
1403: and ee.target_entry_id = p_element_entry_id
1404: and ee.effective_start_date <= p_validation_end_date
1446: --
1447: cursor csr_ele_info is
1448: select EL.element_type_id element_type_id,
1449: 'Y' salary_element
1450: from pay_element_entries_f EE,
1451: pay_element_links_f EL
1452: where EE.element_entry_id = p_element_entry_id
1453: and EL.element_link_id = EE.element_link_id
1454: and nvl(p_old_date,p_effective_start_date) between
1462: and PB.input_value_id = IV.input_value_id)
1463: UNION ALL
1464: select EL.element_type_id element_type_id,
1465: 'N' salary_element
1466: from pay_element_entries_f EE,
1467: pay_element_links_f EL
1468: where EE.element_entry_id = p_element_entry_id
1469: and EL.element_link_id = EE.element_link_id
1470: and nvl(p_old_date,p_effective_start_date) between
1659: --
1660: if p_start_or_end_date = 'START' then
1661: --
1662: --
1663: update pay_element_entries_f ee
1664: set ee.effective_start_date = p_new_date,
1665: ee.updating_action_id = decode(ee.updating_action_type, 'S', ee.updating_action_id,
1666: null),
1667: ee.updating_action_type = decode(ee.updating_action_type, 'S', 'S', null)
1675: --
1676: elsif p_start_or_end_date = 'END' then
1677: --
1678: --
1679: update pay_element_entries_f ee
1680: set ee.effective_end_date = p_new_date,
1681: ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
1682: null),
1683: ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
1740: and pet.element_type_id = pel.element_type_id
1741: and pec.classification_id = pet.classification_id
1742: and not exists (
1743: select null
1744: from pay_element_entries_f pee
1745: where pee.element_entry_id = p_element_entry_id)
1746: and rownum = 1;
1747: exception
1748: -- If we didn't get a row, this means that we haven't
2192: hr_utility.trace('***** bring EED of new EE back');
2193: end if;
2194: --
2195: -- Change l_ee_eed_out here, for separate update to
2196: -- pay_element_entries_f, below, which *always* occurs.
2197: --
2198: l_ee_eed_out := l_calc_ee_eed;
2199: --
2200: update pay_element_entry_values_f eev
2202: where eev.element_entry_id = l_eeid_out;
2203: --
2204: end if;
2205: --
2206: -- Set attributes on pay_element_entries_f that are not supported by API.
2207: -- Also, effective_end_date of new entry is brought back here, if
2208: -- necessary.
2209: --
2210: update pay_element_entries_f pee
2206: -- Set attributes on pay_element_entries_f that are not supported by API.
2207: -- Also, effective_end_date of new entry is brought back here, if
2208: -- necessary.
2209: --
2210: update pay_element_entries_f pee
2211: set pee.effective_end_date = l_ee_eed_out,
2212: pee.balance_adj_cost_flag = p_rec_ee.balance_adj_cost_flag,
2213: pee.source_asg_action_id = p_rec_ee.source_asg_action_id,
2214: pee.source_link_id = p_rec_ee.source_link_id,
2278: (
2279: p_assignment_id number
2280: ) is
2281: select distinct ee.element_entry_id
2282: from pay_element_entries_f ee
2283: where ee.assignment_id = p_assignment_id;
2284: --
2285: cursor csr_entry
2286: (
2298: el.element_link_id,
2299: el.standard_link_flag,
2300: el.element_type_id
2301: from
2302: pay_element_entries_f ee,
2303: pay_element_links_f el,
2304: pay_element_types_f et
2305: where ee.assignment_id = p_assignment_id
2306: and ee.effective_start_date <= p_validation_end_date
2493: where eev.element_entry_id = p_element_entry_id
2494: and eev.effective_start_date >= p_ee_effective_start_date
2495: and eev.effective_end_date <= p_ee_effective_end_date;
2496: --
2497: delete from pay_element_entries_f ee
2498: where ee.element_entry_id = p_element_entry_id
2499: and ee.effective_start_date >= p_ee_effective_start_date
2500: and ee.effective_end_date <= p_ee_effective_end_date;
2501: --
2532: where exc.source_id = p_element_entry_id
2533: and exc.source_type = 'EE'
2534: and not exists
2535: ( select null
2536: from pay_element_entries_f pee
2537: where pee.element_entry_id = p_element_entry_id);
2538: --
2539: -- Call the routine that checks whether an illegal purge
2540: -- has occurred (i.e. disallowed by profile).
2632: and eev.effective_end_date >= p_validation_start_date
2633: -- End of change 115.60
2634: ;
2635: --
2636: UPDATE PAY_ELEMENT_ENTRIES_F ee
2637: SET ee.effective_end_date = p_validation_start_date - 1,
2638: ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
2639: null),
2640: ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
2762: -- End of change 115.60
2763: and eev.effective_end_date > p_validation_end_date
2764: ;
2765:
2766: UPDATE PAY_ELEMENT_ENTRIES_F ee
2767: SET ee.effective_start_date = p_validation_end_date + 1,
2768: ee.updating_action_id = decode(ee.updating_action_type, 'S', ee.updating_action_id, null),
2769: ee.updating_action_type = decode(ee.updating_action_type, 'S', 'S', null)
2770: WHERE ee.element_entry_id = p_element_entry_id
2819: --
2820: delete from pay_element_entry_values_f eev
2821: where eev.element_entry_id = v_entry.element_entry_id;
2822: --
2823: delete from pay_element_entries_f ee
2824: where ee.element_entry_id = v_entry.element_entry_id;
2825: --
2826: end loop;
2827: --
2970: select ee.element_entry_id,
2971: ee.effective_start_date,
2972: ee.effective_end_date,
2973: ee.element_link_id
2974: from pay_element_entries_f ee
2975: where ee.assignment_id = p_assignment_id
2976: and ee.effective_start_date <= p_validation_end_date
2977: and ee.effective_end_date >= p_validation_start_date
2978: and ee.creator_type in ('F','H')
3088: hr_utility.trace('before delete');
3089: end if;
3090:
3091: select OBJECT_VERSION_NUMBER into l_obj_ver_num
3092: from pay_element_entries_f
3093: where p_validation_start_date-1 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
3094: and element_entry_id=v_entry.element_entry_id;
3095:
3096: if g_debug then
3108: --Commented the below code as part of 6809717
3109: /* delete from pay_element_entry_values_f eev
3110: where eev.element_entry_id = v_entry.element_entry_id;
3111: --
3112: delete from pay_element_entries_f ee
3113: where ee.element_entry_id = v_entry.element_entry_id;*/
3114: --
3115: -- Call the routine that checks whether an illegal purge
3116: -- has occurred (i.e. disallowed by profile).
3350: p_validation_end_date date,
3351: p_adjust_ee_source varchar2
3352: ) is
3353: select ee.*
3354: from pay_element_entries_f ee
3355: ,pay_element_types_f et
3356: where ee.assignment_id = p_assignment_id
3357: and ee.effective_start_date <= p_validation_end_date
3358: and ee.effective_end_date >= p_validation_start_date
3600: hr_utility.set_location(l_proc, 60);
3601: end if;
3602: --Bug 6809717 Added the following code to end date entries
3603: select OBJECT_VERSION_NUMBER into l_obj_ver_num
3604: from pay_element_entries_f
3605: where p_validation_start_date-1 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
3606: and element_entry_id=r_entry.element_entry_id;
3607:
3608: if g_debug then
3653: -- Do bulk update of element entries, to point to their respective new
3654: -- alternative links
3655: --
3656: forall i in 1 .. l_entry_table.element_entry_id.count
3657: update pay_element_entries_f
3658: set element_link_id = l_entry_table.element_link_id(i)
3659: where element_entry_id = l_entry_table.element_entry_id(i)
3660: and effective_start_date = l_entry_table.effective_start_date(i)
3661: and effective_end_date = l_entry_table.effective_end_date(i);
3698: ) is
3699: select ee.element_entry_id,
3700: ee.effective_start_date,
3701: ee.effective_end_date
3702: from pay_element_entries_f ee
3703: where ee.assignment_id = p_assignment_id
3704: and ee.effective_start_date <= p_val_end_date_plus_one
3705: and ee.effective_end_date >= p_val_start_date_minus_one
3706: and ((ee.entry_type <> 'E') or
3989: p_entry_start_date date,
3990: p_entry_end_date date
3991: ) is
3992: select distinct nvl(ee.original_entry_id,ee.element_entry_id)
3993: from pay_element_entries_f ee
3994: where p_mult_ent_allowed_flag = 'Y'
3995: and ee.entry_type = 'E'
3996: and ee.assignment_id = p_assignment_id
3997: and ee.element_link_id = p_element_link_id
4002: from sys.dual
4003: where p_mult_ent_allowed_flag = 'N'
4004: and exists
4005: (select null
4006: from pay_element_entries_f ee
4007: where ee.entry_type = 'E'
4008: and ee.assignment_id = p_assignment_id
4009: and ee.element_link_id = p_element_link_id
4010: and ee.effective_start_date <= p_entry_end_date
4029: ee.effective_start_date,
4030: ee.effective_end_date,
4031: ee.element_link_id,
4032: ee.creator_type
4033: from pay_element_entries_f ee
4034: where ee.entry_type = 'E'
4035: and ee.effective_start_date <= p_entry_end_date
4036: and ee.effective_end_date >= p_entry_start_date
4037: and p_mult_ent_allowed_flag = 'Y'
4043: ee.effective_start_date,
4044: ee.effective_end_date,
4045: ee.element_link_id,
4046: ee.creator_type
4047: from pay_element_entries_f ee
4048: where ee.entry_type = 'E'
4049: and ee.effective_start_date <= p_entry_end_date
4050: and ee.effective_end_date >= p_entry_start_date
4051: and p_mult_ent_allowed_flag = 'Y'
4056: ee.effective_start_date,
4057: ee.effective_end_date,
4058: ee.element_link_id,
4059: ee.creator_type
4060: from pay_element_entries_f ee
4061: where ee.entry_type = 'E'
4062: and ee.effective_start_date <= p_entry_end_date
4063: and ee.effective_end_date >= p_entry_start_date
4064: and p_mult_ent_allowed_flag = 'N'
4707: p_entry_value_tbl => v_entry_value_tbl);
4708:
4709: -- ** remove ** following update when api supports
4710: -- the attributes that are being updated.
4711: update pay_element_entries_f pee
4712: set pee.balance_adj_cost_flag = v_ele_entry_rec.balance_adj_cost_flag,
4713: pee.source_asg_action_id = v_ele_entry_rec.source_asg_action_id,
4714: pee.source_link_id = v_ele_entry_rec.source_link_id,
4715: pee.source_trigger_entry = v_ele_entry_rec.source_trigger_entry,
4788:
4789: --
4790: -- only single element entry row exists, no need for effective dates
4791: --
4792: UPDATE pay_element_entries_f
4793: SET entry_information_category = l_category
4794: WHERE element_entry_id = v_element_entry_id;
4795: end if;
4796: --
4838: ee.creator_id,
4839: ee.effective_start_date,
4840: ee.effective_end_date,
4841: ee.element_link_id
4842: from pay_element_entries_f ee
4843: where ee.assignment_id = p_assignment_id
4844: and ee.entry_type = 'E'
4845: and exists
4846: (select null
4971: and eev.effective_end_date = v_entry.effective_end_date;
4972: --
4973: -- Remove element entries.
4974: --
4975: delete from pay_element_entries_f ee
4976: where ee.element_entry_id = v_entry.element_entry_id
4977: and ee.effective_start_date = v_entry.effective_start_date
4978: and ee.effective_end_date = v_entry.effective_end_date;
4979: --
5161: select ee.element_entry_id,
5162: ee.effective_start_date,
5163: ee.effective_end_date,
5164: ee.element_link_id
5165: from pay_element_entries_f ee
5166: where ee.assignment_id = p_assignment_id
5167: and ee.entry_type = 'E'
5168: and nvl(ee.updating_action_type,'null') <> 'S' -- Bugfix 2249308
5169: and exists
5470: exists
5471: (
5472: -- change 115.40
5473: --select null
5474: select /*+ index(ee pay_element_entries_f_n51) */ null
5475: from pay_element_entries_f ee,
5476: pay_element_types_f et
5477: where ee.element_type_id = et.element_type_id
5478: and et.processing_type = 'R'
5471: (
5472: -- change 115.40
5473: --select null
5474: select /*+ index(ee pay_element_entries_f_n51) */ null
5475: from pay_element_entries_f ee,
5476: pay_element_types_f et
5477: where ee.element_type_id = et.element_type_id
5478: and et.processing_type = 'R'
5479: and ee.entry_type = 'E'
5495: -- Bugfix 2121907
5496: -- Ensure no entries exist for this element link and assignment
5497: and not exists (
5498: select null
5499: from pay_element_entries_f ee
5500: where ee.assignment_id = p_assignment_id
5501: and ee.element_link_id = el.element_link_id
5502: and ee.effective_start_date <= p_validation_end_date
5503: and ee.effective_end_date >= p_validation_start_date
6102: --
6103: -- ensure no entries exist for the assignment
6104: --
6105: and not exists
6106: (select null from pay_element_entries_f pee
6107: where pee.assignment_id = asg.assignment_id
6108: and pee.element_link_id = p_element_link_id
6109: )
6110: order by asg.assignment_id, asg.effective_start_date
6319: hr_utility.trace('********** SL creation');
6320: hr_utility.trace('********** l_category>' || l_category || '<');
6321: end if;
6322:
6323: UPDATE pay_element_entries_f
6324: SET entry_information_category = l_category
6325: WHERE element_entry_id = v_dummy_number;
6326: end if;
6327:
6413: pel.grade_id,
6414: pel.pay_basis_id,
6415: pel.link_to_all_payrolls_flag,
6416: pel.standard_link_flag
6417: from pay_element_entries_f pee,
6418: pay_element_links_f pel
6419: where pee.assignment_id = p_assignment_id
6420: and pel.element_link_id = pee.element_link_id;
6421:
6999: et.processing_type,
7000: ee.updating_action_id,
7001: ee.object_version_number
7002: from per_all_assignments_f asg
7003: , pay_element_entries_f ee
7004: , pay_element_types_f et
7005: where asg.assignment_id = c_assignment_id
7006: and asg.assignment_id = ee.assignment_id
7007: and ee.element_type_id = et.element_type_id
7110: set effective_end_date = l_new_eed
7111: where element_entry_id = r_entry.element_entry_id
7112: and p_old_final_process_date between effective_start_date and effective_end_date;
7113:
7114: update pay_element_entries_f
7115: set effective_end_date = l_new_eed
7116: where element_entry_id = r_entry.element_entry_id
7117: and p_old_final_process_date between effective_start_date and effective_end_date;
7118:
7130: where element_entry_id = r_entry.element_entry_id
7131: and p_old_final_process_date between effective_start_date and effective_end_date
7132: and l_new_eed >= effective_start_date;
7133:
7134: update pay_element_entries_f
7135: set effective_end_date = l_new_eed
7136: where element_entry_id = r_entry.element_entry_id
7137: and p_old_final_process_date between effective_start_date and effective_end_date
7138: and l_new_eed >= effective_start_date;