[Home] [Help]
12: single_party number;
13:
14:
15: cursor repopulate is
16: select party_id from hz_purge_gt;
17:
18:
19: BEGIN
20:
27: delete from hz_application_trans_gt;
28: open repopulate;
29: fetch repopulate into single_party;
30: close repopulate;
31: --delete and insert records into hz_purge_gt for an application
32: appid:=8404;
33: if(regid_proc = true) then
34: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where
35:
30: close repopulate;
31: --delete and insert records into hz_purge_gt for an application
32: appid:=8404;
33: if(regid_proc = true) then
34: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where
35:
36:
37: exists (select 'Y' from IGW_PROP_PERSONS_TCA_V xx where xx.PERSON_PARTY_ID = temp.party_id
38: );
36:
37: exists (select 'Y' from IGW_PROP_PERSONS_TCA_V xx where xx.PERSON_PARTY_ID = temp.party_id
38: );
39: else
40: delete from hz_purge_gt temp where
41:
42:
43: exists (select 'Y' from IGW_PROP_PERSONS_TCA_V xx where xx.PERSON_PARTY_ID = temp.party_id
44: );
44: );
45: end if;
46:
47:
48: --delete and insert records into hz_purge_gt for an application
49: appid:=691;
50: if(regid_proc = true) then
51: insert into hz_application_trans_gt(app_id,party_id) select 691, temp.party_id from hz_purge_gt temp where
52:
47:
48: --delete and insert records into hz_purge_gt for an application
49: appid:=691;
50: if(regid_proc = true) then
51: insert into hz_application_trans_gt(app_id,party_id) select 691, temp.party_id from hz_purge_gt temp where
52:
53:
54: exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_ID = temp.party_id
55: )
146:
147: or exists (select 'Y' from PV_PARTNER_ACCESSES xx where xx.PARTNER_ID = temp.party_id
148: );
149: else
150: delete from hz_purge_gt temp where
151:
152:
153: exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_ID = temp.party_id
154: )
247: );
248: end if;
249:
250:
251: --delete and insert records into hz_purge_gt for an application
252: appid:=665;
253: if(regid_proc = true) then
254: insert into hz_application_trans_gt(app_id,party_id) select 665, temp.party_id from hz_purge_gt temp where
255:
250:
251: --delete and insert records into hz_purge_gt for an application
252: appid:=665;
253: if(regid_proc = true) then
254: insert into hz_application_trans_gt(app_id,party_id) select 665, temp.party_id from hz_purge_gt temp where
255:
256:
257: exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
258: )
264: (select 'Y' from WSH_CARRIER_SITES
265: yy where yy.CARRIER_SITE_ID=xx.PARTY_SITE_ID
266: ));
267: else
268: delete from hz_purge_gt temp where
269:
270:
271: exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
272: )
280: ));
281: end if;
282:
283:
284: --delete and insert records into hz_purge_gt for an application
285: appid:=222;
286: if(regid_proc = true) then
287: insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where
288:
283:
284: --delete and insert records into hz_purge_gt for an application
285: appid:=222;
286: if(regid_proc = true) then
287: insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where
288:
289:
290: exists (select 'Y' from HZ_ORGANIZATION_PROFILES xx where xx.DISPLAYED_DUNS_PARTY_ID = temp.party_id
291: and (nvl(STATUS, 'A') in ('A','I')))
314: or exists (select 'Y' from HZ_RELATIONSHIPS xx where xx.SUBJECT_ID = temp.party_id
315: and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
316: AND directional_flag = 'F'));
317: else
318: delete from hz_purge_gt temp where
319:
320:
321: exists (select 'Y' from HZ_ORGANIZATION_PROFILES xx where xx.DISPLAYED_DUNS_PARTY_ID = temp.party_id
322: and (nvl(STATUS, 'A') in ('A','I')))
347: AND directional_flag = 'F'));
348: end if;
349:
350:
351: --delete and insert records into hz_purge_gt for an application
352: appid:=671;
353: if(regid_proc = true) then
354: insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where
355:
350:
351: --delete and insert records into hz_purge_gt for an application
352: appid:=671;
353: if(regid_proc = true) then
354: insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where
355:
356:
357: exists (select 'Y' from IBE_SH_SHP_LISTS_ALL xx where xx.PARTY_ID = temp.party_id
358: )
378:
379: or exists (select 'Y' from IBE_MSITE_PRTY_ACCSS xx where xx.PARTY_ID = temp.party_id
380: );
381: else
382: delete from hz_purge_gt temp where
383:
384:
385: exists (select 'Y' from IBE_SH_SHP_LISTS_ALL xx where xx.PARTY_ID = temp.party_id
386: )
408: );
409: end if;
410:
411:
412: --delete and insert records into hz_purge_gt for an application
413: appid:=682;
414: if(regid_proc = true) then
415: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where
416:
411:
412: --delete and insert records into hz_purge_gt for an application
413: appid:=682;
414: if(regid_proc = true) then
415: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where
416:
417:
418: exists (select 'Y' from OZF_CUST_TRD_PRFLS_ALL xx where xx.PARTY_ID = temp.party_id
419: )
444:
445: or exists (select 'Y' from OZF_REQUEST_HEADERS_ALL_B xx where xx.PARTNER_ID = temp.party_id
446: );
447: else
448: delete from hz_purge_gt temp where
449:
450:
451: exists (select 'Y' from OZF_CUST_TRD_PRFLS_ALL xx where xx.PARTY_ID = temp.party_id
452: )
479: );
480: end if;
481:
482:
483: --delete and insert records into hz_purge_gt for an application
484: appid:=695;
485: if(regid_proc = true) then
486: insert into hz_application_trans_gt(app_id,party_id) select 695, temp.party_id from hz_purge_gt temp where
487:
482:
483: --delete and insert records into hz_purge_gt for an application
484: appid:=695;
485: if(regid_proc = true) then
486: insert into hz_application_trans_gt(app_id,party_id) select 695, temp.party_id from hz_purge_gt temp where
487:
488:
489: exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
490: )
496: (select 'Y' from IEX_CASE_CONTACTS
497: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
498: ));
499: else
500: delete from hz_purge_gt temp where
501:
502:
503: exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
504: )
512: ));
513: end if;
514:
515:
516: --delete and insert records into hz_purge_gt for an application
517: appid:=8405;
518: if(regid_proc = true) then
519: insert into hz_application_trans_gt(app_id,party_id) select 8405, temp.party_id from hz_purge_gt temp where
520:
515:
516: --delete and insert records into hz_purge_gt for an application
517: appid:=8405;
518: if(regid_proc = true) then
519: insert into hz_application_trans_gt(app_id,party_id) select 8405, temp.party_id from hz_purge_gt temp where
520:
521:
522: exists (select 'Y' from HZ_EDUCATION
523: xx where xx.PARTY_ID = temp.party_id
595:
596: or exists (select 'Y' from IGP_AC_ACCOUNTS xx where xx.PARTY_ID = temp.party_id
597: );
598: else
599: delete from hz_purge_gt temp where
600:
601:
602: exists (select 'Y' from HZ_EDUCATION
603: xx where xx.PARTY_ID = temp.party_id
677: );
678: end if;
679:
680:
681: --delete and insert records into hz_purge_gt for an application
682: appid:=661;
683: if(regid_proc = true) then
684: insert into hz_application_trans_gt(app_id,party_id) select 661, temp.party_id from hz_purge_gt temp where
685:
680:
681: --delete and insert records into hz_purge_gt for an application
682: appid:=661;
683: if(regid_proc = true) then
684: insert into hz_application_trans_gt(app_id,party_id) select 661, temp.party_id from hz_purge_gt temp where
685:
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'))))
694: (select 'Y' from QP_QUALIFIERS
695: yy where yy.QUALIFIER_ATTR_VALUE=to_char(xx.PARTY_SITE_ID)
696: and (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
697: else
698: delete from hz_purge_gt temp where
699:
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'))))
710: and (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
711: end if;
712:
713:
714: --delete and insert records into hz_purge_gt for an application
715: appid:=694;
716: if(regid_proc = true) then
717: insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where
718:
713:
714: --delete and insert records into hz_purge_gt for an application
715: appid:=694;
716: if(regid_proc = true) then
717: insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where
718:
719:
720: exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
721: );
719:
720: exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
721: );
722: else
723: delete from hz_purge_gt temp where
724:
725:
726: exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
727: );
727: );
728: end if;
729:
730:
731: --delete and insert records into hz_purge_gt for an application
732: appid:=542;
733: if(regid_proc = true) then
734: insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where
735:
730:
731: --delete and insert records into hz_purge_gt for an application
732: appid:=542;
733: if(regid_proc = true) then
734: insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where
735:
736:
737: exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
738: )
790: (select 'Y' from CSI_SYSTEMS_B
791: yy where yy.INSTALL_SITE_USE_ID=xx.PARTY_SITE_ID
792: ));
793: else
794: delete from hz_purge_gt temp where
795:
796:
797: exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
798: )
852: ));
853: end if;
854:
855:
856: --delete and insert records into hz_purge_gt for an application
857: appid:=510;
858: if(regid_proc = true) then
859: insert into hz_application_trans_gt(app_id,party_id) select 510, temp.party_id from hz_purge_gt temp where
860:
855:
856: --delete and insert records into hz_purge_gt for an application
857: appid:=510;
858: if(regid_proc = true) then
859: insert into hz_application_trans_gt(app_id,party_id) select 510, temp.party_id from hz_purge_gt temp where
860:
861:
862: exists (select 'Y' from OKC_K_PARTY_ROLES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
863: 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')))
948: and nvl(STATUS, 'A') in ('A','I')
949: and exists
950: (select 'Y' from OKC_K_ITEMS 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'))));
951: else
952: delete from hz_purge_gt temp where
953:
954:
955: exists (select 'Y' from OKC_K_PARTY_ROLES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
956: 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')))
1043: (select 'Y' from OKC_K_ITEMS 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'))));
1044: end if;
1045:
1046:
1047: --delete and insert records into hz_purge_gt for an application
1048: appid:=697;
1049: if(regid_proc = true) then
1050: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where
1051:
1046:
1047: --delete and insert records into hz_purge_gt for an application
1048: appid:=697;
1049: if(regid_proc = true) then
1050: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where
1051:
1052:
1053: exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
1054: )
1063:
1064: or exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.CUST_PARTY_ID = temp.party_id
1065: );
1066: else
1067: delete from hz_purge_gt temp where
1068:
1069:
1070: exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
1071: )
1082: );
1083: end if;
1084:
1085:
1086: --delete and insert records into hz_purge_gt for an application
1087: appid:=777;
1088: if(regid_proc = true) then
1089: insert into hz_application_trans_gt(app_id,party_id) select 777, temp.party_id from hz_purge_gt temp where
1090:
1085:
1086: --delete and insert records into hz_purge_gt for an application
1087: appid:=777;
1088: if(regid_proc = true) then
1089: insert into hz_application_trans_gt(app_id,party_id) select 777, temp.party_id from hz_purge_gt temp where
1090:
1091:
1092: exists (select 'Y' from OKE_K_FUNDING_SOURCES_PM_HV xx where xx.K_PARTY_ID = temp.party_id
1093: )
1094:
1095: or exists (select 'Y' from OKE_K_FUNDING_SOURCES xx where xx.K_PARTY_ID = temp.party_id
1096: );
1097: else
1098: delete from hz_purge_gt temp where
1099:
1100:
1101: exists (select 'Y' from OKE_K_FUNDING_SOURCES_PM_HV xx where xx.K_PARTY_ID = temp.party_id
1102: )
1105: );
1106: end if;
1107:
1108:
1109: --delete and insert records into hz_purge_gt for an application
1110: appid:=867;
1111: if(regid_proc = true) then
1112: insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where
1113:
1108:
1109: --delete and insert records into hz_purge_gt for an application
1110: appid:=867;
1111: if(regid_proc = true) then
1112: insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where
1113:
1114:
1115: exists (select 'Y' from AHL_DOCUMENTS_B xx where xx.SOURCE_PARTY_ID = temp.party_id
1116: )
1123:
1124: or exists (select 'Y' from AHL_DOC_REVISIONS_B xx where xx.APPROVED_BY_PARTY_ID = temp.party_id
1125: );
1126: else
1127: delete from hz_purge_gt temp where
1128:
1129:
1130: exists (select 'Y' from AHL_DOCUMENTS_B xx where xx.SOURCE_PARTY_ID = temp.party_id
1131: )
1140: );
1141: end if;
1142:
1143:
1144: --delete and insert records into hz_purge_gt for an application
1145: appid:=279;
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:
1143:
1144: --delete and insert records into hz_purge_gt for an application
1145: appid:=279;
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
1462:
1463: or exists (select 'Y' from AS_AP_ACCOUNT_PLANS xx where xx.CUST_PARTY_ID = temp.party_id
1464: );
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
1783: );
1784: end if;
1785:
1786:
1787: --delete and insert records into hz_purge_gt for an application
1788: appid:=206;
1789: if(regid_proc = true) then
1790: insert into hz_application_trans_gt(app_id,party_id) select 206, temp.party_id from hz_purge_gt temp where
1791:
1786:
1787: --delete and insert records into hz_purge_gt for an application
1788: appid:=206;
1789: if(regid_proc = true) then
1790: insert into hz_application_trans_gt(app_id,party_id) select 206, temp.party_id from hz_purge_gt temp where
1791:
1792:
1793: exists (select 'Y' from LNS_LOAN_HEADERS_ALL xx where xx.PRIMARY_BORROWER_ID = temp.party_id
1794: )
1795:
1796: or exists (select 'Y' from LNS_PARTICIPANTS xx where xx.HZ_PARTY_ID = temp.party_id
1797: );
1798: else
1799: delete from hz_purge_gt temp where
1800:
1801:
1802: exists (select 'Y' from LNS_LOAN_HEADERS_ALL xx where xx.PRIMARY_BORROWER_ID = temp.party_id
1803: )
1806: );
1807: end if;
1808:
1809:
1810: --delete and insert records into hz_purge_gt for an application
1811: appid:=275;
1812: if(regid_proc = true) then
1813: insert into hz_application_trans_gt(app_id,party_id) select 275, temp.party_id from hz_purge_gt temp where
1814:
1809:
1810: --delete and insert records into hz_purge_gt for an application
1811: appid:=275;
1812: if(regid_proc = true) then
1813: insert into hz_application_trans_gt(app_id,party_id) select 275, temp.party_id from hz_purge_gt temp where
1814:
1815:
1816: exists (select 'Y' from PA_CI_ACTIONS xx where xx.ASSIGNED_TO = temp.party_id
1817: )
1839:
1840: or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.CLOSED_BY_ID = temp.party_id
1841: );
1842: else
1843: delete from hz_purge_gt temp where
1844:
1845:
1846: exists (select 'Y' from PA_CI_ACTIONS xx where xx.ASSIGNED_TO = temp.party_id
1847: )
1871: );
1872: end if;
1873:
1874:
1875: --delete and insert records into hz_purge_gt for an application
1876: appid:=451;
1877: if(regid_proc = true) then
1878: insert into hz_application_trans_gt(app_id,party_id) select 451, temp.party_id from hz_purge_gt temp where
1879:
1874:
1875: --delete and insert records into hz_purge_gt for an application
1876: appid:=451;
1877: if(regid_proc = true) then
1878: insert into hz_application_trans_gt(app_id,party_id) select 451, temp.party_id from hz_purge_gt temp where
1879:
1880:
1881: exists (select 'Y' from OPI_DBI_COGS_F xx where xx.CUSTOMER_ID = temp.party_id
1882: );
1880:
1881: exists (select 'Y' from OPI_DBI_COGS_F xx where xx.CUSTOMER_ID = temp.party_id
1882: );
1883: else
1884: delete from hz_purge_gt temp where
1885:
1886:
1887: exists (select 'Y' from OPI_DBI_COGS_F xx where xx.CUSTOMER_ID = temp.party_id
1888: );
1888: );
1889: end if;
1890:
1891:
1892: --delete and insert records into hz_purge_gt for an application
1893: appid:=396;
1894: if(regid_proc = true) then
1895: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where
1896:
1891:
1892: --delete and insert records into hz_purge_gt for an application
1893: appid:=396;
1894: if(regid_proc = true) then
1895: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where
1896:
1897:
1898: exists (select 'Y' from PON_TE_RECIPIENTS xx where xx.TO_ID = temp.party_id
1899: )
1930:
1931: or exists (select 'Y' from PON_BID_HEADERS xx where xx.TRADING_PARTNER_ID = temp.party_id
1932: );
1933: else
1934: delete from hz_purge_gt temp where
1935:
1936:
1937: exists (select 'Y' from PON_TE_RECIPIENTS xx where xx.TO_ID = temp.party_id
1938: )
1971: );
1972: end if;
1973:
1974:
1975: --delete and insert records into hz_purge_gt for an application
1976: appid:=540;
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:
1974:
1975: --delete and insert records into hz_purge_gt for an application
1976: appid:=540;
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
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
2028:
2029:
2030: exists (select 'Y' from HZ_PARTY_SITES
2031: xx where xx.PARTY_ID = temp.party_id
2074: (select 'Y' from OKL_RELOCATE_ASSETS_B zz where zz.PAC_ID = yy.CONTACT_POINT_ID )));
2075: end if;
2076:
2077:
2078: --delete and insert records into hz_purge_gt for an application
2079: appid:=530;
2080: if(regid_proc = true) then
2081: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where
2082:
2077:
2078: --delete and insert records into hz_purge_gt for an application
2079: appid:=530;
2080: if(regid_proc = true) then
2081: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where
2082:
2083:
2084: exists (select 'Y' from AMS_PARTY_SOURCES xx where xx.PARTY_ID = temp.party_id
2085: )
2128:
2129: or exists (select 'Y' from AMS_TCOP_CONTACT_SUMMARY xx where xx.PARTY_ID = temp.party_id
2130: );
2131: else
2132: delete from hz_purge_gt temp where
2133:
2134:
2135: exists (select 'Y' from AMS_PARTY_SOURCES xx where xx.PARTY_ID = temp.party_id
2136: )
2181: );
2182: end if;
2183:
2184:
2185: --delete and insert records into hz_purge_gt for an application
2186: appid:=511;
2187: if(regid_proc = true) then
2188: insert into hz_application_trans_gt(app_id,party_id) select 511, temp.party_id from hz_purge_gt temp where
2189:
2184:
2185: --delete and insert records into hz_purge_gt for an application
2186: appid:=511;
2187: if(regid_proc = true) then
2188: insert into hz_application_trans_gt(app_id,party_id) select 511, temp.party_id from hz_purge_gt temp where
2189:
2190:
2191: exists (select 'Y' from CSC_CUSTOMERS xx where xx.PARTY_ID = temp.party_id
2192: )
2193:
2194: or exists (select 'Y' from CSC_CUSTOMIZED_PLANS xx where xx.PARTY_ID = temp.party_id
2195: );
2196: else
2197: delete from hz_purge_gt temp where
2198:
2199:
2200: exists (select 'Y' from CSC_CUSTOMERS xx where xx.PARTY_ID = temp.party_id
2201: )
2204: );
2205: end if;
2206:
2207:
2208: --delete and insert records into hz_purge_gt for an application
2209: appid:=454;
2210: if(regid_proc = true) then
2211: insert into hz_application_trans_gt(app_id,party_id) select 454, temp.party_id from hz_purge_gt temp where
2212:
2207:
2208: --delete and insert records into hz_purge_gt for an application
2209: appid:=454;
2210: if(regid_proc = true) then
2211: insert into hz_application_trans_gt(app_id,party_id) select 454, temp.party_id from hz_purge_gt temp where
2212:
2213:
2214: exists (select 'Y' from ISC_BOOK_SUM2_PDUE_F xx where xx.CUSTOMER_ID = temp.party_id
2215: )
2233:
2234: or exists (select 'Y' from ISC_FS_TASKS_F xx where xx.CUSTOMER_ID = temp.party_id
2235: );
2236: else
2237: delete from hz_purge_gt temp where
2238:
2239:
2240: exists (select 'Y' from ISC_BOOK_SUM2_PDUE_F xx where xx.CUSTOMER_ID = temp.party_id
2241: )
2261: );
2262: end if;
2263:
2264:
2265: --delete and insert records into hz_purge_gt for an application
2266: appid:=690;
2267: if(regid_proc = true) then
2268: insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where
2269:
2264:
2265: --delete and insert records into hz_purge_gt for an application
2266: appid:=690;
2267: if(regid_proc = true) then
2268: insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where
2269:
2270:
2271: exists (select 'Y' from JTF_TASKS_B xx where xx.CUSTOMER_ID = temp.party_id
2272: )
2450:
2451: or exists (select 'Y' from JTF_TTY_NAMED_ACCTS xx where xx.PARTY_ID = temp.party_id
2452: );
2453: else
2454: delete from hz_purge_gt temp where
2455:
2456:
2457: exists (select 'Y' from JTF_TASKS_B xx where xx.CUSTOMER_ID = temp.party_id
2458: )
2638: );
2639: end if;
2640:
2641:
2642: --delete and insert records into hz_purge_gt for an application
2643: appid:=515;
2644: if(regid_proc = true) then
2645: insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where
2646:
2641:
2642: --delete and insert records into hz_purge_gt for an application
2643: appid:=515;
2644: if(regid_proc = true) then
2645: insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where
2646:
2647:
2648: exists (select 'Y' from OKS_BILLING_PROFILES_B xx where xx.OWNED_PARTY_ID1 = to_char(temp.party_id)
2649: )
2653:
2654: or exists (select 'Y' from OKS_SERV_AVAIL_EXCEPTS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
2655: and (JTOT_OBJECT1_CODE = 'OKX_PARTY'));
2656: else
2657: delete from hz_purge_gt temp where
2658:
2659:
2660: exists (select 'Y' from OKS_BILLING_PROFILES_B xx where xx.OWNED_PARTY_ID1 = to_char(temp.party_id)
2661: )
2667: and (JTOT_OBJECT1_CODE = 'OKX_PARTY'));
2668: end if;
2669:
2670:
2671: --delete and insert records into hz_purge_gt for an application
2672: appid:=0;
2673: if(regid_proc = true) then
2674: insert into hz_application_trans_gt(app_id,party_id) select 0, temp.party_id from hz_purge_gt temp where
2675:
2670:
2671: --delete and insert records into hz_purge_gt for an application
2672: appid:=0;
2673: if(regid_proc = true) then
2674: insert into hz_application_trans_gt(app_id,party_id) select 0, temp.party_id from hz_purge_gt temp where
2675:
2676:
2677: exists (select 'Y' from FND_USER xx where xx.CUSTOMER_ID = temp.party_id
2678: )
2682:
2683: or exists (select 'Y' from UMX_REG_REQUESTS xx where xx.REQUESTED_FOR_PARTY_ID = temp.party_id
2684: and (ROWNUM < 2));
2685: else
2686: delete from hz_purge_gt temp where
2687:
2688:
2689: exists (select 'Y' from FND_USER xx where xx.CUSTOMER_ID = temp.party_id
2690: )
2696: and (ROWNUM < 2));
2697: end if;
2698:
2699:
2700: --delete and insert records into hz_purge_gt for an application
2701: appid:=170;
2702: if(regid_proc = true) then
2703: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where
2704:
2699:
2700: --delete and insert records into hz_purge_gt for an application
2701: appid:=170;
2702: if(regid_proc = true) then
2703: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where
2704:
2705:
2706: exists (select 'Y' from CS_INCIDENTS_ALL_B xx where xx.CUSTOMER_ID = temp.party_id
2707: )
2724: (select 'Y' from CS_ESTIMATE_DETAILS
2725: yy where yy.SHIP_TO_ORG_ID=xx.PARTY_SITE_ID
2726: ));
2727: else
2728: delete from hz_purge_gt temp where
2729:
2730:
2731: exists (select 'Y' from CS_INCIDENTS_ALL_B xx where xx.CUSTOMER_ID = temp.party_id
2732: )
2751: ));
2752: end if;
2753:
2754:
2755: --delete and insert records into hz_purge_gt for an application
2756: appid:=660;
2757: if(regid_proc = true) then
2758: insert into hz_application_trans_gt(app_id,party_id) select 660, temp.party_id from hz_purge_gt temp where
2759:
2754:
2755: --delete and insert records into hz_purge_gt for an application
2756: appid:=660;
2757: if(regid_proc = true) then
2758: insert into hz_application_trans_gt(app_id,party_id) select 660, temp.party_id from hz_purge_gt temp where
2759:
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')))
2768: (select 'Y' from OE_PRICE_ADJ_ATTRIBS
2769: yy where yy.PRICING_ATTR_VALUE_FROM=to_char(xx.PARTY_SITE_ID)
2770: and pricing_context = 'ASOPARTYINFO' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR pricing_context = 'CUSTOMER' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18')));
2771: else
2772: delete from hz_purge_gt temp where
2773:
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')))
2784: and pricing_context = 'ASOPARTYINFO' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR pricing_context = 'CUSTOMER' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18')));
2785: end if;
2786:
2787:
2788: --delete and insert records into hz_purge_gt for an application
2789: appid:=800;
2790: if(regid_proc = true) then
2791: insert into hz_application_trans_gt(app_id,party_id) select 800, temp.party_id from hz_purge_gt temp where
2792:
2787:
2788: --delete and insert records into hz_purge_gt for an application
2789: appid:=800;
2790: if(regid_proc = true) then
2791: insert into hz_application_trans_gt(app_id,party_id) select 800, temp.party_id from hz_purge_gt temp where
2792:
2793:
2794: exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
2795: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2793:
2794: exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
2795: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2796: else
2797: delete from hz_purge_gt temp where
2798:
2799:
2800: exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
2801: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2801: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2802: end if;
2803:
2804:
2805: --delete and insert records into hz_purge_gt for an application
2806: appid:=174;
2807: if(regid_proc = true) then
2808: insert into hz_application_trans_gt(app_id,party_id) select 174, temp.party_id from hz_purge_gt temp where
2809:
2804:
2805: --delete and insert records into hz_purge_gt for an application
2806: appid:=174;
2807: if(regid_proc = true) then
2808: insert into hz_application_trans_gt(app_id,party_id) select 174, temp.party_id from hz_purge_gt temp where
2809:
2810:
2811: exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
2812: );
2810:
2811: exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
2812: );
2813: else
2814: delete from hz_purge_gt temp where
2815:
2816:
2817: exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
2818: );
2818: );
2819: end if;
2820:
2821:
2822: --delete and insert records into hz_purge_gt for an application
2823: appid:=204;
2824: if(regid_proc = true) then
2825: insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where
2826:
2821:
2822: --delete and insert records into hz_purge_gt for an application
2823: appid:=204;
2824: if(regid_proc = true) then
2825: insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where
2826:
2827:
2828: exists (select 'Y' from XLE_ENTITY_PROFILES xx where xx.PARTY_ID = temp.party_id
2829: )
2830:
2831: or exists (select 'Y' from XLE_ETB_PROFILES xx where xx.PARTY_ID = temp.party_id
2832: );
2833: else
2834: delete from hz_purge_gt temp where
2835:
2836:
2837: exists (select 'Y' from XLE_ENTITY_PROFILES xx where xx.PARTY_ID = temp.party_id
2838: )
2841: );
2842: end if;
2843:
2844:
2845: --delete and insert records into hz_purge_gt for an application
2846: appid:=666;
2847: if(regid_proc = true) then
2848: insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where
2849:
2844:
2845: --delete and insert records into hz_purge_gt for an application
2846: appid:=666;
2847: if(regid_proc = true) then
2848: insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where
2849:
2850:
2851: exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
2852: );
2850:
2851: exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
2852: );
2853: else
2854: delete from hz_purge_gt temp where
2855:
2856:
2857: exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
2858: );
2861:
2862: --IGW_PROP_LOCATIONS;PARTY_ID
2863: appid:=8404;
2864: if(regid_proc = true) then
2865: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8404 and appl.party_id=temp.party_id) and
2866:
2867: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PROP_LOCATIONS xx
2868: );
2869: else
2866:
2867: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PROP_LOCATIONS xx
2868: );
2869: else
2870: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2871: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PROP_LOCATIONS xx
2872: );
2873: end if;
2874: --IGW_PERSON_DEGREES;PARTY_ID
2873: end if;
2874: --IGW_PERSON_DEGREES;PARTY_ID
2875: appid:=8404;
2876: if(regid_proc = true) then
2877: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8404 and appl.party_id=temp.party_id) and
2878:
2879: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PERSON_DEGREES xx
2880: );
2881: else
2878:
2879: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PERSON_DEGREES xx
2880: );
2881: else
2882: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2883: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PERSON_DEGREES xx
2884: );
2885: end if;
2886: --IGW_PERSON_BIOSKETCH;PARTY_ID
2885: end if;
2886: --IGW_PERSON_BIOSKETCH;PARTY_ID
2887: appid:=8404;
2888: if(regid_proc = true) then
2889: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8404 and appl.party_id=temp.party_id) and
2890:
2891: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PERSON_BIOSKETCH xx
2892: );
2893: else
2890:
2891: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PERSON_BIOSKETCH xx
2892: );
2893: else
2894: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2895: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGW_PERSON_BIOSKETCH xx
2896: );
2897: end if;
2898: --IGW_PROP_PERSONS_TCA_V;ORG_PARTY_ID
2897: end if;
2898: --IGW_PROP_PERSONS_TCA_V;ORG_PARTY_ID
2899: appid:=8404;
2900: if(regid_proc = true) then
2901: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8404 and appl.party_id=temp.party_id) and
2902:
2903: temp.party_id in (select /*+ parallel(xx)*/ xx.ORG_PARTY_ID from IGW_PROP_PERSONS_TCA_V xx
2904: );
2905: else
2902:
2903: temp.party_id in (select /*+ parallel(xx)*/ xx.ORG_PARTY_ID from IGW_PROP_PERSONS_TCA_V xx
2904: );
2905: else
2906: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2907: temp.party_id in (select /*+ parallel(xx)*/ xx.ORG_PARTY_ID from IGW_PROP_PERSONS_TCA_V xx
2908: );
2909: end if;
2910: --IGW_PROP_PERSON_SUPPORT;LOCATION_PARTY_ID
2909: end if;
2910: --IGW_PROP_PERSON_SUPPORT;LOCATION_PARTY_ID
2911: appid:=8404;
2912: if(regid_proc = true) then
2913: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8404 and appl.party_id=temp.party_id) and
2914:
2915: temp.party_id in (select /*+ parallel(xx)*/ xx.LOCATION_PARTY_ID from IGW_PROP_PERSON_SUPPORT xx
2916: );
2917: else
2914:
2915: temp.party_id in (select /*+ parallel(xx)*/ xx.LOCATION_PARTY_ID from IGW_PROP_PERSON_SUPPORT xx
2916: );
2917: else
2918: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2919: temp.party_id in (select /*+ parallel(xx)*/ xx.LOCATION_PARTY_ID from IGW_PROP_PERSON_SUPPORT xx
2920: );
2921: end if;
2922: --IGW_PROP_PERSON_SUPPORT;PI_PARTY_ID
2921: end if;
2922: --IGW_PROP_PERSON_SUPPORT;PI_PARTY_ID
2923: appid:=8404;
2924: if(regid_proc = true) then
2925: insert into hz_application_trans_gt(app_id,party_id) select 8404, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8404 and appl.party_id=temp.party_id) and
2926:
2927: temp.party_id in (select /*+ parallel(xx)*/ xx.PI_PARTY_ID from IGW_PROP_PERSON_SUPPORT xx
2928: );
2929: else
2926:
2927: temp.party_id in (select /*+ parallel(xx)*/ xx.PI_PARTY_ID from IGW_PROP_PERSON_SUPPORT xx
2928: );
2929: else
2930: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2931: temp.party_id in (select /*+ parallel(xx)*/ xx.PI_PARTY_ID from IGW_PROP_PERSON_SUPPORT xx
2932: );
2933: end if;
2934: --WSH_PARTY_SITES_V;PARTY_SITE_ID
2933: end if;
2934: --WSH_PARTY_SITES_V;PARTY_SITE_ID
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'))
2943: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from WSH_PARTY_SITES_V
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
2955: end if;
2956: --IGF_AP_FA_BASE_REC_ALL;PERSON_ID
2957: appid:=8406;
2958: if(regid_proc = true) then
2959: insert into hz_application_trans_gt(app_id,party_id) select 8406, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8406 and appl.party_id=temp.party_id) and
2960:
2961: temp.party_id in (select /*+ parallel(xx)*/ xx.PERSON_ID from IGF_AP_FA_BASE_REC_ALL xx
2962: );
2963: else
2960:
2961: temp.party_id in (select /*+ parallel(xx)*/ xx.PERSON_ID from IGF_AP_FA_BASE_REC_ALL xx
2962: );
2963: else
2964: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2965: temp.party_id in (select /*+ parallel(xx)*/ xx.PERSON_ID from IGF_AP_FA_BASE_REC_ALL xx
2966: );
2967: end if;
2968: --IGF_AW_FUND_MAST_ALL;PARTY_ID
2967: end if;
2968: --IGF_AW_FUND_MAST_ALL;PARTY_ID
2969: appid:=8406;
2970: if(regid_proc = true) then
2971: insert into hz_application_trans_gt(app_id,party_id) select 8406, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8406 and appl.party_id=temp.party_id) and
2972:
2973: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGF_AW_FUND_MAST_ALL xx
2974: );
2975: else
2972:
2973: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGF_AW_FUND_MAST_ALL xx
2974: );
2975: else
2976: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2977: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGF_AW_FUND_MAST_ALL xx
2978: );
2979: end if;
2980: --HZ_CODE_ASSIGNMENTS;OWNER_TABLE_ID
2979: end if;
2980: --HZ_CODE_ASSIGNMENTS;OWNER_TABLE_ID
2981: appid:=222;
2982: if(regid_proc = true) then
2983: 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
2984:
2985: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_TABLE_ID from HZ_CODE_ASSIGNMENTS xx
2986: where (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I')));
2987: else
2984:
2985: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_TABLE_ID from HZ_CODE_ASSIGNMENTS xx
2986: where (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I')));
2987: else
2988: delete /*+ parallel(temp) */ from hz_purge_gt temp where
2989: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_TABLE_ID from HZ_CODE_ASSIGNMENTS xx
2990: where (OWNER_TABLE_NAME='HZ_PARTIES' AND nvl(STATUS, 'A') in ('A','I')));
2991: end if;
2992: --HZ_CODE_ASSIGNMENTS;OWNER_TABLE_ID
2991: end if;
2992: --HZ_CODE_ASSIGNMENTS;OWNER_TABLE_ID
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'))
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
3008: where (nvl(STATUS, 'A') in ('A','I'))
3009: and xx.PARTY_SITE_ID
3013: end if;
3014: --HZ_WORK_CLASS;EMPLOYMENT_HISTORY_ID
3015: appid:=222;
3016: if(regid_proc = true) then
3017: 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
3018:
3019: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_EMPLOYMENT_HISTORY
3020: xx
3021: where (nvl(STATUS, 'A') in ('A','I'))
3030: in (select /*+ parallel(yy)*/ yy.EMPLOYMENT_HISTORY_ID from HZ_WORK_CLASS
3031: yy
3032: where nvl(STATUS, 'A') in ('A','I')));
3033: else
3034: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3035: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_EMPLOYMENT_HISTORY
3036: xx
3037: where (nvl(STATUS, 'A') in ('A','I'))
3038: and xx.EMPLOYMENT_HISTORY_ID
3049: end if;
3050: --FND_ATTACHED_DOCUMENTS;PK1_VALUE
3051: appid:=222;
3052: if(regid_proc = true) then
3053: 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
3054:
3055: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PK1_VALUE from FND_ATTACHED_DOCUMENTS xx
3056: where (entity_name = 'HZ_PARTIES'));
3057: else
3054:
3055: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PK1_VALUE from FND_ATTACHED_DOCUMENTS xx
3056: where (entity_name = 'HZ_PARTIES'));
3057: else
3058: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3059: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PK1_VALUE from FND_ATTACHED_DOCUMENTS xx
3060: where (entity_name = 'HZ_PARTIES'));
3061: end if;
3062: --HZ_EMAIL_DOMAINS;PARTY_ID
3061: end if;
3062: --HZ_EMAIL_DOMAINS;PARTY_ID
3063: appid:=222;
3064: if(regid_proc = true) then
3065: 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
3066:
3067: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
3068: );
3069: else
3066:
3067: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
3068: );
3069: else
3070: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3071: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
3072: );
3073: end if;
3074: --IBE_SH_QUOTE_ACCESS;PARTY_ID
3073: end if;
3074: --IBE_SH_QUOTE_ACCESS;PARTY_ID
3075: appid:=671;
3076: if(regid_proc = true) then
3077: insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 671 and appl.party_id=temp.party_id) and
3078:
3079: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
3080: );
3081: else
3078:
3079: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
3080: );
3081: else
3082: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3083: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
3084: );
3085: end if;
3086: --IBE_ACTIVE_QUOTES_ALL;PARTY_ID
3085: end if;
3086: --IBE_ACTIVE_QUOTES_ALL;PARTY_ID
3087: appid:=671;
3088: if(regid_proc = true) then
3089: insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 671 and appl.party_id=temp.party_id) and
3090:
3091: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
3092: );
3093: else
3090:
3091: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
3092: );
3093: else
3094: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3095: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
3096: );
3097: end if;
3098: --OZF_CLAIMS_HISTORY_ALL;BROKER_ID
3097: end if;
3098: --OZF_CLAIMS_HISTORY_ALL;BROKER_ID
3099: appid:=682;
3100: if(regid_proc = true) then
3101: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3102:
3103: temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
3104: );
3105: else
3102:
3103: temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
3104: );
3105: else
3106: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3107: temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
3108: );
3109: end if;
3110: --OZF_CLAIMS_HISTORY_ALL;CONTACT_ID
3109: end if;
3110: --OZF_CLAIMS_HISTORY_ALL;CONTACT_ID
3111: appid:=682;
3112: if(regid_proc = true) then
3113: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3114:
3115: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
3116: );
3117: else
3114:
3115: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
3116: );
3117: else
3118: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3119: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
3120: );
3121: end if;
3122: --OZF_RESALE_LINES_INT_ALL;SHIP_FROM_CONTACT_PARTY_ID
3121: end if;
3122: --OZF_RESALE_LINES_INT_ALL;SHIP_FROM_CONTACT_PARTY_ID
3123: appid:=682;
3124: if(regid_proc = true) then
3125: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3126:
3127: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3128: );
3129: else
3126:
3127: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3128: );
3129: else
3130: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3131: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3132: );
3133: end if;
3134: --OZF_RESALE_LINES_INT_ALL;SOLD_FROM_CONTACT_PARTY_ID
3133: end if;
3134: --OZF_RESALE_LINES_INT_ALL;SOLD_FROM_CONTACT_PARTY_ID
3135: appid:=682;
3136: if(regid_proc = true) then
3137: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3138:
3139: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3140: );
3141: else
3138:
3139: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3140: );
3141: else
3142: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3143: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3144: );
3145: end if;
3146: --OZF_RESALE_LINES_INT_ALL;BILL_TO_PARTY_ID
3145: end if;
3146: --OZF_RESALE_LINES_INT_ALL;BILL_TO_PARTY_ID
3147: appid:=682;
3148: if(regid_proc = true) then
3149: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3150:
3151: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3152: );
3153: else
3150:
3151: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3152: );
3153: else
3154: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3155: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3156: );
3157: end if;
3158: --OZF_RESALE_LINES_INT_ALL;BILL_TO_CONTACT_PARTY_ID
3157: end if;
3158: --OZF_RESALE_LINES_INT_ALL;BILL_TO_CONTACT_PARTY_ID
3159: appid:=682;
3160: if(regid_proc = true) then
3161: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3162:
3163: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3164: );
3165: else
3162:
3163: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3164: );
3165: else
3166: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3167: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3168: );
3169: end if;
3170: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_PARTY_ID
3169: end if;
3170: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_PARTY_ID
3171: appid:=682;
3172: if(regid_proc = true) then
3173: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3174:
3175: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3176: );
3177: else
3174:
3175: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3176: );
3177: else
3178: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3179: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3180: );
3181: end if;
3182: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_CONTACT_PARTY_ID
3181: end if;
3182: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_CONTACT_PARTY_ID
3183: appid:=682;
3184: if(regid_proc = true) then
3185: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3186:
3187: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3188: );
3189: else
3186:
3187: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3188: );
3189: else
3190: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3191: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3192: );
3193: end if;
3194: --OZF_RESALE_LINES_INT_ALL;END_CUST_PARTY_ID
3193: end if;
3194: --OZF_RESALE_LINES_INT_ALL;END_CUST_PARTY_ID
3195: appid:=682;
3196: if(regid_proc = true) then
3197: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3198:
3199: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3200: );
3201: else
3198:
3199: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3200: );
3201: else
3202: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3203: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3204: );
3205: end if;
3206: --OZF_RESALE_LINES_INT_ALL;END_CUST_CONTACT_PARTY_ID
3205: end if;
3206: --OZF_RESALE_LINES_INT_ALL;END_CUST_CONTACT_PARTY_ID
3207: appid:=682;
3208: if(regid_proc = true) then
3209: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3210:
3211: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3212: );
3213: else
3210:
3211: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3212: );
3213: else
3214: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3215: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3216: );
3217: end if;
3218: --OZF_RESALE_HEADERS_ALL;BILL_TO_PARTY_ID
3217: end if;
3218: --OZF_RESALE_HEADERS_ALL;BILL_TO_PARTY_ID
3219: appid:=682;
3220: if(regid_proc = true) then
3221: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3222:
3223: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3224: );
3225: else
3222:
3223: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3224: );
3225: else
3226: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3227: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3228: );
3229: end if;
3230: --OZF_RESALE_HEADERS_ALL;BILL_TO_CONTACT_PARTY_ID
3229: end if;
3230: --OZF_RESALE_HEADERS_ALL;BILL_TO_CONTACT_PARTY_ID
3231: appid:=682;
3232: if(regid_proc = true) then
3233: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3234:
3235: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3236: );
3237: else
3234:
3235: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3236: );
3237: else
3238: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3239: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3240: );
3241: end if;
3242: --OZF_RESALE_HEADERS_ALL;SHIP_TO_PARTY_ID
3241: end if;
3242: --OZF_RESALE_HEADERS_ALL;SHIP_TO_PARTY_ID
3243: appid:=682;
3244: if(regid_proc = true) then
3245: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3246:
3247: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3248: );
3249: else
3246:
3247: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3248: );
3249: else
3250: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3251: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3252: );
3253: end if;
3254: --OZF_RESALE_HEADERS_ALL;SHIP_TO_CONTACT_PARTY_ID
3253: end if;
3254: --OZF_RESALE_HEADERS_ALL;SHIP_TO_CONTACT_PARTY_ID
3255: appid:=682;
3256: if(regid_proc = true) then
3257: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3258:
3259: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3260: );
3261: else
3258:
3259: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3260: );
3261: else
3262: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3263: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3264: );
3265: end if;
3266: --OZF_RESALE_LINES_ALL;SHIP_FROM_CONTACT_PARTY_ID
3265: end if;
3266: --OZF_RESALE_LINES_ALL;SHIP_FROM_CONTACT_PARTY_ID
3267: appid:=682;
3268: if(regid_proc = true) then
3269: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3270:
3271: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3272: );
3273: else
3270:
3271: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3272: );
3273: else
3274: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3275: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3276: );
3277: end if;
3278: --OZF_RESALE_LINES_ALL;SOLD_FROM_CONTACT_PARTY_ID
3277: end if;
3278: --OZF_RESALE_LINES_ALL;SOLD_FROM_CONTACT_PARTY_ID
3279: appid:=682;
3280: if(regid_proc = true) then
3281: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3282:
3283: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3284: );
3285: else
3282:
3283: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3284: );
3285: else
3286: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3287: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3288: );
3289: end if;
3290: --OZF_RESALE_LINES_ALL;BILL_TO_PARTY_ID
3289: end if;
3290: --OZF_RESALE_LINES_ALL;BILL_TO_PARTY_ID
3291: appid:=682;
3292: if(regid_proc = true) then
3293: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3294:
3295: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3296: );
3297: else
3294:
3295: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3296: );
3297: else
3298: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3299: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3300: );
3301: end if;
3302: --OZF_RESALE_LINES_ALL;BILL_TO_CONTACT_PARTY_ID
3301: end if;
3302: --OZF_RESALE_LINES_ALL;BILL_TO_CONTACT_PARTY_ID
3303: appid:=682;
3304: if(regid_proc = true) then
3305: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3306:
3307: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3308: );
3309: else
3306:
3307: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3308: );
3309: else
3310: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3311: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3312: );
3313: end if;
3314: --OZF_RESALE_LINES_ALL;SHIP_TO_PARTY_ID
3313: end if;
3314: --OZF_RESALE_LINES_ALL;SHIP_TO_PARTY_ID
3315: appid:=682;
3316: if(regid_proc = true) then
3317: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3318:
3319: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3320: );
3321: else
3318:
3319: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3320: );
3321: else
3322: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3323: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3324: );
3325: end if;
3326: --OZF_RESALE_LINES_ALL;SHIP_TO_CONTACT_PARTY_ID
3325: end if;
3326: --OZF_RESALE_LINES_ALL;SHIP_TO_CONTACT_PARTY_ID
3327: appid:=682;
3328: if(regid_proc = true) then
3329: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3330:
3331: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3332: );
3333: else
3330:
3331: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3332: );
3333: else
3334: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3335: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3336: );
3337: end if;
3338: --OZF_RESALE_LINES_ALL;END_CUST_PARTY_ID
3337: end if;
3338: --OZF_RESALE_LINES_ALL;END_CUST_PARTY_ID
3339: appid:=682;
3340: if(regid_proc = true) then
3341: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3342:
3343: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
3344: );
3345: else
3342:
3343: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
3344: );
3345: else
3346: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3347: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
3348: );
3349: end if;
3350: --OZF_RESALE_LINES_ALL;END_CUST_CONTACT_PARTY_ID
3349: end if;
3350: --OZF_RESALE_LINES_ALL;END_CUST_CONTACT_PARTY_ID
3351: appid:=682;
3352: if(regid_proc = true) then
3353: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3354:
3355: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3356: );
3357: else
3354:
3355: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3356: );
3357: else
3358: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3359: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3360: );
3361: end if;
3362: --OZF_REQUEST_HEADERS_ALL_B;END_CUST_PARTY_ID
3361: end if;
3362: --OZF_REQUEST_HEADERS_ALL_B;END_CUST_PARTY_ID
3363: appid:=682;
3364: if(regid_proc = true) then
3365: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3366:
3367: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3368: );
3369: else
3366:
3367: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3368: );
3369: else
3370: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3371: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3372: );
3373: end if;
3374: --OZF_REQUEST_HEADERS_ALL_B;RESELLER_PARTY_ID
3373: end if;
3374: --OZF_REQUEST_HEADERS_ALL_B;RESELLER_PARTY_ID
3375: appid:=682;
3376: if(regid_proc = true) then
3377: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3378:
3379: temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3380: );
3381: else
3378:
3379: temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3380: );
3381: else
3382: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3383: temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3384: );
3385: end if;
3386: --OZF_OFFERS;AUTOPAY_PARTY_ID
3385: end if;
3386: --OZF_OFFERS;AUTOPAY_PARTY_ID
3387: appid:=682;
3388: if(regid_proc = true) then
3389: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3390:
3391: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
3392: );
3393: else
3390:
3391: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
3392: );
3393: else
3394: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3395: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
3396: );
3397: end if;
3398: --OZF_ACTIVITY_CUSTOMERS;PARTY_ID
3397: end if;
3398: --OZF_ACTIVITY_CUSTOMERS;PARTY_ID
3399: appid:=682;
3400: if(regid_proc = true) then
3401: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3402:
3403: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
3404: );
3405: else
3402:
3403: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
3404: );
3405: else
3406: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3407: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
3408: );
3409: end if;
3410: --OZF_CLAIM_LINES_HIST_ALL;BUY_GROUP_PARTY_ID
3409: end if;
3410: --OZF_CLAIM_LINES_HIST_ALL;BUY_GROUP_PARTY_ID
3411: appid:=682;
3412: if(regid_proc = true) then
3413: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3414:
3415: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
3416: );
3417: else
3414:
3415: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
3416: );
3417: else
3418: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3419: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
3420: );
3421: end if;
3422: --OZF_RESALE_BATCHES_ALL;PARTNER_CONTACT_PARTY_ID
3421: end if;
3422: --OZF_RESALE_BATCHES_ALL;PARTNER_CONTACT_PARTY_ID
3423: appid:=682;
3424: if(regid_proc = true) then
3425: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3426:
3427: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
3428: );
3429: else
3426:
3427: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
3428: );
3429: else
3430: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3431: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
3432: );
3433: end if;
3434: --OZF_CLAIM_LINES_ALL;BUY_GROUP_PARTY_ID
3433: end if;
3434: --OZF_CLAIM_LINES_ALL;BUY_GROUP_PARTY_ID
3435: appid:=682;
3436: if(regid_proc = true) then
3437: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3438:
3439: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
3440: );
3441: else
3438:
3439: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
3440: );
3441: else
3442: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3443: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
3444: );
3445: end if;
3446: --OZF_ACCOUNT_ALLOCATIONS;PARENT_PARTY_ID
3445: end if;
3446: --OZF_ACCOUNT_ALLOCATIONS;PARENT_PARTY_ID
3447: appid:=682;
3448: if(regid_proc = true) then
3449: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3450:
3451: temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3452: );
3453: else
3450:
3451: temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3452: );
3453: else
3454: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3455: temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3456: );
3457: end if;
3458: --OZF_ACCOUNT_ALLOCATIONS;ROLLUP_PARTY_ID
3457: end if;
3458: --OZF_ACCOUNT_ALLOCATIONS;ROLLUP_PARTY_ID
3459: appid:=682;
3460: if(regid_proc = true) then
3461: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 682 and appl.party_id=temp.party_id) and
3462:
3463: temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3464: );
3465: else
3462:
3463: temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3464: );
3465: else
3466: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3467: temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3468: );
3469: end if;
3470: --FUN_TRX_BATCHES;INITIATOR_ID
3469: end if;
3470: --FUN_TRX_BATCHES;INITIATOR_ID
3471: appid:=435;
3472: if(regid_proc = true) then
3473: 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
3474:
3475: temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
3476: );
3477: else
3474:
3475: temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
3476: );
3477: else
3478: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3479: temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
3480: );
3481: end if;
3482: --FUN_TRX_HEADERS;RECIPIENT_ID
3481: end if;
3482: --FUN_TRX_HEADERS;RECIPIENT_ID
3483: appid:=435;
3484: if(regid_proc = true) then
3485: 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
3486:
3487: temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
3488: );
3489: else
3486:
3487: temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
3488: );
3489: else
3490: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3491: temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
3492: );
3493: end if;
3494: --FUN_DIST_LINES;PARTY_ID
3493: end if;
3494: --FUN_DIST_LINES;PARTY_ID
3495: appid:=435;
3496: if(regid_proc = true) then
3497: 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
3498:
3499: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
3500: );
3501: else
3498:
3499: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
3500: );
3501: else
3502: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3503: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
3504: );
3505: end if;
3506: --FUN_SUPPLIER_MAPS;VENDOR_SITE_ID
3505: end if;
3506: --FUN_SUPPLIER_MAPS;VENDOR_SITE_ID
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'))
3515: in (select /*+ parallel(yy)*/ yy.VENDOR_SITE_ID from FUN_SUPPLIER_MAPS
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
3527: end if;
3528: --IGS_PE_STAT_DETAILS;PERSON_PROFILE_ID
3529: appid:=8405;
3530: if(regid_proc = true) then
3531: insert into hz_application_trans_gt(app_id,party_id) select 8405, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8405 and appl.party_id=temp.party_id) and
3532:
3533: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PERSON_PROFILES
3534: xx
3535: where (nvl(STATUS, 'A') in ('A','I') and effective_end_date is null)
3537: in (select /*+ parallel(yy)*/ yy.PERSON_PROFILE_ID from IGS_PE_STAT_DETAILS
3538: yy
3539: ));
3540: else
3541: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3542: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PERSON_PROFILES
3543: xx
3544: where (nvl(STATUS, 'A') in ('A','I') and effective_end_date is null)
3545: and xx.PERSON_PROFILE_ID
3549: end if;
3550: --IGS_FI_REFUNDS;PAY_PERSON_ID
3551: appid:=8405;
3552: if(regid_proc = true) then
3553: insert into hz_application_trans_gt(app_id,party_id) select 8405, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8405 and appl.party_id=temp.party_id) and
3554:
3555: temp.party_id in (select /*+ parallel(xx)*/ xx.PAY_PERSON_ID from IGS_FI_REFUNDS xx
3556: );
3557: else
3554:
3555: temp.party_id in (select /*+ parallel(xx)*/ xx.PAY_PERSON_ID from IGS_FI_REFUNDS xx
3556: );
3557: else
3558: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3559: temp.party_id in (select /*+ parallel(xx)*/ xx.PAY_PERSON_ID from IGS_FI_REFUNDS xx
3560: );
3561: end if;
3562: --IGP_AC_ACCOUNT_INTS;PARTY_ID
3561: end if;
3562: --IGP_AC_ACCOUNT_INTS;PARTY_ID
3563: appid:=8405;
3564: if(regid_proc = true) then
3565: insert into hz_application_trans_gt(app_id,party_id) select 8405, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8405 and appl.party_id=temp.party_id) and
3566:
3567: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGP_AC_ACCOUNT_INTS xx
3568: );
3569: else
3566:
3567: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGP_AC_ACCOUNT_INTS xx
3568: );
3569: else
3570: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3571: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IGP_AC_ACCOUNT_INTS xx
3572: );
3573: end if;
3574: --IGP_US_REG_VIEWERS;ORG_PARTY_ID
3573: end if;
3574: --IGP_US_REG_VIEWERS;ORG_PARTY_ID
3575: appid:=8405;
3576: if(regid_proc = true) then
3577: insert into hz_application_trans_gt(app_id,party_id) select 8405, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 8405 and appl.party_id=temp.party_id) and
3578:
3579: temp.party_id in (select /*+ parallel(xx)*/ xx.ORG_PARTY_ID from IGP_US_REG_VIEWERS xx
3580: );
3581: else
3578:
3579: temp.party_id in (select /*+ parallel(xx)*/ xx.ORG_PARTY_ID from IGP_US_REG_VIEWERS xx
3580: );
3581: else
3582: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3583: temp.party_id in (select /*+ parallel(xx)*/ xx.ORG_PARTY_ID from IGP_US_REG_VIEWERS xx
3584: );
3585: end if;
3586: --XDP_ORDER_HEADERS;CUSTOMER_ID
3585: end if;
3586: --XDP_ORDER_HEADERS;CUSTOMER_ID
3587: appid:=535;
3588: if(regid_proc = true) then
3589: insert into hz_application_trans_gt(app_id,party_id) select 535, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 535 and appl.party_id=temp.party_id) and
3590:
3591: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
3592: );
3593: else
3590:
3591: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
3592: );
3593: else
3594: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3595: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
3596: );
3597: end if;
3598: --QA_RESULTS;PARTY_ID
3597: end if;
3598: --QA_RESULTS;PARTY_ID
3599: appid:=250;
3600: if(regid_proc = true) then
3601: insert into hz_application_trans_gt(app_id,party_id) select 250, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 250 and appl.party_id=temp.party_id) and
3602:
3603: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
3604: );
3605: else
3602:
3603: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
3604: );
3605: else
3606: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3607: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
3608: );
3609: end if;
3610: --PRP_EMAIL_RECIPIENTS;PARTY_ID
3609: end if;
3610: --PRP_EMAIL_RECIPIENTS;PARTY_ID
3611: appid:=694;
3612: if(regid_proc = true) then
3613: insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 694 and appl.party_id=temp.party_id) and
3614:
3615: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
3616: );
3617: else
3614:
3615: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
3616: );
3617: else
3618: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3619: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
3620: );
3621: end if;
3622: --PRP_PROPOSALS;CONTACT_PARTY_ID
3621: end if;
3622: --PRP_PROPOSALS;CONTACT_PARTY_ID
3623: appid:=694;
3624: if(regid_proc = true) then
3625: insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 694 and appl.party_id=temp.party_id) and
3626:
3627: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
3628: );
3629: else
3626:
3627: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
3628: );
3629: else
3630: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3631: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
3632: );
3633: end if;
3634: --CSI_ITEM_INSTANCES;LOCATION_ID
3633: end if;
3634: --CSI_ITEM_INSTANCES;LOCATION_ID
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'))
3643: in (select /*+ parallel(yy)*/ yy.LOCATION_ID from CSI_ITEM_INSTANCES
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
3655: end if;
3656: --CSI_ITEM_INSTANCES;INSTALL_LOCATION_ID
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'))
3665: in (select /*+ parallel(yy)*/ yy.INSTALL_LOCATION_ID from CSI_ITEM_INSTANCES
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
3677: end if;
3678: --CSI_T_TXN_SYSTEMS;INSTALL_SITE_USE_ID
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'))
3687: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CSI_T_TXN_SYSTEMS
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
3699: end if;
3700: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_PARTY_ID
3701: appid:=697;
3702: if(regid_proc = true) then
3703: 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
3704:
3705: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3706: );
3707: else
3704:
3705: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3706: );
3707: else
3708: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3709: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3710: );
3711: end if;
3712: --ASO_QUOTE_LINES_ALL;INVOICE_TO_PARTY_ID
3711: end if;
3712: --ASO_QUOTE_LINES_ALL;INVOICE_TO_PARTY_ID
3713: appid:=697;
3714: if(regid_proc = true) then
3715: 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
3716:
3717: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3718: );
3719: else
3716:
3717: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3718: );
3719: else
3720: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3721: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3722: );
3723: end if;
3724: --ASO_SHIPMENTS;SHIP_TO_PARTY_ID
3723: end if;
3724: --ASO_SHIPMENTS;SHIP_TO_PARTY_ID
3725: appid:=697;
3726: if(regid_proc = true) then
3727: 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
3728:
3729: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
3730: );
3731: else
3728:
3729: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
3730: );
3731: else
3732: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3733: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
3734: );
3735: end if;
3736: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_PARTY_SITE_ID
3735: end if;
3736: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_PARTY_SITE_ID
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'))
3745: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
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
3757: end if;
3758: --ASO_QUOTE_LINES_ALL;INVOICE_TO_PARTY_SITE_ID
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'))
3767: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL
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
3779: end if;
3780: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_CUST_PARTY_ID
3781: appid:=697;
3782: if(regid_proc = true) then
3783: 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
3784:
3785: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3786: );
3787: else
3784:
3785: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3786: );
3787: else
3788: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3789: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3790: );
3791: end if;
3792: --ASO_QUOTE_HEADERS_ALL;SOLD_TO_PARTY_SITE_ID
3791: end if;
3792: --ASO_QUOTE_HEADERS_ALL;SOLD_TO_PARTY_SITE_ID
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'))
3801: in (select /*+ parallel(yy)*/ yy.SOLD_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
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
3813: end if;
3814: --ASO_QUOTE_LINES_ALL;INVOICE_TO_CUST_PARTY_ID
3815: appid:=697;
3816: if(regid_proc = true) then
3817: 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
3818:
3819: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3820: );
3821: else
3818:
3819: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3820: );
3821: else
3822: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3823: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3824: );
3825: end if;
3826: --ASO_SHIPMENTS;SHIP_TO_CUST_PARTY_ID
3825: end if;
3826: --ASO_SHIPMENTS;SHIP_TO_CUST_PARTY_ID
3827: appid:=697;
3828: if(regid_proc = true) then
3829: 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
3830:
3831: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
3832: );
3833: else
3830:
3831: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
3832: );
3833: else
3834: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3835: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
3836: );
3837: end if;
3838: --ASO_QUOTE_HEADERS_ALL;END_CUSTOMER_PARTY_ID
3837: end if;
3838: --ASO_QUOTE_HEADERS_ALL;END_CUSTOMER_PARTY_ID
3839: appid:=697;
3840: if(regid_proc = true) then
3841: 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
3842:
3843: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3844: );
3845: else
3842:
3843: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3844: );
3845: else
3846: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3847: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3848: );
3849: end if;
3850: --ASO_QUOTE_HEADERS_ALL;END_CUSTOMER_CUST_PARTY_ID
3849: end if;
3850: --ASO_QUOTE_HEADERS_ALL;END_CUSTOMER_CUST_PARTY_ID
3851: appid:=697;
3852: if(regid_proc = true) then
3853: 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
3854:
3855: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3856: );
3857: else
3854:
3855: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3856: );
3857: else
3858: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3859: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3860: );
3861: end if;
3862: --ASO_QUOTE_HEADERS_ALL;END_CUSTOMER_PARTY_SITE_ID
3861: end if;
3862: --ASO_QUOTE_HEADERS_ALL;END_CUSTOMER_PARTY_SITE_ID
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'))
3871: in (select /*+ parallel(yy)*/ yy.END_CUSTOMER_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
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
3883: end if;
3884: --ASO_QUOTE_LINES_ALL;END_CUSTOMER_PARTY_ID
3885: appid:=697;
3886: if(regid_proc = true) then
3887: 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
3888:
3889: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3890: );
3891: else
3888:
3889: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3890: );
3891: else
3892: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3893: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3894: );
3895: end if;
3896: --ASO_QUOTE_LINES_ALL;END_CUSTOMER_CUST_PARTY_ID
3895: end if;
3896: --ASO_QUOTE_LINES_ALL;END_CUSTOMER_CUST_PARTY_ID
3897: appid:=697;
3898: if(regid_proc = true) then
3899: 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
3900:
3901: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3902: );
3903: else
3900:
3901: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3902: );
3903: else
3904: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3905: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUSTOMER_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3906: );
3907: end if;
3908: --ASO_QUOTE_LINES_ALL;END_CUSTOMER_PARTY_SITE_ID
3907: end if;
3908: --ASO_QUOTE_LINES_ALL;END_CUSTOMER_PARTY_SITE_ID
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'))
3917: in (select /*+ parallel(yy)*/ yy.END_CUSTOMER_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL
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
3929: end if;
3930: --OKE_POOL_PARTIES;PARTY_ID
3931: appid:=777;
3932: if(regid_proc = true) then
3933: insert into hz_application_trans_gt(app_id,party_id) select 777, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 777 and appl.party_id=temp.party_id) and
3934:
3935: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
3936: );
3937: else
3934:
3935: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
3936: );
3937: else
3938: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3939: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
3940: );
3941: end if;
3942: --AHL_SUBSCRIPTIONS_B;REQUESTED_BY_PARTY_ID
3941: end if;
3942: --AHL_SUBSCRIPTIONS_B;REQUESTED_BY_PARTY_ID
3943: appid:=867;
3944: if(regid_proc = true) then
3945: insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 867 and appl.party_id=temp.party_id) and
3946:
3947: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3948: );
3949: else
3946:
3947: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3948: );
3949: else
3950: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3951: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3952: );
3953: end if;
3954: --AHL_SUBSCRIPTIONS_B;SUBSCRIBED_FRM_PARTY_ID
3953: end if;
3954: --AHL_SUBSCRIPTIONS_B;SUBSCRIBED_FRM_PARTY_ID
3955: appid:=867;
3956: if(regid_proc = true) then
3957: insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 867 and appl.party_id=temp.party_id) and
3958:
3959: temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3960: );
3961: else
3958:
3959: temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3960: );
3961: else
3962: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3963: temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3964: );
3965: end if;
3966: --AHL_ROUTES_B;OPERATOR_PARTY_ID
3965: end if;
3966: --AHL_ROUTES_B;OPERATOR_PARTY_ID
3967: appid:=867;
3968: if(regid_proc = true) then
3969: insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 867 and appl.party_id=temp.party_id) and
3970:
3971: temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
3972: );
3973: else
3970:
3971: temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
3972: );
3973: else
3974: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3975: temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
3976: );
3977: end if;
3978: --AS_SALES_LEADS;INCUMBENT_PARTNER_PARTY_ID
3977: end if;
3978: --AS_SALES_LEADS;INCUMBENT_PARTNER_PARTY_ID
3979: appid:=279;
3980: if(regid_proc = true) then
3981: 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
3982:
3983: temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
3984: );
3985: else
3982:
3983: temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
3984: );
3985: else
3986: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3987: temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
3988: );
3989: end if;
3990: --AS_SALES_CREDITS_DENORM;CLOSE_COMPETITOR_ID
3989: end if;
3990: --AS_SALES_CREDITS_DENORM;CLOSE_COMPETITOR_ID
3991: appid:=279;
3992: if(regid_proc = true) then
3993: 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
3994:
3995: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
3996: );
3997: else
3994:
3995: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
3996: );
3997: else
3998: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3999: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
4000: );
4001: end if;
4002: --AS_LEADS_LOG;CUSTOMER_ID
4001: end if;
4002: --AS_LEADS_LOG;CUSTOMER_ID
4003: appid:=279;
4004: if(regid_proc = true) then
4005: 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
4006:
4007: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
4008: );
4009: else
4006:
4007: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
4008: );
4009: else
4010: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4011: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
4012: );
4013: end if;
4014: --AS_LEADS_LOG;ADDRESS_ID
4013: end if;
4014: --AS_LEADS_LOG;ADDRESS_ID
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'))
4023: in (select /*+ parallel(yy)*/ yy.ADDRESS_ID from AS_LEADS_LOG
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
4035: end if;
4036: --AS_LEADS_LOG;CLOSE_COMPETITOR_ID
4037: appid:=279;
4038: if(regid_proc = true) then
4039: 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
4040:
4041: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
4042: );
4043: else
4040:
4041: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
4042: );
4043: else
4044: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4045: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
4046: );
4047: end if;
4048: --AS_CURRENT_ENVIRONMENT;CUSTOMER_ID
4047: end if;
4048: --AS_CURRENT_ENVIRONMENT;CUSTOMER_ID
4049: appid:=279;
4050: if(regid_proc = true) then
4051: 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
4052:
4053: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
4054: );
4055: else
4052:
4053: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
4054: );
4055: else
4056: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4057: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
4058: );
4059: end if;
4060: --AS_SALES_LEADS;REFERRED_BY
4059: end if;
4060: --AS_SALES_LEADS;REFERRED_BY
4061: appid:=279;
4062: if(regid_proc = true) then
4063: 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
4064:
4065: temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
4066: );
4067: else
4064:
4065: temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
4066: );
4067: else
4068: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4069: temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
4070: );
4071: end if;
4072: --AMW_ASSESSMENTS_B;ASSESSMENT_OWNER_ID
4071: end if;
4072: --AMW_ASSESSMENTS_B;ASSESSMENT_OWNER_ID
4073: appid:=242;
4074: if(regid_proc = true) then
4075: insert into hz_application_trans_gt(app_id,party_id) select 242, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 242 and appl.party_id=temp.party_id) and
4076:
4077: temp.party_id in (select /*+ parallel(xx)*/ xx.ASSESSMENT_OWNER_ID from AMW_ASSESSMENTS_B xx
4078: );
4079: else
4076:
4077: temp.party_id in (select /*+ parallel(xx)*/ xx.ASSESSMENT_OWNER_ID from AMW_ASSESSMENTS_B xx
4078: );
4079: else
4080: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4081: temp.party_id in (select /*+ parallel(xx)*/ xx.ASSESSMENT_OWNER_ID from AMW_ASSESSMENTS_B xx
4082: );
4083: end if;
4084: --AMW_AP_EXECUTIONS;EXECUTED_BY
4083: end if;
4084: --AMW_AP_EXECUTIONS;EXECUTED_BY
4085: appid:=242;
4086: if(regid_proc = true) then
4087: insert into hz_application_trans_gt(app_id,party_id) select 242, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 242 and appl.party_id=temp.party_id) and
4088:
4089: temp.party_id in (select /*+ parallel(xx)*/ xx.EXECUTED_BY from AMW_AP_EXECUTIONS xx
4090: );
4091: else
4088:
4089: temp.party_id in (select /*+ parallel(xx)*/ xx.EXECUTED_BY from AMW_AP_EXECUTIONS xx
4090: );
4091: else
4092: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4093: temp.party_id in (select /*+ parallel(xx)*/ xx.EXECUTED_BY from AMW_AP_EXECUTIONS xx
4094: );
4095: end if;
4096: --AMW_CONSTRAINTS_B;ENTERED_BY_ID
4095: end if;
4096: --AMW_CONSTRAINTS_B;ENTERED_BY_ID
4097: appid:=242;
4098: if(regid_proc = true) then
4099: insert into hz_application_trans_gt(app_id,party_id) select 242, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 242 and appl.party_id=temp.party_id) and
4100:
4101: temp.party_id in (select /*+ parallel(xx)*/ xx.ENTERED_BY_ID from AMW_CONSTRAINTS_B xx
4102: );
4103: else
4100:
4101: temp.party_id in (select /*+ parallel(xx)*/ xx.ENTERED_BY_ID from AMW_CONSTRAINTS_B xx
4102: );
4103: else
4104: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4105: temp.party_id in (select /*+ parallel(xx)*/ xx.ENTERED_BY_ID from AMW_CONSTRAINTS_B xx
4106: );
4107: end if;
4108: --AMW_VIOLATIONS;REQUESTED_BY_ID
4107: end if;
4108: --AMW_VIOLATIONS;REQUESTED_BY_ID
4109: appid:=242;
4110: if(regid_proc = true) then
4111: insert into hz_application_trans_gt(app_id,party_id) select 242, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 242 and appl.party_id=temp.party_id) and
4112:
4113: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_ID from AMW_VIOLATIONS xx
4114: );
4115: else
4112:
4113: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_ID from AMW_VIOLATIONS xx
4114: );
4115: else
4116: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4117: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_ID from AMW_VIOLATIONS xx
4118: );
4119: end if;
4120: --AMW_CERTIFICATION_B;CERTIFICATION_OWNER_ID
4119: end if;
4120: --AMW_CERTIFICATION_B;CERTIFICATION_OWNER_ID
4121: appid:=242;
4122: if(regid_proc = true) then
4123: insert into hz_application_trans_gt(app_id,party_id) select 242, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 242 and appl.party_id=temp.party_id) and
4124:
4125: temp.party_id in (select /*+ parallel(xx)*/ xx.CERTIFICATION_OWNER_ID from AMW_CERTIFICATION_B xx
4126: );
4127: else
4124:
4125: temp.party_id in (select /*+ parallel(xx)*/ xx.CERTIFICATION_OWNER_ID from AMW_CERTIFICATION_B xx
4126: );
4127: else
4128: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4129: temp.party_id in (select /*+ parallel(xx)*/ xx.CERTIFICATION_OWNER_ID from AMW_CERTIFICATION_B xx
4130: );
4131: end if;
4132: --JTF_PERZ_DATA_V;ATTRIBUTE_VALUE
4131: end if;
4132: --JTF_PERZ_DATA_V;ATTRIBUTE_VALUE
4133: appid:=514;
4134: if(regid_proc = true) then
4135: insert into hz_application_trans_gt(app_id,party_id) select 514, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 514 and appl.party_id=temp.party_id) and
4136:
4137: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
4138: where (attribute_name = 'customer ID' and application_id = 514 and perz_data_type in ('CSS_PROFILE_DEFECT_TEMPLATE','CSS_PROFILE_ENH_TEMPLATE') and profile_name like 'CSS_514%'));
4139: else
4136:
4137: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
4138: where (attribute_name = 'customer ID' and application_id = 514 and perz_data_type in ('CSS_PROFILE_DEFECT_TEMPLATE','CSS_PROFILE_ENH_TEMPLATE') and profile_name like 'CSS_514%'));
4139: else
4140: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4141: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
4142: where (attribute_name = 'customer ID' and application_id = 514 and perz_data_type in ('CSS_PROFILE_DEFECT_TEMPLATE','CSS_PROFILE_ENH_TEMPLATE') and profile_name like 'CSS_514%'));
4143: end if;
4144: --JTF_PERZ_QUERY_PARAM_V;PARAMETER_VALUE
4143: end if;
4144: --JTF_PERZ_QUERY_PARAM_V;PARAMETER_VALUE
4145: appid:=514;
4146: if(regid_proc = true) then
4147: insert into hz_application_trans_gt(app_id,party_id) select 514, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 514 and appl.party_id=temp.party_id) and
4148:
4149: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
4150: where (parameter_name in ('CUSTOMER_ID','SR_CUSTOMER_ID') and application_id = 514 and query_type like 'ADV_SEARCH_%' and profile_name like 'CSS_514%' ));
4151: else
4148:
4149: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
4150: where (parameter_name in ('CUSTOMER_ID','SR_CUSTOMER_ID') and application_id = 514 and query_type like 'ADV_SEARCH_%' and profile_name like 'CSS_514%' ));
4151: else
4152: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4153: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
4154: where (parameter_name in ('CUSTOMER_ID','SR_CUSTOMER_ID') and application_id = 514 and query_type like 'ADV_SEARCH_%' and profile_name like 'CSS_514%' ));
4155: end if;
4156: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_ID
4155: end if;
4156: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_ID
4157: appid:=396;
4158: if(regid_proc = true) then
4159: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4160:
4161: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
4162: );
4163: else
4160:
4161: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
4162: );
4163: else
4164: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4165: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
4166: );
4167: end if;
4168: --PON_SUPPLIER_ACCESS;SUPPLIER_TRADING_PARTNER_ID
4167: end if;
4168: --PON_SUPPLIER_ACCESS;SUPPLIER_TRADING_PARTNER_ID
4169: appid:=396;
4170: if(regid_proc = true) then
4171: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4172:
4173: temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
4174: );
4175: else
4172:
4173: temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
4174: );
4175: else
4176: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4177: temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
4178: );
4179: end if;
4180: --PON_PARTY_LINE_EXCLUSIONS;TRADING_PARTNER_ID
4179: end if;
4180: --PON_PARTY_LINE_EXCLUSIONS;TRADING_PARTNER_ID
4181: appid:=396;
4182: if(regid_proc = true) then
4183: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4184:
4185: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
4186: );
4187: else
4184:
4185: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
4186: );
4187: else
4188: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4189: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
4190: );
4191: end if;
4192: --PON_PF_SUPPLIER_FORMULA;TRADING_PARTNER_ID
4191: end if;
4192: --PON_PF_SUPPLIER_FORMULA;TRADING_PARTNER_ID
4193: appid:=396;
4194: if(regid_proc = true) then
4195: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4196:
4197: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
4198: );
4199: else
4196:
4197: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
4198: );
4199: else
4200: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4201: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
4202: );
4203: end if;
4204: --PON_BID_HEADERS;SURROG_BID_CREATED_TP_ID
4203: end if;
4204: --PON_BID_HEADERS;SURROG_BID_CREATED_TP_ID
4205: appid:=396;
4206: if(regid_proc = true) then
4207: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4208:
4209: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
4210: );
4211: else
4208:
4209: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
4210: );
4211: else
4212: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4213: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
4214: );
4215: end if;
4216: --PON_CONTRACTS;AUTHORING_PARTY_CONTACT_ID
4215: end if;
4216: --PON_CONTRACTS;AUTHORING_PARTY_CONTACT_ID
4217: appid:=396;
4218: if(regid_proc = true) then
4219: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4220:
4221: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
4222: );
4223: else
4220:
4221: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
4222: );
4223: else
4224: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4225: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
4226: );
4227: end if;
4228: --PON_CONTRACTS;AUTHORING_PARTY_ID
4227: end if;
4228: --PON_CONTRACTS;AUTHORING_PARTY_ID
4229: appid:=396;
4230: if(regid_proc = true) then
4231: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4232:
4233: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
4234: );
4235: else
4232:
4233: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
4234: );
4235: else
4236: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4237: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
4238: );
4239: end if;
4240: --PON_THREADS;OWNER_PARTY_ID
4239: end if;
4240: --PON_THREADS;OWNER_PARTY_ID
4241: appid:=396;
4242: if(regid_proc = true) then
4243: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4244:
4245: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
4246: );
4247: else
4244:
4245: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
4246: );
4247: else
4248: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4249: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
4250: );
4251: end if;
4252: --PON_THREAD_ENTRIES;FROM_ID
4251: end if;
4252: --PON_THREAD_ENTRIES;FROM_ID
4253: appid:=396;
4254: if(regid_proc = true) then
4255: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4256:
4257: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
4258: );
4259: else
4256:
4257: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
4258: );
4259: else
4260: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4261: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
4262: );
4263: end if;
4264: --PON_THREAD_ENTRIES;FROM_COMPANY_ID
4263: end if;
4264: --PON_THREAD_ENTRIES;FROM_COMPANY_ID
4265: appid:=396;
4266: if(regid_proc = true) then
4267: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4268:
4269: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
4270: );
4271: else
4268:
4269: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
4270: );
4271: else
4272: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4273: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
4274: );
4275: end if;
4276: --PON_TE_RECIPIENTS;TO_COMPANY_ID
4275: end if;
4276: --PON_TE_RECIPIENTS;TO_COMPANY_ID
4277: appid:=396;
4278: if(regid_proc = true) then
4279: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4280:
4281: temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
4282: );
4283: else
4280:
4281: temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
4282: );
4283: else
4284: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4285: temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
4286: );
4287: end if;
4288: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_CONTACT_ID
4287: end if;
4288: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_CONTACT_ID
4289: appid:=396;
4290: if(regid_proc = true) then
4291: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4292:
4293: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4294: );
4295: else
4292:
4293: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4294: );
4295: else
4296: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4297: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4298: );
4299: end if;
4300: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_TP_ID
4299: end if;
4300: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_TP_ID
4301: appid:=396;
4302: if(regid_proc = true) then
4303: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4304:
4305: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
4306: );
4307: else
4304:
4305: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
4306: );
4307: else
4308: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4309: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
4310: );
4311: end if;
4312: --PON_SUPPLIER_ACCESS;BUYER_TP_CONTACT_ID
4311: end if;
4312: --PON_SUPPLIER_ACCESS;BUYER_TP_CONTACT_ID
4313: appid:=396;
4314: if(regid_proc = true) then
4315: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4316:
4317: temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
4318: );
4319: else
4316:
4317: temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
4318: );
4319: else
4320: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4321: temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
4322: );
4323: end if;
4324: --PON_BIDDING_PARTIES;TRADING_PARTNER_CONTACT_ID
4323: end if;
4324: --PON_BIDDING_PARTIES;TRADING_PARTNER_CONTACT_ID
4325: appid:=396;
4326: if(regid_proc = true) then
4327: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4328:
4329: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4330: );
4331: else
4328:
4329: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4330: );
4331: else
4332: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4333: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4334: );
4335: end if;
4336: --PON_BIDDING_PARTIES;ACK_PARTNER_CONTACT_ID
4335: end if;
4336: --PON_BIDDING_PARTIES;ACK_PARTNER_CONTACT_ID
4337: appid:=396;
4338: if(regid_proc = true) then
4339: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4340:
4341: temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4342: );
4343: else
4340:
4341: temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4342: );
4343: else
4344: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4345: temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4346: );
4347: end if;
4348: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_CONTACT_ID
4347: end if;
4348: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_CONTACT_ID
4349: appid:=396;
4350: if(regid_proc = true) then
4351: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4352:
4353: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
4354: );
4355: else
4352:
4353: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
4354: );
4355: else
4356: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4357: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
4358: );
4359: end if;
4360: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_ID
4359: end if;
4360: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_ID
4361: appid:=396;
4362: if(regid_proc = true) then
4363: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4364:
4365: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
4366: );
4367: else
4364:
4365: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
4366: );
4367: else
4368: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4369: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
4370: );
4371: end if;
4372: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_CONTACT_ID
4371: end if;
4372: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_CONTACT_ID
4373: appid:=396;
4374: if(regid_proc = true) then
4375: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4376:
4377: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4378: );
4379: else
4376:
4377: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4378: );
4379: else
4380: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4381: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4382: );
4383: end if;
4384: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_ID
4383: end if;
4384: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_ID
4385: appid:=396;
4386: if(regid_proc = true) then
4387: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4388:
4389: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
4390: );
4391: else
4388:
4389: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
4390: );
4391: else
4392: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4393: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
4394: );
4395: end if;
4396: --PON_AUCTION_SUMMARY;TRADING_PARTNER_CONTACT_ID
4395: end if;
4396: --PON_AUCTION_SUMMARY;TRADING_PARTNER_CONTACT_ID
4397: appid:=396;
4398: if(regid_proc = true) then
4399: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4400:
4401: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
4402: );
4403: else
4400:
4401: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
4402: );
4403: else
4404: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4405: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
4406: );
4407: end if;
4408: --PON_AUCTION_SUMMARY;TRADING_PARTNER_ID
4407: end if;
4408: --PON_AUCTION_SUMMARY;TRADING_PARTNER_ID
4409: appid:=396;
4410: if(regid_proc = true) then
4411: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4412:
4413: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
4414: );
4415: else
4412:
4413: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
4414: );
4415: else
4416: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4417: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
4418: );
4419: end if;
4420: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_CONTACT_ID
4419: end if;
4420: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_CONTACT_ID
4421: appid:=396;
4422: if(regid_proc = true) then
4423: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4424:
4425: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
4426: );
4427: else
4424:
4425: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
4426: );
4427: else
4428: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4429: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
4430: );
4431: end if;
4432: --PON_AUCTION_HEADERS_ALL;DRAFT_LOCKED_BY_CONTACT_ID
4431: end if;
4432: --PON_AUCTION_HEADERS_ALL;DRAFT_LOCKED_BY_CONTACT_ID
4433: appid:=396;
4434: if(regid_proc = true) then
4435: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4436:
4437: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4438: );
4439: else
4436:
4437: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4438: );
4439: else
4440: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4441: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4442: );
4443: end if;
4444: --PON_AUCTION_HEADERS_ALL;DRAFT_UNLOCKED_BY_CONTACT_ID
4443: end if;
4444: --PON_AUCTION_HEADERS_ALL;DRAFT_UNLOCKED_BY_CONTACT_ID
4445: appid:=396;
4446: if(regid_proc = true) then
4447: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4448:
4449: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4450: );
4451: else
4448:
4449: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4450: );
4451: else
4452: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4453: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4454: );
4455: end if;
4456: --PON_AUCTION_HEADERS_ALL;SCORING_LOCK_TP_CONTACT_ID
4455: end if;
4456: --PON_AUCTION_HEADERS_ALL;SCORING_LOCK_TP_CONTACT_ID
4457: appid:=396;
4458: if(regid_proc = true) then
4459: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4460:
4461: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4462: );
4463: else
4460:
4461: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4462: );
4463: else
4464: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4465: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4466: );
4467: end if;
4468: --PON_AUCTION_EVENTS;TRADING_PARTNER_CONTACT_ID
4467: end if;
4468: --PON_AUCTION_EVENTS;TRADING_PARTNER_CONTACT_ID
4469: appid:=396;
4470: if(regid_proc = true) then
4471: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4472:
4473: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
4474: );
4475: else
4472:
4473: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
4474: );
4475: else
4476: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4477: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
4478: );
4479: end if;
4480: --PON_BID_HEADERS;SURROG_BID_CREATED_CONTACT_ID
4479: end if;
4480: --PON_BID_HEADERS;SURROG_BID_CREATED_CONTACT_ID
4481: appid:=396;
4482: if(regid_proc = true) then
4483: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4484:
4485: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
4486: );
4487: else
4484:
4485: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
4486: );
4487: else
4488: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4489: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
4490: );
4491: end if;
4492: --PON_BID_HEADERS;SCORE_OVERRIDE_TP_CONTACT_ID
4491: end if;
4492: --PON_BID_HEADERS;SCORE_OVERRIDE_TP_CONTACT_ID
4493: appid:=396;
4494: if(regid_proc = true) then
4495: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4496:
4497: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
4498: );
4499: else
4496:
4497: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
4498: );
4499: else
4500: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4501: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
4502: );
4503: end if;
4504: --PON_BID_HEADERS;SHORTLIST_TPC_ID
4503: end if;
4504: --PON_BID_HEADERS;SHORTLIST_TPC_ID
4505: appid:=396;
4506: if(regid_proc = true) then
4507: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4508:
4509: temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
4510: );
4511: else
4508:
4509: temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
4510: );
4511: else
4512: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4513: temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
4514: );
4515: end if;
4516: --PON_BID_HEADERS;DRAFT_UNLOCKED_BY_CONTACT_ID
4515: end if;
4516: --PON_BID_HEADERS;DRAFT_UNLOCKED_BY_CONTACT_ID
4517: appid:=396;
4518: if(regid_proc = true) then
4519: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4520:
4521: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4522: );
4523: else
4520:
4521: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4522: );
4523: else
4524: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4525: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4526: );
4527: end if;
4528: --PON_BID_HEADERS;DRAFT_LOCKED_BY_CONTACT_ID
4527: end if;
4528: --PON_BID_HEADERS;DRAFT_LOCKED_BY_CONTACT_ID
4529: appid:=396;
4530: if(regid_proc = true) then
4531: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 396 and appl.party_id=temp.party_id) and
4532:
4533: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4534: );
4535: else
4532:
4533: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4534: );
4535: else
4536: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4537: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4538: );
4539: end if;
4540: --OKL_INS_POLICIES_B;INT_ID
4539: end if;
4540: --OKL_INS_POLICIES_B;INT_ID
4541: appid:=540;
4542: if(regid_proc = true) then
4543: 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
4544:
4545: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_B xx
4546: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4547: else
4544:
4545: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_B xx
4546: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4547: else
4548: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4549: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_B xx
4550: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4551: end if;
4552: --OKL_INS_POLICIES_B;AGENT_SITE_ID
4551: end if;
4552: --OKL_INS_POLICIES_B;AGENT_SITE_ID
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'))
4561: in (select /*+ parallel(yy)*/ yy.AGENT_SITE_ID from OKL_INS_POLICIES_B
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)
4573: end if;
4574: --OKL_INS_POLICIES_B;AGENCY_SITE_ID
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'))
4583: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B
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
4595: end if;
4596: --OKL_INS_POLICIES_B;AGENCY_SITE_ID
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'))
4605: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B
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
4617: end if;
4618: --OKL_INS_POLICIES_B;ISU_ID
4619: appid:=540;
4620: if(regid_proc = true) then
4621: 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
4622:
4623: temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_B xx
4624: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4625: else
4622:
4623: temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_B xx
4624: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4625: else
4626: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4627: temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_B xx
4628: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4629: end if;
4630: --OKL_EXT_SELL_INVS_B;CUSTOMER_ID
4629: end if;
4630: --OKL_EXT_SELL_INVS_B;CUSTOMER_ID
4631: appid:=540;
4632: if(regid_proc = true) then
4633: 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
4634:
4635: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from OKL_EXT_SELL_INVS_B xx
4636: );
4637: else
4634:
4635: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from OKL_EXT_SELL_INVS_B xx
4636: );
4637: else
4638: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4639: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from OKL_EXT_SELL_INVS_B xx
4640: );
4641: end if;
4642: --OKL_OPEN_INT;PARTY_ID
4641: end if;
4642: --OKL_OPEN_INT;PARTY_ID
4643: appid:=540;
4644: if(regid_proc = true) then
4645: 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
4646:
4647: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT xx
4648: );
4649: else
4646:
4647: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT xx
4648: );
4649: else
4650: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4651: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT xx
4652: );
4653: end if;
4654: --AMS_IBA_PL_SITES_B;SITE_CATEGORY_OBJECT_ID
4653: end if;
4654: --AMS_IBA_PL_SITES_B;SITE_CATEGORY_OBJECT_ID
4655: appid:=530;
4656: if(regid_proc = true) then
4657: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
4658:
4659: temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
4660: where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
4661: else
4658:
4659: temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
4660: where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
4661: else
4662: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4663: temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
4664: where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
4665: end if;
4666: --AMS_PARTY_MARKET_SEGMENTS;PARTY_ID
4665: end if;
4666: --AMS_PARTY_MARKET_SEGMENTS;PARTY_ID
4667: appid:=530;
4668: if(regid_proc = true) then
4669: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
4670:
4671: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
4672: );
4673: else
4670:
4671: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
4672: );
4673: else
4674: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4675: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
4676: );
4677: end if;
4678: --AMS_AGENDAS_B;COORDINATOR_ID
4677: end if;
4678: --AMS_AGENDAS_B;COORDINATOR_ID
4679: appid:=530;
4680: if(regid_proc = true) then
4681: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
4682:
4683: temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
4684: );
4685: else
4682:
4683: temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
4684: );
4685: else
4686: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4687: temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
4688: );
4689: end if;
4690: --AMS_TCOP_CHANNEL_SUMMARY;PARTY_ID
4689: end if;
4690: --AMS_TCOP_CHANNEL_SUMMARY;PARTY_ID
4691: appid:=530;
4692: if(regid_proc = true) then
4693: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
4694:
4695: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
4696: );
4697: else
4694:
4695: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
4696: );
4697: else
4698: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4699: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
4700: );
4701: end if;
4702: --AMS_TCOP_CONTACTS;PARTY_ID
4701: end if;
4702: --AMS_TCOP_CONTACTS;PARTY_ID
4703: appid:=530;
4704: if(regid_proc = true) then
4705: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
4706:
4707: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
4708: );
4709: else
4706:
4707: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
4708: );
4709: else
4710: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4711: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
4712: );
4713: end if;
4714: --AMS_TCOP_PRVW_CONTACTS;PARTY_ID
4713: end if;
4714: --AMS_TCOP_PRVW_CONTACTS;PARTY_ID
4715: appid:=530;
4716: if(regid_proc = true) then
4717: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
4718:
4719: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
4720: );
4721: else
4718:
4719: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
4720: );
4721: else
4722: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4723: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
4724: );
4725: end if;
4726: --AMS_TCOP_PRVW_FTG_DTLS;PARTY_ID
4725: end if;
4726: --AMS_TCOP_PRVW_FTG_DTLS;PARTY_ID
4727: appid:=530;
4728: if(regid_proc = true) then
4729: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 530 and appl.party_id=temp.party_id) and
4730:
4731: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
4732: );
4733: else
4730:
4731: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
4732: );
4733: else
4734: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4735: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
4736: );
4737: end if;
4738: --CSC_CUST_PLANS;PARTY_ID
4737: end if;
4738: --CSC_CUST_PLANS;PARTY_ID
4739: appid:=511;
4740: if(regid_proc = true) then
4741: insert into hz_application_trans_gt(app_id,party_id) select 511, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 511 and appl.party_id=temp.party_id) and
4742:
4743: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
4744: );
4745: else
4742:
4743: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
4744: );
4745: else
4746: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4747: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
4748: );
4749: end if;
4750: --ISC_DR_REPAIR_ORDERS_F;CUSTOMER_ID
4749: end if;
4750: --ISC_DR_REPAIR_ORDERS_F;CUSTOMER_ID
4751: appid:=454;
4752: if(regid_proc = true) then
4753: insert into hz_application_trans_gt(app_id,party_id) select 454, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 454 and appl.party_id=temp.party_id) and
4754:
4755: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from ISC_DR_REPAIR_ORDERS_F xx
4756: );
4757: else
4754:
4755: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from ISC_DR_REPAIR_ORDERS_F xx
4756: );
4757: else
4758: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4759: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from ISC_DR_REPAIR_ORDERS_F xx
4760: );
4761: end if;
4762: --FTE_LOCATION_PARAMETERS;FACILITY_CONTACT_ID
4761: end if;
4762: --FTE_LOCATION_PARAMETERS;FACILITY_CONTACT_ID
4763: appid:=716;
4764: if(regid_proc = true) then
4765: insert into hz_application_trans_gt(app_id,party_id) select 716, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 716 and appl.party_id=temp.party_id) and
4766:
4767: temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
4768: );
4769: else
4766:
4767: temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
4768: );
4769: else
4770: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4771: temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
4772: );
4773: end if;
4774: --FEM_PARTY_PROFITABILITY;PARTY_ID
4773: end if;
4774: --FEM_PARTY_PROFITABILITY;PARTY_ID
4775: appid:=272;
4776: if(regid_proc = true) then
4777: insert into hz_application_trans_gt(app_id,party_id) select 272, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 272 and appl.party_id=temp.party_id) and
4778:
4779: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FEM_PARTY_PROFITABILITY xx
4780: );
4781: else
4778:
4779: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FEM_PARTY_PROFITABILITY xx
4780: );
4781: else
4782: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4783: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FEM_PARTY_PROFITABILITY xx
4784: );
4785: end if;
4786: --JTF_FM_PROCESSED_V;PARTY_ID
4785: end if;
4786: --JTF_FM_PROCESSED_V;PARTY_ID
4787: appid:=690;
4788: if(regid_proc = true) then
4789: 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
4790:
4791: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
4792: );
4793: else
4790:
4791: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
4792: );
4793: else
4794: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4795: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
4796: );
4797: end if;
4798: --JTF_FM_CONTENT_HISTORY_V;PARTY_ID
4797: end if;
4798: --JTF_FM_CONTENT_HISTORY_V;PARTY_ID
4799: appid:=690;
4800: if(regid_proc = true) then
4801: 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
4802:
4803: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
4804: );
4805: else
4802:
4803: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
4804: );
4805: else
4806: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4807: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
4808: );
4809: end if;
4810: --JTF_TTY_NAMED_ACCTS;PARTY_SITE_ID
4809: end if;
4810: --JTF_TTY_NAMED_ACCTS;PARTY_SITE_ID
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'))
4819: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from JTF_TTY_NAMED_ACCTS
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
4831: end if;
4832: --OKS_QUALIFIERS;QUALIFIER_ATTR_VALUE
4833: appid:=515;
4834: if(regid_proc = true) then
4835: 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
4836:
4837: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
4838: where ((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'))));
4839: else
4836:
4837: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
4838: where ((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'))));
4839: else
4840: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4841: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
4842: where ((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'))));
4843: end if;
4844: --OKS_QUALIFIERS;QUALIFIER_ATTR_VALUE
4843: end if;
4844: --OKS_QUALIFIERS;QUALIFIER_ATTR_VALUE
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'))
4853: in (select /*+ parallel(yy)*/ yy.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS
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)
4865: end if;
4866: --CS_ESTIMATE_DETAILS;BILL_TO_PARTY_ID
4867: appid:=170;
4868: if(regid_proc = true) then
4869: 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
4870:
4871: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
4872: );
4873: else
4870:
4871: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
4872: );
4873: else
4874: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4875: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
4876: );
4877: end if;
4878: --CS_ESTIMATE_DETAILS;SHIP_TO_PARTY_ID
4877: end if;
4878: --CS_ESTIMATE_DETAILS;SHIP_TO_PARTY_ID
4879: appid:=170;
4880: if(regid_proc = true) then
4881: 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
4882:
4883: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
4884: );
4885: else
4882:
4883: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
4884: );
4885: else
4886: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4887: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
4888: );
4889: end if;
4890: --CS_INCIDENTS_ALL_B;SITE_ID
4889: end if;
4890: --CS_INCIDENTS_ALL_B;SITE_ID
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'))
4899: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_ALL_B
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
4911: end if;
4912: --CS_INCIDENTS_AUDIT_B;SITE_ID
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'))
4921: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_AUDIT_B
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
4933: end if;
4934: --CS_INCIDENTS_ALL_B;BILL_TO_SITE_ID
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'))
4943: in (select /*+ parallel(yy)*/ yy.BILL_TO_SITE_ID from CS_INCIDENTS_ALL_B
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
4955: end if;
4956: --CS_INCIDENTS_ALL_B;SHIP_TO_SITE_ID
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'))
4965: in (select /*+ parallel(yy)*/ yy.SHIP_TO_SITE_ID from CS_INCIDENTS_ALL_B
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
4977: end if;
4978: --CS_INCIDENTS_ALL_B;INSTALL_SITE_ID
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'))
4987: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_ID from CS_INCIDENTS_ALL_B
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
4999: end if;
5000: --CS_INCIDENTS_AUDIT_B;BILL_TO_CONTACT_ID
5001: appid:=170;
5002: if(regid_proc = true) then
5003: 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
5004:
5005: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
5006: );
5007: else
5004:
5005: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
5006: );
5007: else
5008: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5009: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
5010: );
5011: end if;
5012: --CS_INCIDENTS_ALL_B;BILL_TO_PARTY_ID
5011: end if;
5012: --CS_INCIDENTS_ALL_B;BILL_TO_PARTY_ID
5013: appid:=170;
5014: if(regid_proc = true) then
5015: 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
5016:
5017: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5018: );
5019: else
5016:
5017: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5018: );
5019: else
5020: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5021: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5022: );
5023: end if;
5024: --CS_INCIDENTS_ALL_B;SHIP_TO_PARTY_ID
5023: end if;
5024: --CS_INCIDENTS_ALL_B;SHIP_TO_PARTY_ID
5025: appid:=170;
5026: if(regid_proc = true) then
5027: 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
5028:
5029: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5030: );
5031: else
5028:
5029: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5030: );
5031: else
5032: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5033: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5034: );
5035: end if;
5036: --CS_CHG_SUB_RESTRICTIONS;VALUE_OBJECT_ID
5035: end if;
5036: --CS_CHG_SUB_RESTRICTIONS;VALUE_OBJECT_ID
5037: appid:=170;
5038: if(regid_proc = true) then
5039: 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
5040:
5041: temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
5042: );
5043: else
5040:
5041: temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
5042: );
5043: else
5044: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5045: temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
5046: );
5047: end if;
5048: --CS_INCIDENTS_ALL_B;BILL_TO_CONTACT_ID
5047: end if;
5048: --CS_INCIDENTS_ALL_B;BILL_TO_CONTACT_ID
5049: appid:=170;
5050: if(regid_proc = true) then
5051: 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
5052:
5053: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5054: );
5055: else
5052:
5053: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5054: );
5055: else
5056: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5057: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5058: );
5059: end if;
5060: --CS_INCIDENTS_ALL_B;SHIP_TO_CONTACT_ID
5059: end if;
5060: --CS_INCIDENTS_ALL_B;SHIP_TO_CONTACT_ID
5061: appid:=170;
5062: if(regid_proc = true) then
5063: 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
5064:
5065: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5066: );
5067: else
5064:
5065: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5066: );
5067: else
5068: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5069: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5070: );
5071: end if;
5072: --CS_INCIDENTS_ALL_B;CUSTOMER_SITE_ID
5071: end if;
5072: --CS_INCIDENTS_ALL_B;CUSTOMER_SITE_ID
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'))
5081: in (select /*+ parallel(yy)*/ yy.CUSTOMER_SITE_ID from CS_INCIDENTS_ALL_B
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
5093: end if;
5094: --CS_INCIDENTS_ALL_B;INSTALL_SITE_USE_ID
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'))
5103: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CS_INCIDENTS_ALL_B
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
5115: end if;
5116: --CS_INCIDENTS_ALL_B;INCIDENT_LOCATION_ID
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'))
5125: in (select /*+ parallel(yy)*/ yy.INCIDENT_LOCATION_ID from CS_INCIDENTS_ALL_B
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
5137: end if;
5138: --ECX_TP_HEADERS;PARTY_SITE_ID
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'))
5147: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from ECX_TP_HEADERS
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
5159: end if;
5160: --XLE_REGISTRATIONS;ISSUING_AUTHORITY_ID
5161: appid:=204;
5162: if(regid_proc = true) then
5163: 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
5164:
5165: temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
5166: );
5167: else
5164:
5165: temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
5166: );
5167: else
5168: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5169: temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
5170: );
5171: end if;
5172: --XLE_REG_FUNCTIONS;AUTHORITY_ID
5171: end if;
5172: --XLE_REG_FUNCTIONS;AUTHORITY_ID
5173: appid:=204;
5174: if(regid_proc = true) then
5175: 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
5176:
5177: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
5178: );
5179: else
5176:
5177: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
5178: );
5179: else
5180: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5181: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
5182: );
5183: end if;
5184: --XLE_REGISTRATIONS;ISSUING_AUTHORITY_SITE_ID
5183: end if;
5184: --XLE_REGISTRATIONS;ISSUING_AUTHORITY_SITE_ID
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'))
5193: in (select /*+ parallel(yy)*/ yy.ISSUING_AUTHORITY_SITE_ID from XLE_REGISTRATIONS
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
5205: end if;
5206: --XLE_REG_FUNCTIONS;AUTHORITY_SITE_ID
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'))
5215: in (select /*+ parallel(yy)*/ yy.AUTHORITY_SITE_ID from XLE_REG_FUNCTIONS
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
5227: end if;
5228: --IBW_PAGE_VIEWS;PARTY_ID
5229: appid:=666;
5230: if(regid_proc = true) then
5231: insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 666 and appl.party_id=temp.party_id) and
5232:
5233: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
5234: );
5235: else
5232:
5233: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
5234: );
5235: else
5236: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5237: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
5238: );
5239: end if;
5240: --IBW_PAGE_VIEWS;PARTY_RELATIONSHIP_ID
5239: end if;
5240: --IBW_PAGE_VIEWS;PARTY_RELATIONSHIP_ID
5241: appid:=666;
5242: if(regid_proc = true) then
5243: insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 666 and appl.party_id=temp.party_id) and
5244:
5245: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5246: );
5247: else
5244:
5245: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5246: );
5247: else
5248: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5249: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5250: );
5251: end if;
5252: if(regid_proc = true) then
5249: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5250: );
5251: end if;
5252: if(regid_proc = true) then
5253: delete from hz_purge_gt temp where temp.party_id in (select appl.party_id from hz_application_trans_gt appl) ;
5254: end if;
5255:
5256:
5257: EXCEPTION