1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Useful R12 Scripts-2

Discussion in 'Oracle Apps Technical' started by Ramji, Jan 31, 2012.

  1. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    PREREQUISITES:

    To run using SQL Developer of SQL*Plus set the Server Output with a 1 million buffer as below
    From SQL Developer
    Select the Script SQL Worksheet Tab and the "DBMS Output" SubTab.
    Set Buffer Size to 1000000 or more
    Click on the "Enable DBMS Output" icon

    Or

    set serveroutput on size 1000000

    PURPOSE OF THE SCRIPT
    To Validate all setups are complete
    To Interact with Oracle Support and speed up resolution of Problems
    To Trouble shoot problems with the product
    To get an insight into the instance architecture.

    PARAMETER

    Payment system Short name (Only one parameter)

    THE SCRIPT

    Code (SQL):

    DECLARE
    l_bep VARCHAR2(3) := UPPER('&enter_bep_short_name');
    l_bepid NUMBER;
    l_bepname VARCHAR2(250);
    l_baseurl VARCHAR2(2000);
    l_ibyecapp VARCHAR2(2000);
    l_active VARCHAR2(1);
    l_type VARCHAR2(20);
    l_s1 VARCHAR2(1000);
    l_n1 NUMBER;
    l_i NUMBER;
    l_ii NUMBER;

    cursor bepschemes (bpid NUMBER) IS
    SELECT decode(pmtschemename, 'SSL','CREDITCARD',pmtschemename) sch
    FROM iby_pmtschemes
    WHERE bepid = bpid;

    cursor bepdefault (bpid NUMBER) IS
    SELECT instrtype, mpayeeid, payment_channel_code, fndcpt_user_profile_code, bep_account_id
    FROM iby_default_bep
    WHERE bepid = bpid;

    cursor bepformats (bpid NUMBER) IS
    SELECT a.payment_format_code, b.format_type_code, b.format_template_code, c.file_name,
    c.xdo_file_type, c.file_status, c.file_content_type,
    utl_raw.CAST_TO_VARCHAR2(dbms_lob.SUBSTR(c.FILE_DATA,100,dbms_lob.INSTR(c.FILE_DATA,utl_raw.cast_to_raw( '$Header')))) file_version
    FROM IBY_PMT_SYS_FORMATS a, IBY_FORMATS_B b, XDO_LOBS c
    WHERE format_code = payment_format_code
    AND payment_system_id = bpid
    AND trunc(nvl(a.inactive_date,sysdate)) >= trunc(sysdate)
    AND b.format_template_code = c.lob_code;

    cursor bepproto (bpid NUMBER) IS
    SELECT a.transmit_protocol_code, b.transmit_code_language, b.transmit_code_package||'.'||b.transmit_code_entry_point||'()' code,
    c.transmit_protocol_name
    FROM IBY_PMT_SYS_TRANSMISSIONS a, IBY_TRANSMIT_PROTOCOLS_B b, IBY_TRANSMIT_PROTOCOLS_TL c
    WHERE a.transmit_protocol_code = b.transmit_protocol_code
    AND c.transmit_protocol_code = b.transmit_protocol_code
    AND a.bepid = bpid;

    cursor bepprotconfigs (protcode varchar2) IS
    SELECT a.transmit_configuration_name, a.transmit_configuration_id,
    b.transmit_protocol_code, b.tunneling_trans_config_id
    FROM IBY_TRANSMIT_CONFIGS_TL a, IBY_TRANSMIT_CONFIGS_B b
    WHERE b.transmit_protocol_code = protcode
    AND a.transmit_configuration_id = b.transmit_configuration_id
    AND a.LANGUAGE = 'US';

    cursor cfgiddet (trcfgid NUMBER) IS
    SELECT a.transmit_configuration_name, b.transmit_protocol_code, c.transmit_protocol_name
    FROM IBY_TRANSMIT_CONFIGS_TL a, IBY_TRANSMIT_CONFIGS_B b, IBY_TRANSMIT_PROTOCOLS_TL c
    WHERE a.transmit_configuration_id = b.transmit_configuration_id
    AND a.transmit_configuration_id = trcfgid
    AND b.transmit_protocol_code = c.transmit_protocol_code
    AND a.LANGUAGE = 'US'
    AND c.LANGUAGE = 'US';

    cursor bepprotconfvals (trconfid NUMBER, protcode varchar2) IS
    SELECT a.transmit_parameter_code, decode(a.transmit_varchar2_value, NULL,
    decode(to_char(a.transmit_number_value), NULL, to_char(a.transmit_date_value), to_char(a.transmit_number_value)),
    a.transmit_varchar2_value) VALUE,
    b.transmit_parameter_name
    FROM IBY_TRANSMIT_VALUES a, IBY_TRANSMIT_PARAMETERS_TL b
    WHERE a.transmit_configuration_id = trconfid
    AND a.transmit_parameter_code = b.transmit_parameter_code
    AND b.transmit_protocol_code = protcode
    AND b.LANGUAGE = 'US';

    cursor bepk (bpid NUMBER) IS
    SELECT KEY, bep_account_id, decode(defaults,'Y','Default Account','') deft
    FROM iby_bepkeys
    WHERE bepid = bpid;

    cursor bepkv (bpid NUMBER, bpacct NUMBER) IS
    SELECT b.account_option_name, nvl(a.account_option_value, '<>') val
    FROM IBY_BEP_ACCT_OPT_VALS a, IBY_BEP_ACCT_OPT_NAME_VL b
    WHERE b.bepid = bpid
    AND a.bepid (+) = b.bepid
    AND a.bep_account_id (+) = bpacct
    AND b.account_option_code = a.account_option_code;

    cursor bepkrr (acid NUMBER, bpid NUMBER) IS
    SELECT a.payeeid, a.paymentmethodid, a.paymentmethodname, b.mpayeeid, a.priority,
    a.instr_type, a.bepkey, a.fndcpt_user_profile_code, a.payment_channel_code,
    b.name, b.activestatus, b.risk_enabled, b.threshold
    FROM IBY_ROUTINGINFO a, IBY_PAYEE b
    WHERE a.bepid = bpid
    AND a.bep_account_id = acid
    AND a.payeeid = b.payeeid
    ORDER BY a.priority;

    cursor payeeorg (pyid NUMBER) IS
    SELECT decode(c.org_type,'OPERATING_UNIT','OU',c.org_type) org_typ, c.org_id
    FROM IBY_FNDCPT_PAYEE_APPL c
    WHERE c.mpayeeid = pyid;

    cursor bepkrrc (pmtid NUMBER) IS
    SELECT parameter_code, decode(operation_code,'GE','>=','GT','>','EQ','=','LE','<=',operation_code) op_code, VALUE, entry_sequence
    FROM IBY_PMTMTHD_CONDITIONS
    WHERE paymentmethodid = pmtid
    ORDER BY entry_sequence ASC;

    cursor profiledetails (prid varchar2) IS
    SELECT 'CC' prof_type, a.user_cc_profile_code profile_code, a.sys_cc_profile_code sys_code, a.bep_account_id acct_id,
    a.online_auth_trans_config_id online_trcfg, a.settlement_trans_config_id settle_trcfg, a.query_trans_config_id qry_trcfg
    FROM IBY_FNDCPT_USER_CC_PF_B a
    WHERE a.user_cc_profile_code = prid
    AND trunc(nvl(a.inactive_date,sysdate)) >= trunc(sysdate)
    UNION ALL
    SELECT 'EFT' prof_type, b.user_eft_profile_code profile_code, b.sys_eft_profile_code sys_code, b.bep_account_id acct_id,
    b.verify_trans_config_id online_trcfg /*verify*/, funds_xfer_trans_config_id settle_trcfg /*xfr*/, b.query_trans_config_id qry_trcfg
    FROM IBY_FNDCPT_USER_EFT_PF_B b
    WHERE b.user_eft_profile_code = prid
    AND trunc(nvl(b.inactive_date,sysdate)) >= trunc(sysdate);

    cursor profdetcc(profcode varchar2) IS
    SELECT a.*
    FROM IBY_FNDCPT_SYS_CC_PF_B a
    WHERE a.sys_cc_profile_code = profcode
    AND trunc(nvl(a.inactive_date,sysdate)) >= trunc(sysdate);

    cursor profdeteft(profcode varchar2) IS
    SELECT a.*
    FROM IBY_FNDCPT_SYS_EFT_PF_B a
    WHERE a.sys_eft_profile_code = profcode
    AND trunc(nvl(a.inactive_date,sysdate)) >= trunc(sysdate);

    cursor profs IS
    SELECT f.user_profile_option_name uname, e.profile_option_name pname,
    decode(a.level_id, 10001,'Site',10002,'Application', 10003,'Responsibility',10004,'User') lvl,
    decode(a.level_id, 10001,'Site',10002,b.application_short_name,
    10003,c.responsibility_key,10004,d.user_name) lval,
    a.profile_option_value val
    FROM fnd_profile_option_values a,
    fnd_application b,
    fnd_responsibility c,
    fnd_user d,
    fnd_profile_options e,
    fnd_profile_options_tl f
    WHERE a.profile_option_id = e.profile_option_id
    AND a.level_value = b.application_id(+)
    AND a.level_value = c.responsibility_id(+)
    AND a.level_value = d.user_id(+)
    AND (d.user_name IS NOT NULL OR a.level_id <> 10004)
    AND (c.application_id IS NOT NULL OR a.level_id <> 10003)
    AND (c.application_id IS NOT NULL OR a.level_id <> 10002)
    AND e.profile_option_name = f.profile_option_name
    AND f.LANGUAGE = 'US'
    AND (e.profile_option_name LIKE 'AFLOG%' OR
    e.profile_option_name LIKE 'IBY%' OR
    e.profile_option_name LIKE 'ICX%PAY%')
    ORDER BY e.profile_option_name;

    cursor concprgopts IS
    SELECT concurrent_program_name, execution_options
    FROM fnd_concurrent_programs
    WHERE concurrent_program_name LIKE 'IBY%'
    AND execution_options IS NOT NULL;

    cursor arch1 (bpid NUMBER) IS
    SELECT a.bep_account_id, b.fndcpt_user_profile_code
    FROM iby_bepkeys a,
    IBY_ROUTINGINFO b, IBY_PAYEE c
    WHERE a.bepid = bpid
    AND a.bepid = b.bepid
    AND b.bep_account_id = a.bep_account_id
    AND c.payeeid = b.payeeid
    ORDER BY b.priority;

    cursor arch2 IS
    SELECT host||'.'||DOMAIN hst FROM fnd_nodes WHERE support_cp = 'Y';

    BEGIN
    SELECT bepid, name, baseurl, activestatus, bep_type
    INTO l_bepid, l_bepname, l_baseurl, l_active, l_type
    FROM IBY_BEPINFO
    WHERE UPPER(suffix) = l_bep;
    dbms_output.put_line('#############################################');
    dbms_output.put_line('Information for '||l_bepname||' ('||l_bep||')');
    dbms_output.put_line('#############################################');
    dbms_output.put_line('BepID = '||l_bepid);
    dbms_output.put_line('BaseUrl = '||l_baseurl);
    SELECT DOMAIN INTO l_s1
    FROM fnd_nodes
    WHERE DOMAIN IS NOT NULL
    AND STATUS = 'Y'
    AND support_web = 'Y'
    AND rownum = 1;
    IF instr(UPPER(l_baseurl),UPPER(l_s1)) < 1 THEN
    dbms_output.put_line('Warning: Base URL not pointing to instance domain');
    END IF;
    IF nvl(l_active,'N') = 'N' THEN
    dbms_output.put_line('Fatal: BEP is not active');
    END IF;
    dbms_output.put_line('Type = '||l_type);
    l_s1 := '';
    FOR i IN bepschemes(l_bepid) LOOP
    l_s1 := l_s1 || i.sch || ' ';
    END LOOP;
    dbms_output.put_line('Configured Methods = '||l_s1);
    dbms_output.put_line('');
    dbms_output.put_line('===========================');
    dbms_output.put_line('1. Default BEP For:');
    dbms_output.put_line('===========================');
    FOR i IN bepdefault(l_bepid) LOOP
    dbms_output.put_line(' - Instrument = '||nvl(i.instrtype,'NULL'));
    dbms_output.put_line(' - Payee = '||nvl(to_char(i.mpayeeid),'NULL'));
    dbms_output.put_line(' - Payment Channel Code = '||nvl(i.payment_channel_code,'NULL'));
    dbms_output.put_line(' - Fund Capture Profile Code = '||nvl(i.fndcpt_user_profile_code,'NULL'));
    dbms_output.put_line(' - Account Id = '|| nvl(to_char(i.bep_account_id),'NULL'));
    END LOOP;
    dbms_output.put_line('');
    dbms_output.put_line('===========================');
    dbms_output.put_line('2. Supported Formats:');
    dbms_output.put_line('===========================');
    FOR i IN bepformats(l_bepid) LOOP
    dbms_output.put_line(' - Format Code = '||i.payment_format_code||' ['||i.format_type_code||']');
    dbms_output.put_line(' -- Template Code = '||i.format_template_code||' ['||nvl(substr(i.file_version,10,instr(i.file_version,' ',1,3)-9),i.file_name||' <>')||']');
    dbms_output.put_line(' --- File Type = '||i.xdo_file_type ||', Content Type = '||i.file_content_type||' and Status = '||i.file_status);
    END LOOP;
    dbms_output.put_line('');
    dbms_output.put_line('===========================');
    dbms_output.put_line('3. Transmission Protocols:');
    dbms_output.put_line('===========================');
    FOR i IN bepproto(l_bepid) LOOP
    dbms_output.put_line(' - Protocol Name = '||i.transmit_protocol_name||' ('||i.transmit_protocol_code||')');
    dbms_output.put_line(' -- ['||i.transmit_code_language||': '||i.code||' ]');
    END LOOP;
    dbms_output.put_line('');
    dbms_output.put_line('===================================');
    dbms_output.put_line('4. Configured Accounts and Options:');
    dbms_output.put_line('===================================');
    dbms_output.put_line('');
    l_i := 0; l_ii:= 0;
    FOR i IN bepk(l_bepid) LOOP
    l_i := l_i + 1;
    dbms_output.put_line(' ------------------------------------------------------');
    dbms_output.put_line(' 4.'||l_i||' Options/Details for Payment System Account Id '||i.bep_account_id||' '||i.deft);
    dbms_output.put_line(' ------------------------------------------------------');
    dbms_output.put_line(' - key = '||i.KEY);
    FOR j IN bepkv(l_bepid, i.bep_account_id) LOOP
    dbms_output.put_line(' -- '||j.account_option_name||' = '||j.val);
    END LOOP;
    dbms_output.put_line(' ');
    dbms_output.put_line(' - Routing Rules:');
    l_s1 := '';
    l_ii := 0;
    FOR k IN bepkrr (i.bep_account_id, l_bepid) LOOP
    l_ii := l_ii + 1;
    FOR m IN payeeorg(k.mpayeeid) LOOP
    l_s1 := l_s1 || m.org_id ||' ['||m.org_typ||'] ';
    END LOOP;
    dbms_output.put_line(' -- Rule '||k.paymentmethodname||' ('||k.paymentmethodid||') [payee = '||k.payeeid||']');
    dbms_output.put_line(' --- Instrument Type = '||k.instr_type);
    dbms_output.put_line(' --- Payment Channel = '||k.payment_channel_code);
    dbms_output.put_line(' --- Profile Code = '||k.fndcpt_user_profile_code);
    dbms_output.put_line(' --- Payee Org = '||l_s1);
    dbms_output.put_line(' --- Payee Options : Risk ='||k.risk_enabled||' Threshold = '||k.threshold||' Active = '||k.activestatus);
    dbms_output.put_line(' --- Routing conditions (if any):');
    FOR l IN bepkrrc (k.paymentmethodid) LOOP
    dbms_output.put_line(' ---- '||l.entry_sequence||'. '||l.parameter_code||' '||l.op_code||' '||l.VALUE);
    END LOOP;
    dbms_output.put_line(' ---------------------------------------------------------');
    dbms_output.put_line(' 4.'||l_i||'.'||l_ii||' Funds Capture Process Profile '||k.fndcpt_user_profile_code||' details:');
    dbms_output.put_line(' ---------------------------------------------------------');
    FOR l IN profiledetails (k.fndcpt_user_profile_code) LOOP
    IF l.prof_type = 'CC' THEN -- CC profile
    dbms_output.put_line(' - Profile Name = '||l.sys_code);
    FOR z IN profdetcc(l.sys_code) LOOP
    dbms_output.put_line(' -- Auth Format = '||z.online_auth_format_code);
    dbms_output.put_line(' -- Auth Proto = '||z.online_auth_trans_prtcl_code);
    dbms_output.put_line(' -- Auth ACK = '||z.online_auth_ack_rdr_code);
    dbms_output.put_line(' -- Settle Fmt = '||z.settlement_format_code);
    dbms_output.put_line(' -- Settle Proto= '||z.settlement_trans_prtcl_code);
    dbms_output.put_line(' -- Settle ACK = '||z.settlement_ack_rdr_code);
    dbms_output.put_line(' -- Query Fmt = '||z.query_format_code);
    dbms_output.put_line(' -- Query Proto = '||z.query_trans_prtcl_code);
    dbms_output.put_line(' -- Query ACK = '||z.query_ack_rdr_code);
    dbms_output.put_line(' -- Grp by Org = '||z.group_by_org);
    dbms_output.put_line(' -- Grp by LE = '||z.group_by_legal_entity);
    dbms_output.put_line(' -- Grp by BAcct= '||z.group_by_int_bank_account);
    dbms_output.put_line(' -- Grp by Curr = '||z.group_by_settlement_curr);
    dbms_output.put_line(' -- Grp by Date = '||z.group_by_settlement_date);
    dbms_output.put_line(' -- Lmt by Curr = '||z.limit_by_amt_curr);
    dbms_output.put_line(' -- Lmt by ExRt = '||z.limit_by_exch_rate_type);
    dbms_output.put_line(' -- Lmt by Set# = '||z.limit_by_settlement_num);
    END LOOP;
    dbms_output.put_line(' - Operation Type = Credit Card');
    dbms_output.put_line(' - Account Id = '||l.acct_id);
    l_s1 := 'not set';
    IF l.online_trcfg IS NOT NULL THEN
    SELECT transmit_configuration_name INTO l_s1
    FROM IBY_TRANSMIT_CONFIGS_TL WHERE transmit_configuration_id = l.online_trcfg AND LANGUAGE = 'US';
    END IF;
    dbms_output.put_line(' - Auth TransCfg = '||l_s1||' ('||l.online_trcfg||')');
    l_s1 := 'not set';
    IF l.settle_trcfg IS NOT NULL THEN
    SELECT transmit_configuration_name INTO l_s1
    FROM IBY_TRANSMIT_CONFIGS_TL WHERE transmit_configuration_id = l.settle_trcfg AND LANGUAGE = 'US';
    END IF;
    dbms_output.put_line(' - Settle TransCfg = '||l_s1||' ('||l.settle_trcfg||')');
    l_s1 := 'not set';
    IF l.qry_trcfg IS NOT NULL THEN
    SELECT transmit_configuration_name INTO l_s1
    FROM IBY_TRANSMIT_CONFIGS_TL WHERE transmit_configuration_id = l.qry_trcfg AND LANGUAGE = 'US';
    END IF;
    dbms_output.put_line(' - Query TransCfg = '||l_s1||' ('||l.qry_trcfg||')');
    ELSE -- EFT profile
    dbms_output.put_line(' - Profile Name = '||l.sys_code);
    FOR x IN profdeteft(l.sys_code) LOOP
    dbms_output.put_line(' -- Verif Format= '||x.verify_format_code);
    dbms_output.put_line(' -- Verif Proto = '||x.verify_trans_prtcl_code);
    dbms_output.put_line(' -- Verif ACK = '||x.verify_ack_rdr_code);
    dbms_output.put_line(' -- XFR Fmt = '||x.funds_xfer_format_code);
    dbms_output.put_line(' -- XFR Proto = '||x.funds_xfer_trans_prtcl_code);
    dbms_output.put_line(' -- XFR ACK = '||x.funds_xfer_ack_rdr_code);
    dbms_output.put_line(' -- Query Fmt = '||x.query_format_code);
    dbms_output.put_line(' -- Query Proto = '||x.query_trans_prtcl_code);
    dbms_output.put_line(' -- Query ACK = '||x.query_ack_rdr_code);
    dbms_output.put_line(' -- Verif Reqd = '||x.settle_require_vrfy_flag);
    dbms_output.put_line(' -- Grp by Org = '||x.group_by_org);
    dbms_output.put_line(' -- Grp by LE = '||x.group_by_legal_entity);
    dbms_output.put_line(' -- Grp by BAcct= '||x.group_by_int_bank_account);
    dbms_output.put_line(' -- Grp by Curr = '||x.group_by_settlement_curr);
    dbms_output.put_line(' -- Grp by Date = '||x.group_by_settlement_date);
    dbms_output.put_line(' -- Lmt by Curr = '||x.limit_by_amt_curr);
    dbms_output.put_line(' -- Lmt by ExRt = '||x.limit_by_exch_rate_type);
    dbms_output.put_line(' -- Lmt by Total= '||x.limit_by_total_amt);
    dbms_output.put_line(' -- Lmt by Set# = '||x.limit_by_settlement_num);
    END LOOP;
    dbms_output.put_line(' - Operation Type = EFT');
    dbms_output.put_line(' - Account Id = '||l.acct_id);
    l_s1 := 'not set';
    IF l.online_trcfg IS NOT NULL THEN
    SELECT transmit_configuration_name INTO l_s1
    FROM IBY_TRANSMIT_CONFIGS_TL WHERE transmit_configuration_id = l.online_trcfg AND LANGUAGE = 'US';
    END IF;
    dbms_output.put_line(' - Verify TransCfg = '||l_s1||' ('||l.online_trcfg||')');
    l_s1 := 'not set';
    IF l.settle_trcfg IS NOT NULL THEN
    SELECT transmit_configuration_name INTO l_s1
    FROM IBY_TRANSMIT_CONFIGS_TL WHERE transmit_configuration_id = l.settle_trcfg AND LANGUAGE = 'US';
    END IF;
    dbms_output.put_line(' - XFR TransCfg = '||l_s1||' ('||l.settle_trcfg||')');
    l_s1 := 'not set';
    IF l.qry_trcfg IS NOT NULL THEN
    SELECT transmit_configuration_name INTO l_s1
    FROM IBY_TRANSMIT_CONFIGS_TL WHERE transmit_configuration_id = l.qry_trcfg AND LANGUAGE = 'US';
    END IF;
    dbms_output.put_line(' - Query TransCfg = '||l_s1||' ('||l.qry_trcfg||')');
    END IF;
    END LOOP;
    END LOOP;
    END LOOP;
    dbms_output.put_line('');
    dbms_output.put_line('------------------------------------------------------------');
    dbms_output.put_line('5. All Transmission Configurations for supported BEP protocols:');
    dbms_output.put_line('------------------------------------------------------------');
    FOR i IN bepproto(l_bepid) LOOP
    --dbms_output.put_line(' - Protocol Name = '||i.transmit_protocol_name||' ('||i.transmit_protocol_code||') ['||i.transmit_code_language||': '||i.code||' ]');
    FOR j IN bepprotconfigs(i.transmit_protocol_code) LOOP
    dbms_output.put_line(' - Details for Configuration "'||j.transmit_configuration_name||'" ('||j.transmit_configuration_id||')');
    dbms_output.put_line(' -- Protocol = '||i.transmit_protocol_name ||' [ '||i.transmit_protocol_code||' ]');
    FOR k IN bepprotconfvals (j.transmit_configuration_id, i.transmit_protocol_code) LOOP
    dbms_output.put_line(' -- '||k.transmit_parameter_name||' ['||k.transmit_parameter_code||'] = '||k.VALUE);
    END LOOP;
    IF j.tunneling_trans_config_id IS NOT NULL THEN
    FOR k IN cfgiddet(j.tunneling_trans_config_id) LOOP
    dbms_output.put_line(' => Uses Tunnel Config "'||k.transmit_configuration_name||'" ('||j.tunneling_trans_config_id||') with protocol '||k.transmit_protocol_name||' ('||k.transmit_protocol_code||')');
    FOR m IN bepprotconfvals (j.tunneling_trans_config_id, k.transmit_protocol_code) LOOP
    dbms_output.put_line(' -- '||m.transmit_parameter_name||' ['||m.transmit_parameter_code||'] = '||m.VALUE);
    END LOOP;
    END LOOP;
    END IF;
    dbms_output.put_line('');
    END LOOP;
    dbms_output.put_line('');
    END LOOP;
    dbms_output.put_line('');
    dbms_output.put_line('------------------------------------------------------------');
    dbms_output.put_line('6. Profile Options, all levels :');
    dbms_output.put_line('------------------------------------------------------------');
    l_s1 := ' ';
    FOR i IN profs LOOP
    IF l_s1 <> i.uname THEN
    dbms_output.put_line(' -------------------');
    dbms_output.put_line(' - '||i.uname||' [ '||i.pname||' ]');
    l_s1 := i.uname;
    END IF;
    dbms_output.put_line(' -- '||i.lvl||' ('||i.lval||') = '||i.val);
    IF i.pname = 'IBY_ECAPP_URL' THEN
    l_ibyecapp := i.val;
    END IF;
    END LOOP;
    dbms_output.put_line('');
    dbms_output.put_line('------------------------------------------------------------');
    dbms_output.put_line('7. Concurrent program options (only non null) :');
    dbms_output.put_line('------------------------------------------------------------');
    FOR i IN concprgopts LOOP
    dbms_output.put_line(' - '||i.concurrent_program_name||' = '||i.execution_options);
    END LOOP;
    dbms_output.put_line('');
    dbms_output.put_line('------------------------------------------------------------');
    dbms_output.put_line('8. Connectivity architecture :');
    dbms_output.put_line('------------------------------------------------------------');
    dbms_output.put_line('');
    SELECT a.instance_name||'@'||a.host_name||' ['||a.version||']' INTO l_s1 FROM v$instance a;
    dbms_output.put_line('Database : '|| l_s1 );
    FOR i IN arch2 LOOP
    dbms_output.put_line('CP Tier : '|| i.hst );
    END LOOP;
    dbms_output.put_line(' +----> '||nvl(l_ibyecapp,'')||' [ engine ]');
    dbms_output.put_line(' +----> '||nvl(l_baseurl,'')||' [ bepservlet ]');
    BEGIN
    FOR i IN arch1(l_bepid) LOOP
    FOR j IN profiledetails(i.fndcpt_user_profile_code) LOOP
    SELECT tunneling_trans_config_id INTO l_n1 FROM IBY_TRANSMIT_CONFIGS_B WHERE transmit_configuration_id = j.online_trcfg;
    dbms_output.put_line(' +----> For BepAcct: '||i.bep_account_id||' Capture Profile: '||i.fndcpt_user_profile_code);
    IF l_n1 IS NOT NULL THEN
    SELECT transmit_varchar2_value INTO l_s1 FROM IBY_TRANSMIT_VALUES
    WHERE transmit_parameter_code = 'WEB_URL' AND transmit_configuration_id = l_n1;
    dbms_output.put_line(' Online Tunnels to BEP Through : '||nvl(l_s1,''));
    ELSE
    dbms_output.put_line(' Online Not Tunnelled Connects to BEP directly from [ bepservlet ] node');
    END IF;
    SELECT tunneling_trans_config_id INTO l_n1 FROM IBY_TRANSMIT_CONFIGS_B WHERE transmit_configuration_id = j.settle_trcfg;
    IF l_n1 IS NOT NULL THEN
    SELECT transmit_varchar2_value INTO l_s1 FROM IBY_TRANSMIT_VALUES
    WHERE transmit_parameter_code = 'WEB_URL' AND transmit_configuration_id = l_n1;
    dbms_output.put_line(' Settle Tunnels to BEP Through : '||nvl(l_s1,''));
    ELSE
    dbms_output.put_line(' Settle Not Tunnelled Connects directly to BEP from [ bepservlet ] node');
    END IF;
    END LOOP;
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(' +----> Dummy setup for AR base integration');
    END;
    dbms_output.put_line('');
    dbms_output.put_line('End Report');
    END;
    /
     
    Enjoy
     
    Sadik likes this.
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Please Indent the code for better readability, if possible.
     
    Ramji likes this.
  3. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    Dear Raj,

    Thank you for doing the hard bit. I am preparing training stuff for users and have a lot to do in the spare time I have. Hence while multitasking, I make it a point to ignore the fine tuning in an attempt to get as much stuff across as quickly as possible when the time is there. Time and tide wait for none and in this difficult market a nomad generally moves on the flip of a coin.

    Thanks anyway for sorting the script. Shall do my best but please accept my apologies if I miss in the melee.
    Best Regards,
    Ramji