public string UpdateArrival(T_ARRIVAL_HDR ArrHdr, List <T_ARRIVAL_DTL> lstArrDtl, string userid)
{
string resultMsg = string.Empty;
try
{
#region "Transaction Header"
ProcParam procHeader = new ProcParam(11)
{
ProcedureName = "MTL_IN_PACK.T_ARRIVAL_HDR_UPD"
};
procHeader.AddParamInput(0, "strARRIVAL_NO", ArrHdr.ARRIVAL_NO);
procHeader.AddParamInput(1, "strARRIVAL_DATE", ArrHdr.ARRIVAL_DATE);
procHeader.AddParamInput(2, "strWH_ID", ArrHdr.WH_ID);
procHeader.AddParamInput(3, "strPARTY_ID", ArrHdr.PARTY_ID);
procHeader.AddParamInput(4, "strREF_NO ", ArrHdr.REF_NO);
procHeader.AddParamInput(5, "strREF_DATE", ArrHdr.REF_DATE);
procHeader.AddParamInput(6, "strREMARK", ArrHdr.REMARK);
procHeader.AddParamInput(7, "strUSER_ID", ArrHdr.USER_ID);
procHeader.AddParamInput(8, "strREC_STAT", ArrHdr.REC_STAT ? "Y" : "N");
procHeader.AddParamInput(9, "strARR_TYPE", ArrHdr.ARR_TYPE);
procHeader.AddParamOutput(10, "RESULTMSG", OracleDbType.NVarchar2, 255, "OK");
#endregion "Transaction Header"
#region "Transaction Detail"
#region Arrival Delete #Flag=0
var delArr = from arr in lstArrDtl
where arr.FLAG == 0
select arr;
ProcParam paramDel = null;
if (delArr.Any() && delArr.Count() > 0)
{
paramDel = new ProcParam(4)
{
ProcedureName = "MTL_IN_PACK.T_ARRIVAL_DTL_DEL"
};
//strUNIT_CODE
var arrARRIVAL_NO = (from arr in delArr
select arr.ARRIVAL_NO).ToArray();
paramDel.AddParamInput(0, "strARRIVAL_NO", arrARRIVAL_NO, OracleDbType.Varchar2);
var arrLINE_NO = (from arr in delArr
select(object) arr.LINE_NO).ToArray();
paramDel.AddParamInput(1, "strLINE_NO", arrLINE_NO, OracleDbType.Int32);
//User id
paramDel.AddParamInput(2, "strUSER_ID", ArrayOf <object> .Create(delArr.Count(), userid), OracleDbType.Varchar2);
//RESULTMSG
paramDel.AddParamOutput(3, "RESULTMSG", OracleDbType.NVarchar2, 255, "OK", delArr.Count());
}
#endregion
#region Arrival Insert #Flag=2
var insArr = from arr in lstArrDtl
where arr.FLAG == 2
select arr;
ProcParam paramIns = null;
if (insArr.Any() && insArr.Count() > 0)
{
paramIns = new ProcParam(10)
{
ProcedureName = "MTL_IN_PACK.T_ARRIVAL_DTL_INS"
};
var arrARRIVAL_NO = (from arr in insArr
select arr.ARRIVAL_NO).ToArray();
paramIns.AddParamInput(0, "strARRIVAL_NO", arrARRIVAL_NO, OracleDbType.Varchar2);
var arrLINE_NO = (from arr in insArr
select(object) arr.LINE_NO).ToArray();
paramIns.AddParamInput(1, "strLINE_NO", arrLINE_NO, OracleDbType.Int32);
var arrMTL_SEQ_NO = (from arr in insArr
select arr.MTL_SEQ_NO).ToArray();
paramIns.AddParamInput(2, "strMTL_SEQ_NO", arrMTL_SEQ_NO, OracleDbType.Varchar2);
var arrUNIT_ID = (from arr in insArr
select arr.UNIT_ID).ToArray();
paramIns.AddParamInput(3, "strUNIT_ID", arrUNIT_ID, OracleDbType.Varchar2);
var arrQTY = (from arr in insArr
select(object) arr.QTY).ToArray();
paramIns.AddParamInput(4, "strQTY", arrQTY, OracleDbType.Decimal);
var arrREMARK = (from arr in insArr
select arr.REMARK).ToArray();
paramIns.AddParamInput(5, "strREMARK", arrREMARK, OracleDbType.Varchar2);
//strCREATED_BY
paramIns.AddParamInput(6, "strUSER_ID", ArrayOf <object> .Create(insArr.Count(), userid), OracleDbType.Varchar2);
//REC_STATUS
paramIns.AddParamInput(7, "strREC_STAT", ArrayOf <object> .Create(insArr.Count(), "Y"), OracleDbType.Varchar2);
var arrLOT_DATE = (from arr in insArr
select(object) arr.LOT_DATE).ToArray();
paramIns.AddParamInput(8, "strLOT_DATE", arrLOT_DATE, OracleDbType.Date);
//RESULTMSG
paramIns.AddParamOutput(9, "RESULTMSG", OracleDbType.Varchar2, 255, "OK", insArr.Count());
}
#endregion
#region Arrival Update #Flag=3
var updArr = from arr in lstArrDtl
where arr.FLAG == 3
select arr;
ProcParam paramUpd = null;
if (updArr.Any() && updArr.Count() > 0)
{
paramUpd = new ProcParam(9)
{
ProcedureName = "MTL_IN_PACK.T_ARRIVAL_DTL_UPD"
};
var arrARRIVAL_NO = (from arr in updArr
select arr.ARRIVAL_NO).ToArray();
paramUpd.AddParamInput(0, "strARRIVAL_NO", arrARRIVAL_NO, OracleDbType.Varchar2);
var arrLINE_NO = (from arr in updArr
select(object) arr.LINE_NO).ToArray();
paramUpd.AddParamInput(1, "strLINE_NO", arrLINE_NO, OracleDbType.Int32);
var arrMTL_SEQ_NO = (from arr in updArr
select arr.MTL_SEQ_NO).ToArray();
paramUpd.AddParamInput(2, "strMTL_SEQ_NO", arrMTL_SEQ_NO, OracleDbType.Varchar2);
var arrUNIT_ID = (from arr in updArr
select arr.UNIT_ID).ToArray();
paramUpd.AddParamInput(3, "strUNIT_ID", arrUNIT_ID, OracleDbType.Varchar2);
var arrQTY = (from arr in updArr
select(object) arr.QTY).ToArray();
paramUpd.AddParamInput(4, "strQTY", arrQTY, OracleDbType.Decimal);
var arrREMARK = (from arr in updArr
select arr.REMARK).ToArray();
paramUpd.AddParamInput(5, "strREMARK", arrREMARK, OracleDbType.Varchar2);
//strCREATED_BY
paramUpd.AddParamInput(6, "strUSER_ID", ArrayOf <object> .Create(updArr.Count(), userid), OracleDbType.Varchar2);
//REC_STATUS
paramUpd.AddParamInput(7, "strREC_STAT", ArrayOf <object> .Create(updArr.Count(), "Y"), OracleDbType.Varchar2);
//RESULTMSG
paramUpd.AddParamOutput(8, "RESULTMSG", OracleDbType.Varchar2, 255, "OK", updArr.Count());
}
#endregion
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(procHeader, paramDel, delArr.Count(), paramIns, insArr.Count(), paramUpd, updArr.Count());
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
resultMsg = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public List <T_ARRIVAL_HDR> GetArrivalHeaderList(string FindAll, string arrival_no, DateTime?formDate, DateTime?todate, string whId)
{
List <T_ARRIVAL_HDR> arrivalList = null;
T_ARRIVAL_HDR objArrival;
try
{
ProcParam param = new ProcParam(6)
{
ProcedureName = "MTL_IN_PACK.GET_ARRIVAL_HDR"
};
param.AddParamRefCursor(0, "io_cursor");
param.AddParamInput(1, "strFindAll", FindAll);
param.AddParamInput(2, "strARRIVAL_NO", arrival_no);
if (formDate.HasValue)
{
param.AddParamInput(3, "strREF_DT_FROM", formDate.Value);
}
else
{
param.AddParamInput(3, "strREF_DT_FROM", DBNull.Value);
}
if (todate.HasValue)
{
param.AddParamInput(4, "strREF_DT_TO", todate.Value);
}
else
{
param.AddParamInput(4, "strREF_DT_TO", DBNull.Value);
}
param.AddParamInput(5, "strWH_ID", whId);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(param);
if (OraDataReader.Instance.HasRows) //reader.HasRows
{
OraDataReader.Instance.OraReader.FetchSize = OraDataReader.Instance.OraReader.RowSize * 1000;
arrivalList = new List <T_ARRIVAL_HDR>();
while (OraDataReader.Instance.OraReader.Read()) //reader.Read()
{
objArrival = new T_ARRIVAL_HDR(); //reader.GetOrdinal()
objArrival.ARRIVAL_NO = OraDataReader.Instance.GetString("ARRIVAL_NO");
objArrival.ARR_TYPE = OraDataReader.Instance.GetString("ARR_TYPE");
//objArrival.ARRIVAL_TYPE_DESC = OraDataReader.Instance.GetString("ARRIVAL_TYPE_DESC");
if (!OraDataReader.Instance.IsDBNull("ARRIVAL_DATE"))
{
objArrival.ARRIVAL_DATE = OraDataReader.Instance.GetDateTime("ARRIVAL_DATE");
}
objArrival.PARTY_ID = OraDataReader.Instance.GetString("PARTY_ID");
objArrival.PARTY_NAME = OraDataReader.Instance.GetString("PARTY_NAME");
if (!OraDataReader.Instance.IsDBNull("REF_DATE"))
{
objArrival.REF_DATE = OraDataReader.Instance.GetDateTime("REF_DATE");
}
objArrival.REF_NO = OraDataReader.Instance.GetString("REF_NO");
objArrival.WH_ID = OraDataReader.Instance.GetString("WH_ID");
objArrival.REMARK = OraDataReader.Instance.GetString("REMARK");
objArrival.REC_STAT = OraDataReader.Instance.GetString("REC_STAT").Equals("Y");
objArrival.STATUS = OraDataReader.Instance.GetString("STATUS");
objArrival.ARR_TYPE = OraDataReader.Instance.GetString("ARR_TYPE");
arrivalList.Add(objArrival);
}
}
// always call Close when done reading.
//reader.Close();
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
arrivalList = null;
throw ex;
}
return(arrivalList);
}
public string InsertArrival(T_ARRIVAL_HDR ArrHdr, List <T_ARRIVAL_DTL> lstArrDtl, string userid, out string ARRIVAL_NO)
{
string resultMsg = string.Empty;
ARRIVAL_NO = string.Empty;
try
{
#region "Transaction Header"
ProcParam procHeader = new ProcParam(11)
{
ProcedureName = "MTL_IN_PACK.T_ARRIVAL_HDR_INS"
};
procHeader.AddParamInOutput(0, "strARRIVAL_NO", OracleDbType.NVarchar2, 30, ArrHdr.ARRIVAL_NO);
procHeader.AddParamInput(1, "strARRIVAL_DATE", ArrHdr.ARRIVAL_DATE);
procHeader.AddParamInput(2, "strWH_ID", ArrHdr.WH_ID);
procHeader.AddParamInput(3, "strPARTY_ID", ArrHdr.PARTY_ID);
procHeader.AddParamInput(4, "strREF_NO ", ArrHdr.REF_NO);
procHeader.AddParamInput(5, "strREF_DATE", ArrHdr.REF_DATE);
procHeader.AddParamInput(6, "strREMARK", ArrHdr.REMARK);
procHeader.AddParamInput(7, "strUSER_ID", ArrHdr.USER_ID);
procHeader.AddParamInput(8, "strREC_STAT", ArrHdr.REC_STAT ? "Y" : "N");
procHeader.AddParamInput(9, "strARR_TYPE", ArrHdr.ARR_TYPE);
procHeader.AddParamOutput(10, "RESULTMSG", OracleDbType.NVarchar2, 255, "OK");
#endregion "Transaction Header"
#region "Transaction Detail"
#region Arrival Insert #Flag=2
var insArr = from arr in lstArrDtl
where arr.FLAG == 2
select arr;
ProcParam paramIns = null;
if (insArr.Any() && insArr.Count() > 0)
{
paramIns = new ProcParam(10)
{
ProcedureName = "MTL_IN_PACK.T_ARRIVAL_DTL_INS"
};
var arrARRIVAL_NO = (from arr in insArr
select arr.ARRIVAL_NO).ToArray();
paramIns.AddParamInput(0, "strARRIVAL_NO", arrARRIVAL_NO, OracleDbType.Varchar2);
var arrLINE_NO = (from arr in insArr
select(object) arr.LINE_NO).ToArray();
paramIns.AddParamInput(1, "strLINE_NO", arrLINE_NO, OracleDbType.Int32);
var arrMTL_SEQ_NO = (from arr in insArr
select arr.MTL_SEQ_NO).ToArray();
paramIns.AddParamInput(2, "strMTL_SEQ_NO", arrMTL_SEQ_NO, OracleDbType.Varchar2);
var arrUNIT_ID = (from arr in insArr
select arr.UNIT_ID).ToArray();
paramIns.AddParamInput(3, "strUNIT_ID", arrUNIT_ID, OracleDbType.Varchar2);
var arrQTY = (from arr in insArr
select(object) arr.QTY).ToArray();
paramIns.AddParamInput(4, "strQTY", arrQTY, OracleDbType.Decimal);
var arrREMARK = (from arr in insArr
select arr.REMARK).ToArray();
paramIns.AddParamInput(5, "strREMARK", arrREMARK, OracleDbType.Varchar2);
//strCREATED_BY
paramIns.AddParamInput(6, "strUSER_ID", ArrayOf <object> .Create(insArr.Count(), userid), OracleDbType.Varchar2);
//REC_STATUS
paramIns.AddParamInput(7, "strREC_STAT", ArrayOf <object> .Create(insArr.Count(), "Y"), OracleDbType.Varchar2);
var arrLOT_DATE = (from arr in insArr
select(object) arr.LOT_DATE).ToArray();
paramIns.AddParamInput(8, "strLOT_DATE", arrLOT_DATE, OracleDbType.Date);
//RESULTMSG
paramIns.AddParamOutput(9, "RESULTMSG", OracleDbType.Varchar2, 255, "OK", insArr.Count());
}
#endregion
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(procHeader, paramIns, insArr.Count(), 0, 0);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
OracleString resultKey = (OracleString)procHeader.ReturnValue(0);
OracleString result = (OracleString)procHeader.ReturnValue(10);
if (!resultKey.IsNull)
{
ARRIVAL_NO = resultKey.Value;
}
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
