1 PACKAGE PO_VALIDATION_HELPER AS
2 -- $Header: PO_VALIDATION_HELPER.pls 120.8.12010000.2 2008/11/17 06:08:31 mugoel ship $
3
4 -- Input parameters for start_date_le_end_date.p_column_value_selector.
5 c_START_DATE CONSTANT VARCHAR2(30) := 'START_DATE';
6 c_END_DATE CONSTANT VARCHAR2(30) := 'END_DATE';
7
8 PROCEDURE greater_than_zero(
9 p_calling_module IN VARCHAR2
10 , p_null_allowed_flag IN VARCHAR2 DEFAULT NULL
11 , p_value_tbl IN PO_TBL_NUMBER
12 , p_entity_id_tbl IN PO_TBL_NUMBER
13 , p_entity_type IN VARCHAR2
14 , p_column_name IN VARCHAR2
15 , p_message_name IN VARCHAR2 DEFAULT NULL
16 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
17 , x_result_type OUT NOCOPY VARCHAR2
18 );
19
20 PROCEDURE greater_or_equal_zero(
21 p_calling_module IN VARCHAR2,
22 p_null_allowed_flag IN VARCHAR2 DEFAULT NULL,
23 p_value_tbl IN PO_TBL_NUMBER,
24 p_entity_id_tbl IN PO_TBL_NUMBER,
25 p_entity_type IN VARCHAR2,
26 p_column_name IN VARCHAR2,
27 p_message_name IN VARCHAR2 DEFAULT NULL,
28 p_token1_name IN VARCHAR2 DEFAULT NULL,
29 p_token1_value IN VARCHAR2 DEFAULT NULL,
30 p_token2_name IN VARCHAR2 DEFAULT NULL,
31 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
32 p_validation_id IN NUMBER DEFAULT NULL,
33 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
34 x_result_type OUT NOCOPY VARCHAR2);
35
36 PROCEDURE within_percentage_range(
37 p_calling_module IN VARCHAR2
38 , p_null_allowed_flag IN VARCHAR2 DEFAULT NULL
39 , p_value_tbl IN PO_TBL_NUMBER
40 , p_entity_id_tbl IN PO_TBL_NUMBER
41 , p_entity_type IN VARCHAR2
42 , p_column_name IN VARCHAR2
43 , p_message_name IN VARCHAR2 DEFAULT NULL
44 , p_token1_name IN VARCHAR2 DEFAULT NULL
45 , p_token1_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL
46 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
47 , x_result_type OUT NOCOPY VARCHAR2
48 );
49
50 PROCEDURE open_period(
51 p_calling_module IN VARCHAR2
52 , p_date_tbl IN PO_TBL_DATE
53 , p_org_id_tbl IN PO_TBL_NUMBER
54 , p_entity_id_tbl IN PO_TBL_NUMBER
55 , p_entity_type IN VARCHAR2
56 , p_column_name IN VARCHAR2
57 , p_message_name IN VARCHAR2
58 -- PBWC Message Change Impact: Adding a token
59 , p_token1_name IN VARCHAR2 DEFAULT NULL
60 , p_token1_value IN PO_TBL_NUMBER DEFAULT NULL
61 , x_result_set_id IN OUT NOCOPY NUMBER
62 , x_result_type OUT NOCOPY VARCHAR2
63 );
64
65 PROCEDURE not_null(p_calling_module IN VARCHAR2,
66 p_value_tbl IN PO_TBL_VARCHAR4000,
67 p_entity_id_tbl IN PO_TBL_NUMBER,
68 p_entity_type IN VARCHAR2,
69 p_column_name IN VARCHAR2,
70 p_message_name IN VARCHAR2,
71 p_token1_name IN VARCHAR2 DEFAULT NULL,
72 p_token1_value IN VARCHAR2 DEFAULT NULL,
73 p_token2_name IN VARCHAR2 DEFAULT NULL,
74 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
75 p_validation_id IN NUMBER DEFAULT NULL,
76 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
77 x_result_type OUT NOCOPY VARCHAR2);
78
79 PROCEDURE ensure_null(p_calling_module IN VARCHAR2,
80 p_value_tbl IN PO_TBL_VARCHAR4000,
81 p_entity_id_tbl IN PO_TBL_NUMBER,
82 p_entity_type IN VARCHAR2,
83 p_column_name IN VARCHAR2,
84 p_message_name IN VARCHAR2,
85 p_token1_name IN VARCHAR2 DEFAULT NULL,
86 p_token1_value IN VARCHAR2 DEFAULT NULL,
87 p_token2_name IN VARCHAR2 DEFAULT NULL,
88 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
89 p_validation_id IN NUMBER DEFAULT NULL,
90 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
91 x_result_type OUT NOCOPY VARCHAR2);
92
93 PROCEDURE flag_value_Y_N(p_calling_module IN VARCHAR2,
94 p_flag_value_tbl IN PO_TBL_VARCHAR1,
95 p_entity_id_tbl IN PO_TBL_NUMBER,
96 p_entity_type IN VARCHAR2,
97 p_column_name IN VARCHAR2,
98 p_message_name IN VARCHAR2,
99 p_token1_name IN VARCHAR2 DEFAULT NULL,
100 p_token1_value IN VARCHAR2 DEFAULT NULL,
101 p_token2_name IN VARCHAR2 DEFAULT NULL,
102 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
103 p_validation_id IN NUMBER DEFAULT NULL,
104 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
105 x_result_type OUT NOCOPY VARCHAR2);
106
107 PROCEDURE gt_zero_order_type_filter(
108 p_calling_module IN VARCHAR2
109 , p_value_tbl IN PO_TBL_NUMBER
110 , p_entity_id_tbl IN PO_TBL_NUMBER
111 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
112 , p_check_quantity_types_flag IN VARCHAR2
113 , p_entity_type IN VARCHAR2
114 , p_column_name IN VARCHAR2
115 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
116 , x_result_type OUT NOCOPY VARCHAR2
117 );
118
119 PROCEDURE no_timecards_exist(
120 p_calling_module IN VARCHAR2
121 , p_line_id_tbl IN PO_TBL_NUMBER
122 , p_start_date_tbl IN PO_TBL_DATE DEFAULT NULL
123 , p_expiration_date_tbl IN PO_TBL_DATE DEFAULT NULL
124 , p_column_name IN VARCHAR2
125 , p_message_name IN VARCHAR2
126 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
127 , x_result_type OUT NOCOPY VARCHAR2
128 );
129
130 PROCEDURE amount_notif_ctrl_warning(
131 p_calling_module IN VARCHAR2
132 , p_line_id_tbl IN PO_TBL_NUMBER
133 , p_quantity_tbl IN PO_TBL_NUMBER
134 , p_column_name IN VARCHAR2
135 , p_message_name IN VARCHAR2
136 , x_result_set_id IN OUT NOCOPY NUMBER
137 , x_result_type OUT NOCOPY VARCHAR2
138 );
139
140 PROCEDURE child_num_unique(
141 p_calling_module IN VARCHAR2
142 , p_entity_type IN VARCHAR2
143 , p_entity_id_tbl IN PO_TBL_NUMBER
144 , p_parent_id_tbl IN PO_TBL_NUMBER
145 , p_entity_num_tbl IN PO_TBL_NUMBER
146 , x_result_set_id IN OUT NOCOPY NUMBER
147 , x_result_type OUT NOCOPY VARCHAR2
148 , p_entity_type_tbl IN PO_TBL_VARCHAR30 DEFAULT NULL -- <Complex Work R12>
149 );
150
151 PROCEDURE price_diff_value_unique(
152 p_calling_module IN VARCHAR2
153 , p_price_diff_id_tbl IN PO_TBL_NUMBER
154 , p_entity_id_tbl IN PO_TBL_NUMBER
155 , p_entity_type_tbl IN PO_TBL_VARCHAR30
156 , p_unique_value_tbl IN PO_TBL_VARCHAR4000
157 , p_column_name IN VARCHAR2
158 , p_message_name IN VARCHAR2
159 , x_result_set_id IN OUT NOCOPY NUMBER
160 , x_result_type OUT NOCOPY VARCHAR2
161 );
162
163 PROCEDURE start_date_le_end_date(
164 p_calling_module IN VARCHAR2
165 , p_start_date_tbl IN PO_TBL_DATE
166 , p_end_date_tbl IN PO_TBL_DATE
167 , p_entity_id_tbl IN PO_TBL_NUMBER
168 , p_entity_type IN VARCHAR2
169 , p_column_name IN VARCHAR2
170 , p_column_val_selector IN VARCHAR2
171 , p_message_name IN VARCHAR2
172 , p_validation_id IN NUMBER DEFAULT NULL
173 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
174 , x_result_type OUT NOCOPY VARCHAR2
175 );
176
177 PROCEDURE num1_less_or_equal_num2(
178 p_calling_module IN VARCHAR2
179 , p_num1_tbl IN PO_TBL_NUMBER
180 , p_num2_tbl IN PO_TBL_NUMBER
181 , p_entity_id_tbl IN PO_TBL_NUMBER
182 , p_entity_type IN VARCHAR2
183 , p_column_name IN VARCHAR2
184 , p_message_name IN VARCHAR2
185 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
186 , x_result_type OUT NOCOPY VARCHAR2
187 );
188
189 PROCEDURE zero(p_calling_module IN VARCHAR2,
190 p_value_tbl IN PO_TBL_NUMBER,
191 p_entity_id_tbl IN PO_TBL_NUMBER,
192 p_entity_type IN VARCHAR2,
193 p_column_name IN VARCHAR2,
194 p_message_name IN VARCHAR2,
195 p_token1_name IN VARCHAR2 DEFAULT NULL,
196 p_token1_value IN VARCHAR2 DEFAULT NULL,
197 p_token2_name IN VARCHAR2 DEFAULT NULL,
198 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
199 p_validation_id IN NUMBER DEFAULT NULL,
200 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
201 x_result_type OUT NOCOPY VARCHAR2);
202
203 PROCEDURE terms_id(p_calling_module IN VARCHAR2,
204 p_terms_id_tbl IN PO_TBL_NUMBER,
205 p_entity_id_tbl IN PO_TBL_NUMBER,
206 p_entity_type IN VARCHAR2,
207 p_validation_id IN NUMBER DEFAULT NULL,
208 x_result_set_id IN OUT NOCOPY NUMBER,
209 x_result_type OUT NOCOPY VARCHAR2);
210
211 PROCEDURE gt_zero_opm_filter(
212 p_calling_module IN VARCHAR2
213 , p_value_tbl IN PO_TBL_NUMBER
214 , p_entity_id_tbl IN PO_TBL_NUMBER
215 , p_item_id_tbl IN PO_TBL_NUMBER
216 , p_inv_org_id_tbl IN PO_TBL_NUMBER
217 , p_entity_type IN VARCHAR2
218 , p_column_name IN VARCHAR2
219 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
220 , x_result_type OUT NOCOPY VARCHAR2
221 );
222
223 PROCEDURE qtys_within_deviation(
224 p_calling_module IN VARCHAR2
225 , p_entity_id_tbl IN PO_TBL_NUMBER
226 , p_item_id_tbl IN PO_TBL_NUMBER
227 , p_inv_org_id_tbl IN PO_TBL_NUMBER
228 , p_quantity_tbl IN PO_TBL_NUMBER
229 , p_primary_uom_tbl IN PO_TBL_VARCHAR30
230 , p_sec_quantity_tbl IN PO_TBL_NUMBER
231 , p_secondary_uom_tbl IN PO_TBL_VARCHAR30
232 , p_column_name IN VARCHAR2
233 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
234 , x_result_type OUT NOCOPY VARCHAR2
235 );
236
237 PROCEDURE secondary_unit_of_measure(
238 p_id_tbl IN po_tbl_number,
239 p_entity_type IN VARCHAR2,
240 p_secondary_unit_of_meas_tbl IN po_tbl_varchar30,
241 p_item_id_tbl IN po_tbl_number,
242 p_item_tbl IN po_tbl_varchar2000,
243 p_organization_id_tbl IN po_tbl_number,
244 p_doc_type IN VARCHAR2,
245 p_create_or_update_item_flag IN VARCHAR2,
246 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
247 x_result_type OUT NOCOPY VARCHAR2);
248
249 PROCEDURE secondary_quantity(
250 p_id_tbl IN po_tbl_number,
251 p_entity_type IN VARCHAR2,
252 p_secondary_quantity_tbl IN po_tbl_number,
253 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
254 p_item_id_tbl IN po_tbl_number,
255 p_item_tbl IN po_tbl_varchar2000,
256 p_organization_id_tbl IN po_tbl_number,
257 p_doc_type IN VARCHAR2,
258 p_create_or_update_item_flag IN VARCHAR2,
259 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
260 x_result_type OUT NOCOPY VARCHAR2);
261
262 PROCEDURE secondary_uom_update(
263 p_id_tbl IN po_tbl_number,
264 p_entity_type IN VARCHAR2,
265 p_secondary_unit_of_meas_tbl IN po_tbl_varchar30,
266 p_item_id_tbl IN po_tbl_number,
267 p_organization_id_tbl IN po_tbl_number,
268 p_create_or_update_item_flag IN VARCHAR2,
269 x_result_set_id IN OUT NOCOPY NUMBER,
270 x_result_type OUT NOCOPY VARCHAR2);
271
272 PROCEDURE preferred_grade(
273 p_id_tbl IN po_tbl_number,
274 p_entity_type IN VARCHAR2,
278 p_organization_id_tbl IN po_tbl_number,
275 p_preferred_grade_tbl IN po_tbl_varchar2000,
276 p_item_id_tbl IN po_tbl_number,
277 p_item_tbl IN po_tbl_varchar2000,
279 p_create_or_update_item_flag IN VARCHAR2,
280 p_validation_id IN NUMBER DEFAULT NULL,
281 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
282 x_result_set_id IN OUT NOCOPY NUMBER,
283 x_result_type OUT NOCOPY VARCHAR2);
284
285 PROCEDURE process_enabled(
286 p_id_tbl IN po_tbl_number,
287 p_entity_type IN VARCHAR2,
288 p_ship_to_organization_id_tbl IN po_tbl_number,
289 p_item_id_tbl IN po_tbl_number,
290 x_result_set_id IN OUT NOCOPY NUMBER,
291 x_result_type OUT NOCOPY VARCHAR2);
292
293 -- Bug 7516824: New procedure added
294 PROCEDURE validate_desc_flex (
295 p_calling_module IN VARCHAR2,
296 p_id_tbl IN po_tbl_number,
297 p_desc_flex_name IN fnd_descr_flex_column_usages.descriptive_flexfield_name%TYPE,
298 p_attribute_category_tbl IN PO_TBL_VARCHAR30,
299 p_attribute1_tbl IN PO_TBL_VARCHAR2000,
300 p_attribute2_tbl IN PO_TBL_VARCHAR2000,
301 p_attribute3_tbl IN PO_TBL_VARCHAR2000,
302 p_attribute4_tbl IN PO_TBL_VARCHAR2000,
303 p_attribute5_tbl IN PO_TBL_VARCHAR2000,
304 p_attribute6_tbl IN PO_TBL_VARCHAR2000,
305 p_attribute7_tbl IN PO_TBL_VARCHAR2000,
306 p_attribute8_tbl IN PO_TBL_VARCHAR2000,
307 p_attribute9_tbl IN PO_TBL_VARCHAR2000,
308 p_attribute10_tbl IN PO_TBL_VARCHAR2000,
309 p_attribute11_tbl IN PO_TBL_VARCHAR2000,
310 p_attribute12_tbl IN PO_TBL_VARCHAR2000,
311 p_attribute13_tbl IN PO_TBL_VARCHAR2000,
312 p_attribute14_tbl IN PO_TBL_VARCHAR2000,
313 p_attribute15_tbl IN PO_TBL_VARCHAR2000,
314 p_entity_type IN VARCHAR2,
315 x_results IN OUT NOCOPY po_validation_results_type,
316 x_result_type OUT NOCOPY VARCHAR2);
317
318 END PO_VALIDATION_HELPER;