Oracle Treasury Tables in oracle apps

XTR_A_BANK_ACCOUNTS           

XTR_A_BANK_BALANCES             Created by ORACLE on 17-DEC-93

XTR_A_BANK_SETTLEMENT_CODES     Created on 21-SEP-94

XTR_A_BOND_ISSUES             

XTR_A_BUY_SELL_COMBINATIONS    

XTR_A_COMPANY_LIMITS            

XTR_A_COMPANY_PARAMETERS        This table is used to audit Xtr_Company_Parameters

XTR_A_COUNTERPARTY_LIMITS      

XTR_A_DEAL_DATE_AMOUNTS         

XTR_A_DEAL_ORDERS               Created from Entity XTR DEALS by ORACLE on 28-NOV-94

XTR_A_DEALER_CODES              Created by ORACLE on 06-MAY-95

XTR_A_DEALER_LIMITS             Created by ORACLE on 25-JUL-96

XTR_A_DEALS                     Created from Entity XTR DEAL by ORACLE on 06-MAY-93

XTR_A_EXPOSURE_TRANSACTIONS   


XTR_A_EXPOSURE_TYPES           
XTR_A_FUTURES                   Created from Entity XTR FUTURES by ORACLE on 16-JUN-95

XTR_A_FUTURES_FEES             

XTR_A_FUTURES_MARGIN_DETAILS    

XTR_A_GL_REFERENCES            

XTR_A_INTERGROUP_TRANSFERS      

XTR_A_JOURNAL_ENTRY_ACTIONS    

XTR_A_JOURNALS                 

XTR_A_MASTER_CURRENCIES        

XTR_A_PARTY_DEFAULTS            Created from Entity XTR PARTY by ORACLE

XTR_A_PARTY_INFO                Created from Entity XTR PARTY by ORACLE on 06-MAY-93

XTR_A_PORTFOLIOS               

XTR_A_PRO_PARAM                 Created by ORACLE on 20-FEB-95

XTR_A_PRODUCT_TYPES           

XTR_A_RATE_SETS                 Created on 01-FEB-95

XTR_A_REVALUATION_DETAILS       Created on 01-FEB-95

XTR_A_REVALUATION_RATES         Created on 01-FEB-95

XTR_A_ROLLOVER_TRANSACTIONS  

XTR_A_SETTLEMENT_CATEGORIES     Created on 27-JAN-95

XTR_A_STANDING_INSTRUCTIONS     Created on 27-JAN-95

XTR_A_TAX_BROKERAGE_RATES       Created by ORACLE on 13-MAR-95

XTR_A_TAX_BROKERAGE_SETUP       Created on 27-JAN-95

XTR_A_TAX_CATEGORIES            Created on 27-JAN-95

XTR_ACCEPTED_BIDS               Created by ORACLE on 13-SEP-95

XTR_ACCOUNT_DEBIT_AUTHS             Created by ORACLE on 14-FEB-97

XTR_ACCRLS_AMORT                    Accrual amounts for deal transactions

XTR_ACCRUAL_AMORTISE_SETUP         

XTR_AMOUNT_ACTIONS          Amount action information

XTR_AMOUNT_TYPES            Amount type information

XTR_ARCHIVE_DETAILS         Created by ORACLE on 02-JUN-95

XTR_ARCHIVE_ERROR_LOG       Created by ORACLE on 02-JUN-95

XTR_ARCHIVE_HISTORY         Created by ORACLE on 02-JUN-95

XTR_ARCHIVE_SETUP           Created by ORACLE on 02-JUN-95

XTR_AUDIT_COLUMNS           Created by ORACLE on 27-MAY-96

XTR_AUDIT_GROUPS     

XTR_AUDIT_MODULE_ACCESS             Created by ORACLE on 06-MAR-97

XTR_AUDIT_SUMMARY                 

XTR_AUTH_CCY_COMBINATIONS         

XTR_AUTH_TYPE_SUBTYPE_PROD        

XTR_AVG_RATES_RESULTS     

XTR_BANK_ACCOUNTS                   Bank account information

XTR_BANK_BAL_INTERFACE           

XTR_BANK_BALANCES                   Daily bank balance information

XTR_BATCH_EVENTS                   

XTR_BATCHES                        

XTR_BIDS_RECD                       Created on 23-NOV-94

XTR_BILL_BOND_ISSUES               

XTR_BOND_ALLOC_DETAILS              For realized gain/loss from bond resale

XTR_BOND_COUPON_DATES               Coupon dates for bond issues

XTR_BOND_ISSUES                     Bond issues information

XTR_BROKERAGE_CATEGORIES           

XTR_BUY_SELL_COMBINATIONS            Buy and sell currency combination information

XTR_CALL_DETAILS                    

XTR_CASHPOOL_ATTRIBUTES            

XTR_CFLOW_REQUEST_DETAILS            Update Settlement Accounts Request Details.

XTR_CFLOW_UPDATED_RECORDS          

XTR_COMPANY_AUTHORITIES             User access authorities of companies

XTR_COMPANY_LIMITS                 

XTR_COMPANY_PARAMETERS             

XTR_CONFIRMATION_ACTIONS           

XTR_CONFIRMATION_DETAILS            Confirmation letter details

XTR_CONFIRMATION_SETUP              Created by ORACLE on 06-JUL-96

XTR_COST_OF_FUNDS                  

XTR_COUNTERPARTY_LIMITS            

XTR_COUNTRY_COMPANY_LIMITS         

XTR_CUM_DIST_CALCS                  Created on 03-DEC-93

XTR_CURRENCY_CROSS_RATES           

XTR_DATA_FEED_CODES                 Created by ORACLE on 30-MAY-96

XTR_DATE_AMOUNT_TYPES             

XTR_DATE_TYPES                      Date types information

XTR_DDE_FEED_LOCKS                   Created by ORACLE on 20-NOV-96

XTR_DEAL_CONFO_TYPES               Deal confirmation types used in confirmation letter templates

XTR_DEAL_CONTACTS                  Created on 28-MAR-96

XTR_DEAL_DATE_AMOUNTS           

XTR_DEAL_LINKING     

XTR_DEAL_ORDERS                     Created from Entity XTR DEALS by ORACLE on 28-NOV-94

XTR_DEAL_OVERWRITE_TYPES           

XTR_DEAL_RETRO_TESTS               

XTR_DEAL_STATUSES                   Deal status information

XTR_DEAL_SUBTYPES                   Deal subtype information

XTR_DEAL_TYPES                      Deal type information

XTR_DEALER_CODES                    Treasury users information

XTR_DEALER_LIMITS                   Dealer limits information including limit amounts

XTR_DEALS                         

XTR_DEALS_INTERFACE              

XTR_DEDUCTION_CALCS             

XTR_EXPOSURE_SUMMARY                Created by ORACLE on 21-OCT-96

XTR_EXPOSURE_TRANSACTIONS          

XTR_EXPOSURE_TYPES                  Exposure type information

XTR_EXT_IMPORT_INTERFACE           

XTR_EXTERNAL_CASHFLOWS              Not currently used

XTR_FORECAST_PERIOD_TEMP     

XTR_FORECAST_PERIODS                Not currently used

XTR_FUTURES                         Created from Entity XTR FUTURES by ORACLE on 16-JUN-95

XTR_FUTURES_FEES                   

XTR_FUTURES_MARGIN_DETAILS         

XTR_FX_MULTIPLE_CONTRACTS          

XTR_FX_PERIOD_WEIGHTINGS            Limit weightings information

XTR_FX_ROLL_PRE_DETAILS             Created by ORACLE on 15-SEP-95

XTR_FX_ROLL_PRE_ITERATIONS          Created by ORACLE on 15-SEP-95

XTR_FX_WHAT_IFS                     Created by ORACLE on 01-MAY-97

XTR_GAIN_LOSS_DNM                  

XTR_GENERAL_LEDGER_SUMMARY          Created by ORACLE on 28-JUN-96

XTR_GL_BY_PARTY                     Created by ORACLE on 06-MAY-97


XTR_GL_REFERENCES                 

XTR_GROUP_LIMITS                   

XTR_HEDGE_ATTRIBUTES              

XTR_HEDGE_CRITERIA                 

XTR_HEDGE_ITEMS_TEMP               

XTR_HEDGE_JOURNAL_ACTIONS          

XTR_HEDGE_POLICIES                 

XTR_HEDGE_PRO_TESTS                 Stores the hedge prospective effectiveness test results.

XTR_HEDGE_RELATIONSHIPS             This table stores hedge relationship information

XTR_HEDGE_RETRO_TESTS               Stores hedge level retrospective effectiveness test and measurement details.

XTR_HEDGE_STRATEGIES                This table stores key reporting, or financial disclosure, information that is common to all the
                                    Hedges designated within that Strategy.

XTR_HOLIDAY_RULES                   Holiday rules defined for different currencies

XTR_HOLIDAYS                        Holidays defined for different currencies

XTR_IG_JOURNAL_STRUCTURES          

XTR_IMPORT_TRAILER_DETAILS        

XTR_IMPORT_TRANSFER_ERRORS        

XTR_INTERACCT_TRANSFERS            

XTR_INTEREST_PERIOD_RATES          

XTR_INTEREST_RATE_AVERAGES          Created by ORACLE on 05-DEC-94

XTR_INTEREST_RATE_BANDS             Interest rate band details for different interest rate policies

XTR_INTEREST_RATE_EXP_PER           Created by ORACLE

XTR_INTEREST_RATE_EXPOSURE     

XTR_INTEREST_RATE_POLICY      

XTR_INTEREST_RATE_RANGES       

XTR_INTEREST_TOLERANCES        

XTR_INTERFACE_ERRORS           

XTR_INTERGROUP_TRANSFERS        Intercompany loans or deposits information

XTR_INTRA_DAY_LIMITS            Created by ORACLE on 07-AUG-96

XTR_IRE_MATURITY_BY_POLICY      Created by ORACLE on 12-JUN-97

XTR_IRS_ACTUALS                 Created by ORACLE on 22-MAY-97

XTR_ITEM_RELEASE_DETAILS     

XTR_JEA_DUPLICATE              

XTR_JOURNAL_ENTRY_ACTIONS      

XTR_JOURNALS                    Detailed journal entry information

XTR_LAYOUT_RESULTS            

XTR_LAYOUT_TEMPLATE             Confirmation letter template information

XTR_LAYOUT_TEMPLATE_LINE        Confirmation template lines

XTR_LAYOUT_VALUES               Common variables for the confirmation templates

XTR_LAYOUT_VIEW_FX_CONFO         Created by ORACLE on 6 JUL 96

XTR_LAYOUT_WHERE_CLAUSE    
    
XTR_LIMIT_EXCESS_LOG            Limit exceptions caused by deal transactions

XTR_LIMIT_TYPES                 Information on defined limit types

XTR_LIMIT_UTILIZATION_FEE       Created by ORACLE on 22-OCT-97

XTR_LOAN_SECURITIES             Created by ORACLE on 30 October 1995

XTR_MARKET_DATA_INTERFACE      

XTR_MARKET_PRICES               Current rates for foreign exchange and money market deals

XTR_MASTER_CURRENCIES         

XTR_MIRROR_DDA_LIM_ROW_TMP     Created by ORACLE on 17-JUL-96

XTR_MIRROR_DDA_LIMIT_ROW      

XTR_MTS_RECORDS               

XTR_MTS_RECORDS_HISTORY       

XTR_ONE_STEP_SETTLE_CODES     

XTR_PARCEL_SPLITS               Parcel split detail information for discounted securities deals

XTR_PARTY_DEFAULTS              Counterparty default settlement information

XTR_PARTY_GROUPS                Party group information

XTR_PARTY_INFO                  Created from Entity XTR PARTY by ORACLE on 06-MAY-93

XTR_PAY_REC_RECONCILIATION      Information on successfully transferred bank statements

XTR_PAYMENT_SCHEDULE            Payment schedule information for retail term money deals

XTR_PORTFOLIO_DEAL_AMOUNTS     

XTR_PORTFOLIOS                  Company portfolio information

XTR_POSITION_HISTORY           

XTR_PRICE_MODELS             

XTR_PRINTED_FILE_DETAILS     

XTR_PRO_DUAL                     Used within ORACLE for basing forms on without base tables

XTR_PRO_PARAM                   Treasury system parameter settings

XTR_PRODUCT_TYPES               Product type information

XTR_PRT_BODY                    Created by ORACLE on 07-MAR-96

XTR_PRT_HEAD                    Created by ORACLE on 07-MAR-96

XTR_QUICK_INPUT_DEALS          

XTR_QUICK_INPUT_QUOTES          Created by ORACLE on 28-APR-97

XTR_R_BANK_BALANCES             Created by ORACLE on 17-DEC-93

XTR_R_CURRENCY_CROSS_RATES     

XTR_R_DEAL_DATE_AMOUNTS        

XTR_R_DEALS                     Created from Entity XTR DEAL by ORACLE on 06-MAY-93

XTR_R_EXPOSURE_TRANSACTIONS   

XTR_R_INTEREST_PERIOD_RATES    

XTR_R_ROLLOVER_TRANSACTIONS    

XTR_R_SPOT_RATES               

XTR_RATE_GROUPS                 Created from by ORACLE on 07-JUL-96

XTR_RATE_PREFERENCES            Created by ORACLE on 09-JUL-96

XTR_RATE_SETS                   Interest rates for retail term money deals

XTR_RECLASS_DETAILS             Stores reclassified effective unrealized gain/loss amounts.

XTR_RECONCILIATION_PASSES       Reconciliation criteria for bank statement lines
   
XTR_RECONCILIATION_PROCESS      Reconciliation method definitions

XTR_REG_HEDGE_POLICIES         

XTR_REVALUATION_DETAILS        Revaluation results of Treasury deals

XTR_REVALUATION_MARGINS     

XTR_REVALUATION_RATES         

XTR_REVIEW_CURRENCIES     

XTR_REVIEW_TRANSACTIONS         Created on 08-FEB-95

XTR_RM_MD_CURVE_RATES          

XTR_RM_MD_CURVES                Market data curve attributes

XTR_RM_MD_SET_CURVES           

XTR_RM_MD_SETS                  Market data set attributes

XTR_ROLLOVER_TRANSACTIONS   

XTR_SETTLEMENT_CATEGORIES    

XTR_SETTLEMENT_CONTACTS      Created on 28-MAR-96

XTR_SETTLEMENT_SCRIPTS       Created on 18-FEB-97

XTR_SETTLEMENT_SUMMARY      .

XTR_SETUP_AUDIT_REQMTS      Created on 28-JAN-96

XTR_SOURCE_OF_IMPORTS       Import source information used for reconciliation

XTR_SOURCE_TYPES                                      

XTR_SPOT_RATES              History of foreign exchange rates

XTR_STANDING_INSTRUCTIONS     

XTR_STMT_TXNS     

XTR_STOCK_ALLOC_DETAILS         This table is to store realized gain/loss info for STOCK

XTR_STOCK_CASH_DIVIDENDS        This table is to store the Stock Cash Dividend records

XTR_STOCK_ISSUES                This table is to store the Stock Issues setup.

XTR_SYS_LANGUAGES     

XTR_SYS_LANGUAGES_TL        Created by ORACLE on 16-AUG-96

XTR_TAX_BROKERAGE_RATES    

XTR_TAX_BROKERAGE_SETUP     Tax and brokerage schedule information

XTR_TAX_CATEGORIES          Tax categories used for grouping the calculation methods and rates

XTR_TAX_DEDUCTION_CALCS    

XTR_TEMP_AUDIT_VIEW     

XTR_TERM_ACTIONS            Term adjustments for retail term money deals

XTR_TIME_RESTRICTIONS       Term limits information

XTR_TMP_FX_EXPOSURE         Created by ORACLE on 11-JUN-97

XTR_TRANSACTIONS_INTERFACE    

XTR_VOUCHER_REFERENCES      Created on 02-DEC-94

XTR_WF_USER_ROLES           Treasury Workflow Events

XTR_WHATIF_DETAILS          Created on 02-DEC-94

XTR_WHATIF_RATES            Created on 02-DEC-94

XTR_YIELD_CURVE_DETAILS     Created on 29-OCT-96

XTR_YIELD_CURVES            Created on 29-OCT-96

Oracle Property table list in oracle apps

Property:

PN_PROPERTIES_ALL
PN_LOCATIONS_ALL

Leases :

PN_LEASES_ALL
PN_LEASE_DETAILS_ALL
PN_OPTIONS_ALL
PN_PAYMENT_TERMS_ALL
PN_DISTRIBUTIONS_ALL

Variable Rent Agreement:

PN_VAR_LINES_ALL
PN_VAR_PERIODS_ALL
PN_VAR_RENTS_ALL
PN_VAR_RENT_DATES_ALL
PN_VAR_RENT_INV_ALL
PN_VAR_RENT_SUMM_ALL

Full list is here:

PN_ACCOUNTING_EVENTS_ALL
PN_ADDRESSES_ALL
PN_AE_HEADERS_ALL
PN_AE_LINES_ALL
PN_COMPANIES_ALL
PN_COMPANY_SITES_ALL
PN_CONTACTS_ALL
PN_CONTACT_ASSIGNMENTS_ALL
PN_CONTACT_ASSIGN_HISTORY
PN_CURRENCIES
PN_DISTRIBUTIONS_ALL
PN_EMP_SPACE_ASSIGN_ITF
PN_INDEX_EXCLUDE_TERM_ALL
PN_INDEX_HISTORY_HEADERS
PN_INDEX_HISTORY_LINES
PN_INDEX_LEASES_ALL
PN_INDEX_LEASE_CONSTRAINTS_ALL
PN_INDEX_LEASE_PERIODS_ALL
PN_INDEX_LEASE_TERMS_ALL
PN_INSURANCE_REQUIREMENTS_ALL
PN_INSUR_REQUIRE_HISTORY
PN_LANDLORD_SERVICES_ALL
PN_LANDLORD_SERVICE_HISTORY
PN_LEASES_ALL
PN_LEASE_CHANGES_ALL
PN_LEASE_DETAILS_ALL
PN_LEASE_DETAILS_HISTORY
PN_LEASE_MILESTONES_ALL
PN_LEASE_OPTIONS_ITF
PN_LEASE_TRANSACTIONS_ALL
PN_LOCATIONS_ALL
PN_LOCATIONS_ITF
PN_LOCATION_FEATURES_ALL
PN_LOCATION_PARKS
PN_MILESTONES_ITF
PN_NOTE_DETAILS
PN_NOTE_HEADERS
PN_OPTIONS_ALL
PN_OPTIONS_HISTORY
PN_PAYMENT_ITEMS_ALL
PN_PAYMENT_ITEMS_EFC
PN_PAYMENT_SCHEDULES_ALL
PN_PAYMENT_TERMS_ALL
PN_PAYMENT_TERMS_EFC
PN_PHONES_ALL
PN_PROPERTIES_ALL
PN_RENT_ROLL_LEASE_EXP_ITF
PN_RIGHTS_ALL
PN_RIGHTS_HISTORY
PN_SET_MILESTONES
PN_SET_TYPES
PN_SPACE_ALLOCATIONS_ALL
PN_SPACE_ALLOC_ITF
PN_SPACE_ASSIGN_CUST_ALL
PN_SPACE_ASSIGN_EMP_ALL
PN_SPACE_ASSIGN_LEASE_ITF
PN_SPACE_ASSIGN_LOC_ITF
PN_SPACE_UTIL_LEASE_ITF
PN_SPACE_UTIL_LOC_ITF
PN_SYSTEM_SETUP_OPTIONS
PN_TENANCIES_ALL
PN_TENANCIES_HISTORY
PN_TERM_TEMPLATES_ALL
PN_VAR_ABATEMENTS_ALL
PN_VAR_BKPTS_DET_ALL
PN_VAR_BKPTS_HEAD_ALL
PN_VAR_CONSTRAINTS_ALL
PN_VAR_DEDUCTIONS_ALL
PN_VAR_GRP_DATES_ALL
PN_VAR_LINES_ALL
PN_VAR_PERIODS_ALL
PN_VAR_RENTS_ALL
PN_VAR_RENT_DATES_ALL
PN_VAR_RENT_INV_ALL
PN_VAR_RENT_SUMM_ALL
PN_VAR_VOL_HIST_ALL
PN_VOL_HIST_BATCH_ITF
PN_VOL_HIST_LINES_ITF
PN_REC_EXP_ITF
PN_REC_EXPCL_DTL_ALL
PN_REC_EXPCL_DTLLN_ALL
PN_REC_EXPCL_DTLACC_ALL
PN_REC_ARCL_ALL
PN_REC_ARCL_EXC_ALL
PN_REC_EXP_LINE_ALL
PN_REC_EXP_LINE_DTL_ALL
PN_REC_AGREEMENTS_ALL
PN_REC_AGR_LINABAT_ALL
PN_REC_AGR_LINAREA_ALL
PN_REC_AGR_LINCONST_ALL
PN_REC_AGR_LINES_ALL
PN_REC_AGR_LINEXP_ALL
PN_REC_ARCL_DTL_ALL
PN_REC_ARCL_DTLLN_ALL
PN_REC_EXPCL_ALL
PN_REC_EXPCL_TYPE_ALL
PN_REC_EXPCL_INC_ALL
PN_LOC_ACC_MAP_ALL
PN_LOC_ACC_MAP_HDR_ALL
PN_REC_CALC_PERIODS_ALL
PN_REC_PERIOD_BILL_ALL
PN_REC_PERIOD_LINES_ALL
PN_REC_LINBILL_ALL
PN_PAY_GROUP_RULES
PN_PAY_GROUP_BYS
PN_ADJUSTMENT_DETAILS
PN_ADJUSTMENT_SUMMARIES
PN_DISTRIBUTIONS_HISTORY
PN_PAYMENT_TERMS_HISTORY
PN_VAR_BKHD_DEFAULTS_ALL
PN_VAR_TEMPLATES_ALL
PN_VAR_CONSTR_TEMPLATES_ALL
PN_VAR_CONSTR_DEFAULTS_ALL
PN_VAR_LINE_TEMPLATES_ALL
PN_VAR_BKDT_DEFAULTS_ALL
PN_VAR_TRX_DETAILS_ALL
PN_VAR_TRX_HEADERS_ALL
PN_VAR_ABAT_DEFAULTS_ALL
PN_VAR_DEDUCT_ARCH_ALL
PN_RENT_INCREASE_SUMMARY_ITF
PN_VAR_VOL_ARCH_ALL
PN_OPEX_AGREEMENTS_ALL
PN_OPEX_PRORAT_BASIS_DTLS_ALL
PN_OPEX_EST_PAYMENTS_ALL
PN_OPEX_CRITICAL_DATES_ALL
PN_OPEX_NOTES_ALL
PN_OPEX_RECON_ALL
PN_OPEX_RECON_PRTBS_ALL
PN_OPEX_RECON_EXP_GRP_ALL
PN_OPEX_RECON_DETAILS_ALL
PN_OPEX_EXP_GRPS_ALL
PN_OPEX_RECON_CRDT_ALL
PN_VAR_REPORT_DATES_ALL
PN_RENT_INCREASE_DETAIL_ITF
PN_RENT_INCREASE_DETAILACC_ITF
PN_VAR_LINE_DEFAULTS_ALL

Register Custom Tables in Oracle Apps

                                               Say you have a custom table called “ERPS_EMPLOYEE” with columns EMP_ID, EMP_NAME and EMP_TYPE in your database.  You need to create a  TABLE type Value set  that pulls up information from  this table as LOV. If you give in the custom table name in “TABLE NAME” field in the “Validation Table Information” Form, Oracle Apps will not recognize it and you will get the below error saying table does not exist.


So to make your custom table  visible in front end ( while creating Value Set or in Alerts or Audits etc), you have to register it in Oracle Apps.
Let’s now see how to register a custom table.  You will need API named AD_DD for this.

1.  First you register the table using the below API:

/* Formatted on 10/19/2015 1:08:16 PM (QP5 v5.240.12305.39446) */ BEGIN    ad_dd.register_table (p_appl_short_name = >  'CUSTOM',    --Application name in which you want to register p_tab_name      =>  'ERPS_EMPLOYEE', --Table Name p_tab_type      =>  'T',   -- T for Transaction data , S for seeded data p_next_extent     =>  512,   -- default 512 p_pct_free        =>  10,   -- Default 10 p_pct_used        =>  70 --Default 70 );

2. Secondly register each of the columns as below:
 
Register Column EMP_ID

begin ad_dd.register_column (p_appl_short_name =>   'CUSTOM', --Application Name p_tab_name      =>   'ERPS_EMPLOYEE', --Table Name p_col_name     =>   'EMP_ID', --Column Name p_col_seq      =>   1, --Column Sequence p_col_type     =>   'NUMBER', --Column Data type p_col_width    =>   10, --Column Width p_nullable        =>   'N', --Use'N' if mandatory column otherwise 'Y' p_translate       =>   'N', --Use 'Y' if this has translatable values p_precision       =>   null, --Decimal precision p_scale           =>   NULL --Number of digits in number );   end; Commit;

Register Column EMP_NAME

begin ad_dd.register_column (p_appl_short_name =>   'CUSTOM', p_tab_name      =>   'ERPS_EMPLOYEE', p_col_name     =>   'EMP_NAME', p_col_seq      =>   2, p_col_type     =>   'VARCHAR2', p_col_width    =>   15, p_nullable        =>   'Y', p_translate       =>   'N', p_precision       =>   null, p_scale           =>   null ); end;

Register Column EMP_TYPE

begin ad_dd.register_column (p_appl_short_name =>   'CUSTOM', p_tab_name      =>   'ERPS_EMPLOYEE', p_col_name     =>   'EMP_TYPE', p_col_seq      =>   3, p_col_type     =>   'VARCHAR2', p_col_width    =>   15, p_nullable        =>   'Y', p_translate       =>   'N', p_precision       =>   null, p_scale           =>   null); end; commit;

3. Thirdly you register Primary Key if the table has any using the below code snippet:

Begin ad_dd.register_primary_key (p_appl_short_name =>   'CUSTOM', --Application Name p_key_name        =>   'EMP_ID_PK', --Unique name for primary key p_tab_name      =>   'ERPS_EMPLOYEE', --Table Name p_description     =>   'Emp ID Primary Key', --Description p_key_type        =>   'S', --S for Surrogate, D for Developer p_audit_flag      =>   'Y', p_enabled_flag    =>   'Y'); end; commit;

4. Finally you register Primary Key column if your table has a primary key:

Begin ad_dd.register_primary_key_column (p_appl_short_name =>  'CUSTOM', --Application Name p_key_name        =>  'EMP_ID_PK', --Primary Key name given above p_tab_name        =>  'ERPS_EMPLOYEE',--Table Name p_col_name        =>  'EMP_ID', --Primary Column name p_col_sequence    =>  1); --Column seq end; commit;

Navigate to Application Developer responsibility > Application > Database > Table




Query for the table name that we have registered – “ERPS_EMPLOYEE”. Please note that you cannot register your table using this form in the front end. You will have to use API. This form is only meant for viewing the information.




Check for the primary key information by clicking on the Primary Key button




Now in your Value set, you will be able to  use the table ERPS_EMPLOYEE without any errors.

To delete the registered Tables and its columns, use the below API:

 

AD_DD.DELETE_COLUMN(appl_short_name,
                                                       table_name,
                                                       column_name);

 

 AD_DD.DELETE_TABLE( appl_short_name, table_name);

Important Tables Joins in Oracle apps WIP, BOM, INV

1) BOM :

BOM_RESOURCES (BR)

BOM_BILL_OF_MATERIALS (BBOM)

BOM_DEPARTMENTS (BD)

BOM_OPERATIONAL_ROUTINGS (BOR)

BOM_OPERATION_SEQUENCES (BOS)

BOM_OPERATION_SEQUENCES (BOS)

BOM_INVENTORY_COMPONENTS (BIC)

BOM_STANDARD_OPERATIONS (BSO)

2) INV : 

MTL_PARAMETERS (MP)

MTL_SYSTEM_ITEMS_B (MSIB)

MTL_MATERIAL_TRANSACTION (MTT)

MTL_SERIAL_NUMBERS (MSN)

MTL_ITEM_REVISIONS (MIR)

MTL_ITEM_CATEGORIES (MIC)

MTL_DEMAND (MD)

MTL_TRANSACTION_TYPES (MTTY)

MTL_CATEGORIES (MC)

CST_ITEM_COSTS (CIC)

CST_COST_ELEMENTS (CCE)

3) WIP :

WIP_PARAMETERS (WP)

WIP_DISCRETE_JOBS (WDJ)

WIP_ENTITIES (WE)

WIP_OPERATIONS (WO)

WIP_REQUIREMENT_OPERATIONS (WRO)

WIP_OPERATION_RESOURCES (WOR)

WIP_TRANSACTIONS (WT)

WIP_TRANSACTION_ACCOUNTS (WTA)

WIP_LINES (WL)

WIP_EAM_WORK_REQUESTS (WEWR)

WIP_EAM_PARAMETERS (WEP)

1) JOINS IN BOM and BOM-INV

BOM_BILL_OF_MATERIALS (BBOM)                       MTL_SYSTEM_ITEMS_B (MSIB)

organization_id                                                                  organization_id

assembly_item_id                                                              inventory_item_id

MTL_SYSTEM_ITEMS_B (MSIB)                                BOM_OPERATIONAL_ROUTINGS (BOR)

inventory_item_id                                                              assembly_item_id

organization_id                                                                  organization_id

BOM_BILL_OF_MATERIALS (BBOM)                       BOM_INVENTORY_COMPONENTS (BIC)

bill_sequence_id                                                                bill_sequence_id

BOM_INVENTORY_COMPONENTS (BIC)               MTL_SYSTEM_ITEMS_B (MSIB)

component_item_id                                                           inventory_item_id

BOM_OPERATIONAL_ROUTINGS(BOR)                  BOM_OPERATION_SEQUENCES(B0S)

routing_sequence_id                                                          routing_sequence_id

BOM_OPERATION_SEQUENCES (BOS)                   BOM_STANDARD_OPERATIONS (BSO)

standard_operation_id                                                       standard_operation_id

BOM_OPEARATION_SEQUENCES(BOS)                  BOM_DEPARTMENTS (BD)

department_id                                                                   department_id

BOM_DEPARTMENTS (BD)                                        BOM_DEPARTMENT_CLASSES

department_id                                                                   department_id

operation_sequence_id                                                      operation_sequence_id

BOM_OPERATION_RESOURCE (BORE)                   BOM_RESOURCES (BR)

resource_id                                                                       resource_id

BOM_DEPARMTENTS (BD)                                         BOM_RESOURCES (BR)

organization_id                                                                  organization_id 

2) JOINS IN INV :

MTL_SYSTEM_ITEMS_B (MSIB)                                 MTL_PARAMETERS (MP)

organization_id                                                                   organization_id

MTL_MATERIAL_TRANSACTION (MMT)                 MTL_TRANSACTION_TYPES (MTT)

transaction_type_id                                                            transaction_type_id

MTL_MATERIAL_TRANSACTION (MMT)                 MTL_ITEM_CATEGORIES (MIC)

organization_id                                                                  organization_id

inventory_item_id                                                              inventory_item_id

MTL_MATERIAL_TRANSACTION (MMT)                MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

MTL_TRANSACTION_TYPES (MTT)                         MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

MTL_ITEM_REVISIONS(MIR)                                     MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

organization_id                                                                  organization_id

CST_ITEM_COSTS(CIC)                                              MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

organization_id                                                                  organization_id

MTL_ITEM_CATEGORIES (MIC)                                MTL_CATEGORIES (MC)

category_id                                                                       category_id

JOINS IN INV WITH OTHER MODULES :

MTL_SYSTEM_ITEMS_B (MSIB)                                 RCV_TRANSACTIONS

organization_id                                                                  organization_id

MTL_SYSTEM_ITEMS_B (MSIB)                                 HR_EMPLOYEES

buyer_id                                                                            employee_id

3) JOINS IN WIP :

WIP_ENTITIES (WE)                                                     WIP_TRANSACTIONS (WT)

organization_id                                                                  organization_id

wip_entity_id                                                                     wip_entity_id

WIP_TRANSACTION_ACCOUNTS (WTA)                WIP_TRANSACTIONS (WT)

transaction_id                                                                    transaction_id

WIP_LINES (WL)                                                           WIP_TRANSACTIONS(WT)

line_id                                                                                line_id

WIP_DISCRETE_JOBS (WDJ)                                       WIP_ENTITIES (WE)

wip_entity_id                                                                      wip_entity_id

WIP_ENTITIES (WE)                                                       WIP_OPERATION_RESOURCES (WOR)

wip_entity_id                                                                       wip_entity_id

WIP_ENTITITES (WE)                                                     WIP_REQUIREMENT_OPERATIONS

organization_id                                                                    organization_id

WIP_ENTITIES                                                                 WIP_OPERATIONS

wip_entity_id                                                                       wip_entity_id

organization_id                                                                    organization_id

JOINS IN WIP WITH OTHER MODULES

WIP_TRANSACTION_ACCOUNTS (WTA)               PO_HEADERS_ALL (POH)

po_header_id                                                                   po_header_id

CST_COST_ELEMENTS (CSE)                                   WIP_TRANSACTION (WT)

cst_element_id                                                                 cst_element_id

WIP_OPERATIONS (WO)                                            BOM_DEPARTMENTS (BD)

department_id                                                                  department_id

WIP_ENTITIES (WE)                                                     MTL_SYSTEM_ITEMS_B (MSIB)

organization_id                                                                  organization_id

primary_item_id                                                                inventory_item_id

WIP_OPERATION_RESOURCES (WOR)                    BOM_RESOURCES (BR)

resource_id                                                                       resource_id