XML Publisher Report in Oracle Transportation Management (OTM)

Please follow the below steps to create XML Publisher Report in Oracle Transportation Management:

Query Template

Format Template

Report

Adding to SHIPMENT_MANAGER Report Group

Now report appears under Shipment Management

Report Output in PDF

Report Output in Email

TOOL-TIP FOR THE REPORT COLUMN

The tool-tip displaying report column while hovering on the another report column.

STEP 1:

l   ENAME, DEPARTMENT and MANAGER  is the report Column.

l   Make the ENAME column’s display type as “Display As Text”. DEPARTMENT and MANAGER column’s display type as hidden.

l   In Column Formatting, apply this Html code in the HTML Expression

        

        <span data-tooltip="Department : #DEPT# &#xa;Manager : #MGR#">#ENAME#</span>

l   “Department : #DEPT# &#xa;Manager : #MGR#” this will be displayed while hovering on to the employee name and “&#xa” this is used to display text in the new line.

STEP 2:

l    Click on the edit page icon

l    Go to css

l    Apply this code in the inline Region

 /* Add this attribute to the element that needs a tooltip */

[data-tooltip] {

  position: relative;

  display: inline-block;

}

/* Hide the tooltip content by default */

[data-tooltip]:before,

[data-tooltip]:after {

  visibility: hidden;

  opacity: 0;

  pointer-events: none;

}

/* Position tooltip above the element */

[data-tooltip]:before {

  position: absolute;

  padding: 10px;

  width: 160px;

  top:100%;

  left:100%;

  border-radius: 10px;

  background-color: #000;

  color: #fff;

  content: attr(data-tooltip);

  

   

}

/* Show tooltip content on hover */

[data-tooltip]:hover:before,

[data-tooltip]:hover:after {

  visibility: visible;

  z-index: 999;

  white-space:pre-wrap;

  opacity: 1;

}

.t-Body-nav {

      overflow: hidden;

}

By 

Angel M

Performance Using hint

Performance issue.

 The issue was due to joins across remote sites where oracle optimizer might 

 Wrongly choose the driving site. 

 This can be resolved by using driving site hint. 

 It is always good to do all manipulations in one site and fetch the final

 Results to the target site.

 

QUERY EXCEUTED IN DATA BASE 2

Select t1.column1, t2.column2, t2.column3

  From table1 t1, xxsc.table2@db1todb2 t2

 Where t1.column1 = t2.column1

Query which was taking around 47 msecs for execution.

QUERY EXCEUTED IN DATA BASE 2

Select /*+driving_site(t2)*/  t1.column1, t2.column2, t2.column3

  From table1 t1, xxsc.table2@db1todb2 t2

 Where t1.column1 = t2.column1

Query which was taking around 15 msecs for execution.

Query to find open invoices with the Supplier and PO details

SELECT   i.invoice_num "Invoice Number",

         (SELECT MAX (pha.segment1) po_number

            FROM apps.ap_invoices_all aia,

                 apps.ap_invoice_lines_all aila,

                 apps.ap_invoice_distributions_all aida,

                 apps.po_headers_all pha,

                 apps.po_lines_all pla,

                 apps.po_distributions_all pda

           WHERE pha.po_header_id = pla.po_header_id

             AND pla.po_line_id = pda.po_line_id

             AND aida.po_distribution_id = pda.po_distribution_id

             AND aida.invoice_id = aia.invoice_id

             AND aia.invoice_id = aila.invoice_id

             AND aia.invoice_id = i.invoice_id

             AND aila.line_number = ail.line_number

             AND aia.vendor_id = i.vendor_id) "PO Number",

         v.segment1 "Supplier Number", v.vendor_name "Supplier Name",

         vs.vendor_site_code "Supplier Site", i.invoice_date "Invoice Date",

         i.description "Invoice Description",

         ail.description "Invoice Line Description", SUM (ail.amount)

                                                                     "Amount",

         DECODE (i.cancelled_date, NULL, 'NO', 'YES') "Cancel Status"

    FROM po_vendors v,

         po_vendor_sites_all vs,

         ap_invoices_all i,

         apps.ap_invoice_lines_all ail

   WHERE v.vendor_id = vs.vendor_id

     AND i.invoice_id = ail.invoice_id

     AND i.vendor_id = v.vendor_id

     AND i.vendor_site_id = vs.vendor_site_id

--and     i.invoice_num = '10190183'

     AND i.invoice_date BETWEEN '01-JAN-2015' AND '31-DEC-2015'

     AND EXISTS (

               SELECT 1

                 FROM apps.ap_invoice_distributions_all d

                WHERE d.invoice_id = i.invoice_id

                      AND d.match_status_flag = 'A')

GROUP BY v.vendor_name,

         vs.vendor_site_code,

         i.invoice_id,

         i.invoice_num,

         i.invoice_date,

         i.description,

         ail.description,

         ail.line_number,

         i.vendor_id,

         v.segment1,

         i.cancelled_date

ORDER BY v.vendor_name, i.invoice_num;

By

Sivachandaran S