API Delete Orphan Events in AR invoices not in XLA in R12
api_events api_events This is useful script while doing period close on every month basis. Some time there will be no relation will generate between AR invoice and Subledger . In…
Read MorePayables VMD Data Extract in R12
vmdvendormetadata_extract_query This reusable component very useful in pulling payables vendor metadata that will have all the the information vendor payments bank details and along with VAT details
Read MoreAR invoice Number change Script for Brazil Country in R12
invoice-numberchange-script-for-brazil This is useful script that will help for Brazil Country. This script will find the invoice number of Brazil localization tables and modify the invoice number as per standard…
Read MoreSetting NLS language in SQL developer
Sometimes we will observe HR Operating units table will not show any data in database. For this we need to do the set up in SQL Developer. Preferences –> NLS…
Read MoreCustomer Bill_To Address in Oracle Apps R12
Please find below query to get customer Bill_To Address SELECT DECODE (hcsua.site_use_code, ‘BILL_TO’, hl.address1 || ‘, ‘ || hl.address2 || ‘, ‘ || hl.city || ‘, ‘ || hl.state…
Read MoreSpecial characters validation
For any field if you want throw any exception when entered any special characters. We need to use following pattern to find. Pattern p = Pattern.compile(“[^a-zA-Z0-9\\s]”);
Read MoreHow to Delete AP Invoice Data from the Oracle Apps EBS R12.
API “AP_AI_TABLE_HANDLER_PKG.DELETE_ROW” can be used to delete AP Invoice Data. This Script can be used when invoice accounting is not done. DECLARE CURSOR CUR1 IS SELECT AIA.ROWID,AIA.* FROM AP_INVOICES_ALL…
Read MoreHow to Delete ‘INCOMPLETE’ and ‘APPROVED’ Purchase Order (PO) using API in Oracle Apps EBS R12 – API to delete purchase Orders
Below Pseudocode, DECLARE L_RESULT BOOLEAN; L_PO_HEADER_ID NUMBER; — := <PO_HEADER_ID> ; L_TYPE_LOOKUP_CODE VARCHAR2(20); — := <LOOKUP_CODE> ; L_VALIDATION VARCHAR2(10) := ‘N’; — := <VALIDATION_FLAG> ; CURSOR CUR_PO_DEL IS SELECT…
Read MoreHow to Convert Comma Separated Values into Table using ‘REGEXP_SUBSTR’
WITH csv AS (SELECT ‘AA,BB,CC,D33D,EE,FFDD.MM,GG’ AS csvdata FROM DUAL) SELECT REGEXP_SUBSTR (csv.csvdata, ‘[^,]+’, 1, LEVEL) pivot_char FROM DUAL, csv CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+’, 1, LEVEL) IS NOT NULL;
Read MoreConvert the Amount into the Word using Function in Oracle Apps R12
Create or Replace Function AMT_IN_WORDS ( P_TOT_AMT float, P_TRANSACTION_CURR Varchar2) return varchar2 IS InvalidNumberFormatModel EXCEPTION; PRAGMA EXCEPTION_INIT(InvalidNumberFormatModel,-1481); InvalidNumber EXCEPTION; PRAGMA EXCEPTION_INIT(InvalidNumber,-1722); TYPE GroupTableType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;…
Read More