XMLP: How to Display Leading Zeros in XMLP Report – Excel Output

Microsoft Excel has a tendency to display number format based columns with no prefix of ZEROs. For example, if there is a value ’007′ excel displays it as ’7′, excel display it as ’007′ only if the column is set in text format. You can see the difference in the below screenshot

Please go through the below URL for an example to generate an XMLP report:

In the example mentioned in the above URL you could see the output as shown in the below screenshot

But if you observe the data in the emp table, you could see three rows have leading zeros for employee number(Please note, I have updated the data of seeded emp table with prefix of 00 and also changed the data type of empno column to varchar2(6) for the sake of example.)

As the output is of excel type the empno column is considered as Number column (as the entire column consists of number format data) and the leading zeros are removed in display.

We have multiple ways to resolve this, I have listed them below.

Method 1

1. Open the RTF Template in MS Word.
2. Go to Data -> Load XML Data.

3. Once the data is loaded successfully, double click on that field.

4. Under field properties window set the field formatting type as “Regular Text” and set the check box “Force LTR”.

After following the above said steps, save the template and preview the output which is as shown below

Method 2

Add Ctrl+Shift+Space after/before emplyee number on template to create a non-breaking space. The non-breakable space converts the number column to string column

Disadvantage of this method is that the non-breakable space is visible in the output as well, you can see in the screenshot below

Method 3

This is the best method I have found, In this method we need to use an equal-to symbol before the field and enclose the field in double quotes for example:- =”ENUM”

This works only in excel however it will allow you to cut and paste (ie to use the value to search in Oracle) and also to do vlookups.

The theory is that excel will concatenate the values together because it has quotes around it, it will treat it as a string rather than simply a value.

For any other formats this method will not work.

You can see in the above screenshot that Enum column values are turned to blue color as the content is explicit converted to text format from number format.

Hope this article is useful for those who are in need to show leading zeros for number columns in XMLP reports. If you have any best solution, please leave a comment and share with the readers.

Excel Output From BI Publisher or XML Publisher is Trimming Leading Zeros [ID 417811.1]  0xA0

DB: Generate Trace Files for Concurrent Programs

The main use of enabling trace for a concurrent program comes during performance tuning.

By examining a trace file, we come to know which query/queries is/are taking the longest

time to execute, there by letting us to concentrate on tuning them in order to improve the

overall performance of the program.

The following is an illustration of how to Enable and View a trace file for a Concurrent  Program.

Navigation: Application Developer–>Concurrent–>Program 

Check the Enable Trace Check box. After that go to that particular Responsibility and run the Concurrent Program. 

Check that the Concurrent Program has been completed successfully. 

The trace file by default is post fixed with oracle Process_id which helps us to identify which trace file belongs to which concurrent request. The below SQL Query returns the process_id of the concurrent request:

Select oracle_process_id from fnd_concurrent_requests where request_id=’2768335′

(This query displays Process Id)

The path to the trace file can be found in 2 ways

1.Run the below query in the database: 

1	SELECT * FROM V$PARAMETER WHERE NAME='user_dump_dest'

(This Query displays the path of trace file)

2. Login As sysadmin

Navigation> OAM->OAM Setup->Site MAP->Monitoring->System Configuration-> Database Init.ora Setting.

Check out the value for Parameter user_dump_dest which is nothing but the trace file location.

The Trace File generated will not be in the readable format. We have to use TKPROF utility to convert the file into a readable format.

Run the below tkprof command at the command prompt.

TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

A readable file will be generated from the original trace file which can be further

analyzed to improve the performance. This file has the information about the

parsing, execution and fetch times of various queries used in the program.

API: DBMS_CRYPTO: encrypt and decrypt data

Encrypt or decrypt, we will need a key. This key will be stored in a table which can be accessed only by the authorized personnel.

In the below examples, the key has been hardcoded

dbms_crypto example to encrypt information

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Declare   l_key varchar2(2000) := '1234567890123456'; l_mod number := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5; l_enc raw (2000); begin   l_enc := dbms_crypto.encrypt ( UTL_I18N.STRING_TO_RAW (p_in_val, 'AL32UTF8'), l_mod, UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8') );   end;

dbms_crypto example to decrypt information

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Declare   l_key varchar2(2000) := '1234567890123456'; lv_in_val raw (2000) := hextoraw(p_in_val); l_mod number := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5; l_dec raw (2000);   begin   <code> l_dec := dbms_crypto.decrypt ( lv_in_val, l_mod, UTL_I18N.STRING_TO_RAW (l_key, 'AL32UTF8') ); dbms_output.put_line ('Decrypted='||utl_i18n.raw_to_char(l_dec)); end;

SQL: External Banks in R12

Customer & Supplier Banks(External Banks) Technical Information.................

 

 

Suppliers and their Bank Details in Oracle Apps R12 

 

In this post, I tried to show the relationship between Important Supplier tables, TCA Tables,Payment Tables with the help of a query. I hope it will be useful.

Table Introduction:

Ø  IBY_EXTERNAL_PAYEES_ALL :  supplier information and customer information

Ø  IBY_EXT_BANK_ACCOUNTS : external bank account information

Ø  IBY_PMT_INSTR_USES_ALL : Payment instruments assignments

Ø  AP_SUPPLIERS : Stores Supplier Information

Ø  AP_SUPPLIERS_SITES_ALL : Stores Supplier site Information     

Query:               

SELECT                           

             /*Supplier Information*/           

              aps.segment1                       oracle_supplier_number

             ,aps.vendor_id

             ,aps.vendor_name                    supplier_name

             ,aps.party_id                       supplier_party_id

             ,iepa.remit_advice_fax              remit_advice_fax

             ,iepa.remit_advice_email            remit_advice_email

             /* Supplier Site Information */

             ,assa.vendor_site_id

             ,assa.party_site_id                 supplier_party_site_id

             ,assa.vendor_site_code              vendor_site_code

             ,assa.pay_site_flag                 pay_site_flag

             ,assa.purchasing_site_flag          purchasing_site_flag

             ,assa.rfq_only_site_flag            rfq_only_site_flag

             /* Bank Information*/

             ,ieba.ext_bank_account_id

             ,hp.party_name                      Bank_party_name

             ,ieba.bank_account_num              bank_account_num

             ,ieba.bank_account_name             bank_account_name

             ,ieba.country_code                  bank_acct_country_code

             ,ieba.currency_code                 bank_acct_currency_code

             /* Bank Address */

             ,hp.address1                        bank_address_line1

             ,hp.address2                        bank_address_line2

             ,hp.address3                        bank_address_line3

             ,hp.city                            bank_address_city

             ,hp.state                           bank_address_state

             ,hp.postal_code                     bank_address_zip

             ,hp.country                         bank_address_country

             /* Bank Branch Address */

             ,hp1.address1                       branch_address_line1

             ,hp1.address2                       branch_address_line2

             ,hp1.address3                       branch_address_line3

             ,hp1.city                           branch_address_city

             ,hp1.state                          branch_address_state

             ,hp1.postal_code                    branch_address_zip

             ,hp1.country                        branch_address_country

FROM          ap_supplier_sites_all              assa

             ,hz_parties                         hp           

             ,iby_ext_bank_accounts              ieba

             ,iby_external_payees_all            iepa

             ,iby_pmt_instr_uses_all             ipiua            

             ,ap_suppliers                       aps

             ,hz_parties                         hp1           

WHERE        assa.vendor_site_id         =      iepa.supplier_site_id

AND          hp.party_id                 =      ieba.bank_id

AND          ipiua.instrument_id         =      ieba.ext_bank_account_id

AND          ipiua.ext_pmt_party_id      =      iepa.ext_payee_id

AND          assa.vendor_id              =      aps.vendor_id

AND          ieba.branch_id              =      hp1.party_id

AND          ipiua.instrument_type       =      'BANKACCOUNT'

AND          ipiua.payment_flow          =      'DISBURSEMENTS'

AND          ipiua.order_of_preference   =      1;

 

Suppliers and their Bank Details in Oracle Apps R12

 

Let us go step by step and finally we can see the consolidated Query. All these queries were tested in R12.1.1 instance.

Query to Fetch Customer Data:

SELECT

      cust.party_name customer_name

    , cust_acct.cust_account_id

    , cust_acct.account_number

    , cust_uses.site_use_code

    , cust_loc.address1

    , cust_loc.address2

    , cust_loc.address3

    , cust_loc.address4

    , cust_loc.city

    , cust_loc.postal_code

FROM

      hz_parties             cust

    , hz_cust_accounts       cust_acct

    , hz_cust_acct_sites_all cust_site

    , hz_party_sites         party_site

    , hz_cust_site_uses_all  cust_uses

    , hz_locations           cust_loc

WHERE cust.party_id               = cust_acct.party_id

AND   cust_acct.cust_account_id   = cust_site.cust_account_id

AND   cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id

AND   cust_site.party_site_id     = party_site.party_site_id

AND   party_site.location_id      = cust_loc.location_id

AND   cust.party_name             like '%&party_name%';

Query to Find the Bank Account id based on Customer Info

SELECT

    account.ext_bank_account_id -- Link to Bank and Branch Information

   ,acc_instr.instrument_id

   ,acc_instr.ext_pmt_party_id

   ,ext_payer.ext_payer_id

   ,ext_payer.cust_account_id  -- Link to Cust Account Info

   ,ext_payer.acct_site_use_id

FROM

    iby_ext_bank_accounts   account

  , iby_pmt_instr_uses_all  acc_instr

  , iby_external_payers_all ext_payer 

WHERE 1= 1

AND account.ext_bank_account_id = acc_instr.instrument_id

AND acc_instr.ext_pmt_party_id  = ext_payer.ext_payer_id

AND ext_payer.cust_account_id   = '&id_from_previus_query';

Query to Find the Bank And Branch Information based on previous Query

SELECT

     cust.party_name                   customer_Party_name

    ,cust.party_id                     customer_party_id

    ,bank.party_name                   bank_name

    ,bank_prof.home_country   

    ,account.bank_account_num

    ,account.bank_account_name

    ,branch.party_name                 branch_name

    ,branch_prof.bank_or_branch_number branch_number

FROM hz_parties               bank

   , hz_relationships         rel

   , hz_parties               branch

   , hz_organization_profiles bank_prof

   , hz_organization_profiles branch_prof

   , iby_ext_bank_accounts    account

   , iby_account_owners       acc_owner

   ,hz_parties                cust

WHERE 1=1

AND bank.party_id                    = rel.object_id

and bank.party_type                  = rel.object_type

AND rel.object_table_name            = 'HZ_PARTIES'

AND rel.relationship_code            = 'BRANCH_OF'

AND rel.subject_id                   = branch.party_id

AND rel.subject_type                 = branch.party_type

AND rel.subject_table_name           = 'HZ_PARTIES'

AND bank.party_id                    = bank_prof.party_id

AND branch.party_id                  = branch_prof.party_id

AND bank.party_id                    = account.bank_id

AND branch.party_id                  = account.branch_id

AND account.ext_bank_account_id      = acc_owner.ext_bank_account_id

AND acc_owner.account_owner_party_id = cust.party_id

AND account.ext_bank_account_id      ='&ext_bank_accout_id_frm_previous_query';

Consolidated Query to fetch Customer info, Customer Site info, Bank Info and Bank Branch Info:

SELECT

     cust.party_name customer_name

   , cust_acct.account_number

   , cust_acct.cust_account_id

   , cust_uses.site_use_code

   , cust_loc.address1

   , cust_loc.address2

   , cust_loc.address3

   , cust_loc.address4

   , cust_loc.city

   , cust_loc.postal_code

   , bank.party_name                   bank_name

   , bank_prof.home_country

   , branch.party_name                 branch_name

   , branch_prof.bank_or_branch_number branch_number

   , account.bank_account_num

   , account.bank_account_name

FROM hz_parties               bank

   , hz_relationships         rel

   , hz_parties               branch

   , hz_organization_profiles bank_prof

   , hz_organization_profiles branch_prof

   , iby_ext_bank_accounts    account

   , iby_external_payers_all  ext_payer

   , iby_pmt_instr_uses_all   acc_instr

   , hz_parties               cust

   , hz_cust_accounts         cust_acct

   , hz_cust_acct_sites_all   cust_site

   , hz_cust_site_uses_all    cust_uses

   , hz_party_sites           party_site

   , hz_locations             cust_loc

WHERE 1=1

AND bank.party_id                    = rel.object_id

and bank.party_type                  = rel.object_type

AND rel.object_table_name            = 'HZ_PARTIES'

AND rel.relationship_code            = 'BRANCH_OF'

AND rel.subject_id                   = branch.party_id

AND rel.subject_type                 = branch.party_type

AND rel.subject_table_name           = 'HZ_PARTIES'

AND bank.party_id                    = bank_prof.party_id

AND branch.party_id                  = branch_prof.party_id

AND cust_acct.cust_account_id        = cust_site.cust_account_id

AND cust_site.cust_acct_site_id      = cust_uses.cust_acct_site_id

AND party_site.party_id              = cust.party_id

AND party_site.party_site_id         = cust_site.party_site_id

AND party_site.location_id           = cust_loc.location_id

AND cust.party_id                    = cust_acct.party_id

AND bank.party_id                    = account.bank_id

AND branch.party_id                  = account.branch_id

AND account.ext_bank_account_id      = acc_instr.instrument_id

AND acc_instr.ext_pmt_party_id       = ext_payer.ext_payer_id

AND ext_payer.cust_account_id        = cust_acct.cust_account_id

AND cust_uses.site_use_id            = ext_payer.acct_site_use_id

AND cust.party_name             like '%$Party_name%';