git.roojs.org Git - Pman.Xtuple/blob - pgsql/x-dragon-invdetail-bydate.sql

   1
   2
   3 -- create an index on location so it's fast..
   4
   5
   6
   7 -- returns the exact quantity of stock at the invdetail_id time..
   8
   9
  10
  11
  12
  13 CREATE OR REPLACE FUNCTION invdetail_bydate(i_id int)
  14   RETURNS  numeric(18,6)  AS
  15 $BODY$
  16 DECLARE
  17   v_return numeric(18,6) ;
  18   BEGIN
  19     v_return := 0;
  20     SELECT invdetail_at_id(i_id) into v_return;
  21
  22
  23     RETURN v_return;
  24 END;
  25 $BODY$
  26   LANGUAGE plpgsql STABLE
  27   COST 100;
  28 ALTER FUNCTION  invdetail_bydate(int)
  29   OWNER TO admin;
  30
  31
  32 CREATE OR REPLACE FUNCTION invdetail_at_id(i_id int)
  33   RETURNS  numeric(18,6)  AS
  34 $BODY$
  35 DECLARE
  36   v_itemsite_id INTEGER;
  37   v_location_id INTEGER;
  38   v_transdate timestamp with time zone;
  39   v_qty numeric(18,6) ;
  40   v_return numeric(18,6) ;
  41 BEGIN
  42     v_return := 0;
  43
  44
  45
  46
  47    SELECT
  48      invhist_itemsite_id,
  49      invdetail_location_id,
  50      invhist_transdate,
  51      invdetail_qty
  52         INTO
  53         v_itemsite_id,
  54         v_location_id ,
  55         v_transdate,
  56         v_qty
  57           FROM invdetail LEFT JOIN invhist ON
  58             invdetail_invhist_id = invhist_id
  59          WHERE
  60             invdetail_id = i_id
  61           LIMIT 1;
  62
  63
  64 -- # when transactions are the same day, we only want to include the ones with lower ids..
  65
  66
  67     SELECT
  68             COALESCE(SUM( invdetail_qty), 0) + v_qty INTO v_return
  69
  70          FROM invdetail LEFT JOIN invhist ON
  71             invdetail_invhist_id = invhist_id
  72         WHERE
  73             invdetail_location_id = v_location_id
  74             AND
  75             invhist_itemsite_id = v_itemsite_id
  76             AND   (
  77                 invhist_transdate <  v_transdate
  78                 OR
  79                 (invhist_transdate =  v_transdate AND invdetail_id < i_id)
  80             )  ;
  81
  82     IF (v_return IS NULL) THEN
  83         v_return = 0;
  84     END IF;
  85
  86
  87     -- UPDATE invdetail SET invdetail_bydate_qty_after = v_return WHERE invdetail_id = i_id;
  88
  89
  90   RETURN v_return;
  91 END;
  92 $BODY$
  93   LANGUAGE plpgsql STABLE
  94   COST 100;
  95 ALTER FUNCTION  invdetail_byid(int)
  96   OWNER TO admin;
  97
  98
  99
 100
 101
 102 -- DATE, location_id, itemsite_id
 103 CREATE OR REPLACE FUNCTION invdetail_atdate(i_transdate timestamp with time zone, i_location_id int, i_itemsite_id int)
 104   RETURNS  numeric(18,6)  AS
 105 $BODY$
 106 DECLARE
 107
 108     v_return numeric(18,6) ;
 109 BEGIN
 110     v_return := 0;
 111
 112
 113     SELECT   COALESCE(SUM( invdetail_qty), 0)
 114         INTO
 115             v_return
 116         FROM
 117             invdetailview
 118         WHERE
 119
 120                 invhist_itemsite_id = i_itemsite_id
 121             AND
 122                 invhist_transdate <  i_transdate
 123             AND
 124                 invfifo_void = 0
 125
 126             AND
 127                invdetail_location_id = i_location_id;
 128
 129
 130     RETURN COALESCE(v_return ,0);
 131
 132
 133 END;
 134 $BODY$
 135   LANGUAGE plpgsql STABLE
 136   COST 100;
 137 ALTER FUNCTION  invdetail_atdate(timestamp with time zone, int, int)
 138   OWNER TO admin;
 139
 140
 141
 142 -- DATE, location_id, itemsite_id
 143 CREATE OR REPLACE FUNCTION invdetail_cost_atdate(timestamp with time zone, int, int)
 144   RETURNS  numeric(18,6)  AS
 145 $BODY$
 146 DECLARE
 147     i_transdate   ALIAS FOR $1;
 148     i_location_id   ALIAS FOR $2;
 149     i_itemsite_id   ALIAS FOR $3;
 150     v_return numeric(18,6) ;
 151 BEGIN
 152     v_return := 0;
 153
 154
 155     SELECT
 156             COALESCE(SUM( invfifo_landedunitcost * invdetail_qty)  , 0)
 157         INTO
 158             v_return
 159         FROM
 160             invdetailview
 161         WHERE
 162             invhist_itemsite_id = i_itemsite_id
 163             AND
 164             invhist_transdate <  i_transdate
 165             AND
 166             invdetail_location_id = i_location_id
 167             AND
 168             invfifo_void = 0;
 169
 170     RETURN COALESCE(v_return ,0);
 171
 172
 173 END;
 174 $BODY$
 175   LANGUAGE plpgsql STABLE
 176   COST 100;
 177 ALTER FUNCTION  invdetail_cost_atdate(timestamp with time zone, int, int)
 178   OWNER TO admin;
 179
 180
 181
 182
 183
 184
 185
 186
 187
 188
 189
 190
 191 -- testing vlogic. on 2012-08-31 (124)
 192
 193 -- select invdetail_location_atdate('2012-08-31', 124,1);
 194 --itemsite 1231 has 93?
 195 --
 196 --
 197 --(SELECT
 198 --                    invhist_itemsite_id,
 199 --                    sum(invdetail_qty) as invdetail_total
 200 --                FROM
 201 --                    invdetailview
 202 --                WHERE
 203 --                    invhist_transdate <  '2012-08-31'
 204 --                AND
 205 --                    invdetail_location_id = 124
 206 --                GROUP BY
 207 --                    invhist_itemsite_id
 208 --                HAVING
 209 --                    SUM(invdetail_qty) > 0
 210 --            ) dsum;
 211 --
 212
 213
 214 CREATE OR REPLACE FUNCTION invdetail_location_atdate(timestamp with time zone, int, int)
 215   RETURNS  numeric(18,6)  AS
 216 $BODY$
 217 DECLARE
 218     i_transdate   ALIAS FOR $1;
 219     i_location_id   ALIAS FOR $2;
 220     v_posneg ALIAS FOR $3;
 221     v_return numeric(18,6) ;
 222 BEGIN
 223     v_return := 0;
 224
 225
 226
 227     IF v_posneg > 0 THEN
 228
 229         SELECT SUM(invdetail_total)
 230             INTO v_return
 231         FROM
 232             (SELECT
 233                     invhist_itemsite_id,
 234                     sum(invdetail_qty) as invdetail_total
 235                 FROM
 236                     invdetailview
 237                 WHERE
 238                     invhist_transdate <  i_transdate
 239                 AND
 240                     invdetail_location_id = i_location_id
 241                 AND
 242                     invfifo_void = 0
 243                 GROUP BY
 244                     invhist_itemsite_id
 245                 HAVING
 246                     SUM(invdetail_qty) > 0
 247
 248             ) dsum;
 249
 250
 251         RETURN COALESCE(v_return ,0);
 252     END IF;
 253
 254
 255     SELECT SUM(invdetail_total)
 256             INTO v_return
 257         FROM
 258             (SELECT
 259                     invhist_itemsite_id,
 260                     sum(invdetail_qty) as invdetail_total
 261                 FROM
 262                     invdetailview
 263                 WHERE
 264                     invhist_transdate <  i_transdate
 265                 AND
 266                     invdetail_location_id = i_location_id
 267                 AND
 268                     invfifo_void = 0
 269                 GROUP BY
 270                     invhist_itemsite_id
 271                 HAVING
 272                     SUM(invdetail_qty) < 0
 273             ) dsum;
 274
 275
 276
 277     RETURN COALESCE(v_return ,0);
 278
 279 END;
 280 $BODY$
 281   LANGUAGE plpgsql STABLE
 282   COST 100;
 283 ALTER FUNCTION  invdetail_location_atdate(timestamp with time zone, int, int )
 284   OWNER TO admin;
 285
 286
 287
 288 CREATE OR REPLACE FUNCTION invcost_location_atdate(timestamp with time zone, int)
 289   RETURNS  numeric(18,6)  AS
 290 $BODY$
 291 DECLARE
 292     i_transdate   ALIAS FOR $1;
 293     i_location_id   ALIAS FOR $2;
 294     v_return numeric(18,6) ;
 295 BEGIN
 296     v_return := 0;
 297
 298
 299     SELECT
 300         COALESCE(SUM( invfifo_landedunitcost * invdetail_qty ), 0)
 301         INTO
 302             v_return
 303         FROM
 304             invdetailview
 305         WHERE
 306             invhist_transdate <  i_transdate
 307             AND
 308             invdetail_location_id = i_location_id
 309             AND
 310             invfifo_void = 0;
 311
 312     RETURN COALESCE(v_return ,0);
 313
 314
 315 END;
 316 $BODY$
 317   LANGUAGE plpgsql STABLE
 318   COST 100;
 319 ALTER FUNCTION  invcost_location_atdate(timestamp with time zone, int )
 320   OWNER TO admin;
 321
 322
 323
 324 CREATE OR REPLACE FUNCTION invdetail_cost_location_atdate(timestamp with time zone, int)
 325   RETURNS  numeric(18,6)  AS
 326 $BODY$
 327 DECLARE
 328     i_transdate   ALIAS FOR $1;
 329     i_location_id   ALIAS FOR $2;
 330     v_return numeric(18,6) ;
 331 BEGIN
 332     v_return := 0;
 333
 334
 335     SELECT
 336             COALESCE(SUM( invfifo_totalcost * (invdetail_qty / ABS(invdetail_qty)) ), 0)
 337         INTO
 338             v_return
 339         FROM
 340             invdetailview
 341
 342         WHERE
 343             invhist_transdate <  i_transdate
 344         AND
 345             invdetail_location_id = i_location_id
 346         AND
 347             invfifo_void = 0
 348         ;
 349
 350
 351
 352     RETURN COALESCE(v_return ,0);
 353
 354
 355 END;
 356 $BODY$
 357   LANGUAGE plpgsql STABLE
 358   COST 100;
 359 ALTER FUNCTION  invdetail_cost_location_atdate(timestamp with time zone, int )
 360   OWNER TO admin;
 361
 362
 363
 364
 365 CREATE OR REPLACE FUNCTION invdetail_qty_at_id(int)
 366   RETURNS  numeric(18,6)  AS
 367 $BODY$
 368 DECLARE
 369   i_id ALIAS FOR $1;
 370   v_itemsite_id INTEGER;
 371   v_location_id INTEGER;
 372   v_transdate timestamp with time zone;
 373   v_return numeric(18,6) ;
 374 BEGIN
 375     v_return := 0;
 376
 377     SELECT
 378      invhist_itemsite_id,
 379      invdetail_location_id,
 380      invhist_transdate
 381         INTO
 382         v_itemsite_id,
 383         v_location_id ,
 384         v_transdate
 385           FROM invdetail
 386             LEFT JOIN invhist ON invdetail_invhist_id = invhist_id
 387          WHERE
 388             invdetail_id = i_id
 389           LIMIT 1;
 390
 391     SELECT   COALESCE(SUM( invdetail_qty), 0) INTO v_return
 392          FROM invdetail
 393             LEFT JOIN invhist ON invdetail_invhist_id = invhist_id
 394             LEFT JOIN invfifo ON invdetail_id = invfifo_invdetail_id
 395         WHERE
 396             invdetail_location_id = v_location_id
 397             AND
 398             invhist_itemsite_id = v_itemsite_id
 399             AND
 400             ((invhist_transdate <  v_transdate) OR (invhist_transdate =  v_transdate AND invdetail_id <= i_id))
 401             AND
 402             (invfifo_void IS NULL OR invfifo_void = 0);
 403
 404     IF (v_return IS NULL) THEN
 405         v_return = 0;
 406     END IF;
 407
 408
 409   RETURN v_return;
 410 END;
 411 $BODY$
 412   LANGUAGE plpgsql STABLE
 413   COST 100;
 414 ALTER FUNCTION  invdetail_qty_at_id(int)
 415   OWNER TO admin;
 416
 417
 418
 419
 420 -- DATE, location_id, itemsite_id
 421 CREATE OR REPLACE FUNCTION invdetail_sold_atdate(timestamp with time zone, int, int)
 422   RETURNS  numeric(18,6)  AS
 423 $BODY$
 424 DECLARE
 425     i_transdate   ALIAS FOR $1;
 426     i_location_id   ALIAS FOR $2;
 427     i_itemsite_id   ALIAS FOR $3;
 428     v_return numeric(18,6) ;
 429 BEGIN
 430     v_return := 0;
 431
 432
 433     SELECT   COALESCE(SUM( invdetail_qty), 0)
 434         INTO
 435             v_return
 436         FROM
 437             invdetailview
 438         WHERE
 439
 440                 invhist_itemsite_id = i_itemsite_id
 441             AND
 442                 invhist_transdate::date =  i_transdate::date
 443             AND
 444                 invfifo_void = 0
 445
 446             AND
 447                invdetail_location_id = i_location_id;
 448
 449
 450     RETURN COALESCE(v_return ,0);
 451
 452
 453 END;
 454 $BODY$
 455   LANGUAGE plpgsql STABLE
 456   COST 100;
 457 ALTER FUNCTION  invdetail_sold_atdate(timestamp with time zone, int, int)
 458   OWNER TO admin;
 459
 460
 461
 462 --  location_id, itemsite_id
 463 CREATE OR REPLACE FUNCTION invdetail_sold_after(timestamp with time zone, int, int)
 464   RETURNS  numeric(18,6)  AS
 465 $BODY$
 466 DECLARE
 467     i_transdate   ALIAS FOR $1;
 468     i_location_id   ALIAS FOR $2;
 469     i_itemsite_id   ALIAS FOR $3;
 470     v_return numeric(18,6) ;
 471 BEGIN
 472     v_return := 0;
 473
 474
 475     SELECT   COALESCE(SUM( invdetail_qty), 0)
 476         INTO
 477             v_return
 478         FROM
 479             invdetailview
 480         WHERE
 481                 invhist_itemsite_id = i_itemsite_id
 482             AND
 483                 invhist_transdate::date >  i_transdate::date
 484             AND
 485                 invfifo_void = 0
 486             AND
 487                 invdetail_qty < 0
 488
 489             AND
 490                invdetail_location_id = i_location_id;
 491
 492
 493     RETURN COALESCE(v_return ,0);
 494
 495
 496 END;
 497 $BODY$
 498   LANGUAGE plpgsql STABLE
 499   COST 100;
 500 ALTER FUNCTION  invdetail_sold_after(timestamp with time zone, int, int)
 501   OWNER TO admin;
 502
 503
 504
 505
 506
 507 CREATE OR REPLACE FUNCTION invdetail_balance_byitem( i_itemsite_id INTEGER, i_location_id INTEGER)
 508   RETURNS  NUMERIC(18,6)  AS
 509 $BODY$
 510 DECLARE
 511     v_return NUMERIC(18,6) ;
 512 BEGIN
 513     v_return := 0;
 514
 515     SELECT
 516             COALESCE(SUM(invdetail_qty),0)
 517     INTO
 518             v_return
 519     FROM
 520             invdetail
 521     LEFT JOIN
 522             invhist
 523     ON
 524             invdetail_invhist_id = invhist_id
 525     WHERE
 526             invhist_itemsite_id = i_itemsite_id
 527         AND
 528             invdetail_location_id = i_location_id;
 529
 530     RETURN v_return;
 531
 532
 533 END;
 534 $BODY$
 535   LANGUAGE plpgsql STABLE
 536   COST 100;
 537 ALTER FUNCTION  invdetail_balance_byitem(INTEGER, INTEGER)
 538   OWNER TO admin;