4 Replies Latest reply on Nov 25, 2015 1:43 PM by Srini Chavali-Oracle

    SQL link between Requisition,Purchase Order, Requisition history, PO history and Receiving

    891058

      I want to create an SQL query that link requisition number, requisition action history, purchase order number, purchase order action history and receiving

       

      Can anyone helps ?

        • 1. Re: SQL link between Requisition,Purchase Order, Requisition history, PO history and Receiving
          vijayrsehgal-Oracle

          How do we know what tables and relationship you are talking about, could you share tables and relationship and your attempt to write a query?

          • 2. Re: SQL link between Requisition,Purchase Order, Requisition history, PO history and Receiving
            891058

            The tables :

            po_headers_all

            po_lines_all

            po_line_locations_all

            po_distributions_all

            po_action_history

            po_requisition_headers_all

            po_requisition_lines_all

            po_req_distributions_all

            rcv_transactions

             

            I tried with this query but I had a problem with the sequence in the po_action_history (i want to check the last approval action) :

             

            SELECT distinct prha.segment1

            , prha.creation_date

            , prha.authorization

            , pahr.action_code

            , pahr.action_date

            , pha.segment1

            , pha.creation_date

            , pha.authorization_status

            , pah.action_code

            , pah.action_date

            , rcv.transaction_date

            FROM po.po_requisition_headers_all prha

            , po.po_requisition_lines_all prla

            , po.po_req_distributions_all prda

            , po.po_distributions_all pda

            , po.po_headers_all pha

            , po_action_history pah

            , po_action_history pahr

            , rcv_transactions rcv

            WHERE prha.requisition_header_id = prla.requisition_header_id

            AND prla.requisition_line_id = prda.requisition_line_id

            AND prda.distribution_id = pda.req_distribution_id(+)

            AND pda.po_header_id = pha.po_header_id(+)

            AND pah.object_id = pha.po_header_id

            AND pah.object_type_code = 'PO'

            AND pah.action_code = 'APPROVE'

            AND pah.sequence_num  =(Select Max(pah.sequence_num) from po_action_history pha where pah.object_type_code = 'PO'and pah.action_code = 'APPROVE' and pah.object_id = pha.po_header_id)

            AND pahr.object_id = prha.requisition_header_id

            AND pahr.object_type_code = 'REQUISITION'

            AND pahr.action_code = 'APPROVE'

            AND pahr.sequence_num in (Select Max(pahr.sequence_num) from po_action_history phar where pahr.object_type_code = 'REQUISITION' and pahr.action_code = 'APPROVE' and  pahr.object_id = prha.requisition_header_id )

            AND rcv.po_header_id = pha.po_header_id

            AND rcv.transaction_type = 'DELIVER'

            AND pha.authorization_status ='APPROVED'

            AND prha.authorization_status ='APPROVED';

            • 3. Re: SQL link between Requisition,Purchase Order, Requisition history, PO history and Receiving
              vijayrsehgal-Oracle

              I am not aware of you data model and relationships but in the given query shouldn't the join "and pah.object_id = pha.po_header_id" be "and pah.object_id = pda.po_header_id". Also please give different alias to tables it would lead to confusion if you give the same alias to different tables in main query and subquery e.g.

              po_headers_all pha

              po_action_history pha