API tools faq
paste
Advertisement
Pandaaaa906

Untitled

Pandaaaa906
Jan 7th, 2022
381
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.23 KB | None | 0 0
raw download clone embed print report
  1.  
  2. with disp as (
  3.  
  4. select
  5.  
  6.  
  7.  
  8. case when (count(*) over(PARTITION by disp_249."cDLCode") != count(*) over(PARTITION by disp_253."cDLCode") ) then '已补单但条目数不一致' else null end "条目数状态",
  9.  
  10.  
  11.  
  12. case when Inventory_249."cInvCCode" not like '0304%' and (disp_249."cBatch" != COALESCE(disp_253."cBatch", '')) then '已补单但批次不一致' else null end "批次状态",
  13.  
  14.  
  15.  
  16.  
  17.  
  18. case when (disp_249."iSum" :: FLOAT != COALESCE(disp_253."iSum" :: float, 0) ) then '已补单但原币含税合计不一致' else null end "原币含税合计状态",
  19.  
  20.  
  21.  
  22. case when (disp_249."iTaxUnitPrice":: FLOAT != COALESCE(disp_253."iTaxUnitPrice" :: FLOAT, 0)) then '已补单但原币含税单价不一致' else null end "原币含税单价状态",
  23.  
  24.  
  25.  
  26. case when (disp_249."cCusCode") != COALESCE(disp_253."cCusCode", '') then '已补单但客户名称不一致' else null end "客户名称状态",
  27.  
  28.  
  29.  
  30. case when disp_253."AutoID" is null then '缺发货单' else null end "发货单状态",
  31.  
  32.  
  33.  
  34. case when (disp_249."iQuantity" != COALESCE(disp_253."iQuantity", '')) then '已补单但数量不一致' else null end "数量状态",
  35.  
  36.  
  37.  
  38. case when disp_249."dverifydate" !='2021-11-07' and (disp_249."dverifydate" != COALESCE(disp_253."dverifydate", '')) then '已补单但审核日期不一致' else null end "审核日期状态",
  39.  
  40.  
  41.  
  42. case when disp_253."dDate"!='2021-11-07' and (disp_249."dDate" != COALESCE(disp_253."dDate", '')) then '已补单但单据日期不一致' else null end "单据日期状态",
  43.  
  44. disp_249."dDate" "006单据日期",
  45.  
  46. disp_253."dDate" "009单据日期",
  47.  
  48. disp_249.dverifydate "006dverifydate",
  49.  
  50. disp_253.dverifydate "009dverifydate",
  51.  
  52. disp_249."AutoID" "006发货单标识",
  53.  
  54. so_249."iSOsID" "006销售单标识",
  55.  
  56. disp_253."AutoID" "009发货单标识",
  57.  
  58. so_253."iSOsID" "009销售单标识",
  59.  
  60. disp_249."cBatch" "006发货单批次",
  61.  
  62. disp_253."cBatch" "009发货单批次",
  63.  
  64. disp_249."iQuantity" "006发货数量",
  65. disp_253."iQuantity" "009发货数量",
  66. disp_253."dverifydate" "009审核日期",
  67. disp_249."cCusName" "006客户名称",
  68. disp_253."cCusName" "009客户名称",
  69. disp_249."iSum" "006原币含税合计",
  70.  
  71.  
  72.  
  73. disp_253."iSum" "009原币含税合计",
  74.  
  75.  
  76.  
  77.  
  78.  
  79. disp_249."iTaxUnitPrice" "006原币含税单价",
  80.  
  81.  
  82.  
  83. disp_253."iTaxUnitPrice" "009原币含税单价",
  84.  
  85. sales."cUser_Name" cUser_Name,
  86.  
  87.  
  88.  
  89.  
  90.  
  91. Inventory_249."cInvCCode",
  92.  
  93.  
  94.  
  95.  
  96.  
  97. count(*) over(PARTITION by disp_249."cDLCode") "006发货条数",
  98.  
  99.  
  100.  
  101. count(*) over(PARTITION by disp_253."cDLCode") "009发货条数",
  102.  
  103.  
  104.  
  105. disp_249.*
  106.  
  107.  
  108.  
  109. from fdw_u8db.disp_249 disp_249
  110.  
  111.  
  112.  
  113.  
  114.  
  115. left join fdw_u8db.so_249 so_249
  116. on so_249."iSOsID" = disp_249."iSOsID"
  117.  
  118.  
  119.  
  120.  
  121.  
  122. left join fdw_u8db."Inventory_249" Inventory_249
  123.  
  124. on so_249."cInvCode" = Inventory_249."cInvCode"
  125.  
  126.  
  127.  
  128. left join fdw_u8db."UA_User" sales
  129.  
  130. on sales."cUser_Id" = so_249."cMainPersonCode"
  131.  
  132.  
  133.  
  134. left join fdw_u8db.so_253
  135.  
  136. on(
  137.  
  138. so_249."cSOCode" = so_253."cSOCode"
  139.  
  140. and
  141.  
  142. so_249."cInvCode" = so_253."cInvCode"
  143.  
  144. and
  145.  
  146. COALESCE(so_249."cFree1", '') = COALESCE(so_253."cFree1", '')
  147.  
  148. and
  149.  
  150. so_249."iQuantity"::FLOAT = so_253."iQuantity"::FLOAT
  151.  
  152. )
  153.  
  154.  
  155.  
  156. left join fdw_u8db.disp_253
  157.  
  158.  
  159.  
  160. on (
  161. so_253."iSOsID" = disp_253."iSOsID"
  162. and disp_249."cDLCode" = disp_253."cDLCode"
  163. )
  164.  
  165. ORDER BY disp_249."cDLCode"
  166.  
  167. )
  168.  
  169.  
  170.  
  171. SELECT
  172.  
  173. disp."cInvCode",
  174.  
  175.  
  176. COALESCE(
  177.  
  178. max(case when pos_sum."cInvCode" != disp."cInvCode" then '货号不一致,缺生产' else null end) over (PARTITION by disp."cDLCode"),
  179. max(case when disp."009审核日期" is null and pos_sum."iquantity" :: float < disp."006发货数量"::float then '库存数量不足,不能发货' else null end) over (PARTITION by disp."cDLCode"),
  180. max (disp."发货单状态") over (PARTITION by disp."cDLCode"),
  181. disp."条目数状态",
  182. max(disp."原币含税合计状态") over (PARTITION by disp."cDLCode"),
  183. max(disp."原币含税单价状态") over (PARTITION by disp."cDLCode"),
  184.  
  185. max(disp."单据日期状态") over (PARTITION by disp."cDLCode"),
  186. disp."客户名称状态",
  187. max(disp."批次状态") over (PARTITION by disp."cDLCode"),
  188. max(disp."数量状态") over (PARTITION by disp."cDLCode"),
  189. case when disp."009审核日期" is null then '客服已做单, 数据无误 ,缺审核' else null end,
  190. max(disp."审核日期状态") over (PARTITION by disp."cDLCode"),
  191. '正常'
  192. ) 状态,
  193.  
  194.  
  195.  
  196.  
  197.  
  198.  
  199.  
  200. disp."006单据日期",
  201.  
  202. disp."009单据日期",
  203.  
  204. disp."单据日期状态",
  205.  
  206.  
  207.  
  208.  
  209.  
  210. disp."006dverifydate",
  211.  
  212. disp."009dverifydate",
  213.  
  214. disp."审核日期状态",
  215.  
  216.  
  217.  
  218.  
  219.  
  220. disp."006发货条数",
  221.  
  222. disp."009发货条数",
  223.  
  224. disp."条目数状态",
  225.  
  226.  
  227.  
  228. disp."006原币含税合计",
  229.  
  230. disp."009原币含税合计",
  231.  
  232. disp."原币含税合计状态",
  233.  
  234.  
  235.  
  236.  
  237.  
  238. disp."006原币含税单价",
  239.  
  240. disp."009原币含税单价",
  241.  
  242. disp."原币含税单价状态",
  243.  
  244.  
  245.  
  246.  
  247.  
  248. disp."006发货数量",
  249.  
  250. disp."009发货数量",
  251.  
  252. disp."数量状态",
  253.  
  254.  
  255.  
  256. disp."006发货单批次",
  257.  
  258. disp."009发货单批次",
  259.  
  260. disp."批次状态",
  261.  
  262.  
  263.  
  264.  
  265.  
  266. disp."006客户名称",
  267.  
  268. disp."009客户名称",
  269.  
  270. disp."客户名称状态",
  271.  
  272.  
  273.  
  274. case when pos_sum."cInvCode" is null then '缺生产' else null end "生产状态",
  275.  
  276. case when pos_sum."iquantity" :: float < disp."009发货数量" :: FLOAT then '库存数量不足,不能发货' else null end "发货状态",
  277.  
  278. case when disp."009审核日期" is null then '缺审核' else null end "审核状态",
  279.  
  280.  
  281.  
  282. pos_sum."iquantity" :: FLOAT 现有库存量,
  283.  
  284.  
  285.  
  286.  
  287.  
  288.  
  289.  
  290. disp."DLID" ID,
  291.  
  292.  
  293.  
  294.  
  295.  
  296.  
  297.  
  298.  
  299.  
  300.  
  301.  
  302. disp."cDLCode" 发货退货单号,
  303.  
  304.  
  305.  
  306. disp."cInvCode" "货号",
  307.  
  308.  
  309.  
  310. disp."cCusName" 客户名称,
  311.  
  312.  
  313.  
  314. disp."iQuantity" 数量,
  315.  
  316.  
  317.  
  318. disp."iTaxUnitPrice" 原币含税单价,
  319.  
  320.  
  321.  
  322. disp."iSum"原币价税合计,
  323.  
  324.  
  325.  
  326. disp."cBatch" 批号,
  327.  
  328. disp."cuser_name" SO业务人员,
  329.  
  330. disp."cMaker" 制单人,
  331.  
  332.  
  333.  
  334. disp."iBatch" 批次,
  335.  
  336.  
  337.  
  338. disp."cPosition" 货位,
  339.  
  340.  
  341.  
  342.  
  343.  
  344.  
  345.  
  346. disp."cVouchType" 单据类型编码,
  347.  
  348.  
  349.  
  350. disp."cBusType" 业务类型,
  351.  
  352.  
  353.  
  354. disp."dDate" 单据日期 ,
  355.  
  356.  
  357.  
  358. disp."cRdCode" 收发类别编码 ,
  359.  
  360.  
  361.  
  362. disp."cDepCode" 部门编码,
  363.  
  364.  
  365.  
  366. disp."cPersonCode" 业务员编码,
  367.  
  368.  
  369.  
  370. disp."cSoCode" 销售订单号,
  371.  
  372.  
  373.  
  374. disp."cSBVCode" 销售发票号,
  375.  
  376.  
  377.  
  378. disp."cCusCode" 客户编码 ,
  379.  
  380.  
  381.  
  382. disp."ccusperson" 客户联系人,
  383.  
  384.  
  385.  
  386. disp."cShipAddress" 发往地址,
  387.  
  388.  
  389.  
  390. disp."cexch_name" 币种名称,
  391.  
  392.  
  393.  
  394. disp."disp_m_iTaxRate" 表头税率,
  395.  
  396.  
  397.  
  398. disp."disp_m_cMemo" 表头备注,
  399.  
  400.  
  401.  
  402.  
  403.  
  404.  
  405.  
  406. disp."dcreatesystime" 制单时间,
  407.  
  408.  
  409.  
  410. disp."cVerifier" 审核人,
  411.  
  412.  
  413.  
  414. disp."dverifydate" 审核日期,
  415.  
  416.  
  417.  
  418. disp."cmodifier" 修改人,
  419.  
  420.  
  421.  
  422. disp."dmoddate" 修改日期,
  423.  
  424.  
  425.  
  426. disp."cAccounter" 记账人 ,
  427.  
  428.  
  429.  
  430. disp."cinvoicecompany" 开票单位编码,
  431.  
  432.  
  433.  
  434.  
  435.  
  436.  
  437.  
  438. disp."AutoID" 发货退货单子表标识,
  439.  
  440.  
  441.  
  442. disp."cWhCode" 仓库编码 ,
  443.  
  444.  
  445.  
  446. disp."cInvCode" 存货编码,
  447.  
  448.  
  449.  
  450. disp."cInvName" 存货名称 ,
  451.  
  452.  
  453.  
  454. disp."disp_d_iTaxRate" 表体税率,
  455.  
  456.  
  457.  
  458.  
  459.  
  460. disp."cFree1" 规格,
  461.  
  462.  
  463.  
  464.  
  465.  
  466.  
  467.  
  468. disp."disp_d_cMemo" 表体备注,
  469.  
  470.  
  471.  
  472. disp."iSOsID" 销售订单子表标识 ,
  473.  
  474.  
  475.  
  476. disp."iDLsID" "发货退货单子表标识2",
  477.  
  478.  
  479.  
  480. disp."cSoCode" 表体销售订单号 ,
  481.  
  482.  
  483.  
  484. disp."cordercode" 订单号,
  485.  
  486.  
  487.  
  488. disp."cDefine29" 跨表备注,
  489.  
  490.  
  491.  
  492. disp."cSCloser" 关闭人,
  493.  
  494.  
  495.  
  496. disp."cbaccounter" 表体记账人,
  497.  
  498.  
  499.  
  500. disp."cdemandid" 需求跟踪id
  501.  
  502.  
  503.  
  504.  
  505.  
  506. from disp
  507.  
  508.  
  509.  
  510.  
  511.  
  512.  
  513.  
  514.  
  515.  
  516.  
  517.  
  518. left join (
  519.  
  520.  
  521.  
  522.  
  523.  
  524.  
  525.  
  526. select
  527.  
  528.  
  529.  
  530. InvPositionSum_253."cInvCode",
  531.  
  532.  
  533.  
  534. case when Inventory_249."cInvCCode" like '0304%' then '' else InvPositionSum_253."cBatch" end "cBatch",
  535.  
  536.  
  537.  
  538. sum(InvPositionSum_253."iQuantity" :: FLOAT) iQuantity
  539.  
  540.  
  541.  
  542.  
  543.  
  544.  
  545.  
  546. from fdw_u8db."InvPositionSum_253" InvPositionSum_253
  547.  
  548.  
  549.  
  550.  
  551.  
  552.  
  553.  
  554.  
  555.  
  556.  
  557.  
  558. left join fdw_u8db."Inventory_249" Inventory_249
  559.  
  560.  
  561.  
  562. on InvPositionSum_253."cInvCode" = Inventory_249."cInvCode"
  563.  
  564.  
  565.  
  566.  
  567.  
  568.  
  569.  
  570. group by (InvPositionSum_253."cInvCode"), case when Inventory_249."cInvCCode" like '0304%' then '' else InvPositionSum_253."cBatch" end
  571.  
  572.  
  573.  
  574.  
  575.  
  576.  
  577.  
  578. )pos_sum
  579.  
  580.  
  581.  
  582.  
  583.  
  584.  
  585.  
  586. on disp."cInvCode" = pos_sum."cInvCode"
  587.  
  588.  
  589.  
  590. and case when disp."cInvCCode" like '0304%' then '' else disp."cBatch" end = pos_sum."cBatch"
  591.  
  592.  
  593.  
  594.  
  595.  
  596.  
  597.  
  598. ORDER BY disp."cDLCode"
  599.  
  600.  
  601.  
  602.  
  603.  
  604.  
  605.  
  606.  
  607.  
  608.  
  609.  
  610.  
  611.  
  612.  
  613.  
  614.  
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!