1 package body BEN_BENEFIT_CONTRIBUTIONS_PKG as
2 /* $Header: pybco.pkb 115.0 99/07/17 05:45:27 porting ship $ */
3 ------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1994 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved |
9 +==============================================================================+
10
11 Name
12 Benefit Contributions Table Handler
13 Purpose
14 To interface with the Benefit Contributions entity and maintain its
15 integrity
16 History
17 27 Jan 94 N Simpson Created
18 */
19 --------------------------------------------------------------------------------
20 procedure PARENT_DELETED (
21 --
22 --***************************************************************************
23 --* Handles the case when a referenced master entity has one of its records *
24 --* deleted or shut down *
25 --***************************************************************************
26 --
27 -- Parameters are:
28 --
29 -- Identifier of parent record
30 p_parent_id number,
31 --
32 p_delete_mode varchar2 default 'DELETE',
33 p_session_date date default trunc(sysdate),
34 --
35 -- Name of parent entity from which a deletion has been made
36 p_parent_name varchar2
37 --
38 ) is
39 --
40 cursor csr_orphaned_rows is
41 --
42 /* Returns the set of rows whose foreign key references
43 the deleted record. The decode determines which
44 foreign key is used, thus the cursor applies to any
45 parent entity. */
46 --
47 select *
48 from ben_benefit_contributions_f
49 where p_parent_id = decode (p_parent_name,
50 'PAY_ELEMENT_TYPES_F', element_type_id,
51 'PER_BUSINESS_GROUPS', business_group_id,
52 'PER_COBRA_COVERAGE_BENEFITS_F',coverage_type)
53 for update;
54 --
55 begin
56 --
57 <<REMOVE_ORPHANED_ROWS>>
58 for fetched_benefit_contribution in csr_orphaned_rows loop
59 --
60 -- For ZAP deletions, all child records must be deleted
61 -- For date effective deletions, all children who start after the date
62 -- of effective deletion must be deleted
63 --
64 if p_delete_mode = 'ZAP'
65 or (p_delete_mode = 'DELETE'
66 and p_session_date < fetched_benefit_contribution.effective_start_date )
67 then
68 --
69 delete from ben_benefit_contributions_f
70 where current of csr_orphaned_rows;
71 --
72 -- For date effective deletions, the current children of the now closed parent
73 -- must have their end dates updated to match that of the parent
74 --
75 elsif (p_delete_mode = 'DELETE'
76 and p_session_date between fetched_benefit_contribution.effective_start_date
77 and fetched_benefit_contribution.effective_end_date)
78 then
79 --
80 update ben_benefit_contributions_f
81 set effective_end_date = p_session_date
82 where current of csr_orphaned_rows;
83 --
84 end if;
85 --
86 end loop remove_orphaned_rows;
87 --
88 end parent_deleted;
89 --------------------------------------------------------------------------------
90 end BEN_BENEFIT_CONTRIBUTIONS_PKG;
[Home] [Help]