[Home] [Help]
1: PACKAGE BODY PAY_ACCRUAL_PLANS_PKG as
2: /* $Header: pyappap.pkb 115.3 99/08/24 06:49:02 porting shi $ */
3:
4:
5: PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
15: X_Accrual_Units_Of_Measure VARCHAR2,
16: X_Ineligible_Period_Length NUMBER,
17: X_Ineligible_Period_Type VARCHAR2
18: ) IS
19: CURSOR C IS SELECT rowid FROM PAY_ACCRUAL_PLANS
20: WHERE accrual_plan_id = X_Accrual_Plan_Id;
21:
22: CURSOR C2 IS SELECT pay_accrual_plans_s.nextval FROM sys.dual;
23: BEGIN
18: ) IS
19: CURSOR C IS SELECT rowid FROM PAY_ACCRUAL_PLANS
20: WHERE accrual_plan_id = X_Accrual_Plan_Id;
21:
22: CURSOR C2 IS SELECT pay_accrual_plans_s.nextval FROM sys.dual;
23: BEGIN
24:
25: if (X_Accrual_Plan_Id is NULL) then
26: OPEN C2;
26: OPEN C2;
27: FETCH C2 INTO X_Accrual_Plan_Id;
28: CLOSE C2;
29: end if;
30: INSERT INTO PAY_ACCRUAL_PLANS(
31: accrual_plan_id,
32: business_group_id,
33: accrual_plan_element_type_id,
34: pto_input_value_id,
78: X_Ineligible_Period_Type VARCHAR2
79: ) IS
80: CURSOR C IS
81: SELECT *
82: FROM PAY_ACCRUAL_PLANS
83: WHERE rowid = X_Rowid
84: FOR UPDATE of Accrual_Plan_Id NOWAIT;
85: Recinfo C%ROWTYPE;
86: BEGIN
159: X_Ineligible_Period_Length NUMBER,
160: X_Ineligible_Period_Type VARCHAR2
161: ) IS
162: BEGIN
163: UPDATE PAY_ACCRUAL_PLANS
164: SET
165: accrual_plan_id = X_Accrual_Plan_Id,
166: business_group_id = X_Business_Group_Id,
167: accrual_plan_element_type_id = X_Accrual_Plan_Element_Type_Id,
183: END Update_Row;
184:
185: PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
186: BEGIN
187: DELETE FROM PAY_ACCRUAL_PLANS
188: WHERE rowid = X_Rowid;
189:
190: if (SQL%NOTFOUND) then
191: RAISE NO_DATA_FOUND;
211: l_comb_exists VARCHAR2(2);
212: --
213: CURSOR dup_rec1 IS
214: select 'Y'
215: from PAY_ACCRUAL_PLANS
216: where upper(ACCRUAL_PLAN_NAME) = upper(p_plan_name)
217: and ((p_accrual_plan_id is null)
218: or
219: (p_accrual_plan_id is not null
278: p_accrual_plan_id IN number) IS
279: --
280: BEGIN
281: --
282: PAY_ACCRUAL_PLANS_PKG.CHK_PLAN_NAME(p_plan_name,
283: p_accrual_plan_id);
284: END insert_validation;
285: --
286: --
315: l_effective_end_date date;
316: l_element_type_id number;
317: BEGIN
318: --
319: hr_utility.set_location('pay_accrual_plans_pkg.create_element',1);
320: l_effective_start_date := hr_general.start_of_time;
321: l_effective_end_date := hr_general.end_of_time;
322: --
323: l_element_type_id := PAY_DB_PAY_SETUP.create_element
391: l_input_value_id number;
392: --
393: BEGIN
394: --
395: hr_utility.set_location('pay_accrual_plans_pkg.create_input_value',1);
396: l_effective_start_date := hr_general.start_of_time;
397: l_effective_end_date := hr_general.end_of_time;
398: --
399: l_input_value_id := pay_db_pay_setup.create_input_value(
465: /*
466: ****************************************************************************
467: NAME pre_insert_actions
468:
469: DESCRIPTION handles all of the pre-insert actions for pay_accrual_plans; at
470: the moment all it does is creates the element types and input
471: values, but it may need to do more in the future (there's nothing
472: wrong with anticipating expansion in functionality)
473:
515: nvl (non_payments_flag, 'X') desc, classification_name;
516: --
517: BEGIN
518: hr_utility.set_location
519: ('pay_accrual_plans_pkg.pre_insert_actions',1);
520: --
521: -- create the accrual plan element type and input value...
522: --
523: -- Added by RMAMGAIN
568: l_element_description := 'Accrual plan for ' || l_element_name;
569: l_primary_classification_id := 37;
570: l_classification_type := 'N';
571: --
572: l_element_type_id := PAY_ACCRUAL_PLANS_PKG.CREATE_ELEMENT(
573: l_element_name,
574: l_element_description,
575: 'R',
576: l_bg_name,
583: --
584: l_input_value_name := 'Continuous Service Date';
585: l_uom_code := 'D';
586: --
587: l_input_value_id := PAY_ACCRUAL_PLANS_PKG.CREATE_INPUT_VALUE(
588: l_element_name,
589: l_input_value_name,
590: l_uom_code,
591: l_bg_name,
624: --
625: l_primary_classification_id := 40;
626: l_classification_type := 'Y';
627: --
628: l_element_type_id := PAY_ACCRUAL_PLANS_PKG.CREATE_ELEMENT(
629: l_element_name,
630: l_element_description,
631: 'N',
632: l_bg_name,
634: l_leg_code,
635: l_curr_code,
636: l_post_termination_rule);
637: --
638: l_input_value_id := PAY_ACCRUAL_PLANS_PKG.CREATE_INPUT_VALUE(
639: l_element_name,
640: l_input_value_name,
641: l_uom_code,
642: l_bg_name,
657: l_element_name := substr('Residual ' || p_plan_name, 1, 80);
658: l_element_description :=
659: 'Residual entitlement for accrual plan ' || p_plan_name;
660: --
661: l_element_type_id := PAY_ACCRUAL_PLANS_PKG.CREATE_ELEMENT(
662: l_element_name,
663: l_element_description,
664: 'N',
665: l_bg_name,
667: l_leg_code,
668: l_curr_code,
669: l_post_termination_rule);
670: --
671: l_input_value_id := PAY_ACCRUAL_PLANS_PKG.CREATE_INPUT_VALUE(
672: l_element_name,
673: l_input_value_name,
674: l_uom_code,
675: l_bg_name,
691: /*
692: ****************************************************************************
693: NAME post_insert_actions
694:
695: DESCRIPTION handles all of the post-insert actions for pay_accrual_plans; at
696: the moment all it does is creates the default net calculation
697: rules for the plan.
698:
699: 04-AUG-95 hparicha 279860 Update Pay Value mand_flag
712: BEGIN
713: --
714: -- insert the pto input value (always reduces entitlement)
715: --
716: hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',1);
717: insert into pay_net_calculation_rules(
718: net_calculation_rule_id,
719: accrual_plan_id,
720: business_group_id,
729: from dual;
730: --
731: -- insert the carried over input value (always increases entitlement)
732: --
733: hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',2);
734: insert into pay_net_calculation_rules(
735: net_calculation_rule_id,
736: accrual_plan_id,
737: business_group_id,
744: p_co_input_value_id,
745: 1
746: from dual;
747: --
748: hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',3);
749: --
750: -- The update below was originally written as a separate implicit SELECT
751: -- and UPDATE. Changed to a single UPDATE, because non-US accrual plans
752: -- create all element as non-payment, hence have no PAY INPUT VALUE. The
756: SET mandatory_flag = 'X'
757: WHERE input_value_id =
758: ( SELECT piv.input_value_id
759: FROM pay_input_values_f piv,
760: pay_accrual_plans pap,
761: hr_lookups hrl
762: WHERE pap.accrual_plan_id = p_accrual_plan_id
763: AND pap.accrual_plan_element_type_id = piv.element_type_id
764: AND piv.name = hrl.meaning
785: --
786: BEGIN
787: --
788: if p_plan_name <> p_old_plan_name then
789: PAY_ACCRUAL_PLANS_PKG.CHK_PLAN_NAME(p_plan_name,
790: p_accrual_plan_id);
791: end if;
792: --
793: END update_validation;
817: if p_pto_input_value_id <> p_old_pto_input_value_id then
818: --
819: -- delete the old pto input value from the net calculation rules
820: --
821: hr_utility.set_location('pay_accrual_plans_pkg.post_update_actions',1);
822: delete from pay_net_calculation_rules
823: where input_value_id = p_old_pto_input_value_id
824: and accrual_plan_id = p_accrual_plan_id;
825: --
825: --
826: -- create a new net calculation rule for the new pto input value if one
827: -- does not already exist
828: --
829: hr_utility.set_location('pay_accrual_plans_pkg.post_update_actions',2);
830: insert into pay_net_calculation_rules(
831: net_calculation_rule_id,
832: accrual_plan_id,
833: business_group_id,
880: --
881: --
882: -- delete the accrual bands
883: --
884: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 1);
885: delete from pay_accrual_bands
886: where accrual_plan_id = p_accrual_plan_id;
887: --
888: -- delete the net calculation rules
886: where accrual_plan_id = p_accrual_plan_id;
887: --
888: -- delete the net calculation rules
889: --
890: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 2);
891: delete from pay_net_calculation_rules
892: where accrual_plan_id = p_accrual_plan_id;
893: --
894: -- delete the element types created for the plan
894: -- delete the element types created for the plan
895: --
896: -- first the accrual plan element type...
897: --
898: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 3);
899: hr_elements.chk_del_element_type (
900: 'ZAP',
901: p_accrual_plan_element_type_id,
902: '',
903: p_session_date,
904: l_effective_start_date,
905: l_effective_end_date);
906: --
907: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 4);
908: hr_elements.del_3p_element_type (
909: p_accrual_plan_element_type_id,
910: 'ZAP',
911: p_session_date,
912: l_effective_start_date,
913: l_effective_end_date,
914: '');
915: --
916: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',5);
917: delete from pay_element_types_f
918: where element_type_id = p_accrual_plan_element_type_id;
919: --
920: --
920: --
921: -- ...then the carried over element type...
922: --
923: --
924: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 6);
925: hr_elements.chk_del_element_type (
926: 'ZAP',
927: p_co_element_type_id,
928: '',
929: p_session_date,
930: l_effective_start_date,
931: l_effective_end_date);
932: --
933: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 7);
934: hr_elements.del_3p_element_type (
935: p_co_element_type_id,
936: 'ZAP',
937: p_session_date,
938: l_effective_start_date,
939: l_effective_end_date,
940: '');
941: --
942: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',8);
943: delete from pay_element_types_f
944: where element_type_id = p_co_element_type_id;
945: --
946: --
946: --
947: -- ...then the residual element type.
948: --
949: --
950: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 9);
951: hr_elements.chk_del_element_type (
952: 'ZAP',
953: p_residual_element_type_id,
954: '',
955: p_session_date,
956: l_effective_start_date,
957: l_effective_end_date);
958: --
959: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 10);
960: hr_elements.del_3p_element_type (
961: p_residual_element_type_id,
962: 'ZAP',
963: p_session_date,
964: l_effective_start_date,
965: l_effective_end_date,
966: '');
967: --
968: hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 11);
969: delete from pay_element_types_f
970: where element_type_id = p_residual_element_type_id;
971: --
972: END pre_delete_actions;
972: END pre_delete_actions;
973: --
974: --
975: --
976: END PAY_ACCRUAL_PLANS_PKG;