asked on

Oracle - running a packate routine(function) - Testing in a cursor and comparing old and new

PKG_PROJECTS_SHARED_old.FX_GET_EDD_ORDITM(ord_id)

PKG_PROJECTS_SHARED_new.FX_GET_EDD_ORDITM(ord_id)

This is a function

I want to write a sql script that has a cursor to loop through the old and new function and make sure it works. I need to add some dbms messages for the output.

In the Cursor I need to call the old and new functions.

Using Oracle 10G

FUNCTION fx_get_edd_orditm(
      in_orditm_id      IN      NUMBER)
      RETURN DATE
   IS
      v_edd                      DATE;
      v_order_type               orders.order_type%TYPE;
      v_order_id                 NUMBER;
      v_status                   ordered_items.status%TYPE;
      v_delivery_days            carrier_methods.delivery_days%TYPE;
      v_orditm_id_dist_xfer      NUMBER;
      LS_INCLUDE_SATURDAY   DSS.CARRIER_METHODS.INCLUDE_SATURDAY%TYPE;       --35594: Added
      LS_INCLUDE_SUNDAY     DSS.CARRIER_METHODS.INCLUDE_SUNDAY%TYPE;         --35594: Added
      LS_INCLUDE_HOLIDAY    DSS.CARRIER_METHODS.INCLUDE_HOLIDAY%TYPE;        --35594: Added

      FUNCTION get_edd(
         in_orditm_id      IN      NUMBER)
         RETURN DATE
      IS
         v_order_type          orders.order_type%TYPE;
         v_revised             DATE;
         v_order_required      DATE;
         v_order_id            NUMBER;
         v_promise             DATE;
         v_edd                 DATE;
         v_delivery_days       carrier_methods.delivery_days%TYPE;
         -- 27590
         v_div_no              orders.div_no%TYPE;
         v_approved_date       orders.approved_date%TYPE;
         v_eddro_value         division_parameters.value%TYPE;
         v_eddpo_value         division_parameters.value%TYPE;
         v_csp_count           NUMBER;
         -- 27590 end
         LS_INCLUDE_SATURDAY   DSS.CARRIER_METHODS.INCLUDE_SATURDAY%TYPE;       --35594: Added
         LS_INCLUDE_SUNDAY     DSS.CARRIER_METHODS.INCLUDE_SUNDAY%TYPE;         --35594: Added
         LS_INCLUDE_HOLIDAY    DSS.CARRIER_METHODS.INCLUDE_HOLIDAY%TYPE;        --35594: Added
 
      BEGIN
         SELECT ord.order_type
               ,ord.required
               ,orditm.revised
               ,ord.ID
               ,ord.div_no
               ,orditm.approved_date
           INTO v_order_type
               ,v_order_required
               ,v_revised
               ,v_order_id
               ,v_div_no
               ,v_approved_date
           FROM ordered_items orditm
               ,orders ord
          WHERE orditm.ID = in_orditm_id
            AND ord.ID = orditm.order_id;

      

         IF v_order_type IN('PO', 'RO')
         THEN

            BEGIN
              
               SELECT PKG_IMOPS_UTIL.FUNC_GETENDINGSHIPPABLEDATE
                        ( TRUNC( ADVSHP.DATE_SHIPPED )
                        , coalesce (advshp.override_delivery_days, CARRMETH.DELIVERY_DAYS, 3) -- 36967
                        , CARRMETH.INCLUDE_SATURDAY
                        , CARRMETH.INCLUDE_SUNDAY
                        , CARRMETH.INCLUDE_HOLIDAY
                        )
                 INTO V_EDD
                 FROM ADVANCED_SHIPMENTS ADVSHP
                    , CARRIER_METHODS    CARRMETH
                WHERE ADVSHP.ORDITM_ID = IN_ORDITM_ID
                  AND CARRMETH.ID(+) = ADVSHP.CARRMETH_ID
                  AND advshp.id = pkg_projects_shared.fx_get_advshp_id(advshp.orditm_id);
    

            EXCEPTION
               WHEN no_data_found
               THEN
                  v_edd := NULL;
            END;

            IF v_edd IS NULL
            THEN
 
               IF v_order_type = 'RO'
               THEN

                  IF fx_is_program_order(in_orditm_id) = 'Y'
                  THEN
                                        
                     v_promise := fx_get_promise(in_orditm_id
                                                ,'RECENT');
                  ELSE


                     BEGIN
                        SELECT value
                          INTO v_eddro_value
                          FROM division_parameters
                         WHERE div_no = v_div_no
                           AND parm_short_name = 'EDDRO'
                           AND active = 'A';
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           v_eddro_value := 'A';
                     END;
                     IF v_eddro_value IN ('A','C')
                     THEN

                        v_promise := fx_get_promise(in_orditm_id
                                                   ,'CURRENT');
                     ELSIF v_eddro_value = 'P'
                     THEN

                        v_promise := fx_get_promise(in_orditm_id
                                                   ,'RECENT');
                     ELSE
                        IF v_approved_date IS NOT NULL
                        THEN
                           IF v_eddro_value IN ('QA','QC')
                           THEN

                              v_promise := fx_get_promise(in_orditm_id
                                                         ,'CURRENT');
                           ELSIF v_eddro_value = 'QP'
                           THEN

                              v_promise := fx_get_promise(in_orditm_id
                                                         ,'RECENT');
                           END IF;
                        END IF;
                     END IF;
                  END IF;
               ELSIF v_order_type = 'PO'
               THEN

                  SELECT COUNT(*)
                    INTO v_csp_count
                    FROM projects_jn
                   WHERE order_id_po = v_order_id;
                  IF v_csp_count = 0
                  THEN
                     SELECT COUNT(*)
                       INTO v_csp_count
                       FROM customers_jn
                      WHERE order_id_po = v_order_id;
                  END IF;
                  IF v_csp_count > 0
                  THEN
                     BEGIN
                        SELECT value
                          INTO v_eddpo_value
                          FROM division_parameters
                         WHERE div_no = v_div_no
                           AND parm_short_name = 'EDDPOCSP'
                           AND active = 'A';
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           v_eddpo_value := 'A';
                     END;
                  ELSE
                     BEGIN
                        SELECT value
                          INTO v_eddpo_value
                          FROM division_parameters
                         WHERE div_no = v_div_no
                           AND parm_short_name = 'EDDPO'
                           AND active = 'A';
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           v_eddpo_value := 'A';
                     END;
                  END IF;
                  IF v_eddpo_value IN ('A','C')
                  THEN

                     v_promise := fx_get_promise(in_orditm_id
                                                ,'CURRENT');
                  ELSIF v_eddpo_value = 'P'
                  THEN

                     v_promise := fx_get_promise(in_orditm_id
                                                ,'RECENT');
                  END IF;
               END IF;

       
               IF v_promise IS NOT NULL
               THEN
                  v_edd := v_promise;
               ELSE
                  -- 27590
                  IF ( v_order_type = 'RO'
                       AND ( v_eddro_value = 'A'
                             OR
                             ( v_eddro_value = 'QA'
                               AND v_approved_date IS NOT NULL )
                           )
                     )
                     OR
                     ( v_order_type = 'PO'
                       AND v_eddpo_value = 'A' )
                  THEN
                     v_edd := v_revised;
                  ELSE
                     v_edd := NULL;

                  END IF;
                
                END IF;
                   

    
            END IF;

         ELSIF v_order_type = 'XFER'
         THEN
            BEGIN

               SELECT PKG_IMOPS_UTIL.FUNC_GETENDINGSHIPPABLEDATE
                        ( TRUNC( SHIP.SHIP_DATE )
                        , DECODE( NVL( CARRMETH.DELIVERY_DAYS, 0 )
                                ,0 ,3
                                ,CARRMETH.DELIVERY_DAYS
                                )
                        , CARRMETH.INCLUDE_SATURDAY
                        , CARRMETH.INCLUDE_SUNDAY
                        , CARRMETH.INCLUDE_HOLIDAY
                        )
                 INTO V_EDD
                 FROM SHIPMENTS       SHIP
                     ,CARRIER_METHODS CARRMETH
                WHERE SHIP.ORDER_ID = V_ORDER_ID
                  AND SHIP.SHIP_DATE IS NOT NULL
                  AND CARRMETH.ID(+) = SHIP.CARRMETH_ID;

            EXCEPTION
               WHEN no_data_found
               THEN
                  BEGIN
                     SELECT decode(nvl(carrmeth.delivery_days
                                      ,0)
                                  ,0, 3
                                  ,carrmeth.delivery_days)
                          , CARRMETH.INCLUDE_SATURDAY  --35594: Added
                          , CARRMETH.INCLUDE_SUNDAY    --35594: Added
                          , CARRMETH.INCLUDE_HOLIDAY   --35594: Added
                       INTO v_delivery_days
                          , LS_INCLUDE_SATURDAY  --35594: Added
                          , LS_INCLUDE_SUNDAY    --35594: Added
                          , LS_INCLUDE_HOLIDAY   --35594: Added
                       FROM plan_shipments plshp
                           ,carrier_methods carrmeth
                      WHERE plshp.order_id = v_order_id
                        AND carrmeth.ID(+) = plshp.carrmeth_id;
                  EXCEPTION
                     WHEN no_data_found
                     THEN
                        v_delivery_days := 0;
                        LS_INCLUDE_SATURDAY := 'N';  --35594: Added
                        LS_INCLUDE_SUNDAY   := 'N';  --35594: Added
                        LS_INCLUDE_HOLIDAY  := 'N';  --35594: Added
                  END;


                  v_edd :=
                     PKG_IMOPS_UTIL.FUNC_GETENDINGSHIPPABLEDATE
                       ( V_ORDER_REQUIRED
                       , v_delivery_days + 1
                       , LS_INCLUDE_SATURDAY
                       , LS_INCLUDE_SUNDAY
                       , LS_INCLUDE_HOLIDAY
                       );
            END;

         END IF;

         RETURN(v_edd);
      END get_edd;
   BEGIN
      SELECT ord.order_type
            ,ord.ID
        INTO v_order_type
            ,v_order_id
        FROM ordered_items orditm
            ,orders ord
       WHERE orditm.ID = in_orditm_id
         AND ord.ID = orditm.order_id;

      IF v_order_type IN('PO', 'RO')
      THEN
         v_edd := get_edd(in_orditm_id);
      ELSIF v_order_type = 'XFER'
      THEN
         SELECT status
           INTO v_status
           FROM ordered_items
          WHERE ID = in_orditm_id;

         IF v_status <> 'R'
         THEN
            v_edd := get_edd(in_orditm_id);
         ELSE
            BEGIN
             
               SELECT DECODE( NVL( CARRMETH.DELIVERY_DAYS, 0 )
                            , 0 ,3
                            , CARRMETH.DELIVERY_DAYS
                            )
                    , CARRMETH.INCLUDE_SATURDAY
                    , CARRMETH.INCLUDE_SUNDAY
                    , CARRMETH.INCLUDE_HOLIDAY
                 INTO V_DELIVERY_DAYS
                    , LS_INCLUDE_SATURDAY
                    , LS_INCLUDE_SUNDAY
                    , LS_INCLUDE_HOLIDAY
                 FROM PLAN_SHIPMENTS PLSHP
                     ,CARRIER_METHODS CARRMETH
                WHERE PLSHP.ORDER_ID = V_ORDER_ID
                  AND CARRMETH.ID(+) = PLSHP.CARRMETH_ID;
               --35594: End

            EXCEPTION
               WHEN no_data_found
               THEN
                  v_delivery_days := 0;
                  LS_INCLUDE_SATURDAY := 'N';  --35594: Added
                  LS_INCLUDE_SUNDAY   := 'N';  --35594: Added
                  LS_INCLUDE_HOLIDAY  := 'N';  --35594: Added
            END;

            SELECT orditm_id_dist
              INTO v_orditm_id_dist_xfer
              FROM item_fulfillments
             WHERE orditm_id = in_orditm_id;

            v_edd := get_edd(v_orditm_id_dist_xfer);

            v_edd :=
               PKG_IMOPS_UTIL.FUNC_GETENDINGSHIPPABLEDATE
                 ( V_EDD
                 , V_DELIVERY_DAYS + 1
                 , LS_INCLUDE_SATURDAY
                 , LS_INCLUDE_SUNDAY
                 , LS_INCLUDE_HOLIDAY
                 );


         END IF;
      END IF;

      RETURN(v_edd);
   END fx_get_edd_orditm;

Open in new window

The one that I have is old code.
I need to check for 'PURCHORD','REPAIRORD','STOCKTFR'

like v_order_type = 'XFER' v_order_type IN('PO', 'RO')

Help apreciated

Guy Hengel [angelIII / a3]

presuming you have the table with the values of ord_id; you just do this:

select t.ord_id
   , PKG_PROJECTS_SHARED_old.FX_GET_EDD_ORDITM(t.ord_id)  old_function_value
   , PKG_PROJECTS_SHARED_new.FX_GET_EDD_ORDITM(t.ord_id)  new_function_value
 from yourtable t
where <as you need it >
   AND PKG_PROJECTS_SHARED_old.FX_GET_EDD_ORDITM(t.ord_id)   <> PKG_PROJECTS_SHARED_new.FX_GET_EDD_ORDITM(t.ord_id)

Open in new window

I presume that the function does not return nulls, but that can also be handled by an adapted where clause

anumoses

ASKER

I want to add few orders for few divisions like div 1 and div 11. I want to declare a cursor. In your solution your_table t , I do not have any table

anumoses

ASKER

Basically add a parameter in the cursor to take the order id's

anumoses

ASKER

SELECT orditm.ID
           FROM ordered_items orditm
               ,orders ord
          WHERE ORDER_TYPE in('PO','XFER' ,'RO')
            and ord.dt_created between '15-jun-2016' and '30-jun-2016'
            AND ord.ID = orditm.order_id
            and div_no in (1,11)   
            
            
SELECT orditm.ID,ord.order_number,ord.div_no,ord.order_type
           FROM ordered_items orditm
               ,orders ord
          WHERE ORDER_TYPE in('PO','XFER' ,'RO')
            and ord.dt_created between '01-may-2016' and '30-jun-2016'
            AND ord.ID = orditm.order_id
            and div_no in (1,11)

Open in new window

The first select statement I want to use in the cursor and those orders ids will be a parameter to my test.

data

anumoses

ASKER

Declare
v_ord_id number;
Cursor C1 is
         SELECT orditm.ID
           FROM ordered_items orditm
               ,orders ord
          WHERE ORDER_TYPE in('PO','XFER' ,'RO')
            AND trunc(ord.dt_created) BETWEEN '20-jun-2016' AND '22-jun-2016'
            AND ord.ID = orditm.order_id
            AND div_no in (1,11);
   BEGIN
      OPEN C1;
      LOOP
      FETCH C1 INTO v_order_id;
         select PKG_PROJECTS_SHARED_OLD.FX_GET_EDD_ORDITM(v_order_id) from dual;
         select PKG_PROJECTS_SHARED_NEW.FX_GET_EDD_ORDITM(v_order_id) from dual;
      EXIT WHEN C1%NOTFOUND;
      END LOOP;
   END;

Open in new window

IS this correct to test?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Thanks