Thursday, October 11, 2012

Query to identify receipts for which multiple entries are generated with Transaction Type = 'RECEIVE'

SELECT   receipt_num
        ,transaction_id
        ,shipment_line_id
        ,acct_type
        ,acct_nature
        ,a.code_combination_id
        ,entered_dr
        ,entered_cr
        ,COUNT(*) cnt
        , SUM(NVL(entered_cr, 0) ) - AVG(NVL(entered_cr, 0) ) "Excess Credit"
        , SUM(NVL(entered_dr, 0) ) - AVG(NVL(entered_dr, 0) ) "Excess Debit"
        ,gcc.concatenated_segments
    FROM jai_rcv_journal_entries a
        ,gl_code_combinations_kfv gcc
   WHERE 0 <
            (SELECT COUNT(*)
               FROM jai_rcv_journal_entries b
              WHERE a.transaction_id = b.transaction_id
                AND a.code_combination_id = b.code_combination_id
                AND a.acct_nature = b.acct_nature
                AND a.acct_type = b.acct_type
                AND NVL(a.entered_cr, 0) = NVL(b.entered_cr, 0)
                AND NVL(a.entered_dr, 0) = NVL(b.entered_dr, 0)
                AND a.ROWID <> b.ROWID)
     AND a.period_name = 'P01-11'                                                                                                 --'&period_name'
     AND a.organization_code = 22                                                                                           --'&organization_code'
     AND a.transaction_type = 'RECEIVE'
--and a.code_combination_id=
     AND gcc.code_combination_id = a.code_combination_id
GROUP BY receipt_num
        ,transaction_id
        ,shipment_line_id
        ,acct_type
        ,acct_nature
        ,a.code_combination_id
        ,entered_dr
        ,entered_cr
        ,gcc.concatenated_segments
ORDER BY receipt_num
        ,transaction_id
        ,shipment_line_id
        ,acct_type
        ,acct_nature
        ,a.code_combination_id
        ,entered_dr
        ,entered_cr

No comments:

Post a Comment