How to Convert Oracle Discoverer reports to BI Publisher reports

 Below are the details about how we can convert the oracle discoverer reports to BI Publisher reports.

1. Login to Oracle Discoverer with the corresponding user details and request the Administrator to assign the required responsibilities needed to view the reports.

2.After the login page you will be directed to select responsibility page where you need to select the responsibility and click on continue button.

3.Click on browse button below to search the report

4.You will find the list of all the reports ,select any report and click on open button

5.You will find the parameter list if the report has parameters, select the values in the parameters and the report will run and produce output

6.Now click on Tools Menu -> Show SQL to get the query written behind the report.

7.Copy the SQL Query , for additional conditions or calculations there are tab pages under available items on top left, you can verify for all those conditions.

8.Now create data xml with the same parameter list as you have seen above and use the query to create select statement in your data xml for BI Publisher report. Add the additional conditions noted in above steps to this select statement and also you can add the conditions.

9.As the Oracle discoverers are excel outputs, you can either build excel layouts or PDF layouts based on the requirement.

Query to get the All Supplier, Site and Bank Details at Site Level with or without bank setup at site level

Below query is used to retrieve the supplier, site and bank details of all the suppliers  belonging to particular org_id, also to retrieve the supplier and site information if no bank account is setup for a particular site. 

If a site does not have any bank account setup then only supplier and site details are displayed.

SELECT * FROM (select  distinct 

    asp.vendor_name "VENDOR NAME",

    asp.segment1 "VENDOR NUMBER",

      /* Supplier Site Information */

    assa.vendor_site_code "SITE CODE" ,

     assa.pay_site_flag   "PAY SITE FLAG",

    assa.purchasing_site_flag "PURCHASE SITE FLAG",

    assa.rfq_only_site_flag   "RFQ SITE FLAG",

    assa.ADDRESS_LINE1 ,

    assa.ADDRESS_LINE2,

    assa.ADDRESS_LINE3,

    assa.city,

    assa.state,

    assa.zip,

    assa.country,

     /* Bank Information*/

    NULL "ACCOUNT NUMBER",

    NULL "ACCOUNT NAME",

    NULL "Bank Account Type",

    NULL ext_bank_account_id,

    NULL bank_account_num,

    NULL  ext_bank_account_iban_number,

    NULL bank_account_name,

    NULL bank_account_name_alt,

    NULL bank_account_num_electronic,

    NULL  bank_account_end_date,

   NULL bank_branch_name,

   NULL bank_branch_name_alt,

   NULL branch_number,

   NULL branch_party_id,

   NULL bank_name_alt,

   NULL bank_name,

   NULL bank_number

FROM ap_supplier_sites_all assa,

  apps.ap_suppliers asp

 WHERE assa.vendor_id              = asp.vendor_id

and asp.segment1 = NVL(:P_VENDOR_NUMBER,aps.segment1)

and assa.org_id=:P_ORG_ID

MINUS

SELECT

/*Supplier Information*/  

distinct 

    aps.vendor_name "VENDOR NAME",

    aps.segment1 "VENDOR NUMBER",

      /* Supplier Site Information */

    assa.vendor_site_code "SITE CODE"   ,

     assa.pay_site_flag   "PAY SITE FLAG",

    assa.purchasing_site_flag "PURCHASE SITE FLAG",

    assa.rfq_only_site_flag   "RFQ SITE FLAG",

    assa.ADDRESS_LINE1 ,

    assa.ADDRESS_LINE2,

    assa.ADDRESS_LINE3,

    assa.city,

    assa.state,

    assa.zip,

    assa.country,

     /* Bank Information*/

    ieb.bank_account_num "ACCOUNT NUMBER",

    ieb.bank_account_name "ACCOUNT NAME",

    ieb.bank_account_type "Bank Account Type",

    ieb.ext_bank_account_id,

    ieb.bank_account_num,

    ieb.iban ext_bank_account_iban_number,

    ieb.bank_account_name,

    ieb.bank_account_name_alt,

    ieb.bank_account_num_electronic,

    ieb.end_date bank_account_end_date,

    ext_branch.bank_branch_name,

    ext_branch.bank_branch_name_alt,

    ext_branch.branch_number,

    ext_branch.branch_party_id,

    ext_bank.bank_name_alt,

    ext_bank.bank_name,

    ext_bank.bank_number

FROM

    apps.hz_parties hzp,

    apps.ap_suppliers aps,

    apps.hz_party_sites site_supp,

    apps.ap_supplier_sites_all assa,

    apps.iby_external_payees_all iep,

    apps.iby_pmt_instr_uses_all ipi,

    apps.iby_ext_bank_accounts ieb,

    iby_ext_banks_v ext_bank,

    iby_ext_bank_branches_v ext_branch

WHERE

        hzp.party_id = aps.party_id

    AND        hzp.party_id = site_supp.party_id

    AND        site_supp.party_site_id = assa.party_site_id

    AND        assa.vendor_id = aps.vendor_id

    AND        aps.enabled_flag ='Y'

    AND      ( aps.end_date_active IS NULL OR aps.end_date_active > SYSDATE)

    AND        iep.payee_party_id = hzp.party_id

    AND        iep.party_site_id = site_supp.party_site_id

    AND        iep.supplier_site_id = assa.vendor_site_id

    AND        ieb.bank_id = ext_bank.bank_party_id

    AND        ieb.branch_id = ext_branch.branch_party_id

   AND        ipi.instrument_id = ieb.ext_bank_account_id

   AND        iep.ext_payee_id = ipi.ext_pmt_party_id  

   and       (assa.inactive_date is null OR assa.inactive_date >SYSDATE  )                             

    AND        assa.org_id = :P_ORG_ID

   AND    aps.segment1= NVL(:P_VENDOR_NUMBER,aps.segment1)

  ) order by 1

 

            Trekkenture Camp @Mathapalli 

                      Siddipet Telangana 

Share in Whatsapp 

On Saturday 13^th November we went to a Trekking camp at Mathappalli village near Siddipet Telangana district which is 80 km from Hyderabad organized by Trekkenture.

Excited for the camp we started from Kukatpally at 11:00 A.M. and ORR, we passed through the famous shamirpet Maisamma temple and reached Mathapalli Village at 1:00 P.M. 

Our Trekking camp is hosted at the large forest kind of space. The Trekking hosts Ajit Sir and Bharathi Mam welcomed us with tilak and flowers wishing for a nice stay over the weekend. Surprise for the kids they got to blast lot of balloons to cheer up the kids, followed by a casual introduction with each other. We all were super hungry after travel of almost 1 and half hour, the lunch provided was more than my expectation as generally in the camp we are more inclined to physical activities than munching on food, but to my surprise the lunch was simply superb with all the delicious items in the menu like paneer, Butter naan, rice, other items and yummy gulab jamun as dessert.

Now its turn to choose our tents which were laid in the lush green meadows and they made choosing tent easy by asking kids to select their tent. We dumped our luggage’s in the tents and they asked us to take a break of 1 hour or so and get ready for trek. We took all the required things like torch, water bottles, hand towels and whatever we need to feel comfortable for almost 3 hours to walk in the woods.

While walking we came across the rocks which looked just like burger with patty in between, kids just loved that burger rock and then we moved on to see some ancient beautifully intricate rock paintings which depicted their hunting scene. Even though this painting was done centuries back but still the pictures were intact. Next we headed deep into the thick trees and meadows to reach the beautiful trekking place, we started to hike the steep hill, kids and adults were like Oh My God can we do this, but we supported each other, had lot of fun while climbing, it was both adventurous and fun to climb with kids. Finally, we made it to the hill top. All the efforts we put in has gone just like a jiffy when we saw the breath taking view from top of the hill. This hill is surrounded by river body with cloudy winters added to this the reddish rays during sunset covered the sky and reflecting in the water made the scene magnificent. It was a breathtaking view for all of us, hold on your breath when I describe you next scene, in this weather what would it be if we crave for a cup of hot tea and snacks to munch on and make the scene unforgettable, don’t you think we are expecting too much, yes it too much but we are so lucky that the Trekkenture people arranged us with nice tea and snacks, kids were overjoyed to enjoy the snacks on hill top there are no words to describe their excitement.

 We returned back to camp from there freshened up thinking for a quite evening, but we did not knew that we had more exciting things to look for, any clues what it is , even we had no clue that we are going to get to see wonderful stargazing in the dark nights .The Trekkenture people got telescope for us , we got to see the Saturn , no wonder when we saw the rings of the Saturn through telescope it was wow moment for us  and for kids it was amazing to see the planets with their eyes and map it with what they saw in books all the time, next planet we got to see was our favourite moon , it looked so different from telescope and the moon was looking spectacular, magnificent and all the words which we can add to describe it, the view of the holes and close look at the moon just made it an experience of astronaut landing on the moon , I am not exaggerating but this is what happened to all of us.

So, we had some fun games where parents and kids were involved we enjoyed those new type of games a lot and then we had an impressive and lavish dinner again out of my expectations, which included roti’s, fries (to enjoy nice chilly weather) , to add to this weather nice rasam made the meal complete followed by double ka meetha sweet. After the dinner we just enjoyed gathering sharing our hobbies , likes with each other at the camp fire and the returned to tent for a good night sleep.

 Next day we woke up at 6:00 A.M and enjoyed the cold misty mornings with dew drops on the plants. We had nice tea, boost and biscuits and surprisingly kids are ready before us for morning walk in the nature, they were more excited than us, so we started off and this time our walk was through nice ponds, water bodies, lakes. When we were passing through greenery we saw many birds like cuckoo, lapwing, ibis, red mantled sallebag, hycleus, wasp, lady birds and many more birds, the Trekkenture hosts had good knowledge about all these birds and we also learnt about different types of trees and their uses from them, it was like nature and biology lessons to kids with fun. We also saw weavers bird nests there were many to amaze us. We also went deep into the woods to find an ancient laksmi narasimha swamy temple , lot of monkey to watch for. Kids enjoyed the morning trek a lot looking at blooming buds, flowers, honey bees , I can say that they were very close and in the lap of nature ,This experience is just rare sight for most of us living in concrete jungles but we are lucky to gather all this memories with us . 

By this time all the kids became good friends of each other and we are back to camp for morning breakfast . It was always exceeded expectations from Trekkenture camp .Good amount of walk through nature increased the appetitie of kids, generally this generation kids when coming to eating they are very choosy and reluctant, but this time it was surprising to all the parents that they had more than what they generally they eat for breakfast. The yummy spread also tickled their taste buds and we enjoyed it like a brunch. 

Finally to conclude we were loaded with good memories, friends and nature experiences, taking back with us for life time from this Trekkenture.

Please follow below link for more details to contact Trekkenture 

 https://telanganatoday.com/promise-of-weekend-adventure-for-both-kids-and-their-parents.