Thursday, July 30, 2015

Oracle XML Publisher Commands

Feature
Command
Choose
<?choose:?><?when:ABSENCE_DAYS=1?> 
(Any logo for EX)  XXXXX.jpg
<?end when?><?when:ABSENCE_DAYS=2?>
(Any Word for EX)    2 Days   
<?end when?><?end choose?>


If (Change field color)
<?ABSENCE_DAYS?><?if:ABSENCE_DAYS=1?>  <xsl:attribute  xdofo:ctx="block" name="background-color">red  </xsl:attribute>  <?end if?>
If (Change Record but this code in Key)
<?if@row:ABSENCE_DAYS=1?><xsl:attribute xdofo:ctx="incontext" name="background-color">green</xsl:attribute><?end if?>
If-Then-Else
<?xdofx:if ABSENCE_DAYS = 1 then 'x' else 'VV'end if?>


Column Format
<?if@column:ORGANIZATION_NAME="Access Network Development"?>
Column Name
<?end if?>


Variable (set & get)
<?xdoxslt:set_variable($_XDOCTX, 'x', 1)?>  <?xdoxslt:get_variable($_XDOCTX, 'x')?>
Add 10 to Variable x (code in Key)
<?xdoxslt:set_variable($_XDOCTX, 'x', xdoxslt:get_variable($_XDOCTX, 'x')+10)?><?xdoxslt:get_variable($_XDOCTX, 'x')?>  


Page break
<?split-by-page-break:?>


Header & Footer
Header
<?start:body?>
                         Report Body
<?end body?>
Footer

In header <?call:customheader?>
In footer <?call:customfooter?>
In bage body
<?template: customheader?>
Here our header
<?end template ?>
<?template: customfooter?>
Here our footer
<?end template?>
Report Body


Page Total
<?ABSENCE_DAYS?><?add-page-total:XXCPX;'ABSENCE_DAYS'?>

In the footer to appear in every page
Sub Total Absence Days per page : <?show-page-total:XXCPX;'#,##0.00'?>


Run accumulative Total for column
Before the table define variable in the Key
<?xdoxslt:set_variable($_XDOCTX, 'RTotVar', 0)?>

Delete table End code and create new column the set in the Key
<?xdoxslt:set_variable($_XDOCTX, 'RTotVar', xdoxslt:get_variable($_XDOCTX, 'RTotVar') + ABSENCE_DAYS)?><?xdoxslt:get_variable($_XDOCTX, 'RTotVar')?>


Forward & Carry Forward
Before the table
<?init-page-total: EntAmt?>

In the table
<?ABSENCE_DAYS?><?add-page-total:EntAmt;ABSENCE_DAYS?>

After the table
<?end-page-total:EntAmt?>

In the Header in the Key code
<xdofo:inline-total display-condition="exceptfirst" name="EntAmt">Brought Forward: <xdofo:show-brought-forward name="EntAmt" format="99G999G999D00"/></xdofo:inline-total>

In the Footer
1-      Get page total
<?show-page-total:EntAmt;"99G999G999D00"?>

2-      Get Carried forward in the Key code
<xdofo:inline-total display-condition="exceptlast" name="EntAmt">Carried Forward:   <xdofo:show-carry-forward name="EntAmt" format="99G999G999D00"/></xdofo:inline-total>
SQL to test
SELECT   paf.full_name, paf.employee_number, org.NAME organization_name,
         l.c_type_desc, l.date_start, l.date_end, l.absence_days
    FROM per_absence_attendances_v l,
         per_all_people_f paf,
         per_all_assignments_f ass,
         hr_all_organization_units org
   WHERE l.person_id = paf.person_id
     AND ass.person_id = paf.person_id
     AND ass.organization_id = org.organization_id
     AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
     AND SYSDATE BETWEEN ass.effective_start_date AND ass.effective_end_date
     AND TO_CHAR (l.date_start, 'MON') = 'DEC'
     AND l.date_end IS NOT NULL
ORDER BY employee_number, start_date DESC

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect