Infolinks

Thursday 21 June 2012

XML Publisher

XML Publisher


XML Publisher

Creating an RTF Template
XML Input File
<?xml version="1.0" encoding="WINDOWS-1252" ?>
- <VENDOR_REPORT>
- <LIST_G_VENDOR_NAME>
- <G_VENDOR_NAME>
<VENDOR_NAME>COMPANY A</VENDOR_NAME>
- <LIST_G_INVOICE_NUM>
- <G_INVOICE_NUM>
<SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
<GL_DATE>10-NOV-03</GL_DATE>
<INV_TYPE>Standard</INV_TYPE>
<INVOICE_NUM>031110</INVOICE_NUM>
<INVOICE_DATE>10-NOV-03</INVOICE_DATE>
<INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>
<ENT_AMT>122</ENT_AMT>
<ACCTD_AMT>122</ACCTD_AMT>
<VAT_CODE>VAT22%</VAT_CODE>
</G_INVOICE_NUM>
</LIST_G_INVOICE_NUM>
<ENT_SUM_VENDOR>1000.00</ENT_SUM_VENDOR>
<ACCTD_SUM_VENDOR>1000.00</ACCTD_SUM_VENDOR>
</G_VENDOR_NAME>
</LIST_G_VENDOR_NAME>
<ACCTD_SUM_REP>108763.68</ACCTD_SUM_REP>
<ENT_SUM_REP>122039</ENT_SUM_REP>
</VENDOR_REPORT>
XML files are composed of elements. Each tag set is an element. For example
<INVOICE_DATE></INVOICE_DATE> is the invoice date element. "INVOICE_DATE"
is the tag name. The data between the tags is the value of the element. For example, the
value of INVOICE_DATE is "10-NOV-03".
Enter placeholders in your document using the following syntax:
<?XML element tag name?>
EX : <?VENDOR_NAME?>
Form Field Method
Use Microsoft Word's Text Form Field Options window to insert the placeholder tags:
1. Enable the Forms toolbar in your Microsoft Word application.
2. Position your cursor in the place you want to create a placeholder.
3. Select the Text Form Field toolbar icon. This action inserts a form field area in your
document.
4. Double-click the form field area to invoke the Text Form Field Options dialog box.
5. (Optional) Enter a description of the field in the Default text field. The entry in this
field will populate the placeholder's position on the template.
For the example, enter "Supplier 1".
6. Select the Add Help Text button.
7. In the help text entry field, enter the XML element's tag name using the syntax:
<?XML element tag name?>
Template Field Name Default Text Entry (Form Field Method)
<?INVOICE_NUM?>
<?INVOICE_DATE?>
<?GL_DATE?>
<?INVOICE_CURRENCY_CODE?>
<?ENT_AMT?>
<?ACCTD_AMT?>
<?ENT_SUM_VENDOR?>
<?ACCTD_SUM_VENDOR?>
LOOPS: Without loops we will get only one record for output and using loops we can expect expected results.
For-each is a loop:
Insert the following tag before the first element:
<?for-each:XML group element tag name?>
Insert the following tag after the final element:
<?end for-each?>
<?for-each:G_VENDOR_NAME?> <?sort:VENDOR_NAME?>
<?INVOICE_NUM?>
<?INVOICE_DATE?>
<?GL_DATE?>
<?INVOICE_CURRENCY_CODE?>
<?ENT_AMT?>
<?ACCTD_AMT?>
<?ENT_SUM_VENDOR?>
<?ACCTD_SUM_VENDOR?>
<?end for-each?>
Multiple or Complex Headers and Footers:
Using start body n end body we can divide header n footer.
<?start:body?>
<?end body?>
1. Insert <?start:body?> before the Suppliers group tag:
<?for-each:G_VENDOR_NAME?>
2. Insert <?end body?> after the Suppliers group closing tag: <?end for-each?>
Images and Charts
url:{'http://image location'}
For example, enter:
url:{'${OA_MEDIA}/image name'}
you can retrieve the chart title from an XML tag by using the following syntax:
<Title text="{CHARTTITLE}" visible="true" horizontalAlighment="CENTER"/>
where "CHARTTITLE" is the XML tag name that contains the chart title. Note that the
tag name is enclosed in curly braces.
The next section defines the column and row labels:
<LocalGridData colCount="{count(//division)}" rowCount="1">
<RowLabels>
<Label>Total Sales $1000s</Label>
</RowLabels>
<ColLabels>
<xsl:for-each select="//division">
<Label>
<xsl:value-of select="name"/>
</Label>
</xsl:for-each>
</ColLabels>
The LocalGridData element has two attributes: colCount and rowCount. These
define the number of columns and rows that will be shown at runtime. In this example,
a count function calculates the number of columns to render:
colCount="{count(//division)}"
The rowCount has been hard-coded to 1. This value defines the number of sets of data
to be charted. In this case it is 1.
XSL : XML STYLE SHEET LANGUAGE
Using xsl language we can design the template.
<ColLabels>
<xsl:for-each select="//division">
<Label>
<xsl:value-of select="name"/>
</Label>
</xsl:for-each>
</ColLabels>
This code loops through the <division> group and inserts the value of the <name>
element into the <Label> tag. At runtime, this will generate the following XML:
<ColLabels>
<Label>Groceries</Label>
<Label>Toys</Label>
<Label>Cars</Label>
<Label>Hardware</Label>
<Label>Electronics</Label>
</ColLabels>
The next section defines the actual data values to chart:
<DataValues>
<RowData>
<xsl:for-each select="//division">
<Cell>
<xsl:value-of select="totalsales"/>
</Cell>
</xsl:for-each>
</RowData>
</DataValues>
Replicate a Shape
<?for-each@shape:SHAPE_GROUP?>
<?shape-offset-y:(position()-1)*100?>
<?end for-each?>
where
for-each@shape opens the for-each loop for the shape context
Add Text to a Shape
<?shape-text:SHAPETEXT?>
Add Text Along a Path
<?shape-text-along-path:SHAPETEXT?>
where SHAPETEXT is the element from the XML data. At runtime the value of the
element SHAPETEXT will be inserted above and along the line
Moving a Shape
You can move a shape or transpose it along both the x and y-axes based on the XML
data. For example to move a shape 200 pixels along the y-axis and 300 along the x-axis,
enter the following commands in the property dialog of the shape:
<?shape-offset-x:300?>
<?shape-offset-y:200?>
Rotating a Shape
To rotate a shape about a specified axis based on the incoming data, use the following
command:
<?shape-rotate:ANGLE;'POSITION'?>
Skewing a Shape
You can skew a shape along its x or y axis using the following commands:
<?shape-skew-x:ANGLE;'POSITION'?>
<?shape-skew-y:ANGLE;'POSITION'?>
Changing the Size of a Shape
You can change the size of a shape using the appropriate commands either along a
single axis or both axes. To change a shape's size along both axes, use:
<?shape-size:RATIO?>
Combining Commands
<for-each@shape:SHAPE_GROUP?>
<?shape-text:position()?>
<?shape-offset-y:position()*50?>
<?shape-rotate:5;'center/center'?>
<?shape-size-x:position()+1?>
<end for-each?>
ADD Page Total
<?add-page-total:TotalFieldName;'element'?>
Show Page Total
<?show-page-total:TotalFieldName;'Oracle-number-format'?>
Page Breaks
To create a page break after the occurrence of a specific element use the
"split-by-page-break" alias. This will cause the report output to insert a hard page break
between every instance of a specific element.
To insert a page break between each occurrence of a group, insert the
"split-by-page-break" form field within the group immediately before the <?end
for-each?> tag that closes the group. In the Help Text of this form field enter the
syntax:
<?split-by-page-break:?>
Initial Page Number
Some reports require that the initial page number be set at a specified number
Use the following syntax in your template to set the initial page number:
<?initial-page-number:pagenumber?>
where pagenumber is the XML element or parameter that holds the numeric value.
EX:
<REPORT>
<PAGESTART>200<\PAGESTART>
....
</REPORT>
Enter the following in your template:
<?initial-page-number:PAGESTART?>
Example 2 - Set page number by passing a parameter value
If you define a parameter called PAGESTART, you can pass the initial value by calling
the parameter.
Enter the following in your template:
<?initial-page-number:$PAGESTART?>
Last Page Only Content
XML Publisher provides this ability. To utilize this feature, you must:
1. Create a section break in your template to ensure the content of the final page is
separated from the rest of the report.
2. Insert the following syntax on the final page:
<?start@last-page:body?>
<?end body?>
To end on an even page with layout:
Insert the following syntax in a form field in your template:
<?section:force-page-count;'end-on-even-layout'?>
To end on an odd page layout:
<?section:force-page-count;'end-on-odd-layout'?>
If you do not have layout requirements for the final page, but would like a blank page
ejected to force the page count to the preferred odd or even, use the following syntax:
<?section:force-page-count;'end-on-even'?>
or
<?section:force-page-count;'end-on-odd'?>
Generating Bookmarks in PDF Output
To create links for a static table of contents:
Enter the syntax:
<?copy-to-bookmark:?>
directly above your table of contents and
2-58 Oracle XML Publisher Core Components Guide
<?end copy-to-bookmark:?>
directly below the table of contents.
• To create links for a dynamic table of contents:
Enter the syntax:
<?convert-to-bookmark:?>
directly above the table of contents and
<?end convert-to-bookmark:?>
directly below the table of contents.
Drop-Down Form Field Options
<?contintentIndex?>
The drop-down form field for the
continentIndex element. See the preceding
description for its contents. At runtime, the value of
the XML element is replaced with the value it is
cross-referenced to in the drop-down form field.
If Statements
<?if:condition?>
<?end if?>.
For example, to set up the Payables Invoice Register to display invoices only when the
Supplier name is "Company A", insert the syntax
<?if:VENDOR_NAME='COMPANYA'?>
before the Supplier field on the template.
Enter the <?end if?> tag after the invoices table.
EX1:
<?if:SUCCESS='N'?>not<?end if?>
<?if@inlines:SUCCESS='N'?>not<?end if?>
If-then-Else Statements
<?xdofx:if element_condition then result1 else result2 end if?>
IF X=0 THEN
Y=2
ELSE
Y=3
END IF
<?xdofx:if AMOUNT > 1000 then 'Higher'
else
if AMOUNT < 1000 then 'Lower'
else
'Equal'
end if?>
Choose Statements
Use the choosewhen, and otherwise elements to express multiple conditional tests.
If certain conditions are met in the incoming XML data then specific sections of the
template will be rendered. This is a very powerful feature of the RTF template. In
regular XSL programming, if a condition is met in the choose command then further
XSL code is executed. In the template, however, you can actually use visual widgets in
the conditional flow (in the following example, a table).
Use the following syntax for these elements:
<?choose:?>
<?when:expression?>
<?otherwise?>
The code to highlight the debit column as shown in the table is:
<?if:debit>1000?>
<xsl:attribute
xdofo:ctx="block" name="background-color">red
</xsl:attribute>
<?end if?>
Field Form Field Entry
<?for-each:CD?>
TITLE <?TITLE?>
ARTIST <?ARTIST?>
<?end for-each?>
(star shape) Web Tab Entry:
<?for-each@shape:xdoxslt:foreach_number($_XDOCTX,0
,USER_RATING,1)?>
<?shape-offset-x:(position()-1)*25?>
<?end for-each?>
Using Functions
Group by TmpRng <?for-each-group:temp;floor(degree div 10?>
<?sort:floor(degree div 10)?>
Range <?concat(floor(degree div 10)*10,' F to ',floor(degree
div 10)*10+10, F')?>
Months <?count(current-group())?>
End TmpRng <?end for-each-group?>
Securing a PDF Output
For this example, suppose you want to use a password from the XML data to secure the
PDF output document. The XML data is as follows:
Creating an RTF Template 2-93
<PO>
<security>true</security>
<password>welcome</password>
<PO_DETAILS>
..
</PO>
In the Properties dialog set two properties: pdf-security to set the security feature as
enabled or not, and pdf-open-password to set the password. Enter the following in
the Properties dialog:
Name: xdo-pdf-security
Type: Text
Value: {/PO/security}
Name: xdo-pdf-open-password
Type: Text
Value: {/PO/password}
Dynamic Data Columns
Dynamic Column Header
<?split-column-header:group element name?>
Use this tag to define which group to split for the column headers of a table.
• Dynamic Column <?split-column-data:group element name?>
Use this tag to define which group to split for the column data of a table.
• Dynamic Column Width
<?split-column-width:name?> or
<?split-column-width:@width?>
Defining Columns to Repeat Across Pages
If your table columns expand horizontally across more than one page, you can define
how many row heading columns you want to repeat on every page. Use the following
syntax to specify the number of columns to repeat:
<?horizontal-break-table:number?>
where number is the number of columns (starting from the left) to repeat.
Note that this functionality is supported for PDF output only..
Time Zone Specification
<?format-date:hiredate;'LONG_TIME_TZ';'Asia/Shanghai'?>
Using External Fonts
<font family="MyFontName" style="normal" weight="normal">
<truetype path="\user\fonts\MyFontName.ttf"/>
</font>
Register the Barcode Encoding Class
Use the following syntax in a form field in your template to register the barcode
encoding class:
<?register-barcode-vendor:java_class_name;barcode_vendor_id?>
This command requires a Java class name (this will carry out the encoding) and a
barcode vendor ID as defined by the class. This command must be placed in the
template before the commands to encode the data in the template. For example:
<?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.barcoder.Ba
rcodeUtil';'XMLPBarVendor'?>
where
oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtil is the Java
class and
XMLPBarVendor is the vendor ID that is defined by the class.
Using XSL Elements
You can use any XSL element in your template by inserting the XSL syntax into a form
field.
If you are using the basic RTF method, you cannot insert XSL syntax directly into your
template. XML Publisher has extended the following XSL elements for use in RTF
templates.
To use these in a basic-method RTF template, you must use the XML Publisher Tag
form of the XSL element. If you are using form fields, use either option.
Apply a Template Rule
Use this element to apply a template rule to the current element's child nodes.
XSL Syntax<xsl:apply-templates select="name">
XML Publisher Tag<?apply:name?> This function applies to <xsl:template-match="n"> where is the element name.
Copy the Current Node
Use this element to create a copy of the current node.
XSL Syntax: <xsl:copy-of select="name">
XML Publisher Tag: <?copy-of:name?>
Call Template
Use this element to call a named template to be inserted into or applied to the current
template. For example, use this feature to render a table multiple times.
XSL Syntax<xsl:call-template name="name">
XML Publisher Tag<?call-template:name?>
Template Declaration
Use this element to apply a set of rules when a specified node is matched.
XSL Syntax<xsl:template name="name">
XML Publisher Tag: <?template:name?>
Variable Declaration
Use this element to declare a local or global variable.
XSL Syntax: <xsl:variable name="name">
XML Publisher Tag: <?variable:name?>
Example:
<xsl:variable name="color" select="'red'"/>
Assigns the value "red" to the "color" variable. The variable can then be referenced in
the template.
Import Stylesheet
Use this element to import the contents of one style sheet into another.
Note: An imported style sheet has lower precedence than the importing
style sheet.
XSL Syntax: <xsl:import href="/url">
XML Publisher Tag: <?import:url?>
Define the Root Element of the Stylesheet
This and the <xsl:stylesheet> element are completely synonymous elements. Both
are used to define the root element of the style sheet.
Note: An included style sheet has the same precedence as the including
style sheet.
XSL Syntax<xsl:stylesheet xmlns:x="url">
XML Publisher Tag: <?namespace:x=url?>
API Method Call
The following is an example of an API method call:
String xmlInputPath = "c:\\tmp\\toc.xml";
String pdfOutputPath = "c:\\tmp\\final_book.pdf";
PDFBookBinder bookBinder = new PDFBookBinder(xmlInputPath,
pdfOutputPath);
bookBinder.setConfig(new Properties());
bookBinder.process();
Hierarchy and Elements of the Document Processor XML File
The Document Processor XML file has the following element hierarchy:
Requestset
request
delivery
filesystem
print
fax
number
email
message
document
background
text
pagenumber
template
data
Element Attributes Description
requestset xmlns Root element must contain [xmlns:xapi="http://xml
ns.oracle.com/oxp/xapi/"] block
The version is not required,
but defaults to "1.0".
request N/A lement that contains the data and template processing definitions.
delivery N/A Defines where the generated output is sent.
document output-type Specify one output that can have several template
elements. The output-type
attribute is optional. Valid
values are:
pdf (Default)
rtf
html
excel
text
filesystem output Specify this element to save the output to the file system.
Define the directory path in the output attribute.
print • printer
• server-alias
The print element can occur multiple times under delivery to print one document to several printers. Specify the printer attribute as a URI, such as: "ipp://myprintserver:6
31/printers/printernam e"
fax • server
• server-alias
Specify a URI in the server attribute, for example: "ipp://myfaxserver1:63 1/printers/myfaxmachin e" number The number element can occur multiple times to list
multiple fax numbers. Each element occurrence must contain only one number.
email • server
• port
• from
• reply-to
• server-alias
Specify the outgoing mail server (SMTP) in the server attribute. Specify the mail server port in the port attribute.
message • to
• cc
• bcc
• attachment
• subject
The message element can be placed several times under the email element. You can
specify character data in the message element. You can specify multiple e-mail addresses in the tocc and bcc attributes separated by a comma. The attachment value is either true or false (default). If attachment is true, then a generated document will be
attached when the e-mail is sent. The subject attribute is optional.
background where If the background text is required on a specific page, then set the where value to the page numbers required. The page index starts at 1. The default value is 0, which places the background on all pages.
text • title
• default
Specify the watermark text in the title value. A default value of "yes" automatically draws the watermark with forward slash type. The default value is yes.
pagenumber • initial-page-index
• initial-value
• x-pos
• y-pos
The initial-page-index default value is 0. The initial-value default value is 1. "Helvetica" is used for the page number font. The x-posprovides lower left x position. The y-pos provides lower left y position.
template • locale
• location
• type
Contains template information. Valid values for the type attribute are pdf rtf
xsl-fo etext The default value is "pdf".
Data location
Define the location attribute to specify the location of the data, or attach
the actual XML data with subelements. The default value of location is "inline". It the location points to either an XML file or a URL, then the data should contain an XML declaration with the proper encoding. If the location attribute is not specified, the data
element should contain the subelements for the actual data. This must not include an
XML declaration.
Defining multiple templates and data
The following example builds on the previous examples by applying two data sources
to one template and two data sources to a second template, and then merging the two
into a single output file. Note that when merging documents, the output-type must
be "pdf".
Example
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\tmp\outfile3.pdf"/>
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf"
location="d:\mywork\template1.pdf">
<xapi:data>
<field1>The first set of data</field1>
</xapi:data>
<xapi:data>
<field1>The second set of data</field1>
</xapi:data>
</xapi:template>
<xapi:template type="pdf"
location="d:\mywork\template2.pdf">
<xapi:data>
<field1>The third set of data</field1>
</xapi:data>
<xapi:data>
<field1>The fourth set of data</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
Retrieving templates over HTTP
This sample is identical to the previous example, except in this case the two templates
are retrieved over HTTP:
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\temp\out4.pdf"/>
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf"
location="http://your.server:9999/templates/template1.pdf">
<xapi:data>
<field1>The first page data</field1>
</xapi:data>
<xapi:data>
<field1>The second page data</field1>
</xapi:data>
</xapi:template>
<xapi:template type="pdf"
location="http://your.server:9999/templates/template2.pdf">
<xapi:data>
<field1>The third page data</field1>
</xapi:data>
<xapi:data>
<field1>The fourth page data</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
Defining two data sets
The following example shows how to define two data sources to merge with one
template to produce one output file delivered to the file system:
Example
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\tmp\outfile.pdf"/>
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf"
location="d:\mywork\template1.pdf">
<xapi:data>
<field1>The first set of data</field1>
</xapi:data>
<xapi:data>
<field1>The second set of data</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
Simple XML sample
The following sample is a simple example that shows the definition of one template (
template1.pdf) and one data source (data1) to produce one output file (
outfile.pdf) delivered to the file system: Example
<?xml version="1.0" encoding="UTF-8" ?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\tmp\outfile.pdf" />
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf" location="d:\mywork\template1.pdf">
<xapi:data>
<field1>data1</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>


<dataTemplate name="INTDOMHOLD" defaultPackage="" description="INTDOMHOLD">
<parameters>
<parameter name="p_hold" dataType="VARCHAR2" defaultValue=""/>
</parameters>
<dataQuery>
<sqlStatement name="Q1" dataSourceRef=""><![CDATA[SELECT ooh.order_number
,ooh.flow_status_code header_status
,hp.party_name customer_name
, ool.line_number ||
'.' ||
ool.shipment_number line
,ott.NAME line_type
,ool.unit_selling_price expected_price
,ool.flow_status_code line_status
,ool.ordered_item
,'Yes' "Invoiceable Item"
,DECODE( oh.released_flag, 'N', 'Yes', 'No' ) hold_active
,ohd.NAME hold_name
,TO_CHAR( ohs.hold_until_date, 'DD-Mon-YYYY' ) hold_until_date
,TO_CHAR( ohs.hold_until_date, 'DD-Mon-YYYY' ) hold_release_date
,( SELECT rct.trx_number
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' )invoice
,( SELECT TO_CHAR( rct.trx_date, 'DD-Mon-YYYY' )
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' ) invoice_date
FROM oe_order_lines_all ool
,oe_order_headers_all ooh
,oe_order_holds_all oh
,mtl_system_items_b msi
,apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_cust_site_uses_all hcsu
,oe_transaction_types_tl ott
,oe_hold_sources_all ohs
,oe_hold_definitions ohd
WHERE ooh.header_id = ool.header_id
AND ool.org_id = msi.organization_id
AND ool.ordered_item = msi.segment1
AND msi.invoiceable_item_flag = 'Y'
AND ool.flow_status_code IN( 'CLOSED', 'INVOICE_HOLD' )
AND ool.line_type_id = ott.transaction_type_id
AND ott.NAME = 'OKS Billing Line'
AND ool.header_id = 11878
AND ool.line_id = oh.line_id
AND ooh.header_id = oh.header_id
AND oh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND oh.released_flag = 'N'
AND ool.invoice_to_org_id = hcsu.site_use_id(+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+)
AND hca.party_id = hp.party_id(+)
AND hcas.cust_account_id = hca.cust_account_id(+)
AND hcsu.site_use_code(+) = 'BILL_TO'
AND ohd.NAME = NVL( :p_hold, ohd.NAME )
-- AND ohs.hold_until_date = NVL( :p_to_release, ohs.hold_until_date )
AND NOT EXISTS(
SELECT 1
FROM ra_customer_trx_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND sales_order_line = ool.line_number
AND sales_order = ooh.order_number
AND interface_line_attribute2 = 'OKS Billing Line' )
UNION
SELECT ooh.order_number
,ooh.flow_status_code header_status
,hp.party_name customer_name
, ool.line_number ||
'.' ||
ool.shipment_number line
,ott.NAME line_type
,ool.unit_selling_price expected_price
,ool.flow_status_code line_status
,ool.ordered_item
,'Yes' "Invoiceable Item"
,DECODE( oh.released_flag, 'N', 'Yes', 'No' ) hold_active
,ohd.NAME hold_name
,TO_CHAR( ohs.hold_until_date, 'DD-Mon-YYYY' ) hold_until_date
,TO_CHAR( ohr.creation_date, 'DD-Mon-YYYY' ) hold_release_date
,( SELECT rct.trx_number
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' ) invoice
,( SELECT TO_CHAR( rct.trx_date, 'DD-Mon-YYYY' )
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' ) invoice_date
FROM oe_order_lines_all ool
,oe_order_headers_all ooh
,oe_order_holds_all oh
,mtl_system_items_b msi
,apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_cust_site_uses_all hcsu
,oe_transaction_types_tl ott
,oe_hold_sources_all ohs
,oe_hold_definitions ohd
,oe_hold_releases ohr
WHERE ooh.header_id = ool.header_id
AND ool.org_id = msi.organization_id
AND ool.ordered_item = msi.segment1
AND msi.invoiceable_item_flag = 'Y'
AND ool.flow_status_code IN( 'CLOSED', 'INVOICE_HOLD' )
AND ool.line_type_id = ott.transaction_type_id
AND ott.NAME = 'OKS Billing Line'
AND ool.header_id = 11878
AND ool.line_id = oh.line_id
AND ooh.header_id = oh.header_id
AND oh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND oh.released_flag = 'Y'
AND ohr.hold_release_id = oh.hold_release_id
AND ool.invoice_to_org_id = hcsu.site_use_id(+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+)
AND hca.party_id = hp.party_id(+)
AND hcas.cust_account_id = hca.cust_account_id(+)
AND hcsu.site_use_code(+) = 'BILL_TO'
AND ohd.NAME = NVL( :p_hold, ohd.NAME )
-- AND ohr.creation_date = NVL( :p_to_release, ohr.creation_date )
AND NOT EXISTS(
SELECT 1
FROM ra_customer_trx_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND sales_order_line = ool.line_number
AND sales_order = ooh.order_number
AND interface_line_attribute2 = 'OKS Billing Line')]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_ORDER" source="Q1" groupFilter="">
<element name="ORDER" value="ORDER" function=""/>
<element name="HEADER_STATUS" value="HEADER_STATUS" function=""/>
<element name="CUSTOMER" value="CUSTOMER" function=""/>
<element name="LINE" value="LINE" function=""/>
<element name="LINE_TYPE" value="LINE_TYPE" function=""/>
<element name="EXPECTED_PRICE" value="EXPECTED_PRICE" function=""/>
<element name="LINE_STATUS" value="LINE_STATUS" function=""/>
<element name="ORDERED_ITEM" value="ORDERED_ITEM" function=""/>
<element name="HOLD_ACTIVE" value="HOLD_ACTIVE" function=""/>
<element name="HOLD_NAME" value="HOLD_NAME" function=""/>
<element name="HOLD_UNTIL_DATE" value="HOLD_UNTIL_DATE" function=""/>
<element name="HOLD_RELEASE_DATE" value="HOLD_RELEASE_DATE" function=""/>
<element name="INVOICE" value="INVOICE" function=""/>
<element name="INVOICE_DATE" value="INVOICE_DATE" function=""/>
</group>
</dataStructure>
</dataTemplate>

6 comments:

  1. Currently I have made a Project Timeline Report

    In which I have a chart whose X axis is a charater column and Y axis should be Date

    Can I add a date column on Y axis, currently It is giving me error

    ReplyDelete
  2. I have HTML tags in data which is stored in the database, how could I display the processed HTML in PDF output?

    ReplyDelete
  3. Can you please help me how can we define in XSL template for below code.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Issue : Column AG has is not displaying correct values, by delivery, and the report is missing rows of data for rows where the quantity is supposed to be divided by the item container relationship for carton.
    • Line quantities, divided by the carton relationship, are not having additional lines added to the report. In essence, each carton needs a line on the report
    • Carton counts should be based on the number of cartons within a delivery
    • if a delivery only has 1 line, and the quantity, divided by the carton value is one or less, than there would be one line on the report, representing one carton, the count would be 1/1
    • if a delivery only has 1 line, and the quantity, divided by the carton value is two or less, than there would be a second line on the report, so two cartons, count of ½, 2/2
    • if the same delivery had a second item, with only 1 carton, then for the above example there would be 3 lines, count of 1/3, 2/3, 3/3.

    I need answer for above one .

    ReplyDelete