Find the Invalid Email Address using Oracle SQL and PL/SQL
Objective: To find the validity of the email address using Oracle Pl/sql and to find the invalid email address from a object like table using Oracle Sql. Using Pl/Sql: DECLARE…
Read MoreTo Find IP Address of the User who login into Apex Application
This query will help to sql, Plsql and Apex Developer to find the user IP Address. Normally we tracking only page id, username and time of the user for login…
Read MoreQuery to Check locks on a table
Select object_id, session_id, oracle_username, os_user_name, Process, locked_mode From sys.v_$locked_object; Select a.object_name, b.oracle_username From all_objects a, v$locked_object b Where a.object_id = b.object_id And a.object_name like ‘po%’;
Read MoreOracle R12 Customer TaxPayer ID Update API
/*Upload the customer data that needs to be updated in a temp table*/ CREATE TABLE scratch.RITM0669580 (account_number VARCHAR2(30), tax_payer_id VARCHAR2(20), tax_reg_num VARCHAR2(50), status VARCHAR2(1), message VARCHAR2(1000)); / set serveroutput on;…
Read MoreOracle R12 Customer Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR2 (200) := NULL; l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type; p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type; p_ext_payee_id_tab_type…
Read MoreOracle R12 Customer Site Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR2 (200) := NULL; l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type; p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type; p_ext_payee_id_tab_type…
Read MoreOracle R12 AP Invoice Extract With PO And CHECK Details
select aia.INVOICE_NUM ,aia.INVOICE_AMOUNT ,aia.CREATION_DATE,aia.INVOICE_DATE,aia.SOURCE,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE, (select aca.STATUS_LOOKUP_CODE from apps.ap_invoice_payments_all aipa, apps.ap_checks_all aca where aipa.check_id = aca.check_id and nvl(aipa.reversal_flag,’N’) <> ‘Y’ and aipa.invoice_id = aia.invoice_id and aipa.org_id = aia.org_id and rownum…
Read MoreOracle R12 Expense Type Master Based On Operating Unit
SELECT ( SELECT hou.name FROM apps.hr_operating_units hou WHERE hou.organization_id = aerpa.org_id ) operating_unit, aerpa.prompt expense_type, aerpa.flex_description gl_description, aerpa.flex_concactenated gl_code, aerpa.category_code, aerpa.end_date FROM apps.ap_expense_report_params_all aerpa WHERE aerpa.org_id IN ( 382, 402,…
Read MoreOracle R12 Item Master Extract With Other Essential Details
select (select category_concat_segs from apps.mtl_item_categories_v where inventory_item_id = msib.inventory_item_id and organization_id = msib.organization_id and category_set_name = ‘Tax Category Code Set’) tax_category, msib.segment1 item_number, msib.enabled_flag active_status, msib.description item_description, msib.attribute4 form_factor, gcck.segment5…
Read MoreOracle R12 GL Code Combination With Segment Description
select SEGMENT1 Company, apps.gl_flexfields_pkg.get_description_sql (chart_of_accounts_id,— chart of account id 1, —– Position of segment segment1 —- Segment value ) Company_desc, SEGMENT2 Location, apps.gl_flexfields_pkg.get_description_sql (chart_of_accounts_id,— chart of account id 2, —–…
Read More