Solved

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

Posted on 2016-08-25
7
44 Views
Last Modified: 2016-08-26
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
0
Comment
Question by:anumoses
  • 5
  • 2
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Basically add a parameter in the cursor to take the order id's
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
Comment Utility
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.  

datadata
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
you can do that, but why do you want to make that a cursor solution?
cursors are "always" slower ...

also, that code so far does nothing, except doing the "selects", but not comparing the function outcomes

using your select and my suggestion:
SELECT orditm.ID
, PKG_PROJECTS_SHARED_old.FX_GET_EDD_ORDITM(orditm.ID)  old_function_value
   , PKG_PROJECTS_SHARED_new.FX_GET_EDD_ORDITM(orditm.ID)  new_function_value
           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)
  --- and optionally filter for only those giving different outcomes on the functions:
   AND PKG_PROJECTS_SHARED_old.FX_GET_EDD_ORDITM(orditm.ID)   <>   PKG_PROJECTS_SHARED_new.FX_GET_EDD_ORDITM(orditm.ID)

;

Open in new window



note:

AND trunc(ord.dt_created) BETWEEN '20-jun-2016' AND '22-jun-2016'

should really be:
AND ord.dt_created >= to_date('20-jun-2016', 'dd-MON-YYYY')
AND ord.dt_created  < to_date('22-jun-2016', 'dd-MON-YYYY') + 1
0
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now