SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
(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) ) ) ) ))
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear