SOME OF THE MAIN PRICING API’s AVAILABLE

Business Object for Pricing Formulas Application Program Interface

· QP_Price_formula_PUB.Get_Price_Formula
The Formula Calculation package retrieves the price formula header and lines for a given formula.
· QP_Price_formula_PUB.Process_Price_Formula
Performs the insert, update, and delete of price formula header and price formula lines.
· QP_Price_formula_PUB.Lock_Price_FormulaLocks price formula header and price formula lines records prior to updates

Get Custom Price Used in Formulas Setup) Application Program Interface

· QP_CUSTOM.Get_Custom_Price
You may add custom code to this customizable function. The pricing engine while evaluating a formula that contains a formula line (step) of type “function” calls this API.

Request Application Program Interface

· QP_PREQ_GRP.Price_Request
The Price Request Application Program Interface (API) is a public API that allows you to get a base price and to apply price adjustments, other benefits, and charges to a transaction.

STEPS FOR PRICING AN ORDER

1. Create a price list (one time)
2. creation of price list headers and lines(associates items to a particular price list)
3. Create pricing formulas (optional)
4. Create price list modifiers/qualifiers (optional)
5. Create an order.
6. Attach the price list to that order (we can associate price list to order type and check the box (enforce price list)).

HOW ORACLE PRICING WORKS

Pricing Engine takes the pricing request from the calling application, selects the applicable price lists and modifier lists and determines the base list price. It then calculates the final selling price by applying the benefits and surcharges from the modifier lists to the base list price

Process flow, Events and the various associated Pricing Phases

How to Setup Blanket Agreement Transaction Type

How to Setup Blanket Agreement Transaction Type

Blanket Sales Agreements are used when we have specific characteristics related to a purchasing agreement between a customer and a supplier. These characteristics include the date range of the agreement, the items included, the price of the items, the quantity of each item that the parties committed to, as well as other attributes, like freight or payment terms. Once a Blanket Sales Agreement is entered for a customer, multiple releases (sales orders) against the Blanket Sales Agreement are processed over a period of time within Order Management.

Documnet

How to Install STATSPACK

To install STATSPACK follow the steps below:

1. Create PERFSTAT Tablespace by using the below command:
I. Logon to SQLPLUS by Sys User

II. Pass the following command

III. SQL> CREATE TABLESPACE statspack DATAFILE 'c: \oracle\datafile\statspack.dbf' SIZE 400M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT AUTO PERMANENT ONLINE;

2. Run catdbsyn.sql and dbmspool.sql as SYS from SQLPLUS

I. $ sqlplus "/ as sysdba"
II. SQL> @?/rdbms/admin/catdbsyn.sql
III. SQL> @?/rdbms/admin/dbmspool.sql

3. Run the create script
I. $ sqlplus "/ as sysdba"
II. SQL> @?/rdbms/admin/spcreate


You can now start using Oracle STATSPACK.

How to Restrict Access of Some Users or Program in Oracle Prodcution Database

CREATE OR REPLACE TRIGGER programe_restrication

AFTER LOGON ON DATABASE

BEGIN

FOR x IN (SELECT username, program

FROM SYS.v_$session

WHERE audsid = USERENV ('sessionid'))

LOOP

IF LTRIM (RTRIM (x.username)) = 'AHMADBILAL'

AND LTRIM (RTRIM (x.program)) IN ('sqlplusw.exe', 'TOAD.exe')

THEN

raise_application_error

(-20999,

'Not authorized to use in the Production environment!'

);

END IF;

END LOOP;

END programe_restrication;

/

Check Work Flow Activity In Error

I have used This Query to check Error in Purchase Order Work flow(Mean How Much PO stuck in work flow)

SELECT DISTINCT wi.item_type item_type, wi.item_key item_key,

wpa.process_name

|| ':'

|| wpa.instance_label error_process_activity_label,

wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_TYPE'

) errant_item_type,

wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_KEY'

) errant_item_key

FROM wf_items wi,

wf_item_activity_statuses wias,

wf_item_activity_statuses wias1,

wf_process_activities wpa

WHERE wi.item_type = 'WFERROR'

AND wi.end_date IS NULL

AND TO_NUMBER (wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ACTIVITY_ID'

)

) = wias.process_activity

AND wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_TYPE'

) = wias.item_type(+)

AND wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_KEY'

) = wias.item_key(+)

AND wias.activity_status(+) <> 'ERROR'

AND wias1.item_type = wi.item_type

AND wias1.item_key = wi.item_key

AND wias1.end_date IS NULL

AND wias1.notification_id IS NOT NULL

AND wias1.process_activity = wpa.instance_id;

Check Profile and Value with level

SELECT (SELECT user_profile_option_name

FROM fnd_profile_options_tl fpot

WHERE TRIM (fpot.profile_option_name) =

TRIM (fpo.profile_option_name))

PROFILE,

fpov.profile_option_value VALUE,

DECODE (fpov.level_id,

10001, 'SITE',

10002, 'APPLICATION',

10003, 'RESPONSIBILITY',

10004, 'USER'

) "Apply On",

fa.application_name application,

fr.responsibility_name responsibility, fu.user_name "USER"

FROM fnd_profile_option_values fpov,

fnd_profile_options fpo,

fnd_application_tl fa,

fnd_responsibility_vl fr,

fnd_user fu,

fnd_logins fl

WHERE fpo.profile_option_id = fpov.profile_option_id

AND fa.application_id(+) = fpov.level_value

AND fr.application_id(+) = fpov.level_value_application_id

AND fr.responsibility_id(+) = fpov.level_value

AND fu.user_id(+) = fpov.level_value

AND fl.login_id(+) = fpov.last_update_login

ORDER BY 1, 3

Oracle’s Application Implementation Methodology

Oracle’s Application Implementation Methodology is their methodology for the implementation of its e-Business Suite (ebs). According to Oracle: -

AIM Advantage is a time-tested implementation approach and toolkit for planning, executing and controlling the implementation of your Oracle E-Business Suite. It is the only implementation method specifically built for Oracle Applications, and has been used in thousands of successful implementations by Oracle Consulting, Oracle’s select implementation partners, and customers.

I’ve used this methodology on all of the Oracle Applications implementations I have been involved in and have found it to be a very useful tool. The methodology helps you plan and document an implementation at all stages of the lifecycle. Information on the pricing can be found by performing a search for "AIM" at the Oracle Store. Here you will find two options: -

1. AIM advantage without Supplement Option - Packaged Method Named User (US$2,200)
2. AIM Advantage with Supplement Option - Packaged Method Named User (US$2,530)

Both of these options provide you with the AIM CD-ROM and documentation whilst according to the Store, with the supplement option:

Oracle will provide access to AIM Advantage 3.0 supplements made generally available to commercial customers for a period of twelve months from the effective date of purchase. Supplements may include new deliverable templates and point releases of AIM Advantage (e.g. Version 3.0 to version 3.1), but will not include new major releases (e.g. Version 3.0 to version 4.0); major releases are licensed separately.

As the pricing is based on a named user, an organizations implementation cost would be directly related to the number of users that would make use of the software. To continue receiving annual updates via the supplementary option the cost would be 15% of the current list price of AIM.

Source

Import of an Excel or any other file (External data) into Oracle

For this purpose we can use the following three methods

1. Oracle Application Express

One way which is very easy is to install Oracle Application Express (Formerly HTML DB). Within Application Express we can actually just copy and paste our Excel rows and it will import them to a table. Other then that Application Express is a very useful tool for other things too.

2. SQL Loader SQLLDR

One solution is to use the sqlloader to load any external data into the Oracle database. The problem with this is that we need to run the sqlloader (sqlldr) script every time we need an update.

3. External Table

The more elegant and faster method is to use external tables.

Now, with an external table we can initialize our table once and never have to worry about it anymore. Plus we can use the external table just like any other table in database and issue SQL commands to join the table (may be processing is bit slowly).

Steps required

a. Export your excel sheet to a tab. Delimited format, we call our file "data_2_import.txt".

b. If you don't have a directory alias set up within Oracle then create one now. The directory allows Oracle to read files from this directory on your hard drive.

You create a directory with the following commands:

Create or replace directory importdir as 'C:\data_to_import';

c. Now we only need to create the external table.

CREATE TABLE EXT_MEMBERS

(

ID VARCHAR2(20 CHAR),

NAME VARCHAR2(100 CHAR)

)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY importdir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY X'9' (

ID char(20),

Name CHAR(100)

)

)

LOCATION (importdir:'data_2_import.txt')

)

REJECT LIMIT 0

PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT)

NOMONITORING;

As we can see in the code above, we are creating a table with the filenames "id" and "name" which represents the order from our Excel file. We also tell the external table that every record is on a newline and that the fields are separated with tabs (X'9') 

In case we are using Comma delimited we use fields terminated by ',' in case we are using fixed field length then we use fields (

       field_1 position(1: 4) char( 4),

       field_2 position(5:30) char(30)

    )

Now when we issue a select command on the external table Oracle will read in our data_2_import.txt file. Whenever there is an update of our data_2_import files we only need to replace the file with same formatting on the hard drive and the table is automatically updated within the database.

Note: If external tables are created with NOLOG then granting READ on the DIRECTORY object is sufficient. If an external table is created without the NOLOG syntax then both READ and WRITE must be granted to SELECT from it.

External tables are READ ONLY. Insert, update, and delete can not be performed

How to recover Control file in oracle

/* I have already backup control file to trace */

SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace;

/* modify and run your trace file and your control file is up to date */

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE OPEN;

Up the database

Order Management Integration With Oracle Inventory Module

Order Management integrates with Oracle Inventory Management in the following areas:

Managing reservations

Order Management calls Inventory’s reservation APIs to manage reservations. You can create reservations to on-hand quantities from the Sales Orders form. You can also go to Inventory reservation form the Sales Orders form and create a reservation to any level of inventory

(Sub-inventory, locator, lot).When supply is created for an ATO configuration (for a configuration item); it is reserved to a Work Order. This reservation gets transferred to on-hand

When the work order is completed, thus reserves the Order line to on-hand. For every Order created, Order Management creates a record in MTL_SALES_ORDERS, an entity that Inventory uses to manage demand from various sources. Reservation information is stored in MTL_RESERVATIONS. Reservation records for order Lines point to both MTL_SALES_ORDERS and OE_ORDER_LINES_ALL.

Customer-Item Cross reference

You can use Oracle Inventory to set-up Customer Items and Customer Item Cross reference information. You can then place orders using those pre-defined customer item identifiers. Order Management calls the Inventory API INV_CUSTOMER_ITEM_GRP to derive the internal item based on the specified customer item, ship-to site and the warehouse on the Order Line.

The Item Identifier Type on the Order Line indicates the Cross-reference Type that was used for placing the order. The Ordered Item tracks identifier that was used to place the order.

Change the font and font size in SQLPLUS

You can change the font in SQL*Plus for Windows NT/2000.

In regedit, go to
HKEY_LOCAL_MACHINE
-> SOFTWARE
-> ORACLE
-> HOME0

Create a new registry value called SQLPLUS_FONT of type REG_EXPAND_SZ and set it to your favourite fixed-width font, Eg. Courier New
Create a new registry value called SQLPLUS_FONT_SIZE of type REG_EXPAND_SZ and set it to the size you want (13 is a good size).

How to change Message Displayed on invalid login Information

We are going to change the message displayed in e business suite on invalid login information

Login by Sys Admin Login and Navigate to Application Developer Responsibility

Menu Navigation

Application ----> Messages

Query for “FND_APPL_LOGIN_FAILED” Change the Message Display in Current Message Text

After Saving Record Reboot application tear services.

Formated Query For Auto Month Addition of Given period with all tabs etc for Data Loader

By Using this query you can generate your sequence information in data Loader Format

SELECT SUBSTR (sa.method_code, 1, 1) ty, fap.application_name, '\{TAB}' tb1,

dsc.NAME, '\{TAB}' tb2, sob.NAME, '\{TAB}' tb3,

DECODE (SUBSTR (sa.method_code, 1, 1), 'A', '\{LEFT}') lf1,

'\{LEFT}' lf2, '\{TAB}' tb4,

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'DD-MON-YYYY') start_dt,

'\{TAB}' tb5,

TO_CHAR (ADD_MONTHS (sa.end_date, 1), 'DD-MON-YYYY') date_ed,

'\{TAB}' tb6,

REPLACE (REPLACE (UPPER (ds.NAME),

TO_CHAR (sa.start_date, 'MON'),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'MON')

),

TO_CHAR (sa.start_date, 'YY'),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'YY')

) seq_na,

'\{TAB}' tb7

FROM fnd_doc_sequence_assignments sa,

fnd_application_vl fap,

gl_sets_of_books sob,

fnd_document_sequences ds,

fnd_doc_sequence_categories dsc

WHERE (sa.start_date >= TO_DATE ('01-01-2007', 'DD-MM-YYYY'))

AND (sa.end_date <= TO_DATE ('31-01-2007', 'DD-MM-YYYY'))

AND sa.application_id = fap.application_id

AND sa.set_of_books_id = sob.set_of_books_id

AND sa.doc_sequence_id = ds.doc_sequence_id

AND sa.category_code = dsc.code

ORDER BY sob.NAME, sa.category_code, sa.method_code, sa.application_id

Just need to Input Start_date and end_date in my case this is '01-01-2007 and 31-01-2007'

Oracle Identity Management and Oracle AS Single Sign-On

Oracle Application Server provides a security framework that incorporates the different key components here I discuss oracle identity management

Oracle Identity Management supports a variety of complex password policies. These fall into two categories:

• Value-based policies (including minimum lengths and the presence of a minimum number of special characters)
• State-based policies (e.g., expiration and maximum number of retries)

Many users face a proliferation of passwords as they gain access to more applications and systems. Because it is so easy for users to forget passwords when they have so many to remember, users may end up writing them down in public places, thus creating a security risk. Oracle Identity Management can help lift this burden on users by enabling deployment of single sign-on, allowing a single user and password combination across these applications and systems.

Follow these steps to set up a basic single sign-on system:

1. Install the identity management infrastructure database, database server, and single sign-on servers using the Oracle Universal Installer.
2. Configure the HTTP servers in the single sign-on middle tier.
3. Configure the HTTP hardware load balancer or Oracle AS Web Cache.
4. Configure the identity management infrastructure database single sign-on server to accept authentication requests from an externally published address of the Oracle AS Single Sign-On server.
5. Re Register the mod_osso (Oracle AS Single Sign-On extension) to the Oracle AS Single Sign-On middle tier.

How to Implement Function Security for Orders and Returns in Order Management 11.5.10.2

Use function security to control user access to functions in the Order Organizer and Sales Orders window. Your system administrator customizes a responsibility at your site by including or excluding functions and menus in the Responsibilities window.

The form functions listed below are available by default, but may be excluded from menus tied to restricted responsibilities:

• Sales Orders

• Order Organizer

• Order Organizer View

Note: The functions Returns: Enter and Returns: View are available for backward compatibility with older releases of Oracle Order Entry. Do not use these for new installations. If you exclude Sales Orders, Order Organizer, and Order Organizer View from a responsibility, that responsibility’s users can neither access the Sales Orders window by selecting from the Navigator menu nor query orders from the Orders Organizer. If you exclude all three functions, you should also remove the Orders, Returns menu item from the Navigator.

• Sales Orders and Order Organizer gives you the right to view, enter or modify orders and returns.

• Order Organizer View only enables you to view the orders in the Sales Orders window from the Order Organizer.

Additionally, utilizing standard form functional security, you can restrict actions that a user can perform in the Sales Order window by adding or removing these functions from the menu associated with a responsibility. Below lists the following actions that can be restricted using this feature.

Apply Holds

Authorize Payment

Book Order

Calculate Tax

Cancel Orders

Charges

Configurations (Link, Delink, Match & Reserve)

Copy Orders

Gross Margin

Mass Change

Send Notifications

Price Orders

Progress Order

Release Holds

Sales Credits

Schedule Orders

Function Security Example

Your company employs some individuals whose tasks include viewing orders and returns. They do not enter orders or returns.

Before Applying Any Rule for user Ahmad Bilal

Now we will process function security rule for User Ahmadbilal

1. Navigate to the Responsibilities window. Query an existing responsibility “Order Management Super User, Vision Operations (USA)” whose functionality you want to limit, or define a new one.

In the Function and Menu Exclusions block, choose Function as the type of exclusion Rule to apply against the responsibility. Here we have restricted two function within this responsibility

a. Book Order

b. Price Order

And save work

Again switch back to Order Management Super User, Vision Operations (USA) Responsibility and query and existing order with status entered

Look in below picture there is no book Order Button and price order also disappear from action list