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: 201


      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: 211


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

Line: 237


            l_unpack_lpn_id_tbl.delete;

Line: 281


            l_pack_lpn_id_tbl.delete;

Line: 324


            l_unasgn_lpn_id_tbl.delete;

Line: 368


            l_asgn_lpn_id_tbl.delete;

Line: 387


      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: 391


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

Line: 392


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

Line: 395


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

Line: 397


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

Line: 398


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

Line: 399


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

Line: 400


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

Line: 401


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

Line: 402


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

Line: 403


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

Line: 404


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

Line: 405


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

Line: 406


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

Line: 407


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

Line: 408


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

Line: 409


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

Line: 441


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

Line: 483


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

Line: 499


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

Line: 516


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

Line: 541


      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: 619


      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: 628


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

Line: 653


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

Line: 671


             l_wms_lpn_tbl.delete;

Line: 680


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

Line: 681


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

Line: 684


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

Line: 685


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

Line: 686


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

Line: 687


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

Line: 688


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

Line: 689


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

Line: 690


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

Line: 691


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

Line: 692


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

Line: 693


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

Line: 694


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

Line: 695


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

Line: 696


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

Line: 697


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

Line: 731


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

Line: 773


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

Line: 789


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

Line: 806


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