<Issue Description>

設定Record group 並指定LOV，都正常，Compile 也ok

但Open form 並下拉選單選值會發生下列錯誤

ORA-00904: "DOCUMENT_NO": invalid identifier

--------------------------------------------------------------
FRM-40502: ORACLE error: unable to read list of values.

 

Record group sql 如下

SELECT distinct wnd.name as document_no,
       wnd.delivery_id as document_id,
       wdd.org_id as org_id
  FROM wsh_new_deliveries wnd,
  wsh_delivery_assignments wda,
  wsh_delivery_details wdd
WHERE wnd.organization_id = 374
   and wda.delivery_id= wnd.delivery_id
   and wdd.delivery_detail_id= wda.delivery_detail_id
   and wdd.source_code ='OE'
   and wda.delivery_detail_id is not null
   AND wnd.initial_pickup_date >= add_months(:DOCUMENTS_HEADER.ESTIMATE_CARRY_DATE,-6)

找到這篇文章才知道，原來這一個bug.

It looks like this is caused by bug 725059:
"FILTER BEFORE DISPLAY" DOESN'T WORK IF LOV HAS COLUMN ALIASES (TRIAGE1098)

Oracle的說明是…

AVAILABLE WORKAROUNDS:
Don't use column aliases in the LOV select statement.
However if a form is generated via Designer/2000, all lookup columns
automatically use column aliases in the LOV select statement.
This would mean that Des2k users have to change all their forms.

 

 

將Record Group SQL 修改下面這樣後就正常了

select document_no,document_id,org_id from (SELECT distinct wnd.name as document_no,
       wnd.delivery_id as document_id,
       wdd.org_id as org_id
  FROM wsh_new_deliveries wnd,
  wsh_delivery_assignments wda,
  wsh_delivery_details wdd
WHERE wnd.organization_id = 374
   and wda.delivery_id= wnd.delivery_id
   and wdd.delivery_detail_id= wda.delivery_detail_id
   and wdd.source_code ='OE'
   and wda.delivery_detail_id is not null
   AND wnd.initial_pickup_date >= add_months(:DOCUMENTS_HEADER.ESTIMATE_CARRY_DATE,-6))

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 Somebaby 的頭像
    Somebaby

    Oracle eBS經驗分享

    Somebaby 發表在 痞客邦 留言(0) 人氣()