1) ---------CPA to PO Amount----------
select poh.segment1, (select sum(nvl(cpol.quantity,1)* cpol.unit_price) from po_headers_all cpoh,po_lines_all cpol
where cpol.contract_id=poh.po_header_id
and cpoh.po_header_id=cpol.po_header_id ) po_amount
from po_headers_all poh
where poh.segment1='2024-003'
--and poh.po_header_id=pol.po_header_id
--and poh.type_lookup_code ='CONTRACT'
2)------------contract to PO Wise---
select cpoh.segment1 CPA_PO_NUM,poh.segment1 PO_NUM, sum(nvl(pol.quantity,1)* pol.unit_price) po_amount
from po_headers_all cpoh, from po_headers_all poh,po_lines_all pol
where cpoh.segment1='2024-003'
and cpoh.po_header_id=cpol.contract_id
and poh.po_header_id=pol.po_header_id
and cpoh.type_lookup_code ='CONTRACT'
3) -------------PO Depart Query ( Cost Center Segment)-----------
(select substr(c_desc,instr(c_desc,'.',1,1)+1, instr(c_desc,'.',1,2)-1 - instr(c_desc,'.',1,1)) charge_acct_desc
from (select
POR_UTIL_PVT.GET_CHARGE_ACCOUNT_DESC('GL','GL#',GLL.chart_of_accounts_id,
pda.CODE_COMBINATION_ID) c_desc
from --PO_HEADERS_ALL pha,
PO_DISTRIBUTIONS_ALL pda,GL_LEDGERS GLL
where pda.PO_HEADER_ID=header.PO_HEADER_ID
--and pha.po_header_id=pda.po_header_id
AND PDA.SET_OF_BOOKS_ID = GLL.LEDGER_ID
)where rownum=1)DEPT_NAME
#Fusion Purchasing Query#Oracle
