[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Access_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Access_Type VARCHAR2,
X_Freeze_Flag VARCHAR2,
X_Reassign_Flag VARCHAR2,
X_Team_Leader_Flag VARCHAR2,
X_Person_Id NUMBER,
X_Customer_Id NUMBER,
X_Address_Id NUMBER,
X_Salesforce_Id NUMBER,
X_Partner_Customer_Id NUMBER,
X_Partner_Address_Id NUMBER,
X_Created_Person_Id NUMBER,
X_Lead_Id NUMBER,
X_Freeze_Date DATE,
X_Reassign_Reason VARCHAR2,
x_reassign_request_date DATE,
x_reassign_requested_person_id NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Salesforce_Role_Code VARCHAR2,
X_Salesforce_Relationship_Code VARCHAR2,
X_Internal_update_access NUMBER ,
X_Sales_lead_id NUMBER ,
X_Sales_group_id NUMBER ,
X_Partner_Cont_Party_Id NUMBER,
X_owner_flag VARCHAR2,
X_created_by_tap_flag VARCHAR2,
X_prm_keep_flag VARCHAR2,
X_open_flag VARCHAR2,
X_lead_rank_score NUMBER,
X_object_creation_date DATE,
X_contributor_flag VARCHAR2 -- Added for ASNB
) IS
CURSOR C IS SELECT rowid FROM as_accesses_all
WHERE access_id = X_Access_Id;
CURSOR C2 IS SELECT as_accesses_s.nextval FROM sys.dual;
INSERT INTO as_accesses_all(
access_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
access_type,
freeze_flag,
reassign_flag,
team_leader_flag,
person_id,
customer_id,
address_id,
salesforce_id,
partner_customer_id,
partner_address_id,
created_person_id,
lead_id,
freeze_date,
reassign_reason,
reassign_request_date,
reassign_requested_person_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
salesforce_role_code,
salesforce_relationship_code,
internal_update_access,
sales_lead_id,
sales_group_id,
partner_cont_party_id,
owner_flag,
created_by_tap_flag,
prm_keep_flag,
open_flag,
lead_rank_score,
object_creation_date,
contributor_flag -- Added for ASNB
) VALUES (
X_Access_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
decode(X_Access_Type,FND_API.G_MISS_CHAR,NULL,X_Access_Type),
decode(X_Freeze_Flag,FND_API.G_MISS_CHAR,'Y',X_Freeze_Flag),
decode(X_Reassign_Flag,FND_API.G_MISS_CHAR,'N',X_Reassign_Flag),
decode(X_Team_Leader_Flag,FND_API.G_MISS_CHAR,'N',X_Team_Leader_Flag),
decode(X_Person_Id, FND_API.G_MISS_NUM, NULL, X_person_id),
decode(X_Customer_Id,FND_API.G_MISS_NUM, NULL, X_customer_id),
decode(X_Address_Id,FND_API.G_MISS_NUM, NULL, X_address_id ),
decode(X_Salesforce_Id,FND_API.G_MISS_NUM, NULL, X_salesforce_id),
decode(X_Partner_Customer_Id,FND_API.G_MISS_NUM, NULL, X_partner_customer_id),
decode(X_Partner_Address_Id,FND_API.G_MISS_NUM, NULL, X_partner_address_id),
decode(X_Created_Person_Id,FND_API.G_MISS_NUM, NULL, X_created_person_id ),
decode(X_Lead_Id,FND_API.G_MISS_NUM, NULL, X_lead_id),
decode(X_Freeze_Date,FND_API.G_MISS_DATE, NULL,X_Freeze_Date),
decode(X_Reassign_Reason,FND_API.G_MISS_CHAR,NULL,X_Reassign_Reason),
decode(X_reassign_request_date,FND_API.G_MISS_DATE,
NULL,X_reassign_request_date),
decode(X_reassign_requested_person_id ,FND_API.G_MISS_NUM,
NULL,X_reassign_requested_person_id ),
decode(X_Attribute_Category,FND_API.G_MISS_CHAR,NULL,X_Attribute_Category),
decode(X_Attribute1,FND_API.G_MISS_CHAR,NULL,X_Attribute1),
decode(X_Attribute2,FND_API.G_MISS_CHAR,NULL,X_Attribute2),
decode(X_Attribute3,FND_API.G_MISS_CHAR,NULL,X_Attribute3),
decode(X_Attribute4,FND_API.G_MISS_CHAR,NULL,X_Attribute4),
decode(X_Attribute5,FND_API.G_MISS_CHAR,NULL,X_Attribute5),
decode(X_Attribute6,FND_API.G_MISS_CHAR,NULL,X_Attribute6),
decode(X_Attribute7,FND_API.G_MISS_CHAR,NULL,X_Attribute7),
decode(X_Attribute8,FND_API.G_MISS_CHAR,NULL,X_Attribute8),
decode(X_Attribute9,FND_API.G_MISS_CHAR,NULL,X_Attribute9),
decode(X_Attribute10,FND_API.G_MISS_CHAR,NULL,X_Attribute10),
decode(X_Attribute11,FND_API.G_MISS_CHAR,NULL,X_Attribute11),
decode(X_Attribute12,FND_API.G_MISS_CHAR,NULL,X_Attribute12),
decode(X_Attribute13,FND_API.G_MISS_CHAR,NULL,X_Attribute13),
decode(X_Attribute14,FND_API.G_MISS_CHAR,NULL,X_Attribute14),
decode(X_Attribute15,FND_API.G_MISS_CHAR,NULL,X_Attribute15),
decode(X_Salesforce_Role_Code,FND_API.G_MISS_CHAR,NULL,X_Salesforce_Role_Code),
decode(X_Salesforce_Relationship_Code,FND_API.G_MISS_CHAR,NULL,X_Salesforce_Relationship_Code),
decode(X_Internal_update_access,FND_API.G_MISS_NUM,NULL,X_Internal_update_access),
decode(X_Sales_lead_id,FND_API.G_MISS_NUM,NULL,X_Sales_lead_id),
decode(X_Sales_group_id,FND_API.G_MISS_NUM,NULL,X_Sales_group_id),
decode(X_Partner_Cont_Party_id,FND_API.G_MISS_NUM,NULL,X_Partner_Cont_Party_Id),
decode(X_owner_flag,FND_API.G_MISS_CHAR,'N',X_owner_flag),
decode(X_created_by_tap_flag,FND_API.G_MISS_CHAR,NULL,X_created_by_tap_flag),
decode(X_prm_keep_flag,FND_API.G_MISS_CHAR,NULL,X_prm_keep_flag),
decode(X_open_flag, FND_API.G_MISS_CHAR, 'N', X_open_flag),
decode(X_lead_rank_score, FND_API.G_MISS_NUM, NULL, X_lead_rank_score),
decode(X_object_creation_date, FND_API.G_MISS_DATE, TO_DATE(NULL), X_object_creation_date),
decode(X_contributor_flag, FND_API.G_MISS_CHAR, 'N', X_contributor_flag) -- Added for ASNB
);
END Insert_Row;
SELECT *
FROM as_accesses_all
WHERE rowid = X_Rowid
FOR UPDATE of Access_Id NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Access_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Access_Type VARCHAR2,
X_Freeze_Flag VARCHAR2,
X_Reassign_Flag VARCHAR2,
X_Team_Leader_Flag VARCHAR2,
X_Person_Id NUMBER,
X_Customer_Id NUMBER,
X_Address_Id NUMBER,
X_Salesforce_Id NUMBER,
X_Partner_Customer_Id NUMBER,
X_Partner_Address_Id NUMBER,
X_Created_Person_Id NUMBER,
X_Lead_Id NUMBER,
X_Freeze_Date DATE,
X_Reassign_Reason VARCHAR2,
x_reassign_request_date DATE,
x_reassign_requested_person_id NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Salesforce_Role_Code VARCHAR2,
X_Salesforce_Relationship_Code VARCHAR2,
X_Internal_update_access NUMBER ,
X_Sales_lead_id NUMBER ,
X_Sales_group_id NUMBER ,
X_Partner_Cont_Party_Id NUMBER ,
X_owner_flag VARCHAR2,
X_created_by_tap_flag VARCHAR2,
X_prm_keep_flag VARCHAR2,
X_contributor_flag VARCHAR2 -- Added for ASNB
) IS
BEGIN
UPDATE as_accesses_all
SET object_version_number = nvl(object_version_number,0) + 1,
access_id = X_Access_Id,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
access_type = decode(X_Access_Type,FND_API.G_MISS_CHAR,access_type,X_Access_Type),
freeze_flag = decode(X_Freeze_Flag,FND_API.G_MISS_CHAR,freeze_flag,X_Freeze_Flag),
reassign_flag = decode(X_Reassign_Flag,FND_API.G_MISS_CHAR,reassign_flag,X_Reassign_Flag),
team_leader_flag = decode(X_Team_Leader_Flag,FND_API.G_MISS_CHAR,team_leader_flag,X_Team_Leader_Flag),
person_id = decode(X_Person_Id,FND_API.G_MISS_NUM, person_id,X_Person_Id ),
customer_id = decode(X_Customer_Id,FND_API.G_MISS_NUM,customer_id,X_Customer_Id),
address_id = decode(X_Address_Id,FND_API.G_MISS_NUM,address_id,X_Address_Id ),
salesforce_id = decode(X_Salesforce_Id,FND_API.G_MISS_NUM,salesforce_id,X_Salesforce_Id),
partner_customer_id =decode(X_Partner_Customer_Id,FND_API.G_MISS_NUM,partner_customer_id,X_Partner_Customer_Id),
partner_address_id = decode(X_Partner_Address_Id,FND_API.G_MISS_NUM,partner_address_id,X_Partner_Address_Id),
created_person_id = decode(X_Created_Person_Id,FND_API.G_MISS_NUM,created_person_id,X_Created_Person_Id ),
lead_id = decode(X_Lead_Id,FND_API.G_MISS_NUM,lead_id,X_Lead_Id ),
freeze_date = decode(X_Freeze_Date,FND_API.G_MISS_DATE, freeze_date,X_Freeze_Date),
reassign_reason = decode(X_Reassign_Reason,FND_API.G_MISS_CHAR,reassign_reason,X_Reassign_Reason),
reassign_request_date = decode(X_reassign_request_date,FND_API.G_MISS_DATE,
reassign_request_date,X_reassign_request_date),
reassign_requested_person_id = decode(X_reassign_requested_person_id ,FND_API.G_MISS_NUM,
reassign_requested_person_id ,X_reassign_requested_person_id ),
attribute_category = decode(X_Attribute_Category,FND_API.G_MISS_CHAR,attribute_category,X_Attribute_Category),
attribute1 = decode(X_Attribute1,FND_API.G_MISS_CHAR,attribute1,X_Attribute1),
attribute2 = decode(X_Attribute2,FND_API.G_MISS_CHAR,attribute2,X_Attribute2),
attribute3 = decode(X_Attribute3,FND_API.G_MISS_CHAR,attribute3,X_Attribute3),
attribute4 = decode(X_Attribute4,FND_API.G_MISS_CHAR,attribute4,X_Attribute4),
attribute5 = decode(X_Attribute5,FND_API.G_MISS_CHAR,attribute5,X_Attribute5),
attribute6 = decode(X_Attribute6,FND_API.G_MISS_CHAR,attribute6,X_Attribute6),
attribute7 = decode(X_Attribute7,FND_API.G_MISS_CHAR,attribute7,X_Attribute7),
attribute8 = decode(X_Attribute8,FND_API.G_MISS_CHAR,attribute8,X_Attribute8),
attribute9 = decode(X_Attribute9,FND_API.G_MISS_CHAR,attribute9,X_Attribute9),
attribute10 = decode(X_Attribute10,FND_API.G_MISS_CHAR,attribute10,X_Attribute10),
attribute11 = decode(X_Attribute11,FND_API.G_MISS_CHAR,attribute11,X_Attribute11),
attribute12 = decode(X_Attribute12,FND_API.G_MISS_CHAR,attribute12,X_Attribute12),
attribute13 = decode(X_Attribute13,FND_API.G_MISS_CHAR,attribute13,X_Attribute13),
attribute14 = decode(X_Attribute14,FND_API.G_MISS_CHAR,attribute14,X_Attribute14),
attribute15 = decode(X_Attribute15,FND_API.G_MISS_CHAR,attribute15,X_Attribute15),
salesforce_role_code = decode(X_Salesforce_Role_Code,FND_API.G_MISS_CHAR,salesforce_role_code,X_Salesforce_Role_Code),
salesforce_relationship_code = decode(X_Salesforce_Relationship_Code,FND_API.G_MISS_CHAR,salesforce_relationship_code,X_Salesforce_Relationship_Code),
internal_update_access = decode(X_Internal_update_access,FND_API.G_MISS_NUM,internal_update_access,X_Internal_update_access),
sales_lead_id = decode(X_Sales_lead_id,FND_API.G_MISS_NUM,sales_lead_id,X_Sales_lead_id),
sales_group_id = decode(X_Sales_group_id,FND_API.G_MISS_NUM,sales_group_id,X_Sales_group_id),
partner_cont_party_id =decode(X_Partner_Cont_Party_id,FND_API.G_MISS_NUM,partner_cont_party_id,X_Partner_Cont_Party_Id),
owner_flag = decode(X_owner_flag,FND_API.G_MISS_CHAR,owner_flag,X_owner_flag),
created_by_tap_flag = decode(X_created_by_tap_flag,FND_API.G_MISS_CHAR,created_by_tap_flag,X_created_by_tap_flag),
prm_keep_flag = decode(X_prm_keep_flag,FND_API.G_MISS_CHAR,prm_keep_flag,X_prm_keep_flag),
contributor_flag = decode(X_contributor_flag,FND_API.G_MISS_CHAR,contributor_flag,X_contributor_flag) -- Added for ASNB
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM as_accesses_all
WHERE rowid = X_Rowid;
END Delete_Row;
select access_id
into X_Acc_Id
from as_accesses_all
where customer_id = X_Customer_Id
-- and address_id = X_Address_Id
and salesforce_id = X_Salesforce_Id
and person_id = X_Person_Id
and ( (lead_id = X_Lead_Id) or
(lead_id is null and X_Lead_Id is null)
)
and ( (access_id <> X_Access_Id) or
(X_Access_Id is NULL) );
select Team_Leader_Flag
from as_accesses_all
where customer_id = X_Customer_Id
-- and address_id = X_Address_Id
and salesforce_id = X_Salesforce_Id
and person_id = X_Person_Id
and ( (lead_id = X_Lead_Id) or
(lead_id is null and X_Lead_Id is null)
);
select access_id
into X_Acc_Id
from as_accesses_all
where customer_id = X_Customer_Id
-- and address_id = X_Address_Id
and salesforce_id = X_Salesforce_Id
and partner_customer_id = X_Partner_Customer_Id
-- and partner_address_id = X_Partner_Address_Id
and ( (lead_id = X_Lead_Id) or
(lead_id is null and X_Lead_Id is null)
)
and ( (access_id <> X_Access_Id) or
(X_Access_Id is NULL) );