protected void Page_Init(object sender, EventArgs e) { DataSetAssetClass imageDataSet = new DataSetAssetClass(); string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection con = new OracleConnection(connStr); string AsMonthYear = Request.QueryString["MonthYear"].ToString(); string GoodsInID = Request.QueryString["GoodsInID"].ToString(); string IsReport = Request.QueryString["IsReport"].ToString(); string MakeMonthYear = Request.QueryString["MonthYear"].ToString(); string[] MakeMonthYearSplit = MakeMonthYear.Split('-'); String MonthYearTemp = MakeMonthYearSplit[0].Replace("/", "-"); DateTime AsMonthYearNew = DateTime.ParseExact(MonthYearTemp, "MM-yyyy", CultureInfo.InvariantCulture); // string AsMonthYearNew = AsOnDateNewD.ToString("MM-yyyy"); string sqlString = "", MonthOf = ""; if (GoodsInID == "0") { if (IsReport == "CurrentMonth") { MonthOf = "Month Of"; sqlString = " SELECT PEWC.EXP_WBCON_ID, TO_CHAR(PEWC.DISPATCH_DATE,'dd/mm/yyyy') AS DISPATCH_DATE, PEWC.WB_SLIP_NO, PEWC.CONTAINER_NO, PEWC.REL_ORDER_NO, nvl(PEWC.ITEM_WEIGHT_WB, 0) AS ITEM_WEIGHT_WB, PEWCI.IS_INVENTORY_STATUS, PI.ITEM_NAME, PEWCI.ITEM_WEIGHT, PEWCI.ITEM_BALES FROM WP_EXPORT_WBSLIP_CON PEWC LEFT JOIN WP_EXPORT_WBSLIP_CON_ITEM PEWCI ON PEWCI.WB_SLIP_NO = PEWC.WB_SLIP_NO LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = PEWCI.ITEM_ID WHERE TO_CHAR(PEWC.DISPATCH_DATE,'mm/yyyy') = '" + AsMonthYear + "' ORDER BY PEWC.DISPATCH_DATE ASC "; } else { MonthOf = "Till Month Of"; sqlString = " SELECT PEWC.EXP_WBCON_ID, TO_CHAR(PEWC.DISPATCH_DATE,'dd/mm/yyyy') AS DISPATCH_DATE, PEWC.WB_SLIP_NO, PEWC.CONTAINER_NO, PEWC.REL_ORDER_NO, nvl(PEWC.ITEM_WEIGHT_WB, 0) AS ITEM_WEIGHT_WB, PEWCI.IS_INVENTORY_STATUS, PI.ITEM_NAME, PEWCI.ITEM_WEIGHT, PEWCI.ITEM_BALES FROM WP_EXPORT_WBSLIP_CON PEWC LEFT JOIN WP_EXPORT_WBSLIP_CON_ITEM PEWCI ON PEWCI.WB_SLIP_NO = PEWC.WB_SLIP_NO LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = PEWCI.ITEM_ID WHERE TO_CHAR(PEWC.DISPATCH_DATE,'mm/yyyy') <= '" + AsMonthYear + "' ORDER BY PEWC.DISPATCH_DATE ASC "; } } else { if (IsReport == "CurrentMonth") { MonthOf = "Month Of"; sqlString = " SELECT PEWC.EXP_WBCON_ID, TO_CHAR(PEWC.DISPATCH_DATE,'dd/mm/yyyy') AS DISPATCH_DATE, PEWC.WB_SLIP_NO, PEWC.CONTAINER_NO, PEWC.REL_ORDER_NO, nvl(PEWC.ITEM_WEIGHT_WB, 0) AS ITEM_WEIGHT_WB, PEWCI.IS_INVENTORY_STATUS, PI.ITEM_NAME, PEWCI.ITEM_WEIGHT, PEWCI.ITEM_BALES FROM WP_EXPORT_WBSLIP_CON PEWC LEFT JOIN WP_EXPORT_WBSLIP_CON_ITEM PEWCI ON PEWCI.WB_SLIP_NO = PEWC.WB_SLIP_NO LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = PEWCI.ITEM_ID WHERE PEWCI.IS_INVENTORY_STATUS = '" + GoodsInID + "' AND TO_CHAR(PEWC.DISPATCH_DATE,'mm/yyyy') = '" + AsMonthYear + "' ORDER BY PEWC.DISPATCH_DATE ASC "; } else { MonthOf = "Till Month Of"; sqlString = " SELECT PEWC.EXP_WBCON_ID, TO_CHAR(PEWC.DISPATCH_DATE,'dd/mm/yyyy') AS DISPATCH_DATE, PEWC.WB_SLIP_NO, PEWC.CONTAINER_NO, PEWC.REL_ORDER_NO, nvl(PEWC.ITEM_WEIGHT_WB, 0) AS ITEM_WEIGHT_WB, PEWCI.IS_INVENTORY_STATUS, PI.ITEM_NAME, PEWCI.ITEM_WEIGHT, PEWCI.ITEM_BALES FROM WP_EXPORT_WBSLIP_CON PEWC LEFT JOIN WP_EXPORT_WBSLIP_CON_ITEM PEWCI ON PEWCI.WB_SLIP_NO = PEWC.WB_SLIP_NO LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = PEWCI.ITEM_ID WHERE PEWCI.IS_INVENTORY_STATUS = '" + GoodsInID + "' AND TO_CHAR(PEWC.DISPATCH_DATE,'mm/yyyy') <= '" + AsMonthYear + "' ORDER BY PEWC.DISPATCH_DATE ASC "; } } OracleCommand cmd = new OracleCommand(sqlString, con); cmd.CommandType = CommandType.Text; cmd.CommandText = sqlString; OracleDataAdapter dt = new OracleDataAdapter(cmd); con.Open(); dt.Fill(imageDataSet.Tables["reportTable"]); con.Close(); crystalReport.Load(Server.MapPath("~/WP/WP_Reports/Wp_ExpContainer_Monthly_Wise_Report.rpt")); crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]); crystalReport.SetParameterValue("MonthOf", MonthOf); crystalReport.SetParameterValue("AsMonthYear", AsMonthYearNew); string datetime = DateTime.Now.ToString("dd-MM-yyyy"); CrystalReportViewer1.ID = "Purchase_Supplier_Statement_Report_(As_On_Date)_" + datetime + ""; CrystalReportViewer1.ReportSource = crystalReport; }
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedTableSchema(global::System.Xml.Schema.XmlSchemaSet xs) { global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType(); global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence(); DataSetAssetClass ds = new DataSetAssetClass(); global::System.Xml.Schema.XmlSchemaAny any1 = new global::System.Xml.Schema.XmlSchemaAny(); any1.Namespace = "http://www.w3.org/2001/XMLSchema"; any1.MinOccurs = new decimal(0); any1.MaxOccurs = decimal.MaxValue; any1.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax; sequence.Items.Add(any1); global::System.Xml.Schema.XmlSchemaAny any2 = new global::System.Xml.Schema.XmlSchemaAny(); any2.Namespace = "urn:schemas-microsoft-com:xml-diffgram-v1"; any2.MinOccurs = new decimal(1); any2.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax; sequence.Items.Add(any2); global::System.Xml.Schema.XmlSchemaAttribute attribute1 = new global::System.Xml.Schema.XmlSchemaAttribute(); attribute1.Name = "namespace"; attribute1.FixedValue = ds.Namespace; type.Attributes.Add(attribute1); global::System.Xml.Schema.XmlSchemaAttribute attribute2 = new global::System.Xml.Schema.XmlSchemaAttribute(); attribute2.Name = "tableTypeName"; attribute2.FixedValue = "reportTableDataTable"; type.Attributes.Add(attribute2); type.Particle = sequence; global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable(); if (xs.Contains(dsSchema.TargetNamespace)) { global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream(); global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream(); try { global::System.Xml.Schema.XmlSchema schema = null; dsSchema.Write(s1); for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();) { schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current)); s2.SetLength(0); schema.Write(s2); if ((s1.Length == s2.Length)) { s1.Position = 0; s2.Position = 0; for (; ((s1.Position != s1.Length) && (s1.ReadByte() == s2.ReadByte()));) { ; } if ((s1.Position == s1.Length)) { return(type); } } } } finally { if ((s1 != null)) { s1.Close(); } if ((s2 != null)) { s2.Close(); } } } xs.Add(dsSchema); return(type); }
protected void Page_Init(object sender, EventArgs e) { DataSetAssetClass imageDataSet = new DataSetAssetClass(); string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection con = new OracleConnection(connStr); string StartDate = Request.QueryString["StartDate"].ToString(); string EndDate = Request.QueryString["EndDate"].ToString(); DateTime StartDateNew = DateTime.ParseExact(StartDate, "dd/mm/yyyy", CultureInfo.InvariantCulture); string StartDateQuery = StartDateNew.ToString("yyyy/mm/dd"); DateTime EndDateNew = DateTime.ParseExact(EndDate, "dd/mm/yyyy", CultureInfo.InvariantCulture); string EndDateQuery = EndDateNew.ToString("yyyy/mm/dd"); string StartDateTemp = Request.QueryString["StartDate"].ToString(); string[] StartDateTempSplit = StartDateTemp.Split('-'); String StartDateFormTemp = StartDateTempSplit[0].Replace("/", "-"); DateTime StartDateFormTempNew = DateTime.ParseExact(StartDateFormTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string StartDateForm = StartDateFormTempNew.ToString("dd-MMMM-yyyy"); string EndDateTemp = Request.QueryString["EndDate"].ToString(); string[] EndDateTempSplit = EndDateTemp.Split('-'); String EndDateFormTemp = EndDateTempSplit[0].Replace("/", "-"); DateTime EndDateFormTempNew = DateTime.ParseExact(EndDateFormTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string EndDateForm = EndDateFormTempNew.ToString("dd-MMMM-yyyy"); string DropDownItem = Request.QueryString["DropDownVehicleID2"].ToString(); string DropDownItemID = DropDownItem.Remove(DropDownItem.Length - 1, 1); string sqlString = " SELECT PD.VEHICLE_MODE_ID AS SUPPLIER_ID, PD.VEHICLE_MODE_NAME AS SUPPLIER_NAME, PI.ITEM_CODE || ' : ' || PI.ITEM_NAME AS ITEM_NAME, nvl(SUM(WPM.ITEM_WEIGHT),0) AS ITEM_WEIGHT, nvl(SUM(WPM.ITEM_AMOUNT),0) AS ITEM_AMOUNT FROM NRC_VEHICLE_MODE PD LEFT JOIN MS_PURCHASE_MASTER WPM ON WPM.VEHICLE_MODE_ID = PD.VEHICLE_MODE_ID LEFT JOIN MF_ITEM PI ON PI.ITEM_ID = WPM.ITEM_ID WHERE TO_CHAR(WPM.ENTRY_DATE, 'yyyy/mm/dd') between '" + StartDateQuery + "' AND '" + EndDateQuery + "' AND ( "; string[] ItemID = DropDownItemID.Split('-'); for (int i = 0; i < ItemID.Length; i++) { sqlString += " WPM.VEHICLE_MODE_ID = '" + ItemID[i] + "' OR"; } sqlString = sqlString.Remove(sqlString.Length - 2, 2) + " ) GROUP BY PD.VEHICLE_MODE_ID, PD.VEHICLE_MODE_NAME, PI.ITEM_CODE || ' : ' || PI.ITEM_NAME ORDER BY PI.ITEM_CODE || ' : ' || PI.ITEM_NAME "; // pnlReport.GroupingText = sqlString; OracleCommand cmd = new OracleCommand(sqlString, con); cmd.CommandType = CommandType.Text; cmd.CommandText = sqlString; OracleDataAdapter dt = new OracleDataAdapter(cmd); con.Open(); dt.Fill(imageDataSet.Tables["reportTable"]); con.Close(); crystalReport.Load(Server.MapPath("~/MS/MS_Reports/Ms_Purchase_Mode_Wise_Report.rpt")); crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]); crystalReport.SetParameterValue("StartDate", StartDateForm); crystalReport.SetParameterValue("EndDate", EndDateForm); string datetime = DateTime.Now.ToString("dd-MM-yyyy"); CrystalReportViewer1.ID = "Purchase_Supplier_Statement_Report_(As_On_Date)_Metal_Scrap_" + datetime + ""; CrystalReportViewer1.ReportSource = crystalReport; }
protected void Page_Init(object sender, EventArgs e) { DataSetAssetClass imageDataSet = new DataSetAssetClass(); string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection con = new OracleConnection(connStr); string StartDate = Request.QueryString["StartDate"].ToString(); string EndDate = Request.QueryString["EndDate"].ToString(); DateTime StartDateNew = DateTime.ParseExact(StartDate, "dd/mm/yyyy", CultureInfo.InvariantCulture); string StartDateQuery = StartDateNew.ToString("yyyy/mm/dd"); DateTime EndDateNew = DateTime.ParseExact(EndDate, "dd/mm/yyyy", CultureInfo.InvariantCulture); string EndDateQuery = EndDateNew.ToString("yyyy/mm/dd"); string StartDateTemp = Request.QueryString["StartDate"].ToString(); string[] StartDateTempSplit = StartDateTemp.Split('-'); String StartDateFormTemp = StartDateTempSplit[0].Replace("/", "-"); DateTime StartDateFormTempNew = DateTime.ParseExact(StartDateFormTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string StartDateForm = StartDateFormTempNew.ToString("dd-MMMM-yyyy"); string EndDateTemp = Request.QueryString["EndDate"].ToString(); string[] EndDateTempSplit = EndDateTemp.Split('-'); String EndDateFormTemp = EndDateTempSplit[0].Replace("/", "-"); DateTime EndDateFormTempNew = DateTime.ParseExact(EndDateFormTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string EndDateForm = EndDateFormTempNew.ToString("dd-MMMM-yyyy"); string CollectionForID = Request.QueryString["CollectionForID"].ToString(); string DropDownItem = Request.QueryString["DropDownItemID"].ToString(); string DropDownItemID = DropDownItem.Remove(DropDownItem.Length - 1, 1); string[] ItemID = DropDownItemID.Split('-'); string sqlString = ""; if (ItemID[0] == "0") { sqlString = " SELECT PP.PARTY_ID AS SUPPLIER_ID, PP.PARTY_NAME AS SUPPLIER_NAME, WCF.COLLECTION_FOR_NAME AS SALESMAN_NAME, PI.ITEM_NAME, PI.ITEM_CODE, TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') AS ENTRY_DATE, WPM.SLIP_NO, WPM.ITEM_WEIGHT, WPM.ITEM_RATE, WPM.ITEM_AMOUNT, WPM.VAT_PERCENT, WPM.VAT_AMOUNT, WPM.TOTAL_AMOUNT FROM WP_PARTY PP LEFT JOIN WP_PURCHASE_MASTER WPM ON WPM.PARTY_ID = PP.PARTY_ID LEFT JOIN WP_COLLECTION_FOR WCF ON WCF.COLLECTION_FOR_ID = WPM.COLLECTION_FOR_ID LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = WPM.ITEM_ID WHERE WPM.COLLECTION_FOR_ID = '" + CollectionForID + "' AND TO_CHAR(WPM.ENTRY_DATE, 'yyyy/mm/dd') between '" + StartDateQuery + "' AND '" + EndDateQuery + "' ORDER BY TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') ASC, WPM.SLIP_NO ASC "; } else { sqlString = " SELECT PP.PARTY_ID AS SUPPLIER_ID, PP.PARTY_NAME AS SUPPLIER_NAME, WCF.COLLECTION_FOR_NAME AS SALESMAN_NAME, PI.ITEM_NAME, PI.ITEM_CODE, TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') AS ENTRY_DATE, WPM.SLIP_NO, WPM.ITEM_WEIGHT, WPM.ITEM_RATE, WPM.ITEM_AMOUNT, WPM.VAT_PERCENT, WPM.VAT_AMOUNT, WPM.TOTAL_AMOUNT FROM WP_PARTY PP LEFT JOIN WP_PURCHASE_MASTER WPM ON WPM.PARTY_ID = PP.PARTY_ID LEFT JOIN WP_COLLECTION_FOR WCF ON WCF.COLLECTION_FOR_ID = WPM.COLLECTION_FOR_ID LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = WPM.ITEM_ID WHERE WPM.COLLECTION_FOR_ID = '" + CollectionForID + "' AND TO_CHAR(WPM.ENTRY_DATE, 'yyyy/mm/dd') between '" + StartDateQuery + "' AND '" + EndDateQuery + "' AND ( "; for (int i = 0; i < ItemID.Length; i++) { sqlString += " WPM.ITEM_ID = '" + ItemID[i] + "' OR"; } sqlString = sqlString.Remove(sqlString.Length - 2, 2) + " ) ORDER BY TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') ASC, WPM.SLIP_NO ASC "; } // pnlReport.GroupingText = sqlString; OracleCommand cmd = new OracleCommand(sqlString, con); cmd.CommandType = CommandType.Text; cmd.CommandText = sqlString; OracleDataAdapter dt = new OracleDataAdapter(cmd); con.Open(); dt.Fill(imageDataSet.Tables["reportTable"]); con.Close(); crystalReport.Load(Server.MapPath("~/WP/WP_Reports/Wp_Purchase_Collection_For_Wise_Report.rpt")); crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]); crystalReport.SetParameterValue("StartDate", StartDateForm); crystalReport.SetParameterValue("EndDate", EndDateForm); string datetime = DateTime.Now.ToString("dd-MM-yyyy"); CrystalReportViewer1.ID = "Wp_Purchase_Collection_For_Wise_Report_(As_On_Date)_Waste_Paper_" + datetime + ""; CrystalReportViewer1.ReportSource = crystalReport; }
protected void Page_Init(object sender, EventArgs e) { DataSetAssetClass imageDataSet = new DataSetAssetClass(); string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection conn = new OracleConnection(connStr); string AsOnDate = Request.QueryString["AsOnDate"].ToString(); string IsReport = Request.QueryString["IsReport"].ToString(); string MakeAsOnDate = Request.QueryString["AsOnDate"].ToString(); string[] MakeAsOnDateSplit = MakeAsOnDate.Split('-'); String AsOnDateTemp = MakeAsOnDateSplit[0].Replace("/", "-"); DateTime AsOnDateNewD = DateTime.ParseExact(AsOnDateTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string AsOnDateNew = AsOnDateNewD.ToString("dd-MM-yyyy"); DateTime curDate = AsOnDateNewD; DateTime startDate = curDate.AddMonths(-1); DateTime LastDateTemp = curDate.AddDays(-(curDate.Day)); string LastDate = LastDateTemp.ToString("dd-MM-yyyy"); string LastMonth = startDate.ToString("MM-yyyy"); string CurrentMonth = AsOnDateNewD.ToString("MM-yyyy"); // string sqlString = " SELECT WI.ITEM_ID, WI.ITEM_NAME, nvl(BEGWRSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) AS BEG_AMT, ROUND(nvl(nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) / BEGWRSIH.FINAL_STOCK_WT, 0), 3)*1000 AS BEG_AVG_RATE, nvl(WPM.ITEM_WEIGHT, 0) AS PURCHASE_WT, nvl(WPM.ITEM_AMOUNT, 0) AS PURCHASE_AMT, nvl(ROUND((nvl(WPM.ITEM_AMOUNT, 0) / nullif(nvl(WPM.ITEM_WEIGHT, 0), 0)) * 1000, 2), 0) AS PURCHASE_AVG_RATE, ROUND((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100, 2) AS GAR_EST_WT, ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 2) AS PURCHASE_NET_WT, nvl(ROUND(nvl(WPM.ITEM_AMOUNT, 0) / nullif(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 0), 2) * 1000, 0) AS PURCHASE_NET_AVG_RATE, nvl(BEGWRSIH.FINAL_STOCK_WT, 0)+ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 2) AS PURCHASE_NET_GAR_EST_WT, nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) +nvl(WPM.ITEM_AMOUNT, 0) AS PURCHASE_BEG_AMT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4)*1000 AS PURCHASE_BEG_AVG_RATE, nvl(WMTI.ITEM_WEIGHT, 0) AS MAT_ISSUED_WT, nvl(WMTR.ITEM_WEIGHT, 0) AS MAT_RECEVIED_WT, nvl(WMTRM.ITEM_WEIGHT, 0) AS MAT_TRANSFER_DEDUC_WT, ROUND((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0), 2) AS MAT_TRANSFER_DEDUC_AMT, nvl(WMTT.ITEM_WEIGHT, 0) AS MAT_TRANSFER_WT, nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0) AS PURCHASE_TRANS_AVG_RATE, ROUND((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000, 2) AS MAT_TRANSFER_AMT, nvl(BEGWRSIH.FINAL_STOCK_WT, 0)+ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))) AS SALES_AVAIL_WT, ROUND(nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ROUND((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000, 2) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0))), 2) AS SALES_AVAIL_AMT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0)))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100)) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0) * 1000, 2) AS SALES_AVAIL_AVG_RATE, nvl(WEWCI.ITEM_WEIGHT, 0) AS SALES_OVERSEAS_WT, nvl(WSM.ITEM_WEIGHT, 0) AS SALES_LOCAL_WT, nvl(WSIDM.ITEM_WEIGHT, 0) AS SALES_INTER_DIV_WT, (nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) AS END_FSTOCK_WT, ROUND(((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0))) * (((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0)))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100)) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0))), 2) AS END_AMT, ROUND((((((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0))) * (ROUND(((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000)) - ((ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4)) * nvl(WMTRM.ITEM_WEIGHT, 0))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0) * 1000, 2))))) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)), 0), 2) AS END_AVG_RATE, nvl(WEWCIT.ITEM_WEIGHT, 0) AS ITEM_WEIGHT_TRANSIT, ((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)))-nvl(WEWCIT.ITEM_WEIGHT, 0) AS END_AS_PER_BOOK FROM WP_ITEM WI LEFT JOIN(SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_END_AMOUNT FROM WP_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGWRSIH ON WI.ITEM_ID = BEGWRSIH.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS ITEM_AMOUNT FROM WP_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WPM ON WI.ITEM_ID = WPM.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTI ON WI.ITEM_ID = WMTI.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 2 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTR ON WI.ITEM_ID = WMTR.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 3 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTRM ON WI.ITEM_ID = WMTRM.ITEM_ID LEFT JOIN(SELECT WMT.ITEM_ID, WMT.ITEM_TRANSFER_ID, sum(nvl(WMT.ITEM_WEIGHT,0)) AS ITEM_WEIGHT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4)*1000 AS PURCHASE_TRANS_AVG_RATE FROM WP_MATERIAL_TRANSACTION WMT LEFT JOIN(SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_END_AMOUNT FROM WP_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGWRSIH ON WMT.ITEM_ID = BEGWRSIH.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS ITEM_AMOUNT FROM WP_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WPM ON WMT.ITEM_ID = WPM.ITEM_ID WHERE WMT.TRANSACTION_FOR_ID = 3 AND TO_CHAR(TO_DATE(WMT.ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY WMT.ITEM_ID, WMT.ITEM_TRANSFER_ID, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4) * 1000) WMTT ON WI.ITEM_ID = WMTT.ITEM_TRANSFER_ID LEFT JOIN(SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM WP_EXPORT_WBSLIP_CON_ITEM WHERE IS_INVENTORY_STATUS = 'Complete' AND TO_CHAR(TO_DATE(IS_SHIPMENT_COMPLETE_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WEWCI ON WI.ITEM_ID = WEWCI.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM WP_EXPORT_WBSLIP_CON_ITEM WHERE IS_INVENTORY_STATUS = 'Transit' AND (TO_CHAR(TO_DATE(SYSDATE), 'mm-YYYY') = '" + CurrentMonth + "' OR TO_CHAR(TO_DATE(DISPATCH_DATE), 'mm-YYYY') = '" + CurrentMonth + "') GROUP BY ITEM_ID) WEWCIT ON WI.ITEM_ID = WEWCIT.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, SUM(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS TOTAL_AMOUNT FROM WP_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WSM ON WI.ITEM_ID = WSM.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_SALES_INTER_DIV_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WSIDM ON WI.ITEM_ID = WSIDM.ITEM_ID WHERE(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) > 0 OR(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) > 0) ORDER BY WI.ITEM_ID "; // string sqlString = " SELECT WI.ITEM_ID, WI.ITEM_NAME, nvl(BEGWRSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) AS BEG_AMT, ROUND(nvl(nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) / nullif(BEGWRSIH.FINAL_STOCK_WT, 0), 3)*1000, 0) AS BEG_AVG_RATE, nvl(WPM.ITEM_WEIGHT, 0) AS PURCHASE_WT, nvl(WPM.ITEM_AMOUNT, 0) AS PURCHASE_AMT, nvl(ROUND((nvl(WPM.ITEM_AMOUNT, 0) / nullif(nvl(WPM.ITEM_WEIGHT, 0), 0)) * 1000, 2), 0) AS PURCHASE_AVG_RATE, ROUND((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100, 2) AS GAR_EST_WT, ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 2) AS PURCHASE_NET_WT, nvl(ROUND(nvl(WPM.ITEM_AMOUNT, 0) / nullif(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 0), 2) * 1000, 0) AS PURCHASE_NET_AVG_RATE, nvl(BEGWRSIH.FINAL_STOCK_WT, 0)+ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 2) AS PURCHASE_NET_GAR_EST_WT, nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) +nvl(WPM.ITEM_AMOUNT, 0) AS PURCHASE_BEG_AMT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4)*1000 AS PURCHASE_BEG_AVG_RATE, nvl(WMTI.ITEM_WEIGHT, 0) AS MAT_ISSUED_WT, nvl(WMTR.ITEM_WEIGHT, 0) AS MAT_RECEVIED_WT, nvl(WMTRM.ITEM_WEIGHT, 0) AS MAT_TRANSFER_DEDUC_WT, ROUND((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0), 2) AS MAT_TRANSFER_DEDUC_AMT, nvl(WMTT.ITEM_WEIGHT, 0) AS MAT_TRANSFER_WT, nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0) AS PURCHASE_TRANS_AVG_RATE, ROUND((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000, 2) AS MAT_TRANSFER_AMT, nvl(BEGWRSIH.FINAL_STOCK_WT, 0)+ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))) AS SALES_AVAIL_WT, ROUND(nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ROUND((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000, 2) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0))), 2) AS SALES_AVAIL_AMT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0)))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100)) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0) * 1000, 2) AS SALES_AVAIL_AVG_RATE, nvl(WEWCI.ITEM_WEIGHT, 0) AS SALES_OVERSEAS_WT, nvl(WSM.ITEM_WEIGHT, 0) AS SALES_LOCAL_WT, nvl(WSIDM.ITEM_WEIGHT, 0) AS SALES_INTER_DIV_WT, (nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) AS END_FSTOCK_WT, ROUND(((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0))) * (((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0)))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100)) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0))), 2) AS END_AMT, ROUND((((((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0))) * (ROUND(((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000)) - ((ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4)) * nvl(WMTRM.ITEM_WEIGHT, 0))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0) * 1000, 2))))) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)), 0), 2) AS END_AVG_RATE, nvl(WEWCIT.ITEM_WEIGHT, 0) AS ITEM_WEIGHT_TRANSIT, ((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)))-nvl(WEWCIT.ITEM_WEIGHT, 0) AS END_AS_PER_BOOK FROM WP_ITEM WI LEFT JOIN(SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_END_AMOUNT FROM WP_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGWRSIH ON WI.ITEM_ID = BEGWRSIH.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS ITEM_AMOUNT FROM WP_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WPM ON WI.ITEM_ID = WPM.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTI ON WI.ITEM_ID = WMTI.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 2 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTR ON WI.ITEM_ID = WMTR.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 3 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTRM ON WI.ITEM_ID = WMTRM.ITEM_ID LEFT JOIN(SELECT WMT.ITEM_ID, WMT.ITEM_TRANSFER_ID, sum(nvl(WMT.ITEM_WEIGHT,0)) AS ITEM_WEIGHT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4)*1000 AS PURCHASE_TRANS_AVG_RATE FROM WP_MATERIAL_TRANSACTION WMT LEFT JOIN(SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_END_AMOUNT FROM WP_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGWRSIH ON WMT.ITEM_ID = BEGWRSIH.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS ITEM_AMOUNT FROM WP_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WPM ON WMT.ITEM_ID = WPM.ITEM_ID WHERE WMT.TRANSACTION_FOR_ID = 3 AND TO_CHAR(TO_DATE(WMT.ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY WMT.ITEM_ID, WMT.ITEM_TRANSFER_ID, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100))), 0), 4) * 1000) WMTT ON WI.ITEM_ID = WMTT.ITEM_TRANSFER_ID LEFT JOIN(SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM WP_EXPORT_WBSLIP_CON_ITEM WHERE IS_INVENTORY_STATUS = 'Complete' AND TO_CHAR(TO_DATE(IS_SHIPMENT_COMPLETE_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WEWCI ON WI.ITEM_ID = WEWCI.ITEM_ID LEFT JOIN(SELECT WEWCI.ITEM_ID, sum(WEWCI.ITEM_WEIGHT) AS ITEM_WEIGHT FROM WP_EXPORT_WBSLIP_CON_ITEM WEWCI LEFT JOIN WP_EXPORT_WBSLIP_CON WEWC ON WEWC.WB_SLIP_NO = WEWCI.WB_SLIP_NO WHERE WEWCI.IS_INVENTORY_STATUS = 'Transit' AND (TO_CHAR(TO_DATE(WEWC.DISPATCH_DATE), 'mm-YYYY') <= '" + CurrentMonth + "') GROUP BY WEWCI.ITEM_ID) WEWCIT ON WI.ITEM_ID = WEWCIT.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, SUM(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS TOTAL_AMOUNT FROM WP_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WSM ON WI.ITEM_ID = WSM.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM WP_SALES_INTER_DIV_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WSIDM ON WI.ITEM_ID = WSIDM.ITEM_ID WHERE ((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) > 0 OR nvl(BEGWRSIH.FINAL_STOCK_WT, 0) > 0 OR (nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - ((nvl(WPM.ITEM_WEIGHT, 0) * 2) / 100), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) > 0 ) ORDER BY WI.ITEM_ID "; // TO_CHAR(TO_DATE(SYSDATE), 'mm-YYYY') = '" + CurrentMonth + "' OR string sqlString = " SELECT WI.ITEM_ID, WI.ITEM_CODE || ' : ' || WI.ITEM_NAME AS ITEM_NAME, nvl(BEGWRSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) AS BEG_AMT, ROUND(nvl(nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) / nullif(BEGWRSIH.FINAL_STOCK_WT, 0), 3)*1000, 0) AS BEG_AVG_RATE, nvl(WPM.ITEM_WEIGHT, 0) AS PURCHASE_WT, nvl(WPM.ITEM_AMOUNT, 0) AS PURCHASE_AMT, nvl(ROUND((nvl(WPM.ITEM_AMOUNT, 0) / nullif(nvl(WPM.ITEM_WEIGHT, 0), 0)) * 1000, 2), 0) AS PURCHASE_AVG_RATE, ROUND(nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 2) AS GAR_EST_WT, ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 2) AS PURCHASE_NET_WT, nvl(ROUND(nvl(WPM.ITEM_AMOUNT, 0) / nullif(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 0), 2) * 1000, 0) AS PURCHASE_NET_AVG_RATE, nvl(BEGWRSIH.FINAL_STOCK_WT, 0)+ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 2) AS PURCHASE_NET_GAR_EST_WT, nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) +nvl(WPM.ITEM_AMOUNT, 0) AS PURCHASE_BEG_AMT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0), 4)*1000 AS PURCHASE_BEG_AVG_RATE, nvl(WMTI.ITEM_WEIGHT, 0) AS MAT_ISSUED_WT, nvl(WMTR.ITEM_WEIGHT, 0) AS MAT_RECEVIED_WT, nvl(WMTRM.ITEM_WEIGHT, 0) AS MAT_TRANSFER_DEDUC_WT, ROUND((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0), 2) AS MAT_TRANSFER_DEDUC_AMT, nvl(WMTT.ITEM_WEIGHT, 0) AS MAT_TRANSFER_WT, nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0) AS PURCHASE_TRANS_AVG_RATE, ROUND((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000, 2) AS MAT_TRANSFER_AMT, nvl(BEGWRSIH.FINAL_STOCK_WT, 0)+ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))) AS SALES_AVAIL_WT, ROUND(nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ROUND((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000, 2) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0))), 2) AS SALES_AVAIL_AMT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0)))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0)) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0) * 1000, 2) AS SALES_AVAIL_AVG_RATE, nvl(WEWCI.ITEM_WEIGHT, 0) AS SALES_OVERSEAS_WT, nvl(WSM.ITEM_WEIGHT, 0) AS SALES_LOCAL_WT, nvl(WSIDM.ITEM_WEIGHT, 0) AS SALES_INTER_DIV_WT, (nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) AS END_FSTOCK_WT, ROUND(((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0))) * (((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000) - (((((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0))) * nvl(WMTRM.ITEM_WEIGHT, 0)))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0)) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0))), 2) AS END_AMT, ROUND((((((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0))) * (ROUND(((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0) + ((nvl(WMTT.ITEM_WEIGHT, 0) * nvl(WMTT.PURCHASE_TRANS_AVG_RATE, 0)) / 1000)) - ((ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0), 4)) * nvl(WMTRM.ITEM_WEIGHT, 0))) / nullif(nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0)))), 0) * 1000, 2))))) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)), 0), 2) AS END_AVG_RATE, nvl(WEWCIT.ITEM_WEIGHT, 0) AS ITEM_WEIGHT_TRANSIT, ((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)))-nvl(WEWCIT.ITEM_WEIGHT, 0) AS END_AS_PER_BOOK FROM MF_ITEM WI LEFT JOIN(SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_END_AMOUNT FROM MS_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGWRSIH ON WI.ITEM_ID = BEGWRSIH.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS ITEM_AMOUNT FROM MS_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WPM ON WI.ITEM_ID = WPM.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM MS_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTI ON WI.ITEM_ID = WMTI.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM MS_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 2 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTR ON WI.ITEM_ID = WMTR.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM MS_MATERIAL_TRANSACTION WHERE TRANSACTION_FOR_ID = 3 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WMTRM ON WI.ITEM_ID = WMTRM.ITEM_ID LEFT JOIN(SELECT WMT.ITEM_ID, WMT.ITEM_TRANSFER_ID, sum(nvl(WMT.ITEM_WEIGHT,0)) AS ITEM_WEIGHT, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0), 4)*1000 AS PURCHASE_TRANS_AVG_RATE FROM MS_MATERIAL_TRANSACTION WMT LEFT JOIN(SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_END_AMOUNT FROM MS_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGWRSIH ON WMT.ITEM_ID = BEGWRSIH.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS ITEM_AMOUNT FROM MS_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WPM ON WMT.ITEM_ID = WPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, nvl(sum(nvl(ACTUAL_GAR_WEIGHT, 0)), 0) AS ACTUAL_GAR_WEIGHT FROM MS_ACTUAL_GARBAGE WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) MAGAR ON WMT.ITEM_ID = MAGAR.ITEM_ID WHERE WMT.TRANSACTION_FOR_ID = 3 AND TO_CHAR(TO_DATE(WMT.ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY WMT.ITEM_ID, WMT.ITEM_TRANSFER_ID, ROUND((nvl(BEGWRSIH.ITEM_END_AMOUNT, 0) + nvl(WPM.ITEM_AMOUNT, 0)) / nullif((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + (nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0))), 0), 4) * 1000) WMTT ON WI.ITEM_ID = WMTT.ITEM_TRANSFER_ID LEFT JOIN(SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM MS_EXPORT_WBSLIP_CON_ITEM WHERE IS_INVENTORY_STATUS = 'Complete' AND TO_CHAR(TO_DATE(IS_SHIPMENT_COMPLETE_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WEWCI ON WI.ITEM_ID = WEWCI.ITEM_ID LEFT JOIN(SELECT WEWCI.ITEM_ID, sum(WEWCI.ITEM_WEIGHT) AS ITEM_WEIGHT FROM MS_EXPORT_WBSLIP_CON_ITEM WEWCI LEFT JOIN MS_EXPORT_WBSLIP_CON WEWC ON WEWC.WB_SLIP_NO = WEWCI.WB_SLIP_NO WHERE WEWCI.IS_INVENTORY_STATUS = 'Transit' AND (TO_CHAR(TO_DATE(SYSDATE), 'mm-YYYY') = '" + CurrentMonth + "' OR TO_CHAR(TO_DATE(WEWC.DISPATCH_DATE), 'mm-YYYY') <= '" + CurrentMonth + "') GROUP BY WEWCI.ITEM_ID) WEWCIT ON WI.ITEM_ID = WEWCIT.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, SUM(nvl(ITEM_AMOUNT, 0) + nvl(VAT_AMOUNT, 0)) AS TOTAL_AMOUNT FROM MS_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WSM ON WI.ITEM_ID = WSM.ITEM_ID LEFT JOIN(SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT FROM MS_SALES_INTER_DIV_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) WSIDM ON WI.ITEM_ID = WSIDM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, nvl(sum(nvl(ACTUAL_GAR_WEIGHT, 0)), 0) AS ACTUAL_GAR_WEIGHT FROM MS_ACTUAL_GARBAGE WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) MAGAR ON WI.ITEM_ID = MAGAR.ITEM_ID WHERE ((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) > 0 OR nvl(BEGWRSIH.FINAL_STOCK_WT, 0) > 0 OR (nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) > 0 ) OR nvl(WPM.ITEM_WEIGHT, 0) > 0 ORDER BY WI.ITEM_ID "; // WHERE ((nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) + (nvl(WMTR.ITEM_WEIGHT, 0) + nvl(WMTT.ITEM_WEIGHT, 0) - ((nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))))) -(nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) > 0 OR nvl(BEGWRSIH.FINAL_STOCK_WT, 0) > 0 OR (nvl(BEGWRSIH.FINAL_STOCK_WT, 0) + ROUND(nvl(WPM.ITEM_WEIGHT, 0) - nvl(MAGAR.ACTUAL_GAR_WEIGHT, 0), 4) - (nvl(WMTRM.ITEM_WEIGHT, 0) + nvl(WMTI.ITEM_WEIGHT, 0) + nvl(WMTRM.ITEM_WEIGHT, 0))) - (nvl(WEWCI.ITEM_WEIGHT, 0) + nvl(WSM.ITEM_WEIGHT, 0) + nvl(WSIDM.ITEM_WEIGHT, 0)) > 0 ) /* string sqlString = "PRO_WP_INVENTORY_VALUATION_RPT"; * conn.Open(); * OracleCommand cmd = new OracleCommand(sqlString, conn); * * cmd.Parameters.Add(new OracleParameter("TextLastDate", OracleType.VarChar)).Value = LastDate; * cmd.Parameters.Add(new OracleParameter("TextLastMonth", OracleType.VarChar)).Value = LastMonth; * cmd.Parameters.Add(new OracleParameter("TextCurrentMonth", OracleType.VarChar)).Value = CurrentMonth; * cmd.CommandText = sqlString;cmd.CommandType = CommandType.StoredProcedure; * // cmd.ExecuteNonQuery(); * // cmdl = new OracleCommand(makeSQL); * // oradata = new OracleDataAdapter(cmdl.CommandText, conn); * // dt = new DataTable(); * // oradata.Fill(dt); * using (OracleDataAdapter dt = new OracleDataAdapter(cmd)) * { * dt.Fill(imageDataSet.Tables["reportTable"]); * } * * // OracleDataAdapter dt = new OracleDataAdapter(cmd); * // ds = new DataTable(); * // oradata.Fill(ds); * // dt.SelectCommand = cmd; * // dt.Fill(imageDataSet.Tables["reportTable"]); * conn.Close(); * */ OracleCommand cmd = new OracleCommand(sqlString, conn); cmd.CommandType = CommandType.Text; cmd.CommandText = sqlString; OracleDataAdapter dt = new OracleDataAdapter(cmd); conn.Open(); dt.Fill(imageDataSet.Tables["reportTable"]); conn.Close(); if (IsReport == "1") { crystalReport.Load(Server.MapPath("~/MS/Ms_Reports/MsInventoryStatementReport.rpt")); string datetime = DateTime.Now.ToString("dd-MM-yyyy"); CrystalReportViewer1.ID = "Metal_Scrap_Inventory_Valuation_Report_(As_On_Date)_" + datetime + ""; } else { crystalReport.Load(Server.MapPath("~/MS/Ms_Reports/MsInventoryValuationStatementReport.rpt")); string datetime = DateTime.Now.ToString("dd-MM-yyyy"); CrystalReportViewer1.ID = "Metal_Scrap_Inventory_Valuation_Statement_Report_(As_On_Date)_" + datetime + ""; } crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]); crystalReport.SetParameterValue("AsOnDate", AsOnDateNewD); CrystalReportViewer1.ReportSource = crystalReport; }