Oracle Ebs-sql (PO-5): Purchase order control information query. SQL

SELECT DISTINCT

PLA.PO_HEADER_ID,

--pha.type_lookup_code,

PHA.SEGMENT1 Purchase order number,

Appf.full_name Buyer,

Pla.line_num order lines,

Msi.segment1 Material Code,

Pla.item_description Material Description,

Case

When Pha. authorization_status= ' approved ' then ' approved '

When pha.authorization_status= ' REQUIRES reapproval ' then ' requires re-approval '

When pha.authorization_status= ' in process ' then ' processing '

When pha.authorization_status= ' rejected ' and then ' refuses '

Else ' not completed '

End Approval Status,

--Decode (Pha.approved_flag, case

--' Y ', when pha.approved_flag= ' y ' and then ' approved '

--' approved ',

--' n ', when pha.approved_flag= ' n ' Then ' processing '

--' In process ',

--' R ', when the pha.approved_flag= ' R ' then ' requires re-approval '

--' request for re-approval ',

--' f ', when pha.approved_flag= ' F ' then ' reject '

--' reject ', Else ' not completed '

--' not completed ') Approval status,

End Approval Status,

Case

When Pla.closed_code= ' closed ' and then ' closed '

When pla.closed_code= ' finally closed ' and then ' finally closed '

Else ' open '

End off state,

Case

When Plla.closed_code= ' closed ' and then ' closed '

When Plla.closed_code= ' closed for receiving ' then ' receive off '

When Plla.closed_code= ' closed for INVOICE ' then ' Invoice off '

When plla.closed_code= ' finally closed ' and then ' finally closed '

Else ' open '

End sends the running state,

--pla.closed_code State,

Number of plla.quantity,

--plla. The number of quantity_due expires,

--(Plla.quantity-plla.quantity_received) the number expires,

Plla. quantity_received Receive Quantity,

--plla. quantity_accepted Acceptance Quantity,

Plla. Quantity_rejected rejected quantity,

Plla. quantity_billed Number of open orders,

Plla. quantity_cancelled Cancel Quantity,

(Plla. quantity_received-plla.quantity_billed) not open singular,

Ppv.vendor_name suppliers,

/*case

When Ppv.vendor_type_lookup_code= ' A_vendor ' and ' then ' fixed asset supplier '

When Ppv.vendor_type_lookup_code= ' L_vendor ' and ' then ' spare parts suppliers '

When the ppv.vendor_type_lookup_code= ' M_vendor ' then ' material supplier '

When ppv.vendor_type_lookup_code= ' F_vendor ' then ' finished supplier '

When Ppv.vendor_type_lookup_code= ' C_vendor ' and ' Then ' cost funding class suppliers '

When ppv.vendor_type_lookup_code= ' Vendor ' then ' Supplier '

When ppv.vendor_type_lookup_code= ' public SECTOR COMPANIES ' then ' PR Department company '

When ppv.vendor_type_lookup_code= ' taxes Authority ' then ' the tax authority '

When ppv.vendor_type_lookup_code= ' employee ' then ' employees '

When Ppv.vendor_type_lookup_code= ' S_vendor ' and ' Then ' Install maintenance class supplier '

When ppv.vendor_type_lookup_code= ' O_vendor ' then ' other suppliers '

Else '

End Vendor Type, */

Pvs.vendor_site_code Suppliers Location,

Pla.unit_price Unit Price,

Pla.creation_date Date Created,

Pla.last_update_date Date Modified,

Plla.promised_date Commitment Date,

Plla.need_by_date Demand Date,

Pha.comments Order Description

From Po.po_headers_all Pha,

Po.po_lines_all PLA,

Po.po_line_locations_all Plla,

Inv.mtl_system_items_b MSI,

Po.po_vendors PPV,

Po.po_vendor_sites_all PVs,

Apps.per_people_f APPF

where pha.po_header_id=pla.po_header_id

and pla.item_id=msi.inventory_item_id

and plla.po_header_id=pha.po_header_id

and msi.organization_id=x

and pha.org_id=y

and plla.po_line_id=pla.po_line_id

and pha.vendor_id=ppv.vendor_id

and pha.agent_id=appf.person_id

and pvs.vendor_site_id=pha.vendor_site_id and pha.type_lookup_code= ' standards '--standard purchase order--' BLANKET '

--and decode (Pla.closed_code, ', ' open ', pla.closed_code) = ' open '--find not closed order line

--and pla.creation_date between To_date (' 20**-01-01 ', ' yyyy-mm-dd ') and to_date (' 20**-01-31 ', ' yyyy-mm-dd ')

and Pla.creation_date > To_date (' 20**-01-15 ', ' yyyy-mm-dd ')

and Msi.item_type = ' OP ' and appf.full_name not like ' ERP worker% '

and decode (Plla.cancel_flag, ', ' n ', plla.cancel_flag) = ' n '

--Find non-canceled order lines--and Pla.creation_date < to_date (' 20**-02-01 ', ' yyyy-mm-dd ')

--and (pla.quantity-plla.quantity_received) >0--Find orders with remaining number

--and plla.quantity_received > 0--and pla.unit_price=0

--and plla.quantity >1000000

ORDER BY Pha.segment1,

Pla.line_num