●集團年度出貨數量金額統計表●
sql_str += " SELECT '"+para1+"' OPLANT,t1.OYEAR,t1.OMONTH,"
sql_str += " CASE WHEN t1.OSALES IS NULL THEN '全部合計' ";
sql_str += " WHEN t1.OSALES IS NOT NULL AND t1.OITEMNO IS NULL ";
sql_str += " THEN '小計 ('||t1.OSALES||')' ELSE t1.OSALES END OSALES, ";
sql_str += " (t1.OITEMNO||'_') OITEMNO,t1.OITEMD1,t1.OITEMD2,t1.OCUSTID,t1.OCUSTOM,sum(t1.ONUM) ONUM,sum(t1.OTOTAL) OTOTAL ,sum(t2.ONUM) ONUM2,sum(t2.OTOTAL) OTOTAL2 FROM ( ";
sql_str += " SELECT OYEAR,OMONTH,OSALES,OITEMNO,OITEMD1,OITEMD2,OCUSTID,OCUSTOM,SUM(ONUM) AS ONUM,SUM(OTOTAL) AS OTOTAL FROM ( ";
sql_str += " SELECT '"+para2+"' AS OYEAR,NVL(month(oga02),0) AS OMonth,NVL(USERNAME,'N/A') AS OSALES,NVL(IMA01,' ') AS OITEMNO,NVL(IMA02,' ') AS OITEMD1,NVL(IMA021,' ') AS OITEMD2,NVL(occ01,' ') AS OCUSTID,NVL(occ02,' ') AS OCUSTOM,ogb12 AS ONUM,(ogb12*ogb13*oga24) as OTOTAL ";
sql_str += " FROM "+para1+".ima_file,"+para1+".oga_file,"+para1+".ogb_file,"+para1+".occ_file,DS.STAFF_FILE ";
sql_str += " WHERE occ01 = oga03 ";
sql_str += " AND ogb04 = ima01 ";
sql_str += " AND oga01 = ogb01 ";
sql_str += " AND ogapost = 'Y' "; // -- 已扣帳
sql_str += " AND ogaconf = 'Y' ";// -- 已確認
sql_str += " AND oga55 ='1' ";
sql_str += " AND oga09 not in ('1', '5') ";// -- 排除出通單
sql_str += " AND year(oga02) LIKE '"+para2+"' ";
sql_str += " AND month(oga02) LIKE '"+para3+"' ";
sql_str += " AND occ04 = USERID(+) ";
sql_str += " AND ogb04 LIKE '"+para4+"' ";
sql_str += " order by 1,2 ";
sql_str += " ) ";
sql_str += " GROUP BY OYEAR,OMONTH,OSALES,OITEMNO,OITEMD1,OITEMD2,OCUSTID,OCUSTOM ";
sql_str += " order by 1,2,3 ";
sql_str += " ) t1,( ";
sql_str += " SELECT '"+para1+"' OPLANT,OYEAR,OSALES,OITEMNO,OITEMD1,OITEMD2,OCUSTID,OCUSTOM,SUM(ONUM) AS ONUM,SUM(OTOTAL) AS OTOTAL FROM ( ";
sql_str += " SELECT '"+para2+"' AS OYEAR,NVL(USERNAME,'N/A') AS OSALES,NVL(IMA01,' ') AS OITEMNO,NVL(IMA02,' ') AS OITEMD1,NVL(IMA021,' ') AS OITEMD2,NVL(occ01,' ') AS OCUSTID,NVL(occ02,' ') AS OCUSTOM,ogb12 AS ONUM,(ogb12*ogb13*oga24) as OTOTAL ";
sql_str += " FROM "+para1+".ima_file,"+para1+".oga_file,"+para1+".ogb_file,"+para1+".occ_file,DS.STAFF_FILE ";
sql_str += " WHERE occ01 = oga03 ";
sql_str += " AND ogb04 = ima01 ";
sql_str += " AND oga01 = ogb01 ";
sql_str += " AND ogapost = 'Y' ";// -- 已扣帳
sql_str += " AND ogaconf = 'Y' ";// -- 已確認
sql_str += " AND oga55 ='1' ";
sql_str += " AND oga09 not in ('1', '5') ";// -- 排除出通單
sql_str += " AND year(oga02) LIKE '"+para2+"' ";
sql_str += " AND occ04 = USERID(+) ";
sql_str += " AND ogb04 LIKE '"+para4+"' ";
sql_str += " order by 1,2 ";
sql_str += " ) ";
sql_str += " GROUP BY OYEAR,OSALES,OITEMNO,OITEMD1,OITEMD2,OCUSTID,OCUSTOM ";
sql_str += " order by 1,2,3 ";
sql_str += " ) t2 ";
sql_str += " where ";
sql_str += " t1.OSALES = t2.OSALES ";
sql_str += " AND t1.OYEAR = t2.OYEAR ";
sql_str += " AND t1.OITEMNO = t2.OITEMNO ";
sql_str += " AND t1.OITEMD1 = t2.OITEMD1 ";
sql_str += " AND t1.OITEMD2 = t2.OITEMD2 ";
sql_str += " AND t1.OCUSTID = t2.OCUSTID ";
sql_str += " AND t1.OCUSTOM = t2.OCUSTOM ";
sql_str += " GROUP BY GROUPING SETS((t1.OSALES),(t1.OYEAR,t1.OMONTH,t1.OSALES,t1.OITEMNO,t1.OITEMD1,t1.OITEMD2,t1.OCUSTID,t1.OCUSTOM,t1.ONUM,t1.OTOTAL, t2.ONUM, t2.OTOTAL ),()) ";
全站熱搜
留言列表
站台介紹 (1)