Hello,

I am trying to produce a report that is showing duplicate transaction amounts.

Its by a same draft amount by same account

The problem is that we get false positives where it reports duplicate transactions but when you go to look in the application there is an offsetting transaction that reverses the transaction. I am trying have my fex dectect these transaction and throw out all the transactions that are associated with it. All draft transactions have a code and there is also a status. The offset transactions have no status but do have a code.

Does anybody have a routine that would do this?


Thanks

This message has been edited. Last edited by: moyer1dl,

You're going to have to provide a data sample showing when a transaction should be included and when it should be excluded. It's hard to give guidance for something so specific without some samples.

Here is some sample code dummied up of it working with the false positives. What I need to do is when I have a transaction that zeros out the associated transaction with the equvalent negative transaction I need to throw them both out. Normal transactions have a status of "printed" (which I am using in this one without the transaction code) and they also do have a transaction code of like 'PT' and the offset transactions don't have a status but a transaction code like 'XR'

 TABLE FILE POLICY
PRINT
     TRXAMT
     POLICY
     TRXCODE 

WHERE POLICY LIKE 'Z%' OR 'F%';
WHERE TRANSDATE FROM '20070801' TO '20080912';
WHERE STATUS EQ 'prntd';
WHERE TRXAMT GT 100.00;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS DUPHLD1
 
DEFINE FILE DUPHLD1
ABCD/I2=1;
END
TABLE FILE DUPHLD1
SUM ABCD BY POLICY BY TRXAMT
PRINT 			
		POLICY
		TRXCODE
		TRXAMT
		ABCD
BY POLDISPLAYPOLICY  BY TRXAMOUNT
ON TABLE HOLD AS DUPHLD4
END
TABLE FILE DUPHLD4
PRINT
     TRXAMT/P21.2C AS 'AMT'
     POLICY AS 'PO'
     TRXCODE AS 'TC'
BY TRXAMT NOPRINT
BY POLICY NOPRINT

ON POLDISPLAYPOLICY SKIP-LINE
IF ABCD GT 1

ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF  

The sample code is not as helpful as some sample data would be. Is there a description or some other field that would identify an offset to a specific transaction? You could compute a field that is the absolute value of the transaction amount and Sort and Sum by that field. A WHERE TOTAL actual_amount_field NE 0 would eliminate any combinations where every value has an offset. However, say there are three transactions - two for $100 and one for -$100. All three would still appear. The only way you'll get them to match up and cancel out is if they have some common characterisitic that couples them together.

DRAFT    PO        PMT         CLAIM                 DRAFT                              ISSUE
AMOUNT   NUMBER    STATUS COV NUMBER     PAYEE NAME  NUMBER                        TC   DATE       ITEM

108.00     31xxxx  .       20 11k33333 .           .                               OS 2xxx/xx/30 011
108.00     31xxxx  Printed 20 11k33333 johndoe     001184xxxx                      PC 2xxx/xx/22 011
108.00     31xxxx  Printed 20 11k33333 JANE DOE    001183xxxx                      PC 2xxx/1x/03 011

Here is some dummied up data. I need to my program to through out all of these because of the first transaction being an offset.

I'm not sure why we're trying to help you work out a business-rules problem, but here are a couple of questions:

How do you know which are the duplicate transactions? I see three and they look triplicate to me.

How do you know which transaction offsets which transaction? The OS transaction offsets one of the other two because they have the same PO Number and Claim Number, but which of the other two is it supposed to offset? Or does it matter?

It doesn't matter

Well, it sounds like you're throwing out every record just because an offset record exists - which doesn't make sense to me, but like Francis said, this is entirely business logic which will be whatever fits your business rules. So just create a field counter that checks to see if an 'OS' transaction occurs for those related transactions (related meaning amount and whatever other fields will define a related transaction set) and use a WHERE TOTAL to throw out any set that has anything other than a 0.

Something like this:

SUM
COMPUTE OFFSET/I1=IF TC EQ 'OS' THEN 1 ELSE 0; NOPRINT
BY DRAFT_AMOUNT NOPRINT
BY PO_NUMBER NOPRINT
PRINT DRAFT_AMOUNT PO_NUMBER PMT_STATUS ...
BY DRAFT_AMOUNT
BY PO_NUMBER
WHERE TOTAL OFFSET EQ 0;

.... or use MATCH with the first extract being all records and the matched file being only those of status OS. Then use OLD-NOT-NEW to drop the records. You'll have to be judicial on the sort field of course.

T

Thanks for your help.

The code worked but instead of throwing them out we changed the offset amount to a positive amount so it would pair with all the matching transactions amounts. That way the reviewer can decide if the transaction needs to be thrown out or not. Although the other version still might come in handy.