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
,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