tko_pb

issue 20210923.sql

Sep 22nd, 2021
959
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. select
  3.     issue.material_movement_id,
  4.     issue.material_movement_line_id,
  5.     issue.material_movement_per_size_id,
  6.     issue.from_locator_erp_id,
  7.     issue.to_locator_erp_id,
  8.     issue.po_buyer,
  9.     issue.item_id,
  10.     issue.c_order_id,
  11.     issue.uom,
  12.     issue.size,
  13.     issue.qty_per_size,
  14.     issue.status,
  15.     issue.movement_date,
  16.     issue.warehouse_id,
  17.     issue.is_integrate,
  18.     issue.integration_date,
  19.     coalesce(issue.item_id_source, item_id) as item_id_source
  20. from
  21.     dblink( 'dbname=wmsacc port=5432 host=192.168.51.22 user=dev password=Pass@123',
  22.     'select * from material_out_issue_mv where is_integrate=false and integration_date is null and status_mo=true') issue (material_movement_id char(36),
  23.     material_movement_line_id char(36),
  24.     material_movement_per_size_id char(36),
  25.     from_locator_erp_id integer,
  26.     to_locator_erp_id integer,
  27.     po_buyer varchar(255),
  28.     item_id varchar(255),
  29.     c_order_id varchar(255),
  30.     uom varchar(255),
  31.     size varchar(255),
  32.     qty_per_size numeric,
  33.     status varchar(255),
  34.     movement_date timestamp without time zone,
  35.     warehouse_id numeric,
  36.     is_integrate boolean,
  37.     integration_date timestamp without time zone,
  38.     item_id_source varchar(255),
  39.     status_mo boolean)
  40. where
  41.     trunc(issue.movement_date)>= '2020-04-07'::timestamp
  42.     and not exists (
  43.     select
  44.         1
  45.     from
  46.         PP_Cost_Collector
  47.     where
  48.         Description = issue.material_movement_per_size_id
  49.         and DocStatus = 'CO')
  50. order by
  51.     1 asc,
  52.     2 asc,
  53.     3 asc
RAW Paste Data