Sub Templates in Oracle XML BI Publisher

There May be scenarios that where and when we have some part of XML data needs to be represent in fixed format and remaining XML data needs to be represent in multiple formats based on some conditions (let us say for each country specific remaining data XML data needs to be processed) in that case  once after registering the concurrent program (either rdf or Data template type) we can create one Default data template for the concurrent program  and other multiple sub templates for each country specific .

 Create one Default or main Data template for concurrent program along with the multiple sub templates for the same data definition in oracle apps.

We can create sub templates in the oracle apps by selecting sub template option as "Yes" while creating template using Xml Publisher Administrator responsibility.

While creating the main or Default Data template RTF file first we have to import all the sub templates that are going to be called in this as below.

<?import:xdo://XXPIC.TEST_FRT.en.us?>

xxpic ->Application Shortname
TEST_FRT-> Subtemplate Code.

After Importing all required sub templates  next we have to design the layout that is common in all cases.

Now based on conditions we have to call different sub templates.

<?IF:SUBTEMPLATE='TEST_FRT'?> <?call-template:TEST?> <?END IF?>
<?IF:SUBTEMPLATE='TEST_FRT2'?><?call-template:TEST1?><?END IF?>

Here TEST and TEST 1 are Sub template Names

This concludes the creation of the Main RTF template.

Now in each Sub template we have to design the layout between below tags.

<?TEMPLATE:TEST?>
----Layout design---
<?END TEMPLATE?>

TEST : Sub template Name


Please See this Oracle white Paper on Sub templates

Dropship Orders in Order Management R12

What is Dropship Order - In dropship Order, sales order line creates a requisition line that becomes a PO and sent to your supplier. You instruct your supplier to send the item or configured item directly to your customer only and items never physically pass through your warehouse, and therefore you do not pick, pack or ship them yourselves

In this blog I will explain the R12 Dataflow of Dropship order from OM to Oracle Payables.
Topics covered in this blog are
Drop Ship Sales order creation and booking.
Requisition Import
Auto Creation of PO
Receipt against PO
AP against PO


Since in Dropship we don’t receive the goods in our warehouse, SOURCE_TYPE will be “External”, where as for non-dropship orders it is Internal. Once Book, we cannot Changes SOURCE_TYPE.

We can change the SOURCE_TYPE as “External” for an item at the time of order creation, or we can define it permanently in the Item creation Setup, by setting Default SO Source Type = External.

Also as we don’t have any shipping activity in dropship orders, Receiving Transaction Processor (RTP) , triggered after Submitting the receipt will push the workflow from Shipping activity to Fulfillment.
R12 Dataflow of Dropship order from OM to Oracle Payables.

Step1 - Drop ship Sales Order Creation

Step2 - Source Type = External

Step3 -Once order is Book, order line workflow will be at Purchase release Deferred Status .

Step4 -Submit Workflow to progress the Order line workflow.

Step5 -Submit the requisition Import to create Requisition for Drop Ship

Step6 -Once requisition Import Completed successfully , Navigate to Order lines Action >Additional Information and then go to DropShip tab.

Step7 - Switch to Purchasing Responsibility and Navigate to Requisition Summary

Step8 -Query for Requisition Number

Step9 – Open the Auto Create Window and Query for Requisition #

Step9 –Auto Create

Step10 -Approve the PO

Step11 - Browse the Shipment Details of PO , (Shipment Button)

Step11 - Browse the Shipment Details of PO, (Shipment Button).We can select the Approval Level and Invoice Match Option based on your business requirements.

Step11 - Browse the Shipment Details of PO , (Shipment button).

Step11 - Browse the Shipment Details of PO , (Shipment Button), it will display Qty Ordered/billed/shipped etc.

Step12 - Browse the Distribution Details of PO , (Distribution Button).

Step13 - Navigate to receipts.

Step13 - Navigate to Receipt , system will prompt for Inventory Org , Select your Inventory Organization.

Step14 - Enter Receipt against PO.

Step15 - Once you save the Receipt , System will submit Receiving Transaction processor.

Step16 - RTP on successful completion Progress the OM Workflow and Line status change from Awaiting receipt to SHIPPED.

http://oracleapplicationsfunctional.blogspot.in/2011/08/business-flow-of-drop-ship-order.html

How to kill sessions from backend

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine   FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id;  

ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';  

ALTER SYSTEM KILL SESSION '1105,389';  

commit;

What are user exits in oracle reports

User exits in oracle reports:

A User Exit is an API, which provides a way to pass control from Reports Builder to a 3GL program that performs some function, and then returns control to Reports Builder.

Using these we can integrate Oracle reports with Oracle apps AOL, and run them as concurrent programs.

Following are the user exits available in Oracle Reports that makes the AOL integration:
FND SRWINIT
FND SRWEXIT
FND FORMAT_CURRENCY
FND FLEXIDVAL
FND FLEXSQL

When the concurrent manager runs an Oracle Report concurrent request, it passes the concurrent request id in the P_CONC_REQUEST_ID parameter. When you call the FND SRWINIT user exit in the report’s Before Report trigger, the user exit uses the passed request id to query the Oracle Applications context that the concurrent request was submitted in (via the FND_CONCURRENT_REQUESTS table) and then log on with that context. 

This allows you to use operating specific views, access profile option values, etc within your report. You should also call the FND SRWEXIT user exit in the After Report trigger.

If you try calling the FND SRWINIT user exit in your report without the P_CONC_REQUEST_ID parameter defined as a user parameter on your report, the user exit will fail.

Material Transaction Distribution button is disabled

The cost cut off  had  an impact in processing the transaction above this date. After I  the made this as Null all the transactions  got successfully submitted and able to their distributions as well.

Query to find all Oracle Discoverer Reports/Workbooks

Run the following sequence of queries in APPS schema to find all Discoverer Reports/Workbooks that exist in the system.

Query1: Run the following query to know the name of table

SELECT owner,table_name
  FROM all_tables
 WHERE owner = 'EUL4_US'
   AND table_name LIKE '%DOCUMENT%';

Generally the above query returns table_name as 'EUL4_DOCUMENTS' 
Meaning, all the discoverer report/Workbook names are stored in this table

Query 2: Run the following query to see all reports

SELECT DOC_ID,
       DOC_NAME,
       DOC_DEVELOPER_KEY,
       DOC_CREATED_BY
  FROM EUL4_US.EUL4_DOCUMENTS;

Note:
If the First Query is not returning any data then try the following query and manually find the table name.

   SELECT owner,table_name
     FROM all_tables
    WHERE table_name LIKE '%EUL%DOCUMENT%';

You have to modify the 2nd query in this case with the new OWNER.TABLE_NAME

Register a Discoverer report in Oracle Apps

Steps for Register a Discoverer report in Oracle Apps. 

1. Login to Application Developer-->Function

2. Enter Function , User Function Name, Description(Optional) in Description Tab.
    E.g:

• Function: TEST_DISCO
• User Function Name: Discoverer Report
• Description: Discoverer Report

3. In Properties Tab

• Select Type “SSWA plsql function”
• Maintenance Mode Support – None
• Context Dependence - Responsibility

4. In Form Tab Parameters --> Enter the Workbook name created previously
     E.g:

• workbook=TEST_WORKBOOK
• In case you want to give some parameters then you can use:

workbook=<workbook_name>&parameters=<Parameter_nam e1>~<value1>*<Parameter_name2>~<value2>*<Parameter _name3>~<value3>*

 
Example

workbook=<workbook_name>&Parameters=age~26*salary~ 1000*

5. In Web HTML Tab

Enter HTML Call - OracleOasis.RunDiscoverer

6. Now you can add this Function in any existing or new Menu

Also Note that Discoverer Report cannot be invoked when you’ve direct login into Oracle Applications through http://<url>:<port number>/dev60cgi/f60cgi
You need to access through logging in from the browser web page. Otherwise it will give an Error.

7. Now navigate to Responsibility which contain the Menu to which you added this function.

8. Click the Function Name to Display the report.