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;

How to replace enter in oracle

 select translate(your_column, chr(10)||chr(11)||chr(13), ' ') from your_table;

 

Oracle Purchasing Tables

Usual Oracle Applications PO Tables
PO_HEADERS_ALL: Purchase Order information with Supplier, Site and status
PO_LINES_ALL: PO Lines with Item Information and quantity
PO_LINE_LOCATIONS_ALL: PO Information on Shipments Destination
PO_DISTRIBUTIONS_ALL: PO Distribution where the accounts are
PO_LINE_LOCATIONS_ARCHIVE_ALL: table updated for history on Shipment/Destination Location Information
PO_LINE_TYPES_B: PO Types used in the PO_LINES_ALL
PO_LINES_TYPES_TL: PO Line Types used in MLS
PO_RELEASES_ALL: Stores revision number for the PO
PO_HEADERS_ARCHIVE_ALL: table updated for the history on the status and PO Header changes, Lines, Location and PO Accounts Distribution
PO_LINES_ARCHIVE_ALL: table updated for the history on the lines
PO_DISTRIBUTIONS_ARCHIVE_ALL: table updated for the history on the account distribution
PO_AGENTS_V: Buyer
PO_VENDORS: Supplier Table
PO_VENDOR_SITES: Supplier Sites
PO_VENDOR_CONTACTS: Supplier Contacts

PO_HAZARD_CLASSES: contains code and description for hazardous items which gets automatically printed into purchase order, RFQ and Receipt Travelers

PO_REQUISITION_LINES_ALL: Requisition Lines
PO_REQUISITION_HEADERS_ALL: Requisition Headers
PO_REQ_DISTRIBUTIONS_ALL: Distribution Lines of Requisition where accounts are
PO_APPROVED_SUPPLIER_LIST: Supplier List for Auto-Sourcing
PO_ASL_DOCUMENTS: Advanced Shipment Documents
PO_APPROVAL_LIST_HEADERS: PO Approval Path
PO_APPROVAL_LIST_LINES: PO Approval Path


OTHER RELATED:
MRP_SOURCING_RULES: Used for Auto-Sourcing Rules
MRP_SR_RECEIPT_ORG: Used for Auto-Sourcing Rules
MRP_SR_SOURCE_ORG: Used for Auto-Sourcing Rules
MRP_ASSIGNMENT_SETS: Used for Auto-Sourcing Rules
MRP_SR_ASSIGNMENTS: Used for Auto-Sourcing Rules
RCV_SHIPMENT_HEADERS: Shipment Table Header with grouping information
RCV_SHIPMENT_LINES: Shipment Table lines with item information
RCV_TRANSACTIONS: PO Lines or Requisition received in destination or transit
RCV_ACCOUNTING_EVENTS: Receiving information on accounts
RCV_RECEIVING_SUB_LEDGER: Accounting entries generated for the receiving transactions
RCV_SUB_LEDGER_DETAILS: Detail accounting entries generated for the receiving transactions
RCV_LOT_SUPPLY: Parent for RCV_LOT_TRANSACTIONS
RCV_LOT_TRANSACTIONS: Table housing the information what lot the item is received

INTERFACES:
PO_HEADERS_INTERFACE: Used for creating PO
PO_LINES_INTERFACE: Used for creating PO
PO_DISTRIBUTIONS_INTERFACE: Used for creating PO
PO_REQUISITIONS_INTERFACE: Used for creating Requisition
PO_REQ_DIST_INTERFACE: Used for creating Requisition
PO_INTERFACE_ERRORS: Error created during the processing of the PO or requisition
RCV_INTERFACE: Used for creating Received Items
RCV_HEADERS_INTERFACE: Used for creating Received Group of items
RCV_LOT_INTERFACE: Used for receiving item into a particular lot

How to clean Oracle temp table when it is full

How to clean Oracle temp table when it is full

1. Check status

SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM V$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name


2. Clean up

alter tablespace temp default storage(pctincrease 5);
//to have smon clean up temp tablespace,
alter tablespace temp default storage(pctincrease 0);

How to export Assets data from AP to FA

How to export Assets data from AP to FA

In order to transfer a expense line from AP to FA - (1) Track as Asset flag should be enabled (AP Invoice Lines)
(2) AP line distribution (expense) account should match with the clearing account specified in FA Book controls or category definition
(3) Transfer to GL flag should be Y (you can transfer to FA only after AP to GL transfer is complete)

Program:
Mass Additions Create
Select the FA Book and Period

Data will be transferred to FA as New records in Mass additions table.

Update the record as Assetized and assign FA Category,update the depreciation account, update employee assignments and Location (if any).

Oracle EBS - ap_suppliers to hz_parties