Saturday, 14 August 2021

XML reports with Oracle Bursting

XML Bursting in Oracle Apps

XML Bursting

XML Bursting can be used to split one XML File into multiple XML Blocks. These individual xml blocks can then be used to generate reports and even use different layouts. You can also deliver the reports to multiple destinations based on a XML Element. The steps involved are listed below for your understanding:

Generate the XML File
Split the XML into multiple XML Blocks
Generate Report based on the individual XML Blocks
Deliver the report

Take a look at the below image and the process will be much more clearer:

XML Bursting Process

Bursting Control File

Bursting control is used to identify:

How to split the XML file / data
How to deliver the Report
Delivery destination details

After defining the XML Bursting Control File, we will upload the same to the Data Definition. But before that, lets take a look at the XML file that we have.

<?xml version="1.0" encoding="UTF-8"?>

<INVOICEDATA>

  <LIST_G_SUPPLIER>

    <G_SUPPLIER>

      <SUPPLIER_NUMBER>1001</SUPPLIER_NUMBER>

      <SUPPLIER_NAME>VENDOR001</SUPPLIER_NAME>

      <SUPPLIER_EMAIL>contact@vendor1.com </SUPPLIER_EMAIL>

      <LIST_G_INV>

        <G_INV>

         <INV_NUMBER>INVOICE_01_001</INV_NUMBER>

         <CURRENCY_CODE>USD</CURRENCY_CODE>

         <AMOUNT>100.27</AMOUNT>

        <G/G_INV>

      </LIST_G_INV>

    </G_SUPPLIER>

    <G_SUPPLIER>

      <SUPPLIER_NUMBER>1002</SUPPLIER_NUMBER>

      <SUPPLIER_NAME>VENDOR002</SUPPLIER_NAME>

      <SUPPLIER_EMAIL>contact@vendor2.com</SUPPLIER_EMAIL>

      <LIST_G_INV>

        <G_INV>

          <INV_NUMBER>INVOICE_02_001</INV_NUMBER>

          <CURRENCY_CODE>USD</CURRENCY_CODE>

          <AMOUNT>612.99</AMOUNT>

        </G_INV>

      </LIST_G_INV>

    </G_SUPPLIER>

  </LIST_G_SUPPLIER>

</INVOICEDATA>

As you can see the above XML file has details of two Suppliers (VENDOR001 and VENDOR002). I want the invoice details of VENDOR001 to be emailed to contact@vendor1.com and invoice details of VENDOR002 to be emailed to contact@vendor2.com. In this case, I know that I have to split the XML File into two.

The first thing that we need to do is to identify the Level at which the file has to be split, so that we get two different XML files. If you take a closer look at the XML file, you will see that the <G_SUPPLIER> … </G_SUPPLIER> group is repeated twice. So to get two different XML Files, we will split the actual file at /INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIER Level. The result is the below XML Files.

XML Files After Splitting

We also know that we need to deliver these reports by email. We will use the G_SUPPLIER/SUPPLIER_EMAIL element to get the email id from the XML file. Now let us take a look at the Bursting Control File.

<?xml version="1.0" encoding="utf-8"?>

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">

  <xapi:request select="/INVOICEDATA/LIST_G_SUPPLIER/G_SUPPLIER">

     <xapi:delivery>

       <xapi:email id="${SUPPLIER_NUMBER}" reply-to="ap@quest4apps.com" from="ap@quest4apps.com" port="25" server="ora.q4apps.us">

         <xapi:message subject="Invoice Details" attachment="true" to="${SUPPLIER_EMAIL}" id="${SUPPLIER_NUMBER}">

          Please find your electronically formatted Invoice Details.

         </xapi:message>

       </xapi:email>

     </xapi:delivery>

     <xapi:document delivery="${SUPPLIER_NUMBER}" output-type="pdf" output="/data/test1/interfaces/out/${SUPPLIER_NUMBER}.pdf">

       <xapi:template type="rtf" location="xdo://Q4A.XXQ4ASUPINV.en.00/?getSource=true" />

     </xapi:document>

  </xapi:request>

</xapi:requestset>

Please find below details of the XML tags.

xapi:request: Give details of how you want to split the XML File

xapi:email: Give details of the Email

id: This is a unique value that identifies each group. In our case it is the Supplier Number.
reply-to: This is the reply to email address
from: This is the from email address that will be used while sending the email
port: The port number of the email server. Check with your DBA / System Administrator for this value.
server: The details of the email server

xapi:message: Give details of the Email Message

subject: Subject of the Email
attachment: We are attaching the report output with the email. So the value should be “true”.
to: The email id of the Supplier, which is the recipient’s email address.
id: This is a unique value that identifies each group. In our case it is the Supplier Number.

xapi:document: Here we define the output document details

output-type: The output type of the report output.
output: The folder in which the output file will be saved.

xapi:template: Give details of the RTF Template

type: Give the details of the Template Type. In our case it is RTF
location: Location of the RTF Template.

Lets take a closer look at the location:

location="xdo://Q4A.XXQ4ASUPINV.en.00/?getSource=true"

For simplicity the above statement can be broken down as

location="xdo://Application Short Name.Template Code.Default Language.Default Territory/?getSource=true"

And you can use the below SQL Statement to get the details of the Template.

select   xtb.application_short_name||'.'||

         xtb.template_code ||'.'||

         xtb.default_language ||'.'||

         xtb.default_territory

  from   apps.xdo_templates_b xtb

 where   xtb.template_code ='<Template Code>';

Uploading the Bursting Control File

Once the bursting control file has been defined, you need to attach the same to the Data Definition. Query for the Data Definition and open the defintion. And then click on “Add File” button and upload the XML file that we have defined. Save the changes and you are all set to test the process.

Check the below image for more details:

Submitting the Bursting Process

Attaching the XML Bursting Control file will not burst the report just yet. You need to submit the “XML Publisher Report Bursting Program” Concurrent program after the XML Publisher Report is completed. You can either manually Submit the program or use the below SQL Code Snippet in the After Report Trigger to submit the bursting program once the XML Publisher Report is completed.

DECLARE

   l_conc_id     NUMBER;

   g_request_id  NUMBER;

BEGIN

   l_conc_id :=

   fnd_request.submit_request

           (

             application   => 'XDO'

            ,program       => 'XDOBURSTREP'

            ,description   => NULL

            ,start_time    => SYSDATE

            ,sub_request   => FALSE

            ,argument1     => NULL         

            ,argument2     => g_request_id  -– Request ID of XML Publisher Report

            ,argument3     => 'Y'           -– debug Flag

           );

   COMMIT;

END;

Thursday, 4 February 2021

Common Exceptions in PLSQL with sample example

CREATE OR REPLACE PROCEDURE APPS.XXLEARN_EXCEPTIONS_HANDLING(P_ID NUMBER)

l_id number;

l_name varchar2(100);

l_fun number;

cursor c1

select * from XXLEARN_STAGING

where id=90;

BEGIN

select id,name into l_id,l_name
from XXLEARN_STAGING
where id=p_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('When NO_DATA_FOUND Exceptions');

when TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('When TOO_MANY_ROWS Exceptions');

WHEN INVALID_NUMBER THEN
--this occuers when we pass wrong value in where condition if column dataype is number and if we pass char/string or vice versa then this eeror wil occer

DBMS_OUTPUT.PUT_LINE('When INVALID_NUMBER Exceptions');

WHEN VALUE_ERROR THEN

-- For example, when your program selects a column value into a character variable,

--if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR.

--In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)

DBMS_OUTPUT.PUT_LINE('When VALUE_ERROR Exceptions');

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('When ZERO_DIVIDE Exceptions');

WHEN TIMEOUT_ON_RESOURCE THEN

--A time-out occurs while Oracle is waiting for a resource.

DBMS_OUTPUT.PUT_LINE('When TIMEOUT_ON_RESOURCE Exceptions');

WHEN ACCESS_INTO_NULL THEN

DBMS_OUTPUT.PUT_LINE('When ACCESS_INTO_NULL Exceptions');

WHEN PROGRAM_ERROR THEN

--PL/SQL has an internal problem.

DBMS_OUTPUT.PUT_LINE('When PROGRAM_ERROR Exceptions');

WHEN STORAGE_ERROR THEN

--PL/SQL runs out of memory or memory has been corrupted.

DBMS_OUTPUT.PUT_LINE('When STORAGE_ERROR Exceptions');
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('When Others Exceptions');
END;

BEGIN

dbms_output.put_line('cursor total count='||c1%ROWCOUNT);

for i in c1 loop
dbms_output.put_line('Inside cursor');
if c1%FOUND
then
dbms_output.put_line('cursor FOUND');
end if;

if C1%NOTFOUND
then
dbms_output.put_line('cursor notfound');
end if;

IF C1%ISOPEN then
dbms_output.put_line('cursor ISOPEN');
end if;
dbms_output.put_line('cursor count='||c1%ROWCOUNT);
END loop;

EXCEPTION WHEN INVALID_CURSOR THEN
dbms_output.put_line('INVALID_CURSOR');
END;
END;

Oracle Integration tutorials For Beginner

Oracle Integration

1- What is integration?

When there is a requirement to share/transfer/exchange a data between two different application/system which is having same database (Oracle database) then this can be done with help of integration. i.e To connect two different application integration is used.

2- Different Type Of Integration ?

- Oracle Advance Queue (AQ/MQ) based integration: It is purely based on oracle plsql & used in oracle database only.

- File Integration: This can be done by sharing the data in different files format like (DOC/TXT/EXCEL/CSV/) , Files can be sharee on server of two system by multiple programing language. (in oracle it can be done using Unix Schell Script/ UTL File Packages which is PLSQL based.)

3- Oracle Advance Queue (AQ/MQ):

- This is oracle PLSQL based Integration which is based on advance Queuing concept.

- In this method data can be shared with the help of oracle QUEUE & QUEUE Tables.

- Data shared by using this method is called PAYLOAD whose data type is XML TYPE.

- This is more Secure method of integration.

4- How Oracle Queueing works?

It contains below component:

- Gateway

- QUEUE Table

- QUEUE

- Foreign QUEUE

-SUBSCRIBER

-Propogation Scheduler.

Steps:

Note: Before creating any integration, it is mandatory to open the firewall port between two system. If Firewall port/Connection doesn’t open then Queues will go in error.

- First Create Database Link on oracle DB to enable connection between two system.

- Then CREATE_QUEUE_TABLE In source & destination system.

- Then Create CREATE_QUEUE on queue table.

- Then START_QUEUE

- Then Register_Foreign_Queue

- Then Add_Subscriber

- Then Schedule_Propagation

- Then Enable_Propagation_Schedule

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

PLSQL Code to create/Configure Queue (This is only for reference No need to Remember Code in interview.):

Configure LINK:

DECLARE

v_options SYS.mgw_properties;

v_prop SYS.mgw_mqseries_properties;

BEGIN

v_prop := SYS.mgw_mqseries_properties.construct ();

v_prop.max_connections := 10;

v_prop.queue_manager := 'QMEIBS1';

v_prop.hostname := '10.87.205.70';

v_prop.channel := 'QMEIGS1.CRM.SVRCONN';

v_prop.port := '7111';

v_prop.outbound_log_queue := 'ENTCRM.LOG.OUT';

v_prop.inbound_log_queue := 'ENTCRM.LOG.IN';

DBMS_MGWADM.create_msgsystem_link

(linkname => BAJAJAL_TO_BAJABFIN_LINK', -- link name

properties => v_prop, -- mqseries driver properties

options => v_options -- options

);

END;

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

--unconfigure Link

begin

dbms_mgwadm.remove_msgsystem_link (linkname => 'CRM_ENT_TO_EAI_LINK');

end;

1- begin DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table =>'XXBAJAJALIANZ_INSURANCE_INTBL', queue_payload_type => 'SYS.MGW_BASIC_MSG_T', multiple_consumers => FALSE ); end;

2- begin DBMS_AQADM.CREATE_QUEUE (queue_name => 'XXBAJAJALIANZ_INSURANCE_INQ', queue_table =>'XXBAJAJALIANZ_INSURANCE_INTBL'); end;

3- begin DBMS_AQADM.START_QUEUE (queue_name => 'XXBAJAJALIANZ_INSURANCE_INQ'); end

4- begin dbms_mgwadm.register_foreign_queue(name =>'XXBAJAJALIANZ_INSURANCE_INFQ', linkname =>'CRM_ENT_TO_EAI_LINK',provider_queue =>'DOX.CMCOLLSRV.ECRM.ESB.RPO.02',domain => dbms_mgwadm.DOMAIN_QUEUE); end;

5- begin dbms_mgwadm.add_subscriber(subscriber_id =>'XXBAJAJALIANZ_INSURANCE_INSUB', propagation_type => dbms_mgwadm.inbound_propagation, queue_name =>'XXBAJAJALIANZ_INSURANCE_INFQ@CRM_ENT_TO_EAI_LINK',destination =>'apps.XXBAJAJALIANZ_INSURANCE_INQ');end;

6- begin dbms_mgwadm.schedule_propagation(schedule_id =>'XXBAJAJALIANZ_INSURANCE_INSCH' ,propagation_type =>dbms_mgwadm.inbound_propagation,source =>'XXBAJAJALIANZ_INSURANCE_INFQ@CRM_ENT_TO_EAI_LINK',destination =>'apps.XXBAJAJALIANZ_INSURANCE_INQ' , latency => 2); end;

7- begin dbms_mgwadm.enable_propagation_schedule('XXBAJAJALIANZ_INSURANCE_INSCH'); end;

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

Tuesday, 12 May 2020

Oracle XML Publisher Multi Language Reports

1. Create your rdf or data template for your data model as per scope, supporting multi language.

2. Create your RTF template for the layout.

3. Create data definition and upload your RTF File to the template definition as we do for the normal XML publisher reports.

4. In the template definition, Check the TRANSLATABLE check box and default language as EN-US (R12).

5. Now from the template definition page, Export the Translation for the default template uploaded. This will get downloaded as an .xlf file., Eg: XXFALIFREP_en_US.xlf where en_US is the Language and Territory w.r.t the languages installed.

6. In the translation file, edit the target language and the corresponding translated label for each English label in notepad. Translations should be obtained from the Client.

The tag will look like below, for English to Spanish translation.

Example translations for header of the xlf file:

Example translations for labels:

<body>

<trans-unit id="4d626ab9" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">

<source>Asset Number</source>

<target>Número de Activo</target>

<note>Text located: body/table/table</note>

</trans-unit>

<trans-unit id="731282e2" maxbytes="4000" maxwidth="24" size-unit="char" translate="yes">

<source>Life Years Asset</source>

<target>Años de Vida Útil del Activo</target>

<note>Text located: body/table/table</note>

</trans-unit>

7. SAVE the file in UNIX FORMAT and UTF8 ENCODING and name it as “XXFALIFREP_es_ES.xlf” for our case.

Note: If the file is not saved as per this specification, the translation will not get uploaded to the template definition.

8. Upload the translations to the template definition(PFA upld_trnslations.png)

9. After upload the Translations will be shown like Template_Definition_Page.png

10. Now when we submit the concurrent program for different user from different Geography (or login to apps with language as Spanish), the Concurrent output on completion can be viewed in Spanish.

Thursday, 23 May 2013

CUSTOMER DUMP

SELECT DISTINCT
a.customer_name
,a.customer_name_phonetic alternate_name
,a.customer_number
,a.party_number
,ps.party_site_number site_number
,d.LOCATION
,a.orig_system_reference customer_ref
,b.orig_system_reference address_ref
,b.attribute3 site_classification
,b.attribute4 legacy_customer_num
,b.org_id
,hou.name circle_name
,d.SITE_USE_CODE
,d.PRIMARY_FLAG
,e.creation_date
,getlogin(e.created_by)
,ADDRESS1
,ADDRESS2
,ADDRESS3 ,
B.CITY,
b.STATE,
B.COUNTRY ,
B.POSTAL_CODE,
decode(a.STATUS,'A','Active','I','Inactive',b.STATUS) cust_header_status,
decode(b.STATUS,'A','Active','I','Inactive',b.STATUS) cust_site_status,
(select f.PARTY_NAME object
from HZ_RELATIONSHIPS_V f
where
f.subject_id = a.party_id
and rownum = 1) object,
d.SITE_USE_ID
FROM ra_customers a,
ra_addresses_all b,
hz_party_sites ps,
hz_cust_accounts_all c,
hz_cust_site_uses_all d,
hz_cust_acct_sites_all e,
hr_operating_units hou
--HZ_RELATIONSHIPS_V f
WHERE a.party_id=b.party_id
AND b.party_id=c.PARTY_ID
AND e.PARTY_SITE_ID=b.PARTY_SITE_ID
AND e.party_site_id=ps.party_site_id
AND d.org_id = hou.organization_id
AND e.cust_account_id = c.cust_account_id
AND e.cust_account_id = a.customer_id --------This code added by SIVA Reddy.
AND e.cust_acct_site_id = d.cust_acct_site_id
AND b.org_id=hou.ORGANIZATION_ID
--and nvl(a.party_id,1) = nvl(f.subject_id,1)
AND b.org_id = 109
ORDER BY ps.party_site_number