[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:
2548: ,old_primary_flag
2549: ,new_primary_flag
2550: ,old_preferred_flag
2551: ,new_preferred_flag
2552: FROM csi_i_parties_h
2553: WHERE csi_i_parties_h.instance_party_history_id = p_party_hist_id
2554: FOR UPDATE OF object_version_number ;
2555: l_pty_hist_csr pty_hist_csr%rowtype;
2556: l_party_hist_id NUMBER;
2549: ,new_primary_flag
2550: ,old_preferred_flag
2551: ,new_preferred_flag
2552: FROM csi_i_parties_h
2553: WHERE csi_i_parties_h.instance_party_history_id = p_party_hist_id
2554: FOR UPDATE OF object_version_number ;
2555: l_pty_hist_csr pty_hist_csr%rowtype;
2556: l_party_hist_id NUMBER;
2557: l_open_service VARCHAR2(1);
2673: THEN
2674: BEGIN
2675: SELECT object_version_number
2676: INTO p_party_rec.object_version_number
2677: FROM csi_i_parties
2678: WHERE instance_party_id=p_party_rec.instance_party_id;
2679: EXCEPTION
2680: WHEN NO_DATA_FOUND THEN
2681: csi_gen_utility_pvt.put_line('Passed instance_party_id : '||p_party_rec.instance_party_id||' is not found.');
2763: csi_gen_utility_pvt.put_line( 'Added line of code...');
2764: END IF;
2765: --bug 7015558 fix...Contact_flag is missing.
2766: IF p_party_rec.RELATIONSHIP_TYPE_CODE <> 'OWNER' THEN
2767: select contact_flag into p_party_rec.contact_flag FROM csi_i_parties where instance_id=p_party_rec.instance_id and instance_party_id=p_party_rec.instance_party_id;
2768: END IF;
2769: IF (l_csi_debug_level > 0) THEN
2770: csi_gen_utility_pvt.put_line( 'Added line of code ends...');
2771: csi_gen_utility_pvt.put_line('Contact flag'|| p_party_rec.contact_flag);
2908: END IF;
2909: END IF;
2910: END IF;
2911: END IF;
2912: -- Call table handlers to insert into the csi_i_parties table
2913: -- Verify if the party_source_table exists in CSI_LOOKUPS
2914: IF (p_party_rec.PARTY_SOURCE_TABLE <> FND_API.G_MISS_CHAR) THEN
2915: IF NOT(CSI_Instance_parties_vld_pvt.Is_Pty_Source_tab_Valid(p_party_rec.PARTY_SOURCE_TABLE)) THEN
2916: RAISE FND_API.G_EXC_ERROR;
3029: THEN
3030: BEGIN
3031: SELECT instance_party_id
3032: INTO l_ins_pty_found
3033: FROM csi_i_parties
3034: WHERE instance_id = l_alt_pk_instance_id
3035: AND party_source_table = l_alt_pk_pty_source_tab
3036: AND party_id = l_alt_pk_party_id
3037: AND relationship_type_code = l_alt_pk_rel_type_code
3037: AND relationship_type_code = l_alt_pk_rel_type_code
3038: AND contact_flag = l_alt_pk_contact_flag
3039: AND NVL(contact_ip_id,fnd_api.g_miss_num) = NVL(p_party_rec.contact_ip_id,fnd_api.g_miss_num)
3040: AND ((active_end_date IS NULL) OR (active_end_date >= sysdate));
3041: -- If found then there exists a record in csi_i_parties, Hence
3042: -- I need to expire(if active) this record.
3043: BEGIN
3044: SELECT active_end_date
3045: INTO l_pty_end_date
3042: -- I need to expire(if active) this record.
3043: BEGIN
3044: SELECT active_end_date
3045: INTO l_pty_end_date
3046: FROM csi_i_parties
3047: WHERE instance_party_id=p_party_rec.instance_party_id
3048: AND ((active_end_date IS NULL) OR (active_end_date > sysdate));
3049: -- Active record found so make it inactive.
3050: p_party_rec.active_end_date:=sysdate;
3053: NULL;
3054: END;
3055: EXCEPTION
3056: WHEN TOO_MANY_ROWS THEN
3057: csi_gen_utility_pvt.put_line('Too many rows exist in csi_i_parties with the same party rel combination.');
3058: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_REL_COMB');
3059: 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));
3060: FND_MSG_PUB.Add;
3061: RAISE FND_API.G_EXC_ERROR;
3121: fnd_msg_pub.Add;
3122: RAISE fnd_api.g_exc_error;
3123: END IF;
3124: -- Call table handlers to update the table
3125: CSI_I_PARTIES_PKG.Update_Row
3126: (
3127: p_INSTANCE_PARTY_ID => p_party_rec.instance_party_id,
3128: p_INSTANCE_ID => p_party_rec.instance_id,
3129: p_PARTY_SOURCE_TABLE => p_party_rec.PARTY_SOURCE_TABLE,
3210: -- Start of Changes for Bug#2547034 on 09/20/02 - rtalluri
3211: BEGIN
3212: SELECT instance_party_history_id
3213: INTO l_party_hist_id
3214: FROM csi_i_parties_h h
3215: WHERE h.transaction_id = p_txn_rec.transaction_id
3216: AND h.instance_party_id = p_party_rec.instance_party_id;
3217:
3218: OPEN pty_hist_csr(l_party_hist_id);
3220: CLOSE pty_hist_csr;
3221:
3222: IF l_pty_hist_csr.full_dump_flag = 'Y'
3223: THEN
3224: CSI_I_PARTIES_H_PKG.update_Row
3225: (
3226: p_INSTANCE_PARTY_HISTORY_ID => l_party_hist_id ,
3227: p_INSTANCE_PARTY_ID => fnd_api.g_miss_num ,
3228: p_TRANSACTION_ID => fnd_api.g_miss_num ,
3660: l_pty_hist_csr.old_preferred_flag := fnd_api.g_miss_char;
3661: l_pty_hist_csr.new_preferred_flag := l_temp_party_rec.preferred_flag;
3662: END IF;
3663:
3664: CSI_I_PARTIES_H_PKG.update_Row
3665: (
3666: p_INSTANCE_PARTY_HISTORY_ID => l_party_hist_id ,
3667: p_INSTANCE_PARTY_ID => FND_API.G_MISS_NUM ,
3668: p_TRANSACTION_ID => FND_API.G_MISS_NUM ,
3807: IF (p_party_rec.PREFERRED_FLAG = FND_API.G_MISS_CHAR) THEN
3808: l_temp_party_rec.PREFERRED_FLAG := l_curr_party_rec.PREFERRED_FLAG ;
3809: END IF;
3810:
3811: -- Call table handlers to insert into csi_i_parties_h table
3812: CSI_I_PARTIES_H_PKG.Insert_Row
3813: (
3814: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
3815: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
3808: l_temp_party_rec.PREFERRED_FLAG := l_curr_party_rec.PREFERRED_FLAG ;
3809: END IF;
3810:
3811: -- Call table handlers to insert into csi_i_parties_h table
3812: CSI_I_PARTIES_H_PKG.Insert_Row
3813: (
3814: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
3815: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
3816: p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
4124: l_party_hist_rec.old_preferred_flag := l_curr_party_rec.preferred_flag ;
4125: l_party_hist_rec.new_preferred_flag := p_party_rec.preferred_flag ;
4126: END IF;
4127:
4128: -- Call table handlers to insert into csi_i_parties_h table
4129: CSI_I_PARTIES_H_PKG.Insert_Row
4130: (
4131: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
4132: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
4125: l_party_hist_rec.new_preferred_flag := p_party_rec.preferred_flag ;
4126: END IF;
4127:
4128: -- Call table handlers to insert into csi_i_parties_h table
4129: CSI_I_PARTIES_H_PKG.Insert_Row
4130: (
4131: px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
4132: p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
4133: p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
4356: null call_contracts,
4357: null interface_id,
4358: null contact_parent_tbl_index,
4359: null cascade_ownership_flag -- Added for bug 2972082
4360: FROM CSI_I_PARTIES
4361: WHERE INSTANCE_PARTY_ID = p_inst_party_id
4362: AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
4363: FOR UPDATE OF OBJECT_VERSION_NUMBER;
4364:
5573: ELSE
5574: BEGIN
5575: SELECT relationship_type_code
5576: INTO l_rel_type_code
5577: FROM csi_i_parties
5578: WHERE instance_party_id=l_curr_party_acct_rec.instance_party_id;
5579:
5580: IF (l_rel_type_code='OWNER' AND
5581: l_curr_party_acct_rec.relationship_type_code='OWNER' )/* OR
5809: AND relationship_type_code = l_alt_pk_rel_type_code
5810: AND ((active_end_date IS NULL) OR (active_end_date >= sysdate))
5811: AND ROWNUM=1;
5812:
5813: -- If found then there exists a record in csi_i_parties, Hence
5814: -- I need to expire(if active) this record.
5815: BEGIN
5816: SELECT active_end_date
5817: INTO l_temp_acct_date
6529: IF (p_party_account_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
6530: l_temp_party_account_rec.ATTRIBUTE15 := l_curr_party_acct_rec.ATTRIBUTE15 ;
6531: END IF;
6532:
6533: -- Call table handlers to insert into csi_i_parties_h table
6534: CSI_IP_ACCOUNTS_H_PKG.Insert_Row
6535: (
6536: px_IP_ACCOUNT_HISTORY_ID => l_ip_account_history_id ,
6537: p_IP_ACCOUNT_ID => p_party_account_rec.ip_account_id,
6825: l_account_hist_rec.old_instance_party_id := l_curr_party_acct_rec.instance_party_id ;
6826: l_account_hist_rec.new_instance_party_id := p_party_account_rec.instance_party_id ;
6827: END IF;
6828: -- End addition
6829: -- Call table handlers to insert into csi_i_parties_h table
6830: CSI_IP_ACCOUNTS_H_PKG.Insert_Row
6831: (
6832: px_IP_ACCOUNT_HISTORY_ID => l_ip_account_history_id ,
6833: p_IP_ACCOUNT_ID => p_party_account_rec.IP_ACCOUNT_ID ,
6931: l_new_party_id := null;
6932: Begin
6933: select old_party_id,new_party_id
6934: into l_old_party_id,l_new_party_id
6935: from CSI_I_PARTIES_H
6936: where instance_party_id = l_party_rec.instance_party_id
6937: and transaction_id = p_txn_rec.transaction_id;
6938: Exception
6939: when no_data_found then
7552: x_contact_details.email
7553:
7554: FROM
7555: HZ_PARTIES HP,
7556: CSI_I_PARTIES CIP,
7557: -- CSI_IPA_RELATION_TYPES CIR,
7558: HZ_CONTACT_POINTS HCP_WP,
7559: HZ_CONTACT_POINTS HCP_HP,
7560: HZ_CONTACT_POINTS HCP_PG,
7562: HZ_CONTACT_POINTS HCP_FX,
7563: HZ_CONTACT_POINTS HCP_MB,
7564: -- CSI_LOOKUPS CL,
7565: HZ_RELATIONSHIPS HR,
7566: CSI_I_PARTIES CIPO
7567: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id -- party_id for the contact
7568: AND CIP.PARTY_ID = HR.SUBJECT_ID
7569: AND CIP.CONTACT_IP_ID = CIPO.INSTANCE_PARTY_ID
7570: AND CIPO.PARTY_ID = HR.OBJECT_ID
7651: x_contact_details.postal_code,
7652: x_contact_details.state,
7653: x_contact_details.country,
7654: x_contact_details.email
7655: FROM CSI_I_PARTIES CIP,
7656: PO_VENDOR_CONTACTS PVC,
7657: PO_VENDOR_SITES_ALL PVS
7658: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7659: AND CIP.PARTY_ID = PVC.VENDOR_CONTACT_ID
7697: x_contact_details.postal_code,
7698: x_contact_details.state,
7699: x_contact_details.country,
7700: x_contact_details.email
7701: FROM CSI_I_PARTIES CIP,
7702: PER_ALL_PEOPLE_F PAP
7703: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7704: AND CIP.PARTY_ID = PAP.PERSON_ID
7705: AND PAP.EFFECTIVE_START_DATE <= SYSDATE
7742: x_contact_details.postal_code,
7743: x_contact_details.state,
7744: x_contact_details.country,
7745: x_contact_details.email
7746: FROM CSI_I_PARTIES CIP,
7747: JTF_RS_GROUPS_VL JG
7748: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7749: AND CIP.PARTY_ID = JG.GROUP_ID
7750: AND rownum < 2;
7785: x_contact_details.postal_code,
7786: x_contact_details.state,
7787: x_contact_details.country,
7788: x_contact_details.email
7789: FROM CSI_I_PARTIES CIP,
7790: JTF_RS_TEAMS_VL JT
7791: WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
7792: AND CIP.PARTY_ID = JT.TEAM_ID
7793: AND rownum < 2;
7935: ciph.OLD_PRIMARY_FLAG,
7936: ciph.NEW_PRIMARY_FLAG,
7937: cip.INSTANCE_ID,
7938: ciph.creation_date --Added for bug 2781480
7939: FROM csi_i_parties_h ciph,
7940: csi_i_parties cip
7941: WHERE ciph.transaction_id = i_transaction_id
7942: AND ciph.instance_party_id = cip.instance_party_id; -- Added by sk on 08-APR for fixing bug
7943: -- 2304649 .
7936: ciph.NEW_PRIMARY_FLAG,
7937: cip.INSTANCE_ID,
7938: ciph.creation_date --Added for bug 2781480
7939: FROM csi_i_parties_h ciph,
7940: csi_i_parties cip
7941: WHERE ciph.transaction_id = i_transaction_id
7942: AND ciph.instance_party_id = cip.instance_party_id; -- Added by sk on 08-APR for fixing bug
7943: -- 2304649 .
7944: -- Added for bug 2781480
8661: cah.NEW_INSTANCE_PARTY_ID,
8662: cah.creation_date
8663: FROM csi_ip_accounts_h cah,
8664: csi_ip_accounts ca,
8665: csi_i_parties cip
8666: WHERE cah.transaction_id = i_transaction_id
8667: AND cah.ip_account_id = ca.ip_account_id
8668: AND ca.instance_party_id = cip.instance_party_id; -- Added by sguthiva on 9-APR for bug 2304649
8669: