[Home] [Help]
PACKAGE BODY: APPS.EGO_GTIN_PVT
Source
1 PACKAGE BODY EGO_GTIN_PVT AS
2 /* $Header: EGOUCCPB.pls 120.19 2007/03/27 16:59:46 dsakalle ship $ */
3
4 g_pkg_name VARCHAR2(30) := 'EGO_UCCNET_PUBLICATION_PUB';
5 g_app_name VARCHAR2(3) := 'EGO';
6 g_current_user_id NUMBER := EGO_SCTX.Get_User_Id();
7 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
8 g_plsql_err VARCHAR2(17) := 'EGO_PLSQL_ERR';
9 g_pkg_name_token VARCHAR2(8) := 'PKG_NAME';
10 g_api_name_token VARCHAR2(8) := 'API_NAME';
11 g_sql_err_msg_token VARCHAR2(11) := 'SQL_ERR_MSG';
12 g_debug_flag VARCHAR2(1) := 'N';
13 g_bo_identifier VARCHAR2(20);
14 g_log_file VARCHAR2(240);
15 g_log_file_dir VARCHAR2(1000);
16 Debug_File UTL_FILE.FILE_TYPE;
17
18 /************************************************************************
19 * Procedure: WRITE_DEBUG_LOG
20 * Purpose : This method will write debug information to the standard debug
21 **************************************************************************/
22 PROCEDURE WRITE_DEBUG_LOG(p_message IN varchar2) IS
23 BEGIN
24 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_module => 'EGO_GTIN_PVT',
25 p_message => p_message);
26 END WRITE_DEBUG_LOG;
27
28 -------------------------------------------
29 -- Private function to check if propagation required
30 -------------------------------------------
31 FUNCTION check_propagation_allowed (p_attr_id IN NUMBER)
32 RETURN BOOLEAN IS
33 l_temp_char VARCHAR2(1);
34 BEGIN
35 SELECT 'X'
36 INTO l_temp_char
37 FROM EGO_FND_DF_COL_USGS_EXT attr_col
38 WHERE attr_col.attr_id = p_attr_id
39 AND attr_col.edit_in_hierarchy_code IN ('AP', 'LP');
40 RETURN TRUE;
41 EXCEPTION
42 WHEN OTHERS THEN
43 RETURN FALSE;
44 END check_propagation_allowed;
45
46 -------------------------------------------
47
48 FUNCTION Is_Pub_Status_Param_Expected
49 ( p_inventory_item_id IN NUMBER
50 , p_org_id IN NUMBER
51 , p_gln IN VARCHAR2
52 , p_customer_id IN NUMBER
53 , p_address_id IN NUMBER
54 ) RETURN BOOLEAN
55 IS
56 l_temp VARCHAR2(1);
57 BEGIN
58 SELECT 'X' INTO l_temp
59 FROM DUAL
60 WHERE EXISTS
61 (
62 SELECT 1
63 FROM MTL_CUSTOMER_ITEMS MCI,
64 MTL_CUSTOMER_ITEM_XREFS MCIX,
65 MTL_CROSS_REFERENCES MCR,
66 MTL_SYSTEM_ITEMS_B MSI,
67 MTL_PARAMETERS MP
68 WHERE MSI.INVENTORY_ITEM_ID = p_inventory_item_id
69 AND MSI.ORGANIZATION_ID = p_org_id
70 AND MSI.INVENTORY_ITEM_ID = MCIX.INVENTORY_ITEM_ID
71 AND MSI.ORGANIZATION_ID = MCIX.MASTER_ORGANIZATION_ID
72 AND MCIX.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID
73 AND MCI.ITEM_DEFINITION_LEVEL = 3
74 AND MCI.CUSTOMER_CATEGORY_CODE = 'UCCNET'
75 AND MCI.CUSTOMER_ID = p_customer_id
76 AND MCI.ADDRESS_ID = p_address_id
77 AND MCI.CUSTOMER_ITEM_NUMBER = MCR.CROSS_REFERENCE
78 AND MCR.CROSS_REFERENCE_TYPE = 'GTIN'
79 AND MCR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
80 AND
81 (
82 MCR.ORGANIZATION_ID IS NULL
83 OR MCR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
84 )
85 AND MCR.UOM_CODE = MSI.PRIMARY_UOM_CODE
86 AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
87 AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_id
88 );
89 RETURN(TRUE);
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 RETURN(FALSE);
93 WHEN OTHERS THEN
94 RETURN(FALSE);
95 END Is_Pub_Status_Param_Expected;
96
97
98 FUNCTION Get_Publication_Status
99 ( p_inventory_item_id IN NUMBER
100 , p_org_id IN NUMBER
101 , p_address_id IN NUMBER
102 )RETURN VARCHAR2
103 IS
104 l_gln VARCHAR2(80);
105 l_customer_id NUMBER ;
106 l_party_site_id NUMBER;
107 l_publication_status VARCHAR2(80);
108
109 BEGIN
110 --derive gln and customer_id from address_id
111
112 SELECT cust_account_id, party_site_id INTO l_customer_id, l_party_site_id
113 FROM hz_cust_acct_sites_all
114 WHERE cust_acct_site_id = p_address_id;
115
116 SELECT global_location_number INTO l_gln
117 FROM hz_party_sites
118 WHERE party_site_id = l_party_site_id;
119
120 l_publication_status := Get_Publication_Status( p_inventory_item_id,
121 p_org_id,
122 l_gln,
123 l_customer_id,
124 p_address_id );
125
126 RETURN( l_publication_status);
127
128 END Get_Publication_Status;
129
130 -------------------------------------------
131
132 FUNCTION Get_Publication_Status
133 ( p_inventory_item_id IN NUMBER
134 , p_org_id IN NUMBER
135 , p_gln IN VARCHAR2
136 , p_customer_id IN NUMBER
137 , p_address_id IN NUMBER
138 )RETURN VARCHAR2
139 IS
140
141 l_publication_status VARCHAR2(80);
142 l_publication_code VARCHAR2(30);
143 l_not_published BOOLEAN;
144 l_published BOOLEAN;
145 l_re_publish_needed BOOLEAN;
146 l_publication_in_prog BOOLEAN;
147 l_rejected BOOLEAN;
148 l_withdrawn BOOLEAN;
149 l_delisted BOOLEAN;
150 BEGIN
151
152 --hard code for testing
153 l_publication_code := Get_Publication_Status_Code( p_inventory_item_id
154 , p_org_id
155 , p_gln
156 , p_customer_id
157 , p_address_id);
158
159 IF( l_publication_code IS NULL ) THEN
160 -- no publication code found
161 l_publication_status := '';
162 ELSE
163 -- get publication status meaning
164 SELECT meaning INTO l_publication_status
165 FROM fnd_lookups
166 WHERE lookup_type = 'EGO_UCCNET_PUB_STATUS'
167 AND lookup_code = l_publication_code;
168
169 END IF;
170
171 RETURN( l_publication_status );
172 END Get_Publication_Status;
173
174
175
176 -------------------------------------------
177
178 FUNCTION Get_Publication_Status_Code
179 ( p_inventory_item_id IN NUMBER
180 , p_org_id IN NUMBER
181 , p_address_id IN NUMBER
182 )RETURN VARCHAR2
183 IS
184 l_gln VARCHAR2(80);
185 l_customer_id NUMBER ;
186 l_party_site_id NUMBER;
187 l_publication_status VARCHAR2(80);
188
189 BEGIN
190 --derive gln and customer_id from address_id
191
192 SELECT cust_account_id, party_site_id INTO l_customer_id, l_party_site_id
193 FROM hz_cust_acct_sites_all
194 WHERE cust_acct_site_id = p_address_id;
195
196 SELECT global_location_number INTO l_gln
197 FROM hz_party_sites
198 WHERE party_site_id = l_party_site_id;
199
200 l_publication_status := Get_Publication_Status_Code( p_inventory_item_id,
201 p_org_id,
202 l_gln,
203 l_customer_id,
204 p_address_id );
205
206 RETURN( l_publication_status);
207
208 END Get_Publication_Status_Code;
209
210 -------------------------------------------
211
212 FUNCTION Get_Publication_Status_Code
213 ( p_inventory_item_id IN NUMBER
214 , p_org_id IN NUMBER
215 , p_gln IN VARCHAR2
216 , p_customer_id IN NUMBER
217 , p_address_id IN NUMBER
218 )RETURN VARCHAR2
219 IS
220
221 l_publication_code VARCHAR2(30);
222 l_not_published BOOLEAN;
223 l_published BOOLEAN;
224 l_re_publish_needed BOOLEAN;
225 l_publication_in_prog BOOLEAN;
226 l_rejected BOOLEAN;
227 l_withdrawn BOOLEAN;
228 l_delisted BOOLEAN;
229 BEGIN
230
231 IF Is_Pub_Status_Param_Expected( p_inventory_item_id
232 , p_org_id
233 , p_gln
234 , p_customer_id
235 , p_address_id) = FALSE
236 THEN
237 IF Is_Delisted(p_inventory_item_id, p_org_id) THEN
238 RETURN 'DELISTED';
239 ELSE
240 RETURN NULL;
241 END IF;
242 END IF;
243 --hard code for testing
244 l_publication_code := NULL;
245
246 l_not_published := Is_Not_Published( p_inventory_item_id
247 , p_org_id
248 , p_gln
249 , p_customer_id
250 , p_address_id );
251
252
253 IF( l_not_published ) THEN
254 l_publication_code := 'NOT_PUBLISHED';
255 ELSE
256 l_publication_in_prog := Is_Publication_In_Prog( p_inventory_item_id
257 , p_org_id
258 , p_gln
259 , p_customer_id
260 , p_address_id );
261
262
263 IF(l_publication_in_prog) THEN
264 l_publication_code := 'PUBLICATION_IN_PROGRESS';
265 ELSE
266 l_rejected := Is_Rejected( p_inventory_item_id
267 , p_org_id
268 , p_gln
269 , p_customer_id
270 , p_address_id );
271 IF( l_rejected ) THEN
272 l_publication_code := 'REJECTED';
273 ELSE
274 l_delisted := Is_Delisted( p_inventory_item_id
275 , p_org_id );
276 IF( l_delisted ) THEN
277 l_publication_code := 'DELISTED';
278 ELSE
279 l_withdrawn := Is_Withdrawn( p_inventory_item_id
280 , p_org_id
281 , p_gln
282 , p_customer_id
283 , p_address_id );
284 IF( l_withdrawn) THEN
285 l_publication_code := 'WITHDRAWN';
286 ELSE
287 l_published := Is_Published( p_inventory_item_id
288 , p_org_id
289 , p_gln
290 , p_customer_id
291 , p_address_id );
292
293 IF(l_published) THEN
294 l_publication_code := 'PUBLISHED';
295 ELSE
296 l_re_publish_needed := Is_Re_Publish_Needed( p_inventory_item_id
297 , p_org_id
298 , p_gln
299 , p_customer_id
300 , p_address_id );
301
302 IF(l_re_publish_needed) THEN
303 l_publication_code := 'RE_PUBLISH_NEEDED';
304
305 END IF;
306 END IF;
307 END IF;
308 END IF;
309 END IF;
310 END IF;
311 END IF;
312
313
314 RETURN( l_publication_code );
315 END Get_Publication_Status_Code;
316
317 -------------------------------------------
318
319 FUNCTION Is_Not_Published
320 ( p_inventory_item_id IN NUMBER
321 , p_org_id IN NUMBER
322 , p_gln IN VARCHAR2
323 , p_customer_id IN NUMBER
324 , p_address_id IN NUMBER
325 ) RETURN BOOLEAN
326 IS
327 l_is_not_published BOOLEAN;
328 l_temp VARCHAR2(1);
329 BEGIN
330
331 SELECT
332 'Y'
333 INTO l_temp
334 FROM DUAL
335 WHERE
336 NOT EXISTS
337 (
338 SELECT
339 1
340 FROM EGO_UCCNET_EVENTS
341 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
342 AND ORGANIZATION_ID = p_org_id
343 AND ADDRESS_ID = p_address_id
344 AND PARENT_GTIN = 0
345 AND EVENT_TYPE = 'PUBLICATION'
346 AND EVENT_ACTION IN ( 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION', 'NEW_ITEM' )
347 AND
348 (
349 DISPOSITION_CODE <> 'FAILED'
350 OR DISPOSITION_CODE IS NULL
351 )
352 )
353 AND NOT EXISTS
354 (
355 SELECT
356 1
357 FROM EGO_UCCNET_EVENTS
358 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
359 AND ORGANIZATION_ID = p_org_id
360 AND PARENT_GTIN = 0
361 AND EVENT_TYPE = 'PUBLICATION'
362 AND EVENT_ACTION = 'DE_LIST'
363 AND DISPOSITION_CODE <> 'FAILED'
364 ); -- DELISTED
365
366 RETURN(TRUE);
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 RETURN(FALSE);
371 WHEN OTHERS THEN
372 RETURN(FALSE);
373
374 END Is_Not_Published;
375
376 -------------------------------------------
377
378 FUNCTION Is_Publication_In_Prog
379 ( p_inventory_item_id IN NUMBER
380 , p_org_id IN NUMBER
381 , p_gln IN VARCHAR2
382 , p_customer_id IN NUMBER
383 , p_address_id IN NUMBER
384 ) RETURN BOOLEAN
385 IS
386 l_is_publish_in_prog BOOLEAN;
387 l_temp VARCHAR2(1);
388 BEGIN
389
390 SELECT
391 'Y'
392 INTO l_temp
393 FROM DUAL
394 WHERE
395 EXISTS
396 (
397 SELECT
398 'Y'
399 FROM EGO_UCCNET_EVENTS
400 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
401 AND ORGANIZATION_ID = p_org_id
402 AND ADDRESS_ID = p_address_id
403 AND PARENT_GTIN = 0
404 AND EVENT_TYPE = 'PUBLICATION'
405 AND DISPOSITION_CODE IS NULL
406 )
407 AND NOT EXISTS
408 (
409 SELECT
410 1
411 FROM EGO_UCCNET_EVENTS
412 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
413 AND ORGANIZATION_ID = p_org_id
414 AND PARENT_GTIN = 0
415 AND EVENT_TYPE = 'PUBLICATION'
416 AND EVENT_ACTION = 'DE_LIST'
417 AND DISPOSITION_CODE <> 'FAILED'
418 ); -- DELISTED
419
420 RETURN(TRUE);
421
422 EXCEPTION
423 WHEN NO_DATA_FOUND THEN
424 RETURN(FALSE);
425 WHEN OTHERS THEN
426 RETURN(FALSE);
427
428 END Is_Publication_In_Prog;
429
430
431 -------------------------------------------
432
433 FUNCTION Is_Published
434 ( p_inventory_item_id IN NUMBER
435 , p_org_id IN NUMBER
436 , p_gln IN VARCHAR2
437 , p_customer_id IN NUMBER
438 , p_address_id IN NUMBER
439 ) RETURN BOOLEAN
440 IS
441 l_is_published BOOLEAN;
442 l_temp VARCHAR2(1);
443 BEGIN
444
445 SELECT
446 'Y'
447 INTO l_temp
448 FROM DUAL
449 WHERE EXISTS
450 (
451 SELECT
452 1
453 FROM EGO_UCCNET_EVENTS UE
454 WHERE UE.EVENT_ROW_ID =
455 (
456 SELECT
457 MAX(EVENT_ROW_ID)
458 FROM EGO_UCCNET_EVENTS
459 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
460 AND ORGANIZATION_ID = p_org_id
461 AND ADDRESS_ID = p_address_id
462 AND PARENT_GTIN = 0
463 AND EVENT_TYPE = 'PUBLICATION'
464 AND EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
465 AND DISPOSITION_CODE <> 'FAILED'
466 )
467 AND NOT EXISTS
468 (
469 SELECT
470 1
471 FROM EGO_UCCNET_EVENTS
472 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
473 AND ORGANIZATION_ID = p_org_id
474 AND ADDRESS_ID = p_address_id
475 AND PARENT_GTIN = 0
476 AND EVENT_TYPE = 'PUBLICATION'
477 AND
478 (
479 (
480 DISPOSITION_CODE = 'REJECTED'
481 AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
482 )-- REJECTED
483 OR DISPOSITION_CODE IS NULL -- IN-PROGRESS
484 OR
485 (
486 EVENT_ACTION = 'WITHDRAW'
487 AND DISPOSITION_CODE <> 'FAILED'
488 AND UE.EVENT_ROW_ID < EVENT_ROW_ID
489 ) -- Withdrawn
490 )
491 )
492 AND NOT EXISTS
493 (
494 SELECT
495 1
496 FROM EGO_UCCNET_EVENTS
497 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
498 AND ORGANIZATION_ID = p_org_id
499 AND PARENT_GTIN = 0
500 AND EVENT_TYPE = 'PUBLICATION'
501 AND EVENT_ACTION = 'DE_LIST'
502 AND DISPOSITION_CODE <> 'FAILED'
503 ) -- DELISTED
504 AND
505 (
506 UE.CREATION_DATE >=
507 (
508 SELECT
509 NVL(MAX(EICA.LAST_UPDATE_DATE) , TO_DATE('01-01-1998', 'MM-DD-YYYY'))
510 FROM EGO_ITEM_CUST_ATTRS_B EICA,
511 EGO_UCCNET_EVENTS EV2,
512 HZ_CUST_ACCT_SITES_ALL HCAS
513 WHERE EV2.BATCH_ID = UE.BATCH_ID
514 AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
515 AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
516 AND EV2.ADDRESS_ID = UE.ADDRESS_ID
517 AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
518 AND EICA.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
519 AND EICA.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
520 AND EICA.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
521 )
522 AND UE.CREATION_DATE >=
523 (
524 SELECT
525 NVL(MAX(TP_NEUTRAL_UPDATE_DATE), TO_DATE('01-01-1998', 'MM-DD-YYYY'))
526 FROM EGO_ITEM_GTN_ATTRS_B EGA2,
527 EGO_UCCNET_EVENTS EV2
528 WHERE EV2.BATCH_ID = UE.BATCH_ID
529 AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
530 AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
531 AND EV2.ADDRESS_ID = UE.ADDRESS_ID
532 AND EGA2.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
533 AND EGA2.ORGANIZATION_ID = EV2.ORGANIZATION_ID
534 )
535 AND UE.CREATION_DATE >=
536 (
537 SELECT
538 (
539 CASE
540 WHEN(
541 Nvl( (Max(tl.LAST_UPDATE_DATE)), ( To_Date('01-01-1990','MM-DD-YYYY')) )
542 >=
543 Nvl( (Max(b.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
544 )
545 THEN Nvl( (Max(tl.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
546 ELSE Nvl( (Max(b.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
547 END
548 ) AS LAST_UPDATE_DATE
549 FROM EGO_ITEM_TP_ATTRS_EXT_B b,
550 EGO_ITEM_TP_ATTRS_EXT_TL tl,
551 EGO_UCCNET_EVENTS EV2,
552 HZ_CUST_ACCT_SITES_ALL HCAS
553 WHERE EV2.BATCH_ID = UE.BATCH_ID
554 AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
555 AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
556 AND EV2.ADDRESS_ID = UE.ADDRESS_ID
557 AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
558 AND b.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
559 AND tl.LANGUAGE = USERENV('LANG')
560 AND b.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
561 AND b.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
562 AND b.EXTENSION_ID = tl.EXTENSION_ID
563 )
564 )
565 );
566
567 RETURN(TRUE);
568
569 EXCEPTION
570 WHEN NO_DATA_FOUND THEN
571 RETURN(FALSE);
572 WHEN OTHERS THEN
573 RETURN(FALSE);
574
575
576
577 END Is_Published;
578
579 -------------------------------------------
580
581
582 FUNCTION Is_Re_Publish_Needed
583 ( p_inventory_item_id IN NUMBER
584 , p_org_id IN NUMBER
585 , p_gln IN VARCHAR2
586 , p_customer_id IN NUMBER
587 , p_address_id IN NUMBER
588 ) RETURN BOOLEAN
589 IS
590 l_is_re_publish_needed BOOLEAN;
591 l_temp VARCHAR2(1);
592 BEGIN
593
594 SELECT
595 'Y'
596 INTO l_temp
597 FROM DUAL
598 WHERE EXISTS
599 (
600 SELECT
601 1
602 FROM EGO_UCCNET_EVENTS UE
603 WHERE UE.EVENT_ROW_ID =
604 (
605 SELECT
606 MAX(EVENT_ROW_ID)
607 FROM EGO_UCCNET_EVENTS
608 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
609 AND ORGANIZATION_ID = p_org_id
610 AND ADDRESS_ID = p_address_id
611 AND PARENT_GTIN = 0
612 AND EVENT_TYPE = 'PUBLICATION'
613 AND EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
614 AND DISPOSITION_CODE <> 'FAILED'
615 )
616 AND NOT EXISTS
617 (
618 SELECT
619 1
620 FROM EGO_UCCNET_EVENTS
621 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
622 AND ORGANIZATION_ID = p_org_id
623 AND ADDRESS_ID = p_address_id
624 AND PARENT_GTIN = 0
625 AND EVENT_TYPE = 'PUBLICATION'
626 AND
627 (
628 (
629 DISPOSITION_CODE = 'REJECTED'
630 AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
631 )-- REJECTED
632 OR DISPOSITION_CODE IS NULL -- IN-PROGRESS
633 OR
634 (
635 EVENT_ACTION = 'WITHDRAW'
636 AND DISPOSITION_CODE <> 'FAILED'
637 AND UE.EVENT_ROW_ID < EVENT_ROW_ID
638 ) -- Withdrawn
639 )
640 )
641 AND NOT EXISTS
642 (
643 SELECT
644 1
645 FROM EGO_UCCNET_EVENTS
646 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
647 AND ORGANIZATION_ID = p_org_id
648 AND PARENT_GTIN = 0
649 AND EVENT_TYPE = 'PUBLICATION'
650 AND EVENT_ACTION = 'DE_LIST'
651 AND DISPOSITION_CODE <> 'FAILED'
652 ) -- DELISTED
653 AND
654 (
655 UE.CREATION_DATE <
656 (
657 SELECT
658 NVL(MAX(EICA.LAST_UPDATE_DATE) , TO_DATE('01-01-1998', 'MM-DD-YYYY'))
659 FROM EGO_ITEM_CUST_ATTRS_B EICA,
660 EGO_UCCNET_EVENTS EV2,
661 HZ_CUST_ACCT_SITES_ALL HCAS
662 WHERE EV2.BATCH_ID = UE.BATCH_ID
663 AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
664 AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
665 AND EV2.ADDRESS_ID = UE.ADDRESS_ID
666 AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
667 AND EICA.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
668 AND EICA.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
669 AND EICA.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
670 )
671 OR UE.CREATION_DATE <
672 (
673 SELECT
674 NVL(MAX(TP_NEUTRAL_UPDATE_DATE), TO_DATE('01-01-1998', 'MM-DD-YYYY'))
675 FROM EGO_ITEM_GTN_ATTRS_B EGA2,
676 EGO_UCCNET_EVENTS EV2
677 WHERE EV2.BATCH_ID = UE.BATCH_ID
678 AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
679 AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
680 AND EV2.ADDRESS_ID = UE.ADDRESS_ID
681 AND EGA2.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
682 AND EGA2.ORGANIZATION_ID = EV2.ORGANIZATION_ID
683 )
684 OR UE.CREATION_DATE <
685 (
686 SELECT
687 (
688 CASE
689 WHEN(
690 Nvl( (Max(tl.LAST_UPDATE_DATE)), ( To_Date('01-01-1990','MM-DD-YYYY')) )
691 >=
692 Nvl( (Max(b.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
693 )
694 THEN Nvl( (Max(tl.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
695 ELSE Nvl( (Max(b.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
696 END
697 ) AS LAST_UPDATE_DATE
698 FROM EGO_ITEM_TP_ATTRS_EXT_B b,
699 EGO_ITEM_TP_ATTRS_EXT_TL tl,
700 EGO_UCCNET_EVENTS EV2,
701 HZ_CUST_ACCT_SITES_ALL HCAS
702 WHERE EV2.BATCH_ID = UE.BATCH_ID
703 AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
704 AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
705 AND EV2.ADDRESS_ID = UE.ADDRESS_ID
706 AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
707 AND b.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
708 AND tl.LANGUAGE = USERENV('LANG')
709 AND b.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
710 AND b.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
711 AND b.EXTENSION_ID = tl.EXTENSION_ID
712 )
713 )
714 );
715
716 RETURN(TRUE);
717
718 EXCEPTION
719 WHEN NO_DATA_FOUND THEN
720 RETURN(FALSE);
721 WHEN OTHERS THEN
722 RETURN(FALSE);
723
724
725
726 END Is_Re_Publish_Needed;
727
728 --------------------------------
729
730
731 FUNCTION Is_Withdrawn
732 ( p_inventory_item_id IN NUMBER
733 , p_org_id IN NUMBER
734 , p_gln IN VARCHAR2
735 , p_customer_id IN NUMBER
736 , p_address_id IN NUMBER
737 ) RETURN BOOLEAN
738 IS
739 l_temp VARCHAR2(1);
740 BEGIN
741
742 SELECT
743 'Y'
744 INTO l_temp
745 FROM DUAL
746 WHERE EXISTS
747 (
748 SELECT
749 1
750 FROM EGO_UCCNET_EVENTS UE
751 WHERE UE.EVENT_ROW_ID =
752 (
753 SELECT
754 MAX(EVENT_ROW_ID)
755 FROM EGO_UCCNET_EVENTS
756 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
757 AND ORGANIZATION_ID = p_org_id
758 AND ADDRESS_ID = p_address_id
759 AND PARENT_GTIN = 0
760 AND EVENT_TYPE = 'PUBLICATION'
761 AND EVENT_ACTION = 'WITHDRAW'
762 AND DISPOSITION_CODE <> 'FAILED'
763 )
764 AND NOT EXISTS
765 (
766 SELECT
767 1
768 FROM EGO_UCCNET_EVENTS
769 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
770 AND ORGANIZATION_ID = p_org_id
771 AND ADDRESS_ID = p_address_id
772 AND PARENT_GTIN = 0
773 AND EVENT_TYPE = 'PUBLICATION'
774 AND
775 (
776 (
777 DISPOSITION_CODE = 'REJECTED'
778 AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
779 )-- REJECTED
780 OR DISPOSITION_CODE IS NULL -- IN-PROGRESS
781 OR
782 (
783 EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
784 AND DISPOSITION_CODE <> 'FAILED'
785 AND UE.EVENT_ROW_ID < EVENT_ROW_ID
786 )
787 )
788 )
789 AND NOT EXISTS
790 (
791 SELECT
792 1
793 FROM EGO_UCCNET_EVENTS
794 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
795 AND ORGANIZATION_ID = p_org_id
796 AND PARENT_GTIN = 0
797 AND EVENT_TYPE = 'PUBLICATION'
798 AND EVENT_ACTION = 'DE_LIST'
799 AND DISPOSITION_CODE <> 'FAILED'
800 ) -- DELISTED
801 );
802
803 RETURN(TRUE);
804
805 EXCEPTION
806 WHEN NO_DATA_FOUND THEN
807 RETURN(FALSE);
808 WHEN OTHERS THEN
809 RETURN(FALSE);
810
811
812
813 END Is_Withdrawn;
814
815 --------------------------------
816
817 FUNCTION Is_Rejected
818 ( p_inventory_item_id IN NUMBER
819 , p_org_id IN NUMBER
820 , p_gln IN VARCHAR2
821 , p_customer_id IN NUMBER
822 , p_address_id IN NUMBER
823 ) RETURN BOOLEAN
824 IS
825 l_temp VARCHAR2(1);
826 BEGIN
827
828 SELECT
829 'Y'
830 INTO l_temp
831 FROM DUAL
832 WHERE EXISTS
833 (
834 SELECT
835 1
836 FROM DUAL
837 WHERE EXISTS
838 (
839 SELECT
840 1
841 FROM EGO_UCCNET_EVENTS
842 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
843 AND ORGANIZATION_ID = p_org_id
844 AND ADDRESS_ID = p_address_id
845 AND PARENT_GTIN = 0
846 AND EVENT_TYPE = 'PUBLICATION'
847 AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
848 AND DISPOSITION_CODE = 'REJECTED'
849 )
850 AND NOT EXISTS
851 (
852 SELECT
853 1
854 FROM EGO_UCCNET_EVENTS
855 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
856 AND ORGANIZATION_ID = p_org_id
857 AND PARENT_GTIN = 0
858 AND EVENT_TYPE = 'PUBLICATION'
859 AND EVENT_ACTION = 'DE_LIST'
860 AND DISPOSITION_CODE <> 'FAILED'
861 ) -- DELISTED
862 );
863
864
865 RETURN(TRUE);
866
867 EXCEPTION
868 WHEN NO_DATA_FOUND THEN
869 RETURN(FALSE);
870 WHEN OTHERS THEN
871 RETURN(FALSE);
872
873
874
875 END Is_Rejected;
876
877 --------------------------------
878
879 FUNCTION Is_Delisted
880 ( p_inventory_item_id IN NUMBER
881 , p_org_id IN NUMBER
882 ) RETURN BOOLEAN
883 IS
884 l_temp VARCHAR2(1);
885 BEGIN
886
887 SELECT
888 'Y'
889 INTO l_temp
890 FROM DUAL
891 WHERE EXISTS
892 (
893 SELECT
894 1
895 FROM DUAL
896 WHERE EXISTS
897 (
898 SELECT
899 1
900 FROM ego_uccnet_events
901 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
902 AND ORGANIZATION_ID = p_org_id
903 AND EVENT_TYPE = 'PUBLICATION'
904 AND PARENT_GTIN = 0
905 AND EVENT_ACTION = 'DE_LIST'
906 AND DISPOSITION_CODE <> 'FAILED'
907 )
908 );
909
910 RETURN(TRUE);
911
912 EXCEPTION
913 WHEN NO_DATA_FOUND THEN
914 RETURN(FALSE);
915 WHEN OTHERS THEN
916 RETURN(FALSE);
917
918
919
920 END Is_Delisted;
921
922
923 -------------------------------------------
924
925 FUNCTION Is_Reg_Status_Param_Expected
926 ( p_inventory_item_id IN NUMBER
927 , p_org_id IN NUMBER
928 ) RETURN BOOLEAN
929 IS
930 l_temp VARCHAR2(1);
931 BEGIN
932 SELECT 'X' INTO l_temp
933 FROM MTL_CROSS_REFERENCES MCR,
934 MTL_SYSTEM_ITEMS_B MSI,
935 MTL_PARAMETERS MP
936 WHERE MCR.CROSS_REFERENCE_TYPE = 'GTIN'
937 AND MCR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
938 AND NVL(MCR.ORGANIZATION_ID, MSI.ORGANIZATION_ID) = MSI.ORGANIZATION_ID
939 AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
940 AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
941 AND MCR.UOM_CODE = MSI.PRIMARY_UOM_CODE
942 AND MSI.INVENTORY_ITEM_ID = p_inventory_item_id
943 AND MSI.ORGANIZATION_ID = p_org_id
944 AND NVL(GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y';
945 RETURN(TRUE);
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 RETURN(FALSE);
949 WHEN OTHERS THEN
950 RETURN(FALSE);
951 END Is_Reg_Status_Param_Expected;
952
953
954 FUNCTION Get_Registration_Status
955 ( p_inventory_item_id IN NUMBER
956 , p_org_id IN NUMBER
957 )RETURN VARCHAR2
958 IS
959
960 l_registration_status VARCHAR2(80);
961 l_registration_code VARCHAR2(30);
962 l_not_registered BOOLEAN;
963 l_registered BOOLEAN;
964 l_re_register_needed BOOLEAN;
965 l_registration_in_prog BOOLEAN;
966 BEGIN
967
968 --hard code for testing
969 l_registration_code := Get_Registration_Status_Code(p_inventory_item_id, p_org_id);
970
971 -- this is needed due to a seeded lookup code not consistent with the
972 -- registration status code
973 IF l_registration_code = 'RE_REGISTER_NEEDED' THEN
974 l_registration_code := 'REREG_NEEDED';
975 END IF;
976
977 IF( l_registration_code IS NULL ) THEN
978 -- no registration code found
979 l_registration_status := '';
980 ELSE
981 -- get publication status meaning
982 select meaning into l_registration_status
983 from fnd_lookups
984 where lookup_type = 'EGO_UCCNET_STATUS'
985 and lookup_code = l_registration_code;
986
987 END IF;
988
989 RETURN( l_registration_status );
990 END Get_Registration_Status;
991
992 -------------------------------------------
993
994 FUNCTION Get_Registration_Status_Code
995 ( p_inventory_item_id IN NUMBER
996 , p_org_id IN NUMBER
997 )RETURN VARCHAR2
998 IS
999
1000 l_registration_code VARCHAR2(30);
1001 l_not_registered BOOLEAN;
1002 l_registered BOOLEAN;
1003 l_re_register_needed BOOLEAN;
1004 l_registration_in_prog BOOLEAN;
1005 BEGIN
1006
1007 IF Is_Reg_Status_Param_Expected(p_inventory_item_id, p_org_id) = FALSE THEN
1008 RETURN NULL;
1009 END IF;
1010
1011 --hard code for testing
1012 l_registration_code := null;
1013
1014 l_not_registered := Is_Not_Registered( p_inventory_item_id
1015 , p_org_id );
1016
1017
1018 IF( l_not_registered ) THEN
1019 l_registration_code := 'NOT_REGISTERED';
1020 ELSE
1021 l_registration_in_prog := Is_Registration_In_Prog( p_inventory_item_id
1022 , p_org_id );
1023
1024
1025 IF(l_registration_in_prog) THEN
1026 l_registration_code := 'REGISTRATION_IN_PROGRESS';
1027 ELSE
1028 l_registered := Is_Registered( p_inventory_item_id
1029 , p_org_id );
1030
1031
1032 IF(l_registered) THEN
1033 l_registration_code := 'REGISTERED';
1034 ELSE
1035 l_re_register_needed := Is_Re_Register_Needed( p_inventory_item_id
1036 , p_org_id );
1037
1038
1039 IF(l_re_register_needed) THEN
1040 l_registration_code := 'RE_REGISTER_NEEDED';
1041
1042 END IF;
1043 END IF;
1044 END IF;
1045 END IF;
1046
1047
1048 RETURN( l_registration_code );
1049 END Get_Registration_Status_Code;
1050
1051 -------------------------------------------
1052
1053
1054 FUNCTION Is_Not_Registered
1055 ( p_inventory_item_id IN NUMBER
1056 , p_org_id IN NUMBER
1057 ) RETURN BOOLEAN
1058 IS
1059 l_temp VARCHAR2(1);
1060 BEGIN
1061
1062 SELECT
1063 'Y'
1064 INTO l_temp
1065 FROM DUAL
1066 WHERE NOT EXISTS
1067 (
1068 SELECT
1069 1
1070 FROM EGO_UCCNET_EVENTS
1071 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1072 AND ORGANIZATION_ID = p_org_id
1073 AND EVENT_TYPE = 'REGISTRATION'
1074 AND
1075 (
1076 DISPOSITION_CODE <> 'FAILED'
1077 OR DISPOSITION_CODE IS NULL
1078 )
1079 );
1080
1081 RETURN(TRUE);
1082
1083 EXCEPTION
1084 WHEN NO_DATA_FOUND THEN
1085 RETURN(FALSE);
1086 WHEN OTHERS THEN
1087 RETURN(FALSE);
1088
1089 END Is_Not_Registered;
1090
1091 -------------------------------------------
1092
1093 FUNCTION Is_Registration_In_Prog
1094 ( p_inventory_item_id IN NUMBER
1095 , p_org_id IN NUMBER
1096 ) RETURN BOOLEAN
1097 IS
1098 l_temp VARCHAR2(1);
1099 BEGIN
1100
1101 SELECT
1102 'Y'
1103 INTO l_temp
1104 FROM DUAL
1105 WHERE EXISTS
1106 (
1107 SELECT
1108 1
1109 FROM EGO_UCCNET_EVENTS
1110 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1111 AND ORGANIZATION_ID = p_org_id
1112 AND EVENT_TYPE = 'REGISTRATION'
1113 AND DISPOSITION_CODE IS NULL
1114 );
1115
1116 RETURN(TRUE);
1117
1118 EXCEPTION
1119 WHEN NO_DATA_FOUND THEN
1120 RETURN(FALSE);
1121 WHEN OTHERS THEN
1122 RETURN(FALSE);
1123
1124 END Is_Registration_In_Prog;
1125
1126
1127 -------------------------------------------
1128
1129 FUNCTION Is_Registered
1130 ( p_inventory_item_id IN NUMBER
1131 , p_org_id IN NUMBER
1132 ) RETURN BOOLEAN
1133 IS
1134 l_temp VARCHAR2(1);
1135 BEGIN
1136
1137 SELECT
1138 'Y'
1139 INTO l_temp
1140 FROM DUAL
1141 WHERE EXISTS
1142 (
1143 SELECT
1144 1
1145 FROM EGO_UCCNET_EVENTS UE,
1146 EGO_ITEM_GTN_ATTRS_B GA
1147 WHERE UE.EVENT_ROW_ID =
1148 (
1149 SELECT
1150 max(EVENT_ROW_ID)
1151 FROM EGO_UCCNET_EVENTS
1152 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1153 AND ORGANIZATION_ID = p_org_id
1154 AND EVENT_TYPE = 'REGISTRATION'
1155 AND EVENT_ACTION IN ('ADD', 'CHANGE', 'CORRECT')
1156 AND DISPOSITION_CODE <> 'FAILED'
1157 )
1158 AND NOT EXISTS
1159 (
1160 SELECT
1161 1
1162 FROM EGO_UCCNET_EVENTS
1163 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1164 AND ORGANIZATION_ID = p_org_id
1165 AND EVENT_TYPE = 'REGISTRATION'
1166 AND DISPOSITION_CODE IS NULL
1167 )
1168 AND UE.INVENTORY_ITEM_ID = GA.INVENTORY_ITEM_ID
1169 AND UE.ORGANIZATION_ID = GA.ORGANIZATION_ID
1170 AND (
1171 GA.REGISTRATION_UPDATE_DATE <= UE.CREATION_DATE
1172 OR GA.REGISTRATION_UPDATE_DATE IS NULL
1173 )
1174 );
1175 RETURN(TRUE);
1176
1177 EXCEPTION
1178 WHEN NO_DATA_FOUND THEN
1179 RETURN(FALSE);
1180 WHEN OTHERS THEN
1181 RETURN(FALSE);
1182
1183
1184 END Is_Registered;
1185
1186 -------------------------------------------
1187
1188
1189 FUNCTION Is_Re_Register_Needed
1190 ( p_inventory_item_id IN NUMBER
1191 , p_org_id IN NUMBER
1192 ) RETURN BOOLEAN
1193 IS
1194 l_temp VARCHAR2(1);
1195 BEGIN
1196
1197 SELECT
1198 'Y'
1199 INTO l_temp
1200 FROM DUAL
1201 WHERE EXISTS
1202 (
1203 SELECT
1204 1
1205 FROM EGO_UCCNET_EVENTS UE,
1206 EGO_ITEM_GTN_ATTRS_B GA
1207 WHERE UE.EVENT_ROW_ID =
1208 (
1209 SELECT
1210 max(EVENT_ROW_ID)
1211 FROM EGO_UCCNET_EVENTS
1212 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1213 AND ORGANIZATION_ID = p_org_id
1214 AND EVENT_TYPE = 'REGISTRATION'
1215 AND EVENT_ACTION IN ('ADD', 'CHANGE', 'CORRECT')
1216 AND DISPOSITION_CODE <> 'FAILED'
1217 )
1218 AND NOT EXISTS
1219 (
1220 SELECT
1221 1
1222 FROM EGO_UCCNET_EVENTS
1223 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1224 AND ORGANIZATION_ID = p_org_id
1225 AND EVENT_TYPE = 'REGISTRATION'
1226 AND DISPOSITION_CODE IS NULL
1227 )
1228 AND UE.INVENTORY_ITEM_ID = GA.INVENTORY_ITEM_ID
1229 AND UE.ORGANIZATION_ID = GA.ORGANIZATION_ID
1230 AND GA.REGISTRATION_UPDATE_DATE > UE.CREATION_DATE
1231 );
1232 RETURN(TRUE);
1233
1234 EXCEPTION
1235 WHEN NO_DATA_FOUND THEN
1236 RETURN(FALSE);
1237 WHEN OTHERS THEN
1238 RETURN(FALSE);
1239
1240
1241
1242 END Is_Re_Register_Needed;
1243
1244 --------------------------------
1245
1246 FUNCTION Is_Globally_Published
1247 ( p_inventory_item_id IN NUMBER
1248 , p_org_id IN NUMBER
1249 ) RETURN VARCHAR2
1250 IS
1251 l_temp VARCHAR2(1);
1252 BEGIN
1253
1254 SELECT 'Y' into l_temp
1255 FROM DUAL
1256 WHERE EXISTS(
1257 SELECT 1 FROM EGO_UCCNET_EVENTS
1258 WHERE inventory_item_id = p_inventory_item_id
1259 AND organization_id = p_org_id
1260 AND event_type = 'PUBLICATION'
1261 AND event_action IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
1262 AND NOT (disposition_code = 'FAILED'));
1263
1264 RETURN('Y');
1265
1266 EXCEPTION
1267 WHEN NO_DATA_FOUND THEN
1268 RETURN('N');
1269 WHEN OTHERS THEN
1270 RETURN('N');
1271
1272
1273 END Is_Globally_Published;
1274
1275 /* Bug 5523228 - API validates the Unit wt and wt uom against Trade Item Descriptor */
1276 FUNCTION Validate_Unit_Wt_Uom
1277 ( p_inventory_item_id IN NUMBER
1278 , p_org_id IN NUMBER
1279 ) RETURN VARCHAR2
1280 IS
1281 l_trade_unit_desc MTL_SYSTEM_ITEMS_B.TRADE_ITEM_DESCRIPTOR%TYPE;
1282 l_ret_code VARCHAR2(100);
1283 BEGIN
1284 l_ret_code := FND_API.G_TRUE;
1285
1286 SELECT TRADE_ITEM_DESCRIPTOR
1287 INTO l_trade_unit_desc
1288 FROM MTL_SYSTEM_ITEMS_B
1289 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1290 AND ORGANIZATION_ID = p_org_id;
1291
1292 IF NVL(l_trade_unit_desc,'BASE_UNIT_OR_EACH') <> 'BASE_UNIT_OR_EACH' THEN
1293 l_ret_code := FND_API.G_FALSE;
1294 END IF;
1295 RETURN(l_ret_code);
1296 EXCEPTION
1297 WHEN NO_DATA_FOUND THEN
1298 RETURN(FND_API.G_TRUE);
1299 WHEN OTHERS THEN
1300 RETURN (FND_API.G_FALSE);
1301 END Validate_Unit_Wt_Uom;
1302
1303 --------------------------------
1304 /**
1305 * Written by Nisar to changed REGISTRATION_UPDATE_DATE when UDEX Catelog Category is updated.
1306 */
1307 PROCEDURE PROCESS_CAT_ASSIGNMENT ( p_inventory_item_id NUMBER,
1308 p_organization_id NUMBER) AS
1309 BEGIN
1310 UPDATE EGO_ITEM_GTN_ATTRS_B
1311 SET REGISTRATION_UPDATE_DATE = SYSDATE
1312 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1313 AND ORGANIZATION_ID = p_organization_id;
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 NULL;
1317 END PROCESS_CAT_ASSIGNMENT;
1318
1319
1320 /*
1321 ** Added by Devendra - for updation of REGISTRATION_UPDATE_DATE and TP_NEUTRAL_UPDATE_DATE
1322 */
1323 PROCEDURE PROCESS_ATTRIBUTE_UPDATES (p_inventory_item_id NUMBER,
1324 p_organization_id NUMBER,
1325 p_attribute_names EGO_VARCHAR_TBL_TYPE,
1326 p_commit VARCHAR2 := FND_API.G_FALSE,
1327 x_return_status OUT NOCOPY VARCHAR2,
1328 x_msg_count OUT NOCOPY NUMBER,
1329 x_msg_data OUT NOCOPY VARCHAR2) AS
1330
1331 l_reg_attr_updated BOOLEAN := FALSE;
1332 l_pub_attr_updated BOOLEAN := FALSE;
1333 l_tp_attr_updated BOOLEAN := FALSE;
1334 l_non_tp_attr_updated BOOLEAN := FALSE;
1335 l_update_last_upd_date BOOLEAN := FALSE;
1336
1337 Net_Content CONSTANT VARCHAR2(100) := 'Net_Content';
1338 Uom_Net_Content CONSTANT VARCHAR2(100) := 'Uom_Net_Content';
1339 Trade_Item_Descriptor CONSTANT VARCHAR2(100) := 'Trade_Item_Descriptor';
1340 Brand_Name CONSTANT VARCHAR2(100) := 'Brand_Name';
1341 Is_Trade_Item_A_Consumer_Unit CONSTANT VARCHAR2(100) := 'Is_Trade_Item_A_Consumer_Unit';
1342 Customer_Order_Enabled_Flag CONSTANT VARCHAR2(100) := 'CUSTOMER_ORDER_ENABLED_FLAG';
1343 Unit_Weight CONSTANT VARCHAR2(100) := 'Unit_Weight';
1344 Unit_Height CONSTANT VARCHAR2(100) := 'Unit_Height';
1345 Unit_Length CONSTANT VARCHAR2(100) := 'Unit_Length';
1346 Unit_Width CONSTANT VARCHAR2(100) := 'Unit_Width';
1347 Unit_Volume CONSTANT VARCHAR2(100) := 'Unit_Volume';
1348 Dimension_Uom_Code CONSTANT VARCHAR2(100) := 'DIMENSION_UOM_CODE';
1349 Size_Code_Value CONSTANT VARCHAR2(100) := 'Size_Code_Value';
1350 Size_Code_List_Agency CONSTANT VARCHAR2(100) := 'Size_Code_List_Agency';
1351 Effective_Date CONSTANT VARCHAR2(100) := 'Effective_Date';
1352 Eanucc_Code CONSTANT VARCHAR2(100) := 'Eanucc_Code';
1353 EANUCC_Type CONSTANT VARCHAR2(100) := 'EANUCC_Type';
1354 Retail_Brand_Owner_Gln CONSTANT VARCHAR2(100) := 'Retail_Brand_Owner_Gln';
1355 Retail_Brand_Owner_Name CONSTANT VARCHAR2(100) := 'Retail_Brand_Owner_Name';
1356 Description CONSTANT VARCHAR2(100) := 'Description';
1357 Is_Trade_Item_Info_Private CONSTANT VARCHAR2(100) := 'Is_Trade_Item_Info_Private';
1358 Description_Short CONSTANT VARCHAR2(100) := 'Description_Short'; -- Bug: 3863176
1359 Weight_Uom_Code CONSTANT VARCHAR2(100) := 'WEIGHT_UOM_CODE'; -- Bug: 3874653
1360 Delivery_Method_Indicator CONSTANT VARCHAR2(100) := 'Delivery_Method_Indicator'; -- Bug: 3990094
1361 -- Bug: 3921782
1362 -- Few registration attributes were missing, while checking for registration attributes changed or not
1363 -- added Weight_Uom_Code, Gross_Weight, Uom_Gross_Weight, Volume_Uom_Code in the list of registration attrs
1364 Gross_Weight CONSTANT VARCHAR2(100) := 'Gross_Weight';
1365 Uom_Gross_Weight CONSTANT VARCHAR2(100) := 'Uom_Gross_Weight';
1366 Volume_Uom_Code CONSTANT VARCHAR2(100) := 'VOLUME_UOM_CODE';
1367
1368 Canceled_Date CONSTANT VARCHAR2(100) := 'Canceled_Date';
1369 Discontinued_Date CONSTANT VARCHAR2(100) := 'Discontinued_Date';
1370
1371 l_action_map Bom_Rollup_Pub.Rollup_Action_Map := Bom_Rollup_Pub.G_EMPTY_ACTION_MAP;
1372 l_structure_type_name VARCHAR2(200);
1373 x_error_message VARCHAR2(2000);
1374 l_rollup_reqd BOOLEAN := FALSE;
1375 l_gtin MTL_CROSS_REFERENCES.CROSS_REFERENCE%TYPE;
1376
1377 l_start_availability_date_time EGO_ITEM_CUST_ATTRS_B.START_AVAILABILITY_DATE_TIME%TYPE;
1378 l_end_availability_date_time EGO_ITEM_CUST_ATTRS_B.END_AVAILABILITY_DATE_TIME%TYPE;
1379 l_is_trade_item_a_despatch_unt EGO_ITEM_CUST_ATTRS_B.IS_TRADE_ITEM_A_DESPATCH_UNIT%TYPE;
1380 l_is_trade_item_an_invoice_unt EGO_ITEM_CUST_ATTRS_B.IS_TRADE_ITEM_AN_INVOICE_UNIT%TYPE;
1381 l_min_trade_item_life_arr EGO_ITEM_CUST_ATTRS_B.MIN_TRADE_ITEM_LIFE_ARR%TYPE;
1382 l_order_quantity_min EGO_ITEM_CUST_ATTRS_B.ORDER_QUANTITY_MIN%TYPE;
1383 l_order_quantity_max EGO_ITEM_CUST_ATTRS_B.ORDER_QUANTITY_MAX%TYPE;
1384
1385 l_reg_attr_list EGO_VARCHAR_TBL_TYPE := EGO_VARCHAR_TBL_TYPE(Net_Content, Uom_Net_Content, Trade_Item_Descriptor, Brand_Name,
1386 Is_Trade_Item_A_Consumer_Unit, Customer_Order_Enabled_Flag,
1387 Unit_Weight, Unit_Height, Unit_Length, Unit_Width, Unit_Volume,
1388 Dimension_Uom_Code, Size_Code_Value, Size_Code_List_Agency,
1389 Effective_Date, Eanucc_Code, EANUCC_Type, Retail_Brand_Owner_Gln,
1390 Retail_Brand_Owner_Name, Description, Is_Trade_Item_Info_Private,
1391 Description_Short, Weight_Uom_Code, Gross_Weight, Uom_Gross_Weight,
1392 Volume_Uom_Code, Delivery_Method_Indicator, Canceled_Date, Discontinued_Date
1393 );
1394
1395 l_core_tp_attr_list EGO_VARCHAR_TBL_TYPE := EGO_VARCHAR_TBL_TYPE
1396 ('Shelf_Life_Days', 'Start_Availability_Date_Time', 'End_Availability_Date_Time',
1397 'Order_Quantity_Min', 'Order_Quantity_Max', 'Shippable_Item_Flag',
1398 'Invoiceable_Item_Flag');
1399 CURSOR c_customers(c_gtin IN VARCHAR2) IS
1400 SELECT ACCT_SITE.PARTY_SITE_ID AS PARTY_SITE_ID
1401 FROM MTL_CUSTOMER_ITEMS MCI, MTL_CUSTOMER_ITEM_XREFS MCIX, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1402 WHERE MCI.ITEM_DEFINITION_LEVEL = 3
1403 AND MCI.CUSTOMER_CATEGORY_CODE= 'UCCNET'
1404 AND MCI.CUSTOMER_ITEM_ID = MCIX.CUSTOMER_ITEM_ID
1405 AND MCIX.PREFERENCE_NUMBER = 1
1406 AND MCI.CUSTOMER_ITEM_NUMBER = c_gtin
1407 AND MCIX.INVENTORY_ITEM_ID = p_inventory_item_id
1408 AND MCIX.MASTER_ORGANIZATION_ID = p_organization_id
1409 AND MCI.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
1410 AND MCI.ADDRESS_ID = ACCT_SITE.CUST_ACCT_SITE_ID;
1411
1412 BEGIN
1413 write_debug_log('Entering EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1414 write_debug_log('Item ID , Org ID = '||p_inventory_item_id||','||p_organization_id);
1415 FOR i IN p_attribute_names.FIRST..p_attribute_names.LAST LOOP
1416 FOR j IN l_reg_attr_list.FIRST..l_reg_attr_list.LAST LOOP
1417 IF UPPER(p_attribute_names(i)) = UPPER(l_reg_attr_list(j)) THEN
1418 l_reg_attr_updated := TRUE;
1419 EXIT;
1420 END IF;
1421 END LOOP; -- j
1422
1423 IF l_reg_attr_updated = TRUE THEN
1424 EXIT;
1425 END IF;
1426 END LOOP; -- i
1427
1428 -- Bug: 5254856
1429 -- finding out if any TP dependant attributes are updated
1430 -- l_non_tp_attr_updated - if this is true, then we have to update the TP_NEUTRAL_UPDATE_DATE
1431 -- l_non_tp_attr_updated - if this is false and l_tp_attr_updated is true, then we need to only
1432 -- update the LAST_UPDATE_DATE for customer (which does not have this attribute value associated)
1433 FOR i IN p_attribute_names.FIRST..p_attribute_names.LAST LOOP
1434 FOR j IN l_core_tp_attr_list.FIRST..l_core_tp_attr_list.LAST LOOP
1435 IF UPPER(p_attribute_names(i)) = UPPER(l_core_tp_attr_list(j)) THEN
1436 l_tp_attr_updated := TRUE;
1437 EXIT;
1438 END IF;
1439 END LOOP; -- j
1440
1441 IF l_tp_attr_updated = FALSE THEN
1442 l_non_tp_attr_updated := TRUE;
1443 EXIT;
1444 END IF;
1445 END LOOP; -- i
1446
1447 IF l_reg_attr_updated = TRUE THEN
1448 UPDATE EGO_ITEM_GTN_ATTRS_B
1449 SET REGISTRATION_UPDATE_DATE = SYSDATE,
1450 TP_NEUTRAL_UPDATE_DATE = SYSDATE
1451 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1452 AND ORGANIZATION_ID = p_organization_id;
1453 ELSE
1454 IF p_attribute_names.COUNT > 0 AND l_non_tp_attr_updated THEN
1455 UPDATE EGO_ITEM_GTN_ATTRS_B
1456 SET TP_NEUTRAL_UPDATE_DATE = SYSDATE
1457 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1458 AND ORGANIZATION_ID = p_organization_id;
1459 ELSIF p_attribute_names.COUNT > 0 AND (l_tp_attr_updated AND (NOT l_non_tp_attr_updated) ) THEN
1460 -- getting GTIN of item
1461 BEGIN
1462 SELECT CROSS_REFERENCE INTO l_gtin
1463 FROM MTL_CROSS_REFERENCES MCR, MTL_SYSTEM_ITEMS_B MSIB
1464 WHERE MCR.CROSS_REFERENCE_TYPE = 'GTIN'
1465 AND MCR.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
1466 AND MSIB.PRIMARY_UOM_CODE = MCR.UOM_CODE
1467 AND MSIB.ORGANIZATION_ID = p_organization_id
1468 AND MSIB.INVENTORY_ITEM_ID = p_inventory_item_id;
1469 EXCEPTION WHEN NO_DATA_FOUND THEN
1470 l_gtin := NULL;
1471 END;
1472
1473 FOR i IN c_customers(l_gtin) LOOP
1474 BEGIN
1475 SELECT
1476 START_AVAILABILITY_DATE_TIME,
1477 END_AVAILABILITY_DATE_TIME,
1478 IS_TRADE_ITEM_A_DESPATCH_UNIT,
1479 IS_TRADE_ITEM_AN_INVOICE_UNIT,
1480 MIN_TRADE_ITEM_LIFE_ARR,
1481 ORDER_QUANTITY_MIN,
1482 ORDER_QUANTITY_MAX
1483 INTO
1484 l_start_availability_date_time,
1485 l_end_availability_date_time,
1486 l_is_trade_item_a_despatch_unt,
1487 l_is_trade_item_an_invoice_unt,
1488 l_min_trade_item_life_arr,
1489 l_order_quantity_min,
1490 l_order_quantity_max
1491 FROM EGO_ITEM_CUST_ATTRS_B
1492 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1493 AND MASTER_ORGANIZATION_ID = p_organization_id
1494 AND PARTY_SITE_ID = i.PARTY_SITE_ID;
1495
1496 l_update_last_upd_date := FALSE;
1497 FOR j IN p_attribute_names.FIRST..p_attribute_names.LAST LOOP
1498 write_debug_log('attr - '||UPPER(p_attribute_names(j)) );
1499 IF UPPER(p_attribute_names(j)) = 'SHELF_LIFE_DAYS' AND l_min_trade_item_life_arr IS NULL THEN
1500 l_update_last_upd_date := TRUE;
1501 ELSIF UPPER(p_attribute_names(j)) = 'START_AVAILABILITY_DATE_TIME' AND l_start_availability_date_time IS NULL THEN
1502 l_update_last_upd_date := TRUE;
1503 ELSIF UPPER(p_attribute_names(j)) = 'END_AVAILABILITY_DATE_TIME' AND l_end_availability_date_time IS NULL THEN
1504 l_update_last_upd_date := TRUE;
1505 ELSIF UPPER(p_attribute_names(j)) = 'ORDER_QUANTITY_MIN' AND l_order_quantity_min IS NULL THEN
1506 l_update_last_upd_date := TRUE;
1507 ELSIF UPPER(p_attribute_names(j)) = 'ORDER_QUANTITY_MAX' AND l_order_quantity_max IS NULL THEN
1508 l_update_last_upd_date := TRUE;
1509 ELSIF UPPER(p_attribute_names(j)) = 'SHIPPABLE_ITEM_FLAG' AND l_is_trade_item_a_despatch_unt IS NULL THEN
1510 l_update_last_upd_date := TRUE;
1511 ELSIF UPPER(p_attribute_names(j)) = 'INVOICEABLE_ITEM_FLAG' AND l_is_trade_item_an_invoice_unt IS NULL THEN
1512 l_update_last_upd_date := TRUE;
1513 END IF;
1514 END LOOP; --FOR j IN p_attribute_names.FIRST..p_attribute_names.LAST LOOP
1515
1516 IF l_update_last_upd_date THEN
1517 UPDATE EGO_ITEM_CUST_ATTRS_B
1518 SET LAST_UPDATE_DATE = SYSDATE
1519 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1520 AND MASTER_ORGANIZATION_ID = p_organization_id
1521 AND PARTY_SITE_ID = i.PARTY_SITE_ID;
1522 END IF; --IF l_update_last_upd_date THEN
1523 EXCEPTION WHEN NO_DATA_FOUND THEN
1524 INSERT INTO EGO_ITEM_CUST_ATTRS_B
1525 (
1526 EXTENSION_ID,
1527 INVENTORY_ITEM_ID,
1528 MASTER_ORGANIZATION_ID,
1529 PARTY_SITE_ID,
1530 CREATED_BY,
1531 CREATION_DATE,
1532 LAST_UPDATED_BY,
1533 LAST_UPDATE_DATE
1534 )
1535 VALUES
1536 (
1537 EGO_EXTFWK_S.NEXTVAL,
1538 p_inventory_item_id,
1539 p_organization_id,
1540 i.PARTY_SITE_ID,
1541 FND_GLOBAL.USER_ID,
1542 SYSDATE,
1543 FND_GLOBAL.USER_ID,
1544 SYSDATE
1545 );
1546 END;
1547 END LOOP; --FOR i IN c_customers(l_gtin) LOOP
1548 END IF; -- IF other attrs got updated
1549 END IF; -- IF registration attrs got updated
1550
1551 -- achampan: removed rollup call
1552 IF p_commit = FND_API.G_TRUE THEN
1553 write_debug_log('Commiting...');
1554 COMMIT;
1555 END IF;
1556
1557 x_return_status := 'S';
1558 x_msg_count := 0;
1559 x_msg_data := NULL;
1560
1561 write_debug_log('End - EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1562 EXCEPTION WHEN OTHERS THEN
1563
1564 x_return_status := 'U';
1565 x_msg_data := SQLERRM;
1566 x_msg_count := 1;
1567
1568 END PROCESS_ATTRIBUTE_UPDATES;
1569
1570 /*
1571 * This procedure is added as a part of fix for bug: 3983838
1572 * This procedure is called from User Defined attributes EO i.e. EgoMtlSyItemsExtVLEOImpl
1573 * If any Extension GDSN attributes are updated, we update the TP_NEUTRAL_UPDATE_DATE or
1574 * LAST_UPDATE_DATE of EGO_ITEM_TP_ATTRS_EXT_B, depending upon whether the Attibute group
1575 * is TP-Dependant or not.
1576 */
1577 PROCEDURE PROCESS_EXTN_ATTRIBUTE_UPDATES (p_inventory_item_id NUMBER,
1578 p_organization_id NUMBER,
1579 p_attribute_names EGO_VARCHAR_TBL_TYPE,
1580 p_attr_group_name VARCHAR2,
1581 p_commit VARCHAR2 := FND_API.G_FALSE,
1582 x_return_status OUT NOCOPY VARCHAR2,
1583 x_msg_count OUT NOCOPY NUMBER,
1584 x_msg_data OUT NOCOPY VARCHAR2) AS
1585 x_error_message VARCHAR2(2000);
1586 l_gtin MTL_CROSS_REFERENCES.CROSS_REFERENCE%TYPE;
1587 l_view_name VARCHAR2(100);
1588 l_select_columns VARCHAR2(32000);
1589 l_sql VARCHAR2(32000);
1590 l_value VARCHAR2(15000);
1591 l_extn_id NUMBER;
1592 l_attr_group_id NUMBER;
1593 l_ext_seq_val NUMBER;
1594
1595 CURSOR c_customers IS
1596 SELECT PARTY_SITE_ID
1597 FROM EGO_UCCNET_EVENTS
1598 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1599 AND ORGANIZATION_ID = p_organization_id
1600 AND EVENT_TYPE = 'PUBLICATION'
1601 AND EVENT_ACTION IN ( 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION', 'NEW_ITEM' )
1602 AND
1603 (
1604 DISPOSITION_CODE <> 'FAILED'
1605 OR DISPOSITION_CODE IS NULL
1606 )
1607 GROUP BY PARTY_SITE_ID;
1608 BEGIN
1609 write_debug_log('Entering EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1610 write_debug_log('Item ID , Org ID, Attr_Group_Name = '||p_inventory_item_id||','||p_organization_id||','||p_attr_group_name);
1611
1612 BEGIN
1613 SELECT AGV_NAME, ATTR_GROUP_ID INTO l_view_name, l_attr_group_id
1614 FROM EGO_FND_DSC_FLX_CTX_EXT
1615 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEM_TP_EXT_ATTRS'
1616 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
1617 AND APPLICATION_ID = 431;
1618 EXCEPTION WHEN NO_DATA_FOUND THEN
1619 write_debug_log('Attribute group does not belongs to EGO_ITEM_TP_EXT_ATTRS, so updating TP_NEUTRAL_UPDATE_DATE');
1620 -- even if a single attribute other than TP-Dependant attribute is updated, update the TP-Neutral Update date.
1621 FOR i IN p_attribute_names.FIRST..p_attribute_names.LAST LOOP
1622 UPDATE EGO_ITEM_GTN_ATTRS_B
1623 SET TP_NEUTRAL_UPDATE_DATE = SYSDATE
1624 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1625 AND ORGANIZATION_ID = p_organization_id;
1626
1627 EXIT;
1628 END LOOP;
1629 write_debug_log('Exiting EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1630 x_return_status := 'S';
1631 x_msg_count := 0;
1632 x_msg_data := NULL;
1633 RETURN;
1634 END;
1635
1636 FOR i IN p_attribute_names.FIRST..p_attribute_names.LAST LOOP
1637 l_select_columns := l_select_columns || UPPER(p_attribute_names(i)) || '||';
1638 END LOOP; -- i
1639
1640 l_select_columns := RTRIM(l_select_columns, '||');
1641
1642 l_sql := ' SELECT '||l_select_columns||' ,EXTENSION_ID FROM '||l_view_name||
1643 ' WHERE INVENTORY_ITEM_ID = :1 AND MASTER_ORGANIZATION_ID = :2 AND PARTY_SITE_ID = :3 AND ROWNUM = 1';
1644
1645 write_debug_log('l_sql = '||l_sql);
1646
1647 FOR i IN c_customers LOOP
1648 BEGIN
1649 write_debug_log('i.PARTY_SITE_ID = '||i.PARTY_SITE_ID);
1650 EXECUTE IMMEDIATE l_sql INTO l_value, l_extn_id USING p_inventory_item_id, p_organization_id, i.PARTY_SITE_ID;
1651
1652 IF l_value IS NULL THEN
1653 write_debug_log('Value is null l_extn_id= '||l_extn_id);
1654 UPDATE EGO_ITEM_TP_ATTRS_EXT_B
1655 SET LAST_UPDATE_DATE = SYSDATE
1656 WHERE EXTENSION_ID = l_extn_id;
1657 END IF;
1658 write_debug_log('Value is not null l_extn_id= '||l_extn_id);
1659 EXCEPTION WHEN NO_DATA_FOUND THEN
1660 write_debug_log('No Data Found - inserting');
1661 SELECT EGO_EXTFWK_S.NEXTVAL INTO l_ext_seq_val FROM DUAL;
1662
1663 INSERT INTO EGO_ITEM_TP_ATTRS_EXT_B
1664 (
1665 EXTENSION_ID,
1666 INVENTORY_ITEM_ID,
1667 MASTER_ORGANIZATION_ID,
1668 PARTY_SITE_ID,
1669 ATTR_GROUP_ID,
1670 CREATED_BY,
1671 CREATION_DATE,
1672 LAST_UPDATED_BY,
1673 LAST_UPDATE_DATE
1674 )
1675 VALUES
1676 (
1677 l_ext_seq_val,
1678 p_inventory_item_id,
1679 p_organization_id,
1680 i.PARTY_SITE_ID,
1681 l_attr_group_id,
1682 FND_GLOBAL.USER_ID,
1683 SYSDATE,
1684 FND_GLOBAL.USER_ID,
1685 SYSDATE
1686 );
1687
1688 INSERT INTO EGO_ITEM_TP_ATTRS_EXT_TL
1689 (
1690 EXTENSION_ID,
1691 INVENTORY_ITEM_ID,
1692 MASTER_ORGANIZATION_ID,
1693 PARTY_SITE_ID,
1694 ATTR_GROUP_ID,
1695 LANGUAGE,
1696 SOURCE_LANG,
1697 CREATED_BY,
1698 CREATION_DATE,
1699 LAST_UPDATED_BY,
1700 LAST_UPDATE_DATE
1701 )
1702 SELECT
1703 l_ext_seq_val,
1704 p_inventory_item_id,
1705 p_organization_id,
1706 i.PARTY_SITE_ID,
1707 l_attr_group_id,
1708 L.LANGUAGE_CODE,
1709 USERENV('LANG'),
1710 FND_GLOBAL.USER_ID,
1711 SYSDATE,
1712 FND_GLOBAL.USER_ID,
1713 SYSDATE
1714 FROM FND_LANGUAGES L
1715 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1716 AND NOT EXISTS
1717 (SELECT NULL
1718 FROM EGO_ITEM_TP_ATTRS_EXT_TL T
1719 WHERE T.EXTENSION_ID = l_ext_seq_val
1720 AND T.LANGUAGE = L.LANGUAGE_CODE);
1721 END;
1722 END LOOP; --FOR i IN c_customers(l_gtin) LOOP
1723
1724 IF p_commit = FND_API.G_TRUE THEN
1725 write_debug_log('Commiting...');
1726 COMMIT;
1727 END IF;
1728
1729 x_return_status := 'S';
1730 x_msg_count := 0;
1731 x_msg_data := NULL;
1732
1733 write_debug_log('End - EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1734 EXCEPTION WHEN OTHERS THEN
1735 x_return_status := 'U';
1736 x_msg_data := SQLERRM;
1737 x_msg_count := 1;
1738 END PROCESS_EXTN_ATTRIBUTE_UPDATES;
1739
1740 /*
1741 ** Added by Devendra - This method will be called from Items IOI.
1742 * This procedure will validate the MSI attributes for UCCnet and will call PROCESS_ATTRIBUTE_UPDATES
1743 */
1744 PROCEDURE PROCESS_UCCNET_ATTRIBUTES (P_Prog_AppId NUMBER DEFAULT -1,
1745 P_Prog_Id NUMBER DEFAULT -1,
1746 P_Request_Id NUMBER DEFAULT -1,
1747 P_User_Id NUMBER DEFAULT -1,
1748 P_Login_Id NUMBER DEFAULT -1,
1749 P_Set_id NUMBER DEFAULT -999,
1750 P_Suppress_Rollup VARCHAR2 DEFAULT 'N'
1751 )
1752 IS
1753 CURSOR c_upated_items IS
1754 SELECT
1755 inventory_item_id,
1756 organization_id,
1757 unit_length,
1758 unit_weight,
1759 unit_width,
1760 unit_height,
1761 unit_volume,
1762 dimension_uom_code,
1763 list_price_per_unit,
1764 shippable_item_flag,
1765 invoiceable_item_flag,
1766 customer_order_enabled_flag,
1767 description,
1768 rowid,
1769 transaction_id,
1770 weight_uom_code,
1771 volume_uom_code,
1772 shelf_life_days,
1773 trade_item_descriptor
1774 FROM MTL_SYSTEM_ITEMS_INTERFACE
1775 WHERE (SET_PROCESS_ID = p_set_id OR SET_PROCESS_ID = p_set_id + 1000000000000)
1776 AND TRANSACTION_TYPE IN ('UPDATE', 'AUTO_CHILD')
1777 AND PROCESS_FLAG = 4;
1778
1779 l_msib_rowid ROWID;
1780 l_unit_length MTL_SYSTEM_ITEMS.UNIT_LENGTH%TYPE := NULL;
1781 l_unit_weight MTL_SYSTEM_ITEMS.UNIT_WEIGHT%TYPE := NULL;
1782 l_unit_width MTL_SYSTEM_ITEMS.UNIT_WIDTH%TYPE := NULL;
1783 l_unit_height MTL_SYSTEM_ITEMS.UNIT_HEIGHT%TYPE := NULL;
1784 l_unit_volume MTL_SYSTEM_ITEMS.UNIT_VOLUME%TYPE := NULL;
1785 l_dimension_uom_code MTL_SYSTEM_ITEMS.DIMENSION_UOM_CODE%TYPE := NULL;
1786 l_list_price_per_unit MTL_SYSTEM_ITEMS.LIST_PRICE_PER_UNIT%TYPE := NULL;
1787 l_shippable_item_flag MTL_SYSTEM_ITEMS.SHIPPABLE_ITEM_FLAG%TYPE := NULL;
1788 l_invoiceable_item_flag MTL_SYSTEM_ITEMS.INVOICEABLE_ITEM_FLAG%TYPE := NULL;
1789 l_customer_order_enabled_flag MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_ENABLED_FLAG%TYPE := NULL;
1790 l_description MTL_SYSTEM_ITEMS.DESCRIPTION%TYPE := NULL;
1791 l_weight_uom_code MTL_SYSTEM_ITEMS.WEIGHT_UOM_CODE%TYPE := NULL; -- Bug: 3874653
1792 l_volume_uom_code MTL_SYSTEM_ITEMS.VOLUME_UOM_CODE%TYPE := NULL; -- Bug: 3921782
1793 l_shelf_life_days MTL_SYSTEM_ITEMS.SHELF_LIFE_DAYS%TYPE := NULL; -- Bug: 5254856
1794 l_trade_item_desc MTL_SYSTEM_ITEMS_B.TRADE_ITEM_DESCRIPTOR%TYPE;
1795
1796 l_attribute_names EGO_VARCHAR_TBL_TYPE := EGO_VARCHAR_TBL_TYPE(null);
1797
1798 k BINARY_INTEGER := 0;
1799 l_return_status VARCHAR2(1);
1800 l_msg_text VARCHAR2(2000);
1801 l_msg_count NUMBER;
1802 err_text VARCHAR2(2000);
1803 l_gdsn_outbound_enabled_flag VARCHAR2(1);
1804 dumm_status NUMBER;
1805 l_error BOOLEAN := FALSE;
1806 l_gross_weight NUMBER := NULL;
1807 l_msib_upd_reqd BOOLEAN := FALSE;
1808 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1809 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1810 l_data_level_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1811 l_attr_diffs EGO_USER_ATTR_DIFF_TABLE;
1812 l_error_message VARCHAR2(2000);
1813 l_item_catalog_group_id NUMBER;
1814 BEGIN
1815 write_debug_log('Entering EGO_GTIN_PVT.PROCESS_UCCNET_ATTRIBUTES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1816 FOR i IN c_upated_items LOOP
1817 write_debug_log('Item ID , Org ID = '||i.inventory_item_id||','||i.organization_id);
1818 l_error := FALSE;
1819 l_msib_upd_reqd := FALSE;
1820 l_attribute_names := EGO_VARCHAR_TBL_TYPE(null);
1821 k := 0;
1822 l_attr_diffs := EGO_USER_ATTR_DIFF_TABLE();
1823 -- find whether updated item is an UCCnet enabled item or not
1824 BEGIN
1825 SELECT GDSN_OUTBOUND_ENABLED_FLAG INTO l_gdsn_outbound_enabled_flag
1826 FROM MTL_SYSTEM_ITEMS_B
1827 WHERE INVENTORY_ITEM_ID = i.inventory_item_id
1828 AND ORGANIZATION_ID = i.organization_id;
1829 EXCEPTION
1830 WHEN NO_DATA_FOUND THEN
1831 l_gdsn_outbound_enabled_flag := NULL;
1832 END;
1833
1834 write_debug_log('l_gdsn_outbound_enabled_flag = '||l_gdsn_outbound_enabled_flag);
1835 -- if the updated item is UCCnet enabled item then only proceed
1836 IF NVL(l_gdsn_outbound_enabled_flag, 'N') = 'Y' THEN
1837 -- Bug: 3930946 - validating only if the involved attributes are changed
1838 -- fetching old attribute values
1839 BEGIN
1840 SELECT
1841 msib.ROWID,
1842 msib.unit_length,
1843 msib.unit_weight,
1844 msib.unit_width,
1845 msib.unit_height,
1846 msib.unit_volume,
1847 msib.dimension_uom_code,
1848 msib.list_price_per_unit,
1849 msib.shippable_item_flag ,
1850 msib.invoiceable_item_flag,
1851 msib.customer_order_enabled_flag,
1852 msit.description,
1853 msib.weight_uom_code,
1854 msib.volume_uom_code,
1855 msib.shelf_life_days,
1856 msib.trade_item_descriptor
1857 INTO
1858 l_msib_rowid,
1859 l_unit_length,
1860 l_unit_weight,
1861 l_unit_width,
1862 l_unit_height,
1863 l_unit_volume,
1864 l_dimension_uom_code,
1865 l_list_price_per_unit,
1866 l_shippable_item_flag,
1867 l_invoiceable_item_flag,
1868 l_customer_order_enabled_flag,
1869 l_description,
1870 l_weight_uom_code,
1871 l_volume_uom_code,
1872 l_shelf_life_days,
1873 l_trade_item_desc
1874 FROM MTL_SYSTEM_ITEMS_B msib, MTL_SYSTEM_ITEMS_TL msit
1875 WHERE msib.INVENTORY_ITEM_ID = i.inventory_item_id
1876 AND msib.ORGANIZATION_ID = i.organization_id
1877 AND msib.INVENTORY_ITEM_ID = msit.INVENTORY_ITEM_ID
1878 AND msib.ORGANIZATION_ID = msit.ORGANIZATION_ID
1879 AND msit.LANGUAGE = USERENV('LANG');
1880
1881 EXCEPTION WHEN NO_DATA_FOUND THEN
1882 NULL;
1883 END;
1884
1885 -- Removed the inter-attribute group validations from here i.e. the validation that Gross_Weight
1886 -- is required when Customer Order Enabled flag is Y. Removed this validation since we were validating
1887 -- on the un-commited data.
1888 -- Bug: 3864260 - removed the validation for gross_weight can not be less than unit weight
1889
1890 IF NOT l_error THEN
1891 write_debug_log('No Errors ...');
1892 -- if old and new values are different then putting attribute names into a Nested Table
1893 IF nvl(i.unit_length, -999) <> nvl(l_unit_length, -999) THEN
1894 l_attribute_names.EXTEND;
1895 k := k +1;
1896 l_attribute_names(k) := 'Unit_Length';
1897 END IF;
1898
1899 IF nvl(i.unit_weight, -999) <> nvl(l_unit_weight, -999) THEN
1900 l_attribute_names.EXTEND;
1901 k := k +1;
1902 l_attribute_names(k) := 'Unit_Weight';
1903 l_msib_upd_reqd := TRUE;
1904 l_attr_diffs.EXTEND();
1905 l_attr_diffs(l_attr_diffs.LAST) := EGO_USER_ATTR_DIFF_OBJ
1906 ( attr_id => 0
1907 , attr_name => l_attribute_names(k)
1908 , old_attr_value_str => null
1909 , old_attr_value_num => l_unit_weight
1910 , old_attr_value_date => null
1911 , old_attr_uom => null
1912 , new_attr_value_str => null
1913 , new_attr_value_num => i.unit_weight
1914 , new_attr_value_date => null
1915 , new_attr_uom => null
1916 , unique_key_flag => null
1917 , extension_id => null
1918 );
1919 END IF;
1920
1921 IF nvl(i.unit_width, -999) <> nvl(l_unit_width, -999) THEN
1922 l_attribute_names.EXTEND;
1923 k := k +1;
1924 l_attribute_names(k) := 'Unit_Width';
1925 END IF;
1926
1927 IF nvl(i.unit_height, -999) <> nvl(l_unit_height, -999) THEN
1928 l_attribute_names.EXTEND;
1929 k := k +1;
1930 l_attribute_names(k) := 'Unit_Height';
1931 END IF;
1932
1933 IF nvl(i.unit_volume, -999) <> nvl(l_unit_volume, -999) THEN
1934 l_attribute_names.EXTEND;
1935 k := k +1;
1936 l_attribute_names(k) := 'Unit_Volume';
1937 END IF;
1938
1939 IF nvl(i.dimension_uom_code, '-x-') <> nvl(l_dimension_uom_code, '-x-') THEN
1940 l_attribute_names.EXTEND;
1941 k := k +1;
1942 l_attribute_names(k) := 'Dimension_Uom_Code';
1943 END IF;
1944
1945 IF nvl(i.list_price_per_unit, -999) <> nvl(l_list_price_per_unit, -999) THEN
1946 l_attribute_names.EXTEND;
1947 k := k +1;
1948 l_attribute_names(k) := 'List_Price_Per_Unit';
1949 END IF;
1950
1951 IF nvl(i.shippable_item_flag, '-x-') <> nvl(l_shippable_item_flag, '-x-') THEN
1952 l_attribute_names.EXTEND;
1953 k := k +1;
1954 l_attribute_names(k) := 'Shippable_Item_Flag';
1955 END IF;
1956
1957 IF nvl(i.invoiceable_item_flag, '-x-') <> nvl(l_invoiceable_item_flag, '-x-') THEN
1958 l_attribute_names.EXTEND;
1959 k := k +1;
1960 l_attribute_names(k) := 'Invoiceable_Item_Flag';
1961 END IF;
1962
1963 IF nvl(i.customer_order_enabled_flag, '-x-') <> nvl(l_customer_order_enabled_flag, '-x-') THEN
1964 l_attribute_names.EXTEND;
1965 k := k +1;
1966 l_attribute_names(k) := 'Customer_Order_Enabled_Flag';
1967 l_msib_upd_reqd := TRUE;
1968 l_attr_diffs.EXTEND();
1969 l_attr_diffs(l_attr_diffs.LAST) := EGO_USER_ATTR_DIFF_OBJ
1970 ( attr_id => 0
1971 , attr_name => l_attribute_names(k)
1972 , old_attr_value_str => l_customer_order_enabled_flag
1973 , old_attr_value_num => null
1974 , old_attr_value_date => null
1975 , old_attr_uom => null
1976 , new_attr_value_str => i.customer_order_enabled_flag
1977 , new_attr_value_num => null
1978 , new_attr_value_date => null
1979 , new_attr_uom => null
1980 , unique_key_flag => null
1981 , extension_id => null
1982 );
1983 END IF;
1984
1985 IF nvl(i.description, '-x-') <> nvl(l_description, '-x-') THEN
1986 l_attribute_names.EXTEND;
1987 k := k +1;
1988 l_attribute_names(k) := 'Description';
1989 END IF;
1990
1991 -- Bug: 3874653 -- if the UOM of unit weight was changed, the change was not
1992 -- getting propagated to higher level GTINs
1993 IF nvl(i.weight_uom_code, '-x-') <> nvl(l_weight_uom_code, '-x-') THEN
1994 l_attribute_names.EXTEND;
1995 k := k +1;
1996 l_attribute_names(k) := 'Weight_Uom_Code';
1997 l_msib_upd_reqd := TRUE;
1998 l_attr_diffs.EXTEND();
1999 l_attr_diffs(l_attr_diffs.LAST) := EGO_USER_ATTR_DIFF_OBJ
2000 ( attr_id => 0
2001 , attr_name => l_attribute_names(k)
2002 , old_attr_value_str => l_weight_uom_code
2003 , old_attr_value_num => null
2004 , old_attr_value_date => null
2005 , old_attr_uom => null
2006 , new_attr_value_str => i.weight_uom_code
2007 , new_attr_value_num => null
2008 , new_attr_value_date => null
2009 , new_attr_uom => null
2010 , unique_key_flag => null
2011 , extension_id => null
2012 );
2013 END IF;
2014
2015 -- Bug: 3921782 -- if the UOM of Volume was changed, the registration status was
2016 -- not getting changed to re-registration needed
2017 IF nvl(i.volume_uom_code, '-x-') <> nvl(l_volume_uom_code, '-x-') THEN
2018 l_attribute_names.EXTEND;
2019 k := k +1;
2020 l_attribute_names(k) := 'Volume_Uom_Code';
2021 END IF;
2022
2023 -- Bug: 5254856 -- for TP dependent attrs
2024 IF nvl(i.shelf_life_days, -999) <> nvl(l_shelf_life_days, -999) THEN
2025 l_attribute_names.EXTEND;
2026 k := k +1;
2027 l_attribute_names(k) := 'Shelf_Life_Days';
2028 END IF;
2029
2030 -- R12-C
2031 IF nvl(i.trade_item_descriptor, '-x-') <> nvl(l_trade_item_desc, '-x-') THEN
2032 l_attribute_names.EXTEND;
2033 k := k +1;
2034 l_attribute_names(k) := 'Trade_Item_Descriptor';
2035 END IF;
2036
2037 IF k > 0 THEN
2038 FOR l in 1..k LOOP
2039 write_debug_log('Attribute modified -> '||l_attribute_names(l));
2040 END LOOP;
2041 END IF;
2042
2043 IF k > 0 THEN
2044 IF l_msib_upd_reqd THEN
2045 UPDATE MTL_SYSTEM_ITEMS_B
2046 SET
2047 UNIT_WEIGHT = i.unit_weight,
2048 CUSTOMER_ORDER_ENABLED_FLAG = i.customer_order_enabled_flag,
2049 WEIGHT_UOM_CODE = i.weight_uom_code -- Bug: 3874653
2050 WHERE ROWID = l_msib_rowid;
2051 END IF;
2052
2053 IF P_Suppress_Rollup <> 'Y' THEN
2054
2055 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
2056 ( EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', to_char(i.inventory_item_id))
2057 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', to_char(i.organization_id))
2058 );
2059
2060 -- issue query against MSIB for item_cat_group_id, value for item_id, org_id
2061 SELECT item_catalog_group_id
2062 INTO l_item_catalog_group_id
2063 FROM mtl_system_items_b
2064 WHERE inventory_item_id = i.inventory_item_id
2065 AND organization_id = i.organization_id;
2066
2067 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
2068 (EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', to_char(l_item_catalog_group_id)));
2069
2070 l_data_level_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
2071 (EGO_COL_NAME_VALUE_PAIR_OBJ('DATA_LEVEL', 'EGO_ITEM'));
2072
2073 -- achampan: add call to item_propagate_attributes
2074 EGO_GTIN_PVT.Item_Propagate_Attributes
2075 ( p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
2076 , p_class_code_name_value_pairs => l_class_code_name_value_pairs
2077 , p_data_level_name_value_pairs => l_data_level_name_value_pairs
2078 , p_attr_diffs => l_attr_diffs
2079 , p_transaction_type => 'UPDATE'
2080 , x_error_message => l_error_message
2081 );
2082
2083 END IF;
2084
2085 write_debug_log('Before calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES from IOI ...');
2086 EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES(
2087 p_inventory_item_id => i.inventory_item_id,
2088 p_organization_id => i.organization_id,
2089 p_attribute_names => l_attribute_names,
2090 p_commit => FND_API.G_FALSE,
2091 x_return_status => l_return_status,
2092 x_msg_count => l_msg_count,
2093 x_msg_data => l_msg_text);
2094
2095 write_debug_log('After calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES from IOI ... return_status, error = '||l_return_status||' , '||l_msg_text);
2096 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2097 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
2098 SET process_flag = 3
2099 WHERE rowid = i.rowid;
2100
2101 dumm_status := INVPUOPI.mtl_log_interface_err(
2102 i.organization_id
2103 ,P_User_Id
2104 ,P_Login_Id
2105 ,P_Prog_AppId
2106 ,P_Prog_Id
2107 ,P_Request_Id
2108 ,i.transaction_id
2109 ,l_msg_text
2110 ,'UCCnet'
2111 ,'MTL_SYSTEM_ITEMS_INTERFACE'
2112 ,'INV_IOI_ERR'
2113 ,err_text);
2114
2115 END IF; -- if not success
2116 END IF; -- if k > 0 i.e. if new attribute values are not equal to old attribute values
2117 END IF; -- end if not l_error
2118 END IF; -- if l_gdsn_outbound_enabled_flag = Y
2119 END LOOP;
2120 write_debug_log('End calling EGO_GTIN_PVT.PROCESS_UCCNET_ATTRIBUTES...');
2121 END PROCESS_UCCNET_ATTRIBUTES;
2122
2123 /*
2124 ** Added by Devendra - This method will update the REGISTRATION_UPDATE_DATE and TP_NEUTRAL_UPDATE_DATE
2125 ** for an item. If parameter p_update_reg is supplied as 'Y' then REGISTRATION_UPDATE_DATE and
2126 ** TP_NEUTRAL_UPDATE_DATE will be updated else only TP_NEUTRAL_UPDATE_DATE will be updated.
2127 */
2128 PROCEDURE UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id NUMBER,
2129 p_organization_id NUMBER,
2130 p_update_reg VARCHAR2 := 'N',
2131 p_commit VARCHAR2 := FND_API.G_FALSE,
2132 x_return_status OUT NOCOPY VARCHAR2,
2133 x_msg_count OUT NOCOPY NUMBER,
2134 x_msg_data OUT NOCOPY VARCHAR2)
2135 IS
2136 BEGIN
2137 IF NVL(p_update_reg, 'N') = 'Y' THEN
2138 UPDATE EGO_ITEM_GTN_ATTRS_B
2139 SET REGISTRATION_UPDATE_DATE = SYSDATE,
2140 TP_NEUTRAL_UPDATE_DATE = SYSDATE
2141 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
2142 AND ORGANIZATION_ID = p_organization_id;
2143 ELSE
2144 UPDATE EGO_ITEM_GTN_ATTRS_B
2145 SET TP_NEUTRAL_UPDATE_DATE = SYSDATE
2146 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
2147 AND ORGANIZATION_ID = p_organization_id;
2148 END IF;
2149
2150 IF p_commit = FND_API.G_TRUE THEN
2151 COMMIT;
2152 END IF;
2153
2154 x_return_status := 'S';
2155 x_msg_count := 0;
2156 x_msg_data := NULL;
2157 EXCEPTION WHEN OTHERS THEN
2158 x_return_status := 'U';
2159 x_msg_data := SQLERRM;
2160 x_msg_count := 1;
2161 END UPDATE_REG_PUB_UPDATE_DATES;
2162
2163 --------------------------------
2164 /*
2165 ** Added by Amay - for propagation of attributes up the hierarchy
2166 */
2167 --------------------------------
2168 -- sridhar modified
2169 --------------------------------
2170
2171 PROCEDURE Item_Propagate_Attributes
2172 ( p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2173 , p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2174 , p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2175 , p_attr_diffs IN EGO_USER_ATTR_DIFF_TABLE
2176 , p_transaction_type IN VARCHAR2
2177 , p_attr_group_id IN NUMBER DEFAULT NULL
2178 , x_error_message OUT NOCOPY VARCHAR2
2179 )
2180 IS
2181 l_action_map Bom_Rollup_Pub.Rollup_Action_Map;
2182 l_item_id NUMBER;
2183 l_organization_id NUMBER;
2184 l_structure_type_name VARCHAR2(200);
2185
2186 l_api_name VARCHAR2(30);
2187 l_item_obj_name VARCHAR2(30);
2188
2189 l_propagate_brand_info BOOLEAN;
2190 l_propagate_mfg_info BOOLEAN;
2191 l_trade_item_or_consumer_unit BOOLEAN;
2192 l_propagate_sh_temps BOOLEAN;
2193 l_propagate_top_gtin BOOLEAN;
2194 l_propagate_unit_weight BOOLEAN;
2195
2196 l_null_str_value VARCHAR2(1);
2197 l_null_num_value NUMBER;
2198 l_null_date_value DATE;
2199
2200 l_user_attr_diff_data EGO_USER_ATTR_DIFF_OBJ;
2201
2202 BEGIN
2203
2204 l_api_name := 'Item_Propagate_Attributes';
2205 WRITE_DEBUG_LOG(l_api_name || ': Started ');
2206 l_item_obj_name := 'EGO_ITEM';
2207 l_action_map := Bom_Rollup_Pub.G_EMPTY_ACTION_MAP;
2208 l_structure_type_name := 'Packaging Hierarchy';
2209
2210 l_propagate_brand_info := FALSE;
2211 l_propagate_mfg_info := FALSE;
2212 l_trade_item_or_consumer_unit := FALSE;
2213 l_propagate_sh_temps := FALSE;
2214 l_propagate_top_gtin := FALSE;
2215 l_propagate_unit_weight := FALSE;
2216
2217 l_null_str_value := FND_API.G_MISS_CHAR;
2218 l_null_num_value := FND_API.G_MISS_NUM;
2219 l_null_date_value := FND_API.G_MISS_DATE;
2220
2221 l_item_id := p_pk_column_name_value_pairs(p_pk_column_name_value_pairs.FIRST).VALUE;
2222 l_organization_id := p_pk_column_name_value_pairs(p_pk_column_name_value_pairs.FIRST+1).VALUE;
2223
2224 IF (p_attr_diffs.COUNT > 0) THEN
2225 WRITE_DEBUG_LOG(l_api_name || ': exists a list of attributes ');
2226 FOR i in p_attr_diffs.first..p_attr_diffs.last LOOP
2227 l_user_attr_diff_data := p_attr_diffs(i);
2228 WRITE_DEBUG_LOG(l_api_name || ': checking record - '|| i || ' - in the list of attributes sent - '||l_user_attr_diff_data.attr_name);
2229 IF (NVL(l_user_attr_diff_data.old_attr_value_str,l_null_str_value) <>
2230 NVL(l_user_attr_diff_data.new_attr_value_str,l_null_str_value))
2231 OR
2232 (NVL(l_user_attr_diff_data.old_attr_value_num,l_null_num_value) <>
2233 NVL(l_user_attr_diff_data.new_attr_value_num,l_null_num_value))
2234 OR
2235 (NVL(l_user_attr_diff_data.old_attr_value_date,l_null_date_value) <>
2236 NVL(l_user_attr_diff_data.new_attr_value_date,l_null_date_value))
2237 OR
2238 (NVL(l_user_attr_diff_data.old_attr_uom,l_null_str_value) <>
2239 NVL(l_user_attr_diff_data.new_attr_uom,l_null_str_value)) THEN
2240 WRITE_DEBUG_LOG(l_api_name || ': an attribute has changed ');
2241 IF NOT l_propagate_brand_info
2242 AND
2243 UPPER(l_user_attr_diff_data.attr_name)
2244 IN ('RETAIL_BRAND_OWNER_NAME'
2245 ,'RETAIL_BRAND_OWNER_GLN'
2246 ,'FUNCTIONAL_NAME'
2247 ,'SUB_BRAND') THEN
2248 WRITE_DEBUG_LOG(l_api_name || ': Brand info has changed ');
2249 l_propagate_brand_info := check_propagation_allowed(l_user_attr_diff_data.attr_id);
2250 -- the following functions will be rolled up
2251 -- attribute attribute_name
2252 -- ** BrandOwnerName Retail_Brand_Owner_Name
2253 -- ** BrandOwnerGLN Retail_Brand_Owner_Gln
2254 -- ** FunctionalName Functional_name
2255 -- ** SubBrand Sub_Brand
2256 ELSIF NOT l_propagate_mfg_info
2257 AND
2258 UPPER(l_user_attr_diff_data.attr_name)
2259 IN ('MANUFACTURER_GLN'
2260 ,'NAME_OF_MANUFACTURER') THEN
2261 WRITE_DEBUG_LOG(l_api_name || ': Manufacturer info has changed ');
2262 l_propagate_mfg_info := check_propagation_allowed(l_user_attr_diff_data.attr_id);
2263 -- the following functions will be rolled up
2264 -- attribute attribute_name
2265 -- ** ManufacturerGLN Manufacturer_Gln
2266 -- ** ManufacturerName Name_Of_Manufacturer
2267 ELSIF NOT l_trade_item_or_consumer_unit
2268 AND
2269 upper(l_user_attr_diff_data.attr_name) = 'IS_TRADE_ITEM_A_CONSUMER_UNIT' THEN
2270 l_trade_item_or_consumer_unit := check_propagation_allowed(l_user_attr_diff_data.attr_id);
2271 ELSIF NOT l_propagate_sh_temps
2272 AND
2273 UPPER(l_user_attr_diff_data.attr_name)
2274 IN ('UCCNET_STORAGE_TEMP_MIN'
2275 ,'UOM_STORAGE_HANDLING_TEMP_MIN'
2276 ,'UCCNET_STORAGE_TEMP_MAX'
2277 ,'UOM_STORAGE_HANDLING_TEMP_MAX') THEN
2278 WRITE_DEBUG_LOG(l_api_name || ': Storage Handling Temps have changed ');
2279 l_propagate_sh_temps := check_propagation_allowed(l_user_attr_diff_data.attr_id);
2280 IF NOT l_propagate_sh_temps THEN
2281 l_propagate_sh_temps := TRUE;
2282 END IF;
2283 -- the following functions will be rolled up
2284 -- attribute attribute_name
2285 -- ** UccnetStorageTempMin Uccnet_Storage_Temp_Min
2286 -- ** UomStorageHandlingTempMin Uom_Storage_Handling_Temp_Min
2287 -- ** UccnetStorageTempMax Uccnet_Storage_Temp_Max
2288 -- ** UomStorageHandlingTempMax Uom_Storage_Handling_Temp_Max
2289 ELSIF NOT l_propagate_top_gtin
2290 AND
2291 UPPER(l_user_attr_diff_data.attr_name) = 'CUSTOMER_ORDER_ENABLED_FLAG' THEN
2292 WRITE_DEBUG_LOG(l_api_name || ': Customer Order Enabled Flag has changed ');
2293 l_propagate_top_gtin := TRUE; --check_propagation_allowed(l_user_attr_diff_data.attr_id);
2294 -- the following functions will be rolled up
2295 -- attribute attribute_name
2296 -- ** TopGtin TopGtin
2297 ELSIF NOT l_propagate_unit_weight
2298 AND
2299 UPPER(l_user_attr_diff_data.attr_name)
2300 IN ('UNIT_WEIGHT'
2301 ,'WEIGHT_UOM_CODE') THEN
2302 WRITE_DEBUG_LOG(l_api_name || ': Unit Weight/UOM has changed ');
2303 l_propagate_unit_weight := TRUE; --check_propagation_allowed(l_user_attr_diff_data.attr_id);
2304 -- the following functions will be rolled up
2305 -- attribute attribute_name
2306 -- ** UnitWeight Unit_Weight
2307 -- ** WeightUomCode Weight_Uom_Code
2308 ELSE
2309 WRITE_DEBUG_LOG(l_api_name || ': Attribute values ALREADY tagged for change OR a different GTIN attribute has been observed ');
2310 END IF; -- attribute that needs to be changed.
2311 ELSE
2312 WRITE_DEBUG_LOG(l_api_name || ': Attribute values have not changed ');
2313 END IF; -- value has changed
2314 END LOOP; -- looping each attribute
2315 ELSE
2316 WRITE_DEBUG_LOG(l_api_name || ': list of attributes is NULL ');
2317 END IF; -- there exists attr diffs
2318 WRITE_DEBUG_LOG(l_api_name || ': using params ('||l_item_id||','||l_organization_id||','||l_structure_type_name||')');
2319
2320 IF l_propagate_brand_info THEN
2321 Bom_Rollup_Pub.Add_Rollup_Function
2322 ( p_Object_Name => l_item_obj_name
2323 , p_Rollup_Action => Bom_Rollup_Pub.G_PROPOGATE_BRAND_INFO
2324 , p_DML_Function => 'Bom_Compute_Functions.Set_Brand_Info'
2325 , p_DML_Delayed_Write => 'N'
2326 , x_Rollup_Action_Map => l_action_map
2327 );
2328 END IF;
2329
2330 IF l_propagate_mfg_info THEN
2331 Bom_Rollup_Pub.Add_Rollup_Function
2332 ( p_Object_Name => l_item_obj_name
2333 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_MULTI_ROW_ATTRS
2334 , p_DML_Function => 'Bom_Compute_Functions.Set_Multirow_Attributes'
2335 , p_DML_Delayed_Write => 'N'
2336 , x_Rollup_Action_Map => l_action_map
2337 );
2338 END IF;
2339
2340 IF l_trade_item_or_consumer_unit OR l_propagate_top_gtin THEN
2341 Bom_Rollup_Pub.Add_Rollup_Function
2342 ( p_Object_Name => l_item_obj_name
2343 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_TOP_GTIN_FLAG
2344 , p_DML_Function => 'Bom_Compute_Functions.Set_Top_GTIN_Flag'
2345 , p_DML_Delayed_Write => 'N'
2346 , x_Rollup_Action_Map => l_action_map
2347 );
2348 END IF;
2349
2350 IF l_propagate_sh_temps THEN
2351 Bom_Rollup_Pub.Add_Rollup_Function
2352 ( p_Object_Name => l_item_obj_name
2353 , p_Rollup_Action => Bom_Rollup_Pub.G_PROPAGATE_SH_TEMPS
2354 , p_DML_Function => 'Bom_Compute_Functions.Set_SH_Temps'
2355 , p_DML_Delayed_Write => 'N'
2356 , x_Rollup_Action_Map => l_action_map
2357 );
2358 END IF;
2359
2360 IF l_propagate_unit_weight THEN
2361 Bom_Rollup_Pub.Add_Rollup_Function
2362 ( p_Object_Name => 'EGO_ITEM'
2363 , p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_NET_WEIGHT
2364 , p_DML_Function => 'Bom_Compute_Functions.Set_Net_Weight'
2365 , p_DML_Delayed_Write => 'N'
2366 , x_Rollup_Action_Map => l_action_map
2367 );
2368 END IF;
2369
2370 -- IF l_propagate_brand_info OR l_trade_item_or_consumer_unit THEN
2371 -- todo uncomment the below after making mfg changes
2372 IF l_propagate_brand_info OR
2373 l_propagate_mfg_info OR
2374 l_trade_item_or_consumer_unit OR
2375 l_propagate_sh_temps OR
2376 l_propagate_top_gtin OR
2377 l_propagate_unit_weight THEN
2378 WRITE_DEBUG_LOG(l_api_name || ': calling Bom_Rollup_Pub.Perform_Rollup');
2379 Bom_Rollup_Pub.Perform_Rollup
2380 ( p_item_id => l_item_id
2381 , p_organization_id => l_organization_id
2382 , p_structure_type_name => l_structure_type_name
2383 , p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
2384 , p_class_code_name_value_pairs => p_class_code_name_value_pairs
2385 , p_data_level_name_value_pairs => p_data_level_name_value_pairs
2386 , p_attr_diffs => p_attr_diffs
2387 , p_transaction_type => p_transaction_type
2388 , p_attr_group_id => p_attr_group_id
2389 , p_action_map => l_action_map
2390 , x_error_message => x_error_message
2391 );
2392 END IF;
2393 WRITE_DEBUG_LOG(l_api_name || 'return value from Perform_Rollup:' || x_error_message);
2394 WRITE_DEBUG_LOG(l_api_name || ': Done ');
2395
2396
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
2400 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
2401 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, l_api_name);
2402 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
2403 x_error_message := FND_MSG_PUB.Get;
2404 WRITE_DEBUG_LOG(x_error_message);
2405 RETURN;
2406 END Item_Propagate_Attributes;
2407 --------------------------------
2408
2409 -- Call correct EGO API to update attribute, update registration flag as necessary
2410 -- Currently, this only support SINGLE-ROW attributes. See the other Update_Attributes API for MULTI-ROW
2411 PROCEDURE Update_Attribute
2412 ( p_inventory_item_id IN NUMBER
2413 , p_organization_id IN NUMBER
2414 , p_attr_name IN VARCHAR2
2415 , p_attr_group_type IN VARCHAR2 DEFAULT NULL
2416 , p_attr_group_name IN VARCHAR2 DEFAULT NULL
2417 , p_attr_new_value_str IN VARCHAR2 DEFAULT NULL
2418 , p_attr_new_value_num IN NUMBER DEFAULT NULL
2419 , p_attr_new_value_date IN DATE DEFAULT NULL
2420 , p_attr_new_value_uom IN VARCHAR2 DEFAULT NULL
2421 , p_debug_level IN NUMBER DEFAULT 0
2422 , x_return_status OUT NOCOPY VARCHAR2
2423 , x_errorcode OUT NOCOPY NUMBER
2424 , x_msg_count OUT NOCOPY NUMBER
2425 , x_msg_data OUT NOCOPY VARCHAR2
2426 )
2427 IS
2428 CURSOR c_msi_old_values IS
2429 SELECT
2430 inventory_item_id,
2431 organization_id,
2432 segment1,
2433 unit_weight,
2434 weight_uom_code
2435 FROM MTL_SYSTEM_ITEMS_B
2436 WHERE inventory_item_id = p_inventory_item_id
2437 AND organization_id = p_organization_id;
2438
2439 l_unit_weight MTL_SYSTEM_ITEMS.UNIT_WEIGHT%TYPE := NULL;
2440 l_weight_uom_code MTL_SYSTEM_ITEMS.WEIGHT_UOM_CODE%TYPE := NULL;
2441
2442 l_msi_attr_names EGO_VARCHAR_TBL_TYPE := EGO_VARCHAR_TBL_TYPE(null);
2443 l_msi_attr_given BOOLEAN := FALSE;
2444 l_msi_index NUMBER;
2445
2446 CURSOR c_user_attr_metadata IS
2447 SELECT data_type_code
2448 , database_column
2449 FROM ego_attrs_v v
2450 WHERE attr_group_type = p_attr_group_type
2451 AND attr_group_name = p_attr_group_name
2452 AND attr_name = p_attr_name
2453 AND application_id = 431;
2454
2455 l_data_type_code VARCHAR2(30);
2456 l_attr_old_value_str VARCHAR2(1000) := NULL;
2457 l_attr_old_value_num NUMBER := NULL;
2458 l_attr_old_value_date DATE := NULL;
2459 l_attr_old_value_uom VARCHAR2(3) := NULL;
2460 l_old_query VARCHAR2(4000);
2461
2462 l_pk_columns EGO_COL_NAME_VALUE_PAIR_ARRAY;
2463 l_class_code EGO_COL_NAME_VALUE_PAIR_ARRAY;
2464 l_item_catalog_group_id NUMBER;
2465 l_error_code NUMBER;
2466 l_attribute_names EGO_VARCHAR_TBL_TYPE;
2467 l_attr_new_values EGO_USER_ATTR_DATA_TABLE;
2468
2469 --Cursor for creating the classification code
2470 CURSOR get_classification_code IS
2471 SELECT item_catalog_group_id
2472 FROM mtl_system_items_b
2473 WHERE inventory_item_id = p_inventory_item_id
2474 AND organization_id = p_organization_id;
2475
2476 l_inventory_item_id NUMBER;
2477 l_organization_id NUMBER;
2478 l_api_name VARCHAR2(40);
2479 l_weight_changed BOOLEAN := FALSE;
2480 l_uom_changed BOOLEAN := FALSE;
2481 l_found_attr BOOLEAN := FALSE;
2482
2483 BEGIN
2484
2485 x_return_status := FND_API.G_RET_STS_SUCCESS;
2486 l_api_name := 'Update_Attribute';
2487
2488 write_debug_log(l_api_name||': starting for '||p_inventory_item_id||'-'||p_organization_id);
2489
2490 IF p_attr_group_type IS NOT NULL THEN
2491
2492 -- setting user-defined attributes
2493 -- todo: uoms? for right now, SH temp and weight are the only ones with uoms,
2494 -- which we handle correctly
2495
2496 -- first query up the current values
2497 FOR i IN c_user_attr_metadata LOOP
2498
2499 l_found_attr := TRUE;
2500 l_old_query :=
2501 'SELECT '||i.database_column||
2502 ' FROM ego_item_gtn_attrs_vl '||
2503 ' WHERE inventory_item_id = :1 '||
2504 ' AND organization_id = :2';
2505
2506 write_debug_log(l_api_name||': found data type code '||i.data_type_code);
2507
2508 IF (i.data_type_code = EGO_EXT_FWK_PUB.G_CHAR_DATA_TYPE OR
2509 i.data_type_code = EGO_EXT_FWK_PUB.G_TRANS_TEXT_DATA_TYPE)
2510 THEN
2511
2512 EXECUTE IMMEDIATE l_old_query
2513 INTO l_attr_old_value_str
2514 USING p_inventory_item_id, p_organization_id;
2515
2516 ELSIF (i.data_type_code = EGO_EXT_FWK_PUB.G_NUMBER_DATA_TYPE)
2517 THEN
2518
2519 EXECUTE IMMEDIATE l_old_query
2520 INTO l_attr_old_value_num
2521 USING p_inventory_item_id, p_organization_id;
2522
2523 ELSIF (i.data_type_code = EGO_EXT_FWK_PUB.G_DATE_DATA_TYPE OR
2524 i.data_type_code = EGO_EXT_FWK_PUB.G_DATE_TIME_DATA_TYPE)
2525 THEN
2526
2527 EXECUTE IMMEDIATE l_old_query
2528 INTO l_attr_old_value_date
2529 USING p_inventory_item_id, p_organization_id;
2530
2531 END IF;
2532
2533 write_debug_log(l_api_name||': got old value '||l_attr_old_value_str||','||l_attr_old_value_num||','||l_attr_old_value_date);
2534
2535 -- only perform update if values are different
2536 IF (NVL(l_attr_old_value_str, '-x-') <> NVL(p_attr_new_value_str, '-x-')) OR
2537 (NVL(l_attr_old_value_num, -999) <> NVL(p_attr_new_value_num, -999)) OR
2538 (NOT ((l_attr_old_value_date IS NULL AND
2539 p_attr_new_value_date IS NULL) OR
2540 (l_attr_old_value_date IS NOT NULL AND
2541 p_attr_new_value_date IS NOT NULL AND
2542 l_attr_old_value_date = p_attr_new_value_date)))
2543 THEN
2544
2545 l_pk_columns := EGO_COL_NAME_VALUE_PAIR_ARRAY
2546 ( EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', p_inventory_item_id)
2547 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', p_organization_id)
2548 );
2549
2550 FOR c1 IN get_classification_code
2551 LOOP
2552 l_item_catalog_group_id := c1.item_catalog_group_id;
2553 END LOOP;
2554
2555 l_class_code := EGO_COL_NAME_VALUE_PAIR_ARRAY
2556 (EGO_COL_NAME_VALUE_PAIR_OBJ( 'ITEM_CATALOG_GROUP_ID', to_char(l_item_catalog_group_id)));
2557
2558 l_attr_new_values :=
2559 EGO_USER_ATTR_DATA_TABLE
2560 ( EGO_USER_ATTR_DATA_OBJ
2561 ( 1
2562 , p_attr_name
2563 , p_attr_new_value_str
2564 , p_attr_new_value_num
2565 , p_attr_new_value_date
2566 , NULL
2567 , p_attr_new_value_uom
2568 , NULL
2569 )
2570 );
2571
2572 write_debug_log(l_api_name||': calling Perform_DML_On_Row');
2573
2574 EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row
2575 ( p_api_version => 1.0
2576 , p_object_name => 'EGO_ITEM'
2577 , p_application_id => 431
2578 , p_attr_group_type => p_attr_group_type
2579 , p_attr_group_name => p_attr_group_name
2580 , p_pk_column_name_value_pairs => l_pk_columns
2581 , p_class_code_name_value_pairs => l_class_code
2582 -- , p_data_level_name_value_pairs => l_data_level
2583 , p_data_level_name_value_pairs => Bom_Rollup_Pub.g_data_level_name_value_pairs
2584 , p_attr_name_value_pairs => l_attr_new_values
2585 -- this is very important, because otherwise, updates would trigger rollups, creating an infinite loop
2586 , p_debug_level => p_debug_level
2587 , p_bulkload_flag => FND_API.G_TRUE
2588 , x_return_status => x_return_status
2589 , x_errorcode => x_errorcode
2590 , x_msg_count => x_msg_count
2591 , x_msg_data => x_msg_data
2592 );
2593
2594 write_debug_log(l_api_name||': called Perform_DML_On_Row with ret='||x_return_status);
2595
2596 IF x_return_status = 'S' THEN
2597
2598 -- build attr list and call process_attr_updates
2599 l_attribute_names := EGO_VARCHAR_TBL_TYPE(p_attr_name);
2600
2601 write_debug_log(l_api_name||': calling Process_Attribute_Updates');
2602
2603 Process_Attribute_Updates
2604 ( p_inventory_item_id => p_inventory_item_id
2605 , p_organization_id => p_organization_id
2606 , p_attribute_names => l_attribute_names
2607 , x_return_status => x_return_status
2608 , x_msg_count => x_msg_count
2609 , x_msg_data => x_msg_data
2610 );
2611
2612 write_debug_log(l_api_name||': called Process_Attribute_Updates with ret='||x_return_status);
2613
2614 END IF;
2615
2616 END IF;
2617
2618 END LOOP; -- c_user_attr_metadata
2619
2620 ELSE
2621
2622 write_debug_log(l_api_name||': processing msi or top gtin attribute');
2623
2624 -- Get old attr value from the db
2625 IF UPPER(p_attr_name) = 'UNIT_WEIGHT' OR
2626 UPPER(p_attr_name) = 'WEIGHT_UOM_CODE'
2627 THEN
2628
2629 l_found_attr := TRUE;
2630
2631 IF UPPER(p_attr_name) = 'UNIT_WEIGHT' THEN
2632
2633 l_unit_weight := p_attr_new_value_num;
2634 l_weight_changed := TRUE;
2635
2636 -- if uom is given in p_attr_new_value_uom, use it
2637 IF p_attr_new_value_uom IS NOT NULL THEN
2638
2639 l_weight_uom_code := p_attr_new_value_uom;
2640 l_uom_changed := TRUE;
2641
2642 END IF;
2643
2644 ELSE
2645
2646 -- allow passing of weight uom in either str or uom (str preferred)
2647 l_weight_uom_code := nvl(p_attr_new_value_str, p_attr_new_value_uom);
2648 l_uom_changed := TRUE;
2649
2650 END IF;
2651
2652 -- Compare old with new from the attr map
2653 FOR c2 IN c_msi_old_values
2654 LOOP
2655
2656 write_debug_log(l_api_name||': old wt = '||c2.unit_weight||' '||c2.weight_uom_code||' new wt = '||l_unit_weight||' '||l_weight_uom_code);
2657
2658 -- only perform update if values are different
2659 l_weight_changed := l_weight_changed AND (NVL(c2.unit_weight, -999) <> NVL(l_unit_weight, -999));
2660 l_uom_changed := l_uom_changed AND (NVL(c2.weight_uom_code, '-x-') <> NVL(l_weight_uom_code, '-x-'));
2661
2662 -- default weight/uom to old value if missing
2663 IF l_weight_changed AND (NOT l_uom_changed) THEN
2664
2665 write_debug_log(l_api_name||': defaulting uom');
2666 l_weight_uom_code := c2.weight_uom_code;
2667
2668 ELSIF (NOT l_weight_changed) AND l_uom_changed THEN
2669
2670 write_debug_log(l_api_name||': defaulting wt');
2671 l_unit_weight := c2.unit_weight;
2672
2673 END IF;
2674
2675 IF l_weight_changed OR l_uom_changed THEN
2676
2677 write_debug_log(l_api_name||': calling Process_Item with wt '||l_unit_weight||' uom '||l_weight_uom_code);
2678
2679 -- Call INV API for MSI attributes
2680 EGO_ITEM_PUB.Process_Item(
2681 p_api_version => 1.0
2682 , p_transaction_type => EGO_ITEM_PUB.G_TTYPE_UPDATE
2683 , p_inventory_item_id => p_inventory_item_id
2684 , p_organization_id => p_organization_id
2685 , p_item_number => c2.segment1
2686 , p_weight_uom_code => l_weight_uom_code
2687 , p_unit_weight => l_unit_weight
2688 , p_process_control => 'SUPPRESS_ROLLUP'
2689 , x_inventory_item_id => l_inventory_item_id
2690 , x_organization_id => l_organization_id
2691 , x_return_status => x_return_status
2692 , x_msg_count => x_msg_count
2693 , x_msg_data => x_msg_data
2694 );
2695
2696 write_debug_log(l_api_name||': Process_Item returned '||x_return_status||' '||x_msg_data);
2697
2698 END IF;
2699
2700 END LOOP;
2701
2702 ELSIF UPPER(p_attr_name) = 'TOP_GTIN' THEN
2703
2704 l_found_attr := TRUE;
2705 Set_Top_GTIN_Flag(p_inventory_item_id, p_organization_id, p_attr_new_value_str, x_return_status);
2706
2707 END IF; --p_attr_name
2708
2709 END IF; --p_attr_group_type IS NOT NULL
2710
2711 IF NOT l_found_attr THEN
2712
2713 -- add more descriptive error
2714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2715 x_msg_data := SQLERRM;
2716 x_msg_count := 1;
2717
2718 END IF;
2719
2720 IF x_return_status <> 'S' THEN
2721
2722 write_debug_log(l_api_name||' : ******* ERROR ******* '||x_return_status||' '||fnd_msg_pub.get(1,x_msg_data));
2723
2724 END IF;
2725
2726 EXCEPTION
2727
2728 WHEN OTHERS THEN
2729
2730 write_debug_log(l_api_name||': failed: '||sqlerrm);
2731
2732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2733
2734 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
2735 FND_MESSAGE.Set_Token('PKG_NAME', g_pkg_name);
2736 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
2737 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
2738 FND_MSG_PUB.Add;
2739 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2740 ,p_count => x_msg_count
2741 ,p_data => x_msg_data);
2742
2743 END Update_Attribute;
2744
2745 --------------------------------
2746
2747 PROCEDURE Update_Attributes
2748 ( p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2749 , p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2750 , p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2751 , p_attr_diffs IN EGO_USER_ATTR_DIFF_TABLE
2752 , p_transaction_type IN VARCHAR2
2753 , p_attr_group_id IN NUMBER DEFAULT NULL
2754 , x_error_message OUT NOCOPY VARCHAR2
2755 )
2756 IS
2757
2758 l_object_name_table_index NUMBER;
2759 l_object_id NUMBER;
2760
2761 BEGIN
2762
2763 EGO_USER_ATTRS_DATA_PVT.Update_Attributes
2764 ( p_pk_column_name_value_pairs
2765 , p_class_code_name_value_pairs
2766 , 'ITEM_LEVEL'
2767 , p_data_level_name_value_pairs
2768 , p_attr_diffs
2769 , p_transaction_type
2770 , p_attr_group_id
2771 , x_error_message);
2772
2773 END Update_Attributes;
2774
2775 --------------------------------
2776
2777 /*
2778 ** Added by Amay - for getting of attribute diff objects (called by BOM_ROLLUP_PUB)
2779 */
2780 PROCEDURE Get_Attr_Diffs
2781 ( p_inventory_item_id IN NUMBER
2782 , p_org_id IN NUMBER
2783 , p_attr_group_id IN NUMBER DEFAULT NULL
2784 , p_application_id IN NUMBER DEFAULT NULL
2785 , p_attr_group_type IN VARCHAR2 DEFAULT NULL
2786 , p_attr_group_name IN VARCHAR2 DEFAULT NULL
2787 , px_attr_diffs IN OUT NOCOPY EGO_USER_ATTR_DIFF_TABLE
2788 , px_pk_column_name_value_pairs OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
2789 , px_class_code_name_value_pairs OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
2790 , px_data_level_name_value_pairs OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
2791 , x_error_message OUT NOCOPY VARCHAR2
2792 )
2793 IS
2794 -- l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2795 -- l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2796 l_item_catalog_group_id NUMBER;
2797 BEGIN
2798
2799 px_pk_column_name_value_pairs :=
2800 EGO_COL_NAME_VALUE_PAIR_ARRAY
2801 ( EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', to_char(p_inventory_item_id))
2802 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', to_char(p_org_id))
2803 );
2804
2805 -- issue query against MSIB for item_cat_group_id, value for item_id, org_id
2806 SELECT item_catalog_group_id INTO l_item_catalog_group_id
2807 FROM mtl_system_items_b
2808 WHERE inventory_item_id = p_inventory_item_id
2809 AND organization_id = p_org_id;
2810
2811 px_class_code_name_value_pairs :=
2812 EGO_COL_NAME_VALUE_PAIR_ARRAY
2813 (EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', to_char(l_item_catalog_group_id)));
2814
2815 EGO_USER_ATTRS_DATA_PVT.Get_Attr_Diffs
2816 ( p_object_name => 'EGO_ITEM'
2817 , p_pk_column_name_value_pairs => px_pk_column_name_value_pairs
2818 , p_class_code_name_value_pairs => px_class_code_name_value_pairs
2819 , p_data_level => 'ITEM_LEVEL'
2820 , p_data_level_name_value_pairs => NULL
2821 , p_attr_group_id => p_attr_group_id
2822 , p_application_id => p_application_id
2823 , p_attr_group_type => p_attr_group_type
2824 , p_attr_group_name => p_attr_group_name
2825 , px_attr_diffs => px_attr_diffs
2826 , x_error_message => x_error_message);
2827
2828 END Get_Attr_Diffs;
2829
2830 --------------------------------
2831
2832 PROCEDURE Set_Top_GTIN_Flag
2833 ( p_inventory_item_id IN NUMBER
2834 , p_organization_id IN NUMBER
2835 , p_top_gtin_flag IN VARCHAR2
2836 , x_return_status OUT NOCOPY VARCHAR2
2837 )
2838 IS
2839 l_gtin_dml_str VARCHAR2(2000);
2840 BEGIN
2841 x_return_status := FND_API.G_RET_STS_SUCCESS;
2842
2843 -- for now just update
2844 l_gtin_dml_str := 'UPDATE ego_item_gtn_attrs_b SET ';
2845 l_gtin_dml_str := l_gtin_dml_str || 'TOP_GTIN = ''' || p_top_gtin_flag || '''';
2846 l_gtin_dml_str := l_gtin_dml_str ||
2847 ' WHERE inventory_item_id = :item_id ' ||
2848 ' AND organization_id = :org_id ' ;
2849 /* Commented by snelloli as we have to update with empty string for 'N'
2850 TOP_GTIN != '' does not return any rows and the update fails
2851 ||
2852 ' AND (TOP_GTIN IS NULL OR TOP_GTIN <> ''' || p_top_gtin_flag || ''')';
2853 */
2854
2855 WRITE_DEBUG_LOG('Set_Top_GTIN_Flag: executing '||l_gtin_dml_str||' for '||p_inventory_item_id||'-'||p_organization_id);
2856 EXECUTE IMMEDIATE l_gtin_dml_str USING p_inventory_item_id, p_organization_id;
2857
2858 END Set_Top_GTIN_Flag;
2859
2860 --------------------------------
2861
2862 Function Is_Attribute_Group_Associated
2863 ( p_application_id IN NUMBER
2864 , p_attr_group_type IN VARCHAR2
2865 , p_attr_group_name IN VARCHAR2
2866 , p_inventory_item_id IN NUMBER
2867 , p_organization_id IN NUMBER
2868 )
2869 RETURN BOOLEAN
2870 IS
2871
2872 l_temp VARCHAR2(1);
2873
2874 BEGIN
2875
2876 SELECT 'X' into l_temp
2877 FROM
2878 EGO_OBJ_AG_ASSOCS_B A,
2879 EGO_FND_DSC_FLX_CTX_EXT EXT
2880 WHERE
2881 A.ATTR_GROUP_ID = EXT.ATTR_GROUP_ID
2882 AND EXT.APPLICATION_ID = p_application_id
2883 and EXT.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
2884 and EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
2885 and A.CLASSIFICATION_CODE IN
2886 ( SELECT ITEM_CATALOG_GROUP_ID
2887 FROM MTL_ITEM_CATALOG_GROUPS_B
2888 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
2889 START WITH ITEM_CATALOG_GROUP_ID = (select item_catalog_group_id
2890 from mtl_system_items_b
2891 where inventory_item_id = p_inventory_item_id
2892 and organization_id = p_organization_id) );
2893 RETURN(TRUE);
2894
2895 EXCEPTION
2896 WHEN NO_DATA_FOUND THEN
2897 RETURN(FALSE);
2898 WHEN OTHERS THEN
2899 RETURN(FALSE);
2900
2901 END IS_Attribute_Group_Associated;
2902
2903
2904 PROCEDURE Seed_Uccnet_Attributes_Pages
2905
2906 IS
2907
2908 TYPE t_NumberTbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2909 TYPE t_PageNameTbl IS TABLE OF EGO_PAGES_TL.DISPLAY_NAME%TYPE INDEX BY BINARY_INTEGER;
2910 TYPE t_AttrGroupNameTbl IS TABLE OF EGO_FND_DSC_FLX_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE INDEX BY BINARY_INTEGER;
2911 TYPE t_AttrGroupTypeTbl IS TABLE OF EGO_FND_DSC_FLX_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME%TYPE INDEX BY BINARY_INTEGER;
2912
2913 -- CONSTANTS
2914 G_EGO_APP_ID CONSTANT NUMBER := 431;
2915 G_ITEM_OBJECT_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM';
2916 G_ITEM_LEVEL CONSTANT VARCHAR2(150) := 'ITEM_LEVEL';
2917
2918 -- Tables to hold the bulk collected values
2919 l_CatalogGroupIds t_NumberTbl;
2920 l_PageIds t_NumberTbl;
2921 l_AssocIds t_NumberTbl;
2922 l_PageIndexes t_NumberTbl;
2923 l_AttrGroupIds t_NumberTbl;
2924 l_AttrGroupNames t_AttrGroupNameTbl;
2925 l_AttrGroupTypes t_AttrGroupTypeTbl;
2926 l_EntrySeqs t_NumberTbl;
2927 l_PageNames t_PageNameTbl;
2928 l_PageSeqs t_NumberTbl;
2929 l_AssocPageIds t_NumberTbl;
2930 l_TmpAttrGrpNames t_AttrGroupNameTbl;
2931 l_TmpAttrGrpTypes t_AttrGroupTypeTbl;
2932 l_TmpAttrGrpIds t_NumberTbl;
2933 l_TmpPageNames t_PageNameTbl;
2934 l_TmpPageIds t_NumberTbl;
2935
2936
2937 --local variables
2938 l_attr_group_id NUMBER;
2939 l_attr_group_name VARCHAR2(30);
2940 l_item_catalog_group_id NUMBER;
2941 l_object_id NUMBER;
2942 l_page_sequence NUMBER;
2943 l_entry_sequence NUMBER;
2944 l_page_name VARCHAR2(240);
2945
2946 l_classification_code VARCHAR2(150);
2947 l_attr_grp_id_list VARCHAR2(2000);
2948
2949 l_index NUMBER;
2950 l_zeroeth_assoc_exists NUMBER;
2951 l_no_run BOOLEAN;
2952 l_loop_index NUMBER;
2953 l_hash NUMBER;
2954 l_page_index NUMBER;
2955
2956 l_current_user_id NUMBER := FND_GLOBAL.User_Id;
2957 l_current_login_id NUMBER := FND_GLOBAL.Login_Id;
2958 l_sysdate DATE := SYSDATE;
2959 l_attr_group_found BOOLEAN;
2960 j NUMBER;
2961 l_mssg_text VARCHAR2(2000);
2962
2963 -- status params
2964 x_return_status VARCHAR2(1);
2965 x_page_id NUMBER;
2966 x_association_id NUMBER;
2967 x_errorcode VARCHAR2(1);
2968 x_msg_count NUMBER;
2969 x_msg_data VARCHAR2(2000);
2970
2971 --errors
2972 e_exception EXCEPTION;
2973 BEGIN
2974 -- check if script has already been run
2975 BEGIN
2976 --dbms_output.put_line('in run11 ');
2977 select 1
2978 into l_zeroeth_assoc_exists
2979 from EGO_FND_DSC_FLX_CTX_EXT ext,
2980 EGO_OBJ_AG_ASSOCS_B assocs
2981 where assocs.classification_code = '-1'
2982 and assocs.object_id = (select object_id from fnd_objects where obj_name = 'EGO_ITEM')
2983 and assocs.attr_group_id =ext.attr_group_id
2984 and ext.DESCRIPTIVE_FLEXFIELD_NAME in ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
2985 and ext.application_id = G_EGO_APP_ID
2986 and rownum<2;
2987 IF( l_zeroeth_assoc_exists = 1 ) THEN
2988 l_no_run := true;
2989 ELSE
2990 --script has already been run
2991 l_no_run := false;
2992 END IF;
2993 --dbms_output.put_line('in run112 ' || l_zeroeth_assoc_exists);
2994
2995 EXCEPTION
2996 WHEN NO_DATA_FOUND THEN
2997 l_no_run := false;
2998 END;
2999
3000 IF (l_no_run = false ) THEN
3001 --dbms_output.put_line('in run');
3002 -- Since already we have decided the sequence of attribute groups hard code the values.
3003 -- The Entry sequence values are based on the attribute group sequence in the page.
3004 -- Page Indexes specifies in which page the attribute group should appear.
3005
3006
3007 --UCCnet Physical Attributes EGO_UCCNET_PHYSICAL_ATTRIBUTES
3008 l_PageIndexes(1) := 1;
3009 l_AttrGroupNames(1) := 'Trade_Item_Description';
3010 l_AttrGroupTypes(1) := 'EGO_ITEM_GTIN_ATTRS';
3011 l_EntrySeqs(1) := 10;
3012
3013 l_PageIndexes(2) := 1;
3014 l_AttrGroupNames(2) := 'Trade_Item_Measurements';
3015 l_AttrGroupTypes(2) := 'EGO_ITEM_GTIN_ATTRS';
3016 l_EntrySeqs(2) := 20;
3017
3018 l_PageIndexes(3) := 1;
3019 l_AttrGroupNames(3) := 'Temperature_Information';
3020 l_AttrGroupTypes(3) := 'EGO_ITEM_GTIN_ATTRS';
3021 l_EntrySeqs(3) := 30;
3022
3023 l_PageIndexes(4) := 1;
3024 l_AttrGroupNames(4) := 'Trade_Item_Marking';
3025 l_AttrGroupTypes(4) := 'EGO_ITEM_GTIN_ATTRS';
3026 l_EntrySeqs(4) := 40;
3027
3028 l_PageIndexes(5) := 1;
3029 l_AttrGroupNames(5) := 'Gtin_Unit_Indicator';
3030 l_AttrGroupTypes(5) := 'EGO_ITEM_GTIN_ATTRS';
3031 l_EntrySeqs(5) := 50;
3032
3033 l_PageIndexes(6) := 1;
3034 l_AttrGroupNames(6) := 'Uccnet_Size_Description';
3035 l_AttrGroupTypes(6) := 'EGO_ITEM_GTIN_ATTRS';
3036 l_EntrySeqs(6) := 60;
3037
3038 l_PageIndexes(7) := 1;
3039 l_AttrGroupNames(7) := 'Material_Safety_Data';
3040 l_AttrGroupTypes(7) := 'EGO_ITEM_GTIN_ATTRS';
3041 l_EntrySeqs(7) := 70;
3042
3043 l_PageIndexes(8) := 1;
3044 l_AttrGroupNames(8) := 'Gtin_Color_Description';
3045 l_AttrGroupTypes(8) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3046 l_EntrySeqs(8) := 80;
3047
3048 l_PageIndexes(9) := 1;
3049 l_AttrGroupNames(9) := 'Manufacturing_Info';
3050 l_AttrGroupTypes(9) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3051 l_EntrySeqs(9) := 90;
3052
3053 l_PageIndexes(10) := 1;
3054 l_AttrGroupNames(10) := 'Country_Of_Origin';
3055 l_AttrGroupTypes(10) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3056 l_EntrySeqs(10) := 100;
3057
3058
3059 --UCCnet Order Information EGO_UCCNET_ORDER_INFORMATION
3060 l_PageIndexes(11) := 2;
3061 l_AttrGroupNames(11) := 'Order_Information';
3062 l_AttrGroupTypes(11) := 'EGO_ITEM_GTIN_ATTRS';
3063 l_EntrySeqs(11) := 10;
3064
3065 l_PageIndexes(12) := 2;
3066 l_AttrGroupNames(12) := 'Price_Information';
3067 l_AttrGroupTypes(12) := 'EGO_ITEM_GTIN_ATTRS';
3068 l_EntrySeqs(12) := 20;
3069
3070 l_PageIndexes(13) := 2;
3071 l_AttrGroupNames(13) := 'Price_Date_Information';
3072 l_AttrGroupTypes(13) := 'EGO_ITEM_GTIN_ATTRS';
3073 l_EntrySeqs(13) := 30;
3074
3075 l_PageIndexes(14) := 2;
3076 l_AttrGroupNames(14) := 'Date_Information';
3077 l_AttrGroupTypes(14) := 'EGO_ITEM_GTIN_ATTRS';
3078 l_EntrySeqs(14) := 40;
3079
3080
3081 --UCCnet Packaging EGO_UCCNET_PACKAGING
3082 l_PageIndexes(15) := 3;
3083 l_AttrGroupNames(15) := 'Packaging_Marking';
3084 l_AttrGroupTypes(15) := 'EGO_ITEM_GTIN_ATTRS';
3085 l_EntrySeqs(15) := 10;
3086
3087 l_PageIndexes(16) := 3;
3088 l_AttrGroupNames(16) := 'Trade_Item_Hierarchy';
3089 l_AttrGroupTypes(16) := 'EGO_ITEM_GTIN_ATTRS';
3090 l_EntrySeqs(16) := 20;
3091
3092 l_PageIndexes(17) := 3;
3093 l_AttrGroupNames(17) := 'Bar_Code';
3094 l_AttrGroupTypes(17) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3095 l_EntrySeqs(17) := 30;
3096
3097 l_PageIndexes(18) := 3;
3098 l_AttrGroupNames(18) := 'Handling_Information';
3099 l_AttrGroupTypes(18) := 'EGO_ITEM_GTIN_ATTRS';
3100 l_EntrySeqs(18) := 40;
3101
3102 l_PageIndexes(19) := 3;
3103 l_AttrGroupNames(19) := 'Handling_Information';
3104 l_AttrGroupTypes(19) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3105 l_EntrySeqs(19) := 50;
3106
3107 l_PageIndexes(20) := 3;
3108 l_AttrGroupNames(20) := 'Security_Tag';
3109 l_AttrGroupTypes(20) := 'EGO_ITEM_GTIN_ATTRS';
3110 l_EntrySeqs(20) := 50;
3111
3112 --UCCnet Hazardous EGO_UCCNET_HAZARDOUS
3113 l_PageIndexes(21) := 4;
3114 l_AttrGroupNames(21) := 'Hazardous_Information';
3115 l_AttrGroupTypes(21) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3116 l_EntrySeqs(21) := 10;
3117
3118 -- Bug: 4027782 - Attribute group without any pages
3119 l_AttrGroupNames(22) := 'Delivery_Method_Indicator';
3120 l_AttrGroupTypes(22) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3121
3122 l_AttrGroupNames(23) := 'Size_Description';
3123 l_AttrGroupTypes(23) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3124 -- end Bug: 4027782
3125
3126 --UCCnet Industry EGO_UCCNET_INDUSTRY
3127 /*l_PageIndexes(21) := 5;
3128 l_AttrGroupNames(21) := 'Uccnet_Hardlines';
3129 l_AttrGroupTypes(21) := 'EGO_ITEM_GTIN_ATTRS';
3130 l_EntrySeqs(21) := 10;*/
3131
3132 /*l_PageIndexes(21) := 5;
3133 l_AttrGroupNames(21) := 'TRADE_ITEM_HARMN_SYS_IDENT';
3134 l_AttrGroupTypes(21) := 'EGO_ITEM_GTIN_MULTI_ATTRS';
3135 l_EntrySeqs(21) := 20;
3136
3137 l_PageIndexes(21) := 5;
3138 l_AttrGroupNames(21) := 'FMCG_MARKING';
3139 l_AttrGroupTypes(21) := 'EGO_ITEM_GTIN_ATTRS';
3140 l_EntrySeqs(21) := 30;
3141
3142 l_PageIndexes(22) := 5;
3143 l_AttrGroupNames(22) := 'FMCG_Measurements';
3144 l_AttrGroupTypes(22) := 'EGO_ITEM_GTIN_ATTRS';
3145 l_EntrySeqs(22) := 40;
3146
3147 l_PageIndexes(23) := 5;
3148 l_AttrGroupNames(23) := 'FMCG_Identification';
3149 l_AttrGroupTypes(23) := 'EGO_ITEM_GTIN_ATTRS';
3150 l_EntrySeqs(23) := 50;*/
3151
3152
3153 -- define page order
3154 -- Hard code page sequence values. Because it's not going to change.
3155
3156 --UCCnet Physical Attributes EGO_UCCNET_PHYSICAL_ATTRIBUTES
3157 --UCCnet Order Information EGO_UCCNET_ORDER_INFORMATION
3158 --UCCnet Packaging EGO_UCCNET_PACKAGING
3159 --UCCnet Hazardous EGO_UCCNET_HAZARDOUS
3160 --UCCnet Industry EGO_UCCNET_INDUSTRY
3161
3162 l_PageNames(1) := 'EGO_UCCNET_PHYSICAL_ATTRIBUTES';
3163 l_PageSeqs(1) := 10;
3164 l_PageNames(2) := 'EGO_UCCNET_ORDER_INFORMATION';
3165 l_PageSeqs(2) := 20;
3166 l_PageNames(3) := 'EGO_UCCNET_PACKAGING';
3167 l_PageSeqs(3) := 30;
3168 l_PageNames(4) := 'EGO_UCCNET_HAZARDOUS';
3169 l_PageSeqs(4) := 40;
3170 --l_PageNames(5) := 'EGO_UCCNET_INDUSTRY';
3171 --l_PageSeqs(5) := 50;
3172
3173 --select all item catalog groups without parents
3174 l_CatalogGroupIds(1) := -1;
3175
3176 -- Do a bulk collect for catalog groups without parent and without any UCCnet associations
3177 SELECT mi.item_catalog_group_id
3178 BULK COLLECT INTO l_CatalogGroupIds
3179 FROM mtl_item_catalog_groups_b mi
3180 WHERE mi.parent_catalog_group_id IS NULL
3181 AND NOT EXISTS
3182 (SELECT oa.attr_group_id
3183 FROM ego_obj_ag_assocs_b oa,
3184 ego_attr_groups_v eag
3185 WHERE oa.classification_code = mi.item_catalog_group_id
3186 AND oa.attr_group_id = eag.attr_group_id
3187 AND eag.attr_group_type IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS'));
3188
3189 l_CatalogGroupIds(l_CatalogGroupIds.count +1) := -1;
3190 l_object_id := EGO_EXT_FWK_PUB.Get_Object_Id_From_Name (p_object_name => G_ITEM_OBJECT_NAME) ;
3191
3192 --dbms_output.put_line('after selecting all catalogs');
3193 -- Since same attribute group names are used for all catalog groups fetch it before
3194 -- looping through catalog groups.
3195 SELECT ATTR_GROUP_ID, DESCRIPTIVE_FLEX_CONTEXT_CODE, DESCRIPTIVE_FLEXFIELD_NAME
3196 BULK COLLECT INTO l_TmpAttrGrpIds, l_TmpAttrGrpNames, l_TmpAttrGrpTypes
3197 FROM EGO_FND_DSC_FLX_CTX_EXT
3198 -- Attribute Group Type
3199 WHERE DESCRIPTIVE_FLEXFIELD_NAME in ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
3200 AND application_id = G_EGO_APP_ID
3201 -- Attribute Group Name
3202 AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN
3203 ( 'Trade_Item_Description'
3204 , 'Trade_Item_Measurements'
3205 , 'Temperature_Information'
3206 , 'Trade_Item_Marking'
3207 , 'Gtin_Unit_Indicator'
3208 , 'Uccnet_Size_Description'
3209 , 'Material_Safety_Data'
3210 , 'Gtin_Color_Description'
3211 , 'Manufacturing_Info'
3212 , 'Country_Of_Origin'
3213 , 'Order_Information'
3214 , 'Price_Information'
3215 , 'Price_Date_Information'
3216 , 'Date_Information'
3217 , 'Packaging_Marking'
3218 , 'Trade_Item_Hierarchy'
3219 , 'Bar_Code'
3220 , 'Handling_Information'
3221 , 'Hazardous_Information'
3222 , 'Size_Description' -- Bug: 4027782
3223 , 'Delivery_Method_Indicator' -- Bug: 4027782
3224 , 'Security_Tag'
3225 -- , 'Uccnet_Hardlines'
3226 -- , 'TRADE_ITEM_HARMN_SYS_IDENT'
3227 -- , 'FMCG_MARKING'
3228 -- , 'FMCG_Measurements'
3229 -- , 'FMCG_Identification'
3230 );
3231 --dbms_output.put_line('after selecting attr group ids');
3232
3233 -- Fetching can be in any order. So populate the attribute group ids based on the sequence
3234 -- by performing linear search.
3235 FOR i IN l_AttrGroupNames.FIRST..l_AttrGroupNames.LAST LOOP
3236 j := 1;
3237 l_attr_group_found := FALSE;
3238 WHILE NOT l_attr_group_found LOOP
3239 IF (l_AttrGroupNames(i) = l_TmpAttrGrpNames(j) AND l_AttrGroupTypes(i) = l_TmpAttrGrpTypes(j)) THEN
3240 l_AttrGroupIds(i) := l_TmpAttrGrpIds(j);
3241 l_attr_group_found := TRUE;
3242 END IF;
3243 j := j + 1;
3244 END LOOP;
3245 END LOOP;
3246 --dbms_output.put_line('after setting attr group ids');
3247 --dbms_output.put_line('l_CatalogGroupIds count' || l_CatalogGroupIds.count);
3248 -- create default operational attribute pages for each item catalog category without a parent
3249 FOR l_loop_index IN l_CatalogGroupIds.FIRST..l_CatalogGroupIds.LAST
3250 LOOP
3251 l_item_catalog_group_id := l_CatalogGroupIds(l_loop_index);
3252 --dbms_output.put_line('getting l_item_catalog_group_id' || l_item_catalog_group_id);
3253 -- Tables uses classification_code which is varchar2 column. So type cast catalog group id.
3254 l_classification_code := TO_CHAR(l_item_catalog_group_id);
3255
3256 BEGIN
3257 -- get the sequence for this page
3258 l_page_sequence := 0;
3259 BEGIN
3260 SELECT max(sequence) INTO l_page_sequence
3261 FROM EGO_PAGES_B
3262 WHERE object_id = l_object_id
3263 AND classification_code = l_classification_code;
3264
3265 IF( l_page_sequence IS NULL ) THEN
3266 l_page_sequence := 0;
3267 END IF;
3268 EXCEPTION
3269 WHEN NO_DATA_FOUND THEN
3270 l_page_sequence := 0;
3271 END;
3272
3273 -- We have to create all 5 pages for this catalog group.
3274 -- Perform bulk insert for these 5 pages.
3275 FORALL i IN l_PageNames.FIRST..l_PageNames.LAST
3276 INSERT INTO EGO_PAGES_B
3277 (
3278 PAGE_ID
3279 ,OBJECT_ID
3280 ,CLASSIFICATION_CODE
3281 ,DATA_LEVEL
3282 ,INTERNAL_NAME
3283 ,SEQUENCE
3284 ,CREATION_DATE
3285 ,CREATED_BY
3286 ,LAST_UPDATE_DATE
3287 ,LAST_UPDATED_BY
3288 ,LAST_UPDATE_LOGIN
3289 )
3290 SELECT EGO_PAGES_S.NEXTVAL
3291 ,l_object_id
3292 ,l_classification_code
3293 ,G_ITEM_LEVEL
3294 ,l_PageNames(i)
3295 ,l_PageSeqs(i) + l_page_sequence
3296 ,l_sysdate
3297 ,l_current_user_id
3298 ,l_sysdate
3299 ,l_current_user_id
3300 ,L_current_login_id
3301 FROM DUAL
3302 WHERE NOT EXISTS (
3303 SELECT *
3304 FROM EGO_PAGES_V
3305 WHERE CLASSIFICATION_CODE = l_classification_code
3306 AND INTERNAL_NAME = l_PageNames(i)
3307 );
3308
3309 --dbms_output.put_line('after inserting pages');
3310
3311 -- TL table requires Page_Id, so perform bulk collect to get page ids for
3312 -- the inserted pages.
3313 SELECT page_id, internal_name
3314 BULK COLLECT INTO l_TmpPageIds, l_TmpPageNames
3315 FROM EGO_PAGES_B
3316 WHERE OBJECT_ID = l_object_id
3317 AND CLASSIFICATION_CODE = l_classification_code
3318 AND SEQUENCE > l_page_sequence -- Need to get only newly inserted rows
3319 ORDER BY SEQUENCE; -- Make sure to get it in the order in which have been inserted
3320
3321 l_PageIds.DELETE;
3322 -- Associate the correct page IDs with the correct index
3323 IF (l_TmpPageIds.COUNT > 0) THEN
3324 FOR i IN l_TmpPageIds.FIRST..l_TmpPageIds.LAST
3325 LOOP
3326 FOR j IN l_PageNames.FIRST..l_PageNames.LAST
3327 LOOP
3328 IF (l_TmpPageNames(i) = l_PageNames(j)) THEN
3329 l_PageIds(j) := l_TmpPageIds(i);
3330 EXIT;
3331 END IF;
3332 END LOOP;
3333 END LOOP;
3334 END IF;
3335
3336 -- ensure there are actually pages to insert into the TL table
3337 IF (l_PageIds.COUNT > 0) THEN
3338 -- Perform bulk insert for TL table.
3339 FOR i IN l_PageIds.FIRST..l_PageIds.LAST
3340 LOOP
3341 SELECT message_text
3342 INTO l_mssg_text
3343 FROM fnd_new_messages
3344 WHERE message_name = l_PageNames(i)
3345 AND application_id = G_EGO_APP_ID
3346 AND language_code = USERENV('LANG');
3347 --dbms_output.put_line('inserting...');
3348 INSERT INTO EGO_PAGES_TL
3349 (
3350 PAGE_ID
3351 ,DISPLAY_NAME
3352 ,LANGUAGE
3353 ,SOURCE_LANG
3354 ,CREATION_DATE
3355 ,CREATED_BY
3356 ,LAST_UPDATE_DATE
3357 ,LAST_UPDATED_BY
3358 ,LAST_UPDATE_LOGIN
3359 )
3360 SELECT
3361 l_PageIds(i)
3362 ,l_mssg_text
3363 ,L.LANGUAGE_CODE
3364 ,USERENV('LANG')
3365 ,l_Sysdate
3366 ,l_current_user_id
3367 ,l_Sysdate
3368 ,l_current_user_id
3369 ,l_current_login_id
3370 FROM FND_LANGUAGES L
3371 WHERE L.INSTALLED_FLAG in ('I', 'B');
3372
3373 END LOOP;
3374 END IF;
3375
3376 --dbms_output.put_line('after inserting tl pages');
3377 -- Create Page Association
3378 -- For each (total 15) attribute group create an association with the current catalog group.
3379 FORALL i IN l_AttrGroupIds.FIRST..l_AttrGroupIds.LAST
3380 INSERT INTO EGO_OBJ_AG_ASSOCS_B
3381 (
3382 ASSOCIATION_ID
3383 ,OBJECT_ID
3384 ,CLASSIFICATION_CODE
3385 ,DATA_LEVEL
3386 ,ATTR_GROUP_ID
3387 ,ENABLED_FLAG
3388 ,VIEW_PRIVILEGE_ID
3389 ,EDIT_PRIVILEGE_ID
3390 ,CREATION_DATE
3391 ,CREATED_BY
3392 ,LAST_UPDATE_DATE
3393 ,LAST_UPDATED_BY
3394 ,LAST_UPDATE_LOGIN
3395 )
3396 VALUES
3397 (
3398 EGO_ASSOCS_S.NEXTVAL
3399 ,l_object_id
3400 ,l_classification_code
3401 ,G_ITEM_LEVEL
3402 ,l_AttrGroupIds(i)
3403 ,'Y'
3404 ,to_number(NULL)
3405 ,to_number(NULL)
3406 ,l_Sysdate
3407 ,l_current_user_id
3408 ,l_Sysdate
3409 ,l_current_user_id
3410 ,l_current_login_id
3411 );
3412
3413 --dbms_output.put_line('after inserting assocs');
3414 -- Bulk fetch the association ids for the association which have created.
3415 l_attr_grp_id_list :='';
3416 FOR i IN l_AttrGroupIds.FIRST..l_AttrGroupIds.LAST
3417 LOOP
3418 l_attr_grp_id_list := l_attr_grp_id_list || l_AttrGroupIds(i);
3419
3420 IF (i <> l_AttrGroupIds.count) THEN
3421 l_attr_grp_id_list := l_attr_grp_id_list || ',';
3422 END IF;
3423 END LOOP;
3424
3425 --dbms_output.put_line('after getting attr grp id list ' || l_attr_grp_id_list);
3426
3427 SELECT ASSOCIATION_ID -- , ATTR_GROUP_ID
3428 BULK COLLECT INTO l_AssocIds --, l_AttrGroupIds
3429 FROM EGO_OBJ_AG_ASSOCS_B
3430 WHERE OBJECT_ID = l_object_id
3431 AND CLASSIFICATION_CODE = l_classification_code
3432 and ATTR_GROUP_ID in (SELECT ATTR_GROUP_ID FROM EGO_FND_DSC_FLX_CTX_EXT
3433 WHERE DESCRIPTIVE_FLEXFIELD_NAME in ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
3434 AND application_id = G_EGO_APP_ID
3435 AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN
3436 ( 'Trade_Item_Description'
3437 , 'Trade_Item_Measurements'
3438 , 'Temperature_Information'
3439 , 'Trade_Item_Marking'
3440 , 'Gtin_Unit_Indicator'
3441 , 'Uccnet_Size_Description'
3442 , 'Material_Safety_Data'
3443 , 'Gtin_Color_Description'
3444 , 'Manufacturing_Info'
3445 , 'Country_Of_Origin'
3446 , 'Order_Information'
3447 , 'Price_Information'
3448 , 'Price_Date_Information'
3449 , 'Date_Information'
3450 , 'Packaging_Marking'
3451 , 'Trade_Item_Hierarchy'
3452 , 'Bar_Code'
3453 , 'Handling_Information'
3454 , 'Hazardous_Information'
3455 , 'Security_Tag'
3456 --, 'Uccnet_Hardlines'
3457 --, 'TRADE_ITEM_HARMN_SYS_IDENT'
3458 --, 'FMCG_MARKING'
3459 --, 'FMCG_Measurements'
3460 --, 'FMCG_Identification'
3461 ))
3462 ORDER BY ASSOCIATION_ID;
3463
3464 --dbms_output.put_line('after getting assoc ids');
3465 -- Populate l_AssocPageIds with page id to which the attribute group belongs to.
3466 FOR i IN l_PageIndexes.FIRST..l_PageIndexes.LAST
3467 LOOP
3468 BEGIN
3469 l_AssocPageIds(i) := l_PageIds(l_PageIndexes(i));
3470 EXCEPTION
3471 WHEN NO_DATA_FOUND THEN
3472 -- if no page ID can be found, then skip the page entry.
3473 NULL;
3474 END;
3475 END LOOP;
3476
3477 --dbms_output.put_line('after getting page ids');
3478 -- Perform Bulk insert and create page entries for each association.
3479 --dbms_output.put_line('assoc cound -' || l_AssocIds.count);
3480 FORALL i IN l_AssocIds.FIRST..l_AssocIds.LAST
3481 INSERT INTO EGO_PAGE_ENTRIES_B
3482 (
3483 PAGE_ID
3484 ,ASSOCIATION_ID
3485 ,SEQUENCE
3486 ,CLASSIFICATION_CODE
3487 ,CREATION_DATE
3488 ,CREATED_BY
3489 ,LAST_UPDATE_DATE
3490 ,LAST_UPDATED_BY
3491 ,LAST_UPDATE_LOGIN
3492 )
3493 VALUES
3494 (
3495 l_AssocPageIds(i)
3496 ,l_AssocIds(i)
3497 ,l_EntrySeqs(i)
3498 ,l_classification_code
3499 ,l_Sysdate
3500 ,l_current_user_id
3501 ,l_Sysdate
3502 ,l_current_user_id
3503 ,l_current_login_id
3504 );
3505 --dbms_output.put_line('after inserting page entries for classification' || l_classification_code);
3506
3507 --EXCEPTION
3508 -- WHEN OTHERS THEN
3509 -- ROLLBACK;
3510 END;
3511 END LOOP; -- for all item catalog categories w/o parents and w/o UCCnet attr groups
3512 COMMIT; -- Commit only after processing all the catalog groups.
3513 END IF; --check if script has already run
3514
3515 END Seed_Uccnet_Attributes_Pages;
3516
3517 FUNCTION Is_In_Sync_Customer
3518 ( p_inventory_item_id IN NUMBER
3519 , p_org_id IN NUMBER
3520 , p_address_id IN NUMBER
3521 , p_explode_group_id IN NUMBER
3522 ) RETURN VARCHAR2
3523 IS
3524 l_result VARCHAR2(1);
3525 BEGIN
3526 SELECT 'Y' INTO l_result
3527 FROM DUAL
3528 WHERE NOT EXISTS
3529 (
3530 SELECT
3531 1
3532 FROM EGO_UCCNET_EVENTS EV1
3533 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3534 AND ORGANIZATION_ID = p_org_id
3535 AND ADDRESS_ID = p_address_id
3536 AND EVENT_TYPE = 'PUBLICATION'
3537 AND PARENT_GTIN = 0
3538 AND
3539 (
3540 DISPOSITION_CODE IS NULL
3541 OR
3542 (
3543 EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
3544 AND DISPOSITION_CODE = 'REJECTED'
3545 )
3546 OR
3547 (
3548 EVENT_ACTION = 'DE_LIST'
3549 AND DISPOSITION_CODE <> 'FAILED'
3550 )
3551 OR
3552 (
3553 EVENT_ACTION = 'WITHDRAW'
3554 AND DISPOSITION_CODE <> 'FAILED'
3555 AND NOT EXISTS
3556 (
3557 SELECT
3558 1
3559 FROM EGO_UCCNET_EVENTS
3560 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3561 AND ORGANIZATION_ID = p_org_id
3562 AND ADDRESS_ID = p_address_id
3563 AND EVENT_TYPE = 'PUBLICATION'
3564 AND PARENT_GTIN = 0
3565 AND EVENT_ROW_ID > EV1.EVENT_ROW_ID
3566 AND DISPOSITION_CODE <> 'FALIED'
3567 )
3568 )
3569 )
3570 )
3571 AND EXISTS
3572 (
3573 SELECT
3574 1
3575 FROM EGO_ITEM_GTN_ATTRS_B EGA,
3576 BOM_EXPLOSIONS_ALL_V EXPL
3577 WHERE EXPL.GROUP_ID = p_explode_group_id
3578 AND EXPL.TOP_ITEM_ID = p_inventory_item_id
3579 AND EXPL.ORGANIZATION_ID = EGA.ORGANIZATION_ID
3580 AND EXPL.COMPONENT_ITEM_ID = EGA.INVENTORY_ITEM_ID
3581 AND TP_NEUTRAL_UPDATE_DATE >
3582 (
3583 SELECT
3584 MAX(CREATION_DATE)
3585 FROM EGO_UCCNET_EVENTS
3586 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3587 AND ORGANIZATION_ID = p_org_id
3588 AND ADDRESS_ID = p_address_id
3589 AND EVENT_TYPE = 'PUBLICATION'
3590 AND PARENT_GTIN = 0
3591 AND EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
3592 AND DISPOSITION_CODE IN ('PROCESSED', 'ACCEPTED', 'REVIEW', 'SYNCHRONIZED')
3593 )
3594 );
3595 RETURN l_result;
3596
3597 EXCEPTION
3598 WHEN NO_DATA_FOUND THEN
3599 RETURN 'N';
3600 WHEN OTHERS THEN
3601 RETURN 'N';
3602 END Is_In_Sync_Customer;
3603
3604 FUNCTION Get_Attr_Display_Name(p_attr_group_type VARCHAR2,
3605 p_attr_group_name VARCHAR2,
3606 p_attr_name VARCHAR2)
3607 RETURN VARCHAR2 IS
3608 l_disp_name VARCHAR2(4000);
3609 BEGIN
3610 SELECT TL.FORM_LEFT_PROMPT ATTR_DISPLAY_NAME
3611 INTO l_disp_name
3612 FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL ,FND_DESCR_FLEX_COL_USAGE_TL TL
3613 WHERE FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
3614 AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
3615 AND FL_COL.APPLICATION_ID = 431
3616 AND FL_COL.END_USER_COLUMN_NAME = p_attr_name
3617 AND FL_COL.APPLICATION_ID = TL.APPLICATION_ID
3618 AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME
3619 AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
3620 AND FL_COL.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
3621 AND TL.LANGUAGE = USERENV('LANG');
3622
3623 RETURN l_disp_name;
3624 EXCEPTION WHEN NO_DATA_FOUND THEN
3625 RETURN p_attr_name;
3626 END Get_Attr_Display_Name;
3627
3628 /*
3629 * This method returns the page_id and page_display_name for the attribute group
3630 */
3631 PROCEDURE Get_Associated_Page_Details(p_catalog_group_id NUMBER,
3632 p_attr_group_type VARCHAR2,
3633 p_attr_group_name VARCHAR2,
3634 x_page_id OUT NOCOPY NUMBER,
3635 x_page_display_name OUT NOCOPY VARCHAR2)
3636 IS
3637 l_page_id NUMBER;
3638 l_page_disp_name VARCHAR2(4000);
3639 BEGIN
3640 SELECT PT.PAGE_ID, PT.DISPLAY_NAME
3641 INTO l_page_id, l_page_disp_name
3642 FROM EGO_FND_DSC_FLX_CTX_EXT EXT, EGO_OBJ_AG_ASSOCS_B ASOC, EGO_PAGE_ENTRIES_B PGE, EGO_PAGES_TL PT
3643 WHERE EXT.ATTR_GROUP_ID = ASOC.ATTR_GROUP_ID
3644 AND EXT.APPLICATION_ID = 431
3645 AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
3646 AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
3647 AND ASOC.OBJECT_ID = (SELECT OBJECT_ID FROM FND_OBJECTS WHERE OBJ_NAME = 'EGO_ITEM')
3648 AND ASOC.CLASSIFICATION_CODE = p_catalog_group_id
3649 AND ASOC.ASSOCIATION_ID = PGE.ASSOCIATION_ID
3650 AND ASOC.CLASSIFICATION_CODE = PGE.CLASSIFICATION_CODE
3651 AND PGE.PAGE_ID = PT.PAGE_ID
3652 AND PT.LANGUAGE = USERENV('LANG')
3653 AND ROWNUM = 1;
3654
3655 x_page_id := l_page_id;
3656 x_page_display_name := l_page_disp_name;
3657 EXCEPTION
3658 WHEN NO_DATA_FOUND THEN
3659 x_page_id := -1;
3660 x_page_display_name := NULL;
3661 END Get_Associated_Page_Details;
3662
3663 /*
3664 * This method validates SBDH attributes. if p_address_id is null then only item level attributes
3665 * are validated.
3666 * Returns a data object containing all errors
3667 */
3668 PROCEDURE Validate_SBDH_Attributes(p_inventory_item_id NUMBER,
3669 p_organization_id NUMBER,
3670 p_address_id NUMBER,
3671 p_errors OUT NOCOPY REF_CURSOR_TYPE)
3672 IS
3673 l_start_date DATE;
3674 l_end_date DATE;
3675 l_start_date1 DATE;
3676 l_end_date1 DATE;
3677 l_min_value NUMBER;
3678 l_max_value NUMBER;
3679 l_attr_value NUMBER;
3680 l_attr_value_uom VARCHAR2(10);
3681 l_attr1_value NUMBER;
3682 l_attr1_value_uom VARCHAR2(10);
3683
3684 l_sql VARCHAR2(15000);
3685 l_error_rec SYSTEM.EGO_PAGEWISE_ERROR_REC;
3686 l_error_table SYSTEM.EGO_PAGEWISE_ERROR_TABLE;
3687 l_unexp_err VARCHAR2(4000);
3688 l_attr1_name VARCHAR2(4000);
3689 l_attr2_name VARCHAR2(4000);
3690 l_attr3_name VARCHAR2(4000);
3691 l_peg_hole_number NUMBER;
3692 l_peg_vertical NUMBER;
3693 l_peg_horizontal NUMBER;
3694 l_catalog_group_id NUMBER;
3695 l_party_site_id NUMBER;
3696 l_page_id NUMBER;
3697 l_page_display_name VARCHAR2(4000);
3698 l_do_tp_validations BOOLEAN;
3699
3700 TYPE ref_cursor_type IS REF CURSOR;
3701 c_ref_cursor ref_cursor_type;
3702
3703 column_not_found EXCEPTION;
3704 PRAGMA EXCEPTION_INIT(column_not_found, -904);
3705 BEGIN
3706 l_error_table := SYSTEM.EGO_PAGEWISE_ERROR_TABLE();
3707 l_error_rec := SYSTEM.EGO_PAGEWISE_ERROR_REC(NULL, '', '', '');
3708
3709 BEGIN
3710 SELECT ITEM_CATALOG_GROUP_ID INTO l_catalog_group_id
3711 FROM MTL_SYSTEM_ITEMS_B
3712 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3713 AND ORGANIZATION_ID = p_organization_id;
3714 EXCEPTION WHEN OTHERS THEN
3715 l_error_table.EXTEND;
3716 l_error_rec.PAGE_ID := -1;
3717 l_error_rec.ERROR_MESSAGE := SQLERRM;
3718 l_error_table(l_error_table.LAST) := l_error_rec;
3719 OPEN p_errors FOR
3720 SELECT *
3721 FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );
3722 RETURN;
3723 END;
3724
3725 IF p_address_id IS NULL THEN
3726 l_do_tp_validations := FALSE;
3727 ELSE
3728 l_do_tp_validations := TRUE;
3729 END IF;
3730
3731 IF l_do_tp_validations THEN
3732 -- validating address_id
3733 BEGIN
3734 SELECT PARTY_SITE_ID INTO l_party_site_id
3735 FROM HZ_CUST_ACCT_SITES_ALL
3736 WHERE CUST_ACCT_SITE_ID = p_address_id;
3737 EXCEPTION
3738 WHEN NO_DATA_FOUND THEN
3739 l_error_table.EXTEND;
3740 l_error_rec.PAGE_ID := -1;
3741 l_error_rec.ERROR_MESSAGE := 'Unexpected Error: Invalid Address_ID - '||TO_CHAR(p_address_id);
3742 l_error_rec.ERROR_LEVEL := 'C';
3743 l_error_table(l_error_table.LAST) := l_error_rec;
3744 OPEN p_errors FOR
3745 SELECT *
3746 FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );
3747 RETURN;
3748 WHEN OTHERS THEN
3749 l_error_table.EXTEND;
3750 l_error_rec.PAGE_ID := -1;
3751 l_error_rec.ERROR_MESSAGE := 'Error while validating address: ' || SQLERRM;
3752 l_error_rec.ERROR_LEVEL := 'C';
3753 l_error_table(l_error_table.LAST) := l_error_rec;
3754 OPEN p_errors FOR
3755 SELECT *
3756 FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );
3757 RETURN;
3758 END;
3759 END IF; -- IF l_do_tp_validations THEN
3760
3761 -- validating first the attributes that are not TP-Dependant
3762 -- 1. DEPOSIT_VALUE_EFFECTIVE_DATE can not be greater than DEPOSIT_VALUE_END_DATE
3763 BEGIN
3764 l_sql := ' SELECT DEPOSIT_VALUE_EFFECTIVE_DATE, DEPOSIT_VALUE_END_DATE ' ||
3765 ' FROM EGO_SBDH_DEP_VAL_DATE_INFO_AGV ' ||
3766 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
3767 ' AND ORGANIZATION_ID = :p_organization_id';
3768 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date USING p_inventory_item_id, p_organization_id;
3769
3770 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
3771 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_DEP_VAL_DATE_INFO', l_page_id, l_page_display_name);
3772 l_error_table.EXTEND;
3773 l_error_rec.PAGE_ID := l_page_id;
3774 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
3775 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
3776 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_DEP_VAL_DATE_INFO', 'DEPOSIT_VALUE_EFFECTIVE_DATE');
3777 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_DEP_VAL_DATE_INFO', 'DEPOSIT_VALUE_END_DATE');
3778 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
3779 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
3780 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
3781 l_error_rec.ERROR_LEVEL := 'I';
3782 l_error_table(l_error_table.LAST) := l_error_rec;
3783 END IF;
3784 EXCEPTION
3785 WHEN NO_DATA_FOUND THEN
3786 NULL;
3787 WHEN OTHERS THEN
3788 l_unexp_err := 'EGOINT_GDSN_DEP_VAL_DATE_INFO - '||SQLERRM;
3789 l_error_table.EXTEND;
3790 l_error_rec.PAGE_ID := -1;
3791 l_error_rec.ERROR_MESSAGE := l_unexp_err;
3792 l_error_rec.ERROR_LEVEL := 'I';
3793 l_error_table(l_error_table.LAST) := l_error_rec;
3794 END;
3795
3796 -- 2. CAMPAIGN_START_DATE can not be greater than CAMPAIGN_END_DATE
3797 BEGIN
3798 l_sql := ' SELECT CAMPAIGN_START_DATE, CAMPAIGN_END_DATE ' ||
3799 ' FROM EGO_SBDH_CAMPAIGN_INFO_AGV ' ||
3800 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
3801 ' AND ORGANIZATION_ID = :p_organization_id';
3802 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date USING p_inventory_item_id, p_organization_id;
3803
3804 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
3805 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_CAMPAIGN_INFO', l_page_id, l_page_display_name);
3806 l_error_table.EXTEND;
3807 l_error_rec.PAGE_ID := l_page_id;
3808 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
3809 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
3810 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_CAMPAIGN_INFO', 'CAMPAIGN_START_DATE');
3811 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_CAMPAIGN_INFO', 'CAMPAIGN_END_DATE');
3812 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
3813 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
3814 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
3815 l_error_rec.ERROR_LEVEL := 'I';
3816 l_error_table(l_error_table.LAST) := l_error_rec;
3817 END IF;
3818 EXCEPTION
3819 WHEN NO_DATA_FOUND THEN
3820 NULL;
3821 WHEN OTHERS THEN
3822 l_unexp_err := 'EGOINT_GDSN_CAMPAIGN_INFO - '||SQLERRM;
3823 l_error_table.EXTEND;
3824 l_error_rec.PAGE_ID := -1;
3825 l_error_rec.ERROR_MESSAGE := l_unexp_err;
3826 l_error_rec.ERROR_LEVEL := 'I';
3827 l_error_table(l_error_table.LAST) := l_error_rec;
3828 END;
3829
3830 -- 3. SEASONAL_AVL_START_DATE can not be greater than SEASONAL_AVL_END_DATE
3831 BEGIN
3832 l_sql := ' SELECT SEASONAL_AVL_START_DATE, SEASONAL_AVL_END_DATE ' ||
3833 ' FROM EGO_SBDH_SEASON_AVL_DATE_AGV ' ||
3834 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
3835 ' AND ORGANIZATION_ID = :p_organization_id';
3836 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date USING p_inventory_item_id, p_organization_id;
3837
3838 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
3839 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_SEASON_AVL_DATE', l_page_id, l_page_display_name);
3840 l_error_table.EXTEND;
3841 l_error_rec.PAGE_ID := l_page_id;
3842 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
3843 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
3844 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_SEASON_AVL_DATE', 'SEASONAL_AVL_START_DATE');
3845 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_SEASON_AVL_DATE', 'SEASONAL_AVL_END_DATE');
3846 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
3847 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
3848 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
3849 l_error_rec.ERROR_LEVEL := 'I';
3850 l_error_table(l_error_table.LAST) := l_error_rec;
3851 END IF;
3852 EXCEPTION
3853 WHEN NO_DATA_FOUND THEN
3854 NULL;
3855 WHEN OTHERS THEN
3856 l_unexp_err := 'EGOINT_GDSN_SEASON_AVL_DATE - '||SQLERRM;
3857 l_error_table.EXTEND;
3858 l_error_rec.PAGE_ID := -1;
3859 l_error_rec.ERROR_MESSAGE := l_unexp_err;
3860 l_error_rec.ERROR_LEVEL := 'I';
3861 l_error_table(l_error_table.LAST) := l_error_rec;
3862 END;
3863
3864 -- 4. STORAGE_HNDLNG_HUMDTY_MIN can not be greater than STORAGE_HNDLNG_HUMDTY_MAX
3865 -- this is a multi-row attribute group, so using ref cursor.
3866 BEGIN
3867 l_page_id := -99;
3868 l_page_display_name := NULL;
3869
3870 l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX ' ||
3871 ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
3872 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
3873 ' AND ORGANIZATION_ID = :p_organization_id';
3874 OPEN c_ref_cursor FOR l_sql USING p_inventory_item_id, p_organization_id;
3875 LOOP
3876 FETCH c_ref_cursor INTO l_min_value, l_max_value;
3877 EXIT WHEN c_ref_cursor%NOTFOUND;
3878
3879 IF l_min_value > l_max_value THEN
3880 IF l_page_id = -99 THEN
3881 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', l_page_id, l_page_display_name);
3882 END IF;
3883 l_error_table.EXTEND;
3884 l_error_rec.PAGE_ID := l_page_id;
3885 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
3886 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
3887 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', 'STORAGE_HNDLNG_HUMDTY_MIN');
3888 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', 'STORAGE_HNDLNG_HUMDTY_MAX');
3889 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
3890 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
3891 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
3892 l_error_rec.ERROR_LEVEL := 'I';
3893 l_error_table(l_error_table.LAST) := l_error_rec;
3894 END IF;
3895 END LOOP;
3896 CLOSE c_ref_cursor;
3897 EXCEPTION
3898 WHEN NO_DATA_FOUND THEN
3899 IF c_ref_cursor%ISOPEN THEN
3900 CLOSE c_ref_cursor;
3901 END IF;
3902 WHEN OTHERS THEN
3903 IF c_ref_cursor%ISOPEN THEN
3904 CLOSE c_ref_cursor;
3905 END IF;
3906 l_unexp_err := 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY - '||SQLERRM;
3907 l_error_table.EXTEND;
3908 l_error_rec.PAGE_ID := -1;
3909 l_error_rec.ERROR_MESSAGE := l_unexp_err;
3910 l_error_rec.ERROR_LEVEL := 'I';
3911 l_error_table(l_error_table.LAST) := l_error_rec;
3912 END;
3913
3914 -- 5. pegHorizontal, pegVertical and pegHoleNumber should be specified when either of them is specified.
3915 BEGIN
3916 l_sql := ' SELECT sbdh.PEG_HOLE_NUMBER, core.PEG_VERTICAL, core.PEG_HORIZONTAL ' ||
3917 ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV sbdh, EGO_ITEM_GTN_ATTRS_B core ' ||
3918 ' WHERE core.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
3919 ' AND core.ORGANIZATION_ID = :p_organization_id ' ||
3920 ' AND sbdh.INVENTORY_ITEM_ID (+) = core.INVENTORY_ITEM_ID ' ||
3921 ' AND sbdh.ORGANIZATION_ID (+) = core.ORGANIZATION_ID ';
3922 EXECUTE IMMEDIATE l_sql INTO l_peg_hole_number, l_peg_vertical, l_peg_horizontal USING p_inventory_item_id, p_organization_id;
3923
3924 IF l_peg_hole_number IS NOT NULL AND (l_peg_vertical IS NULL OR l_peg_horizontal IS NULL) OR
3925 l_peg_vertical IS NOT NULL AND (l_peg_hole_number IS NULL OR l_peg_horizontal IS NULL) OR
3926 l_peg_horizontal IS NOT NULL AND (l_peg_hole_number IS NULL OR l_peg_vertical IS NULL)
3927 THEN
3928 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRADE_ITEM_INFO', l_page_id, l_page_display_name);
3929 l_error_table.EXTEND;
3930 l_error_rec.PAGE_ID := l_page_id;
3931 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
3932 FND_MESSAGE.SET_NAME('EGO', 'EGO_3ATTRS_MUST_COEXIST');
3933 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRADE_ITEM_INFO', 'PEG_HOLE_NUMBER');
3934 l_attr2_name := Get_Attr_Display_Name('EGO_ITEM_GTIN_ATTRS', 'Trade_Item_Measurements', 'Peg_Horizontal');
3935 l_attr3_name := Get_Attr_Display_Name('EGO_ITEM_GTIN_ATTRS', 'Trade_Item_Measurements', 'Peg_Vertical');
3936 FND_MESSAGE.SET_TOKEN('ATTR1', l_attr1_name);
3937 FND_MESSAGE.SET_TOKEN('ATTR2', l_attr2_name);
3938 FND_MESSAGE.SET_TOKEN('ATTR3', l_attr3_name);
3939 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
3940 l_error_rec.ERROR_LEVEL := 'I';
3941 l_error_table(l_error_table.LAST) := l_error_rec;
3942 END IF;
3943 EXCEPTION
3944 WHEN NO_DATA_FOUND THEN
3945 NULL;
3946 WHEN OTHERS THEN
3947 l_unexp_err := 'EGOINT_GDSN_TRADE_ITEM_INFO - '||SQLERRM;
3948 l_error_table.EXTEND;
3949 l_error_rec.PAGE_ID := -1;
3950 l_error_rec.ERROR_MESSAGE := l_unexp_err;
3951 l_error_rec.ERROR_LEVEL := 'I';
3952 l_error_table(l_error_table.LAST) := l_error_rec;
3953 END;
3954
3955 -- validating that UOM values and class are present
3956 -- 6. UOM for PRICE_COMPARISON_MSRMNT must be present if PRICE_COMPARISON_MSRMNT is present
3957 BEGIN
3958 l_page_id := -99;
3959 l_page_display_name := NULL;
3960
3961 l_sql := ' SELECT PRICE_COMPARISON_MSRMNT, PRICE_COMPARISON_MSRMNT_UUOM ' ||
3962 ' FROM EGO_SBDH_PRC_CMPRSN_MSRMT_AGV ' ||
3963 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
3964 ' AND ORGANIZATION_ID = :p_organization_id ';
3965 OPEN c_ref_cursor FOR l_sql USING p_inventory_item_id, p_organization_id;
3966 LOOP
3967 FETCH c_ref_cursor INTO l_attr_value, l_attr_value_uom;
3968 EXIT WHEN c_ref_cursor%NOTFOUND;
3969
3970 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
3971 IF l_page_id = -99 THEN
3972 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_PRC_CMPRSN_MSRMT', l_page_id, l_page_display_name);
3973 END IF;
3974 l_error_table.EXTEND;
3975 l_error_rec.PAGE_ID := l_page_id;
3976 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
3977 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_REQD');
3978 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_PRC_CMPRSN_MSRMT', 'PRICE_COMPARISON_MSRMNT');
3979 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
3980 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
3981 l_error_rec.ERROR_LEVEL := 'I';
3982 l_error_table(l_error_table.LAST) := l_error_rec;
3983 END IF;
3984 END LOOP;
3985 CLOSE c_ref_cursor;
3986 EXCEPTION
3987 WHEN column_not_found THEN
3988 IF c_ref_cursor%ISOPEN THEN
3989 CLOSE c_ref_cursor;
3990 END IF;
3991 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_PRC_CMPRSN_MSRMT', l_page_id, l_page_display_name);
3992 l_error_table.EXTEND;
3993 l_error_rec.PAGE_ID := l_page_id;
3994 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
3995 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_CLASS_REQD');
3996 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_PRC_CMPRSN_MSRMT', 'PRICE_COMPARISON_MSRMNT');
3997 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
3998 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
3999 l_error_rec.ERROR_LEVEL := 'I';
4000 l_error_table(l_error_table.LAST) := l_error_rec;
4001 WHEN NO_DATA_FOUND THEN
4002 IF c_ref_cursor%ISOPEN THEN
4003 CLOSE c_ref_cursor;
4004 END IF;
4005 WHEN OTHERS THEN
4006 IF c_ref_cursor%ISOPEN THEN
4007 CLOSE c_ref_cursor;
4008 END IF;
4009 l_unexp_err := 'EGOINT_GDSN_PRC_CMPRSN_MSRMT - '||SQLERRM;
4010 l_error_table.EXTEND;
4011 l_error_rec.PAGE_ID := -1;
4012 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4013 l_error_rec.ERROR_LEVEL := 'I';
4014 l_error_table(l_error_table.LAST) := l_error_rec;
4015 END;
4016
4017 -- 7. UOM for TRADE_ITEM_COMPOSTN_WIDTH must be present if TRADE_ITEM_COMPOSTN_WIDTH is present
4018 BEGIN
4019 l_sql := ' SELECT TRADE_ITEM_COMPOSTN_WIDTH, TRADE_ITEM_COMPOSTN_WIDTH_UUOM ' ||
4020 ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV ' ||
4021 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4022 ' AND ORGANIZATION_ID = :p_organization_id ';
4023 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom USING p_inventory_item_id, p_organization_id;
4024
4025 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4026 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRADE_ITEM_INFO', l_page_id, l_page_display_name);
4027 l_error_table.EXTEND;
4028 l_error_rec.PAGE_ID := l_page_id;
4029 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4030 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_REQD');
4031 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRADE_ITEM_INFO', 'TRADE_ITEM_COMPOSTN_WIDTH');
4032 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4033 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4034 l_error_rec.ERROR_LEVEL := 'I';
4035 l_error_table(l_error_table.LAST) := l_error_rec;
4036 END IF;
4037 EXCEPTION
4038 WHEN column_not_found THEN
4039 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRADE_ITEM_INFO', l_page_id, l_page_display_name);
4040 l_error_table.EXTEND;
4041 l_error_rec.PAGE_ID := l_page_id;
4042 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4043 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_CLASS_REQD');
4044 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRADE_ITEM_INFO', 'TRADE_ITEM_COMPOSTN_WIDTH');
4045 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4046 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4047 l_error_rec.ERROR_LEVEL := 'I';
4048 l_error_table(l_error_table.LAST) := l_error_rec;
4049 WHEN NO_DATA_FOUND THEN
4050 NULL;
4051 WHEN OTHERS THEN
4052 l_unexp_err := 'EGOINT_GDSN_TRADE_ITEM_INFO - '||SQLERRM;
4053 l_error_table.EXTEND;
4054 l_error_rec.PAGE_ID := -1;
4055 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4056 l_error_rec.ERROR_LEVEL := 'I';
4057 l_error_table(l_error_table.LAST) := l_error_rec;
4058 END;
4059
4060 -- 8. UOM for MATERIAL_WEIGHT must be present if MATERIAL_WEIGHT is present
4061 BEGIN
4062 l_sql := ' SELECT MATERIAL_WEIGHT, MATERIAL_WEIGHT_UUOM ' ||
4063 ' FROM EGO_SBDH_MATERIAL_INFO_AGV ' ||
4064 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4065 ' AND ORGANIZATION_ID = :p_organization_id ';
4066 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom USING p_inventory_item_id, p_organization_id;
4067
4068 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4069 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_MATERIAL_INFO', l_page_id, l_page_display_name);
4070 l_error_table.EXTEND;
4071 l_error_rec.PAGE_ID := l_page_id;
4072 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4073 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_REQD');
4074 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_MATERIAL_INFO', 'MATERIAL_WEIGHT');
4075 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4076 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4077 l_error_rec.ERROR_LEVEL := 'I';
4078 l_error_table(l_error_table.LAST) := l_error_rec;
4079 END IF;
4080 EXCEPTION
4081 WHEN column_not_found THEN
4082 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_MATERIAL_INFO', l_page_id, l_page_display_name);
4083 l_error_table.EXTEND;
4084 l_error_rec.PAGE_ID := l_page_id;
4085 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4086 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_CLASS_REQD');
4087 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_MATERIAL_INFO', 'MATERIAL_WEIGHT');
4088 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4089 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4090 l_error_rec.ERROR_LEVEL := 'I';
4091 l_error_table(l_error_table.LAST) := l_error_rec;
4092 WHEN NO_DATA_FOUND THEN
4093 NULL;
4094 WHEN OTHERS THEN
4095 l_unexp_err := 'EGOINT_GDSN_MATERIAL_INFO - '||SQLERRM;
4096 l_error_table.EXTEND;
4097 l_error_rec.PAGE_ID := -1;
4098 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4099 l_error_rec.ERROR_LEVEL := 'I';
4100 l_error_table(l_error_table.LAST) := l_error_rec;
4101 END;
4102
4103 -- 9. UOM for STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX must be present if value os present
4104 -- this is a multi-row attribute group, so using ref cursor.
4105 BEGIN
4106 l_page_id := -99;
4107 l_page_display_name := NULL;
4108
4109 l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX, STORAGE_HNDLNG_HUMDTY_MIN_UUOM, STORAGE_HNDLNG_HUMDTY_MAX_UUOM ' ||
4110 ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
4111 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4112 ' AND ORGANIZATION_ID = :p_organization_id';
4113 OPEN c_ref_cursor FOR l_sql USING p_inventory_item_id, p_organization_id;
4114 LOOP
4115 FETCH c_ref_cursor INTO l_attr_value, l_attr1_value, l_attr_value_uom, l_attr1_value_uom;
4116 EXIT WHEN c_ref_cursor%NOTFOUND;
4117
4118 -- checking for STORAGE_HNDLNG_HUMDTY_MIN
4119 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4120 IF l_page_id = -99 THEN
4121 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', l_page_id, l_page_display_name);
4122 END IF;
4123 l_error_table.EXTEND;
4124 l_error_rec.PAGE_ID := l_page_id;
4125 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4126 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_REQD');
4127 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', 'STORAGE_HNDLNG_HUMDTY_MIN');
4128 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4129 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4130 l_error_rec.ERROR_LEVEL := 'I';
4131 l_error_table(l_error_table.LAST) := l_error_rec;
4132 END IF;
4133
4134 -- checking for STORAGE_HNDLNG_HUMDTY_MAX
4135 IF l_attr1_value IS NOT NULL AND l_attr1_value_uom IS NULL THEN
4136 IF l_page_id = -99 THEN
4137 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', l_page_id, l_page_display_name);
4138 END IF;
4139 l_error_table.EXTEND;
4140 l_error_rec.PAGE_ID := l_page_id;
4141 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4142 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_REQD');
4143 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', 'STORAGE_HNDLNG_HUMDTY_MAX');
4144 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4145 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4146 l_error_rec.ERROR_LEVEL := 'I';
4147 l_error_table(l_error_table.LAST) := l_error_rec;
4148 END IF;
4149 END LOOP;
4150 CLOSE c_ref_cursor;
4151 EXCEPTION
4152 WHEN column_not_found THEN
4153 IF c_ref_cursor%ISOPEN THEN
4154 CLOSE c_ref_cursor;
4155 END IF;
4156 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', l_page_id, l_page_display_name);
4157 l_error_table.EXTEND;
4158 l_error_rec.PAGE_ID := l_page_id;
4159 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4160 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_CLASS_REQD2');
4161 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', 'STORAGE_HNDLNG_HUMDTY_MIN');
4162 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY', 'STORAGE_HNDLNG_HUMDTY_MAX');
4163 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4164 FND_MESSAGE.SET_TOKEN('ATTR1_NAME', l_attr2_name);
4165 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4166 l_error_rec.ERROR_LEVEL := 'I';
4167 l_error_table(l_error_table.LAST) := l_error_rec;
4168 WHEN NO_DATA_FOUND THEN
4169 IF c_ref_cursor%ISOPEN THEN
4170 CLOSE c_ref_cursor;
4171 END IF;
4172 WHEN OTHERS THEN
4173 IF c_ref_cursor%ISOPEN THEN
4174 CLOSE c_ref_cursor;
4175 END IF;
4176 l_unexp_err := 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY - '||SQLERRM;
4177 l_error_table.EXTEND;
4178 l_error_rec.PAGE_ID := -1;
4179 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4180 l_error_rec.ERROR_LEVEL := 'I';
4181 l_error_table(l_error_table.LAST) := l_error_rec;
4182 END;
4183
4184 -- validating TP-Dependant attributes
4185 -- 10. AGREED_MINIMUM_BUYING_QTY can not be more than AGREED_MAXIMUM_BUYING_QTY
4186 -- 11. START_DATE_MINIMUM_BUYING_QTY can not be more than END_DATE_MINIMUM_BUYING_QTY
4187 -- 12. START_DATE_MAXIMUM_BUYING_QTY can not be more than END_DATE_MAXIMUM_BUYING_QTY
4188 BEGIN
4189 l_page_id := -99;
4190 l_page_display_name := NULL;
4191
4192 IF l_do_tp_validations THEN
4193 l_sql := ' SELECT AGREED_MINIMUM_BUYING_QTY, ' ||
4194 ' AGREED_MAXIMUM_BUYING_QTY, ' ||
4195 ' START_DATE_MINIMUM_BUYING_QTY, ' ||
4196 ' END_DATE_MINIMUM_BUYING_QTY, ' ||
4197 ' START_DATE_MAXIMUM_BUYING_QTY, ' ||
4198 ' END_DATE_MAXIMUM_BUYING_QTY ' ||
4199 ' FROM EGO_SBDH_BUYING_QTY_INFO_TPV O ' ||
4200 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4201 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4202 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4203 ' OR (PARTY_SITE_ID IS NULL '||
4204 ' AND NOT EXISTS (SELECT NULL ' ||
4205 ' FROM EGO_SBDH_BUYING_QTY_INFO_TPV I ' ||
4206 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4207 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4208 ' AND I.PARTY_SITE_ID = :2)))';
4209 EXECUTE IMMEDIATE l_sql INTO l_min_value, l_max_value, l_start_date, l_end_date, l_start_date1, l_end_date1
4210 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4211 ELSE
4212 l_sql := ' SELECT AGREED_MINIMUM_BUYING_QTY, ' ||
4213 ' AGREED_MAXIMUM_BUYING_QTY, ' ||
4214 ' START_DATE_MINIMUM_BUYING_QTY, ' ||
4215 ' END_DATE_MINIMUM_BUYING_QTY, ' ||
4216 ' START_DATE_MAXIMUM_BUYING_QTY, ' ||
4217 ' END_DATE_MAXIMUM_BUYING_QTY ' ||
4218 ' FROM EGO_SBDH_BUYING_QTY_INFO_AGV O ' ||
4219 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4220 ' AND ORGANIZATION_ID = :p_organization_id ';
4221 EXECUTE IMMEDIATE l_sql INTO l_min_value, l_max_value, l_start_date, l_end_date, l_start_date1, l_end_date1
4222 USING p_inventory_item_id, p_organization_id;
4223 END IF; --IF l_do_tp_validations THEN
4224
4225 -- 10. AGREED_MINIMUM_BUYING_QTY can not be more than AGREED_MAXIMUM_BUYING_QTY
4226 IF l_min_value > l_max_value THEN
4227 l_error_table.EXTEND;
4228 IF l_page_id = -99 THEN
4229 IF l_do_tp_validations THEN
4230 l_error_rec.ERROR_LEVEL := 'C';
4231 l_page_id := -1;
4232 ELSE
4233 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', l_page_id, l_page_display_name);
4234 l_error_rec.ERROR_LEVEL := 'I';
4235 END IF; -- IF l_do_tp_validations THEN
4236 END IF;
4237 l_error_rec.PAGE_ID := l_page_id;
4238 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4239 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
4240 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', 'AGREED_MINIMUM_BUYING_QTY');
4241 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', 'AGREED_MAXIMUM_BUYING_QTY');
4242 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
4243 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
4244 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4245 l_error_table(l_error_table.LAST) := l_error_rec;
4246 END IF;
4247
4248 -- 11. START_DATE_MINIMUM_BUYING_QTY can not be more than END_DATE_MINIMUM_BUYING_QTY
4249 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4250 l_error_table.EXTEND;
4251 IF l_page_id = -99 THEN
4252 IF l_do_tp_validations THEN
4253 l_error_rec.ERROR_LEVEL := 'C';
4254 l_page_id := -1;
4255 ELSE
4256 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', l_page_id, l_page_display_name);
4257 l_error_rec.ERROR_LEVEL := 'I';
4258 END IF; --IF l_do_tp_validations THEN
4259 END IF;
4260 l_error_rec.PAGE_ID := l_page_id;
4261 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4262 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
4263 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', 'START_DATE_MINIMUM_BUYING_QTY');
4264 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', 'END_DATE_MINIMUM_BUYING_QTY');
4265 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
4266 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
4267 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4268 l_error_table(l_error_table.LAST) := l_error_rec;
4269 END IF;
4270
4271 -- 12. START_DATE_MAXIMUM_BUYING_QTY can not be more than END_DATE_MAXIMUM_BUYING_QTY
4272 IF NVL(l_start_date1, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date1, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4273 l_error_table.EXTEND;
4274 IF l_page_id = -99 THEN
4275 IF l_do_tp_validations THEN
4276 l_error_rec.ERROR_LEVEL := 'C';
4277 l_page_id := -1;
4278 ELSE
4279 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', l_page_id, l_page_display_name);
4280 l_error_rec.ERROR_LEVEL := 'I';
4281 END IF; --IF l_do_tp_validations THEN
4282 END IF;
4283 l_error_rec.PAGE_ID := l_page_id;
4284 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4285 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
4286 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', 'START_DATE_MAXIMUM_BUYING_QTY');
4287 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_BUYING_QTY_INFO', 'END_DATE_MAXIMUM_BUYING_QTY');
4288 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
4289 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
4290 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4291 l_error_table(l_error_table.LAST) := l_error_rec;
4292 END IF;
4293 EXCEPTION
4294 WHEN NO_DATA_FOUND THEN
4295 NULL;
4296 WHEN OTHERS THEN
4297 l_unexp_err := 'EGOINT_GDSN_BUYING_QTY_INFO - '||SQLERRM;
4298 l_error_table.EXTEND;
4299 l_error_rec.PAGE_ID := -1;
4300 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4301 IF l_do_tp_validations THEN
4302 l_error_rec.ERROR_LEVEL := 'C';
4303 ELSE
4304 l_error_rec.ERROR_LEVEL := 'I';
4305 END IF; -- IF l_do_tp_validations THEN
4306 l_error_table(l_error_table.LAST) := l_error_rec;
4307 END;
4308
4309 -- 13. FIRST_ORDER_DATE can not be more than LAST_ORDER_DATE
4310 BEGIN
4311 IF l_do_tp_validations THEN
4312 l_sql := ' SELECT FIRST_ORDER_DATE, ' ||
4313 ' LAST_ORDER_DATE ' ||
4314 ' FROM EGO_SBDH_ORDERING_INFO_TPV O ' ||
4315 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4316 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4317 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4318 ' OR (PARTY_SITE_ID IS NULL '||
4319 ' AND NOT EXISTS (SELECT NULL ' ||
4320 ' FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
4321 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4322 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4323 ' AND I.PARTY_SITE_ID = :2)))';
4324 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4325 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4326 ELSE
4327 l_sql := ' SELECT FIRST_ORDER_DATE, ' ||
4328 ' LAST_ORDER_DATE ' ||
4329 ' FROM EGO_SBDH_ORDERING_INFO_AGV O ' ||
4330 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4331 ' AND ORGANIZATION_ID = :p_organization_id ';
4332 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4333 USING p_inventory_item_id, p_organization_id;
4334 END IF; --IF l_do_tp_validations THEN
4335
4336 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4337 l_error_table.EXTEND;
4338 IF l_do_tp_validations THEN
4339 l_error_rec.ERROR_LEVEL := 'C';
4340 l_page_id := -1;
4341 ELSE
4342 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_ORDERING_INFO', l_page_id, l_page_display_name);
4343 l_error_rec.ERROR_LEVEL := 'I';
4344 END IF; --IF l_do_tp_validations THEN
4345 l_error_rec.PAGE_ID := l_page_id;
4346 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4347 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
4348 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_ORDERING_INFO', 'FIRST_ORDER_DATE');
4349 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_ORDERING_INFO', 'LAST_ORDER_DATE');
4350 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
4351 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
4352 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4353 l_error_table(l_error_table.LAST) := l_error_rec;
4354 END IF;
4355 EXCEPTION
4356 WHEN NO_DATA_FOUND THEN
4357 NULL;
4358 WHEN OTHERS THEN
4359 l_unexp_err := 'EGOINT_GDSN_ORDERING_INFO - '||SQLERRM;
4360 l_error_table.EXTEND;
4361 l_error_rec.PAGE_ID := -1;
4362 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4363 IF l_do_tp_validations THEN
4364 l_error_rec.ERROR_LEVEL := 'C';
4365 ELSE
4366 l_error_rec.ERROR_LEVEL := 'I';
4367 END IF; -- IF l_do_tp_validations THEN
4368 l_error_table(l_error_table.LAST) := l_error_rec;
4369 END;
4370
4371 -- 14. FIRST_SHIP_DATE can not be more than LAST_SHIP_DATE
4372 BEGIN
4373 IF l_do_tp_validations THEN
4374 l_sql := ' SELECT FIRST_SHIP_DATE, ' ||
4375 ' LAST_SHIP_DATE ' ||
4376 ' FROM EGO_SBDH_SHIP_EXCL_DATES_TPV O ' ||
4377 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4378 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4379 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4380 ' OR (PARTY_SITE_ID IS NULL '||
4381 ' AND NOT EXISTS (SELECT NULL ' ||
4382 ' FROM EGO_SBDH_SHIP_EXCL_DATES_TPV I ' ||
4383 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4384 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4385 ' AND I.PARTY_SITE_ID = :2)))';
4386 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4387 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4388 ELSE
4389 l_sql := ' SELECT FIRST_SHIP_DATE, ' ||
4390 ' LAST_SHIP_DATE ' ||
4391 ' FROM EGO_SBDH_SHIP_EXCL_DATES_AGV O ' ||
4392 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4393 ' AND ORGANIZATION_ID = :p_organization_id ';
4394 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4395 USING p_inventory_item_id, p_organization_id;
4396 END IF; --IF l_do_tp_validations THEN
4397
4398 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4399 l_error_table.EXTEND;
4400 IF l_do_tp_validations THEN
4401 l_error_rec.ERROR_LEVEL := 'C';
4402 l_page_id := -1;
4403 ELSE
4404 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_SHIP_EXCL_DATES', l_page_id, l_page_display_name);
4405 l_error_rec.ERROR_LEVEL := 'I';
4406 END IF; --IF l_do_tp_validations THEN
4407 l_error_rec.PAGE_ID := l_page_id;
4408 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4409 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
4410 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_SHIP_EXCL_DATES', 'FIRST_SHIP_DATE');
4411 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_SHIP_EXCL_DATES', 'LAST_SHIP_DATE');
4412 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
4413 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
4414 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4415 l_error_table(l_error_table.LAST) := l_error_rec;
4416 END IF;
4417 EXCEPTION
4418 WHEN NO_DATA_FOUND THEN
4419 NULL;
4420 WHEN OTHERS THEN
4421 l_unexp_err := 'EGOINT_GDSN_SHIP_EXCL_DATES - '||SQLERRM;
4422 l_error_table.EXTEND;
4423 l_error_rec.PAGE_ID := -1;
4424 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4425 IF l_do_tp_validations THEN
4426 l_error_rec.ERROR_LEVEL := 'C';
4427 ELSE
4428 l_error_rec.ERROR_LEVEL := 'I';
4429 END IF; -- IF l_do_tp_validations THEN
4430 l_error_table(l_error_table.LAST) := l_error_rec;
4431 END;
4432
4433 -- 15. MIN_TRADE_ITEM_LIFE_ARR can not be more than MIN_TRADE_ITEM_LIFE_PROD
4434 BEGIN
4435 l_sql := ' SELECT NVL(C.MIN_TRADE_ITEM_LIFE_ARR, MSI.SHELF_LIFE_DAYS) MIN_TRADE_ITEM_LIFE_ARR ' ||
4436 ' FROM MTL_SYSTEM_ITEMS_B MSI,EGO_ITEM_CUST_ATTRS_B C ' ||
4437 ' WHERE C.PARTY_SITE_ID (+) = :party_site_id ' ||
4438 ' AND MSI.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4439 ' AND MSI.ORGANIZATION_ID = :p_organization_id ' ||
4440 ' AND MSI.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID (+) '||
4441 ' AND MSI.ORGANIZATION_ID = C.MASTER_ORGANIZATION_ID (+) ';
4442
4443 EXECUTE IMMEDIATE l_sql INTO l_min_value
4444 USING l_party_site_id, p_inventory_item_id, p_organization_id;
4445
4446 IF l_do_tp_validations THEN
4447 l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
4448 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV O ' ||
4449 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4450 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4451 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4452 ' OR (PARTY_SITE_ID IS NULL '||
4453 ' AND NOT EXISTS (SELECT NULL ' ||
4454 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV I ' ||
4455 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4456 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4457 ' AND I.PARTY_SITE_ID = :2)))';
4458 EXECUTE IMMEDIATE l_sql INTO l_max_value
4459 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4460 ELSE
4461 l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
4462 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_AGV O ' ||
4463 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4464 ' AND ORGANIZATION_ID = :p_organization_id ';
4465 EXECUTE IMMEDIATE l_sql INTO l_max_value
4466 USING p_inventory_item_id, p_organization_id;
4467 END IF; --IF l_do_tp_validations THEN
4468
4469 IF l_min_value > l_max_value THEN
4470 l_error_table.EXTEND;
4471 IF l_do_tp_validations THEN
4472 l_error_rec.ERROR_LEVEL := 'C';
4473 l_page_id := -1;
4474 ELSE
4475 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRD_ITM_LIFESPAN', l_page_id, l_page_display_name);
4476 l_error_rec.ERROR_LEVEL := 'I';
4477 END IF; --IF l_do_tp_validations THEN
4478 l_error_rec.PAGE_ID := l_page_id;
4479 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4480 FND_MESSAGE.SET_NAME('EGO', 'EGO_MIN_GT_MAX');
4481 l_attr1_name := Get_Attr_Display_Name('EGO_ITEM_CUSTOMER_ATTRS', 'Handling_Information', 'Min_Trade_Item_Life_Arr');
4482 l_attr2_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_TRD_ITM_LIFESPAN', 'MIN_TRADE_ITEM_LIFE_PROD');
4483 FND_MESSAGE.SET_TOKEN('MIN_ATTR', l_attr1_name);
4484 FND_MESSAGE.SET_TOKEN('MAX_ATTR', l_attr2_name);
4485 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4486 l_error_table(l_error_table.LAST) := l_error_rec;
4487 END IF;
4488
4489 EXCEPTION
4490 WHEN NO_DATA_FOUND THEN
4491 NULL;
4492 WHEN OTHERS THEN
4493 l_unexp_err := 'EGOINT_GDSN_TRD_ITM_LIFESPAN - '||SQLERRM;
4494 l_error_table.EXTEND;
4495 l_error_rec.PAGE_ID := -1;
4496 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4497 IF l_do_tp_validations THEN
4498 l_error_rec.ERROR_LEVEL := 'C';
4499 ELSE
4500 l_error_rec.ERROR_LEVEL := 'I';
4501 END IF; -- IF l_do_tp_validations THEN
4502 l_error_table(l_error_table.LAST) := l_error_rec;
4503 END;
4504
4505 -- 16. UOM for GOODS_PICK_UP_LEAD_TIME must be present if GOODS_PICK_UP_LEAD_TIME is present
4506 BEGIN
4507 IF l_do_tp_validations THEN
4508 l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
4509 ' FROM EGO_SBDH_ORDERING_INFO_TPV O' ||
4510 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4511 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4512 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4513 ' OR (PARTY_SITE_ID IS NULL '||
4514 ' AND NOT EXISTS (SELECT NULL ' ||
4515 ' FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
4516 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4517 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4518 ' AND I.PARTY_SITE_ID = :2)))';
4519
4520 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom
4521 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4522 ELSE
4523 l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
4524 ' FROM EGO_SBDH_ORDERING_INFO_AGV ' ||
4525 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4526 ' AND ORGANIZATION_ID = :p_organization_id ';
4527
4528 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom
4529 USING p_inventory_item_id, p_organization_id;
4530 END IF; --IF l_do_tp_validations THEN
4531
4532 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4533 l_error_table.EXTEND;
4534 IF l_do_tp_validations THEN
4535 l_error_rec.ERROR_LEVEL := 'C';
4536 l_page_id := -1;
4537 ELSE
4538 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_ORDERING_INFO', l_page_id, l_page_display_name);
4539 l_error_rec.ERROR_LEVEL := 'I';
4540 END IF; --IF l_do_tp_validations THEN
4541 l_error_rec.PAGE_ID := l_page_id;
4542 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4543 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_REQD');
4544 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_ORDERING_INFO', 'GOODS_PICK_UP_LEAD_TIME');
4545 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4546 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4547 l_error_table(l_error_table.LAST) := l_error_rec;
4548 END IF;
4549 EXCEPTION
4550 WHEN column_not_found THEN
4551 Get_Associated_Page_Details(l_catalog_group_id, 'EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_ORDERING_INFO', l_page_id, l_page_display_name);
4552 l_error_table.EXTEND;
4553 l_error_rec.PAGE_ID := l_page_id;
4554 l_error_rec.PAGE_DISPLAY_NAME := l_page_display_name;
4555 FND_MESSAGE.SET_NAME('EGO', 'EGO_UOM_CLASS_REQD');
4556 l_attr1_name := Get_Attr_Display_Name('EGO_ITEMMGMT_GROUP', 'EGOINT_GDSN_ORDERING_INFO', 'GOODS_PICK_UP_LEAD_TIME');
4557 FND_MESSAGE.SET_TOKEN('ATTR_NAME', l_attr1_name);
4558 l_error_rec.ERROR_MESSAGE := FND_MESSAGE.GET();
4559 IF l_do_tp_validations THEN
4560 l_error_rec.ERROR_LEVEL := 'C';
4561 ELSE
4562 l_error_rec.ERROR_LEVEL := 'I';
4563 END IF; -- IF l_do_tp_validations THEN
4564 l_error_table(l_error_table.LAST) := l_error_rec;
4565 WHEN NO_DATA_FOUND THEN
4566 NULL;
4567 WHEN OTHERS THEN
4568 l_unexp_err := 'EGOINT_GDSN_ORDERING_INFO - '||SQLERRM;
4569 l_error_table.EXTEND;
4570 l_error_rec.PAGE_ID := -1;
4571 l_error_rec.ERROR_MESSAGE := l_unexp_err;
4572 IF l_do_tp_validations THEN
4573 l_error_rec.ERROR_LEVEL := 'C';
4574 ELSE
4575 l_error_rec.ERROR_LEVEL := 'I';
4576 END IF; -- IF l_do_tp_validations THEN
4577 l_error_table(l_error_table.LAST) := l_error_rec;
4578 END;
4579
4580 OPEN p_errors FOR
4581 SELECT *
4582 FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );
4583 END Validate_SBDH_Attributes;
4584
4585 /*
4586 * This method validates SBDH attributes. if p_address_id is null then only item level attributes
4587 * are validated.
4588 * Returns 'F' if some validation fails
4589 */
4590 FUNCTION Is_SBDH_Attributes_Valid(p_inventory_item_id NUMBER,
4591 p_organization_id NUMBER,
4592 p_address_id NUMBER)
4593 RETURN VARCHAR2
4594 IS
4595 l_start_date DATE;
4596 l_end_date DATE;
4597 l_start_date1 DATE;
4598 l_end_date1 DATE;
4599 l_min_value NUMBER;
4600 l_max_value NUMBER;
4601 l_attr_value NUMBER;
4602 l_attr_value_uom VARCHAR2(10);
4603 l_attr1_value NUMBER;
4604 l_attr1_value_uom VARCHAR2(10);
4605
4606 l_sql VARCHAR2(15000);
4607 l_peg_hole_number NUMBER;
4608 l_peg_vertical NUMBER;
4609 l_peg_horizontal NUMBER;
4610 l_party_site_id NUMBER;
4611 l_do_tp_validations BOOLEAN;
4612
4613 TYPE ref_cursor_type IS REF CURSOR;
4614 c_ref_cursor ref_cursor_type;
4615
4616 column_not_found EXCEPTION;
4617 PRAGMA EXCEPTION_INIT(column_not_found, -904);
4618 BEGIN
4619 IF p_address_id IS NULL THEN
4620 l_do_tp_validations := FALSE;
4621 ELSE
4622 l_do_tp_validations := TRUE;
4623 END IF;
4624
4625 IF l_do_tp_validations THEN
4626 -- retreiving party_site_id from address_id
4627 SELECT PARTY_SITE_ID INTO l_party_site_id
4628 FROM HZ_CUST_ACCT_SITES_ALL
4629 WHERE CUST_ACCT_SITE_ID = p_address_id;
4630 END IF; --IF l_do_tp_validations THEN
4631
4632 -- validating first the attributes that are not TP-Dependant
4633 -- 1. DEPOSIT_VALUE_EFFECTIVE_DATE can not be greater than DEPOSIT_VALUE_END_DATE
4634 BEGIN
4635 l_sql := ' SELECT DEPOSIT_VALUE_EFFECTIVE_DATE, DEPOSIT_VALUE_END_DATE ' ||
4636 ' FROM EGO_SBDH_DEP_VAL_DATE_INFO_AGV ' ||
4637 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4638 ' AND ORGANIZATION_ID = :p_organization_id';
4639 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date USING p_inventory_item_id, p_organization_id;
4640
4641 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4642 RETURN 'F';
4643 END IF;
4644 EXCEPTION WHEN NO_DATA_FOUND THEN
4645 NULL;
4646 END;
4647
4648 -- 2. CAMPAIGN_START_DATE can not be greater than CAMPAIGN_END_DATE
4649 BEGIN
4650 l_sql := ' SELECT CAMPAIGN_START_DATE, CAMPAIGN_END_DATE ' ||
4651 ' FROM EGO_SBDH_CAMPAIGN_INFO_AGV ' ||
4652 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4653 ' AND ORGANIZATION_ID = :p_organization_id';
4654 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date USING p_inventory_item_id, p_organization_id;
4655
4656 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4657 RETURN 'F';
4658 END IF;
4659 EXCEPTION WHEN NO_DATA_FOUND THEN
4660 NULL;
4661 END;
4662
4663 -- 3. SEASONAL_AVL_START_DATE can not be greater than SEASONAL_AVL_END_DATE
4664 BEGIN
4665 l_sql := ' SELECT SEASONAL_AVL_START_DATE, SEASONAL_AVL_END_DATE ' ||
4666 ' FROM EGO_SBDH_SEASON_AVL_DATE_AGV ' ||
4667 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4668 ' AND ORGANIZATION_ID = :p_organization_id';
4669 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date USING p_inventory_item_id, p_organization_id;
4670
4671 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4672 RETURN 'F';
4673 END IF;
4674 EXCEPTION WHEN NO_DATA_FOUND THEN
4675 NULL;
4676 END;
4677
4678 -- 4. STORAGE_HNDLNG_HUMDTY_MIN can not be greater than STORAGE_HNDLNG_HUMDTY_MAX
4679 -- this is a multi-row attribute group, so using ref cursor.
4680 BEGIN
4681 l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX ' ||
4682 ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
4683 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4684 ' AND ORGANIZATION_ID = :p_organization_id';
4685 OPEN c_ref_cursor FOR l_sql USING p_inventory_item_id, p_organization_id;
4686 LOOP
4687 FETCH c_ref_cursor INTO l_min_value, l_max_value;
4688 EXIT WHEN c_ref_cursor%NOTFOUND;
4689
4690 IF l_min_value > l_max_value THEN
4691 IF c_ref_cursor%ISOPEN THEN
4692 CLOSE c_ref_cursor;
4693 END IF;
4694 RETURN 'F';
4695 END IF;
4696 END LOOP;
4697 CLOSE c_ref_cursor;
4698 EXCEPTION
4699 WHEN NO_DATA_FOUND THEN
4700 IF c_ref_cursor%ISOPEN THEN
4701 CLOSE c_ref_cursor;
4702 END IF;
4703 WHEN OTHERS THEN
4704 IF c_ref_cursor%ISOPEN THEN
4705 CLOSE c_ref_cursor;
4706 END IF;
4707 RAISE;
4708 END;
4709
4710 -- 5. pegHorizontal, pegVertical and pegHoleNumber should be specified when either of them is specified.
4711 BEGIN
4712 l_sql := ' SELECT sbdh.PEG_HOLE_NUMBER, core.PEG_VERTICAL, core.PEG_HORIZONTAL ' ||
4713 ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV sbdh, EGO_ITEM_GTN_ATTRS_B core ' ||
4714 ' WHERE core.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4715 ' AND core.ORGANIZATION_ID = :p_organization_id ' ||
4716 ' AND sbdh.INVENTORY_ITEM_ID (+) = core.INVENTORY_ITEM_ID ' ||
4717 ' AND sbdh.ORGANIZATION_ID (+) = core.ORGANIZATION_ID ';
4718 EXECUTE IMMEDIATE l_sql INTO l_peg_hole_number, l_peg_vertical, l_peg_horizontal USING p_inventory_item_id, p_organization_id;
4719
4720 IF l_peg_hole_number IS NOT NULL AND (l_peg_vertical IS NULL OR l_peg_horizontal IS NULL) OR
4721 l_peg_vertical IS NOT NULL AND (l_peg_hole_number IS NULL OR l_peg_horizontal IS NULL) OR
4722 l_peg_horizontal IS NOT NULL AND (l_peg_hole_number IS NULL OR l_peg_vertical IS NULL)
4723 THEN
4724 RETURN 'F';
4725 END IF;
4726 EXCEPTION WHEN NO_DATA_FOUND THEN
4727 NULL;
4728 END;
4729
4730 -- validating that UOM values and class are present
4731 -- 6. UOM for PRICE_COMPARISON_MSRMNT must be present if PRICE_COMPARISON_MSRMNT is present
4732 BEGIN
4733 l_sql := ' SELECT PRICE_COMPARISON_MSRMNT, PRICE_COMPARISON_MSRMNT_UUOM ' ||
4734 ' FROM EGO_SBDH_PRC_CMPRSN_MSRMT_AGV ' ||
4735 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4736 ' AND ORGANIZATION_ID = :p_organization_id ';
4737 OPEN c_ref_cursor FOR l_sql USING p_inventory_item_id, p_organization_id;
4738 LOOP
4739 FETCH c_ref_cursor INTO l_attr_value, l_attr_value_uom;
4740 EXIT WHEN c_ref_cursor%NOTFOUND;
4741
4742 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4743 IF c_ref_cursor%ISOPEN THEN
4744 CLOSE c_ref_cursor;
4745 END IF;
4746 RETURN 'F';
4747 END IF;
4748 END LOOP;
4749 CLOSE c_ref_cursor;
4750 EXCEPTION
4751 WHEN column_not_found THEN
4752 IF c_ref_cursor%ISOPEN THEN
4753 CLOSE c_ref_cursor;
4754 END IF;
4755 RETURN 'F';
4756 WHEN NO_DATA_FOUND THEN
4757 IF c_ref_cursor%ISOPEN THEN
4758 CLOSE c_ref_cursor;
4759 END IF;
4760 WHEN OTHERS THEN
4761 IF c_ref_cursor%ISOPEN THEN
4762 CLOSE c_ref_cursor;
4763 END IF;
4764 RETURN 'F';
4765 END;
4766
4767 -- 7. UOM for TRADE_ITEM_COMPOSTN_WIDTH must be present if TRADE_ITEM_COMPOSTN_WIDTH is present
4768 BEGIN
4769 l_sql := ' SELECT TRADE_ITEM_COMPOSTN_WIDTH, TRADE_ITEM_COMPOSTN_WIDTH_UUOM ' ||
4770 ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV ' ||
4771 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4772 ' AND ORGANIZATION_ID = :p_organization_id ';
4773 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom USING p_inventory_item_id, p_organization_id;
4774
4775 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4776 RETURN 'F';
4777 END IF;
4778 EXCEPTION
4779 WHEN column_not_found THEN
4780 RETURN 'F';
4781 WHEN NO_DATA_FOUND THEN
4782 NULL;
4783 END;
4784
4785 -- 8. UOM for MATERIAL_WEIGHT must be present if MATERIAL_WEIGHT is present
4786 BEGIN
4787 l_sql := ' SELECT MATERIAL_WEIGHT, MATERIAL_WEIGHT_UUOM ' ||
4788 ' FROM EGO_SBDH_MATERIAL_INFO_AGV ' ||
4789 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4790 ' AND ORGANIZATION_ID = :p_organization_id ';
4791 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom USING p_inventory_item_id, p_organization_id;
4792
4793 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4794 RETURN 'F';
4795 END IF;
4796 EXCEPTION
4797 WHEN column_not_found THEN
4798 RETURN 'F';
4799 WHEN NO_DATA_FOUND THEN
4800 NULL;
4801 END;
4802
4803 -- 9. UOM for STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX must be present if value os present
4804 -- this is a multi-row attribute group, so using ref cursor.
4805 BEGIN
4806 l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX, STORAGE_HNDLNG_HUMDTY_MIN_UUOM, STORAGE_HNDLNG_HUMDTY_MAX_UUOM ' ||
4807 ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
4808 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4809 ' AND ORGANIZATION_ID = :p_organization_id';
4810 OPEN c_ref_cursor FOR l_sql USING p_inventory_item_id, p_organization_id;
4811 LOOP
4812 FETCH c_ref_cursor INTO l_attr_value, l_attr1_value, l_attr_value_uom, l_attr1_value_uom;
4813 EXIT WHEN c_ref_cursor%NOTFOUND;
4814
4815 -- checking for STORAGE_HNDLNG_HUMDTY_MIN
4816 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
4817 IF c_ref_cursor%ISOPEN THEN
4818 CLOSE c_ref_cursor;
4819 END IF;
4820 RETURN 'F';
4821 END IF;
4822
4823 -- checking for STORAGE_HNDLNG_HUMDTY_MAX
4824 IF l_attr1_value IS NOT NULL AND l_attr1_value_uom IS NULL THEN
4825 IF c_ref_cursor%ISOPEN THEN
4826 CLOSE c_ref_cursor;
4827 END IF;
4828 RETURN 'F';
4829 END IF;
4830 END LOOP;
4831 CLOSE c_ref_cursor;
4832 EXCEPTION
4833 WHEN column_not_found THEN
4834 IF c_ref_cursor%ISOPEN THEN
4835 CLOSE c_ref_cursor;
4836 END IF;
4837 RETURN 'F';
4838 WHEN NO_DATA_FOUND THEN
4839 IF c_ref_cursor%ISOPEN THEN
4840 CLOSE c_ref_cursor;
4841 END IF;
4842 WHEN OTHERS THEN
4843 IF c_ref_cursor%ISOPEN THEN
4844 CLOSE c_ref_cursor;
4845 END IF;
4846 RAISE;
4847 END;
4848
4849 -- validating TP-Dependant attributes
4850 -- 10. AGREED_MINIMUM_BUYING_QTY can not be more than AGREED_MAXIMUM_BUYING_QTY
4851 -- 11. START_DATE_MINIMUM_BUYING_QTY can not be more than END_DATE_MINIMUM_BUYING_QTY
4852 -- 12. START_DATE_MAXIMUM_BUYING_QTY can not be more than END_DATE_MAXIMUM_BUYING_QTY
4853 BEGIN
4854 IF l_do_tp_validations THEN
4855 l_sql := ' SELECT AGREED_MINIMUM_BUYING_QTY, ' ||
4856 ' AGREED_MAXIMUM_BUYING_QTY, ' ||
4857 ' START_DATE_MINIMUM_BUYING_QTY, ' ||
4858 ' END_DATE_MINIMUM_BUYING_QTY, ' ||
4859 ' START_DATE_MAXIMUM_BUYING_QTY, ' ||
4860 ' END_DATE_MAXIMUM_BUYING_QTY ' ||
4861 ' FROM EGO_SBDH_BUYING_QTY_INFO_TPV O ' ||
4862 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4863 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4864 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4865 ' OR (PARTY_SITE_ID IS NULL '||
4866 ' AND NOT EXISTS (SELECT NULL ' ||
4867 ' FROM EGO_SBDH_BUYING_QTY_INFO_TPV I ' ||
4868 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4869 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4870 ' AND I.PARTY_SITE_ID = :2)))';
4871 EXECUTE IMMEDIATE l_sql INTO l_min_value, l_max_value, l_start_date, l_end_date, l_start_date1, l_end_date1
4872 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4873 ELSE
4874 l_sql := ' SELECT AGREED_MINIMUM_BUYING_QTY, ' ||
4875 ' AGREED_MAXIMUM_BUYING_QTY, ' ||
4876 ' START_DATE_MINIMUM_BUYING_QTY, ' ||
4877 ' END_DATE_MINIMUM_BUYING_QTY, ' ||
4878 ' START_DATE_MAXIMUM_BUYING_QTY, ' ||
4879 ' END_DATE_MAXIMUM_BUYING_QTY ' ||
4880 ' FROM EGO_SBDH_BUYING_QTY_INFO_AGV O ' ||
4881 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4882 ' AND ORGANIZATION_ID = :p_organization_id ';
4883 EXECUTE IMMEDIATE l_sql INTO l_min_value, l_max_value, l_start_date, l_end_date, l_start_date1, l_end_date1
4884 USING p_inventory_item_id, p_organization_id;
4885 END IF; --IF l_do_tp_validations THEN
4886
4887 -- 10. AGREED_MINIMUM_BUYING_QTY can not be more than AGREED_MAXIMUM_BUYING_QTY
4888 IF l_min_value > l_max_value THEN
4889 RETURN 'F';
4890 END IF;
4891
4892 -- 11. START_DATE_MINIMUM_BUYING_QTY can not be more than END_DATE_MINIMUM_BUYING_QTY
4893 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4894 RETURN 'F';
4895 END IF;
4896
4897 -- 12. START_DATE_MAXIMUM_BUYING_QTY can not be more than END_DATE_MAXIMUM_BUYING_QTY
4898 IF NVL(l_start_date1, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date1, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4899 RETURN 'F';
4900 END IF;
4901 EXCEPTION WHEN NO_DATA_FOUND THEN
4902 NULL;
4903 END;
4904
4905 -- 13. FIRST_ORDER_DATE can not be more than LAST_ORDER_DATE
4906 BEGIN
4907 IF l_do_tp_validations THEN
4908 l_sql := ' SELECT FIRST_ORDER_DATE, ' ||
4909 ' LAST_ORDER_DATE ' ||
4910 ' FROM EGO_SBDH_ORDERING_INFO_TPV O ' ||
4911 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4912 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4913 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4914 ' OR (PARTY_SITE_ID IS NULL '||
4915 ' AND NOT EXISTS (SELECT NULL ' ||
4916 ' FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
4917 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4918 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4919 ' AND I.PARTY_SITE_ID = :2)))';
4920 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4921 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4922 ELSE
4923 l_sql := ' SELECT FIRST_ORDER_DATE, ' ||
4924 ' LAST_ORDER_DATE ' ||
4925 ' FROM EGO_SBDH_ORDERING_INFO_AGV O ' ||
4926 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4927 ' AND ORGANIZATION_ID = :p_organization_id ';
4928 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4929 USING p_inventory_item_id, p_organization_id;
4930 END IF; --IF l_do_tp_validations THEN
4931
4932 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4933 RETURN 'F';
4934 END IF;
4935 EXCEPTION WHEN NO_DATA_FOUND THEN
4936 NULL;
4937 END;
4938
4939 -- 14. FIRST_SHIP_DATE can not be more than LAST_SHIP_DATE
4940 BEGIN
4941 IF l_do_tp_validations THEN
4942 l_sql := ' SELECT FIRST_SHIP_DATE, ' ||
4943 ' LAST_SHIP_DATE ' ||
4944 ' FROM EGO_SBDH_SHIP_EXCL_DATES_TPV O ' ||
4945 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4946 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4947 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4948 ' OR (PARTY_SITE_ID IS NULL '||
4949 ' AND NOT EXISTS (SELECT NULL ' ||
4950 ' FROM EGO_SBDH_SHIP_EXCL_DATES_TPV I ' ||
4951 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4952 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4953 ' AND I.PARTY_SITE_ID = :2)))';
4954 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4955 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
4956 ELSE
4957 l_sql := ' SELECT FIRST_SHIP_DATE, ' ||
4958 ' LAST_SHIP_DATE ' ||
4959 ' FROM EGO_SBDH_SHIP_EXCL_DATES_AGV O ' ||
4960 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4961 ' AND ORGANIZATION_ID = :p_organization_id ';
4962 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4963 USING p_inventory_item_id, p_organization_id;
4964 END IF; --IF l_do_tp_validations THEN
4965
4966 IF NVL(l_start_date, TO_DATE('01-01-1990', 'DD-MM-YYYY')) > NVL(l_end_date, TO_DATE('31-12-9990', 'DD-MM-YYYY')) THEN
4967 RETURN 'F';
4968 END IF;
4969 EXCEPTION WHEN NO_DATA_FOUND THEN
4970 NULL;
4971 END;
4972
4973 -- 15. MIN_TRADE_ITEM_LIFE_ARR can not be more than MIN_TRADE_ITEM_LIFE_PROD
4974 BEGIN
4975 l_sql := ' SELECT NVL(C.MIN_TRADE_ITEM_LIFE_ARR, MSI.SHELF_LIFE_DAYS) MIN_TRADE_ITEM_LIFE_ARR ' ||
4976 ' FROM MTL_SYSTEM_ITEMS_B MSI,EGO_ITEM_CUST_ATTRS_B C ' ||
4977 ' WHERE C.PARTY_SITE_ID (+) = :party_site_id ' ||
4978 ' AND MSI.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4979 ' AND MSI.ORGANIZATION_ID = :p_organization_id ' ||
4980 ' AND MSI.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID (+) '||
4981 ' AND MSI.ORGANIZATION_ID = C.MASTER_ORGANIZATION_ID (+) ';
4982
4983 EXECUTE IMMEDIATE l_sql INTO l_min_value
4984 USING l_party_site_id, p_inventory_item_id, p_organization_id;
4985
4986 IF l_do_tp_validations THEN
4987 l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
4988 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV O ' ||
4989 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
4990 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
4991 ' AND (PARTY_SITE_ID = :party_site_id ' ||
4992 ' OR (PARTY_SITE_ID IS NULL '||
4993 ' AND NOT EXISTS (SELECT NULL ' ||
4994 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV I ' ||
4995 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
4996 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
4997 ' AND I.PARTY_SITE_ID = :2)))';
4998 EXECUTE IMMEDIATE l_sql INTO l_max_value
4999 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
5000 ELSE
5001 l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
5002 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_AGV O ' ||
5003 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
5004 ' AND ORGANIZATION_ID = :p_organization_id ';
5005 EXECUTE IMMEDIATE l_sql INTO l_max_value
5006 USING p_inventory_item_id, p_organization_id;
5007 END IF; --IF l_do_tp_validations THEN
5008
5009 IF l_min_value > l_max_value THEN
5010 RETURN 'F';
5011 END IF;
5012 EXCEPTION WHEN NO_DATA_FOUND THEN
5013 NULL;
5014 END;
5015
5016 -- 16. UOM for GOODS_PICK_UP_LEAD_TIME must be present if GOODS_PICK_UP_LEAD_TIME is present
5017 BEGIN
5018 IF l_do_tp_validations THEN
5019 l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
5020 ' FROM EGO_SBDH_ORDERING_INFO_TPV O' ||
5021 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
5022 ' AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
5023 ' AND (PARTY_SITE_ID = :party_site_id ' ||
5024 ' OR (PARTY_SITE_ID IS NULL '||
5025 ' AND NOT EXISTS (SELECT NULL ' ||
5026 ' FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
5027 ' WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
5028 ' AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
5029 ' AND I.PARTY_SITE_ID = :2)))';
5030
5031 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom
5032 USING p_inventory_item_id, p_organization_id, l_party_site_id, l_party_site_id;
5033 ELSE
5034 l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
5035 ' FROM EGO_SBDH_ORDERING_INFO_AGV ' ||
5036 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
5037 ' AND ORGANIZATION_ID = :p_organization_id ';
5038
5039 EXECUTE IMMEDIATE l_sql INTO l_attr_value, l_attr_value_uom
5040 USING p_inventory_item_id, p_organization_id;
5041 END IF; --IF l_do_tp_validations THEN
5042
5043 IF l_attr_value IS NOT NULL AND l_attr_value_uom IS NULL THEN
5044 RETURN 'F';
5045 END IF;
5046 EXCEPTION
5047 WHEN column_not_found THEN
5048 RETURN 'F';
5049 WHEN NO_DATA_FOUND THEN
5050 NULL;
5051 END;
5052
5053 RETURN 'T';
5054 END Is_SBDH_Attributes_Valid;
5055
5056 /*
5057 * This method is called after Trade Item Descriptor is updated and
5058 * item is a GDSN Outbound Enabled Item. This method NULLs out all the attributes
5059 * that are not updateable at Non-Leaf level
5060 */
5061 PROCEDURE PROCESS_GTID_UPDATE (p_inventory_item_id NUMBER,
5062 p_organization_id NUMBER,
5063 p_trade_item_desc VARCHAR2,
5064 x_return_status OUT NOCOPY VARCHAR2,
5065 x_msg_count OUT NOCOPY NUMBER,
5066 x_msg_data OUT NOCOPY VARCHAR2)
5067 IS
5068 CURSOR c_single_attrs_not_upd IS
5069 SELECT DATA_TYPE_CODE, DATABASE_COLUMN
5070 FROM EGO_ATTRS_V
5071 WHERE ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
5072 AND APPLICATION_ID = 431
5073 AND EDIT_IN_HIERARCHY_CODE LIKE 'L%'
5074 AND NVL(ENABLED_FLAG, 'N') = 'Y';
5075
5076 l_b_sql VARCHAR2(32000);
5077 l_tl_sql VARCHAR2(32000);
5078 l_execute_b_sql BOOLEAN;
5079 l_execute_tl_sql BOOLEAN;
5080 BEGIN
5081 SAVEPOINT GTID_UPDATE;
5082 WRITE_DEBUG_LOG('Starting PROCESS_GTID_UPDATE');
5083 WRITE_DEBUG_LOG('Parameters: p_inventory_item_id, p_organization_id, p_trade_item_desc=' || p_inventory_item_id ||','|| p_organization_id||','|| p_trade_item_desc);
5084 IF NVL(p_trade_item_desc, 'BASE_UNIT_OR_EACH') <> 'BASE_UNIT_OR_EACH' THEN
5085 l_b_sql := 'UPDATE EGO_ITEM_GTN_ATTRS_B SET ';
5086 l_tl_sql := 'UPDATE EGO_ITEM_GTN_ATTRS_TL SET ';
5087 l_execute_b_sql := FALSE;
5088 l_execute_tl_sql := FALSE;
5089 FOR i IN c_single_attrs_not_upd LOOP
5090 IF i.DATA_TYPE_CODE <> 'A' THEN
5091 l_b_sql := l_b_sql || i.DATABASE_COLUMN || ' = NULL ,';
5092 l_execute_b_sql := TRUE;
5093 ELSE
5094 l_tl_sql := l_tl_sql || i.DATABASE_COLUMN || ' = NULL ,';
5095 l_execute_tl_sql := TRUE;
5096 END IF;
5097 END LOOP;
5098 l_b_sql := RTRIM(l_b_sql, ',') || ' WHERE INVENTORY_ITEM_ID = :1 AND ORGANIZATION_ID = :2 ';
5099 l_tl_sql := RTRIM(l_tl_sql, ',') || ' WHERE INVENTORY_ITEM_ID = :1 AND ORGANIZATION_ID = :2 ';
5100
5101 WRITE_DEBUG_LOG('l_b_sql='||l_b_sql);
5102 WRITE_DEBUG_LOG('l_tl_sql='||l_tl_sql);
5103
5104 IF l_execute_b_sql THEN
5105 EXECUTE IMMEDIATE l_b_sql USING p_inventory_item_id, p_organization_id;
5106 END IF;
5107
5108 IF l_execute_tl_sql THEN
5109 EXECUTE IMMEDIATE l_tl_sql USING p_inventory_item_id, p_organization_id;
5110 END IF;
5111
5112 WRITE_DEBUG_LOG('Deleting multi-row attributes');
5113
5114 DELETE FROM EGO_ITM_GTN_MUL_ATTRS_B
5115 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
5116 AND ORGANIZATION_ID = p_organization_id
5117 AND ATTR_GROUP_ID IN (SELECT AG.ATTR_GROUP_ID
5118 FROM EGO_FND_DSC_FLX_CTX_EXT AG, EGO_ATTRS_V EAV
5119 WHERE AG.APPLICATION_ID = 431
5120 AND EAV.APPLICATION_ID = AG.APPLICATION_ID
5121 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = EAV.ATTR_GROUP_TYPE
5122 AND AG.DESCRIPTIVE_FLEX_CONTEXT_CODE = EAV.ATTR_GROUP_NAME
5123 AND EAV.EDIT_IN_HIERARCHY_CODE LIKE 'L%'
5124 AND NVL(EAV.ENABLED_FLAG, 'N') = 'Y'
5125 AND EAV.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS');
5126 WRITE_DEBUG_LOG('Deleted '||SQL%ROWCOUNT||' rows');
5127 END IF;
5128 x_return_status := 'S';
5129 x_msg_data := NULL;
5130 x_msg_count := 0;
5131 WRITE_DEBUG_LOG('Done PROCESS_GTID_UPDATE');
5132 EXCEPTION WHEN OTHERS THEN
5133 ROLLBACK TO SAVEPOINT GTID_UPDATE;
5134 WRITE_DEBUG_LOG('Error in PROCESS_GTID_UPDATE-'||SQLERRM);
5135 x_return_status := 'U';
5136 x_msg_data := SQLERRM;
5137 x_msg_count := 1;
5138 END PROCESS_GTID_UPDATE;
5139
5140 END EGO_GTIN_PVT;