14 Replies Latest reply: Nov 5, 2015 6:18 AM by balraj ahlawat RSS

    Script utilization - need to be robust

    Mina Patel

      I have the following script which runs really slow, does anyone have any tips on making the load quicker?

       

      OrderBookStatusTMP:

      Load OE_Order_Lines_All.LINE_ID as [OBST Line Id],

           [Order Quantity]

      Resident Order_Line

      where [Booked Flag] = 'Y'

        and Status = 'AWAITING_SHIPPING';

       

      Inner Join(OrderBookStatusTMP)

      Load WSH_Delivery_Details.SOURCE_LINE_ID as [OBST Line Id],

           WSH_Delivery_Details.DELIVERY_DETAIL_ID,

           WSH_Delivery_Details.REQUESTED_QUANTITY

      Resident WSH_DeliveryDetails

      Where ApplyMap('HasAssignmentRecordMap',[Join WDA to WDD],'N') = 'N';

       

       

      OrderBookStatus:

      Load

        WSH_Delivery_Details.DELIVERY_DETAIL_ID as [Join WDD to OBS],

        //[Join OBS to Order Line],

        //sum(OE_Order_Lines_All.PRICING_QUANTITY - OE_Order_Lines_All.CANCELLED_QUANTITY) as Quantity,

        Sum(WSH_Delivery_Details.REQUESTED_QUANTITY) as Quantity,

        'Order Units Booked' as [Order Sub Status],

        'Booked' as [Order Status]

      Resident

        OrderBookStatusTMP

      group by WSH_Delivery_Details.DELIVERY_DETAIL_ID;

       

       

      drop table OrderBookStatusTMP;

       

       

      Concatenate(OrderBookStatus)

      Load

        [PD Detail Id] as [Join WDD to OBS],

        Sum([PD Pickticket Quantity]) as Quantity,

        'Released to Warehouse' as [Order Sub Status],

        'Released to Warehouse' as [Order Status]

      Resident

        PickTicket_Detail

      where

        [PD PH Status] = '10'

      group by [PD Detail Id];

       

      Concatenate(OrderBookStatus)

      Load

          [PD Detail Id] as [Join WDD to OBS],

        Sum([PD Pickticket Quantity]) as Quantity,

        'Waved' as [Order Sub Status],

        'In Picking/Packing' as [Order Status]

      Resident

        PickTicket_Detail

      where

        [PD PH Status] = '20'

      group by [PD Detail Id];

       

       

      Concatenate(OrderBookStatus)

      Load

          [PD Detail Id] as [Join WDD to OBS],

        Sum([PD Pickticket Quantity]) as Quantity,

        'Picked' as [Order Sub Status],

        'In Picking/Packing' as [Order Status]

      Resident

        PickTicket_Detail

      where

        [PD PH Status] >= '21' and [PD PH Status] <= '34'

      group by [PD Detail Id];

       

      Concatenate(OrderBookStatus)

      Load

          [PD Detail Id] as [Join WDD to OBS],

        Sum([PD Pickticket Quantity]) as Quantity,

        'In Packing' as [Order Sub Status],

        'In Picking/Packing' as [Order Status]

      Resident

        PickTicket_Detail

      where

        [PD PH Status] >= '35' and [PD PH Status] <= '40'

      group by [PD Detail Id];

       

       

      Concatenate(OrderBookStatus)

      Load

          [PD Detail Id] as [Join WDD to OBS],

      // If(Sum([PD Shipped Quantity]) = 0,

      // If(sum([PD Packed Quantity]) = 0, sum([PD Pickticket Quantity]),sum([PD Packed Quantity]))

      // ,sum([PD Shipped Quantity])) as Quantity,

          Sum([PD State Quantity]) as Quantity,

        'Loaded on Trailer' as [Order Sub Status],

        'Loaded on Trailer' as [Order Status]

      Resident

        PickTicket_Detail

      where

        [PD PH Status] = '70'

      group by [PD Detail Id];