Home » Developer & Programmer » Reports & Discoverer » report error REP-1401/Ora- 00904
| report error REP-1401/Ora- 00904 [message #90270] |
Fri, 07 January 2005 21:29 |
arupa
Messages: 3
Registered: January 2005
|
Junior Member |
|
|
Hi!
To all forum members. I'm in bit trouble with a peculier problem which i'm explaining below--
When i'm running the procedure given below - report builder is showing the error message number-- report error REP-1401/Ora- 00904.
I'm calling the procedure in the Before report trigger.It is showing me
'Before report trigger fatal pl/sql error occured'.
' Ora - 00904: Invalid identifier'.
N.B: The procedure is running successfully in Sql plus.
The related tables and the procedure is as given below----
SQL> desc invoice_hdr i
Name Null? Type
------------------------------- -------- ----
INVOICE_NO NOT NULL VARCHAR2(11)
INVOICE_DT NOT NULL DATE
ORDER_NO VARCHAR2(11)
ORDER_DT DATE
ORD_AMEND_NO NUMBER(2)
ACTUAL_INV_NO VARCHAR2(11)
ACTUAL_INV_DT DATE
BRANCH_ID VARCHAR2(10)
CUST_ID VARCHAR2(5)
CUST_TYPE VARCHAR2(1)
CONS_NAME VARCHAR2(50)
DELIVERY_ADDRESS VARCHAR2(250)
INV_PREP_PERSON VARCHAR2(5)
LOAD_PLACE VARCHAR2(60)
DEST_PLACE VARCHAR2(60)
TRANSPORT_MODE VARCHAR2(1)
TRANSPORTER_ID VARCHAR2(5)
VEHICLE_NO VARCHAR2(25)
CURRENCY_ID VARCHAR2(3)
PAY_MODE_CD VARCHAR2(2)
PRICE_BASIS VARCHAR2(1)
PACKING_CHARGE NUMBER(8,2)
FREIGHT_AMT NUMBER(8,2)
INSURANCE_AMT NUMBER(8,2)
DISCOUNT_AMT NUMBER(8,2)
OTHER_DISC_RATE NUMBER(5,2)
OTHER_DISC_AMT NUMBER(8,2)
EXCISE_DUTY_AMT NUMBER(8,2)
ST_RATE NUMBER(5,2)
ST_TAG VARCHAR2(1)
SURCHARGE_RATE NUMBER(5,2)
ADDL_TAX NUMBER(5,2)
ADDL_TAX_DESC VARCHAR2(20)
INVOICE_AMT NUMBER(15,2)
ACKN_TAG VARCHAR2(1)
INVOICE_TYPE VARCHAR2(1)
INV_DESPATCH_DT DATE
DELV_DESPATCH_DT DATE
REMARKS VARCHAR2(500)
SQL> desc invoice_dtl d
Name Null? Type
------------------------------- -------- ----
INVOICE_NO NOT NULL VARCHAR2(11)
INVOICE_DT NOT NULL DATE
ITEM_CODE NOT NULL VARCHAR2(10)
QUANTITY NUMBER(9,3)
QTY_ACC NUMBER(9,3)
REJECT_REASON VARCHAR2(250)
UOM VARCHAR2(5)
RATE NUMBER(12,2)
EXCISE_DUTY NUMBER(5,2)
DISC_PERC NUMBER(5,2)
DISCOUNT_ON VARCHAR2(1)
SERVICE_RATE NUMBER(12,2)
PROBLEM_DESC VARCHAR2(300)
PACK_ID VARCHAR2(10)
NO_OF_PACKAGE NUMBER(6)
MARKS_NO VARCHAR2(100)
SRL_NO NUMBER(2)
REMARKS VARCHAR2(500)
SQL> desc order_hdr
Name Null? Type
------------------------------- -------- ----
ORDER_NO NOT NULL VARCHAR2(11)
ORDER_DT NOT NULL DATE
AMEND_NO NOT NULL NUMBER(2)
BRANCH_ID VARCHAR2(10)
ORDER_FOR NOT NULL VARCHAR2(1)
ORD_MESSAGE_TYPE NOT NULL VARCHAR2(1)
REPEAT_ORDER VARCHAR2(1)
SERVICE_TYPE VARCHAR2(1)
CUST_ORDER_NO VARCHAR2(20)
CUST_ORDER_DT DATE
CUST_ID VARCHAR2(5)
CUST_TYPE VARCHAR2(1)
CONS_NAME VARCHAR2(50)
DELIVERY_ADDRESS VARCHAR2(250)
ORDER_RECV_PERSON VARCHAR2(5)
LOAD_PLACE VARCHAR2(60)
DEST_PLACE VARCHAR2(60)
TRANSPORT_MODE VARCHAR2(1)
DELIVERY_PERIOD VARCHAR2(100)
CURRENCY_ID VARCHAR2(3)
PAY_MODE_CD VARCHAR2(2)
PRICE_BASIS VARCHAR2(1)
PACKING_CHARGE NUMBER(8,2)
FREIGHT_AMT NUMBER(8,2)
INSURANCE_AMT NUMBER(8,2)
DISCOUNT_AMT NUMBER(8,2)
OTHER_DISC_RATE NUMBER(5,2)
OTHER_DISC_AMT NUMBER(8,2)
EXCISE_DUTY_AMT NUMBER(8,2)
ST_RATE NUMBER(5,2)
ST_TAG VARCHAR2(1)
SURCHARGE_RATE NUMBER(5,2)
ADDL_TAX NUMBER(5,2)
ADDL_TAX_DESC VARCHAR2(20)
ORDER_AMT NUMBER(15,2)
AMEND_NOTE VARCHAR2(500)
AMEND_DT DATE
CANCEL_DT DATE
CANCEL_REMARKS VARCHAR2(200)
QLTY_ASSURANCE VARCHAR2(200)
WEIGHMENT VARCHAR2(200)
PACKING_DETAILS VARCHAR2(200)
PENALTY VARCHAR2(200)
QUOT_NO VARCHAR2(11)
QUOT_AMEND_NO NUMBER(2)
QUOT_DT DATE
CLOSE_TAG VARCHAR2(1)
SHOW_TAG VARCHAR2(1)
CLOSE_REMARKS VARCHAR2(200)
INDENT_NO VARCHAR2(11)
INDENT_DT DATE
IND_AMEND_NO NUMBER(2)
REMARKS VARCHAR2(500)
SQL> desc customer c
Name Null? Type
------------------------------- -------- ----
CUST_ID NOT NULL VARCHAR2(5)
CUST_NAME VARCHAR2(50)
CUST_ISCOMPANY CHAR(1)
CUST_PARENTID VARCHAR2(5)
CUST_ADD VARCHAR2(150)
CUST_PLACE VARCHAR2(25)
CUST_POSTALCODE VARCHAR2(12)
CUST_STATE_ID VARCHAR2(3)
CUST_COUNTRY_ID VARCHAR2(3)
CUST_TEL_NO1 VARCHAR2(20)
CUST_TEL_NO2 VARCHAR2(20)
CUST_TEL_NO3 VARCHAR2(20)
CUST_FAX_NO1 VARCHAR2(20)
CUST_FAX_NO2 VARCHAR2(20)
CUST_EMAIL VARCHAR2(30)
CUST_ISO_CODE VARCHAR2(20)
CUST_STATUS CHAR(1)
CUST_CATEGORY CHAR(1)
CUST_REGULAR CHAR(1)
CUST_LOCAL CHAR(1)
CUST_HOME_STATE CHAR(1)
CUST_RANGE VARCHAR2(20)
CUST_DIVISION VARCHAR2(20)
CUST_EC_CODE VARCHAR2(25)
CUST_STAX_NO1 VARCHAR2(30)
CUST_STAX1_VALIDITY DATE
CUST_STAX_NO2 VARCHAR2(30)
CUST_STAX2_VALIDITY DATE
CUST_CON_NAME VARCHAR2(50)
CUST_CON_DESG VARCHAR2(15)
CUST_CON_DEPT VARCHAR2(15)
CUST_OWN_CODE VARCHAR2(20)
CUST_OLD_CODE VARCHAR2(20)
CUST_SUPP_CODE VARCHAR2(5)
CUST_LANGUAGE VARCHAR2(3)
CUST_CURRENCY VARCHAR2(3)
CUST_CREDIT_RATING NUMBER(2)
CUST_CREDIT_LIMIT NUMBER(17,2)
CUST_INVOICE_TO_CUSTID VARCHAR2(5)
CUST_STATEMENT_TO_CUSTID VARCHAR2(5)
CUST_BANK VARCHAR2(120)
CUST_HUNDI_DAYS NUMBER(3)
CUST_DEL_ADDR_SAME_AS_MAIN CHAR(1)
CUST_MAIN_DEL_ADDR VARCHAR2(150)
CUST_MAIN_DEL_PLACE VARCHAR2(25)
CUST_MAIN_DEL_POSTALCODE VARCHAR2(12)
CUST_MAIN_DEL_STATE_ID VARCHAR2(3)
CUST_MAIN_DEL_COUNTRY_ID VARCHAR2(3)
CUST_MAIN_DEL_TEL_NO1 VARCHAR2(20)
CUST_MAIN_DEL_TEL_NO2 VARCHAR2(20)
CUST_MAIN_DEL_TEL_NO3 VARCHAR2(20)
CUST_MAIN_DEL_FAX_NO1 VARCHAR2(20)
CUST_MAIN_DEL_FAX_NO2 VARCHAR2(20)
CUST_MAIN_DEL_EMAIL VARCHAR2(30)
CUST_EDI_FLAG CHAR(1)
CUST_EDI_LOCATION VARCHAR2(15)
CUST_EDI_NO VARCHAR2(20)
CUST_CREATED_ON DATE
CUST_TYPE VARCHAR2(1)
VALUED_DELVNOTE_TAG VARCHAR2(1)
SQL> desc itemmaster t
Name Null? Type
------------------------------- -------- ----
MST_ITEM_NO NOT NULL VARCHAR2(10)
MST_ITEM_DESC VARCHAR2(40)
MST_CAT_CODE NOT NULL VARCHAR2(2)
MST_BRAND_NO VARCHAR2(2)
MST_ITEM_CLASS VARCHAR2(1)
MST_ITEM_TYPE VARCHAR2(3)
MST_STOCK_UOM NOT NULL VARCHAR2(5)
MST_PUR_UOM NOT NULL VARCHAR2(5)
MST_ISS_UOM NOT NULL VARCHAR2(5)
MST_ALTERNATE_ITEM_NO VARCHAR2(8)
MST_PUR_STK_CONV_FACT NUMBER(10,5)
MST_STK_ISS_CONV_FACT NUMBER(10,5)
MST_PUR_AVG_RATE NUMBER(11,4)
MST_LAST_PUR_RATE NUMBER(11,4)
MST_LOC CHAR(1)
MST_INSP_STAT CHAR(1)
MST_PUR_STAT CHAR(1)
MST_MKT_PRICE NUMBER(11,4)
MST_PHY_ATTRIB VARCHAR2(25)
MST_PART_NO VARCHAR2(25)
MST_DESIGN_NO VARCHAR2(25)
MST_DRAW_NO VARCHAR2(25)
MST_MOD_NO VARCHAR2(25)
MST_FILE_NO VARCHAR2(5)
MST_IS VARCHAR2(15)
MST_SS VARCHAR2(10)
MST_TS VARCHAR2(10)
MST_OTHERS VARCHAR2(10)
MST_MNF_LT NUMBER(3)
MST_SUPL_LT NUMBER(3)
MST_PRE_LT NUMBER(3)
MST_PRO_LT NUMBER(3)
MST_POST_LT NUMBER(3)
MST_SHELF_LIFE NUMBER(3)
MST_WT_FACT NUMBER(10,8)
MST_UOM_WT_FACT VARCHAR2(5)
MST_TARIF_HEAD VARCHAR2(12)
MST_REGD_ITEM CHAR(1)
MST_HMI CHAR(1)
MST_HMICST NUMBER(13,3)
MST_PUR_REG_ITEM CHAR(1)
MST_LONG_DESC VARCHAR2(500)
MST_OLD_ICODE VARCHAR2(20)
MST_OLD_GRP_CODE VARCHAR2(2)
MST_OLD_GRP_DESC VARCHAR2(40)
MST_OLD_SGRP_CODE VARCHAR2(2)
MST_OLD_SGRP_DESC VARCHAR2(40)
MST_BATCHTRACABLE_TAG CHAR(1)
MST_MODVAT_TAG CHAR(1)
MST_CREATED_ON DATE
MST_PROCESS_TAG CHAR(1)
BOM_MADE VARCHAR2(1)
BOP_MADE VARCHAR2(1)
PROD_TR_MST VARCHAR2(1)
MST_REF_DRG_NO VARCHAR2(10)
STANDARD_PRICE NUMBER(12,2)
SQL> desc uom
Name Null? Type
------------------------------- -------- ----
UOM_CODE NOT NULL VARCHAR2(5)
UOM_DESCRIPTION VARCHAR2(30)
UOM_PRECISION NUMBER(4)
============================================--------------------------------------===================================
create global temporary table cust_prod
(
invoice_no varchar2(11),
invoice_dt date,
cust_id varchar2(10),
cust_name varchar2(100),
item_code varchar2(10),
item_desc varchar2(100),
uom varchar2(5),
quantity number(9,3),
rate number(10,2),
base_amt number(10,2),
exc_duty number(10,2),
addl_tax number(10,2),
frght_amt number(10,2),
vat number(10,2),
pkg number(10,2),
tot_amt number(15,2)
);
===================================--------------------------------------------------------=============================
create or replace procedure proc_cust_prod as
cursor c1 is
select a.invoice_no,item_code,quantity,rate,(quantity * rate) base_amt,
excise_duty,st_rate,packing_charge,addl_tax,uom,
freight_amt,a.cust_id,cust_name,mst_item_desc,a.invoice_dt
from invoice_hdr a, invoice_dtl b, customer c, itemmaster d
where a.invoice_no = b.invoice_no
and a.cust_id = c.cust_id
and item_code = mst_item_no
and a.invoice_dt between '13-FEB-2001' AND '03-JAN-2005'
order by base_amt asc;
cursor c2 is
select invoice_no,sum(quantity * rate) tot_base
from invoice_dtl
where invoice_dt between '13-FEB-2001' AND '03-JAN-2005'
group by invoice_no
order by tot_base;
r1 c1%rowtype;
r2 c2%rowtype;
pct number(6,2);
adltax number(10,2);
vt number(10,2);
ft number(10,2);
pk number(10,2);
dn number(10);
c number(10);
tot_adltax number(10,2);
tot_vt number(10,2);
tot_ft number(10,2);
tot_pk number(10,2);
total number(15,2);
begin
open c2;
dbms_output.put_line('222');
fetch c2 into r2;
dbms_output.put_line('Records-'||c2%rowcount);
--commit;
close c2;
commit;
for r2 in c2
loop
select count(invoice_no) into dn from invoice_dtl
where invoice_no = r2.invoice_no;
c:=1;
tot_adltax := 0;
tot_vt := 0;
tot_ft := 0;
tot_pk := 0;
for r1 in c1
loop
if r2.invoice_no = r1.invoice_no then
IF c <dn then
pct := r1.base_amt/r2.tot_base;
adltax := (pct/100) * r1.addl_tax ;
tot_adltax := tot_adltax + adltax;
vt := r1.base_amt * (r1.st_rate/100);
tot_vt := tot_vt + vt;
ft := (pct/100) * r1.freight_amt;
tot_ft := tot_ft + ft;
pk := (pct/100) * r1.packing_charge;
tot_pk := tot_pk + pk;
else
adltax := r1.addl_tax - tot_adltax;
vt := r1.base_amt * (r1.st_rate/100);
ft := r1.freight_amt - tot_ft;
pk := r1.packing_charge - tot_pk;
end if;
total := nvl(r1.base_amt,0) + nvl(r1.excise_duty,0) + nvl(adltax,0) + nvl(ft,0) + nvl(vt,0) + nvl(pk,0) ;
insert into cust_prod (INVOICE_NO,INVOICE_DT,CUST_ID,CUST_NAME,ITEM_CODE,ITEM_DESC,
UOM,QUANTITY,RATE,BASE_AMT,EXC_DUTY,ADDL_TAX,FRGHT_AMT,VAT,PKG,tot_amt)
values( r1.invoice_no, r1.invoice_dt,r1.cust_id,r1.cust_name,r1.item_code,r1.mst_item_desc,
r1.uom,r1.quantity,r1.rate,r1.base_amt,r1.excise_duty,adltax,ft,vt,pk,total);
end if;
c := c+1;
end loop;
end loop;
end;
/
exec proc_cust_prod;
select * from cust_prod;
|
|
|
|
Goto Forum:
Current Time: Sun May 12 12:26:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
