Online Service Contract Renewal Process in Oracle Service Contracts R12i

High level overview of Online Service Contract Renewal Process in Oracle Service Contracts (OKS)

Have Question ? Feel free to post your questions in comments section.

Seeded Mapping Between Oracle Receivable Transaction Flexfield and Service Contract

Seeded Mapping Between Oracle Receivable Transaction Flexfield  and Service Contract

Table Level Mapping for Oracle Advance Pricing (QP)

Table Level Mapping for Oracle Advance Pricing (QP) 

How to handle SHIP_FROM_ORG_ID in Oracle iStore

iStore does not expose warehouse (ship_from_org_id) field in the Customer UI pages such as the Shopping Cart nor the Checkout pages.  Therefore, iStore does not have any logic to populate this information to the underlying ASO schema (ASO_SHIPMENTS.ship_from_org_id).

Only the Quoting application exposes this field on the Quoting Forms UI.  The ASO schema (Order Capture) is shared by iStore and the Quoting modules.

In EBS we have profile option ASO: Default Ship From Org , as per R11i behavior iStore refer this profile option to drive value of a warehouse during normal pricing call or whenever item gets added to cart or during checkout

but in R12i  this behavior is completely changed and istore no longer  refer this profile to drive value of warehouse during pricing call or when ever  item gets added to cart or during checkout.But iStore still refer this profile option during ATP call.

iStore defaults warehouse (ship_from_org_id) on the Sales Order during cart to Order conversion using the OM Defaulting rule for the attribute "warehouse".   However, this defaulted value will not be saved to the iStore side.

If there is a business need to use the warehouse in custom pricing attribute sourcing for iStore, we can execute that by means of simple customization. We can consider below 2 opions...

1. Create a custom sourcing rule for the request type ASO to source the value from the profile option using  FND_PROFILE.value('ASO_SHIP_FROM_ORG_ID') in the PL/SQL sourcing rule 

2.Use Custom Hooks APIs to Populate aso_shipments.ship_from_org_id  when a new cart line is being added using  ASO user hook (ASO_QUOTE_CUHK package body).  Add the logic in create_quote_pre (first time when cart was created) or update_quote_pre.

Hope that helps , for any additional information fee free to write me back at sgupta@bizinsightinc.com

How To set the default Order Type for Orders Created During Oracle iStore Checkout

The purpose of this document is to describe how to default order type for orders created during the Oracle iStore checkout flow.

 

In R12, profile 'ASO: Default Order Type' is no longer used.   The Operating Unit specific profiles have been obsoleted and are replaced by the corresponding Operating Unit-specific parameters , and we need to setup these quoting parameter to default Order Type.

The following procedure will setup the Quoting parameters for each Operating Unit:

1. Switch to HRMS Manager responsibility

2. Navigation to HRMS Manager > Work Structures > Organization > Description

3. In the Find Organization dialog box, specify the Operating Unit to set the parameters in the     

            Name field

4. For Organization Classification Name, select Operating Unit

5. Click Find

6. From the Organization Classifications section, select Operating Unit.

○ Ensure that the Enabled checkbox is selected.

7. Click Others.

8. On the Additional Organization Information dialog box, select Quoting Parameters

9. Click inside the Quoting Parameters field to display the Quoting Parameters dialog box with the 

             following fields:

○ Operating Unit – You need to select same OU from LOV that you have selected in #3.

○ Default Order Type

○ Default Salesrep

○ Default Sales Group

○ Default Sales Role

○ Default Contract Template

10. Specify a value for each of the parameters using the respective LOVs (In case you want to  

               default values for all these parameters , but in case you are interested only in   

               Order Type parameter then just enter value for that only). The LOVs for the  

                   Default Order Type, Default  Salesrep, and Default Contract Template are filtered by the 

                   selected Operating Unit. The LOVs for the Default Sales Group and Default Sales Role are 

                   based on the Default Salesrep parameter.

11. Click OK to save changes

12. This procedure must be used to set the Quoting parameters for each Operating Unit being 

               used.

Oracle Application Data Model

Oracle Application Data Model


The following code objects are installed in the APPS schema:

1. Packages/Procedures/Functions 
2. Triggers 
3. Views 
4. Materialized views 
5. Java classes 
6. Queues 

The following objects are installed in the base product schemas: 

1. Tables 
2. Sequences 
3. Indexes 
4. Constraints 
5. Queues 

Query to get Invalid Object in Oracle database

select * from all_objects
where status ='INVALID'
--and object_name like 'Enter_object_name%'


select * from DBA_objects
where status ='INVALID'
--and object_name like 'Enter_object_name%'

Oracle Performance : TKPROF for formatting raw SQL Trace file

TKPROF is a program that formats a raw SQL Trace file into a user-friendly file. The program reads the Trace file and creates a file that has the following section:
• Header
• Body
• Summary.

The header section contains the version of TKPROF, any sort options used and a glossary of terms. The body section displays the following information for each user level SQL statement traced:
• SQL statement text
• Tabulated Parse
• Execute and Fetch statistics
• Number of library cache misses during Parse
• Parsing user id

If specified, TKPROF also:
• Shows the explain plans when the SQL Trace was executed and when TKPROF was run
• Creates a SQL script that creates a table and inserts a row of statistics for each SQL statement

The power of TKPROF is the ability to sort the SQL statements. The sorting helps identify and sequence statements that are using the most resources. At the end of the report, a tabular summary for all the user level and recursive SQL statements is provided.

Formatting a Trace File using TKPROF
You execute TKPROF from the command line. Although TKPROF has many arguments, generally only two mandatory and three optional arguments are used. The execution syntax and arguments are as follows:

tkprof infile outfile sort=options explain=username/password@db print=integer

The tkprof arguments are:

Sample Header , Body and Summary generated by TKPROF from a raw SQL Trace file.

Oracle Performance : Sql Trace and how to enable Sql Trace

Sql Trace is facility/utility  when enabled, it capture performance information for executed SQL
statements and write out to a trace file until the SQL trace facility is disabled.

Command to Enable Sql Trace
dbms_session.set_sql_trace,
dbms_system.set_sql_trace_in_session ( usually used to enable trace in a specific session)

Example - 
Begin
dbms_system.set_sql_trace_in_session(SID,Serial #, SQL_TRACE)
End;

SQL_TRACE is Boolean parameter and has value TRUE/FALSE , and value for Parameter #1 and #2 can be retrieve by executing below query
SELECT  s.sid,
s.serial#
FROM           v$session s
WHERE          s.osuser = '&ENTER_OS_USER_NAME'
AND            s.username = '&ENTER_ORACLE_DB_USER_NAME'

we can enable the trace for current session by executing below command

SQL> ALTER SESSION SET SQL_TRACE=TRUE;
SQL> ALTER SESSION SET SQL_TRACE=FALSE;
Or
SQL> EXECUTE dbms_session.set_sql_trace(TRUE);

SQL> Execute SQL statements
SQL> EXECUTE dbms_session.set_sql_trace(FALSE);
Sql Trace always generate raw trace file and it is not very user friendly especially if you are first timers , we can use TKPROF utility to formats a raw SQL Trace file into a user-friendly file. TKPROF reads the Trace file and creates a file that has the following section:

• Header
• Body
• Summary.

Oracle Performance : How to use Index hints in sql Query

Some key pointers we need to consider while defining index hints in sql query to improve performance
Syntax for an index hint is:

select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
 There are a number of rules that need to be applied to this hint:

1.The TABLE_NAME is mandatory in the hint

2.The table alias MUST be used if the table is aliased in the query

3.If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.

4.The INDEX_NAME is optional.
If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applie
If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.

5.If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelled correctly then the     hint will not be applied even though the TABLE_NAME is correct.

6.If there are multiple index hints to be applied, then the simplest way of addressing this is to         repeat the index hint syntax for each index e.g.:

 select /*+ index(TABLE_NAME1 INDEX_NAME1) index(TABLE_NAME2 INDEX_NAME2) */ col1...

7.Remember that the parser/optimizer may have transformed/rewritten the query  or may have chosen an access path which make the use of the index invalid and   this may result in the index not being used.

Please note that as long as the index() hint structure is correct it will force the use of the Cost Based Optimizer (CBO). This will happen even if the alias or table name is incorrect.

Oracle OPM : Query to Connect Resources , Operations , Routing and Recipes

Oracle OPM : Query to Connect Resources , Operations , Routing and Recipes

SELECT grb.recipe_no,
grb.recipe_version,
gor.resources,
mp.organization_code,
grb.recipe_id,
grb.routing_id,
grb.formula_id
grvr.recipe_validity_rule_id
FROM gmd_operation_resources gor,
gmd_operation_activities goa,
gmd_operations_b gob,
gmd_recipes_b grb,
fm_rout_dtl frd,
gmd_recipe_validity_rules grvr,
mtl_parameters mp,
gmd_recipe_orgn_resources gror
WHERE gor.oprn_line_id = goa.oprn_line_id
AND goa.oprn_id = gob.oprn_id
AND gob.oprn_id = frd.oprn_id
AND frd.routing_id = grb.routing_id
AND grb.recipe_id = grvr.recipe_id
AND grb.delete_mark = 0
AND grvr.delete_mark = 0
AND grvr.recipe_use IN('0', '1')
AND grvr.validity_rule_status IN('100','700', '900')
AND nvl(grvr.end_date, (sysdate + 1)) > sysdate
AND grvr.organization_id = mp.organization_id (+)
AND gor.resources = gror.resources

Oracle OPM : Query to Retrieve Recipe and Recipe Validation Rule

Query to Retrive Recipe and Recipe Validation Rule


select recipe_no, recipe_version,grvr.orgn_code, grvr.item_id,grvr.min_qty, grvr.max_qty,grvr.std_qty,
grvr.recipe_validity_rule_id, grb.routing_id,
grb.formula_id, grb.recipe_id, grvr.organization_id,grb.owner_id,grb.owner_lab_type,grb.owner_organization_id
from gmd_recipes_b grb, gmd_recipe_validity_rules grvr
where grb.recipe_id = grvr.recipe_id
and grb.delete_mark = 0
and grvr.delete_mark = 0
AND grvr.recipe_use IN ('0', '1')
AND grvr.validity_rule_status IN ('100','700', '900')
AND nvl (grvr.end_date, (sysdate + 1)) > sysdate

undefined error in Oracle apps after Java upgrade

This short post is about how to fix the undefined issues in Oracle Application . Many a time when we install the Java for Oracle EBS , name of all of the responsibilities change to undefined.

As shown below to fix this issue just click on Compatibility View . System will prompt you to login again and once you login  you can see correct responsibility Name.

your security settings have blocked a self signed application from running ( window 8 , Java 7 Update 51)

This is Short post about how to fix error.
----------
your security settings have blocked a self signed application from running
----------

there could be many reason for this error , but I have encountered this error while trying to open Oracle EBS 12.1.3 after I have upgraded my Java 
FROM  Version 7 Update 40 
To  Version 7 Update 51

Well ,  this is a genuine message as Oracle has enhance security feature in Version 7 update 51 . It is highly recommended that we should not run the applications that raised this type of messages , but in my case I am trying to my EBS application which is a safe application. Options that I have - 

1.Contact DBA
2,Use below workaround , and I like option #2 as this is quick one ( but at the same time run these applications only if you understand the risks and implications).