//int _docType = 0;
protected override string DoIt()
{
try
{
MVA027PostDatedCheck _pdc = new MVA027PostDatedCheck(GetCtx(), GetRecord_ID(), null);
int _id = _pdc.GetVA027_PostDatedCheck_ID();
_sql.Append(@"SELECT C_PAYMENT_ID FROM VA027_POSTDATEDCHECK WHERE VA027_POSTDATEDCHECK_ID=" + _id + "AND AD_Client_ID = " + GetAD_Client_ID());
int _payID = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
if (_payID > 0)
{
return(Msg.GetMsg(GetCtx(), "VA027_PaymentAlreadyGenerated"));
}
string _docStatus = _pdc.GetDocStatus();
if (_docStatus == "CO")
{
ViennaAdvantage.Process.VA027_GenPayment _genPayment = new ViennaAdvantage.Process.VA027_GenPayment();
_genPayment.GenratePayment(GetCtx(), GetRecord_ID(), Get_TrxName());
return(Msg.GetMsg(GetCtx(), "VA027_PaymentGenerated"));
}
return(Msg.GetMsg(GetCtx(), "VA027_PDCNotCompleted"));
}
catch (Exception ex)
{
log.Severe(ex.ToString());
}
return("");
}
protected override string DoIt()
{
MVA027PostDatedCheck pdc = new MVA027PostDatedCheck(GetCtx(), GetRecord_ID(), Get_TrxName());
if (!pdc.IsVA027_MultiCheque() && pdc.IsVA027_DiscountingPDC() && pdc.GetDocStatus() == "CO")
{
res = GenratePaymentHdr(GetCtx(), GetRecord_ID(), paymentDocumentTypeId, Get_TrxName());
}
else if (!pdc.IsVA027_MultiCheque() && Convert.ToDateTime(pdc.GetVA027_CheckDate()) <= _sysDate && pdc.GetDocStatus() == "CO")
{
res = GenratePaymentHdr(GetCtx(), GetRecord_ID(), paymentDocumentTypeId, Get_TrxName());
}
else if (pdc.GetDocStatus() == "CO" && pdc.IsVA027_MultiCheque())
{
res = GenratePaymentLine(GetCtx(), GetRecord_ID(), paymentDocumentTypeId, Get_TrxName());
}
else if (pdc.GetDocStatus() != "CO")
{
return("@DocumentNotCompleted@");
}
else
{
return("PaymentNotGenerated");
}
if (res == "E")
{
return(Msg.GetMsg(GetCtx(), "VA027_PaymentAlreadyGenerated"));
}
else
if (res == "F")
{
return(Msg.GetMsg(GetCtx(), "VA027_PaymentNotGenerated"));
}
else
if (res.Equals("N"))
{
return(_msg);
}
else
{
return(Msg.GetMsg(GetCtx(), "VA027_PaymentGenerated") + " " + documentno);
}
}
protected override string DoIt()
{
MVA027ChequeDetails PDCLine = new MVA027ChequeDetails(GetCtx(), GetRecord_ID(), Get_Trx());
PDCLine_ID = PDCLine.GetVA027_ChequeDetails_ID();
PDC_id = PDCLine.GetVA027_PostDatedCheck_ID();
MVA027PostDatedCheck pdc = new MVA027PostDatedCheck(GetCtx(), PDC_id, Get_Trx());
DocStatus = pdc.GetDocStatus();
try
{
_sql.Append("Select DocBaseType from C_DocType WHERE C_DocType_ID=" + pdc.GetC_DocType_ID());
DocumentBaseType = (String)DB.ExecuteScalar(_sql.ToString(), null, Get_Trx());
if ((DocStatus == "RE" || DocStatus == "VO") && DocumentBaseType == "PDP" && PDCLine_ID > 0)
{
#region Creating View
_sql.Clear();
_sql.Append(@"Create OR replace View VA009_PostDataCheck_V AS
SELECT T.AD_CLIENT_ID,
T.AD_ORG_ID,
T.VA027_ChequeDetails_ID,
UPPER(T.VA027_PAYEE) AS VA027_PAYEE,
VA027_CHECKDATE,
'CANCELLED' AS VA009_Cancel,
T.VA027_CHEQUEAMOUNT,
UPPER(SPELL_NUMBER(abs(VA027_CHEQUEAMOUNT))) AS AmtInword FROM
(SELECT PDC.AD_CLIENT_ID,
PDC.AD_ORG_ID,
DC.VA027_ChequeDetails_ID,
CASE WHEN PDC.C_BPartner_ID IS NOT NULL
THEN BP.NAME
ELSE PDC.VA027_PAYEE
END AS VA027_PAYEE,
DC.VA027_CHECKDATE,DC.VA027_ChequeAmount
FROM VA027_CHEQUEDETAILS DC
LEFT JOIN VA027_POSTDATEDCHECK PDC
ON DC.VA027_POSTDATEDCHECK_ID =PDC.VA027_POSTDATEDCHECK_ID
LEFT JOIN C_BPartner BP ON
BP.C_BPartner_ID=PDC.C_BPartner_ID
Where PDC.VA027_POSTDATEDCHECK_ID=" + PDC_id + @"
And dc.VA027_CHEQUEDETAILS_ID=" + PDCLine_ID + @") T");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
_sql.Clear();
#endregion
DataSet ds = new DataSet();
PDC_id = PDCLine.GetVA027_PostDatedCheck_ID();
int bankAccount = pdc.GetC_BankAccount_ID();
// GET data from Bank Window
_sql.Clear();
_sql.Append("Select NVL(BAD.C_BankAccountDoc_ID,0) C_BankAccountDoc_ID,NVL(BAD.CurrentNext,0) CurrentNext,NVL(BAD.VA009_CheckPrintSetting_ID,0) VA009_CheckPrintSetting_ID," +
" NVL(BA.C_BANK_ID,0) C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID" +
" Where BAD.C_BankAccount_ID =" + bankAccount);
//_sql.Append("Select C_BankAccountDoc_ID,CurrentNext,SqlQuery,ReportPath From C_BankAccountDoc Where C_BankAccount_ID =" + bankAccount);
ds = DB.ExecuteDataset(_sql.ToString(), null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
checkNo = Convert.ToInt32(ds.Tables[0].Rows[0]["CurrentNext"]);
Doc_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BankAccountDoc_ID"]);
Header_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["VA009_CheckPrintSetting_ID"]);
Bank_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BANK_ID"]);
}
else
{
log.SaveError("Error", Msg.GetMsg(GetCtx(), "There is no Bank Account Related to This Payee"));
ds.Dispose();
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CancelCheckPrintLineReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
//log.SaveError("Error", Msg.GetMsg(GetCtx(), "Not Able to print check"));
return("");
}
checkNo = checkNo + 1;
ds.Clear();
// Check no increase by one
_sql.Clear();
_sql.Append("Update C_BankAccountDoc Set CurrentNext=" + checkNo + " Where isactive='Y' And C_BankAccountDoc_ID =" + Doc_ID);
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
// Get Data from Print Check Window ( GEt Check Setting)
_sql.Clear();
_sql.Append(@"SELECT L.REPORTPATH ,
L.SQLQUERY
FROM VA009_DocPrintConfig L
LEFT JOIN VA009_CheckPrintSetting H
ON H.VA009_CheckPrintSetting_ID =L.VA009_CheckPrintSetting_ID
WHERE L.VA009_CHECKTYPE ='P1'
AND H.VA009_CheckPrintSetting_ID=" + Header_ID + " AND H.C_BANK_ID =" + Bank_ID);
ds = DB.ExecuteDataset(_sql.ToString(), null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
sqlQuery = ds.Tables[0].Rows[0]["SQLQUERY"].ToString();
path = ds.Tables[0].Rows[0]["REPORTPATH"].ToString();
// update print
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CancelCheckPrintLineReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
_sql.Clear();
_sql.Append("Update VA027_CHEQUEDETAILS set VA009_IsCancelled='Y' where isactive='Y' and VA027_CHEQUEDETAILS_ID=" + PDCLine_ID);
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
_sql.Clear();
_sql.Append("SELECT COUNT(*) FROM VA027_CHEQUEDETAILS WHERE VA027_POSTDATEDCHECK_ID=" + PDC_id + " AND VA009_IsCancelled='N' AND isactive='Y'");
int i = Convert.ToInt32(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx()));
if (i == 0)
{
_sql.Clear();
_sql.Append("UPDATE VA027_POSTDATEDCHECK SET VA009_IsCancelled='Y' WHERE isactive='Y' and VA027_POSTDATEDCHECK_ID=" + PDC_id);
DB.ExecuteScalar(_sql.ToString(), null, Get_Trx());
}
}
else
{
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CancelCheckPrintLineReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
log.SaveError("Error", Msg.GetMsg(GetCtx(), "There is no Print Format Related to This Payee"));
return("");
}
ds.Dispose();
}
else
{
}
_sql.Clear();
if (DocumentBaseType != "PDP")
{
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CancelCheckPrintLineReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
//log.SaveError("Error", Msg.GetMsg(GetCtx(), "Not Able to print check"));
}
}
catch (Exception ex)
{
log.Log(Level.SEVERE, ex.Message.ToString());
}
return(" ");
}
protected override string DoIt()
{
#region Check RE-Print Working for PDc
MVA027PostDatedCheck pdc = new MVA027PostDatedCheck(GetCtx(), GetRecord_ID(), null);
PDC_ID = pdc.GetVA027_PostDatedCheck_ID();
try
{
if (PDC_ID > 0)
{
int bankAccount = pdc.GetC_BankAccount_ID();
#region Creating View
_sql.Clear();
_sql.Append(@"Create OR replace View VA009_PostDataCheck_V AS
SELECT T.AD_CLIENT_ID AS AD_CLIENT_ID,
T.AD_ORG_ID AS AD_ORG_ID,
UPPER(T.VA027_PAYEE) AS VA027_PAYEE,
VA027_CHECKDATE AS VA027_CHECKDATE,
T.VA027_CHEQUEAMOUNT AS VA027_CHEQUEAMOUNT,
'' AS VA009_CANCEL,
UPPER( SPELL_NUMBER(ABS(T.VA027_CHEQUEAMOUNT))) AS AMTINWORD,
T.VA027_POSTDATEDCHECK_ID
FROM
(SELECT PDC.AD_CLIENT_ID,
PDC.AD_ORG_ID,
PDC.VA027_PostDatedCheck_ID,
CASE
WHEN PDC.C_BPartner_ID IS NOT NULL
THEN BP.NAME
ELSE PDC.VA027_PAYEE
END AS VA027_PAYEE,
CASE PDC.VA027_MULTICHEQUE
WHEN 'Y'
THEN DC.VA027_CHECKDATE
ELSE PDC.VA027_CHECKDATE
END AS VA027_CHECKDATE,
CASE PDC.VA027_MULTICHEQUE
WHEN 'Y'
THEN DC.VA027_ChequeAmount
ELSE PDC.VA027_PayAmt
END AS VA027_ChequeAmount
FROM VA027_POSTDATEDCHECK PDC
LEFT JOIN VA027_CHEQUEDETAILS DC
ON DC.VA027_POSTDATEDCHECK_ID =PDC.VA027_POSTDATEDCHECK_ID
LEFT JOIN C_BPARTNER BP
ON BP.C_BPartner_ID=PDC.C_BPartner_ID
Where PDC.VA027_POSTDATEDCHECK_ID=" + PDC_ID + @"
) T");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString()));
_sql.Clear();
#endregion
// GET data from Bank Window
_sql.Clear();
//_sql.Append("Select BAD.C_BankAccountDoc_ID,BAD.CurrentNext,BAD.VA009_CheckPrintSetting_ID, BA.C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID Where BAD.C_BankAccount_ID =" + bankAccount);
_sql.Append("Select NVL(BAD.C_BankAccountDoc_ID,0) C_BankAccountDoc_ID,NVL(BAD.CurrentNext,0) CurrentNext,NVL(BAD.VA009_CheckPrintSetting_ID,0) VA009_CheckPrintSetting_ID, NVL(BA.C_BANK_ID,0) C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID Where BAD.C_BankAccount_ID =" + bankAccount);
//_sql.Append("Select C_BankAccountDoc_ID,CurrentNext,SqlQuery,ReportPath From C_BankAccountDoc Where C_BankAccount_ID =" + bankAccount);
ds = DB.ExecuteDataset(_sql.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
checkNo = Convert.ToInt32(ds.Tables[0].Rows[0]["CurrentNext"]);
Doc_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BankAccountDoc_ID"]);
Header_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["VA009_CheckPrintSetting_ID"]);
Bank_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BANK_ID"]);
checkNo = checkNo + 1;
ds.Clear();
}
// Check no increase by one
//_sql.Clear();
//_sql.Append("Update C_BankAccountDoc Set CurrentNext=" + checkNo + " Where C_BankAccountDoc_ID =" + Doc_ID);
//DB.ExecuteQuery(_sql.ToString());
// Get Data from Print Check Window ( GEt Check Setting)
_sql.Clear();
_sql.Append(@"SELECT L.REPORTPATH ,
L.SQLQUERY
FROM VA009_DocPrintConfig L
LEFT JOIN VA009_CheckPrintSetting H
ON H.VA009_CheckPrintSetting_ID =L.VA009_CheckPrintSetting_ID
WHERE L.VA009_CHECKTYPE ='P1'
AND H.VA009_CheckPrintSetting_ID=" + Header_ID + " AND H.C_BANK_ID =" + Bank_ID);
ds = DB.ExecuteDataset(_sql.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
sqlQuery = ds.Tables[0].Rows[0]["SQLQUERY"].ToString();
path = ds.Tables[0].Rows[0]["REPORTPATH"].ToString();
ds.Clear();
}
// update print
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckRePrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString()));
}
#endregion ;
#region RE-Check Print Working for PAyment
MPayment pay = new MPayment(GetCtx(), GetRecord_ID(), null);
Payment_ID = pay.GetC_Payment_ID();
if (Payment_ID > 0)
{
int bankAccount = pay.GetC_BankAccount_ID();
#region Creating View
_sql.Clear();
_sql.Append(@"Create OR replace View VA009_PostDataCheck_V AS
SELECT P.AD_CLIENT_ID AS AD_CLIENT_ID,
P.AD_ORG_ID AS AD_ORG_ID,
'' AS VA009_CANCEL,
UPPER(BP.NAME) AS VA027_PAYEE,
P.CHECKDATE AS VA027_CHECKDATE,
p.PAYAMT AS VA027_CHEQUEAMOUNT,
UPPER(SPELL_NUMBER(ABS(PAYAMT))) AS AMTINWORD,
P.C_PAYMENT_ID
FROM C_PAYMENT P
LEFT JOIN C_BPARTNER BP
ON BP.C_BPartner_ID=P.C_BPartner_ID Where P.C_PAYMENT_ID=" + Payment_ID);
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString()));
_sql.Clear();
#endregion
// GET data from Bank Window
_sql.Clear();
//_sql.Append("Select BAD.C_BankAccountDoc_ID,BAD.CurrentNext,BAD.VA009_CheckPrintSetting_ID, BA.C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID Where BAD.C_BankAccount_ID =" + bankAccount);
_sql.Append("Select NVL(BAD.C_BankAccountDoc_ID,0) C_BankAccountDoc_ID,NVL(BAD.CurrentNext,0) CurrentNext,NVL(BAD.VA009_CheckPrintSetting_ID,0) VA009_CheckPrintSetting_ID, NVL(BA.C_BANK_ID,0) C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID Where BAD.C_BankAccount_ID =" + bankAccount);
//_sql.Append("Select C_BankAccountDoc_ID,CurrentNext,SqlQuery,ReportPath From C_BankAccountDoc Where C_BankAccount_ID =" + bankAccount);
ds = DB.ExecuteDataset(_sql.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
checkNo = Convert.ToInt32(ds.Tables[0].Rows[0]["CurrentNext"]);
Doc_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BankAccountDoc_ID"]);
Header_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["VA009_CheckPrintSetting_ID"]);
Bank_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BANK_ID"]);
checkNo = checkNo + 1;
ds.Clear();
}
// Check no increase by one
//_sql.Clear();
//_sql.Append("Update C_BankAccountDoc Set CurrentNext=" + checkNo + " Where C_BankAccountDoc_ID =" + Doc_ID);
//DB.ExecuteQuery(_sql.ToString());
// Get Data from Print Check Window ( GEt Check Setting)
_sql.Clear();
_sql.Append(@"SELECT L.REPORTPATH ,
L.SQLQUERY
FROM VA009_DocPrintConfig L
LEFT JOIN VA009_CheckPrintSetting H
ON H.VA009_CheckPrintSetting_ID =L.VA009_CheckPrintSetting_ID
WHERE L.VA009_CHECKTYPE ='P1'
AND H.VA009_CheckPrintSetting_ID=" + Header_ID + " AND H.C_BANK_ID =" + Bank_ID);
ds = DB.ExecuteDataset(_sql.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
sqlQuery = ds.Tables[0].Rows[0]["SQLQUERY"].ToString();
path = ds.Tables[0].Rows[0]["REPORTPATH"].ToString();
ds.Clear();
}
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckRePrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString()));
#endregion ;
}
}
catch (Exception ex)
{
log.Log(Level.SEVERE, ex.Message.ToString());
}
return(" ");
}
/** Generate Payment for Single Cheque */
public string GenratePaymentHdr(Ctx ctx, int Record_Id, int paymentDocumentTypeId, Trx trxName)
{
StringBuilder _sql = new StringBuilder();
MVA027PostDatedCheck _pdc = new MVA027PostDatedCheck(ctx, Record_Id, trxName);
ViennaAdvantage.Model.MPayment _payment = new ViennaAdvantage.Model.MPayment(ctx, 0, trxName);
_payment.SetAD_Client_ID(_pdc.GetAD_Client_ID());
_payment.SetAD_Org_ID(_pdc.GetAD_Org_ID());
if (c_BankAccount_ID == 0)
{
_payment.SetC_BankAccount_ID(_pdc.GetC_BankAccount_ID());
}
else
{
_payment.SetC_BankAccount_ID(c_BankAccount_ID);
}
_payment.SetDateTrx(_sysDate); // Set Today date in transaction Date.
_payment.SetDateAcct(_pdc.GetVA027_CheckDate()); // In Case of Discounting PDC set Today date in Account Date. // _pdc.GetVA027_CheckDate()
if (_pdc.IsVA027_DiscountingPDC())
{
_payment.SetVA027_DiscountingPDC(true);
}
_payment.SetDescription(_pdc.GetVA027_Description());
if (_pdc.GetC_BPartner_ID() > 0)
{
_payment.SetC_BPartner_ID(_pdc.GetC_BPartner_ID());
_payment.SetC_BPartner_Location_ID(_pdc.GetC_BPartner_Location_ID());
}
_payment.SetIsPrepayment(_pdc.IsVA027_IsPrepayment());
if (_pdc.GetC_Invoice_ID() > 0)
{
_payment.SetC_Invoice_ID(_pdc.GetC_Invoice_ID());
}
if (_pdc.GetC_InvoicePaySchedule_ID() > 0)
{
_payment.SetC_InvoicePaySchedule_ID(_pdc.GetC_InvoicePaySchedule_ID());
}
if (_pdc.GetC_Order_ID() > 0)
{
_payment.SetC_Order_ID(_pdc.GetC_Order_ID());
}
if (_pdc.GetVA009_OrderPaySchedule_ID() > 0)
{
_payment.SetVA009_OrderPaySchedule_ID(_pdc.GetVA009_OrderPaySchedule_ID());
}
if (_pdc.GetC_Charge_ID() > 0)
{
_payment.SetC_Charge_ID(_pdc.GetC_Charge_ID());
}
if (_pdc.GetVA027_Payee() != null)
{
_payment.SetDescription(_pdc.GetVA027_Payee());
}
_payment.SetC_Tax_ID(_pdc.GetC_Tax_ID());
_payment.SetTaxAmount(Math.Round(_pdc.GetTaxAmount(), 2));
_payment.SetPayAmt(Math.Round(_pdc.GetVA027_PayAmt(), 2));
_payment.SetC_Currency_ID(_pdc.GetC_Currency_ID());
_payment.SetDiscountAmt(Math.Round(_pdc.GetVA027_DiscountAmt(), 2));
_payment.SetWriteOffAmt(Math.Round(_pdc.GetVA027_WriteoffAmt(), 2));
_payment.SetVA009_PaymentMethod_ID(_pdc.GetVA009_PaymentMethod_ID());
_payment.SetCheckNo(_pdc.GetVA027_CheckNo());
_payment.SetCheckDate(_pdc.GetVA027_CheckDate());
_payment.SetValidMonths(_pdc.GetVA027_ValidMonth());
_payment.SetMicr(_pdc.GetVA027_MICR());
_payment.SetAccountNo(_pdc.GetVA027_AccountNo());
_payment.SetA_Name(_pdc.GetVA027_AccountName());
_payment.SetPDCType(_pdc.GetPDCType());
//_sql.Append("select docbasetype,c_doctype_id from C_doctype where c_doctype_id=" + _pdc.GetC_DocType_ID());
//_docBaseType = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString()));
_sql.Clear();
//if (_docBaseType == "PDR")
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='ARR' AND IsActive='Y' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _docuType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
// _payment.SetC_DocType_ID(_docuType);
// _sql.Clear();
//}
//else
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='APP' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _documentType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString(), null, trxName));
// _payment.SetC_DocType_ID(_documentType);
//}
_payment.SetC_DocType_ID(paymentDocumentTypeId);
_exeStatus = _payment.GetVA009_ExecutionStatus();
_payment.SetVA009_ExecutionStatus(_exeStatus = "I");
if (_payment.Save(trxName))
{
_status = null;
_status = _payment.CompleteIt();
}
else
{
return("F");
}
if (_status == "CO")
{
_payment.SetDocStatus("CO");
_pdc.SetVA027_PaymentStatus("1");
_payment.Set_Value("VA027_PostDatedCheck_ID", _pdc.GetVA027_PostDatedCheck_ID());
_payment.Save(trxName);
}
_pdc.SetC_Payment_ID(_payment.GetC_Payment_ID());
_pdc.SetVA027_PaymentGenerated(true);
_pdc.SetVA027_GeneratePayment("Y");
if (!_pdc.Save(trxName))
{
return("E");
}
else
{
return("Success");
}
}
/** Generate Payment for Multi Cheque Details */
public string GenratePaymentLine(Ctx ctx, int Record_Id, int paymentDocumentTypeId, Trx trxName)
{
StringBuilder _sql = new StringBuilder();
MVA027PostDatedCheck _pdc = new MVA027PostDatedCheck(ctx, Record_Id, trxName);
String sql = "SELECT * FROM VA027_ChequeDetails WHERE VA027_PostDatedCheck_ID=" + Record_Id;
DataSet _ds = new DataSet();
ViennaAdvantage.Model.MPayment _payment = null;
_ds = DB.ExecuteDataset(sql.ToString(), null, trxName);
int _count = _ds.Tables[0].Rows.Count;
if (_ds != null && _ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < _ds.Tables[0].Rows.Count; i++)
{
MVA027ChequeDetails cd = new MVA027ChequeDetails(GetCtx(), Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), Get_Trx());
if (cd.IsVA027_DiscountingPDC())
{
if (Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_Payment_ID"]) == 0)
{
_sql.Clear();
_payment = new ViennaAdvantage.Model.MPayment(ctx, 0, trxName);
_payment.SetAD_Client_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Client_ID"]));
_payment.SetAD_Org_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Org_ID"]));
if (c_BankAccount_ID == 0)
{
_payment.SetC_BankAccount_ID(_pdc.GetC_BankAccount_ID());
}
else
{
_payment.SetC_BankAccount_ID(c_BankAccount_ID);
}
_payment.SetDateTrx(_sysDate);
_payment.SetDateAcct(cd.GetVA027_CheckDate());
_payment.SetDescription(_pdc.GetVA027_Description());
_payment.SetIsPrepayment(_pdc.IsVA027_IsPrepayment());
if (_pdc.GetC_Charge_ID() > 0)
{
_payment.SetC_Charge_ID(_pdc.GetC_Charge_ID());
}
_payment.SetVA027_DiscountingPDC(true);
if (_pdc.GetVA027_Payee() != null)
{
_payment.SetDescription(_pdc.GetVA027_Payee());
}
//added by arpit
if (_pdc.GetC_BPartner_ID() > 0)
{
_payment.SetC_BPartner_ID(_pdc.GetC_BPartner_ID());
if (_pdc.GetC_BPartner_Location_ID() > 0)
{
_payment.SetC_BPartner_Location_ID(_pdc.GetC_BPartner_Location_ID());
}
}
//end here
_payment.SetC_Tax_ID(_pdc.GetC_Tax_ID());
_payment.SetPayAmt(Math.Round(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), 2));
_payment.SetC_Currency_ID(_pdc.GetC_Currency_ID());
_payment.SetDiscountAmt(Math.Round(_pdc.GetVA027_DiscountAmt(), 2));
_payment.SetWriteOffAmt(Math.Round(_pdc.GetVA027_WriteoffAmt(), 2));
_payment.SetVA009_PaymentMethod_ID(_pdc.GetVA009_PaymentMethod_ID());
_payment.SetCheckNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_CheckNo"]));
_payment.SetCheckDate(Util.GetValueOfDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]));
_payment.SetValidMonths(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ValidMonth"]));
_payment.SetMicr(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_MICR"]));
_payment.SetAccountNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountNo"]));
_payment.SetA_Name(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountName"]));
_payment.SetPDCType(_pdc.GetPDCType());
//_sql.Append("select docbasetype,c_doctype_id from C_doctype where c_doctype_id=" + _pdc.GetC_DocType_ID());
//_docBaseType = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString()));
_sql.Clear();
//if (_docBaseType == "PDR")
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='ARR' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _docuType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
// _payment.SetC_DocType_ID(_docuType);
// _sql.Clear();
//}
//else
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='APP' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _documentType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString(), null, trxName));
// _payment.SetC_DocType_ID(_documentType);
// _sql.Clear();
//}
_payment.SetC_DocType_ID(paymentDocumentTypeId);
_exeStatus = _payment.GetVA009_ExecutionStatus();
_payment.SetVA009_ExecutionStatus(_exeStatus = "I");
if (_payment.Save(trxName))
{
_status = null;
_status = _payment.CompleteIt();
}
else
{
return("F");
}
if (_status == "CO")
{
_payment.SetDocStatus("CO");
_payment.Set_Value("VA027_PostDatedCheck_ID", _pdc.GetVA027_PostDatedCheck_ID());
if (_payment.Save(trxName))
{
cqd = new MVA027ChequeDetails(ctx, Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), trxName);
cqd.SetC_Payment_ID(_payment.GetC_Payment_ID());
cqd.SetVA027_PaymentStatus("1");
if (cqd.Save(trxName))
{
}
}
}
}
}
else
{
if (Convert.ToDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]) <= _sysDate)
{
if (Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_Payment_ID"]) == 0)
{
_sql.Clear();
_payment = new ViennaAdvantage.Model.MPayment(ctx, 0, trxName);
_payment.SetAD_Client_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Client_ID"]));
_payment.SetAD_Org_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Org_ID"]));
if (c_BankAccount_ID == 0)
{
_payment.SetC_BankAccount_ID(_pdc.GetC_BankAccount_ID());
}
else
{
_payment.SetC_BankAccount_ID(c_BankAccount_ID);
}
_payment.SetDateTrx(_sysDate);
_payment.SetDateAcct(cd.GetVA027_CheckDate());
_payment.SetDescription(_pdc.GetVA027_Description());
_payment.SetIsPrepayment(_pdc.IsVA027_IsPrepayment());
if (_pdc.GetC_Charge_ID() > 0)
{
_payment.SetC_Charge_ID(_pdc.GetC_Charge_ID());
}
if (_pdc.GetVA027_Payee() != null)
{
_payment.SetDescription(_pdc.GetVA027_Payee());
}
//added by arpit
if (_pdc.GetC_BPartner_ID() > 0)
{
_payment.SetC_BPartner_ID(_pdc.GetC_BPartner_ID());
if (_pdc.GetC_BPartner_Location_ID() > 0)
{
_payment.SetC_BPartner_Location_ID(_pdc.GetC_BPartner_Location_ID());
}
}
//end here
_payment.SetC_Tax_ID(_pdc.GetC_Tax_ID());
_payment.SetPayAmt(Math.Round(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), 2));
_payment.SetC_Currency_ID(_pdc.GetC_Currency_ID());
_payment.SetDiscountAmt(Math.Round(_pdc.GetVA027_DiscountAmt(), 2));
_payment.SetWriteOffAmt(Math.Round(_pdc.GetVA027_WriteoffAmt(), 2));
_payment.SetVA009_PaymentMethod_ID(_pdc.GetVA009_PaymentMethod_ID());
_payment.SetCheckNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_CheckNo"]));
_payment.SetCheckDate(Util.GetValueOfDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]));
_payment.SetValidMonths(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ValidMonth"]));
_payment.SetMicr(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_MICR"]));
_payment.SetAccountNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountNo"]));
_payment.SetA_Name(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountName"]));
_payment.SetPDCType(_pdc.GetPDCType());
//_sql.Append("select docbasetype,c_doctype_id from C_doctype where c_doctype_id=" + _pdc.GetC_DocType_ID());
//_docBaseType = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString()));
_sql.Clear();
//if (_docBaseType == "PDR")
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='ARR' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _docuType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
// _payment.SetC_DocType_ID(_docuType);
// _sql.Clear();
//}
//else
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='APP' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _documentType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString(), null, trxName));
// _payment.SetC_DocType_ID(_documentType);
// _sql.Clear();
//}
_payment.SetC_DocType_ID(paymentDocumentTypeId);
_exeStatus = _payment.GetVA009_ExecutionStatus();
_payment.SetVA009_ExecutionStatus(_exeStatus = "I");
if (_payment.Save(trxName))
{
_status = null;
_status = _payment.CompleteIt();
}
else
{
return("F");
}
if (_status == "CO")
{
_payment.SetDocStatus("CO");
_payment.Set_Value("VA027_PostDatedCheck_ID", _pdc.GetVA027_PostDatedCheck_ID());
if (_payment.Save(trxName))
{
cqd = new MVA027ChequeDetails(ctx, Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), trxName);
cqd.SetC_Payment_ID(_payment.GetC_Payment_ID());
cqd.SetVA027_PaymentStatus("1");
if (cqd.Save(trxName))
{
}
}
}
}
else
{
return("E");
}
}
else
{
return("F");
}
}
}
}
if (Util.GetValueOfInt(DB.ExecuteScalar("SELECT Count(VA027_ChequeDetails_ID) From VA027_ChequeDetails Where VA027_PostDatedCheck_ID=" + Record_Id
+ " AND VA027_PaymentStatus='1'", null, trxName)) == _count)
{
_pdc.SetVA027_PaymentStatus("1");
_pdc.SetVA027_PaymentGenerated(true);
_pdc.SetVA027_GeneratePayment("Y");
if (!_pdc.Save(trxName))
{
return("E");
}
}
return("Success");
}
protected override string DoIt()
{
MVA027PostDatedCheck pdc = new MVA027PostDatedCheck(GetCtx(), GetRecord_ID(), null);
PDC_ID = pdc.GetVA027_PostDatedCheck_ID();
docStatus = pdc.GetDocStatus();
doctype_ID = pdc.GetC_DocType_ID();
_sql.Clear();
try
{
_sql.Append("Select DocBaseType from C_DocType WHERE C_DocType_ID=" + doctype_ID);
docbasetype = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx()));
//ds = DB.ExecuteDataset(_sql.ToString());
//if (ds != null && ds.Tables[0].Rows.Count > 0)
//{
// docbasetype = ds.Tables[0].Rows[0]["DocBaseType"].ToString();
// ds.Clear();
//}
if (!String.IsNullOrEmpty(docbasetype))
{
_DocBaseType = docbasetype;
}
#region Check Print Working for PDc
if (PDC_ID > 0 && docStatus == "CO" && docbasetype == "PDP")
{
// MVA027PostDatedCheck pdc = new MVA027PostDatedCheck(GetCtx(), GetRecord_ID(), null);
int bankAccount = pdc.GetC_BankAccount_ID();
//getting multicheck id
if (pdc.IsVA027_MultiCheque())
{
_sql.Clear();
_sql.Append("Select VA027_CHEQUEDETAILS_ID From VA027_CHEQUEDETAILS where isactive='Y' and VA009_PRINTED='N' AND VA027_POSTDATEDCHECK_ID=" + PDC_ID);
DataSet ds1 = DB.ExecuteDataset(_sql.ToString(), null, Get_Trx());
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
if (_CHEQUEDETAILS_ID == "")
{
_CHEQUEDETAILS_ID = (ds1.Tables[0].Rows[i]["VA027_CHEQUEDETAILS_ID"]).ToString();
}
else
{
_CHEQUEDETAILS_ID = _CHEQUEDETAILS_ID + ", " + (ds1.Tables[0].Rows[i]["VA027_CHEQUEDETAILS_ID"]).ToString();
}
}
}
}
//end
#region Creating View
_sql.Clear();
_sql.Append(@"Create OR replace View VA009_PostDataCheck_V AS
SELECT T.AD_CLIENT_ID AS AD_CLIENT_ID,
T.AD_ORG_ID AS AD_ORG_ID,
UPPER(T.VA027_PAYEE) AS VA027_PAYEE,
VA027_CHECKDATE AS VA027_CHECKDATE,
T.VA027_CHEQUEAMOUNT AS VA027_CHEQUEAMOUNT,
'' AS VA009_CANCEL,
UPPER( SPELL_NUMBER(ABS(T.VA027_CHEQUEAMOUNT))) AS AMTINWORD,
T.VA027_POSTDATEDCHECK_ID
FROM
(SELECT PDC.AD_CLIENT_ID,
PDC.AD_ORG_ID,
PDC.VA027_PostDatedCheck_ID,
CASE
WHEN PDC.C_BPartner_ID IS NOT NULL
THEN BP.NAME
ELSE PDC.VA027_PAYEE
END AS VA027_PAYEE,
CASE PDC.VA027_MULTICHEQUE
WHEN 'Y'
THEN DC.VA027_CHECKDATE
ELSE PDC.VA027_CHECKDATE
END AS VA027_CHECKDATE,
CASE PDC.VA027_MULTICHEQUE
WHEN 'Y'
THEN DC.VA027_ChequeAmount
ELSE PDC.VA027_PayAmt
END AS VA027_ChequeAmount
FROM VA027_POSTDATEDCHECK PDC
LEFT JOIN VA027_CHEQUEDETAILS DC
ON DC.VA027_POSTDATEDCHECK_ID =PDC.VA027_POSTDATEDCHECK_ID
LEFT JOIN C_BPARTNER BP
ON BP.C_BPartner_ID=PDC.C_BPartner_ID
Where PDC.VA027_POSTDATEDCHECK_ID=" + PDC_ID);
if (pdc.IsVA027_MultiCheque())
{
_sql.Append(@" And dc.VA027_CHEQUEDETAILS_ID IN (" + _CHEQUEDETAILS_ID + ")");
}
_sql.Append(@" ) T");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
_sql.Clear();
#endregion
// GET data from Bank Window
_sql.Clear();
_sql.Append("Select NVL(BAD.C_BankAccountDoc_ID,0) C_BankAccountDoc_ID,NVL(BAD.CurrentNext,0) CurrentNext,NVL(BAD.VA009_CheckPrintSetting_ID,0) VA009_CheckPrintSetting_ID, NVL(BA.C_BANK_ID,0) C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID Where BAD.C_BankAccount_ID =" + bankAccount);
//_sql.Append("Select C_BankAccountDoc_ID,CurrentNext,SqlQuery,ReportPath From C_BankAccountDoc Where C_BankAccount_ID =" + bankAccount);
ds = DB.ExecuteDataset(_sql.ToString(), null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
checkNo = Convert.ToInt32(ds.Tables[0].Rows[0]["CurrentNext"]);
Doc_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BankAccountDoc_ID"]);
Header_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["VA009_CheckPrintSetting_ID"]);
Bank_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BANK_ID"]);
if (pdc.IsVA027_MultiCheque())
{
_sql.Clear();
_sql.Append("Select count(*) from VA027_CHEQUEDETAILS where VA009_PRINTED='N' and VA027_POSTDATEDCHECK_ID=" + PDC_ID);
count = 0;
count = Convert.ToInt32(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx()));
checkNo = checkNo + count;
_sql.Clear();
_sql.Append("Update VA027_CHEQUEDETAILS set VA009_Printed='Y' where VA027_POSTDATEDCHECK_ID=" + PDC_ID);
DB.ExecuteScalar(_sql.ToString(), null, Get_Trx());
}
else
{
checkNo = checkNo + 1;
//_sql.Clear();
//_sql.Append("Update VA027_CHEQUEDETAILS set VA009_Printed='Y' where VA027_POSTDATEDCHECK_ID=" + PDC_ID);
//DB.ExecuteScalar(_sql.ToString());
}
ds.Clear();
}
else
{
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckPrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
return("");
}
// Check no increase by one
_sql.Clear();
_sql.Append("Update C_BankAccountDoc Set CurrentNext=" + checkNo + " Where isactive='Y' and C_BankAccountDoc_ID =" + Doc_ID);
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
// Get Data from Print Check Window ( GEt Check Setting)
_sql.Clear();
_sql.Append(@"SELECT L.REPORTPATH ,
L.SQLQUERY
FROM VA009_DocPrintConfig L
LEFT JOIN VA009_CheckPrintSetting H
ON H.VA009_CheckPrintSetting_ID =L.VA009_CheckPrintSetting_ID
WHERE L.VA009_CHECKTYPE ='P1'
AND H.VA009_CheckPrintSetting_ID=" + Header_ID + " AND H.C_BANK_ID =" + Bank_ID);
ds = DB.ExecuteDataset(_sql.ToString());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
pdc.SetVA009_Printed(true);
//pdc.setva0
pdc.Save();
sqlQuery = ds.Tables[0].Rows[0]["SQLQUERY"].ToString();
path = ds.Tables[0].Rows[0]["REPORTPATH"].ToString();
ds.Clear();
// update print
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckPrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
PDC_ID = 0;
}
else
{
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckPrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
return("");
}
}
#endregion
#region Check Print Working for PAyment
else
{
MPayment pay = new MPayment(GetCtx(), GetRecord_ID(), null);
Payment_ID = pay.GetC_Payment_ID();
docStatus = pay.GetDocStatus();
doctype_ID = pay.GetC_DocType_ID();
_sql.Clear();
_sql.Append("Select DocBaseType from C_DocType WHERE C_DocType_ID=" + doctype_ID);
docbasetype = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx()));
//ds = DB.ExecuteDataset(_sql.ToString());
//if (ds != null && ds.Tables[0].Rows.Count > 0)
//{
// docbasetype = ds.Tables[0].Rows[0]["DocBaseType"].ToString();
// ds.Clear();
//}
if (Payment_ID > 0 && docStatus == "CO" && docbasetype == "APP")
{
//MPayment pay = new MPayment(GetCtx(), GetRecord_ID(), null);
int bankAccount = pay.GetC_BankAccount_ID();
pay.SetVA009_Printed(true);
pay.Save();
#region Creating View
_sql.Clear();
_sql.Append(@"Create OR replace View VA009_PaymentCheck_V AS
SELECT P.AD_CLIENT_ID AS AD_CLIENT_ID,
P.AD_ORG_ID AS AD_ORG_ID,
'' AS VA009_CANCEL,
UPPER(BP.NAME) AS VA027_PAYEE,
P.CHECKDATE AS VA027_CHECKDATE,
p.PAYAMT AS VA027_CHEQUEAMOUNT,
UPPER(SPELL_NUMBER(ABS(PAYAMT))) AS AMTINWORD,
P.C_PAYMENT_ID
FROM C_PAYMENT P
LEFT JOIN C_BPARTNER BP
ON BP.C_BPartner_ID=P.C_BPartner_ID
Where P.C_PAYMENT_ID=" + Payment_ID);
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
_sql.Clear();
#endregion
// GET data from Bank Window
_sql.Clear();
//_sql.Append("Select BAD.C_BankAccountDoc_ID,BAD.CurrentNext,BAD.VA009_CheckPrintSetting_ID, BA.C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID Where BAD.C_BankAccount_ID =" + bankAccount);
_sql.Append("Select NVL(BAD.C_BankAccountDoc_ID,0) C_BankAccountDoc_ID,NVL(BAD.CurrentNext,0) CurrentNext,NVL(BAD.VA009_CheckPrintSetting_ID,0) VA009_CheckPrintSetting_ID, NVL(BA.C_BANK_ID,0) C_BANK_ID From C_BankAccountDoc BAD Left join C_BANKACCOUNT BA ON BA.C_bankAccount_ID=BAD.C_bankAccount_ID Where BAD.C_BankAccount_ID =" + bankAccount);
//_sql.Append("Select C_BankAccountDoc_ID,CurrentNext,SqlQuery,ReportPath From C_BankAccountDoc Where C_BankAccount_ID =" + bankAccount);
ds = DB.ExecuteDataset(_sql.ToString(), null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
checkNo = Convert.ToInt32(ds.Tables[0].Rows[0]["CurrentNext"]);
Doc_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BankAccountDoc_ID"]);
Header_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["VA009_CheckPrintSetting_ID"]);
Bank_ID = Convert.ToInt32(ds.Tables[0].Rows[0]["C_BANK_ID"]);
checkNo = checkNo + 1;
ds.Clear();
}
else
{
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckPrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
return("");
}
// Check no increase by one
_sql.Clear();
_sql.Append("Update C_BankAccountDoc Set CurrentNext=" + checkNo + " Where C_BankAccountDoc_ID =" + Doc_ID);
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
// Get Data from Print Check Window ( GEt Check Setting)
_sql.Clear();
_sql.Append(@"SELECT L.REPORTPATH ,
L.SQLQUERY
FROM VA009_DocPrintConfig L
LEFT JOIN VA009_CheckPrintSetting H
ON H.VA009_CheckPrintSetting_ID =L.VA009_CheckPrintSetting_ID
WHERE L.VA009_CHECKTYPE ='P2'
AND H.VA009_CheckPrintSetting_ID=" + Header_ID + " AND H.C_BANK_ID =" + Bank_ID);
ds = DB.ExecuteDataset(_sql.ToString(), null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
sqlQuery = ds.Tables[0].Rows[0]["SQLQUERY"].ToString();
path = ds.Tables[0].Rows[0]["REPORTPATH"].ToString();
ds.Clear();
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckPrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
}
else
{
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckPrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
return("");
}
#endregion
}
}
if (_DocBaseType != "PDP" && docbasetype != "APP")
{
_sql.Clear();
_sql.Append("Update AD_Process Set SqlQuery='" + sqlQuery + "' , ReportPath='" + path + "' Where NAme='VA009_CheckPrintReport'");
count = 0;
count = Convert.ToInt32(DB.ExecuteQuery(_sql.ToString(), null, Get_Trx()));
//log.SaveError("Error", Msg.GetMsg(GetCtx(), "Not Able to print check"));
}
}
catch (Exception ex)
{
log.Log(Level.SEVERE, ex.Message.ToString());
}
return(" ");
}
/** Generate Payment for Multi Cheque Details */
public string GenratePaymentLine(Ctx ctx, int Record_Id, int paymentDocumentTypeId, Trx trxName)
{
int stdprecision = 0;
Decimal surchargeAmt = Env.ZERO;
Decimal TaxAmt = Env.ZERO;
StringBuilder _sql = new StringBuilder();
MVA027PostDatedCheck _pdc = new MVA027PostDatedCheck(ctx, Record_Id, trxName);
String sql = "SELECT * FROM VA027_ChequeDetails WHERE VA027_PostDatedCheck_ID= " + Record_Id + " AND NVL(C_Payment_ID,0)=0 ORDER BY Va027_CheckDate";
DataSet _ds = new DataSet();
MPayment _payment = null;
_ds = DB.ExecuteDataset(sql.ToString(), null, trxName);
int _count = _ds.Tables[0].Rows.Count;
if (_ds != null && _ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < _ds.Tables[0].Rows.Count; i++)
{
MVA027ChequeDetails cd = new MVA027ChequeDetails(GetCtx(), Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), Get_Trx());
if (cd.IsVA027_DiscountingPDC())
{
if (Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_Payment_ID"]) == 0)
{
_sql.Clear();
_payment = new MPayment(ctx, 0, trxName);
_payment.SetAD_Client_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Client_ID"]));
_payment.SetAD_Org_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Org_ID"]));
if (c_BankAccount_ID == 0)
{
_payment.SetC_BankAccount_ID(_pdc.GetC_BankAccount_ID());
}
else
{
_payment.SetC_BankAccount_ID(c_BankAccount_ID);
}
_payment.SetDateTrx(_sysDate);
_payment.SetDateAcct(_sysDate); //cd.GetVA027_CheckDate());
_payment.SetDescription(_pdc.GetVA027_Description());
_payment.SetIsPrepayment(_pdc.IsVA027_IsPrepayment());
if (_pdc.GetC_Charge_ID() > 0)
{
_payment.SetC_Charge_ID(_pdc.GetC_Charge_ID());
}
_payment.SetVA027_DiscountingPDC(true);
if (_pdc.GetVA027_Payee() != null)
{
_payment.SetDescription(_pdc.GetVA027_Payee());
}
//added by arpit
if (_pdc.GetC_BPartner_ID() > 0)
{
_payment.SetC_BPartner_ID(_pdc.GetC_BPartner_ID());
if (_pdc.GetC_BPartner_Location_ID() > 0)
{
_payment.SetC_BPartner_Location_ID(_pdc.GetC_BPartner_Location_ID());
}
}
//end here
_payment.SetC_Tax_ID(_pdc.GetC_Tax_ID());
_payment.SetPayAmt(Math.Round(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), 2));
_payment.SetC_Currency_ID(_pdc.GetC_Currency_ID());
_payment.SetDiscountAmt(Math.Round(_pdc.GetVA027_DiscountAmt(), 2));
_payment.SetWriteOffAmt(Math.Round(_pdc.GetVA027_WriteoffAmt(), 2));
_payment.SetVA009_PaymentMethod_ID(_pdc.GetVA009_PaymentMethod_ID());
_payment.SetCheckNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_CheckNo"]));
_payment.SetCheckDate(Util.GetValueOfDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]));
_payment.SetValidMonths(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ValidMonth"]));
_payment.SetMicr(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_MICR"]));
_payment.SetAccountNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountNo"]));
_payment.SetA_Name(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountName"]));
_payment.SetPDCType(_pdc.GetPDCType());
//calculate Tax Amount
if (_pdc.GetC_Tax_ID() > 0)
{
sql = "SELECT StdPrecision FROM VA027_PostDatedCheck i INNER JOIN C_Currency c ON i.C_Currency_ID = c.C_Currency_ID " +
"WHERE VA027_PostDatedCheck_ID = " + _pdc.GetVA027_PostDatedCheck_ID();
stdprecision = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
MTax tax = new MTax(GetCtx(), _pdc.GetC_Tax_ID(), null);
if (tax.Get_ColumnIndex("Surcharge_Tax_ID") > 0 && tax.GetSurcharge_Tax_ID() > 0)
{
TaxAmt = tax.CalculateSurcharge(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision, out surchargeAmt);
}
else
{
TaxAmt = tax.CalculateTax(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision);
}
_payment.SetTaxAmount(TaxAmt);
_payment.Set_Value("SurchargeAmt", surchargeAmt);
}
//_sql.Append("select docbasetype,c_doctype_id from C_doctype where c_doctype_id=" + _pdc.GetC_DocType_ID());
//_docBaseType = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString()));
_sql.Clear();
//if (_docBaseType == "PDR")
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='ARR' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _docuType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
// _payment.SetC_DocType_ID(_docuType);
// _sql.Clear();
//}
//else
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='APP' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _documentType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString(), null, trxName));
// _payment.SetC_DocType_ID(_documentType);
// _sql.Clear();
//}
_payment.SetC_DocType_ID(paymentDocumentTypeId);
_exeStatus = _payment.GetVA009_ExecutionStatus();
_payment.SetVA009_ExecutionStatus(_exeStatus = "I");
if (_payment.Save(trxName))
{
//payment will get completed only if PaymentAllocate is generated.
if (GenratePaymentAllocate(GetCtx(), Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), _payment.GetC_Payment_ID(), Get_TrxName()))
{
_status = null;
_status = _payment.CompleteIt();
}
else
{
return("N");
}
}
else
{
return("F");
}
if (_status == "CO")
{
_payment.SetDocStatus("CO");
_payment.Set_Value("VA027_PostDatedCheck_ID", _pdc.GetVA027_PostDatedCheck_ID());
if (_payment.Save(trxName))
{
cqd = new MVA027ChequeDetails(ctx, Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), trxName);
cqd.SetC_Payment_ID(_payment.GetC_Payment_ID());
cqd.SetVA027_PaymentStatus("1");
if (!cqd.Save(trxName))
{
trxName.Rollback();
ValueNamePair pp = VLogger.RetrieveError();
log.Info("Error Saving Chequedetails : " + pp.GetValue() + " , Error Name : " + pp.GetName());
_msg = Msg.GetMsg(ctx, "ChequedetailsNotSaved") + ", " + (pp != null ? pp.GetName() : "");
return("N");
}
}
documentno += _payment.GetDocumentNo() + ",";
}
}
}
else
{
if (Convert.ToDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]) <= _sysDate)
{
if (Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_Payment_ID"]) == 0)
{
_sql.Clear();
_payment = new MPayment(ctx, 0, trxName);
_payment.SetAD_Client_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Client_ID"]));
_payment.SetAD_Org_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Org_ID"]));
if (c_BankAccount_ID == 0)
{
_payment.SetC_BankAccount_ID(_pdc.GetC_BankAccount_ID());
}
else
{
_payment.SetC_BankAccount_ID(c_BankAccount_ID);
}
_payment.SetDateTrx(_sysDate);
_payment.SetDateAcct(cd.GetVA027_CheckDate());
_payment.SetDescription(_pdc.GetVA027_Description());
_payment.SetIsPrepayment(_pdc.IsVA027_IsPrepayment());
if (_pdc.GetC_Charge_ID() > 0)
{
_payment.SetC_Charge_ID(_pdc.GetC_Charge_ID());
}
if (_pdc.GetVA027_Payee() != null)
{
_payment.SetDescription(_pdc.GetVA027_Payee());
}
//added by arpit
if (_pdc.GetC_BPartner_ID() > 0)
{
_payment.SetC_BPartner_ID(_pdc.GetC_BPartner_ID());
if (_pdc.GetC_BPartner_Location_ID() > 0)
{
_payment.SetC_BPartner_Location_ID(_pdc.GetC_BPartner_Location_ID());
}
}
//end here
_payment.SetC_Tax_ID(_pdc.GetC_Tax_ID());
_payment.SetPayAmt(Math.Round(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), 2));
_payment.SetC_Currency_ID(_pdc.GetC_Currency_ID());
_payment.SetDiscountAmt(Math.Round(_pdc.GetVA027_DiscountAmt(), 2));
_payment.SetWriteOffAmt(Math.Round(_pdc.GetVA027_WriteoffAmt(), 2));
_payment.SetVA009_PaymentMethod_ID(_pdc.GetVA009_PaymentMethod_ID());
_payment.SetCheckNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_CheckNo"]));
_payment.SetCheckDate(Util.GetValueOfDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]));
_payment.SetValidMonths(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ValidMonth"]));
_payment.SetMicr(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_MICR"]));
_payment.SetAccountNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountNo"]));
_payment.SetA_Name(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountName"]));
_payment.SetPDCType(_pdc.GetPDCType());
//calculate Tax Amount
if (_pdc.GetC_Tax_ID() > 0)
{
sql = "SELECT StdPrecision FROM VA027_PostDatedCheck i INNER JOIN C_Currency c ON i.C_Currency_ID = c.C_Currency_ID " +
"WHERE VA027_PostDatedCheck_ID = " + _pdc.GetVA027_PostDatedCheck_ID();
stdprecision = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
MTax tax = new MTax(GetCtx(), _pdc.GetC_Tax_ID(), null);
if (tax.Get_ColumnIndex("Surcharge_Tax_ID") > 0 && tax.GetSurcharge_Tax_ID() > 0)
{
TaxAmt = tax.CalculateSurcharge(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision, out surchargeAmt);
}
else
{
TaxAmt = tax.CalculateTax(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision);
}
_payment.SetTaxAmount(TaxAmt);
_payment.Set_Value("SurchargeAmt", surchargeAmt);
}
//_sql.Append("select docbasetype,c_doctype_id from C_doctype where c_doctype_id=" + _pdc.GetC_DocType_ID());
//_docBaseType = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString()));
_sql.Clear();
//if (_docBaseType == "PDR")
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='ARR' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _docuType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
// _payment.SetC_DocType_ID(_docuType);
// _sql.Clear();
//}
//else
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='APP' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _documentType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString(), null, trxName));
// _payment.SetC_DocType_ID(_documentType);
// _sql.Clear();
//}
_payment.SetC_DocType_ID(paymentDocumentTypeId);
_exeStatus = _payment.GetVA009_ExecutionStatus();
_payment.SetVA009_ExecutionStatus(_exeStatus = "I");
if (_payment.Save(trxName))
{
//payment will get completed only if PaymentAllocate is generated.
if (GenratePaymentAllocate(GetCtx(), Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), _payment.GetC_Payment_ID(), Get_TrxName()))
{
_status = null;
_status = _payment.CompleteIt();
}
else
{
return("N");
}
}
else
{
return("F");
}
if (_status == "CO")
{
_payment.SetDocStatus("CO");
_payment.Set_Value("VA027_PostDatedCheck_ID", _pdc.GetVA027_PostDatedCheck_ID());
if (_payment.Save(trxName))
{
cqd = new MVA027ChequeDetails(ctx, Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), trxName);
cqd.SetC_Payment_ID(_payment.GetC_Payment_ID());
cqd.SetVA027_PaymentStatus("1");
if (!cqd.Save(trxName))
{
trxName.Rollback();
ValueNamePair pp = VLogger.RetrieveError();
log.Info("Error Saving Chequedetails : " + pp.GetValue() + " , Error Name : " + pp.GetName());
_msg = Msg.GetMsg(ctx, "ChequedetailsNotSaved") + ", " + (pp != null ? pp.GetName() : "");
return("N");
}
}
documentno += _payment.GetDocumentNo() + ",";
}
}
else
{
return("E");
}
}
else
{
return("F");
}
}
}
}
if (Util.GetValueOfInt(DB.ExecuteScalar("SELECT Count(VA027_ChequeDetails_ID) From VA027_ChequeDetails Where VA027_PostDatedCheck_ID=" + Record_Id
+ " AND VA027_PaymentStatus!='1'", null, trxName)) == 0)
{
_pdc.SetVA027_PaymentStatus("1");
_pdc.SetVA027_PaymentGenerated(true);
_pdc.SetVA027_GeneratePayment("Y");
if (!_pdc.Save(trxName))
{
return("E");
}
}
return("Success");
}
