1 PACKAGE PO_MULTI_MOD_VALIDATIONS AUTHID CURRENT_USER AS
2 /* $Header: PO_MULTI_MOD_VALIDATIONS.pls 120.8.12020000.2 2013/02/11 00:34:45 vegajula ship $ */
3
4 ---------------------------------------------------------------
5 -- Global constants and types.
6 ---------------------------------------------------------------
7
8 c_result_type_FATAL CONSTANT VARCHAR2(30) := 'FATAL';
9 c_result_type_FAILURE CONSTANT VARCHAR2(30) := 'FAILURE';
10 c_result_type_WARNING CONSTANT VARCHAR2(30) := 'WARNING';
11 c_result_type_SUCCESS CONSTANT VARCHAR2(30) := 'SUCCESS';
12
13 g_result_type_rank_FATAL NUMBER;
14 g_result_type_rank_SUCCESS NUMBER;
15
16
17 FUNCTION has_address_changed(p_request_id IN NUMBER)
18 RETURN VARCHAR2;
19
20
21 FUNCTION has_clause_changed(p_request_id IN NUMBER)
22 RETURN VARCHAR2;
23
24 FUNCTION has_vendor_changed(p_request_id IN NUMBER
25 ,p_change_id_tbl IN PO_TBL_NUMBER)
26 RETURN VARCHAR2;
27
28 FUNCTION unpaid_invoices_exists( p_invoice_id IN NUMBER)
29 RETURN BOOLEAN;
30
31 FUNCTION unval_invoices_exists( p_po_header_id IN NUMBER
32 ,p_invoice_id IN NUMBER
33 )
34 RETURN BOOLEAN;
35
36 FUNCTION check_match_exists( p_shipment_line_id IN NUMBER)
37 RETURN BOOLEAN;
38
39 FUNCTION get_invoice_id( p_doc_id IN NUMBER
40 ,p_match_type IN VARCHAR2)
41 RETURN NUMBER;
42
43 PROCEDURE unmtch_inv_for_vndr_exists( p_vendor_id IN NUMBER
44 --bug13084712
45 ,x_invoice_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
46 ,x_invoice_num_tbl IN OUT NOCOPY PO_TBL_VARCHAR50
47 ,x_invoice_date_tbl IN OUT NOCOPY PO_TBL_DATE);
48
49
50 PROCEDURE prepd_inv_for_vndr_exists( p_vendor_id IN NUMBER
51 --bug13084712
52 ,x_invoice_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
53 ,x_invoice_num_tbl IN OUT NOCOPY PO_TBL_VARCHAR50
54 ,x_invoice_date_tbl IN OUT NOCOPY PO_TBL_DATE);
55
56
57 PROCEDURE partpd_inv_for_vndr_exists(p_vendor_id IN NUMBER
58 --bug13084712
59 ,x_invoice_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
60 ,x_invoice_num_tbl IN OUT NOCOPY PO_TBL_VARCHAR50
61 ,x_invoice_date_tbl IN OUT NOCOPY PO_TBL_DATE);
62
63
64
65 PROCEDURE validate_multi_mod( p_multi_mod_request_id IN NUMBER
66 ,p_validation_type IN VARCHAR2 DEFAULT NULL
67 ,x_result_type OUT NOCOPY VARCHAR2
68 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
69 );
70
71 PROCEDURE validate_set( p_validation_set IN PO_TBL_VARCHAR2000
72 ,p_multi_mod_request_id IN NUMBER
73 ,p_multi_mod_request_type IN VARCHAR2
74 ,p_novation_effective_date DATE
75 ,p_acceptance_reqd_flag IN VARCHAR2
76 ,p_acceptance_due_date DATE
77 ,p_clm_noofcopies IN NUMBER
78 ,p_exemption_reason IN VARCHAR2
79 ,p_initiate_approval_flag IN VARCHAR2
80 ,p_clm_contract_officer IN NUMBER
81 ,p_multi_mod_docs IN PO_MULTI_MOD_DOCS_VAL_TYPE
82 ,p_multi_mod_changes IN PO_MULTI_MOD_CHANGES_VAL_TYPE
83 ,p_validation_type IN VARCHAR2
84 ,x_result_type OUT NOCOPY VARCHAR2
85 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
86 );
87
88
89 PROCEDURE atleast_one_doc_selected
90 ( p_request_id IN NUMBER
91 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
92 ,p_validation_type IN VARCHAR2
93 ,x_result_type OUT NOCOPY VARCHAR2
94 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
95 );
96
97
98 PROCEDURE exemption_reason_given
99 ( p_request_id IN NUMBER
100 ,p_exemption_reason IN VARCHAR2
101 ,p_initiate_app_flag IN VARCHAR2
102 ,p_validation_type IN VARCHAR2
103 ,x_result_type OUT NOCOPY VARCHAR2
104 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
105 );
106
107
108
109 PROCEDURE new_vendor_contact_provided
110 ( p_request_id IN NUMBER
111 ,p_acceptance_reqd_flag IN VARCHAR2
112 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
113 ,p_document_id_tbl IN PO_TBL_NUMBER
114 ,p_change_id_tbl IN PO_TBL_NUMBER
115 ,p_change_type_tbl IN PO_TBL_VARCHAR30
116 ,p_org_id_tbl IN PO_TBL_NUMBER
117 ,p_new_vendor_site_id_tbl IN PO_TBL_NUMBER
118 ,p_new_vendor_contact_id_tbl IN PO_TBL_NUMBER
119 ,p_validation_type IN VARCHAR2
120 ,x_result_type OUT NOCOPY VARCHAR2
121 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
122 );
123
124
125 PROCEDURE vendor_name_chg_valid
126 ( p_request_id IN NUMBER
127 ,p_change_id_tbl PO_TBL_NUMBER
128 ,p_change_type_tbl PO_TBL_VARCHAR30
129 ,p_old_vendor_name_tbl PO_TBL_VARCHAR240
130 ,p_new_vendor_name_tbl PO_TBL_VARCHAR240
131 ,p_validation_type IN VARCHAR2
132 ,x_result_type OUT NOCOPY VARCHAR2
133 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
134 );
135
136 PROCEDURE new_vendor_on_hold
137 ( p_request_id IN NUMBER
138 ,p_change_id_tbl PO_TBL_NUMBER
139 ,p_change_type_tbl PO_TBL_VARCHAR30
140 ,p_new_vendor_id_tbl PO_TBL_NUMBER
141 ,p_validation_type IN VARCHAR2
142 ,x_result_type OUT NOCOPY VARCHAR2
143 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
144 );
145
146 PROCEDURE vendor_chg_valid
147 ( p_request_id IN NUMBER
148 ,p_change_id_tbl PO_TBL_NUMBER
149 ,p_change_type_tbl PO_TBL_VARCHAR30
150 ,p_old_vendor_id_tbl PO_TBL_NUMBER
151 ,p_new_vendor_id_tbl PO_TBL_NUMBER
152 ,p_validation_type IN VARCHAR2
153 ,x_result_type OUT NOCOPY VARCHAR2
154 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
155 );
156
157 PROCEDURE no_of_copies_ge_zero
158 ( p_request_id IN NUMBER
159 ,p_acceptance_reqd_flag IN VARCHAR2
160 ,p_clm_noofcopies IN NUMBER
161 ,p_validation_type IN VARCHAR2
162 ,x_result_type OUT NOCOPY VARCHAR2
163 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
164 );
165
166 PROCEDURE acceptance_due_date_valid
167 ( p_request_id IN NUMBER
168 ,p_acceptance_reqd_flag IN VARCHAR2
169 ,p_acceptance_due_date IN DATE
170 ,p_validation_type IN VARCHAR2
171 ,x_result_type OUT NOCOPY VARCHAR2
172 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
173 );
174
175 PROCEDURE clause_change_valid
176 ( p_request_id IN NUMBER
177 ,p_validation_type IN VARCHAR2
178 ,x_result_type OUT NOCOPY VARCHAR2
179 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
180 );
181
182 PROCEDURE atleast_one_change_done
183 ( p_request_id IN NUMBER
184 ,p_change_id_tbl IN PO_TBL_NUMBER
185 ,p_validation_type IN VARCHAR2
186 ,x_result_type OUT NOCOPY VARCHAR2
187 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
188 );
189
190 PROCEDURE new_vendor_site_provided
191 ( p_request_id IN NUMBER
192 ,p_change_id_tbl IN PO_TBL_NUMBER
193 ,p_change_type_tbl IN PO_TBL_VARCHAR30
194 ,p_org_id_tbl IN PO_TBL_NUMBER
195 ,p_old_vendor_site_id_tbl IN PO_TBL_NUMBER
196 ,p_new_vendor_site_id_tbl IN PO_TBL_NUMBER
197 ,p_validation_type IN VARCHAR2
198 ,x_result_type OUT NOCOPY VARCHAR2
199 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
200 );
201
202 PROCEDURE new_vendor_site_CCR_valid
203 ( p_request_id IN NUMBER
204 ,p_change_id_tbl IN PO_TBL_NUMBER
205 ,p_change_type_tbl IN PO_TBL_VARCHAR30
206 ,p_old_vendor_site_id_tbl IN PO_TBL_NUMBER
207 ,p_new_vendor_site_id_tbl IN PO_TBL_NUMBER
208 ,p_validation_type IN VARCHAR2
209 ,x_result_type OUT NOCOPY VARCHAR2
210 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
211 );
212
213 PROCEDURE generate_idv_exceptions
214 ( p_request_id IN NUMBER
215 --,p_document_id_tbl IN PO_TBL_NUMBER
216 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
217 ,x_result_type OUT NOCOPY VARCHAR2
218 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
219 );
220
221 PROCEDURE generate_award_exceptions
222 ( p_request_id IN NUMBER
223 -- ,p_document_id_tbl IN PO_TBL_NUMBER
224 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
225 ,x_result_type OUT NOCOPY VARCHAR2
226 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
227 );
228
229 PROCEDURE generate_receipts_exceptions
230 ( p_request_id IN NUMBER
231 ,p_document_id_tbl IN PO_TBL_NUMBER
232 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
233 ,p_novation_effective_date IN DATE
234 ,x_result_type OUT NOCOPY VARCHAR2
235 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
236 );
237
238 PROCEDURE generate_project_exceptions
239 ( p_request_id IN NUMBER
240 ,p_document_id_tbl IN PO_TBL_NUMBER
241 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
242 ,p_novation_effective_date IN DATE
243 ,x_result_type OUT NOCOPY VARCHAR2
244 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
245 );
246
247 PROCEDURE generate_invoice_exceptions
248 ( p_request_id IN NUMBER
249 ,p_document_id_tbl IN PO_TBL_NUMBER
250 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
251 ,p_novation_effective_date IN DATE
252 ,x_result_type OUT NOCOPY VARCHAR2
253 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
254 );
255
256 PROCEDURE set_validation_status( p_multi_mod_request_id IN NUMBER
257 ,p_validation_status IN VARCHAR2
258 );
259
260 /*PROCEDURE update_doc_status( p_request_id IN NUMBER
261 ,p_document_id IN NUMBER
262 ,p_status IN VARCHAR2
263 );*/
264
265 PROCEDURE generate_supp_chg_exceptions( errbuf OUT NOCOPY VARCHAR2
266 ,retcode OUT NOCOPY VARCHAR2
267 , p_multi_mod_request_id IN NUMBER
268 );
269
270 PROCEDURE uda_addresses_valid( p_request_id IN NUMBER
271 ,x_result_type OUT NOCOPY VARCHAR2
272 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
273 );
274
275 PROCEDURE contract_officer_provided
276 ( p_request_id IN NUMBER
277 ,p_clm_contract_officer IN NUMBER
278 ,p_validation_type IN VARCHAR2
279 ,p_org_id_tbl IN PO_TBL_NUMBER -- Added this parameter as part of CLM Controls Project
280 ,x_result_type OUT NOCOPY VARCHAR2
281 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
282 );
283
284 /* Checks if compatible locks at header level are available to make the requested
285 changes.
286 */
287 PROCEDURE header_lock_compatible(p_request_id IN NUMBER
288 , x_result_type OUT NOCOPY VARCHAR2
289 , x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE);
290
291 /* Checks if compatible lock at any address is available to make the requested
292 changes at that particular address.
293 */
294 PROCEDURE address_lock_compatible(p_request_id IN NUMBER
295 , x_result_type OUT NOCOPY VARCHAR2
296 , x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE);
297
298 /* Checks if there already exists an open modification if conc modifications
299 */
300 PROCEDURE validate_open_mod(p_request_id IN NUMBER
301 , x_result_type OUT NOCOPY VARCHAR2
302 , x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE);
303
304
305 END PO_MULTI_MOD_VALIDATIONS;
306
[Home] [Help]