Friday, September 27, 2019

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

Thursday, May 5, 2016

SQL to fetch first day and last day of the month

SELECT LEVEL,
       TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, LEVEL - 1), 'MM'), 'DD-MON-RRRR'),
       TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, LEVEL - 1)), 'DD-MON-RRRR')
FROM DUAL
CONNECT BY LEVEL <= 100;

How to open wrapped Package Source Code in Oracle Apps

http://www.codecrete.net/UnwrapIt/

How to fetch current login user using sql

select SYS_CONTEXT('USERENV', 'SESSION_USER') from dual;