[Home] [Help]
1: package body hr_entry_api as
2: /* $Header: pyentapi.pkb 120.20.12020000.5 2012/11/20 06:42:29 vmaripal ship $ */
3: --
4: -- Record defined to hold information about a entry element that is being
5: -- updated.
100: processing_type varchar2(30),
101: non_payments_flag varchar2(30),
102: personal_payment_method_id number);
103: --
104: g_package constant varchar2 (32) := 'hr_entry_api.';
105: g_override_user_ent_chk varchar2(1);
106:
107: -- Hold context variables for formula validation. (Fix 1123084).
108: -- We do not need to hold the DATE_EARNED context as a variable
119: ------------------------------------------------------------------------------
120: -- Dave Harris, 01-Feb-1994, G590 --
121: -- --
122: -- NAME --
123: -- hr_entry_api.element_entry_changed PRIVATE FUNCTION --
124: -- --
125: -- DESCRIPTION --
126: -- Determines if the entry to be updated will cause a change on either --
127: -- the PAY_ELEMENT_ENTRIES_F or PAY_ELEMENT_ENTRY_VALUES_F tables. --
225: --
226: -- Function error variables
227: --
228: l_appid number := 801;
229: l_proc_func_name varchar2(60) := 'hr_entry_api.element_entry_changed';
230: l_proc_func_error varchar2(30) := 'HR_6153_ALL_PROCEDURE_FAIL';
231: --
232: begin
233: --
514: end element_entry_changed;
515: --
516: ------------------------------------------------------------------------------
517: -- NAME --
518: -- hr_entry_api.set_formula_contexts PRIVATE FUNCTION --
519: -- --
520: -- DESCRIPTION --
521: --
522: -- Called to cache some (global) information about the assignment passed. --
579: end set_formula_contexts;
580: --
581: ------------------------------------------------------------------------------
582: -- NAME --
583: -- hr_entry_api.run_validation_formula PRIVATE FUNCTION --
584: -- --
585: -- DESCRIPTION --
586: --
587: -- Calls input validation formula to check entry value.
630: else
631: -- No context recognised.
632: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
633: hr_utility.set_message_token('PROCEDURE',
634: 'hr_entry_api.run_validation_formula');
635: hr_utility.set_message_token('STEP','10');
636: hr_utility.raise_error;
637: end if;
638: end loop;
644: -- exactly two outputs.
645: if v_outputs.count <> 2 then
646: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
647: hr_utility.set_message_token('PROCEDURE',
648: 'hr_entry_api.run_validation_formula');
649: hr_utility.set_message_token('STEP','20');
650: hr_utility.raise_error;
651: end if;
652: --
659: v_formula_status := upper(v_outputs(i).value);
660: else
661: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
662: hr_utility.set_message_token('PROCEDURE',
663: 'hr_entry_api.run_validation_formula');
664: hr_utility.set_message_token('STEP','30');
665: hr_utility.raise_error;
666: end if;
667: end loop;
929: if v_session_date is null then
930:
931: close csr_benefit_entry;
932: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
933: hr_utility.set_message_token('PROCEDURE','hr_entry_api.validate_entry_value');
934: hr_utility.set_message_token('STEP','10');
935: hr_utility.raise_error;
936:
937: end if;
980: end validate_entry_value;
981: --
982: ------------------------------------------------------------------------------
983: -- NAME
984: -- hr_entry_api.chk_user_enterable
985: --
986: -- DESCRIPTION
987: -- additional validation on User Enterable IV_IDs and associated EVs,
988: -- preconditions, only called if creator type in forms or MIX and the
1012: --
1013: --
1014: BEGIN
1015: if g_debug then
1016: hr_utility.trace('> hr_entry_api.chk_user_enterable');
1017: hr_utility.trace('| p_el_id>' || p_el_id || '<');
1018: hr_utility.trace('| p_lookup_type>' || p_lookup_type || '<');
1019: hr_utility.trace('| p_value_set_id>' || p_value_set_id || '<');
1020: hr_utility.trace('| p_default_value>' || p_default_value || '<');
1047: EXCEPTION
1048: WHEN NO_DATA_FOUND THEN
1049: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1050: hr_utility.set_message_token('PROCEDURE',
1051: 'hr_entry_api.chk_user_enterable');
1052: hr_utility.set_message_token('STEP','1');
1053: hr_utility.raise_error;
1054: END;
1055: --
1099: exception
1100: when NO_DATA_FOUND then
1101: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1102: hr_utility.set_message_token('PROCEDURE',
1103: 'hr_entry_api.chk_user_enterable');
1104: hr_utility.set_message_token('STEP','2');
1105: hr_utility.raise_error;
1106: end;
1107: --
1180: --
1181: END IF;
1182: --
1183: if g_debug then
1184: hr_utility.trace('< hr_entry_api.chk_user_enterable');
1185: end if;
1186: END chk_user_enterable;
1187: --
1188: ------------------------------------------------------------------------------
1186: END chk_user_enterable;
1187: --
1188: ------------------------------------------------------------------------------
1189: -- NAME --
1190: -- hr_entry_api.conv_entry_values_to_table --
1191: -- --
1192: -- DESCRIPTION --
1193: -- Converts a set of predefined parameters containing the INPUT_VALUE_IDs --
1194: -- and SCREEN_ENTRY_VALUEs for an element entry to a pair of PLSQL tables --
1506: --
1507: -- only raise the error if the EV does not match the
1508: -- default value stored on PLIV
1509: --
1510: hr_entry_api.chk_user_enterable(
1511: p_el_id => p_element_link_id,
1512: p_lookup_type => v_link_value.lookup_type,
1513: p_value_set_id => v_link_value.value_set_id,
1514: p_uom => v_link_value.uom,
1530: --
1531: v_screen_format := v_link_value.entry_value;
1532: v_db_format := v_screen_format;
1533: --
1534: hr_entry_api.validate_entry_value
1535: (p_element_link_id,
1536: v_link_value.input_value_id,
1537: p_session_date,
1538: v_screen_format,
1638: --
1639: -- only raise the error if the EV does not match the
1640: -- existing value.
1641: --
1642: hr_entry_api.chk_user_enterable(
1643: p_el_id => v_entry_value.element_link_id,
1644: p_lookup_type => v_entry_value.lookup_type,
1645: p_value_set_id => v_entry_value.value_set_id,
1646: p_uom => v_entry_value.uom,
1663: if g_debug then
1664: hr_utility.set_location('hr_entry.conv_entry_values_to_table',27);
1665: end if;
1666: --
1667: hr_entry_api.validate_entry_value
1668: (v_entry_value.element_link_id,
1669: v_entry_value.input_value_id,
1670: p_session_date,
1671: v_screen_format,
1901: --
1902: -- only raise the error if the EV does not match the
1903: -- default value stored on PLIV
1904: --
1905: hr_entry_api.chk_user_enterable(
1906: p_el_id => p_element_link_id,
1907: p_lookup_type => v_link_value.lookup_type,
1908: p_value_set_id => v_link_value.value_set_id,
1909: p_uom => v_link_value.uom,
1932: -- the value of the PREVIOUS input value
1933: v_db_format := v_screen_format;
1934: -- end bug 900288
1935: --
1936: hr_entry_api.validate_entry_value
1937: (p_element_link_id,
1938: v_link_value.input_value_id,
1939: p_session_date,
1940: v_screen_format,
2050: --
2051: -- only raise the error if the EV does not match the
2052: -- existing value.
2053: --
2054: hr_entry_api.chk_user_enterable(
2055: p_el_id => v_entry_value.element_link_id,
2056: p_lookup_type => v_entry_value.lookup_type,
2057: p_value_set_id => v_entry_value.value_set_id,
2058: p_uom => v_entry_value.uom,
2072: v_screen_format := p_entry_value_tbl(v_loop);
2073: -- bug 900288
2074: v_db_format := v_screen_format;
2075: -- end bug 900288
2076: hr_entry_api.validate_entry_value
2077: (v_entry_value.element_link_id,
2078: v_entry_value.input_value_id,
2079: p_session_date,
2080: v_screen_format,
2137: end conv_table_to_table;
2138: --
2139: ------------------------------------------------------------------------------
2140: -- NAME --
2141: -- hr_entry_api.get_link --
2142: -- --
2143: -- DESCRIPTION --
2144: -- Returns for the element link that represents the assignments eligibility --
2145: -- for a particular element type on a given date. --
2207: end get_link;
2208: --
2209: ------------------------------------------------------------------------------
2210: -- NAME
2211: -- hr_entry_api.validate_ppm_id
2212: --
2213: -- DESCRIPTION
2214: -- Make sure personal_payment_method_id exists
2215: ------------------------------------------------------------------------------
2317: --
2318: end validate_ppm_id;
2319: ------------------------------------------------------------------------------
2320: -- NAME
2321: -- hr_entry_api.validate_date_earned
2322: --
2323: -- DESCRIPTION
2324: -- Validate the date_earned column
2325: ------------------------------------------------------------------------------
2393: --
2394: end validate_date_earned;
2395: ------------------------------------------------------------------------------
2396: -- NAME --
2397: -- hr_entry_api.val_assignment ==
2398: -- --
2399: -- DESCRIPTION --
2400: -- Make sure assignment exists. --
2401: ------------------------------------------------------------------------------
2431: end val_assignment;
2432: --
2433: ------------------------------------------------------------------------------
2434: -- NAME --
2435: -- hr_entry_api.val_element_link --
2436: -- --
2437: -- DESCRIPTION --
2438: -- Make sure element link exists. --
2439: ------------------------------------------------------------------------------
2469: end val_element_link;
2470: --
2471: ------------------------------------------------------------------------------
2472: -- NAME --
2473: -- hr_entry_api.val_cost_allocation --
2474: -- --
2475: -- DESCRIPTION --
2476: -- Make sure cost allocation exists. --
2477: ------------------------------------------------------------------------------
2533: end val_cost_allocation;
2534: --
2535: ------------------------------------------------------------------------------
2536: -- NAME --
2537: -- hr_entry_api.val_comment --
2538: -- --
2539: -- DESCRIPTION --
2540: -- Make sure comment exists. --
2541: ------------------------------------------------------------------------------
2566: end val_comment;
2567: --
2568: ------------------------------------------------------------------------------
2569: -- NAME --
2570: -- hr_entry_api.val_adjustment --
2571: -- --
2572: -- DESCRIPTION --
2573: -- Make sure adjustment is valid. --
2574: ------------------------------------------------------------------------------
2616: end val_adjustment;
2617: --
2618: ------------------------------------------------------------------------------
2619: -- NAME --
2620: -- hr_entry_api.val_original_entry --
2621: -- --
2622: -- DESCRIPTION --
2623: -- Make sure original entry is valid. --
2624: ------------------------------------------------------------------------------
2672: and p_effective_date between et.effective_start_date
2673: and et.effective_end_date
2674: ;
2675: --
2676: l_proc varchar2(80):= 'hr_entry_api.val_original_entry';
2677: --
2678: begin
2679: --
2680: if g_debug then
2752: end val_original_entry;
2753: --
2754: ------------------------------------------------------------------------------
2755: -- NAME --
2756: -- hr_entry_api.val_reason --
2757: -- --
2758: -- DESCRIPTION --
2759: -- Make sure reason is valid. --
2760: ------------------------------------------------------------------------------
2790: end val_reason;
2791: --
2792: -------------------------------------------------------------------------------
2793: -- NAME --
2794: -- hr_entry_api.derive_element_type_id --
2795: -- --
2796: -- DESCRIPTION --
2797: -- Derives the element_type_id from the previously validated element_link_id --
2798: -- The resulting element_type_id will populate the denormalised column, --
2818: END derive_element_type_id;
2819: --
2820: -------------------------------------------------------------------------------
2821: -- NAME --
2822: -- hr_entry_api.derive_all_entry_values_null --
2823: -- --
2824: -- DESCRIPTION --
2825: -- If one or more Element Entry Values exist for the current Element Entry --
2826: -- and the SCREEN_ENTRY_VALUE for all those Entry Values is NULL, this --
2840: g_debug := hr_utility.debug_enabled;
2841: --
2842: if g_debug then
2843: hr_utility.set_location(
2844: 'Entering hr_entry_api.derive_all_entry_values_null', 5
2845: );
2846: end if;
2847: --
2848: -- First, check to see if there are any entry values...
2913: END derive_all_entry_values_null;
2914: --
2915: ------------------------------------------------------------------------------
2916: -- NAME --
2917: -- hr_entry_api.ins_ele_entry_param_val --
2918: -- --
2919: -- DESCRIPTION --
2920: -- Validates the parameters passed to hr_entry.insert_element_entry. See --
2921: -- below for a list of the validation steps : --
2955: begin
2956: --
2957: -- Make sure assignment exists
2958: --
2959: hr_entry_api.val_assignment
2960: (p_assignment_id,
2961: p_effective_start_date);
2962: --
2963: -- Make sure element link exists
2961: p_effective_start_date);
2962: --
2963: -- Make sure element link exists
2964: --
2965: hr_entry_api.val_element_link
2966: (p_element_link_id,
2967: p_effective_start_date);
2968: --
2969: -- If specified make sure cost allocation exists
2969: -- If specified make sure cost allocation exists
2970: --
2971: if p_cost_allocation_keyflex_id is not null then
2972: --
2973: hr_entry_api.val_cost_allocation
2974: (p_cost_allocation_keyflex_id);
2975: --
2976: end if;
2977: --
2978: -- If specified make sure reason is a valid lookup
2979: --
2980: if p_reason is not null then
2981: --
2982: hr_entry_api.val_reason
2983: (p_reason,
2984: p_effective_start_date);
2985: --
2986: end if;
2988: -- If specified make sure comment exists
2989: --
2990: if p_comment_id is not null then
2991: --
2992: hr_entry_api.val_comment
2993: (p_comment_id);
2994: --
2995: end if;
2996: --
2998: -- element link as the element entry being created
2999: --
3000: if p_original_entry_id is not null then
3001: --
3002: hr_entry_api.val_original_entry
3003: (p_original_entry_id,
3004: p_assignment_id,
3005: p_element_link_id,
3006: p_entry_type,
3012: -- and that it is a normal entry for a recurring element
3013: --
3014: if p_entry_type in ('R','A') then
3015: --
3016: hr_entry_api.val_adjustment
3017: (p_assignment_id,
3018: p_element_link_id,
3019: p_target_entry_id,
3020: p_entry_type,
3599: -- --
3600: --
3601: ------------------------------------------------------------------------------
3602: -- NAME --
3603: -- hr_entry_api.ins_element_entry --
3604: -- --
3605: -- DESCRIPTION --
3606: -- Inserts an element entry providing full validation and also making sure --
3607: -- the end date is set correctly ie. taking into account future entries, --
3727: l_effective_date := p_effective_start_date;
3728: --
3729: -- Validate the parameters
3730: --
3731: hr_entry_api.ins_ele_entry_param_val
3732: (p_effective_start_date,
3733: p_assignment_id,
3734: p_element_link_id,
3735: p_creator_type,
3816: if g_debug then
3817: hr_utility.trace('| element entry context in use, do validation');
3818: end if;
3819:
3820: hr_entry_api.ddf_insert_validate(
3821: p_entry_information_category,
3822: p_entry_information1,
3823: p_entry_information2,
3824: p_entry_information3,
4236:
4237: --
4238: ------------------------------------------------------------------------------
4239: -- NAME --
4240: -- hr_entry_api.insert_element_entry --
4241: -- --
4242: -- DESCRIPTION --
4243: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4244: -- entry values passed as a table. It converts the entry values to DB --
4239: -- NAME --
4240: -- hr_entry_api.insert_element_entry --
4241: -- --
4242: -- DESCRIPTION --
4243: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4244: -- entry values passed as a table. It converts the entry values to DB --
4245: -- format and also defaults any entry values that were not specified. --
4246: -- NB. all entry values specified are validated ! --
4247: ------------------------------------------------------------------------------
4327: --
4328: -- Make sure PLSQL table contains all the entry values ie. fill in missing
4329: -- entry values not specified by the user.
4330: --
4331: hr_entry_api.conv_table_to_table
4332: ('SCREEN',
4333: p_effective_start_date,
4334: null,
4335: p_element_link_id,
4340: v_entry_value_tbl);
4341: --
4342: -- Do real insertion of element entry.
4343: --
4344: hr_entry_api.ins_element_entry
4345: (p_effective_start_date,
4346: p_effective_end_date,
4347: p_element_entry_id,
4348: p_original_entry_id,
4425: end insert_element_entry;
4426: --
4427: ------------------------------------------------------------------------------
4428: -- NAME --
4429: -- hr_entry_api.insert_element_entry --
4430: -- --
4431: -- DESCRIPTION --
4432: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4433: -- entry values passed as parameters. It converts the entry values to DB --
4428: -- NAME --
4429: -- hr_entry_api.insert_element_entry --
4430: -- --
4431: -- DESCRIPTION --
4432: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4433: -- entry values passed as parameters. It converts the entry values to DB --
4434: -- format and also defaults any entry values that were not specified. --
4435: -- NB. all entry values specified are validated ! --
4436: ------------------------------------------------------------------------------
4537: -- Copy entry values into PLSQL table and make sure PLSQL table contains
4538: -- all the entry values ie. fill in missing entry values not specified by
4539: -- the user.
4540: --
4541: hr_entry_api.conv_entry_values_to_table
4542: ('SCREEN',
4543: null,
4544: p_element_link_id,
4545: p_effective_start_date,
4580: v_entry_value_tbl);
4581: --
4582: -- Do real insertion of element entry.
4583: --
4584: hr_entry_api.ins_element_entry
4585: (p_effective_start_date,
4586: p_effective_end_date,
4587: p_element_entry_id,
4588: p_original_entry_id,
4667: -- --
4668: --
4669: ------------------------------------------------------------------------------
4670: -- NAME --
4671: -- hr_entry_api.insert_element_entry --
4672: -- --
4673: -- DESCRIPTION --
4674: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4675: -- entry values passed as a table. It converts the entry values to DB --
4670: -- NAME --
4671: -- hr_entry_api.insert_element_entry --
4672: -- --
4673: -- DESCRIPTION --
4674: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4675: -- entry values passed as a table. It converts the entry values to DB --
4676: -- format and also defaults any entry values that were not specified. --
4677: -- NB. all entry values specified are validated ! --
4678: ------------------------------------------------------------------------------
4791: --
4792: -- Make sure PLSQL table contains all the entry values ie. fill in missing
4793: -- entry values not specified by the user.
4794: --
4795: hr_entry_api.conv_table_to_table
4796: ('SCREEN',
4797: p_effective_start_date,
4798: null,
4799: p_element_link_id,
4804: v_entry_value_tbl);
4805: --
4806: -- Do real insertion of element entry.
4807: --
4808: hr_entry_api.ins_element_entry
4809: (p_effective_start_date,
4810: p_effective_end_date,
4811: p_element_entry_id,
4812: p_original_entry_id,
4890: -- --
4891: --
4892: ------------------------------------------------------------------------------
4893: -- NAME --
4894: -- hr_entry_api.insert_element_entry --
4895: -- --
4896: -- DESCRIPTION --
4897: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4898: -- entry values passed as parameters. It converts the entry values to DB --
4893: -- NAME --
4894: -- hr_entry_api.insert_element_entry --
4895: -- --
4896: -- DESCRIPTION --
4897: -- Cover routine for call to hr_entry_api.ins_element_entry that accepts --
4898: -- entry values passed as parameters. It converts the entry values to DB --
4899: -- format and also defaults any entry values that were not specified. --
4900: -- NB. all entry values specified are validated ! --
4901: ------------------------------------------------------------------------------
5036: -- Copy entry values into PLSQL table and make sure PLSQL table contains
5037: -- all the entry values ie. fill in missing entry values not specified by
5038: -- the user.
5039: --
5040: hr_entry_api.conv_entry_values_to_table
5041: ('SCREEN',
5042: null,
5043: p_element_link_id,
5044: p_effective_start_date,
5079: v_entry_value_tbl);
5080: --
5081: -- Do real insertion of element entry.
5082: --
5083: hr_entry_api.ins_element_entry
5084: (p_effective_start_date,
5085: p_effective_end_date,
5086: p_element_entry_id,
5087: p_original_entry_id,
5163: end insert_element_entry;
5164: --
5165: ------------------------------------------------------------------------------
5166: -- NAME
5167: -- hr_entry_api.check_salary_admin_updates
5168: --
5169: -- DESCRIPTION
5170: -- Validates the rules for updating Salary Admin entries
5171: ------------------------------------------------------------------------------
5171: ------------------------------------------------------------------------------
5172: procedure check_salary_admin_updates
5173: (
5174: p_element_entry_id number,
5175: p_new_entry hr_entry_api.t_update_entry_rec,
5176: p_effective_date date,
5177: p_dt_update_mode varchar2
5178: ) is
5179: --
5222: --
5223: begin
5224: --
5225: if g_debug then
5226: l_proc := 'hr_entry_api.check_salary_admin_updates';
5227: check_parameters;
5228: end if;
5229: --
5230: if
5320: --
5321: end check_salary_admin_updates;
5322: ------------------------------------------------------------------------------
5323: -- NAME --
5324: -- hr_entry_api.upd_ele_entry_param_val --
5325: -- --
5326: -- DESCRIPTION --
5327: -- Validates the parameters passed to hr_entry_api.update_element_entry. --
5328: ------------------------------------------------------------------------------
5323: -- NAME --
5324: -- hr_entry_api.upd_ele_entry_param_val --
5325: -- --
5326: -- DESCRIPTION --
5327: -- Validates the parameters passed to hr_entry_api.update_element_entry. --
5328: ------------------------------------------------------------------------------
5329: procedure upd_ele_entry_param_val
5330: (
5331: --
5406: p_entry_information27 varchar2,
5407: p_entry_information28 varchar2,
5408: p_entry_information29 varchar2,
5409: p_entry_information30 varchar2,
5410: p_entry_rec out nocopy hr_entry_api.t_update_entry_rec
5411: ) is
5412: --
5413: -- Local Variables
5414: --
5411: ) is
5412: --
5413: -- Local Variables
5414: --
5415: v_entry_rec hr_entry_api.t_update_entry_rec;
5416: --
5417: begin
5418: --
5419: -- Cache entry details taking into account if the user has specifed a
5517: exception
5518: when no_data_found then
5519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5520: hr_utility.set_message_token('PROCEDURE',
5521: 'hr_entry_api.upd_ele_entry_param_val');
5522: hr_utility.set_message_token('STEP','1');
5523: hr_utility.raise_error;
5524: end;
5525: --
5525: --
5526: -- If specified make sure cost allocation exists
5527: --
5528: if p_cost_allocation_keyflex_id <> hr_api.g_number then
5529: hr_entry_api.val_cost_allocation
5530: (p_cost_allocation_keyflex_id);
5531: end if;
5532: --
5533: -- If specified make sure reason is a valid lookup
5532: --
5533: -- If specified make sure reason is a valid lookup
5534: --
5535: if p_reason <> hr_api.g_varchar2 then
5536: hr_entry_api.val_reason
5537: (p_reason,
5538: p_session_date);
5539: end if;
5540: --
5540: --
5541: -- If specified make sure comment exists
5542: --
5543: if p_comment_id <> hr_api.g_number then
5544: hr_entry_api.val_comment
5545: (p_comment_id);
5546: end if;
5547: --
5548: -- If specified make sure original entry is for the same assignment and
5548: -- If specified make sure original entry is for the same assignment and
5549: -- element link as the element entry being created
5550: --
5551: if p_original_entry_id <> hr_api.g_number then
5552: hr_entry_api.val_original_entry
5553: (p_original_entry_id,
5554: v_entry_rec.assignment_id,
5555: v_entry_rec.element_link_id,
5556: v_entry_rec.entry_type,
5607: -- --
5608: --
5609: ------------------------------------------------------------------------------
5610: -- NAME --
5611: -- hr_entry_api.upd_element_entry --
5612: -- --
5613: -- DESCRIPTION --
5614: -- Updates an element entry providing full validation. --
5615: ------------------------------------------------------------------------------
5824: v_validation_start_date date;
5825: v_validation_end_date date;
5826: v_max_effective_end_date date;
5827: v_dt_update_mode varchar2(30) := p_dt_update_mode;
5828: v_entry_rec hr_entry_api.t_update_entry_rec;
5829: v_entry_being_changed boolean := true;
5830: v_pay_proposal_id number;
5831: l_all_entry_values_null varchar2(30);
5832: -- bug 666197
5999: close csr_old_entry_values;
6000: exception
6001: when no_data_found then
6002: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6003: hr_utility.set_message_token('PROCEDURE','hr_entry_api.upd_element_entry');
6004: hr_utility.set_message_token('STEP','10');
6005: hr_utility.raise_error;
6006: end;
6007: -- If the entry row is to be checked for a changed attribute then
6008: -- check to see if the entry row is being changed.
6009: --
6010: if (p_check_for_update = 'Y') then
6011: v_entry_being_changed :=
6012: hr_entry_api.element_entry_changed
6013: (p_session_date,
6014: p_element_entry_id,
6015: p_cost_allocation_keyflex_id,
6016: p_updating_action_id,
6082: end if;
6083: --
6084: if v_entry_being_changed then
6085: -- Validate the parameters and return details of the current element entry.
6086: hr_entry_api.upd_ele_entry_param_val
6087: (v_dt_update_mode,
6088: p_session_date,
6089: p_element_entry_id,
6090: p_cost_allocation_keyflex_id,
6210: p_creator_type);
6211: --
6212: -- Validate salary admin updates to user-enterable fields
6213: --
6214: hr_entry_api.check_salary_admin_updates (
6215: --
6216: p_element_entry_id => p_element_entry_id,
6217: p_new_entry => v_entry_rec,
6218: p_effective_date => p_session_date,
6256: if g_debug then
6257: hr_utility.trace('| element entry context in use, do validation');
6258: end if;
6259:
6260: hr_entry_api.ddf_update_validate(
6261: p_entry_information_category => p_entry_information_category
6262: , p_entry_information1 => p_entry_information1
6263: , p_entry_information2 => p_entry_information2
6264: , p_entry_information3 => p_entry_information3
6945:
6946:
6947: ------------------------------------------------------------------------------
6948: -- NAME --
6949: -- hr_entry_api.update_element_entry --
6950: -- --
6951: -- DESCRIPTION --
6952: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
6953: -- entry values passed as a table. It converts the entry values to DB --
6948: -- NAME --
6949: -- hr_entry_api.update_element_entry --
6950: -- --
6951: -- DESCRIPTION --
6952: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
6953: -- entry values passed as a table. It converts the entry values to DB --
6954: -- format. Any entry values that were not specified are copied from the --
6955: -- element entry being updated. --
6956: -- NB. all entry values specified are validated ! --
7054: -- Formula validation of Input Values.
7055: --
7056: set_formula_contexts (v_assignment_id, p_session_date);
7057: --
7058: hr_entry_api.conv_table_to_table
7059: ('SCREEN',
7060: p_session_date,
7061: p_element_entry_id,
7062: null,
7065: l_entry_type,
7066: v_input_value_id_tbl,
7067: v_entry_value_tbl);
7068: --
7069: hr_entry_api.upd_element_entry
7070: (p_dt_update_mode,
7071: p_session_date,
7072: p_check_for_update,
7073: p_element_entry_id,
7146: end update_element_entry;
7147: --
7148: ------------------------------------------------------------------------------
7149: -- NAME --
7150: -- hr_entry_api.update_element_entry --
7151: -- --
7152: -- DESCRIPTION --
7153: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
7154: -- entry values passed as parameters. It converts the entry values to DB --
7149: -- NAME --
7150: -- hr_entry_api.update_element_entry --
7151: -- --
7152: -- DESCRIPTION --
7153: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
7154: -- entry values passed as parameters. It converts the entry values to DB --
7155: -- format. Any entry values that were not specified are copied from the --
7156: -- element entry being updated. --
7157: -- NB. all entry values specified are validated ! --
7282: -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
7283: -- into two tables to be passed into the overloaded version of
7284: -- update_element_entry. The overloaded version is capable of handling
7285: -- unlimited numbers of entry values.
7286: hr_entry_api.conv_entry_values_to_table
7287: ('SCREEN',
7288: p_element_entry_id,
7289: null,
7290: p_session_date,
7323: v_num_entry_values,
7324: v_input_value_id_tbl,
7325: v_entry_value_tbl);
7326: --
7327: hr_entry_api.upd_element_entry
7328: (p_dt_update_mode,
7329: p_session_date,
7330: p_check_for_update,
7331: p_element_entry_id,
7406: -- --
7407: --
7408: ------------------------------------------------------------------------------
7409: -- NAME --
7410: -- hr_entry_api.update_element_entry --
7411: -- --
7412: -- DESCRIPTION --
7413: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
7414: -- entry values passed as a table. It converts the entry values to DB --
7409: -- NAME --
7410: -- hr_entry_api.update_element_entry --
7411: -- --
7412: -- DESCRIPTION --
7413: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
7414: -- entry values passed as a table. It converts the entry values to DB --
7415: -- format. Any entry values that were not specified are copied from the --
7416: -- element entry being updated. --
7417: -- NB. all entry values specified are validated ! --
7546: -- Formula validation of Input Values.
7547: --
7548: set_formula_contexts (v_assignment_id, p_session_date);
7549: --
7550: hr_entry_api.conv_table_to_table
7551: ('SCREEN',
7552: p_session_date,
7553: p_element_entry_id,
7554: null,
7557: l_entry_type,
7558: v_input_value_id_tbl,
7559: v_entry_value_tbl);
7560: --
7561: hr_entry_api.upd_element_entry
7562: (p_dt_update_mode,
7563: p_session_date,
7564: p_check_for_update,
7565: p_element_entry_id,
7639: -- --
7640: --
7641: ------------------------------------------------------------------------------
7642: -- NAME --
7643: -- hr_entry_api.update_element_entry --
7644: -- --
7645: -- DESCRIPTION --
7646: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
7647: -- entry values passed as parameters. It converts the entry values to DB --
7642: -- NAME --
7643: -- hr_entry_api.update_element_entry --
7644: -- --
7645: -- DESCRIPTION --
7646: -- Cover routine for call to hr_entry_api.upd_element_entry that accepts --
7647: -- entry values passed as parameters. It converts the entry values to DB --
7648: -- format. Any entry values that were not specified are copied from the --
7649: -- element entry being updated. --
7650: -- NB. all entry values specified are validated ! --
7806: -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
7807: -- into two tables to be passed into the overloaded version of
7808: -- update_element_entry. The overloaded version is capable of handling
7809: -- unlimited numbers of entry values.
7810: hr_entry_api.conv_entry_values_to_table
7811: ('SCREEN',
7812: p_element_entry_id,
7813: null,
7814: p_session_date,
7847: v_num_entry_values,
7848: v_input_value_id_tbl,
7849: v_entry_value_tbl);
7850: --
7851: hr_entry_api.upd_element_entry
7852: (p_dt_update_mode,
7853: p_session_date,
7854: p_check_for_update,
7855: p_element_entry_id,
7927: end update_element_entry;
7928: --
7929: ------------------------------------------------------------------------------
7930: -- NAME --
7931: -- hr_entry_api.del_ele_entry_param_val --
7932: -- --
7933: -- DESCRIPTION --
7934: -- Validates the parameters passed to hr_entry_api.delete_element_entry. --
7935: ------------------------------------------------------------------------------
7930: -- NAME --
7931: -- hr_entry_api.del_ele_entry_param_val --
7932: -- --
7933: -- DESCRIPTION --
7934: -- Validates the parameters passed to hr_entry_api.delete_element_entry. --
7935: ------------------------------------------------------------------------------
7936: function del_ele_entry_param_val
7937: (
7938: p_dt_delete_mode in out nocopy varchar2,
7938: p_dt_delete_mode in out nocopy varchar2,
7939: p_session_date date,
7940: p_element_entry_id number,
7941: p_assignment_id number,
7942: p_entry_rec out nocopy hr_entry_api.t_delete_entry_rec
7943: ) return boolean is
7944: --
7945: -- Local Variables
7946: --
7944: --
7945: -- Local Variables
7946: --
7947: l_processed varchar2(1) := 'N';
7948: v_entry_rec hr_entry_api.t_delete_entry_rec;
7949:
7950: --Added for bug:6809717
7951: l_assign_exist number:=0;
7952: l_before_ovm number;
8020: exception
8021: when no_data_found then
8022: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
8023: hr_utility.set_message_token('PROCEDURE',
8024: 'hr_entry_api.delete_element_entry');
8025: hr_utility.set_message_token('STEP','1');
8026: hr_utility.raise_error;
8027: when too_many_rows then
8028: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
8026: hr_utility.raise_error;
8027: when too_many_rows then
8028: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
8029: hr_utility.set_message_token('PROCEDURE',
8030: 'hr_entry_api.delete_element_entry');
8031: hr_utility.set_message_token('STEP','2');
8032: hr_utility.raise_error;
8033: end;
8034: --
8176: end del_ele_entry_param_val;
8177: --
8178: ------------------------------------------------------------------------------
8179: -- NAME --
8180: -- hr_entry_api.delete_element_entry --
8181: -- --
8182: -- DESCRIPTION --
8183: -- Deletes an element entry providing full validation. --
8184: ------------------------------------------------------------------------------
8284: v_validation_start_date date;
8285: v_validation_end_date date;
8286: v_next_effective_end_date date;
8287: v_dt_delete_mode varchar2(30) := p_dt_delete_mode;
8288: v_entry_rec hr_entry_api.t_delete_entry_rec;
8289: v_pay_proposal_id number;
8290: -- bug 666197
8291: l_effective_start_date date;
8292: l_effective_end_date date;
8455: begin
8456: g_debug := hr_utility.debug_enabled;
8457: --
8458: if g_debug then
8459: hr_utility.set_location('hr_entry_api.delete_element_entry', 5);
8460: end if;
8461: --
8462: -- bug 666197, retrieve and store old values for the entry row
8463: --
8511: close csr_old_entry_values;
8512: exception
8513: when no_data_found then
8514: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
8515: hr_utility.set_message_token('PROCEDURE','hr_entry_api.upd_element_entry');
8516: hr_utility.set_message_token('STEP','10');
8517: hr_utility.raise_error;
8518: end;
8519: --
8534: else
8535: set_formula_contexts(l_assignment_id_o, p_session_date);
8536: end if;
8537: --
8538: if hr_entry_api.del_ele_entry_param_val
8539: (v_dt_delete_mode,
8540: p_session_date,
8541: p_element_entry_id,
8542: l_assignment_id_o,
8547: --
8548: if v_dt_delete_mode = 'DELETE_NEXT_CHANGE' then
8549: --
8550: if g_debug then
8551: hr_utility.set_location('hr_entry_api.delete_element_entry', 15);
8552: end if;
8553: --
8554: begin
8555: select min(ee.effective_end_date)
8586: -- for purge. We check this here because the v_dt_delete_mode
8587: -- can be changed by the previous call.
8588: --
8589: if g_debug then
8590: hr_utility.set_location('hr_entry_api.delete_element_entry', 18);
8591: end if;
8592: -- Start by reading the value.
8593: fnd_profile.get('HR_ELE_ENTRY_PURGE_CONTROL', l_prof_value);
8594: --
8617: --
8618: end if;
8619: --
8620: if g_debug then
8621: hr_utility.set_location('hr_entry_api.delete_element_entry', 20);
8622: end if;
8623: --
8624: -- Make sure the delete is valid.
8625: --
8690: open csr_accepted_pay_proposals(p_element_entry_id,
8691: v_entry_rec.assignment_id);
8692: --
8693: if g_debug then
8694: hr_utility.set_location('hr_entry_api.delete_element_entry', 25);
8695: end if;
8696: --
8697: -- Trigger workload shifting if change to element entry overlaps with the
8698: -- latest payroll run for the assignment and workload shifting is enabled.
8711: --
8712: if v_dt_delete_mode = 'DELETE' or p_dt_delete_mode = 'ZAP' then
8713: --
8714: if g_debug then
8715: hr_utility.set_location('hr_entry_api.delete_element_entry', 30);
8716: end if;
8717: --
8718: -- Enhancement 3368211
8719: --
8751: --
8752: if v_dt_delete_mode = 'DELETE_NEXT_CHANGE' then
8753: --
8754: if g_debug then
8755: hr_utility.set_location('hr_entry_api.delete_element_entry', 40);
8756: end if;
8757: --
8758: delete from pay_element_entries_f ee
8759: where ee.element_entry_id = p_element_entry_id
8772: --
8773: elsif v_dt_delete_mode = 'FUTURE_CHANGE' then
8774: --
8775: if g_debug then
8776: hr_utility.set_location('hr_entry_api.delete_element_entry', 45);
8777: end if;
8778: --
8779: delete from pay_element_entries_f ee
8780: where ee.element_entry_id = p_element_entry_id
8790: --
8791: elsif v_dt_delete_mode = 'DELETE' then
8792: --
8793: if g_debug then
8794: hr_utility.set_location('hr_entry_api.delete_element_entry', 50);
8795: end if;
8796: --
8797: delete from pay_element_entries_f ee
8798: where ee.element_entry_id = p_element_entry_id
8808: --
8809: elsif v_dt_delete_mode = 'ZAP' then
8810: --
8811: if g_debug then
8812: hr_utility.set_location('hr_entry_api.delete_element_entry', 55);
8813: end if;
8814: --
8815: -- For Zap mode, delete any balance exclusions rows for the entry
8816: --
8825: l_effective_start_date_o := v_validation_start_date;
8826: end if;
8827: --
8828: if g_debug then
8829: hr_utility.set_location('hr_entry_api.delete_element_entry', 60);
8830: end if;
8831: --
8832: -- Delete element entry values
8833: --
8845: v_validation_start_date,
8846: v_validation_end_date);
8847: --
8848: if g_debug then
8849: hr_utility.set_location('hr_entry_api.delete_element_entry', 65);
8850: end if;
8851: --
8852: -- Go through list of accepted pay proposals for the assignment /
8853: -- element entry to see if any of the pay proosals have lost the element
8884: and ee.effective_start_date = pp.change_date);
8885: --
8886: end loop;
8887: --
8888: else -- hr_entry_api.del_ele_entry_param_val returned 'FALSE'
8889: --
8890: if v_entry_rec.processing_type = 'N' and
8891: --v_dt_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
8892: v_dt_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE','DELETE') then
8891: --v_dt_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
8892: v_dt_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE','DELETE') then
8893: --
8894: if g_debug then
8895: hr_utility.set_location('hr_entry_api.delete_element_entry', 67);
8896: end if;
8897: --
8898: -- We do not wish to continue processing
8899: --
8915: --
8916: begin
8917: --
8918: -- NB bug 3057670 has highlighted the fact that the orig delete_mode can be altered
8919: -- in the earlier call to hr_entry_api.del_ele_entry_param_val
8920: -- Therefore the passed value can be p_dt_delete_mode or v_dt_delete_mode.
8921: -- From JUL FP 03 this has been made the latter, ie if we handle a DELETE as a ZAP
8922: -- as in the bug above, then tell the hook (and DYT's etc) that we have just
8923: -- performed a ZAP
9019: end;
9020: --
9021: --
9022: if g_debug then
9023: hr_utility.set_location('hr_entry_api.delete_element_entry', 70);
9024: end if;
9025: --
9026:
9027: --
9041: end if;
9042:
9043: end delete_element_entry;
9044: --
9045: end hr_entry_api;