Tuesday, March 14, 2017

Payment File XML SQL

 select * from iby_trxn_documents where payment_instruction_id=<>

Monday, February 20, 2017

SQL Functions for Hex to Character and Character to Hex

SELECT ascii(UTL_I18N.RAW_TO_CHAR ('00', 'AL32UTF8')),ascii(UTL_I18N.RAW_TO_CHAR ('20', 'AL32UTF8')),utl_raw.cast_to_raw(' ') FROM DUAL;

Monday, January 23, 2017

SQL for Active Banks and Branches in EBS R12

--SQL for Active Banks

SELECT BankOrgProfile.*

FROM HZ_ORGANIZATION_PROFILES BankOrgProfile, HZ_CODE_ASSIGNMENTS BankCA

WHERE 1 = 1

AND SYSDATE BETWEEN TRUNC (BankOrgProfile.effective_start_date)

AND NVL (TRUNC (BankOrgProfile.effective_end_date),

SYSDATE + 1)

AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'

AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')

AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'

AND (BankCA.STATUS = 'A' OR BankCA.STATUS IS NULL)

AND BankCA.OWNER_TABLE_ID = BankOrgProfile.PARTY_ID

--SQL for Active Bank Branches

select count(1) from CE_BANK_BRANCHES_V where nvl(END_DATE,sysdate)>=sysdate