How to Call a report within the from using personalization

How to call the report within the form using personalization

 create the personalization by using following methods

1. seq : 20
    Description : Invoice Report Special
    Level    : Function
    Enabled : Yes

Condition

Trigger Event : When New Form Instance
Processing Mode : Not in Enter-Query Mode

Action : 

Type : Menu
Menu Entry : Special22: Launch invoice Report special
Menu Label : Launch invoice Report special
Reminder line before menu enabled

Menu will be created.Now we can launch the report
1. seq : 30
    Description : launch Invoice Report Special
    Level    : Function
    Enabled : Yes

Condition
 
Trigger Event : Special22
Processing Mode : Not in Enter-Query Mode

Action

seq : 10
Type : Builtin
Language : ALL
Enabled : Yes

Builtin type : Execute a procedure
Argument : we can write whatever the parameter we are passing in report

='declare

begin

XXHFL_INVOICE_AUTO_PKG.AP_INVOICE('''||${item.INV_SUM_FOLDER.VENDOR_Number.value}||''',

'''||${item.INV_SUM_FOLDER.VENDOR_NUMBER.value}||''',

'''||${item.INV_SUM_FOLDER.VENDOR_SITE_CODE.value}||''',

'''||${item.INV_SUM_FOLDER.INVOICE_NUM.value}||''',

'''||${item.INV_SUM_FOLDER.INVOICE_TYPE.value}||'''); COMMIT;

end'
 seq : 20
Type : Message
Language : ALL
Enabled : Yes

Message Type : Show

Message Text :

='Request ID:'||XX_invoice_auto_pkg.print_request_id

seq : 30
Type : Builtin
Language : ALL
Enabled : Yes

Builtin Type : Launch a function

Function code :XX_VIEW_REQ_OUTPUT

function name :XX_VIEW_REQ_OUTPUT

Parameter : ='Request ID:'||XX_invoice_auto_pkg.print_request_id

CREATE OR REPLACE PACKAGE XXHFL_INVOICE_AUTO_PKG

AS

g_message_error VARCHAR2 (320);

l_request_id NUMBER;
PROCEDURE AP_INVOICE(P_SUPPLIER_NAME IN VARCHAR,
P_SUPPLIER_NUM IN VARCHAR,
P_SUPPLIER_SITE IN VARCHAR,
P_TRANSACTION_NUM IN VARCHAR,
P_TRANSACTION_TYPE IN VARCHAR
 );

FUNCTION PRINT_REQUEST_ID

RETURN NUMBER;

END XX_INVOICE_AUTO_PKG;

CREATE OR REPLACE PACKAGE BODY XX_INVOICE_AUTO_PKG

AS

g_message_error VARCHAR2 (320);

PROCEDURE AP_INVOICE(

P_SUPPLIER_NAME IN VARCHAR,

P_SUPPLIER_NUM IN VARCHAR,

P_SUPPLIER_SITE IN VARCHAR,

P_TRANSACTION_NUM IN VARCHAR,

P_TRANSACTION_TYPE IN VARCHAR )

IS

request_id_main NUMBER := NULL;

request_id_sale_invoice NUMBER := NULL;

v_mesg VARCHAR2(100) := NULL;

v_error BOOLEAN;

v_status BOOLEAN;

v_error_info VARCHAR2(1000);

v_error_msg VARCHAR2(2000):=NULL;

v_stmt NUMBER;

l_set_layout_option BOOLEAN;

l_result BOOLEAN:= FALSE;

-- l_request_id NUMBER;

l_request_phase VARCHAR2(20);

l_request_status VARCHAR2(1000);

l_request_dev_phase VARCHAR2(1000);

l_request_dev_status VARCHAR2(1000);

l_request_message VARCHAR2(1000);

l_GL_ACCESS_SET_ID NUMBER; -- new

l_VENDOR_ID NUMBER; -- new

l_vendor_num NUMBER; --new

L_TRANSACTION_TYPE VARCHAR2(100); --new

text_only BOOLEAN;

BEGIN

BEGIN

FND_GLOBAL.apps_initialize ( user_id => fnd_global.user_id,

resp_id => fnd_global.resp_id,

resp_appl_id => fnd_global.resp_appl_id

);

END;

fnd_file.put_line(fnd_file.log,'AP Program started');

l_result := fnd_request.set_mode (TRUE);

l_set_layout_option := apps.fnd_request.add_layout ( template_appl_name => 'XX' ,

template_code => 'XXInvoice' ,

template_language => 'en' ,

template_territory => NULL , --'AE' ,

output_format => 'PDF'

);

l_GL_ACCESS_SET_ID:= FND_PROFILE.VALUE('GL_ACCESS_SET_ID');

fnd_file.put_line(fnd_file.log,'l_GL_ACCESS_SET_ID IS ' || l_GL_ACCESS_SET_ID);

-- To get Vendor_id From Vendor Name

/* BEGIN

IF P_SUPPLIER_NAME IS NOT NULL THEN

SELECT DISTINCT APSA.VENDOR_ID

INTO L_VENDOR_ID

FROM AP_SUPPLIERS APS,

AP_SUPPLIER_SITES_ALL APSA

WHERE APS.VENDOR_ID = APSA.VENDOR_ID

AND APS.VENDOR_NAME = P_SUPPLIER_NAME

AND ROWNUM =1;

ELSE

L_VENDOR_ID:= NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

L_VENDOR_ID:= NULL;

fnd_file.put_line(fnd_file.log,'Exception while fetching Vendor ID from Vendor Name' || sqlerrm);

END;*/

-- To get Vendor_id from Vendor Num

BEGIN

IF P_SUPPLIER_NUM IS NOT NULL THEN

SELECT DISTINCT APSA.VENDOR_ID

INTO l_vendor_num

FROM AP_SUPPLIERS APS,

AP_SUPPLIER_SITES_ALL APSA

WHERE APS.VENDOR_ID = APSA.VENDOR_ID

AND APS.SEGMENT1 = P_SUPPLIER_NUM

AND ROWNUM =1;

ELSE

L_VENDOR_NUM:= NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

L_VENDOR_NUM:= NULL;

fnd_file.put_line(fnd_file.log,'Exception while fetching Vendor ID from Vendor Number' || sqlerrm);

END;

IF P_TRANSACTION_TYPE = 'Debit Memo' THEN

L_TRANSACTION_TYPE := 'DEBIT';

ELSIF P_TRANSACTION_TYPE = 'Credit Memo' THEN

L_TRANSACTION_TYPE := 'CREDIT';

ELSE

L_TRANSACTION_TYPE:= upper(P_TRANSACTION_TYPE);

END IF;

-- Call standard FND_REQUEST package for submit request

l_request_id := fnd_request.submit_request ( 'XX',  

-- Application 'XXinvoice', -- program

NULL,

NULL,

FALSE, -- sub_request

l_GL_ACCESS_SET_ID,

--L_VENDOR_ID, --P_SUPPLIER_NAME,

l_vendor_num, --P_SUPPLIER_NAME,

l_vendor_num, --P_SUPPLIER_NUM,                       

P_SUPPLIER_SITE,

P_TRANSACTION_NUM,

L_TRANSACTION_TYPE --P_TRANSACTION_TYPE

);

IF l_request_id <> 0 THEN

COMMIT;

-- Wait for the completion of Invoice Program

LOOP

l_result := fnd_concurrent.wait_for_request ( request_id => l_request_id ,

INTERVAL => 1,

max_wait => 100,

phase => l_request_phase ,

status => l_request_status ,

dev_phase => l_request_dev_phase ,

dev_status => l_request_dev_status ,

MESSAGE => l_request_message

);

EXIT WHEN l_request_phase = 'Completed';

END LOOP;

ELSE           

fnd_file.put_line(fnd_file.log, 'AP Submit request failed');

fnd_message.retrieve(v_mesg);

fnd_file.put_line(fnd_file.log, l_request_message);

v_error := TRUE;

END IF;

EXCEPTION

WHEN OTHERS THEN

Fnd_File.PUT_LINE(Fnd_File.LOG,'Exception Occured in the AP Invoice Program '||sqlerrm);    

END AP_INVOICE;

FUNCTION PRINT_REQUEST_ID

RETURN NUMBER

AS

BEGIN

RETURN(l_request_id);

END PRINT_REQUEST_ID;

END XX_INVOICE_AUTO_PKG;