/// <summary>/// This class contains core logic to create pending vendor invoices, depending upon the results of pre-requisite validations being done./// Added by Nishath Salman > SCM-06 IN - PO invoice interface./// </summary>class CNLHEROPOInvoiceInterfaceIN{ // Table declarations static VendInvoiceInfoTable vendInvoiceInfoTable; static VendInvoiceInfoSubTable vendInvoiceInfoSubTable; static VendInvoiceInfoLine vendInvoiceInfoLine; static VendPackingSlipTrans vendPackingSlipTrans; static VendInvoiceInfoSubLine vendInvoiceInfoSubLine; static CNLInterfaceLog poInvoiceInterfaceLog; static PurchLine purchLine; static PurchTable purchTable; static VendTable vendTable; static CNLPurchaseInvoice cnlPurchaseInvoiceLine; static CnlUnitPriceDiscrepancy cnlUnitPrcDiscrepancy; static CNLFAORLIItemTax cnlFAOrliItemTax; static DMFExecutionId jobExecId; // Variables Desclaration static Notes errorMessage; static TaxAmountCur taxAmountCur; static boolean isPOInvoiceCanBeProcessed,isListInitialized; static int lastAttemptCnt; static boolean isProductReceiptRequired; // Query(s) declaration & initialization static Query qry = new Query(); static QueryRun qryRun; static QueryBuildDataSource qbds; static QueryBuildRange qbr,qbrRecordType,qbrParentGUID; // Record insert list declaration & initialization static RecordInsertList pendingVendInvList = new RecordInsertList(tableNum(VendInvoiceInfoLine),false); static RecordInsertList pendingVendInfoSubLineList = new RecordInsertList(tableNum(VendInvoiceInfoSubLine)); static RecordInsertList poInvoiceInterfaceLogList = new RecordInsertList(tableNum(CNLInterfaceLog)); static RecordInsertList poInvoiceInterfaceLogMainList = new RecordInsertList(tableNum(CNLInterfaceLog)); static RecordInsertList poUnitPricDiscrepancyList = new RecordInsertList(tableNum(CnlUnitPriceDiscrepancy)); // Query(s) declaration & initialization static Query query = new Query(queryStr(CNLPurchaseInvoiceQry)); static QueryRun queryrun; static QueryBuildDataSource qbdsList; static QueryBuildRange qbrList; // Variable declarations static VendInvoiceAccount vendAcc; static anytype preReqValidationType,isPendingVendInvProcessed; static boolean typeBool; static InvoiceId invoiceNumber; static InvoiceDate invoiceDate,postingDate,invoiceReceivedDate; // Record insert list declaration & initialization static RecordInsertList insertList,typeList; /// <summary> /// Create an instance of <class>CNLHEROPOInvoiceInterfaceIN</class> /// </summary> /// <returns>An initialized instance of class <CNLHEROPOInvoiceInterfaceIN> </returns> public static CNLHEROPOInvoiceInterfaceIN construct() { CNLHEROPOInvoiceInterfaceIN poInvInterface = new CNLHEROPOInvoiceInterfaceIN(); return poInvInterface; } /// <summary> /// Creating pending venor header / lines /// </summary> /// <param name = /_contract/></param> public void createPendingInvoice(CNLHEROPOInvContract _contract) { // List declaration List sourceTypeList; // Tables declaration CNLPurchaseInvoice cnlPurchInvoice,cnlPurchInvStatusUpdate,cnlPurchInvData; // top picking the locked records is disabled for now //cnlPurchInvoicePessimisticLock.readPast(true); qbdsList = query.dataSourceTable(tableNum(CNLPurchaseInvoice)); qbrList = qbdsList.addRange(fieldNum(CNLPurchaseInvoice,Source)); str rangeValue; // List iteration for the multi lookup filter sourceTypeList = _contract.parmSourceType(); if (sourceTypeList && !sourceTypeList.empty()) { ListEnumerator listEnumerator; listEnumerator = sourceTypeList.getEnumerator(); while (listEnumerator.moveNext()) { rangeValue = queryRangeConcat(rangeValue, listEnumerator.current()); } } if(rangeValue) { qbrList.value(rangeValue); } queryrun = new QueryRun(query); while (queryrun.next()) { cnlPurchInvoice = queryrun.get(tableNum(CNLPurchaseInvoice)); // Get whole data from CNLPurchaseInvoice to overcome group by restrictions select cnlPurchInvData where cnlPurchInvData.InvoiceNumber == cnlPurchInvoice.InvoiceNumber && cnlPurchInvData.RecordType == CNLHeroPurchaseInvoiceRecordType::Header; isListInitialized = true; // Get the last process attempts count lastAttemptCnt += CNLPurchaseInvoice::getLastCount(cnlPurchInvData); // Initialize Job Execution ID jobExecId = cnlPurchInvData.JobExecutionId; // Ensure If the current Purchase Invoice is not ended in error in an earlier Batch execution, If yes then the process // attempts counts are less than or equal to the count specified in the Chanel interface parameters setup if (cnlPurchInvData.Attempts <= CNLInterfaceParameter::find().Attempts) { if (CNLHEROPOInvoiceInterfacePreReqValidation::construct().validateWorkingTableRec(cnlPurchInvData)) { // Initialize default fields CNLHEROPOInvoiceInterfaceIN::construct().initializeKeyFields(cnlPurchInvData); // Create pending vendor invoice Header / Lines CNLHEROPOInvoiceInterfaceIN::construct().createPendingVendHeaderLines(cnlPurchInvData); // Update processed update_recordset cnlPurchInvStatusUpdate setting ProcessingStatus = CNLHeroProcessingStatus::Processed where cnlPurchInvStatusUpdate.InvoiceNumber == cnlPurchInvData.InvoiceNumber; } } } // Check if the log list is not empty, if yes commit the exception logs to the database if (isListInitialized) { poInvoiceInterfaceLogList.insertDatabase(); } //Ensure to mark all the orphan header records as error for the current Job Execution ID if there are any if (jobExecId) { CNLHEROPOInvoiceInterfacePreReqValidation::construct().updateOrphanHeader(jobExecId); } } /// <summary> /// To initialize all the key fields required for pending vendor invoice creation /// </summary> public void initializeKeyFields(CNLPurchaseInvoice _purchInvoiceVendAcc) { // Vendor account identification PurchTable purchInvoiceVendAcc; select firstonly InvoiceAccount from purchInvoiceVendAcc where purchInvoiceVendAcc.VendorRef == _purchInvoiceVendAcc.VendorReference; vendAcc = purchInvoiceVendAcc.InvoiceAccount; // Invoice number invoiceNumber = _purchInvoiceVendAcc.InvoiceNumber; // Invoice date invoiceDate = _purchInvoiceVendAcc.InvoiceDate; // Posting date postingDate = _purchInvoiceVendAcc.InvoiceDate; // Invoice received date invoiceReceivedDate = DateTimeUtil::date(DateTimeUtil::utcNow()); } /// <summary> /// Creates and manages both Pending vendor invoice Headers / Lines /// </summary> /// <param name = /_cnlPurchPendVendInv/></param> public void createPendingVendHeaderLines(CNLPurchaseInvoice _cnlPurchPendVendInv) { // Purchase order select firstonly purchTable where purchTable.PurchId == _cnlPurchPendVendInv.PurchaseOrderNumber; // Vendor vendTable = VendTable::find(vendAcc,false); ttsbegin; try { // Insert pending vendor invoice header CNLHEROPOInvoiceInterfaceIN::construct().createPendingVendInvHeader(purchTable); //TODO::Need to cater below dates as those would have calculated fields //Due date //Purchase order //Product receipt // Loop purch line to create pending vendor invoice lines CNLHEROPOInvoiceInterfaceIN::construct().createPendingVendInvLines(_cnlPurchPendVendInv); } catch (Exception::Error) { // Update the process attempt count CNLPurchaseInvoice::updateAttempts(_cnlPurchPendVendInv,lastAttemptCnt); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchPendVendInv,CNLHEROLogExceptions::getErrorMessage())); ttsabort; } catch (Exception::Info) { // Update the process attempt count CNLPurchaseInvoice::updateAttempts(_cnlPurchPendVendInv,lastAttemptCnt); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchPendVendInv,CNLHEROLogExceptions::getErrorMessage())); ttsabort; } catch (Exception::CLRError) { // Update the process attempt count CNLPurchaseInvoice::updateAttempts(_cnlPurchPendVendInv,lastAttemptCnt); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchPendVendInv,CNLHEROLogExceptions::getErrorMessage())); ttsabort; } ttscommit; } /// <summary> /// To allocate ORLI charges based Purchase Interface ORLI charges parameters /// </summary> /// <param name = /_vendInvInfoTable/></param> public void allocateORLIcharges(Common _vendInvInfoTable) { MarkupAllocation markupAllocation; markupAllocation = MarkupAllocation::newMarkupAllocation(_vendInvInfoTable); CNLInterfaceParameter interfaceParameter = CNLInterfaceParameter::find(); markupAllocation.parmAllocateAfter(interfaceParameter.ChargesAllocation); markupAllocation.parmAllocateOn(interfaceParameter.AllocateChargesToLines); markupAllocation.parmAllocateAll(interfaceParameter.AllocateAll); markupAllocation.runOperation(); } /// <summary> /// To create records in <table>VendInvoiceInfoTable</table> /// </summary> /// <param name = /_purchTable/></param> public void createPendingVendInvHeader(PurchTable _purchTable) { vendInvoiceInfoTable.clear(); vendInvoiceInfoTable.initValue(); vendInvoiceInfoTable.initFromPurchTable(_purchTable); vendInvoiceInfoTable.Num = invoiceNumber; vendInvoiceInfoTable.DocumentDate = invoiceDate; vendInvoiceInfoTable.TransDate = invoiceDate; vendInvoiceInfoTable.ReceivedDate = invoiceReceivedDate; vendInvoiceInfoTable.VendorVATDate = invoiceDate; vendInvoiceInfoTable.DocumentOrigin = DocumentOrigin::Manual; } /// <summary> /// To create records in <table>VendInvoiceInfoLines</table> /// </summary> /// <param name = /_cnlPurchInvoice/></param> public void createPendingVendInvLines(CNLPurchaseInvoice _cnlPurchInvoice) { qbds = qry.addDataSource(tableNum(CNLPurchaseInvoice)); qbr = qbds.addRange(fieldNum(CNLPurchaseInvoice,InvoiceNumber)); qbrRecordType = qbds.addRange(fieldNum(CNLPurchaseInvoice,RecordType)); qbrParentGUID = qbds.addRange(fieldNum(CNLPurchaseInvoice,ParentGuild)); qbr.value(queryValue(_cnlPurchInvoice.InvoiceNumber)); qbrRecordType.value(queryValue(CNLHeroPurchaseInvoiceRecordType::Line)); qbrParentGUID.value(queryValue(_cnlPurchInvoice.GUIDValue)); qryRun = new QueryRun(qry); while (qryRun.next()) { cnlPurchaseInvoiceLine = qryRun.get(tableNum(CNLPurchaseInvoice)); //TODO::Need to check with functional on purchline selection if two lines with same Item Id but with different varint IDs select firstonly purchLine where purchLine.PurchId == purchTable.PurchId && purchLine.ItemId == cnlPurchaseInvoiceLine.ItemNumber; // Validate Release Products if (!EcoResProduct::find(InventTable::find(purchLine.ItemId,false).Product,false).RecId) { errorMessage = strFmt('@CNLHeroModelLabel:SCM06_000050',purchLine.ItemId,_cnlPurchInvoice.InvoiceNumber); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchInvoice,errorMessage)); ttsabort; } // Update imported sals tax amount if PO invoice lines total calculated tax amount is > 0 if (Tax::calcTaxAmount(purchLine.TaxGroup, purchLine.TaxItemGroup, Systemdateget(), purchLine.CurrencyCode, purchLine.LineAmount, TaxModuleType::Purch) > 0) { taxAmountCur += cnlPurchaseInvoiceLine.ImportedTaxAmount; } //// Initialize Vend packing slip trans //// Line level validations //select firstonly vendPackingSlipTrans // where vendPackingSlipTrans.PackingSlipId == cnlPurchaseInvoiceLine.ProductReceiptNumber // && vendPackingSlipTrans.OrigPurchid == purchTable.PurchId // && vendPackingSlipTrans.ItemId == cnlPurchaseInvoiceLine.ItemNumber // && vendPackingSlipTrans.LineNum == cnlPurchaseInvoiceLine.ExternalLineNumber; //if (!vendPackingSlipTrans) // If no matching records found in VendPackingSLipTrans //{ // errorMessage = strFmt('@CNLHeroModelLabel:SCM06_000043',purchTable.PurchId); // // Exception caught and the same has been logged into the exception monitoring log table. // poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchInvoice,errorMessage)); // ttsabort; //} if (cnlPurchaseInvoiceLine.InvoicedQty > purchLine.PurchQty) // If InvoiceQty > PurchLine's Purch Qty { errorMessage += strFmt('@CNLHeroModelLabel:SCM06_000045',purchTable.purchId); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchInvoice,errorMessage)); ttsabort; } else // The current record from working table <CNLPurchaseInvoice> is fully qualified for further processing { vendInvoiceInfoLine.initValue(); vendInvoiceInfoLine.initFromPurchLine(purchLine); vendInvoiceInfoLine.OrigPurchId = purchLine.PurchId; vendInvoiceInfoLine.ItemId = purchLine.ItemId; vendInvoiceInfoLine.currencyCode = purchLine.CurrencyCode; vendInvoiceInfoLine.InventNow = purchLine.PurchQty; vendInvoiceInfoLine.PurchUnit = purchLine.PurchUnit; vendInvoiceInfoLine.LineDisc = purchLine.LineDisc; vendInvoiceInfoLine.LinePercent = purchLine.LinePercent; vendInvoiceInfoLine.LineAmount = purchLine.LineAmount; vendInvoiceInfoLine.LineNum = cnlPurchaseInvoiceLine.ExternalLineNumber; vendInvoiceInfoLine.CNLMadeIn = cnlPurchaseInvoiceLine.MadeIn; vendInvoiceInfoLine.DocumentOrigin = DocumentOrigin::Manual; // ORLI fiscal representative cnlFAOrliItemTax = CNLFAORLIItemTax::find(cnlPurchaseInvoiceLine.ORLIFiscalRepresentative); if (cnlFAOrliItemTax.RecId) { vendInvoiceInfoLine.OrderAccount = cnlFAOrliItemTax.VendAccount; vendInvoiceInfoLine.TaxGroup = cnlFAOrliItemTax.SalesTaxGroup; vendInvoiceInfoLine.TaxItemGroup = cnlFAOrliItemTax.ItemTaxGroup; } else { vendInvoiceInfoLine.OrderAccount = vendInvoiceInfoTable.OrderAccount; vendInvoiceInfoLine.TaxGroup = purchLine.TaxGroup; vendInvoiceInfoLine.TaxItemGroup = purchLine.TaxItemGroup; } pendingVendInvList.add(vendInvoiceInfoLine); // Insert vendor invoice info sub line CNLHEROPOInvoiceInterfaceIN::construct().createVendInvoiceInfoSubLine(vendInvoiceInfoLine,vendPackingSlipTrans); // Update unit price discrepancy if (cnlPurchaseInvoiceLine.PriceUnit != purchLine.PurchPrice && CNLInterfaceParameter::find().UpdateUnitPrice == NoYes::Yes) { PurchLine::updatePurchUnitPrcDicrepancy(purchLine.RecId,cnlPurchaseInvoiceLine.PriceUnit); // Insert unit price discrepancy into table <CNLUnitPriceDiscrepancy> for auditing CNLHEROPOInvoiceInterfaceIN::construct().createUnitPriceDiscrepancy(cnlPurchaseInvoiceLine,purchLine); } // Allocate ORLI charges if (cnlPurchaseInvoiceLine.InvoiceCharges > 0 && cnlPurchaseInvoiceLine.Source == CNLHEROSourceSystem::ORLI) { CNLHEROPOInvoiceInterfaceIN::construct().allocateORLIcharges(vendInvoiceInfoTable); } } } if (!errorMessage) { // Update Imported sales tax amount vendInvoiceInfoTable.ImportedSalesTax = taxAmountCur; vendInvoiceInfoTable.insert(); // Insert vendor invoice info sub table vendInvoiceInfoSubTable::createFromVendInvoiceInfoTable(vendInvoiceInfoTable,false); pendingVendInvList.insertDatabase(); pendingVendInfoSubLineList.insertDatabase(); poUnitPricDiscrepancyList.insertDatabase(); // Submit to Workflow CNLHEROPOInvoiceInterfaceIN::construct().submitWorkFlow(vendInvoiceInfoTable,_cnlPurchInvoice); } } /// <summary> /// To create records in <table>VendInvoiceInfoSubLine</table> /// </summary> /// <param name = /_vendInvoiceInfoLine/></param> /// <param name = /_vendPackingSlipTrans/></param> public void createVendInvoiceInfoSubLine(VendInvoiceInfoLine _vendInvoiceInfoLine, VendPackingSlipTrans _vendPackingSlipTrans) { vendInvoiceInfoSubLine.clear(); vendInvoiceInfoSubLine.initValue(); vendInvoiceInfoSubLine.defaultRow(); vendInvoiceInfoSubLine.ParmId = _vendInvoiceInfoLine.ParmId; vendInvoiceInfoSubLine.LineRefRecId = _vendInvoiceInfoLine.RecId; vendInvoiceInfoSubLine.ReceiveNow = _vendPackingSlipTrans.Qty; vendInvoiceInfoSubLine.InventNow = _vendPackingSlipTrans.Qty; vendInvoiceInfoSubLine.JournalRefRecId = _vendPackingSlipTrans.RecId; vendInvoiceInfoSubLine.JournalRefTableId = _vendPackingSlipTrans.TableId; vendInvoiceInfoSubLine.DocumentId = _vendPackingSlipTrans.PackingSlipId; pendingVendInfoSubLineList.add(vendInvoiceInfoSubLine); } /// <summary> /// To create records in <table>CNLUnitPriceDiscrepancy</table> /// </summary> /// <param name = /_cnlPurchInv/></param> /// <param name = /_purchLine/></param> public void createUnitPriceDiscrepancy(CNLPurchaseInvoice _cnlPurchInv, PurchLine _purchLine) { cnlUnitPrcDiscrepancy.Source = _cnlPurchInv.Source; cnlUnitPrcDiscrepancy.GuidId = _cnlPurchInv.GuidId; cnlUnitPrcDiscrepancy.InvoiceNumber = _cnlPurchInv.InvoiceNumber; cnlUnitPrcDiscrepancy.PriceUnit = _cnlPurchInv.PriceUnit; cnlUnitPrcDiscrepancy.PurchaseOrderNumber = _cnlPurchInv.PurchaseOrderNumber; cnlUnitPrcDiscrepancy.POLineNumber = _purchLine.LineNumber; cnlUnitPrcDiscrepancy.InitialPOUnitPrice = _purchLine.PriceUnit; cnlUnitPrcDiscrepancy.UpdatedPOUnitPrice = _cnlPurchInv.PriceUnit; cnlUnitPrcDiscrepancy.UpdateStatus = CNLInterfaceParameter::find().UpdateUnitPrice; if (PurchTable::find(_cnlPurchInv.PurchaseOrderNumber,false).InterCompanySalesId) { cnlUnitPrcDiscrepancy.RelatedSalesOrderNumber = PurchTable::find(_cnlPurchInv.PurchaseOrderNumber,false).InterCompanySalesId; } poUnitPricDiscrepancyList.add(cnlUnitPrcDiscrepancy); } public void submitWorkFlow(VendInvoiceInfoTable _vendInvoiceInfoTable,CNLPurchaseInvoice _cnlPurchInvoiceWF) { WorkflowComment workflowComment; TradeLineRefId headerReference; VendInvoiceInfoTable vendInvoiceInfoTbl; headerReference = _vendInvoiceInfoTable.TableRefId; workflowComment = /@CNLHeroModelLabel:SCM06_000055/; vendInvoiceInfoTbl = VendInvoiceInfoTable::findTableRefId(headerReference); if (vendInvoiceInfoTbl) { try { VendInvoiceHeaderWorkflow::submitToWorkflow(vendInvoiceInfoTbl, workflowComment); } catch (Exception::CLRError) { // Update the process attempt count CNLPurchaseInvoice::updateAttempts(_cnlPurchInvoiceWF,lastAttemptCnt); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchInvoiceWF,CNLHEROLogExceptions::getErrorMessage())); ttsabort; } catch (Exception::Error) { // Update the process attempt count CNLPurchaseInvoice::updateAttempts(_cnlPurchInvoiceWF,lastAttemptCnt); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchInvoiceWF,CNLHEROLogExceptions::getErrorMessage())); ttsabort; } catch (Exception::Info) { // Update the process attempt count CNLPurchaseInvoice::updateAttempts(_cnlPurchInvoiceWF,lastAttemptCnt); // Exception caught and the same has been logged into the exception monitoring log table. poInvoiceInterfaceLogList.add(CNLHEROPOInvoiceInterfaceLog::createPurchaseInvoiceLog(_cnlPurchInvoiceWF,CNLHEROLogExceptions::getErrorMessage())); ttsabort; } } }}
{
// Update Imported sales tax amount
vendInvoiceInfoTable.ImportedSalesTax = taxAmountCur;
vendInvoiceInfoTable.insert();
vendInvoiceInfoSubTable::createFromVendInvoiceInfoTable(vendInvoiceInfoTable,false);
pendingVendInfoSubLineList.insertDatabase();
poUnitPricDiscrepancyList.insertDatabase();
CNLHEROPOInvoiceInterfaceIN::construct().submitWorkFlow(vendInvoiceInfoTable,_cnlPurchInvoice);
}