--************************************************************************** --*　故紙受入月報の指定された月・仕入先名を取得 --*　(受入日付・仕入先コード) --************************************************************************** [lselectRecords6] SELECT MRW.DTS_YEAR ,MRW.DTS_MONTH ,MRW.CD_SUPPLIER FROM T_RECEIVE_WP MRW WHERE 1=1 AND (('&2' = '') OR (MRW.DT_RECEIVESLIP >= TO_DATE('&2', 'yyyy/mm/dd'))) AND (('&4' = '') OR (MRW.DT_RECEIVESLIP <= TO_DATE('&4', 'yyyy/mm/dd'))) AND (('&6' = '') OR (MRW.CD_SUPPLIER >= ('&6' ::text)::numeric )) AND (('&7' = '') OR (MRW.CD_SUPPLIER <= ('&7' ::text)::numeric )) AND (('&8' = '') OR (MRW.CD_SHIPMENT >= ('&8' ::text)::numeric )) AND (('&9' = '') OR (MRW.CD_SHIPMENT <= ('&9' ::text)::numeric )) AND (('&10' = '') OR (MRW.CD_WP >= ('&10' ::text)::numeric )) AND (('&11' = '') OR (MRW.CD_WP <= ('&11' ::text)::numeric )) AND (('&12' = '') OR (MRW.CD_GRADE = ('&12' ::text)::numeric )) AND MRW.FLG_DELETE = 0 GROUP BY MRW.DTS_YEAR , MRW.DTS_MONTH , MRW.CD_SUPPLIER ORDER BY MRW.DTS_YEAR , MRW.DTS_MONTH , MRW.CD_SUPPLIER --************************************************************************** --*　故紙受入月報の指定された月分の明細を取得 --*　(単価・数量・金額・前月21-末(単価)・前月21-末(数量)・前月21-末(金額)) --************************************************************************** [lselectRecords] SELECT MRW.CD_SUPPLIER ,MS.NAME_SUPPLIER ,MRW.CD_SHIPMENT ,MSIP.NAME_SHIPMENT ,MRW.CD_WP ,MRW.CD_GRADE ,MW.NAME_GRADE ,MRW.DTS_MONTH ,MRW.DTS_YEAR ,case coalesce(SUM(case MRW.DTS_DAY when 01 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 01 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 01 then MRW.WEIGHT_WP end),0) end TANKA01 ,case coalesce(SUM(case MRW.DTS_DAY when 02 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 02 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 02 then MRW.WEIGHT_WP end),0) end TANKA02 ,case coalesce(SUM(case MRW.DTS_DAY when 03 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 03 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 03 then MRW.WEIGHT_WP end),0) end TANKA03 ,case coalesce(SUM(case MRW.DTS_DAY when 04 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 04 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 04 then MRW.WEIGHT_WP end),0) end TANKA04 ,case coalesce(SUM(case MRW.DTS_DAY when 05 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 05 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 05 then MRW.WEIGHT_WP end),0) end TANKA05 ,case coalesce(SUM(case MRW.DTS_DAY when 06 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 06 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 06 then MRW.WEIGHT_WP end),0) end TANKA06 ,case coalesce(SUM(case MRW.DTS_DAY when 07 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 07 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 07 then MRW.WEIGHT_WP end),0) end TANKA07 ,case coalesce(SUM(case MRW.DTS_DAY when 08 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 08 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 08 then MRW.WEIGHT_WP end),0) end TANKA08 ,case coalesce(SUM(case MRW.DTS_DAY when 09 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 09 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 09 then MRW.WEIGHT_WP end),0) end TANKA09 ,case coalesce(SUM(case MRW.DTS_DAY when 10 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 10 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 10 then MRW.WEIGHT_WP end),0) end TANKA10 ,case coalesce(SUM(case MRW.DTS_DAY when 11 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 11 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 11 then MRW.WEIGHT_WP end),0) end TANKA11 ,case coalesce(SUM(case MRW.DTS_DAY when 12 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 12 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 12 then MRW.WEIGHT_WP end),0) end TANKA12 ,case coalesce(SUM(case MRW.DTS_DAY when 13 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 13 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 13 then MRW.WEIGHT_WP end),0) end TANKA13 ,case coalesce(SUM(case MRW.DTS_DAY when 14 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 14 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 14 then MRW.WEIGHT_WP end),0) end TANKA14 ,case coalesce(SUM(case MRW.DTS_DAY when 15 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 15 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 15 then MRW.WEIGHT_WP end),0) end TANKA15 ,case coalesce(SUM(case MRW.DTS_DAY when 16 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 16 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 16 then MRW.WEIGHT_WP end),0) end TANKA16 ,case coalesce(SUM(case MRW.DTS_DAY when 17 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 17 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 17 then MRW.WEIGHT_WP end),0) end TANKA17 ,case coalesce(SUM(case MRW.DTS_DAY when 18 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 18 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 18 then MRW.WEIGHT_WP end),0) end TANKA18 ,case coalesce(SUM(case MRW.DTS_DAY when 19 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 19 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 19 then MRW.WEIGHT_WP end),0) end TANKA19 ,case coalesce(SUM(case MRW.DTS_DAY when 20 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 20 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 20 then MRW.WEIGHT_WP end),0) end TANKA20 ,case coalesce(SUM(case MRW.DTS_DAY when 21 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 21 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 21 then MRW.WEIGHT_WP end),0) end TANKA21 ,case coalesce(SUM(case MRW.DTS_DAY when 22 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 22 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 22 then MRW.WEIGHT_WP end),0) end TANKA22 ,case coalesce(SUM(case MRW.DTS_DAY when 23 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 23 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 23 then MRW.WEIGHT_WP end),0) end TANKA23 ,case coalesce(SUM(case MRW.DTS_DAY when 24 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 24 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 24 then MRW.WEIGHT_WP end),0) end TANKA24 ,case coalesce(SUM(case MRW.DTS_DAY when 25 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 25 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 25 then MRW.WEIGHT_WP end),0) end TANKA25 ,case coalesce(SUM(case MRW.DTS_DAY when 26 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 26 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 26 then MRW.WEIGHT_WP end),0) end TANKA26 ,case coalesce(SUM(case MRW.DTS_DAY when 27 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 27 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 27 then MRW.WEIGHT_WP end),0) end TANKA27 ,case coalesce(SUM(case MRW.DTS_DAY when 28 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 28 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 28 then MRW.WEIGHT_WP end),0) end TANKA28 ,case coalesce(SUM(case MRW.DTS_DAY when 29 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 29 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 29 then MRW.WEIGHT_WP end),0) end TANKA29 ,case coalesce(SUM(case MRW.DTS_DAY when 30 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 30 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 30 then MRW.WEIGHT_WP end),0) end TANKA30 ,case coalesce(SUM(case MRW.DTS_DAY when 31 then MRW.WEIGHT_WP end),0) when 0 then 0 else coalesce(SUM(case MRW.DTS_DAY when 31 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) / coalesce(SUM(case MRW.DTS_DAY when 31 then MRW.WEIGHT_WP end),0) end TANKA31 ,coalesce(SUM(case MRW.DTS_DAY when 01 then MRW.WEIGHT_WP end),0) SURYO01 ,coalesce(SUM(case MRW.DTS_DAY when 02 then MRW.WEIGHT_WP end),0) SURYO02 ,coalesce(SUM(case MRW.DTS_DAY when 03 then MRW.WEIGHT_WP end),0) SURYO03 ,coalesce(SUM(case MRW.DTS_DAY when 04 then MRW.WEIGHT_WP end),0) SURYO04 ,coalesce(SUM(case MRW.DTS_DAY when 05 then MRW.WEIGHT_WP end),0) SURYO05 ,coalesce(SUM(case MRW.DTS_DAY when 06 then MRW.WEIGHT_WP end),0) SURYO06 ,coalesce(SUM(case MRW.DTS_DAY when 07 then MRW.WEIGHT_WP end),0) SURYO07 ,coalesce(SUM(case MRW.DTS_DAY when 08 then MRW.WEIGHT_WP end),0) SURYO08 ,coalesce(SUM(case MRW.DTS_DAY when 09 then MRW.WEIGHT_WP end),0) SURYO09 ,coalesce(SUM(case MRW.DTS_DAY when 10 then MRW.WEIGHT_WP end),0) SURYO10 ,coalesce(SUM(case MRW.DTS_DAY when 11 then MRW.WEIGHT_WP end),0) SURYO11 ,coalesce(SUM(case MRW.DTS_DAY when 12 then MRW.WEIGHT_WP end),0) SURYO12 ,coalesce(SUM(case MRW.DTS_DAY when 13 then MRW.WEIGHT_WP end),0) SURYO13 ,coalesce(SUM(case MRW.DTS_DAY when 14 then MRW.WEIGHT_WP end),0) SURYO14 ,coalesce(SUM(case MRW.DTS_DAY when 15 then MRW.WEIGHT_WP end),0) SURYO15 ,coalesce(SUM(case MRW.DTS_DAY when 16 then MRW.WEIGHT_WP end),0) SURYO16 ,coalesce(SUM(case MRW.DTS_DAY when 17 then MRW.WEIGHT_WP end),0) SURYO17 ,coalesce(SUM(case MRW.DTS_DAY when 18 then MRW.WEIGHT_WP end),0) SURYO18 ,coalesce(SUM(case MRW.DTS_DAY when 19 then MRW.WEIGHT_WP end),0) SURYO19 ,coalesce(SUM(case MRW.DTS_DAY when 20 then MRW.WEIGHT_WP end),0) SURYO20 ,coalesce(SUM(case MRW.DTS_DAY when 21 then MRW.WEIGHT_WP end),0) SURYO21 ,coalesce(SUM(case MRW.DTS_DAY when 22 then MRW.WEIGHT_WP end),0) SURYO22 ,coalesce(SUM(case MRW.DTS_DAY when 23 then MRW.WEIGHT_WP end),0) SURYO23 ,coalesce(SUM(case MRW.DTS_DAY when 24 then MRW.WEIGHT_WP end),0) SURYO24 ,coalesce(SUM(case MRW.DTS_DAY when 25 then MRW.WEIGHT_WP end),0) SURYO25 ,coalesce(SUM(case MRW.DTS_DAY when 26 then MRW.WEIGHT_WP end),0) SURYO26 ,coalesce(SUM(case MRW.DTS_DAY when 27 then MRW.WEIGHT_WP end),0) SURYO27 ,coalesce(SUM(case MRW.DTS_DAY when 28 then MRW.WEIGHT_WP end),0) SURYO28 ,coalesce(SUM(case MRW.DTS_DAY when 29 then MRW.WEIGHT_WP end),0) SURYO29 ,coalesce(SUM(case MRW.DTS_DAY when 30 then MRW.WEIGHT_WP end),0) SURYO30 ,coalesce(SUM(case MRW.DTS_DAY when 31 then MRW.WEIGHT_WP end),0) SURYO31 ,coalesce(SUM(case MRW.DTS_DAY when 01 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU01 ,coalesce(SUM(case MRW.DTS_DAY when 02 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU02 ,coalesce(SUM(case MRW.DTS_DAY when 03 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU03 ,coalesce(SUM(case MRW.DTS_DAY when 04 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU04 ,coalesce(SUM(case MRW.DTS_DAY when 05 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU05 ,coalesce(SUM(case MRW.DTS_DAY when 06 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU06 ,coalesce(SUM(case MRW.DTS_DAY when 07 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU07 ,coalesce(SUM(case MRW.DTS_DAY when 08 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU08 ,coalesce(SUM(case MRW.DTS_DAY when 09 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU09 ,coalesce(SUM(case MRW.DTS_DAY when 10 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU10 ,coalesce(SUM(case MRW.DTS_DAY when 11 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU11 ,coalesce(SUM(case MRW.DTS_DAY when 12 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU12 ,coalesce(SUM(case MRW.DTS_DAY when 13 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU13 ,coalesce(SUM(case MRW.DTS_DAY when 14 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU14 ,coalesce(SUM(case MRW.DTS_DAY when 15 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU15 ,coalesce(SUM(case MRW.DTS_DAY when 16 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU16 ,coalesce(SUM(case MRW.DTS_DAY when 17 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU17 ,coalesce(SUM(case MRW.DTS_DAY when 18 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU18 ,coalesce(SUM(case MRW.DTS_DAY when 19 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU19 ,coalesce(SUM(case MRW.DTS_DAY when 20 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU20 ,coalesce(SUM(case MRW.DTS_DAY when 21 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU21 ,coalesce(SUM(case MRW.DTS_DAY when 22 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU22 ,coalesce(SUM(case MRW.DTS_DAY when 23 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU23 ,coalesce(SUM(case MRW.DTS_DAY when 24 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU24 ,coalesce(SUM(case MRW.DTS_DAY when 25 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU25 ,coalesce(SUM(case MRW.DTS_DAY when 26 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU26 ,coalesce(SUM(case MRW.DTS_DAY when 27 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU27 ,coalesce(SUM(case MRW.DTS_DAY when 28 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU28 ,coalesce(SUM(case MRW.DTS_DAY when 29 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU29 ,coalesce(SUM(case MRW.DTS_DAY when 30 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU30 ,coalesce(SUM(case MRW.DTS_DAY when 31 then MRW.TOTAL_MONEY_WP_NO_FREIGHT - MRW.TAX_NO_FREIGHT end),0) KINGAKU31 ,coalesce(BEFOR.BFOKAZU,0) BFOKAZU ,coalesce(BEFOR.BFOKINGAKU,0) BFOKINGAKU FROM T_RECEIVE_WP MRW left join M_SHIPMENT MSIP on (MRW.CD_SHIPMENT = MSIP.CD_SHIPMENT) left join M_WP MW on (MRW.CD_WP = MW.CD_WP) left join( SELECT CD_SUPPLIER, CD_SHIPMENT ,CD_WP,SUM(WEIGHT_WP) BFOKAZU ,SUM(MONEY_WP) BFOKINGAKU FROM T_RECEIVE_WP WHERE 1 = 1 AND DT_RECEIVESLIP >= TO_DATE('&10', 'yyyy/mm/dd') AND DT_RECEIVESLIP <= TO_DATE('&11', 'yyyy/mm/dd') AND FLG_DELETE = 0 GROUP BY CD_SUPPLIER, CD_SHIPMENT , CD_WP ) BEFOR on (MRW.CD_SUPPLIER = BEFOR.CD_SUPPLIER AND MRW.CD_SHIPMENT = BEFOR.CD_SHIPMENT AND MRW.CD_WP = BEFOR.CD_WP) left join( SELECT CD_SUPPLIER ,NAME_SUPPLIER FROM M_SUPPLIER WHERE 1 = 1 AND DT_START <= TO_DATE('&12', 'yyyy/mm/dd') AND DT_END >= TO_DATE('&12', 'yyyy/mm/dd') AND FLG_DELETE = 0 )MS ON (MRW.CD_SUPPLIER = MS.CD_SUPPLIER) WHERE 1 = 1 AND MRW.CD_SUPPLIER = ('&1')::numeric AND MRW.DTS_YEAR = ('&2')::numeric AND MRW.DTS_MONTH = ('&3')::numeric AND (('&4' = '') OR (MRW.CD_SHIPMENT >= ('&4' ::text)::numeric )) AND (('&5' = '') OR (MRW.CD_SHIPMENT <= ('&5' ::text)::numeric )) AND (('&6' = '') OR (MRW.CD_WP >= ('&6' ::text)::numeric )) AND (('&7' = '') OR (MRW.CD_WP <= ('&7' ::text)::numeric )) AND (('&8' = '') OR (MRW.CD_GRADE = ('&8' ::text)::numeric )) AND MRW.FLG_DELETE = 0 AND MSIP.DT_START <= TO_DATE('&12', 'yyyy/mm/dd') AND MSIP.DT_END >= TO_DATE('&12', 'yyyy/mm/dd') AND MSIP.FLG_DELETE = 0 AND MW.DT_START <= TO_DATE('&12', 'yyyy/mm/dd') AND MW.DT_END >= TO_DATE('&12', 'yyyy/mm/dd') AND MW.FLG_DELETE = 0 GROUP BY MRW.CD_SUPPLIER ,MS.NAME_SUPPLIER ,MRW.CD_SHIPMENT ,MSIP.NAME_SHIPMENT ,MRW.CD_WP ,MRW.CD_GRADE ,MW.NAME_GRADE ,MRW.DTS_MONTH ,MRW.DTS_YEAR ,BEFOR.BFOKAZU ,BEFOR.BFOKINGAKU ORDER BY MRW.DTS_MONTH ,MRW.CD_SUPPLIER ,MRW.CD_SHIPMENT ,MRW.CD_GRADE --************************************************************************** --*　運賃込みの仕入先別合計のデータ(1ヶ月分）を取得 --*　(数量・金額・消費税・前月21-末(数量)・前月21-末(金額)前月21-末(消費税)) --************************************************************************** [lselectRecords2] select SUM(SURYO01) SURYO01 ,SUM(SURYO02) SURYO02 ,SUM(SURYO03) SURYO03 ,SUM(SURYO04) SURYO04 ,SUM(SURYO05) SURYO05 ,SUM(SURYO06) SURYO06 ,SUM(SURYO07) SURYO07 ,SUM(SURYO08) SURYO08 ,SUM(SURYO09) SURYO09 ,SUM(SURYO10) SURYO10 ,SUM(SURYO11) SURYO11 ,SUM(SURYO12) SURYO12 ,SUM(SURYO13) SURYO13 ,SUM(SURYO14) SURYO14 ,SUM(SURYO15) SURYO15 ,SUM(SURYO16) SURYO16 ,SUM(SURYO17) SURYO17 ,SUM(SURYO18) SURYO18 ,SUM(SURYO19) SURYO19 ,SUM(SURYO20) SURYO20 ,SUM(SURYO21) SURYO21 ,SUM(SURYO22) SURYO22 ,SUM(SURYO23) SURYO23 ,SUM(SURYO24) SURYO24 ,SUM(SURYO25) SURYO25 ,SUM(SURYO26) SURYO26 ,SUM(SURYO27) SURYO27 ,SUM(SURYO28) SURYO28 ,SUM(SURYO29) SURYO29 ,SUM(SURYO30) SURYO30 ,SUM(SURYO31) SURYO31 ,SUM(KINGAKU01) KINGAKU01 ,SUM(KINGAKU02) KINGAKU02 ,SUM(KINGAKU03) KINGAKU03 ,SUM(KINGAKU04) KINGAKU04 ,SUM(KINGAKU05) KINGAKU05 ,SUM(KINGAKU06) KINGAKU06 ,SUM(KINGAKU07) KINGAKU07 ,SUM(KINGAKU08) KINGAKU08 ,SUM(KINGAKU09) KINGAKU09 ,SUM(KINGAKU10) KINGAKU10 ,SUM(KINGAKU11) KINGAKU11 ,SUM(KINGAKU12) KINGAKU12 ,SUM(KINGAKU13) KINGAKU13 ,SUM(KINGAKU14) KINGAKU14 ,SUM(KINGAKU15) KINGAKU15 ,SUM(KINGAKU16) KINGAKU16 ,SUM(KINGAKU17) KINGAKU17 ,SUM(KINGAKU18) KINGAKU18 ,SUM(KINGAKU19) KINGAKU19 ,SUM(KINGAKU20) KINGAKU20 ,SUM(KINGAKU21) KINGAKU21 ,SUM(KINGAKU22) KINGAKU22 ,SUM(KINGAKU23) KINGAKU23 ,SUM(KINGAKU24) KINGAKU24 ,SUM(KINGAKU25) KINGAKU25 ,SUM(KINGAKU26) KINGAKU26 ,SUM(KINGAKU27) KINGAKU27 ,SUM(KINGAKU28) KINGAKU28 ,SUM(KINGAKU29) KINGAKU29 ,SUM(KINGAKU30) KINGAKU30 ,SUM(KINGAKU31) KINGAKU31 ,SUM(TAX01) TAX01 ,SUM(TAX02) TAX02 ,SUM(TAX03) TAX03 ,SUM(TAX04) TAX04 ,SUM(TAX05) TAX05 ,SUM(TAX06) TAX06 ,SUM(TAX07) TAX07 ,SUM(TAX08) TAX08 ,SUM(TAX09) TAX09 ,SUM(TAX10) TAX10 ,SUM(TAX11) TAX11 ,SUM(TAX12) TAX12 ,SUM(TAX13) TAX13 ,SUM(TAX14) TAX14 ,SUM(TAX15) TAX15 ,SUM(TAX16) TAX16 ,SUM(TAX17) TAX17 ,SUM(TAX18) TAX18 ,SUM(TAX19) TAX19 ,SUM(TAX20) TAX20 ,SUM(TAX21) TAX21 ,SUM(TAX22) TAX22 ,SUM(TAX23) TAX23 ,SUM(TAX24) TAX24 ,SUM(TAX25) TAX25 ,SUM(TAX26) TAX26 ,SUM(TAX27) TAX27 ,SUM(TAX28) TAX28 ,SUM(TAX29) TAX29 ,SUM(TAX30) TAX30 ,SUM(TAX31) TAX31 ,t.BFOKAZU ,t.BFOKINGAKU ,t.BFOTAX ,t.BFOALLKAZU ,t.BFOALLKINGAKU FROM( SELECT SUM(case DTS_DAY when 01 then WEIGHT_WP else null end) SURYO01 ,SUM(case DTS_DAY when 02 then WEIGHT_WP else null end) SURYO02 ,SUM(case DTS_DAY when 03 then WEIGHT_WP else null end) SURYO03 ,SUM(case DTS_DAY when 04 then WEIGHT_WP else null end) SURYO04 ,SUM(case DTS_DAY when 05 then WEIGHT_WP else null end) SURYO05 ,SUM(case DTS_DAY when 06 then WEIGHT_WP else null end) SURYO06 ,SUM(case DTS_DAY when 07 then WEIGHT_WP else null end) SURYO07 ,SUM(case DTS_DAY when 08 then WEIGHT_WP else null end) SURYO08 ,SUM(case DTS_DAY when 09 then WEIGHT_WP else null end) SURYO09 ,SUM(case DTS_DAY when 10 then WEIGHT_WP else null end) SURYO10 ,SUM(case DTS_DAY when 11 then WEIGHT_WP else null end) SURYO11 ,SUM(case DTS_DAY when 12 then WEIGHT_WP else null end) SURYO12 ,SUM(case DTS_DAY when 13 then WEIGHT_WP else null end) SURYO13 ,SUM(case DTS_DAY when 14 then WEIGHT_WP else null end) SURYO14 ,SUM(case DTS_DAY when 15 then WEIGHT_WP else null end) SURYO15 ,SUM(case DTS_DAY when 16 then WEIGHT_WP else null end) SURYO16 ,SUM(case DTS_DAY when 17 then WEIGHT_WP else null end) SURYO17 ,SUM(case DTS_DAY when 18 then WEIGHT_WP else null end) SURYO18 ,SUM(case DTS_DAY when 19 then WEIGHT_WP else null end) SURYO19 ,SUM(case DTS_DAY when 20 then WEIGHT_WP else null end) SURYO20 ,SUM(case DTS_DAY when 21 then WEIGHT_WP else null end) SURYO21 ,SUM(case DTS_DAY when 22 then WEIGHT_WP else null end) SURYO22 ,SUM(case DTS_DAY when 23 then WEIGHT_WP else null end) SURYO23 ,SUM(case DTS_DAY when 24 then WEIGHT_WP else null end) SURYO24 ,SUM(case DTS_DAY when 25 then WEIGHT_WP else null end) SURYO25 ,SUM(case DTS_DAY when 26 then WEIGHT_WP else null end) SURYO26 ,SUM(case DTS_DAY when 27 then WEIGHT_WP else null end) SURYO27 ,SUM(case DTS_DAY when 28 then WEIGHT_WP else null end) SURYO28 ,SUM(case DTS_DAY when 29 then WEIGHT_WP else null end) SURYO29 ,SUM(case DTS_DAY when 30 then WEIGHT_WP else null end) SURYO30 ,SUM(case DTS_DAY when 31 then WEIGHT_WP else null end) SURYO31 ,SUM(case DTS_DAY when 01 then MONEY_WP else null end) KINGAKU01 ,SUM(case DTS_DAY when 02 then MONEY_WP else null end) KINGAKU02 ,SUM(case DTS_DAY when 03 then MONEY_WP else null end) KINGAKU03 ,SUM(case DTS_DAY when 04 then MONEY_WP else null end) KINGAKU04 ,SUM(case DTS_DAY when 05 then MONEY_WP else null end) KINGAKU05 ,SUM(case DTS_DAY when 06 then MONEY_WP else null end) KINGAKU06 ,SUM(case DTS_DAY when 07 then MONEY_WP else null end) KINGAKU07 ,SUM(case DTS_DAY when 08 then MONEY_WP else null end) KINGAKU08 ,SUM(case DTS_DAY when 09 then MONEY_WP else null end) KINGAKU09 ,SUM(case DTS_DAY when 10 then MONEY_WP else null end) KINGAKU10 ,SUM(case DTS_DAY when 11 then MONEY_WP else null end) KINGAKU11 ,SUM(case DTS_DAY when 12 then MONEY_WP else null end) KINGAKU12 ,SUM(case DTS_DAY when 13 then MONEY_WP else null end) KINGAKU13 ,SUM(case DTS_DAY when 14 then MONEY_WP else null end) KINGAKU14 ,SUM(case DTS_DAY when 15 then MONEY_WP else null end) KINGAKU15 ,SUM(case DTS_DAY when 16 then MONEY_WP else null end) KINGAKU16 ,SUM(case DTS_DAY when 17 then MONEY_WP else null end) KINGAKU17 ,SUM(case DTS_DAY when 18 then MONEY_WP else null end) KINGAKU18 ,SUM(case DTS_DAY when 19 then MONEY_WP else null end) KINGAKU19 ,SUM(case DTS_DAY when 20 then MONEY_WP else null end) KINGAKU20 ,SUM(case DTS_DAY when 21 then MONEY_WP else null end) KINGAKU21 ,SUM(case DTS_DAY when 22 then MONEY_WP else null end) KINGAKU22 ,SUM(case DTS_DAY when 23 then MONEY_WP else null end) KINGAKU23 ,SUM(case DTS_DAY when 24 then MONEY_WP else null end) KINGAKU24 ,SUM(case DTS_DAY when 25 then MONEY_WP else null end) KINGAKU25 ,SUM(case DTS_DAY when 26 then MONEY_WP else null end) KINGAKU26 ,SUM(case DTS_DAY when 27 then MONEY_WP else null end) KINGAKU27 ,SUM(case DTS_DAY when 28 then MONEY_WP else null end) KINGAKU28 ,SUM(case DTS_DAY when 29 then MONEY_WP else null end) KINGAKU29 ,SUM(case DTS_DAY when 30 then MONEY_WP else null end) KINGAKU30 ,SUM(case DTS_DAY when 31 then MONEY_WP else null end) KINGAKU31 ,ROUND(SUM(case DTS_DAY when 01 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX01 ,ROUND(SUM(case DTS_DAY when 02 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX02 ,ROUND(SUM(case DTS_DAY when 03 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX03 ,ROUND(SUM(case DTS_DAY when 04 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX04 ,ROUND(SUM(case DTS_DAY when 05 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX05 ,ROUND(SUM(case DTS_DAY when 06 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX06 ,ROUND(SUM(case DTS_DAY when 07 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX07 ,ROUND(SUM(case DTS_DAY when 08 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX08 ,ROUND(SUM(case DTS_DAY when 09 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX09 ,ROUND(SUM(case DTS_DAY when 10 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX10 ,ROUND(SUM(case DTS_DAY when 11 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX11 ,ROUND(SUM(case DTS_DAY when 12 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX12 ,ROUND(SUM(case DTS_DAY when 13 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX13 ,ROUND(SUM(case DTS_DAY when 14 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX14 ,ROUND(SUM(case DTS_DAY when 15 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX15 ,ROUND(SUM(case DTS_DAY when 16 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX16 ,ROUND(SUM(case DTS_DAY when 17 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX17 ,ROUND(SUM(case DTS_DAY when 18 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX18 ,ROUND(SUM(case DTS_DAY when 19 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX19 ,ROUND(SUM(case DTS_DAY when 20 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX20 ,ROUND(SUM(case DTS_DAY when 21 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX21 ,ROUND(SUM(case DTS_DAY when 22 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX22 ,ROUND(SUM(case DTS_DAY when 23 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX23 ,ROUND(SUM(case DTS_DAY when 24 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX24 ,ROUND(SUM(case DTS_DAY when 25 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX25 ,ROUND(SUM(case DTS_DAY when 26 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX26 ,ROUND(SUM(case DTS_DAY when 27 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX27 ,ROUND(SUM(case DTS_DAY when 28 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX28 ,ROUND(SUM(case DTS_DAY when 29 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX29 ,ROUND(SUM(case DTS_DAY when 30 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX30 ,ROUND(SUM(case DTS_DAY when 31 then MONEY_WP else null end) * (RATE_TAX/100.0),0) TAX31 ,BEFOR.BFOKAZU ,BEFOR.BFOKINGAKU ,BEFOR.BFOTAX ,BEFORALL.BFOALLKAZU ,BEFORALL.BFOALLKINGAKU FROM T_RECEIVE_WP MRW left join( SELECT CD_SUPPLIER ,SUM(WEIGHT_WP) BFOKAZU ,SUM(MONEY_WP) BFOKINGAKU,SUM(TAX) BFOTAX FROM T_RECEIVE_WP WHERE 1 = 1 AND CD_SUPPLIER = ('&1')::numeric AND DT_RECEIVESLIP >= TO_DATE('&10', 'yyyy/mm/dd') AND DT_RECEIVESLIP <= TO_DATE('&11', 'yyyy/mm/dd') AND FLG_DELETE = 0 GROUP BY CD_SUPPLIER ) BEFOR on (MRW.CD_SUPPLIER = BEFOR.CD_SUPPLIER) left join( SELECT CD_SUPPLIER ,SUM(WEIGHT_WP) BFOALLKAZU ,SUM(MONEY_WP) BFOALLKINGAKU FROM T_RECEIVE_WP WHERE 1 = 1 AND CD_SUPPLIER = ('&1')::numeric AND DT_RECEIVESLIP >= TO_DATE('&9', 'yyyy/mm/dd') AND DT_RECEIVESLIP <= TO_DATE('&11', 'yyyy/mm/dd') AND FLG_DELETE = 0 GROUP BY CD_SUPPLIER ) BEFORALL ON (MRW.CD_SUPPLIER = BEFORALL.CD_SUPPLIER) WHERE 1 = 1 AND MRW.CD_SUPPLIER = ('&1')::numeric AND MRW.DTS_YEAR = ('&2')::numeric AND MRW.DTS_MONTH = ('&3')::numeric AND (('&4' = '') OR (MRW.CD_SHIPMENT >= ('&4' ::text)::numeric )) AND (('&5' = '') OR (MRW.CD_SHIPMENT <= ('&5' ::text)::numeric )) AND (('&6' = '') OR (MRW.CD_WP >= ('&6' ::text)::numeric )) AND (('&7' = '') OR (MRW.CD_WP <= ('&7' ::text)::numeric )) AND (('&8' = '') OR (MRW.CD_GRADE = ('&8' ::text)::numeric )) AND MRW.FLG_DELETE = 0 GROUP BY MRW.CD_SUPPLIER ,BEFOR.BFOKAZU ,BEFOR.BFOKINGAKU ,BEFOR.BFOTAX ,BEFORALL.BFOALLKAZU ,BEFORALL.BFOALLKINGAKU ,MRW.RATE_TAX ) t group by t.BFOKAZU ,t.BFOKINGAKU ,t.BFOTAX ,t.BFOALLKAZU ,t.BFOALLKINGAKU --************************************************************************** --*　運賃除く仕入先別合計のデータ(1ヶ月分）を取得 --*　(数量・金額・消費税・前月21-末(数量)・前月21-末(金額)前月21-末(消費税)) --************************************************************************** [lselectRecords3] SELECT t.CD_SUPPLIER ,SUM(NOKINGAKU01) NOKINGAKU01 ,SUM(NOKINGAKU02) NOKINGAKU02 ,SUM(NOKINGAKU03) NOKINGAKU03 ,SUM(NOKINGAKU04) NOKINGAKU04 ,SUM(NOKINGAKU05) NOKINGAKU05 ,SUM(NOKINGAKU06) NOKINGAKU06 ,SUM(NOKINGAKU07) NOKINGAKU07 ,SUM(NOKINGAKU08) NOKINGAKU08 ,SUM(NOKINGAKU09) NOKINGAKU09 ,SUM(NOKINGAKU10) NOKINGAKU10 ,SUM(NOKINGAKU11) NOKINGAKU11 ,SUM(NOKINGAKU12) NOKINGAKU12 ,SUM(NOKINGAKU13) NOKINGAKU13 ,SUM(NOKINGAKU14) NOKINGAKU14 ,SUM(NOKINGAKU15) NOKINGAKU15 ,SUM(NOKINGAKU16) NOKINGAKU16 ,SUM(NOKINGAKU17) NOKINGAKU17 ,SUM(NOKINGAKU18) NOKINGAKU18 ,SUM(NOKINGAKU19) NOKINGAKU19 ,SUM(NOKINGAKU20) NOKINGAKU20 ,SUM(NOKINGAKU21) NOKINGAKU21 ,SUM(NOKINGAKU22) NOKINGAKU22 ,SUM(NOKINGAKU23) NOKINGAKU23 ,SUM(NOKINGAKU24) NOKINGAKU24 ,SUM(NOKINGAKU25) NOKINGAKU25 ,SUM(NOKINGAKU26) NOKINGAKU26 ,SUM(NOKINGAKU27) NOKINGAKU27 ,SUM(NOKINGAKU28) NOKINGAKU28 ,SUM(NOKINGAKU29) NOKINGAKU29 ,SUM(NOKINGAKU30) NOKINGAKU30 ,SUM(NOKINGAKU31) NOKINGAKU31 ,SUM(NOTAX01) NOTAX01 ,SUM(NOTAX02) NOTAX02 ,SUM(NOTAX03) NOTAX03 ,SUM(NOTAX04) NOTAX04 ,SUM(NOTAX05) NOTAX05 ,SUM(NOTAX06) NOTAX06 ,SUM(NOTAX07) NOTAX07 ,SUM(NOTAX08) NOTAX08 ,SUM(NOTAX09) NOTAX09 ,SUM(NOTAX10) NOTAX10 ,SUM(NOTAX11) NOTAX11 ,SUM(NOTAX12) NOTAX12 ,SUM(NOTAX13) NOTAX13 ,SUM(NOTAX14) NOTAX14 ,SUM(NOTAX15) NOTAX15 ,SUM(NOTAX16) NOTAX16 ,SUM(NOTAX17) NOTAX17 ,SUM(NOTAX18) NOTAX18 ,SUM(NOTAX19) NOTAX19 ,SUM(NOTAX20) NOTAX20 ,SUM(NOTAX21) NOTAX21 ,SUM(NOTAX22) NOTAX22 ,SUM(NOTAX23) NOTAX23 ,SUM(NOTAX24) NOTAX24 ,SUM(NOTAX25) NOTAX25 ,SUM(NOTAX26) NOTAX26 ,SUM(NOTAX27) NOTAX27 ,SUM(NOTAX28) NOTAX28 ,SUM(NOTAX29) NOTAX29 ,SUM(NOTAX30) NOTAX30 ,SUM(NOTAX31) NOTAX31 ,SUM(NOSOUGAKU01) NOSOUGAKU01 ,SUM(NOSOUGAKU02) NOSOUGAKU02 ,SUM(NOSOUGAKU03) NOSOUGAKU03 ,SUM(NOSOUGAKU04) NOSOUGAKU04 ,SUM(NOSOUGAKU05) NOSOUGAKU05 ,SUM(NOSOUGAKU06) NOSOUGAKU06 ,SUM(NOSOUGAKU07) NOSOUGAKU07 ,SUM(NOSOUGAKU08) NOSOUGAKU08 ,SUM(NOSOUGAKU09) NOSOUGAKU09 ,SUM(NOSOUGAKU10) NOSOUGAKU10 ,SUM(NOSOUGAKU11) NOSOUGAKU11 ,SUM(NOSOUGAKU12) NOSOUGAKU12 ,SUM(NOSOUGAKU13) NOSOUGAKU13 ,SUM(NOSOUGAKU14) NOSOUGAKU14 ,SUM(NOSOUGAKU15) NOSOUGAKU15 ,SUM(NOSOUGAKU16) NOSOUGAKU16 ,SUM(NOSOUGAKU17) NOSOUGAKU17 ,SUM(NOSOUGAKU18) NOSOUGAKU18 ,SUM(NOSOUGAKU19) NOSOUGAKU19 ,SUM(NOSOUGAKU20) NOSOUGAKU20 ,SUM(NOSOUGAKU21) NOSOUGAKU21 ,SUM(NOSOUGAKU22) NOSOUGAKU22 ,SUM(NOSOUGAKU23) NOSOUGAKU23 ,SUM(NOSOUGAKU24) NOSOUGAKU24 ,SUM(NOSOUGAKU25) NOSOUGAKU25 ,SUM(NOSOUGAKU26) NOSOUGAKU26 ,SUM(NOSOUGAKU27) NOSOUGAKU27 ,SUM(NOSOUGAKU28) NOSOUGAKU28 ,SUM(NOSOUGAKU29) NOSOUGAKU29 ,SUM(NOSOUGAKU30) NOSOUGAKU30 ,SUM(NOSOUGAKU31) NOSOUGAKU31 ,t.BFONOKINGAKU ,t.BFONOTAX ,t.BFOALLNOKINGAKU FROM( SELECT MRW.CD_SUPPLIER ,SUM(case DTS_DAY when 01 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU01 ,SUM(case DTS_DAY when 02 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU02 ,SUM(case DTS_DAY when 03 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU03 ,SUM(case DTS_DAY when 04 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU04 ,SUM(case DTS_DAY when 05 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU05 ,SUM(case DTS_DAY when 06 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU06 ,SUM(case DTS_DAY when 07 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU07 ,SUM(case DTS_DAY when 08 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU08 ,SUM(case DTS_DAY when 09 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU09 ,SUM(case DTS_DAY when 10 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU10 ,SUM(case DTS_DAY when 11 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU11 ,SUM(case DTS_DAY when 12 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU12 ,SUM(case DTS_DAY when 13 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU13 ,SUM(case DTS_DAY when 14 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU14 ,SUM(case DTS_DAY when 15 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU15 ,SUM(case DTS_DAY when 16 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU16 ,SUM(case DTS_DAY when 17 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU17 ,SUM(case DTS_DAY when 18 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU18 ,SUM(case DTS_DAY when 19 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU19 ,SUM(case DTS_DAY when 20 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU20 ,SUM(case DTS_DAY when 21 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU21 ,SUM(case DTS_DAY when 22 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU22 ,SUM(case DTS_DAY when 23 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU23 ,SUM(case DTS_DAY when 24 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU24 ,SUM(case DTS_DAY when 25 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU25 ,SUM(case DTS_DAY when 26 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU26 ,SUM(case DTS_DAY when 27 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU27 ,SUM(case DTS_DAY when 28 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU28 ,SUM(case DTS_DAY when 29 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU29 ,SUM(case DTS_DAY when 30 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU30 ,SUM(case DTS_DAY when 31 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) NOKINGAKU31 ,ROUND(SUM(case DTS_DAY when 01 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX01 ,ROUND(SUM(case DTS_DAY when 02 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX02 ,ROUND(SUM(case DTS_DAY when 03 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX03 ,ROUND(SUM(case DTS_DAY when 04 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX04 ,ROUND(SUM(case DTS_DAY when 05 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX05 ,ROUND(SUM(case DTS_DAY when 06 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX06 ,ROUND(SUM(case DTS_DAY when 07 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX07 ,ROUND(SUM(case DTS_DAY when 08 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX08 ,ROUND(SUM(case DTS_DAY when 09 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX09 ,ROUND(SUM(case DTS_DAY when 10 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX10 ,ROUND(SUM(case DTS_DAY when 11 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX11 ,ROUND(SUM(case DTS_DAY when 12 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX12 ,ROUND(SUM(case DTS_DAY when 13 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX13 ,ROUND(SUM(case DTS_DAY when 14 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX14 ,ROUND(SUM(case DTS_DAY when 15 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX15 ,ROUND(SUM(case DTS_DAY when 16 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX16 ,ROUND(SUM(case DTS_DAY when 17 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX17 ,ROUND(SUM(case DTS_DAY when 18 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX18 ,ROUND(SUM(case DTS_DAY when 19 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX19 ,ROUND(SUM(case DTS_DAY when 20 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX20 ,ROUND(SUM(case DTS_DAY when 21 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX21 ,ROUND(SUM(case DTS_DAY when 22 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX22 ,ROUND(SUM(case DTS_DAY when 23 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX23 ,ROUND(SUM(case DTS_DAY when 24 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX24 ,ROUND(SUM(case DTS_DAY when 25 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX25 ,ROUND(SUM(case DTS_DAY when 26 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX26 ,ROUND(SUM(case DTS_DAY when 27 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX27 ,ROUND(SUM(case DTS_DAY when 28 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX28 ,ROUND(SUM(case DTS_DAY when 29 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX29 ,ROUND(SUM(case DTS_DAY when 30 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX30 ,ROUND(SUM(case DTS_DAY when 31 then MONEY_WP - MONEY_WP_NO_FREIGHT else NULL end) * (RATE_TAX / 100.0),0) NOTAX31 ,SUM(case DTS_DAY when 01 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU01 ,SUM(case DTS_DAY when 02 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU02 ,SUM(case DTS_DAY when 03 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU03 ,SUM(case DTS_DAY when 04 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU04 ,SUM(case DTS_DAY when 05 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU05 ,SUM(case DTS_DAY when 06 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU06 ,SUM(case DTS_DAY when 07 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU07 ,SUM(case DTS_DAY when 08 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU08 ,SUM(case DTS_DAY when 09 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU09 ,SUM(case DTS_DAY when 10 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU10 ,SUM(case DTS_DAY when 11 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU11 ,SUM(case DTS_DAY when 12 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU12 ,SUM(case DTS_DAY when 13 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU13 ,SUM(case DTS_DAY when 14 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU14 ,SUM(case DTS_DAY when 15 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU15 ,SUM(case DTS_DAY when 16 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU16 ,SUM(case DTS_DAY when 17 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU17 ,SUM(case DTS_DAY when 18 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU18 ,SUM(case DTS_DAY when 19 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU19 ,SUM(case DTS_DAY when 20 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU20 ,SUM(case DTS_DAY when 21 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU21 ,SUM(case DTS_DAY when 22 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU22 ,SUM(case DTS_DAY when 23 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU23 ,SUM(case DTS_DAY when 24 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU24 ,SUM(case DTS_DAY when 25 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU25 ,SUM(case DTS_DAY when 26 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU26 ,SUM(case DTS_DAY when 27 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU27 ,SUM(case DTS_DAY when 28 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU28 ,SUM(case DTS_DAY when 29 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU29 ,SUM(case DTS_DAY when 30 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU30 ,SUM(case DTS_DAY when 31 then TOTAL_MONEY_WP_NO_FREIGHT else null end) NOSOUGAKU31 ,BEFOR.BFONOKINGAKU ,BEFOR.BFONOTAX ,BEFORALL.BFOALLNOKINGAKU FROM T_RECEIVE_WP MRW left join( SELECT CD_SUPPLIER ,SUM(UNIT_PRICE_WP * WEIGHT_WP) BFONOKINGAKU ,SUM(TAX_NO_FREIGHT) BFONOTAX FROM T_RECEIVE_WP WHERE 1 = 1 AND CD_SUPPLIER = ('&1')::numeric AND DT_RECEIVESLIP >= TO_DATE('&10', 'yyyy/mm/dd') AND DT_RECEIVESLIP <= TO_DATE('&11', 'yyyy/mm/dd') AND FLG_DELETE = 0 GROUP BY CD_SUPPLIER ) BEFOR on (MRW.CD_SUPPLIER = BEFOR.CD_SUPPLIER) left join( SELECT CD_SUPPLIER,SUM(UNIT_PRICE_WP * WEIGHT_WP) BFOALLNOKINGAKU ,SUM(TAX_NO_FREIGHT) BFOALLNOTAX FROM T_RECEIVE_WP WHERE 1 = 1 AND CD_SUPPLIER = ('&1')::numeric AND DT_RECEIVESLIP >= TO_DATE('&9', 'yyyy/mm/dd') AND DT_RECEIVESLIP <= TO_DATE('&11', 'yyyy/mm/dd') AND FLG_DELETE = 0 GROUP BY CD_SUPPLIER ) BEFORALL ON (MRW.CD_SUPPLIER = BEFORALL.CD_SUPPLIER) WHERE 1 = 1 AND MRW.CD_SUPPLIER = ('&1')::numeric AND MRW.DTS_YEAR = ('&2')::numeric AND MRW.DTS_MONTH = ('&3')::numeric AND (('&4' = '') OR (MRW.CD_SHIPMENT >= ('&4' ::text)::numeric )) AND (('&5' = '') OR (MRW.CD_SHIPMENT <= ('&5' ::text)::numeric )) AND (('&6' = '') OR (MRW.CD_WP >= ('&6' ::text)::numeric )) AND (('&7' = '') OR (MRW.CD_WP <= ('&7' ::text)::numeric )) AND (('&8' = '') OR (MRW.CD_GRADE = ('&8' ::text)::numeric )) AND MRW.FLG_DELETE = 0 GROUP BY MRW.CD_SUPPLIER ,BEFOR.BFONOKINGAKU ,BEFOR.BFONOTAX ,BEFORALL.BFOALLNOKINGAKU ,MRW.RATE_TAX ) t group by t.CD_SUPPLIER ,t.BFONOKINGAKU ,t.BFONOTAX ,t.BFOALLNOKINGAKU --************************************************************************** --*　支払金額のデータ(1ヶ月分）を取得 --*　(支払金額・前月21-末(支払金額)) --************************************************************************** [lselectRecords4] SELECT TP.CD_SUPPLIER ,SUM(case DTS_DAY when 01 then AMOUND_DUE else null end) SIHARAI01 ,SUM(case DTS_DAY when 02 then AMOUND_DUE else null end) SIHARAI02 ,SUM(case DTS_DAY when 03 then AMOUND_DUE else null end) SIHARAI03 ,SUM(case DTS_DAY when 04 then AMOUND_DUE else null end) SIHARAI04 ,SUM(case DTS_DAY when 05 then AMOUND_DUE else null end) SIHARAI05 ,SUM(case DTS_DAY when 06 then AMOUND_DUE else null end) SIHARAI06 ,SUM(case DTS_DAY when 07 then AMOUND_DUE else null end) SIHARAI07 ,SUM(case DTS_DAY when 08 then AMOUND_DUE else null end) SIHARAI08 ,SUM(case DTS_DAY when 09 then AMOUND_DUE else null end) SIHARAI09 ,SUM(case DTS_DAY when 10 then AMOUND_DUE else null end) SIHARAI10 ,SUM(case DTS_DAY when 11 then AMOUND_DUE else null end) SIHARAI11 ,SUM(case DTS_DAY when 12 then AMOUND_DUE else null end) SIHARAI12 ,SUM(case DTS_DAY when 13 then AMOUND_DUE else null end) SIHARAI13 ,SUM(case DTS_DAY when 14 then AMOUND_DUE else null end) SIHARAI14 ,SUM(case DTS_DAY when 15 then AMOUND_DUE else null end) SIHARAI15 ,SUM(case DTS_DAY when 16 then AMOUND_DUE else null end) SIHARAI16 ,SUM(case DTS_DAY when 17 then AMOUND_DUE else null end) SIHARAI17 ,SUM(case DTS_DAY when 18 then AMOUND_DUE else null end) SIHARAI18 ,SUM(case DTS_DAY when 19 then AMOUND_DUE else null end) SIHARAI19 ,SUM(case DTS_DAY when 20 then AMOUND_DUE else null end) SIHARAI20 ,SUM(case DTS_DAY when 21 then AMOUND_DUE else null end) SIHARAI21 ,SUM(case DTS_DAY when 22 then AMOUND_DUE else null end) SIHARAI22 ,SUM(case DTS_DAY when 23 then AMOUND_DUE else null end) SIHARAI23 ,SUM(case DTS_DAY when 24 then AMOUND_DUE else null end) SIHARAI24 ,SUM(case DTS_DAY when 25 then AMOUND_DUE else null end) SIHARAI25 ,SUM(case DTS_DAY when 26 then AMOUND_DUE else null end) SIHARAI26 ,SUM(case DTS_DAY when 27 then AMOUND_DUE else null end) SIHARAI27 ,SUM(case DTS_DAY when 28 then AMOUND_DUE else null end) SIHARAI28 ,SUM(case DTS_DAY when 29 then AMOUND_DUE else null end) SIHARAI29 ,SUM(case DTS_DAY when 30 then AMOUND_DUE else null end) SIHARAI30 ,SUM(case DTS_DAY when 31 then AMOUND_DUE else null end) SIHARAI31 ,BEFOR.BFOSIHARAI FROM T_PAYMENT TP left join( SELECT CD_SUPPLIER ,SUM(AMOUND_DUE) BFOSIHARAI FROM T_PAYMENT WHERE 1 = 1 AND CD_SUPPLIER = ('&1')::numeric AND DT_PAYMENTSLIP >= TO_DATE('&5', 'yyyy/mm/dd') AND DT_PAYMENTSLIP <= TO_DATE('&6', 'yyyy/mm/dd') AND FLG_DELETE = 0 GROUP BY CD_SUPPLIER ) BEFOR ON (TP.CD_SUPPLIER = BEFOR.CD_SUPPLIER) WHERE 1 = 1 AND TP.CD_SUPPLIER = ('&1')::numeric AND TP.DTS_YEAR = ('&2')::numeric AND TP.DTS_MONTH = ('&3')::numeric AND TP.FLG_DELETE = 0 GROUP BY TP.CD_SUPPLIER , BEFOR.BFOSIHARAI --************************************************************************** --*　前月の残高を取得 --*　(指定月の全月末までの(総受入金額-支払金額)) --************************************************************************** [lselectRecords5] SELECT coalesce((case MRW.GTOTALUKEIRE when null then 0 else MRW.GTOTALUKEIRE end - case TP.GTOTALSIHARAI when null then 0 else coalesce(TP.GTOTALSIHARAI,0) end),0) ZENGETUZAN FROM ( SELECT SUM(TOTAL_MONEY_WP_NO_FREIGHT) GTOTALUKEIRE FROM T_RECEIVE_WP WHERE 1 = 1 AND CD_SUPPLIER = ('&1')::numeric AND DT_RECEIVESLIP <= TO_DATE('&6', 'yyyy/mm/dd') AND FLG_DELETE = 0 ) MRW ,( SELECT SUM(AMOUND_DUE) GTOTALSIHARAI FROM T_PAYMENT WHERE 1 = 1 AND CD_SUPPLIER = ('&1')::numeric AND DT_PAYMENTSLIP <= TO_DATE('&6', 'yyyy/mm/dd') AND FLG_DELETE = 0 ) TP