Adding Hyperlink to XML Publisher RTF

Using Word's Hyperlink feature will not work, as it appends the server or local path. Instead use a form field with the URL referenced . Ex:

 
<?variable@incontext:link;TRACKING_URL?><fo:basic-link external-destination="url({$link})"><?WAYBILL?></fo:basic-link>

Shows value of waybill flyover as TRACKING_URL.

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

http://www.adivaconsulting.com/blog-bi-publisher/item/49-using-drill-down-in-bip-report.html

<?param@begin:CURRENT_SERVER_URL?>

<?$CURRENT_SERVER_URL?>

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

=================
Refer: http://winrichman.blogspot.com/2010/03/hyperlink.html
=================

option1:

If the XML data includes an element that contains a hyperlink , then you can use that element to create dynamic hyperlinks at runtime. 
a. insert hyperlink from word menu 
b. In the Type the file or Web page name field of the Insert Hyperlink dialog box, enter the following syntax:
c. {ELEMENT_NAME_WHICH_HAS_URL_LINK}
where ELEMENT_NAME_WHICH_HAS_URL_LINK is the xml data element name

option2:

create a form field, you can add it

<fo:basic-link external-destination="http://www.google..com">
<fo:inline text-decoration="underline">google Link</fo:inline>
</fo:basic-link>

or

<fo:basic-link external-destination="{ELEMENT_NAME}">
<fo:inline text-decoration="underline">google Link</fo:inline>
</fo:basic-link>

or

<fo:basic-link>
<xsl:attribute name="external-destination"><xsl:value-of select="ELEMENT_URL"/>
</xsl:attribute>
<xsl:value-of select="LINK_NAME"/>
</fo:basic-link>

Dynamic Image Display in XML Publisher

OA Media Directory Reference 

(XML Publisher Report Designer's Guide Release 12 Part No. B31410-01 )

Note: This method only applies to Oracle E-Business Suite installations.

1. Insert a dummy image in your template.

2. In Microsoft Word's Format Picture dialog box select the Web tab. Enter the following syntax in the Alternative text region to reference the OA_MEDIA directory:

url:{'${OA_MEDIA}/image name'}

For example, enter:

url:{'${OA_MEDIA}/ORACLE_LOGO.gif'}

Element Reference from XML File

1. Insert a dummy image in your template.

2. In Microsoft Word's Format Picture dialog box select the Web tab. Enter the following syntax in the Alternative text region to reference the image URL:

url:{IMAGE_LOCATION}

where IMAGE_LOCATION is an element from your XML file that holds the full

URL to the image.

You can also build a URL based on multiple elements at runtime. Just use the concat function to build the URL string. For example:

url:{concat(SERVER,'/',IMAGE_DIR,'/',IMAGE_FILE)}

where SERVER, IMAGE_DIR, and IMAGE_FILE are element names from your XML

file that hold the values to construct the URL.

This method can also be used with the OA_MEDIA reference as follows:

url:{concat('${OA_MEDIA}','/',IMAGE_FILE)}

Report Triggers

Sample Before Report Trigger

function BeforeReport return boolean is

begin

DECLARE

BEGIN 

  BEGIN 

  SRW.USER_EXIT('FND SRWINIT');

  EXCEPTION

     WHEN SRW.USER_EXIT_FAILURE THEN

SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger - SRWINIT USER EXIT');

     return (FALSE);

  END;

BEGIN  /*MOAC*/

:P_ORG_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();

END;

Sample After Report Trigger Calls Bursting

function AfterReport return boolean is

 nReturnRequestID NUMBER;

begin

srw.message(1,'Start of after report trigger: ');

BEGIN

  IF :CF_REQ_BY_EMAIL IS NOT NULL THEN

    nReturnRequestID       := FND_REQUEST.SUBMIT_REQUEST ( 'XDO' -- Application Short Name

    , 'XDOBURSTREP'                                              -- Program Short Name

    , 'XML Publisher Report Bursting Program'                    -- Program Name

    , NULL                                                       -- Start Date

    , FALSE                                                      -- Sub-request

    /* Program Parameters START */

    , 'Y'           -- Hidden Param

    , :P_CONC_REQUEST_ID -- Parameter 1: Data extract Request ID

    , 'N'           -- Debug Flag, defaulted to 'N'

    /* Program Parameters END */

    );

    srw.message(1,'REQUEST ID of the BURSTING PROGRAM is: ' || nReturnRequestID);

  END IF;

EXCEPTION

WHEN OTHERS THEN

    srw.message(1,'Unhandled error in after report trigger: ' || SQLERRM);

end;

BEGIN 

SRW.USER_EXIT('FND SRWEXIT');

EXCEPTION

   when srw.user_exit_failure then

      srw.message(1,'Failed in AFTER REPORT TRIGGER');

    return (FALSE);

END;  

return (TRUE);

End;

Sample After Parameter Form Trigger Using Laxical Parameters

function AfterPForm return boolean is

begin

srw.message(99999,'$Header: OEXOEACK.rdf 120.8.12000000.4 2008/07/28 04:03  zsingh noship                        $');

/*----------- Disable Trace Bug 3304392 ----------------*/

/*

declare

x varchar2(100);

begin

  if (:p_enable_trace = 'Y') Then

     srw.do_sql('ALTER SESSION SET SQL_TRACE=TRUE');

     select 'OEXOECK TRACE' into x from dual;

  end if;

end;

*/

/*----------- Disable Trace Bug 3304392 Ends----------------*/

BEGIN

  

  if :p_booked_status is NOT NULL then

     if :p_booked_status = 'Y' then

    :lp_booked_status := ' and h.booked_flag = ''Y'' ' ;

     else

:lp_booked_status := ' and h.booked_flag = ''N'' ' ; 

     end if;

  else

     :lp_booked_status := '';

  end if ;

/*-----------   Lexical parameter for the Order Date range    --------------------*/

    if (:p_order_date_low is NOT NULL ) AND (:p_order_date_high is NOT NULL )then

      :lp_order_date := ' and h.ordered_date between :p_order_date_low and (:p_order_date_high+1) ' ;

    elsif  (:p_order_date_low is NOT NULL ) then

      :lp_order_date := ' and h.ordered_date  >= :p_order_date_low ' ;

    elsif  (:p_order_date_high is NOT NULL ) then

      :lp_order_date := ' and h.ordered_date <= (:p_order_date_high+1) ' ;

    end if;

  /*-----------   Lexical parameter for the Schedule Date range    --------------------*/

    if (:p_schedule_date_low is NOT NULL ) AND (:p_schedule_date_high is NOT NULL )then

      :lp_schedule_date := ' and (l.schedule_ship_date between :p_schedule_date_low and (:p_schedule_date_high+1)) ' ;

    elsif  (:p_schedule_date_low is NOT NULL ) then

      :lp_schedule_date := ' and l.schedule_ship_date  >= :p_schedule_date_low ' ;

    elsif  (:p_schedule_date_high is NOT NULL ) then

      :lp_schedule_date := ' and l.schedule_ship_date <= (:p_schedule_date_high+1) ' ;

    end if;

  /*-----------   Lexical parameter for the Request Date range    --------------------*/

    if (:p_request_date_low is NOT NULL ) AND (:p_request_date_high is NOT NULL )then

      :lp_request_date := ' and (l.request_date between :p_request_date_low and (:p_request_date_high+1)) ' ;

    elsif  (:p_request_date_low is NOT NULL ) then

      :lp_request_date := ' and l.request_date  >= :p_request_date_low ' ;

    elsif  (:p_request_date_high is NOT NULL ) then

      :lp_request_date := ' and l.request_date <= (:p_request_date_high+1) ' ;

    end if;

  /*-----------   Lexical parameter for the Promise Date range    --------------------*/

    if (:p_promise_date_low is NOT NULL ) AND (:p_promise_date_high is NOT NULL )then

      :lp_promise_date := ' and (l.promise_date between :p_promise_date_low and (:p_promise_date_high+1)) ' ;

    elsif  (:p_promise_date_low is NOT NULL ) then

      :lp_promise_date := ' and l.promise_date  >= :p_promise_date_low ' ;

    elsif  (:p_promise_date_high is NOT NULL ) then

      :lp_promise_date := ' and l.promise_date  <= (:p_promise_date_high+1) ' ;

    end if;

/* Changed the ship_cust, bill_cust, del_cust to ship_party, bill_party, del_party for the bug# 2347438 */

/* Modified ship_party.customer_name to ship_party.party_name, this is done for all bill/del as well --bug 2425515 */

  /*-----------   Lexical parameter for the Customer Name  (Ship To) range    ---------*/

    

    if (:p_ship_to_customer_name_lo is NOT NULL ) AND (:p_ship_to_customer_name_hi is NOT NULL ) then

      :lp_ship_to_customer_name := ' and (ship_party.party_name between :p_ship_to_customer_name_lo and 

:p_ship_to_customer_name_hi) ';

    elsif (:p_ship_to_customer_name_lo is NOT NULL ) then

      :lp_ship_to_customer_name := ' and ship_party.party_name >= :p_ship_to_customer_name_lo ';

    elsif (:p_ship_to_customer_name_hi is NOT NULL ) then

      :lp_ship_to_customer_name := ' and ship_party.party_name <= :p_ship_to_customer_name_hi ';

    end if;

  /*-----------   Lexical parameter for the Customer Name  ( Bill To)range    ------------*/

    

    if (:p_bill_to_customer_name_lo is NOT NULL ) AND (:p_bill_to_customer_name_hi is NOT NULL ) then

      :lp_bill_to_customer_name := ' and (bill_party.party_name between :p_bill_to_customer_name_lo and 

:p_bill_to_customer_name_hi) ';

    elsif (:p_bill_to_customer_name_lo is NOT NULL ) then

      :lp_bill_to_customer_name := ' and bill_party.party_name >= :p_bill_to_customer_name_lo ';

    elsif (:p_bill_to_customer_name_hi is NOT NULL ) then 

      :lp_bill_to_customer_name := ' and bill_party.party_name <= :p_bill_to_customer_name_hi ';

    end if;

  /*---------   Lexical parameter for the Customer Name  ( Deliver To)range    ------------*/

    

    if (:p_del_to_customer_name_lo is NOT NULL ) AND (:p_del_to_customer_name_hi is NOT NULL ) then

      :lp_del_to_customer_name := ' and (del_party.party_name between :p_del_to_customer_name_lo and 

:p_del_to_customer_name_hi) ';

    elsif (:p_del_to_customer_name_lo is NOT NULL ) then

      :lp_del_to_customer_name := ' and del_party.party_name >= :p_del_to_customer_name_lo ';

    elsif (:p_del_to_customer_name_hi is NOT NULL ) then 

      :lp_del_to_customer_name := ' and del_party.party_name <= :p_del_to_customer_name_hi ';

    end if;

  /*-----------   Lexical parameter for the Order Number range    ------------------*/

    

    if (:p_order_num_low is NOT NULL ) AND (:p_order_num_high is NOT NULL ) then

      if ( :p_order_num_low = :p_order_num_high) then

          :lp_order_num := ' and h.order_number = :p_order_num_low ';

      else

          :lp_order_num := ' and (h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high)) ';

      end if;

    elsif (:p_order_num_low is NOT NULL ) then

      :lp_order_num := ' and h.order_number >= to_number(:p_order_num_low) ';

    elsif (:p_order_num_high is NOT NULL ) then

      :lp_order_num := ' and h.order_number <= to_number(:p_order_num_high) ';

    end if;

  /*-----------   Lexical parameter for Salesrep    --------------------*/

  

  if :p_salesrep is  NOT NULL then

    :lp_salesrep := ' and sr.name = :p_salesrep ' ;

  else 

    :lp_salesrep := null;

  end if ;

  if :p_created_by is NOT NULL then

    :lp_created_by := ' and u.user_name = :p_created_by ' ;

  else

    :lp_created_by := null;

  end if ;

/*------------- Lexical Parameter for the Open Orders Only

----------------------- */

  

    if :p_open_orders = 'Y' then

       :lp_open_orders := 

                    'and h.open_flag = ''Y''';

    else

    :lp_open_orders := null;

    end if;

/*_______________Lexical Parameter for Order Type ___________________________*/

   if :p_order_type is not null then

      :lp_order_type := ' and h.order_type_id = :p_order_type ';

   else

      :lp_order_type := null;

   end if;

/* --------------Lexical Parameter for order Category----------------------*/

if :p_order_num_low=:p_order_num_high then null; 

else

 if :p_order_category is not null then

   if :p_order_category = 'SALES' then

   :lp_order_category := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';

   elsif :p_order_category = 'CREDIT' then

   :lp_order_category := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';

   elsif :p_order_category = 'ALL' then

   :lp_order_category := null;

   end if;

 else

  :lp_order_category := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';

 end if;

end if;

/*----------------Lexical Parameter for Line Category ---------------------*/

if :p_line_category is not null then

   if :p_line_category = 'SALES' then

    :lp_line_category := 'and l.line_category_code = ''ORDER'' ';

   elsif :p_line_category = 'CREDIT' then

    :lp_line_category := 'and l.line_category_code = ''RETURN'' ';

   elsif :p_line_category = 'ALL' then

    :lp_line_category := null;

   end if;

 else

  :lp_line_category := 'and l.line_category_code = ''ORDER'' ';

 end if;

/*---------------Set User Language -------------------*/

select userenv('LANG')

into :p_user_lang

from dual;

END ;

  return (TRUE);

end;

sample formula column code

Code Snippets for Formula Column in Reports

FUNCTION CF_Display_Bundled_CompFormula

   RETURN CHAR

IS

   l_found   VARCHAR (10);

BEGIN

   srw.MESSAGE (100, 'XX: ENTER CF_Display_Bundled_CompFormula');

   SELECT DISTINCT 'Exists'

     INTO l_found

     FROM apps.oe_order_headers_all h,

          apps.oe_order_lines_all l,

          apps.mtl_system_items msi,

          apps.mtl_item_categories sp,

          apps.mtl_category_sets cs,

          apps.mtl_categories cat

    WHERE     h.order_number = :ORDER_NUMBER

          AND h.header_id = l.header_id

          AND cat.segment1 IN ('PACK', 'BUNDLE')

          AND l.inventory_item_id = msi.inventory_item_id

          AND msi.organization_id = 124 --:c_master_org1

          AND msi.Inventory_item_id = sp.Inventory_Item_ID

          --AND msi.Inventory_item_id =  :INVENTORY_ITEM_ID

          AND msi.Organization_id = sp.Organization_id

          AND SP.category_set_id = cs.category_set_id

          AND cs.category_set_name = 'XX PROCESSING'

          AND sp.category_id = cat.category_id

          AND l.line_number = :l_number;

   --

   IF l_found = 'Exists'

   THEN

      IF    :CF_ITEM_CATEGORY NOT IN ('PACK', 'BUNDLE')

         OR :CF_ITEM_CATEGORY IS NULL

      THEN

         RETURN ('NO');

      ELSE

         IF :CF_BUNDLE_TOP_LEVEL = 'YES'

         THEN

            RETURN ('YES');

         ELSE

            RETURN ('NO');

         END IF;

      END IF;

   ELSE

      RETURN ('YES');

   END IF;

--

EXCEPTION

   WHEN OTHERS

   THEN

      srw.MESSAGE (

         5000,

         'DEBUG:  exception CF_Display_Bundled_CompFormula.' || SQLERRM);

      RETURN ('YES');

END;

Oracle / PLSQL: TO_CHAR Function Number Formatting

Number Formatting

      Character       Example       Explanation            9               9999          Each character represents a place holder for digits.      0               999.00        Used for leading or trailing zeros.           $               $9999         prefixes the number with a dollar sign.           S               S9999         Useful for positive and negative numbers in query results.           PR              9999PR        Displays negative numbers in angle brackets.           D or .          99D99, 99.99  Places a decimal point in the place where the D or. is.           ,               9,999         Places a comma in the place where the , is.           RN or rn        RN            Displays roman numerals, in upper-or lowercase depending on the case of the format mask.           DATE            date          Assumes the number in a Julian date, and displays the resulting value in MM/DD/YY format.      

TO_CHAR(-12345.67, '99,999.99PR'): negative value in angle brackets

SQL>  SQL>  SQL>  SQL> SELECT TO_CHAR(-12345.67, '99,999.99PR') FROM dual;    TO_CHAR(-12  -----------  <12,345.67>    SQL>  

TO_CHAR(12345.67, '99999V99'): shift specified number of digits

SQL>  SQL>  SQL> SELECT TO_CHAR(12345.67, '99999V99') FROM dual;    TO_CHAR(  --------   1234567    SQL>  SQL>

TO_CHAR(12345.67, 'U99,999.99')

SQL>  SQL>  SQL> SELECT TO_CHAR(12345.67, 'U99,999.99') FROM dual;    TO_CHAR(12345.67,'U9  --------------------            $12,345.67    SQL>

Refer: http://www.java2s.com/Code/Oracle/Data-Type/TOCHAR1234567U9999999.htm

Oracle Pricing: Concept of Price List

concept of pricing

---------------

1. Items/ item categories/ services

2. Qualifiers

 a. determine eligibility rules

 b. determine who receives the price benefit

3. Modifiers

 a. Pricing actions that can be used to adjust the list price

The pricing engine determines the selling price for products and services. 

It provides the final selling price after retrieving the initial price from the price list or customer agreement. 

If this order qualifies for any increases or decreases from the initial price, 

adjustments called modifiers are applied to the price.

-------------------------------------------When the sales order is entered, the pricing engine is called and reads the relevant price list or pricing agreement. 

The appropriate modifiers are then applied to the base price and the final selling price is sent to the sales order line or the calling application. 

The Adjustments window displays the original price, applied modifiers, and the final price.

---------------------------------------------

Overview of the Pricing Engine

The pricing engine, which consists of a search engine and a calculation engine, works through open APIs to provide the pricing results to the calling application. 

From the pricing request, the pricing engine evaluates the appropriate modifiers and price lists, resolves incompatibility issues, retrieves the list price, and calculates the unit selling 

price and adjustments. 

The search engine receives pricing information from entities like price lists, modifiers, qualifiers, formulas, products, and pricing attributes.

--------------------------------------------------------

Overview of Pricing Security

In Oracle Applications, the basic level of security called functional security is implemented to control users' access to different windows, controls, and reports in the application. 

Additionally, Basic Pricing has its own security setup called pricing security that works along with functional security to provide a secure environment where the user can view, update, or 

add pricing entities within a framework of authorizations and permissions. Pricing entities are price lists, modifiers, and agreements. You can set up pricing security if you have the 

Oracle Pricing Administrator responsibility. 

Using pricing security, you can assign pricing entities to operating units, assign privileges, and set security rules for pricing entities.

----------------------------------------------------

Overview of Price Lists

A price list is useful for maintaining the prices and other pricing details of products and services. 

-> It serves as a repository of items with their related pricing details.

-> You can retrieve a price list and add/edit/delete related items and item categories. 

-> You can also use the price list to define attributes for the products that determine the pricing action.

-> An item should have a price when it is entered on a sales order line. 

-> Once the pricing engine populates the unit Price field by deriving the price from the appropriate price list, the order can be booked.

-> The header region of the price list consists of fields to store the price list 

name, 

the effective dates of the price list, 

currency, 

multi-currency conversion, 

pricing controls, 

rounding factor, 

and shipping defaults like freight terms and freight carriers.

-> You can enter one or more pricing lines to the price list. 

-> However you may add only one secondary price list. 

-> Pricing attributes can also be used to determine the price in the price list.

-> You can create as many price lists as needed by entering the information or copying existing price lists. 

-> The price lists can be modified to include new items or groups of items. 

-> The list price of the price list lines can be increased or decreased by either an amount or a percentage.

----------------------------------------------------

Overview of Formulas

-> You can define prices as constant values or create formulas to provide pricing flexibility and the ability to carry out complex computations. 

-> Some of the formulas that are created are based on the following formula component types:

Factor List: You can apply different factor values for varying item attributes

Numeric Constant: Numeric value

Pricing Context: Grouping of pricing attributes, of which a maximum of 100 can be incorporated in the formula

Product Context: Which contains only one attribute, item

-> You can create formulas in the Pricing Formulas window. 

-> Basic Pricing provides seeded formulas, such as the Freight and Special Charges Formulas, which includes the Cost to Charge Conversion formula and Cost to Charge Markup formula. 

-> These formulas convert freight costs to freight charges.

------------------------------------------------------

Overview of Qualifiers

-> A qualifier defines eligibility rules for modifiers and price lists. 

-> A qualifier can be a customer name, a customer class, an order type, or an order amount that can span orders. 

-> Though a qualifier may be set up independently, it comes into effect only when linked to a modifier or a price list. 

-> Thus, the modifier that is set up and linked to a qualifier determines who the price adjustment will affect as well as the amount that will change. 

-> You can create qualifier groups to apply individual qualifiers as a set. When you group qualifiers, they become a set, which allows for faster searching and setup.

------------------------------------------------------

Concurrent Program output through email

Oracle Applications : Concurrent Program output through email

11/21/2016 10:44:00 PM

Sekhar Byna

Refer: http://ebiztechnics.blogspot.in/2016/11/oracle-applications-concurrent-program-output-trhough-email.html

How to send output of concurrent program to email?

 Set the profile options "FND:SMTP Host" and "FND:SMTP Port" by providing the right values 
---------------------------------------------------------------
a. Log into System Administrator responsibility.
b. Navigate to Profile - System.
c. Query up the %SMTP% profiles.
d. Set the following profile values to the defined host and port:

FND:SMTP Host
FND:SMTP Port

Example
FND:SMTP Host = mail.ebiztechnics.com
FND:SMTP Port = 25

e. Resubmit request and monitor email delivery.