making standard or custom webadi integrator updatable

Run below script:

update bne_integrators_b

set source='C'

where integrator_code = <>

Dynamic Date in Footer in XML Publisher using XDO Function

Use below code to achieve it.

©<?xdoxslt:sysdate('yyyy')?> Company, Inc. All rights reserved.

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