常用查询scripts
/*bom*/
select p_item.segment1,c_item.segment1,bic.COMPONENT_QUANTITY,bic.COMPONENT_YIELD_FACTOR
from bom_bill_of_materials bbom,
bom_inventory_components bic,
mtl_system_items_b p_item,
mtl_system_items_b c_item
where bbom.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
and bbom.ASSEMBLY_ITEM_ID = p_item.inventory_item_id
and bic.COMPONENT_ITEM_ID = c_item.inventory_item_id
and bbom.ORGANIZATION_ID = p_item.organization_id
and p_item.organization_id = c_item.organization_id
and nvl(bic.DISABLE_DATE,sysdate)>= sysdate
and p_item.segment1 = &p_item_code
and p_item.organization_id = &p_org_id
and bbom.ALTERNATE_BOM_DESIGNATOR is null
order 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'
) boms
CONNECT 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 t1
ORDER 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.MEANING
from fnd_lookup_values_vl flv
where 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 = ''
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的PR
select prh.requisition_header_id,
prl.requisition_line_id,
prd.distribution_id,
prl.destination_organization_id dest_org,
(select haout.name
from HR_ALL_ORGANIZATION_UNITS_TL haout
where haout.organization_id = prl.org_id
and 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_date
from po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
per_people_f ppf,
mtl_system_items_b msi
where prh.requisition_header_id = prl.requisition_header_id
and prd.requisition_line_id = prl.requisition_line_id
and prd.org_id = prl.org_id
and prh.org_id = prl.org_id
and prl.to_person_id = ppf.PERSON_ID
and (nvl(ppf.EFFECTIVE_END_DATE,sysdate) >=sysdate)
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
and 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 haout
where haout.organization_id = porl.org_id
and 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_by
FROM 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_id
AND pod.po_line_id = pol.po_line_id
AND poh.po_header_id = pol.po_header_id
AND 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_id
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND poh.agent_id = p.person_id
AND hrl1.location_id(+) = poh.ship_to_location_id
and porl.item_id = msi.inventory_item_id
and porl.destination_organization_id = msi.organization_id
--- and porh.authorization_status = 'APPROVED'
--- and porh.type_lookup_code = 'PURCHASE'
/*外协工单关联PR*/
select prha.org_id "org id",
prha.segment1 "PR NO",
prha.authorization_status,
prla.line_num "PR LINE NO",
prla.cancel_flag,
prla.quantity "REQ QTY",
prla.quantity_received "REC QTY",
prla.quantity_cancelled "CANCEL QTY",
prla.quantity_delivered "DELIVERY QTY",
we.organization_id organization_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 = &organization_id
and we.wip_entity_name = '&job';
/*外协工单关联PO*/
select pha.org_id "Org id",
pha.segment1 "PO NO",
pha.authorization_status "STATUS",
pha.cancel_flag "PO HEADER CANCEL FLAG",
pla.line_num "PO LINE NO",
pla.cancel_flag "PO LINE CANCEL FLAG",
plla.shipment_num,
plla.ship_to_organization_id "SHIP ORGANIZATION ",
plla.ship_to_location_id "SHIP TO LOCATION",
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 "DISABLE DATE",
(select flv.meaning
from fnd_lookup_values flv
where flv.lookup_type = 'BOM_RESOURCE_TYPE'
and flv.language = 'ZHS'
and flv.lookup_code = br.resource_type) "RESOURCE TYPE", ---1:?? 2:?? 3:?? 4:?? 5;??
(select flv.meaning
from fnd_lookup_values flv
where flv.lookup_type = 'BOM_AUTOCHARGE_TYPE'
and flv.language = 'ZHS'
and flv.lookup_code = br.autocharge_type) "CHARGE TYPE", ---1:WIP?? 2:?? 3:PO?? 4:PO??
br.unit_of_measure "UOM",
(select flv.meaning
from fnd_lookup_values flv
where flv.lookup_type = 'BOM_BASIS_TYPE'
and flv.language = 'US'
and flv.lookup_code = br.default_basis_type) "BASIS",
(select flv.meaning
from fnd_lookup_values flv
where flv.lookup_type = 'CST_COST_CODE_TYPE'
and flv.language = 'ZHS'
and flv.lookup_code = br.cost_code_type) "COST TYPE", ---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) "OSP ITEM",
decode(br.allow_costs_flag, 1, 'Y', 2, 'N') "COST FLAG", ---1:? 2:?
---brv.default_activity ??,
decode(br.standard_rate_flag, 1, 'Y', 2, 'N') "STANDARD RATE FLAG" ---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 = &organization_id
and we.wip_entity_name = '&job'
order by pha.segment1, pla.line_num