转:Order Management Useful Scripts .

Listed some useful queries scripts for Oracle Order Management Flow.

(For Order Management Detailed Flow, You may check my previous post: Oracle EBS订单的流程(Order->AR): http://blog.youkuaiyun.com/pan_tian/article/details/7693447 )


Sales Order Info

Order Header

SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = '65020';

Order Lines

SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID IN (SELECT HEADER_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = '65020');

Inventory

MTL_SALES_ORDERS

SELECT S.SALES_ORDER_ID,S.SEGMENT1, S.SEGMENT2,S.SEGMENT3,H.ORDER_NUMBER,H.FLOW_STATUS_CODE,H.SOLD_TO_ORG_ID,H.OPEN_FLAG
FROM MTL_SALES_ORDERS S,OE_ORDER_HEADERS_ALL H
WHERE S.SEGMENT1 = TO_CHAR(H.ORDER_NUMBER)
AND  H.ORDER_NUMBER = '65020'

(MTL_SALES_ORDERS exists for the purpose of mapping sales orders between other applications and Inventory,Once Sales Order get booked, There will be record go into mtl_sales_orders.refer:link)

Reservations

SELECT MR.* FROM MTL_RESERVATIONS MR,OE_ORDER_LINES_ALL OOLA
WHERE MR.DEMAND_SOURCE_LINE_ID = OOLA.LINE_ID
AND OOLA.LINE_ID = &ORDER_LINE_ID;
(Each record is a reservation that ties an item/organization combination with a demand source and a supply source.NOTE:MR.DEMAND_SOURCE_HEADER_ID point to Mtl_Sales_Orders.Sales_Order_ID, NOT OE_ORDER_HEADERS_ALL.HEADER_ID)


Allocation

SELECT * FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRX_SOURCE_LINE_ID IN
      (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
       WHERE HEADER_ID IN (SELECT HEADER_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = '65020'));


Move Order

SELECT * FROM MTL_TXN_REQUEST_LINES
WHERE TXN_SOURCE_LINE_ID IN
       (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
       WHERE HEADER_ID IN (SELECT HEADER_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = '65020'));

(NOTE:MTRL.TXN_SOURCE_ID point to Mtl_Sales_Orders.Sales_Order_ID, NOT OE_ORDER_HEADERS_ALL.HEADER_ID)


Material Transactions(Picking, Issuing Transaction Type)

SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRX_SOURCE_LINE_ID IN
      (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
       WHERE HEADER_ID IN (SELECT HEADER_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = '65020'));


Shipping

Delivery Lines

SELECT * FROM WSH_DELIVERY_DETAILS WHERE  SOURCE_HEADER_NUMBER = '65020';

(WSH_DELIVERY_DETAILS has delivery lines and LPNs.Once Sales Order get booked, There will be record go into WDD)


Delivery Assignments

SELECT * FROM WSH_DELIVERY_ASSIGNMENTS WDA, WSH_DELIVERY_DETAILS WDD
WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.SOURCE_HEADER_NUMBER = '65020';
(WSH_DELIVERY_ASSIGNMENTS assigns delivery details to a delivery and/or a parent delivery detail (LPN),Once Sales Order get booked, There will be record go into WDA)


SELECT * FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID=WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID

(WSH_NEW_DELIVERIES has the delivery records.)


SELECT * FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID=WSH_NEW_DELIVERIES.DELIVERY_ID

(WSH_DELIVERY_LEGS maps deliveries to their pick up and drop off stops.)

SELECT * FROM WSH_TRIP_STOPS WTS
WHERE STOP_ID=WSH_DELIVERY_LEGS.PICK_UP_STOP_ID
(WSH_TRIP_STOPS has trip stop records.)


SELECT * FROM WSH_TRIPS WT
WHERE TRIP_ID=WSH_TRIP_STOPS.TRIP_ID

(Trip records.)



Other Related Queries

Order Type Info
select * from apps.oe_transaction_types_tl
where transaction_type_id= oe_order_headers_all.order_type_id;


Price List Info
select * from apps.qp_list_headers_tl
where list_header_id=oe_order_headers_all.price_list_id;

select * from apps.qp_list_lines
where list_header_id=oe_order_headers_all.price_list_id;

Find Customer Info
select * from hz_cust_accounts hca
where cust_account_id=oe_order_headers_all.sold_to_org_id

select * from apps.hz_parties
where party_id=hz_cust_accounts.party_id

Find Ship to Location Info
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.ship_to_org_id

select * from apps.hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

Find Bill to location
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.invoice_to_org_id

select * from hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

Actual Address
select * from hz_locations
where location_id=hz_party_sites.location_id

Ship Method
select ship_method_code from wsh.wsh_carrier_services
where ship_method_code = oe_order_headers_all.shipping_method_code

---------------------------------------------------------------------------------------------------------

BTW:There are two standard ways to diagnostic a Sales Order

1. HTMOMSE Sales Order Diagnostic Script. [ID 133464.1]

HTMomse11i.sql or HTMomse12.sql

2.Report:"Diagnostics: OM Order Information" for given order number

run the 'Diagnostic: Order Information' report (OMORDER.SQL) from Order Management Super User > Reports, Requests > Requests menu.



---------------------------------------------------------------------------------------------------------

Following information copy from http://oracleapps88.blogspot.jp/2013/02/order-management-tables.html

Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.

Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release

Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock

Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned

Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock

Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped

oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N

Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated

Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits

Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Orders
oe_order_lines_all Cancel Order Details.

http://blog.youkuaiyun.com/pan_tian/article/details/8771423

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12122734/viewspace-760988/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/12122734/viewspace-760988/

AP应付票据信息一览 select cux_bq_common.get_lookup_meaning('PAYMENT TYPE', aca.payment_type_flag) as "类型", hou.name as "业务实体", decode(aca.party_id, '', 阅读详情

相关推荐

WPF数据可视化:LiveCharts自定义X轴与Y轴的5个实战技巧(附完整代码)

本文详细介绍了在WPF应用中使用LiveCharts进行数据可视化时,自定义X轴与Y轴的5个核心实战技巧。内容涵盖精确控制刻度步距、深度定制视觉样式、实现多轴布局对比、优化显示空间以及构建响应式动态轴,并提供了完整的C#和XAML代码示例,帮助开发者创建专业且用户体验出色的图表。

moss5的博客 693

Order Management Useful Scripts

Listed some useful queries scripts for Oracle Order Management Flow.(For Order Management Detailed Flow, You may check my previous post: Oracle EBS订单的流程(Order->AR): http://blog.csdn.net/pan_tian/artic

田攀的日志 1万+

【C++】小猫钓鱼纸牌游戏--C++实现

小猫钓鱼问题: 描述:初始,两个人各有n张牌(牌面0-10),顺序固定,只能按顺序出牌。然后,两个人轮流在桌面上以接龙的方式出牌,当某个人放置的牌在桌面上已经出现时,他收起相同牌面及其中间的牌作为自己的筹码,同时需要再放一张牌到桌面。某人手上的牌出完后,另一个人继续出,直到两人手上都没牌为止,输出结束时两个人手上的筹码数。 #include<iostream> using namespace std; struct queue { int head; //队列首 int tail; //

qq_41344430的博客 3202

oracle erp 10.7 设置,请教一个简单的问题about Oracle application10.7 Ap

我在AP application 输入invoice num去查询有数据显示。然后,我想知道这些数据来自数据库的那个表。help----about thisrecord.显示table_name ap_invoices_v--查看view 定义select * from dba_viewswhereview_name = 'AP_INVOICES_V'执行 select * from ...

weixin_42121272的博客 497

Scipy优化踩坑实录:trust-constr和SLSQP约束定义到底差在哪?

本文深入解析了Scipy优化模块中trust-constr和SLSQP两种方法在约束定义上的差异。通过对比面向对象的trust-constr和字典式定义的SLSQP,揭示了它们各自的优势与适用场景,帮助开发者在有约束优化问题中选择合适的算法,提升计算效率与准确性。

weixin_42573113的博客 271

深度学习入门:使用CMSIS-NN在微控制器上部署模型的完整指南与Python Jupyter实践

CMSIS-NN是ARM为Cortex-M系列微控制器设计的神经网络库。它旨在提供一套高效、轻量级的神经网络API,使得开发者可以在资源受限的微控制器上运行深度学习模型。CMSIS-NN优化了各种常见的神经网络层,如卷积层、全连接层等,确保它们在微控制器上的运行效率。# 定义模型])# 编译模型# 训练模型这样,我们就得到了一个简单的手写数字识别模型。注意:为了简洁和清晰,本文中的代码可能不是最优的或最完整的实现。为了获得完整的项目和更多的优化技巧,请下载完整项目。

m0_57781768的博客 5886

总帐接口表gl_interface的详细说明

 要使"日记帐导入"成功地将导入数据转换为日记帐分录,您必须在要求输入值的所有GL_INTERFACE 表列(包括所有非空列)中输入值。请在 GL_INTERFACE表的以下要求列中输入值:    1. 状态:输入 NEW 值,以指明您要将新数据导入总帐应用产品。    2. SET_OF_BOOKS_ID:输入事务处理的相应帐套标识。您可以在总帐应用产品的"帐套"表单-->"帐套"表的 SET_

yy5512的专栏-Oracle ERP 经验累计 2283

泛微E9表单建模查询列表勾选后带入流程明细表中

注意:参照下图各项配置,否则会有问题。

泛微二次开发后端知识总结 5314

根付款相关的表

Payments and its tables:You can create either single payments or payments batches containing multiple payments.  Payment Types-- Manual Payment: When you create a single payment, rather than an entir

yy5512的专栏-Oracle ERP 经验累计 2628

三菱 FX5U PLC 4轴程序 控制松下伺服3个, 步进电机一个

三菱 FX5U PLC 4轴程序。 控制松下伺服3个, 步进电机一个, 四轴自动堆垛码垛设备程序, 回原点动作用专用的原点回归指令写的, 手动运行用三菱相对定位指令写的 , 自动运行用绝对定位指令写的取料运行, 表格定位写的自动放料运行, 设备各种报警异常都有写出 程序每一步都有注释。 包括PLC程序、显控触摸屏 程序、松下伺服驱动器接线图。 程序内都有IO注释 编号:4816635519484681汇美0808 ...

m0_68069172的博客 3382

【电子通识】TINA-TI怎么使用受控源向导创建受控源

TINA-TI怎么使用受控源向导创建受控源

一点硬件 360

Useful queries/code/scripts in Oracle Apps Order Management

Following are some of the most frequently used queries in oracle apps order management: Order header info Select * from oe_order_headers_all ooh where order_number= :p_order_number Op...

weixin_30426879的博客 138

手把手教你完成基于cnn的手写数字识别系统MATLAB语言

手写数字识别是经典的 CNN 分类应用之一,常用的数据集就是 MNIST 手写数字数据集,包含 0~9 这 10 个数字的手写图片,每个数字都由 6 万幅训练图像和 1 万幅测试图像构成。为了便于直观分析,这里选择 MATLAB 工具箱提供的 DigitDataset 进行训练识别,读者也可以直接利用 MNIST 数据集或者自己设计的数据集进行实验。其中,数据集文件夹 db 如图 所示。在数据集文件夹 db 里面对每个数字都建立了子文件夹,各自包含 1000 幅 28×28 大小的二维灰度图像。

在职AI算法工程师,擅长计算机视觉,YOLO目标检测、分割等,擅长web、pyqt界面可视化,好内容持续更新中,来这里跟大家一起学习,共同进步 1369

Authn & Authz for Microservice: 04 — Local Demo Guide

This demo showcases a PoC for an identity and access management system using Keycloak (IdP), Kong (PEP), OPA (PDP), and a banking API service. The quickstart guide explains how to run the demo locally via a script that executes 10 steps testing authenticat

PhilZhou的专栏 183

霍尼韦尔SoftMaster使用说明

霍尼韦尔SoftMaster使用说明

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

Awesome Claude Code终极指南:快速掌握AI助手资源宝库

你是否在寻找提升Claude Code工作效率的终极解决方案?是否被海量的AI工具淹没而不知从何入手?Awesome Claude Code项目正是你需要的答案!这个精心策划的资源集合汇集了Claude Code生态系统中最优秀的技能、钩子、斜杠命令、代理编排器和插件,帮助开发者快速找到最适合的工具,大幅提升开发效率。 ## 项目核心价值:为什么你需要Awesome Claude Code?

gitblog_01067的博客 316

最新的sdio 4.0 spec 协议标准 带书签 高清完整PDF版

Part-E1-SDIO-Specification-Ver4.00-Final-120220.pdf:最新的sdio 4.0 spec 协议标准 带书签 高清完整PDF版,包括SDIO 接口的外设,SDIO 接口官方协议等。

PHP - PHP-FPM - 学习/实践

本文摘要: 文章主要探讨了Nginx+PHP-FPM的运行机制、配置优化与实践应用。首先介绍了PHP-FPM作为PHP FastCGI进程管理器的工作原理,对比了CGI与FastCGI的区别,强调FastCGI通过主进程管理worker进程提升性能。详细解析了php-fpm.conf配置文件结构及核心参数,包括进程管理、日志设置等。同时展示了Nginx与PHP-FPM的协同配置,特别是虚拟主机多站点部署。文中还讨论了PHP-FPM的进程模型(master+worker)、平滑重启方法,以及常驻内存特性与Sw

"代码"的日常搬运 2293

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

EBS年底定义sequence以及assign的相关操作

每到年底的时候,我们公司都需要进行sequence的定义以及assign。针对这次的操作,特记录下来以备以后查看。 Sequence定义的画面如下所示: 路径如下所示:GL –> Setup –> Finalcials –> Sequences –> Document –> Define。 Sequence的定义是比较简单的,因为需要...

weixin_30298497的博客 269

oracle ebs csi,oracle ebs Order Management Tables.

Enteredoe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular recordsoe_price_adjustments When discount gets appliedoe_order_price_attribs If line has price attri...

weixin_35648264的博客 374

销售订单头表的订单编号生产规则

销售订单头表是 ont.oe_order_headers_all . order number 是系統設定產生的流水號碼,設定 order mangager respobibility---》setup--> document-->define Header_id 是用序列 OE_ORDER_HEADERS_S產生的, Line_id 是用序列  OE_ORDER_LINES_S產生的,

EBS学习杂记 4937
上一篇: 做到这六点,你就是公司和团队的宝!
下一篇: Purchasing Useful Scriptes .
cuixie7913
博客等级 码龄11年 10粉丝 0原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值