using “sys” with “as sysdba” in odbc connection like
$username = "sys" $password = "syspassword as sysdba" $dsn="siebeldb"
November 30, 2014
odbc source to access oracle database by “sys as sysdba”
November 9, 2012
CONCSUB and XDOREPPB for r12
Main discussion in Thread: How to submit BIP report using CONCSUB utility (after upgrade is How to submit BIP report using CONCSUB utility)
Before test:
– create Data Definition XXAND17 with Data Template as
<dataQuery> <sqlStatement name="Q_1"> <![CDATA[ select ct.customer_trx_id, ct.trx_number, ct.trx_date, ctt.type from ra_customer_trx_all ct, ra_cust_trx_types_all ctt where 1 = 1 AND ctt.cust_trx_type_id(+) = ct.cust_trx_type_id and rownum <= 3 ]]> </sqlStatement></dataQuery> <dataStructure> <group name="G_trxs" dataType="varchar2" source="Q_1"> <element name="trx_number" dataType="varchar2" value="trx_number"/> <element name="trx_date" dataType="date" value="trx_date"/> <element name="doc_type" dataType="number" value="doc_type"/> </group>
– create template XXAND17 with RTF layout
– create concurrent program XXAND17 and add to group of FND
Test:
[oracle@oebs out]$ CONCSUB APPS/APPS FND 'Application Developer' SYSADMIN WAIT=N CONCURRENT FND XXAND17 Submitted request 9915625 for CONCURRENT FND XXAND17
Find result:
[oracle@oebs ~]$ find /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/ -iname '*9915625*' /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/o9915625.out
open it by pwd $APPLCSF/$APPLOUT
<?xml version="1.0" encoding="UTF-8"?> <XXAND5> <LIST_G_TRXS> <G_TRXS> <TRX_NUMBER>10000000</TRX_NUMBER> <TRX_DATE>1997-03-11T00:00:00.000+03:00</TRX_DATE> <DOC_TYPE/> </G_TRXS> <G_TRXS> <TRX_NUMBER>10000000</TRX_NUMBER> <TRX_DATE>1997-03-11T00:00:00.000+03:00</TRX_DATE> <DOC_TYPE/> </G_TRXS> <G_TRXS> <TRX_NUMBER>10000000</TRX_NUMBER> <TRX_DATE>1997-03-11T00:00:00.000+03:00</TRX_DATE> <DOC_TYPE/> </G_TRXS> </LIST_G_TRXS> </XXAND5>
Next format xml …
<code>[oracle@oebs out]$ CONCSUB apps/apps FND 'Application Developer' SYSADMIN WAIT=N CONCURRENT XDO XDOREPPB Y 9915625 0 XXAND17 en N RTF RTF Submitted request 9915626 for CONCURRENT XDO XDOREPPB Y 9915625 0 XXAND17 en N RTF RTF
Find result it’s must be RTF output
[oracle@oebs ~]$ find /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/ -iname '*9915626*' /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/o9915626.out [oracle@oebs ~]$
Oops “.out”, not “.rtf”
If we find 9915626 by View->Request and “View Output” then you can see RTF output as well.
Open o9915626.out
{\rtf1\ansi\deff0
{\fonttbl
{\f0 Arial;}
{\f1 Times;}
{\f2 Courier New;}
{\f3 Calibri;}
}
{\colortbl
;
\red0\green0\blue0;
\red231\green243\blue253;
\red255\green255\blue255;
}
{\info
{\title RTF Template}
{\doccomm Generated by Oracle XML Publisher 5.6.3}
}
\formshade\viewkind1\viewbksp1\uc1\dntblnsbdb
it’s look like rtf
so
[oracle@oebs ~]$ cd /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out [oracle@oebs out]$ cp o9915626.out o9915626.rtf
Open o9915626.rtf by ms word and it’s Ok.
But accordantly fnd_conc_req_outputs
This table stores output files created by Concurrent Request. Initial output file generated by Concurrent Request will be stored in FND_CONCURRENT_REQUESTS table and all remaining output files generated Concurrent Request including XML Publisher generated output will be stored in this table.
hm
SQL> select LOGFILE_NAME, OUTFILE_NAME from fnd_concurrent_requests r 2 where request_id in (9915625, 9915626) 3 / LOGFILE_NAME OUTFILE_NAME -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/log/l9915625.req /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/o9915625.out /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/log/l9915626.req /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/o9915626.out SQL> select * from fnd_conc_req_outputs where concurrent_request_id in (9915625, 9915626) 2 / CONCURRENT_REQUEST_ID OUTPUT_ID FILE_TYPE FILE_NAME FILE_NODE_NAME FILE_SIZE ACTION_TYPE FILE_CREATION_DATE --------------------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------- ------------------ SQL>
but what about GUI. Submit request for XXAND17 with id = 9916625
SQL> select LOGFILE_NAME, OUTFILE_NAME from fnd_concurrent_requests r 2 where request_id in (9916625) 3 / LOGFILE_NAME OUTFILE_NAME -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/log/l9916625.req /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/o9916625.out SQL> select * from fnd_conc_req_outputs where concurrent_request_id in (9916625) 2 / CONCURRENT_REQUEST_ID OUTPUT_ID FILE_TYPE FILE_NAME FILE_NODE_NAME FILE_SIZE ACTION_TYPE FILE_CREATION_DATE --------------------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------- ------------------ 9916625 17265 RTF /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/XXAND17_9916625_1.RTF OEBS 4855 6 09.11.2012 SQL>
we have row for 9916625 in fnd_conc_req_outputs. Nice but what about os output
[oracle@oebs out]$ find /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/ -iname '*9916625*' /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/o9916625.out /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/XXAND17_9916625_1.RTF [oracle@oebs out]$
Yes. Rtf output. Nice.
as for PDF
[oracle@oebs out]$ CONCSUB apps/apps FND 'Application Developer' SYSADMIN WAIT=N CONCURRENT XDO XDOREPPB Y 9915625 0 XXAND17 en N RTF PDF Submitted request 9917625 for CONCURRENT XDO XDOREPPB Y 9915625 0 XXAND17 en N RTF PDF [oracle@oebs out]$
If we find 9917625 by View->Request and “View Output” then you can see PDF output as well. Ok but what about os output
[oracle@oebs out]$ find /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/ -iname '*9917625*' /oracle/VIS/inst/apps/VIS_oebs/logs/appl/conc/out/o9917625.out [oracle@oebs out]$
so try as for rtf
[oracle@oebs out]$ cp o9917625.out o9917625.pdf
and yes it’s pdf output as well.
so conclusion:
XDOREPPB work correctly but not rename output accordantly output format (rtf, pdf in my case)
it’s also not create row in fnd_conc_req_outputs. Strange …
so it’s a bug or some profile must be set
comment any ideas plz
October 9, 2012
approval status for invoice in r12
list of approval status can be selected based on ap_invoices_utility_pkg.get_approval_status.
it returns:
'CANCELLED'
'APPROVED'
'NEEDS REAPPROVAL'
'NEVER APPROVED'
'UNPAID'
'FULL'
'PERMANENT'
'AVAILABLE'
'UNAPPROVED'
for displayed approval status we can use lookups like
select * from (select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'NLS TRANSLATION' and lookup_code = 'APPROVED' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'NLS TRANSLATION' and lookup_code = 'NEVER APPROVED' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'NLS TRANSLATION' and lookup_code = 'CANCELLED' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'NLS TRANSLATION' and lookup_code = 'NEEDS REAPPROVAL' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'PREPAY STATUS' and lookup_code = 'UNPAID' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'PREPAY STATUS' and lookup_code = 'FULL' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'PREPAY STATUS' and lookup_code = 'PERMANENT' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'PREPAY STATUS' and lookup_code = 'AVAILABLE' union all select meaning, lookup_code, language from FND_LOOKUP_VALUES where lookup_type = 'PREPAY STATUS' and lookup_code = 'UNAPPROVED') where language = 'US'
for Validation status:
http://www.shareoracleapps.com/2010/10/api-to-find-ap-invoice-status.html
MOS: Where Is The Invoice Validation Status Stored? How Is The Invoice Validation Status Determined? [ID 301806.1]
August 23, 2012
run concurrent program twice with different layout r12
Task:
using one xml data definition and several layout of report and generate it in one step by main concurrent program
Plan:
1) create data definition (XXAND10_1)
2) create two template with different content (XXAND10_1 and XXAND10_2) but based on one data definition
3) create concurrent for working (merging) with data definition and template (XXAND10_1)
4) create main concurrent which will run one concurrent with different template
Go…..
Step 1:
Step 2:
Step 3:
Step 4:
procedure conc_twice(errbuf out varchar2,
retcode out varchar2)
is
v_request_id number;
xml_layout boolean;
begin
xml_layout := FND_REQUEST.ADD_LAYOUT('FND','XXAND10_1','en','US','RTF');
v_request_id := fnd_request.submit_request('FND','XXAND10_1','Template 1',NULL,FALSE, null);
if v_request_id > 0 then
fnd_file.put_line(fnd_file.log,'XXAND10_1 with Template 1 - successfully submitted.');
commit;
else
fnd_file.put_line(fnd_file.log,'XXAND10_1 with Template 1 - not submitted.');
rollback;
end if;
xml_layout := FND_REQUEST.ADD_LAYOUT('FND','XXAND10_2','en','US','RTF');
v_request_id := fnd_request.submit_request('FND','XXAND10_1','Template 2',NULL,FALSE, null);
if v_request_id > 0 then
fnd_file.put_line(fnd_file.log,'XXAND10_1 with Template 2 - successfully submitted.');
commit;
else
fnd_file.put_line(fnd_file.log,'XXAND10_1 with Template 2 - not submitted.');
rollback;
end if;
end;
Add XXAND10 concurrent program to reguest group
Test:
+---------------------------------------------------------------------------+ Start of log messages from FND_FILE +---------------------------------------------------------------------------+ XXAND10_1 with Template 1 - successfully submitted. XXAND10_1 with Template 2 - successfully submitted. +---------------------------------------------------------------------------+ End of log messages from FND_FILE +---------------------------------------------------------------------------+
August 1, 2012
From Big Data to Big Value by Gwen Shapira
Interesting note by Gwen Shapira
July 19, 2012
OPatch for Developer Suite 10g for Win7-64
Before all some useful MOS:
- Master Note For OPatch [ID 293369.1]
- OPatch for Oracle Application Server 10g (10.1.2, 10.1.3, 10.1.4) [ID 283367.1]
- Opatch errors when Perl is not correctly configured [ID 395240.1]
- Certification of Oracle Developer Suite 10g (10.1.2) on Windows 7 (32-bit) [ID 1292919.1]
And so…
I installed Developer Suite 10g and want to apply some patches
I use p6880880_101000_WINNT.zip and extract into C:\TEMP and it’ll create C:\TEMP\OPatch
Downloaded, for example, p9774440_10123_WINNT.zip and extract into C:\TEMP and it’ll create C:\TEMP\9774440
> set ORACLE_HOME=c:\OraDevSuite10 > set PATH=c:\Temp\OPatch;C:\OraDevSuite10\perl\5.6.1\bin\MSWin32-x86;%PATH% > set PERL5LIB=C:\OraDevSuite10\perl\5.6.1\bin\MSWin32-x86;C:\OraDevSuite10\perl\5.6.1\lib;C:\OraDevSuite10\perl\5.6.1\lib\MSWin32-x86;C:\OraDevSuite10\perl\site\5.6.1;C:\OraDevSuite10\perl\site\5.6.1\lib; > perl -v > cd c:\Temp\9774440 > perl C:\TEMP\OPatch\opatch.pl apply
and
OPatch succeeded.
also useful:
June 27, 2012
c# to ebs r12
i try to connect to ebs r12 and retrieve some data
in ebs check init session
SQL> select count(*) from ap_invoices;
COUNT(*)
----------
0
SQL>
SQL> begin
2 mo_global.init('SQLAP');
3 begin fnd_global.apps_initialize(0, 20639, 200); end;
4 end;
5 /
PL/SQL procedure successfully completed
SQL> select count(*) from ap_invoices;
COUNT(*)
----------
33336
SQL>
in ebs r12 you must use mo_global for init session. in code below remove mo_global.init and check
then in vs 2008 + odac without init session
put on form button and dataGridView1
and for button
private void button1_Click(object sender, EventArgs e)
{
String conString = "User Id=apps; Password=apps; Data Source=172.16.21.40:1521/vis; Enlist=False; Pooling=False;";
OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
try
{
con.Open();
OracleCommand cmd = con.CreateCommand();
// init session
//cmd.CommandText = @"begin apps.mo_global.init('SQLAP'); begin apps.fnd_global.apps_initialize(5710, 20639, 200); end; end;";
//cmd.ExecuteNonQuery();
cmd.CommandText = "select count(*) from ap_invoices";
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
then init session
private void button1_Click(object sender, EventArgs e)
{
String conString = "User Id=apps; Password=apps; Data Source=172.16.21.40:1521/vis; Enlist=False; Pooling=False;";
OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
try
{
con.Open();
OracleCommand cmd = con.CreateCommand();
// init session
cmd.CommandText = @"begin apps.mo_global.init('SQLAP'); begin apps.fnd_global.apps_initialize(5710, 20639, 200); end; end;";
cmd.ExecuteNonQuery();
cmd.CommandText = "select count(*) from ap_invoices";
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
for connectiong string:
Using ODP.NET To Call Oracle Apps Stored Procedures Results In Unexpected Behavior On Second Call [ID 1351984.1]
May 31, 2012
November 18, 2011
OLAP Versus Big Data
Very interesting discussion OLAP Versus Big Data from datawarehouse.ittoolbox.com and very interesting answer Peter J
“It doesn’t have to be Big data vs BI or OLAP.
Big data has become the hot trend and most people have no clue what it
really is or how to actually use it.
Great they can get more data and not do anything with it. It’s the whole
grab as much data and it’ll ‘automagically’ just work for you. Tons of
companies market GUI front ends that don’t do anything for a company if the
data is crap. Sell and market whatever you want, if the data stinks, it
just doesn’t matter. And right now big data is more about the technology
than the business.Yeah yahoo, google, facebook, linkedin, groupon etc can throw out their big
data usage, their massive work on big data, but those companies rely on
unstructured data, streaming data, near real time metrics, etc. They all
are more or less advertising companies. Without advertising, those
companies make no money. Banks and wall street have a need for big data
because they need tons of data, lots of unstructured data, and they need it
yesterday.A lot of big data is really just unstructured data. How to get the logs,
the web streams, the emails, the social media status updates, maybe image
and video info and find useful ways to help your business or organization.
It can be a few terabytes or a few petabytes. It’s more about structured
or barely structured over tons of data.OLAP and BI systems are mostly structured, single versions of truth. Do
it the Kimball way, Inmon, or what not doesn’t matter because you take
different systems, different data, normalize it or denormalize it, wrap it
up into star schemas, and run calculations and aggregations on it and then
build cubes or reports based off of that. It’s all the same in regards to
having one large EDW or a ton of silo data marts. it’s mostly structured
data.So it’s not about doing OLAP or Big Data, it’s figuring a way to integrate
Big Data, mostly unstructured data, with your BI and MDM systems.
Integrate it all together and it becomes useful. Building one big data
system, another BI system, another MDM system, a bunch of OLTP systems, and
so on just means you’re still doing what’s always been done. And that’s
having a ton of different systems that really don’t talk to one another or
relate to one another.The technology doesn’t matter. It’s about how to use that data to get
some kind of return(more $$$, savings, better data, reports, charts, etc)
for an organization or business. Starting a big data project just because
it’s hot and you need to do it won’t change the facts. If the data still
stinks, it’ll just be another system nobody really uses. At least not use
it well. There is a big reason why, even after all these years, most people
still use Excel spreadsheets to do half their analytics and reporting
needs. More technology isn’t going to help a secretary or mid level
manager who could care less how you get the data.”
Ranet OLAP
” What is Ranet OLAP?
Components library Ranet.UILibrary.OLAP is intended for creating full-featured business intelligence applications (RIA, Rich Internet Applications) based on the Microsoft SQL Server Analysis Services platform.
Components library Ranet.UILibrary.OLAP is implemented based on the most resent Web-technologies: Microsoft .NET and Microsoft Silverlight.
It can be used for displaying data from Microsoft SQL Server Analysis Services 2005/2008 using most popular Web-browsers: Mozilla Firefox, Google Chrome, Internet Explorer. “
MDX Parser,Builder,DOM and OLAP visual controls with Writeback for Silverlight
Andyblg's Blog 