SAP POS Database connect SAP Database system by oracle DBlink with MView for update Master information (Oracle&Oracle database)

** Myself
Create POS Database connect SAP Database system

On SAP system DB tier



-Create tablespace  PSAPINF  (by  brtools)



-Create schema  user name  inf on tablespace PSAPINF




create user inf identified by <XXX> default tablespace PSAPINF;
-Grant default role  to inf user




grant create session,resource to inf;

-For Inbound Grant insert,select,delete tables ZSD_POS_INB_HO01,ZSD_POS_INB_DT01,ZSD_POS_INB_DT02  to inf user

grant insert,delete,select on sapsr3.ZSD_POS_INB_HO01 to inf;
-For Outbound grant select main saptable for create MVIEW  to inf user



grant select on sapsr3.MARA to inf;


grant select on sapsr3.MAKT to inf;


grant select on sapsr3.MVKE to inf;


grant select on sapsr3.MAW1 to inf;


grant select on sapsr3.T006A to inf;


grant select on sapsr3.MARM to inf;


grant select on sapsr3.MAMT to inf;


grant select on sapsr3.T006A to inf;


grant select on sapsr3.A071 to inf;


grant select on sapsr3.A155 to inf;


grant select on sapsr3.WAKHIND to inf;


grant select on sapsr3.A073 to inf;


grant select on sapsr3.KONP to inf;


grant select on sapsr3.KONH to inf;


grant select on sapsr3.MCHB to inf;


grant select on sapsr3.KNA1 to inf;


grant select on sapsr3.KNB1 to inf;


grant select on sapsr3.KNVV to inf;


grant select on sapsr3.TSAD3T to inf;


grant select on sapsr3.ADRC to inf;


On Company POS database system



- Create TNS connection to SAP server




# Create service connection (Example DB_name=DEV,hostname=infdev,port=1527)
- Create dblink  connect inf user and SAP system




# Create DB-Link (Example Service_name=DEV,username=inf,password=<XXX>)
##CREATE DATABASE LINK DEV
##CONNECT TO inf IDENTIFIED BY <XXX>
- Test Inbound insert data from POS DB  to SAP Z-table




# Insert data  to ZSD_POS_INB_HO01 from POS

- Test Outbound select SAP-table and Create Snapshot or Mview



# Create Mview from SAP-table with join condition and create snapshot to view
##CREATE MATERIALIZED VIEW SAP_MAT_VIEW
##<refresh_mode>
##AS <select_statement>;
##<select_statement> contains the query that is used to create the materialized view.







SAP tables(transparent tables)

Database Name tables
Inbound from POS


ZSD_POS_INB_HO01

sapsr3.ZSD_POS_INB_HO01

Outbound to POS

 Database Name tables
MARA

sapsr3.MARA
MAKT

sapsr3.MAKT
MVKE

sapsr3.MVKE
MAW1

sapsr3.MAW1
T006A

sapsr3.T006A
MARM

sapsr3.MARM
MAMT

sapsr3.MAMT
T006A

sapsr3.T006A
A071

sapsr3.A071
A155

sapsr3.A155
A073

sapsr3.A073
KONP

sapsr3.KONP
KONH

sapsr3.KONH
MCHB

sapsr3.MCHB
KNA1

sapsr3.KNA1
KNB1

sapsr3.KNB1
KNVV

sapsr3.KNVV
TSAD3T

sapsr3.TSAD3T
ADRC

sapsr3.ADRC
Z_PAY_GROUP

sapsr3.Z_PAY_GROUP
Z_PAY_SOURCE

sapsr3.Z_PAY_SOURCE
Z_PAY_TYPE

sapsr3.Z_PAY_TYPE




0 comments:

Loading