Axapta, d365 finance and operations : August 2019

Tuesday, 20 August 2019

Create and post Invoice proposal through code in ax 2012 X++

Create and post Invoice proposal through code in ax 2012 X++

public class CreateInvoiceProposal extends RunBaseBatch
{

}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public boolean canGoBatch()
{
return true;
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

protected boolean canGoBatchJournal()
{
return true;
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public boolean runsImpersonated()
{
return true;
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

static ClassDescription description()
{
return "Invocie proposal creation";
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public static void main(Args args)
{
CreateInvoiceProposal createInvoiceProposal = new CreateInvoiceProposal();

if (createInvoiceProposal.prompt())
{
createInvoiceProposal.run();
}
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public void run()

{

ProjParameters projParameters;

ProjEmplTrans projEmplTrans;

ProjOnAccTrans projOnAccTrans;

ProjProposalJour projProposalJour; //Table which will store journal records after creating Invoice Proposal

ProjFormLetter projFormLetter;

ProjOnAccTrans objProjOnAccTrans_1;

ALE_STG_Milestone milestone,milestoneLines;

ALE_STG_HourJournal hourJournal,hourJournalLines;

ALE_STG_ExpenseJournal expenseJournal,expenseJournalLines;

TransactionID transId;

ALE_ProposalInvoiceTable proposalInvoiceTable;

CategoryId categoryId;

ProjTable projTable;

container transIdCon;

int i,j;

ProjInvoiceProposalInsertLines projInvoiceProposalInsertLines;

ProjInvoiceProposalCreateLinesBase projInvoiceProposalCreateLinesBase;

ProjInvoiceProposalCreateLines proposalCreateLines;

ProjInvoiceProposalCreateLinesParams projInvoiceProposalCreateLinesParams;

proposalCreateLines = new ProjInvoiceProposalCreateLines();

projInvoiceProposalCreateLinesParams = new ProjInvoiceProposalCreateLinesParams();

proposalCreateLines.parmProposalCreateLinesParams(projInvoiceProposalCreateLinesParams).parmInvoiceDate(today());

projInvoiceProposalInsertLines = new ProjInvoiceProposalInsertLines(proposalCreateLines,false);

select projParameters;

try

{

while select hourJournal group by InvoiceId,CompanyId order by ProjectDate asc //ProjectDate,ProjId

join expenseJournal

where hourJournal.Status == 2 && hourJournal.InterCompany == NoYes::No && hourJournal.InvoiceId != '' && hourJournal.AXInvoiceId == '' && expenseJournal.PSAInvoiceId == hourJournal.InvoiceId

{

ttsBegin;

changeCompany(hourJournal.CompanyId)

// Start added by kiran PSA AX Integration 13 Aug 2019

delete_from proposalInvoiceTable;

// End added by kiran PSA AX Integration 13 Aug 2019

proposalInvoiceTable.PSAInvoiceId = hourJournal.InvoiceId;

proposalInvoiceTable.insert();

projInvoiceProposalInsertLines.run();

select proposalInvoiceTable;

select forUpdate projProposalJour where projProposalJour.ProposalId == proposalInvoiceTable.ProposalId &&

projProposalJour.InvoiceDate == today()

&& (projProposalJour.LineProperty!=ProjLinePropertyCode::Invoiced && projProposalJour.LineProperty!=ProjLinePropertyCode::Canceled);

{

//For approving Invoice proposals

projProposalJour.LineProperty = ProjLinePropertyCode::Approved;

projProposalJour.update();

//For Posting Invoice Proposals

projFormLetter = ProjFormLetter::construct(DocumentStatus::ProjectInvoice);

projFormLetter.createParmLine(projProposalJour);

projFormLetter.run();

//To get latest record

projProposalJour = projProposalJour::find(projProposalJour.ProposalId);

info(strFmt("Invoice ProposalId: %1 \t InvoiceId: %2",projProposalJour.ProposalId,projProposalJour.ProjInvoiceId));

}

select proposalInvoiceTable;

while select forUpdate hourJournalLines order by RecId asc where hourJournalLines.InvoiceId == hourJournal.InvoiceId

&& hourJournalLines.Status == 2

&& hourJournalLines.InterCompany == NoYes::No

{

hourJournalLines.AXInvoiceId = proposalInvoiceTable.InvoiceId;

hourJournalLines.Status = 3;

hourJournalLines.update();

}

while select forUpdate expenseJournalLines order by RecId asc where expenseJournalLines.PSAInvoiceId == hourJournal.InvoiceId

&& expenseJournalLines.Status == 2

&& expenseJournalLines.InterCompany == NoYes::No

{

expenseJournalLines.AXInvoiceId = proposalInvoiceTable.InvoiceId;

expenseJournalLines.Status = 3;

expenseJournalLines.update();

}

ttsCommit;

}

// Onaccount - expense

while select milestone group by InvoiceId,CompanyId order by ProjDate asc //ProjectDate,ProjId

join expenseJournal

where milestone.Status == 2 && milestone.InterCompany == NoYes::No && milestone.InvoiceId != '' && milestone.AXInvoiceId == '' && expenseJournal.PSAInvoiceId == milestone.InvoiceId

{

ttsBegin;

changeCompany(milestone.CompanyId)

// Start added by kiran PSA AX Integration 13 Aug 2019

delete_from proposalInvoiceTable;

// End added by kiran PSA AX Integration 13 Aug 2019

proposalInvoiceTable.PSAInvoiceId = milestone.InvoiceId;

proposalInvoiceTable.insert();

projInvoiceProposalInsertLines.run();

select proposalInvoiceTable;

select forUpdate projProposalJour where projProposalJour.ProposalId == proposalInvoiceTable.ProposalId &&

projProposalJour.InvoiceDate == today()

&& (projProposalJour.LineProperty!=ProjLinePropertyCode::Invoiced && projProposalJour.LineProperty!=ProjLinePropertyCode::Canceled);

{

//For approving Invoice proposals

projProposalJour.LineProperty = ProjLinePropertyCode::Approved;

projProposalJour.update();

//For Posting Invoice Proposals

projFormLetter = ProjFormLetter::construct(DocumentStatus::ProjectInvoice);

projFormLetter.createParmLine(projProposalJour);

projFormLetter.run();

//To get latest record

projProposalJour = projProposalJour::find(projProposalJour.ProposalId);

info(strFmt("Invoice ProposalId: %1 \t InvoiceId: %2",projProposalJour.ProposalId,projProposalJour.ProjInvoiceId));

}

select proposalInvoiceTable;

while select forUpdate milestoneLines order by RecId asc where milestoneLines.InvoiceId == milestone.InvoiceId

&& milestoneLines.Status == 2

&& milestoneLines.InterCompany == NoYes::No

{

milestoneLines.AXInvoiceId = proposalInvoiceTable.InvoiceId;

milestoneLines.Status = 3;

milestoneLines.update();

}

while select forUpdate expenseJournalLines order by RecId asc where expenseJournalLines.PSAInvoiceId == milestone.InvoiceId

&& expenseJournalLines.Status == 2

&& expenseJournalLines.InterCompany == NoYes::No

{

expenseJournalLines.AXInvoiceId = proposalInvoiceTable.InvoiceId;

expenseJournalLines.Status = 3;

expenseJournalLines.update();

}

ttsCommit;

}

// On Account - expense end

// On account alone - start

while select milestone group by InvoiceId,CompanyId order by ProjDate asc //ProjectDate,ProjId

where milestone.Status == 2 && milestone.InterCompany == NoYes::No && milestone.InvoiceId != '' && milestone.AXInvoiceId == ''

{

ttsBegin;

changeCompany(milestone.CompanyId)

// Start added by kiran PSA AX Integration 13 Aug 2019

delete_from proposalInvoiceTable;

// End added by kiran PSA AX Integration 13 Aug 2019

proposalInvoiceTable.PSAInvoiceId = milestone.InvoiceId;

proposalInvoiceTable.insert();

projInvoiceProposalInsertLines.run();

select proposalInvoiceTable;

select forUpdate projProposalJour where projProposalJour.ProposalId == proposalInvoiceTable.ProposalId &&

projProposalJour.InvoiceDate == today()

&& (projProposalJour.LineProperty!=ProjLinePropertyCode::Invoiced && projProposalJour.LineProperty!=ProjLinePropertyCode::Canceled);

{

//For approving Invoice proposals

projProposalJour.LineProperty = ProjLinePropertyCode::Approved;

projProposalJour.update();

//For Posting Invoice Proposals

projFormLetter = ProjFormLetter::construct(DocumentStatus::ProjectInvoice);

projFormLetter.createParmLine(projProposalJour);

projFormLetter.run();

//To get latest record

projProposalJour = projProposalJour::find(projProposalJour.ProposalId);

info(strFmt("Invoice ProposalId: %1 \t InvoiceId: %2",projProposalJour.ProposalId,projProposalJour.ProjInvoiceId));

}

select proposalInvoiceTable;

while select forUpdate milestoneLines order by RecId asc where milestoneLines.InvoiceId == milestone.InvoiceId

&& milestoneLines.Status == 2

&& milestoneLines.InterCompany == NoYes::No

{

milestoneLines.AXInvoiceId = proposalInvoiceTable.InvoiceId;

milestoneLines.Status = 3;

milestoneLines.update();

}

ttsCommit;

}

// On account alone - end

// Expense/Cost Invoice proposal

while select expenseJournal group by PSAInvoiceId,CompanyId order by ProjectDate asc //ProjectDate,ProjId

where expenseJournal.Status == 2 && expenseJournal.InterCompany == NoYes::No && expenseJournal.PSAInvoiceId != '' && expenseJournal.AXInvoiceId == ''

{

ttsBegin;

changeCompany(expenseJournal.CompanyId)

// Start added by kiran PSA AX Integration 13 Aug 2019

delete_from proposalInvoiceTable;

// End added by kiran PSA AX Integration 13 Aug 2019

proposalInvoiceTable.PSAInvoiceId = expenseJournal.PSAInvoiceId;

proposalInvoiceTable.insert();

projInvoiceProposalInsertLines.run();

select proposalInvoiceTable;

select forUpdate projProposalJour where projProposalJour.ProposalId == proposalInvoiceTable.ProposalId &&

projProposalJour.InvoiceDate == today()

&& (projProposalJour.LineProperty!=ProjLinePropertyCode::Invoiced && projProposalJour.LineProperty!=ProjLinePropertyCode::Canceled);

{

//For approving Invoice proposals

projProposalJour.LineProperty = ProjLinePropertyCode::Approved;

projProposalJour.update();

//For Posting Invoice Proposals

projFormLetter = ProjFormLetter::construct(DocumentStatus::ProjectInvoice);

projFormLetter.createParmLine(projProposalJour);

projFormLetter.run();

//To get latest record

projProposalJour = projProposalJour::find(projProposalJour.ProposalId);

info(strFmt("Invoice ProposalId: %1 \t InvoiceId: %2",projProposalJour.ProposalId,projProposalJour.ProjInvoiceId));

}

select proposalInvoiceTable;

while select forUpdate expenseJournalLines order by RecId asc where expenseJournalLines.PSAInvoiceId == expenseJournal.PSAInvoiceId

&& expenseJournalLines.Status == 2

&& expenseJournalLines.InterCompany == NoYes::No

{

expenseJournalLines.AXInvoiceId = proposalInvoiceTable.InvoiceId;

expenseJournalLines.Status = 3;

expenseJournalLines.update();

}

ttsCommit;

}

while select hourJournal group by InvoiceId,CompanyId order by ProjectDate asc //ProjectDate,ProjId

where hourJournal.Status == 2 && hourJournal.InterCompany == NoYes::No && hourJournal.InvoiceId != '' && hourJournal.AXInvoiceId == ''

{

ttsBegin;

changeCompany(hourJournal.CompanyId)

// Start added by kiran PSA AX Integration 13 Aug 2019

delete_from proposalInvoiceTable;

// End added by kiran PSA AX Integration 13 Aug 2019

proposalInvoiceTable.PSAInvoiceId = hourJournal.InvoiceId;

proposalInvoiceTable.insert();

projInvoiceProposalInsertLines.run();

select proposalInvoiceTable;

select forUpdate projProposalJour where projProposalJour.ProposalId == proposalInvoiceTable.ProposalId &&

projProposalJour.InvoiceDate == today()

&& (projProposalJour.LineProperty!=ProjLinePropertyCode::Invoiced && projProposalJour.LineProperty!=ProjLinePropertyCode::Canceled);

{

//For approving Invoice proposals

projProposalJour.LineProperty = ProjLinePropertyCode::Approved;

projProposalJour.update();

//For Posting Invoice Proposals

projFormLetter = ProjFormLetter::construct(DocumentStatus::ProjectInvoice);

projFormLetter.createParmLine(projProposalJour);

projFormLetter.run();

//To get latest record

projProposalJour = projProposalJour::find(projProposalJour.ProposalId);

info(strFmt("Invoice ProposalId: %1 \t InvoiceId: %2",projProposalJour.ProposalId,projProposalJour.ProjInvoiceId));

}

select proposalInvoiceTable;

while select forUpdate hourJournalLines order by RecId asc where hourJournalLines.InvoiceId == hourJournal.InvoiceId

&& hourJournalLines.Status == 2

&& hourJournalLines.InterCompany == NoYes::No

{

hourJournalLines.AXInvoiceId = proposalInvoiceTable.InvoiceId;

hourJournalLines.Status = 3;

hourJournalLines.update();

}

ttsCommit;

}

catch(Exception::Error)

{

error('An Exception has occurred');

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Get data from db in ax 2012 X++

Get data from db in ax 2012 X++

class PSA_InvoiceProposalInboundProcess extends RunBaseBatch
{
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdSelect,cmdUpdate;
System.Data.OleDb.OleDbDataReader reader,readerUpdate;
container resultCon;
str connectStr;
ProjParameters projParameters;
Map dimAttributeMap;
ProjId projID;
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public boolean canGoBatch()
{
return true;

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

protected boolean canGoBatchJournal()
{
return true;

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public void createMilestone(container _c)
{
ALE_STG_Milestone milestone;
ProjLineProperty projLineProperty;
ProjId projIDLoc,psaProjId;
TransDate ProjDateLoc;
DimensionValue WorkerLoc;
Qty HoursLoc;
AmountMST SalesPriceLoc,CostPriceLoc;
str LinePropertyLoc;
CurrencyCode CurrencyCodeLoc;
Description GuidLoc,ContractingUnitLoc,resourcingUnitLoc;
str CompanyIdLoc,msg;
String30 invoiceStatus,psaInvoiceId;

;
GuidLoc = '';
CompanyIdLoc = '';
/*ContractingUnitLoc = '';
resourcingUnitLoc = '';*/

GuidLoc = conpeek(_c, 1);
CompanyIdLoc = conpeek(_c, 2);
invoiceStatus = conpeek(_c, 3);
psaInvoiceId = conpeek(_c, 4);
/*resourcingUnitLoc = conpeek(_c, 11);
ContractingUnitLoc = conpeek(_c, 12);*/

select forupdate milestone where milestone.GUID == GuidLoc;
milestone.BillingStatus = invoiceStatus;
milestone.InvoiceId = psaInvoiceId;
milestone.Status = 2 ;
milestone.update();

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public Str1260 formatString(Str1260 _msg)
{
str replace(str _source, str _what, str _with)
{
int found = 0;
str target = _source;
do
{
found = strscan(target, _what, found, strlen(target));
if (found != 0)
{
target = strdel(target, found, strlen(_what));
target = strins(target, _with, found);
found += strlen(_with);
}
} while (found != 0);
return target;
}
;
_msg = replace(_msg, '\n', '');
_msg = replace(_msg, '\r', '');
_msg = replace(_msg, '\t', '');
_msg = replace(_msg, '\'', '');
return _msg;
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public container pack()
{
return conNull();
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public container processMilestone(System.Data.OleDb.OleDbDataReader readerProject)
{
int startLine = infologline(), currentLine, axStatus;
int i;
Name name,psaContractingUnit,psaResourcingUnit;
ProjId projIDLoc,psaProjId;
TransDate psaProjDate;
DimensionValue psaWorker;
Qty psaHours;
AmountMST psaSalesPrice,psaCostPrice;
str psaLineProperty;
String30 psaInvoiceStatus,psaInvoiceId;
Description psaGuid;
str psaCompanyId,msg;

psaGuid = '';
psaCompanyId = '';
psaInvoiceStatus = '';
psaInvoiceId = '';
/*psaResourcingUnit = '';
psaContractingUnit = '';*/

try
{

if (!readerProject.IsDBNull(0))
psaInvoiceStatus = readerProject.GetString(0);
else
psaInvoiceStatus = "";

if (!readerProject.IsDBNull(1))
psaGuid = readerProject.GetString(1);
else
psaGuid = "";

if (!readerProject.IsDBNull(2))
psaCompanyId = readerProject.GetString(2);
else
psaCompanyId = "";
if (!readerProject.IsDBNull(3))
psaInvoiceId = readerProject.GetString(3);
else
psaInvoiceId = "";

ttsbegin;

// Invoice Status
if (readerProject.IsDBNull(0))
{
msg = 'InvoiceStatus cannot be null';
ttsAbort;
axStatus = -1;
return [axStatus, msg];
}

this.createMilestone([psaGuid,psaCompanyId,psaInvoiceStatus,psaInvoiceId]);

axStatus = 2;
msg = strFmt('Milestone successfully ');
ttscommit;
}
catch
{
ttsAbort;
axStatus = -1;
for (currentLine = startLine + 1; currentLine <= infologline(); currentLine++)
{
msg += infolog.text(currentLine);
}
msg = this.formatString(msg);

infolog.clear();
}
return [axStatus, msg];
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public void run()

{

str datestr;

ALE_STG_HourJournal hourJournalUpdate;

ALE_STG_ExpenseJournal expenseJournal;

ALE_STG_Milestone milestone;

Ale_ProcessingStatus status;

DataAreaId psaCompanyId;

Description pGuid;

string255 err;

projParameters = ProjParameters::find();

connectStr = 'Provider=SQLNCLI11;Integrated Security=SSPI;' +'Persist Security Info=False;Initial Catalog=' +

projParameters.ALE_PSA_StagingDBName + ';Data Source=' + projParameters.ALE_PSA_StagingDBServer + ';'+ 'User id=axadmin;Password=gavs_2012';

try

{

objConn = new System.Data.OleDb.OleDbConnection(connectStr);

objConn.Open();

cmdSelect = objConn.CreateCommand();

cmdSelect.set_CommandText('SELECT HJ.BillingStatus,HJ.GUID,HJ.CompanyId,HJ.PSAInvoiceId '+

'FROM HourJournal AS HJ '+ 'WHERE HJ.STATUS = 2');

reader = cmdSelect.ExecuteReader();

while (reader.Read())

{

psaCompanyId = reader.GetString(2);

if (psaCompanyId == '' || !CompanyInfo::findDataArea(psaCompanyId))

{

resultCon = [-1,"Company code error"];

pGuid = reader.GetString(1);

cmdUpdate = objConn.CreateCommand();

}

else

{

changeCompany(psaCompanyId)

resultCon = this.processHourJournalTable(reader);

pGuid = reader.GetString(1);

cmdUpdate = objConn.CreateCommand();

}

cmdUpdate = objConn.CreateCommand();

cmdUpdate.set_CommandText(strFmt('UPDATE HourJournal SET STATUS = %1,' + ' ERRORMESSAGE = ' + "'%2'" + 'WHERE GUID = ' + "'%3'",

conPeek(resultCon, 1), conPeek(resultCon, 2), pGuid));

readerUpdate = cmdUpdate.ExecuteReader();

if (conPeek(resultCon, 1) == 1)

status = Ale_ProcessingStatus::Processed;

else if (conPeek(resultCon, 1) == -1)

status = Ale_ProcessingStatus::Error;

else

status = Ale_ProcessingStatus::Unprocessed;

err = conPeek(resultCon, 2);

//Update AX staging hourJournal-start

update_recordSet hourJournalUpdate

setting processingstatus = status,ErrorMsg = err

where hourJournalUpdate.GUID == pGuid

&& hourJournalUpdate.ProcessingStatus == Ale_ProcessingStatus::Unprocessed;

//Update AX staging hourJournal-end

}

// update expense journal for Invoice proposal

cmdSelect = objConn.CreateCommand();

cmdSelect.set_CommandText('SELECT EJ.BillingStatus,EJ.GUID,EJ.CompanyId,EJ.PSAInvoiceId '+

'FROM ExpenseJournal AS EJ '+ 'WHERE EJ.STATUS = 2');

reader = cmdSelect.ExecuteReader();

while (reader.Read())

{

psaCompanyId = reader.GetString(2);

if (psaCompanyId == '' || !CompanyInfo::findDataArea(psaCompanyId))

{

resultCon = [-1,"Company code error"];

pGuid = reader.GetString(1);

cmdUpdate = objConn.CreateCommand();

}

else

{

changeCompany(psaCompanyId)

resultCon = this.processExpenseJournalTable(reader);

pGuid = reader.GetString(1);

cmdUpdate = objConn.CreateCommand();

}

cmdUpdate = objConn.CreateCommand();

cmdUpdate.set_CommandText(strFmt('UPDATE ExpenseJournal SET STATUS = %1,' + ' ERRORMESSAGE = ' + "'%2'" + 'WHERE GUID = ' + "'%3'",

conPeek(resultCon, 1), conPeek(resultCon, 2), pGuid));

readerUpdate = cmdUpdate.ExecuteReader();

if (conPeek(resultCon, 1) == 1)

status = Ale_ProcessingStatus::Processed;

else if (conPeek(resultCon, 1) == -1)

status = Ale_ProcessingStatus::Error;

else

status = Ale_ProcessingStatus::Unprocessed;

err = conPeek(resultCon, 2);

//Update AX staging hourJournal-start

update_recordSet expenseJournal

setting processingstatus = status,ErrorMsg = err

where expenseJournal.GUID == pGuid

&& expenseJournal.ProcessingStatus == Ale_ProcessingStatus::Unprocessed;

//Update AX staging hourJournal-end

}

// on Account

cmdSelect = objConn.CreateCommand();

cmdSelect.set_CommandText('SELECT MS.BillingStatus,MS.GUID,MS.CompanyId,MS.PSAInvoiceId '+

'FROM Milestone AS MS '+ 'WHERE MS.STATUS = 2');

reader = cmdSelect.ExecuteReader();

while (reader.Read())

{

psaCompanyId = reader.GetString(2);

if (psaCompanyId == '' || !CompanyInfo::findDataArea(psaCompanyId))

{

resultCon = [-1,"Company code error"];

pGuid = reader.GetString(1);

cmdUpdate = objConn.CreateCommand();

}

else

{

changeCompany(psaCompanyId)

resultCon = this.processMilestone(reader);

pGuid = reader.GetString(1);

cmdUpdate = objConn.CreateCommand();

}

cmdUpdate = objConn.CreateCommand();

cmdUpdate.set_CommandText(strFmt('UPDATE Milestone SET STATUS = %1,' + ' ERRORMESSAGE = ' + "'%2'" + 'WHERE GUID = ' + "'%3'",

conPeek(resultCon, 1), conPeek(resultCon, 2), pGuid));

readerUpdate = cmdUpdate.ExecuteReader();

if (conPeek(resultCon, 1) == 1)

status = Ale_ProcessingStatus::Processed;

else if (conPeek(resultCon, 1) == -1)

status = Ale_ProcessingStatus::Error;

else

status = Ale_ProcessingStatus::Unprocessed;

err = conPeek(resultCon, 2);

//Update AX staging hourJournal-start

update_recordSet milestone

setting processingstatus = status,ErrorMsg = err

where milestone.GUID == pGuid

&& milestone.ProcessingStatus == Ale_ProcessingStatus::Unprocessed;

//Update AX staging hourJournal-end

}

catch(Exception::Error)

{

error('An Exception has occurred');

}

if(objConn)

{

objConn.Close();

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public boolean runsImpersonated()

{

return true;

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public boolean unpack(container _packedClass)

{

return true;

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

static ClassDescription description()

{

return " Project invoice proposal inbound process";

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

public static void main(Args args)

{

ALE_PSA_InvoiceProposalInboundProcess invoiceProposalInboundProcess = new ALE_PSA_InvoiceProposalInboundProcess();;

if (invoiceProposalInboundProcess.prompt())

{

invoiceProposalInboundProcess.run();

}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Thursday, 15 August 2019

AX Integration, updating table in SQL

AX Integration, updating table in SQL

//This class created by Kiran on 20-Jun-19 for PSA integration
class ALE_ReturnInvoiceIdToDB extends RunBaseBatch
{
System.Data.OleDb.OleDbConnection objConn;
System.Data.OleDb.OleDbCommand cmdInsert,cmdUpdate,cmdSelect;
System.Data.OleDb.OleDbDataReader reader,readerUpdate;
container resultCon;
str connectStr;
ProjParameters projParameters;

}

>>.....................>>>>>>>>>>>>>>>>>>

public boolean canGoBatch()
{
return true;
}

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

protected boolean canGoBatchJournal()
{
return true;
}
.......................................................

public container pack()
{
return conNull();
}

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

public boolean runsImpersonated()
{
return true;
}

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

public boolean unpack(container _packedClass)
{
return true;
}

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

static ClassDescription description()
{
return "Return AX Invoice Id to DB";
}

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

public static void main(Args args)
{
ALE_ReturnInvoiceIdToDB returnInvoiceIdToDB = new ALE_ReturnInvoiceIdToDB();

if (returnInvoiceIdToDB.prompt())
{
returnInvoiceIdToDB.run();
}
}

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

public void run()
{
str datestr;
String255 err;

projParameters = ProjParameters::find();

connectStr = 'Provider=SQLNCLI11;Integrated Security=SSPI;' +'Persist Security Info=False;Initial Catalog=' +
projParameters.ALE_PSA_StagingDBName + ';Data Source=' + projParameters.ALE_PSA_StagingDBServer + ';'+ 'User id= ;Password=';
try
{
this.hourJournal();
this.expenseJournal();
this.milestone();
}
catch(Exception::Error)
{
error('An Exception has occurred');
}

if(objConn)
{
objConn.Close();
}
}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

private void hourJournal()
{
QueryBuildDataSource qbds;
Query q;
QueryRun qr;
QueryBuildRange qbr;
ALE_STG_HourJournal ale_STG_HourJournal,ale_STG_HourJournalUpdate;

ttsBegin;
objConn = new System.Data.OleDb.OleDbConnection(connectStr);
objConn.Open();

q = new query();
qbds = q.addDataSource(tableNum(ALE_STG_HourJournal));
qbds.addRange(fieldNum(ALE_STG_HourJournal,Status)).value('3');
qr = new QueryRun(q);

while (qr.next())
{
ale_STG_HourJournal = qr.get(tableNum(ALE_STG_HourJournal));
if(ale_STG_HourJournal.AXInvoiceId)
{
cmdUpdate = objConn.CreateCommand();

cmdUpdate.set_CommandText(strFmt('UPDATE HourJournal SET AXInvoiceId =' + "'%1'" + ' where GUID = ' + "'%2'",ale_STG_HourJournal.AXInvoiceId,ale_STG_HourJournal.GUID));

cmdUpdate.ExecuteNonQuery();

//update_recordSet ale_STG_HourJournalUpdate
//setting ProcessingStatus = Ale_ProcessingStatus::Processed
//where ale_STG_HourJournalUpdate.RecId == ale_STG_HourJournal.RecId;
}

}
ttsCommit;
}

Thursday, 1 August 2019

dynamic parameter and single parameter in ssrs Report ax 2012

dynamic parameter and single parameter in ssrs Report ax 2012

[DataContractAttribute,SysOperationContractProcessingAttribute(classStr(ALE_PositionVacantUIBuilder))]

class ALE_PositionVacantContract
{
Ale_Period period;
ProjId projId;
}

[DataMemberAttribute('period')]
public Ale_Period parmPeriod(Ale_Period _period = period)
{
period = _period;
return period;
}

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

class ALE_PositionVacantController extends SrsReportRunController
{
#define.ReportName('ALE_PositionVacantReports.Report')
}

public static void main(Args _args)
{
ALE_PositionVacantController controller = new ALE_PositionVacantController();
ALE_PositionVacantContract contract;
contract = new ALE_PositionVacantContract();

controller.parmReportName(#ReportName);
controller.parmShowDialog(true);
controller.startOperation();
}

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

class ALE_PositionVacantUIBuilder extends SrsReportDataContractUIBuilder
{
ALE_PositionVacantContract positionVacantContract;
DialogField peroidDialogField;
}

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

public void build()
{
positionVacantContract = this.dataContractObject();

peroidDialogField = this.addDialogField(methodstr(ALE_PositionVacantContract,parmPeriod), positionVacantContract);

}

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

public void getFromDialog()
{
positionVacantContract = this.dataContractObject();
super();
}

..........

private void peroidLookup(FormStringControl _formStringControl)
{
Query query = new Query();
QueryBuildDataSource qbdsProjTable;
SysTableLookup sysTableLookup;

// Create an instance of SysTableLookup with the current calling form control.
sysTableLookup = SysTableLookup::newParameters(tableNum(Ale_PeriodMaster), _formStringControl);

// Add fields to be shown in the lookup form.
sysTableLookup.addLookupfield(fieldNum(Ale_PeriodMaster, Name), true);

qbdsProjTable = query.addDataSource(tableNum(Ale_PeriodMaster));

sysTableLookup.parmQuery(query);

// Perform the lookup
sysTableLookup.performFormLookup();

}

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

public void postBuild()
{
super();
peroidDialogField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(ALE_PositionVacantContract,parmPeriod));
peroidDialogField.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(ALE_PositionVacantUIBuilder, peroidLookup), this);
peroidDialogField.lookupButton(2);

}

.......

public void postRun()
{
//super();
}

.......

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

[
SRSReportQueryAttribute(querystr(ALE_PositionVacantProjTable)),
SRSReportParameterAttribute(classstr(ALE_PositionVacantContract))
]
class ALE_PositionVacantDP extends SrsReportDataProviderPreProcessTempDB//SRSReportDataProviderBase //SrsReportDataProviderPreProcess //
{
int serialNo;
Ale_Period period;
ALE_PositionVacantTmp positionVacantTmp;
}

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

[SRSReportDataSetAttribute('ALE_PositionVacantTmp')]

public ALE_PositionVacantTmp getpositionVacantTmp()

{

select * from positionVacantTmp;

return positionVacantTmp;

}

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

[SysEntryPointAttribute(true)]

public void processReport()

{

ProjTable projTable;

ALE_PositionVacantContract contract = this.parmDataContract() as ALE_PositionVacantContract;

Query q = this.parmQuery();

QueryRun qr = new QueryRun(q);

period = contract.parmPeriod();

while (qr.next())

{

projTable = qr.get(tableNum(ProjTable));

}