Update credit SalesTable
//CUS.sn by FC on 20160914 - ALT_CreditManagement
class A_CreditSalesTableUpdate extends RunBaseBatch
{
QueryRun qr;
transdate fromDate;
transdate toDate;
dialogfield dialogFieldFromDate;
dialogfield dialogFieldToDate;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
fromDate,
toDate
#endmacro
}
/* === Method::Start === */
public boolean canGoBatch()
{
return true;
}
/* === Method::Start === */
protected Object dialog()
{
DialogRunBase dialog;
;
dialog = super();
dialogFieldFromDate = dialog.addFieldValue(extendedTypeStr(transdate),fromdate,"@SYS24050");
dialogFieldToDate = dialog.addFieldValue(extendedTypeStr(transdate),todate,"@SYS14656");
return dialog;
}
/* === Method::Start === */
public boolean getFromDialog()
{
boolean ret;
ret = super();
fromDate = dialogFieldFromDate.value();
toDate = dialogFieldToDate.value();
return ret;
}
/* === Method::Start === */
public void initParmDefault()
{
this.initQuery();
super();
}
/* === Method::Start === */
void initQuery()
{
Query q;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
QueryBuildRange qbr2;
;
q = new Query();
qbds = q.addDataSource(tablenum(CustTrans));
qbds.addRange(fieldnum(CustTrans,AccountNum));
qbds.addRange(fieldnum(CustTrans,Invoice));
qbr = qbds.addRange(fieldnum(CustTrans,InvoiceType));
qbr.value(QueryValue(InvoiceType::CreditNote));
qbr.value(QueryValue(InvoiceType::Invoice));
qbr2 = qbds.addRange(fieldnum(CustTrans,A_CreditInsuredGuaranteeStatus));
qbr2.value(QueryValue(A_CreditInsuredGuaranteeStatus::None)+','+
QueryValue(A_CreditInsuredGuaranteeStatus::Covered)+','+
QueryValue(A_CreditInsuredGuaranteeStatus::NotCovered));
qr = new QueryRun(q);
}
/* === Method::Start === */
boolean isCreditInsuredCovered(CustTrans _custTrans)
{
CustTrans custTrans;
CustSettlement custSettlement;
CustInvoiceTrans custInvoiceTrans;
SalesTable salesTable;
CustInvoiceTable custInvoiceTable;
A_CreditLine A_CreditLine;
A_CreditTable A_CreditTable;
;
select firstonly custSettlement
where custSettlement.TransRecId == _custTrans.RecId
&& custSettlement.CanBeReversed
join firstonly custTrans
where custTrans.Voucher == custSettlement.OffsetTransVoucher;
if (_custTrans.AmountMST < 0 && custSettlement.RecId)
return false;
if (_custTrans.TransType == LedgerTransType::Sales)
{
select firstonly CustInvoiceTrans
where CustInvoiceTrans.InvoiceId == _custTrans.Invoice
&& CustInvoiceTrans.InvoiceDate == _custTrans.TransDate
&& CustInvoiceTrans.OrigSalesId
join salesTable where salesTable.SalesId == CustInvoiceTrans.OrigSalesId
&& salesTable.A_CreditInsuredGuarantee;
if (CustInvoiceTrans)
return true;
}
if (_custTrans.TransType == LedgerTransType::Cust)
{
select firstonly CustInvoiceTrans
where CustInvoiceTrans.InvoiceId == _custTrans.Invoice
&& CustInvoiceTrans.InvoiceDate == _custTrans.TransDate
&& ! CustInvoiceTrans.OrigSalesId
join custInvoiceTable where custInvoiceTable.InvoiceId == CustInvoiceTrans.InvoiceId
&& custInvoiceTable.A_CreditInsuredGuarantee;
if (CustInvoiceTrans)
return true;
}
select firstonly A_CreditLine
where A_CreditLine.CreditCategory == A_CreditCategory::Insured
join A_CreditTable order by transdate desc
where A_CreditTable.CreditLineRecId == A_CreditLine.RecId
&& A_CreditTable.CustAccount == _custTrans.AccountNum
&& ((A_CreditTable.CreditStatus == A_CreditStatus::granted) || (A_CreditTable.CreditStatus == A_CreditStatus::modified))
&& ((A_CreditTable.TransDate <= _custTrans.transDate) && (A_CreditTable.Duedate >= _custTrans.transDate || !A_CreditTable.Duedate));
if (!A_CreditLine)
return false;
select A_CreditTable order by transdate desc
where A_CreditTable.CreditLineRecId == A_CreditLine.RecId
&& A_CreditTable.CustAccount ==_custTrans.AccountNum
&& (A_CreditTable.CreditStatus == A_CreditStatus::cancelled ||
A_CreditTable.CreditStatus == A_CreditStatus::suspended)
&& A_CreditTable.TransDate <= _custTrans.transDate
&& (A_CreditTable.Duedate >= _custTrans.transDate || !A_CreditTable.Duedate);
if (A_CreditTable)
return false;
if (_custTrans.PaymMode && !CustPaymModeTable::find(_custTrans.PaymMode).A_CreditInsuredGuarantee)
return false;
return true;
}
/* === Method::Start === */
public container pack()
{
return [#CurrentVersion,#CurrentList,qr.pack()];
}
/* === Method::Start === */
public QueryRun queryRun()
{
return qr;
}
/* === Method::Start === */
public void run()
{
#OCCRetryCount
if (! this.validate())
throw error("@SYS107513");
try
{
ttsbegin;
this.Update();
ttscommit;
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
}
/* === Method::Start === */
protected boolean showClearButton()
{
return true;
}
/* === Method::Start === */
public boolean showDefaultButton()
{
return true;
}
/* === Method::Start === */
public boolean showQueryValues()
{
return true;
}
/* === Method::Start === */
public boolean unpack(container packedClass)
{
version version = runbase::getVersion(packedClass);
container queryCon;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList,queryCon] = packedClass;
if (sysQuery::isPackedOk(queryCon))
qr = new queryRun(queryCon);
else
this.initQuery();
break;
default:
return false;
}
return true;
}
/* === Method::Start === */
void update()
{
CustTrans custTrans;
CustInvoiceTrans custInvoiceTrans;
SalesTable salesTable;
SalesPool salesPool;
QueryBuildRange qbrTransDate;
int covered = 0;
int notcovered = 0;
int total = 0;
int creditNote;
;
if (todate == datenull())
toDate=datemax();
if (toDate < fromDate)
toDate=fromDate;
setprefix("@RDM74");
qbrTransDate = qr.query().dataSourceTable(tablenum(CustTrans)).findRange(fieldnum(CustTrans,transDate));
if (!qbrTransDate)
qbrTransDate=qr.query().dataSourceTable(tablenum(CustTrans)).addRange(fieldnum(CustTrans,transDate));
qbrTransDate.value(date2str(fromDate,-1,-1,-1,-1,-1,-1)+".."+date2str(toDate,-1,-1,-1,-1,-1,-1));
while(qr.next())
{
custTrans = qr.get(tablenum(CustTrans));
if (custTrans.isCreditNote() || custTrans.isInvoice())
{
select firstonly custInvoiceTrans
where custInvoiceTrans.InvoiceId == custTrans.Invoice
&& custInvoiceTrans.InvoiceDate == custTrans.TransDate
join salesTable where salesTable.SalesId == custInvoiceTrans.OrigSalesId
join salesPool where salesPool.SalesPoolId == salesTable.SalesPoolId;
if (custInvoiceTrans)
continue;
if(this.isCreditInsuredCovered(custTrans))
{
this.UpdateCustTrans(custTrans,NoYes::Yes);
covered++;
}
else
{
this.UpdateCustTrans(custTrans,NoYes::No);
notcovered++;
}
total++;
}
}
creditNote = this.UpdateLinkedCreditNotes();
total += creditNote;
covered += creditNote;
if(total)
{
info(strfmt("@RDM70",total,covered,notcovered));
}
else
{
warning("@SYS107537");
}
}
/* === Method::Start === */
void UpdateCustTrans(CustTrans _custTrans,
A_CreditInsuredGuarantee _Guarantee)
{
CustTrans custTrans;
LogisticsPostalAddress custInvAddress;
;
try
{
ttsbegin;
while select forupdate custTrans
where custTrans.RecId == _custTrans.RecId
{
custTrans.A_CreditInsuredGuarantee = _Guarantee;
if (_Guarantee)
custTrans.A_CreditInsuredGuaranteeStatus = A_CreditInsuredGuaranteeStatus::Covered;
else
custTrans.A_CreditInsuredGuaranteeStatus = A_CreditInsuredGuaranteeStatus::NotCovered;
custTrans.update();
custInvAddress = LogisticsPostalAddress::findRecId(custTrans.custInvoiceJour().InvoicePostalAddress);
info(strfmt("@RDM75",custTrans.Invoice, custTrans.transDate, custTrans.AccountNum, custTrans.custTableName(), custTrans.PaymMode, custInvAddress.CountryRegionId, custTrans.A_CreditInsuredGuaranteeStatus));
}
ttscommit;
}
catch(Exception::Error)
{
Error(strfmt("@RDM76",custTrans.Invoice));
}
}
/* === Method::Start === */
int UpdateLinkedCreditNotes()
{
QueryRun qRCustTrans;
Query qCustTrans;
CustTrans custTrans;
CustTrans custTransOffset;
CustSettlement custSettlement;
int tot = 0;
LogisticsPostalAddress custInvAddress;
;
try
{
ttsbegin;
qCustTrans = new Query();
qCustTrans.addDataSource(tablenum(CustTrans));
qCustTrans.dataSourceTable(tablenum(CustTrans)).update(true);
qCustTrans.dataSourceTable(tablenum(CustTrans)).addRange(fieldnum(CustTrans, Invoice)).value(SysQuery::valueNotEmptyString());
qCustTrans.dataSourceTable(tablenum(CustTrans)).addRange(fieldnum(CustTrans, AmountMST)).value("<0");
qCustTrans.dataSourceTable(tablenum(CustTrans)).addRange(fieldnum(CustTrans, A_CreditInsuredGuaranteeStatus)).value(SysQuery::valueNot(A_CreditInsuredGuaranteeStatus::ManualNotCovered));
if (qr.query().dataSourceTable(tablenum(CustTrans)).findRange(fieldnum(CustTrans, AccountNum)))
qCustTrans.dataSourceTable(tablenum(CustTrans)).addRange(fieldnum(CustTrans, AccountNum)).value(qr.query().dataSourceTable(tablenum(CustTrans)).findRange(fieldnum(CustTrans, AccountNum)).value());
qRCustTrans = new QueryRun(qCustTrans);
qRCustTrans.reset();
while(qRCustTrans.next())
{
custTrans = qRCustTrans.get(tablenum(CustTrans));
select firstonly custSettlement
where custSettlement.TransRecId == custTrans.RecId
&& custSettlement.CanBeReversed;
if (!custSettlement)
continue;
select firstonly custTransOffset order by A_CreditInsuredGuaranteeStatus desc
where custTransOffset.Voucher == custSettlement.OffsetTransVoucher
&& custTransOffset.A_CreditInsuredGuarantee
&& (custTransOffset.A_CreditInsuredGuaranteeStatus == A_CreditInsuredGuaranteeStatus::Covered
|| custTransOffset.A_CreditInsuredGuaranteeStatus == A_CreditInsuredGuaranteeStatus::ManualCovered);
if (!custTransOffset)
continue;
custTrans.A_CreditInsuredGuarantee = custTransOffset.A_CreditInsuredGuarantee;
custTrans.A_CreditInsuredGuaranteeStatus = custTransOffset.A_CreditInsuredGuaranteeStatus;
custTrans.update();
custInvAddress = LogisticsPostalAddress::findRecId(custTrans.custInvoiceJour().InvoicePostalAddress);
info(strfmt("@RDM75",custTrans.Invoice, custTrans.transDate, custTrans.AccountNum, custTrans.custTableName(), custTrans.PaymMode, custInvAddress.CountryRegionId, custTrans.A_CreditInsuredGuaranteeStatus));
tot++;
}
ttscommit;
}
catch(Exception::Error)
{
Error(strfmt("@RDM76",custTrans.Invoice));
}
return tot;
}
/* === Method::Start === */
server static A_CreditSalesTableUpdate construct()
{
return new A_CreditSalesTableUpdate();
}
/* === Method::Start === */
static ClassDescription description()
{
return "@RDM74";
}
/* === Method::Start === */
static void main(Args _args)
{
A_CreditSalesTableUpdate A_CreditSalesTableUpdate;
;
A_CreditSalesTableUpdate = A_CreditSalesTableUpdate::Construct();
if(A_CreditSalesTableUpdate.prompt())
A_CreditSalesTableUpdate.run();
}