[Home] [Help]
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';
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
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
348: WHERE aas.vendor_id = eiai.pk1_value
349: and NVL(aas.end_date_active,SYSDATE+1) > SYSDATE --bug11072046
350: )
351: );
352: UPDATE ego_item_associations_intf eiai
353: SET process_flag = G_REC_INVALID_PK2_VALUE
354: WHERE eiai.batch_id = p_batch_id
355: AND eiai.process_flag = G_REC_IN_PROCESS
356: AND ( eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
368: AND nvl(assa.inactive_date,SYSDATE + 1)>SYSDATE --BUG 11072046
369: AND assa.org_id = fnd_profile.value('ORG_ID')
370: )
371: );
372: UPDATE ego_item_associations_intf eiai
373: SET process_flag = G_REC_ASSOCIATION_NOT_EXISTS
374: WHERE eiai.batch_id = p_batch_id
375: AND eiai.process_flag = G_REC_IN_PROCESS
376: AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE )
374: WHERE eiai.batch_id = p_batch_id
375: AND eiai.process_flag = G_REC_IN_PROCESS
376: AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE )
377: AND eiai.association_id IS NULL;
378: UPDATE ego_item_associations_intf eiai
379: SET process_flag = G_REC_INVALID_ASSOC_TYPE
380: WHERE eiai.batch_id = p_batch_id
381: AND eiai.process_flag = G_REC_IN_PROCESS
382: AND NOT EXISTS
384: SELECT 1
385: FROM ego_data_level_b edlb
386: WHERE edlb.data_level_id = eiai.data_level_id
387: );
388: UPDATE ego_item_associations_intf eiai
389: SET process_flag = G_REC_INVALID_STATUS
390: WHERE eiai.batch_id = p_batch_id
391: AND eiai.process_flag = G_REC_IN_PROCESS
392: AND eiai.status_code IS NOT NULL
396: FROM fnd_lookups fl
397: WHERE fl.lookup_type = 'EGO_ASSOCIATION_STATUS'
398: AND fl.lookup_code = eiai.status_code
399: );
400: UPDATE ego_item_associations_intf eiai
401: SET process_flag = G_REC_INVALID_PRIMARY
402: WHERE eiai.batch_id = p_batch_id
403: AND eiai.process_flag = G_REC_IN_PROCESS
404: AND eiai.primary_flag IS NOT NULL
452:
453: IF x_return_status IN ('T','F') THEN
454: IF l_sec_predicate IS NOT NULL THEN
455: l_dynamic_sql :=
456: ' UPDATE ego_item_associations_intf eiai ' ||
457: ' SET process_flag = '||G_REC_NO_CREATE_ASSOC_PRIV ||
458: ' WHERE batch_id = :p_batch_id '||
459: ' AND process_flag = '||G_REC_IN_PROCESS||
460: ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
485: ,x_return_status => x_return_status );
486: IF x_return_status IN ('T','F') THEN
487: IF l_sec_predicate IS NOT NULL THEN
488: l_dynamic_sql :=
489: ' UPDATE ego_item_associations_intf eiai ' ||
490: ' SET process_flag = '||G_REC_NO_EDIT_ASSOC_PRIV ||
491: ' WHERE batch_id = :p_batch_id '||
492: ' AND process_flag = '||G_REC_IN_PROCESS||
493: ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
519: ,x_return_status => x_return_status );
520: IF x_return_status IN ('T','F') THEN
521: IF l_sec_predicate IS NOT NULL THEN
522: l_dynamic_sql :=
523: ' UPDATE ego_item_associations_intf eiai ' ||
524: ' SET process_flag = '||G_REC_NO_EDIT_ITEM_ORG_PRIV ||
525: ' WHERE batch_id = :p_batch_id '||
526: ' AND data_level_id = 43105 '||
527: ' AND process_flag = '||G_REC_IN_PROCESS||
540: APP_EXCEPTION.RAISE_EXCEPTION();
541: END IF;
542:
543: IF ego_item_associations_util.is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
544: UPDATE ego_item_associations_intf eiai
545: SET process_flag = G_REC_NO_SUPPL_ACCESS_PRIV
546: WHERE eiai.batch_id = p_batch_id
547: AND eiai.process_flag = G_REC_IN_PROCESS
548: AND NOT EXISTS
576: */
577: PROCEDURE validate_associations(p_batch_id IN NUMBER)
578: IS
579: BEGIN
580: UPDATE ego_item_associations_intf eiai
581: SET process_flag = G_REC_ORG_NO_ACCESS
582: WHERE eiai.batch_id = p_batch_id
583: AND eiai.process_flag = G_REC_IN_PROCESS
584: AND NOT EXISTS
588: WHERE oav.organization_id = eiai.organization_id
589: AND oav.responsibility_id = FND_PROFILE.Value('RESP_ID')
590: AND oav.resp_application_id = FND_PROFILE.Value('RESP_APPL_ID')
591: );
592: UPDATE ego_item_associations_intf eiai
593: SET process_flag = G_REC_ALREADY_ASSIGNED
594: WHERE eiai.batch_id = p_batch_id
595: AND eiai.process_flag = G_REC_IN_PROCESS
596: AND eiai.transaction_type = G_CREATE
605: AND ( ( eia.pk2_value IS NULL AND eiai.pk2_value IS NULL )
606: OR ( eia.pk2_value = eiai.pk2_value )
607: )
608: );
609: UPDATE ego_item_associations_intf eiai
610: SET process_flag = G_REC_ASSOC_SITE_NOT_EXISTS
611: WHERE eiai.batch_id = p_batch_id
612: AND eiai.process_flag = G_REC_IN_PROCESS
613: AND eiai.transaction_type = G_CREATE
618: WHERE assa.vendor_id = eiai.pk1_value
619: AND assa.org_id = fnd_profile.value('ORG_ID')
620: AND nvl(assa.inactive_date,SYSDATE + 1)>SYSDATE --BUG11072046
621: );
622: UPDATE ego_item_associations_intf eiai
623: SET process_flag = G_REC_ASSOC_ITEM_NOT_IN_ORG
624: WHERE eiai.batch_id = p_batch_id
625: AND eiai.process_flag = G_REC_IN_PROCESS
626: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
633: AND msibk.organization_id = eiai.organization_id
634: );
635: -- Primary has been set in Create-Create or Create-Update or Update - Update
636: /*
637: UPDATE ego_item_associations_intf eiai
638: SET process_flag = G_REC_DUPLICATE_PRIMARY
639: WHERE eiai.batch_id = p_batch_id
640: AND eiai.process_flag = G_REC_IN_PROCESS
641: AND eiai.primary_flag = 'Y'
642: AND ( eiai.transaction_type = G_CREATE OR eiai.transaction_type = G_UPDATE)
643: AND exists
644: (
645: SELECT 1
646: FROM ego_item_associations_intf eiai2
647: WHERE eiai2.batch_id = eiai.batch_id
648: AND eiai2.process_flag = eiai.process_flag
649: AND eiai2.primary_flag = eiai.primary_flag
650: AND eiai2.inventory_item_id = eiai.inventory_item_id
657: );
658: */
659: -- If more than one row have primary flag set, then unset for all other records
660: -- except the last one (mode CREATE)
661: UPDATE ego_item_associations_intf eiai
662: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
663: WHERE eiai.batch_id = p_batch_id
664: AND eiai.process_flag = G_REC_IN_PROCESS
665: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
666: AND eiai.transaction_type = G_CREATE
667: AND eiai.ROWID NOT IN
668: (
669: SELECT MAX(eiai2.ROWID)
670: FROM ego_item_associations_intf eiai2
671: WHERE eiai2.batch_id = p_batch_id
672: AND eiai2.process_flag = G_REC_IN_PROCESS
673: AND eiai2.primary_flag = G_PRIMARY
674: AND eiai2.transaction_type = G_CREATE
675: AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
676: GROUP BY eiai2.inventory_item_id, eiai2.organization_id
677: HAVING count(*) >= 1
678: );
679: UPDATE ego_item_associations_intf eiai
680: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
681: WHERE eiai.batch_id = p_batch_id
682: AND eiai.process_flag = G_REC_IN_PROCESS
683: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
684: AND eiai.transaction_type = G_CREATE
685: AND eiai.ROWID NOT IN
686: (
687: SELECT MAX(eiai2.ROWID)
688: FROM ego_item_associations_intf eiai2
689: WHERE eiai2.batch_id = p_batch_id
690: AND eiai2.process_flag = G_REC_IN_PROCESS
691: AND eiai2.primary_flag = G_PRIMARY
692: AND eiai2.transaction_type = G_CREATE
696: );
697:
698: -- If more than one row have primary flag set, then unset for all other records
699: -- except the last one (mode UPDATE)
700: UPDATE ego_item_associations_intf eiai
701: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
702: WHERE eiai.batch_id = p_batch_id
703: AND eiai.process_flag = G_REC_IN_PROCESS
704: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
705: AND eiai.transaction_type = G_UPDATE
706: AND eiai.ROWID NOT IN
707: (
708: SELECT MAX(eiai2.ROWID)
709: FROM ego_item_associations_intf eiai2
710: WHERE eiai2.batch_id = p_batch_id
711: AND eiai2.process_flag = G_REC_IN_PROCESS
712: AND eiai2.primary_flag = G_PRIMARY
713: AND eiai2.transaction_type = G_UPDATE
716: HAVING count(*) >= 1
717: )
718: AND eiai.primary_flag = G_PRIMARY; -- fix for bug#8995869
719:
720: UPDATE ego_item_associations_intf eiai
721: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
722: WHERE eiai.batch_id = p_batch_id
723: AND eiai.process_flag = G_REC_IN_PROCESS
724: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
725: AND eiai.transaction_type = G_UPDATE
726: AND eiai.ROWID NOT IN
727: (
728: SELECT MAX(eiai2.ROWID)
729: FROM ego_item_associations_intf eiai2
730: WHERE eiai2.batch_id = p_batch_id
731: AND eiai2.process_flag = G_REC_IN_PROCESS
732: AND eiai2.primary_flag = G_PRIMARY
733: AND eiai2.transaction_type = G_UPDATE
739:
740:
741: -- If the both CREATE and UPDATE has primary flag set then unset for CREATE operations
742: -- because UPDATE is the last operation to be performed
743: UPDATE ego_item_associations_intf eiai
744: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
745: WHERE eiai.batch_id = p_batch_id
746: AND eiai.process_flag = G_REC_IN_PROCESS
747: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
748: AND eiai.transaction_type = G_CREATE
749: AND EXISTS
750: (
751: SELECT 1
752: FROM ego_item_associations_intf eiai2
753: WHERE eiai2.batch_id = p_batch_id
754: AND eiai2.process_flag = G_REC_IN_PROCESS
755: AND eiai2.inventory_item_id = eiai.inventory_item_id
756: AND eiai2.organization_id = eiai.organization_id
758: AND eiai2.primary_flag = G_PRIMARY
759: AND eiai2.transaction_type = G_UPDATE
760: );
761:
762: UPDATE ego_item_associations_intf eiai
763: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
764: WHERE eiai.batch_id = p_batch_id
765: AND eiai.process_flag = G_REC_IN_PROCESS
766: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
767: AND eiai.transaction_type = G_CREATE
768: AND EXISTS
769: (
770: SELECT 1
771: FROM ego_item_associations_intf eiai2
772: WHERE eiai2.batch_id = p_batch_id
773: AND eiai2.process_flag = G_REC_IN_PROCESS
774: AND eiai2.inventory_item_id = eiai.inventory_item_id
775: AND eiai2.organization_id = eiai.organization_id
784: WHERE primary_flag = G_PRIMARY
785: AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
786: AND EXISTS
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.batch_id = p_batch_id
792: AND eiai.process_flag = G_REC_IN_PROCESS
795: AND eiai.data_level_id = eia.data_level_id
796: )
797: AND NOT EXISTS
798: ( SELECT 1
799: FROM ego_item_associations_intf eiai
800: WHERE eiai.inventory_item_id = eia.inventory_item_id
801: AND eiai.organization_id = eia.organization_id
802: AND eiai.pk1_value = eia.pk1_value
803: AND eiai.batch_id = p_batch_id
808: );
809: -- Bug 6931470: fix performance issue, using EXIST and IN to substitute UNION operation
810: -- AND EXISTS
811: -- ( SELECT 1
812: -- FROM ego_item_associations_intf eiai
813: -- WHERE eiai.inventory_item_id = eia.inventory_item_id
814: -- AND eiai.organization_id = eia.organization_id
815: -- AND eiai.pk1_value <> eia.pk1_value
816: -- AND eiai.batch_id = p_batch_id
819: -- AND eiai.transaction_type = G_UPDATE
820: -- AND eiai.data_level_id = eia.data_level_id
821: -- UNION ALL
822: -- SELECT 1
823: -- FROM ego_item_associations_intf eiai
824: -- WHERE eiai.inventory_item_id = eia.inventory_item_id
825: -- AND eiai.organization_id = eia.organization_id
826: -- AND eiai.pk1_value <> eia.pk1_value
827: -- AND eiai.batch_id = p_batch_id
835: WHERE eia.primary_flag = G_PRIMARY
836: AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
837: AND EXISTS
838: ( SELECT 1
839: FROM ego_item_associations_intf eiai
840: WHERE eiai.inventory_item_id = eia.inventory_item_id
841: AND eiai.organization_id = eia.organization_id
842: AND eiai.pk1_value = eia.pk1_value
843: AND eiai.batch_id = p_batch_id
847: AND eiai.data_level_id = eia.data_level_id
848: )
849: AND NOT EXISTS
850: ( SELECT 1
851: FROM ego_item_associations_intf eiai
852: WHERE eiai.inventory_item_id = eia.inventory_item_id
853: AND eiai.organization_id = eia.organization_id
854: AND eiai.pk1_value = eia.pk1_value
855: AND eiai.pk2_value = eia.pk2_value
861: );
862: -- Bug 6931470: fix performance issue, using EXIST and IN to substitute UNION operation
863: -- AND EXISTS
864: -- ( SELECT 1
865: -- FROM ego_item_associations_intf eiai
866: -- WHERE eiai.inventory_item_id = eia.inventory_item_id
867: -- AND eiai.organization_id = eia.organization_id
868: -- AND eiai.pk1_value = eia.pk1_value
869: -- AND eiai.pk2_value <> eia.pk2_value
873: -- AND eiai.transaction_type = G_UPDATE
874: -- AND eiai.data_level_id = eia.data_level_id
875: -- UNION ALL
876: -- SELECT 1
877: -- FROM ego_item_associations_intf eiai
878: -- WHERE eiai.inventory_item_id = eia.inventory_item_id
879: -- AND eiai.organization_id = eia.organization_id
880: -- AND eiai.pk1_value = eia.pk1_value
881: -- AND eiai.pk2_value <> eia.pk2_value
884: -- AND eiai.primary_flag = G_PRIMARY
885: -- AND eiai.transaction_type = G_CREATE
886: -- AND eiai.data_level_id = eia.data_level_id
887: -- );
888: UPDATE ego_item_associations_intf eiai
889: SET process_flag = G_REC_PRIMARY_NOT_ACTIVE
890: WHERE eiai.batch_id = p_batch_id
891: AND eiai.process_flag = G_REC_IN_PROCESS
892: AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
919: AND eiai.status_code <> G_ACTIVE
920: AND eia.primary_flag = G_PRIMARY
921: )
922: );
923: UPDATE ego_item_associations_intf eiai
924: SET process_flag = G_REC_PARENT_NOT_ACTIVE
925: WHERE eiai.batch_id = p_batch_id
926: AND eiai.process_flag = G_REC_IN_PROCESS
927: AND eiai.status_code = G_ACTIVE
936: AND eia.pk1_value = eiai.pk1_value
937: AND eia.status_code = G_ACTIVE
938: UNION ALL
939: SELECT 1
940: FROM ego_item_associations_intf eiai2
941: WHERE eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
942: AND eiai2.inventory_item_id = eiai.inventory_item_id
943: AND eiai2.organization_id = eiai.organization_id
944: AND eiai2.pk1_value = eiai.pk1_value
945: AND eiai2.batch_id = p_batch_id
946: AND eiai2.process_flag = G_REC_IN_PROCESS
947: AND eiai2.status_code = G_ACTIVE
948: );
949: UPDATE ego_item_associations_intf eiai
950: SET process_flag = G_REC_PARENT_NOT_ACTIVE
951: WHERE eiai.batch_id = p_batch_id
952: AND eiai.process_flag = G_REC_IN_PROCESS
953: AND eiai.status_code = G_ACTIVE
964: AND eia.pk2_value = eiai.pk2_value
965: AND eia.status_code = G_ACTIVE
966: UNION ALL
967: SELECT 1
968: FROM ego_item_associations_intf eiai2, mtl_parameters mp
969: WHERE eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
970: AND eiai2.inventory_item_id = eiai.inventory_item_id
971: AND eiai2.organization_id = mp.master_organization_id
972: AND mp.organization_id = eiai.organization_id
975: AND eiai2.batch_id = p_batch_id
976: AND eiai2.process_flag = G_REC_IN_PROCESS -- Means there is no validation error
977: AND eiai2.status_code = G_ACTIVE
978: );
979: UPDATE ego_item_associations_intf eiai
980: SET process_flag = G_REC_SUPPLIER_NOT_ASSIGNED
981: WHERE eiai.batch_id = p_batch_id
982: AND eiai.process_flag = G_REC_IN_PROCESS
983: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
993: AND eia.pk1_value = eiai.pk1_value
994: AND eia.pk2_value IS NULL
995: UNION ALL
996: SELECT 1
997: FROM ego_item_associations_intf eiai1, mtl_parameters mp
998: WHERE eiai1.inventory_item_id = eiai.inventory_item_id
999: AND eiai1.organization_id = mp.master_organization_id
1000: AND mp.organization_id = eiai.organization_id
1001: AND eiai1.data_level_id = G_ITEM_SUPPLIER_LEVEL
1004: AND eiai1.process_flag = G_REC_IN_PROCESS -- means there is not validation error
1005: AND eiai1.batch_id = p_batch_id -- BUG 6322084
1006: AND eiai1.transaction_type = G_CREATE -- BUG 6322084
1007: );
1008: UPDATE ego_item_associations_intf eiai
1009: SET process_flag = G_REC_SITE_NOT_ASSIGNED
1010: WHERE eiai.batch_id = p_batch_id
1011: AND eiai.process_flag = G_REC_IN_PROCESS
1012: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1022: AND eia.pk1_value = eiai.pk1_value
1023: AND eia.pk2_value = eiai.pk2_value
1024: UNION ALL
1025: SELECT 1
1026: FROM ego_item_associations_intf eiai1, mtl_parameters mp
1027: WHERE eiai1.inventory_item_id = eiai.inventory_item_id
1028: AND eiai1.organization_id = mp.master_organization_id
1029: AND mp.organization_id = eiai.organization_id
1030: AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1063: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1064: AND EXISTS
1065: (
1066: SELECT 1
1067: FROM ego_item_associations_intf eiai
1068: WHERE eiai.association_id = eia.association_id
1069: AND eiai.batch_id = p_batch_id
1070: AND eiai.process_flag = G_REC_IN_PROCESS
1071: AND eiai.transaction_type = G_DELETE
1077: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1078: AND EXISTS
1079: (
1080: SELECT 1
1081: FROM ego_item_associations_intf eiai, mtl_parameters mp
1082: WHERE eiai.inventory_item_id = eia.inventory_item_id
1083: AND eiai.batch_id = p_batch_id
1084: AND eiai.process_flag = G_REC_IN_PROCESS
1085: AND eiai.organization_id = mp.master_organization_id
1095: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1096: AND EXISTS
1097: (
1098: SELECT 1
1099: FROM ego_item_associations_intf eiai, mtl_parameters mp
1100: WHERE eiai.inventory_item_id = eia.inventory_item_id
1101: AND eiai.batch_id = p_batch_id
1102: AND eiai.process_flag = G_REC_IN_PROCESS
1103: AND eiai.organization_id = mp.master_organization_id
1111: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1112: AND EXISTS
1113: (
1114: SELECT 1
1115: FROM ego_item_associations_intf eiai, mtl_parameters mp
1116: WHERE eiai.association_id = eia.association_id
1117: AND eiai.batch_id = p_batch_id
1118: AND eiai.process_flag = G_REC_IN_PROCESS
1119: AND eiai.organization_id = mp.master_organization_id
1127: WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1128: AND EXISTS
1129: (
1130: SELECT 1
1131: FROM ego_item_associations_intf eiai, mtl_parameters mp
1132: WHERE eiai.inventory_item_id = eia.inventory_item_id
1133: AND eiai.batch_id = p_batch_id
1134: AND eiai.process_flag = G_REC_IN_PROCESS
1135: AND eiai.organization_id = mp.master_organization_id
1142: FROM ego_item_associations eia
1143: WHERE EXISTS
1144: (
1145: SELECT 1
1146: FROM ego_item_associations_intf eiai
1147: WHERE eiai.association_id = eia.association_id
1148: AND eiai.batch_id = p_batch_id
1149: AND eiai.process_flag = G_REC_IN_PROCESS
1150: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1215: G_REQUEST_ID,
1216: G_PROG_APPID,
1217: G_PROG_ID,
1218: G_SYSDATE
1219: FROM ego_item_associations_intf eiai
1220: WHERE eiai.batch_id = p_batch_id
1221: AND eiai.process_flag = G_REC_IN_PROCESS
1222: AND eiai.transaction_type = G_CREATE
1223: AND NOT EXISTS
1256: ,G_USER_ID
1257: ,G_SYSDATE
1258: ,G_LOGIN_ID
1259: ,G_REQUEST_ID
1260: FROM ego_item_associations_intf eiai
1261: WHERE eiai.association_id = eia.association_id
1262: AND eiai.batch_id = p_batch_id
1263: AND eiai.process_flag = G_REC_IN_PROCESS
1264: AND eiai.transaction_type = G_UPDATE
1266: )
1267: WHERE EXISTS
1268: (
1269: SELECT 1
1270: FROM ego_item_associations_intf eiai
1271: WHERE eiai.batch_id = p_batch_id
1272: AND eiai.process_flag = G_REC_IN_PROCESS
1273: AND eiai.association_id = eia.association_id
1274: AND eiai.transaction_type = G_UPDATE
1281: ,G_USER_ID
1282: ,G_SYSDATE
1283: ,G_LOGIN_ID
1284: ,G_REQUEST_ID
1285: FROM ego_item_associations_intf eiai
1286: WHERE eiai.inventory_item_id = eia.inventory_item_id
1287: AND eiai.organization_id = eia.organization_id
1288: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1289: AND eiai.pk1_value = eia.pk1_value
1293: )
1294: WHERE EXISTS
1295: (
1296: SELECT 1
1297: FROM ego_item_associations_intf eiai
1298: WHERE eiai.inventory_item_id = eia.inventory_item_id
1299: AND eiai.organization_id = eia.organization_id
1300: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1301: -- AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL -- Bug#6927009
1311: ,G_USER_ID
1312: ,G_SYSDATE
1313: ,G_LOGIN_ID
1314: ,G_REQUEST_ID
1315: FROM ego_item_associations_intf eiai, mtl_parameters mp
1316: WHERE eiai.inventory_item_id = eia.inventory_item_id
1317: AND eiai.organization_id = mp.master_organization_id
1318: AND mp.organization_id = eia.organization_id
1319: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1324: )
1325: WHERE EXISTS
1326: (
1327: SELECT 1
1328: FROM ego_item_associations_intf eiai, mtl_parameters mp
1329: WHERE eiai.inventory_item_id = eia.inventory_item_id
1330: AND eiai.organization_id = mp.master_organization_id
1331: AND eia.organization_id = mp.organization_id
1332: AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1344: ,G_USER_ID
1345: ,G_SYSDATE
1346: ,G_LOGIN_ID
1347: ,G_REQUEST_ID
1348: FROM ego_item_associations_intf eiai, mtl_parameters mp
1349: WHERE eiai.inventory_item_id = eia.inventory_item_id
1350: AND eiai.organization_id = mp.master_organization_id
1351: AND eia.organization_id = mp.organization_id
1352: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1358: )
1359: WHERE EXISTS
1360: (
1361: SELECT 1
1362: FROM ego_item_associations_intf eiai, mtl_parameters mp
1363: WHERE eiai.inventory_item_id = eia.inventory_item_id
1364: AND eiai.organization_id = mp.master_organization_id
1365: AND mp.organization_id = eia.organization_id
1366: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1431: WHERE assa.vendor_site_id = eiai.pk2_value
1432: )
1433: END AS supplier_site_name,
1434: data_level_name
1435: FROM ego_item_associations_intf eiai
1436: WHERE eiai.batch_id = p_batch_id
1437: AND eiai.process_flag >= G_REC_MISSING_REQ_VALUE;
1438: l_err_msg VARCHAR2(2000);
1439: BEGIN
1525: l_err_rec.transaction_id
1526: , l_err_rec.organization_id
1527: , l_err_msg
1528: , 'E'
1529: , 'EGO_ITEM_ASSOCIATIONS_INTF'
1530: , 'ITEM_ASSOC'
1531: , G_SYSDATE
1532: , G_USER_ID
1533: , G_SYSDATE
1556: */
1557:
1558:
1559: END LOOP;
1560: UPDATE ego_item_associations_intf
1561: SET process_flag = G_REC_SUCCESS
1562: WHERE batch_id = p_batch_id
1563: AND process_flag = G_REC_IN_PROCESS;
1564: UPDATE ego_item_associations_intf
1560: UPDATE ego_item_associations_intf
1561: SET process_flag = G_REC_SUCCESS
1562: WHERE batch_id = p_batch_id
1563: AND process_flag = G_REC_IN_PROCESS;
1564: UPDATE ego_item_associations_intf
1565: SET process_flag = G_REC_ERROR
1566: WHERE batch_id = p_batch_id
1567: AND process_flag >= G_REC_UNEXPECTED_ERROR;
1568: END insert_errors;
1577: -- p_batch_id IN NUMBER Required
1578: -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
1579: -- x_msg_data OUT NOCOPY VARCHAR2(2000)
1580: -- Version : Initial version 1.0
1581: -- Notes : i) Inserts rows into ego_item_associations_intf for the new SKU's which
1582: -- are getting created.
1583: -- ii) Inserts rows into ego_item_associations_intf for the pack hierarchy
1584: -- if there exists a packaging hierarchy for the item association's item.
1585: -- iii) Converts the processing independent values to Ids
1579: -- x_msg_data OUT NOCOPY VARCHAR2(2000)
1580: -- Version : Initial version 1.0
1581: -- Notes : i) Inserts rows into ego_item_associations_intf for the new SKU's which
1582: -- are getting created.
1583: -- ii) Inserts rows into ego_item_associations_intf for the pack hierarchy
1584: -- if there exists a packaging hierarchy for the item association's item.
1585: -- iii) Converts the processing independent values to Ids
1586: -- a) Master Org Code and Master Org Id for ITEM_SUP and ITEM_SUP_SITE
1587: -- b) Org Code and Org Id for ITEM_SUP_SITE_ORG
1607: x_return_status := fnd_api.G_RET_STS_SUCCESS;
1608: set_globals();
1609: write_log_message(' ego_item_associations_pub.pre_process Batch Id ' || p_batch_id);
1610: -- Set the transaction type to UPPER case..
1611: UPDATE ego_item_associations_intf
1612: SET transaction_type = UPPER(transaction_type)
1613: WHERE batch_id = p_batch_id
1614: AND process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH);
1615: UPDATE ego_item_associations_intf eiai
1611: UPDATE ego_item_associations_intf
1612: SET transaction_type = UPPER(transaction_type)
1613: WHERE batch_id = p_batch_id
1614: AND process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH);
1615: UPDATE ego_item_associations_intf eiai
1616: SET data_level_id = G_ITEM_SUPPLIER_LEVEL
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.data_level_name = G_ITEM_SUP_LEVEL_NAME
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.data_level_name = G_ITEM_SUP_LEVEL_NAME
1620: AND eiai.transaction_type = G_CREATE;
1621: UPDATE ego_item_associations_intf eiai
1622: SET data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1623: WHERE eiai.batch_id = p_batch_id
1624: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1625: AND eiai.data_level_name = G_ITEM_SUP_SITE_LEVEL_NAME
1623: WHERE eiai.batch_id = p_batch_id
1624: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1625: AND eiai.data_level_name = G_ITEM_SUP_SITE_LEVEL_NAME
1626: AND eiai.transaction_type = G_CREATE;
1627: UPDATE ego_item_associations_intf eiai
1628: SET data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1629: WHERE eiai.batch_id = p_batch_id
1630: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1631: AND eiai.data_level_name = G_ITEM_SUP_SITE_ORG_LEVEL_NAME
1631: AND eiai.data_level_name = G_ITEM_SUP_SITE_ORG_LEVEL_NAME
1632: AND eiai.transaction_type = G_CREATE;
1633:
1634: -- Convert the master org values. Convert Organization Code to Organization Id
1635: UPDATE ego_item_associations_intf eiai
1636: SET organization_id = ( SELECT mp.organization_id
1637: FROM mtl_parameters mp
1638: WHERE mp.organization_code = eiai.organization_code
1639: AND mp.master_organization_id = mp.organization_id)
1643: AND eiai.organization_id IS NULL
1644: AND eiai.organization_code IS NOT NULL;
1645:
1646: -- Correct the organization id to master organization for double intersections
1647: UPDATE ego_item_associations_intf eiai
1648: SET organization_id = ( SELECT mp.master_organization_id
1649: FROM mtl_parameters mp
1650: WHERE mp.organization_id = eiai.organization_id)
1651: WHERE eiai.batch_id = p_batch_id
1652: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1653: AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL );
1654:
1655: -- Convert the master org values. Convert Organization Code to Organization Id
1656: UPDATE ego_item_associations_intf eiai
1657: SET organization_id = ( SELECT mp.organization_id
1658: FROM mtl_parameters mp
1659: WHERE mp.organization_code = eiai.organization_code)
1660: WHERE eiai.batch_id = p_batch_id
1662: AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1663: AND eiai.organization_id IS NULL
1664: AND eiai.organization_code IS NOT NULL;
1665: -- Convert the organization id to code in order to throw the errors for rules
1666: UPDATE ego_item_associations_intf eiai
1667: SET organization_code = ( SELECT mp.organization_code
1668: FROM mtl_parameters mp
1669: WHERE mp.organization_id = eiai.organization_id
1670: )
1672: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1673: AND eiai.organization_code IS NULL
1674: AND eiai.organization_id IS NOT NULL;
1675:
1676: UPDATE ego_item_associations_intf eiai
1677: SET pk1_value = ( SELECT vendor_id
1678: FROM ap_suppliers aas
1679: WHERE aas.segment1 = eiai.supplier_number
1680: and NVL(aas.end_date_active,SYSDATE+1) > SYSDATE --bug11072046
1682: WHERE eiai.batch_id = p_batch_id
1683: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1684: AND eiai.pk1_value IS NULL
1685: AND eiai.supplier_number IS NOT NULL;
1686: UPDATE ego_item_associations_intf eiai
1687: SET pk1_value = ( SELECT vendor_id
1688: FROM ap_suppliers aas
1689: WHERE aas.vendor_name = eiai.supplier_name
1690: and NVL(aas.end_date_active,SYSDATE+1) > SYSDATE --bug11072046
1693: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1694: AND eiai.pk1_value IS NULL
1695: AND eiai.supplier_name IS NOT NULL
1696: AND eiai.supplier_number IS NULL;
1697: UPDATE ego_item_associations_intf eiai
1698: SET supplier_name = ( SELECT vendor_name
1699: FROM ap_suppliers aas
1700: WHERE aas.vendor_id = eiai.pk1_value
1701: )
1702: WHERE eiai.batch_id = p_batch_id
1703: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1704: AND eiai.supplier_name IS NULL
1705: AND eiai.pk1_value IS NOT NULL;
1706: UPDATE ego_item_associations_intf eiai
1707: SET pk2_value = NVL(
1708: ( SELECT vendor_site_id
1709: FROM ap_suppliers aas, ap_supplier_sites_all asa
1710: WHERE aas.vendor_id = asa.vendor_id
1717: WHERE eiai.batch_id = p_batch_id
1718: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1719: AND eiai.pk2_value IS NULL
1720: AND eiai.supplier_site_name IS NOT NULL;
1721: UPDATE ego_item_associations_intf eiai
1722: SET supplier_site_name = ( SELECT vendor_site_code
1723: FROM ap_supplier_sites_all asa
1724: WHERE asa.vendor_site_id = eiai.pk2_value
1725: )
1727: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1728: AND eiai.supplier_site_name IS NULL
1729: AND eiai.pk2_value IS NOT NULL;
1730:
1731: UPDATE ego_item_associations_intf eiai
1732: SET inventory_item_id = ( SELECT inventory_item_id
1733: FROM mtl_system_items_b_kfv msibk
1734: WHERE msibk.organization_id = eiai.organization_id
1735: AND msibk.concatenated_segments = eiai.item_number
1738: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1739: AND eiai.inventory_item_id IS NULL
1740: AND eiai.item_number IS NOT NULL;
1741: -- If Row EXISTS in production the its UPDATE
1742: UPDATE ego_item_associations_intf eiai
1743: SET transaction_type = G_UPDATE
1744: WHERE eiai.batch_id = p_batch_id
1745: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1746: AND eiai.transaction_type = G_SYNC
1754: AND eia.pk1_value = eiai.pk1_value
1755: AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
1756: );
1757: -- Rest of the SYNC are CREATE
1758: UPDATE ego_item_associations_intf eiai
1759: SET transaction_type = G_CREATE
1760: WHERE eiai.batch_id = p_batch_id
1761: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1762: AND eiai.transaction_type = G_SYNC;
1760: WHERE eiai.batch_id = p_batch_id
1761: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1762: AND eiai.transaction_type = G_SYNC;
1763:
1764: UPDATE ego_item_associations_intf eiai
1765: SET eiai.process_flag = G_REC_INVALID_TRAN_TYPE
1766: WHERE eiai.batch_id = p_batch_id
1767: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1768: AND eiai.transaction_type NOT IN (G_CREATE, G_UPDATE, G_DELETE);
1767: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1768: AND eiai.transaction_type NOT IN (G_CREATE, G_UPDATE, G_DELETE);
1769:
1770: -- Bug 6438461. Default the Status and Primary Flag if it is null.
1771: UPDATE ego_item_associations_intf eiai
1772: SET eiai.status_code = G_DEFAULT_STATUS_CODE
1773: WHERE eiai.batch_id = p_batch_id
1774: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1775: AND eiai.status_code IS NULL
1774: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1775: AND eiai.status_code IS NULL
1776: AND eiai.transaction_type = G_CREATE;
1777:
1778: UPDATE ego_item_associations_intf eiai
1779: SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
1780: WHERE eiai.batch_id = p_batch_id
1781: AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1782: AND eiai.primary_flag IS NULL
1802: write_log_message(' ego_item_associations_pub.pre_process Error Return Status ' || x_return_status);
1803: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1804: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1805: ROLLBACK TO pre_process_pub;
1806: UPDATE ego_item_associations_intf
1807: SET process_flag = G_REC_UNEXPECTED_ERROR
1808: WHERE batch_id = p_batch_id
1809: AND process_flag = G_REC_TO_BE_PROCESSED;
1810: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1817: write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Return Status ' || x_return_status);
1818: WHEN OTHERS THEN
1819: --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1820: ROLLBACK TO pre_process_pub;
1821: UPDATE ego_item_associations_intf
1822: SET process_flag = G_REC_UNEXPECTED_ERROR
1823: WHERE batch_id = p_batch_id
1824: AND process_flag = G_REC_TO_BE_PROCESSED;
1825: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1845: -- API name : import_item_associations
1846: -- Type : Public.
1847: -- Function : Imports the item associations into the systems.
1848: -- Pre-reqs : i) pre_process should have been called.
1849: -- ii) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF.
1850: -- iii) Errors will be grouped based on concurrent program's request id or batch_id.
1851: -- Query the errors using batch_id for non-concurrent program flows and create a batch or use
1852: -- unique batch id in order to group the errors properly.
1853: -- Parameters :
1939: write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Data ' || x_msg_data);
1940: write_log_message(' ego_item_associations_pub.import_item_associations Error Return Status ' || x_return_status);
1941: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1942: ROLLBACK TO import_item_associations_pub;
1943: UPDATE ego_item_associations_intf
1944: SET process_flag = G_REC_UNEXPECTED_ERROR
1945: WHERE batch_id = x_batch_id
1946: AND process_flag = G_REC_TO_BE_PROCESSED;
1947: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1953: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Data ' || x_msg_data);
1954: write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Return Status ' || x_return_status);
1955: WHEN OTHERS THEN
1956: ROLLBACK TO import_item_associations_pub;
1957: UPDATE ego_item_associations_intf
1958: SET process_flag = G_REC_UNEXPECTED_ERROR
1959: WHERE batch_id = x_batch_id
1960: AND process_flag = G_REC_TO_BE_PROCESSED;
1961: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1982: -- API name : import_item_associations
1983: -- Type : private.
1984: -- Function : Imports the item associations in the excel import flow.
1985: -- Pre-reqs :
1986: -- i) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF if the data is not from temp tables.
1987: -- ii) Errors will be grouped based on concurrent program's request id.
1988: -- Parameters :
1989: -- IN : p_api_version IN NUMBER Required
1990: -- IN OUT : x_batch_id IN OUT NOCOPY Optional
2086: ELSIF ( p_data_level_names(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2087: l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2088: END IF;
2089: write_log_message(' l_data_level_id ' || l_data_level_id);
2090: INSERT INTO ego_item_associations_intf
2091: (
2092: BATCH_ID
2093: ,ITEM_NUMBER
2094: ,INVENTORY_ITEM_ID
2155: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Data ' || x_msg_data);
2156: write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Return Status ' || x_return_status);
2157: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2158: ROLLBACK TO copy_associations_to_items_pub;
2159: UPDATE ego_item_associations_intf
2160: SET process_flag = G_REC_UNEXPECTED_ERROR
2161: WHERE batch_id = p_batch_id
2162: AND process_flag = G_REC_TO_BE_PROCESSED;
2163: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2169: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Data ' || x_msg_data);
2170: write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Return Status ' || x_return_status);
2171: WHEN OTHERS THEN
2172: ROLLBACK TO copy_associations_to_items_pub;
2173: UPDATE ego_item_associations_intf
2174: SET process_flag = G_REC_UNEXPECTED_ERROR
2175: WHERE batch_id = p_batch_id
2176: AND process_flag = G_REC_TO_BE_PROCESSED;
2177: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2240: ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2241: l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2242: l_default_option_code := G_ASSIGN_STYLE_SS_ORG;
2243: IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2244: INSERT INTO ego_item_associations_intf
2245: (
2246: BATCH_ID
2247: ,ORGANIZATION_ID
2248: ,ORGANIZATION_CODE
2297: AND msii2.process_flag = p_msii_miri_process_flag -- Bug 12635842
2298: AND NOT EXISTS
2299: (
2300: SELECT 1
2301: FROM ego_item_associations_intf eiai1
2302: WHERE eiai1.inventory_item_id = msii.inventory_item_id
2303: AND eiai1.organization_id = msii.organization_id
2304: AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
2305: AND eiai1.batch_id = p_batch_id
2318: END IF;
2319: END IF;
2320: --dbms_output.put_line(' Defaulting option ' || ego_common_pvt.get_option_value(l_default_option_code) );
2321: IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2322: INSERT INTO ego_item_associations_intf
2323: (
2324: BATCH_ID
2325: ,ORGANIZATION_ID
2326: ,ORGANIZATION_CODE
2370: AND eia.organization_id = mp.organization_id
2371: AND NOT EXISTS
2372: (
2373: SELECT 1
2374: FROM ego_item_associations_intf eiai1
2375: WHERE eiai1.inventory_item_id = msii.inventory_item_id
2376: AND eiai1.organization_id = msii.organization_id
2377: AND eiai1.data_level_id = l_data_level_id
2378: AND eiai1.batch_id = p_batch_id
2388: AND eia2.pk1_value = eia.pk1_value
2389: AND NVL(eia2.pk2_value,-1) = NVL(eia.pk2_value,-1)
2390: );
2391: -- Copy the rows to existing SKUs
2392: INSERT INTO ego_item_associations_intf
2393: (
2394: BATCH_ID
2395: ,ORGANIZATION_ID
2396: ,ORGANIZATION_CODE
2435: ,NULL
2436: ,NULL
2437: ,G_REQUEST_ID
2438: ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2439: FROM ego_item_associations_intf eiai1
2440: ,mtl_system_items_b_kfv msibk
2441: ,mtl_parameters mp
2442: WHERE eiai1.inventory_item_id = msibk.style_item_id
2443: AND eiai1.organization_id = msibk.organization_id
2448: AND eiai1.process_flag = G_REC_SUCCESS
2449: AND NOT EXISTS
2450: (
2451: SELECT 1
2452: FROM ego_item_associations_intf eiai2
2453: WHERE eiai2.inventory_item_id = msibk.inventory_item_id
2454: AND eiai2.organization_id = msibk.organization_id
2455: AND eiai2.data_level_id = l_data_level_id
2456: AND eiai2.batch_id = p_batch_id
2488: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Return Status ' || x_return_status);
2489: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2490: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2491: ROLLBACK TO copy_from_style_to_SKUs_pub;
2492: UPDATE ego_item_associations_intf
2493: SET process_flag = G_REC_UNEXPECTED_ERROR
2494: WHERE batch_id = p_batch_id
2495: AND process_flag = G_REC_TO_BE_PROCESSED;
2496: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2503: write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Return Status ' || x_return_status);
2504: WHEN OTHERS THEN
2505: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2506: ROLLBACK TO copy_from_style_to_SKUs_pub;
2507: UPDATE ego_item_associations_intf
2508: SET process_flag = G_REC_UNEXPECTED_ERROR
2509: WHERE batch_id = p_batch_id
2510: AND process_flag = G_REC_TO_BE_PROCESSED;
2511: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2566: CURSOR l_pack_item_csr(p_batch_id IN NUMBER)
2567: IS
2568: SELECT msibk.inventory_item_id
2569: ,msibk.organization_id
2570: FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk
2571: WHERE msibk.concatenated_segments = eiai.item_number
2572: AND msibk.organization_id = eiai.organization_id
2573: AND eiai.batch_id = p_batch_id
2574: AND eiai.process_flag = G_REC_TO_BE_PROCESSED
2632: l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2633: l_default_option_code := G_ASSIGN_PACK_SS_ORG;
2634: END IF;
2635: IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2636: INSERT INTO ego_item_associations_intf
2637: (
2638: BATCH_ID
2639: ,SOURCE_SYSTEM_REFERENCE
2640: ,ITEM_NUMBER
2675: ,G_REC_TO_BE_PROCESSED
2676: ,mtl_system_items_interface_s.NEXTVAL
2677: ,G_REQUEST_ID
2678: ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2679: FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk,
2680: mtl_parameters mp
2681: WHERE eiai.inventory_item_id = l_pack_rec.inventory_item_id
2682: AND eiai.organization_id = mp.organization_id
2683: --AND mp.master_organization_id = mp.organization_id
2689: AND msibk.organization_id = mp.organization_id
2690: AND NOT EXISTS
2691: (
2692: SELECT 1
2693: FROM ego_item_associations_intf eiai1
2694: WHERE eiai1.inventory_item_id = msibk.inventory_item_id
2695: AND eiai1.organization_id = msibk.organization_id
2696: AND eiai1.data_level_id = l_data_level_id
2697: AND eiai1.batch_id = p_batch_id
2732: write_log_message(' ego_item_associations_pub.copy_to_packs Error Return Status ' || x_return_status);
2733: WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2734: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2735: ROLLBACK TO copy_to_packs_pub;
2736: UPDATE ego_item_associations_intf
2737: SET process_flag = G_REC_UNEXPECTED_ERROR
2738: WHERE batch_id = p_batch_id
2739: AND process_flag = G_REC_TO_BE_PROCESSED;
2740: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2747: write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Return Status ' || x_return_status);
2748: WHEN OTHERS THEN
2749: --dbms_output.put_line(' Error Msg ' || SQLERRM);
2750: ROLLBACK TO copy_to_packs_pub;
2751: UPDATE ego_item_associations_intf
2752: SET process_flag = G_REC_UNEXPECTED_ERROR
2753: WHERE batch_id = p_batch_id
2754: AND process_flag = G_REC_TO_BE_PROCESSED;
2755: x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;