Showing posts with label REPORTS. Show all posts
Showing posts with label REPORTS. Show all posts

Tuesday, 27 July 2021

link between PO and Projects Query in oracle fusion (po_headers_all and pjf_projects_all_b)

 SELECT

ph.segment1 CommitmentNumber
,pl.line_num ItemNumber
,(SELECT DISTINCT esi.item_number
  FROM egp_system_items_b esi
  WHERE esi.inventory_item_id=pl.item_id) ITEM
,pl.item_description ItemDescription
,plt.line_type ITEM_TYPE
,gcc.segment1||gcc.segment2||gcc.segment3 ACCT_UNIT
,gcc.segment4 ACCT_CATEGORY
,ppa.segment1 ActivityNumber
,pl.list_price ENT_UNIT_CST
,pll.quantity QUANTITY
,(pll.quantity*pl.list_price) CommitmentItemAmount
,pll.need_by_date POSTED_DATE
,ps.segment1 CompanyNumber
,hp.party_name Description
,ppa.attribute3 ProjectIdentifier
,ph.creation_date CommitmentDate
,ppn.full_name BUYER_CODE
,pld.location_code REQ_LOCATION
,pl.attribute1||','||pl.attribute2 BudgetLineItem
,'Purchase Order' CommitmentType
FROM po_headers_all ph,po_lines_all pl
,po_line_types_tl plt
,gl_code_combinations gcc
,po_distributions_all pd
,pjf_projects_all_b ppa
,po_line_locations_all pll
,poz_suppliers ps
,hz_parties hp
,per_person_names_f ppn
,per_location_details_f_tl pld
,per_location_details_f pldf
WHERE ph.po_header_id=pl.po_header_id
AND plt.line_type_id=pl.line_type_id
AND plt.language=USERENV('LANG')
AND pd.po_header_id=pl.po_header_id
AND pd.po_line_id=pl.po_line_id
AND pd.code_combination_id=gcc.code_combination_id
AND gcc.enabled_flag='Y'
AND SYSDATE BETWEEN NVL(gcc.start_date_active,SYSDATE) AND NVL(gcc.end_date_active,SYSDATE)
AND pd.Pjc_Project_id=ppa.project_id
AND pll.po_header_id=pl.po_header_id
AND pll.po_line_id=pl.po_line_id
AND ph.vendor_id=ps.vendor_id
AND ps.party_id=hp.party_id
AND ppn.person_id=ph.agent_id
AND ppn.name_type='GLOBAL'
AND SYSDATE BETWEEN NVL(ppn.effective_start_date,SYSDATE) AND NVL(ppn.effective_end_date,SYSDATE)
AND ps.enabled_flag='Y'
AND SYSDATE BETWEEN NVL(ps.start_date_active,SYSDATE) AND NVL(ps.end_date_active,SYSDATE)
AND pldf.location_id=pd.Deliver_to_location_ID
AND pldf.active_status='A'
AND SYSDATE BETWEEN NVL(pldf.effective_start_date,SYSDATE) AND NVL(pldf.effective_end_date,SYSDATE)
AND pld.location_details_id=pldf.location_details_id
AND pld.language=USERENV('LANG')

Friday, 3 March 2017

BARCODE IN XML PUBLISHER REPORTS ORACLE R12

BARCODE IN XML PUBLISHER
1.     Client Setup
·         Get the font IDAutomation font from idautomation
·         Place the IDAutomation font under c:\Windows\Fonts.
·         Select IDAutomation font for Barcode fields in XML Publisher Template.
·         Calling encoder in the template.(Only if vendor specific fonts and java encoder is used else ignore)
·         Add following expression in your template, It can be added directly to template or as a value to Form Field.
<?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtilaaa';'XMLPBarVendor'?>




·         Add format-barcode syntax to barcode field. Replace BARCODE in below syntax with your xml field.
*<?format-barcode:BARCODE;'code128a';'XMLPBarVendor'?>*






2.     Server Setup -- Only needed if you have vendor specific barcode fonts else ignore.
If vendor specific fonts are used, java encoder will be provided along with font which will be recognized by external device.
Below imports have to be added to the vendor provided java encoder.
  package oracle.apps.xdo.template.rtf.util.barcoder;
import java.util.Hashtable;
import java.lang.reflect.Method;
import oracle.apps.xdo.template.rtf.util.XDOBarcodeEncoder;
import oracle.apps.xdo.common.log.Logger;
// This class name will be used in the register vendor field in the template.
public class BarcodeUtil implements XDOBarcodeEncoder
// The class implements the XDOBarcodeEncoder interface
{
// This is the barcode vendor id that is used in the register vendor field and
// format-barcode fields
public static final String BARCODE_VENDOR_ID = "XMLPBarVendor";
// The hastable is used to store references to the encoding methods
public static final Hashtable ENCODERS = new Hashtable(10);
// The BarcodeUtil class needs to be instantiated
public static final BarcodeUtil mUtility = new BarcodeUtil();
// This is the main code that is executed in the class, it is loading the methods
// for the encoding into the hashtable. In this case we are loading the three code128
// encoding methods we have created.
static {
try {
Class[] clazz = new Class[] { "".getClass() } ;
ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));
ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));
ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));
} catch (Exception e) {
// This is using the XML Publisher logging class to push errors to the XMLP log file.
Logger.log(e,5);
}
}
// The getVendorID method is called from the template layer at runtime to ensure the correct
// encoding method are used
public final String getVendorID()
{
return BARCODE_VENDOR_ID;
}
// The isSupported method is called to ensure that the encoding method
// called from the template is actually present in this class. If not
// then XMLP will report this in the log.
public final boolean isSupported(String s)
{
if(s != null)
return ENCODERS.containsKey(s.trim().toLowerCase());
else
return false;
}
// The encode method is called to then call the appropriate encoding method,
// in this example the code128a/b/c methods.
public final String encode(String s, String s1)
{
if(s != null && s1 != null)
{
try
{
Method method = (Method)ENCODERS.get(s1.trim().toLowerCase());
if(method != null)
return (String)method.invoke(this, new Object[] {
s
});
else
return s;
}
catch(Exception exception)
{
Logger.log(exception,5);
}
return s;
} else
{
return s;
}
}
/** Add Vendor Method for Code128a */
public static final String code128a( String DataToEncode )
{
return Printable_string;
}
}
 
 Generate class file from java code and place it under OA_JAVA/oracle/apps/xdo/template/rtf/util/barcoder. If barcoder directory doesn't exist create one.

$ cd $OA_JAVA/oracle/apps/xdo/template/rtf/util

$ mkdir barcoder
 Change permissions of the barcoder directory to 777/755.

3.     XML Publisher Font Setup
No longer XML publisher fonts needed to be placed on the server. They can be uploaded and used from XML publisher font file and font mappings.



Navigate to XML Publisher responsibility.
Go to Administration Tab.
Click on Font Files and create font file


HERE THE FONT IS IDAutomationSHcC128M



Font Name: IDAutomationSHcC128M
File: Select IDAutomationSHcC128M.ttf from your saved location.
Click Apply.
Click on Font Mappings. Click “Create Font Mapping Set”.

Mapping Name: IDAutomationSHcC128M
Mapping Code: IDAutomationSHcC128M
Type: FO To Pdf
Click Apply.




Click on Create Font Mapping. Fill the values as below screen shot.
Font Family: IDAutomationSHcC128M
Font Value: IDAutomationSHcC128M
Click Apply.

In the next page enter

Click on Administration Tab


Expand FO Processing and enter Barcodes as Font Mapping Set value.




Wednesday, 20 April 2016

RTF Template BI Publisher XML Tags in oracle

RTF Template XML Tags

Concatenation tag:
<?xdofx:rpad(FIRST_NAME||LAST_NAME),30,'x')?>
<?value-of:concat(Payee/Address/City,’, ‘,Payee/Address/State)?>

formatting tags in rtf template for excel output

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?C_INVOICE_NUM?></fo:b idi-override>

here C_INVOICE_NUM is a xml tag.
If tag:
<?IF:COMM=’’?>SRY<?END IF?><?IF:COMM!=’’?><?COMM?><?END IF?>

Else if:
<?xdofx:if COMM!='' then SAL+COMM else SAL end if?>

DECODE TAG:
<?xdofx:decode(COMM,’’,’sry’,COMM)?>
<?xdofx:decode(COMM,’’,ENAME,COMM||’, ’||ENAME)?>

CHR:
<?xdofx:chr(65)?>
<?xdofx: to_char(sysdate,'dd-mm-yyyy')?>

ROUND:
<?xdofx: round(9.6)?>

SORT:
<?for-each:G_EMPNO?><?sort:ENAME;’ascending’;data-type=’char’?>
<?ENAME?>                                     
<?end for-each?>

SUM:
SUM:<?2+3?>
<?xdoxslt:sum(SAL)+xdoxslt:sum(COMM)?>
<?sum(current-group()//SAL)?>  //when ever we use double group it shows total sal for current group.

PAGE BREAK
<?split-by-page-break:?>
(OR)
<?for-each-group@section:G_EMPNO;DEPTNO?>//AUTOMATICALLY IT SPLIT DEPT WISE

XML tag for row# column to display serial numbers for rtf layout

<xsl:value-of select="position()"/>

PAGE LAYOUT:
IT DISPLAYS INFORMATION LIKE NOTE,TERMS ETC IN THE ODD OR EVEN PAGE. IN HEADERS AND FOOTERS REGION WE CAN DISPLAY GO TO FILE AND SELECT PAGE SETUP AND SELECT LAYOUT TAB THERE CHECK THE OPTIONS.
<?section:force-page-count;'end-on-odd-layout'?>
<?section:force-page-count;'end-on-even-layout'?>

VARIABLE DECLARATION:
<?xdoxslt:set_variable($_XDOCTX,’A’,20)?>        
<?xdoxslt:get_variable($_XDOCTX,’A’)?>
<?xdoxslt:get_variable($_XDOCTX,’A’)+1?>

TO_CHECK_NUMBER:
<?xdofx:to_check_number(12345.67,1)?>
<?xdofx:to_check_number(123,0)?>
<?xdofx:to_check_number(12345.67,’EUR’,’CASE_LOWER’,’DECIMAL_STYLE_WORDS’)?>
<?xdofx:to_check_number(12345.67,’JPY’,’CASE_UPPER’,’DECIMAL_STYLE_WORDS’)?>

ADD & SHOW PAGE TOTAL:
<?add-page-total:t;’SAL’?>
<?show-page-total:t?>

TEMPLATE HEADERS:
<?template:header?>
EMPLOYEE DETAILS
<?end template?>             //WRITE THESE IN HEADER FORM AND CALL IN THE WORKING
<?call-template:header?>  //WRITE THESE WHERE U NEED

CHOOSE
Ex: <?choose:?>
<?when: DEPTNO=1610?>
Abc
<?end when?>
<?when: DEPTNO=210?>
Kas
<?end when?>
<?otherwise:?>
Def
<?end otherwise?>
<?end choose?>

ARRAYS:
<?xdoxslt:set_array($_XDOCTX, ‘RAVI’, 2, ‘KANTH’)?>
<?xdoxslt:set_array($_XDOCTX,’RAVI’,1,’NERELLA’)?>
<?xdoxslt:get_array($_XDOCTX, ‘RAVI’, 1)?>
<?xdoxslt:get_array($_XDOCTX,’RAVI’,2)?>

INSERT IMAGES:
INSERT ANY IMAGE AS U WISH AND RIGHT CLICK ON IT SELECT FORMAT PICTURE IN THAT SELECT WEB TAB AND ENTER



Formatting char/number to US style phone number
using extended XSL functions for use in RTF template

Case:
Let's say we have xml data in below format
1234567890
Now we want to display it as (123)456-7890 (US style Ph #)
Solution:

<?xdofx:’(‘||substr(Phone_No,1,3)||’) ’||substr(Phone_No,4,3)||’-‘||substr(Phone_No,7,4)?>
HYPERLINKS:
Hi: it is hyperlink right click on hi and select hyperlink in that select browed pages enter web site at run time we can open it.

SQL Statement or XSL Expression
Usage
Description
2+3
<?xdofx:2+3?>
Addition
2-3
<?xdofx:2-3?>
Subtraction
2*3
<?xdofx:2*3?>
Multiplication
2/3
<?xdofx:2/3?>
Division
2**3
<?xdofx:2**3?>
Exponential
3||2
<?xdofx:3||2?>
Concatenation
lpad('aaa',10,'.')
<?xdofx:lpad('aaa',10,'.')?>
The lpad function pads the left side of a string with a specific set of characters. The syntax for the lpad function is:
lpad(string1,padded_length,[pad_string])
string1 is the string to pad characters to (the left-hand side).
padded_length is the number of characters to return.
pad_string is the string that will be padded to the left-hand side of string1 .
rpad('aaa',10,'.')
<?xdofx:rpad('aaa',10,'.')?>
The rpad function pads the right side of a string with a specific set of characters.
The syntax for the rpad function is:
rpad(string1,padded_length,[pad_string]).
string1 is the string to pad characters to (the right-hand side).
padded_length is the number of characters to return.
pad_string is the string that will be padded to the right-hand side of string1
decode('xxx','bbb','ccc','xxx','ddd')
<?xdofx:decode('xxx','bbb','ccc','xxx','ddd')?>
The decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is:
decode(expression, search, result [,search, result]...[, default])
expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is returned if no matches are found.
Instr('abcabcabc','a',2)
<?xdofx:Instr('abcabcabc','a',2)?>
The instr function returns the location of a substring in a string. The syntax for the instr function is:
instr(string1,string2,[start_position],[nth_appearance])
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.
nth appearance is the nth appearance of string2.
substr('abcdefg',2,3)
<?xdofx:substr('abcdefg',2,3)?>
The substr function allows you to extract a substring from a string. The syntax for the substr function is:
substr(string, start_position, [length])
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is the number of characters to extract.
replace(name,'John','Jon')
<?xdofx:replace(name,'John','Jon')?>
The replace function replaces a sequence of characters in a string with another set of characters. The syntax for the replace function is:
replace(string1,string_to_replace,[replacement_string])
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1.
to_number('12345')
<?xdofx:to_number('12345')?>
Function to_number converts char, a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.
to_char(12345)
<?xdofx:to_char('12345')?>
Use the TO_CHAR function to translate a value of NUMBER datatype to VARCHAR2 datatype.
to_date
<?xdofx:to_date ( char [, fmt [, 'nlsparam']] )
TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, then charmust be in the default date format. If fmt is 'J', for Julian, then char must be an integer.
sysdate()
<?xdofx:sysdate()?>
SYSDATE returns the current date and time. The datatype of the returned value is DATE. The function requires no arguments.
minimum
<?xdoxslt:minimum(ELEMENT_NAME)?>
Returns the minimum value of the element in the set.
maximum
<?xdoxslt:maximum(ELEMENT_NAME)?>
Returns the maximum value of the element in the set.
chr
<?xdofx:chr(n)?>
CHR returns the character having the binary equivalent to n in either the database character set or the national character set.
ceil
<?xdofx:ceil(n)?>
CEIL returns smallest integer greater than or equal to n.
floor
<?xdofx:floor(n)?>
FLOOR returns largest integer equal to or less than n.
round
<?xdofx:round ( number [, integer ] )?>
ROUND returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integer can be negative to round off digits left of the decimal point. integer must be an integer.
lower
<?xdofx:lower (char)?>
LOWER returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.
upper
<?xdofx:upper(char)?>
UPPER returns char, with all letters uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype aschar.
length
<?xdofx:length(char)?>
The "length" function returns the length of char. LENGTH calculates length using characters as defined by the input character set.
greatest
<?xdofx:greatest ( expr [, expr]... )?>
GREATEST returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.
least
<?xdofx:least ( expr [, expr]... )?>
LEAST returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.
The following table shows supported combination functions:
SQL Statement
Usage
(2+3/4-6*7)/8
<?xdofx:(2+3/4-6*7)/8?>
lpad(substr('1234567890',5,3),10,'^')
<?xdofx:lpad(substr('1234567890',5,3),10,'^')?>
decode('a','b','c','d','e','1')||instr('321',1,1)
<?xdofx:decode('a','b','c','d','e','1')||instr('321',1,1)?>
XSL EQUIVALENTS
The following table lists the BI Publisher simplified syntax with the XSL equivalents.
Supported XSL Elements
Description
BI Publisher Syntax
<xsl:value-of select= "name">
Placeholder syntax
<?name?>
<xsl:apply-templates select="name">
Applies a template rule to the current element's child nodes.
<?apply:name?>
<xsl:copy-of select="name">
Creates a copy of the current node.
<?copy-of:name?>
<xsl:call-template name="name">
Calls a named template to be inserted into/applied to the current template.
<?call:name?>
<xsl:sort select="name">
Sorts a group of data based on an element in the dataset.
<?sort:name?>
<xsl:for-each select="name">
Loops through the rows of data of a group, used to generate tabular output.
<?for-each:name?>
<xsl:choose>
Used in conjunction with when and otherwise to express multiple conditional tests.
<?choose?>
<xsl:when test="exp">
Used in conjunction with choose and otherwise to express multiple conditional tests
<?when:expression?>
<xsl:otherwise>
Used in conjunction with choose and when to express multiple conditional tests
<?otherwise?>
<xsl:if test="exp">
Used for conditional formatting.
<?if:expression?>
<xsl:template name="name">
Template declaration
<?template:name?>
<xsl:variable name="name">
Local or global variable declaration
<?variable:name?>
<xsl:import href="url">
Import the contents of one stylesheet into another
<?import:url?>
<xsl:include href="url">
Include one stylesheet in another
<?include:url?>
<xsl:stylesheet xmlns:x="url">
Define the root element of a stylesheet
<?namespace:x=url?>
USING FO ELEMENTS
You can use most FO elements in an RTF template inside the Microsoft Word form fields. The following FO elements have been extended for use with BI Publisher RTF templates. The BI Publisher syntax can be used with either RTF template method.
The full list of FO elements supported by BI Publisher can be found in the Appendix: Supported XSL-FO Elements.
FO Element
BI Publisher Syntax
<fo:page-number-citation ref-id="id">
<?fo:page-number-citation:id?>
<fo:page-number>
<?fo:page-number?>
<fo:ANY NAME WITHOUT ATTRIBUTE>
<?fo:ANY NAME WITHOUT ATTRIBUTE?>