QQ:16441708
MSN:Baconxu@hotmail.com
最近发现,记下来:
declare
V_L_SEQ_ID NUMBER; -- bom list sequence id bom_lists_s.nextval
V_GROUP_ID NUMBER; -- explode group id bom_explosion_temp_s.nextval
V_LEVEL_EXPLODE NUMBER:= 9; -- levels to explode
V_bom_or_eng NUMBER:= 1; -- BOM is 1, ENG is 2
V_IMPL_FLAG NUMBER:= 1; -- 'Implemented only' or 'Implemented or not''
V_EXPLODE_OPTION VARCHAR2(10):= 2; --''All' , Current
V_err_msg varchar(20);
V_err_code varchar(20);
--2).Explore BOM
BEGIN
SELECT BOM_LISTS_S.NEXTVAL INTO V_L_SEQ_ID FROM DUAL;
INSERT INTO BOM_LISTS(SEQUENCE_ID,ASSEMBLY_ITEM_ID) VALUES(V_L_SEQ_ID,'53289');
SELECT BOM_EXPLOSION_TEMP_S.NEXTVAL INTO V_GROUP_ID FROM DUAL;
DBMS_OUTPUT.PUT_LINE('GROUP ID:' || TO_CHAR(V_GROUP_ID));
Bompexpl.explosion_report
(org_id => 81,
order_by =>1, --:P_ORDER_BY_TYPE,
list_id =>V_l_seq_id,
grp_id =>V_GROUP_ID,
session_id => -1,
levels_to_explode => V_LEVEL_EXPLODE, --:P_EXPLOSION_LEVEL,
bom_or_eng => V_bom_or_eng,
impl_flag => V_IMPL_FLAG, --:P_IMPL_FLAG,
explode_option => V_EXPLODE_OPTION, --:P_EXPLODE_OPTION_TYPE,
module => 2, --:P_MODULE,
cst_type_id => -1,
std_comp_flag => -1,
expl_qty => 1, --:P_EXPLOSION_QUANTITY,
report_option => -1,
req_id => 0, --:P_CONC_REQUEST_ID,
lock_flag => -1,
rollup_option => -1,
alt_rtg_desg => '',
alt_desg =>'', --P_ALTERNATE_DESG, --:P_ALTERNATE_DESG,
rev_date =>'', --:P_REVISION_DATE,
err_msg =>V_err_msg,
error_code =>V_err_code,
verify_flag =>0,
cst_rlp_id =>0,
plan_factor_flag =>2, -- :P_PLAN_FACTOR_FLAG,
incl_lt_flag =>2);
END;
SELECT ORGANIZATION_ID,
COMPONENT_ITEM_ID CII,
COMPONENT_QUANTITY CQ,
COMPONENT_SEQUENCE_ID CSI,
PLAN_LEVEL,
ITEM_TYPE IT
FROM BOM_EXPLOSION_VIEW A
WHERE
( ((TRUNC(EFFECTIVITY_DATE)<=TRUNC(Sysdate)) AND
(TRUNC(sysdate)<TRUNC(DISABLE_DATE))
)
OR (DISABLE_DATE IS NULL) OR (Sysdate IS NULL)
)
AND PLAN_LEVEL > 0
ORDER BY SORT_ORDER;
select * from BOM_EXPLOSION_VIEW
工艺路线,部门,资源:
Select distinct Bd.Department_Id,Bd.Department_Code,Br.Resource_Code,Bor.Assembly_Item_Id,Mtl.Segment1,Res.Usage_Rate_Or_Amount,Res.Usage_Rate_Or_Amount_Inverse
From Bom_Operational_Routings Bor,
Bom_Operation_Sequences Bos,
Bom_Operation_Resources Res,
Bom_Departments Bd,
Bom_Resources Br,
mtl_system_items Mtl
Where Bor.Organization_Id =81
--And Bor.Assembly_Item_Id =74694
And Bos.Routing_Sequence_Id = Bor.Routing_Sequence_Id
And Res.Operation_Sequence_Id = Bos.Operation_Sequence_Id
And Res.Schedule_Flag = 1 --可计划
And Bd.Organization_Id = 81
And Bd.Department_Id = Bos.Department_Id
And Br.Organization_Id =81
And Br.Resource_Id = Res.Resource_Id
and Bos.Disable_date is null
And Mtl.Inventory_Item_id=Bor.Assembly_Item_Id