BOM Data Drill Down Query in Oracle Apps R12

SELECT (SELECT ou.name 
        FROM   org_organization_definitions ood, 
               hr_operating_units ou 
        WHERE  ood.operating_unit = ou.organization_id 
               AND ood.organization_id = msi1.organization_id)ou, 
       (SELECT ood.organization_name 
        FROM   org_organization_definitions ood 
        WHERE  ood.organization_id = msi1.organization_id)    inv_code, 
       (SELECT segment1 
        FROM   mtl_system_items_b 
        WHERE  1 = 1 
               AND inventory_item_id = path 
               AND organization_id = :p_organization_id)      assembly_item, 
       msi.description                                        assembly_item_desc, 
       alternate_bom_designator 
       ALTERNATE_name, 
       item_num, 
       operation_seq_num, 
       Lpad (' ', 10 * ( lvl - 1 ))||msi1.segment1           AS Component_item, 
       msi1.description                                       comp_item_desc, 
       component_quantity, 
       msi1.primary_unit_of_measure                           uom 
FROM   mtl_system_items_b msi, 
       mtl_system_items_b msi1, 
       (SELECT assembly_item_id, 
               LEVEL                                                lvl, 
               Substr (Sys_connect_by_path (assembly_item_id, '/') 
                       || '/', 2, Instr (( Sys_connect_by_path (assembly_item_id 
                                           , '/') 
                                           || '/' ), '/', 2) - 2)   path, 
               component_item_id, 
               component_quantity, 
               item_num, 
               supply_subinventory, 
               component_quantity * Nvl(PRIOR component_quantity, 1)extended_qty, 
               alternate_bom_designator, 
               implementation_date, 
               creation_date, 
               from_date, 
               to_date, 
               operation_seq_num 
        FROM   (SELECT DISTINCT bom.assembly_item_id, 
                                bic.component_item_id, 
                                bom.organization_id, 
                                bom.alternate_bom_designator, 
                                Trunc (bom.implementation_date) 
                                implementation_date, 
                                bic.component_quantity, 
                                bic.item_num, 
                                bic.supply_subinventory, 
                                bom.creation_date, 
                                bic.effectivity_date            from_date, 
                                bic.disable_date                to_date, 
                                bic.operation_seq_num 
                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 ) 
                       AND bic.implementation_date IS NOT NULL 
                       AND bom.alternate_bom_designator IS NULL 
                       AND bom.organization_id = Nvl(:p_organization_id,bom.organization_id) 
               
               ) 
        START WITH assembly_item_id IN (SELECT inventory_item_id 
                                        FROM   mtl_system_items_b 
                                        WHERE  segment1 = Nvl (:p_item_code,segment1) 
                                               AND organization_id = Nvl (:p_organization_id,organization_id))
        CONNECT BY NOCYCLE PRIOR component_item_id = assembly_item_id) bom 
WHERE  bom.assembly_item_id = msi.inventory_item_id 
       AND msi.organization_id = msi1.organization_id 
       AND bom.component_item_id = msi1.inventory_item_id 
       AND msi.organization_id = :p_organization_id; 

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;