[Home] [Help]
786: NEW_ATTRIBUTE14 ,
787: NEW_ATTRIBUTE15 ,
788: NEW_PRIMARY_FLAG ,
789: NEW_PREFERRED_FLAG
790: FROM CSI_I_PARTIES_H
791: WHERE instance_party_id = p_inst_pty_id
792: -- and creation_date < x_first_no_dump
793: -- and full_dump_flag = 'N'
794: order by creation_date;
872: NEW_ATTRIBUTE14 ,
873: NEW_ATTRIBUTE15 ,
874: NEW_PRIMARY_FLAG ,
875: NEW_PREFERRED_FLAG
876: FROM CSI_I_PARTIES_H
877: WHERE instance_party_history_id = p_inst_pty_hist_id
878: and full_dump_flag = 'Y' ;
879:
880: BEGIN
932:
933: CURSOR get_nearest_full_dump(p_inst_party_id IN NUMBER ,p_time IN DATE) IS
934: SELECT
935: MAX(instance_party_history_id)
936: FROM CSI_I_PARTIES_H
937: WHERE creation_date <= p_time
938: and instance_party_id = p_inst_party_id
939: and full_dump_flag = 'Y' ;
940:
992: OLD_PRIMARY_FLAG ,
993: NEW_PRIMARY_FLAG ,
994: OLD_PREFERRED_FLAG ,
995: NEW_PREFERRED_FLAG
996: FROM CSI_I_PARTIES_H
997: WHERE creation_date <= p_time
998: and creation_date >= p_nearest_full_dump
999: and instance_party_id = p_inst_party_id
1000: and full_dump_flag = 'N'
1554: active_start_date
1555: INTO p_party_rec.instance_party_id,
1556: p_party_rec.object_version_number,
1557: p_party_rec.active_start_date
1558: FROM csi_i_parties
1559: WHERE instance_id = p_party_rec.instance_id
1560: AND party_source_table = p_party_rec.party_source_table
1561: AND party_id = p_party_rec.party_id
1562: AND relationship_type_code = p_party_rec.relationship_type_code
1766: l_exists := NULL;
1767: BEGIN
1768: SELECT 'X'
1769: INTO l_exists
1770: FROM csi_i_parties
1771: WHERE instance_id <> p_party_rec.INSTANCE_ID
1772: AND instance_party_id = p_party_rec.CONTACT_IP_ID;
1773:
1774: IF l_exists IS NOT NULL THEN
2055: RAISE fnd_api.g_exc_error;
2056: END IF;
2057:
2058:
2059: -- Call table handlers to insert into the csi_i_parties table
2060: CSI_I_PARTIES_PKG.Insert_Row(
2061: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
2062: p_INSTANCE_ID => p_party_rec.INSTANCE_ID ,
2063: p_PARTY_SOURCE_TABLE => p_party_rec.PARTY_SOURCE_TABLE ,
2056: END IF;
2057:
2058:
2059: -- Call table handlers to insert into the csi_i_parties table
2060: CSI_I_PARTIES_PKG.Insert_Row(
2061: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
2062: p_INSTANCE_ID => p_party_rec.INSTANCE_ID ,
2063: p_PARTY_SOURCE_TABLE => p_party_rec.PARTY_SOURCE_TABLE ,
2064: p_PARTY_ID => p_party_rec.PARTY_ID ,
2122:
2123: -- Generate a unique instance_party_history_id from the sequence
2124: l_inst_party_his_id := CSI_Instance_parties_vld_pvt.gen_inst_party_hist_id;
2125:
2126: -- Call table handlers to insert into csi_i_parties_h table
2127: CSI_I_PARTIES_H_PKG.Insert_Row
2128: (
2129: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
2130: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
2123: -- Generate a unique instance_party_history_id from the sequence
2124: l_inst_party_his_id := CSI_Instance_parties_vld_pvt.gen_inst_party_hist_id;
2125:
2126: -- Call table handlers to insert into csi_i_parties_h table
2127: CSI_I_PARTIES_H_PKG.Insert_Row
2128: (
2129: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
2130: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
2131: p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
2321: null call_contracts,
2322: null interface_id,
2323: null contact_parent_tbl_index,
2324: null cascade_ownership_flag -- Added for bug 2972082
2325: FROM CSI_I_PARTIES
2326: WHERE INSTANCE_PARTY_ID = p_inst_party_id
2327: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
2328:
2329: CURSOR get_cont_party_rec (p_cont_ip_id IN NUMBER) IS
2355: attribute15 ,
2356: object_version_number,
2357: primary_flag ,
2358: preferred_flag
2359: FROM CSI_I_PARTIES
2360: WHERE CONTACT_IP_ID = p_cont_ip_id
2361: AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
2362: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
2363:
2547: ,old_primary_flag
2548: ,new_primary_flag
2549: ,old_preferred_flag
2550: ,new_preferred_flag
2551: FROM csi_i_parties_h
2552: WHERE csi_i_parties_h.instance_party_history_id = p_party_hist_id
2553: FOR UPDATE OF object_version_number ;
2554: l_pty_hist_csr pty_hist_csr%rowtype;
2555: l_party_hist_id NUMBER;
2548: ,new_primary_flag
2549: ,old_preferred_flag
2550: ,new_preferred_flag
2551: FROM csi_i_parties_h
2552: WHERE csi_i_parties_h.instance_party_history_id = p_party_hist_id
2553: FOR UPDATE OF object_version_number ;
2554: l_pty_hist_csr pty_hist_csr%rowtype;
2555: l_party_hist_id NUMBER;
2556: l_open_service VARCHAR2(1);
2653: THEN
2654: BEGIN
2655: SELECT object_version_number
2656: INTO p_party_rec.object_version_number
2657: FROM csi_i_parties
2658: WHERE instance_party_id=p_party_rec.instance_party_id;
2659: EXCEPTION
2660: WHEN NO_DATA_FOUND THEN
2661: csi_gen_utility_pvt.put_line('Passed instance_party_id : '||p_party_rec.instance_party_id||' is not found.');
2869: END IF;
2870: END IF;
2871: END IF;
2872: END IF;
2873: -- Call table handlers to insert into the csi_i_parties table
2874: -- Verify if the party_source_table exists in CSI_LOOKUPS
2875: IF (p_party_rec.PARTY_SOURCE_TABLE <> FND_API.G_MISS_CHAR) THEN
2876: IF NOT(CSI_Instance_parties_vld_pvt.Is_Pty_Source_tab_Valid(p_party_rec.PARTY_SOURCE_TABLE)) THEN
2877: RAISE FND_API.G_EXC_ERROR;
2990: THEN
2991: BEGIN
2992: SELECT instance_party_id
2993: INTO l_ins_pty_found
2994: FROM csi_i_parties
2995: WHERE instance_id = l_alt_pk_instance_id
2996: AND party_source_table = l_alt_pk_pty_source_tab
2997: AND party_id = l_alt_pk_party_id
2998: AND relationship_type_code = l_alt_pk_rel_type_code
2998: AND relationship_type_code = l_alt_pk_rel_type_code
2999: AND contact_flag = l_alt_pk_contact_flag
3000: AND NVL(contact_ip_id,fnd_api.g_miss_num) = NVL(p_party_rec.contact_ip_id,fnd_api.g_miss_num)
3001: AND ((active_end_date IS NULL) OR (active_end_date >= sysdate));
3002: -- If found then there exists a record in csi_i_parties, Hence
3003: -- I need to expire(if active) this record.
3004: BEGIN
3005: SELECT active_end_date
3006: INTO l_pty_end_date
3003: -- I need to expire(if active) this record.
3004: BEGIN
3005: SELECT active_end_date
3006: INTO l_pty_end_date
3007: FROM csi_i_parties
3008: WHERE instance_party_id=p_party_rec.instance_party_id
3009: AND ((active_end_date IS NULL) OR (active_end_date > sysdate));
3010: -- Active record found so make it inactive.
3011: p_party_rec.active_end_date:=sysdate;
3014: NULL;
3015: END;
3016: EXCEPTION
3017: WHEN TOO_MANY_ROWS THEN
3018: csi_gen_utility_pvt.put_line('Too many rows exist in csi_i_parties with the same party rel combination.');
3019: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_REL_COMB');
3020: FND_MESSAGE.SET_TOKEN('PARTY_REL_COMB',to_char(l_alt_pk_instance_id) ||','||l_alt_pk_pty_source_tab||','||to_char(l_alt_pk_party_id)||','||l_alt_pk_rel_type_code||','||to_char(p_party_rec.contact_ip_id));
3021: FND_MSG_PUB.Add;
3022: RAISE FND_API.G_EXC_ERROR;
3082: fnd_msg_pub.Add;
3083: RAISE fnd_api.g_exc_error;
3084: END IF;
3085: -- Call table handlers to update the table
3086: CSI_I_PARTIES_PKG.Update_Row
3087: (
3088: p_INSTANCE_PARTY_ID => p_party_rec.instance_party_id,
3089: p_INSTANCE_ID => p_party_rec.instance_id,
3090: p_PARTY_SOURCE_TABLE => p_party_rec.PARTY_SOURCE_TABLE,
3171: -- Start of Changes for Bug#2547034 on 09/20/02 - rtalluri
3172: BEGIN
3173: SELECT instance_party_history_id
3174: INTO l_party_hist_id
3175: FROM csi_i_parties_h h
3176: WHERE h.transaction_id = p_txn_rec.transaction_id
3177: AND h.instance_party_id = p_party_rec.instance_party_id;
3178:
3179: OPEN pty_hist_csr(l_party_hist_id);
3181: CLOSE pty_hist_csr;
3182:
3183: IF l_pty_hist_csr.full_dump_flag = 'Y'
3184: THEN
3185: CSI_I_PARTIES_H_PKG.update_Row
3186: (
3187: p_INSTANCE_PARTY_HISTORY_ID => l_party_hist_id ,
3188: p_INSTANCE_PARTY_ID => fnd_api.g_miss_num ,
3189: p_TRANSACTION_ID => fnd_api.g_miss_num ,
3621: l_pty_hist_csr.old_preferred_flag := fnd_api.g_miss_char;
3622: l_pty_hist_csr.new_preferred_flag := l_temp_party_rec.preferred_flag;
3623: END IF;
3624:
3625: CSI_I_PARTIES_H_PKG.update_Row
3626: (
3627: p_INSTANCE_PARTY_HISTORY_ID => l_party_hist_id ,
3628: p_INSTANCE_PARTY_ID => FND_API.G_MISS_NUM ,
3629: p_TRANSACTION_ID => FND_API.G_MISS_NUM ,
3768: IF (p_party_rec.PREFERRED_FLAG = FND_API.G_MISS_CHAR) THEN
3769: l_temp_party_rec.PREFERRED_FLAG := l_curr_party_rec.PREFERRED_FLAG ;
3770: END IF;
3771:
3772: -- Call table handlers to insert into csi_i_parties_h table
3773: CSI_I_PARTIES_H_PKG.Insert_Row
3774: (
3775: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
3776: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
3769: l_temp_party_rec.PREFERRED_FLAG := l_curr_party_rec.PREFERRED_FLAG ;
3770: END IF;
3771:
3772: -- Call table handlers to insert into csi_i_parties_h table
3773: CSI_I_PARTIES_H_PKG.Insert_Row
3774: (
3775: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
3776: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
3777: p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
4085: l_party_hist_rec.old_preferred_flag := l_curr_party_rec.preferred_flag ;
4086: l_party_hist_rec.new_preferred_flag := p_party_rec.preferred_flag ;
4087: END IF;
4088:
4089: -- Call table handlers to insert into csi_i_parties_h table
4090: CSI_I_PARTIES_H_PKG.Insert_Row
4091: (
4092: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
4093: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
4086: l_party_hist_rec.new_preferred_flag := p_party_rec.preferred_flag ;
4087: END IF;
4088:
4089: -- Call table handlers to insert into csi_i_parties_h table
4090: CSI_I_PARTIES_H_PKG.Insert_Row
4091: (
4092: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
4093: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
4094: p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
4317: null call_contracts,
4318: null interface_id,
4319: null contact_parent_tbl_index,
4320: null cascade_ownership_flag -- Added for bug 2972082
4321: FROM CSI_I_PARTIES
4322: WHERE INSTANCE_PARTY_ID = p_inst_party_id
4323: AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
4324: FOR UPDATE OF OBJECT_VERSION_NUMBER;
4325:
5532: ELSE
5533: BEGIN
5534: SELECT relationship_type_code
5535: INTO l_rel_type_code
5536: FROM csi_i_parties
5537: WHERE instance_party_id=l_curr_party_acct_rec.instance_party_id;
5538:
5539: IF (l_rel_type_code='OWNER' AND
5540: l_curr_party_acct_rec.relationship_type_code='OWNER' )/* OR
5768: AND relationship_type_code = l_alt_pk_rel_type_code
5769: AND ((active_end_date IS NULL) OR (active_end_date >= sysdate))
5770: AND ROWNUM=1;
5771:
5772: -- If found then there exists a record in csi_i_parties, Hence
5773: -- I need to expire(if active) this record.
5774: BEGIN
5775: SELECT active_end_date
5776: INTO l_temp_acct_date
6488: IF (p_party_account_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
6489: l_temp_party_account_rec.ATTRIBUTE15 := l_curr_party_acct_rec.ATTRIBUTE15 ;
6490: END IF;
6491:
6492: -- Call table handlers to insert into csi_i_parties_h table
6493: CSI_IP_ACCOUNTS_H_PKG.Insert_Row
6494: (
6495: px_IP_ACCOUNT_HISTORY_ID => l_ip_account_history_id ,
6496: p_IP_ACCOUNT_ID => p_party_account_rec.ip_account_id,
6784: l_account_hist_rec.old_instance_party_id := l_curr_party_acct_rec.instance_party_id ;
6785: l_account_hist_rec.new_instance_party_id := p_party_account_rec.instance_party_id ;
6786: END IF;
6787: -- End addition
6788: -- Call table handlers to insert into csi_i_parties_h table
6789: CSI_IP_ACCOUNTS_H_PKG.Insert_Row
6790: (
6791: px_IP_ACCOUNT_HISTORY_ID => l_ip_account_history_id ,
6792: p_IP_ACCOUNT_ID => p_party_account_rec.IP_ACCOUNT_ID ,
6890: l_new_party_id := null;
6891: Begin
6892: select old_party_id,new_party_id
6893: into l_old_party_id,l_new_party_id
6894: from CSI_I_PARTIES_H
6895: where instance_party_id = l_party_rec.instance_party_id
6896: and transaction_id = p_txn_rec.transaction_id;
6897: Exception
6898: when no_data_found then
7502: x_contact_details.email
7503:
7504: FROM
7505: HZ_PARTIES HP,
7506: CSI_I_PARTIES CIP,
7507: -- CSI_IPA_RELATION_TYPES CIR,
7508: HZ_CONTACT_POINTS HCP_WP,
7509: HZ_CONTACT_POINTS HCP_HP,
7510: HZ_CONTACT_POINTS HCP_PG,
7512: HZ_CONTACT_POINTS HCP_FX,
7513: HZ_CONTACT_POINTS HCP_MB,
7514: -- CSI_LOOKUPS CL,
7515: HZ_RELATIONSHIPS HR,
7516: CSI_I_PARTIES CIPO
7517: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id -- party_id for the contact
7518: AND CIP.PARTY_ID = HR.SUBJECT_ID
7519: AND CIP.CONTACT_IP_ID = CIPO.INSTANCE_PARTY_ID
7520: AND CIPO.PARTY_ID = HR.OBJECT_ID
7601: x_contact_details.postal_code,
7602: x_contact_details.state,
7603: x_contact_details.country,
7604: x_contact_details.email
7605: FROM CSI_I_PARTIES CIP,
7606: PO_VENDOR_CONTACTS PVC,
7607: PO_VENDOR_SITES_ALL PVS
7608: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7609: AND CIP.PARTY_ID = PVC.VENDOR_CONTACT_ID
7647: x_contact_details.postal_code,
7648: x_contact_details.state,
7649: x_contact_details.country,
7650: x_contact_details.email
7651: FROM CSI_I_PARTIES CIP,
7652: PER_ALL_PEOPLE_F PAP
7653: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7654: AND CIP.PARTY_ID = PAP.PERSON_ID
7655: AND PAP.EFFECTIVE_START_DATE <= SYSDATE
7692: x_contact_details.postal_code,
7693: x_contact_details.state,
7694: x_contact_details.country,
7695: x_contact_details.email
7696: FROM CSI_I_PARTIES CIP,
7697: JTF_RS_GROUPS_VL JG
7698: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7699: AND CIP.PARTY_ID = JG.GROUP_ID
7700: AND rownum < 2;
7735: x_contact_details.postal_code,
7736: x_contact_details.state,
7737: x_contact_details.country,
7738: x_contact_details.email
7739: FROM CSI_I_PARTIES CIP,
7740: JTF_RS_TEAMS_VL JT
7741: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7742: AND CIP.PARTY_ID = JT.TEAM_ID
7743: AND rownum < 2;
7885: ciph.OLD_PRIMARY_FLAG,
7886: ciph.NEW_PRIMARY_FLAG,
7887: cip.INSTANCE_ID,
7888: ciph.creation_date --Added for bug 2781480
7889: FROM csi_i_parties_h ciph,
7890: csi_i_parties cip
7891: WHERE ciph.transaction_id = i_transaction_id
7892: AND ciph.instance_party_id = cip.instance_party_id; -- Added by sk on 08-APR for fixing bug
7893: -- 2304649 .
7886: ciph.NEW_PRIMARY_FLAG,
7887: cip.INSTANCE_ID,
7888: ciph.creation_date --Added for bug 2781480
7889: FROM csi_i_parties_h ciph,
7890: csi_i_parties cip
7891: WHERE ciph.transaction_id = i_transaction_id
7892: AND ciph.instance_party_id = cip.instance_party_id; -- Added by sk on 08-APR for fixing bug
7893: -- 2304649 .
7894: -- Added for bug 2781480
8611: cah.NEW_INSTANCE_PARTY_ID,
8612: cah.creation_date
8613: FROM csi_ip_accounts_h cah,
8614: csi_ip_accounts ca,
8615: csi_i_parties cip
8616: WHERE cah.transaction_id = i_transaction_id
8617: AND cah.ip_account_id = ca.ip_account_id
8618: AND ca.instance_party_id = cip.instance_party_id; -- Added by sguthiva on 9-APR for bug 2304649
8619: