查询BOM
1. 接口表:
CST_RES_OVERHEADS_INTERFACE CST_RESOURCE_COSTS_INTERFACE CST_PC_ITEM_COST_INTERFACE CST_PC_COST_DET_INTERFACE CST_LC_ADJ_INTERFACE_ERRORS CST_LC_ADJ_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE CST_ITEM_COSTS_INTERFACE CST_INTERFACE_ERRORS
CST_DEPT_OVERHEADS_INTERFACE CST_COMP_SNAP_INTERFACE
BOM_SUB_OP_RESOURCES_INTERFACE BOM_SUB_COMPS_INTERFACE BOM_REF_DESGS_INTERFACE BOM_OP_SEQUENCES_INTERFACE BOM_OP_ROUTINGS_INTERFACE BOM_OP_RESOURCES_INTERFACE BOM_OP_NETWORKS_INTERFACE BOM_INVENTORY_COMPS_INTERFACE BOM_INTERFACE_DELETE_GROUPS BOM_COMPONENT_OPS_INTERFACE BOM_CMP_USR_ATTR_INTERFACE BOM_BILL_OF_MTLS_INTERFACE
2. 查询表中字段描述语法:
select aa.table_name ,aa.column_name ,aa.column_type ,aa.width
,decode(aa.flexfield_name, NULL,
aa.description,
nvl(bb.form_left_prompt,
aa.description)) AS description ,aa.null_allowed_flag ,aa.default_value FROM (SELECT b.table_type ,b.table_name ,a.column_name ,a.column_sequence
,flv.meaning column_type ,a.width
,a.null_allowed_flag ,a.description ,a.flexfield_name ,a.flexfield_usage_code ,a.flexfield_application_id ,a.flex_value_set_application_id ,a.flex_value_set_id ,a.default_value FROM fnd_columns a ,fnd_tables b ,fnd_lookup_values flv
WHERE a.table_id = b.table_id AND flv.lookup_type = 'COLUMN_TYPE' AND flv.lookup_code = a.column_type and flv.language = 'US') aa ,(SELECT c.descriptive_flexfield_name ,c.application_column_name ,c.form_left_prompt
,c.descriptive_flex_context_code ,c.form_above_prompt
FROM fnd_descr_flex_col_usage_tl c ,fnd_descr_flex_contexts_tl d WHERE c.descriptive_flexfield_name = d.descriptive_flexfield_name
AND c.descriptive_flex_context_code = d.descriptive_flex_context_code
AND c.application_id = d.application_id AND c.LANGUAGE = d.LANGUAGE AND c.LANGUAGE = 'ZHS' ) bb
WHERE aa.column_name = bb.application_column_name(+) AND aa.flexfield_name = bb.descriptive_flexfield_name(+) and aa.table_name ='BOM_SUB_OP_RESOURCES_INTERFACE' --'&表名' --and aa.column_name = '&字段名称'
ORDER BY aa.column_sequence;
3. 要系统临时表中展BOM
DECLARE
v_verify_flag number; v_grp_id number; v_session_id number; v_plan_factor_flag number;
v_cst_type_id number; v_std_comp_flag number := 2; v_expl_qty number;
v_alt_desg varchar2(1000); v_comp_code varchar2(1000); v_rev_date varchar2(1000); v_err_msg varchar2(1000); v_error_code number; v_bom_qty number;
item_id NUMBER:=12380; n_org_id NUMBER:=83;
cursor c_bom is select bet.*
from bom_explosion_temp bet; begin
delete from bom_explosion_temp;
v_rev_date := to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'); select bom_explosion_temp_s.nextval into v_grp_id from dual; bompexpl.exploder_userexit(verify_flag => v_verify_flag, order_by => 1,
grp_id => v_grp_id, session_id => v_session_id, levels_to_explode => 10, bom_or_eng => 2, impl_flag => 2,
plan_factor_flag => v_plan_factor_flag, explode_option => 2, module => 2,
cst_type_id => v_cst_type_id, std_comp_flag => v_std_comp_flag, expl_qty => v_expl_qty, alt_desg => v_alt_desg, comp_code => v_comp_code, rev_date => v_rev_date, err_msg => v_err_msg, error_code => v_error_code); commit;
for c_r in c_bom loop
org_id => n_org_id, --:p_org_id,
item_id => item_id, --:item_id,
dbms_output.put_line(c_r.plan_level); end loop; exception
when others then
dbms_output.put_line(Sqlerrm);
end bom_explosion;
4. SQL语法从上到下展BOM SELECT DISTINCT
LEVEL level_id, --层次号 --BOM头信息
bih.organization_id, --组织
bih.assembly_item_id, --物料ID(制造件) msih.segment1 assm_item_code, --物料编码 msih.description assm_item_desc, --物料描述 msih.primary_uom_code assm_primary_uom_code, --物料单位 --BOM组件信息
bic.component_item_id, --组件物料ID msil.segment1 com_item_code, --组件物料编码 msil.description com_item_desc, --组件物料描述 bic.item_num, --组件物料序号 bic.operation_seq_num, --组件工序号 msil.primary_uom_code com_primary_uom_code, --组件单位 bic.component_quantity, --组件数量 bic.component_yield_factor, --组件产出率 --替代料信息
bsc.substitute_component_id, --替代物料ID (SELECT msis.segment1
FROM mtl_system_items_vl msis
WHERE msis.organization_id = bih.organization_id AND msis.inventory_item_id =
bsc.substitute_component_id)sub_item_code, --替代物料编码 (SELECT msis.description
FROM mtl_system_items_vl msis
WHERE msis.organization_id = bih.organization_id AND msis.inventory_item_id =
bsc.substitute_component_id)sbu_item_desc,--替代物料描述 bsc.substitute_item_quantity, --替代物料数量 (SELECT msis.primary_uom_code FROM mtl_system_items_vl msis
WHERE msis.organization_id = bih.organization_id
AND msis.inventory_item_id = bsc.substitute_component_id) sub_primary_uom_code, --替代物料单位
bsc.attribute1 sub_level, --替代等级
