USP_SIMPLEDATALIST_CREDITCARDPROCESSINGBATCHTEMPLATEBYUSER

CREATE procedure dbo.USP_SIMPLEDATALIST_CREDITCARDPROCESSINGBATCHTEMPLATEBYUSER 
(
    @CURRENTAPPUSERID uniqueidentifier,
    @ISFORWEBSHELL bit = 0
)
as
    set nocount on;

    declare @MULTICURRENCYENABLED as bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');

    --return all batch templates with the required fields.

    with xmlnamespaces (
        'bb_appfx_commontypes' as c
    )    
    select distinct 
        BATCHTEMPLATE.ID as VALUE, 
        BATCHTEMPLATE.NAME as LABEL 
    from 
        dbo.BATCHTEMPLATE 
        left join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
    where
        dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR(@CURRENTAPPUSERID, BATCHTEMPLATE.ID) = 1
        and BATCHTEMPLATE.CUSTOM = 0
        and BATCHTEMPLATE.ACTIVE = 1
        and BATCHTEMPLATE.TEMPLATEUSECODE in (0,1)
        and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
            (
                BATCHTYPECATALOG.SPECXML.query
                    (
                        'declare namespace common="bb_appfx_commontypes";
                        /*/common:InstalledProductList'
                    )
            )
        and
            (
                isnull(@ISFORWEBSHELL, 0) = 0
                --Exclude batch types that have handlers but no web shell conversion when the list is loaded for web shell

                or 0 = BATCHTYPECATALOG.SPECXML.value
                    (
                        'declare namespace batch="bb_appfx_batchtype";
                        empty(/batch:BatchTypeSpec/batch:WebEventHandlers/batch:BatchEventHandler)
                        and not(empty(/batch:BatchTypeSpec/batch:EventHandlers/batch:BatchEventHandler))',
                        'bit'
                    )
            )

        --Fields required by all EFT processes

        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CONSTITUENTID"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="DATE"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="AMOUNT"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="PAYMENTMETHODCODE"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="RECEIPTAMOUNT"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="REVENUESTREAMS"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="POSTSTATUSCODE"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="TYPECODE"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="SEQUENCE"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="GIVENANONYMOUSLY"])') = 1

        --Fields required specifically by the credit card processing process

        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CARDHOLDERNAME"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CREDITCARDNUMBER"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CREDITCARDTOKEN"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="CREDITTYPECODEID"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="EXPIRESON"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="AUTHORIZATIONCODE"])') = 1
        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="REJECTIONMESSAGE"])') = 1

        and (    
            ( --if not enhanced revenue batch, then multicurrency must be turned off

                BATCHTYPECATALOG.ID <> '326c43a6-d162-4fd4-8d61-fef9a0ee8c5e' --Enhanced revenue batch

                and @MULTICURRENCYENABLED = 0
            )
            or 
            ( --if enhanced revenue batch, the fields below are required

                BATCHTYPECATALOG.ID = '326c43a6-d162-4fd4-8d61-fef9a0ee8c5e' --Enhanced revenue batch

                and
                BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="PDACCOUNTSYSTEMID"])') = 1 --only if batch type is ERB

                and (
                    @MULTICURRENCYENABLED = 0
                    or (
                        BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="TRANSACTIONCURRENCYID"])') = 1
                        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="BASECURRENCYID"])') = 1
                        and    BATCHTEMPLATE.FORMDEFINITIONXML.exist('(c:FormMetaData/c:FormFields/c:FormField[upper-case(@FieldID)="BASEEXCHANGERATEID"])') = 1
                    )
                )
            )
        )
    order by 
        BATCHTEMPLATE.NAME;