eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IMC_REPORTS_SUMMARY_PKG dependencies on HZ_PARTIES

Line 56: FROM hz_parties

52: add_months(sysdate,-23),count(*),0)),
53: SUM(count(*))
54: INTO rp_grth_per_cnt, rp_grth_org_cnt, rp_grth_rel_cnt,
55: rp_grth_total_cnt, rp_total_cnt
56: FROM hz_parties
57: GROUP BY party_type,creation_date;
58:
59: -- for total person, organization and relationship count
60: -- the party must be active. Don't include inactive,

Line 67: FROM hz_parties

63: SUM(DECODE(party_type,'PERSON', count(*),0)),
64: SUM(DECODE(party_type,'ORGANIZATION', count(*),0)),
65: SUM(DECODE(party_type,'PARTY_RELATIONSHIP',count(*),0))
66: INTO rp_per_cnt, rp_org_cnt, rp_rel_cnt
67: FROM hz_parties
68: WHERE status = 'A'
69: GROUP BY party_type;
70:
71: g_party_exists := 2;

Line 76: write_log('No data found in HZ_PARTIES:' || sqlerrm);

72:
73: EXCEPTION
74: WHEN NO_DATA_FOUND THEN
75: g_party_exists := 1;
76: write_log('No data found in HZ_PARTIES:' || sqlerrm);
77:
78: WHEN OTHERS THEN
79: write_log('Error:' || sqlerrm);
80:

Line 123: -- Run sql statement twice is much faster than having outer join to HZ_PARTIES

119: -- fix bug 3296096, get CUSTOMER_CATEGORY from
120: -- HZ_CODE_ASSIGNMENTS table
121:
122: -- fix perf bug 3638775, here are the steps
123: -- Run sql statement twice is much faster than having outer join to HZ_PARTIES
124: -- 1) get all industry with customer_category
125: -- 2) count the total number of parties with customer_category
126: -- 3) deduct the total number of organization by total number calculated at step 2
127: -- the result will be those undefined industry

Line 140: FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp

136: SELECT 'INDUSTRY','R',
137: industry, 'YES' industry_code, sum(org_count), sysdate
138: FROM (SELECT lkp.meaning industry,
139: decode(pty.party_type, 'ORGANIZATION', count(*), 0) org_count
140: FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
141: WHERE look.class_category = 'CUSTOMER_CATEGORY'
142: AND look.owner_table_name = 'HZ_PARTIES'
143: AND pty.party_id = look.owner_table_id
144: AND pty.party_type = 'ORGANIZATION'

Line 142: AND look.owner_table_name = 'HZ_PARTIES'

138: FROM (SELECT lkp.meaning industry,
139: decode(pty.party_type, 'ORGANIZATION', count(*), 0) org_count
140: FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
141: WHERE look.class_category = 'CUSTOMER_CATEGORY'
142: AND look.owner_table_name = 'HZ_PARTIES'
143: AND pty.party_id = look.owner_table_id
144: AND pty.party_type = 'ORGANIZATION'
145: AND pty.status = 'A'
146: AND look.class_code = lkp.lookup_code

Line 155: FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp

151:
152: -- Step 2
153: SELECT count(1) INTO l_nd_ind
154: FROM (SELECT 1
155: FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
156: WHERE look.class_category = 'CUSTOMER_CATEGORY'
157: AND look.owner_table_name = 'HZ_PARTIES'
158: AND pty.party_id = look.owner_table_id
159: AND pty.party_type = 'ORGANIZATION'

Line 157: AND look.owner_table_name = 'HZ_PARTIES'

153: SELECT count(1) INTO l_nd_ind
154: FROM (SELECT 1
155: FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
156: WHERE look.class_category = 'CUSTOMER_CATEGORY'
157: AND look.owner_table_name = 'HZ_PARTIES'
158: AND pty.party_id = look.owner_table_id
159: AND pty.party_type = 'ORGANIZATION'
160: AND pty.status = 'A'
161: AND look.class_code = lkp.lookup_code

Line 279: -- Fix perf bug 4915034, use parallel hint on HZ_PARTIES table

275: -- having country information, we will get the number of parties which do
276: -- not have country information
277:
278: -- Step 1
279: -- Fix perf bug 4915034, use parallel hint on HZ_PARTIES table
280: INSERT INTO imc_reports_tempinfo(report_name,
281: report_type,
282: category,
283: parent_category,

Line 295: FROM hz_parties pty

291: FROM (SELECT country, sum(org_count) org_count, sum(per_count) per_count
292: FROM (SELECT /*+ parallel(pty) */ pty.country,
293: DECODE(pty.party_type,'ORGANIZATION',count(*),0) org_count,
294: DECODE(pty.party_type,'PERSON',count(*),0) per_count
295: FROM hz_parties pty
296: WHERE pty.party_type IN ('ORGANIZATION','PERSON')
297: AND pty.status = 'A'
298: GROUP BY pty.country, pty.party_type )
299: GROUP BY country ) pty ,

Line 507: FROM hz_parties pty

503: FROM (SELECT nvl(pty.state,rp_msg_undefined) state,
504: pty.country,
505: decode(pty.party_type,'ORGANIZATION',count(*),0) org_count,
506: decode(pty.party_type,'PERSON',count(*),0) per_count
507: FROM hz_parties pty
508: WHERE pty.party_type IN ('ORGANIZATION','PERSON')
509: AND pty.status = 'A'
510: GROUP BY pty.country, pty.state, pty.party_type)
511: GROUP BY country, state;

Line 716: FROM hz_parties hp, hz_locations hl, hz_party_sites hs

712: FROM (SELECT hp.customer_key || hl.address_key key_comb,
713: decode(hp.party_type,'ORGANIZATION',count(*),0) org_count,
714: decode(hp.party_type,'PERSON',count(*),0) per_count,
715: hp.party_type ptype
716: FROM hz_parties hp, hz_locations hl, hz_party_sites hs
717: WHERE hp.party_id = hs.party_id (+)
718: AND hp.party_type in ('ORGANIZATION','PERSON')
719: AND hp.status = 'A'
720: AND hs.identifying_address_flag (+) = 'Y'

Line 758: from hz_parties hp, hz_party_sites hs, hz_locations hl

754: , pt, count(*) occurence, col1*count(*) totalptycount
755: from
756: ( select /*+ parallel(hp) parallel(hs) parallel(hl) use_hash(hs,hl) */
757: count(*) col1, hp.party_type pt
758: from hz_parties hp, hz_party_sites hs, hz_locations hl
759: where hp.party_type in ('ORGANIZATION','PERSON')
760: and hp.status = 'A'
761: and hp.party_id = hs.party_id(+)
762: and hs.identifying_address_flag(+) = 'Y'

Line 899: FROM hz_parties

895: decode(party_type,'PARTY_RELATIONSHIP',count(*),0) rel_count,
896: count(*) tot_count,
897: to_char(creation_date,'Mon-YY') month_name,
898: to_number (to_char(creation_date,'MM')) month_num
899: FROM hz_parties
900: WHERE creation_date >= add_months(sysdate,-23)
901: GROUP BY to_char(creation_date,'Mon-YY'),
902: to_number(to_char(creation_date,'MM')), party_type),
903: imc_growth_time_v gro

Line 951: FROM hz_parties

947: decode(party_type,'PARTY_RELATIONSHIP',count(*),0) rel_count,
948: count(*) tot_count,
949: to_char(creation_date,'Mon-YY') month_name,
950: to_number (to_char(creation_date,'MM')) month_num
951: FROM hz_parties
952: WHERE creation_date >= add_months(sysdate,-23)
953: GROUP BY to_char(creation_date,'Mon-YY'),
954: to_number(to_char(creation_date,'MM')), party_type)
955: GROUP BY month_name;

Line 1251: -- description like HZ_PARTIES.DUNS_NUMBER

1247: AND rs.report_name = 'COMPLRPT_STATUS'
1248: AND rs.report_type = 'A';
1249:
1250: -- get all attributes and decode the description to table name and column name
1251: -- description like HZ_PARTIES.DUNS_NUMBER
1252: cursor get_all_attributes(l_report_name VARCHAR2, l_system_date DATE, l_type VARCHAR2) is
1253: SELECT substrb(t.description,instr(t.description,'.')+1), substrb(t.description,1,instr(t.description,'.')-1)
1254: , t.lookup_code
1255: FROM imc_lookups r, imc_lookups t, imc_lookups a

Line 1309: FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op

1305:
1306: -- get party count of organization
1307: cursor get_org_count is
1308: SELECT count(1)
1309: FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
1310: WHERE p.status in ('A','I')
1311: AND p.party_type = 'ORGANIZATION'
1312: AND p.party_id = op.party_id
1313: AND sysdate between op.effective_start_date and nvl(op.effective_end_date, sysdate);

Line 1318: FROM HZ_PARTIES p, HZ_PERSON_PROFILES pp

1314:
1315: -- get party count of person
1316: cursor get_per_count is
1317: SELECT count(1)
1318: FROM HZ_PARTIES p, HZ_PERSON_PROFILES pp
1319: WHERE p.status in ('A','I')
1320: AND p.party_type = 'PERSON'
1321: AND p.party_id = pp.party_id
1322: AND sysdate between pp.effective_start_date and nvl(pp.effective_end_date, sysdate);

Line 1327: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p

1323:
1324: -- get contact count
1325: cursor get_cnt_count is
1326: SELECT count(1)
1327: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p
1328: , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt
1329: WHERE oc.party_relationship_id = r.relationship_id
1330: AND r.subject_type = 'PERSON'
1331: AND r.subject_id = p.party_id

Line 1575: IF(p_table_name = 'HZ_PARTIES') THEN

1571: write_log('Start inserting daily score for completeness report: '||p_report_name);
1572: write_log('>> p_total_party:'||p_total_party||' p_party_type:'||p_party_type||' p_attribute: '||p_attribute);
1573: write_log('>> p_attr_code: '||p_attr_code||' p_table_name: '||p_table_name||' p_parent_cat: '||p_parent_cat);
1574:
1575: IF(p_table_name = 'HZ_PARTIES') THEN
1576: table_prefix := 'pty.';
1577: fromandwhere_str := get_party_clause(p_table_name, p_party_type, p_system_date, x_return_status);
1578: -- Perf Bug 6322629 (Add parallel hint for contact tables as volume of data is too high)
1579: IF (p_party_type = 'CONTACT') THEN

Line 2512: FROM HZ_PARTIES

2508:
2509: -- count ORGANIZATION or PERSON parties
2510: cursor get_pty_count(l_party_type VARCHAR2, l_date DATE) is
2511: SELECT count(1)
2512: FROM HZ_PARTIES
2513: WHERE status in ('A','I')
2514: AND party_type = l_party_type
2515: AND trunc(creation_date) <= trunc(l_date);
2516:

Line 2520: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p

2516:
2517: -- count CONTACT
2518: cursor get_contact_count(l_date DATE) is
2519: SELECT count(1)
2520: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p
2521: , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt
2522: WHERE oc.party_relationship_id = r.relationship_id
2523: AND r.subject_type = 'PERSON'
2524: AND r.subject_id = p.party_id

Line 2623: str := ' from HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||

2619:
2620: BEGIN
2621:
2622: IF(p_party_type = 'CONTACT') THEN
2623: str := ' from HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2624: ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2625: ' where pty.status in (''A'',''I'')'||
2626: ' and oc.party_relationship_id = r.relationship_id' ||
2627: ' and r.subject_type = ''PERSON'''||

Line 2637: str := ' from HZ_PARTIES pty'||

2633: ' and rt.subject_type = ''PERSON'''||
2634: ' and rt.forward_rel_code = r.relationship_code'||
2635: ' and rt.relationship_type = r.relationship_type';
2636: ELSE
2637: str := ' from HZ_PARTIES pty'||
2638: ' where pty.status in (''A'',''I'')'||
2639: ' and pty.party_type = '''||p_party_type||'''';
2640: END IF;
2641:

Line 2665: ' where owner_table_name = ''HZ_PARTIES'''||

2661:
2662: -- code assignment is only for organization report
2663: str := ' from (select owner_table_id '||p_attribute||
2664: ' from HZ_CODE_ASSIGNMENTS c'||
2665: ' where owner_table_name = ''HZ_PARTIES'''||
2666: ' and status = ''A'''||
2667: ' and :p_date between c.start_date_active and nvl(c.end_date_active,:p_date)'||
2668: ' group by owner_table_id) ca';
2669:

Line 2694: str := ' from HZ_PARTIES pty, '||p_table_name||' prof'||

2690: -- the following if may not be valid now
2691: IF(p_party_type = 'CONTACT') THEN
2692: NULL;
2693: ELSE
2694: str := ' from HZ_PARTIES pty, '||p_table_name||' prof'||
2695: ' where pty.status in (''A'',''I'')'||
2696: ' and pty.party_type = '''||p_party_type||''''||
2697: ' and pty.party_id = prof.party_id'||
2698: ' and :p_date between prof.effective_start_date and nvl(prof.effective_end_date,:p_date)';

Line 2734: str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt,'||

2730: -- if the reports is used for contact, then we need to use HZ_RELATIONSHIPS
2731: -- and HZ_ORG_CONTACTS table to find out that person
2732:
2733: IF(p_party_type = 'CONTACT') THEN
2734: str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt,'||
2735: ' HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r,'||
2736: ' HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2737: ' where pty.status in (''A'',''I'')'||
2738: ' and oc.party_relationship_id = r.relationship_id' ||

Line 2749: ' and contpt.owner_table_name = ''HZ_PARTIES'''||

2745: ' and rt.subject_type = ''PERSON'''||
2746: ' and rt.forward_rel_code = r.relationship_code'||
2747: ' and rt.relationship_type = r.relationship_type'||
2748: ' and pty.party_id = contpt.owner_table_id'||
2749: ' and contpt.owner_table_name = ''HZ_PARTIES'''||
2750: ' and contpt.status in (''A'',''I'')'||
2751: ' and contpt.contact_point_type = '''||l_contact_point_type||''''||
2752: ' and contpt.primary_flag = ''Y''';
2753: ELSE

Line 2754: str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt'||

2750: ' and contpt.status in (''A'',''I'')'||
2751: ' and contpt.contact_point_type = '''||l_contact_point_type||''''||
2752: ' and contpt.primary_flag = ''Y''';
2753: ELSE
2754: str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt'||
2755: ' where pty.status in (''A'',''I'')'||
2756: ' and pty.party_type = '''||p_party_type||''''||
2757: ' and pty.party_id = contpt.owner_table_id'||
2758: ' and contpt.owner_table_name = ''HZ_PARTIES'''||

Line 2758: ' and contpt.owner_table_name = ''HZ_PARTIES'''||

2754: str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt'||
2755: ' where pty.status in (''A'',''I'')'||
2756: ' and pty.party_type = '''||p_party_type||''''||
2757: ' and pty.party_id = contpt.owner_table_id'||
2758: ' and contpt.owner_table_name = ''HZ_PARTIES'''||
2759: ' and contpt.status in (''A'',''I'')'||
2760: ' and contpt.contact_point_type = '''||l_contact_point_type||''''||
2761: ' and contpt.primary_flag = ''Y''';
2762: END IF;

Line 2784: str := ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||

2780:
2781: BEGIN
2782:
2783: -- org_contact is only used for contact report
2784: str := ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2785: ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2786: ' where pty.status in (''A'',''I'')'||
2787: ' and orgcnt.party_relationship_id = r.relationship_id' ||
2788: ' and r.subject_type = ''PERSON'''||

Line 2819: ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||

2815:
2816: BEGIN
2817:
2818: str := ' from (select r.party_id '||p_attribute||
2819: ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2820: ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2821: ' where pty.status in (''A'',''I'')'||
2822: ' and orgcnt.party_relationship_id = r.relationship_id' ||
2823: ' and r.subject_type = ''PERSON'''||