XML Report RTF Design

Karthik7

Summary

To display AP Invoice and PO details with distinct values

Content

Hi Dear all,

I am working on a XML Publisher report which displays VENDOR NAME, INV_BATCH, INVOICE_NUM, PO_NUM, PO_LINE, PO_LINE_AMOUNT

I am using the below xml sample data:

======================================================

<?xml version="1.0" encoding="UTF-8"?>
<XXAPINVPODETAILS>
<LIST_G_SUM>
<G_SUM>
<INV_VEN>ABC VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH</BATCH_NAME>
<INV_NUM>   TEST_INV_001</INV_NUM>
<PO_NUM>          1000</PO_NUM>
<PO_LNUM>2</PO_LNUM>
<PO_LAMT>50</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>ABC VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH</BATCH_NAME>
<INV_NUM>   TEST_INV_001</INV_NUM>
<PO_NUM>          1000</PO_NUM>
<PO_LNUM>3</PO_LNUM>
<PO_LAMT>100</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>XYZ VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH2</BATCH_NAME>
<INV_NUM>   TEST_INV_002</INV_NUM>
<PO_NUM>          1001</PO_NUM>
<PO_LNUM>1</PO_LNUM>
<PO_LAMT>200</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>XYZ VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH2</BATCH_NAME>
<INV_NUM>   TEST_INV_002</INV_NUM>
<PO_NUM>          1001</PO_NUM>
<PO_LNUM>2</PO_LNUM>
<PO_LAMT>50</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>XYZ VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH2</BATCH_NAME>
<INV_NUM>   TEST_INV_002</INV_NUM>
<PO_NUM>          1001</PO_NUM>
<PO_LNUM>3</PO_LNUM>
<PO_LAMT>50</PO_LAMT>
</G_SUM>
</LIST_G_SUM>
</XXAPINVPODETAILS>

======================================================

Using the above xml data, The report output displays as follows, 

 

VENDOR NAME	BATCH NAME	INVOICE NUM	PO NUM	PO LINE	PO LINE AMT
ABC VENDOR NAME	TEST_BATCH	TEST_INV_001	1000	2	50
ABC VENDOR NAME	TEST_BATCH	TEST_INV_001	1000	3	100
XYZ VENDOR NAME	TEST_BATCH2	TEST_INV_002	1001	1	200
XYZ VENDOR NAME	TEST_BATCH2	TEST_INV_002	1001	2	50
XYZ VENDOR NAME	TEST_BATCH2	TEST_INV_002	1001	3	150

 

But I Would like to get the output design as follows,

VENDOR NAME	BATCH NAME	INVOICE NUM	PO NUM	PO LINE	PO LINE AMT
ABC VENDOR NAME	TEST_BATCH	TEST_INV_001	1000	2	50
				3	100
XYZ VENDOR NAME	TEST_BATCH2	TEST_INV_002	1001	1	200
				2	50
				3	50

 

Could you please help me on this to get required RTF format. 

 

Regards,

Karthik

 

Version

R12.2

Code Snippet

<?xml version="1.0" encoding="UTF-8"?>
<XXAPINVPODETAILS>
<LIST_G_SUM>
<G_SUM>
<INV_VEN>ABC VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH</BATCH_NAME>
<INV_NUM>   TEST_INV_001</INV_NUM>
<PO_NUM>          1000</PO_NUM>
<PO_LNUM>2</PO_LNUM>
<PO_LAMT>50</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>ABC VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH</BATCH_NAME>
<INV_NUM>   TEST_INV_001</INV_NUM>
<PO_NUM>          1000</PO_NUM>
<PO_LNUM>3</PO_LNUM>
<PO_LAMT>100</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>XYZ VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH2</BATCH_NAME>
<INV_NUM>   TEST_INV_002</INV_NUM>
<PO_NUM>          1001</PO_NUM>
<PO_LNUM>1</PO_LNUM>
<PO_LAMT>200</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>XYZ VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH2</BATCH_NAME>
<INV_NUM>   TEST_INV_002</INV_NUM>
<PO_NUM>          1001</PO_NUM>
<PO_LNUM>2</PO_LNUM>
<PO_LAMT>50</PO_LAMT>
</G_SUM>
<G_SUM>
<INV_VEN>XYZ VENDOR NAME</INV_VEN>
<BATCH_NAME>TEST_BATCH2</BATCH_NAME>
<INV_NUM>   TEST_INV_002</INV_NUM>
<PO_NUM>          1001</PO_NUM>
<PO_LNUM>3</PO_LNUM>
<PO_LAMT>50</PO_LAMT>
</G_SUM>
</LIST_G_SUM>
</XXAPINVPODETAILS>

Aaron Leggett

Hi Karthik,

We have been doing something similar and we solved this problem by using a partitioning statement on the PO number within the SQL, have you tried that yet? The other option is to group left within the RTF template.

Aaron

Aaron Leggett

Attached a very basic example of Group Left, please use your XML to run it. Obviously, this merges cells, thus can be harder to filter/unpleasing to the eye. Partioning would offer the results you are after in the output design. 

Karthik7

Customization of Oracle Standard Check Printing: Issue with printing check info at fixed position: 

Dear Experts, 

I am working on new check printing format ( Used the oracle standard check template and customizing it) which is to print invoice data on first half of the page and the second half is to print check information(assuming a page a table with single column and 2 rows).

Inside the first row, I created another table for displaying invoice data like invoice number, invoice amount, amount paid

Inside the second row, I created another table for displaying check info like check number, amount, bank info and payee name and address.

Facing an issue that unable to fix the table size which results to moving check info to next page when there are more invoices. 

 

I want to fix the check data at the bottom of the page as always and it should not above or below the page due to less or more invoices case. 

 

Could you please help me to resolve the issue. 

 

Thanks,

Karthik