Oracle ERP World

Monday, June 8, 2015

Patch history tables in Oracle Apps

AD_APPL_TOPS - Stores information about all APPL_TOPS utilizing this instance.
AD_APPLIED_PATCHES - Stores information about all distinct updates applied.
AD_BUGS - Stores information about all distinct bug fixes applied.
AD_DRIVERS - Stores information about the drivers that comprise an update.
AD_FILE_VERSIONS - Stores version information for files.
AD_FILES - Stores information about files in the system.
AD_PATCH_COMMON_ACTIONS - Stores information about actions in update driver files.
AD_PATCH_DRIVER_LANGS - Stores information about the languages included in an update driver file.
AD_PATCH_DRIVER_MINIPKS - Stores information about minipacks.
AD_PATCH_RUN_BUG_ACTIONS - Stores detailed information about actions applied by an update.
AD_PATCH_RUN_BUGS - Stores information about all bug fixes included in an update , applied or not.
AD_PATCH_RUNS - Stores information about all incvocations of AutoPatch for all of the various updates applied.
AD_RELEASES - Stores release information about the Oracle Applications system.

References:
http://appsoracle.blogspot.ca/2012/03/patch-history-tables-in-oracle-apps.html

Tuesday, June 2, 2015

Oracle Application URL from the Apps database

SELECT profile_option_value
FROM apps.fnd_profile_option_values
WHERE profile_option_id =
(SELECT profile_option_id
FROM apps.fnd_profile_options
WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
AND level_value = 0

Friday, May 29, 2015

OAF Basic Part 1

Index
What is OA Framework
Why OA Framework
OAF Vs FORMS
OA Framework Architecture
MVC Architecture and its Components
Profile Options
JDeveloper Structure
Naming Standards
What is OA Framework?
Oracle Application Framework(OA Framework) is a proprietary framework developed by Oracle Corporation for application development within the Oracle E-Business Suite.

Available to customers for personalization, customizations and custom-application development.

The OA Framework is a Model-view-controller (MVC) framework built using J2EE (Java 2 Platform, Enterprise Edition)technologies.
Why OA Framework?

Advantages :
Enterprise-Grade Performance and Scalability
Improved End User Productivity
Highly extensible architecture
Browser Look and Feel (BLAF terminology) for all applications
Open industry Standards such as XML, HTML, Java, JSP,
SQL and Web Services.

Using Oracle Forms	OA Framework
With Oracle Forms I can create custom Apps modules and modify seeded Apps Forms.	JDeveloper is the development tool for creating custom Apps web pages and modifying seeded Apps web pages.
I use SQL and PL/SQL in Oracle Forms	We will still use SQL statements, specifically in View Objects. We can execute PL/SQL, but within Java code. If your Java skills are nil or weak, now might be a good time to plan a training program. See What To Do Next for more information.
An Oracle Forms file (.fmb) contains screen design and custom procedures and functions.	Reusable code, that is the mantra for JDeveloper and OA Framework. In Oracle Forms a data block references a table or view, and that definition is contained within the .fmb. With JDeveloper, we create view objects or entity objects that can be reused by other web pages.
In Forms, I can reuse forms, libraries, and menus by referencing.	OA Framework is an assembly of reusable objects.
I use TEMPLATE.fmb as the starting point for any custom Apps forms. TEMPLATE.fmb contains references to libraries, property settings, and menus that will help make custom forms look-and-feel like Oracle-provided forms.	OA Framework is a development and deployment platform presented through JDeveloper. The seeded objects and settings give custom web pages the same look-and-feel as Oracle-provided web pages
In Forms I create and use canvases: Forms requires a root canvas I use stacked canvases for controlling access to groups of items Fields and buttons appear on a canvas Properties control the appearance of canvases and items	In OA Framework we use the concept of a region: A web page requires a root region Regions can be nested Widgets (fields, buttons, images, etc) appear in a region Properties control the appearance of widgets

Components of MVC Architecture

Interaction between model, view and controller

Entity Object (EO)

Entity Object is based on database table or other data source.

Entity Object contains attributes which represent database columns.

All insert/update/delete (DML Operations) transactions go through EO to database.

View Object (VO)

Two types
     1. SQL based
     2. EO based
Represents a query result.
Is used for joining, filtering, projecting, and sorting your business
     data.
Can be based on any number of entity objects (EOs) and provides
     access to EOs.

Application Module (AM)

Every page must have a root application module.

Handles transactions.

Application Modules serve as containers for related BC4J Objects.

The objects are related by participating in the same task (for example, a multi-page UI for creating purchase orders)

Application Modules provide transaction context

Application Modules establish database connections

Application Modules may be nested to provide more complex application modules

Controller (CO) in MVC Architecture

Responds to user actions and directs application flow.
Model objects like EO and VO can't be accessed directly from the Controller Class, except AM.
Contains methods such as
           1. ProcessRequest: Fires when OAF page loads for the first time.
           2. ProcessFormRequest: Fires when user submits the page.
                                           (Ex: clicking GO button)

MVC Architecture

Model-View-Controller (MVC) Design Pattern

Profile Options

To Enable the “about this page” link on the page set the below profile option as “yes”
FND: Diagnostics : Yes
To Enable the “personalize page” link on the page set the below profile options as “yes”
Personalize Self-Service Defn : Yes
FND: Personalization Region Link Enabled : Yes

Jdeveloper Structure

Naming Standards

Page Name Ends with PG/PAGE
Region name ends with RN
Entity Object Name ends with EO
View Object ends with VO
Application Module Name Ends with AM
Controller Name ends with CO

References:

http://oafadfmahi.com/oaf/oafbasic1.html

Thursday, March 12, 2015

Data Quality Management(DQM) in TCA

In this article I will touch base upon the data quality management feature of TCA. This article also contains a link to a DQM presentation that will compliment the Q & A in this article. A combination of this article and thepowerpoint presentation should help you understand the basics of DQM [ Data Quality Management ] in Oracle TCA.
Is DQM a part of TCA?
Yes it is, the setup can be done using responsibility named "Trading Community Manager".

What are the setup steps for DQM?
Note: These steps will get clearer once you finish reading this article + PowerPoint Presentation
Here are the steps :-
1. Identify the Attributes that you wish to use for Search and Scoring.
2. Define custom attributes for those columns on which you would like to either search or score, but those columns are not in TCA tables.
3. Assign transformation to each column. Define custom transformation rules if need be, by using PL/SQL
4. Decide which transformed values are used for Searching and which transformed values are to be used for [both] Searching and/or just Scoring
The attribute transformations that you wish to search upon, will have "Acquisition" checkbox set to Yes.
5. Define your match rules. You can define as many match rules
6. In Match rules, you specify how the users search criteria[values being used by users] are transformed prior to acquisition.
7. Define scoring/ranking rules
8. Run the staging process.

Give me some examples where DQM is used?
Lets say your organization has 50000 employees and a person named john smith gets recruited for a job. HRMS team must know whether this John Smith has had any kind of interaction with the organization in past. Ideally if John Smith has had any kind of interaction with the company in past, then there must exist an entry for this record in TCA. In this case, HR must ensure that HRMS record can be made to point to existing TCA record. By doing so a 360 degree view of the employee be maintained in your organization.

So DQM is about being able to identify a duplicate entry in your system.
Correct, and this I made possible by applying some transformations as you will see in the presentation.

Is DQM only applicable at party level in TCA?
No way, DQM can be made to work at party address level & contact level too.

Isn't finding duplicates as simple as matching the social security number or national insurance number?
Correct, but say if your payables department wants to pay expenses to a third party for their interview travel expenses, then payables team will not have access to SSN or "NI Number". In this case, they rely upon other attributes of the person to find duplicates.

Ah, it means DQM will not be used merely by HRMS department or the Receivables department?
Certainly not, in an efficient organization, any department that registers a party into oracle apps must have access to DQM functionality.

But what if Payables team wants to use DQM, but with different set of search and ranking criteria altogether?
This is a very common requirement. Hence in this case Oracle lets you define multiple "Match Rules" in DQM.

Fine, we can define different Match Rules, but how will we enforce Payables users to use say "Match Rule 1" and HRMS users to use say "Match Rule 2"?
You can differentiate such requirements by creating different DQM search responsibilities. For each such responsibility you will assign profile option "DQM Match Rule for Search" to respective value.
In case you do not wish to have different responsibilities, though recommended, you can set this profile option at user level too.

But DQM searches for duplication in TCA tables, however "NI Number" and SSN [Social Security] are stored in HRMS tables. How will DQM search data in other modules?
You can define custom attributes in DQM. Custom attribute is nothing but a pseudo column which uses pl/sql to return the value,in this case from per_people_f

Does DQM search data within TCA tables?
Well, after defining rules for DQM setup, you run a concurrent program named "DQM Staging Program". This process de-normalises TCA data into staging tables. Transformed values for columns are stored in staging table. This will be clear when you go through the PowerPoint presentation.

What are the staging tables?
There are a list staging tables where DQM Transformed data is stored. Each transformed value resides in a different column. The transformed values used for Acquisition are indexed by Oracle.
The list of tables are
HZ_STAGED_CONTACTS
HZ_STAGED_CONTACT_POINTS
HZ_STAGED_PARTIES
HZ_STAGED_PARTY_SITES

What is event "oracle.apps.ar.hz.DQM.realtimesync" which keeps on firing when employee records or the TCA records are modified?
As you know, the TCA data is transformed and de-normalized into staging tables. Any changes to the TCA data must update the DQM staging tables, so that changes can be searched upon in DQM. The subscription of this event reads the pending entries in staging interface table HZ_DQM_SYNC_INTERFACE and transfers them to DQM staging tables after having applied the transformations.

NOTE: Although the event name is realtimesync, Oracle has disabled the realtime option for this event subscription. Hence synchronization with DQM staging tables happens in deferred mode only.

Here is the link to DQM presentation
Link for DQM and TCA presentation

References:
http://oracle.anilpassi.com/dqm-tca.html

Thursday, March 5, 2015

BI Publisher Bursting: Data Definition sample XML file

<?xml version="1.0"?>
<dataTemplate name="XXCOMPSOL" description="Concurrent Program Name" version="1.0" defaultPackage="XXCOMP_CUSTOMER_TOOL_PKG" >

<properties>
<property name="xml_tag_case" value="upper"/>
<property name="db_fetch_size"/>
<property name="scalable_mode"/>
<property name="include_rowsettag"/>
<property name="debug_mode"/>
</properties>

<parameters>
<parameter name="P_ORDER_HEADER_ID" dataType="number" />
<parameter name="P_SEND_EMAIL" dataType="character" />
<parameter name="P_EMAIL_BCC_01" dataType="character" />
<parameter name="P_CTL_TBL_RETAIN_DAYS" dataType="number" />
</parameters>

<dataTrigger name="beforeReport" source="XXCOMP_CUSTOMER_TOOL_PKG.BEFORE_REPORT"/>
<dataTrigger name="afterReport" source="XXCOMP_CUSTOMER_TOOL_PKG.AFTER_REPORT"/>

<dataQuery>
<sqlStatement name="Q_MAIN">
<![CDATA[ select *
from XXCOMP_CUSTOMER_TOOL_STG
where CREATED_BY_REQ_ID = :LP_REQUEST_ID
]]>
</sqlStatement>
</dataQuery>

<dataStructure>
<group name="Q_MAIN_HDR" source="Q_MAIN" groupFilter="XXCOMP_CUSTOMER_TOOL_PKG.FLAG_AS_NOTIFIED(:HEADER_ID)">
<element name="HEADER_ID" value="HEADER_ID" />
<element name="SHIP_TO_CONT_EMAIL" value="SHIP_TO_CONT_EMAIL" />
<element name="EMAIL_TO" value="EMAIL_TO" />
<element name="EMAIL_SUBJ" value="EMAIL_SUBJ" />
<element name="EMAIL_BCC" value="EMAIL_BCC" />
<element name="IMG_URL_PORT" value="IMG_URL_PORT" />
<element name="EMAIL_BODY_1" value="EMAIL_BODY_1" />
<element name="EMAIL_BODY_2" value="EMAIL_BODY_2" />
<element name="EMAIL_BODY_3" value="EMAIL_BODY_3" />
<element name="EMAIL_BODY_4" value="EMAIL_BODY_4" />
<group name="Q_MAIN" source="Q_MAIN">
<element name="SOLD_TO_CUST" value="SOLD_TO_CUST" />
<element name="END_CUST" value="END_CUST" />
<element name="CUST_PO_NUMBER" value="CUST_PO_NUMBER" />
<element name="ORDER_NUMBER" value="ORDER_NUMBER" />
<element name="ORDERED_DATE" value="ORDERED_DATE" />
<element name="SEGMENT1" value="SEGMENT1" />
<element name="DESCRIPTION" value="DESCRIPTION" />
<element name="ORDERED_QUANTITY" value="ORDERED_QUANTITY" />
<element name="SOLUTION_NAME" value="SOLUTION_NAME" />
<element name="SOLUTION_TYPE" value="SOLUTION_TYPE" />
<element name="CONTRACT_DURATION" value="CONTRACT_DURATION" />
<element name="CONTRACT_DURATION_WORDS" value="CONTRACT_DURATION_WORDS" />
<element name="CONTRACT_DURATION_UOM" value="CONTRACT_DURATION_UOM" />
<element name="SERVICE_START_DATE" value="SERVICE_START_DATE" />
<element name="SERVICE_END_DATE" value="SERVICE_END_DATE" />
</group>
</group>
</dataStructure>

</dataTemplate>

Tuesday, February 10, 2015

Sales Order Line Status Flow

OM = Order Management Sales Order Form

SE = Shipping Transactions or Shipping Execution Form

Entered [OM]: Order is saved but not booked

Booked [OM]: Order is Booked.

Awaiting Shipping [OM]: Order is booked but lines are not yet picked.

Navigating to Shipping Execution, the delivery line status flow is:Not Ready to Release [SE]: A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used.

Released to Warehouse [SE]: Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.
Ready to Release [SE]: Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.
Backordered [SE]: The status of Backorderd is assigned to a line under the following circumstances.● The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
● At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
● The user manually Backorders the entire delivery.
Shipped [SE]: The delivery line is shipped confirmed.
Confirmed [SE]: The delivery line is shipped or backordered and the trip stops are open.
Picked [OM]: Pick release is complete, both allocations and pick confirm
Picked Partial [OM]: This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred
Interfaced [SE]: The delivery line is shipped and Inventory interface concurrent process is complete.
Awaiting Fulfillment [OM]: When fulfillment set is used, Not all shippable lines in a fulfillment set or a configuration are fulfilled
Fulfilled [OM]: All lines in a fulfillment set are fulfilled.
Interfaced to Receivables [OM]: The order is linked with Receivables and the invoice is created.
Partially Interfaced to Receivables [OM]: This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.
Closed [OM]: Closed indicates that the line is closed.

Canceled [OM]: Indicates that the line has been completely canceled. No further processing will occur for this line.

WSH_DELIVERY_DETAILS.Release_Status can have any of the below valid values

Execute the below query to see lookup values

SELECT lookup_type,
  lookup_code,
  meaning,
  description
FROM fnd_lookup_values
WHERE lookup_type = 'PICK_STATUS'
AND LANGUAGE      = 'US';

LOOKUP_TYPE	LOOKUP_CODE	MEANING	DESCRIPTION
PICK_STATUS	B	Backordered	Line failed to be allocated in Inventory
PICK_STATUS	C	Shipped	Line has been shipped
PICK_STATUS	D	Cancelled	Line is Cancelled
PICK_STATUS	E	Replenishment Requested	Line has been replenishment requested
PICK_STATUS	F	Replenishment Completed	Line has been replenishment completed
PICK_STATUS	I	Interfaced	Line has been shipped and interfaced to Order Management and Inventory
PICK_STATUS	K	Planned for Crossdocking	Line has been Planned for X-dock
PICK_STATUS	L	Closed	Line has been Received
PICK_STATUS	N	Not Ready to Release	Line is not ready to be released
PICK_STATUS	P	Purged	Line has been purged from source system
PICK_STATUS	R	Ready to Release	Line is ready to be released
PICK_STATUS	S	Released to Warehouse	Line has been released to Inventory for processing
PICK_STATUS	X	Not Applicable	Line is not applicable for Pick Release
PICK_STATUS	Y	Staged/Pick Confirmed	Line has been picked and staged by Inventory

WSH_NEW_DELIVERIES.status_code column values

SELECT lookup_type,
  lookup_code,
  meaning,
  description
FROM fnd_lookup_values
WHERE lookup_type = 'TRIP_STATUS'
AND LANGUAGE      = 'US';

LOOKUP_TYPE	LOOKUP_CODE	MEANING	DESCRIPTION
TRIP_STATUS	CL	Closed	Trip has completed
TRIP_STATUS	IT	In-Transit	Trip is in-transit and has begun
TRIP_STATUS	OP	Open	Trip is Open and has not begun

References:

http://oracleappssharing.blogspot.ca/2013/11/sales-order-line-status-flow.html

Wednesday, January 21, 2015

OBIEE 11.1.1.6 new features/enhancements

Auto-Complete Functionality for Prompts: When enabled by the administrator, auto-complete suggests and highlights matching prompt values as the user types in the prompt selection field
Optional Apply and Reset Buttons for Prompts: You can show or hide a prompt's apply and reset buttons.
Miscellaneous Prompts Enhancements: The prompt Reset button now provides three reset options: Reset to last applied values, Reset to default values, and Clear All.
Favorites: You can now bookmark as favorites the catalog objects
Customizable Global Header and Get Started: The order of the links on the global header can now be customized, and links to external locations can be added to the global header. The Get Started... section of the home page can also be customized to meet the informational needs of the users
Oracle BI Client Installer Option Added to the Home Page: A link to the Oracle BI Client Installer has been added to the Download BI Desktop Tools option (just like it is for BI-Publisher Desktop)
Integrated Full-Text Search Capabilities: This method of searching allows the user to find objects by searching on their attributes such as author and column name
Ability to Set Accessibility Mode in the Sign In Page: Facilitates the use of a screen reader.
Enhancement to Oracle Scorecard and Strategy Management
Oracle Business Intelligence Mobile
Enhancements to Tables and Pivot Tables
Enhancements to Map Views: You can now include non-BI layers on a map view
Enhancements to Selections: The ability to include selected members in hierarchy selections steps that are based on family relationships, the addition of Siblings of and Leaves of options in family relationships.
Enhancements to Views: The ability to rename views and compound layouts
BI Composer Enhancements: BI Composer is now available in regular mode as well accessibility mode

References:
http://obieestuff.blogspot.com/2012/02/11116-new-features.html

Pages