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.

XMLBursting001

 

Connect to database:

XMLBursting002

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.

XMLBursting003

XMLBursting004

XMLBursting005

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

XMLBursting006

Save your report.

XMLBursting007

XMLBursting008

Compile the Report using Ctrl+Shift+K :

XMLBursting009

Move your report to custom top:

XMLBursting010

 

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

TCA-Customers:

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.

TCA-Suppliers:

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.

  • AP_SUPPLIERS
  1. HZ_PARTIES
  2. HZ_PERSON_PROFILES
  3. HZ_PARTY_USAGE_ASSIGNMENTS
  4. HZ_PARTY_RELATIONSHIPS
  • AP_SUPPLIER_SITES_ALL
  1. HZ_LOCATIONS
  2. HZ_LOCATION_PROFILE
  3. HZ_PARTY_SITES
  4. HZ_PARTY_SITE_USES
  • AP_SUPPLIER_CONTACTS
  1.  HZ_PARTIES
  2.   HZ_RELATIONSHIPS
  3.   HZ_PARTY_SITES
  4.   HZ_ORG_CONTACTS
  5.   HZ_CONTACT_POINTS

TCA ER-Diagram:

TCA1

 

reference: www.docs.oracle.com

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.

NEW_XAOA_NF028

NEW_XAOA_NF029

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:

NEW_XAOA_NF023

Open the Notification:

NEW_XAOA_NF024

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.

NEW_XAOA_NF025

NEW_XAOA_NF026

NEW_XAOA_NF027

 

Some of the workflow tables:

WF_LOOKUP_TYPES_TL
WF_MESSAGES
WF_MESSAGE_ATTRIBUTES
WF_NOTIFICATION_ATTRIBUTES
WF_ITEM_ATTRIBUTES
WF_ITEM_ACTIVITY_STATUSES

WF_ITEM_ATTRIBUTE_VALUES

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.

NEW_XAOA_NF019

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.

NEW_XAOA_NF020

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

NEW_XAOA_NF021

NEW_XAOA_NF022

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

Next

Related posts: