Find User password for Application in Oracle Apps

Find User password for Application in Oracle Apps from Backend:
===============================================

CREATE OR REPLACE PACKAGE aa_get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
 PROCEDURE check_login(p_user_name IN VARCHAR2,p_pwd IN VARCHAR2,x_status OUT VARCHAR2,x_msg OUT VARCHAR2);
END aa_get_pwd;
/

CREATE OR REPLACE PACKAGE BODY aa_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';
PROCEDURE check_login(p_user_name IN VARCHAR2,p_pwd IN VARCHAR2,x_status OUT VARCHAR2,x_msg OUT VARCHAR2)
IS
lc_user_name   VARCHAR2(200);
     lc_pwd            VARCHAR2(200);
    BEGIN
SELECT usr.user_name,
       aa_get_pwd.decrypt
          ((SELECT (SELECT aa_get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM apps.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
          INTO lc_user_name,lc_pwd
  FROM apps.fnd_user usr
 WHERE usr.user_name = p_user_name;
--
    IF (lc_pwd = p_pwd AND lc_user_name=p_user_name) THEN
        x_status := 'Success';
        x_msg := 'Login Successful';
    ELSE
        x_status := 'Fail';
        x_msg := 'Login Un Successful, Please check user name and password';  
    END IF;
--
EXCEPTION
WHEN OTHERS THEN
        x_status := 'Error';
        x_msg := SQLERRM;  
    END check_login;
END aa_get_pwd;
/


SELECT usr.user_name,
       aa_get_pwd.decrypt
          ((SELECT (SELECT aa_get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM apps.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 apps.fnd_user usr
 WHERE usr.user_name like '%User Name%' ;
;

PO to AP full Query in Oracle Apps

PO to AP full Query in Oracle Apps:
==========================

SELECT aia.invoice_num invoicenumber, aia.invoice_date, aia.gl_date,
       aida.amount, pha.segment1 ponumber, asa.vendor_name vendorname,
       glcc.segment2 account_number, glh.posted_date postedate,
       glh.period_name perioddate, asa.creation_date
  FROM gl.gl_je_headers glh,
       gl.gl_je_lines gll,
       gl.gl_import_references glir,
       xla.xla_ae_lines xll,
       xla.xla_ae_headers xlh,
       xla.xla_events xle,
       xla.xla_transaction_entities xlte,
       ap_invoices_all aia,
       ap_invoice_lines_all ail,
       ap_invoice_distributions_all aida,
       po_distributions_all pda,
       po_headers_all pha,
       ap_suppliers asa,
       gl_code_combinations_kfv glcc
 WHERE glh.je_header_id = gll.je_header_id
   AND gll.je_header_id = glir.je_header_id
   AND glh.je_header_id = glir.je_header_id                      
   AND gll.je_line_num = glir.je_line_num
   AND glir.gl_sl_link_table = xll.gl_sl_link_table
   AND glir.gl_sl_link_id = xll.gl_sl_link_id
   AND xll.application_id = xlh.application_id
   AND xll.ae_header_id = xlh.ae_header_id
   AND xlh.application_id = xle.application_id
   AND xlh.event_id = xle.event_id
   AND xle.application_id = xlte.application_id
   AND xle.entity_id = xlte.entity_id
   AND xlh.entity_id = xlte.entity_id                            
   AND xlte.source_id_int_1 = aia.invoice_id
   AND aia.invoice_id = ail.invoice_id
   AND ail.invoice_id = aida.invoice_id
   AND ail.line_number = aida.invoice_line_number
   AND aia.invoice_num = '12345'
   AND aida.po_distribution_id = pda.po_distribution_id(+)
   AND pda.po_header_id = pha.po_header_id(+)
   AND aia.vendor_id = asa.vendor_id
   AND aida.dist_code_combination_id = glcc.code_combination_id
   AND xll.code_combination_id = glcc.code_combination_id  ;

Item Category Details in Oracle Apps

Item Category Details Based on Item Wise:


SELECT msi.segment1 Item_Number,mc.segment1 Category
            FROM inv.mtl_system_items_b msi,
              inv.mtl_item_categories mic,
                 inv.mtl_categories_b mc,
                 inv.mtl_category_sets_b mcs,
                 inv.mtl_default_category_sets mdcs
           WHERE 1 = 1
             AND mic.inventory_item_id = msi.inventory_item_id
             AND mic.organization_id = msi.organization_id
             AND mic.category_id = mc.category_id
             AND mic.category_set_id = mcs.category_set_id
             AND mcs.category_set_id = mdcs.category_set_id
             AND msi.segment1='Item Number'
             AND msi.organization_id=Organization_id
             AND mdcs.functional_area_id = 1;

Find Concurrent Program last run Date in Oracle Apps

Concurrent Program Last run details:
========================

SELECT  to_char(MAX (fcr.REQUEST_DATE)
  FROM fnd_concurrent_requests          fcr,
       fnd_concurrent_programs_tl       fcpt
 WHERE     fcr.concurrent_program_id = fcpt.concurrent_program_id
       AND fcr.program_application_id = fcpt.application_id
    --   AND fcr.actual_start_date > SYSDATE - 1
       AND fcpt.user_concurrent_program_name LIKE '%Program Name%' ;