Announcements
select DTCustomerDetailsTable.SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DTCustomerDetailsTable.DATAAREAID from DTCustomerDetailsTablejoin salestable on salestable.INTERCOMPANYCOMPANYID = DTCustomerDetailsTable.DATAAREAIDwhere DTCustomerDetailsTable.SALESID = 'SO01-10001732'group by DTCustomerDetailsTable.SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DTCustomerDetailsTable.DATAAREAID
while select SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED, DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId from dTCustomerDetailsTable group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED, DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId join salestable where dTCustomerDetailsTable.SALESID == salestable.InterCompanyOriginalSalesId && dTCustomerDetailsTable.DataAreaId == companyId
public class YourClass
{
public void processOperation()
{
CustInvoiceJour custInvoiceJour;
CustInvoiceSalesLine
SalesTable salesTable;
while select custInvoiceJour
where custInvoiceJour.SalesId == 'sa01SO01-10001732'
join InterCompanyOriginalSalesId, InterCompanyCompanyId, WorkerSalesResponsible from salesTable
where salesTable.InterCompanyPurchId == custInvoiceJour.InterCompanyPurchId
{
DTPaymentTrackingTbl paymentTracking;
paymentTracking.initFromCustInvoiceJour(custInvoiceJour);
paymentTracking.DTWorkerName = HcmWorker::find(salesTable.WorkerSalesResponsible).name();
DTCustomerDetailsTable dTCustomerDetailsTable;
while select crosscompany dTCustomerDetailsTable
group by SalesId, PackingSlipId, InvoiceId, InvoiceAmount, AmountNotSettled,
DTCustInvoicePaymType, DTInvoiceStatus, DataAreaId
where dTCustomerDetailsTable.DataAreaId == salesTable.InterCompanyCompanyId
&& dTCustomerDetailsTable.SalesId == salesTable.InterCompanyOriginalSalesId
{
paymentTracking.initFromDTCustomerDetailsTable(dTCustomerDetailsTable);
paymentTracking.DTEntityName = salesTable.InterCompanyCompanyId;
Purchtable purchTable;
Purchline purchLine;
select firstOnly crosscompany CustomerRef, sum(LineAmount) from purchLine
group by purchTable.PurchId, purchTable.PurchStatus, purchTable.IntercompanyCompanyId, purchTable.CreatedDateTime
where purchLine.DataAreaId == salesTable.InterCompanyCompanyId
join purchTable
where purchTable.PurchId == purchLine.PurchId
&& purchTable.PurchId == intPruchId;
paymentTracking.InterCompanyPurchId = purchTable.PurchId;
paymentTracking.PurchStatus = purchTable.PurchStatus;
paymentTracking.DTDateTime = purchTable.CreatedDateTime;
paymentTracking.CustomerRef = purchLine.CustomerRef;
paymentTracking.LineAmount = purchLine.LineAmount;
if (DTPaymentTrackingTbl::exist(
paymentTracking.SalesId,
paymentTracking.InvoiceId,
paymentTracking.PackingSlipId)
{
this.updateInvoicesDetails(paymentTracking);
}
else
{
paymentTracking.insert();
}
}
}
}
}
public class DTPaymentTrackingTbl extends common
{
public void initFromCustInvoiceJour(CustInvoiceJour _custInvoiceJour)
{
this.InvoiceAccount = _custInvoiceJour.InvoiceAccount;
this.Name = _custInvoiceJour.custTable_OrderAccount().name();
}
public void initFromDTCustomerDetailsTable(DTCustomerDetailsTable _custDetails)
{
this.SalesId = _custDetails.SalesId;
this.PackingSlipId = _custDetails.PackingSlipId;
this.InvoiceId = _custDetails.InvoiceId;
this.InvoiceAmount = _custDetails.InvoiceAmount;
this.AmountNotSettled = _custDetails.AmountNotSettled;
this.DTCustInvoicePaymtType = _custDetails.DTCustInvoicePaymtType;
this.DTInvoiceStatus = _custDetails.DTInvoiceStatus;
}
public static boolean exist(...)
{
...
}
}
public void processOperation()
{
CustInvoiceJour custInvoiceJour,custInvoiceJourloc;
SALESTABLE salestable;
DTCustomerDetailsTable dTCustomerDetailsTable;
Purchtable purchTable;
Purchline purchLine;
Custtable custtable;
DirPartyTable dirPartyTable;
HCMWORKER HcmWorker;
DTPaymentTrackingTbl dTPaymentTrackingTbl,dTPaymentTrackingTblUpdate;
InterCompanyPurchId intPruchId;
CustInvoiceId invoiceid;
InterCompanyCompanyId intercompanyId;
//SalesId salesId;
str salesId;
while select INVOICEACCOUNT,SALESID,INTERCOMPANYPURCHID,InvoiceId,InterCompanyCompanyId from custInvoiceJour
where custInvoiceJour.SalesId == 'sa01SO01-10001732'
{
intPruchId = custInvoiceJour.InterCompanyPurchId;
invoiceid = custInvoiceJour.InvoiceId;
dTPaymentTrackingTbl.InvoiceAccount = custInvoiceJour.InvoiceAccount;
select firstonly name from dirPartyTable
join custtable
where custtable.PARTY == dirPartyTable.RecId
join custInvoiceJourloc
where custtable.AccountNum == custInvoiceJourloc.OrderAccount
&& custInvoiceJourloc.InvoiceId == invoiceid;
dTPaymentTrackingTbl.Name = dirPartyTable.Name;
while select InterCompanyOriginalSalesId,WORKERSALESRESPONSIBLE,InterCompanyCompanyId from salestable
where salestable.INTERCOMPANYPURCHID == intPruchId
{
intercompanyId = salestable.InterCompanyCompanyId;
salesId = salestable.InterCompanyOriginalSalesId;
select Name from dirPartyTable
join hcmWorker
where hcmWorker.Person == dirPartyTable.RecId
join salestable
where salestable.WorkerSalesResponsible == hcmWorker.RecId
&& salestable.InterCompanyPurchId == intPruchId;
dTPaymentTrackingTbl.DTWorkerName = dirPartyTable.Name;
while select crosscompany SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId from dTCustomerDetailsTable
group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
where dTCustomerDetailsTable.DataAreaId == intercompanyId
join salestable
where dTCustomerDetailsTable.SALESID == salesId
{
dTPaymentTrackingTbl.SalesId = dTCustomerDetailsTable.SalesId;
dTPaymentTrackingTbl.PackingSlipId = dTCustomerDetailsTable.PackingSlipId;
dTPaymentTrackingTbl.InvoiceId = dTCustomerDetailsTable.InvoiceId;
dTPaymentTrackingTbl.InvoiceAmount = dTCustomerDetailsTable.InvoiceAmount;
dTPaymentTrackingTbl.AmountNotSettled = dTCustomerDetailsTable.AmountNotSettled;
dTPaymentTrackingTbl.DTCustInvoicePaymtType = dTCustomerDetailsTable.DTCustInvoicePaymtType;
dTPaymentTrackingTbl.DTInvoiceStatus = dTCustomerDetailsTable.DTInvoiceStatus;
dTPaymentTrackingTbl.DTEntityName = intercompanyId;
select crosscompany CustomerRef,sum(LineAmount) from purchLine
where purchLine.DataAreaId == intercompanyId
join * from purchTable
group by purchTable.PURCHID,purchTable.PURCHSTATUS,purchTable.INTERCOMPANYCOMPANYID,purchTable.CREATEDDATETIME
where purchLine.PURCHID == purchTable.PURCHID
&& purchTable.PURCHID == intPruchId;
dTPaymentTrackingTbl.InterCompanyPurchId = purchTable.PurchId;
dTPaymentTrackingTbl.PurchStatus = purchTable.PurchStatus;
dTPaymentTrackingTbl.DTDateTime = purchTable.CREATEDDATETIME;
dTPaymentTrackingTbl.CustomerRef = purchLine.CustomerRef;
dTPaymentTrackingTbl.LineAmount = purchLine.LineAmount;
select firstonly dTPaymentTrackingTblUpdate
where dTPaymentTrackingTblUpdate.SalesId == dTPaymentTrackingTbl.SalesId
&& dTPaymentTrackingTblUpdate.InvoiceId == dTPaymentTrackingTbl.InvoiceId
&& dTPaymentTrackingTblUpdate.PackingSlipId == dTPaymentTrackingTbl.PackingSlipId;
if(dTPaymentTrackingTblUpdate.RecId != 0)
{
this.UpdateInvoicesDetails(dTPaymentTrackingTbl);
}
else
{
dTPaymentTrackingTbl.insert();
}
}
}
}
}
SELECT T1.SALESID,T1.PACKINGSLIPID,T1.INVOICEID,T1.INVOICEAMOUNT,T1.AMOUNTNOTSETTLED,T1.DTCUSTINVOICEPAYMTTYPE,T1.DTINVOICESTATUS
FROM DTCUSTOMERDETAILSTABLE T1 CROSS JOIN SALESTABLE T2 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'sa09')) AND (T1.DATAAREAID=N'sa01'))
AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'sa09')) AND (T1.SALESID=N'SO01-10001732'))
GROUP BY T1.SALESID,T1.PACKINGSLIPID,T1.INVOICEID,T1.INVOICEAMOUNT,T1.AMOUNTNOTSETTLED,T1.DTCUSTINVOICEPAYMTTYPE,T1.DTINVOICESTATUS,T1.DATAAREAID
ORDER BY T1.SALESID,T1.PACKINGSLIPID,T1.INVOICEID,T1.INVOICEAMOUNT,T1.AMOUNTNOTSETTLED,T1.DTCUSTINVOICEPAYMTTYPE,T1.DTINVOICESTATUS,T1.DATAAREAID
select generateOnly forceLiterals dTCustomerDetailsTable
group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
where dTCustomerDetailsTable.DataAreaId == companyId
join salestable
where dTCustomerDetailsTable.SALESID == salesId;
info(dTCustomerDetailsTable.getSQLStatement());
class DTPaymentTrackingBatchService extends SysOperationServiceBase
{
public void processOperation()
{
CustInvoiceJour custInvoiceJour,custInvoiceJourloc;
SALESTABLE salestable;
DTCustomerDetailsTable dTCustomerDetailsTable;
Purchtable purchTable;
Purchline purchLine;
Custtable custtable;
DirPartyTable dirPartyTable;
HCMWORKER HcmWorker;
DTPaymentTrackingTbl dTPaymentTrackingTbl;
InterCompanyPurchId intPruchId;
CustInvoiceId invoiceid;
InterCompanyCompanyId companyId;
SalesId salesId;
while select INVOICEACCOUNT,SALESID,INTERCOMPANYPURCHID,InvoiceId,InterCompanyCompanyId from custInvoiceJour
where custInvoiceJour.SalesId == 'sa01SO01-10001732'
{
intPruchId = custInvoiceJour.InterCompanyPurchId;
invoiceid = custInvoiceJour.InvoiceId;
dTPaymentTrackingTbl.InvoiceAccount = custInvoiceJour.InvoiceAccount;
select firstonly name from dirPartyTable
join custtable
where custtable.PARTY == dirPartyTable.RecId
join custInvoiceJourloc
where custtable.AccountNum == custInvoiceJourloc.OrderAccount
&& custInvoiceJourloc.InvoiceId == invoiceid;
dTPaymentTrackingTbl.Name = dirPartyTable.Name;
while select InterCompanyOriginalSalesId,WORKERSALESRESPONSIBLE,InterCompanyCompanyId from salestable
where salestable.INTERCOMPANYPURCHID == 'PO01-10002188'
{
companyId = salestable.InterCompanyCompanyId;
= salestable.InterCompanyOriginalSalesId;
select Name from dirPartyTable
join hcmWorker
where hcmWorker.Person == dirPartyTable.RecId
join salestable
where salestable.WorkerSalesResponsible == hcmWorker.RecId
&& salestable.InterCompanyPurchId == 'PO01-10002188';
dTPaymentTrackingTbl.DTWorkerName = dirPartyTable.Name;
while select CrossCompany SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId from dTCustomerDetailsTable
group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
join salestable
where dTCustomerDetailsTable.SALESID == salestable.InterCompanyOriginalSalesId
&& dTCustomerDetailsTable.DataAreaId == companyId
{
dTPaymentTrackingTbl.SalesId = dTCustomerDetailsTable.SalesId;
dTPaymentTrackingTbl.PackingSlipId = dTCustomerDetailsTable.PackingSlipId;
dTPaymentTrackingTbl.InvoiceId = dTCustomerDetailsTable.InvoiceId;
dTPaymentTrackingTbl.InvoiceAmount = dTCustomerDetailsTable.InvoiceAmount;
dTPaymentTrackingTbl.AmountNotSettled = dTCustomerDetailsTable.AmountNotSettled;
dTPaymentTrackingTbl.DTCustInvoicePaymtType = dTCustomerDetailsTable.DTCustInvoicePaymtType;
dTPaymentTrackingTbl.DTInvoiceStatus = dTCustomerDetailsTable.DTInvoiceStatus;
select CustomerRef,sum(LineAmount) from purchLine
join * from purchTable
group by purchTable.PURCHID,purchTable.PURCHSTATUS,purchTable.INTERCOMPANYCOMPANYID,purchTable.CREATEDDATETIME
where purchLine.PURCHID == purchTable.PURCHID
&& purchTable.PURCHID == 'PO01-10002188';
dTPaymentTrackingTbl.InterCompanyPurchId = purchTable.PurchId;
dTPaymentTrackingTbl.PurchStatus = purchTable.PurchStatus;
dTPaymentTrackingTbl.DTDateTime = purchTable.CREATEDDATETIME;
dTPaymentTrackingTbl.CustomerRef = purchLine.CustomerRef;
dTPaymentTrackingTbl.LineAmount = purchLine.LineAmount;
dTPaymentTrackingTbl.insert();
}
}
}
}
}
select generateOnly forceLiterals dTCustomerDetailsTable
group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
where dTCustomerDetailsTable.DataAreaId == companyId
join salesTable
where salesTable.InterCompanyOriginalSalesId == dTCustomerDetailsTable.SalesId;
info(dTCustomerDetailsTable.getSQLStatement());
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,807 Super User 2024 Season 2
Martin Dráb 229,135 Most Valuable Professional
nmaenpaa 101,156