Qlik sense Unable to read Oracle Script

joshibabu_are — Tue, 24 Jan 2017 07:46:35 GMT

Hi Guru's,

Iam compiling oracle EBS Script in Qlik sense, its unable to read Composite key script in SQL.

How to get it work done,any help would be greately appreciated.

SELECT L.LEDGER_ID "LEDGER_KEY",

TO_NUMBER(TO_CHAR(L.EFFECTIVE_DATE,'YYYYMMDD')) "JOURNAL_DT_KEY",

TO_NUMBER(TO_CHAR(H.POSTED_DATE,'YYYYMMDD')) "POSTED_DT_KEY",

L.STATUS "JOURNAL_LINE_STATUS",

L.CODE_COMBINATION_ID "GL_ACCOUNT_KEY",

B.NAME "JE_BATCH_NAME",

L.JE_HEADER_ID "JE_HEADER_ID",

L.JE_LINE_NUM "JE_LINE_NUM",

L.JE_HEADER_ID

||'~'

||L.JE_LINE_NUM "JOURNAL_ID",

H.NAME "JE_HEADER_DESCRIPTION",

L.DESCRIPTION "JE_LINE_DESCRIPTION",

L.CODE_COMBINATION_ID,

H.JE_SOURCE "JE_SOURCE",

H.JE_CATEGORY "JE_CATEGORY_NAME",

L.REFERENCE_6 "JE_SOURCE_REFERENCE",

H.CURRENCY_CODE "DOC_CURR_CODE",

GL.CURRENCY_CODE "LOC_CURR_CODE" ,

XDL.UNROUNDED_ENTERED_DR "ENTERED_DR",

XDL.UNROUNDED_ENTERED_CR "ENTERED_CR",

XDL.UNROUNDED_ACCOUNTED_DR "ACCOUNTED_DR",

XDL.UNROUNDED_ACCOUNTED_CR "ACCOUNTED_CR",

B.STATUS "BATCH_STATUS",

L.CREATION_DATE "CREATION_DATE",

L.LAST_UPDATE_DATE "LAST_UPDATE_DATE",

L.JE_HEADER_ID

||'~'

||L.JE_LINE_NUM

||'~'

||L.CODE_COMBINATION_ID

||'~'

||XDL.EVENT_ID

||'~'

||XDL.AE_HEADER_ID

||'~'

||XDL.AE_LINE_NUM

||'~'

||xdl.source_distribution_id_num_1 "INTEGRATION_ID",

--ai.invoice_num ap_invoice_id ,

ai.invoice_num "invoice_num" ,

ai.invoice_date "invoice_date",

ail.DESCRIPTION "invoice_line_desc",

V.VENDOR_NAME "SUP_CUST_NAME",

PA.PROJECT_ID,

PA.NAME "PROJECT_NAME",

PA.SEGMENT1 "PROJECT_NUMBER",

PA.PROJECT_TYPE,

PA.DESCRIPTION "PROJECT_DESCRIPTION",

PA.START_DATE "PROJECT_START_DATE",

PA.COMPLETION_DATE "PROJECT_END_DATE",

FU.USER_NAME "CREATED_BY"

FROM GL_JE_LINES L,

GL_JE_HEADERS H,

GL_JE_BATCHES B,

GL_LEDGERS GL ,

GL_IMPORT_REFERENCES gir,

XLA.XLA_AE_LINES xlal ,

XLA.XLA_AE_HEADERS xlah ,

XLA.XLA_EVENTS xlae ,

XLA.XLA_TRANSACTION_ENTITIES xlate ,

xla.xla_distribution_links xdl,

ap_invoice_distributions_all aid,

AP_INVOICES_ALL ai,

PO_VENDORS V,

ap_invoice_lines_all ail,

PA_PROJECTS_ALL PA,

FND_USER FU

WHERE 1 =1

AND L.JE_HEADER_ID = H.JE_HEADER_ID

AND H.JE_BATCH_ID = B.JE_BATCH_ID

AND GL.LEDGER_ID = H.LEDGER_ID

--AND B.STATUS = 'P'

AND L.LEDGER_ID IN (874,919,916,875,876,125,22)

AND b.je_batch_id = gir.je_batch_id

AND l.je_header_id = gir.je_header_id

AND l.je_line_num = gir.je_line_num

AND gir.gl_sl_link_table = xlal.gl_sl_link_table

AND gir.gl_sl_link_id = xlal.gl_sl_link_id

AND xlal.ae_header_id = xlah.ae_header_id

AND xlal.application_id = xlah.application_id

AND xlah.event_id = xlae.event_id

AND xlah.application_id = xlae.application_id

AND xlae.entity_id = xlate.entity_id

AND xlae.application_id = xlate.application_id

AND h.je_source = 'Payables'

AND xlate.source_id_int_1 = ai.invoice_id

AND xlate.security_id_int_1 = ai.org_id

AND xlate.entity_code = 'AP_INVOICES'

AND V.VENDOR_ID = xlal.party_id

AND xdl.ae_header_id = xlah.ae_header_id

AND xdl.ae_line_num = xlal.ae_line_num

AND (xlae.EVENT_ID =xdl.EVENT_ID

AND xlae.APPLICATION_ID =xdl.APPLICATION_ID)

AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id

AND aid.invoice_id = ai.invoice_id

AND ail.invoice_id = ai.invoice_id

AND ail.line_number = aid.invoice_line_number

AND AID.PROJECT_ID = PA.PROJECT_ID(+)

AND L."CREATED_BY" = FU.USER_ID

AND SUBSTR(h.POSTED_DATE,8,9) BETWEEN 05 AND 08

AND SUBSTR(l.effective_date,8,9) BETWEEN 05 AND 08

Thanks,

joshi

Re: Qlik sense Unable to read Oracle Script

petter — Tue, 24 Jan 2017 16:47:58 GMT

What do you mean by "unable to read" - do you get a specific error message and could you share it with us?

Do you get this problem in the Data Manager og Data Load Editor?

Re: Qlik sense Unable to read Oracle Script

satheshreddy — Tue, 24 Jan 2017 16:54:41 GMT

Hi Joshi,

can you elaborate the Error?

Regards

Sathish

Re: Qlik sense Unable to read Oracle Script

joshibabu_are — Wed, 25 Jan 2017 07:23:56 GMT

Iam getting below error in data load Editor

QVX_UNEXPECTED_END_OF_DATA: ERROR [22018] [Qlik][ODBC Oracle Wire Protocol driver][Oracle]ORA-01722: invalid number.

Sql Developer Its running without error But in Qlik sense its throwing error.

May be its unable to read below code

L.JE_HEADER_ID

||'~'

||L.JE_LINE_NUM

||'~'

||L.CODE_COMBINATION_ID

||'~'

||XDL.EVENT_ID

||'~'

||XDL.AE_HEADER_ID

||'~'

||XDL.AE_LINE_NUM

||'~'

Thanks,

Joshi

Re: Qlik sense Unable to read Oracle Script

petter — Wed, 25 Jan 2017 11:30:34 GMT

Maybe you have to do an explicit CAST of the numeric column before trying to concatenate it.

I am not sure if Oracle will do an implicit type conversion from numeric to char ...?

Re: Qlik sense Unable to read Oracle Script

satheshreddy — Wed, 25 Jan 2017 16:00:28 GMT

Hi Joshi,

oracle not able to convert string to int. better to use resident and convert to int.

Regards

Sathish

topic Re: Qlik sense Unable to read Oracle Script in QlikView

Qlik sense Unable to read Oracle Script

Re: Qlik sense Unable to read Oracle Script

Re: Qlik sense Unable to read Oracle Script

Re: Qlik sense Unable to read Oracle Script

Re: Qlik sense Unable to read Oracle Script

Re: Qlik sense Unable to read Oracle Script