PL/SQL Procedure Amendment

Question

Currently we have an issue with a procedure. An amendment to the procedure to differentiate the returned value determined on if the Machine Type is 'ROAD TRAILER' or 'ROAD WAGON'.

The view which has these values and know that we can use 'MCH_CODE' to create a relationship between ACTIVE_WORK_ORDER_TAB and EQUIPMENT_FUNCTIONAL_UIV.

Tables I think are required (some currently in use)

• EQUIPMENT_FUNCTIONAL_UIV (required for MCH_TYPE and joins on VIA MCH_CODE to ACTIVE_WORK_ORDER_TAB
• ACTIVE_WORK_ORDER_TAB

Basically, this is how it should read however, I'm finding it difficult to update the procedure every time I've amended the statement it has broken.

If the MCH_TYPE is ROAD_WAGON or ROAD_TRAILER and TEMP_ in ('682','720','770','775','784') then return 14

ELSEIF the MCH_TYPE is NOT 'ROAD_WAGON' or 'ROAD_TRAILER' and TEMP_ in ('720') then return 14

Example of procedure below:

temp_               NUMBER;
  serv_               VARCHAR2(100);
  attr_               VARCHAR2(2000);
  info_               VARCHAR2(2000);
  WO_STATUS_ID_       VARCHAR2(200);
  Record_Found_       BOOLEAN;
  contract_           VARCHAR2(200);
  Pre_Accounting_id_  NUMBER;
  LoopCount_          NUMBER;

  CURSOR get_preacc IS
     SELECT * 
     FROM PRE_ACCOUNTING
     WHERE pre_accounting_id = Pre_Accounting_id_ ;

  CURSOR get_rec IS  
     SELECT WO_STATUS_ID,contract
     FROM ACTIVE_WORK_ORDER_TAB
     WHERE PRE_ACCOUNTING_ID = Pre_Accounting_id_;


BEGIN
  Pre_Accounting_Id_ := Client_sys.Get_Item_Value('PRE_ACCOUNTING_ID', Pre_Accounting_Attr_);
  Record_Found_      := FALSE;
  FOR c_get_rec IN get_rec LOOP
     Record_Found_ := TRUE;
     WO_STATUS_ID_ := c_get_rec.wo_status_id;
     contract_     := c_get_rec.contract;
  END LOOP;
  IF NVL(WO_STATUS_ID_,'XXX1') NOT IN ('RELEASED','STARTED','FAULTREPORT', 'WORKREQUEST') THEN
     RETURN;
  END IF;
  LoopCount_ := 0;
  <<IF_ERROR_LOOP>>
  BEGIN
      FOR x_ IN get_preacc LOOP
         BEGIN
          temp_ := to_number(x_.codeno_b);
         EXCEPTION
         WHEN OTHERS then
            temp_ := 0;
         END;
         IF temp_ >= 450 and temp_ <= 470 THEN
            serv_ := '12';
         ELSIF temp_ = 682 or temp_ = 720 or temp_ = 770 or temp_ = 775 or temp_ = 784 THEN
            serv_ := '14';
         ELSIF temp_ = 950 THEN
            serv_ := '12';
         ELSIF temp_ = 685 THEN
            serv_ := '22';
         ELSE
            serv_ := '16';
         END IF;

         IF nvl(x_.CODENO_C,'X') = serv_ THEN
             RETURN;
         END IF;
         Client_SYS.clear_Attr(attr_);
         Client_SYS.Add_To_Attr('CODENO_C', serv_,attr_); 
         Client_SYS.Add_To_Attr('COMPANY', Site_API.Get_Company(contract_),attr_); 
         Client_SYS.Add_To_Attr('CONTRACT', contract_,attr_); 

         PRE_ACCOUNTING_API.Modify__(info_,x_.objid,x_.objversion,attr_,'DO');
      END LOOP;
   EXCEPTION
        WHEN OTHERS THEN
            LoopCount_ := LoopCount_ +1;
            IF LoopCount_ < 20 THEN
                DBMS_LOCK.SLEEP (20);
                GOTO IF_ERROR_LOOP;
            END IF;
   END;

Answer 1

Resolved the problem by simply including the following in to the stored procedure:

 BEGIN
  FOR x_ IN get_preacc LOOP


     BEGIN
      temp_ := to_number(x_.codeno_b);
      MCH_TYPE := MCH_TYPE;
     EXCEPTION

     WHEN OTHERS then
        temp_ := 0;
     END;

     IF temp_ >= 450 
       and temp_ <= 470 THEN
        serv_ := '12';

         ELSIF (temp_ = 720 and (MCH_TYPE = 'ROAD WAGON' OR  MCH_TYPE = 'ROAD TRAILER'))
         then serv_ := '20';  

         ELSIF temp_ = 682
         or (temp_ = 720 and (MCH_TYPE != 'ROAD WAGON' OR  MCH_TYPE != 'ROAD TRAILER'))
         or temp_ = 770 
         or temp_ = 775 
         or temp_ = 784 
         THEN
         serv_ := '14';

     ELSIF temp_ = 950 THEN
        serv_ := '12';

     ELSIF temp_ = 741 
       or temp_ = 773 
       or temp_ = 774 
       or temp_ = 775 
       or temp_ = 740 THEN
       serv_ := '22';


     ELSIF temp_ = 685 THEN
        serv_ := '22';

     ELSE
        serv_ := '16';
     END IF;

     IF nvl(x_.CODENO_C,'X') = serv_ THEN
         RETURN;
     END IF;
     Client_SYS.clear_Attr(attr_);
     Client_SYS.Add_To_Attr('CODENO_C', serv_,attr_); 
     Client_SYS.Add_To_Attr('COMPANY', Site_API.Get_Company(contract_),attr_); 
     Client_SYS.Add_To_Attr('CONTRACT', contract_,attr_); 
     PRE_ACCOUNTING_API.Modify__(info_,x_.objid,x_.objversion,attr_,'DO');
  END LOOP;