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.WSH_LPN_SYNC_COMM_PKG SQL Statements

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

Line: 25


      l_original_update_to_cnt VARCHAR2(2);

Line: 28


      select wddc.lpn_id,
             wddp.lpn_id old_parent_lpn_id
      from   wsh_delivery_details wddc,
             wsh_delivery_details wddp,
             wsh_delivery_assignments_v wda,
             wsh_wms_sync_tmp wlst
      where  wlst.delivery_detail_id = wda.delivery_detail_id
      and    wddc.delivery_detail_id = wda.delivery_detail_id
      and    wlst.parent_delivery_detail_id = wddp.delivery_detail_id
      and    nvl(wda.parent_delivery_detail_id, -1) <> nvl(wlst.parent_delivery_detail_id,-1)
      and    wlst.parent_delivery_detail_id is not null
      and    wlst.operation_type = 'PRIOR'
      and    wddc.lpn_id is not null
      and    wddp.lpn_id is not null
      and    wlst.creation_date = p_hw_time_stamp
      order by
             wlst.parent_delivery_detail_id;

Line: 51


      select wddc.lpn_id,
             wddp.lpn_id parent_lpn_id
      from   wsh_delivery_details wddc,
             wsh_delivery_details wddp,
             wsh_delivery_assignments_v wda,
             wsh_wms_sync_tmp wlst
      where  wlst.delivery_detail_id = wda.delivery_detail_id
      and    wddc.delivery_detail_id = wda.delivery_detail_id
      and    wda.parent_delivery_detail_id = wddp.delivery_detail_id
      and    nvl(wda.parent_delivery_detail_id, -1) <> nvl(wlst.parent_delivery_detail_id,-1)
      and    wda.parent_delivery_detail_id is not null
      and    wddc.lpn_id is not null
      and    wddp.lpn_id is not null
      and    wlst.operation_type = 'PRIOR'
      and    wlst.creation_date = p_hw_time_stamp
      order by
             wda.parent_delivery_detail_id;

Line: 74


      select wdd.lpn_id,
             wlst.delivery_id old_delivery_id
      from   wsh_delivery_details wdd,
             wsh_delivery_assignments_v wda,
             wsh_wms_sync_tmp wlst
      where  wlst.delivery_detail_id = wda.delivery_detail_id
      and    wdd.delivery_detail_id = wda.delivery_detail_id
      and    nvl(wda.delivery_id, -1) <> nvl(wlst.delivery_id,-1)
      and    wlst.delivery_id is not null
      and    wdd.lpn_id is not null
      and    wlst.operation_type = 'PRIOR'
      and    wlst.creation_date = p_hw_time_stamp
      order by
             wlst.delivery_id;

Line: 95


      select wdd.lpn_id,
             wda.delivery_id new_delivery_id
      from   wsh_delivery_details wdd,
             wsh_delivery_assignments_v wda,
             wsh_wms_sync_tmp wlst
      where  wlst.delivery_detail_id = wda.delivery_detail_id
      and    wdd.delivery_detail_id = wda.delivery_detail_id
      and    nvl(wda.delivery_id, -1) <> nvl(wlst.delivery_id,-1)
      and    wda.delivery_id is not null
      and    wdd.lpn_id is not null
      and    wlst.operation_type = 'PRIOR'
      and    wlst.creation_date = p_hw_time_stamp
      order by
             wda.delivery_id;

Line: 114


      cursor l_update_csr (p_hw_time_stamp IN DATE) is
      select wdd.lpn_id,
             wlst.delivery_detail_id,
             wdd.container_name,
             wdd.inventory_item_id,
             wdd.organization_id,
             wdd.subinventory,
             wdd.locator_id,
             wdd.gross_weight,
             wdd.volume_uom_code,
             wdd.filled_volume,
             wdd.volume,
             wdd.weight_uom_code,
             wdd.net_weight
      from   wsh_delivery_details wdd,
             wsh_wms_sync_tmp wlst
      where  wlst.delivery_detail_id = wdd.delivery_detail_id
      and    wdd.lpn_id is not null
      and    wlst.operation_type = 'UPDATE'
      and    wlst.creation_date = p_hw_time_stamp;

Line: 193


      select count(*)  into l_tmp_tbl_size from wsh_wms_sync_tmp
      where creation_date = l_current_hw_time_stamp;

Line: 206


         update wsh_wms_sync_tmp
         set    operation_type = 'DELETE'
         where  delivery_detail_id in (select delivery_detail_id
                                       from wsh_wms_sync_tmp
                                       where operation_type = 'DELETE'
                                       and creation_date = l_current_hw_time_stamp)
         and    operation_type = 'UPDATE'
         and    creation_date = l_current_hw_time_stamp;

Line: 216


           WSH_DEBUG_SV.log(l_module_name,'Count of rows updated is', SQL%ROWCOUNT);

Line: 242


               l_unpack_lpn_id_tbl.delete;

Line: 286


               l_pack_lpn_id_tbl.delete;

Line: 329


               l_unasgn_lpn_id_tbl.delete;

Line: 373


               l_asgn_lpn_id_tbl.delete;

Line: 392


         FOR update_rec in l_update_csr(l_current_hw_time_stamp) LOOP
         --{
             IF l_debug_on THEN
               WSH_DEBUG_SV.log(l_module_name,'Inside the loop i is', i);

Line: 396


               WSH_DEBUG_SV.log(l_module_name,'Inside the loop delivery_detail_id is', update_rec.delivery_detail_id);

Line: 397


               WSH_DEBUG_SV.log(l_module_name,'Inside the loop lpn_id is', update_rec.lpn_id);

Line: 400


             IF (update_rec.lpn_id IS NOT NULL) THEN
               l_wms_lpn_tbl(i).LPN_ID                  := update_rec.lpn_id;

Line: 402


               l_wms_lpn_tbl(i).LICENSE_PLATE_NUMBER    := update_rec.container_name;

Line: 403


               l_wms_lpn_tbl(i).INVENTORY_ITEM_ID       := update_rec.inventory_item_id;

Line: 404


               l_wms_lpn_tbl(i).ORGANIZATION_ID         := update_rec.ORGANIZATION_ID;

Line: 405


               l_wms_lpn_tbl(i).SUBINVENTORY_CODE       := update_rec.SUBINVENTORY;

Line: 406


               l_wms_lpn_tbl(i).LOCATOR_ID              := update_rec.LOCATOR_ID;

Line: 407


               l_wms_lpn_tbl(i).GROSS_WEIGHT_UOM_CODE   := update_rec.weight_uom_code;

Line: 408


               l_wms_lpn_tbl(i).GROSS_WEIGHT            := update_rec.gross_weight;

Line: 409


               l_wms_lpn_tbl(i).CONTAINER_VOLUME_UOM    := update_rec.volume_uom_code;

Line: 410


               l_wms_lpn_tbl(i).CONTAINER_VOLUME        := update_rec.volume;

Line: 411


               l_wms_lpn_tbl(i).CONTENT_VOLUME_UOM_CODE := update_rec.volume_uom_code;

Line: 412


               l_wms_lpn_tbl(i).CONTENT_VOLUME          := update_rec.filled_volume;

Line: 413


               l_wms_lpn_tbl(i).TARE_WEIGHT_UOM_CODE    := update_rec.weight_uom_code;

Line: 414


               l_wms_lpn_tbl(i).TARE_WEIGHT             := (update_rec.gross_weight - update_rec.net_weight);

Line: 448


           IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
             wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 490


      IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
        wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 506


      IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
        wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 523


      IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
        wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 548


      cursor l_pr_update_csr (p_hw_time_stamp IN DATE) is
      select wdd.lpn_id,
             wlst.delivery_detail_id,
             wdd.container_name,
             wdd.inventory_item_id,
             wdd.organization_id,
             wdd.subinventory,
             wdd.locator_id,
             wdd.gross_weight,
             wdd.volume_uom_code,
             wdd.filled_volume,
             wdd.volume,
             wdd.weight_uom_code,
             wdd.net_weight,
             wlst.call_level
      from   wsh_delivery_details wdd,
             wsh_wms_sync_tmp wlst
      where  wlst.delivery_detail_id = wdd.delivery_detail_id
      and    wdd.lpn_id is not null
      and    wlst.operation_type = 'UPDATE'
      and    wlst.creation_date = p_hw_time_stamp
      order  by nvl(wlst.call_level,0) desc;

Line: 626


      FOR update_rec in l_pr_update_csr(l_current_hw_time_stamp) LOOP
      --{
          --
          IF l_debug_on THEN
            WSH_DEBUG_SV.log(l_module_name,'count of plsql table l_wms_lpn_tbl is', l_wms_lpn_tbl.count);

Line: 635


          l_call_level := nvl(update_rec.call_level,0);

Line: 660


               IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
                 wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 678


             l_wms_lpn_tbl.delete;

Line: 687


            WSH_DEBUG_SV.log(l_module_name,'Inside the loop delivery_detail_id is', update_rec.delivery_detail_id);

Line: 688


            WSH_DEBUG_SV.log(l_module_name,'Inside the loop lpn_id is', update_rec.lpn_id);

Line: 691


          l_wms_lpn_tbl(i).LPN_ID                  := update_rec.lpn_id;

Line: 692


          l_wms_lpn_tbl(i).LICENSE_PLATE_NUMBER    := update_rec.container_name;

Line: 693


          l_wms_lpn_tbl(i).INVENTORY_ITEM_ID       := update_rec.inventory_item_id;

Line: 694


          l_wms_lpn_tbl(i).ORGANIZATION_ID         := update_rec.ORGANIZATION_ID;

Line: 695


          l_wms_lpn_tbl(i).SUBINVENTORY_CODE       := update_rec.SUBINVENTORY;

Line: 696


          l_wms_lpn_tbl(i).LOCATOR_ID              := update_rec.LOCATOR_ID;

Line: 697


          l_wms_lpn_tbl(i).GROSS_WEIGHT_UOM_CODE   := update_rec.weight_uom_code;

Line: 698


          l_wms_lpn_tbl(i).GROSS_WEIGHT            := update_rec.gross_weight;

Line: 699


          l_wms_lpn_tbl(i).CONTAINER_VOLUME_UOM    := update_rec.volume_uom_code;

Line: 700


          l_wms_lpn_tbl(i).CONTAINER_VOLUME        := update_rec.volume;

Line: 701


          l_wms_lpn_tbl(i).CONTENT_VOLUME_UOM_CODE := update_rec.volume_uom_code;

Line: 702


          l_wms_lpn_tbl(i).CONTENT_VOLUME          := update_rec.filled_volume;

Line: 703


          l_wms_lpn_tbl(i).TARE_WEIGHT_UOM_CODE    := update_rec.weight_uom_code;

Line: 704


          l_wms_lpn_tbl(i).TARE_WEIGHT             := (update_rec.gross_weight - update_rec.net_weight);

Line: 738


           IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
             wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 780


      IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
        wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 796


      IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
        wsh_wms_lpn_grp.g_update_to_containers := 'N';

Line: 813


      IF (nvl(wsh_wms_lpn_grp.g_update_to_containers,'N') = 'Y') THEN
        wsh_wms_lpn_grp.g_update_to_containers := 'N';