1290: hr.organization_id = l_org_id AND
1291: hr.org_information_context = hr_type AND
1292: hr.org_information1 = loc.country AND
1293: ( NOT EXISTS (
1294: SELECT 1 FROM hz_cust_acct_sites_all cas1
1295: WHERE cas1.party_site_id = ps.party_site_id
1296: AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
1297: ( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
1298: WHERE party_site_id = ps.party_site_id
1293: ( NOT EXISTS (
1294: SELECT 1 FROM hz_cust_acct_sites_all cas1
1295: WHERE cas1.party_site_id = ps.party_site_id
1296: AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
1297: ( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
1298: WHERE party_site_id = ps.party_site_id
1299: AND org_id = MO_GLOBAL.get_current_org_id()
1300: AND status = 'A') AND
1301: (
1299: AND org_id = MO_GLOBAL.get_current_org_id()
1300: AND status = 'A') AND
1301: (
1302: NOT EXISTS (
1303: SELECT 1 FROM hz_cust_acct_sites_all cas2, hz_cust_site_uses_all csu2
1304: WHERE csu2.cust_acct_site_id = cas2.cust_acct_site_id
1305: AND cas2.party_site_id = ps.party_site_id
1306: AND cas2.org_id = MO_GLOBAL.get_current_org_id()
1307: AND csu2.site_use_code = site_type) OR
1306: AND cas2.org_id = MO_GLOBAL.get_current_org_id()
1307: AND csu2.site_use_code = site_type) OR
1308: EXISTS (
1309: SELECT 1
1310: FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
1311: WHERE cas.party_site_id = ps.party_site_id AND
1312: cas.org_id = MO_GLOBAL.get_current_org_id() AND
1313: csu.cust_acct_site_id (+) = cas.cust_acct_site_id AND
1314: NVL(csu.status,'A') = 'A' AND
1335: hr.organization_id = l_org_id AND
1336: hr.org_information_context = hr_type AND
1337: rownum = 1) AND
1338: ( NOT EXISTS (
1339: SELECT 1 FROM hz_cust_acct_sites_all cas1
1340: WHERE cas1.party_site_id = ps.party_site_id
1341: AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
1342: ( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
1343: WHERE party_site_id = ps.party_site_id
1338: ( NOT EXISTS (
1339: SELECT 1 FROM hz_cust_acct_sites_all cas1
1340: WHERE cas1.party_site_id = ps.party_site_id
1341: AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
1342: ( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
1343: WHERE party_site_id = ps.party_site_id
1344: AND org_id = MO_GLOBAL.get_current_org_id()
1345: AND status = 'A') AND
1346: (
1344: AND org_id = MO_GLOBAL.get_current_org_id()
1345: AND status = 'A') AND
1346: (
1347: NOT EXISTS (
1348: SELECT 1 FROM hz_cust_acct_sites_all cas2, hz_cust_site_uses_all csu2
1349: WHERE csu2.cust_acct_site_id = cas2.cust_acct_site_id
1350: AND cas2.party_site_id = ps.party_site_id
1351: AND cas2.org_id = MO_GLOBAL.get_current_org_id()
1352: AND csu2.site_use_code = site_type) OR
1351: AND cas2.org_id = MO_GLOBAL.get_current_org_id()
1352: AND csu2.site_use_code = site_type) OR
1353: EXISTS (
1354: SELECT 1
1355: FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
1356: WHERE cas.party_site_id = ps.party_site_id AND
1357: cas.org_id = MO_GLOBAL.get_current_org_id() AND
1358: csu.cust_acct_site_id (+) = cas.cust_acct_site_id AND
1359: NVL(csu.status,'A') = 'A' AND