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!

Query not giving output

Discussion in 'Oracle Apps Technical' started by sambhaji, Nov 7, 2013.

  1. sambhaji

    sambhaji Active Member

    Messages:
    62
    Likes Received:
    2
    Trophy Points:
    160
    Hi All,

    Check the following query if it is giving o/p or not?

    ********************
    select distinct
    ooha.order_number, ooha.created_by,ooha.ATTRIBUTE1 Payment_Mode,ooha.BLANKET_NUMBER "Booking Reference",loc.CITY Destination,
    rcta.CT_REFERENCE,cust_acct.account_number cust_code,party.party_name cust_name,
    ooha.ORDERED_DATE,ooha.ATTRIBUTE7 Truck_NO, ooha.ATTRIBUTE8 Driver_Name,rcta.Attribute5 c_Date ,
    rcta.Attribute4 Cheque,rcta.attribute3 Bank , rcta.attribute1 CASH,ooha.TRANSACTIONAL_CURR_CODE Currency,
    CASE
    WHEN ot.NAME LIKE '%OPC%'
    THEN 'OPC'
    WHEN ot.NAME LIKE '%SRC%'
    THEN 'SRC'
    WHEN ot.NAME LIKE '%Clinker%OPC%'
    THEN 'Clinker-OPC'
    WHEN ot.NAME LIKE '%Clinker%SRC%'
    THEN 'Clinker-SRC'
    ELSE ot.NAME
    END AS PRODUCT_TYPE,
    /* CASE
    WHEN ot.NAME LIKE '%Bag%'
    THEN wsd.requested_quantity
    ELSE NULL
    END AS sold_bag_quantity,
    CASE
    WHEN ot.NAME LIKE '%TONS%'
    THEN wsd.shipped_quantity
    ELSE NULL
    END AS sold_ton_quantity,*/
    CASE
    WHEN oola.ORDER_QUANTITY_UOM = 'TON'
    THEN oola.ORDERED_QUANTITY
    WHEN oola.ORDER_QUANTITY_UOM = 'BAG'
    THEN (oola.ORDERED_QUANTITY*50)
    ELSE NULL
    END AS sold_ton_quantity,
    CASE
    WHEN oola.ORDER_QUANTITY_UOM = 'BAG'
    THEN oola.ORDERED_QUANTITY
    ELSE NULL
    END AS sold_bag_quantity,
    CASE
    WHEN ot.NAME LIKE '%BULK%TONS%'
    THEN wsd.shipped_quantity
    ELSE NULL
    END AS sold_bilk_ton_quantity,
    CASE
    WHEN ooha.attribute1 LIKE 'Cash'
    THEN (oola.unit_selling_price * oola.ordered_quantity)
    ELSE NULL
    END AS Pay_Cash,
    CASE
    WHEN ooha.attribute1 LIKE 'Check'
    THEN (oola.unit_selling_price * oola.ordered_quantity)
    ELSE NULL
    END AS Pay_Cheque,
    CASE
    WHEN ooha.attribute1 LIKE 'Cash-Transfer'
    THEN (oola.unit_selling_price * oola.ordered_quantity)
    ELSE NULL
    END AS Pay_Cash_Transfer,
    CASE
    WHEN ooha.attribute1 LIKE 'Cash-Deposits'
    THEN (oola.unit_selling_price * oola.ordered_quantity)
    ELSE NULL
    END AS Pay_Cash_Deposit,
    (oola.unit_selling_price * oola.ordered_quantity)Total,
    ship_su.location||' '||loc.ADDRESS1||' '||loc.ADDRESS2||' '||loc.ADDRESS3||' '||loc.POSTAL_CODE ||' '||loc. CITY ||' '||NVL(loc.STATE , loc.PROVINCE) ||' '||loc.COUNTRY Location,
    ship_su.SITE_USE_CODE,
    /*----------------Dispatch Details----------*/
    TO_CHAR (mtrh.date_required, 'HH12:MI AM') time_in,
    TO_CHAR (wnd.ultimate_dropoff_date, 'HH12:MI AM') time_out,
    wpbv.attribute1 "Empty Weight",
    wpbv.attribute2 "empty Weigh Bridge Ref",
    wsd.shipped_quantity net_weight,
    mmt.ATTRIBUTE1 "Gross weigh M Tons", mmt.ATTRIBUTE4 "Weigh Brid Ref.",
    /* wpbv.attribute2 Wt_Bridge_Ref, wpbv.attribute1 Gross_wt_M_Tons,wpbv.TO_SCHEDULED_SHIP_DATE,*/
    /*----------------Invoice Details----------------*/
    CASE
    WHEN oola.ORDER_QUANTITY_UOM LIKE 'TON'
    THEN (oola.unit_selling_price)
    WHEN oola.ORDER_QUANTITY_UOM LIKE 'BAG'
    THEN (oola.unit_selling_price)*(1000/50)
    ELSE NULL
    END AS "Price Per M Ton (SR)",
    rcta.trx_number invoice_number,
    (oola.unit_selling_price * oola.ordered_quantity) "Total Amount (SR)" ,
    to_char(to_date((oola.ordered_quantity * oola.unit_selling_price),'J'), 'JSP')||' RIYAL ONLY' "Amount in Words" ,
    ooha.PAYMENT_TYPE_CODE
    from ra_customer_trx_all rcta,
    oe_order_headers_all ooha,
    oe_order_lines_all oola,
    hz_cust_accounts cust_acct,
    hz_parties party,
    hz_cust_site_uses_all ship_su,
    hz_cust_acct_sites_all acct,
    hz_party_sites party_site ,
    hz_locations loc,
    oe_transaction_types_tl ot,
    wsh_shipping_details_v wsd,
    wsh_delivery_details wdd,
    wsh_delivery_assignments wda,
    wsh_new_deliveries wnd,
    wsh_picking_batches wpb,
    mtl_txn_request_lines mtrl,
    mtl_txn_request_headers mtrh,
    WSH_PICKING_BATCHES_V wpbv,
    mtl_material_transactions mmt,
    WSH_PICK_SLIP_V WPSV
    where
    ooha.header_id=oola.header_id
    AND ooha.ATTRIBUTE1 in ('Cash','Cash-Transfer','Cash-Deposit','Check')
    AND to_char(ooha.order_number)=rcta.CT_REFERENCE(+)
    AND ooha.sold_to_org_id = cust_acct.cust_account_id
    AND cust_acct.party_id = party.party_id
    --AND ooha.ship_to_org_id = ship_su.site_use_id(+)
    AND ooha.ship_to_org_id = ship_su.site_use_id
    AND acct.cust_acct_site_id = ship_su.cust_acct_site_id
    and acct.party_site_id = party_site.party_site_id
    and loc.location_id = party_site.location_id
    and cust_acct.party_id = party.party_id
    --AND ship_su.SITE_USE_CODE = 'BILL_TO'
    AND ooha.order_type_id = ot.transaction_type_id
    AND ot.LANGUAGE = USERENV ('LANG')
    AND ooha.header_id=wsd.order_header_id
    AND oola.line_id=wsd.order_line_id
    AND wdd.source_header_id=ooha.header_id
    AND wdd.source_line_id=oola.line_id
    AND wdd.delivery_detail_id = wda.delivery_detail_id
    AND wda.delivery_id= wnd.delivery_id
    AND wdd.move_order_line_id = mtrl.line_id
    AND mtrl.header_id = mtrh.header_id
    and ooha.header_id = wpb.order_header_id(+)
    AND ooha.ORDER_NUMBER=wpbv.ORDER_NUMBER(+)
    AND oola.LINE_ID=mmt.TRX_SOURCE_LINE_ID
    AND wpsv.move_order_line_id =wdd.move_order_line_id
    AND wpsv.TRANSACTION_ID=mmt.TRANSACTION_ID
    AND ooha.order_number = NVL :)p_order_number, ooha.order_number)
    AND party.party_name=nvl:)p_party_name,party.party_name)
    AND cust_acct.account_number=nvl:)p_account_number,cust_acct.account_number)
    AND trunc(ooha.ORDERED_DATE) between nvl:)p_inv_date_from,ooha.ORDERED_DATE) and nvl:)p_inv_date_to,ooha.ORDERED_DATE)ORDER BY ooha.order_number;
    ********************************
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,
    You are using 11i or R12?
     
  3. sambhaji

    sambhaji Active Member

    Messages:
    62
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    R12

    Regards
    Sambhaji
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Then first execute this and try your query....

    BEGIN
    MO_GLOBAL.SET_POLICY_CONTEXT('S',&P_ORG_ID);
    END;