CONCSUB and XDOREPPB for r12

Main discussion in Thread: 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

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]

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
+---------------------------------------------------------------------------+

From Big Data to Big Value by Gwen Shapira

Interesting note by Gwen Shapira

From Big Data to Big Value

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:

• http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/

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]

F1 RDBMS by Google

Abstract by Google

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

• http://ranetuilibraryolap.codeplex.com

• http://code.google.com/p/ranet-uilibrary-olap

DWH Basics By Dylan Wan (remark)

Data Warehouse Basics By Dylan Wan on Feb 15, 2008