Runnng a Conc.Program from Unix command

Now i turned my side towards Unix Scripts and its relation to Oracle Apps projects.
Yes we can submit concurrent request directly from Unix command prompt.
Most of the unix lovers will have it handy.
The below piece can be useful.
$FND_TOP/bin/CONCSUB APPS/APPS \
SYSADMIN \
"System Administrator" \
VYAGHRESH \
WAIT=N \
CONCURRENT \
XXApplshortname \
XXcocnprogshortname \
param1 param2

where XXApplshortname is the Short name of the Conc Program
XXcocnprogshortname is the Short name of the conc. program we are running
Param1 and Param2 are the parameters for the program we are running.
We can sent any no.of parameters in this way.

Upon successful submission it will give the Request_id.

PO Update APIs po_change_api1_s.update_po

It is quite often to update some of the lines of a PO after it is approved.
In order to Update a PO, we can use the API po_change_api1_s.update_po.

It Update a standard purchase order or release changes of quantity, price, promise date and Need-by-date.This Need-by-date updation with this API has been enhanced in R12 only. In earlier releases updating the PO line’s Need-by-date is not available.

Psudo code of the usage
l_result := po_change_api1_s.update_po
(x_po_number => p_po_num ,
x_release_number => NULL ,
x_revision_number => l_revision_no,
x_line_number => lines_rec.line_num,
x_shipment_number => lines_rec.shipment_num,
new_quantity => NULL,
new_price => NULL,
new_promised_date => NULL ,
new_need_by_date => TO_DATE(nedbydate,'DD-MON-RRRR'),
launch_approvals_flag => 'N' ,
update_source => NULL,
VERSION => '1.0',
x_api_errors => l_api_errors,
p_buyer_name => Lines_rec.agent_name,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => g_org_id);

It will either return a 1 for successful update or 0 for failure.

Pre-requisites to use this API
1) PO header to be either in APPROVED or REQUIRES REAPPROVAL status.
2) No update if the revision number doesn't match the current revision.
Post Update
1)Upon successful updation,the PO revision will be incremented.
2) if launch_approvals_flag is set to 'Y' then it will launch the PO Approval. But if you set this parameter as 'Y' then the program try to submit the PO Approval for every successful update of the line.Most of the time this Approval will not update the PO to APPROVED, it may be delay waiting for some other input, so it will make the PO status as INPROCESS.
So please avoid to launch the Po Approval with this Update PO API.
3)The authorization status of the PO will be in REQUIRES REAPPROVAL is the above paramter is set to 'N'

Difficulties faced:
1) This will update the PO if it have one line without any issue in the first run.
2) If the PO have more than one line, then we need to run the Update program the no.of lines po have.We need to pass the Po line num for every run.
3)If the first PO line got updated then revision will gets chnaged to next highest number.So we need to grab the revision number for every updated line and need to input to the above program.
4) By doing this we can avoid the weird error 'The revision number doesn't match the current revision'.
5)Need to launch the PO approval manually because it will leave teh PO in REQUIRES REAPPROVAL state.

To Find the Sales Order's Subtotal,discount,charges and Tax

It is quite common to find the Sales orders Total amount distributions such as Sub total (Line Total),Discount,Tax and Charges if any.

These distribution amounts will not be the straight database columns in the Order Management tables.
In order to find these need to explore the API.i.e.

OE_OE_TOTALS_SUMMARY.ORDER_TOTALS(
header_id In,
subtotal,out,
discount out,
charges out,
Tax out);

Total amunt of the Order=Subtotal+charges+Tax.

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.