Re-Allocate Purchase Invoice Lines     

{This is a Related Menu script allows you to call up an existing Purchase Invoice and amend all line Allocations.  This will remove any existing Allocations form the current Invoice Lines and replace it with your new selection.   At the same time it will go to the source of that allocation and amend the source.}

var

TheJobNumber,JobTaskCount,TheJobTask: string;

TheProductCode, TheCodeType, TheProductUnit: string;

TheProductDescription, TheCatalogueName: string;

TheAllocID,TheSourceID: double;

TheInvoiceBatchNo,InvoiceCount: string;

procedure UpdatePurchaseInvoiceLines;

var

TempQuery: TpFIBQuery;

TempTrans: TpFIBTransaction;

begin

TempTrans := TpFIBTransaction.Create(nil);

TempQuery := TpFIBQuery.Create(nil);

try

begin

  TempTrans.DefaultDatabase := OstendoDB;

  TempQuery.Database := OstendoDB;

  TempQuery.Transaction := TempTrans;

  TempQuery.Options := qoStartTransaction;

  TempQuery.SQL.Clear;

  TempQuery.SQL.ADD('select PurchaseInvoiceAllocations.sysuniqueid, PurchaseInvoiceLines.codetype, PurchaseInvoiceLines.linecode, PurchaseInvoiceLines.lineunit, ');

  TempQuery.SQL.ADD('linedescription, linkedcataloguename ');

  TempQuery.SQL.add(' from PurchaseInvoiceAllocations, PurchaseInvoiceLines ');

  TempQuery.SQL.add(' where PurchaseInvoiceAllocations.headersysuniqueid = PurchaseInvoiceLines.sysuniqueid ');

  TempQuery.SQL.add(' and PurchaseInvoiceLines.invoicebatchno = ' + TheInvoiceBatchNo);

  TempQuery.ExecQuery;

  While not(TempQuery.eof) do

  begin

    TheProductCode := TempQuery.FN('linecode').asstring;

    TheCodeType := TempQuery.FN('codetype').asstring;

    TheProductUnit := TempQuery.FN('lineunit').asstring;

    TheProductDescription := TempQuery.FN('linedescription').asstring;

    TheCatalogueName := TempQuery.FN('linkedcataloguename').asstring;

    TheAllocID := TempQuery.FN('sysuniqueid').value;

    TheSourceID := AllocateToJobOrder(TheJobNumber, TheJobTask, TheProductCode, TheCodeType, TheProductUnit, TheProductDescription, TheCatalogueName, TheAllocID);

    executeSQL('update PurchaseInvoiceAllocations set AllocationType = ''Job Order'', AllocationReference = ''' + TheJobNumber + ''',sourcesysuniqueid = ' + floattostr(TheSourceID) + ' where sysuniqueid = ' + floattostr(TheAllocID));

   TempQuery.next;

  end;

end;

finally

   TempTrans.Free;

   TempQuery.Close;

   TempQuery.Free;

end;

end;

begin

TheInvoiceBatchNo := GetSourceFieldValue('INVOICEBATCHNO','HEADER');

InvoiceCount := GetSQLResult('select count(sysuniqueid) from PurchaseInvoices where INVOICEBATCHNO = ' + TheInvoiceBatchNo + ' and PurchaseInvStatus = ''InProgress''');

if (InvoiceCount = '1') then

begin

{Change Allocation}

  TheJobNumber := AskMandatoryQuestionWithLookup('Job Number', 'Please select the Job Number', 1075);

  JobTaskCount := GetSQLResult('select count(sysuniqueid) from JobTasks where OrderNumber = ''' + TheJobNumber + '''');

  if JobTaskCount <> '1' then

   begin

    TheJobTask := AskQuestionWithUserDefinedLookup('select Taskname as "Task" from Jobtasks where ordernumber = ''' + TheJobNumber + '''', 'Job Task','Please select the Job Task','', 'Job Tasks for ' + TheJobNumber, 'task');

   end

  else

   begin

    TheJobTask := GetSQLResult('select Taskname from Jobtasks where ordernumber = ''' + TheJobNumber + '''');

   end;

  {Procedure to update the Allocations}

  UpdatePurchaseInvoiceLines;

  ExecuteSQL('update PurchaseInvoices set DEFAULTALLOCATIONTYPE = ''Job Order'', DEFAULTALLOCATIONREF = ''' + TheJobNumber + ''' where INVOICEBATCHNO = ' + TheInvoiceBatchNo);

  RelatedScreenRefreshData('HEADER');

  RelatedScreenRefreshData('LINE');

end;

end.