BOM Extract Query 11i

--Bill Of materials Extract Queries

SELECT
m.organization_id,
m.segment1 assembly_item,
b.alternate_bom_designator,
--m.REVISION,
--b.assembly_item_id,
--b.bill_sequence_id assembly_bom_seqid,
c.item_num item_seq,
c.operation_seq_num,
m1.segment1 component_item,
c.component_quantity,
c.component_yield_factor,
(SELECT meaning FROM fnd_lookup_values WHERE lookup_type='WIP_SUPPLY'AND language ='US'AND lookup_code =c.wip_supply_type)supply_type,
c.supply_subinventory,
(SELECT segment1 FROM apps.mtl_item_locations WHERE  organization_id = 1657 AND inventory_location_id =c.supply_locator_id)supply_locator,
c.check_atp,
c.component_remarks comments,
--c.component_item_id,
--c.bill_sequence_id component_bom_seqid
b.creation_date
FROM apps.bom_bill_of_materials b,
apps.mtl_system_items m,
apps.bom_inventory_components c,
apps.mtl_system_items m1
WHERE b.organization_id = 1657
AND b.assembly_item_id = m.inventory_item_id
AND c.component_item_id = m1.inventory_item_id
AND m1.organization_id = m.organization_id
AND c.bill_sequence_id = b.bill_sequence_id
--AND m.planning_make_buy_code = 1
AND m.enabled_flag = 'Y'
AND m.organization_id = b.organization_id
--AND m.segment1='A196298000'
AND to_date(trunc(b.creation_date))='02-SEP-14'
ORDER BY 2;

AR Transaction types SQL Query 11i

--AR Transactions SQL Query
SELECT
  (SELECT NAME FROM apps.hr_operating_units WHERE organization_id=rct.org_id
  ) ou_name ,
  rct.NAME ,
  (SELECT meaning
  FROM ar_lookups
  WHERE lookup_type='INV/CM'
  AND lookup_code  =rct.TYPE
  ) CLASS ,
  rct.description ,
  rct.post_to_gl ,
  rct.default_printing_option ,
  (SELECT meaning
  FROM ar_lookups
  WHERE lookup_type='INVOICE_TRX_STATUS'
  AND lookup_code  =rct.default_status
  )transaction_status,
  rct.allow_freight_flag ,
  rct.accounting_affect_flag ,
  rct.status ,
  (SELECT NAME FROM ra_terms WHERE term_id=rct.default_term
  ) terms,
  (SELECT NAME
  FROM ra_cust_trx_types_all
  WHERE cust_trx_type_id=rct.subsequent_trx_type_id
  ) invoice_type ,
  -- RCT.SET_OF_BOOKS_ID ,
  rct.allow_overapplication_flag ,
  rct.tax_calculation_flag ,
  rct.creation_sign ,
  (SELECT rule_set_name
  FROM apps.ar_app_rule_sets
  WHERE rule_set_id=rct.rule_set_id
  )application_rule_set,
  (SELECT NAME
  FROM ra_cust_trx_types_all
  WHERE cust_trx_type_id=rct.credit_memo_type_id
  )credit_memo_type ,
  rct.start_date ,
  rct.end_date ,
  rct.allocate_tax_freight,
  rct.signed_flag ,
  rct.drawee_issued_flag ,
  rct.natural_application_only_flag ,
  --RCT.MAGNETIC_FORMAT_CODE ,
  (
  SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_rec
  )receivable_acc,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_rev
  )revenue_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_unpaid_rec
  )unapp_receivable_acc ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_tax
  ) tax_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_freight
  )frieght_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_clearing
  )clearing_account ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_unearned
  )unern_revenue_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_unbilled
  )unbilled_account ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_remittance
  )remittance_account ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_factor
  )factor_account
FROM ra_cust_trx_types_all rct
WHERE org_id=:p_org_id
  --AND NAME='EIT Deposit'--'EIT Deposit';
  ;

AP AR Payment Terms Extract Query 11i

AR payments terms

SELECT t.NAME ,
  t.description,
  b.base_amount,
  b.partial_discount_flag,
  nvl(b.prepayment_flag,'N'),
  b.credit_check_flag,
  al.meaning discount_basis,
  b.start_date_active,
  b.end_date_active,
  b.printing_lead_days,
  b.due_cutoff_day,
  al.meaning installment_option,
  b.in_use,
  b.term_id,
  rtl.sequence_num,
  rtl.relative_amount,
  rtl.due_days,
  rtl.due_date,
  rtl.due_day_of_month,
  rtl.due_months_forward
FROM apps.ra_terms_tl t,
  apps.ra_terms_b b,
  apps.ar_lookups al,
  apps.ar_lookups al1,
  apps.ra_terms_lines rtl
WHERE b.term_id = t.term_id
AND rtl.term_id(+)=b.term_id
AND SYSDATE BETWEEN nvl(b.start_date_active,SYSDATE) AND nvl(b.end_date_active,SYSDATE)
AND al.lookup_code=  b.calc_discount_on_lines_flag
AND al.lookup_type='DISCOUNT_BASIS'
AND al1.lookup_type='INSTALLMENT_OPTION'
AND al1.lookup_code=    b.first_installment_code
AND t.language  = userenv('LANG')
ORDER BY 1;


--AP Payment terms

SELECT  b.NAME,
  b.description,
  b.term_id,
  b.enabled_flag,
  b.due_cutoff_day,
  b.TYPE,
  b.start_date_active,
  b.end_date_active,
  b.rank  ,
  atl.sequence_num,
  atl.due_percent,
  atl.due_amount,
  atl.fixed_date,
  atl.calendar,
  atl.due_days,
  atl.due_day_of_month,
  atl.due_months_forward,
  atl.discount_percent,
  atl.discount_days,
  atl.discount_day_of_month,
  atl.discount_months_forward,
  atl.discount_percent_2,
  atl.discount_days_2,
  atl.discount_day_of_month_2,
  atl.discount_months_forward_2,
  atl.discount_percent_3,
  atl.discount_days_3,
  atl.discount_day_of_month_3,
  atl.discount_months_forward_3
FROM apps.ap_terms_tl b,
 ap_terms_lines atl
WHERE b.language = userenv('LANG')
AND b.term_id=atl.term_id(+)
 AND SYSDATE BETWEEN nvl(b.start_date_active,SYSDATE) AND nvl(b.end_date_active,SYSDATE)
 ORDER BY 1;

SQL Query to get Session ID and Kill the session

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
 FROM apps.fnd_concurrent_requests a,
 apps.fnd_concurrent_processes b,
 v$process c,
 v$session d
 WHERE a.controlling_manager = b.concurrent_process_id
 AND c.pid = b.oracle_process_id
 AND b.session_id=d.audsid
 AND a.request_id = 277287411
 --AND a.phase_code = 'R'
 ;
alter system kill session '1403,15205'

AP AR tax Code Extract Query

--AP Tax Codes

SELECT atc.*,
    gcc.segment1
    ||'-'
    ||gcc.segment2
    ||'-'
    ||gcc.segment3
    ||'-'
    ||gcc.segment4
    ||'-'
    ||gcc.segment5
    ||'-'
    ||gcc.segment6
    ||'-'
    ||gcc.segment7
    ||'-'
    ||gcc.segment8
    ||'-'
    ||gcc.segment9
    ||'-'
    ||gcc.segment10 tax_code_combination  
FROM apps.ap_tax_codes_all atc,
apps.gl_code_combinations gcc
WHERE
gcc.code_combination_id = atc.tax_code_combination_id
AND atc.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(atc.start_date, SYSDATE) AND nvl(atc.inactive_date,SYSDATE)
AND atc.org_id     = 2659;

-- AR tax Code:

SELECT avtb.* ,
  gcc.segment1
  ||'-'
  ||gcc.segment2
  ||'-'
  ||gcc.segment3
  ||'-'
  ||gcc.segment4
  ||'-'
  ||gcc.segment5
  ||'-'
  ||gcc.segment6
  ||'-'
  ||gcc.segment7
  ||'-'
  ||gcc.segment8
  ||'-'
  ||gcc.segment9
  ||'-'
  ||gcc.segment10 tax_code_combination
FROM apps.ar_vat_tax_all_b avtb,
  apps.ar_vat_tax_all_tl avtt,
  apps.gl_code_combinations gcc
WHERE gcc.code_combination_id (+) = avtb.tax_account_id
AND avtb.vat_tax_id               = avtt.vat_tax_id
AND avtt.language                 = 'US'
AND avtb.enabled_flag             = 'Y'
AND SYSDATE BETWEEN nvl(avtb.start_date, SYSDATE) AND nvl(avtb.end_date,SYSDATE)
AND avtt.org_id = :p_org_id