Oracle ERP Base

Here you can find some SQL samples and some selected commands.

--Select
select vendor_name
from po_vendors

--Where
select vendor_name
from po_vendors
where vendor_name = 'Google'

--Group by
select vendor_name,segment1 'vendor_number'
from po_vendors
group by vendor_name

--Having
select vendor_name,segment1 'vendor_number'
from po_vendors
group by vendor_name
having segment1 > 5010

--And / Or
select vendor_name,segment1 'vendor_number'
from po_vendors
where vendor_name = 'google' or 'Yahoo'

--Insert
INSERT INTO table_name (field1 [, field2]…)
VALUES (value1 [,value2]…)

--Update
UPDATE table_name
SET field1 = (value1|expr1)
[, field2 = (value2|expr2)]…)
[WHERE (condition1 [, condition2]…)]

Caution: Using the wrong WHERE conditions it is easy to "update" the entire table with the wrong values!
Take care to ensure that you are updating the right records.

--Delete
DELETE FROM table_name
[WHERE (condition1 [, condition2]…)]


Below are the different methods of Oracle apps initialization which sets the environment for each responsibility we login.

fnd_global.apps_initialize
fnd_global.initialize
fnd_profile.initialize
dbms_application.set_client_info

mo_global.init('S',null);
MO_GLOBAL.INIT（’M’）

begin
  mo_global.set_policy_context('S',276);
  dbms_application_info.set_client_info(client_info=>'276');
end;

Getting user_id, login_id
-------------------------
fnd_profile.value('user_id')
fnd_profile.value('login_id')
and
fnd_global.user_id
fnd_global.login_id

fnd_file.put_line(fnd_file.log, 'Log');
fnd_file.put_line(fnd_file.output, 'Output');

Set Following Profile Options as Required at User or Site

Personalize Self-Service Defn = YES  --enable                                                                                                                 
FND: Personalization Region Link Enabled = YES
Disable Self-Service Personal = NO (Only at Site)
FND: Diagnostics = Yes

this profile  "Utilities:Diagnostics"  using control Diagnostics funaction

How to extract from view ?

Copy view  for example:AR_CUSTOMERS_V

Past view and do right click choose open object at cursor

select *
from user_views u
where u.view_name = 'AR_CUSTOMERS_V'

Press TEXT (LONG)

Copy the "select"
It's looks very long...press formater and run sql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-705063/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-705063/