Sql Programming: How to Create Aga Ana
Autor: Sara17 • September 17, 2017 • 9,384 Words (38 Pages) • 640 Views
...
--(-) 140915 Miwelk R_G1426959-1 (START)
--(+) 140306 Thualk R_G1358412-1 (START)
/*
AND EXISTS (SELECT 1 AS state
FROM Inventory_Transaction_Hist_Tab a
WHERE a.location_no NOT LIKE ’%INSPECT%’
AND a.contract = t.contract
AND a.part_no = t.part_no
AND a.transaction= ’INVM-IN’
AND a.direction = ’+’
AND a.transaction_id=t.transaction_id);
*/
--(+) 140306 Thualk R_G1358412-1 (END)
--(-) 140915 Miwelk R_G1426959-1 (END)
CURSOR get_minus_with_bay_location(part_no_ IN VARCHAR2, site_ IN VARCHAR2, from_date_ IN VARCHAR2) IS
--(+/-) 140915 Miwelk R_G1426959-1 (START)
--(+/-) 140306 Thualk R_G1358412-1 (START)
SELECT t.quantity , t.transaction_id
--SELECT SUM(t.quantity) quantity --,t.transaction_id
--(+/-) 140306 Thualk R_G1358412-1 (END)
FROM Inventory_Transaction_Hist2 t
WHERE part_no = part_no_
AND contract = site_
AND direction = ’-’
AND location_no IS NULL
AND date_created <= (TO_DATE(from_date_,’DD/MM/YYYY’) + ( 1 - 1/ ( 60*60*24 )));
--(+) 140306 Thualk R_G1358412-1 (START)
/*
AND EXISTS (SELECT 1 AS state
FROM Inventory_Transaction_Hist_Tab a
WHERE a.location_no NOT LIKE ’%INSPECT%’
AND a.contract = t.contract
AND a.part_no = t.part_no
AND a.transaction= ’INVM-IN’
AND a.direction = ’+’
AND a.transaction_id=t.transaction_id);
*/
--(+) 140306 Thualk R_G1358412-1 (END)
--(+/-) 140915 Miwelk R_G1426959-1 (END)
CURSOR get_minus_with_null(part_no_ IN VARCHAR2, site_ IN VARCHAR2, from_date_ IN VARCHAR2) IS
--(+/-) 140915 Miwelk R_G1426959-1 (START)
--(+/-) 140306 Thualk R_G1358412-1 (START)
SELECT t.quantity , t.transaction_id
--SELECT SUM(t.quantity) quantity --,t.transaction_id
--(+/-) 140306 Thualk R_G1358412-1 (END)
FROM Inventory_Transaction_Hist2 t
WHERE part_no = part_no_
AND contract = site_
AND direction = ’-’
AND location_no NOT LIKE ’%INSPECT%’
AND date_created <= (to_date(from_date_,’DD/MM/YYYY’) + ( 1 - 1/ ( 60*60*24 )));
--(+) 140306 Thualk R_G1358412-1 (START)
/*
AND EXISTS (SELECT 1 AS state
FROM Inventory_Transaction_Hist_Tab a
WHERE a.location_no NOT LIKE ’%INSPECT%’
AND a.contract = t.contract
AND a.part_no = t.part_no
AND a.transaction= ’INVM-IN’
AND a.direction = ’+’
AND a.transaction_id=t.transaction_id);
*/
--(+) 140306 Thualk R_G1358412-1 (END)
--(+/-) 140915 Miwelk R_G1426959-1 (END)
CURSOR get_minus_with_null_location(part_no_ IN VARCHAR2, site_ IN VARCHAR2, from_date_ IN VARCHAR2) IS
--(+/-) 140915 Miwelk R_G1426959-1 (START)
--(+/-) 140306 Thualk R_G1358412-1 (START)
SELECT t.quantity , t.transaction_id
--SELECT SUM(t.quantity) quantity --,t.transaction_id
--(+/-) 140306 Thualk R_G1358412-1 (END)
FROM Inventory_Transaction_Hist2 t
WHERE part_no = part_no_
AND contract = site_
AND direction = ’-’
AND location_no IS NULL
AND date_created <= (to_date(from_date_,’DD/MM/YYYY’) + ( 1 - 1/ ( 60*60*24 )));
--(+) 140306 Thualk R_G1358412-1 (START)
/*
AND EXISTS (SELECT 1 AS state
FROM Inventory_Transaction_Hist_Tab a
...