Category Archives: Oracle Apps

XML Bursting from RDF

XML Publisher Bursting from RDF

Create a new Oracle Report using Oracle Report Builder using the following query:

Create new RDF as below:

Open Report Builder, Click on SQL Query in data model and enter your sql valid query.



Connect to database:


In the query passing two bind parameters (ORG_ID & SALES_ORDER_NUMBER) Click on ok button and the Variables are created in User Parameters Section.




Add one more mandatory parameter P_CONC_REQUEST_ID to have Trace generated after the Concurrent Program.


Save your report.



Compile the Report using Ctrl+Shift+K :


Move your report to custom top:



XML Bursting2

Related posts:

Oracle Trading Community Architecture (TCA)

Oracle Trading Community Architecture (TCA)

Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them using the TCA registry.

TCA registry is the single source of trading community information for Oracle E-Business Suite applications which provide user interfaces, batch data entry functionality, and other features to view, create, and update Registry information.

TCA key entities:

Parties:  Parties are the entities of type person/organization that enter into a business relationship

Party Sites: Addresses of the parties

Customers: Parties with whom you have a selling relationship

Customer Accounts: The business relationships between you and your customers

Customer Account Sites: Address of the party used in the case of customer accounts

Locations: Geo-spatial points, usually defined by an address

Contacts: People who have a contact or employment relationship with an organization or person

Contact Points: Means of contact, for example, phone and e-mail address

Process Flow:

This diagram shows the process flow for managing, searching, creating, and updating customer information.

R12 Trading Community Architecture:

Includes following new trading entities:

  • Bank and Bank Branches
  • Customers
  • Suppliers

TCA-Bank and Bank Branches:

The TCA tables used by Cash Management for modeling Banks and Bank Branches are listed below:

Three key CE tables now as:

    • CE_BANK_ACCOUNTS for bank accounts
    • CE_BANK_ACCT_USES_ALL for account uses by Operating Units & Legal Entities
    • CE_GL_ACCOUNTS_CCID for bank account use accounting data


The word “Customer” is the combination of both the “Party layer” and the “Account layer”.

  • Party layer exists independent of any selling or buying relationship.
  • Customer Account layer exists in the context of a Party and only when a selling      relationship exists.
  • The Party Layer captures intrinsic truths about a person or organization.
  • The Account Layer captures the details describing the Party’s financial relationship with the implementing organization.
  • The Account Layer cannot exist without the Party Layer.


Following are the three new AP tables’ technical details for R12 Supplier in TCA. They have the links to TCA tables which are listed below.


TCA ER-Diagram:




Related posts:

Issues releated to Back Order in Oracle OM

Any issue related to Back ordering will definitely fall 1 among these cases:

1. Check in Shipping Transaction Form, make sure the order still in “Ready to Release”.
2. There might be a lot of possibilities for this problem:

  • Order on hold
  • Do not have enough qty
  • Lot expired
  • Wrong reservation (even inventory have enough qty)
  • Inventory reserved for other orders.
  • Inventory picked up by other orders
  • Previously return to stock not done properly.

3. Case 1, check if the order is on hold. Check the order type in Shipping Transaction Form ® “Detail” ® “Source”.
4. There is another way to check the order type. That is go to WMS Control Board ® “Picking”.
5. Or go to OM Shipping View to see the order type.
6. Go to the correct “Sales Order View” instant accordingly by keying in the order# and order type.
7. Click on “Open Order” ® “Action” ® “Additional order info”.
8. Do the same for order level and line level.
9. Case 2 & 3, go to Material Workbench to check if the lot has expired or not having enough inventory for this order.
10. We can check if any lot is reserved for other order as well.
11. In Material Work Bench, go check for each lot in the physical locator by clicking on the “Attribute” button. (lot level reservation)
12. Alternatively, go to Shipping Transaction Form ® OM Shipping View, or go to “Lot Number” (Ctrl + L), click on Attribute column to check the SO reservation of the lot.
13. Compare the SO info with the order that are having problem.
14. Case 4, go “Reserve Supply” form (Ctrl + L) to check if the order is wrongly reserved.
15. Case 5, in the “Reserve Supply form, we can check if the qty is being picked up by other order as well. (order level reservation regardless of lot)
16. Also, can go to Material Workbench ® Availability, to check for the available to reserve.
17. Case 6, it might have high possibility the qty is taken up by order with different line, or other orders which have been auto launched.
18. If this is the case, must do backorder.
19. Go to Transact Move Order Form ® tab “Pick Wave” ® View/Update allocation ® Lot/Serial.
20. Click on the checkbox for order which need to be backordered, then go to Tool ® Back Order Line.
21. Case 7, ask the user if the order has done return to stock previously.
22. If yes, it might has high possibility that the return to stock not done properly.
23. Check the lot state, must be in “Resides in inventory”.
24. Check in Material Workbench to see if the lot is still packed with LPN or not.
25. Check also if the lot is in physical locator rather than logical locator.

Source for this post is from Oracle Forums.

Related posts:

Workflow with Approval Notification

Creating WF Local Roles:

Adhoc roles can be created through PL/SQL from database or they can be created from Applications using User Management Responsibility. If PL/SQL is used to create roles, all user names and role names in UPPER case to avoid some errors:

Script to Create an Adhoc Role:

 Script to validate WF Roles:

 Script to Add user to an already existing Adhoc Role:

 Script to Remove user from an existing Adhoc Role:

Using Adhoc roles in workflow notifications:
Once the Roles are created in the database we can load the Roles from database as discussed from Oracle Worflow Builder as below:

Navigation: File > Load Roles from Database

Select roles you want to use and then click OK.



Open the notification properties and then navigate to node tab, select performer as the role you just created and loaded from database.

Modified Package Code:

Sample Code to test the workflow:

 Sample scripts to test the Workflow:

Once triggered See the below Approval Notification for either Workflow –> Notifications or directly on the screen depending on the Profile Option setup:


Open the Notification:


Once Approved see the Activity history or Status Diagram from Status Monitor. This can be done at any stage of the workflow once kicked off. Usually while debugging this is the first place we need to check and then check in wf tables.





Some of the workflow tables:



Please respond if any questions.

Related posts:

Workflow with Approval Notification

Sample workflow with Approval notification:

In the node change the Performer type to Item attribute as we intent to set the attribute value from function. We can also load the roles from database and add it here. The timeout is also important in a notification as if the user does not respond in a particular time period we can control the workflow path. The Time out can also be an item attribute.


Drag another end so that when in the notification when user selects another result which is Reject, it should also flow till the end. Use can also use default as when there are multiple results, then other than the result mentioned in the flow rest all comes under this flow. Any can also be chosen if the workflow should also follow this path in either case.


Validate and save the workflow in the database or on the desktop and upload it using WFLOAD.



Once the Workflow is saved in the database then validate using following scripts:


Related posts: