UFN_REVENUE_GENERATEGIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUE_GENERATEGIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION(@REVENUEID uniqueidentifier = null)
            returns table 
            as return 
            /*    Getting 'Postable' GIFTINKIND Sales    */
            select 
                REVENUE.ID as REVENUEID, 
                REVENUESPLIT.ID as REVENUESPLITID, 
                dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(CODES.PAYMENTMETHODCODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                tf.TRANSACTIONTYPECODE, 
                case when GIFTINKINDSALEADJUSTMENT.ID is null then GIFTINKINDSALE.SALEPOSTDATE  --if no unposted adjustment then use Gift-in-kind Detail Post Date

                     else GIFTINKINDSALEADJUSTMENT.POSTDATE
                end AS POSTDATE, 
                case when GIFTINKINDSALEADJUSTMENT.ID is null then GIFTINKINDSALE.SALEPOSTSTATUSCODE  --if no unposted adjustment then use Gift-in-kind Detail Post Status Code

                     else GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE
                end AS POSTSTATUSCODE,
                tf.ACCOUNTSTRING, 
                tf.PROJECTCODE as PROJECT, 
                SPLITS.AMOUNT AMOUNT, 
                dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE  (REVENUESPLIT.ID, CODES.PAYMENTMETHOD, CODES.REVENUETYPE) as REFERENCE, 
                tf.ERRORMESSAGE,
                REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                REVENUEPAYMENTMETHOD.ID as REVENUEPAYMENTMETHODID,
                GIFTINKINDSALE.ID as GIFTINKINDSALEID,
                tf.ACCOUNTID as ACCOUNTID, 
                SPLITS.TRANSACTIONSPLITAMOUNT TRANSACTIONAMOUNT, 
                SPLITS.ORGANIZATIONAMOUNT ORGANIZATIONAMOUNT, 
                REVENUE.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID, 
                REVENUE.BASECURRENCYID as BASECURRENCYID, 
                REVENUE.BASEEXCHANGERATEID as BASEEXCHANGERATEID, 
                REVENUE.ORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
                tf.MAPPEDVALUES
            from dbo.REVENUE with (nolock)
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
                inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL with (nolock) on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
                inner join dbo.GIFTINKINDSALE with (nolock) on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
                left join dbo.GIFTINKINDSALEADJUSTMENT with (nolock) on GIFTINKINDSALE.ID = GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID and GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE <> 0
                cross join 
                    ( 
                        select '12' as PAYMENTMETHODCODE, 'Sold Gift-in-kind' as PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE
                            union all 
                        select '207' as PAYMENTMETHODCODE, 'Gain' as PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                            union all 
                        select '208' as PAYMENTMETHODCODE, 'Loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                    ) as CODES
                cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as tf 
                outer apply dbo.UFN_REVENUE_GETGIFTINKINDPAYMENTMETHODDETAILSPLITSBYTRANSACTION(REVENUE.ID, CODES.PAYMENTMETHODCODE) as SPLITS
            where  
                (REVENUE.ID = @REVENUEID or @REVENUEID is NULL) 
                and ((CODES.PAYMENTMETHODCODE = 207 and GIFTINKINDSALE.SALEAMOUNT > (GIFTINKINDSALE.NUMBEROFUNITS * GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE)) or  -- GIFTINKIND Gain

                     (CODES.PAYMENTMETHODCODE = 208 and GIFTINKINDSALE.SALEAMOUNT < (GIFTINKINDSALE.NUMBEROFUNITS * GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE)) or  -- GIFTINKIND Loss

                      CODES.PAYMENTMETHODCODE = 12)                                                                                                                     -- Sold Gift-in-kind

                and (SPLITS.REVENUESPLITID = REVENUESPLIT.ID or (SPLITS.REVENUESPLITID is null and REVENUESPLIT.ID is null and REVENUESPLIT.APPLICATIONCODE = 0 and SPLITS.REVENUEID = REVENUE.ID))
                and SPLITS.GIFTINKINDSALEID = GIFTINKINDSALE.ID
                and (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 6) -- Verify the Gift-in-kind has sold (it's now implied since GIFTINKINDSALE is included in the join)