1 PACKAGE AHL_SB_RULES_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVSBRS.pls 120.2.12020000.2 2012/12/07 14:38:10 sareepar noship $ */
3
4 -- NOTE:The following constants and the two record and table type variables are not used as of now.
5 -- Written for future purposes, if any.
6
7 -- Operations
8 G_OP_CREATE CONSTANT VARCHAR2(1) := 'C';
9 G_OP_UPDATE CONSTANT VARCHAR2(1) := 'U';
10 G_OP_DELETE CONSTANT VARCHAR2(1) := 'D';
11
12 -- record structure for the DB table AHL_SB_POSITION_RULES
13 TYPE SB_Rule_Rec_Type IS RECORD (
14 rule_id NUMBER,
15 operation_flag VARCHAR2(1),
16 object_version_number NUMBER,
17 last_update_date DATE,
18 last_updated_by NUMBER,
19 creation_date DATE,
20 created_by NUMBER,
21 last_update_login NUMBER,
22 rule_name VARCHAR2(80),
23 mr_header_id NUMBER,
24 mc_header_id NUMBER,
25 relationship_id NUMBER,
26 rule_sequence NUMBER,
27 active_start_date DATE,
28 active_end_date DATE,
29 security_group_id NUMBER,
30 attribute_category VARCHAR2(30),
31 attribute1 VARCHAR2(150),
32 attribute2 VARCHAR2(150),
33 attribute3 VARCHAR2(150),
34 attribute4 VARCHAR2(150),
35 attribute5 VARCHAR2(150),
36 attribute6 VARCHAR2(150),
37 attribute7 VARCHAR2(150),
38 attribute8 VARCHAR2(150),
39 attribute9 VARCHAR2(150),
40 attribute10 VARCHAR2(150),
41 attribute11 VARCHAR2(150),
42 attribute12 VARCHAR2(150),
43 attribute13 VARCHAR2(150),
44 attribute14 VARCHAR2(150),
45 attribute15 VARCHAR2(150)
46 );
47
48 -- table type for the above record structure
49 TYPE SB_Rule_Tbl_Type IS TABLE OF SB_Rule_Rec_Type
50 INDEX BY BINARY_INTEGER;
51
52 -- record structure for the DB table AHL_SB_RULE_ITEMS
53 TYPE SB_Rule_Items_Rec_Type IS RECORD (
54 rule_association_id NUMBER,
55 operation_flag VARCHAR2(1),
56 object_version_number NUMBER,
57 last_update_date DATE,
58 last_updated_by NUMBER,
59 creation_date DATE,
60 created_by NUMBER,
61 last_update_login NUMBER,
62 rule_id NUMBER,
63 item_group_id NUMBER,
64 item_association_id NUMBER,
65 sub_config_item VARCHAR2(1),
66 security_group_id NUMBER,
67 attribute_category VARCHAR2(30),
68 attribute1 VARCHAR2(150),
69 attribute2 VARCHAR2(150),
70 attribute3 VARCHAR2(150),
71 attribute4 VARCHAR2(150),
72 attribute5 VARCHAR2(150),
73 attribute6 VARCHAR2(150),
74 attribute7 VARCHAR2(150),
75 attribute8 VARCHAR2(150),
76 attribute9 VARCHAR2(150),
77 attribute10 VARCHAR2(150),
78 attribute11 VARCHAR2(150),
79 attribute12 VARCHAR2(150),
80 attribute13 VARCHAR2(150),
81 attribute14 VARCHAR2(150),
82 attribute15 VARCHAR2(150)
83 );
84
85 -- table type for the above record structure
86 TYPE SB_Rule_Items_Tbl_Type IS TABLE OF SB_Rule_Items_Rec_Type
87 INDEX BY BINARY_INTEGER;
88
89 -- table type of NUMBER, used to store SB rules' violated instance ids in a unit
90 TYPE Violated_Inst_Tbl_Type IS TABLE OF NUMBER
91 INDEX BY BINARY_INTEGER;
92
93 -- table type of NUMBER, used to store affected MC header ids for an MR
94 TYPE Affected_MC_Tbl_Type IS TABLE OF NUMBER
95 INDEX BY BINARY_INTEGER;
96
97 ------------------------------------------------------------------------------------
98 -- Start of Comments
99 -- Procedure name : Check_Unit_Completeness
100 -- Type : Public
101 -- Function : If Visit id is given:
102 -- - it's a call from workorder UIs
103 -- - along with the MRs accomplished on the unit, consider the ones attached to the visit too
104 -- Else:
105 -- - it's a call from UC Validate UI
106 -- - consider just the MRs accomplished on the unit
107 -- Gives the error messages pointing to the erring rules.
108 -- Pre-reqs :
109 -- Parameters :
110 --
111 -- Check_Unit_Completeness Parameters:
112 -- p_uc_header_id IN Given unit header id. Required
113 -- p_visit_id IN Given visit id. Required
114 --
115 -- End of Comments
116
117 PROCEDURE Check_Unit_Completeness (
118 p_api_version IN NUMBER := 1.0,
119 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
120 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
121 p_module_type IN VARCHAR2 := NULL,
122 p_uc_header_id IN NUMBER,
123 p_visit_id IN NUMBER,
124 p_x_erring_rules_tbl IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2
128 );
129 ------------------------------------------------------------------------------------
130
131 ------------------------------------------------------------------------------------
132 -- Start of Comments
133 -- Procedure name : Check_Inst_Completeness
134 -- Type : Public
135 -- Function : Checks for the validity of applicable SB rules, if any, for a given instance.
136 -- Gives the error messages pointing to the erring rules.
137 -- Pre-reqs :
138 -- Parameters :
139 --
140 -- Check_Inst_Completeness Parameters:
141 -- p_csi_instance_id IN Given instance id. Required
142 --
143 -- End of Comments
144
145 PROCEDURE Check_Inst_Completeness (
146 p_api_version IN NUMBER := 1.0,
147 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
148 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
149 p_module_type IN VARCHAR2 := NULL,
150 p_csi_instance_id IN NUMBER,
151 p_x_erring_rules_tbl IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2
155 );
156 ------------------------------------------------------------------------------------
157
158 ------------------------------------------------------------------------------------
159 -- Start of Comments
160 -- Procedure name : Evaluate_Rules_For_Pos
161 -- Type : Public
162 -- Function : Evaluates SB rules for a given MC position and matching unit in the given unit.
163 -- Puts the erring rules in the message stack. Calling APIs need to check the message stack count.
164 -- Pre-reqs :
165 -- Parameters :
166 --
167 -- Evaluate_Rules_For_Pos Parameters:
168 -- p_mc_header_id IN Given MC header id. Required
169 -- p_mc_pos_key IN Given MC position key. Required
170 -- p_inv_item_id IN Given inventory item id. Required
171 -- p_inv_org_id IN Given inventory org id. Required
172 -- p_uc_header_id IN Given unit header id. Required
173 --
174 -- End of Comments
175
176 PROCEDURE Evaluate_Rules_For_Pos (
177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178 p_mc_header_id IN NUMBER,
179 p_mc_pos_key IN NUMBER,
180 p_inv_item_id IN NUMBER,
181 p_inv_org_id IN NUMBER,
182 p_uc_header_id IN NUMBER
183 );
184 ------------------------------------------------------------------------------------
185
186 ------------------------------------------------------------------------------------
187 -- Start of Comments
188 -- Procedure name : Copy_Rules_For_MR
189 -- Type : Public
190 -- Function : Copies the SB rules from a source MR (original) to destination MR (revised).
191 -- NOTE: Translated string for 'Copy' will be prefixed to the rule name.
192 -- Pre-reqs :
193 -- Parameters :
194 --
195 -- Copy_Rules_For_MR Parameters:
196 -- p_src_mr_header_id IN Given source MR header id. Required
197 -- p_dst_mr_header_id IN Given destination MR header id. Required
198 --
199 -- End of Comments
200
201 PROCEDURE Copy_Rules_For_MR (
202 p_api_version IN NUMBER := 1.0,
203 p_commit IN VARCHAR2 := FND_API.G_FALSE,
204 p_src_mr_header_id IN NUMBER,
205 p_dst_mr_header_id IN NUMBER,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2
209 );
210 ------------------------------------------------------------------------------------
211
212 ------------------------------------------------------------------------------------
213 -- Start of Comments
214 -- Procedure name : Update_Rules_For_MR
215 -- Type : Public
216 -- Function : Updates the SB rules for a given MR. To be called during MR completion, to
217 -- update any non-effective rules of the edited DRAFT version.
218 -- Pre-reqs :
219 -- Parameters :
220 --
221 -- Update_Rules_For_MR Parameters:
222 -- p_mr_header_id IN Given MR header id. Required
223 --
224 -- End of Comments
225
226 PROCEDURE Update_Rules_For_MR (
227 p_api_version IN NUMBER := 1.0,
228 p_commit IN VARCHAR2 := FND_API.G_FALSE,
229 p_mr_header_id IN NUMBER,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2
233 );
234 ------------------------------------------------------------------------------------
235
236 ------------------------------------------------------------------------------------
237 -- Start of Comments
238 -- Procedure name : Delete_Rules_For_MR
239 -- Type : Public
240 -- Function : Deletes the SB rules for a given MR. To be called during MR deletion, to
241 -- delete all the rules for the version getting deleted.
242 -- Pre-reqs :
243 -- Parameters :
244 --
245 -- Update_Rules_For_MR Parameters:
246 -- p_mr_header_id IN Given MR header id. Required
247 --
248 -- End of Comments
249
250 PROCEDURE Delete_Rules_For_MR (
251 p_api_version IN NUMBER := 1.0,
252 p_commit IN VARCHAR2 := FND_API.G_FALSE,
253 p_mr_header_id IN NUMBER,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2
257 );
258 ------------------------------------------------------------------------------------
259
260 ------------------------------------------------------------------------------------
261 -- Start of Comments
262 -- Procedure name : Update_Rules_For_IG
263 -- Type : Public
264 -- Function : Updates the SB rules for a given Item Group. To be called during item group completion, to
265 -- update SB rules like this:
266 -- 1) Fork: Deletes the rule items for the old item group and the affected positions.
267 -- 2) Merge: Deletes the rule items for the removed items in the item group.
268 -- If there are no items in the SB rule after the above, then deletes the rule too.
269 -- Pre-reqs :
270 -- Parameters :
271 --
272 -- Update_Rules_For_IG Parameters:
273 -- p_frk_or_mrg_flg IN Fork or merge indicator ('F' / 'M'). Required
274 -- p_item_group_id IN Given item group id. Required
275 -- p_src_item_group_id IN Source item group id for the given item group. Required
276 --
277 -- End of Comments
278
279 PROCEDURE Update_Rules_For_IG (
280 p_frk_or_mrg_flg IN VARCHAR2,
281 p_item_group_id IN NUMBER,
282 p_src_item_group_id IN NUMBER
283 );
284 ------------------------------------------------------------------------------------
285
286 ------------------------------------------------------------------------------------
287 -- Start of Comments
288 -- Procedure name : Get_Violated_Instances
289 -- Type : Public
290 -- Function : Gets the SB rules' violated instance ids for a given unit, in an associative array.
291 -- Pre-reqs :
292 -- Parameters :
293 --
294 -- Get_Violated_Instances Parameters:
295 -- p_uc_header_id IN Given unit header id. Required
296 --
297 -- End of Comments
298
299 PROCEDURE Get_Violated_Instances (
300 p_api_version IN NUMBER := 1.0,
301 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
302 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
303 p_module_type IN VARCHAR2 := NULL,
304 p_uc_header_id IN NUMBER,
305 x_violated_inst_tbl OUT NOCOPY Violated_Inst_Tbl_Type,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2
309 );
310 ------------------------------------------------------------------------------------
311
312 ------------------------------------------------------------------------------------
313 -- Start of Comments
314 -- Procedure name : Get_Affected_MC
315 -- Type : Public
316 -- Function : Gets the affected MC header ids for a given MR
317 -- Pre-reqs :
318 -- Parameters :
319 --
320 -- Get_Affected_MC Parameters:
321 -- p_mr_header_id IN Given MR header id. Required
322 --
323 -- End of Comments
324
325 PROCEDURE Get_Affected_MC (
326 p_api_version IN NUMBER := 1.0,
327 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
328 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
329 p_module_type IN VARCHAR2 := NULL,
330 p_mr_header_id IN NUMBER,
331 x_affected_mc_tbl OUT NOCOPY Affected_MC_Tbl_Type,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_msg_count OUT NOCOPY NUMBER,
334 x_msg_data OUT NOCOPY VARCHAR2
335 );
336 ------------------------------------------------------------------------------------
337
338 END AHL_SB_RULES_PVT;