USP_REPORT_AMPROIMPORTBATCHEXCEPTION_AUCTIONPURCHASE

            CREATE procedure dbo.USP_REPORT_AMPROIMPORTBATCHEXCEPTION_AUCTIONPURCHASE
            (
                @BATCHID uniqueidentifier
            )
            with execute as owner
            as
            begin
                set nocount on;

                declare @TABLENAME nvarchar(128);
                select @TABLENAME = dbo.UFN_BATCH_GETREPORTTABLENAME(@BATCHID, 1);

                declare @SQL nvarchar(max);
                set @SQL = N'set nocount on;' +
                             ' with PURCHASE_CTE(ID, REASON, RECORDTYPECODE, RECORDTYPE, PURCHASE_AMOUNT, PURCHASE_PAYMENTMETHOD, PURCHASE_CONSTITUENTNAME, ISO4217, DECIMALDIGITS, CURRENCYSYMBOL, SYMBOLDISPLAYSETTINGCODE, SEQUENCE)' +
                             ' as' +
                             ' (' +
                             ' select' +
                                ' REPORTTABLE.ID,' +
                                ' cast(REPORTTABLE.REASON as nvarchar(max)) REASON,' +
                                ' REPORTTABLE.RECORDTYPECODE,' +
                                ' REPORTTABLE.RECORDTYPE,' +
                                ' BATCHAMPROIMPORTPURCHASE.PURCHASE_AMOUNT,' +
                                ' BATCHAMPROIMPORTPURCHASE.PURCHASE_PAYMENTMETHOD,' +
                                ' REPORTTABLE.PURCHASE_CONSTITUENTNAME,' +
                                ' REPORTTABLE.ISO4217,' +
                                ' REPORTTABLE.DECIMALDIGITS,' +
                                ' REPORTTABLE.CURRENCYSYMBOL,' +
                                ' REPORTTABLE.SYMBOLDISPLAYSETTINGCODE,' +
                                ' REPORTTABLE.SEQUENCE' +
                             ' from' +
                                ' dbo.' + @TABLENAME + ' REPORTTABLE' +
                                ' inner join dbo.BATCHAMPROIMPORTPURCHASE on BATCHAMPROIMPORTPURCHASE.ID = REPORTTABLE.ID' +
                            ' where' +
                                ' REPORTTABLE.RECORDTYPECODE = 5' +
                        ' )' +
                        ' select' +
                            ' PURCHASE.ID,' +
                            ' PURCHASE.REASON,' +
                            ' PURCHASE.RECORDTYPECODE,' +
                            ' PURCHASE.RECORDTYPE,' +
                            ' PURCHASE.PURCHASE_AMOUNT,' +
                            ' PURCHASE.PURCHASE_PAYMENTMETHOD,' +
                            ' PURCHASE.PURCHASE_CONSTITUENTNAME,' +
                            ' PURCHASE.ISO4217,' +
                            ' PURCHASE.DECIMALDIGITS,' +
                            ' PURCHASE.CURRENCYSYMBOL,' +
                            ' PURCHASE.SYMBOLDISPLAYSETTINGCODE,' +
                            ' count(BATCHAMPROIMPORTPURCHASEDETAIL.ID) PURCHASECOUNT,' +
                            ' TRANSACTIONTYPE.PURCHASEDETAIL_TRANSACTIONTYPE' +
                        ' from' +
                            ' PURCHASE_CTE PURCHASE' +
                            ' inner join dbo.BATCHAMPROIMPORTPURCHASEDETAIL on BATCHAMPROIMPORTPURCHASEDETAIL.PURCHASEDETAIL_PURCHASEID = PURCHASE.ID' +
                            ' cross apply(' +
                                            ' select top 1' +
                                                ' PURCHASEDETAIL.PURCHASEDETAIL_TRANSACTIONTYPE ' +
                                            ' from ' +
                                                ' dbo.BATCHAMPROIMPORTPURCHASEDETAIL PURCHASEDETAIL' +
                                            ' where ' +
                                                ' PURCHASEDETAIL.PURCHASEDETAIL_PURCHASEID = PURCHASE.ID' +
                            ' ) TRANSACTIONTYPE' +
                         ' group by' +
                            ' PURCHASE.ID,' +
                            ' PURCHASE.REASON,' +
                            ' PURCHASE.RECORDTYPECODE,' +
                            ' PURCHASE.RECORDTYPE,' +
                            ' PURCHASE.PURCHASE_AMOUNT,' +
                            ' PURCHASE.PURCHASE_PAYMENTMETHOD,' +
                            ' PURCHASE.PURCHASE_CONSTITUENTNAME,' +
                            ' PURCHASE.ISO4217,' +
                            ' PURCHASE.DECIMALDIGITS,' +
                            ' PURCHASE.CURRENCYSYMBOL,' +
                            ' PURCHASE.SYMBOLDISPLAYSETTINGCODE,' +
                            ' TRANSACTIONTYPE.PURCHASEDETAIL_TRANSACTIONTYPE,' +
                            ' PURCHASE.SEQUENCE' +
                         ' order by PURCHASE.SEQUENCE;'

                exec sp_executesql @SQL;
            end