APPS.EGO_GTIN_PVT SQL Statements

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

Line: 35

  SELECT 'X'
  INTO l_temp_char
  FROM EGO_FND_DF_COL_USGS_EXT attr_col
  WHERE attr_col.attr_id = p_attr_id
    AND attr_col.edit_in_hierarchy_code IN ('AP', 'LP');

Line: 58

    SELECT 'X' INTO l_temp
    FROM DUAL
    WHERE EXISTS
        (
        SELECT 1
        FROM MTL_CUSTOMER_ITEMS MCI,
            MTL_CUSTOMER_ITEM_XREFS MCIX,
            MTL_CROSS_REFERENCES MCR,
            MTL_SYSTEM_ITEMS_B MSI,
            MTL_PARAMETERS MP
        WHERE MSI.INVENTORY_ITEM_ID = p_inventory_item_id
            AND MSI.ORGANIZATION_ID = p_org_id
            AND MSI.INVENTORY_ITEM_ID = MCIX.INVENTORY_ITEM_ID
            AND MSI.ORGANIZATION_ID = MCIX.MASTER_ORGANIZATION_ID
            AND MCIX.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID
            AND MCI.ITEM_DEFINITION_LEVEL = 3
            AND MCI.CUSTOMER_CATEGORY_CODE = 'UCCNET'
            AND MCI.CUSTOMER_ID = p_customer_id
            AND MCI.ADDRESS_ID = p_address_id
            AND MCI.CUSTOMER_ITEM_NUMBER = MCR.CROSS_REFERENCE
            AND MCR.CROSS_REFERENCE_TYPE = 'GTIN'
            AND MCR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
            AND
            (
                MCR.ORGANIZATION_ID IS NULL
                OR MCR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
            )
            AND MCR.UOM_CODE = MSI.PRIMARY_UOM_CODE
            AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
            AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_id
        );

Line: 112

  SELECT cust_account_id, party_site_id INTO l_customer_id, l_party_site_id
  FROM hz_cust_acct_sites_all
  WHERE cust_acct_site_id = p_address_id;

Line: 116

  SELECT global_location_number INTO l_gln
  FROM hz_party_sites
  WHERE party_site_id = l_party_site_id;

Line: 164

    SELECT meaning INTO l_publication_status
    FROM fnd_lookups
    WHERE lookup_type = 'EGO_UCCNET_PUB_STATUS'
    AND lookup_code = l_publication_code;

Line: 192

  SELECT cust_account_id, party_site_id INTO l_customer_id, l_party_site_id
  FROM hz_cust_acct_sites_all
  WHERE cust_acct_site_id = p_address_id;

Line: 196

  SELECT global_location_number INTO l_gln
  FROM hz_party_sites
  WHERE party_site_id = l_party_site_id;

Line: 331

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE
      NOT EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
          AND ORGANIZATION_ID = p_org_id
          AND ADDRESS_ID = p_address_id
          AND PARENT_GTIN = 0
          AND EVENT_TYPE = 'PUBLICATION'
          AND EVENT_ACTION IN ( 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION', 'NEW_ITEM' )
          AND
          (
              DISPOSITION_CODE <> 'FAILED'
              OR DISPOSITION_CODE IS NULL
          )
      )
      AND NOT EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
          AND ORGANIZATION_ID = p_org_id
          AND PARENT_GTIN = 0
          AND EVENT_TYPE = 'PUBLICATION'
          AND EVENT_ACTION = 'DE_LIST'
          AND DISPOSITION_CODE <> 'FAILED'
      ); -- DELISTED

Line: 390

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE
      EXISTS
      (
      SELECT
          'Y'
      FROM EGO_UCCNET_EVENTS
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
          AND ORGANIZATION_ID = p_org_id
          AND ADDRESS_ID = p_address_id
          AND PARENT_GTIN = 0
          AND EVENT_TYPE = 'PUBLICATION'
          AND DISPOSITION_CODE IS NULL
      )
      AND NOT EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
          AND ORGANIZATION_ID = p_org_id
          AND PARENT_GTIN = 0
          AND EVENT_TYPE = 'PUBLICATION'
          AND EVENT_ACTION = 'DE_LIST'
          AND DISPOSITION_CODE <> 'FAILED'
      ); -- DELISTED

Line: 445

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS UE
      WHERE UE.EVENT_ROW_ID =
          (
          SELECT
              MAX(EVENT_ROW_ID)
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND ADDRESS_ID = p_address_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
              AND DISPOSITION_CODE <> 'FAILED'
          )
          AND NOT EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND ADDRESS_ID = p_address_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND
              (
                  (
                      DISPOSITION_CODE = 'REJECTED'
                      AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
                  )-- REJECTED
                  OR DISPOSITION_CODE IS NULL -- IN-PROGRESS
                  OR
                  (
                      EVENT_ACTION = 'WITHDRAW'
                      AND DISPOSITION_CODE <> 'FAILED'
                      AND UE.EVENT_ROW_ID < EVENT_ROW_ID
                  ) -- Withdrawn
              )
          )
          AND NOT EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION = 'DE_LIST'
              AND DISPOSITION_CODE <> 'FAILED'
          ) -- DELISTED
          AND
          (
              UE.CREATION_DATE >=
              (
              SELECT
                  NVL(MAX(EICA.LAST_UPDATE_DATE) , TO_DATE('01-01-1998', 'MM-DD-YYYY'))
              FROM EGO_ITEM_CUST_ATTRS_B EICA,
                  EGO_UCCNET_EVENTS EV2,
                  HZ_CUST_ACCT_SITES_ALL HCAS
              WHERE EV2.BATCH_ID = UE.BATCH_ID
                  AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
                  AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
                  AND EV2.ADDRESS_ID = UE.ADDRESS_ID
                  AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
                  AND EICA.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
                  AND EICA.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
                  AND EICA.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
              )
              AND UE.CREATION_DATE >=
              (
              SELECT
                  NVL(MAX(TP_NEUTRAL_UPDATE_DATE), TO_DATE('01-01-1998', 'MM-DD-YYYY'))
              FROM EGO_ITEM_GTN_ATTRS_B EGA2,
                  EGO_UCCNET_EVENTS EV2
              WHERE EV2.BATCH_ID = UE.BATCH_ID
                  AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
                  AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
                  AND EV2.ADDRESS_ID = UE.ADDRESS_ID
                  AND EGA2.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
                  AND EGA2.ORGANIZATION_ID = EV2.ORGANIZATION_ID
              )
              AND UE.CREATION_DATE >=
              (
              SELECT
                  (
                  CASE
                      WHEN(
                            Nvl( (Max(tl.LAST_UPDATE_DATE)), ( To_Date('01-01-1990','MM-DD-YYYY')) )
                                >=
                            Nvl( (Max(b.LAST_UPDATE_DATE)),  (To_Date('01-01-1990','MM-DD-YYYY')) )
                          )
                      THEN Nvl( (Max(tl.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
                      ELSE Nvl( (Max(b.LAST_UPDATE_DATE)),  (To_Date('01-01-1990','MM-DD-YYYY')) )
                  END
                  ) AS LAST_UPDATE_DATE
              FROM EGO_ITEM_TP_ATTRS_EXT_B b,
                   EGO_ITEM_TP_ATTRS_EXT_TL tl,
                   EGO_UCCNET_EVENTS EV2,
                   HZ_CUST_ACCT_SITES_ALL HCAS
              WHERE   EV2.BATCH_ID = UE.BATCH_ID
                  AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
                  AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
                  AND EV2.ADDRESS_ID = UE.ADDRESS_ID
                  AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
                  AND b.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
                  AND tl.LANGUAGE = USERENV('LANG')
                  AND b.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
                  AND b.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
                  AND b.EXTENSION_ID = tl.EXTENSION_ID
              )
          )
      );

Line: 594

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS UE
      WHERE UE.EVENT_ROW_ID =
          (
          SELECT
              MAX(EVENT_ROW_ID)
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND ADDRESS_ID = p_address_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
              AND DISPOSITION_CODE <> 'FAILED'
          )
          AND NOT EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND ADDRESS_ID = p_address_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND
              (
                  (
                      DISPOSITION_CODE = 'REJECTED'
                      AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
                  )-- REJECTED
                  OR DISPOSITION_CODE IS NULL -- IN-PROGRESS
                  OR
                  (
                      EVENT_ACTION = 'WITHDRAW'
                      AND DISPOSITION_CODE <> 'FAILED'
                      AND UE.EVENT_ROW_ID < EVENT_ROW_ID
                  ) -- Withdrawn
              )
          )
          AND NOT EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION = 'DE_LIST'
              AND DISPOSITION_CODE <> 'FAILED'
          ) -- DELISTED
          AND
          (
              UE.CREATION_DATE <
              (
              SELECT
                  NVL(MAX(EICA.LAST_UPDATE_DATE) , TO_DATE('01-01-1998', 'MM-DD-YYYY'))
              FROM EGO_ITEM_CUST_ATTRS_B EICA,
                  EGO_UCCNET_EVENTS EV2,
                  HZ_CUST_ACCT_SITES_ALL HCAS
              WHERE EV2.BATCH_ID = UE.BATCH_ID
                  AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
                  AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
                  AND EV2.ADDRESS_ID = UE.ADDRESS_ID
                  AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
                  AND EICA.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
                  AND EICA.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
                  AND EICA.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
              )
              OR UE.CREATION_DATE <
              (
              SELECT
                  NVL(MAX(TP_NEUTRAL_UPDATE_DATE), TO_DATE('01-01-1998', 'MM-DD-YYYY'))
              FROM EGO_ITEM_GTN_ATTRS_B EGA2,
                  EGO_UCCNET_EVENTS EV2
              WHERE EV2.BATCH_ID = UE.BATCH_ID
                  AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
                  AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
                  AND EV2.ADDRESS_ID = UE.ADDRESS_ID
                  AND EGA2.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
                  AND EGA2.ORGANIZATION_ID = EV2.ORGANIZATION_ID
              )
              OR UE.CREATION_DATE <
              (
              SELECT
                  (
                  CASE
                      WHEN(
                            Nvl( (Max(tl.LAST_UPDATE_DATE)), ( To_Date('01-01-1990','MM-DD-YYYY')) )
                                >=
                            Nvl( (Max(b.LAST_UPDATE_DATE)),  (To_Date('01-01-1990','MM-DD-YYYY')) )
                          )
                      THEN Nvl( (Max(tl.LAST_UPDATE_DATE)), (To_Date('01-01-1990','MM-DD-YYYY')) )
                      ELSE Nvl( (Max(b.LAST_UPDATE_DATE)),  (To_Date('01-01-1990','MM-DD-YYYY')) )
                  END
                  ) AS LAST_UPDATE_DATE
              FROM EGO_ITEM_TP_ATTRS_EXT_B b,
                   EGO_ITEM_TP_ATTRS_EXT_TL tl,
                   EGO_UCCNET_EVENTS EV2,
                   HZ_CUST_ACCT_SITES_ALL HCAS
              WHERE   EV2.BATCH_ID = UE.BATCH_ID
                  AND EV2.TOP_ITEM_ID = UE.INVENTORY_ITEM_ID
                  AND EV2.ORGANIZATION_ID = UE.ORGANIZATION_ID
                  AND EV2.ADDRESS_ID = UE.ADDRESS_ID
                  AND HCAS.CUST_ACCT_SITE_ID = UE.ADDRESS_ID
                  AND b.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
                  AND tl.LANGUAGE = USERENV('LANG')
                  AND b.INVENTORY_ITEM_ID = EV2.INVENTORY_ITEM_ID
                  AND b.MASTER_ORGANIZATION_ID = EV2.ORGANIZATION_ID
                  AND b.EXTENSION_ID = tl.EXTENSION_ID
              )
          )
      );

Line: 742

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS UE
      WHERE UE.EVENT_ROW_ID =
          (
          SELECT
              MAX(EVENT_ROW_ID)
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND ADDRESS_ID = p_address_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION = 'WITHDRAW'
              AND DISPOSITION_CODE <> 'FAILED'
          )
          AND NOT EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND ADDRESS_ID = p_address_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND
              (
                  (
                      DISPOSITION_CODE = 'REJECTED'
                      AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
                  )-- REJECTED
                  OR DISPOSITION_CODE IS NULL -- IN-PROGRESS
                  OR
                  (
                      EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
                      AND DISPOSITION_CODE <> 'FAILED'
                      AND UE.EVENT_ROW_ID < EVENT_ROW_ID
                  )
              )
          )
          AND NOT EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION = 'DE_LIST'
              AND DISPOSITION_CODE <> 'FAILED'
          ) -- DELISTED
      );

Line: 828

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM DUAL
      WHERE EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND ADDRESS_ID = p_address_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION IN ('INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION','NEW_ITEM')
              AND DISPOSITION_CODE = 'REJECTED'
          )
          AND NOT EXISTS
          (
          SELECT
              1
          FROM EGO_UCCNET_EVENTS
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND PARENT_GTIN = 0
              AND EVENT_TYPE = 'PUBLICATION'
              AND EVENT_ACTION = 'DE_LIST'
              AND DISPOSITION_CODE <> 'FAILED'
          ) -- DELISTED
      );

Line: 887

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM DUAL
      WHERE EXISTS
          (
          SELECT
              1
          FROM ego_uccnet_events
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND ORGANIZATION_ID = p_org_id
              AND EVENT_TYPE = 'PUBLICATION'
              AND PARENT_GTIN = 0
              AND EVENT_ACTION = 'DE_LIST'
              AND DISPOSITION_CODE <> 'FAILED'
          )
      );

Line: 932

    SELECT 'X' INTO l_temp
    FROM MTL_CROSS_REFERENCES MCR,
         MTL_SYSTEM_ITEMS_B MSI,
         MTL_PARAMETERS MP
    WHERE MCR.CROSS_REFERENCE_TYPE = 'GTIN'
        AND MCR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
        AND NVL(MCR.ORGANIZATION_ID, MSI.ORGANIZATION_ID) = MSI.ORGANIZATION_ID
        AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
        AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
        AND MCR.UOM_CODE = MSI.PRIMARY_UOM_CODE
        AND MSI.INVENTORY_ITEM_ID = p_inventory_item_id
        AND MSI.ORGANIZATION_ID = p_org_id
        AND NVL(GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y';

Line: 982

    select meaning into l_registration_status
    from fnd_lookups
    where lookup_type = 'EGO_UCCNET_STATUS'
    and lookup_code = l_registration_code;

Line: 1062

    SELECT
        'Y'
    INTO l_temp
    FROM DUAL
    WHERE NOT EXISTS
        (
        SELECT
            1
        FROM EGO_UCCNET_EVENTS
        WHERE INVENTORY_ITEM_ID = p_inventory_item_id
            AND ORGANIZATION_ID = p_org_id
            AND EVENT_TYPE = 'REGISTRATION'
            AND
            (
                DISPOSITION_CODE <> 'FAILED'
                OR DISPOSITION_CODE IS NULL
            )
        );

Line: 1101

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
          AND ORGANIZATION_ID = p_org_id
          AND EVENT_TYPE = 'REGISTRATION'
          AND DISPOSITION_CODE IS NULL
      );

Line: 1137

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS UE,
          EGO_ITEM_GTN_ATTRS_B GA
      WHERE UE.EVENT_ROW_ID =
          (
              SELECT
                  max(EVENT_ROW_ID)
              FROM EGO_UCCNET_EVENTS
              WHERE INVENTORY_ITEM_ID = p_inventory_item_id
                  AND ORGANIZATION_ID = p_org_id
                  AND EVENT_TYPE = 'REGISTRATION'
                  AND EVENT_ACTION IN ('ADD', 'CHANGE', 'CORRECT')
                  AND DISPOSITION_CODE <> 'FAILED'
          )
          AND NOT EXISTS
          (
              SELECT
                  1
              FROM EGO_UCCNET_EVENTS
              WHERE INVENTORY_ITEM_ID = p_inventory_item_id
                  AND ORGANIZATION_ID = p_org_id
                  AND EVENT_TYPE = 'REGISTRATION'
                  AND DISPOSITION_CODE IS NULL
          )
          AND UE.INVENTORY_ITEM_ID = GA.INVENTORY_ITEM_ID
          AND UE.ORGANIZATION_ID = GA.ORGANIZATION_ID
          AND (
                  GA.REGISTRATION_UPDATE_DATE <= UE.CREATION_DATE
                  OR GA.REGISTRATION_UPDATE_DATE IS NULL
              )
       );

Line: 1197

  SELECT
      'Y'
  INTO l_temp
  FROM DUAL
  WHERE EXISTS
      (
      SELECT
          1
      FROM EGO_UCCNET_EVENTS UE,
          EGO_ITEM_GTN_ATTRS_B GA
      WHERE UE.EVENT_ROW_ID =
          (
              SELECT
                  max(EVENT_ROW_ID)
              FROM EGO_UCCNET_EVENTS
              WHERE INVENTORY_ITEM_ID = p_inventory_item_id
                  AND ORGANIZATION_ID = p_org_id
                  AND EVENT_TYPE = 'REGISTRATION'
                  AND EVENT_ACTION IN ('ADD', 'CHANGE', 'CORRECT')
                  AND DISPOSITION_CODE <> 'FAILED'
          )
          AND NOT EXISTS
          (
              SELECT
                  1
              FROM EGO_UCCNET_EVENTS
              WHERE INVENTORY_ITEM_ID = p_inventory_item_id
                  AND ORGANIZATION_ID = p_org_id
                  AND EVENT_TYPE = 'REGISTRATION'
                  AND DISPOSITION_CODE IS NULL
          )
          AND UE.INVENTORY_ITEM_ID = GA.INVENTORY_ITEM_ID
          AND UE.ORGANIZATION_ID = GA.ORGANIZATION_ID
          AND GA.REGISTRATION_UPDATE_DATE > UE.CREATION_DATE
      );

Line: 1254

  SELECT 'Y' into l_temp
  FROM DUAL
  WHERE EXISTS(
    SELECT 1 FROM EGO_UCCNET_EVENTS
    WHERE inventory_item_id = p_inventory_item_id
    AND organization_id = p_org_id
    AND event_type = 'PUBLICATION'
    AND event_action IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
    AND NOT (disposition_code = 'FAILED'));

Line: 1286

   SELECT TRADE_ITEM_DESCRIPTOR
     INTO l_trade_unit_desc
     FROM MTL_SYSTEM_ITEMS_B
    WHERE INVENTORY_ITEM_ID = p_inventory_item_id
      AND ORGANIZATION_ID = p_org_id;

Line: 1305

* Written by Nisar to changed REGISTRATION_UPDATE_DATE when UDEX Catelog Category is updated.
*/
PROCEDURE PROCESS_CAT_ASSIGNMENT ( p_inventory_item_id NUMBER,
                                   p_organization_id   NUMBER) AS
BEGIN
  UPDATE EGO_ITEM_GTN_ATTRS_B
  SET REGISTRATION_UPDATE_DATE = SYSDATE
  WHERE INVENTORY_ITEM_ID = p_inventory_item_id
    AND ORGANIZATION_ID = p_organization_id;

Line: 1321

** Added by Devendra - for updation of REGISTRATION_UPDATE_DATE and TP_NEUTRAL_UPDATE_DATE
*/
PROCEDURE PROCESS_ATTRIBUTE_UPDATES (p_inventory_item_id NUMBER,
                                     p_organization_id   NUMBER,
                                     p_attribute_names   EGO_VARCHAR_TBL_TYPE,
                                     p_commit            VARCHAR2 := FND_API.G_FALSE,
                                     x_return_status     OUT NOCOPY VARCHAR2,
                                     x_msg_count         OUT NOCOPY NUMBER,
                                     x_msg_data          OUT NOCOPY VARCHAR2) AS

  l_reg_attr_updated       BOOLEAN :=  FALSE;

Line: 1332

  l_pub_attr_updated       BOOLEAN := FALSE;

Line: 1333

  l_tp_attr_updated        BOOLEAN := FALSE;

Line: 1334

  l_non_tp_attr_updated    BOOLEAN := FALSE;

Line: 1335

  l_update_last_upd_date   BOOLEAN := FALSE;

Line: 1400

    SELECT ACCT_SITE.PARTY_SITE_ID AS PARTY_SITE_ID
    FROM MTL_CUSTOMER_ITEMS MCI, MTL_CUSTOMER_ITEM_XREFS MCIX, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
    WHERE MCI.ITEM_DEFINITION_LEVEL = 3
      AND MCI.CUSTOMER_CATEGORY_CODE= 'UCCNET'
      AND MCI.CUSTOMER_ITEM_ID  = MCIX.CUSTOMER_ITEM_ID
      AND MCIX.PREFERENCE_NUMBER = 1
      AND MCI.CUSTOMER_ITEM_NUMBER = c_gtin
      AND MCIX.INVENTORY_ITEM_ID = p_inventory_item_id
      AND MCIX.MASTER_ORGANIZATION_ID = p_organization_id
      AND MCI.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
      AND MCI.ADDRESS_ID = ACCT_SITE.CUST_ACCT_SITE_ID;

Line: 1413

  write_debug_log('Entering EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));

Line: 1418

        l_reg_attr_updated := TRUE;

Line: 1423

    IF l_reg_attr_updated = TRUE THEN
      EXIT;

Line: 1436

        l_tp_attr_updated := TRUE;

Line: 1441

    IF l_tp_attr_updated = FALSE THEN
      l_non_tp_attr_updated := TRUE;

Line: 1447

  IF l_reg_attr_updated = TRUE THEN
    UPDATE EGO_ITEM_GTN_ATTRS_B
    SET REGISTRATION_UPDATE_DATE = SYSDATE,
        TP_NEUTRAL_UPDATE_DATE = SYSDATE
    WHERE INVENTORY_ITEM_ID = p_inventory_item_id
      AND ORGANIZATION_ID = p_organization_id;

Line: 1454

    IF p_attribute_names.COUNT > 0 AND l_non_tp_attr_updated THEN
      UPDATE EGO_ITEM_GTN_ATTRS_B
      SET TP_NEUTRAL_UPDATE_DATE = SYSDATE
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
        AND ORGANIZATION_ID = p_organization_id;

Line: 1459

    ELSIF p_attribute_names.COUNT > 0 AND (l_tp_attr_updated AND (NOT l_non_tp_attr_updated) ) THEN
      -- getting GTIN of item
      BEGIN
        SELECT CROSS_REFERENCE INTO l_gtin
        FROM MTL_CROSS_REFERENCES MCR, MTL_SYSTEM_ITEMS_B MSIB
        WHERE MCR.CROSS_REFERENCE_TYPE = 'GTIN'
          AND MCR.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
          AND MSIB.PRIMARY_UOM_CODE = MCR.UOM_CODE
          AND MSIB.ORGANIZATION_ID = p_organization_id
          AND MSIB.INVENTORY_ITEM_ID = p_inventory_item_id;

Line: 1475

          SELECT
            START_AVAILABILITY_DATE_TIME,
            END_AVAILABILITY_DATE_TIME,
            IS_TRADE_ITEM_A_DESPATCH_UNIT,
            IS_TRADE_ITEM_AN_INVOICE_UNIT,
            MIN_TRADE_ITEM_LIFE_ARR,
            ORDER_QUANTITY_MIN,
            ORDER_QUANTITY_MAX
          INTO
            l_start_availability_date_time,
            l_end_availability_date_time,
            l_is_trade_item_a_despatch_unt,
            l_is_trade_item_an_invoice_unt,
            l_min_trade_item_life_arr,
            l_order_quantity_min,
            l_order_quantity_max
          FROM EGO_ITEM_CUST_ATTRS_B
          WHERE INVENTORY_ITEM_ID = p_inventory_item_id
            AND MASTER_ORGANIZATION_ID = p_organization_id
            AND PARTY_SITE_ID = i.PARTY_SITE_ID;

Line: 1496

          l_update_last_upd_date := FALSE;

Line: 1500

              l_update_last_upd_date := TRUE;

Line: 1502

              l_update_last_upd_date := TRUE;

Line: 1504

              l_update_last_upd_date := TRUE;

Line: 1506

              l_update_last_upd_date := TRUE;

Line: 1508

              l_update_last_upd_date := TRUE;

Line: 1510

              l_update_last_upd_date := TRUE;

Line: 1512

              l_update_last_upd_date := TRUE;

Line: 1516

          IF l_update_last_upd_date THEN
            UPDATE EGO_ITEM_CUST_ATTRS_B
            SET LAST_UPDATE_DATE = SYSDATE
            WHERE INVENTORY_ITEM_ID = p_inventory_item_id
              AND MASTER_ORGANIZATION_ID = p_organization_id
              AND PARTY_SITE_ID = i.PARTY_SITE_ID;

Line: 1522

          END IF; --IF l_update_last_upd_date THEN

Line: 1524

          INSERT INTO EGO_ITEM_CUST_ATTRS_B
          (
            EXTENSION_ID,
            INVENTORY_ITEM_ID,
            MASTER_ORGANIZATION_ID,
            PARTY_SITE_ID,
            CREATED_BY,
            CREATION_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_DATE
          )
          VALUES
          (
            EGO_EXTFWK_S.NEXTVAL,
            p_inventory_item_id,
            p_organization_id,
            i.PARTY_SITE_ID,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE
          );

Line: 1548

    END IF; -- IF other attrs got updated

Line: 1549

  END IF; -- IF registration attrs got updated

Line: 1561

  write_debug_log('End - EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));

Line: 1568

END PROCESS_ATTRIBUTE_UPDATES;

Line: 1573

 * If any Extension GDSN attributes are updated, we update the TP_NEUTRAL_UPDATE_DATE or
 * LAST_UPDATE_DATE of EGO_ITEM_TP_ATTRS_EXT_B, depending upon whether the Attibute group
 * is TP-Dependant or not.
 */
PROCEDURE PROCESS_EXTN_ATTRIBUTE_UPDATES (p_inventory_item_id NUMBER,
                                          p_organization_id   NUMBER,
                                          p_attribute_names   EGO_VARCHAR_TBL_TYPE,
                                          p_attr_group_name   VARCHAR2,
                                          p_commit            VARCHAR2 := FND_API.G_FALSE,
                                          x_return_status     OUT NOCOPY VARCHAR2,
                                          x_msg_count         OUT NOCOPY NUMBER,
                                          x_msg_data          OUT NOCOPY VARCHAR2) AS
  x_error_message          VARCHAR2(2000);

Line: 1588

  l_select_columns         VARCHAR2(32000);

Line: 1596

    SELECT PARTY_SITE_ID
    FROM EGO_UCCNET_EVENTS
    WHERE INVENTORY_ITEM_ID = p_inventory_item_id
      AND ORGANIZATION_ID = p_organization_id
      AND EVENT_TYPE = 'PUBLICATION'
      AND EVENT_ACTION IN ( 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION', 'NEW_ITEM' )
      AND
         (
           DISPOSITION_CODE <> 'FAILED'
           OR DISPOSITION_CODE IS NULL
         )
    GROUP BY PARTY_SITE_ID;

Line: 1609

  write_debug_log('Entering EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));

Line: 1613

    SELECT AGV_NAME, ATTR_GROUP_ID INTO l_view_name, l_attr_group_id
    FROM EGO_FND_DSC_FLX_CTX_EXT
    WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEM_TP_EXT_ATTRS'
      AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
      AND APPLICATION_ID = 431;

Line: 1619

    write_debug_log('Attribute group does not belongs to EGO_ITEM_TP_EXT_ATTRS, so updating TP_NEUTRAL_UPDATE_DATE');

Line: 1622

      UPDATE EGO_ITEM_GTN_ATTRS_B
      SET TP_NEUTRAL_UPDATE_DATE = SYSDATE
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
        AND ORGANIZATION_ID = p_organization_id;

Line: 1629

    write_debug_log('Exiting EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));

Line: 1637

    l_select_columns := l_select_columns || UPPER(p_attribute_names(i)) || '||';

Line: 1640

  l_select_columns := RTRIM(l_select_columns, '||');

Line: 1642

  l_sql := ' SELECT '||l_select_columns||' ,EXTENSION_ID FROM '||l_view_name||
           ' WHERE INVENTORY_ITEM_ID = :1 AND MASTER_ORGANIZATION_ID = :2 AND PARTY_SITE_ID = :3 AND ROWNUM = 1';

Line: 1654

        UPDATE EGO_ITEM_TP_ATTRS_EXT_B
        SET LAST_UPDATE_DATE = SYSDATE
        WHERE EXTENSION_ID = l_extn_id;

Line: 1660

      write_debug_log('No Data Found - inserting');

Line: 1661

      SELECT EGO_EXTFWK_S.NEXTVAL INTO l_ext_seq_val FROM DUAL;

Line: 1663

      INSERT INTO EGO_ITEM_TP_ATTRS_EXT_B
      (
        EXTENSION_ID,
        INVENTORY_ITEM_ID,
        MASTER_ORGANIZATION_ID,
        PARTY_SITE_ID,
        ATTR_GROUP_ID,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE
      )
      VALUES
      (
        l_ext_seq_val,
        p_inventory_item_id,
        p_organization_id,
        i.PARTY_SITE_ID,
        l_attr_group_id,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.USER_ID,
        SYSDATE
      );

Line: 1688

      INSERT INTO EGO_ITEM_TP_ATTRS_EXT_TL
      (
        EXTENSION_ID,
        INVENTORY_ITEM_ID,
        MASTER_ORGANIZATION_ID,
        PARTY_SITE_ID,
        ATTR_GROUP_ID,
        LANGUAGE,
        SOURCE_LANG,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE
      )
      SELECT
        l_ext_seq_val,
        p_inventory_item_id,
        p_organization_id,
        i.PARTY_SITE_ID,
        l_attr_group_id,
        L.LANGUAGE_CODE,
        USERENV('LANG'),
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.USER_ID,
        SYSDATE
      FROM FND_LANGUAGES L
      WHERE L.INSTALLED_FLAG IN ('I', 'B')
        AND NOT EXISTS
             (SELECT NULL
              FROM EGO_ITEM_TP_ATTRS_EXT_TL T
              WHERE T.EXTENSION_ID = l_ext_seq_val
                AND T.LANGUAGE = L.LANGUAGE_CODE);

Line: 1733

  write_debug_log('End - EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES ... Date and Time - '||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));

Line: 1738

END PROCESS_EXTN_ATTRIBUTE_UPDATES;

Line: 1742

*  This procedure will validate the MSI attributes for UCCnet and will call PROCESS_ATTRIBUTE_UPDATES
*/
PROCEDURE PROCESS_UCCNET_ATTRIBUTES (P_Prog_AppId  NUMBER  DEFAULT -1,
                                     P_Prog_Id     NUMBER  DEFAULT -1,
                                     P_Request_Id  NUMBER  DEFAULT -1,
                                     P_User_Id     NUMBER  DEFAULT -1,
                                     P_Login_Id    NUMBER  DEFAULT -1,
                                     P_Set_id      NUMBER  DEFAULT -999,
                                     P_Suppress_Rollup VARCHAR2 DEFAULT 'N'
                                    )
IS
  CURSOR c_upated_items IS
    SELECT
      inventory_item_id,
      organization_id,
      unit_length,
      unit_weight,
      unit_width,
      unit_height,
      unit_volume,
      dimension_uom_code,
      list_price_per_unit,
      shippable_item_flag,
      invoiceable_item_flag,
      customer_order_enabled_flag,
      description,
      rowid,
      transaction_id,
      weight_uom_code,
      volume_uom_code,
      shelf_life_days,
      trade_item_descriptor
    FROM MTL_SYSTEM_ITEMS_INTERFACE
    WHERE (SET_PROCESS_ID = p_set_id OR SET_PROCESS_ID = p_set_id + 1000000000000)
      AND TRANSACTION_TYPE IN ('UPDATE', 'AUTO_CHILD')
      AND PROCESS_FLAG = 4;

Line: 1825

      SELECT GDSN_OUTBOUND_ENABLED_FLAG INTO l_gdsn_outbound_enabled_flag
      FROM MTL_SYSTEM_ITEMS_B
      WHERE INVENTORY_ITEM_ID = i.inventory_item_id
        AND ORGANIZATION_ID = i.organization_id;

Line: 1840

        SELECT
          msib.ROWID,
          msib.unit_length,
          msib.unit_weight,
          msib.unit_width,
          msib.unit_height,
          msib.unit_volume,
          msib.dimension_uom_code,
          msib.list_price_per_unit,
          msib.shippable_item_flag ,
          msib.invoiceable_item_flag,
          msib.customer_order_enabled_flag,
          msit.description,
          msib.weight_uom_code,
          msib.volume_uom_code,
          msib.shelf_life_days,
          msib.trade_item_descriptor
        INTO
          l_msib_rowid,
          l_unit_length,
          l_unit_weight,
          l_unit_width,
          l_unit_height,
          l_unit_volume,
          l_dimension_uom_code,
          l_list_price_per_unit,
          l_shippable_item_flag,
          l_invoiceable_item_flag,
          l_customer_order_enabled_flag,
          l_description,
          l_weight_uom_code,
          l_volume_uom_code,
          l_shelf_life_days,
          l_trade_item_desc
       FROM MTL_SYSTEM_ITEMS_B msib, MTL_SYSTEM_ITEMS_TL msit
       WHERE msib.INVENTORY_ITEM_ID = i.inventory_item_id
         AND msib.ORGANIZATION_ID = i.organization_id
         AND msib.INVENTORY_ITEM_ID = msit.INVENTORY_ITEM_ID
         AND msib.ORGANIZATION_ID = msit.ORGANIZATION_ID
         AND msit.LANGUAGE = USERENV('LANG');

Line: 2045

            UPDATE MTL_SYSTEM_ITEMS_B
            SET
              UNIT_WEIGHT = i.unit_weight,
              CUSTOMER_ORDER_ENABLED_FLAG = i.customer_order_enabled_flag,
              WEIGHT_UOM_CODE = i.weight_uom_code -- Bug: 3874653
            WHERE ROWID = l_msib_rowid;

Line: 2061

            SELECT item_catalog_group_id
              INTO l_item_catalog_group_id
              FROM mtl_system_items_b
             WHERE inventory_item_id = i.inventory_item_id
               AND organization_id = i.organization_id;

Line: 2079

              , p_transaction_type => 'UPDATE'
              , x_error_message => l_error_message
              );

Line: 2085

          write_debug_log('Before calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES from IOI ...');

Line: 2086

          EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES(
              p_inventory_item_id => i.inventory_item_id,
              p_organization_id   => i.organization_id,
              p_attribute_names   => l_attribute_names,
              p_commit            => FND_API.G_FALSE,
              x_return_status     => l_return_status,
              x_msg_count         => l_msg_count,
              x_msg_data          => l_msg_text);

Line: 2095

          write_debug_log('After calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES from IOI ... return_status, error = '||l_return_status||' , '||l_msg_text);

Line: 2097

            UPDATE MTL_SYSTEM_ITEMS_INTERFACE
            SET process_flag = 3
            WHERE rowid = i.rowid;

Line: 2124

** Added by Devendra - This method will update the REGISTRATION_UPDATE_DATE and TP_NEUTRAL_UPDATE_DATE
**  for an item. If parameter p_update_reg is supplied as 'Y' then REGISTRATION_UPDATE_DATE and
**  TP_NEUTRAL_UPDATE_DATE will be updated else only TP_NEUTRAL_UPDATE_DATE will be updated.
*/
PROCEDURE UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id NUMBER,
                                       p_organization_id   NUMBER,
                                       p_update_reg        VARCHAR2 := 'N',
                                       p_commit            VARCHAR2 := FND_API.G_FALSE,
                                       x_return_status     OUT NOCOPY VARCHAR2,
                                       x_msg_count         OUT NOCOPY NUMBER,
                                       x_msg_data          OUT NOCOPY VARCHAR2)
IS
BEGIN
  IF NVL(p_update_reg, 'N') = 'Y' THEN
    UPDATE EGO_ITEM_GTN_ATTRS_B
    SET REGISTRATION_UPDATE_DATE = SYSDATE,
        TP_NEUTRAL_UPDATE_DATE = SYSDATE
    WHERE INVENTORY_ITEM_ID = p_inventory_item_id
      AND ORGANIZATION_ID = p_organization_id;

Line: 2144

    UPDATE EGO_ITEM_GTN_ATTRS_B
    SET TP_NEUTRAL_UPDATE_DATE = SYSDATE
    WHERE INVENTORY_ITEM_ID = p_inventory_item_id
      AND ORGANIZATION_ID = p_organization_id;

Line: 2161

END UPDATE_REG_PUB_UPDATE_DATES;

Line: 2411

PROCEDURE Update_Attribute
        ( p_inventory_item_id             IN NUMBER
        , p_organization_id               IN NUMBER
        , p_attr_name                     IN VARCHAR2
        , p_attr_group_type               IN VARCHAR2 DEFAULT NULL
        , p_attr_group_name               IN VARCHAR2 DEFAULT NULL
        , p_attr_new_value_str            IN VARCHAR2 DEFAULT NULL
        , p_attr_new_value_num            IN NUMBER   DEFAULT NULL
        , p_attr_new_value_date           IN DATE     DEFAULT NULL
        , p_attr_new_value_uom            IN VARCHAR2 DEFAULT NULL
        , p_debug_level                   IN NUMBER   DEFAULT 0
        , x_return_status                 OUT NOCOPY VARCHAR2
        , x_errorcode                     OUT NOCOPY NUMBER
        , x_msg_count                     OUT NOCOPY NUMBER
        , x_msg_data                      OUT NOCOPY VARCHAR2
        )
  IS
    CURSOR c_msi_old_values IS
      SELECT
        inventory_item_id,
        organization_id,
        segment1,
        unit_weight,
        weight_uom_code
      FROM MTL_SYSTEM_ITEMS_B
      WHERE inventory_item_id = p_inventory_item_id
        AND organization_id = p_organization_id;

Line: 2447

      SELECT data_type_code
           , database_column
        FROM ego_attrs_v v
       WHERE attr_group_type = p_attr_group_type
         AND attr_group_name = p_attr_group_name
         AND attr_name = p_attr_name
         AND application_id = 431;

Line: 2471

      SELECT item_catalog_group_id
        FROM mtl_system_items_b
       WHERE inventory_item_id = p_inventory_item_id
         AND organization_id = p_organization_id;

Line: 2486

    l_api_name := 'Update_Attribute';

Line: 2501

          'SELECT '||i.database_column||
          '  FROM ego_item_gtn_attrs_vl '||
          ' WHERE inventory_item_id = :1 '||
          '   AND organization_id = :2';

Line: 2601

            write_debug_log(l_api_name||': calling Process_Attribute_Updates');

Line: 2603

            Process_Attribute_Updates
              ( p_inventory_item_id => p_inventory_item_id
              , p_organization_id   => p_organization_id
              , p_attribute_names   => l_attribute_names
              , x_return_status     => x_return_status
              , x_msg_count         => x_msg_count
              , x_msg_data          => x_msg_data
              );

Line: 2612

            write_debug_log(l_api_name||': called Process_Attribute_Updates with ret='||x_return_status);

Line: 2682

                , p_transaction_type  => EGO_ITEM_PUB.G_TTYPE_UPDATE
                , p_inventory_item_id => p_inventory_item_id
                , p_organization_id   => p_organization_id
                , p_item_number       => c2.segment1
                , p_weight_uom_code   => l_weight_uom_code
                , p_unit_weight       => l_unit_weight
                , p_process_control   => 'SUPPRESS_ROLLUP'
                , x_inventory_item_id => l_inventory_item_id
                , x_organization_id   => l_organization_id
                , x_return_status     => x_return_status
                , x_msg_count         => x_msg_count
                , x_msg_data          => x_msg_data
                );

Line: 2743

END Update_Attribute;

Line: 2747

PROCEDURE Update_Attributes
        ( p_pk_column_name_value_pairs    IN EGO_COL_NAME_VALUE_PAIR_ARRAY
        , p_class_code_name_value_pairs   IN EGO_COL_NAME_VALUE_PAIR_ARRAY
        , p_data_level_name_value_pairs   IN EGO_COL_NAME_VALUE_PAIR_ARRAY
        , p_attr_diffs                    IN EGO_USER_ATTR_DIFF_TABLE
        , p_transaction_type              IN VARCHAR2
        , p_attr_group_id                 IN NUMBER DEFAULT NULL
        , x_error_message                 OUT NOCOPY VARCHAR2
        )
  IS

    l_object_name_table_index NUMBER;

Line: 2763

      EGO_USER_ATTRS_DATA_PVT.Update_Attributes
        ( p_pk_column_name_value_pairs
        , p_class_code_name_value_pairs
        , 'ITEM_LEVEL'
        , p_data_level_name_value_pairs
        , p_attr_diffs
        , p_transaction_type
        , p_attr_group_id
        , x_error_message);

Line: 2773

END Update_Attributes;

Line: 2806

    SELECT item_catalog_group_id INTO l_item_catalog_group_id
    FROM mtl_system_items_b
    WHERE inventory_item_id = p_inventory_item_id
    AND organization_id = p_org_id;

Line: 2844

    l_gtin_dml_str := 'UPDATE ego_item_gtn_attrs_b SET ';

Line: 2849

/* Commented by snelloli as we have to update with empty string for 'N'
TOP_GTIN != '' does not return any rows and the update fails
 ||
      ' AND (TOP_GTIN IS NULL OR TOP_GTIN <> ''' || p_top_gtin_flag || ''')';

Line: 2876

     SELECT 'X' into l_temp
     FROM
        EGO_OBJ_AG_ASSOCS_B A,
        EGO_FND_DSC_FLX_CTX_EXT EXT
     WHERE
          A.ATTR_GROUP_ID = EXT.ATTR_GROUP_ID
      AND EXT.APPLICATION_ID = p_application_id
      and EXT.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
      and EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
      and A.CLASSIFICATION_CODE IN
             ( SELECT ITEM_CATALOG_GROUP_ID
                 FROM MTL_ITEM_CATALOG_GROUPS_B
                 CONNECT BY PRIOR  PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
                 START WITH  ITEM_CATALOG_GROUP_ID = (select item_catalog_group_id
                                                      from mtl_system_items_b
                                                      where inventory_item_id = p_inventory_item_id
                                                      and organization_id = p_organization_id) );

Line: 2977

    select 1
    into  l_zeroeth_assoc_exists
    from EGO_FND_DSC_FLX_CTX_EXT ext,
         EGO_OBJ_AG_ASSOCS_B assocs
    where assocs.classification_code = '-1'
      and assocs.object_id = (select object_id from fnd_objects where obj_name  = 'EGO_ITEM')
      and assocs.attr_group_id =ext.attr_group_id
      and ext.DESCRIPTIVE_FLEXFIELD_NAME in ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
      and ext.application_id = G_EGO_APP_ID
      and rownum<2;

Line: 3177

    SELECT mi.item_catalog_group_id
      BULK COLLECT INTO l_CatalogGroupIds
    FROM mtl_item_catalog_groups_b mi
    WHERE mi.parent_catalog_group_id IS NULL
      AND NOT EXISTS
           (SELECT oa.attr_group_id
            FROM ego_obj_ag_assocs_b oa,
                 ego_attr_groups_v eag
            WHERE oa.classification_code = mi.item_catalog_group_id
              AND oa.attr_group_id = eag.attr_group_id
              AND eag.attr_group_type IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS'));

Line: 3195

    SELECT ATTR_GROUP_ID, DESCRIPTIVE_FLEX_CONTEXT_CODE, DESCRIPTIVE_FLEXFIELD_NAME
      BULK COLLECT INTO l_TmpAttrGrpIds, l_TmpAttrGrpNames, l_TmpAttrGrpTypes
    FROM EGO_FND_DSC_FLX_CTX_EXT
    -- Attribute Group Type
    WHERE DESCRIPTIVE_FLEXFIELD_NAME in ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
      AND application_id = G_EGO_APP_ID
      -- Attribute Group Name
      AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN
                                         (  'Trade_Item_Description'
                                          , 'Trade_Item_Measurements'
                                          , 'Temperature_Information'
                                          , 'Trade_Item_Marking'
                                          , 'Gtin_Unit_Indicator'
                                          , 'Uccnet_Size_Description'
                                          , 'Material_Safety_Data'
                                          , 'Gtin_Color_Description'
                                          , 'Manufacturing_Info'
                                          , 'Country_Of_Origin'
                                          , 'Order_Information'
                                          , 'Price_Information'
                                          , 'Price_Date_Information'
                                          , 'Date_Information'
                                          , 'Packaging_Marking'
                                          , 'Trade_Item_Hierarchy'
                                          , 'Bar_Code'
                                          , 'Handling_Information'
                                          , 'Hazardous_Information'
                                          , 'Size_Description' -- Bug: 4027782
                                          , 'Delivery_Method_Indicator' -- Bug: 4027782
                                          , 'Security_Tag'
                                         --  , 'Uccnet_Hardlines'
                                         --  , 'TRADE_ITEM_HARMN_SYS_IDENT'
                                         --  , 'FMCG_MARKING'
                                         --  , 'FMCG_Measurements'
                                         --  , 'FMCG_Identification'
                                         );

Line: 3260

          SELECT max(sequence) INTO  l_page_sequence
          FROM EGO_PAGES_B
          WHERE object_id = l_object_id
            AND classification_code = l_classification_code;

Line: 3276

          INSERT INTO EGO_PAGES_B
          (
            PAGE_ID
           ,OBJECT_ID
           ,CLASSIFICATION_CODE
           ,DATA_LEVEL
           ,INTERNAL_NAME
           ,SEQUENCE
           ,CREATION_DATE
           ,CREATED_BY
           ,LAST_UPDATE_DATE
           ,LAST_UPDATED_BY
           ,LAST_UPDATE_LOGIN
          )
          SELECT EGO_PAGES_S.NEXTVAL
                ,l_object_id
                ,l_classification_code
                ,G_ITEM_LEVEL
                ,l_PageNames(i)
                ,l_PageSeqs(i) + l_page_sequence
                ,l_sysdate
                ,l_current_user_id
                ,l_sysdate
                ,l_current_user_id
                ,L_current_login_id
            FROM DUAL
           WHERE NOT EXISTS (
                  SELECT *
                    FROM EGO_PAGES_V
                   WHERE CLASSIFICATION_CODE = l_classification_code
                     AND INTERNAL_NAME = l_PageNames(i)
                 );

Line: 3313

        SELECT page_id, internal_name
        BULK COLLECT INTO l_TmpPageIds, l_TmpPageNames
        FROM EGO_PAGES_B
        WHERE OBJECT_ID = l_object_id
          AND CLASSIFICATION_CODE = l_classification_code
          AND SEQUENCE > l_page_sequence -- Need to get only newly inserted rows
        ORDER BY SEQUENCE; -- Make sure to get it in the order in which have been inserted

Line: 3321

        l_PageIds.DELETE;

Line: 3341

            SELECT message_text
              INTO l_mssg_text
            FROM fnd_new_messages
            WHERE message_name = l_PageNames(i)
              AND application_id = G_EGO_APP_ID
              AND language_code = USERENV('LANG');

Line: 3348

            INSERT INTO EGO_PAGES_TL
            (
              PAGE_ID
             ,DISPLAY_NAME
             ,LANGUAGE
             ,SOURCE_LANG
             ,CREATION_DATE
             ,CREATED_BY
             ,LAST_UPDATE_DATE
             ,LAST_UPDATED_BY
             ,LAST_UPDATE_LOGIN
            )
            SELECT
              l_PageIds(i)
             ,l_mssg_text
             ,L.LANGUAGE_CODE
             ,USERENV('LANG')
             ,l_Sysdate
             ,l_current_user_id
             ,l_Sysdate
             ,l_current_user_id
             ,l_current_login_id
            FROM FND_LANGUAGES L
            WHERE L.INSTALLED_FLAG in ('I', 'B');

Line: 3380

          INSERT INTO EGO_OBJ_AG_ASSOCS_B
          (
            ASSOCIATION_ID
           ,OBJECT_ID
           ,CLASSIFICATION_CODE
           ,DATA_LEVEL
           ,ATTR_GROUP_ID
           ,ENABLED_FLAG
           ,VIEW_PRIVILEGE_ID
           ,EDIT_PRIVILEGE_ID
           ,CREATION_DATE
           ,CREATED_BY
           ,LAST_UPDATE_DATE
           ,LAST_UPDATED_BY
           ,LAST_UPDATE_LOGIN
          )
          VALUES
          (
            EGO_ASSOCS_S.NEXTVAL
           ,l_object_id
           ,l_classification_code
           ,G_ITEM_LEVEL
           ,l_AttrGroupIds(i)
           ,'Y'
           ,to_number(NULL)
           ,to_number(NULL)
           ,l_Sysdate
           ,l_current_user_id
           ,l_Sysdate
           ,l_current_user_id
           ,l_current_login_id
          );

Line: 3427

        SELECT ASSOCIATION_ID -- , ATTR_GROUP_ID
        BULK COLLECT INTO l_AssocIds --, l_AttrGroupIds
        FROM EGO_OBJ_AG_ASSOCS_B
        WHERE OBJECT_ID = l_object_id
          AND CLASSIFICATION_CODE = l_classification_code
          and ATTR_GROUP_ID in (SELECT ATTR_GROUP_ID  FROM EGO_FND_DSC_FLX_CTX_EXT
                                WHERE DESCRIPTIVE_FLEXFIELD_NAME in ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
                                 AND application_id = G_EGO_APP_ID
                                 AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN
                                     (  'Trade_Item_Description'
                                      , 'Trade_Item_Measurements'
                                      , 'Temperature_Information'
                                      , 'Trade_Item_Marking'
                                      , 'Gtin_Unit_Indicator'
                                      , 'Uccnet_Size_Description'
                                      , 'Material_Safety_Data'
                                      , 'Gtin_Color_Description'
                                      , 'Manufacturing_Info'
                                      , 'Country_Of_Origin'
                                      , 'Order_Information'
                                      , 'Price_Information'
                                      , 'Price_Date_Information'
                                      , 'Date_Information'
                                      , 'Packaging_Marking'
                                      , 'Trade_Item_Hierarchy'
                                      , 'Bar_Code'
                                      , 'Handling_Information'
                                      , 'Hazardous_Information'
                                      , 'Security_Tag'
                                      --, 'Uccnet_Hardlines'
                                      --, 'TRADE_ITEM_HARMN_SYS_IDENT'
                                      --, 'FMCG_MARKING'
                                      --, 'FMCG_Measurements'
                                      --, 'FMCG_Identification'
                                      ))
        ORDER BY ASSOCIATION_ID;

Line: 3481

          INSERT INTO EGO_PAGE_ENTRIES_B
          (
            PAGE_ID
           ,ASSOCIATION_ID
           ,SEQUENCE
           ,CLASSIFICATION_CODE
           ,CREATION_DATE
           ,CREATED_BY
           ,LAST_UPDATE_DATE
           ,LAST_UPDATED_BY
           ,LAST_UPDATE_LOGIN
          )
          VALUES
          (
            l_AssocPageIds(i)
           ,l_AssocIds(i)
           ,l_EntrySeqs(i)
           ,l_classification_code
           ,l_Sysdate
           ,l_current_user_id
           ,l_Sysdate
           ,l_current_user_id
           ,l_current_login_id
          );

Line: 3526

   SELECT 'Y' INTO l_result
   FROM DUAL
   WHERE NOT EXISTS
   (
   SELECT
       1
   FROM EGO_UCCNET_EVENTS EV1
   WHERE INVENTORY_ITEM_ID = p_inventory_item_id
       AND ORGANIZATION_ID = p_org_id
       AND ADDRESS_ID = p_address_id
       AND EVENT_TYPE = 'PUBLICATION'
       AND PARENT_GTIN = 0
       AND
       (
           DISPOSITION_CODE IS NULL
           OR
           (
               EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
               AND DISPOSITION_CODE = 'REJECTED'
           )
           OR
           (
               EVENT_ACTION = 'DE_LIST'
               AND DISPOSITION_CODE <> 'FAILED'
           )
           OR
           (
               EVENT_ACTION = 'WITHDRAW'
               AND DISPOSITION_CODE <> 'FAILED'
               AND NOT EXISTS
               (
               SELECT
                   1
               FROM EGO_UCCNET_EVENTS
               WHERE INVENTORY_ITEM_ID = p_inventory_item_id
                   AND ORGANIZATION_ID = p_org_id
                   AND ADDRESS_ID = p_address_id
                   AND EVENT_TYPE = 'PUBLICATION'
                   AND PARENT_GTIN = 0
                   AND EVENT_ROW_ID > EV1.EVENT_ROW_ID
                   AND DISPOSITION_CODE <> 'FALIED'
               )
           )
       )
   )
   AND EXISTS
   (
   SELECT
       1
   FROM EGO_ITEM_GTN_ATTRS_B EGA,
       BOM_EXPLOSIONS_ALL_V EXPL
   WHERE EXPL.GROUP_ID = p_explode_group_id
       AND EXPL.TOP_ITEM_ID = p_inventory_item_id
       AND EXPL.ORGANIZATION_ID = EGA.ORGANIZATION_ID
       AND EXPL.COMPONENT_ITEM_ID = EGA.INVENTORY_ITEM_ID
       AND TP_NEUTRAL_UPDATE_DATE >
       (
       SELECT
           MAX(CREATION_DATE)
       FROM EGO_UCCNET_EVENTS
       WHERE INVENTORY_ITEM_ID = p_inventory_item_id
           AND ORGANIZATION_ID = p_org_id
           AND ADDRESS_ID = p_address_id
           AND EVENT_TYPE = 'PUBLICATION'
           AND PARENT_GTIN = 0
           AND EVENT_ACTION IN ('NEW_ITEM', 'INITIAL_LOAD', 'DATA_CHANGE', 'CORRECTION')
           AND DISPOSITION_CODE IN ('PROCESSED', 'ACCEPTED', 'REVIEW', 'SYNCHRONIZED')
       )
   );

Line: 3610

    SELECT TL.FORM_LEFT_PROMPT ATTR_DISPLAY_NAME
    INTO l_disp_name
    FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL ,FND_DESCR_FLEX_COL_USAGE_TL TL
    WHERE FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
      AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
      AND FL_COL.APPLICATION_ID = 431
      AND FL_COL.END_USER_COLUMN_NAME = p_attr_name
      AND FL_COL.APPLICATION_ID = TL.APPLICATION_ID
      AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME
      AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
      AND FL_COL.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
      AND TL.LANGUAGE = USERENV('LANG');

Line: 3640

    SELECT PT.PAGE_ID, PT.DISPLAY_NAME
    INTO l_page_id, l_page_disp_name
    FROM EGO_FND_DSC_FLX_CTX_EXT EXT, EGO_OBJ_AG_ASSOCS_B ASOC, EGO_PAGE_ENTRIES_B PGE, EGO_PAGES_TL PT
    WHERE EXT.ATTR_GROUP_ID = ASOC.ATTR_GROUP_ID
      AND EXT.APPLICATION_ID = 431
      AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
      AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
      AND ASOC.OBJECT_ID = (SELECT OBJECT_ID FROM FND_OBJECTS WHERE OBJ_NAME = 'EGO_ITEM')
      AND ASOC.CLASSIFICATION_CODE = p_catalog_group_id
      AND ASOC.ASSOCIATION_ID = PGE.ASSOCIATION_ID
      AND ASOC.CLASSIFICATION_CODE = PGE.CLASSIFICATION_CODE
      AND PGE.PAGE_ID = PT.PAGE_ID
      AND PT.LANGUAGE = USERENV('LANG')
      AND ROWNUM = 1;

Line: 3710

      SELECT ITEM_CATALOG_GROUP_ID INTO l_catalog_group_id
      FROM MTL_SYSTEM_ITEMS_B
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
        AND ORGANIZATION_ID = p_organization_id;

Line: 3720

        SELECT *
        FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );

Line: 3734

        SELECT PARTY_SITE_ID INTO l_party_site_id
        FROM HZ_CUST_ACCT_SITES_ALL
        WHERE CUST_ACCT_SITE_ID = p_address_id;

Line: 3745

            SELECT *
            FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );

Line: 3755

            SELECT *
            FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );

Line: 3764

      l_sql := ' SELECT DEPOSIT_VALUE_EFFECTIVE_DATE, DEPOSIT_VALUE_END_DATE ' ||
               ' FROM EGO_SBDH_DEP_VAL_DATE_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 3798

      l_sql := ' SELECT CAMPAIGN_START_DATE, CAMPAIGN_END_DATE ' ||
               ' FROM EGO_SBDH_CAMPAIGN_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 3832

      l_sql := ' SELECT SEASONAL_AVL_START_DATE, SEASONAL_AVL_END_DATE ' ||
               ' FROM EGO_SBDH_SEASON_AVL_DATE_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 3870

      l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX ' ||
               ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 3916

      l_sql := ' SELECT sbdh.PEG_HOLE_NUMBER, core.PEG_VERTICAL, core.PEG_HORIZONTAL ' ||
               ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV sbdh, EGO_ITEM_GTN_ATTRS_B core ' ||
               ' WHERE core.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND core.ORGANIZATION_ID = :p_organization_id ' ||
               '   AND sbdh.INVENTORY_ITEM_ID (+) = core.INVENTORY_ITEM_ID ' ||
               '   AND sbdh.ORGANIZATION_ID (+) = core.ORGANIZATION_ID ';

Line: 3961

      l_sql := ' SELECT PRICE_COMPARISON_MSRMNT, PRICE_COMPARISON_MSRMNT_UUOM ' ||
               ' FROM EGO_SBDH_PRC_CMPRSN_MSRMT_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4019

      l_sql := ' SELECT TRADE_ITEM_COMPOSTN_WIDTH, TRADE_ITEM_COMPOSTN_WIDTH_UUOM ' ||
               ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4062

      l_sql := ' SELECT MATERIAL_WEIGHT, MATERIAL_WEIGHT_UUOM ' ||
               ' FROM EGO_SBDH_MATERIAL_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4109

      l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX, STORAGE_HNDLNG_HUMDTY_MIN_UUOM, STORAGE_HNDLNG_HUMDTY_MAX_UUOM ' ||
               ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 4193

        l_sql := ' SELECT  AGREED_MINIMUM_BUYING_QTY, ' ||
                 '         AGREED_MAXIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MAXIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MAXIMUM_BUYING_QTY ' ||
                 ' FROM EGO_SBDH_BUYING_QTY_INFO_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_BUYING_QTY_INFO_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4212

        l_sql := ' SELECT  AGREED_MINIMUM_BUYING_QTY, ' ||
                 '         AGREED_MAXIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MAXIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MAXIMUM_BUYING_QTY ' ||
                 ' FROM EGO_SBDH_BUYING_QTY_INFO_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4312

        l_sql := ' SELECT  FIRST_ORDER_DATE, ' ||
                 '         LAST_ORDER_DATE ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4327

        l_sql := ' SELECT  FIRST_ORDER_DATE, ' ||
                 '         LAST_ORDER_DATE ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4374

        l_sql := ' SELECT  FIRST_SHIP_DATE, ' ||
                 '         LAST_SHIP_DATE ' ||
                 ' FROM EGO_SBDH_SHIP_EXCL_DATES_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_SHIP_EXCL_DATES_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4389

        l_sql := ' SELECT  FIRST_SHIP_DATE, ' ||
                 '         LAST_SHIP_DATE ' ||
                 ' FROM EGO_SBDH_SHIP_EXCL_DATES_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4435

      l_sql := ' SELECT NVL(C.MIN_TRADE_ITEM_LIFE_ARR, MSI.SHELF_LIFE_DAYS) MIN_TRADE_ITEM_LIFE_ARR ' ||
               ' FROM MTL_SYSTEM_ITEMS_B MSI,EGO_ITEM_CUST_ATTRS_B C ' ||
               ' WHERE C.PARTY_SITE_ID (+) = :party_site_id ' ||
               '   AND MSI.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND MSI.ORGANIZATION_ID = :p_organization_id ' ||
               '   AND MSI.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID (+) '||
               '   AND MSI.ORGANIZATION_ID = C.MASTER_ORGANIZATION_ID (+) ';

Line: 4447

        l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
                 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4461

        l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
                 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4508

        l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_TPV O' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4523

        l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_AGV ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4581

      SELECT *
      FROM TABLE( CAST(l_error_table AS SYSTEM.EGO_PAGEWISE_ERROR_TABLE) );

Line: 4627

      SELECT PARTY_SITE_ID INTO l_party_site_id
      FROM HZ_CUST_ACCT_SITES_ALL
      WHERE CUST_ACCT_SITE_ID = p_address_id;

Line: 4635

      l_sql := ' SELECT DEPOSIT_VALUE_EFFECTIVE_DATE, DEPOSIT_VALUE_END_DATE ' ||
               ' FROM EGO_SBDH_DEP_VAL_DATE_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 4650

      l_sql := ' SELECT CAMPAIGN_START_DATE, CAMPAIGN_END_DATE ' ||
               ' FROM EGO_SBDH_CAMPAIGN_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 4665

      l_sql := ' SELECT SEASONAL_AVL_START_DATE, SEASONAL_AVL_END_DATE ' ||
               ' FROM EGO_SBDH_SEASON_AVL_DATE_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 4681

      l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX ' ||
               ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 4712

      l_sql := ' SELECT sbdh.PEG_HOLE_NUMBER, core.PEG_VERTICAL, core.PEG_HORIZONTAL ' ||
               ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV sbdh, EGO_ITEM_GTN_ATTRS_B core ' ||
               ' WHERE core.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND core.ORGANIZATION_ID = :p_organization_id ' ||
               '   AND sbdh.INVENTORY_ITEM_ID (+) = core.INVENTORY_ITEM_ID ' ||
               '   AND sbdh.ORGANIZATION_ID (+) = core.ORGANIZATION_ID ';

Line: 4733

      l_sql := ' SELECT PRICE_COMPARISON_MSRMNT, PRICE_COMPARISON_MSRMNT_UUOM ' ||
               ' FROM EGO_SBDH_PRC_CMPRSN_MSRMT_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4769

      l_sql := ' SELECT TRADE_ITEM_COMPOSTN_WIDTH, TRADE_ITEM_COMPOSTN_WIDTH_UUOM ' ||
               ' FROM EGO_SBDH_TRADE_ITEM_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4787

      l_sql := ' SELECT MATERIAL_WEIGHT, MATERIAL_WEIGHT_UUOM ' ||
               ' FROM EGO_SBDH_MATERIAL_INFO_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4806

      l_sql := ' SELECT STORAGE_HNDLNG_HUMDTY_MIN, STORAGE_HNDLNG_HUMDTY_MAX, STORAGE_HNDLNG_HUMDTY_MIN_UUOM, STORAGE_HNDLNG_HUMDTY_MAX_UUOM ' ||
               ' FROM EGO_SBDH_STRG_HNDLG_HMDTY_AGV ' ||
               ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND ORGANIZATION_ID = :p_organization_id';

Line: 4855

        l_sql := ' SELECT  AGREED_MINIMUM_BUYING_QTY, ' ||
                 '         AGREED_MAXIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MAXIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MAXIMUM_BUYING_QTY ' ||
                 ' FROM EGO_SBDH_BUYING_QTY_INFO_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_BUYING_QTY_INFO_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4874

        l_sql := ' SELECT  AGREED_MINIMUM_BUYING_QTY, ' ||
                 '         AGREED_MAXIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MINIMUM_BUYING_QTY, ' ||
                 '         START_DATE_MAXIMUM_BUYING_QTY, ' ||
                 '         END_DATE_MAXIMUM_BUYING_QTY ' ||
                 ' FROM EGO_SBDH_BUYING_QTY_INFO_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4908

        l_sql := ' SELECT  FIRST_ORDER_DATE, ' ||
                 '         LAST_ORDER_DATE ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4923

        l_sql := ' SELECT  FIRST_ORDER_DATE, ' ||
                 '         LAST_ORDER_DATE ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4942

        l_sql := ' SELECT  FIRST_SHIP_DATE, ' ||
                 '         LAST_SHIP_DATE ' ||
                 ' FROM EGO_SBDH_SHIP_EXCL_DATES_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_SHIP_EXCL_DATES_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 4957

        l_sql := ' SELECT  FIRST_SHIP_DATE, ' ||
                 '         LAST_SHIP_DATE ' ||
                 ' FROM EGO_SBDH_SHIP_EXCL_DATES_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 4975

      l_sql := ' SELECT NVL(C.MIN_TRADE_ITEM_LIFE_ARR, MSI.SHELF_LIFE_DAYS) MIN_TRADE_ITEM_LIFE_ARR ' ||
               ' FROM MTL_SYSTEM_ITEMS_B MSI,EGO_ITEM_CUST_ATTRS_B C ' ||
               ' WHERE C.PARTY_SITE_ID (+) = :party_site_id ' ||
               '   AND MSI.INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
               '   AND MSI.ORGANIZATION_ID = :p_organization_id ' ||
               '   AND MSI.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID (+) '||
               '   AND MSI.ORGANIZATION_ID = C.MASTER_ORGANIZATION_ID (+) ';

Line: 4987

        l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
                 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_TRD_ITM_LIFESPAN_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 5001

        l_sql := ' SELECT MIN_TRADE_ITEM_LIFE_PROD ' ||
                 ' FROM EGO_SBDH_TRD_ITM_LIFESPAN_AGV O ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 5019

        l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_TPV O' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND MASTER_ORGANIZATION_ID = :p_organization_id ' ||
                 '   AND (PARTY_SITE_ID = :party_site_id ' ||
                 '        OR (PARTY_SITE_ID IS NULL '||
                 '            AND NOT EXISTS (SELECT NULL ' ||
                 '                            FROM EGO_SBDH_ORDERING_INFO_TPV I ' ||
                 '                            WHERE O.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID ' ||
                 '                              AND O.MASTER_ORGANIZATION_ID = I.MASTER_ORGANIZATION_ID ' ||
                 '                              AND I.PARTY_SITE_ID = :2)))';

Line: 5034

        l_sql := ' SELECT GOODS_PICK_UP_LEAD_TIME, GOODS_PICK_UP_LEAD_TIME_UUOM ' ||
                 ' FROM EGO_SBDH_ORDERING_INFO_AGV ' ||
                 ' WHERE INVENTORY_ITEM_ID = :p_inventory_item_id ' ||
                 '   AND ORGANIZATION_ID = :p_organization_id ';

Line: 5057

   * This method is called after Trade Item Descriptor is updated and
   * item is a GDSN Outbound Enabled Item. This method NULLs out all the attributes
   * that are not updateable at Non-Leaf level
   */
  PROCEDURE PROCESS_GTID_UPDATE (p_inventory_item_id NUMBER,
                                 p_organization_id   NUMBER,
                                 p_trade_item_desc   VARCHAR2,
                                 x_return_status     OUT NOCOPY VARCHAR2,
                                 x_msg_count         OUT NOCOPY NUMBER,
                                 x_msg_data          OUT NOCOPY VARCHAR2)
  IS
    CURSOR c_single_attrs_not_upd IS
      SELECT DATA_TYPE_CODE, DATABASE_COLUMN
      FROM EGO_ATTRS_V
      WHERE ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
        AND APPLICATION_ID = 431
        AND EDIT_IN_HIERARCHY_CODE LIKE 'L%'
        AND NVL(ENABLED_FLAG, 'N') = 'Y';

Line: 5081

    SAVEPOINT GTID_UPDATE;

Line: 5082

    WRITE_DEBUG_LOG('Starting PROCESS_GTID_UPDATE');

Line: 5085

      l_b_sql := 'UPDATE EGO_ITEM_GTN_ATTRS_B SET ';

Line: 5086

      l_tl_sql := 'UPDATE EGO_ITEM_GTN_ATTRS_TL SET ';

Line: 5114

      DELETE FROM EGO_ITM_GTN_MUL_ATTRS_B
      WHERE INVENTORY_ITEM_ID = p_inventory_item_id
        AND ORGANIZATION_ID = p_organization_id
        AND ATTR_GROUP_ID IN (SELECT AG.ATTR_GROUP_ID
                              FROM EGO_FND_DSC_FLX_CTX_EXT AG, EGO_ATTRS_V EAV
                              WHERE AG.APPLICATION_ID = 431
                                AND EAV.APPLICATION_ID = AG.APPLICATION_ID
                                AND AG.DESCRIPTIVE_FLEXFIELD_NAME = EAV.ATTR_GROUP_TYPE
                                AND AG.DESCRIPTIVE_FLEX_CONTEXT_CODE = EAV.ATTR_GROUP_NAME
                                AND EAV.EDIT_IN_HIERARCHY_CODE LIKE 'L%'
                                AND NVL(EAV.ENABLED_FLAG, 'N') = 'Y'
                                AND EAV.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS');

Line: 5126

      WRITE_DEBUG_LOG('Deleted '||SQL%ROWCOUNT||' rows');

Line: 5131

    WRITE_DEBUG_LOG('Done PROCESS_GTID_UPDATE');

Line: 5133

    ROLLBACK TO SAVEPOINT GTID_UPDATE;

Line: 5134

    WRITE_DEBUG_LOG('Error in PROCESS_GTID_UPDATE-'||SQLERRM);

Line: 5138

  END PROCESS_GTID_UPDATE;