OAF download problem from MessageDownload solved


I had a problem in the below Query on using Bind variable where I was not able to download the attachment.

PG disp

Above is the screenshot of my page where open/view file is the Message Download Item. After I used FND_GLOBAL.USER_ID instead of using Bind Variable in VO(View Object) my problem got resolved.

Below is the screenshot what I got as problem by using the bind variable.

PG Error

Just have a look at below Query. I had a Message Download item by which I download a file and the below Query contains a Bind variable by which i was not able to download the file . After I changed the bind variable in VO I was able to download it.

 
SELECT   Document_number,

Document_Name,

Document_file,

VERSION_NUMBER,

OWNING_DEPT_DESC,

CATEGORY_DESC,

CATEGORY,

OWNING_DEPT,

EFFECTIVE_START_DATE,

EFFECTIVE_END_DATE,

check_switcher (DOC_ID, :1)

FROM   moiss_doc_library_v

where SYSDATE between  EFFECTIVE_START_DATE   AND   NVL(EFFECTIVE_END_DATE, trunc(sysdate)+1)

ORDER BY Document_number, VERSION_NUMBER

 

The Query given below is the place where I use the Bind variable for the function as input.

CREATE OR REPLACE FUNCTION CHECK_SWITCHER(DOC_ID_F IN NUMBER, USER_ID_F IN

NUMBER)

RETURN VARCHAR2

IS

RETURN_VAR VARCHAR2(60):=”;

CURSOR C1 IS SELECT DISTINCT DOC_ID, USER_ID FROM MOISS_DOC_LIBRARY_USER;

BEGIN

FOR I IN C1

LOOP

IF (I.DOC_ID = DOC_ID_F AND I.USER_ID = USER_ID_F)

THEN

RETURN_VAR := ‘CheckDisable’;

ELSE

RETURN_VAR := ‘CheckEnable’;

END IF;

END LOOP;

RETURN RETURN_VAR;

END;

 

My New Modified Query which solved the problem. This was said by my senior Raghunathan. Here there is no bind variable in the Query and fucntionality is done by  FND_GLOBAL.USER_ID.

PG modified Query

SELECT  DOCUMENT_NUMBER,

DOCUMENT_NAME,

DOCUMENT_FILE,

VERSION_NUMBER,

OWNING_DEPT_DESC,

CATEGORY_DESC,

CATEGORY,

OWNING_DEPT,

EFFECTIVE_START_DATE,

EFFECTIVE_END_DATE,

CHECK_SWITCHER (DOC_ID, FND_GLOBAL.USER_ID) CHECK_SWITCHER,

DOC_ID

FROM   MOISS_DOC_LIBRARY_V

WHERE SYSDATE BETWEEN  EFFECTIVE_START_DATE   AND    NVL(EFFECTIVE_END_DATE,

TRUNC(SYSDATE)+1)

 

About pacesettergraam

A good and realistic person

Posted on December 18, 2013, in OAF, Oracle and tagged , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: