Untitled
user_8843816
plain_text
a month ago
10 kB
2
Indexable
SELECT
T.BIZ_KEY
, T.USER_ID
, T.DOC_ID
, T.HAND_DOC_ID
, T.ISSU_TAX_ID
, T.DOC_TYPE
, T.ISSU_CMNM
, T.ISSU_CMENM
, T.ISSU_RPPR_NM
, T.ISSU_RPRS_TLPH
, T.ISSU_FXNO
, T.ISSU_BSUN_POST_ADRS
, T.ISSU_BSUN_DTL_ADRS
, T.ISSU_BSUN_ST
, T.ISSU_BSUN_CITY
, T.TRCO_KEY
, T.TRCO_TAX_ID
, T.TRCO_CMNM
, T.TRCO_CMENM
, T.TRCO_RPPR_NM
, T.TRCO_RPRS_TLPH
, T.TRCO_FXNO
, T.TRCO_BSUN_POST_ADRS
, T.TRCO_BSUN_DTL_ADRS
, T.TRCO_BSUN_ST
, T.TRCO_BSUN_CITY
, T.TRCO_TRSC_CCY_CD
, T.ISSU_DT
, T.DUE_DT
, T.PAY_DUE_CD
, T.BUSA_USAG_CD
, T.USAG_NM
, TOTL_AMT::NUMERIC AS TOTL_AMT
, T.SPLY_AMT_SUM
, T.TAX_AMT_SUM
, T.PRJT_NO
, T.PRJT_NM
, T.TRSC_MSG
, T.PROV_STTS
, T.EML_TRF_DTM
, T.CONT
, T.USE_DT
, T.USER_NM
, T.MEMO
, T.COLOR
, T.APPR_SEQ_NO
, T.APLY_EXCH_RATE
, T.REG_CHNL
, T.PC_MOD_YN
, T.REG_DT
, T.SEQ_NO
, T.ITEM_DETL_USE_YN
, T.PRJT_USE_YN
, T.TRCO_TYPE
, T.RCV_YN
, T.TRCO_BSUN_ADRS
, T.NOTE
, T.DOC_TITLE
, T.REG_ID
, T.REG_NM
, T.REG_DTM
, T.MOD_ID
, T.MOD_NM
, T.MOD_DTM
, T.BUSA_DOC_FILE
, T.TAX_TYPE
, T.BAL
, T.WHT_CD
, T.INV_TYPE
, T.HAND_REG_YN
, T.EINV_INVOICEINFO
, T.TOTL_DIS_AMT
, T.EINV_INVOICESTATUS
, T.EINV_CHECKRESULTS
, T.EINV_NUMERICSYMBOL
, T.EINV_INVOICESYMBOL
, T.EINV_TITLE
, T.EINV_MCCQT
, T.EINV_BUYERBANKACC
, T.EINV_SELLERBANKACC
, T.EINV_PAYMENTTYPE
, T.EINV_LISTNUMBER
, T.EINV_LISTDATE
, T.EINV_TOTALAMTINLETTER
, T.EINV_BUYERSIGNBY
, T.EINV_BUYERSIGNDATE
, T.EINV_BUYERSIGNTIME
, T.EINV_SELLERSIGNBY
, T.EINV_SELLERSIGNDATE
, T.EINV_SELLERSIGNTIME
, T.EINV_QRCODE
, T.EINV_BUYERFULLNAME
, T.TOTL_FEE_AMT
, T.EINV_BUYERBANKNAME
, T.EINV_SELLERBANKNAME
, T.DISP_DOC_ID
, T.WEIN_STTS
, T.WEIN_ID
FROM (
SELECT
BDM.BIZ_KEY
, BDM.USER_ID
, BDM.DOC_ID
, BDM.HAND_DOC_ID
, BDM.DISP_DOC_ID
, BDM.ISSU_TAX_ID
, BDM.DOC_TYPE
, BDM.ISSU_CMNM
, BDM.ISSU_CMENM
, BDM.ISSU_RPPR_NM
, BDM.ISSU_RPRS_TLPH
, BDM.ISSU_FXNO
, BDM.ISSU_BSUN_POST_ADRS
, BDM.ISSU_BSUN_DTL_ADRS
, BDM.ISSU_BSUN_ST
, BDM.ISSU_BSUN_CITY
, BDM.TRCO_KEY
,(CASE TRCO_TYPE WHEN '3' THEN BDM.GST_NO ELSE BDM.TRCO_TAX_ID END) AS TRCO_TAX_ID
, BDM.TRCO_CMNM
, BDM.TRCO_CMENM
, BDM.TRCO_RPPR_NM
, BDM.TRCO_RPRS_TLPH
, BDM.TRCO_FXNO
, BDM.TRCO_BSUN_POST_ADRS
, BDM.TRCO_BSUN_DTL_ADRS
, BDM.TRCO_BSUN_ST
, BDM.TRCO_BSUN_CITY
, BDM.TRCO_TRSC_CCY_CD
, BDM.ISSU_DT
, BDM.DUE_DT
, BDM.PAY_DUE_CD
, BDM.BUSA_USAG_CD
, WB_GET_USAG_NM (BDM.BIZ_KEY,BDM.BUSA_USAG_CD,'EN') USAG_NM
, BDM.TOTL_AMT
, BDM.SPLY_AMT_SUM
, BDM.TAX_AMT_SUM
, BDM.PRJT_NO
, WB_GET_PRJT_NM (BDM.BIZ_KEY, BDM.PRJT_NO) AS PRJT_NM
, BDM.TRSC_MSG
, BDM.PROV_STTS
, BDM.EML_TRF_DTM
, (CASE DOC_TYPE WHEN 'E' THEN BDM.CONT ELSE BDM.DOC_TITLE END) AS CONT
, BDM.USE_DT
, BDM.USER_NM
, wb_get_memo_cont(BDM.BIZ_KEY,'sale_0001_001',BDM.DOC_ID,'','','','','','','','') AS MEMO
, wb_get_memo_color(BDM.BIZ_KEY,'sale_0001_001',BDM.DOC_ID,'','','','','','','','') AS COLOR
, BDM.APPR_SEQ_NO
, BDM.APLY_EXCH_RATE
, BDM.REG_CHNL
, BDM.PC_MOD_YN
, BDM.REG_DT
, BDM.SEQ_NO
, BDM.ITEM_DETL_USE_YN
, BDM.PRJT_USE_YN
, BDM.TRCO_TYPE
,(CASE WHEN BDM.INV_TYPE ='C' THEN 'Commercial' ELSE 'Tax' END ) INV_TYPE
--, CASE WHEN BDRP.RCV_PAY_WAY_CD IS NULL OR BDRP.RCV_PAY_WAY_CD='' THEN 'N' ELSE 'Y' END AS RCV_YN
, CASE WHEN (SELECT count(*) FROM BUSA_DOC_RCV_PAY_HIS WHERE 1=1 AND BIZ_KEY = BDM.BIZ_KEY AND DOC_ID = BDM.DOC_ID ) > 0 THEN 'Y' ELSE 'N' END AS RCV_YN
, concat(TRCO_BSUN_POST_ADRS,',',TRCO_BSUN_DTL_ADRS,',',TRCO_BSUN_CITY,',',TRCO_BSUN_ST) AS TRCO_BSUN_ADRS
/* , (CASE WHEN BDRP.RCV_PAY_WAY_CD = 'Q' THEN 'Cheque' || ' (' || wb_get_bank_nm(BDRP.BANK_CD,'s')||','|| BDRP.REF_NO || ')' || ', ' || BDRP.TRSC_DT
WHEN BDRP.RCV_PAY_WAY_CD = 'C' THEN 'Cash' || ', ' || BDRP.TRSC_DT
WHEN BDRP.RCV_PAY_WAY_CD = 'A' THEN 'TRANSFER ' || '(' || wb_get_bank_nm(BDRP.BANK_CD,'s') || ' ' || ACCT_NO || ')' || ', ' || BDRP.TRSC_DT
END) RCV_PAY_WAY_CD
*/
,(SELECT json_agg(
(CASE
WHEN RCV_PAY_WAY_CD = 'Q' THEN
json_build_object('RCV_PAY_WAY_CD', RCV_PAY_WAY_CD, 'BANK_NM', wb_get_bank_nm(BANK_CD,'s'), 'REF_NO', REF_NO,'TRSC_DT', TRSC_DT)
WHEN RCV_PAY_WAY_CD = 'C' THEN
json_build_object('RCV_PAY_WAY_CD', RCV_PAY_WAY_CD,'TRSC_DT', TRSC_DT)
WHEN RCV_PAY_WAY_CD = 'A' THEN
json_build_object('RCV_PAY_WAY_CD', RCV_PAY_WAY_CD, 'BANK_NM', wb_get_bank_nm(BANK_CD,'s') , 'ACCT_NO', ACCT_NO,'TRSC_DT', TRSC_DT)
WHEN RCV_PAY_WAY_CD = 'W' THEN
json_build_object('DOC_ID' , DOC_ID , 'RCV_PAY_WAY_CD', RCV_PAY_WAY_CD, 'BANK_NM',coalesce( wb_get_bank_nm(BANK_CD,'s'),'PPCB') , 'ACCT_NO', ACCT_NO,'TRSC_DT', TRSC_DT, 'REF_NO', REF_NO)
END)
)
FROM (
SELECT
RCV_PAY_WAY_CD
,BANK_CD
,REF_NO
,TRSC_DT
,ACCT_NO
FROM BUSA_DOC_RCV_PAY_HIS
WHERE 1=1
AND BIZ_KEY = BDM.BIZ_KEY
AND DOC_ID = BDM.DOC_ID
ORDER BY TRSC_DT DESC
) AS B
) NOTE
, BDM.DOC_TITLE
, BDM.REG_ID
, wb_get_user_nm(BDM.REG_ID) AS REG_NM
, BDM.REG_DTM
, BDM.MOD_ID
, wb_get_user_nm(BDM.MOD_ID) AS MOD_NM
, BDM.MOD_DTM
, BDM.WHT_CD
, wb_get_efiling_tax_type(BDM.BIZ_KEY, BDM.DOC_ID) AS TAX_TYPE
, (SELECT
(CASE
WHEN (C.BAL ISNULL) THEN BDM.TOTL_AMT
ELSE C.BAL
END)
FROM (SELECT
(BDM.TOTL_AMT - sum(DEDU_AMT)) AS BAL
FROM BUSA_DOC_RCV_PAY_HIS
WHERE 1=1
AND BIZ_KEY = BDM.BIZ_KEY
AND DOC_ID = BDM.DOC_ID
) C
)
, (
SELECT json_agg(
json_build_object(
'FILE_ID', BDF.FILE_ID,
'ORG_FILE_NM', BDF.ORG_FILE_NM,
'FILE_SIZE', BDF.FILE_SIZE
)
)
FROM BUSA_DOC_FILE BDF
WHERE BDF.BIZ_KEY = BDM.BIZ_KEY
AND BDF.DOC_ID = BDM.DOC_ID
) AS BUSA_DOC_FILE
, HAND_REG_YN
, EINV_INVOICEINFO
, TOTL_DIS_AMT
, EINV_INVOICESTATUS
, EINV_CHECKRESULTS
, EINV_NUMERICSYMBOL
, EINV_INVOICESYMBOL
, EINV_TITLE
, EINV_MCCQT
, EINV_BUYERBANKACC
, EINV_SELLERBANKACC
, EINV_PAYMENTTYPE
, EINV_LISTNUMBER
, EINV_LISTDATE
, EINV_TOTALAMTINLETTER
, EINV_BUYERSIGNBY
, EINV_BUYERSIGNDATE
, EINV_BUYERSIGNTIME
, EINV_SELLERSIGNBY
, EINV_SELLERSIGNDATE
, EINV_SELLERSIGNTIME
, EINV_QRCODE
, EINV_BUYERFULLNAME
, TOTL_FEE_AMT
, EINV_BUYERBANKNAME
, EINV_SELLERBANKNAME
, WEIN_STTS
, WEIN_ID
FROM BUSA_DOC_MSTR BDM
WHERE 1=1
and BDM.BIZ_KEY = 'KH_CAM0001_000195'
and BDM.ISSU_DT between '20240408' and '20250408'
and BDM.doc_type = 'I'
and BDM.PROV_STTS NOT IN (
'EE' , 'VD'
)
AND BDM.hand_reg_yn = 'Y'
) T
WHERE 1=1
order by issu_dt desc, lower(doc_id) collate "C" desc
Editor is loading...
Leave a Comment