Wednesday 1 February 2012

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)

Po Releases Script


SQL Script:


SELECT   a.segment1 po_num
        ,a.creation_date ordercreation_date
        ,b.item_description
        ,c.need_by_date
        ,a.type_lookup_code
        ,g.quantity
        ,g.line_rev
        ,g.header_rev
    FROM po_headers_all a
        ,po_lines_all b
        ,po_line_locations_all c
        ,po_line_locations_archive_all f
        ,(SELECT DISTINCT e.segment1
                         ,f.quantity
                         ,f.revision_num line_rev
                         ,e.revision_num header_rev
                         ,e.last_update_date
                         ,LOCATION(e.bill_to_location_id) LOCATION
                         ,e.po_header_id
                     FROM po_headers_archive_all e
                         ,po_line_locations_archive_all f
                    WHERE e.po_header_id = f.po_header_id(+)
                      AND f.revision_num(+) = e.revision_num
                      AND TRUNC(e.last_update_date) =
                                            TO_DATE('5/11/2011', 'mm/dd/yyyy')
                 ORDER BY e.revision_num
                         ,f.revision_num) g
   WHERE a.po_header_id = b.po_header_id
     AND b.po_line_id = c.po_line_id
     AND a.po_header_id = g.po_header_id