1.ABAP函数代码FUNCTION zfm_get_dayn. *---------------------------------------------------------------------- **本地接口 * IMPORTING * VALUE(IT_BUKRS) TYPE FAGL_RANGE_T_BUKRS OPTIONAL * VALUE(IV_KEYDAT) TYPE DATS DEFAULT SY-DATUM * EXPORTING * VALUE(ET_DATA) TYPE ZTYT_DAY *---------------------------------------------------------------------- RANGES: rt_vstel FOR likp-vstel, rt_werks FOR t001w-werks. DATA ls_data TYPE zss_day. DATA ls_data_line TYPE zss_day_data. DATA ls_stock TYPE zss_stock_day. DATA lt_stock TYPE TABLE OF zss_stock_day. DATA lt_init_stock TYPE TABLE OF ty_stock. DATA lt_init_bcp_stock TYPE TABLE OF ty_stock. DATA lv_init_date TYPE dats. DATA out_bound TYPE ztyt_out. DATA in_bound TYPE ztyt_in_bound. DATA: BEGIN OF ls_mara, meins TYPE mara-meins, ferth TYPE mara-ferth, END OF ls_mara. DATA: lv_begda TYPE begda, lv_endda TYPE endda, lv_date TYPE dats. DATA: lv_do TYPE i. lv_endda iv_keydat. lv_do 10. lv_begda iv_keydat - ( lv_do - 1 ). SELECT bukrs,butxt INTO TABLE DATA(lt_t001) FROM t001 WHERE bukrs IN it_bukrs. PERFORM get_init_stock TABLES lt_init_stock lt_init_bcp_stock USING lv_begda CHANGING lv_init_date. 获取缓存表里最新的初始库存 LOOP AT lt_t001 INTO DATA(ls_t001). CLEAR: rt_vstel[],rt_werks[], rt_vstel,rt_werks. lv_date lv_begda. SELECT DISTINCT I AS sign, EQ AS option, a~vstel AS low INTO CORRESPONDING FIELDS OF TABLE rt_vstel FROM tvswz AS a INNER JOIN t001w AS b ON a~werks b~werks INNER JOIN t001k AS c ON b~bwkey c~bwkey WHERE c~bukrs ls_t001-bukrs. SELECT DISTINCT I AS sign, EQ AS option, a~werks AS low INTO CORRESPONDING FIELDS OF TABLE rt_werks FROM t001w AS a INNER JOIN t001k AS b ON a~bwkey b~bwkey WHERE b~bukrs ls_t001-bukrs. DO lv_do TIMES. * 每日出货数量 IF rt_vstel[] IS NOT INITIAL. SELECT b~matnr,c~maktx, mara~ferth,mara~meins, SUM( lfimg ) AS quantity FROM likp AS a INNER JOIN lips AS b ON a~vbeln b~vbeln INNER JOIN mara ON b~matnr mara~matnr LEFT JOIN makt AS c ON b~matnr c~matnr WHERE a~vstel IN rt_vstel AND a~wadat_ist lv_date AND a~lfart IN ( LF ) AND a~wbstk C AND c~spras 1 GROUP BY b~matnr,mara~ferth,mara~meins,c~maktx INTO CORRESPONDING FIELDS OF TABLE out_bound. ENDIF. CLEAR: ls_data_line-out_bound_total. LOOP AT out_bound INTO DATA(ls_out). ls_data_line-out_bound_total ls_data_line-out_bound_total ls_out-quantity. * 电芯数量 IF ls_out-ferth IS NOT INITIAL. ls_data_line-out_bound_ferth ls_data_line-out_bound_ferth ls_out-quantity * ls_out-ferth. ELSE. ls_data_line-out_bound_ferth ls_data_line-out_bound_ferth ls_out-quantity. ENDIF. CLEAR: ls_out. ENDLOOP. * 每日入库数量 SELECT a~matnr,c~maktx, mara~ferth,mara~meins, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity FROM mseg AS a INNER JOIN mkpf AS b ON a~mjahr b~mjahr AND a~mblnr b~mblnr INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS c ON a~matnr c~matnr WHERE a~bwart IN ( 101,102,531,532,109,110 ) AND a~werks IN rt_werks AND b~budat lv_date AND mara~mtart IN ( BATT,PACK ) AND c~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,c~maktx INTO CORRESPONDING FIELDS OF TABLE in_bound. CLEAR: ls_data_line-in_bound_total. LOOP AT in_bound INTO DATA(ls_in). ls_data_line-in_bound_total ls_data_line-in_bound_total ls_in-quantity. * 电芯数量 IF ls_in-ferth IS NOT INITIAL. ls_data_line-in_bound_ferth ls_data_line-in_bound_ferth ls_in-quantity * ls_in-ferth. ELSE. ls_data_line-in_bound_ferth ls_data_line-in_bound_ferth ls_in-quantity. ENDIF. CLEAR: ls_in. ENDLOOP. * 半成品入库数量 PERFORM get_bcp_in_bound TABLES rt_werks USING lv_date CHANGING ls_data_line. CLEAR: lt_stock. * 每日库存数量 SELECT a~matnr, mara~ferth,mara~meins, b~maktx, a~sobkz, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity INTO CORRESPONDING FIELDS OF TABLE lt_stock FROM mseg AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE mara~mtart IN ( BATT,PACK ) AND a~werks IN rt_werks AND a~budat_mkpf GE lv_init_date AND a~budat_mkpf LE lv_date AND a~bwart NOT IN (107,108,315,316,169,170) AND b~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,b~maktx,a~sobkz. LOOP AT lt_init_stock INTO DATA(ls_init_stock) WHERE werks IN rt_werks. MOVE-CORRESPONDING ls_init_stock TO ls_stock. COLLECT ls_stock INTO lt_stock. CLEAR: ls_init_stock,ls_stock. ENDLOOP. DELETE lt_stock WHERE quantity 0. CLEAR: ls_data_line-stock_total. LOOP AT lt_stock INTO ls_stock. CASE ls_stock-sobkz. 在途库存 WHEN T. ls_data_line-stock_total_t ls_data_line-stock_total_t ls_stock-quantity. IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_ferth_t ls_data_line-stock_ferth_t ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_ferth_t ls_data_line-stock_ferth_t ls_stock-quantity. ENDIF. WHEN OTHERS. 正常库存 ls_data_line-stock_total ls_data_line-stock_total ls_stock-quantity. * 电芯数量 IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_ferth ls_data_line-stock_ferth ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_ferth ls_data_line-stock_ferth ls_stock-quantity. ENDIF. ENDCASE. CLEAR: ls_stock. ENDLOOP. IF ls_data_line-stock_ferth 0. ls_data_line-stock_ferth 0. ENDIF. IF ls_data_line-stock_total 0. ls_data_line-stock_total 0. ENDIF. * 半成品电芯库存 PERFORM get_bcp_stock_new TABLES rt_werks lt_init_bcp_stock USING lv_init_date lv_date CHANGING ls_data_line. ls_data-bukrs ls_t001-bukrs. ls_data-butxt ls_t001-butxt. ls_data_line-date lv_date. APPEND ls_data_line TO ls_data-data. CLEAR: lt_stock,out_bound,in_bound,ls_data_line. lv_date lv_date 1. ENDDO. APPEND ls_data TO et_data. CLEAR:ls_t001,ls_data. ENDLOOP. ENDFUNCTION. *----------------------------------------------------------------------* ***INCLUDE LZFG_FI04F01. *----------------------------------------------------------------------* *---------------------------------------------------------------------* * Form get_bcp_kc *---------------------------------------------------------------------* * text *---------------------------------------------------------------------* * -- LV_DATE * -- LS_DATA *---------------------------------------------------------------------* FORM get_bcp_stock TABLES rt_werks STRUCTURE range_s_werks USING lv_date TYPE dats CHANGING ls_data_line TYPE zss_day_data. DATA lt_stock TYPE TABLE OF zss_stock_day. DATA ls_stock TYPE zss_stock_day. SELECT a~matnr, mara~ferth,mara~meins, b~maktx, a~sobkz, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity INTO CORRESPONDING FIELDS OF TABLE lt_stock FROM mseg AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100) AND a~werks IN rt_werks AND a~budat_mkpf GE 20150101 AND a~budat_mkpf LE lv_date AND a~bwart NOT IN (107,108,315,316,169,170) AND b~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,b~maktx,a~sobkz. DELETE lt_stock WHERE quantity 0. CLEAR: ls_data_line-stock_bcp_total. LOOP AT lt_stock INTO ls_stock. ls_data_line-stock_bcp_total ls_data_line-stock_bcp_total ls_stock-quantity. * 电芯数量 IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity. ENDIF. CLEAR: ls_stock. ENDLOOP. ENDFORM. FORM get_bcp_stock_new TABLES rt_werks STRUCTURE range_s_werks lt_init_bcp_stock STRUCTURE gs_stock USING lv_init_date TYPE dats lv_date TYPE dats CHANGING ls_data_line TYPE zss_day_data. DATA lt_stock TYPE TABLE OF zss_stock_day. DATA ls_stock TYPE zss_stock_day. SELECT a~matnr, mara~ferth,mara~meins, b~maktx, a~sobkz, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity INTO CORRESPONDING FIELDS OF TABLE lt_stock FROM mseg AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100) AND a~werks IN rt_werks AND a~budat_mkpf GE lv_init_date AND a~budat_mkpf LE lv_date AND a~bwart NOT IN (107,108,315,316,169,170) AND b~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,b~maktx,a~sobkz. LOOP AT lt_init_bcp_stock INTO DATA(ls_init_stock) WHERE werks IN rt_werks. MOVE-CORRESPONDING ls_init_stock TO ls_stock. COLLECT ls_stock INTO lt_stock. CLEAR: ls_init_stock,ls_stock. ENDLOOP. DELETE lt_stock WHERE quantity 0. CLEAR: ls_data_line-stock_bcp_total. LOOP AT lt_stock INTO ls_stock. ls_data_line-stock_bcp_total ls_data_line-stock_bcp_total ls_stock-quantity. * 电芯数量 IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity. ENDIF. CLEAR: ls_stock. ENDLOOP. ENDFORM. *---------------------------------------------------------------------* * Form get_bcp_in_bound *---------------------------------------------------------------------* * text *---------------------------------------------------------------------* * -- RT_WERKS * -- LV_DATE * -- LS_DATA_LINE *---------------------------------------------------------------------* FORM get_bcp_in_bound TABLES rt_werks STRUCTURE range_s_werks USING lv_date TYPE dats CHANGING ls_data_line TYPE zss_day_data. DATA in_bound TYPE ztyt_in_bound. SELECT a~matnr,c~maktx, mara~ferth,mara~meins, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity FROM mseg AS a INNER JOIN mkpf AS b ON a~mjahr b~mjahr AND a~mblnr b~mblnr INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS c ON a~matnr c~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100 ) AND a~bwart IN ( 101,102,531,532,109,110 ) AND a~werks IN rt_werks AND b~budat lv_date AND c~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,c~maktx INTO CORRESPONDING FIELDS OF TABLE in_bound. CLEAR: ls_data_line-in_bcp_total. LOOP AT in_bound INTO DATA(ls_in). ls_data_line-in_bcp_total ls_data_line-in_bcp_total ls_in-quantity. * 电芯数量 IF ls_in-ferth IS NOT INITIAL. ls_data_line-in_bcp_ferth ls_data_line-in_bcp_ferth ls_in-quantity * ls_in-ferth. ELSE. ls_data_line-in_bcp_ferth ls_data_line-in_bcp_ferth ls_in-quantity. ENDIF. CLEAR: ls_in. ENDLOOP. ENDFORM. *---------------------------------------------------------------------* * Form GET_INIT_STOCK *---------------------------------------------------------------------* * text *---------------------------------------------------------------------* * -- p1 text * -- p2 text *---------------------------------------------------------------------* FORM get_init_stock TABLES lt_init_stock STRUCTURE gs_stock lt_init_bcp_stock STRUCTURE gs_stock USING lv_begda CHANGING lv_init_date. DATA lv_kc_date TYPE dats. SELECT MAX( kc_date ) INTO lv_kc_date FROM zmmt0011 WHERE kc_date LE lv_begda. IF lv_kc_date IS NOT INITIAL. SELECT a~matnr, a~werks, mara~ferth,mara~meins, b~maktx, a~sobkz, a~quantity INTO CORRESPONDING FIELDS OF TABLE lt_init_stock FROM zmmt0011 AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE mara~mtart IN ( BATT,PACK ) AND a~kc_date lv_kc_date AND b~spras 1. SELECT a~matnr, a~werks, mara~ferth,mara~meins, b~maktx, a~sobkz, a~quantity INTO CORRESPONDING FIELDS OF TABLE lt_init_bcp_stock FROM zmmt0011 AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100) AND a~kc_date lv_kc_date AND b~spras 1. lv_init_date lv_kc_date 1. ELSE. lv_init_date 20150101. ENDIF. ENDFORM.2.转成AMDP实现2.1CDS代码table functionEndUserText.label: 获取每日出入库及库存数据 define table function ZTF_GET_DAYN with parameters Environment.systemField: #SYSTEM_DATE p_keydat : dats returns { client : abap.clnt; EndUserText.label: 公司代码 bukrs : bukrs; EndUserText.label: 公司名称 butxt : butxt; EndUserText.label: 日期 log_date : dats; EndUserText.label: 包装后出货数量 out_bound_total : menge_d; EndUserText.label: 包装后电芯数量 out_bound_ferth : menge_d; EndUserText.label: 包装后入库数量 in_bound_total : menge_d; EndUserText.label: 包装后入库电芯数量 in_bound_ferth : menge_d; EndUserText.label: 化成后入库数量 in_bcp_total : menge_d; EndUserText.label: 化成后入库电芯数量 in_bcp_ferth : menge_d; EndUserText.label: 包装后库存数量 stock_total : menge_d; EndUserText.label: 包装后库存电芯数量 stock_ferth : menge_d; EndUserText.label: 在途库存数量 stock_total_t : menge_d; EndUserText.label: 在途库存电芯数量 stock_ferth_t : menge_d; EndUserText.label: 化成后库存数量 stock_bcp_total : menge_d; EndUserText.label: 化成后库存电芯数量 stock_bcp_ferth : menge_d; } implemented by method zcl_amdp_get_daynget_data;2.2 AMDP class代码CLASS zcl_amdp_get_dayn DEFINITION PUBLIC FINAL CREATE PUBLIC . PUBLIC SECTION. INTERFACES if_amdp_marker_hdb. CLASS-METHODS get_data FOR TABLE FUNCTION ztf_get_dayn. ENDCLASS. CLASS zcl_amdp_get_dayn IMPLEMENTATION. METHOD get_data BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING t001 t001w t001k tvswz likp lips mara makt nsdm_e_mseg nsdm_e_mkpf zmmt0011. * 1. 变量定义计算起始日期和期初快照日期 declare lv_keydat date to_date( :p_keydat, YYYYMMDD ); declare lv_begda DATE ADD_DAYS( :lv_keydat, -9 ); declare lv_init_date date; declare lv_init_date2 date; * 2. 生成 10 天的日期列表 (0 到 9)完美替代你的 DO 10 TIMES declare lt_date_arr nvarchar(8) array; lt_date_arr[1] : to_dats( ADD_DAYS(:lv_begda, 0) ); lt_date_arr[2] : to_dats( ADD_DAYS(:lv_begda, 1) ); lt_date_arr[3] : to_dats( ADD_DAYS(:lv_begda, 2) ); lt_date_arr[4] : to_dats( ADD_DAYS(:lv_begda, 3) ); lt_date_arr[5] : to_dats( ADD_DAYS(:lv_begda, 4) ); lt_date_arr[6] : to_dats( ADD_DAYS(:lv_begda, 5) ); lt_date_arr[7] : to_dats( ADD_DAYS(:lv_begda, 6) ); lt_date_arr[8] : to_dats( ADD_DAYS(:lv_begda, 7) ); lt_date_arr[9] : to_dats( ADD_DAYS(:lv_begda, 8) ); lt_date_arr[10] : to_dats( ADD_DAYS(:lv_begda, 9) ); * 使用 unnest 函数将数组直接转化为内存临时表 lt_dates unnest( :lt_date_arr ) AS ( log_date ); * 3. 获取基础主数据关系 (公司代码 - 工厂 - 装运点) lt_bukrs SELECT bukrs,butxt from t001 where mandt session_context(CLIENT) and bukrs between 1000 and 1500; lt_werks select distinct c.bukrs, a.werks from t001w as a inner join t001k as c on a.bwkey c.bwkey and a.mandt c.mandt where a.mandt session_context(CLIENT) AND c.bukrs between 1000 and 1500; lt_vstel select distinct c.bukrs, a.vstel from tvswz as a inner join t001w as b on a.werks b.werks and a.mandt b.mandt inner join t001k as c on b.bwkey c.bwkey and b.mandt c.mandt where a.mandt session_context(CLIENT) AND c.bukrs between 1000 and 1500; lt_mara_clean select mara.matnr, mtart, meins, * 处理 char(18) 的 ferth非数字转为 0数字转为 decimal case when ferth is not null and ferth and occurrences_regexpr(^[0-9](\.[0-9])?$ in ferth) 0 THEN cast(ferth AS decimal(13,3)) ELSE 0 END AS ferth_num FROM mara INNER JOIN makt ON mara.matnr makt.matnr and mara.mandt makt.mandt where mara.mandt session_context(CLIENT) and makt.spras 1; * 每日出库计算 (outbound) lt_outbound SELECT org.bukrs, a.wadat_ist as log_date, sum( b.lfimg ) as out_bound_total, sum( case when m.ferth_num 0 then b.lfimg * m.ferth_num else b.lfimg end ) as out_bound_ferth from likp as a inner join lips as b on a.vbeln b.vbeln and a.mandt b.mandt inner join :lt_mara_clean as m on b.matnr m.matnr inner join :lt_vstel as org on a.vstel org.vstel where a.mandt session_context(CLIENT) AND a.lfart LF AND a.wbstk C AND a.wadat_ist to_dats(:lv_begda) AND a.wadat_ist to_dats(:lv_keydat) GROUP BY org.bukrs, a.wadat_ist; * 每日入库计算 (inbound inbound bcp) lt_inbound select org.bukrs, mk.budat as log_date, * 成品 sum( case when ( m.mtart BATT OR M.mtart PACK ) then ( CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end ) else 0 end ) as in_bound_total, sum( case when ( m.mtart BATT OR M.mtart PACK ) then ( CASE when M.ferth_num is not null and m.ferth_num 0 then ( case ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) * m.ferth_num else ( case ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) END ) ELSE 0 END ) AS in_bound_ferth, * 针对半成品的条件统计 (bcp) sum( case WHEN ( MS.matnr like HBC%D100 or ms.matnr like HBC%E100 or ms.matnr like HBE%H030 or ms.matnr like HBP%D100 ) then (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) ELSE 0 END ) AS in_bcp_total, sum( case WHEN ( MS.matnr like HBC%D100 or ms.matnr like HBC%E100 or ms.matnr like HBE%H030 or ms.matnr like HBP%D100 ) then (CASE when M.ferth_num is not null and m.ferth_num 0 then (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) * m.ferth_num else (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) END ) ELSE 0 END ) AS in_bcp_ferth FROM nsdm_e_mseg AS MS INNER JOIN nsdm_e_mkpf AS mk ON MS.mjahr mk.mjahr and ms.mblnr mk.mblnr and ms.mandt mk.mandt inner join :lt_mara_clean as m on ms.matnr m.matnr inner join :lt_werks as org on ms.werks org.werks where ms.mandt session_context(CLIENT) AND MS.bwart in (101,102,531,532,109,110) and mk.budat to_dats(:lv_begda) AND mk.budat to_dats(:lv_keydat) GROUP BY org.bukrs, mk.budat; * 寻找最近的期初库存日期 select to_date( max(kc_date),YYYYMMDD ) into lv_init_date from zmmt0011 where mandt session_context(CLIENT) and kc_date to_dats(:lv_begda); IF :lv_init_date IS NULL THEN lv_init_date : to_date(20150101, YYYYMMDD); lv_init_date2 : to_date(20150101, YYYYMMDD); ELSE * 加上 1 天作为增量的计算起点 lv_init_date2 : add_days(:lv_init_date, 1 ); END if; * 库存推算核心获取所有参与计算的凭证明细 (INITIAL delta) * 1. 获取期初表的数据 lt_stock_init SELECT org.bukrs, a.matnr, m.ferth_num, a.sobkz, m.mtart, a.quantity from zmmt0011 as a inner join :lt_mara_clean as m on a.matnr m.matnr inner join :lt_werks as org on a.werks org.werks where a.mandt session_context(CLIENT) AND M.mtart in (BATT,PACK) and a.kc_date to_dats( :lv_init_date ); lt_stock_init_bcp SELECT org.bukrs, a.matnr, m.ferth_num, a.sobkz, m.mtart, a.quantity from zmmt0011 as a inner join :lt_mara_clean as m on a.matnr m.matnr inner join :lt_werks as org on a.werks org.werks where a.mandt session_context(CLIENT) AND ( M.matnr like HBC%D100 or m.matnr like HBC%E100 or m.matnr like HBE%H030 or m.matnr like HBP%D100 ) and a.kc_date to_dats( :lv_init_date ); * 2. 获取期初到当前期间的凭证变动数据 (delta) lt_stock_delta SELECT org.bukrs, ms.matnr, m.ferth_num, ms.sobkz, m.mtart, (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) AS QUANTITY, MS.budat_mkpf as act_date from nsdm_e_mseg as ms inner join :lt_mara_clean as m on ms.matnr m.matnr inner join :lt_werks as org on ms.werks org.werks where ms.mandt session_context(CLIENT) AND M.mtart in (BATT,PACK) and ms.budat_mkpf to_dats(:lv_init_date2) AND MS.budat_mkpf to_dats(:lv_keydat) AND MS.bwart not in (107,108,315,316,169,170); lt_stock_delta_bcp SELECT org.bukrs, ms.matnr, m.ferth_num, ms.sobkz, m.mtart, (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) AS QUANTITY, MS.budat_mkpf as act_date from nsdm_e_mseg as ms inner join :lt_mara_clean as m on ms.matnr m.matnr inner join :lt_werks as org on ms.werks org.werks where ms.mandt session_context(CLIENT) AND ( M.matnr like HBC%D100 or m.matnr like HBC%E100 or m.matnr like HBE%H030 or m.matnr like HBP%D100 ) and ms.budat_mkpf to_dats(:lv_init_date2) AND MS.budat_mkpf to_dats(:lv_keydat) AND MS.bwart not in (107,108,315,316,169,170); * 3. 使用 theta JOIN (范围关联) 推算出这 10 天每天的累计库存 lt_stock_daily SELECT d.log_date, org.bukrs, * 正常库存 (total) sum( case when base.sobkz T then base.quantity else 0 end ) as stock_total, sum( case when base.sobkz T then (CASE when BASE.ferth_num 0 then base.quantity * base.ferth_num else base.quantity end) ELSE 0 END ) AS stock_ferth, * 在途库存 (t) sum( case WHEN base.sobkz T THEN BASE.quantity else 0 end ) as stock_total_t, sum( case when base.sobkz T THEN (CASE when BASE.ferth_num 0 then base.quantity * base.ferth_num else base.quantity end) ELSE 0 END ) AS stock_ferth_t FROM :lt_dates AS d CROSS JOIN :lt_bukrs as org * 左连接所有变动包含期初条件是发生的日期必须 当前遍历的天数! LEFT OUTER JOIN ( * 将期初数据伪装成一个极小的时间让每天都能累加上 SELECT bukrs, matnr, ferth_num, sobkz, QUANTITY, 19000101 AS act_date FROM :lt_stock_init UNION ALL SELECT bukrs, matnr, ferth_num, sobkz, QUANTITY, act_date FROM :lt_stock_delta ) AS BASE ON org.bukrs base.bukrs and base.act_date d.log_date group by d.log_date, org.bukrs; * 4 独立推算半成品库存每天的汇总 lt_stock_daily_bcp select d.log_date, org.bukrs, sum( bcp.quantity ) as stock_bcp_total, sum( case when bcp.ferth_num 0 then bcp.quantity * bcp.ferth_num else bcp.quantity end ) as stock_bcp_ferth from :lt_dates as d cross join :lt_bukrs as org left outer join ( select bukrs, matnr, ferth_num, sobkz, quantity, 19000101 as act_date from :lt_stock_init_bcp union all select bukrs, matnr, ferth_num, sobkz, quantity, act_date from :lt_stock_delta_bcp ) as bcp on org.bukrs bcp.bukrs and bcp.act_date d.log_date group by d.log_date, org.bukrs; * 最终结果组装以 10天的维度表 为骨架将所有结果拼接返回 return select session_context(CLIENT) as client, org.bukrs as bukrs, org.butxt as butxt, d.log_date as log_date, * 出库 coalesce( outb.out_bound_total, 0 ) as out_bound_total, coalesce( outb.out_bound_ferth, 0 ) as out_bound_ferth, * 入库 coalesce( inb.in_bound_total, 0 ) as in_bound_total, coalesce( inb.in_bound_ferth, 0 ) as in_bound_ferth, coalesce( inb.in_bcp_total, 0 ) as in_bcp_total, coalesce( inb.in_bcp_ferth, 0 ) as in_bcp_ferth, * 库存 (带负数兜底保护逻辑同原 ABAP: IF 0 THEN 0) greatest( coalesce( stk.stock_total, 0 ), 0 ) as stock_total, greatest( coalesce( stk.stock_ferth, 0 ), 0 ) as stock_ferth, coalesce( stk.stock_total_t, 0 ) as stock_total_t, coalesce( stk.stock_ferth_t, 0 ) as stock_ferth_t, * 半成品库存 (从独立的 stk_bcp 汇总表取) greatest( coalesce( stk_bcp.stock_bcp_total, 0 ), 0 ) as stock_bcp_total, greatest( coalesce( stk_bcp.stock_bcp_ferth, 0 ), 0 ) as stock_bcp_ferth from :lt_dates as d cross join :lt_bukrs as org left outer join :lt_outbound as outb on d.log_date outb.log_date and org.bukrs outb.bukrs left outer join :lt_inbound as inb on d.log_date inb.log_date and org.bukrs inb.bukrs left outer join :lt_stock_daily as stk on d.log_date stk.log_date and org.bukrs stk.bukrs left outer join :lt_stock_daily_bcp as stk_bcp on d.log_date stk_bcp.log_date and org.bukrs stk_bcp.bukrs order by org.bukrs, d.log_date; endmethod. ENDCLASS.
ABAP转AMDP的一个实践案例
发布时间：2026/5/24 9:04:52
1.ABAP函数代码FUNCTION zfm_get_dayn. *---------------------------------------------------------------------- **本地接口 * IMPORTING * VALUE(IT_BUKRS) TYPE FAGL_RANGE_T_BUKRS OPTIONAL * VALUE(IV_KEYDAT) TYPE DATS DEFAULT SY-DATUM * EXPORTING * VALUE(ET_DATA) TYPE ZTYT_DAY *---------------------------------------------------------------------- RANGES: rt_vstel FOR likp-vstel, rt_werks FOR t001w-werks. DATA ls_data TYPE zss_day. DATA ls_data_line TYPE zss_day_data. DATA ls_stock TYPE zss_stock_day. DATA lt_stock TYPE TABLE OF zss_stock_day. DATA lt_init_stock TYPE TABLE OF ty_stock. DATA lt_init_bcp_stock TYPE TABLE OF ty_stock. DATA lv_init_date TYPE dats. DATA out_bound TYPE ztyt_out. DATA in_bound TYPE ztyt_in_bound. DATA: BEGIN OF ls_mara, meins TYPE mara-meins, ferth TYPE mara-ferth, END OF ls_mara. DATA: lv_begda TYPE begda, lv_endda TYPE endda, lv_date TYPE dats. DATA: lv_do TYPE i. lv_endda iv_keydat. lv_do 10. lv_begda iv_keydat - ( lv_do - 1 ). SELECT bukrs,butxt INTO TABLE DATA(lt_t001) FROM t001 WHERE bukrs IN it_bukrs. PERFORM get_init_stock TABLES lt_init_stock lt_init_bcp_stock USING lv_begda CHANGING lv_init_date. 获取缓存表里最新的初始库存 LOOP AT lt_t001 INTO DATA(ls_t001). CLEAR: rt_vstel[],rt_werks[], rt_vstel,rt_werks. lv_date lv_begda. SELECT DISTINCT I AS sign, EQ AS option, a~vstel AS low INTO CORRESPONDING FIELDS OF TABLE rt_vstel FROM tvswz AS a INNER JOIN t001w AS b ON a~werks b~werks INNER JOIN t001k AS c ON b~bwkey c~bwkey WHERE c~bukrs ls_t001-bukrs. SELECT DISTINCT I AS sign, EQ AS option, a~werks AS low INTO CORRESPONDING FIELDS OF TABLE rt_werks FROM t001w AS a INNER JOIN t001k AS b ON a~bwkey b~bwkey WHERE b~bukrs ls_t001-bukrs. DO lv_do TIMES. * 每日出货数量 IF rt_vstel[] IS NOT INITIAL. SELECT b~matnr,c~maktx, mara~ferth,mara~meins, SUM( lfimg ) AS quantity FROM likp AS a INNER JOIN lips AS b ON a~vbeln b~vbeln INNER JOIN mara ON b~matnr mara~matnr LEFT JOIN makt AS c ON b~matnr c~matnr WHERE a~vstel IN rt_vstel AND a~wadat_ist lv_date AND a~lfart IN ( LF ) AND a~wbstk C AND c~spras 1 GROUP BY b~matnr,mara~ferth,mara~meins,c~maktx INTO CORRESPONDING FIELDS OF TABLE out_bound. ENDIF. CLEAR: ls_data_line-out_bound_total. LOOP AT out_bound INTO DATA(ls_out). ls_data_line-out_bound_total ls_data_line-out_bound_total ls_out-quantity. * 电芯数量 IF ls_out-ferth IS NOT INITIAL. ls_data_line-out_bound_ferth ls_data_line-out_bound_ferth ls_out-quantity * ls_out-ferth. ELSE. ls_data_line-out_bound_ferth ls_data_line-out_bound_ferth ls_out-quantity. ENDIF. CLEAR: ls_out. ENDLOOP. * 每日入库数量 SELECT a~matnr,c~maktx, mara~ferth,mara~meins, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity FROM mseg AS a INNER JOIN mkpf AS b ON a~mjahr b~mjahr AND a~mblnr b~mblnr INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS c ON a~matnr c~matnr WHERE a~bwart IN ( 101,102,531,532,109,110 ) AND a~werks IN rt_werks AND b~budat lv_date AND mara~mtart IN ( BATT,PACK ) AND c~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,c~maktx INTO CORRESPONDING FIELDS OF TABLE in_bound. CLEAR: ls_data_line-in_bound_total. LOOP AT in_bound INTO DATA(ls_in). ls_data_line-in_bound_total ls_data_line-in_bound_total ls_in-quantity. * 电芯数量 IF ls_in-ferth IS NOT INITIAL. ls_data_line-in_bound_ferth ls_data_line-in_bound_ferth ls_in-quantity * ls_in-ferth. ELSE. ls_data_line-in_bound_ferth ls_data_line-in_bound_ferth ls_in-quantity. ENDIF. CLEAR: ls_in. ENDLOOP. * 半成品入库数量 PERFORM get_bcp_in_bound TABLES rt_werks USING lv_date CHANGING ls_data_line. CLEAR: lt_stock. * 每日库存数量 SELECT a~matnr, mara~ferth,mara~meins, b~maktx, a~sobkz, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity INTO CORRESPONDING FIELDS OF TABLE lt_stock FROM mseg AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE mara~mtart IN ( BATT,PACK ) AND a~werks IN rt_werks AND a~budat_mkpf GE lv_init_date AND a~budat_mkpf LE lv_date AND a~bwart NOT IN (107,108,315,316,169,170) AND b~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,b~maktx,a~sobkz. LOOP AT lt_init_stock INTO DATA(ls_init_stock) WHERE werks IN rt_werks. MOVE-CORRESPONDING ls_init_stock TO ls_stock. COLLECT ls_stock INTO lt_stock. CLEAR: ls_init_stock,ls_stock. ENDLOOP. DELETE lt_stock WHERE quantity 0. CLEAR: ls_data_line-stock_total. LOOP AT lt_stock INTO ls_stock. CASE ls_stock-sobkz. 在途库存 WHEN T. ls_data_line-stock_total_t ls_data_line-stock_total_t ls_stock-quantity. IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_ferth_t ls_data_line-stock_ferth_t ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_ferth_t ls_data_line-stock_ferth_t ls_stock-quantity. ENDIF. WHEN OTHERS. 正常库存 ls_data_line-stock_total ls_data_line-stock_total ls_stock-quantity. * 电芯数量 IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_ferth ls_data_line-stock_ferth ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_ferth ls_data_line-stock_ferth ls_stock-quantity. ENDIF. ENDCASE. CLEAR: ls_stock. ENDLOOP. IF ls_data_line-stock_ferth 0. ls_data_line-stock_ferth 0. ENDIF. IF ls_data_line-stock_total 0. ls_data_line-stock_total 0. ENDIF. * 半成品电芯库存 PERFORM get_bcp_stock_new TABLES rt_werks lt_init_bcp_stock USING lv_init_date lv_date CHANGING ls_data_line. ls_data-bukrs ls_t001-bukrs. ls_data-butxt ls_t001-butxt. ls_data_line-date lv_date. APPEND ls_data_line TO ls_data-data. CLEAR: lt_stock,out_bound,in_bound,ls_data_line. lv_date lv_date 1. ENDDO. APPEND ls_data TO et_data. CLEAR:ls_t001,ls_data. ENDLOOP. ENDFUNCTION. *----------------------------------------------------------------------* ***INCLUDE LZFG_FI04F01. *----------------------------------------------------------------------* *---------------------------------------------------------------------* * Form get_bcp_kc *---------------------------------------------------------------------* * text *---------------------------------------------------------------------* * -- LV_DATE * -- LS_DATA *---------------------------------------------------------------------* FORM get_bcp_stock TABLES rt_werks STRUCTURE range_s_werks USING lv_date TYPE dats CHANGING ls_data_line TYPE zss_day_data. DATA lt_stock TYPE TABLE OF zss_stock_day. DATA ls_stock TYPE zss_stock_day. SELECT a~matnr, mara~ferth,mara~meins, b~maktx, a~sobkz, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity INTO CORRESPONDING FIELDS OF TABLE lt_stock FROM mseg AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100) AND a~werks IN rt_werks AND a~budat_mkpf GE 20150101 AND a~budat_mkpf LE lv_date AND a~bwart NOT IN (107,108,315,316,169,170) AND b~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,b~maktx,a~sobkz. DELETE lt_stock WHERE quantity 0. CLEAR: ls_data_line-stock_bcp_total. LOOP AT lt_stock INTO ls_stock. ls_data_line-stock_bcp_total ls_data_line-stock_bcp_total ls_stock-quantity. * 电芯数量 IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity. ENDIF. CLEAR: ls_stock. ENDLOOP. ENDFORM. FORM get_bcp_stock_new TABLES rt_werks STRUCTURE range_s_werks lt_init_bcp_stock STRUCTURE gs_stock USING lv_init_date TYPE dats lv_date TYPE dats CHANGING ls_data_line TYPE zss_day_data. DATA lt_stock TYPE TABLE OF zss_stock_day. DATA ls_stock TYPE zss_stock_day. SELECT a~matnr, mara~ferth,mara~meins, b~maktx, a~sobkz, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity INTO CORRESPONDING FIELDS OF TABLE lt_stock FROM mseg AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100) AND a~werks IN rt_werks AND a~budat_mkpf GE lv_init_date AND a~budat_mkpf LE lv_date AND a~bwart NOT IN (107,108,315,316,169,170) AND b~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,b~maktx,a~sobkz. LOOP AT lt_init_bcp_stock INTO DATA(ls_init_stock) WHERE werks IN rt_werks. MOVE-CORRESPONDING ls_init_stock TO ls_stock. COLLECT ls_stock INTO lt_stock. CLEAR: ls_init_stock,ls_stock. ENDLOOP. DELETE lt_stock WHERE quantity 0. CLEAR: ls_data_line-stock_bcp_total. LOOP AT lt_stock INTO ls_stock. ls_data_line-stock_bcp_total ls_data_line-stock_bcp_total ls_stock-quantity. * 电芯数量 IF ls_stock-ferth IS NOT INITIAL. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity * ls_stock-ferth. ELSE. ls_data_line-stock_bcp_ferth ls_data_line-stock_bcp_ferth ls_stock-quantity. ENDIF. CLEAR: ls_stock. ENDLOOP. ENDFORM. *---------------------------------------------------------------------* * Form get_bcp_in_bound *---------------------------------------------------------------------* * text *---------------------------------------------------------------------* * -- RT_WERKS * -- LV_DATE * -- LS_DATA_LINE *---------------------------------------------------------------------* FORM get_bcp_in_bound TABLES rt_werks STRUCTURE range_s_werks USING lv_date TYPE dats CHANGING ls_data_line TYPE zss_day_data. DATA in_bound TYPE ztyt_in_bound. SELECT a~matnr,c~maktx, mara~ferth,mara~meins, SUM( CASE shkzg WHEN H THEN - menge WHEN S THEN menge END ) AS quantity FROM mseg AS a INNER JOIN mkpf AS b ON a~mjahr b~mjahr AND a~mblnr b~mblnr INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS c ON a~matnr c~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100 ) AND a~bwart IN ( 101,102,531,532,109,110 ) AND a~werks IN rt_werks AND b~budat lv_date AND c~spras 1 GROUP BY a~matnr,mara~ferth,mara~meins,c~maktx INTO CORRESPONDING FIELDS OF TABLE in_bound. CLEAR: ls_data_line-in_bcp_total. LOOP AT in_bound INTO DATA(ls_in). ls_data_line-in_bcp_total ls_data_line-in_bcp_total ls_in-quantity. * 电芯数量 IF ls_in-ferth IS NOT INITIAL. ls_data_line-in_bcp_ferth ls_data_line-in_bcp_ferth ls_in-quantity * ls_in-ferth. ELSE. ls_data_line-in_bcp_ferth ls_data_line-in_bcp_ferth ls_in-quantity. ENDIF. CLEAR: ls_in. ENDLOOP. ENDFORM. *---------------------------------------------------------------------* * Form GET_INIT_STOCK *---------------------------------------------------------------------* * text *---------------------------------------------------------------------* * -- p1 text * -- p2 text *---------------------------------------------------------------------* FORM get_init_stock TABLES lt_init_stock STRUCTURE gs_stock lt_init_bcp_stock STRUCTURE gs_stock USING lv_begda CHANGING lv_init_date. DATA lv_kc_date TYPE dats. SELECT MAX( kc_date ) INTO lv_kc_date FROM zmmt0011 WHERE kc_date LE lv_begda. IF lv_kc_date IS NOT INITIAL. SELECT a~matnr, a~werks, mara~ferth,mara~meins, b~maktx, a~sobkz, a~quantity INTO CORRESPONDING FIELDS OF TABLE lt_init_stock FROM zmmt0011 AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE mara~mtart IN ( BATT,PACK ) AND a~kc_date lv_kc_date AND b~spras 1. SELECT a~matnr, a~werks, mara~ferth,mara~meins, b~maktx, a~sobkz, a~quantity INTO CORRESPONDING FIELDS OF TABLE lt_init_bcp_stock FROM zmmt0011 AS a INNER JOIN mara ON a~matnr mara~matnr LEFT JOIN makt AS b ON a~matnr b~matnr WHERE ( a~matnr LIKE HBC%D100 OR a~matnr LIKE HBC%E100 OR a~matnr LIKE HBE%H030 OR a~matnr LIKE HBP%D100) AND a~kc_date lv_kc_date AND b~spras 1. lv_init_date lv_kc_date 1. ELSE. lv_init_date 20150101. ENDIF. ENDFORM.2.转成AMDP实现2.1CDS代码table functionEndUserText.label: 获取每日出入库及库存数据 define table function ZTF_GET_DAYN with parameters Environment.systemField: #SYSTEM_DATE p_keydat : dats returns { client : abap.clnt; EndUserText.label: 公司代码 bukrs : bukrs; EndUserText.label: 公司名称 butxt : butxt; EndUserText.label: 日期 log_date : dats; EndUserText.label: 包装后出货数量 out_bound_total : menge_d; EndUserText.label: 包装后电芯数量 out_bound_ferth : menge_d; EndUserText.label: 包装后入库数量 in_bound_total : menge_d; EndUserText.label: 包装后入库电芯数量 in_bound_ferth : menge_d; EndUserText.label: 化成后入库数量 in_bcp_total : menge_d; EndUserText.label: 化成后入库电芯数量 in_bcp_ferth : menge_d; EndUserText.label: 包装后库存数量 stock_total : menge_d; EndUserText.label: 包装后库存电芯数量 stock_ferth : menge_d; EndUserText.label: 在途库存数量 stock_total_t : menge_d; EndUserText.label: 在途库存电芯数量 stock_ferth_t : menge_d; EndUserText.label: 化成后库存数量 stock_bcp_total : menge_d; EndUserText.label: 化成后库存电芯数量 stock_bcp_ferth : menge_d; } implemented by method zcl_amdp_get_daynget_data;2.2 AMDP class代码CLASS zcl_amdp_get_dayn DEFINITION PUBLIC FINAL CREATE PUBLIC . PUBLIC SECTION. INTERFACES if_amdp_marker_hdb. CLASS-METHODS get_data FOR TABLE FUNCTION ztf_get_dayn. ENDCLASS. CLASS zcl_amdp_get_dayn IMPLEMENTATION. METHOD get_data BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING t001 t001w t001k tvswz likp lips mara makt nsdm_e_mseg nsdm_e_mkpf zmmt0011. * 1. 变量定义计算起始日期和期初快照日期 declare lv_keydat date to_date( :p_keydat, YYYYMMDD ); declare lv_begda DATE ADD_DAYS( :lv_keydat, -9 ); declare lv_init_date date; declare lv_init_date2 date; * 2. 生成 10 天的日期列表 (0 到 9)完美替代你的 DO 10 TIMES declare lt_date_arr nvarchar(8) array; lt_date_arr[1] : to_dats( ADD_DAYS(:lv_begda, 0) ); lt_date_arr[2] : to_dats( ADD_DAYS(:lv_begda, 1) ); lt_date_arr[3] : to_dats( ADD_DAYS(:lv_begda, 2) ); lt_date_arr[4] : to_dats( ADD_DAYS(:lv_begda, 3) ); lt_date_arr[5] : to_dats( ADD_DAYS(:lv_begda, 4) ); lt_date_arr[6] : to_dats( ADD_DAYS(:lv_begda, 5) ); lt_date_arr[7] : to_dats( ADD_DAYS(:lv_begda, 6) ); lt_date_arr[8] : to_dats( ADD_DAYS(:lv_begda, 7) ); lt_date_arr[9] : to_dats( ADD_DAYS(:lv_begda, 8) ); lt_date_arr[10] : to_dats( ADD_DAYS(:lv_begda, 9) ); * 使用 unnest 函数将数组直接转化为内存临时表 lt_dates unnest( :lt_date_arr ) AS ( log_date ); * 3. 获取基础主数据关系 (公司代码 - 工厂 - 装运点) lt_bukrs SELECT bukrs,butxt from t001 where mandt session_context(CLIENT) and bukrs between 1000 and 1500; lt_werks select distinct c.bukrs, a.werks from t001w as a inner join t001k as c on a.bwkey c.bwkey and a.mandt c.mandt where a.mandt session_context(CLIENT) AND c.bukrs between 1000 and 1500; lt_vstel select distinct c.bukrs, a.vstel from tvswz as a inner join t001w as b on a.werks b.werks and a.mandt b.mandt inner join t001k as c on b.bwkey c.bwkey and b.mandt c.mandt where a.mandt session_context(CLIENT) AND c.bukrs between 1000 and 1500; lt_mara_clean select mara.matnr, mtart, meins, * 处理 char(18) 的 ferth非数字转为 0数字转为 decimal case when ferth is not null and ferth and occurrences_regexpr(^[0-9](\.[0-9])?$ in ferth) 0 THEN cast(ferth AS decimal(13,3)) ELSE 0 END AS ferth_num FROM mara INNER JOIN makt ON mara.matnr makt.matnr and mara.mandt makt.mandt where mara.mandt session_context(CLIENT) and makt.spras 1; * 每日出库计算 (outbound) lt_outbound SELECT org.bukrs, a.wadat_ist as log_date, sum( b.lfimg ) as out_bound_total, sum( case when m.ferth_num 0 then b.lfimg * m.ferth_num else b.lfimg end ) as out_bound_ferth from likp as a inner join lips as b on a.vbeln b.vbeln and a.mandt b.mandt inner join :lt_mara_clean as m on b.matnr m.matnr inner join :lt_vstel as org on a.vstel org.vstel where a.mandt session_context(CLIENT) AND a.lfart LF AND a.wbstk C AND a.wadat_ist to_dats(:lv_begda) AND a.wadat_ist to_dats(:lv_keydat) GROUP BY org.bukrs, a.wadat_ist; * 每日入库计算 (inbound inbound bcp) lt_inbound select org.bukrs, mk.budat as log_date, * 成品 sum( case when ( m.mtart BATT OR M.mtart PACK ) then ( CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end ) else 0 end ) as in_bound_total, sum( case when ( m.mtart BATT OR M.mtart PACK ) then ( CASE when M.ferth_num is not null and m.ferth_num 0 then ( case ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) * m.ferth_num else ( case ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) END ) ELSE 0 END ) AS in_bound_ferth, * 针对半成品的条件统计 (bcp) sum( case WHEN ( MS.matnr like HBC%D100 or ms.matnr like HBC%E100 or ms.matnr like HBE%H030 or ms.matnr like HBP%D100 ) then (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) ELSE 0 END ) AS in_bcp_total, sum( case WHEN ( MS.matnr like HBC%D100 or ms.matnr like HBC%E100 or ms.matnr like HBE%H030 or ms.matnr like HBP%D100 ) then (CASE when M.ferth_num is not null and m.ferth_num 0 then (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) * m.ferth_num else (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) END ) ELSE 0 END ) AS in_bcp_ferth FROM nsdm_e_mseg AS MS INNER JOIN nsdm_e_mkpf AS mk ON MS.mjahr mk.mjahr and ms.mblnr mk.mblnr and ms.mandt mk.mandt inner join :lt_mara_clean as m on ms.matnr m.matnr inner join :lt_werks as org on ms.werks org.werks where ms.mandt session_context(CLIENT) AND MS.bwart in (101,102,531,532,109,110) and mk.budat to_dats(:lv_begda) AND mk.budat to_dats(:lv_keydat) GROUP BY org.bukrs, mk.budat; * 寻找最近的期初库存日期 select to_date( max(kc_date),YYYYMMDD ) into lv_init_date from zmmt0011 where mandt session_context(CLIENT) and kc_date to_dats(:lv_begda); IF :lv_init_date IS NULL THEN lv_init_date : to_date(20150101, YYYYMMDD); lv_init_date2 : to_date(20150101, YYYYMMDD); ELSE * 加上 1 天作为增量的计算起点 lv_init_date2 : add_days(:lv_init_date, 1 ); END if; * 库存推算核心获取所有参与计算的凭证明细 (INITIAL delta) * 1. 获取期初表的数据 lt_stock_init SELECT org.bukrs, a.matnr, m.ferth_num, a.sobkz, m.mtart, a.quantity from zmmt0011 as a inner join :lt_mara_clean as m on a.matnr m.matnr inner join :lt_werks as org on a.werks org.werks where a.mandt session_context(CLIENT) AND M.mtart in (BATT,PACK) and a.kc_date to_dats( :lv_init_date ); lt_stock_init_bcp SELECT org.bukrs, a.matnr, m.ferth_num, a.sobkz, m.mtart, a.quantity from zmmt0011 as a inner join :lt_mara_clean as m on a.matnr m.matnr inner join :lt_werks as org on a.werks org.werks where a.mandt session_context(CLIENT) AND ( M.matnr like HBC%D100 or m.matnr like HBC%E100 or m.matnr like HBE%H030 or m.matnr like HBP%D100 ) and a.kc_date to_dats( :lv_init_date ); * 2. 获取期初到当前期间的凭证变动数据 (delta) lt_stock_delta SELECT org.bukrs, ms.matnr, m.ferth_num, ms.sobkz, m.mtart, (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) AS QUANTITY, MS.budat_mkpf as act_date from nsdm_e_mseg as ms inner join :lt_mara_clean as m on ms.matnr m.matnr inner join :lt_werks as org on ms.werks org.werks where ms.mandt session_context(CLIENT) AND M.mtart in (BATT,PACK) and ms.budat_mkpf to_dats(:lv_init_date2) AND MS.budat_mkpf to_dats(:lv_keydat) AND MS.bwart not in (107,108,315,316,169,170); lt_stock_delta_bcp SELECT org.bukrs, ms.matnr, m.ferth_num, ms.sobkz, m.mtart, (CASE ms.shkzg when H then -ms.menge when S then ms.menge else 0 end) AS QUANTITY, MS.budat_mkpf as act_date from nsdm_e_mseg as ms inner join :lt_mara_clean as m on ms.matnr m.matnr inner join :lt_werks as org on ms.werks org.werks where ms.mandt session_context(CLIENT) AND ( M.matnr like HBC%D100 or m.matnr like HBC%E100 or m.matnr like HBE%H030 or m.matnr like HBP%D100 ) and ms.budat_mkpf to_dats(:lv_init_date2) AND MS.budat_mkpf to_dats(:lv_keydat) AND MS.bwart not in (107,108,315,316,169,170); * 3. 使用 theta JOIN (范围关联) 推算出这 10 天每天的累计库存 lt_stock_daily SELECT d.log_date, org.bukrs, * 正常库存 (total) sum( case when base.sobkz T then base.quantity else 0 end ) as stock_total, sum( case when base.sobkz T then (CASE when BASE.ferth_num 0 then base.quantity * base.ferth_num else base.quantity end) ELSE 0 END ) AS stock_ferth, * 在途库存 (t) sum( case WHEN base.sobkz T THEN BASE.quantity else 0 end ) as stock_total_t, sum( case when base.sobkz T THEN (CASE when BASE.ferth_num 0 then base.quantity * base.ferth_num else base.quantity end) ELSE 0 END ) AS stock_ferth_t FROM :lt_dates AS d CROSS JOIN :lt_bukrs as org * 左连接所有变动包含期初条件是发生的日期必须 当前遍历的天数! LEFT OUTER JOIN ( * 将期初数据伪装成一个极小的时间让每天都能累加上 SELECT bukrs, matnr, ferth_num, sobkz, QUANTITY, 19000101 AS act_date FROM :lt_stock_init UNION ALL SELECT bukrs, matnr, ferth_num, sobkz, QUANTITY, act_date FROM :lt_stock_delta ) AS BASE ON org.bukrs base.bukrs and base.act_date d.log_date group by d.log_date, org.bukrs; * 4 独立推算半成品库存每天的汇总 lt_stock_daily_bcp select d.log_date, org.bukrs, sum( bcp.quantity ) as stock_bcp_total, sum( case when bcp.ferth_num 0 then bcp.quantity * bcp.ferth_num else bcp.quantity end ) as stock_bcp_ferth from :lt_dates as d cross join :lt_bukrs as org left outer join ( select bukrs, matnr, ferth_num, sobkz, quantity, 19000101 as act_date from :lt_stock_init_bcp union all select bukrs, matnr, ferth_num, sobkz, quantity, act_date from :lt_stock_delta_bcp ) as bcp on org.bukrs bcp.bukrs and bcp.act_date d.log_date group by d.log_date, org.bukrs; * 最终结果组装以 10天的维度表 为骨架将所有结果拼接返回 return select session_context(CLIENT) as client, org.bukrs as bukrs, org.butxt as butxt, d.log_date as log_date, * 出库 coalesce( outb.out_bound_total, 0 ) as out_bound_total, coalesce( outb.out_bound_ferth, 0 ) as out_bound_ferth, * 入库 coalesce( inb.in_bound_total, 0 ) as in_bound_total, coalesce( inb.in_bound_ferth, 0 ) as in_bound_ferth, coalesce( inb.in_bcp_total, 0 ) as in_bcp_total, coalesce( inb.in_bcp_ferth, 0 ) as in_bcp_ferth, * 库存 (带负数兜底保护逻辑同原 ABAP: IF 0 THEN 0) greatest( coalesce( stk.stock_total, 0 ), 0 ) as stock_total, greatest( coalesce( stk.stock_ferth, 0 ), 0 ) as stock_ferth, coalesce( stk.stock_total_t, 0 ) as stock_total_t, coalesce( stk.stock_ferth_t, 0 ) as stock_ferth_t, * 半成品库存 (从独立的 stk_bcp 汇总表取) greatest( coalesce( stk_bcp.stock_bcp_total, 0 ), 0 ) as stock_bcp_total, greatest( coalesce( stk_bcp.stock_bcp_ferth, 0 ), 0 ) as stock_bcp_ferth from :lt_dates as d cross join :lt_bukrs as org left outer join :lt_outbound as outb on d.log_date outb.log_date and org.bukrs outb.bukrs left outer join :lt_inbound as inb on d.log_date inb.log_date and org.bukrs inb.bukrs left outer join :lt_stock_daily as stk on d.log_date stk.log_date and org.bukrs stk.bukrs left outer join :lt_stock_daily_bcp as stk_bcp on d.log_date stk_bcp.log_date and org.bukrs stk_bcp.bukrs order by org.bukrs, d.log_date; endmethod. ENDCLASS.
