Query to find Salesperson associated with a Sales Order in Oracle Apps R12

SELECT oha.order_number,
       jrre.resource_name SALES_PERSON,
       jrre.source_email,
       jrs.name
FROM   oe_order_headers_all OHA,
       jtf_rs_salesreps jrs,
       jtf_rs_resource_extns_vl JRRE
WHERE  1 = 1
       AND oha.salesrep_id = jrs.salesrep_id
       AND JRS.resource_id = JRRE.resource_id
       AND oha.order_number = :order_number; 

(or)

SELECT xx.resource_name sales_person
FROM   jtf_rs_salesreps jrs,
       jtf_rs_resource_extns_vl xx
WHERE  jrs.resource_id = xx.resource_id
       AND jrs.org_id = rct.org_id
       AND jrs.salesrep_id IN (SELECT ool.salesrep_id
                               FROM   oe_order_lines_all ool
                               WHERE  ool.line_id = ra_customer_trx_lines_all.interface_line_attribute6); 

(or)

SELECT Max(Nvl(jrs.name, jrd.resource_name)) 
FROM   oe_order_headers_all ooh, 
       apps.jtf_rs_salesreps jrs, 
       apps.jtf_rs_defresources_v jrd, 
       apps.ra_customer_trx_all rct 
WHERE  ooh.salesrep_id = jrs.salesrep_id 
       AND jrs.org_id = ooh.org_id 
       AND jrs.resource_id = jrd.resource_id 
       AND rct.interface_header_context = 'ORDER ENTRY' 
       AND rct.org_id = ooh.org_id 
       AND To_char(ooh.order_number) = rct.interface_header_attribute1 
       AND rct.customer_trx_id = :customer_trx_id 
       AND rct.org_id = :org_id;

The field JTF_RS_SALESREPS.NAME is an obsolete column in 11i release. Since salesrep is now tied to a resource, resource name should be used instead of Salesrep Name. The Resource name is maintained in JTF_RS_RESOURCE_EXTNS_VL.RESOURCE_NAME.

You can use the following SQL statement to determine the salesrep name:

SELECT rs.salesrep_number,
       --rs.NAME salesrep_name,
       res.resource_name salesrep_name,
       hou.name          org_name,
       rs.salesrep_id,
       rs.org_id,
       rs.start_date_active,
       rs.end_date_active
FROM   apps.jtf_rs_salesreps rs,
       apps.jtf_rs_resource_extns_vl RES,
       hr_organization_units hou
WHERE  hou.organization_id = rs.org_id
       AND rs.resource_id = res.resource_id; 

Query to find Item template attribute values in Oracle Apps R12

Most Important Tables used by Item Template query in oracle apps

1. mtl_item_templates
2. mtl_item_templ_attributes

Query :

SELECT   * 
FROM     ( 
                SELECT it.template_name, 
                       it.description      template_description, 
                       user_attribute_name attribute_name, 
                       report_user_value   attribute_value 
                FROM   mtl_item_templ_attributes ta, 
                       mtl_item_attributes att, 
                       mtl_item_templates it, 
                       mfg_lookups mfg 
                WHERE  att.attribute_name(+) = ta.attribute_name 
                AND    it.template_id(+) = ta.template_id 
                AND    mfg.lookup_code(+) = att.control_level 
                AND    mfg.lookup_type(+) = 'ITEM_CONTROL_LEVEL_GUI' ) 
pivot (min(attribute_value) FOR attribute_name IN ('Unit Volume',
                                   'Volume Unit of Measure',
                                   'BOM Item Type',
                                   'Asset Category',
                                   'List Price',
                                   'Purchasable',
                                   'Taxable Item',
                                   'UN Number',
                                   'Allow Substitute Receipts',
                                   'Receipt Required',
                                   'Receive Close Tolerance',
                                   'Reservation Control',
                                   'Subinventory Restrictions',
                                   'Safety Stock Bucket Days',
                                   'End Assembly Pegging',
                                   'Calculate ATP',
                                   'Preprocessing Lead Time',
                                   'Weight Unit of Measure',
                                   'Shrinkage Rate',
                                   'Expense Account',
                                   'Outside Processing Item',
                                   'Purchased Item',
                                   'Allow Unordered Receipts',
                                   'Days Late Receipt Allowed',
                                   'Lot Control',
                                   'Locator Restrictions',
                                   'Inventory Planning Method',
                                   'Min-Max Minimum Quantity', 
                                   'Order Cost',
                                   'Acceptable Early Days',
                                   'WIP Supply Type',
                                   'Internal Ordered Item',
                                   'Picking Rule',
                                   'Assemble to Order',
                                   'Unit Weight',
                                   'Primary Unit of Measure',
                                   'Costing Enabled',
                                   'Inventory Asset Value',
                                   'Allow Description Update',
                                   'RFQ Required',
                                   'Rounding Factor',
                                   'Receipt Date Exception',
                                   'Safety Stock Percent',
                                   'Replenishment Source Type',
                                   'MRP Planning Method',
                                   'Invoice Enabled',
                                   'Starting Serial Number',
                                   'Stockable',
                                   'BOM Allowed',
                                   'Market Price',
                                   'Unit of Issue',
                                   'Starting Serial Prefix',
                                   'Pos Measurement Error',
                                   'Revision Control',
                                   'Shelf Life Control',
                                   'Safety Stock',
                                   'Overrun Percentage',
                                   'Starting Lot Prefix',
                                   'Starting Lot Number',
                                   'Conversions',
                                   'Standard Lot Size',
                                   'Hazard Class',
                                   'Invoice Close Tolerance',
                                   'Stock Locator Control',
                                   'Shelf Life Days',
                                   'Source Subinventory',
                                   'Demand Time Fence Days',
                                   'Planning Time Fence',
                                   'Planning Time Fence Days',
                                   'WIP Supply Locator',
                                   'WIP Supply Subinventory',
                                   'ATP Rule',
                                   'Forecast Control',
                                   'Days Early Receipt Allowed',
                                   'Quantity Received Tolerance',
                                   'User Item Type',
                                   'Base Model',
                                   'Use Approved Vendor',
                                   'Enforce Ship-To Location', 
                                   'Inspection Required',
                                   'Fixed Order Quantity',
                                   'Min-Max Maximum Quantity', 
                                   'Minimum Order Quantity',
                                   'Source Organization',
                                   'Acceptable Rate Decrease',
                                   'Acceptable Rate Increase',
                                   'Reduce MPS',
                                   'Demand Time Fence',
                                   'Repetitive Planning',
                                   'Cumulative Total Lead Time',
                                   'Lead Time Lot Size',
                                   'Postprocessing Lead Time',
                                   'ATP Components',
                                   'Default Shipping Organization',
                                   'Ship Model Complete',
                                   'Accounting Rule',
                                   'Invoiceable Item',
                                   'Tax Code',
                                   'Carrying Cost Percent',
                                   'Processing Lead Time',
                                   'Cost of Goods Sold Account',
                                   'Default Buyer',
                                   'Outside Processing Unit Type',
                                   'Price Tolerance %',
                                   'Allow Express Delivery',
                                   'Quantity Received Exception',
                                   'Cycle Count Enabled',
                                   'Inventory Item',
                                   'Planner',
                                   'Make or Buy',
                                   'Cum Manufacturing Lead Time',
                                   'Fixed Lead Time',
                                   'Check ATP',
                                   'Customer Ordered Item',
                                   'Internal Orders Enabled',
                                   'Shippable Item',
                                   'Sales Account',
                                   'Fixed Days Supply',
                                   'Fixed Lot Size Multiplier',
                                   'Build in WIP',
                                   'Item Status',
                                   'Include in Rollup',
                                   'Encumbrance Account',
                                   'Receipt Routing',
                                   'Transactable',
                                   'Serial Number Control',
                                   'Maximum Order Quantity',
                                   'Rounding Control',
                                   'Variable Lead Time',
                                   'Customer Orders Enabled',
                                   'Pick Components',
                                   'Returnable',
                                   'RMA Inspection Status',
                                   'OE Transactable',
                                   'Invoicing Rule',
                                   'Payment Terms',
                                   'Neg Measurement Error',
                                   'Planning Exception Set') )
ORDER BY template_name;