你的位置:首页 > 数据库

[数据库]BOM/ROUTING/PO/WIP等模块常用查询


常用查询scripts

/*bom*/select p_item.segment1,c_item.segment1,bic.COMPONENT_QUANTITY,bic.COMPONENT_YIELD_FACTORfrom bom_bill_of_materials bbom,   bom_inventory_components bic,   mtl_system_items_b p_item,   mtl_system_items_b c_itemwhere bbom.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_IDand  bbom.ASSEMBLY_ITEM_ID = p_item.inventory_item_idand  bic.COMPONENT_ITEM_ID = c_item.inventory_item_idand  bbom.ORGANIZATION_ID = p_item.organization_idand  p_item.organization_id = c_item.organization_idand  nvl(bic.DISABLE_DATE,sysdate)>= sysdate and  p_item.segment1 = &p_item_codeand  p_item.organization_id = &p_org_idand  bbom.ALTERNATE_BOM_DESIGNATOR is nullorder by 1,2

/*Intend BOM*/with t as(SELECT boms.organization_id,    boms.organization_id || '>' || connect_by_root assembly_number || sys_connect_by_path(boms.component_number, '>') code_chain,    boms.organization_id || '>' || connect_by_root bill_sequence_id || sys_connect_by_path(boms.component_sequence_id, '>') id_chain,    connect_by_root assembly_number assembly_number,    boms.assembly_description,    LEVEL bom_level,    boms.component_number component_number,    lpad(' ', (LEVEL - 1) * 2, ' ') || boms.component_number ind_component_number,    boms.component_description,    boms.primary_uom_code uom,    boms.component_quantity component_quantity,    boms.planning_factor,    boms.component_yield_factor,    boms.effectivity_date FROM (SELECT bom1.organization_id,        bom1.assembly_item_id,        mst1.segment1 assembly_number,        mst1.description assembly_description,        bom1.bill_sequence_id bill_sequence_id,        bom1.alternate_bom_designator assembly_alternate,        bomc.component_sequence_id,        bomc.component_item_id,        mstc.segment1 component_number,        mstc.description component_description,        mstc.primary_uom_code,        bomc.component_quantity,        bomc.effectivity_date,        bomc.planning_factor,        bomc.component_yield_factor,        bomc.supply_subinventory     FROM apps.bom_bill_of_materials  bom1,        inv.mtl_system_items_b    mst1,---主件        apps.bom_inventory_components bomc,        inv.mtl_system_items_b    mstc ---组件     WHERE bom1.organization_id = mst1.organization_id      AND bom1.assembly_item_id = mst1.inventory_item_id      AND bom1.bill_sequence_id = bomc.bill_sequence_id      AND bom1.organization_id = mstc.organization_id      AND bomc.component_item_id = mstc.inventory_item_id       --Item      AND mst1.bom_enabled_flag = 'Y'      AND mst1.bom_item_type IN (1, 2, 3, 4) --Dependent       --BOM Header      AND bom1.assembly_type = 1 --1 Manufature,2 ENG      ---AND nvl(bom1.effectivity_control, 1) <= 3       --BOM Line      AND nvl(bomc.disable_date, SYSDATE) >= SYSDATE      AND bomc.effectivity_date <= SYSDATE      AND bomc.implementation_date IS NOT NULL      AND nvl(bomc.eco_for_production, 2) = 2       --Filters      AND mst1.organization_id = 89      AND bom1.alternate_bom_designator IS NULL      ----and mst1.segment1 = '17G4-01003-0001'      ) bomsCONNECT BY PRIOR boms.organization_id = boms.organization_id    AND PRIOR boms.component_item_id = boms.assembly_item_id  start with boms.assembly_number = '17G4-01003-0001')SELECT t1.organization_id,    t1.code_chain,    t1.ind_component_number,    t1.assembly_number,    t1.assembly_description,    t1.bom_level,    t1.component_number,    t1.component_description,    t1.uom,       t1.id_chain,    t1.component_quantity,    (SELECT power(10,           SUM(CASE              WHEN t2.component_quantity = 0 THEN              0              ELSE              log(10, abs(t2.component_quantity))             END)) * decode(MOD(COUNT(decode(sign(t2.component_quantity), -1, 1)), 2), 1, -1, 1) *        (CASE         WHEN COUNT(decode(t2.component_quantity, 0, 1)) >= 1 THEN          0         ELSE          1        END)     FROM t t2     WHERE t1.id_chain LIKE t2.id_chain || '%') extended_quantity,    t1.planning_factor,    t1.component_yield_factor,    t1.effectivity_date FROM t t1ORDER BY t1.id_chain;

/*routing*/select p_item.segment1,    bos.operation_seq_num,    bos.standard_operation_code,    bso.operation_description from bom_operational_routings bor,    bom_operation_sequences_v bos,    mtl_system_items_b    p_item,    bom_standard_operations  bso where bor.routing_sequence_id = bos.routing_sequence_id  and bor.assembly_item_id = p_item.inventory_item_id  and bor.organization_id = p_item.organization_id  and bor.alternate_routing_designator is null  and nvl(bos.disable_date, sysdate) >= sysdate  and bso.organization_id = bor.organization_id  and bso.standard_operation_id = bos.standard_operation_id  and p_item.segment1 = &p_item_code  and p_item.organization_id = &p_org_id order by 1, 2

 


/*job status type*/select flv.LOOKUP_CODE,flv.MEANINGfrom fnd_lookup_values_vl flvwhere lookup_type = 'WIP_JOB_STATUS'order by 1

/*eco info*/select  eec.change_notice ECO号, /*er.revised_item_id ,*/ msi1.segment1 装配件编码, msi1.description 装配件描述, er.alternate_bom_designator 替代项, eec.creation_date ECO创建时间, (select FLV.MEANING from FND_LOOKUP_VALUES FLV where FLV.LOOKUP_TYPE = 'ECG_ACTION'  AND FLV.LANGUAGE = 'ZHS'  AND FLV.LOOKUP_CODE = ERC.acd_type) ACTION, msi2.segment1 组件编码, msi2.description 组件件描述, erc.primary_uom_code 单位, ---erc.old_component_sequence_id, bic.COMPONENT_QUANTITY 旧组件用量, bic.COMPONENT_YIELD_FACTOR 旧产出率, -----erc.component_sequence_id, erc.component_quantity 新组件用量, erc.component_yield_factor 新产出率, erc.disable_date 失效日期 from ENG_ENGINEERING_CHANGES_V eec,    ENG_REVISED_ITEMS_V    er,    ENG_REVISED_COMPONENTS_V erc,    mtl_system_items_b msi1,    mtl_system_items_b msi2,    bom_inventory_components bic where eec.change_notice = er.change_notice  and eec.change_id = er.change_id  and eec.organization_id = er.organization_id  and erc.change_notice = er.change_notice  and erc.revised_item_sequence_id = er.revised_item_sequence_id  and er.organization_id = msi1.organization_id  and er.revised_item_id = msi1.inventory_item_id  and erc.component_item_id = msi2.inventory_item_id  and msi2.organization_id = msi1.organization_id  and eec.organization_id = 89  and erc.old_component_sequence_id = bic.COMPONENT_SEQUENCE_ID  and bic.BILL_SEQUENCE_ID =er.bill_sequence_id  -----and erc.acd_type = 1 ----1:添加 2:更改 3:禁用  ---and msi1.segment1 = 'E678-01001-0001'  ----and msi2.segment1 = 'E678-01016-0002'---and eec.change_notice = 'PAO4180'  and eec.status_type = 6 ---已实施  and to_char(eec.creation_date, 'yyyy/mm/dd') >= '2014/12/01'  and to_char(eec.creation_date, 'yyyy/mm/dd') <= '2015/01/21'  order by msi1.segment1,er.alternate_bom_designator,msi2.segment1

/*ERP 采购接收\检验\入库*/select pha.segment1 PO号,    pla.line_num PO行,    rsh.receipt_num,    DECODE(rt.transaction_type,'RECEIVE',1,'ACCEPT',2,'DELIVER',3,'RETURN TO RECEIVING',4,'RETURN TO VENDOR',5) TRX_TYPE,    rt.transaction_type,    (case     when rt.transaction_type = 'RECEIVE'     then sum(rt.quantity)    end ) QTY_REC,     (case     when rt.transaction_type = 'ACCEPT'     then sum(rt.quantity)    end ) QTY_ACC,     (case     when rt.transaction_type = 'DELIVER'     then sum(rt.quantity)    end ) QTY_DELIVER,     (case     when rt.transaction_type = 'RETURN TO RECEIVING'     then sum(rt.quantity)    end ) QTY_R_REC,     (case     when rt.transaction_type = 'RETURN TO VENDOR'     then sum(rt.quantity)    end ) QTY_R_VEN,    (case     when rt.transaction_type = 'REJECT'     then sum(rt.quantity)    end ) QTY_Reject from po_headers_all    pha,    po_lines_all     pla,    rcv_transactions   rt,    rcv_shipment_headers rsh,    rcv_shipment_lines  rsl where pha.po_header_id = pla.po_header_id  and pla.org_id = pha.org_id  and rt.po_header_id = pha.po_header_id  and rt.po_line_id = pla.po_line_id  and rt.shipment_header_id = rsh.shipment_header_id  and rt.shipment_line_id = rsl.shipment_line_id  and rsl.po_header_id = pha.po_header_id  and rsl.po_line_id = pla.po_line_id  and rsh.shipment_header_id = rsl.shipment_header_id  ---and rsh.receipt_num = 10131029  and pha.segment1 = '73098472'  GROUP BY pha.segment1,pla.line_num,rsh.receipt_num,rt.transaction_type order by 3,4

/*PO与PR关联 SCRIPTS*/
/*思路: po_requisition_headers_all po_requisition_lines_all po_req_distributions_all 3表 分别与以下4表关联 po_headers_all po_lines_all po_line_locations_all po_distributions_all */---1、未创建PO的PRselect prh.requisition_header_id,prl.requisition_line_id,prd.distribution_id,prl.destination_organization_id dest_org,(select haout.namefrom HR_ALL_ORGANIZATION_UNITS_TL haoutwhere haout.organization_id = prl.org_idand haout.language = 'ZHS') REQUESTING_ORG,prl.cancel_flag ,prh.segment1 pr,prl.line_num ,prl.creation_date,msi.segment1 item_code,prl.item_description,prl.quantity,prl.unit_meas_lookup_code,---prl.created_by,---prl.to_person_id,ppf.FULL_NAME,prl.need_by_datefrom po_requisition_headers_all prh,po_requisition_lines_all prl,po_req_distributions_all prd,per_people_f ppf,mtl_system_items_b msiwhere prh.requisition_header_id = prl.requisition_header_idand prd.requisition_line_id = prl.requisition_line_idand prd.org_id = prl.org_idand prh.org_id = prl.org_idand prl.to_person_id = ppf.PERSON_IDand (nvl(ppf.EFFECTIVE_END_DATE,sysdate) >=sysdate)and msi.inventory_item_id = prl.item_idand msi.organization_id = prl.destination_organization_idand prh.authorization_status = 'APPROVED'and prh.type_lookup_code = 'PURCHASE'and (prl.cancel_flag ='N' OR prl.cancel_flag is null)and (prh.cancel_flag = 'N' OR prh.cancel_flag is null)AND prh.segment1 = &pr/*未创建PO*/and not exists (select 1 from po_distributions_all pda where pda.req_distribution_id = prd.distribution_id)order by prh.segment1,prl.line_num;----2、PR 信息SELECT porl.destination_organization_id dest_org,(select haout.name from HR_ALL_ORGANIZATION_UNITS_TL haoutwhere haout.organization_id = porl.org_idand haout.language ='ZHS') REQUESTING_ORG,porh.segment1 pr,poh.segment1 po,p.full_name Buyer,pov.vendor_name,hrl1.location_code,pol.line_num,msi.segment1,pol.item_description,pol.unit_meas_lookup_code,pol.quantity,pll.need_by_date need_by,pll.promised_date promised_byFROM po_requisition_headers_all porh,po_requisition_lines_all porl,po_req_distributions_all pord,po_headers_all poh,po_lines_all pol,po_line_locations_all pll,po_distributions_all pod,po_vendors pov,po_vendor_sites_all povs,per_people_f p,hr_locations hrl1,mtl_system_items_b msi--po_releases_all por WHERE pod.po_header_id = poh.po_header_idAND pod.po_line_id = pol.po_line_idAND poh.po_header_id = pol.po_header_idAND pod.line_location_id = pll.line_location_id--AND pod.po_release_id = por.po_release_id(+)AND pod.req_distribution_id = pord.distribution_id(+)AND pord.requisition_line_id = porl.requisition_line_id(+)AND porl.requisition_header_id = porh.requisition_header_id(+)AND pov.vendor_id(+) = poh.vendor_idAND povs.vendor_site_id(+) = poh.vendor_site_idAND poh.agent_id = p.person_idAND hrl1.location_id(+) = poh.ship_to_location_idand porl.item_id = msi.inventory_item_idand porl.destination_organization_id = msi.organization_id--- and porh.authorization_status = 'APPROVED'--- and porh.type_lookup_code = 'PURCHASE'

/*外协工单关联PR*/select prha.segment1 申请号,    prha.authorization_status,    prla.line_num 申请行,    prla.cancel_flag,    prla.quantity 申请数量,    prla.quantity_received 接收数量,    prla.quantity_cancelled 取消数量,    prla.quantity_delivered 交货数量,    we.organization_id org_id,    we.wip_entity_name,    (select f.meaning     from fnd_lookup_values f     where f.lookup_type = 'WIP_JOB_STATUS'      and f.lookup_code = wdj.status_type      and f.language = 'ZHS') status,    wdj.class_code,    msi1.segment1 assm_item_cd,    msi1.description assm_item_dsp,    wdj.start_quantity,    wdj.quantity_completed,    wdj.quantity_scrapped,    wdj.date_released,    wdj.date_completed,    wdj.date_closed,    wo.operation_seq_num,    wo.operation_code,    wo.description,    wor.resource_seq_num,    br.resource_code,    br.description from wip_entities        we,    wip_discrete_jobs     wdj,    mtl_system_items_b     msi1, ----装配件    wip_operations_v      wo,    wip_operation_resources  wor,    bom_resources       br,    po_requisition_lines_all  prla,    po_requisition_headers_all prha where we.wip_entity_id = wdj.wip_entity_id  and we.organization_id = wdj.organization_id  and wdj.primary_item_id = msi1.inventory_item_id  and we.organization_id = msi1.organization_id  and wdj.wip_entity_id = wo.wip_entity_id  and wdj.organization_id = wo.organization_id  and wor.organization_id = wo.organization_id  and wor.wip_entity_id = wo.wip_entity_id  and wor.operation_seq_num = wo.operation_seq_num  and br.organization_id = wor.organization_id  and br.resource_id = wor.resource_id  and prla.wip_entity_id = wdj.wip_entity_id  and prla.Wip_Operation_Seq_Num = wo.operation_seq_num  and prla.wip_resource_seq_num = wor.resource_seq_num  and prla.requisition_header_id = prha.requisition_header_id  and prla.org_id = prha.org_id  and we.organization_id = &org_id  and we.wip_entity_name = &job;/*外协工单关联PO*/select pha.segment1 PO号,     pha.authorization_status 状态,     pha.cancel_flag 头取消状态,     pla.line_num PO行,     pla.cancel_flag 行取消状态,     plla.shipment_num,     plla.ship_to_organization_id 发运组织,     plla.ship_to_location_id 收货方,     we.organization_id org_Id,     we.wip_entity_name,     (select f.meaning      from fnd_lookup_values f      where f.lookup_type = 'WIP_JOB_STATUS'       and f.lookup_code = wdj.status_type       and f.language = 'ZHS') status,     wdj.class_code,     msi1.segment1 assm_item_cd,     msi1.description assm_item_dsp,     wdj.start_quantity,     wdj.quantity_completed,     wdj.quantity_scrapped,     wdj.date_released,     wdj.date_completed,     wdj.date_closed,     wo.operation_seq_num,     wo.OPERATION_CODE,     wo.DEPARTMENT_CODE,     wo.description,     wor.resource_seq_num,     wor.resource_id,     br.resource_code,     br.description,     br.disable_date 失效日期,     /*br.resource_type,*/     decode(br.resource_type,1,'设备',2,'人员',3,'币种',4,'杂项',5,'金额') 资源类型, ---1:设备 2:人员 3:币种 4:杂项 5;金额    decode(br.autocharge_type,1,'WIP移动',2,'人工',3,'PO接收',4,'PO移动') 计费类型,---1:WIP移动 2:人工 3:PO接收 4:PO移动    br.unit_of_measure 单位,    decode(br.default_basis_type,1,'物料',2,'批次') 基准,    decode(br.cost_code_type,3,'N',4,'Y')  外协加工, ---3:否 4:是    (select msi.segment1    from mtl_system_items_b msi    where msi.organization_id = br.organization_id    and  msi.inventory_item_id = br.purchase_item_id) 外协加工物料,    decode(br.allow_costs_flag,1,'Y',2,'N') 是否计算成本, ---1:是 2:否    ---brv.default_activity 活动,    decode(br.standard_rate_flag,1,'Y',2,'N') 是否标准费率 ---1:是 2:非 from wip_entities    we,    wip_discrete_jobs wdj,    mtl_system_items_b msi1,    wip_operations_v   wo,    wip_operation_resources wor,    bom_resources br,    po_distributions_all pda,    po_line_locations_all plla,    po_lines_all pla,    po_headers_all pha where we.wip_entity_id = wdj.wip_entity_id and  we.organization_id = wdj.organization_id and  wdj.primary_item_id = msi1.inventory_item_id and  we.organization_id = msi1.organization_id and  wdj.wip_entity_id =wo.wip_entity_id and  wdj.organization_id =wo.organization_id and  wor.organization_id = wo.organization_id and  wor.wip_entity_id = wo.wip_entity_id and  wor.operation_seq_num = wo.operation_seq_num and  br.organization_id = wor.organization_id and  br.resource_id = wor.resource_id and  pda.wip_entity_id = wdj.wip_entity_id and  pda.wip_operation_seq_num = wo.operation_seq_num and  pda.wip_resource_seq_num = wor.resource_seq_num and  pda.destination_organization_id = wdj.organization_id and  pda.line_location_id =plla.line_location_id and  pda.org_id =plla.org_id and  plla.po_line_id = pla.po_line_id and  plla.org_id = pla.org_id and  pla.org_id =pha.org_id and  pla.po_header_id = pha.po_header_id and  plla.po_header_id = pha.po_header_id and  pda.po_header_id = pha.po_header_id and  we.organization_id = &org_id and  we.wip_entity_name = &joborder by pha.segment1,pla.line_num