1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Outer join restricted some conditions.

Discussion in 'SQL PL/SQL' started by Muhammad Masoom, Feb 18, 2015.

  1. Muhammad Masoom

    Muhammad Masoom Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Dear,
    I built this query to find the GRN either their invoices have been made or not therefore I used outer join from rcv_transactions rcv table, but when I put the last check “ aila.cancelled_flag<>’Y’ ” to only valid invoices means status should not be cancel, then it show only those GRN which has invoice ,obsolete GRN that has no invoice ,that’s a problem for me.
    I want to show those GRN too which has no invoice
    RDBMS : 11.1.0.7.0
    Oracle Applications : 12.1.2
    ------------------------------------
    select grn_date,create_date,inward_no,receipt_num,quantity_received,po_unit_price
    ,amount,ship_to_org_id,vendor_id,vendor_name,invoice_id,invoice_num,ap_amount,check_id
    from
    (
    SELECT distinct
    to_char(rcv.transaction_date,'dd-mm-yyyy') grn_date
    ,to_char(rcv.creation_date,'dd-mm-yyyy') create_date,
    rcv.transaction_id,
    rsh.attribute13 inward_no,
    rsh.attribute13,rsh.receipt_num
    ,nvl(rcv.quantity,0) quantity_received
    -- ,sum(nvl(rcv.quantity,0))* sum(rcv.po_unit_price) amount
    ,rcv.po_unit_price
    ,nvl(rcv.quantity,0)*rcv.po_unit_price amount
    -- ,(aila.amount) amt
    ,rsh.ship_to_org_id
    ,rsh.vendor_id
    ,ven.vendor_name
    ,aia.invoice_id
    ,aia.invoice_num
    ,(select sum(amount) from ap_invoice_lines_all aila1 where rcv_transaction_id=rcv.transaction_id
    ) ap_amount


    ,aipa.check_id
    --,aila.line_number,
    ,(SELECT max(TAX_RATE)
    FROM ap.AP_TAX_CODES_ALL
    WHERE name in (select tax_classification_code from ap_invoice_lines_all aila2 where aila2.rcv_transaction_id=rcv.transaction_id and aila2.invoice_id=aia.invoice_id))gst1

    FROM rcv_transactions rcv,rcv_shipment_headers rsh,po_vendors ven
    ,ap_invoice_distributions_all aida,ap_invoices_all aia,ap_invoice_lines_all aila,ap_invoice_payments_all aipa
    where rcv.transaction_type='RECEIVE'
    AND (rcv.po_header_id,rcv.po_line_id) in (select rt.po_header_id,rt.po_line_id
    from rcv_transactions rt where rt.po_header_id=rcv.po_header_id
    and rt.po_line_id=rcv.po_line_id
    and rt.SUBINVENTORY='STORE'
    AND rt.transaction_type='DELIVER')
    and rsh.shipment_header_id=rcv.shipment_header_id
    and ven.vendor_id=rsh.vendor_id
    and aida.rcv_transaction_id(+)=rcv.transaction_id
    and aia.invoice_id(+)=aida.invoice_id
    and aila.invoice_id(+)=aia.invoice_id
    and aipa.invoice_id(+)=aia.invoice_id
    and aila.cancelled_flag<>'Y'

    and rcv.organization_id= decode:)p_unit,null,rcv.organization_id,:p_unit)
    --AND rsh.vendor_id = DECODE:)P_VENDOR,null,rsh.vendor_id,:p_VENDOR)
    AND to_char(rcv.creation_date,'yyyy/mm/dd')||' 00:00:00' BETWEEN :p_Date_From AND :p_Date_To
    )
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    This condition and aila.cancelled_flag<>'Y' is incorrect in the general unit of query.

    It is necessary to decide on logic of processing.
    What data you need to output, on what filters?

    First candidate solution(classic) : devide your query to two requests with UNION ALL with separate logic
     
  3. Muhammad Masoom

    Muhammad Masoom Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Thaks,
    I also wanted to confirm about my logic means there is no any solution to write this condition in general query, have to change the logic for those transactions which are still pending to go in ap_incoices_all aia.
    so in your point of view I have to use union all.
    Regards
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    If I correctly understood everything, then if in case of data acquisition it is impossible to filter and connect correctly, the logician of assembly of data needs to separate.

    It is one of possible candidate solutions
     
    Muhammad Masoom likes this.
  5. Muhammad Masoom

    Muhammad Masoom Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    I did with union all after changing some logic.
    problem solved. Thanks. how can I make this post "Answered".
    Code (SQL):
    SELECT grn_date,
           create_date,
           inward_no,
           receipt_num,
           quantity_received,
           po_unit_price,
           Round(amount)                            amount,
           ship_to_org_id,
           vendor_id,
           vendor_name,
           invoice_id,
           invoice_num,
           ap_amount,
           check_id,
           Round(( ap_amount * awt_rate / 100 ), 0) awt,
           gst_rate,
           Round(( ap_amount * gst_rate / 100 ), 0) gst,
           check_number,
           awt_rate
    FROM   (SELECT DISTINCT To_char(rcv.transaction_date, 'dd-mm-yyyy')
                                   grn_date,
                            To_char(rcv.creation_date, 'dd-mm-yyyy')
                                    create_date,
                            rcv.transaction_id,
                            rsh.attribute13
                                    inward_no,
                            rsh.attribute13,
                            rsh.receipt_num,
                            Nvl(rcv.quantity, 0)
                                    quantity_received,
                            rcv.po_unit_price,
                            Nvl(rcv.quantity, 0) * rcv.po_unit_price
                                    amount,
                            rsh.ship_to_org_id,
                            rsh.vendor_id,
                            ven.vendor_name,
                            aia.invoice_id,
                            aia.invoice_num,
                            (SELECT SUM(amount)
                             FROM   ap_invoice_lines_all aila1
                             WHERE  rcv_transaction_id = rcv.transaction_id)
                                    ap_amount,
                            (SELECT MAX(tax_rate)
                             FROM   ap.ap_tax_codes_all
                             WHERE  name IN (SELECT tax_classification_code
                                             FROM   ap_invoice_lines_all aila2
                                             WHERE
                                    aila2.rcv_transaction_id = rcv.transaction_id
                                    AND aila2.invoice_id = aia.invoice_id))GST_RATE,
                            (SELECT tax_rate
                             FROM   ap.ap_awt_tax_rates_all aatra
                             WHERE  aatra.tax_name = (SELECT DISTINCT aag.name
                                                      FROM   ap_awt_groups aag
                                                      WHERE
                                    aag.group_id = (SELECT pay_awt_group_id
                                                    FROM   ap_invoices_all aia2
                                                    WHERE
                                    aia2.invoice_id = aia.invoice_id
                                                   )))            AWT_rate,
                            aipa.check_id,
                            aca.check_number
            FROM   rcv_transactions rcv,
                   rcv_shipment_headers rsh,
                   po_vendors ven,
                   ap_invoice_distributions_all aida,
                   ap_invoices_all aia,
                   ap_invoice_lines_all aila,
                   ap_invoice_payments_all aipa,
                   ap_checks_all aca
            WHERE  rcv.transaction_type = 'RECEIVE'
                   AND ( rcv.po_header_id, rcv.po_line_id ) IN
                       (SELECT rt.po_header_id,
                               rt.po_line_id
                        FROM   rcv_transactions rt
                        WHERE
                           rt.po_header_id = rcv.po_header_id
                           AND rt.po_line_id = rcv.po_line_id
                           AND rt.subinventory = 'STORE'
                           AND rt.transaction_type = 'DELIVER')
                   AND rsh.shipment_header_id = rcv.shipment_header_id
                   AND ven.vendor_id = rsh.vendor_id
                   AND aida.rcv_transaction_id(+) = rcv.transaction_id
                   AND aia.invoice_id(+) = aida.invoice_id
                   AND aila.invoice_id(+) = aia.invoice_id
                   AND aipa.invoice_id(+) = aia.invoice_id
                   AND aca.check_id(+) = aipa.check_id
                   [COLOR="red"]AND aila.cancelled_flag <> 'Y'[/COLOR]
                   --and     rsh.attribute13='2550'--'1581' --'1555'
                   --and    rsh.receipt_num='18749'
                   --  and    aia.invoice_id=215598
                   AND rcv.organization_id =
                       Decode(:p_unit, NULL, rcv.organization_id,
                                       :p_unit)
                   AND rsh.vendor_id = Decode(:P_VENDOR, NULL, rsh.vendor_id,
                                                         :P_VENDOR)
                   AND To_char(rcv.creation_date, 'yyyy/mm/dd')
                       ||' 00:00:00' BETWEEN :P_Date_From AND :P_Date_To
            UNION ALL
            SELECT DISTINCT To_char(rcv.transaction_date, 'dd-mm-yyyy') grn_date,
                            To_char(rcv.creation_date, 'dd-mm-yyyy')    create_date,
                            rcv.transaction_id,
                            rsh.attribute13                             inward_no,
                            rsh.attribute13,
                            rsh.receipt_num,
                            Nvl(rcv.quantity, 0)
                            quantity_received,
                            rcv.po_unit_price,
                            Nvl(rcv.quantity, 0) * rcv.po_unit_price    amount,
                            rsh.ship_to_org_id,
                            rsh.vendor_id,
                            ven.vendor_name,
                            NULL                                        invoice_id,
                            NULL                                        invoice_num,
                            NULL                                        ap_amount,
                            NULL                                        GST_RATE,
                            NULL                                        awt_rate,
                            NULL                                        check_id,
                            NULL                                        check_number
            FROM   rcv_transactions rcv,
                   rcv_shipment_headers rsh,
                   po_vendors ven
            WHERE  rcv.transaction_type = 'RECEIVE'
                   AND ( rcv.po_header_id, rcv.po_line_id ) IN
                       (SELECT rt.po_header_id,
                               rt.po_line_id
                        FROM   rcv_transactions rt
                        WHERE
                           rt.po_header_id = rcv.po_header_id
                           AND rt.po_line_id = rcv.po_line_id
                           AND rt.subinventory = 'STORE'
                           AND rt.transaction_type = 'DELIVER')
                   AND rsh.shipment_header_id = rcv.shipment_header_id
                   AND ven.vendor_id = rsh.vendor_id
                   AND rcv.transaction_id NOT IN (SELECT rcv_transaction_id
                                                  FROM
                       ap_invoice_distributions_all aida
                                                  WHERE
                       rcv.transaction_id = aida.rcv_transaction_id)
                   AND rcv.organization_id =
                       Decode(:p_unit, NULL, rcv.organization_id,
                                       :p_unit)
                   AND rsh.vendor_id = Decode(:P_VENDOR, NULL, rsh.vendor_id,
                                                         :P_VENDOR)
                   AND To_char(rcv.creation_date, 'yyyy/mm/dd')
                       ||' 00:00:00' BETWEEN :P_Date_From AND :P_Date_To)  
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Massom,

    There is no such option here.
     
    Muhammad Masoom likes this.