troubleshooting Question

MSSQL -- calling Oracle Package ?

Avatar of finance_teacher
finance_teacher asked on
Microsoft SQL ServerOracle DatabaseMicrosoft SQL Server 2008
4 Comments1 Solution495 ViewsLast Modified:
How you have any examples on how I change the below YELLOW #2 so my Oracle PROJECT_TRANSACTION_API.NEW__ package works via MSSQL Server Management Studio ?
-----------------------------------------------------------------------------------------
#1 = Below works in MSSQL Server Management Studio

(SELECT * FROM OPENQUERY(IFSTEST,'Select SABR1APP.Supplier_API.Get_Vendor_name(''CONOP'') from dual'))
-----------------------------------------------------------------------------------------
#2 = Below works in Oracle SQL Developer

DECLARE
   attr_  VARCHAR2(32000);
   a_ VARCHAR2(32000) := NULL; --p0
   B_ VARCHAR2(32000) := NULL; --p1
   c_ VARCHAR2(32000) := NULL; --p2
BEGIN
   CLIENT_SYS.ADD_TO_ATTR('COMPANY_ID','10',ATTR_);
   CLIENT_SYS.ADD_TO_ATTR('REGISTERED_BY','JOHNDOE',ATTR_);
  Client_SYS.Add_To_Attr('ACCOUNT_DATE','2012-09-05-00.00.00',attr_);           ---- automatically changes daily
   client_sys.add_to_attr('PROJECT_ID','IND 2013',attr_);                ---- change after every June EOY
   client_sys.add_to_attr('SUB_PROJECT_ID','INDSAL',attr_);
   Client_SYS.Add_To_Attr('ACTIVITY_SEQ','100027626',attr_);
   Client_SYS.Add_To_Attr('REPORT_COST_TYPE_DB','1',attr_);
   Client_SYS.Add_To_Attr('REPORT_COST_CODE','INDIRLAB',attr_);
   CLIENT_SYS.ADD_TO_ATTR('INTERNAL_QUANTITY',8,ATTR_);
   Client_SYS.Add_To_Attr('INTERNAL_PRICE','10.1',attr_);          ---!!!!!!!---- based on employee #
   Client_SYS.Add_To_Attr('SALES_QUANTITY',8,attr_);
   Client_SYS.Add_To_Attr('CURRENCY_CODE','USD',attr_);
   client_sys.add_to_attr('CURRENCY_TYPE',1,attr_);
   Client_SYS.Add_To_Attr('SALES_PRICE',0,attr_);                      
   client_sys.add_to_attr('SALES_AMOUNT_DOMESTIC',0,attr_);
   CLIENT_SYS.ADD_TO_ATTR('INVOICABILITY_DB','1',ATTR_);                  
   Client_SYS.Add_To_Attr('EMP_NO','000380',attr_);                        ---!!!!!!!---- employee #
   Client_SYS.Add_To_Attr('ORG_CODE','',attr_);                  ---!!!!!!!---- based on employee #
   Client_SYS.Add_To_Attr('CURR_RATE','1',attr_);
   Client_SYS.Add_To_Attr('CREATE_COST_ACCOUNTING_DB','YES',attr_);
   Client_SYS.Add_To_Attr('COST_ACCOUNTING','0',attr_);
   Client_SYS.Add_To_Attr('SET_CONTS_INVOICE',0,attr_);
   client_sys.add_to_attr('PROJECT_COST_ELEMENT','DIRECTLABOR',attr_);
   CLIENT_SYS.ADD_TO_ATTR('FOR_PRJREP','TRUE',ATTR_);
   -- (SELECT * FROM OPENQUERY(IFSTEST,'PROJECT_TRANSACTION_API.NEW__(a_, b_, c_, ATTR_, 'DO');
   PROJECT_TRANSACTION_API.NEW__(a_, b_, c_, ATTR_, 'DO');
END;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros