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;
}
