[Home] [Help]
1: PACKAGE BODY ego_item_associations_pub AS
2: /* $Header: EGOPIASB.pls 120.30 2007/10/05 12:37:01 earumuga noship $ */
3:
4: G_FILE_NAME VARCHAR2(12);
5: G_PKG_NAME VARCHAR2(30);
7: G_USER_ID fnd_user.user_id%TYPE;
8: G_PARTY_ID hz_parties.party_id%TYPE;
9: G_LOGIN_ID fnd_user.last_update_login%TYPE;
10: G_REQUEST_ID NUMBER;
11: G_PROG_APPID ego_item_associations_intf.program_application_id%TYPE;
12: G_PROG_ID ego_item_associations_intf.program_id%TYPE;
13: G_SYSDATE fnd_user.creation_date%TYPE;
14: G_SESSION_LANG VARCHAR2(99);
15: G_DATA_LEVEL_NAMES VARCHAR2_TBL_TYPE;
8: G_PARTY_ID hz_parties.party_id%TYPE;
9: G_LOGIN_ID fnd_user.last_update_login%TYPE;
10: G_REQUEST_ID NUMBER;
11: G_PROG_APPID ego_item_associations_intf.program_application_id%TYPE;
12: G_PROG_ID ego_item_associations_intf.program_id%TYPE;
13: G_SYSDATE fnd_user.creation_date%TYPE;
14: G_SESSION_LANG VARCHAR2(99);
15: G_DATA_LEVEL_NAMES VARCHAR2_TBL_TYPE;
16: G_LOG_TIMESTAMP_FORMAT VARCHAR2(25) := 'DD-MM-YYYY HH24:MI:SS';
60: --
61: -- file names
62: --
63: G_FILE_NAME := NVL(G_FILE_NAME,'EGOPIASB.pls');
64: G_PKG_NAME := NVL(G_PKG_NAME,'EGO_ITEM_ASSOCIATIONS_PUB');
65: --
66: -- user values
67: --
68: G_USER_ID := FND_GLOBAL.user_id;
119: PROCEDURE initialize( p_batch_id IN NUMBER )
120: IS
121: BEGIN
122: FOR l_null_tx_rec IN ( SELECT ROWID
123: FROM ego_item_associations_intf
124: WHERE batch_id = p_batch_id
125: AND process_flag = G_REC_TO_BE_PROCESSED
126: AND transaction_id IS NULL
127: )
125: AND process_flag = G_REC_TO_BE_PROCESSED
126: AND transaction_id IS NULL
127: )
128: LOOP
129: UPDATE ego_item_associations_intf
130: SET transaction_id = mtl_system_items_interface_s.nextval
131: WHERE ROWID = l_null_tx_rec.ROWID;
132: END LOOP;
133: -- Set the process flag to in process
130: SET transaction_id = mtl_system_items_interface_s.nextval
131: WHERE ROWID = l_null_tx_rec.ROWID;
132: END LOOP;
133: -- Set the process flag to in process
134: UPDATE ego_item_associations_intf
135: SET process_flag = G_REC_IN_PROCESS
136: WHERE batch_id = p_batch_id
137: AND process_flag = G_REC_TO_BE_PROCESSED;
138: -- Check Required Values based on Transaction Type
137: AND process_flag = G_REC_TO_BE_PROCESSED;
138: -- Check Required Values based on Transaction Type
139:
140: -- Atleast one of the item value and one of the org value should be populated
141: UPDATE ego_item_associations_intf
142: SET process_flag = G_REC_MISSING_REQ_VALUE
143: WHERE batch_id = p_batch_id
144: AND process_flag = G_REC_IN_PROCESS
145: AND transaction_type = G_CREATE
145: AND transaction_type = G_CREATE
146: AND ( ( inventory_item_id IS NULL AND item_number IS NULL )
147: OR ( organization_id IS NULL AND organization_code IS NULL)
148: );
149: UPDATE ego_item_associations_intf
150: SET process_flag = G_REC_MISSING_REQ_VALUE
151: WHERE batch_id = p_batch_id
152: AND process_flag = G_REC_IN_PROCESS
153: AND transaction_type = G_CREATE
189: IS
190: BEGIN
191: /* Do not convert name to ids, which is required only for error reporting.
192: -- Convert the master org values. Convert Organization Id to Organization Code
193: UPDATE ego_item_associations_intf eiai
194: SET organization_code = ( SELECT mp.organization_code
195: FROM mtl_parameters mp
196: WHERE mp.organization_id = eiai.organization_id
197: AND mp.master_organization_id = mp.organization_id)
201: AND eiai.organization_code IS NULL
202: AND eiai.organization_id IS NOT NULL;
203:
204: -- Convert the org values. Convert Organization Id to Organization Code
205: UPDATE ego_item_associations_intf eiai
206: SET organization_code = ( SELECT mp.organization_code
207: FROM mtl_parameters mp
208: WHERE mp.organization_id = eiai.organization_id)
209: WHERE eiai.batch_id = p_batch_id
212: AND eiai.organization_code IS NULL
213: AND eiai.organization_id IS NOT NULL;
214:
215: -- check whether item value converion is required..
216: UPDATE ego_item_associations_intf eiai
217: SET item_number = ( SELECT concatenated_segments
218: FROM mtl_system_items_b_kfv msibk
219: WHERE msibk.organization_id = eiai.organization_id
220: AND msibk.inventory_item_id = eiai.inventory_item_id
223: AND eiai.process_flag = G_REC_IN_PROCESS
224: AND eiai.item_number IS NULL
225: AND eiai.inventory_item_id IS NOT NULL;
226: */
227: UPDATE ego_item_associations_intf eiai
228: SET inventory_item_id = ( SELECT inventory_item_id
229: FROM mtl_system_items_b_kfv msibk
230: WHERE msibk.organization_id = eiai.organization_id
231: AND msibk.concatenated_segments = eiai.item_number
234: AND eiai.process_flag = G_REC_IN_PROCESS
235: AND eiai.inventory_item_id IS NULL
236: AND eiai.item_number IS NOT NULL;
237: /*
238: UPDATE ego_item_associations_intf eiai
239: SET (supplier_number,supplier_name) = ( SELECT segment1, vendor_name
240: FROM ap_suppliers aas
241: WHERE aas.vendor_id = eiai.pk1_value
242: )
243: WHERE eiai.batch_id = p_batch_id
244: AND eiai.process_flag = G_REC_IN_PROCESS
245: AND eiai.supplier_number IS NULL
246: AND eiai.pk1_value IS NOT NULL;
247: UPDATE ego_item_associations_intf eiai
248: SET supplier_site_name = ( SELECT vendor_site_code
249: FROM ap_supplier_sites_all asa
250: WHERE asa.vendor_site_id = eiai.pk2_value
251: AND asa.org_id = fnd_profile.value('ORG_ID')
254: AND eiai.process_flag = G_REC_IN_PROCESS
255: AND eiai.supplier_site_name IS NULL
256: AND eiai.pk2_value IS NOT NULL;
257:
258: UPDATE ego_item_associations_intf eiai
259: SET item_number = ( SELECT concatenated_segments
260: FROM mtl_system_items_b_kfv msibk
261: WHERE msibk.organization_id = eiai.organization_id
262: AND msibk.inventory_item_id = eiai.inventory_item_id
266: AND eiai.item_number IS NULL
267: AND eiai.inventory_item_id IS NOT NULL;
268: */
269:
270: UPDATE ego_item_associations_intf eiai
271: SET association_id = ( SELECT eia.association_id
272: FROM ego_item_associations eia
273: WHERE eia.data_level_id = eiai.data_level_id
274: AND eia.organization_id = eiai.organization_id
268: */
269:
270: UPDATE ego_item_associations_intf eiai
271: SET association_id = ( SELECT eia.association_id
272: FROM ego_item_associations eia
273: WHERE eia.data_level_id = eiai.data_level_id
274: AND eia.organization_id = eiai.organization_id
275: AND eia.inventory_item_id = eiai.inventory_item_id
276: AND eia.pk1_value = eiai.pk1_value
279: WHERE eiai.batch_id = p_batch_id
280: AND eiai.process_flag = G_REC_IN_PROCESS
281: AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE );
282: /*
283: UPDATE ego_item_associations_intf eiai1
284: SET process_flag = G_REC_DUPLICATE
285: WHERE eiai1.batch_id = p_batch_id
286: AND EXISTS
287: (
285: WHERE eiai1.batch_id = p_batch_id
286: AND EXISTS
287: (
288: SELECT 1
289: FROM ego_item_associations_intf eiai2
290: WHERE eiai2.transaction_type = eiai1.transaction_type
291: AND eiai2.batch_id = eiai1.batch_id
292: AND eiai2.transaction_id <> eiai1.transaction_id
293: AND eiai2.inventory_item_id = eiai1.inventory_item_id
296: AND eiai2.pk1_value = eiai1.pk1_value
297: AND NVL(eiai1.pk2_value,-1) = NVL(eiai2.pk2_value,-1)
298: );
299: */
300: UPDATE ego_item_associations_intf eiai
301: SET process_flag = G_REC_INVALID_MASTER_ORG
302: WHERE eiai.batch_id = p_batch_id
303: AND eiai.process_flag = G_REC_IN_PROCESS
304: AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
309: FROM mtl_parameters mp
310: WHERE mp.master_organization_id = eiai.organization_id
311: )
312: );
313: UPDATE ego_item_associations_intf eiai
314: SET process_flag = G_REC_INVALID_ORG
315: WHERE eiai.batch_id = p_batch_id
316: AND eiai.process_flag = G_REC_IN_PROCESS
317: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
322: FROM mtl_parameters mp
323: WHERE mp.organization_id = eiai.organization_id
324: )
325: );
326: UPDATE ego_item_associations_intf eiai
327: SET process_flag = G_REC_INVALID_ITEM
328: WHERE eiai.batch_id = p_batch_id
329: AND eiai.process_flag = G_REC_IN_PROCESS
330: AND ( eiai.inventory_item_id IS NULL
335: WHERE msib.inventory_item_id = eiai.inventory_item_id
336: AND msib.organization_id = eiai.organization_id
337: )
338: );
339: UPDATE ego_item_associations_intf eiai
340: SET process_flag = G_REC_INVALID_PK1_VALUE
341: WHERE eiai.batch_id = p_batch_id
342: AND eiai.process_flag = G_REC_IN_PROCESS
343: AND ( eiai.pk1_value IS NULL
347: FROM ap_suppliers aas
348: WHERE aas.vendor_id = eiai.pk1_value
349: )
350: );
351: UPDATE ego_item_associations_intf eiai
352: SET process_flag = G_REC_INVALID_PK2_VALUE
353: WHERE eiai.batch_id = p_batch_id
354: AND eiai.process_flag = G_REC_IN_PROCESS
355: AND ( eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
366: AND assa.vendor_id = eiai.pk1_value
367: AND assa.org_id = fnd_profile.value('ORG_ID')
368: )
369: );
370: UPDATE ego_item_associations_intf eiai
371: SET process_flag = G_REC_ASSOCIATION_NOT_EXISTS
372: WHERE eiai.batch_id = p_batch_id
373: AND eiai.process_flag = G_REC_IN_PROCESS
374: AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE )
372: WHERE eiai.batch_id = p_batch_id
373: AND eiai.process_flag = G_REC_IN_PROCESS
374: AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE )
375: AND eiai.association_id IS NULL;
376: UPDATE ego_item_associations_intf eiai
377: SET process_flag = G_REC_INVALID_ASSOC_TYPE
378: WHERE eiai.batch_id = p_batch_id
379: AND eiai.process_flag = G_REC_IN_PROCESS
380: AND NOT EXISTS
382: SELECT 1
383: FROM ego_data_level_b edlb
384: WHERE edlb.data_level_id = eiai.data_level_id
385: );
386: UPDATE ego_item_associations_intf eiai
387: SET process_flag = G_REC_INVALID_STATUS
388: WHERE eiai.batch_id = p_batch_id
389: AND eiai.process_flag = G_REC_IN_PROCESS
390: AND eiai.status_code IS NOT NULL
394: FROM fnd_lookups fl
395: WHERE fl.lookup_type = 'EGO_ASSOCIATION_STATUS'
396: AND fl.lookup_code = eiai.status_code
397: );
398: UPDATE ego_item_associations_intf eiai
399: SET process_flag = G_REC_INVALID_PRIMARY
400: WHERE eiai.batch_id = p_batch_id
401: AND eiai.process_flag = G_REC_IN_PROCESS
402: AND eiai.primary_flag IS NOT NULL
450:
451: IF x_return_status IN ('T','F') THEN
452: IF l_sec_predicate IS NOT NULL THEN
453: l_dynamic_sql :=
454: ' UPDATE ego_item_associations_intf eiai ' ||
455: ' SET process_flag = '||G_REC_NO_CREATE_ASSOC_PRIV ||
456: ' WHERE batch_id = :p_batch_id '||
457: ' AND process_flag = '||G_REC_IN_PROCESS||
458: ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
483: ,x_return_status => x_return_status );
484: IF x_return_status IN ('T','F') THEN
485: IF l_sec_predicate IS NOT NULL THEN
486: l_dynamic_sql :=
487: ' UPDATE ego_item_associations_intf eiai ' ||
488: ' SET process_flag = '||G_REC_NO_EDIT_ASSOC_PRIV ||
489: ' WHERE batch_id = :p_batch_id '||
490: ' AND process_flag = '||G_REC_IN_PROCESS||
491: ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
517: ,x_return_status => x_return_status );
518: IF x_return_status IN ('T','F') THEN
519: IF l_sec_predicate IS NOT NULL THEN
520: l_dynamic_sql :=
521: ' UPDATE ego_item_associations_intf eiai ' ||
522: ' SET process_flag = '||G_REC_NO_EDIT_ITEM_ORG_PRIV ||
523: ' WHERE batch_id = :p_batch_id '||
524: ' AND data_level_id = 43105 '||
525: ' AND process_flag = '||G_REC_IN_PROCESS||
537: fnd_message.set_token ('SQL_ERR_MSG', l_msg_data);
538: APP_EXCEPTION.RAISE_EXCEPTION();
539: END IF;
540:
541: IF ego_item_associations_util.is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
542: UPDATE ego_item_associations_intf eiai
543: SET process_flag = G_REC_NO_SUPPL_ACCESS_PRIV
544: WHERE eiai.batch_id = p_batch_id
545: AND eiai.process_flag = G_REC_IN_PROCESS
538: APP_EXCEPTION.RAISE_EXCEPTION();
539: END IF;
540:
541: IF ego_item_associations_util.is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
542: UPDATE ego_item_associations_intf eiai
543: SET process_flag = G_REC_NO_SUPPL_ACCESS_PRIV
544: WHERE eiai.batch_id = p_batch_id
545: AND eiai.process_flag = G_REC_IN_PROCESS
546: AND NOT EXISTS
574: */
575: PROCEDURE validate_associations(p_batch_id IN NUMBER)
576: IS
577: BEGIN
578: UPDATE ego_item_associations_intf eiai
579: SET process_flag = G_REC_ORG_NO_ACCESS
580: WHERE eiai.batch_id = p_batch_id
581: AND eiai.process_flag = G_REC_IN_PROCESS
582: AND NOT EXISTS
586: WHERE oav.organization_id = eiai.organization_id
587: AND oav.responsibility_id = FND_PROFILE.Value('RESP_ID')
588: AND oav.resp_application_id = FND_PROFILE.Value('RESP_APPL_ID')
589: );
590: UPDATE ego_item_associations_intf eiai
591: SET process_flag = G_REC_ALREADY_ASSIGNED
592: WHERE eiai.batch_id = p_batch_id
593: AND eiai.process_flag = G_REC_IN_PROCESS
594: AND eiai.transaction_type = G_CREATE
594: AND eiai.transaction_type = G_CREATE
595: AND EXISTS
596: (
597: SELECT 1
598: FROM ego_item_associations eia
599: WHERE eia.inventory_item_id = eiai.inventory_item_id
600: AND eia.organization_id = eiai.organization_id
601: AND eia.data_level_id = eiai.data_level_id
602: AND eia.pk1_value = eiai.pk1_value
603: AND ( ( eia.pk2_value IS NULL AND eiai.pk2_value IS NULL )
604: OR ( eia.pk2_value = eiai.pk2_value )
605: )
606: );
607: UPDATE ego_item_associations_intf eiai
608: SET process_flag = G_REC_ASSOC_SITE_NOT_EXISTS
609: WHERE eiai.batch_id = p_batch_id
610: AND eiai.process_flag = G_REC_IN_PROCESS
611: AND eiai.transaction_type = G_CREATE
615: FROM ap_supplier_sites_all assa
616: WHERE assa.vendor_id = eiai.pk1_value
617: AND assa.org_id = fnd_profile.value('ORG_ID')
618: );
619: UPDATE ego_item_associations_intf eiai
620: SET process_flag = G_REC_ASSOC_ITEM_NOT_IN_ORG
621: WHERE eiai.batch_id = p_batch_id
622: AND eiai.process_flag = G_REC_IN_PROCESS
623: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
630: AND msibk.organization_id = eiai.organization_id
631: );
632: -- Primary has been set in Create-Create or Create-Update or Update - Update
633: /*
634: UPDATE ego_item_associations_intf eiai
635: SET process_flag = G_REC_DUPLICATE_PRIMARY
636: WHERE eiai.batch_id = p_batch_id
637: AND eiai.process_flag = G_REC_IN_PROCESS
638: AND eiai.primary_flag = 'Y'
639: AND ( eiai.transaction_type = G_CREATE OR eiai.transaction_type = G_UPDATE)
640: AND exists
641: (
642: SELECT 1
643: FROM ego_item_associations_intf eiai2
644: WHERE eiai2.batch_id = eiai.batch_id
645: AND eiai2.process_flag = eiai.process_flag
646: AND eiai2.primary_flag = eiai.primary_flag
647: AND eiai2.inventory_item_id = eiai.inventory_item_id
654: );
655: */
656: -- If more than one row have primary flag set, then unset for all other records
657: -- except the last one (mode CREATE)
658: UPDATE ego_item_associations_intf eiai
659: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
660: WHERE eiai.batch_id = p_batch_id
661: AND eiai.process_flag = G_REC_IN_PROCESS
662: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
663: AND eiai.transaction_type = G_CREATE
664: AND eiai.ROWID NOT IN
665: (
666: SELECT MAX(eiai2.ROWID)
667: FROM ego_item_associations_intf eiai2
668: WHERE eiai2.batch_id = p_batch_id
669: AND eiai2.process_flag = G_REC_IN_PROCESS
670: AND eiai2.primary_flag = G_PRIMARY
671: AND eiai2.transaction_type = G_CREATE
672: AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
673: GROUP BY eiai2.inventory_item_id, eiai2.organization_id
674: HAVING count(*) >= 1
675: );
676: UPDATE ego_item_associations_intf eiai
677: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
678: WHERE eiai.batch_id = p_batch_id
679: AND eiai.process_flag = G_REC_IN_PROCESS
680: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
681: AND eiai.transaction_type = G_CREATE
682: AND eiai.ROWID NOT IN
683: (
684: SELECT MAX(eiai2.ROWID)
685: FROM ego_item_associations_intf eiai2
686: WHERE eiai2.batch_id = p_batch_id
687: AND eiai2.process_flag = G_REC_IN_PROCESS
688: AND eiai2.primary_flag = G_PRIMARY
689: AND eiai2.transaction_type = G_CREATE
692: HAVING count(*) >= 1
693: );
694: -- If more than one row have primary flag set, then unset for all other records
695: -- except the last one (mode UPDATE)
696: UPDATE ego_item_associations_intf eiai
697: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
698: WHERE eiai.batch_id = p_batch_id
699: AND eiai.process_flag = G_REC_IN_PROCESS
700: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
701: AND eiai.transaction_type = G_UPDATE
702: AND eiai.ROWID NOT IN
703: (
704: SELECT MAX(eiai2.ROWID)
705: FROM ego_item_associations_intf eiai2
706: WHERE eiai2.batch_id = p_batch_id
707: AND eiai2.process_flag = G_REC_IN_PROCESS
708: AND eiai2.primary_flag = G_PRIMARY
709: AND eiai2.transaction_type = G_UPDATE
710: AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
711: GROUP BY eiai2.inventory_item_id, eiai2.organization_id
712: HAVING count(*) >= 1
713: );
714: UPDATE ego_item_associations_intf eiai
715: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
716: WHERE eiai.batch_id = p_batch_id
717: AND eiai.process_flag = G_REC_IN_PROCESS
718: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
719: AND eiai.transaction_type = G_UPDATE
720: AND eiai.ROWID NOT IN
721: (
722: SELECT MAX(eiai2.ROWID)
723: FROM ego_item_associations_intf eiai2
724: WHERE eiai2.batch_id = p_batch_id
725: AND eiai2.process_flag = G_REC_IN_PROCESS
726: AND eiai2.primary_flag = G_PRIMARY
727: AND eiai2.transaction_type = G_UPDATE
730: HAVING count(*) >= 1
731: );
732: -- If the both CREATE and UPDATE has primary flag set then unset for CREATE operations
733: -- because UPDATE is the last operation to be performed
734: UPDATE ego_item_associations_intf eiai
735: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
736: WHERE eiai.batch_id = p_batch_id
737: AND eiai.process_flag = G_REC_IN_PROCESS
738: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
739: AND eiai.transaction_type = G_CREATE
740: AND EXISTS
741: (
742: SELECT 1
743: FROM ego_item_associations_intf eiai2
744: WHERE eiai2.batch_id = p_batch_id
745: AND eiai2.process_flag = G_REC_IN_PROCESS
746: AND eiai2.inventory_item_id = eiai.inventory_item_id
747: AND eiai2.organization_id = eiai.organization_id
748: AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
749: AND eiai2.primary_flag = G_PRIMARY
750: AND eiai2.transaction_type = G_UPDATE
751: );
752: UPDATE ego_item_associations_intf eiai
753: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
754: WHERE eiai.batch_id = p_batch_id
755: AND eiai.process_flag = G_REC_IN_PROCESS
756: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
757: AND eiai.transaction_type = G_CREATE
758: AND EXISTS
759: (
760: SELECT 1
761: FROM ego_item_associations_intf eiai2
762: WHERE eiai2.batch_id = p_batch_id
763: AND eiai2.process_flag = G_REC_IN_PROCESS
764: AND eiai2.inventory_item_id = eiai.inventory_item_id
765: AND eiai2.organization_id = eiai.organization_id
767: AND eiai2.pk1_value = eiai.pk1_value
768: AND eiai2.primary_flag = G_PRIMARY
769: AND eiai2.transaction_type = G_UPDATE
770: );
771: UPDATE ego_item_associations eia
772: SET primary_flag = G_DEFAULT_PRIMARY_FLAG
773: WHERE primary_flag = G_PRIMARY
774: AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
775: AND EXISTS
773: WHERE primary_flag = G_PRIMARY
774: AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
775: AND EXISTS
776: ( SELECT 1
777: FROM ego_item_associations_intf eiai
778: WHERE eiai.inventory_item_id = eia.inventory_item_id
779: AND eiai.organization_id = eia.organization_id
780: AND eiai.pk1_value <> eia.pk1_value
781: AND eiai.batch_id = p_batch_id
784: AND eiai.transaction_type = G_UPDATE
785: AND eiai.data_level_id = eia.data_level_id
786: UNION ALL
787: SELECT 1
788: FROM ego_item_associations_intf eiai
789: WHERE eiai.inventory_item_id = eia.inventory_item_id
790: AND eiai.organization_id = eia.organization_id
791: AND eiai.pk1_value <> eia.pk1_value
792: AND eiai.batch_id = p_batch_id
795: AND eiai.transaction_type = G_CREATE
796: AND eiai.data_level_id = eia.data_level_id
797: );
798:
799: UPDATE ego_item_associations eia
800: SET primary_flag = G_DEFAULT_PRIMARY_FLAG
801: WHERE eia.primary_flag = G_PRIMARY
802: AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
803: AND EXISTS
801: WHERE eia.primary_flag = G_PRIMARY
802: AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
803: AND EXISTS
804: ( SELECT 1
805: FROM ego_item_associations_intf eiai
806: WHERE eiai.inventory_item_id = eia.inventory_item_id
807: AND eiai.organization_id = eia.organization_id
808: AND eiai.pk1_value = eia.pk1_value
809: AND eiai.pk2_value <> eia.pk2_value
813: AND eiai.transaction_type = G_UPDATE
814: AND eiai.data_level_id = eia.data_level_id
815: UNION ALL
816: SELECT 1
817: FROM ego_item_associations_intf eiai
818: WHERE eiai.inventory_item_id = eia.inventory_item_id
819: AND eiai.organization_id = eia.organization_id
820: AND eiai.pk1_value = eia.pk1_value
821: AND eiai.pk2_value <> eia.pk2_value
824: AND eiai.primary_flag = G_PRIMARY
825: AND eiai.transaction_type = G_CREATE
826: AND eiai.data_level_id = eia.data_level_id
827: );
828: UPDATE ego_item_associations_intf eiai
829: SET process_flag = G_REC_PRIMARY_NOT_ACTIVE
830: WHERE eiai.batch_id = p_batch_id
831: AND eiai.process_flag = G_REC_IN_PROCESS
832: AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
834: AND ( ( eiai.primary_flag = G_PRIMARY
835: AND (( eiai.status_code <> G_ACTIVE
836: AND eiai.status_code IS NOT NULL ) -- Both attrs are from interface
837: OR EXISTS (SELECT 1 -- Primary flag is being updated and Staus inactive in prod
838: FROM ego_item_associations eia
839: WHERE eia.inventory_item_id = eiai.inventory_item_id
840: AND eia.organization_id = eiai.organization_id
841: AND eia.data_level_id = eiai.data_level_id
842: AND eia.pk1_value = eiai.pk1_value
848: )
849: OR EXISTS -- Status being updated and primary flag is set in prod
850: (
851: SELECT 1
852: FROM ego_item_associations eia
853: WHERE eia.inventory_item_id = eiai.inventory_item_id
854: AND eia.organization_id = eiai.organization_id
855: AND eia.data_level_id = eiai.data_level_id
856: AND eia.pk1_value = eiai.pk1_value
859: AND eiai.status_code <> G_ACTIVE
860: AND eia.primary_flag = G_PRIMARY
861: )
862: );
863: UPDATE ego_item_associations_intf eiai
864: SET process_flag = G_REC_PARENT_NOT_ACTIVE
865: WHERE eiai.batch_id = p_batch_id
866: AND eiai.process_flag = G_REC_IN_PROCESS
867: AND eiai.status_code = G_ACTIVE
868: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
869: AND NOT EXISTS
870: (
871: SELECT 1
872: FROM ego_item_associations eia
873: WHERE eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
874: AND eia.inventory_item_id = eiai.inventory_item_id
875: AND eia.organization_id = eiai.organization_id
876: AND eia.pk1_value = eiai.pk1_value
876: AND eia.pk1_value = eiai.pk1_value
877: AND eia.status_code = G_ACTIVE
878: UNION ALL
879: SELECT 1
880: FROM ego_item_associations_intf eiai2
881: WHERE eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
882: AND eiai2.inventory_item_id = eiai.inventory_item_id
883: AND eiai2.organization_id = eiai.organization_id
884: AND eiai2.pk1_value = eiai.pk1_value
885: AND eiai2.batch_id = p_batch_id
886: AND eiai2.process_flag = G_REC_IN_PROCESS
887: AND eiai2.status_code = G_ACTIVE
888: );
889: UPDATE ego_item_associations_intf eiai
890: SET process_flag = G_REC_PARENT_NOT_ACTIVE
891: WHERE eiai.batch_id = p_batch_id
892: AND eiai.process_flag = G_REC_IN_PROCESS
893: AND eiai.status_code = G_ACTIVE
894: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
895: AND NOT EXISTS
896: (
897: SELECT 1
898: FROM ego_item_associations eia, mtl_parameters mp
899: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
900: AND eia.inventory_item_id = eiai.inventory_item_id
901: AND eia.organization_id = mp.master_organization_id
902: AND mp.organization_id = eiai.organization_id
904: AND eia.pk2_value = eiai.pk2_value
905: AND eia.status_code = G_ACTIVE
906: UNION ALL
907: SELECT 1
908: FROM ego_item_associations_intf eiai2, mtl_parameters mp
909: WHERE eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
910: AND eiai2.inventory_item_id = eiai.inventory_item_id
911: AND eiai2.organization_id = mp.master_organization_id
912: AND mp.organization_id = eiai.organization_id
915: AND eiai2.batch_id = p_batch_id
916: AND eiai2.process_flag = G_REC_IN_PROCESS -- Means there is no validation error
917: AND eiai2.status_code = G_ACTIVE
918: );
919: UPDATE ego_item_associations_intf eiai
920: SET process_flag = G_REC_SUPPLIER_NOT_ASSIGNED
921: WHERE eiai.batch_id = p_batch_id
922: AND eiai.process_flag = G_REC_IN_PROCESS
923: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
924: AND eiai.transaction_type = G_CREATE
925: AND NOT EXISTS
926: (
927: SELECT 1
928: FROM ego_item_associations eia, mtl_parameters mp
929: WHERE eia.inventory_item_id = eiai.inventory_item_id
930: AND eia.organization_id = mp.master_organization_id
931: AND mp.organization_id = eiai.organization_id
932: AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
933: AND eia.pk1_value = eiai.pk1_value
934: AND eia.pk2_value IS NULL
935: UNION ALL
936: SELECT 1
937: FROM ego_item_associations_intf eiai1, mtl_parameters mp
938: WHERE eiai1.inventory_item_id = eiai.inventory_item_id
939: AND eiai1.organization_id = mp.master_organization_id
940: AND mp.organization_id = eiai.organization_id
941: AND eiai1.data_level_id = G_ITEM_SUPPLIER_LEVEL
944: AND eiai1.process_flag = G_REC_IN_PROCESS -- means there is not validation error
945: AND eiai1.batch_id = p_batch_id -- BUG 6322084
946: AND eiai1.transaction_type = G_CREATE -- BUG 6322084
947: );
948: UPDATE ego_item_associations_intf eiai
949: SET process_flag = G_REC_SITE_NOT_ASSIGNED
950: WHERE eiai.batch_id = p_batch_id
951: AND eiai.process_flag = G_REC_IN_PROCESS
952: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
953: AND eiai.transaction_type = G_CREATE
954: AND NOT EXISTS
955: (
956: SELECT 1
957: FROM ego_item_associations eia, mtl_parameters mp
958: WHERE eia.inventory_item_id = eiai.inventory_item_id
959: AND eia.organization_id = mp.master_organization_id
960: AND mp.organization_id = eiai.organization_id
961: AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
962: AND eia.pk1_value = eiai.pk1_value
963: AND eia.pk2_value = eiai.pk2_value
964: UNION ALL
965: SELECT 1
966: FROM ego_item_associations_intf eiai1, mtl_parameters mp
967: WHERE eiai1.inventory_item_id = eiai.inventory_item_id
968: AND eiai1.organization_id = mp.master_organization_id
969: AND mp.organization_id = eiai.organization_id
970: AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
998: IS
999: BEGIN
1000: -- Delete Item-Site-Org
1001: DELETE
1002: FROM ego_item_associations eia
1003: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1004: AND EXISTS
1005: (
1006: SELECT 1
1003: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1004: AND EXISTS
1005: (
1006: SELECT 1
1007: FROM ego_item_associations_intf eiai
1008: WHERE eiai.association_id = eia.association_id
1009: AND eiai.batch_id = p_batch_id
1010: AND eiai.process_flag = G_REC_IN_PROCESS
1011: AND eiai.transaction_type = G_DELETE
1012: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1013: );
1014: -- Delete Item-Site-Org associated with Sites
1015: DELETE
1016: FROM ego_item_associations eia
1017: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1018: AND EXISTS
1019: (
1020: SELECT 1
1017: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1018: AND EXISTS
1019: (
1020: SELECT 1
1021: FROM ego_item_associations_intf eiai, mtl_parameters mp
1022: WHERE eiai.inventory_item_id = eia.inventory_item_id
1023: AND eiai.batch_id = p_batch_id
1024: AND eiai.process_flag = G_REC_IN_PROCESS
1025: AND eiai.organization_id = mp.master_organization_id
1030: AND eiai.transaction_type = G_DELETE
1031: );
1032: -- Delete Item-Site-Org associations with sites of supplier
1033: DELETE
1034: FROM ego_item_associations eia
1035: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1036: AND EXISTS
1037: (
1038: SELECT 1
1035: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1036: AND EXISTS
1037: (
1038: SELECT 1
1039: FROM ego_item_associations_intf eiai, mtl_parameters mp
1040: WHERE eiai.inventory_item_id = eia.inventory_item_id
1041: AND eiai.batch_id = p_batch_id
1042: AND eiai.process_flag = G_REC_IN_PROCESS
1043: AND eiai.organization_id = mp.master_organization_id
1046: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1047: AND eiai.transaction_type = G_DELETE
1048: );
1049: DELETE
1050: FROM ego_item_associations eia
1051: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1052: AND EXISTS
1053: (
1054: SELECT 1
1051: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1052: AND EXISTS
1053: (
1054: SELECT 1
1055: FROM ego_item_associations_intf eiai, mtl_parameters mp
1056: WHERE eiai.association_id = eia.association_id
1057: AND eiai.batch_id = p_batch_id
1058: AND eiai.process_flag = G_REC_IN_PROCESS
1059: AND eiai.organization_id = mp.master_organization_id
1062: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1063: AND eiai.transaction_type = G_DELETE
1064: );
1065: DELETE
1066: FROM ego_item_associations eia
1067: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1068: AND EXISTS
1069: (
1070: SELECT 1
1067: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1068: AND EXISTS
1069: (
1070: SELECT 1
1071: FROM ego_item_associations_intf eiai, mtl_parameters mp
1072: WHERE eiai.inventory_item_id = eia.inventory_item_id
1073: AND eiai.batch_id = p_batch_id
1074: AND eiai.process_flag = G_REC_IN_PROCESS
1075: AND eiai.organization_id = mp.master_organization_id
1078: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1079: AND eiai.transaction_type = G_DELETE
1080: );
1081: DELETE
1082: FROM ego_item_associations eia
1083: WHERE EXISTS
1084: (
1085: SELECT 1
1086: FROM ego_item_associations_intf eiai
1082: FROM ego_item_associations eia
1083: WHERE EXISTS
1084: (
1085: SELECT 1
1086: FROM ego_item_associations_intf eiai
1087: WHERE eiai.association_id = eia.association_id
1088: AND eiai.batch_id = p_batch_id
1089: AND eiai.process_flag = G_REC_IN_PROCESS
1090: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1108: PROCEDURE perform_create (p_batch_id IN NUMBER )
1109: IS
1110: BEGIN
1111: INSERT INTO
1112: ego_item_associations
1113: (
1114: association_id,
1115: organization_id,
1116: inventory_item_id,
1128: program_application_id,
1129: program_id,
1130: program_update_date
1131: )
1132: SELECT ego_item_associations_s.NEXTVAL,
1133: eiai.organization_id,
1134: eiai.inventory_item_id,
1135: eiai.pk1_value,
1136: CASE
1155: G_REQUEST_ID,
1156: G_PROG_APPID,
1157: G_PROG_ID,
1158: G_SYSDATE
1159: FROM ego_item_associations_intf eiai
1160: WHERE eiai.batch_id = p_batch_id
1161: AND eiai.process_flag = G_REC_IN_PROCESS
1162: AND eiai.transaction_type = G_CREATE
1163: AND NOT EXISTS
1162: AND eiai.transaction_type = G_CREATE
1163: AND NOT EXISTS
1164: (
1165: SELECT 1
1166: FROM ego_item_associations eia1
1167: WHERE eia1.inventory_item_id = eiai.inventory_item_id
1168: AND eia1.organization_id = eiai.organization_id
1169: AND eia1.data_level_id = eiai.data_level_id
1170: AND eia1.pk1_value = eiai.pk1_value
1188: */
1189: PROCEDURE perform_update ( p_batch_id IN NUMBER )
1190: IS
1191: BEGIN
1192: UPDATE ego_item_associations eia
1193: SET (primary_flag, status_code, last_updated_by, last_update_date, last_update_login, request_id) =
1194: ( SELECT NVL(eiai.primary_flag, eia.primary_flag)
1195: ,NVL(eiai.status_code, eia.status_code)
1196: ,G_USER_ID
1196: ,G_USER_ID
1197: ,G_SYSDATE
1198: ,G_LOGIN_ID
1199: ,G_REQUEST_ID
1200: FROM ego_item_associations_intf eiai
1201: WHERE eiai.association_id = eia.association_id
1202: AND eiai.batch_id = p_batch_id
1203: AND eiai.process_flag = G_REC_IN_PROCESS
1204: AND eiai.transaction_type = G_UPDATE
1206: )
1207: WHERE EXISTS
1208: (
1209: SELECT 1
1210: FROM ego_item_associations_intf eiai
1211: WHERE eiai.batch_id = p_batch_id
1212: AND eiai.process_flag = G_REC_IN_PROCESS
1213: AND eiai.association_id = eia.association_id
1214: AND eiai.transaction_type = G_UPDATE
1213: AND eiai.association_id = eia.association_id
1214: AND eiai.transaction_type = G_UPDATE
1215: );
1216: -- Update the status to child associations
1217: UPDATE ego_item_associations eia
1218: SET (status_code, primary_flag, last_updated_by, last_update_date, last_update_login, request_id) =
1219: ( SELECT eiai.status_code
1220: ,G_DEFAULT_PRIMARY_FLAG
1221: ,G_USER_ID
1221: ,G_USER_ID
1222: ,G_SYSDATE
1223: ,G_LOGIN_ID
1224: ,G_REQUEST_ID
1225: FROM ego_item_associations_intf eiai
1226: WHERE eiai.inventory_item_id = eia.inventory_item_id
1227: AND eiai.organization_id = eia.organization_id
1228: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1229: AND eiai.pk1_value = eia.pk1_value
1233: )
1234: WHERE EXISTS
1235: (
1236: SELECT 1
1237: FROM ego_item_associations_intf eiai
1238: WHERE eiai.inventory_item_id = eia.inventory_item_id
1239: AND eiai.organization_id = eia.organization_id
1240: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1241: AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1243: AND eiai.batch_id = p_batch_id
1244: AND eiai.process_flag = G_REC_IN_PROCESS
1245: AND eiai.status_code <> G_ACTIVE
1246: );
1247: UPDATE ego_item_associations eia
1248: SET (status_code, last_updated_by, last_update_date, last_update_login, request_id) =
1249: ( SELECT eiai.status_code
1250: ,G_USER_ID
1251: ,G_SYSDATE
1250: ,G_USER_ID
1251: ,G_SYSDATE
1252: ,G_LOGIN_ID
1253: ,G_REQUEST_ID
1254: FROM ego_item_associations_intf eiai, mtl_parameters mp
1255: WHERE eiai.inventory_item_id = eia.inventory_item_id
1256: AND eiai.organization_id = mp.master_organization_id
1257: AND mp.organization_id = eia.organization_id
1258: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1263: )
1264: WHERE EXISTS
1265: (
1266: SELECT 1
1267: FROM ego_item_associations_intf eiai, mtl_parameters mp
1268: WHERE eiai.inventory_item_id = eia.inventory_item_id
1269: AND eiai.organization_id = mp.master_organization_id
1270: AND eia.organization_id = mp.organization_id
1271: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1274: AND eiai.batch_id = p_batch_id
1275: AND eiai.process_flag = G_REC_IN_PROCESS
1276: AND eiai.status_code <> G_ACTIVE
1277: );
1278: UPDATE ego_item_associations eia
1279: SET (status_code, last_updated_by, last_update_date, last_update_login, request_id) =
1280: ( SELECT eiai.status_code
1281: ,G_USER_ID
1282: ,G_SYSDATE
1281: ,G_USER_ID
1282: ,G_SYSDATE
1283: ,G_LOGIN_ID
1284: ,G_REQUEST_ID
1285: FROM ego_item_associations_intf eiai, mtl_parameters mp
1286: WHERE eiai.inventory_item_id = eia.inventory_item_id
1287: AND eiai.organization_id = mp.master_organization_id
1288: AND eia.organization_id = mp.organization_id
1289: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1295: )
1296: WHERE EXISTS
1297: (
1298: SELECT 1
1299: FROM ego_item_associations_intf eiai, mtl_parameters mp
1300: WHERE eiai.inventory_item_id = eia.inventory_item_id
1301: AND eiai.organization_id = mp.master_organization_id
1302: AND mp.organization_id = eia.organization_id
1303: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1367: WHERE assa.vendor_site_id = eiai.pk2_value
1368: )
1369: END AS supplier_site_name,
1370: data_level_name
1371: FROM ego_item_associations_intf eiai
1372: WHERE eiai.batch_id = p_batch_id
1373: AND eiai.process_flag >= G_REC_MISSING_REQ_VALUE;
1374: l_err_msg VARCHAR2(2000);
1375: BEGIN
1461: l_err_rec.transaction_id
1462: , l_err_rec.organization_id
1463: , l_err_msg
1464: , 'E'
1465: , 'EGO_ITEM_ASSOCIATIONS_INTF'
1466: , 'ITEM_ASSOC'
1467: , G_SYSDATE
1468: , G_USER_ID
1469: , G_SYSDATE
1492: */
1493:
1494:
1495: END LOOP;
1496: UPDATE ego_item_associations_intf
1497: SET process_flag = G_REC_SUCCESS
1498: WHERE batch_id = p_batch_id
1499: AND process_flag = G_REC_IN_PROCESS;
1500: UPDATE ego_item_associations_intf
1496: UPDATE ego_item_associations_intf
1497: SET process_flag = G_REC_SUCCESS
1498: WHERE batch_id = p_batch_id
1499: AND process_flag = G_REC_IN_PROCESS;
1500: UPDATE ego_item_associations_intf
1501: SET process_flag = G_REC_ERROR
1502: WHERE batch_id = p_batch_id
1503: AND process_flag >= G_REC_UNEXPECTED_ERROR;
1504: END insert_errors;
1513: -- p_batch_id IN NUMBER Required
1514: -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
1515: -- x_msg_data OUT NOCOPY VARCHAR2(2000)
1516: -- Version : Initial version 1.0
1517: -- Notes : i) Inserts rows into ego_item_associations_intf for the new SKU's which
1518: -- are getting created.
1519: -- ii) Inserts rows into ego_item_associations_intf for the pack hierarchy
1520: -- if there exists a packaging hierarchy for the item association's item.
1521: -- iii) Converts the processing independent values to Ids
1515: -- x_msg_data OUT NOCOPY VARCHAR2(2000)
1516: -- Version : Initial version 1.0
1517: -- Notes : i) Inserts rows into ego_item_associations_intf for the new SKU's which
1518: -- are getting created.
1519: -- ii) Inserts rows into ego_item_associations_intf for the pack hierarchy
1520: -- if there exists a packaging hierarchy for the item association's item.
1521: -- iii) Converts the processing independent values to Ids
1522: -- a) Master Org Code and Master Org Id for ITEM_SUP and ITEM_SUP_SITE
1523: -- b) Org Code and Org Id for ITEM_SUP_SITE_ORG
1541: BEGIN
1542: SAVEPOINT pre_process_pub;
1543: x_return_status := fnd_api.G_RET_STS_SUCCESS;
1544: set_globals();
1545: write_log_message(' ego_item_associations_pub.pre_process Batch Id ' || p_batch_id);
1546: -- Set the transaction type to UPPER case..
1547: UPDATE ego_item_associations_intf
1548: SET transaction_type = UPPER(transaction_type)
1549: WHERE batch_id = p_batch_id
1543: x_return_status := fnd_api.G_RET_STS_SUCCESS;
1544: set_globals();
1545: write_log_message(' ego_item_associations_pub.pre_process Batch Id ' || p_batch_id);
1546: -- Set the transaction type to UPPER case..
1547: UPDATE ego_item_associations_intf
1548: SET transaction_type = UPPER(transaction_type)
1549: WHERE batch_id = p_batch_id
1550: AND process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH);
1551: UPDATE ego_item_associations_intf eiai
1547: UPDATE ego_item_associations_intf
1548: SET transaction_type = UPPER(transaction_type)
1549: WHERE batch_id = p_batch_id
1550: AND process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH);
1551: UPDATE ego_item_associations_intf eiai
1552: SET data_level_id = G_ITEM_SUPPLIER_LEVEL
1553: WHERE eiai.batch_id = p_batch_id
1554: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1555: AND eiai.data_level_name = G_ITEM_SUP_LEVEL_NAME
1553: WHERE eiai.batch_id = p_batch_id
1554: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1555: AND eiai.data_level_name = G_ITEM_SUP_LEVEL_NAME
1556: AND eiai.transaction_type = G_CREATE;
1557: UPDATE ego_item_associations_intf eiai
1558: SET data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1559: WHERE eiai.batch_id = p_batch_id
1560: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1561: AND eiai.data_level_name = G_ITEM_SUP_SITE_LEVEL_NAME
1559: WHERE eiai.batch_id = p_batch_id
1560: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1561: AND eiai.data_level_name = G_ITEM_SUP_SITE_LEVEL_NAME
1562: AND eiai.transaction_type = G_CREATE;
1563: UPDATE ego_item_associations_intf eiai
1564: SET data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1565: WHERE eiai.batch_id = p_batch_id
1566: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1567: AND eiai.data_level_name = G_ITEM_SUP_SITE_ORG_LEVEL_NAME
1567: AND eiai.data_level_name = G_ITEM_SUP_SITE_ORG_LEVEL_NAME
1568: AND eiai.transaction_type = G_CREATE;
1569:
1570: -- Convert the master org values. Convert Organization Code to Organization Id
1571: UPDATE ego_item_associations_intf eiai
1572: SET organization_id = ( SELECT mp.organization_id
1573: FROM mtl_parameters mp
1574: WHERE mp.organization_code = eiai.organization_code
1575: AND mp.master_organization_id = mp.organization_id)
1579: AND eiai.organization_id IS NULL
1580: AND eiai.organization_code IS NOT NULL;
1581:
1582: -- Correct the organization id to master organization for double intersections
1583: UPDATE ego_item_associations_intf eiai
1584: SET organization_id = ( SELECT mp.master_organization_id
1585: FROM mtl_parameters mp
1586: WHERE mp.organization_id = eiai.organization_id)
1587: WHERE eiai.batch_id = p_batch_id
1588: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1589: AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL );
1590:
1591: -- Convert the master org values. Convert Organization Code to Organization Id
1592: UPDATE ego_item_associations_intf eiai
1593: SET organization_id = ( SELECT mp.organization_id
1594: FROM mtl_parameters mp
1595: WHERE mp.organization_code = eiai.organization_code)
1596: WHERE eiai.batch_id = p_batch_id
1598: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1599: AND eiai.organization_id IS NULL
1600: AND eiai.organization_code IS NOT NULL;
1601: -- Convert the organization id to code in order to throw the errors for rules
1602: UPDATE ego_item_associations_intf eiai
1603: SET organization_code = ( SELECT mp.organization_code
1604: FROM mtl_parameters mp
1605: WHERE mp.organization_id = eiai.organization_id
1606: )
1608: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1609: AND eiai.organization_code IS NULL
1610: AND eiai.organization_id IS NOT NULL;
1611:
1612: UPDATE ego_item_associations_intf eiai
1613: SET pk1_value = ( SELECT vendor_id
1614: FROM ap_suppliers aas
1615: WHERE aas.segment1 = eiai.supplier_number
1616: )
1617: WHERE eiai.batch_id = p_batch_id
1618: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1619: AND eiai.pk1_value IS NULL
1620: AND eiai.supplier_number IS NOT NULL;
1621: UPDATE ego_item_associations_intf eiai
1622: SET pk1_value = ( SELECT vendor_id
1623: FROM ap_suppliers aas
1624: WHERE aas.vendor_name = eiai.supplier_name
1625: )
1627: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1628: AND eiai.pk1_value IS NULL
1629: AND eiai.supplier_name IS NOT NULL
1630: AND eiai.supplier_number IS NULL;
1631: UPDATE ego_item_associations_intf eiai
1632: SET supplier_name = ( SELECT vendor_name
1633: FROM ap_suppliers aas
1634: WHERE aas.vendor_id = eiai.pk1_value
1635: )
1636: WHERE eiai.batch_id = p_batch_id
1637: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1638: AND eiai.supplier_name IS NULL
1639: AND eiai.pk1_value IS NOT NULL;
1640: UPDATE ego_item_associations_intf eiai
1641: SET pk2_value = NVL(
1642: ( SELECT vendor_site_id
1643: FROM ap_suppliers aas, ap_supplier_sites_all asa
1644: WHERE aas.vendor_id = asa.vendor_id
1650: WHERE eiai.batch_id = p_batch_id
1651: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1652: AND eiai.pk2_value IS NULL
1653: AND eiai.supplier_site_name IS NOT NULL;
1654: UPDATE ego_item_associations_intf eiai
1655: SET supplier_site_name = ( SELECT vendor_site_code
1656: FROM ap_supplier_sites_all asa
1657: WHERE asa.vendor_site_id = eiai.pk2_value
1658: )
1660: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1661: AND eiai.supplier_site_name IS NULL
1662: AND eiai.pk2_value IS NOT NULL;
1663:
1664: UPDATE ego_item_associations_intf eiai
1665: SET inventory_item_id = ( SELECT inventory_item_id
1666: FROM mtl_system_items_b_kfv msibk
1667: WHERE msibk.organization_id = eiai.organization_id
1668: AND msibk.concatenated_segments = eiai.item_number
1671: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1672: AND eiai.inventory_item_id IS NULL
1673: AND eiai.item_number IS NOT NULL;
1674: -- If Row EXISTS in production the its UPDATE
1675: UPDATE ego_item_associations_intf eiai
1676: SET transaction_type = G_UPDATE
1677: WHERE eiai.batch_id = p_batch_id
1678: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1679: AND eiai.transaction_type = G_SYNC
1679: AND eiai.transaction_type = G_SYNC
1680: AND EXISTS
1681: (
1682: SELECT 1
1683: FROM ego_item_associations eia
1684: WHERE eia.inventory_item_id = eiai.inventory_item_id
1685: AND eia.organization_id = eiai.organization_id
1686: AND eia.data_level_id = eiai.data_level_id
1687: AND eia.pk1_value = eiai.pk1_value
1687: AND eia.pk1_value = eiai.pk1_value
1688: AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
1689: );
1690: -- Rest of the SYNC are CREATE
1691: UPDATE ego_item_associations_intf eiai
1692: SET transaction_type = G_CREATE
1693: WHERE eiai.batch_id = p_batch_id
1694: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1695: AND eiai.transaction_type = G_SYNC;
1693: WHERE eiai.batch_id = p_batch_id
1694: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1695: AND eiai.transaction_type = G_SYNC;
1696:
1697: UPDATE ego_item_associations_intf eiai
1698: SET eiai.process_flag = G_REC_INVALID_TRAN_TYPE
1699: WHERE eiai.batch_id = p_batch_id
1700: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1701: AND eiai.transaction_type NOT IN (G_CREATE, G_UPDATE, G_DELETE);
1700: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1701: AND eiai.transaction_type NOT IN (G_CREATE, G_UPDATE, G_DELETE);
1702:
1703: -- Bug 6438461. Default the Status and Primary Flag if it is null.
1704: UPDATE ego_item_associations_intf eiai
1705: SET eiai.status_code = G_DEFAULT_STATUS_CODE
1706: WHERE eiai.batch_id = p_batch_id
1707: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1708: AND eiai.status_code IS NULL
1707: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1708: AND eiai.status_code IS NULL
1709: AND eiai.transaction_type = G_CREATE;
1710:
1711: UPDATE ego_item_associations_intf eiai
1712: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
1713: WHERE eiai.batch_id = p_batch_id
1714: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1715: AND eiai.primary_flag IS NULL
1718: FND_MSG_PUB.Count_And_Get
1719: ( p_count => x_msg_count,
1720: p_data => x_msg_data
1721: );
1722: write_log_message(' ego_item_associations_pub.pre_process Msg Count ' || x_msg_count);
1723: write_log_message(' ego_item_associations_pub.pre_process Msg Data ' || x_msg_data);
1724: write_log_message(' ego_item_associations_pub.pre_process Return Status ' || x_return_status);
1725: EXCEPTION
1726: WHEN fnd_api.G_EXC_ERROR THEN
1719: ( p_count => x_msg_count,
1720: p_data => x_msg_data
1721: );
1722: write_log_message(' ego_item_associations_pub.pre_process Msg Count ' || x_msg_count);
1723: write_log_message(' ego_item_associations_pub.pre_process Msg Data ' || x_msg_data);
1724: write_log_message(' ego_item_associations_pub.pre_process Return Status ' || x_return_status);
1725: EXCEPTION
1726: WHEN fnd_api.G_EXC_ERROR THEN
1727: ROLLBACK TO pre_process_pub;
1720: p_data => x_msg_data
1721: );
1722: write_log_message(' ego_item_associations_pub.pre_process Msg Count ' || x_msg_count);
1723: write_log_message(' ego_item_associations_pub.pre_process Msg Data ' || x_msg_data);
1724: write_log_message(' ego_item_associations_pub.pre_process Return Status ' || x_return_status);
1725: EXCEPTION
1726: WHEN fnd_api.G_EXC_ERROR THEN
1727: ROLLBACK TO pre_process_pub;
1728: x_return_status := fnd_api.G_RET_STS_ERROR ;
1729: FND_MSG_PUB.Count_And_Get
1730: ( p_count => x_msg_count ,
1731: p_data => x_msg_data
1732: );
1733: write_log_message(' ego_item_associations_pub.pre_process Error Msg Count ' || x_msg_count);
1734: write_log_message(' ego_item_associations_pub.pre_process Error Msg Data ' || x_msg_data);
1735: write_log_message(' ego_item_associations_pub.pre_process Error Return Status ' || x_return_status);
1736: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1737: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1730: ( p_count => x_msg_count ,
1731: p_data => x_msg_data
1732: );
1733: write_log_message(' ego_item_associations_pub.pre_process Error Msg Count ' || x_msg_count);
1734: write_log_message(' ego_item_associations_pub.pre_process Error Msg Data ' || x_msg_data);
1735: write_log_message(' ego_item_associations_pub.pre_process Error Return Status ' || x_return_status);
1736: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1737: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1738: ROLLBACK TO pre_process_pub;
1731: p_data => x_msg_data
1732: );
1733: write_log_message(' ego_item_associations_pub.pre_process Error Msg Count ' || x_msg_count);
1734: write_log_message(' ego_item_associations_pub.pre_process Error Msg Data ' || x_msg_data);
1735: write_log_message(' ego_item_associations_pub.pre_process Error Return Status ' || x_return_status);
1736: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1737: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1738: ROLLBACK TO pre_process_pub;
1739: UPDATE ego_item_associations_intf
1735: write_log_message(' ego_item_associations_pub.pre_process Error Return Status ' || x_return_status);
1736: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1737: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1738: ROLLBACK TO pre_process_pub;
1739: UPDATE ego_item_associations_intf
1740: SET process_flag = G_REC_UNEXPECTED_ERROR
1741: WHERE batch_id = p_batch_id
1742: AND process_flag = G_REC_TO_BE_PROCESSED;
1743: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1744: FND_MSG_PUB.Count_And_Get
1745: ( p_count => x_msg_count ,
1746: p_data => x_msg_data
1747: );
1748: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Count ' || x_msg_count);
1749: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Data ' || x_msg_data);
1750: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Return Status ' || x_return_status);
1751: WHEN OTHERS THEN
1752: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1745: ( p_count => x_msg_count ,
1746: p_data => x_msg_data
1747: );
1748: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Count ' || x_msg_count);
1749: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Data ' || x_msg_data);
1750: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Return Status ' || x_return_status);
1751: WHEN OTHERS THEN
1752: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1753: ROLLBACK TO pre_process_pub;
1746: p_data => x_msg_data
1747: );
1748: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Count ' || x_msg_count);
1749: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Data ' || x_msg_data);
1750: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Return Status ' || x_return_status);
1751: WHEN OTHERS THEN
1752: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1753: ROLLBACK TO pre_process_pub;
1754: UPDATE ego_item_associations_intf
1750: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Return Status ' || x_return_status);
1751: WHEN OTHERS THEN
1752: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1753: ROLLBACK TO pre_process_pub;
1754: UPDATE ego_item_associations_intf
1755: SET process_flag = G_REC_UNEXPECTED_ERROR
1756: WHERE batch_id = p_batch_id
1757: AND process_flag = G_REC_TO_BE_PROCESSED;
1758: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1767: FND_MSG_PUB.Count_And_Get
1768: ( p_count => x_msg_count,
1769: p_data => x_msg_data
1770: );
1771: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Count ' || x_msg_count);
1772: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Data ' || x_msg_data);
1773: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Return Status ' || x_return_status);
1774: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS SQLERRM ' || SQLERRM);
1775: END pre_process;
1768: ( p_count => x_msg_count,
1769: p_data => x_msg_data
1770: );
1771: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Count ' || x_msg_count);
1772: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Data ' || x_msg_data);
1773: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Return Status ' || x_return_status);
1774: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS SQLERRM ' || SQLERRM);
1775: END pre_process;
1776:
1769: p_data => x_msg_data
1770: );
1771: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Count ' || x_msg_count);
1772: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Data ' || x_msg_data);
1773: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Return Status ' || x_return_status);
1774: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS SQLERRM ' || SQLERRM);
1775: END pre_process;
1776:
1777: -- Start of comments
1770: );
1771: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Count ' || x_msg_count);
1772: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Data ' || x_msg_data);
1773: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Return Status ' || x_return_status);
1774: write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS SQLERRM ' || SQLERRM);
1775: END pre_process;
1776:
1777: -- Start of comments
1778: -- API name : import_item_associations
1778: -- API name : import_item_associations
1779: -- Type : Public.
1780: -- Function : Imports the item associations into the systems.
1781: -- Pre-reqs : i) pre_process should have been called.
1782: -- ii) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF.
1783: -- iii) Errors will be grouped based on concurrent program's request id or batch_id.
1784: -- Query the errors using batch_id for non-concurrent program flows and create a batch or use
1785: -- unique batch id in order to group the errors properly.
1786: -- Parameters :
1830: */
1831: FND_MSG_PUB.initialize;
1832: -- Initialize API return status to success
1833: x_return_status := fnd_api.G_RET_STS_SUCCESS;
1834: write_log_message(' ego_item_associations_pub.import_item_associations Batch Id ' || x_batch_id);
1835: -- API body
1836:
1837: -- Set records status as in process
1838: initialize(x_batch_id);
1856: FND_MSG_PUB.Count_And_Get
1857: ( p_count => x_msg_count,
1858: p_data => x_msg_data
1859: );
1860: write_log_message(' ego_item_associations_pub.import_item_associations Msg Count ' || x_msg_count);
1861: write_log_message(' ego_item_associations_pub.import_item_associations Msg Data ' || x_msg_data);
1862: write_log_message(' ego_item_associations_pub.import_item_associations Return Status ' || x_return_status);
1863: EXCEPTION
1864: WHEN fnd_api.G_EXC_ERROR THEN
1857: ( p_count => x_msg_count,
1858: p_data => x_msg_data
1859: );
1860: write_log_message(' ego_item_associations_pub.import_item_associations Msg Count ' || x_msg_count);
1861: write_log_message(' ego_item_associations_pub.import_item_associations Msg Data ' || x_msg_data);
1862: write_log_message(' ego_item_associations_pub.import_item_associations Return Status ' || x_return_status);
1863: EXCEPTION
1864: WHEN fnd_api.G_EXC_ERROR THEN
1865: ROLLBACK TO import_item_associations_pub;
1858: p_data => x_msg_data
1859: );
1860: write_log_message(' ego_item_associations_pub.import_item_associations Msg Count ' || x_msg_count);
1861: write_log_message(' ego_item_associations_pub.import_item_associations Msg Data ' || x_msg_data);
1862: write_log_message(' ego_item_associations_pub.import_item_associations Return Status ' || x_return_status);
1863: EXCEPTION
1864: WHEN fnd_api.G_EXC_ERROR THEN
1865: ROLLBACK TO import_item_associations_pub;
1866: x_return_status := fnd_api.G_RET_STS_ERROR ;
1867: FND_MSG_PUB.Count_And_Get
1868: ( p_count => x_msg_count ,
1869: p_data => x_msg_data
1870: );
1871: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Count ' || x_msg_count);
1872: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Data ' || x_msg_data);
1873: write_log_message(' ego_item_associations_pub.import_item_associations Error Return Status ' || x_return_status);
1874: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1875: ROLLBACK TO import_item_associations_pub;
1868: ( p_count => x_msg_count ,
1869: p_data => x_msg_data
1870: );
1871: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Count ' || x_msg_count);
1872: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Data ' || x_msg_data);
1873: write_log_message(' ego_item_associations_pub.import_item_associations Error Return Status ' || x_return_status);
1874: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1875: ROLLBACK TO import_item_associations_pub;
1876: UPDATE ego_item_associations_intf
1869: p_data => x_msg_data
1870: );
1871: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Count ' || x_msg_count);
1872: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Data ' || x_msg_data);
1873: write_log_message(' ego_item_associations_pub.import_item_associations Error Return Status ' || x_return_status);
1874: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1875: ROLLBACK TO import_item_associations_pub;
1876: UPDATE ego_item_associations_intf
1877: SET process_flag = G_REC_UNEXPECTED_ERROR
1872: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Data ' || x_msg_data);
1873: write_log_message(' ego_item_associations_pub.import_item_associations Error Return Status ' || x_return_status);
1874: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1875: ROLLBACK TO import_item_associations_pub;
1876: UPDATE ego_item_associations_intf
1877: SET process_flag = G_REC_UNEXPECTED_ERROR
1878: WHERE batch_id = x_batch_id
1879: AND process_flag = G_REC_TO_BE_PROCESSED;
1880: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1881: FND_MSG_PUB.Count_And_Get
1882: ( p_count => x_msg_count ,
1883: p_data => x_msg_data
1884: );
1885: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Count ' || x_msg_count);
1886: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Data ' || x_msg_data);
1887: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Return Status ' || x_return_status);
1888: WHEN OTHERS THEN
1889: ROLLBACK TO import_item_associations_pub;
1882: ( p_count => x_msg_count ,
1883: p_data => x_msg_data
1884: );
1885: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Count ' || x_msg_count);
1886: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Data ' || x_msg_data);
1887: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Return Status ' || x_return_status);
1888: WHEN OTHERS THEN
1889: ROLLBACK TO import_item_associations_pub;
1890: UPDATE ego_item_associations_intf
1883: p_data => x_msg_data
1884: );
1885: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Count ' || x_msg_count);
1886: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Data ' || x_msg_data);
1887: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Return Status ' || x_return_status);
1888: WHEN OTHERS THEN
1889: ROLLBACK TO import_item_associations_pub;
1890: UPDATE ego_item_associations_intf
1891: SET process_flag = G_REC_UNEXPECTED_ERROR
1886: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Data ' || x_msg_data);
1887: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Return Status ' || x_return_status);
1888: WHEN OTHERS THEN
1889: ROLLBACK TO import_item_associations_pub;
1890: UPDATE ego_item_associations_intf
1891: SET process_flag = G_REC_UNEXPECTED_ERROR
1892: WHERE batch_id = x_batch_id
1893: AND process_flag = G_REC_TO_BE_PROCESSED;
1894: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1903: FND_MSG_PUB.Count_And_Get
1904: ( p_count => x_msg_count,
1905: p_data => x_msg_data
1906: );
1907: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS SQLERRM ' || SQLERRM);
1908: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Count ' || x_msg_count);
1909: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msggggg Data ' || x_msg_data);
1910: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Data ' || x_msg_data);
1911: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Return Status ' || x_return_status);
1904: ( p_count => x_msg_count,
1905: p_data => x_msg_data
1906: );
1907: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS SQLERRM ' || SQLERRM);
1908: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Count ' || x_msg_count);
1909: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msggggg Data ' || x_msg_data);
1910: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Data ' || x_msg_data);
1911: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Return Status ' || x_return_status);
1912: END import_item_associations;
1905: p_data => x_msg_data
1906: );
1907: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS SQLERRM ' || SQLERRM);
1908: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Count ' || x_msg_count);
1909: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msggggg Data ' || x_msg_data);
1910: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Data ' || x_msg_data);
1911: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Return Status ' || x_return_status);
1912: END import_item_associations;
1913:
1906: );
1907: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS SQLERRM ' || SQLERRM);
1908: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Count ' || x_msg_count);
1909: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msggggg Data ' || x_msg_data);
1910: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Data ' || x_msg_data);
1911: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Return Status ' || x_return_status);
1912: END import_item_associations;
1913:
1914: -- Start of comments
1907: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS SQLERRM ' || SQLERRM);
1908: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Count ' || x_msg_count);
1909: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msggggg Data ' || x_msg_data);
1910: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Data ' || x_msg_data);
1911: write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Return Status ' || x_return_status);
1912: END import_item_associations;
1913:
1914: -- Start of comments
1915: -- API name : import_item_associations
1915: -- API name : import_item_associations
1916: -- Type : private.
1917: -- Function : Imports the item associations in the excel import flow.
1918: -- Pre-reqs :
1919: -- i) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF if the data is not from temp tables.
1920: -- ii) Errors will be grouped based on concurrent program's request id.
1921: -- Parameters :
1922: -- IN : p_api_version IN NUMBER Required
1923: -- IN OUT : x_batch_id IN OUT NOCOPY Optional
2019: ELSIF ( p_data_level_names(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2020: l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2021: END IF;
2022: write_log_message(' l_data_level_id ' || l_data_level_id);
2023: INSERT INTO ego_item_associations_intf
2024: (
2025: BATCH_ID
2026: ,ITEM_NUMBER
2027: ,INVENTORY_ITEM_ID
2054: ,msii.source_system_reference
2055: ,msii.source_system_id
2056: ,msii.bundle_id
2057: ,G_REQUEST_ID
2058: FROM ego_item_associations eia
2059: ,mtl_system_items_interface msii
2060: ,mtl_parameters mp
2061: WHERE eia.inventory_item_id = p_src_item_id
2062: -- AND msii.organization_id = mp.organization_id Copy_Item_Id will be populated only for master org items
2066: AND eia.data_level_id = l_data_level_id
2067: AND msii.process_flag = G_REC_TO_BE_PROCESSED
2068: AND eia.organization_id = msii.organization_id
2069: AND msii.organization_id = mp.organization_id;
2070: write_log_message(' ego_item_associations_pub.copy_associations_to_items after insert ');
2071: END LOOP;
2072: FND_MSG_PUB.Count_And_Get
2073: ( p_count => x_msg_count,
2074: p_data => x_msg_data
2072: FND_MSG_PUB.Count_And_Get
2073: ( p_count => x_msg_count,
2074: p_data => x_msg_data
2075: );
2076: write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Count ' || x_msg_count);
2077: write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Data ' || x_msg_data);
2078: write_log_message(' ego_item_associations_pub.copy_associations_to_items Return Status ' || x_return_status);
2079: EXCEPTION
2080: WHEN fnd_api.G_EXC_ERROR THEN
2073: ( p_count => x_msg_count,
2074: p_data => x_msg_data
2075: );
2076: write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Count ' || x_msg_count);
2077: write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Data ' || x_msg_data);
2078: write_log_message(' ego_item_associations_pub.copy_associations_to_items Return Status ' || x_return_status);
2079: EXCEPTION
2080: WHEN fnd_api.G_EXC_ERROR THEN
2081: ROLLBACK TO copy_associations_to_items_pub;
2074: p_data => x_msg_data
2075: );
2076: write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Count ' || x_msg_count);
2077: write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Data ' || x_msg_data);
2078: write_log_message(' ego_item_associations_pub.copy_associations_to_items Return Status ' || x_return_status);
2079: EXCEPTION
2080: WHEN fnd_api.G_EXC_ERROR THEN
2081: ROLLBACK TO copy_associations_to_items_pub;
2082: x_return_status := fnd_api.G_RET_STS_ERROR ;
2083: FND_MSG_PUB.Count_And_Get
2084: ( p_count => x_msg_count ,
2085: p_data => x_msg_data
2086: );
2087: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Count ' || x_msg_count);
2088: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Data ' || x_msg_data);
2089: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Return Status ' || x_return_status);
2090: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2091: ROLLBACK TO copy_associations_to_items_pub;
2084: ( p_count => x_msg_count ,
2085: p_data => x_msg_data
2086: );
2087: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Count ' || x_msg_count);
2088: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Data ' || x_msg_data);
2089: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Return Status ' || x_return_status);
2090: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2091: ROLLBACK TO copy_associations_to_items_pub;
2092: UPDATE ego_item_associations_intf
2085: p_data => x_msg_data
2086: );
2087: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Count ' || x_msg_count);
2088: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Data ' || x_msg_data);
2089: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Return Status ' || x_return_status);
2090: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2091: ROLLBACK TO copy_associations_to_items_pub;
2092: UPDATE ego_item_associations_intf
2093: SET process_flag = G_REC_UNEXPECTED_ERROR
2088: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Data ' || x_msg_data);
2089: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Return Status ' || x_return_status);
2090: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2091: ROLLBACK TO copy_associations_to_items_pub;
2092: UPDATE ego_item_associations_intf
2093: SET process_flag = G_REC_UNEXPECTED_ERROR
2094: WHERE batch_id = p_batch_id
2095: AND process_flag = G_REC_TO_BE_PROCESSED;
2096: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2097: FND_MSG_PUB.Count_And_Get
2098: ( p_count => x_msg_count ,
2099: p_data => x_msg_data
2100: );
2101: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Count ' || x_msg_count);
2102: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Data ' || x_msg_data);
2103: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Return Status ' || x_return_status);
2104: WHEN OTHERS THEN
2105: ROLLBACK TO copy_associations_to_items_pub;
2098: ( p_count => x_msg_count ,
2099: p_data => x_msg_data
2100: );
2101: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Count ' || x_msg_count);
2102: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Data ' || x_msg_data);
2103: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Return Status ' || x_return_status);
2104: WHEN OTHERS THEN
2105: ROLLBACK TO copy_associations_to_items_pub;
2106: UPDATE ego_item_associations_intf
2099: p_data => x_msg_data
2100: );
2101: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Count ' || x_msg_count);
2102: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Data ' || x_msg_data);
2103: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Return Status ' || x_return_status);
2104: WHEN OTHERS THEN
2105: ROLLBACK TO copy_associations_to_items_pub;
2106: UPDATE ego_item_associations_intf
2107: SET process_flag = G_REC_UNEXPECTED_ERROR
2102: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Data ' || x_msg_data);
2103: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Return Status ' || x_return_status);
2104: WHEN OTHERS THEN
2105: ROLLBACK TO copy_associations_to_items_pub;
2106: UPDATE ego_item_associations_intf
2107: SET process_flag = G_REC_UNEXPECTED_ERROR
2108: WHERE batch_id = p_batch_id
2109: AND process_flag = G_REC_TO_BE_PROCESSED;
2110: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2119: FND_MSG_PUB.Count_And_Get
2120: ( p_count => x_msg_count,
2121: p_data => x_msg_data
2122: );
2123: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Count ' || x_msg_count);
2124: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Data ' || x_msg_data);
2125: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Return Status ' || x_return_status);
2126: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS SQLERRM ' || SQLERRM);
2127: END copy_associations_to_items;
2120: ( p_count => x_msg_count,
2121: p_data => x_msg_data
2122: );
2123: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Count ' || x_msg_count);
2124: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Data ' || x_msg_data);
2125: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Return Status ' || x_return_status);
2126: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS SQLERRM ' || SQLERRM);
2127: END copy_associations_to_items;
2128:
2121: p_data => x_msg_data
2122: );
2123: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Count ' || x_msg_count);
2124: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Data ' || x_msg_data);
2125: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Return Status ' || x_return_status);
2126: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS SQLERRM ' || SQLERRM);
2127: END copy_associations_to_items;
2128:
2129: -- Start of comments
2122: );
2123: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Count ' || x_msg_count);
2124: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Data ' || x_msg_data);
2125: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Return Status ' || x_return_status);
2126: write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS SQLERRM ' || SQLERRM);
2127: END copy_associations_to_items;
2128:
2129: -- Start of comments
2130: -- API name : copy_from_style_to_SKUs
2172: ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2173: l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2174: l_default_option_code := G_ASSIGN_STYLE_SS_ORG;
2175: IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2176: INSERT INTO ego_item_associations_intf
2177: (
2178: BATCH_ID
2179: ,ORGANIZATION_ID
2180: ,ORGANIZATION_CODE
2211: ,msii.source_system_id
2212: ,msii.bundle_id
2213: ,G_REQUEST_ID
2214: ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2215: FROM ego_item_associations eia
2216: ,mtl_system_items_interface msii
2217: ,mtl_parameters mp
2218: ,mtl_system_items_interface msii2
2219: WHERE eia.inventory_item_id = msii2.style_item_id
2229: AND msii2.process_flag = G_REC_TO_BE_PROCESSED
2230: AND NOT EXISTS
2231: (
2232: SELECT 1
2233: FROM ego_item_associations_intf eiai1
2234: WHERE eiai1.inventory_item_id = msii.inventory_item_id
2235: AND eiai1.organization_id = msii.organization_id
2236: AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
2237: AND eiai1.batch_id = p_batch_id
2239: AND eiai1.pk1_value = eia.pk1_value
2240: AND NVL(eiai1.pk2_value,-1) = NVL(eia.pk2_value,-1)
2241: UNION ALL
2242: SELECT 1
2243: FROM ego_item_associations eia2
2244: WHERE eia2.inventory_item_id = msii.inventory_item_id
2245: AND eia2.organization_id = msii.organization_id
2246: AND eia2.data_level_id = eia.data_level_id
2247: AND eia2.pk1_value = eia.pk1_value
2250: END IF;
2251: END IF;
2252: --dbms_output.put_line(' Defaulting option ' || ego_common_pvt.get_option_value(l_default_option_code) );
2253: IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2254: INSERT INTO ego_item_associations_intf
2255: (
2256: BATCH_ID
2257: ,ORGANIZATION_ID
2258: ,ORGANIZATION_CODE
2289: ,msii.source_system_id
2290: ,msii.bundle_id
2291: ,G_REQUEST_ID
2292: ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2293: FROM ego_item_associations eia
2294: ,mtl_system_items_interface msii
2295: ,mtl_parameters mp
2296: WHERE eia.inventory_item_id = msii.style_item_id
2297: AND msii.organization_id = mp.organization_id
2302: AND eia.organization_id = mp.organization_id
2303: AND NOT EXISTS
2304: (
2305: SELECT 1
2306: FROM ego_item_associations_intf eiai1
2307: WHERE eiai1.inventory_item_id = msii.inventory_item_id
2308: AND eiai1.organization_id = msii.organization_id
2309: AND eiai1.data_level_id = l_data_level_id
2310: AND eiai1.batch_id = p_batch_id
2312: AND eiai1.pk1_value = eia.pk1_value
2313: AND NVL(eiai1.pk2_value,-1) = NVL(eia.pk2_value,-1)
2314: UNION ALL
2315: SELECT 1
2316: FROM ego_item_associations eia2
2317: WHERE eia2.inventory_item_id = msii.inventory_item_id
2318: AND eia2.organization_id = msii.organization_id
2319: AND eia2.data_level_id = eia.data_level_id
2320: AND eia2.pk1_value = eia.pk1_value
2320: AND eia2.pk1_value = eia.pk1_value
2321: AND NVL(eia2.pk2_value,-1) = NVL(eia.pk2_value,-1)
2322: );
2323: -- Copy the rows to existing SKUs
2324: INSERT INTO ego_item_associations_intf
2325: (
2326: BATCH_ID
2327: ,ORGANIZATION_ID
2328: ,ORGANIZATION_CODE
2367: ,NULL
2368: ,NULL
2369: ,G_REQUEST_ID
2370: ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2371: FROM ego_item_associations_intf eiai1
2372: ,mtl_system_items_b_kfv msibk
2373: ,mtl_parameters mp
2374: WHERE eiai1.inventory_item_id = msibk.style_item_id
2375: AND eiai1.organization_id = msibk.organization_id
2380: AND eiai1.process_flag = G_REC_SUCCESS
2381: AND NOT EXISTS
2382: (
2383: SELECT 1
2384: FROM ego_item_associations_intf eiai2
2385: WHERE eiai2.inventory_item_id = msibk.inventory_item_id
2386: AND eiai2.organization_id = msibk.organization_id
2387: AND eiai2.data_level_id = l_data_level_id
2388: AND eiai2.batch_id = p_batch_id
2390: AND eiai2.pk1_value = eiai1.pk1_value
2391: AND NVL(eiai2.pk2_value,-1) = NVL(eiai1.pk2_value,-1)
2392: UNION ALL
2393: SELECT 1
2394: FROM ego_item_associations eia2
2395: WHERE eia2.inventory_item_id = msibk.inventory_item_id
2396: AND eia2.organization_id = msibk.organization_id
2397: AND eia2.data_level_id = eiai1.data_level_id
2398: AND eia2.pk1_value = eiai1.pk1_value
2403: FND_MSG_PUB.Count_And_Get
2404: ( p_count => x_msg_count,
2405: p_data => x_msg_data
2406: );
2407: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Count ' || x_msg_count);
2408: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Data ' || x_msg_data);
2409: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Return Status ' || x_return_status);
2410: EXCEPTION
2411: WHEN fnd_api.G_EXC_ERROR THEN
2404: ( p_count => x_msg_count,
2405: p_data => x_msg_data
2406: );
2407: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Count ' || x_msg_count);
2408: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Data ' || x_msg_data);
2409: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Return Status ' || x_return_status);
2410: EXCEPTION
2411: WHEN fnd_api.G_EXC_ERROR THEN
2412: ROLLBACK TO copy_from_style_to_SKUs_pub;
2405: p_data => x_msg_data
2406: );
2407: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Count ' || x_msg_count);
2408: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Data ' || x_msg_data);
2409: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Return Status ' || x_return_status);
2410: EXCEPTION
2411: WHEN fnd_api.G_EXC_ERROR THEN
2412: ROLLBACK TO copy_from_style_to_SKUs_pub;
2413: x_return_status := fnd_api.G_RET_STS_ERROR ;
2414: FND_MSG_PUB.Count_And_Get
2415: ( p_count => x_msg_count ,
2416: p_data => x_msg_data
2417: );
2418: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Count ' || x_msg_count);
2419: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Data ' || x_msg_data);
2420: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Return Status ' || x_return_status);
2421: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2422: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2415: ( p_count => x_msg_count ,
2416: p_data => x_msg_data
2417: );
2418: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Count ' || x_msg_count);
2419: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Data ' || x_msg_data);
2420: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Return Status ' || x_return_status);
2421: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2422: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2423: ROLLBACK TO copy_from_style_to_SKUs_pub;
2416: p_data => x_msg_data
2417: );
2418: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Count ' || x_msg_count);
2419: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Data ' || x_msg_data);
2420: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Return Status ' || x_return_status);
2421: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2422: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2423: ROLLBACK TO copy_from_style_to_SKUs_pub;
2424: UPDATE ego_item_associations_intf
2420: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Return Status ' || x_return_status);
2421: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2422: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2423: ROLLBACK TO copy_from_style_to_SKUs_pub;
2424: UPDATE ego_item_associations_intf
2425: SET process_flag = G_REC_UNEXPECTED_ERROR
2426: WHERE batch_id = p_batch_id
2427: AND process_flag = G_REC_TO_BE_PROCESSED;
2428: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2429: FND_MSG_PUB.Count_And_Get
2430: ( p_count => x_msg_count ,
2431: p_data => x_msg_data
2432: );
2433: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Count ' || x_msg_count);
2434: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Data ' || x_msg_data);
2435: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Return Status ' || x_return_status);
2436: WHEN OTHERS THEN
2437: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2430: ( p_count => x_msg_count ,
2431: p_data => x_msg_data
2432: );
2433: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Count ' || x_msg_count);
2434: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Data ' || x_msg_data);
2435: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Return Status ' || x_return_status);
2436: WHEN OTHERS THEN
2437: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2438: ROLLBACK TO copy_from_style_to_SKUs_pub;
2431: p_data => x_msg_data
2432: );
2433: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Count ' || x_msg_count);
2434: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Data ' || x_msg_data);
2435: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Return Status ' || x_return_status);
2436: WHEN OTHERS THEN
2437: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2438: ROLLBACK TO copy_from_style_to_SKUs_pub;
2439: UPDATE ego_item_associations_intf
2435: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Return Status ' || x_return_status);
2436: WHEN OTHERS THEN
2437: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2438: ROLLBACK TO copy_from_style_to_SKUs_pub;
2439: UPDATE ego_item_associations_intf
2440: SET process_flag = G_REC_UNEXPECTED_ERROR
2441: WHERE batch_id = p_batch_id
2442: AND process_flag = G_REC_TO_BE_PROCESSED;
2443: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2452: FND_MSG_PUB.Count_And_Get
2453: ( p_count => x_msg_count,
2454: p_data => x_msg_data
2455: );
2456: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Count ' || x_msg_count);
2457: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Data ' || x_msg_data);
2458: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Return Status ' || x_return_status);
2459: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS SQLERRM ' || SQLERRM);
2460: END copy_from_style_to_SKUs;
2453: ( p_count => x_msg_count,
2454: p_data => x_msg_data
2455: );
2456: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Count ' || x_msg_count);
2457: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Data ' || x_msg_data);
2458: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Return Status ' || x_return_status);
2459: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS SQLERRM ' || SQLERRM);
2460: END copy_from_style_to_SKUs;
2461:
2454: p_data => x_msg_data
2455: );
2456: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Count ' || x_msg_count);
2457: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Data ' || x_msg_data);
2458: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Return Status ' || x_return_status);
2459: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS SQLERRM ' || SQLERRM);
2460: END copy_from_style_to_SKUs;
2461:
2462: -- Start of comments
2455: );
2456: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Count ' || x_msg_count);
2457: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Data ' || x_msg_data);
2458: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Return Status ' || x_return_status);
2459: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS SQLERRM ' || SQLERRM);
2460: END copy_from_style_to_SKUs;
2461:
2462: -- Start of comments
2463: -- API name : copy_to_packs
2498: CURSOR l_pack_item_csr(p_batch_id IN NUMBER)
2499: IS
2500: SELECT msibk.inventory_item_id
2501: ,msibk.organization_id
2502: FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk
2503: WHERE msibk.concatenated_segments = eiai.item_number
2504: AND msibk.organization_id = eiai.organization_id
2505: AND eiai.batch_id = p_batch_id
2506: AND eiai.process_flag = G_REC_TO_BE_PROCESSED
2511: WHERE bsb.assembly_item_id = msibk.inventory_item_id
2512: AND bsb.assembly_item_id = msibk.inventory_item_id
2513: AND bsb.organization_id = eiai.organization_id
2514: AND bsb.bill_sequence_id = bsb.common_bill_sequence_id
2515: AND bsb.alternate_bom_designator = ego_item_associations_pub.G_PACK_STR_NAME
2516: );
2517: BEGIN
2518: x_return_status := fnd_api.G_RET_STS_SUCCESS;
2519: -- Set the Global Variables
2564: l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2565: l_default_option_code := G_ASSIGN_PACK_SS_ORG;
2566: END IF;
2567: IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2568: INSERT INTO ego_item_associations_intf
2569: (
2570: BATCH_ID
2571: ,SOURCE_SYSTEM_REFERENCE
2572: ,ITEM_NUMBER
2607: ,G_REC_TO_BE_PROCESSED
2608: ,mtl_system_items_interface_s.NEXTVAL
2609: ,G_REQUEST_ID
2610: ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2611: FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk,
2612: mtl_parameters mp
2613: WHERE eiai.inventory_item_id = l_pack_rec.inventory_item_id
2614: AND eiai.organization_id = mp.organization_id
2615: --AND mp.master_organization_id = mp.organization_id
2621: AND msibk.organization_id = mp.organization_id
2622: AND NOT EXISTS
2623: (
2624: SELECT 1
2625: FROM ego_item_associations_intf eiai1
2626: WHERE eiai1.inventory_item_id = msibk.inventory_item_id
2627: AND eiai1.organization_id = msibk.organization_id
2628: AND eiai1.data_level_id = l_data_level_id
2629: AND eiai1.batch_id = p_batch_id
2631: AND eiai1.pk1_value = eiai.pk1_value
2632: AND NVL(eiai1.pk2_value,-1) = NVL(eiai.pk2_value,-1)
2633: UNION ALL
2634: SELECT 1
2635: FROM ego_item_associations eia2
2636: WHERE eia2.inventory_item_id = msibk.inventory_item_id
2637: AND eia2.organization_id = msibk.organization_id
2638: AND eia2.data_level_id = l_data_level_id
2639: AND eia2.pk1_value = eiai.pk1_value
2647: FND_MSG_PUB.Count_And_Get
2648: ( p_count => x_msg_count,
2649: p_data => x_msg_data
2650: );
2651: write_log_message(' ego_item_associations_pub.copy_to_packs Msg Count ' || x_msg_count);
2652: write_log_message(' ego_item_associations_pub.copy_to_packs Msg Data ' || x_msg_data);
2653: write_log_message(' ego_item_associations_pub.copy_to_packs Return Status ' || x_return_status);
2654: EXCEPTION
2655: WHEN fnd_api.G_EXC_ERROR THEN
2648: ( p_count => x_msg_count,
2649: p_data => x_msg_data
2650: );
2651: write_log_message(' ego_item_associations_pub.copy_to_packs Msg Count ' || x_msg_count);
2652: write_log_message(' ego_item_associations_pub.copy_to_packs Msg Data ' || x_msg_data);
2653: write_log_message(' ego_item_associations_pub.copy_to_packs Return Status ' || x_return_status);
2654: EXCEPTION
2655: WHEN fnd_api.G_EXC_ERROR THEN
2656: ROLLBACK TO copy_to_packs_pub;
2649: p_data => x_msg_data
2650: );
2651: write_log_message(' ego_item_associations_pub.copy_to_packs Msg Count ' || x_msg_count);
2652: write_log_message(' ego_item_associations_pub.copy_to_packs Msg Data ' || x_msg_data);
2653: write_log_message(' ego_item_associations_pub.copy_to_packs Return Status ' || x_return_status);
2654: EXCEPTION
2655: WHEN fnd_api.G_EXC_ERROR THEN
2656: ROLLBACK TO copy_to_packs_pub;
2657: x_return_status := fnd_api.G_RET_STS_ERROR ;
2658: FND_MSG_PUB.Count_And_Get
2659: ( p_count => x_msg_count ,
2660: p_data => x_msg_data
2661: );
2662: write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Count ' || x_msg_count);
2663: write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Data ' || x_msg_data);
2664: write_log_message(' ego_item_associations_pub.copy_to_packs Error Return Status ' || x_return_status);
2665: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2666: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2659: ( p_count => x_msg_count ,
2660: p_data => x_msg_data
2661: );
2662: write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Count ' || x_msg_count);
2663: write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Data ' || x_msg_data);
2664: write_log_message(' ego_item_associations_pub.copy_to_packs Error Return Status ' || x_return_status);
2665: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2666: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2667: ROLLBACK TO copy_to_packs_pub;
2660: p_data => x_msg_data
2661: );
2662: write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Count ' || x_msg_count);
2663: write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Data ' || x_msg_data);
2664: write_log_message(' ego_item_associations_pub.copy_to_packs Error Return Status ' || x_return_status);
2665: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2666: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2667: ROLLBACK TO copy_to_packs_pub;
2668: UPDATE ego_item_associations_intf
2664: write_log_message(' ego_item_associations_pub.copy_to_packs Error Return Status ' || x_return_status);
2665: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2666: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2667: ROLLBACK TO copy_to_packs_pub;
2668: UPDATE ego_item_associations_intf
2669: SET process_flag = G_REC_UNEXPECTED_ERROR
2670: WHERE batch_id = p_batch_id
2671: AND process_flag = G_REC_TO_BE_PROCESSED;
2672: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2673: FND_MSG_PUB.Count_And_Get
2674: ( p_count => x_msg_count ,
2675: p_data => x_msg_data
2676: );
2677: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Count ' || x_msg_count);
2678: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Data ' || x_msg_data);
2679: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Return Status ' || x_return_status);
2680: WHEN OTHERS THEN
2681: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2674: ( p_count => x_msg_count ,
2675: p_data => x_msg_data
2676: );
2677: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Count ' || x_msg_count);
2678: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Data ' || x_msg_data);
2679: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Return Status ' || x_return_status);
2680: WHEN OTHERS THEN
2681: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2682: ROLLBACK TO copy_to_packs_pub;
2675: p_data => x_msg_data
2676: );
2677: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Count ' || x_msg_count);
2678: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Data ' || x_msg_data);
2679: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Return Status ' || x_return_status);
2680: WHEN OTHERS THEN
2681: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2682: ROLLBACK TO copy_to_packs_pub;
2683: UPDATE ego_item_associations_intf
2679: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Return Status ' || x_return_status);
2680: WHEN OTHERS THEN
2681: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2682: ROLLBACK TO copy_to_packs_pub;
2683: UPDATE ego_item_associations_intf
2684: SET process_flag = G_REC_UNEXPECTED_ERROR
2685: WHERE batch_id = p_batch_id
2686: AND process_flag = G_REC_TO_BE_PROCESSED;
2687: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2696: FND_MSG_PUB.Count_And_Get
2697: ( p_count => x_msg_count,
2698: p_data => x_msg_data
2699: );
2700: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Count ' || x_msg_count);
2701: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Data ' || x_msg_data);
2702: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Return Status ' || x_return_status);
2703: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS SQLERRM ' || SQLERRM);
2704: END copy_to_packs;
2697: ( p_count => x_msg_count,
2698: p_data => x_msg_data
2699: );
2700: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Count ' || x_msg_count);
2701: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Data ' || x_msg_data);
2702: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Return Status ' || x_return_status);
2703: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS SQLERRM ' || SQLERRM);
2704: END copy_to_packs;
2705:
2698: p_data => x_msg_data
2699: );
2700: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Count ' || x_msg_count);
2701: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Data ' || x_msg_data);
2702: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Return Status ' || x_return_status);
2703: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS SQLERRM ' || SQLERRM);
2704: END copy_to_packs;
2705:
2706: END ego_item_associations_pub;
2699: );
2700: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Count ' || x_msg_count);
2701: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Data ' || x_msg_data);
2702: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Return Status ' || x_return_status);
2703: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS SQLERRM ' || SQLERRM);
2704: END copy_to_packs;
2705:
2706: END ego_item_associations_pub;
2702: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Return Status ' || x_return_status);
2703: write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS SQLERRM ' || SQLERRM);
2704: END copy_to_packs;
2705:
2706: END ego_item_associations_pub;