[Home] [Help]
64: update Assignment rows in
65: DO_PRIMARY_UPDATE
66: 70.23 11-MAR-93 NKHAN Added 'exit' to end of code
67: 70.24 25-MAR-93 JRHODES Added procedure 'tidy_up_ref_int'
68: 70.25 27-MAY-93 JRHODES PER_SECONDARY_ASS_STATUSES are now
69: removed when they start after an
70: assignment end date.
71: 70.26 02-JUN-93 JRHODES PER_SECONDARY_ASS_STATUSES are removed
72: totally on ZAP.
67: 70.24 25-MAR-93 JRHODES Added procedure 'tidy_up_ref_int'
68: 70.25 27-MAY-93 JRHODES PER_SECONDARY_ASS_STATUSES are now
69: removed when they start after an
70: assignment end date.
71: 70.26 02-JUN-93 JRHODES PER_SECONDARY_ASS_STATUSES are removed
72: totally on ZAP.
73: The ref int check on LETTER REQUESTS
74: is no longer required - they are
75: Auto Shut Down.
1117: Determines whether there are any dependent records for the Assignment.
1118: If any are found then delete them.
1119: The following tables are examined
1120: PER_SPINAL_POINT_PLACEMENTS
1121: PER_SECONDARY_ASS_STATUSES
1122: PER_ASSIGNMENT_BUDGET_VALUES
1123:
1124: PARAMETERS
1125: p_business_group_id - Business Group ID
1171: into del_flag
1172: from sys.dual
1173: WHERE EXISTS
1174: (SELECT NULL
1175: FROM PER_SECONDARY_ASS_STATUSES S
1176: WHERE S.business_group_id + 0 = p_business_group_id
1177: AND S.ASSIGNMENT_ID = p_assignment_id);
1178: --
1179: EXCEPTION
1182: --
1183: IF del_flag = 'Y' THEN
1184: hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
1185: --
1186: DELETE FROM PER_SECONDARY_ASS_STATUSES
1187: WHERE business_group_id + 0 = p_business_group_id
1188: AND ASSIGNMENT_ID = p_assignment_id;
1189: --
1190: END IF;
2540: --
2541: hr_utility.set_location('hr_assignment.del_ref_int_check',5);
2542: p_del_flag := 'N';
2543: /*
2544: N.B. PER_SECONDARY_ASS_STATUSES rows will now be deleted if they
2545: started after the Assignment End Date - changed 27/5/93.
2546: --
2547: BEGIN
2548: select 'Y'
2549: into p_del_flag
2550: from sys.dual
2551: where exists (
2552: select null
2553: from PER_SECONDARY_ASS_STATUSES
2554: where assignment_id = p_assignment_id
2555: and p_mode = 'ZAP');
2556: EXCEPTION
2557: WHEN NO_DATA_FOUND THEN NULL;
3433: into p_del_flag
3434: FROM SYS.DUAL
3435: WHERE EXISTS
3436: (SELECT NULL
3437: FROM PER_SECONDARY_ASS_STATUSES
3438: WHERE ASSIGNMENT_ID = p_assignment_id);
3439: --
3440: EXCEPTION
3441: WHEN NO_DATA_FOUND THEN NULL;
3443: --
3444: IF p_del_flag = 'Y' THEN
3445: --
3446: hr_utility.set_location('hr_assignment.delete_ass_ref_int',10);
3447: DELETE FROM PER_SECONDARY_ASS_STATUSES
3448: WHERE ASSIGNMENT_ID = p_assignment_id;
3449: END IF;
3450: --
3451: --
3620:
3621: The following tables are affected.
3622:
3623: PAY_COST_ALLOCATIONS_F
3624: PER_SECONDARY_ASS_STATUSES
3625: PAY_PERSONAL_PAYMENT_METHODS_F
3626: PER_ASSIGNMENT_BUDGET_VALUES_F
3627:
3628: PARAMETERS
3686: into p_del_flag
3687: from sys.dual
3688: where exists (
3689: select null
3690: from per_secondary_ass_statuses
3691: where assignment_id = p_assignment_id
3692: and ((p_mode = 'END'
3693: and p_new_end_date
3694: between START_DATE and nvl(END_DATE,
3704: IF p_del_flag = 'Y' THEN
3705: --
3706: hr_utility.set_location('hr_assignment.tidy_up_ref_int',2);
3707: --
3708: update per_secondary_ass_statuses
3709: set END_DATE = decode(p_new_end_date,to_date('31/12/4712','DD/MM/YYYY'),
3710: null,p_new_end_date)
3711: , last_updated_by = P_LAST_UPDATED_BY
3712: , last_update_login = P_LAST_UPDATE_LOGIN
3731: into p_del_flag
3732: from sys.dual
3733: where exists (
3734: select null
3735: from per_secondary_ass_statuses
3736: where assignment_id = p_assignment_id
3737: and p_mode = 'END'
3738: and p_new_end_date < START_DATE);
3739: EXCEPTION
3743: IF p_del_flag = 'Y' THEN
3744: --
3745: hr_utility.set_location('hr_assignment.tidy_up_ref_int',4);
3746: --
3747: delete from per_secondary_ass_statuses
3748: where assignment_id = p_assignment_id
3749: and p_mode = 'END'
3750: and p_new_end_date < START_DATE;
3751: END IF;