반응형 프로그래밍/Oracle24 select number 더하기(+) DECLARE N_CNT NUMBER(1); BEGIN SELECT COUNT(1) AS N_CNT INTO N_CNT FROM ZMMT016 WHERE 1=1 AND MANDT = #G_MANDT# AND CPUDT = TO_CHAR(SYSDATE, 'YYYYMMDD') AND BUDAT = #BUDAT# AND LIFNR = #LIFNR# AND WERKS = #WERKS# AND MATNR = #MATNR# AND BWART = '101' AND WAERS = #WAERS#; IF #INS_Q# > 0 THEN -- 합격수량이 0 보다 클경우만 INSERT 또는 UPDATE 한다. IF N_CNT = 0 THEN INSERT INTO ZMMT016 (MANDT, CPUDT, BUDAT, LIFNR,.. 2013. 8. 1. [FUNCTION]GET_PROJ_END CREATE OR REPLACE FUNCTION SAPHEE.GET_PROJ_END (IN_MANDT VARCHAR2, IN_POSID VARCHAR2 ) RETURN VARCHAR2 IS RT VARCHAR(100) ; P_MANDT VARCHAR(72) DEFAULT ''; P_POSID VARCHAR(72) DEFAULT ''; P_WBS VARCHAR(72) DEFAULT ''; P_LN INTEGER DEFAULT 0; P_LR INTEGER DEFAULT 0; BEGIN P_MANDT := RTRIM(IN_MANDT); P_POSID := RTRIM(IN_POSID); P_LN := INSTR(P_POSID, '-P'); P_LR := INSTR(P_POSID, '-R'); IF (P_LR >.. 2013. 7. 11. [FUNCTION]GET_ZERODATE CREATE OR REPLACE FUNCTION SAPHEE.GET_ZERODATE (IN_DATE VARCHAR2 ) RETURN VARCHAR2 IS RT VARCHAR2(8); BEGIN RT := IN_DATE; IF IN_DATE = '00000000' THEN RT := ''; END IF; IF IN_DATE = '000000' THEN RT := ''; END IF; RETURN RT; END; / 2013. 7. 11. [FUNCTION]SET_ZERODATE CREATE OR REPLACE FUNCTION SAPHEE.SET_ZERODATE (IN_DATE VARCHAR2, GB VARCHAR2) RETURN VARCHAR2 IS RT VARCHAR2(8); BEGIN RT := IN_DATE; IF GB = 'D' AND IN_DATE = '0' THEN RT := '00000000'; END IF; IF GB = 'T' AND IN_DATE='0' THEN RT := '000000'; END IF; RETURN RT; END; / 2013. 7. 11. [FUNCTION]GET_JAJEA_BALJU CREATE OR REPLACE FUNCTION SAPHEE.GET_JAJEA_BALJU ( IN_MANDT VARCHAR2, IN_POSID VARCHAR2, IN_MATNR VARCHAR2, IN_EBELN VARCHAR2, IN_EBELP NUMBER ) RETURN NUMBER IS RT NUMBER(13, 3); P_MANDT VARCHAR2(72) ; P_POSID VARCHAR2(72) ; P_MATNR VARCHAR2(72) ; P_WBS VARCHAR2(72) ; P_LN NUMBER(13, 3) ; P_A_MENGE NUMBER(13, 3) ; P_B_MENGE NUMBER(13, 3) ; P_C_MENGE NUMBER(13, 3) ; BEGIN P_MANDT := RTRIM(IN_MA.. 2013. 7. 11. [FUNCTION]GET_BUDAT CREATE OR REPLACE FUNCTION SAPHEE.GET_BUDAT (IN_MANDT VARCHAR2, IN_EBELN VARCHAR2, IN_EBELP VARCHAR2 ) RETURN VARCHAR2 IS RT VARCHAR(30) DEFAULT ''; P_MANDT VARCHAR(9) DEFAULT ''; P_EBELN VARCHAR(30) DEFAULT ''; P_EBELP VARCHAR(30) DEFAULT ''; BEGIN P_MANDT := RTRIM(IN_MANDT); P_EBELN := RTRIM(IN_EBELN); P_EBELP := RTRIM(IN_EBELP); SELECT MAX(SAPHEE.GET_ZERODATE(BUDAT)) AS BUDAT INTO RT FROM SAP.. 2013. 7. 11. 이전 1 2 3 4 다음 반응형