Skip to main content
Question

The Order report under Operation Reports does not pass parameters such as order number to the RDF view.

  • December 11, 2024
  • 0 replies
  • 43 views

Forum|alt.badge.img+1

Dear All/Teams,

I have set the vendorNO parameter inside my RDF, but when I enter this venderno value in the order report (this value is checked in the cursor), and go to the view based on this result key, I find the result set is empty.

But when I take these parameters to PLSQL to run, there is data inserted into the view.

So I think there is something not configured in the parameter passing relationship from front end to database.



define MODULE             = INVOIC
define LU                 = ASupplierPayDueDetail
define PKG                = A_SUPPLIER_PAY_DUE_DETAIL_RPI

define TABLE              = INFO_SERVICES_RPT
define VIEW               = A_SUPPLIER_PAY_DUE_DETAIL_REP
define METHOD             = Report_Overview

define ledger_item_id     	= S1
define code_a        		= S2
define ledger_date          = D1
define due_date            	= D2
define currency             = S3
define open_amount      	= N1
define identity           	= S4
define name          		= S5
define pay_term_id      	= N2
define company          	= N3
-----------------------------------------------------------------------------
-- PACKAGE SPECIFICATION:
-----------------------------------------------------------------------------

PROMPT Creating &PKG specification

CREATE OR REPLACE PACKAGE &PKG AS

MODULE_  CONSTANT VARCHAR2(6) := '&MODULE';
LU_NAME_ CONSTANT VARCHAR2(25) := '&LU';

PROCEDURE &METHOD( report_attr_    IN VARCHAR2, 
                   parameter_attr_ IN VARCHAR2) ; 


PROCEDURE test (
   reportKey   IN VARCHAR2,
   vender_no    IN VARCHAR2 );				 

END &PKG;
/
PROMPT Creating report view &VIEW

CREATE OR REPLACE VIEW &VIEW AS
SELECT 
H.RESULT_KEY       		RESULT_KEY
,H.ROW_NO         		ROW_NO
,H.&ledger_item_id     	ledger_item_id
,H.&code_a         		code_a      
,H.&ledger_date        	ledger_date     
,H.&due_date           	due_date        
,H.&currency           	currency        
,H.&open_amount       	open_amount    
,H.&identity           	identity        
,H.&name         		name        
,H.&pay_term_id       	pay_term_id    
,H.&company           	company  
FROM  &TABLE H
with read only
; 

COMMENT ON TABLE &VIEW IS
   'MODULE=&MODULE^LU=&LU^PROMPT=&LU^
   ^METHOD=&PKG..&METHOD^MODE=PLSQL1.2^TABLE=&TABLE^TITLE=Supplier Pay Due Detail^';

COMMENT ON COLUMN &VIEW..result_key IS
   'FLAGS=M----^DATATYPE=NUMBER^';
COMMENT ON COLUMN &VIEW..row_no IS
   'FLAGS=M----^DATATYPE=NUMBER^';
COMMENT ON COLUMN &VIEW..ledger_item_id IS
   'FLAGS=A----^DATATYPE=STRING(100)^PROMPT=Ledger Item Id^TITLE=Ledger Item Id^ITEM_NAME=iLedgerItemId^';
COMMENT ON COLUMN &VIEW..code_a IS
   'FLAGS=A----^DATATYPE=STRING(100)^PROMPT=Code A^TITLE=Code A:^ITEM_NAME=iCodeA^';
COMMENT ON COLUMN &VIEW..ledger_date IS
   'FLAGS=A----^DATATYPE=Date^PROMPT=Ledger Date^TITLE=Ledger Date:^ITEM_NAME=iLedgerDate^';
COMMENT ON COLUMN &VIEW..due_date IS
   'FLAGS=A----^DATATYPE=Date^PROMPT=Due Date^TITLE=Due Date^ITEM_NAME=iDueDate^';
COMMENT ON COLUMN &VIEW..currency IS
   'FLAGS=A----^DATATYPE=STRING(25)/UPPERCASE^PROMPT=Currency^TITLE=Currency^ITEM_NAME=iCurrency^';
COMMENT ON COLUMN &VIEW..open_amount IS
   'FLAGS=A----^DATATYPE=NUMBER(10)^PROMPT=Open Amount^TITLE=Open Amount:^ITEM_NAME=iOpenAmount^';
COMMENT ON COLUMN &VIEW..identity IS
   'FLAGS=A----^DATATYPE=STRING(35)/UPPERCASE^PROMPT=Identity^TITLE=Identity^ITEM_NAME=iIdentity^Query=Vender_No';
COMMENT ON COLUMN &VIEW..name IS
   'FLAGS=A----^DATATYPE=STRING(200)^PROMPT=Name^TITLE=Name^ITEM_NAME=iName^';
COMMENT ON COLUMN &VIEW..pay_term_id IS
   'FLAGS=A----^DATATYPE=STRING(50)^PROMPT=Pay Term Id^TITLE=Pay Term Id^ITEM_NAME=iPayTermId^';

COMMENT ON COLUMN &VIEW..company IS
   'FLAGS=A----^DATATYPE=STRING(50)^PROMPT=Company^TITLE=Company^ITEM_NAME=iCompany^';

BEGIN 
    Report_Sys.Define_Report_('&VIEW', '&MODULE', '&LU', 'Supplier Pay Due Detail', '&TABLE', '&PKG..&METHOD',1);
    Report_Sys.Define_Report_Layout_( '&VIEW', 'ASupplierPayDueDetail.rpt', 'A4', 'DYNAMIC', '','CRYSTAL' ); 
    Report_SYS.Refresh_('&VIEW');
END;
/
-----------------------------------------------------------------------------
-- PACKAGE IMPLEMENTATION
-----------------------------------------------------------------------------

PROMPT Creating &PKG implementation

CREATE OR REPLACE PACKAGE BODY &PKG AS           

PROCEDURE &METHOD( report_attr_    IN VARCHAR2, 
                   parameter_attr_ IN VARCHAR2) 
IS
   result_key_            NUMBER;
   row_no_                NUMBER := 1;
   vender_no_             &VIEW..identity%TYPE;
   reference_date_		  &VIEW..due_date%TYPE;



		CURSOR get_header_rec IS
			 SELECT 

			A.ledger_item_id   
			,a.CODE_A 
			,a.identity   
			,a.ledger_date  
			,a.due_date   
			,a.currency   
			,a.open_amount   
			,a.name    
			,D.PAY_TERM_ID
			,a.company

			FROM IFSAPP.LEDGER_ITEM_SU_QRY A 

			inner join ifsapp.IDENTITY_INVOICE_INFO D
			on D.identity = to_char(a.Identity)
			and D.company=a.company

			where a.open_amount<>0 
			and a.identity = vender_no_
			--CASE WHEN vender_no_ ='*' THEN a.identity ELSE vender_no_ END
			--AND A.DUE_DATE <reference_date_ 
			--to_date(reference_date_,'YYYYMMDD')
		;


BEGIN 
   result_key_ := Client_Sys.Attr_Value_To_Number(Client_Sys.Get_Item_Value('RESULT_KEY',report_attr_));

   --order_no_       := Client_Sys.Get_Item_Value('ORDER_NO',parameter_attr_);
   vender_no_       := Client_Sys.Get_Item_Value('VENDER_NO:',parameter_attr_);
   reference_date_  := Client_Sys.Get_Item_Value('Reference_Date:',parameter_attr_);

DBMS_OUTPUT.PUT_LINE('**************:::'||vender_no_);

  FOR header_rec_ IN get_header_rec LOOP

		  INSERT INTO &TABLE (
		  result_key ,row_no ,parent_row_no
          ,&ledger_item_id   
          ,&code_a
          ,&ledger_date
          ,&due_date
          ,&currency
          ,&open_amount
          ,&identity
          ,&name
          ,&pay_term_id  
          ,&company
		  )
          VALUES ( 
		  result_key_,row_no_,0,
		  header_rec_.ledger_item_id

         ,header_rec_.code_a
         ,header_rec_.ledger_date
         ,header_rec_.due_date
         ,header_rec_.currency
         ,header_rec_.open_amount
         ,header_rec_.identity
         ,header_rec_.name
         ,header_rec_.pay_term_id
         ,header_rec_.company

                  );    
             row_no_ := row_no_ + 1; 

    END LOOP;
END &METHOD; 


PROCEDURE test (
   reportKey      IN VARCHAR2,
   vender_no        IN VARCHAR2 )
IS
   reportAttr_    VARCHAR2(200);
   parameterAttr_ VARCHAR2(200);
BEGIN
   General_SYS.Init_Method(lu_name_, '&PKG', 'test');
   Client_SYS.Add_To_Attr('RESULT_KEY', reportKey, reportAttr_);
   Client_SYS.Add_To_Attr('VENDER_NO', vender_no, parameterAttr_);
   &METHOD(reportAttr_, parameterAttr_);
END test;

END &PKG;
/
SHOW ERROR 

COMMIT;
-----------------------------------------------------------------------------
-- END
-----------------------------------------------------------------------------

 

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings