How to Get Single Employee with Multiple Rules in WorkFlow

Use Following Query to Get Multiple Rules Attached to a Single Employee
Connect to instance By Using APPS User Name

SELECT orig_system, description, orig_system_id, NAME “Login Name”,
display_name “Employee Name”
FROM wf_local_roles wfr1
WHERE (wfr1.orig_system ‘WF_LOCAL_ROLES’ AND wfr1.orig_system_id 0)
AND EXISTS (
SELECT ‘row found’
FROM wf_local_roles wfr2
WHERE ( wfr2.orig_system ‘WF_LOCAL_ROLES’
AND wfr2.orig_system_id 0
)
AND wfr2.orig_system_id = wfr1.orig_system_id
AND wfr2.orig_system = wfr1.orig_system
AND wfr2.NAME wfr1.NAME)
ORDER BY orig_system

Get Report List With Parameters

Use this query to Get Report List along With Parameters

Connect with APPS Password

SELECT a.concurrent_program_name AS concurrent_program_name,
a.user_concurrent_program_name AS user_concurrent_program_name,
c.application_short_name AS application_short_name,
b.column_seq_num AS column_seq_num, b.srw_param AS param_seq,
b.form_left_prompt AS prompt,
d.flex_value_set_name AS values_set_name
FROM fnd_concurrent_programs_vl a,
fnd_descr_flex_col_usage_vl b,
fnd_application c,
fnd_flex_value_sets d
WHERE a.enabled_flag = ‘Y’
AND a.concurrent_program_name =
SUBSTR (b.descriptive_flexfield_name, 7, 100)
AND a.application_id = c.application_id
AND b.enabled_flag = ‘Y’
AND b.flex_value_set_id = d.flex_value_set_id
ORDER BY a.concurrent_program_id, b.column_seq_num

Oracle Order to Cash Basics

If you want a high-level overview of Oracle Order to Cash then please visit the following link

http://www.bryanthompsononline.com/oracle/wp-content/uploads/2006/07/Oracle%20OTC.ppt

Tables Used in Pricing

Following Tables used to store all price List Data and used Frequently in reporting and oracle Internal operations

· OE_PRICE_ADJUSTMENTS

This table is used to store price adjustments that have been applied to an order or a line. The column automatic flag indicates if the adjustment was applied automatically or manually. Manual discounts are created with applied_Flag = Y.

SELECT price_adjustment_id, creation_date, header_id “Order Header”,

automatic_flag “discount applied automatically”,

line_id “ORDER LINE_ID”,

orig_sys_discount_ref “Original discount reference”,

list_header_id “Header Id of the Modifier”,

list_line_id “Line id of the Modifier”,

list_line_type_code “Line Type of the Modifier”,

accrual_flag “adjustment is accrued”, benefit_qty “Quantity accrued”,

benefit_uom_code

FROM ont.oe_price_adjustments

· OE_PRICE_ADJ_ATTRIBS

This table stores information on qualifiers and pricing attributes, which to that corresponding price adjustment line qualified for “price_adjustment_id” is link between this table and ont.oe_price_adjustments

SELECT price_adjustment_id,

pricing_attr_value_from “from Value pricing Attribute”,

pricing_attr_value_to “To Value pricing Attribute”,

comparison_operator “Operators”,

flex_title “pricing_context Flex_name”, price_adj_attrib_id,

lock_control

FROM ont.oe_price_adj_attribs;

· OE_PRICE_ADJ_ASSOCS

This table stores the association between Order lines and price adjustments and also between price adjustments. One adjustment may be a result of benefit on one or more order lines. “price_adjustment_id” is link between this table and ont.oe_price_adjustments

SELECT line_id “Order Line Id”, price_adjustment_id,

rltd_price_adj_id “price_adjustment_id”

FROM ont.oe_price_adj_assocs;

How to Get User’s Status in the ICX_SESSIONS Table

Connect to Application by using apps user and run the following SQL Query

SELECT (SELECT user_function_name
FROM fnd_form_functions_vl fffv
WHERE (fffv.function_id = a.function_id)) “Current Function”,
TO_CHAR (first_connect, ‘MM/DD/YYYY HH:MI:SS’) start_time,
TO_CHAR (last_connect,
‘MM/DD/YYYY HH:MI:SS’
) “Date and time of last hit”,
TO_CHAR (SYSDATE, ‘HH:MI:SS’) current_time, user_name, session_id,
(SYSDATE - last_connect) * 24 * 60 mins_idle,
fnd_profile.value_specific (’ICX_SESSION_TIMEOUT’,
a.user_id,
a.responsibility_id,
a.responsibility_application_id,
a.org_id,
NULL
) TIMEOUT,
counter “How many hits a User has made”,
a.limit_connects “No of hits allowed in session”
FROM icx_sessions a, fnd_user b
WHERE a.user_id = b.user_id AND last_connect > SYSDATE - 1 / 24;

Oracle Cash Management and Oracle Receivable

For Cash Management to work with Oracle Receivables, we need to establish the following in Cash Management:

· Set of Books

· Banks

· Receipt sources

· Receipt class (optional)

· Receivable activities (optional)

Set of Books

Set of Books must be assigned as system options in Oracle Receivables for each operating unit. We must also select these Sets of Books as the Sets of Books in the Oracle Cash Management System Parameters form.

Banks

We must configure bank accounts for banks that will be reconciled using Oracle Cash Management. We must mark the bank accounts with an account use of Internal—but not Customer or Supplier. We must also have the cash clearing, bank charges, and bank errors accounts set up in the GL Accounts alternative region of Bank Accounts. For remittance bank accounts, we need to assign the cash account and remittance account.

Receipt Sources

We need to define at least one receipt source that will be used for assigning numbers to receipt batches.

Receipt Class

If we are planning to create miscellaneous receipts or payments from Oracle Cash Management, we need to set up a receipt class for them. Miscellaneous payments are negative receipts, and are generated in Oracle Receivables, not Oracle Payables. The receipts class setup for this purpose utilizes a manual creation method and is set to not require remittance.

Receivable Activities

To create miscellaneous receipts or payments from Oracle Cash Management, we must set up a receivable activity.

A receivable activity set up for this purpose requires an account type of either bank or miscellaneous cash. For the miscellaneous cash type, we must either enter an account or select a distribution set.

Display database links with remote passwords

Connect to instance by using sys login (as sysdba)

Execute the following query

SELECT u.NAME owner, l.NAME “Dblink Name”, l.HOST “Host Name”,

l.userid || ‘/’ || l.PASSWORD userpass

FROM SYS.user$ u, SYS.link$ l

WHERE l.owner# = u.user#;

Accounts Receivable Profile Options

For the Accounts Receivable values the following are the most critical profile options and used to control Oracle Receivable functionality

AR: Allow Transaction Batching - If you enter small volumes of manual transactions, it is not necessary to use batches for transactions. If you enter high volumes, you should use batching for transactions. You should always use batching for receipts since you get immediate feedback of what you have done and any potential problems.

AR: Cash Allow Actions - As part of the cash application process, the applier has the option to create adjustments (write-offs) and charge backs if you set this option to Yes. I recommend that you do that, you can always control the amounts they write-off using approval limits.

AR: Cash - Default Amount Applied - Always set so the default is the amount remaining on the receipt, not the amount remaining on the invoice. If you allow the receipt amount to default to the invoice balance, this allows you to apply more than you really have.

AR: Change Customer Name - In reality customers change their names and this is a valuable feature but, I recommend that you set it “Yes” only after to have been live for a few weeks. This is a good way to keep novice users from overlaying good names with invalid names, until the know what they are doing.

AR: Close Period - Run Collections Effectiveness - I recommend that you set this option to “No.” Most users do not use this report and it will just slow the close process.

AR: Update Due Date - Allow change to due date. In rare instances it is desirable to change the due date on an invoice (for example, you have negotiated this with the customer). By setting this profile option to “Yes,” you have the option of doing this. What this really does is to allow you to make your aging look better. Consider whether or not this is desirable in you business situation.

AR: Use Invoice Accounting For Credit Memo - In most cases, if you know the original invoice, it is desirable to offset the accounts that were used for the invoice when creating the related Credit Memo. This option allows you to do that.

PURCHASE ORDER AND INVOICE MATCHING

Purchasing and Payables enable you to match invoices to purchase order shipments or purchase order distribution lines. There are following three ways to match Purchase Order and Invoices

2-way matching

This verifies that Purchase order and invoice information match within your tolerances as follows and check following:

Invoice price <= Purchase order price Quantity billed <= Quantity Ordered

3-way matching
This verifies that the receipt and invoice information match with the quantity tolerances defined and check following:

Invoice price <= Purchase order price Quantity billed <= Quantity Ordered Quantity billed <= Quantity received

4-way matching
This verifies that acceptance documents and invoice information match within the quantity tolerances defined and check following:

Invoice price <= Purchase order price
Quantity billed <= Quantity Ordered
Quantity billed <= Quantity received
Quantity billed <= Quantity accepted

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