Home » SQL & PL/SQL » SQL & PL/SQL » function (12c)
function [message #665032] Fri, 18 August 2017 06:25 Go to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
I have to write a function to derive the approval template, based on three criteria.

there are two tables
1) contrib_tab -- which records details of transaction of salesman like his coordinator id , margin per order and value of order
2) appr_tab --- manager table which holds details of approving manager.

each salesman is linked to manager.
based on salesman,contribution margin and value approving manager has to be selected.
my question to all the experts is that is there a better way to do this as this is just sample as i have too many salesmen and managers combinations which i need to incorporate in the code.
Can it be achieved using sql.


CREATE TABLE APPR_MST(APPR_CODE VARCHAR2(12));
INSERT INTO APPR_MST(APPR_CODE) VALUES ('EQS001');
INSERT INTO APPR_MST(APPR_CODE) VALUES ('EQS002');
INSERT INTO APPR_MST(APPR_CODE) VALUES ('EQS003');
INSERT INTO APPR_MST(APPR_CODE) VALUES ('FDS001');
INSERT INTO APPR_MST(APPR_CODE) VALUES ('FDS002');
INSERT INTO APPR_MST(APPR_CODE) VALUES ('FDS003');

--below is the transaction table
CREATE TABLE CONTRIB_TAB ( COORD_CD VARCHAR2(10),COORD_MARGIN NUMBER, COORD_VALUE NUMBER);
INSERT INTO CONTRIB_TAB ( COORD_CD,COORD_MARGIN, COORD_VALUE ) VALUES ( '10',25,10000);
INSERT INTO CONTRIB_TAB ( COORD_CD,COORD_MARGIN, COORD_VALUE ) VALUES ( '20',27,15000);
INSERT INTO CONTRIB_TAB ( COORD_CD,COORD_MARGIN, COORD_VALUE ) VALUES ( '30',20,15000);
INSERT INTO CONTRIB_TAB ( COORD_CD,COORD_MARGIN, COORD_VALUE ) VALUES ( '40',20,50000);


---coordinator or salesman (10,20) are linked to approving manager EQS001,EQS002,EQS003
---coordinator or salesman (30,40) are linked to approving manager FDS001,FDS002,FDS003
-- Condition is if value less than 10,000 and margin greater than 25 then EQS001
--If margin > 25 and regardless of any value then EQS002
--IF The margin goes below 25 then EQS003 will approve , 
--above three rules apply for coordinator 30 and 40.


/* Formatted on 8/18/2017 2:21:37 PM (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FUNCTION GET_APPR_TEMPL (P_COORD     VARCHAR2,
                                           P_MARGIN    NUMBER,
                                           P_VALUE     NUMBER)
   RETURN VARCHAR2
IS
   P_TEMP_   VARCHAR2 (12);
BEGIN
   IF P_COORD IN ('10', '20')
   THEN
      IF P_MARGIN > 25 AND P_VALUE > 0 AND P_VALUE <= 10000
      THEN
         P_TEMP_ := 'EQS001';
      ELSIF P_MARGIN > 25 AND P_VALUE > 10000
      THEN
         P_TEMP_ := 'EQS002';
      ELSIF P_MARGIN < 25 AND P_VALUE > 0
      THEN
         P_TEMP_ := 'EQS003';      
      END IF;
   ELSIF P_COORD IN ('30', '40')
   THEN
      IF P_MARGIN > 25 AND  P_VALUE > 0 AND P_VALUE <= 10000
      THEN
         P_TEMP_ := 'FDS001';
      ELSIF P_MARGIN > 25 AND P_VALUE > 10000
      THEN
         P_TEMP_ := 'FDS002';
      ELSIF P_MARGIN < 25 AND P_VALUE > 0
      THEN
         P_TEMP_ := 'FDS003';
      END IF;
   END IF;

   RETURN P_TEMP_;
END;


SELECT COORD_CD,
       COORD_MARGIN,
       COORD_VALUE,
       GET_APPR_TEMPL (coord_cd, coord_margin, coord_value) REQD_APPR
  FROM contrib_tab;

--result is

10	25	10000	EQS001
20	27	15000	EQS002
30	20	15000	FDS003
40	20	50000	FDS003








Re: function [message #665034 is a reply to message #665032] Fri, 18 August 2017 07:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
To do it in SQL you would need to put all those hard-coded values in a table first. Probably one that looks like this
APPR_CODE VARCHAR2(12)
COORD_CD VARCHAR2(10)
MIN_MARGIN NUMBER
MAX_MARGIN NUMBER
MIN_VALUE NUMBER
MAX_VALUE NUMBER
Re: function [message #665035 is a reply to message #665032] Fri, 18 August 2017 08:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
No, your function results are:

COORD_CD   COORD_MARGIN COORD_VALUE REQD_APPR
---------- ------------ ----------- ----------
10                   25       10000
20                   27       15000 EQS002
30                   20       15000 FDS003
40                   20       50000 FDS003

SQL> 

COORD_MARGIN = 25 meets neither P_MARGIN > 25 nor P_MARGIN < 25.

Anyway:

WITH T AS (
           SELECT  COORD_CD,
                   COORD_MARGIN,
                   COORD_VALUE,
                   CASE
                     WHEN COORD_CD IN ('10','20') THEN 'EQS00'
                     WHEN COORD_CD IN ('30','40') THEN 'FDS00'
                   END PART1,
                   CASE
                     WHEN COORD_MARGIN > 25 AND COORD_VALUE > 0 AND COORD_VALUE <= 10000 THEN '1'
                     WHEN COORD_MARGIN > 25 AND COORD_VALUE > 10000 THEN '2'
                     WHEN COORD_MARGIN < 25 AND COORD_VALUE > 0 THEN '3'
                   END PART2
             FROM  CONTRIB_TAB
          )
SELECT  COORD_CD,
        COORD_MARGIN,
        COORD_VALUE,
        CASE
          WHEN PART1 IS NULL OR PART2 IS NULL THEN NULL
          ELSE PART1 || PART2
        END REQD_APPR
  FROM  T
/

COORD_CD   COORD_MARGIN COORD_VALUE REQD_APPR
---------- ------------ ----------- ----------
10                   25       10000
20                   27       15000 EQS002
30                   20       15000 FDS003
40                   20       50000 FDS003

SQL> 

SY.
Re: function [message #665038 is a reply to message #665035] Fri, 18 August 2017 14:23 Go to previous message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
thanks solomon and cookiemonster, yes you are right solomon i missed out a condition like <=25 and the other criteria > 25 is solving my problem.
I have re written it like this.


CREATE OR REPLACE FUNCTION GET_APPR_TEMPL (P_COORD     VARCHAR2,
                                           P_MARGIN    NUMBER,
                                           P_VALUE     NUMBER)
   RETURN VARCHAR2
IS
   P_TEMP_   VARCHAR2 (12);
BEGIN
   IF P_COORD IN ('10', '20')
   THEN
      IF P_MARGIN >= 25 AND P_VALUE > 0 AND P_VALUE <= 10000
      THEN
         P_TEMP_ := 'EQS001';
      ELSIF P_MARGIN >= 25 AND P_VALUE > 10000
      THEN
         P_TEMP_ := 'EQS002';
      ELSIF P_MARGIN < 25 AND P_VALUE > 0
      THEN
         P_TEMP_ := 'EQS003';
      END IF;
   ELSIF P_COORD IN ('30', '40')
   THEN
      IF P_MARGIN >= 25 AND  P_VALUE > 0 AND P_VALUE <= 10000
      THEN
         P_TEMP_ := 'FDS001';
      ELSIF P_MARGIN >= 25 AND P_VALUE > 10000
      THEN
         P_TEMP_ := 'FDS002';
      ELSIF P_MARGIN < 25 AND P_VALUE > 0
      THEN
         P_TEMP_ := 'FDS003';
      END IF;
   END IF;

   RETURN P_TEMP_;
END;

Previous Topic: Should I Clean Up My Collections?
Next Topic: Solution for ora-00020 maximum number of processes
Goto Forum:
  


Current Time: Fri Mar 29 01:27:45 CDT 2024