154 lines
5.2 KiB
MySQL
154 lines
5.2 KiB
MySQL
CREATE OR REPLACE FUNCTION getFX_VALUE(
|
|
P_CURR_CD VARCHAR2
|
|
, P_INVOICE_DT DATE /* NULL 인 경우 EXTENDED FX VALUE */
|
|
)
|
|
RETURN NUMBER
|
|
IS
|
|
RET_VALUE NUMBER := 0;
|
|
BEGIN
|
|
IF (P_INVOICE_DT IS NULL) THEN
|
|
SELECT FX_VALUE INTO RET_VALUE
|
|
FROM MKT_FX_QUOTE FQ
|
|
INNER JOIN MKT_FX_QUOTE_DEFINITION FQD ON FQ.FX_QUOTE_DEF_NUM = FQD.FX_QUOTE_DEF_NUM
|
|
WHERE FQD.FX_QUOTE_DEF_CD = P_CURR_CD + '-USD'
|
|
AND FQ.QUOTE_DT = (SELECT MAX(XFQ.QUOTE_DT)
|
|
FROM MKT_FX_QUOTE XFQ
|
|
INNER JOIN MKT_FX_QUOTE_DEFINITION XFQD ON XFQ.FX_QUOTE_DEF_NUM = XFQD.FX_QUOTE_DEF_NUM
|
|
WHERE XFQD.FX_QUOTE_DEF_CD = P_CURR_CD+'-USD'
|
|
AND XFQ.QUOTE_DT BETWEEN TO_DATE(TO_CHAR(P_INVOICE_DT, 'YYYYMM') + '01', 'YYYYMMDD') AND P_INVOICE_DT);
|
|
ELSE
|
|
SELECT FX_VALUE INTO RET_VALUE
|
|
FROM MKT_FX_QUOTE FQ
|
|
INNER JOIN MKT_FX_QUOTE_DEFINITION FQD ON FQ.FX_QUOTE_DEF_NUM = FQD.FX_QUOTE_DEF_NUM
|
|
WHERE FQD.FX_QUOTE_DEF_CD = P_CURR_CD + '-USD'
|
|
AND FQ.QUOTE_DT = (SELECT MAX(XFQ.QUOTE_DT)
|
|
FROM MKT_FX_QUOTE XFQ
|
|
INNER JOIN MKT_FX_QUOTE_DEFINITION XFQD ON XFQ.FX_QUOTE_DEF_NUM = XFQD.FX_QUOTE_DEF_NUM
|
|
WHERE XFQD.FX_QUOTE_DEF_CD = P_CURR_CD+'-USD'
|
|
AND XFQ.QUOTE_DT BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'YYYYMM') + '01', 'YYYYMMDD') AND SYSDATE);
|
|
END IF;
|
|
|
|
RETURN RET_VALUE;
|
|
END;
|
|
|
|
CREATE OR REPLACE FUNCTION currencyAmount(
|
|
P_CURR_CD VARCHAR2
|
|
, P_AMOUNT NUMBER
|
|
)
|
|
RETURN NUMBER
|
|
IS
|
|
RET_VALUE NUMBER(25,5) := NULL;
|
|
BEGIN
|
|
RET_VALUE := (CASE WHEN P_CURR_CD = 'USC' THEN NVL(P_AMOUNT,0) / 100 ELSE NVL(P_AMOUNT,0) END ) ;
|
|
RETURN RET_VALUE ;
|
|
END;
|
|
|
|
CREATE OR REPLACE FUNCTION currencyExtendedAmount(
|
|
P_CURR_CD VARCHAR2
|
|
, P_AMOUNT NUMBER
|
|
)
|
|
RETURN NUMBER
|
|
IS
|
|
RET_VALUE NUMBER(25,5) := NULL;
|
|
BEGIN
|
|
RET_VALUE := (CASE WHEN P_CURR_CD = 'USC' THEN NVL(P_AMOUNT,0) / 100 ELSE NVL(P_AMOUNT,0) END ) ;
|
|
RETURN RET_VALUE ;
|
|
END;
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION dateDiff(
|
|
P_RETURN_TYPE VARCHAR2
|
|
, P_FROM DATE
|
|
, P_TO DATE
|
|
)
|
|
RETURN NUMBER
|
|
IS
|
|
RET_VALUE NUMBER(12) := NULL;
|
|
BEGIN
|
|
IF (P_RETURN_TYPE = 'DD') THEN
|
|
RET_VALUE := TRUNC(P_TO - P_FROM) ;
|
|
ELSE
|
|
RET_VALUE := TRUNC(P_TO - P_FROM) ;
|
|
END IF;
|
|
RETURN RET_VALUE ;
|
|
END;
|
|
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION selectInvoiceNumber(
|
|
P_TRADE_NUM NUMBER
|
|
)
|
|
RETURN VARCHAR2
|
|
IS
|
|
RET_VALUE VARCHAR2(3000) := NULL ;
|
|
BEGIN
|
|
|
|
SELECT
|
|
LISTAGG(X.INVOICE_NUMBER,',') WITHIN GROUP (ORDER BY X.TRADE_NUM ) INVOICE_NUMBER
|
|
INTO RET_VALUE
|
|
FROM (
|
|
SELECT DISTINCT
|
|
B.TRADE_NUM , B.INVOICE_NUMBER
|
|
FROM REF_CREDIT_LIMIT_DEFINITION A
|
|
INNER JOIN STL_ITEM_HDR B ON A.COMPANY_NUM = B.COUNTERPART_COMPANY_NUM
|
|
LEFT JOIN STL_INVOICE_DETAIL C ON B.ITEM_HDR_NUM = C.ITEM_HDR_NUM AND C.INV_DETAIL_STATUS_IND = 1
|
|
LEFT JOIN STL_INVOICE_HDR D ON C.INVOICE_HDR_NUM = D.INVOICE_HDR_NUM
|
|
AND TO_CHAR(D.INVOICE_STATUS_IND) IN (
|
|
SELECT REGEXP_SUBSTR(NVL('#{V_INVOICE_STATUS_IND}',''), '[^,]+', 1, LEVEL) FROM DUAL
|
|
CONNECT BY REGEXP_SUBSTR(NVL('#{V_INVOICE_STATUS_IND}',''), '[^,]+', 1, LEVEL) IS NOT NULL )
|
|
INNER JOIN TRD_TERM F ON B.trade_num = F.trade_num
|
|
INNER JOIN REF_PAYMENT_TERM E ON B.settle_payment_term_cd = E.payment_term_cd
|
|
INNER JOIN TRD_HEADER G ON F.trade_num = G.trade_num AND F.internal_side_ind = G.internal_side_ind --2014.02.25 추가
|
|
WHERE 1=1
|
|
AND B.TRADE_NUM = P_TRADE_NUM
|
|
AND B.ITEM_STATUS_IND <> 0
|
|
AND B.PAYMENT_STATUS_IND <> 2
|
|
AND (SELECT COUNT(*)
|
|
FROM STL_ITEM_HDR
|
|
WHERE PAYMENT_STATUS_IND = 2
|
|
AND ITEM_STATUS_IND IN (2, 3)
|
|
AND EXTERNAL_KEY = B.EXTERNAL_KEY) = 0
|
|
AND B.INTERNAL_COMPANY_NUM NOT IN (1668, 2307, 2142)
|
|
AND B.COST_TYPE_CD IN
|
|
( 'FB Chartering_CR'
|
|
, 'FB Chartering_DR'
|
|
, 'FB Chartering'
|
|
, 'Primary Settlement'
|
|
, 'Provisional Price'
|
|
, 'Provisional Price Rv'
|
|
, 'Purchase Adjustment'
|
|
, 'Sales Adjustment'
|
|
, 'Barging Fee'
|
|
, 'Barging Cost'
|
|
, 'Barging Cost_CR'
|
|
, 'Barging Cost_DR')
|
|
AND ( NVL(B.INSTRUMENT_CLASS_CD, '') = 'Physical' OR NVL(G.CLEARED_IND, -1) = -1 )
|
|
AND ( NVL(B.INSTRUMENT_CLASS_CD, '') <> 'Physical'
|
|
OR (
|
|
UPPER(NVL(B.DEAL_ID2, '')) NOT LIKE '%FPP%'
|
|
AND UPPER(NVL(B.DEAL_ID2, '')) NOT LIKE '%TARS%'
|
|
AND UPPER(NVL(B.DEAL_ID2, '')) NOT LIKE '%KNOCK OUT%'
|
|
)
|
|
OR NVL( TO_CHAR( B.UNDERLYING_START_DT, 'YYYYMMDD'), '19000101') <= TO_CHAR( SYSDATE, 'YYYYMMDD')
|
|
)
|
|
AND B.TRADE_STATUS_IND NOT IN (5, 6) /* 5:Void , 6:What If */
|
|
AND ( NVL(B.INSTRUMENT_CLASS_CD, '') <> 'Physical'
|
|
OR A.CREDIT_NETTING_IND = 1
|
|
OR B.DELIVERY_TYPE_IND IN (2, 3)
|
|
OR ((A.CREDIT_NETTING_IND = 0 OR A.CREDIT_NETTING_IND IS NULL) AND B.STL_BUY_SELL_IND = -1)
|
|
)
|
|
AND E.INTERFACE_VALUE_CODE = 'TT'
|
|
AND B.COUNTERPART_COMPANY_NUM IN (
|
|
SELECT COMPANY_NUM FROM REF_COMPANY
|
|
WHERE ( COMPANY_TYPE_IND = '0' AND COMPANY_NUM NOT IN (3739,3743,3745) )
|
|
OR ( COMPANY_TYPE_IND = '10' AND COMPANY_CD NOT IN ( 'SKEE', 'SKEA','SKEI' ))
|
|
)
|
|
AND B.SPLIT_ITEM_IND <> '1' /* 0:N/A, 1:Source, 2:Split. Split 된 경우, Source는 신용 산출 대상에서 제외. split 항목과 중복으로 계산됨 */
|
|
) X
|
|
GROUP BY X.TRADE_NUM;
|
|
|
|
RETURN RET_VALUE ;
|
|
|
|
END;
|
|
/
|