Credit Card Number (of different format) from specific column

Credit Card Number (of different format) from specific column:-
--------------------------------------------------------------------------

Following query can be used to get the Credit Card Number (of different format) from specific column.

Column would have credit card Number in the text date. Date is not in any fixed format. And Credit Card Number would also not in any specific Format. In the following query, we have considered few credit card formats. In can include other formats accordingly as your requirement.

This query is contributed with Chandra Kadali. I thank him for this posting.

SELECT jtf_note_id, creation_date, LANGUAGE, notes,
CASE
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'99999999999999999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'99999999999999999'
),
LENGTH ('99999999999999999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'999999999999999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'999999999999999'
),
LENGTH ('999999999999999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999 9999 9999 9999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999 9999 9999 9999'
),
LENGTH ('9999 9999 9999 9999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999 999999 99999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999 999999 99999'
),
LENGTH ('9999 999999 99999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999-9999-9999-9999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999-9999-9999-9999'
),
LENGTH ('9999-9999-9999-9999')
)
WHEN INSTR (TRANSLATE (UPPER (notes), '0123456789', '9999999999'),
'9999-999999-99999'
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
'0123456789',
'9999999999'
),
'9999-999999-99999'
),
LENGTH ('9999-999999-99999')
)
ELSE 'No Credit card Number'
END "Credit card Number"
FROM jtf_notes_tl
WHERE 1 = 1
AND (TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%999999999999999 %') ) -- 15 digit---
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999999999999999 %')
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999 9999 9999 9999 %')
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999 999999 99999 %')
--- below are different formats with '-' instead of ' ''
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999-9999-9999-9999 %')
OR TRANSLATE (UPPER (notes), '0123456789', '9999999999') LIKE ('%9999-999999-99999 %')

I hope the above information would be helpful to you.

Initialize Apps

Initialize Apps:-
----------------

To run or execute the Oracle API from the back-end, we need to Initialize the Apps first.

We have API to Initialize the Apps.

To initialize apps using the API, we need to pass few IN parameter values.

The following is the script prepared to initialize apps based on the
Application, User-name and Responsibility name given.

Example:-

DECLARE
   l_appl_id             NUMBER;
   l_appl_name           VARCHAR2 (100) := 'PA';
   l_user_id             NUMBER;
   l_user_name           VARCHAR2 (100) := 'OPERATIONS';
   l_responsibility_id   NUMBER;
   l_resp_name           VARCHAR2 (200)
                                       := 'Projects, Vision Operations (USA)';
BEGIN

-- To get the Application ID of given Application.
   SELECT application_id
     INTO l_appl_id
     FROM fnd_application
    WHERE application_short_name = l_appl_name;

-- To get the User ID information of  given user
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

-- To get the Resp ID information of the given responsibility.
   SELECT responsibility_id
     INTO l_responsibility_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

--Initialixze the Application to use the API. 
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
END;

I Hope the above script would help understanding about the Initializing the Apps API.