Dynamics Community Forum Thread Details

Integration rewriting my query and adding UCASE

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???

Quick Links

Leaderboard