Call XML/BI Publisher report form OAF Page in Oracle E-Business Suite/ Oracle APPS

 Call XML Publisher report from OAF Page in Oracle E-Business Suite/ Oracle APPS

/*
AM Code
*/
public BlobDomain getData (String strOrg)
{
BlobDomain blobDomain = new BlobDomain();

try
{

/*
XXCOMP = Aplication short name
XX_USER_LIST= Data Template Code
*/

DataTemplate datatemplate = new DataTemplate(((OADBTransactionImpl)getOADBTransaction()).getAppsContext(), "XXCOMP", "XX_USER_LIST");


Hashtable parameters = new Hashtable();
parameters.put("P_ORG",strOrg);
datatemplate.setParameters(parameters);
datatemplate.setOutput(blobDomain.getBinaryOutputStream());
datatemplate.processData();
}

catch(SQLException e)
{
throw new OAException("SQL Error=" + e.getMessage(),OAException.ERROR);
}

catch (OAException e)
{
throw new OAException("OAException" + e.getMessage(),OAException.ERROR);
}

catch(Exception e)
{
throw new OAException("Exception" + e.getMessage(),OAException.ERROR);
}
return blobDomain;
}

//--------------------------

/*
CO Code
*/

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

{
super.processFormRequest(pageContext, webBean);
OAApplicationModule oaAM = pageContext.getApplicationModule(webBean);

if (pageContext.getParameter("Print") != null) /*On Submit Button press*/

{
String str_P_Org = pageContext.getParameter("item1"); /*Text box for input parameters. */

pageContext.writeDiagnostics(this,"P_Org: " + str_P_Org,3);
Serializable[] oaParams = {str_P_Org};

BlobDomain result = (BlobDomain)oaAM.invokeMethod("getData",oaParams);

try

{
DocumentHelper.exportDocument(pageContext,
"XXCOMP", //= Aplication short name
"XX_USER_LIST", //= Data Template Code
"en",
"US",
result.getInputStream(),
"PDF", /*Output file type*/
null
);
}
catch(OAException e)
{
System.out.println("error");
throw new OAException(" Error= " + e.getMessage(),OAException.ERROR);
}
}
}

Oracle Enterprise Repository for Oracle Fusion Applications Link

 https://www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html

Bi Publisher / XML Publisher: RTf Template Unable to Sum with NULL data

Sum results in NaN

RTF Template: If your column contains Null value the XML publishers SUM function results in NaN.

<?sum(current-group()/ELEMENT_NAME[.!=''])?> 

<?xdoxslt:sum(Amount))?>

Get XML Publisher report Output file

Get the Request Output File for a XML/BI Publisher based concurrent report:
The output is $APPLCSF/$APPLOUT/{REPORTNAME}_{REQUEST_ID}_{COUNT}.PDF|RTF|EXCEL|HTML



REPORTNAME is the concurrent program short name,
REQUEST_ID is the concurrent request ID
COUNT is a counter based on the number of times a request has been re-published.
One of PDF, RTF, EXCEL, HTML is the file extension/type dependent on the output formats chosen.

Custom HRMS Menu not displaying data form Session Controlled Tables

Custom HRMS Menu not displaying data form Session Controlled Tables like PER_PEOPLE used in value set or anywhere else.

No Data Found in LOV if accessed from Custom HRMS Responsibility, where custom menu is attached.

Solution:

Add following function to Custom HRMS Menu (do not Add any Prompt).

Navigator: Disable Multiform

Display HRMS View data in Toad

Display data form HRMS Views form Toad.



INSERT INTO FND_SESSIONS VALUES (USERENV ('SESSIONID'), SYSDATE);

Generate DDL Script (A Faster way)


SELECT dbms_metadata.get_ddl(Object_type,object_name,OWNER)
FROM all_objects
WHERE object_name LIKE 'XX%'
AND Object_type IN ( 'TABLE' ,'PACKAGE' , 'PROCEDURE' ,'FUNCTION' ,'SEQUENCE' ,'DIRECTORY' ,'TRIGGER' )
Order by Object_type

Restart Inactive Concurrent Manager (Concurrent Program Phase is "Inactive" and Status is "No Manager")

To re-start concurrent manager if Concurrent Program Phase is "Inactive" and Status is "No Manager"

Set the APPS Environment

Check the Status:

$adcmctl.sh status apps/<apps password>

To Stop:

adcmctl.sh stop apps/<apps password>

To start:

adcmctl.sh start apps/<apps password>

Enable/Disable Concurrent Program Parameter based on the value of another Parameter.

Refer Seeded Concurrent program "Element Register Report" in HRMS

Organization Unit - Inventory Org - Sub Inventory - Locator Query

Organization Unit - Inventory Org - Sub Inventory - Locator Query

SELECT
    hou.name                          operating_unit,
    ood.organization_name             inventory_org_name,
    subinv.secondary_inventory_name   sub_inventory_name,
    subinv.description                sub_inventory_desc,
    loc.description                   locator_name
FROM
    hr_operating_units hou,
    org_organization_definitions ood,
    mtl_secondary_inventories subinv,
    mtl_item_locations loc
WHERE
    ood.organization_id = subinv.organization_id
    AND ood.operating_unit = hou.organization_id
    AND subinv.secondary_inventory_name = loc.subinventory_code
ORDER BY
    hou.name,
    ood.organization_name,
    subinv.description,
    loc.description;

How to get XML Data for AP Payment Instruction

XML Data is available in the DOCUMENT column of iby_trxn_documents table.

SELECT   *
  FROM   iby_trxn_documents
 WHERE   payment_instruction_id = :l_payment_instruction_id;

its a CLOB column. 

Interface blog

PO Interface Blog

Generate DDL Script (A Faster way)

Generate DDL Script (A Faster way)


SELECT dbms_metadata.get_ddl(Object_type,object_name,OWNER)
FROM all_objects
WHERE object_name LIKE 'XX%'
AND Object_type IN ( 'TABLE' ,'PACKAGE' , 'PROCEDURE' ,'FUNCTION' ,'SEQUENCE' ,'DIRECTORY' ,'TRIGGER' )
Order by Object_type

Query to Get All Concurrent Program & Executable Name, Description and More

Concurrent Program & Executable Name, Description


SELECT fcp.user_concurrent_program_name ,
  fcp.description Description,
  fef.executable_name exe_Name,
  fef.description Exe_Description,
  fef.execution_file_name execution_file_name,
  DECODE (fe.execution_method_code , 'A', 'Spawned' , 'B', 'Request Set Stage Function' , 'E', 'Perl Concurrent Program' , 'H', 'Host' , 'I', 'PL/SQL Stored Procedure' , 'J', 'Java Stored Procedure' , 'K', 'Java Concurrent Program' , 'L', 'SQL*Loader' , 'M', 'Multi Language Function' , 'P', 'Oracle Reports' , 'Q', 'SQL*Plus' , 'S', 'Immediate' , 'Other')execution_method
FROM fnd_executables_form_v fef,
  fnd_concurrent_programs_vl fcp ,
  fnd_executables fe
WHERE fcp.APPLICATION_ID=fef.APPLICATION_ID
AND fef.EXECUTABLE_ID   =fcp.EXECUTABLE_ID
AND fef.EXECUTABLE_ID   =fe.EXECUTABLE_ID
--AND fef.executable_name='XX%'---Your Executable Name
--AND fcp.user_concurrent_program_name LIKE 'XX%' -- Your Concurrent Program name

BI/ XML Publisher Excel Template

Oracle Link.
https://docs.oracle.com/cd/E21764_01/bi.1111/e13881/T527073T571887.htm

Print rdf Parameters directly in rtf template

Define User Parameter in rfd report

Open rtf file

Add following Line

<?param@begin:P_PERIOD?>

Period: <?$P_PERIOD?>

Tuning SQL using DBMS_SQLTUNE

DECLARE
  SQL_STRING  VARCHAR2(15000);
  TASK_NUMBER VARCHAR2(200);
BEGIN
  SQL_STRING  := '<<write your sql query>>';
  TASK_NUMBER := dbms_sqltune.create_tuning_task(sql_text => SQL_STRING, task_name=>'TASK_NUMBER1');
END;

EXEC dbms_sqltune.execute_tuning_task('TASK_NUMBER1');

SELECT DBMS_SQLTUNE.report_tuning_task('TASK_NUMBER1') FROM dual; 

... and follow the recommendation

Oracle BI/XML Publisher : format numbers as text so that leading zero don't disappear or excel will not convert large numbers in scientific format

To preserve leading zero in a number string, modify rtf file replace field code with following code.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?NUMBER_FIELD?>
</fo:bidi-override>

Update Supplier Site Information using API

CREATE OR REPLACE PROCEDURE xx_update_supplier_site AS

  lr_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

  p_vendor_site_id   NUMBER;

  x_msg_data         VARCHAR2(1000);

  x_msg_count        NUMBER;

  p_count            NUMBER;

  x_return_status    VARCHAR2(10);

  lc_msg_data        VARCHAR2(4000);

-- Modify cursor as per your requirement to select Vendor and site

  CURSOR lcu_head IS

    SELECT aps.vendor_id,

           apss.vendor_site_id,

           apss.org_id           

      FROM ap_suppliers aps, ap_supplier_sites_all apss

     WHERE 1 = 1      

       AND aps.vendor_id = apss.vendor_id     

       --AND aps.vendor_id = 10284 

       ;

BEGIN

  fnd_global.apps_initialize(1110, 50747, 200);

  mo_global.init('SQLAP');

  fnd_client_info.set_org_context(83);

  FOR i IN lcu_head LOOP

   

      p_vendor_site_id := NULL;

      x_msg_data       := NULL;

      x_msg_count      := NULL;

      p_count          := NULL;

      x_return_status  := NULL;

      lc_msg_data      := NULL;

      -- Assign Vendor Site Details      

      lr_vendor_site_rec.last_update_date := SYSDATE;

      lr_vendor_site_rec.last_updated_by  := 1110;

      lr_vendor_site_rec.vendor_id        := i.vendor_id;

      lr_vendor_site_rec.org_id           := i.org_id;   

      

      --lr_vendor_site_rec.pay_group_lookup_code := 'PAY GROUP';

     

   

      ap_vendor_pub_pkg.update_vendor_site(p_api_version      => 1.0

                                           x_return_status    => x_return_status,

                                           x_msg_count        => x_msg_count,

                                           x_msg_data         => x_msg_data,

                                           p_vendor_site_rec  => lr_vendor_site_rec,

                                           p_vendor_site_id   => i.vendor_site_id

                                           );

   

      Commit;

      IF x_return_status = 'S' THEN

        dbms_output.put_line('Success ' || x_return_status);

      ELSE

        dbms_output.put_line('Failed ' || x_return_status);

        IF (x_msg_count = 1) THEN

          dbms_output.put_line('x_msg_data ' || x_msg_data);

          lc_msg_data := x_msg_data;

        ELSIF (x_msg_count > 1) THEN

          LOOP

            p_count    := p_count + 1;

            x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);

            IF (x_msg_data IS NULL) THEN

              EXIT;

            END IF;

            dbms_output.put_line('Message' || p_count || ' ---' || x_msg_data);

            lc_msg_data := lc_msg_data || ' ' || x_msg_data;

          END LOOP;

        END IF;

     

      END IF;

    

 

  END LOOP;

END;

Register Table and its column

Register Table and its column:

create or replace procedure XXIMR_REGISTER_TABLE(APPL_SHORT_NAME varchar2,
                                                 TABLE_OWNER     VARCHAR2,
                                                 TABLE_NAME      VARCHAR2) is

  cursor tbl_column(p_owner varchar2, p_Table varchar2) is
    select clmn.COLUMN_NAME,
           clmn.DATA_TYPE,
           clmn.DATA_LENGTH,
           clmn.DATA_PRECISION,
           clmn.DATA_SCALE,
           clmn.NULLABLE,
           clmn.COLUMN_ID
      from ALL_TAB_COLUMNS clmn
     where clmn.OWNER = p_owner
       and clmn.TABLE_NAME = p_Table;

begin

  ad_dd.register_table(p_appl_short_name => APPL_SHORT_NAME, p_tab_name => TABLE_NAME, p_tab_type => 'T');

  Commit;
  for i in tbl_column(TABLE_OWNER, TABLE_NAME) loop
    ad_dd.register_column(p_appl_short_name => APPL_SHORT_NAME,
                          p_tab_name        => TABLE_NAME,
                          p_col_name        => i.column_name,
                          p_col_seq         => i.column_id,
                          p_col_type        => i.data_type,
                          p_col_width       => i.data_length,
                          p_nullable        => i.nullable,
                          p_translate       => 'N',
                          p_precision       => i.data_precision,
                          p_scale           => i.data_scale);
  end loop;

  commit;
end;