applications license select

Burada her application hangi lisansa ait cross bilgileri tutan bir de table oluşturmam gerekti onun için. Aşağıdaki sorguda o tabloyu çıkardım, her bölümde application sayılarını veren kısmı var:

SELECT pgt.NAME grade, apr.application_name, COUNT(*) sayi

FROM per_all_assignments_f a,

per_grades_tl pgt,

per_all_people_f p,

--xxfr_uygulama_lisans xul,

(SELECT DISTINCT fa.APPLICATION_NAME, fu.EMPLOYEE_ID, fa.APPLICATION_ID

FROM fnd_user_resp_groups frg, fnd_user fu, fnd_application_tl fa

WHERE ((NVL(frg.END_DATE,TO_DATE('31.12.4712','dd.mm.yyyy')) = TO_DATE('31.12.4712','dd.mm.yyyy')) OR

(NVL(frg.END_DATE,TO_DATE('31.12.4712','dd.mm.yyyy')) > SYSDATE))

AND frg.USER_ID = fu.USER_ID

AND fa.APPLICATION_ID = frg.RESPONSIBILITY_APPLICATION_ID

AND fa.LANGUAGE = 'US') apr

WHERE a.GRADE_ID = pgt.GRADE_ID

AND a.EFFECTIVE_END_DATE = TO_DATE('31.12.4712','dd.mm.yyyy')

AND pgt.LANGUAGE = 'US'

-- AND a.PERSON_ID = 787

AND p.PERSON_ID = a.PERSON_ID

AND p.EFFECTIVE_END_DATE = TO_DATE('31.12.4712','dd.mm.yyyy')

AND a.PERSON_ID = apr.employee_id

-- AND xul.APPLICATION_ID = apr.APPLICATION_ID

GROUP BY pgt.NAME, apr.application_name

ORDER BY pgt.NAME