API tools faq
paste
Advertisement
Pandaaaa906

Untitled

Pandaaaa906
Jan 12th, 2022
5,961
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.02 KB | None | 0 0
raw download clone embed print report
  1. WITH disp AS (
  2.     SELECT
  3.     CASE
  4.            
  5.         WHEN
  6.             ( COUNT ( * ) OVER ( PARTITION BY disp_249."cDLCode" ) != COUNT ( * ) OVER ( PARTITION BY disp_253."cDLCode" ) ) THEN
  7.                 '已补单但条目数不一致' ELSE NULL
  8.             END "条目数状态",
  9. CASE
  10.    
  11.     WHEN disp_253."AutoID" IS NOT NULL
  12.     AND Inventory_249."cInvCCode" NOT LIKE'0304%'
  13.     AND ( disp_249."cBatch" != COALESCE ( disp_253."cBatch", '' ) ) THEN
  14.     '已补单但批次不一致' ELSE NULL
  15.     END "批次状态",
  16. CASE
  17.    
  18.     WHEN disp_253."AutoID" IS NOT NULL
  19.     AND ( disp_249."iSum" :: FLOAT != COALESCE ( disp_253."iSum" :: FLOAT, 0 ) ) THEN
  20.     '已补单但原币含税合计不一致' ELSE NULL
  21.     END "原币含税合计状态",
  22. CASE
  23.    
  24.     WHEN disp_253."AutoID" IS NOT NULL
  25.     AND ( disp_249."iTaxUnitPrice" :: FLOAT != COALESCE ( disp_253."iTaxUnitPrice" :: FLOAT, 0 ) ) THEN
  26.     '已补单但原币含税单价不一致' ELSE NULL
  27.     END "原币含税单价状态",
  28. CASE
  29.    
  30.     WHEN disp_253."AutoID" IS NOT NULL
  31.     AND ( disp_249."cCusCode" ) != COALESCE ( disp_253."cCusCode", '' ) THEN
  32.     '已补单但客户名称不一致' ELSE NULL
  33.     END "客户名称状态",
  34. CASE
  35.    
  36.     WHEN disp_253."AutoID" IS NULL THEN
  37.     '缺发货单' ELSE NULL
  38.     END "发货单状态",
  39. CASE
  40.    
  41.     WHEN disp_253."AutoID" IS NOT NULL
  42.     AND ( disp_249."iQuantity" != COALESCE ( disp_253."iQuantity", '' ) ) THEN
  43.     '已补单但数量不一致' ELSE NULL
  44.     END "数量状态",
  45. CASE
  46.    
  47.     WHEN disp_253."AutoID" IS NOT NULL
  48.     AND disp_249."dverifydate" != '2021-11-07'
  49.     AND ( disp_249."dverifydate" != COALESCE ( disp_253."dverifydate", '' ) ) THEN
  50.     '已补单但审核日期不一致' ELSE NULL
  51.     END "审核日期状态",
  52. CASE
  53.    
  54.     WHEN disp_253."AutoID" IS NOT NULL
  55.     AND disp_253."dDate" != '2021-11-07'
  56.     AND ( disp_249."dDate" != COALESCE ( disp_253."dDate", '' ) ) THEN
  57.     '已补单但单据日期不一致' ELSE NULL
  58.     END "单据日期状态",
  59.     disp_249."dDate" "006单据日期",
  60.     disp_253."dDate" "009单据日期",
  61.     disp_249.dverifydate "006dverifydate",
  62.     disp_253.dverifydate "009dverifydate",
  63.     disp_249."AutoID" "006发货单标识",
  64.     so_249."iSOsID" "006销售单标识",
  65.     disp_253."AutoID" "009发货单标识",
  66.     so_253."iSOsID" "009销售单标识",
  67.     disp_249."cBatch" "006发货单批次",
  68.     disp_253."cBatch" "009发货单批次",
  69.     disp_249."iQuantity" "006发货数量",
  70.     disp_253."iQuantity" "009发货数量",
  71.     disp_253."dverifydate" "009审核日期",
  72.     disp_249."cCusName" "006客户名称",
  73.     disp_253."cCusName" "009客户名称",
  74.     disp_249."iSum" "006原币含税合计",
  75.     disp_253."iSum" "009原币含税合计",
  76.     disp_249."iTaxUnitPrice" "006原币含税单价",
  77.     disp_253."iTaxUnitPrice" "009原币含税单价",
  78.     sales."cUser_Name" cUser_Name,
  79.     Inventory_249."cInvCCode",
  80.     COUNT ( * ) OVER ( PARTITION BY disp_249."cDLCode" ) "006发货条数",
  81.     COUNT ( * ) OVER ( PARTITION BY disp_253."cDLCode" ) "009发货条数",
  82.     disp_249.*
  83. FROM
  84.     fdw_u8db.disp_249 disp_249
  85.     LEFT JOIN fdw_u8db.so_249 so_249 ON so_249."iSOsID" = disp_249."iSOsID"
  86.     LEFT JOIN fdw_u8db."Inventory_249" Inventory_249 ON so_249."cInvCode" = Inventory_249."cInvCode"
  87.     LEFT JOIN fdw_u8db."UA_User" sales ON sales."cUser_Id" = so_249."cMainPersonCode"
  88.     LEFT JOIN fdw_u8db.so_253 ON (
  89.         so_249."cSOCode" = so_253."cSOCode"
  90.         AND so_249."cInvCode" = so_253."cInvCode"
  91.         AND COALESCE ( so_249."cFree1", '' ) = COALESCE ( so_253."cFree1", '' )
  92.         AND so_249."iQuantity" :: FLOAT = so_253."iQuantity" :: FLOAT
  93.     )
  94.     LEFT JOIN fdw_u8db.disp_253 ON ( so_253."iSOsID" = disp_253."iSOsID" AND disp_249."cDLCode" = disp_253."cDLCode" )
  95. ORDER BY
  96.     disp_249."cDLCode"
  97.     )
  98. SELECT
  99.     disp."cInvCode",
  100.     COALESCE (
  101.         MAX ( CASE WHEN pos_sum."cInvCode" IS NULL THEN '缺生产' ELSE NULL END ) OVER ( PARTITION BY disp."cDLCode" ),
  102.         MAX ( CASE WHEN disp."009审核日期" IS NULL AND pos_sum."iquantity" :: FLOAT < disp."006发货数量" :: FLOAT THEN '库存数量不足,不能发货' ELSE NULL END ) OVER ( PARTITION BY disp."cDLCode" ),
  103.         MAX ( disp."发货单状态" ) OVER ( PARTITION BY disp."cDLCode" ),
  104.         disp."条目数状态",
  105.         MAX ( disp."原币含税合计状态" ) OVER ( PARTITION BY disp."cDLCode" ),
  106.         MAX ( disp."原币含税单价状态" ) OVER ( PARTITION BY disp."cDLCode" ),
  107.         MAX ( disp."单据日期状态" ) OVER ( PARTITION BY disp."cDLCode" ),
  108.         disp."客户名称状态",
  109.         MAX ( disp."批次状态" ) OVER ( PARTITION BY disp."cDLCode" ),
  110.         MAX ( disp."数量状态" ) OVER ( PARTITION BY disp."cDLCode" ),
  111.     CASE
  112.            
  113.             WHEN disp."009审核日期" IS NULL THEN
  114.             '客服已做单, 数据无误 ,缺审核' ELSE NULL
  115.         END,
  116.         MAX ( disp."审核日期状态" ) OVER ( PARTITION BY disp."cDLCode" ),
  117.         '正常'
  118.     ) 状态,
  119.     disp."006单据日期",
  120.     disp."009单据日期",
  121.     disp."单据日期状态",
  122.     disp."006dverifydate",
  123.     disp."009dverifydate",
  124.     disp."审核日期状态",
  125.     disp."006发货条数",
  126.     disp."009发货条数",
  127.     disp."条目数状态",
  128.     disp."006原币含税合计",
  129.     disp."009原币含税合计",
  130.     disp."原币含税合计状态",
  131.     disp."006原币含税单价",
  132.     disp."009原币含税单价",
  133.     disp."原币含税单价状态",
  134.     disp."006发货数量",
  135.     disp."009发货数量",
  136.     disp."数量状态",
  137.     disp."006发货单批次",
  138.     disp."009发货单批次",
  139.     disp."批次状态",
  140.     disp."006客户名称",
  141.     disp."009客户名称",
  142.     disp."客户名称状态",
  143. CASE
  144.        
  145.         WHEN pos_sum."cInvCode" IS NULL THEN
  146.         '缺生产' ELSE NULL
  147.     END "生产状态",
  148. CASE
  149.    
  150.     WHEN pos_sum."iquantity" :: FLOAT < disp."009发货数量" :: FLOAT THEN
  151.     '库存数量不足,不能发货' ELSE NULL
  152.     END "发货状态",
  153. CASE
  154.    
  155.     WHEN disp."009审核日期" IS NULL THEN
  156.     '缺审核' ELSE NULL
  157.     END "审核状态",
  158.     pos_sum."iquantity" :: FLOAT 现有库存量,
  159.     disp."DLID" ID,
  160.     disp."cDLCode" 发货退货单号,
  161.     disp."cInvCode" "货号",
  162.     disp."cCusName" 客户名称,
  163.     disp."iQuantity" 数量,
  164.     disp."iTaxUnitPrice" 原币含税单价,
  165.     disp."iSum"原币价税合计,
  166.     disp."cBatch" 批号,
  167.     disp."cuser_name" SO业务人员,
  168.     disp."cMaker" 制单人,
  169.     disp."iBatch" 批次,
  170.     disp."cPosition" 货位,
  171.     disp."cVouchType" 单据类型编码,
  172.     disp."cBusType" 业务类型,
  173.     disp."dDate" 单据日期 ,
  174.     disp."cRdCode" 收发类别编码 ,
  175.     disp."cDepCode" 部门编码,
  176.     disp."cPersonCode" 业务员编码,
  177.     disp."cSoCode" 销售订单号,
  178.     disp."cSBVCode" 销售发票号,
  179.     disp."cCusCode" 客户编码 ,
  180.     disp."ccusperson" 客户联系人,
  181.     disp."cShipAddress" 发往地址,
  182.     disp."cexch_name" 币种名称,
  183.     disp."disp_m_iTaxRate" 表头税率,
  184.     disp."disp_m_cMemo" 表头备注,
  185.     disp."dcreatesystime" 制单时间,
  186.     disp."cVerifier" 审核人,
  187.     disp."dverifydate" 审核日期,
  188.     disp."cmodifier" 修改人,
  189.     disp."dmoddate" 修改日期,
  190.     disp."cAccounter" 记账人 ,
  191.     disp."cinvoicecompany" 开票单位编码,
  192.     disp."AutoID" 发货退货单子表标识,
  193.     disp."cWhCode" 仓库编码 ,
  194.     disp."cInvCode" 存货编码,
  195.     disp."cInvName" 存货名称 ,
  196.     disp."disp_d_iTaxRate" 表体税率,
  197.     disp."cFree1" 规格,
  198.     disp."disp_d_cMemo" 表体备注,
  199.     disp."iSOsID" 销售订单子表标识 ,
  200.     disp."iDLsID" "发货退货单子表标识2",
  201.     disp."cSoCode" 表体销售订单号 ,
  202.     disp."cordercode" 订单号,
  203.     disp."cDefine29" 跨表备注,
  204.     disp."cSCloser" 关闭人,
  205.     disp."cbaccounter" 表体记账人,
  206.     disp."cdemandid" 需求跟踪 ID
  207. FROM
  208.     disp
  209.     LEFT JOIN (
  210.     SELECT
  211.         InvPositionSum_253."cInvCode",
  212.     CASE
  213.            
  214.             WHEN Inventory_249."cInvCCode" LIKE'0304%' THEN
  215.             '' ELSE InvPositionSum_253."cBatch"
  216.         END "cBatch",
  217.     SUM ( InvPositionSum_253."iQuantity" :: FLOAT ) iQuantity
  218. FROM
  219.     fdw_u8db."InvPositionSum_253" InvPositionSum_253
  220.     LEFT JOIN fdw_u8db."Inventory_249" Inventory_249 ON InvPositionSum_253."cInvCode" = Inventory_249."cInvCode"
  221. GROUP BY
  222.     ( InvPositionSum_253."cInvCode" ),
  223. CASE
  224.        
  225.         WHEN Inventory_249."cInvCCode" LIKE'0304%' THEN
  226.         '' ELSE InvPositionSum_253."cBatch"
  227.     END
  228.     ) pos_sum ON disp."cInvCode" = pos_sum."cInvCode"
  229. AND
  230. CASE
  231.        
  232.         WHEN disp."cInvCCode" LIKE'0304%' THEN
  233.         '' ELSE disp."cBatch"
  234.     END = pos_sum."cBatch"
  235. ORDER BY
  236.     disp."cDLCode"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!