I am creating an informatica workflow which can extract data from two tables of DB2 database and load into a temporary table. Suppose the two source tables name are Account (Parent) and Activities (Child). They have 1:M relationship. Means an Account can have many Activities (Account.PK = Activities.FK).
Activities table has two columns- first 'Type' whose value could be 'Paid', 'Will-Pay' or 'Not-Paid'.And second column is 'Created_Date' datetime datatype, whenever you create new activity record, date and time would get stamp in this field. Now, condition to load data in temporary table is - "For an Account record, it would 1st check in Activities table for today's Paid activities (Type = Paid). If it finds more than one paid activities, then it would pick the Latest created one (Created_Date column) out of them.
If there is no Paid activity record for the Account, then it would pick latest created 'Will-Pay' activity." Means, it should pick latest Paid activity for today (Sysdate) for an Account, if it is not present then only It will pick latest Will-pay activity for today. Please help me to understand how I can implement this logic in Informatica workflow and which transformations I should use and how? Thanks alot. Kindly help.
