DBA Data[Home] [Help]

APPS.INV_INVENTORY_ADJUSTMENT SQL Statements

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

Line: 56

    select transaction_type_id
      into l_txn_type_id
      from mtl_transaction_types
     where transaction_type_name = p_trx_type;
Line: 102

  select mtl_txns_history_s.nextval
  into l_entity_id
  from dual;
Line: 121

       inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 124

    insert into mtl_adjustment_sync_temp
     (TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     ENTITY_ID)
     select
     TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     l_entity_id
     from mtl_adj_sync_wrapper_v
     where organization_id =	p_org_id
     AND wms_deploy.get_client_code(inventory_item_id) =  p_client_code
     AND NVL(transaction_extracted, 'N') NOT IN ('Y','P')
     AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id)
     AND transaction_date >= trx_date_from
     AND transaction_date < trx_date_to + 1; -- Added 1 as the input dates are truncated.
Line: 201

       inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 208

       inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 211

    insert into mtl_adjustment_sync_temp
     (TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     ENTITY_ID)
     select
     TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     l_entity_id
     from mtl_adj_sync_wrapper_v
     where organization_id = p_org_id
     AND wms_deploy.get_client_code(inventory_item_id) =  p_client_code
     AND xml_document_id = p_xml_doc_id
     AND NVL(transaction_extracted, 'N') IN ('Y');
Line: 290

       inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 305

       inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 308

    insert into mtl_adjustment_sync_temp
     (TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     ENTITY_ID)
     select
     TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     l_entity_id
     from mtl_adj_sync_wrapper_v
     where organization_id =	p_org_id
     AND NVL(transaction_extracted, 'N') NOT	IN ('Y','P')
     AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id)
     AND transaction_date >= trx_date_from
     AND transaction_date < trx_date_to + 1;
Line: 384

       inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 391

       inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 394

    insert into mtl_adjustment_sync_temp
     (TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     ENTITY_ID)
     select
     TRANSACTION_NUMBER,
     TRANSACTION_DATE,
     CATEGORY,
     CATEGORY_ID,
     WAREHOUSE,
     ORGANIZATION_ID,
     WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
     ITEM_DESCRIPTION,
     INVENTORY_ITEM_ID,
     REVISION,
     SUBINVENTORY,
     LOCATOR,
     TRANSFER_WAREHOUSE,
     TRANSFER_SUBINVENTORY,
     TRANSFER_LOCATOR,
     LPN,
     TRANSFER_LPN,
     CONTENT_LPN,
     TRANSACTION_TYPE,
     TRANSACTION_TYPE_ID,
     TRANSACTION_SOURCE_TYPE_ID,
     TRANSACTION_ACTION_ID,
     TRANSACTION_SOURCE,
     CREATION_DATE,
     TRANSACTION_EXTRACTED,
     XML_DOCUMENT_ID,
     TRANSACTION_QUANTITY,
     TRANSACTION_UOM,
     PRIMARY_QUANTITY,
     PRIMARY_UOM,
     SECONDARY_QUANTITY,
     SECONDARY_UOM,
     l_entity_id
     from mtl_adj_sync_wrapper_v
     where organization_id = p_org_id
     AND xml_document_id = p_xml_doc_id
     AND NVL(transaction_extracted, 'N') IN ('Y');
Line: 472

       inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 488

    UPDATE mtl_material_transactions
    SET transaction_extracted = 'P'
    WHERE organization_id = p_org_id
    AND NVL(transaction_extracted, 'N') NOT IN ( 'Y', 'P')
    AND xml_document_id IS NULL
    AND transaction_id IN (select transaction_number
                           from mtl_adjustment_sync_temp
                           where entity_id = l_entity_id);
Line: 528

           UPDATE mtl_material_transactions
           SET transaction_extracted = null,
               xml_document_id = null
           WHERE organization_id = p_org_id
           AND NVL(transaction_extracted, 'N') = 'P'
           AND xml_document_id IS NULL
           AND transaction_id IN (select transaction_number
                                  from mtl_adjustment_sync_temp
                                  where entity_id = l_entity_id);
Line: 540

        delete_temp_table(l_entity_id);
Line: 563

          delete_temp_table(l_entity_id);
Line: 571

 PROCEDURE delete_temp_table (p_entity_id NUMBER) IS

 BEGIN

    if (p_entity_id is not null) then

       if (g_debug = 1) then
           inv_trx_util_pub.TRACE('deleting the temp table for entity_id: '||p_entity_id, 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 581

       delete from mtl_adjustment_sync_temp
       where entity_id = p_entity_id;
Line: 592

           inv_trx_util_pub.TRACE('Exception : '||sqlerrm||' occurred in delete_temp_table', 'INV_INVENTORY_ADJUSTMENT', 9);
Line: 596

 END delete_temp_table;