[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CREATION_DATE ,
NEW_RELATIONSHIP_TYPE_CODE ,
NEW_ACTIVE_START_DATE ,
NEW_ACTIVE_END_DATE ,
NEW_CONTEXT ,
NEW_ATTRIBUTE1 ,
NEW_ATTRIBUTE2 ,
NEW_ATTRIBUTE3 ,
NEW_ATTRIBUTE4 ,
NEW_ATTRIBUTE5 ,
NEW_ATTRIBUTE6 ,
NEW_ATTRIBUTE7 ,
NEW_ATTRIBUTE8 ,
NEW_ATTRIBUTE9 ,
NEW_ATTRIBUTE10 ,
NEW_ATTRIBUTE11 ,
NEW_ATTRIBUTE12 ,
NEW_ATTRIBUTE13 ,
NEW_ATTRIBUTE14 ,
NEW_ATTRIBUTE15 ,
NEW_BILL_TO_ADDRESS ,
NEW_SHIP_TO_ADDRESS
FROM CSI_IP_ACCOUNTS_H
WHERE ip_account_id = i_ip_acct_id
--and full_dump_flag = 'N'
--and creation_date < x_first_no_dump
order by creation_date;
SELECT
CREATION_DATE ,
NEW_RELATIONSHIP_TYPE_CODE ,
NEW_ACTIVE_START_DATE ,
NEW_ACTIVE_END_DATE ,
NEW_CONTEXT ,
NEW_ATTRIBUTE1 ,
NEW_ATTRIBUTE2 ,
NEW_ATTRIBUTE3 ,
NEW_ATTRIBUTE4 ,
NEW_ATTRIBUTE5 ,
NEW_ATTRIBUTE6 ,
NEW_ATTRIBUTE7 ,
NEW_ATTRIBUTE8 ,
NEW_ATTRIBUTE9 ,
NEW_ATTRIBUTE10 ,
NEW_ATTRIBUTE11 ,
NEW_ATTRIBUTE12 ,
NEW_ATTRIBUTE13 ,
NEW_ATTRIBUTE14 ,
NEW_ATTRIBUTE15 ,
NEW_BILL_TO_ADDRESS ,
NEW_SHIP_TO_ADDRESS
FROM CSI_IP_ACCOUNTS_H
WHERE ip_account_history_id = p_ip_acct_hist_id
and full_dump_flag = 'Y';
SELECT
MAX(ip_account_history_id)
FROM CSI_IP_ACCOUNTS_H
WHERE creation_date <= p_time
and ip_account_id = p_ip_acct_id
and full_dump_flag = 'Y' ;
SELECT
OLD_PARTY_ACCOUNT_ID ,
NEW_PARTY_ACCOUNT_ID ,
OLD_RELATIONSHIP_TYPE_CODE ,
NEW_RELATIONSHIP_TYPE_CODE ,
OLD_ACTIVE_START_DATE ,
NEW_ACTIVE_START_DATE ,
OLD_ACTIVE_END_DATE ,
NEW_ACTIVE_END_DATE ,
OLD_CONTEXT ,
NEW_CONTEXT ,
OLD_ATTRIBUTE1 ,
NEW_ATTRIBUTE1 ,
OLD_ATTRIBUTE2 ,
NEW_ATTRIBUTE2 ,
OLD_ATTRIBUTE3 ,
NEW_ATTRIBUTE3 ,
OLD_ATTRIBUTE4 ,
NEW_ATTRIBUTE4 ,
OLD_ATTRIBUTE5 ,
NEW_ATTRIBUTE5 ,
OLD_ATTRIBUTE6 ,
NEW_ATTRIBUTE6 ,
OLD_ATTRIBUTE7 ,
NEW_ATTRIBUTE7 ,
OLD_ATTRIBUTE8 ,
NEW_ATTRIBUTE8 ,
OLD_ATTRIBUTE9 ,
NEW_ATTRIBUTE9 ,
OLD_ATTRIBUTE10 ,
NEW_ATTRIBUTE10 ,
OLD_ATTRIBUTE11 ,
NEW_ATTRIBUTE11 ,
OLD_ATTRIBUTE12 ,
NEW_ATTRIBUTE12 ,
OLD_ATTRIBUTE13 ,
NEW_ATTRIBUTE13 ,
OLD_ATTRIBUTE14 ,
NEW_ATTRIBUTE14 ,
OLD_ATTRIBUTE15 ,
NEW_ATTRIBUTE15 ,
OLD_BILL_TO_ADDRESS ,
NEW_BILL_TO_ADDRESS ,
OLD_SHIP_TO_ADDRESS ,
NEW_SHIP_TO_ADDRESS
FROM CSI_IP_ACCOUNTS_H
WHERE creation_date <= p_time
and creation_date >= p_nearest_full_dump
and ip_account_id = p_ip_account_id
ORDER BY creation_date;
l_party_account_tbl.DELETE(i);
x_party_account_tbl.DELETE;
SELECT account_number,
account_name
INTO p_account_header_tbl(tab_row).party_account_number,
p_account_header_tbl(tab_row).party_account_name
FROM hz_cust_accounts
WHERE cust_account_id = p_account_header_tbl(tab_row).party_account_id;
SELECT hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.state
,hl.postal_code
,hl.country
INTO p_account_header_tbl(tab_row).bill_to_address1
,p_account_header_tbl(tab_row).bill_to_address2
,p_account_header_tbl(tab_row).bill_to_address3
,p_account_header_tbl(tab_row).bill_to_address4
,p_account_header_tbl(tab_row).bill_to_city
,p_account_header_tbl(tab_row).bill_to_state
,p_account_header_tbl(tab_row).bill_to_postal_code
,p_account_header_tbl(tab_row).bill_to_country
FROM hz_cust_site_uses_all hcs
,hz_cust_acct_sites_all hca
,hz_party_sites hps
,hz_locations hl
WHERE hcs.cust_acct_site_id=hca.cust_acct_site_id
AND hca.party_site_id=hps.party_site_id
AND hps.location_id=hl.location_id
AND hcs.site_use_id=p_account_header_tbl(tab_row).bill_to_address
AND hcs.site_use_code = 'BILL_TO';
SELECT hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.state
,hl.postal_code
,hl.country
INTO p_account_header_tbl(tab_row).ship_to_address1
,p_account_header_tbl(tab_row).ship_to_address2
,p_account_header_tbl(tab_row).ship_to_address3
,p_account_header_tbl(tab_row).ship_to_address4
,p_account_header_tbl(tab_row).ship_to_city
,p_account_header_tbl(tab_row).ship_to_state
,p_account_header_tbl(tab_row).ship_to_postal_code
,p_account_header_tbl(tab_row).ship_to_country
FROM hz_cust_site_uses_all hcs
,hz_cust_acct_sites_all hca
,hz_party_sites hps
,hz_locations hl
WHERE hcs.cust_acct_site_id=hca.cust_acct_site_id
AND hca.party_site_id=hps.party_site_id
AND hps.location_id=hl.location_id
AND hcs.site_use_id=p_account_header_tbl(tab_row).ship_to_address
AND hcs.site_use_code = 'SHIP_TO';
SELECT location
INTO p_account_header_tbl(tab_row).bill_to_location
FROM hz_cust_site_uses_all
WHERE site_use_id = p_account_header_tbl(tab_row).bill_to_address
AND site_use_code = 'BILL_TO';
SELECT location
INTO p_account_header_tbl(tab_row).ship_to_location
FROM hz_cust_site_uses_all
WHERE site_use_id = p_account_header_tbl(tab_row).ship_to_address
AND site_use_code = 'SHIP_TO';
SELECT
CREATION_DATE ,
NEW_PARTY_SOURCE_TABLE ,
NEW_PARTY_ID ,
NEW_RELATIONSHIP_TYPE_CODE ,
NEW_CONTACT_FLAG ,
NEW_CONTACT_IP_ID ,
NEW_ACTIVE_START_DATE ,
NEW_ACTIVE_END_DATE ,
NEW_CONTEXT ,
NEW_ATTRIBUTE1 ,
NEW_ATTRIBUTE2 ,
NEW_ATTRIBUTE3 ,
NEW_ATTRIBUTE4 ,
NEW_ATTRIBUTE5 ,
NEW_ATTRIBUTE6 ,
NEW_ATTRIBUTE7 ,
NEW_ATTRIBUTE8 ,
NEW_ATTRIBUTE9 ,
NEW_ATTRIBUTE10 ,
NEW_ATTRIBUTE11 ,
NEW_ATTRIBUTE12 ,
NEW_ATTRIBUTE13 ,
NEW_ATTRIBUTE14 ,
NEW_ATTRIBUTE15 ,
NEW_PRIMARY_FLAG ,
NEW_PREFERRED_FLAG
FROM CSI_I_PARTIES_H
WHERE instance_party_id = p_inst_pty_id
-- and creation_date < x_first_no_dump
-- and full_dump_flag = 'N'
order by creation_date;
SELECT
CREATION_DATE ,
NEW_PARTY_SOURCE_TABLE ,
NEW_PARTY_ID ,
NEW_RELATIONSHIP_TYPE_CODE ,
NEW_CONTACT_FLAG ,
NEW_CONTACT_IP_ID ,
NEW_ACTIVE_START_DATE ,
NEW_ACTIVE_END_DATE ,
NEW_CONTEXT ,
NEW_ATTRIBUTE1 ,
NEW_ATTRIBUTE2 ,
NEW_ATTRIBUTE3 ,
NEW_ATTRIBUTE4 ,
NEW_ATTRIBUTE5 ,
NEW_ATTRIBUTE6 ,
NEW_ATTRIBUTE7 ,
NEW_ATTRIBUTE8 ,
NEW_ATTRIBUTE9 ,
NEW_ATTRIBUTE10 ,
NEW_ATTRIBUTE11 ,
NEW_ATTRIBUTE12 ,
NEW_ATTRIBUTE13 ,
NEW_ATTRIBUTE14 ,
NEW_ATTRIBUTE15 ,
NEW_PRIMARY_FLAG ,
NEW_PREFERRED_FLAG
FROM CSI_I_PARTIES_H
WHERE instance_party_history_id = p_inst_pty_hist_id
and full_dump_flag = 'Y' ;
SELECT
MAX(instance_party_history_id)
FROM CSI_I_PARTIES_H
WHERE creation_date <= p_time
and instance_party_id = p_inst_party_id
and full_dump_flag = 'Y' ;
SELECT
INSTANCE_PARTY_HISTORY_ID ,
OLD_PARTY_SOURCE_TABLE ,
NEW_PARTY_SOURCE_TABLE ,
OLD_PARTY_ID ,
NEW_PARTY_ID ,
OLD_RELATIONSHIP_TYPE_CODE ,
NEW_RELATIONSHIP_TYPE_CODE ,
OLD_CONTACT_FLAG ,
NEW_CONTACT_FLAG ,
OLD_CONTACT_IP_ID ,
NEW_CONTACT_IP_ID ,
OLD_ACTIVE_START_DATE ,
NEW_ACTIVE_START_DATE ,
OLD_ACTIVE_END_DATE ,
NEW_ACTIVE_END_DATE ,
OLD_CONTEXT ,
NEW_CONTEXT ,
OLD_ATTRIBUTE1 ,
NEW_ATTRIBUTE1 ,
OLD_ATTRIBUTE2 ,
NEW_ATTRIBUTE2 ,
OLD_ATTRIBUTE3 ,
NEW_ATTRIBUTE3 ,
OLD_ATTRIBUTE4 ,
NEW_ATTRIBUTE4 ,
OLD_ATTRIBUTE5 ,
NEW_ATTRIBUTE5 ,
OLD_ATTRIBUTE6 ,
NEW_ATTRIBUTE6 ,
OLD_ATTRIBUTE7 ,
NEW_ATTRIBUTE7 ,
OLD_ATTRIBUTE8 ,
NEW_ATTRIBUTE8 ,
OLD_ATTRIBUTE9 ,
NEW_ATTRIBUTE9 ,
OLD_ATTRIBUTE10 ,
NEW_ATTRIBUTE10 ,
OLD_ATTRIBUTE11 ,
NEW_ATTRIBUTE11 ,
OLD_ATTRIBUTE12 ,
NEW_ATTRIBUTE12 ,
OLD_ATTRIBUTE13 ,
NEW_ATTRIBUTE13 ,
OLD_ATTRIBUTE14 ,
NEW_ATTRIBUTE14 ,
OLD_ATTRIBUTE15 ,
NEW_ATTRIBUTE15 ,
OLD_PRIMARY_FLAG ,
NEW_PRIMARY_FLAG ,
OLD_PREFERRED_FLAG ,
NEW_PREFERRED_FLAG
FROM CSI_I_PARTIES_H
WHERE creation_date <= p_time
and creation_date >= p_nearest_full_dump
and instance_party_id = p_inst_party_id
and full_dump_flag = 'N'
ORDER BY creation_date;
l_party_tbl.DELETE(i);
x_party_tbl.DELETE;
SELECT instance_party_id,
object_version_number,
active_start_date
INTO p_party_rec.instance_party_id,
p_party_rec.object_version_number,
p_party_rec.active_start_date
FROM csi_i_parties
WHERE instance_id = p_party_rec.instance_id
AND party_source_table = p_party_rec.party_source_table
AND party_id = p_party_rec.party_id
AND relationship_type_code = p_party_rec.relationship_type_code
AND contact_flag = p_party_rec.contact_flag
AND nvl(contact_ip_id,fnd_api.g_miss_num) = nvl(p_party_rec.contact_ip_id,fnd_api.g_miss_num)
AND active_end_date < SYSDATE
AND ROWNUM=1;
update_inst_party_relationship
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_party_rec => p_party_rec
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
SELECT 'X'
INTO l_exists
FROM csi_i_parties
WHERE instance_id <> p_party_rec.INSTANCE_ID
AND instance_party_id = p_party_rec.CONTACT_IP_ID;
update csi_item_instances
set owner_party_source_table = p_party_rec.party_source_table,
owner_party_id = p_party_rec.party_id
where instance_id = p_party_rec.instance_id;
CSI_I_PARTIES_PKG.Insert_Row(
p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
p_INSTANCE_ID => p_party_rec.INSTANCE_ID ,
p_PARTY_SOURCE_TABLE => p_party_rec.PARTY_SOURCE_TABLE ,
p_PARTY_ID => p_party_rec.PARTY_ID ,
p_RELATIONSHIP_TYPE_CODE => p_party_rec.RELATIONSHIP_TYPE_CODE,
p_CONTACT_FLAG => p_party_rec.CONTACT_FLAG ,
p_CONTACT_IP_ID => p_party_rec.CONTACT_IP_ID ,
p_ACTIVE_START_DATE => p_party_rec.ACTIVE_START_DATE ,
p_ACTIVE_END_DATE => p_party_rec.ACTIVE_END_DATE ,
p_CONTEXT => p_party_rec.CONTEXT ,
p_ATTRIBUTE1 => p_party_rec.ATTRIBUTE1 ,
p_ATTRIBUTE2 => p_party_rec.ATTRIBUTE2 ,
p_ATTRIBUTE3 => p_party_rec.ATTRIBUTE3 ,
p_ATTRIBUTE4 => p_party_rec.ATTRIBUTE4 ,
p_ATTRIBUTE5 => p_party_rec.ATTRIBUTE5 ,
p_ATTRIBUTE6 => p_party_rec.ATTRIBUTE6 ,
p_ATTRIBUTE7 => p_party_rec.ATTRIBUTE7 ,
p_ATTRIBUTE8 => p_party_rec.ATTRIBUTE8 ,
p_ATTRIBUTE9 => p_party_rec.ATTRIBUTE9 ,
p_ATTRIBUTE10 => p_party_rec.ATTRIBUTE10 ,
p_ATTRIBUTE11 => p_party_rec.ATTRIBUTE11 ,
p_ATTRIBUTE12 => p_party_rec.ATTRIBUTE12 ,
p_ATTRIBUTE13 => p_party_rec.ATTRIBUTE13 ,
p_ATTRIBUTE14 => p_party_rec.ATTRIBUTE14 ,
p_ATTRIBUTE15 => p_party_rec.ATTRIBUTE15 ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_PRIMARY_FLAG => p_party_rec.PRIMARY_FLAG ,
p_PREFERRED_FLAG => p_party_rec.PREFERRED_FLAG );
CSI_I_PARTIES_H_PKG.Insert_Row
(
px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
p_OLD_PARTY_SOURCE_TABLE => NULL ,
p_NEW_PARTY_SOURCE_TABLE => p_party_rec.PARTY_SOURCE_TABLE,
p_OLD_PARTY_ID => NULL ,
p_NEW_PARTY_ID => p_party_rec.PARTY_ID ,
p_OLD_RELATIONSHIP_TYPE_CODE => NULL ,
p_NEW_RELATIONSHIP_TYPE_CODE => p_party_rec.RELATIONSHIP_TYPE_CODE,
p_OLD_CONTACT_FLAG => NULL ,
p_NEW_CONTACT_FLAG => p_party_rec.CONTACT_FLAG ,
p_OLD_CONTACT_IP_ID => NULL ,
p_NEW_CONTACT_IP_ID => p_party_rec.CONTACT_IP_ID ,
p_OLD_ACTIVE_START_DATE => NULL ,
p_NEW_ACTIVE_START_DATE => p_party_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => NULL ,
p_NEW_ACTIVE_END_DATE => p_party_rec.ACTIVE_END_DATE,
p_OLD_CONTEXT => NULL ,
p_NEW_CONTEXT => p_party_rec.context ,
p_OLD_ATTRIBUTE1 => NULL ,
p_NEW_ATTRIBUTE1 => p_party_rec.ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => NULL ,
p_NEW_ATTRIBUTE2 => p_party_rec.ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => NULL ,
p_NEW_ATTRIBUTE3 => p_party_rec.ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => NULL ,
p_NEW_ATTRIBUTE4 => p_party_rec.ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => NULL ,
p_NEW_ATTRIBUTE5 => p_party_rec.ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => NULL ,
p_NEW_ATTRIBUTE6 => p_party_rec.ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => NULL ,
p_NEW_ATTRIBUTE7 => p_party_rec.ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => NULL ,
p_NEW_ATTRIBUTE8 => p_party_rec.ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => NULL ,
p_NEW_ATTRIBUTE9 => p_party_rec.ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => NULL ,
p_NEW_ATTRIBUTE10 => p_party_rec.ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => NULL ,
p_NEW_ATTRIBUTE11 => p_party_rec.ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => NULL ,
p_NEW_ATTRIBUTE12 => p_party_rec.ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => NULL ,
p_NEW_ATTRIBUTE13 => p_party_rec.ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => NULL ,
p_NEW_ATTRIBUTE14 => p_party_rec.ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => NULL ,
p_NEW_ATTRIBUTE15 => p_party_rec.ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => 'N' ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => sysdate ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => sysdate ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_PRIMARY_FLAG => NULL ,
p_NEW_PRIMARY_FLAG => p_party_rec.PRIMARY_FLAG ,
p_OLD_PREFERRED_FLAG => NULL ,
p_NEW_PREFERRED_FLAG => p_party_rec.PREFERRED_FLAG );
/* Procedure name: Update_inst_party_relationship */
/* Description : Procedure used to update the existing */
/* instance -party relationships */
/*------------------------------------------------------------*/
PROCEDURE update_inst_party_relationship
( p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_party_rec IN OUT NOCOPY csi_datastructures_pub.party_rec
,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INST_PARTY_RELATIONSHIP';
SELECT
ip_account_id,
object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = p_inst_party_id
AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE)) ;
SELECT
instance_party_id ,
instance_id ,
party_source_table ,
party_id ,
relationship_type_code,
contact_flag ,
contact_ip_id ,
active_start_date ,
active_end_date ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
object_version_number,
primary_flag ,
preferred_flag ,
null parent_tbl_index ,
null call_contracts,
null interface_id,
null contact_parent_tbl_index,
null cascade_ownership_flag -- Added for bug 2972082
FROM CSI_I_PARTIES
WHERE INSTANCE_PARTY_ID = p_inst_party_id
FOR UPDATE OF OBJECT_VERSION_NUMBER ;
SELECT
instance_party_id ,
instance_id ,
party_source_table ,
party_id ,
relationship_type_code,
contact_flag ,
contact_ip_id ,
active_start_date ,
active_end_date ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
object_version_number,
primary_flag ,
preferred_flag
FROM CSI_I_PARTIES
WHERE CONTACT_IP_ID = p_cont_ip_id
AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
FOR UPDATE OF OBJECT_VERSION_NUMBER ;
SELECT
INSTANCE_ID,
INSTANCE_NUMBER,
EXTERNAL_REFERENCE,
INVENTORY_ITEM_ID,
LAST_VLD_ORGANIZATION_ID VLD_ORGANIZATION_ID,
INVENTORY_REVISION,
INV_MASTER_ORGANIZATION_ID,
SERIAL_NUMBER,
MFG_SERIAL_NUMBER_FLAG,
LOT_NUMBER,
QUANTITY,
UNIT_OF_MEASURE,
ACCOUNTING_CLASS_CODE,
INSTANCE_CONDITION_ID,
INSTANCE_STATUS_ID,
CUSTOMER_VIEW_FLAG,
MERCHANT_VIEW_FLAG,
SELLABLE_FLAG,
SYSTEM_ID,
INSTANCE_TYPE_CODE,
ACTIVE_START_DATE,
ACTIVE_END_DATE,
LOCATION_TYPE_CODE,
LOCATION_ID,
INV_ORGANIZATION_ID,
INV_SUBINVENTORY_NAME,
INV_LOCATOR_ID,
PA_PROJECT_ID,
PA_PROJECT_TASK_ID,
IN_TRANSIT_ORDER_LINE_ID,
WIP_JOB_ID,
PO_ORDER_LINE_ID,
LAST_OE_ORDER_LINE_ID,
LAST_OE_RMA_LINE_ID,
LAST_PO_PO_LINE_ID,
LAST_OE_PO_NUMBER,
LAST_WIP_JOB_ID,
LAST_PA_PROJECT_ID,
LAST_PA_TASK_ID,
LAST_OE_AGREEMENT_ID,
INSTALL_DATE,
MANUALLY_CREATED_FLAG,
RETURN_BY_DATE,
ACTUAL_RETURN_DATE,
CREATION_COMPLETE_FLAG,
COMPLETENESS_FLAG,
NULL VERSION_LABEL,
NULL VERSION_LABEL_DESCRIPTION,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
LAST_TXN_LINE_DETAIL_ID,
INSTALL_LOCATION_TYPE_CODE,
INSTALL_LOCATION_ID,
INSTANCE_USAGE_CODE,
NULL CHECK_FOR_INSTANCE_EXPIRY,
NULL PROCESSED_FLAG,
NULL CALL_CONTRACTS,
NULL INTERFACE_ID,
NULL GRP_CALL_CONTRACTS,
CONFIG_INST_HDR_ID,
CONFIG_INST_REV_NUM,
CONFIG_INST_ITEM_ID,
CONFIG_VALID_STATUS,
INSTANCE_DESCRIPTION,
NULL CALL_BATCH_VALIDATION,
NULL REQUEST_ID,
NULL PROGRAM_APPLICATION_ID,
NULL PROGRAM_ID,
NULL PROGRAM_UPDATE_DATE,
NULL cascade_ownership_flag, -- Added for bug 2972082
NULL NETWORK_ASSET_FLAG,
NULL MAINTAINABLE_FLAG,
NULL PN_LOCATION_ID,
NULL ASSET_CRITICALITY_CODE,
NULL CATEGORY_ID,
NULL EQUIPMENT_GEN_OBJECT_ID,
NULL INSTANTIATION_FLAG,
NULL LINEAR_LOCATION_ID,
NULL OPERATIONAL_LOG_FLAG,
NULL CHECKIN_STATUS,
NULL SUPPLIER_WARRANTY_EXP_DATE,
NULL ATTRIBUTE16,
NULL ATTRIBUTE17,
NULL ATTRIBUTE18,
NULL ATTRIBUTE19,
NULL ATTRIBUTE20,
NULL ATTRIBUTE21,
NULL ATTRIBUTE22,
NULL ATTRIBUTE23,
NULL ATTRIBUTE24,
NULL ATTRIBUTE25,
NULL ATTRIBUTE26,
NULL ATTRIBUTE27,
NULL ATTRIBUTE28,
NULL ATTRIBUTE29,
NULL ATTRIBUTE30,
NULL PURCHASE_UNIT_PRICE,
NULL PURCHASE_CURRENCY_CODE,
NULL PAYABLES_UNIT_PRICE,
NULL PAYABLES_CURRENCY_CODE,
NULL SALES_UNIT_PRICE,
NULL SALES_CURRENCY_CODE,
NULL OPERATIONAL_STATUS_CODE,
NULL DEPARTMENT_ID,
NULL WIP_ACCOUNTING_CLASS,
NULL AREA_ID,
NULL OWNER_PARTY_ID,
NULL SOURCE_CODE, -- Bug 6407307, added Code for Siebel Genesis Project
NULL REC_INDEX
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT instance_party_history_id
,instance_party_id
,transaction_id
,old_party_source_table
,new_party_source_table
,old_party_id
,new_party_id
,old_relationship_type_code
,new_relationship_type_code
,old_contact_flag
,new_contact_flag
,old_contact_ip_id
,new_contact_ip_id
,old_active_start_date
,new_active_start_date
,old_active_end_date
,new_active_end_date
,old_context
,new_context
,old_attribute1
,new_attribute1
,old_attribute2
,new_attribute2
,old_attribute3
,new_attribute3
,old_attribute4
,new_attribute4
,old_attribute5
,new_attribute5
,old_attribute6
,new_attribute6
,old_attribute7
,new_attribute7
,old_attribute8
,new_attribute8
,old_attribute9
,new_attribute9
,old_attribute10
,new_attribute10
,old_attribute11
,new_attribute11
,old_attribute12
,new_attribute12
,old_attribute13
,new_attribute13
,old_attribute14
,new_attribute14
,old_attribute15
,new_attribute15
,full_dump_flag
,object_version_number
,old_primary_flag
,new_primary_flag
,old_preferred_flag
,new_preferred_flag
FROM csi_i_parties_h
WHERE csi_i_parties_h.instance_party_history_id = p_party_hist_id
FOR UPDATE OF object_version_number ;
csi_gen_utility_pvt.put_line( 'update_inst_party_relationship ');
csi_gen_utility_pvt.put_line( 'update_inst_party_relationship:' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list );
SELECT 'T'
INTO l_inst_just_expired
FROM CSI_ITEM_INSTANCES_H
WHERE INSTANCE_ID = p_party_rec.INSTANCE_ID
AND TRANSACTION_ID = p_txn_rec.TRANSACTION_ID
AND OLD_ACTIVE_END_DATE IS NULL
AND NEW_ACTIVE_END_DATE < SYSDATE
AND ROWNUM = 1;
SELECT object_version_number
INTO p_party_rec.object_version_number
FROM csi_i_parties
WHERE instance_party_id=p_party_rec.instance_party_id;
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;
SELECT instance_party_id
INTO l_ins_pty_found
FROM csi_i_parties
WHERE instance_id = l_alt_pk_instance_id
AND party_source_table = l_alt_pk_pty_source_tab
AND party_id = l_alt_pk_party_id
AND relationship_type_code = l_alt_pk_rel_type_code
AND contact_flag = l_alt_pk_contact_flag
AND NVL(contact_ip_id,fnd_api.g_miss_num) = NVL(p_party_rec.contact_ip_id,fnd_api.g_miss_num)
AND ((active_end_date IS NULL) OR (active_end_date >= sysdate));
SELECT active_end_date
INTO l_pty_end_date
FROM csi_i_parties
WHERE instance_party_id=p_party_rec.instance_party_id
AND ((active_end_date IS NULL) OR (active_end_date > sysdate));
update csi_item_instances
set owner_party_source_table = l_alt_pk_pty_source_tab, --p_party_rec.party_source_table,--bug 2769176
owner_party_id = l_alt_pk_party_id, --p_party_rec.party_id, --bug 2769176
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where instance_id = l_curr_party_rec.instance_id;
CSI_I_PARTIES_PKG.Update_Row
(
p_INSTANCE_PARTY_ID => p_party_rec.instance_party_id,
p_INSTANCE_ID => p_party_rec.instance_id,
p_PARTY_SOURCE_TABLE => p_party_rec.PARTY_SOURCE_TABLE,
p_PARTY_ID => p_party_rec.PARTY_ID,
p_RELATIONSHIP_TYPE_CODE => p_party_rec.RELATIONSHIP_TYPE_CODE,
p_CONTACT_FLAG => p_party_rec.CONTACT_FLAG,
p_CONTACT_IP_ID => p_party_rec.CONTACT_IP_ID,
p_ACTIVE_START_DATE => p_party_rec.ACTIVE_START_DATE,
p_ACTIVE_END_DATE => p_party_rec.ACTIVE_END_DATE,
p_CONTEXT => p_party_rec.CONTEXT,
p_ATTRIBUTE1 => p_party_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_party_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_party_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_party_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_party_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_party_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_party_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_party_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_party_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_party_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_party_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_party_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_party_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_party_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_party_rec.ATTRIBUTE15,
p_CREATED_BY => FND_API.G_MISS_NUM, -- FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE, -- sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER,
p_PRIMARY_FLAG => p_party_rec.PRIMARY_FLAG,
p_PREFERRED_FLAG => p_party_rec.PREFERRED_FLAG);
select mod(l_object_version_number,l_full_dump_frequency)
into l_mod_value
from dual;
SELECT instance_party_history_id
INTO l_party_hist_id
FROM csi_i_parties_h h
WHERE h.transaction_id = p_txn_rec.transaction_id
AND h.instance_party_id = p_party_rec.instance_party_id;
CSI_I_PARTIES_H_PKG.update_Row
(
p_INSTANCE_PARTY_HISTORY_ID => l_party_hist_id ,
p_INSTANCE_PARTY_ID => fnd_api.g_miss_num ,
p_TRANSACTION_ID => fnd_api.g_miss_num ,
p_OLD_PARTY_SOURCE_TABLE => fnd_api.g_miss_char ,
p_NEW_PARTY_SOURCE_TABLE => l_temp_party_rec.PARTY_SOURCE_TABLE,
p_OLD_PARTY_ID => fnd_api.g_miss_num ,
p_NEW_PARTY_ID => l_temp_party_rec.PARTY_ID ,
p_OLD_RELATIONSHIP_TYPE_CODE => fnd_api.g_miss_char ,
p_NEW_RELATIONSHIP_TYPE_CODE => l_temp_party_rec.RELATIONSHIP_TYPE_CODE,
p_OLD_CONTACT_FLAG => fnd_api.g_miss_char ,
p_NEW_CONTACT_FLAG => l_temp_party_rec.CONTACT_FLAG ,
p_OLD_CONTACT_IP_ID => fnd_api.g_miss_num ,
p_NEW_CONTACT_IP_ID => l_temp_party_rec.CONTACT_IP_ID ,
p_OLD_ACTIVE_START_DATE => l_curr_party_rec.ACTIVE_START_DATE ,
p_NEW_ACTIVE_START_DATE => l_temp_party_rec.ACTIVE_START_DATE ,
p_OLD_ACTIVE_END_DATE => l_curr_party_rec.ACTIVE_END_DATE ,
p_NEW_ACTIVE_END_DATE => l_temp_party_rec.ACTIVE_END_DATE ,
p_OLD_CONTEXT => fnd_api.g_miss_char ,
p_NEW_CONTEXT => l_temp_party_rec.context ,
p_OLD_ATTRIBUTE1 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE1 => l_temp_party_rec.ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE2 => l_temp_party_rec.ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE3 => l_temp_party_rec.ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE4 => l_temp_party_rec.ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE5 => l_temp_party_rec.ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE6 => l_temp_party_rec.ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE7 => l_temp_party_rec.ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE8 => l_temp_party_rec.ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE9 => l_temp_party_rec.ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE10 => l_temp_party_rec.ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE11 => l_temp_party_rec.ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE12 => l_temp_party_rec.ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE13 => l_temp_party_rec.ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE14 => l_temp_party_rec.ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE15 => l_temp_party_rec.ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => fnd_api.g_miss_char ,
p_CREATED_BY => FND_API.G_MISS_NUM ,
p_CREATION_DATE => FND_API.G_MISS_DATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => fnd_api.g_miss_num ,
p_OLD_PRIMARY_FLAG => fnd_api.g_miss_char ,
p_NEW_PRIMARY_FLAG => l_temp_party_rec.PRIMARY_FLAG ,
p_OLD_PREFERRED_FLAG => fnd_api.g_miss_char ,
p_NEW_PREFERRED_FLAG => l_temp_party_rec.PREFERRED_FLAG );
CSI_I_PARTIES_H_PKG.update_Row
(
p_INSTANCE_PARTY_HISTORY_ID => l_party_hist_id ,
p_INSTANCE_PARTY_ID => FND_API.G_MISS_NUM ,
p_TRANSACTION_ID => FND_API.G_MISS_NUM ,
p_OLD_PARTY_SOURCE_TABLE => l_pty_hist_csr.OLD_PARTY_SOURCE_TABLE ,
p_NEW_PARTY_SOURCE_TABLE => l_pty_hist_csr.NEW_PARTY_SOURCE_TABLE ,
p_OLD_PARTY_ID => l_pty_hist_csr.OLD_PARTY_ID ,
p_NEW_PARTY_ID => l_pty_hist_csr.NEW_PARTY_ID ,
p_OLD_RELATIONSHIP_TYPE_CODE => l_pty_hist_csr.OLD_RELATIONSHIP_TYPE_CODE ,
p_NEW_RELATIONSHIP_TYPE_CODE => l_pty_hist_csr.NEW_RELATIONSHIP_TYPE_CODE ,
p_OLD_CONTACT_FLAG => l_pty_hist_csr.OLD_CONTACT_FLAG ,
p_NEW_CONTACT_FLAG => l_pty_hist_csr.NEW_CONTACT_FLAG ,
p_OLD_CONTACT_IP_ID => l_pty_hist_csr.OLD_CONTACT_IP_ID ,
p_NEW_CONTACT_IP_ID => l_pty_hist_csr.NEW_CONTACT_IP_ID ,
p_OLD_ACTIVE_START_DATE => l_pty_hist_csr.OLD_ACTIVE_START_DATE ,
p_NEW_ACTIVE_START_DATE => l_pty_hist_csr.NEW_ACTIVE_START_DATE ,
p_OLD_ACTIVE_END_DATE => l_pty_hist_csr.OLD_ACTIVE_END_DATE ,
p_NEW_ACTIVE_END_DATE => l_pty_hist_csr.NEW_ACTIVE_END_DATE ,
p_OLD_CONTEXT => l_pty_hist_csr.OLD_CONTEXT ,
p_NEW_CONTEXT => l_pty_hist_csr.NEW_CONTEXT ,
p_OLD_ATTRIBUTE1 => l_pty_hist_csr.OLD_ATTRIBUTE1 ,
p_NEW_ATTRIBUTE1 => l_pty_hist_csr.NEW_ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => l_pty_hist_csr.OLD_ATTRIBUTE2 ,
p_NEW_ATTRIBUTE2 => l_pty_hist_csr.NEW_ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => l_pty_hist_csr.OLD_ATTRIBUTE3 ,
p_NEW_ATTRIBUTE3 => l_pty_hist_csr.NEW_ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => l_pty_hist_csr.OLD_ATTRIBUTE4 ,
p_NEW_ATTRIBUTE4 => l_pty_hist_csr.NEW_ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => l_pty_hist_csr.OLD_ATTRIBUTE5 ,
p_NEW_ATTRIBUTE5 => l_pty_hist_csr.NEW_ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => l_pty_hist_csr.OLD_ATTRIBUTE6 ,
p_NEW_ATTRIBUTE6 => l_pty_hist_csr.NEW_ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => l_pty_hist_csr.OLD_ATTRIBUTE7 ,
p_NEW_ATTRIBUTE7 => l_pty_hist_csr.NEW_ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => l_pty_hist_csr.OLD_ATTRIBUTE8 ,
p_NEW_ATTRIBUTE8 => l_pty_hist_csr.NEW_ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => l_pty_hist_csr.OLD_ATTRIBUTE9 ,
p_NEW_ATTRIBUTE9 => l_pty_hist_csr.NEW_ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => l_pty_hist_csr.OLD_ATTRIBUTE10 ,
p_NEW_ATTRIBUTE10 => l_pty_hist_csr.NEW_ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => l_pty_hist_csr.OLD_ATTRIBUTE11 ,
p_NEW_ATTRIBUTE11 => l_pty_hist_csr.NEW_ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => l_pty_hist_csr.OLD_ATTRIBUTE12 ,
p_NEW_ATTRIBUTE12 => l_pty_hist_csr.NEW_ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => l_pty_hist_csr.OLD_ATTRIBUTE13 ,
p_NEW_ATTRIBUTE13 => l_pty_hist_csr.NEW_ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => l_pty_hist_csr.OLD_ATTRIBUTE14 ,
p_NEW_ATTRIBUTE14 => l_pty_hist_csr.NEW_ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => l_pty_hist_csr.OLD_ATTRIBUTE15 ,
p_NEW_ATTRIBUTE15 => l_pty_hist_csr.NEW_ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => FND_API.G_MISS_CHAR ,
p_CREATED_BY => FND_API.G_MISS_NUM ,
p_CREATION_DATE => FND_API.G_MISS_DATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM ,
p_OLD_PRIMARY_FLAG => l_pty_hist_csr.OLD_CONTACT_FLAG ,
p_NEW_PRIMARY_FLAG => l_pty_hist_csr.NEW_PRIMARY_FLAG ,
p_OLD_PREFERRED_FLAG => l_pty_hist_csr.OLD_CONTACT_FLAG ,
p_NEW_PREFERRED_FLAG => l_pty_hist_csr.NEW_PREFERRED_FLAG );
CSI_I_PARTIES_H_PKG.Insert_Row
(
px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
p_OLD_PARTY_SOURCE_TABLE => l_curr_party_rec.PARTY_SOURCE_TABLE,
p_NEW_PARTY_SOURCE_TABLE => l_temp_party_rec.PARTY_SOURCE_TABLE,
p_OLD_PARTY_ID => l_curr_party_rec.PARTY_ID ,
p_NEW_PARTY_ID => l_temp_party_rec.PARTY_ID ,
p_OLD_RELATIONSHIP_TYPE_CODE => l_curr_party_rec.RELATIONSHIP_TYPE_CODE,
p_NEW_RELATIONSHIP_TYPE_CODE => l_temp_party_rec.RELATIONSHIP_TYPE_CODE,
p_OLD_CONTACT_FLAG => l_curr_party_rec.CONTACT_FLAG ,
p_NEW_CONTACT_FLAG => l_temp_party_rec.CONTACT_FLAG ,
p_OLD_CONTACT_IP_ID => l_curr_party_rec.CONTACT_IP_ID ,
p_NEW_CONTACT_IP_ID => l_temp_party_rec.CONTACT_IP_ID ,
p_OLD_ACTIVE_START_DATE => l_curr_party_rec.ACTIVE_START_DATE,
p_NEW_ACTIVE_START_DATE => l_temp_party_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => l_curr_party_rec.ACTIVE_END_DATE ,
p_NEW_ACTIVE_END_DATE => l_temp_party_rec.ACTIVE_END_DATE ,
p_OLD_CONTEXT => l_curr_party_rec.context ,
p_NEW_CONTEXT => l_temp_party_rec.context ,
p_OLD_ATTRIBUTE1 => l_curr_party_rec.ATTRIBUTE1 ,
p_NEW_ATTRIBUTE1 => l_temp_party_rec.ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => l_curr_party_rec.ATTRIBUTE2 ,
p_NEW_ATTRIBUTE2 => l_temp_party_rec.ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => l_curr_party_rec.ATTRIBUTE3 ,
p_NEW_ATTRIBUTE3 => l_temp_party_rec.ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => l_curr_party_rec.ATTRIBUTE4 ,
p_NEW_ATTRIBUTE4 => l_temp_party_rec.ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => l_curr_party_rec.ATTRIBUTE5 ,
p_NEW_ATTRIBUTE5 => l_temp_party_rec.ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => l_curr_party_rec.ATTRIBUTE6 ,
p_NEW_ATTRIBUTE6 => l_temp_party_rec.ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => l_curr_party_rec.ATTRIBUTE7 ,
p_NEW_ATTRIBUTE7 => l_temp_party_rec.ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => l_curr_party_rec.ATTRIBUTE8 ,
p_NEW_ATTRIBUTE8 => l_temp_party_rec.ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => l_curr_party_rec.ATTRIBUTE9 ,
p_NEW_ATTRIBUTE9 => l_temp_party_rec.ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => l_curr_party_rec.ATTRIBUTE10 ,
p_NEW_ATTRIBUTE10 => l_temp_party_rec.ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => l_curr_party_rec.ATTRIBUTE11 ,
p_NEW_ATTRIBUTE11 => l_temp_party_rec.ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => l_curr_party_rec.ATTRIBUTE12 ,
p_NEW_ATTRIBUTE12 => l_temp_party_rec.ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => l_curr_party_rec.ATTRIBUTE13 ,
p_NEW_ATTRIBUTE13 => l_temp_party_rec.ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => l_curr_party_rec.ATTRIBUTE14 ,
p_NEW_ATTRIBUTE14 => l_temp_party_rec.ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => l_curr_party_rec.ATTRIBUTE15 ,
p_NEW_ATTRIBUTE15 => l_temp_party_rec.ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => 'Y' ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_PRIMARY_FLAG => l_curr_party_rec.PRIMARY_FLAG ,
p_NEW_PRIMARY_FLAG => l_temp_party_rec.PRIMARY_FLAG ,
p_OLD_PREFERRED_FLAG => l_curr_party_rec.PREFERRED_FLAG ,
p_NEW_PREFERRED_FLAG => l_temp_party_rec.PREFERRED_FLAG );
CSI_I_PARTIES_H_PKG.Insert_Row
(
px_INSTANCE_PARTY_HISTORY_ID => l_inst_party_his_id ,
p_INSTANCE_PARTY_ID => p_party_rec.INSTANCE_PARTY_ID ,
p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
p_OLD_PARTY_SOURCE_TABLE => l_party_hist_rec.OLD_PARTY_SOURCE_TABLE ,
p_NEW_PARTY_SOURCE_TABLE => l_party_hist_rec.NEW_PARTY_SOURCE_TABLE ,
p_OLD_PARTY_ID => l_party_hist_rec.OLD_PARTY_ID ,
p_NEW_PARTY_ID => l_party_hist_rec.NEW_PARTY_ID ,
p_OLD_RELATIONSHIP_TYPE_CODE => l_party_hist_rec.OLD_RELATIONSHIP_TYPE_CODE ,
p_NEW_RELATIONSHIP_TYPE_CODE => l_party_hist_rec.NEW_RELATIONSHIP_TYPE_CODE ,
p_OLD_CONTACT_FLAG => l_party_hist_rec.OLD_CONTACT_FLAG ,
p_NEW_CONTACT_FLAG => l_party_hist_rec.NEW_CONTACT_FLAG ,
p_OLD_CONTACT_IP_ID => l_party_hist_rec.OLD_CONTACT_IP_ID ,
p_NEW_CONTACT_IP_ID => l_party_hist_rec.NEW_CONTACT_IP_ID ,
p_OLD_ACTIVE_START_DATE => l_party_hist_rec.OLD_ACTIVE_START_DATE ,
p_NEW_ACTIVE_START_DATE => l_party_hist_rec.NEW_ACTIVE_START_DATE ,
p_OLD_ACTIVE_END_DATE => l_party_hist_rec.OLD_ACTIVE_END_DATE ,
p_NEW_ACTIVE_END_DATE => l_party_hist_rec.NEW_ACTIVE_END_DATE ,
p_OLD_CONTEXT => l_party_hist_rec.OLD_CONTEXT ,
p_NEW_CONTEXT => l_party_hist_rec.NEW_CONTEXT ,
p_OLD_ATTRIBUTE1 => l_party_hist_rec.OLD_ATTRIBUTE1 ,
p_NEW_ATTRIBUTE1 => l_party_hist_rec.NEW_ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => l_party_hist_rec.OLD_ATTRIBUTE2 ,
p_NEW_ATTRIBUTE2 => l_party_hist_rec.NEW_ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => l_party_hist_rec.OLD_ATTRIBUTE3 ,
p_NEW_ATTRIBUTE3 => l_party_hist_rec.NEW_ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => l_party_hist_rec.OLD_ATTRIBUTE4 ,
p_NEW_ATTRIBUTE4 => l_party_hist_rec.NEW_ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => l_party_hist_rec.OLD_ATTRIBUTE5 ,
p_NEW_ATTRIBUTE5 => l_party_hist_rec.NEW_ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => l_party_hist_rec.OLD_ATTRIBUTE6 ,
p_NEW_ATTRIBUTE6 => l_party_hist_rec.NEW_ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => l_party_hist_rec.OLD_ATTRIBUTE7 ,
p_NEW_ATTRIBUTE7 => l_party_hist_rec.NEW_ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => l_party_hist_rec.OLD_ATTRIBUTE8 ,
p_NEW_ATTRIBUTE8 => l_party_hist_rec.NEW_ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => l_party_hist_rec.OLD_ATTRIBUTE9 ,
p_NEW_ATTRIBUTE9 => l_party_hist_rec.NEW_ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => l_party_hist_rec.OLD_ATTRIBUTE10 ,
p_NEW_ATTRIBUTE10 => l_party_hist_rec.NEW_ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => l_party_hist_rec.OLD_ATTRIBUTE11 ,
p_NEW_ATTRIBUTE11 => l_party_hist_rec.NEW_ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => l_party_hist_rec.OLD_ATTRIBUTE12 ,
p_NEW_ATTRIBUTE12 => l_party_hist_rec.NEW_ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => l_party_hist_rec.OLD_ATTRIBUTE13 ,
p_NEW_ATTRIBUTE13 => l_party_hist_rec.NEW_ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => l_party_hist_rec.OLD_ATTRIBUTE14 ,
p_NEW_ATTRIBUTE14 => l_party_hist_rec.NEW_ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => l_party_hist_rec.OLD_ATTRIBUTE15 ,
p_NEW_ATTRIBUTE15 => l_party_hist_rec.NEW_ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => 'N',
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_PRIMARY_FLAG => l_party_hist_rec.OLD_PRIMARY_FLAG ,
p_NEW_PRIMARY_FLAG => l_party_hist_rec.NEW_PRIMARY_FLAG ,
p_OLD_PREFERRED_FLAG => l_party_hist_rec.OLD_PREFERRED_FLAG ,
p_NEW_PREFERRED_FLAG => l_party_hist_rec.NEW_PREFERRED_FLAG );
csi_item_instance_pvt.get_and_update_acct_class
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_id => l_curr_party_rec.instance_id
,p_txn_rec => p_txn_rec
,x_acct_class_code => l_acct_class_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END update_inst_party_relationship ;
SELECT
instance_party_id ,
instance_id ,
party_source_table ,
party_id ,
relationship_type_code,
contact_flag ,
contact_ip_id ,
active_start_date ,
active_end_date ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
object_version_number,
primary_flag ,
preferred_flag ,
null parent_tbl_index ,
null call_contracts,
null interface_id,
null contact_parent_tbl_index,
null cascade_ownership_flag -- Added for bug 2972082
FROM CSI_I_PARTIES
WHERE INSTANCE_PARTY_ID = p_inst_party_id
AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
FOR UPDATE OF OBJECT_VERSION_NUMBER;
update_inst_party_relationship
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_party_rec => l_party_rec
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
l_update_record BOOLEAN := FALSE;
SELECT *
FROM csi_item_instances
WHERE instance_id = p_ins_id;
l_update_record := FALSE;
SELECT ip_account_id,
active_start_date,
object_version_number
INTO p_party_account_rec.ip_account_id,
p_party_account_rec.active_start_date,
p_party_account_rec.object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = p_party_account_rec.instance_party_id
AND relationship_type_code = 'OWNER'
AND (active_end_date IS NULL OR active_end_date > SYSDATE)
AND ROWNUM = 1;
l_update_record := TRUE;
SELECT ip_account_id,
active_start_date,
object_version_number
INTO p_party_account_rec.ip_account_id,
p_party_account_rec.active_start_date,
p_party_account_rec.object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = p_party_account_rec.instance_party_id
AND relationship_type_code = 'OWNER'
AND ROWNUM = 1;
l_update_record := TRUE;
IF l_update_record
THEN
update_inst_party_account
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_party_account_rec => p_party_account_rec
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT ip_account_id ,
object_version_number
INTO p_party_account_rec.ip_account_id,
p_party_account_rec.object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = p_party_account_rec.instance_party_id
AND party_account_id = p_party_account_rec.party_account_id
AND relationship_type_code = p_party_account_rec.relationship_type_code
AND active_end_date < sysdate
AND ROWNUM = 1;
IF NOT(l_update_record)
THEN -- Added for bug 2110790
IF l_record_found THEN
-- Added by sk for fixing the bug 2232880
IF p_party_account_rec.active_end_date = fnd_api.g_miss_date
THEN
p_party_account_rec.active_end_date := NULL;
update_inst_party_account
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_party_account_rec => p_party_account_rec
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => p_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
update csi_item_instances
set owner_party_account_id = p_party_account_rec.party_account_id
where instance_id = l_party_rec.instance_id;
CSI_IP_ACCOUNTS_PKG.Insert_Row(
px_IP_ACCOUNT_ID => p_party_account_rec.ip_account_id,
p_INSTANCE_PARTY_ID => p_party_account_rec.INSTANCE_PARTY_ID,
p_PARTY_ACCOUNT_ID => p_party_account_rec.PARTY_ACCOUNT_ID,
p_RELATIONSHIP_TYPE_CODE => p_party_account_rec.RELATIONSHIP_TYPE_CODE,
p_ACTIVE_START_DATE => p_party_account_rec.ACTIVE_START_DATE,
p_ACTIVE_END_DATE => p_party_account_rec.ACTIVE_END_DATE,
p_CONTEXT => p_party_account_rec.CONTEXT,
p_ATTRIBUTE1 => p_party_account_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_party_account_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_party_account_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_party_account_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_party_account_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_party_account_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_party_account_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_party_account_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_party_account_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_party_account_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_party_account_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_party_account_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_party_account_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_party_account_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_party_account_rec.ATTRIBUTE15,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_BILL_TO_ADDRESS => p_party_account_rec.BILL_TO_ADDRESS,
p_SHIP_TO_ADDRESS => p_party_account_rec.SHIP_TO_ADDRESS,
p_REQUEST_ID => p_party_account_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => p_party_account_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => p_party_account_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => p_party_account_rec.PROGRAM_UPDATE_DATE
);
CSI_IP_ACCOUNTS_H_PKG.Insert_Row
(
px_IP_ACCOUNT_HISTORY_ID => l_ip_account_history_id ,
p_IP_ACCOUNT_ID => p_party_account_rec.ip_account_id,
p_TRANSACTION_ID => p_txn_rec.transaction_id ,
p_OLD_PARTY_ACCOUNT_ID => NULL ,
p_NEW_PARTY_ACCOUNT_ID => p_party_account_rec.PARTY_ACCOUNT_ID,
p_OLD_RELATIONSHIP_TYPE_CODE => NULL ,
p_NEW_RELATIONSHIP_TYPE_CODE => p_party_account_rec.RELATIONSHIP_TYPE_CODE,
p_OLD_ACTIVE_START_DATE => NULL ,
p_NEW_ACTIVE_START_DATE => p_party_account_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => NULL ,
p_NEW_ACTIVE_END_DATE => p_party_account_rec.ACTIVE_END_DATE,
p_OLD_CONTEXT => NULL ,
p_NEW_CONTEXT => p_party_account_rec.CONTEXT ,
p_OLD_ATTRIBUTE1 => NULL ,
p_NEW_ATTRIBUTE1 => p_party_account_rec.ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => NULL ,
p_NEW_ATTRIBUTE2 => p_party_account_rec.ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => NULL ,
p_NEW_ATTRIBUTE3 => p_party_account_rec.ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => NULL ,
p_NEW_ATTRIBUTE4 => p_party_account_rec.ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => NULL ,
p_NEW_ATTRIBUTE5 => p_party_account_rec.ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => NULL ,
p_NEW_ATTRIBUTE6 => p_party_account_rec.ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => NULL ,
p_NEW_ATTRIBUTE7 => p_party_account_rec.ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => NULL ,
p_NEW_ATTRIBUTE8 => p_party_account_rec.ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => NULL ,
p_NEW_ATTRIBUTE9 => p_party_account_rec.ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => NULL ,
p_NEW_ATTRIBUTE10 => p_party_account_rec.ATTRIBUTE10,
p_OLD_ATTRIBUTE11 => NULL ,
p_NEW_ATTRIBUTE11 => p_party_account_rec.ATTRIBUTE11,
p_OLD_ATTRIBUTE12 => NULL ,
p_NEW_ATTRIBUTE12 => p_party_account_rec.ATTRIBUTE12,
p_OLD_ATTRIBUTE13 => NULL ,
p_NEW_ATTRIBUTE13 => p_party_account_rec.ATTRIBUTE13,
p_OLD_ATTRIBUTE14 => NULL ,
p_NEW_ATTRIBUTE14 => p_party_account_rec.ATTRIBUTE14,
p_OLD_ATTRIBUTE15 => NULL ,
p_NEW_ATTRIBUTE15 => p_party_account_rec.ATTRIBUTE15,
p_FULL_DUMP_FLAG => 'N' ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_BILL_TO_ADDRESS => NULL ,
p_NEW_BILL_TO_ADDRESS => p_party_account_rec.BILL_TO_ADDRESS,
p_OLD_SHIP_TO_ADDRESS => NULL ,
p_NEW_SHIP_TO_ADDRESS => p_party_account_rec.SHIP_TO_ADDRESS,
p_OLD_INSTANCE_PARTY_ID => NULL ,
p_NEW_INSTANCE_PARTY_ID => p_party_account_rec.INSTANCE_PARTY_ID);
SELECT last_vld_organization_id
INTO l_last_vld_org
FROM csi_item_instances
WHERE instance_id = l_party_rec.instance_id;
/* Procedure name: Update_inst_party_account */
/* Description : Procedure used to update the existing */
/* instance-party account relationships */
/*-----------------------------------------------------------*/
PROCEDURE update_inst_party_account
( p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_party_account_rec IN csi_datastructures_pub.party_account_rec
,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,p_oks_txn_inst_tbl IN OUT NOCOPY oks_ibint_pub.txn_instance_tbl
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INST_PARTY_ACCOUNT';
SELECT *
FROM csi_item_instances
WHERE instance_id = p_ins_id;
SELECT
ip_account_id ,
FND_API.G_MISS_NUM parent_tbl_index,
instance_party_id ,
party_account_id ,
relationship_type_code ,
bill_to_address ,
ship_to_address ,
active_start_date ,
active_end_date ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
object_version_number
FROM CSI_IP_ACCOUNTS
WHERE IP_ACCOUNT_ID = p_ip_account_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
SELECT ip_account_history_id
,ip_account_id
,transaction_id
,old_party_account_id
,new_party_account_id
,old_relationship_type_code
,new_relationship_type_code
,old_active_start_date
,new_active_start_date
,old_active_end_date
,new_active_end_date
,old_context
,new_context
,old_attribute1
,new_attribute1
,old_attribute2
,new_attribute2
,old_attribute3
,new_attribute3
,old_attribute4
,new_attribute4
,old_attribute5
,new_attribute5
,old_attribute6
,new_attribute6
,old_attribute7
,new_attribute7
,old_attribute8
,new_attribute8
,old_attribute9
,new_attribute9
,old_attribute10
,new_attribute10
,old_attribute11
,new_attribute11
,old_attribute12
,new_attribute12
,old_attribute13
,new_attribute13
,old_attribute14
,new_attribute14
,old_attribute15
,new_attribute15
,object_version_number
,old_bill_to_address
,new_bill_to_address
,old_ship_to_address
,new_ship_to_address
,full_dump_flag
,old_instance_party_id
,new_instance_party_id
FROM csi_ip_accounts_h
WHERE csi_ip_accounts_h.ip_account_history_id = p_act_hist_id
FOR UPDATE OF object_version_number ;
l_found_for_update VARCHAR2(1):='N';
csi_gen_utility_pvt.put_line( 'update_inst_party_account');
csi_gen_utility_pvt.put_line( 'update_inst_party_account '||'-'||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list );
SELECT relationship_type_code
INTO l_rel_type_code
FROM csi_i_parties
WHERE instance_party_id=l_curr_party_acct_rec.instance_party_id;
l_found_for_update:='T';
SELECT 'x'
INTO l_dummy
FROM csi_ip_accounts
WHERE instance_party_id = l_alt_pk_inst_pty_id
AND party_account_id = l_alt_pk_pty_acct_id
AND relationship_type_code = l_alt_pk_rel_type_code
AND ((active_end_date IS NULL) OR (active_end_date >= sysdate))
AND ROWNUM=1;
SELECT active_end_date
INTO l_temp_acct_date
FROM csi_ip_accounts
WHERE ip_account_id=p_party_account_rec.ip_account_id
AND ((active_end_date IS NULL) OR (active_end_date > sysdate));
update csi_item_instances
set owner_party_account_id = p_party_account_rec.party_account_id
where instance_id = l_party_rec.instance_id;
l_found_for_update='T'
THEN
l_acct_end_date:=l_temp_acct_date;
CSI_IP_ACCOUNTS_PKG.Update_Row
(
p_IP_ACCOUNT_ID => p_party_account_rec.ip_account_id,
p_INSTANCE_PARTY_ID => p_party_account_rec.INSTANCE_PARTY_ID,
p_PARTY_ACCOUNT_ID => p_party_account_rec.PARTY_ACCOUNT_ID,
p_RELATIONSHIP_TYPE_CODE => p_party_account_rec.RELATIONSHIP_TYPE_CODE,
p_ACTIVE_START_DATE => p_party_account_rec.ACTIVE_START_DATE,
p_ACTIVE_END_DATE => l_acct_end_date, --p_party_account_rec.ACTIVE_END_DATE,
p_CONTEXT => p_party_account_rec.CONTEXT,
p_ATTRIBUTE1 => p_party_account_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_party_account_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_party_account_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_party_account_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_party_account_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_party_account_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_party_account_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_party_account_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_party_account_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_party_account_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_party_account_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_party_account_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_party_account_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_party_account_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_party_account_rec.ATTRIBUTE15,
p_CREATED_BY => FND_API.G_MISS_NUM, -- FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE, -- SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER ,
p_BILL_TO_ADDRESS => p_party_account_rec.BILL_TO_ADDRESS,
p_SHIP_TO_ADDRESS => p_party_account_rec.SHIP_TO_ADDRESS,
p_REQUEST_ID => p_party_account_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => p_party_account_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => p_party_account_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => p_party_account_rec.PROGRAM_UPDATE_DATE);
select mod(l_object_version_number,l_full_dump_frequency)
into l_mod_value
from dual;
SELECT ip_account_history_id
INTO l_ip_acct_hist_id
FROM csi_ip_accounts_h h
WHERE h.transaction_id = p_txn_rec.transaction_id
AND h.ip_account_id = p_party_account_rec.ip_account_id;
CSI_IP_ACCOUNTS_H_PKG.Update_Row
(
p_IP_ACCOUNT_HISTORY_ID => l_ip_acct_hist_id ,
p_IP_ACCOUNT_ID => fnd_api.g_miss_num ,
p_TRANSACTION_ID => fnd_api.g_miss_num ,
p_OLD_PARTY_ACCOUNT_ID => fnd_api.g_miss_num ,
p_NEW_PARTY_ACCOUNT_ID => l_temp_party_account_rec.PARTY_ACCOUNT_ID,
p_OLD_RELATIONSHIP_TYPE_CODE => fnd_api.g_miss_char ,
p_NEW_RELATIONSHIP_TYPE_CODE => l_temp_party_account_rec.RELATIONSHIP_TYPE_CODE,
p_OLD_ACTIVE_START_DATE => fnd_api.g_miss_date ,
p_NEW_ACTIVE_START_DATE => l_temp_party_account_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => fnd_api.g_miss_date ,
p_NEW_ACTIVE_END_DATE => l_temp_party_account_rec.ACTIVE_END_DATE ,
p_OLD_CONTEXT => fnd_api.g_miss_char ,
p_NEW_CONTEXT => l_temp_party_account_rec.CONTEXT ,
p_OLD_ATTRIBUTE1 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE1 => l_temp_party_account_rec.ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE2 => l_temp_party_account_rec.ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE3 => l_temp_party_account_rec.ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE4 => l_temp_party_account_rec.ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE5 => l_temp_party_account_rec.ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE6 => l_temp_party_account_rec.ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE7 => l_temp_party_account_rec.ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE8 => l_temp_party_account_rec.ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE9 => l_temp_party_account_rec.ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE10 => l_temp_party_account_rec.ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE11 => l_temp_party_account_rec.ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE12 => l_temp_party_account_rec.ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE13 => l_temp_party_account_rec.ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE14 => l_temp_party_account_rec.ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => fnd_api.g_miss_char ,
p_NEW_ATTRIBUTE15 => l_temp_party_account_rec.ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => fnd_api.g_miss_char ,
p_CREATED_BY => FND_API.G_MISS_NUM ,
p_CREATION_DATE => FND_API.G_MISS_DATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => fnd_api.g_miss_num ,
p_OLD_BILL_TO_ADDRESS => fnd_api.g_miss_num ,
p_NEW_BILL_TO_ADDRESS => l_temp_party_account_rec.BILL_TO_ADDRESS ,
p_OLD_SHIP_TO_ADDRESS => fnd_api.g_miss_num ,
p_NEW_SHIP_TO_ADDRESS => l_temp_party_account_rec.SHIP_TO_ADDRESS ,
p_OLD_INSTANCE_PARTY_ID => fnd_api.g_miss_num ,
p_NEW_INSTANCE_PARTY_ID => l_temp_party_account_rec.INSTANCE_PARTY_ID);
CSI_IP_ACCOUNTS_H_PKG.Update_Row
(
p_IP_ACCOUNT_HISTORY_ID => l_ip_acct_hist_id ,
p_IP_ACCOUNT_ID => fnd_api.g_miss_num ,
p_TRANSACTION_ID => fnd_api.g_miss_num ,
p_OLD_PARTY_ACCOUNT_ID => l_pty_acct_csr.old_party_account_id ,
p_NEW_PARTY_ACCOUNT_ID => l_pty_acct_csr.NEW_PARTY_ACCOUNT_ID,
p_OLD_RELATIONSHIP_TYPE_CODE => l_pty_acct_csr.old_relationship_type_code,
p_NEW_RELATIONSHIP_TYPE_CODE => l_pty_acct_csr.NEW_RELATIONSHIP_TYPE_CODE,
p_OLD_ACTIVE_START_DATE => l_pty_acct_csr.old_active_start_date ,
p_NEW_ACTIVE_START_DATE => l_pty_acct_csr.NEW_ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => l_pty_acct_csr.old_active_end_date ,
p_NEW_ACTIVE_END_DATE => l_pty_acct_csr.NEW_ACTIVE_END_DATE ,
p_OLD_CONTEXT => l_pty_acct_csr.old_context ,
p_NEW_CONTEXT => l_pty_acct_csr.NEW_CONTEXT ,
p_OLD_ATTRIBUTE1 => l_pty_acct_csr.old_attribute1 ,
p_NEW_ATTRIBUTE1 => l_pty_acct_csr.NEW_ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => l_pty_acct_csr.old_attribute2 ,
p_NEW_ATTRIBUTE2 => l_pty_acct_csr.NEW_ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => l_pty_acct_csr.old_attribute3 ,
p_NEW_ATTRIBUTE3 => l_pty_acct_csr.NEW_ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => l_pty_acct_csr.old_attribute4 ,
p_NEW_ATTRIBUTE4 => l_pty_acct_csr.NEW_ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => l_pty_acct_csr.old_attribute5 ,
p_NEW_ATTRIBUTE5 => l_pty_acct_csr.NEW_ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => l_pty_acct_csr.old_attribute6 ,
p_NEW_ATTRIBUTE6 => l_pty_acct_csr.NEW_ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => l_pty_acct_csr.old_attribute7 ,
p_NEW_ATTRIBUTE7 => l_pty_acct_csr.NEW_ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => l_pty_acct_csr.old_attribute8 ,
p_NEW_ATTRIBUTE8 => l_pty_acct_csr.NEW_ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => l_pty_acct_csr.old_attribute9 ,
p_NEW_ATTRIBUTE9 => l_pty_acct_csr.NEW_ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => l_pty_acct_csr.old_attribute10 ,
p_NEW_ATTRIBUTE10 => l_pty_acct_csr.NEW_ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => l_pty_acct_csr.old_attribute11 ,
p_NEW_ATTRIBUTE11 => l_pty_acct_csr.NEW_ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => l_pty_acct_csr.old_attribute12 ,
p_NEW_ATTRIBUTE12 => l_pty_acct_csr.NEW_ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => l_pty_acct_csr.old_attribute13 ,
p_NEW_ATTRIBUTE13 => l_pty_acct_csr.NEW_ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => l_pty_acct_csr.old_attribute14 ,
p_NEW_ATTRIBUTE14 => l_pty_acct_csr.NEW_ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => l_pty_acct_csr.old_attribute15 ,
p_NEW_ATTRIBUTE15 => l_pty_acct_csr.NEW_ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => fnd_api.g_miss_char ,
p_CREATED_BY => FND_API.G_MISS_NUM ,
p_CREATION_DATE => FND_API.G_MISS_DATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => fnd_api.g_miss_num ,
p_OLD_BILL_TO_ADDRESS => l_pty_acct_csr.old_bill_to_address ,
p_NEW_BILL_TO_ADDRESS => l_pty_acct_csr.NEW_BILL_TO_ADDRESS ,
p_OLD_SHIP_TO_ADDRESS => l_pty_acct_csr.old_ship_to_address ,
p_NEW_SHIP_TO_ADDRESS => l_pty_acct_csr.NEW_SHIP_TO_ADDRESS,
p_OLD_INSTANCE_PARTY_ID => l_pty_acct_csr.old_instance_party_id ,
p_NEW_INSTANCE_PARTY_ID => l_pty_acct_csr.new_instance_party_id );
CSI_IP_ACCOUNTS_H_PKG.Insert_Row
(
px_IP_ACCOUNT_HISTORY_ID => l_ip_account_history_id ,
p_IP_ACCOUNT_ID => p_party_account_rec.ip_account_id,
p_TRANSACTION_ID => p_txn_rec.transaction_id ,
p_OLD_PARTY_ACCOUNT_ID => l_curr_party_acct_rec.party_account_id,
p_NEW_PARTY_ACCOUNT_ID => l_temp_party_account_rec.PARTY_ACCOUNT_ID,
p_OLD_RELATIONSHIP_TYPE_CODE => l_curr_party_acct_rec.RELATIONSHIP_TYPE_CODE,
p_NEW_RELATIONSHIP_TYPE_CODE => l_temp_party_account_rec.RELATIONSHIP_TYPE_CODE,
p_OLD_ACTIVE_START_DATE => l_curr_party_acct_rec.ACTIVE_START_DATE,
p_NEW_ACTIVE_START_DATE => l_temp_party_account_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => l_curr_party_acct_rec.ACTIVE_END_DATE,
p_NEW_ACTIVE_END_DATE => l_temp_party_account_rec.ACTIVE_END_DATE,
p_OLD_CONTEXT => l_curr_party_acct_rec.CONTEXT,
p_NEW_CONTEXT => l_temp_party_account_rec.CONTEXT ,
p_OLD_ATTRIBUTE1 => l_curr_party_acct_rec.ATTRIBUTE1,
p_NEW_ATTRIBUTE1 => l_temp_party_account_rec.ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => l_curr_party_acct_rec.ATTRIBUTE2,
p_NEW_ATTRIBUTE2 => l_temp_party_account_rec.ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => l_curr_party_acct_rec.ATTRIBUTE3,
p_NEW_ATTRIBUTE3 => l_temp_party_account_rec.ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => l_curr_party_acct_rec.ATTRIBUTE4,
p_NEW_ATTRIBUTE4 => l_temp_party_account_rec.ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => l_curr_party_acct_rec.ATTRIBUTE5,
p_NEW_ATTRIBUTE5 => l_temp_party_account_rec.ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => l_curr_party_acct_rec.ATTRIBUTE6,
p_NEW_ATTRIBUTE6 => l_temp_party_account_rec.ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => l_curr_party_acct_rec.ATTRIBUTE7,
p_NEW_ATTRIBUTE7 => l_temp_party_account_rec.ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => l_curr_party_acct_rec.ATTRIBUTE8,
p_NEW_ATTRIBUTE8 => l_temp_party_account_rec.ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => l_curr_party_acct_rec.ATTRIBUTE9,
p_NEW_ATTRIBUTE9 => l_temp_party_account_rec.ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => l_curr_party_acct_rec.ATTRIBUTE10,
p_NEW_ATTRIBUTE10 => l_temp_party_account_rec.ATTRIBUTE10,
p_OLD_ATTRIBUTE11 => l_curr_party_acct_rec.ATTRIBUTE11,
p_NEW_ATTRIBUTE11 => l_temp_party_account_rec.ATTRIBUTE11,
p_OLD_ATTRIBUTE12 => l_curr_party_acct_rec.ATTRIBUTE12,
p_NEW_ATTRIBUTE12 => l_temp_party_account_rec.ATTRIBUTE12,
p_OLD_ATTRIBUTE13 => l_curr_party_acct_rec.ATTRIBUTE13,
p_NEW_ATTRIBUTE13 => l_temp_party_account_rec.ATTRIBUTE13,
p_OLD_ATTRIBUTE14 => l_curr_party_acct_rec.ATTRIBUTE14,
p_NEW_ATTRIBUTE14 => l_temp_party_account_rec.ATTRIBUTE14,
p_OLD_ATTRIBUTE15 => l_curr_party_acct_rec.ATTRIBUTE15,
p_NEW_ATTRIBUTE15 => l_temp_party_account_rec.ATTRIBUTE15,
p_FULL_DUMP_FLAG => 'Y' ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_BILL_TO_ADDRESS => l_curr_party_acct_rec.BILL_TO_ADDRESS,
p_NEW_BILL_TO_ADDRESS => l_temp_party_account_rec.BILL_TO_ADDRESS,
p_OLD_SHIP_TO_ADDRESS => l_curr_party_acct_rec.SHIP_TO_ADDRESS ,
p_NEW_SHIP_TO_ADDRESS => l_temp_party_account_rec.SHIP_TO_ADDRESS,
p_OLD_INSTANCE_PARTY_ID => l_curr_party_acct_rec.INSTANCE_PARTY_ID ,
p_NEW_INSTANCE_PARTY_ID => l_temp_party_account_rec.INSTANCE_PARTY_ID);
CSI_IP_ACCOUNTS_H_PKG.Insert_Row
(
px_IP_ACCOUNT_HISTORY_ID => l_ip_account_history_id ,
p_IP_ACCOUNT_ID => p_party_account_rec.IP_ACCOUNT_ID ,
p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID ,
p_OLD_PARTY_ACCOUNT_ID => l_account_hist_rec.OLD_PARTY_ACCOUNT_ID ,
p_NEW_PARTY_ACCOUNT_ID => l_account_hist_rec.NEW_PARTY_ACCOUNT_ID ,
p_OLD_RELATIONSHIP_TYPE_CODE => l_account_hist_rec.OLD_RELATIONSHIP_TYPE_CODE ,
p_NEW_RELATIONSHIP_TYPE_CODE => l_account_hist_rec.NEW_RELATIONSHIP_TYPE_CODE ,
p_OLD_ACTIVE_START_DATE => l_account_hist_rec.OLD_ACTIVE_START_DATE ,
p_NEW_ACTIVE_START_DATE => l_account_hist_rec.NEW_ACTIVE_START_DATE ,
p_OLD_ACTIVE_END_DATE => l_account_hist_rec.OLD_ACTIVE_END_DATE ,
p_NEW_ACTIVE_END_DATE => l_account_hist_rec.NEW_ACTIVE_END_DATE ,
p_OLD_CONTEXT => l_account_hist_rec.OLD_CONTEXT ,
p_NEW_CONTEXT => l_account_hist_rec.NEW_CONTEXT ,
p_OLD_ATTRIBUTE1 => l_account_hist_rec.OLD_ATTRIBUTE1 ,
p_NEW_ATTRIBUTE1 => l_account_hist_rec.NEW_ATTRIBUTE1 ,
p_OLD_ATTRIBUTE2 => l_account_hist_rec.OLD_ATTRIBUTE2 ,
p_NEW_ATTRIBUTE2 => l_account_hist_rec.NEW_ATTRIBUTE2 ,
p_OLD_ATTRIBUTE3 => l_account_hist_rec.OLD_ATTRIBUTE3 ,
p_NEW_ATTRIBUTE3 => l_account_hist_rec.NEW_ATTRIBUTE3 ,
p_OLD_ATTRIBUTE4 => l_account_hist_rec.OLD_ATTRIBUTE4 ,
p_NEW_ATTRIBUTE4 => l_account_hist_rec.NEW_ATTRIBUTE4 ,
p_OLD_ATTRIBUTE5 => l_account_hist_rec.OLD_ATTRIBUTE5 ,
p_NEW_ATTRIBUTE5 => l_account_hist_rec.NEW_ATTRIBUTE5 ,
p_OLD_ATTRIBUTE6 => l_account_hist_rec.OLD_ATTRIBUTE6 ,
p_NEW_ATTRIBUTE6 => l_account_hist_rec.NEW_ATTRIBUTE6 ,
p_OLD_ATTRIBUTE7 => l_account_hist_rec.OLD_ATTRIBUTE7 ,
p_NEW_ATTRIBUTE7 => l_account_hist_rec.NEW_ATTRIBUTE7 ,
p_OLD_ATTRIBUTE8 => l_account_hist_rec.OLD_ATTRIBUTE8 ,
p_NEW_ATTRIBUTE8 => l_account_hist_rec.NEW_ATTRIBUTE8 ,
p_OLD_ATTRIBUTE9 => l_account_hist_rec.OLD_ATTRIBUTE9 ,
p_NEW_ATTRIBUTE9 => l_account_hist_rec.NEW_ATTRIBUTE9 ,
p_OLD_ATTRIBUTE10 => l_account_hist_rec.OLD_ATTRIBUTE10 ,
p_NEW_ATTRIBUTE10 => l_account_hist_rec.NEW_ATTRIBUTE10 ,
p_OLD_ATTRIBUTE11 => l_account_hist_rec.OLD_ATTRIBUTE11 ,
p_NEW_ATTRIBUTE11 => l_account_hist_rec.NEW_ATTRIBUTE11 ,
p_OLD_ATTRIBUTE12 => l_account_hist_rec.OLD_ATTRIBUTE12 ,
p_NEW_ATTRIBUTE12 => l_account_hist_rec.NEW_ATTRIBUTE12 ,
p_OLD_ATTRIBUTE13 => l_account_hist_rec.OLD_ATTRIBUTE13 ,
p_NEW_ATTRIBUTE13 => l_account_hist_rec.NEW_ATTRIBUTE13 ,
p_OLD_ATTRIBUTE14 => l_account_hist_rec.OLD_ATTRIBUTE14 ,
p_NEW_ATTRIBUTE14 => l_account_hist_rec.NEW_ATTRIBUTE14 ,
p_OLD_ATTRIBUTE15 => l_account_hist_rec.OLD_ATTRIBUTE15 ,
p_NEW_ATTRIBUTE15 => l_account_hist_rec.NEW_ATTRIBUTE15 ,
p_FULL_DUMP_FLAG => 'N' ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_BILL_TO_ADDRESS => l_account_hist_rec.OLD_BILL_TO_ADDRESS ,
p_NEW_BILL_TO_ADDRESS => l_account_hist_rec.NEW_BILL_TO_ADDRESS ,
p_OLD_SHIP_TO_ADDRESS => l_account_hist_rec.OLD_SHIP_TO_ADDRESS ,
p_NEW_SHIP_TO_ADDRESS => l_account_hist_rec.NEW_SHIP_TO_ADDRESS ,
p_OLD_INSTANCE_PARTY_ID => l_account_hist_rec.OLD_INSTANCE_PARTY_ID ,
p_NEW_INSTANCE_PARTY_ID => l_account_hist_rec.NEW_INSTANCE_PARTY_ID );
SELECT last_vld_organization_id
INTO l_last_vld_org
FROM csi_item_instances
WHERE instance_id = l_party_rec.instance_id;
select old_party_id,new_party_id
into l_old_party_id,l_new_party_id
from CSI_I_PARTIES_H
where instance_party_id = l_party_rec.instance_party_id
and transaction_id = p_txn_rec.transaction_id;
UPDATE csi_item_instances
SET owner_party_account_id = NULL
WHERE instance_id = l_party_rec.instance_id;
END update_inst_party_account ;
SELECT
ip_account_id ,
FND_API.G_MISS_NUM parent_tbl_index,
instance_party_id ,
party_account_id ,
relationship_type_code ,
bill_to_address ,
ship_to_address ,
active_start_date ,
active_end_date ,
context ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
object_version_number
FROM CSI_IP_ACCOUNTS
WHERE IP_ACCOUNT_ID = p_ip_account_id
AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
FOR UPDATE OF OBJECT_VERSION_NUMBER;
update_inst_party_account
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_party_account_rec => l_party_account_rec
,p_txn_rec => p_txn_rec
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT hp.party_name party_name,
decode( hcp_wp.phone_country_code, null, null, hcp_wp.phone_country_code || '-'
)
|| decode( hcp_wp.phone_area_code, null, null, '(' || hcp_wp.phone_Area_code || ')'
)
|| hcp_wp.phone_number
|| decode( hcp_wp.phone_extension, null, null, ' x'|| hcp_wp.phone_extension)
work_phone_number,
decode( hcp_hp.phone_country_code, null, null, hcp_hp.phone_country_code || '-'
)
|| decode( hcp_hp.phone_area_code, null, null, '(' || hcp_hp.phone_Area_code || ')'
)
|| hcp_hp.phone_number
|| decode( hcp_hp.phone_extension, null, null, ' x'|| hcp_hp.phone_extension)
home_phone_number,
decode( hcp_mb.phone_country_code, null, null, hcp_mb.phone_country_code || '-'
)
|| decode( hcp_mb.phone_area_code, null, null, '(' || hcp_mb.phone_Area_code || ')'
)
|| hcp_mb.phone_number
|| decode( hcp_mb.phone_extension, null, null, ' x'|| hcp_mb.phone_extension)
mobile_number,
decode( hcp_pg.phone_country_code, null, null, hcp_pg.phone_country_code || '-'
)
|| decode( hcp_pg.phone_area_code, null, null, '(' || hcp_pg.phone_Area_code || ')'
)
|| hcp_pg.phone_number
|| decode( hcp_pg.phone_extension, null, null, ' x'|| hcp_pg.phone_extension)
pager_number,
decode( hcp_fx.phone_country_code, null, null, hcp_fx.phone_country_code || '-'
)
|| decode( hcp_fx.phone_area_code, null, null, '(' || hcp_fx.phone_Area_code || ')'
)
|| hcp_fx.phone_number
|| decode( hcp_fx.phone_extension, null, null, ' x'|| hcp_fx.phone_extension)
fax_number,
HP.ADDRESS1, HP.ADDRESS2, HP.ADDRESS3, HP.ADDRESS4, HP.CITY, HP.POSTAL_CODE,
HP.STATE, HP.COUNTRY,
hcp_em.email_address
INTO x_contact_details.party_name,
x_contact_details.officephone,
x_contact_details.homephone,
x_contact_details.mobile,
x_contact_details.page,
x_contact_details.fax,
x_contact_details.address1,
x_contact_details.address2,
x_contact_details.address3,
x_contact_details.address4,
x_contact_details.city,
x_contact_details.postal_code,
x_contact_details.state,
x_contact_details.country,
x_contact_details.email
FROM
HZ_PARTIES HP,
CSI_I_PARTIES CIP,
-- CSI_IPA_RELATION_TYPES CIR,
HZ_CONTACT_POINTS HCP_WP,
HZ_CONTACT_POINTS HCP_HP,
HZ_CONTACT_POINTS HCP_PG,
HZ_CONTACT_POINTS HCP_EM,
HZ_CONTACT_POINTS HCP_FX,
HZ_CONTACT_POINTS HCP_MB,
-- CSI_LOOKUPS CL,
HZ_RELATIONSHIPS HR,
CSI_I_PARTIES CIPO
WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id -- party_id for the contact
AND CIP.PARTY_ID = HR.SUBJECT_ID
AND CIP.CONTACT_IP_ID = CIPO.INSTANCE_PARTY_ID
AND CIPO.PARTY_ID = HR.OBJECT_ID
AND HR.SUBJECT_ID = HP.PARTY_ID
AND CIP.CONTACT_FLAG = 'Y'
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES'
-- AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE(+)
-- AND CL.LOOKUP_CODE(+) = CIP.PARTY_SOURCE_TABLE
AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HCP_WP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP_WP.OWNER_TABLE_ID(+) = HR.PARTY_ID
AND HCP_WP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP_WP.PHONE_LINE_TYPE(+) = 'GEN'
AND HCP_WP.CONTACT_POINT_PURPOSE(+) = 'BUSINESS'
AND HCP_HP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP_HP.OWNER_TABLE_ID(+) = HR.PARTY_ID
AND HCP_HP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP_HP.PHONE_LINE_TYPE(+) = 'GEN'
AND HCP_HP.CONTACT_POINT_PURPOSE(+) = 'PERSONAL'
AND HCP_MB.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP_MB.OWNER_TABLE_ID(+) = HR.PARTY_ID
AND HCP_MB.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP_MB.PHONE_LINE_TYPE(+) = 'MOBILE'
AND HCP_MB.CONTACT_POINT_PURPOSE(+) = 'BUSINESS'
AND HCP_PG.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP_PG.OWNER_TABLE_ID(+) = HR.PARTY_ID
AND HCP_PG.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP_PG.PHONE_LINE_TYPE(+) = 'PAGER'
AND HCP_PG.CONTACT_POINT_PURPOSE(+) = 'BUSINESS'
AND HCP_FX.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP_FX.OWNER_TABLE_ID(+) = HR.PARTY_ID
AND HCP_FX.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP_FX.PHONE_LINE_TYPE(+) = 'FAX'
AND HCP_FX.CONTACT_POINT_PURPOSE(+) = 'BUSINESS'
AND HCP_EM.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP_EM.OWNER_TABLE_ID(+) = HR.PARTY_ID
AND HCP_EM.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND HCP_EM.PRIMARY_FLAG(+) = 'Y'
AND ROWNUM < 2;
SELECT PVC.PREFIX || ' ' || PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' '|| PVC.LAST_NAME PARTY_NAME,
PVC.area_code||'-'||PVC.phone, --WORKPHONE,
NULL, --HOMEPHONE
NULL, --MOBILE
NULL, --PAGE
NULL, --FAX,
PVS.ADDRESS_LINE1 ADDRESS1, --address_line1
PVS.ADDRESS_LINE2 ADDRESS2, --address_line2
PVS.ADDRESS_LINE3 ADDRESS3, --address_line3
NULL, --address_line4
PVS.CITY, --city,
PVS.ZIP POSTAL_CODE, --zip
PVS.STATE STATE, --state
PVS.COUNTRY COUNTRY, --country
PVC.mail_stop --EMAIL ADDRESS
INTO x_contact_details.party_name,
x_contact_details.officephone,
x_contact_details.homephone,
x_contact_details.mobile,
x_contact_details.page,
x_contact_details.fax,
x_contact_details.address1,
x_contact_details.address2,
x_contact_details.address3,
x_contact_details.address4,
x_contact_details.city,
x_contact_details.postal_code,
x_contact_details.state,
x_contact_details.country,
x_contact_details.email
FROM CSI_I_PARTIES CIP,
PO_VENDOR_CONTACTS PVC,
PO_VENDOR_SITES_ALL PVS
WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
AND CIP.PARTY_ID = PVC.VENDOR_CONTACT_ID
AND PVS.VENDOR_SITE_ID = PVC.VENDOR_SITE_ID
AND rownum < 2;
SELECT distinct PAP.full_name, --PARTY NAME
PAP.work_telephone, --WORK PHONE
NULL , -- HOME_PHONE_NUMBER
NULL , --MOBILE
NULL , --PAGE
NULL , --FAX
NULL , --ADDRESS1
NULL , --ADDRESS2
NULL , --ADDRESS3
NULL , --ADDRESS4
NULL , --CITY
NULL , --POSTAL CODE
NULL , --STATE
NULL , --COUNTRY
PAP.email_address --EMAIL
INTO x_contact_details.party_name,
x_contact_details.officephone,
x_contact_details.homephone,
x_contact_details.mobile,
x_contact_details.page,
x_contact_details.fax,
x_contact_details.address1,
x_contact_details.address2,
x_contact_details.address3,
x_contact_details.address4,
x_contact_details.city,
x_contact_details.postal_code,
x_contact_details.state,
x_contact_details.country,
x_contact_details.email
FROM CSI_I_PARTIES CIP,
PER_ALL_PEOPLE_F PAP
WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
AND CIP.PARTY_ID = PAP.PERSON_ID
AND PAP.EFFECTIVE_START_DATE <= SYSDATE
AND PAP.EFFECTIVE_END_DATE >= SYSDATE
AND rownum < 2;
SELECT distinct JG.group_name,
NULL , --WORK PHONE
NULL , --HOME_PHONE_NUMBER
NULL , --MOBILE
NULL , --PAGE
NULL , --FAX
NULL , --ADDRESS1
NULL , --ADDRESS2
NULL , --ADDRESS3
NULL , --ADDRESS4
NULL , --CITY
NULL , --POSTAL CODE
NULL , --STATE
NULL , --COUNTRY
JG.email_address --EMAIL
INTO x_contact_details.party_name,
x_contact_details.officephone,
x_contact_details.homephone,
x_contact_details.mobile,
x_contact_details.page,
x_contact_details.fax,
x_contact_details.address1,
x_contact_details.address2,
x_contact_details.address3,
x_contact_details.address4,
x_contact_details.city,
x_contact_details.postal_code,
x_contact_details.state,
x_contact_details.country,
x_contact_details.email
FROM CSI_I_PARTIES CIP,
JTF_RS_GROUPS_VL JG
WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
AND CIP.PARTY_ID = JG.GROUP_ID
AND rownum < 2;
SELECT distinct JT.TEAM_NAME,
NULL , --WORK PHONE
NULL , --HOME_PHONE_NUMBER
NULL , --MOBILE
NULL , --PAGE
NULL , --FAX
NULL , --ADDRESS1
NULL , --ADDRESS2
NULL , --ADDRESS3
NULL , --ADDRESS4
NULL , --CITY
NULL , --POSTAL CODE
NULL , --STATE
NULL , --COUNTRY
JT.email_address --EMAIL
INTO x_contact_details.party_name,
x_contact_details.officephone,
x_contact_details.homephone,
x_contact_details.mobile,
x_contact_details.page,
x_contact_details.fax,
x_contact_details.address1,
x_contact_details.address2,
x_contact_details.address3,
x_contact_details.address4,
x_contact_details.city,
x_contact_details.postal_code,
x_contact_details.state,
x_contact_details.country,
x_contact_details.email
FROM CSI_I_PARTIES CIP,
JTF_RS_TEAMS_VL JT
WHERE CIP.INSTANCE_PARTY_ID = p_contact_party_id
AND CIP.PARTY_ID = JT.TEAM_ID
AND rownum < 2;
SELECT ciph.INSTANCE_PARTY_ID,
ciph.INSTANCE_PARTY_HISTORY_ID,
ciph.TRANSACTION_ID,
ciph.OLD_PARTY_SOURCE_TABLE,
ciph.NEW_PARTY_SOURCE_TABLE,
ciph.OLD_PARTY_ID,
ciph.NEW_PARTY_ID,
ciph.OLD_RELATIONSHIP_TYPE_CODE,
ciph.NEW_RELATIONSHIP_TYPE_CODE,
ciph.OLD_CONTACT_FLAG,
ciph.NEW_CONTACT_FLAG,
ciph.OLD_CONTACT_IP_ID,
ciph.NEW_CONTACT_IP_ID,
ciph.OLD_ACTIVE_START_DATE,
ciph.NEW_ACTIVE_START_DATE,
ciph.OLD_ACTIVE_END_DATE,
ciph.NEW_ACTIVE_END_DATE,
ciph.OLD_CONTEXT,
ciph.NEW_CONTEXT,
ciph.OLD_ATTRIBUTE1,
ciph.NEW_ATTRIBUTE1,
ciph.OLD_ATTRIBUTE2,
ciph.NEW_ATTRIBUTE2,
ciph.OLD_ATTRIBUTE3,
ciph.NEW_ATTRIBUTE3,
ciph.OLD_ATTRIBUTE4,
ciph.NEW_ATTRIBUTE4,
ciph.OLD_ATTRIBUTE5,
ciph.NEW_ATTRIBUTE5,
ciph.OLD_ATTRIBUTE6,
ciph.NEW_ATTRIBUTE6,
ciph.OLD_ATTRIBUTE7,
ciph.NEW_ATTRIBUTE7,
ciph.OLD_ATTRIBUTE8,
ciph.NEW_ATTRIBUTE8,
ciph.OLD_ATTRIBUTE9,
ciph.NEW_ATTRIBUTE9,
ciph.OLD_ATTRIBUTE10,
ciph.NEW_ATTRIBUTE10,
ciph.OLD_ATTRIBUTE11,
ciph.NEW_ATTRIBUTE11,
ciph.OLD_ATTRIBUTE12,
ciph.NEW_ATTRIBUTE12,
ciph.OLD_ATTRIBUTE13,
ciph.NEW_ATTRIBUTE13,
ciph.OLD_ATTRIBUTE14,
ciph.NEW_ATTRIBUTE14,
ciph.OLD_ATTRIBUTE15,
ciph.NEW_ATTRIBUTE15,
ciph.FULL_DUMP_FLAG,
ciph.OBJECT_VERSION_NUMBER,
ciph.OLD_PREFERRED_FLAG,
ciph.NEW_PREFERRED_FLAG,
ciph.OLD_PRIMARY_FLAG,
ciph.NEW_PRIMARY_FLAG,
cip.INSTANCE_ID,
ciph.creation_date --Added for bug 2781480
FROM csi_i_parties_h ciph,
csi_i_parties cip
WHERE ciph.transaction_id = i_transaction_id
AND ciph.instance_party_id = cip.instance_party_id; -- Added by sk on 08-APR for fixing bug
SELECT employee_number,
'EMPLYOEE'
INTO x_party_history_tbl(i).old_contact_party_number,
x_party_history_tbl(i).old_contact_party_type
FROM per_all_people_f
WHERE person_id = l_old_contact_party_id
AND rownum < 2; -- Bug # 2183107 srramakr
SELECT party_number,
'PARTY'
INTO x_party_history_tbl(i).old_contact_party_number,
x_party_history_tbl(i).old_contact_party_type
FROM hz_parties
WHERE party_id = l_old_contact_party_id;
SELECT segment1,
'VENDOR'
INTO x_party_history_tbl(i).old_contact_party_number,
x_party_history_tbl(i).old_contact_party_type
FROM po_vendors
WHERE vendor_id = l_old_contact_party_id;
SELECT group_number,
'GROUP'
INTO x_party_history_tbl(i).old_contact_party_number,
x_party_history_tbl(i).old_contact_party_type
FROM jtf_rs_groups_vl
WHERE group_id = l_old_contact_party_id;
SELECT team_number,
'TEAM'
INTO x_party_history_tbl(i).old_contact_party_number,
x_party_history_tbl(i).old_contact_party_type
FROM jtf_rs_teams_vl
WHERE team_id = l_old_contact_party_id;
SELECT employee_number,
'EMPLYOEE',
full_name
INTO x_party_history_tbl(i).old_party_number,
x_party_history_tbl(i).old_party_type,
x_party_history_tbl(i).old_party_name
FROM per_all_people_f
WHERE person_id = x_party_history_tbl(i).old_party_id
AND rownum < 2; -- Bug # 2183107 srramakr
SELECT party_number,
'PARTY',
party_name
INTO x_party_history_tbl(i).old_party_number,
x_party_history_tbl(i).old_party_type,
x_party_history_tbl(i).old_party_name
FROM hz_parties
WHERE party_id = x_party_history_tbl(i).old_party_id;
SELECT segment1,
'VENDOR',
vendor_name
INTO x_party_history_tbl(i).old_party_number,
x_party_history_tbl(i).old_party_type,
x_party_history_tbl(i).old_party_name
FROM po_vendors
WHERE vendor_id = x_party_history_tbl(i).old_party_id;
SELECT group_number,
'GROUP',
group_name
INTO x_party_history_tbl(i).old_party_number,
x_party_history_tbl(i).old_party_type,
x_party_history_tbl(i).old_party_name
FROM jtf_rs_groups_vl
WHERE group_id = x_party_history_tbl(i).old_party_id;
SELECT team_number,
'TEAM',
team_name
INTO x_party_history_tbl(i).old_party_number,
x_party_history_tbl(i).old_party_type,
x_party_history_tbl(i).old_party_name
FROM jtf_rs_teams_vl
WHERE team_id = x_party_history_tbl(i).old_party_id;
SELECT employee_number,
'EMPLYOEE'
INTO x_party_history_tbl(i).new_contact_party_number,
x_party_history_tbl(i).new_contact_party_type
FROM per_all_people_f
WHERE person_id = l_new_contact_party_id
AND rownum < 2; -- Bug # 2183107 srramakr
SELECT party_number,
'PARTY'
INTO x_party_history_tbl(i).new_contact_party_number,
x_party_history_tbl(i).new_contact_party_type
FROM hz_parties
WHERE party_id = l_new_contact_party_id;
SELECT segment1,
'VENDOR'
INTO x_party_history_tbl(i).new_contact_party_number,
x_party_history_tbl(i).new_contact_party_type
FROM po_vendors
WHERE vendor_id = l_new_contact_party_id;
SELECT group_number,
'GROUP'
INTO x_party_history_tbl(i).new_contact_party_number,
x_party_history_tbl(i).new_contact_party_type
FROM jtf_rs_groups_vl
WHERE group_id = l_new_contact_party_id;
SELECT team_number,
'TEAM'
INTO x_party_history_tbl(i).new_contact_party_number,
x_party_history_tbl(i).new_contact_party_type
FROM jtf_rs_teams_vl
WHERE team_id = x_party_history_tbl(i).new_party_id;
SELECT employee_number,
'EMPLYOEE',
full_name
INTO x_party_history_tbl(i).new_party_number,
x_party_history_tbl(i).new_party_type,
x_party_history_tbl(i).new_party_name
FROM per_all_people_f
WHERE person_id = x_party_history_tbl(i).new_party_id
AND rownum < 2; -- Bug # 2183107 srramakr
SELECT party_number,
'PARTY',
party_name
INTO x_party_history_tbl(i).new_party_number,
x_party_history_tbl(i).new_party_type,
x_party_history_tbl(i).new_party_name
FROM hz_parties
WHERE party_id = x_party_history_tbl(i).new_party_id;
SELECT segment1,
'VENDOR',
vendor_name
INTO x_party_history_tbl(i).new_party_number,
x_party_history_tbl(i).new_party_type,
x_party_history_tbl(i).new_party_name
FROM po_vendors
WHERE vendor_id = x_party_history_tbl(i).new_party_id;
SELECT group_number,
'GROUP',
group_name
INTO x_party_history_tbl(i).new_party_number,
x_party_history_tbl(i).new_party_type,
x_party_history_tbl(i).new_party_name
FROM jtf_rs_groups_vl
WHERE group_id = x_party_history_tbl(i).new_party_id;
SELECT team_number,
'TEAM',
team_name
INTO x_party_history_tbl(i).new_party_number,
x_party_history_tbl(i).new_party_type,
x_party_history_tbl(i).new_party_name
FROM jtf_rs_teams_vl
WHERE team_id = x_party_history_tbl(i).new_party_id;
SELECT cah.IP_ACCOUNT_HISTORY_ID ,
cah.IP_ACCOUNT_ID ,
cah.TRANSACTION_ID ,
cah.OLD_PARTY_ACCOUNT_ID ,
cah.NEW_PARTY_ACCOUNT_ID ,
cah.OLD_RELATIONSHIP_TYPE_CODE ,
cah.NEW_RELATIONSHIP_TYPE_CODE ,
cah.OLD_ACTIVE_START_DATE ,
cah.NEW_ACTIVE_START_DATE ,
cah.OLD_ACTIVE_END_DATE ,
cah.NEW_ACTIVE_END_DATE ,
cah.OLD_CONTEXT ,
cah.NEW_CONTEXT ,
cah.OLD_ATTRIBUTE1 ,
cah.NEW_ATTRIBUTE1 ,
cah.OLD_ATTRIBUTE2 ,
cah.NEW_ATTRIBUTE2 ,
cah.OLD_ATTRIBUTE3 ,
cah.NEW_ATTRIBUTE3 ,
cah.OLD_ATTRIBUTE4 ,
cah.NEW_ATTRIBUTE4 ,
cah.OLD_ATTRIBUTE5 ,
cah.NEW_ATTRIBUTE5 ,
cah.OLD_ATTRIBUTE6 ,
cah.NEW_ATTRIBUTE6 ,
cah.OLD_ATTRIBUTE7 ,
cah.NEW_ATTRIBUTE7 ,
cah.OLD_ATTRIBUTE8 ,
cah.NEW_ATTRIBUTE8 ,
cah.OLD_ATTRIBUTE9 ,
cah.NEW_ATTRIBUTE9 ,
cah.OLD_ATTRIBUTE10 ,
cah.NEW_ATTRIBUTE10 ,
cah.OLD_ATTRIBUTE11 ,
cah.NEW_ATTRIBUTE11 ,
cah.OLD_ATTRIBUTE12 ,
cah.NEW_ATTRIBUTE12 ,
cah.OLD_ATTRIBUTE13 ,
cah.NEW_ATTRIBUTE13 ,
cah.OLD_ATTRIBUTE14 ,
cah.NEW_ATTRIBUTE14 ,
cah.OLD_ATTRIBUTE15 ,
cah.NEW_ATTRIBUTE15 ,
cah.FULL_DUMP_FLAG ,
cah.OBJECT_VERSION_NUMBER ,
cah.OLD_BILL_TO_ADDRESS ,
cah.NEW_BILL_TO_ADDRESS ,
cah.OLD_SHIP_TO_ADDRESS ,
cah.NEW_SHIP_TO_ADDRESS ,
cip.INSTANCE_ID,
cah.OLD_INSTANCE_PARTY_ID ,
cah.NEW_INSTANCE_PARTY_ID,
cah.creation_date
FROM csi_ip_accounts_h cah,
csi_ip_accounts ca,
csi_i_parties cip
WHERE cah.transaction_id = i_transaction_id
AND cah.ip_account_id = ca.ip_account_id
AND ca.instance_party_id = cip.instance_party_id; -- Added by sguthiva on 9-APR for bug 2304649