ERP工厂 订单 品名 PO 出货单 客人sql

select distinct oh.order_number ORDERHEAD,ol.line_number||'.'||ol.shipment_number ORDERLINE,wsh.delivery_id DELIVERY,ol.ordered_item MODEL,OL.ORDERED_QUANTITY SHIPPCS,Round(decode(nvl(msi.attribute4, 0),1,OL.ORDERED_QUANTITY /decode(instr(nvl(msi.ATTRIBUTE3, 0), '*'),2,substr(msi.ATTRIBUTE3, 1, 1) *substr(msi.ATTRIBUTE3, 3, 3)),OL.ORDERED_QUANTITY),3) SHIPSET,to_char(wnd.confirm_date, 'YYYY/MM/DD') SHIPDATE,oh.cust_po_number CUSTOMPONO,wsh.lot_number CYCLYS, substr(ol.ordered_item,11,2) LAYERS,DECODE(ol.attribute3, 'Y', '是', '否') SAMPLE,
							(select cmip.param_value from cux.cux_mfg_item_prms cmip where cmip.param_name = '客户品名' and cmip.inventory_item_id = ol.inventory_item_id and cmip.organization_id = ol.attribute15 and rownum = 1) CATEGO,ol.unit_selling_price SALESPRICE,oh.transactional_curr_code WDCURRENCY,'0' PRODUCTTYPE,ac.CUSTOMER_ID CUSTOMER_ID,OL.ATTRIBUTE15 MFGORGID,OL.INVENTORY_ITEM_ID INVENTORYITEMID,msi.PRIMARY_UNIT_OF_MEASURE ORDERQUANTITYUOM,
 							(CASE WHEN OH.ORG_ID = OL.SHIP_FROM_ORG_ID AND OH.ORG_ID = OL.ATTRIBUTE15 THEN 'AAA' WHEN OH.ORG_ID != OL.ATTRIBUTE15 AND OH.ORG_ID = OL.SHIP_FROM_ORG_ID THEN 'ABA' WHEN OH.ORG_ID != OL.ATTRIBUTE15 AND OL.ATTRIBUTE15 = OL.SHIP_FROM_ORG_ID THEN 'ABB' WHEN OH.ORG_ID != OL.ATTRIBUTE15 AND OL.ATTRIBUTE15 != OL.SHIP_FROM_ORG_ID THEN 'ABC' ELSE '' END) ORDERTYPE 
							FROM apps.MTL_SYSTEM_ITEMS_FVL MSI,apps.wsh_deliverables_v wsh,ont.oe_order_lines_all ol,ont.oe_order_headers_all oh,apps.wsh_new_deliveries wnd,apps.ar_customers ac WHERE ol.attribute15 = MSI.ORGANIZATION_ID AND OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND oh.header_id = ol.header_id AND oh.sold_to_org_id = ac.customer_id AND oh.header_id = wsh.source_header_id AND ol.line_id = wsh.source_line_id AND wnd.delivery_id(+) = wsh.delivery_id AND wnd.delivery_type(+) = 'STANDARD'"	
                            others=" AC.CUSTOMER_ID =NVL('@{CUSTOMER_ID}',AC.CUSTOMER_ID)