客户 外币账龄报表
*&---------------------------------------------------------------------*
*& Report ZDBCO_SD_CREDIT_INFO
*&
*&---------------------------------------------------------------------*
*&from wjg:zwbzl + 授信
*&tcode: zsd10
*&---------------------------------------------------------------------*REPORT ZDBCO_SD_CREDIT_INFO.TABLES:KNKK,BSID,KNA1,BSAD,T691T.TYPES:BEGIN OF WTAB,KONZS LIKE KNA1-KONZS, "組代碼KUNNR LIKE KNA1-KUNNR, "客戶號NAME1 LIKE KNA1-NAME1, "姓名SKFOR LIKE KNKK-SKFOR, "應收總額SAUFT LIKE KNKK-SAUFT, "銷售額KLIMK LIKE KNKK-KLIMK, "信貸限額CTLPC LIKE KNKK-CTLPC, "信貸風險類別KNKLI LIKE KNKK-KNKLI, "借貸賬號KKBER LIKE KNKK-KKBER, "信貸范圍OBLIG LIKE KNKK-KLIMK, "信貸風險總額WAERB LIKE BSID-WAERS, "信貸幣種WRBTR LIKE KNKK-KLIMK, "超賬期金額WRBTR0 LIKE KNKK-KLIMK,WRBTR1 LIKE KNKK-KLIMK,WRBTR2 LIKE KNKK-KLIMK,WRBTR3 LIKE KNKK-KLIMK,WRBTR4 LIKE KNKK-KLIMK,WRBTR5 LIKE KNKK-KLIMK,WRBTR6 LIKE KNKK-KLIMK,WRBTR7 LIKE KNKK-KLIMK,WRBTR8 LIKE KNKK-KLIMK,WRBTR9 LIKE KNKK-KLIMK,WAERS LIKE BSID-WAERS,RTEXT LIKE T691T-RTEXT, "風險種類描述
END OF WTAB.DATA:STDATUM LIKE SY-DATUM,STUZEIT LIKE SY-UZEIT,GT_DATA TYPE TABLE OF WTAB,GS_DATA TYPE WTAB,GT_FIELDCAT TYPE LVC_T_FCAT,GS_FIELDCAT TYPE LVC_S_FCAT,GS_LAYOUT TYPE LVC_S_LAYO.DATA:BEGIN OF gd,d1 TYPE d,d2 TYPE d,d3 TYPE d,d4 TYPE d,d5 TYPE d,d6 TYPE d,d7 TYPE d,d8 TYPE d,
END OF gd.*t_itab類型處理未清類型憑證
TYPES:BEGIN OF t_itab,belnr LIKE bsid-belnr, "憑證編號kunnr LIKE bsid-kunnr, "客戶編號1name1 LIKE kna1-name1, "名稱1blart LIKE bsid-blart, "憑證類型waers LIKE bsid-waers, "貸幣代碼dmbtr LIKE bsid-dmbtr, "按本位幣的金額wrbtr LIKE bsid-wrbtr, "憑證貨幣金額shkzg LIKE bsid-shkzg, "借方/貨方標識(H 為負)budat LIKE bsid-budat, "記帳日期rebzg LIKE bsid-rebzg, "發票參考號rebzj LIKE bsid-rebzj, "參考發票的會計年度budat2 LIKE bsid-budat, "參考發票的日期
END OF t_itab.DATA:itab TYPE TABLE OF t_itab WITH NON-UNIQUE KEY kunnr waers WITH HEADER LINE.SELECTION-SCREEN BEGIN OF BLOCK 001 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS S_KUNNR FOR KNA1-KUNNR DEFAULT '400000' to '610000'. "物料代碼
SELECT-OPTIONS S_BUKRS FOR BSID-BUKRS DEFAULT '1000'. "工廠
SELECT-OPTIONS S_KKBER FOR KNKK-KKBER DEFAULT '2000' no INTERVALS. "信貸范圍
SELECT-OPTIONS S_GSBER FOR BSID-GSBER DEFAULT '0001'. "業務范圍
PARAMETERS:PBUDAT LIKE BSID-BUDAT DEFAULT sy-datum.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 01(31) TEXT-002.
*modified by jack_cao 2007.12.07
PARAMETERS: rast1(4) TYPE C DEFAULT '0000'.
PARAMETERS: rast2(4) TYPE C DEFAULT '0030'.
PARAMETERS: rast3(4) TYPE C DEFAULT '0060'.
PARAMETERS: rast4(4) TYPE C DEFAULT '0090'.
PARAMETERS: rast5(4) TYPE C DEFAULT '0120'.
PARAMETERS: rast6(4) TYPE C DEFAULT '0150'.
PARAMETERS: rast7(4) TYPE C DEFAULT '0180'.
PARAMETERS: rast8(4) TYPE C DEFAULT '0210'.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN ULINE.SELECTION-SCREEN END OF BLOCK 001.SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE TXT2.PARAMETERS: PCONME1 LIKE DBCON-CON_NAME DEFAULT 'ORA_209MES'.
SELECTION-SCREEN END OF BLOCK B2 .INITIALIZATION.TXT2 = '目標數據庫(不選擇即空著,當前目標不同步)'.START-OF-SELECTION.STDATUM = SY-DATUM.STUZEIT = SY-UZEIT.PERFORM GET_DATA.PERFORM PROCESS_DATA.if PCONME1 is not INITIAL and pconme1 NE space.PERFORM UPLOAD2DBCENTER USING PCONME1.endif.PERFORM WRITERPTRUNRECORD.PERFORM DISPLAY_DATA.*&---------------------------------------------------------------------*
*& Form UPLOAD2DBCENTER
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_DBNAME text
* -->CNT text
*----------------------------------------------------------------------*
FORM UPLOAD2DBCENTER USING P_DBNAME like DBCON-CON_NAME.DATA:lcnt TYPE I .DATA: lcx_error TYPE REF TO cx_root.DATA: err_text TYPE C LENGTH 1000.DATA: ll_lines(10),"行數ll_tabix(10),"循環標號ll_pecnt TYPE p LENGTH 6 DECIMALS 2,"百分比ll_pecet(6),"百分數ll_text(40)."消息CLEAR: ll_lines,ll_tabix,ll_pecnt,ll_pecet,ll_text.TRY.PERFORM GETCON USING P_DBNAME.DESCRIBE TABLE GT_DATA LINES ll_lines."獲取內表行數LOOP AT GT_DATA into gs_Data.ll_tabix = sy-tabix."當前處理的行號ll_pecnt = ll_tabix / ll_lines * 100."已處理百分比ll_pecet = ll_pecnt."拼接顯示消息CONCATENATE '上傳209中間庫:已完成' ll_pecet '%,共' ll_tabix '條/' ll_lines '條' INTO ll_text.CONDENSE ll_text."整理格式,去掉多余空格,格式更整齊CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'"調用進度條函數,傳入百分比和消息EXPORTINGpercentage = ll_pecntTEXT = ll_text.SHIFT GS_DATA-KUNNR LEFT DELETING LEADING '0'.SHIFT GS_DATA-KONZS LEFT DELETING LEADING '0'.SHIFT GS_DATA-KNKLI LEFT DELETING LEADING '0'.lcnt = lcnt + 1.EXEC SQL.DELETE FROM CRM_ACCOUNTS_RECEIVABLE_ERP WHERE CUSTNO = :GS_DATA-KUNNRENDEXEC.commit WORK AND WAIT.EXEC SQL.INSERT INTO CRM_ACCOUNTS_RECEIVABLE_ERP(GRPNO,CUSTNO,CUSTNAME,SKFOR,SAUFT,OBLIG,KLIMK,CTLPC,KNKLI,WAERB,KKBER,WRBTR,WRBTR0,WRBTR1,WRBTR2,WRBTR3,WRBTR4,WRBTR5,WRBTR6,WRBTR7,WRBTRN,WAERS,RTEXT )VALUES(:GS_DATA-KONZS,:GS_DATA-KUNNR,:GS_DATA-NAME1,:GS_DATA-SKFOR,:GS_DATA-SAUFT,:GS_DATA-OBLIG,:GS_DATA-KLIMK,:GS_DATA-CTLPC,:GS_DATA-KNKLI,:GS_DATA-WAERB,:GS_DATA-KKBER,:GS_DATA-WRBTR,:GS_DATA-WRBTR1,:GS_DATA-WRBTR2,:GS_DATA-WRBTR3,:GS_DATA-WRBTR4,:GS_DATA-WRBTR5,:GS_DATA-WRBTR6,:GS_DATA-WRBTR7,:GS_DATA-WRBTR8,:GS_DATA-WRBTR9,:GS_DATA-WAERS,:GS_DATA-RTEXT )ENDEXEC.endloop.PERFORM CLOSECON USING P_DBNAME.CATCH cx_sy_open_sql_db CX_SY_NATIVE_SQL_ERROR INTO lcx_error.err_text = lcx_error->get_text( ). "得到錯誤信息WRITE:/ SY-VLINE, '上傳數據到',P_DBNAME,'失敗,原因:',err_text , SY-VLINE.ENDTRY.WRITE:/ SY-VLINE, '上傳數據',lcnt,'/',ll_lines,'到',P_DBNAME,'成功', SY-VLINE.WRITE:/ SY-ULINE.
ENDFORM. "UPLOAD2DBCENTER*&---------------------------------------------------------------------*
*& Form GET_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM GET_DATA.data:sauft1 like s066-OEIKW.data:sauft2 like s067-OLIKW.SELECT KONZS kna1~KUNNR NAME1 SKFOR SAUFT KLIMK CTLPC KNKLI KNKK~KKBERinto TABLE GT_DATAFROM KNA1JOIN KNKK ON KNKK~KUNNR = KNA1~KUNNRWHERE kna1~KUNNR IN S_KUNNR AND KKBER IN S_KKBER.loop at gt_Data into gs_data.IF gs_data-KKBER = '2000'.select sum( OLIKW ) into sauft2 from s067where s067~kkber = gs_data-kkber and s067~KNKLI = gs_data-knkli.SELECT SUM( OEIKW ) INTO sauft1 FROM s066WHERE s066~kkber = gs_data-kkber AND s066~KNKLI = gs_data-knkli.gs_data-sauft = sauft1 + sauft2.gs_data-OBLIG = gs_data-SKFOR + gs_data-SAUFT.gs_data-WAERB = 'USD'.endif.SELECT SINGLE rtext INTO Gs_data-RTEXTFROM T691T WHERE kkber = gs_data-kkber AND CTLPC = gs_data-CTLPC and spras = 1.modify gt_data from gs_Data.ENDLOOP.
ENDFORM. "GET_DATA*&---------------------------------------------------------------------*
*& Form PROCESS_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM PROCESS_DATA.PERFORM getgd.loop at gt_data into gs_Data.FIELD-SYMBOLS:<itab> LIKE itab ,<fs> LIKE itab .DATA:loc_budat LIKE bsid-budat,loc_id TYPE I .
************ 獲取未清項SELECTbsid~belnrbsid~kunnrkna1~name1bsid~blartbsid~waersSUM( bsid~dmbtr )SUM( bsid~wrbtr )bsid~shkzgbsid~budatbsid~rebzgbsid~rebzjINTO TABLE itabFROM bsid INNER JOIN kna1 ON ( bsid~kunnr = kna1~kunnr )WHERE bsid~bukrs in s_bukrs ANDbsid~kunnr EQ gs_data-KUNNR ANDbsid~waers EQ gs_data-WAERB ANDbsid~budat <= pbudat ANDbsid~gsber IN s_gsber ANDbsid~umskz EQ spaceGROUP BY bsid~belnrbsid~kunnrkna1~name1bsid~blartbsid~shkzgbsid~waersbsid~budatbsid~rebzgbsid~rebzj.LOOP AT itab ASSIGNING <fs>.IF <fs>-shkzg EQ 'H' .<fs>-dmbtr = -1 * <fs>-dmbtr .<fs>-wrbtr = -1 * <fs>-wrbtr .ENDIF .IF <fs>-blart = 'AB' AND <fs>-rebzg NE space .SELECT SINGLEbsid~budatFROM bsidINTO <fs>-budat2WHERE bsid~belnr EQ <fs>-rebzgAND bsid~gjahr EQ <fs>-rebzj .IF sy-subrc NE 0 .SELECT SINGLEbsad~budatFROM bsadINTO <fs>-budat2WHERE bsad~belnr EQ <fs>-rebzg AND bsad~gjahr EQ <fs>-rebzj .ENDIF .<fs>-budat = <fs>-budat2.ENDIF.gs_data-WRBTR = gs_data-WRBTR + <FS>-WRBTR.IF <fs>-budat >= gd-D1.gs_data-WRBTR1 = gs_data-WRBTR1 + <FS>-WRBTR.ELSEIF <fs>-budat >= gd-D2.gs_data-WRBTR2 = gs_data-WRBTR2 + <FS>-WRBTR.ELSEIF <fs>-budat >= gd-D3.gs_data-WRBTR3 = gs_data-WRBTR3 + <FS>-WRBTR.ELSEIF <fs>-budat >= gd-D4.gs_data-WRBTR4 = gs_data-WRBTR4 + <FS>-WRBTR.ELSEIF <fs>-budat >= gd-D5.gs_data-WRBTR5 = gs_data-WRBTR5 + <FS>-WRBTR.ELSEIF <fs>-budat >= gd-D6.gs_data-WRBTR6 = gs_data-WRBTR6 + <FS>-WRBTR.ELSEIF <fs>-budat >= gd-D7.gs_data-WRBTR7 = gs_data-WRBTR7 + <FS>-WRBTR.ELSEIF <fs>-budat >= gd-D8.gs_data-WRBTR8 = gs_data-WRBTR8 + <FS>-WRBTR.Else.gs_data-WRBTR9 = gs_data-WRBTR9 + <FS>-WRBTR.endif.ENDLOOP .gs_data-WAERS = gs_data-WAERB.MODIFY gt_data FROM gs_data.ENDLOOP.
ENDFORM. "PROCESS_DATA*&---------------------------------------------------------------------*
*& Form getgd
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM GETGD.DATA:LOC_NAME(10),LOC_TOTAL TYPE I,LOC_RASTBIS(4) TYPE C ,
* LOC_RASTBIS TYPE ALLGROGR,LOC_POINT TYPE D.FIELD-SYMBOLS:<LOC_P> TYPE D.DO 8 TIMES.CLEAR LOC_NAME.LOC_NAME+0(1) = SY-INDEX.CONCATENATE 'RAST' LOC_NAME INTO LOC_NAME.CLEAR LOC_RASTBIS.WRITE (LOC_NAME) TO LOC_RASTBIS.ASSIGN COMPONENT SY-INDEX OF STRUCTURE GD TO <LOC_P>.<LOC_P> = PBUDAT - LOC_RASTBIS.ENDDO.
ENDFORM. "PROCESS_DATA*&---------------------------------------------------------------------*
*& FORM WRITERPTRUNRECORD
*&---------------------------------------------------------------------*
* 獲取數據
*----------------------------------------------------------------------*
FORM WRITERPTRUNRECORD.TYPES:BEGIN OF TYP_BW,BUKRS LIKE T001-BUKRS ,WERKS LIKE T001W-WERKS ,END OF typ_BW.DATA:GT_BW TYPE TABLE OF TYP_BW,GS_BW TYPE TYP_BW.GET TIME.CALL FUNCTION 'ZSYS_RPT_RUNRECORD'EXPORTINGTCODE = SY-TCODEPROGRAMM = SY-CPROGDATUMRST = STDATUMUZEITRST = STUZEITBATCH = SY-BATCHDATUMREND = SY-datumUZEITREND = sy-UZEITDATUMC = '20220510'RUNNER = SY-UNAMERPTSRC = 'SD 王建國'.ENDFORM. "WRITERPTRUNRECORD*&---------------------------------------------------------------------*
*& FORM DISPLAY_DATA
*&---------------------------------------------------------------------*
* 顯示數據
*----------------------------------------------------------------------*
FORM DISPLAY_DATA.PERFORM BUILD_FIELDCAT USING 'KONZS' '' '' '' '' '' '' '組代碼' 'X'.PERFORM BUILD_FIELDCAT USING 'KUNNR' '' '' '' '' '' '' '客戶號' 'X'.PERFORM BUILD_FIELDCAT USING 'NAME1' '' '' '' '' '' '' '客戶名稱' 'X'.PERFORM BUILD_FIELDCAT USING 'SKFOR' '' '' '' '' '' '' '應收總額' 'X'.PERFORM BUILD_FIELDCAT USING 'SAUFT' '' '' '' '' '' '' '銷售額' 'X'.PERFORM BUILD_FIELDCAT USING 'OBLIG' '' '' '' '' '' '' '信貸風險' 'X'.PERFORM BUILD_FIELDCAT USING 'KLIMK' '' '' '' '' '' '' '信貸限額' 'X'.PERFORM BUILD_FIELDCAT USING 'CTLPC' '' '' '' '' '' '' '信貸風險' 'X'.PERFORM BUILD_FIELDCAT USING 'KNKLI' '' '' '' '' '' '' '信貸賬戶' 'X'.PERFORM BUILD_FIELDCAT USING 'KKBER' '' '' '' '' '' '' '信貸范圍' 'X'.PERFORM BUILD_FIELDCAT USING 'WAERB' '' '' '' '' '' '' '信貸幣種' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR' '' '' '' '' '' '' '超賬期總金額' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR1' '' '' '' '' '' '' '賬齡1' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR2' '' '' '' '' '' '' '賬齡2' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR3' '' '' '' '' '' '' '賬齡3' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR4' '' '' '' '' '' '' '賬齡4' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR5' '' '' '' '' '' '' '賬齡5' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR6' '' '' '' '' '' '' '賬齡6' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR7' '' '' '' '' '' '' '賬齡7' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR8' '' '' '' '' '' '' '賬齡8' 'X'.PERFORM BUILD_FIELDCAT USING 'WRBTR9' '' '' '' '' '' '' '賬齡9' 'X'.PERFORM BUILD_FIELDCAT USING 'WAERS' '' '' '' '' '' '' '憑證貨幣' 'X'.gs_layout-cwidth_opt = 'X'.gs_layout-zebra = 'X'.CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'EXPORTINGI_CALLBACK_PROGRAM = SY-REPID" I_CALLBACK_PF_STATUS_SET = 'PF_STATUS_ALV'I_CALLBACK_USER_COMMAND = 'USER_COMMAND_ALV'IT_FIELDCAT_LVC = GT_FIELDCATIS_LAYOUT_LVC = GS_LAYOUTI_DEFAULT = 'X'I_SAVE = 'A'TABLEST_OUTTAB = GT_DATAEXCEPTIONSOTHERS = 1.CLEAR GT_DATA[].
ENDFORM. "DISPLAY_DATA*&---------------------------------------------------------------------*
*& Form BUILD_FIELDCAT
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
FORM BUILD_FIELDCAT USING P_FIELDNAME TYPE FIELDNAMEP_QFIELDNAME TYPE LVC_QFNAMEP_CFIELDNAME TYPE LVC_CFNAMEP_REF_TABLE TYPE LVC_RTNAMEP_REF_FIELD TYPE LVC_RFNAMEP_CONVEXIT TYPE CONVEXITP_EMPHASIZE TYPE LVC_EMPHSZP_SCRTEXT_L TYPE SCRTEXT_LP_NO_ZERO TYPE XFLAG.GS_FIELDCAT-FIELDNAME = P_FIELDNAME.GS_FIELDCAT-QFIELDNAME = P_QFIELDNAME.GS_FIELDCAT-CFIELDNAME = P_CFIELDNAME.GS_FIELDCAT-REF_TABLE = P_REF_TABLE.GS_FIELDCAT-REF_FIELD = P_REF_FIELD.GS_FIELDCAT-CONVEXIT = P_CONVEXIT.GS_FIELDCAT-EMPHASIZE = P_EMPHASIZE.GS_FIELDCAT-SCRTEXT_L = P_SCRTEXT_L.GS_FIELDCAT-NO_ZERO = P_NO_ZERO.GS_FIELDCAT-COLDDICTXT = 'L'.APPEND GS_FIELDCAT TO GT_FIELDCAT.CLEAR: GS_FIELDCAT.
ENDFORM. "BUILD_FIELDCAT**&---------------------------------------------------------------------*
**& FORM PF_STATUS_ALV
**&---------------------------------------------------------------------*
** TEXT
**----------------------------------------------------------------------*
*FORM PF_STATUS_ALV USING RT_EXTAB TYPE SLIS_T_EXTAB .
* SET PF-STATUS 'STATUS_ALV' EXCLUDING RT_EXTAB.
*ENDFORM. "PF_STATUS_ALV
*&---------------------------------------------------------------------*
*& FORM USER_COMMAND_ALV
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
FORM USER_COMMAND_ALV USING R_UCOMM LIKE SY-UCOMMRS_SELFIELD TYPE SLIS_SELFIELD.CASE R_UCOMM.WHEN '&IC1'.CASE RS_SELFIELD-FIELDNAME.WHEN ''.
* CHECK RS_SELFIELD-VALUE IS NOT INITIAL.
* SET PARAMETER ID 'MBN' FIELD RS_SELFIELD-VALUE.
* CALL TRANSACTION 'MB03' AND SKIP FIRST SCREEN.ENDCASE.ENDCASE.
ENDFORM. "USER_COMMAND_ALV*&---------------------------------------------------------------------*
*& FORM GETCON
*&---------------------------------------------------------------------*
* TEXT 調用外部數據庫
*----------------------------------------------------------------------*
FORM GETCON USING P_DBNAME LIKE DBCON-CON_NAME.EXEC SQL.CONNECT TO :P_DBNAMEENDEXEC.IF SY-SUBRC NE 0.WRITE:/ '連接數據庫失敗'.ENDIF.EXEC SQL.SET CONNECTION :P_DBNAMEENDEXEC.
ENDFORM. "GETCON*&---------------------------------------------------------------------*
*& FORM CLOSECON
*&---------------------------------------------------------------------*
* TEXT 關閉外部數據庫
*----------------------------------------------------------------------*FORM CLOSECON USING P_DBNAME LIKE DBCON-CON_NAME.COMMIT CONNECTION (P_DBNAME).EXEC SQL.DISCONNECT :P_DBNAMEENDEXEC.
ENDFORM. "CLOSECON
總結
- 上一篇: 宝鲲财经:三大外汇交易大忌
- 下一篇: 瑞吉外卖(五)