oracle中如何获取商,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);