Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Integration rewriting my query and adding UCASE

Posted on by 5

1. What I enter...

 
SELECT b.je_batch_id, b.transaction_date, b.je_batch_type, -1 * d.amount AS AMOUNT,
d.note, d.city_no, d.je_acct_number
FROM je_batch b, je_detail d WHERE b.je_batch_id = d.je_batch_id AND
b.JE_BATCH_TYPE) = 'ESF_ESCHEAT' AND b.je_import_status = 'NOT_TRAN' 
ORDER BY b.je_batch_type,b.je_batch_id, b.transaction_date
 
2. Script that grabs that and submits...
 
SELECT b.je_batch_id, b.transaction_date, b.je_batch_type, -1 * d.amount AS AMOUNT,
d.note, d.city_no, d.je_acct_number
FROM je_batch b, je_detail d WHERE b.je_batch_id = d.je_batch_id AND
upper(b.JE_BATCH_TYPE) = 'ESF_ESCHEAT' AND upper(b.je_import_status) = 'NOT_TRAN' 
ORDER BY b.je_batch_type,b.je_batch_id, b.transaction_date
 
 
 
go = 1
 
If GetVariable("g_doc_warn") = 1 then
 MsgBox("Doc warnings occured.")
 go = 0
End If
 
If GetVariable("g_doc_error") = 1 then
 MsgBox("Doc errors occured.")
 go = 0
End If
 
If GetVariable("g_integration_error") = 1 then
 MsgBox("Integration errors occured.")
 go = 0
End If
 
If go = 1 then
   sSQL = "Update JE_BATCH set IMPORT_JOB_ID = '" & GetVariable("g_batch_id") & _
         "', JE_IMPORT_STATUS = 'COMPLETE'" & _
   ", LAST_MODIFIED_USER = 'GP Integration'" & _
         ", IMPORT_DATE = TO_DATE('" & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & "','MM/DD/YYYY')" & _
         " WHERE JE_BATCH_ID IN (" & GetVariable("g_BuildSQLFilter") & ") AND " & _
         "JE_BATCH_TYPE = '" & GetVariable("g_batch_type") & "'"
  Query.ExecuteSQL(sSQL)
 
  sSQL = "Update JE_DETAIL Set IMPORT_FLAG = 'Y'" & _
         " WHERE JE_BATCH_ID IN (" & GetVariable("g_BuildSQLFilter") & ")"
  Query.ExecuteSQL(sSQL)
Else
  MsgBox("Tables not updated.") 
End If
 
ClearVariables
 
3. What Oracle sees...
 
SELECT b.je_batch_id, b.transaction_date, b.je_batch_type, -1 * d.amount AS AMOUNT,  d.note, d.city_no, d.je_acct_number FROM je_batch b, je_detail d WHERE b.je_batch_id = d.je_batch_id AND  UCase(b.JE_BATCH_TYPE) = UCase('ESF_ESCHEAT') AND UCase(b.je_import_status) = UCase('NOT_TRAN') AND JE_BATCH_ID = 99999 AND UCase(TRANSACTION_DATE) = UCase('2009-02-01 00:00:00') ORDER BY b.je_batch_type,b.je_batch_id, b.transaction_date
 
 How is this query getting modified???

*This post is locked for comments

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans