1 PACKAGE PO_VALIDATION_HELPER AUTHID CURRENT_USER AS
2 -- $Header: PO_VALIDATION_HELPER.pls 120.14.12020000.6 2013/04/03 22:23:40 rarajar 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_clm_info_flag_tbl IN PO_TBL_VARCHAR1
113 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30
114 , p_check_quantity_types_flag IN VARCHAR2
115 , p_entity_type IN VARCHAR2
116 , p_column_name IN VARCHAR2
117 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
118 , x_result_type OUT NOCOPY VARCHAR2
119 , p_header_id_tbl IN PO_TBL_NUMBER --<Bug 15871378>
120 );
121
122 PROCEDURE gt_zero_order_type_filter(
123 p_calling_module IN VARCHAR2
124 , p_value_tbl IN PO_TBL_NUMBER
125 , p_entity_id_tbl IN PO_TBL_NUMBER
126 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
127 , p_check_quantity_types_flag IN VARCHAR2
128 , p_entity_type IN VARCHAR2
129 , p_column_name IN VARCHAR2
130 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
131 , x_result_type OUT NOCOPY VARCHAR2
132 );
133
134
135 PROCEDURE no_timecards_exist(
136 p_calling_module IN VARCHAR2
137 , p_line_id_tbl IN PO_TBL_NUMBER
138 , p_start_date_tbl IN PO_TBL_DATE DEFAULT NULL
139 , p_expiration_date_tbl IN PO_TBL_DATE DEFAULT NULL
140 , p_column_name IN VARCHAR2
141 , p_message_name IN VARCHAR2
142 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
143 , x_result_type OUT NOCOPY VARCHAR2
144 );
145
146 PROCEDURE amount_notif_ctrl_warning(
147 p_calling_module IN VARCHAR2
148 , p_line_id_tbl IN PO_TBL_NUMBER
149 , p_quantity_tbl IN PO_TBL_NUMBER
150 , p_column_name IN VARCHAR2
151 , p_message_name IN VARCHAR2
152 , x_result_set_id IN OUT NOCOPY NUMBER
153 , x_result_type OUT NOCOPY VARCHAR2
154 );
155
156 PROCEDURE child_num_unique(
157 p_calling_module IN VARCHAR2
158 , p_entity_type IN VARCHAR2
159 , p_entity_id_tbl IN PO_TBL_NUMBER
160 , p_parent_id_tbl IN PO_TBL_NUMBER
161 , p_entity_num_tbl IN PO_TBL_NUMBER
162 , p_par_draft_id_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL -- <PAR project>
163 , p_draft_id_tbl IN PO_TBL_NUMBER
164 , x_result_set_id IN OUT NOCOPY NUMBER
165 , x_result_type OUT NOCOPY VARCHAR2
166 , p_entity_type_tbl IN PO_TBL_VARCHAR30 DEFAULT NULL -- <Complex Work R12>
167 );
168
169 PROCEDURE price_diff_value_unique(
170 p_calling_module IN VARCHAR2
171 , p_price_diff_id_tbl IN PO_TBL_NUMBER
172 , p_entity_id_tbl IN PO_TBL_NUMBER
173 , p_entity_type_tbl IN PO_TBL_VARCHAR30
174 , p_unique_value_tbl IN PO_TBL_VARCHAR4000
175 , p_column_name IN VARCHAR2
176 , p_message_name IN VARCHAR2
177 , x_result_set_id IN OUT NOCOPY NUMBER
178 , x_result_type OUT NOCOPY VARCHAR2
179 );
180
181 PROCEDURE start_date_le_end_date(
182 p_calling_module IN VARCHAR2
183 , p_start_date_tbl IN PO_TBL_DATE
184 , p_end_date_tbl IN PO_TBL_DATE
185 , p_entity_id_tbl IN PO_TBL_NUMBER
186 , p_entity_type IN VARCHAR2
187 , p_column_name IN VARCHAR2
188 , p_column_val_selector IN VARCHAR2
189 , p_message_name IN VARCHAR2
190 , p_validation_id IN NUMBER DEFAULT NULL
191 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
192 , x_result_type OUT NOCOPY VARCHAR2
193 );
194
195 PROCEDURE num1_less_or_equal_num2(
196 p_calling_module IN VARCHAR2
197 , p_num1_tbl IN PO_TBL_NUMBER
198 , p_num2_tbl IN PO_TBL_NUMBER
199 , p_entity_id_tbl IN PO_TBL_NUMBER
200 , p_entity_type IN VARCHAR2
201 , p_column_name IN VARCHAR2
202 , p_message_name IN VARCHAR2
203 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
204 , x_result_type OUT NOCOPY VARCHAR2
205 );
206
207 PROCEDURE zero(p_calling_module IN VARCHAR2,
208 p_value_tbl IN PO_TBL_NUMBER,
209 p_entity_id_tbl IN PO_TBL_NUMBER,
210 p_entity_type IN VARCHAR2,
211 p_column_name IN VARCHAR2,
212 p_message_name IN VARCHAR2,
213 p_token1_name IN VARCHAR2 DEFAULT NULL,
214 p_token1_value IN VARCHAR2 DEFAULT NULL,
215 p_token2_name IN VARCHAR2 DEFAULT NULL,
216 p_token2_value_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
217 p_validation_id IN NUMBER DEFAULT NULL,
218 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
219 x_result_type OUT NOCOPY VARCHAR2);
220
221 PROCEDURE terms_id(p_calling_module IN VARCHAR2,
222 p_terms_id_tbl IN PO_TBL_NUMBER,
223 p_entity_id_tbl IN PO_TBL_NUMBER,
224 p_entity_type IN VARCHAR2,
225 p_validation_id IN NUMBER DEFAULT NULL,
226 x_result_set_id IN OUT NOCOPY NUMBER,
227 x_result_type OUT NOCOPY VARCHAR2);
228
229 PROCEDURE gt_zero_opm_filter(
230 p_calling_module IN VARCHAR2
231 , p_value_tbl IN PO_TBL_NUMBER
232 , p_entity_id_tbl IN PO_TBL_NUMBER
233 , p_item_id_tbl IN PO_TBL_NUMBER
234 , p_inv_org_id_tbl IN PO_TBL_NUMBER
235 , p_entity_type IN VARCHAR2
236 , p_column_name IN VARCHAR2
237 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
238 , x_result_type OUT NOCOPY VARCHAR2
239 );
240
241 PROCEDURE qtys_within_deviation(
242 p_calling_module IN VARCHAR2
243 , p_entity_id_tbl IN PO_TBL_NUMBER
244 , p_item_id_tbl IN PO_TBL_NUMBER
245 , p_inv_org_id_tbl IN PO_TBL_NUMBER
246 , p_quantity_tbl IN PO_TBL_NUMBER
247 , p_primary_uom_tbl IN PO_TBL_VARCHAR30
248 , p_sec_quantity_tbl IN PO_TBL_NUMBER
249 , p_secondary_uom_tbl IN PO_TBL_VARCHAR30
250 , p_column_name IN VARCHAR2
251 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
252 , x_result_type OUT NOCOPY VARCHAR2
253 );
254
255 PROCEDURE secondary_unit_of_measure(
256 p_id_tbl IN po_tbl_number,
257 p_entity_type IN VARCHAR2,
258 p_secondary_unit_of_meas_tbl IN po_tbl_varchar30,
259 p_item_id_tbl IN po_tbl_number,
260 p_item_tbl IN po_tbl_varchar2000,
261 p_organization_id_tbl IN po_tbl_number,
262 p_doc_type IN VARCHAR2,
263 p_create_or_update_item_flag IN VARCHAR2,
264 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
265 x_result_type OUT NOCOPY VARCHAR2);
266
267 PROCEDURE secondary_quantity(
268 p_id_tbl IN po_tbl_number,
269 p_entity_type IN VARCHAR2,
270 p_secondary_quantity_tbl IN po_tbl_number,
271 p_order_type_lookup_code_tbl IN po_tbl_varchar30,
272 p_item_id_tbl IN po_tbl_number,
273 p_item_tbl IN po_tbl_varchar2000,
274 p_organization_id_tbl IN po_tbl_number,
275 p_doc_type IN VARCHAR2,
276 p_create_or_update_item_flag IN VARCHAR2,
277 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
278 x_result_type OUT NOCOPY VARCHAR2);
279
280 PROCEDURE secondary_uom_update(
281 p_id_tbl IN po_tbl_number,
282 p_entity_type IN VARCHAR2,
283 p_secondary_unit_of_meas_tbl IN po_tbl_varchar30,
284 p_item_id_tbl IN po_tbl_number,
285 p_organization_id_tbl IN po_tbl_number,
286 p_create_or_update_item_flag IN VARCHAR2,
287 x_result_set_id IN OUT NOCOPY NUMBER,
288 x_result_type OUT NOCOPY VARCHAR2);
289
290 PROCEDURE preferred_grade(
291 p_id_tbl IN po_tbl_number,
292 p_entity_type IN VARCHAR2,
293 p_preferred_grade_tbl IN po_tbl_varchar2000,
294 p_item_id_tbl IN po_tbl_number,
295 p_item_tbl IN po_tbl_varchar2000,
296 p_organization_id_tbl IN po_tbl_number,
297 p_create_or_update_item_flag IN VARCHAR2,
298 p_validation_id IN NUMBER DEFAULT NULL,
299 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
300 x_result_set_id IN OUT NOCOPY NUMBER,
301 x_result_type OUT NOCOPY VARCHAR2);
302
303 PROCEDURE process_enabled(
304 p_id_tbl IN po_tbl_number,
305 p_entity_type IN VARCHAR2,
306 p_ship_to_organization_id_tbl IN po_tbl_number,
307 p_item_id_tbl IN po_tbl_number,
308 x_result_set_id IN OUT NOCOPY NUMBER,
309 x_result_type OUT NOCOPY VARCHAR2);
310
311 --Bug 8546034-Removed the declaration of validate_desc_flex procedure
312
313 --UCA Project - CLMR4 Changes Begin
314 PROCEDURE start_date_ge_sys_date(
315 p_calling_module IN VARCHAR2
316 , p_start_date_tbl IN PO_TBL_DATE
317 , p_entity_id_tbl IN PO_TBL_NUMBER
318 , p_entity_type IN VARCHAR2
319 , p_column_name IN VARCHAR2
320 , p_message_name IN VARCHAR2
321 , p_validation_id IN NUMBER DEFAULT NULL
322 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
323 , x_result_type OUT NOCOPY VARCHAR2
324 );
325 --UCA Project - CLMR4 Changes End
326
327 --PAR Project
328 PROCEDURE extract_par_draft_id_tbl(
329 p_par_draft_id IN VARCHAR2
330 , l_par_draft_id_tbl OUT NOCOPY PO_TBL_NUMBER);
331
332 END PO_VALIDATION_HELPER;