Different type of xml tags in bi publisher

Not null:

<?if@column:G_MON1_QTY_MON1!=''?>

If not null

<?xdoxslt:ifelse(UNIT_PRICE!='',UNIT_PRICE,0)?>

If null then

<?xdoxslt:ifelse(UNIT_PRICE ='',UNIT_PRICE,0)?>

<?xdofx:if TOTAL_COST = '' then 0 end if?>

<?xdofx:if TOTAL_COST = '' then TOTAL_COST= 0 end if?>

If with < or > process

<?xdofx:if ITEM_COST <10 then 'John' else 'Smith' end if?>

write decode condition in xml tag

<?xdofx:Decode(tag_name ,'',0,tag_name?>

tamplate with matrix format

<?for-each-group:G_SUPPLIER;ITEM?>// Group name;column name //

cross table xml tag Row act as column process in xml tag
<?for-each-group@column:G_SUPPLIER;PO_CREATION_MONTH?> // group name;column name //

<?for-each-group@cell://G_BUDGET;./TASK_NAME?>

cross table xml tag Row act as column with other details

<?for-each-group@cell:current-group();PO_CREATION_MONTH?>


section command in xml tag:
<?for-each-group@section:G_1;./FK_REF_REGION_CODE?>

<?if@section:count(//G_1)=0?>No Data Found<?end if?>

<?end for-each-group?>


Detail Solution

 I am giving this solution for Standard Check Printing Report. Tree structure of data (Sample XML data is as follow).

<LIST_G_CHECKS>

    <G_CHECKS>  -- Top Most root -- Header

      <C_CHECK_NUMBER>21897</C_CHECK_NUMBER>

      <C_VENDOR_NUMBER>2205</C_VENDOR_NUMBER>

      <LIST_G_INVOICES>

        <G_INVOICES> -- Inner loop - Line Section

          <C_PAYMENT_NUMBER>1</C_PAYMENT_NUMBER>

          <C_INVOICE_NUMBER>ERS-20-SEP-06-243</C_INVOICE_NUMBER>

        </G_INVOICES>

        <G_INVOICES> -- Inner loop - Line Section

          <C_PAYMENT_NUMBER>2</C_PAYMENT_NUMBER>

          <C_INVOICE_NUMBER>ERS-20-SEP-06-244</C_INVOICE_NUMBER>

        </G_INVOICES>

      </LIST_G_INVOICES>

    </G_CHECKS>

</LIST_G_CHECKS>



Below is the step-step guide which I follow.



1) Open the Outermost for loop --  G_CHECKS

<?for-each@section:G_CHECKS?>



2) Declare Global Variable called ‘no_of_lines_per_page’  -- In this case I have fixed 40 lines per page.

<xsl:variable name="no_of_lines_per_page" select="number(40)"/>



3) Declare incontext variable for inner group (G_INVOICES), variable is called ‘inner_group’

<xsl:variable xdofo:ctx="incontext" name="inner_group” select=".//G_INVOICES"/>



4) Open the Inner Loop

   <?for-each:$inner_group?>



5) Before putting any elements with the help of current record pointer 'position()’, I am checking if the current position is modulizing with the no_of_lines_per_page equals zero or not. If it reaches the first record after modulizing then I will create local variable 'first_rec' and initialize it with '0'.

<?if:(position()-1) mod $no_of_lines_per_page=0?><xsl:variable name="first_rec" xdofo:ctx="incontext" select="position()"/>



Note : -- Above 3 steps ( 3,4,5) are created under ‘V_inner_group_And_V_First_rec’ form-field. Here there is limitation of Microsoft-word. We can enter upto 138 characters only in ‘Status’ field of ‘Add help text’ button.If you want to add more , you can do this by clicking on ‘Help Key’  which is adjacent to ‘Status’ tab.



6) If above condition holds true then we will iterate the inner loop.

<?for-each:$inner_group?>



7) I will check with the help of current record pointer 'position()' that the current record position is either greater than 'first_rec' i.e. the first record or less the 'no_of_lines_per_page' value set up earlier. If it is then show the record otherwise not otherwise it will not go in loop.

   <?if:position()>=$first_rec and position()<$first_rec+$no_of_lines_per_page?>



8) Here I am closing the inner for loop and if condition

   <?end if?><?end for-each?>



9) Here I am checking if no_of_lines of invoice is modulizing with the no_of_lines_per_page equals to zero or not ,and the same time I am checking if $first_rec+$no_of_lines_per_page is greater than no_of_lines of invoice or not. This is important step for filling the blank rows.

<?if:not(count($inner_group) mod $no_of_lines_per_page=0) and ($first_rec+$no_of_lines_per_page>count($inner_group))?>





10) Now I am calling sub-template recursively for filling the blank rows. While calling this template I am passing one parameter which is having value of no_of_rows to fill. Sub-template will have just one row table.

<xsl:call-template xdofo:ctx="inline" name="countdown"><xsl:with-param name="countdown" select="$no_of_lines_per_page - (count($inner_group) mod $no_of_lines_per_page)"/></xsl:call-template>



11) Sub-template declaration

   <xsl:template name="countdown">

   <xsl:param name="countdown"/><xsl:if test="$countdown"><xsl:call-template  

   name="countdown"><xsl:with-param name="countdown" select="$countdown - 1"/> 

   </xsl:call-template></xsl:if>

   </xsl:template>



12) I have created page break after the fixed number of rows have been displayed.

   <xsl:if xdofo:ctx="inblock" test="$first_rec+$no_of_lines_per_page<=count($inner_group)">

      <xsl:attribute name="break-before">page</xsl:attribute>

   </xsl:if>



13) Finally closing outer if and inner for loop and outer for loop.

   <?end if?><?end for-each?><?end for-each?>

You can use IF condition directly or using xdofx in RTF Template. Below are few examples

(?xdofx:if LENGTH(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/POH_CUSTOMER)=0 THEN 'DELIVER TO:'POD_REQUESTOR_NAME'('POD_QUANTITY_ORDERED')' END IF?)


(?xdofx:if LENGTH(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/POH_CUSTOMER) !=0 AND(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/POH_CUSTOMER)!=POD_REQUESTOR_NAME THEN 'DELIVER TO:'POD_REQUESTOR_NAME'('POD_QUANTITY_ORDERED')' END IF?)


(?if:string-length(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/LIST_G_CANCEL_RELEASE/CANCEL_RELEASE_DATE)!=0?)


(?xdofx:if AMOUNT > 1000 then ’Higher’
else
if AMOUNT < 1000 then ’Lower’
else
’Equal’
end if?>


Note: Replace symbol '(' with '<' and ')' with '>' for starting and ending Tags above

AP_INVOICES_PKG.GET_APPROVAL_STATUS

When I tried to find the AP invoice status from backend, I found the column name APPROVAL_STATUS_LOOKUP_CODE under the view name AP_INVOICE_V. Since it is org based view, setting the context is mandatory. Please find my script to set the context here.

Below query will give you the status,

SELECT invoice_num

      ,approval_status_lookup_code

FROM   ap_invoices_v

WHERE  invoice_num = 'DM080310';

When I further traced to understand the source of the view column, I found that there is no column in the AP_INVOICES_ALL table that stores the validation status. An API named AP_INVOICES_PKG.GET_APPROVAL_STATUS is used by the view to finding the status.

Below query will give you the usage of the mentioned api,

SELECT APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS

            (

             I.INVOICE_ID

            ,I.INVOICE_AMOUNT

            ,I.PAYMENT_STATUS_FLAG

            ,I.INVOICE_TYPE_LOOKUP_CODE

            ) Approval_Status

      ,invoice_num

FROM   AP_INVOICES I

WHERE  invoice_num = 'DM080310';

    

When I went one more step deeper, I got the below,

Invoice distributions are validated individually and the status is stored at the invoice distribution level.  This status is stored in AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG.

Valid values for the column are:

A - Validated (it used to be called Approved)

N or NULL - Never validated

T - Tested but not validated

The invoice header form derives the invoice validation status based on the following:

'Validated'

-          If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'

'Never Validated'

-          If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'

'Needs Revalidation'

-          If there are any rows in AP_HOLDS that do not have a release code.

-          If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.

-          If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed).

BIP Formatting

Adding formatting is easier in BIP.
It can be done manually or automated through menu.

How to add the BIP formatting manually,  here is an example.
Just add the logic and the formatting you needed.

How to add the BIP formatting through menu,  here is an example.

use the Menu and choose conditional formatting

You have to select the data filed and choose the menu option conditional format