[Home] [Help]
493: ,p_account_hist_tbl IN OUT NOCOPY csi_diagnostics_pkg.T_NUM
494: ) IS
495: BEGIN
496: FOR i in p_account_tbl.FIRST .. p_account_tbl.LAST LOOP
497: select CSI_IP_ACCOUNTS_H_S.nextval
498: into p_account_hist_tbl(i) from dual;
499: --
500: p_account_rec_tab.ip_account_id(i) := p_account_tbl(i).ip_account_id;
501: p_account_rec_tab.parent_tbl_index(i) := p_account_tbl(i).parent_tbl_index;
925: x_pa_tbl(l_ind).attribute14 := p_pa_h_tbl(l_ind).attribute14;
926: x_pa_tbl(l_ind).attribute15 := p_pa_h_tbl(l_ind).attribute15;
927: SELECT object_version_number
928: INTO x_pa_tbl(l_ind).object_version_number
929: FROM csi_ip_accounts
930: WHERE ip_account_id = p_pa_h_tbl(l_ind).ip_account_id;
931:
932: END LOOP;
933: END IF;
2149: --
2150: -- Insert into History
2151: log('Before Inserting into Party Accounts history');
2152: FORALL i in 1 .. l_account_rec_tab.ip_account_id.count
2153: INSERT INTO CSI_IP_ACCOUNTS_H(
2154: IP_ACCOUNT_HISTORY_ID,
2155: IP_ACCOUNT_ID,
2156: TRANSACTION_ID,
2157: NEW_PARTY_ACCOUNT_ID,
4195:
4196: BEGIN
4197: SELECT ip_account_id
4198: INTO l_owner_ipa_id
4199: FROM csi_ip_accounts
4200: WHERE instance_party_id = l_owner_ip_id
4201: AND relationship_type_code = 'OWNER';
4202: EXCEPTION
4203: WHEN no_data_found THEN
10425: --
10426: PROCEDURE Delete_Dup_Account IS
10427: CURSOR IP_CUR IS
10428: select cia.instance_party_id
10429: from csi_ip_accounts cia
10430: where cia.relationship_type_code = 'OWNER'
10431: group by cia.instance_party_id
10432: having count(*) > 1;
10433: --
10432: having count(*) > 1;
10433: --
10434: CURSOR ACCT_CUR(p_inst_party_id IN NUMBER,p_ip_acct_id IN NUMBER) IS
10435: select ip_account_id
10436: from csi_ip_accounts
10437: where instance_party_id = p_inst_party_id
10438: and relationship_type_code = 'OWNER'
10439: and ip_account_id <> p_ip_acct_id
10440: order by ip_account_id asc;
10453: v_min_ret_id := null;
10454: --
10455: select max(ip_account_id)
10456: into v_max_ip_acct_id
10457: from csi_ip_accounts
10458: where instance_party_id = party.instance_party_id
10459: and relationship_type_code = 'OWNER'
10460: and ((active_end_date is null) or (active_end_date > sysdate));
10461: --
10464: END IF;
10465: --
10466: select min(ip_account_history_id)
10467: into v_min_ret_id
10468: from CSI_IP_ACCOUNTS_H
10469: where ip_account_id = v_max_ip_acct_id;
10470: --
10471: v_old_account_id := NULL;
10472: --
10473: FOR acct in ACCT_CUR(party.instance_party_id,v_max_ip_acct_id) LOOP
10474: v_min_history_id := NULL;
10475: select min(ip_account_history_id)
10476: into v_min_history_id
10477: from CSI_IP_ACCOUNTS_H
10478: where ip_account_id = acct.ip_account_id;
10479: --
10480: IF v_min_history_id IS NULL THEN
10481: Raise Process_next;
10481: Raise Process_next;
10482: END IF;
10483: --
10484: IF ACCT_CUR%ROWCOUNT > 1 THEN
10485: Update CSI_IP_ACCOUNTS_H
10486: set old_party_account_id = v_old_account_id
10487: where ip_account_history_id = v_min_history_id;
10488: END IF;
10489: --
10486: set old_party_account_id = v_old_account_id
10487: where ip_account_history_id = v_min_history_id;
10488: END IF;
10489: --
10490: DELETE from CSI_IP_ACCOUNTS_H
10491: where ip_account_id = acct.ip_account_id
10492: and ip_account_history_id <> v_min_history_id;
10493: --
10494: v_old_account_id := NULL;
10494: v_old_account_id := NULL;
10495: Begin
10496: select new_party_account_id
10497: into v_old_account_id
10498: from CSI_IP_ACCOUNTS_H
10499: where ip_account_history_id = v_min_history_id;
10500: Exception
10501: when no_data_found then
10502: v_old_account_id := NULL;
10501: when no_data_found then
10502: v_old_account_id := NULL;
10503: End;
10504: --
10505: Update CSI_IP_ACCOUNTS_H
10506: set ip_account_id = v_max_ip_acct_id
10507: where ip_account_history_id = v_min_history_id;
10508: --
10509: Delete from CSI_IP_ACCOUNTS
10505: Update CSI_IP_ACCOUNTS_H
10506: set ip_account_id = v_max_ip_acct_id
10507: where ip_account_history_id = v_min_history_id;
10508: --
10509: Delete from CSI_IP_ACCOUNTS
10510: where ip_account_id = acct.ip_account_id;
10511: --
10512: v_commit_counter := v_commit_counter + 1;
10513: IF v_commit_counter = 100 THEN
10514: v_commit_counter := 0;
10515: commit;
10516: END IF;
10517: END LOOP;
10518: Update CSI_IP_ACCOUNTS_H
10519: set old_party_account_id = v_old_account_id
10520: where ip_account_history_id = v_min_ret_id;
10521: Exception
10522: when Process_next Then
10801: when no_data_found then
10802: Raise Process_next;
10803: End;
10804: --
10805: Update CSI_IP_ACCOUNTS
10806: set active_end_date = sysdate
10807: where instance_party_id = l_ip_id
10808: and relationship_type_code = 'OWNER';
10809: --
10809: --
10810: -- Insert into IP Accounts history
10811: select ip_account_id
10812: into l_ip_account_id
10813: from CSI_IP_ACCOUNTS
10814: where instance_party_id = l_ip_id
10815: and relationship_type_code = 'OWNER';
10816: --
10817: INSERT INTO CSI_IP_ACCOUNTS_H
10813: from CSI_IP_ACCOUNTS
10814: where instance_party_id = l_ip_id
10815: and relationship_type_code = 'OWNER';
10816: --
10817: INSERT INTO CSI_IP_ACCOUNTS_H
10818: ( IP_ACCOUNT_HISTORY_ID
10819: ,IP_ACCOUNT_ID
10820: ,TRANSACTION_ID
10821: ,OLD_ACTIVE_END_DATE
10827: ,LAST_UPDATE_LOGIN
10828: ,OBJECT_VERSION_NUMBER
10829: )
10830: VALUES
10831: ( CSI_IP_ACCOUNTS_H_S.nextval
10832: ,l_ip_account_id
10833: ,l_txn_id
10834: ,NULL
10835: ,SYSDATE
15872: FOR ip_rec IN ip_cur(dup_inst_rec.instance_id)
15873: LOOP
15874:
15875: -- there may not be an account, but just in case
15876: DELETE FROM csi_ip_accounts
15877: WHERE instance_party_id = ip_rec.instance_party_id;
15878:
15879: DELETE FROM csi_i_parties_h
15880: WHERE instance_party_id = ip_rec.instance_party_id;