Call different rtf templates from Oracle reports based on certain conditions (e.g. org_id)
There are many cases that we need to generate different report layouts but underneath data or extracting logic is the same. For example, invoice. Normally invoices with different languages and layouts should be generated for different on organizations (or countries).
If we build the underneath extraction logic by using Oracle reports, we can call different XML Publisher requests in after report trigger.
So in AfterReport trigger, the code could be like this.
Function AfterReport return boolean is
v_case_result number,
v_org_name varchar(100);
begin
select name into v_org_name
from hr_operating_units where organization_id = :CF_ORG_ID;
IF :CF_ORG_ID = '692' THEN
v_case_result := FND_REQUEST.SUBMIT_REQUEST('XDO', 'XDOREPPB', '', '', FALSE, :P_CONC_REQUEST_ID,'20004', 'ARINVOU_UK','', 'N', 'RTF','PDF', '','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
ELSIF :CF_ORG_ID = '108' THEN
v_case_result := FND_REQUEST.SUBMIT_REQUEST('XDO', 'XDOREPPB', '', '', FALSE, :P_CONC_REQUEST_ID, '20004', 'ARINVOU_CAD','', 'N', 'RTF', 'PDF', '','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','', '','','','','','','','','','','','','','','','','','','','','','','','','','','','','','', '','','','','','','','','','', '','','','','','','');
ELSIF v_org_name = 'OU_USD_PRESAGIS' OR v_org_name = 'OU_CAD_PRESAGIS' THEN
v_case_result := FND_REQUEST.SUBMIT_REQUEST('XDO', 'XDOREPPB', '', '', FALSE, :P_CONC_REQUEST_ID,'20004', 'ARINVOU_PRESAGIS','', 'N', 'RTF', 'PDF', '','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','');
ELSIF v_org_name = 'OU_CAD_PROFESSIONAL_SERVICES' THEN
v_case_result := FND_REQUEST.SUBMIT_REQUEST('XDO', 'XDOREPPB', '', '', FALSE, :P_CONC_REQUEST_ID,'20004', 'ARINVOU_PSI','', 'N', 'RTF', 'PDF', '','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','');
ELSE
v_case_result := FND_REQUEST.SUBMIT_REQUEST('XDO', 'XDOREPPB', '', '', FALSE, :P_CONC_REQUEST_ID, '20004', 'ARINVOU','', 'N', 'RTF', 'PDF', '','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','');
END if;
srw.user_exit( 'FND SRWEXIT' );
if :p_commit_flag = 'Y'
then
srw.do_sql('commit work');
else
srw.do_sql('rollback work');
end if; return (TRUE);
end;
We just need to define templates with codes above in XML publisher so that when the request is submitted, the right template will be picked up.
For example, template with Code: ‘ARINVOU_UK’ needs to be defined with following information by XML publisher administrator responsibility.
Name: AR Invoice –
Application: Development; Data Definition: BI dummy (means it’s not related to any data definition)
Type: RTF;
Default File: OU_UK.rtf
…
Then upload the template file with name: OU_UK.rtf
Template with Code: ‘ARINVOU_UK’ needs to be defined with following information by XML publisher administrator responsibility.
Name: AR Invoice – CAD; Code: ARINVOU_CAD;
Application: Development; Data Definition: BI dummy (means it’s not related to any data definition)
Type: RTF;
Default File: OU_CAD.rtf
…
Then upload the template file with name: OU_CAD.rtf
………