Oracle标准供应商地点层次所有LOV与PickList的值集取法

呕心沥血之作，找了半个下午，转载请注明~~3q~~

--收货地点
SELECT loc.location_id, loc.location_code, loc.description
  FROM hr_locations_all loc, hr_locations_all_tl lot
 where loc.ship_to_site_flag = 'Y'
   and sysdate < nvl(trunc(loc.inactive_date), sysdate + 1)
   and (nvl(loc.business_group_id,
            nvl(hr_general.get_business_group_id, -99)) =
       nvl(hr_general.get_business_group_id, -99))
   and loc.location_id = lot.location_id
   and lot.language = userenv('LANG')

 order by upper(lot.location_code);

--收单地点
select location_id, location_code
  from hr_locations
 where bill_to_site_flag = 'Y';

--发运方式
select fr.freight_code,
       fr.organization_id,
       fr.freight_code_tl,
       fr.description,
       fr.organization_code,
       fspa.org_id,
       fr.language
  from org_freight_vl fr, financials_system_params_all fspa
 where ORGANIZATION_ID = fspa.inventory_organization_id
   and (disable_date is null or disable_date > sysdate);

--付款方式
--有寄售
select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'ERS PAY_ON_CODE_CONSIGNED'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);
--无寄售
select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'ERS PAY_ON_CODE'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--替代支付地点
select vendor_site_id, vendor_site_code, org_id, vendor_id
  from po_vendor_SITES_ALL
 where PAY_SITE_FLAG = 'Y'
   and vendor_id = 11001; --限制为当前供应商地点;

--发票汇总层
select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'ERS INVOICE_SUMMARY'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--FOB
select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'FOB'
   and enabled_flag = 'Y'
   and view_application_id = 201
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--运输条款
select lookup_code, meaning, description, view_application_id, LOOKUP_TYPE
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'FREIGHT TERMS'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--已安排运输

select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'SHIPPING CONTROL'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--原产地
select territory_code, territory_short_name, description
  from fnd_territories_vl;

--发票允差
select * from AP_TOLERANCE_TEMPLATES where tolerance_type = 'GOODS';

--发票匹配选项
select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'POS_INVOICE_MATCH_OPTION'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--发票币种,付款币种
Select currency_code, description, name
  from fnd_currencies_vl
 where enabled_flag = 'Y'
   and currency_flag = 'Y'
   and trunc(nvl(start_date_active, sysdate)) <= trunc(sysdate)
   and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate)
 order by currency_code;

--服务允差
select * from AP_TOLERANCE_TEMPLATES where tolerance_type = 'SERVICES';

--支付组
select lookup_code, meaning, description
  from fnd_lookup_values_vl
 where lookup_type = 'PAY GROUP'
   and enabled_flag = 'Y'
   and (end_date_active is null or end_date_active > sysdate);
--从付款中扣除银行手续费
select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'BANK CHARGE BEARER'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--条件
select term_id, name, type, rank, description
  from ap_terms_vl
 where enabled_flag = 'Y'
   and (end_date_active is null or end_date_active > sysdate);
--条件日期基准
select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'TERMS DATE BASIS'
   and enabled_flag = 'Y'
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

--支付日期基准

select lookup_code, meaning, description, view_application_id
  from fnd_lookup_values_vl
 where FND_LOOKUP_VALUES_vl.LOOKUP_TYPE = 'PAY DATE BASIS'
   and enabled_flag = 'Y'
   and view_application_id = 201
   and sysdate between nvl(start_date_active, sysdate - 1) and
       nvl(end_date_active, sysdate + 1);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22414008/viewspace-659940/，如需转载，请注明出处，否则将追究法律责任。