Recently, I had to integrate around 1000+ Sales Quotations which comprised of 10000+ Sales Item Lines. This had to be done in a seemingly impossible timeframe, as we were totally running out of schedule.

I devised a strategy to do an All-SQL work as follows:

1. First Export required SOP tables from source database and Import it as SOP TEMP tables to destination database. The Source & Destination databases denote GP Company Databases, of course.

2. Generate the SOP Number by taking the Number Sequence from destination GP Company SOP Quotation Setup.

3. Update all SOP TEMP tables with relevant new SOP Number.

4. Identity only those fields which are required and then do a direct INSERT INTO. This insertion, in my case, happened for SOP10100 (Header), SOP10200 (Detail) & SOP10106 (User-Defined).

5. Once the above is done, run a Reconcilation on all these inserted documents, to make sure that these documents got accepted by destination GP Company as valid SOP Quotations.

All above tasks took me 5 hours of time (including 2.5 Hours of Data Updates & Reconciliation).

Excellent, and I was so happy that I could meet the deadline. I intimated the users to verify and send me the feedback. Users reported back with positive feedback and I was relieved.

ISSUE FACED: Quotations were not getting converted to Orders. Transaction Edit List shown nothing. All looked perfect.

TROUBLESHOOTING RESULTS: 30mins of troubleshooting yielded me one very silly reason behind this issue. The field in SOP10100, USDOCID1, was an Empty String for all Quotations integrated thru’ SQL. This field is the one which tells GP to which Order ID it is suppose to convert this quotation to.

SOLUTION: You all may know by now what the solution would be. Another SQL UPDATE which took the “Transfer To Order ID” value from SOP Quotation Setup and updated SOP10100 accordingly, did the trick.

WARNINGS:

1. Though I had mentioned that I followed ALL-SQL approach, it’s not supported by Microsoft.

2. I have not shared the SQL Scripts here, as it completely depends on the particular scenario and the way SOP Quotations are used by clients.

3. This post is intended to explain two things:
3.1. There are several successful & time saving approaches that we can follow, even if it’s unconventional.
3.2. With each unconventional approach, there lies a risk which cannot be foreseen. Unless, you are dead sure about your GP Technical & Database Skills, you are NOT suppose to tread any such approach.

4. I just wanted to highlight how one single field (which in our perception may look simple) may cause a critical consequence.

VAIDY