Home » Developer & Programmer » Forms » Help with FND_REQUEST.SUBMIT_REQUEST (Oracle 10g )
icon9.gif  Help with FND_REQUEST.SUBMIT_REQUEST [message #390966] Tue, 10 March 2009 09:18 Go to next message
Johnnyjohn
Messages: 10
Registered: February 2009
Location: Nigeria
Junior Member
Hello friends

Save me from pulling all my hair out. I have been trying figure out what is wrong with the PL/SQL stored procedure below. I supposed to call another report called PO_PURCHODR. The Procedure runs from oracle but the FND_REQUEST.SUBMIT_REQUEST does not call the report. Even the FND_FILE.put_line does not write anything to the log file cause it's empty. People pls help. I will be so greatful and you will be my heros.


CREATE OR REPLACE PROCEDURE PRINT_PO_REPORTS(errmesg varchar2, errcode NUMBER, PO_TYPE IN VARCHAR2, PO_FROM_ID IN NUMBER, PO_TO_ID IN NUMBER, P_ORG_ID IN NUMBER, P_INVOICE_TYPE IN VARCHAR2, P_INVOICE_ID IN NUMBER, P_VENDOR_ID IN NUMBER)

IS

V_AUTHORIZED_STATUS VARCHAR2(40);
V_PRINT_COUNT NUMBER;
V_PRINT_STATUS VARCHAR(30);
V_REQ_ID NUMBER;
V_RESULT VARCHAR(100);
--fHandler UTL_FILE.FILE_TYPE;
output_file utl_file.file_type;

BEGIN

SELECT AUTHORIZATION_STATUS, PRINT_COUNT INTO V_AUTHORIZED_STATUS, V_PRINT_COUNT FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = PO_FROM_ID AND Nvl(ORG_ID,0)=P_ORG_ID;



FND_FILE.put_line( FND_FILE.LOG,'finished executing first statement');

FND_FILE.put_line( FND_FILE.OUTPUT,'finished executing first statement' ) ;


V_REQ_ID := FND_REQUEST.SUBMIT_REQUEST ('PO',
'PO_PURCHODR', 'PO purchase order report',
'14-JUL-2007 00:00:00',
FALSE,PO_TYPE,PO_FROM_ID,PO_TO_ID, P_ORG_ID,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);


if (V_REQ_ID = 0) then
fnd_file.put_line( fnd_file.output, v_result );
FND_FILE.put_line( FND_FILE.LOG,'req ID is 0');
FND_FILE.put_line( FND_FILE.OUTPUT,'# req ID is 0' ) ;

end if;

END PRINT_PO_REPORTS;



Re: Help with FND_REQUEST.SUBMIT_REQUEST [message #390967 is a reply to message #390966] Tue, 10 March 2009 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

CREATE OR REPLACE PROCEDURE Print_po_reports 
     (errmesg         VARCHAR2, 
      errcode         NUMBER, 
      po_type         IN VARCHAR2, 
      po_from_id      IN NUMBER, 
      po_to_id        IN NUMBER, 
      p_org_id        IN NUMBER, 
      p_invoice_type  IN VARCHAR2, 
      p_invoice_id    IN NUMBER, 
      p_vendor_id     IN NUMBER) 
IS 
  v_authorized_status  VARCHAR2(40); 
  v_print_count        NUMBER; 
  v_print_status       VARCHAR(30); 
  v_req_id             NUMBER; 
  v_result             VARCHAR(100); 
  --fHandler UTL_FILE.FILE_TYPE; 
  output_file  utl_file.file_type; 
BEGIN 
  SELECT authorization_status, 
         print_count 
  INTO   v_authorized_status,v_print_count 
  FROM   po_headers_all 
  WHERE  po_header_id = po_from_id 
         AND Nvl(org_id,0) = p_org_id; 
   
  fnd_file.Put_line(fnd_file.LOG,'finished executing first statement'); 
   
  fnd_file.Put_line(fnd_file.output,'finished executing first statement'); 
   
  v_req_id := fnd_request.Submit_request('PO','PO_PURCHODR','PO purchase order report', 
                                         '14-JUL-2007 00:00:00',false,po_type,po_from_id, 
                                         po_to_id,p_org_id,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 
                                         NULL); 
   
  IF (v_req_id = 0) THEN 
    fnd_file.Put_line(fnd_file.output,v_result); 
     
    fnd_file.Put_line(fnd_file.LOG,'req ID is 0'); 
     
    fnd_file.Put_line(fnd_file.output,'# req ID is 0'); 
  END IF; 
END print_po_reports;


Which code is easier to read, yours or mine?

[Updated on: Tue, 10 March 2009 09:23]

Report message to a moderator

Re: Help with FND_REQUEST.SUBMIT_REQUEST [message #390975 is a reply to message #390967] Tue, 10 March 2009 09:42 Go to previous messageGo to next message
Johnnyjohn
Messages: 10
Registered: February 2009
Location: Nigeria
Junior Member
Thanks for the correction. Do you have an idea what is wrong?
Re: Help with FND_REQUEST.SUBMIT_REQUEST [message #390987 is a reply to message #390967] Tue, 10 March 2009 10:43 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe SELECT returns nothing so it ends up with the NO-DATA-FOUND error (and, thus, never reaches 'fnd_file.put_line')? ./fa/3314/0/

Why wouldn't you replace your procedures ('fnd_file.put_line', 'fnd_request.submit_request') with Forms built-ins, such as MESSAGE or WEB.SHOW_DOCUMENT? We have no idea what your procedures are doing. Is there, by any chance, WHEN OTHERS exception handler which does NULL?
Re: Help with FND_REQUEST.SUBMIT_REQUEST [message #391309 is a reply to message #390987] Wed, 11 March 2009 13:21 Go to previous message
Johnnyjohn
Messages: 10
Registered: February 2009
Location: Nigeria
Junior Member
Hey little foot you are the man. you are right the select statement didnt return anything. Thanks a million. I just wish I realized that much earlier
Previous Topic: Restrict SQL use
Next Topic: Populate error message when inserting duplicated field in primary key?
Goto Forum:
  


Current Time: Mon Dec 05 13:13:26 CST 2016

Total time taken to generate the page: 0.08302 seconds