VIEW: APPS.IMC_GROWTH_RPT_V

Source

View Text - Preformatted

SELECT t.month, t.month_no, nvl(per_party.p_party,0)per_party, to_char(round((per_party.p_party*100)/per_cnt.per_party_cnt,2))per_pct, nvl(org_party.o_party,0)org_party, to_char(round((org_party.o_party*100)/org_cnt.org_party_cnt,2))org_pct, nvl(rel_party.r_party,0) rel_party, to_char(round((rel_party.r_party*100)/rel_cnt.rel_party_cnt,2))rel_pct, nvl(total_party.total_pty,0) total_cnt, to_char(round((total_party.total_pty*100)/total_cnt.total_cnt,2))total_pct from (select month, month_no, count(party_id) p_party from imc_growth_parties_v where party_type='PERSON' group by month, month_no) per_party, (select count(party_id) per_party_cnt from imc_growth_parties_v where party_type='PERSON') per_cnt, (select month, month_no, count(party_id) o_party from imc_growth_parties_v where party_type='ORGANIZATION' group by month, month_no) org_party, (select count(party_id) org_party_cnt from imc_growth_parties_v where party_type='ORGANIZATION') org_cnt, (select month, month_no, count(party_id) r_party from imc_growth_parties_v where party_type='PARTY_RELATIONSHIP' group by month, month_no) rel_party, (select count(party_id) rel_party_cnt from imc_growth_parties_v where party_type='PARTY_RELATIONSHIP') rel_cnt, (select month, month_no, count(party_id) total_pty from imc_growth_parties_v group by month, month_no) total_party, (select count(*) total_cnt from imc_growth_parties_v) total_cnt, (select month,month_no from imc_growth_time_v) t WHERE t.month=per_party.month(+) and t.month=org_party.month(+) and t.month=rel_party.month(+) and t.month=total_party.month(+) UNION select fnd_message.get_string('IMC','IMC_REPORTS_TOTAL') month, 50 month_no,0 per_party,'0.00' per_pct, 0 org_party, '0.00' org_pct, 0 rel_party,'0.00' rel_pct, 0 total_cnt,'0.00' total_pct from dual

View Text - HTML Formatted

SELECT T.MONTH
, T.MONTH_NO
, NVL(PER_PARTY.P_PARTY
, 0)PER_PARTY
, TO_CHAR(ROUND((PER_PARTY.P_PARTY*100)/PER_CNT.PER_PARTY_CNT
, 2))PER_PCT
, NVL(ORG_PARTY.O_PARTY
, 0)ORG_PARTY
, TO_CHAR(ROUND((ORG_PARTY.O_PARTY*100)/ORG_CNT.ORG_PARTY_CNT
, 2))ORG_PCT
, NVL(REL_PARTY.R_PARTY
, 0) REL_PARTY
, TO_CHAR(ROUND((REL_PARTY.R_PARTY*100)/REL_CNT.REL_PARTY_CNT
, 2))REL_PCT
, NVL(TOTAL_PARTY.TOTAL_PTY
, 0) TOTAL_CNT
, TO_CHAR(ROUND((TOTAL_PARTY.TOTAL_PTY*100)/TOTAL_CNT.TOTAL_CNT
, 2))TOTAL_PCT
FROM (SELECT MONTH
, MONTH_NO
, COUNT(PARTY_ID) P_PARTY
FROM IMC_GROWTH_PARTIES_V
WHERE PARTY_TYPE='PERSON' GROUP BY MONTH
, MONTH_NO) PER_PARTY
, (SELECT COUNT(PARTY_ID) PER_PARTY_CNT
FROM IMC_GROWTH_PARTIES_V
WHERE PARTY_TYPE='PERSON') PER_CNT
, (SELECT MONTH
, MONTH_NO
, COUNT(PARTY_ID) O_PARTY
FROM IMC_GROWTH_PARTIES_V
WHERE PARTY_TYPE='ORGANIZATION' GROUP BY MONTH
, MONTH_NO) ORG_PARTY
, (SELECT COUNT(PARTY_ID) ORG_PARTY_CNT
FROM IMC_GROWTH_PARTIES_V
WHERE PARTY_TYPE='ORGANIZATION') ORG_CNT
, (SELECT MONTH
, MONTH_NO
, COUNT(PARTY_ID) R_PARTY
FROM IMC_GROWTH_PARTIES_V
WHERE PARTY_TYPE='PARTY_RELATIONSHIP' GROUP BY MONTH
, MONTH_NO) REL_PARTY
, (SELECT COUNT(PARTY_ID) REL_PARTY_CNT
FROM IMC_GROWTH_PARTIES_V
WHERE PARTY_TYPE='PARTY_RELATIONSHIP') REL_CNT
, (SELECT MONTH
, MONTH_NO
, COUNT(PARTY_ID) TOTAL_PTY
FROM IMC_GROWTH_PARTIES_V GROUP BY MONTH
, MONTH_NO) TOTAL_PARTY
, (SELECT COUNT(*) TOTAL_CNT
FROM IMC_GROWTH_PARTIES_V) TOTAL_CNT
, (SELECT MONTH
, MONTH_NO
FROM IMC_GROWTH_TIME_V) T
WHERE T.MONTH=PER_PARTY.MONTH(+)
AND T.MONTH=ORG_PARTY.MONTH(+)
AND T.MONTH=REL_PARTY.MONTH(+)
AND T.MONTH=TOTAL_PARTY.MONTH(+) UNION SELECT FND_MESSAGE.GET_STRING('IMC'
, 'IMC_REPORTS_TOTAL') MONTH
, 50 MONTH_NO
, 0 PER_PARTY
, '0.00' PER_PCT
, 0 ORG_PARTY
, '0.00' ORG_PCT
, 0 REL_PARTY
, '0.00' REL_PCT
, 0 TOTAL_CNT
, '0.00' TOTAL_PCT
FROM DUAL