Auditing Enabling for the Standard Tables.

Most of the time we may asked to get the previous values stored in any seeded or custom tables for auiting purpose; Eg. IF a PO is created with a Unit Price of $100 and then somebody updated to $120 then if you were asked to get the old value i.e $100. We have to follow the below steps for enabling the audit for the table po_line_all for the column unit_price. Oracle will create a table like po_lines_all_a internally and it will store all the audit records with the Primary key column and the auditing column and a sequence id; For the above transaction this table contains two records With transaction_type as ‘I’ (Insert) and unit_price column would be blank. And transaction_type as ‘U’ (Update) and unit_price column would be 100. (The old value). 1)Goto System Administrtor->Audit Trail -> Tables -> Query for Define an Application User.let say you are suppose to do the audit on these tables. PO_LINES_ALL 2)Add the columns whatever you want to audit. 3)Go to Audit Trail -> Groups -> create a new group. Enable the ‘Audit Setup Group’. 4)Run Audittrail Update Table. Add the application user table under Audit Setup Group. 5)Run Audittrail Update Table. 6)Go to Audit Query Navigator ->Functional Groups-> Now you can see the Define an Application user table added under the audit setup group. 7)If the table is available as per the step 6,Now run the Audit trail report,you will be able to get the audit information.

How to make a Report Concurrent Program to end up a Warning state.

It is pretty easy to make a PLSQL Stored procedure Concurrent Program's status to Warning by assigning the value '1' to the Ret_code Out variable.

In the case of reports, we can use
declare
var boolean;
if :cs_count >10000 then
var:=fnd_concurrent.set_completion_status('WARNING','Report will show the warning state now');
end if;
The above package will return a Boolean value TRUE if it successfully completes with a Warning.
You can use it in the Before or After report Trigger or anywhere in the report trigger depends on the requirement.

Oracle Technical

Understanding XML Publisher

It is one of the recent revolution in the Oracle Apps, by which the reporting can be made easy and very fast. XML Publisher will keep the Data model and Layout separately and at run time link the layout to the correct data model and run the report and displays the output. The output can be displayed either in PDF, EXCEL or RTF formats also.

Unlike Reports Builder, it can display the Output only in Text mode i.e we can not get the GUI items like Image,Bold etc in the output if it is run in the Oracle Applications as a Concurrent Request. Usually for reporting purpose, the reports can be a mix of both text and GUI items both.

e.g Invoice Print Report is a report basically and it will display the Invoice details as the output.Usually this report can have the logo of the company on top left corner of the page, and the name of the company in the BOLD style etc.This can not be achieved with just Report Builder6i, We have to use some other tools like Optio Designer Studio where in it will try to link the concurrent program adn its layout.

XML Publisher is the tool directly from Oracle and it will do a lot more things than it has to do.Both Data model and Layout are not encapsulated will give the facility to edit teh layout without disturbing the data model. (Going forward i am using 'Data definition' for Data model and 'Template' for Layout).

If the guys who are familiar with developing the reports using Reports Builder can shift towards this XMLP and can use it.

Steps to craete a simple XMLP report.

1) Create the datamodel using Oracle Report Builder. (Leave the Layout Page empty)

2) Register it as the Conc Program and select XML as the output and save it.

3) Open the 'XML Publisher Responsibility' and create the data definition adn use the short name of the conc program created in step2 as the Data Definition code.

4) Create the Layout either in PDF Distiller or Microsoft Word. For craating the layout download the latest XMLP Desktop version and install it after installation you can see a Menu 'Template Builder in your word'. With this tool create Layout and save it.

5) Create the Template from the XML Publisher Administrator responsibility Use the same short name used earlier.

6) Load the Physical Layout file to the database.

7) Try running the report .

Oracle Applications A Magic word

Oracle Applications is my passion and you can find the most of the news about Oracle conundrums in this blog.
Oracle Applications is a product from Oracle a leading ERP product in the market.
ERP --> Enterprise Resource planning an end to end maintenance of a business transactions by automating the ever changing business needs with easily and configurable manner.