Get Invoice Number from PO Number

SELECT ap_inv.invoice_num,
po_hdr.segment1,
po_hdr.org_id
FROM ap_invoices_all ap_inv,
ap_invoice_distributions_all ap_dis,
po_distributions_all po_dis,
po_headers_all po_hdr
WHERE ap_inv.invoice_id = ap_dis.invoice_id
AND ap_dis.po_distribution_id = po_dis.po_distribution_id
AND po_dis.po_header_id = po_hdr.po_header_id
AND ap_inv.org_id = po_hdr.org_id
AND po_hdr.segment1 like ‘Give PO Number’
–AND ap_inv.invoice_num like ‘Give Invoice Number’

SQL QUERIES TO GET ORGANIZATION FROM REQUISITION, PURCHASE ORDER, PO RELEASE

From Requisition:

    select hr.name, prh.segment1, prh.org_id

    from po_requisition_headers_all prh,

    hr_all_organization_units hr

    where prh.org_id = hr.organization_id and

    prh.segment1 = '&Enter_Req_Number';

From Purchase Order:

    select hr.name, poh.segment1, poh.org_id

    from po_headers_all poh,

    hr_all_organization_units hr

    where poh.org_id = hr.organization_id and

    poh.segment1 = '&Enter_PO_Number';

From Purchase Order Release:
    select hr.name, poh.segment1, por.release_num, por.org_id

    from po_headers_all poh,

    po_releases_all por,

    hr_all_organization_units hr

    where poh.org_id = hr.organization_id and

    por.org_id = poh.org_id and

    poh.po_header_id = por.po_header_id and

    poh.segment1 = '&Enter_PO_Number' and

    por.release_num = '&Enter_Release_Num';

BIACM Queries

When Creating a Document on Domain values that have been mapped to target values etc in BIACM we can use the BIACOMP schema to derive various pieces of information.

Below are some querys that can be used to get information on mappings etc.

Query to get Offerings enabled
select offering_code,offering_name
from c_bia_offering
where installed_flag = 1;


Query to get offering, implementation project, task list

SELECT ATLT.TASK_LIST_NAME,
  ATT.Task_name,
  AIPT.IMPL_PROJECT_NAME,
  AIEI.*
FROM ASM_IP_EXECUTION_ITEMS AIEI,
  ASM_TASK_LISTS_TL ATLT,
  ASM_TASK_LIST_ITEMS ATLI ,
  ASK_TASKS_TL ATT ,
  ASM_IMPL_PROJECTS_TL AIPT
WHERE ATLT.TASK_LIST_ID   =AIEI.TASK_LIST_ID
AND ATLI.TASK_LIST_ITEM_ID=AIEI.TASK_LIST_ITEM_ID
AND ATT.TASK_ID           =ATLI.ITEM_TASK_ID
AND AIPT.IMPL_PROJECT_ID  =AIEI.IMPL_PROJECT_ID
AND ATLT.language ='US'
AND ATT.Language  ='US'
AND AIPT.Language ='US'
  --AND TASK_LIST_NAME= 'Oracle Financial Analytics' --Give the required Module name here
AND AIPT.IMPL_PROJECT_NAME
LIKE 'Financials _ P and S Implementation Project' ; -- Give your implementation project name here

--Query to get standard domain Mappings list in biacm which are loaded with BIAPPS installation

SELECT src.domain_code source_domain_code,
  trg.domain_code target_domain_code,
  srct.domain_name source_domain_name,
  trgt.domain_name target_domain_name
FROM c_domain_map cdm ,
  c_domain_tl srct,
  c_domain_tl trgt,
  c_domain src,
  c_domain trg
WHERE cdm.TRG_DOMAIN_KEY=trgt.DOMAIN_KEY
AND cdm.SRC_DOMAIN_KEY  =srct.DOMAIN_KEY
AND trg.DOMAIN_KEY      =trgt.DOMAIN_KEY
AND src.DOMAIN_KEY      =srct.DOMAIN_KEY
AND trgt.LANGUAGE_CODE  ='US'
AND srct.language_code  ='US'
AND src.domain_code     ='COUNTRY';