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.IEX_UTILITIES dependencies on HZ_CUSTOMER_PROFILES

Line 622: FROM hz_customer_profiles hp,ar_collectors ac

618: x_resource_tab OUT NOCOPY resource_tab_type) IS
619: --Territory Assignment Changes
620: CURSOR c_get_person IS
621: SELECT DISTINCT ac.employee_id
622: FROM hz_customer_profiles hp,ar_collectors ac
623: WHERE hp.collector_id = ac.collector_id
624: AND ac.employee_id is not null
625: AND hp.party_id = p_party_id;
626:

Line 648: FROM hz_customer_profiles hp,ar_collectors ac

644: IF PG_DEBUG < 10 THEN
645: iex_debug_pub.logmessage ('**** BEGIN get_access_resources ************');
646: iex_debug_pub.logmessage ('get_person cursor = ' ||
647: 'SELECT DISTINCT hp.employee_id
648: FROM hz_customer_profiles hp,ar_collectors ac
649: WHERE m.person_id = ac.employee_id
650: AND hp.collector_id = ac.collector_id
651: and ac.employee_id is not null
652: AND hp.party_id = p_party_id ' ||

Line 755: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,

751: --Territory Assignment Changes
752: CURSOR c_get_person IS
753: (
754: SELECT ac.employee_id, ac.resource_id, count(work_item_id)
755: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,
756: ar_collectors ac
757: WHERE hp.party_id = p_party_id
758: and hp.cust_account_id = -1
759: and rs.resource_id = ac.resource_id

Line 768: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac

764: and ac.employee_id is not null
765: group by ac.resource_id, ac.employee_id
766: union all
767: SELECT ac.employee_id, ac.resource_id, 0
768: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
769: WHERE hp.party_id = p_party_id
770: and hp.cust_account_id = -1
771: and rs.resource_id = ac.resource_id
772: and hp.collector_id = ac.collector_id

Line 782: /* We use hz_customer_profiles and

778: group by ac.resource_id, ac.employee_id
779: ) order by 3;
780: */
781:
782: /* We use hz_customer_profiles and
783: Load balancing when the assigned with Group Resource
784: */
785: /*cursor c_get_person IS
786: SELECT ac.employee_id, ac.resource_id, 0

Line 787: FROM hz_customer_profiles hp, ar_collectors ac

783: Load balancing when the assigned with Group Resource
784: */
785: /*cursor c_get_person IS
786: SELECT ac.employee_id, ac.resource_id, 0
787: FROM hz_customer_profiles hp, ar_collectors ac
788: WHERE hp.party_id = p_party_id
789: and hp.cust_account_id = -1
790: and hp.collector_id = ac.collector_id
791: and ac.resource_type = 'RS_RESOURCE'

Line 798: FROM hz_customer_profiles hp, iex_strategy_work_items wi,

794: and nvl(ac.status,'A') = 'A'
795: -- Bug4483896. Fixed by lkkumar. Check for inactive_date. End.
796: union all
797: ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
798: FROM hz_customer_profiles hp, iex_strategy_work_items wi,
799: ar_collectors ac, jtf_rs_group_members jtg
800: WHERE hp.party_id = P_PARTY_ID
801: and hp.cust_account_id = -1
802: and hp.collector_id = ac.collector_id

Line 813: FROM hz_customer_profiles hp, ar_collectors ac,

809: and nvl(ac.status,'A') = 'A'
810: group by jtg.resource_id, jtg.person_id
811: UNION ALL
812: SELECT jtg.person_id, jtg.resource_id, 0
813: FROM hz_customer_profiles hp, ar_collectors ac,
814: jtf_rs_group_members jtg
815: WHERE hp.party_id = p_party_id
816: and hp.cust_account_id = -1
817: and hp.collector_id = ac.collector_id

Line 851: ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||

847: get_access_resources ************');
848: l_init_msg_list := p_init_msg_list;
849: iex_debug_pub.logmessage ('get_person cursor = ' ||
850: 'SELECT ac.employee_id, ac.resource_id, count(work_item_id) ' ||
851: ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||
852: ' ar_collectors ac ' ||
853: ' WHERE hp.party_id = p_party_id ' ||
854: ' and rs.resource_id = ac.resource_id' ||
855: ' and hp.collector_id = ac.collector_id' ||

Line 939: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,

935: --Territory Assignment Changes
936: /* CURSOR c_get_person IS
937: (
938: SELECT ac.employee_id, ac.resource_id, count(work_item_id)
939: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,
940: ar_collectors ac
941: WHERE hp.cust_account_id = p_account_id
942: and hp.site_use_id is null
943: and rs.resource_id = ac.resource_id

Line 952: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac

948: and ac.employee_id is not null
949: group by ac.resource_id, ac.employee_id
950: union all
951: SELECT ac.employee_id, ac.resource_id, 0
952: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
953: WHERE hp.cust_account_id = p_account_id
954: and hp.site_use_id is null
955: and rs.resource_id = ac.resource_id
956: and hp.collector_id = ac.collector_id

Line 966: /* We use hz_customer_profiles and

962: group by ac.resource_id, ac.employee_id
963: ) order by 3;
964: */
965:
966: /* We use hz_customer_profiles and
967: Load balancing when the assigned with Group Resource
968: */
969: /*CURSOR c_get_person IS
970: SELECT ac.employee_id, ac.resource_id, 0

Line 971: FROM hz_customer_profiles hp, ar_collectors ac

967: Load balancing when the assigned with Group Resource
968: */
969: /*CURSOR c_get_person IS
970: SELECT ac.employee_id, ac.resource_id, 0
971: FROM hz_customer_profiles hp, ar_collectors ac
972: WHERE hp.cust_account_id = p_account_id
973: and hp.site_use_id is null
974: and hp.collector_id = ac.collector_id
975: and ac.resource_type = 'RS_RESOURCE'

Line 978: FROM hz_customer_profiles hp, iex_strategy_work_items wi,

974: and hp.collector_id = ac.collector_id
975: and ac.resource_type = 'RS_RESOURCE'
976: union all
977: ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
978: FROM hz_customer_profiles hp, iex_strategy_work_items wi,
979: ar_collectors ac, jtf_rs_group_members jtg
980: WHERE hp.cust_account_id = p_account_id
981: and hp.site_use_id is NULL
982: and hp.collector_id = ac.collector_id

Line 990: FROM hz_customer_profiles hp, ar_collectors ac,

986: and wi.status_code = 'OPEN'
987: group by jtg.resource_id, jtg.person_id
988: UNION ALL
989: SELECT jtg.person_id, jtg.resource_id, 0
990: FROM hz_customer_profiles hp, ar_collectors ac,
991: jtf_rs_group_members jtg
992: WHERE hp.cust_account_id = p_account_id
993: and hp.site_use_id is null
994: and hp.collector_id = ac.collector_id

Line 1024: ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||

1020: get_access_resources ************');
1021: l_init_msg_list := p_init_msg_list;
1022: iex_debug_pub.logmessage ('get_person cursor = ' ||
1023: 'SELECT ac.employee_id, ac.resource_id, count(work_item_id) ' ||
1024: ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||
1025: ' ar_collectors ac ' ||
1026: ' WHERE hp.party_id = p_party_id ' ||
1027: ' and rs.resource_id = ac.resource_id' ||
1028: ' and hp.collector_id = ac.collector_id' ||

Line 1111: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac

1107: x_resource_tab OUT NOCOPY resource_tab_type) IS
1108: --Territory Assignment Changes
1109: CURSOR c_get_person IS
1110: SELECT ac.employee_id, ac.resource_id, count(cas_id)
1111: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
1112: WHERE hp.party_id = p_party_id
1113: and rs.resource_id = ac.resource_id
1114: and hp.collector_id = ac.collector_id
1115: and ac.resource_id = wi.owner_resource_id(+)

Line 1139: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac

1135:
1136: iex_debug_pub.logmessage ('**** BEGIN get_case_resources ************');
1137: iex_debug_pub.logmessage ('get_person cursor = ' ||
1138: 'SELECT ac.employee_id, ac.resource_id, count(cas_id)
1139: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
1140: WHERE hp.party_id = p_party_id
1141: and rs.resource_id = ac.resource_id
1142: and hp.collector_id = ac.collector_id
1143: and ac.resource_id = wi.owner_resource_id(+)

Line 1279: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,

1275: --Territory Assignment Changes
1276: /* CURSOR c_get_person IS
1277: (
1278: SELECT ac.employee_id, ac.resource_id
1279: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,
1280: ar_collectors ac
1281: WHERE hp.site_use_id = p_site_use_id
1282: and rs.resource_id = ac.resource_id
1283: and hp.collector_id = ac.collector_id

Line 1289: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac

1285: and ac.employee_id is not null
1286: group by ac.resource_id, ac.employee_id
1287: union all
1288: SELECT ac.employee_id, ac.resource_id, 0
1289: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
1290: WHERE hp.site_use_id = p_site_use_id
1291: and rs.resource_id = ac.resource_id
1292: and hp.collector_id = ac.collector_id
1293: and rs.user_id is not null and

Line 1301: /* We use hz_customer_profiles and

1297: and ac.employee_id is not null
1298: group by ac.resource_id, ac.employee_id
1299: ) order by 3;
1300: */
1301: /* We use hz_customer_profiles and
1302: Load balancing when the assigned with Group Resource
1303: */
1304: /*CURSOR c_get_person is
1305: SELECT ac.employee_id, ac.resource_id, 0

Line 1306: FROM hz_customer_profiles hp, ar_collectors ac

1302: Load balancing when the assigned with Group Resource
1303: */
1304: /*CURSOR c_get_person is
1305: SELECT ac.employee_id, ac.resource_id, 0
1306: FROM hz_customer_profiles hp, ar_collectors ac
1307: WHERE hp.site_use_id = p_site_use_id
1308: and hp.collector_id = ac.collector_id
1309: and ac.resource_type = 'RS_RESOURCE'
1310: union all

Line 1312: FROM hz_customer_profiles hp, iex_strategy_work_items wi,

1308: and hp.collector_id = ac.collector_id
1309: and ac.resource_type = 'RS_RESOURCE'
1310: union all
1311: ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
1312: FROM hz_customer_profiles hp, iex_strategy_work_items wi,
1313: ar_collectors ac, jtf_rs_group_members jtg
1314: WHERE hp.site_use_id = p_site_use_id
1315: and hp.collector_id = ac.collector_id
1316: and ac.resource_type = 'RS_GROUP'

Line 1323: FROM hz_customer_profiles hp, ar_collectors ac,

1319: and wi.status_code = 'OPEN'
1320: group by jtg.resource_id, jtg.person_id
1321: UNION ALL
1322: SELECT jtg.person_id, jtg.resource_id, 0
1323: FROM hz_customer_profiles hp, ar_collectors ac,
1324: jtf_rs_group_members jtg
1325: WHERE hp.site_use_id = p_site_use_id
1326: and hp.collector_id = ac.collector_id
1327: and ac.resource_type = 'RS_GROUP'

Line 1349: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,ar_collectors ac

1345: iex_debug_pub.logmessage ('**** BEGIN on all
1346: get_billto_resources ************');
1347: iex_debug_pub.logmessage ('get_person cursor = ' ||
1348: ' SELECT ac.employee_id, ac.resource_id, count(work_item_id)
1349: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,ar_collectors ac
1350: WHERE hp.site_use_id = p_site_use_id
1351: and rs.resource_id = ac.resource_id
1352: and ac.resource_id = wi.resource_id
1353: and hp.collector_id = ac.collector_id

Line 1537: select dunning_letters from hz_customer_profiles

1533: AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
1534: AND ca.cust_account_id = acct_sites.cust_account_id;
1535:
1536: cursor c_billto (p_site_use_id number) is
1537: select dunning_letters from hz_customer_profiles
1538: where site_use_id = p_site_use_id and status = 'A';
1539:
1540: CURSOR get_party_id_cur (p_cust_account_id number) is
1541: SELECT party_id FROM HZ_CUST_ACCOUNTS

Line 1545: select dunning_letters from hz_customer_profiles

1541: SELECT party_id FROM HZ_CUST_ACCOUNTS
1542: WHERE cust_account_id = p_cust_account_id ;
1543:
1544: cursor c_account (p_cust_account_id number) is
1545: select dunning_letters from hz_customer_profiles
1546: where cust_account_id = p_cust_account_id and status = 'A' and site_use_id is null;
1547:
1548: cursor c_party (p_party_id number) is
1549: select dunning_letters from hz_customer_profiles

Line 1549: select dunning_letters from hz_customer_profiles

1545: select dunning_letters from hz_customer_profiles
1546: where cust_account_id = p_cust_account_id and status = 'A' and site_use_id is null;
1547:
1548: cursor c_party (p_party_id number) is
1549: select dunning_letters from hz_customer_profiles
1550: -- begin bug 4587842 ctlee 09/06/2005
1551: where party_id = p_party_id and status = 'A'
1552: and site_use_id is null and cust_account_id = -1;
1553: -- where party_id = p_party_id and status = 'A' and site_use_id is null and cust_account_id is null;

Line 4733: FROM hz_customer_profiles hp, ar_collectors ac

4729:
4730: /* ------ SQLs assembled at run time -----------------------------------
4731: CURSOR c_billto_collector is
4732: SELECT ac.employee_id, ac.resource_id, 0
4733: FROM hz_customer_profiles hp, ar_collectors ac
4734: WHERE hp.site_use_id = p_site_use_id
4735: and hp.collector_id = ac.collector_id
4736: and ac.resource_type = 'RS_RESOURCE'
4737: and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)

Line 4742: FROM hz_customer_profiles hp, iex_strategy_work_items wi,

4738: and nvl(ac.status,'A') = 'A'
4739: and nvl(hp.status,'A') = 'A'
4740: union all
4741: ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
4742: FROM hz_customer_profiles hp, iex_strategy_work_items wi,
4743: ar_collectors ac, jtf_rs_group_members jtg
4744: WHERE hp.site_use_id = p_site_use_id
4745: and hp.collector_id = ac.collector_id
4746: and ac.resource_type = 'RS_GROUP'

Line 4757: FROM hz_customer_profiles hp, ar_collectors ac,

4753: and nvl(jtg.delete_flag,'N') = 'N'
4754: group by jtg.resource_id, jtg.person_id
4755: UNION ALL
4756: SELECT jtg.person_id, jtg.resource_id, 0
4757: FROM hz_customer_profiles hp, ar_collectors ac,
4758: jtf_rs_group_members jtg
4759: WHERE hp.site_use_id = p_site_use_id
4760: and hp.collector_id = ac.collector_id
4761: and ac.resource_type = 'RS_GROUP'

Line 4775: FROM hz_customer_profiles hp, ar_collectors ac

4771: ) order by 3;
4772:
4773: CURSOR c_account_collector IS
4774: SELECT ac.employee_id, ac.resource_id, 0
4775: FROM hz_customer_profiles hp, ar_collectors ac
4776: WHERE hp.cust_account_id = p_account_id
4777: and hp.site_use_id is null
4778: and hp.collector_id = ac.collector_id
4779: and ac.resource_type = 'RS_RESOURCE'

Line 4785: FROM hz_customer_profiles hp, iex_strategy_work_items wi,

4781: and nvl(ac.status,'A') = 'A'
4782: and nvl(hp.status,'A') = 'A'
4783: union all
4784: ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
4785: FROM hz_customer_profiles hp, iex_strategy_work_items wi,
4786: ar_collectors ac, jtf_rs_group_members jtg
4787: WHERE hp.cust_account_id = p_account_id
4788: and hp.site_use_id is NULL
4789: and hp.collector_id = ac.collector_id

Line 4801: FROM hz_customer_profiles hp, ar_collectors ac,

4797: and nvl(jtg.delete_flag,'N') = 'N'
4798: group by jtg.resource_id, jtg.person_id
4799: UNION ALL
4800: SELECT jtg.person_id, jtg.resource_id, 0
4801: FROM hz_customer_profiles hp, ar_collectors ac,
4802: jtf_rs_group_members jtg
4803: WHERE hp.cust_account_id = p_account_id
4804: and hp.site_use_id is null
4805: and hp.collector_id = ac.collector_id

Line 4820: FROM hz_customer_profiles hp, ar_collectors ac

4816: ) order by 3;
4817:
4818: cursor c_party_collector IS
4819: SELECT ac.employee_id, ac.resource_id, 0
4820: FROM hz_customer_profiles hp, ar_collectors ac
4821: WHERE hp.party_id = p_party_id
4822: and hp.cust_account_id = -1
4823: and hp.collector_id = ac.collector_id
4824: and ac.resource_type = 'RS_RESOURCE'

Line 4830: FROM hz_customer_profiles hp, iex_strategy_work_items wi,

4826: and nvl(ac.status,'A') = 'A'
4827: and nvl(hp.status,'A') = 'A'
4828: union all
4829: ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
4830: FROM hz_customer_profiles hp, iex_strategy_work_items wi,
4831: ar_collectors ac, jtf_rs_group_members jtg
4832: WHERE hp.party_id = P_PARTY_ID
4833: and hp.cust_account_id = -1
4834: and hp.collector_id = ac.collector_id

Line 4846: FROM hz_customer_profiles hp, ar_collectors ac,

4842: and nvl(jtg.delete_flag,'N') = 'N'
4843: group by jtg.resource_id, jtg.person_id
4844: UNION ALL
4845: SELECT jtg.person_id, jtg.resource_id, 0
4846: FROM hz_customer_profiles hp, ar_collectors ac,
4847: jtf_rs_group_members jtg
4848: WHERE hp.party_id = p_party_id
4849: and hp.cust_account_id = -1
4850: and hp.collector_id = ac.collector_id

Line 4865: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac

4861: ) order by 3;
4862:
4863: CURSOR c_case_collector IS
4864: SELECT ac.employee_id, ac.resource_id, count(cas_id)
4865: FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
4866: WHERE hp.party_id = p_party_id
4867: and rs.resource_id = ac.resource_id
4868: and hp.collector_id = ac.collector_id
4869: and ac.resource_id = wi.owner_resource_id(+)

Line 4947: l_select1 := l_select1 || 'FROM hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs, ';

4943: --Start Bug 7134688 gnramasa 17th June 08
4944:
4945: -- Initialize SQL statements - REMEMBER CHANGING 1 MEANS CHANGING ALL
4946: l_select1 := 'SELECT ac.employee_id, ac.resource_id, 0 ';
4947: l_select1 := l_select1 || 'FROM hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs, ';
4948: l_select1 := l_select1 || ' jtf_rs_role_relations jtr, jtf_rs_roles_b jtrr '; -- Added by bibeura for bug 12562473
4949: l_where1 := 'WHERE ';
4950: l_where1 := l_where1 || ' hp.collector_id = ac.collector_id ';
4951: l_where1 := l_where1 || ' and ac.resource_type = ''RS_RESOURCE'' ';

Line 4965: l_select2 := l_select2 || 'FROM hz_customer_profiles hp, iex_strategy_work_items wi, ';

4961: l_where1 := l_where1 || ' and NVL(jtr.delete_flag,''N'') = ''N'' '; -- Added by bibeura for bug 12562473
4962: l_where1 := l_where1 || ' and TRUNC(NVL(jtr.end_date_active,sysdate)) >= TRUNC(sysdate) '; -- Added by bibeura for bug 12562473
4963:
4964: l_select2 := 'SELECT jtg.person_id, jtg.resource_id, count(work_item_id) ';
4965: l_select2 := l_select2 || 'FROM hz_customer_profiles hp, iex_strategy_work_items wi, ';
4966: l_select2 := l_select2 || ' ar_collectors ac, jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
4967: l_select2 := l_select2 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
4968: l_where2 := 'WHERE ';
4969: l_where2 := l_where2 || ' hp.collector_id = ac.collector_id ';

Line 4992: l_select3 := l_select3 || 'FROM hz_customer_profiles hp, ar_collectors ac, ';

4988: l_where2 := l_where2 || ' and trunc(nvl(jtr.end_date_active,sysdate)) >= trunc(sysdate) ';
4989: l_group2 := 'group by jtg.resource_id, jtg.person_id ';
4990:
4991: l_select3 := 'SELECT jtg.person_id, jtg.resource_id, 0 ';
4992: l_select3 := l_select3 || 'FROM hz_customer_profiles hp, ar_collectors ac, ';
4993: l_select3 := l_select3 || ' jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
4994: l_select3 := l_select3 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
4995: l_where3 := ' WHERE ';
4996: l_where3 := l_where3 || ' hp.collector_id = ac.collector_id ';

Line 5021: -- l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac ';

5017: l_group3 := ' group by jtg.resource_id, jtg.person_id ';
5018:
5019: l_select4 := 'SELECT ac.employee_id, ac.resource_id, count(cas_id) ';
5020: --Begin Bug#6962575 29-Jul-2008 barathsr
5021: -- l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac ';
5022: l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_all_b wi,ar_collectors ac ';
5023: --End Bug#6962575 29-Jul-2008 barathsr
5024: l_where4 := ' WHERE hp.party_id = :1 ';
5025: l_where4 := l_where4 || ' and rs.resource_id = ac.resource_id ';

Line 5022: l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_all_b wi,ar_collectors ac ';

5018:
5019: l_select4 := 'SELECT ac.employee_id, ac.resource_id, count(cas_id) ';
5020: --Begin Bug#6962575 29-Jul-2008 barathsr
5021: -- l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac ';
5022: l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_all_b wi,ar_collectors ac ';
5023: --End Bug#6962575 29-Jul-2008 barathsr
5024: l_where4 := ' WHERE hp.party_id = :1 ';
5025: l_where4 := l_where4 || ' and rs.resource_id = ac.resource_id ';
5026: l_where4 := l_where4 || ' and hp.collector_id = ac.collector_id ';

Line 5039: l_select5 := l_select5 || ' FROM hz_customer_profiles hp, jtf_tasks_vl t, jtf_task_statuses_vl s, ';

5035:
5036: --Begin Bug#5229763 schekuri 27-Jul-2006
5037: --Added following sql's for getting resource for task creation in dunning callback concurrent program
5038: l_select5 := ' SELECT jtg.person_id, jtg.resource_id, count(t.task_id) ';
5039: l_select5 := l_select5 || ' FROM hz_customer_profiles hp, jtf_tasks_vl t, jtf_task_statuses_vl s, ';
5040: l_select5 := l_select5 || ' ar_collectors ac, jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
5041: l_select5 := l_select5 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
5042: l_where5 := ' WHERE ';
5043: l_where5 := l_where5 || ' hp.collector_id = ac.collector_id ';

Line 5068: l_select6 := l_select6 || ' FROM hz_customer_profiles hp, ar_collectors ac, ';

5064: l_where5 := l_where5 || ' and trunc(nvl(jtr.end_date_active,sysdate)) >= trunc(sysdate) ';
5065: l_group5 := 'group by jtg.resource_id, jtg.person_id ';
5066:
5067: l_select6 := 'SELECT jtg.person_id, jtg.resource_id, 0 ';
5068: l_select6 := l_select6 || ' FROM hz_customer_profiles hp, ar_collectors ac, ';
5069: l_select6 := l_select6 || ' jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
5070: l_select6 := l_select6 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
5071: l_where6 := ' WHERE ';
5072: l_where6 := l_where6 || ' hp.collector_id = ac.collector_id ';

Line 5266: l_select1 := l_select1 || 'FROM hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs ';

5262: x_return_status := FND_API.G_RET_STS_SUCCESS;
5263:
5264: -- Initialize SQL statements - REMEMBER CHANGING 1 MEANS CHANGING ALL
5265: l_select1 := 'SELECT ac.employee_id, ac.resource_id ';
5266: l_select1 := l_select1 || 'FROM hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs ';
5267: l_where1 := 'WHERE ';
5268: l_where1 := l_where1 || ' hp.collector_id = ac.collector_id ';
5269: l_where1 := l_where1 || ' and ac.resource_type = ''RS_RESOURCE'' ';
5270: l_where1 := l_where1 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';

Line 5277: l_select2 := l_select2 || 'FROM hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs ';

5273: l_where1 := l_where1 || ' and ac.resource_id = rs.resource_id ';
5274: l_where1 := l_where1 || ' and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate) ';
5275:
5276: l_select2 := 'SELECT ac.employee_id, rs.resource_id ';
5277: l_select2 := l_select2 || 'FROM hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs ';
5278: l_where2 := 'WHERE ';
5279: l_where2 := l_where2 || ' hp.collector_id = ac.collector_id ';
5280: l_where2 := l_where2 || ' and ac.resource_type = ''RS_GROUP'' ';
5281: l_where2 := l_where2 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';

Line 5381: from hz_customer_profiles

5377: l_msg_data VARCHAR2(32767);
5378:
5379: cursor c_gracedays_party_level (p_party_id number) is
5380: select nvl(payment_grace_days ,0)
5381: from hz_customer_profiles
5382: where party_id = p_party_id
5383: and cust_account_id = -1;
5384:
5385: cursor c_gracedays_acc_level (p_party_id number, p_cust_acct_id number) is

Line 5387: from hz_customer_profiles

5383: and cust_account_id = -1;
5384:
5385: cursor c_gracedays_acc_level (p_party_id number, p_cust_acct_id number) is
5386: select nvl(payment_grace_days ,0)
5387: from hz_customer_profiles
5388: where party_id = p_party_id
5389: and cust_account_id = p_cust_acct_id
5390: and site_use_id is null;
5391:

Line 5394: from hz_customer_profiles

5390: and site_use_id is null;
5391:
5392: cursor c_gracedays_billto_level (p_party_id number, p_cust_acct_id number, p_site_use_id number) is
5393: select nvl(payment_grace_days ,0)
5394: from hz_customer_profiles
5395: where party_id = p_party_id
5396: and cust_account_id = p_cust_acct_id
5397: and site_use_id = p_site_use_id;
5398: