Wednesday, November 30, 2011

How to install MICR font using XML Publisher Administrator for Check Printing reports in Oracle EBS



Background for Check printing
Check printing from the E-Business Suite has historically required a third-party solution that can either query the necessary data and format the output or accept the existing flat text output generated by the provided check print programs. To then generate the checks, the format components had to be placed with pixel-perfect precision onto the page using the necessary MICR font to format the bank routing number and bank account of the payee. Alternatively customers could take the existing check print program and customize it to format the output correctly. These solutions are costly in terms of their implementation, their ongoing maintenance and their complexity. This all adds to the total cost of ownership for the E-Business Suite.
Oracle XML Publisher offers integrated functionality that allows customers to design their own check format using familiar desktop tools that can then be applied to a check print data source, whether that is the seeded Oracle Report or a custom solution. This format is easily maintainable and can include all the necessary components required in the final output to be printed onto blank check stock.
Taking this approach reduces the complexity of the systems surrounding the check generation and thereby reducing the total cost of ownership of the E-Business Suite.
 
Creating the MICR data placeholders
XML Publisher has the ability to use external fonts at runtime; as long as the font is accessible at runtime on the server. XML Publisher does not currently license a MICR font but a quick search on the web will find both freeware and commercial fonts that can be used.
Once you have the MICR font you will need to make it available to MS Word. Do this by installing it in the <<windows root>>/fonts directory. The font can now be accessed from Word. You can check this by looking for it in the font drop box.
To insert the MICR strings into your template, insert the appropriate number of form fields and map them to the XML data that will come in at runtime. Highlight these fields and set the font to the MICR font installed earlier.

Following White paper will provide a step by step process with screenshots explaining the processing of setting up Magnetic Ink Character Recognition(MICR) Font in XML Publisher Administrator Responsibility:

Monday, November 28, 2011

Oracle Time & Labor(OTL) - How to derive project, task, overriding approver from hxc_time_building_blocks

The table hxc_time_building_blocks stores the time related information. This is a hierarchial table which stores one record for timecard (identified by scope as TIMECARD), one record for every day in the timecard (identified by scope as DAY) and 1 record for every detail in the timecard (identified by scope as DETAIL).
The attributes are stored in hxc_time_attributes table. The tables hxc_time_building_blocks and hxc_time_attributes are linked through hxc_time_attribute_usages. i.e. hxc_time_attribute_usages contains the time building block id and time building block ovn of hxc_timecard_building_blocks and time_attribute_id of hxc_time_attributes table.
These attributes are Descriptive Flexfields with the following navigation:
Application Developer => Flexfields => Descriptive => Segments
Search for Title - OTL Information Types, Code is "PROJECTS"
Attribute1,Attribute2 and Attribute3 of hxc_time_attributes stores Project Id,Task id and Expenditure Type respectively, where as attribute category is 'PROJECTS'. Details are highlighted in the below diagram:
 
SELECT   pp.project_id, pp.segment1 project_number, pp.name project_name,
         pt.task_number, pt.task_name, pt.task_id, ppx.person_id resource_id,
         ppx.employee_number resource_empnum, ppx.full_name resource_name,
         ppxa.person_id override_approver_personid,
         ppxa.employee_number override_approver_empnum,
         ppxa.full_name ovverride_approver_name,
         tbbde.measure Hours,
         tbbde.unit_of_measure uom,
         tbbde.time_building_block_id detail_time_building_block_id,
         tbbda.time_building_block_id day_time_building_block_id,
         tbb.time_building_block_id parent_building_block_id
 FROM hxc_time_attributes ta,
         hxc_time_attribute_usages tau,
         hxc_time_building_blocks tbb,
         per_people_x ppxa,
         hxc_time_attributes tap,
         hxc_time_attribute_usages taup,
         hxc_time_building_blocks tbbde,
         hxc_time_building_blocks tbbda,
         per_people_x ppx,
         pa_projects_all pp,
         pa_tasks pt
   WHERE tbb.time_building_block_id = tau.time_building_block_id
     AND tbb.object_version_number = tau.time_building_block_ovn
     AND ta.time_attribute_id = tau.time_attribute_id
     AND ta.attribute10 IS NOT NULL
     AND ta.bld_blk_info_type_id = 10
     AND TO_NUMBER (ta.attribute10) = ppxa.person_id
     AND ta.attribute_category = 'APPROVAL'
     AND tbb.approval_style_id = 1
     AND (tbb.start_time) BETWEEN TO_DATE (:P_START_DATE)
                              AND TO_DATE (:P_END_DATE)
     AND tbb.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbb.time_building_block_id = time_building_block_id
                AND tbb.approval_style_id = approval_style_id)
     AND tbbde.time_building_block_id = taup.time_building_block_id
     AND tbbde.object_version_number = taup.time_building_block_ovn
     AND tbbde.parent_building_block_id = tbbda.time_building_block_id
     AND tbbde.parent_building_block_ovn = tbbda.object_version_number
     AND tbbda.parent_building_block_id = tbb.time_building_block_id
     AND tbbda.parent_building_block_ovn = tbb.object_version_number
     AND tap.time_attribute_id = taup.time_attribute_id
     AND tap.bld_blk_info_type_id = 13
     AND tbbde.resource_id = ppx.person_id
     AND tap.attribute_category = 'PROJECTS'
     AND TO_NUMBER (tap.attribute1) = pp.project_id
     AND TO_NUMBER (tap.attribute2) = pt.task_id
     AND pt.project_id = pp.project_id
     AND tbbde.approval_style_id = 1
     AND (tbbda.start_time) BETWEEN TO_DATE (:P_START_DATE)
                                AND TO_DATE (:P_END_DATE)
     AND tbbde.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbbde.time_building_block_id = time_building_block_id
                AND tbbde.approval_style_id = approval_style_id)
ORDER BY 1, 6;

How to derive Approvers, Overriding Approvers - Oracle Time & Labor(OTL) using SQL Statement

In Oracle Time & Labor(OTL) module, while entering the Timecard, the employee has an option to enter the Overriding Approver instead of the default approver, PERSON_ID of the overriding approver is stored in HXC_TIME_ATTRIBUTES.ATTRITE10
These attributes are Descriptive Flexfields with the following navigation:

Application Developer => Flexfields => Descriptive => Segments
Search for Title - OTL Information Types, Code is "APPROVAL"



Following query will list out all the Approvers for a given time period :-
Parameters: P_START_DATE  -- Timecard building block start date
                  P_END_DATE      -- Timecard building block end date

SELECT   tbs.timecard_id timecard_id, haps.application_period_id,
         haps.application_period_ovn, ppx.person_id approver_id,
         ppx.employee_number approver_empnum, ppx.full_name approver_name,
         ppxr.person_id resource_id, ppxr.employee_number resource_emp_num,
         ppxr.full_name resource_emp_name
 FROM hxc_app_period_summary haps,
         hxc_timecard_summary tbs,
         hxc.hxc_tc_ap_links htal,
         per_people_x ppx,
         per_people_x ppxr
 WHERE 1 = 1
     AND htal.timecard_id = tbs.timecard_id
     AND htal.application_period_id = haps.application_period_id
     AND haps.resource_id = tbs.resource_id
     AND haps.approver_id = ppx.person_id
     AND haps.start_time BETWEEN ppx.effective_start_date AND ppx.effective_end_date
     AND TRUNC (haps.start_time) = TRUNC (tbs.start_time)
     AND TRUNC (haps.stop_time) = TRUNC (tbs.stop_time)
     AND ppxr.person_id = tbs.resource_id
     AND TRUNC (tbs.start_time) BETWEEN TO_DATE (:p_start_date, 'DD-MON-RRRR')
                                    AND TO_DATE (:p_end_date, 'DD-MON-RRRR')
     AND haps.application_period_ovn =
                   (SELECT MAX (application_period_ovn)
                      FROM hxc_app_period_summary
                     WHERE application_period_id = haps.application_period_id)
ORDER BY 1, 2;


Following query will list out all the Overriding approvers for a given time period :-
Parameters: P_START_DATE  -- Timecard building block start date
                  P_END_DATE      -- Timecard building block end date
SELECT tau.time_building_block_id,
       ppx.person_id override_approver_personid,
       ppx.employee_number override_approver_empnum,
       ppx.full_name ovverride_approver_name,
       ppxr.person_id Resource_id,
       ppxr.EMPLOYEE_NUMBER Resource_Emp_Num,
       ppxr.full_name Resource_Emp_Name
  FROM hxc_time_attributes ta,
       hxc_time_attribute_usages tau,
       hxc_time_building_blocks tbb,
       hxc_bld_blk_info_types bbi,
       per_people_x ppx,
       per_people_x ppxr
 WHERE tbb.time_building_block_id = tau.time_building_block_id
   AND tbb.object_version_number = tau.time_building_block_ovn
   AND ta.time_attribute_id = tau.time_attribute_id
   AND ta.attribute10 IS NOT NULL
   AND bbi.bld_blk_info_type ='APPROVAL'
   AND bbi.bld_blk_info_type_id = ta.bld_blk_info_type_id
   AND ppxr.person_id = tbb.resource_id
   AND tbb.resource_type = 'PERSON'
  AND TO_NUMBER (ta.attribute10) = ppx.person_id
   AND ta.attribute_category = 'APPROVAL'
   AND tbb.approval_style_id = 1
   AND (tbb.start_time) BETWEEN TO_DATE (:p_start_date, 'DD-MON-RRRR')
                                    AND TO_DATE (:p_end_date, 'DD-MON-RRRR')
   AND tbb.object_version_number =
          (SELECT MAX (object_version_number)
             FROM hxc_time_building_blocks
            WHERE tbb.time_building_block_id = time_building_block_id
             AND tbb.approval_style_id = approval_style_id);

Oracle XML Bursting Control File - Sample

XML Format and group can be found from the View Requests Form/Diagnostics/View XML as follows:

Navigate XML Publisher Administrator/Home/Data Definitions
Click on "Create Data Definition" as highlighted


Under the XML Data Definition you will find the Bursting Control File option, Click on "Add File" button to upload the Bursting Control File as highlighted:-
 
Following is a sample XML Bursting control file:
 Parameters : P_SERVER_NAME,P_FROM_EMAIL,
                   P_TO_EMAIL,XML_GROUP_KEY,P_FILE_NAME,
                   P_PRINTER_NAME,P_NUM_OF_COPIES
Note: select is the XML file which can be derived from "Diagnostics/View XML" from the SRS View Requests Form
<?xml version="1.0" encoding="UTF-8" ?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
  <xapi:request select="/SAMPLEDOC/SAMPLE_GROUP">
<xapi:delivery>
<xapi:email server=<P_SERVER_NAME> port="25" from=<P_FROM_EMAIL>>
<xapi:message id="123" to=<P_TO_EMAIL> attachment="true" subject="Sample Document report">
Please find attached output of Sample Document report
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output-type="pdf" delivery="123">
<xapi:template type="rtf" location="xdo://<APP_SHORT_NAME>.<RTF Template Code>.en.US?getSource=true">
</xapi:template>
</xapi:document>
</xapi:request>
  <xapi:request select=
"/SAMPLEDOC/SAMPLE_GROUP">
  <xapi:delivery>
   <xapi:print id="PRINT" printer=<P_PRINTER_NAME> copies=<P_NUM_OF_COPIES> />
    </xapi:delivery>
     <xapi:document key=<XML_GROUP_KEY> output="/tmp/<P_FILE_NAME>.PDF" output-type="PDF" delivery="PRINT">
     <xapi:template type="rtf" location="xdo://<APP_SHORT_NAME>.<RTF Template Code>.en.US/?getSource=true"/>
      </xapi:document>
      </xapi:request> 
</xapi:requestset>


----------------------------------------------------------------------------------------------

If you need to ftp a concurrent program then you can use the following XML Bursting syntax:



<xapi:request select="/SAMPLEDOC/SAMPLE_GROUP">
  <xapi:delivery>
   <xapi:ftp id="FTPBOX" server="<Server IP>" user="<Unix User>" password="<Unix Password>" remote-directory="  </FTP_Sample_Directory>" remote-file="${File_Name}.xls">
   </xapi:ftp> 
  </xapi:delivery>
  <xapi:document output="<Output_File_Name>" output-type="<PDF/EXCEL>" delivery="123">  

  <xapi:template type="rtf" location="xdo://<APPL_SHORT_NAME>.<RTF Template Code>.en.US/?getSource=true"/>
   </xapi:document>   

</xapi:request>

----------------------------------------------------------------------------------------------
 Note:- XML Publisher data definition and template definition are stored in XDO tables. Following 
         are the details:-

Template Definition Source tables:

XDO_TEMPLATES_B
XDO_TEMPLATES_TL

Data Definition Source tables:


XDO_DS_DEFINITIONS_TL
XDO_DS_DEFINITIONS_B