GL_Interface containing duplicate records which has already been posted in GL transferred from Payables (Query) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/glinterface-containing-duplicate.html

GL_Interface containing duplicate records which has already been posted in GL transferred from Payables (Query)

The below Query will return Data after giving Invoice_id. GL_Interface Table contains the Sub ledger Document Sequence Values through which we can get the Invoice Id's .

SELECT   distinct gll.*                  
FROM     GL_IMPORT_REFERENCES gir,
GL_JE_LINES   gll,                            
XLA_AE_LINES  ael,                            
XLA_AE_HEADERS   aeh,                            
XLA_EVENTS    aea                    
WHERE    aea.event_id IN ( SELECT  aid.accounting_event_id FROM    AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE   aid.invoice_id in (10448,
68267)
 )                  
AND      ael.gl_sl_link_id = gir.gl_sl_link_id                
AND      gir.gl_sl_link_table in ('APECL', 'XLAJEL')                
AND      aea.application_id = 200                
AND      aea.event_id = aeh.event_id                  
AND      aeh.ae_header_id = ael.ae_header_id                    
AND      gll.je_header_id = gir.je_header_id                  
AND      gll.je_line_num  = gir.je_line_num

----------------------------------------------------------------------------------------------------------------------

Raheem Bhai Query (Data Already Transferred to GL)

SELECT
H.NAME,
H.DEFAULT_EFFECTIVE_DATE,
H.DESCRIPTION,
H.JE_HEADER_ID,
A.JE_HEADER_ID,
H.JE_SOURCE,
H.JE_CATEGORY,
H.LEDGER_ID,
H.ACTUAL_FLAG,
H.PERIOD_NAME,
A.GL_SL_LINK_ID,
H.CODE_COMBINATION_ID,
H.ENTERED_DR,
H.ENTERED_CR,
A.ACCOUNTED_DR,
A.Accounted_Cr
FROM
(
SELECT * FROM GL_INTERFACE
WHERE USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices' AND LEDGER_ID=2021 AND ACTUAL_FLAG='A' -- 783
) A,
(
SELECT
H.JE_SOURCE,
H.JE_CATEGORY,
H.LEDGER_ID,
H.ACTUAL_FLAG,
H.PERIOD_NAME,
gll.CODE_COMBINATION_ID,
ael.ENTERED_DR, ael.ENTERED_CR,
ael.gl_sl_link_id,
H.NAME,
H.DEFAULT_EFFECTIVE_DATE,
H.JE_HEADER_ID,
H.DESCRIPTION
FROM
GL_JE_HEADERS H,
GL_JE_LINES gll,
XLA_AE_HEADERS aeh,
XLA_AE_LINES ael,
XLA_EVENTS aea,
GL_IMPORT_REFERENCES gir

WHERE 1=1
AND H.JE_HEADER_ID=gll.JE_HEADER_ID
   AND ael.gl_sl_link_id = gir.gl_sl_link_id
   AND gir.gl_sl_link_table in ('APECL', 'XLAJEL','XLAJEL')
   AND aea.application_id = 200
   AND aea.event_id = aeh.event_id
   AND aeh.ae_header_id = ael.ae_header_id
   AND gll.je_header_id = gir.je_header_id
   AND gll.je_line_num = gir.je_line_num

AND H.JE_SOURCE='Payables'
AND H.JE_CATEGORY='Purchase Invoices'
AND H.LEDGER_ID=2021
AND H.ACTUAL_FLAG='A'
--AND H.DEFAULT_EFFECTIVE_DATE='20-JUL-2009'
--AND H.PERIOD_NAME='JUL-09'
--AND H.DESCRIPTION LIKE '%200900160%'
) H
WHERE 1=1
AND H.JE_SOURCE=A.USER_JE_SOURCE_NAME
AND H.JE_CATEGORY=A.USER_JE_CATEGORY_NAME
AND H.LEDGER_ID=A.LEDGER_ID
AND H.ACTUAL_FLAG=A.ACTUAL_FLAG
AND H.PERIOD_NAME=A.PERIOD_NAME
--AND H.CODE_COMBINATION_ID=A.CODE_COMBINATION_ID
--AND (H.ENTERED_DR=A.ENTERED_DR or H.ENTERED_CR=A.ENTERED_CR)
AND H.GL_SL_LINK_ID=A.GL_SL_LINK_ID

-------------------------------------------------------------------------------------------------------------------------

Raheem Bhai Query (Data Not Transferred to GL)

SELECT * FROM GL_INTERFACE WHERE USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices' AND
(   USER_JE_SOURCE_NAME,
    USER_JE_CATEGORY_NAME,
    LEDGER_ID,
    ACTUAL_FLAG,
    PERIOD_NAME,
    GL_SL_LINK_ID
)
NOT IN -- 413
(
    SELECT
    --H.NAME,
    --H.DEFAULT_EFFECTIVE_DATE,
    --H.DESCRIPTION,
    --H.JE_HEADER_ID,
    --A.JE_HEADER_ID,
    H.JE_SOURCE,
    H.JE_CATEGORY,
    H.LEDGER_ID,
    H.ACTUAL_FLAG,
    H.PERIOD_NAME,
    A.GL_SL_LINK_ID
    --H.CODE_COMBINATION_ID,
    --H.ENTERED_DR,
    --H.ENTERED_CR
    FROM
    (
    SELECT * FROM GL_INTERFACE
    WHERE USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices' AND LEDGER_ID=2021 AND ACTUAL_FLAG='A' -- 783
    ) A,
    (
    SELECT
    H.JE_SOURCE,
    H.JE_CATEGORY,
    H.LEDGER_ID,
    H.ACTUAL_FLAG,
    H.PERIOD_NAME,
    gll.CODE_COMBINATION_ID,
    ael.ENTERED_DR, ael.ENTERED_CR,
    ael.gl_sl_link_id,
    H.NAME,
    H.DEFAULT_EFFECTIVE_DATE,
    H.JE_HEADER_ID,
    H.DESCRIPTION
    FROM
    GL_JE_HEADERS H,
    GL_JE_LINES gll,
    XLA_AE_HEADERS aeh,
    XLA_AE_LINES ael,
    XLA_EVENTS aea,
    GL_IMPORT_REFERENCES gir

    WHERE 1=1
    AND H.JE_HEADER_ID=gll.JE_HEADER_ID
       AND ael.gl_sl_link_id = gir.gl_sl_link_id
       AND gir.gl_sl_link_table in ('APECL', 'XLAJEL','XLAJEL')
       AND aea.application_id = 200
       AND aea.event_id = aeh.event_id
       AND aeh.ae_header_id = ael.ae_header_id
       AND gll.je_header_id = gir.je_header_id
       AND gll.je_line_num = gir.je_line_num

    AND H.JE_SOURCE='Payables'
    AND H.JE_CATEGORY='Purchase Invoices'
    AND H.LEDGER_ID=2021
    AND H.ACTUAL_FLAG='A'
    --AND H.DEFAULT_EFFECTIVE_DATE='20-JUL-2009'
    --AND H.PERIOD_NAME='JUL-09'
    --AND H.DESCRIPTION LIKE '%200900160%'
    ) H
    WHERE 1=1
    AND H.JE_SOURCE=A.USER_JE_SOURCE_NAME
    AND H.JE_CATEGORY=A.USER_JE_CATEGORY_NAME
    AND H.LEDGER_ID=A.LEDGER_ID
    AND H.ACTUAL_FLAG=A.ACTUAL_FLAG
    AND H.PERIOD_NAME=A.PERIOD_NAME
    --AND H.CODE_COMBINATION_ID=A.CODE_COMBINATION_ID
    --AND (H.ENTERED_DR=A.ENTERED_DR or H.ENTERED_CR=A.ENTERED_CR)
    AND H.GL_SL_LINK_ID=A.GL_SL_LINK_ID


)

--------------------------------------------------------------------------------------------------------------------------

How to verify Posted data in GL from GL_Interface Duplicate Records

select * from gl_je_headers ll, gl_je_lines kk
where ll.JE_HEADER_ID=kk.JE_HEADER_ID
and ll.STATUS='P'
and ll.ACTUAL_FLAG='A'
and kk.STATUS='P'
and kk.LEDGER_ID=2021
and ll.JE_SOURCE='Payables'
and ll.JE_CATEGORY='Purchase Invoices'
and (ll.JE_HEADER_ID, kk.JE_LINE_NUM) in
(select mm.JE_HEADER_ID, mm.JE_LINE_NUM
from gl_import_references mm, gl_interface jj
where mm.GL_SL_LINK_ID=jj.GL_SL_LINK_ID
)