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;
/