Oracle Analytics Cloud and Server

Products Banner

XML Report RTF Design

Received Response
42
Views
3
Comments

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>

Answers

  • 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

  • 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. 

  • 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