代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
OracleEBS
】
SQL写出带扩展数量的BOM多级清单
作者:
专注Oracle EBS
/ 发布于
2012/11/29
/
1067
只用SQL的原因可能为: 因为没有权限去修改和定义程序。 我们唯一能用的手段就是Discover Report,因此需要用SQL来实现多级BOM展开。 代码 Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 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 = 207 AND bom1.alternate_bom_designator IS NULL) boms CONNECT BY PRIOR boms.organization_id = boms.organization_id AND PRIOR boms.component_item_id = boms.assembly_item_id ) SELECT t1.organization_id, t1.code_chain, t1.assembly_number, t1.assembly_description, t1.bom_level, t1.component_number, t1.component_description, t1.uom, t1.ind_component_number, 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;
试试其它关键字
BOM多级清单
同语言下
.
设置窗体的位置
.
初始化操作
.
在FORMS调用WEB页面
.
判断数据块的项是否为空
.
EBS 循环处理块记录
.
Oracle Form中调用并发请求生成报表并输出为PDF的方法
.
添加并发程序到指定职责API
.
EBS请求查询输出
.
删除 AP 发票相关脚本
.
ebs安全性
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
专注Oracle EBS
贡献的其它代码
(
66
)
.
ERP系统里的BOM展开函数
.
EBS查找报表或菜单所属职责
.
查询用户客户化的文件配置
.
SQL写出带扩展数量的BOM多级清单
.
oracle开发常用LOV
.
Oracle Patch 版本的查询
.
科目余额SQL
.
根据报表文件名称关键字查找报表的执行文件名称等信息
.
查找在标准请求组里提交的报表所在的职责
.
查找在菜单里提交的报表所在职责
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3