Queries For Oracle Interface Errors Records.

Below are the queries to get the error records information while processing interface data.

AP Invoices:

   airl.last_update_date process_date,
  nvl(ail.invoice_line_id,aii.invoice_id) process_id,
  'AP' module,
  'Import AP Invoice' interface_name,
  aii.source source,
  NVL(SUBSTR( ail.dist_code_concatenated,1,instr(ail.dist_code_concatenated,'.',1,1)-1),'ALL') company,
  'ERROR' status,
  ail.line_type_lookup_code ,
  airl.parent_table ,
  ail.description ,
  NVL(ail.distribution_set_name,dist_code_concatenated) ,
  pvsa.vendor_site_code ,
  listagg(NVL(airl.reject_lookup_code,'AP_INV_ERROR') ,';') within group (order by NVL(airl.reject_lookup_code,'AP_INV_ERROR')) error_code,
  listagg(flv.meaning,';')  within group (order by NVL(flv.meaning,'AP_INV_ERROR'))error_summary,
  NULL error_details,
  pv.vendor_name ,
  pv.segment1 ,
  aii.invoice_date ,…

Oracle Reports For Dot Matrix Printers to Print output on Stationary

I would like to share my experience on developing the reports which output should print on the preformed stationary using the dot matrix printers.

If we have to print the output on dot matrix printer it would be better if we should generate the report which  gives the output in normal plain text format.
We can generate the text output reports either by using oracle RDF reports or XSL-FO text output bi publisher report.
Before starting the development  we need to ensure the below points.

we should have the exact hard copy of stationary (or photo copy of it) on which data should print.If we get photo copy then it should have the same dimensions like length and width of the original stationary.Has to fix or load the stationary in dot matrix printer as per the printer guidelines always. Identify the number of lines should feed for each copy of the print so that we can make sure in the report should generate same number of lines for each copy.Identify for each section of information starti…

Customising PO Output For Communication Report in Oracle Purchasing

PO Print PDF Report:

Oracle EBS provided  a standard functionality to print the PO report directly from the PO form itself by clicking tools menu and view PDF option. Once we select that option output will be rendered in PDF format in the browser instantly.

Internally it will run the concurrent program 'PO Output For Communication'. This concurrent program is of JAVA type which will uses the seeded package  'PO_COMMUNICATION_PVT'  for generating the XML data and later this XML data will be processed and formatted by using the XSL template of data definition 'Standard Purchase Order Data Source' in case of standard PO's.

Oracle by default will provide one standard BI publisher template for this. In case if we want to have custom layout for this report we can build a new template for the same data definition and this template has to be used while doing the setup for PO Document types.

Creating Custom Template and Setup this for PO Document type:
To create the…

RDF Reports FAQ

RDF Reports FAQ:
DATA TRIGGERS: Ref Cursor Query.  This uses PL/SQL to fetch data for the report. In this a PL/SQL function need to be specified to return a cursor value from a cursor variable. Group Filter:  This is PL/SQL function that determines which record to be included in a group in the property of PL/SQL.  The function must return a BOOLEAN value. True … Includes the current record in the report. False…. Excludes the current record from the report. Formula  These are PL/SQL functions that populate formula or place holder columns.
Validation Trigger  These are also PL/SQL functions that are executed when parameter values specified on the command line and when the run time parameter form is accepted.  Are also used to validate the initial value property of the parameter.
Layout TriggersFormat Trigger. These are PL/SQL functions executed before the object is formatted. Used to dynamically change the formatting attributes of the object.
Action Trigger These are PI/SQL proc…

Oracle APPS Useful Queries


Payable Open Interface Rejection:

SELECT ail.*
  ap_invoice_lines_interface ail ,
  ap_interface_rejections AIR
WHERE ai.GROUP_ID='EO_AUG-13_010813_443'
AND ai.status    ='REJECTED'
AND ai.invoice_id=ail.invoice_id
  --and ail.dist_code_concatenated='66810-00-999-00000000-00000000-00000-FI100'
AND air.parent_id = DECODE(air.parent_table,'AP_INVOICES_INTERFACE',AI.INVOICE_ID,'AP_INVOICE_LINES_INTERFACE',ail.invoice_line_id,NULL)

AP Invoices Query:


SELECT  apia.invoice_id,
       alc.displayed_field "Invoice Type",
       apia.invoice_num "Invoice Number",
       apia.invoice_date "Invoice Date",
       apia.gl_date   "Invoice GL Date",
       nvl(apia.VOUCHER_NUM,doc_sequence_value)"Voucher Number",
       apia.description inv_Description,

UTL File Processing

In ORACLE PL/SQL programs to write or read any kind of data from the file to the system and vice versa we will make use of the oracle standard API UTL FILE . 

To use this feature first user have to create both logical and physical directories in the data base server. If we are writing into the file then the user should have the write privilege and to read user should have the read privilege to the directory. This UTL_FILE package or API is owned by SYS user.

1. Create the Directory As Below.
In the above XXDIRECTORY is a Directory name and  'C:\abc\axefolder\GL\UTL_EX' is a directory path in the oracle database server.

2. Grant the Privileges.
3.Create the physical Directories.

4. Add the XXDIRECTORY path (i.e'C:\abc\axefolder\GL\UTL_EX') to UTIL_FILE parameter.     We can check the current parameter value by using the below query 
SELECT value FROM v$par…