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