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.¤cy 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
,¤cy
,&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
-----------------------------------------------------------------------------