0 Replies Latest reply on Aug 2, 2016 7:34 PM by 2795332

    Receipt Number Logic

    2795332

      Hi,

       

      I m trying to add receipt number to below standard query, Could you suggest.

      I need receipt number based on below logic.

       

      Assume that PO Number 100 has 1 line with Quantity 10.

      I will receive 5 quantity for PO 100 and receipt number is 3456, Now the line will have 2 rows.

       

      PO#   Qty  Receipt#

      ----     ----  -----------

      100    5     3456

      100    5     NULL

       

      I m trying to get receipt number only for the line Quantity which is received.

       

      ##################################################################

       

         SELECT DISTINCT

                SYSDATE RECEIPT_DATE,

                NVL (POD.DELIVER_TO_PERSON_ID, POH.AGENT_ID) REQUESTOR_ID,

                POLL.PO_HEADER_ID PO_HEADER_ID,

                POH.SEGMENT1 PO_NUMBER,

                POLL.PO_LINE_ID PO_LINE_ID,

                POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID,

                POLL.PO_RELEASE_ID PO_RELEASE_ID,

                POR.RELEASE_NUM PO_RELEASE_NUMBER,

                TO_NUMBER (NULL) REQ_HEADER_ID,

                NULL REQUISITION_NUMBER,

                POL.LINE_NUM PO_LINE_NUMBER,

                POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER,

                POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID,

                POL.ITEM_ID ITEM_ID,

                POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,

                POH.VENDOR_ID VENDOR_ID,

                POV.VENDOR_NAME SOURCE,

                POH.VENDOR_SITE_ID VENDOR_SITE_ID,

                POVS.VENDOR_SITE_CODE SUPPLIER_SITE,

                POLL.NEED_BY_DATE NEED_BY_DATE,

                POLL.PROMISED_DATE PROMISED_DATE,

                NVL (POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE,

                POD.PO_DISTRIBUTION_ID,

                POD.DELIVER_TO_LOCATION_ID,

                POD.REQ_DISTRIBUTION_ID,

                POD.DISTRIBUTION_NUM,

                POL.VENDOR_PRODUCT_NUM,

                POH.SEGMENT1

                POD.GL_ENCUMBERED_DATE,

                POLL.RECEIVING_ROUTING_ID

           FROM PO_RELEASES POR,

                MTL_UNITS_OF_MEASURE MUM,

                PO_VENDORS POV,

                PO_VENDOR_SITES POVS,

                PO_LINES POL,

                PO_DISTRIBUTIONS POD,

                PO_HEADERS POH,

                PO_LINE_LOCATIONS POLL,

                HR_ALL_ORGANIZATION_UNITS_TL HRO                             

          WHERE     POD.REQ_DISTRIBUTION_ID IS NULL

                AND NVL (POL.ORDER_TYPE_LOOKUP_CODE, 'QUANTITY') <> 'RATE'

                AND DECODE (

                       POL.MATCHING_BASIS,

                       'AMOUNT',   POD.AMOUNT_ORDERED

                                 - NVL (POD.AMOUNT_DELIVERED, 0)

                                 - NVL (POD.AMOUNT_CANCELLED, 0),

                         POD.QUANTITY_ORDERED

                       - NVL (POD.QUANTITY_DELIVERED, 0)

                       - NVL (POD.QUANTITY_CANCELLED, 0)) > 0

                AND NVL (POLL.APPROVED_FLAG, 'N') = 'Y'

                AND NVL (POLL.CANCEL_FLAG, 'N') = 'N'

                AND NVL (POLL.CLOSED_CODE, 'OPEN') NOT IN

                       ('FINALLY CLOSED', 'CLOSED', 'CANCELLED') -- 'CLOSED FOR RECEIVING',

                AND POLL.SHIPMENT_TYPE IN ('STANDARD', 'BLANKET', 'SCHEDULED')

                AND POLL.RECEIVING_ROUTING_ID = 1

                AND POLL.PAYMENT_TYPE IS NULL

                AND POH.PO_HEADER_ID = POL.PO_HEADER_ID

                AND POL.PO_LINE_ID = POLL.PO_LINE_ID

                AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

                AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)

                AND POH.VENDOR_ID = POV.VENDOR_ID

                AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID

                AND POD.ORG_ID = HRO.ORGANIZATION_ID

                AND HRO.LANGUAGE = USERENV ('LANG')

                AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)

                AND NVL (POH.TYPE_LOOKUP_CODE, 'STANDARD') NOT IN

                       ('CONTRACT', 'QUOTATION', 'RFQ')

                AND NVL (POH.CANCEL_FLAG, 'N') = 'N'

                AND EXISTS

                       (SELECT NULL  

                          FROM rcv_transactions rcv,

                               rcv_shipment_headers rsh,

                               rcv_shipment_lines rsl

                         WHERE     1 = 1

                               AND transaction_type = 'DELIVER'

                               AND rcv.po_header_id = poh.po_header_id

                               AND rcv.shipment_header_id = rsh.shipment_header_id

                               AND rcv.shipment_line_id = rsl.shipment_line_id);

      Thanks