Andyblg's Blog

November 9, 2012

CONCSUB and XDOREPPB for r12

Filed under: ebs — andyblg @ 13:33
Tags: ,

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

Filed under: ebs — andyblg @ 16:46
Tags: ,

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

Filed under: ebs — andyblg @ 13:59
Tags: ,

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

June 27, 2012

c# to ebs r12

Filed under: ebs — andyblg @ 11:12
Tags: , ,

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]

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.