Skip to main content

Posts

Showing posts from September, 2024

Identify the line number where an exception was thrown

To identify the line number where an exception was thrown in PL/SQL, you can use the  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE  function. This function provides a backtrace that includes the line number(s) in the PL/SQL block, procedure, or function where the error occurred, making it easier to pinpoint the location of the problem. EXCEPTION WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);  END; Output: ORA-06502: PL/SQL: numeric or value error: character string buffer too small The above Example will throw the Erro r "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" but we cannot find which line causing the issue without debugging the entire program. Here We can use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify the line number of the code which causing the issue. EXCEPTION WHEN OTHERS THEN...

Applying AR Credit Memo to AR Invoice using AR_CM_API_PUB.APPLY_ON_ACCOUNT

  In Oracle Apps R12, the process of applying on-account credits to customer invoices can be complex. Oracle provides the AR_CM_API_PUB.APPLY_ON_ACCOUNT API to facilitate this process. This API is particularly useful when there is a need for programmatic application of credit memos (CM) to invoices. API: AR_CM_API_PUB.APPLY_ON_ACCOUNT API Staging Table Design: CREATE TABLE XX_AR_CM_STG (    TRANSACTION_ID      NUMBER,     OPERATING_UNIT    VARCHAR2(240),    ORG_ID    NUMBER,     CM_NUMBER           VARCHAR2(50),  -- Credit Memo Number    INVOICE_NUMBER      VARCHAR2(50),  -- Invoice Number    APPLIED_AMOUNT      NUMBER,  -- Amount to be applied    APPLIED_DATE    DATE,    CT_REFERENCE    VARCHAR2(240),    CUSTOMER_NUMBER     VARC...