EBS structure level difference for R11 & R12

 R11.

    BG - Business Group

    SOB - Set of Book

    LE - Legal Entity

    OU - Operation Unit

    Org - Organization

R12.

    BG - Business Group

    LE - Legal Entity

    SOB - Set of Book

    OU - Operation Unit

    Org - Organization

1) Supplier & Customer are belonged to OU level , difference OU only can see header information. 

2) MOAC(multi-org access control) can make one responsibility to see whole supplier information.

 

Cost - Periodic Actual Cost Worker CSTPPINV.COST_INV_TXN:CSTPPAQH.ACQ_RECEIPT_COST_HOOK-No_Receipt_cost error

 --期初結帳找不到採購行為

open procedure:

CSTPPAHK.acq_receipt_cost_hook

edit:

  a) Change o_error_num := -1 to o_error_num := 0;

  b) Change o_hook_cost := 0 to 

Tablespace Check

 

Check tablespace

SELECT a.tablespace_name "表空間名",

   a.bytes / 1024 / 1024 "表空間大小(M)",

   (a.bytes - b.bytes) / 1024 / 1024 "已使用空間(M)",

   b.bytes / 1024 / 1024 "空閒空間(M)",

   round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"

   FROM (SELECT tablespace_name, sum(bytes) bytes

   FROM dba_data_files

   GROUP BY tablespace_name) a,

   (SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest

   FROM dba_free_space

   GROUP BY tablespace_name) b

   WHERE a.tablespace_name = b.tablespace_name

   ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC

   

 

Query if tablespace auto-extend

select tablespace_name, file_name, autoextensible from dba_data_files

--where autoextensible <> 'YES';

Last day, last time

 

select last_day(add_months(sysdate,-1)) from dual;
select last_day(add_months(trunc(sysdate+1)- INTERVAL '1' SECOND,-1)) from dual;

SELECT TRUNC(SYSDATE + 1) - 1/(24*60*60) FROM DUAL
SELECT TRUNC(SYSDATE + 1) - INTERVAL '1' SECOND FROM DUAL;

Cost related tables - Include PAC

 

Most Important Item cost tables in oracle apps

1.cst_cost_types

2.cst_item_costs

3.mtl_system_items_b

4.mtl_parameters

5.cst_pac_item_costs

Detail SQL Query using Item cost tables in oracle apps

SELECT msi.segment1 "ITEM_NAME", msi.inventory_item_id,

item_cst.item_cost, mp.organization_code, mp.organization_id,

cost_type.cost_type, cost_type.description cost_type_description, item_cst.tl_material,

item_cst.tl_material_overhead, item_cst.material_cost,

item_cst.material_overhead_cost, item_cst.tl_item_cost,

 item_cst.unburdened_cost, item_cst.burden_cost,

 pl_resource recorce_cost, pl_overhead overhead_cost

  FROM apps.cst_cost_types cost_type, apps.cst_item_costs item_cst,

  apps.mtl_system_items_b msi, apps.mtl_parameters mp

 WHERE cost_type.cost_type_id = item_cst.cost_type_id

   AND item_cst.organization_id = msi.organization_id

   AND msi.organization_id = mp.organization_id

   AND item_cst.inventory_item_id = msi.inventory_item_id

   AND cost_type.cost_type = 'Average'

   AND msi.segment1 = '1233333' ---ITEM CODE----

   AND mp.organization_code = '121'  --ORG CODE--

Oracle Tables information website - eTRM - Technical Reference

 eTRM - Technical Reference 

see information through below website

http://etrm.oracle.com/pls/trm1222p/etrm_fndnav.ls_object

Insert query into Form Personalization items and get profile value

 =(select distinct(legal_entity)  from org_organization_definitions where operating_unit=FND_PROFILE.VALUE ('ORG_ID'))

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;