[Home] [Help]
PACKAGE BODY: APPS.PO_MULTI_MOD_VALIDATIONS
Source
1 PACKAGE BODY PO_MULTI_MOD_VALIDATIONS AS
2 /* $Header: PO_MULTI_MOD_VALIDATIONS.plb 120.21.12020000.2 2013/02/10 15:16:59 vegajula ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- Modules for debugging.
7 ---------------------------------------------------------------------------
8
9 -- The module base for this package.
10 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
11 PO_LOG.get_package_base('PO_MULTI_MOD_VALIDATIONS');
12
13 -- The module base for the subprogram.
14 D_validate_multi_mod CONSTANT VARCHAR2(100) :=
15 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'validate_multi_mod');
16
17 -- The module base for the subprogram.
18 D_validate_set CONSTANT VARCHAR2(100) :=
19 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'validate_set');
20
21 D_atleast_one_doc_selected CONSTANT VARCHAR2(100) :=
22 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'atleast_one_doc_selected');
23
24 D_clause_change_valid CONSTANT VARCHAR2(100) :=
25 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'clause_change_valid');
26
27 D_exemption_reason_given CONSTANT VARCHAR2(100) :=
28 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'exemption_reason_given');
29
30 D_new_vendor_contact_provided CONSTANT VARCHAR2(100) :=
31 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'new_vendor_contact_provided');
32
33 D_acceptance_due_date_valid CONSTANT VARCHAR2(100) :=
34 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'acceptance_due_date_valid');
35
36 D_no_of_copies_ge_zero CONSTANT VARCHAR2(100) :=
37 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'no_of_copies_ge_zero');
38
39 D_vendor_name_chg_valid CONSTANT VARCHAR2(100) :=
40 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vendor_name_chg_valid');
41
42 D_new_vendor_on_hold CONSTANT VARCHAR2(100) :=
43 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'new_vendor_on_hold');
44
45 D_vendor_chg_valid CONSTANT VARCHAR2(100) :=
46 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vendor_chg_valid');
47
48 D_atleast_one_change_done CONSTANT VARCHAR2(100) :=
49 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'atleast_one_change_done');
50
51 D_new_vendor_site_provided CONSTANT VARCHAR2(100) :=
52 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'new_vendor_site_provided');
53
54 D_new_vendor_site_CCR_valid CONSTANT VARCHAR2(100) :=
55 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'new_vendor_site_CCR_valid');
56
57 D_generate_idv_exceptions CONSTANT VARCHAR2(100) :=
58 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'generate_idv_exceptions');
59
60 D_generate_award_exceptions CONSTANT VARCHAR2(100) :=
61 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'generate_award_exceptions');
62
63 D_generate_receipts_exceptions CONSTANT VARCHAR2(100) :=
64 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'generate_receipts_exceptions');
65
66 D_generate_invoice_exceptions CONSTANT VARCHAR2(100) :=
67 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'generate_invoice_exceptions');
68
69 D_has_address_changed CONSTANT VARCHAR2(100) :=
70 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'has_address_changed');
71
72 D_has_clause_changed CONSTANT VARCHAR2(100) :=
73 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'has_clause_changed');
74
75 D_has_vendor_changed CONSTANT VARCHAR2(100) :=
76 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'has_vendor_changed');
77
78 D_unpaid_invoices_exists CONSTANT VARCHAR2(100) :=
79 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'unpaid_invoices_exists');
80
81 D_unval_invoices_exists CONSTANT VARCHAR2(100) :=
82 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'unval_invoices_exists');
83
84 D_check_match_exists CONSTANT VARCHAR2(100) :=
85 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_match_exists');
86
87 D_get_invoice_id CONSTANT VARCHAR2(100) :=
88 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_invoice_id');
89
90 D_pending_rcv_trx_exists CONSTANT VARCHAR2(100) :=
91 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'pending_rcv_trx_exists');
92
93 D_shared_rcpt_docs_not_inc CONSTANT VARCHAR2(100) :=
94 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'shared_rcpt_docs_not_inc');
95
96 D_unmtch_inv_for_vndr_exists CONSTANT VARCHAR2(100) :=
97 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'unmtch_inv_for_vndr_exists');
98
99 D_prepd_inv_for_vndr_exists CONSTANT VARCHAR2(100) :=
100 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'prepd_inv_for_vndr_exists');
101
102 D_partpd_inv_for_vndr_exists CONSTANT VARCHAR2(100) :=
103 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'partpd_inv_for_vndr_exists');
104
105 D_set_validation_status CONSTANT VARCHAR2(100) :=
106 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'set_validation_status');
107
108 D_update_doc_status CONSTANT VARCHAR2(100) :=
109 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'update_doc_status');
110
111 D_generate_supp_chg_exceptions CONSTANT VARCHAR2(100) :=
112 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'generate_supp_chg_exceptions');
113
114 D_uda_addresses_valid CONSTANT VARCHAR2(100) :=
115 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'uda_addresses_valid');
116
117 D_contract_officer_provided CONSTANT VARCHAR2(100) :=
118 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'contract_officer_provided');
119
120 D_header_lock_compatible CONSTANT VARCHAR2(100) :=
121 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'header_lock_compatible');
122
123 D_address_lock_compatible CONSTANT VARCHAR2(100) :=
124 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'address_lock_compatible');
125
126 D_validate_open_mod CONSTANT VARCHAR2(100) :=
127 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'validate_open_mod');
128
129 D_generate_project_exceptions CONSTANT VARCHAR2(100) :=
130 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'generate_project_exceptions');
131
132
133 --------------------------------------------------------------
134 -- Validation Subroutine Constants
135 --------------------------------------------------------------
136 --------------------------------------------------------------
137 -- Pre-submit validation constants
138 --------------------------------------------------------------
139
140 c_atleast_one_doc_selected CONSTANT VARCHAR2(30) := 'C_ATLEAST_ONE_DOC_SELECTED';
141 c_exemption_reason_given CONSTANT VARCHAR2(30) := 'C_EXEMPTION_REASON_GIVEN';
142 c_new_vendor_contact_provided CONSTANT VARCHAR2(30) := 'C_NEW_VENDOR_CONTACT_PROVIDED';
143 c_acceptance_due_date_valid CONSTANT VARCHAR2(30) := 'C_ACCEPTANCE_DUE_DATE_VALID';
144 c_no_of_copies_ge_zero CONSTANT VARCHAR2(30) := 'C_NO_OF_COPIES_GE_ZERO';
145 c_vendor_name_chg_valid CONSTANT VARCHAR2(30) := 'C_VENDOR_NAME_CHG_VALID';
146 c_vendor_chg_valid CONSTANT VARCHAR2(30) := 'C_VENDOR_CHG_VALID';
147 c_new_vendor_on_hold CONSTANT VARCHAR2(30) := 'C_NEW_VENDOR_ON_HOLD';
148 c_clause_change_valid CONSTANT VARCHAR2(30) := 'C_CLAUSE_CHANGE_VALID';
149 c_atleast_one_change_done CONSTANT VARCHAR2(30) := 'C_ATLEAST_ONE_CHANGE_DONE';
150 c_new_vendor_site_provided CONSTANT VARCHAR2(30) := 'C_NEW_VENDOR_SITE_PROVIDED';
151 c_new_vendor_site_CCR_valid CONSTANT VARCHAR2(30) := 'C_NEW_VENDOR_SITE_CCR_VALID';
152 c_uda_addresses_valid CONSTANT VARCHAR2(30) := 'C_UDA_ADDRESSES_VALID';
153 c_contract_officer_provided CONSTANT VARCHAR2(30) := 'C_CONTRACT_OFFICER_PROVIDED';
154 --<Conc Mod Project>
155 c_header_lock_compatible CONSTANT VARCHAR2(30) := 'C_HEADER_LOCK_COMPATIBLE';
156 c_address_lock_compatible CONSTANT VARCHAR2(30) := 'C_ADDRESS_LOCK_COMPATIBLE';
157 c_validate_open_mod CONSTANT VARCHAR2(30) := 'C_VALIDATE_OPEN_MOD';
158
159 --------------------------------------------------------------
160 -- Concurrent validation constants
161 --------------------------------------------------------------
162 c_generate_idv_exceptions CONSTANT VARCHAR2(30) := 'C_GENERATE_IDV_EXCEPTIONS';
163 c_generate_award_exceptions CONSTANT VARCHAR2(30) := 'C_GENERATE_AWARD_EXCEPTIONS';
164 c_generate_receipts_exceptions CONSTANT VARCHAR2(30) := 'C_GENERATE_RECEIPTS_EXCEPTIONS';
165 c_generate_invoice_exceptions CONSTANT VARCHAR2(30) := 'C_GENERATE_INVOICE_EXCEPTIONS';
166 c_generate_custom_exceptions CONSTANT VARCHAR2(30) := 'C_GENERATE_CUSTOM_EXCEPTIONS';
167 c_generate_project_exceptions CONSTANT VARCHAR2(30) := 'C_GENERATE_PROJECT_EXCEPTIONS';
168
169
170
171 --validation set for Address change
172 c_address_change_vs CONSTANT PO_TBL_VARCHAR2000 :=
173 PO_TBL_VARCHAR2000(
174 c_atleast_one_doc_selected
175 , c_exemption_reason_given
176 , c_new_vendor_contact_provided
177 , c_clause_change_valid
178 , c_atleast_one_change_done
179 , c_acceptance_due_date_valid
180 , c_no_of_copies_ge_zero
181 , c_uda_addresses_valid
182 , c_contract_officer_provided
183 -- , c_header_lock_compatible removing header lock validation from address change (Bug: 13566640)
184 , c_address_lock_compatible
185 , c_validate_open_mod
186 );
187
188 --validation set for vendor name change
189 c_vendor_name_change_vs CONSTANT PO_TBL_VARCHAR2000 :=
190 PO_TBL_VARCHAR2000(
191 c_atleast_one_doc_selected
192 , c_exemption_reason_given
193 , c_new_vendor_contact_provided
194 , c_clause_change_valid
195 , c_atleast_one_change_done
196 , c_acceptance_due_date_valid
197 , c_no_of_copies_ge_zero
198 , c_vendor_name_chg_valid
199 , c_header_lock_compatible
200 , c_validate_open_mod
201 );
202
203 --validation set for vendor change
204 c_vendor_change_vs CONSTANT PO_TBL_VARCHAR2000 :=
205 PO_TBL_VARCHAR2000(
206 c_atleast_one_doc_selected
207 , c_exemption_reason_given
208 , c_new_vendor_contact_provided
209 , c_clause_change_valid
210 , c_atleast_one_change_done
211 , c_new_vendor_site_provided
212 , c_new_vendor_site_CCR_valid
213 , c_acceptance_due_date_valid
214 , c_no_of_copies_ge_zero
215 , c_vendor_chg_valid
216 , c_new_vendor_on_hold
217 , c_header_lock_compatible
218 , c_validate_open_mod
219 );
220
221 --validation set for Supplier change - exceptions concurrent program
222 c_vendor_change_exception_vs CONSTANT PO_TBL_VARCHAR2000 :=
223 PO_TBL_VARCHAR2000(
224 c_generate_idv_exceptions
225 , c_generate_award_exceptions
226 , c_generate_receipts_exceptions
227 , c_generate_invoice_exceptions
228 , c_generate_custom_exceptions
229 , c_generate_project_exceptions
230 );
231
232 -------------------------------------------------------------------------------
233 --Start of Comments
234 --Name: validate_multi_mod
235 --Pre-reqs: None.
236 --Function:
237 -- Based on the validation type and the request type the validation set is created and passed on to validate_set
238 -- procedure to perform the validations and store the results in the object type po_multi_mod_val_results.
239 --Parameters:
240 --IN:
241 --p_multi_mod_requests
242 -- po_multi_mod_requests table data for validation
243 --p_multi_mod_docs
244 -- po_multi_mod_docs table data for validation
245 --p_multi_mod_changes
246 -- po_multi_mod_changes table data for validation
247 --p_validation_type
248 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
249 --OUT:
250 --x_result_type
251 -- Identifies if the validation was a success or failure
252 --x_results
253 -- Out variable that holds the validation results
254 --End of Comments
255 -------------------------------------------------------------------------------
256 PROCEDURE validate_multi_mod( p_multi_mod_request_id IN NUMBER
257 ,p_validation_type IN VARCHAR2 DEFAULT NULL
258 ,x_result_type OUT NOCOPY VARCHAR2
259 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
260 )
261 is
262 l_validation_set PO_TBL_VARCHAR2000;
263 l_multi_mod_request_type PO_MULTI_MOD_REQUESTS.multi_mod_request_type%type;
264 l_novation_effective_date PO_MULTI_MOD_REQUESTS.novation_effective_date%TYPE;
265 l_acceptance_reqd_flag PO_MULTI_MOD_REQUESTS.acceptance_required_flag%TYPE;
266 l_acceptance_due_date PO_MULTI_MOD_REQUESTS.acceptance_due_date%TYPE;
267 l_clm_noofcopies PO_MULTI_MOD_REQUESTS.clm_noofcopies%TYPE;
268 l_exemption_reason PO_MULTI_MOD_REQUESTS.exemption_reason%TYPE;
269 l_initiate_approval_flag PO_MULTI_MOD_REQUESTS.initiate_approval_flag%TYPE;
270 l_clm_contract_officer PO_MULTI_MOD_REQUESTS.clm_contract_officer%TYPE;
271 l_multi_mod_docs PO_MULTI_MOD_DOCS_VAL_TYPE ;
272 l_multi_mod_changes PO_MULTI_MOD_CHANGES_VAL_TYPE;
273 d_mod CONSTANT VARCHAR2(100) := D_validate_multi_mod;
274 d_position NUMBER := 0;
275
276 BEGIN
277
278 IF PO_LOG.d_stmt THEN
279 PO_LOG.proc_begin(d_mod);
280 END IF;
281
282
283 d_position := 10;
284 l_validation_set := PO_TBL_VARCHAR2000();
285 IF PO_LOG.d_stmt THEN
286 PO_LOG.stmt(d_mod,d_position,'l_validation_set',l_validation_set);
287 PO_LOG.stmt(d_mod,d_position,'p_validation_type',p_validation_type);
288 PO_LOG.stmt(d_mod,d_position,'p_multi_mod_request_id',p_multi_mod_request_id);
289 PO_LOG.stmt(d_mod,d_position,'x_result_type',x_result_type);
290 END IF;
291
292 --set validation_status to inprocess and delete the records in po_multi_mod_val_results table for this request_id
293 d_position := 20;
294 IF p_validation_type = 'SUPP_CHG_EXCEPTIONS' THEN
295 set_validation_status (p_multi_mod_request_id => p_multi_mod_request_id
296 ,p_validation_status => 'IN PROCESS'
297 );
298
299 DELETE FROM po_multi_mod_val_results
300 WHERE multi_mod_request_id = p_multi_mod_request_id;
301 END IF;
302
303 --populate po_multi_mod_requests parameters
304 d_position := 30;
305 BEGIN
306 SELECT multi_mod_request_type
307 ,NVL(novation_effective_date, sysdate)
308 ,acceptance_required_flag
309 ,acceptance_due_date
310 ,clm_noofcopies
311 ,exemption_reason
312 ,initiate_approval_flag
313 ,clm_contract_officer
314 INTO l_multi_mod_request_type
315 ,l_novation_effective_date
316 ,l_acceptance_reqd_flag
317 ,l_acceptance_due_date
318 ,l_clm_noofcopies
319 ,l_exemption_reason
320 ,l_initiate_approval_flag
321 ,l_clm_contract_officer
322 FROM po_multi_mod_requests
323 WHERE multi_mod_request_id = p_multi_mod_request_id;
324 EXCEPTION
325 WHEN OTHERS THEN
326 l_multi_mod_request_type := null;
327 l_novation_effective_date := null;
328 l_acceptance_reqd_flag := null;
329 l_acceptance_due_date := null;
330 l_clm_noofcopies := null;
331 l_exemption_reason := null;
332 l_initiate_approval_flag := null;
333 l_clm_contract_officer := null;
334 END;
335
336 IF PO_LOG.d_stmt THEN
337 PO_LOG.stmt(d_mod,d_position,'l_multi_mod_request_type',l_multi_mod_request_type);
338 PO_LOG.stmt(d_mod,d_position,'l_novation_effective_date',l_novation_effective_date);
339 PO_LOG.stmt(d_mod,d_position,'l_acceptance_reqd_flag',l_acceptance_reqd_flag);
340 PO_LOG.stmt(d_mod,d_position,'l_acceptance_due_date',l_acceptance_due_date);
341 PO_LOG.stmt(d_mod,d_position,'l_clm_noofcopies',l_clm_noofcopies);
342 PO_LOG.stmt(d_mod,d_position,'l_exemption_reason',l_exemption_reason);
343 PO_LOG.stmt(d_mod,d_position,'l_initiate_approval_flag',l_initiate_approval_flag);
344 PO_LOG.stmt(d_mod,d_position,'l_clm_contract_officer',l_clm_contract_officer);
345 END IF;
346
347 --populate po_multi_mod_docs records into p_multi_mod_docs object type
348 d_position := 40;
349 BEGIN
350 SELECT multi_mod_doc_id
351 ,multi_mod_request_id
352 ,document_id
353 ,status
354 ,draft_id
355 BULK COLLECT INTO
356 l_multi_mod_docs.multi_mod_doc_id
357 ,l_multi_mod_docs.multi_mod_request_id
358 ,l_multi_mod_docs.document_id
359 ,l_multi_mod_docs.status
360 ,l_multi_mod_docs.draft_id
361 FROM po_multi_mod_docs
362 WHERE multi_mod_request_id = p_multi_mod_request_id;
363 EXCEPTION
364 WHEN OTHERS THEN
365 null;
366 END;
367
368 IF PO_LOG.d_stmt THEN
369 PO_LOG.stmt(d_mod,d_position,'l_multi_mod_docs-count',l_multi_mod_docs.multi_mod_doc_id.COUNT);
370 END IF;
371
372 --populate po_multi_mod_changes records into p_multi_mod_docs object type
373 d_position := 50;
374 BEGIN
375 SELECT multi_mod_change_id
376 ,multi_mod_request_id
377 ,change_type
378 ,old_vendor_id
379 ,old_vendor_name
380 ,new_vendor_id
381 ,new_vendor_name
382 ,org_id
383 ,old_vendor_site_id
384 ,new_vendor_site_id
385 ,new_vendor_contact_id
386 ,old_remit_to_addr
387 ,new_remit_to_addr
388 BULK COLLECT INTO
389 l_multi_mod_changes.multi_mod_change_id
390 ,l_multi_mod_changes.multi_mod_request_id
391 ,l_multi_mod_changes.change_type
392 ,l_multi_mod_changes.old_vendor_id
393 ,l_multi_mod_changes.old_vendor_name
394 ,l_multi_mod_changes.new_vendor_id
395 ,l_multi_mod_changes.new_vendor_name
396 ,l_multi_mod_changes.org_id
397 ,l_multi_mod_changes.old_vendor_site_id
398 ,l_multi_mod_changes.new_vendor_site_id
399 ,l_multi_mod_changes.new_vendor_contact_id
400 ,l_multi_mod_changes.old_remit_to_addr
401 ,l_multi_mod_changes.new_remit_to_addr
402 FROM po_multi_mod_changes
403 WHERE multi_mod_request_id = p_multi_mod_request_id;
404 EXCEPTION
405 WHEN OTHERS THEN
406 null;
407 END;
408 IF PO_LOG.d_stmt THEN
409 PO_LOG.stmt(d_mod,d_position,'l_multi_mod_changes-count',l_multi_mod_changes.multi_mod_change_id.COUNT);
410 END IF;
411
412 d_position := 60;
413 IF p_validation_type = 'PRE_SUBMIT' THEN
414
415 d_position := 70;
416 IF l_multi_mod_request_type = 'MULTI_MOD' THEN
417 d_position := 80;
418 l_validation_set := l_validation_set MULTISET UNION DISTINCT c_address_change_vs;
419 IF PO_LOG.d_stmt THEN
420 PO_LOG.stmt(d_mod,d_position,'l_validation_set',l_validation_set);
421 END IF;
422 END IF;
423
424 IF l_multi_mod_request_type = 'VENDOR_NAME_CHANGE' THEN
425 d_position := 90;
426 l_validation_set := l_validation_set MULTISET UNION DISTINCT c_vendor_name_change_vs;
427 IF PO_LOG.d_stmt THEN
428 PO_LOG.stmt(d_mod,d_position,'l_validation_set',l_validation_set);
429 END IF;
430 END IF;
431
432 IF l_multi_mod_request_type = 'VENDOR_CHANGE' THEN
433 d_position := 100;
434 l_validation_set := l_validation_set MULTISET UNION DISTINCT c_vendor_change_vs;
435 IF PO_LOG.d_stmt THEN
436 PO_LOG.stmt(d_mod,d_position,'l_validation_set',l_validation_set);
437 END IF;
438 END IF;
439 ELSIF p_validation_type = 'SUPP_CHG_EXCEPTIONS' THEN
440
441 d_position := 110;
442 l_validation_set := l_validation_set MULTISET UNION DISTINCT c_vendor_change_exception_vs;
443 IF PO_LOG.d_stmt THEN
444 PO_LOG.stmt(d_mod,d_position,'l_validation_set',l_validation_set);
445 END IF;
446 END IF;
447
448 d_position := 120;
449 PO_MULTI_MOD_VALIDATIONS.validate_set( p_validation_set => l_validation_set
450 ,p_multi_mod_request_id => p_multi_mod_request_id
451 ,p_multi_mod_request_type => l_multi_mod_request_type
452 ,p_novation_effective_date => l_novation_effective_date
453 ,p_acceptance_reqd_flag => l_acceptance_reqd_flag
454 ,p_acceptance_due_date => l_acceptance_due_date
455 ,p_clm_noofcopies => l_clm_noofcopies
456 ,p_exemption_reason => l_exemption_reason
457 ,p_initiate_approval_flag => l_initiate_approval_flag
458 ,p_clm_contract_officer => l_clm_contract_officer
459 ,p_multi_mod_docs => l_multi_mod_docs
460 ,p_multi_mod_changes => l_multi_mod_changes
461 ,p_validation_type => p_validation_type
462 ,x_result_type => x_result_type
463 ,x_results => x_results
464 );
465
466
467 IF x_results.result_type.COUNT > 0 THEN
468 /* for supplier chnage exceptions the 'view exceptions and continue' button in UI will not be displayed if the results is 'FAILURE' as 'FAILURE' means an exception in that context*/
469 IF p_validation_type = 'SUPP_CHG_EXCEPTIONS' THEN
470 x_result_type := c_result_type_WARNING;
471 ELSIF p_validation_type = 'PRE_SUBMIT' THEN
472 x_result_type := c_result_type_FAILURE;
473 END IF;
474 ELSIF x_results.result_type.COUNT = 0 THEN
475 x_result_type := c_result_type_SUCCESS;
476 END IF;
477
478 d_position := 130;
479 IF p_validation_type = 'SUPP_CHG_EXCEPTIONS' THEN
480 set_validation_status (p_multi_mod_request_id => p_multi_mod_request_id
481 ,p_validation_status => x_result_type
482 );
483 END IF;
484
485 IF PO_LOG.d_stmt THEN
486 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
487 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
488 END IF;
489 EXCEPTION
490 WHEN others THEN
491 x_result_type := c_result_type_FAILURE;
492 IF p_validation_type = 'SUPP_CHG_EXCEPTIONS' THEN
493 set_validation_status (p_multi_mod_request_id => p_multi_mod_request_id
494 ,p_validation_status => x_result_type
495 );
496 END IF;
497 IF PO_LOG.d_exc THEN
498 PO_LOG.exc(d_mod,d_position,NULL);
499 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
500 END IF;
501 RAISE;
502
503
504 END validate_multi_mod;
505
506 -------------------------------------------------------------------------------
507 --Start of Comments
508 --Name: validate_set
509 --Pre-reqs: None.
510 --Function:
511 -- Performs the necessary validation based on the validation set
512 -- and and store the results in the object type po_multi_mod_val_results.
513 --Parameters:
514 --IN:
515 --p_validation_set
516 -- Specifies the validations to perform.
517 --p_multi_mod_requests
518 -- po_multi_mod_requests table data for validation
519 --p_multi_mod_docs
520 -- po_multi_mod_docs table data for validation
521 --p_multi_mod_changes
522 -- po_multi_mod_changes table data for validation
523 --p_validation_type
524 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
525 --OUT:
526 --x_result_type
527 -- Identifies if the validation was a success or failure
528 --x_results
529 -- Out variable that holds the validation results
530 --End of Comments
531 -------------------------------------------------------------------------------
532 PROCEDURE validate_set( p_validation_set IN PO_TBL_VARCHAR2000
533 ,p_multi_mod_request_id IN NUMBER
534 ,p_multi_mod_request_type IN VARCHAR2
535 ,p_novation_effective_date DATE
536 ,p_acceptance_reqd_flag IN VARCHAR2
537 ,p_acceptance_due_date DATE
538 ,p_clm_noofcopies IN NUMBER
539 ,p_exemption_reason IN VARCHAR2
540 ,p_initiate_approval_flag IN VARCHAR2
541 ,p_clm_contract_officer IN NUMBER
542 ,p_multi_mod_docs IN PO_MULTI_MOD_DOCS_VAL_TYPE
543 ,p_multi_mod_changes IN PO_MULTI_MOD_CHANGES_VAL_TYPE
544 ,p_validation_type IN VARCHAR2
545 ,x_result_type OUT NOCOPY VARCHAR2
546 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
547 )
548 is
549 d_mod CONSTANT VARCHAR2(100) := D_validate_set;
550 d_position NUMBER := 0;
551 l_val VARCHAR2(2000);
552 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
553 l_result_type VARCHAR2(30);
554 l_name_value_pair po_name_value_pair_tab;
555 BEGIN
556
557 IF PO_LOG.d_proc THEN
558 PO_LOG.proc_begin(d_mod,'p_validation_set',p_validation_set);
559 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
560 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
561 END IF;
562 d_position := 10;
563 IF (x_result_type IS NULL) THEN
564 x_result_type := c_result_type_SUCCESS;
565 END IF;
566
567 IF (x_results IS NULL) THEN
568 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
569 END IF;
570
571 --loop through the validation set, performing each validation
572 FOR i IN 1 .. p_validation_set.COUNT LOOP
573 d_position := 20;
574
575 l_val := p_validation_set(i);
576
577 IF d_stmt THEN
578 PO_LOG.stmt(d_mod,d_position,'p_validation_set('||i||')',l_val);
579 END IF;
580
581 d_position := 30;
582
583 BEGIN
584
585 CASE l_val
586
587 --multi mod validations
588 WHEN c_atleast_one_doc_selected THEN
589
590 PO_MULTI_MOD_VALIDATIONS.atleast_one_doc_selected
591 ( p_request_id => p_multi_mod_request_id
592 ,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
593 ,p_validation_type => p_validation_type
594 ,x_result_type => l_result_type
595 ,x_results => x_results
596 );
597
598 WHEN c_exemption_reason_given THEN
599
600 PO_MULTI_MOD_VALIDATIONS.exemption_reason_given
601 ( p_request_id => p_multi_mod_request_id
602 ,p_exemption_reason => p_exemption_reason
603 ,p_initiate_app_flag => p_initiate_approval_flag
604 ,p_validation_type => p_validation_type
605 ,x_result_type => l_result_type
606 ,x_results => x_results
607 );
608
609 WHEN c_new_vendor_contact_provided THEN
610
611 PO_MULTI_MOD_VALIDATIONS.new_vendor_contact_provided
612 ( p_request_id => p_multi_mod_request_id
613 ,p_acceptance_reqd_flag => p_acceptance_reqd_flag
614 ,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
615 ,p_document_id_tbl => p_multi_mod_docs.document_id
616 ,p_change_id_tbl => p_multi_mod_changes.multi_mod_change_id
617 ,p_change_type_tbl => p_multi_mod_changes.change_type
618 ,p_org_id_tbl => p_multi_mod_changes.org_id
619 ,p_new_vendor_site_id_tbl => p_multi_mod_changes.new_vendor_site_id
620 ,p_new_vendor_contact_id_tbl => p_multi_mod_changes.new_vendor_contact_id
621 ,p_validation_type => p_validation_type
622 ,x_result_type => l_result_type
623 ,x_results => x_results
624 );
625
626 WHEN c_acceptance_due_date_valid THEN
627
628 PO_MULTI_MOD_VALIDATIONS.acceptance_due_date_valid
629 ( p_request_id => p_multi_mod_request_id
630 ,p_acceptance_reqd_flag => p_acceptance_reqd_flag
631 ,p_acceptance_due_date => p_acceptance_due_date
632 ,p_validation_type => p_validation_type
633 ,x_result_type => l_result_type
634 ,x_results => x_results
635 );
636
637 WHEN c_no_of_copies_ge_zero THEN
638
639 PO_MULTI_MOD_VALIDATIONS.no_of_copies_ge_zero
640 ( p_request_id => p_multi_mod_request_id
641 ,p_acceptance_reqd_flag => p_acceptance_reqd_flag
642 ,p_clm_noofcopies => p_clm_noofcopies
643 ,p_validation_type => p_validation_type
644 ,x_result_type => l_result_type
645 ,x_results => x_results
646 );
647
648 WHEN c_vendor_name_chg_valid THEN
649
650 PO_MULTI_MOD_VALIDATIONS.vendor_name_chg_valid
651 ( p_request_id => p_multi_mod_request_id
652 ,p_change_id_tbl => p_multi_mod_changes.multi_mod_change_id
653 ,p_change_type_tbl => p_multi_mod_changes.change_type
654 ,p_old_vendor_name_tbl => p_multi_mod_changes.old_vendor_name
655 ,p_new_vendor_name_tbl => p_multi_mod_changes.new_vendor_name
656 ,p_validation_type => p_validation_type
657 ,x_result_type => l_result_type
658 ,x_results => x_results
659 );
660
661 WHEN c_clause_change_valid THEN
662
663 PO_MULTI_MOD_VALIDATIONS.clause_change_valid
664 ( p_request_id => p_multi_mod_request_id
665 ,p_validation_type => p_validation_type
666 ,x_result_type => l_result_type
667 ,x_results => x_results
668 );
669
670 WHEN c_atleast_one_change_done THEN
671
672 PO_MULTI_MOD_VALIDATIONS.atleast_one_change_done
673 ( p_request_id => p_multi_mod_request_id
674 ,p_change_id_tbl => p_multi_mod_changes.multi_mod_change_id
675 ,p_validation_type => p_validation_type
676 ,x_result_type => l_result_type
677 ,x_results => x_results
678 );
679
680 --vendor change specific validations
681 WHEN c_new_vendor_site_provided THEN
682
683 PO_MULTI_MOD_VALIDATIONS.new_vendor_site_provided
684 ( p_request_id => p_multi_mod_request_id
685 ,p_change_id_tbl => p_multi_mod_changes.multi_mod_change_id
686 ,p_change_type_tbl => p_multi_mod_changes.change_type
687 ,p_org_id_tbl => p_multi_mod_changes.org_id
688 ,p_old_vendor_site_id_tbl => p_multi_mod_changes.old_vendor_site_id
689 ,p_new_vendor_site_id_tbl => p_multi_mod_changes.new_vendor_site_id
690 ,p_validation_type => p_validation_type
691 ,x_result_type => l_result_type
692 ,x_results => x_results
693 );
694
695 WHEN c_new_vendor_site_CCR_valid THEN
696
697 PO_MULTI_MOD_VALIDATIONS.new_vendor_site_CCR_valid
698 ( p_request_id => p_multi_mod_request_id
699 ,p_change_id_tbl => p_multi_mod_changes.multi_mod_change_id
700 ,p_change_type_tbl => p_multi_mod_changes.change_type
701 ,p_old_vendor_site_id_tbl => p_multi_mod_changes.old_vendor_site_id
702 ,p_new_vendor_site_id_tbl => p_multi_mod_changes.new_vendor_site_id
703 ,p_validation_type => p_validation_type
704 ,x_result_type => l_result_type
705 ,x_results => x_results
706 );
707
708 WHEN c_vendor_chg_valid THEN
709
710 PO_MULTI_MOD_VALIDATIONS.vendor_chg_valid
711 ( p_request_id => p_multi_mod_request_id
712 ,p_change_id_tbl => p_multi_mod_changes.multi_mod_change_id
713 ,p_change_type_tbl => p_multi_mod_changes.change_type
714 ,p_old_vendor_id_tbl => p_multi_mod_changes.old_vendor_id
715 ,p_new_vendor_id_tbl => p_multi_mod_changes.new_vendor_id
716 ,p_validation_type => p_validation_type
717 ,x_result_type => l_result_type
718 ,x_results => x_results
719 );
720 WHEN c_new_vendor_on_hold THEN
721
722 PO_MULTI_MOD_VALIDATIONS.new_vendor_on_hold
723 ( p_request_id => p_multi_mod_request_id
724 ,p_change_id_tbl => p_multi_mod_changes.multi_mod_change_id
725 ,p_change_type_tbl => p_multi_mod_changes.change_type
726 ,p_new_vendor_id_tbl => p_multi_mod_changes.new_vendor_id
727 ,p_validation_type => p_validation_type
728 ,x_result_type => l_result_type
729 ,x_results => x_results
730 );
731
732 WHEN c_uda_addresses_valid THEN
733 PO_MULTI_MOD_VALIDATIONS.uda_addresses_valid
734 ( p_request_id => p_multi_mod_request_id
735 ,x_result_type => l_result_type
736 ,x_results => x_results
737 );
738
739 WHEN c_contract_officer_provided THEN
740 PO_MULTI_MOD_VALIDATIONS.contract_officer_provided
741 ( p_request_id => p_multi_mod_request_id
742 ,p_clm_contract_officer => p_clm_contract_officer
743 ,p_validation_type => p_validation_type
744 ,p_org_id_tbl => p_multi_mod_changes.org_id
745 ,x_result_type => l_result_type
746 ,x_results => x_results
747 );
748
749 WHEN c_header_lock_compatible THEN
750
751 PO_MULTI_MOD_VALIDATIONS.header_lock_compatible(p_request_id => p_multi_mod_request_id
752 , x_result_type => l_result_type
753 , x_results => x_results
754 );
755
756 WHEN c_address_lock_compatible THEN
757
758 PO_MULTI_MOD_VALIDATIONS.address_lock_compatible(p_request_id => p_multi_mod_request_id
759 , x_result_type => l_result_type
760 , x_results => x_results
761 );
762
763 WHEN c_validate_open_mod THEN
764
765 PO_MULTI_MOD_VALIDATIONS.validate_open_mod(p_request_id => p_multi_mod_request_id
766 , x_result_type => l_result_type
767 , x_results => x_results
768 );
769
770 --concurrent program 'supplier change - ecxeptions' validations
771 WHEN c_generate_idv_exceptions THEN
772
773 PO_MULTI_MOD_VALIDATIONS.generate_idv_exceptions
774 ( p_request_id => p_multi_mod_request_id
775 -- ,p_document_id_tbl => p_multi_mod_docs.document_id
776 ,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
777 ,x_result_type => l_result_type
778 ,x_results => x_results
779 );
780
781 WHEN c_generate_award_exceptions THEN
782
783 PO_MULTI_MOD_VALIDATIONS.generate_award_exceptions
784 ( p_request_id => p_multi_mod_request_id
785 -- ,p_document_id_tbl => p_multi_mod_docs.document_id
786 ,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
787 ,x_result_type => l_result_type
788 ,x_results => x_results
789 );
790
791 WHEN c_generate_receipts_exceptions THEN
792
793 PO_MULTI_MOD_VALIDATIONS.generate_receipts_exceptions
794 ( p_request_id => p_multi_mod_request_id
795 ,p_document_id_tbl => p_multi_mod_docs.document_id
796 ,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
797 ,p_novation_effective_date => p_novation_effective_date
798 ,x_result_type => l_result_type
799 ,x_results => x_results
800 );
801
802 WHEN c_generate_invoice_exceptions THEN
803
804 PO_MULTI_MOD_VALIDATIONS.generate_invoice_exceptions
805 ( p_request_id => p_multi_mod_request_id
806 ,p_document_id_tbl => p_multi_mod_docs.document_id
807 ,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
808 ,p_novation_effective_date => p_novation_effective_date
809 ,x_result_type => l_result_type
810 ,x_results => x_results
811 );
812
813 WHEN c_generate_project_exceptions THEN
814
815 PO_MULTI_MOD_VALIDATIONS.generate_project_exceptions
816 ( p_request_id => p_multi_mod_request_id
817 ,p_document_id_tbl => p_multi_mod_docs.document_id
818 ,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
819 ,p_novation_effective_date => p_novation_effective_date
820 ,x_result_type => l_result_type
821 ,x_results => x_results
822 );
823
824 WHEN c_generate_custom_exceptions THEN
825
826 PO_CUSTOM_MULTIMOD_VAL_PVT.generate_custom_exceptions
827 ( p_name_value_pair => l_name_value_pair
828 ,x_result_type => l_result_type
829 ,x_results => x_results
830 );
831
832 ELSE
833 d_position := 150;
834 IF PO_LOG.d_exc THEN
835 PO_LOG.exc(d_mod,d_position,'Invalid identifier in validation set: '||l_val);
836 END IF;
837 RAISE CASE_NOT_FOUND;
838
839 END CASE;
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 IF PO_LOG.d_exc THEN
844 PO_LOG.exc(d_mod,d_position,
845 'Validation subroutine '||l_val||' threw exception');
846 END IF;
847
848 x_result_type := c_result_type_FATAL;
849
850 x_results.add_result(
851 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
852 , p_multi_mod_request_id => p_multi_mod_request_id
853 , p_validation_type => p_validation_type
854 , p_result_type => c_result_type_FATAL
855 , p_message_application => 'PO'
856 , p_message_name => PO_MESSAGE_S.PO_ALL_SQL_ERROR
857 , p_token1_name => PO_MESSAGE_S.c_ROUTINE_token
858 , p_token1_value => d_mod
859 , p_token2_name => PO_MESSAGE_S.c_ERR_NUMBER_token
860 , p_token2_value => TO_CHAR(d_position)
861 , p_token3_name => PO_MESSAGE_S.c_SQL_ERR_token
862 , p_token3_value => l_val
863 , p_token4_name => PO_MESSAGE_S.c_LSQL_ERR_token
864 , p_token4_value => SQLERRM
865 );
866 END;
867
868 IF (x_result_type = c_result_type_FATAL) THEN
869 d_position := 160;
870 -- x_result_type := l_result_type;
871 IF d_stmt THEN
872 PO_LOG.stmt(d_mod,d_position,'Stopping loop. x_result_type',x_result_type);
873 END IF;
874
875 EXIT;
876 END IF;
877 END LOOP;
878
879 IF x_results.result_type.COUNT > 0 THEN
880 x_result_type := c_result_type_WARNING;
881 ELSIF x_results.result_type.COUNT = 0 THEN
882 x_result_type := c_result_type_SUCCESS;
883 END IF;
884
885 d_position := 170;
886 IF PO_LOG.d_proc THEN
887 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
888 END IF;
889
890 EXCEPTION
891 WHEN OTHERS THEN
892 x_result_type := c_result_type_FAILURE;
893 IF PO_LOG.d_exc THEN
894 PO_LOG.exc(d_mod,d_position,NULL);
895 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
896 END IF;
897 RAISE;
898
899 END validate_set;
900
901
902 -------------------------------------------------------------------------------
903 --Start of Comments
904 --Name: atleast_one_doc_selected
905 --Pre-reqs: None.
906 --Function:
907 -- Checks if atleast one document is selected for change.
908 --Parameters:
909 --IN:
910 --p_request_id
911 -- Request id from po_multi_mod_requests
912 --p_multi_mod_doc_id_tbl
913 -- Table type having the multi_mod_doc_id for the pertaining request_id
914 --p_validation_type
915 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
916 --OUT:
917 --x_result_type
918 -- Identifies if the validation was a success or failure
919 --x_results
920 -- Out variable that holds the validation results
921 --End of Comments
922 -------------------------------------------------------------------------------
923 PROCEDURE atleast_one_doc_selected
924 ( p_request_id IN NUMBER
925 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
926 ,p_validation_type IN VARCHAR2
927 ,x_result_type OUT NOCOPY VARCHAR2
928 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
929 )
930 is
931 l_results_count NUMBER;
932 d_mod CONSTANT VARCHAR2(100) := D_atleast_one_doc_selected;
933 d_position NUMBER := 0;
934 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
935
936 BEGIN
937 IF PO_LOG.d_proc THEN
938 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
939 PO_LOG.proc_begin(d_mod,'p_multi_mod_doc_id_tbl',p_multi_mod_doc_id_tbl);
940 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
941 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
942 END IF;
943
944 IF (x_results IS NULL) THEN
945 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
946 END IF;
947
948 d_position := 10;
949
950 l_results_count := x_results.result_type.COUNT;
951 IF d_stmt THEN
952 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
953 END IF;
954
955 IF p_multi_mod_doc_id_tbl.COUNT < 1 THEN
956 d_position := 20;
957 x_results.add_result(
958 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
959 , p_multi_mod_request_id => p_request_id
960 , p_result_type => 'ERROR'
961 , p_validation_type => p_validation_type
962 , p_message_application => 'PO'
963 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NO_DOC_SELECTED
964 );
965 END IF;
966 d_position := 30;
967
968 IF l_results_count < x_results.result_type.COUNT THEN
969 x_result_type := c_result_type_FAILURE;
970 ELSE
971 x_result_type := c_result_type_SUCCESS;
972 END IF;
973
974 IF PO_LOG.d_proc THEN
975 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
976 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
977 END IF;
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 IF PO_LOG.d_exc THEN
982 PO_LOG.exc(d_mod,d_position,NULL);
983 END IF;
984 RAISE;
985
986 END atleast_one_doc_selected;
987
988 -------------------------------------------------------------------------------
989 --Start of Comments
990 --Name: exemption_reason_given
991 --Pre-reqs: None.
992 --Function:
993 -- Checks if exemption reason is specified when the initiate approval flag is unchecked
994 --Parameters:
995 --IN:
996 --p_request_id
997 -- Request id from po_multi_mod_requests
998 --p_exemption_reason
999 -- exemption reason from po_multi_mod_requests
1000 --p_initiate_app_flag
1001 -- initiate approval flag from po_multi_mod_requests
1002 --p_validation_type
1003 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1004 --OUT:
1005 --x_result_type
1006 -- Identifies if the validation was a success or failure
1007 --x_results
1008 -- Out variable that holds the validation results
1009 --End of Comments
1010 -------------------------------------------------------------------------------
1011 PROCEDURE exemption_reason_given
1012 ( p_request_id IN NUMBER
1013 ,p_exemption_reason IN VARCHAR2
1014 ,p_initiate_app_flag IN VARCHAR2
1015 ,p_validation_type IN VARCHAR2
1016 ,x_result_type OUT NOCOPY VARCHAR2
1017 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1018 )
1019 is
1020 l_results_count NUMBER;
1021 d_mod CONSTANT VARCHAR2(100) := D_exemption_reason_given;
1022 d_position NUMBER := 0;
1023 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1024
1025 BEGIN
1026 IF PO_LOG.d_proc THEN
1027 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1028 PO_LOG.proc_begin(d_mod,'p_exemption_reason',p_exemption_reason);
1029 PO_LOG.proc_begin(d_mod,'p_initiate_app_flag',p_initiate_app_flag);
1030 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1031 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1032 END IF;
1033
1034 IF (x_results IS NULL) THEN
1035 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1036 END IF;
1037
1038 d_position := 10;
1039
1040 l_results_count := x_results.result_type.COUNT;
1041 IF d_stmt THEN
1042 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1043 END IF;
1044
1045
1046 IF p_initiate_app_flag = 'Y' AND p_exemption_reason IS NULL THEN
1047 d_position := 20;
1048 x_results.add_result(
1049 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1050 , p_multi_mod_request_id => p_request_id
1051 , p_result_type => 'ERROR'
1052 , p_validation_type => p_validation_type
1053 , p_message_application => 'PO'
1054 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NO_EXEMPT_REASON
1055 );
1056 END IF; --IF p_initiate_app_flag
1057
1058
1059 d_position := 30;
1060
1061 IF l_results_count < x_results.result_type.COUNT THEN
1062 x_result_type := c_result_type_FAILURE;
1063 ELSE
1064 x_result_type := c_result_type_SUCCESS;
1065 END IF;
1066
1067 IF PO_LOG.d_proc THEN
1068 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1069 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1070 END IF;
1071
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 IF PO_LOG.d_exc THEN
1075 PO_LOG.exc(d_mod,d_position,NULL);
1076 END IF;
1077 RAISE;
1078
1079 END exemption_reason_given;
1080
1081 -------------------------------------------------------------------------------
1082 --Start of Comments
1083 --Name: has_clause_changed
1084 --Pre-reqs: None.
1085 --Function:
1086 -- Checks if new vendor contact is provided if the bi-lateral signature is enforced
1087 --Parameters:
1088 --IN:
1089 --p_request_id
1090 -- Request id from po_multi_mod_requests
1091 --p_acceptance_reqd_flag
1092 -- will hold the value of the bilateral indicator flag
1093 --p_multi_mod_doc_id_tbl
1094 -- Table type having the multi_mod_doc_id for the pertaining request id
1095 --p_document_id_tbl
1096 -- Table type having the document_ids of the documents selected for change for the pertaining request id
1097 --p_change_id_tbl
1098 -- Table type having all the multi_mod_change_ids for a particular request_id in po_multi_mod_changes
1099 --p_change_type_tbl
1100 -- Table type having all the change_types for a particular request_id in po_multi_mod_changes
1101 --p_org_id_tbl
1102 -- Table type having all the org_ids for a particular request_id in po_multi_mod_changes
1103 --p_old_vendor_site_id_tbl
1104 -- Table type having all the old_vendor_site_ids for a particular request_id in po_multi_mod_changes
1105 --p_new_vendor_site_id_tbl
1106 -- Table type having all the new_vendor_site_ids for a particular request_id in po_multi_mod_changes
1107 --p_validation_type
1108 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1109 --OUT:
1110 --x_result_type
1111 -- Identifies if the validation was a success or failure
1112 --x_results
1113 -- Out variable that holds the validation results
1114 --End of Comments
1115 -------------------------------------------------------------------------------
1116 PROCEDURE new_vendor_contact_provided
1117 ( p_request_id IN NUMBER
1118 ,p_acceptance_reqd_flag IN VARCHAR2
1119 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
1120 ,p_document_id_tbl IN PO_TBL_NUMBER
1121 ,p_change_id_tbl IN PO_TBL_NUMBER
1122 ,p_change_type_tbl IN PO_TBL_VARCHAR30
1123 ,p_org_id_tbl IN PO_TBL_NUMBER
1124 ,p_new_vendor_site_id_tbl IN PO_TBL_NUMBER
1125 ,p_new_vendor_contact_id_tbl IN PO_TBL_NUMBER
1126 ,p_validation_type IN VARCHAR2
1127 ,x_result_type OUT NOCOPY VARCHAR2
1128 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1129 )
1130 is
1131 l_results_count NUMBER;
1132 d_mod CONSTANT VARCHAR2(100) := D_new_vendor_contact_provided;
1133 d_position NUMBER := 0;
1134 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1135 l_vendor_contact_id NUMBER;
1136 BEGIN
1137 IF PO_LOG.d_proc THEN
1138 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1139 PO_LOG.proc_begin(d_mod,'p_acceptance_required_flag',p_acceptance_reqd_flag);
1140 PO_LOG.proc_begin(d_mod,'p_multi_mod_doc_id_tbl',p_multi_mod_doc_id_tbl);
1141 PO_LOG.proc_begin(d_mod,'p_document_id_tbl',p_document_id_tbl);
1142 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
1143 PO_LOG.proc_begin(d_mod,'p_change_type_tbl',p_change_type_tbl);
1144 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1145 PO_LOG.proc_begin(d_mod,'p_new_vendor_site_id_tbl',p_new_vendor_site_id_tbl);
1146 PO_LOG.proc_begin(d_mod,'p_new_vendor_contact_id_tbl',p_new_vendor_contact_id_tbl);
1147 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1148 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1149 END IF;
1150
1151 IF (x_results IS NULL) THEN
1152 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1153 END IF;
1154
1155 d_position := 10;
1156
1157 l_results_count := x_results.result_type.COUNT;
1158 IF d_stmt THEN
1159 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1160 END IF;
1161
1162 IF p_acceptance_reqd_flag IN ('S','P') THEN
1163 d_position := 20;
1164 IF p_change_id_tbl.COUNT > 0 THEN
1165 d_position := 30;
1166 FOR i IN 1..p_change_id_tbl.COUNT LOOP
1167 d_position := 40;
1168 IF (p_change_type_tbl(i) = 'VENDOR_SITE_CONTACT'
1169 AND p_org_id_tbl(i) is not null
1170 AND p_new_vendor_site_id_tbl(i) IS NOT NULL
1171 AND p_new_vendor_contact_id_tbl(i) IS NULL) THEN
1172
1173 d_position := 50;
1174 x_results.add_result(
1175 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1176 , p_multi_mod_request_id => p_request_id
1177 , p_result_type => 'ERROR'
1178 , p_validation_type => p_validation_type
1179 , p_message_application => 'PO'
1180 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NO_VENDOR_CONTACT
1181 , p_token1_name => 'NEW_SUPPLIER_SITE'
1182 , p_token1_value => p_new_vendor_site_id_tbl(i)
1183 );
1184 END IF;
1185
1186 END LOOP; --i IN 1..p_change_id_tbl.COUNT
1187 ELSE
1188 d_position := 60;
1189 FOR i IN 1..p_multi_mod_doc_id_tbl.COUNT LOOP
1190 d_position := 70;
1191 SELECT vendor_contact_id
1192 INTO l_vendor_contact_id
1193 FROM po_headers_all
1194 WHERE po_header_id = p_document_id_tbl(i);
1195
1196 IF d_stmt THEN
1197 PO_LOG.stmt(d_mod,d_position,'p_document_id_tbl(i)',p_document_id_tbl(i));
1198 PO_LOG.stmt(d_mod,d_position,'l_vendor_contact_id',l_vendor_contact_id);
1199 END IF;
1200
1201 IF l_vendor_contact_id IS NULL THEN
1202 d_position := 80;
1203 x_results.add_result(
1204 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1205 , p_multi_mod_request_id => p_request_id
1206 , P_multi_mod_doc_id => p_multi_mod_doc_id_tbl(i)
1207 , p_result_type => 'ERROR'
1208 , P_document_id => p_document_id_tbl(i)
1209 , p_validation_type => p_validation_type
1210 , p_message_application => 'PO'
1211 , p_message_name => PO_MESSAGE_S.PO_SUB_ENTER_VENDOR_CONTACT
1212 );
1213 END IF; --l_vendor_contact_id
1214 END LOOP; --1..p_multi_mod_doc_id_tbl.COUNT
1215 END IF; --IF p_change_id_tbl.COUNT > 0
1216
1217 d_position := 90;
1218
1219 END IF; --p_acceptance_reqd_flag IN ('S','P')
1220
1221 d_position := 100;
1222
1223 IF l_results_count < x_results.result_type.COUNT THEN
1224 x_result_type := c_result_type_WARNING;
1225 ELSE
1226 x_result_type := c_result_type_SUCCESS;
1227 END IF;
1228
1229 IF PO_LOG.d_proc THEN
1230 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1231 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1232 END IF;
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 IF PO_LOG.d_exc THEN
1237 PO_LOG.exc(d_mod,d_position,NULL);
1238 END IF;
1239 RAISE;
1240
1241 END new_vendor_contact_provided;
1242
1243 -------------------------------------------------------------------------------
1244 --Start of Comments
1245 --Name: acceptance_due_date_valid
1246 --Pre-reqs: None.
1247 --Function:
1248 -- checks if acceptance due date is provided when bi-lateral signature is enabled
1249 --Parameters:
1250 --IN:
1251 --p_request_id
1252 -- Request id from po_multi_mod_requests
1253 --p_acceptance_reqd_flag
1254 -- Indicates if bi-lateral signature is required
1255 --p_acceptance_due_date
1256 -- The date by which the document needs to be acknowledged
1257 --p_validation_type
1258 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1259 --OUT:
1260 --x_result_type
1261 -- Identifies if the validation was a success or failure
1262 --x_results
1263 -- Out variable that holds the validation results
1264 --End of Comments
1265 -------------------------------------------------------------------------------
1266 PROCEDURE acceptance_due_date_valid
1267 ( p_request_id IN NUMBER
1268 ,p_acceptance_reqd_flag IN VARCHAR2
1269 ,p_acceptance_due_date IN DATE
1270 ,p_validation_type IN VARCHAR2
1271 ,x_result_type OUT NOCOPY VARCHAR2
1272 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1273 )
1274 is
1275 l_results_count NUMBER;
1276 d_mod CONSTANT VARCHAR2(100) := D_acceptance_due_date_valid;
1277 d_position NUMBER := 0;
1278 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1279
1280 BEGIN
1281 IF PO_LOG.d_proc THEN
1282 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1283 PO_LOG.proc_begin(d_mod,'p_acceptance_reqd_flag',p_acceptance_reqd_flag);
1284 PO_LOG.proc_begin(d_mod,'p_acceptance_due_date',p_acceptance_due_date);
1285 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1286 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1287 END IF;
1288
1289 IF (x_results IS NULL) THEN
1290 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1291 END IF;
1292
1293 l_results_count := x_results.result_type.COUNT;
1294 IF d_stmt THEN
1295 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1296 END IF;
1297
1298 d_position := 10;
1299
1300 IF p_acceptance_reqd_flag IN ('S','P') AND (p_acceptance_due_date Is NULL OR p_acceptance_due_date <= SYSDATE) THEN
1301 d_position := 20;
1302 x_results.add_result(
1303 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1304 , p_multi_mod_request_id => p_request_id
1305 , p_result_type => 'ERROR'
1306 , p_validation_type => p_validation_type
1307 , p_message_application => 'PO'
1308 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_INVALID_ACK_BY
1309 );
1310 END IF;
1311
1312 d_position := 30;
1313 IF l_results_count < x_results.result_type.COUNT THEN
1314 x_result_type := c_result_type_FAILURE;
1315 ELSE
1316 x_result_type := c_result_type_SUCCESS;
1317 END IF;
1318
1319 IF PO_LOG.d_proc THEN
1320 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1321 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1322 END IF;
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 IF PO_LOG.d_exc THEN
1327 PO_LOG.exc(d_mod,d_position,NULL);
1328 END IF;
1329 RAISE;
1330
1331 END acceptance_due_date_valid;
1332
1333 -------------------------------------------------------------------------------
1334 --Start of Comments
1335 --Name: no_of_copies_ge_zero
1336 --Pre-reqs: None.
1337 --Function:
1338 -- Checks if clm_noofcopies is greater than 0 when bi-lateral signature is required
1339 --Parameters:
1340 --IN:
1341 --p_request_id
1342 -- Request id from po_multi_mod_request
1343 --p_clm_noofcopies
1344 --p_validation_type
1345 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1346 --OUT:
1347 --x_result_type
1348 -- Identifies if the validation was a success or failure
1349 --x_results
1350 -- Out variable that holds the validation results
1351 --End of Comments
1352 -------------------------------------------------------------------------------
1353
1354 PROCEDURE no_of_copies_ge_zero
1355 ( p_request_id IN NUMBER
1356 ,p_acceptance_reqd_flag IN VARCHAR2
1357 ,p_clm_noofcopies IN NUMBER
1358 ,p_validation_type IN VARCHAR2
1359 ,x_result_type OUT NOCOPY VARCHAR2
1360 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1361 )
1362 is
1363 l_results_count NUMBER;
1364 d_mod CONSTANT VARCHAR2(100) := D_no_of_copies_ge_zero;
1365 d_position NUMBER := 0;
1366 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1367
1368 BEGIN
1369 IF PO_LOG.d_proc THEN
1370 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1371 PO_LOG.proc_begin(d_mod,'p_acceptance_reqd_flag',p_acceptance_reqd_flag);
1372 PO_LOG.proc_begin(d_mod,'p_clm_noofcopies',p_clm_noofcopies);
1373 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1374 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1375 END IF;
1376
1377 IF (x_results IS NULL) THEN
1378 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1379 END IF;
1380
1381 l_results_count := x_results.result_type.COUNT;
1382 IF d_stmt THEN
1383 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1384 END IF;
1385
1386 d_position := 10;
1387
1388 IF (p_acceptance_reqd_flag IN ('S','P')) AND (NVL(p_clm_noofcopies,0) <= 0) THEN
1389
1390
1391 x_results.add_result(
1392 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1393 , p_multi_mod_request_id => p_request_id
1394 , p_result_type => 'ERROR'
1395 , p_validation_type => p_validation_type
1396 , p_message_application => 'PO'
1397 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NOOFCOPIES_GE_ZERO
1398 );
1399 END IF;
1400
1401 d_position := 20;
1402 IF l_results_count < x_results.result_type.COUNT THEN
1403 x_result_type := c_result_type_FAILURE;
1404 ELSE
1405 x_result_type := c_result_type_SUCCESS;
1406 END IF;
1407
1408 IF PO_LOG.d_proc THEN
1409 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1410 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1411 END IF;
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 IF PO_LOG.d_exc THEN
1416 PO_LOG.exc(d_mod,d_position,NULL);
1417 END IF;
1418 RAISE;
1419
1420 END no_of_copies_ge_zero;
1421
1422
1423 -------------------------------------------------------------------------------
1424 --Start of Comments
1425 --Name: vendor_name_chg_valid
1426 --Pre-reqs: None.
1427 --Function:
1428 -- Checks if the new vendor name provided is not same as old vendor name in case of vendor_name_change
1429 --Parameters:
1430 --IN:
1431 --p_request_id
1432 -- Request id from po_multi_mod_requests
1433 --p_change_id_tbl
1434 --p_change_type_tbl
1435 --p_old_vendor_name_tbl
1436 --p_new_vendor_name_tbl
1437 --p_validation_type
1438 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1439 --OUT:
1440 --x_result_type
1441 -- Identifies if the validation was a success or failure
1442 --x_results
1443 -- Out variable that holds the validation results
1444 --End of Comments
1445 -------------------------------------------------------------------------------
1446
1447 PROCEDURE vendor_name_chg_valid
1448 ( p_request_id IN NUMBER
1449 ,p_change_id_tbl PO_TBL_NUMBER
1450 ,p_change_type_tbl PO_TBL_VARCHAR30
1451 ,p_old_vendor_name_tbl PO_TBL_VARCHAR240
1452 ,p_new_vendor_name_tbl PO_TBL_VARCHAR240
1453 ,p_validation_type IN VARCHAR2
1454 ,x_result_type OUT NOCOPY VARCHAR2
1455 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1456 )
1457 is
1458 l_results_count NUMBER;
1459 d_mod CONSTANT VARCHAR2(100) := D_vendor_name_chg_valid;
1460 d_position NUMBER := 0;
1461 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1462
1463 BEGIN
1464 IF PO_LOG.d_proc THEN
1465 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1466 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
1467 PO_LOG.proc_begin(d_mod,'p_change_type_tbl',p_change_type_tbl);
1468 --PO_LOG.proc_begin(d_mod,'p_old_vendor_name_tbl',p_old_vendor_name_tbl);
1469 --PO_LOG.proc_begin(d_mod,'p_new_vendor_name_tbl',p_new_vendor_name_tbl);
1470 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1471 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1472 END IF;
1473
1474 IF (x_results IS NULL) THEN
1475 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1476 END IF;
1477
1478 l_results_count := x_results.result_type.COUNT;
1479 IF d_stmt THEN
1480 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1481 END IF;
1482
1483 d_position := 10;
1484
1485 FOR i IN 1..p_change_id_tbl.COUNT LOOP
1486 d_position := 40;
1487 IF p_change_type_tbl(i) = 'VENDOR_NAME' THEN
1488 IF p_old_vendor_name_tbl(i) = p_new_vendor_name_tbl(i) THEN
1489 d_position := 50;
1490 x_results.add_result(
1491 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1492 , p_multi_mod_request_id => p_request_id
1493 , p_result_type => 'ERROR'
1494 , p_validation_type => p_validation_type
1495 , p_message_application => 'PO'
1496 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_INV_VNDRNAME_CHG
1497 , p_token1_name => 'NEW_SUPPLIER'
1498 , p_token1_value => p_new_vendor_name_tbl(i)
1499 , p_token2_name => 'OLD_SUPPLIER'
1500 , p_token2_value => p_old_vendor_name_tbl(i)
1501 );
1502 END IF; --IF p_old_vendor_name_tbl(i) = p_new_vendor_name_tbl(i) THEN
1503 END IF; --IF p_change_type_tbl(i) = 'VENDOR_NAME' THEN
1504 END LOOP;
1505
1506 d_position := 60;
1507
1508 IF l_results_count < x_results.result_type.COUNT THEN
1509 x_result_type := c_result_type_FAILURE;
1510 ELSE
1511 x_result_type := c_result_type_SUCCESS;
1512 END IF;
1513
1514 IF PO_LOG.d_proc THEN
1515 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1516 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1517 END IF;
1518
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 IF PO_LOG.d_exc THEN
1522 PO_LOG.exc(d_mod,d_position,NULL);
1523 END IF;
1524 RAISE;
1525
1526 END vendor_name_chg_valid;
1527
1528 -------------------------------------------------------------------------------
1529 --Start of Comments
1530 --Name: vendor_chg_valid
1531 --Pre-reqs: None.
1532 --Function:
1533 -- Checks if the new vendor id provided is not same as old vendor id in case of vendor_change
1534 --Parameters:
1535 --IN:
1536 --p_request_id
1537 -- Request id from po_multi_mod_requests
1538 --p_change_id_tbl
1539 --p_change_type_tbl
1540 --p_old_vendor_id_tbl
1541 --p_new_vendor_id_tbl
1542 --p_validation_type
1543 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1544 --OUT:
1545 --x_result_type
1546 -- Identifies if the validation was a success or failure
1547 --x_results
1548 -- Out variable that holds the validation results
1549 --End of Comments
1550 -------------------------------------------------------------------------------
1551
1552 PROCEDURE vendor_chg_valid
1553 ( p_request_id IN NUMBER
1554 ,p_change_id_tbl PO_TBL_NUMBER
1555 ,p_change_type_tbl PO_TBL_VARCHAR30
1556 ,p_old_vendor_id_tbl PO_TBL_NUMBER
1557 ,p_new_vendor_id_tbl PO_TBL_NUMBER
1558 ,p_validation_type IN VARCHAR2
1559 ,x_result_type OUT NOCOPY VARCHAR2
1560 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1561 )
1562 is
1563 l_results_count NUMBER;
1564 d_mod CONSTANT VARCHAR2(100) := D_vendor_chg_valid;
1565 d_position NUMBER := 0;
1566 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1567
1568 BEGIN
1569 IF PO_LOG.d_proc THEN
1570 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1571 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
1572 PO_LOG.proc_begin(d_mod,'p_change_type_tbl',p_change_type_tbl);
1573 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1574 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1575 END IF;
1576
1577 IF (x_results IS NULL) THEN
1578 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1579 END IF;
1580
1581 l_results_count := x_results.result_type.COUNT;
1582 IF d_stmt THEN
1583 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1584 END IF;
1585
1586 d_position := 10;
1587
1588 FOR i IN 1..p_change_id_tbl.COUNT LOOP
1589 d_position := 40;
1590 IF p_change_type_tbl(i) = 'VENDOR' THEN
1591 IF p_old_vendor_id_tbl(i) = p_new_vendor_id_tbl(i) THEN
1592 d_position := 50;
1593 x_results.add_result(
1594 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1595 , p_multi_mod_request_id => p_request_id
1596 , p_result_type => 'ERROR'
1597 , p_validation_type => p_validation_type
1598 , p_message_application => 'PO'
1599 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_INV_VNDR_CHG
1600 , p_token1_name => 'NEW_SUPPLIER'
1601 , p_token1_value => p_new_vendor_id_tbl(i)
1602 , p_token2_name => 'OLD_SUPPLIER'
1603 , p_token2_value => p_old_vendor_id_tbl(i)
1604 );
1605 END IF; --IF p_old_vendor_id_tbl(i) = p_new_vendor_id_tbl(i) THEN
1606 END IF; --IF p_change_type_tbl(i) = 'VENDOR_NAME' THEN
1607 END LOOP;
1608
1609 d_position := 60;
1610
1611 IF l_results_count < x_results.result_type.COUNT THEN
1612 x_result_type := c_result_type_FAILURE;
1613 ELSE
1614 x_result_type := c_result_type_SUCCESS;
1615 END IF;
1616
1617 IF PO_LOG.d_proc THEN
1618 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1619 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1620 END IF;
1621
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 IF PO_LOG.d_exc THEN
1625 PO_LOG.exc(d_mod,d_position,NULL);
1626 END IF;
1627 RAISE;
1628
1629 END vendor_chg_valid;
1630
1631 -------------------------------------------------------------------------------
1632 --Start of Comments
1633 --Name: new_vendor_on_hold
1634 --Pre-reqs: None.
1635 --Function:
1636 -- Checks if the new vendor is on hold
1637 --Parameters:
1638 --IN:
1639 --p_request_id
1640 -- Request id from po_multi_mod_requests
1641 --p_change_id_tbl
1642 --p_change_type_tbl
1643 --p_new_vendor_id_tbl
1644 --p_validation_type
1645 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1646 --OUT:
1647 --x_result_type
1648 -- Identifies if the validation was a success or failure
1649 --x_results
1650 -- Out variable that holds the validation results
1651 --End of Comments
1652 -------------------------------------------------------------------------------
1653
1654 PROCEDURE new_vendor_on_hold
1655 ( p_request_id IN NUMBER
1656 ,p_change_id_tbl PO_TBL_NUMBER
1657 ,p_change_type_tbl PO_TBL_VARCHAR30
1658 ,p_new_vendor_id_tbl PO_TBL_NUMBER
1659 ,p_validation_type IN VARCHAR2
1660 ,x_result_type OUT NOCOPY VARCHAR2
1661 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1662 )
1663 is
1664 l_results_count NUMBER;
1665 d_mod CONSTANT VARCHAR2(100) := D_new_vendor_on_hold;
1666 d_position NUMBER := 0;
1667 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1668 l_hold_exists VARCHAR2(1);
1669
1670 BEGIN
1671 IF PO_LOG.d_proc THEN
1672 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1673 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
1674 PO_LOG.proc_begin(d_mod,'p_change_type_tbl',p_change_type_tbl);
1675 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1676 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1677 END IF;
1678
1679 IF (x_results IS NULL) THEN
1680 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1681 END IF;
1682
1683 l_results_count := x_results.result_type.COUNT;
1684 IF d_stmt THEN
1685 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1686 END IF;
1687
1688 d_position := 10;
1689
1690 FOR i IN 1..p_change_id_tbl.COUNT LOOP
1691 d_position := 20;
1692 IF p_change_type_tbl(i) = 'VENDOR' THEN
1693 BEGIN
1694 SELECT 'Y'
1695 INTO l_hold_exists
1696 FROM po_vendors pov
1697 ,po_system_parameters psp
1698 WHERE pov.vendor_id = p_new_vendor_id_tbl(i)
1699 AND NVL(psp.enforce_vendor_hold_flag,'N') = 'Y'
1700 AND NVL(pov.hold_flag,'N') = 'Y';
1701
1702 EXCEPTION
1703 WHEN others THEN
1704 l_hold_exists := 'N';
1705 END;
1706
1707 IF l_hold_exists = 'Y' THEN
1708 d_position := 30;
1709 x_results.add_result(
1710 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1711 , p_multi_mod_request_id => p_request_id
1712 , p_result_type => 'ERROR'
1713 , p_validation_type => p_validation_type
1714 , p_message_application => 'PO'
1715 , p_message_name => PO_MESSAGE_S.PO_SUB_VENDOR_ON_HOLD
1716 );
1717 END IF; --Il_hold_exists = 'Y'
1718 END IF; --IF p_change_type_tbl(i) = 'VENDOR_NAME' THEN
1719 END LOOP;
1720
1721 d_position := 40;
1722
1723 IF l_results_count < x_results.result_type.COUNT THEN
1724 x_result_type := c_result_type_FAILURE;
1725 ELSE
1726 x_result_type := c_result_type_SUCCESS;
1727 END IF;
1728
1729 IF PO_LOG.d_proc THEN
1730 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1731 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1732 END IF;
1733
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 IF PO_LOG.d_exc THEN
1737 PO_LOG.exc(d_mod,d_position,NULL);
1738 END IF;
1739 RAISE;
1740
1741 END new_vendor_on_hold;
1742 -------------------------------------------------------------------------------
1743 --Start of Comments
1744 --Name: clause_change_valid
1745 --Pre-reqs: None.
1746 --Function:
1747 -- Checks if the clause change that has been done as part of a multi mod request is valid
1748 --Parameters:
1749 --IN:
1750 --p_request_id
1751 -- Request id from po_multi_mod_requests
1752 --p_validation_type
1753 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1754 --OUT:
1755 --x_result_type
1756 -- Identifies if the validation was a success or failure
1757 --x_results
1758 -- Out variable that holds the validation results
1759 --End of Comments
1760 -------------------------------------------------------------------------------
1761 PROCEDURE clause_change_valid
1762 ( p_request_id IN NUMBER
1763 ,p_validation_type IN VARCHAR2
1764 ,x_result_type OUT NOCOPY VARCHAR2
1765 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1766 )
1767 is
1768 l_results_count NUMBER;
1769 d_mod CONSTANT VARCHAR2(100) := D_clause_change_valid;
1770 d_position NUMBER := 0;
1771 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1772 l_return_status VARCHAR2(1);
1773
1774 BEGIN
1775 IF PO_LOG.d_proc THEN
1776 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1777 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1778 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1779 END IF;
1780
1781 IF (x_results IS NULL) THEN
1782 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1783 END IF;
1784
1785 d_position := 10;
1786
1787 l_results_count := x_results.result_type.COUNT;
1788 IF d_stmt THEN
1789 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1790 END IF;
1791
1792
1793 /*Call to the OKC api is made here. currently there are no validations.this dummy check is included so that we have the option of including additional checks in the future*/
1794 OKC_PO_MOD_PKG.multimod_clause_presubmit(p_multi_mod_req_id => p_request_id,
1795 x_result_type => x_result_type,
1796 x_results => x_results,
1797 x_return_status => l_return_status);
1798
1799 d_position := 30;
1800
1801 IF l_results_count < x_results.result_type.COUNT THEN
1802 x_result_type := c_result_type_FAILURE;
1803 ELSE
1804 x_result_type := c_result_type_SUCCESS;
1805 END IF;
1806
1807 IF PO_LOG.d_proc THEN
1808 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1809 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1810 END IF;
1811
1812 EXCEPTION
1813 WHEN OTHERS THEN
1814 IF PO_LOG.d_exc THEN
1815 PO_LOG.exc(d_mod,d_position,NULL);
1816 END IF;
1817 RAISE;
1818
1819 END clause_change_valid;
1820
1821 -------------------------------------------------------------------------------
1822 --Start of Comments
1823 --Name: atleast_one_change_done
1824 --Pre-reqs: None.
1825 --Function:
1826 -- Checks if atleast one change has been done as part of this multi mod request.There should either be a address change or clause change or vendor change
1827 --Parameters:
1828 --IN:
1829 --p_request_id
1830 -- Request id from po_multi_mod_requests
1831 --p_change_id_tbl
1832 -- Table type having all the multi_mod_change_ids for a particular request_id in po_multi_mod_changes
1833 --p_validation_type
1834 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1835 --OUT:
1836 --x_result_type
1837 -- Identifies if the validation was a success or failure
1838 --x_results
1839 -- Out variable that holds the validation results
1840 --End of Comments
1841 -------------------------------------------------------------------------------
1842 PROCEDURE atleast_one_change_done
1843 ( p_request_id IN NUMBER
1844 ,p_change_id_tbl IN PO_TBL_NUMBER
1845 ,p_validation_type IN VARCHAR2
1846 ,x_result_type OUT NOCOPY VARCHAR2
1847 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1848 )
1849 is
1850
1851 l_results_count NUMBER;
1852 d_mod CONSTANT VARCHAR2(100) := D_atleast_one_change_done;
1853 d_position NUMBER := 0;
1854 l_has_address_changed VARCHAR2(1);
1855 l_has_clause_changed VARCHAR2(1);
1856 l_has_vendor_changed VARCHAR2(1);
1857 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1858
1859 BEGIN
1860 IF PO_LOG.d_proc THEN
1861 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1862 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
1863 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1864 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1865 END IF;
1866
1867 IF (x_results IS NULL) THEN
1868 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1869 END IF;
1870
1871 l_results_count := x_results.result_type.COUNT;
1872 IF d_stmt THEN
1873 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
1874 END IF;
1875 d_position := 10;
1876 -- address change validation
1877 l_has_address_changed := PO_MULTI_MOD_VALIDATIONS.has_address_changed(p_request_id => p_request_id);
1878
1879 d_position := 20;
1880 -- clause change validation. OKC api
1881 l_has_clause_changed := PO_MULTI_MOD_VALIDATIONS.has_clause_changed(p_request_id => p_request_id);
1882
1883 d_position := 30;
1884 --supplier change validation
1885 l_has_vendor_changed := PO_MULTI_MOD_VALIDATIONS.has_vendor_changed(p_request_id => p_request_id
1886 ,p_change_id_tbl => p_change_id_tbl);
1887
1888 IF d_stmt THEN
1889 PO_LOG.stmt(d_mod,d_position,'l_has_address_changed',l_has_address_changed);
1890 PO_LOG.stmt(d_mod,d_position,'l_has_clause_changed',l_has_clause_changed);
1891 PO_LOG.stmt(d_mod,d_position,'l_has_vendor_changed',l_has_vendor_changed);
1892 END IF;
1893
1894 IF l_has_address_changed = 'N' THEN
1895 IF l_has_clause_changed = 'N' THEN
1896 IF l_has_vendor_changed = 'N' THEN
1897
1898 d_position := 40;
1899 x_results.add_result(
1900 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1901 , p_multi_mod_request_id => p_request_id
1902 , p_result_type => 'ERROR'
1903 , p_validation_type => p_validation_type
1904 , p_message_application => 'PO'
1905 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NO_CHANGE_DONE
1906 );
1907 END IF;
1908 END IF;
1909 END IF;
1910
1911 d_position := 50;
1912
1913 IF l_results_count < x_results.result_type.COUNT THEN
1914 x_result_type := c_result_type_FAILURE;
1915 ELSE
1916 x_result_type := c_result_type_SUCCESS;
1917 END IF;
1918
1919 IF PO_LOG.d_proc THEN
1920 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1921 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
1922 END IF;
1923
1924 EXCEPTION
1925 WHEN OTHERS THEN
1926 IF PO_LOG.d_exc THEN
1927 PO_LOG.exc(d_mod,d_position,NULL);
1928 END IF;
1929 RAISE;
1930
1931 END atleast_one_change_done;
1932
1933 -------------------------------------------------------------------------------
1934 --Start of Comments
1935 --Name: new_vendor_site_provided
1936 --Pre-reqs: None.
1937 --Function:
1938 -- Checks whether the new vendor site has been provided for each of the existing old vendor sites
1939 --Parameters:
1940 --IN:
1941 --p_request_id
1942 -- Request id from po_multi_mod_requests
1943 --p_change_id_tbl
1944 -- Table type having all the multi_mod_change_ids for a particular request_id in po_multi_mod_changes
1945 --p_change_type_tbl
1946 -- Table type having all the change_types for a particular request_id in po_multi_mod_changes
1947 --p_org_id_tbl
1948 -- Table type having all the org_ids for a particular request_id in po_multi_mod_changes
1949 --p_old_vendor_site_id_tbl
1950 -- Table type having all the old_vendor_site_ids for a particular request_id in po_multi_mod_changes
1951 --p_new_vendor_site_id_tbl
1952 -- Table type having all the new_vendor_site_ids for a particular request_id in po_multi_mod_changes
1953 --p_validation_type
1954 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
1955 --OUT:
1956 --x_result_type
1957 -- Identifies if the validation was a success or failure
1958 --x_results
1959 -- Out variable that holds the validation results
1960 --End of Comments
1961 -------------------------------------------------------------------------------
1962 PROCEDURE new_vendor_site_provided
1963 ( p_request_id IN NUMBER
1964 ,p_change_id_tbl IN PO_TBL_NUMBER
1965 ,p_change_type_tbl IN PO_TBL_VARCHAR30
1966 ,p_org_id_tbl IN PO_TBL_NUMBER
1967 ,p_old_vendor_site_id_tbl IN PO_TBL_NUMBER
1968 ,p_new_vendor_site_id_tbl IN PO_TBL_NUMBER
1969 ,p_validation_type IN VARCHAR2
1970 ,x_result_type OUT NOCOPY VARCHAR2
1971 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
1972 )
1973 is
1974 l_results_count NUMBER;
1975 d_mod CONSTANT VARCHAR2(100) := D_new_vendor_site_provided;
1976 d_position NUMBER := 0;
1977 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
1978
1979 BEGIN
1980 IF PO_LOG.d_proc THEN
1981 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
1982 PO_LOG.proc_begin(d_mod,'p_change_type_tbl',p_change_type_tbl);
1983 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
1984 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1985 PO_LOG.proc_begin(d_mod,'p_new_vendor_site_id_tbl',p_new_vendor_site_id_tbl);
1986 PO_LOG.proc_begin(d_mod,'p_old_vendor_site_id_tbl',p_old_vendor_site_id_tbl);
1987 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
1988 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1989 END IF;
1990
1991 IF (x_results IS NULL) THEN
1992 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
1993 END IF;
1994
1995 d_position := 10;
1996
1997 l_results_count := x_results.result_type.COUNT;
1998 IF d_stmt THEN
1999 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
2000 END IF;
2001
2002 d_position := 20;
2003 FOR i IN 1..p_change_id_tbl.COUNT LOOP
2004 IF (p_change_type_tbl(i) = 'VENDOR_SITE_CONTACT'
2005 AND p_org_id_tbl(i) is not null
2006 AND p_old_vendor_site_id_tbl(i) IS NOT NULL
2007 AND p_new_vendor_site_id_tbl(i) IS NULL) THEN
2008
2009 d_position := 30;
2010 x_results.add_result(
2011 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
2012 , p_multi_mod_request_id => p_request_id
2013 , p_result_type => 'ERROR'
2014 , p_validation_type => p_validation_type
2015 , p_message_application => 'PO'
2016 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NO_VENDOR_SITE
2017 );
2018 END IF;
2019
2020 END LOOP; --i IN 1..p_change_id_tbl.COUNT
2021
2022 d_position := 40;
2023
2024 IF l_results_count < x_results.result_type.COUNT THEN
2025 x_result_type := c_result_type_FAILURE;
2026 ELSE
2027 x_result_type := c_result_type_SUCCESS;
2028 END IF;
2029
2030 IF PO_LOG.d_proc THEN
2031 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2032 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
2033 END IF;
2034
2035 EXCEPTION
2036 WHEN OTHERS THEN
2037 IF PO_LOG.d_exc THEN
2038 PO_LOG.exc(d_mod,d_position,NULL);
2039 END IF;
2040 RAISE;
2041
2042 END new_vendor_site_provided;
2043
2044
2045 -------------------------------------------------------------------------------
2046 --Start of Comments
2047 --Name: new_vendor_site_CCR_valid
2048 --Pre-reqs: None.
2049 --Function:
2050 -- Checks whether the new vendor site has a active CCR registration
2051 --Parameters:
2052 --IN:
2053 --p_request_id
2054 -- Request id from po_multi_mod_requests
2055 --p_change_id_tbl
2056 -- Table type having all the multi_mod_change_ids for a particular request_id in po_multi_mod_changes
2057 --p_change_type_tbl
2058 -- Table type having all the change_types for a particular request_id in po_multi_mod_changes
2059 --p_old_vendor_site_id_tbl
2060 -- Table type having all the old_vendor_site_ids for a particular request_id in po_multi_mod_changes
2061 --p_new_vendor_site_id_tbl
2062 -- Table type having all the new_vendor_site_ids for a particular request_id in po_multi_mod_changes
2063 --p_validation_type
2064 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
2065 --OUT:
2066 --x_result_type
2067 -- Identifies if the validation was a success or failure
2068 --x_results
2069 -- Out variable that holds the validation results
2070 --End of Comments
2071 -------------------------------------------------------------------------------
2072 PROCEDURE new_vendor_site_CCR_valid
2073 ( p_request_id IN NUMBER
2074 ,p_change_id_tbl IN PO_TBL_NUMBER
2075 ,p_change_type_tbl IN PO_TBL_VARCHAR30
2076 ,p_old_vendor_site_id_tbl IN PO_TBL_NUMBER
2077 ,p_new_vendor_site_id_tbl IN PO_TBL_NUMBER
2078 ,p_validation_type IN VARCHAR2
2079 ,x_result_type OUT NOCOPY VARCHAR2
2080 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
2081 )
2082 is
2083 l_results_count NUMBER;
2084 d_mod CONSTANT VARCHAR2(100) := D_new_vendor_site_CCR_valid;
2085 d_position NUMBER := 0;
2086 l_old_vendor_id NUMBER;
2087 l_new_vendor_id NUMBER;
2088 l_old_valid_registration BOOLEAN := FALSE;
2089 l_new_valid_registration BOOLEAN := FALSE;
2090 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
2091
2092 BEGIN
2093 IF PO_LOG.d_proc THEN
2094 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2095 PO_LOG.proc_begin(d_mod,'p_change_type_tbl',p_change_type_tbl);
2096 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
2097 PO_LOG.proc_begin(d_mod,'p_new_vendor_site_id_tbl',p_new_vendor_site_id_tbl);
2098 PO_LOG.proc_begin(d_mod,'p_old_vendor_site_id_tbl',p_old_vendor_site_id_tbl);
2099 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
2100 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
2101 END IF;
2102
2103 IF (x_results IS NULL) THEN
2104 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
2105 END IF;
2106
2107 d_position := 10;
2108
2109 l_results_count := x_results.result_type.COUNT;
2110 IF d_stmt THEN
2111 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
2112 END IF;
2113
2114 d_position := 20;
2115
2116 BEGIN
2117 SELECT old_vendor_id, new_vendor_id
2118 INTO l_old_vendor_id, l_new_vendor_id
2119 FROM po_multi_mod_changes
2120 WHERE multi_mod_request_id = p_request_id
2121 AND change_type = 'VENDOR';
2122 EXCEPTION
2123 WHEN no_data_found THEN
2124 l_new_vendor_id := null;
2125 l_old_vendor_id := null;
2126 END;
2127
2128 IF d_stmt THEN
2129 PO_LOG.stmt(d_mod,d_position,'l_old_vendor_id',l_old_vendor_id);
2130 PO_LOG.stmt(d_mod,d_position,'l_new_vendor_id',l_new_vendor_id);
2131 END IF;
2132
2133 d_position := 30;
2134 FOR i IN 1..p_change_id_tbl.COUNT LOOP
2135 IF (p_change_type_tbl(i) = 'VENDOR_SITE_CONTACT'
2136 AND p_new_vendor_site_id_tbl(i) IS NOT NULL) THEN
2137
2138 d_position := 40;
2139 --check if old vendor site has a active CCR registration.
2140 --If the old vendor site is CCR active then check the CCR registration for new vendor,
2141 --if not then the check for new vendor site can also be skipped
2142 l_old_valid_registration := PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis(p_vendor_id => l_old_vendor_id,
2143 p_vendor_site_id => p_old_vendor_site_id_tbl(i));
2144
2145 IF d_stmt THEN
2146 PO_LOG.stmt(d_mod,d_position,'l_old_valid_registration',l_old_valid_registration);
2147 END IF;
2148
2149 IF l_old_valid_registration THEN --old vendor site has a active CCR registration
2150
2151 d_position := 50;
2152 l_new_valid_registration := PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis(p_vendor_id => l_new_vendor_id,
2153 p_vendor_site_id => p_new_vendor_site_id_tbl(i));
2154
2155 IF d_stmt THEN
2156 PO_LOG.stmt(d_mod,d_position,'l_new_valid_registration',l_new_valid_registration);
2157 END IF;
2158
2159 IF NOT l_new_valid_registration THEN
2160
2161 d_position := 60;
2162 x_results.add_result(
2163 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
2164 , p_multi_mod_request_id => p_request_id
2165 , p_result_type => 'ERROR'
2166 , p_validation_type => p_validation_type
2167 , p_message_application => 'PO'
2168 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NO_VENDOR_CCR
2169 , p_token1_name => 'NEW_SUPPLIER_SITE'
2170 , p_token1_value => p_new_vendor_site_id_tbl(i)
2171 );
2172
2173 END IF; --IF NOT l_new_valid_registration
2174
2175 END IF; --IF l_old_valid_registration
2176
2177 END IF; --IF (p_change_type_tbl(i) = 'VENDOR_SITE_CONTACT'
2178
2179 END LOOP; --i IN 1..p_change_id_tbl.COUNT
2180
2181 d_position := 70;
2182
2183 IF l_results_count < x_results.result_type.COUNT THEN
2184 x_result_type := c_result_type_FAILURE;
2185 ELSE
2186 x_result_type := c_result_type_SUCCESS;
2187 END IF;
2188
2189 IF PO_LOG.d_proc THEN
2190 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2191 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
2192 END IF;
2193
2194 EXCEPTION
2195 WHEN OTHERS THEN
2196 IF PO_LOG.d_exc THEN
2197 PO_LOG.exc(d_mod,d_position,NULL);
2198 END IF;
2199 RAISE;
2200
2201 END new_vendor_site_CCR_valid;
2202
2203 -------------------------------------------------------------------------------
2204 --Start of Comments
2205 --Name: uda_addresses_valid
2206 --Pre-reqs: None.
2207 --Function:
2208 -- Runs Pre-Submit Validations on the Multi-Mod UDA
2209 --Parameters:
2210 --IN:
2211 --p_multi_mod_request_id
2212 --End of Comments
2213 -------------------------------------------------------------------------------
2214 PROCEDURE uda_addresses_valid( p_request_id IN NUMBER
2215 ,x_result_type OUT NOCOPY VARCHAR2
2216 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
2217 )
2218 IS
2219 d_mod CONSTANT VARCHAR2(100) := D_uda_addresses_valid;
2220 d_position NUMBER := 0;
2221 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
2222 l_results_count NUMBER;
2223
2224 l_uda_template_id NUMBER;
2225 l_uda_key_po_header_id NUMBER;
2226 l_uda_key_draft_id NUMBER;
2227
2228 l_address_lookup_type CONSTANT VARCHAR2(100) := 'PO_MOD_UDA_ADDRESS_TYPES';
2229 l_return_status VARCHAR2(1);
2230 l_errorcode VARCHAR2(50);
2231 l_msg_count NUMBER;
2232 l_msg_data VARCHAR2(4000);
2233 l_msg_text VARCHAR2(2000);
2234 l_msg_name VARCHAR2(30) := NULL;
2235 l_application VARCHAR2(30) := NULL;
2236 l_application_length NUMBER;
2237 l_msg_name_length NUMBER;
2238 l_msg_text_length NUMBER;
2239 l_start_pos NUMBER;
2240 l_end_pos NUMBER;
2241 l_token_name VARCHAR2(30) := NULL;
2242 l_token_value VARCHAR2(4000) := NULL;
2243
2244 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
2245 ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
2246 g_xsc_return_status VARCHAR2(3) := FND_API.G_RET_STS_SUCCESS;
2247
2248 BEGIN
2249 IF PO_LOG.d_proc THEN
2250 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2251 END IF;
2252
2253 IF (x_results IS NULL) THEN
2254 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
2255 END IF;
2256
2257 d_position := 10;
2258
2259 l_results_count := x_results.result_type.COUNT;
2260 IF d_stmt THEN
2261 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
2262 END IF;
2263
2264 PO_FED_FIELD_FUNCTIONS.g_is_multi_mod_context := 'Y';-- Set Multi-Mod Context
2265
2266 d_position := 10;
2267 SELECT uda_template_id,
2268 uda_key_po_header_id,
2269 uda_key_draft_id
2270 INTO l_uda_template_id,
2271 l_uda_key_po_header_id,
2272 l_uda_key_draft_id
2273 FROM po_multi_mod_requests
2274 WHERE multi_mod_request_id = p_request_id;
2275
2276 -- Prepare l_external_attr_value_pairs
2277 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
2278 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
2279 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
2280 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
2281
2282 d_position := 20;
2283 IF d_stmt THEN
2284 PO_LOG.stmt(d_mod,d_position,'Calling PO_UDA_DEFAULTING_PKG.UDA_SUBMISSION_CHECK');
2285 END IF;
2286
2287 --initialize FND_MSG_PUB before adding any new messages to the stack
2288 FND_MSG_PUB.Initialize;
2289
2290 PO_UDA_DEFAULTING_PKG.UDA_SUBMISSION_CHECK(
2291 p_pk1_value => l_uda_key_po_header_id,
2292 p_pk2_value => l_uda_key_draft_id,
2293 p_template_id => l_uda_template_id,
2294 p_address_lookup_type => l_address_lookup_type,
2295 x_external_attr_value_pairs => l_external_attr_value_pairs,
2296 x_return_status => l_return_status,
2297 x_errorcode => l_errorcode,
2298 x_msg_count => l_msg_count,
2299 x_msg_data => l_msg_data
2300 );
2301
2302 d_position := 30;
2303 IF d_stmt THEN
2304 PO_LOG.stmt(d_mod,d_position,'After calling PO_UDA_DEFAULTING_PKG.UDA_SUBMISSION_CHECK Return Status:' || l_return_status);
2305 END IF;
2306
2307 PO_FED_FIELD_FUNCTIONS.g_is_multi_mod_context := 'N';-- reset Multi-Mod Context
2308 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2309 x_result_type := c_result_type_FAILURE;
2310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2311 END IF;
2312
2313 -- Fetch UDA Validation Return Status
2314 d_position := 40;
2315 PO_UDA_DEFAULTING_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', l_external_attr_value_pairs, ext_name_val_obj);
2316 g_xsc_return_status := ext_name_val_obj.VALUE;
2317
2318
2319 IF d_stmt THEN
2320 PO_LOG.stmt(d_mod,d_position,'Submission check has status ' || g_xsc_return_status);
2321 END IF;
2322
2323 d_position := 50;
2324 IF g_xsc_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2325
2326 IF d_stmt THEN
2327 PO_LOG.stmt(d_mod,d_position,'Submission check has failed for UDA');
2328 END IF;
2329
2330 l_msg_count := FND_MSG_PUB.Count_Msg;
2331
2332 IF d_stmt THEN
2333 PO_LOG.stmt(d_mod,d_position,'l_msg_count',l_msg_count);
2334 END IF;
2335
2336 d_position := 60;
2337 IF l_msg_count > 0 THEN
2338 FOR msg_counter IN 1 ..l_msg_count
2339 LOOP
2340
2341 d_position := 70;
2342 l_msg_text := FND_MSG_PUB.get (msg_counter, FND_API.g_true);
2343
2344 IF d_stmt THEN
2345 PO_LOG.stmt(d_mod,d_position,'l_msg_text',l_msg_text);
2346 END IF;
2347
2348 fnd_message.parse_encoded
2349 (encoded_message => l_msg_text
2350 ,app_short_name => l_application
2351 ,message_name => l_msg_name);
2352 --after we get the message application and message name, we can eliminate them from the message and get the token name and value
2353 l_msg_text_length := length(l_msg_text);
2354 l_application_length := length(l_application);
2355 l_msg_name_length := length(l_msg_name);
2356
2357 IF d_stmt THEN
2358 PO_LOG.stmt(d_mod,d_position,'l_msg_name',l_msg_name);
2359 PO_LOG.stmt(d_mod,d_position,'l_msg_name_length',l_msg_name_length);
2360 PO_LOG.stmt(d_mod,d_position,'l_application',l_application);
2361 PO_LOG.stmt(d_mod,d_position,'l_application_length',l_application_length);
2362 PO_LOG.stmt(d_mod,d_position,'l_msg_text_length',l_msg_text_length);
2363 END IF;
2364
2365 d_position := 80;
2366 IF l_msg_text_length > (l_application_length + l_msg_name_length + 2) THEN
2367
2368 l_start_pos := l_application_length + l_msg_name_length + 5;
2369 l_end_pos := instr(l_msg_text,chr(0),l_start_pos);
2370
2371 IF d_stmt THEN
2372 PO_LOG.stmt(d_mod,d_position,'l_start_pos',l_start_pos);
2373 PO_LOG.stmt(d_mod,d_position,'l_end_pos',l_end_pos);
2374 END IF;
2375
2376 d_position := 90;
2377 l_token_name := TRIM(substr(l_msg_text,l_start_pos,(l_end_pos-l_start_pos)));
2378
2379 l_token_value := TRIM(substr(l_msg_text,l_end_pos));
2380
2381 IF d_stmt THEN
2382 PO_LOG.stmt(d_mod,d_position,'l_token_name',l_token_name);
2383 PO_LOG.stmt(d_mod,d_position,'l_token_value',l_token_value);
2384 END IF;
2385 END IF;
2386
2387 d_position := 100;
2388 x_results.add_result(
2389 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
2390 , p_multi_mod_request_id => p_request_id
2391 , p_result_type => 'ERROR'
2392 , p_validation_type => 'PRE-SUBMIT'
2393 , p_message_application => l_application
2394 , p_message_name => l_msg_name
2395 , p_token1_name => l_token_name
2396 , p_token1_value => l_token_value
2397 );
2398 END LOOP;
2399
2400 END IF; --l_msg_count > 0 THEN
2401
2402 END IF; --g_xsc_return_status <> FND_API.G_RET_STS_SUCCESS
2403
2404 d_position := 110;
2405
2406 IF l_results_count < x_results.result_type.COUNT THEN
2407 x_result_type := c_result_type_FAILURE;
2408 ELSE
2409 x_result_type := c_result_type_SUCCESS;
2410 END IF;
2411
2412 IF PO_LOG.d_proc THEN
2413 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2414 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
2415 END IF;
2416
2417 EXCEPTION
2418 WHEN OTHERS THEN
2419 IF PO_LOG.d_exc THEN
2420 PO_FED_FIELD_FUNCTIONS.g_is_multi_mod_context := 'N';-- reset Multi-Mod Context
2421 PO_LOG.exc(d_mod,d_position,NULL);
2422 END IF;
2423 RAISE;
2424
2425 END uda_addresses_valid;
2426
2427 -------------------------------------------------------------------------------
2428 --Start of Comments
2429 --Name: no_of_copies_ge_zero
2430 --Pre-reqs: None.
2431 --Function:
2432 -- Checks if clm_noofcopies is greater than 0 when bi-lateral signature is required
2433 --Parameters:
2434 --IN:
2435 --p_request_id
2436 -- Request id from po_multi_mod_request
2437 --p_clm_noofcopies
2438 --p_validation_type
2439 -- Parameter to identify if the validations are 'pre-submit' or 'concurrent'
2440 --p_ord_id_tbl
2441 -- Added this parameter as part of CLM Controls Project
2442 --OUT:
2443 --x_result_type
2444 -- Identifies if the validation was a success or failure
2445 --x_results
2446 -- Out variable that holds the validation results
2447 --End of Comments
2448 -------------------------------------------------------------------------------
2449
2450 PROCEDURE contract_officer_provided
2451 ( p_request_id IN NUMBER
2452 ,p_clm_contract_officer IN NUMBER
2453 ,p_validation_type IN VARCHAR2
2454 ,p_org_id_tbl IN PO_TBL_NUMBER
2455 ,x_result_type OUT NOCOPY VARCHAR2
2456 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
2457 )
2458 is
2459 l_results_count NUMBER;
2460 l_ko_sign_required VARCHAR2(1);
2461 d_mod CONSTANT VARCHAR2(100) := D_contract_officer_provided;
2462 d_position NUMBER := 0;
2463 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
2464
2465 BEGIN
2466 IF PO_LOG.d_proc THEN
2467 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2468 PO_LOG.proc_begin(d_mod,'p_clm_contract_officer',p_clm_contract_officer);
2469 PO_LOG.proc_begin(d_mod,'p_validation_type',p_validation_type);
2470 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
2471 END IF;
2472
2473 IF (x_results IS NULL) THEN
2474 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
2475 END IF;
2476
2477 l_results_count := x_results.result_type.COUNT;
2478 IF d_stmt THEN
2479 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
2480 END IF;
2481
2482 d_position := 10;
2483 -- CLM Controls Project Changes
2484 FOR i IN 1..p_org_id_tbl.COUNT LOOP
2485 l_ko_sign_required := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id_tbl(i),
2486 p_option_column => PO_CORE_S.g_KO_SIGNATURE_REQD_COL);
2487 --NVL(FND_PROFILE.VALUE('PO_CLM_KO_SIGNATURE_REQD'),'N');
2488
2489 IF (NVL(l_ko_sign_required,'N') = 'Y') AND (p_clm_contract_officer IS NULL) THEN
2490
2491
2492 x_results.add_result(
2493 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
2494 , p_multi_mod_request_id => p_request_id
2495 , p_result_type => 'ERROR'
2496 , p_validation_type => p_validation_type
2497 , p_message_application => 'PO'
2498 , p_message_name => 'PO_CO_IS_NULL'
2499 , p_token1_name => 'ORG_ID'
2500 , p_token1_value => p_org_id_tbl(i)
2501 );
2502 END IF;
2503 END LOOP;
2504
2505 d_position := 20;
2506 IF l_results_count < x_results.result_type.COUNT THEN
2507 x_result_type := c_result_type_FAILURE;
2508 ELSE
2509 x_result_type := c_result_type_SUCCESS;
2510 END IF;
2511
2512 IF PO_LOG.d_proc THEN
2513 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2514 --PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
2515 END IF;
2516
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 IF PO_LOG.d_exc THEN
2520 PO_LOG.exc(d_mod,d_position,NULL);
2521 END IF;
2522 RAISE;
2523
2524 END contract_officer_provided;
2525
2526 -------------------------------------------------------------------------------
2527 --Start of Comments
2528 --Name: has_address_changed
2529 --Pre-reqs: None.
2530 --Function:
2531 -- Checks if there has been any address change made
2532 --Parameters:
2533 --IN:
2534 --p_request_id
2535 -- Request id from po_multi_mod_requests
2536 --End of Comments
2537 -------------------------------------------------------------------------------
2538 FUNCTION has_address_changed(p_request_id IN NUMBER)
2539
2540 RETURN VARCHAR2
2541 IS
2542 l_has_address_changed VARCHAR2(1) := 'N';
2543 l_sum_check NUMBER;
2544 d_mod CONSTANT VARCHAR2(100) := D_has_address_changed;
2545 d_position NUMBER := 0;
2546 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
2547 l_addr_attr_group_id NUMBER;
2548
2549 BEGIN
2550 IF PO_LOG.d_proc THEN
2551 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2552 END IF;
2553 d_position := 10;
2554 BEGIN
2555 SELECT attr_group_id
2556 INTO l_addr_attr_group_id
2557 FROM ego_attr_groups_v
2558 WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS'
2559 AND attr_group_name = 'addresses';
2560 EXCEPTION
2561 WHEN no_data_found THEN
2562 l_addr_attr_group_id := null;
2563 END;
2564
2565
2566 BEGIN
2567 SELECT NVL(SUM(NVL(n_ext_attr1,0) + NVL(n_ext_attr2,0) + NVL(n_ext_attr3,0) +
2568 NVL(n_ext_attr4,0) + NVL(n_ext_attr5,0) + NVL(n_ext_attr6,0) +
2569 NVL(n_ext_attr7,0) + NVL(n_ext_attr8,0) + NVL(n_ext_attr9,0) +
2570 NVL(n_ext_attr10,0) + NVL(n_ext_attr11,0) + NVL(n_ext_attr12,0) +
2571 NVL(n_ext_attr13,0) + NVL(n_ext_attr14,0) + NVL(n_ext_attr15,0) +
2572 NVL(n_ext_attr16,0) + NVL(n_ext_attr17,0) + NVL(n_ext_attr18,0) +
2573 NVL(n_ext_attr19,0) + NVL(n_ext_attr20,0)),0) attr_sum
2574 INTO l_sum_check
2575 FROM po_headers_all_ext_b ext,
2576 po_multi_mod_requests multimod
2577 WHERE ext.po_header_id = multimod.uda_key_po_header_id
2578 AND ext.draft_id = multimod.uda_key_draft_id
2579 AND multimod.multi_mod_request_id = p_request_id
2580 AND ext.attr_group_id = l_addr_attr_group_id
2581 AND ext.c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE');
2582 EXCEPTION
2583 WHEN no_data_found THEN
2584 l_sum_check := 0;
2585 END;
2586
2587 IF d_stmt THEN
2588 PO_LOG.stmt(d_mod,d_position,'l_sum_check',l_sum_check);
2589 END IF;
2590
2591 d_position := 20;
2592 IF l_sum_check >0 THEN
2593 l_has_address_changed := 'Y';
2594 END IF;
2595
2596 IF d_stmt THEN
2597 PO_LOG.stmt(d_mod,d_position,'l_has_address_changed',l_has_address_changed);
2598 END IF;
2599
2600 RETURN l_has_address_changed;
2601
2602 EXCEPTION
2603 WHEN OTHERS THEN
2604 IF PO_LOG.d_exc THEN
2605 PO_LOG.exc(d_mod,d_position,NULL);
2606 END IF;
2607 RAISE;
2608
2609 END has_address_changed;
2610
2611 -------------------------------------------------------------------------------
2612 --Start of Comments
2613 --Name: has_clause_changed
2614 --Pre-reqs: None.
2615 --Function:
2616 -- Checks if there has been any clause change made
2617 --Parameters:
2618 --IN:
2619 --p_request_id
2620 -- Request id from po_multi_mod_requests
2621 --End of Comments
2622 -------------------------------------------------------------------------------
2623 FUNCTION has_clause_changed(p_request_id IN NUMBER)
2624 RETURN VARCHAR2
2625 IS
2626 l_has_clause_changed VARCHAR2(1) := 'N';
2627 d_mod CONSTANT VARCHAR2(100) := D_has_clause_changed;
2628 d_position NUMBER := 0;
2629 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
2630 l_count NUMBER;
2631
2632 BEGIN
2633
2634 IF PO_LOG.d_proc THEN
2635 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2636 END IF;
2637 IF d_stmt THEN
2638 PO_LOG.stmt(d_mod,d_position,'l_has_clause_changed',l_has_clause_changed);
2639 END IF;
2640
2641 BEGIN
2642
2643 SELECT Count(*)
2644 INTO l_count
2645 FROM po_multi_mod_clause_changes
2646 WHERE multi_mod_request_id = p_request_id;
2647
2648 EXCEPTION
2649 WHEN no_data_found THEN
2650 l_has_clause_changed := 'N';
2651 END;
2652
2653 IF(l_count > 0) THEN
2654 l_has_clause_changed := 'Y';
2655 END IF;
2656
2657 RETURN l_has_clause_changed;
2658
2659 EXCEPTION
2660 WHEN OTHERS THEN
2661 IF PO_LOG.d_exc THEN
2662 PO_LOG.exc(d_mod,d_position,NULL);
2663 END IF;
2664 RAISE;
2665
2666 END has_clause_changed;
2667
2668
2669 -------------------------------------------------------------------------------
2670 --Start of Comments
2671 --Name: has_vendor_changed
2672 --Pre-reqs: None.
2673 --Function:
2674 -- Checks if there has been any vendor change made
2675 --Parameters:
2676 --IN:
2677 --p_request_id
2678 -- Request id from po_multi_mod_requests
2679 --p_change_id_tbl
2680 -- Table type having all the multi_mod_change_ids for a particular request_id in po_multi_mod_changes
2681 --End of Comments
2682 -------------------------------------------------------------------------------
2683 FUNCTION has_vendor_changed(p_request_id IN NUMBER
2684 ,p_change_id_tbl IN PO_TBL_NUMBER)
2685 RETURN VARCHAR2
2686 IS
2687 l_has_vendor_changed VARCHAR2(1) := 'N';
2688 d_mod CONSTANT VARCHAR2(100) := D_has_vendor_changed;
2689 d_position NUMBER := 0;
2690 l_multi_mod_request_type PO_MULTI_MOD_REQUESTS.multi_mod_request_type%TYPE;
2691 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
2692
2693 BEGIN
2694
2695 IF PO_LOG.d_proc THEN
2696 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2697 PO_LOG.proc_begin(d_mod,'p_change_id_tbl',p_change_id_tbl);
2698 END IF;
2699
2700 d_position := 10;
2701 BEGIN
2702 SELECT multi_mod_request_type
2703 INTO l_multi_mod_request_type
2704 FROM po_multi_mod_requests
2705 WHERE multi_mod_request_id = p_request_id;
2706 EXCEPTION
2707 WHEN no_data_found THEN
2708 l_multi_mod_request_type := null;
2709 END;
2710
2711 IF d_stmt THEN
2712 PO_LOG.stmt(d_mod,d_position,'l_multi_mod_request_type',l_multi_mod_request_type);
2713 END IF;
2714
2715 d_position := 20;
2716 IF l_multi_mod_request_type IN ('VENDOR_CHANGE', 'VENDOR_NAME_CHANGE')
2717 AND p_change_id_tbl.COUNT >0 THEN
2718
2719 l_has_vendor_changed := 'Y';
2720 END IF;
2721
2722 IF d_stmt THEN
2723 PO_LOG.stmt(d_mod,d_position,'l_has_vendor_changed',l_has_vendor_changed);
2724 END IF;
2725
2726 RETURN l_has_vendor_changed;
2727
2728 EXCEPTION
2729 WHEN OTHERS THEN
2730 IF PO_LOG.d_exc THEN
2731 PO_LOG.exc(d_mod,d_position,NULL);
2732 END IF;
2733 RAISE;
2734
2735 END has_vendor_changed;
2736
2737
2738 -------------------------------------------------------------------------------
2739 --Start of Comments
2740 --Name: header_lock_compatible
2741 --Pre-reqs: None.
2742 --Function:
2743 -- Checks if compatible locks at header level are available to make the requested
2744 -- changes.
2745 --Parameters:
2746 --IN:
2747 --p_request_id
2748 -- Request id from po_multi_mod_requests
2749 --x_result_type
2750 -- Result type of the validation.
2751 --x_results
2752 -- Validation results. Errors and warnings.
2753 --End of Comments
2754 -------------------------------------------------------------------------------
2755 PROCEDURE header_lock_compatible(p_request_id IN NUMBER
2756 , x_result_type OUT NOCOPY VARCHAR2
2757 , x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE)
2758 IS
2759 l_header_lock VARCHAR2(1) := 'N';
2760 l_document_id_tbl PO_TBL_NUMBER;
2761 l_results_count NUMBER;
2762 d_mod CONSTANT VARCHAR2(100) := D_header_lock_compatible;
2763 d_position NUMBER := 0;
2764 l_document_num_tbl PO_TBL_VARCHAR100;
2765 l_modification_num_tbl PO_TBL_VARCHAR100;
2766 BEGIN
2767 IF PO_LOG.d_proc THEN
2768 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2769 END IF;
2770
2771 l_results_count := x_results.result_type.COUNT;
2772
2773 SELECT pmmd.document_id, pha.clm_document_number, pd.modification_number
2774 BULK COLLECT INTO l_document_id_tbl, l_document_num_tbl, l_modification_num_tbl
2775 FROM po_entity_locks pel,
2776 po_multi_mod_docs pmmd,
2777 po_headers_all pha,
2778 po_doc_style_headers pdsh,
2779 po_drafts pd
2780 WHERE pmmd.multi_mod_request_id = p_request_id
2781 AND pha.po_header_id = pmmd.document_id
2782 AND pmmd.document_id = To_Number(pel.entity_pk1)
2783 AND pd.draft_id = pel.lock_by_draft_id
2784 AND pel.entity_name = 'PO_HEADER'
2785 AND pel.lock_type IN ('F', 'P')
2786 AND pdsh.style_id = pha.style_id
2787 AND Nvl(pdsh.conc_mods_enabled_flag, 'N') = 'Y';
2788
2789 d_position := 10;
2790 IF PO_LOG.d_proc THEN
2791 PO_LOG.stmt(d_mod, d_position, 'l_document_num_tbl: ', l_document_num_tbl);
2792 END IF;
2793
2794 FOR i IN 1..l_document_id_tbl.Count() LOOP
2795
2796 x_results.add_result(
2797 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
2798 , p_multi_mod_request_id => p_request_id
2799 , p_validation_type => 'ERROR'
2800 , p_document_number => l_document_num_tbl(i)
2801 , p_message_application => 'PO'
2802 , p_message_name => 'MULTI_MOD_HEADER_LOCKED'
2803 , p_token1_name => 'DOCUMENT_NUMBER'
2804 , p_token1_value => l_document_num_tbl(i)
2805 , p_token2_name => 'MODIFICATION_NUMBER'
2806 , p_token2_value => l_modification_num_tbl(i));
2807 END LOOP;
2808
2809 d_position := 20;
2810
2811 IF l_results_count < x_results.result_type.COUNT THEN
2812 x_result_type := c_result_type_FAILURE;
2813 ELSE
2814 x_result_type := c_result_type_SUCCESS;
2815 END IF;
2816
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 IF PO_LOG.d_exc THEN
2820 PO_LOG.exc(d_mod,d_position,NULL);
2821 END IF;
2822 RAISE;
2823 END header_lock_compatible;
2824
2825
2826 -------------------------------------------------------------------------------
2827 --Start of Comments
2828 --Name: address_lock_compatible
2829 --Pre-reqs: None.
2830 --Function:
2831 -- Checks if compatible lock at any address is available to make the requested
2832 -- changes at that particular address.
2833 --Parameters:
2834 --IN:
2835 --p_request_id
2836 -- Request id from po_multi_mod_requests
2837 --x_result_type
2838 -- Result type of the validation.
2839 --x_results
2840 -- Validation results. Errors and warnings.
2841 --End of Comments
2842 -------------------------------------------------------------------------------
2843 PROCEDURE address_lock_compatible(p_request_id IN NUMBER
2844 , x_result_type OUT NOCOPY VARCHAR2
2845 , x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE)
2846 IS
2847 d_position NUMBER := 0;
2848 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
2849 d_mod CONSTANT VARCHAR2(2000) := D_address_lock_compatible;
2850 l_addr_attr_group_id NUMBER;
2851 l_address_code_tbl PO_TBL_VARCHAR2000;
2852 l_sum_check_tbl PO_TBL_NUMBER;
2853 l_document_id_tbl PO_TBL_NUMBER;
2854 l_modification_number_tbl PO_TBL_VARCHAR100;
2855 l_document_number_tbl PO_TBL_VARCHAR100;
2856 l_results_count NUMBER;
2857 BEGIN
2858
2859 IF PO_LOG.d_proc THEN
2860 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
2861 END IF;
2862
2863 l_results_count := x_results.result_type.COUNT;
2864
2865 BEGIN
2866 SELECT attr_group_id
2867 INTO l_addr_attr_group_id
2868 FROM ego_attr_groups_v
2869 WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS'
2870 AND attr_group_name = 'addresses';
2871 EXCEPTION
2872 WHEN no_data_found THEN
2873 l_addr_attr_group_id := null;
2874 END;
2875
2876
2877 SELECT
2878 pha.clm_document_number,
2879 (SELECT DISPLAYED_FIELD FROM po_lookup_codes
2880 WHERE lookup_type LIKE 'PO_UDA_ADDRESS_TYPES'
2881 AND LOOKUP_CODE = ext.c_ext_attr39) DISPLAYED_FIELD,
2882 pd.modification_number,
2883 NVL(SUM(NVL(n_ext_attr1,0) + NVL(n_ext_attr2,0) + NVL(n_ext_attr3,0) +
2884 NVL(n_ext_attr4,0) + NVL(n_ext_attr5,0) + NVL(n_ext_attr6,0) +
2885 NVL(n_ext_attr7,0) + NVL(n_ext_attr8,0) + NVL(n_ext_attr9,0) +
2886 NVL(n_ext_attr10,0) + NVL(n_ext_attr11,0) + NVL(n_ext_attr12,0) +
2887 NVL(n_ext_attr13,0) + NVL(n_ext_attr14,0) + NVL(n_ext_attr15,0) +
2888 NVL(n_ext_attr16,0) + NVL(n_ext_attr17,0) + NVL(n_ext_attr18,0) +
2889 NVL(n_ext_attr19,0) + NVL(n_ext_attr20,0)),0) attr_sum
2890 BULK COLLECT INTO l_document_number_tbl,
2891 l_address_code_tbl, l_modification_number_tbl,
2892 l_sum_check_tbl
2893 FROM po_headers_all_ext_b ext,
2894 po_multi_mod_requests multimod,
2895 po_entity_locks pel,
2896 po_multi_mod_docs pmmd,
2897 po_headers_all pha,
2898 po_doc_style_headers pdsh,
2899 po_drafts pd
2900 WHERE ext.po_header_id = multimod.uda_key_po_header_id
2901 AND ext.draft_id = multimod.uda_key_draft_id
2902 AND ext.attr_group_id = l_addr_attr_group_id
2903 AND multimod.multi_mod_request_id = p_request_id
2904 AND pmmd.multi_mod_request_id = multimod.multi_mod_request_id
2905 AND pha.po_header_id = pmmd.document_id
2906 AND pd.draft_id = pel.lock_by_draft_id
2907 AND ext.c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE')
2908 AND pel.entity_name = 'PO_UDA_ADDRESS'
2909 AND ext.c_ext_attr39 = pel.entity_pk2
2910 AND To_Number(pel.entity_pk1) = pmmd.document_id
2911 AND pdsh.style_id = pha.style_id
2912 AND Nvl(pdsh.conc_mods_enabled_flag, 'N') = 'Y'
2913 GROUP BY ext.c_ext_attr39, pha.clm_document_number, pd.modification_number;
2914
2915 d_position := 10;
2916
2917 IF PO_LOG.d_proc THEN
2918 PO_LOG.stmt(d_mod, d_position, 'l_document_number_tbl: ', l_document_number_tbl);
2919 END IF;
2920
2921 FOR i IN 1..l_sum_check_tbl.Count() LOOP
2922
2923 IF l_sum_check_tbl(i) > 0 THEN
2924
2925 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
2926 , p_multi_mod_request_id => p_request_id
2927 , p_validation_type => 'ERROR'
2928 , p_document_number => l_document_number_tbl(i)
2929 , p_message_application => 'PO'
2930 , p_message_name => 'MULTI_MOD_ADDRESS_LOCKED'
2931 , p_token1_name => 'ADDRESS_TYPE'
2932 , p_token1_value => l_address_code_tbl(i)
2933 , p_token2_name => 'DOCUMENT_NUMBER'
2934 , p_token2_value => l_document_number_tbl(i)
2935 , p_token3_name => 'MODIFICATION_NUMBER'
2936 , p_token3_value => l_modification_number_tbl(i));
2937 END IF;
2938 END LOOP;
2939
2940 d_position := 20;
2941
2942 IF l_results_count < x_results.result_type.COUNT THEN
2943 x_result_type := c_result_type_FAILURE;
2944 ELSE
2945 x_result_type := c_result_type_SUCCESS;
2946 END IF;
2947
2948 EXCEPTION
2949 WHEN OTHERS THEN
2950 IF PO_LOG.d_exc THEN
2951 PO_LOG.exc(d_mod,d_position,NULL);
2952 END IF;
2953 RAISE;
2954 END address_lock_compatible;
2955
2956
2957 -------------------------------------------------------------------------------
2958 --Start of Comments
2959 --Name: validate_open_mod
2960 --Pre-reqs: None.
2961 --Function:
2962 -- Checks if there already exists an open modification if conc modifications
2963 -- are not enabled for the document style.
2964 --Parameters:
2965 --IN:
2966 --p_request_id
2967 -- Request id from po_multi_mod_requests
2968 --x_result_type
2969 -- Result type of the validation.
2970 --x_results
2971 -- Validation results. Errors and warnings.
2972 --End of Comments
2973 -------------------------------------------------------------------------------
2974 PROCEDURE validate_open_mod(p_request_id IN NUMBER
2975 , x_result_type OUT NOCOPY VARCHAR2
2976 , x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE)
2977 IS
2978 d_mod CONSTANT VARCHAR2(2000) := D_validate_open_mod;
2979 l_document_num_tbl PO_TBL_VARCHAR100;
2980 l_conc_mod_flag_tbl PO_TBL_VARCHAR1;
2981 l_document_id_tbl PO_TBL_NUMBER;
2982 l_mod_exists VARCHAR2(1);
2983 d_position NUMBER := 0;
2984 l_results_count NUMBER;
2985 BEGIN
2986 IF PO_LOG.d_proc THEN
2987 PO_LOG.proc_begin(d_mod,'p_request_id ',p_request_id);
2988 END IF;
2989
2990 l_results_count := x_results.result_type.COUNT;
2991
2992 --collect all document_ids which are being updated
2993 SELECT pha.clm_document_number
2994 BULK COLLECT INTO l_document_num_tbl
2995 FROM po_multi_mod_docs pmmd,
2996 po_headers_all pha,
2997 po_doc_style_headers pdsh
2998 WHERE pmmd.multi_mod_request_id = p_request_id
2999 AND pmmd.document_id = pha.po_header_id
3000 AND pha.style_id = pdsh.style_id
3001 AND Nvl(pdsh.conc_mods_enabled_flag, 'N') = 'N'
3002 AND EXISTS (SELECT 'Open Mod Exists'
3003 FROM po_drafts dft
3004 WHERE dft.document_id = pmmd.document_id
3005 AND dft.status IN ('DRAFT', 'IN PROCESS', 'REJECTED', 'SUPPLIER SIGN', 'PRE-APPROVED'));
3006
3007 d_position := 10;
3008
3009 IF PO_LOG.d_proc THEN
3010 PO_LOG.stmt(d_mod, d_position, 'l_document_num_tbl: ', l_document_num_tbl);
3011 END IF;
3012
3013 FOR i IN 1..l_document_num_tbl.Count() LOOP
3014
3015 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3016 , p_multi_mod_request_id => p_request_id
3017 , p_validation_type => 'ERROR'
3018 , p_document_number => l_document_num_tbl(i)
3019 , p_message_application => 'PO'
3020 , p_message_name => 'CONC_MOD_NOT_ENABLED'
3021 , p_token1_name => 'DOCUMENT_NUMBER'
3022 , p_token1_value => l_document_num_tbl(i));
3023 END LOOP;
3024
3025 d_position := 20;
3026
3027 IF l_results_count < x_results.result_type.COUNT THEN
3028 x_result_type := c_result_type_FAILURE;
3029 ELSE
3030 x_result_type := c_result_type_SUCCESS;
3031 END IF;
3032
3033 EXCEPTION
3034 WHEN OTHERS THEN
3035 IF PO_LOG.d_exc THEN
3036 PO_LOG.exc(d_mod,d_position,NULL);
3037 END IF;
3038 RAISE;
3039 END validate_open_mod;
3040
3041 -----------------------------------------------------------------------
3042 --validation routines for Supplier change exceptions concurrent program
3043 -----------------------------------------------------------------------
3044 -------------------------------------------------------------------------------
3045 --Start of Comments
3046 --Name: generate_idv_exceptions
3047 --Pre-reqs: None.
3048 --Function:
3049 -- Checks if there are IDVs selected for supplier change and aslo checks if all the awards corresponding to the IDV are selected as well.
3050 --Parameters:
3051 --IN:
3052 --p_request_id
3053 -- Request id from po_multi_mod_requests
3054 --p_document_id_tbl
3055 -- Table type having all the document_ids for a particular request_id in po_multi_mod_changes
3056 --p_multi_mod_doc_id_tbl
3057 -- Table type having all the p_multi_mod_doc_ids for a particular request_id in po_multi_mod_changes
3058 --OUT:
3059 --x_result_type
3060 -- Identifies if the validation was a success or failure
3061 --x_results
3062 -- Out variable that holds the validation results
3063 --End of Comments
3064 -------------------------------------------------------------------------------
3065 PROCEDURE generate_idv_exceptions
3066 ( p_request_id IN NUMBER
3067 -- ,p_document_id_tbl IN PO_TBL_NUMBER
3068 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
3069 ,x_result_type OUT NOCOPY VARCHAR2
3070 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
3071 )
3072 is
3073 l_results_count NUMBER;
3074 d_mod CONSTANT VARCHAR2(100) := D_generate_idv_exceptions;
3075 d_position NUMBER := 0;
3076 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
3077 l_document_id_tbl PO_TBL_NUMBER;
3078 l_document_number_tbl PO_TBL_VARCHAR50;
3079 l_related_document_id_tbl PO_TBL_NUMBER;
3080 l_related_doc_number_tbl PO_TBL_VARCHAR50;
3081
3082
3083 BEGIN
3084 IF PO_LOG.d_proc THEN
3085 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
3086 --PO_LOG.proc_begin(d_mod,'p_document_id_tbl',p_document_id_tbl);
3087 PO_LOG.proc_begin(d_mod,'p_multi_mod_doc_id_tbl',p_multi_mod_doc_id_tbl);
3088 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3089 END IF;
3090
3091 IF (x_results IS NULL) THEN
3092 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
3093 END IF;
3094
3095 d_position := 10;
3096
3097 l_results_count := x_results.result_type.COUNT;
3098 IF d_stmt THEN
3099 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
3100 END IF;
3101
3102 d_position := 20;
3103
3104
3105 SELECT pmmd_a.document_id
3106 ,ph_a.clm_document_number
3107 ,ph_e.po_header_id
3108 ,ph_e.clm_document_number
3109 BULK COLLECT INTO l_document_id_tbl
3110 ,l_document_number_tbl
3111 ,l_related_document_id_tbl
3112 ,l_related_doc_number_tbl
3113 FROM po_multi_mod_docs pmmd_a,/*current set of documents being validated*/
3114 po_headers_all ph_a, /*current document joined to headers, filtered by BLANKET/CONTRACT*/
3115 po_headers_all ph_e /*list of execution documents tied to the agreement*/
3116 WHERE pmmd_a.document_id = ph_a.po_header_id
3117 AND pmmd_a.multi_mod_request_id = p_request_id
3118 AND ph_a.type_lookup_code IN ('BLANKET', 'CONTRACT')
3119 AND ph_e.clm_source_document_id = ph_a.po_header_id
3120 AND NVL(ph_e.closed_code,'OPEN') <> 'FINALLY CLOSED'
3121 AND NVL(ph_e.authorization_status,'INCOMPLETE') = 'APPROVED'
3122 AND ph_e.po_header_id NOT IN /*Subquery to retrieve all the documents in current batch*/
3123 (SELECT pmmd_e.document_id FROM po_multi_mod_docs pmmd_e WHERE pmmd_e.multi_mod_request_id = p_request_id );
3124
3125 FOR i IN 1..l_document_id_tbl.COUNT LOOP
3126 d_position := 30;
3127 x_results.add_result(
3128 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3129 , p_multi_mod_request_id => p_request_id
3130 , p_multi_mod_doc_id => p_multi_mod_doc_id_tbl(i)
3131 , p_result_type => 'ERROR'
3132 , p_exception_type => PO_MESSAGE_S.PO_MULTIMOD_AWARD_NOT_INC_SUM
3133 , p_validation_type => 'IDV'
3134 , p_document_id => l_document_id_tbl(i)
3135 , p_document_number => l_document_number_tbl(i)
3136 , p_related_document_id => l_related_document_id_tbl(i)
3137 , p_related_document_number => l_related_doc_number_tbl(i)
3138 , p_message_application => 'PO'
3139 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_AWARD_NOT_INC --PO_MULTIMOD_AWARD_NOT_INC_SUM
3140 );
3141 END LOOP;
3142
3143 d_position := 40;
3144
3145 IF l_results_count < x_results.result_type.COUNT THEN
3146 x_result_type := c_result_type_WARNING;
3147 ELSE
3148 x_result_type := c_result_type_SUCCESS;
3149 END IF;
3150
3151 IF PO_LOG.d_proc THEN
3152 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3153 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
3154 END IF;
3155
3156 EXCEPTION
3157 WHEN OTHERS THEN
3158 x_result_type := c_result_type_FAILURE;
3159 IF PO_LOG.d_exc THEN
3160 PO_LOG.exc(d_mod,d_position,NULL);
3161 END IF;
3162 RAISE;
3163
3164 END generate_idv_exceptions;
3165
3166 -------------------------------------------------------------------------------
3167 --Start of Comments
3168 --Name: generate_award_exceptions
3169 --Pre-reqs: None.
3170 --Function:
3171 -- Checks if there are IDVs selected for supplier change and aslo checks if all the awards corresponding to the IDV are selected as well.
3172 --Parameters:
3173 --IN:
3174 --p_request_id
3175 -- Request id from po_multi_mod_requests
3176 --p_document_id_tbl
3177 -- Table type having all the document_ids for a particular request_id in po_multi_mod_changes
3178 --p_multi_mod_doc_id_tbl
3179 -- Table type having all the p_multi_mod_doc_ids for a particular request_id in po_multi_mod_changes
3180 --OUT:
3181 --x_result_type
3182 -- Identifies if the validation was a success or failure
3183 --x_results
3184 -- Out variable that holds the validation results
3185 --End of Comments
3186 -------------------------------------------------------------------------------
3187 PROCEDURE generate_award_exceptions
3188 ( p_request_id IN NUMBER
3189 -- ,p_document_id_tbl IN PO_TBL_NUMBER
3190 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
3191 ,x_result_type OUT NOCOPY VARCHAR2
3192 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
3193 )
3194 is
3195 l_results_count NUMBER;
3196 d_mod CONSTANT VARCHAR2(100) := D_generate_award_exceptions;
3197 d_position NUMBER := 0;
3198 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
3199 /*l_clm_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
3200 l_clm_source_document_id PO_HEADERS_ALL.clm_source_document_id%TYPE;
3201 l_idv_exists VARCHAR2(1) := 'N';
3202 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3203 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;*/
3204 l_document_id_tbl PO_TBL_NUMBER;
3205 l_document_number_tbl PO_TBL_VARCHAR50;
3206 l_related_document_id_tbl PO_TBL_NUMBER;
3207 l_related_doc_number_tbl PO_TBL_VARCHAR50;
3208
3209 BEGIN
3210 IF PO_LOG.d_proc THEN
3211 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
3212 --PO_LOG.proc_begin(d_mod,'p_document_id_tbl',p_document_id_tbl);
3213 PO_LOG.proc_begin(d_mod,'p_multi_mod_doc_id_tbl',p_multi_mod_doc_id_tbl);
3214 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3215 END IF;
3216
3217 IF (x_results IS NULL) THEN
3218 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
3219 END IF;
3220
3221 d_position := 10;
3222
3223 l_results_count := x_results.result_type.COUNT;
3224 IF d_stmt THEN
3225 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
3226 END IF;
3227
3228 d_position := 20;
3229
3230
3231 SELECT ph_e.po_header_id
3232 ,ph_e.clm_document_number
3233 ,ph_a.po_header_id
3234 ,ph_a.clm_document_number
3235 BULK COLLECT INTO l_document_id_tbl
3236 ,l_document_number_tbl
3237 ,l_related_document_id_tbl
3238 ,l_related_doc_number_tbl
3239 FROM po_multi_mod_docs pmmd_e,
3240 po_headers_all ph_e,
3241 po_headers_all ph_a
3242 WHERE pmmd_e.multi_mod_request_id = p_request_id
3243 AND ph_e.po_header_id = pmmd_e.document_id
3244 AND ph_e.type_lookup_code = 'STANDARD'
3245 AND ph_e.clm_source_document_id = ph_a.po_header_id
3246 AND ph_e.clm_source_document_id IS NOT NULL
3247 AND ph_e.clm_source_document_id NOT IN (SELECT pmmd_a.document_id FROM po_multi_mod_docs pmmd_a WHERE pmmd_a.multi_mod_request_id = p_request_id);
3248
3249 FOR i IN 1..l_document_id_tbl.COUNT LOOP
3250 d_position := 30;
3251
3252 x_results.add_result(
3253 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3254 , p_multi_mod_request_id => p_request_id
3255 , p_multi_mod_doc_id => p_multi_mod_doc_id_tbl(i)
3256 , p_result_type => 'ERROR'
3257 , p_exception_type => PO_MESSAGE_S.PO_MULTIMOD_IDV_NOT_INC_SUM
3258 , p_validation_type => 'AWARD'
3259 , p_document_id => l_document_id_tbl(i)
3260 , p_document_number => l_document_number_tbl(i)
3261 , p_related_document_id => l_related_document_id_tbl(i)
3262 , p_related_document_number => l_related_doc_number_tbl(i)
3263 , p_message_application => 'PO'
3264 , p_message_name => PO_MESSAGE_S.PO_MULTIMOD_IDV_NOT_INC
3265 );
3266 END LOOP;
3267
3268 d_position := 40;
3269
3270 IF l_results_count < x_results.result_type.COUNT THEN
3271 x_result_type := c_result_type_WARNING;
3272 ELSE
3273 x_result_type := c_result_type_SUCCESS;
3274 END IF;
3275
3276 IF PO_LOG.d_proc THEN
3277 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3278 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
3279 END IF;
3280
3281 EXCEPTION
3282 WHEN OTHERS THEN
3283 x_result_type := c_result_type_FAILURE;
3284 IF PO_LOG.d_exc THEN
3285 PO_LOG.exc(d_mod,d_position,NULL);
3286 END IF;
3287 RAISE;
3288
3289 END generate_award_exceptions;
3290
3291 -------------------------------------------------------------------------------
3292 --Start of Comments
3293 --Name: generate_receipts_exceptions
3294 --Pre-reqs: None.
3295 --Function:
3296 -- Performs all the receipts related checks that should be made and reported as receipts related exceptions
3297 --Parameters:
3298 --IN:
3299 --p_request_id
3300 -- Request id from po_multi_mod_requests
3301 --p_document_id_tbl
3302 -- Table type having all the document_ids for a particular request_id in po_multi_mod_changes
3303 --p_multi_mod_doc_id_tbl
3304 -- Table type having all the p_multi_mod_doc_ids for a particular request_id in po_multi_mod_changes
3305 --OUT:
3306 --x_result_type
3307 -- Identifies if the validation was a success or failure
3308 --x_results
3309 -- Out variable that holds the validation results
3310 --End of Comments
3311 -------------------------------------------------------------------------------
3312 PROCEDURE generate_receipts_exceptions
3313 ( p_request_id IN NUMBER
3314 ,p_document_id_tbl IN PO_TBL_NUMBER
3315 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
3316 ,p_novation_effective_date IN DATE
3317 ,x_result_type OUT NOCOPY VARCHAR2
3318 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
3319 )
3320 is
3321 l_results_count NUMBER;
3322 d_mod CONSTANT VARCHAR2(100) := D_generate_receipts_exceptions;
3323 d_position NUMBER := 0;
3324 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
3325 l_match_option PO_LINE_LOCATIONS_ALL.match_option%TYPE;
3326 l_inspection_required_flag PO_LINE_LOCATIONS_ALL.inspection_required_flag%TYPE;
3327 l_receipt_required_flag PO_LINE_LOCATIONS_ALL.receipt_required_flag%TYPE;
3328 l_shipment_line_id_tbl PO_TBL_NUMBER;
3329 l_shipment_header_id RCV_SHIPMENT_HEADERS.shipment_header_id%TYPE;
3330 l_receipt_num RCV_SHIPMENT_HEADERS.receipt_num%TYPE;
3331 l_invoice_id AP_INVOICE_LINES_ALL.invoice_id%TYPE;
3332 l_match_exists VARCHAR2(1);
3333 l_unpaid_invoices_exists VARCHAR2(1);
3334 l_unval_invoice_exists VARCHAR2(1);
3335 l_receipt_date DATE;
3336 l_receipt_amount NUMBER;
3337 l_vendor_id NUMBER;
3338 l_results PO_MULTI_MOD_VAL_RESULTS_TYPE;
3339 l_return_status VARCHAR2(10);
3340 l_error_msg VARCHAR2(100);
3341
3342 BEGIN
3343 IF PO_LOG.d_proc THEN
3344 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
3345 PO_LOG.proc_begin(d_mod,'p_document_id_tbl',p_document_id_tbl);
3346 PO_LOG.proc_begin(d_mod,'p_multi_mod_doc_id_tbl',p_multi_mod_doc_id_tbl);
3347 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3348 END IF;
3349
3350 IF (x_results IS NULL) THEN
3351 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
3352 END IF;
3353
3354 d_position := 10;
3355
3356 l_results_count := x_results.result_type.COUNT;
3357 IF d_stmt THEN
3358 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
3359 END IF;
3360
3361 d_position := 20;
3362
3363 FOR i IN 1..p_document_id_tbl.COUNT LOOP
3364
3365 d_position := 30;
3366
3367 IF d_stmt THEN
3368 PO_LOG.stmt(d_mod,d_position,'p_document_id_tbl',p_document_id_tbl(i));
3369 END IF;
3370
3371 BEGIN
3372 SELECT shipment_line_id
3373 BULK COLLECT INTO l_shipment_line_id_tbl
3374 FROM rcv_shipment_lines
3375 WHERE po_header_id = p_document_id_tbl(i);
3376 EXCEPTION
3377 WHEN no_data_found THEN
3378 l_shipment_line_id_tbl := null;
3379 END;
3380
3381 IF d_stmt THEN
3382 PO_LOG.stmt(d_mod,d_position,'l_shipment_line_id_tbl',l_shipment_line_id_tbl);
3383 END IF;
3384
3385 IF l_shipment_line_id_tbl.COUNT > 0 THEN
3386
3387 FOR i IN 1..l_shipment_line_id_tbl.COUNT LOOP
3388
3389 d_position := 50;
3390 IF d_stmt THEN
3391 PO_LOG.stmt(d_mod,d_position,'l_shipment_line_id_tbl',l_shipment_line_id_tbl(i));
3392 END IF;
3393
3394 SELECT rcvh.shipment_header_id
3395 ,rcvh.receipt_num
3396 ,Min(rt.transaction_date)
3397 INTO l_shipment_header_id
3398 ,l_receipt_num
3399 ,l_receipt_date
3400 FROM rcv_shipment_lines rcvl
3401 ,rcv_shipment_headers rcvh
3402 , rcv_transactions rt
3403 WHERE rcvl.shipment_header_id = rcvh.shipment_header_id
3404 AND rt.shipment_header_id = rcvh.shipment_header_id
3405 AND rt.transaction_type IN ('RECEIVE', 'MATCH')
3406 AND rcvl.shipment_line_id = l_shipment_line_id_tbl(i)
3407 GROUP BY rcvh.shipment_header_id,
3408 rcvh.receipt_num;
3409
3410 SELECT Nvl(Sum(Nvl(rsl.quantity_received, 0) * NVL(poll.price_override, pol.unit_price) ), 0)
3411 INTO l_receipt_amount
3412 FROM rcv_shipment_lines rsl,
3413 po_line_locations_all poll,
3414 po_lines_all pol
3415 WHERE rsl.po_line_location_id = poll.line_location_id
3416 AND rsl.po_line_id = pol.po_line_id
3417 AND pol.po_line_id = poll.po_line_id
3418 AND rsl.quantity_shipped IS NOT NULL
3419 AND rsl.shipment_line_id = l_shipment_line_id_tbl(i);
3420
3421 IF l_receipt_amount = 0 THEN
3422 SELECT Nvl(Sum(Nvl(rsl.amount_received, 0)), 0)
3423 INTO l_receipt_amount
3424 FROM rcv_shipment_lines rsl
3425 WHERE rsl.quantity_shipped IS NULL
3426 AND rsl.shipment_line_id = l_shipment_line_id_tbl(i);
3427 END IF;
3428
3429 IF d_stmt THEN
3430 PO_LOG.stmt(d_mod,d_position,'l_shipment_header_id',l_shipment_header_id);
3431 PO_LOG.stmt(d_mod,d_position,'l_receipt_num',l_receipt_num);
3432 PO_LOG.stmt(d_mod,d_position,'l_receipt_date',l_receipt_date);
3433 PO_LOG.stmt(d_mod,d_position,'l_receipt_amount',l_receipt_amount);
3434 END IF;
3435 d_position := 60;
3436 -- if receipts exists for the PO check if corresponding invoices exists.
3437 IF check_match_exists(l_shipment_line_id_tbl(i)) THEN
3438 d_position := 70;
3439 l_invoice_id := get_invoice_id(l_shipment_line_id_tbl(i),'RECEIPT');
3440
3441 d_position := 80;
3442 --check if invoice is paid
3443 IF unpaid_invoices_exists(l_invoice_id) THEN
3444 x_results.add_result(
3445 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3446 , p_multi_mod_request_id => p_request_id
3447 , p_result_type => 'WARNING'
3448 , p_exception_type => 'Unpaid Invoices exists for Receipts.'
3449 , p_validation_type => 'RECEIPTS'
3450 , p_document_id => p_document_id_tbl(i)
3451 , p_related_document_id => l_shipment_header_id
3452 , p_related_document_number => l_receipt_num
3453 , p_related_document_date => l_receipt_date
3454 , p_related_document_amount => l_receipt_amount
3455 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNPD_RCV
3456 );
3457 END IF;
3458 d_position := 90;
3459 --check if invoice is validated
3460 IF unval_invoices_exists(p_document_id_tbl(i),l_invoice_id) THEN
3461 x_results.add_result(
3462 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3463 , p_multi_mod_request_id => p_request_id
3464 , p_result_type => 'WARNING'
3465 , p_exception_type => 'Unpaid Invoices exists for Receipts.'
3466 , p_validation_type => 'RECEIPTS'
3467 , p_document_id => p_document_id_tbl(i)
3468 , p_related_document_id => l_shipment_header_id
3469 , p_related_document_number => l_receipt_num
3470 , p_related_document_date => l_receipt_date
3471 , p_related_document_amount => l_receipt_amount
3472 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNVAL_RCV
3473 );
3474 END IF;
3475
3476 END IF; --IF l_match_exists = 'Y'
3477
3478 END LOOP; --FOR i IN 1..l_shipment_line_id_tbl.COUNT
3479
3480 END IF; --IF l_shipment_line_id_tbl.COUNT > 0
3481
3482 END LOOP; --FOR i IN 1..p_document_id_tbl.COUNT
3483
3484
3485 --get the vendor_id and call the rcv api for validations
3486 d_position := 100;
3487 BEGIN
3488 SELECT old_vendor_id
3489 INTO l_vendor_id
3490 FROM po_multi_mod_changes
3491 WHERE multi_mod_request_id = p_request_id
3492 AND change_type = 'VENDOR';
3493 EXCEPTION
3494 WHEN no_data_found THEN
3495 l_vendor_id := null;
3496 END;
3497
3498
3499 IF d_stmt THEN
3500 PO_LOG.stmt(d_mod,d_position,'l_vendor_id',l_vendor_id);
3501 END IF;
3502 RCV_VALIDATE_PO.validate_novation_receipts( p_request_id => p_request_id,
3503 p_vendor_id => l_vendor_id,
3504 p_novation_date => p_novation_effective_date,
3505 p_header_id_tbl => p_document_id_tbl,
3506 x_validation_results => l_results,
3507 x_validation_result_type => x_result_type,
3508 x_return_status => l_return_status,
3509 x_error_msg => l_error_msg
3510 );
3511
3512 IF d_stmt THEN
3513 PO_LOG.stmt(d_mod,d_position,'l_return_status',l_return_status);
3514 END IF;
3515
3516 d_position := 110;
3517 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3518 FOR i IN 1..l_results.result_type.COUNT LOOP
3519 IF d_stmt THEN
3520 PO_LOG.stmt(d_mod,d_position,'i',i);
3521 END IF;
3522 IF l_results.exception_type(i) = 'PO_SUPCHG_UNINV_RCV' AND l_results.message_name(i) IS NULL THEN
3523
3524 BEGIN
3525 SELECT match_option
3526 ,inspection_required_flag
3527 ,receipt_required_flag
3528 INTO l_match_option
3529 ,l_inspection_required_flag
3530 ,l_receipt_required_flag
3531 FROM po_line_locations_all
3532 WHERE po_header_id = l_results.document_id(i) /*Bug 13055377 - the wrong document_id was being passes and hence the proper exceptions were not shown.passing the correct documemt_id*/
3533 AND match_option='R' -- Bug 13690746 - get the first shipment with match_option 'R'
3534 AND ROWNUM =1; -- Bug 13690746 - get the first shipment with match_option 'R'
3535
3536 EXCEPTION
3537 WHEN no_data_found THEN
3538 l_match_option := null;
3539 l_inspection_required_flag := null;
3540 l_receipt_required_flag := null;
3541 END;
3542
3543 IF d_stmt THEN
3544 PO_LOG.stmt(d_mod,d_position,'l_match_option',l_match_option);
3545 PO_LOG.stmt(d_mod,d_position,'l_inspection_required_flag',l_inspection_required_flag);
3546 PO_LOG.stmt(d_mod,d_position,'l_receipt_required_flag',l_receipt_required_flag);
3547 END IF;
3548
3549 d_position := 120;
3550
3551 -- 4 way match
3552 IF (l_match_option = 'R' AND l_receipt_required_flag = 'Y' AND l_inspection_required_flag = 'Y') THEN
3553
3554 x_results.add_result(
3555 p_multi_mod_val_result_id => l_results.multi_mod_val_result_id(i)
3556 , p_multi_mod_request_id => l_results.multi_mod_request_id(i)
3557 , p_result_type => l_results.result_type(i)
3558 , p_exception_type => l_results.exception_type(i)
3559 , p_validation_type => l_results.validation_type(i)
3560 , p_document_id => l_results.document_id(i)
3561 , p_related_document_id => l_results.related_document_id(i)
3562 , p_related_document_number => l_results.related_document_number(i)
3563 , p_related_document_date => l_results.related_document_date(i)
3564 , p_related_document_amount => l_results.related_document_amount(i)
3565 , p_message_application => 'PO'
3566 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNINV_4WAY
3567 );
3568
3569 -- 3 way match
3570 ELSIF (l_match_option = 'R' AND l_receipt_required_flag = 'Y' AND l_inspection_required_flag = 'N') THEN
3571
3572 x_results.add_result(
3573 p_multi_mod_val_result_id => l_results.multi_mod_val_result_id(i)
3574 , p_multi_mod_request_id => l_results.multi_mod_request_id(i)
3575 , p_result_type => l_results.result_type(i)
3576 , p_exception_type => l_results.exception_type(i)
3577 , p_validation_type => l_results.validation_type(i)
3578 , p_document_id => l_results.document_id(i)
3579 , p_related_document_id => l_results.related_document_id(i)
3580 , p_related_document_number => l_results.related_document_number(i)
3581 , p_related_document_date => l_results.related_document_date(i)
3582 , p_related_document_amount => l_results.related_document_amount(i)
3583 , p_message_application => 'PO'
3584 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNINV_3WAY
3585 );
3586
3587 -- 2 way match
3588 ELSIF (l_match_option = 'R' AND l_receipt_required_flag = 'N' AND l_inspection_required_flag = 'N') THEN
3589
3590 x_results.add_result(
3591 p_multi_mod_val_result_id => l_results.multi_mod_val_result_id(i)
3592 , p_multi_mod_request_id => l_results.multi_mod_request_id(i)
3593 , p_result_type => l_results.result_type(i)
3594 , p_exception_type => l_results.exception_type(i)
3595 , p_validation_type => l_results.validation_type(i)
3596 , p_document_id => l_results.document_id(i)
3597 , p_related_document_id => l_results.related_document_id(i)
3598 , p_related_document_number => l_results.related_document_number(i)
3599 , p_related_document_date => l_results.related_document_date(i)
3600 , p_related_document_amount => l_results.related_document_amount(i)
3601 , p_message_application => 'PO'
3602 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNINV_2WAY
3603 );
3604
3605 END IF;
3606
3607 ELSE
3608 d_position := 130;
3609 x_results.add_result(
3610 p_multi_mod_val_result_id => l_results.multi_mod_val_result_id(i)
3611 , p_multi_mod_request_id => l_results.multi_mod_request_id(i)
3612 , p_result_type => l_results.result_type(i)
3613 , p_exception_type => l_results.exception_type(i)
3614 , p_validation_type => l_results.validation_type(i)
3615 , p_document_id => l_results.document_id(i)
3616 , p_related_document_id => l_results.related_document_id(i)
3617 , p_related_document_number => l_results.related_document_number(i)
3618 , p_related_document_date => l_results.related_document_date(i)
3619 , p_related_document_amount => l_results.related_document_amount(i)
3620 , p_message_application => 'PO'
3621 , p_message_name => l_results.message_name(i)
3622 );
3623
3624 IF d_stmt THEN
3625 PO_LOG.stmt(d_mod,d_position,'l_results.exception_type(i)',l_results.exception_type(i));
3626 END IF;
3627 END IF; --if l_results.exception_type(i) = 'PO_SUPCHG_UNINV_RCV'
3628 END LOOP;
3629 END IF; --if x_return_status
3630
3631 d_position := 140;
3632 IF d_stmt THEN
3633 PO_LOG.stmt(d_mod,d_position,'x_results.result_type.COUNT',x_results.result_type.COUNT);
3634 END IF;
3635 IF l_results_count < x_results.result_type.COUNT THEN
3636 x_result_type := c_result_type_FAILURE;
3637 ELSE
3638 x_result_type := c_result_type_SUCCESS;
3639 END IF;
3640
3641 IF PO_LOG.d_proc THEN
3642 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3643 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
3644 END IF;
3645
3646 EXCEPTION
3647 WHEN OTHERS THEN
3648 IF PO_LOG.d_exc THEN
3649 PO_LOG.exc(d_mod,d_position,NULL);
3650 END IF;
3651 RAISE;
3652
3653 END generate_receipts_exceptions;
3654 -------------------------------------------------------------------------------
3655 --Start of Comments
3656 --Name: generate_invoice_exceptions
3657 --Pre-reqs: None.
3658 --Function:
3659 -- Performs all the Invoices related checks that should be made and reported as Invoices related Exceptions
3660 --Parameters:
3661 --IN:
3662 --p_request_id
3663 -- Request id from po_multi_mod_requests
3664 --p_document_id_tbl
3665 -- Table type having all the document_ids for a particular request_id in po_multi_mod_changes
3666 --p_multi_mod_doc_id_tbl
3667 -- Table type having all the p_multi_mod_doc_ids for a particular request_id in po_multi_mod_changes
3668 --OUT:
3669 --x_result_type
3670 -- Identifies if the validation was a success or failure
3671 --x_results
3672 -- Out variable that holds the validation results
3673 --End of Comments
3674 -------------------------------------------------------------------------------
3675 PROCEDURE generate_invoice_exceptions
3676 ( p_request_id IN NUMBER
3677 ,p_document_id_tbl IN PO_TBL_NUMBER
3678 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
3679 ,p_novation_effective_date IN DATE
3680 ,x_result_type OUT NOCOPY VARCHAR2
3681 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
3682 )
3683 is
3684 l_results_count NUMBER;
3685 d_mod CONSTANT VARCHAR2(100) := D_generate_invoice_exceptions;
3686 d_position NUMBER := 0;
3687 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
3688 l_invoice_id_tbl PO_TBL_NUMBER;
3689 l_invoice_num_tbl PO_TBL_VARCHAR50;
3690 l_invoice_date_tbl PO_TBL_DATE;
3691 l_invoice_amount_tbl PO_TBL_NUMBER;
3692 --bug13084712
3693 l_invoice_id NUMBER;
3694 l_invoice_num VARCHAR2(50);
3695 l_invoice_date DATE;
3696 l_vendor_id NUMBER;
3697 l_document_id NUMBER;
3698 l_multi_mod_doc_id NUMBER;
3699
3700 BEGIN
3701 IF PO_LOG.d_proc THEN
3702 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
3703 PO_LOG.proc_begin(d_mod,'p_document_id_tbl',p_document_id_tbl);
3704 PO_LOG.proc_begin(d_mod,'p_multi_mod_doc_id_tbl',p_multi_mod_doc_id_tbl);
3705 PO_LOG.proc_begin(d_mod,'p_novation_effective_date',p_novation_effective_date);
3706 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3707 END IF;
3708
3709 IF (x_results IS NULL) THEN
3710 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
3711 END IF;
3712
3713 d_position := 10;
3714
3715 l_results_count := x_results.result_type.COUNT;
3716 IF d_stmt THEN
3717 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
3718 END IF;
3719
3720 d_position := 20;
3721
3722 FOR i IN 1..p_document_id_tbl.COUNT LOOP
3723
3724 IF d_stmt THEN
3725 PO_LOG.stmt(d_mod,d_position,'p_document_id_tbl',p_document_id_tbl(i));
3726 END IF;
3727
3728 l_document_id := p_document_id_tbl(i);
3729 l_multi_mod_doc_id := p_multi_mod_doc_id_tbl(i);
3730
3731 d_position := 30;
3732 --getting the matched invoices
3733 BEGIN
3734 SELECT apl.invoice_id, ap.invoice_num, ap.invoice_date, ap.invoice_amount
3735 BULK COLLECT INTO l_invoice_id_tbl, l_invoice_num_tbl,l_invoice_date_tbl, l_invoice_amount_tbl
3736 FROM ap_invoice_lines_all apl
3737 ,ap_invoices_all ap
3738 WHERE apl.invoice_id = ap.invoice_id
3739 AND apl.match_type = 'ITEM_TO_PO'
3740 AND apl.po_header_id = p_document_id_tbl(i);
3741 EXCEPTION
3742 WHEN no_data_found THEN
3743 l_invoice_id_tbl := null;
3744 l_invoice_num_tbl := null;
3745 l_invoice_date_tbl := null;
3746 l_invoice_amount_tbl := null;
3747 END;
3748
3749 IF d_stmt THEN
3750 PO_LOG.stmt(d_mod,d_position,'l_invoice_id_tbl',l_invoice_id_tbl);
3751 END IF;
3752
3753
3754 IF l_invoice_id_tbl.COUNT > 0 THEN
3755 FOR i IN 1..l_invoice_id_tbl.COUNT LOOP
3756 d_position := 40;
3757 --check if invoice is paid
3758 IF unpaid_invoices_exists(l_invoice_id_tbl(i)) THEN
3759 x_results.add_result(
3760 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3761 , p_multi_mod_request_id => p_request_id
3762 , p_multi_mod_doc_id => l_multi_mod_doc_id
3763 , p_result_type => 'ERROR'
3764 , p_exception_type => PO_MESSAGE_S.PO_SUPCHG_UNPD_INV_SUM
3765 , p_validation_type => 'INVOICE'
3766 , p_document_id => l_document_id
3767 , p_related_document_id => l_invoice_id_tbl(i)
3768 , p_related_document_number => l_invoice_num_tbl(i)
3769 , p_related_document_date => l_invoice_date_tbl(i)
3770 , p_related_document_amount => l_invoice_amount_tbl(i)
3771 , p_message_application => 'PO'
3772 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNPD_INV
3773 );
3774
3775 END IF;
3776
3777 d_position := 50;
3778 --check if invoice is validated
3779 IF unval_invoices_exists(l_document_id,l_invoice_id_tbl(i)) THEN
3780 x_results.add_result(
3781 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3782 , p_multi_mod_request_id => p_request_id
3783 , p_multi_mod_doc_id => l_multi_mod_doc_id
3784 , p_result_type => 'ERROR'
3785 , p_exception_type => PO_MESSAGE_S.PO_SUPCHG_UNPD_INV_SUM
3786 , p_validation_type => 'INVOICE'
3787 , p_document_id => l_document_id
3788 , p_related_document_id => l_invoice_id_tbl(i)
3789 , p_related_document_number => l_invoice_num_tbl(i)
3790 , p_related_document_date => l_invoice_date_tbl(i)
3791 , p_related_document_amount => l_invoice_amount_tbl(i)
3792 , p_message_application => 'PO'
3793 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNVAL_INV
3794 );
3795
3796
3797 END IF;
3798
3799 d_position := 60;
3800
3801 IF NVL(l_invoice_date_tbl(i),sysdate) >= NVL(p_novation_effective_date,sysdate) THEN
3802
3803 x_results.add_result(
3804 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3805 , p_multi_mod_request_id => p_request_id
3806 , p_multi_mod_doc_id => l_multi_mod_doc_id
3807 , p_result_type => 'ERROR'
3808 , p_exception_type => PO_MESSAGE_S.PO_SUPCHG_INDAT_GE_NOVDAT_SUM
3809 , p_validation_type => 'INVOICE'
3810 , p_document_id => l_document_id
3811 , p_related_document_id => l_invoice_id_tbl(i)
3812 , p_related_document_number => l_invoice_num_tbl(i)
3813 , p_related_document_date => l_invoice_date_tbl(i)
3814 , p_related_document_amount => l_invoice_amount_tbl(i)
3815 , p_message_application => 'PO'
3816 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_INDAT_GE_NOVDAT
3817 );
3818
3819 END IF;
3820 END LOOP; --FOR i IN 1..l_invoice_id_tbl.COUNT LOOP
3821
3822 END IF; --IF l_invoice_id_tbl.COUNT > 0
3823
3824 END LOOP; --FOR i IN 1..p_document_id_tbl.COUNT
3825
3826 --Check for unmatched invoices at the vendor level
3827 d_position := 70;
3828 BEGIN
3829 SELECT old_vendor_id
3830 INTO l_vendor_id
3831 FROM po_multi_mod_changes
3832 WHERE multi_mod_request_id = p_request_id
3833 AND change_type = 'VENDOR';
3834 EXCEPTION
3835 WHEN no_data_found THEN
3836 l_vendor_id := null;
3837 END;
3838
3839 IF d_stmt THEN
3840 PO_LOG.stmt(d_mod,d_position,'l_vendor_id',l_vendor_id);
3841 END IF;
3842 --bug 13084712 start
3843 -- for the vendor level checks the atleast one invoice number and details need to be shown in the exceptions report
3844 l_invoice_id_tbl := null;
3845 l_invoice_num_tbl := null;
3846 l_invoice_date_tbl := null;
3847 unmtch_inv_for_vndr_exists( p_vendor_id =>l_vendor_id
3848 ,x_invoice_id_tbl => l_invoice_id_tbl
3849 ,x_invoice_num_tbl => l_invoice_num_tbl
3850 ,x_invoice_date_tbl => l_invoice_date_tbl);
3851
3852 IF l_invoice_id_tbl.Count > 0 THEN
3853 FOR i IN 1..l_invoice_id_tbl.COUNT LOOP
3854 x_results.add_result(
3855 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3856 , p_multi_mod_request_id => p_request_id
3857 , p_multi_mod_doc_id => l_multi_mod_doc_id
3858 , p_result_type => 'ERROR'
3859 , p_exception_type => PO_MESSAGE_S.PO_SUPCHG_UNMTCH_INV_VNDR_SUM
3860 , p_validation_type => 'INVOICE'
3861 , p_document_id => null--l_vendor_id
3862 , p_related_document_id => l_invoice_id_tbl(i)
3863 , p_related_document_number => l_invoice_num_tbl(i)
3864 , p_related_document_date => l_invoice_date_tbl(i)
3865 , p_message_application => 'PO'
3866 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_UNMTCH_INV_VNDR
3867 );
3868 END LOOP;
3869 END IF;
3870
3871 d_position := 80;
3872 l_invoice_id_tbl := null;
3873 l_invoice_num_tbl := null;
3874 l_invoice_date_tbl := null;
3875 partpd_inv_for_vndr_exists(p_vendor_id =>l_vendor_id
3876 ,x_invoice_id_tbl => l_invoice_id_tbl
3877 ,x_invoice_num_tbl => l_invoice_num_tbl
3878 ,x_invoice_date_tbl => l_invoice_date_tbl);
3879
3880 IF l_invoice_id_tbl.Count > 0 THEN
3881 FOR i IN 1..l_invoice_id_tbl.COUNT LOOP
3882 x_results.add_result(
3883 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3884 , p_multi_mod_request_id => p_request_id
3885 , p_multi_mod_doc_id => l_multi_mod_doc_id
3886 , p_result_type => 'ERROR'
3887 , p_exception_type => PO_MESSAGE_S.PO_SUPCHG_PARTPD_INV_VNDR_SUM
3888 , p_validation_type => 'INVOICE'
3889 , p_document_id => null -- l_vendor_id
3890 , p_related_document_id => l_invoice_id_tbl(i)
3891 , p_related_document_number => l_invoice_num_tbl(i)
3892 , p_related_document_date => l_invoice_date_tbl(i)
3893 , p_message_application => 'PO'
3894 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_PARTPD_INV_VNDR
3895 );
3896 END LOOP;
3897 END IF;
3898
3899 d_position := 90;
3900 l_invoice_id_tbl := null;
3901 l_invoice_num_tbl := null;
3902 l_invoice_date_tbl := null;
3903 prepd_inv_for_vndr_exists(p_vendor_id =>l_vendor_id
3904 ,x_invoice_id_tbl => l_invoice_id_tbl
3905 ,x_invoice_num_tbl => l_invoice_num_tbl
3906 ,x_invoice_date_tbl => l_invoice_date_tbl);
3907
3908 IF l_invoice_id_tbl.Count > 0 THEN
3909 FOR i IN 1..l_invoice_id_tbl.COUNT LOOP
3910 x_results.add_result(
3911 p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
3912 , p_multi_mod_request_id => p_request_id
3913 , p_multi_mod_doc_id => l_multi_mod_doc_id
3914 , p_result_type => 'ERROR'
3915 , p_exception_type => PO_MESSAGE_S.PO_SUPCHG_PREPD_INV_VNDR_SUM
3916 , p_validation_type => 'INVOICE'
3917 , p_document_id => null --l_vendor_id
3918 , p_related_document_id => l_invoice_id_tbl(i)
3919 , p_related_document_number => l_invoice_num_tbl(i)
3920 , p_related_document_date => l_invoice_date_tbl(i)
3921 , p_message_application => 'PO'
3922 , p_message_name => PO_MESSAGE_S.PO_SUPCHG_PREPD_INV_VNDR
3923 );
3924 END LOOP;
3925 END IF;
3926
3927 --bug 13084712 end
3928
3929 IF l_results_count < x_results.result_type.COUNT THEN
3930 x_result_type := c_result_type_WARNING;
3931 ELSE
3932 x_result_type := c_result_type_SUCCESS;
3933 END IF;
3934
3935 IF PO_LOG.d_proc THEN
3936 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3937 -- PO_LOG.log(PO_LOG.c_PROC_END,d_mod,d_position,'x_results',x_results);
3938 END IF;
3939
3940 EXCEPTION
3941 WHEN OTHERS THEN
3942 x_result_type := c_result_type_FAILURE;
3943 IF PO_LOG.d_exc THEN
3944 PO_LOG.exc(d_mod,d_position,NULL);
3945 END IF;
3946 RAISE;
3947
3948 END generate_invoice_exceptions;
3949
3950 -------------------------------------------------------------------------------
3951 --Start of Comments
3952 --Name: unpaid_invoices_exists
3953 --Pre-reqs: None.
3954 --Function:
3955 -- Checks if there are unpaid invoices
3956 --Parameters:
3957 --IN:
3958 --p_invoice_id
3959 --End of Comments
3960 -------------------------------------------------------------------------------
3961 FUNCTION unpaid_invoices_exists( p_invoice_id IN NUMBER)
3962 RETURN BOOLEAN
3963 IS
3964 d_mod CONSTANT VARCHAR2(100) := D_unpaid_invoices_exists;
3965 d_position NUMBER := 0;
3966 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
3967 l_unpaid_invoices_exists VARCHAR2(1) := 'Y';
3968 BEGIN
3969
3970 IF PO_LOG.d_proc THEN
3971 PO_LOG.proc_begin(d_mod,'p_invoice_id',p_invoice_id);
3972 END IF;
3973
3974 d_position := 10;
3975 BEGIN
3976 SELECT 'N'
3977 INTO l_unpaid_invoices_exists
3978 FROM ap_payment_schedules_all
3979 WHERE invoice_id = p_invoice_id
3980 AND amount_remaining = 0
3981 AND payment_status_flag = 'Y';
3982 EXCEPTION
3983 WHEN no_data_found THEN
3984 l_unpaid_invoices_exists := 'Y';
3985 END;
3986
3987 IF d_stmt THEN
3988 PO_LOG.stmt(d_mod,d_position,'l_unpaid_invoices_exists',l_unpaid_invoices_exists);
3989 END IF;
3990
3991 IF l_unpaid_invoices_exists = 'N' THEN
3992 RETURN FALSE;
3993 ELSE
3994 RETURN TRUE;
3995 END IF;
3996
3997 EXCEPTION
3998 WHEN OTHERS THEN
3999 IF PO_LOG.d_exc THEN
4000 PO_LOG.exc(d_mod,d_position,NULL);
4001 END IF;
4002 RAISE;
4003 END unpaid_invoices_exists;
4004
4005
4006 -------------------------------------------------------------------------------
4007 --Start of Comments
4008 --Name: unval_invoices_exists
4009 --Pre-reqs: None.
4010 --Function:
4011 -- Checks if there are unvalidated invoices
4012 --Parameters:
4013 --IN:
4014 --p_po_header_id
4015 --p_invoice_id
4016 --End of Comments
4017 -------------------------------------------------------------------------------
4018
4019 FUNCTION unval_invoices_exists( p_po_header_id IN NUMBER
4020 ,p_invoice_id IN NUMBER
4021 )
4022 RETURN BOOLEAN
4023 IS
4024 d_mod CONSTANT VARCHAR2(100) := D_unval_invoices_exists;
4025 d_position NUMBER := 0;
4026 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4027 l_unval_invoice_exists BOOLEAN := TRUE;
4028 BEGIN
4029
4030 IF PO_LOG.d_proc THEN
4031 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
4032 PO_LOG.proc_begin(d_mod,'p_invoice_id',p_invoice_id);
4033 END IF;
4034
4035 d_position := 10;
4036
4037 l_unval_invoice_exists := AP_MATCH_UTILITIES_PUB.Check_Unvalidated_Invoices(
4038 p_invoice_type => 'BOTH',
4039 p_po_header_id => p_po_header_id,
4040 p_po_release_id => null,
4041 p_po_line_id => null,
4042 p_line_location_id => null,
4043 p_po_distribution_id => null,
4044 p_invoice_id => p_invoice_id,
4045 p_calling_sequence => null
4046 );
4047
4048 IF d_stmt THEN
4049 PO_LOG.stmt(d_mod,d_position,'l_unval_invoice_exists',l_unval_invoice_exists);
4050 END IF;
4051
4052 RETURN l_unval_invoice_exists;
4053
4054 EXCEPTION
4055 WHEN OTHERS THEN
4056 IF PO_LOG.d_exc THEN
4057 PO_LOG.exc(d_mod,d_position,NULL);
4058 END IF;
4059 RAISE;
4060 END unval_invoices_exists;
4061
4062
4063 -------------------------------------------------------------------------------
4064 --Start of Comments
4065 --Name: check_match_exists
4066 --Pre-reqs: None.
4067 --Function:
4068 -- Checks if the receipt is matched with the invoice
4069 --Parameters:
4070 --IN:
4071 --p_shipment_line_id
4072 --End of Comments
4073 -------------------------------------------------------------------------------
4074
4075 FUNCTION check_match_exists( p_shipment_line_id IN NUMBER)
4076 RETURN BOOLEAN
4077 IS
4078
4079 d_mod CONSTANT VARCHAR2(100) := D_check_match_exists;
4080 d_position NUMBER := 0;
4081 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4082 l_match_exists VARCHAR2(1) := 'N';
4083
4084 BEGIN
4085
4086 IF PO_LOG.d_proc THEN
4087 PO_LOG.proc_begin(d_mod,'p_shipment_line_id',p_shipment_line_id);
4088 END IF;
4089
4090 d_position := 10;
4091
4092 BEGIN
4093 SELECT 'Y'
4094 INTO l_match_exists
4095 FROM ap_invoice_lines_all inv,
4096 rcv_transactions rcvt
4097 WHERE inv.rcv_transaction_id = rcvt.transaction_id
4098 AND inv.match_type = 'ITEM_TO_RECEIPT'
4099 AND rcvt.shipment_line_id = p_shipment_line_id;
4100 EXCEPTION
4101 WHEN no_data_found THEN
4102 l_match_exists := 'N';
4103 END;
4104
4105 IF d_stmt THEN
4106 PO_LOG.stmt(d_mod,d_position,'l_match_exists',l_match_exists);
4107 END IF;
4108
4109 IF l_match_exists = 'Y' THEN
4110 RETURN true;
4111 ELSE
4112 RETURN false;
4113 END IF;
4114
4115 EXCEPTION
4116 WHEN OTHERS THEN
4117 IF PO_LOG.d_exc THEN
4118 PO_LOG.exc(d_mod,d_position,NULL);
4119 END IF;
4120 RAISE;
4121 END check_match_exists;
4122
4123
4124 -------------------------------------------------------------------------------
4125 --Start of Comments
4126 --Name: get_invoice_id
4127 --Pre-reqs: None.
4128 --Function:
4129 -- returns the invoice_id
4130 --Parameters:
4131 --IN:
4132 --p_doc_id
4133 -- Based on the match type the doc_id will be 'shipment_line_id' or 'po_header_id'
4134 --p_match_type
4135 --End of Comments
4136 -------------------------------------------------------------------------------
4137
4138 FUNCTION get_invoice_id( p_doc_id IN NUMBER
4139 ,p_match_type IN VARCHAR2)
4140 RETURN NUMBER
4141 IS
4142 d_mod CONSTANT VARCHAR2(100) := D_get_invoice_id;
4143 d_position NUMBER := 0;
4144 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4145 l_invoice_id NUMBER;
4146 BEGIN
4147
4148 IF PO_LOG.d_proc THEN
4149 PO_LOG.proc_begin(d_mod,'p_doc_id',p_doc_id);
4150 PO_LOG.proc_begin(d_mod,'p_match_type',p_match_type);
4151 END IF;
4152
4153 d_position := 10;
4154 IF p_match_type = 'RECEIPT' THEN
4155
4156 d_position := 20;
4157 BEGIN
4158 SELECT inv.invoice_id
4159 INTO l_invoice_id
4160 FROM ap_invoice_lines_all inv,
4161 rcv_transactions rcvt
4162 WHERE inv.rcv_transaction_id = rcvt.transaction_id
4163 AND inv.match_type = 'ITEM_TO_RECEIPT'
4164 AND rcvt.shipment_line_id = p_doc_id;
4165 EXCEPTION
4166 WHEN no_data_found THEN
4167 l_invoice_id := null;
4168 END;
4169
4170 ELSIF p_match_type = 'PO' THEN
4171
4172 d_position := 30;
4173 BEGIN
4174 SELECT invoice_id
4175 INTO l_invoice_id
4176 FROM ap_invoice_lines_all
4177 WHERE match_type = 'ITEM_TO_PO'
4178 AND po_header_id = p_doc_id;
4179 EXCEPTION
4180 WHEN no_data_found THEN
4181 l_invoice_id := null;
4182 END;
4183
4184 END IF; --IF p_match_type = 'RECEIPT'
4185
4186 IF d_stmt THEN
4187 PO_LOG.stmt(d_mod,d_position,'l_invoice_id',l_invoice_id);
4188 END IF;
4189
4190 RETURN l_invoice_id;
4191
4192 EXCEPTION
4193 WHEN OTHERS THEN
4194 IF PO_LOG.d_exc THEN
4195 PO_LOG.exc(d_mod,d_position,NULL);
4196 END IF;
4197 RAISE;
4198 END get_invoice_id;
4199
4200 -------------------------------------------------------------------------------
4201 --Start of Comments
4202 --Name: unmtch_inv_for_vndr_exists
4203 --Pre-reqs: None.
4204 --Function:
4205 -- checks if there are any unmatched invoices for a given vendor
4206 --Parameters:
4207 --IN:
4208 --p_vendor_id
4209 --End of Comments
4210 -------------------------------------------------------------------------------
4211
4212 PROCEDURE unmtch_inv_for_vndr_exists( p_vendor_id IN NUMBER
4213 --bug13084712
4214 ,x_invoice_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4215 ,x_invoice_num_tbl IN OUT NOCOPY PO_TBL_VARCHAR50
4216 ,x_invoice_date_tbl IN OUT NOCOPY PO_TBL_DATE)
4217 IS
4218 d_mod CONSTANT VARCHAR2(100) := D_unmtch_inv_for_vndr_exists;
4219 d_position NUMBER := 0;
4220 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4221
4222 BEGIN
4223
4224 IF PO_LOG.d_proc THEN
4225 PO_LOG.proc_begin(d_mod,'p_vendor_id',p_vendor_id);
4226 END IF;
4227
4228 d_position := 10;
4229 BEGIN
4230 --bug13084712. Selecting the invoice id,invoice num and invoice date as they have to be displayed in the exceptions report.
4231 SELECT ap.invoice_id,ap.invoice_num, ap.invoice_date
4232 BULK COLLECT INTO x_invoice_id_tbl, x_invoice_num_tbl, x_invoice_date_tbl
4233 FROM ap_invoices_all ap
4234 , ap_invoice_lines_all apl
4235 WHERE ap.invoice_id = apl.invoice_id
4236 AND ap.vendor_id = p_vendor_id
4237 AND NVL(apl.match_type, 'NOT_MATCHED') = 'NOT_MATCHED';
4238 EXCEPTION
4239 WHEN no_data_found THEN
4240 x_invoice_id_tbl := null;
4241 x_invoice_num_tbl := null;
4242 x_invoice_date_tbl := null;
4243 END;
4244
4245 EXCEPTION
4246 WHEN OTHERS THEN
4247 IF PO_LOG.d_exc THEN
4248 PO_LOG.exc(d_mod,d_position,NULL);
4249 END IF;
4250 RAISE;
4251 END unmtch_inv_for_vndr_exists;
4252
4253
4254 -------------------------------------------------------------------------------
4255 --Start of Comments
4256 --Name: prepd_inv_for_vndr_exists
4257 --Pre-reqs: None.
4258 --Function:
4259 -- checks if there are any pre-paid invoices for a given vendor
4260 --Parameters:
4261 --IN:
4262 --p_vendor_id
4263 --End of Comments
4264 -------------------------------------------------------------------------------
4265
4266 PROCEDURE prepd_inv_for_vndr_exists( p_vendor_id IN NUMBER
4267 --bug13084712
4268 ,x_invoice_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4269 ,x_invoice_num_tbl IN OUT NOCOPY PO_TBL_VARCHAR50
4270 ,x_invoice_date_tbl IN OUT NOCOPY PO_TBL_DATE)
4271
4272 IS
4273 d_mod CONSTANT VARCHAR2(100) := D_prepd_inv_for_vndr_exists;
4274 d_position NUMBER := 0;
4275 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4276 l_count NUMBER;
4277 BEGIN
4278
4279 IF PO_LOG.d_proc THEN
4280 PO_LOG.proc_begin(d_mod,'p_vendor_id',p_vendor_id);
4281 END IF;
4282
4283 d_position := 10;
4284 BEGIN
4285
4286 SELECT ap.invoice_id,ap.invoice_num, ap.invoice_date
4287 BULK COLLECT INTO x_invoice_id_tbl, x_invoice_num_tbl, x_invoice_date_tbl
4288 FROM ap_invoice_lines_all apl
4289 ,ap_invoices_all ap
4290 WHERE apl.prepay_invoice_id = ap.invoice_id
4291 AND NVL(apl.match_type, 'NOT_MATCHED') = 'NOT_MATCHED'
4292 AND ap.invoice_type_lookup_code = 'PREPAYMENT'
4293 AND ap.vendor_id = p_vendor_id;
4294
4295 EXCEPTION
4296 WHEN no_data_found THEN
4297 x_invoice_id_tbl := null;
4298 x_invoice_num_tbl := null;
4299 x_invoice_date_tbl := null;
4300 END;
4301
4302
4303
4304 EXCEPTION
4305 WHEN OTHERS THEN
4306 IF PO_LOG.d_exc THEN
4307 PO_LOG.exc(d_mod,d_position,NULL);
4308 END IF;
4309 RAISE;
4310 END prepd_inv_for_vndr_exists;
4311
4312
4313 -------------------------------------------------------------------------------
4314 --Start of Comments
4315 --Name: prepd_inv_for_vndr_exists
4316 --Pre-reqs: None.
4317 --Function:
4318 -- checks if there are any partly-paid invoices for a given vendor
4319 --Parameters:
4320 --IN:
4321 --p_vendor_id
4322 --End of Comments
4323 -------------------------------------------------------------------------------
4324
4325 PROCEDURE partpd_inv_for_vndr_exists( p_vendor_id IN NUMBER
4326 --bug13084712
4327 ,x_invoice_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4328 ,x_invoice_num_tbl IN OUT NOCOPY PO_TBL_VARCHAR50
4329 ,x_invoice_date_tbl IN OUT NOCOPY PO_TBL_DATE)
4330
4331 IS
4332 d_mod CONSTANT VARCHAR2(100) := D_partpd_inv_for_vndr_exists;
4333 d_position NUMBER := 0;
4334 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4335 l_count NUMBER;
4336 BEGIN
4337
4338 IF PO_LOG.d_proc THEN
4339 PO_LOG.proc_begin(d_mod,'p_vendor_id',p_vendor_id);
4340 END IF;
4341
4342 d_position := 10;
4343 BEGIN
4344 SELECT ap.invoice_id,ap.invoice_num, ap.invoice_date
4345 BULK COLLECT INTO x_invoice_id_tbl, x_invoice_num_tbl, x_invoice_date_tbl
4346 FROM ap_invoices_all ap
4347 ,ap_payment_schedules_all apps
4348 WHERE ap.invoice_id = apps.invoice_id
4349 AND NVL(ap.invoice_amount,0) <> NVL(ap.amount_paid,0)
4350 AND NVL(apps.amount_remaining,0) <> 0
4351 AND apps.payment_status_flag <> 'Y'
4352 AND ap.vendor_id = p_vendor_id;
4353
4354 EXCEPTION
4355 WHEN no_data_found THEN
4356 x_invoice_id_tbl := null;
4357 x_invoice_num_tbl := null;
4358 x_invoice_date_tbl := null;
4359 END;
4360
4361
4362 EXCEPTION
4363 WHEN OTHERS THEN
4364 IF PO_LOG.d_exc THEN
4365 PO_LOG.exc(d_mod,d_position,NULL);
4366 END IF;
4367 RAISE;
4368 END partpd_inv_for_vndr_exists;
4369
4370
4371 -------------------------------------------------------------------------------
4372 --Start of Comments
4373 --Name: set_validation_status
4374 --Pre-reqs: None.
4375 --Function:
4376 -- sets the validation status of the po_multi_mod_requests
4377 --Parameters:
4378 --IN:
4379 --p_vendor_id
4380 --End of Comments
4381 -------------------------------------------------------------------------------
4382
4383 PROCEDURE set_validation_status( p_multi_mod_request_id IN NUMBER
4384 ,p_validation_status IN VARCHAR2
4385 )
4386 IS
4387 PRAGMA AUTONOMOUS_TRANSACTION;
4388 d_mod CONSTANT VARCHAR2(100) := D_set_validation_status;
4389 d_position NUMBER := 0;
4390 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4391
4392 BEGIN
4393 IF PO_LOG.d_proc THEN
4394 PO_LOG.proc_begin(d_mod,'p_multi_mod_request_id',p_multi_mod_request_id);
4395 PO_LOG.proc_begin(d_mod,'p_validation_status',p_validation_status);
4396 END IF;
4397
4398 d_position := 10;
4399
4400 UPDATE po_multi_mod_requests
4401 SET validation_status = p_validation_status
4402 ,last_update_date = sysdate
4403 ,last_update_login = fnd_global.login_id
4404 ,last_updated_by = fnd_global.user_id
4405 WHERE multi_mod_request_id = p_multi_mod_request_id;
4406
4407 COMMIT;
4408 EXCEPTION
4409 WHEN OTHERS THEN
4410 IF PO_LOG.d_exc THEN
4411 PO_LOG.exc(d_mod,d_position,NULL);
4412 END IF;
4413 RAISE;
4414 END set_validation_status;
4415
4416
4417 -------------------------------------------------------------------------------
4418 --Start of Comments
4419 --Name: generate_project_exceptions
4420 --Pre-reqs: None.
4421 --Function:
4422 -- Call the PA validate_novation_projects API to perform the project related validations for novation
4423 --Parameters:
4424 --IN:
4425 --p_request_id
4426 -- Request id from po_multi_mod_requests
4427 --p_document_id_tbl
4428 -- Table type having all the document_ids for a particular request_id in po_multi_mod_changes
4429 --p_multi_mod_doc_id_tbl
4430 -- Table type having all the p_multi_mod_doc_ids for a particular request_id in po_multi_mod_changes
4431 --OUT:
4432 --x_result_type
4433 -- Identifies if the validation was a success or failure
4434 --x_results
4435 -- Out variable that holds the validation results
4436 --End of Comments
4437 -------------------------------------------------------------------------------
4438 PROCEDURE generate_project_exceptions
4439 ( p_request_id IN NUMBER
4440 ,p_document_id_tbl IN PO_TBL_NUMBER
4441 ,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
4442 ,p_novation_effective_date IN DATE
4443 ,x_result_type OUT NOCOPY VARCHAR2
4444 ,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
4445 )
4446 is
4447 l_results_count NUMBER;
4448 d_mod CONSTANT VARCHAR2(100) := D_generate_project_exceptions;
4449 d_position NUMBER := 0;
4450 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4451 l_vendor_id NUMBER;
4452 l_return_status VARCHAR2(10);
4453 l_error_msg VARCHAR2(100);
4454
4455 BEGIN
4456 IF PO_LOG.d_proc THEN
4457 PO_LOG.proc_begin(d_mod,'p_request_id',p_request_id);
4458 PO_LOG.proc_begin(d_mod,'p_document_id_tbl',p_document_id_tbl);
4459 PO_LOG.proc_begin(d_mod,'p_multi_mod_doc_id_tbl',p_multi_mod_doc_id_tbl);
4460 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
4461 END IF;
4462
4463 IF (x_results IS NULL) THEN
4464 x_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.new_instance();
4465 END IF;
4466
4467 d_position := 10;
4468
4469 l_results_count := x_results.result_type.COUNT;
4470 IF d_stmt THEN
4471 PO_LOG.stmt(d_mod,d_position,'l_results_count',l_results_count);
4472 END IF;
4473
4474 d_position := 20;
4475 --get the vendor_id and call the projects api for validations
4476 BEGIN
4477 SELECT old_vendor_id
4478 INTO l_vendor_id
4479 FROM po_multi_mod_changes
4480 WHERE multi_mod_request_id = p_request_id
4481 AND change_type = 'VENDOR';
4482 EXCEPTION
4483 WHEN no_data_found THEN
4484 l_vendor_id := null;
4485 END;
4486
4487
4488 IF d_stmt THEN
4489 PO_LOG.stmt(d_mod,d_position,'l_vendor_id',l_vendor_id);
4490 END IF;
4491
4492 d_position := 30;
4493
4494 pa_po_integration_utils.validate_novation_projects( p_request_id => p_request_id,
4495 p_vendor_id => l_vendor_id,
4496 p_novation_date => p_novation_effective_date,
4497 p_header_id_tbl => p_document_id_tbl,
4498 x_validation_results => x_results,
4499 x_validation_result_type => x_result_type,
4500 x_return_status => l_return_status,
4501 x_error_msg => l_error_msg
4502 );
4503
4504 IF d_stmt THEN
4505 PO_LOG.stmt(d_mod,d_position,'l_return_status',l_return_status);
4506 PO_LOG.stmt(d_mod,d_position,'l_error_msg',l_error_msg);
4507 END IF;
4508
4509
4510 d_position := 40;
4511 IF d_stmt THEN
4512 PO_LOG.stmt(d_mod,d_position,'x_results.result_type.COUNT',x_results.result_type.COUNT);
4513 END IF;
4514 IF l_results_count < x_results.result_type.COUNT THEN
4515 x_result_type := c_result_type_FAILURE;
4516 ELSE
4517 x_result_type := c_result_type_SUCCESS;
4518 END IF;
4519
4520 IF PO_LOG.d_proc THEN
4521 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4522 END IF;
4523
4524 EXCEPTION
4525 WHEN OTHERS THEN
4526 IF PO_LOG.d_exc THEN
4527 PO_LOG.exc(d_mod,d_position,'Unhandled exception in project validation');
4528 END IF;
4529 RAISE;
4530
4531 END generate_project_exceptions;
4532
4533 -----------------------------------------------------------------------------
4534 --Start of Comments
4535 --Name: set_validation_status
4536 --Pre-reqs: None.
4537 --Function:
4538 -- sets the status of the po_multi_mod_docs
4539 --Parameters:
4540 --IN:
4541 --p_vendor_id
4542 --End of Comments
4543 -------------------------------------------------------------------------------
4544 PROCEDURE generate_supp_chg_exceptions( errbuf OUT NOCOPY VARCHAR2
4545 ,retcode OUT NOCOPY VARCHAR2
4546 ,p_multi_mod_request_id IN NUMBER
4547 )
4548 IS
4549 d_mod CONSTANT VARCHAR2(100) := D_generate_supp_chg_exceptions;
4550 d_position NUMBER := 0;
4551 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
4552 l_results PO_MULTI_MOD_VAL_RESULTS_TYPE;
4553 l_result_type VARCHAR2(30);
4554 BEGIN
4555 IF PO_LOG.d_proc THEN
4556 PO_LOG.proc_begin(d_mod,'p_request_id',p_multi_mod_request_id);
4557 END IF;
4558
4559 d_position := 10;
4560 --call the validate_mult_mod routine to generate the supplier change exceptions
4561 PO_MULTI_MOD_VALIDATIONS.validate_multi_mod
4562 ( p_multi_mod_request_id => p_multi_mod_request_id
4563 ,p_validation_type => 'SUPP_CHG_EXCEPTIONS'
4564 ,x_result_type => l_result_type
4565 ,x_results => l_results
4566 );
4567
4568 d_position := 20;
4569 IF PO_LOG.d_proc THEN
4570 PO_LOG.proc_begin(d_mod,'l_results.result_type.COUNT',l_results.result_type.COUNT);
4571 END IF;
4572 -- insert the results in po_multi_mod_val_results table
4573 FOR i IN 1..l_results.result_type.COUNT LOOP
4574
4575 INSERT INTO po_multi_mod_val_results
4576 ( MULTI_MOD_VAL_RESULT_ID
4577 ,MULTI_MOD_REQUEST_ID
4578 ,MULTI_MOD_DOC_ID
4579 ,VALIDATION_TYPE
4580 ,EXCEPTION_TYPE
4581 ,RESULT_TYPE
4582 ,DOCUMENT_ID
4583 ,DOCUMENT_NUMBER
4584 ,RELATED_DOCUMENT_ID
4585 ,RELATED_DOCUMENT_NUMBER
4586 ,RELATED_DOCUMENT_DATE
4587 ,RELATED_DOCUMENT_AMOUNT
4588 ,MESSAGE_APPLICATION
4589 ,MESSAGE_NAME
4590 ,TOKEN1_NAME
4591 ,TOKEN1_VALUE
4592 ,TOKEN2_NAME
4593 ,TOKEN2_VALUE
4594 ,TOKEN3_NAME
4595 ,TOKEN3_VALUE
4596 ,TOKEN4_NAME
4597 ,TOKEN4_VALUE
4598 ,TOKEN5_NAME
4599 ,TOKEN5_VALUE
4600 ,TOKEN6_NAME
4601 ,TOKEN6_VALUE
4602 ,LAST_UPDATE_DATE
4603 ,LAST_UPDATED_BY
4604 ,LAST_UPDATE_LOGIN
4605 ,CREATION_DATE
4606 ,CREATED_BY
4607 ,REQUEST_ID
4608 )
4609 VALUES
4610 ( l_results.multi_mod_val_result_id(i)
4611 , l_results.multi_mod_request_id(i)
4612 , l_results.multi_mod_doc_id(i)
4613 , l_results.validation_type(i)
4614 , l_results.exception_type(i)
4615 , l_results.result_type(i)
4616 , l_results.document_id(i)
4617 , l_results.document_number(i)
4618 , l_results.related_document_id(i)
4619 , l_results.related_document_number(i)
4620 , l_results.related_document_date(i)
4621 , l_results.related_document_amount(i)
4622 , l_results.message_application(i)
4623 , l_results.message_name(i)
4624 , l_results.token1_name(i)
4625 , l_results.token1_value(i)
4626 , l_results.token2_name(i)
4627 , l_results.token2_value(i)
4628 , l_results.token3_name(i)
4629 , l_results.token3_value(i)
4630 , l_results.token4_name(i)
4631 , l_results.token4_value(i)
4632 , l_results.token5_name(i)
4633 , l_results.token5_value(i)
4634 , l_results.token6_name(i)
4635 , l_results.token6_value(i)
4636 , sysdate
4637 , fnd_global.user_id
4638 , fnd_global.login_id
4639 , sysdate
4640 , fnd_global.user_id
4641 , fnd_global.conc_request_id
4642 );
4643 END LOOP;
4644 /*bug 13094620 - the selection changed flag has to be reset once the supplier change exceptions program has been regenerated.*/
4645 UPDATE po_multi_mod_requests
4646 SET selection_changed_flag = null
4647 WHERE multi_mod_request_id = p_multi_mod_request_id
4648 and selection_changed_flag is not null;
4649 commit;
4650 EXCEPTION
4651 WHEN OTHERS THEN
4652 IF PO_LOG.d_exc THEN
4653 PO_LOG.exc(d_mod,d_position,NULL);
4654 END IF;
4655 RAISE;
4656 END generate_supp_chg_exceptions;
4657
4658 END PO_MULTI_MOD_VALIDATIONS;