Home » SQL & PL/SQL » SQL & PL/SQL » function (12c)
function [message #665032] |
Fri, 18 August 2017 06:25 |
|
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 |
cookiemonster
Messages: 13943 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 |
Solomon Yakobson
Messages: 3285 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 |
|
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;
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:38:36 CDT 2024
|