Thursday, 28 June 2012

How to Register Oracle XML Reports in oracle apps R12

Overview
Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. At runtime, XML Publisher merges the custom templates with the concurrent request data extracts to generate output in PDF, HTML, RTF, EXCEL or even TEXT for use with EFT and EDI transmissions.

                                                                           STEP -1
Navigation: Login into Oracle Applications –> Go to System Administrator  Responsibility –> Concurrent –>   
Executable


FIELDS:

  • Executable: This is User Understandable Name
  • Short Name: This is Unique and for system reference
  • Application: Under which application you want to register this CONA_PO Program
  • Description: Description
  • Execution Method: Based on this field, your file has to be placed in respective directory or database.
  • Execution File Name: This is the actual Report file name.
Action: Save






                                                               STEP -2
 Create a new concurrent program Purchase Order Report that will call the CONA_PO executable declared above. Make sure that output format is placed as XML.


Navigation: Go to Application Developer Responsibility -> Concurrent ->Program


Note: Output Format should by  'XML' for registering the report in XML.

                                                                      STEP -3
Make sure that the report parameter name and the token name are same.





 
                                                                      STEP -4

Add this new concurrent  program to the corresponding responsibility.
Navigation: Go to System Administrator Responsibility ->Security ->Responsibility->Request










                                                                      STEP -5

Next process is to attach the designed rtf file with the XML code. 
In order to attach the rtf file the user should have the responsibility XML Publisher Administrator assigned to him.

First provide the concurrent program short name as Data Definition name in the  template manager and register the template using the data definition created.


Navigation: Go to XML Publisher Administrator->Data Definitions->Create Data definition.





Note: Make sure the code of the data definition must be the same as the short name of the Concurrent Program we registered for the procedure. So that the concurrent manager can retrieve the templates associated with the concurrent program

  

Now create the template with the help of template manager


                                                                      STEP -6
Navigation: Go to XML Publisher Administrator->Templates ->Create Templates.












Friday, 15 June 2012

How to find Company Address in a database function

/***** Company Ship to Location *********/
Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE SHIP_TO_LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

/***** Company Bill to Location *********/

Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;


/***** Find the Multiple Operating Unit *********/
SELECT LOCATION_ID FROM APPS.HR_ORGANIZATION_UNITS
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID FROM APPS.HR_OPERATING_UNITS HOU
        WHERE HOU.NAME = NVL(:P_OPERATING_UNIT_NAME,HOU.NAME))

Tuesday, 5 June 2012

How to Find Application Top by particular application name

  SELECT    B.APPLICATION_ID,
                    B.APPLICATION_SHORT_NAME,
                    B.BASEPATH APPLICATION_TOP,
                    T.APPLICATION_NAME,
                    T.DESCRIPTION,
                     B.PRODUCT_CODE
     FROM   FND_APPLICATION_TL T
                  ,FND_APPLICATION B
    WHERE   B.APPLICATION_ID = T.APPLICATION_ID
     AND    T.APPLICATION_NAME = :KNOW_APPLICATION_NAME

Friday, 1 June 2012

How to increase oracle froms session times

Session Timeout

Log in : System Administrator

Select the Profile option (Profile - > System -> ICX:Session Timeout) in user level and set time.