Difference between org_id and organisation_id

Organization ID refers to a unique id of an inventory organization (mtl_parameters)

Org_id refers to a operating unit (hr_operating_units)

Interface Tables

GL INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ----------------------
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE

AP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES


TNAME TABTYPE

------------------------------ --------------------------------------
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE


RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE

FA INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- ----------------------


FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE

INVENTORY INTERFACE TABLES


TNAME TABTYPE


------------------------------ ------- ------------------------------------


MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE

TNAME TABTYPE


------------------------------ ------- --------------------------------------


MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE


TNAME TABTYPE


------------------------------ ------- ------------------------------------
MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE


PO INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- -------------------------


PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE

BOM INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- ----------------------------------


BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE

WIP INTERFACE TABLES

TNAME TABTYPE


------------------------------ ------- --------------------------


WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE

Lock a table

Locking a table prevents two users to update a table at same time. 

Using locks we can prevent data replications. 

LOCK TABLE employees
   IN EXCLUSIVE MODE;

This exclusive mode enables the user to look into the rows but can update it.

And this command for remote link tables.

LOCK TABLE employees@DB

IN SHARE MODE 

The alternative for this is to use sequence in your table.It will also prevent data duplications. 

Index

Indexing...


why we should go for indexing?


When we are using more then two tables with 'n' number of check with DB.
We can create index for the specific column name to be referenced.


e.g:


create

So now when i run the query in my package it will refer to the index in the ratio 1:1:1:1

i.e everything join conditionally.

This will reduce the time consumption also. index ix_index on per_all_people_f(person_id,effective_end_date,person_type_id,national_identifier);

Date track for update

To pull the date updated by eliminating the previous version we can use this truncate staement for date tracked tables which will give only the updated values.

E.g:- select nvl(pac.segment1,null) from
                         per_person_analyses ppa,
                         per_analysis_criteria pac,
                         per_all_people_f papf
                         where ppa.ANALYSIS_CRITERIA_ID=pac.ANALYSIS_CRITERIA_ID
                         and ppa.id_flex_num =p_id_flex_num and ppa.person_id=p_person_id
                         and papf.person_id=ppa.person_id and
                         trunc(sysdate) between papf.EFFECTIVE_START_DATE
                         and papf.EFFECTIVE_END_DATE and trunc(sysdate) between ppa.DATE_FROM
                         and nvl  (ppa.DATE_TO,trunc(sysdate));

NVL in reports

To choose all the values or the parametr value:

nvl(:p_param,colum_name);

this will replace the param even if we dont pass manually.

Custom Report Notes:

While Registering the report to instance we need to make sure od date format:

1.The default type is YYYY/MM/DD HH24:MI:SS
  go to properties of the parameter and update in format mask.

2.Have to pass P_Conc_Request_id to make sure we can retrive the request id.
 The parameter name and the token name should be the same.

3.In before report trigger have to call the client info if we take inputs from views.

eg: begin
      fnd_client_info.set_org_context('104');
      end;

4.If u need the template to be same for multiple inputs.e.g payslip for all employees to be generated in a template from two or more queries then have to use Link in reports builder.