AR DNB Global Data Products Request

SELECT  org.organization_name          party_name,
        party.party_number             party_number,
        lookup.meaning                 requested_product,
        1 as records,
        fnduser.user_name              purchased_by,
         request.creation_date  purchased_date
FROM  hz_parties                     party,
        hz_organization_profiles       org,
        hz_dnb_request_log             request,
        fnd_user                       fnduser,
        ar_lookups                     lookup
 WHERE  request.party_id = party.party_id
   AND  TRUNC( request.creation_date ) >= trunc(:p_from_date)
   AND  TRUNC( request.creation_date ) <= trunc(:p_to_date)
   AND  copied_from_party_id IS NULL
   AND  org.party_id = party.party_id
   AND  org.effective_end_date IS NULL
   AND  org.actual_content_source = 'DNB'
   AND  fnduser.user_id = request.created_by
   AND  lookup.lookup_type = 'DNB_GDP_PRODUCT'
   AND  lookup.lookup_code = request.requested_product
UNION
SELECT  '' as                           party_name,
        '' as                           party_number,
        'Batch Load' as                      requested_product,
        batch.NUM_PARTIES_PURCHASED as  records,
        fnduser.user_name               purchased_by,
         batch.creation_date     purchased_date
FROM hz_imp_batch_summary batch,
        fnd_user fnduser
  WHERE TRUNC(batch.creation_date) >= trunc(:p_from_date)
    AND TRUNC(batch.creation_date) <= trunc(:p_to_date)
    AND fnduser.user_id = batch.created_by
    AND batch.NUM_PARTIES_PURCHASED is not null
    ANd batch.original_system = 'DNB'
order by purchased_date