APEX

We just upgraded one of our test instances from apex 3.1.2.00.02 to 4.1.0.00.32 and are testing all our applications to ensure the continue to work. One interactive report, so far, is getting the error "ORA-20001: get_dbms_sql_cursor error ORA-01747: invalid user.table.column, table.column, or column specification".

I pulled the sql and ran it in sql workshop and it runs without an issue. This report runs without issue in the 3.1.2 instance. I'm at a loss as to why we are getting this issue with the upgrade??

SELECT cv.lease_change_id || '-' || cv.lease_id "UID",
am.uid2,
lv.lease_number "Lease Num",
--Begin COA changes
initcap(substr(lv.Lease_name,6,40)) "Name",
initcap(substr(lv.Lease_name, INSTR(lv.lease_name, '.', 1) + 1, 40)) "Name",
--End COA changes
cv.last_update_date "Updated on",
to_char(cv.last_update_date, 'yyyy') "Year",
decode(cv.lease_status,'TER','Terminated','ACT','Active','MTM','Mon to Mon','Holdover') "Current Status",
decode(cv.status, 'F', 'Final', 'Draft') "Condition",
--Begin COA changes
decode(substr(lv.lease_number,6,1),'1','Rent','5','Sub-let','9','Owned','MD Fees') "Category",initcap(cv.ATTRIBUTE1) "Amendment Code",
decode(substr(lv.lease_number, INSTR(lv.lease_number, '.', 1) + 1, 1),
'1','Rent','5','Sub-let','9','Owned','MD Fees') "Category",
initcap(cv.ATTRIBUTE1) "Amendment Code",
cv.lease_change_name "Amendment Description",
usr.analyst "Updated by",
(case
when am.from_comm = am.to_comm then null
else am.from_comm
end) "From Comm",
(case
when am.from_comm = am.to_comm then null
else am.to_comm
end) "To Comm",
(case
when am.from_term = am.to_term then null
else am.from_term
end) "From Term",
(case
when am.from_term = am.to_term then null
else am.to_term
end) "To Term",
am.from_extend "From Extend",
am.to_extend "To Extend",
(case
when am.from_Status = am.to_Status then null
else decode(am.FROM_STATUS,'TER','Terminated','ACT','Active','MTM','Mon to Mon','HLD','Holdover','')
end) "From Status",
(case
when am.from_status = am.to_status then null
else decode(AM.to_STATUS,'TER','Terminated','ACT','Active','MTM','Mon to Mon','HLD','Holdover','')
end) "To Status"
FROM APPS.xxc_PN_LEASES_V LV,
APPS.xxc_PN_LEASE_CHANGES_V cv,
(select lease_id l_id,
initcap(replace(DECODE(INSTR(usr.email_address, '@'),0,usr.email_address,
SUBSTR(usr.email_address,1,INSTR(usr.email_address, '@') - 1)),'.',' ')) analyst
from apps.pn_lease_details_all lda, apps.fnd_user usr
where lda.responsible_user = usr.user_ID) usr,
(select (amd.lease_change_id || '-' || lal.lease_id) uid2,
lal.lease_num,
--Begin COA changes
--substr(lal.NAME,6,40) Short_Name,
substr(lal.NAME, INSTR(lal.NAME, '.', 1) + 1, 40) Short_Name,
lcv.lease_change_name Amend_name,
lcv.lease_change_number Chg_Num,
to_char(lcv.last_update_date, 'MM/DD/YYYY') Chg_Date,
to_char(AMD.FRM_EXEC, 'MM/DD/YYYY') From_Exec,
to_char(AMD.AFT_EXEC, 'MM/DD/YYYY') To_Exec,
to_char(AMD.FRM_COMM, 'MM/DD/YYYY') From_Comm,
to_char(AMD.AFT_COMM, 'MM/DD/YYYY') To_Comm,
to_char(AMD.FRM_TERM, 'MM/DD/YYYY') From_Term,
to_char(AMD.AFT_TERM, 'MM/DD/YYYY') To_Term,
to_char(AMD.FRM_EXT, 'MM/DD/YYYY') From_Extend,
to_char(AMD.AFT_EXT, 'MM/DD/YYYY') To_Extend,
frm_status from_status,
aft_status to_status
From apps.pn_leases_all lal,
(select trn.lease_transaction_id,
trn.lease_id,
trn.lease_change_id,
trn.created_by_name,
trn.date_effective eff_date,
frm.lease_change_id frm_Lc_id,
aft.new_lease_change_id aft_nlc_id,
frm.lease_execution_date frm_exec,
aft.lease_execution_date aft_exec,
frm.lease_commencement_date frm_comm,
aft.lease_commencement_date aft_comm,
frm.lease_termination_date frm_term,
aft.lease_termination_date aft_term,
frm.lease_extension_end_date frm_ext,
aft.lease_extension_end_date aft_ext,
frm.lease_term frm_term_days,
aft.lease_term aft_term_days,
frm.LEASE_STATUS frm_status,
aft.lease_status aft_status
from apps.pn_lease_details_history_v frm,
apps.pn_lease_details_history_v aft,
apps.xxc_pn_lease_transactions_v trn
where aft.lease_change_id = frm.new_lease_change_id
and aft.lease_change_id = trn.lease_change_id
and trn.transaction_type_code = 'AMEND') amd,
apps.xxc_pn_lease_changes_v lcv
where lal.lease_id = amd.lease_id
and amd.lease_change_id = lcv.lease_change_id) am
WHERE LV.lease_id = CV.lease_id
and lv.lease_id = usr.l_id
and cv.lease_change_id || '-' || cv.lease_id = am.uid2(+)
and cv.lease_number not like '%.7%'
and cv.lease_change_name <> 'EDIT'
and (cv.lease_status <> 'TER' or
lv.lease_termination_date > to_date('31-DEC-2009', 'dd-mon-yyyy'))
and cv.last_update_date > to_date('31-DEC-2009', 'dd-mon-yyyy')
order by cv.lease_number,cv.last_update_date desc