[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_select_str varchar2(3000);
SELECT
SUM(DECODE(party_type,'PERSON',
DECODE(LEAST(creation_date,add_months(sysdate,-23)),
add_months(sysdate,-23),count(*),0),
0)),
SUM(DECODE(party_type,'ORGANIZATION',
DECODE(LEAST(creation_date,add_months(sysdate,-23)),
add_months(sysdate,-23),count(*),0),
0)),
SUM(DECODE(party_type,'PARTY_RELATIONSHIP',
DECODE(LEAST(creation_date,add_months(sysdate,-23)),
add_months(sysdate,-23),count(*),0),
0)),
SUM(DECODE(LEAST(creation_date,add_months(sysdate,-23)),
add_months(sysdate,-23),count(*),0)),
SUM(count(*))
INTO rp_grth_per_cnt, rp_grth_org_cnt, rp_grth_rel_cnt,
rp_grth_total_cnt, rp_total_cnt
FROM hz_parties
GROUP BY party_type,creation_date;
SELECT
SUM(DECODE(party_type,'PERSON', count(*),0)),
SUM(DECODE(party_type,'ORGANIZATION', count(*),0)),
SUM(DECODE(party_type,'PARTY_RELATIONSHIP',count(*),0))
INTO rp_per_cnt, rp_org_cnt, rp_rel_cnt
FROM hz_parties
WHERE status = 'A'
GROUP BY party_type;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
time_stamp)
SELECT 'INDUSTRY','R',
industry, 'YES' industry_code, sum(org_count), sysdate
FROM (SELECT lkp.meaning industry,
decode(pty.party_type, 'ORGANIZATION', count(*), 0) org_count
FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
WHERE look.class_category = 'CUSTOMER_CATEGORY'
AND look.owner_table_name = 'HZ_PARTIES'
AND pty.party_id = look.owner_table_id
AND pty.party_type = 'ORGANIZATION'
AND pty.status = 'A'
AND look.class_code = lkp.lookup_code
AND sysdate between look.start_date_active and nvl(look.end_date_active,sysdate)
AND lkp.lookup_type = 'CUSTOMER_CATEGORY'
GROUP BY lkp.meaning, pty.party_type)
GROUP BY industry;
SELECT count(1) INTO l_nd_ind
FROM (SELECT 1
FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
WHERE look.class_category = 'CUSTOMER_CATEGORY'
AND look.owner_table_name = 'HZ_PARTIES'
AND pty.party_id = look.owner_table_id
AND pty.party_type = 'ORGANIZATION'
AND pty.status = 'A'
AND look.class_code = lkp.lookup_code
AND sysdate between look.start_date_active and nvl(look.end_date_active,sysdate)
AND lkp.lookup_type = 'CUSTOMER_CATEGORY'
GROUP BY pty.party_id);
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
time_stamp)
VALUES
('INDUSTRY','R', rp_msg_undefined, 'YES', rp_org_cnt-l_nd_ind, sysdate);
SELECT nvl(sum(org_cnt),0)
INTO rp_ind_org_cnt
FROM imc_reports_tempinfo
WHERE report_name = 'INDUSTRY'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_ind_org_cnt)*100,2)
WHERE report_name = 'INDUSTRY'
AND report_type = 'R';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
time_stamp)
SELECT 'INDUSTRY','R',rp_msg_total, nvl(rp_org_cnt,0),'100.00',sysdate
FROM dual;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
org_pct,
time_stamp)
SELECT 'INDUSTRY','G', category, parent_category,
org_cnt, org_pct, sysdate
FROM (SELECT category, parent_category, org_cnt, org_pct
FROM imc_reports_tempinfo
WHERE report_name = 'INDUSTRY'
AND report_type = 'R'
AND org_cnt IS NOT NULL
AND NOT (org_pct = 100 AND parent_category IS NULL)
ORDER BY org_cnt DESC)
WHERE rownum < 6;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
time_stamp)
SELECT 'INDUSTRY','G',rp_msg_all_others,
(rp_ind_org_cnt - sum(imc_tmp.org_cnt)),
round(((rp_ind_org_cnt - sum(imc_tmp.org_cnt))/rp_ind_org_cnt) * 100,2),
sysdate
FROM imc_reports_tempinfo imc_tmp
WHERE imc_tmp.report_name = 'INDUSTRY'
AND imc_tmp.report_type = 'G';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
time_stamp)
SELECT 'COUNTRY','R',
terr.territory_short_name country,
terr.territory_code country_code,
pty.org_count, pty.per_count, sysdate
FROM (SELECT country, sum(org_count) org_count, sum(per_count) per_count
FROM (SELECT /*+ parallel(pty) */ pty.country,
DECODE(pty.party_type,'ORGANIZATION',count(*),0) org_count,
DECODE(pty.party_type,'PERSON',count(*),0) per_count
FROM hz_parties pty
WHERE pty.party_type IN ('ORGANIZATION','PERSON')
AND pty.status = 'A'
GROUP BY pty.country, pty.party_type )
GROUP BY country ) pty ,
fnd_territories_tl terr
WHERE pty.country = terr.territory_code
AND terr.language = userenv('LANG')
ORDER BY country, country_code;
SELECT sum(org_cnt), sum(per_cnt) INTO l_nd_org, l_nd_per
FROM IMC_REPORTS_TEMPINFO
WHERE report_name = 'COUNTRY'
AND report_type = 'R';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
time_stamp)
VALUES
('COUNTRY','R', rp_msg_undefined, NULL,
rp_org_cnt-l_nd_org, rp_per_cnt-l_nd_per, sysdate);
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_org_cnt)*100,2),
per_pct = round((per_cnt/rp_per_cnt)*100,2)
WHERE report_name = 'COUNTRY'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_org_cnt)*100,2)
WHERE report_name = 'COUNTRY'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET per_pct = round((per_cnt/rp_per_cnt)*100,2)
WHERE report_name = 'COUNTRY'
AND report_type = 'R';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'COUNTRY','R',rp_msg_total,
nvl(rp_org_cnt,0),'100.00',nvl(rp_per_cnt,0),'100.00',sysdate
FROM dual;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
org_pct,
time_stamp)
SELECT 'COUNTRY','G', category, parent_category,
org_cnt, org_pct, sysdate
FROM (SELECT category, parent_category, org_cnt, org_pct
FROM imc_reports_tempinfo
WHERE report_name = 'COUNTRY'
AND report_type = 'R'
AND org_cnt IS NOT NULL
AND NOT (org_pct = 100 AND parent_category IS NULL)
ORDER BY org_cnt DESC)
WHERE rownum < 6;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
per_cnt,
per_pct,
time_stamp)
SELECT 'COUNTRY','G', category, parent_category,
per_cnt, per_pct, sysdate
FROM (SELECT category, parent_category, per_cnt, per_pct
FROM imc_reports_tempinfo
WHERE report_name = 'COUNTRY'
AND report_type = 'R'
AND per_cnt IS NOT NULL
AND NOT (per_pct = 100 AND parent_category IS NULL)
ORDER BY per_cnt DESC)
WHERE rownum < 6;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'COUNTRY','G',rp_msg_all_others,
(rp_org_cnt - sum(imc_tmp.org_cnt)),
round(((rp_org_cnt - sum(imc_tmp.org_cnt))/rp_org_cnt) * 100,2),
(rp_per_cnt - sum(imc_tmp.per_cnt)),
round(((rp_per_cnt - sum(imc_tmp.per_cnt))/rp_per_cnt) * 100,2),
sysdate
FROM imc_reports_tempinfo imc_tmp
WHERE imc_tmp.report_name = 'COUNTRY'
AND imc_tmp.report_type = 'G';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'COUNTRY','G',rp_msg_all_others,
(rp_org_cnt - sum(imc_tmp.org_cnt)), 0,
(rp_per_cnt - sum(imc_tmp.per_cnt)),
round(((rp_per_cnt - sum(imc_tmp.per_cnt))/rp_per_cnt) * 100,2),
sysdate
FROM imc_reports_tempinfo imc_tmp
WHERE imc_tmp.report_name = 'COUNTRY'
AND imc_tmp.report_type = 'G';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'COUNTRY','G',rp_msg_all_others,
(rp_org_cnt - sum(imc_tmp.org_cnt)),
round(((rp_org_cnt - sum(imc_tmp.org_cnt))/rp_org_cnt) * 100,2),
(rp_per_cnt - sum(imc_tmp.per_cnt)), 0,
sysdate
FROM imc_reports_tempinfo imc_tmp
WHERE imc_tmp.report_name = 'COUNTRY'
AND imc_tmp.report_type = 'G';
SELECT parent_category,
sum(org_cnt) org_total,
sum(per_cnt) per_total
FROM imc_reports_tempinfo
WHERE report_name = 'STATE'
AND report_type = 'R'
AND parent_category <> rp_msg_total
GROUP BY parent_category;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
time_stamp)
SELECT 'STATE','R',
state, country, sum(org_count), sum(per_count), sysdate
FROM (SELECT nvl(pty.state,rp_msg_undefined) state,
pty.country,
decode(pty.party_type,'ORGANIZATION',count(*),0) org_count,
decode(pty.party_type,'PERSON',count(*),0) per_count
FROM hz_parties pty
WHERE pty.party_type IN ('ORGANIZATION','PERSON')
AND pty.status = 'A'
GROUP BY pty.country, pty.state, pty.party_type)
GROUP BY country, state;
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/i.org_total)*100,2),
per_pct = round((per_cnt/i.per_total)*100,2)
WHERE report_name = 'STATE'
AND report_type = 'R'
AND parent_category = i.parent_category;
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/i.org_total)*100,2)
WHERE report_name = 'STATE'
AND report_type = 'R'
AND parent_category = i.parent_category;
UPDATE imc_reports_tempinfo
SET per_pct = round((per_cnt/i.per_total)*100,2)
WHERE report_name = 'STATE'
AND report_type = 'R'
AND parent_category = i.parent_category;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
org_pct,
time_stamp)
SELECT 'STATE','G', category, parent_category,
org_cnt, org_pct, sysdate
FROM (SELECT category, parent_category, org_cnt, org_pct
FROM imc_reports_tempinfo
WHERE report_name = 'STATE'
AND report_type = 'R'
AND org_cnt IS NOT NULL
AND NOT (org_pct = 100 AND parent_category IS NULL)
AND category <> rp_msg_total
AND parent_category = i.parent_category
ORDER BY org_cnt DESC)
WHERE rownum < 6;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
per_cnt,
per_pct,
time_stamp)
SELECT 'STATE','G', category, parent_category,
per_cnt, per_pct, sysdate
FROM (SELECT category, parent_category, per_cnt, per_pct
FROM imc_reports_tempinfo
WHERE report_name = 'STATE'
AND report_type = 'R'
AND category <> rp_msg_total
AND per_cnt IS NOT NULL
AND NOT (per_pct = 100 AND parent_category IS NULL)
AND parent_category = i.parent_category
ORDER BY per_cnt DESC)
WHERE rownum < 6;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
parent_category,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'STATE','R', i.parent_category, rp_msg_total,
nvl(i.org_total,0),'100.00',nvl(i.per_total,0),'100.00',sysdate
FROM dual;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
parent_category,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'STATE','G',i.parent_category,rp_msg_all_others,
(i.org_total - sum(imc_tmp.org_cnt)),
round(((i.org_total - sum(imc_tmp.org_cnt))/i.org_total) * 100,2),
(i.per_total - sum(imc_tmp.per_cnt)),
round(((i.per_total - sum(imc_tmp.per_cnt))/i.per_total) * 100,2),
sysdate
FROM imc_reports_tempinfo imc_tmp
WHERE imc_tmp.report_name = 'STATE'
AND imc_tmp.report_type = 'G'
AND imc_tmp.parent_category = i.parent_category;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
parent_category,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'STATE','G',i.parent_category,rp_msg_all_others, 0, 0,
(i.per_total - sum(imc_tmp.per_cnt)),
round(((i.per_total - sum(imc_tmp.per_cnt))/i.per_total) * 100,2),
sysdate
FROM imc_reports_tempinfo imc_tmp
WHERE imc_tmp.report_name = 'STATE'
AND imc_tmp.report_type = 'G'
AND imc_tmp.parent_category = i.parent_category;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
parent_category,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'STATE','G',i.parent_category,rp_msg_all_others,
(i.org_total - sum(imc_tmp.org_cnt)),
round(((i.org_total - sum(imc_tmp.org_cnt))/i.org_total) * 100,2),
0, 0, sysdate
FROM imc_reports_tempinfo imc_tmp
WHERE imc_tmp.report_name = 'STATE'
AND imc_tmp.report_type = 'G'
AND imc_tmp.parent_category = i.parent_category;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
time_stamp)
SELECT 'DUPLICATE', 'R',
decode(allcount.dn, 0, rp_msg_no_dupl,
1, allcount.dr || ' ' || rp_msg_dupl,
allcount.dr || ' ' || rp_msg_dupls) category, allcount.dn,
sum(decode(allcount.ptype,'ORGANIZATION',allcount.dc,0)) ocount
, sum(decode(allcount.ptype,'PERSON',allcount.dc,0)) pcount
, sysdate
FROM
(SELECT x.ptype, rng.rng_no dn, rng.dupl_rng dr, count(1) dc
FROM (SELECT hp.customer_key || hl.address_key key_comb,
decode(hp.party_type,'ORGANIZATION',count(*),0) org_count,
decode(hp.party_type,'PERSON',count(*),0) per_count,
hp.party_type ptype
FROM hz_parties hp, hz_locations hl, hz_party_sites hs
WHERE hp.party_id = hs.party_id (+)
AND hp.party_type in ('ORGANIZATION','PERSON')
AND hp.status = 'A'
AND hs.identifying_address_flag (+) = 'Y'
AND hs.location_id = hl.location_id (+)
GROUP BY hp.customer_key || hl.address_key, hp.party_type) x,
imc_dupl_range_v rng
WHERE ((x.org_count between rng.min and rng.max)
OR (x.per_count between rng.min and rng.max))
GROUP BY rng.dupl_rng, rng.rng_no, ptype
) allcount
GROUP BY allcount.dn, allcount.dr;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
time_stamp)
SELECT /*+ parallel(v3) */ 'DUPLICATE', 'R'
, decode(val,'0',rp_msg_no_dupl,'1',val||' '||rp_msg_dupl, val||' '||rp_msg_dupls) category
, decode(val,'0','0','1','1','2','2','3','3','4-10','4','11-100','5','101-10000','6','10001-9999999999','7') dn
, sum(decode(pt, 'ORGANIZATION', tpc, 0)) otpc
, sum(decode(pt, 'PERSON', tpc, 0)) ptpc
, sysdate
FROM
( select /*+ parallel(v2) */
val, pt, sum(occurence) occur, sum(totalptycount) tpc
from
( select /*+ parallel(v1) */
decode(least(col1,4),col1,to_char(col1-1)
, decode(least(col1,11),col1 ,'4-10'
, decode(least(col1,101),col1 ,'11-100'
, decode(least(col1,10001),col1 ,'101-10000'
, '10001-9999999999')))) val
, pt, count(*) occurence, col1*count(*) totalptycount
from
( select /*+ parallel(hp) parallel(hs) parallel(hl) use_hash(hs,hl) */
count(*) col1, hp.party_type pt
from hz_parties hp, hz_party_sites hs, hz_locations hl
where hp.party_type in ('ORGANIZATION','PERSON')
and hp.status = 'A'
and hp.party_id = hs.party_id(+)
and hs.identifying_address_flag(+) = 'Y'
and hs.location_id = hl.location_id(+)
group by hp.customer_key || hl.address_key, hp.party_type ) v1
group by decode(least(col1,4),col1,to_char(col1-1),
decode(least(col1,11),col1 ,'4-10',
decode(least(col1,101),col1 ,'11-100',
decode(least(col1,10001),col1 ,'101-10000',
'10001-9999999999')))), pt, col1 ) v2
group by val, pt
) v3
GROUP BY val;
SELECT nvl(sum(org_cnt),0), nvl(sum(per_cnt),0)
INTO rp_dupl_org_cnt, rp_dupl_per_cnt
FROM imc_reports_tempinfo
WHERE report_name = 'DUPLICATE'
AND report_type = 'R';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
time_stamp)
SELECT 'DUPLICATE', 'R',
decode(rng.rng_no, 0, rp_msg_no_dupl,
1, rng.dupl_rng || ' ' || rp_msg_dupl,
rng.dupl_rng || ' ' || rp_msg_dupls),
rng.rng_no, 0, 0, sysdate
FROM imc_dupl_range_v rng
WHERE NOT EXISTS (SELECT '1' FROM imc_reports_tempinfo tmp
WHERE tmp.report_name = 'DUPLICATE'
AND tmp.report_type = 'R'
AND tmp.parent_category = rng.rng_no);
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_dupl_org_cnt)*100,2),
per_pct = round((per_cnt/rp_dupl_per_cnt)*100,2)
WHERE report_name = 'DUPLICATE'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_dupl_org_cnt)*100,2),
per_pct = 0
WHERE report_name = 'DUPLICATE'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = 0,
per_pct = round((per_cnt/rp_dupl_per_cnt)*100,2)
WHERE report_name = 'DUPLICATE'
AND report_type = 'R';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
time_stamp)
SELECT 'DUPLICATE','R',rp_msg_total, nvl(rp_dupl_org_cnt,0),'100.00',
nvl(rp_dupl_per_cnt,0),'100.00', sysdate
FROM dual;
SELECT category, to_number(parent_category)
FROM IMC_REPORTS_TEMPINFO
WHERE report_name = 'PRE-GROWTH'
AND report_type = 'R'
ORDER BY parent_category;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
rel_cnt,
total_cnt,
time_stamp)
SELECT 'PRE-GROWTH', 'R', month, month_no,
sum(org_count), sum(per_count),
sum(rel_count), sum(tot_count), sysdate
FROM (SELECT decode(party_type,'ORGANIZATION',count(*),0) org_count,
decode(party_type,'PERSON',count(*),0) per_count,
decode(party_type,'PARTY_RELATIONSHIP',count(*),0) rel_count,
count(*) tot_count,
to_char(creation_date,'Mon-YY') month_name,
to_number (to_char(creation_date,'MM')) month_num
FROM hz_parties
WHERE creation_date >= add_months(sysdate,-23)
GROUP BY to_char(creation_date,'Mon-YY'),
to_number(to_char(creation_date,'MM')), party_type),
imc_growth_time_v gro
WHERE gro.month = month_name (+)
GROUP BY month_no, month;
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
rel_cnt,
total_cnt,
time_stamp)
SELECT 'PRE-GROWTH', 'R', month_name,
decode(month_name,
to_char(add_months(sysdate, -23), 'Mon-YY'), 1,
to_char(add_months(sysdate, -22), 'Mon-YY'), 2,
to_char(add_months(sysdate, -21), 'Mon-YY'), 3,
to_char(add_months(sysdate, -20), 'Mon-YY'), 4,
to_char(add_months(sysdate, -19), 'Mon-YY'), 5,
to_char(add_months(sysdate, -18), 'Mon-YY'), 6,
to_char(add_months(sysdate, -17), 'Mon-YY'), 7,
to_char(add_months(sysdate, -16), 'Mon-YY'), 8,
to_char(add_months(sysdate, -15), 'Mon-YY'), 9,
to_char(add_months(sysdate, -14), 'Mon-YY'), 10,
to_char(add_months(sysdate, -13), 'Mon-YY'), 11,
to_char(add_months(sysdate, -12), 'Mon-YY'), 12,
to_char(add_months(sysdate, -11), 'Mon-YY'), 13,
to_char(add_months(sysdate, -10), 'Mon-YY'), 14,
to_char(add_months(sysdate, -9), 'Mon-YY'), 15,
to_char(add_months(sysdate, -8), 'Mon-YY'), 16,
to_char(add_months(sysdate, -7), 'Mon-YY'), 17,
to_char(add_months(sysdate, -6), 'Mon-YY'), 18,
to_char(add_months(sysdate, -5), 'Mon-YY'), 19,
to_char(add_months(sysdate, -4), 'Mon-YY'), 20,
to_char(add_months(sysdate, -3), 'Mon-YY'), 21,
to_char(add_months(sysdate, -2), 'Mon-YY'), 22,
to_char(add_months(sysdate, -1), 'Mon-YY'), 23,
to_char(sysdate, 'Mon-YY'), 24
) month_no,
sum(org_count), sum(per_count),
sum(rel_count), sum(tot_count), sysdate
FROM (SELECT decode(party_type,'ORGANIZATION',count(*),0) org_count,
decode(party_type,'PERSON',count(*),0) per_count,
decode(party_type,'PARTY_RELATIONSHIP',count(*),0) rel_count,
count(*) tot_count,
to_char(creation_date,'Mon-YY') month_name,
to_number (to_char(creation_date,'MM')) month_num
FROM hz_parties
WHERE creation_date >= add_months(sysdate,-23)
GROUP BY to_char(creation_date,'Mon-YY'),
to_number(to_char(creation_date,'MM')), party_type)
GROUP BY month_name;
INSERT INTO imc_reports_tempinfo(report_name, report_type, category, parent_category,
org_cnt, per_cnt, rel_cnt, total_cnt, time_stamp)
VALUES ('PRE-GROWTH', 'R', l_pregrowth_tbl(i).month_name, i, null, null, null, null, sysdate);
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
parent_category,
org_cnt,
per_cnt,
rel_cnt,
total_cnt,
time_stamp)
SELECT 'GROWTH', 'R', category, parent_category,
org_cnt, per_cnt, rel_cnt, total_cnt, sysdate
FROM (SELECT a.category,
to_number(a.parent_category) parent_category,
nvl(sum(d.org_cnt),0) org_cnt,
nvl(sum(d.per_cnt),0) per_cnt,
nvl(sum(d.rel_cnt),0) rel_cnt,
nvl(sum(d.total_cnt),0) total_cnt
FROM imc_reports_tempinfo a, imc_reports_tempinfo d
WHERE a.report_name = 'PRE-GROWTH'
AND d.report_name = 'PRE-GROWTH'
AND to_number(d.parent_category) <= to_number(a.parent_category)
AND UPPER(d.parent_category) = LOWER(d.parent_category)
AND UPPER(a.parent_category) = LOWER(a.parent_category)
GROUP BY a.parent_category,a.category
ORDER BY to_number(a.parent_category)) ;
DELETE imc_reports_tempinfo
WHERE report_name = 'PRE-GROWTH';
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
WHERE report_name = 'GROWTH'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = 0,
per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
WHERE report_name = 'GROWTH'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
per_pct = 0,
rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
WHERE report_name = 'GROWTH'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
rel_pct = 0,
total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
WHERE report_name = 'GROWTH'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = 0,
per_pct = 0,
rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
WHERE report_name = 'GROWTH'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
per_pct = 0,
rel_pct = 0,
total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
WHERE report_name = 'GROWTH'
AND report_type = 'R';
UPDATE imc_reports_tempinfo
SET org_pct = 0,
per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
rel_pct = 0,
total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
WHERE report_name = 'GROWTH'
AND report_type = 'R';
INSERT INTO imc_reports_tempinfo(report_name,
report_type,
category,
org_cnt,
org_pct,
per_cnt,
per_pct,
rel_cnt,
rel_pct,
total_cnt,
total_pct,
time_stamp)
SELECT 'GROWTH','R',rp_msg_total,
nvl(rp_grth_org_cnt,0),'100.00',
nvl(rp_grth_per_cnt,0),'100.00',
nvl(rp_grth_rel_cnt,0),'100.00',
nvl(rp_grth_total_cnt,0),'100.00',
sysdate
FROM dual;
l_select_stmt VARCHAR2(2000);
SELECT rpt.lookup_code, substrb(rs.category,1,30)
FROM imc_lookups rpt, imc_reports_summary rs
WHERE rpt.lookup_type = 'COMPLETENESS_REPORTS'
AND rpt.enabled_flag = 'Y'
AND rpt.lookup_code = rs.parent_category
AND rs.report_name = 'COMPLRPT_STATUS'
AND rs.report_type = 'A';
SELECT substrb(t.description,instr(t.description,'.')+1), substrb(t.description,1,instr(t.description,'.')-1)
, t.lookup_code
FROM imc_lookups r, imc_lookups t, imc_lookups a
WHERE r.lookup_type = l_report_name
AND r.enabled_flag = 'Y'
AND r.lookup_code = t.lookup_code
and t.lookup_type = a.lookup_code
and a.lookup_type = l_type
AND l_system_date BETWEEN r.start_date_active AND nvl(r.end_date_active, l_system_date);
SELECT decode(to_char(l_system_date,'DD'),'01',1,0),
decode(to_char(l_system_date,'MM'),'01',1,'04',1,'07',1,'10',1,0),
to_char(l_system_date, 'YYYY-MM')
FROM dual;
SELECT 'X'
FROM IMC_REPORTS_SUMMARY a, IMC_LOOKUPS b
WHERE a.report_name = b.lookup_code
AND b.lookup_type = 'COMPLETENESS_REPORTS'
AND a.report_type = 'M'
AND rownum = 1;
SELECT 'X'
FROM IMC_REPORTS_SUMMARY
WHERE report_name = l_report_name
AND report_type = 'M'
AND parent_category = to_char(l_date,'YYYY-MM')
AND rownum = 1;
SELECT 'X'
FROM IMC_REPORTS_SUMMARY
WHERE report_name = l_report_name
AND report_type = 'Q'
AND parent_category = to_char(l_date,'YYYY-')||'Q'||to_char(l_date,'Q')
AND rownum = 1;
SELECT 'X'
FROM IMC_REPORTS_SUMMARY
WHERE report_name = l_report_name
AND report_type = 'D'
AND parent_category = to_char(l_date,'YYYY-MM')
AND rownum = 1;
SELECT count(1)
FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
WHERE p.status in ('A','I')
AND p.party_type = 'ORGANIZATION'
AND p.party_id = op.party_id
AND sysdate between op.effective_start_date and nvl(op.effective_end_date, sysdate);
SELECT count(1)
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pp
WHERE p.status in ('A','I')
AND p.party_type = 'PERSON'
AND p.party_id = pp.party_id
AND sysdate between pp.effective_start_date and nvl(pp.effective_end_date, sysdate);
SELECT count(1)
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p
, HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt
WHERE oc.party_relationship_id = r.relationship_id
AND r.subject_type = 'PERSON'
AND r.subject_id = p.party_id
AND ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND ca.class_code = 'PARTY_REL_GRP_CONTACTS'
AND rt.relationship_type_id = ca.owner_table_id
AND rt.subject_type = 'PERSON'
AND rt.forward_rel_code = r.relationship_code
AND rt.relationship_type = r.relationship_type
AND p.status in ('A','I');
delete_daily_score(l_report_name, l_system_date, l_return_status, l_msg_count, l_msg_data);
insert_daily_score(l_report_name, l_total_party, l_party_type
, l_attribute, l_attr_code, l_table_name
, l_system_date, l_parent_cat
, l_return_status, l_msg_count, l_msg_data);
insert_monthly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
, l_return_status, l_msg_count, l_msg_data);
insert_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
, l_return_status, l_msg_count, l_msg_data);
insert_monthly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
, l_return_status, l_msg_count, l_msg_data);
insert_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
, l_return_status, l_msg_count, l_msg_data);
update_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
, l_return_status, l_msg_count, l_msg_data);
update_monthly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
, l_return_status, l_msg_count, l_msg_data);
update_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
, l_return_status, l_msg_count, l_msg_data);
PROCEDURE delete_daily_score (
p_report_name IN VARCHAR2,
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
str VARCHAR2(2000);
savepoint delete_daily_score_pvt;
str := 'delete from imc_reports_summary '||
' where report_name = '''||p_report_name||''''||
' and parent_category = to_char(:p_date,''YYYY-MM'')'||
' and report_type = ''D''';
ROLLBACK TO delete_daily_score_pvt;
END delete_daily_score;
PROCEDURE insert_daily_score (
p_report_name IN VARCHAR2,
p_total_party IN NUMBER,
p_party_type IN VARCHAR2,
p_attribute IN VARCHAR2,
p_attr_code IN VARCHAR2,
p_table_name IN VARCHAR2,
p_system_date IN DATE,
p_parent_cat IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
table_prefix VARCHAR2(10);
savepoint insert_daily_score_pvt;
write_log('Start inserting daily score for completeness report: '||p_report_name);
str := 'insert into imc_reports_summary('||
' report_name, report_type,'||
' category, parent_category,'||
' total_cnt, total_pct,'||
' time_stamp )'||
' select '||l_perf_hint||
''''||p_report_name||''''||','||
'''D'''||','||
''''||p_attr_code||''''||','||
''''||p_parent_cat||''''||','||
'nvl(sum(decode('||table_prefix||p_attribute||', NULL, 0, 1)),0),'||
p_total_party ||','||
':p_date '||fromandwhere_str;
ROLLBACK TO insert_daily_score_pvt;
END insert_daily_score;
PROCEDURE insert_monthly_score (
p_report_name IN VARCHAR2,
p_total_party IN NUMBER,
p_total_attribute IN NUMBER,
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint insert_monthly_score_pvt;
write_log('Start inserting monthly score for completeness report: '||p_report_name);
insert into IMC_REPORTS_SUMMARY (
report_name,
report_type,
category,
parent_category,
org_cnt,
total_cnt,
total_pct,
time_stamp
) select
p_report_name,
'M',
NULL,
to_char(p_system_date, 'YYYY-MM'),
p_total_attribute,
(sum(total_cnt)),
(p_total_party*p_total_attribute),
-- (sum(total_cnt)/(p_total_attribute*p_total_party))*100,
-- p_total_party,
p_system_date
from IMC_REPORTS_SUMMARY
where report_name = p_report_name
and report_type = 'D'
and parent_category = to_char(p_system_date,'YYYY-MM');
ROLLBACK TO insert_monthly_score_pvt;
END insert_monthly_score;
PROCEDURE update_monthly_score (
p_report_name IN VARCHAR2,
p_total_party IN NUMBER,
p_total_attribute IN NUMBER,
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint update_monthly_score_pvt;
update IMC_REPORTS_SUMMARY
set total_cnt =
(select (sum(total_cnt))
from IMC_REPORTS_SUMMARY
where report_name = p_report_name
and parent_category = to_char(p_system_date,'YYYY-MM')
and report_type = 'D'),
total_pct = (p_total_party*p_total_attribute),
org_cnt = p_total_attribute,
time_stamp = p_system_date
where report_name = p_report_name
and report_type = 'M'
and parent_category = to_char(p_system_date,'YYYY-MM');
ROLLBACK TO update_monthly_score_pvt;
END update_monthly_score;
PROCEDURE insert_quarterly_score (
p_report_name IN VARCHAR2,
p_total_party IN NUMBER,
p_total_attribute IN NUMBER,
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint insert_quarterly_score_pvt;
write_log('Start inserting quarterly score for completeness report: '||p_report_name);
insert into IMC_REPORTS_SUMMARY (
report_name,
report_type,
category,
parent_category,
org_cnt,
total_cnt,
total_pct,
time_stamp
) select
p_report_name,
'Q',
NULL,
to_char(p_system_date, 'YYYY-')||'Q'||to_char(p_system_date,'Q'),
org_cnt,
total_cnt,
total_pct,
time_stamp
from IMC_REPORTS_SUMMARY
where report_name = p_report_name
and report_type = 'M'
and parent_category = to_char(p_system_date, 'YYYY-MM');
ROLLBACK TO insert_quarterly_score_pvt;
END insert_quarterly_score;
PROCEDURE update_quarterly_score (
p_report_name IN VARCHAR2,
p_total_party IN NUMBER,
p_total_attribute IN NUMBER,
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint update_quarterly_score_pvt;
update IMC_REPORTS_SUMMARY
set (total_cnt, total_pct, org_cnt, time_stamp) =
(select total_cnt, total_pct, org_cnt, time_stamp
from IMC_REPORTS_SUMMARY
where report_name = p_report_name
and report_type = 'M'
and parent_category = to_char(p_system_date,'YYYY-MM'))
where report_name = p_report_name
and report_type = 'Q'
and parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
ROLLBACK TO update_quarterly_score_pvt;
END update_quarterly_score;
SELECT 'X'
FROM IMC_REPORTS_SUMMARY
WHERE report_name = 'PARTY_ENRICH'
AND rownum = 1;
SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
, nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
, to_number(to_char(l_system_date, 'YYYY')) xy
, to_number(to_char(l_system_date, 'MM')) xm
FROM HZ_PARTY_INTERFACE
WHERE content_source_type = 'DNB'
AND status = 'P2';
SELECT MIN(iy), MIN(im),
TO_NUMBER(to_char(l_system_date, 'YYYY')) xy,
TO_NUMBER(to_char(l_system_date, 'MM')) xm
FROM (
SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
, nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
FROM HZ_PARTY_INTERFACE
WHERE content_source_type = 'DNB'
AND status = 'P2'
UNION
SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
, nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
FROM hz_imp_batch_summary
WHERE ORIGINAL_SYSTEM = 'DNB'
AND IMPORT_STATUS <> 'PENDING'
);
SELECT decode(to_char(l_system_date,'DD'),'01',1,0),
decode(to_char(l_system_date,'MM'),'01',1,'04',1,'07',1,'10',1,0)
FROM dual;
SELECT add_months(to_date(parent_category,'YYYY-MM'),1)-1
, decode(substrb(parent_category,6,2),'03',1,'06',1,'09',1,'12',1,0)
, decode(parent_category, to_char(sysdate,'YYYY-MM'), 1, 0)
FROM IMC_REPORTS_SUMMARY
WHERE report_name = 'PARTY_ENRICH'
AND report_type = 'M';
SELECT 'X'
FROM IMC_REPORTS_SUMMARY
WHERE report_name = 'PARTY_ENRICH'
AND report_type = 'M'
AND parent_category = to_char(l_date,'YYYY-MM')
AND rownum = 1;
SELECT 'X'
FROM IMC_REPORTS_SUMMARY
WHERE report_name = 'PARTY_ENRICH'
AND report_type = 'Q'
AND parent_category = to_char(l_date,'YYYY-')||'Q'||to_char(l_date,'Q')
AND rownum = 1;
DELETE FROM IMC_REPORTS_SUMMARY
WHERE REPORT_NAME = 'PARTY_ENRICH';
INSERT INTO IMC_REPORTS_SUMMARY (
REPORT_NAME
,REPORT_TYPE
,CATEGORY
,PARENT_CATEGORY
,TIME_STAMP )
SELECT
'PARTY_ENRICH'
,'M'
,NULL
,to_char(I)||'-'||lookup_code
,sysdate
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'MONTH'
AND ENABLED_FLAG = 'Y'
GROUP BY lookup_code;
DELETE IMC_REPORTS_SUMMARY
WHERE report_name = 'PARTY_ENRICH'
AND (parent_category < to_char(l_min_year)||'-'||lpad(to_char(l_min_month),2,'0')
OR parent_category > to_char(l_max_year)||'-'||lpad(to_char(l_max_month),2,'0'));
update_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
update_menrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
insert_qenrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
insert_qenrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
insert_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
update_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
update_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
update_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
PROCEDURE insert_menrich_score (
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint insert_menrich_score_pvt;
write_log('Start inserting monthly data for enrichment report: '||p_system_date);
INSERT INTO IMC_REPORTS_SUMMARY (
report_name
,report_type
,category
,parent_category
,org_cnt
,total_cnt
,total_pct
,time_stamp
) SELECT
'PARTY_ENRICH'
,'M'
,NULL
,to_char(p_system_date,'YYYY-MM')
,nvl(sum(decode(count(1),0,0,1)),0)
,l_enpty_count
,l_party_count
,p_system_date
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND to_char(last_update_date,'YYYY-MM') = to_char(p_system_date,'YYYY-MM')
GROUP BY party_id;
INSERT INTO IMC_REPORTS_SUMMARY (
report_name
,report_type
,category
,parent_category
,org_cnt
,total_cnt
,total_pct
,time_stamp
) SELECT
'PARTY_ENRICH'
,'M'
,NULL
,to_char(p_system_date,'YYYY-MM')
,SUM(org_enriched_for_period)
,l_enpty_count
,l_party_count
,p_system_date
FROM (
SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND content_source_type = 'DNB'
AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
GROUP BY party_id
UNION ALL
SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
FROM hz_imp_batch_summary
WHERE ORIGINAL_SYSTEM = 'DNB'
AND IMPORT_STATUS <> 'PENDING'
AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
) ;
ROLLBACK TO insert_menrich_score_pvt;
END insert_menrich_score;
PROCEDURE update_menrich_score (
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint update_menrich_score_pvt;
UPDATE IMC_REPORTS_SUMMARY
SET (org_cnt, total_cnt,total_pct, time_stamp) =
(SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND to_char(last_update_date,'YYYY-MM') = to_char(p_system_date,'YYYY-MM')
GROUP BY party_id)
WHERE report_name = 'PARTY_ENRICH'
AND report_type = 'M'
AND parent_category = to_char(p_system_date,'YYYY-MM');
UPDATE IMC_REPORTS_SUMMARY
SET (org_cnt, total_cnt,total_pct, time_stamp) =
(SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
FROM (
SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND content_source_type = 'DNB'
AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
GROUP BY party_id
UNION ALL
SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
FROM hz_imp_batch_summary
WHERE ORIGINAL_SYSTEM = 'DNB'
AND IMPORT_STATUS <> 'PENDING'
AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
)
)
WHERE report_name = 'PARTY_ENRICH'
AND report_type = 'M'
AND parent_category = to_char(p_system_date,'YYYY-MM');
ROLLBACK TO update_menrich_score_pvt;
END update_menrich_score;
PROCEDURE insert_qenrich_score (
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint insert_qenrich_score_pvt;
write_log('Start inserting quarterly data for enrichment report: '||p_system_date);
INSERT INTO IMC_REPORTS_SUMMARY (
report_name
,report_type
,category
,parent_category
,org_cnt
,total_cnt
,total_pct
,time_stamp
) SELECT
'PARTY_ENRICH'
,'Q'
,NULL
,to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q')
,nvl(sum(decode(count(1),0,0,1)),0)
,l_enpty_count
,l_party_count
,p_system_date
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
GROUP BY party_id;
INSERT INTO IMC_REPORTS_SUMMARY (
report_name
,report_type
,category
,parent_category
,org_cnt
,total_cnt
,total_pct
,time_stamp
) SELECT
'PARTY_ENRICH'
,'Q'
,NULL
,to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q')
,SUM(org_enriched_for_period)
,l_enpty_count
,l_party_count
,p_system_date
FROM (
SELECT nvl(SUM(decode(count(1),0,0,1)),0) org_enriched_for_period
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND content_source_type = 'DNB'
AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
GROUP BY party_id
UNION ALL
SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
FROM hz_imp_batch_summary
WHERE ORIGINAL_SYSTEM = 'DNB'
AND IMPORT_STATUS <> 'PENDING'
AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
);
ROLLBACK TO insert_qenrich_score_pvt;
END insert_qenrich_score;
PROCEDURE update_qenrich_score (
p_system_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(30);
savepoint update_qenrich_score_pvt;
UPDATE IMC_REPORTS_SUMMARY
SET (org_cnt, total_cnt,total_pct, time_stamp) =
(SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
GROUP BY party_id)
WHERE report_name = 'PARTY_ENRICH'
AND report_type = 'Q'
AND parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
UPDATE IMC_REPORTS_SUMMARY
SET (org_cnt, total_cnt,total_pct, time_stamp) =
(SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
FROM (
SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND content_source_type = 'DNB'
AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
GROUP BY party_id
UNION ALL
SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
FROM hz_imp_batch_summary
WHERE ORIGINAL_SYSTEM = 'DNB'
AND IMPORT_STATUS <> 'PENDING'
AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
)
)
WHERE report_name = 'PARTY_ENRICH'
AND report_type = 'Q'
AND parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
ROLLBACK TO update_qenrich_score_pvt;
END update_qenrich_score;
SELECT count(1)
FROM HZ_PARTIES
WHERE status in ('A','I')
AND party_type = l_party_type
AND trunc(creation_date) <= trunc(l_date);
SELECT count(1)
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p
, HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt
WHERE oc.party_relationship_id = r.relationship_id
AND r.subject_type = 'PERSON'
AND r.subject_id = p.party_id
AND ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND ca.class_code = 'PARTY_REL_GRP_CONTACTS'
AND rt.relationship_type_id = ca.owner_table_id
AND rt.subject_type = 'PERSON'
AND rt.forward_rel_code = r.relationship_code
AND rt.relationship_type = r.relationship_type
AND p.status in ('A','I')
AND trunc(p.creation_date) <= trunc(l_date);
SELECT nvl(sum(decode(count(1),0,0,1)),0)
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND content_source_type = 'DNB'
AND trunc(last_update_date) <= trunc(l_date)
GROUP BY party_id;
SELECT SUM(total_org_enriched) FROM (
SELECT nvl(sum(decode(count(1),0,0,1)),0) total_org_enriched
FROM HZ_PARTY_INTERFACE
WHERE status = 'P2'
AND content_source_type = 'DNB'
AND TRUNC(last_update_date) <= TRUNC(l_date)
GROUP BY party_id
UNION ALL
SELECT nvl(SUM(parties_imported),0) total_org_enriched
FROM hz_imp_batch_summary
WHERE ORIGINAL_SYSTEM = 'DNB'
AND IMPORT_STATUS <> 'PENDING'
AND TRUNC(last_update_date) <= TRUNC(l_date)
);
str := ' from (select owner_table_id '||p_attribute||
' from HZ_CODE_ASSIGNMENTS c'||
' where owner_table_name = ''HZ_PARTIES'''||
' and status = ''A'''||
' and :p_date between c.start_date_active and nvl(c.end_date_active,:p_date)'||
' group by owner_table_id) ca';
str := ' from (select r.party_id '||p_attribute||
' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
' where pty.status in (''A'',''I'')'||
' and orgcnt.party_relationship_id = r.relationship_id' ||
' and r.subject_type = ''PERSON'''||
' and r.party_id = pty.party_id'||
' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
' and rt.relationship_type_id = ca.owner_table_id'||
' and rt.subject_type = ''PERSON'''||
' and rt.forward_rel_code = r.relationship_code'||
' and rt.relationship_type = r.relationship_type'||
' and exists (select 1 from HZ_ORG_CONTACT_ROLES ocr'||
' where ocr.status = ''A'''||
' and ocr.org_contact_id = orgcnt.org_contact_id)) ocrole';
SELECT meaning, description, start_date_active
FROM IMC_LOOKUPS
WHERE lookup_type = 'COMPLETENESS_REPORTS'
AND lookup_code = p_report_code;
FND_LOOKUP_VALUES_PKG.update_row(
x_lookup_type => 'COMPLETENESS_REPORTS',
x_security_group_id => NULL,
x_view_application_id => 879,
x_lookup_code => p_report_code,
x_tag => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active => l_start_date,
x_end_date_active => sysdate,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => l_mean,
x_description => l_desc,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id);