Oracle Bursting Reports
Below queries we can use for bursting any report to any desired FTP/SFTP, Email, UCM Server.
Main Query - Single Files
SELECT
'XX' key,
papf.person_number,
pea.email_address,
ppnf.full_name
FROM
per_all_people_f papf,
per_email_addresses pea,
per_person_names_f ppnf
WHERE
papf.person_id = pea.person_id
AND papf.person_id = ppnf.person_id
AND pea.email_type = 'W1'
AND ppnf.name_type = 'GLOBAL'
AND ROWNUM <= 10
Main Query - Multiple Files
SELECT
'XX' key,
papf.person_number,
pea.email_address,
ppnf.full_name
FROM
per_all_people_f papf,
per_email_addresses pea,
per_person_names_f ppnf
WHERE
papf.person_id = pea.person_id
AND papf.person_id = ppnf.person_id
AND pea.email_type = 'W1'
AND ppnf.name_type = 'GLOBAL'
AND ROWNUM <= 10
Split By:
The Split By element is an element from the data that will govern how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.
Deliver By:
The Deliver By element is the element from the data that will govern how formatting and delivery options are applied. In the invoice example, it is likely that each invoice will have delivery criteria determined by customer, therefore the Deliver By element may also be CUSTOMER_ID.
Email Bursting Query
SELECT DISTINCT
'XX' key, -- value of this column must match the value of the field “Deliver By”
'test' template,
'en-EN' locale, -- Locale for which you have developed the template
'PDF' output_format, -- output format you desire
'EMAIL' del_channel, -- This is the delivery channel.
'EMP_DETAILS'
|| '_'
|| to_char(sysdate, 'mm/dd/yyyy') output_name, -- Name of the file
'DINESH.NEREDU@gmailcom' parameter1, -- To field of the email
'DINESH.NEREDU@gmail.com' parameter2, -- CC field of the email
'bipublisher-report@oracle.com' parameter3, -- this value will show up as “From” in the email
'EMP_DETAILS' parameter4, -- subject of the email
'Hi'
|| CHR(13)
|| ','
|| 'Please find attached Emp Details.' parameter5, -- Body of the email
'true' parameter6, -- attachment flag. for pdf, excel keep it as true
'DINESH.NEREDU@gmail.com' parameter7 --Reply To
FROM
dual
SFTP Bursting Query
SELECT
'XX' key,
'Emp_Details_FTP' template,
'en-US' locale,
'CSV' output_format,
'FTP' del_channel,
'Default Document' output_name,
'OIC_File_Server' parameter1,
NULL parameter2,
NULL parameter3,
'/upload/DineshN/' parameter4,
'EMP_DETAILS'
|| systimestamp
|| '.csv' parameter5,
'TRUE' parameter6
FROM
dual
UCM Server Bursting Query
SELECT DISTINCT
'XX' key, -- Split Key from data model SQL
'Emp_Details_UCM_Report' template,
'en-US' locale,
'PDF' output_format, -- Output type
'WCC' del_channel, -- To send to content server
'FA_UCM_PROVISIONED' parameter1, -- Content Server name
'FAFusionImportExport' parameter2, -- Security group
'john.bull' parameter3, -- Author of the file to be shown in content server
'' parameter4, -- Account (Optional)
'Emp_Details' parameter5, -- Title
'Emp_Details' ||'.pdf' parameter6, -- Output File name
'Emp_Details' parameter8, -- Content ID (Optional)
'false' parameter9, -- Custom metadata (Optional)
'/Contribution Folders/Inbound' parameter10 -- Folder path to deliver files
FROM
DUAL
0 Comments