Purchasing Useful Query

1)QUERY TO BE PREAPRED TO LIST ALL THE REQUISTIONS WITH STATUS
REQNO,DOF REQ,APPROVED BY,AMOUNT OF REQ,STATUS,DESCRIPTION

/* Formatted on 9/4/2014 2:08:50 PM (QP5 v5.115.810.9015) */
SELECT h.segment1,
       h.creation_date,
       h.authorization_status,
       h.description,
       l.to_person_id,
       ah.action_code,
       e.full_name,
       l.quantity * l.unit_price "req.amt"
FROM po_requisition_headers_all h,
     po_requisition_lines_all l,
     po_action_history ah,
     hr_employees e
WHERE     h.requisition_header_id = l.requisition_header_id
      AND h.requisition_header_id = ah.object_id
      AND ah.employee_id = e.employee_id
      AND h.segment1 = TO_CHAR (6076);
     
2)QUERY TO PULL THE DEATILS OF RECEIVING TRANSACTION
INVENTORY_CODE,INVEN NAME,ITEM NO,ORDERD QUANTITY,RECEIVED QUANTITY,
VENDOR NAME,VENDOR ADD AND RECEIPT NO

/* Formatted on 9/4/2014 2:09:14 PM (QP5 v5.115.810.9015) */
SELECT msi.secondary_inventory_name,
       msi.description,
       msib.segment1,
       pla.quantity,
       SUM (rsl.quantity_received),
       pv.vendor_name,
          pvsa.address_line1
       || ' '
       || pvsa.address_line2
       || ' '
       || pvsa.city
       || ' '
       || pvsa.state
          "vendor address",
       ft.territory_short_name,
       rsh.receipt_num
FROM po_headers_all pha,
     po_lines_all pla,
     mtl_system_items_b msib,
     rcv_shipment_headers rsh,
     rcv_shipment_lines rsl,
     mtl_secondary_inventories msi,
     po_vendors pv,
     po_vendor_sites_all pvsa,
     fnd_territories_tl ft
WHERE     pv.vendor_name = 'Supriya'
      --and pha.segment1 = '6076'
      AND pha.po_header_id = pla.po_header_id
      AND pla.item_id = msib.inventory_item_id
      AND pha.org_id = msib.organization_id
      AND rsh.shipment_header_id = rsl.shipment_header_id
      AND pha.po_header_id = rsl.po_header_id
      AND pla.po_line_id = rsl.po_line_id
      AND rsl.to_organization_id = msi.organization_id
      AND rsl.to_subinventory = msi.secondary_inventory_name
      AND pha.vendor_id = pv.vendor_id
      AND pha.vendor_site_id = pvsa.vendor_site_id
      AND pvsa.country = ft.territory_code
GROUP BY msi.secondary_inventory_name,
         msi.description,
         msib.segment1,
         pla.quantity,
         pv.vendor_name,
         pvsa.address_line1,
         pvsa.address_line2,
         pvsa.city,
         pvsa.state,
         ft.territory_short_name,
         rsh.receipt_num;

3)QUERY TO PULL THE DETAILS OF PURCHASE ORDERS
PO NO,PO TYPE,PO CREATOR NAME,BUYER,APPROVER,STATUS,ITEM NO,ITEM DESCRIPTION,PO AMOUNT


/* Formatted on 9/4/2014 2:09:35 PM (QP5 v5.115.810.9015) */
SELECT pha.segment1,
       plct.description,
       hpc.party_name "po_creator",
       hpa.party_name "po_approver",
       plcs.displayed_field,
       plcs.description,
       msib.segment1,
       msib.description,
       (pla.unit_price * pla.quantity)
FROM po_headers_all pha,
     hz_parties hpc,
     po_action_history pah,
     po_lookup_codes plct,
     hz_parties hpa,
     po_lookup_codes plcs,
     po_lines_all pla,
     mtl_system_items_b msib
WHERE     1 = 1
      AND pha.segment1 = '4947'
      AND pha.agent_id = hpc.person_identifier
      AND pha.po_header_id = pah.object_id
      AND pah.action_code = 'APPROVE'
      AND plct.lookup_type = 'PO TYPE'
      AND plct.lookup_code = pah.object_sub_type_code
      AND pah.employee_id = hpa.person_identifier
      AND plcs.lookup_type = 'AUTHORIZATION STATUS'
      AND pha.authorization_status = plcs.lookup_code
      AND pha.po_header_id = pla.po_header_id
      AND pla.item_id = msib.inventory_item_id
      AND pha.org_id = msib.organization_id;

4)QUERY TO B PREPARED TO LIST THE DEATILS OF SUPPLIER,
SUPPLIER DETAILS ALONG WITH THE NO.OF.PO PLACED DURING THE LAST_FINANCIAL_YEAR
(THIS JUNE TO LAST_JUNE)


/* Formatted on 9/4/2014 2:09:56 PM (QP5 v5.115.810.9015) */
SELECT pv.vendor_name,
          pvsa.address_line1
       || ' '
       || pvsa.address_line2
       || ' '
       || pvsa.city
       || ' '
       || pvsa.state
          "vendor address",
       ft.territory_short_name,
       COUNT (pv.vendor_name)
FROM po_vendors pv,
     po_headers_all pha,
     po_vendor_sites_all pvsa,
     fnd_territories_tl ft
WHERE     1 = 1
      AND pv.vendor_name = 'Supriya'
      AND pvsa.vendor_id = pv.vendor_id
      AND pha.vendor_id = pv.vendor_id
      AND pha.vendor_site_id = pvsa.vendor_site_id
      AND pvsa.country = ft.territory_code
      AND pha.org_id = 204
GROUP BY pvsa.address_line1,
         pvsa.address_line2,
         pv.vendor_name,
         pvsa.city,
         pvsa.state,
         ft.territory_short_name;

5)COMPLETE PO FROM REQNO TO RECEIPTS
REQ NO,ITEM NO,REQ QUANTITY,ORG,TRANS QUANTITY,SUBINV CODE,
DISTRIBUTION ACCOUNT,PO NO,PO TYPE,PO QUANTITY,VENDOR ID,VENDOR NAME,VENDOR ADD


/* Formatted on 9/4/2014 2:10:40 PM (QP5 v5.115.810.9015) */
SELECT msib.segment1,
       prh.segment1 "Req Num",
       plc.description,
       pha.segment1 "PO Num",
       msi.secondary_inventory_name,
       pv.vendor_name,
       pvs.address_line1 || ' ' || pvs.city,
       pla.quantity,
       prl.quantity,
       SUM (pla.quantity * pla.unit_price),
       SUM (prl.quantity * prl.unit_price),
       SUM (rcv2.quantity) "quantity delivered",
       SUM (rcvl.quantity_shipped) "quantity shipped"
FROM po_headers_all pha,
     po_lines_all pla,
     po_distributions_all pda,
     po_req_distributions_all prd,
     po_requisition_lines_all prl,
     po_requisition_headers_all prh,
     rcv_transactions rcv2,
     rcv_shipment_headers rcvh,
     rcv_shipment_lines rcvl,
     po_vendors pv,
     po_vendor_sites_all pvs,
     mtl_system_items_b msib,
     mtl_secondary_inventories msi,
     po_lookup_codes plc
WHERE     pv.vendor_name = 'Supriya'
      AND pha.po_header_id = pda.po_header_id
      AND pda.req_distribution_id = prd.distribution_id
      AND prd.requisition_line_id = prl.requisition_line_id
      AND prl.requisition_header_id = prh.requisition_header_id
      AND pha.po_header_id = pla.po_header_id
      AND pha.org_id = 204
      --and pha.segment1='&enter'
      AND pha.po_header_id = rcv2.po_header_id
      AND pha.po_header_id = pla.po_header_id
      AND pla.po_line_id = rcvl.po_line_id
      AND pla.po_line_id = rcv2.po_line_id
      AND rcv2.transaction_type = 'DELIVER'
      AND rcvh.shipment_header_id = rcv2.shipment_header_id
      AND pha.vendor_id = pv.vendor_id
      AND pha.vendor_site_id = pvs.vendor_site_id
      AND msib.inventory_item_id = pla.item_id
      AND msib.inventory_item_id = rcvl.item_id
      AND msib.organization_id = rcvl.to_organization_id
      AND plc.lookup_type = 'PO TYPE'
      AND pha.type_lookup_code = plc.lookup_code
      --and msi.organization_id=rcvl.to_organization_id
      --and pha.org_id=204
      AND msi.secondary_inventory_name = rcv2.subinventory
      AND msib.organization_id = msi.organization_id
GROUP BY msib.segment1,
         prh.segment1,
         plc.description,
         pha.segment1,
         msi.secondary_inventory_name,
         pv.vendor_name,
         pvs.address_line1 || ' ' || pvs.city,
         pla.quantity,
         prl.quantity;