[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:=691;
33: if(regid_proc = true) then
34: insert into hz_application_trans_gt(app_id,party_id) select 691, 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:=691;
33: if(regid_proc = true) then
34: insert into hz_application_trans_gt(app_id,party_id) select 691, temp.party_id from hz_purge_gt temp where
35:
36:
37: exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_ID = temp.party_id
38: )
129:
130: or exists (select 'Y' from PV_PARTNER_ACCESSES xx where xx.PARTNER_ID = temp.party_id
131: );
132: else
133: delete from hz_purge_gt temp where
134:
135:
136: exists (select 'Y' from PV_PARTNER_PROFILES xx where xx.PARTNER_ID = temp.party_id
137: )
230: );
231: end if;
232:
233:
234: --delete and insert records into hz_purge_gt for an application
235: appid:=665;
236: if(regid_proc = true) then
237: insert into hz_application_trans_gt(app_id,party_id) select 665, temp.party_id from hz_purge_gt temp where
238:
233:
234: --delete and insert records into hz_purge_gt for an application
235: appid:=665;
236: if(regid_proc = true) then
237: insert into hz_application_trans_gt(app_id,party_id) select 665, temp.party_id from hz_purge_gt temp where
238:
239:
240: exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
241: )
247: (select 'Y' from WSH_CARRIER_SITES
248: yy where yy.CARRIER_SITE_ID=xx.PARTY_SITE_ID
249: ));
250: else
251: delete from hz_purge_gt temp where
252:
253:
254: exists (select 'Y' from WSH_CARRIERS xx where xx.CARRIER_ID = temp.party_id
255: )
263: ));
264: end if;
265:
266:
267: --delete and insert records into hz_purge_gt for an application
268: appid:=222;
269: if(regid_proc = true) then
270: insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where
271:
266:
267: --delete and insert records into hz_purge_gt for an application
268: appid:=222;
269: if(regid_proc = true) then
270: insert into hz_application_trans_gt(app_id,party_id) select 222, temp.party_id from hz_purge_gt temp where
271:
272:
273: exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.PARTY_ID = temp.party_id
274: and (nvl(STATUS, 'A') in ('A','I')))
297: or exists (select 'Y' from HZ_RELATIONSHIPS xx where xx.SUBJECT_ID = temp.party_id
298: and (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
299: AND directional_flag = 'F'));
300: else
301: delete from hz_purge_gt temp where
302:
303:
304: exists (select 'Y' from HZ_CUST_ACCOUNTS xx where xx.PARTY_ID = temp.party_id
305: and (nvl(STATUS, 'A') in ('A','I')))
330: AND directional_flag = 'F'));
331: end if;
332:
333:
334: --delete and insert records into hz_purge_gt for an application
335: appid:=671;
336: if(regid_proc = true) then
337: insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where
338:
333:
334: --delete and insert records into hz_purge_gt for an application
335: appid:=671;
336: if(regid_proc = true) then
337: insert into hz_application_trans_gt(app_id,party_id) select 671, temp.party_id from hz_purge_gt temp where
338:
339:
340: exists (select 'Y' from IBE_SH_SHP_LISTS_ALL xx where xx.PARTY_ID = temp.party_id
341: )
361:
362: or exists (select 'Y' from IBE_MSITE_PRTY_ACCSS xx where xx.PARTY_ID = temp.party_id
363: );
364: else
365: delete from hz_purge_gt temp where
366:
367:
368: exists (select 'Y' from IBE_SH_SHP_LISTS_ALL xx where xx.PARTY_ID = temp.party_id
369: )
391: );
392: end if;
393:
394:
395: --delete and insert records into hz_purge_gt for an application
396: appid:=682;
397: if(regid_proc = true) then
398: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where
399:
394:
395: --delete and insert records into hz_purge_gt for an application
396: appid:=682;
397: if(regid_proc = true) then
398: insert into hz_application_trans_gt(app_id,party_id) select 682, temp.party_id from hz_purge_gt temp where
399:
400:
401: exists (select 'Y' from OZF_CUST_TRD_PRFLS_ALL xx where xx.PARTY_ID = temp.party_id
402: )
427:
428: or exists (select 'Y' from OZF_REQUEST_HEADERS_ALL_B xx where xx.PARTNER_ID = temp.party_id
429: );
430: else
431: delete from hz_purge_gt temp where
432:
433:
434: exists (select 'Y' from OZF_CUST_TRD_PRFLS_ALL xx where xx.PARTY_ID = temp.party_id
435: )
462: );
463: end if;
464:
465:
466: --delete and insert records into hz_purge_gt for an application
467: appid:=695;
468: if(regid_proc = true) then
469: insert into hz_application_trans_gt(app_id,party_id) select 695, temp.party_id from hz_purge_gt temp where
470:
465:
466: --delete and insert records into hz_purge_gt for an application
467: appid:=695;
468: if(regid_proc = true) then
469: insert into hz_application_trans_gt(app_id,party_id) select 695, temp.party_id from hz_purge_gt temp where
470:
471:
472: exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
473: )
479: (select 'Y' from IEX_CASE_CONTACTS
480: yy where yy.ADDRESS_ID=xx.PARTY_SITE_ID
481: ));
482: else
483: delete from hz_purge_gt temp where
484:
485:
486: exists (select 'Y' from IEX_DELINQUENCIES_ALL xx where xx.PARTY_CUST_ID = temp.party_id
487: )
495: ));
496: end if;
497:
498:
499: --delete and insert records into hz_purge_gt for an application
500: appid:=661;
501: if(regid_proc = true) then
502: insert into hz_application_trans_gt(app_id,party_id) select 661, temp.party_id from hz_purge_gt temp where
503:
498:
499: --delete and insert records into hz_purge_gt for an application
500: appid:=661;
501: if(regid_proc = true) then
502: insert into hz_application_trans_gt(app_id,party_id) select 661, temp.party_id from hz_purge_gt temp where
503:
504:
505: exists (select 'Y' from QP_QUALIFIERS xx where xx.QUALIFIER_ATTR_VALUE = to_char(temp.party_id)
506: 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'))))
512: (select 'Y' from QP_QUALIFIERS
513: yy where yy.QUALIFIER_ATTR_VALUE=to_char(xx.PARTY_SITE_ID)
514: and (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
515: else
516: delete from hz_purge_gt temp where
517:
518:
519: exists (select 'Y' from QP_QUALIFIERS xx where xx.QUALIFIER_ATTR_VALUE = to_char(temp.party_id)
520: 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'))))
528: and (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
529: end if;
530:
531:
532: --delete and insert records into hz_purge_gt for an application
533: appid:=177;
534: if(regid_proc = true) then
535: insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where
536:
531:
532: --delete and insert records into hz_purge_gt for an application
533: appid:=177;
534: if(regid_proc = true) then
535: insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where
536:
537:
538: exists (select 'Y' from POS_SUPP_PROF_EXT_B xx where xx.PARTY_ID = temp.party_id
539: )
540:
541: or exists (select 'Y' from POS_BUS_CLASS_ATTR xx where xx.PARTY_ID = temp.party_id
542: );
543: else
544: delete from hz_purge_gt temp where
545:
546:
547: exists (select 'Y' from POS_SUPP_PROF_EXT_B xx where xx.PARTY_ID = temp.party_id
548: )
551: );
552: end if;
553:
554:
555: --delete and insert records into hz_purge_gt for an application
556: appid:=694;
557: if(regid_proc = true) then
558: insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where
559:
554:
555: --delete and insert records into hz_purge_gt for an application
556: appid:=694;
557: if(regid_proc = true) then
558: insert into hz_application_trans_gt(app_id,party_id) select 694, temp.party_id from hz_purge_gt temp where
559:
560:
561: exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
562: );
560:
561: exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
562: );
563: else
564: delete from hz_purge_gt temp where
565:
566:
567: exists (select 'Y' from PRP_PROPOSALS xx where xx.PARTY_ID = temp.party_id
568: );
568: );
569: end if;
570:
571:
572: --delete and insert records into hz_purge_gt for an application
573: appid:=542;
574: if(regid_proc = true) then
575: insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where
576:
571:
572: --delete and insert records into hz_purge_gt for an application
573: appid:=542;
574: if(regid_proc = true) then
575: insert into hz_application_trans_gt(app_id,party_id) select 542, temp.party_id from hz_purge_gt temp where
576:
577:
578: exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
579: )
631: (select 'Y' from CSI_SYSTEMS_B
632: yy where yy.INSTALL_SITE_USE_ID=xx.PARTY_SITE_ID
633: ));
634: else
635: delete from hz_purge_gt temp where
636:
637:
638: exists (select 'Y' from CSI_ITEM_INSTANCES xx where xx.OWNER_PARTY_ID = temp.party_id
639: )
693: ));
694: end if;
695:
696:
697: --delete and insert records into hz_purge_gt for an application
698: appid:=510;
699: if(regid_proc = true) then
700: insert into hz_application_trans_gt(app_id,party_id) select 510, temp.party_id from hz_purge_gt temp where
701:
696:
697: --delete and insert records into hz_purge_gt for an application
698: appid:=510;
699: if(regid_proc = true) then
700: insert into hz_application_trans_gt(app_id,party_id) select 510, temp.party_id from hz_purge_gt temp where
701:
702:
703: exists (select 'Y' from OKC_K_PARTY_ROLES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
704: 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')))
789: and nvl(STATUS, 'A') in ('A','I')
790: and exists
791: (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'))));
792: else
793: delete from hz_purge_gt temp where
794:
795:
796: exists (select 'Y' from OKC_K_PARTY_ROLES_B xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
797: 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')))
884: (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'))));
885: end if;
886:
887:
888: --delete and insert records into hz_purge_gt for an application
889: appid:=697;
890: if(regid_proc = true) then
891: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where
892:
887:
888: --delete and insert records into hz_purge_gt for an application
889: appid:=697;
890: if(regid_proc = true) then
891: insert into hz_application_trans_gt(app_id,party_id) select 697, temp.party_id from hz_purge_gt temp where
892:
893:
894: exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
895: )
932: (select 'Y' from ASO_QUOTE_LINES_ALL
933: yy where yy.END_CUSTOMER_PARTY_SITE_ID=xx.PARTY_SITE_ID
934: ));
935: else
936: delete from hz_purge_gt temp where
937:
938:
939: exists (select 'Y' from ASO_QUOTE_HEADERS_ALL xx where xx.PARTY_ID = temp.party_id
940: )
979: ));
980: end if;
981:
982:
983: --delete and insert records into hz_purge_gt for an application
984: appid:=777;
985: if(regid_proc = true) then
986: insert into hz_application_trans_gt(app_id,party_id) select 777, temp.party_id from hz_purge_gt temp where
987:
982:
983: --delete and insert records into hz_purge_gt for an application
984: appid:=777;
985: if(regid_proc = true) then
986: insert into hz_application_trans_gt(app_id,party_id) select 777, temp.party_id from hz_purge_gt temp where
987:
988:
989: exists (select 'Y' from OKE_K_FUNDING_SOURCES_PM_HV xx where xx.K_PARTY_ID = temp.party_id
990: )
991:
992: or exists (select 'Y' from OKE_K_FUNDING_SOURCES xx where xx.K_PARTY_ID = temp.party_id
993: );
994: else
995: delete from hz_purge_gt temp where
996:
997:
998: exists (select 'Y' from OKE_K_FUNDING_SOURCES_PM_HV xx where xx.K_PARTY_ID = temp.party_id
999: )
1002: );
1003: end if;
1004:
1005:
1006: --delete and insert records into hz_purge_gt for an application
1007: appid:=867;
1008: if(regid_proc = true) then
1009: insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where
1010:
1005:
1006: --delete and insert records into hz_purge_gt for an application
1007: appid:=867;
1008: if(regid_proc = true) then
1009: insert into hz_application_trans_gt(app_id,party_id) select 867, temp.party_id from hz_purge_gt temp where
1010:
1011:
1012: exists (select 'Y' from AHL_DOCUMENTS_B xx where xx.SOURCE_PARTY_ID = temp.party_id
1013: )
1020:
1021: or exists (select 'Y' from AHL_DOC_REVISIONS_B xx where xx.APPROVED_BY_PARTY_ID = temp.party_id
1022: );
1023: else
1024: delete from hz_purge_gt temp where
1025:
1026:
1027: exists (select 'Y' from AHL_DOCUMENTS_B xx where xx.SOURCE_PARTY_ID = temp.party_id
1028: )
1037: );
1038: end if;
1039:
1040:
1041: --delete and insert records into hz_purge_gt for an application
1042: appid:=279;
1043: if(regid_proc = true) then
1044: insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where
1045:
1040:
1041: --delete and insert records into hz_purge_gt for an application
1042: appid:=279;
1043: if(regid_proc = true) then
1044: insert into hz_application_trans_gt(app_id,party_id) select 279, temp.party_id from hz_purge_gt temp where
1045:
1046:
1047: exists (select 'Y' from HZ_PARTY_SITES
1048: xx where xx.PARTY_ID = temp.party_id
1359:
1360: or exists (select 'Y' from AS_AP_ACCOUNT_PLANS xx where xx.CUST_PARTY_ID = temp.party_id
1361: );
1362: else
1363: delete from hz_purge_gt temp where
1364:
1365:
1366: exists (select 'Y' from HZ_PARTY_SITES
1367: xx where xx.PARTY_ID = temp.party_id
1680: );
1681: end if;
1682:
1683:
1684: --delete and insert records into hz_purge_gt for an application
1685: appid:=673;
1686: if(regid_proc = true) then
1687: insert into hz_application_trans_gt(app_id,party_id) select 673, temp.party_id from hz_purge_gt temp where
1688:
1683:
1684: --delete and insert records into hz_purge_gt for an application
1685: appid:=673;
1686: if(regid_proc = true) then
1687: insert into hz_application_trans_gt(app_id,party_id) select 673, temp.party_id from hz_purge_gt temp where
1688:
1689:
1690: exists (select 'Y' from IBY_ACCOUNT_OWNERS xx where xx.ACCOUNT_OWNER_PARTY_ID = temp.party_id
1691: )
1698:
1699: or exists (select 'Y' from IBY_EXTERNAL_PAYERS_ALL xx where xx.PARTY_ID = temp.party_id
1700: );
1701: else
1702: delete from hz_purge_gt temp where
1703:
1704:
1705: exists (select 'Y' from IBY_ACCOUNT_OWNERS xx where xx.ACCOUNT_OWNER_PARTY_ID = temp.party_id
1706: )
1715: );
1716: end if;
1717:
1718:
1719: --delete and insert records into hz_purge_gt for an application
1720: appid:=206;
1721: if(regid_proc = true) then
1722: insert into hz_application_trans_gt(app_id,party_id) select 206, temp.party_id from hz_purge_gt temp where
1723:
1718:
1719: --delete and insert records into hz_purge_gt for an application
1720: appid:=206;
1721: if(regid_proc = true) then
1722: insert into hz_application_trans_gt(app_id,party_id) select 206, temp.party_id from hz_purge_gt temp where
1723:
1724:
1725: exists (select 'Y' from LNS_LOAN_HEADERS_ALL xx where xx.PRIMARY_BORROWER_ID = temp.party_id
1726: )
1727:
1728: or exists (select 'Y' from LNS_PARTICIPANTS xx where xx.HZ_PARTY_ID = temp.party_id
1729: );
1730: else
1731: delete from hz_purge_gt temp where
1732:
1733:
1734: exists (select 'Y' from LNS_LOAN_HEADERS_ALL xx where xx.PRIMARY_BORROWER_ID = temp.party_id
1735: )
1738: );
1739: end if;
1740:
1741:
1742: --delete and insert records into hz_purge_gt for an application
1743: appid:=275;
1744: if(regid_proc = true) then
1745: insert into hz_application_trans_gt(app_id,party_id) select 275, temp.party_id from hz_purge_gt temp where
1746:
1741:
1742: --delete and insert records into hz_purge_gt for an application
1743: appid:=275;
1744: if(regid_proc = true) then
1745: insert into hz_application_trans_gt(app_id,party_id) select 275, temp.party_id from hz_purge_gt temp where
1746:
1747:
1748: exists (select 'Y' from PA_CI_ACTIONS xx where xx.ASSIGNED_TO = temp.party_id
1749: )
1771:
1772: or exists (select 'Y' from PA_CONTROL_ITEMS xx where xx.CLOSED_BY_ID = temp.party_id
1773: );
1774: else
1775: delete from hz_purge_gt temp where
1776:
1777:
1778: exists (select 'Y' from PA_CI_ACTIONS xx where xx.ASSIGNED_TO = temp.party_id
1779: )
1803: );
1804: end if;
1805:
1806:
1807: --delete and insert records into hz_purge_gt for an application
1808: appid:=396;
1809: if(regid_proc = true) then
1810: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where
1811:
1806:
1807: --delete and insert records into hz_purge_gt for an application
1808: appid:=396;
1809: if(regid_proc = true) then
1810: insert into hz_application_trans_gt(app_id,party_id) select 396, temp.party_id from hz_purge_gt temp where
1811:
1812:
1813: exists (select 'Y' from PON_TE_RECIPIENTS xx where xx.TO_ID = temp.party_id
1814: )
1842:
1843: or exists (select 'Y' from PON_BID_HEADERS xx where xx.TRADING_PARTNER_ID = temp.party_id
1844: );
1845: else
1846: delete from hz_purge_gt temp where
1847:
1848:
1849: exists (select 'Y' from PON_TE_RECIPIENTS xx where xx.TO_ID = temp.party_id
1850: )
1880: );
1881: end if;
1882:
1883:
1884: --delete and insert records into hz_purge_gt for an application
1885: appid:=540;
1886: if(regid_proc = true) then
1887: insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where
1888:
1883:
1884: --delete and insert records into hz_purge_gt for an application
1885: appid:=540;
1886: if(regid_proc = true) then
1887: insert into hz_application_trans_gt(app_id,party_id) select 540, temp.party_id from hz_purge_gt temp where
1888:
1889:
1890: exists (select 'Y' from HZ_PARTY_SITES
1891: xx where xx.PARTY_ID = temp.party_id
1960: and OWNER_TABLE_NAME='HZ_PARTY_SITES' AND nvl(STATUS, 'A') in ('A','I')
1961: and exists
1962: (select 'Y' from OKL_RELOCATE_ASTS_ALL_B zz where zz.PAC_ID = yy.CONTACT_POINT_ID )));
1963: else
1964: delete from hz_purge_gt temp where
1965:
1966:
1967: exists (select 'Y' from HZ_PARTY_SITES
1968: xx where xx.PARTY_ID = temp.party_id
2039: (select 'Y' from OKL_RELOCATE_ASTS_ALL_B zz where zz.PAC_ID = yy.CONTACT_POINT_ID )));
2040: end if;
2041:
2042:
2043: --delete and insert records into hz_purge_gt for an application
2044: appid:=200;
2045: if(regid_proc = true) then
2046: insert into hz_application_trans_gt(app_id,party_id) select 200, temp.party_id from hz_purge_gt temp where
2047:
2042:
2043: --delete and insert records into hz_purge_gt for an application
2044: appid:=200;
2045: if(regid_proc = true) then
2046: insert into hz_application_trans_gt(app_id,party_id) select 200, temp.party_id from hz_purge_gt temp where
2047:
2048:
2049: exists (select 'Y' from HZ_RELATIONSHIPS
2050: xx where xx.OBJECT_ID = temp.party_id
2124: (select 'Y' from AP_SUPPLIER_CONTACTS
2125: yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
2126: ));
2127: else
2128: delete from hz_purge_gt temp where
2129:
2130:
2131: exists (select 'Y' from HZ_RELATIONSHIPS
2132: xx where xx.OBJECT_ID = temp.party_id
2208: ));
2209: end if;
2210:
2211:
2212: --delete and insert records into hz_purge_gt for an application
2213: appid:=530;
2214: if(regid_proc = true) then
2215: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where
2216:
2211:
2212: --delete and insert records into hz_purge_gt for an application
2213: appid:=530;
2214: if(regid_proc = true) then
2215: insert into hz_application_trans_gt(app_id,party_id) select 530, temp.party_id from hz_purge_gt temp where
2216:
2217:
2218: exists (select 'Y' from AMS_PARTY_SOURCES xx where xx.PARTY_ID = temp.party_id
2219: )
2262:
2263: or exists (select 'Y' from AMS_TCOP_CONTACT_SUMMARY xx where xx.PARTY_ID = temp.party_id
2264: );
2265: else
2266: delete from hz_purge_gt temp where
2267:
2268:
2269: exists (select 'Y' from AMS_PARTY_SOURCES xx where xx.PARTY_ID = temp.party_id
2270: )
2315: );
2316: end if;
2317:
2318:
2319: --delete and insert records into hz_purge_gt for an application
2320: appid:=511;
2321: if(regid_proc = true) then
2322: insert into hz_application_trans_gt(app_id,party_id) select 511, temp.party_id from hz_purge_gt temp where
2323:
2318:
2319: --delete and insert records into hz_purge_gt for an application
2320: appid:=511;
2321: if(regid_proc = true) then
2322: insert into hz_application_trans_gt(app_id,party_id) select 511, temp.party_id from hz_purge_gt temp where
2323:
2324:
2325: exists (select 'Y' from CSC_CUSTOMERS xx where xx.PARTY_ID = temp.party_id
2326: )
2327:
2328: or exists (select 'Y' from CSC_CUSTOMIZED_PLANS xx where xx.PARTY_ID = temp.party_id
2329: );
2330: else
2331: delete from hz_purge_gt temp where
2332:
2333:
2334: exists (select 'Y' from CSC_CUSTOMERS xx where xx.PARTY_ID = temp.party_id
2335: )
2338: );
2339: end if;
2340:
2341:
2342: --delete and insert records into hz_purge_gt for an application
2343: appid:=690;
2344: if(regid_proc = true) then
2345: insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where
2346:
2341:
2342: --delete and insert records into hz_purge_gt for an application
2343: appid:=690;
2344: if(regid_proc = true) then
2345: insert into hz_application_trans_gt(app_id,party_id) select 690, temp.party_id from hz_purge_gt temp where
2346:
2347:
2348: exists (select 'Y' from JTF_TASKS_B xx where xx.CUSTOMER_ID = temp.party_id
2349: )
2535:
2536: or exists (select 'Y' from JTF_TTY_NAMED_ACCTS xx where xx.PARTY_ID = temp.party_id
2537: );
2538: else
2539: delete from hz_purge_gt temp where
2540:
2541:
2542: exists (select 'Y' from JTF_TASKS_B xx where xx.CUSTOMER_ID = temp.party_id
2543: )
2731: );
2732: end if;
2733:
2734:
2735: --delete and insert records into hz_purge_gt for an application
2736: appid:=515;
2737: if(regid_proc = true) then
2738: insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where
2739:
2734:
2735: --delete and insert records into hz_purge_gt for an application
2736: appid:=515;
2737: if(regid_proc = true) then
2738: insert into hz_application_trans_gt(app_id,party_id) select 515, temp.party_id from hz_purge_gt temp where
2739:
2740:
2741: exists (select 'Y' from OKS_BILLING_PROFILES_B xx where xx.OWNED_PARTY_ID1 = to_char(temp.party_id)
2742: )
2746:
2747: or exists (select 'Y' from OKS_SERV_AVAIL_EXCEPTS xx where xx.OBJECT1_ID1 = to_char(temp.party_id)
2748: and (JTOT_OBJECT1_CODE = 'OKX_PARTY'));
2749: else
2750: delete from hz_purge_gt temp where
2751:
2752:
2753: exists (select 'Y' from OKS_BILLING_PROFILES_B xx where xx.OWNED_PARTY_ID1 = to_char(temp.party_id)
2754: )
2760: and (JTOT_OBJECT1_CODE = 'OKX_PARTY'));
2761: end if;
2762:
2763:
2764: --delete and insert records into hz_purge_gt for an application
2765: appid:=0;
2766: if(regid_proc = true) then
2767: insert into hz_application_trans_gt(app_id,party_id) select 0, temp.party_id from hz_purge_gt temp where
2768:
2763:
2764: --delete and insert records into hz_purge_gt for an application
2765: appid:=0;
2766: if(regid_proc = true) then
2767: insert into hz_application_trans_gt(app_id,party_id) select 0, temp.party_id from hz_purge_gt temp where
2768:
2769:
2770: exists (select 'Y' from FND_USER xx where xx.CUSTOMER_ID = temp.party_id
2771: )
2775:
2776: or exists (select 'Y' from UMX_REG_REQUESTS xx where xx.REQUESTED_FOR_PARTY_ID = temp.party_id
2777: and (ROWNUM < 2));
2778: else
2779: delete from hz_purge_gt temp where
2780:
2781:
2782: exists (select 'Y' from FND_USER xx where xx.CUSTOMER_ID = temp.party_id
2783: )
2789: and (ROWNUM < 2));
2790: end if;
2791:
2792:
2793: --delete and insert records into hz_purge_gt for an application
2794: appid:=170;
2795: if(regid_proc = true) then
2796: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where
2797:
2792:
2793: --delete and insert records into hz_purge_gt for an application
2794: appid:=170;
2795: if(regid_proc = true) then
2796: insert into hz_application_trans_gt(app_id,party_id) select 170, temp.party_id from hz_purge_gt temp where
2797:
2798:
2799: exists (select 'Y' from CS_INCIDENTS_ALL_B xx where xx.CUSTOMER_ID = temp.party_id
2800: )
2817: (select 'Y' from CS_ESTIMATE_DETAILS
2818: yy where yy.SHIP_TO_ORG_ID=xx.PARTY_SITE_ID
2819: ));
2820: else
2821: delete from hz_purge_gt temp where
2822:
2823:
2824: exists (select 'Y' from CS_INCIDENTS_ALL_B xx where xx.CUSTOMER_ID = temp.party_id
2825: )
2844: ));
2845: end if;
2846:
2847:
2848: --delete and insert records into hz_purge_gt for an application
2849: appid:=660;
2850: if(regid_proc = true) then
2851: insert into hz_application_trans_gt(app_id,party_id) select 660, temp.party_id from hz_purge_gt temp where
2852:
2847:
2848: --delete and insert records into hz_purge_gt for an application
2849: appid:=660;
2850: if(regid_proc = true) then
2851: insert into hz_application_trans_gt(app_id,party_id) select 660, temp.party_id from hz_purge_gt temp where
2852:
2853:
2854: exists (select 'Y' from OE_PRICE_ADJ_ATTRIBS xx where xx.PRICING_ATTR_VALUE_FROM = to_char(temp.party_id)
2855: 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'))))
2861: (select 'Y' from OE_PRICE_ADJ_ATTRIBS
2862: yy where yy.PRICING_ATTR_VALUE_FROM=to_char(xx.PARTY_SITE_ID)
2863: and (pricing_context = 'ASOPARTYINFO' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR pricing_context = 'CUSTOMER' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
2864: else
2865: delete from hz_purge_gt temp where
2866:
2867:
2868: exists (select 'Y' from OE_PRICE_ADJ_ATTRIBS xx where xx.PRICING_ATTR_VALUE_FROM = to_char(temp.party_id)
2869: 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'))))
2877: and (pricing_context = 'ASOPARTYINFO' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR pricing_context = 'CUSTOMER' AND pricing_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
2878: end if;
2879:
2880:
2881: --delete and insert records into hz_purge_gt for an application
2882: appid:=800;
2883: if(regid_proc = true) then
2884: insert into hz_application_trans_gt(app_id,party_id) select 800, temp.party_id from hz_purge_gt temp where
2885:
2880:
2881: --delete and insert records into hz_purge_gt for an application
2882: appid:=800;
2883: if(regid_proc = true) then
2884: insert into hz_application_trans_gt(app_id,party_id) select 800, temp.party_id from hz_purge_gt temp where
2885:
2886:
2887: exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
2888: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2886:
2887: exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
2888: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2889: else
2890: delete from hz_purge_gt temp where
2891:
2892:
2893: exists (select 'Y' from PER_ALL_PEOPLE_F xx where xx.PARTY_ID = temp.party_id
2894: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2894: and (effective_end_date = to_date('12/31/4712','MM/DD/YYYY')));
2895: end if;
2896:
2897:
2898: --delete and insert records into hz_purge_gt for an application
2899: appid:=174;
2900: if(regid_proc = true) then
2901: insert into hz_application_trans_gt(app_id,party_id) select 174, temp.party_id from hz_purge_gt temp where
2902:
2897:
2898: --delete and insert records into hz_purge_gt for an application
2899: appid:=174;
2900: if(regid_proc = true) then
2901: insert into hz_application_trans_gt(app_id,party_id) select 174, temp.party_id from hz_purge_gt temp where
2902:
2903:
2904: exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
2905: );
2903:
2904: exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
2905: );
2906: else
2907: delete from hz_purge_gt temp where
2908:
2909:
2910: exists (select 'Y' from ECX_TP_HEADERS xx where xx.PARTY_ID = temp.party_id
2911: );
2911: );
2912: end if;
2913:
2914:
2915: --delete and insert records into hz_purge_gt for an application
2916: appid:=9004;
2917: if(regid_proc = true) then
2918: insert into hz_application_trans_gt(app_id,party_id) select 9004, temp.party_id from hz_purge_gt temp where
2919:
2914:
2915: --delete and insert records into hz_purge_gt for an application
2916: appid:=9004;
2917: if(regid_proc = true) then
2918: insert into hz_application_trans_gt(app_id,party_id) select 9004, temp.party_id from hz_purge_gt temp where
2919:
2920:
2921: exists (select 'Y' from INL_CHARGE_LINES xx where xx.PARTY_ID = temp.party_id
2922: )
2928: (select 'Y' from INL_CHARGE_LINES
2929: yy where yy.PARTY_SITE_ID=xx.PARTY_SITE_ID
2930: ));
2931: else
2932: delete from hz_purge_gt temp where
2933:
2934:
2935: exists (select 'Y' from INL_CHARGE_LINES xx where xx.PARTY_ID = temp.party_id
2936: )
2944: ));
2945: end if;
2946:
2947:
2948: --delete and insert records into hz_purge_gt for an application
2949: appid:=204;
2950: if(regid_proc = true) then
2951: insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where
2952:
2947:
2948: --delete and insert records into hz_purge_gt for an application
2949: appid:=204;
2950: if(regid_proc = true) then
2951: insert into hz_application_trans_gt(app_id,party_id) select 204, temp.party_id from hz_purge_gt temp where
2952:
2953:
2954: exists (select 'Y' from XLE_ENTITY_PROFILES xx where xx.PARTY_ID = temp.party_id
2955: )
2956:
2957: or exists (select 'Y' from XLE_ETB_PROFILES xx where xx.PARTY_ID = temp.party_id
2958: );
2959: else
2960: delete from hz_purge_gt temp where
2961:
2962:
2963: exists (select 'Y' from XLE_ENTITY_PROFILES xx where xx.PARTY_ID = temp.party_id
2964: )
2967: );
2968: end if;
2969:
2970:
2971: --delete and insert records into hz_purge_gt for an application
2972: appid:=666;
2973: if(regid_proc = true) then
2974: insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where
2975:
2970:
2971: --delete and insert records into hz_purge_gt for an application
2972: appid:=666;
2973: if(regid_proc = true) then
2974: insert into hz_application_trans_gt(app_id,party_id) select 666, temp.party_id from hz_purge_gt temp where
2975:
2976:
2977: exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
2978: );
2976:
2977: exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
2978: );
2979: else
2980: delete from hz_purge_gt temp where
2981:
2982:
2983: exists (select 'Y' from IBW_SITE_VISITS xx where xx.PARTY_ID = temp.party_id
2984: );
2987:
2988: --WSH_PARTY_SITES_V;PARTY_SITE_ID
2989: appid:=665;
2990: if(regid_proc = true) then
2991: 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
2992:
2993: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
2994: xx
2995: where (nvl(STATUS, 'A') in ('A','I'))
2997: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from WSH_PARTY_SITES_V
2998: yy
2999: ));
3000: else
3001: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3002: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3003: xx
3004: where (nvl(STATUS, 'A') in ('A','I'))
3005: and xx.PARTY_SITE_ID
3009: end if;
3010: --HZ_WORK_CLASS;EMPLOYMENT_HISTORY_ID
3011: appid:=222;
3012: if(regid_proc = true) then
3013: 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
3014:
3015: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_EMPLOYMENT_HISTORY
3016: xx
3017: where (nvl(STATUS, 'A') in ('A','I'))
3026: in (select /*+ parallel(yy)*/ yy.EMPLOYMENT_HISTORY_ID from HZ_WORK_CLASS
3027: yy
3028: where nvl(STATUS, 'A') in ('A','I')));
3029: else
3030: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3031: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_EMPLOYMENT_HISTORY
3032: xx
3033: where (nvl(STATUS, 'A') in ('A','I'))
3034: and xx.EMPLOYMENT_HISTORY_ID
3045: end if;
3046: --FND_ATTACHED_DOCUMENTS;PK1_VALUE
3047: appid:=222;
3048: if(regid_proc = true) then
3049: 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
3050:
3051: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PK1_VALUE from FND_ATTACHED_DOCUMENTS xx
3052: where (entity_name = 'HZ_PARTIES'));
3053: else
3050:
3051: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PK1_VALUE from FND_ATTACHED_DOCUMENTS xx
3052: where (entity_name = 'HZ_PARTIES'));
3053: else
3054: delete /*+ parallel(temp) */ from hz_purge_gt temp where
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: end if;
3058: --HZ_EMAIL_DOMAINS;PARTY_ID
3057: end if;
3058: --HZ_EMAIL_DOMAINS;PARTY_ID
3059: appid:=222;
3060: if(regid_proc = true) then
3061: 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
3062:
3063: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
3064: );
3065: else
3062:
3063: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
3064: );
3065: else
3066: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3067: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from HZ_EMAIL_DOMAINS xx
3068: );
3069: end if;
3070: --IBE_SH_QUOTE_ACCESS;PARTY_ID
3069: end if;
3070: --IBE_SH_QUOTE_ACCESS;PARTY_ID
3071: appid:=671;
3072: if(regid_proc = true) then
3073: 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
3074:
3075: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
3076: );
3077: else
3074:
3075: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
3076: );
3077: else
3078: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3079: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_SH_QUOTE_ACCESS xx
3080: );
3081: end if;
3082: --IBE_ACTIVE_QUOTES_ALL;PARTY_ID
3081: end if;
3082: --IBE_ACTIVE_QUOTES_ALL;PARTY_ID
3083: appid:=671;
3084: if(regid_proc = true) then
3085: 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
3086:
3087: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
3088: );
3089: else
3086:
3087: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
3088: );
3089: else
3090: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3091: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBE_ACTIVE_QUOTES_ALL xx
3092: );
3093: end if;
3094: --OZF_CLAIMS_HISTORY_ALL;BROKER_ID
3093: end if;
3094: --OZF_CLAIMS_HISTORY_ALL;BROKER_ID
3095: appid:=682;
3096: if(regid_proc = true) then
3097: 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
3098:
3099: temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
3100: );
3101: else
3098:
3099: temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
3100: );
3101: else
3102: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3103: temp.party_id in (select /*+ parallel(xx)*/ xx.BROKER_ID from OZF_CLAIMS_HISTORY_ALL xx
3104: );
3105: end if;
3106: --OZF_CLAIMS_HISTORY_ALL;CONTACT_ID
3105: end if;
3106: --OZF_CLAIMS_HISTORY_ALL;CONTACT_ID
3107: appid:=682;
3108: if(regid_proc = true) then
3109: 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
3110:
3111: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
3112: );
3113: else
3110:
3111: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
3112: );
3113: else
3114: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3115: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_ID from OZF_CLAIMS_HISTORY_ALL xx
3116: );
3117: end if;
3118: --OZF_RESALE_LINES_INT_ALL;SHIP_FROM_CONTACT_PARTY_ID
3117: end if;
3118: --OZF_RESALE_LINES_INT_ALL;SHIP_FROM_CONTACT_PARTY_ID
3119: appid:=682;
3120: if(regid_proc = true) then
3121: 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
3122:
3123: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3124: );
3125: else
3122:
3123: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3124: );
3125: else
3126: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3127: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3128: );
3129: end if;
3130: --OZF_RESALE_LINES_INT_ALL;SOLD_FROM_CONTACT_PARTY_ID
3129: end if;
3130: --OZF_RESALE_LINES_INT_ALL;SOLD_FROM_CONTACT_PARTY_ID
3131: appid:=682;
3132: if(regid_proc = true) then
3133: 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
3134:
3135: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3136: );
3137: else
3134:
3135: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3136: );
3137: else
3138: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3139: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3140: );
3141: end if;
3142: --OZF_RESALE_LINES_INT_ALL;BILL_TO_PARTY_ID
3141: end if;
3142: --OZF_RESALE_LINES_INT_ALL;BILL_TO_PARTY_ID
3143: appid:=682;
3144: if(regid_proc = true) then
3145: 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
3146:
3147: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3148: );
3149: else
3146:
3147: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3148: );
3149: else
3150: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3151: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3152: );
3153: end if;
3154: --OZF_RESALE_LINES_INT_ALL;BILL_TO_CONTACT_PARTY_ID
3153: end if;
3154: --OZF_RESALE_LINES_INT_ALL;BILL_TO_CONTACT_PARTY_ID
3155: appid:=682;
3156: if(regid_proc = true) then
3157: 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
3158:
3159: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3160: );
3161: else
3158:
3159: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3160: );
3161: else
3162: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3163: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3164: );
3165: end if;
3166: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_PARTY_ID
3165: end if;
3166: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_PARTY_ID
3167: appid:=682;
3168: if(regid_proc = true) then
3169: 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
3170:
3171: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3172: );
3173: else
3170:
3171: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3172: );
3173: else
3174: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3175: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3176: );
3177: end if;
3178: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_CONTACT_PARTY_ID
3177: end if;
3178: --OZF_RESALE_LINES_INT_ALL;SHIP_TO_CONTACT_PARTY_ID
3179: appid:=682;
3180: if(regid_proc = true) then
3181: 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
3182:
3183: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3184: );
3185: else
3182:
3183: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3184: );
3185: else
3186: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3187: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3188: );
3189: end if;
3190: --OZF_RESALE_LINES_INT_ALL;END_CUST_PARTY_ID
3189: end if;
3190: --OZF_RESALE_LINES_INT_ALL;END_CUST_PARTY_ID
3191: appid:=682;
3192: if(regid_proc = true) then
3193: 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
3194:
3195: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3196: );
3197: else
3194:
3195: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3196: );
3197: else
3198: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3199: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3200: );
3201: end if;
3202: --OZF_RESALE_LINES_INT_ALL;END_CUST_CONTACT_PARTY_ID
3201: end if;
3202: --OZF_RESALE_LINES_INT_ALL;END_CUST_CONTACT_PARTY_ID
3203: appid:=682;
3204: if(regid_proc = true) then
3205: 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
3206:
3207: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3208: );
3209: else
3206:
3207: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3208: );
3209: else
3210: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3211: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_INT_ALL xx
3212: );
3213: end if;
3214: --OZF_RESALE_HEADERS_ALL;BILL_TO_PARTY_ID
3213: end if;
3214: --OZF_RESALE_HEADERS_ALL;BILL_TO_PARTY_ID
3215: appid:=682;
3216: if(regid_proc = true) then
3217: 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
3218:
3219: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3220: );
3221: else
3218:
3219: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3220: );
3221: else
3222: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3223: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3224: );
3225: end if;
3226: --OZF_RESALE_HEADERS_ALL;BILL_TO_CONTACT_PARTY_ID
3225: end if;
3226: --OZF_RESALE_HEADERS_ALL;BILL_TO_CONTACT_PARTY_ID
3227: appid:=682;
3228: if(regid_proc = true) then
3229: 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
3230:
3231: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3232: );
3233: else
3230:
3231: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3232: );
3233: else
3234: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3235: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3236: );
3237: end if;
3238: --OZF_RESALE_HEADERS_ALL;SHIP_TO_PARTY_ID
3237: end if;
3238: --OZF_RESALE_HEADERS_ALL;SHIP_TO_PARTY_ID
3239: appid:=682;
3240: if(regid_proc = true) then
3241: 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
3242:
3243: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3244: );
3245: else
3242:
3243: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3244: );
3245: else
3246: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3247: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3248: );
3249: end if;
3250: --OZF_RESALE_HEADERS_ALL;SHIP_TO_CONTACT_PARTY_ID
3249: end if;
3250: --OZF_RESALE_HEADERS_ALL;SHIP_TO_CONTACT_PARTY_ID
3251: appid:=682;
3252: if(regid_proc = true) then
3253: 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
3254:
3255: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3256: );
3257: else
3254:
3255: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3256: );
3257: else
3258: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3259: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_HEADERS_ALL xx
3260: );
3261: end if;
3262: --OZF_RESALE_LINES_ALL;SHIP_FROM_CONTACT_PARTY_ID
3261: end if;
3262: --OZF_RESALE_LINES_ALL;SHIP_FROM_CONTACT_PARTY_ID
3263: appid:=682;
3264: if(regid_proc = true) then
3265: 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
3266:
3267: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3268: );
3269: else
3266:
3267: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3268: );
3269: else
3270: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3271: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3272: );
3273: end if;
3274: --OZF_RESALE_LINES_ALL;SOLD_FROM_CONTACT_PARTY_ID
3273: end if;
3274: --OZF_RESALE_LINES_ALL;SOLD_FROM_CONTACT_PARTY_ID
3275: appid:=682;
3276: if(regid_proc = true) then
3277: 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
3278:
3279: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3280: );
3281: else
3278:
3279: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3280: );
3281: else
3282: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3283: temp.party_id in (select /*+ parallel(xx)*/ xx.SOLD_FROM_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3284: );
3285: end if;
3286: --OZF_RESALE_LINES_ALL;BILL_TO_PARTY_ID
3285: end if;
3286: --OZF_RESALE_LINES_ALL;BILL_TO_PARTY_ID
3287: appid:=682;
3288: if(regid_proc = true) then
3289: 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
3290:
3291: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3292: );
3293: else
3290:
3291: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3292: );
3293: else
3294: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3295: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3296: );
3297: end if;
3298: --OZF_RESALE_LINES_ALL;BILL_TO_CONTACT_PARTY_ID
3297: end if;
3298: --OZF_RESALE_LINES_ALL;BILL_TO_CONTACT_PARTY_ID
3299: appid:=682;
3300: if(regid_proc = true) then
3301: 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
3302:
3303: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3304: );
3305: else
3302:
3303: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3304: );
3305: else
3306: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3307: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3308: );
3309: end if;
3310: --OZF_RESALE_LINES_ALL;SHIP_TO_PARTY_ID
3309: end if;
3310: --OZF_RESALE_LINES_ALL;SHIP_TO_PARTY_ID
3311: appid:=682;
3312: if(regid_proc = true) then
3313: 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
3314:
3315: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3316: );
3317: else
3314:
3315: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3316: );
3317: else
3318: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3319: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OZF_RESALE_LINES_ALL xx
3320: );
3321: end if;
3322: --OZF_RESALE_LINES_ALL;SHIP_TO_CONTACT_PARTY_ID
3321: end if;
3322: --OZF_RESALE_LINES_ALL;SHIP_TO_CONTACT_PARTY_ID
3323: appid:=682;
3324: if(regid_proc = true) then
3325: 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
3326:
3327: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3328: );
3329: else
3326:
3327: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3328: );
3329: else
3330: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3331: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3332: );
3333: end if;
3334: --OZF_RESALE_LINES_ALL;END_CUST_PARTY_ID
3333: end if;
3334: --OZF_RESALE_LINES_ALL;END_CUST_PARTY_ID
3335: appid:=682;
3336: if(regid_proc = true) then
3337: 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
3338:
3339: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
3340: );
3341: else
3338:
3339: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
3340: );
3341: else
3342: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3343: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_RESALE_LINES_ALL xx
3344: );
3345: end if;
3346: --OZF_RESALE_LINES_ALL;END_CUST_CONTACT_PARTY_ID
3345: end if;
3346: --OZF_RESALE_LINES_ALL;END_CUST_CONTACT_PARTY_ID
3347: appid:=682;
3348: if(regid_proc = true) then
3349: 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
3350:
3351: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3352: );
3353: else
3350:
3351: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3352: );
3353: else
3354: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3355: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_CONTACT_PARTY_ID from OZF_RESALE_LINES_ALL xx
3356: );
3357: end if;
3358: --OZF_REQUEST_HEADERS_ALL_B;END_CUST_PARTY_ID
3357: end if;
3358: --OZF_REQUEST_HEADERS_ALL_B;END_CUST_PARTY_ID
3359: appid:=682;
3360: if(regid_proc = true) then
3361: 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
3362:
3363: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3364: );
3365: else
3362:
3363: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3364: );
3365: else
3366: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3367: temp.party_id in (select /*+ parallel(xx)*/ xx.END_CUST_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3368: );
3369: end if;
3370: --OZF_REQUEST_HEADERS_ALL_B;RESELLER_PARTY_ID
3369: end if;
3370: --OZF_REQUEST_HEADERS_ALL_B;RESELLER_PARTY_ID
3371: appid:=682;
3372: if(regid_proc = true) then
3373: 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
3374:
3375: temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3376: );
3377: else
3374:
3375: temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3376: );
3377: else
3378: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3379: temp.party_id in (select /*+ parallel(xx)*/ xx.RESELLER_PARTY_ID from OZF_REQUEST_HEADERS_ALL_B xx
3380: );
3381: end if;
3382: --OZF_OFFERS;AUTOPAY_PARTY_ID
3381: end if;
3382: --OZF_OFFERS;AUTOPAY_PARTY_ID
3383: appid:=682;
3384: if(regid_proc = true) then
3385: 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
3386:
3387: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
3388: );
3389: else
3386:
3387: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
3388: );
3389: else
3390: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3391: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTOPAY_PARTY_ID from OZF_OFFERS xx
3392: );
3393: end if;
3394: --OZF_ACTIVITY_CUSTOMERS;PARTY_ID
3393: end if;
3394: --OZF_ACTIVITY_CUSTOMERS;PARTY_ID
3395: appid:=682;
3396: if(regid_proc = true) then
3397: 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
3398:
3399: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
3400: );
3401: else
3398:
3399: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
3400: );
3401: else
3402: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3403: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OZF_ACTIVITY_CUSTOMERS xx
3404: );
3405: end if;
3406: --OZF_CLAIM_LINES_HIST_ALL;BUY_GROUP_PARTY_ID
3405: end if;
3406: --OZF_CLAIM_LINES_HIST_ALL;BUY_GROUP_PARTY_ID
3407: appid:=682;
3408: if(regid_proc = true) then
3409: 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
3410:
3411: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
3412: );
3413: else
3410:
3411: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
3412: );
3413: else
3414: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3415: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_HIST_ALL xx
3416: );
3417: end if;
3418: --OZF_RESALE_BATCHES_ALL;PARTNER_CONTACT_PARTY_ID
3417: end if;
3418: --OZF_RESALE_BATCHES_ALL;PARTNER_CONTACT_PARTY_ID
3419: appid:=682;
3420: if(regid_proc = true) then
3421: 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
3422:
3423: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
3424: );
3425: else
3422:
3423: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
3424: );
3425: else
3426: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3427: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTNER_CONTACT_PARTY_ID from OZF_RESALE_BATCHES_ALL xx
3428: );
3429: end if;
3430: --OZF_CLAIM_LINES_ALL;BUY_GROUP_PARTY_ID
3429: end if;
3430: --OZF_CLAIM_LINES_ALL;BUY_GROUP_PARTY_ID
3431: appid:=682;
3432: if(regid_proc = true) then
3433: 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
3434:
3435: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
3436: );
3437: else
3434:
3435: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
3436: );
3437: else
3438: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3439: temp.party_id in (select /*+ parallel(xx)*/ xx.BUY_GROUP_PARTY_ID from OZF_CLAIM_LINES_ALL xx
3440: );
3441: end if;
3442: --OZF_ACCOUNT_ALLOCATIONS;PARENT_PARTY_ID
3441: end if;
3442: --OZF_ACCOUNT_ALLOCATIONS;PARENT_PARTY_ID
3443: appid:=682;
3444: if(regid_proc = true) then
3445: 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
3446:
3447: temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3448: );
3449: else
3446:
3447: temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3448: );
3449: else
3450: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3451: temp.party_id in (select /*+ parallel(xx)*/ xx.PARENT_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3452: );
3453: end if;
3454: --OZF_ACCOUNT_ALLOCATIONS;ROLLUP_PARTY_ID
3453: end if;
3454: --OZF_ACCOUNT_ALLOCATIONS;ROLLUP_PARTY_ID
3455: appid:=682;
3456: if(regid_proc = true) then
3457: 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
3458:
3459: temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3460: );
3461: else
3458:
3459: temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3460: );
3461: else
3462: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3463: temp.party_id in (select /*+ parallel(xx)*/ xx.ROLLUP_PARTY_ID from OZF_ACCOUNT_ALLOCATIONS xx
3464: );
3465: end if;
3466: --FUN_TRX_BATCHES;INITIATOR_ID
3465: end if;
3466: --FUN_TRX_BATCHES;INITIATOR_ID
3467: appid:=435;
3468: if(regid_proc = true) then
3469: 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
3470:
3471: temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
3472: );
3473: else
3470:
3471: temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
3472: );
3473: else
3474: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3475: temp.party_id in (select /*+ parallel(xx)*/ xx.INITIATOR_ID from FUN_TRX_BATCHES xx
3476: );
3477: end if;
3478: --FUN_TRX_HEADERS;RECIPIENT_ID
3477: end if;
3478: --FUN_TRX_HEADERS;RECIPIENT_ID
3479: appid:=435;
3480: if(regid_proc = true) then
3481: 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
3482:
3483: temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
3484: );
3485: else
3482:
3483: temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
3484: );
3485: else
3486: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3487: temp.party_id in (select /*+ parallel(xx)*/ xx.RECIPIENT_ID from FUN_TRX_HEADERS xx
3488: );
3489: end if;
3490: --FUN_DIST_LINES;PARTY_ID
3489: end if;
3490: --FUN_DIST_LINES;PARTY_ID
3491: appid:=435;
3492: if(regid_proc = true) then
3493: 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
3494:
3495: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
3496: );
3497: else
3494:
3495: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
3496: );
3497: else
3498: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3499: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from FUN_DIST_LINES xx
3500: );
3501: end if;
3502: --FUN_SUPPLIER_MAPS;VENDOR_SITE_ID
3501: end if;
3502: --FUN_SUPPLIER_MAPS;VENDOR_SITE_ID
3503: appid:=435;
3504: if(regid_proc = true) then
3505: 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
3506:
3507: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3508: xx
3509: where (nvl(STATUS, 'A') in ('A','I'))
3511: in (select /*+ parallel(yy)*/ yy.VENDOR_SITE_ID from FUN_SUPPLIER_MAPS
3512: yy
3513: ));
3514: else
3515: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3516: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3517: xx
3518: where (nvl(STATUS, 'A') in ('A','I'))
3519: and xx.PARTY_SITE_ID
3523: end if;
3524: --XDP_ORDER_HEADERS;CUSTOMER_ID
3525: appid:=535;
3526: if(regid_proc = true) then
3527: 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
3528:
3529: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
3530: );
3531: else
3528:
3529: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
3530: );
3531: else
3532: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3533: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from XDP_ORDER_HEADERS xx
3534: );
3535: end if;
3536: --QA_RESULTS;PARTY_ID
3535: end if;
3536: --QA_RESULTS;PARTY_ID
3537: appid:=250;
3538: if(regid_proc = true) then
3539: 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
3540:
3541: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
3542: );
3543: else
3540:
3541: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
3542: );
3543: else
3544: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3545: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from QA_RESULTS xx
3546: );
3547: end if;
3548: --HZ_PARTY_USG_ASSIGNMENTS;PARTY_ID
3547: end if;
3548: --HZ_PARTY_USG_ASSIGNMENTS;PARTY_ID
3549: appid:=177;
3550: if(regid_proc = true) then
3551: insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
3552:
3553: temp.party_id in (select /*+ parallel (xx)*/ xx.OBJECT_ID from HZ_RELATIONSHIPS
3554: xx
3555: where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
3574: in (select /*+ parallel(yy)*/ yy.PARTY_ID from HZ_PARTY_USG_ASSIGNMENTS
3575: yy
3576: ));
3577: else
3578: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3579: temp.party_id in (select /*+ parallel (xx)*/ xx.OBJECT_ID from HZ_RELATIONSHIPS
3580: xx
3581: where (nvl(STATUS, 'A') in ('A','I') AND subject_table_name = 'HZ_PARTIES' AND object_table_name = 'HZ_PARTIES'
3582: AND directional_flag = 'F')
3603: end if;
3604: --POS_PARTY_SITE_UDA_V;PARTY_SITE_ID
3605: appid:=177;
3606: if(regid_proc = true) then
3607: insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
3608:
3609: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3610: xx
3611: where (nvl(STATUS, 'A') in ('A','I'))
3613: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from POS_PARTY_SITE_UDA_V
3614: yy
3615: ));
3616: else
3617: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3618: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3619: xx
3620: where (nvl(STATUS, 'A') in ('A','I'))
3621: and xx.PARTY_SITE_ID
3625: end if;
3626: --AP_SUPPLIERS;PARTY_ID
3627: appid:=177;
3628: if(regid_proc = true) then
3629: insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
3630:
3631: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AP_SUPPLIERS xx
3632: );
3633: else
3630:
3631: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AP_SUPPLIERS xx
3632: );
3633: else
3634: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3635: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AP_SUPPLIERS xx
3636: );
3637: end if;
3638: --POS_PS_MERGE_V;PARTY_ID
3637: end if;
3638: --POS_PS_MERGE_V;PARTY_ID
3639: appid:=177;
3640: if(regid_proc = true) then
3641: insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
3642:
3643: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_PS_MERGE_V xx
3644: );
3645: else
3642:
3643: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_PS_MERGE_V xx
3644: );
3645: else
3646: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3647: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_PS_MERGE_V xx
3648: );
3649: end if;
3650: --POS_BANK_MERGE_V;PARTY_ID
3649: end if;
3650: --POS_BANK_MERGE_V;PARTY_ID
3651: appid:=177;
3652: if(regid_proc = true) then
3653: insert into hz_application_trans_gt(app_id,party_id) select 177, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 177 and appl.party_id=temp.party_id) and
3654:
3655: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_BANK_MERGE_V xx
3656: );
3657: else
3654:
3655: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_BANK_MERGE_V xx
3656: );
3657: else
3658: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3659: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from POS_BANK_MERGE_V xx
3660: );
3661: end if;
3662: --PRP_EMAIL_RECIPIENTS;PARTY_ID
3661: end if;
3662: --PRP_EMAIL_RECIPIENTS;PARTY_ID
3663: appid:=694;
3664: if(regid_proc = true) then
3665: 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
3666:
3667: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
3668: );
3669: else
3666:
3667: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
3668: );
3669: else
3670: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3671: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PRP_EMAIL_RECIPIENTS xx
3672: );
3673: end if;
3674: --PRP_PROPOSALS;CONTACT_PARTY_ID
3673: end if;
3674: --PRP_PROPOSALS;CONTACT_PARTY_ID
3675: appid:=694;
3676: if(regid_proc = true) then
3677: 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
3678:
3679: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
3680: );
3681: else
3678:
3679: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
3680: );
3681: else
3682: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3683: temp.party_id in (select /*+ parallel(xx)*/ xx.CONTACT_PARTY_ID from PRP_PROPOSALS xx
3684: );
3685: end if;
3686: --CSI_ITEM_INSTANCES;LOCATION_ID
3685: end if;
3686: --CSI_ITEM_INSTANCES;LOCATION_ID
3687: appid:=542;
3688: if(regid_proc = true) then
3689: 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
3690:
3691: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3692: xx
3693: where (nvl(STATUS, 'A') in ('A','I'))
3695: in (select /*+ parallel(yy)*/ yy.LOCATION_ID from CSI_ITEM_INSTANCES
3696: yy
3697: ));
3698: else
3699: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3700: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3701: xx
3702: where (nvl(STATUS, 'A') in ('A','I'))
3703: and xx.PARTY_SITE_ID
3707: end if;
3708: --CSI_ITEM_INSTANCES;INSTALL_LOCATION_ID
3709: appid:=542;
3710: if(regid_proc = true) then
3711: 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
3712:
3713: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3714: xx
3715: where (nvl(STATUS, 'A') in ('A','I'))
3717: in (select /*+ parallel(yy)*/ yy.INSTALL_LOCATION_ID from CSI_ITEM_INSTANCES
3718: yy
3719: ));
3720: else
3721: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3722: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3723: xx
3724: where (nvl(STATUS, 'A') in ('A','I'))
3725: and xx.PARTY_SITE_ID
3729: end if;
3730: --CSI_T_TXN_SYSTEMS;INSTALL_SITE_USE_ID
3731: appid:=542;
3732: if(regid_proc = true) then
3733: 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
3734:
3735: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3736: xx
3737: where (nvl(STATUS, 'A') in ('A','I'))
3739: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CSI_T_TXN_SYSTEMS
3740: yy
3741: ));
3742: else
3743: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3744: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3745: xx
3746: where (nvl(STATUS, 'A') in ('A','I'))
3747: and xx.PARTY_SITE_ID
3751: end if;
3752: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_PARTY_ID
3753: appid:=697;
3754: if(regid_proc = true) then
3755: 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
3756:
3757: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3758: );
3759: else
3756:
3757: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3758: );
3759: else
3760: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3761: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3762: );
3763: end if;
3764: --ASO_QUOTE_LINES_ALL;INVOICE_TO_PARTY_ID
3763: end if;
3764: --ASO_QUOTE_LINES_ALL;INVOICE_TO_PARTY_ID
3765: appid:=697;
3766: if(regid_proc = true) then
3767: 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
3768:
3769: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3770: );
3771: else
3768:
3769: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3770: );
3771: else
3772: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3773: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3774: );
3775: end if;
3776: --ASO_SHIPMENTS;SHIP_TO_PARTY_ID
3775: end if;
3776: --ASO_SHIPMENTS;SHIP_TO_PARTY_ID
3777: appid:=697;
3778: if(regid_proc = true) then
3779: 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
3780:
3781: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
3782: );
3783: else
3780:
3781: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
3782: );
3783: else
3784: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3785: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from ASO_SHIPMENTS xx
3786: );
3787: end if;
3788: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_PARTY_SITE_ID
3787: end if;
3788: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_PARTY_SITE_ID
3789: appid:=697;
3790: if(regid_proc = true) then
3791: 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
3792:
3793: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3794: xx
3795: where (nvl(STATUS, 'A') in ('A','I'))
3797: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
3798: yy
3799: ));
3800: else
3801: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3802: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3803: xx
3804: where (nvl(STATUS, 'A') in ('A','I'))
3805: and xx.PARTY_SITE_ID
3809: end if;
3810: --ASO_QUOTE_LINES_ALL;INVOICE_TO_PARTY_SITE_ID
3811: appid:=697;
3812: if(regid_proc = true) then
3813: 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
3814:
3815: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3816: xx
3817: where (nvl(STATUS, 'A') in ('A','I'))
3819: in (select /*+ parallel(yy)*/ yy.INVOICE_TO_PARTY_SITE_ID from ASO_QUOTE_LINES_ALL
3820: yy
3821: ));
3822: else
3823: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3824: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3825: xx
3826: where (nvl(STATUS, 'A') in ('A','I'))
3827: and xx.PARTY_SITE_ID
3831: end if;
3832: --ASO_QUOTE_HEADERS_ALL;INVOICE_TO_CUST_PARTY_ID
3833: appid:=697;
3834: if(regid_proc = true) then
3835: 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
3836:
3837: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3838: );
3839: else
3836:
3837: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3838: );
3839: else
3840: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3841: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_HEADERS_ALL xx
3842: );
3843: end if;
3844: --ASO_QUOTE_HEADERS_ALL;SOLD_TO_PARTY_SITE_ID
3843: end if;
3844: --ASO_QUOTE_HEADERS_ALL;SOLD_TO_PARTY_SITE_ID
3845: appid:=697;
3846: if(regid_proc = true) then
3847: 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
3848:
3849: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3850: xx
3851: where (nvl(STATUS, 'A') in ('A','I'))
3853: in (select /*+ parallel(yy)*/ yy.SOLD_TO_PARTY_SITE_ID from ASO_QUOTE_HEADERS_ALL
3854: yy
3855: ));
3856: else
3857: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3858: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3859: xx
3860: where (nvl(STATUS, 'A') in ('A','I'))
3861: and xx.PARTY_SITE_ID
3865: end if;
3866: --ASO_QUOTE_LINES_ALL;INVOICE_TO_CUST_PARTY_ID
3867: appid:=697;
3868: if(regid_proc = true) then
3869: 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
3870:
3871: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3872: );
3873: else
3870:
3871: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3872: );
3873: else
3874: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3875: temp.party_id in (select /*+ parallel(xx)*/ xx.INVOICE_TO_CUST_PARTY_ID from ASO_QUOTE_LINES_ALL xx
3876: );
3877: end if;
3878: --ASO_SHIPMENTS;SHIP_TO_CUST_PARTY_ID
3877: end if;
3878: --ASO_SHIPMENTS;SHIP_TO_CUST_PARTY_ID
3879: appid:=697;
3880: if(regid_proc = true) then
3881: 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
3882:
3883: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
3884: );
3885: else
3882:
3883: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
3884: );
3885: else
3886: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3887: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CUST_PARTY_ID from ASO_SHIPMENTS xx
3888: );
3889: end if;
3890: --OKE_POOL_PARTIES;PARTY_ID
3889: end if;
3890: --OKE_POOL_PARTIES;PARTY_ID
3891: appid:=777;
3892: if(regid_proc = true) then
3893: 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
3894:
3895: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
3896: );
3897: else
3894:
3895: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
3896: );
3897: else
3898: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3899: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKE_POOL_PARTIES xx
3900: );
3901: end if;
3902: --AHL_SUBSCRIPTIONS_B;REQUESTED_BY_PARTY_ID
3901: end if;
3902: --AHL_SUBSCRIPTIONS_B;REQUESTED_BY_PARTY_ID
3903: appid:=867;
3904: if(regid_proc = true) then
3905: 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
3906:
3907: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3908: );
3909: else
3906:
3907: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3908: );
3909: else
3910: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3911: temp.party_id in (select /*+ parallel(xx)*/ xx.REQUESTED_BY_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3912: );
3913: end if;
3914: --AHL_SUBSCRIPTIONS_B;SUBSCRIBED_FRM_PARTY_ID
3913: end if;
3914: --AHL_SUBSCRIPTIONS_B;SUBSCRIBED_FRM_PARTY_ID
3915: appid:=867;
3916: if(regid_proc = true) then
3917: 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
3918:
3919: temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3920: );
3921: else
3918:
3919: temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3920: );
3921: else
3922: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3923: temp.party_id in (select /*+ parallel(xx)*/ xx.SUBSCRIBED_FRM_PARTY_ID from AHL_SUBSCRIPTIONS_B xx
3924: );
3925: end if;
3926: --AHL_ROUTES_B;OPERATOR_PARTY_ID
3925: end if;
3926: --AHL_ROUTES_B;OPERATOR_PARTY_ID
3927: appid:=867;
3928: if(regid_proc = true) then
3929: 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
3930:
3931: temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
3932: );
3933: else
3930:
3931: temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
3932: );
3933: else
3934: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3935: temp.party_id in (select /*+ parallel(xx)*/ xx.OPERATOR_PARTY_ID from AHL_ROUTES_B xx
3936: );
3937: end if;
3938: --AS_SALES_LEADS;INCUMBENT_PARTNER_PARTY_ID
3937: end if;
3938: --AS_SALES_LEADS;INCUMBENT_PARTNER_PARTY_ID
3939: appid:=279;
3940: if(regid_proc = true) then
3941: 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
3942:
3943: temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
3944: );
3945: else
3942:
3943: temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
3944: );
3945: else
3946: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3947: temp.party_id in (select /*+ parallel(xx)*/ xx.INCUMBENT_PARTNER_PARTY_ID from AS_SALES_LEADS xx
3948: );
3949: end if;
3950: --AS_SALES_CREDITS_DENORM;CLOSE_COMPETITOR_ID
3949: end if;
3950: --AS_SALES_CREDITS_DENORM;CLOSE_COMPETITOR_ID
3951: appid:=279;
3952: if(regid_proc = true) then
3953: 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
3954:
3955: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
3956: );
3957: else
3954:
3955: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
3956: );
3957: else
3958: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3959: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_SALES_CREDITS_DENORM xx
3960: );
3961: end if;
3962: --AS_LEADS_LOG;CUSTOMER_ID
3961: end if;
3962: --AS_LEADS_LOG;CUSTOMER_ID
3963: appid:=279;
3964: if(regid_proc = true) then
3965: 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
3966:
3967: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
3968: );
3969: else
3966:
3967: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
3968: );
3969: else
3970: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3971: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_LEADS_LOG xx
3972: );
3973: end if;
3974: --AS_LEADS_LOG;ADDRESS_ID
3973: end if;
3974: --AS_LEADS_LOG;ADDRESS_ID
3975: appid:=279;
3976: if(regid_proc = true) then
3977: 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
3978:
3979: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3980: xx
3981: where (nvl(STATUS, 'A') in ('A','I'))
3983: in (select /*+ parallel(yy)*/ yy.ADDRESS_ID from AS_LEADS_LOG
3984: yy
3985: ));
3986: else
3987: delete /*+ parallel(temp) */ from hz_purge_gt temp where
3988: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
3989: xx
3990: where (nvl(STATUS, 'A') in ('A','I'))
3991: and xx.PARTY_SITE_ID
3995: end if;
3996: --AS_LEADS_LOG;CLOSE_COMPETITOR_ID
3997: appid:=279;
3998: if(regid_proc = true) then
3999: 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
4000:
4001: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
4002: );
4003: else
4000:
4001: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
4002: );
4003: else
4004: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4005: temp.party_id in (select /*+ parallel(xx)*/ xx.CLOSE_COMPETITOR_ID from AS_LEADS_LOG xx
4006: );
4007: end if;
4008: --AS_CURRENT_ENVIRONMENT;CUSTOMER_ID
4007: end if;
4008: --AS_CURRENT_ENVIRONMENT;CUSTOMER_ID
4009: appid:=279;
4010: if(regid_proc = true) then
4011: 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
4012:
4013: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
4014: );
4015: else
4012:
4013: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
4014: );
4015: else
4016: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4017: temp.party_id in (select /*+ parallel(xx)*/ xx.CUSTOMER_ID from AS_CURRENT_ENVIRONMENT xx
4018: );
4019: end if;
4020: --AS_SALES_LEADS;REFERRED_BY
4019: end if;
4020: --AS_SALES_LEADS;REFERRED_BY
4021: appid:=279;
4022: if(regid_proc = true) then
4023: 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
4024:
4025: temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
4026: );
4027: else
4024:
4025: temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
4026: );
4027: else
4028: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4029: temp.party_id in (select /*+ parallel(xx)*/ xx.REFERRED_BY from AS_SALES_LEADS xx
4030: );
4031: end if;
4032: --JTF_PERZ_DATA_V;ATTRIBUTE_VALUE
4031: end if;
4032: --JTF_PERZ_DATA_V;ATTRIBUTE_VALUE
4033: appid:=514;
4034: if(regid_proc = true) then
4035: 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
4036:
4037: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
4038: 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%'));
4039: else
4036:
4037: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
4038: 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%'));
4039: else
4040: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4041: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.ATTRIBUTE_VALUE from JTF_PERZ_DATA_V xx
4042: 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%'));
4043: end if;
4044: --JTF_PERZ_QUERY_PARAM_V;PARAMETER_VALUE
4043: end if;
4044: --JTF_PERZ_QUERY_PARAM_V;PARAMETER_VALUE
4045: appid:=514;
4046: if(regid_proc = true) then
4047: 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
4048:
4049: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
4050: 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%' ));
4051: else
4048:
4049: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
4050: 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%' ));
4051: else
4052: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4053: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARAMETER_VALUE from JTF_PERZ_QUERY_PARAM_V xx
4054: 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%' ));
4055: end if;
4056: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_ID
4055: end if;
4056: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_ID
4057: appid:=396;
4058: if(regid_proc = true) then
4059: 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
4060:
4061: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
4062: );
4063: else
4060:
4061: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
4062: );
4063: else
4064: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4065: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_SUPPLIER_ACTIVITIES xx
4066: );
4067: end if;
4068: --PON_SUPPLIER_ACCESS;SUPPLIER_TRADING_PARTNER_ID
4067: end if;
4068: --PON_SUPPLIER_ACCESS;SUPPLIER_TRADING_PARTNER_ID
4069: appid:=396;
4070: if(regid_proc = true) then
4071: 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
4072:
4073: temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
4074: );
4075: else
4072:
4073: temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
4074: );
4075: else
4076: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4077: temp.party_id in (select /*+ parallel(xx)*/ xx.SUPPLIER_TRADING_PARTNER_ID from PON_SUPPLIER_ACCESS xx
4078: );
4079: end if;
4080: --PON_PARTY_LINE_EXCLUSIONS;TRADING_PARTNER_ID
4079: end if;
4080: --PON_PARTY_LINE_EXCLUSIONS;TRADING_PARTNER_ID
4081: appid:=396;
4082: if(regid_proc = true) then
4083: 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
4084:
4085: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
4086: );
4087: else
4084:
4085: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
4086: );
4087: else
4088: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4089: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PARTY_LINE_EXCLUSIONS xx
4090: );
4091: end if;
4092: --PON_PF_SUPPLIER_FORMULA;TRADING_PARTNER_ID
4091: end if;
4092: --PON_PF_SUPPLIER_FORMULA;TRADING_PARTNER_ID
4093: appid:=396;
4094: if(regid_proc = true) then
4095: 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
4096:
4097: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
4098: );
4099: else
4096:
4097: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
4098: );
4099: else
4100: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4101: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_PF_SUPPLIER_FORMULA xx
4102: );
4103: end if;
4104: --PON_BID_HEADERS;SURROG_BID_CREATED_TP_ID
4103: end if;
4104: --PON_BID_HEADERS;SURROG_BID_CREATED_TP_ID
4105: appid:=396;
4106: if(regid_proc = true) then
4107: 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
4108:
4109: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
4110: );
4111: else
4108:
4109: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
4110: );
4111: else
4112: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4113: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_TP_ID from PON_BID_HEADERS xx
4114: );
4115: end if;
4116: --PON_CONTRACTS;AUTHORING_PARTY_CONTACT_ID
4115: end if;
4116: --PON_CONTRACTS;AUTHORING_PARTY_CONTACT_ID
4117: appid:=396;
4118: if(regid_proc = true) then
4119: 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
4120:
4121: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
4122: );
4123: else
4120:
4121: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
4122: );
4123: else
4124: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4125: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_CONTACT_ID from PON_CONTRACTS xx
4126: );
4127: end if;
4128: --PON_CONTRACTS;AUTHORING_PARTY_ID
4127: end if;
4128: --PON_CONTRACTS;AUTHORING_PARTY_ID
4129: appid:=396;
4130: if(regid_proc = true) then
4131: 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
4132:
4133: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
4134: );
4135: else
4132:
4133: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
4134: );
4135: else
4136: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4137: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORING_PARTY_ID from PON_CONTRACTS xx
4138: );
4139: end if;
4140: --PON_THREADS;OWNER_PARTY_ID
4139: end if;
4140: --PON_THREADS;OWNER_PARTY_ID
4141: appid:=396;
4142: if(regid_proc = true) then
4143: 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
4144:
4145: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
4146: );
4147: else
4144:
4145: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
4146: );
4147: else
4148: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4149: temp.party_id in (select /*+ parallel(xx)*/ xx.OWNER_PARTY_ID from PON_THREADS xx
4150: );
4151: end if;
4152: --PON_THREAD_ENTRIES;FROM_ID
4151: end if;
4152: --PON_THREAD_ENTRIES;FROM_ID
4153: appid:=396;
4154: if(regid_proc = true) then
4155: 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
4156:
4157: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
4158: );
4159: else
4156:
4157: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
4158: );
4159: else
4160: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4161: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_ID from PON_THREAD_ENTRIES xx
4162: );
4163: end if;
4164: --PON_THREAD_ENTRIES;FROM_COMPANY_ID
4163: end if;
4164: --PON_THREAD_ENTRIES;FROM_COMPANY_ID
4165: appid:=396;
4166: if(regid_proc = true) then
4167: 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
4168:
4169: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
4170: );
4171: else
4168:
4169: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
4170: );
4171: else
4172: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4173: temp.party_id in (select /*+ parallel(xx)*/ xx.FROM_COMPANY_ID from PON_THREAD_ENTRIES xx
4174: );
4175: end if;
4176: --PON_TE_RECIPIENTS;TO_COMPANY_ID
4175: end if;
4176: --PON_TE_RECIPIENTS;TO_COMPANY_ID
4177: appid:=396;
4178: if(regid_proc = true) then
4179: 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
4180:
4181: temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
4182: );
4183: else
4180:
4181: temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
4182: );
4183: else
4184: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4185: temp.party_id in (select /*+ parallel(xx)*/ xx.TO_COMPANY_ID from PON_TE_RECIPIENTS xx
4186: );
4187: end if;
4188: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_CONTACT_ID
4187: end if;
4188: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_CONTACT_ID
4189: appid:=396;
4190: if(regid_proc = true) then
4191: 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
4192:
4193: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4194: );
4195: else
4192:
4193: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4194: );
4195: else
4196: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4197: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4198: );
4199: end if;
4200: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_TP_ID
4199: end if;
4200: --PON_ACKNOWLEDGEMENTS;SURROG_BID_ACK_TP_ID
4201: appid:=396;
4202: if(regid_proc = true) then
4203: 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
4204:
4205: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
4206: );
4207: else
4204:
4205: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
4206: );
4207: else
4208: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4209: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_ACK_TP_ID from PON_ACKNOWLEDGEMENTS xx
4210: );
4211: end if;
4212: --PON_SUPPLIER_ACCESS;BUYER_TP_CONTACT_ID
4211: end if;
4212: --PON_SUPPLIER_ACCESS;BUYER_TP_CONTACT_ID
4213: appid:=396;
4214: if(regid_proc = true) then
4215: 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
4216:
4217: temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
4218: );
4219: else
4216:
4217: temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
4218: );
4219: else
4220: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4221: temp.party_id in (select /*+ parallel(xx)*/ xx.BUYER_TP_CONTACT_ID from PON_SUPPLIER_ACCESS xx
4222: );
4223: end if;
4224: --PON_BIDDING_PARTIES;TRADING_PARTNER_CONTACT_ID
4223: end if;
4224: --PON_BIDDING_PARTIES;TRADING_PARTNER_CONTACT_ID
4225: appid:=396;
4226: if(regid_proc = true) then
4227: 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
4228:
4229: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4230: );
4231: else
4228:
4229: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4230: );
4231: else
4232: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4233: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4234: );
4235: end if;
4236: --PON_BIDDING_PARTIES;ACK_PARTNER_CONTACT_ID
4235: end if;
4236: --PON_BIDDING_PARTIES;ACK_PARTNER_CONTACT_ID
4237: appid:=396;
4238: if(regid_proc = true) then
4239: 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
4240:
4241: temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4242: );
4243: else
4240:
4241: temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4242: );
4243: else
4244: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4245: temp.party_id in (select /*+ parallel(xx)*/ xx.ACK_PARTNER_CONTACT_ID from PON_BIDDING_PARTIES xx
4246: );
4247: end if;
4248: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_CONTACT_ID
4247: end if;
4248: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_CONTACT_ID
4249: appid:=396;
4250: if(regid_proc = true) then
4251: 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
4252:
4253: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
4254: );
4255: else
4252:
4253: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
4254: );
4255: else
4256: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4257: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_OPTIMIZE_CONSTRAINTS xx
4258: );
4259: end if;
4260: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_ID
4259: end if;
4260: --PON_OPTIMIZE_CONSTRAINTS;TRADING_PARTNER_ID
4261: appid:=396;
4262: if(regid_proc = true) then
4263: 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
4264:
4265: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
4266: );
4267: else
4264:
4265: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
4266: );
4267: else
4268: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4269: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_OPTIMIZE_CONSTRAINTS xx
4270: );
4271: end if;
4272: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_CONTACT_ID
4271: end if;
4272: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_CONTACT_ID
4273: appid:=396;
4274: if(regid_proc = true) then
4275: 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
4276:
4277: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4278: );
4279: else
4276:
4277: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4278: );
4279: else
4280: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4281: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_ACKNOWLEDGEMENTS xx
4282: );
4283: end if;
4284: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_ID
4283: end if;
4284: --PON_ACKNOWLEDGEMENTS;TRADING_PARTNER_ID
4285: appid:=396;
4286: if(regid_proc = true) then
4287: 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
4288:
4289: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
4290: );
4291: else
4288:
4289: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
4290: );
4291: else
4292: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4293: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_ACKNOWLEDGEMENTS xx
4294: );
4295: end if;
4296: --PON_AUCTION_SUMMARY;TRADING_PARTNER_CONTACT_ID
4295: end if;
4296: --PON_AUCTION_SUMMARY;TRADING_PARTNER_CONTACT_ID
4297: appid:=396;
4298: if(regid_proc = true) then
4299: 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
4300:
4301: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
4302: );
4303: else
4300:
4301: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
4302: );
4303: else
4304: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4305: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_SUMMARY xx
4306: );
4307: end if;
4308: --PON_AUCTION_SUMMARY;TRADING_PARTNER_ID
4307: end if;
4308: --PON_AUCTION_SUMMARY;TRADING_PARTNER_ID
4309: appid:=396;
4310: if(regid_proc = true) then
4311: 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
4312:
4313: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
4314: );
4315: else
4312:
4313: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
4314: );
4315: else
4316: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4317: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_SUMMARY xx
4318: );
4319: end if;
4320: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_CONTACT_ID
4319: end if;
4320: --PON_SUPPLIER_ACTIVITIES;TRADING_PARTNER_CONTACT_ID
4321: appid:=396;
4322: if(regid_proc = true) then
4323: 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
4324:
4325: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
4326: );
4327: else
4324:
4325: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
4326: );
4327: else
4328: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4329: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_SUPPLIER_ACTIVITIES xx
4330: );
4331: end if;
4332: --PON_AUCTION_HEADERS_ALL;DRAFT_LOCKED_BY_CONTACT_ID
4331: end if;
4332: --PON_AUCTION_HEADERS_ALL;DRAFT_LOCKED_BY_CONTACT_ID
4333: appid:=396;
4334: if(regid_proc = true) then
4335: 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
4336:
4337: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4338: );
4339: else
4336:
4337: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4338: );
4339: else
4340: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4341: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4342: );
4343: end if;
4344: --PON_AUCTION_HEADERS_ALL;DRAFT_UNLOCKED_BY_CONTACT_ID
4343: end if;
4344: --PON_AUCTION_HEADERS_ALL;DRAFT_UNLOCKED_BY_CONTACT_ID
4345: appid:=396;
4346: if(regid_proc = true) then
4347: 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
4348:
4349: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4350: );
4351: else
4348:
4349: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4350: );
4351: else
4352: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4353: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4354: );
4355: end if;
4356: --PON_AUCTION_HEADERS_ALL;SCORING_LOCK_TP_CONTACT_ID
4355: end if;
4356: --PON_AUCTION_HEADERS_ALL;SCORING_LOCK_TP_CONTACT_ID
4357: appid:=396;
4358: if(regid_proc = true) then
4359: 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
4360:
4361: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4362: );
4363: else
4360:
4361: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4362: );
4363: else
4364: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4365: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORING_LOCK_TP_CONTACT_ID from PON_AUCTION_HEADERS_ALL xx
4366: );
4367: end if;
4368: --PON_AUCTION_HEADERS_ALL;TRADING_PARTNER_ID
4367: end if;
4368: --PON_AUCTION_HEADERS_ALL;TRADING_PARTNER_ID
4369: appid:=396;
4370: if(regid_proc = true) then
4371: 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
4372:
4373: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_HEADERS_ALL xx
4374: );
4375: else
4372:
4373: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_HEADERS_ALL xx
4374: );
4375: else
4376: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4377: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_ID from PON_AUCTION_HEADERS_ALL xx
4378: );
4379: end if;
4380: --PON_AUCTION_EVENTS;TRADING_PARTNER_CONTACT_ID
4379: end if;
4380: --PON_AUCTION_EVENTS;TRADING_PARTNER_CONTACT_ID
4381: appid:=396;
4382: if(regid_proc = true) then
4383: 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
4384:
4385: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
4386: );
4387: else
4384:
4385: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
4386: );
4387: else
4388: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4389: temp.party_id in (select /*+ parallel(xx)*/ xx.TRADING_PARTNER_CONTACT_ID from PON_AUCTION_EVENTS xx
4390: );
4391: end if;
4392: --PON_BID_HEADERS;SURROG_BID_CREATED_CONTACT_ID
4391: end if;
4392: --PON_BID_HEADERS;SURROG_BID_CREATED_CONTACT_ID
4393: appid:=396;
4394: if(regid_proc = true) then
4395: 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
4396:
4397: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
4398: );
4399: else
4396:
4397: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
4398: );
4399: else
4400: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4401: temp.party_id in (select /*+ parallel(xx)*/ xx.SURROG_BID_CREATED_CONTACT_ID from PON_BID_HEADERS xx
4402: );
4403: end if;
4404: --PON_BID_HEADERS;SCORE_OVERRIDE_TP_CONTACT_ID
4403: end if;
4404: --PON_BID_HEADERS;SCORE_OVERRIDE_TP_CONTACT_ID
4405: appid:=396;
4406: if(regid_proc = true) then
4407: 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
4408:
4409: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
4410: );
4411: else
4408:
4409: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
4410: );
4411: else
4412: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4413: temp.party_id in (select /*+ parallel(xx)*/ xx.SCORE_OVERRIDE_TP_CONTACT_ID from PON_BID_HEADERS xx
4414: );
4415: end if;
4416: --PON_BID_HEADERS;SHORTLIST_TPC_ID
4415: end if;
4416: --PON_BID_HEADERS;SHORTLIST_TPC_ID
4417: appid:=396;
4418: if(regid_proc = true) then
4419: 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
4420:
4421: temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
4422: );
4423: else
4420:
4421: temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
4422: );
4423: else
4424: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4425: temp.party_id in (select /*+ parallel(xx)*/ xx.SHORTLIST_TPC_ID from PON_BID_HEADERS xx
4426: );
4427: end if;
4428: --PON_BID_HEADERS;DRAFT_UNLOCKED_BY_CONTACT_ID
4427: end if;
4428: --PON_BID_HEADERS;DRAFT_UNLOCKED_BY_CONTACT_ID
4429: appid:=396;
4430: if(regid_proc = true) then
4431: 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
4432:
4433: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4434: );
4435: else
4432:
4433: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4434: );
4435: else
4436: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4437: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_UNLOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4438: );
4439: end if;
4440: --PON_BID_HEADERS;DRAFT_LOCKED_BY_CONTACT_ID
4439: end if;
4440: --PON_BID_HEADERS;DRAFT_LOCKED_BY_CONTACT_ID
4441: appid:=396;
4442: if(regid_proc = true) then
4443: 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
4444:
4445: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4446: );
4447: else
4444:
4445: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4446: );
4447: else
4448: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4449: temp.party_id in (select /*+ parallel(xx)*/ xx.DRAFT_LOCKED_BY_CONTACT_ID from PON_BID_HEADERS xx
4450: );
4451: end if;
4452: --OKL_INS_POLICIES_ALL_B;INT_ID
4451: end if;
4452: --OKL_INS_POLICIES_ALL_B;INT_ID
4453: appid:=540;
4454: if(regid_proc = true) then
4455: 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
4456:
4457: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_ALL_B xx
4458: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4459: else
4456:
4457: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_ALL_B xx
4458: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4459: else
4460: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4461: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.INT_ID from OKL_INS_POLICIES_ALL_B xx
4462: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4463: end if;
4464: --OKL_INS_POLICIES_ALL_B;AGENT_SITE_ID
4463: end if;
4464: --OKL_INS_POLICIES_ALL_B;AGENT_SITE_ID
4465: appid:=540;
4466: if(regid_proc = true) then
4467: 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
4468:
4469: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4470: xx
4471: where (nvl(STATUS, 'A') in ('A','I'))
4473: in (select /*+ parallel(yy)*/ yy.AGENT_SITE_ID from OKL_INS_POLICIES_ALL_B
4474: yy
4475: where IPY_TYPE = 'THIRD_PARTY_POLICY'));
4476: else
4477: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4478: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4479: xx
4480: where (nvl(STATUS, 'A') in ('A','I'))
4481: and to_char(xx.PARTY_SITE_ID)
4485: end if;
4486: --OKL_INS_POLICIES_B;AGENCY_SITE_ID
4487: appid:=540;
4488: if(regid_proc = true) then
4489: 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
4490:
4491: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4492: xx
4493: where (nvl(STATUS, 'A') in ('A','I'))
4495: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_B
4496: yy
4497: where IPY_TYPE = 'THIRD_PARTY_POLICY'));
4498: else
4499: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4500: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4501: xx
4502: where (nvl(STATUS, 'A') in ('A','I'))
4503: and xx.PARTY_SITE_ID
4507: end if;
4508: --OKL_INS_POLICIES_ALL_B;AGENCY_SITE_ID
4509: appid:=540;
4510: if(regid_proc = true) then
4511: 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
4512:
4513: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4514: xx
4515: where (nvl(STATUS, 'A') in ('A','I'))
4517: in (select /*+ parallel(yy)*/ yy.AGENCY_SITE_ID from OKL_INS_POLICIES_ALL_B
4518: yy
4519: where IPY_TYPE = 'THIRD_PARTY_POLICY'));
4520: else
4521: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4522: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4523: xx
4524: where (nvl(STATUS, 'A') in ('A','I'))
4525: and xx.PARTY_SITE_ID
4529: end if;
4530: --OKL_INS_POLICIES_ALL_B;ISU_ID
4531: appid:=540;
4532: if(regid_proc = true) then
4533: 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
4534:
4535: temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_ALL_B xx
4536: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4537: else
4534:
4535: temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_ALL_B xx
4536: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4537: else
4538: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4539: temp.party_id in (select /*+ parallel(xx)*/ xx.ISU_ID from OKL_INS_POLICIES_ALL_B xx
4540: where (IPY_TYPE = 'THIRD_PARTY_POLICY'));
4541: end if;
4542: --OKC_K_PARTY_ROLES_B;OBJECT1_ID1
4541: end if;
4542: --OKC_K_PARTY_ROLES_B;OBJECT1_ID1
4543: appid:=540;
4544: if(regid_proc = true) then
4545: 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
4546:
4547: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4548: xx
4549: where (nvl(STATUS, 'A') in ('A','I'))
4551: in (select /*+ parallel(yy)*/ yy.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B
4552: yy
4553: where JTOT_OBJECT1_CODE = 'OKL_PARTYSITE' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
4554: else
4555: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4556: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4557: xx
4558: where (nvl(STATUS, 'A') in ('A','I'))
4559: and to_char(xx.PARTY_SITE_ID)
4563: end if;
4564: --OKC_K_PARTY_ROLES_B;OBJECT1_ID1
4565: appid:=540;
4566: if(regid_proc = true) then
4567: 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
4568:
4569: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B xx
4570: where (JTOT_OBJECT1_CODE = 'OKX_PARTY' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
4571: else
4568:
4569: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B xx
4570: where (JTOT_OBJECT1_CODE = 'OKX_PARTY' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
4571: else
4572: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4573: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.OBJECT1_ID1 from OKC_K_PARTY_ROLES_B xx
4574: where (JTOT_OBJECT1_CODE = 'OKX_PARTY' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
4575: end if;
4576: --OKL_TRX_CONTRACTS_ALL;PARTY_REL_ID1_OLD
4575: end if;
4576: --OKL_TRX_CONTRACTS_ALL;PARTY_REL_ID1_OLD
4577: appid:=540;
4578: if(regid_proc = true) then
4579: 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
4580:
4581: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_OLD from OKL_TRX_CONTRACTS_ALL xx
4582: );
4583: else
4580:
4581: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_OLD from OKL_TRX_CONTRACTS_ALL xx
4582: );
4583: else
4584: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4585: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_OLD from OKL_TRX_CONTRACTS_ALL xx
4586: );
4587: end if;
4588: --OKL_TRX_CONTRACTS_ALL;PARTY_REL_ID2_OLD
4587: end if;
4588: --OKL_TRX_CONTRACTS_ALL;PARTY_REL_ID2_OLD
4589: appid:=540;
4590: if(regid_proc = true) then
4591: 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
4592:
4593: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_OLD from OKL_TRX_CONTRACTS_ALL xx
4594: );
4595: else
4592:
4593: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_OLD from OKL_TRX_CONTRACTS_ALL xx
4594: );
4595: else
4596: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4597: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_OLD from OKL_TRX_CONTRACTS_ALL xx
4598: );
4599: end if;
4600: --OKL_LEASE_APPS_ALL_B;PROSPECT_ADDRESS_ID
4599: end if;
4600: --OKL_LEASE_APPS_ALL_B;PROSPECT_ADDRESS_ID
4601: appid:=540;
4602: if(regid_proc = true) then
4603: 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
4604:
4605: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4606: xx
4607: where (nvl(STATUS, 'A') in ('A','I'))
4609: in (select /*+ parallel(yy)*/ yy.PROSPECT_ADDRESS_ID from OKL_LEASE_APPS_ALL_B
4610: yy
4611: ));
4612: else
4613: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4614: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4615: xx
4616: where (nvl(STATUS, 'A') in ('A','I'))
4617: and xx.PARTY_SITE_ID
4621: end if;
4622: --OKL_LEASE_OPPS_ALL_B;PROSPECT_ADDRESS_ID
4623: appid:=540;
4624: if(regid_proc = true) then
4625: 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
4626:
4627: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4628: xx
4629: where (nvl(STATUS, 'A') in ('A','I'))
4631: in (select /*+ parallel(yy)*/ yy.PROSPECT_ADDRESS_ID from OKL_LEASE_OPPS_ALL_B
4632: yy
4633: ));
4634: else
4635: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4636: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4637: xx
4638: where (nvl(STATUS, 'A') in ('A','I'))
4639: and xx.PARTY_SITE_ID
4643: end if;
4644: --OKL_TAX_SOURCES;BILL_TO_PARTY_ID
4645: appid:=540;
4646: if(regid_proc = true) then
4647: 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
4648:
4649: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES xx
4650: );
4651: else
4648:
4649: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES xx
4650: );
4651: else
4652: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4653: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES xx
4654: );
4655: end if;
4656: --OKL_TAX_SOURCES;BILL_TO_PARTY_SITE_ID
4655: end if;
4656: --OKL_TAX_SOURCES;BILL_TO_PARTY_SITE_ID
4657: appid:=540;
4658: if(regid_proc = true) then
4659: 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
4660:
4661: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4662: xx
4663: where (nvl(STATUS, 'A') in ('A','I'))
4665: in (select /*+ parallel(yy)*/ yy.BILL_TO_PARTY_SITE_ID from OKL_TAX_SOURCES
4666: yy
4667: ));
4668: else
4669: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4670: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4671: xx
4672: where (nvl(STATUS, 'A') in ('A','I'))
4673: and xx.PARTY_SITE_ID
4677: end if;
4678: --OKL_TAX_SOURCES;SHIP_TO_PARTY_ID
4679: appid:=540;
4680: if(regid_proc = true) then
4681: 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
4682:
4683: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES xx
4684: );
4685: else
4682:
4683: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES xx
4684: );
4685: else
4686: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4687: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES xx
4688: );
4689: end if;
4690: --OKL_TAX_SOURCES;SHIP_TO_PARTY_SITE_ID
4689: end if;
4690: --OKL_TAX_SOURCES;SHIP_TO_PARTY_SITE_ID
4691: appid:=540;
4692: if(regid_proc = true) then
4693: 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
4694:
4695: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4696: xx
4697: where (nvl(STATUS, 'A') in ('A','I'))
4699: in (select /*+ parallel(yy)*/ yy.SHIP_TO_PARTY_SITE_ID from OKL_TAX_SOURCES
4700: yy
4701: ));
4702: else
4703: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4704: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4705: xx
4706: where (nvl(STATUS, 'A') in ('A','I'))
4707: and xx.PARTY_SITE_ID
4711: end if;
4712: --OKL_TAX_SOURCES_T;BILL_TO_PARTY_ID
4713: appid:=540;
4714: if(regid_proc = true) then
4715: 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
4716:
4717: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
4718: );
4719: else
4716:
4717: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
4718: );
4719: else
4720: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4721: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
4722: );
4723: end if;
4724: --OKL_TAX_SOURCES_T;BILL_TO_PARTY_SITE_ID
4723: end if;
4724: --OKL_TAX_SOURCES_T;BILL_TO_PARTY_SITE_ID
4725: appid:=540;
4726: if(regid_proc = true) then
4727: 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
4728:
4729: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4730: xx
4731: where (nvl(STATUS, 'A') in ('A','I'))
4733: in (select /*+ parallel(yy)*/ yy.BILL_TO_PARTY_SITE_ID from OKL_TAX_SOURCES_T
4734: yy
4735: ));
4736: else
4737: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4738: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4739: xx
4740: where (nvl(STATUS, 'A') in ('A','I'))
4741: and xx.PARTY_SITE_ID
4745: end if;
4746: --OKL_TAX_SOURCES_T;SHIP_TO_PARTY_ID
4747: appid:=540;
4748: if(regid_proc = true) then
4749: 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
4750:
4751: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
4752: );
4753: else
4750:
4751: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
4752: );
4753: else
4754: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4755: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from OKL_TAX_SOURCES_T xx
4756: );
4757: end if;
4758: --OKL_TAX_SOURCES_T;SHIP_TO_PARTY_SITE_ID
4757: end if;
4758: --OKL_TAX_SOURCES_T;SHIP_TO_PARTY_SITE_ID
4759: appid:=540;
4760: if(regid_proc = true) then
4761: 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
4762:
4763: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4764: xx
4765: where (nvl(STATUS, 'A') in ('A','I'))
4767: in (select /*+ parallel(yy)*/ yy.SHIP_TO_PARTY_SITE_ID from OKL_TAX_SOURCES_T
4768: yy
4769: ));
4770: else
4771: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4772: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4773: xx
4774: where (nvl(STATUS, 'A') in ('A','I'))
4775: and xx.PARTY_SITE_ID
4779: end if;
4780: --OKL_TERMNT_INTF_PTY;PARTY_OBJECT_ID1
4781: appid:=540;
4782: if(regid_proc = true) then
4783: 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
4784:
4785: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_OBJECT_ID1 from OKL_TERMNT_INTF_PTY xx
4786: where (PARTY_OBJECT_CODE = 'OKX_PARTY'));
4787: else
4784:
4785: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_OBJECT_ID1 from OKL_TERMNT_INTF_PTY xx
4786: where (PARTY_OBJECT_CODE = 'OKX_PARTY'));
4787: else
4788: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4789: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_OBJECT_ID1 from OKL_TERMNT_INTF_PTY xx
4790: where (PARTY_OBJECT_CODE = 'OKX_PARTY'));
4791: end if;
4792: --OKL_TRX_CONTRACTS_ALL;PARTY_REL_ID2_NEW
4791: end if;
4792: --OKL_TRX_CONTRACTS_ALL;PARTY_REL_ID2_NEW
4793: appid:=540;
4794: if(regid_proc = true) then
4795: 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
4796:
4797: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_NEW from OKL_TRX_CONTRACTS_ALL xx
4798: );
4799: else
4796:
4797: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_NEW from OKL_TRX_CONTRACTS_ALL xx
4798: );
4799: else
4800: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4801: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID2_NEW from OKL_TRX_CONTRACTS_ALL xx
4802: );
4803: end if;
4804: --OKC_RULES_B;OBJECT1_ID1
4803: end if;
4804: --OKC_RULES_B;OBJECT1_ID1
4805: appid:=540;
4806: if(regid_proc = true) then
4807: 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
4808:
4809: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4810: xx
4811: where (nvl(STATUS, 'A') in ('A','I'))
4813: in (select /*+ parallel(yy)*/ yy.OBJECT1_ID1 from OKC_RULES_B
4814: yy
4815: where JTOT_OBJECT1_CODE = 'OKL_PARTYSITE' AND DNZ_CHR_ID IN (SELECT ID FROM OKL_K_HEADERS)));
4816: else
4817: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4818: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4819: xx
4820: where (nvl(STATUS, 'A') in ('A','I'))
4821: and to_char(xx.PARTY_SITE_ID)
4825: end if;
4826: --OKL_TRX_CONTRACTS_ALL;PARTY_REL_ID1_NEW
4827: appid:=540;
4828: if(regid_proc = true) then
4829: 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
4830:
4831: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_NEW from OKL_TRX_CONTRACTS_ALL xx
4832: );
4833: else
4830:
4831: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_NEW from OKL_TRX_CONTRACTS_ALL xx
4832: );
4833: else
4834: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4835: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_REL_ID1_NEW from OKL_TRX_CONTRACTS_ALL xx
4836: );
4837: end if;
4838: --OKL_EXT_SELL_INVS_ALL_B;CUSTOMER_ID
4837: end if;
4838: --OKL_EXT_SELL_INVS_ALL_B;CUSTOMER_ID
4839: appid:=540;
4840: if(regid_proc = true) then
4841: 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
4842:
4843: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_CUST_ACCOUNTS
4844: xx
4845: where (nvl(STATUS, 'A') in ('A','I'))
4854: in (select /*+ parallel(yy)*/ yy.CUSTOMER_ID from OKL_EXT_SELL_INVS_ALL_B
4855: yy
4856: ));
4857: else
4858: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4859: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_CUST_ACCOUNTS
4860: xx
4861: where (nvl(STATUS, 'A') in ('A','I'))
4862: and xx.CUST_ACCOUNT_ID
4873: end if;
4874: --OKL_OPEN_INT_ALL;PARTY_ID
4875: appid:=540;
4876: if(regid_proc = true) then
4877: 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
4878:
4879: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT_ALL xx
4880: );
4881: else
4878:
4879: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT_ALL xx
4880: );
4881: else
4882: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4883: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from OKL_OPEN_INT_ALL xx
4884: );
4885: end if;
4886: --PO_VENDORS;PARTY_ID
4885: end if;
4886: --PO_VENDORS;PARTY_ID
4887: appid:=200;
4888: if(regid_proc = true) then
4889: insert into hz_application_trans_gt(app_id,party_id) select 200, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 200 and appl.party_id=temp.party_id) and
4890:
4891: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PO_VENDORS xx
4892: );
4893: else
4890:
4891: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PO_VENDORS xx
4892: );
4893: else
4894: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4895: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from PO_VENDORS xx
4896: );
4897: end if;
4898: --PO_VENDOR_SITES_ALL;PARTY_SITE_ID
4897: end if;
4898: --PO_VENDOR_SITES_ALL;PARTY_SITE_ID
4899: appid:=200;
4900: if(regid_proc = true) then
4901: insert into hz_application_trans_gt(app_id,party_id) select 200, temp.party_id from hz_purge_gt temp where not exists(select 'Y' from hz_application_trans_gt appl where appl.app_id = 200 and appl.party_id=temp.party_id) and
4902:
4903: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4904: xx
4905: where (nvl(STATUS, 'A') in ('A','I'))
4907: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from PO_VENDOR_SITES_ALL
4908: yy
4909: ));
4910: else
4911: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4912: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
4913: xx
4914: where (nvl(STATUS, 'A') in ('A','I'))
4915: and xx.PARTY_SITE_ID
4919: end if;
4920: --AMS_IBA_PL_SITES_B;SITE_CATEGORY_OBJECT_ID
4921: appid:=530;
4922: if(regid_proc = true) then
4923: 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
4924:
4925: temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
4926: where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
4927: else
4924:
4925: temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
4926: where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
4927: else
4928: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4929: temp.party_id in (select /*+ parallel(xx)*/ xx.SITE_CATEGORY_OBJECT_ID from AMS_IBA_PL_SITES_B xx
4930: where (SITE_CATEGORY_TYPE = 'AFFILIATES'));
4931: end if;
4932: --AMS_PARTY_MARKET_SEGMENTS;PARTY_ID
4931: end if;
4932: --AMS_PARTY_MARKET_SEGMENTS;PARTY_ID
4933: appid:=530;
4934: if(regid_proc = true) then
4935: 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
4936:
4937: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
4938: );
4939: else
4936:
4937: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
4938: );
4939: else
4940: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4941: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_PARTY_MARKET_SEGMENTS xx
4942: );
4943: end if;
4944: --AMS_AGENDAS_B;COORDINATOR_ID
4943: end if;
4944: --AMS_AGENDAS_B;COORDINATOR_ID
4945: appid:=530;
4946: if(regid_proc = true) then
4947: 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
4948:
4949: temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
4950: );
4951: else
4948:
4949: temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
4950: );
4951: else
4952: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4953: temp.party_id in (select /*+ parallel(xx)*/ xx.COORDINATOR_ID from AMS_AGENDAS_B xx
4954: );
4955: end if;
4956: --AMS_TCOP_CHANNEL_SUMMARY;PARTY_ID
4955: end if;
4956: --AMS_TCOP_CHANNEL_SUMMARY;PARTY_ID
4957: appid:=530;
4958: if(regid_proc = true) then
4959: 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
4960:
4961: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
4962: );
4963: else
4960:
4961: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
4962: );
4963: else
4964: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4965: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CHANNEL_SUMMARY xx
4966: );
4967: end if;
4968: --AMS_TCOP_CONTACTS;PARTY_ID
4967: end if;
4968: --AMS_TCOP_CONTACTS;PARTY_ID
4969: appid:=530;
4970: if(regid_proc = true) then
4971: 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
4972:
4973: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
4974: );
4975: else
4972:
4973: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
4974: );
4975: else
4976: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4977: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_CONTACTS xx
4978: );
4979: end if;
4980: --AMS_TCOP_PRVW_CONTACTS;PARTY_ID
4979: end if;
4980: --AMS_TCOP_PRVW_CONTACTS;PARTY_ID
4981: appid:=530;
4982: if(regid_proc = true) then
4983: 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
4984:
4985: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
4986: );
4987: else
4984:
4985: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
4986: );
4987: else
4988: delete /*+ parallel(temp) */ from hz_purge_gt temp where
4989: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_CONTACTS xx
4990: );
4991: end if;
4992: --AMS_TCOP_PRVW_FTG_DTLS;PARTY_ID
4991: end if;
4992: --AMS_TCOP_PRVW_FTG_DTLS;PARTY_ID
4993: appid:=530;
4994: if(regid_proc = true) then
4995: 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
4996:
4997: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
4998: );
4999: else
4996:
4997: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
4998: );
4999: else
5000: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5001: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from AMS_TCOP_PRVW_FTG_DTLS xx
5002: );
5003: end if;
5004: --CSC_CUST_PLANS;PARTY_ID
5003: end if;
5004: --CSC_CUST_PLANS;PARTY_ID
5005: appid:=511;
5006: if(regid_proc = true) then
5007: 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
5008:
5009: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
5010: );
5011: else
5008:
5009: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
5010: );
5011: else
5012: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5013: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from CSC_CUST_PLANS xx
5014: );
5015: end if;
5016: --FTE_LOCATION_PARAMETERS;FACILITY_CONTACT_ID
5015: end if;
5016: --FTE_LOCATION_PARAMETERS;FACILITY_CONTACT_ID
5017: appid:=716;
5018: if(regid_proc = true) then
5019: 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
5020:
5021: temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
5022: );
5023: else
5020:
5021: temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
5022: );
5023: else
5024: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5025: temp.party_id in (select /*+ parallel(xx)*/ xx.FACILITY_CONTACT_ID from FTE_LOCATION_PARAMETERS xx
5026: );
5027: end if;
5028: --JTF_FM_PROCESSED_V;PARTY_ID
5027: end if;
5028: --JTF_FM_PROCESSED_V;PARTY_ID
5029: appid:=690;
5030: if(regid_proc = true) then
5031: 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
5032:
5033: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
5034: );
5035: else
5032:
5033: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
5034: );
5035: else
5036: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5037: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_PROCESSED_V xx
5038: );
5039: end if;
5040: --JTF_FM_CONTENT_HISTORY_V;PARTY_ID
5039: end if;
5040: --JTF_FM_CONTENT_HISTORY_V;PARTY_ID
5041: appid:=690;
5042: if(regid_proc = true) then
5043: 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
5044:
5045: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
5046: );
5047: else
5044:
5045: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
5046: );
5047: else
5048: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5049: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from JTF_FM_CONTENT_HISTORY_V xx
5050: );
5051: end if;
5052: --OKS_QUALIFIERS;QUALIFIER_ATTR_VALUE
5051: end if;
5052: --OKS_QUALIFIERS;QUALIFIER_ATTR_VALUE
5053: appid:=515;
5054: if(regid_proc = true) then
5055: 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
5056:
5057: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
5058: 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'))));
5059: else
5056:
5057: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
5058: 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'))));
5059: else
5060: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5061: to_char(temp.party_id) in (select /*+ parallel(xx)*/ xx.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS xx
5062: 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'))));
5063: end if;
5064: --OKS_QUALIFIERS;QUALIFIER_ATTR_VALUE
5063: end if;
5064: --OKS_QUALIFIERS;QUALIFIER_ATTR_VALUE
5065: appid:=515;
5066: if(regid_proc = true) then
5067: 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
5068:
5069: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5070: xx
5071: where (nvl(STATUS, 'A') in ('A','I'))
5073: in (select /*+ parallel(yy)*/ yy.QUALIFIER_ATTR_VALUE from OKS_QUALIFIERS
5074: yy
5075: where (qualifier_context = 'ASOPARTYINFO' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE10','QUALIFIER_ATTRIBUTE11') OR qualifier_context = 'CUSTOMER' AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE17', 'QUALIFIER_ATTRIBUTE18'))));
5076: else
5077: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5078: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5079: xx
5080: where (nvl(STATUS, 'A') in ('A','I'))
5081: and to_char(xx.PARTY_SITE_ID)
5085: end if;
5086: --CS_ESTIMATE_DETAILS;BILL_TO_PARTY_ID
5087: appid:=170;
5088: if(regid_proc = true) then
5089: 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
5090:
5091: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
5092: );
5093: else
5090:
5091: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
5092: );
5093: else
5094: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5095: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
5096: );
5097: end if;
5098: --CS_ESTIMATE_DETAILS;SHIP_TO_PARTY_ID
5097: end if;
5098: --CS_ESTIMATE_DETAILS;SHIP_TO_PARTY_ID
5099: appid:=170;
5100: if(regid_proc = true) then
5101: 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
5102:
5103: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
5104: );
5105: else
5102:
5103: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
5104: );
5105: else
5106: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5107: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_ESTIMATE_DETAILS xx
5108: );
5109: end if;
5110: --CS_INCIDENTS_ALL_B;SITE_ID
5109: end if;
5110: --CS_INCIDENTS_ALL_B;SITE_ID
5111: appid:=170;
5112: if(regid_proc = true) then
5113: 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
5114:
5115: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5116: xx
5117: where (nvl(STATUS, 'A') in ('A','I'))
5119: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_ALL_B
5120: yy
5121: ));
5122: else
5123: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5124: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5125: xx
5126: where (nvl(STATUS, 'A') in ('A','I'))
5127: and xx.PARTY_SITE_ID
5131: end if;
5132: --CS_INCIDENTS_AUDIT_B;SITE_ID
5133: appid:=170;
5134: if(regid_proc = true) then
5135: 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
5136:
5137: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5138: xx
5139: where (nvl(STATUS, 'A') in ('A','I'))
5141: in (select /*+ parallel(yy)*/ yy.SITE_ID from CS_INCIDENTS_AUDIT_B
5142: yy
5143: ));
5144: else
5145: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5146: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5147: xx
5148: where (nvl(STATUS, 'A') in ('A','I'))
5149: and xx.PARTY_SITE_ID
5153: end if;
5154: --CS_INCIDENTS_ALL_B;BILL_TO_SITE_ID
5155: appid:=170;
5156: if(regid_proc = true) then
5157: 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
5158:
5159: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5160: xx
5161: where (nvl(STATUS, 'A') in ('A','I'))
5163: in (select /*+ parallel(yy)*/ yy.BILL_TO_SITE_ID from CS_INCIDENTS_ALL_B
5164: yy
5165: ));
5166: else
5167: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5168: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5169: xx
5170: where (nvl(STATUS, 'A') in ('A','I'))
5171: and xx.PARTY_SITE_ID
5175: end if;
5176: --CS_INCIDENTS_ALL_B;SHIP_TO_SITE_ID
5177: appid:=170;
5178: if(regid_proc = true) then
5179: 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
5180:
5181: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5182: xx
5183: where (nvl(STATUS, 'A') in ('A','I'))
5185: in (select /*+ parallel(yy)*/ yy.SHIP_TO_SITE_ID from CS_INCIDENTS_ALL_B
5186: yy
5187: ));
5188: else
5189: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5190: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5191: xx
5192: where (nvl(STATUS, 'A') in ('A','I'))
5193: and xx.PARTY_SITE_ID
5197: end if;
5198: --CS_INCIDENTS_ALL_B;INSTALL_SITE_ID
5199: appid:=170;
5200: if(regid_proc = true) then
5201: 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
5202:
5203: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5204: xx
5205: where (nvl(STATUS, 'A') in ('A','I'))
5207: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_ID from CS_INCIDENTS_ALL_B
5208: yy
5209: ));
5210: else
5211: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5212: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5213: xx
5214: where (nvl(STATUS, 'A') in ('A','I'))
5215: and xx.PARTY_SITE_ID
5219: end if;
5220: --CS_INCIDENTS_AUDIT_B;BILL_TO_CONTACT_ID
5221: appid:=170;
5222: if(regid_proc = true) then
5223: 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
5224:
5225: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
5226: );
5227: else
5224:
5225: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
5226: );
5227: else
5228: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5229: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_AUDIT_B xx
5230: );
5231: end if;
5232: --CS_INCIDENTS_ALL_B;BILL_TO_PARTY_ID
5231: end if;
5232: --CS_INCIDENTS_ALL_B;BILL_TO_PARTY_ID
5233: appid:=170;
5234: if(regid_proc = true) then
5235: 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
5236:
5237: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5238: );
5239: else
5236:
5237: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5238: );
5239: else
5240: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5241: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5242: );
5243: end if;
5244: --CS_INCIDENTS_ALL_B;SHIP_TO_PARTY_ID
5243: end if;
5244: --CS_INCIDENTS_ALL_B;SHIP_TO_PARTY_ID
5245: appid:=170;
5246: if(regid_proc = true) then
5247: 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
5248:
5249: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5250: );
5251: else
5248:
5249: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5250: );
5251: else
5252: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5253: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_PARTY_ID from CS_INCIDENTS_ALL_B xx
5254: );
5255: end if;
5256: --CS_CHG_SUB_RESTRICTIONS;VALUE_OBJECT_ID
5255: end if;
5256: --CS_CHG_SUB_RESTRICTIONS;VALUE_OBJECT_ID
5257: appid:=170;
5258: if(regid_proc = true) then
5259: 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
5260:
5261: temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
5262: );
5263: else
5260:
5261: temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
5262: );
5263: else
5264: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5265: temp.party_id in (select /*+ parallel(xx)*/ xx.VALUE_OBJECT_ID from CS_CHG_SUB_RESTRICTIONS xx
5266: );
5267: end if;
5268: --CS_INCIDENTS_ALL_B;BILL_TO_CONTACT_ID
5267: end if;
5268: --CS_INCIDENTS_ALL_B;BILL_TO_CONTACT_ID
5269: appid:=170;
5270: if(regid_proc = true) then
5271: 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
5272:
5273: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5274: );
5275: else
5272:
5273: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5274: );
5275: else
5276: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5277: temp.party_id in (select /*+ parallel(xx)*/ xx.BILL_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5278: );
5279: end if;
5280: --CS_INCIDENTS_ALL_B;SHIP_TO_CONTACT_ID
5279: end if;
5280: --CS_INCIDENTS_ALL_B;SHIP_TO_CONTACT_ID
5281: appid:=170;
5282: if(regid_proc = true) then
5283: 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
5284:
5285: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5286: );
5287: else
5284:
5285: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5286: );
5287: else
5288: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5289: temp.party_id in (select /*+ parallel(xx)*/ xx.SHIP_TO_CONTACT_ID from CS_INCIDENTS_ALL_B xx
5290: );
5291: end if;
5292: --CS_INCIDENTS_ALL_B;CUSTOMER_SITE_ID
5291: end if;
5292: --CS_INCIDENTS_ALL_B;CUSTOMER_SITE_ID
5293: appid:=170;
5294: if(regid_proc = true) then
5295: 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
5296:
5297: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5298: xx
5299: where (nvl(STATUS, 'A') in ('A','I'))
5301: in (select /*+ parallel(yy)*/ yy.CUSTOMER_SITE_ID from CS_INCIDENTS_ALL_B
5302: yy
5303: ));
5304: else
5305: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5306: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5307: xx
5308: where (nvl(STATUS, 'A') in ('A','I'))
5309: and xx.PARTY_SITE_ID
5313: end if;
5314: --CS_INCIDENTS_ALL_B;INSTALL_SITE_USE_ID
5315: appid:=170;
5316: if(regid_proc = true) then
5317: 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
5318:
5319: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5320: xx
5321: where (nvl(STATUS, 'A') in ('A','I'))
5323: in (select /*+ parallel(yy)*/ yy.INSTALL_SITE_USE_ID from CS_INCIDENTS_ALL_B
5324: yy
5325: ));
5326: else
5327: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5328: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5329: xx
5330: where (nvl(STATUS, 'A') in ('A','I'))
5331: and xx.PARTY_SITE_ID
5335: end if;
5336: --CS_INCIDENTS_ALL_B;INCIDENT_LOCATION_ID
5337: appid:=170;
5338: if(regid_proc = true) then
5339: 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
5340:
5341: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5342: xx
5343: where (nvl(STATUS, 'A') in ('A','I'))
5345: in (select /*+ parallel(yy)*/ yy.INCIDENT_LOCATION_ID from CS_INCIDENTS_ALL_B
5346: yy
5347: ));
5348: else
5349: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5350: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5351: xx
5352: where (nvl(STATUS, 'A') in ('A','I'))
5353: and xx.PARTY_SITE_ID
5357: end if;
5358: --ECX_TP_HEADERS;PARTY_SITE_ID
5359: appid:=174;
5360: if(regid_proc = true) then
5361: 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
5362:
5363: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5364: xx
5365: where (nvl(STATUS, 'A') in ('A','I'))
5367: in (select /*+ parallel(yy)*/ yy.PARTY_SITE_ID from ECX_TP_HEADERS
5368: yy
5369: ));
5370: else
5371: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5372: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5373: xx
5374: where (nvl(STATUS, 'A') in ('A','I'))
5375: and xx.PARTY_SITE_ID
5379: end if;
5380: --XLE_REGISTRATIONS;ISSUING_AUTHORITY_ID
5381: appid:=204;
5382: if(regid_proc = true) then
5383: 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
5384:
5385: temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
5386: );
5387: else
5384:
5385: temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
5386: );
5387: else
5388: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5389: temp.party_id in (select /*+ parallel(xx)*/ xx.ISSUING_AUTHORITY_ID from XLE_REGISTRATIONS xx
5390: );
5391: end if;
5392: --XLE_REG_FUNCTIONS;AUTHORITY_ID
5391: end if;
5392: --XLE_REG_FUNCTIONS;AUTHORITY_ID
5393: appid:=204;
5394: if(regid_proc = true) then
5395: 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
5396:
5397: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
5398: );
5399: else
5396:
5397: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
5398: );
5399: else
5400: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5401: temp.party_id in (select /*+ parallel(xx)*/ xx.AUTHORITY_ID from XLE_REG_FUNCTIONS xx
5402: );
5403: end if;
5404: --XLE_REGISTRATIONS;ISSUING_AUTHORITY_SITE_ID
5403: end if;
5404: --XLE_REGISTRATIONS;ISSUING_AUTHORITY_SITE_ID
5405: appid:=204;
5406: if(regid_proc = true) then
5407: 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
5408:
5409: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5410: xx
5411: where (nvl(STATUS, 'A') in ('A','I'))
5413: in (select /*+ parallel(yy)*/ yy.ISSUING_AUTHORITY_SITE_ID from XLE_REGISTRATIONS
5414: yy
5415: ));
5416: else
5417: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5418: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5419: xx
5420: where (nvl(STATUS, 'A') in ('A','I'))
5421: and xx.PARTY_SITE_ID
5425: end if;
5426: --XLE_REG_FUNCTIONS;AUTHORITY_SITE_ID
5427: appid:=204;
5428: if(regid_proc = true) then
5429: 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
5430:
5431: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5432: xx
5433: where (nvl(STATUS, 'A') in ('A','I'))
5435: in (select /*+ parallel(yy)*/ yy.AUTHORITY_SITE_ID from XLE_REG_FUNCTIONS
5436: yy
5437: ));
5438: else
5439: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5440: temp.party_id in (select /*+ parallel (xx)*/ xx.PARTY_ID from HZ_PARTY_SITES
5441: xx
5442: where (nvl(STATUS, 'A') in ('A','I'))
5443: and xx.PARTY_SITE_ID
5447: end if;
5448: --IBW_PAGE_VIEWS;PARTY_ID
5449: appid:=666;
5450: if(regid_proc = true) then
5451: 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
5452:
5453: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
5454: );
5455: else
5452:
5453: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
5454: );
5455: else
5456: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5457: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_ID from IBW_PAGE_VIEWS xx
5458: );
5459: end if;
5460: --IBW_PAGE_VIEWS;PARTY_RELATIONSHIP_ID
5459: end if;
5460: --IBW_PAGE_VIEWS;PARTY_RELATIONSHIP_ID
5461: appid:=666;
5462: if(regid_proc = true) then
5463: 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
5464:
5465: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5466: );
5467: else
5464:
5465: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5466: );
5467: else
5468: delete /*+ parallel(temp) */ from hz_purge_gt temp where
5469: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5470: );
5471: end if;
5472: if(regid_proc = true) then
5469: temp.party_id in (select /*+ parallel(xx)*/ xx.PARTY_RELATIONSHIP_ID from IBW_PAGE_VIEWS xx
5470: );
5471: end if;
5472: if(regid_proc = true) then
5473: delete from hz_purge_gt temp where temp.party_id in (select appl.party_id from hz_application_trans_gt appl) ;
5474: end if;
5475:
5476:
5477: EXCEPTION