1 PACKAGE BODY PO_ASL_API_GRP AS
2 /* $Header: PO_ASL_API_GRP.plb 120.3.12020000.2 2013/02/11 13:40:48 vegajula noship $ */
3
4 g_session_key NUMBER;
5
6 PROCEDURE validate_asl_gt(
7 x_return_status OUT NOCOPY VARCHAR2
8 , x_return_msg OUT NOCOPY VARCHAR2
9 );
10
11 PROCEDURE validate_asl_attr_gt(
12 x_return_status OUT NOCOPY VARCHAR2
13 , x_return_msg OUT NOCOPY VARCHAR2
14 );
15
16 PROCEDURE validate_asl_doc_gt(
17 x_return_status OUT NOCOPY VARCHAR2
18 , x_return_msg OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE validate_chv_auth_gt(
22 x_return_status OUT NOCOPY VARCHAR2
23 , x_return_msg OUT NOCOPY VARCHAR2
24 );
25
26 PROCEDURE validate_supp_item_cap_gt(
27 x_return_status OUT NOCOPY VARCHAR2
28 , x_return_msg OUT NOCOPY VARCHAR2
29 );
30
31 PROCEDURE validate_supp_item_tol_gt(
32 x_return_status OUT NOCOPY VARCHAR2
33 , x_return_msg OUT NOCOPY VARCHAR2
34 );
35
36 --------------------------------------------------------------------------------
37 --Start of Comments
38
39 --Name: process
40
41 --Function:
42 -- This will determine whether to do insert or update.
43 -- Create will throw error if you are passing duplicate asl.
44 -- Update will throw error if asl does not exists.
45 -- Call Validation interface to perform field validations
46 -- Call PO_ASL_API_PVT.reject_asl_record for the records in case any
47 -- validation error.
48
49 --Parameters:
50
51 --IN:
52 -- p_session_key NUMBER
53
54 --OUT:
55 -- x_return_status VARCHAR2
56 -- x_return_msg VARCHAR2
57
58 --End of Comments
59 --------------------------------------------------------------------------------
60
61 PROCEDURE process(
62 p_session_key IN NUMBER
63 , x_return_status OUT NOCOPY VARCHAR2
64 , x_return_msg OUT NOCOPY VARCHAR2
65 )
66 IS
67 l_progress NUMBER := 0;
68
69 l_user_key_tbl po_tbl_number;
70 l_entity_name po_tbl_varchar30;
71 l_reject_reason po_tbl_varchar2000;
72
73 l_user_key_tbl1 po_tbl_number;
74 l_entity_name1 po_tbl_varchar30;
75 l_reject_reason1 po_tbl_varchar2000;
76
77 BEGIN
78 po_asl_api_pvt.log('START ::: po_asl_api_grp.process');
79 po_asl_api_pvt.log('p_session_key:' || p_session_key);
80 g_session_key := p_session_key;
81 --Determine Create/Update when mode is Sync
82 UPDATE po_approved_supplier_list_gt GT
83 SET GT.process_action =
84 determine_action(
85 p_item_id => GT.item_id ,
86 p_category_id => GT.category_id ,
87 p_using_organization_id => GT.using_organization_id,
88 p_vendor_id => GT.vendor_id ,
89 p_vendor_site_id => GT.vendor_site_id
90 )
91 WHERE GT.process_action = PO_ASL_API_PUB.g_ACTION_SYNC;
92 l_progress := 5;
93 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
94
95 --Populate asl ids in case of update
96 /* RR: Review Perf */
97 UPDATE po_approved_supplier_list_gt GT
98 SET asl_id =
99 (SELECT asl_id
100 FROM po_approved_supplier_list PASL
101 WHERE (GT.item_id = PASL.item_id
102 OR GT.category_id = PASL.category_id)
103 AND GT.using_organization_id = PASL.using_organization_id
104 AND (GT.vendor_id = PASL.vendor_id
105 OR GT.manufacturer_id = PASL.manufacturer_id)
106 AND Nvl(GT.vendor_site_id,-1)= Nvl(PASL.vendor_site_id,-1)
107 AND ROWNUM < 2)
108 WHERE GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE;
109 l_progress := 10;
110 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
111
112 --Populate asl ids in case of create
113 UPDATE po_approved_supplier_list_gt GT
114 SET asl_id = po_approved_supplier_list_s.NEXTVAL
115 WHERE GT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE;
116
117 l_progress := 15;
118 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
119
120 UPDATE po_asl_attributes_gt PAA
121 SET asl_id = (SELECT asl_id
122 FROM po_approved_supplier_list_gt PASL
123 WHERE PASL.user_key = PAA.user_key
124 AND ROWNUM < 2);
125
126 l_progress := 20;
127 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
128
129 UPDATE po_asl_documents_gt PAD
130 SET asl_id = (SELECT asl_id
131 FROM po_approved_supplier_list_gt PASL
132 WHERE PASL.user_key = PAD.user_key
133 AND ROWNUM < 2);
134
135 l_progress := 25;
136 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
137
138 UPDATE chv_authorizations_gt CHV
139 SET reference_id = (SELECT asl_id
140 FROM po_approved_supplier_list_gt PASL
141 WHERE PASL.user_key = CHV.user_key
142 AND ROWNUM < 2);
143
144 l_progress := 30;
145 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
146
147 UPDATE po_supplier_item_capacity_gt PSIC
148 SET asl_id = (SELECT asl_id
149 FROM po_approved_supplier_list_gt PASL
150 WHERE PASL.user_key = PSIC.user_key
151 AND ROWNUM < 2);
152
153 l_progress := 35;
154 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
155
156 UPDATE po_supplier_item_tolerance_gt PSIT
157 SET asl_id = (SELECT asl_id
158 FROM po_approved_supplier_list_gt PASL
159 WHERE PASL.user_key = PSIT.user_key
160 AND ROWNUM < 2);
161
162 l_progress := 40;
163 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);
164
165 /*--Populate capacity id in case of update and
166 --Capacity's process action is ADD
167 UPDATE po_supplier_item_capacity_gt PSIC
168 SET capacity_id = po_supplier_item_capacity_s.NEXTVAL
169 WHERE process_action = PO_ASL_API_PUB.g_ACTION_ADD;
170 l_progress := 42;
171 po_asl_api_pvt.log('count at prg ' || l_progress || ':' || SQL%ROWCOUNT);*/
172
173 --Reject records if asl exist when mode is Create
174 SELECT user_key ,
175 'po_approved_supplier_list_gt' ,
176 fnd_message.get_string('PO','DUPLICATE_ASL')
177 BULK COLLECT INTO
178 l_user_key_tbl ,
179 l_entity_name ,
180 l_reject_reason
181 FROM po_approved_supplier_list_gt GT
182 WHERE EXISTS
183 (SELECT asl_id
184 FROM po_approved_supplier_list PASL
185 WHERE (GT.item_id = PASL.item_id
186 OR GT.category_id = PASL.category_id)
187 AND GT.using_organization_id = PASL.using_organization_id
188 AND (GT.vendor_id = PASL.vendor_id
189 OR GT.manufacturer_id = PASL.manufacturer_id)
190 AND Nvl(GT.vendor_site_id,-1) = Nvl(PASL.vendor_site_id,-1))
191 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE;
192
193 l_progress := 45;
194 l_user_key_tbl1 := l_user_key_tbl;
195 l_entity_name1 := l_entity_name;
196 l_reject_reason1 := l_reject_reason;
197 --Reject records if asl doesn't exist when mode is update.
198 SELECT user_key ,
199 'po_approved_supplier_list_gt' ,
200 fnd_message.get_string('PO','ASL_DOES_NOT_EXIST')
201 BULK COLLECT INTO
202 l_user_key_tbl ,
203 l_entity_name ,
204 l_reject_reason
205 FROM po_approved_supplier_list_gt GT
206 WHERE NOT EXISTS
207 (SELECT asl_id
208 FROM po_approved_supplier_list PASL
209 WHERE (GT.item_id = PASL.item_id
210 OR GT.category_id = PASL.category_id)
211 AND GT.using_organization_id = PASL.using_organization_id
212 AND (GT.vendor_id = PASL.vendor_id
213 OR GT.manufacturer_id = PASL.manufacturer_id)
214 AND Nvl(GT.vendor_site_id,-1) = Nvl(PASL.vendor_site_id,-1))
215 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE;
216
217 l_progress := 50;
218 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
219 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
220 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
221
222 --Reject records if attributes mode is add, asl mode is update/delete and
223 --attributes already exists for that org
224 SELECT PAAGT.user_key ,
225 'po_asl_attributes_gt' ,
226 fnd_message.get_string('PO','DUPLICATE_ATTRIBUTES')
227 BULK COLLECT INTO
228 l_user_key_tbl ,
229 l_entity_name ,
230 l_reject_reason
231 FROM po_asl_attributes_gt PAAGT,
232 po_approved_supplier_list_gt ASLGT
233 WHERE EXISTS
234 (SELECT 1
235 FROM po_asl_attributes PAA
236 WHERE PAAGT.asl_id = PAA.asl_id
237 AND PAAGT.using_organization_id = PAA.using_organization_id)
238 AND ASLGT.user_key = PAAGT.user_key
239 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
240 AND PAAGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
241
242 l_progress := 55;
243 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
244 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
245 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
246
247 --Reject records if attributes mode is add, asl mode is create and
248 --attributes already exists for that org, i.e., duplicate input
249 SELECT PAAGT.user_key ,
250 'po_asl_attributes_gt' ,
251 fnd_message.get_string('PO','DUPLICATE_ATTRIBUTES')
252 BULK COLLECT INTO
253 l_user_key_tbl ,
254 l_entity_name ,
255 l_reject_reason
256 FROM po_asl_attributes_gt PAAGT,
257 po_approved_supplier_list_gt ASLGT
258 WHERE 2 <=
259 (SELECT Count(user_key)
260 FROM po_asl_attributes_gt PAA
261 WHERE PAAGT.asl_id = PAA.asl_id
262 AND PAAGT.using_organization_id = PAA.using_organization_id)
263 AND ASLGT.user_key = PAAGT.user_key
264 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
265 AND PAAGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
266
267 l_progress := 57;
268 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
269 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
270 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
271
272 --Reject records if attributes mode is delete, asl mode is update and
273 --attributes doesn't exists for that org
274 SELECT PAAGT.user_key ,
275 'po_asl_attributes_gt' ,
276 fnd_message.get_string('PO','ATTRIBUTES_NOT_EXIST')
277 BULK COLLECT INTO
278 l_user_key_tbl ,
279 l_entity_name ,
280 l_reject_reason
281 FROM po_asl_attributes_gt PAAGT,
282 po_approved_supplier_list_gt ASLGT
283 WHERE NOT EXISTS
284 (SELECT 1
285 FROM po_asl_attributes PAA
286 WHERE PAAGT.asl_id = PAA.asl_id
287 AND PAAGT.using_organization_id = PAA.using_organization_id)
288 AND ASLGT.user_key = PAAGT.user_key
289 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
290 AND PAAGT.process_action IN
291 (PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
292
293 l_progress := 60;
294 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
295 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
296 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
297 --Reject records if document mode is add, asl mode is update and
298 --document already exists for that org
299 SELECT DOCGT.user_key ,
300 'po_asl_documents_gt' ,
301 fnd_message.get_string('PO','DUPLICATE_DOCUMENT')
302 BULK COLLECT INTO
303 l_user_key_tbl ,
304 l_entity_name ,
305 l_reject_reason
306 FROM po_asl_documents_gt DOCGT,
307 po_approved_supplier_list_gt ASLGT
308 WHERE EXISTS
309 (SELECT 1
310 FROM po_asl_documents PAD
311 WHERE DOCGT.document_header_id = PAD.document_header_id
312 AND DOCGT.asl_id = PAD.asl_id
313 AND DOCGT.using_organization_id = PAD.using_organization_id)
314 AND ASLGT.user_key = DOCGT.user_key
315 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
316 AND DOCGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
317
318 l_progress := 62;
319 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
320 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
321 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
322 --Reject records if document mode is delete, asl mode is update and
323 --document doesn't exists for that org
324 SELECT DOCGT.user_key ,
325 'po_asl_documents_gt' ,
326 fnd_message.get_string('PO','DOCUMENT_NOT_EXIST')
327 BULK COLLECT INTO
328 l_user_key_tbl ,
329 l_entity_name ,
330 l_reject_reason
331 FROM po_asl_documents_gt DOCGT,
332 po_approved_supplier_list_gt ASLGT
333 WHERE NOT EXISTS
334 (SELECT 1
335 FROM po_asl_documents PAD
336 WHERE DOCGT.document_header_id = PAD.document_header_id
337 AND DOCGT.asl_id = PAD.asl_id
338 AND DOCGT.using_organization_id = PAD.using_organization_id)
339 AND ASLGT.user_key = DOCGT.user_key
340 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
341 AND DOCGT.process_action IN
342 (PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
343
344 l_progress := 65;
345 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
346 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
347 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
348 --Reject records if document mode is add, asl mode is create and
349 --document already exists for that org
350 SELECT DOCGT.user_key ,
351 'po_asl_documents_gt' ,
352 fnd_message.get_string('PO','DUPLICATE_DOCUMENT')
353 BULK COLLECT INTO
354 l_user_key_tbl ,
355 l_entity_name ,
356 l_reject_reason
357 FROM po_asl_documents_gt DOCGT,
358 po_approved_supplier_list_gt ASLGT
359 WHERE 2 <=
360 (SELECT Count(user_key)
361 FROM po_asl_documents_gt PAD
362 WHERE DOCGT.document_header_id = PAD.document_header_id
363 AND DOCGT.asl_id = PAD.asl_id
364 AND DOCGT.using_organization_id = PAD.using_organization_id)
365 AND ASLGT.user_key = DOCGT.user_key
366 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
367 AND DOCGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
368
369 l_progress := 70;
370 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
371 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
372 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
373 --Reject records if authorization process mode is add, asl mode is update
374 --and authorization code or sequence already exists for that org
375 SELECT CHVGT.user_key ,
376 'chv_authorizations_gt' ,
377 fnd_message.get_string('PO','DUPLICATE_AUTHORIZATION')
378 BULK COLLECT INTO
379 l_user_key_tbl ,
380 l_entity_name ,
381 l_reject_reason
382 FROM chv_authorizations_gt CHVGT,
383 po_approved_supplier_list_gt ASLGT
384 WHERE EXISTS
385 (SELECT 1
386 FROM chv_authorizations CHV
387 WHERE CHVGT.reference_id = CHV.reference_id
388 AND CHVGT.using_organization_id = CHV.using_organization_id
389 AND (CHVGT.authorization_code = CHV.authorization_code
390 OR
391 CHVGT.authorization_sequence_dsp=CHV.authorization_sequence)
392 )
393 AND CHVGT.user_key = ASLGT.user_key
394 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
395 AND CHVGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
396
397 l_progress := 72;
398 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
399 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
400 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
401 --Reject records if authorization process mode is delete, asl mode is update
402 --and authorization code and sequence not exists for that org
403 SELECT CHVGT.user_key ,
404 'chv_authorizations_gt' ,
405 fnd_message.get_string('PO','AUTHORIZATION_NOT_EXIST')
406 BULK COLLECT INTO
407 l_user_key_tbl ,
408 l_entity_name ,
409 l_reject_reason
410 FROM chv_authorizations_gt CHVGT,
411 po_approved_supplier_list_gt ASLGT
412 WHERE NOT EXISTS
413 (SELECT 1
414 FROM chv_authorizations CHV
415 WHERE CHVGT.reference_id = CHV.reference_id
416 AND CHVGT.using_organization_id = CHV.using_organization_id
417 AND CHVGT.authorization_code = CHV.authorization_code
418 AND CHVGT.authorization_sequence_dsp=CHV.authorization_sequence
419 )
420 AND CHVGT.user_key = ASLGT.user_key
421 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
422 AND CHVGT.process_action IN
423 (PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
424
425 l_progress := 75;
426 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
427 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
428 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
429 --Reject records if authorization process mode is add, asl mode is create
430 --and authorization code or sequence already exists for that org
431 SELECT CHVGT.user_key ,
432 'chv_authorizations_gt' ,
433 fnd_message.get_string('PO','DUPLICATE_AUTHORIZATION')
434 BULK COLLECT INTO
435 l_user_key_tbl ,
436 l_entity_name ,
437 l_reject_reason
438 FROM chv_authorizations_gt CHVGT ,
439 po_approved_supplier_list_gt ASLGT
440 WHERE 2 <=
441 (SELECT Count(user_key)
442 FROM chv_authorizations_gt CHV
443 WHERE CHVGT.reference_id = CHV.reference_id
444 AND CHVGT.using_organization_id = CHV.using_organization_id
445 AND (CHVGT.authorization_code = CHV.authorization_code
446 OR CHVGT.authorization_sequence_dsp =
447 CHV.authorization_sequence_dsp)
448 )
449 AND CHVGT.user_key = ASLGT.user_key
450 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
451 AND CHVGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
452
453 --*************************************************************************
454 --Duplicate check is not required for po_supplier_item_capacity_gt
455 --as there is a check in the validation for dates overlapping. That check
456 --covers the duplication as well
457 --*************************************************************************
458
459 l_progress := 78;
460 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
461 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
462 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
463 --Reject records if capacity process mode is delete, asl mode is update
464 --and record does not exist for that org
465 SELECT CAPGT.user_key ,
466 'po_supplier_item_capacity_gt' ,
467 fnd_message.get_string('PO','CAPACITY_NOT_EXIST')
468 BULK COLLECT INTO
469 l_user_key_tbl ,
470 l_entity_name ,
471 l_reject_reason
472 FROM po_supplier_item_capacity_gt CAPGT ,
473 po_approved_supplier_list_gt ASLGT
474 WHERE NOT EXISTS
475 (SELECT 1
476 FROM po_supplier_item_capacity CAP
477 WHERE CAPGT.asl_id = CAP.asl_id
478 AND CAPGT.using_organization_id = CAP.using_organization_id
479 AND Nvl(CAPGT.to_date_dsp, SYSDATE) =
480 Nvl(CAP.to_date, SYSDATE)
481 AND CAPGT.from_date_dsp = CAP.from_date
482 AND CAPGT.capacity_per_day_dsp = CAP.capacity_per_day
483 )
484 AND CAPGT.user_key = ASLGT.user_key
485 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
486 AND CAPGT.process_action IN
487 (PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
488
489 l_progress := 80;
490 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
491 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
492 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
493 --Reject records if tolerance process mode is add, asl mode is update
494 --and record already exists for that org
495 SELECT TOLGT.user_key ,
496 'po_supplier_item_tolerance_gt' ,
497 fnd_message.get_string('PO','DUPLICATE_TOLERANCE')
498 BULK COLLECT INTO
499 l_user_key_tbl ,
500 l_entity_name ,
501 l_reject_reason
502 FROM po_supplier_item_tolerance_gt TOLGT,
503 po_approved_supplier_list_gt ASLGT
504 WHERE EXISTS
505 (SELECT 1
506 FROM po_supplier_item_tolerance TOL
507 WHERE TOLGT.asl_id = TOL.asl_id
508 AND TOLGT.using_organization_id = TOL.using_organization_id
509 AND TOLGT.number_of_days_dsp = TOL.number_of_days
510 )
511 AND TOLGT.user_key = ASLGT.user_key
512 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
513 AND TOLGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
514
515 l_progress := 82;
516 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
517 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
518 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
519 --Reject records if tolerance process mode is delete, asl mode is update
520 --and record does not exist for that org
521 SELECT TOLGT.user_key ,
522 'po_supplier_item_tolerance_gt' ,
523 fnd_message.get_string('PO','TOLERANCE_NOT_EXIST')
524 BULK COLLECT INTO
525 l_user_key_tbl ,
526 l_entity_name ,
527 l_reject_reason
528 FROM po_supplier_item_tolerance_gt TOLGT,
529 po_approved_supplier_list_gt ASLGT
530 WHERE NOT EXISTS
531 (SELECT 1
532 FROM po_supplier_item_tolerance TOL
533 WHERE TOLGT.asl_id = TOL.asl_id
534 AND TOLGT.using_organization_id = TOL.using_organization_id
535 AND TOLGT.number_of_days_dsp = TOL.number_of_days
536 )
537 AND TOLGT.user_key = ASLGT.user_key
538 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
539 AND TOLGT.process_action IN
540 (PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
541
542 l_progress := 85;
543 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
544 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
545 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
546 --Reject records if tolerance process mode is add, asl mode is create
547 --and authorization code or sequence already exists for that org
548 SELECT TOLGT.user_key ,
549 'po_supplier_item_tolerance_gt' ,
550 fnd_message.get_string('PO','DUPLICATE_TOLERANCE')
551 BULK COLLECT INTO
552 l_user_key_tbl ,
553 l_entity_name ,
554 l_reject_reason
555 FROM po_supplier_item_tolerance_gt TOLGT,
556 po_approved_supplier_list_gt ASLGT
557 WHERE 2 <=
558 (SELECT Count(user_key)
559 FROM po_supplier_item_tolerance_gt TOL
560 WHERE TOLGT.asl_id = TOL.asl_id
561 AND TOLGT.using_organization_id = TOL.using_organization_id
562 AND TOLGT.number_of_days_dsp = TOL.number_of_days_dsp
563 )
564 AND TOLGT.user_key = ASLGT.user_key
565 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
566 AND TOLGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
567
568 l_progress := 90;
569 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
570 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
571 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
572
573 po_asl_api_pvt.log('PO_ASL_API_GRP Rejected Rec count:'
574 || l_user_key_tbl1.count);
575
576 --call po_asl_api_pvt.reject_asl_record for above rejected records
577 IF l_user_key_tbl1.Count > 0
578 THEN
579 po_asl_api_pvt.reject_asl_record(
580 p_user_key_tbl => l_user_key_tbl1,
581 p_rejection_reason => l_reject_reason1,
582 p_entity_name => l_entity_name1,
583 p_session_key => g_session_key,
584 x_return_status => x_return_status,
585 x_return_msg => x_return_msg
586 );
587 END IF;
588 l_progress := 91;
589 --Peform validation on fields data
590 validate_asl_gt(
591 x_return_status => x_return_status,
592 x_return_msg => x_return_msg
593 );
594 l_progress := 92;
595 validate_asl_attr_gt(
596 x_return_status => x_return_status,
597 x_return_msg => x_return_msg
598 );
599 l_progress := 95;
600 validate_asl_doc_gt(
601 x_return_status => x_return_status,
602 x_return_msg => x_return_msg
603 );
604 l_progress := 96;
605 validate_chv_auth_gt(
606 x_return_status => x_return_status,
607 x_return_msg => x_return_msg
608 );
609 l_progress := 97;
610 validate_supp_item_cap_gt(
611 x_return_status => x_return_status,
612 x_return_msg => x_return_msg
613 );
614 l_progress := 98;
615 validate_supp_item_tol_gt(
616 x_return_status => x_return_status,
617 x_return_msg => x_return_msg
618 );
619 l_progress := 99;
620 --call the po_asl_api_pub.process for the next steps.
621 po_asl_api_pvt.process(
622 p_session_key => p_session_key,
623 x_return_status => x_return_status,
624 x_return_msg => x_return_msg
625 );
626 l_progress := 100;
627 po_asl_api_pvt.log('END ::: po_asl_api_grp.process');
628
629 EXCEPTION
630
631 WHEN OTHERS THEN
632
633 po_asl_api_pvt.log('po_asl_api_grp.process : when others exception at '
634 || l_progress || ';' || SQLERRM );
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 x_return_msg := SQLERRM;
637
638 END process;
639
640 --------------------------------------------------------------------------------
641 --Start of Comments
642
643 --Name: validate_asl_gt
644
645 --Function:
646 -- This will validate data in po_approved_supplier_list_gt table
647
648 --Parameters:
649
650 --OUT:
651 -- x_return_status VARCHAR2
652 -- x_return_msg VARCHAR2
653
654 --End of Comments
655 --------------------------------------------------------------------------------
656
657 PROCEDURE validate_asl_gt(
658 x_return_status OUT NOCOPY VARCHAR2
659 , x_return_msg OUT NOCOPY VARCHAR2
660 )
661 IS
662 l_progress NUMBER := 0;
663
664 l_user_key_tbl po_tbl_number;
665 l_entity_name po_tbl_varchar30;
666 l_reject_reason po_tbl_varchar2000;
667 l_user_key_tbl1 po_tbl_number;
668 l_entity_name1 po_tbl_varchar30;
669 l_reject_reason1 po_tbl_varchar2000;
670
671 BEGIN
672 po_asl_api_pvt.log('START ::: po_asl_api_grp.validate_asl_gt');
673 SELECT user_key ,
674 entity ,
675 msg
676 BULK COLLECT INTO
677 l_user_key_tbl ,
678 l_entity_name ,
679 l_reject_reason
680 FROM (
681 --Reject records if item and category both exists for the same ASL
682 SELECT user_key ,
683 'po_approved_supplier_list_gt' AS entity ,
684 fnd_message.get_string('PO','ITEM_CATEGORY_BOTH_EXIST') AS msg
685 FROM po_approved_supplier_list_gt ASLGT
686 WHERE ASLGT.item_id IS NOT NULL
687 AND ASLGT.category_id IS NOT NULL
688
689 UNION ALL
690 --Reject records if item and category both empty
691 SELECT user_key ,
692 'po_approved_supplier_list_gt' AS entity ,
693 fnd_message.get_string('PO','ITEM_CATEGORY_BOTH_EMPTY') AS msg
694 FROM po_approved_supplier_list_gt ASLGT
695 WHERE (ASLGT.item_id IS NULL
696 AND ASLGT.category_id IS NULL)
697 OR (Trim(ASLGT.item_id) IS NULL
698 AND Trim(ASLGT.category_id) IS NULL)
699
700 UNION ALL
701 --Reject records if vendor business type is empty or null
702 SELECT user_key ,
703 'po_approved_supplier_list_gt' AS entity ,
704 fnd_message.get_string('PO','INVALID_BUSINESS_TYPE') AS msg
705 FROM po_approved_supplier_list_gt ASLGT
706 WHERE ASLGT.vendor_business_type IS NULL
707 OR Upper(ASLGT.vendor_business_type) NOT IN
708 (SELECT lookup_code
709 FROM po_lookup_codes
710 WHERE lookup_type = 'ASL_VENDOR_BUSINESS_TYPE')
711
712 UNION ALL
713 --Reject records if vendor_id is empty
714 SELECT user_key ,
715 'po_approved_supplier_list_gt' AS entity ,
716 fnd_message.get_string('PO','VENDOR_EMPTY') AS msg
717 FROM po_approved_supplier_list_gt ASLGT
718 WHERE ASLGT.vendor_id IS NULL
719 AND Upper(vendor_business_type) <> 'MANUFACTURER'
720
721 UNION ALL
722 --Reject records if status_id is empty
723 SELECT user_key ,
724 'po_approved_supplier_list_gt' AS entity ,
725 fnd_message.get_string('PO','STATUS_EMPTY') AS msg
726 FROM po_approved_supplier_list_gt ASLGT
727 WHERE ASLGT.asl_status_id IS NULL
728 );
729
730 l_progress := 30;
731
732 SELECT user_key ,
733 entity ,
734 msg
735 BULK COLLECT INTO
736 l_user_key_tbl1 ,
737 l_entity_name1 ,
738 l_reject_reason1
739 FROM (
740 --Reject records if manufacturer_asl_id is empty when business type is
741 --'DISTRIBUTOR'
742 SELECT user_key ,
743 'po_approved_supplier_list_gt' AS entity ,
744 fnd_message.get_string('PO','MANUFACTURER_ASL_MANDATORY') AS msg
745 FROM po_approved_supplier_list_gt ASLGT
746 WHERE ASLGT.manufacturer_asl_id IS NULL
747 AND Upper(ASLGT.vendor_business_type) = 'DISTRIBUTOR'
748
749 UNION ALL
750 --Reject records if review date entered and is past date
751 SELECT user_key ,
752 'po_approved_supplier_list_gt' AS entity ,
753 fnd_message.get_string('PO','INVALID_REVIEW_DATE') AS msg
754 FROM po_approved_supplier_list_gt ASLGT
755 WHERE ASLGT.review_by_date IS NOT NULL
756 AND ASLGT.review_by_date < SYSDATE
757
758 UNION ALL
759 --During update ASL, business type can't be editable if the value is DB is
760 --'MANUFACTURER'
761 SELECT user_key ,
762 'po_approved_supplier_list_gt' AS entity ,
763 fnd_message.get_string('PO','BUSINESS_TYPE_NOT_EDITABLE') AS msg
764 FROM po_approved_supplier_list_gt ASLGT
765 WHERE ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
766 AND
767 EXISTS (SELECT asl_id
768 FROM po_approved_supplier_list ASL
769 WHERE ASL.asl_id = ASLGT.asl_id
770 AND Upper(ASL.vendor_business_type) = 'MANUFACTURER')
771 AND Upper(ASLGT.vendor_business_type) <> 'MANUFACTURER'
772
773 UNION ALL
774 --Reject records if business type is MANUFACTURER and manufacter id doesn't
775 --exist also reject if vendor exists for the same case
776 SELECT user_key ,
777 'po_approved_supplier_list_gt' AS entity ,
778 fnd_message.get_string('PO','VENDOR_INVALID_EXP_MANUFACTUR') AS msg
779 FROM po_approved_supplier_list_gt ASLGT
780 WHERE Upper(ASLGT.vendor_business_type) = 'MANUFACTURER'
781 AND (ASLGT.manufacturer_id IS NULL
782 OR ASLGT.vendor_id IS NOT NULL)
783 );
784
785 l_progress := 80;
786
787 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
788 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
789 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
790
791 SELECT user_key ,
792 entity ,
793 msg
794 BULK COLLECT INTO
795 l_user_key_tbl ,
796 l_entity_name ,
797 l_reject_reason
798 FROM (
799 --Reject records when business type is MANUFACTURER and if there are any
800 --child records exist
801 SELECT user_key ,
802 'po_approved_supplier_list_gt' AS entity ,
803 fnd_message.get_string('PO','MANUFAC_INVALID_AUTH_ENTRY') AS msg
804 FROM po_approved_supplier_list_gt ASLGT
805 WHERE Upper(ASLGT.vendor_business_type) = 'MANUFACTURER'
806 AND
807 (EXISTS
808 (SELECT 1
809 FROM po_asl_attributes_gt PAA
810 WHERE PAA.asl_id = ASLGT.asl_id
811 AND PAA.user_key = ASLGT.user_key)
812 OR
813 EXISTS
814 (SELECT 1
815 FROM chv_authorizations_gt CHV
816 WHERE CHV.reference_id = ASLGT.asl_id
817 AND CHV.user_key = ASLGT.user_key)
818 OR
819 EXISTS
820 (SELECT 1
821 FROM po_asl_documents_gt PAD
822 WHERE PAD.asl_id = ASLGT.asl_id
823 AND PAD.user_key = ASLGT.user_key)
824 OR
825 EXISTS
826 (SELECT 1
827 FROM po_supplier_item_capacity_gt PSIC
828 WHERE PSIC.asl_id = ASLGT.asl_id
829 AND PSIC.user_key = ASLGT.user_key)
830 OR
831 EXISTS
832 (SELECT 1
833 FROM po_supplier_item_tolerance_gt PSIT
834 WHERE PSIT.asl_id = ASLGT.asl_id
835 AND PSIT.user_key = ASLGT.user_key)));
836
837 l_progress := 90;
838
839 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
840 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
841 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
842 --call po_asl_api_pvt.reject_asl_record for above rejected records
843 po_asl_api_pvt.log('validate_asl_gt: reject count:' || l_user_key_tbl1.Count);
844 IF l_user_key_tbl1.Count > 0
845 THEN
846 po_asl_api_pvt.reject_asl_record(
847 p_user_key_tbl => l_user_key_tbl1 ,
848 p_rejection_reason=> l_reject_reason1,
849 p_entity_name => l_entity_name1 ,
850 p_session_key => g_session_key ,
851 x_return_status => x_return_status ,
852 x_return_msg => x_return_msg
853 );
854 END IF;
855
856 l_progress := 100;
857 po_asl_api_pvt.log('END ::: po_asl_api_grp.validate_asl_gt');
858
859 EXCEPTION
860
861 WHEN OTHERS THEN
862
863 po_asl_api_pvt.log('validate_asl_gt : when others exception at '
864 || l_progress || ';' || SQLERRM );
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 x_return_msg := SQLERRM;
867
868 END validate_asl_gt;
869
870
871 --------------------------------------------------------------------------------
872 --Start of Comments
873
874 --Name: validate_asl_attr_gt
875
876 --Function:
877 -- This will validate data in po_asl_attributes_gt table
878
879 --Parameters:
880
881 --OUT:
882 -- x_return_status VARCHAR2
883 -- x_return_msg VARCHAR2
884
885 --End of Comments
886 --------------------------------------------------------------------------------
887
888 PROCEDURE validate_asl_attr_gt(
889 x_return_status OUT NOCOPY VARCHAR2
890 , x_return_msg OUT NOCOPY VARCHAR2
891 )
892 IS
893 l_progress NUMBER := 0;
894 l_msg_count NUMBER;
895 l_msg_data VARCHAR2(2000);
896 l_validation_error_name VARCHAR2(30) ;
897
898 l_user_key_tbl po_tbl_number;
899 l_entity_name po_tbl_varchar30;
900 l_reject_reason po_tbl_varchar2000;
901 l_user_key_tbl1 po_tbl_number;
902 l_entity_name1 po_tbl_varchar30;
903 l_reject_reason1 po_tbl_varchar2000;
904
905 BEGIN
906 po_asl_api_pvt.log('START ::: po_asl_api_grp.validate_asl_attr_gt');
907 SELECT user_key ,
908 entity ,
909 msg
910 BULK COLLECT INTO
911 l_user_key_tbl ,
912 l_entity_name ,
913 l_reject_reason
914 FROM (
915 --Reject records if item or site is null and country code is not null
916 SELECT user_key ,
917 'po_asl_attributes_gt' AS entity ,
918 fnd_message.get_string('PO','COUNTRY_CODE_NOT_EMPTY') AS msg
919 FROM po_asl_attributes_gt PAAGT
920 WHERE (PAAGT.item_id IS NULL
921 OR PAAGT.vendor_site_id IS NULL)
922 AND PAAGT.country_of_origin_code_dsp IS NOT NULL
923
924 UNION ALL
925 --Reject records if Purchasing UOM empty and enable_plan_schedule_flag or
926 --enable_ship_schedule_flag is checked
927 SELECT user_key ,
928 'po_asl_attributes_gt' AS entity ,
929 fnd_message.get_string('PO','PURCHASING_UOM_MANDATORY') AS msg
930 FROM po_asl_attributes_gt PAAGT
931 WHERE (PAAGT.enable_plan_schedule_flag_dsp = 'Y'
932 OR PAAGT.enable_ship_schedule_flag_dsp = 'Y')
933 AND (PAAGT.purchasing_unit_of_measure_dsp IS NULL
934 OR Trim(PAAGT.purchasing_unit_of_measure_dsp) = '')
935
936 UNION ALL
937 --Reject records if enable_autoschedule_flag is Y when
938 --enable_plan_schedule_flag_dsp and enable_ship_schedule_flag_dsp
939 --are unchecked.
940 SELECT user_key ,
941 'po_asl_attributes_gt' AS entity ,
942 fnd_message.get_string('PO','INVALID_AUTOSCHEDULE_FLAG') AS msg
943 FROM po_asl_attributes_gt PAAGT
944 WHERE Nvl(PAAGT.enable_plan_schedule_flag_dsp,'N') <> 'Y'
945 AND Nvl(PAAGT.enable_ship_schedule_flag_dsp,'N') <> 'Y'
946 AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
947
948 UNION ALL
949 --Reject records if plan bucket pattern empty if enable_autoschedule_flag
950 --and enable_plan_schedule_flag enabled
951 SELECT user_key ,
952 'po_asl_attributes_gt' AS entity ,
953 fnd_message.get_string('PO','PLAN_BUCKET_MANDATORY') AS msg
954 FROM po_asl_attributes_gt PAAGT
955 WHERE PAAGT.enable_plan_schedule_flag_dsp = 'Y'
956 AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
957 AND PAAGT.plan_bucket_pattern_id IS NULL
958
959 UNION ALL
960 --Reject records if plan schedule type empty if enable_autoschedule_flag
961 --and enable_plan_schedule_flag enabled
962 SELECT user_key ,
963 'po_asl_attributes_gt' AS entity ,
964 fnd_message.get_string('PO','PLAN_SCHEDULE_MANDATORY') AS msg
965 FROM po_asl_attributes_gt PAAGT
966 WHERE PAAGT.enable_plan_schedule_flag_dsp = 'Y'
967 AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
968 AND (PAAGT.plan_schedule_type IS NULL
969 OR Trim(PAAGT.plan_schedule_type) = ''));
970
971 l_progress := 10;
972 l_user_key_tbl1 := l_user_key_tbl;
973 l_entity_name1 := l_entity_name;
974 l_reject_reason1 := l_reject_reason;
975
976 SELECT user_key ,
977 entity ,
978 msg
979 BULK COLLECT INTO
980 l_user_key_tbl ,
981 l_entity_name ,
982 l_reject_reason
983 FROM (
984 --Reject records if ship bucket pattern empty if enable_autoschedule_flag
985 --and enable_ship_schedule_flag enabled
986 SELECT user_key ,
987 'po_asl_attributes_gt' AS entity ,
988 fnd_message.get_string('PO','SHIP_BUCKET_MANDATORY') AS msg
989 FROM po_asl_attributes_gt PAAGT
990 WHERE PAAGT.enable_ship_schedule_flag_dsp = 'Y'
991 AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
992 AND PAAGT.ship_bucket_pattern_id IS NULL
993
994 UNION ALL
995 --Reject records if ship schedule type empty if enable_autoschedule_flag
996 --and enable_ship_schedule_flag enabled
997 SELECT user_key ,
998 'po_asl_attributes_gt' AS entity ,
999 fnd_message.get_string('PO','SHIP_SCHEDULE_MANDATORY') AS msg
1000 FROM po_asl_attributes_gt PAAGT
1001 WHERE PAAGT.enable_ship_schedule_flag_dsp = 'Y'
1002 AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
1003 AND (PAAGT.ship_schedule_type IS NULL
1004 OR Trim(PAAGT.ship_schedule_type) = '')
1005
1006 UNION ALL
1007 --Reject records if there is an entry in po_supplier_item_capacity_gt
1008 -- when global_flag is N and VMI flag is not checked
1009 SELECT PAAGT.user_key ,
1010 'po_asl_attributes_gt' AS entity ,
1011 fnd_message.get_string('PO','INVALID_SUPP_ITEM_CAP_ENTRY') AS msg
1012 FROM po_asl_attributes_gt PAAGT,
1013 po_approved_supplier_list_gt ASLGT
1014 WHERE PAAGT.user_key = ASLGT.user_key
1015 AND ASLGT.using_organization_id <> -1
1016 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1017 AND EXISTS
1018 (SELECT 1
1019 FROM po_supplier_item_capacity_gt PSIC
1020 WHERE PSIC.asl_id = PAAGT.asl_id
1021 AND PSIC.using_organization_id= PAAGT.using_organization_id
1022 AND PSIC.user_key = PAAGT.user_key
1023 AND PSIC.process_action = PO_ASL_API_PUB.g_ACTION_ADD)
1024
1025 );
1026
1027 l_progress := 20;
1028 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1029 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1030 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1031
1032 SELECT user_key ,
1033 entity ,
1034 msg
1035 BULK COLLECT INTO
1036 l_user_key_tbl ,
1037 l_entity_name ,
1038 l_reject_reason
1039 FROM (
1040 --Reject records if plan bucket pattern is not empty if
1041 --enable_autoschedule_flag or enable_plan_schedule_flag disabled
1042 SELECT user_key ,
1043 'po_asl_attributes_gt' AS entity ,
1044 fnd_message.get_string('PO','INVALID_PLAN_BUCKET') AS msg
1045 FROM po_asl_attributes_gt PAAGT
1046 WHERE (Nvl(PAAGT.enable_plan_schedule_flag_dsp, 'N') <> 'Y'
1047 OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1048 AND PAAGT.plan_bucket_pattern_id IS NOT NULL
1049
1050 UNION ALL
1051 --Reject records if plan schedule type not empty if
1052 --enable_autoschedule_flag or enable_plan_schedule_flag disabled
1053 SELECT user_key ,
1054 'po_asl_attributes_gt' AS entity ,
1055 fnd_message.get_string('PO','INVALID_PLAN_SCHEDULE') AS msg
1056 FROM po_asl_attributes_gt PAAGT
1057 WHERE (Nvl(PAAGT.enable_plan_schedule_flag_dsp, 'N') <> 'Y'
1058 OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1059 AND PAAGT.plan_schedule_type IS NOT NULL
1060
1061 UNION ALL
1062 --Reject records if ship bucket pattern not empty if
1063 --enable_autoschedule_flag or enable_ship_schedule_flag disabled
1064 SELECT user_key ,
1065 'po_asl_attributes_gt' AS entity ,
1066 fnd_message.get_string('PO','INVALID_SHIP_BUCKET') AS msg
1067 FROM po_asl_attributes_gt PAAGT
1068 WHERE (Nvl(PAAGT.enable_ship_schedule_flag_dsp, 'N') <> 'Y'
1069 OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1070 AND PAAGT.ship_bucket_pattern_id IS NOT NULL
1071
1072 UNION ALL
1073 --Reject records if ship schedule type not empty if
1074 --enable_autoschedule_flag or enable_ship_schedule_flag disabled
1075 SELECT user_key ,
1076 'po_asl_attributes_gt' AS entity ,
1077 fnd_message.get_string('PO','INVALID_SHIP_SCHEDULE') AS msg
1078 FROM po_asl_attributes_gt PAAGT
1079 WHERE (Nvl(PAAGT.enable_ship_schedule_flag_dsp, 'N') <> 'Y'
1080 OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1081 AND PAAGT.ship_schedule_type IS NOT NULL
1082 );
1083
1084 l_progress := 30;
1085 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1086 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1087 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1088
1089 SELECT user_key ,
1090 entity ,
1091 msg
1092 BULK COLLECT INTO
1093 l_user_key_tbl ,
1094 l_entity_name ,
1095 l_reject_reason
1096 FROM (
1097 --Reject records if authorization flag is not checked and
1098 --there is an entry for chv_authorizations in case of CREATE
1099 SELECT user_key ,
1100 'po_asl_attributes_gt' AS entity ,
1101 fnd_message.get_string('PO','INVALID_AUTHORIZATION_ENTRY') AS msg
1102 FROM po_asl_attributes_gt PAAGT
1103 WHERE NVL(PAAGT.enable_authorizations_flag_dsp,'N') <> 'Y'
1104 AND EXISTS
1105 (SELECT 1
1106 FROM chv_authorizations_gt CHV
1107 WHERE CHV.reference_id = PAAGT.asl_id
1108 AND CHV.using_organization_id = PAAGT.using_organization_id
1109 AND CHV.user_key = PAAGT.user_key
1110 AND CHV.process_action = PO_ASL_API_PUB.g_ACTION_ADD)
1111 AND PAAGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
1112 UNION ALL
1113 --Reject records if there is an entry in po_supplier_item_tolerance_gt
1114 -- when global_flag is N and VMI flag is not checked
1115 SELECT PAAGT.user_key ,
1116 'po_asl_attributes_gt' AS entity ,
1117 fnd_message.get_string('PO','INVALID_SUPP_ITEM_TOL_ENTRY') AS msg
1118 FROM po_asl_attributes_gt PAAGT,
1119 po_approved_supplier_list_gt ASLGT
1120 WHERE PAAGT.user_key = ASLGT.user_key
1121 AND ASLGT.using_organization_id <> -1
1122 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1123 AND EXISTS
1124 (SELECT 1
1125 FROM po_supplier_item_tolerance_gt PSIT
1126 WHERE PSIT.asl_id = PAAGT.asl_id
1127 AND PSIT.using_organization_id= PAAGT.using_organization_id
1128 AND PSIT.user_key = PAAGT.user_key
1129 AND PSIT.process_action = PO_ASL_API_PUB.g_ACTION_ADD)
1130
1131 UNION ALL
1132 --Reject records if Price Update tolerance is -ve number
1133 SELECT user_key ,
1134 'po_asl_attributes_gt' AS entity ,
1135 fnd_message.get_string('PO','INVALID_PRICE_UPDATE_TOLERANCE') AS msg
1136 FROM po_asl_attributes_gt PAAGT
1137 WHERE PAAGT.price_update_tolerance_dsp < 0);
1138
1139 l_progress := 40;
1140 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1141 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1142 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1143
1144 SELECT user_key ,
1145 entity ,
1146 msg
1147 BULK COLLECT INTO
1148 l_user_key_tbl ,
1149 l_entity_name ,
1150 l_reject_reason
1151 FROM (
1152 --Reject records if delivery calendar code is entered when global_flag is N
1153 --or VMI flag is not checked
1154 SELECT user_key ,
1155 'po_asl_attributes_gt' AS entity ,
1156 fnd_message.get_string('PO','DELIVERY_CALENDAR_NOT_NULL') AS msg
1157 FROM po_asl_attributes_gt PAAGT
1158 WHERE PAAGT.delivery_calendar_dsp IS NOT NULL
1159 AND PAAGT.using_organization_id <> -1
1160 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1161
1162 UNION ALL
1163 --Reject records if delivery calendar code is entered and not valid
1164 SELECT user_key ,
1165 'po_asl_attributes_gt' AS entity ,
1166 fnd_message.get_string('PO','INVALID_DELIVERY_CALENDAR_CODE') AS msg
1167 FROM po_asl_attributes_gt PAAGT
1168 WHERE PAAGT.delivery_calendar_dsp IS NOT NULL
1169 AND NOT EXISTS
1170 (SELECT 1
1171 FROM bom_calendars BOM
1172 WHERE Nvl(BOM.calendar_end_date, SYSDATE+1) > SYSDATE
1173 AND BOM.calendar_code = PAAGT.delivery_calendar_dsp)
1174
1175 UNION ALL
1176 --Reject records if delivery calendar code is entered when global_flag is N
1177 --or VMI flag is not checked
1178 SELECT user_key ,
1179 'po_asl_attributes_gt' AS entity ,
1180 fnd_message.get_string('PO','PROCESSING_LEAD_TIME_NOT_NULL') AS msg
1181 FROM po_asl_attributes_gt PAAGT
1182 WHERE PAAGT.processing_lead_time_dsp IS NOT NULL
1183 AND PAAGT.using_organization_id <> -1
1184 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1185
1186 UNION ALL
1187 --Reject records if Processing lead time is -ve number or zero
1188 SELECT user_key ,
1189 'po_asl_attributes_gt' AS entity ,
1190 fnd_message.get_string('PO','INVALID_PROCESSING_LEAD_TIME') AS msg
1191 FROM po_asl_attributes_gt PAAGT
1192 WHERE PAAGT.processing_lead_time_dsp <= 0);
1193
1194 l_progress := 50;
1195 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1196 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1197 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1198
1199 SELECT user_key ,
1200 entity ,
1201 msg
1202 BULK COLLECT INTO
1203 l_user_key_tbl ,
1204 l_entity_name ,
1205 l_reject_reason
1206 FROM (
1207 --Reject records if Min Order Qty is -ve number or zero
1208 SELECT user_key ,
1209 'po_asl_attributes_gt' AS entity ,
1210 fnd_message.get_string('PO','INVALID_MIN_ORDER_QTY') AS msg
1211 FROM po_asl_attributes_gt PAAGT
1212 WHERE PAAGT.min_order_qty_dsp <= 0
1213
1214 UNION ALL
1215 --Reject records if fixed lot multiple is -ve number or zero
1216 SELECT user_key ,
1217 'po_asl_attributes_gt' AS entity ,
1218 fnd_message.get_string('PO','INVALID_FIXED_LOT_MULTIPLE') AS msg
1219 FROM po_asl_attributes_gt PAAGT
1220 WHERE PAAGT.fixed_lot_multiple_dsp <= 0
1221
1222 UNION ALL
1223 --Reject records if enable_vmi_flag is checked when site is null or
1224 --ASL created for commodity or PO_THIRD_PARTY_STOCK_GRP.validate_local_asl
1225 --retunrs false
1226 SELECT user_key ,
1227 'po_asl_attributes_gt' AS entity ,
1228 fnd_message.get_string('PO','INVALID_VMI_FLAG') AS msg
1229 FROM po_asl_attributes_gt PAAGT
1230 WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
1231 AND (PAAGT.vendor_site_id IS NULL
1232 OR PAAGT.item_id IS NULL
1233 OR validate_vmi(
1234 p_item_id => PAAGT.item_id
1235 ,p_vendor_site_id => PAAGT.vendor_site_id
1236 ,p_using_organization_id => PAAGT.using_organization_id) = 'F'));
1237
1238 l_progress := 60;
1239 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1240 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1241 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1242
1243 SELECT user_key ,
1244 entity ,
1245 msg
1246 BULK COLLECT INTO
1247 l_user_key_tbl ,
1248 l_entity_name ,
1249 l_reject_reason
1250 FROM (
1251 --Reject records if vmi_flag is not checked and automatic allowed is checked
1252 SELECT user_key ,
1253 'po_asl_attributes_gt' AS entity ,
1254 fnd_message.get_string('PO','INVALID_AUTO_REPLENISH_FLAG') AS msg
1255 FROM po_asl_attributes_gt PAAGT
1256 WHERE Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1257 AND PAAGT.enable_vmi_auto_replenish_flag = 'Y'
1258
1259 UNION ALL
1260 --Reject records if vmi_flag is not checked and replenishment
1261 --method is entered
1262 SELECT user_key ,
1263 'po_asl_attributes_gt' AS entity ,
1264 fnd_message.get_string('PO','INVALID_REPLENISH_METHOD') AS msg
1265 FROM po_asl_attributes_gt PAAGT
1266 WHERE Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1267 AND PAAGT.replenishment_method IS NOT NULL
1268
1269 UNION ALL
1270 --Reject records if automatic allowed is not checked and replenishment
1271 --approval is not 'SUPPLIER_OR_BUYER'
1272 SELECT user_key ,
1273 'po_asl_attributes_gt' AS entity ,
1274 fnd_message.get_string('PO','INVALID_REPLENISH_APPROVAL') AS msg
1275 FROM po_asl_attributes_gt PAAGT
1276 WHERE Nvl(PAAGT.enable_vmi_auto_replenish_flag,'N') <> 'Y'
1277 AND PAAGT.vmi_replenishment_approval <>'SUPPLIER_OR_BUYER'
1278 AND PAAGT.vmi_replenishment_approval IS NOT NULL
1279
1280 UNION ALL
1281 --Reject records if vmi_flag, automatic allowed is checked and
1282 --replenishment approval is empty
1283 SELECT user_key ,
1284 'po_asl_attributes_gt' AS entity ,
1285 fnd_message.get_string('PO','REPLENISH_APPROVAL_REQUIRED') AS msg
1286 FROM po_asl_attributes_gt PAAGT
1287 WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
1288 AND PAAGT.enable_vmi_auto_replenish_flag = 'Y'
1289 AND PAAGT.vmi_replenishment_approval IS NULL
1290
1291
1292 UNION ALL
1293 --Reject records if vmi_flag is checked and replenishment method is empty
1294 SELECT user_key ,
1295 'po_asl_attributes_gt' AS entity ,
1296 fnd_message.get_string('PO','REPLENISH_METHOD_REQUIRED') AS msg
1297 FROM po_asl_attributes_gt PAAGT
1298 WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
1299 AND PAAGT.replenishment_method IS NULL
1300
1301 UNION ALL
1302 --Reject records if forecast horizon is not +ve integer or zero
1303 SELECT user_key ,
1304 'po_asl_attributes_gt' AS entity ,
1305 fnd_message.get_string('PO','INVALID_FORECAST_HORIZON') AS msg
1306 FROM po_asl_attributes_gt PAAGT
1307 WHERE PAAGT.forecast_horizon_dsp <= 0
1308 OR Round(PAAGT.forecast_horizon_dsp) <> PAAGT.forecast_horizon_dsp
1309 OR (PAAGT.forecast_horizon_dsp IS NOT NULL
1310 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'));
1311
1312 l_progress := 70;
1313 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1314 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1315 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1316
1317 SELECT user_key ,
1318 entity ,
1319 msg
1320 BULK COLLECT INTO
1321 l_user_key_tbl ,
1322 l_entity_name ,
1323 l_reject_reason
1324 FROM (
1325 --Reject records if VMI Min Qty is -ve number or is entered when
1326 --vmi_flag is not checked or replenishment method is 2/4
1327 SELECT user_key ,
1328 'po_asl_attributes_gt' AS entity ,
1329 fnd_message.get_string('PO','INVALID_VIM_MIN_QTY') AS msg
1330 FROM po_asl_attributes_gt PAAGT
1331 WHERE PAAGT.vmi_min_qty_dsp < 0
1332 OR (PAAGT.vmi_min_qty_dsp <> 0
1333 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
1334 OR (PAAGT.vmi_min_qty_dsp <> 0
1335 AND PAAGT.replenishment_method IN (2,4))
1336
1337 UNION ALL
1338 --Reject records if VMI Max Qty is -ve number or is entered when
1339 --vmi flag is not checked or replenishment method is 2/3/4 or
1340 --this qty is less than vmi min qty
1341 SELECT user_key ,
1342 'po_asl_attributes_gt' AS entity ,
1343 fnd_message.get_string('PO','INVALID_VIM_MAX_QTY') AS msg
1344 FROM po_asl_attributes_gt PAAGT
1345 WHERE PAAGT.vmi_max_qty_dsp < 0
1346 OR (PAAGT.vmi_max_qty_dsp < PAAGT.vmi_min_qty_dsp)
1347 OR (PAAGT.vmi_max_qty_dsp <> 0
1348 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
1349 OR (PAAGT.vmi_max_qty_dsp <> 0 AND PAAGT.replenishment_method
1350 IN (2,3,4))
1351
1352 UNION ALL
1353 --Reject records if VMI Min Days is not +ve integer or is entered when
1354 --vmi flag is not checked or replenishment method is 1/3
1355 SELECT user_key ,
1356 'po_asl_attributes_gt' AS entity ,
1357 fnd_message.get_string('PO','INVALID_VIM_MIN_DAYS') AS msg
1358 FROM po_asl_attributes_gt PAAGT
1359 WHERE PAAGT.vmi_min_days_dsp < 0
1360 OR Round(PAAGT.vmi_min_days_dsp) <> PAAGT.vmi_min_days_dsp
1361 OR (PAAGT.vmi_min_days_dsp <> 0
1362 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
1363 OR (PAAGT.vmi_min_days_dsp <> 0
1364 AND PAAGT.replenishment_method IN (1,3))
1365
1366 UNION ALL
1367 --Reject records if VMI Max Days is not +ve integer or is entered when
1368 --vmi flag is not checked or replenishment method is 1/3/4
1369 SELECT user_key ,
1370 'po_asl_attributes_gt' AS entity ,
1371 fnd_message.get_string('PO','INVALID_VIM_MAXS_DAYS') AS msg
1372 FROM po_asl_attributes_gt PAAGT
1373 WHERE PAAGT.vmi_max_days_dsp < 0
1374 OR Round(PAAGT.vmi_max_days_dsp) <> PAAGT.vmi_max_days_dsp
1375 OR (PAAGT.vmi_max_days_dsp < PAAGT.vmi_min_days_dsp)
1376 OR (PAAGT.vmi_max_days_dsp <> 0
1377 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
1378 OR (PAAGT.vmi_max_days_dsp <> 0
1379 AND PAAGT.replenishment_method IN (1,3,4))
1380
1381 UNION ALL
1382 --Reject records if Fixed Order Quantity is -ve number or is entered when
1383 --vmi flag is not checked or replenishment method is 1/2
1384 SELECT user_key ,
1385 'po_asl_attributes_gt' AS entity ,
1386 fnd_message.get_string('PO','INVALID_FIXED_ORDER_QTY') AS msg
1387 FROM po_asl_attributes_gt PAAGT
1388 WHERE PAAGT.fixed_order_quantity_dsp < 0
1389 OR (PAAGT.fixed_order_quantity_dsp <> 0
1390 AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
1391 OR (PAAGT.fixed_order_quantity_dsp <> 0
1392 AND PAAGT.replenishment_method IN (1,2)));
1393
1394 l_progress := 80;
1395 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1396 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1397 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1398 SELECT user_key ,
1399 entity ,
1400 msg
1401 BULK COLLECT INTO
1402 l_user_key_tbl ,
1403 l_entity_name ,
1404 l_reject_reason
1405 FROM (
1406 --Reject records if consigned from supplier is checked when site is null or
1407 --ASL created for commodity or PO_THIRD_PARTY_STOCK_GRP.validate_local_asl
1408 --retunrs false
1409 SELECT user_key ,
1410 'po_asl_attributes_gt' AS entity ,
1411 fnd_message.get_string('PO','INVALID_CONSIGNED_FLAG') AS msg
1412 FROM po_asl_attributes_gt PAAGT
1413 WHERE PAAGT.consigned_from_supp_flag_dsp = 'Y'
1414 AND (PAAGT.vendor_site_id IS NULL
1415 OR PAAGT.item_id IS NULL
1416 OR validate_vmi(
1417 p_item_id => PAAGT.item_id
1418 ,p_vendor_site_id => PAAGT.vendor_site_id
1419 ,p_using_organization_id=>PAAGT.using_organization_id)='F')
1420
1421 UNION ALL
1422 --Reject records if Consigned billing cycle is -ve number
1423 SELECT user_key ,
1424 'po_asl_attributes_gt' AS entity ,
1425 fnd_message.get_string('PO','INVALID_CONSIGN_BILL_CYCLE') AS msg
1426 FROM po_asl_attributes_gt PAAGT
1430
1427 WHERE PAAGT.consigned_billing_cycle_dsp < 0
1428 OR (PAAGT.consigned_billing_cycle_dsp IS NOT NULL
1429 AND Nvl(PAAGT.consigned_from_supp_flag_dsp,'N') <> 'Y')
1431 UNION ALL
1432 --Reject records if Consume on Aging flag is checked when Consigned flag
1433 --is not checked
1434 SELECT user_key ,
1435 'po_asl_attributes_gt' AS entity ,
1436 fnd_message.get_string('PO','INVALID_CONSUME_AGING_FLAG') AS msg
1437 FROM po_asl_attributes_gt PAAGT
1438 WHERE PAAGT.consume_on_aging_flag_dsp = 'Y'
1439 AND Nvl(PAAGT.consigned_from_supp_flag_dsp,'N') <> 'Y'
1440
1441 UNION ALL
1442 --Reject records if ageing period is not +ve number or zero or is entered
1443 --when consume of ageing flag is not checked
1444 SELECT user_key ,
1445 'po_asl_attributes_gt' AS entity ,
1446 fnd_message.get_string('PO','INVALID_AGEING_PERIOD') AS msg
1447 FROM po_asl_attributes_gt PAAGT
1448 WHERE PAAGT.aging_period_dsp <= 0
1449 OR Round(PAAGT.aging_period_dsp) <> PAAGT.aging_period_dsp
1450 OR (Nvl(PAAGT.consume_on_aging_flag_dsp,'N') <> 'Y'
1451 AND PAAGT.aging_period_dsp IS NOT NULL)
1452
1453 UNION ALL
1454 --Reject records if vendor site is null and supplier scheduling tab details
1455 --are given
1456 SELECT user_key ,
1457 'po_asl_attributes_gt' AS entity ,
1458 fnd_message.get_string('PO','SUPPLIER_SCHEDULING_DISABLED') AS msg
1459 FROM po_asl_attributes_gt PAAGT
1460 WHERE PAAGT.vendor_site_id IS NULL
1461 AND PAAGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
1462 AND (PAAGT.enable_plan_schedule_flag_dsp = 'Y'
1463 OR PAAGT.enable_ship_schedule_flag_dsp = 'Y'
1464 OR PAAGT.enable_autoschedule_flag_dsp = 'Y'
1465 OR PAAGT.scheduler_id IS NOT NULL
1466 OR PAAGT.enable_authorizations_flag_dsp = 'Y'
1467 OR EXISTS
1468 (SELECT 1
1469 FROM chv_authorizations_gt CHV
1470 WHERE CHV.reference_id = PAAGT.asl_id
1471 AND CHV.using_organization_id = PAAGT.using_organization_id
1472 AND CHV.user_key = PAAGT.user_key))
1473
1474 );
1475
1476 l_progress := 90;
1477 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1478 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1479 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1480
1481 --call po_asl_api_pvt.reject_asl_record for above rejected records
1482 po_asl_api_pvt.log('validate_asl_attr_gt: reject count:' ||
1483 l_user_key_tbl1.Count);
1484 IF l_user_key_tbl1.Count > 0
1485 THEN
1486 po_asl_api_pvt.reject_asl_record(
1487 p_user_key_tbl => l_user_key_tbl1,
1488 p_rejection_reason => l_reject_reason1,
1489 p_entity_name => l_entity_name1,
1490 p_session_key => g_session_key,
1491 x_return_status => x_return_status,
1492 x_return_msg => x_return_msg
1493 );
1494 END IF;
1495
1496 l_progress := 100;
1497 po_asl_api_pvt.log('END ::: po_asl_api_grp.validate_asl_attr_gt');
1498
1499 EXCEPTION
1500
1501 WHEN OTHERS THEN
1502
1503 po_asl_api_pvt.log('validate_asl_attr_gt : when others exception at '
1504 || l_progress || ';' || SQLERRM );
1505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1506 x_return_msg := SQLERRM;
1507
1508 END validate_asl_attr_gt;
1509
1510 --------------------------------------------------------------------------------
1511 --Start of Comments
1512
1513 --Name: validate_asl_doc_gt
1514
1515 --Function:
1516 -- This will validate data in po_asl_documents_gt table
1517
1518 --Parameters:
1519
1520 --OUT:
1521 -- x_return_status VARCHAR2
1522 -- x_return_msg VARCHAR2
1523
1524 --End of Comments
1525 --------------------------------------------------------------------------------
1526
1527 PROCEDURE validate_asl_doc_gt(
1528 x_return_status OUT NOCOPY VARCHAR2
1529 , x_return_msg OUT NOCOPY VARCHAR2
1530 )
1531 IS
1532 l_progress NUMBER := 0;
1533
1534 l_user_key_tbl po_tbl_number;
1535 l_entity_name po_tbl_varchar30;
1536 l_reject_reason po_tbl_varchar2000;
1537
1538 BEGIN
1539 po_asl_api_pvt.log('START ::: po_asl_api_grp.validate_asl_doc_gt');
1540 SELECT user_key ,
1541 entity ,
1542 msg
1543 BULK COLLECT INTO
1544 l_user_key_tbl ,
1545 l_entity_name ,
1546 l_reject_reason
1547 FROM (
1548 --Reject records if sequence number is empty or lessthan 1
1549 SELECT user_key ,
1550 'po_asl_documents_gt' AS entity ,
1551 fnd_message.get_string('PO','INVALID_DOC_SEQUENCE') AS msg
1552 FROM po_asl_documents_gt DOCGT
1553 WHERE DOCGT.sequence_num IS NULL
1554 OR DOCGT.sequence_num < 1
1555
1556 UNION ALL
1557 --Reject records if document type is null
1558 SELECT user_key ,
1559 'po_asl_documents_gt' AS entity ,
1560 fnd_message.get_string('PO','DOC_TYPE_MANDATORY') AS msg
1561 FROM po_asl_documents_gt DOCGT
1562 WHERE DOCGT.document_type_code IS NULL
1563
1564 UNION ALL
1565 --Reject records if header id is null
1566 SELECT user_key ,
1567 'po_asl_documents_gt' AS entity ,
1568 fnd_message.get_string('PO','DOC_HEADER_MANDATORY') AS msg
1569 FROM po_asl_documents_gt DOCGT
1570 WHERE DOCGT.document_header_id IS NULL
1571
1572 UNION ALL
1573 --Reject records if document type is Not CONTRACT, 'LINE_NUM' is null
1574 SELECT user_key ,
1575 'po_asl_documents_gt' AS entity ,
1576 fnd_message.get_string('PO','LINE_NUM_MANDATORY') AS msg
1577 FROM po_asl_documents_gt DOCGT
1578 WHERE DOCGT.document_type_code <> 'CONTRACT'
1579 AND DOCGT.document_line_id IS NULL
1580 AND DOCGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
1581 );
1582
1583 l_progress := 50;
1584 --call po_asl_api_pvt.reject_asl_record for above rejected records
1585 po_asl_api_pvt.log('validate_asl_doc_gt: reject count:' ||
1586 l_user_key_tbl.Count);
1587 IF l_user_key_tbl.Count > 0
1588 THEN
1589 po_asl_api_pvt.reject_asl_record(
1590 p_user_key_tbl => l_user_key_tbl,
1591 p_rejection_reason => l_reject_reason,
1592 p_entity_name => l_entity_name,
1593 p_session_key => g_session_key,
1594 x_return_status => x_return_status,
1595 x_return_msg => x_return_msg
1596 );
1597 END if;
1598
1599 l_progress := 100;
1600 po_asl_api_pvt.log('END ::: po_asl_api_grp.validate_asl_doc_gt');
1601
1602 EXCEPTION
1603
1604 WHEN OTHERS THEN
1605
1606 po_asl_api_pvt.log('validate_asl_doc_gt : when others exception at '
1607 || l_progress || ';' || SQLERRM );
1608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1609 x_return_msg := SQLERRM;
1610
1611 END validate_asl_doc_gt;
1612
1613 --------------------------------------------------------------------------------
1614 --Start of Comments
1615
1616 --Name: validate_chv_auth_gt
1617
1618 --Function:
1619 -- This will validate data in chv_authorizations_gt table
1620
1621 --Parameters:
1622
1623 --OUT:
1624 -- x_return_status VARCHAR2
1625 -- x_return_msg VARCHAR2
1626
1627 --End of Comments
1628 --------------------------------------------------------------------------------
1629
1630 PROCEDURE validate_chv_auth_gt(
1631 x_return_status OUT NOCOPY VARCHAR2
1632 , x_return_msg OUT NOCOPY VARCHAR2
1633 )
1634 IS
1635 l_progress NUMBER := 0;
1636
1637 l_user_key_tbl po_tbl_number;
1638 l_entity_name po_tbl_varchar30;
1639 l_reject_reason po_tbl_varchar2000;
1640
1641 BEGIN
1642 po_asl_api_pvt.log('START ::: po_asl_api_grp.validate_chv_auth_gt');
1643 SELECT user_key ,
1644 entity ,
1645 msg
1646 BULK COLLECT INTO
1647 l_user_key_tbl ,
1648 l_entity_name ,
1649 l_reject_reason
1650 FROM (
1651 --Reject records if sequence number is empty or not in 1,2,3,4
1652 SELECT user_key ,
1653 'chv_authorizations_gt' AS entity ,
1654 fnd_message.get_string('PO','INVALID_AUTH_SEQUENCE') AS msg
1655 FROM chv_authorizations_gt CHVGT
1656 WHERE CHVGT.authorization_sequence_dsp IS NULL
1657 OR CHVGT.authorization_sequence_dsp NOT IN (1,2,3,4)
1658
1659 UNION ALL
1660 --Reject records if timefence days is less than 1
1661 SELECT user_key ,
1662 'chv_authorizations_gt' AS entity ,
1663 fnd_message.get_string('PO','INVALID_TIMEFENCE_DAYS') AS msg
1664 FROM chv_authorizations_gt CHVGT
1665 WHERE CHVGT.timefence_days_dsp IS NULL
1666 OR CHVGT.timefence_days_dsp <= 0
1667
1668 );
1669
1670
1671 l_progress := 50;
1672 --call po_asl_api_pvt.reject_asl_record for above rejected records
1673 po_asl_api_pvt.log('validate_chv_auth_gt: reject count:' ||
1674 l_user_key_tbl.Count);
1675 IF l_user_key_tbl.Count > 0
1676 THEN
1677 po_asl_api_pvt.reject_asl_record(
1678 p_user_key_tbl => l_user_key_tbl,
1679 p_rejection_reason => l_reject_reason,
1680 p_entity_name => l_entity_name,
1681 p_session_key => g_session_key,
1682 x_return_status => x_return_status,
1683 x_return_msg => x_return_msg
1684 );
1685 END IF;
1686
1687 l_progress := 100;
1688 po_asl_api_pvt.log('END ::: po_asl_api_grp.validate_chv_auth_gt');
1689
1690 EXCEPTION
1691
1692 WHEN OTHERS THEN
1693
1694 po_asl_api_pvt.log('validate_chv_auth_gt : when others exception at '
1695 || l_progress || ';' || SQLERRM );
1696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1697 x_return_msg := SQLERRM;
1698
1699 END validate_chv_auth_gt;
1700
1701 --------------------------------------------------------------------------------
1702 --Start of Comments
1703
1704 --Name: validate_supp_item_cap_gt
1705
1706 --Function:
1707 -- This will validate data in po_supplier_item_capacity_gt table
1708
1709 --Parameters:
1710
1711 --OUT:
1712 -- x_return_status VARCHAR2
1713 -- x_return_msg VARCHAR2
1714
1715 --End of Comments
1716 --------------------------------------------------------------------------------
1717
1718 PROCEDURE validate_supp_item_cap_gt(
1719 x_return_status OUT NOCOPY VARCHAR2
1720 , x_return_msg OUT NOCOPY VARCHAR2
1721 )
1722 IS
1723 l_progress NUMBER := 0;
1724
1725 l_user_key_tbl po_tbl_number;
1726 l_entity_name po_tbl_varchar30;
1727 l_reject_reason po_tbl_varchar2000;
1728 l_user_key_tbl1 po_tbl_number;
1729 l_entity_name1 po_tbl_varchar30;
1730 l_reject_reason1 po_tbl_varchar2000;
1731
1732 BEGIN
1733 po_asl_api_pvt.log('START ::: po_asl_api_grp.validate_supp_item_cap_gt');
1734 SELECT user_key ,
1735 entity ,
1736 msg
1737 BULK COLLECT INTO
1738 l_user_key_tbl ,
1739 l_entity_name ,
1740 l_reject_reason
1741 FROM (
1742 --Reject records if from_date is less than current date
1743 SELECT user_key ,
1744 'po_supplier_item_capacity_gt' AS entity ,
1745 fnd_message.get_string('PO','INVALID_FROM_DATE') AS msg
1746 FROM po_supplier_item_capacity_gt PSICGT
1747 WHERE (PSICGT.from_date_dsp IS NULL
1748 OR PSICGT.from_date_dsp < SYSDATE)
1749 AND PSICGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
1750
1751 UNION ALL
1752 --Reject records if to_date is less than current date or from_date
1753 SELECT user_key ,
1754 'po_supplier_item_capacity_gt' AS entity ,
1755 fnd_message.get_string('PO','INVALID_TO_DATE') AS msg
1756 FROM po_supplier_item_capacity_gt PSICGT
1757 WHERE PSICGT.to_date_dsp < SYSDATE
1758 OR PSICGT.to_date_dsp < PSICGT.from_date_dsp
1759
1760 UNION ALL
1761 --Reject records if capacity is less than 1
1762 SELECT user_key ,
1763 'po_supplier_item_capacity_gt' ,
1764 fnd_message.get_string('PO','INVALID_CAPACITY_PER_DAY') AS msg
1765 FROM po_supplier_item_capacity_gt PSICGT
1766 WHERE PSICGT.capacity_per_day_dsp IS NULL
1767 OR PSICGT.capacity_per_day_dsp <= 0);
1768
1769 l_progress := 20;
1770 l_user_key_tbl1 := l_user_key_tbl;
1771 l_entity_name1 := l_entity_name;
1772 l_reject_reason1 := l_reject_reason;
1773
1774 SELECT PSICGT.user_key ,
1775 'po_supplier_item_capacity_gt' AS entity ,
1776 fnd_message.get_string('PO','DATES_OVERLAPPED') AS msg
1777 BULK COLLECT INTO
1778 l_user_key_tbl ,
1779 l_entity_name ,
1780 l_reject_reason
1781 FROM po_supplier_item_capacity_gt PSICGT ,
1782 po_approved_supplier_list_gt ASLGT
1783 WHERE 2 <= (SELECT Count(user_key)
1784 FROM po_supplier_item_capacity_gt PSIC
1785 WHERE (PSICGT.from_date_dsp
1786 BETWEEN PSIC.from_date_dsp AND PSIC.to_date_dsp
1787 OR PSICGT.to_date_dsp
1788 BETWEEN PSIC.from_date_dsp AND PSIC.to_date_dsp)
1789 AND PSICGT.user_key = PSIC.user_key
1790 AND PSICGT.using_organization_id = PSIC.using_organization_id)
1791 AND PSICGT.user_key = ASLGT.user_key
1792 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
1793 AND PSICGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
1794
1795 l_progress := 40;
1796 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1797 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1798 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1799 --Reject records if dates overlapped when update
1800 SELECT PSICGT.user_key ,
1801 'po_supplier_item_capacity_gt' AS entity ,
1802 fnd_message.get_string('PO','DATES_OVERLAPPED') AS msg
1803 BULK COLLECT INTO
1804 l_user_key_tbl ,
1805 l_entity_name ,
1806 l_reject_reason
1807 FROM po_supplier_item_capacity_gt PSICGT ,
1808 po_approved_supplier_list_gt ASLGT
1809 WHERE EXISTS
1810 (SELECT 1
1811 FROM po_supplier_item_capacity PSIC
1812 WHERE (PSICGT.from_date_dsp
1813 BETWEEN PSIC.from_date AND PSIC.To_Date
1814 OR PSICGT.to_date_dsp
1815 BETWEEN PSIC.from_date AND PSIC.To_Date)
1816 AND PSICGT.asl_id =PSIC.asl_id
1817 AND PSICGT.using_organization_id=PSIC.using_organization_id)
1818 AND PSICGT.user_key = ASLGT.user_key
1819 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1820 AND PSICGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
1821
1822 l_progress := 60;
1823 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
1824 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
1825 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1826
1827 --call po_asl_api_pvt.reject_asl_record for above rejected records
1828 po_asl_api_pvt.log('validate_supp_item_cap_gt: reject count:' ||
1829 l_user_key_tbl1.Count);
1830 IF l_user_key_tbl1.Count > 0
1831 THEN
1832 po_asl_api_pvt.reject_asl_record(
1833 p_user_key_tbl => l_user_key_tbl1,
1834 p_rejection_reason => l_reject_reason1,
1835 p_entity_name => l_entity_name1,
1836 p_session_key => g_session_key,
1837 x_return_status => x_return_status,
1838 x_return_msg => x_return_msg
1839 );
1840 END IF;
1841
1842 l_progress := 100;
1843 po_asl_api_pvt.log('END ::: po_asl_api_grp.validate_supp_item_cap_gt');
1844
1845 EXCEPTION
1846
1847 WHEN OTHERS THEN
1848
1849 po_asl_api_pvt.log('validate_supp_item_cap_gt : when others exception at '
1850 || l_progress || ';' || SQLERRM );
1851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1852 x_return_msg := SQLERRM;
1853
1854 END validate_supp_item_cap_gt;
1855
1856 --------------------------------------------------------------------------------
1857 --Start of Comments
1858
1859 --Name: validate_supp_item_tol_gt
1860
1861 --Function:
1862 -- This will validate data in po_supplier_item_tolerance_gt table
1863
1864 --Parameters:
1865
1866 --OUT:
1867 -- x_return_status VARCHAR2
1868 -- x_return_msg VARCHAR2
1869
1870 --End of Comments
1871 --------------------------------------------------------------------------------
1872
1873 PROCEDURE validate_supp_item_tol_gt(
1874 x_return_status OUT NOCOPY VARCHAR2
1875 , x_return_msg OUT NOCOPY VARCHAR2
1876 )
1877 IS
1878 l_progress NUMBER := 0;
1879
1880 l_user_key_tbl po_tbl_number;
1881 l_entity_name po_tbl_varchar30;
1882 l_reject_reason po_tbl_varchar2000;
1883
1884 BEGIN
1885 po_asl_api_pvt.log('START ::: po_asl_api_grp.validate_supp_item_tol_gt');
1886 SELECT user_key ,
1887 entity ,
1888 msg
1889 BULK COLLECT INTO
1890 l_user_key_tbl ,
1891 l_entity_name ,
1892 l_reject_reason
1893 FROM (
1894 --Reject records if number_of_days_dsp is less than 1
1895 SELECT user_key ,
1896 'po_supplier_item_tolerance_gt' AS entity,
1897 fnd_message.get_string('PO','INVALID_NUM_OF_DAYS') AS msg
1898 FROM po_supplier_item_tolerance_gt PSITGT
1899 WHERE PSITGT.number_of_days_dsp IS NULL
1900 OR PSITGT.number_of_days_dsp <= 0
1901
1902 UNION ALL
1903 --Reject records if tolerance_dsp is less than 1
1904 SELECT user_key ,
1905 'po_supplier_item_tolerance_gt' AS entity,
1906 fnd_message.get_string('PO','INVALID_TOLERANCE') AS msg
1907 FROM po_supplier_item_tolerance_gt PSITGT
1908 WHERE PSITGT.tolerance_dsp IS NULL
1909 OR PSITGT.tolerance_dsp <= 0
1910 );
1911
1912 l_progress := 50;
1913 --call po_asl_api_pvt.reject_asl_record for above rejected records
1914 po_asl_api_pvt.log('validate_supp_item_tol_gt: reject count:' ||
1915 l_user_key_tbl.Count);
1916 IF l_user_key_tbl.Count > 0
1917 THEN
1918 po_asl_api_pvt.reject_asl_record(
1919 p_user_key_tbl => l_user_key_tbl,
1920 p_rejection_reason => l_reject_reason,
1921 p_entity_name => l_entity_name,
1922 p_session_key => g_session_key,
1923 x_return_status => x_return_status,
1924 x_return_msg => x_return_msg
1925 );
1926 END IF;
1927
1928 l_progress := 100;
1929 po_asl_api_pvt.log('END ::: po_asl_api_grp.validate_supp_item_tol_gt');
1930
1931 EXCEPTION
1932
1933 WHEN OTHERS THEN
1934
1935 po_asl_api_pvt.log('validate_supp_item_tol_gt : when others exception at '
1936 || l_progress || ';' || SQLERRM );
1937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1938 x_return_msg := SQLERRM;
1939
1940 END validate_supp_item_tol_gt;
1941
1942
1943 --------------------------------------------------------------------------------
1944 --Start of Comments
1945
1946 --Name: determine_action
1947
1948 --Function:
1949 -- This will determine the process action based on item/category, orgId,
1950 -- vendor, site.
1951
1952 --Parameters:
1953
1954 --IN:
1955 -- p_item_id NUMBER
1956 -- p_category_id NUMBER
1957 -- p_using_organization_id NUMBER
1958 -- p_vendor_id NUMBER
1959 -- p_vendor_site_id NUMBER
1960
1961 --OUT:
1962 -- x_return_status VARCHAR2
1963 -- x_return_msg VARCHAR2
1964
1965 --RETURN
1966 -- varchar2(20)
1967
1968 --End of Comments
1969 --------------------------------------------------------------------------------
1970
1971 FUNCTION determine_action(
1972 p_item_id IN NUMBER
1973 , p_category_id IN NUMBER
1974 , p_using_organization_id IN NUMBER
1975 , p_vendor_id IN NUMBER
1976 , p_vendor_site_id IN NUMBER
1977 )
1978 RETURN VARCHAR2
1979 IS
1980 l_process_action VARCHAR2(20);
1981 l_found NUMBER;
1982
1983 BEGIN
1984 po_asl_api_pvt.log('START ::: determine_action');
1985 po_asl_api_pvt.log('p_item_id' || p_item_id);
1986 po_asl_api_pvt.log('p_category_id' || p_category_id);
1987 po_asl_api_pvt.log('p_using_organization_id' || p_using_organization_id);
1988 po_asl_api_pvt.log('p_vendor_id' || p_vendor_id);
1989 po_asl_api_pvt.log('p_vendor_site_id' || p_vendor_site_id);
1990 l_found := 0;
1991 l_process_action := PO_ASL_API_PUB.g_ACTION_CREATE;
1992
1993 SELECT 1 INTO l_found
1994 FROM dual
1995 WHERE EXISTS
1996 (SELECT asl_id
1997 FROM po_approved_supplier_list PASL
1998 WHERE (PASL.item_id = p_item_id
1999 OR PASL.category_id = p_category_id)
2000 AND PASL.using_organization_id = p_using_organization_id
2001 AND PASL.vendor_id = p_vendor_id
2002 AND Nvl(PASL.vendor_site_id,-1) = Nvl(p_vendor_site_id,-1));
2003
2004 IF l_found = 1
2005 THEN
2006 l_process_action := PO_ASL_API_PUB.g_ACTION_UPDATE;
2007 END IF;
2008
2009 po_asl_api_pvt.log('process_action:' || l_process_action);
2010 po_asl_api_pvt.log('END ::: determine_action');
2011
2012 RETURN(l_process_action);
2013 EXCEPTION
2014 WHEN NO_DATA_FOUND THEN
2015 po_asl_api_pvt.log('determine_action : when NO_DATA_FOUND at '
2016 || SQLERRM );
2017 RETURN(l_process_action);
2018
2019 WHEN OTHERS THEN
2020
2021 po_asl_api_pvt.log('determine_action : when others exception at '
2022 || SQLERRM );
2023 RETURN(l_process_action);
2024
2025 END determine_action;
2026
2027 --------------------------------------------------------------------------------
2028 --Start of Comments
2029
2030 --Name: validate_vmi
2031
2032 --Function:
2033 -- This will validate the vmi flag based on item, orgId, vendor site.
2034
2035 --Parameters:
2036
2037 --IN:
2038 -- p_item_id NUMBER
2039 -- p_using_organization_id NUMBER
2040 -- p_vendor_site_id NUMBER
2041
2042 --OUT:
2043 -- x_return_status VARCHAR2
2044 -- x_return_msg VARCHAR2
2045
2046 --RETURN
2047 -- varchar2(20)
2048
2049 --End of Comments
2050 --------------------------------------------------------------------------------
2051
2052 FUNCTION validate_vmi(
2053 p_item_id IN NUMBER
2054 , p_using_organization_id IN NUMBER
2055 , p_vendor_site_id IN NUMBER
2056 )
2057 RETURN VARCHAR2
2058 IS
2059 l_result BOOLEAN;
2060 l_msg_count NUMBER;
2061 l_msg_data VARCHAR2(2000);
2062 l_validation_error_name VARCHAR2(30);
2063 l_return_status VARCHAR2(10);
2064
2065
2066 BEGIN
2067 po_asl_api_pvt.log('START ::: validate_vmi');
2068 po_asl_api_pvt.log('p_item_id:' || p_item_id);
2069 po_asl_api_pvt.log('p_using_organization_id:' || p_using_organization_id);
2070 po_asl_api_pvt.log('p_vendor_site_id:' || p_vendor_site_id);
2071
2072 IF p_using_organization_id <> -1
2073 THEN
2074 l_result := PO_THIRD_PARTY_STOCK_GRP.validate_local_asl(
2075 p_api_version => 1.0
2076 , p_init_msg_list => NULL
2077 , p_commit => NULL
2078 , p_validation_level => NULL
2079 , x_return_status => l_return_status
2080 , x_msg_count => l_msg_count
2081 , x_msg_data => l_msg_data
2082 , p_inventory_item_id => p_item_id
2083 , p_supplier_site_id => p_vendor_site_id
2084 , p_inventory_org_id => p_using_organization_id
2085 , p_validation_type => 'VMI'
2086 , x_validation_error_name => l_validation_error_name);
2087 ELSE
2088 l_result := PO_THIRD_PARTY_STOCK_GRP.validate_global_asl(
2089 p_api_version => 1.0
2090 , p_init_msg_list => NULL
2091 , p_commit => NULL
2092 , p_validation_level => NULL
2093 , x_return_status => l_return_status
2094 , x_msg_count => l_msg_count
2095 , x_msg_data => l_msg_data
2096 , p_inventory_item_id => p_item_id
2097 , p_supplier_site_id => p_vendor_site_id
2098 , p_validation_type => 'VMI'
2099 , x_validation_error_name => l_validation_error_name) ;
2100
2101 END IF;
2102
2103 IF l_result = TRUE
2104 THEN
2105 po_asl_api_pvt.log('END ::: validate_vmi -> Result:' || 'T');
2106 RETURN 'T';
2107 ELSE
2108 po_asl_api_pvt.log('END ::: validate_vmi -> Result:' || 'F');
2109 RETURN 'F';
2110 END IF;
2111
2112 EXCEPTION
2113
2114 WHEN OTHERS THEN
2115 po_asl_api_pvt.log('validate_vmi : when others exception at ' || SQLERRM );
2116 RETURN 'F';
2117
2118 END validate_vmi;
2119
2120 END PO_ASL_API_GRP;
[Home] [Help]