SQL & PL/SQL

Last night I attempted to roll a Crystal Reports .NET project to my production web server. This app has 3 new Crystal Reports. The Reports are all based upon the same Oracle Views on the same Oracle Database. Two of the reports work while the third report returns an ORA-24333 error. The exact reports and .NET code, connecting to the same Oracle database work fine in Preview mode and when published to my development IIS web server. The ONLY thing that is different is that the report is now being run on the production IIS web server. (But it is still the same Oracle Database we are connecting to in all environments.)

Here is the Command used for the Crystal Report that fails. Sorry it is so long. Basically, it is 4 Select statements joined together with Unions. Stuff in {} are parameters that are passed to it.

SELECT A.Custno as Custno, C.CUST_NAME as Cust_Name, D.CONTACT_NAME as Contact_Name, A.FISCALPRD as FiscalPrd, A.PROJECTNAME as ProjectName, A.INVOICENO as InvoiceNO, A.INVDATE, A.PAYDATE, A.ORDERNO as OrderNo, A.TOTALAMT as TotalAmt, A.ELIGIBLEAMT as EligibleAmt, A.SALESPOINTERD as SalesPointErd, 0 as FORFEIT, 0 as BONUSPOINTERD, 0 as PAYPTSEARNED, P.POINTPERCD as POINTPERCD, case when A.PAYDATE is null and A.INVDATE>SYSDATE-30 then round(P.POINTPERCD*A.ELIGIBLEAMT/100,0) else 0 end as PAYPTSELIG, case when A.PAYDATE is null and A.INVDATE>SYSDATE-30 then TO_CHAR(A.INVDATE+30,'MM/DD/YYYY') else '' end as PAYBYforPAYPTS
FROM VW_TRIPPOINTS A
left join VW_POINTS P on A.COMPANY=P.COMPANY and substr(A.FISCALPRD,1,4)=to_char(P.FISCALYEAR) and P.CODE='P'
join VW_CUSTOMERS C on (C.COMPANY=A.COMPANY or A.custno='313243') and C.CUST_NUMB=A.custno
join VW_SALESMEN D on C.DIST_MGR=D.CONTACT_NUMBER
where A.POINTCODE='S' and A.COMPANY='30' and A.ELIGIBLEAMT<>0 and A.custno='{?@Cust_ID}' and A.FISCALPRD>={?@BegPeriod} and A.FISCALPRD<={?@EndPeriod}
Union --Bonus Points
SELECT A.Custno as Custno, C.CUST_NAME as Cust_Name, D.CONTACT_NAME as Contact_Name, A.FISCALPRD as FiscalPrd, A.Notes as ProjectName, Cast((Row_Number() over (Order By CustNo, OrderNo, InvoiceNo, IDKey)) as varchar(10)) as InvoiceNO, A.INVDATE, Null as PAYDATE, Cast((Row_Number() over (Order By IDKey)) as varchar(10)) as ORDERNO, Coalesce(A.TOTALAMT,0) as TotalAmt, Coalesce(A.ELIGIBLEAMT,0) as EligibleAmt, Coalesce(A.SALESPOINTERD,0) as SalesPointErd, 0 as FORFEIT, A.BONUSPOINTERD as BonusPointErd, 0 as PAYPTSEARNED, 0 as POINTPERCD, 0 as PAYPTSELIG, '' as PAYBYforPAYPTS
FROM VW_TRIPPOINTS A
join VW_CUSTOMERS C on (C.COMPANY=A.COMPANY or A.custno='313243') and C.CUST_NUMB=A.custno
join VW_SALESMEN D on C.DIST_MGR=D.CONTACT_NUMBER
where A.POINTCODE='B' and A.COMPANY='30' and A.custno='{?@Cust_ID}' and A.FISCALPRD>={?@BegPeriod} and A.FISCALPRD<={?@EndPeriod}
Union --Pay Points
SELECT A.Custno as Custno, C.CUST_NAME as Cust_Name, D.CONTACT_NAME as Contact_Name, A.FISCALPRD as FiscalPrd, A.PROJECTNAME as ProjectName, A.INVOICENO as InvoiceNO, A.INVDATE, A.PAYDATE, A.ORDERNO as OrderNo, 0 as TotalAmt, 0 as EligibleAmt, 0 as SALESPOINTERD, 0 as FORFEIT, 0 as BONUSPOINTERD, coalesce(A.PAYPOINTERD,0) as PAYPTSEARNED, 0 as POINTPERCD, 0 as PAYPTSELIG, '' as PAYBYforPAYPTS
FROM VW_TRIPPOINTS A
join VW_CUSTOMERS C on (C.COMPANY=A.COMPANY or A.custno='313243') and C.CUST_NUMB=A.custno
join VW_SALESMEN D on C.DIST_MGR=D.CONTACT_NUMBER
where (A.POINTCODE='P' or A.POINTCODE='C') and A.COMPANY='30' and A.PAYPOINTERD<>0 and A.custno='{?@Cust_ID}' and A.FISCALPRD>={?@BegPeriod} and A.FISCALPRD<={?@EndPeriod}
Union --Sales Points Forfeited
SELECT A.Custno as Custno, C.CUST_NAME as Cust_Name, D.CONTACT_NAME as Contact_Name, A.FISCALPRD as FiscalPrd, A.PROJECTNAME as ProjectName, A.INVOICENO as InvoiceNO, A.INVDATE, A.PAYDATE, A.ORDERNO as OrderNo, 0 as TotalAmt, 0 as EligibleAmt, 0 as SalesPointErd, Coalesce(A.SalesPointErd,0) as FORFEIT, 0 as BONUSPOINTERD, 0 as PAYPTSEARNED, 0 as POINTPERCD, 0 as PAYPTSELIG, '' as PAYBYforPAYPTS
FROM VW_TRIPPOINTS A
join VW_CUSTOMERS C on (C.COMPANY=A.COMPANY or A.custno='313243') and C.CUST_NUMB=A.custno
join VW_SALESMEN D on C.DIST_MGR=D.CONTACT_NUMBER
where A.POINTCODE='R' and A.COMPANY='30' and A.SalesPointErd<>0 and A.custno='{?@Cust_ID}' and A.FISCALPRD>={?@BegPeriod} and A.FISCALPRD<={?@EndPeriod}
order by custno, OrderNo, InvoiceNo