FUSION: Purchasing Query

 

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