1 PACKAGE per_ric_pkg AUTHID CURRENT_USER AS
2 /* $Header: pericpkg.pkh 120.1 2011/04/28 18:33:41 sidsaxen noship $ */
3
4 -- New type to capture column information.
5 TYPE column_info_rec IS RECORD (
6 column_NAME VARCHAR2(120),
7 value_VARCHAR VARCHAR2(120),
8 value_NUMBER NUMBER,
9 value_DATE DATE );
10
11 TYPE column_info_tbl
12 IS TABLE OF column_info_rec;
13
14 -- New type to capture referenced table information.
15 TYPE ref_entity_rec is record (
16 ref_entity varchar2(240),
17 column_info column_info_tbl);
18
19 TYPE ref_entity_tbl
20 IS TABLE OF ref_entity_rec;
21
22 g_column_info_tbl per_ric_pkg.column_info_tbl := per_ric_pkg.column_info_tbl();
23 g_ref_entity_tbl per_ric_pkg.ref_entity_tbl := per_ric_pkg.ref_entity_tbl();
24
25 --
26 -- ----------------------------------------------------------------------------
27 -- |-----------------------------< col_info_rec >-----------------------------|
28 -- ----------------------------------------------------------------------------
29 -- {Start Of Comments}
30 --
31 -- Description:
32 -- This function is used to turn attribute arguments into the record
33 -- structure column_info_rec.
34 --
35 -- Pre Conditions:
36 -- This is a public function and can only be called to build up record type column_info_rec.
37 --
38 -- In Arguments:
39 --
40 -- Post Success:
41 -- A returning record structure will be returned.
42 --
43 -- Post Failure:
44 -- No direct error handling is required within this function. Any possible
45 -- errors within this function will be a PL/SQL value error due to conversion
46 -- of datatypes or data lengths.
47 --
48 -- Developer Implementation Notes:
49 -- None.
50 --
51 -- Access Status:
52 -- Internal Table Handler Use Only.
53 --
54 -- {End Of Comments}
55
56 FUNCTION col_info_rec(
57 column_name IN VARCHAR2,
58 value_varchar IN VARCHAR2 default null,
59 value_number IN NUMBER default null,
60 value_date IN DATE default null)
61 return column_info_rec;
62
63 --
64 -- ----------------------------------------------------------------------------
65 -- |-----------------------------< ref_info_rec >-----------------------------|
66 -- ----------------------------------------------------------------------------
67 -- {Start Of Comments}
68 --
69 -- Description:
70 -- This function is used to turn attribute arguments into the record
71 -- structure ref_entity_rec.
72 --
73 -- Pre Conditions:
74 -- This is a public function and can only be called to build up record type ref_entity_rec.
75 --
76 -- In Arguments:
77 --
78 -- Post Success:
79 -- A returning record structure will be returned.
80 --
81 -- Post Failure:
82 -- No direct error handling is required within this function. Any possible
83 -- errors within this function will be a PL/SQL value error due to conversion
84 -- of datatypes or data lengths.
85 --
86 -- Developer Implementation Notes:
87 -- None.
88 --
89 -- Access Status:
90 -- Internal Table Handler Use Only.
91 --
92 -- {End Of Comments}
93 function ref_info_rec(
94 p_ref_entity_name IN VARCHAR2,
95 p_column_info_rec IN column_info_tbl)
96 return ref_entity_rec;
97
98 -- ---------------------------------------------------------------------------+
99 -- |--------------------------< chk_integrity >-------------------------------|
100 -- ---------------------------------------------------------------------------+
101 -- Description:
102 -- To check the referencial integrity of an entity.
103 --
104 -- Pre Conditions:
105 -- A variable of ref_entity_tbl table type has been populated with details of the entities need to check
106 -- for the mail entry.
107 --
108 -- In Parameters:
109 -- Name Reqd Type Description
110 -- p_entity_NAME Yes varchar2 Name of the entity
111 -- for which the reference check will execute.
112 -- p_ref_entity_info Yes ref_entity_tbl This pl-sql table contains the information
113 -- of the entities which are used to maintain
114 -- the referencial relation with the main entity.
115 -- p_ref_type Yes varchar2 Define the type of referential integrity check,
116 -- either 'INS' or 'DEL'.
117 -- p_integrity_error Yes VARCHAR2 If this parameter is passed 'Y' then the procedure
118 -- will return error message in the same parameter
119 -- else the procedure will raise an error if the
120 -- referential integrity voilates.
121 --
122 -- Post Success:
123 -- No error will be raised.
124 --
125 -- Post Failure:
126 -- An error is raised if the correct reference has not found.
127 --
128 -- Developer Implementation Notes:
129 -- For the 'Zero Down Time' project, Foreign Key constraints on the seed tables are deleted.
130 -- So this procedure is created to maintain the integrity of the seed tables.
131 --
132 -- All the input parameters are mandatory. An error will be raised if any
133 -- input parameter is passed as NULL.
134 --
135 -- All the reference entities passed to parameter 'p_ref_entities' must
136 -- exist in the database. If any entity does not exist this procedure will
137 -- raise an error.
138 --
139 -- Once above checks are passed, SQL statements are prepared and executed
140 -- to check the referencial integrity.
141 --
142 -- If the above executed SQL fetches the results then the validation is done
143 -- whether the entity is being checked for INS/DEL operation and then this will
144 -- raise an error accordingly(if any).
145 --
146 -- Access Status:
147 -- Internal Development Use Only.
148
149 -- If there is only 1 reference entity and 1 column.
150 PROCEDURE chk_integrity (
151 p_entity_name IN VARCHAR2,
152 p_ref_entity IN varchar2,
153 p_ref_column_name IN varchar2,
154 p_ref_col_value_number IN number default null,
155 p_ref_col_value_varchar IN varchar2 default null,
156 p_ref_col_value_date IN date default null,
157 p_ref_type IN VARCHAR2);
158
159 -- If there is only 1 reference entity and 1 column and the error name is needed in the OUT parameter.
160 PROCEDURE chk_integrity (
161 p_entity_name IN VARCHAR2,
162 p_ref_entity IN varchar2,
163 p_ref_column_name IN varchar2,
164 p_ref_col_value_number IN number default null,
165 p_ref_col_value_varchar IN number default null,
166 p_ref_col_value_date IN date default null,
167 p_ref_type IN VARCHAR2,
168 p_integrity_error IN OUT nocopy VARCHAR2);
169
170 -- If there is 1 reference entity and more than 1 column.
171 PROCEDURE chk_integrity (
172 p_entity_name IN VARCHAR2,
173 p_ref_entity IN varchar2,
174 p_ref_column_info IN column_info_tbl,
175 p_ref_type IN VARCHAR2);
176
177 -- If there is 1 reference entity and more than 1 column and the error name is needed in the OUT parameter.
178 PROCEDURE chk_integrity (
179 p_entity_name IN VARCHAR2,
180 p_ref_entity IN varchar2,
181 p_ref_column_info IN column_info_tbl,
182 p_ref_type IN VARCHAR2,
183 p_integrity_error IN OUT nocopy VARCHAR2);
184
185 -- If there are more than 1 reference entity and more than 1 column.
186 PROCEDURE chk_integrity (
187 p_entity_name IN VARCHAR2,
188 p_ref_entity_info IN ref_entity_tbl,
189 p_ref_type IN VARCHAR2);
190
191 -- If there are more than 1 reference entity and more than 1 column and the error name is needed in the OUT parameter..
192 PROCEDURE chk_integrity (
193 p_entity_name IN VARCHAR2,
194 p_ref_entity_info IN ref_entity_tbl,
195 p_ref_type IN VARCHAR2,
196 p_integrity_error IN OUT nocopy VARCHAR2);
197
198 END per_ric_pkg;
[Home] [Help]