Supplier Performance Evaluation Report (Functional Document MD 50)

Parameters:

PO Date From: Date Field (Mandatory)
PO Date To: Date Field (Mandatory)
PO Number From:
PO Number To: This field will show only the approved PO numbers which are created between the selected dates (PO Date From & To)
Supplier Name From:
Supplier Name To: This field will show the supplier name of the PO numbers which are created between the selected dates
Note: Supplier name will be selected even without selecting the ‘PO Number From & To’ parameter. In such case system will display all the supplier name in the LOV

Sr. #	Heading	Description	Table Name	Column Name
1
1	Supplier Number	Select the supplier numbers of the given PO Dates, which are selected in Parameter. If only one PO number is selected in parameter it will display only that particular PO Number`s supplier number.	po_headers_all take vendor_id and get the supplier number  from po_vendors table (segment1)
2	Supplier Name	Supplier name of the given PO Dates which are selected in Parameter. If only one PO number is selected in parameter it will display only that particular PO Number`s supplier name.	po_vendors	Vendor_name
3	PO No.	PO Numbers of the given parameter	po_headers_all	Segment1
4	PO Date	PO Date of the given parameter	po_headers_all	Creation_date
5	PO Type	PO type	po_headers_all	Type_lookup_code
5.1	Release No.	If the PO type is ‘Blanket’, it will display the release number of the PO.	po_releases_all	Release_num
6	PO Line		Po_lines_all	Line_num
7	Item Code	Item Code of the PO	Po_lines_all	Item_id
8	Item Description	Item Description of the PO	Po_lines_all	Item_description
9	Item Category	Item category of the above item	Po_lines_all	There is a field for category_id based on that get the category_name
10	PO Qty	Purchase order QTY for the above item	Po_lines_all	quantity
11	Promised Date	Delivery Date of the PO line	po_line_locations_all	Promise_date
12	GRN No.	PO Receipt number for the particular PO line. There may be multiple GRN numbers for one PO.	rcv_shipment_headers	Receipt_num
13	GRN Qty	PO Receipt Qty for the particular PO Line	rcv_shipment_lines	Quantity_received
14	GRN Date	PO Receipt Date	rcv_shipment_lines	Creation_date
15	Total Due Days	Different between Promise Date and GRN Date (in Days)

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

QUERY:
========================================================================
SELECT DISTINCT PV.SEGMENT1 "SUPPLIER NUMBER"
,PV.VENDOR_NAME "SUPPLIER NAME"
,PHA.SEGMENT1 "PO NUMBER"
,PHA.CREATION_DATE "PO DATE"
,PHA.TYPE_LOOKUP_CODE "PO TYPE"
,PRA.RELEASE_NUM "RELEASE NO"
,PLA.LINE_NUM "PO LINE"
,MSI.SEGMENT1 "ITEM"
,MSI.DESCRIPTION "ITEM DESC"
,MC.SEGMENT1||MC.SEGMENT1 "CATEGORY"
,PLA.QUANTITY "QTY"
,PLLA.PROMISED_DATE "PROMISED DATE"
,RSH.RECEIPT_NUM "GRN NO"
,RSL.QUANTITY_RECEIVED "GRN QTY"
,RSL.CREATION_DATE "GRN DATE"
,TRUNC(RSL.CREATION_DATE-PLLA.PROMISED_DATE) "TOTAL DUE DAYS"
FROM PO_VENDORS PV
,PO_HEADERS_ALL PHA
,PO_RELEASES_ALL PRA
,PO_LINES_ALL PLA
,MTL_SYSTEM_ITEMS_B MSI
,MTL_CATEGORIES MC
,PO_LINE_LOCATIONS_ALL PLLA
,RCV_SHIPMENT_HEADERS RSH
,PO_DISTRIBUTIONS_ALL PDA
,RCV_SHIPMENT_LINES RSL
WHERE PV.VENDOR_ID = PHA.VENDOR_ID
AND PHA.PO_HEADER_ID =  PRA.PO_HEADER_ID(+)
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PLA.CATEGORY_ID =  MC.CATEGORY_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLA.PO_LINE_ID=PDA.PO_LINE_ID
AND PDA.PO_DISTRIBUTION_ID=RSL.PO_DISTRIBUTION_ID
AND RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND (PHA.CREATION_DATE BETWEEN :PO_DATE_FROM AND :PO_DATE_TO)
AND (PHA.SEGMENT1 BETWEEN NVL(:PO_NUMBER_FROM,PHA.SEGMENT1) AND NVL(:PO_NUMBER_TO,PHA.SEGMENT1))
AND (PV.VENDOR_NAME BETWEEN :VENDOR_NAME_FROM AND :VENDOR_NAME_TO)