Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Hi Joshi,
can you elaborate the Error?
Regards
Sathish
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
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 ...?
Hi Joshi,
oracle not able to convert string to int. better to use resident and convert to int.
Regards
Sathish