[Home] [Help]
457: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_DUNNING',FALSE);
458: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
459: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
460: IEX_DEBUG_PUB.logMessage('l_profile_val = ' || l_profile_val);
461: /* process IEX_STRATEGIES.CUST_ACCOUNT_ID */
462: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
463: IEX_DEBUG_PUB.logMessage('IEX_DUNNING.DUNNING_OBJECT_ID');
464: END IF;
465: open merged_records1;
687: |
688: | PROCEDURE
689: | STRATEGY_MERGE
690: | DESCRIPTION :
691: | Account merge procedure for the table, IEX_STRATEGIES
692: |
693: | NOTES:
694: | ******* Please delete these lines after modifications *******
695: | This account merge procedure was NOT generated using a perl script.
705: INDEX BY BINARY_INTEGER;
706: MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
707:
708: TYPE STRATEGY_ID_LIST_TYPE IS TABLE OF
709: IEX_STRATEGIES.STRATEGY_ID%TYPE
710: INDEX BY BINARY_INTEGER;
711: PRIMARY_KEY_ID_LIST STRATEGY_ID_LIST_TYPE;
712:
713: TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
710: INDEX BY BINARY_INTEGER;
711: PRIMARY_KEY_ID_LIST STRATEGY_ID_LIST_TYPE;
712:
713: TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
714: IEX_STRATEGIES.CUST_ACCOUNT_ID%TYPE
715: INDEX BY BINARY_INTEGER;
716: NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
717: NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
718:
716: NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
717: NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
718:
719: TYPE JTF_OBJECT_ID_LIST_TYPE IS TABLE OF
720: IEX_STRATEGIES.JTF_OBJECT_ID%TYPE
721: INDEX BY BINARY_INTEGER;
722: NUM_COL2_ORIG_LIST JTF_OBJECT_ID_LIST_TYPE;
723: NUM_COL2_NEW_LIST JTF_OBJECT_ID_LIST_TYPE;
724: NUM_COL3_ORIG_LIST JTF_OBJECT_ID_LIST_TYPE;
726:
727:
728: --Added for bug#6974531 by schekuri on 14-Aug-2008
729: TYPE STATUS_CODE_LIST_TYPE IS TABLE OF
730: IEX_STRATEGIES.STATUS_CODE%TYPE
731: INDEX BY BINARY_INTEGER;
732: STATUS_CODE_LIST STATUS_CODE_LIST_TYPE;
733: TYPE PARTY_ID_LIST_TYPE IS TABLE OF
734: IEX_STRATEGIES.PARTY_ID%TYPE
730: IEX_STRATEGIES.STATUS_CODE%TYPE
731: INDEX BY BINARY_INTEGER;
732: STATUS_CODE_LIST STATUS_CODE_LIST_TYPE;
733: TYPE PARTY_ID_LIST_TYPE IS TABLE OF
734: IEX_STRATEGIES.PARTY_ID%TYPE
735: INDEX BY BINARY_INTEGER;
736: PARTY_ID_LIST PARTY_ID_LIST_TYPE;
737:
738:
737:
738:
739: l_profile_val VARCHAR2(30);
740:
741: /* this cursor is for IEX_STRATEGIES.CUST_ACCOUNT_ID column update */
742: CURSOR merged_records1 IS
743: SELECT distinct CUSTOMER_MERGE_HEADER_ID
744: ,yt.STRATEGY_ID
745: ,yt.CUST_ACCOUNT_ID
744: ,yt.STRATEGY_ID
745: ,yt.CUST_ACCOUNT_ID
746: ,hca.party_id
747: ,yt.status_code --Added for bug#6974531 by schekuri on 14-Aug-2008
748: FROM IEX_STRATEGIES yt, ra_customer_merges m, hz_cust_accounts hca
749: WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID AND
750: hca.cust_account_id = m.customer_id AND
751: m.process_flag = 'N' AND
752: m.request_id = req_id AND
752: m.request_id = req_id AND
753: m.set_number = set_num;
754:
755:
756: /* this cursor is for IEX_STRATEGIES.JTF_OBJECT_ID column update if Object is IEX_ACCOUNT */
757: CURSOR merged_records2 IS
758: SELECT distinct CUSTOMER_MERGE_HEADER_ID
759: ,STRATEGY_ID
760: ,JTF_OBJECT_ID
758: SELECT distinct CUSTOMER_MERGE_HEADER_ID
759: ,STRATEGY_ID
760: ,JTF_OBJECT_ID
761: ,yt.status_code --Added for bug#6974531 by schekuri on 14-Aug-2008
762: FROM IEX_STRATEGIES yt, ra_customer_merges m
763: WHERE yt.JTF_OBJECT_ID = m.DUPLICATE_ID AND
764: m.process_flag = 'N' AND
765: m.request_id = req_id AND
766: m.set_number = set_num AND
765: m.request_id = req_id AND
766: m.set_number = set_num AND
767: yt.jtf_object_type = 'IEX_ACCOUNT';
768:
769: /* this cursor is for IEX_STRATEGIES.JTF_OBJECT_ID column update if Object is IEX_BILLTO */
770: CURSOR merged_records3 IS
771: SELECT distinct CUSTOMER_MERGE_HEADER_ID
772: ,STRATEGY_ID
773: ,JTF_OBJECT_ID
771: SELECT distinct CUSTOMER_MERGE_HEADER_ID
772: ,STRATEGY_ID
773: ,JTF_OBJECT_ID
774: ,yt.status_code --Added for bug#6974531 by schekuri on 14-Aug-2008
775: FROM IEX_STRATEGIES yt, ra_customer_merges m
776: WHERE yt.JTF_OBJECT_ID = m.DUPLICATE_SITE_ID AND
777: m.process_flag = 'N' AND
778: m.request_id = req_id AND
779: m.set_number = set_num AND
791: IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.STRATEGY_MERGE BEGIN');
792: END IF;
793:
794: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
795: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_STRATEGIES',FALSE);
796: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
797: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
798:
799: /* process IEX_STRATEGIES.CUST_ACCOUNT_ID */
795: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_STRATEGIES',FALSE);
796: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
797: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
798:
799: /* process IEX_STRATEGIES.CUST_ACCOUNT_ID */
800: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
801: IEX_DEBUG_PUB.logMessage('IEX_STRATEGIES.CUST_ACCOUNT_ID');
802: END IF;
803: open merged_records1;
797: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
798:
799: /* process IEX_STRATEGIES.CUST_ACCOUNT_ID */
800: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
801: IEX_DEBUG_PUB.logMessage('IEX_STRATEGIES.CUST_ACCOUNT_ID');
802: END IF;
803: open merged_records1;
804: LOOP
805: FETCH merged_records1 BULK COLLECT INTO
839: LAST_UPDATE_DATE,
840: LAST_UPDATED_BY)
841: VALUES
842: (HZ_CUSTOMER_MERGE_LOG_s.nextval,
843: 'IEX_STRATEGIES',
844: MERGE_HEADER_ID_LIST(I),
845: PRIMARY_KEY_ID_LIST(I),
846: NUM_COL1_ORIG_LIST(I),
847: NUM_COL1_NEW_LIST(I),
863:
864: select count(CUST_ACCOUNT_ID) into l_acc_status_cnt from hz_cust_accounts where CUST_ACCOUNT_ID = NUM_COL1_ORIG_LIST(I) and status = 'A';
865: if l_acc_status_cnt = 0 then
866:
867: UPDATE IEX_STRATEGIES yt SET
868: CUST_ACCOUNT_ID = NUM_COL1_NEW_LIST(I)
869: , PARTY_ID = PARTY_ID_LIST(I)
870: , LAST_UPDATE_DATE = SYSDATE
871: , last_updated_by = arp_standard.profile.user_id
887: arp_message.set_name('AR','AR_ROWS_UPDATED');
888: arp_message.set_token('NUM_ROWS',to_char(l_count));
889:
890: <
891: /* process IEX_STRATEGIES.JTF_OBJECT_ID where JTF_OBJECT_TYPE = 'IEX_ACCOUNT' */
892: MERGE_HEADER_ID_LIST.delete;
893: PRIMARY_KEY_ID_LIST.delete;
894: PARTY_ID_LIST.delete;
895: STATUS_CODE_LIST.delete; --Added for bug#6974531 by schekuri on 14-Aug-2008
894: PARTY_ID_LIST.delete;
895: STATUS_CODE_LIST.delete; --Added for bug#6974531 by schekuri on 14-Aug-2008
896: l_count := 0;
897: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
898: IEX_DEBUG_PUB.logMessage('IEX_STRATEGIES.JTF_OBJECT_ID.TYPE=IEX_ACCOUNT');
899: END IF;
900: open merged_records2;
901: LOOP
902: FETCH merged_records2 BULK COLLECT INTO
936: LAST_UPDATE_DATE,
937: LAST_UPDATED_BY)
938: VALUES
939: (HZ_CUSTOMER_MERGE_LOG_s.nextval,
940: 'IEX_STRATEGIES',
941: MERGE_HEADER_ID_LIST(I),
942: PRIMARY_KEY_ID_LIST(I),
943: NUM_COL2_ORIG_LIST(I),
944: NUM_COL2_NEW_LIST(I),
962:
963: select count(CUST_ACCOUNT_ID) into l_acc_status_cnt from hz_cust_accounts where CUST_ACCOUNT_ID = NUM_COL2_ORIG_LIST(I) and status = 'A';
964: if l_acc_status_cnt = 0 then
965:
966: UPDATE IEX_STRATEGIES yt SET
967: JTF_OBJECT_ID = NUM_COL2_NEW_LIST(I)
968: , LAST_UPDATE_DATE = SYSDATE
969: , last_updated_by = arp_standard.profile.user_id
970: , last_update_login = arp_standard.profile.last_update_login
999: arp_message.set_token('NUM_ROWS',to_char(l_count));
1000:
1001: <
1002:
1003: /* process IEX_STRATEGIES.JTF_OBJECT_ID where JTF_OBJECT_TYPE = 'IEX_BILLTO' */
1004: MERGE_HEADER_ID_LIST.delete;
1005: PRIMARY_KEY_ID_LIST.delete;
1006: STATUS_CODE_LIST.delete; --Added for bug#6974531 by schekuri on 14-Aug-2008
1007: l_count := 0;
1005: PRIMARY_KEY_ID_LIST.delete;
1006: STATUS_CODE_LIST.delete; --Added for bug#6974531 by schekuri on 14-Aug-2008
1007: l_count := 0;
1008: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1009: IEX_DEBUG_PUB.logMessage('IEX_STRATEGIES.JTF_OBJECT_ID.TYPE=IEX_BILLTO');
1010: END IF;
1011: open merged_records3;
1012: LOOP
1013: FETCH merged_records3 BULK COLLECT INTO
1047: LAST_UPDATE_DATE,
1048: LAST_UPDATED_BY)
1049: VALUES (
1050: HZ_CUSTOMER_MERGE_LOG_s.nextval,
1051: 'IEX_STRATEGIES',
1052: MERGE_HEADER_ID_LIST(I),
1053: PRIMARY_KEY_ID_LIST(I),
1054: NUM_COL3_ORIG_LIST(I),
1055: NUM_COL3_NEW_LIST(I),
1071: -- Changed for all loop to for loop to fix 14492050 Snuthala
1072: for I in MERGE_HEADER_ID_LIST.first..MERGE_HEADER_ID_LIST.last loop
1073: select count(site_use_id) into l_acc_status_cnt from hz_cust_site_uses_all where site_use_id = NUM_COL3_ORIG_LIST(I) and status = 'A';
1074: if l_acc_status_cnt = 0 then
1075: UPDATE IEX_STRATEGIES yt SET
1076: -- CUST_ACCOUNT_ID = NUM_COL3_NEW_LIST(I) Updated for bug#6974531 by schekuri on 14-Aug-2008
1077: JTF_OBJECT_ID = NUM_COL3_NEW_LIST(I)
1078: , CUSTOMER_SITE_USE_ID = NUM_COL3_NEW_LIST(I)
1079: , LAST_UPDATE_DATE = SYSDATE
1696: iex_writeoffs
1697: iex_bankruptcies
1698: iex_litigations
1699: -- 02182002 add IEX_SCORE_HISTORIES
1700: IEX_STRATEGIES
1701: */
1702: UPDATE IEX_STRATEGIES
1703: SET JTF_OBJECT_ID = p_To_FK_ID,
1704: last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1698: iex_litigations
1699: -- 02182002 add IEX_SCORE_HISTORIES
1700: IEX_STRATEGIES
1701: */
1702: UPDATE IEX_STRATEGIES
1703: SET JTF_OBJECT_ID = p_To_FK_ID,
1704: last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1705: last_updated_by = HZ_UTILITY_V2PUB.user_id,
1706: last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1714: WHERE JTF_OBJECT_ID = p_from_fk_id AND
1715: JTF_OBJECT_TYPE = 'PARTY';
1716:
1717: -- Begin - 10/12/2005 - Andre Araujo - Need to update party_id also
1718: UPDATE IEX_STRATEGIES
1719: SET last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1720: last_updated_by = HZ_UTILITY_V2PUB.user_id,
1721: last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1722: request_id = HZ_UTILITY_V2PUB.request_id,