Query To Find Dependent Objects Of An Invalid Object
SELECT owner, object_type, object_name FROM dba_objects WHERE status = ‘INVALID’ AND object_name IN (SELECT referenced_name FROM dba_dependencies WHERE name = ‘<INVALID_OBJECT_NAME>’);
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 MoreGL Batches Query
SELECT jb.NAME, gh.period_name, gh.je_source, gh.je_category, gh.currency_code, gc.concatenated_segments, gl.entered_cr, gl.entered_dr, gl.accounted_cr, gl.accounted_dr FROM gl_je_batches jb, gl_je_headers gh, gl_je_lines gl, gl_code_combinations_kfv gc WHERE jb.je_batch_id = gh.je_batch_id AND gh.je_header_id = gl.je_header_id AND gl.code_combination_id…
Read MoreAPI to Assign Collector for the Customers
create or replace PACKAGE BODY XX_Ar_Collector_Assign_Pkg AS PROCEDURE update_collectors(errbuf OUT VARCHAR2, retcode OUT NUMBER, p_oracle_user_id VARCHAR2) IS l_oracle_user_id NUMBER; l_update_rowcount NUMBER; v_customer_profile_rec_type hz_customer_profile_v2pub.customer_profile_rec_type; v_latest_ver_num NUMBER; x_return_status VARCHAR2 (10); x_msg_count NUMBER;…
Read MoreiStore Setup and Customization Tips
ISTORE SETUP CATALOG CREATION Go to the below responsibility:- iStore Administrator 1. Navigate to Catalog Tab to see Hierarchy. 2. Click Section image in Accessories Section which is root section.…
Read MoreSupplier with Bank and Tax details query
select (SELECT hou.NAME FROM apps.hr_operating_units hou WHERE 1 = 1 AND hou.organization_id = aps.org_id) ou_name, (SELECT paym.payment_method_code FROM APPS.iby_ext_party_pmt_mthds paym WHERE IEPA.ext_payee_id = paym.ext_pmt_party_id and aps.vendor_site_id = iepa.supplier_site_id –AND ass.supplier_site_id…
Read MoreAP Invoice posted register query
select aia.INVOICE_NUM ,aba.batch_name ,aia.INVOICE_AMOUNT ,aia.PAY_GROUP_LOOKUP_CODE INv_group ,aia.INVOICE_CURRENCY_CODE ,aia.CREATION_DATE ,To_char(aia.creation_date,’MONTH’) InvCreation_Month ,aia.INVOICE_DATE ,aia.SOURCE ,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE ,PAYMENT_METHOD_CODE INVOICE_PAY_METHOD ,aia.amount_paid amount_paid ,aia.description ,aia.doc_sequence_value VOUCHER_NUM ,(select max(aila.ACCOUNTING_DATE) from apps.ap_invoice_lines_all aila where aila.invoice_id = aia.invoice_id…
Read MoreAPI to update Customer Bill to Role
set serveroutput on; DECLARE l_contact_point_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE; l_role_id NUMBER := 0; l_cust_acct_id NUMBER := 0; l_obj_num NUMBER := 0; p_party_id HZ_PARTIES.party_id % TYPE; x_msg_count NUMBER; x_msg_data VARCHAR2(2000) := NULL; x_return_status VARCHAR2(1000)…
Read MoreAPI to Update Customer Name
SET SERVEROUTPUT ON; SET DEFINE OFF; DECLARE l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE; l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE; –l_party_obj_version NUMBER; x_profile_id NUMBER; x_return_status VARCHAR2(1); x_msg_count NUMBER; x_msg_data VARCHAR2(4000); CURSOR C1 IS SELECT ICNU.CUST_NUMBER, ICNU.CURR_NAME, ICNU.CORRECT_NAME, ICNU.STATUS, HCA.PARTY_ID,…
Read More