Untitled
user_8843816
plain_text
13 days ago
10 kB
1
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