Tuesday, May 20, 2014

Development Steps For Procedure, Reports,Xml publisher Report,SQLLoader,Inbound Interface,Outbound Interface

How To Develop Procedure:
Step1: To develeop a procedure
Step2: To compile the procedure in the database
Step3: To create the Concurrent Executable.
Step4: To create the Concurrent Program
Step5: To Attach Concurrent Program to the Request Group.
Step6: To submit the Concurrent Program
--> Standard Top -- Appl-Top Specific Responsibility--FND, PO, AP---etc.
--> Cutom Top -- xxGenpact , xxpcs---etc.
How To Develop reports (RDF):
Step1: To Develop the report or we create the report
Step2: To place the report in the server specific path or To Move the report in server
Step3: To Create the Concurrent Executable
Step4: To Create the Concurrent Program
Step5: To Attach the Concurrent Program to the Request Group
Step6: Submit the Concurrent Program
How To Develop XML Publisher Report:
Step1: To create a report with out Layout
Step2: Transfer the file to the server into a specific path(responsibility)
           EX: $xxql-Top/reports/us
Step3: To create a Concurrent Executable based on the report
Step4: To create a Concurrent Program based on Concurrent Executable and change the   
          OUTPUT Type=XML
Step5: To Attach the Request Group (To identify url frontend oracle application select * from
           icx_parameters)
Step6: We need to save the XML Output file
Step7: To create a rtf template (Rich Text Format) using the XML OutPut file.
Step8:  Login into Oracle Application XML Publisher Administrator and select Responsibility
           name
Step9: Create data definition.XML Publisher Administrator-->Home-->Data Definition
           Note: create data definition click on
            Name: Emp_DD        Code:XXEM        (c.p shortname)
             Application: AOL                Apply
Step10: Create a template.
        click on templatetab click on create template button
        Name:USER_EMP            Code:XXEM       
        Application: AOL                 Data definition: EMP_DD
        Template File
        * File: Emp.rtf            Browse           (Here rtf file)
        * Language : English         Apply
Step11  Run the same Concurrent Program (PDF Format by default)

How to Develop SQLLoader in APPs
Step1: Create table emp1 as select * from emp where 1=2 
           To create excel data  EX: empno ename sal job date to save "emp.csv"
           To create control file and save .ctl (Notepad)
       
Step2:  To move the files in server EX: D:\oracle\vis\apps\apps_st\appl\fnd\12.0.0\bin\"emp.csv"
       --> Data files(.ctl), Control files(.csv) place in bin dirctory
       --> Appl-Top-->fnd-->11.5.0-->bin  data-admin folder
Step3: To create Concurrent Executable
           Executable Method: SQL*Loader  Execution File Name:emp (ctl)
Step4: To create Concurrent Program
Step5: To Attach the Concurrnt Program in Request Group
Step6:  Submit the Concurrent Program
How to Develop Interface (Inbound):
Step1: Interface tables, Mandatory Columns
           po_headers_interface, po_lines_interface, po_distributions_interface
Step2: Base Tables
           po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all
Step3: concurrent Prgrom (Here submit the Standard Concurrent Program But what ever  
           resposibility develop we submit specific responsibility c.p
          Select* from fnd_concurrent_programs_vl where concurrent_program_name
          ='POXPOPDOI'
          import standard purchase orders, requisition import
Step4: Interface error tbale (Standard error table/ custom error table handling)
           select * from po_interface_errors
Step5: Validations (Custom-- procedures, packages,)
           How to identify in database objects(pkg,proc,func,view-----etc)
          select * from all_objects where object_name like 'PO_INT_PKG'
          operating_unit, vendor_id, vendor_site_id, ----etc
            To create Concurrent Executable & Concurrent Program & Attach c.p to Request Group
           & Submit the c.p
Step6: Generic Issues
       --> select * from po_headers_all where trunc(creation_date)=trunc(sysdate)
       --> select * from po_lines_interface where interface_header_id=1021357
       --> select * from po_distributions_interface where interface_header_id=1021358
Inbound Interface: It will be used to transfer the data from flat file to oracle application base  
                                  tables, before transferring the data from flat file we need to check whether 
                              data is valid or not.
  à We will develop the inbound interface to transfer the data from flat file to oracle apps base    
       tables
àWe are getting that flat file from the client.we will check the record whether it is valid or not
àStaging tables will be used to store the data from flat file by using SQL*loader
àInterface tables contains the data whether interface is running that will be used to check that validations.Once interface will be complete than interface table will be emty
àBase tables are exact tables whether tables will be stored and system will  retrieve the data from these tables  
How to develop Outbound Interface:
Step1: To Declare a file type variable
Step2: Open the file Using file variable with “WRITE” mode
Step3: Write the data into the file
Step4: Close the file.
Step5: To create the Concurrent Executable.
Step6: To Create the Concurrent Program
Step7: To Attach the Concurrent Program To Request Group.
Step8: To submit the Concurrent Program.
OUT BOUND INTERFACE: It will be used to transfer the data from oracle apps base tables to flat file by using UTL_FILE
àWe will develop the query and define the cursor
àWe will create the file by using UTL_FILE
àWe will insert the data into the flat file and we will close the file
àWe will makes these complete process as PL/SQL Stored Procedure, Concurrent Program
àWe will create the executable with executable method as PL/SQL stored procedure
àAnd create the concurrent program and attach parameters and incompatibility
àAnd attach concurrent program to request group
àAnd attach request group to responsibility
àAnd attach responsibility to user

Tables
àHow to identify multi-arg implement or not/ Enable/Disable?
SELECT MULTI_ORG_FLAG FROM FND_PRODUCT_GROUPS
SELECT * FROM FND_PRODUCT_GROUPS
SELECT * FROM HRFV_BUSINESS_GROUPS (BUSSINESS GROUP)
SELECT * FROM GL_SETS_OF_BOOKS (SET OF BOOKS)
SELECT * FROM HR_LEGAL_ENTITIES (LEGAL ENTITY)
SELECT * FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID
=888 (OPERATING UNITS)
SELECT * FROM ORG_ORGANIZATION_DEFINITIONS
SELECT * FROM MTL_SYSTEM_ITEMS_B
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1 =4428
SELECT * FROM PO_VENDORS WHERE VENDOR_ID =600
SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_SITE_ID =1414
àPO_HEADERS_V (VIEW DOESN’T DISPLAY IN RECORDS WITH TABLES BUT I  
   WANT TO VIEW RECORDS HOW?)
BEGIN
 FND_CLIENT_INFO.SET_ORG_CONTEXT (‘204’);
END;
SELECT * FROM PO_LINES_ALL WHERE PO HEADER_ID =304056;
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=304056
SELECT * FROM    MTL_SYSTEM_ITEMS_B WHERE
INVENTORY_ITEM_ID=149
SELECT *  FROM  PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=304070
SELECT*  FROM PO_DISTRIBUTIONS_ALL WHERE
LINE_LOCATION_ID=304056
SELECT * NEED_BY_DATE, A.*  FROM  PO_LINES_ALL A
SELECT *  FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1= 'AS54888'

SELECT *  FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM=14571
SELECT *  FROM RCV_SHIPMENT_LINES WHERE
SHIPMENT_HEADER_ID=59416
SELECT *  FROM RCV_TRANSACTIONS WHERE
SHIPMENT_HEADER_ID=59416
SELECT *  FROM MTL_MATERIAL_TRANSACTIONS WHERE
RCV_TRANSACTION_ID IN (110679, 110680) --> TRANSACTION_TYPE_ID
SELECT *  FROM MTL_ONHAND_QUANTITIES WHERE
INVENTORY_ITEM_ID=149 AND ORGANIZATION_ID=606
SELECT *  FROM FROM ORG_ORGANIZATION_DEFINITIONS WHERE
ORGANIZATION_CODE='M3'
SELECT  SUM(TRANSACTION_QUANTITY)  FROM
MTL_ONHAND_QUANTITIES WHERE INVENTORY_ITEM_ID=149  AND
ORGANIZATION_ID=606
SELECT *  FROM MTL_TRANSACTION_TYPES WHERE
TRANSACTION_TYPE_ID=18 (TRANSACTION_TYPE_NAME)
SELECT *  FROM MTL_MATERIAL_TRANSACTIONS WHERE
INVENTORY_ITEM_ID=149 AND ORGANIZATION_ID=207 AND
TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT *  FROM AP_INVOICES_ALL WHERE INVOICE_NUM= 'INV27012'
SELECT *  FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE
INVOICE_ID=63247
SELECT *  FROM AP_AE_HEADERS_ALL
SELECT *  FROM AP_AE_LINES_ALL WHERE SOURCE_ID=63247
SELECT *  FROM AP_PAYMENT_SCHEDULES_ALL WHERE
INVOICE_ID=63247
SELECT *  FROM AP_CHECKS_ALL WHERE CHECK_ID=28598
SELECT *  FROM AP_BANK_ACCOUNTS_ALL WHERE
BANK_BRANCH_ID=10002
SELECT * FROM AP_INVOICE_PAYMENTS_ALL
SELECT * FROM AP_BANK_BRANCHES

SELECT *  FROM GL_JE_HEADERS
SELECT *  FROM GL_JE_LINES
SELECT *  FROM GL_JE_BATCHES
SELECT *  FROM GL_SETS_OF_BOOKS
SELECT *  FROM GL_CODE_COMBINATIONS
SELECT *  FROM GL_PERIODS
SELECT *  FROM GL_BUDGETS

SELECT *  FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='5633'
SELECT * FROM PO_REQUISITION_LINES_ALL WHERE
REQUISITION_LINE_ID=60500
SELECT *  FROM PO_REQ_DISTRIBUTIONS_ALL WHERE
DISTRIBUTION_ID=59467

SELECT *  FROM PO_AGENTS
SELECT *  FROM PO_ACTION_HISTORY
SELECT *  FROM PO_VENDOR_CONTACTS
SELECT *  FROM PER_ALL_PEOPLE_F

SELECT *  FROM AP_BATCHES_ALL
SELECT *  FROM AP_TERMS
SELECT *  FROM AP_ACCOUNTING_EVENTS_ALL
SELECT *  FROM AP_LOOKUP_CODES
SELECT *  FROM AP_DISTRIBUTIONS_SETS_ALL
SELECT *  FROM AP_RECURRING_PAYMENTS_ALL
SELECT *  FROM AP_EXPENSE_REPORT_HEADERS_ALL
SELECT *  FROM AP_EXPENSE_REPORT_LINES_ALL

SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE='XXDEPARTMENT_NUMBERS'
SELECT *  FROM FND_NEW_MESSAGES WHERE MESSAGE_NAME LIKE
'VALIDATE_SAL' OR MESSAGE_TEXT LIKE '%GREATE THAN 2000%'

SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER=56707
SELECT ORDERED_QUANTITY*UNIT_SELLING_PRICE FROM
OE_ORDER_LINES_ALL WHERE HEADER_ID=94119
SELECT LINE_NUMBER, SHIPMENT_NUMBER LINE_NUM, A.8 FROM
OE_ORDER_LINES_ALL A WHERE HEADER_ID=94119
SELECT * FROM OE_TRANSACTION_TYPES_TL WHERE
TRANSACTION_TYPE_ID=1000
SELECT * FROM QP_LIST_HEADERS WHERE LIST_HEADER_ID=1000
SELECT * FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID=1005
 SELECT * FROM HZ_PARTIES
SELECT RELEASED_STATUS, A.* FROM WSH_DELIVERY_DETAILS A WHERE SOURCE_LINE_ID=195967
SELECT * FROM WSH_DELIVERY_DETAILS WHERE
SOURCE_HEADER_ID=941666

SELECT * FROM RA_INTERFACE_LINES_ALL WHERE
INTERFACE_LINE_ATTRIBUTE1='56707'
SELECT * FROM RA_INTERAFCE_SALESCREDITS_ALL
SELECT * FROM RA_INTERAFACE_DISTRIBUTIONS_ALL
SELECT * FROM RA_INTERAFCE_ERRORS_ALL
SELECT * FROM RA_CUSTOMER_TRX_ALL WHERE TRX_NUMBER='10020697'
SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL WHERE
CUSTOMER_TRX_ID=208295
SELECT * FROM  AR_CASH_RECEIPTS_ALL WHERE
RECEIPT_NUMBER='123456'

SELECT * FROM OE_ORDER_HOLDS_ALL
SELECT * FROM OE_HOLD_DEFINITIONS WHERE HOLD_ID=1022
SELECT * FROM OE_HOLD_SOURCES_ALL WHERE HOLD_SOURCE_ID=1206

SELECT * FROM FND_TABLES WHERE TABLE_NAME LIKE
'FND%RESP%GROUP%'
SELECT * FROM  FND_PROFILE_OPTIONS WHERE
PROFILE_OPTION_NAME='PO_QTY_RESTRICT'
SELECT  PROFILE_OPTION_VALUE FROM FND_PROFILE _OPTION_VALUES WHERE PROFILE_OPTION_ID=1007461
SELECT * FROM FND_USER
SELECT * FROM FND_APPLICATION_TL
SELECT * FROM FND_APPLICATION_VL WHERE
APPLICATION_NAME='APPLICATION OBJECT LIBRARY'
SELECT * FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_NAME LIKE 'XX23SAMPLE RES%'
SELECT * FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_NAME LIKE 'XX23SAMPLE RESP%'
SELECT * FROM FND_USER_RESP_GROUPS
SELECT * FROM FND_CONCURRENT_PROGRAMS_TL WHERE
USER_CONCURRENT_PROGRAM_NAME LIKE 'XXSAMPLE PROGRAM 0112'
SELECT * FROM FND_CONCURRENT_PROGRAMS_VL WHERE
USER_CONCURRENT_PROGRAM_NAME LIKE 'XXSAMPLE PROGRAM0112'
SELECT * FROM FND_EXECUTABLES_TL
SELECT * FROM FND_EXECUTABLES_VL WHERE
EXECUTION_FILE_NAME='EMP'
SELECT * FROM FND_CONCURRENT_PROGRAMS_VL WHERE
EXECUTABLE_ID=12187 OR EXECUTABLE_NAME='XXSAMP%'
SELECT * FROM FND_CONCURRENT_REQUESTS WHERE
REQUEST_ID=2770611
SELECT * FROM FND_CONCURRENT_REQUESTS WHERE
CONCURRENT_PROGRAM_ID=59750
SELECT * FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE='CP_PHASE_CODE'
SELECT * FROM FND_FLEX _VALUE_SET WHERE
FLEX_VALUE_SET_NAME='XXIND_SAM09'


PO:
à CREATING THE REQUISITION
à CREATING PURCHASE  ORDER
à CREATING RECEIPT
à CREATING INVOICE IN AP(aCCOUNT PAYABLES)
à PAYING THE INVOICE
à TRANSFER , IMPORT AND POST TO GL
       Create the invoice
       Match the invoice either with po or with receipt
       Validate the invoice
       Create accounting entries
       Make the payment
       Create Accounting entries
       Transfer the data to gl
O2C:
Complete order to cash cycle steps including
  à  Entering the sales order
  à  Booking the sales order
  à  Launch  pick release
  à  ShipConfirm
  à  Create invoice
  à  create the receipts either manual or using autolock box (in this article we will
        concentrate on manual creation)
  à  Transfer to general ledger
  à   Journal Import
  à  posting

Differnce between PO & SO:

                       PO     
                     SO
Purchasing
 Selling
supplierinformation(supplier_site)à Address
customerinformation(customer_num) (Bill_To/ Ship_to)

item information
item information
Qty
Qty
need_by_date
requestdate, promisedate, schedule shipdate
organization information
organization information
price information
price informatioin with come from pricelist
purchase po approved befor status is in complete
sales order must be approve booked before status is entered
purchase po status incomplete, requires, closed, cancelled
flow_status_code: oeoh
flow_status_code:oeol not same
PO (H, L , S, D) 
SO(H,L)
Default in status po: incomplete
Default in status header level: entered
line level: entered
In PO status created after that status is "approved"
oeoh booked approve.

Backend DB: API
à  fnd_program.register
à  fnd_profile.value
à  fnd_global.apps_initialize
à  fnd_request.submit_request
                              

                                                              
                                                     
                                                                                                         
                                                                                 
                                                                
                                                   
                                                             
            
                                                                                                                                                   



 



  
        





    

XML Publisher

XML Publisher Templates by Example

Learn how to create reporting layout formats easily and simply using Oracle XML Publisher.
by Tim Dexter 
Oracle XML Publisher provides a new reporting paradigm in the market place. Traditional reporting tools hold the datasource (query), layout and translation in a single executable file. XML Publisher takes the approach of splitting the three components apart. This makes reporting much more flexible and easier to maintain.
With the layout now independent of the data this allows IT departments to start consolidating their datsources into fewer extraction programs. No longer do they have to have one query, one layout. A single data extraction can now support multiple layouts allowing users to apply various layouts to a single datasource and generate completely different report outputs.
The IT department can also free itself from the perrenial problem of having to build and maintain layouts formats. The layout templates can be built using familiar desktop tools such as MSWord or Adobe Acrobat. These can be built by functional users with a little practice. Organizations can see huge savings in time and effort in getting reports to their end users. At every stage the process is faster:
§  Developing data extraction programs – the IT department can use various methods for extracting data one of the fastest being the XML Publisher data engine. At its most basic, give it SQL and it will return XML data.
§  Building Layouts – no longer a tedious thread of email between business analyst and developer to get the layout just right. The analyst knows the requirements intimately and can build the layout him or herself and get it right first time. Then pass to the developer for deployment to the server.
§  Deploying  - whether you’re using EBS or Online or you have plugged XML Publisher into your own application deployment is simple. There are options to deploy datasources and templates to either the filesystem or the database.

Background / Overview

This article will introduce the RTF template formats that can be used with XML Publisher. It will take you from a simple listing report to a complete report layout inlcuding headers, footers, images, master-detail relationships and conditional formatting all from the comfort of an MSWord interface.

Preliminary Setup

To aid in the building of RTF templates XML Publisher provides the XML Publisher Template Builder for MSWord. Prior to starting the examples you should download this plugin and install it. This is available from metalink, the latest version is 5.5 and can be found by downloading patch 4561835. Alternatively you can download from the Oracle Store site by searching for XML Publisher. You will also need to download and unpack the zip file associated with this article.
Once you have sucessfully installed the plugin you will find a new tool bar and menu entry in your MSWord install.
All of the following report formats are based on EmployeeListing.xml:
<?xml version="1.0"?>
<ROWSET>
   <ROW num="1">
      <EMPNO>100</EMPNO>
      <ENAME>Jo Bloggs</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
      <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW num="2">
      <EMPNO>100</EMPNO>
      <ENAME>Jane Doe</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </ROW>
   ...
</ROWSET>

As  you can see the data is showing employee details and the department they work in. There is no master-detail relationship in the data, it is completely ‘flat’.

Your First Template - Simple Employee Detail

The simplest output we could generate would be to show an employee and their details. So open MSWord and use the Data > Load XML data option on the Template Builder toolbar and select the XML file – EmployeeListing.xml. You will get confirmation that the data has loaded successfully. Now use the Insert > Field option, this will open a dialog box showing the available fields from the data.
Figure 1: Insert field dialog

Notice the list of available fields in the dialog. We can use this dialog to enter these fields into our report. Select the insertion point in the Word document and then click on a field and then use the Insert button. Using this method build a layout similar to the one below add the boiler plate text as you go.
Figure 2: Simple Employee Detail Template
You’ll notice that as you use the insert button that Microsoft Form Fields are being inserted into the document. This is the object that XML Publisher uses to store the mapping to the XML element in the data. This is done so that a user can easily drag and drop report objects around the document without having to worry about the ‘nuts and bolts’ of the report. Double click on a field and you’ll get the Field Property dialog, now click on the Help button and you’ll see the method XML Publisher uses to map to the data elements.
Figure 3: Form Field Dialog
Notice in this case we see <?EMPNO?>, this tells the XML Publisher template parser that the EMPNO element value should be inserted into the final document using ‘<?’ and ‘?>’ to delimit the element mapping.
Now select Preview > PDF from the toolbar, the template builder will prompt you to save template as an RTF format. Once this is done the template will be applied to the data. You’ll get something like:
Figure 4: Simple Employee Detail Output
Great, so now you have the first employee and their details but where are the others from the XML data. This template lacks the commands to loop over the employees and it therefore only shows the first record. Next we’ll add those commands. (EmployeeListing1.rtf)

 Get your employees listing

So we want to loop over the employees and get all of their data into the output. Go back to MS Word close your original document and create a new one and load the data again, alternatively just delete the content. Now use the toolbar and select Insert > Table/Form. You’ll be presented with the following dialog, notice it is made up of three regions.
§  Left Pane – this shows the source XML tree
§  Center Pane – this is the target or document pane
§  Right Pane – this has the Property palette and Preview region
Figure 5: Insert Table/Form dialog
This dialog is very powerful and allows you to build either small parts of a report layout or the complete report structure in one go. To create the looping structure use your mouse and click on the ‘Row’ node, hold it down and drag it to the center pane and let go. A popup will ask if you want to drop a ‘Single Node’ or ‘All Nodes’, select ‘All Nodes’. You’ll now see a new tree structure with the ‘Row’ element at the top. Select this node with your mouse and you see the properties palette and preview region fill.
Figure 6: Properties and Preview
 Try changing the ‘Style’ property and then clicking on the tree; you can specify ‘Table’, ‘Form’ and ‘Free Form’. You’ll see the preview region update based on your choices. Select ‘Table’ and then highlight the ‘@num’ value and use the delete key to remove it from the tree. Now hit OK. The template builder will then insert the necessary structures into your template.
Figure 7: Inserted table structure
Notice the data is now presented in a table, there are two rows created, one for the column headers  and the second for the form fields. The builder will put in some default boilerplate based on the XML data element names. The regular form fields are still there but you will also notice two extra fields ‘F’ and ‘E’. These contain the looping commands:
§  F - <?for-each:ROW?> - ROW is the group in the XML and this command is saying, “for each member of the ROW group carry out the following ie enter the fields EMPNO, ENAME, etc
§  E - <?end for-each?> - this command closes the loop.
Now preview this template in the format of your choice, you’ll see the following:
So now we are seeing all of the employees details in a table. So we now have a basic listing report but lets say we wanted to group the employees by their department or job. (EmployeeListing2.rtf)

Regroup your employees

Our data does not reflect this grouping but the template builder can help us to group the data in the template despite not having the supporting structure in the data. This feature is extremely powerful, a generic ‘flat’ extract can support many layout formats allowing users to decide how they want to see the data.
Either create a new document or delete the existing content. We are going to create a report that shows the employees by department. We will use the same dialog as before, so open it up, Insert > Table/Form and clear the center pane if needed.
Click and hold on the ROW node and drag to the center pane and ‘Drop All’, remove the @num node. Highlight the ROW node and then on the Grouping property drop down select ‘DNAME’ i.e. the department name. Now click back on the ROW node and you’ll see the tree has been updated.
Figure 8: Regrouping dialog
You’ll notice that the document tree has been updated to show the grouping we have created. In its current state we will have a Table in Table report ie the employee data will be a nested table within a department table. We are going to have a simpler format. Select the Row node and change the Style property to ‘Free Form’, notice the icon next to the node is updated to give a clue as to the nature of how that node will be rendered. Now click OK and you should get template like:
Figure 9: Regrouping template
You can see the familiar table and its fields but we now have two extra fields:
§  G - <?for-each-group:ROW;./DNAME?><?sort:DNAME;'ascending';data-type='text'?>  - this command is specifying that the ROW group should be then grouped by DNAME. There is also a sort command to sort by the DNAME as well. The sort criteria can be changed using the property palette. The template will now loop over this newly created group.
§  E - <?end for-each-group?> - this closes out the new DNAME group.
There has been another update, the original ‘F’ field now contains, <?for-each:current-group()?>, this is saying loop over the current group i.e. the DNAME group we created above. Try previewing this template and you’ll get something like:
Figure 10: Grouping by department output
We have now created a report that shows our employees by their department. We could easily group them by their job titles, or even group by department and then by job title.

Totals and tallies

So now we have imposed some grouping on our data we want to add some sub totals, lets say we want to know how much the salary cost is in each department and how many employees are in each. These values are not in the XML data but we can create them in the template. For this we are going to go back to the insert field dialog. Lets insert the employee count first, move the cursor to just after the DNAME field, then use the toolbar Insert > Field. Select the EMPNO field and then select ‘count’ from the Calculation drop down box. Finally we need to check the Grouping box to let the builder know we are creating a summary field on top of the grouped data and then click on insert.
Figure 11: Insert summary field
You’ll see a new field created, ‘countEMPNO’, this field contains the code to calculate the number of employees, <?count(current-group()/ENAME)?>.
Now create a new row in the employee table and move the cursor to the cell under the SAL field. Use the dialog again, this time create a ‘sum’ on the SAL element, don’t forget the Grouping checkbox. You should end up with a template like this:
Figure 12: Summary column template
Now preview your template and you’ll get the following:
Figure 13: Summary output
You’ll notice we now have an employee number after the department name and a total salary amount for each department. You can create complex calculations in the template, its up to you.

Conditional Formatting

You’ll probably have noticed that the report output has some employees that do not belong to any department. Let’s assume we want to hide those employees that do not have a department.
We can achieve this quite simply with an ‘if’ statement. The current release of the Template Builder  does not support inserting conditional statements so we are going to have to write some code by hand.
We need to add a new formfield, you may need to enable the ‘Form’ toolbar in MSWord. Now place the cursor just after the  ‘G’ field and insert a form field. Open the properties and then the help text and enter, ‘<?if:DNAME!=""?>’. This command is basically checking if the DNAME element has a value. Now place the cursor just before the last ‘E’ field and create another field, this time enter ‘<?end if?>’, this closes the if statement. So if the ‘if’ statement is evaluated to true then the content between the ‘if’ and ‘end if’ statement i.e the department and employee data will be displayed; if evaluated to false as in the first set of data then it will not be show. Your template should look something like this:
Figure 14: Conditional template
The output when you run this template will not contain those employees that do not belong to a department. You could equally exclude employees with a salary greater than 5000. Its up to you and your users.  

Dressing Up

So we now have the basic structure of the report we want, we now need to add formatting to make it more readable. For this we’ll use MSWord functionality.
You can update colors, fonts, styles highlighting plain boiler plate and fields, XML Publisher will respect the formatting. For number formatting, again we use MSWord functionality. Double click on the SAL field to get the field dialog, now specify the field type to be ‘Number’ and then pick a Format from the drop list, you can enter a default number in the ‘Default Number’ field.
Now click OK. You’ll see the field value is now formatted according to the format you specified. XML Publisher will respect this at runtime. This will also work for date formatting.
You can update the header and footer too; add boiler plate, images, etc. MSWord will not allow you to enter form fields in the header or footer but you can enter the command string e.g. <?DNAME?>. You can also add page numbers using the native MSWord method. You can very quickly build a report layout similar to:
Figure 15: Formatted template
This template will now generate the following output.
Figure 16: Formatted report output
Of course if you or your users want to make a change then just reopen the template and update the layout.

Deployment

Once you are happy with your template its then a question of deploying it to the server whether that be for the E Business Suite, Peoplesoft, JD Edwards or XML Publisher Enterprise.

Conclusion


This document has given you brief introduction to the world of XML Publisher RTF templates, it has hopefully provided you a glimpse of the power of XML Publisher and the flexibility it provides to you and your users. There are many sample templates delivered with the Template builder install and the User Guide documentation covers how to create other report objects such as charts, shapes, page totals, conditional formatting objects etc.

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...