Add System Administrator Responsibility to a User from Back end in Oracle Apps R12

BEGIN
   FND_USER_PKG.ADDRESP
                       ('YOUR_USERNAME',
                        'SYSADMIN',
                        'SYSTEM_ADMINISTRATOR',
                        'STANDARD',
                        'Add Sysadmin Responsibility to A user from Backend',
                        SYSDATE - 1,
                        SYSDATE + 99
                       );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Responsibility Added Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (   ' Sys Admin Resp cannot be added: '
                            || SQLCODE
                            || SUBSTR (SQLERRM, 1, 100)
                           );
END;

Query to get Concurrent Program's Parameters, Value Sets and Default Values in Oracle Apps R12

SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.APPLICATION_SHORT_NAME
      , fav.APPLICATION_NAME
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.DEFAULT_VALUE    
 FROM   apps.fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
 AND    fcpl.LANGUAGE = 'US'
 AND    fav.application_id=fcp.application_id
 AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = fdfcuv.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG')
 AND    fcpl.user_concurrent_program_name LIKE 'WIP Value Report'

PO Queries in Oracle Apps

Purchase Requisition details
====================================================
SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by,
       poh.segment1 "PO #", ppx.full_name "Requestor Name",
       prh.description "Req Description", prh.authorization_status,
       prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,
       prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code,
       prl.unit_price, prl.quantity, prl.quantity_delivered, prl.need_by_date,
       prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date,
       prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND ppx.person_id = prh.preparer_id
   AND prh.type_lookup_code = 'PURCHASE'
   AND prd.requisition_line_id = prl.requisition_line_id
   AND pda.req_distribution_id = prd.distribution_id
   AND pda.po_header_id = poh.po_header_id
  -- AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')
 
Internal Requisition details
====================================================
SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by,
       poh.segment1 "PO #", ppx.full_name "Requestor Name",
       prh.description "Req Description", prh.authorization_status,
       prh.note_to_authorizer, prl.line_num, prl.line_type_id,
       prl.source_type_code, prl.item_description, prl.unit_meas_lookup_code,
       prl.unit_price, prl.quantity, prl.quantity_delivered, prl.need_by_date,
       prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date,
       prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND ppx.person_id = prh.preparer_id
   AND prh.type_lookup_code = 'INTERNAL'
   AND prd.requisition_line_id = prl.requisition_line_id
   AND pda.req_distribution_id(+) = prd.distribution_id
   AND pda.po_header_id = poh.po_header_id(+)
   AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')
 
Purchase Order details
====================================================
-- Purchase Orders for non inventory items like service
SELECT ph.segment1 po_num, ph.creation_date, hou.NAME "Operating Unit",
       ppx.full_name "Buyer Name", ph.type_lookup_code "PO Type",
       plc.displayed_field "PO Status", ph.comments, pl.line_num,
       plt.order_type_lookup_code "Line Type", NULL "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,
       po_line_types_b plt,
       org_organization_definitions ood,
       per_people_x ppx1,
       po_lookup_codes plc
 WHERE 1 = 1
   AND TO_CHAR (ph.creation_date, 'YYYY') IN (2010, 2011)
   AND ph.vendor_id = pv.vendor_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 plc.lookup_type = 'DOCUMENT STATE'
   AND plc.lookup_code = ph.closed_code
   AND pl.item_id IS NULL
UNION
-- Purchase Orders for inventory items
SELECT ph.segment1 po_num, ph.creation_date, hou.NAME "Operating Unit",
       ppx.full_name "Buyer Name", 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 TO_CHAR (ph.creation_date, 'YYYY') IN (2010, 2011)
   AND ph.vendor_id = pv.vendor_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 pl.item_id IS NOT NULL

Receiving transactions with PO and requisition information
====================================================
SELECT   ph.segment1 po_num, ood.organization_name, pol.po_line_id,
         pll.quantity, rsh.receipt_source_code, rsh.vendor_id,
         rsh.vendor_site_id, rsh.organization_id, rsh.shipment_num,
         rsh.receipt_num, rsh.ship_to_location_id, rsh.bill_of_lading,
         rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received,
         rct.transaction_type, rct.transaction_id,
         NVL (rct.source_doc_quantity, 0) transaction_qty
    FROM rcv_transactions rct,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pol,
         po_line_locations_all pll,
         po_headers_all ph,
         org_organization_definitions ood
   WHERE 1 = 1
     AND TO_CHAR (rct.creation_date, 'YYYY') IN ('2010', '2011')
     AND rct.po_header_id = ph.po_header_id
     AND rct.po_line_location_id = pll.line_location_id
     AND rct.po_line_id = pol.po_line_id
     AND rct.shipment_line_id = rsl.shipment_line_id
     AND rsl.shipment_header_id = rsh.shipment_header_id
     AND rsh.ship_to_org_id = ood.organization_id
ORDER BY rct.transaction_id  


Cancel Requisitions
====================================================

SELECT prh.requisition_header_id, prh.preparer_id, prh.segment1 "REQ NUM",
       TRUNC (prh.creation_date), prh.description, prh.note_to_authorizer
  FROM apps.po_requisition_headers_all prh, apps.po_action_history pah
 WHERE action_code = 'CANCEL'
   AND pah.object_type_code = 'REQUISITION'
   AND pah.object_id = prh.requisition_header_id
 
Internal Requisitions that do not have an associated Internal Sales Order
====================================================
 
   SELECT   rqh.segment1, rql.line_num, rql.requisition_header_id,
         rql.requisition_line_id, rql.item_id, rql.unit_meas_lookup_code,
         rql.unit_price, rql.quantity, rql.quantity_cancelled,
         rql.quantity_delivered, rql.cancel_flag, rql.source_type_code,
         rql.source_organization_id, rql.destination_organization_id,
         rqh.transferred_to_oe_flag
    FROM po_requisition_lines_all rql, po_requisition_headers_all rqh
   WHERE rql.requisition_header_id = rqh.requisition_header_id
     AND rql.source_type_code = 'INVENTORY'
     AND rql.source_organization_id IS NOT NULL
     AND NOT EXISTS (
            SELECT 'existing internal order'
              FROM oe_order_lines_all lin
             WHERE lin.source_document_line_id = rql.requisition_line_id
               AND lin.source_document_type_id = 10)
ORDER BY rqh.requisition_header_id, rql.line_num

Relation with Requisition and PO
====================================================

SELECT r.segment1 "Req Num", p.segment1 "PO Num"
  FROM po_headers_all p,
       po_distributions_all d,
       po_req_distributions_all rd,
       po_requisition_lines_all rl,
       po_requisition_headers_all r
 WHERE p.po_header_id = d.po_header_id
   AND d.req_distribution_id = rd.distribution_id
   AND rd.requisition_line_id = rl.requisition_line_id
   AND rl.requisition_header_id = r.requisition_header_id
 
Purchase Requisition without a Purchase Order
====================================================

SELECT   prh.segment1 "PR NUM", TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date", prl.line_num "Seq #",
         msi.segment1 "Item Num", prl.item_description "Description",
         prl.quantity "Qty", TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name
                     FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE prh.requisition_header_id = prl.requisition_header_id
     AND prl.requisition_line_id = prd.requisition_line_id
     AND ppf1.person_id = prh.preparer_id
     AND prh.creation_date BETWEEN ppf1.effective_start_date
                               AND ppf1.effective_end_date
     AND ppf2.agent_id(+) = msi.buyer_id
     AND msi.inventory_item_id = prl.item_id
     AND msi.organization_id = prl.destination_organization_id
     AND pll.line_location_id(+) = prl.line_location_id
     AND pll.po_header_id = ph.po_header_id(+)
     AND pll.pl_line_id = pl.po_line_id(+)
     AND prh.authorization_status = 'APPROVED'
     AND pll.line_location_id IS NULL
     AND prl.closed_code IS NULL
     AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2

Requisition moved from different stages till converting into PR
====================================================

SELECT DISTINCT u.description "Requestor", porh.segment1 AS "Req Number",
                TRUNC (porh.creation_date) "Created On", pord.last_updated_by,
                porh.authorization_status "Status",
                porh.description "Description", poh.segment1 "PO Number",
                TRUNC (poh.creation_date) "PO Creation Date",
                poh.authorization_status "PO Status",
                TRUNC (poh.approved_date) "Approved Date"
           FROM apps.po_headers_all poh,
                apps.po_distributions_all pod,
                apps.po_req_distributions_all pord,
                apps.po_requisition_lines_all porl,
                apps.po_requisition_headers_all porh,
                apps.fnd_user u
          WHERE porh.requisition_header_id = porl.requisition_header_id
            AND porl.requisition_line_id = pord.requisition_line_id
            AND pord.distribution_id = pod.req_distribution_id(+)
            AND pod.po_header_id = poh.po_header_id(+)
            AND porh.created_by = u.user_id
       ORDER BY 2
     
 PO’s which does not have any PR
====================================================
      
SELECT   prh.segment1 "PR NUM", TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date", prl.line_num "Seq #",
         msi.segment1 "Item Num", prl.item_description "Description",
         prl.quantity "Qty", TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name
                     FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE prh.requisition_header_id = prl.requisition_header_id
     AND prl.requisition_line_id = prd.requisition_line_id
     AND ppf1.person_id = prh.preparer_id
     AND prh.creation_date BETWEEN ppf1.effective_start_date
                               AND ppf1.effective_end_date
     AND ppf2.agent_id(+) = msi.buyer_id
     AND msi.inventory_item_id = prl.item_id
     AND msi.organization_id = prl.destination_organization_id
     AND pll.line_location_id(+) = prl.line_location_id
     AND pll.po_header_id = ph.po_header_id(+)
     AND pll.po_line_id = pl.po_line_id(+)
     AND prh.authorization_status = 'APPROVED'
     AND pll.line_location_id IS NULL
     AND prl.closed_code IS NULL
     AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2    

All Open Purchase Orders
====================================================

SELECT h.segment1 "PO NUM", h.authorization_status "STATUS",
       l.line_num "SEQ NUM", ll.line_location_id, d.po_distribution_id,
       h.type_lookup_code "TYPE"
  FROM po.po_headers_all h,
       po.po_lines_all l,
       po.po_line_locations_all ll,
       po.po_distributions_all d
 WHERE h.po_header_id = l.po_header_id
   AND ll.po_line_id = l.po_line_id
   AND ll.line_location_id = d.line_location_id
   AND h.closed_date IS NULL
   AND h.type_lookup_code NOT IN ('QUOTATION')
 
All POs with Approval, Invoice & Payment details
====================================================

SELECT a.org_id "ORG ID", e.segment1 "VENDOR NUM",
       e.vendor_name "SUPPLIER NAME",
       UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
       f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
       f.city "CITY", f.country "COUNTRY",
       TO_CHAR (TRUNC (d.creation_date)) "PO Date", d.segment1 "PO NUM",
       d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED",
       c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID",
       g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
         (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
         )
       * NVL (g.unit_price, 0) "PO Line Amount",
       (SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
          FROM po.po_headers_all ph
         WHERE ph.po_header_id = d.po_header_id) "PO Approved?",
       a.invoice_type_lookup_code "INVOICE TYPE",
       a.invoice_amount "INVOICE AMOUNT",
       TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
       a.invoice_num "INVOICE NUMBER",
       (SELECT DECODE (x.match_status_flag,
                       'A', 'Approved'
                      )
          FROM ap.ap_invoice_distributions_all x
         WHERE x.invoice_distribution_id = b.invoice_distribution_id)
                                                          "Invoice Approved?",
       a.amount_paid, h.amount, h.check_id, h.invoice_payment_id "Payment Id",
       i.check_number "Cheque Number",
       TO_CHAR (TRUNC (i.check_date)) "Payment Date"
  FROM ap.ap_invoices_all a,
       ap.ap_invoice_distributions_all b,
       po.po_distributions_all c,
       po.po_headers_all d,
       po.po_vendors e,
       po.po_vendor_sites_all f,
       po.po_lines_all g,
       ap.ap_invoice_payments_all h,
       ap.ap_checks_all i
 WHERE a.invoice_id = b.invoice_id
   AND b.po_distribution_id = c.po_distribution_id(+)
   AND c.po_header_id = d.po_header_id(+)
   AND e.vendor_id(+) = d.vendor_id
   AND f.vendor_site_id(+) = d.vendor_site_id
   AND d.po_header_id = g.po_header_id
   AND c.po_line_id = g.po_line_id
   AND a.invoice_id = h.invoice_id
   AND h.check_id = i.check_id
   AND f.vendor_site_id = i.vendor_site_id
   AND c.po_header_id IS NOT NULL
   AND a.payment_status_flag = 'Y'
   AND d.type_lookup_code != 'BLANKET'
 
Po Which are not received for a perticular Preparer
====================================================

SELECT p.po_header_id po_header_id, r.segment1 req_number,
       p.segment1 po_number
  FROM po_headers_all p,
       po_distributions_all d,
       po_req_distributions_all rd,
       po_requisition_lines_all rl,
       po_requisition_headers_all r
 WHERE p.po_header_id = d.po_header_id
   AND d.req_distribution_id = rd.distribution_id
   AND rd.requisition_line_id = rl.requisition_line_id
   AND rl.requisition_header_id = r.requisition_header_id
   AND r.preparer_id = 12345
   AND p.po_header_id NOT IN (
          SELECT po_header_id
            FROM apps.rcv_transactions
           WHERE po_header_id IN (
                    SELECT   p.po_header_id po_header_id
                        FROM po_headers_all p,
                             po_distributions_all d,
                             po_req_distributions_all rd,
                             po_requisition_lines_all rl,
                             po_requisition_headers_all r
                       WHERE p.po_header_id = d.po_header_id
                         AND d.req_distribution_id = rd.distribution_id
                         AND rd.requisition_line_id = rl.requisition_line_id
                         AND rl.requisition_header_id =
                                                       r.requisition_header_id
                         AND r.preparer_id = 12345
                    GROUP BY p.po_header_id))    

Query to get Profile values for all the Levels in Oracle Apps R12

SELECT   SUBSTR (e.profile_option_name, 1, 25) internal_name,
         SUBSTR (pot.user_profile_option_name, 1, 60) name_in_forms,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User',
                 10005, 'Server',
                 10007, 'Server + Resp',
                 a.level_id
                ) levell,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, c.application_short_name,
                 10003, b.responsibility_name,
                 10004, d.user_name,
                 10005, n.node_name,
                 10007, m.node_name || ' + ' || b.responsibility_name,
                 a.level_id
                ) level_value,
         NVL (a.profile_option_value, 'Is Null') VALUE,
         TO_CHAR (a.last_update_date, 'DD-MON-YYYY HH24:MI') last_update_date,
         dd.user_name last_update_user
    FROM fnd_profile_option_values a,
         fnd_responsibility_tl b,
         fnd_application c,
         fnd_user d,
         fnd_profile_options e,
         fnd_nodes n,
         fnd_nodes m,
         fnd_responsibility_tl x,
         fnd_user dd,
         fnd_profile_options_tl pot
   WHERE e.profile_option_name LIKE 'MFG_ORGANIZATION_ID'
     AND e.profile_option_name = pot.profile_option_name(+)
     AND e.profile_option_id = a.profile_option_id(+)
     AND a.level_value = b.responsibility_id(+)
     AND a.level_value = c.application_id(+)
     AND a.level_value = d.user_id(+)
     AND a.level_value = n.node_id(+)
     AND a.level_value_application_id = x.responsibility_id(+)
     AND a.level_value2 = m.node_id(+)
     AND a.last_updated_by = dd.user_id(+)
     AND pot.LANGUAGE = 'US'
ORDER BY e.profile_option_name

Queries to get Oracle Form details in Oracle Apps R12

-- Starting of Script for form and its assigned responsibility

SELECT Ffv.TYPE
      ,ff.form_name
      ,Ffv.Function_Name
      ,Ffv.User_Function_Name
      ,Ffv.Description    
      --,fm.menu_name
      ,frl.responsibility_name
      ,Fme.entry_sequence
      ,fme.prompt    
FROM   Fnd_Form_Functions_Vl Ffv
      ,fnd_form ff
      ,Fnd_Menu_Entries_Vl Fme    
      ,fnd_menus fm
      ,fnd_responsibility fr
      ,fnd_responsibility_tl frl
WHERE  ff.form_id            = ffv.form_id
AND    fme.function_id       = ffv.function_id
AND    fm.menu_id            = fme.menu_id
AND    fr.menu_id            = fme.menu_id
AND    frl.responsibility_id = fr.responsibility_id
AND    ff.form_Name          = :form_name;

-- Starting of Script for form personalizations details and its assigned responsibility
SELECT ffr.form_name
      ,ffr.function_name
      ,ffr.description
      ,ffr.sequence
      ,ffr.trigger_event
      ,ffr.trigger_object
      ,ffr.enabled
      ,ffa.SEQUENCE action_seq
      ,ffa.action_type
      ,ffa.SUMMARY action_desc
      ,ffa.enabled action_enabled
      ,ffa.object_type
      ,ffa.target_object
      ,ffa.property_name
      ,ffa.property_value
FROM   fnd_form_custom_rules ffr
      ,fnd_form_custom_actions ffa
WHERE  ffr.ID = ffa.rule_id
and    ffr.form_name = :form_name;

Query to get Patches and Application Install details in Oracle Apps R12

---------------------------------
SELECT a.application_name,
       DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
       patch_level, a.application_id
  FROM fnd_application_vl a, fnd_product_installations b
 WHERE a.application_id = b.application_id;

---------------------------------
 SELECT   patch_name, patch_type, maint_pack_level, creation_date
    FROM ad_applied_patches
ORDER BY creation_date DESC;

---------------------------------

SELECT aru_release_name || '.' || minor_version || '.'
       || tape_version VERSION,
       start_date_active updated, row_source_comments "how it is done",
       base_release_flag "Base version"
  FROM ad_releases
 WHERE end_date_active IS NULL
---------------------------------
SELECT a.applied_patch_id, a.patch_name, a.patch_type, b.patch_drvier_id,
       b.driver_file_name, b.orig_patch_name, b.creation_date, b.platform,
       b.source_code, b.creationg_date, b.file_size, b.merged_driver_flag,
       b.merge_date
  FROM ad_applied_patches a, ad_patch_drivers b
 WHERE a.applied_patch_id = b.applied_patch_id
   AND a.patch_name = :PATCH_NUMBER

Get Concurrent Request Set Details in Oracle Apps R12

SELECT   rs.user_request_set_name "Request Set", rss.display_sequence seq,
         cp.user_concurrent_program_name "Concurrent Program",
         e.executable_name, e.execution_file_name, lv.meaning file_type,
         fat.application_name "Application Name"
FROM     fnd_request_sets_vl rs,
         fnd_req_set_stages_form_v rss,
         fnd_request_set_programs rsp,
         fnd_concurrent_programs_vl cp,
         fnd_executables e,
         fnd_lookup_values lv,
         fnd_application_tl fat
   WHERE 1 = 1
     AND rs.application_id = rss.set_application_id
     AND rs.request_set_id = rss.request_set_id
     AND rs.user_request_set_name     = :p_request_set_name
     AND e.application_id = fat.application_id
     AND rss.set_application_id = rsp.set_application_id
     AND rss.request_set_id = rsp.request_set_id
     AND rss.request_set_stage_id = rsp.request_set_stage_id
     AND rsp.program_application_id = cp.application_id
     AND rsp.concurrent_program_id = cp.concurrent_program_id
     AND cp.executable_id = e.executable_id
     AND cp.executable_application_id = e.application_id
     AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
     AND lv.lookup_code = e.execution_method_code
     AND lv.LANGUAGE = 'US'
     AND fat.LANGUAGE = 'US'
     AND rs.end_date_active IS NULL
ORDER BY 1, 2;


-- Starting of Script to find the request set assigned to a responsibility

SELECT frt.responsibility_name
      ,fcpt.user_request_set_name
      ,frst.request_set_stage_id
      ,frst.user_stage_name
FROM   apps.fnd_Responsibility fr
      ,apps.fnd_responsibility_tl frt
      ,apps.fnd_request_groups frg
      ,apps.fnd_request_group_units frgu
      ,apps.fnd_request_Sets_tl fcpt
      ,fnd_request_set_stages_tl frst
WHERE  frt.responsibility_id = fr.responsibility_id
AND    frg.request_group_id = fr.request_group_id
AND    frgu.request_group_id = frg.request_group_id
AND    fcpt.request_set_id = frgu.request_unit_id
AND    frst.request_set_id = fcpt.request_set_id
AND    frst.LANGUAGE = fcpt.LANGUAGE
AND    frt.LANGUAGE = USERENV('LANG')
AND    fcpt.LANGUAGE = USERENV('LANG')
AND    fcpt.user_request_set_name = :request_set_name
ORDER BY frt.responsibility_name
        ,frst.request_set_stage_id
        ,fcpt.user_request_set_name
        ,frst.user_stage_name

How to Kill the Session in Oracle

SELECT SID, SERIAL#, STATUS  FROM V$SESSION
----------------------------------------------
                1        17         INACTIVE
                5        566       INACTIVE
                9        55         ACTIVE

ALTER  SYSTEM KILL SESSION 'sid,serial#'

ALTER  SYSTEM KILL SESSION '1,17'
/

ALTER  SYSTEM KILL SESSION '5,566'
/

Query to get Trace File details of a Concurrent Request in Oracle Apps R12

SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
       'Trace Flag: ' || req.enable_trace,
          'Trace Name:
'
       || dest.VALUE
       || '/'
       || LOWER (dbnm.VALUE)
       || '_ora_'
       || oracle_process_id
       || '.trc',
       'Prog. Name: ' || prog.user_concurrent_program_name,
       'File Name: ' || execname.execution_file_name
       || execname.subroutine_name,
          'Status : '
       || DECODE (phase_code, 'R', 'Running')
       || '-'
       || DECODE (status_code, 'R', 'Normal'),
       'SID Serial: ' || ses.SID || ',' || ses.serial#,
       'Module : ' || ses.module
  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  req.request_id = :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

Check who are all logged into the instance Today in Oracle Apps R12

SELECT   fu.user_name "User Name", papf.full_name "Employee Name",
         NVL (papf.email_address, fu.email_address) "Email Address"
    FROM per_all_people_f papf, fnd_user fu, fnd_logins fl
   WHERE TRUNC (fl.start_time) = TRUNC (SYSDATE)
     AND fu.user_id = fl.user_id
     AND papf.person_id(+) = fu.employee_id
     AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST')
GROUP BY fu.user_name,
         papf.full_name,
         NVL (papf.email_address, fu.email_address)
ORDER BY fu.user_name

Base Tables of BI Publisher Data Definitions and Templates in Oracle Apps R12

XDO_DS_DEFINITIONS_B
    XDO_DS_DEFINITIONS_TL
XDO_TEMPLATES_B    
    XDO_TEMPLATES_TL     
XDO_LOBS    

XDO_CONFIG_KEYS      
XDO_CONFIG_VALUES
XDO_CONFIG_PROPERTIES_B
XDO_CONFIG_PREPORTIES_TL

XDO_FONT_MAPPINGS
XDO_FONT_MAPPING_SETS_B
XDO_FINT_MAPPING_SETS_TL
























/* Submitting Oracle Workflow from Backend
DECLARE
    l_itemtype VARCHAR2(10) := 'DEMO';
    l_itemkey  NUMBER := dbms_random.random();
BEGIN
    wf_engine.createprocess(l_itemtype, l_itemkey, 'DEMO_PRC');
    wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'DEMO_ROLE', 'FND_RESP101:20434');
    wf_engine.startprocess(l_itemtype, l_itemkey);
    COMMIT;
END;

*/

Submit FND Concurrent Program from Backend in Oracle Apps R12

DECLARE
ln_responsibility_id NUMBER;
ln_application_id     NUMBER;
ln_user_id           NUMBER;
ln_request_id            NUMBER;
BEGIN

--  FND_GLOBAL.APPS_INITIALIZE (ln_user_id,ln_responsibility_id,ln_application_id);
    FND_GLOBAL.APPS_INITIALIZE (1312,20434,101);  

   -- Submitting Concurrent Request
  ln_request_id := FND_REQUEST.SUBMIT_REQUEST (
                            application   => 'XXSVL', -- Application Short Code
                            program       => 'XXCUSTCONVPRG', -- Concurrent Program Short NAme
                            description   => 'SVL Customer Conversion Program', -- Concurrent Program Description
                            start_time    => SYSDATE,
                            sub_request   => FALSE,
argument1     => 100 -- Parameter Value
  );

  COMMIT; -- It is Mandatory

  IF ln_request_id = 0
  THEN
     DBMS_OUTPUT.PUT_LINE ('Concurrent Request Failed to Submit.');
  ELSE
     DBMS_OUTPUT.PUT_LINE('Successfully Submitted the Concurrent Request. Request Id: '||ln_request_id);
  END IF;

EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||SQLERRM);
END;
/  

Get FND Profile values in Oracle Apps R12

SELECT
      FND_PROFILE.VALUE ('MFG_ORGANIZATION_ID'),
       FND_PROFILE.VALUE ('ORG_ID'), FND_PROFILE.VALUE ('LOGIN_ID'),
       FND_PROFILE.VALUE ('USER_ID'), FND_PROFILE.VALUE ('USERNAME'),
       FND_PROFILE.VALUE ('CONCURRENT_REQUEST_ID'),
       FND_PROFILE.VALUE ('GL_SET_OF_BKS_ID'),
       FND_PROFILE.VALUE ('SO_ORGANIZATION_ID'),
       FND_PROFILE.VALUE ('APPL_SHRT_NAME'),
      FND_PROFILE.VALUE ('RESP_NAME'),
       FND_PROFILE.VALUE ('RESP_ID')
  FROM DUAL;

Initialization in Oracle Apps R12

------------------------------------------------------------------------------------------------
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S',204);
END;

------------------------------------------------------------------------------------------------

BEGIN
   MO_GLOBAL.INIT ('AR');
END;

------------------------------------------------------------------------------------------------

BEGIN
   FND_GLOBAL.APPS_INITIALIZE (
                               USER_ID              => 12348765,
                               RESP_ID              => 20407,
                               RESP_APPL_ID   => 101
                              );
END;

------------------------------------------------------------------------------------------------

Get All Responsibilities of a FND User in Oracle Apps R12

SELECT fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
  FROM apps.fnd_user_resp_groups_direct furg,
       apps.fnd_user fu,
       apps.fnd_responsibility_tl fr
 WHERE furg.user_id = fu.user_id
   AND furg.responsibility_id = fr.responsibility_id
   AND fr.LANGUAGE = USERENV ('LANG')
   AND fu.user_name LIKE 'AYAAN'

------------------------------------------------------------------------------------------------------------

SELECT DISTINCT fu.user_name, papf.employee_number, papf.full_name,
                fres.responsibility_name responsibility,
                (SELECT NAME
                   FROM hr_operating_units
                  WHERE organization_id = paaf.organization_id) company
           FROM fnd_user_resp_groups_direct fur,
                fnd_user fu,
                fnd_responsibility_vl fres,
                per_all_people_f papf,
                per_all_assignments_f paaf
          WHERE fu.user_id = fur.user_id
            AND fres.responsibility_id = fur.responsibility_id
            AND papf.person_id = paaf.person_id
            AND papf.person_id = fu.employee_id
            AND papf.current_employee_flag = 'Y'
            AND papf.employee_number IS NOT NULL
            AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                    AND papf.effective_end_date
            AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                    AND paaf.effective_end_date
       ORDER BY fu.user_name;

Query to identify the Concurrent Program based on the Value Set in Oracle Apps R12

SELECT fvs.flex_value_set_name, fct.user_concurrent_program_name,
       ffc.enabled_flag, fvt.application_table_name, fvt.value_column_name,
       fvt.id_column_name, fvt.meaning_column_name,
       fvt.additional_where_clause
  FROM fnd_flex_value_sets fvs,
       fnd_descr_flex_col_usage_vl ffc,
       fnd_flex_validation_tables fvt,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fct
 WHERE ffc.flex_value_set_id = fvs.flex_value_set_id
   AND fvs.flex_value_set_id = fvt.flex_value_set_id
   AND fcp.concurrent_program_id = fct.concurrent_program_id
   AND ffc.descriptive_flexfield_name ='$SRS$.' || fcp.concurrent_program_name
   AND fvs.flex_value_set_name = :value_set_name

Get Form and Its Assigned responsibility in Oracle Apps R12

SELECT ffv.TYPE, ff.form_name, ffv.function_name, ffv.user_function_name,
       ffv.description, fm.menu_name, frl.responsibility_name,
       fme.entry_sequence, fme.prompt
  FROM fnd_form_functions_vl ffv,
       fnd_form ff,
       fnd_menu_entries_vl fme,
       fnd_menus fm,
       fnd_responsibility fr,
       fnd_responsibility_tl frl
 WHERE ff.form_id = ffv.form_id
   AND fme.function_id = ffv.function_id
   AND fm.menu_id = fme.menu_id
   AND fr.menu_id = fme.menu_id
   AND frl.responsibility_id = fr.responsibility_id
   AND ff.form_name = :form_name;

Get PO Approval Limits in Oracle Apps

SELECT   a.user_id, a.employee_id, a.user_name, a.description,
         b.responsibility_name, b.description, d.full_name, e.job_id,
         g.control_group_name, h.object_code, h.amount_limit
    FROM fnd_user a,
         fnd_responsibility_vl b,
         fnd_user_resp_groups_direct c,
         per_all_people_f d,
         per_all_assignments_f e,
         po_position_controls_all f,
         po_control_groups_all g,
         po_control_rules h
   WHERE b.responsibility_id = c.responsibility_id
     AND c.user_id = a.user_id
     AND b.responsibility_name LIKE '%PO%Super%User'
     AND a.end_date IS NULL
     AND d.effective_end_date > SYSDATE
     AND d.person_id = a.employee_id
     AND d.person_id = e.person_id
     AND e.effective_end_date > SYSDATE
     AND e.job_id = f.job_id
     AND f.control_group_id = g.control_group_id
     AND g.control_group_id = h.control_group_id
     AND g.org_id = 105
ORDER BY user_name

Query to get All BOM Levels in Oracle Apps R12

SELECT (SELECT segment1
          FROM mtl_system_items_b
         WHERE bom.assembly_item_id = inventory_item_id
           AND organization_id = 204
           AND bom.lvl = 1) part,
       msi.segment1 parent_item_number, msi1.segment1 item_number,
       msi1.description, lvl bom_level,
       (SELECT MAX (revision)
          FROM mtl_item_revisions
         WHERE inventory_item_id = bom.component_item_id
           AND organization_id = 204) revision,
       component_quantity quantity
  FROM mtl_system_items_b msi,
       mtl_system_items_b msi1,
       (SELECT     assembly_item_id, LEVEL lvl, component_item_id,
                   component_quantity
              FROM (SELECT bom.assembly_item_id assembly_item_id,
                           bic.component_item_id component_item_id,
                           component_quantity
                      FROM bom_bill_of_materials bom,
                           bom_inventory_components bic
                     WHERE bom.bill_sequence_id = bic.bill_sequence_id
                       AND (   bic.disable_date IS NULL
                            OR bic.disable_date > SYSDATE
                           ))
        START WITH assembly_item_id IN (
                         SELECT inventory_item_id
                           FROM mtl_system_items_b
                          WHERE segment1 = '124-4567'
                                AND organization_id = 204)
        CONNECT BY PRIOR component_item_id = assembly_item_id) bom
 WHERE bom.assembly_item_id = msi.inventory_item_id
   AND msi.organization_id = 204
   AND bom.component_item_id = msi1.inventory_item_id
   AND msi1.organization_id = 204

Employee and Supervisor hierarchy in Oracle Apps HRMS R12

SELECT LEVEL seq,
  e.person_id,
  e.grade_id,
  e.job_id,
  e.supervisor_id,
  e.employee_number,
  e.full_name
FROM
  (SELECT DISTINCT paf.person_id,
    paf.grade_id,
    paf.job_id,
    paf.supervisor_id,
    ppf.employee_number,
    ppf.full_name
  FROM per_all_people_f ppf
  LEFT JOIN per_all_assignments_f paf
  ON ppf.person_id   = paf.person_id
  AND paf.person_id IS NOT NULL
  AND (SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date )
  AND (SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date )
  ) e
  CONNECT BY person_id = PRIOR supervisor_id
  START WITH person_id = :P_PERSON_ID
ORDER BY LEVEL