Query to Get Profile value at any level  

SELECT 

  distinct pot.user_profile_option_name Profile, 

  DECODE (

    a.profile_option_value, '1', '1 (may be "Yes")', 

    '2', '2 (may be "No")', a.profile_option_value

  ) Value, 

  DECODE (

    a.level_id, 10001, 'Site', 10002, 'Application', 

    10003, 'Responsibility', 10004, 'User', 

    '????'

  ) Level_identifier, 

  DECODE (

    a.level_id, 10002, e.application_name, 

    10003, c.responsibility_name, 10004, 

    d.user_name, '-'

  ) Level_Name 

FROM 

  applsys.fnd_application_tl e, 

  applsys.fnd_user d, 

  applsys.fnd_responsibility_tl c, 

  applsys.fnd_profile_option_values a, 

  applsys.fnd_profile_options b, 

  applsys.fnd_profile_options_tl pot 

WHERE 

  1 = 1 

  AND UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%') 

  AND pot.profile_option_name = b.profile_option_name 

  AND pot.language = userenv('lang') 

  AND b.application_id = a.application_id(+) 

  AND b.profile_option_id = a.profile_option_id(+) 

  AND a.level_value = c.responsibility_id(+) 

  AND a.level_value = d.user_id(+) 

  AND a.level_value = e.application_id(+) 

  AND e.language(+) = userenv('lang') 

  AND (

    UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%') 

    OR UPPER (c.responsibility_name) LIKE UPPER (

      '%&&appname_respname_username%'

    ) 

    OR UPPER (d.user_name) LIKE UPPER (

      '%&&appname_respname_username%'

    )

  ) 

ORDER BY 

  Profile, 

  Level_identifier, 

  Level_name, 

  Value

Below query can be handy to explode and Query BOM Details

SELECT 

  DISTINCT LPAD (' ', LEVEL * 2) || LEVEL order_level, 

  msib.segment1 assembly_item, 

  msib.description assembly_description, 

  msib.inventory_item_status_code assembly_item_status, 

  SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH, 

  msib2.segment1 AS component_item, 

  msib2.description component_item_description, 

  msib2.inventory_item_status_code component_item_status, 

  bic.item_num, 

  bic.operation_seq_num, 

  bic.component_quantity 

FROM 

  bom.bom_components_b bic, 

  bom.bom_structures_b bom, 

  inv.mtl_system_items_b msib, 

  inv.mtl_system_items_b msib2, 

  mtl_parameters mp 

WHERE 

  1 = 1 

  AND bic.bill_sequence_id = bom.bill_sequence_id 

  AND SYSDATE BETWEEN bic.effectivity_date 

  AND Nvl(bic.disable_date, SYSDATE) 

  AND bom.assembly_item_id = msib.inventory_item_id 

  AND bom.organization_id = msib.organization_id 

  AND bic.component_item_id = msib2.inventory_item_id 

  AND bom.organization_id = msib2.organization_id 

  AND mp.organization_id = msib.organization_id 

  AND mp.organization_code = : p_org_code 

  /* organization here */

  AND bom.alternate_bom_designator IS NULL START WITH msib.segment1 = : p_item_number 

  /*  component item to be used here */

  CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id 

ORDER BY 

  PATH 

SELECT 

  CONNECT_BY_ROOT q_bom.assembly_num root_assembly, 

  q_bom.assembly_num, 

  q_bom.component_num, 

  q_bom.qty, 

  SUBSTR(

    SYS_CONNECT_BY_PATH(q_bom.assembly_Num, ' <-- '), 

    5

  ) assembly_path, 

  LEVEL bom_level, 

  CONNECT_BY_ISCYCLE is_cycle 

FROM 

  (

    SELECT 

      mb1.segment1 assembly_num, 

      mb2.segment1 component_num, 

      bc.component_quantity qty 

    FROM 

      bom.bom_components_b bc, 

      bom.bom_structures_b bs, 

      inv.mtl_system_items_b mb1, 

      inv.mtl_system_items_b mb2 

    WHERE 

      bs.assembly_item_id = mb1.inventory_item_id 

      AND bc.component_item_id = mb2.inventory_item_id 

      AND bc.bill_sequence_id = bs.bill_sequence_id 

      AND mb1.organization_id = mb2.organization_id 

      AND bs.organization_id = mb2.organization_id 

      AND bc.disable_date Is Null 

      AND bs.alternate_bom_designator IS NULL 

      AND mb1.organization_id = 90101110109

  ) q_bom START WITH q_bom.assembly_Num = '&i_comp' CONNECT BY NOCYCLE PRIOR q_bom.component_num = q_bom.assembly_num ORDER SIBLINGS BY q_bom.assembly_Num;

we can convert rows into columns using the pivot function

let us consider the following data as sample data

DEPTNO ENAME JOB

10 ALLEN ANALYST

10 JONES CLERK

10 FORD MANAGER

10 ABCD PRESIDENT

10 CLARK SALESMAN

20 MILLER ANALYST

20 SMITH CLERK

20 WARD MANAGER

20 efgh PRESIDENT

20 MARTIN SALESMAN

30 SCOTT ANALYST

30 TURNER CLERK

30 ADAMS MANAGER

30 BLAKE PRESIDENT

30 KING SALESMAN

now create a table as emp_jobs with this data

using pivot function

we are converting rows into columns

select * from emp_jobs pivot( min(ename) for job in ('PRESIDENT','MANAGER','CLERK','ANALYST','SALESMAN'));

the output is in the form

DEPTNO PRESIDENT' MANAGER' CLERK' ANALYST' SALESMAN'

20 efgh WARD SMITH MILLER MARTIN

10 ABCD FORD JONES ALLEN CLARK

30 BLAKE ADAMS TURNER SCOTT KING

but while using pivot function aggregate function should be used 

we can also achieve this conversion without using pivot function using this sql query

 select

   a1.deptno,

   a1.ename  AS PRESIDENT,

   a2.ename AS MANAGER,

   a3.ename AS CLERK

from

   emp_jobs  a1,

   emp_jobs a2,

   emp_jobs  a3

   where

   a1.deptno=a2.deptno

   and a2.deptno=a3.deptno

   and a3.deptno=a1.deptno

   and a1.job = 'PRESIDENT'

   and a2.job = 'MANAGER'

   and a3.job ='CLERK';

SELECT 

  POH.PO_HEADER_ID, 

  POH.SEGMENT1, 

  PRHA.REQUISITION_NUMBER 

FROM 

  PO_HEADERS_ALL POH, 

  PO_DISTRIBUTIONS_ALL PDA, 

  POR_REQ_DISTRIBUTIONS_ALL PRDA, 

  POR_REQUISITION_LINES_ALL PRLA, 

  POR_REQUISITION_HEADERS_ALL PRHA 

WHERE 

  1 = 1 

  AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID 

  AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID 

  AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID 

  AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID 

  AND PRHA.REQUISITION_NUMBER = < Requsition_number >

 Query to get AP Invoice lines Tax details in Oracle Fusion 

SELECT 

  aia.invoice_num, 

  xep.name Legal_Entity_Name, 

  psv.vendor_name party, 

  aila.line_number Line_Ref, 

  aila.line_type_lookup_code, 

  gcc.segment1 Distribution_entity, 

  zl.tax_amt Tax_amount, 

  zl.tax_regime_code Tax_regime, 

  zl.tax_jurisdiction_code Tax_Juridiction, 

  zl.taxable_amt TaxableAmt, 

  ZE.exception_reason_code Exemption_Reason, 

  ZE.tax_rate_code Exemption_Rate, 

  zrb.country_code Country_of_taxation, 

  zl.tax_line_number Tax_Line_Ref, 

  zl.tax, 

  zl.tax_rate_code, 

  zl.tax_rate 

FROM 

  ap_invoices_all aia, 

  poz_suppliers_v psv, 

  xle_entity_profiles xep, 

  ap_invoice_lines_all aila, 

  gl_code_combinations gcc, 

  ap_invoice_distributions_all aida, 

  zx_lines zl, 

  zx_status_b zsb, 

  zx_exceptions ze, 

  zx_regimes_b zrb 

WHERE 

  1 = 1 

  AND psv.vendor_id = aia.vendor_id 

  AND aia.legal_entity_id = xep.legal_entity_id(+) 

  AND aia.invoice_id = aila.invoice_id 

  AND aila.invoice_id = aida.invoice_id 

  AND aila.line_number = aida.invoice_line_number 

  AND aia.invoice_id = zl.trx_id(+) 

  AND aila.line_number = zl.trx_line_number(+) 

  AND zl.application_id(+) = 200 

  AND ze.tax_exception_id(+) = zl.tax_exception_id 

  AND zsb.tax_status_id = zl.tax_status_id 

  AND zl.tax_regime_id = zrb.tax_regime_id 

  AND aida.dist_code_combination_id = gcc.code_combination_id 

  AND (

    aida.line_type_lookup_code = 'MISCELLANEOUS' 

    OR aida.line_type_lookup_code = 'FREIGHT' 

    OR aida.line_type_lookup_code = 'ITEM'

  ) 

  AND zsb.tax_status_code = 'EXEMPT'

 Query to fetch Level -1 BOM Details for an Inventory Item HOME » QUERY TO FETCH LEVEL -1 BOM DETAILS FOR AN INVENTORY ITEM 

SELECT 

  msi.segment1 “Parent material coding”, 

  msi.inventory_item_id, 

  msi.description “Parent item description”, 

  msi1.segment1 “Sub item code”, 

  msi1.inventory_item_id sub_item_id, 

  msi1.description “Sub item description” 

FROM 

  mtl_system_items_b msi, 

  bom_bill_of_materials bom, 

  bom_inventory_components bic, 

  mtl_system_items_b msi1 

WHERE 

  1 = 1 

  AND msi.organization_id = : organization_id 

  AND msi.inventory_item_id = bom.assembly_item_id 

  AND msi.organization_id = bom.organization_id 

  AND bom.organization_id = : organization_id 

  AND bom.alternate_bom_designator IS NULL 

  AND bom.bill_sequence_id = bic.bill_sequence_id 

  AND bic.disable_date IS NULL 

  AND bic.component_item_id = msi1.inventory_item_id 

  AND msi1.organization_id = : organization_id 

  AND msi.segment1 =: item_number;

 OM Related queries ============== Oracle Apps BOM Tables and Queries

BOM_DEPARTMENTS Departments 

BOM_DEPARTMENT_CLASSES Department classes 

BOM_DEPARTMENT_RESOURCES Resources associated with departments

BOM_OPERATIONAL_ROUTINGS Routings 

BOM_OPERATION_NETWORKS Routing operation networks 

BOM_OPERATION_RESOURCES Resources on operations 

BOM_OPERATION_SEQUENCES Routing operations

 BOM_OPERATION_SKILLS 

BOM_RESOURCES Resources, overheads, material cost codes, and material overheads 

BOM_STANDARD_OPERATIONS Standard operations 

BOM_ALTERNATE_DESIGNATORS Alternate designators 

BOM_COMPONENTS_B Bill of material components 

BOM_STRUCTURES_B Bills of material 

BOM_STRUCTURE_TYPES_B Structure Type master table 

BOM_BILL_OF_MATERIALS 

BOM_PARAMETERS

 BOM_DEPENDENT_DESC_ELEMENTS 

BOM_REFERENCE_DESIGNATORS 

BOM_SUBSTITUTE_COMPONENTS 

FND_DOCUMENTS 

FND_DOCUMENTS_TL 

FND_DOCUMENTS_SHORT_TEXT 

FND_ATTACHED_DOCUMENTS 

Select 

  * 

from 

  MFG_LOOKUPS 

WHERE 

  --LOOKUP_CODE =  'BOM_ITEM_TYPE'

  --LOOKUP_CODE =  'WIP_SUPPLY_TYPE'

  --LOOKUP_CODE =  'BOM_ASSEMBLY_TYPE'

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

1) Query for BOM Details 

SELECT 

  d.inventory_item_id, 

  d.concatenated_segments "Assembly_item", 

  e.organization_code "Organization", 

  nvl(

    a.ALTERNATE_BOM_DESIGNATOR, '10'

  ) "ALTERNATIVE BOM", 

  b.ITEM_NUM "ITEM", 

  c.concatenated_segments "COMPONENT", 

  b.component_quantity "UNIT", 

  c.primary_uom_code "QUANTITY", 

  d.primary_uom_code "UNIT" 

FROM 

  bom_bill_of_materials a, 

  bom_inventory_components b, 

  mtl_system_items_kfv c, 

  mtl_system_items_kfv d, 

  org_organization_definitions e 

WHERE 

  1 = 1 

  AND a.organization_id in (: P_organization_id) 

  AND (

    a.BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID 

    OR a.COMMON_BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID

  ) 

  AND b.implementation_date IS NOT NULL 

  AND b.DISABLE_DATE IS NULL 

  AND a.organization_id = c.organization_id 

  AND b.COMPONENT_ITEM_ID = c.inventory_ITEM_ID 

  AND a.assembly_item_id = d.inventory_item_id 

  AND a.organization_id = d.organization_id 

  AND a.organization_id = e.organization_id --AND    c.organization_id   = :P_organization_id

  --AND  a.ASSEMBLY_ITEM_ID  = :P_assembly_item_id

  -- AND  ALTERNATE_BOM_DESIGNATOR IS NULL

ORDER BY 

  e.organization_code, 

  d.inventory_item_id, 

  ALTERNATE_BOM_DESIGNATOR, 

  b.ITEM_NUM;

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

2) Query for Resources 

SELECT 

  c.concatenated_segments "Assemly_Item", 

  d.organization_code "Organization", 

  a.alternate_routing_designator "Alternate_routing", 

  b.operation_seq_num "OPERATION NUMBER", 

  e.department_code "WORK CENTER", 

  b.operation_description "DESCRIPTION(Operation)", 

  c.primary_uom_code "OPERATION UNIT", 

  G.uom "UNIT", 

  f.operation_code, 

  g.resource_code, 

  g.USAGE_RATE_OR_AMOUNT h.DESCRIPTION 

FROM 

  bom_operational_routings a, 

  bom_operation_sequences b, 

  mtl_system_items_kfv c, 

  org_organization_definitions d, 

  bom_departments e, 

  bom_standard_operations f, 

  bom_operation_resources_v g, 

  bom_resources h 

WHERE 

  a.organization_id in (: P_organization_id) 

  AND a.organization_id = d.organization_id 

  AND a.organization_id = c.organization_id 

  AND a.assembly_item_id = c.inventory_item_id 

  AND a.routing_sequence_id = b.routing_sequence_id 

  AND b.department_id = e.department_id 

  AND b.disable_date is null --AND   a.assembly_item_id = :P_assembly_item_id

  AND f.standard_operation_id(+) = b.standard_operation_id 

  AND g.operation_sequence_id = b.operation_sequence_id 

  AND h.resource_id = g.resource_id 

order by 

  d.organization_code, 

  c.concatenated_segments, 

  a.alternate_routing_designator, 

  b.operation_seq_num --------------------------------------------------------------------------------------------

3) Departments and Resources and Departments associated with Resources 

select 

  b.organization_code "Plant", 

  a.DEPARTMENT_CODE "Department", 

  a.DESCRIPTION "Department Description", 

  a.DEPARTMENT_CLASS_CODE "Department Class", 

  c.DESCRIPTION "Department Class Description", 

  d.location_code "Department_location" 

from 

  BOM_DEPARTMENTS a, 

  org_organization_definitions b, 

  BOM_DEPARTMENT_CLASSES c, 

  hr_locations d 

where 

  a.organization_id in (: P_organization_id) 

  AND a.organization_id = b.organization_id 

  AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE 

  AND a.organization_id = c.organization_id 

  AND a.location_id = d.location_id(+) 

select 

  b.organization_code "Plant", 

  a.RESOURCE_CODE "Resource Name", 

  a.DESCRIPTION "Resource Description", 

  a.UNIT_OF_MEASURE 

from 

  BOM_RESOURCES a, 

  org_organization_definitions b --,mfg_lookups c

where 

  a.organization_id in (: P_organization_id) 

  AND a.organization_id = b.organization_id 

  AND a.Disable_date is Null --AND    c.lookup_type = 'BOM_BASIS_TYPE'

  --AND    c.LOOKUP_CODE = a.DEFAULT_BASIS_TYPE

  exec mo_global.set_policy_context('S',: P_org_id);

--BOM_RESOURCE_TYPE

--BOM_BASIS_TYPE

select 

  * 

from 

  mfg_lookups 

where 

  lookup_type like 'BOM_BASIS_TYPE' 

select 

  * 

from 

  BOM_DEPARTMENT_RESOURCES 

select 

  * 

from 

  BOM_RESOURCE_SHIFTS 

select 

  b.organization_code "Plant", 

  a.DEPARTMENT_CODE "Department", 

  a.DESCRIPTION "Department Description", 

  a.DEPARTMENT_CLASS_CODE "Department Class", 

  c.DESCRIPTION "Department Class Description", 

  d.location_code "Department_location", 

  f.RESOURCE_CODE "Resource Name", 

  f.DESCRIPTION "Resource Description", 

  f.UNIT_OF_MEASURE 

from 

  BOM_DEPARTMENTS a, 

  org_organization_definitions b, 

  BOM_DEPARTMENT_CLASSES c, 

  hr_locations d, 

  BOM_DEPARTMENT_RESOURCES e, 

  BOM_RESOURCES f -- ,BOM_RESOURCE_SHIFTS g

where 

  a.organization_id in (: P_oranization_id) 

  AND a.organization_id = b.organization_id 

  AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE 

  AND a.organization_id = c.organization_id 

  AND a.location_id = d.location_id(+) 

  AND a.DEPARTMENT_ID = e.DEPARTMENT_ID 

  AND e.RESOURCE_ID = f.RESOURCE_ID --AND    a.DEPARTMENT_ID = g.DEPARTMENT_ID

  --AND    f.RESOURCE_ID = g.RESOURCE_ID

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

  Routing Open Interface Concurrent: Bill 

  and Routing Interface -- Create Routing

  insert into bom_op_routings_interface (

    assembly_item_id, process_revision, 

    process_flag, transaction_type, 

    routing_type, organization_id

  ) 

values 

  (

    218967, 

    Null, 

    1, 

    --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).

    'CREATE', 

    1, 

    ----1) manufacturing, 2) engineering

    204

  );

-- Create Routing Sequences

insert into bom_op_sequences_interface (

  assembly_item_id, operation_seq_num, 

  department_id, department_code, 

  process_flag, transaction_type, 

  organization_id, effectivity_date

) 

values 

  (

    218967, 10, 42877, 'PD1', 1, 'CREATE', 

    204, sysdate

  );

-- Create Operation Resources

insert into bom_op_resources_interface (

  resource_seq_num, 

  resource_id, 

  resource_code, 

  usage_rate_or_amount, 

  --basis_type ,

  assigned_units, 

  --schedule_flag,autocharge_type,

  assembly_item_id, 

  operation_seq_num, 

  --operation_sequence_id,

  process_flag, 

  transaction_type, 

  effectivity_date, 

  organization_id, 

  schedule_flag, 

  schedule_seq_num

) 

values 

  (

    10, 

    90529, 

    'PR1', 

    1, 

    1, 

    218967, 

    10, 

    --2346216 ,

    1, 

    'CREATE', 

    sysdate, 

    204, 

    1, 

    10

  );

select 

  * 

from 

  mtl_system_items_kfv 

where 

  inventory_item_id = 133 

  and organization_id = 204 

select 

  * 

from 

  bom_departments 

where 

  department_id = 42877 

  and organization_id = 204 

select 

  * 

from 

  BOM_DEPARTMENT_RESOURCES 

where 

  department_id = 42877 

select 

  * 

from 

  bom_resources 

where 

  resource_id = 90529 

select 

  * 

from 

  bom_op_resources_interface

 A printer type identifies a printer by manufacturer and model. A print style tells the printer how a printed output should look. A printer driver delivers commands that tell the printer how to output the specified print style. The ability to print a report in a particular print style depends on the type of printer the report file is sent to.

 Setting the Applications Context FND_GLOBAL.APPS_INITIALIZE in oracle applications: