Item Classification Query in Oracle Apps

Item Classification Query

-- To Retrieve Template Level Values --

SELECT 'q1' query1, msi.organization_id, msi.inventory_item_id,
       msi.segment1 item_code, msi.description item_desc, tmpl.template_name,
       tmpl.description, tmpl.regime_code, att.attribute_code,
       att.attribute_value
  FROM jai_rgm_tmpl_itm_regns tmp_itm,
       jai_rgm_tmpl_org_regns tmp_org,
       jai_rgm_itm_templates tmpl,
       jai_rgm_itm_tmpl_attrs att,
       mtl_system_items_b msi
 WHERE tmp_itm.templ_org_regns_id = tmp_org.templ_org_regns_id
   AND tmpl.template_id = tmp_org.template_id
   AND tmpl.template_id = att.template_id
   AND msi.inventory_item_id = tmp_itm.inventory_item_id
   AND msi.organization_id = tmp_org.organization_id
   AND tmp_org.organization_id = 3
   --AND tmp_itm.inventory_item_id = 2377
UNION ALL
-- To Retrieve Attribute Level Values --
SELECT 'q2' query2, msi.organization_id, msi.inventory_item_id,
       msi.segment1 item_code, msi.description item_desc,
       'tmpl' template_name, NULL description, rgm.regime_code,
       tmpl.attribute_code, tmpl.attribute_value
  FROM jai_rgm_itm_regns rgm,
       jai_rgm_itm_tmpl_attrs tmpl,
       mtl_system_items_b msi
 WHERE rgm.rgm_item_regns_id = tmpl.rgm_item_regns_id
   AND rgm.organization_id = 3
   --AND rgm.inventory_item_id = 2377
   AND msi.inventory_item_id = rgm.inventory_item_id
   AND rgm.organization_id = msi.organization_id

Ap Invoice to XLA Ref

Ap Invoice to XLA Ref Link


SELECT distinct ai.invoice_num, ai.gl_date, xah.accounting_date,
             gjh.je_category, gjh.je_source, gjh.period_name, gjh.status,
             aid.invoice_line_number, aid.line_type_lookup_code,
             ail.description, aid.amount, aid.dist_code_combination_id
        FROM apps.gl_je_headers gjh,
             apps.gl_je_lines gjl,
             apps.gl_import_references gir,
             apps.xla_ae_lines xal,
             apps.xla_ae_headers xah,
             apps.xla_events xe,
             apps.xla_event_types_tl xet,
             apps.xla_event_classes_tl xect,
             apps.xla_distribution_links xdl,
             apps.ap_invoice_distributions_all aid,
             apps.ap_invoices_all ai,
             apps.ap_invoice_lines_all ail
       WHERE gjh.je_header_id = gjl.je_header_id
         AND gjh.je_header_id = gir.je_header_id
         AND gjl.je_header_id = gir.je_header_id
         AND gir.je_line_num = gjl.je_line_num
         AND gir.gl_sl_link_id = xal.gl_sl_link_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xah.event_id = xe.event_id
         AND xe.event_type_code = xet.event_type_code
         AND xe.application_id = xet.application_id
         AND xet.LANGUAGE = USERENV ('LANG')
         AND xect.event_class_code = xet.event_class_code
         AND xect.application_id = xe.application_id
         AND xect.LANGUAGE = USERENV ('LANG')
         AND xah.ae_header_id = xdl.ae_header_id
         AND xal.ae_line_num = xdl.ae_line_num
         AND xdl.source_distribution_type = 'AP_INV_DIST'
         AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
         AND ai.invoice_id = aid.invoice_id
         AND ai.invoice_id = ail.invoice_id
         AND ail.invoice_id = aid.invoice_id
         AND aid.invoice_line_number = ail.line_number
         AND xah.event_type_code <> ' MANUAL'
         AND gjh.je_source = 'Payables'
         AND ai.org_id = p_org_id
         AND xah.accounting_date BETWEEN p_period_start_date AND p_period_end_date;

Item On hand Quantity

Item on hand Quantity

SELECT   msi.segment1, SUM (mq.transaction_quantity) on_hand,
         ood.organization_name, mq.subinventory_code
    FROM apps.org_organization_definitions ood,
         apps.mtl_onhand_quantities mq,
         apps.mtl_system_items_b msi
   WHERE 1 = 1
     AND mq.organization_id = msi.organization_id
     AND ood.organization_id = msi.organization_id
     AND mq.inventory_item_id = msi.inventory_item_id
     AND msi.segment1 = :P_ITEM_CODE               ------- pass segment1 here
GROUP BY msi.segment1, ood.organization_name, mq.subinventory_code

Responsibility Add Package

To Add Responsibility via Toad


   -- To get responsiblity ID --
SELECT *
  FROM fnd_responsibility_tl
 WHERE responsibility_name = 'Application Developer'
   
   -- To get User ID --   
SELECT *
  FROM fnd_user
 WHERE user_name = :user_name 
   
   -- To get Application ID --
SELECT *
  FROM fnd_application_tl
 WHERE application_name = 'Application Object Library'




-- Execute the below anonymous package to add responsibility --


DECLARE
   p_user_id         NUMBER;
   p_responsibility_id   NUMBER;
   p_application_id      NUMBER;
BEGIN
   p_user_id := 8891;
   p_responsibility_id := 53956;
   p_application_id := 7000;
   fnd_user_resp_groups_api.insert_assignment
                          (user_id                            => p_user_id,
                           responsibility_id                  => p_responsibility_id,
                           responsibility_application_id      => p_application_id,
                           security_group_id                  => 0,
                           start_date                         => SYSDATE - 1,
                           end_date                           => NULL,
                           description                        => NULL
                          );
END;

DFF Flex field Query to find attributes

To Find DFF Flex field  attributes

SELECT ffv.application_table_name, ffv.descriptive_flexfield_name,
       ffv.context_column_name, ffv.title, att.application_column_name,
       att.end_user_column_name, att.column_seq_num, att.enabled_flag,
       att.required_flag, att.security_enabled_flag, att.display_flag,
       att.flex_value_set_id, att.form_left_prompt, ffv.*
  FROM fnd_descriptive_flexs_vl ffv, fnd_descr_flex_col_usage_vl att
 WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
   -- AND ffv.descriptive_flexfield_name = 'PO_LINES'
   AND (ffv.title) = 'Transaction Information' 

To Find joins between Tables in Oracle Apps

To Find Joins between tables in oracle apps


set pagesize 5000
set linesize 5000
spool C:\Script\OUTPUT.TXT
set colsep "|"
select * from dual ;
set colsep " "
spool off
set pagesize 50
set linesize 200


/* Formatted on 2011/02/18 10:05 (Formatter Plus v4.8.8) */
SELECT   d.table_name "Table name", d.constraint_name "Constraint name",
         DECODE (d.constraint_type,
                 'P', 'Primary Key',
                 'R', 'Foreign Key',
                 'C', 'Check/Not Null',
                 'U', 'Unique',
                 'V', 'View Cons'
                ) "Type",
         d.search_condition "Check Condition", p.table_name "Ref Table name",
         p.constraint_name "Ref by", m.column_name "Ref col",
         m.POSITION "Position", p.owner "Ref owner"
    FROM dba_constraints d LEFT JOIN dba_constraints p
         ON (d.r_owner = p.owner AND d.r_constraint_name = p.constraint_name)
         LEFT JOIN dba_cons_columns m ON (d.constraint_name =
                                                             m.constraint_name
                                         )
   WHERE d.table_name IN (
                        SELECT table_name
                          FROM dba_tables
                         WHERE owner = UPPER ('mkm')
                        UNION ALL
                        SELECT view_name
                          FROM dba_views
                         WHERE owner = UPPER ('mkm'))
ORDER BY 1, 2

AP Third Party Invoice Query against Receipt

AP Third Party Invoice Query against Receipt


SELECT rsh.receipt_num, TRUNC (rsh.creation_date) receipt_date,
       jrti.invoice_num, aia.invoice_date, aia.gl_date, jrti.invoice_id,
       pov.vendor_name, pvs.vendor_site_code, jrtid.line_number,
       jrtid.tax_type, jrtid.tax_rate, jrtid.tax_amount
  FROM jai_rcv_tp_invoices jrti,
       jai_rcv_tp_inv_details jrtid,
       rcv_shipment_headers rsh,
       po_vendors pov,
       po_vendor_sites_all pvs,
       ap_invoices_all aia
 WHERE jrti.batch_invoice_id = jrtid.batch_invoice_id
   AND rsh.shipment_header_id = jrti.shipment_header_id
   AND jrti.vendor_id = pov.vendor_id
   AND pov.vendor_id = pvs.vendor_id
   AND pvs.vendor_site_id = jrti.vendor_site_id
   AND aia.invoice_id = jrti.invoice_id
   AND rsh.shipment_header_id = 1664789