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.HZ_PURGE_GEN dependencies on HZ_PARTY_SITES

Line 93: or exists (select 'Y' from HZ_PARTY_SITES

89:
90: or exists (select 'Y' from PV_REFERRALS_B xx where xx.CUSTOMER_PARTY_ID = temp.party_id
91: )
92:
93: or exists (select 'Y' from HZ_PARTY_SITES
94: xx where xx.PARTY_ID = temp.party_id
95: and (nvl(STATUS, 'A') in ('A','I'))
96: and exists
97: (select 'Y' from PV_REFERRALS_B

Line 101: or exists (select 'Y' from HZ_PARTY_SITES

97: (select 'Y' from PV_REFERRALS_B
98: yy where yy.CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
99: ))
100:
101: or exists (select 'Y' from HZ_PARTY_SITES
102: xx where xx.PARTY_ID = temp.party_id
103: and (nvl(STATUS, 'A') in ('A','I'))
104: and exists
105: (select 'Y' from HZ_ORG_CONTACTS

Line 192: or exists (select 'Y' from HZ_PARTY_SITES

188:
189: or exists (select 'Y' from PV_REFERRALS_B xx where xx.CUSTOMER_PARTY_ID = temp.party_id
190: )
191:
192: or exists (select 'Y' from HZ_PARTY_SITES
193: xx where xx.PARTY_ID = temp.party_id
194: and (nvl(STATUS, 'A') in ('A','I'))
195: and exists
196: (select 'Y' from PV_REFERRALS_B

Line 200: or exists (select 'Y' from HZ_PARTY_SITES

196: (select 'Y' from PV_REFERRALS_B
197: yy where yy.CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
198: ))
199:
200: or exists (select 'Y' from HZ_PARTY_SITES
201: xx where xx.PARTY_ID = temp.party_id
202: and (nvl(STATUS, 'A') in ('A','I'))
203: and exists
204: (select 'Y' from HZ_ORG_CONTACTS

Line 260: or exists (select 'Y' from HZ_PARTY_SITES

256:
257: exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
258: )
259:
260: or exists (select 'Y' from HZ_PARTY_SITES
261: xx where xx.PARTY_ID = temp.party_id
262: and (nvl(STATUS, 'A') in ('A','I'))
263: and exists
264: (select 'Y' from WSH_CARRIER_SITES

Line 274: or exists (select 'Y' from HZ_PARTY_SITES

270:
271: exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
272: )
273:
274: or exists (select 'Y' from HZ_PARTY_SITES
275: xx where xx.PARTY_ID = temp.party_id
276: and (nvl(STATUS, 'A') in ('A','I'))
277: and exists
278: (select 'Y' from WSH_CARRIER_SITES

Line 296: or exists (select 'Y' from HZ_PARTY_SITES

292:
293: or exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.PARTY_ID = temp.party_id
294: and (nvl(STATUS, 'A') in ('A','I')))
295:
296: or exists (select 'Y' from HZ_PARTY_SITES
297: xx where xx.PARTY_ID = temp.party_id
298: and (nvl(STATUS, 'A') in ('A','I'))
299: and exists
300: (select 'Y' from HZ_CUST_ACCT_SITES_ALL

Line 327: or exists (select 'Y' from HZ_PARTY_SITES

323:
324: or exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.PARTY_ID = temp.party_id
325: and (nvl(STATUS, 'A') in ('A','I')))
326:
327: or exists (select 'Y' from HZ_PARTY_SITES
328: xx where xx.PARTY_ID = temp.party_id
329: and (nvl(STATUS, 'A') in ('A','I'))
330: and exists
331: (select 'Y' from HZ_CUST_ACCT_SITES_ALL

Line 363: or exists (select 'Y' from HZ_PARTY_SITES

359:
360: or exists (select 'Y' from IBE_ORD_ONECLICK_ALL xx where xx.PARTY_ID = temp.party_id
361: )
362:
363: or exists (select 'Y' from HZ_PARTY_SITES
364: xx where xx.PARTY_ID = temp.party_id
365: and (nvl(STATUS, 'A') in ('A','I'))
366: and exists
367: (select 'Y' from IBE_ORD_ONECLICK_ALL

Line 371: or exists (select 'Y' from HZ_PARTY_SITES

367: (select 'Y' from IBE_ORD_ONECLICK_ALL
368: yy where yy.BILL_TO_PTY_SITE_ID=xx.PARTY_SITE_ID
369: ))
370:
371: or exists (select 'Y' from HZ_PARTY_SITES
372: xx where xx.PARTY_ID = temp.party_id
373: and (nvl(STATUS, 'A') in ('A','I'))
374: and exists
375: (select 'Y' from IBE_ORD_ONECLICK_ALL

Line 391: or exists (select 'Y' from HZ_PARTY_SITES

387:
388: or exists (select 'Y' from IBE_ORD_ONECLICK_ALL xx where xx.PARTY_ID = temp.party_id
389: )
390:
391: or exists (select 'Y' from HZ_PARTY_SITES
392: xx where xx.PARTY_ID = temp.party_id
393: and (nvl(STATUS, 'A') in ('A','I'))
394: and exists
395: (select 'Y' from IBE_ORD_ONECLICK_ALL

Line 399: or exists (select 'Y' from HZ_PARTY_SITES

395: (select 'Y' from IBE_ORD_ONECLICK_ALL
396: yy where yy.BILL_TO_PTY_SITE_ID=xx.PARTY_SITE_ID
397: ))
398:
399: or exists (select 'Y' from HZ_PARTY_SITES
400: xx where xx.PARTY_ID = temp.party_id
401: and (nvl(STATUS, 'A') in ('A','I'))
402: and exists
403: (select 'Y' from IBE_ORD_ONECLICK_ALL

Line 492: or exists (select 'Y' from HZ_PARTY_SITES

488:
489: exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
490: )
491:
492: or exists (select 'Y' from HZ_PARTY_SITES
493: xx where xx.PARTY_ID = temp.party_id
494: and (nvl(STATUS, 'A') in ('A','I'))
495: and exists
496: (select 'Y' from IEX_CASE_CONTACTS

Line 506: or exists (select 'Y' from HZ_PARTY_SITES

502:
503: exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
504: )
505:
506: or exists (select 'Y' from HZ_PARTY_SITES
507: xx where xx.PARTY_ID = temp.party_id
508: and (nvl(STATUS, 'A') in ('A','I'))
509: and exists
510: (select 'Y' from IEX_CASE_CONTACTS

Line 690: or exists (select 'Y' from HZ_PARTY_SITES

686:
687: exists (select 'Y' from QP_QUALIFIERS xx where xx.QUALIFIER_ATTR_VALUE = to_char(temp.party_id)
688: and ((qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' OR qualifier_context = 'CUSTOMER' AND qualifier_attribute ='QUALIFIER_ATTRIBUTE16' OR qualifier_context = 'CUSTOMER_GROUP' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' OR qualifier_context = 'PARTY' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))))
689:
690: or exists (select 'Y' from HZ_PARTY_SITES
691: xx where xx.PARTY_ID = temp.party_id
692: and (nvl(STATUS, 'A') in ('A','I'))
693: and exists
694: (select 'Y' from QP_QUALIFIERS

Line 704: or exists (select 'Y' from HZ_PARTY_SITES

700:
701: exists (select 'Y' from QP_QUALIFIERS xx where xx.QUALIFIER_ATTR_VALUE = to_char(temp.party_id)
702: and ((qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' OR qualifier_context = 'CUSTOMER' AND qualifier_attribute ='QUALIFIER_ATTRIBUTE16' OR qualifier_context = 'CUSTOMER_GROUP' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' OR qualifier_context = 'PARTY' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2'))))
703:
704: or exists (select 'Y' from HZ_PARTY_SITES
705: xx where xx.PARTY_ID = temp.party_id
706: and (nvl(STATUS, 'A') in ('A','I'))
707: and exists
708: (select 'Y' from QP_QUALIFIERS

Line 740: or exists (select 'Y' from HZ_PARTY_SITES

736:
737: exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
738: )
739:
740: or exists (select 'Y' from HZ_PARTY_SITES
741: xx where xx.PARTY_ID = temp.party_id
742: and (nvl(STATUS, 'A') in ('A','I'))
743: and exists
744: (select 'Y' from CSI_T_TXN_LINE_DETAILS

Line 748: or exists (select 'Y' from HZ_PARTY_SITES

744: (select 'Y' from CSI_T_TXN_LINE_DETAILS
745: yy where yy.LOCATION_ID=xx.PARTY_SITE_ID
746: ))
747:
748: or exists (select 'Y' from HZ_PARTY_SITES
749: xx where xx.PARTY_ID = temp.party_id
750: and (nvl(STATUS, 'A') in ('A','I'))
751: and exists
752: (select 'Y' from CSI_T_TXN_LINE_DETAILS

Line 786: or exists (select 'Y' from HZ_PARTY_SITES

782:
783: or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.TECHNICAL_CONTACT_ID = temp.party_id
784: )
785:
786: or exists (select 'Y' from HZ_PARTY_SITES
787: xx where xx.PARTY_ID = temp.party_id
788: and (nvl(STATUS, 'A') in ('A','I'))
789: and exists
790: (select 'Y' from CSI_SYSTEMS_B

Line 800: or exists (select 'Y' from HZ_PARTY_SITES

796:
797: exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
798: )
799:
800: or exists (select 'Y' from HZ_PARTY_SITES
801: xx where xx.PARTY_ID = temp.party_id
802: and (nvl(STATUS, 'A') in ('A','I'))
803: and exists
804: (select 'Y' from CSI_T_TXN_LINE_DETAILS

Line 808: or exists (select 'Y' from HZ_PARTY_SITES

804: (select 'Y' from CSI_T_TXN_LINE_DETAILS
805: yy where yy.LOCATION_ID=xx.PARTY_SITE_ID
806: ))
807:
808: or exists (select 'Y' from HZ_PARTY_SITES
809: xx where xx.PARTY_ID = temp.party_id
810: and (nvl(STATUS, 'A') in ('A','I'))
811: and exists
812: (select 'Y' from CSI_T_TXN_LINE_DETAILS

Line 846: or exists (select 'Y' from HZ_PARTY_SITES

842:
843: or exists (select 'Y' from CSI_T_TXN_SYSTEMS xx where xx.TECHNICAL_CONTACT_ID = temp.party_id
844: )
845:
846: or exists (select 'Y' from HZ_PARTY_SITES
847: xx where xx.PARTY_ID = temp.party_id
848: and (nvl(STATUS, 'A') in ('A','I'))
849: and exists
850: (select 'Y' from CSI_SYSTEMS_B

Line 877: or exists (select 'Y' from HZ_PARTY_SITES

873:
874: or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT3_ID1 = to_char(temp.party_id)
875: and (jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
876:
877: or exists (select 'Y' from HZ_PARTY_SITES
878: xx where xx.PARTY_ID = temp.party_id
879: and (nvl(STATUS, 'A') in ('A','I'))
880: and exists
881: (select 'Y' from OKC_RULES_B

Line 885: or exists (select 'Y' from HZ_PARTY_SITES

881: (select 'Y' from OKC_RULES_B
882: yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
883: and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
884:
885: or exists (select 'Y' from HZ_PARTY_SITES
886: xx where xx.PARTY_ID = temp.party_id
887: and (nvl(STATUS, 'A') in ('A','I'))
888: and exists
889: (select 'Y' from OKC_RULES_B

Line 893: or exists (select 'Y' from HZ_PARTY_SITES

889: (select 'Y' from OKC_RULES_B
890: yy where yy.OBJECT2_ID1=to_char(xx.PARTY_SITE_ID)
891: and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
892:
893: or exists (select 'Y' from HZ_PARTY_SITES
894: xx where xx.PARTY_ID = temp.party_id
895: and (nvl(STATUS, 'A') in ('A','I'))
896: and exists
897: (select 'Y' from OKC_RULES_B

Line 901: or exists (select 'Y' from HZ_PARTY_SITES

897: (select 'Y' from OKC_RULES_B
898: yy where yy.OBJECT3_ID1=to_char(xx.PARTY_SITE_ID)
899: and jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
900:
901: or exists (select 'Y' from HZ_PARTY_SITES
902: xx where xx.PARTY_ID = temp.party_id
903: and (nvl(STATUS, 'A') in ('A','I'))
904: and exists
905: (select 'Y' from HZ_PARTY_SITE_USES

Line 911: or exists (select 'Y' from HZ_PARTY_SITES

907: and nvl(STATUS, 'A') in ('A','I')
908: and exists
909: (select 'Y' from OKC_RULES_B zz where zz.OBJECT1_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
910:
911: or exists (select 'Y' from HZ_PARTY_SITES
912: xx where xx.PARTY_ID = temp.party_id
913: and (nvl(STATUS, 'A') in ('A','I'))
914: and exists
915: (select 'Y' from HZ_PARTY_SITE_USES

Line 921: or exists (select 'Y' from HZ_PARTY_SITES

917: and nvl(STATUS, 'A') in ('A','I')
918: and exists
919: (select 'Y' from OKC_RULES_B zz where zz.OBJECT2_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
920:
921: or exists (select 'Y' from HZ_PARTY_SITES
922: xx where xx.PARTY_ID = temp.party_id
923: and (nvl(STATUS, 'A') in ('A','I'))
924: and exists
925: (select 'Y' from HZ_PARTY_SITE_USES

Line 934: or exists (select 'Y' from HZ_PARTY_SITES

930:
931: or exists (select 'Y' from OKC_K_ITEMS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
932: and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
933:
934: or exists (select 'Y' from HZ_PARTY_SITES
935: xx where xx.PARTY_ID = temp.party_id
936: and (nvl(STATUS, 'A') in ('A','I'))
937: and exists
938: (select 'Y' from OKC_K_ITEMS

Line 942: or exists (select 'Y' from HZ_PARTY_SITES

938: (select 'Y' from OKC_K_ITEMS
939: yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
940: and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
941:
942: or exists (select 'Y' from HZ_PARTY_SITES
943: xx where xx.PARTY_ID = temp.party_id
944: and (nvl(STATUS, 'A') in ('A','I'))
945: and exists
946: (select 'Y' from HZ_PARTY_SITE_USES

Line 970: or exists (select 'Y' from HZ_PARTY_SITES

966:
967: or exists (select 'Y' from OKC_RULES_B xx where xx.OBJECT3_ID1 = to_char(temp.party_id)
968: and (jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
969:
970: or exists (select 'Y' from HZ_PARTY_SITES
971: xx where xx.PARTY_ID = temp.party_id
972: and (nvl(STATUS, 'A') in ('A','I'))
973: and exists
974: (select 'Y' from OKC_RULES_B

Line 978: or exists (select 'Y' from HZ_PARTY_SITES

974: (select 'Y' from OKC_RULES_B
975: yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
976: and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
977:
978: or exists (select 'Y' from HZ_PARTY_SITES
979: xx where xx.PARTY_ID = temp.party_id
980: and (nvl(STATUS, 'A') in ('A','I'))
981: and exists
982: (select 'Y' from OKC_RULES_B

Line 986: or exists (select 'Y' from HZ_PARTY_SITES

982: (select 'Y' from OKC_RULES_B
983: yy where yy.OBJECT2_ID1=to_char(xx.PARTY_SITE_ID)
984: and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
985:
986: or exists (select 'Y' from HZ_PARTY_SITES
987: xx where xx.PARTY_ID = temp.party_id
988: and (nvl(STATUS, 'A') in ('A','I'))
989: and exists
990: (select 'Y' from OKC_RULES_B

Line 994: or exists (select 'Y' from HZ_PARTY_SITES

990: (select 'Y' from OKC_RULES_B
991: yy where yy.OBJECT3_ID1=to_char(xx.PARTY_SITE_ID)
992: and jtot_object3_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
993:
994: or exists (select 'Y' from HZ_PARTY_SITES
995: xx where xx.PARTY_ID = temp.party_id
996: and (nvl(STATUS, 'A') in ('A','I'))
997: and exists
998: (select 'Y' from HZ_PARTY_SITE_USES

Line 1004: or exists (select 'Y' from HZ_PARTY_SITES

1000: and nvl(STATUS, 'A') in ('A','I')
1001: and exists
1002: (select 'Y' from OKC_RULES_B zz where zz.OBJECT1_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
1003:
1004: or exists (select 'Y' from HZ_PARTY_SITES
1005: xx where xx.PARTY_ID = temp.party_id
1006: and (nvl(STATUS, 'A') in ('A','I'))
1007: and exists
1008: (select 'Y' from HZ_PARTY_SITE_USES

Line 1014: or exists (select 'Y' from HZ_PARTY_SITES

1010: and nvl(STATUS, 'A') in ('A','I')
1011: and exists
1012: (select 'Y' from OKC_RULES_B zz where zz.OBJECT2_ID1=to_char(yy.PARTY_SITE_USE_ID) and jtot_object2_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE_USE'))))
1013:
1014: or exists (select 'Y' from HZ_PARTY_SITES
1015: xx where xx.PARTY_ID = temp.party_id
1016: and (nvl(STATUS, 'A') in ('A','I'))
1017: and exists
1018: (select 'Y' from HZ_PARTY_SITE_USES

Line 1027: or exists (select 'Y' from HZ_PARTY_SITES

1023:
1024: or exists (select 'Y' from OKC_K_ITEMS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
1025: and (jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_PARTY')))
1026:
1027: or exists (select 'Y' from HZ_PARTY_SITES
1028: xx where xx.PARTY_ID = temp.party_id
1029: and (nvl(STATUS, 'A') in ('A','I'))
1030: and exists
1031: (select 'Y' from OKC_K_ITEMS

Line 1035: or exists (select 'Y' from HZ_PARTY_SITES

1031: (select 'Y' from OKC_K_ITEMS
1032: yy where yy.OBJECT1_ID1=to_char(xx.PARTY_SITE_ID)
1033: and jtot_object1_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'OKX_P_SITE')))
1034:
1035: or exists (select 'Y' from HZ_PARTY_SITES
1036: xx where xx.PARTY_ID = temp.party_id
1037: and (nvl(STATUS, 'A') in ('A','I'))
1038: and exists
1039: (select 'Y' from HZ_PARTY_SITE_USES

Line 1056: or exists (select 'Y' from HZ_PARTY_SITES

1052:
1053: exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
1054: )
1055:
1056: or exists (select 'Y' from HZ_PARTY_SITES
1057: xx where xx.PARTY_ID = temp.party_id
1058: and (nvl(STATUS, 'A') in ('A','I'))
1059: and exists
1060: (select 'Y' from ASO_SHIPMENTS

Line 1073: or exists (select 'Y' from HZ_PARTY_SITES

1069:
1070: exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
1071: )
1072:
1073: or exists (select 'Y' from HZ_PARTY_SITES
1074: xx where xx.PARTY_ID = temp.party_id
1075: and (nvl(STATUS, 'A') in ('A','I'))
1076: and exists
1077: (select 'Y' from ASO_SHIPMENTS

Line 1150: exists (select 'Y' from HZ_PARTY_SITES

1146: if(regid_proc = true) then
1147: insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where
1148:
1149:
1150: exists (select 'Y' from HZ_PARTY_SITES
1151: xx where xx.PARTY_ID = temp.party_id
1152: and (nvl(STATUS, 'A') in ('A','I'))
1153: and exists
1154: (select 'Y' from AS_SALES_LEAD_CONTACTS

Line 1165: or exists (select 'Y' from HZ_PARTY_SITES

1161: and exists
1162: (select 'Y' from AS_SALES_LEAD_CONTACTS
1163: yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
1164: ))
1165: or exists (select 'Y' from HZ_PARTY_SITES
1166: xx where xx.PARTY_ID = temp.party_id
1167: and (nvl(STATUS, 'A') in ('A','I'))
1168: and exists
1169: (select 'Y' from HZ_CONTACT_POINTS

Line 1171: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

1167: and (nvl(STATUS, 'A') in ('A','I'))
1168: and exists
1169: (select 'Y' from HZ_CONTACT_POINTS
1170: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
1171: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1172: and exists
1173: (select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
1174:
1175: or exists (select 'Y' from AS_SALES_LEAD_CONTACTS xx where xx.CONTACT_PARTY_ID = temp.party_id

Line 1181: or exists (select 'Y' from HZ_PARTY_SITES

1177:
1178: or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.CUSTOMER_ID = temp.party_id
1179: )
1180:
1181: or exists (select 'Y' from HZ_PARTY_SITES
1182: xx where xx.PARTY_ID = temp.party_id
1183: and (nvl(STATUS, 'A') in ('A','I'))
1184: and exists
1185: (select 'Y' from AS_ACCESSES_ALL

Line 1198: or exists (select 'Y' from HZ_PARTY_SITES

1194:
1195: or exists (select 'Y' from AS_INTERESTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
1196: )
1197:
1198: or exists (select 'Y' from HZ_PARTY_SITES
1199: xx where xx.PARTY_ID = temp.party_id
1200: and (nvl(STATUS, 'A') in ('A','I'))
1201: and exists
1202: (select 'Y' from AS_INTERESTS_ALL

Line 1206: or exists (select 'Y' from HZ_PARTY_SITES

1202: (select 'Y' from AS_INTERESTS_ALL
1203: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
1204: ))
1205:
1206: or exists (select 'Y' from HZ_PARTY_SITES
1207: xx where xx.PARTY_ID = temp.party_id
1208: and (nvl(STATUS, 'A') in ('A','I'))
1209: and exists
1210: (select 'Y' from HZ_ORG_CONTACTS

Line 1246: or exists (select 'Y' from HZ_PARTY_SITES

1242: and nvl(STATUS, 'A') in ('A','I')
1243: and exists
1244: (select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
1245:
1246: or exists (select 'Y' from HZ_PARTY_SITES
1247: xx where xx.PARTY_ID = temp.party_id
1248: and (nvl(STATUS, 'A') in ('A','I'))
1249: and exists
1250: (select 'Y' from AS_ACCESSES_ALL

Line 1263: or exists (select 'Y' from HZ_PARTY_SITES

1259:
1260: or exists (select 'Y' from AS_LEADS_ALL xx where xx.CUSTOMER_ID = temp.party_id
1261: )
1262:
1263: or exists (select 'Y' from HZ_PARTY_SITES
1264: xx where xx.PARTY_ID = temp.party_id
1265: and (nvl(STATUS, 'A') in ('A','I'))
1266: and exists
1267: (select 'Y' from AS_LEADS_ALL

Line 1280: or exists (select 'Y' from HZ_PARTY_SITES

1276:
1277: or exists (select 'Y' from AS_LEAD_COMPETITORS xx where xx.COMPETITOR_ID = temp.party_id
1278: )
1279:
1280: or exists (select 'Y' from HZ_PARTY_SITES
1281: xx where xx.PARTY_ID = temp.party_id
1282: and (nvl(STATUS, 'A') in ('A','I'))
1283: and exists
1284: (select 'Y' from HZ_ORG_CONTACTS

Line 1326: or exists (select 'Y' from HZ_PARTY_SITES

1322:
1323: or exists (select 'Y' from AS_LEAD_CONTACTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
1324: )
1325:
1326: or exists (select 'Y' from HZ_PARTY_SITES
1327: xx where xx.PARTY_ID = temp.party_id
1328: and (nvl(STATUS, 'A') in ('A','I'))
1329: and exists
1330: (select 'Y' from AS_LEAD_CONTACTS_ALL

Line 1337: or exists (select 'Y' from HZ_PARTY_SITES

1333:
1334: or exists (select 'Y' from AS_SALES_CREDITS xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
1335: )
1336:
1337: or exists (select 'Y' from HZ_PARTY_SITES
1338: xx where xx.PARTY_ID = temp.party_id
1339: and (nvl(STATUS, 'A') in ('A','I'))
1340: and exists
1341: (select 'Y' from AS_SALES_CREDITS

Line 1348: or exists (select 'Y' from HZ_PARTY_SITES

1344:
1345: or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
1346: )
1347:
1348: or exists (select 'Y' from HZ_PARTY_SITES
1349: xx where xx.PARTY_ID = temp.party_id
1350: and (nvl(STATUS, 'A') in ('A','I'))
1351: and exists
1352: (select 'Y' from AS_SALES_CREDITS_DENORM

Line 1359: or exists (select 'Y' from HZ_PARTY_SITES

1355:
1356: or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.CUSTOMER_ID = temp.party_id
1357: )
1358:
1359: or exists (select 'Y' from HZ_PARTY_SITES
1360: xx where xx.PARTY_ID = temp.party_id
1361: and (nvl(STATUS, 'A') in ('A','I'))
1362: and exists
1363: (select 'Y' from AS_SALES_CREDITS_DENORM

Line 1374: or exists (select 'Y' from HZ_PARTY_SITES

1370: and exists
1371: (select 'Y' from AS_LEAD_CONTACTS_ALL
1372: yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
1373: ))
1374: or exists (select 'Y' from HZ_PARTY_SITES
1375: xx where xx.PARTY_ID = temp.party_id
1376: and (nvl(STATUS, 'A') in ('A','I'))
1377: and exists
1378: (select 'Y' from HZ_CONTACT_POINTS

Line 1380: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

1376: and (nvl(STATUS, 'A') in ('A','I'))
1377: and exists
1378: (select 'Y' from HZ_CONTACT_POINTS
1379: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
1380: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1381: and exists
1382: (select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
1383:
1384: or exists (select 'Y' from HZ_PARTY_SITES

Line 1384: or exists (select 'Y' from HZ_PARTY_SITES

1380: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1381: and exists
1382: (select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
1383:
1384: or exists (select 'Y' from HZ_PARTY_SITES
1385: xx where xx.PARTY_ID = temp.party_id
1386: and (nvl(STATUS, 'A') in ('A','I'))
1387: and exists
1388: (select 'Y' from AS_CURRENT_ENVIRONMENT

Line 1392: or exists (select 'Y' from HZ_PARTY_SITES

1388: (select 'Y' from AS_CURRENT_ENVIRONMENT
1389: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
1390: ))
1391:
1392: or exists (select 'Y' from HZ_PARTY_SITES
1393: xx where xx.PARTY_ID = temp.party_id
1394: and (nvl(STATUS, 'A') in ('A','I'))
1395: and exists
1396: (select 'Y' from HZ_ORG_CONTACTS

Line 1432: or exists (select 'Y' from HZ_PARTY_SITES

1428: and nvl(STATUS, 'A') in ('A','I')
1429: and exists
1430: (select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
1431:
1432: or exists (select 'Y' from HZ_PARTY_SITES
1433: xx where xx.PARTY_ID = temp.party_id
1434: and (nvl(STATUS, 'A') in ('A','I'))
1435: and exists
1436: (select 'Y' from AS_SALES_LEADS

Line 1453: or exists (select 'Y' from HZ_PARTY_SITES

1449: and exists
1450: (select 'Y' from AS_SALES_LEADS
1451: yy where yy.PRIMARY_CONTACT_PHONE_ID=xx.CONTACT_POINT_ID
1452: ))
1453: or exists (select 'Y' from HZ_PARTY_SITES
1454: xx where xx.PARTY_ID = temp.party_id
1455: and (nvl(STATUS, 'A') in ('A','I'))
1456: and exists
1457: (select 'Y' from HZ_CONTACT_POINTS

Line 1459: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

1455: and (nvl(STATUS, 'A') in ('A','I'))
1456: and exists
1457: (select 'Y' from HZ_CONTACT_POINTS
1458: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
1459: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1460: and exists
1461: (select 'Y' from AS_SALES_LEADS zz where zz.PRIMARY_CONTACT_PHONE_ID = yy.CONTACT_POINT_ID )))
1462:
1463: or exists (select 'Y' from AS_AP_ACCOUNT_PLANS xx where xx.CUST_PARTY_ID = temp.party_id

Line 1469: exists (select 'Y' from HZ_PARTY_SITES

1465: else
1466: delete from hz_purge_gt temp where
1467:
1468:
1469: exists (select 'Y' from HZ_PARTY_SITES
1470: xx where xx.PARTY_ID = temp.party_id
1471: and (nvl(STATUS, 'A') in ('A','I'))
1472: and exists
1473: (select 'Y' from AS_SALES_LEAD_CONTACTS

Line 1484: or exists (select 'Y' from HZ_PARTY_SITES

1480: and exists
1481: (select 'Y' from AS_SALES_LEAD_CONTACTS
1482: yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
1483: ))
1484: or exists (select 'Y' from HZ_PARTY_SITES
1485: xx where xx.PARTY_ID = temp.party_id
1486: and (nvl(STATUS, 'A') in ('A','I'))
1487: and exists
1488: (select 'Y' from HZ_CONTACT_POINTS

Line 1490: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

1486: and (nvl(STATUS, 'A') in ('A','I'))
1487: and exists
1488: (select 'Y' from HZ_CONTACT_POINTS
1489: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
1490: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1491: and exists
1492: (select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
1493:
1494: or exists (select 'Y' from AS_SALES_LEAD_CONTACTS xx where xx.CONTACT_PARTY_ID = temp.party_id

Line 1500: or exists (select 'Y' from HZ_PARTY_SITES

1496:
1497: or exists (select 'Y' from AS_ACCESSES_ALL xx where xx.CUSTOMER_ID = temp.party_id
1498: )
1499:
1500: or exists (select 'Y' from HZ_PARTY_SITES
1501: xx where xx.PARTY_ID = temp.party_id
1502: and (nvl(STATUS, 'A') in ('A','I'))
1503: and exists
1504: (select 'Y' from AS_ACCESSES_ALL

Line 1517: or exists (select 'Y' from HZ_PARTY_SITES

1513:
1514: or exists (select 'Y' from AS_INTERESTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
1515: )
1516:
1517: or exists (select 'Y' from HZ_PARTY_SITES
1518: xx where xx.PARTY_ID = temp.party_id
1519: and (nvl(STATUS, 'A') in ('A','I'))
1520: and exists
1521: (select 'Y' from AS_INTERESTS_ALL

Line 1525: or exists (select 'Y' from HZ_PARTY_SITES

1521: (select 'Y' from AS_INTERESTS_ALL
1522: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
1523: ))
1524:
1525: or exists (select 'Y' from HZ_PARTY_SITES
1526: xx where xx.PARTY_ID = temp.party_id
1527: and (nvl(STATUS, 'A') in ('A','I'))
1528: and exists
1529: (select 'Y' from HZ_ORG_CONTACTS

Line 1565: or exists (select 'Y' from HZ_PARTY_SITES

1561: and nvl(STATUS, 'A') in ('A','I')
1562: and exists
1563: (select 'Y' from AS_INTERESTS_ALL zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
1564:
1565: or exists (select 'Y' from HZ_PARTY_SITES
1566: xx where xx.PARTY_ID = temp.party_id
1567: and (nvl(STATUS, 'A') in ('A','I'))
1568: and exists
1569: (select 'Y' from AS_ACCESSES_ALL

Line 1582: or exists (select 'Y' from HZ_PARTY_SITES

1578:
1579: or exists (select 'Y' from AS_LEADS_ALL xx where xx.CUSTOMER_ID = temp.party_id
1580: )
1581:
1582: or exists (select 'Y' from HZ_PARTY_SITES
1583: xx where xx.PARTY_ID = temp.party_id
1584: and (nvl(STATUS, 'A') in ('A','I'))
1585: and exists
1586: (select 'Y' from AS_LEADS_ALL

Line 1599: or exists (select 'Y' from HZ_PARTY_SITES

1595:
1596: or exists (select 'Y' from AS_LEAD_COMPETITORS xx where xx.COMPETITOR_ID = temp.party_id
1597: )
1598:
1599: or exists (select 'Y' from HZ_PARTY_SITES
1600: xx where xx.PARTY_ID = temp.party_id
1601: and (nvl(STATUS, 'A') in ('A','I'))
1602: and exists
1603: (select 'Y' from HZ_ORG_CONTACTS

Line 1645: or exists (select 'Y' from HZ_PARTY_SITES

1641:
1642: or exists (select 'Y' from AS_LEAD_CONTACTS_ALL xx where xx.CUSTOMER_ID = temp.party_id
1643: )
1644:
1645: or exists (select 'Y' from HZ_PARTY_SITES
1646: xx where xx.PARTY_ID = temp.party_id
1647: and (nvl(STATUS, 'A') in ('A','I'))
1648: and exists
1649: (select 'Y' from AS_LEAD_CONTACTS_ALL

Line 1656: or exists (select 'Y' from HZ_PARTY_SITES

1652:
1653: or exists (select 'Y' from AS_SALES_CREDITS xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
1654: )
1655:
1656: or exists (select 'Y' from HZ_PARTY_SITES
1657: xx where xx.PARTY_ID = temp.party_id
1658: and (nvl(STATUS, 'A') in ('A','I'))
1659: and exists
1660: (select 'Y' from AS_SALES_CREDITS

Line 1667: or exists (select 'Y' from HZ_PARTY_SITES

1663:
1664: or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.PARTNER_CUSTOMER_ID = temp.party_id
1665: )
1666:
1667: or exists (select 'Y' from HZ_PARTY_SITES
1668: xx where xx.PARTY_ID = temp.party_id
1669: and (nvl(STATUS, 'A') in ('A','I'))
1670: and exists
1671: (select 'Y' from AS_SALES_CREDITS_DENORM

Line 1678: or exists (select 'Y' from HZ_PARTY_SITES

1674:
1675: or exists (select 'Y' from AS_SALES_CREDITS_DENORM xx where xx.CUSTOMER_ID = temp.party_id
1676: )
1677:
1678: or exists (select 'Y' from HZ_PARTY_SITES
1679: xx where xx.PARTY_ID = temp.party_id
1680: and (nvl(STATUS, 'A') in ('A','I'))
1681: and exists
1682: (select 'Y' from AS_SALES_CREDITS_DENORM

Line 1693: or exists (select 'Y' from HZ_PARTY_SITES

1689: and exists
1690: (select 'Y' from AS_LEAD_CONTACTS_ALL
1691: yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
1692: ))
1693: or exists (select 'Y' from HZ_PARTY_SITES
1694: xx where xx.PARTY_ID = temp.party_id
1695: and (nvl(STATUS, 'A') in ('A','I'))
1696: and exists
1697: (select 'Y' from HZ_CONTACT_POINTS

Line 1699: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

1695: and (nvl(STATUS, 'A') in ('A','I'))
1696: and exists
1697: (select 'Y' from HZ_CONTACT_POINTS
1698: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
1699: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1700: and exists
1701: (select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
1702:
1703: or exists (select 'Y' from HZ_PARTY_SITES

Line 1703: or exists (select 'Y' from HZ_PARTY_SITES

1699: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1700: and exists
1701: (select 'Y' from AS_LEAD_CONTACTS_ALL zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
1702:
1703: or exists (select 'Y' from HZ_PARTY_SITES
1704: xx where xx.PARTY_ID = temp.party_id
1705: and (nvl(STATUS, 'A') in ('A','I'))
1706: and exists
1707: (select 'Y' from AS_CURRENT_ENVIRONMENT

Line 1711: or exists (select 'Y' from HZ_PARTY_SITES

1707: (select 'Y' from AS_CURRENT_ENVIRONMENT
1708: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
1709: ))
1710:
1711: or exists (select 'Y' from HZ_PARTY_SITES
1712: xx where xx.PARTY_ID = temp.party_id
1713: and (nvl(STATUS, 'A') in ('A','I'))
1714: and exists
1715: (select 'Y' from HZ_ORG_CONTACTS

Line 1751: or exists (select 'Y' from HZ_PARTY_SITES

1747: and nvl(STATUS, 'A') in ('A','I')
1748: and exists
1749: (select 'Y' from AS_SALES_LEAD_CONTACTS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID )))
1750:
1751: or exists (select 'Y' from HZ_PARTY_SITES
1752: xx where xx.PARTY_ID = temp.party_id
1753: and (nvl(STATUS, 'A') in ('A','I'))
1754: and exists
1755: (select 'Y' from AS_SALES_LEADS

Line 1772: or exists (select 'Y' from HZ_PARTY_SITES

1768: and exists
1769: (select 'Y' from AS_SALES_LEADS
1770: yy where yy.PRIMARY_CONTACT_PHONE_ID=xx.CONTACT_POINT_ID
1771: ))
1772: or exists (select 'Y' from HZ_PARTY_SITES
1773: xx where xx.PARTY_ID = temp.party_id
1774: and (nvl(STATUS, 'A') in ('A','I'))
1775: and exists
1776: (select 'Y' from HZ_CONTACT_POINTS

Line 1778: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

1774: and (nvl(STATUS, 'A') in ('A','I'))
1775: and exists
1776: (select 'Y' from HZ_CONTACT_POINTS
1777: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
1778: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1779: and exists
1780: (select 'Y' from AS_SALES_LEADS zz where zz.PRIMARY_CONTACT_PHONE_ID = yy.CONTACT_POINT_ID )))
1781:
1782: or exists (select 'Y' from AS_AP_ACCOUNT_PLANS xx where xx.CUST_PARTY_ID = temp.party_id

Line 1981: exists (select 'Y' from HZ_PARTY_SITES

1977: if(regid_proc = true) then
1978: insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where
1979:
1980:
1981: exists (select 'Y' from HZ_PARTY_SITES
1982: xx where xx.PARTY_ID = temp.party_id
1983: and (nvl(STATUS, 'A') in ('A','I'))
1984: and exists
1985: (select 'Y' from HZ_PARTY_SITE_USES

Line 1991: or exists (select 'Y' from HZ_PARTY_SITES

1987: and nvl(STATUS, 'A') in ('A','I')
1988: and exists
1989: (select 'Y' from OKL_RELOCATE_ASSETS_B zz where zz.IST_ID = yy.PARTY_SITE_USE_ID )))
1990:
1991: or exists (select 'Y' from HZ_PARTY_SITES
1992: xx where xx.PARTY_ID = temp.party_id
1993: and (nvl(STATUS, 'A') in ('A','I'))
1994: and exists
1995: (select 'Y' from HZ_PARTY_SITE_USES

Line 2017: or exists (select 'Y' from HZ_PARTY_SITES

2013: and exists
2014: (select 'Y' from OKL_RELOCATE_ASSETS_B
2015: yy where yy.PAC_ID=xx.CONTACT_POINT_ID
2016: ))
2017: or exists (select 'Y' from HZ_PARTY_SITES
2018: xx where xx.PARTY_ID = temp.party_id
2019: and (nvl(STATUS, 'A') in ('A','I'))
2020: and exists
2021: (select 'Y' from HZ_CONTACT_POINTS

Line 2023: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

2019: and (nvl(STATUS, 'A') in ('A','I'))
2020: and exists
2021: (select 'Y' from HZ_CONTACT_POINTS
2022: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
2023: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
2024: and exists
2025: (select 'Y' from OKL_RELOCATE_ASSETS_B zz where zz.PAC_ID = yy.CONTACT_POINT_ID )));
2026: else
2027: delete from hz_purge_gt temp where

Line 2030: exists (select 'Y' from HZ_PARTY_SITES

2026: else
2027: delete from hz_purge_gt temp where
2028:
2029:
2030: exists (select 'Y' from HZ_PARTY_SITES
2031: xx where xx.PARTY_ID = temp.party_id
2032: and (nvl(STATUS, 'A') in ('A','I'))
2033: and exists
2034: (select 'Y' from HZ_PARTY_SITE_USES

Line 2040: or exists (select 'Y' from HZ_PARTY_SITES

2036: and nvl(STATUS, 'A') in ('A','I')
2037: and exists
2038: (select 'Y' from OKL_RELOCATE_ASSETS_B zz where zz.IST_ID = yy.PARTY_SITE_USE_ID )))
2039:
2040: or exists (select 'Y' from HZ_PARTY_SITES
2041: xx where xx.PARTY_ID = temp.party_id
2042: and (nvl(STATUS, 'A') in ('A','I'))
2043: and exists
2044: (select 'Y' from HZ_PARTY_SITE_USES

Line 2066: or exists (select 'Y' from HZ_PARTY_SITES

2062: and exists
2063: (select 'Y' from OKL_RELOCATE_ASSETS_B
2064: yy where yy.PAC_ID=xx.CONTACT_POINT_ID
2065: ))
2066: or exists (select 'Y' from HZ_PARTY_SITES
2067: xx where xx.PARTY_ID = temp.party_id
2068: and (nvl(STATUS, 'A') in ('A','I'))
2069: and exists
2070: (select 'Y' from HZ_CONTACT_POINTS

Line 2072: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

2068: and (nvl(STATUS, 'A') in ('A','I'))
2069: and exists
2070: (select 'Y' from HZ_CONTACT_POINTS
2071: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
2072: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
2073: and exists
2074: (select 'Y' from OKL_RELOCATE_ASSETS_B zz where zz.PAC_ID = yy.CONTACT_POINT_ID )));
2075: end if;
2076:

Line 2226: or exists (select 'Y' from HZ_PARTY_SITES

2222:
2223: or exists (select 'Y' from ISC_BOOK_SUM2_F xx where xx.CUSTOMER_ID = temp.party_id
2224: )
2225:
2226: or exists (select 'Y' from HZ_PARTY_SITES
2227: xx where xx.PARTY_ID = temp.party_id
2228: and (nvl(STATUS, 'A') in ('A','I'))
2229: and exists
2230: (select 'Y' from ISC_FS_TASKS_F

Line 2252: or exists (select 'Y' from HZ_PARTY_SITES

2248:
2249: or exists (select 'Y' from ISC_BOOK_SUM2_F xx where xx.CUSTOMER_ID = temp.party_id
2250: )
2251:
2252: or exists (select 'Y' from HZ_PARTY_SITES
2253: xx where xx.PARTY_ID = temp.party_id
2254: and (nvl(STATUS, 'A') in ('A','I'))
2255: and exists
2256: (select 'Y' from ISC_FS_TASKS_F

Line 2292: or exists (select 'Y' from HZ_PARTY_SITES

2288:
2289: or exists (select 'Y' from JTF_TASK_CONTACTS xx where xx.CONTACT_ID = temp.party_id
2290: and (CONTACT_TYPE_CODE = 'CUST'))
2291:
2292: or exists (select 'Y' from HZ_PARTY_SITES
2293: xx where xx.PARTY_ID = temp.party_id
2294: and (nvl(STATUS, 'A') in ('A','I'))
2295: and exists
2296: (select 'Y' from JTF_TASK_REFERENCES_B

Line 2298: and object_type_code in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTY_SITES' and rtrim(ltrim(upper(select_id))) = 'PARTY_SITE_ID')))

2294: and (nvl(STATUS, 'A') in ('A','I'))
2295: and exists
2296: (select 'Y' from JTF_TASK_REFERENCES_B
2297: yy where yy.OBJECT_ID=xx.PARTY_SITE_ID
2298: and object_type_code in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTY_SITES' and rtrim(ltrim(upper(select_id))) = 'PARTY_SITE_ID')))
2299:
2300: or exists (select 'Y' from JTF_TASK_REFERENCES_B xx where xx.OBJECT_ID = temp.party_id
2301: and (OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
2302:

Line 2303: or exists (select 'Y' from HZ_PARTY_SITES

2299:
2300: or exists (select 'Y' from JTF_TASK_REFERENCES_B xx where xx.OBJECT_ID = temp.party_id
2301: and (OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
2302:
2303: or exists (select 'Y' from HZ_PARTY_SITES
2304: xx where xx.PARTY_ID = temp.party_id
2305: and (nvl(STATUS, 'A') in ('A','I'))
2306: and exists
2307: (select 'Y' from JTF_TASKS_B

Line 2311: or exists (select 'Y' from HZ_PARTY_SITES

2307: (select 'Y' from JTF_TASKS_B
2308: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
2309: ))
2310:
2311: or exists (select 'Y' from HZ_PARTY_SITES
2312: xx where xx.PARTY_ID = temp.party_id
2313: and (nvl(STATUS, 'A') in ('A','I'))
2314: and exists
2315: (select 'Y' from JTF_TASK_AUDITS_B

Line 2319: or exists (select 'Y' from HZ_PARTY_SITES

2315: (select 'Y' from JTF_TASK_AUDITS_B
2316: yy where yy.NEW_ADDRESS_ID=xx.PARTY_SITE_ID
2317: ))
2318:
2319: or exists (select 'Y' from HZ_PARTY_SITES
2320: xx where xx.PARTY_ID = temp.party_id
2321: and (nvl(STATUS, 'A') in ('A','I'))
2322: and exists
2323: (select 'Y' from JTF_TASK_AUDITS_B

Line 2334: or exists (select 'Y' from HZ_PARTY_SITES

2330: and exists
2331: (select 'Y' from JTF_TASK_PHONES
2332: yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
2333: ))
2334: or exists (select 'Y' from HZ_PARTY_SITES
2335: xx where xx.PARTY_ID = temp.party_id
2336: and (nvl(STATUS, 'A') in ('A','I'))
2337: and exists
2338: (select 'Y' from HZ_CONTACT_POINTS

Line 2340: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

2336: and (nvl(STATUS, 'A') in ('A','I'))
2337: and exists
2338: (select 'Y' from HZ_CONTACT_POINTS
2339: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
2340: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
2341: and exists
2342: (select 'Y' from JTF_TASK_PHONES zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
2343:
2344: or exists (select 'Y' from HZ_PARTY_SITES

Line 2344: or exists (select 'Y' from HZ_PARTY_SITES

2340: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
2341: and exists
2342: (select 'Y' from JTF_TASK_PHONES zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
2343:
2344: or exists (select 'Y' from HZ_PARTY_SITES
2345: xx where xx.PARTY_ID = temp.party_id
2346: and (nvl(STATUS, 'A') in ('A','I'))
2347: and exists
2348: (select 'Y' from HZ_ORG_CONTACTS

Line 2384: or exists (select 'Y' from HZ_PARTY_SITES

2380: and nvl(STATUS, 'A') in ('A','I')
2381: and exists
2382: (select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
2383:
2384: or exists (select 'Y' from HZ_PARTY_SITES
2385: xx where xx.PARTY_ID = temp.party_id
2386: and (nvl(STATUS, 'A') in ('A','I'))
2387: and exists
2388: (select 'Y' from JTF_NOTES_B

Line 2390: and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))

2386: and (nvl(STATUS, 'A') in ('A','I'))
2387: and exists
2388: (select 'Y' from JTF_NOTES_B
2389: yy where yy.SOURCE_OBJECT_ID=xx.PARTY_SITE_ID
2390: and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
2391:
2392: or exists (select 'Y' from HZ_PARTY_SITES
2393: xx where xx.PARTY_ID = temp.party_id
2394: and (nvl(STATUS, 'A') in ('A','I'))

Line 2392: or exists (select 'Y' from HZ_PARTY_SITES

2388: (select 'Y' from JTF_NOTES_B
2389: yy where yy.SOURCE_OBJECT_ID=xx.PARTY_SITE_ID
2390: and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
2391:
2392: or exists (select 'Y' from HZ_PARTY_SITES
2393: xx where xx.PARTY_ID = temp.party_id
2394: and (nvl(STATUS, 'A') in ('A','I'))
2395: and exists
2396: (select 'Y' from JTF_NOTE_CONTEXTS

Line 2398: and NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))

2394: and (nvl(STATUS, 'A') in ('A','I'))
2395: and exists
2396: (select 'Y' from JTF_NOTE_CONTEXTS
2397: yy where yy.NOTE_CONTEXT_TYPE_ID=xx.PARTY_SITE_ID
2398: and NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
2399:
2400: or exists (select 'Y' from JTF_NOTES_B xx where xx.SOURCE_OBJECT_ID = temp.party_id
2401: and (source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTIES' )))
2402:

Line 2412: or exists (select 'Y' from HZ_PARTY_SITES

2408:
2409: or exists (select 'Y' from JTF_RS_RESOURCE_EXTNS xx where xx.SOURCE_ID = temp.party_id
2410: and (category IN ( 'PARTY' , 'PARTNER')))
2411:
2412: or exists (select 'Y' from HZ_PARTY_SITES
2413: xx where xx.PARTY_ID = temp.party_id
2414: and (nvl(STATUS, 'A') in ('A','I'))
2415: and exists
2416: (select 'Y' from JTF_RS_RESOURCE_EXTNS

Line 2423: or exists (select 'Y' from HZ_PARTY_SITES

2419:
2420: or exists (select 'Y' from JTF_UM_APPROVERS xx where xx.ORG_PARTY_ID = temp.party_id
2421: and (ROWNUM < 2))
2422:
2423: or exists (select 'Y' from HZ_PARTY_SITES
2424: xx where xx.PARTY_ID = temp.party_id
2425: and (nvl(STATUS, 'A') in ('A','I'))
2426: and exists
2427: (select 'Y' from JTF_TERR_VALUES_ALL

Line 2443: or exists (select 'Y' from HZ_PARTY_SITES

2439:
2440: or exists (select 'Y' from JTF_PERZ_QUERY_PARAM xx where xx.PARAMETER_VALUE = to_char(temp.party_id)
2441: and (parameter_name = 'CUSTOMER_ID' AND query_id IN (SELECT q.query_id FROM jtf_perz_query q, jtf_perz_query_param p WHERE q.query_type= 'JTF_TASK' AND q.application_id = 690 AND p.query_id = q.query_id AND p.parameter_name = 'CUSTOMER' AND p.parameter_value = 'NUMBER')))
2442:
2443: or exists (select 'Y' from HZ_PARTY_SITES
2444: xx where xx.PARTY_ID = temp.party_id
2445: and (nvl(STATUS, 'A') in ('A','I'))
2446: and exists
2447: (select 'Y' from JTF_RS_RESOURCE_EXTNS

Line 2478: or exists (select 'Y' from HZ_PARTY_SITES

2474:
2475: or exists (select 'Y' from JTF_TASK_CONTACTS xx where xx.CONTACT_ID = temp.party_id
2476: and (CONTACT_TYPE_CODE = 'CUST'))
2477:
2478: or exists (select 'Y' from HZ_PARTY_SITES
2479: xx where xx.PARTY_ID = temp.party_id
2480: and (nvl(STATUS, 'A') in ('A','I'))
2481: and exists
2482: (select 'Y' from JTF_TASK_REFERENCES_B

Line 2484: and object_type_code in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTY_SITES' and rtrim(ltrim(upper(select_id))) = 'PARTY_SITE_ID')))

2480: and (nvl(STATUS, 'A') in ('A','I'))
2481: and exists
2482: (select 'Y' from JTF_TASK_REFERENCES_B
2483: yy where yy.OBJECT_ID=xx.PARTY_SITE_ID
2484: and object_type_code in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTY_SITES' and rtrim(ltrim(upper(select_id))) = 'PARTY_SITE_ID')))
2485:
2486: or exists (select 'Y' from JTF_TASK_REFERENCES_B xx where xx.OBJECT_ID = temp.party_id
2487: and (OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
2488:

Line 2489: or exists (select 'Y' from HZ_PARTY_SITES

2485:
2486: or exists (select 'Y' from JTF_TASK_REFERENCES_B xx where xx.OBJECT_ID = temp.party_id
2487: and (OBJECT_TYPE_CODE in (select object_code from jtf_objects_b where ltrim(rtrim(upper(from_table))) = 'HZ_PARTIES' and rtrim(ltrim(upper(select_id))) = 'PARTY_ID')))
2488:
2489: or exists (select 'Y' from HZ_PARTY_SITES
2490: xx where xx.PARTY_ID = temp.party_id
2491: and (nvl(STATUS, 'A') in ('A','I'))
2492: and exists
2493: (select 'Y' from JTF_TASKS_B

Line 2497: or exists (select 'Y' from HZ_PARTY_SITES

2493: (select 'Y' from JTF_TASKS_B
2494: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
2495: ))
2496:
2497: or exists (select 'Y' from HZ_PARTY_SITES
2498: xx where xx.PARTY_ID = temp.party_id
2499: and (nvl(STATUS, 'A') in ('A','I'))
2500: and exists
2501: (select 'Y' from JTF_TASK_AUDITS_B

Line 2505: or exists (select 'Y' from HZ_PARTY_SITES

2501: (select 'Y' from JTF_TASK_AUDITS_B
2502: yy where yy.NEW_ADDRESS_ID=xx.PARTY_SITE_ID
2503: ))
2504:
2505: or exists (select 'Y' from HZ_PARTY_SITES
2506: xx where xx.PARTY_ID = temp.party_id
2507: and (nvl(STATUS, 'A') in ('A','I'))
2508: and exists
2509: (select 'Y' from JTF_TASK_AUDITS_B

Line 2520: or exists (select 'Y' from HZ_PARTY_SITES

2516: and exists
2517: (select 'Y' from JTF_TASK_PHONES
2518: yy where yy.PHONE_ID=xx.CONTACT_POINT_ID
2519: ))
2520: or exists (select 'Y' from HZ_PARTY_SITES
2521: xx where xx.PARTY_ID = temp.party_id
2522: and (nvl(STATUS, 'A') in ('A','I'))
2523: and exists
2524: (select 'Y' from HZ_CONTACT_POINTS

Line 2526: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')

2522: and (nvl(STATUS, 'A') in ('A','I'))
2523: and exists
2524: (select 'Y' from HZ_CONTACT_POINTS
2525: yy where yy.OWNER_TABLE_ID=xx.PARTY_SITE_ID
2526: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
2527: and exists
2528: (select 'Y' from JTF_TASK_PHONES zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
2529:
2530: or exists (select 'Y' from HZ_PARTY_SITES

Line 2530: or exists (select 'Y' from HZ_PARTY_SITES

2526: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
2527: and exists
2528: (select 'Y' from JTF_TASK_PHONES zz where zz.PHONE_ID = yy.CONTACT_POINT_ID )))
2529:
2530: or exists (select 'Y' from HZ_PARTY_SITES
2531: xx where xx.PARTY_ID = temp.party_id
2532: and (nvl(STATUS, 'A') in ('A','I'))
2533: and exists
2534: (select 'Y' from HZ_ORG_CONTACTS

Line 2570: or exists (select 'Y' from HZ_PARTY_SITES

2566: and nvl(STATUS, 'A') in ('A','I')
2567: and exists
2568: (select 'Y' from JTF_RS_RESOURCE_EXTNS zz where zz.CONTACT_ID = yy.ORG_CONTACT_ID and category in ('PARTY', 'PARTNER' ))))
2569:
2570: or exists (select 'Y' from HZ_PARTY_SITES
2571: xx where xx.PARTY_ID = temp.party_id
2572: and (nvl(STATUS, 'A') in ('A','I'))
2573: and exists
2574: (select 'Y' from JTF_NOTES_B

Line 2576: and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))

2572: and (nvl(STATUS, 'A') in ('A','I'))
2573: and exists
2574: (select 'Y' from JTF_NOTES_B
2575: yy where yy.SOURCE_OBJECT_ID=xx.PARTY_SITE_ID
2576: and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
2577:
2578: or exists (select 'Y' from HZ_PARTY_SITES
2579: xx where xx.PARTY_ID = temp.party_id
2580: and (nvl(STATUS, 'A') in ('A','I'))

Line 2578: or exists (select 'Y' from HZ_PARTY_SITES

2574: (select 'Y' from JTF_NOTES_B
2575: yy where yy.SOURCE_OBJECT_ID=xx.PARTY_SITE_ID
2576: and source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
2577:
2578: or exists (select 'Y' from HZ_PARTY_SITES
2579: xx where xx.PARTY_ID = temp.party_id
2580: and (nvl(STATUS, 'A') in ('A','I'))
2581: and exists
2582: (select 'Y' from JTF_NOTE_CONTEXTS

Line 2584: and NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))

2580: and (nvl(STATUS, 'A') in ('A','I'))
2581: and exists
2582: (select 'Y' from JTF_NOTE_CONTEXTS
2583: yy where yy.NOTE_CONTEXT_TYPE_ID=xx.PARTY_SITE_ID
2584: and NOTE_CONTEXT_TYPE IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTY_SITES')))
2585:
2586: or exists (select 'Y' from JTF_NOTES_B xx where xx.SOURCE_OBJECT_ID = temp.party_id
2587: and (source_object_code IN (SELECT ojt.object_code FROM jtf_objects_b ojt ,jtf_object_usages oue WHERE ojt.object_code = oue.object_code AND oue.object_user_code = 'NOTES' and ojt.from_table ='HZ_PARTIES' )))
2588:

Line 2598: or exists (select 'Y' from HZ_PARTY_SITES

2594:
2595: or exists (select 'Y' from JTF_RS_RESOURCE_EXTNS xx where xx.SOURCE_ID = temp.party_id
2596: and (category IN ( 'PARTY' , 'PARTNER')))
2597:
2598: or exists (select 'Y' from HZ_PARTY_SITES
2599: xx where xx.PARTY_ID = temp.party_id
2600: and (nvl(STATUS, 'A') in ('A','I'))
2601: and exists
2602: (select 'Y' from JTF_RS_RESOURCE_EXTNS

Line 2609: or exists (select 'Y' from HZ_PARTY_SITES

2605:
2606: or exists (select 'Y' from JTF_UM_APPROVERS xx where xx.ORG_PARTY_ID = temp.party_id
2607: and (ROWNUM < 2))
2608:
2609: or exists (select 'Y' from HZ_PARTY_SITES
2610: xx where xx.PARTY_ID = temp.party_id
2611: and (nvl(STATUS, 'A') in ('A','I'))
2612: and exists
2613: (select 'Y' from JTF_TERR_VALUES_ALL

Line 2629: or exists (select 'Y' from HZ_PARTY_SITES

2625:
2626: or exists (select 'Y' from JTF_PERZ_QUERY_PARAM xx where xx.PARAMETER_VALUE = to_char(temp.party_id)
2627: and (parameter_name = 'CUSTOMER_ID' AND query_id IN (SELECT q.query_id FROM jtf_perz_query q, jtf_perz_query_param p WHERE q.query_type= 'JTF_TASK' AND q.application_id = 690 AND p.query_id = q.query_id AND p.parameter_name = 'CUSTOMER' AND p.parameter_value = 'NUMBER')))
2628:
2629: or exists (select 'Y' from HZ_PARTY_SITES
2630: xx where xx.PARTY_ID = temp.party_id
2631: and (nvl(STATUS, 'A') in ('A','I'))
2632: and exists
2633: (select 'Y' from JTF_RS_RESOURCE_EXTNS

Line 2712: or exists (select 'Y' from HZ_PARTY_SITES

2708:
2709: or exists (select 'Y' from CS_HZ_SR_CONTACT_POINTS xx where xx.PARTY_ID = temp.party_id
2710: and (CONTACT_TYPE <> 'EMPLOYEE'))
2711:
2712: or exists (select 'Y' from HZ_PARTY_SITES
2713: xx where xx.PARTY_ID = temp.party_id
2714: and (nvl(STATUS, 'A') in ('A','I'))
2715: and exists
2716: (select 'Y' from CS_ESTIMATE_DETAILS

Line 2720: or exists (select 'Y' from HZ_PARTY_SITES

2716: (select 'Y' from CS_ESTIMATE_DETAILS
2717: yy where yy.INVOICE_TO_ORG_ID=xx.PARTY_SITE_ID
2718: ))
2719:
2720: or exists (select 'Y' from HZ_PARTY_SITES
2721: xx where xx.PARTY_ID = temp.party_id
2722: and (nvl(STATUS, 'A') in ('A','I'))
2723: and exists
2724: (select 'Y' from CS_ESTIMATE_DETAILS

Line 2737: or exists (select 'Y' from HZ_PARTY_SITES

2733:
2734: or exists (select 'Y' from CS_HZ_SR_CONTACT_POINTS xx where xx.PARTY_ID = temp.party_id
2735: and (CONTACT_TYPE <> 'EMPLOYEE'))
2736:
2737: or exists (select 'Y' from HZ_PARTY_SITES
2738: xx where xx.PARTY_ID = temp.party_id
2739: and (nvl(STATUS, 'A') in ('A','I'))
2740: and exists
2741: (select 'Y' from CS_ESTIMATE_DETAILS

Line 2745: or exists (select 'Y' from HZ_PARTY_SITES

2741: (select 'Y' from CS_ESTIMATE_DETAILS
2742: yy where yy.INVOICE_TO_ORG_ID=xx.PARTY_SITE_ID
2743: ))
2744:
2745: or exists (select 'Y' from HZ_PARTY_SITES
2746: xx where xx.PARTY_ID = temp.party_id
2747: and (nvl(STATUS, 'A') in ('A','I'))
2748: and exists
2749: (select 'Y' from CS_ESTIMATE_DETAILS

Line 2764: or exists (select 'Y' from HZ_PARTY_SITES

2760:
2761: exists (select 'Y' from OE_PRICE_ADJ_ATTRIBS xx where xx.PRICING_ATTR_VALUE_FROM = to_char(temp.party_id)
2762: and (pricing_context = 'ASOPARTYINFO' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE1' OR pricing_context = 'CUSTOMER' AND pricing_attribute ='QUALIFIER_ATTRIBUTE16' OR pricing_context = 'CUSTOMER_GROUP' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE3' OR pricing_context = 'PARTY' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2')))
2763:
2764: or exists (select 'Y' from HZ_PARTY_SITES
2765: xx where xx.PARTY_ID = temp.party_id
2766: and (nvl(STATUS, 'A') in ('A','I'))
2767: and exists
2768: (select 'Y' from OE_PRICE_ADJ_ATTRIBS

Line 2778: or exists (select 'Y' from HZ_PARTY_SITES

2774:
2775: exists (select 'Y' from OE_PRICE_ADJ_ATTRIBS xx where xx.PRICING_ATTR_VALUE_FROM = to_char(temp.party_id)
2776: and (pricing_context = 'ASOPARTYINFO' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE1' OR pricing_context = 'CUSTOMER' AND pricing_attribute ='QUALIFIER_ATTRIBUTE16' OR pricing_context = 'CUSTOMER_GROUP' AND pricing_attribute = 'QUALIFIER_ATTRIBUTE3' OR pricing_context = 'PARTY' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2')))
2777:
2778: or exists (select 'Y' from HZ_PARTY_SITES
2779: xx where xx.PARTY_ID = temp.party_id
2780: and (nvl(STATUS, 'A') in ('A','I'))
2781: and exists
2782: (select 'Y' from OE_PRICE_ADJ_ATTRIBS

Line 2939: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

2935: appid:=665;
2936: if(regid_proc = true) then
2937: insert into hz_application_trans_gt(app_id,party_id) select 665, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 665 and appl.party_id=temp.party_id) and
2938:
2939: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
2940: xx
2941: where (nvl(STATUS, 'A') in ('A','I'))
2942: and xx.PARTY_SITE_ID
2943: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from WSH_PARTY_SITES_V

Line 2948: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

2944: yy
2945: ));
2946: else
2947: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2948: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
2949: xx
2950: where (nvl(STATUS, 'A') in ('A','I'))
2951: and xx.PARTY_SITE_ID
2952: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from WSH_PARTY_SITES_V

Line 2997: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

2993: appid:=222;
2994: if(regid_proc = true) then
2995: insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 222 and appl.party_id=temp.party_id) and
2996:
2997: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
2998: xx
2999: where (nvl(STATUS, 'A') in ('A','I'))
3000: and xx.PARTY_SITE_ID
3001: in (select /*+ parallel(yy)*/ yy.OWNER_TABLE_ID from HZ_CODE_ASSIGNMENTS

Line 3003: where OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')));

2999: where (nvl(STATUS, 'A') in ('A','I'))
3000: and xx.PARTY_SITE_ID
3001: in (select /*+ parallel(yy)*/ yy.OWNER_TABLE_ID from HZ_CODE_ASSIGNMENTS
3002: yy
3003: where OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')));
3004: else
3005: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3006: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3007: xx

Line 3006: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3002: yy
3003: where OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')));
3004: else
3005: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3006: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3007: xx
3008: where (nvl(STATUS, 'A') in ('A','I'))
3009: and xx.PARTY_SITE_ID
3010: in (select /*+ parallel(yy)*/ yy.OWNER_TABLE_ID from HZ_CODE_ASSIGNMENTS

Line 3012: where OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')));

3008: where (nvl(STATUS, 'A') in ('A','I'))
3009: and xx.PARTY_SITE_ID
3010: in (select /*+ parallel(yy)*/ yy.OWNER_TABLE_ID from HZ_CODE_ASSIGNMENTS
3011: yy
3012: where OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')));
3013: end if;
3014: --HZ_WORK_CLASS;EMPLOYMENT_HISTORY_ID
3015: appid:=222;
3016: if(regid_proc = true) then

Line 3511: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3507: appid:=435;
3508: if(regid_proc = true) then
3509: insert into hz_application_trans_gt(app_id,party_id) select 435, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 435 and appl.party_id=temp.party_id) and
3510:
3511: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3512: xx
3513: where (nvl(STATUS, 'A') in ('A','I'))
3514: and xx.PARTY_SITE_ID
3515: in (select /*+ parallel(yy)*/ yy.VENDOR_SITE_ID from FUN_SUPPLIER_MAPS

Line 3520: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3516: yy
3517: ));
3518: else
3519: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3520: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3521: xx
3522: where (nvl(STATUS, 'A') in ('A','I'))
3523: and xx.PARTY_SITE_ID
3524: in (select /*+ parallel(yy)*/ yy.VENDOR_SITE_ID from FUN_SUPPLIER_MAPS

Line 3639: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3635: appid:=542;
3636: if(regid_proc = true) then
3637: insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 542 and appl.party_id=temp.party_id) and
3638:
3639: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3640: xx
3641: where (nvl(STATUS, 'A') in ('A','I'))
3642: and xx.PARTY_SITE_ID
3643: in (select /*+ parallel(yy)*/ yy.LOCATION_ID from CSI_ITEM_INSTANCES

Line 3648: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3644: yy
3645: ));
3646: else
3647: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3648: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3649: xx
3650: where (nvl(STATUS, 'A') in ('A','I'))
3651: and xx.PARTY_SITE_ID
3652: in (select /*+ parallel(yy)*/ yy.LOCATION_ID from CSI_ITEM_INSTANCES

Line 3661: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3657: appid:=542;
3658: if(regid_proc = true) then
3659: insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 542 and appl.party_id=temp.party_id) and
3660:
3661: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3662: xx
3663: where (nvl(STATUS, 'A') in ('A','I'))
3664: and xx.PARTY_SITE_ID
3665: in (select /*+ parallel(yy)*/ yy.INSTALL_LOCATION_ID from CSI_ITEM_INSTANCES

Line 3670: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3666: yy
3667: ));
3668: else
3669: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3670: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3671: xx
3672: where (nvl(STATUS, 'A') in ('A','I'))
3673: and xx.PARTY_SITE_ID
3674: in (select /*+ parallel(yy)*/ yy.INSTALL_LOCATION_ID from CSI_ITEM_INSTANCES

Line 3683: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3679: appid:=542;
3680: if(regid_proc = true) then
3681: insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 542 and appl.party_id=temp.party_id) and
3682:
3683: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3684: xx
3685: where (nvl(STATUS, 'A') in ('A','I'))
3686: and xx.PARTY_SITE_ID
3687: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CSI_T_TXN_SYSTEMS

Line 3692: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3688: yy
3689: ));
3690: else
3691: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3692: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3693: xx
3694: where (nvl(STATUS, 'A') in ('A','I'))
3695: and xx.PARTY_SITE_ID
3696: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CSI_T_TXN_SYSTEMS

Line 3741: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3737: appid:=697;
3738: if(regid_proc = true) then
3739: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
3740:
3741: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3742: xx
3743: where (nvl(STATUS, 'A') in ('A','I'))
3744: and xx.PARTY_SITE_ID
3745: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL

Line 3750: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3746: yy
3747: ));
3748: else
3749: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3750: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3751: xx
3752: where (nvl(STATUS, 'A') in ('A','I'))
3753: and xx.PARTY_SITE_ID
3754: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL

Line 3763: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3759: appid:=697;
3760: if(regid_proc = true) then
3761: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
3762:
3763: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3764: xx
3765: where (nvl(STATUS, 'A') in ('A','I'))
3766: and xx.PARTY_SITE_ID
3767: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL

Line 3772: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3768: yy
3769: ));
3770: else
3771: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3772: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3773: xx
3774: where (nvl(STATUS, 'A') in ('A','I'))
3775: and xx.PARTY_SITE_ID
3776: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL

Line 3797: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3793: appid:=697;
3794: if(regid_proc = true) then
3795: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
3796:
3797: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3798: xx
3799: where (nvl(STATUS, 'A') in ('A','I'))
3800: and xx.PARTY_SITE_ID
3801: in (select /*+ parallel(yy)*/ yy.SOLD_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL

Line 3806: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3802: yy
3803: ));
3804: else
3805: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3806: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3807: xx
3808: where (nvl(STATUS, 'A') in ('A','I'))
3809: and xx.PARTY_SITE_ID
3810: in (select /*+ parallel(yy)*/ yy.SOLD_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL

Line 3867: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3863: appid:=697;
3864: if(regid_proc = true) then
3865: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
3866:
3867: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3868: xx
3869: where (nvl(STATUS, 'A') in ('A','I'))
3870: and xx.PARTY_SITE_ID
3871: in (select /*+ parallel(yy)*/ yy.END_CUSTOMER_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL

Line 3876: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3872: yy
3873: ));
3874: else
3875: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3876: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3877: xx
3878: where (nvl(STATUS, 'A') in ('A','I'))
3879: and xx.PARTY_SITE_ID
3880: in (select /*+ parallel(yy)*/ yy.END_CUSTOMER_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL

Line 3913: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3909: appid:=697;
3910: if(regid_proc = true) then
3911: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 697 and appl.party_id=temp.party_id) and
3912:
3913: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3914: xx
3915: where (nvl(STATUS, 'A') in ('A','I'))
3916: and xx.PARTY_SITE_ID
3917: in (select /*+ parallel(yy)*/ yy.END_CUSTOMER_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL

Line 3922: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

3918: yy
3919: ));
3920: else
3921: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3922: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3923: xx
3924: where (nvl(STATUS, 'A') in ('A','I'))
3925: and xx.PARTY_SITE_ID
3926: in (select /*+ parallel(yy)*/ yy.END_CUSTOMER_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL

Line 4019: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4015: appid:=279;
4016: if(regid_proc = true) then
4017: insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 279 and appl.party_id=temp.party_id) and
4018:
4019: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4020: xx
4021: where (nvl(STATUS, 'A') in ('A','I'))
4022: and xx.PARTY_SITE_ID
4023: in (select /*+ parallel(yy)*/ yy.ADDRESS_ID from AS_LEADS_LOG

Line 4028: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4024: yy
4025: ));
4026: else
4027: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4028: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4029: xx
4030: where (nvl(STATUS, 'A') in ('A','I'))
4031: and xx.PARTY_SITE_ID
4032: in (select /*+ parallel(yy)*/ yy.ADDRESS_ID from AS_LEADS_LOG

Line 4557: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4553: appid:=540;
4554: if(regid_proc = true) then
4555: insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
4556:
4557: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4558: xx
4559: where (nvl(STATUS, 'A') in ('A','I'))
4560: and to_char(xx.PARTY_SITE_ID)
4561: in (select /*+ parallel(yy)*/ yy.AGENT_SITE_ID from OKL_INS_POLICIES_B

Line 4566: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4562: yy
4563: where IPY_TYPE = 'THIRD_PARTY_POLICY'));
4564: else
4565: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4566: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4567: xx
4568: where (nvl(STATUS, 'A') in ('A','I'))
4569: and to_char(xx.PARTY_SITE_ID)
4570: in (select /*+ parallel(yy)*/ yy.AGENT_SITE_ID from OKL_INS_POLICIES_B

Line 4579: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4575: appid:=540;
4576: if(regid_proc = true) then
4577: insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
4578:
4579: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4580: xx
4581: where (nvl(STATUS, 'A') in ('A','I'))
4582: and xx.PARTY_SITE_ID
4583: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B

Line 4588: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4584: yy
4585: where IPY_TYPE = 'THIRD_PARTY_POLICY'));
4586: else
4587: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4588: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4589: xx
4590: where (nvl(STATUS, 'A') in ('A','I'))
4591: and xx.PARTY_SITE_ID
4592: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B

Line 4601: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4597: appid:=540;
4598: if(regid_proc = true) then
4599: insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 540 and appl.party_id=temp.party_id) and
4600:
4601: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4602: xx
4603: where (nvl(STATUS, 'A') in ('A','I'))
4604: and xx.PARTY_SITE_ID
4605: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B

Line 4610: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4606: yy
4607: where IPY_TYPE = 'THIRD_PARTY_POLICY'));
4608: else
4609: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4610: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4611: xx
4612: where (nvl(STATUS, 'A') in ('A','I'))
4613: and xx.PARTY_SITE_ID
4614: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B

Line 4815: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4811: appid:=690;
4812: if(regid_proc = true) then
4813: insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 690 and appl.party_id=temp.party_id) and
4814:
4815: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4816: xx
4817: where (nvl(STATUS, 'A') in ('A','I'))
4818: and xx.PARTY_SITE_ID
4819: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from JTF_TTY_NAMED_ACCTS

Line 4824: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4820: yy
4821: ));
4822: else
4823: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4824: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4825: xx
4826: where (nvl(STATUS, 'A') in ('A','I'))
4827: and xx.PARTY_SITE_ID
4828: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from JTF_TTY_NAMED_ACCTS

Line 4849: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4845: appid:=515;
4846: if(regid_proc = true) then
4847: insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 515 and appl.party_id=temp.party_id) and
4848:
4849: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4850: xx
4851: where (nvl(STATUS, 'A') in ('A','I'))
4852: and to_char(xx.PARTY_SITE_ID)
4853: in (select /*+ parallel(yy)*/ yy.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS

Line 4858: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4854: yy
4855: where (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
4856: else
4857: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4858: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4859: xx
4860: where (nvl(STATUS, 'A') in ('A','I'))
4861: and to_char(xx.PARTY_SITE_ID)
4862: in (select /*+ parallel(yy)*/ yy.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS

Line 4895: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4891: appid:=170;
4892: if(regid_proc = true) then
4893: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
4894:
4895: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4896: xx
4897: where (nvl(STATUS, 'A') in ('A','I'))
4898: and xx.PARTY_SITE_ID
4899: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_ALL_B

Line 4904: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4900: yy
4901: ));
4902: else
4903: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4904: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4905: xx
4906: where (nvl(STATUS, 'A') in ('A','I'))
4907: and xx.PARTY_SITE_ID
4908: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_ALL_B

Line 4917: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4913: appid:=170;
4914: if(regid_proc = true) then
4915: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
4916:
4917: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4918: xx
4919: where (nvl(STATUS, 'A') in ('A','I'))
4920: and xx.PARTY_SITE_ID
4921: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_AUDIT_B

Line 4926: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4922: yy
4923: ));
4924: else
4925: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4926: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4927: xx
4928: where (nvl(STATUS, 'A') in ('A','I'))
4929: and xx.PARTY_SITE_ID
4930: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_AUDIT_B

Line 4939: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4935: appid:=170;
4936: if(regid_proc = true) then
4937: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
4938:
4939: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4940: xx
4941: where (nvl(STATUS, 'A') in ('A','I'))
4942: and xx.PARTY_SITE_ID
4943: in (select /*+ parallel(yy)*/ yy.BILL_TO_SITE_ID from CS_INCIDENTS_ALL_B

Line 4948: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4944: yy
4945: ));
4946: else
4947: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4948: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4949: xx
4950: where (nvl(STATUS, 'A') in ('A','I'))
4951: and xx.PARTY_SITE_ID
4952: in (select /*+ parallel(yy)*/ yy.BILL_TO_SITE_ID from CS_INCIDENTS_ALL_B

Line 4961: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4957: appid:=170;
4958: if(regid_proc = true) then
4959: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
4960:
4961: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4962: xx
4963: where (nvl(STATUS, 'A') in ('A','I'))
4964: and xx.PARTY_SITE_ID
4965: in (select /*+ parallel(yy)*/ yy.SHIP_TO_SITE_ID from CS_INCIDENTS_ALL_B

Line 4970: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4966: yy
4967: ));
4968: else
4969: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4970: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4971: xx
4972: where (nvl(STATUS, 'A') in ('A','I'))
4973: and xx.PARTY_SITE_ID
4974: in (select /*+ parallel(yy)*/ yy.SHIP_TO_SITE_ID from CS_INCIDENTS_ALL_B

Line 4983: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4979: appid:=170;
4980: if(regid_proc = true) then
4981: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
4982:
4983: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4984: xx
4985: where (nvl(STATUS, 'A') in ('A','I'))
4986: and xx.PARTY_SITE_ID
4987: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_ID from CS_INCIDENTS_ALL_B

Line 4992: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

4988: yy
4989: ));
4990: else
4991: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4992: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4993: xx
4994: where (nvl(STATUS, 'A') in ('A','I'))
4995: and xx.PARTY_SITE_ID
4996: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_ID from CS_INCIDENTS_ALL_B

Line 5077: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5073: appid:=170;
5074: if(regid_proc = true) then
5075: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
5076:
5077: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5078: xx
5079: where (nvl(STATUS, 'A') in ('A','I'))
5080: and xx.PARTY_SITE_ID
5081: in (select /*+ parallel(yy)*/ yy.CUSTOMER_SITE_ID from CS_INCIDENTS_ALL_B

Line 5086: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5082: yy
5083: ));
5084: else
5085: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5086: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5087: xx
5088: where (nvl(STATUS, 'A') in ('A','I'))
5089: and xx.PARTY_SITE_ID
5090: in (select /*+ parallel(yy)*/ yy.CUSTOMER_SITE_ID from CS_INCIDENTS_ALL_B

Line 5099: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5095: appid:=170;
5096: if(regid_proc = true) then
5097: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
5098:
5099: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5100: xx
5101: where (nvl(STATUS, 'A') in ('A','I'))
5102: and xx.PARTY_SITE_ID
5103: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CS_INCIDENTS_ALL_B

Line 5108: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5104: yy
5105: ));
5106: else
5107: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5108: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5109: xx
5110: where (nvl(STATUS, 'A') in ('A','I'))
5111: and xx.PARTY_SITE_ID
5112: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CS_INCIDENTS_ALL_B

Line 5121: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5117: appid:=170;
5118: if(regid_proc = true) then
5119: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 170 and appl.party_id=temp.party_id) and
5120:
5121: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5122: xx
5123: where (nvl(STATUS, 'A') in ('A','I'))
5124: and xx.PARTY_SITE_ID
5125: in (select /*+ parallel(yy)*/ yy.INCIDENT_LOCATION_ID from CS_INCIDENTS_ALL_B

Line 5130: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5126: yy
5127: ));
5128: else
5129: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5130: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5131: xx
5132: where (nvl(STATUS, 'A') in ('A','I'))
5133: and xx.PARTY_SITE_ID
5134: in (select /*+ parallel(yy)*/ yy.INCIDENT_LOCATION_ID from CS_INCIDENTS_ALL_B

Line 5143: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5139: appid:=174;
5140: if(regid_proc = true) then
5141: insert into hz_application_trans_gt(app_id,party_id) select 174, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 174 and appl.party_id=temp.party_id) and
5142:
5143: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5144: xx
5145: where (nvl(STATUS, 'A') in ('A','I'))
5146: and xx.PARTY_SITE_ID
5147: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from ECX_TP_HEADERS

Line 5152: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5148: yy
5149: ));
5150: else
5151: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5152: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5153: xx
5154: where (nvl(STATUS, 'A') in ('A','I'))
5155: and xx.PARTY_SITE_ID
5156: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from ECX_TP_HEADERS

Line 5189: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5185: appid:=204;
5186: if(regid_proc = true) then
5187: insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 204 and appl.party_id=temp.party_id) and
5188:
5189: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5190: xx
5191: where (nvl(STATUS, 'A') in ('A','I'))
5192: and xx.PARTY_SITE_ID
5193: in (select /*+ parallel(yy)*/ yy.ISSUING_AUTHORITY_SITE_ID from XLE_REGISTRATIONS

Line 5198: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5194: yy
5195: ));
5196: else
5197: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5198: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5199: xx
5200: where (nvl(STATUS, 'A') in ('A','I'))
5201: and xx.PARTY_SITE_ID
5202: in (select /*+ parallel(yy)*/ yy.ISSUING_AUTHORITY_SITE_ID from XLE_REGISTRATIONS

Line 5211: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5207: appid:=204;
5208: if(regid_proc = true) then
5209: insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 204 and appl.party_id=temp.party_id) and
5210:
5211: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5212: xx
5213: where (nvl(STATUS, 'A') in ('A','I'))
5214: and xx.PARTY_SITE_ID
5215: in (select /*+ parallel(yy)*/ yy.AUTHORITY_SITE_ID from XLE_REG_FUNCTIONS

Line 5220: temp.party_id in (select /+ parallel (xx)/ xx.PARTY_ID from HZ_PARTY_SITES

5216: yy
5217: ));
5218: else
5219: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5220: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5221: xx
5222: where (nvl(STATUS, 'A') in ('A','I'))
5223: and xx.PARTY_SITE_ID
5224: in (select /*+ parallel(yy)*/ yy.AUTHORITY_SITE_ID from XLE_REG_FUNCTIONS