SAP与外部数据库ORACLE操作

采雁4934

配置 DBCO
在SAP服务器路径下配置oracle连接。
 
实例代码
　　

*********begin-zhugx 8D 生产订单**************
DATA: qmtab1 LIKE TABLE OF zqm004 WITH HEADER LINE.
DATA: con_name TYPE string.
IF sy-mandt = '800'.
con_name = 'ZPRDOA'. "正式环境
ELSE.
con_name = 'ZOA'. "OA环境
ENDIF.
DATA: qmstr(50) TYPE c.
DATA: qmstr1(10) TYPE c.
DATA: qmstr2(12) TYPE c.
DATA: qmstr3(20) TYPE c.
DATA: qmstr4(4) TYPE c value 'NG'.
DATA: qmnum TYPE i.
DATA: qmnum1 TYPE i.
DATA: d1 LIKE sy-datum.
d1 = sy-datum.
DATA: zaufnr LIKE mseg-aufnr.  "生产订单
DATA: zaufps LIKE mseg-aufps.  "生产订单行项目
DATA: zebeln LIKE mseg-ebeln.  "采购订单
DATA: zebelp LIKE mseg-ebelp.  "采购订单行项目
DATA: zmatnr TYPE mseg-matnr.  "物料
DATA: zwerks1 LIKE mseg-werks. "工厂
DATA: zbwart LIKE mseg-bwart.  "移动类型(库存管理)
DATA: zvgart LIKE mkpf-vgart.  "交易/事件类型
***********生产订单交易类型是 WF 采购订单是WE 使用MIGO时考虑生产订单和销售订单*************
zmatnr = i_mseg-matnr.
zwerks1 = i_mseg-werks.
zvgart = i_mkpf-vgart.
zbwart = i_mseg-bwart.
IF sy-tcode = 'MIGO' AND zbwart = '101' AND ( zvgart = 'WF' OR zvgart = 'WE' ).
IF zvgart = 'WF'.
qmstr3 = '生产工单入库'.
zaufnr = i_mseg-aufnr.  "生产订单
zaufps = i_mseg-aufps.  "生产订单行项目
ENDIF.
IF zvgart = 'WE'.
qmstr3 = '采购订单入库'.
zebeln = i_mseg-ebeln.  "采购订单
zebelp = i_mseg-ebelp.  "采购订单行项目
ENDIF.
SELECT * INTO CORRESPONDING FIELDS OF TABLE qmtab1 FROM zqm004
WHERE zqm004~zmatnr = zmatnr  AND zqm004~zqmawerks = zwerks1.
IF sy-subrc = 0.
LOOP AT qmtab1.
IF qmtab1-zstate = 'OPEN'.
IF qmtab1-ztype IS INITIAL OR qmtab1-ztype =  qmstr3.
************打开数据库连接**************
EXEC SQL.
CONNECT TO :con_name
ENDEXEC.
******************操作OA数据*****************
IF zvgart = 'WF'.
EXEC SQL.
select count(*) into :qmnum from whir$8Dxgqr where whir$8Dxgqr_ddhm = :zaufnr
ENDEXEC.
ENDIF.
IF zvgart = 'WE'.
EXEC SQL.
select count(*) into :qmnum from whir$8Dxgqr where whir$8Dxgqr_ddhm = :zebeln and whir$8Dxgqr_ddhxm = :zebelp
ENDEXEC.
ENDIF.
IF qmnum > 0.

IF ZVGART = 'WF'.
EXEC SQL.
select count(*) into :qmnum1 from whir$8Dxgqr where whir$8Dxgqr_ddhm = :zaufnr and ( whir$8Dxgqr_gjjg = :qmstr4 OR whir$8Dxgqr_gjjg IS null )
ENDEXEC.
ENDIF.
IF ZVGART = 'WE'.
EXEC SQL.
select count(*) into :qmnum1 from whir$8Dxgqr where whir$8Dxgqr_ddhm = :ZEBELN and whir$8Dxgqr_ddhxm = :ZEBELP and
( whir$8Dxgqr_gjjg = :qmstr4 OR whir$8Dxgqr_gjjg IS null )
ENDEXEC.
ENDIF.
IF qmnum1 > 0.
CONCATENATE '请在OA中跟进8D编号为'  qmtab1-zqmnum '的跟进结果' INTO qmstr.
MESSAGE qmstr  TYPE 'E'.
ENDIF.
ELSE.
EXEC SQL.
select whir$8D_id into :qmstr2 from whir$8D where whir$8D_8Dbh = :QMTAB1-ZQMNUM
ENDEXEC.
IF qmstr2 IS NOT INITIAL.
IF ZVGART = 'WF'.
EXEC SQL.
insert into whir$8Dxgqr ( whir$8Dxgqr_id, whir$8Dxgqr_date, whir$8Dxgqr_ddhm, WHIR$8DXGQR_FOREIGNKEY)
values (WF_SEQUENCE.nextval,  :d1, :ZAUFNR, :qmstr2 )
ENDEXEC.
ENDIF.
IF ZVGART = 'WE'.
EXEC SQL.
insert into whir$8Dxgqr ( whir$8Dxgqr_id, whir$8Dxgqr_date, whir$8Dxgqr_ddhm, whir$8Dxgqr_ddhxm,WHIR$8DXGQR_FOREIGNKEY)
values (WF_SEQUENCE.nextval,  :d1, :ZEBELN, :ZEBELP, :qmstr2 )
ENDEXEC.
ENDIF.
*                    insert into whir$8Dxgqr ( whir$8Dxgqr_id ) values ( 6436534 )
CONCATENATE qmstr3 zebeln '在OA效果确认表中产生了一条新记录请跟进' INTO qmstr.
MESSAGE qmstr  TYPE 'E'.
ENDIF.
ENDIF.
***********关闭数据库连接***********
EXEC SQL.
DISCONNECT :con_name
ENDEXEC.
ENDIF.
ENDIF.
ENDLOOP.
ELSE.
*      MESSAGE '没有在中间表找到数据请确定是否在OA发起8D流程！' TYPE   'E'.
ENDIF.


***************写子方法******************************************
ELSEIF sy-tcode = 'MB31'.
zaufnr = i_mseg-aufnr.  "生产订单
zaufps = i_mseg-aufps.  "生产订单行项目
*          PERFORM getzqm004_data USING ZEBELN ZEBELP ZMATNR ZWERKS1 ZVGART.
SELECT * INTO CORRESPONDING FIELDS OF TABLE qmtab1 FROM zqm004
WHERE zqm004~zmatnr = zmatnr  AND zqm004~zqmawerks = zwerks1.
IF sy-subrc = 0.
LOOP AT qmtab1.
IF qmtab1-zstate = 'OPEN'.
IF qmtab1-ztype IS INITIAL OR qmtab1-ztype =  '生产工单入库'.
************打开数据库连接**************
EXEC SQL.
CONNECT TO :con_name
ENDEXEC.
******************操作OA数据*************
EXEC SQL.
select count(*) into :qmnum from whir$8Dxgqr where whir$8Dxgqr_ddhm = :ZAUFNR
ENDEXEC.
IF qmnum > 0.
EXEC SQL.
select count(*) into :qmnum1 from whir$8Dxgqr where whir$8Dxgqr_ddhm = :ZAUFNR and
( whir$8Dxgqr_gjjg = 'NG' OR whir$8Dxgqr_gjjg IS null )
ENDEXEC.
IF qmnum1 > 0.
CONCATENATE '请在OA中跟进8D编号为'  qmtab1-zqmnum '的跟进结果' INTO qmstr.
MESSAGE qmstr  TYPE 'E'.
ENDIF.
ELSE.
EXEC SQL.
select whir$8D_id into :qmstr2 from whir$8D where whir$8D_8Dbh = :QMTAB1-ZQMNUM
ENDEXEC.
IF qmstr2 IS NOT INITIAL.
EXEC SQL.
insert into whir$8Dxgqr ( whir$8Dxgqr_id, whir$8Dxgqr_date, whir$8Dxgqr_ddhm, WHIR$8DXGQR_FOREIGNKEY)
values (WF_SEQUENCE.nextval,  :d1, :ZAUFNR, :qmstr2 )
*                    insert into whir$8Dxgqr ( whir$8Dxgqr_id ) values ( 6436534 )
ENDEXEC.
CONCATENATE '生产工单入库' zaufnr '在OA效果确认表中产生了一条新记录请跟进' INTO qmstr.
MESSAGE qmstr  TYPE 'E'.
ENDIF.
ENDIF.
***********关闭数据库连接***********
EXEC SQL.
DISCONNECT :con_name
ENDEXEC.
ENDIF.
ENDIF.
ENDLOOP.
ELSE.
*      MESSAGE '没有在中间表找到数据请确定是否在OA发起8D流程！' TYPE   'E'.
ENDIF.
ENDIF.
*****************************end-zhugx*************************
　　
 
 

<!--EndFragment--><!--EndFragment--><!--EndFragment-->