rem finished_goods.sql
set feedback off
set heading off
set echo off
set pagesize 0
set linesize 510
spool invent_sap1.unl
SELECT
	b.article_id || ' ' ||
	'CWL' || ' ' ||
	b.mill_id || ' ' ||
	b.track_num || ' ' ||
	stat || ' ' ||
	class || ' ' ||
	TRIM(' ' FROM TO_CHAR(SUM(A.wgt_est), '999999990.9')) || ' ' ||
	TRIM(' ' FROM TO_CHAR(SUM(sheet_count), '999999990.9'))
FROM 
	invent a, 
	(SELECT 
		unit_id, 
		article_id, 
		track_num, 
		mill_id
	FROM 
	 	invent
	WHERE 
		    (class = 'U' 
		OR class = 'W' 
		OR(class = 'T' 
		AND class_orig IN('U', 'W')) 
		OR (class = 'L' 
		AND class_orig IN('U', 'W'))) 
		AND stat IN('G','J','T') 
		AND ts_scaled > 0 
		AND unit_id=roll_id_lead) b
WHERE 
         a.roll_id_lead=b.unit_id
	AND (a.type_code = 'S' 
	OR   a.type_code = 'C')
GROUP BY 
	b.article_id, 
	'CWL', 
	b.mill_id, 
	b.track_num, 
	stat, 
	class
UNION
SELECT 
	b.article_id || ' ' ||
	'CWL' || ' ' ||
	b.mill_id || ' ' ||
	b.track_num || ' ' ||
	stat || ' ' ||
	class || ' ' ||
	TRIM(' ' FROM TO_CHAR(SUM(A.wgt_scaled), '999999990.9')) || ' ' ||
	TRIM(' ' FROM TO_CHAR(SUM(sheet_count), '999999990.9'))
FROM 
	invent a, 
	(SELECT
		unit_id, 
		article_id, 
		track_num, 
		mill_id
     FROM 
	 	invent
	 WHERE 
	 	    (class = 'U' 
		OR  class = 'W' 
		OR  (class = 'T' 
		AND class_orig IN('U', 'W')) 
		OR  (class = 'L' 
		AND class_orig IN('U', 'W'))) 
		AND	stat IN('G','J','T') 
		AND ts_scaled > 0 
		AND unit_id=roll_id_lead) b
WHERE 
	    a.roll_id_lead=b.unit_id
	AND a.type_code='R'
GROUP BY 
	b.article_id, 
	'CWL', 
	b.mill_id, 
	b.track_num, 
	stat, 
	class;
spool OFF
set feedback on
set heading on
set echo on
set pagesize 1000
set linesize 100