Sample Before Report Trigger
function BeforeReport return boolean is
begin
DECLARE
BEGIN
BEGIN
SRW.USER_EXIT('FND SRWINIT');
EXCEPTION
WHEN SRW.USER_EXIT_FAILURE THEN
SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger - SRWINIT USER EXIT');
return (FALSE);
END;
BEGIN /*MOAC*/
:P_ORG_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
END;
Sample After Report Trigger Calls Bursting
function AfterReport return boolean is
nReturnRequestID NUMBER;
begin
srw.message(1,'Start of after report trigger: ');
BEGIN
IF :CF_REQ_BY_EMAIL IS NOT NULL THEN
nReturnRequestID := FND_REQUEST.SUBMIT_REQUEST ( 'XDO' -- Application Short Name
, 'XDOBURSTREP' -- Program Short Name
, 'XML Publisher Report Bursting Program' -- Program Name
, NULL -- Start Date
, FALSE -- Sub-request
/* Program Parameters START */
, 'Y' -- Hidden Param
, :P_CONC_REQUEST_ID -- Parameter 1: Data extract Request ID
, 'N' -- Debug Flag, defaulted to 'N'
/* Program Parameters END */
);
srw.message(1,'REQUEST ID of the BURSTING PROGRAM is: ' || nReturnRequestID);
END IF;
EXCEPTION
WHEN OTHERS THEN
srw.message(1,'Unhandled error in after report trigger: ' || SQLERRM);
end;
BEGIN
SRW.USER_EXIT('FND SRWEXIT');
EXCEPTION
when srw.user_exit_failure then
srw.message(1,'Failed in AFTER REPORT TRIGGER');
return (FALSE);
END;
return (TRUE);
End;
Sample After Parameter Form Trigger Using Laxical Parameters
function AfterPForm return boolean is
begin
srw.message(99999,'$Header: OEXOEACK.rdf 120.8.12000000.4 2008/07/28 04:03 zsingh noship $');
/*----------- Disable Trace Bug 3304392 ----------------*/
/*
declare
x varchar2(100);
begin
if (:p_enable_trace = 'Y') Then
srw.do_sql('ALTER SESSION SET SQL_TRACE=TRUE');
select 'OEXOECK TRACE' into x from dual;
end if;
end;
*/
/*----------- Disable Trace Bug 3304392 Ends----------------*/
BEGIN
if :p_booked_status is NOT NULL then
if :p_booked_status = 'Y' then
:lp_booked_status := ' and h.booked_flag = ''Y'' ' ;
else
:lp_booked_status := ' and h.booked_flag = ''N'' ' ;
end if;
else
:lp_booked_status := '';
end if ;
/*----------- Lexical parameter for the Order Date range --------------------*/
if (:p_order_date_low is NOT NULL ) AND (:p_order_date_high is NOT NULL )then
:lp_order_date := ' and h.ordered_date between :p_order_date_low and (:p_order_date_high+1) ' ;
elsif (:p_order_date_low is NOT NULL ) then
:lp_order_date := ' and h.ordered_date >= :p_order_date_low ' ;
elsif (:p_order_date_high is NOT NULL ) then
:lp_order_date := ' and h.ordered_date <= (:p_order_date_high+1) ' ;
end if;
/*----------- Lexical parameter for the Schedule Date range --------------------*/
if (:p_schedule_date_low is NOT NULL ) AND (:p_schedule_date_high is NOT NULL )then
:lp_schedule_date := ' and (l.schedule_ship_date between :p_schedule_date_low and (:p_schedule_date_high+1)) ' ;
elsif (:p_schedule_date_low is NOT NULL ) then
:lp_schedule_date := ' and l.schedule_ship_date >= :p_schedule_date_low ' ;
elsif (:p_schedule_date_high is NOT NULL ) then
:lp_schedule_date := ' and l.schedule_ship_date <= (:p_schedule_date_high+1) ' ;
end if;
/*----------- Lexical parameter for the Request Date range --------------------*/
if (:p_request_date_low is NOT NULL ) AND (:p_request_date_high is NOT NULL )then
:lp_request_date := ' and (l.request_date between :p_request_date_low and (:p_request_date_high+1)) ' ;
elsif (:p_request_date_low is NOT NULL ) then
:lp_request_date := ' and l.request_date >= :p_request_date_low ' ;
elsif (:p_request_date_high is NOT NULL ) then
:lp_request_date := ' and l.request_date <= (:p_request_date_high+1) ' ;
end if;
/*----------- Lexical parameter for the Promise Date range --------------------*/
if (:p_promise_date_low is NOT NULL ) AND (:p_promise_date_high is NOT NULL )then
:lp_promise_date := ' and (l.promise_date between :p_promise_date_low and (:p_promise_date_high+1)) ' ;
elsif (:p_promise_date_low is NOT NULL ) then
:lp_promise_date := ' and l.promise_date >= :p_promise_date_low ' ;
elsif (:p_promise_date_high is NOT NULL ) then
:lp_promise_date := ' and l.promise_date <= (:p_promise_date_high+1) ' ;
end if;
/* Changed the ship_cust, bill_cust, del_cust to ship_party, bill_party, del_party for the bug# 2347438 */
/* Modified ship_party.customer_name to ship_party.party_name, this is done for all bill/del as well --bug 2425515 */
/*----------- Lexical parameter for the Customer Name (Ship To) range ---------*/
if (:p_ship_to_customer_name_lo is NOT NULL ) AND (:p_ship_to_customer_name_hi is NOT NULL ) then
:lp_ship_to_customer_name := ' and (ship_party.party_name between :p_ship_to_customer_name_lo and
:p_ship_to_customer_name_hi) ';
elsif (:p_ship_to_customer_name_lo is NOT NULL ) then
:lp_ship_to_customer_name := ' and ship_party.party_name >= :p_ship_to_customer_name_lo ';
elsif (:p_ship_to_customer_name_hi is NOT NULL ) then
:lp_ship_to_customer_name := ' and ship_party.party_name <= :p_ship_to_customer_name_hi ';
end if;
/*----------- Lexical parameter for the Customer Name ( Bill To)range ------------*/
if (:p_bill_to_customer_name_lo is NOT NULL ) AND (:p_bill_to_customer_name_hi is NOT NULL ) then
:lp_bill_to_customer_name := ' and (bill_party.party_name between :p_bill_to_customer_name_lo and
:p_bill_to_customer_name_hi) ';
elsif (:p_bill_to_customer_name_lo is NOT NULL ) then
:lp_bill_to_customer_name := ' and bill_party.party_name >= :p_bill_to_customer_name_lo ';
elsif (:p_bill_to_customer_name_hi is NOT NULL ) then
:lp_bill_to_customer_name := ' and bill_party.party_name <= :p_bill_to_customer_name_hi ';
end if;
/*--------- Lexical parameter for the Customer Name ( Deliver To)range ------------*/
if (:p_del_to_customer_name_lo is NOT NULL ) AND (:p_del_to_customer_name_hi is NOT NULL ) then
:lp_del_to_customer_name := ' and (del_party.party_name between :p_del_to_customer_name_lo and
:p_del_to_customer_name_hi) ';
elsif (:p_del_to_customer_name_lo is NOT NULL ) then
:lp_del_to_customer_name := ' and del_party.party_name >= :p_del_to_customer_name_lo ';
elsif (:p_del_to_customer_name_hi is NOT NULL ) then
:lp_del_to_customer_name := ' and del_party.party_name <= :p_del_to_customer_name_hi ';
end if;
/*----------- Lexical parameter for the Order Number range ------------------*/
if (:p_order_num_low is NOT NULL ) AND (:p_order_num_high is NOT NULL ) then
if ( :p_order_num_low = :p_order_num_high) then
:lp_order_num := ' and h.order_number = :p_order_num_low ';
else
:lp_order_num := ' and (h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high)) ';
end if;
elsif (:p_order_num_low is NOT NULL ) then
:lp_order_num := ' and h.order_number >= to_number(:p_order_num_low) ';
elsif (:p_order_num_high is NOT NULL ) then
:lp_order_num := ' and h.order_number <= to_number(:p_order_num_high) ';
end if;
/*----------- Lexical parameter for Salesrep --------------------*/
if :p_salesrep is NOT NULL then
:lp_salesrep := ' and sr.name = :p_salesrep ' ;
else
:lp_salesrep := null;
end if ;
if :p_created_by is NOT NULL then
:lp_created_by := ' and u.user_name = :p_created_by ' ;
else
:lp_created_by := null;
end if ;
/*------------- Lexical Parameter for the Open Orders Only
----------------------- */
if :p_open_orders = 'Y' then
:lp_open_orders :=
'and h.open_flag = ''Y''';
else
:lp_open_orders := null;
end if;
/*_______________Lexical Parameter for Order Type ___________________________*/
if :p_order_type is not null then
:lp_order_type := ' and h.order_type_id = :p_order_type ';
else
:lp_order_type := null;
end if;
/* --------------Lexical Parameter for order Category----------------------*/
if :p_order_num_low=:p_order_num_high then null;
else
if :p_order_category is not null then
if :p_order_category = 'SALES' then
:lp_order_category := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
elsif :p_order_category = 'CREDIT' then
:lp_order_category := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
elsif :p_order_category = 'ALL' then
:lp_order_category := null;
end if;
else
:lp_order_category := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
end if;
end if;
/*----------------Lexical Parameter for Line Category ---------------------*/
if :p_line_category is not null then
if :p_line_category = 'SALES' then
:lp_line_category := 'and l.line_category_code = ''ORDER'' ';
elsif :p_line_category = 'CREDIT' then
:lp_line_category := 'and l.line_category_code = ''RETURN'' ';
elsif :p_line_category = 'ALL' then
:lp_line_category := null;
end if;
else
:lp_line_category := 'and l.line_category_code = ''ORDER'' ';
end if;
/*---------------Set User Language -------------------*/
select userenv('LANG')
into :p_user_lang
from dual;
END ;
return (TRUE);
end;
No comments:
Post a Comment