Untitled

 avatar
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