[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select quote_header_id
from IBE_ACTIVE_QUOTES
where party_id = c_party_id
and cust_account_id = c_cust_account_id
and record_type = 'CART';
select quote_name
from aso_quote_headers_all
where party_id = c_party_id
and cust_account_id = c_cust_account_id
and quote_header_id = c_q_hdr_id;
select customer_id
from FND_USER
where user_id = FND_GLOBAL.USER_ID;
select quote_sharee_id,quote_header_id,party_id, cust_account_id
from ibe_sh_quote_access
where quote_sharee_number = c_retrieval_num ;
select cust_account_id, party_type
from aso_quote_headers_all a, hz_parties p
where a.party_id = p.party_id
and quote_header_id = c_quote_header_id;
IBE_SH_QUOTE_ACCESS_PKG.update_Row(
p_QUOTE_SHAREE_ID => l_recip_id
,p_party_id => p_party_id
,p_cust_account_id => p_cust_account_id);
P_source_last_update_date IN Date := FND_API.G_MISS_DATE ,
p_minisite_id IN NUMBER ,
p_URL IN VARCHAR2 ,
p_notes IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) is
L_control_rec aso_quote_pub.control_rec_type;
l_last_update_date DATE ;
Validate_share_Update(
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_quote_header_rec => P_Quote_header_rec
,p_quote_access_tbl => p_quote_access_tbl
,p_party_id => P_party_id
,p_cust_account_id => P_cust_account_id
,p_retrieval_number => p_retrieval_number
,p_operation_code => p_saveshare_control_rec.operation_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data) ;
x_last_update_date => l_last_update_date ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data);
P_source_last_update_date => P_source_last_update_date,
P_target_header_rec => P_Quote_header_rec,
P_control_rec => P_saveshare_control_rec.control_rec,
P_delete_source_cart => P_saveshare_control_rec.delete_source_cart,
P_combinesameitem => P_saveshare_control_rec.combinesameitem,
P_minisite_id => p_minisite_id,
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF (p_saveshare_control_rec.operation_code = OP_DELETE_CART) THEN
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('Save_share_v2:Op_code in p_saveshare_control_rec.operation_code: '||p_saveshare_control_rec.operation_code);
IBE_UTIL.DEBUG('Save_share_v2:Ready to call IBE_QUOTE_SAVE_PVT.Delete on: '||P_Quote_header_rec.quote_header_id);
IBE_UTIL.DEBUG('save_share_v2:Expunge flag passed to delete_cart is: '||p_saveshare_control_rec.delete_source_cart);
IBE_UTIL.DEBUG('save_share_v2:P_Quote_header_rec.last_update_date: '||P_Quote_header_rec.last_update_date);
IBE_QUOTE_SAVE_PVT.Delete(
p_api_version_number => p_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_quote_header_id => P_Quote_header_rec.quote_header_id
,p_expunge_flag => FND_API.G_FALSE
,p_minisite_id => p_minisite_id
,p_last_update_date => P_Quote_header_rec.last_update_date
,p_Quote_access_tbl => p_quote_access_tbl
,p_notes => p_notes
,p_initiator_party_id => p_party_id
,p_initiator_account_id => p_cust_account_id );
IBE_UTIL.DEBUG('Delete owner cart :Done');
END IF; --op_code = OP_DELETE_CART
-Handling all possible updates to recipient info from Share Cart Details page in subsequent
updates from "Add/Modify Recipients" - this includes adding recipients, changing recipient
info (access level), and removing recipients.
-"End Working" button from recipient pages
-"Remove" button on the "List of Saved Carts" page for recipients.
-Possibly other api's to do single removes, adds, or updates. */
Procedure save_recipients (
P_Quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_TBL_TYPE
:= IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl ,
P_Quote_header_id IN Number ,
P_Party_id IN Number := FND_API.G_MISS_NUM ,
P_Cust_account_id IN Number := FND_API.G_MISS_NUM ,
P_URL IN Varchar2 := FND_API.G_MISS_CHAR ,
P_minisite_id IN Number := FND_API.G_MISS_NUM ,
p_send_notif IN Varchar2 := FND_API.G_TRUE ,
p_notes IN Varchar2 := FND_API.G_MISS_CHAR ,
p_api_version IN Number := 1 ,
p_init_msg_list IN varchar2 := FND_API.G_TRUE ,
p_commit IN Varchar2 := FND_API.G_FALSE ,
x_return_status OUT NOCOPY Varchar2 ,
x_msg_count OUT NOCOPY Number ,
x_msg_data OUT NOCOPY Varchar2 ) is
cursor c_check_recip_row(c_quote_hdr_id number,
c_party_id number,
c_cust_account_id number) is
select quote_header_id, quote_sharee_id, quote_sharee_number
from ibe_sh_quote_access
where party_id = c_party_id
and cust_account_id = c_cust_account_id
and quote_header_id = c_quote_hdr_id
and nvl(end_date_active, sysdate+1) > sysdate;
select nvl(update_privilege_type_code, fnd_api.g_miss_char) access_level,
party_id,
cust_account_id,
contact_point_id,
quote_header_id,
quote_sharee_number,
quote_sharee_id,
fnd.customer_id shared_by_party_id
from ibe_sh_quote_access ibe, fnd_user fnd
where ibe.created_by = fnd.user_id
and (quote_sharee_id = c_recip_id
or (quote_header_id = c_quote_header_id
and party_id = c_party_id));
select party_id, cust_account_id
from ASO_QUOTE_HEADERS_ALL
where quote_header_id = c_quote_id;
select quote_sharee_id
from ibe_sh_quote_access
where party_id = c_party_id
and quote_header_id = c_quote_header_id
and created_by = c_created_by;
l_call_insert_handler VARCHAR2(1) := FND_API.G_FALSE;
l_call_insert_handler := FND_API.G_FALSE;
quote_hdr in quote_access then use this record, else insert a new record*/
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('Save_recipients: Opening c_get_created_recip');
IBE_UTIL.DEBUG('Record found for the recipient in quote_access table, need to update it');
IBE_SH_QUOTE_ACCESS_PKG.update_Row(
p_quote_sharee_id => l_quote_access_rec.quote_sharee_id
,p_quote_header_id => p_quote_header_id
,p_party_id => p_quote_access_tbl(counter).party_id
,p_cust_account_id => p_quote_access_tbl(counter).cust_account_id
,p_update_privilege_type_code => p_quote_access_tbl(counter).update_privilege_type_code
,p_contact_point_id => p_quote_access_tbl(counter).contact_point_id
,p_start_date_active => sysdate
,p_end_date_active => null);
IBE_UTIL.DEBUG('Finsihed calling update handler of quote_access table');
select IBE_SH_QUOTE_ACCESS_s1.nextval into l_quote_recip_id
from dual;
l_call_insert_handler := FND_API.G_TRUE;
IF (l_call_insert_handler = FND_API.G_TRUE) THEN
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('calling ins handler ');
IBE_SH_QUOTE_ACCESS_PKG.Insert_Row(
p_quote_sharee_id => l_quote_recip_id,
p_quote_header_id => p_quote_header_id,
p_quote_sharee_number => l_quote_access_rec.quote_sharee_number,
p_update_privilege_type_code => p_quote_access_tbl(counter).update_privilege_type_code,
p_party_id => p_quote_access_tbl(counter).party_id,
p_cust_account_id => p_quote_access_tbl(counter).cust_account_id,
p_recipient_name => p_quote_access_tbl(counter).recipient_name,
p_contact_point_id => l_contact_point_id);
IBE_UTIL.DEBUG('finished inserting ');
ELSIF(p_quote_access_tbl(counter).operation_code = 'UPDATE') THEN
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('Operation code in quote_access_table is UPDATE');
IF((l_quote_access_rec.update_privilege_type_code is not null) and
(l_quote_access_rec.update_privilege_type_code <> FND_API.G_MISS_CHAR) and
(l_old_access_level <> l_quote_access_rec.update_privilege_type_code)) then
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('Calling IBE_SH_QUOTE_ACCESS_PKG.Update_Row to update the recip record');
IBE_SH_QUOTE_ACCESS_PKG.Update_Row(
p_QUOTE_HEADER_ID => p_quote_header_id,
p_quote_sharee_id => p_quote_access_tbl(counter).quote_sharee_id,
p_UPDATE_PRIVILEGE_TYPE_CODE => p_quote_access_tbl(counter).update_privilege_type_code);
IBE_UTIL.DEBUG('Done IBE_SH_QUOTE_ACCESS_PKG.Update_Row ');
IF(p_quote_access_tbl(counter).UPDATE_PRIVILEGE_TYPE_CODE = 'R') THEN
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('Update privilege type is downgraded to read-only');
IBE_UTIL.DEBUG('Recipient has '||l_quote_present||' shared cart as the active-cart , delete this while downgrading level');
IBE_UTIL.DEBUG('Calling delete handler of active_quotes_all table');
IBE_ACTIVE_QUOTES_ALL_PKG.UPDATE_ROW(
x_object_version_number => 1,
x_quote_header_id => null,
x_party_id => l_quote_access_rec.party_id ,
x_cust_account_id => l_quote_access_rec.cust_account_id,
X_RECORD_TYPE => 'CART',
X_CURRENCY_CODE => null,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => 1);
IBE_UTIL.DEBUG('Done calling Update handler of active_quotes_all table to erase the quote header id' );
IBE_UTIL.DEBUG('Opcode was update but access level has not changed. Consider generic email');
l_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE := l_old_access_level;
IBE_UTIL.DEBUG('l_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE '||l_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE);
ELSIF(p_quote_access_tbl(counter).operation_code = 'DELETE') then
--ELSE --orig functionality was to default to delete, so we've got to keep it that way
--IF(p_quote_access_tbl(counter).operation_code = 'DELETE') THEN --OPERATION_CODE IS DELETE
--DBMS_OUTPUT.PUT_LINE('Operation code in save recip is delete: Caling delete_recipient');
IBE_UTIL.DEBUG('Operation code in save recip is delete');
IBE_UTIL.DEBUG('Calling delete_recipient');
IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
P_Quote_access_rec => l_quote_access_rec ,
p_minisite_id => p_minisite_id ,
p_url => p_url ,
p_notes => p_notes ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
IBE_UTIL.DEBUG('Done calling delete_recipient');
l_last_update_date date;
select aq.quote_header_id ,aq.party_id
from ibe_active_quotes aq
where party_id = c_party_id
and cust_account_id = c_cust_account_id
and record_type = 'CART';
select quote_source_code
from aso_quote_headers
where quote_header_id = c_qte_hdr_id;
select quote_header_id, quote_name
from aso_quote_headers
where quote_header_id = c_qh_id;
IBE_Quote_Misc_pvt.validate_user_update
(p_quote_header_id => p_quote_header_rec.quote_header_id,
p_party_id => p_party_id,
p_cust_account_id => p_cust_account_id,
p_quote_retrieval_number => p_retrieval_number,
p_validate_user => FND_API.G_TRUE,
p_last_update_date => p_quote_header_rec.last_update_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
If yes then update the quote_header_id of this record to p_quote_header_rec.quote_header_id(new active_cart hdr_id)
else insert a record into the active_quotes table*/
for rec_check_ac_aqa in c_check_ac_aqa( p_party_id,
p_cust_account_id) loop
l_ac_present := rec_check_ac_aqa.quote_header_id;
x_last_update_date => l_last_update_date ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data);
IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.activate_quote:Calling update row hdlr');
IBE_ACTIVE_QUOTES_ALL_PKG.update_row(
X_OBJECT_VERSION_NUMBER => 1,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => 1,
X_RECORD_TYPE => 'CART',
X_CURRENCY_CODE => null,
x_party_id => p_party_id,
x_cust_account_id => p_cust_account_id,
x_quote_header_id => p_quote_header_rec.quote_header_id);
IBE_UTIL.DEBUG('no active carts present for p_party_id hence inserting a new record');
IBE_ACTIVE_QUOTES_ALL_PKG.Insert_row(
X_OBJECT_VERSION_NUMBER => 1,
X_QUOTE_HEADER_ID => p_quote_header_rec.quote_header_id,
X_PARTY_ID => p_party_id,
X_CUST_ACCOUNT_ID => p_cust_account_id,
X_LAST_UPDATE_DATE => sysdate,
X_CREATION_DATE => sysdate,
X_RECORD_TYPE => 'CART',
X_CURRENCY_CODE => null,
X_CREATED_BY => fnd_global.USER_ID,
X_LAST_UPDATED_BY => fnd_global.USER_ID,
X_LAST_UPDATE_LOGIN => fnd_global.conc_login_id,
X_ORG_ID => MO_GLOBAL.get_current_org_id());
x_last_update_date => l_last_update_date ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data);
Select contact_point_id, object_version_number
From hz_contact_points
Where OWNER_TABLE_NAME = c_owner_table_name
AND CONTACT_POINT_TYPE = c_CONTACT_POINT_TYPE
AND OWNER_TABLE_ID = c_owner_table_id;
SELECT COUNT(contact_point_id)
INTO l_test
FROM hz_contact_points
WHERE owner_table_name = l_contact_points_rec.owner_table_name
AND owner_table_id = l_contact_points_rec.owner_table_id;
IBE_UTIL.DEBUG('call hz_conteact_point_pub.update_contact_points at'
|| to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
hz_contact_point_v2pub.update_contact_point(
p_init_msg_list => FND_API.G_FALSE
,p_contact_point_rec => l_contact_points_rec
,p_web_rec => l_web_rec
,p_email_rec => l_email_rec
,p_object_version_number => l_object_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
IBE_UTIL.DEBUG('done hz_conteact_point_pub.update_contact_points at'
|| to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
cursor c_select_recip(c_qte_hdr_id number) is
select party_id, cust_account_id
from ibe_sh_quote_access
where quote_header_id = c_qte_hdr_id
and nvl(end_date_active, sysdate+1) > sysdate ;
rec_select_recip c_select_recip%rowtype;
IBE_UTIL.DEBUG('Calling update handler of active_quotes_all table');
IBE_ACTIVE_QUOTES_ALL_PKG.UPDATE_ROW(
X_OBJECT_VERSION_NUMBER => 1,
X_QUOTE_HEADER_ID => null,
X_PARTY_ID => p_party_id ,
X_CUST_ACCOUNT_ID => p_cust_account_id,
X_RECORD_TYPE => 'CART',
X_CURRENCY_CODE => null,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => 1);
IBE_UTIL.DEBUG('Done calling Update handler of active_quotes_all table to erase the quote header id' );
/*to handle new Active Cart Definition (may be able to use original version w/ updates to handle new
active cart definition)
*Usages:
-Saving active cart and selecting a previously saved cart to append to.
*/
Procedure APPEND_QUOTE(
P_source_quote_header_id IN Number ,
P_source_last_update_date IN Date ,
P_target_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type ,
P_control_rec IN ASO_QUOTE_PUB.control_rec_type
:= ASO_QUOTE_PUB.G_MISS_Control_Rec ,
P_delete_source_cart IN Varchar2 := FND_API.G_TRUE ,
P_combinesameitem IN Varchar2 := FND_API.G_TRUE ,
P_minisite_id IN Number := FND_API.G_MISS_NUM ,
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
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) := 'APPENDQUOTE_V2';
l_last_update_date DATE;
IBE_Quote_Misc_pvt.validate_user_update
(p_quote_header_id => P_source_quote_header_id,
p_validate_user => FND_API.G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
,x_last_update_date => l_last_update_date
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data) ;
IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: P_delete_source_cart: '||P_delete_source_cart);
IF (P_delete_source_cart = FND_API.G_TRUE ) THEN
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE: P_delete_source_cart is true');
IBE_Quote_Save_pvt.Delete(
p_api_version_number => p_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_quote_header_id => P_source_quote_header_id
,p_expunge_flag => FND_API.G_FALSE
,p_minisite_id => p_minisite_id
,p_last_update_date => P_source_last_update_date);
IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.APPEND_QUOTE:Delete on source cart done');
-Other apis where cart becomes "unusable" - submit quote and delete on the shared cart.
*/
Procedure stop_sharing (
p_quote_header_id IN Number ,
p_delete_context IN VARCHAR2 := 'IBE_SC_CART_STOPSHARING' ,
P_minisite_id IN Number := FND_API.G_MISS_NUM ,
p_notes IN Varchar2 := FND_API.G_MISS_CHAR ,
p_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
:= IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl,
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
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) := 'STOPSHARING_V2';
select quote_sharee_id,
party_id,
cust_account_id,
SH.contact_point_id,
quote_sharee_number,
HZ.EMAIL_ADDRESS,
FND.customer_id shared_by_party_id
from IBE_SH_QUOTE_ACCESS SH,
HZ_CONTACT_POINTS HZ,
FND_USER FND
where SH.contact_point_id = HZ.Contact_point_id
and quote_header_id = c_qte_hdr_id
and nvl(end_date_active, sysdate+1) > sysdate
and sh.created_by = fnd.user_id;
select quote_sharee_id,
party_id,
cust_account_id,
SH.contact_point_id,
quote_sharee_number,
HZ.EMAIL_ADDRESS,
FND.customer_id shared_by_party_id
from IBE_SH_QUOTE_ACCESS SH,
HZ_CONTACT_POINTS HZ,
FND_USER FND
where SH.contact_point_id = HZ.Contact_point_id
and quote_sharee_id = c_recipient_id
and nvl(end_date_active, sysdate+1) > sysdate
and sh.created_by = fnd.user_id;
select party_id, cust_account_id
from aso_quote_headers
where quote_header_id = c_quote_header_id;
IBE_UTIL.DEBUG('STOP_SHARING:calling delete_recipient on the following recipients:');
IBE_UTIL.DEBUG('STOP_SHARING:calling delete_recipient');
IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
P_Quote_access_rec => l_quote_access_tbl(i) ,
p_minisite_id => p_minisite_id ,
p_delete_code => p_delete_context ,
p_notes => p_notes ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
P_Quote_access_rec => l_quote_access_tbl_tmp(i) ,
p_minisite_id => p_minisite_id ,
p_delete_code => p_delete_context ,
p_notes => FND_API.G_MISS_CHAR ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
SELECT quote_sharee_id, quote_header_id
FROM IBE_SH_QUOTE_ACCESS
WHERE quote_sharee_number = c_retrieval_num
OR (party_id = c_party_id and cust_account_id = c_cust_account_id)
AND quote_header_id = c_quote_header_id
AND nvl(end_date_active, sysdate+1) > sysdate;
SELECT party_id,
quote_sharee_number,
quote_header_id,
update_privilege_type_code,
contact_point_id
FROM ibe_sh_quote_access
where quote_sharee_id = c_recipient_id;
IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT(
P_Quote_access_rec => l_quote_access_rec ,
p_minisite_id => p_minisite_id ,
p_delete_code => 'END_WORKING' ,
p_url => p_url ,
p_notes => p_notes ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
l_quote_access_rec_recip.update_privilege_type_code := rec_get_recipient_party.update_privilege_type_code;
/*updates all recipients' access levels to readonly
*Usages:
-Status changes from "cart" to"quote" - request sales assistance, contract cart
*/
Procedure share_readonly (
p_quote_header_id IN Number ,
P_minisite_id IN Number := FND_API.G_MISS_NUM,
p_url IN Varchar2 := FND_API.G_MISS_CHAR,
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
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) := 'SHAREREADONLY_V2';
select quote_sharee_id,
party_id,
cust_account_id,
quote_header_id
from IBE_SH_QUOTE_ACCESS
where quote_header_id = c_qte_hdr_id
and nvl(end_date_active,sysdate+1) > sysdate;
l_quote_access_tbl(tbl_counter).update_privilege_type_code := 'R';
l_quote_access_tbl(tbl_counter).operation_code := 'UPDATE';
IBE_UTIL.DEBUG('Calling save_recipients to update recipient access level');
IBE_UTIL.DEBUG('Done calling save_recipients to update recipient access level');
/*To delete a recipient (a sort of wrapper around the "raw" delete table hander)
*Usages:
-To remove a recipient from the list of persons to whom a cart is shared.
-All deletes for a recipient should be done using this api.
-As of IBE.P - p_quote_access_rec must have the shared_by_party_id set
*/
Procedure DELETE_RECIPIENT (
P_Quote_access_rec IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE
:= IBE_QUOTE_SAVESHARE_pvt.G_MISS_QUOTE_ACCESS_REC ,
P_minisite_id IN NUMBER ,
p_delete_code IN VARCHAR2 := 'IBE_SC_CART_STOPSHARING' ,
p_url IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_notes IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_api_version IN NUMBER := 1 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
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) := 'DELETERECIPIENT_V2';
select a.quote_header_id, a.party_id ,a.cust_account_id
from IBE_ACTIVE_QUOTES a, ibe_sh_quote_access b
where a.party_id = b.party_id
and a.cust_account_id = b.cust_account_id
and b.quote_sharee_id = P_Quote_access_rec.quote_sharee_id
and nvl(b.end_date_active, sysdate+1) > sysdate
and a.quote_header_id = p_quote_access_rec.quote_header_id
and record_type= 'CART';
select party_id, cust_account_id
from ASO_QUOTE_HEADERS_ALL
where quote_header_id = c_quote_id;
select QUOTE_ACCESS.party_id, CNTCT_POINTS.contact_point_id, CNTCT_POINTS.object_version_number
from hz_contact_points CNTCT_POINTS, ibe_sh_quote_access QUOTE_ACCESS
where CNTCT_POINTS.contact_point_id = QUOTE_ACCESS.contact_point_id
and quote_sharee_id = c_quote_sharee_id;
l_delete_context VARCHAR2(2000);
SAVEPOINT DELETERECIPIENT_V2;
IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT: START');
IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT:user has an active cart');
IBE_UTIL.DEBUG('Calling update row handler to deactivate the cart');
IBE_ACTIVE_QUOTES_ALL_PKG.UPDATE_ROW(
X_OBJECT_VERSION_NUMBER => 1 ,
X_QUOTE_HEADER_ID => null ,
X_PARTY_ID => rec_find_active_cart.party_id ,
X_CUST_ACCOUNT_ID => rec_find_active_cart.cust_account_id,
X_RECORD_TYPE => 'CART' ,
X_CURRENCY_CODE => null ,
x_last_update_date => sysdate ,
x_last_updated_by => fnd_global.user_id ,
x_last_update_login => 1);
IBE_UTIL.DEBUG('Done calling update row handler to deactivate the cart');
IBE_UTIL.DEBUG('Calling Update row handler on sh_quote_access table to end-date recipient row');
HZ_CONTACT_POINT_V2PUB.update_contact_point(
p_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => l_contact_point_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IBE_SH_QUOTE_ACCESS_PKG.update_Row(
p_quote_sharee_id => P_Quote_access_rec.quote_sharee_id
,p_end_date_active => sysdate);
IBE_UTIL.DEBUG('Done calling Update row handler on sh_quote_access table to end-date recipient row');
IBE_UTIL.DEBUG('IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT: END');
IBE_UTIL.DEBUG('delete_code is: '||p_delete_code);
IF (p_delete_code = 'END_WORKING') THEN
/*obtain the owner party_id and acctid of the cart/quote being dealt with*/
FOR rec_get_owner_ids in c_get_owner_ids(p_quote_access_rec.quote_header_id) LOOP
l_owner_partyid := rec_get_owner_ids.party_id;
p_context_code => p_delete_code ,
p_shared_by_partyid=> p_quote_access_rec.shared_by_party_id,
p_notes => p_notes ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
ROLLBACK TO DELETERECIPIENT_V2;
IBE_Util.Debug('Expected error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
ROLLBACK TO DELETERECIPIENT_V2;
IBE_Util.Debug('Unexpected error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
ROLLBACK TO DELETERECIPIENT_V2;
IBE_Util.Debug('Unknown error in IBE_QUOTE_SAVESHARE_V2_PVT.DELETE_RECIPIENT()');
PROCEDURE Validate_share_Update(
p_api_version_number IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_quote_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
,p_quote_access_tbl IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_Tbl_Type
:= IBE_QUOTE_SAVESHARE_pvt.G_miss_quote_access_Tbl
-- partyid and accountid cannot be gmiss coming in
,p_party_id IN NUMBER
,p_cust_account_id IN NUMBER
,p_retrieval_number IN NUMBER := FND_API.G_MISS_NUM
,p_operation_code IN VARCHAR2
,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) := 'Validate_share_Update';
l_check_updates VARCHAR2(1) := FND_API.G_FALSE; -- checks timestamps and if updates are really necessary
l_db_last_update_date DATE;
select party_id
from ASO_QUOTE_HEADERS_ALL
where quote_header_id = c_quote_id;
select update_privilege_type_code
from ibe_sh_quote_access
where quote_sharee_number = c_retrieval_number
and (party_id is null or party_id = c_party_id)
and (cust_account_id is null or cust_account_id = c_acct_id);
select update_privilege_type_code
from ibe_sh_quote_access
where quote_header_id = c_qte_hdr_id
and party_id = c_party_id
and cust_account_id = c_account_id
AND nvl(end_date_active, sysdate+1) > sysdate;
select last_update_date, last_updated_by, end_date_active, update_privilege_type_code
from ibe_sh_quote_access
where quote_sharee_id = c_recipient_id;
IBE_UTIL.DEBUG('Begin validate_share_update : ' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
l_access_level := rec_get_role_by_retr_num.update_privilege_type_code;
l_access_level := rec_get_role_by_user.update_privilege_type_code;
l_check_updates := FND_API.G_TRUE;
elsif (p_operation_code in (OP_STOP_SHARING, OP_DELETE_CART)) then
if ((l_is_owner = FND_API.G_FALSE) and (l_access_level <> 'A')) then
IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
l_check_updates := FND_API.G_TRUE;
l_check_updates := FND_API.G_TRUE;
l_check_updates := FND_API.G_FALSE; -- no validations to do here that have not already been done
IBE_UTIL.DEBUG('l_check_updates is :'||l_check_updates);
if ((l_check_updates = FND_API.G_TRUE) or (l_check_onlynotify = FND_API.G_TRUE)) then
l_env_user_id := FND_GLOBAL.USER_ID;
l_db_quote_access_tbl(i).last_update_date := rec_get_recipient_info.last_update_date;
l_db_quote_access_tbl(i).last_updated_by := rec_get_recipient_info.last_updated_by;
l_db_quote_access_tbl(i).update_privilege_type_code := rec_get_recipient_info.update_privilege_type_code;
IBE_UTIL.DEBUG('input last_update_date : '||to_char(p_quote_access_tbl(i).last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
IBE_UTIL.DEBUG('db last_update_date : '||to_char(l_db_quote_access_tbl(i).last_update_date,'mm/dd/yyyy:hh24:MI:SS'));
IBE_UTIL.DEBUG('input access_level : '||p_quote_access_tbl(i).update_privilege_type_code);
IBE_UTIL.DEBUG('db access_level : '||l_db_quote_access_tbl(i).update_privilege_type_code);
if ((p_quote_access_tbl(i).operation_code = 'DELETE')
or(p_quote_access_tbl(i).operation_code = 'CREATE')
or ((p_quote_access_tbl(i).operation_code = 'UPDATE')
and (p_quote_access_tbl(i).update_privilege_type_code is not null)
and (p_quote_access_tbl(i).update_privilege_type_code <> FND_API.G_MISS_CHAR)
and (p_quote_access_tbl(i).update_privilege_type_code <> l_db_quote_access_tbl(i).update_privilege_type_code))) then
IF FND_Msg_Pub.Check_Msg_Level (FND_Msg_Pub.G_MSG_LVL_ERROR) THEN
FND_Message.Set_Name('IBE', 'IBE_SC_ERR_PRIVILEGE');
if (l_check_updates = FND_API.G_TRUE) then
FOR i in 1..p_quote_access_tbl.count LOOP
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('current quote_sharee_id : '||p_quote_access_tbl(i).quote_sharee_id);
if ((p_quote_access_tbl(i).last_update_date <> FND_API.G_MISS_DATE) and
(p_quote_access_tbl(i).last_update_date <> l_db_quote_access_tbl(i).last_update_date)) then
-- i.e. don't throw exception if we want to end a row and it's already enddated.
if ((p_quote_access_tbl(i).operation_code = 'DELETE') and (nvl(l_db_quote_access_tbl(i).end_date_active,sysdate-1) < sysdate)) then
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('deleteing an end dated row, allowing it to pass through');
elsif (l_env_user_id = l_db_quote_access_tbl(i).last_updated_by) then
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_UTIL.DEBUG('timestamps do not match, but user was the last to update the row so allowing it to go through');
end if; -- end if last_update_dates dont match
end if; -- end if doing check updates
end if; -- end check of updates
IBE_UTIL.DEBUG('End validate_share_update' || to_char(sysdate, 'mm/dd/yyyy:hh24:MI:SS'));
IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: expected error');
IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: unexpected error');
IBE_Util.Debug('End IBE_Quote_Misc_pvt.validate_share_update: other exception');
END validate_share_update;