vendor import ref pepper

vendor import ref pepper


static void VendorImport(Args _args)
{
    Dialog                      dialog;
    Filename                    filename;
    DialogField                 dialogFileName;

    InboundMerchantTable            vendTable;
    InboundMerchantCommunication    vendAddress;
    EcomMerchantContact             vendContact;

    COMVariantType              type;
    SysExcelCells               cells;
    SysExcelWorkbook            workbook;
    SysExcelWorkbooks           workbooks;
    SysExcelWorksheet           worksheet;
    SysExcelWorksheets          worksheets;
    SysExcelApplication         application;

    VendAccount                 vendAccount;
    Name                        vendName;
    CurrencyCode                currency;
    VendGroupId                 vendGroupId;
    DlvTermId                   dlvTermId;
    PaymTermId                  paymTermId;
    PaymMode                    paymMode;
    EcomBusinessModel           ecomBusinessModel;
    Email                       stockIntimationEmail;
    str                         vendBankAccountId;
    VendBank                    vendBank;
    Name                        bankName;
    SWIFTNo                     SWIFTNo;
    BankIBAN                    bankIBAN;
    str                         routingNum;
    str                         ifsc, beneficiaryName;

    str                         purpose;
    Addressing                  street;
    LogisticsAddressCity        city;
    LogisticsAddressStateId     state;
    LogisticsAddressZipCodeId   zipCode;

    LogisticsAddressCountryRegionId      county;

    TaxRegistrationNumber_IN    vatRegistration;
    TaxRegistrationNumber_IN    cstRegistration;
    TaxRegistrationNumber_IN    serviceTaxRegistration;
    TaxRegistrationNumber_IN    tanRegistration;
    TaxGroup                    salesTaxGroup;
    TaxGroup                    whtTaxGroup;
    PANNumber_IN                PANNumber;

    ContactPersonName           contactPersonName;
    ContactPersonName           addContactPersonName;
    Phone                       contactPhone, contactPhone2;
    Email                       contactEmail;
    Email                       POEmail;
    Email                       PREmail;

    int                         processed, failed, totalRecords = 1000, row = 1;
    SysOperationProgress        simpleProgress;
    #AviFiles
    ;

    dialog = new Dialog("Import payment");
    dialogFileName = dialog.addField(extendedTypeStr(Filenameopen), "File path");

    dialog.run();

    if (dialog.run())
    {
        filename = dialogFileName.value();
    }

    simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, 'Importing payment lines.', totalRecords);

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();

    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }

    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();

    //Iterate through cells and get the values
    ttsBegin;
    do
    {
        //Incrementing the row line to next Row
        row++;
        vendAccount              = COMVariant2Str(cells.item(row, 1).value());
        vendName                 = COMVariant2Str(cells.item(row, 2).value());
        currency                 = COMVariant2Str(cells.item(row, 3).value());
        vendGroupId              = COMVariant2Str(cells.item(row, 4).value());
        dlvTermId                = COMVariant2Str(cells.item(row, 5).value());
        paymTermId               = COMVariant2Str(cells.item(row, 6).value());
        paymMode                 = COMVariant2Str(cells.item(row, 7).value());
        ecomBusinessModel        = str2enum(ecomBusinessModel, COMVariant2Str(cells.item(row, 8).value()));
        stockIntimationEmail     = COMVariant2Str(cells.item(row, 9).value());

        vendBankAccountId        = COMVariant2Str(cells.item(row, 10).value());
        vendBank                 = COMVariant2Str(cells.item(row, 11).value());
        bankName                 = COMVariant2Str(cells.item(row, 12).value());
        SWIFTNo                  = COMVariant2Str(cells.item(row, 13).value());
        bankIBAN                 = COMVariant2Str(cells.item(row, 14).value());
        routingNum               = COMVariant2Str(cells.item(row, 15).value());
        ifsc                     = COMVariant2Str(cells.item(row, 16).value());

        purpose                  = COMVariant2Str(cells.item(row, 17).value());
        street                   = COMVariant2Str(cells.item(row, 18).value());
        city                     = COMVariant2Str(cells.item(row, 19).value());
        state                    = COMVariant2Str(cells.item(row, 20).value());
        zipCode                  = COMVariant2Str(cells.item(row, 21).value());
        county                   = COMVariant2Str(cells.item(row, 22).value());

        vatRegistration          = COMVariant2Str(cells.item(row, 23).value());
        cstRegistration          = COMVariant2Str(cells.item(row, 24).value());
        serviceTaxRegistration   = COMVariant2Str(cells.item(row, 25).value());
        tanRegistration          = COMVariant2Str(cells.item(row, 26).value());
        salesTaxGroup            = COMVariant2Str(cells.item(row, 27).value());
        whtTaxGroup              = COMVariant2Str(cells.item(row, 28).value());
        PANNumber                = COMVariant2Str(cells.item(row, 29).value());

        contactPersonName        = COMVariant2Str(cells.item(row, 30).value());
        addContactPersonName     = COMVariant2Str(cells.item(row, 31).value());
        contactPhone             = COMVariant2Str(cells.item(row, 32).value());
        contactPhone2            = COMVariant2Str(cells.item(row, 33).value());
        contactEmail             = COMVariant2Str(cells.item(row, 34).value());
        POEmail                  = COMVariant2Str(cells.item(row, 35).value());
        PREmail                  = COMVariant2Str(cells.item(row, 36).value());

        beneficiaryName          = COMVariant2Str(cells.item(row, 37).value());


        vendTable.clear();
        vendTable.initValue();

        vendTable.MerchantId            = vendAccount;
        vendTable.Name                  = vendName;
        vendTable.Currency              = currency;
        vendTable.TermsOfPayment        = paymTermId;
        vendTable.MethodOfPayment       = paymMode;
        vendTable.EcomBusinessModel     = ecomBusinessModel;

        vendTable.VendBankAccountNum    = vendBankAccountId;
        vendTable.VendBankAccountId     = vendBank;
        vendTable.VendBankName          = bankName;
        vendTable.SWIFTNo               = SWIFTNo;
        vendTable.IFSCCode              = ifsc;
        vendTable.IBAN                  = bankIBAN;
        vendTable.BankRegistrationNum   = routingNum;
        vendTable.PANNumber             = PANNumber;
        vendTable.TDSGroup              = whtTaxGroup;
        vendTable.isVendorOnly          = true;

        vendTable.BeneficiaryName       = beneficiaryName;

        vendTable.ContactFirstName      = contactPersonName;
        vendTable.Email                 = contactEmail;
        vendTable.Phone                 = contactPhone;
        vendTable.Phone2                = contactPhone2;
        vendTable.StockIntimationEmail  = stockIntimationEmail;
        vendTable.PurchaseIntimationEmail= POEmail;

        vendTable.insert();

        vendAddress.clear();
        vendAddress.initValue();

        vendAddress.InboundMerchantTable= vendTable.RecId;

        vendAddress.AddressId           = vendTable.MerchantId + "_1";
        vendAddress.Address             = street;
        vendAddress.City                = city;
        vendAddress.StateId             = state;
        vendAddress.ZipCode             = zipCode;
        vendAddress.CountryRegionId     = county;

        vendAddress.SalesTaxRegistrationNumber     = cstRegistration;
        vendAddress.TIN                            = vatRegistration;
        vendAddress.ServiceTaxRegistrationNumber   = serviceTaxRegistration;
        vendAddress.TANRegistrationNumber          = tanRegistration;
        vendAddress.Email                          = contactEmail;
        vendAddress.Phone                          = contactPhone;

        vendAddress.insert();


        vendContact.clear();
        vendContact.initValue();

        vendContact.InboundMerchantTable    = vendTable.RecId;
        vendContact.FirstName               = addContactPersonName;
        vendContact.Email                   = contactEmail;
        vendContact.Phone                   = contactPhone;
        vendContact.Phone1                  = contactPhone2;

        vendContact.insert();

        simpleProgress.incCount();
        simpleprogress.setText(strfmt("Vendor no.: %1 Rows: %2", vendAccount, row));
        simpleprogress.update(true);

        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);

    ttsCommit;

    // quits the application
    application.quit();

    info(strFmt("Total: %1 Processed: %2 Failed: %3", row, processed, failed));
}

Get GST rates ref pepper

Get GST rates ref pepper

static void aks_GetGSTRate(Args _args)
{
    TaxRuntimeLookupCondition               taxRuntimeLookupCondition;
    TaxRuntimeLookupMeasureResult           taxRuntimeLookupMeasureResult;
    TaxRuntimeLookupMeasureResultDetail     taxRuntimeLookupMeasureResultDetail;
    TaxRuntimeLookup                        taxRuntimeLookup;
    TaxRuntimeLookupVersion                 taxRuntimeLookupVersion;
    TaxRuntimeDocComponentMeasure           taxRuntimeDocComponentMeasure;
    TaxRuntimeDocComponentMeasureVersion    taxRuntimeDocComponentMeasureVersion;
    TaxRuntimeDocComponent                  taxRuntimeDocComponent;
    TaxRuntimeDocComponentVersion           taxRuntimeDocComponentVersion;
    TaxRuntimeDocTaxType                    taxRuntimeDocTaxType;
    TaxRuntimeDocTaxTypeVersion             taxRuntimeDocTaxTypeVersion;


    while select taxRuntimeLookupCondition
        where taxRuntimeLookupCondition.DimValue1 == 'KA' &&
        taxRuntimeLookupCondition.DimValue2 == '73239200' &&
        taxRuntimeLookupCondition.Ledger == CompanyInfo::find().RecId
    {
        taxRuntimeLookupVersion = TaxRuntimeLookupVersion::find(taxRuntimeLookupCondition.LookupVersion);
        taxRuntimeLookup = TaxRuntimeLookup::find(taxRuntimeLookupVersion.Lookup);

        switch(taxRuntimeLookup.LookupOwnerTableId)
        {
            case tableNum(TaxRuntimeDocComponentMeasure):
                taxRuntimeDocComponentMeasure   =   TaxRuntimeDocComponentMeasure::find(taxRuntimeLookup.LookupOwnerRecId);

                select taxRuntimeDocComponentMeasureVersion order by taxRuntimeDocComponentMeasureVersion.RecId desc
                        where taxRuntimeDocComponentMeasureVersion.TaxRuntimeDocComponentMeasure == taxRuntimeDocComponentMeasure.RecId;

                select firstOnly taxRuntimeLookupMeasureResult
                        join forUpdate taxRuntimeLookupMeasureResultDetail
                    where taxRuntimeLookupMeasureResult.LookupCondition == taxRuntimeLookupCondition.RecId
                        && taxRuntimeLookupMeasureResultDetail.LookupMeasureResult == taxRuntimeLookupMeasureResult.RecId;
                break;

            case tableNum(TaxRuntimeDocComponent):
                taxRuntimeDocComponent  =   TaxRuntimeDocComponent::find(taxRuntimeLookup.LookupOwnerRecId);

                select taxRuntimeDocComponentVersion order by taxRuntimeDocComponentVersion.RecId desc
                        where taxRuntimeDocComponentVersion.TaxRuntimeDocComponent ==  taxRuntimeDocComponent.RecId;
                break;

            case tableNum(TaxRuntimeDocTaxType):
                taxRuntimeDocTaxType    =   TaxRuntimeDocTaxType::find(taxRuntimeLookup.LookupOwnerRecId);

                select taxRuntimeDocTaxTypeVersion order by taxRuntimeDocTaxTypeVersion.RecId
                        where taxRuntimeDocTaxTypeVersion.TaxRuntimeDocTaxType ==  taxRuntimeDocTaxType.RecId;
                break;
        }

        info(strFmt("Path: %1,Value:%2",taxRuntimeDocComponentMeasure.Path,taxRuntimeLookupMeasureResultDetail.Value));
    }
}

Remove reserved item

Remove reserved item Ref pepper

static void A_RemoveReserveItem(Args _args)
{
    InventTrans             inventTrans;
    InventTransOrigin       inventTransOrigin;
    InventMovement          inventMovement;
    InventUpd_Reservation   inventUpd_Reservation ;

    while select inventTrans
    where  inventTrans.ItemId                      == -P-WH4688'
    &&  inventTrans.StatusReceipt               == StatusReceipt::None
    && (inventTrans.StatusIssue                 == StatusIssue::ReservPhysical
    ||  inventTrans.StatusIssue                 == StatusIssue::ReservOrdered)
    exists join inventTransOrigin
    where   inventTransOrigin.RecId            == inventTrans.InventTransOrigin
    {

    if (inventTrans.MarkingRefInventTransOrigin)
    {
    InventTransOrigin::deleteMarking(inventTrans.MarkingRefInventTransOrigin, inventTrans.InventTransOrigin, -inventTrans.Qty, true);
    InventTransOrigin::deleteMarking(inventTrans.InventTransOrigin, inventTrans.MarkingRefInventTransOrigin, inventTrans.Qty, true);
    }

    if (inventTrans.StatusIssue == StatusIssue::ReservPhysical || inventTrans.StatusIssue == StatusIssue::ReservOrdered)
    {
    Inventmovement = inventTrans.inventmovement(true);
    inventUpd_Reservation = InventUpd_Reservation::newInventDim(inventmovement,inventTrans.inventDim(), -1 * inventTrans.Qty, false);
    inventUpd_Reservation.updatenow();
    }
    }
    info('Done');
}

on hand qty

on hand qty

static void OnHandQty(Args _args)
{
    InventDim       inventDim;
    InventSum       inventSum;
    InventOnhand    inventOnHand;
    InventDimParm   inventDimParm;
    ItemId          itemId;
    InventQty       availQty;
    InventQty       inventQty;

    while select sum(postedQty), sum(received), sum(deducted), sum(registered),
    sum(picked), sum(reservPhysical)
    from InventSum group by itemId
    join InventDim
    where inventSum.inventDimId == inventDim.inventDimId &&
    inventSum.itemId == 'B' &&
    inventDim.inventLocationId == 'Mumbai'
    {
    inventQty = inventSum.PostedQty+inventSum.Received+inventSum.Deducted-
    inventSum.Picked-inventSum.ReservPhysical;
    }
    info(strfmt('%1', inventQty));

    itemId = 'BA1502926-P-WH15766-01';
    inventDim.InventLocationId = 'Mumbai';
    inventDimParm.initFromInventDim(inventDim);
    inventOnHand = inventOnHand::newParameters(itemId,inventDim,inventDimParm);
    availQty = inventOnHand.availPhysical();
    info(strfmt('%1', availQty));
}

get numerical from the string

Get numerical from the string

gstStr  = subStr(this.RegistrationNumber,1,2);

    if(strlen(gstStr) != strlen(strkeep(gstStr,'1234567890')))
    {
        ret = checkFailed("First two digists should be numericals");
    }

Run SQL queries in Sql Server

Run SQL queries

select SL.SALESID, SL.CREATEDDATETIME, sl.ECOMSALESID, SL.ORDERSKUID, sl.ITEMID, sl.SALESQTY, SL.LINEAMOUNT, SL.SALESSTATUS, id.INVENTSITEID, id.INVENTLOCATIONID from SALESLINE as SL
 Join INVENTDIM as ID
 ON ID.INVENTDIMID = SL.INVENTDIMID
 And ID.DATAAREAID = sl.DATAAREAID
where SL.SALESQTY < 0
And sl.DATAAREAID = 'tsc'
And SL.CREATEDDATETIME >= '2017-04-01'
And SL.CREATEDDATETIME <= '2018-01-31'


........................................................................

select ECOMT.ORDERSKUID,ECOMT.SOPOSTERRORMSG,ECOMT.COURIERPICKDATETIME,SLIN.HSNCODETABLE, SL.ORDERSKUID,SL.SALESID,SL.TAXGROUP,SL.SALESSTATUS from SALESLINE as SL
join INBOUNDWEBORDERLINE as IWL on IWL.SALESID = SL.SALESID
and  IWL.ITEMID = SL.ITEMID
join ECOMMANIFESTTABLE as ECOMT on ECOMT.ORDERSKUID = IWL.ORDERSKUID
join INBOUNDWEBORDERHEADER as IWH on IWH.EcomSalesId = IWL.ECOMSALESID
join ALLE_MANIFESTSTATUS as AMS on AMS.ECOMMANIFESTTABLE = ECOMT.RECID
join SALESLINE_IN as SLIN on SLIN.SALESLINE = SL.RECID
where SLIN.HSNCODETABLE = 0
AND ECOMT.POSTED = 0
AND IWL.ONHOLD = 0
AND IWL.ISRENTALORDER = 0
AND IWH.ONHOLD = 0
AND SL.DATAAREAID = 'tsc'
AND SLIN.DATAAREAID = SL.DATAAREAID
AND IWH.ORDERCONFIRMATIONDATE >= '2017-01-01'
And ECOMT.COURIERPICKDATETIME >= '2017-04-01'
And ECOMT.COURIERPICKDATETIME <= '2017-06-30'

SSRS report save and trigger mail

passing string255 value from Form to the class as

PaymentAdviceSettlementEmail.parmSpecTransRecIdRange(orgRange);
   PaymentAdviceSettlementEmail.run();


.................................................................................................................................


class PaymentAdviceSettlementEmail extends RunBaseBatch
{
    String255       recIdRange;
}

............

public void deleteReportFile(str _reportPath)
{
    if(!_reportPath)
        warning('No file in local to remove');
    else
        System.IO.File::Delete(@_reportPath);
}

..................

private Integer fileSizeInByte(FilePath _filePath)
{
    return WinAPI::filesize(_filePath);
}

.........................

Public void parmSpecTransRecIdRange(String255   _range)
{
    recIdRange =  _range;
}

.............

public NoYesBlank PaymentAdviceEmail(String255 _specTrans)
{
    str                         ccsMailAddr,specRecId;
    int64                       specRecIdInt;
    SpecTrans                   specTrans;
    VendTable                   vendTable;
    VendTrans                   vendTrans;
    Map                         parameterMap = new Map(Types::String, Types::String);
    Email                       requester;

    FilenameOpen                attachmentFilename;
    LogisticsElectronicAddress  logisticsElectronicAddress;
    DirPartyLocation            dirPartyLocation;
    VendPurchOrderJour          vendPurchOrderJour;
    ;

    attachmentFilename = this.runAndSaveSSRSReport(_specTrans);

    specRecId = subStr(_specTrans,1,10);
    specRecIdInt = str2int64(specRecId);

    select specTrans where specTrans.RecId == specRecIdInt;
    select vendTable where vendTable.dataAreaId == specTrans.SpecCompany && vendTable.TableId == specTrans.SpecTableId && vendTable.RecId == specTrans.SpecRecId;

    select RecId from dirPartyLocation
        where dirPartyLocation.Party == VendTable::find(vendTable.AccountNum).Party
    join logisticsElectronicAddress
        where logisticsElectronicAddress.Location   == dirPartyLocation.Location &&
        logisticsElectronicAddress.Type             == LogisticsElectronicAddressMethodType::Email &&
        logisticsElectronicAddress.EcomEmailType    == EcomEmailType::PaymentAdvice;

    if (logisticsElectronicAddress.Locator)
    {
        requester   = logisticsElectronicAddress.Locator;
    }
    else
    {
        requester   = LogisticsElectronicAddress::findRecId(DirPartyTable::findRec(
            VendTable::find(vendTable.AccountNum).Party).PrimaryContactEmail).Locator;
    }

    select vendTrans where vendTrans.AccountNum == vendTable.AccountNum;

    parameterMap.insert('VendorName', VendTable::find(vendTable.AccountNum).name());
    parameterMap.insert('VendorCode', vendTable.AccountNum);
    //parameterMap.insert('InvoiceDate', date2str(vendTrans.TransDate, 123, 2, DateSeparator::Slash, 2, DateSeparator::Slash, 4,DateFlags::None));

    ccsMailAddr = SysEmailTable::find(VendParameters::find().EcomPayAdviceEmailId).EcomReceiverCCs;

    try
    {
        SysEmailTable::sendMail(VendParameters::find().EcomPayAdviceEmailId, SysEmailTable::find(VendParameters::find().EcomPayAdviceEmailId).DefaultLanguage,
                                  requester, parameterMap, attachmentFilename, '', true, curUserId(), true,  strLTrim(strRTrim(ccsMailAddr)),
                                  BaseEmailDocument::PaymentAdvice,"");

        this.deleteReportFile(attachmentFilename);
    }
    catch
    {
        throw Error('Unable to send the E-mail.');
    }

    return NoYesBlank::Yes;
}

////////////////////////////////////////////////////////////////

public void run(Args _args = null)
{
    this.PaymentAdviceEmail(recIdRange);
}


......................................


public str runAndSaveSSRSReport(String255 _specTrans)
{
    str reportPath, attachmentFolder;
    SrsReportRunController           ssrsController = new SrsReportRunController();
    Alle_PaymentAdviceVendContract  contract = new Alle_PaymentAdviceVendContract();


    SRSPrintDestinationSettings     printerSettings;
    ;

    attachmentFolder = SysEmailParameters::find().AttachmentsPath;

    reportPath = attachmentFolder + "\\" + "PaymentAdvice" +'.pdf';

    ssrsController.parmReportName(ssrsReportStr(Alle_PaymentAdviceVendReport, PaymentAdviceVendReport));
    ssrsController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
    ssrsController.parmShowDialog(false);
    contract.parmString(_specTrans);
    ssrsController.parmReportContract().parmRdpContract(contract);

    //link the printer settings to the controller
    printerSettings = ssrsController.parmReportContract().parmPrintSettings();

    //print to pdf and always overwrite if the file exists
    printerSettings.printMediumType(SRSPrintMediumType::File);
    printerSettings.fileFormat(SRSReportFileFormat::PDF);
    printerSettings.overwriteFile(true);
    printerSettings.fileName(@ReportPath);

    //run & save the report
    ssrsController.runReport();

    if ((SysEmailParameters::find().EcomCheckPDFMinSize == NoYes::Yes) && (this.fileSizeInByte(@ReportPath) < SysEmailParameters::find().EcomMinPDFSize))
        throw error('Please check the report manually.');

    return reportPath;
}


..................................................

server static ALE_PaymentAdviceSettlementEmail construct()
{
    return new ALE_PaymentAdviceSettlementEmail();
}


..................................

static void main(Args _args)
{
    ALE_PaymentAdviceSettlementEmail   paymentAdviceSettlementEmail =   new ALE_PaymentAdviceSettlementEmail();

    if (paymentAdviceSettlementEmail.prompt())
    {
        paymentAdviceSettlementEmail.run(_args);
    }

}

.............................................



now report classess

[DataContractAttribute]
class PaymentAdviceVendContract
{
    String255   settleRecId;

}

....................


public String255 parmString(String255 _settleRecId = settleRecId)
{
    settleRecId = _settleRecId;
    return settleRecId;
}


.......................

[
 
    SRSReportParameterAttribute(classStr(PaymentAdviceVendContract))
]
class Alle_PaymentAdviceVendDP extends SrsReportDataProviderPreProcessTempDB
{
    TransDate                           fromDate,toDate;
    VendAccount                         vendorAccount;
    PaymentAdviceVendTmp           PaymentAdviceVendTmp;2018
}


/..............................

[
    SRSReportDataSetAttribute(tableStr(PaymentAdviceVendTmp))
]
public aymentAdviceVendTmp getPurchaseRegisterTmp()
{
    select PaymentAdviceVendTmp;
    return PaymentAdviceVendTmp;
}

...............


/
[
    SysEntryPointAttribute(false)
]
public void processReport()
{
    SpecTrans                       specTrans;
    container                       convertStr2Int64;
    String255                       settledRedIdLoc;
    int64                           settleRecIdLoc,settleRecIdRange;
    int                             i;

    PaymentAdviceVendContract paymentAdviceVendContract;

    paymentAdviceVendContract   = this.parmDataContract() as PaymentAdviceVendContract;
    settledRedIdLoc             = paymentAdviceVendContract.parmString();
    convertStr2Int64            = str2con(settledRedIdLoc,",",true);

    for(i=1;i <= conlen(convertStr2Int64); i++)
    {
        settleRecIdRange = conpeek(convertStr2Int64, i);

        if(settleRecIdRange)
        {
            while select specTrans where specTrans.RecId == settleRecIdRange
            {
                this.insertTmpTable(specTrans);
            }
        }
    }
}



.............................

private void insertTmpTable(SpecTrans   _spectrans)
{
    VendTransOpen       vendtransopen;
    VendTrans           vendTrans;
 
    BankAccountTrans    bankAccountTrans;


    select firstOnly RefRecId, AccountNum, AmountCur, TDSAmount_IN from vendtransopen
        where vendtransopen.TableId == _spectrans.RefTableId && vendtransopen.RecId ==  _spectrans.RefRecId;

    select firstOnly Voucher, AccountNum, Invoice, CurrencyCode from vendTrans
        where vendTrans.RecId == vendtransopen.RefRecId && vendTrans.AccountNum == vendtransopen.AccountNum;


    select firstOnly AccountId from  bankAccountTrans
        where bankAccountTrans.Voucher == vendTrans.Voucher;
 

    PaymentAdviceVendTmp.Voucher       =   vendTrans.Voucher;
 
 
    PaymentAdviceVendTmp.insert();

}

...............................................................................................................

Controller class


public class PaymentAdviceVendController extends SrsReportRunController
{
    Map             SpecTransRecId;
    String255       recIdRange;
    MapEnumerator   mapEnumerator;
    #define.ReportName('PaymentAdviceVendReport.PaymentAdviceVendReport')
}

......................

Public void parmSpecTransRecIdRange(String255   _range)
{
    recIdRange =  _range;
}

....
...

protected void prePromptModifyContract()
{
    //this.setRanges(this.parmReportContract().parmQueryContracts().lookup(this.getFirstQueryContractKey()));
    this.setRanges(this.getFirstQuery());
}

.......

protected void preRunModifyContract()

{

    #ISOCountryRegionCodes

    SrsReportDataContract  contract;

    contract = this.parmReportContract() as SrsReportDataContract;

}

...

public void ReportRun(String255 _rangeLoc)

{

    Alle_PaymentAdviceVendController controller = new Alle_PaymentAdviceVendController();

    controller.parmReportName('Alle_PaymentAdviceVendReport.PaymentAdviceVendReport');

    recIdRange = _rangeLoc;

    controller.parmSpecTransRecIdRange(recIdRange);

    controller.parmShowDialog(false);

    controller.startOperation();

}

....

public void setRanges(Query _query)

{

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    qbds = _query.dataSourceTable(tableNum(SpecTrans));

    qbds.clearRanges();

    qbr = qbds.addRange(fieldNum(SpecTrans, RecId));

    qbr.value(recIdRange);

}

...

/// <summary>

/// Sets the report query ranges based on the caller.

/// </summary>

/// <param name="_query">

/// The hold the <c>Query</c> object of the report.

/// </param>

public void setRangesNew(Query _query)

{

    QueryBuildDataSource        qbds;

    VendTrans                   vendTrans;

    FormDataSource              fds;

    Set                         s;

    ;

    qbds = _query.dataSourceTable(tablenum(VendTrans));

    while (qbds.findRange(fieldNum(VendTrans, RecId)))

    {

        qbds.clearRange(fieldNum(VendTrans, RecId));

    }

    if (this.parmArgs())

    {

        vendTrans = this.parmArgs().record();

        if (vendTrans.isFormDataSource() && vendTrans.dataSource())

        {

            s = new Set(Types::Int64);

            fds = vendTrans.dataSource();  // guaranteed to exist

            for (vendTrans = fds.getFirst(fds.anyMarked()); vendTrans; vendTrans = fds.getNext())

            {

                if (!s.in(vendTrans.RecId))  // check if ProdId already exists in set

                {

                    qbds.addRange(fieldNum(vendTrans, RecId)).value(int642str(vendTrans.RecId));

                    s.add(vendTrans.RecId);

                }

            }

        }

        else

        {

            qbds.addRange(fieldNum(VendTrans, RecId)).value(int642str(vendTrans.RecId));

        }

    }

}

.................................................................

public static void main(Args _args)

{

    Alle_PaymentAdviceVendController controller = new Alle_PaymentAdviceVendController();

    controller.parmReportName('Alle_PaymentAdviceVendReport.PaymentAdviceVendReport');

    controller.parmArgs(_args);

    controller.parmShowDialog(false);

    controller.startOperation();

}

....................................................

Delete duplicatw GST rates - Ref pepper

static void DeleteDuplicateGSTRate(Args _args)
{
    TaxRuntimeLookupCondition               taxRuntimeLookupCondition, taxRuntimeLookupConditionGroup, taxRuntimeLookupConditionSelect;
    TaxRuntimeLookupMeasureResult           taxRuntimeLookupMeasureResult;
    TaxRuntimeLookupMeasureResultDetail     taxRuntimeLookupMeasureResultDetail;
    TaxRuntimeLookup                        taxRuntimeLookup;
    TaxRuntimeLookupVersion                 taxRuntimeLookupVersion;
    TaxRuntimeDocComponentMeasure           taxRuntimeDocComponentMeasure;
    TaxRuntimeDocComponentMeasureVersion    taxRuntimeDocComponentMeasureVersion;
    TaxRuntimeDocComponent                  taxRuntimeDocComponent;
    TaxRuntimeDocComponentVersion           taxRuntimeDocComponentVersion;
    TaxRuntimeDocTaxType                    taxRuntimeDocTaxType;
    TaxRuntimeDocTaxTypeVersion             taxRuntimeDocTaxTypeVersion;
    TaxRuntimeTaxMeasureValue               taxRuntimeTaxMeasureValue;
    container                               gstType;
    Query                                   query;
    QueryBuildDataSource                    qbds;
    QueryHavingFilter                       havingFilter;
    QueryRun                                queryRun;
    int                                     counter;
    container                               gstRate;

    query = new Query();
    qbds = query.addDataSource(tableNum(TaxRuntimeLookupCondition));
    qbds.addSelectionField(fieldNum(TaxRuntimeLookupCondition, RecId), SelectionField::Sum);
    qbds.addRange(fieldNum(TaxRuntimeLookupCondition, DimValue1)).value(SysQuery::valueNotEmptyString());
    qbds.addRange(fieldNum(TaxRuntimeLookupCondition, Ledger)).value(SysQuery::value(CompanyInfo::find().RecId));
    //qbds.addRange(fieldNum(TaxRuntimeLookupCondition, DimValue2)).value(SysQuery::value('34013019'));
    qbds.orderMode(OrderMode::GroupBy);
    qbds.addGroupByField(fieldNum(TaxRuntimeLookupCondition, DimValue1));
    qbds.addGroupByField(fieldNum(TaxRuntimeLookupCondition, DimValue2));
    qbds.addGroupByField(fieldNum(TaxRuntimeLookupCondition, DimValue3));
    qbds.addGroupByField(fieldNum(TaxRuntimeLookupCondition, Ledger));
    qbds.addGroupByField(fieldNum(TaxRuntimeLookupCondition, DateRangeFrom1));
    qbds.addGroupByField(fieldNum(TaxRuntimeLookupCondition, DateRangeTo1));

    havingFilter = query.addHavingFilter(qbds, fieldStr(TaxRuntimeLookupCondition, RecId), AggregateFunction::Count);
    havingFilter.value('>3');
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        taxRuntimeLookupConditionGroup = queryRun.getNo(1);
        gstType = conNull();
        gstRate = conNull();
        while select forUpdate taxRuntimeLookupCondition
            where taxRuntimeLookupCondition.DimValue1 == taxRuntimeLookupConditionGroup.DimValue1 &&
            taxRuntimeLookupCondition.DimValue2 == taxRuntimeLookupConditionGroup.DimValue2 &&
            taxRuntimeLookupCondition.DimValue3 == taxRuntimeLookupConditionGroup.DimValue3 &&
            taxRuntimeLookupCondition.Ledger == taxRuntimeLookupConditionGroup.Ledger &&
            taxRuntimeLookupCondition.DateRangeFrom1 == taxRuntimeLookupConditionGroup.DateRangeFrom1 &&
            taxRuntimeLookupCondition.DateRangeTo1 == taxRuntimeLookupConditionGroup.DateRangeTo1
        {
            taxRuntimeLookupVersion = TaxRuntimeLookupVersion::find(taxRuntimeLookupCondition.LookupVersion);
            taxRuntimeLookup = TaxRuntimeLookup::find(taxRuntimeLookupVersion.Lookup);

            select firstOnly taxRuntimeDocComponentMeasure
                where taxRuntimeDocComponentMeasure.RecId == taxruntimelookup.LookupOwnerRecId;

            select firstOnly taxRuntimeDocComponent
                where taxRuntimeDocComponent.Name == 'IGST';

            if (taxRuntimeDocComponentMeasure.TaxRuntimeDocComponent == taxRuntimeDocComponent.RecId)
            {
                select firstOnly taxRuntimeLookupMeasureResult
                        join forUpdate taxRuntimeLookupMeasureResultDetail
                    where taxRuntimeLookupMeasureResult.LookupCondition == taxRuntimeLookupCondition.RecId
                        && taxRuntimeLookupMeasureResultDetail.LookupMeasureResult == taxRuntimeLookupMeasureResult.RecId;

                if (taxRuntimeLookupMeasureResult.RecId)
                {
                    //taxRuntimeTaxMeasureValue = taxRuntimeLookupMeasureResultDetail.Value;
                    if (gstType == conNull())   // for the 1st record
                    {
                        gstType = ['IGST'];
                        gstRate = [taxRuntimeLookupMeasureResultDetail.Value];
                    }
                    else if (conPeek(gstRate, 1) == taxRuntimeLookupMeasureResultDetail.Value)  // for duplicate based on value
                    {
                        ttsBegin;
                        taxRuntimeLookupCondition.delete();
                        ttsCommit;
                        counter++;
                    }
                }
                //if (gstType == 'CGST' || gstType == 'SGST')
                //{
                    //gstCompFoundNo++;
                //}
                //else if (gstType == 'IGST')
                //{
                    //gstCompFoundNo++;
                //}
            }
        }
    }
    info(strFmt('Total => %1', counter));
}

update FA date - ref pepper

update FA date - ref pepper
static void UpdateFADates(Args _args)
{
    AssetBook                   assetBook;
    AssetId                     assetId;
    CommaTextIo                 file;
    int                         faCount;
    LogisticsLocation           logisticsLocation;
    TaxInformation_IN           taxInformation_IN;
    RefRecId                    refrecid;
    CommaIo                     commaIo;
    Filename                    fileName;
    FileIOPermission            permission;
    container                   con;
    int                         i;
    #File

    fileName = WinAPI::getOpenFileName(0, ['CSV File', '*.csv'], '', "Import File");

    if (!WinAPI::fileExists(fileName))
    {
        throw error(strfmt("@SYS18678", fileName));
    }

    commaIo = new CommaIo(fileName, #io_read);

    if (!commaIo)
    {
        throw error(strfmt("@SYS19312", fileName));
    }

    ttsBegin;
    while (commaIo.status() == IO_Status::Ok)
    {
        con = commaIo.read();
        if(con)
        {
            i++;

            assetId = conPeek(con, 1);

            if (assetId)
            {
                try
                {
                    while select forUpdate assetBook
                        where assetBook.AssetId == assetId
                    {
                        assetBook.UsedFromDate = str2Date(conPeek(con, 2), 213);
                        assetBook.LastDepreciationDate = str2Date(conPeek(con, 3), 213);
                        assetBook.LifeTimeRest = conPeek(con, 4);

                        assetBook.update();
                    }
                }
                catch(Exception::Error)
                {
                    info(assetBook.AssetId);
                }
            }
        }
    }
    ttsCommit;
}

Create PO from code - Ref pepper

Create PO from code - Ref pepper

static void CreatePO(ItemId _itemId,
    InventSiteId _inventSiteId,
    InventLocationId _inventLocationId,
    PurchPrice _purchPrice,
    PurchQty _purchQty,
    EcomBusinessModel _ecomBusinessModel,
    VendAccount _vendAccount)
{
    NumberSeq               numberSeq;

    PurchTable              purchTable;
    PurchLine               purchLine;
    boolean                 purchOrderCreated, PurchLineAvailable = false;
    PurchaseType            purchaseType;
    InventDim               inventDim;
    str                     preVendAccount;
    int64                   i;
    PurchFormLetter         purchFormLetter;
    PurchTable_Ecom         purchTableEcom;
    EcommerceParameters     ecomParameter  = EcommerceParameters::find();

    ttsbegin;
    if (preVendAccount !=  _vendAccount)
    {
        purchOrderCreated = false;
        if (!purchOrderCreated)
        {
            purchTable.clear();
            purchTable.initValue();
            purchTable.initFromVendTable(VendTable::find(_vendAccount));

            numberSeq                   = NumberSeq::newGetNum(PurchParameters::numRefPurchId());
            numberSeq.used();
            purchTable.PurchaseType     = PurchaseType::Purch;
            purchTable.PurchId          = numberSeq.num();
            purchTable.CurrencyCode     = VendTable::find(_vendAccount).Currency;
            purchTable.InventSiteId     = _inventSiteId;
            purchTable.InventLocationId = _inventLocationId;
            purchTable.AccountingDate    =   today();
            purchTable.DeliveryDate  =   today();

            if (!purchTable.validateWrite())
                throw Exception::Error;
            else
                purchTable.insert();

            purchTableEcom = purchTable.purchTable_Ecom();
            purchTableEcom.EcomDocumentType = EcomProcurementType::OnDemand;
            purchTable.packPurchTable_Ecom(purchTableEcom);
            purchTable.SysExtensionSerializerMap::postUpdate();

            purchOrderCreated   =   true;
            i = 0;
        }
        i = i + 1;

        purchLine.clear();
        inventDim.clear();

        purchLine.initValue();
        purchLine.ItemId            =   _itemId;
        purchLine.PurchaseType      =   PurchaseType::Purch;
        purchLine.PurchId           =   purchTable.PurchId;
        inventDim.InventSiteId      =   _inventSiteId;
        inventDim.InventLocationId  =   _inventLocationId;
        purchLine.InventDimId       =   InventDim::findOrCreate(inventDim).InventDimId;
        purchLine.PurchPrice        =   _purchPrice;
        purchLine.createLine(true,true,true,true,true,false,true);
        purchLine.PurchQty          =   _purchQty;
        purchLine.QtyOrdered        =   _purchQty;
        purchLine.PurchPrice        =   _purchPrice;
        purchLine.EcomBusinessModel =   _ecomBusinessModel;

        purchLine.LineAmount = purchLine.calcLineAmount();
        purchLine.calcQtyOrdered(purchLine.PurchQty);
        purchLine::modifyPurchQty(purchLine,inventDim,false);
        InventMovement::bufferSetRemainQty(purchLine);
        purchLine.TaxGroup = VendTable::find(_vendAccount).TaxGroup;
        purchLine.Update();

        preVendAccount              =   _vendAccount;

        purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);
        purchFormLetter.update(purchTable,purchTable.PurchId,systemDateGet(),PurchUpdate::All,AccountOrder::None,false,false);
        ttsCommit;
    }
}

change status bar colour

change status bar colour

sysSetupFormRun class - init

SysSecEntryPointManager::FormRunHook(this.form().name(), this.design());

switch(curext())
{
case "USMF":
this.setStatusBarBackgroundColor(254,255,0,0);
break;
    
case "INMF":
this.setStatusBarBackgroundColor(143,200,0,0);
break;    
    }

Create fields dynamically on the form ax 2012

Create fields dynamically on the form ax 2012


public void clicked()
{
    Form                            tableBrowser;
    FormBuildDataSource formBuildDataSource;
    FormBuildGridControl    formBuildGridControl;
    FormBuildDesign                 formBuildDesign;
    DictTable                       dictTable;
    FormRun                         formRun;
    Args                            args;
    Args                            browserArgs;
    dictTable   = new DictTable(tableNum(CustomTable));

    browserArgs     = new Args();
    browserArgs.name(formstr(CustomForm));
    formRun         = classfactory.formRunClass(browserArgs);
    tableBrowser    = formRun.form();

    formBuildDesign = tableBrowser.design();


    super();

    formBuildDataSource = form.dataSource(1);
    formBuildGridControl = formBuildDesign.control('Grid');
    formBuildGridControl.dataSource(dictTable.name());
    formBuildGridControl.allowEdit(true);

    element.addField(tableNum(CustomTable),fieldNum(CustomTable,Occupation),formBuildDataSource,formBuildGridControl,true );

    args = new Args();
    args.object(tableBrowser);
    formRun = classfactory.formRunClass(args);

    formRun.enableCountryRegion(false);
    formRun.init();

    formRun.run();
    formRun.wait();

}



................................................................................................................................................

    /// <summary>
/// Adds the specified field to the grid control.
/// </summary>
/// <param name="tableId">
/// The <c>tableId</c> value that the field is on.
/// </param>
/// <param name="fieldId">
/// The ID of the field.
/// </param>
/// <param name="formBuildDataSource">
/// The <c>FormBuildDataSource</c> on the form.
/// </param>
/// <param name="formBuildGridControl">
/// The grid control on the form.
/// </param>
/// <param name="allowControlEdit">
/// A Boolean value that specifies whether to allow editing of the control.
/// </param>
public void addField(tableId tableId,
    fieldId fieldId,
    FormBuildDataSource formBuildDataSource,
    FormBuildGridControl formBuildGridControl,
    boolean allowControlEdit)
{
    FormBuildStringControl formBuildStringControl;
    Object formBuildControl;
    SysDictField dictField;
    int j;
    tableId rootTableId;

    dictField = new SysDictField(tableId, fieldId);
    if(dictField.isSystem())
    {
        //System fields are only stored on the root table, so get the information from there.
        rootTableId = SysDictTable::getRootTable(tableId);
        dictField = new SysDictField(rootTableId, fieldId);
    }

    if (dictField.saveContents())
    {
        for(j = 1;j <= dictField.arraySize(); j++)
        {
            if (dictField.baseType() == Types::String ||
                dictField.baseType() == Types::RString ||
                dictField.baseType() == Types::VarString)
            {
                formBuildStringControl = formBuildGridControl.addDataField(formBuildDataSource.id(), fieldId, j);
                formBuildStringControl.displayHeightMode(0);
                formBuildStringControl.displayHeightValue(1);
                formBuildStringControl.label(dictField.name());
                formBuildStringControl.helpText(dictField.label());
                formBuildStringControl.allowEdit(allowControlEdit);
            }
            else if(dictField.baseType() == Types::Int64)
            {
                // Reference Controls are created by default for surrogate foreign keys. However, the raw 64-bit
                // integer should always be displayed. Force all Int64 type fields to use an Int64 Control.
                formBuildControl = formBuildGridControl.addControl(FormControlType::Int64, dictField.name());
                formBuildControl.datasource(formBuildDataSource.id());
                formBuildControl.dataField(fieldId2Ext(fieldId,j));
                formBuildControl.label(dictField.name());
                formBuildControl.helpText(dictField.label());
                formBuildControl.allowEdit(allowControlEdit);
            }
            else
            {
                if (dictField.baseType() != Types::Container)
                {
                    formBuildControl = formBuildGridControl.addDataField(formBuildDataSource.id(), fieldId, j);
                    formBuildControl.label(dictField.name());
                    formBuildControl.helpText(dictField.label());
                    formBuildControl.allowEdit(allowControlEdit);
                }
                else
                {
                    formBuildControl = formBuildGridControl.addControl(FormControlType::String,'TableBrowserContainer');
                    formBuildControl.datasource(formBuildDataSource.id());
                    formBuildControl.dataField(fieldId2Ext(fieldId,j));
                    formBuildControl.label(dictField.name());
                    formBuildControl.helpText(dictField.label());
                    formBuildControl.displayLengthMode(0);
                    formBuildControl.displayLengthValue(5);
                    formBuildControl.lookupButton(2);
                }
            }
        }
    }
}