APPS.EGO_ITEM_ASSOCIATIONS_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

  G_LOGIN_ID                   fnd_user.last_update_login%TYPE;

Line: 81

      SELECT party_id
      INTO G_PARTY_ID
      FROM ego_user_v
      WHERE USER_ID = G_USER_ID;

Line: 87

        SELECT party_id, user_id
        INTO G_PARTY_ID, G_USER_ID
        FROM ego_user_v
        WHERE USER_NAME = FND_GLOBAL.USER_NAME;

Line: 122

    FOR l_null_tx_rec IN ( SELECT ROWID
                             FROM ego_item_associations_intf
                            WHERE batch_id = p_batch_id
                              AND process_flag = G_REC_TO_BE_PROCESSED
                              AND transaction_id IS NULL
                         )
    LOOP
      UPDATE ego_item_associations_intf
         SET transaction_id = mtl_system_items_interface_s.nextval
       WHERE ROWID = l_null_tx_rec.ROWID;

Line: 134

    UPDATE ego_item_associations_intf
       SET process_flag = G_REC_IN_PROCESS
     WHERE batch_id = p_batch_id
       AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 141

    UPDATE ego_item_associations_intf
       SET process_flag = G_REC_MISSING_REQ_VALUE
     WHERE batch_id = p_batch_id
       AND process_flag  = G_REC_IN_PROCESS
       AND transaction_type = G_CREATE
       AND ( ( inventory_item_id IS NULL AND item_number IS NULL )
              OR ( organization_id IS NULL AND organization_code IS NULL)
           );

Line: 149

    UPDATE ego_item_associations_intf
       SET process_flag = G_REC_MISSING_REQ_VALUE
     WHERE batch_id = p_batch_id
       AND process_flag  = G_REC_IN_PROCESS
       AND transaction_type = G_CREATE
       AND ( ( pk1_value IS NULL AND supplier_name IS NULL AND supplier_number IS NULL )
             OR ( ( data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                    OR data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL )
             AND  ( pk2_value IS NULL AND supplier_site_name IS NULL ) )
           );

Line: 193

    UPDATE ego_item_associations_intf eiai
       SET organization_code = ( SELECT mp.organization_code
                                   FROM mtl_parameters mp
                                  WHERE mp.organization_id = eiai.organization_id
                                    AND mp.master_organization_id = mp.organization_id)
     WHERE eiai.batch_id = p_batch_id
       AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.organization_code IS NULL
       AND eiai.organization_id IS NOT NULL;

Line: 205

    UPDATE ego_item_associations_intf eiai
       SET organization_code = ( SELECT mp.organization_code
                                   FROM mtl_parameters mp
                                  WHERE mp.organization_id = eiai.organization_id)
     WHERE eiai.batch_id = p_batch_id
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.organization_code IS NULL
       AND eiai.organization_id IS NOT NULL;

Line: 216

    UPDATE ego_item_associations_intf eiai
       SET item_number = ( SELECT concatenated_segments
                            FROM mtl_system_items_b_kfv msibk
                           WHERE msibk.organization_id = eiai.organization_id
                             AND msibk.inventory_item_id = eiai.inventory_item_id
                         )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.item_number IS NULL
       AND eiai.inventory_item_id IS NOT NULL;

Line: 227

    UPDATE ego_item_associations_intf eiai
       SET inventory_item_id = ( SELECT inventory_item_id
                                    FROM mtl_system_items_b_kfv msibk
                                   WHERE msibk.organization_id = eiai.organization_id
                                     AND msibk.concatenated_segments = eiai.item_number
                                )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.inventory_item_id IS NULL
       AND eiai.item_number IS NOT NULL;

Line: 238

    UPDATE ego_item_associations_intf eiai
       SET (supplier_number,supplier_name) = ( SELECT segment1, vendor_name
                                 FROM ap_suppliers aas
                                WHERE aas.vendor_id = eiai.pk1_value
                             )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.supplier_number IS NULL
       AND eiai.pk1_value IS NOT NULL;

Line: 247

    UPDATE ego_item_associations_intf eiai
       SET supplier_site_name = ( SELECT vendor_site_code
                          FROM ap_supplier_sites_all asa
                         WHERE asa.vendor_site_id = eiai.pk2_value
                           AND asa.org_id = fnd_profile.value('ORG_ID')
                        )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.supplier_site_name IS NULL
       AND eiai.pk2_value IS NOT NULL;

Line: 258

    UPDATE ego_item_associations_intf eiai
       SET item_number = ( SELECT concatenated_segments
                            FROM mtl_system_items_b_kfv msibk
                           WHERE msibk.organization_id = eiai.organization_id
                             AND msibk.inventory_item_id = eiai.inventory_item_id
                         )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.item_number IS NULL
       AND eiai.inventory_item_id IS NOT NULL;

Line: 270

    UPDATE ego_item_associations_intf eiai
       SET association_id = ( SELECT eia.association_id
                                FROM ego_item_associations eia
                               WHERE eia.data_level_id = eiai.data_level_id
                                 AND eia.organization_id = eiai.organization_id
                                 AND eia.inventory_item_id = eiai.inventory_item_id
                                 AND eia.pk1_value = eiai.pk1_value
                                 AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
                            )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE );

Line: 283

    UPDATE ego_item_associations_intf eiai1
       SET process_flag = G_REC_DUPLICATE
     WHERE eiai1.batch_id = p_batch_id
       AND EXISTS
       (
           SELECT 1
             FROM ego_item_associations_intf eiai2
            WHERE eiai2.transaction_type = eiai1.transaction_type
              AND eiai2.batch_id = eiai1.batch_id
              AND eiai2.transaction_id <> eiai1.transaction_id
              AND eiai2.inventory_item_id = eiai1.inventory_item_id
              AND eiai2.organization_id = eiai1.organization_id
              AND eiai2.data_level_id = eiai1.data_level_id
              AND eiai2.pk1_value = eiai1.pk1_value
              AND NVL(eiai1.pk2_value,-1) = NVL(eiai2.pk2_value,-1)
        );

Line: 300

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_MASTER_ORG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
       AND ( eiai.organization_id IS NULL
             OR NOT EXISTS
             (
               SELECT 1
                 FROM mtl_parameters mp
                WHERE mp.master_organization_id = eiai.organization_id
             )
           );

Line: 313

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_ORG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND ( eiai.organization_id IS NULL
             OR NOT EXISTS
             (
               SELECT 1
                 FROM mtl_parameters mp
                WHERE mp.organization_id = eiai.organization_id
             )
           );

Line: 326

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_ITEM
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND ( eiai.inventory_item_id IS NULL
             OR NOT EXISTS
             (
               SELECT 1
                 FROM mtl_system_items_b msib
                WHERE msib.inventory_item_id = eiai.inventory_item_id
                  AND msib.organization_id = eiai.organization_id
             )
           );

Line: 339

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_PK1_VALUE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND ( eiai.pk1_value IS NULL
             OR NOT EXISTS
             (
               SELECT 1
                 FROM ap_suppliers aas
                WHERE aas.vendor_id = eiai.pk1_value
             )
           );

Line: 351

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_PK2_VALUE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND ( eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
	     OR
	     eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL		-- BUG 6322084
	   )
       AND ( eiai.pk2_value IS NULL
             OR NOT EXISTS
             (
               SELECT 1
                 FROM ap_supplier_sites_all assa
                WHERE assa.vendor_site_id = eiai.pk2_value
                  --AND assa.vendor_id = eiai.vendor_id
		  AND assa.vendor_id = eiai.pk1_value
                  AND assa.org_id = fnd_profile.value('ORG_ID')
             )
           );

Line: 370

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_ASSOCIATION_NOT_EXISTS
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE )
       AND eiai.association_id IS NULL;

Line: 376

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_ASSOC_TYPE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND NOT EXISTS
           (
             SELECT 1
               FROM ego_data_level_b edlb
              WHERE edlb.data_level_id = eiai.data_level_id
           );

Line: 386

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_STATUS
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.status_code IS NOT NULL
       AND NOT EXISTS
           (
             SELECT 1
               FROM fnd_lookups fl
              WHERE fl.lookup_type = 'EGO_ASSOCIATION_STATUS'
                AND fl.lookup_code = eiai.status_code
           );

Line: 398

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_INVALID_PRIMARY
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.primary_flag IS NOT NULL
       AND eiai.primary_flag NOT IN(G_DEFAULT_PRIMARY_FLAG, G_PRIMARY);

Line: 454

           ' UPDATE ego_item_associations_intf eiai ' ||
           ' SET process_flag = '||G_REC_NO_CREATE_ASSOC_PRIV ||
           ' WHERE batch_id = :p_batch_id '||
           ' AND process_flag = '||G_REC_IN_PROCESS||
           ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
           ' AND transaction_type = '''||G_CREATE||''' AND NOT '|| l_sec_predicate;

Line: 487

           ' UPDATE ego_item_associations_intf eiai ' ||
           ' SET process_flag = '||G_REC_NO_EDIT_ASSOC_PRIV ||
           ' WHERE batch_id = :p_batch_id '||
           ' AND process_flag = '||G_REC_IN_PROCESS||
           ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
           ' AND ( transaction_type = '''||G_UPDATE||''' OR transaction_type = '''||G_DELETE||''' )'||
           ' AND NOT '|| l_sec_predicate;

Line: 521

           ' UPDATE ego_item_associations_intf eiai ' ||
           ' SET process_flag = '||G_REC_NO_EDIT_ITEM_ORG_PRIV ||
           ' WHERE batch_id = :p_batch_id '||
           ' AND data_level_id = 43105 '||
           ' AND process_flag = '||G_REC_IN_PROCESS||
           ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
           ' AND ( transaction_type = '''||G_UPDATE||''' OR transaction_type = '''||G_DELETE||''' )'||
           ' AND NOT '|| l_sec_predicate;

Line: 542

        UPDATE ego_item_associations_intf eiai
           SET process_flag = G_REC_NO_SUPPL_ACCESS_PRIV
         WHERE eiai.batch_id = p_batch_id
           AND eiai.process_flag = G_REC_IN_PROCESS
           AND NOT EXISTS
               (
                 SELECT 1
                   FROM ego_vendor_v evv
                  WHERE evv.vendor_id = eiai.pk1_value
                    AND evv.user_id = G_USER_ID
               );

Line: 578

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_ORG_NO_ACCESS
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND NOT EXISTS
       (
         SELECT 1
           FROM org_access_view oav
          WHERE oav.organization_id = eiai.organization_id
            AND oav.responsibility_id = FND_PROFILE.Value('RESP_ID')
            AND oav.resp_application_id = FND_PROFILE.Value('RESP_APPL_ID')
       );

Line: 590

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_ALREADY_ASSIGNED
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.transaction_type = G_CREATE
       AND EXISTS
       (
          SELECT 1
            FROM ego_item_associations eia
           WHERE eia.inventory_item_id = eiai.inventory_item_id
             AND eia.organization_id = eiai.organization_id
             AND eia.data_level_id = eiai.data_level_id
             AND eia.pk1_value = eiai.pk1_value
             AND ( ( eia.pk2_value IS NULL AND eiai.pk2_value IS NULL )
                  OR ( eia.pk2_value = eiai.pk2_value )
                  )
        );

Line: 607

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_ASSOC_SITE_NOT_EXISTS
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.transaction_type = G_CREATE
       AND NOT EXISTS
       (
          SELECT 1
            FROM ap_supplier_sites_all assa
           WHERE assa.vendor_id = eiai.pk1_value
             AND assa.org_id = fnd_profile.value('ORG_ID')
        );

Line: 619

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_ASSOC_ITEM_NOT_IN_ORG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND eiai.transaction_type = G_CREATE
       AND NOT EXISTS
       (
         SELECT 1
           FROM mtl_system_items_b_kfv msibk
          WHERE msibk.inventory_item_id = eiai.inventory_item_id
            AND msibk.organization_id = eiai.organization_id
       );

Line: 634

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_DUPLICATE_PRIMARY
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.primary_flag = 'Y'
       AND ( eiai.transaction_type = G_CREATE OR eiai.transaction_type = G_UPDATE)
       AND exists
           (
             SELECT 1
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.batch_id = eiai.batch_id
                AND eiai2.process_flag = eiai.process_flag
                AND eiai2.primary_flag = eiai.primary_flag
                AND eiai2.inventory_item_id = eiai.inventory_item_id
                AND eiai2.organization_id = eiai.organization_id
                AND eiai2.data_level_id = eiai.data_level_id
                AND eiai2.pk1_value = eiai.pk1_value
                AND NVL(eiai2.pk2_value,-1) = NVL(eiai.pk2_value,-1)
                AND ( eiai2.transaction_type = G_CREATE OR eiai2.transaction_type = G_UPDATE )
                AND eiai2.ROWID <> eiai.ROWID
           );

Line: 658

    UPDATE ego_item_associations_intf eiai
       SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
       AND eiai.transaction_type = G_CREATE
       AND eiai.ROWID NOT IN
           (
             SELECT MAX(eiai2.ROWID)
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS
                AND eiai2.primary_flag = G_PRIMARY
                AND eiai2.transaction_type = G_CREATE
                AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
              GROUP BY eiai2.inventory_item_id, eiai2.organization_id
             HAVING count(*) >= 1
           );

Line: 676

    UPDATE ego_item_associations_intf eiai
       SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND eiai.transaction_type = G_CREATE
       AND eiai.ROWID NOT IN
           (
             SELECT MAX(eiai2.ROWID)
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS
                AND eiai2.primary_flag = G_PRIMARY
                AND eiai2.transaction_type = G_CREATE
                AND eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
              GROUP BY eiai2.inventory_item_id, eiai2.organization_id, eiai2.pk1_value
             HAVING count(*) >= 1
           );

Line: 696

    UPDATE ego_item_associations_intf eiai
       SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
       AND eiai.transaction_type = G_UPDATE
       AND eiai.ROWID NOT IN
           (
             SELECT MAX(eiai2.ROWID)
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS
                AND eiai2.primary_flag = G_PRIMARY
                AND eiai2.transaction_type = G_UPDATE
                AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
              GROUP BY eiai2.inventory_item_id, eiai2.organization_id
             HAVING count(*) >= 1
           );

Line: 714

    UPDATE ego_item_associations_intf eiai
       SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND eiai.transaction_type = G_UPDATE
       AND eiai.ROWID NOT IN
           (
             SELECT MAX(eiai2.ROWID)
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS
                AND eiai2.primary_flag = G_PRIMARY
                AND eiai2.transaction_type = G_UPDATE
                AND eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
              GROUP BY eiai2.inventory_item_id, eiai2.organization_id, eiai2.pk1_value
             HAVING count(*) >= 1
           );

Line: 734

    UPDATE ego_item_associations_intf eiai
       SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
       AND eiai.transaction_type = G_CREATE
       AND EXISTS
           (
             SELECT 1
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS
                AND eiai2.inventory_item_id = eiai.inventory_item_id
                AND eiai2.organization_id = eiai.organization_id
                AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
                AND eiai2.primary_flag = G_PRIMARY
                AND eiai2.transaction_type = G_UPDATE
           );

Line: 752

    UPDATE ego_item_associations_intf eiai
       SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND eiai.transaction_type = G_CREATE
       AND EXISTS
           (
             SELECT 1
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS
                AND eiai2.inventory_item_id = eiai.inventory_item_id
                AND eiai2.organization_id = eiai.organization_id
                AND eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                AND eiai2.pk1_value = eiai.pk1_value
                AND eiai2.primary_flag = G_PRIMARY
                AND eiai2.transaction_type = G_UPDATE
           );

Line: 771

    UPDATE ego_item_associations eia
       SET primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE primary_flag = G_PRIMARY
       AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
       AND EXISTS
           ( SELECT 1
               FROM ego_item_associations_intf eiai
              WHERE eiai.inventory_item_id = eia.inventory_item_id
                AND eiai.organization_id = eia.organization_id
                AND eiai.pk1_value <> eia.pk1_value
                AND eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.primary_flag = G_PRIMARY
                AND eiai.transaction_type = G_UPDATE
                AND eiai.data_level_id = eia.data_level_id
             UNION ALL
             SELECT 1
               FROM ego_item_associations_intf eiai
              WHERE eiai.inventory_item_id = eia.inventory_item_id
                AND eiai.organization_id = eia.organization_id
                AND eiai.pk1_value <> eia.pk1_value
                AND eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.primary_flag = G_PRIMARY
                AND eiai.transaction_type = G_CREATE
                AND eiai.data_level_id = eia.data_level_id
            );

Line: 799

    UPDATE ego_item_associations eia
       SET primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eia.primary_flag = G_PRIMARY
       AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND EXISTS
           ( SELECT 1
               FROM ego_item_associations_intf eiai
              WHERE eiai.inventory_item_id = eia.inventory_item_id
                AND eiai.organization_id = eia.organization_id
                AND eiai.pk1_value = eia.pk1_value
                AND eiai.pk2_value <> eia.pk2_value
                AND eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.primary_flag = G_PRIMARY
                AND eiai.transaction_type = G_UPDATE
                AND eiai.data_level_id = eia.data_level_id
             UNION ALL
             SELECT 1
               FROM ego_item_associations_intf eiai
              WHERE eiai.inventory_item_id = eia.inventory_item_id
                AND eiai.organization_id = eia.organization_id
                AND eiai.pk1_value = eia.pk1_value
                AND eiai.pk2_value <> eia.pk2_value
                AND eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.primary_flag = G_PRIMARY
                AND eiai.transaction_type = G_CREATE
                AND eiai.data_level_id = eia.data_level_id
            );

Line: 828

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_PRIMARY_NOT_ACTIVE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
       AND ( eiai.transaction_type = G_CREATE OR eiai.transaction_type = G_UPDATE )
       AND ( ( eiai.primary_flag = G_PRIMARY
             AND (( eiai.status_code <> G_ACTIVE
                    AND eiai.status_code IS NOT NULL ) -- Both attrs are from interface
             OR EXISTS (SELECT 1                       -- Primary flag is being updated and Staus inactive in prod
                           FROM ego_item_associations eia
                          WHERE eia.inventory_item_id = eiai.inventory_item_id
                            AND eia.organization_id = eiai.organization_id
                            AND eia.data_level_id = eiai.data_level_id
                            AND eia.pk1_value = eiai.pk1_value
                            AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
                            AND eiai.status_code IS NULL
                            AND eia.status_code <> G_ACTIVE
                        )
                 )
            )
            OR EXISTS -- Status being updated and primary flag is set in prod
              (
                SELECT 1
                  FROM ego_item_associations eia
                 WHERE eia.inventory_item_id = eiai.inventory_item_id
                   AND eia.organization_id = eiai.organization_id
                   AND eia.data_level_id = eiai.data_level_id
                   AND eia.pk1_value = eiai.pk1_value
                   AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
                   AND eiai.primary_flag IS NULL
                   AND eiai.status_code <> G_ACTIVE
                   AND eia.primary_flag = G_PRIMARY
              )
           );

Line: 863

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_PARENT_NOT_ACTIVE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.status_code = G_ACTIVE
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND NOT EXISTS
           (
             SELECT 1
               FROM ego_item_associations eia
              WHERE eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
                AND eia.inventory_item_id = eiai.inventory_item_id
                AND eia.organization_id = eiai.organization_id
                AND eia.pk1_value = eiai.pk1_value
                AND eia.status_code = G_ACTIVE
              UNION ALL
             SELECT 1
               FROM ego_item_associations_intf eiai2
              WHERE eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
                AND eiai2.inventory_item_id = eiai.inventory_item_id
                AND eiai2.organization_id = eiai.organization_id
                AND eiai2.pk1_value = eiai.pk1_value
                AND eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS
                AND eiai2.status_code = G_ACTIVE
           );

Line: 889

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_PARENT_NOT_ACTIVE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.status_code = G_ACTIVE
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND NOT EXISTS
           (
             SELECT 1
               FROM ego_item_associations eia, mtl_parameters mp
              WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                AND eia.inventory_item_id = eiai.inventory_item_id
                AND eia.organization_id = mp.master_organization_id
                AND mp.organization_id = eiai.organization_id
                AND eia.pk1_value = eiai.pk1_value
                AND eia.pk2_value = eiai.pk2_value
                AND eia.status_code = G_ACTIVE
             UNION ALL
             SELECT 1
               FROM ego_item_associations_intf eiai2, mtl_parameters mp
              WHERE eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                AND eiai2.inventory_item_id = eiai.inventory_item_id
                AND eiai2.organization_id = mp.master_organization_id
                AND mp.organization_id = eiai.organization_id
                AND eiai2.pk1_value = eiai.pk1_value
                AND eiai2.pk2_value = eiai.pk2_value
                AND eiai2.batch_id = p_batch_id
                AND eiai2.process_flag = G_REC_IN_PROCESS -- Means there is no validation error
                AND eiai2.status_code = G_ACTIVE
           );

Line: 919

      UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_SUPPLIER_NOT_ASSIGNED
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND eiai.transaction_type = G_CREATE
       AND NOT EXISTS
       (
         SELECT 1
           FROM ego_item_associations eia, mtl_parameters mp
          WHERE eia.inventory_item_id = eiai.inventory_item_id
            AND eia.organization_id = mp.master_organization_id
            AND mp.organization_id = eiai.organization_id
            AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
            AND eia.pk1_value = eiai.pk1_value
            AND eia.pk2_value IS NULL
         UNION ALL
         SELECT 1
           FROM ego_item_associations_intf eiai1, mtl_parameters mp
          WHERE eiai1.inventory_item_id = eiai.inventory_item_id
            AND eiai1.organization_id = mp.master_organization_id
            AND mp.organization_id = eiai.organization_id
            AND eiai1.data_level_id = G_ITEM_SUPPLIER_LEVEL
            AND eiai1.pk1_value = eiai.pk1_value
            AND eiai1.pk2_value IS NULL
            AND eiai1.process_flag = G_REC_IN_PROCESS -- means there is not validation error
	    AND eiai1.batch_id = p_batch_id			-- BUG 6322084
	    AND eiai1.transaction_type = G_CREATE		-- BUG 6322084
       );

Line: 948

    UPDATE ego_item_associations_intf eiai
       SET process_flag = G_REC_SITE_NOT_ASSIGNED
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND eiai.transaction_type = G_CREATE
       AND NOT EXISTS
       (
         SELECT 1
           FROM ego_item_associations eia, mtl_parameters mp
          WHERE eia.inventory_item_id = eiai.inventory_item_id
            AND eia.organization_id = mp.master_organization_id
            AND mp.organization_id = eiai.organization_id
            AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
            AND eia.pk1_value = eiai.pk1_value
            AND eia.pk2_value = eiai.pk2_value
         UNION ALL
         SELECT 1
           FROM ego_item_associations_intf eiai1, mtl_parameters mp
          WHERE eiai1.inventory_item_id = eiai.inventory_item_id
            AND eiai1.organization_id = mp.master_organization_id
            AND mp.organization_id = eiai.organization_id
            AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
            AND eiai1.pk1_value = eiai.pk1_value
            AND eiai1.pk2_value = eiai.pk2_value
            AND eiai1.process_flag = G_REC_IN_PROCESS -- means there is not validation error
	    AND eiai1.batch_id = p_batch_id			-- BUG 6322084
	    AND eiai1.transaction_type = G_CREATE		-- BUG 6322084
       );

Line: 997

  PROCEDURE perform_delete(p_batch_id IN NUMBER)
  IS
  BEGIN
    -- Delete Item-Site-Org
    DELETE
      FROM ego_item_associations eia
     WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND EXISTS
           (
              SELECT 1
                FROM ego_item_associations_intf eiai
               WHERE eiai.association_id = eia.association_id
                 AND eiai.batch_id = p_batch_id
                 AND eiai.process_flag = G_REC_IN_PROCESS
                 AND eiai.transaction_type = G_DELETE
                 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
            );

Line: 1015

    DELETE
      FROM ego_item_associations eia
     WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND EXISTS
           (
              SELECT 1
                FROM ego_item_associations_intf eiai, mtl_parameters mp
               WHERE eiai.inventory_item_id = eia.inventory_item_id
                 AND eiai.batch_id = p_batch_id
                 AND eiai.process_flag = G_REC_IN_PROCESS
                 AND eiai.organization_id = mp.master_organization_id
                 AND mp.organization_id = eia.organization_id
                 AND eiai.pk1_value = eia.pk1_value
                 AND eiai.pk2_value = eia.pk2_value
                 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                 AND eiai.transaction_type = G_DELETE
            );

Line: 1033

    DELETE
      FROM ego_item_associations eia
     WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND EXISTS
           (
              SELECT 1
                FROM ego_item_associations_intf eiai, mtl_parameters mp
               WHERE eiai.inventory_item_id = eia.inventory_item_id
                 AND eiai.batch_id = p_batch_id
                 AND eiai.process_flag = G_REC_IN_PROCESS
                 AND eiai.organization_id = mp.master_organization_id
                 AND mp.organization_id = eia.organization_id
                 AND eiai.pk1_value = eia.pk1_value
                 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
                 AND eiai.transaction_type = G_DELETE
            );

Line: 1049

    DELETE
      FROM ego_item_associations eia
     WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND EXISTS
           (
              SELECT 1
                FROM ego_item_associations_intf eiai, mtl_parameters mp
               WHERE eiai.association_id = eia.association_id
                 AND eiai.batch_id = p_batch_id
                 AND eiai.process_flag = G_REC_IN_PROCESS
                 AND eiai.organization_id = mp.master_organization_id
                 AND mp.organization_id = eia.organization_id
                 AND eiai.pk1_value = eia.pk1_value
                 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                 AND eiai.transaction_type = G_DELETE
            );

Line: 1065

    DELETE
      FROM ego_item_associations eia
     WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
       AND EXISTS
           (
              SELECT 1
                FROM ego_item_associations_intf eiai, mtl_parameters mp
               WHERE eiai.inventory_item_id = eia.inventory_item_id
                 AND eiai.batch_id = p_batch_id
                 AND eiai.process_flag = G_REC_IN_PROCESS
                 AND eiai.organization_id = mp.master_organization_id
                 AND mp.organization_id = eia.organization_id
                 AND eiai.pk1_value = eia.pk1_value
                 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
                 AND eiai.transaction_type = G_DELETE
            );

Line: 1081

    DELETE
      FROM ego_item_associations eia
     WHERE EXISTS
           (
              SELECT 1
                FROM ego_item_associations_intf eiai
               WHERE eiai.association_id = eia.association_id
                 AND eiai.batch_id = p_batch_id
                 AND eiai.process_flag = G_REC_IN_PROCESS
                 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
                 AND eiai.transaction_type = G_DELETE
            );

Line: 1093

  END perform_delete;

Line: 1111

    INSERT INTO
    ego_item_associations
    (
      association_id,
      organization_id,
      inventory_item_id,
      pk1_value,
      pk2_value,
      data_level_id,
      status_code,
      primary_flag,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login,
      request_id,
      program_application_id,
      program_id,
      program_update_date
    )
    SELECT ego_item_associations_s.NEXTVAL,
           eiai.organization_id,
           eiai.inventory_item_id,
           eiai.pk1_value,
           CASE
             WHEN eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL THEN
               NULL
             ELSE
               eiai.pk2_value
           END AS pk2_value,
           eiai.data_level_id,
           NVL(eiai.status_code,G_DEFAULT_STATUS_CODE),
           CASE
             WHEN eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL THEN
               NULL
             ELSE
               NVL(eiai.primary_flag,G_DEFAULT_PRIMARY_FLAG)
           END AS primary_flag,
           G_USER_ID,
           G_SYSDATE,
           G_USER_ID,
           G_SYSDATE,
           G_LOGIN_ID,
           G_REQUEST_ID,
           G_PROG_APPID,
           G_PROG_ID,
           G_SYSDATE
      FROM ego_item_associations_intf eiai
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_IN_PROCESS
       AND eiai.transaction_type = G_CREATE
       AND NOT EXISTS
           (
             SELECT 1
               FROM ego_item_associations eia1
              WHERE eia1.inventory_item_id = eiai.inventory_item_id
                AND eia1.organization_id = eiai.organization_id
                AND eia1.data_level_id = eiai.data_level_id
                AND eia1.pk1_value = eiai.pk1_value
                AND NVL(eia1.pk2_value,-1) = NVL(eiai.pk2_value,-1)
           );

Line: 1189

  PROCEDURE perform_update ( p_batch_id IN NUMBER )
  IS
  BEGIN
    UPDATE ego_item_associations eia
       SET (primary_flag, status_code, last_updated_by, last_update_date, last_update_login, request_id) =
                         ( SELECT NVL(eiai.primary_flag, eia.primary_flag)
                                  ,NVL(eiai.status_code, eia.status_code)
                                  ,G_USER_ID
                                  ,G_SYSDATE
                                  ,G_LOGIN_ID
                                  ,G_REQUEST_ID
                                 FROM ego_item_associations_intf eiai
                                WHERE eiai.association_id = eia.association_id
                                  AND eiai.batch_id = p_batch_id
                                  AND eiai.process_flag = G_REC_IN_PROCESS
                                  AND eiai.transaction_type = G_UPDATE
                                  AND ROWNUM = 1
                          )
     WHERE EXISTS
           (
             SELECT 1
               FROM ego_item_associations_intf eiai
              WHERE eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.association_id = eia.association_id
                AND eiai.transaction_type = G_UPDATE
            );

Line: 1217

    UPDATE ego_item_associations eia
       SET (status_code, primary_flag, last_updated_by, last_update_date, last_update_login, request_id) =
                         ( SELECT eiai.status_code
			                            ,G_DEFAULT_PRIMARY_FLAG
                                  ,G_USER_ID
                                  ,G_SYSDATE
                                  ,G_LOGIN_ID
                                  ,G_REQUEST_ID
                                 FROM ego_item_associations_intf eiai
                                WHERE eiai.inventory_item_id = eia.inventory_item_id
                                  AND eiai.organization_id = eia.organization_id
                                  AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
                                  AND eiai.pk1_value = eia.pk1_value
                                  AND eiai.batch_id = p_batch_id
                                  AND eiai.process_flag = G_REC_IN_PROCESS
                                  AND ROWNUM = 1
                          )
     WHERE EXISTS
           (
             SELECT 1
               FROM ego_item_associations_intf eiai
              WHERE eiai.inventory_item_id = eia.inventory_item_id
                AND eiai.organization_id = eia.organization_id
                AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
                AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                AND eiai.pk1_value = eia.pk1_value
                AND eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.status_code <> G_ACTIVE
            );

Line: 1247

    UPDATE ego_item_associations eia
       SET (status_code, last_updated_by, last_update_date, last_update_login, request_id) =
                         ( SELECT eiai.status_code
                                  ,G_USER_ID
                                  ,G_SYSDATE
                                  ,G_LOGIN_ID
                                  ,G_REQUEST_ID
                                 FROM ego_item_associations_intf eiai, mtl_parameters mp
                                WHERE eiai.inventory_item_id = eia.inventory_item_id
                                  AND eiai.organization_id = mp.master_organization_id
                                  AND mp.organization_id = eia.organization_id
                                  AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
                                  AND eiai.pk1_value = eia.pk1_value
                                  AND eiai.batch_id = p_batch_id
                                  AND eiai.process_flag = G_REC_IN_PROCESS
                                  AND ROWNUM = 1
                          )
     WHERE EXISTS
           (
             SELECT 1
               FROM ego_item_associations_intf eiai, mtl_parameters mp
              WHERE eiai.inventory_item_id = eia.inventory_item_id
                AND eiai.organization_id = mp.master_organization_id
                AND eia.organization_id = mp.organization_id
                AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
                AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
                AND eiai.pk1_value = eia.pk1_value
                AND eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.status_code <> G_ACTIVE
            );

Line: 1278

      UPDATE ego_item_associations eia
       SET (status_code, last_updated_by, last_update_date, last_update_login, request_id) =
                         ( SELECT eiai.status_code
                                  ,G_USER_ID
                                  ,G_SYSDATE
                                  ,G_LOGIN_ID
                                  ,G_REQUEST_ID
                                 FROM ego_item_associations_intf eiai, mtl_parameters mp
                                WHERE eiai.inventory_item_id = eia.inventory_item_id
                                  AND eiai.organization_id = mp.master_organization_id
                                  AND eia.organization_id = mp.organization_id
                                  AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                                  AND eiai.pk1_value = eia.pk1_value
                                  AND eiai.pk2_value = eia.pk2_value
                                  AND eiai.batch_id = p_batch_id
                                  AND eiai.process_flag = G_REC_IN_PROCESS
                                  AND ROWNUM = 1
                          )
     WHERE EXISTS
           (
             SELECT 1
               FROM ego_item_associations_intf eiai, mtl_parameters mp
              WHERE eiai.inventory_item_id = eia.inventory_item_id
                AND eiai.organization_id = mp.master_organization_id
                AND mp.organization_id = eia.organization_id
                AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
                AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
                AND eiai.pk1_value = eia.pk1_value
                AND eiai.pk2_value = eia.pk2_value
                AND eiai.batch_id = p_batch_id
                AND eiai.process_flag = G_REC_IN_PROCESS
                AND eiai.status_code <> G_ACTIVE
            );

Line: 1311

END perform_update;

Line: 1327

  PROCEDURE insert_errors (p_batch_id IN NUMBER)
  IS
    CURSOR l_err_rows_csr(p_batch_id IN NUMBER)
    IS
      SELECT process_flag, transaction_id, organization_id,
             CASE
              WHEN data_level_id IS NOT NULL
               THEN
                   (
                     SELECT user_data_level_name
                       FROM ego_data_level_tl edlt
                      WHERE edlt.data_level_id = eiai.data_level_id
                        AND edlt.language = USERENV('LANG')
                   )
              ELSE
                   (
                     SELECT user_data_level_name
                       FROM ego_data_level_vl eldt
                      WHERE eldt.application_id = 431
                        AND eldt.attr_group_type = 'EGO_ITEMMGMT_GROUP'
                        AND eldt.data_level_name = eiai.data_level_name
                   )
             END user_data_level_name,
             CASE
              WHEN supplier_name IS NOT NULL
               THEN supplier_name
              ELSE
               (
                 SELECT vendor_name
                   FROM ap_suppliers aas
                  WHERE aas.vendor_id = eiai.pk1_value
               )
             END AS supplier_name,
             CASE
              WHEN supplier_site_name IS NOT NULL
               THEN supplier_site_name
              ELSE
               (
                 SELECT vendor_site_code
                   FROM ap_supplier_sites_all assa
                  WHERE assa.vendor_site_id = eiai.pk2_value
               )
             END AS supplier_site_name,
             data_level_name
        FROM ego_item_associations_intf eiai
       WHERE eiai.batch_id = p_batch_id
         AND eiai.process_flag >= G_REC_MISSING_REQ_VALUE;

Line: 1446

      INSERT INTO mtl_interface_errors
          ( transaction_id
            , organization_id
            , error_message
            , message_type
            , table_name
            , bo_identifier
            , last_update_date
            , last_updated_by
            , creation_date
            , created_by
            , request_id
          )
      VALUES
          (
            l_err_rec.transaction_id
            , l_err_rec.organization_id
            , l_err_msg
            , 'E'
            , 'EGO_ITEM_ASSOCIATIONS_INTF'
            , 'ITEM_ASSOC'
            , G_SYSDATE
            , G_USER_ID
            , G_SYSDATE
            , G_USER_ID
            , G_REQUEST_ID
          );

Line: 1496

    UPDATE ego_item_associations_intf
       SET process_flag = G_REC_SUCCESS
     WHERE batch_id = p_batch_id
       AND process_flag = G_REC_IN_PROCESS;

Line: 1500

    UPDATE ego_item_associations_intf
       SET process_flag = G_REC_ERROR
     WHERE batch_id = p_batch_id
       AND process_flag >= G_REC_UNEXPECTED_ERROR;

Line: 1504

  END insert_errors;

Line: 1547

    UPDATE ego_item_associations_intf
       SET transaction_type = UPPER(transaction_type)
     WHERE batch_id = p_batch_id
       AND process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH);

Line: 1551

    UPDATE ego_item_associations_intf eiai
       SET data_level_id = G_ITEM_SUPPLIER_LEVEL
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.data_level_name = G_ITEM_SUP_LEVEL_NAME
       AND eiai.transaction_type = G_CREATE;

Line: 1557

    UPDATE ego_item_associations_intf eiai
       SET data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.data_level_name = G_ITEM_SUP_SITE_LEVEL_NAME
       AND eiai.transaction_type = G_CREATE;

Line: 1563

    UPDATE ego_item_associations_intf eiai
       SET data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.data_level_name = G_ITEM_SUP_SITE_ORG_LEVEL_NAME
       AND eiai.transaction_type = G_CREATE;

Line: 1571

    UPDATE ego_item_associations_intf eiai
       SET organization_id = ( SELECT mp.organization_id
                                 FROM mtl_parameters mp
                                WHERE mp.organization_code = eiai.organization_code
                                  AND mp.master_organization_id = mp.organization_id)
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
       AND eiai.organization_id IS NULL
       AND eiai.organization_code IS NOT NULL;

Line: 1583

    UPDATE ego_item_associations_intf eiai
       SET organization_id =  ( SELECT mp.master_organization_id
                                 FROM mtl_parameters mp
                                WHERE mp.organization_id = eiai.organization_id)
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL );

Line: 1592

    UPDATE ego_item_associations_intf eiai
       SET organization_id = ( SELECT mp.organization_id
                                 FROM mtl_parameters mp
                                WHERE mp.organization_code = eiai.organization_code)
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
       AND eiai.organization_id IS NULL
       AND eiai.organization_code IS NOT NULL;

Line: 1602

    UPDATE ego_item_associations_intf eiai
       SET organization_code = ( SELECT mp.organization_code
                                   FROM mtl_parameters mp
                                  WHERE mp.organization_id = eiai.organization_id
                               )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.organization_code IS NULL
       AND eiai.organization_id IS NOT NULL;

Line: 1612

    UPDATE ego_item_associations_intf eiai
       SET pk1_value = ( SELECT vendor_id
                           FROM ap_suppliers aas
                          WHERE aas.segment1 = eiai.supplier_number
                        )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.pk1_value IS NULL
       AND eiai.supplier_number IS NOT NULL;

Line: 1621

    UPDATE ego_item_associations_intf eiai
       SET pk1_value = ( SELECT vendor_id
                           FROM ap_suppliers aas
                          WHERE aas.vendor_name = eiai.supplier_name
                        )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.pk1_value IS NULL
       AND eiai.supplier_name IS NOT NULL
       AND eiai.supplier_number IS NULL;

Line: 1631

    UPDATE ego_item_associations_intf eiai
       SET supplier_name = ( SELECT vendor_name
                               FROM ap_suppliers aas
                              WHERE aas.vendor_id = eiai.pk1_value
                            )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.supplier_name IS NULL
       AND eiai.pk1_value IS NOT NULL;

Line: 1640

    UPDATE ego_item_associations_intf eiai
       SET pk2_value = NVL(
		       ( SELECT vendor_site_id
                           FROM ap_suppliers aas, ap_supplier_sites_all asa
                          WHERE aas.vendor_id = asa.vendor_id
                            AND asa.vendor_site_code = eiai.supplier_site_name
			    AND asa.vendor_id = eiai.pk1_value			-- BUG 6322084
                            AND asa.org_id = fnd_profile.value('ORG_ID')
                        )
			, -1)
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.pk2_value IS NULL
       AND eiai.supplier_site_name IS NOT NULL;

Line: 1654

    UPDATE ego_item_associations_intf eiai
       SET supplier_site_name = ( SELECT vendor_site_code
                                    FROM ap_supplier_sites_all asa
                                    WHERE asa.vendor_site_id = eiai.pk2_value
                                )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.supplier_site_name IS NULL
       AND eiai.pk2_value IS NOT NULL;

Line: 1664

    UPDATE ego_item_associations_intf eiai
       SET inventory_item_id = ( SELECT inventory_item_id
                                    FROM mtl_system_items_b_kfv msibk
                                   WHERE msibk.organization_id = eiai.organization_id
                                     AND msibk.concatenated_segments = eiai.item_number
                                )
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.inventory_item_id IS NULL
       AND eiai.item_number IS NOT NULL;

Line: 1675

    UPDATE ego_item_associations_intf eiai
       SET transaction_type = G_UPDATE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.transaction_type = G_SYNC
       AND EXISTS
       (
          SELECT 1
            FROM ego_item_associations eia
           WHERE eia.inventory_item_id = eiai.inventory_item_id
             AND eia.organization_id = eiai.organization_id
             AND eia.data_level_id = eiai.data_level_id
             AND eia.pk1_value = eiai.pk1_value
             AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
        );

Line: 1691

    UPDATE ego_item_associations_intf eiai
       SET transaction_type = G_CREATE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.transaction_type = G_SYNC;

Line: 1697

    UPDATE ego_item_associations_intf eiai
       SET eiai.process_flag = G_REC_INVALID_TRAN_TYPE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.transaction_type NOT IN (G_CREATE, G_UPDATE, G_DELETE);

Line: 1704

    UPDATE ego_item_associations_intf eiai
       SET eiai.status_code = G_DEFAULT_STATUS_CODE
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.status_code IS NULL
       AND eiai.transaction_type = G_CREATE;

Line: 1711

    UPDATE ego_item_associations_intf eiai
       SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
     WHERE eiai.batch_id = p_batch_id
       AND eiai.process_flag  IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
       AND eiai.primary_flag IS NULL
       AND eiai.transaction_type = G_CREATE;

Line: 1739

        UPDATE ego_item_associations_intf
           SET process_flag = G_REC_UNEXPECTED_ERROR
         WHERE batch_id = p_batch_id
           AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 1754

        UPDATE ego_item_associations_intf
           SET process_flag = G_REC_UNEXPECTED_ERROR
         WHERE batch_id = p_batch_id
           AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 1846

    perform_delete(x_batch_id);

Line: 1850

    perform_update(x_batch_id);

Line: 1852

    insert_errors(x_batch_id);

Line: 1876

        UPDATE ego_item_associations_intf
           SET process_flag = G_REC_UNEXPECTED_ERROR
         WHERE batch_id = x_batch_id
           AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 1890

        UPDATE ego_item_associations_intf
           SET process_flag = G_REC_UNEXPECTED_ERROR
         WHERE batch_id = x_batch_id
           AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 1998

      SELECT master_organization_id
        INTO l_master_org_id
        FROM mtl_parameters
       WHERE organization_id = p_from_org_id;

Line: 2023

      INSERT INTO ego_item_associations_intf
      (
        BATCH_ID
        ,ITEM_NUMBER
        ,INVENTORY_ITEM_ID
        ,ORGANIZATION_ID
        ,PK1_VALUE
        ,PK2_VALUE
        ,DATA_LEVEL_ID
        ,PRIMARY_FLAG
        ,STATUS_CODE
        ,TRANSACTION_TYPE
        ,PROCESS_FLAG
        ,TRANSACTION_ID
        ,SOURCE_SYSTEM_REFERENCE
        ,SOURCE_SYSTEM_ID
        ,BUNDLE_ID
        ,REQUEST_ID
      )
      SELECT p_batch_id
             ,msii.item_number
             ,msii.inventory_item_id
             ,msii.organization_id
             ,eia.pk1_value
             ,eia.pk2_value
             ,eia.data_level_id
             ,eia.primary_flag
             ,eia.status_code
             ,G_CREATE
             ,G_REC_TO_BE_PROCESSED
             ,msii.transaction_id
             ,msii.source_system_reference
             ,msii.source_system_id
             ,msii.bundle_id
             ,G_REQUEST_ID
        FROM ego_item_associations eia
             ,mtl_system_items_interface msii
             ,mtl_parameters mp
       WHERE eia.inventory_item_id = p_src_item_id
         -- AND msii.organization_id = mp.organization_id Copy_Item_Id will be populated only for master org items
         --AND mp.organization_id = mp.master_organization_id Copy all triple intersections
         AND msii.set_process_id = p_batch_id
         AND msii.copy_item_id = p_src_item_id
         AND eia.data_level_id = l_data_level_id
         AND msii.process_flag = G_REC_TO_BE_PROCESSED
         AND eia.organization_id = msii.organization_id
         AND msii.organization_id = mp.organization_id;

Line: 2070

    write_log_message(' ego_item_associations_pub.copy_associations_to_items after insert ');

Line: 2092

      UPDATE ego_item_associations_intf
         SET process_flag = G_REC_UNEXPECTED_ERROR
       WHERE batch_id = p_batch_id
         AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 2106

      UPDATE ego_item_associations_intf
         SET process_flag = G_REC_UNEXPECTED_ERROR
       WHERE batch_id = p_batch_id
         AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 2176

          INSERT INTO ego_item_associations_intf
          (
            BATCH_ID
            ,ORGANIZATION_ID
            ,ORGANIZATION_CODE
            ,ITEM_NUMBER
            ,INVENTORY_ITEM_ID
            ,PK1_VALUE
            ,PK2_VALUE
            ,DATA_LEVEL_ID
            ,PRIMARY_FLAG
            ,STATUS_CODE
            ,TRANSACTION_TYPE
            ,PROCESS_FLAG
            ,TRANSACTION_ID
            ,SOURCE_SYSTEM_REFERENCE
            ,SOURCE_SYSTEM_ID
            ,BUNDLE_ID
            ,REQUEST_ID
            ,CREATED_BY -- Bug 6459846
          )
          SELECT p_batch_id
                 ,mp.organization_id
                 ,mp.organization_code
                 ,msii.item_number
                 ,msii.inventory_item_id
                 ,eia.pk1_value
                 ,eia.pk2_value
                 ,eia.data_level_id
                 ,eia.primary_flag
                 ,eia.status_code
                 ,G_CREATE
                 ,G_REC_TO_BE_PROCESSED
                 ,msii.transaction_id
                 ,msii.source_system_reference
                 ,msii.source_system_id
                 ,msii.bundle_id
                 ,G_REQUEST_ID
                 ,G_SKIP_SECURIY_CHECK -- Bug 6459846
            FROM ego_item_associations eia
                 ,mtl_system_items_interface msii
                 ,mtl_parameters mp
                 ,mtl_system_items_interface msii2
           WHERE eia.inventory_item_id = msii2.style_item_id
             AND msii.organization_id = mp.organization_id
             AND msii.set_process_id = p_batch_id
             AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
             AND msii.process_flag = G_REC_TO_BE_PROCESSED
             AND eia.organization_id = mp.organization_id
             AND msii.inventory_item_id = msii2.inventory_item_id
             AND msii2.organization_id = mp.master_organization_id
             AND msii2.style_item_id IS NOT NULL
             AND msii2.set_process_id = p_batch_id
             AND msii2.process_flag = G_REC_TO_BE_PROCESSED
             AND NOT EXISTS
                 (
                   SELECT 1
                     FROM ego_item_associations_intf eiai1
                    WHERE eiai1.inventory_item_id = msii.inventory_item_id
                      AND eiai1.organization_id = msii.organization_id
                      AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
                      AND eiai1.batch_id = p_batch_id
                      AND eiai1.process_flag = G_REC_TO_BE_PROCESSED
                      AND eiai1.pk1_value = eia.pk1_value
                      AND NVL(eiai1.pk2_value,-1) = NVL(eia.pk2_value,-1)
                   UNION ALL
                   SELECT 1
                     FROM ego_item_associations eia2
                    WHERE eia2.inventory_item_id = msii.inventory_item_id
                      AND eia2.organization_id = msii.organization_id
                      AND eia2.data_level_id = eia.data_level_id
                      AND eia2.pk1_value = eia.pk1_value
                      AND NVL(eia2.pk2_value,-1) = NVL(eia.pk2_value,-1)
                 );

Line: 2254

        INSERT INTO ego_item_associations_intf
        (
          BATCH_ID
          ,ORGANIZATION_ID
          ,ORGANIZATION_CODE
          ,ITEM_NUMBER
          ,INVENTORY_ITEM_ID
          ,PK1_VALUE
          ,PK2_VALUE
          ,DATA_LEVEL_ID
          ,PRIMARY_FLAG
          ,STATUS_CODE
          ,TRANSACTION_TYPE
          ,PROCESS_FLAG
          ,TRANSACTION_ID
          ,SOURCE_SYSTEM_REFERENCE
          ,SOURCE_SYSTEM_ID
          ,BUNDLE_ID
          ,REQUEST_ID
          ,CREATED_BY -- Bug 6459846
        )
        SELECT p_batch_id
               ,mp.organization_id
               ,mp.organization_code
               ,msii.item_number
               ,msii.inventory_item_id
               ,eia.pk1_value
               ,eia.pk2_value
               ,eia.data_level_id
               ,eia.primary_flag
               ,eia.status_code
               ,G_CREATE
               ,G_REC_TO_BE_PROCESSED
               ,msii.transaction_id
               ,msii.source_system_reference
               ,msii.source_system_id
               ,msii.bundle_id
               ,G_REQUEST_ID
               ,G_SKIP_SECURIY_CHECK -- Bug 6459846
          FROM ego_item_associations eia
               ,mtl_system_items_interface msii
               ,mtl_parameters mp
         WHERE eia.inventory_item_id = msii.style_item_id
           AND msii.organization_id = mp.organization_id
           --AND mp.organization_id = mp.master_organization_id
           AND msii.set_process_id = p_batch_id
           AND eia.data_level_id = l_data_level_id
           AND msii.process_flag = G_REC_TO_BE_PROCESSED
           AND eia.organization_id = mp.organization_id
           AND NOT EXISTS
               (
                 SELECT 1
                   FROM ego_item_associations_intf eiai1
                  WHERE eiai1.inventory_item_id = msii.inventory_item_id
                    AND eiai1.organization_id = msii.organization_id
                    AND eiai1.data_level_id = l_data_level_id
                    AND eiai1.batch_id = p_batch_id
                    AND eiai1.process_flag = G_REC_TO_BE_PROCESSED
                    AND eiai1.pk1_value = eia.pk1_value
                    AND NVL(eiai1.pk2_value,-1) = NVL(eia.pk2_value,-1)
                 UNION ALL
                 SELECT 1
                   FROM ego_item_associations eia2
                  WHERE eia2.inventory_item_id = msii.inventory_item_id
                    AND eia2.organization_id = msii.organization_id
                    AND eia2.data_level_id = eia.data_level_id
                    AND eia2.pk1_value = eia.pk1_value
                    AND NVL(eia2.pk2_value,-1) = NVL(eia.pk2_value,-1)
               );

Line: 2324

        INSERT INTO ego_item_associations_intf
        (
          BATCH_ID
          ,ORGANIZATION_ID
          ,ORGANIZATION_CODE
          ,ITEM_NUMBER
          ,INVENTORY_ITEM_ID
          ,PK1_VALUE
          ,SUPPLIER_NAME
          ,SUPPLIER_NUMBER
          ,PK2_VALUE
          ,SUPPLIER_SITE_NAME
          ,DATA_LEVEL_ID
          ,DATA_LEVEL_NAME
          ,PRIMARY_FLAG
          ,STATUS_CODE
          ,TRANSACTION_TYPE
          ,PROCESS_FLAG
          ,TRANSACTION_ID
          ,SOURCE_SYSTEM_REFERENCE
          ,SOURCE_SYSTEM_ID
          ,BUNDLE_ID
          ,REQUEST_ID
          ,CREATED_BY -- Bug 6459846
        )
        SELECT p_batch_id
               ,eiai1.organization_id
               ,eiai1.organization_code
               ,msibk.concatenated_segments
               ,msibk.inventory_item_id
               ,eiai1.pk1_value
               ,eiai1.supplier_name
               ,eiai1.supplier_number
               ,eiai1.pk2_value
               ,eiai1.supplier_site_name
               ,eiai1.data_level_id
               ,eiai1.data_level_name
               ,eiai1.primary_flag
               ,eiai1.status_code
               ,G_CREATE
               ,G_REC_TO_BE_PROCESSED
               ,mtl_system_items_interface_s.NEXTVAL
               ,NULL
               ,NULL
               ,NULL
               ,G_REQUEST_ID
               ,G_SKIP_SECURIY_CHECK -- Bug 6459846
          FROM ego_item_associations_intf eiai1
               ,mtl_system_items_b_kfv msibk
               ,mtl_parameters mp
         WHERE eiai1.inventory_item_id = msibk.style_item_id
           AND eiai1.organization_id = msibk.organization_id
           AND msibk.organization_id = mp.organization_id
          -- AND mp.organization_id = mp.master_organization_id
           AND eiai1.batch_id = p_batch_id
           AND eiai1.data_level_id = l_data_level_id
           AND eiai1.process_flag = G_REC_SUCCESS
           AND NOT EXISTS
               (
                 SELECT 1
                   FROM ego_item_associations_intf eiai2
                  WHERE eiai2.inventory_item_id = msibk.inventory_item_id
                    AND eiai2.organization_id = msibk.organization_id
                    AND eiai2.data_level_id = l_data_level_id
                    AND eiai2.batch_id = p_batch_id
                    AND eiai2.process_flag = G_REC_TO_BE_PROCESSED
                    AND eiai2.pk1_value = eiai1.pk1_value
                    AND NVL(eiai2.pk2_value,-1) = NVL(eiai1.pk2_value,-1)
                 UNION ALL
                 SELECT 1
                   FROM ego_item_associations eia2
                  WHERE eia2.inventory_item_id = msibk.inventory_item_id
                    AND eia2.organization_id = msibk.organization_id
                    AND eia2.data_level_id = eiai1.data_level_id
                    AND eia2.pk1_value = eiai1.pk1_value
                    AND NVL(eia2.pk2_value,-1) = NVL(eiai1.pk2_value,-1)
               );

Line: 2424

      UPDATE ego_item_associations_intf
         SET process_flag = G_REC_UNEXPECTED_ERROR
       WHERE batch_id = p_batch_id
         AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 2439

      UPDATE ego_item_associations_intf
         SET process_flag = G_REC_UNEXPECTED_ERROR
       WHERE batch_id = p_batch_id
         AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 2496

      SELECT component_item_id
        FROM bom_explosions_v;

Line: 2500

    SELECT msibk.inventory_item_id
           ,msibk.organization_id
      FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk
     WHERE msibk.concatenated_segments = eiai.item_number
       AND msibk.organization_id = eiai.organization_id
       AND eiai.batch_id = p_batch_id
       AND eiai.process_flag = G_REC_TO_BE_PROCESSED
       AND EXISTS
           (
             SELECT 1
               FROM bom_structures_b bsb
              WHERE bsb.assembly_item_id = msibk.inventory_item_id
                AND bsb.assembly_item_id = msibk.inventory_item_id
                AND bsb.organization_id = eiai.organization_id
                AND bsb.bill_sequence_id = bsb.common_bill_sequence_id
                AND bsb.alternate_bom_designator = ego_item_associations_pub.G_PACK_STR_NAME
           );

Line: 2568

              INSERT INTO ego_item_associations_intf
              (
                BATCH_ID
                ,SOURCE_SYSTEM_REFERENCE
                ,ITEM_NUMBER
                ,INVENTORY_ITEM_ID
                ,ORGANIZATION_ID
                ,ORGANIZATION_CODE
                ,PK1_VALUE
                ,SUPPLIER_NAME
                ,SUPPLIER_NUMBER
                ,PK2_VALUE
                ,SUPPLIER_SITE_NAME
                ,DATA_LEVEL_ID
                ,DATA_LEVEL_NAME
                ,PRIMARY_FLAG
                ,STATUS_CODE
                ,TRANSACTION_TYPE
                ,PROCESS_FLAG
                ,TRANSACTION_ID
                ,REQUEST_ID
                ,CREATED_BY -- Bug 6459846
              )
              SELECT p_batch_id
                     ,NULL
                     ,msibk.concatenated_segments
                     ,msibk.inventory_item_id
                     ,eiai.organization_id
                     ,eiai.organization_code
                     ,eiai.pk1_value
                     ,eiai.supplier_name
                     ,eiai.supplier_number
                     ,eiai.pk2_value
                     ,eiai.supplier_site_name
                     ,eiai.data_level_id
                     ,eiai.data_level_name
                     ,eiai.primary_flag
                     ,eiai.status_code
                     ,G_CREATE
                     ,G_REC_TO_BE_PROCESSED
                     ,mtl_system_items_interface_s.NEXTVAL
                     ,G_REQUEST_ID
                     ,G_SKIP_SECURIY_CHECK -- Bug 6459846
                FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk,
                     mtl_parameters mp
               WHERE eiai.inventory_item_id = l_pack_rec.inventory_item_id
                 AND eiai.organization_id = mp.organization_id
                 --AND mp.master_organization_id = mp.organization_id
                 AND eiai.batch_id = p_batch_id
                 AND eiai.process_flag = G_REC_TO_BE_PROCESSED
                 AND mp.organization_id = l_pack_rec.organization_id
                 AND eiai.data_level_id = l_data_level_id
                 AND msibk.inventory_item_id = l_item_rec.component_item_id
                 AND msibk.organization_id = mp.organization_id
                 AND NOT EXISTS
                     (
                       SELECT 1
                         FROM ego_item_associations_intf eiai1
                        WHERE eiai1.inventory_item_id = msibk.inventory_item_id
                          AND eiai1.organization_id = msibk.organization_id
                          AND eiai1.data_level_id = l_data_level_id
                          AND eiai1.batch_id = p_batch_id
                          AND eiai1.process_flag = G_REC_TO_BE_PROCESSED
                          AND eiai1.pk1_value = eiai.pk1_value
                          AND NVL(eiai1.pk2_value,-1) = NVL(eiai.pk2_value,-1)
                        UNION ALL
                       SELECT 1
                         FROM ego_item_associations eia2
                        WHERE eia2.inventory_item_id = msibk.inventory_item_id
                          AND eia2.organization_id = msibk.organization_id
                          AND eia2.data_level_id = l_data_level_id
                          AND eia2.pk1_value = eiai.pk1_value
                          AND NVL(eia2.pk2_value,-1) = NVL(eiai.pk2_value,-1)
                     );

Line: 2668

      UPDATE ego_item_associations_intf
         SET process_flag = G_REC_UNEXPECTED_ERROR
       WHERE batch_id = p_batch_id
         AND process_flag = G_REC_TO_BE_PROCESSED;

Line: 2683

      UPDATE ego_item_associations_intf
         SET process_flag = G_REC_UNEXPECTED_ERROR
       WHERE batch_id = p_batch_id
         AND process_flag = G_REC_TO_BE_PROCESSED;