Posts by Mohan G

GL 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 More

API 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 More

iStore 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 More

Supplier 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 More

AP 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 More

API 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 More

API 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