EBS 个性化：Custom.pll修改标准Form的LOV

例：修改采购订单界面的供应商LOV。

--供应商LOV对应的记录组原始的SQL: 
select distinct
       pov.vendor_name, 
       pov.segment1, 
       decode(pov.hold_flag,'Y','*',null), 
       pov.hold_flag, 
       pov.vendor_id, 
       pov.num_1099, 
       pov.vat_registration_num 
from po_vendors pov,
     po_supplier_sites_val_v pssv
WHERE pov.enabled_flag = 'Y' 
  and sysdate between nvl(pov.start_date_active, sysdate-1) and nvl(pov.end_date_active, sysdate+1) 
  and pov.vendor_id = pssv.vendor_id
  and nvl(pssv.rfq_only_site_flag, 'N') ='N' 
  and (:po_headers.approved_date is null or 
      (:po_headers.approved_date is not null and 
      (pssv.invoice_currency_code = :po_headers.currency_code or 
            pssv.invoice_currency_code is null))) 
order by upper(pov.vendor_name)

CUSTOM.pll客制如下：

package body custom IS

  PROCEDURE set_po_vendor_lov is
    l_query_string           varchar2(2000);
    l_supplier_group_id      recordgroup;
  begin 
    if name_in('system.cursor_item') = 'PO_HEADERS.VENDOR_NAME' then
        --原始SQL中'替换为' || '''' || '拼接字符串
        --:po_headers.approved_date、:po_headers.currency_code放到字符串中不能成功创建Record GROUP，
        --也没有找到如何传参，所有删除了相关SQL条件语句
        l_query_string := 'select distinct
                           pov.vendor_name, 
                           pov.segment1, 
                           decode(pov.hold_flag,' || '''' || 'Y' || '''' || ',' || '''' || '*' || '''' || ',null), 
                           pov.hold_flag, 
                           pov.vendor_id, 
                           pov.num_1099, 
                           pov.vat_registration_num 
                    from po_vendors pov,
                         po_supplier_sites_val_v pssv
                    WHERE pov.enabled_flag = ' || '''' || 'Y' || '''' || ' 
                      and sysdate between nvl(pov.start_date_active, sysdate-1) and nvl(pov.end_date_active, sysdate+1) 
                      and pov.vendor_id = pssv.vendor_id
                      and nvl(pssv.rfq_only_site_flag, ' || '''' || 'N' || '''' || ') =' || '''' || 'N' || '''' || '  
                      and pov.vendor_name like ' || '''' || '東莞%' || '''' || '
                    order by upper(pov.vendor_name)' ;
          fnd_message.debug('l_query_string: ' || l_query_string);    --test only      
        
         --record group创建一次后，第二次如果再次创建就会报错,所以先判断record group是否已存在，有则先删除    
        if not id_null(find_group('CUX_SUPPLIER_NAME')) then 
            delete_group('CUX_SUPPLIER_NAME'); 
        end if; 
        
        l_supplier_group_id := create_group_from_query('CUX_SUPPLIER_NAME', l_query_string); 
        set_lov_property('SUPPLIER_NAME', GROUP_NAME, 'CUX_SUPPLIER_NAME');
    end if; 
  end set_po_vendor_lov;


  procedure event(event_name varchar2) IS
    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');
    --item_name  varchar2(30) := name_in('system.cursor_item');
  begin 
    if event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
        --判断当前Item是否等于VENDOR_NAME，某一些 Form会出现no data found的错误(例如应用开发员的功能form)，
        --这是因为该Form在打开的时候system.cursor_item为空，所以要把item的判断写在set_po_vendor_lov
        if form_name = 'POXPOEPO' and  block_name = 'PO_HEADERS' /* and item_name = 'PO_HEADERS.VENDOR_NAME'*/ then
            set_po_vendor_lov;
        end if;
    end if;
  end event; 
  
end custom;

编译CUSTOM.pll并测试客制效果: