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!

How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not return

Discussion in 'Oracle Forms and Reports' started by miuco, Mar 27, 2015.

  1. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Q_1;
    SELECT *
    FROM(SELECT DISTINCT(pl.line_num),PL.ITEM_ID,MSI.SEGMENT1 AS ITEM_CODE,PL.ITEM_DESCRIPTION,APS.VENDOR_NAME,APS.VENDOR_ID,MSI.PRIMARY_UOM_CODE AS UOM,
    TO_NUMBER (PH.SEGMENT1) "PO No.",TO_CHAR (PH.CREATION_DATE, 'dd-Mon-yyyy') "PO Date",
    PL.UNIT_PRICE "Current Price", PL2.UNIT_PRICE "Previous Price",ROW_NUMBER()OVER (PARTITION BY pl.line_num ORDER BY PL.ITEM_ID) AS rownumber
    FROM PO_HEADERS_ALL PH,
    PO_LINES_ALL PL,
    PO_LINES_ALL PL2,
    PO_DISTRIBUTIONS_ALL PD,
    AP_SUPPLIERS APS,
    AP_SUPPLIER_SITES_ALL APSS,
    MTL_SYSTEM_ITEMS_B MSI,
    HR_LOCATIONS_ALL HL,
    HR_ALL_ORGANIZATION_UNITS HAOU,
    FND_TERRITORIES FT,
    AP_TERMS_TL APT
    WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
    ---and PH.PO_HEADER_ID = PL2.PO_HEADER_ID
    ---and pl2.creation_date<pl.creation_date
    AND APS.VENDOR_ID = PH.VENDOR_ID
    AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
    AND PD.PO_LINE_ID = PL.PO_LINE_ID
    ---- AND PD.PO_LINE_ID = PL2.PO_LINE_ID
    AND NVL (PL.CANCEL_FLAG, 'N') = 'N'
    AND APS.VENDOR_ID = APSS.VENDOR_ID
    AND APSS.VENDOR_SITE_ID = PH.VENDOR_SITE_ID
    AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
    AND MSI.INVENTORY_ITEM_ID = PL2.ITEM_ID
    AND APSS.COUNTRY = FT.TERRITORY_CODE
    AND APT.TERM_ID(+) = PH.TERMS_ID
    AND HAOU.LOCATION_ID = HL.LOCATION_ID
    AND HAOU.ORGANIZATION_ID = PL.ORG_ID
    and PH.SEGMENT1=:pO_NO ---15000556--:p_PO_No
    and pl2. last_update_date>pl. last_update_date
    order by pl.line_num)
    WHERE rownumber =1

    Q_2;

    SELECT *
    FROM(SELECT DISTINCT(PL.ITEM_ID),MSI.SEGMENT1 AS ITEM_CODE,PL.ITEM_DESCRIPTION,APS.VENDOR_NAME,APS.VENDOR_ID,MSI.PRIMARY_UOM_CODE AS UOM,
    TO_NUMBER (PH.SEGMENT1) "PO No.",TO_CHAR (PH.CREATION_DATE, 'dd-Mon-yyyy') "PO Date",
    PL2.UNIT_PRICE "Previous Price",ROW_NUMBER()OVER (PARTITION BY PL.ITEM_ID ORDER BY PL.ITEM_ID) AS rownumber
    FROM PO_HEADERS_ALL PH,
    PO_LINES_ALL PL,
    PO_LINES_ALL PL2,
    PO_DISTRIBUTIONS_ALL PD,
    AP_SUPPLIERS APS,
    AP_SUPPLIER_SITES_ALL APSS,
    MTL_SYSTEM_ITEMS_B MSI,
    HR_LOCATIONS_ALL HL,
    HR_ALL_ORGANIZATION_UNITS HAOU,
    FND_TERRITORIES FT,
    AP_TERMS_TL APT
    WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
    ---and PH.PO_HEADER_ID = PL2.PO_HEADER_ID
    ---and pl2.creation_date<pl.creation_date
    AND APS.VENDOR_ID = PH.VENDOR_ID
    AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
    AND PD.PO_LINE_ID = PL.PO_LINE_ID
    ---- AND PD.PO_LINE_ID = PL2.PO_LINE_ID
    AND NVL (PL.CANCEL_FLAG, 'N') = 'N'
    AND APS.VENDOR_ID = APSS.VENDOR_ID
    AND APSS.VENDOR_SITE_ID = PH.VENDOR_SITE_ID
    AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
    AND MSI.INVENTORY_ITEM_ID = PL2.ITEM_ID
    AND APSS.COUNTRY = FT.TERRITORY_CODE
    AND APT.TERM_ID(+) = PH.TERMS_ID
    AND HAOU.LOCATION_ID = HL.LOCATION_ID
    AND HAOU.ORGANIZATION_ID = PL.ORG_ID
    AND PL.UNIT_PRICE>PL2.UNIT_PRICE
    --AND PL2.UNIT_PRICE=:pREVIOUS_PRICE_
    ---- AND PL.ITEM_ID=:ITEM_ID1
    --and PH.SEGMENT1=:pO_NO ---15000556--:p_PO_No
    and pl2. last_update_date>pl. last_update_date
    order by PL.ITEM_ID)
    WHERE rownumber =1
     

    Attached Files:

  2. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    Q_2 is not returning output at the moment.
     
  3. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not returning any output.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    Can you explain little more?

    with out data link second query not giving output?
     
  5. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    In my query 2, I need to display the output using item_id field with is being returned by query 1. I am doing this in oracle reports builder as show in the image. Below is my Query 2;

    SELECT *
    FROM(SELECT DISTINCT(PL.ITEM_ID),MSI.SEGMENT1 AS ITEM_CODE,PL.ITEM_DESCRIPTION,APS.VENDOR_NAME,APS. VENDOR_ID,MSI.PRIMARY_UOM_CODE AS UOM,
    TO_NUMBER (PH.SEGMENT1) "PO No.",TO_CHAR (PH.CREATION_DATE, 'dd-Mon-yyyy') "PO Date",
    PL2.UNIT_PRICE "Previous Price",ROW_NUMBER()OVER (PARTITION BY PL.ITEM_ID ORDER BY PL.ITEM_ID) AS rownumber
    FROM PO_HEADERS_ALL PH,
    PO_LINES_ALL PL,
    PO_LINES_ALL PL2,
    PO_DISTRIBUTIONS_ALL PD,
    AP_SUPPLIERS APS,
    AP_SUPPLIER_SITES_ALL APSS,
    MTL_SYSTEM_ITEMS_B MSI,
    HR_LOCATIONS_ALL HL,
    HR_ALL_ORGANIZATION_UNITS HAOU,
    FND_TERRITORIES FT,
    AP_TERMS_TL APT
    WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
    ---and PH.PO_HEADER_ID = PL2.PO_HEADER_ID
    ---and pl2.creation_date<pl.creation_date
    AND APS.VENDOR_ID = PH.VENDOR_ID
    AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
    AND PD.PO_LINE_ID = PL.PO_LINE_ID
    ---- AND PD.PO_LINE_ID = PL2.PO_LINE_ID
    AND NVL (PL.CANCEL_FLAG, 'N') = 'N'
    AND APS.VENDOR_ID = APSS.VENDOR_ID
    AND APSS.VENDOR_SITE_ID = PH.VENDOR_SITE_ID
    AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
    AND MSI.INVENTORY_ITEM_ID = PL2.ITEM_ID
    AND APSS.COUNTRY = FT.TERRITORY_CODE
    AND APT.TERM_ID(+) = PH.TERMS_ID
    AND HAOU.LOCATION_ID = HL.LOCATION_ID
    AND HAOU.ORGANIZATION_ID = PL.ORG_ID
    AND PL.UNIT_PRICE>PL2.UNIT_PRICE
    ---AND PL2.UNIT_PRICE=:pREVIOUS_PRICE1
    AND PL.ITEM_ID=:ITEM_ID --- "Am picking what is returned by Q_1"
    and PH.SEGMENT1=:pO_NO --"This will be entered by the user"
    and pl2. last_update_date>pl. last_update_date
    order by PL.ITEM_ID)
    WHERE rownumber =1
     
  6. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    In my query 2, I need to display the output using item_id field with is being returned by query 1. I am doing this in oracle reports builder as show in the image. Below is my Query 2;

    SELECT *
    FROM(SELECT DISTINCT(PL.ITEM_ID),MSI.SEGMENT1 AS ITEM_CODE,PL.ITEM_DESCRIPTION,APS.VENDOR_NAME,APS. VENDOR_ID,MSI.PRIMARY_UOM_CODE AS UOM,
    TO_NUMBER (PH.SEGMENT1) "PO No.",TO_CHAR (PH.CREATION_DATE, 'dd-Mon-yyyy') "PO Date",
    PL2.UNIT_PRICE "Previous Price",ROW_NUMBER()OVER (PARTITION BY PL.ITEM_ID ORDER BY PL.ITEM_ID) AS rownumber
    FROM PO_HEADERS_ALL PH,
    PO_LINES_ALL PL,
    PO_LINES_ALL PL2,
    PO_DISTRIBUTIONS_ALL PD,
    AP_SUPPLIERS APS,
    AP_SUPPLIER_SITES_ALL APSS,
    MTL_SYSTEM_ITEMS_B MSI,
    HR_LOCATIONS_ALL HL,
    HR_ALL_ORGANIZATION_UNITS HAOU,
    FND_TERRITORIES FT,
    AP_TERMS_TL APT
    WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
    ---and PH.PO_HEADER_ID = PL2.PO_HEADER_ID
    ---and pl2.creation_date<pl.creation_date
    AND APS.VENDOR_ID = PH.VENDOR_ID
    AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
    AND PD.PO_LINE_ID = PL.PO_LINE_ID
    ---- AND PD.PO_LINE_ID = PL2.PO_LINE_ID
    AND NVL (PL.CANCEL_FLAG, 'N') = 'N'
    AND APS.VENDOR_ID = APSS.VENDOR_ID
    AND APSS.VENDOR_SITE_ID = PH.VENDOR_SITE_ID
    AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
    AND MSI.INVENTORY_ITEM_ID = PL2.ITEM_ID
    AND APSS.COUNTRY = FT.TERRITORY_CODE
    AND APT.TERM_ID(+) = PH.TERMS_ID
    AND HAOU.LOCATION_ID = HL.LOCATION_ID
    AND HAOU.ORGANIZATION_ID = PL.ORG_ID
    AND PL.UNIT_PRICE>PL2.UNIT_PRICE
    ---AND PL2.UNIT_PRICE=:pREVIOUS_PRICE1
    AND PL.ITEM_ID=:ITEM_ID --- "Am picking what is returned by Q_1"
    and PH.SEGMENT1=:pO_NO --"This will be entered by the user"
    and pl2. last_update_date>pl. last_update_date
    order by PL.ITEM_ID)
    WHERE rownumber =1
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    Hi,

    AND PL.ITEM_ID=:ITEM_ID --- "Am picking what is returned by Q_1"

    This is correct. As per my knowledge you no need of data link. because you already gave Item_id which is coming form Q1.

    What is your exact problem now?
     
  8. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    The query returns no results.
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: How can I return results in Q_2 based on :item_id in Q_1? Currently Q_2 is not re

    So, First run the second query in toad. give item id which is coming from first query. If there are no records, then for that item id there is no data. Also check by commenting that parameter.