常用查詢scripts /*bom*/ select p_item.segment1,c_item.segment1,bic.COMPONENT_QUANTITY,bic.COMPONENT_YIELD_FACTOR from bom_bill_of_materials bbom, bom_inve
常用查詢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 = '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的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.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 = &job order by pha.segment1,pla.line_num
