ERP系统常用SQL集锦

warzoo · 发表于 2007/12/29 20:20:29

非常感谢各位

sywks · 发表于 2007/12/30 08:47:13

:victory: :victory:

zsj57785888 · 发表于 2008/4/10 08:35:26

樓主，對你94樓發表的配置文件，能否提示一下，如何查職責的配置文件信息呢。這個我目前最想要。

wenstin · 发表于 2008/4/13 14:05:58

经典之作啊

wing365 · 发表于 2008/4/13 15:12:08

已全部收藏，很好的资料，非常感谢楼主。

初学有所成 · 发表于 2008/4/18 10:52:52

对我这么一位初学者，这些都是宝贵资料呀。

mustek · 发表于 2008/12/8 15:39:43

高手，领教了。学习中。

iamlomen · 发表于 2008/12/25 21:09:39

看了這么多，偶也是新人，不過還好找到這么好的論壇，心裡終於有底了。。嘿嘿。我也献丑，发下刚完成的ALERT的程式，BOM与WIP工单用料不一致警示。代码可能太过累赘，还请见谅。
SELECT a.org, a.job_no, a.primary_item_no, a.item_no, a.item_id,
   a.primary_item_id,
   (SELECT COUNT (*)
      FROM bom_bill_of_materials bom,
            bom_inventory_components bic
      WHERE bic.bill_sequence_id = bom.bill_sequence_id
         AND bom.assembly_item_id = a.primary_item_id
         AND bic.component_item_id = a.item_id) bom_count,
   (SELECT COUNT (*)
      FROM wip_entities we, wip_requirement_operations wro
      WHERE we.wip_entity_id = wro.wip_entity_id
         AND we.wip_entity_name = a.job_no
         AND we.primary_item_id = a.primary_item_id
         AND wro.inventory_item_id = a.item_id) wip_count,
   TO_CHAR (SYSDATE-1, 'yyyy-mm-dd') date1
--into &org , &job_no ,&primary_item_no, &item_no , &item_id , &primary_item_id, &bom_count , &wip_count ,&date1
FROM ((SELECT DISTINCT DECODE (wro.organization_id,
                              47, 'JPD',
                              48, 'PPD',
                              49, 'SYS',
                              95, 'PCD'
                              ) org,
                     we.wip_entity_name job_no,
                     msi1.segment1 primary_item_no,
   msi.segment1 item_no,
                     wro.inventory_item_id item_id,
                     we.primary_item_id primary_item_id
                  FROM wip_entities we,
                     wip_requirement_operations wro,
                     wip_discrete_jobs wdj,
                     mtl_system_items_b msi,
                     mtl_system_items_b msi1
               WHERE we.wip_entity_id = wdj.wip_entity_id
                  AND we.wip_entity_id = wro.wip_entity_id
                  AND wdj.job_type = 1
                  AND wdj.status_type IN (12)
                  AND we.organization_id = wro.organization_id
                  AND msi.organization_id = wro.organization_id
                  AND msi.inventory_item_id = wro.inventory_item_id
                  AND msi1.organization_id = wro.organization_id
                  AND msi1.inventory_item_id = we.primary_item_id
                  AND wro.wip_supply_type = 1
   AND substr(we.wip_entity_name,1,1) not like 'P'
                  AND TRUNC (wdj.last_update_date) = TRUNC (SYSDATE) - 1
                  AND ASCII (SUBSTR (msi1.segment1, 1, 1)) BETWEEN ASCII
                                                                        ('A')
                                                               AND ASCII
                                                                        ('Z'))
      UNION
      (SELECT DISTINCT DECODE (wro.organization_id,
                              47, 'JPD',
                              48, 'PPD',
                              49, 'SYS',
                              95, 'PCD'
                              ) org,
                     we.wip_entity_name job_no,
                     msii2.segment1 primary_item_no,
                     msii.segment1 item_no,
   bic.component_item_id item_id,
                     we.primary_item_id primary_item_id
                  FROM wip_entities we,
                     wip_requirement_operations wro,
                     wip_discrete_jobs wdj,
                     bom_bill_of_materials bom,
                     bom_inventory_components bic,
                     mtl_system_items msii,
                     mtl_system_items msii2
               WHERE we.wip_entity_id = wro.wip_entity_id
                  AND wdj.wip_entity_id = we.wip_entity_id
                  AND TRUNC (wdj.last_update_date) = TRUNC (SYSDATE) - 1
                  AND wdj.job_type = 1
                  AND wdj.status_type IN (12)
                  AND bom.assembly_item_id = we.primary_item_id
                  AND bic.bill_sequence_id = bom.bill_sequence_id
                  AND we.organization_id = msii.organization_id
                  AND msii.inventory_item_id = bic.component_item_id
                  AND TO_CHAR (NVL (bic.disable_date, SYSDATE), 'yyyymmdd') >
                                          TO_CHAR (SYSDATE - 1, 'YYYYMMDD')
                  AND msii2.organization_id = we.organization_id
                  AND msii2.inventory_item_id = we.primary_item_id
   AND substr(we.wip_entity_name,1,1) not like 'P'
                  AND ASCII (SUBSTR (msii2.segment1, 1, 1))
                        BETWEEN ASCII ('A')
                              AND ASCII ('Z'))) a
WHERE (SELECT COUNT (*)
      FROM bom_bill_of_materials bom, bom_inventory_components bic
      WHERE bic.bill_sequence_id = bom.bill_sequence_id
         AND bom.assembly_item_id = a.primary_item_id
         AND bic.component_item_id = a.item_id) <= 0
OR (SELECT COUNT (*)
      FROM wip_entities we, wip_requirement_operations wro
      WHERE we.wip_entity_id = wro.wip_entity_id
         AND we.wip_entity_name = a.job_no
         AND we.primary_item_id = a.primary_item_id
         AND wro.inventory_item_id = a.item_id) <= 0

iamlomen · 发表于 2008/12/25 21:11:12

续：以上只是与一阶BOM的比较。。

lois80 · 发表于 2009/1/8 14:42:27

强烈支持一下

shoyo2007 · 发表于 2009/1/19 14:54:03

十分感谢老大～～不过如果能做成下载就更好了...一个一个复制ing

arec62 · 发表于 2009/2/8 18:33:40

thanks! good!

tiantom · 发表于 2009/2/14 10:22:51

不习惯！

arec62 · 发表于 2009/6/4 15:27:08

谢谢楼主的分享

arec62 · 发表于 2009/6/8 20:24:23

谢谢楼主的分享

qinghai_527 · 发表于 2009/9/7 14:04:23

超级顶啊！！
不顶对不起自己啊！！

font · 发表于 2009/9/11 12:51:18

不错，感谢！

lhj01 · 发表于 2009/9/12 08:52:23

学习中，感谢

joetsu · 发表于 2010/5/14 15:04:06

高手 ,厉害

xiaozhi0306 · 发表于 2010/6/7 09:13:26

提示: 作者被禁止或删除内容自动屏蔽

帐号		自动登录	找回密码
密码			注册

社区人物壹佰网重点话题	分享知识原创连载活动正式启动已有673人参加查看全部会员靓照旅行分享文学连载	壹佰读书会开始招收成员了
招聘求职壹佰网信息化选型平台	倾听用户的心声纵横四海谈IT如何提升企业管理管理小说连载《苏州桥》	如何获取努力值
原创连载－我来管管看 ERP分享+	推荐连载：[已结帖][分享知识原创连载]财务供应链项目新手实施手记：记录自己正	入门学习-致所有初学者

xiaozhi0306 xiaozhi0306 当前离线积分 428 IP卡狗仔卡	发表于 2010/6/7 09:13:26 \| 显示全部楼层提示: 作者被禁止或删除内容自动屏蔽
xiaozhi0306 xiaozhi0306 当前离线积分 428 IP卡狗仔卡
	回复支持反对使用道具举报显身卡

ERP系统常用SQL集锦

對94樓感想

浏览过的版块