Query to get Purchase Order details in oracle apps

 SELECT ph.segment1 po_num,

       ph.creation_date,
       hou.name "Operating Unit",
       ppx.full_name "Buyer Name",
       (SELECT asp.segment1 FROM ap_suppliers asp WHERE asp.vendor_id = ph.vendor_id) supplier_number,
       ph.type_lookup_code "PO Type",
       plc.displayed_field "PO Status",
       ph.comments,
       pl.line_num,
       plt.order_type_lookup_code "Line Type",
       msi.segment1 "Item Code",
       pl.item_description,
       pl.unit_meas_lookup_code "UOM",
       pl.base_unit_price,
       pl.unit_price,
       pl.quantity,
       ood.organization_code "Shipment Org Code",
       ood.organization_name "Shipment Org Name",
       pv.vendor_name supplier,
       pvs.vendor_site_code,
       (pl.unit_price * pl.quantity) "Line Amount",
       prh.segment1         req_num,
       prh.type_lookup_code req_method,
       ppx1.full_name "Requisition requestor"
FROM   po_headers_all ph,
       po_lines_all pl,
       po_distributions_all pda,
       po_vendors pv,
       po_vendor_sites_all pvs,
       po_distributions_all pd,
       po_req_distributions_all prd,
       po_requisition_lines_all prl,
       po_requisition_headers_all prh,
       hr_operating_units hou,
       per_people_x ppx,
       mtl_system_items_b msi,
       po_line_types_b plt,
       org_organization_definitions ood,
       per_people_x ppx1,
       po_lookup_codes plc
WHERE  1 = 1
AND    ph.vendor_id = pv.vendor_id
AND    ph.org_id=:P_ORG_ID
AND    ph.po_header_id = pl.po_header_id
AND    ph.vendor_site_id = pvs.vendor_site_id
AND    ph.po_header_id = pd.po_header_id
AND    pl.po_line_id = pd.po_line_id
AND    pd.req_distribution_id = prd.distribution_id(+)
AND    prd.requisition_line_id = prl.requisition_line_id(+)
AND    prl.requisition_header_id = prh.requisition_header_id(+)
AND    hou.organization_id = ph.org_id
AND    ph.agent_id = ppx.person_id
AND    pda.po_header_id = ph.po_header_id
AND    pda.po_line_id = pl.po_line_id
AND    pl.line_type_id = plt.line_type_id
AND    ood.organization_id = pda.destination_organization_id
AND    ppx1.person_id(+) = prh.preparer_id
AND    pda.destination_organization_id = msi.organization_id(+)
AND    msi.inventory_item_id = Nvl (pl.item_id, msi.inventory_item_id)
AND    plc.lookup_type = 'DOCUMENT STATE'
AND    plc.lookup_code = ph.closed_code
AND    ph.segment1 = <po_number>;

Quick DMLs for Concurrent Programs

DELIVERY OPTIONS OF A CONCURRENT PROGRAM (EMAIL IDs)

SELECT concurrent_request_id,
       (SELECT tl.user_concurrent_program_name
        FROM   applsys.fnd_concurrent_programs_tl tl,
               applsys.fnd_concurrent_requests rq
        WHERE  tl.concurrent_program_id = rq.concurrent_program_id
               AND tl.LANGUAGE = Userenv('LANG')
               AND rq.request_id = concurrent_request_id) program,
       argument2                                          subject,
       argument3                                          MAIL_FROM,
       argument4                                          MAIL_To,
       argument5                                          MAIL_CC
FROM   fnd_conc_pp_actions
WHERE  action_type = 7
       AND concurrent_request_id = :p_request_id; 

UPDATE fnd_conc_pp_actions
SET    argument5 = 'abc@xxx.com'
WHERE  concurrent_request_id = 73616896
       AND action_type = 7;

COMMIT; 

DISABLE A SCHEDULE CONCURRENT PROGRAM 

UPDATE fnd_concurrent_requests
SET    phase_code = 'C',
       status_code = 'X'
WHERE  status_code IN ( 'Q', 'I' )
       AND requested_start_date > SYSDATE
       AND hold_flag = 'N'
       AND request_id = 73616896;

COMMIT; 

Check all privileges of User in Oracle

There are many views (VIEW in the SYS schema) that contain the privileges for a user:

• dba_sys_privs : System privileges granted to users and roles
• dba_tab_privs : All grants on objects in the database
• dba_role_privs : Roles granted to users and roles
• table_privileges : Grants on objects for which the user is the grantor, grantee, owner,
  or an enabled role or PUBLIC is the grantee

Below query shows all privileges, roles on objects present in Oracle Database to a particular user objects where

• TYPE : Type of Privilege or ROLE assigned to User.
• GRANTEE : User who have the permission of the object.
• PRIV : Define what privilege, role or object has
• TABNM : Name of the object on which privilege was granted.
• COLNM : Name of the column (if applicable).
• PRIV : Privilege on the object.
• AD : Whether the privilege was granted with the GRANT option. Equal to YES or NO.
• OWNER : Owner of the object

SELECT 'ROLE'       typ,
       grantee      grantee,
       granted_role priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_role_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'SYSTEM'     typ,
       grantee      grantee,
       privilege    priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_sys_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'TABLE'    typ,
       grantee    grantee,
       privilege  priv,
       grantable  ad,
       table_name tabnm,
       '--'       colnm,
       owner      owner
FROM   dba_tab_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'COLUMN'    typ,
       grantee     grantee,
       privilege   priv,
       grantable   ad,
       table_name  tabnm,
       column_name colnm,
       owner       owner
FROM   dba_col_privs
WHERE  grantee = :cp_grantee
ORDER  BY 5; 

Sample O/P:

Note:

--To see which table privileges are granted by you to other users.
SELECT * FROM   user_tab_privs_made;

--To see which table privileges are granted to you by other users
SELECT * FROM   user_tab_privs_recd;

--To see which column level privileges are granted by you to other users.
SELECT * FROM   user_col_privs_made

--To see which column level privileges are granted to you by other users
SELECT * FROM   user_col_privs_recd;

--To see which privileges are granted to roles
SELECT * FROM   user_role_privs; 

ora-12705 cannot access nls data files or invalid environment specified

Suppose we are getting below error while connectiong Database with SQL Developer :

ora-12705 cannot access nls data files or invalid environment specified

1. Open SQL Developer package content. Go to Applications, right click on SQL Developer and select "Show Package Contents".
2. Go to Contents/Resources/sqldeveloper/sqldeveloper/bin/
3. Open sqldeveloper.conf using a text editor.
4. Add the following lines: 

# Options to avoid "ORA-12705: Cannot access NLS data files or invalid environment specified." 

AddVMOption -Duser.language=en 

AddVMOption -Duser.region=US 

AddVMOption -Duser.country=en

5. Restart SQL Developer

We can also use below command if issue occurs after login :

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY'