APPS.WSH_INV_INTEGRATION_GRP SQL Statements

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

Line: 21

        select printer_name
        from wsh_report_printers_v  wrp
        where wrp.level_type_id = '10006'
        and   application_id = 665
        and   concurrent_program_id = (
	      select concurrent_program_id from
	      fnd_concurrent_programs_vl
	      where concurrent_program_name = 'WSHRDPIK'
	      and application_id = 665
	      and rownum = 1 )
        and   organization_id = v_organization_id
        and   wrp.subinventory  = v_subinventory
	and   wrp.enabled_flag = 'Y'
	order by decode ( nvl( wrp.default_printer_flag ,2) ,
				'Y' ,1 , 2 ) ;

Line: 38

         select printer_name
         from wsh_report_printers_v  wrp
         where wrp.level_type_id = '10008'
         and   application_id = 665
         and   concurrent_program_id =  (
	      select concurrent_program_id from
	      fnd_concurrent_programs_vl
	      where concurrent_program_name = 'WSHRDPIK'
	      and application_id = 665
	      and rownum = 1 )
         and   level_value_id  = v_organization_id
	 and   wrp.enabled_flag = 'Y'
	 order by decode ( nvl( wrp.default_printer_flag ,2) ,
				'Y' ,1 , 2 )  ;

Line: 94

	 -- First insert the org in the global table

         WSH_INV_INTEGRATION_GRP.G_ORGTAB(WSH_INV_INTEGRATION_GRP.G_ORGTAB.count + 1 ):= p_organization_id;

Line: 135

             tables.The purpose of this procedure is to update the inventory_interfaced_flag on
             wsh_delivery_details to 'Y' if inventory interface process has completed successfully
             and also to update the pending_interface_flag for the corresponding trip stops to NULL.
*/
PROCEDURE Complete_Inv_Interface(
        p_api_version_number    IN NUMBER,
        p_init_msg_list         IN VARCHAR2,
        p_commit                IN VARCHAR2 DEFAULT FND_API.G_FALSE,
        p_txn_header_id         IN NUMBER,
        p_txn_batch_id          IN NUMBER,
        x_return_status         OUT NOCOPY VARCHAR2,
        x_msg_count             OUT NOCOPY NUMBER,
        x_msg_data              OUT NOCOPY VARCHAR2) IS

CURSOR detail_rec_csr IS
     SELECT 	st.stop_id, wdd.delivery_detail_id
     FROM	wsh_delivery_details wdd,
		mtl_transactions_interface mtf,
                wsh_delivery_assignments_v da ,
                wsh_delivery_legs dg,
                wsh_new_deliveries dl,
                wsh_trip_stops st
     WHERE	wdd.delivery_detail_id = mtf.picking_line_id
     AND	mtf.error_code IS NULL
     AND	mtf.transaction_header_id = p_txn_header_id
     AND	nvl(mtf.transaction_batch_id,-1) = nvl(nvl(p_txn_batch_id,mtf.transaction_batch_id),-1)
     AND        ((mtf.transaction_action_id IN ( 21,3,2,1)
                    AND mtf.transaction_source_type_id = 8)
              OR ((mtf.transaction_action_id = 1) AND
                        mtf.transaction_source_type_id IN (2,16,13)))
     AND 	wdd.container_flag = 'N'
     AND 	nvl(wdd.inv_interfaced_flag , 'N')  <> 'Y'
     AND 	nvl(wdd.inv_interfaced_flag , 'N')  <> 'X'
     AND		wdd.released_status <> 'D'
     AND        (exists (
                SELECT mmt.picking_line_id
                   FROM  mtl_material_transactions mmt
                   WHERE mmt.picking_line_id  =  wdd.delivery_detail_id
               and transaction_source_type_id in ( 2,8,13,16 )
               and trx_source_line_id = wdd.source_line_id
                   )
               )
     AND       wdd.delivery_detail_id = da.delivery_detail_id
     AND       dl.delivery_id = da.delivery_id
     AND       da.delivery_id  IS NOT NULL
     AND       st.stop_id = dg.pick_up_stop_id
     AND       st.stop_location_id = dl.initial_pickup_location_id
     AND       dg.delivery_id = dl.delivery_id
     AND       wdd.source_code in ('OE','OKE', 'WSH')
     ORDER BY  st.stop_id, wdd.delivery_detail_id
     FOR UPDATE of wdd.delivery_detail_id NOWAIT;

Line: 189

   SELECT wdd.delivery_detail_id
   FROM   wsh_trip_stops wts,
         wsh_delivery_legs wdl,
         wsh_delivery_assignments_v wda,
         wsh_delivery_details wdd
   WHERE  (wdd.inv_interfaced_flag IN ('N', 'P') OR wdd.oe_interfaced_flag <> 'Y')
   AND   wts.stop_id = p_stop_id
   AND   wts.stop_location_id = wdd.ship_from_location_id
   AND   wts.stop_id = wdl.pick_up_stop_id
   AND   wdl.delivery_id = wda.delivery_id
   AND   wda.delivery_id IS NOT NULL
   AND   wda.delivery_detail_id = wdd.delivery_detail_id
   AND   wdd.source_code in ('OE','OKE', 'WSH')
   AND   wdd.released_status <> 'D'
   AND   rownum = 1;

Line: 292

    UPDATE wsh_delivery_details
    SET    inv_interfaced_flag = 'Y'
    WHERE  delivery_detail_id = l_det_rec.delivery_detail_id(i);

Line: 299

      WSH_DEBUG_SV.log(l_module_name,'No of Record Update to Y  l_row_count',l_row_count);

Line: 303

     If yes, then update the pending_interface_flag of the trip to NULL.*/

   l_index := l_det_rec.stop_id.FIRST;

Line: 311

           SELECT 'X' INTO   l_temp
           FROM   WSH_TRIP_STOPS
           WHERE  stop_id = l_det_rec.stop_id(l_index)
           FOR UPDATE NOWAIT;

Line: 324

              UPDATE wsh_trip_stops
                 SET    pending_interface_flag = NULL
                 WHERE  stop_id = l_det_rec.stop_id(l_index);

Line: 331

               WSH_DEBUG_SV.log(l_module_name,'pending_interface_flag update to NULL for stop',l_det_rec.stop_id(l_index));