eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OE_INV_IFACE_PVT SQL Statements

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

Line: 182


    OE_MSG_PUB.update_msg_context(
      p_header_id                  => l_line_rec.header_id
     ,p_orig_sys_document_ref      => l_line_rec.orig_sys_document_ref
     ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
     ,p_orig_sys_shipment_ref      => l_line_rec.orig_sys_shipment_ref
     ,p_change_sequence            => l_line_rec.change_sequence
     ,p_source_document_id         => l_line_rec.source_document_id
     ,p_source_document_line_id    => l_line_rec.source_document_line_id
     ,p_order_source_id            => l_line_rec.order_source_id
     ,p_source_document_type_id    => l_line_rec.source_document_type_id);

Line: 198


 SELECT MTL_TRANSACTIONS_ENABLED_FLAG
 INTO   l_transactable_flag
 FROM   MTL_SYSTEM_ITEMS
 WHERE inventory_item_id = l_line_rec.inventory_item_id
 AND   organization_id   = l_line_rec.ship_from_org_id;

Line: 228


   SELECT /* MOAC_SQL_CHANGE */  h.order_number,ot.name,h.ordered_date
   INTO   l_order_number,l_order_type_name,l_ordered_date
   FROM   oe_order_headers_all h, oe_order_types_v ot
   WHERE  h.header_id      = l_line_rec.header_id AND
          ot.order_type_id = h.order_type_id;

Line: 254


   SELECT DECODE(OE_SYS_PARAMETERS.value('TRX_DATE_FOR_INV_IFACE'),
                                   'C',SYSDATE,
                                   'S',nvl(l_line_rec.schedule_ship_date,SYSDATE),
                                   l_ordered_date)
   INTO   l_trx_date_for_inv_iface
   FROM   DUAL;

Line: 293


            SELECT revision_qty_control_code, lot_control_code, serial_number_control_code
            INTO l_revision_code, l_lot_code, l_serial_code
            FROM mtl_system_items
            WHERE inventory_item_id = l_line_rec.inventory_item_id
            AND   organization_id   = l_line_rec.ship_from_org_id;

Line: 301


                    oe_debug_pub.add(  'INV IFACE: REVISION/LOT SELECT FAILURE' , 5 ) ;

Line: 346


  SELECT mtl_material_transactions_s.nextval
  INTO l_transaction_header_id
  FROM dual;

Line: 534


                   GMI_Reservation_Util.update_opm_trxns(
                      l_rsv_tbl(I).reservation_id
                      ,l_line_rec.inventory_item_id
                      ,l_line_rec.ship_from_org_id
                      ,l_return_status
                      ,opm_msg_count
                      ,opm_msg_data);

Line: 544


                          oe_debug_pub.add(  'INV IFACE: FAILED TO UPDATE OPM TRXNS' , 5 ) ;

Line: 569


                 SELECT stock_locator_control_code
                 INTO   l_stock_locator_control_code
                 FROM   mtl_parameters
                 WHERE  organization_id = l_line_rec.ship_from_org_id ;

Line: 587


                 SELECT locator_type
                 INTO   l_locator_type
                 FROM   mtl_secondary_inventories
                 WHERE  secondary_inventory_name = l_rsv_tbl(I).subinventory_code
                 AND    organization_id = l_line_rec.ship_from_org_id;

Line: 605


                 SELECT location_control_code
                 INTO   l_location_control_code
                 FROM   mtl_system_items
                 WHERE  inventory_item_id = l_line_rec.inventory_item_id
                 AND    organization_id = l_line_rec.ship_from_org_id;

Line: 655


               SELECT oe_transactions_iface_s.nextval
               INTO l_source_line_id
               FROM dual;

Line: 660


               INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
               (
                SOURCE_CODE,
                SOURCE_LINE_ID,
                TRANSACTION_INTERFACE_ID,
                LOT_NUMBER,
                TRANSACTION_QUANTITY,
                SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
                LAST_UPDATE_DATE,
                LAST_UPDATED_BY,
                CREATION_DATE,
                CREATED_BY,
                SERIAL_TRANSACTION_TEMP_ID,
                ERROR_CODE,
                PROCESS_FLAG )
               VALUES
               (
                FND_PROFILE.VALUE('ONT_SOURCE_CODE'),
                l_source_line_id,
                l_transaction_interface_id,
                l_rsv_tbl(I).lot_number,
                (-1 * l_rsv_tbl(I).reservation_quantity),
                (-1 * l_rsv_tbl(I).secondary_reservation_quantity), --  INVCONV
                sysdate,
                FND_GLOBAL.USER_ID,
                sysdate,
                FND_GLOBAL.USER_ID,
                null,
                null,
                'Y');

Line: 695


                  oe_debug_pub.add(  'INV IFACE: INSERTING RECORD - 1' , 1 ) ;

Line: 728


                  oe_debug_pub.add(  'LAST_UPDATE_DATE :' || SYSDATE , 5 ) ;

Line: 730


                  oe_debug_pub.add(  'LAST_UPDATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;

Line: 807


              INSERT INTO MTL_TRANSACTIONS_INTERFACE
              (
               SOURCE_CODE,
               SOURCE_LINE_ID,
               SOURCE_HEADER_ID,
               PROCESS_FLAG,
               TRANSACTION_MODE,
               LOCK_FLAG,
               TRANSACTION_HEADER_ID,
               INVENTORY_ITEM_ID,
               SUBINVENTORY_CODE,
               TRANSACTION_QUANTITY,
               SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
               TRANSACTION_DATE,
               ORGANIZATION_ID,
               ACCT_PERIOD_ID,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               CREATION_DATE,
               CREATED_BY,
               TRANSACTION_SOURCE_ID,
               DSP_SEGMENT1,
               DSP_SEGMENT2,
               DSP_SEGMENT3,
               TRANSACTION_SOURCE_TYPE_ID,
               TRANSACTION_ACTION_ID,
               TRANSACTION_TYPE_ID,
               DISTRIBUTION_ACCOUNT_ID,
               DST_SEGMENT1,
               DST_SEGMENT2,
               DST_SEGMENT3,
               DST_SEGMENT4,
               DST_SEGMENT5,
               DST_SEGMENT6,
               DST_SEGMENT7,
               DST_SEGMENT8,
               DST_SEGMENT9,
               DST_SEGMENT10,
               DST_SEGMENT11,
               DST_SEGMENT12,
               DST_SEGMENT13,
               DST_SEGMENT14,
               DST_SEGMENT15,
               DST_SEGMENT16,
               DST_SEGMENT17,
               DST_SEGMENT18,
               DST_SEGMENT19,
               DST_SEGMENT20,
               DST_SEGMENT21,
               DST_SEGMENT22,
               DST_SEGMENT23,
               DST_SEGMENT24,
               DST_SEGMENT25,
               DST_SEGMENT26,
               DST_SEGMENT27,
               DST_SEGMENT28,
               DST_SEGMENT29,
               DST_SEGMENT30,
               TRANSACTION_REFERENCE,
               TRX_SOURCE_LINE_ID,
               TRX_SOURCE_DELIVERY_ID,
               REVISION,
               LOCATOR_ID,
               LOC_SEGMENT1,
               LOC_SEGMENT2,
               LOC_SEGMENT3,
               LOC_SEGMENT4,
               REQUIRED_FLAG,
               PICKING_LINE_ID,
               TRANSFER_SUBINVENTORY,
               TRANSFER_ORGANIZATION,
               SHIP_TO_LOCATION_ID,
               REQUISITION_LINE_ID,
               TRANSACTION_UOM,
               TRANSACTION_INTERFACE_ID,
               DEMAND_ID,
               SHIPMENT_NUMBER,
               CURRENCY_CODE,
               CURRENCY_CONVERSION_TYPE,
               CURRENCY_CONVERSION_DATE,
               CURRENCY_CONVERSION_RATE,
               ENCUMBRANCE_ACCOUNT,
               ENCUMBRANCE_AMOUNT,
	       CONTENT_LPN_ID,   -- added for bug 6313351
               PROJECT_ID,
               TASK_ID)
           SELECT
               profile_values.oe_source_code,
               l_source_line_id,
               l_transaction_reference,
               1,       /* PROCESS_FLAG 	*/
               3,       /* TRANSACTION_MODE */
               2,       /* LOCK_FLAG  */
               l_transaction_header_id,
               l_line_rec.inventory_item_id,
               l_rsv_tbl(I).subinventory_code,
               (-1 * l_rsv_tbl(I).reservation_quantity),
               (-1 * l_rsv_tbl(I).secondary_reservation_quantity), -- INVCONV
               l_trx_date_for_inv_iface/*l_ordered_date*/, --bug5897965 l_ordered_date commented
               l_rsv_tbl(I).organization_id,
               null,
               sysdate,
               profile_values.user_id,
               sysdate,
               profile_values.user_id,
               l_sales_order_id, /* transaction_source_id */
               l_order_number,
               l_order_type_name,
               profile_values.oe_source_code,
               2,
               1,
               33,
               l_trans_acc,
               segment1,
               segment2,
               segment3,
               segment4,
               segment5,
               segment6,
               segment7,
               segment8,
               segment9,
               segment10,
               segment11,
               segment12,
               segment13,
               segment14,
               segment15,
               segment16,
               segment17,
               segment18,
               segment19,
               segment20,
               segment21,
               segment22,
               segment23,
               segment24,
               segment25,
               segment26,
               segment27,
               segment28,
               segment29,
               segment30,
               l_transaction_reference,
               l_line_rec.line_id,
               null,
               l_rsv_tbl(I).revision,
               l_rsv_tbl(I).locator_id,
               null,
               null,
               null,
               null,
               null,
               null, /* l_shipment_line_id */
               null, /* l_dest_subinv */
               null, /* l_to_org_id */
               null, /* l_location_id */
               null, /* l_req_line_id */
               l_line_rec.order_quantity_uom,
               l_transaction_interface_id, /* interface_id */
               null,
               null,
               null,
               null,
               null,
               null,
               null, /* l_budget_acct_id */
               null, /* l_unit_price * p_transaction_detail_qty */
	       l_rsv_tbl(I).lpn_id,   -- added for bug 6313351
               l_line_rec.project_id,
               l_line_rec.task_id
           FROM gl_code_combinations
           WHERE code_combination_id = l_trans_acc;

Line: 981


           SELECT mtl_material_transactions_s.nextval
           INTO   l_transaction_interface_id
           FROM   dual;

Line: 1016


                      oe_debug_pub.add(  'INV IFACE: SUBINV IS NULL OR MISS_CHAR FOR INSERT - 2' , 5 ) ;

Line: 1032


              SELECT stock_locator_control_code
              INTO   l_stock_locator_control_code
              FROM   mtl_parameters
              WHERE  organization_id = l_line_rec.ship_from_org_id;

Line: 1039


                    oe_debug_pub.add(  'INV IFACE: LOCATOR CONTROL CODE SELECT FAILURE' , 5 ) ;

Line: 1048


              SELECT locator_type
              INTO   l_locator_type
              FROM   mtl_secondary_inventories
              WHERE  secondary_inventory_name = l_line_rec.subinventory
              AND    organization_id = l_line_rec.ship_from_org_id;

Line: 1057


                      oe_debug_pub.add(  'INV IFACE: LOCATOR TYPE SELECT FAILURE' , 5 ) ;

Line: 1067


          SELECT location_control_code
          INTO   l_location_control_code
          FROM   mtl_system_items
          WHERE  inventory_item_id = l_line_rec.inventory_item_id
          AND    organization_id = l_line_rec.ship_from_org_id;

Line: 1137


    SELECT oe_transactions_iface_s.nextval
    INTO l_source_line_id
    FROM dual;

Line: 1142


      oe_debug_pub.add(  'INV IFACE: INSERTING RECORD - 2' , 5 ) ;

Line: 1176


      oe_debug_pub.add(  'LAST_UPDATE_DATE :' || SYSDATE , 5 ) ;

Line: 1178


      oe_debug_pub.add(  'LAST_UPDATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;

Line: 1255


  INSERT INTO MTL_TRANSACTIONS_INTERFACE
        (
         SOURCE_CODE,
         SOURCE_LINE_ID,
         SOURCE_HEADER_ID,
         PROCESS_FLAG,
         TRANSACTION_MODE,
         LOCK_FLAG,
         TRANSACTION_HEADER_ID,
         INVENTORY_ITEM_ID,
         SUBINVENTORY_CODE,
         TRANSACTION_QUANTITY,
         SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
         TRANSACTION_DATE,
         ORGANIZATION_ID,
         ACCT_PERIOD_ID,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         TRANSACTION_SOURCE_ID,
         DSP_SEGMENT1,
         DSP_SEGMENT2,
         DSP_SEGMENT3,
         TRANSACTION_SOURCE_TYPE_ID,
         TRANSACTION_ACTION_ID,
         TRANSACTION_TYPE_ID,
         DISTRIBUTION_ACCOUNT_ID,
         DST_SEGMENT1,
         DST_SEGMENT2,
         DST_SEGMENT3,
         DST_SEGMENT4,
         DST_SEGMENT5,
         DST_SEGMENT6,
         DST_SEGMENT7,
         DST_SEGMENT8,
         DST_SEGMENT9,
         DST_SEGMENT10,
         DST_SEGMENT11,
         DST_SEGMENT12,
         DST_SEGMENT13,
         DST_SEGMENT14,
         DST_SEGMENT15,
         DST_SEGMENT16,
         DST_SEGMENT17,
         DST_SEGMENT18,
         DST_SEGMENT19,
         DST_SEGMENT20,
         DST_SEGMENT21,
         DST_SEGMENT22,
         DST_SEGMENT23,
         DST_SEGMENT24,
         DST_SEGMENT25,
         DST_SEGMENT26,
         DST_SEGMENT27,
         DST_SEGMENT28,
         DST_SEGMENT29,
         DST_SEGMENT30,
         TRANSACTION_REFERENCE,
         TRX_SOURCE_LINE_ID,
         TRX_SOURCE_DELIVERY_ID,
         REVISION,
         LOCATOR_ID,
         LOC_SEGMENT1,
         LOC_SEGMENT2,
         LOC_SEGMENT3,
         LOC_SEGMENT4,
         REQUIRED_FLAG,
         PICKING_LINE_ID,
         TRANSFER_SUBINVENTORY,
         TRANSFER_ORGANIZATION,
         SHIP_TO_LOCATION_ID,
         REQUISITION_LINE_ID,
         TRANSACTION_UOM,
         TRANSACTION_INTERFACE_ID,
         DEMAND_ID,
         SHIPMENT_NUMBER,
         CURRENCY_CODE,
         CURRENCY_CONVERSION_TYPE,
         CURRENCY_CONVERSION_DATE,
         CURRENCY_CONVERSION_RATE,
         ENCUMBRANCE_ACCOUNT,
         ENCUMBRANCE_AMOUNT,
         PROJECT_ID,
         TASK_ID)
         SELECT
         profile_values.oe_source_code,
         l_source_line_id,
         l_transaction_reference,
         1,       /* PROCESS_FLAG 	*/
         3,       /* TRANSACTION_MODE */
         2,       /* LOCK_FLAG  */
         l_transaction_header_id,
         l_line_rec.inventory_item_id,
         l_line_rec.subinventory,
         (-1 * decode(reservation_flag, 'Y', l_remained_qty, l_line_rec.ordered_quantity)),
         (-1 * decode(reservation_flag, 'Y', l_remained_qty2, l_line_rec.ordered_quantity2)), -- INVCONV
         l_trx_date_for_inv_iface/*l_ordered_date*/,  --bug5897965 l_ordered_date commented
         l_line_rec.ship_from_org_id,
         null,
         sysdate,
         profile_values.user_id,
         sysdate,
         profile_values.user_id,
         l_sales_order_id, /* transaction_source_id */
         l_order_number,
         l_order_type_name,
         profile_values.oe_source_code,
         2, /* l_trx_source_type_id */
         1, /* l_trx_action_id */
         33, /* l_trx_type_code */
         l_trans_acc,
         segment1,
         segment2,
         segment3,
         segment4,
         segment5,
         segment6,
         segment7,
         segment8,
         segment9,
         segment10,
         segment11,
         segment12,
         segment13,
         segment14,
         segment15,
         segment16,
         segment17,
         segment18,
         segment19,
         segment20,
         segment21,
         segment22,
         segment23,
         segment24,
         segment25,
         segment26,
         segment27,
         segment28,
         segment29,
         segment30,
         l_transaction_reference,
         l_line_rec.line_id,
         null,
         null, /* revision */
         null, /* locator */
         null,
         null,
         null,
         null,
         null,
         null, /* l_shipment_line_id */
         null, /* l_dest_subinv */
         null, /* l_to_org_id */
         null, /* l_location_id */
         null, /* l_req_line_id */
         l_line_rec.order_quantity_uom,
	 l_transaction_interface_id,
         null,
         null,
         null,
         null,
         null,
         null,
         null, /* l_budget_acct_id */
         null, /* l_unit_price * p_transaction_detail_qty */
         l_line_rec.project_id,
         l_line_rec.task_id
  FROM gl_code_combinations
  WHERE code_combination_id = l_trans_acc;

Line: 1428


      oe_debug_pub.add(  'INV IFACE: FINISH INSERTING - 2 , CALLING UPDATE_FLOW_STATUS_CODE' , 1 ) ;

Line: 1437


  /* update flow_status_code */
  OE_ORDER_WF_UTIL.Update_Flow_Status_Code(p_line_id => p_line_id
                             , p_flow_status_code => 'INVENTORY_INTERFACED'
                             , x_return_status => l_return_status);

Line: 1443


             oe_debug_pub.add(  'INV IFACE: UPDATE FLOW STATUS CODE FAILED' , 5 ) ;

Line: 1486


/* bug 4659103: update of visible_demand_flag code removed */

 IF l_debug_level  > 0 THEN
     oe_debug_pub.add(  'INV IFACE: EXITING INVENTORY_INTERFACE' , 1 ) ;