Oracle Fusion #19: Bursting in Oracle Fusion (Delivery Channel - EMAIL, FTP, SFTP & FILE)
Вставка
- Опубліковано 31 жов 2024
- -- Oracle Fusion Video Series : Video 19
#SQL
#PLSQL
#Oracle
#ORACLEAPPS
#OracelFusion
#TechTalkswithNaresh
#Naresh
#R12
#EBSConsultant
Join Our Telegram Group:
t.me/techtalks...
FOLLOW US :
============
UA-cam : / techtalkwithnaresh
For More details Contact Us:
Email: nareshtech21@gmail.com
--------------------------------------------------------------------------------------------------------------------------------------------------------------
In this Playlist I had uploaded all the videos which help you to learn the Oracle Fusion BI Reports and Oracle Fusion OTBI Reports which will help you to improve your skills. If you have any doubt's regarding any of the uploaded videos you can write in comment section. If you need more videos on other topics or training in Oracle fusion you can reach out me at nareshtech21@gmail.com. Please do like, share and Subscribe the channel to get latest videos.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Following are the Methods(DELIVERY CHANNEL) to send invoice using bursting program: EMAIL, FAX, FTP, SFTP, FILE, PRINT & WEBDEV.
1. Create Data Model
Query:
select b.trx_number,
a.customer_trx_id,
b.term_id,
b.org_id,
b.batch_id,
b.status_trx,
b.application_id
FROM ra_customer_trx_lines_all a,
ra_customer_trx_all b
WHERE a.customer_trx_id= b.customer_trx_id
AND a.customer_trx_id in (1002,1)
2. Create Layout and Template.
3. Run The Report and check it is generating output properly or not.
4. Go to Bursting in left corner of data model screen, add new & give required details.
5. In Split by field & Delivered by field select the value(Column) on which you have to split the PDF to send the output (like customer_name, transaction_id or Invoice_no).
E.g: split by customer_name Split by Transaction no.
Invoice 5 Customer1 -- 1 file, 5 Invoice 5 files
3 Customer2 -- 3 file, 3 Invoice 3 files
1 Customer3 -- 1 file, 1 Invoice 1 files
6. In SQL Query section make the query as bellow sample query.
SELECT
trx_number KEY, -- Split by
'Bursting_Layout' TEMPLATE, -- Catalog
:xdo_user_report_locale LOCALE, -- English (es)
'PDF' OUTPUT_FORMAT, -- PDF,HTML,CSV,RTF,EXCEL
'EMAIL' DEL_CHANNEL, -- EMAIL/FAX/FTP/FILE/SFTP/PRINT/WEBDAV
'AR INVOICE_'||trx_number OUTPUT_NAME, -- Output Name
’nareshtech21@gmail.com’ parameter1, -- To
'nareshtech21@gmail.com' parameter2, -- cc
‘nareshtech21@gmail.com’ parameter3, -- From
‘Bursting Program testing’ parameter4, -- Subject
‘Dear Customer PFA Attachment’ parameter5, -- Body
‘true’ parameter6, -- Attachment
‘Reply-To’ parameter7, -- Reply to
‘nareshtech21@gmail.com’ parameter8 -- Bcc
FROM (
select distinct b.trx_number
from ra_customer_trx_lines_all a,
ra_customer_trx_all b
where a.customer_trx_id= b.customer_trx_id
and a.customer_trx_id in (1002,1))
7. Create the Layout for the Data Model.
8. In Layout properties Check the check box Enable Bursting.
9. Run the report by using the scheduler or ESS job & get the output.
Notes:
1. General Split by field is equal to Deliver by field.
2. In schedule report -- diagnostic check all check boxes to get the log & the SQL plan etc according to requirement.
3. According to the DELIVERY_CHANNEL(EMAIL,FTP,SFTP,etc) we have to change the Parameters (parameter1,....).
Hi naresh, this is pavan I have followed your videos on oracle fusion hcm technical.
@Pravin Thanks
easy to understand u r telling the concept
@Siva Thanks. Subscribe to get latest updates.
nice explanation was superbb do more videos on hcm Oracle hcm fusion
@Surendra I will try to upload more videos on mention topic.
Thanks so much for your help ,sharing data !
@Oracke Developer Thanks for Acknowledgement. Keep sharing keep learning.
Very informative and helpful. Thanks a lot. Keep making more videos.
@Runjhun Welcome !!!!!
Great Video Naresh...very informative!!!
Thanks Murali....
Very informative and very simple, keep the good work up!!
@Nabeel Thanks subscribe to get latest update
Wow man well explained
@Irfan Thanks for Acknowledgement. Subscribe to get latest updates.
Thanks for explaining in details
@Rajmanmaurya Welcome, Subscribe to get latest updates.
Thank you, you are very clean and clear. Great Video!
@Aitor Welcome. Subscribe to get latest updates
Helpful for me. Life saver... Thankssssss
@Mangesh Thanks
helpful bro....Thank you.
@Bhargav Welcome. Subscribe to get latest updates.
Great video. Keep making video.you are doing great job. You explained well 👏👌
@Sanket Thanks a Lot !!!
BRO THANKS FOR GIVING INFORMATION
AND PLS MAKE ONE VEDIO FOR TIME AND LABOUR
@Sai Welcome. I will try to prepare on it.
Thanks for sharing
@kv Thanks for Acknowledgement. Subscribe to get latest updates.
Thanks for such an amazing video
@Divyansh Thanks a lot for your word. Subscribe to get latest videos.
Very nicely explained!!! Thanks alot!
@Marufa Thanks.... Please subscribe to get latest updates..
Very informative.
Can I burst the report without adding Spilt by and Delivery by. E.g I want to have one csv file instead number of CSV files.
@Shashidhar If you want only one file gove the condition which have a common valu for all records. Or you can just check the filed is mendetor or not.
Thanks Naresh
Welcome... Subscribe to get latest updates.
Very Helpfull...
Satish Pawar Thanks...
Hi naresh
Can you please share Query for
File Bursting and explain the process how to execute it
@Summanths Go through my same bursting vidro I had explained all those things in thag video.
How to call procedure in bip reports..
Plz provide a video for us
@Gopi I will try to make a video on this asap. Subscribe to get latest updates.
how to create transmission file and public key private key encryption bank payments?
@Eswarv This are some different concepts. I will try to upload the video on same. Subscribe to get latest updates.
Hi,
How can we deliver the data to multiple emails with only their respective details in the data output using Email delivery channel.
As it is hard coded with only one email in the bursting query. how can we send to multiple emails at once with only their related data.
@Pranav Seperate the emails with comma and try. Let me know for any questions.
I have seperated the emails with , but output is only coming to one email id
Is split by and deliver by will same or what if these two are different
@Sriram I depends on user. How to use. We can define same as well as seperate values also, but mostly we will use same.
Hi Naresh - Very nice video. Just to clarify, with this method can I actually schedule a BIP report (on a recurrence) to be emailed with the file attached to the email? Thanks.
@Mastang You can schedule. But my advice is to create an ESS job and schedule it by ESS job.
@@TechTalkwithNaresh Ok great, and the schedule will send an email with the BIP report attached to the email? Do I have to use the Split By function? I just want one file output for the entire query as an attachment to the email.
@Mustang You can ignore split by in this case, if it is not mendatory.
Hi sir , any video on transfer file from winscp sftp location to oracle fusion cloud
@Aditi you can download any file from winscp. By using the archive option you can upload the file in fusion. I have the second part video in my playlist.
Can u explain how to do brusting using DV Data set
@Ramyarangasamy Currently I won't have any available video on mentioned topic. Will try to upload soon.
Hi sir
I need to hide/eliminate the key filed in the output which is used in bursting. For example I have added the select "1" as key, cloumn 1 ,column 2 from table
I used these 1 as key in bursting
In out I am getting key, column1, column2. I don't want these key to get in output.
Can you pls help what can be done in this case
@Raju I am not getting what you are trying to achieve. Please explain in detail and write on nareshtech21@gmail.com.
@@TechTalkwithNaresh Hi sir
I have written the SQL query in data model like
Select "1" as key, supplier_id from poz_suppliers
And created the report
I added the bursting logic as
Select 1 as key,
..............
........from dual
I have tested this report it and bursted it to FTP it completed successfully
In output I have seen two columns
1) key
2) supplier_id
Here I need only supplier_id not key
I have used generate RTF option in template creation in report
@Raju download the template on local and remove the column from template which you won't need. Re upload the template and check. It will resolve your issue.
@@TechTalkwithNaresh I will try that thanks naresh
any video on creating trigger in oracle fusion??
@Pooja Currently I don't have any video on trigger in fusion. U will try to upload asap. Subscribe to get latest updates.
How can we send excel file in mail using bursting???
@Prajakta You can add the parameters as I mentioned in my video.
Hi Sir ,
Is it possible to Send the output of file as encrypted using bursting?
@Raju I had not try such requirement I have to check for this.
Hai sir I am fan of you. I have one doubt pls help here. We can schedule the report from browse cataloge --more--schedule and also we can schedule it from ESS job. What is difference between them and what is significance. Please help
@Raju Thanks for Acknowledgement. We can schedule report from both way, while scheduling the report from catlog we need the Data Model and report access. (You need to know the location of Data model and report where it stored) which only the developer knows. But using ESS JOB any one can schedule the report even functional or client. There you don't have to know the location of report.
Great job !
Can you please create one video how to use api in the pages.
@Kiran Thanks... I will try to create it asap. Please subscribe to get latest updates.
@@TechTalkwithNaresh Before only subscribed
Hi sir
As we know when the bip report doesn't have any data to fetch when we use bursting it will fail saying no data found to process as this is the standard functionality of Oracle but we have one requirement where file need to send to FTP server eventhough there is no data found. How this can be achieved? Pls help
@Raju If there is nodata print custom msg in the report, (like NOData) then it will work.
@@TechTalkwithNaresh thanks naresh
how to automatically saving file when reports generated in bi publisher
@Janai You have not to save the file for bursting. It sends the file if the program got successful.
Hi sir
We have a requirement that bip report need to go to FTP eventhough there is no data . Client is expecting only header also when ever there is no data. Is this can be achieved to send the bip report even though there is no data?
@Raju If there is nodata print custom msg in the report, (like NOData) then it will work.
Split By can be same as delivery by but it can be different too. Split by just groups the data which is to be sent and deliver by does the rest..
So if we take the first row of your example, then it splits 5 invoices or 5 rows into one file and if i use deliver by as transaction id, then i get 5 emails with the same 1 file. Correct me if i am wrong..
Also Key should always match deliver by not split by
@Vignesh split by is basically for batch of invoice by each invoice. The data which is sent must be sorted by this element. Deliver by is the delivery criteria which we had determined...
Yes as mention in video also it can be different in some cases.....
I have one question sir, is it default that an output can be sent only as an attachment or it can be sent directly as an embedded message in email?
@Harshith we can send the embedded messages also as the body (Paramater) of email. But if you want to send the message only you xan use the AOL Alert also.
@@TechTalkwithNaresh thanks
Hi naresh, i want to edit the email body formatting for my report , how can I do it
@Ravi use string operations for formatting.
Sir there are two drop down lists CATEGORY and SUBCATEGORY....
We want subcategory to be cascaded with check boxes is it possible? my consultant saying not feasible in oracle cloud fusion.
@Vasu Can you please elaborate your issue with more details.
Vasu for one item main category there will be multiple sub categories out which one should be defaulted if ur consultant defaults one always it will be functionality mistake ...so he can default values under one main category what are all the sub categories but users has to select the values what ever he needs...this will make ur requirements complete
If we want to display more columns in select like only text number wants to display customer name and location we can display in select but for bursting how do we map as here everything is mapped as a parameter..can u pls help me in understanding this ...
Thank u for the video good and informative video
@Ancer In your case you have to built the query with such mapping.
do you have any video which you used window server for FTP
@Prasanna I don't have the any windy based FTP video.
Sir how to do bursting without any mail attachment?
@R Baibhaw Kumar change the Paramater values only according to your requirements, as explain in videos.
Hi Naresh
Plz provide how to use Event trigger and flexfileds in bip reports in oracle fusion and then plz reply this comment
Hi Gopi I will try to upload the video on mentioned topic. Subscribe to get latest updates.
@@TechTalkwithNaresh waiting for event trigger & flexfields videos
Hello Naresh,
Can we send shipment details via email in oracle fusion?
Hi Narendar, We can send it but just the thing is you have to create a report and do bursting for same.
Could you please upload HCM Extract Video
Sure
sir please tell about advanced Rtfconcepts and excel templates
@Lokesh I had already uploaded the video on excel template. On rtf I will try to upload soon.
@@TechTalkwithNaresh ha sir waiting
good
@Skild Solutions Thanks
How to add multiple mail id in cc
@Shashank Try by separating with coma ",".
Hi Naresh, Do we need to configure anything in the content server for from address ? I am trying to give my email id as from address but the system doesn't have access to my email id hence the report scheduled is failing. Guide me .
@Ram Kumar are you able to get the email from any other email id?
@@TechTalkwithNaresh No Naresh I tried with the server mail address but still the report failed
If it fails with server email address then two this can be there, 1. The email confirmation is not done for the instance. Or the mailing server which you are using is down.
@@TechTalkwithNaresh how to do the email confirmation for the instance
@kanigiri Configuration the email service is done by DBA.
I tried this but mail not came
@Sriram What was the issue. Check bursting is enabled for your instance or not.
How to receive user his record only not the entire report. Like some example i want to send user wise personal information so user should receive the his personal info only not the others.
Please explain.
@kiran In same video I had explain for customer you just have to assume user instade of the customer. Modify your query accordingly. Please subscribe to get latest videos.
video title is misleading , there is no option explain other then email.
@Fayyaz Tge email is explained in detail and for other there is over view.
hi naresh ,
in bursting split = trx_number ; as i was delivery channel email but i have received 100 emails .
after 10 mins again 100 emails wt was the problem .
i need only consolidatin list .
how to rectify it ?
@Dasari when you query the data by Data model The number of trx_number are there that much email you will receive. Check your Data model, bursting query and the Bursting defination it have to the same paramaters.
Can you tell me what actually is report writing in Oracle HCM?
@Adiba I was not able to get your concern. Can you write in detail
@@TechTalkwithNaresh What is BI report writing in Oracle Fusion?
Very nice, could you pls help me how to create dependent value sets in ESS job
@Panibhushanam I had already uploaded the video on value set, please go through the place list. Subscribe to get latest updates.
Can you please create one video for how to create dependence values/LOV in ESS Job creation for customer report if possible.
@ARV.. I had already uploaded the video on Dependent Values/LOV by same way you can create just you have to run the report using ESS job.....
Link for Dependent LOV: ua-cam.com/video/LS-YtzSsyiQ/v-deo.html
For more videos check the play list.......
@@TechTalkwithNaresh. That's great to hear. I will go through the video. Thank you.