Support Analyzers

Get Proactive with Oracle E-Business Suite - Product Support Analyzer Index (Doc ID 1545562.1)

Consolidated note for analyzer scripts

Debug:

BI Publisher - Troubleshooting Oracle Business Intelligence (XML) Publisher For The Oracle E-Business Suite (Doc ID 364547.1)

Table Registration API [AD_DD ]

Table Registration API [Refer Oracle Apps Developer Guide for details]
---------------------------
API: AD_DD package.

Used for custom [Only below]:
###################################
1. Flexfields
2. Oracle Alert
3. Oracle Web Applications Desktop Integrator
###################################

1. register those tables (and all of their columns) that will be used with above.
2. run the AD_DD procedures against the APPS schema.
3. You need not register views.
4. Check yourself that tables exists in custom schema [api does not verify]
5. To modiy, first delete registration, and then re register.

AD_DD.register_table
AD_DD.register_column
AD_DD.delete_table
AD_DD.delete_column

Here is an example of using the AD_DD package to register a flexfield table and its columns:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);

EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID',1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE',2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','SUMMARY_FLAG',8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG',9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16,'VARCHAR2', 60, 'Y', 'N');



=============

procedure register_table (p_appl_short_name in varchar2,
 p_tab_name in varchar2,
 p_tab_type in varchar2,
 p_next_extent in number default 512,
 p_pct_free in number default 10,
 p_pct_used in number default 70);

 procedure register_column (p_appl_short_name in varchar2,
 p_tab_name in varchar2,
 p_col_name in varchar2,
 p_col_seq in number,
 p_col_type in varchar2,
 p_col_width in number,
 p_nullable in varchar2,
 p_translate in varchar2,
 p_precision in number default null,
 p_scale in number default null);

 procedure delete_table (p_appl_short_name in varchar2, p_tab_name in varchar2);

 procedure delete_column (p_appl_short_name in varchar2,  p_tab_name in varchar2, p_col_name in varchar2);

=> p_appl_short_ name The application short name of the application that owns the table (usually your custom application).
=> p_tab_name The name of the table (in uppercase letters).
=> p_tab_type Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table (used only by Oracle EBusiness Suite products).
=> p_pct_free The percentage of space in each of the table's blocks reserved for future updates to the table (1-99). The sum of
=> p_pct_free and p_pct_used must be less than 100.
=> p_pct_used Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.
=> p_col_name The name of the column (in uppercase letters).
=> p_col_seq The sequence number of the column in the table (the order in which the column appears in the table definition).
=> p_col_type The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
=> p_col_width The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
=> p_nullable Use 'N' if the column is mandatory or 'Y' if the column allows null values.
=> p_translate Use 'Y' if the column values will be translated for an Oracle E-Business Suite product release (used only by Oracle EBusiness Suite products) or 'N' if the values are not
=> translated (most application columns).
=> p_next_extent The next extent size, in kilobytes. Do not include the 'K'.
=> p_precision The total number of digits in a number.
=> p_scale The number of digits to the right of the decimal point in a number.

PLSQL : Nesting blocks

Notice that I am using same variable name, however outer variable name can be accessed by using official.ln_email

begin <<official>> 
    declare
        ln_email varchar2(30):= 'abhishek.agrawal@official.com';
    begin
        --PERSONAL
        declare
            ln_email varchar2(30):= 'appsoracle.abhi@gmail.com';
        begin
            DBMS_OUTPUT.PUT_LINE('PERSONAL MAIL: '||LN_EMAIL);
            DBMS_OUTPUT.PUT_LINE('OFFICIAL MAIL: '||OFFICIAL.LN_EMAIL);
        end;
   
    end;
end official;


PERSONAL MAIL: appsoracle.abhi@gmail.com
OFFICIAL MAIL: abhishek.agrawal@official.com