Infolinks

Wednesday 10 February 2016

Query for get the Concurrent Program Trace file Path location based on request id

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    WHERE 1=1
    AND req.request_id = &request --Request ID
    AND req.oracle_process_id=proc.spid(+)
    AND proc.addr = ses.paddr(+)
    AND dest.NAME='user_dump_dest'
    AND dbnm.NAME='db_name'
    AND req.concurrent_program_id = prog.concurrent_program_id
    AND req.program_application_id = prog.application_id
    AND prog.application_id = execname.application_id
    AND prog.executable_id=execname.executable_id

Friday 29 January 2016

Password Retrival in Oracle Apps User

Password Retrival in Oracle Apps User

--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = '&USER_NAME';

AR Invoice

AR Invoice (Auto Invoice / Customer Invoice / AR Transaction) upload through interface step by step


We can use Auto Invoice tool to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos etc in Oracle Receivables. For this, we need to develop a custom program that will be used to transfer transaction data from an external system into the AutoInvoice interface tables.
There are three interface tables in Oracle Receivables for AutoInvoice:
  1. RA_INTERFACE_LINES
  2. RA_INTERFACE_DISTRIBUTIONS
  3. RA_INTERFACE_SALESCREDITS
Now, if we initiate the AutoInvoice then it will transfer all the transactional data from the above three interface tables into the following Receivables tables:
  • RA_BATCHES_ALL
  • RA_CUSTOMER_TRX _ALL
  • RA_CUSTOMER_TRX_LINES _ALL
  • RA_CUST_TRX_LINE_GL_DIST_ALL
  • RA_CUST_TRX_LINE_SALESREPS_ALL
  • AR_PAYMENT_SCHEDULES_ALL
  • AR_RECEIVABLE_APPLICATIONS_ALL
  • AR_ADJUSTMENTS_ALL
AutoInvoice Exception Handling:
Records that fail validation are called ‘Exceptions’
  • Exceptions stay in Interface Tables which isRA_INTERFACE_ERRORS_ALL.
Note: - Tables mainly used are highlighted in Bold

STEPS:-
1.       Create  the staging table as
CREATE TABLE SMZPL_AR_INVOICE_STG
(
  OU_NAME                  VARCHAR2(250 ),
  ORG_ID                   NUMBER,
  TRANSACTION_SOURCE       VARCHAR2(250 ),
  INVOICE_CLASS            VARCHAR2(250 ),
  TRANSACTIONS_TYPE        VARCHAR2(250 ),
  TRX_DATE                 DATE,
  GL_DATE                  DATE,
  CURRENCY                 VARCHAR2(10 ),
  EXCHANGE_RATE            NUMBER,
  REFERENCE                VARCHAR2(250 ), -- Used to store invoice no. in this example
  CUSTOMER_NAME            VARCHAR2(250 ),
  CUSTOMER_SITE            VARCHAR2(100 ),
  DISTRIBUTION_ACCOUNT     VARCHAR2(30 ),
  DISTRIBUTION_ACCOUNT_ID  NUMBER,
  INVOICE_AMOUNT           NUMBER(12,2),
  LINE_AMOUNT              NUMBER(12,2),
  TERMS                    VARCHAR2(250 ),
  QUANTITY                 NUMBER,
  LINE_ITEM                VARCHAR2(20 ),
  INVOICE_NUM              VARCHAR2(40 ),
  L_VERIFY_FLAG            VARCHAR2()     DEFAULT NULL,
  L_ERROR_MESSAGE          VARCHAR2(30 )    DEFAULT NULL
); 
2.       Import the data from excel sheet into staging table through TOAD.  


3.       Create the procedure
CREATE OR REPLACE PROCEDURE APPS.SMZPL_AR_INVOICE_API
AS
    L_ORG_ID HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
    L_SOB_ID HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE;
    L_CUST_TRX_TYPE_ID RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID%TYPE;
    L_GL_ID_REV RA_CUST_TRX_TYPES_ALL.GL_ID_REV%TYPE;
    L_CUST_TRX_TYPE_NAME RA_CUST_TRX_TYPES_ALL.NAME%TYPE;
    L_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
    L_TERM_ID RA_TERMS_TL.TERM_ID%TYPE;
    L_TERM_NAME RA_TERMS_TL.NAME%TYPE;
    L_ADDRESS_ID HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID%TYPE;
    L_CUSTOMER_ID HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
    L_VERIFY_FLAG CHAR(1):='Y';
    L_ERROR_MESSAGE VARCHAR2(2500);
    L_GL_CODE_ID VARCHAR2(100);

    CURSOR CUR_AR IS
     SELECT a.*,a.rowid rd FROM
    SMZPL_AR_INVOICE_STG a
    WHERE NVL(L_VERIFY_FLAG,'N')='N';   

 BEGIN

  FOR AR_INV IN CUR_AR LOOP

    BEGIN
    SELECT ORGANIZATION_ID, SET_OF_BOOKS_ID
    INTO L_ORG_ID, L_SOB_ID
    FROM HR_OPERATING_UNITS
    WHERE trim(NAME) = trim(AR_INV.OU_NAME);
    EXCEPTION
    WHEN OTHERS THEN
    L_VERIFY_FLAG := 'N';
    L_ERROR_MESSAGE := 'INVALIDE OPERATING UNIT...';
   
    Update SMZPL_AR_INVOICE_STG
    Set    L_VERIFY_FLAG    = 'N',
           L_ERROR_MESSAGE  = 'INVALIDE OPERATING UNIT...'
    Where  rowid = AR_INV.rd;

    END;

    BEGIN
    SELECT CUST_TRX_TYPE_ID,NAME,GL_ID_REV
    INTO L_CUST_TRX_TYPE_ID,L_CUST_TRX_TYPE_NAME, L_GL_ID_REV
    FROM RA_CUST_TRX_TYPES_ALL
    WHERE SET_OF_BOOKS_ID = L_SOB_ID
    AND ORG_ID = L_ORG_ID
    AND trim(NAME) =trim(AR_INV.TRANSACTIONS_TYPE);
    EXCEPTION
    WHEN OTHERS THEN
    L_VERIFY_FLAG := 'N';
    L_ERROR_MESSAGE := 'INVALIDE INVOICE TYPE...';
   
    Update SMZPL_AR_INVOICE_STG
    Set    L_VERIFY_FLAG    = 'N',
           L_ERROR_MESSAGE  = 'INVALIDE INVOICE TYPE...'
    Where  rowid = AR_INV.rd;
   
    END;

  
    BEGIN
    SELECT CURRENCY_CODE
    INTO L_CURRENCY_CODE
    FROM FND_CURRENCIES
    WHERE trim(CURRENCY_CODE) =trim(AR_INV.CURRENCY);
    EXCEPTION
    WHEN OTHERS THEN
    L_VERIFY_FLAG := 'N';
    L_ERROR_MESSAGE := 'INVALIDE CURRENCY CODE...';
   
        Update SMZPL_AR_INVOICE_STG
    Set    L_VERIFY_FLAG    = 'N',
           L_ERROR_MESSAGE  = 'INVALIDE CURRENCY CODE...'
    Where  rowid = AR_INV.rd;
   
    END;


   BEGIN
    SELECT TERM_ID,NAME
    INTO L_TERM_ID,L_TERM_NAME
    FROM RA_TERMS_TL
    WHERE UPPER(NAME) = UPPER(AR_INV.TERMS);
    EXCEPTION
    WHEN OTHERS THEN
    L_VERIFY_FLAG := 'N';
    L_ERROR_MESSAGE := 'INVALIDE TERMS NAME...';
   
            Update SMZPL_AR_INVOICE_STG
    Set    L_VERIFY_FLAG    = 'N',
           L_ERROR_MESSAGE  = 'INVALIDE TERMS NAME...'
    Where  rowid = AR_INV.rd;
    END;


    BEGIN
       
    SELECT DISTINCTHCAS.CUST_ACCT_SITE_ID,HCA.CUST_ACCOUNT_ID--HPS.LOCATION_ID,HP.PARTY_ID
    INTO L_ADDRESS_ID,L_CUSTOMER_ID
    FROM HZ_PARTIES HP
    ,HZ_PARTY_SITES HPS
    ,HZ_CUST_ACCOUNTS HCA
    ,HZ_CUST_ACCT_SITES_ALL HCAS
    ,HZ_CUST_SITE_USES_ALL HCSU
    ,HZ_LOCATIONS          HL
    WHERE HCA.PARTY_ID = HP.PARTY_ID
    AND HP.PARTY_ID = HPS.PARTY_ID
    AND HPS.PARTY_SITE_ID=HCAS.PARTY_SITE_ID
    AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
    AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
    AND HCSU.SITE_USE_CODE = 'BILL_TO'
    AND HL.LOCATION_ID  =HPS.LOCATION_ID
    AND UPPER (LTRIM (RTRIM(HP.PARTY_NAME)))=UPPER(LTRIM(RTRIM(AR_INV.CUSTOMER_NAME)))
    AND HCAS.ORG_ID =L_ORG_ID;
    EXCEPTION
    WHEN OTHERS THEN
    L_VERIFY_FLAG := 'N';
    L_ERROR_MESSAGE := 'INVALIDE CUSTOMER NAME...';
   
    Update SMZPL_AR_INVOICE_STG
    Set    L_VERIFY_FLAG    = 'N',
           L_ERROR_MESSAGE  = 'INVALIDE CUSTOMER NAME...'
    Where  rowid = AR_INV.rd;
   
    END;
  
 
    /**
    BEGIN
   
    SELECT CODE_COMBINATION_ID INTO L_GL_CODE_ID
    FROM GL_CODE_COMBINATIONS_KFV
    WHERE CONCATENATED_SEGMENTS=AR_INV.DISTRIBUTION_ACCOUNT;
    EXCEPTION
    WHEN OTHERS THEN
    L_VERIFY_FLAG := 'N';
    L_ERROR_MESSAGE := 'INVALIDE ACCOUNT ID';
    END;
    **/
    
   
   IF L_VERIFY_FLAG<>'N' THEN
    BEGIN
   
        INSERT INTO RA_INTERFACE_LINES_ALL(
        INTERFACE_LINE_ID,
        BATCH_SOURCE_NAME,--ra_batch_sources_all 
        SET_OF_BOOKS_ID,
        LINE_TYPE,
        CUST_TRX_TYPE_ID,--1003
        CUST_TRX_TYPE_NAME,
        TRX_NUMBER,
        TRX_DATE,
        GL_DATE,
        CURRENCY_CODE,
        TERM_ID,
        --TERM_NAME,
        ORIG_SYSTEM_BILL_CUSTOMER_ID,
        ORIG_SYSTEM_BILL_CUSTOMER_REF,
        ORIG_SYSTEM_BILL_ADDRESS_ID,
        ORIG_SYSTEM_BILL_ADDRESS_REF,
        ORIG_SYSTEM_SOLD_CUSTOMER_ID,
        QUANTITY,
        AMOUNT,
        DESCRIPTION,
        CONVERSION_TYPE,
        --CONVERSION_RATE,
        INTERFACE_LINE_CONTEXT,
       -- INTERFACE_LINE_ATTRIBUTE1,
        ORG_ID,
        INTERFACE_LINE_ATTRIBUTE1,
        INTERFACE_LINE_ATTRIBUTE2,
        INTERFACE_LINE_ATTRIBUTE3
        )
        VALUES
        (
        RA_CUSTOMER_TRX_LINES_S.NEXTVAL,
        'OPENING BALANCE UPLOAD',
        L_SOB_ID,
        'LINE',
        L_CUST_TRX_TYPE_ID,
        L_CUST_TRX_TYPE_NAME,
        AR_INV.REFERENCE,
        AR_INV.GL_DATE,
        AR_INV.GL_DATE,
        L_CURRENCY_CODE,
        AR_INV.TERMS,
        --L_TERM_NAME,
        L_CUSTOMER_ID,
        L_CUSTOMER_ID,
        L_ADDRESS_ID,
        L_ADDRESS_ID,
        L_CUSTOMER_ID,
        1,
        AR_INV.INVOICE_AMOUNT,
        'AR OPENING DATA',
        'Corporate',
        --1,
        'AFRICA_INVOICES',
        L_ORG_ID,
        AR_INV.REFERENCE,
        1            ,
        AR_INV.TRX_DATE
        );

        INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
        (
         INTERFACE_LINE_ID
        ,ACCOUNT_CLASS
        ,AMOUNT
        ,CODE_COMBINATION_ID
        ,PERCENT
        --,INTERFACE_LINE_CONTEXT
        --,INTERFACE_LINE_ATTRIBUTE1
        ,ORG_ID
        )
        VALUES
        (
        RA_CUSTOMER_TRX_LINES_S.CURRVAL,
        'REV'
        ,AR_INV.INVOICE_AMOUNT
        ,L_GL_ID_REV
        ,100
        --,L_GL_ID_REV
       -- ,AR_INV.INVOICE_AMOUNT
        ,L_ORG_ID
        );
             
        Update SMZPL_AR_INVOICE_STG
        Set    L_VERIFY_FLAG    = 'Y'
        Where  rowid = AR_INV.rd;
       
    COMMIT;
    END;
   END IF; 
  END LOOP;  
END;

4.       Some important validations
Ø  Check for valid Operating Unit or ORG_ID. [ HR_OPERATING_UNITS ]
Ø  Check for valid Transaction Types or Invoice Types. [RA_CUST_TRX_TYPES_ALL ]
Ø  Check for valid Transaction Source or BATCH_SOURCE_NAME.[RA_BATCH_SOURCES_ALL ]
Ø  Check for valid Currency Code .[ FND_CURRENCIES ]
Ø  Check for valid Terms Name [ RA_TERMS_TL ]
Ø  Check for valid Customer Name and BILL_TO address. [AR_CUSTOMERS, HZ_PARTIES,HZ_PARTY_SITES, HZ_CUST_ACCOUNTS ,HZ_CUST_ACCT_SITES_ALL,HZ_CUST_SITE_USES_ALL ,HZ_LOCATIONS ]
Ø  Transaction Flex Fields at Line level are mandatory which uniquely identifies invoice lines.
Ø  Amount at Line level should match with Amount at Distribution level.
Ø  Check for valid items ( Item field is not mandatory ) [MTL_SYSTEM_ITEMS_B ]
Ø  Check for duplicate value in TRX_NUMBER i.e., two different transactions cannot have same Invoice No.

5.       Execute the above procedure to insert data from staging table into Interface tables i.e., from SMZPL_AR_INVOICE_STG intoRA_INTERFACE_LINES_ALLRA_INTERFACE_DISTRIBUTIONS_ALL. 

BEGIN

SMZPL_AR_INVOICE_API;

END;

6.       L_VERIFY_FLAG for all the rows which get inserted into interface table will be set to ‘Y’ and L_VERIFY_FLAG will be set to ‘N’ for non processed rows along with the ERROR_MESSAGE.
7.       If the records are populated properly into Interface tables then we need to Run the AutoInvoice Import Program.
Navigate to Receivables Responsibility ---> Interface ---> AutoInvoice