[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.
1147: Determines whether there are any dependent records for the Assignment.
1148: If any are found then delete them.
1149: The following tables are examined
1150: PER_SPINAL_POINT_PLACEMENTS
1151: PER_SECONDARY_ASS_STATUSES
1152: PER_ASSIGNMENT_BUDGET_VALUES
1153:
1154: PARAMETERS
1155: p_business_group_id - Business Group ID
1201: into del_flag
1202: from sys.dual
1203: WHERE EXISTS
1204: (SELECT NULL
1205: FROM PER_SECONDARY_ASS_STATUSES S
1206: WHERE S.business_group_id + 0 = p_business_group_id
1207: AND S.ASSIGNMENT_ID = p_assignment_id);
1208: --
1209: EXCEPTION
1212: --
1213: IF del_flag = 'Y' THEN
1214: hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
1215: --
1216: DELETE FROM PER_SECONDARY_ASS_STATUSES
1217: WHERE business_group_id + 0 = p_business_group_id
1218: AND ASSIGNMENT_ID = p_assignment_id;
1219: --
1220: END IF;
2577: --
2578: hr_utility.set_location('hr_assignment.del_ref_int_check',5);
2579: p_del_flag := 'N';
2580: /*
2581: N.B. PER_SECONDARY_ASS_STATUSES rows will now be deleted if they
2582: started after the Assignment End Date - changed 27/5/93.
2583: --
2584: BEGIN
2585: select 'Y'
2586: into p_del_flag
2587: from sys.dual
2588: where exists (
2589: select null
2590: from PER_SECONDARY_ASS_STATUSES
2591: where assignment_id = p_assignment_id
2592: and p_mode = 'ZAP');
2593: EXCEPTION
2594: WHEN NO_DATA_FOUND THEN NULL;
3473: into p_del_flag
3474: FROM SYS.DUAL
3475: WHERE EXISTS
3476: (SELECT NULL
3477: FROM PER_SECONDARY_ASS_STATUSES
3478: WHERE ASSIGNMENT_ID = p_assignment_id);
3479: --
3480: EXCEPTION
3481: WHEN NO_DATA_FOUND THEN NULL;
3483: --
3484: IF p_del_flag = 'Y' THEN
3485: --
3486: hr_utility.set_location('hr_assignment.delete_ass_ref_int',10);
3487: DELETE FROM PER_SECONDARY_ASS_STATUSES
3488: WHERE ASSIGNMENT_ID = p_assignment_id;
3489: END IF;
3490: --
3491: --
3660:
3661: The following tables are affected.
3662:
3663: PAY_COST_ALLOCATIONS_F
3664: PER_SECONDARY_ASS_STATUSES
3665: PAY_PERSONAL_PAYMENT_METHODS_F
3666: PER_ASSIGNMENT_BUDGET_VALUES_F
3667:
3668: PARAMETERS
3726: into p_del_flag
3727: from sys.dual
3728: where exists (
3729: select null
3730: from per_secondary_ass_statuses
3731: where assignment_id = p_assignment_id
3732: and ((p_mode = 'END'
3733: and p_new_end_date
3734: between START_DATE and nvl(END_DATE,
3744: IF p_del_flag = 'Y' THEN
3745: --
3746: hr_utility.set_location('hr_assignment.tidy_up_ref_int',2);
3747: --
3748: update per_secondary_ass_statuses
3749: set END_DATE = decode(p_new_end_date,to_date('31/12/4712','DD/MM/YYYY'),
3750: null,p_new_end_date)
3751: , last_updated_by = P_LAST_UPDATED_BY
3752: , last_update_login = P_LAST_UPDATE_LOGIN
3771: into p_del_flag
3772: from sys.dual
3773: where exists (
3774: select null
3775: from per_secondary_ass_statuses
3776: where assignment_id = p_assignment_id
3777: and p_mode = 'END'
3778: and p_new_end_date < START_DATE);
3779: EXCEPTION
3783: IF p_del_flag = 'Y' THEN
3784: --
3785: hr_utility.set_location('hr_assignment.tidy_up_ref_int',4);
3786: --
3787: delete from per_secondary_ass_statuses
3788: where assignment_id = p_assignment_id
3789: and p_mode = 'END'
3790: and p_new_end_date < START_DATE;
3791: END IF;