1 Reply Latest reply on Aug 1, 2016 7:00 AM by Bashar.

    SQL Query logic

    2795332

      Hi,

       

      Could you suggest on below query.

      For the below query I need to add below logic.

       

      1. When Receipt & Delivery is created 2 records are created into RCV_TRANSACTION with TRANSACTION_TYPE = 'RECEIPT' and TRANSACTION_TYPE = 'DELIVER'

       

      So for below query i need to add logic , if above scenario exists then it should not pull the record which has both TRANSACTION_TYPE = 'RECEIPT' and TRANSACTION_TYPE = 'DELIVER'

       

         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,

           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,

         rcv_transactions RCV       -- NEW

          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'

                      );

       

       

      Thanks

        • 1. Re: SQL Query logic
          Bashar.

          Hi,

           

          This query returns the receiving transactions that has the "Receive" part only.

           

          select *

          from rcv_transactions rcv

          where (transaction_type = 'RECEIVE'

                 and not exists (select 1 from rcv_transactions where shipment_header_id = rcv.shipment_header_id

                                                                  and shipment_line_id = rcv.shipment_line_id

                                                                  and transaction_type = 'DELIVER'))

           

          Regards,

          Bashar