PO Data Flow

PO Data Flow

Read More»

Query to find responsibilities to which a Concurrent program is assigned

SELECT DISTINCT *
FROM apps.fnd_responsibility_tl
WHERE responsibility_id IN
  (SELECT responsibility_id
  FROM apps.fnd_responsibility_vl
  WHERE request_group_id IN
    (SELECT request_group_id
    FROM apps.fnd_request_group_units
    WHERE request_unit_id =
      (SELECT DISTINCT concurrent_program_id
      FROM Apps.fnd_concurrent_programs_tl
      WHERE user_concurrent_program_name = :Concurrent_Program_name
      )
    )
  AND end_date IS NULL
  )
AND "LANGUAGE" LIKE 'US'
ORDER BY responsibility_name;

Read More»

Query to find out Responsibilities assigned to a user

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, u.DESCRIPTION,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
and u.user_name='AT640'
--and r.RESPONSIBILITY_NAME like '%'
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

Read More»

Script To List The Values Of A Profile Option At All Levels

--Script To List The Values Of A Profile Option At All Levels (Doc ID 803587.1)
SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name
, substr(fpot.user_profile_option_name,1,60) profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name
,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value
, fpov.profile_option_value profile_value
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, fnd_responsibility_tl fr
, fnd_user fu
WHERE (fpo.profile_option_name like nvl('X',fpo.profile_option_name) -- Replace X with the profile short name, ie 'ORG_ID'
or fpot.user_profile_option_name like nvl('Y',fpot.user_profile_option_name)) -- Replace Y with profile user name, ie 'MO: Op%'
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value;

Read More»

SQL Query To find out File Version

select sub.filename, sub.version
from (
   select adf.filename filename,
   afv.version version,
   rank()over(partition by adf.filename
     order by afv.version_segment1 desc,
     afv.version_segment2 desc,afv.version_segment3 desc,
     afv.version_segment4 desc,afv.version_segment5 desc,
     afv.version_segment6 desc,afv.version_segment7 desc,
     afv.version_segment8 desc,afv.version_segment9 desc,
     afv.version_segment10 desc,
     afv.translation_level desc) as rank1
   from ad_file_versions afv,
     (
     select filename, app_short_name, subdir, file_id
     from ad_files
     where upper(filename) like upper('%&filename%')
     ) adf
   where adf.file_id = afv.file_id
) sub
where rank1 = 1
order by 1

Read More»

Advance Pricing Concepts

Advance Pricing is an advanced, highly flexible pricing engine that executes pricing calculations for Oracle Order Management. Its a rule based application which enables us to

• Give discounts by percentage and amount
• Default price based on Price list, Qualifiers and Pricing formulas
• Apply price modifiers and 
• Apply a surcharge
• Calculate Freight Charges

Concepts in Advance Pricing

• Price List
• Qualifiers
• Modifiers
• Pricing Formulas
• Pricing Attributes

Price list: Each item must have a price and defining its price in price list is a mandatory step. Price list is a repository of item pricing details and to define a price list you must specify price list name, its effective dates, what is currency, item details etc. In price list you can also define price break, specify a qualifier and secondary price list.

 For a price list, you can define Price Breaks, Pricing attributes, Qualifiers, Formulas, and secondary price lists. 

Qualifiers : Qualifier are the attributes which help the pricing engine to determine eligible sales order line for defaulting price, formulas or Modifiers.

Modifiers : A modifier enables us to make price adjustment like giving discount or applying surcharge or freight.

Pricing Formulas : Formulas are mathematical expressions that the pricing engine uses to determine the list prices of items and the discounts that apply to those items.

Pricing Attributes : Attributes define exactly what is being priced or modified. Attributes are the factors that affect the price of the item. It specify the characteristics which helps to determine the price of a product. For example Customer class, Distance, Age of the product etc. You can specify one or a combination of pricing attributes and assign them to a product. At order entry time, the pricing engine evaluates the attributes you have specified during formula setup to calculate the price.

Thanks
Mandeep

------------------------------------------------------------------------------------------------------------------------------------------------------------------
Please share this knowledge with your friends and subscribe at our blog for more updates. If you have any queries, please participate in our Forum or by leaving your comments below.
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Read More»

Match Approval Level and Invoice Match Option

What is the difference between "Match Approval Level" and "Invoice Match Option" on PO Shipments

Ever wonder whats the difference between Match Approval Level and Invoice match option.

The Invoice Match Option and Match Approval Level both are independent options. you can select one independent of the other.

Match Approval Level: 

Match Approval Level mean Invoice quantity should match with which purchasing document quantity before it can be paid.

Two-way: Purchase Order and Invoice quantities must match within tolerance before the corresponding invoice can be paid.

Three-way: Purchase Order, Receipt and Invoice quantities must match within tolerance before the corresponding invoice can be paid.

Four-way: Purchase Order, Receipt, Accepted and Invoice quantities must match within tolerance before the corresponding invoice can be paid.

Invoice Match Option:

Means Payable must match the Invoice with PO or Receipt.

Purchase Order: Payable must match the invoice to the purchase order

Receipt: Payable must match the invoice to the receipt.

Thanks

Mandeep

------------------------------------------------------------------------------------------------------------------------------------------------------------------
Please share this knowledge with your friends and subscribe at our blog for more updates. If you have any queries, please participate in our Forum or by leaving your comments below.
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Read More»