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!

Generate Reports as per the Reqd Format

Discussion in 'Oracle Forms and Reports' started by jayeshrc, Oct 21, 2011.

  1. jayeshrc

    jayeshrc Guest

    Below is the query that is generating the attached reports


    select 'CMS#: ' || cds_contract_number || ' Ver#: ' ||reference_version
    ||' - '|| 'VAP ' || wm_concat(vap_reference_id) as "CMS – Version VAP Mapping",ecm_contract_number as "ECLM Contract Number"
    from
    (
    select b.cds_contract_number,a.reference_version,wm_concat(a.vap_reference_id),b.ecm_contract_number
    from lcdadmin.lc_approvals@cdsint a,lcdadmin.ecm_cds_mapping@cdsint b
    where b.cds_contract_number = a.reference_number
    and b.ecm_contract_number in
    (
    select min(b.ecm_contract_number) from lcdadmin.lc_approvals@cdsint a,lcdadmin.ecm_cds_mapping@cdsint b
    where b.cds_contract_number = a.reference_number group by b.cds_contract_number
    )
    and a.vap_reference_id in
    (
    select vap_reference_id from lcdadmin.lc_approvals@cdsint where vap_submission_date< to_date('3/27/2009','mm/dd/yyyy')
    minus
    select vap_request_id from salesadm.vap_approval_details
    )
    --and a. reference_number in (select cds_contract_number from lcdadmin.ecm_cds_mapping@cdsint)
    group by b.cds_contract_number,b.ecm_contract_number
    ) tab1
    group by cds_contract_number,ecm_contract_number
    order by tab1.cds_contract_number desc

    can anyone help me out on this...

    Now the format is
    CMS - Version VAP Mapping ECLM Contract Number
    CMS#: 78155 Ver#: 1 - VAP 91760 7819
    CMS#: 78155 Ver#: 2 - VAP 93967 7819

    actually i want the reports in the below format
    CMS - Version VAP Mapping ECLM Contract Number
    CMS#: 78155 Ver# : 1 - VAP 91760,Ver#: 2 - VAP 93967 7819

    thanks in advance

    Regards
    Jayesh
     

    Attached Files: