Report on inactive items that exists on active PO-s

SAMPLE QUERY

==================

po_lines_v
po_headers_v
po_headers_all
po_headers
FINANCIALS_SYSTEM_PARAMETERS
select * from po_lines_all where item_id=7032
select * from org_organization_definitions
select org_id,item_id,item_description from PO_LINES_ALL

select segment1,description,segment1,inventory_item_id from mtl_system_items
select organization_id,transaction_date,inventory_item_id from mtl_material_transactions
select org_id,item_id,item_description,creation_date from PO_LINES_ALL where org_id=204 and creation_date<=sysdate

select msi.segment1,msi.description,msi.inventory_item_id,mmt.subinventory_code,mmt.transaction_date,mmt.organization_id
from mtl_system_items msi,mtl_material_transactions mmt,po_lines_all pla
where msi.organization_id=mmt.organization_id and msi.inventory_item_id=mmt.inventory_item_id and mmt.organization_id=209
and mmt.transaction_date<='01-aug-2000' and msi.inventory_item_id=pla.item_id

select agent_name from PO_HEADERS
 where po_header_id=11965
mtl_material_transactions
mtl_categories
select inventory_item_id,segment1,organization_id from mtl_system_items where organization_id=2871

select buyer_name from PO_HEADERS_ALL
po_lines_all
PO_DOCUMENT_TYPES PDT, PO_LOOKUP_CODES POLC, PO_LOOKUP_CODES POLC2, PO_LOOKUP_CODES POLC3, PO_LOOKUP_CODES POLC4, GL_DAILY_CONVERSION_TYPES GLDC, AP_TERMS AT,
PER_PEOPLE_F P, PO_VENDORS V, PO_VENDOR_SITES VS, PO_VENDOR_CONTACTS VC, HR_LOCATIONS_ALL_TL HRL1, HR_LOCATIONS_ALL_TL HRL2, PO_HEADERS POH


select agent_name from PER_ALL_PEOPLE_F
select * from po_buyers_val_v
select full_name agent_name from po_buyers_val_v where employee_id=(select agent_id from po_headers_all where
po_header_id=(select po_header_id from po_lines_all where po_line_id=

=====================================================================

TABLES

===

SELECT POH.SEGMENT1 PO,PDTA.TYPE_NAME,POH.CREATION_DATE CREATED,POV.VENDOR_NAME SUPPLIER,HLAT.LOCATION_CODE SHIP_TO,
HLA.LOCATION_CODE BILL_TO,POH.CURRENCY_CODE CURRENCY,PAPF.FULL_NAME BUYER,PVSL.VENDOR_SITE_CODE SITE,
PVC.LAST_NAME||','||PREFIX||''||PVC.FIRST_NAME||' '||PVC.MIDDLE_NAME CONTACT,

PLA.LINE_NUM NUM,PLTT.LINE_TYPE TYPE,PLA.ITEM_DESCRIPTION,MSI.SEGMENT1 ITEM,PLA.ITEM_REVISION,
PLA.UNIT_MEAS_LOOKUP_CODE UOM,PLA.QUANTITY,PLA.UNIT_PRICE PRICE,PLLA.CREATION_DATE
FROM PO_HEADERS_ALL POH,
PO_VENDORS POV,
PER_ALL_PEOPLE_F PAPF,PO_VENDOR_SITES_ALL PVSL,HR_LOCATIONS_ALL_TL HLAT,
HR_LOCATIONS_ALL HLA,PO_VENDOR_CONTACTS PVC,PO_DOCUMENT_TYPES_ALL_TL PDTA,
PO_LINES_ALL PLA,PO_LINE_TYPES_TL PLTT,MTL_SYSTEM_ITEMS MSI,PO_LINE_LOCATIONS_ALL PLLA
WHERE POH.VENDOR_ID=POV.VENDOR_ID AND POH.TYPE_LOOKUP_CODE=PDTA.DOCUMENT_SUBTYPE AND
HLAT.LOCATION_ID=POV.SHIP_TO_LOCATION_ID AND
HLA.LOCATION_ID=POV.BILL_TO_LOCATION_ID
AND POH.AGENT_ID=PAPF.PERSON_ID AND POH.VENDOR_SITE_ID=PVSL.VENDOR_SITE_ID AND
PVC.VENDOR_CONTACT_ID=POH.VENDOR_CONTACT_ID
AND POH.PO_HEADER_ID=11786 AND PLA.PO_HEADER_ID=POH.PO_HEADER_ID AND PLTT.LINE_TYPE_ID=PLA.LINE_TYPE_ID
AND (MSI.INVENTORY_ITEM_ID=PLA.ITEM_ID) AND
(PLLA.PO_HEADER_ID=PLA.PO_HEADER_ID AND PLLA.PO_LINE_ID=PLA.PO_LINE_ID);


po_lines_v
PO_LINE_TYPES,MTL_UNITS_OF_MEASURE,AP_TAX_CODES,PO_UN_NUMBERS,PO_HAZARD_CLASSES,
PO_LOOKUP_CODES,PO_LOOKUP_CODES,MTL_SYSTEM_ITEMS,FINANCIALS_SYSTEM_PARAMETERS,PO_LINES
==============

QUERY

=================

select    decode(:P_break_id, 1 , mmt.subinventory_code, 'X')  subinventory,
    &P_item_flex        C_item_flex,
    mmt.revision            rev,
    msi.description,
    &P_cat_flex        C_cat_flex,
    msi.inventory_item_status_code    status,
    max(mmt.last_update_date)    Last_txn_date,
    nvl(sum(mmt.primary_quantity),0)    Quantity,
    msi.primary_uom_code        uom
from    mtl_system_items         msi,
    mtl_material_transactions    mmt,
    mtl_item_categories        mic,
    mtl_categories        mc
where    mmt.inventory_item_id = msi.inventory_item_id
and     mmt.organization_id = :P_org_id
and     msi.organization_id = :P_org_id
and    msi.inventory_item_flag = 'Y'
and    mmt.inventory_item_id =  mic.inventory_item_id
and    mic.category_set_id = :P_cat_set_id
and    mic.organization_id = :P_org_id
and    mic.category_id = mc.category_id
&C_subinv_where
group by decode(:P_break_id, 1 , mmt.subinventory_code, 'X'),
    &P_item_flex,mmt.revision,msi.description,
    &P_cat_flex,msi.inventory_item_status_code,msi.primary_uom_code
having max(mmt.transaction_date) <= :P_cutoff_date
order by  mmt.revision, msi.description,
msi.inventory_item_status_code




select msi.segment1,msi.description,msi.inventory_item_id,mmt.transaction_date,mmt.organization_id
from mtl_system_items msi,mtl_material_transactions mmt,po_lines_all pla
where msi.organization_id=mmt.organization_id and msi.inventory_item_id=mmt.inventory_item_id and mmt.organization_id=204
and mmt.transaction_date<='01-aug-1999' and msi.inventory_item_id=pla.item_id




select * from po_buyers_val_v
select full_name agent_name from po_buyers_val_v where employee_id=(select agent_id from po_headers_all where
po_header_id=(select po_header_id from po_lines_all where po_line_id= 


(mmt.organization_id=:orgid or msi.buyer_id in (select :buyerid from mtl_system_items))







select  msi.segment1,msi.description,msi.inventory_item_id,mmt.subinventory_code,
    max(mmt.last_update_date) last_trans_date,msi.buyer_id,mmt.organization_id

from    mtl_system_items msi,mtl_material_transactions mmt,po_lines_all pla

where   msi.organization_id=mmt.organization_id and
    msi.inventory_item_id=mmt.inventory_item_id and
    pla.org_id=mmt.organization_id and
    mmt.organization_id=:orgid and
    msi.buyer_id in (select :buyerid from mtl_system_items))and
    msi.inventory_item_id=pla.item_id and
    mmt.inventory_item_id=pla.item_id and 
    pla.closed_code!='closed'

group by msi.segment1,msi.description,msi.inventory_item_id,mmt.subinventory_code, msi.buyer_id,mmt.organization_id
having max(mmt.transaction_date)<=:enterdate


====================================================================

AFTER PARAMETER FORM

==========

function AfterPForm return boolean is
begin
  if :buyerid is null and :orgid is not null then
      :pass:='and mmt.organization_id='||:orgid;
  elsif :buyerid is not null and :orgid is null then
      :pass:='and msi.buyer_id='||:buyerid;
  elsif :buyerid is not null and :orgid is not null then
      :pass:='and msi.buyer_id='||:buyerid||'and mmt.organization_id='||:orgid;
    end if;
    return (TRUE);
end;

============================

FINAL QUERY

==============

select  msi.segment1,
    msi.description,
    msi.inventory_item_id,
    mmt.subinventory_code,
    max(mmt.transaction_date) last_trans_date,
    msi.inventory_item_status_code,
    msi.buyer_id,
    mmt.organization_id
from    mtl_system_items msi,
    mtl_material_transactions mmt,
    po_lines_all pla
where   msi.organization_id=mmt.organization_id and
    msi.inventory_item_id=mmt.inventory_item_id and
        msi.inventory_item_id=pla.item_id and    
    mmt.inventory_item_id=pla.item_id and 
    msi.inventory_item_flag = 'Y' and
    msi.purchasing_item_flag='Y' and 
    ((expiration_date>sysdate or expiration_date is null) and
    (closed_code!='CLOSED' or closed_code is null))  &pass
group by msi.segment1,msi.description,msi.inventory_item_id,mmt.subinventory_code,
     msi.inventory_item_status_code, msi.buyer_id,mmt.organization_id

================================================================END===