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
