Puchasing and Payables Module: Technical Details

PO_VENDORS
This table stores information about your suppliers. Oracle Purchasing uses this information to determine active suppliers. The primary key is VENDOR_ID.

PO_VENDOR_SITES_ALL
This table stores information about supplier sites. Oracle Purchasing uses this information to store supplier address information. The primary key is VENDOR_SITE_ID.

PO_VENDOR_CONTACTS
This table stores information about supplier site contacts. The primary key is VENDOR_CONTACT_ID.

PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_HEADERS_ALL stores information about requisition headers. Each row contains the requisition number, preparer status, and description. It is one of three tables that stores requisition information. The primary key is REQUISITION_HEADER_ID.

PO_REQUISITION_LINES_ALL
This table stores information about requisition lines. Each row contains the line number, item number, item category, item description, need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line. This table is one of three tables that stores requisition information. The primary key is REQUISITION_LINE_ID.

PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution. Each row includes the Accounting Flex field ID and requisition line quantity. This table is one of three tables that stores requisition information. The primary key is DISTRIBUTION_ID.

PO_HEADERS_ALL
PO_HEADERS_ALL contains information for your purchasing documents. Each row contains buyer information, supplier information, notes, foreign currency information, terms and conditions information, and the document status. Oracle Purchasing uses this information to record information related to a complete document. The primary key is PO-HEADER_ID.

PO_LINES_ALL
PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document. Each row includes the line number, item number and category unit, price, tax information, and quantity ordered for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs. The primary key is PO_LINE_ID.

PO_LINE_LOCATIONS_ALL
This table contains information about purchase order shipment schedules and blanket agreement price breaks. You must have one row for each schedule or price break you attach to a document line. Each row contains the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders and price break information for blanket purchase orders, quotations, and RFQs. The primary key is LINE_LOCATION_ID.

PO_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL contains accounting information for a purchase order shipment line. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases. It is one of five tables that stores purchase orders and releases. The primary key is PO_DISTRIBUTION_ID.

RCV_SHIPMENT_HEADERS
This table stores common information about the source of your receipts or expected receipts. You group your receipts by the source type and the source of the receipt. Oracle Purchasing does not allow you to group receipts from different sources under one receipt header. The primary key is SHIPMENT_HEADER_ID.

RCV_SHIPMENT_LINES
This table stores information about items that have been shipped or received from a specific receipt source. This table also stores information about the default destination for in-transit shipments. The primary key is SHIPMENT_LINE_ID.

RCV_TRANSACTIONS
This table stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table.
Once a row has been inserted into this table, it will never be updated. When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing. The primary key is TRANSACTION_ID.

PO_RELEASES_ALL
This table contains information about blanket and planned purchase order releases. You need one row for each release you issue for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment (PO_LINE_LOCATIONS_ALL). The primary key is PO_RELEASE_ID.

AP_INVOICES_ALL
This table contains records for supplier invoices and has one row for each invoice.
To pass Invoice Validation, the INVOICE_AMOUNT in the AP_INVOICES_ALL table must equal the sum of the AMOUNT columns in the AP_DISTRIBUTIONS_ALL table. The primary key is INVOICE_ID.

AP_INVOICE_DISTRIBUTIONS_ALL
This table holds the distribution line information.
There is a row for each invoice distribution. If matching is used, distribution information is copied from PO_DISTRIBUTIONS_ALL during the matching process.

AP_PAYMENT_SCHEDULES_ALL
This table contains information about scheduled payments for an invoice.
Oracle Payables uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch.

AP_HOLDS_ALL
This table contains a record for each hold placed on an invoice.
An invoice might have multiple records in this table.
An invoice cannot be paid until all holds placed on it have been released.

AP_CHECKS_ALL
This table stores information about payments issued to suppliers.
Each row includes the supplier name, address, and bank account name for auditing purposes, in case any of them change after you make the payment.
If the payment is electronic, the supplier bank account information is stored in the payment record.

AP_INVOICE_PAYMENTS_ALL
This table provides the link between a payment (CHECK_ID) and the invoice(s) paid by that payment.
This table contains records of invoice payments that you make to suppliers. Oracle Payables updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick Payment. This table contains one row for each payment made for each invoice.
Void payments are also entered into this table as a negative record of the original payment line.

AP_BANK_ACCOUNTS_ALL
This table stores information about your bank accounts and your suppliers’ and customers’ bank accounts. This table contains one row for every bank account, whether it is an internal (supplier) or external (customer) account.

AP_BANK_BRANCHES
This table stores information about the bank branches and details about the branches.

po headers based sub query

select * from po_lines_all where po_header_id =<po_header_id>;

po_line_locations_all

select * from po_line_locations_all where po_header_id =<po_header_id>;

po_distributions_all

select * from po_distributions_all where po_header_id =<po_header_id>;

po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;

Stage 2: Once PO is received data is moved to respective receving tables and inventory tables

RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =<po_header_id>);

RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =<po_header_id>;

RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =<po_header_id>;

RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);

RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =<po_header_id>;

MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =<po_header_id>);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>);

AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>));

Stage 4 : Many Time there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id = <po_header_id> );

Stage 5 : General Ledger

Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN ('<po_header_id>');

GL_INTERFACE

SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id <po_header_id>));

GL_IMPORT_REFERENCES

SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>))

Before report trigger to show the parameter using if condition

FUNCTION BeforeReport
   RETURN BOOLEAN
IS
   item1      VARCHAR2 (250);
   item2      VARCHAR2 (250);
   org_name   VARCHAR2 (250);
   Supplier_name    varchar2(75);
BEGIN
   :from_receipt_date :=
      TO_CHAR (TO_DATE (:from_receipt_date, 'YYYY/MM/DD HH24:MI:SS'),
               'DD-MON-RRRR');
   :to_receipt_date :=
      TO_CHAR (TO_DATE (:to_receipt_date, 'YYYY/MM/DD HH24:MI:SS'),
               'DD-MON-RRRR');


   SELECT   organization_name
     INTO   org_name
     FROM   org_organization_definitions
    WHERE   organization_id = :ORGANIZATION_ID;

   srw.MESSAGE (2, 'org_name' || org_name);
   :CP_ORGANIZATION := org_name;
   srw.MESSAGE (3, ':CP_ORGANIZATION' || :CP_ORGANIZATION);
   :CP_FROM_RECEIPT_DATE := :FROM_RECEIPT_DATE;
   :CP_TO_RECEIPT_DATE := :TO_RECEIPT_DATE;

   BEGIN
      IF :FROM_MATERIAL_CODE IS NOT NULL
      THEN
         SELECT   segment1
           INTO   item1
           FROM   mtl_system_items_b
          WHERE   inventory_item_id = :FROM_MATERIAL_CODE;

         :CP_FROM_MATERIAL_CODE := item1;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;

   BEGIN
      IF :TO_MATERIAL_CODE IS NOT NULL
      THEN
         SELECT   segment1
           INTO   item2
           FROM   mtl_system_items_b
          WHERE   inventory_item_id = :TO_MATERIAL_CODE;
         :CP_TO_MATERIAL_CODE := item2;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;
Begin
     IF :VENDOR_NAME IS NOT NULL then
          SELECT   VENDOR_NAME
           INTO   Supplier_name
           FROM   po_vendors
          WHERE   vendor_id= :VENDOR_NAME;
   :CP_VENDOR_NAME := Supplier_name;
     END IF;
     end;
   RETURN (TRUE);
END;

APIL PO RECEIPT REJECTION REPORT(PO-RCV-INV)_LINK

 SELECT   DISTINCT
           poh.segment1 po_number,
           pol.item_id,
           poh.creation_date po_date,
           aps.vendor_name,
           RSL.TO_ORGANIZATION_ID,
           pol.quantity po_quantity,
           RSH.SHIPMENT_HEADER_ID,
           rsh.receipt_num grn_no,
           rsl.quantity_received receipt_quantity,
           ( (SELECT   SUM (tax_amount)
                FROM   jai_rcv_line_taxes a
               WHERE   rsl.shipment_line_id = a.shipment_line_id(+))
            + (rsl.quantity_received * pol.unit_price))
              receipt_value,
           rsh.creation_date receipt_date,
           DECODE (RT.TRANSACTION_TYPE, 'REJECT', NVL (RT.QUANTITY, 0), 0)
              "REJECTED_QUN",
           rsl.quantity_received
           + DECODE (RT.TRANSACTION_TYPE, 'REJECT', NVL (RT.QUANTITY, 0), 0)
              "RECEIVE_QUN",
           (NVL (RT.QUANTITY, 0) * NVL (pol.unit_price, 0)) "REJECTED_VALUE",
           RT.TRANSACTION_DATE "Rejected_Date",
           rt.attribute2 lr_no,
           rt.attribute3 lr_date
    FROM   po_headers_all poh,
           po_lines_all pol,
           jai_po_taxes jpo,
           po_line_locations_all pll,
           po_distributions_all pda,
           rcv_shipment_headers rsh,
           rcv_shipment_lines rsl,
           rcv_transactions rt,
           ap_suppliers aps,
           hr_all_organization_units_tl hou,
           ap_invoice_lines_all ail,
           ap_payment_schedules_all apsa,
           ap_invoices_all aia
   WHERE       poh.po_header_id = pol.po_header_id
           -- AND jpo.po_line_id(+) = pol.po_line_id
           AND jpo.PO_HEADER_ID = POH.PO_HEADER_ID
           AND pol.po_line_id = pll.po_line_id
           AND pll.line_location_id = pda.line_location_id
           AND rsl.po_header_id = poh.po_header_id
           AND rsh.shipment_header_id = rsl.shipment_header_id
           AND rt.shipment_line_id = rsl.shipment_line_id
           AND ail.invoice_id = aia.invoice_id
           AND rt.transaction_type IN ('REJECT')
           AND poh.vendor_id = aps.vendor_id
           AND poh.org_id = hou.organization_id
           AND pda.po_distribution_id = ail.po_distribution_id
           AND ail.line_type_lookup_code = 'ITEM'
           AND apsa.invoice_id = aia.invoice_id
           AND  trunc(rsh.creation_date) BETWEEN NVL (:from_receipt_date,
                                                      rsh.creation_date)
                                             AND  NVL (:to_receipt_date,
                                                       rsh.creation_date)
           AND pol.item_id BETWEEN NVL (:from_material_code, inventory_item_id)
                               AND  NVL (:to_material_code, inventory_item_id)
           AND RSL.TO_ORGANIZATION_ID = :organization_id
           AND poh.vendor_id = NVl(:vendor_name ,poh.vendor_id)
GROUP BY   poh.segment1,
           pol.item_id,
           poh.creation_date,
           pol.unit_price,
           pol.quantity,
           aps.vendor_name,
           rsl.quantity_received,
           rsl.shipment_line_id,
           apsa.amount_remaining,
           rsh.receipt_num,
           rsl.quantity_received,
           rsl.quantity_shipped,
           pll.need_by_date,
           rsh.creation_date,
           aia.invoice_num,
           aia.invoice_date,
           rt.attribute2,
           rt.attribute3,
           RT.QUANTITY,
           TO_ORGANIZATION_ID,
           RT.TRANSACTION_TYPE,
           RT.TRANSACTION_DATE,
           RSH.SHIPMENT_HEADER_ID
ORDER BY   POH.SEGMENT1, RSH.RECEIPT_NUM

Customer details of an order

SELECT

     h.order_number

    ,h.sold_to_org_id bill_cust_account_id

    ,h.ship_to_org_id ship_to_site_use_id

    ,h.invoice_to_org_id bill_to_site_use_id

    ,hp.party_name "Customer Name"

    ,hca.account_name

    ,hca.org_id

    ,hcasab.orig_system_reference      BILL_TO_ORIG_REF

    ,hpb.status                        BILL_TO_STATUS

    ,'ADDRESS1 - '||bill_loc.address1||','||CHR(10)||

     'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||

     'ADDRESS3 - '||bill_loc.address3||','||CHR(10)||

     'CITY     - '||bill_loc.city||','||CHR(10)||

     'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)||

     'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS

    ,hcasas.orig_system_reference      SHIP_TO_ORIG_REF

    ,hps.status                        SHIP_TO_STATUS

    ,'ADDRESS1 - '||ship_loc.address1||','||CHR(10)||

     'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||

     'ADDRESS3 - '||ship_loc.address3||','||CHR(10)||

     'CITY     - '||ship_loc.city||','||CHR(10)||

     'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)||

     'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS

FROM oe_order_headers_all h

    ,hz_parties hp

    ,hz_cust_accounts hca

    ,hz_cust_acct_sites_all hcasab

    ,hz_cust_acct_sites_all hcasas

    ,hz_cust_site_uses_all hzsuab

    ,hz_cust_site_uses_all hzsuas

    ,hz_party_sites hps

    ,hz_party_sites hpb

    ,hz_locations bill_loc

    ,hz_locations ship_loc

WHERE 1 =1

AND hp.party_id             = hca.party_id

AND hca.CUST_ACCOUNT_ID     = h.sold_to_org_id

AND hcasab.cust_account_id  = hca.cust_account_id

AND hcasas.cust_account_id  = hca.cust_account_id

AND hpb.location_id         = bill_loc.location_id

AND hps.location_id         = ship_loc.location_id

AND hcasab.party_site_id    = hpb.party_site_id

AND hcasas.party_site_id    = hps.party_site_id

AND hcasab.cust_acct_site_id= hzsuab.cust_acct_site_id

AND hcasas.cust_acct_site_id= hzsuas.cust_acct_site_id

AND h.ship_to_org_id        = hzsuas.site_use_id

AND h.invoice_to_org_id     = hzsuab.site_use_id

AND h.order_number          = '&order_number';

Querys

http://appsquery.blogspot.com

XML_Functionalitys _Tutorial

Welcome to Oracle XML Publisher
                Template Builder for Word 5.6.2!

Introduction

The Template Builder is an extension to Microsoft Word that simplifies the development of RTF templates. While the Template Builder is not required to create RTF templates, it provides many functions that will increase your productivity.

The Template Builder is tightly integrated with Microsoft Word and allows you to perform the following functions:

Ÿ  Insert data fields into your RTF templates

Ÿ  Insert data driven tables

Ÿ  Insert data driven forms

Ÿ  Insert data driven charts

Ÿ  Preview RTF templates with sample XML data

Ÿ  Browse and update the content of form fields

Ÿ  Extract boilerplate text into an XLIFF translation file and test translations

This help document assumes a basic working knowledge of Oracle XML Publisher. Please refer to the Oracle XML Publisher User’s Guide for additional information. This Template Builder version is based on XML Publisher 5.6.2.

If the template builder is installed correctly, you should see the menu bar shown below in the toolbar section:

If you are new to Template Builder for Word, please skip the next sections and continue with the Quick Tutorial.

What is new in Version 5.6.2?

Template Builder 5.6.2 provides the following new features:

Ÿ  XML Schema files (.xsd) as data definition

Ÿ  Drag & Drop for the Insert Field dialog

Ÿ  XML Publisher 5.6.2 compatible

Ÿ  Validation Function allows to check templates for problems.

Ÿ  Builder for simple XML Publisher Enterprise reports

Template Builder 5.6.2 allows you to load an XML Schema file (XSD) to describe the report data. The XML schema has the advantage of being complete (a sample xml may not contain all the fields from the data source). The Template Builder can generate dummy sample data for the preview from the XML Schema, but we still recommend that you upload a real sample XML.

What is new in Version 5.5?

Template Builder 5.5 provides the following new features:

Ÿ  Extract the template boilerplate into an industry standard XLIFF translation file

Ÿ  Preview templates with translation files

Ÿ  Sorting and page/section breaks for tables

Ÿ  XML Publisher 5.5 compatible

Quick Tutorial

This tutorial describes how you would typically create a simple layout template. You will create a letter that shows the outstanding balance for a customer. This letter contains a table of all unpaid or partially paid invoices.

Load Word File

Often a template starts from an example document in the form of a regular Microsoft Word document. For this tutorial go to the samples\RTF templates\Balance Letter folder under your XML Publisher installation directory (e.g. C:\Program Files\Oracle\XML Publisher Desktop) and load the file Balance Letter Start.rtf. This file represents a Word document that has not been marked up for use with Oracle XML Publisher.

Load Sample XML Data

From the Data menu select Load XML Data to load a sample XML document that contains the data fields you may want to insert into your RTF layout template. For this tutorial go to the samples\RTF templates\Balance Letter folder under your XML Publisher installation directory and load the file Balance.xml.

Insert Form Fields

You may want to select single data fields, such as the customer name and address at the header of the letter. Position the cursor in the Word document where you want to insert the Address. From the Insert menu select Field… to open the dialog window shown below.

Select the field Customer Name and click Insert to insert a form field for the customer name into the document. You may also insert the remaining address fields (such as Address Line1 to 4, City, State and Zip).

Starting with this version, you can also just drag the Customer Name field from the dialog and drop it into the Word document. NEW!

You can also select the fields, As of Date, Trx Currency Code and the C Inv Open Balance and insert them into the appropriate spaces in the document.

Note: As a beginner, you should use Insert Fields only for data fields that are unique (not repeating) in your document. Refer to the “Insert Table” section for additional information on how to insert repetitive fields

Preview Template

After adding fields to the document, you may want to test your template. Oracle XML Publisher Template Builder allows you to preview your template with your sample XML data. You can preview the output in Adobe Acrobat Reader (PDF), Microsoft Word file (RTF), Microsoft EXCEL (EXCEL) or a browser (HTML).

From the Preview menu select HTML to preview your template in a browser. From the Preview menu select PDF to preview your document in Acrobat Reader. You must have Adobe Acrobat Reader version 5.0 or higher installed to preview documents in PDF format. You can download Acrobat Reader for free at http://www.adobe.com/products/acrobat/readstep2.html.

The preview should show Vision Corporation instead of the field Customer Name.

Insert Table

You can build a table or a repeating section by inserting the elements and then adding additional form fields with processing instructions (for-each). Instead, the Template Builder offers an additional function that generates a table for you.

The Template Builder can generate for-each instructions either as descriptive or abbreviated. Abbreviated instructions are less invasive to the appearance of the document, while descriptive processing instructions make the template easier to understand. For this tutorial, select Options… from the Tools menu, switch to the Build tab and select Descriptive as shown below.

The document should include a table of all invoices that are not or only partially paid. On the Insert menu select Table/Form… to open the Table/Form Dialog. The following dialog will be displayed:

Scroll in the left Data Source view pane until you see the G Invoices element. Push the left mouse button and drag the G Invoices element over to the center Template tree view. When you drop an element with children (other elements below the element), the following pop-up dialog appears:

Note: A Node is a term used in tree views for an element in the tree view. For example, Customer Name, G Currency and G Invoices are all nodes. Transaction Date is also called a child node of G Invoices, and G Invoices is referred to as a parent node of Transaction Date.

To add multiple nodes, click Drop All Nodes with the left mouse button. When you select the "G Invoices" node, the dialog box should now look like this:

We only want to show the fields Trx Number, Transaction Date, Trans Amount, and Trans Amount Remaining. Select the field Trans Type in the center view and press the Delete button on your keyboard. Repeat the procedure for the other fields that you do not want to be included in the table (as shown in the screenshot below).

You should now click again on the G Invoices element in the center view. The Properties region on the right shows how the data fields will be formatted. You should see the following information:

For each group you can set the following properties that describe how a group (such as G Invoices) should be rendered by the template builder:

Style: Select Table to create a table around the data fields.

Grouping: Grouping is an advanced operation that allows you to re-group the data – for example by transaction date instead of currency. You can select the element that the data should be grouped by for this property. Please refer to the Oracle XML Publisher User’s Guide for additional information on grouping.

Show Grouping Value: This property will only be shown if you have selected a node created by the Grouping functionality. You can choose for a group if the Data Field node used as a grouping criterion is shown in the table or form.

Sort By: You can select an element by which the data groups are sorted.

Sort Order: If you have selected an element for Sort By you can select, if the data should be sorted either ascending or descending.

Sort Data Type: If you have selected an element for Sort By the data is by default sorted as Text. That means that 12 will be shown after 111. If you need to sort numbers you should select Number as the sort data type

Break: This property allows you to insert a page break or a section break between every data group. If nothing is select, then the data groups will be shown continuously with no break. If you select New Page per Element, then a page break will be inserted between each element. If you select New Section per Element, a section break will be created for each data group. A section break allows changing the header/footer and resets the page number. You will typically use this option, if you want to print multiple documents (for example invoices or purchase orders) to a single PDF file.

The order in which the data elements are shown reflects the order of the columns in the table. If you want to reorder the columns you need to change the Insert Position box from Child to Same Level. Then drag the elements into the correct order.

Click the OK button to create the table. The inserted table should look like this:

Trx Number	Transaction Date	Trans Amount	Trans Amount Remaining
for-each G_INVOICES TRX_NUMBER	TRANSACTION_DATE	TRANS_AMOUNT	TRANS_AMOUNT_REMAINING  end G_INVOICES

The Template Builder creates two kinds of form fields:

Ÿ  Form fields representing data elements

Ÿ  Form fields with processing instructions for repeating table rows or document section

Form fields representing data elements are replaced with the data when the template is processed. The for-each G_INVOICES and end G_INVOICES form fields indicate a repeating section. The section of the document encapsulated by these two form fields is repeated, if the associated data element G_INVOICE is found repetitively in the data. Please refer to the Oracle XML Publisher User’s Guide for additional information on form fields.

You can now format these elements in Microsoft Word and add additional text. You can also move fields around as long as you keep them in the surrounding for-each G_INVOICES and end G_INVOICES processing instruction. If you remove one of these processing instructions your template may not work anymore with XML Publisher.

You now can preview the template again to review your current template (see above). You probably would like to change the alignment of the table columns or change the data type and format for the amounts. For example, select the TRANS_AMOUNT field, right-click and select Properties to see the Text Form Field Options dialog.

You may select the Number in the Type field, #,##0.00 for Number format and enter 1000 as the Default Number to achieve a more desirable format for currencies in US Dollar. We recommend not to use currency signs such as $ in the format string, since it may lead to difficulties in supporting multiple languages with a single template.

Formatting options defined in this dialog box are understood by Oracle XML Publisher and used to format your fields. Try to modify the template to get an idea which Word functions are supported by XML Publisher 5.5.

Creating Charts

The data used for creating the open balance letter is not well suited to create a chart. Close the previous Word document and open a new Word document. Then load the RetailSales.xml file in the samples\RTF templates\Sales Report folder by selecting Load XML Data from the Data menu.

This data contains retail sales data for different industries. We will create a chart that shows the sales per industry. XML Publisher does not support native Microsoft Word Charts. You need to create charts using the Template Builder.

On the Insert menu select Chart… to open the Chart Dialog. The following dialog will be displayed:

On the left tree view pane you NEED to select the XML element that is repeated for each data element. Please make sure that the Row element is selected. We now select INDUSTRY for Grouping, because we want to see the data accumulated by industry. Then we select sum of Sales for the measure. We select a Pie Chart as the Type and click OK to insert the chart into the RTF template.

The Template Builder will insert the placeholder image shown below for the chart. To change the size of the chart, you can just resize the placeholder image.

From the Preview menu select HTML to preview your template in a browser. From the Preview menu select PDF to preview the output in Acrobat Reader.  The preview will show the correct chart for the preview data:

Please refer to the Oracle XML Publisher User’s Guide for additional information regarding charts.

Next Steps

This concludes the quick tutorial.  Try to create your own template now.  Refer to the help file for additional information. You should also consult section 2 “RTF Templates” of the Oracle XML Publisher User’s Guide packaged with the Template Builder. The User’s Guide is accessible from the Start menu under:

Programs → XML Publisher → Template Builder → XML Publisher User’s Guide

We hope you like the Template Builder and appreciate your feedback.

Your Oracle XML Publisher team

 Welcome to Oracle XML Publisher

                Template Builder for Word 5.6.2!

Introduction

The Template Builder is an extension to Microsoft Word that simplifies the development of RTF templates. While the Template Builder is not required to create RTF templates, it provides many functions that will increase your productivity.

The Template Builder is tightly integrated with Microsoft Word and allows you to perform the following functions:

Ÿ  Insert data fields into your RTF templates

Ÿ  Insert data driven tables

Ÿ  Insert data driven forms

Ÿ  Insert data driven charts

Ÿ  Preview RTF templates with sample XML data

Ÿ  Browse and update the content of form fields

Ÿ  Extract boilerplate text into an XLIFF translation file and test translations

This help document assumes a basic working knowledge of Oracle XML Publisher. Please refer to the Oracle XML Publisher User’s Guide for additional information. This Template Builder version is based on XML Publisher 5.6.2.

If the template builder is installed correctly, you should see the menu bar shown below in the toolbar section:

If you are new to Template Builder for Word, please skip the next sections and continue with the Quick Tutorial.

What is new in Version 5.6.2?

Template Builder 5.6.2 provides the following new features:

Ÿ  XML Schema files (.xsd) as data definition

Ÿ  Drag & Drop for the Insert Field dialog

Ÿ  XML Publisher 5.6.2 compatible

Ÿ  Validation Function allows to check templates for problems.

Ÿ  Builder for simple XML Publisher Enterprise reports

Template Builder 5.6.2 allows you to load an XML Schema file (XSD) to describe the report data. The XML schema has the advantage of being complete (a sample xml may not contain all the fields from the data source). The Template Builder can generate dummy sample data for the preview from the XML Schema, but we still recommend that you upload a real sample XML.

What is new in Version 5.5?

Template Builder 5.5 provides the following new features:

Ÿ  Extract the template boilerplate into an industry standard XLIFF translation file

Ÿ  Preview templates with translation files

Ÿ  Sorting and page/section breaks for tables

Ÿ  XML Publisher 5.5 compatible

Quick Tutorial

This tutorial describes how you would typically create a simple layout template. You will create a letter that shows the outstanding balance for a customer. This letter contains a table of all unpaid or partially paid invoices.

Load Word File

Often a template starts from an example document in the form of a regular Microsoft Word document. For this tutorial go to the samples\RTF templates\Balance Letter folder under your XML Publisher installation directory (e.g. C:\Program Files\Oracle\XML Publisher Desktop) and load the file Balance Letter Start.rtf. This file represents a Word document that has not been marked up for use with Oracle XML Publisher.

Load Sample XML Data

From the Data menu select Load XML Data to load a sample XML document that contains the data fields you may want to insert into your RTF layout template. For this tutorial go to the samples\RTF templates\Balance Letter folder under your XML Publisher installation directory and load the file Balance.xml.

Insert Form Fields

You may want to select single data fields, such as the customer name and address at the header of the letter. Position the cursor in the Word document where you want to insert the Address. From the Insert menu select Field… to open the dialog window shown below.

Select the field Customer Name and click Insert to insert a form field for the customer name into the document. You may also insert the remaining address fields (such as Address Line1 to 4, City, State and Zip).

Starting with this version, you can also just drag the Customer Name field from the dialog and drop it into the Word document. NEW!

You can also select the fields, As of Date, Trx Currency Code and the C Inv Open Balance and insert them into the appropriate spaces in the document.

Note: As a beginner, you should use Insert Fields only for data fields that are unique (not repeating) in your document. Refer to the “Insert Table” section for additional information on how to insert repetitive fields

Preview Template

After adding fields to the document, you may want to test your template. Oracle XML Publisher Template Builder allows you to preview your template with your sample XML data. You can preview the output in Adobe Acrobat Reader (PDF), Microsoft Word file (RTF), Microsoft EXCEL (EXCEL) or a browser (HTML).

From the Preview menu select HTML to preview your template in a browser. From the Preview menu select PDF to preview your document in Acrobat Reader. You must have Adobe Acrobat Reader version 5.0 or higher installed to preview documents in PDF format. You can download Acrobat Reader for free at http://www.adobe.com/products/acrobat/readstep2.html.

The preview should show Vision Corporation instead of the field Customer Name.

Insert Table

You can build a table or a repeating section by inserting the elements and then adding additional form fields with processing instructions (for-each). Instead, the Template Builder offers an additional function that generates a table for you.

The Template Builder can generate for-each instructions either as descriptive or abbreviated. Abbreviated instructions are less invasive to the appearance of the document, while descriptive processing instructions make the template easier to understand. For this tutorial, select Options… from the Tools menu, switch to the Build tab and select Descriptive as shown below.

The document should include a table of all invoices that are not or only partially paid. On the Insert menu select Table/Form… to open the Table/Form Dialog. The following dialog will be displayed:

Scroll in the left Data Source view pane until you see the G Invoices element. Push the left mouse button and drag the G Invoices element over to the center Template tree view. When you drop an element with children (other elements below the element), the following pop-up dialog appears:

Note: A Node is a term used in tree views for an element in the tree view. For example, Customer Name, G Currency and G Invoices are all nodes. Transaction Date is also called a child node of G Invoices, and G Invoices is referred to as a parent node of Transaction Date.

To add multiple nodes, click Drop All Nodes with the left mouse button. When you select the "G Invoices" node, the dialog box should now look like this:

We only want to show the fields Trx Number, Transaction Date, Trans Amount, and Trans Amount Remaining. Select the field Trans Type in the center view and press the Delete button on your keyboard. Repeat the procedure for the other fields that you do not want to be included in the table (as shown in the screenshot below).

You should now click again on the G Invoices element in the center view. The Properties region on the right shows how the data fields will be formatted. You should see the following information:

For each group you can set the following properties that describe how a group (such as G Invoices) should be rendered by the template builder:

Style: Select Table to create a table around the data fields.

Grouping: Grouping is an advanced operation that allows you to re-group the data – for example by transaction date instead of currency. You can select the element that the data should be grouped by for this property. Please refer to the Oracle XML Publisher User’s Guide for additional information on grouping.

Show Grouping Value: This property will only be shown if you have selected a node created by the Grouping functionality. You can choose for a group if the Data Field node used as a grouping criterion is shown in the table or form.

Sort By: You can select an element by which the data groups are sorted.

Sort Order: If you have selected an element for Sort By you can select, if the data should be sorted either ascending or descending.

Sort Data Type: If you have selected an element for Sort By the data is by default sorted as Text. That means that 12 will be shown after 111. If you need to sort numbers you should select Number as the sort data type

Break: This property allows you to insert a page break or a section break between every data group. If nothing is select, then the data groups will be shown continuously with no break. If you select New Page per Element, then a page break will be inserted between each element. If you select New Section per Element, a section break will be created for each data group. A section break allows changing the header/footer and resets the page number. You will typically use this option, if you want to print multiple documents (for example invoices or purchase orders) to a single PDF file.

The order in which the data elements are shown reflects the order of the columns in the table. If you want to reorder the columns you need to change the Insert Position box from Child to Same Level. Then drag the elements into the correct order.

Click the OK button to create the table. The inserted table should look like this:

Trx Number	Transaction Date	Trans Amount	Trans Amount Remaining
for-each G_INVOICES TRX_NUMBER	TRANSACTION_DATE	TRANS_AMOUNT	TRANS_AMOUNT_REMAINING  end G_INVOICES

The Template Builder creates two kinds of form fields:

Ÿ  Form fields representing data elements

Ÿ  Form fields with processing instructions for repeating table rows or document section

Form fields representing data elements are replaced with the data when the template is processed. The for-each G_INVOICES and end G_INVOICES form fields indicate a repeating section. The section of the document encapsulated by these two form fields is repeated, if the associated data element G_INVOICE is found repetitively in the data. Please refer to the Oracle XML Publisher User’s Guide for additional information on form fields.

You can now format these elements in Microsoft Word and add additional text. You can also move fields around as long as you keep them in the surrounding for-each G_INVOICES and end G_INVOICES processing instruction. If you remove one of these processing instructions your template may not work anymore with XML Publisher.

You now can preview the template again to review your current template (see above). You probably would like to change the alignment of the table columns or change the data type and format for the amounts. For example, select the TRANS_AMOUNT field, right-click and select Properties to see the Text Form Field Options dialog.

You may select the Number in the Type field, #,##0.00 for Number format and enter 1000 as the Default Number to achieve a more desirable format for currencies in US Dollar. We recommend not to use currency signs such as $ in the format string, since it may lead to difficulties in supporting multiple languages with a single template.

Formatting options defined in this dialog box are understood by Oracle XML Publisher and used to format your fields. Try to modify the template to get an idea which Word functions are supported by XML Publisher 5.5.

Creating Charts

The data used for creating the open balance letter is not well suited to create a chart. Close the previous Word document and open a new Word document. Then load the RetailSales.xml file in the samples\RTF templates\Sales Report folder by selecting Load XML Data from the Data menu.

This data contains retail sales data for different industries. We will create a chart that shows the sales per industry. XML Publisher does not support native Microsoft Word Charts. You need to create charts using the Template Builder.

On the Insert menu select Chart… to open the Chart Dialog. The following dialog will be displayed:

On the left tree view pane you NEED to select the XML element that is repeated for each data element. Please make sure that the Row element is selected. We now select INDUSTRY for Grouping, because we want to see the data accumulated by industry. Then we select sum of Sales for the measure. We select a Pie Chart as the Type and click OK to insert the chart into the RTF template.

The Template Builder will insert the placeholder image shown below for the chart. To change the size of the chart, you can just resize the placeholder image.

From the Preview menu select HTML to preview your template in a browser. From the Preview menu select PDF to preview the output in Acrobat Reader.  The preview will show the correct chart for the preview data:

Please refer to the Oracle XML Publisher User’s Guide for additional information regarding charts.

Next Steps

This concludes the quick tutorial.  Try to create your own template now.  Refer to the help file for additional information. You should also consult section 2 “RTF Templates” of the Oracle XML Publisher User’s Guide packaged with the Template Builder. The User’s Guide is accessible from the Start menu under:

Programs → XML Publisher → Template Builder → XML Publisher User’s Guide

We hope you like the Template Builder and appreciate your feedback.

Your Oracle XML Publisher team