Deposit Billing Operations

Deposit Billing Operations

Intacct bulk data upload process

For Quarterly billing:

  • Save the billing CSV and defer CSV files from the system email that automatically comes from the system in the morning of the first day of each quarter. [Note: Email subject looks like “Quarter Invoicing: YYYYMM” and we only use the invoice-link though we still save the defered-link for historical purposes. We do not defer in Q4 so there will be no defered data in the Q4 invoicing data.]
  • Upload the CSV file to inf3 and put the file into the /home/crossref/intacct/uploads/ directory.
  • ssh to inf3 (see crossref entry in 1Password)
  • Start a screen to run the processes in if it doesn’t already exist. (screen –R intacct)
  • cd to /home/crossref/intacct/
  • Run the PERL script to convert the CSV file (note n is quarter number [1-4]):
> perl quarterlyDepositsToIntacctCsv.pl uploads/Qn_billing.csv uploads/Qn_billing_intacct.csv
  • Run the following command (Note: XREF-COPY is sandbox, XREF is production)[This process will take a while so take a break and come back in an hour or two.]:
> php -q create_sotransaction_bkroundprocess4.php Qn_billing_intacct.csv XREF|XREF-COPY xml_gw_crossref PASSWORD "CrossRef - US" > Qn_results.html

  • Login to www.intacct.com (See 1Password)

  • Select Order Entry->Records->Membership Invoice->List
    Now you are viewing the uploaded data



Background information:

CSV Template:

Notes on the upload template.

The following sample includes the header line of the CSV file, a line showing a sample of the Membership Invoice records used for annual billing, and a line with a sample Sales Invoice from a quarterly billing.

transactiontype,datecreated,createdfrom,customerid,documentno,referenceno,termname,datedue,message,shippingmethod,shipto_contactname,billto_contactname,shipto,billto,currency,basecurrency,exchratedate,exchratetype,exchrate,vsoepricelist,bundlenumber,itemid,itemdesc,warehouseid,quantity,unit,price,discsurchargememo,locationid,departmentid,memo,revrectemplate,revrecstartdate,revrecenddate,renewalmacro,l_projectid,l_customerid,l_vendorid,l_employeeid,l_classid,st_description,st_total,st_percentval,st_locationid Membership Invoice,12/3/2010,,AAFP00,MI-AAFP00-20101203083906953,,,1/31/2011,CrossRef Annual Fee,,,,,,USD,USD,12/3/2010,Intacct Daily Rate,,,,40000,Annual Member Fee,,1,EA,275,,CrossRef - US,,CrossRef Annual Fee,r12 - Jan - Dec,1/1/2011,12/31/2011,c12 - Jan - Dec,,,,,,,,, Sales Invoice,9/30/2010,,PLOS00,SI-PLOS00-201012814759,,,10/31/2010,7/2010 : 10.1371 : CY journal deposits (user: plos),,,,,,USD,USD,12/8/2010,Intacct Daily Rate,,,,40801,7/2010 : 10.1371 : CY journal deposits (user: plos) [price 1.0 per unit],,721,EA,721,,CrossRef - US,,7/2010 : 10.1371 : CY journal deposits (user: plos),r12 - Jan - Dec,1/1/2011,12/31/2011,c12 - Jan - Dec,,,,,,,,,

  • The header line contains the defined fields required for the upload tool. Not all fields are required to be populated as the examples suggest.
  • The first column, transactiontype, must be a type defined in the intacct system. See Order Entry->Order Entry Transaction Definitions.
  • Datecreated = invoicedate, not transaction date. Important for quarterly billing.
  • The createdfrom field is used to convert records. The transactiontype would contain the type being converted too. The createdfrom would contain the transactiontype-documentno of the original - record being converted. All other data fields must match the existing record in the intacct system.
  • Customerid is the equivalent of our old peachtree id. The customerid must be defined in Order Entry->Customers
  • Documentno must be unique.
  • Termname and datedue fields should not both be populated. If you want to assign the payment due date, just use the datedue field. If you want it calculated in intacct, you can use a termname assuming you know a defined termname that applies for this payment. The existing n30 termname generates a due date 30 days after the datecreated. See Accounts Receivable->AR Terms
  • Currency and basecurrency leave as USD.
  • Exchratedate leave same as datecreated. Not sure this field is even needed since no conversion is being done.
  • Exchratetype leave as Intacct Daily Rate. Not sure this is necessary either.
  • Itemid is a code for the purchased item or service. Items are defined in Order Entry->Items
  • Quantity is the count of deposits of itemid type.
  • Price is the quantity multiplied by the price assigned to the itemid definition in Order Entry->Items
  • Locationid should be CrossRef – US. Must be a value that exists in intacct Company->Entities
  • Revrectemplate must match a template defined in Accounts Receivable->Revenue Recognition Templates
  • Revrecstartdate appears to be the start date of the defined template period
  • Revrecenddate appears to be the end date of the defined template period
  • Renewalmacro from macro definitions in Order Entry->Renewal Macros

Deleting data:

To delete data in bulk, you must have a CSV source of the format below.

Header row: transactionid

Sample data row: Membership Invoice-MI-1000010

The transactionid needs to be a combination of the ‘transactiontype-documentno’ of a current valid record in the system. To generate a delete file, Go into intacct into Order Entry->Sales Invoice and Export data as csv.

NOTE: Make certain to include some parameters to insure only desired data is exported. Triple-check data before proceeding.

Then upload the delete csv file to webhost into intacct/uploads and run the following perl script to generate the right delete upload file for intacct:

perl SalesInvoiceToIntacctDeleteCsv.pl uploads/SalesInvoice.csv uploads/SalesDeletes.csv

NOTE: Check export from intacct. Format changed. SalesInvoiceToIntacctDeleteCsv2.pl now current.

To process the delete file, follow these steps:

  • Upload the CSV file via FTP to webhost and put the file into the /home/crossref/intacct/ directory.
  • ssh to inf3 as user crossref, PASSWORD - See 1Password.
  • Start a screen to run the processes in. (i.e. screen –R intacct)
  • cd to /home/crossref/intacct/
  • Run the following command: php -q delete_sotransactions.php uploads/SalesDeletes.csv XREF xml_gw_crossref PASSWORD > deletes.log This process will take a while so take a break and come back in a while.
  • Login to www.intacct.com (See 1Password)
  • Select Order Entry->Records->Membership Invoice->List

You should be able to see that the records are gone.

DEPRECATED

For Annual billing: (NOT CURRENTLY IN USE)

A few minutes after clicking Submit, you will receive an email with the results attached.

  • Save the CSV file that arrives in your email from the invoiceMaker. Note: the format of the CSV should resemble the sample shown below in the CSV templates section.
  • Upload the CSV file via FTP to webhost and put the file into the /home/crossref/www/intacct/uploads/ directory. The php script assumes this path so it must be here.
  • ssh to webhost
  • Start a screen to run the process in. (i.e. screen –R intacct)
  • cd to /home/crossref/www/intacct . The php script must be run from here.

Run the following command:

  • php -q create_sotransaction_bkroundprocess2.php invoiceFile.csv XREF xml_gw_crossref PASSWORD “CrossRef - US” > results.html

This process will take a while so take a break and come back in an hour.

  • Login to www.intacct.com (company XREF-copy, user xml_gw_crossref, PASSWORD - See 1Password)
  • Select Order Entry->Records->Membership Invoice->List

Now you are viewing the uploaded data

Edit Edit this page