Multiple sheet excel report template generation in Oracle BI Publisher 10g

Objective:

To create template for generating multiple sheet excel in Oracle BI Publisher 10g.

Solution:

Template Creation - EXCEL

Step 1: Excel contains the below template with Sheet1 and 2 as follows

Step 2: Go to Formulas => Name Manager – See the XDO call

Step 3: Go to Sheet2 (XDO_METADATA) – See the XSLT code

Step 4: Come to Sheet1 – See how the groupings were done

Note:  

Ø  Workout example screenshot shared below.  

Ø  Template has been attached separately with this component.

Output: Output will be multiple sheets.

By

Karkuvelraja T

Multiple sheet excel report in Oracle BI Publisher 10g using data template SQL Query

Objective:

To generate multiple excel sheet report from OBIP using data template SQL queries.

Scenario:

Customer needs multiple sheets excel report from OBIP. While developing it, developer came to know multiple dataset of SQL queries required to complete the report. Developer has to develop the report with single dataset of SQL query, Instead of using multiple dataset of SQL queries.

Solution:

To develop multiple sheets excel OBIP report using Data template SQL Queries.

Step 1: Create OBIP report

Step 2:  Create data template SQL Queries and place it into data model in OBIP.

Step 3: Data Template SQL Query.

Step 3: XML data generation.

Step 3: XML data generation.

Output: (Sheet I)

Output: (Sheet III)

By 

Karkuvelraja T

Data template in Oracle BI Publisher 10g

Objective:

To generate reports from OBIP using data template SQL queries.

Scenario:

Customer has given a requirement to develop the report in OBIP. While developing it, developer came to know two dataset of SQL queries required to complete the report. Developer has to develop the report with single dataset of SQL query, Instead of using two dataset of SQL queries.

Solution:

To develop OBIP report using Data template SQL Queries.

Step 1: Create OBIP report

Step 2:  Create data template SQL Queries and place it into data model in OBIP.

Step 3: Data Template SQL Query.

Step 4: XML data generation.

Step 5: Excel Template.

Note: By this method, we can call package or procedure inside the dataset.  It will reduce no. of dataset use in it.

By
Karkuvelraja T

Generate DDL source code using Oracle SQL

Objective:

To generate DDL source code using Oracle SQL.

Scenario:

Customer asks us to generate all the DDL source code for some database objects. So I just found the little piece of code to create DDL source.

Database Objects are, 

Ø  Sequence

Ø  Table

Ø  Index

Ø  View

Ø  Database Link

Ø  Materialized view

Ø  Function

Ø  Procedure

Ø  Package

Ø  Package Body

Solution:

Oracle has provided default package DBMS_METADATA. By using this we can get DDL source code.

By

Karkuvelraja T

Split by count excel report in Oracle BI Publisher 10g

Objective:

To create a template to split records in excel sheet in Oracle BI Publisher 10g.

Solution:

Template Creation - EXCEL

Step 1: Open the Excel. (See below image)

Step 2: Go to Formulas => Name Manager – See the XDO call

Step 3: Go to Sheet2 (XDO_METADATA) – See the XSLT code

Step 4: Come to Sheet1 – See how the groupings were done

Step 5: Create one XML file – with the below code, see how the sheet were split by count of records using XML

<?xml version="1.0" encoding="utf-8" ?>

<xsl:stylesheet version="2.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<ROWSET>

<xsl:variable name="var_size" select="50000" />

<xsl:for-each select="/ROWSET/ROW">

<xsl:variable name="var_pos" select="position()" />

<xsl:variable name="var_mod" select="$var_pos mod($var_size)" />

<xsl:if test="$var_mod = 1">

<xsl:variable name="var_groupNum" select="($var_pos - $var_mod) div

number($var_size) + 1" />

<xsl:element name="CountGroup">

<xsl:attribute name="name">

<xsl:value-of select="concat('Group', $var_groupNum)" />

</xsl:attribute>

<xsl:for-each select="/ROWSET/ROW[position() &gt; ($var_pos -1) and position() &lt; ($var_pos + $var_size)]">

<xsl:copy-of select="." />

</xsl:for-each>

</xsl:element>

</xsl:if>

</xsl:for-each>

</ROWSET>

</xsl:template>

</xsl:stylesheet>

Note:

Ø  Workout example screenshot shared below.  

Ø  Template has been attached separately with this component.

Ø  Output will be multiple sheets, which split by count of records.

Output: Output will be multiple sheets of excel file, which are split by count of records (50000).

By

Karkuvelraja T

Call Oracle package in Oracle BI Publisher 10g

Objective:

To call Oracle package in Oracle BI Publisher 10g.

Scenario:

Oracle BI Publisher is just a reporting tool. From which we can download excel, csv, xml, rtf, PDF reports. In which we can use only SQL statement not PL/SQL. But, we came across the scenario to call Oracle package in OBIP.  

Solution:

We can call the Oracle package in OBIP by Data template SQL.

Step 1: Crete OBIP report

Step 2: Create data template dataset using below SQL

<dataTemplate name="AIRReport" description="Industrial_MIS_Automation"

                  dataSourceRef="BNP_RPTLIVDB_APEXRPS" Version="1.0"

                          defaultPackage="pkg_sbfs_report_log">

                                                                <dataQuery>

                                                                                <sqlStatement name="STMT1">

                                                                                                <![CDATA[SELECT INDUS FROM DS_SBFS_MNTHLY_DATA_ANALYSIS]]>

                                                                                </sqlStatement>

                                                                </dataQuery>

        <dataTrigger name="beforeReportTrigger"

                source="pkg_sbfs_report_log.Report_log('138','START')"/>

                                                                <dataStructure>

                                                                                <group dataType="varchar2" name="G_STMT_1" source="STMT1">

                                                                                                <element dataType="varchar2" name="INDUS" value="INDUS"/>

                                                                                </group>

                                                                </dataStructure>

        <dataTrigger name="afterReportTrigger"

                source="pkg_sbfs_report_log.Report_log('138','END')"/>

                                                </dataTemplate>

By

Karkuvelraja T

Calculate PO Balances

-- Query to retrieve customer PO details

SELECT   obha.order_number, obha.sold_to_org_id, obha.cust_po_number,
         obha.header_id, obha.org_id,
         TO_DATE (obha.attribute16,'YYYY/MM/DD HH24:MI:SS') po_last_calc_date,
         obhe.start_date_active po_start_date,
         TO_CHAR (obhe.end_date_active, 'YYYY/MM/DD HH24:MI:SS') po_end_date,
         obla.ship_to_org_id, obla.line_id, obla.ordered_item,
         hca.account_number, hca.cust_account_id, hp.party_name
    FROM ont.oe_transaction_types_tl ottl,
         ont.oe_blanket_headers_all obha,
         oe_blanket_headers_ext obhe,
         oe_blanket_lines_all obla,
         oe_blanket_lines_ext oble,
         hz_cust_accounts hca,
         hz_parties hp
   WHERE 1 = 1
     AND ottl.transaction_type_id = obha.order_type_id
     AND obha.sold_to_org_id = hca.cust_account_id
     AND hp.party_id = hca.party_id
     AND obha.order_number = obhe.order_number
     AND obha.header_id = obla.header_id
     AND obla.line_id = oble.line_id
     AND ottl.NAME = 'CUST_PO_BSA'
     AND obha.cust_po_number = cust_po_i    -- Parameter 2
     AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (obhe.start_date_active),SYSDATE - 1)
                             AND NVL (TRUNC (obhe.end_date_active),SYSDATE + 1)
     AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (oble.start_date_active),SYSDATE - 1)
                             AND NVL (TRUNC (oble.end_date_active),SYSDATE + 1)
     AND ottl.LANGUAGE = 'US'
     AND obha.attribute2 = 'Open'                   --PO Status
     AND obha.attribute3 = 'Y'                      --Invoice PO
     AND hca.account_number = NVL (cust_i, hca.account_number)  -- Parameter 1
ORDER BY obha.order_number, obla.line_number;


-- Query to retrieve customer po details in Line level

SELECT   obha.order_number, obha.sold_to_org_id, obha.cust_po_number,
         obha.header_id, obha.org_id, obhe.start_date_active po_start_date,
         obhe.end_date_active po_end_date, hca.account_number,
         hcsua.site_use_id ship_to_org_id,
         TO_DATE (obha.attribute16,'YYYY/MM/DD HH24:MI:SS') po_last_calc_date
    FROM ont.oe_transaction_types_tl ottl,
         ont.oe_blanket_headers_all obha,
         oe_blanket_headers_ext obhe,
         hz_cust_accounts hca,
         hz_parties hp,
         hz_locations hl,
         hz_party_sites hps,
         hz_cust_acct_sites_all hcasa,
         hz_cust_site_uses_all hcsua
   WHERE 1 = 1
     AND ottl.transaction_type_id = obha.order_type_id
     AND obha.sold_to_org_id = hca.cust_account_id
     AND hp.party_id = hca.party_id
     AND hp.party_id = hps.party_id
     AND hl.location_id = hps.location_id
     AND hca.cust_account_id = hcasa.cust_account_id
     AND hps.party_site_id = hcasa.party_site_id
     AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
     AND hcsua.site_use_code = 'SHIP_TO'
     AND obha.order_number = obhe.order_number
     AND obha.cust_po_number = cust_po_i -- Parameter 2
     AND ottl.NAME = 'CUST_PO_BSA'
     AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (obhe.start_date_active), SYSDATE - 1)
                             AND NVL (TRUNC (obhe.end_date_active),SYSDATE + 1)
     AND ottl.LANGUAGE = 'US'
     AND obha.attribute2 = 'Open'                                  --PO Status
     AND obha.attribute3 = 'Y'                                    --Invoice PO
     AND hca.account_number = NVL (cust_acct_no_i, hca.account_number) -- Parameter 1
ORDER BY obha.order_number;




-- Query to retrieve party with invoice total amount

SELECT   party_name, account_number, rct.trx_number,
           NVL ((SELECT SUM (extended_amount)
                   FROM ra_customer_trx_lines_all
                  WHERE customer_trx_id = rct.customer_trx_id
                    AND line_type = 'LINE'),
                0
               )
         + NVL ((SELECT SUM (extended_amount)
                   FROM ra_customer_trx_lines_all
                  WHERE customer_trx_id = rct.customer_trx_id
                    AND line_type = 'TAX'),
                0
               ) inv_total_amt
    FROM hz_parties hzp,
         hz_cust_accounts hca,
         ra_customer_trx_all rct,
         ra_customer_trx_lines_all rctl,
         ra_cust_trx_line_gl_dist_all rctd,
         ra_cust_trx_types_all rctta
   WHERE hzp.party_id = hca.party_id
     AND hca.cust_account_id = rct.bill_to_customer_id
     AND rct.customer_trx_id = rctl.customer_trx_id
     AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
     AND rct.cust_trx_type_id = rctta.cust_trx_type_id
     AND hca.account_number = NVL (cust_i, hca.account_number)
     AND rct.ship_to_site_use_id =
                          NVL (ship_to_site_use_id_i, rct.ship_to_site_use_id)
     AND UPPER (LTRIM (RTRIM (rctta.description))) LIKE '%INV%'
     AND rct.trx_date >= GREATEST (trx_start_dt_i, d_last_run_date_i)
     AND rct.purchase_order = customer_po_i
     AND rct.complete_flag = 'Y'
GROUP BY party_name, account_number, rct.trx_number, rct.customer_trx_id;



-- This block is used to calculate the Utilized amount

loop  
    n_net_amount:=0;
    n_vat_amount:=0;
    n_gross_amount:=0;
    n_invoice_amt:=0;
    n_adj_amt:=0;
    n_crm_amt:=0;
    n_dbm_amt:=0;
    n_tot_ship_fulfilled_amt:=0;
    n_line_counter:=0;
    n_insert_yn_flag:='Y';
                                   
    --
    -- Calculate Utilized amount for customer PO
                                   
    for inv_cur in cur_inv_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_invoice_amt:= n_invoice_amt+nvl(inv_cur.inv_total_amt,0);
    end loop;
                                       
    for inv_cur in cur_adj_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_adj_amt:=    n_adj_amt+nvl(inv_cur.adj_total_amt,0);
    end loop;
                                       
    for inv_cur in cur_crm_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_crm_amt:=    n_crm_amt+nvl(inv_cur.crm_total_amt,0);
    end loop;
                                       
    for inv_cur in cur_dbm_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_dbm_amt:=    n_dbm_amt+nvl(inv_cur.dbm_total_amt,0);
    end loop;
                                       
    n_tot_ship_fulfilled_amt:=n_invoice_amt+n_adj_amt+n_crm_amt+n_dbm_amt;                                  
    n_tot_fulfilled_amt:=n_tot_fulfilled_amt+n_tot_ship_fulfilled_amt;
                                   
end loop;