UFN_REVENUE_GENERATESTOCKDETAILGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUE_GENERATESTOCKDETAILGLDISTRIBUTION(@REVENUEID uniqueidentifier = null)
            returns table 
            as return 
            /*    Getting 'Postable' Stock 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 STOCKSALEADJUSTMENT.ID is null then STOCKSALE.SALEPOSTDATE  --if no unposted adjustment then use Stock Detail Post Date

                     else STOCKSALEADJUSTMENT.POSTDATE
                end AS POSTDATE,                 
                case when STOCKSALEADJUSTMENT.ID is null then STOCKSALE.SALEPOSTSTATUSCODE  --if no unposted adjustment then use Stock Detail Post Status Code

                     else STOCKSALEADJUSTMENT.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,
                STOCKSALE.ID as STOCKSALEID,
                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.STOCKDETAIL with (nolock) on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
                inner join dbo.STOCKSALE with (nolock) on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
                left join dbo.STOCKSALEADJUSTMENT with (nolock) on STOCKSALE.ID = STOCKSALEADJUSTMENT.STOCKSALEID and STOCKSALEADJUSTMENT.POSTSTATUSCODE <> 0
                cross join 
                    ( 
                        select '7' as PAYMENTMETHODCODE, 'Sold Stock' as PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE
                            union all 
                        select '200' as PAYMENTMETHODCODE, 'Gain' as PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                            union all 
                        select '202' as PAYMENTMETHODCODE, 'Loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                            union all 
                        select '204' as PAYMENTMETHODCODE, 'Fees' 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_GETSTOCKDETAILSPLITSBYTRANSACTION(REVENUE.ID, CODES.PAYMENTMETHODCODE) as SPLITS                
            where  
                (REVENUE.ID = @REVENUEID or @REVENUEID is NULL) 
                and ((CODES.PAYMENTMETHODCODE = 200 and STOCKSALE.SALEAMOUNT > (STOCKSALE.NUMBEROFUNITS * STOCKDETAIL.MEDIANPRICE)) or    -- Stock Gain

                     (CODES.PAYMENTMETHODCODE = 202 and STOCKSALE.SALEAMOUNT < (STOCKSALE.NUMBEROFUNITS * STOCKDETAIL.MEDIANPRICE)) or    -- Stock Loss

                     (CODES.PAYMENTMETHODCODE = 204 and STOCKSALE.FEE > 0) or                                                           -- Broker Fee

                      CODES.PAYMENTMETHODCODE = 7)                                                                                        -- Sold Stock

                and (SPLITS.REVENUESPLITID = REVENUESPLIT.ID or (SPLITS.REVENUESPLITID is null and REVENUESPLIT.ID is null and REVENUESPLIT.APPLICATIONCODE = 1 and SPLITS.REVENUEID = REVENUE.ID))
                and SPLITS.STOCKSALEID = STOCKSALE.ID
                and STOCKSALE.SALEPOSTSTATUSCODE != 2 -- 2 Do Not Post

                and (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 4) -- Verify the stock has sold (it's now implied since STOCKSALE is included in the join)