SQLize Online / PHPize Online  /  SQLtest Online

(SELECT payments.tnum,payments.batchfcn_in,payments.batchfcn_out,DECODE(payments.TYPE,'FILE',AcctOrCompByActionXRef.Account_or_Company,payments.accountfilter) as accountfilter,payments.approved_by_1,payments.approved_by_2,payments.approved_by_3,payments.approved_by_4,payments.approved_by_5,payments.approved_by_6,payments.approved_by_name_1,payments.approved_by_name_2,payments.approved_by_name_3,payments.approved_by_name_4,payments.approved_by_name_5,payments.approved_by_name_6,payments.approved_by_panelcode_1,payments.approved_by_panelcode_2,payments.approved_by_panelcode_3,payments.approved_by_panelcode_4,payments.approved_by_panelcode_5,payments.approved_by_panelcode_6,payments.approved_timestamp_1,payments.approved_timestamp_2,payments.approved_timestamp_3,payments.approved_timestamp_4,payments.approved_timestamp_5,payments.approved_timestamp_6,payments.approved_usergroup_1,payments.approved_usergroup_2,payments.approved_usergroup_3,payments.approved_usergroup_4,payments.approved_usergroup_5,payments.approved_usergroup_6,payments.approver_rejection_by,payments.approver_rejection_name,payments.approver_rejection_timestamp,payments.approver_rejection_usergroup,payments.batchtypedesc,payments.bene_accountentitlement,payments.canceled_by,payments.canceled_by_name,payments.canceled_timestamp,payments.canceled_usergroup,payments.clientcomp_groupnum,payments.clientcomp_name,payments.checkapp_groupnum,payments.checkapp_desc,payments.confidentialflag,payments.hiddenflag,payments.createdfrom,payments.credit_amount,payments.credit_currency,payments.customer_reference,payments.cutoff_time,payments.debit_account_number,payments.debit_amount,payments.debit_currency,payments.deleted_by,payments.deleted_by_name,payments.deleted_timestamp,payments.deleted_usergroup,payments.digest,payments.digestauditid,payments.duplicatereason,payments.entered_by,payments.entered_by_name,payments.entered_timestamp,payments.entrymethod,payments.extract_timestamp,payments.function,payments.imported_by,payments.imported_usergroup,payments.importid,payments.last_action_time,payments.macfield,payments.modified_by,payments.modified_by_name,payments.modified_timestamp,payments.modified_usergroup,payments.number_of_signatures,payments.parentusergroup,payments.paymentmethod,payments.post_date, get_app_resource(DECODE(payments.possibleduplicateflag,1,'common.yes','common.no'),'en_US') AS possibleduplicateflag,payments.product,GET_APP_RESOURCE('PAY.EntryMethod.' || DECODE(payments.createdfrom, 2, 'Schedule.', 3, 'RFP.', '') || payments.entrymethod, 'en_US') AS entrymethoddesc,CASE payments.TypeWHEN 'BPAY' THEN GET_APP_RESOURCE('RTGS.product.billpay', 'en_US')ELSE GET_APP_RESOURCE(payments.productdesc, 'en_US')END AS PRODUCTDESC,CASE payments.productWHEN 'RTGS'THEN payments.special_instructionsWHEN 'USACH' THEN payments.batchcommentELSE ''END AS paymentcomment,CASE payments.productWHEN 'RTGS'THEN payments.special_instructionsWHEN 'USACH' THEN payments.batchcommentELSE ''END as PAYMENTCOMMENT_DISPLAY,payments.rejection_by,DECODE(payments.REJECTION_REASON,NULL,payments.APPROVER_REJECTION_REASON,GET_APP_RESOURCE(payments.REJECTION_REASON,'en_US')) AS REJECTION_REASON,payments.rejection_timestamp,payments.rejection_usergroup,payments.released_by,payments.released_by_name,payments.released_timestamp,payments.released_usergroup,payments.repaired_by,payments.repaired_by_name,payments.repaired_timestamp,payments.repaired_usergroup,payments.status,GET_APP_RESOURCE(payments.status_description, 'en_US') AS status_description,payments.subtype,payments.surrogatetnum,payments.templatecodeentitlement,payments.totalamtcredit,payments.totalamtdebit,payments.tran_date,payments.TYPE,payments.unapproved_by,payments.unapproved_by_name,payments.unapproved_timestamp,payments.unapproved_usergroup,payments.updatecount__,payments.unreleased_by,payments.unreleased_by_name,payments.unreleased_timestamp,payments.unreleased_usergroup,payments.usergroup,payments.usergroupsequencenumber,payments.lastapprover,payments.TemplateTNum,payments.clearingmethod,payments.Transition_Previous_Status,payments.cmb_bene_account,payments.cmb_bene_bank_name,payments.cmb_bene_bank_id,payments.cmb_bene_id,payments.cmb_bene_name,payments.cmb_dbcr_flag,payments.cmb_debit_account_number,payments.cmb_debit_account_name,payments.cmb_transaction_amount,payments.cmb_transaction_currency,payments.cmb_credit_amount,payments.cmb_credit_currency,payments.cmb_debit_amount,payments.cmb_debit_currency,payments.cmb_debit_bank_code,CASE WHEN productCode = 'USACH' THEN payments.cmb_originator_idELSE NULL END AS payments.cmb_originator_id, CASE WHEN productCode = 'USACH' THEN payments.cmb_originator_nameELSE NULL END AS payments.cmb_originator_name,payments.cmb_template_code,payments.cmb_template_description,GET_APP_RESOURCE(payments.cmb_type_description,'en_US') AS cmb_type_description,payments.cmb_value_date,payments.cmb_value_date AS cmb_value_date_orig,CASE payments.TYPEWHEN 'PAYMODE' THEN '---'WHEN 'FILE' THEN CASE payments.PAYMENTMETHOD WHEN 'PMX' THEN '---'ELSE payments.cmb_account_preferenceENDELSE payments.cmb_account_preferenceEND as CMB_ACCOUNT_PREFERENCE,GET_APP_RESOURCE(payments.cmb_dbcr_indicator, 'en_US') AS cmb_dbcr_indicator,payments.cmb_batchname,payments.cmb_totalnumitems,payments.exchange_rate,payments.exchange_rate_contractid,payments.exchangeratetbd_flag,payments.rateortraderesult,getreleasetype(payments.usergroup, payments.parentusergroup, payments.type, payments.status, payments.value_date, payments.tran_date) AS release_type,payments.rate_traded_by,payments.entered_amount_flag,payments.debit_subaccount_num,payments.bene_subaccount_num,payments.INTEREST_RATE,payments.orderof_name,(CASE payments.Product WHEN 'RTGS' THEN payments.PAYMENT_DETAILSWHEN 'RTP' THEN payments.REMITTANCE_INFOELSE '' END) AS PAYMENT_DETAILS,payments.invoicenum,payments.ue_entrydesc,payments.bene_bank_account_number,DECODE(payments.subtype,'NACHA',DECODE(payments.samedayach, 1, GET_APP_RESOURCE('yes','en_US'),GET_APP_RESOURCE('no','en_US')),'--') AS samedayach, AcctOrCompByActionXRef.ACTIVATE_ACTION,AcctOrCompByActionXRef.APPROVE_ACTION,AcctOrCompByActionXRef.APPROVEOWN,AcctOrCompByActionXRef.DELETE_ACTION,AcctOrCompByActionXRef.GETRATE_ACTION,AcctOrCompByActionXRef.INSERT_ACTION,AcctOrCompByActionXRef.MAKEINST_ACTION,AcctOrCompByActionXRef.MAKETMPL_ACTION,AcctOrCompByActionXRef.MODIFY_ACTION,AcctOrCompByActionXRef.REJECT_ACTION,AcctOrCompByActionXRef.RELEASE_ACTION,AcctOrCompByActionXRef.RELEASEOWN,AcctOrCompByActionXRef.REPAIR_ACTION,DECODE (payments.TYPE, 'FILE', 1, AcctOrCompByActionXRef.SELECT_ACTION) AS SELECT_ACTION,AcctOrCompByActionXRef.SPAYMT_ACTION,AcctOrCompByActionXRef.UNAPPROV_ACTION,AcctOrCompByActionXRef.UNRELEASE_ACTION,AcctOrCompByActionXRef.RESTORE_ACTION,AcctOrCompByActionXRef.REVERSE_ACTION,payments.FILENAME_INBOUND,payments.SECOND_CONFIRMATION_NUMBER,payments.first_confirmation_number,payments.PREFUNDINGRESULT,CASE WHEN payments.TYPE IN ('TRANSFER', 'DRAFT', 'INTL', 'EURO') AND payments.STATUS IN ('EN', 'AP', 'NR', 'IA', 'HV', 'IC', 'RT', 'AR', 'RJ', 'RL') THEN payments.INDICATIVERATE ELSE NULLENDAS INDICATIVE_RATE,CASE WHEN payments.TYPE IN ('TRANSFER', 'DRAFT', 'INTL', 'EURO') AND payments.STATUS IN ('EN', 'AP', 'NR', 'IA', 'HV', 'IC', 'RT', 'AR', 'RJ', 'RL') THEN payments.INDICATIVEAMOUNT ELSE NULLEND AS INDICATIVE_AMOUNT,payments.BALANCECHECKRESULT,'' AS INITIATEPREFUND,payments.EXTRACT_DATE,payments.ENTRYCLASS,payments.BANKCODE,payments.TOTALNUMDEBIT,payments.TOTALNUMCREDIT,payments.DESTCOUNTRYCODE,payments.REVERSAL_APPROVED_BY_1,payments.REVERSAL_APPROVED_BY_2,payments.REVERSAL_APPROVED_BY_3,payments.REVERSAL_APPROVED_BY_4,payments.REVERSAL_APPROVED_BY_5,payments.REVERSAL_APPROVED_USERGROUP_1,payments.REVERSAL_APPROVED_USERGROUP_2,payments.REVERSAL_APPROVED_USERGROUP_3,payments.REVERSAL_APPROVED_USERGROUP_4,payments.REVERSAL_APPROVED_USERGROUP_5,payments.TESTFILEIMPORT,payments.CONINTLWIREFLAG,payments.PAYMENT_EXTERNAL_ID,payments.DIVIDEND_EARNED,payments.DIVIDEND_EARNED_CCY,PAY_CLIENTACCOUNTNAME(payments.ACCOUNTFILTER, 'PARENTBANK06', payments.PRODUCT, payments.CLIENTCOMP_GROUPNUM, 'en_US') as CLIENT_ACCOUNT_NAME,payments.CHILDRENSTATUSES,payments.INDIVIDUALACTION_FILE,DECODE(payments.TYPE,'CRTRAN', DECODE(payments.status,'BC',GET_SUBMITTED_MESSAGE(payments.TNUM), 'cpayfr.hyphen'), '') AS MESSAGE_STATUS,DECODE(payments.TYPE,'CRTRAN', DECODE(payments.status,'BC',GET_SUBMITTED_MESSAGE_TS(payments.tnum), ''), '') AS LAST_MESSAGE_TIMESTAMP,DECODE(payments.TYPE,'CRTRAN', DECODE(payments.status,'BC',GET_SUBMITTED_MESSAGE_STATE(payments.TNUM,payments.USERGROUP, 'USER01'), 0) ,'') AS MESSAGE_STATE,payments.RFP_MSG_ID,(payments.TOTALNUMDEBIT + payments.TOTALNUMCREDIT) AS TOTALNUMTRANSACTIONFROM (SELECT paymentscommon.tnum, ACCOUNTFILTER,APPROVED_BY_1,APPROVED_BY_2,APPROVED_BY_3,APPROVED_BY_4,APPROVED_BY_5,APPROVED_BY_6,APPROVED_BY_NAME_1,APPROVED_BY_NAME_2,APPROVED_BY_NAME_3,APPROVED_BY_NAME_4,APPROVED_BY_NAME_5,APPROVED_BY_NAME_6,APPROVED_BY_PANELCODE_1,APPROVED_BY_PANELCODE_2,APPROVED_BY_PANELCODE_3,APPROVED_BY_PANELCODE_4,APPROVED_BY_PANELCODE_5,APPROVED_BY_PANELCODE_6,APPROVED_TIMESTAMP_1,APPROVED_TIMESTAMP_2,APPROVED_TIMESTAMP_3,APPROVED_TIMESTAMP_4,APPROVED_TIMESTAMP_5,APPROVED_TIMESTAMP_6,APPROVED_USERGROUP_1,APPROVED_USERGROUP_2,APPROVED_USERGROUP_3,APPROVED_USERGROUP_4,APPROVED_USERGROUP_5,APPROVED_USERGROUP_6,APPROVER_REJECTION_BY,APPROVER_REJECTION_NAME,APPROVER_REJECTION_REASON,APPROVER_REJECTION_TIMESTAMP,APPROVER_REJECTION_USERGROUP,BATCHTYPEDESC,BENE_ACCOUNTENTITLEMENT,CANCELED_BY,CANCELED_BY_NAME,CANCELED_TIMESTAMP,CANCELED_USERGROUP,CLIENTCOMP_GROUPNUM,CLIENTCOMP_NAME,CHECKAPP_GROUPNUM,CHECKAPP_DESC,CONFIDENTIALFLAG,HIDDENFLAG,CREATEDFROM,CREDIT_AMOUNT,CREDIT_CURRENCY,CUSTOMER_REFERENCE,DECODE (Status, 'EN', Cutoff_Time, 'IA', Cutoff_Time, 'RT', Cutoff_Time, 'HV', Cutoff_Time, 'RA', Cutoff_Time, 'IV', Cutoff_Time, 'PI', Cutoff_Time, 'RI', Cutoff_Time, NULL) AS Cutoff_Time,DEBIT_ACCOUNT_NUMBER,DEBIT_AMOUNT,DEBIT_CURRENCY,DELETED_BY,DELETED_BY_NAME,DELETED_TIMESTAMP,DELETED_USERGROUP,DIGEST,DIGESTAUDITID,DUPLICATEREASON,ENTERED_BY,ENTERED_BY_NAME,ENTERED_TIMESTAMP,ENTRYMETHOD,EXTRACT_TIMESTAMP,FUNCTION,IMPORTED_BY,IMPORTED_USERGROUP,paymentscommon.importid,LAST_ACTION_TIME,MACFIELD,MODIFIED_BY,MODIFIED_BY_NAME,MODIFIED_TIMESTAMP,MODIFIED_USERGROUP,NUMBER_OF_SIGNATURES,PARENTUSERGROUP,PAYMENTMETHOD,POST_DATE,POSSIBLEDUPLICATEFLAG,PRODUCT,PRODUCTDESC,REJECTION_BY,REJECTION_REASON,REJECTION_TIMESTAMP,REJECTION_USERGROUP,RELEASED_BY,RELEASED_BY_NAME,RELEASED_TIMESTAMP,RELEASED_USERGROUP,REPAIRED_BY,REPAIRED_BY_NAME,REPAIRED_TIMESTAMP,REPAIRED_USERGROUP,STATUS,CASE TESTFILEIMPORT WHEN '1' THEN 'test.file.import.' || STATUS_DESCRIPTION ELSE STATUS_DESCRIPTION END AS STATUS_DESCRIPTION,SUBTYPE,SURROGATETNUM,TEMPLATECODEENTITLEMENT,TOTALAMTCREDIT,TOTALAMTDEBIT,TRAN_DATE,VALUE_DATE,TYPE,UNAPPROVED_BY,UNAPPROVED_BY_NAME,UNAPPROVED_TIMESTAMP,UNAPPROVED_USERGROUP,UPDATECOUNT__,UNRELEASED_BY,UNRELEASED_BY_NAME,UNRELEASED_TIMESTAMP,UNRELEASED_USERGROUP,USERGROUP,USERGROUPSEQUENCENUMBER,LASTAPPROVER,TEMPLATETNUM,CLEARINGMETHOD,PaymentsCommon.FILENAME_INBOUND,second_confirmation_number, first_confirmation_number,PREFUNDINGRESULT,INDICATIVERATE,INDICATIVEAMOUNT,BALANCECHECKRESULT,EFFECTIVEDATE,TESTFILEIMPORT,REVERSAL_APPROVED_BY_1,REVERSAL_APPROVED_BY_2,REVERSAL_APPROVED_BY_3,REVERSAL_APPROVED_BY_4,REVERSAL_APPROVED_BY_5,REVERSAL_APPROVED_USERGROUP_1,REVERSAL_APPROVED_USERGROUP_2,REVERSAL_APPROVED_USERGROUP_3,REVERSAL_APPROVED_USERGROUP_4,REVERSAL_APPROVED_USERGROUP_5,CONINTLWIREFLAG,PAYMENT_EXTERNAL_ID,DIVIDEND_EARNED,DIVIDEND_EARNED_CCY,CLIENT_ACCOUNT_NAME,filepaymentchildrenstatus(product, type, paymentscommon.tnum, paymentscommon.importid) AS childrenstatuses,INDIVIDUALACTION_FILE,TRANSITION_PREVIOUS_STATUS,interest_rate,cmb_bene_account, cmb_bene_bank_name, cmb_bene_bank_id, cmb_bene_id, cmb_bene_name, cmb_debit_account_number,cmb_debit_account_name, cmb_transaction_amount, cmb_transaction_currency, cmb_credit_amount, cmb_credit_currency,cmb_debit_amount, cmb_debit_currency, cmb_dbcr_flag, cmb_debit_bank_code, cmb_originator_id, cmb_originator_name,cmb_template_code, cmb_template_description, cmb_type_description, cmb_value_date,paymentsconsolidated.cmb_debit_account_number AS cmb_account_preference,DECODE(paymentsconsolidated.cmb_dbcr_flag, 'C', 'PS.credit', 'D', 'PS.debit', 'M', 'PS.mixed', 'NA') AScmb_dbcr_indicator, cmb_batchname, cmb_totalnumitems,exchange_rate, exchangeratetbd_flag, rateortraderesult, rate_traded_by, rtgs.entered_amount_flag,exchange_rate_contractid, rtgs.special_instructions, debit_subaccount_num, bene_subaccount_num,orderof_name, payment_details, invoicenum, bene_bank_account_number,Batchfcn_In, Batchfcn_Out,batchcomment,ue_entrydesc,samedayach,EXTRACT_DATE,ENTRYCLASS,BANKCODE,TOTALNUMDEBIT,TOTALNUMCREDIT,DESTCOUNTRYCODE, OFFSETBANKCODE,OFFSETACCOUNTNUM,REMITTANCE_INFO, RFP_MSG_ID FROM PaymentsCommon JOIN PaymentsConsolidated ON ( PaymentsCommon.TNum = PaymentsConsolidated.TNum ) LEFT JOIN RTGSON ( PaymentsCommon.TNum = RTGS.TNum ) LEFT JOIN ACHIntHeader AIH ON ( PaymentsCommon.TNum = AIH.TNum )LEFT JOIN ACHUSHEADER AH ON ( PaymentsCommon.TNum = AH.TNum )LEFT JOIN RTP ON ( PaymentsCommon.TNum = RTP.TNum )WHERE PAYMENTSCOMMON.USERGROUP = 'BANK06'AND PAYMENTSCOMMON.value_date >= (select sysdate - paydaysavail from rtgsusergroup where usergroup='BANK06')AND PAYMENTSCOMMON.HIDDENFLAG IN ('0', '2')AND PAYMENTSCOMMON.STATUS NOT IN ('MF')AND PAYMENTSCOMMON.TYPE != 'TRANSFER' ) payments, (SELECTusergroup,UserID,Account_or_Company,EntryMethod,TypeCode,FunctionCode,ProductCode,0 AS ACTIVATE_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'APPROVE_ACTION') APPROVE_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'APPROVEOWN') APPROVEOWN,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'DELETE_ACTION') DELETE_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'GETRATE_ACTION') GETRATE_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'INSERT_ACTION') INSERT_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'MAKEINST_ACTION') MAKEINST_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'MAKETMPL_ACTION') MAKETMPL_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'MODIFY_ACTION') MODIFY_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'REJECT_ACTION') REJECT_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'RELEASE_ACTION') RELEASE_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'RELEASEOWN') RELEASEOWN,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'REPAIR_ACTION') REPAIR_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'SELECT_ACTION') SELECT_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'SPAYMT_ACTION') SPAYMT_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'UNAPPROV_ACTION') UNAPPROV_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'UNRELEASE_ACTION') UNRELEASE_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'RESTORE_ACTION') RESTORE_ACTION,get_xref_action(usergroup,userid,productcode,functioncode,typecode,entrymethod,account_or_company,dataentfilterattribute,'REVERSE_ACTION') REVERSE_ACTION,DATAENTFILTERATTRIBUTE FROM AcctOrCompByActionXRefInstWHERE UserGroup = 'BANK06' AND UserID = 'USER01' AND TYPECODE NOT IN ('FILE', 'PASSTHRU','BEFTPASS')UNION ALL select usergroup, 'USER01' as UserID, null as Account_or_Company, '3' as EntryMethod, TypeCode, FunctionCode, ProductCode, 0 AS ACTIVATE_ACTION, DECODE (INSTR(actions, 'APPROVE'), 0, 0, 1) as APPROVE_ACTION, DECODE (value1, 'true', 1, 0) as APPROVEOWN, DECODE (INSTR(actions, 'DELETE'), 0, 0, 1) as DELETE_ACTION, 0 as GETRATE_ACTION, 0 as INSERT_ACTION, 0 as MAKEINST_ACTION, 0 as MAKETMPL_ACTION, 0 as MODIFY_ACTION, DECODE (INSTR(actions, 'APPROVE'), 0, 0, 1) as REJECT_ACTION, 0 as RELEASE_ACTION, 0 as RELEASEOWN, 0 as REPAIR_ACTION, DECODE (INSTR(actions, 'SELECT'), 0, 0, 1) as SELECT_ACTION, 0 as SPAYMT_ACTION, DECODE (INSTR(actions, 'UNAPPROV'), 0, 0, 1) as UNAPPROV_ACTION, 0 as UNRELEASE_ACTION, 0 AS RESTORE_ACTION, 0 as REVERSE_ACTION, null as DATAENTFILTERATTRIBUTE from(select e.usergroup, e.productcode, e.functioncode, e.typecode, r.value1, listagg(e.actionmode, ',') within group (order by e.actionmode) actionsfrom entitlements e left outer join restrictions ron e.productcode = r.productcodeand e.functioncode = r.functioncodeand e.typecode = r.typecodeand e.entrymethod = r.entrymethodand e.roleid= r.roleidand e.usergroup = r.usergroupand r.restrictiontype = 'ApproveOwn'where e.usergroup = 'BANK06' and e.roleid in (select roleid from userroles where userid = 'USER01' and usergroup = e.usergroup)and e.productcode = 'RTGS' and e.functioncode = 'INST' and e.typecode = 'FILE'group by e.usergroup, e.productcode, e.functioncode, e.typecode, r.value1) where rownum = 1 UNION ALL select usergroup, 'USER01' as UserID, '*' as Account_or_Company, '3' as EntryMethod, TypeCode, FunctionCode, ProductCode, 0 AS ACTIVATE_ACTION, DECODE (INSTR(actions, 'APPROVE'), 0, 0, 1) as APPROVE_ACTION, DECODE (value1, 'true', 1, 0) as APPROVEOWN, DECODE (INSTR(actions, 'DELETE'), 0, 0, 1) as DELETE_ACTION, 0 as GETRATE_ACTION, 0 as INSERT_ACTION, 0 as MAKEINST_ACTION, 0 as MAKETMPL_ACTION, 0 as MODIFY_ACTION, 0 as REJECT_ACTION, 0 as RELEASE_ACTION, 0 as RELEASEOWN, 0 as REPAIR_ACTION, DECODE (INSTR(actions, 'SELECT'), 0, 0, 1) as SELECT_ACTION, 0 as SPAYMT_ACTION, DECODE (INSTR(actions, 'UNAPPROV'), 0, 0, 1) as UNAPPROV_ACTION, 0 as UNRELEASE_ACTION, 0 AS RESTORE_ACTION, 0 as REVERSE_ACTION, null as DATAENTFILTERATTIBUTE from(select e.usergroup, e.productcode, e.functioncode, e.typecode, r.value1, listagg(e.actionmode, ',') within group (order by e.actionmode) actionsfrom entitlements e left outer join restrictions ron e.productcode = r.productcodeand e.functioncode = r.functioncodeand e.typecode = r.typecodeand e.entrymethod = r.entrymethodand e.roleid= r.roleidand e.usergroup = r.usergroupand r.restrictiontype = 'ApproveOwn'where e.usergroup = 'BANK06' and e.roleid in (select roleid from userroles where userid = 'USER01' and usergroup = e.usergroup)and e.productcode = 'USACH' and e.functioncode = 'BATCH' and e.typecode = 'PASSTHRU'group by e.usergroup, e.productcode, e.functioncode, e.typecode, r.value1) where rownum = 1 UNION ALL select usergroup, 'USER01' as UserID, '*' as Account_or_Company, '3' as EntryMethod, TypeCode, FunctionCode, ProductCode, 0 AS ACTIVATE_ACTION, DECODE (INSTR(actions, 'APPROVE'), 0, 0, 1) as APPROVE_ACTION, DECODE (value1, 'true', 1, 0) as APPROVEOWN, DECODE (INSTR(actions, 'DELETE'), 0, 0, 1) as DELETE_ACTION, 0 as GETRATE_ACTION, 0 as INSERT_ACTION, 0 as MAKEINST_ACTION, 0 as MAKETMPL_ACTION, 0 as MODIFY_ACTION, 0 as REJECT_ACTION, 0 as RELEASE_ACTION, 0 as RELEASEOWN, 0 as REPAIR_ACTION, DECODE (INSTR(actions, 'SELECT'), 0, 0, 1) as SELECT_ACTION, 0 as SPAYMT_ACTION, DECODE (INSTR(actions, 'UNAPPROV'), 0, 0, 1) as UNAPPROV_ACTION,0 as UNRELEASE_ACTION, 0 AS RESTORE_ACTION,0 as REVERSE_ACTION,null as DATAENTFILTERATTRIBUTE from (select e.usergroup, e.productcode, e.functioncode, e.typecode, r.value1, listagg(e.actionmode, ',') within group (order by e.actionmode) actionsfrom entitlements e left outer join restrictions ron e.productcode = r.productcodeand e.functioncode = r.functioncodeand e.typecode = r.typecode and e.entrymethod = r.entrymethod and e.roleid= r.roleid and e.usergroup = r.usergroupand r.restrictiontype = 'ApproveOwn'where e.usergroup = 'BANK06' and e.roleid in (select roleid from userroles where userid = 'USER01' and usergroup = e.usergroup)and e.productcode = 'USACH' and e.functioncode = 'BATCH' and e.typecode = 'BEFTPASS'group by e.usergroup, e.productcode, e.functioncode, e.typecode, r.value1)where rownum = 1) AcctOrCompByActionXRefWHERE payments.USERGROUP = 'BANK06'AND payments.Product = AcctOrCompByActionXRef.ProductCodeAND payments.Function = AcctOrCompByActionXRef.FunctionCodeAND payments.TYPE = AcctOrCompByActionXRef.TypeCodeAND AcctOrCompByActionXRef.EntryMethod IN (TO_CHAR(payments.EntryMethod))AND (((acctorcompbyactionxref.DATAENTFILTERATTRIBUTE is null OR acctorcompbyactionxref.DATAENTFILTERATTRIBUTE = 'BankAccount') ANDAcctOrCompByActionXRef.Account_or_Company IN (Payments.ACCOUNTFILTER, TO_CHAR (Payments.ClientComp_GroupNum), TO_CHAR (Payments.CheckApp_GroupNum)))OR ( acctorcompbyactionxref.account_or_company = '*'and(acctorcompbyactionxref.DATAENTFILTERATTRIBUTE is null OR acctorcompbyactionxref.DATAENTFILTERATTRIBUTE = 'BankAccount')AND CheckFineGrainedAccess_Inst('BANK06', 'USER01', payments.product, payments.function, payments.type, payments.entrymethod,CASE WHEN payments.product IN ('RTGS', 'RTP') THEN 'BankAccount' ELSE NULL END,CASE WHEN payments.product IN ('RTGS', 'RTP') THEN payments.accountfilter WHEN payments.product = 'USACH' THEN TO_CHAR(payments.clientcomp_groupnum) ELSE TO_CHAR(payments.checkapp_groupnum) END, 'false') = 0)OR payments.TYPE in ('FILE'))AND (payments.Type not in ('TRANSFER','LOANDRAW','LOANPAY','PASSTHRU')OR CheckFineGrainedAccess_Inst('BANK06', 'USER01', payments.product, payments.function, payments.type, payments.entrymethod, 'BankAccountCR', payments.bene_accountentitlement, 'true') = 1OREXISTS (select 1 from ACCTORCOMPBYACTIONXREFINST XREF, PaymentsCommon PC where payments.Type = 'PASSTHRU' and XREF.productcode = PC.PRODUCT and XREF.typecode = 'PASSTHRU' and XREF.functioncode = PC.FUNCTION and XREF.entrymethod = TO_CHAR(PC.ENTRYMETHOD)and XREF.usergroup = 'BANK06' and XREF.userid = 'USER01' and XREF.account_or_company in ( TO_CHAR(PC.CLIENTCOMP_GROUPNUM) ,'*') and PC.SURROGATETNUM is not null and PC.SURROGATETNUM = payments.tnum ))AND ( payments.subtype IS NULL OR payments.subtype != 'CAEFT'OR ( EXISTS (SELECT 1 FROM DATAENTATTRVALUES, USERROLES WHERE (DataEntAttrValues.UserGroup= 'BANK06' AND userroles.usergroup= 'BANK06' AND UserRoles.UserId = 'USER01' AND DataEntAttrValues.RoleId= UserRoles.RoleId AND DataEntAttrValues.DataEntFilterAttribute = 'ACHAccount' AND DataEntAttrValues.TypeCode=payments.Type AND DataEntAttrValues.ProductCode =payments.Product AND ( DataEntAttrValues.AttrValue = '_ALL_' OR DataEntAttrValues.AttrValue= payments.OFFSETBANKCODE||'-'||payments.OFFSETACCOUNTNUM) ) ) ) )AND ( payments.TEMPLATECODEENTITLEMENT IS NULLOR ( EXISTS (SELECT 1 FROM DATAENTATTRVALUES, USERROLES WHERE (DataEntAttrValues.UserGroup= 'BANK06' AND userroles.usergroup= 'BANK06' AND UserRoles.UserId = 'USER01' AND DataEntAttrValues.RoleId= UserRoles.RoleId AND DataEntAttrValues.DataEntFilterAttribute =DECODE(payments.PRODUCT,'RTGS','RTGSTemplateCode','USACH','ACHTemplateCode','RTPTemplateCode') AND DataEntAttrValues.TypeCode=payments.Type AND DataEntAttrValues.ProductCode =payments.Product AND ( DataEntAttrValues.AttrValue = '_ALL_' OR DataEntAttrValues.AttrValue= payments.TEMPLATECODEENTITLEMENT) ) ) ) ))