[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR c1 IS SELECT as_leads_s.nextval FROM dual;
SELECT
SCD.sales_credit_id
,SCD.lead_id
,SCD.customer_id
,SCD.employee_person_id
,SCD.opp_open_status_flag
,SCD.owner_salesforce_id
,SCD.owner_sales_group_id
,SCD.salesforce_id
,SCD.sales_group_id
,RANK () OVER (PARTITION BY SCD.lead_id ORDER BY SCD.sales_credit_id) RK
FROM
as_sales_credits_denorm SCD
WHERE
SCD.lead_id BETWEEN pc_start_id AND pc_end_id
AND SCD.credit_type_id = pc_credit_type_id
AND SCD.salesforce_id IS NOT NULL
AND SCD.sales_group_id IS NOT NULL
AND SCD.partner_customer_id IS NULL
AND NOT EXISTS (SELECT 1 FROM as_sales_credits SC2
WHERE SC2.lead_id = SCD.lead_id
AND SC2.credit_type_id = pc_credit_type_id
AND SC2.sales_credit_id <> SCD.sales_credit_id
AND (SC2.salesforce_id <> SCD.salesforce_id
OR SC2.salesgroup_id <> SCD.sales_group_id))
AND (SCD.salesforce_id <> SCD.owner_salesforce_id
OR SCD.sales_group_id <> SCD.owner_sales_group_id
OR SCD.owner_salesforce_id IS NULL
OR SCD.owner_sales_group_id IS NULL);
UPDATE
as_leads_all ALA
SET
ALA.owner_salesforce_id = l_salesforce_ids(i)
,ALA.owner_sales_group_id = l_sales_group_ids(i) ,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE
ALA.lead_id = l_lead_ids(i)
AND l_ranks(i) = 1
AND (ALA.owner_salesforce_id <> l_salesforce_ids(i)
OR ALA.owner_sales_group_id <> l_sales_group_ids(i)
OR ALA.owner_salesforce_id IS NULL
OR ALA.owner_sales_group_id IS NULL);
'Updated owner in as_leads_all: number of rows =' ||
sql%ROWCOUNT);
UPDATE
as_accesses_all ACS
SET
owner_flag = 'N',
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE
ACS.lead_id = l_lead_ids(i)
AND l_ranks(i) = 1
AND ACS.owner_flag = 'Y';
'Updated as_accesses_all to have owner flag reset = ' ||
sql%ROWCOUNT);
UPDATE
as_accesses_all ACS
SET
owner_flag = 'Y'
,freeze_flag = 'Y' ,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE
ACS.lead_id = l_lead_ids(i)
AND l_ranks(i) = 1
AND (ACS.owner_flag = 'N'
OR ACS.owner_flag IS NULL)
AND ACS.salesforce_id = l_salesforce_ids(i)
AND ACS.sales_group_id = l_sales_group_ids(i);
'Updated as_accesses_all to have owner flag set for new owner = ' ||
sql%ROWCOUNT);
'Inserting into as_accesses_all');
INSERT INTO
as_accesses_all
(
access_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,reassign_flag
,team_leader_flag
,customer_id
,salesforce_id
,person_id
,partner_customer_id
,lead_id
,sales_group_id
,partner_cont_party_id
,owner_flag
,created_by_tap_flag
,open_flag
,freeze_flag
,org_id
,object_version_number
)
SELECT
AS_ACCESSES_S.nextval
,sysdate
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID
,NULL
,'Y'
,l_customer_ids(i)
,l_salesforce_ids(i)
,l_person_ids(i)
,NULL
,l_lead_ids(i)
,l_sales_group_ids(i)
,NULL
,'Y'
,'N'
,l_open_flags(i)
,'Y'
,NULL
,1
FROM
dual
WHERE
l_ranks(i) = 1
AND NOT EXISTS (SELECT 1 FROM as_accesses_all ACS
WHERE ACS.lead_id IS NOT NULL
AND ACS.lead_id = l_lead_ids(i)
AND l_ranks(i) = 1
AND ACS.salesforce_id = l_salesforce_ids(i)
AND ACS.sales_group_id = l_sales_group_ids(i));
'Inserted into as_accesses_all = ' ||
sql%ROWCOUNT);
SELECT wf_item_type, wf_item_key, wf_status, matched_due_date,
offered_due_date, bypass_cm_ok_flag, routing_status, routing_type
FROM pv_lead_workflows
WHERE lead_id = pc_lead_id and latest_routing_flag = 'Y'
AND entity = 'OPPORTUNITY';
SELECT partner_id, assign_sequence, lead_id, status, status_date,
wf_item_type, wf_item_key, source_type, related_party_id,
partner_access_code, reason_code, related_party_access_code,
lead_assignment_id
FROM pv_lead_assignments
WHERE wf_item_type = pc_itemtype AND wf_item_key = pc_itemkey;
SELECT notification_type, lead_assignment_id, user_id, user_name,
resource_id, decision_maker_flag, resource_response, response_date
FROM pv_party_notifications WHERE lead_assignment_id = pc_assignment_id;
l_lead_workflow_rec.last_updated_by := l_user_id;
PROCEDURE Update_sc_for_rep (
p_lead_id IN NUMBER,
p_sf_id IN NUMBER,
p_sg_id IN NUMBER,
p_credit_type_id IN NUMBER,
p_identity_sf_id IN NUMBER,
p_debug_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(256) :=
'asn.plsql.asn_mig_sales_credits_pvt.Update_sc_for_rep';
SELECT * FROM as_sales_credits
WHERE lead_id = p_lead_id
AND credit_type_id = p_credit_type_id
AND nvl(salesforce_id, -37) = nvl(p_sf_id, -37)
AND nvl(salesgroup_id, -37) = nvl(p_sg_id, -37)
ORDER BY lead_line_id;
l_sc_select_rec c_rep_quota_credits%ROWTYPE;
l_sc_next_select_rec c_rep_quota_credits%ROWTYPE;
SELECT *
FROM as_lead_lines
WHERE lead_line_id = p_lead_line_id AND total_amount <> p_sc_amount;
'Begin Update_sc_for_rep');
l_ll_tbl.DELETE;
FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
l_sc_select_rec := l_sc_next_select_rec;
FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
l_sc_select_rec.credit_amount;
(l_sc_select_rec.lead_line_id
<> l_sc_next_select_rec.lead_line_id) THEN
l_sc_tbl.DELETE;
l_sc_tbl(1).last_update_date := l_sc_select_rec.last_update_date;
l_sc_tbl(1).last_updated_by := FND_GLOBAL.user_id;
l_sc_tbl(1).creation_Date := l_sc_select_rec.creation_Date;
l_sc_tbl(1).last_update_login := FND_GLOBAL.conc_login_id;
l_sc_tbl(1).program_update_date := sysdate;
l_sc_tbl(1).sales_credit_id := l_sc_select_rec.sales_credit_id;
l_sc_tbl(1).original_sales_credit_id := l_sc_select_rec.original_sales_credit_id;
l_sc_tbl(1).lead_id := l_sc_select_rec.lead_id;
l_sc_tbl(1).lead_line_id := l_sc_select_rec.lead_line_id;
l_sc_tbl(1).salesforce_id := l_sc_select_rec.salesforce_id;
l_sc_tbl(1).person_id := l_sc_select_rec.person_id;
l_sc_tbl(1).salesgroup_id := l_sc_select_rec.salesgroup_id;
l_sc_tbl(1).partner_customer_id := l_sc_select_rec.partner_customer_id;
l_sc_tbl(1).partner_address_id := l_sc_select_rec.partner_address_id;
l_sc_tbl(1).revenue_amount := l_sc_select_rec.revenue_amount;
l_sc_tbl(1).revenue_percent := l_sc_select_rec.revenue_percent;
l_sc_tbl(1).quota_credit_amount := l_sc_select_rec.quota_credit_amount;
l_sc_tbl(1).quota_credit_percent := l_sc_select_rec.quota_credit_percent;
l_sc_tbl(1).MANAGER_REVIEW_FLAG := l_sc_select_rec.MANAGER_REVIEW_FLAG;
l_sc_tbl(1).MANAGER_REVIEW_DATE := l_sc_select_rec.MANAGER_REVIEW_DATE;
l_sc_tbl(1).credit_type_id := l_sc_select_rec.credit_type_id;
l_sc_tbl(1).attribute_category := l_sc_select_rec.attribute_category;
l_sc_tbl(1).attribute1 := l_sc_select_rec.attribute1;
l_sc_tbl(1).attribute2 := l_sc_select_rec.attribute2;
l_sc_tbl(1).attribute3 := l_sc_select_rec.attribute3;
l_sc_tbl(1).attribute4 := l_sc_select_rec.attribute4;
l_sc_tbl(1).attribute5 := l_sc_select_rec.attribute5;
l_sc_tbl(1).attribute6 := l_sc_select_rec.attribute6;
l_sc_tbl(1).attribute7 := l_sc_select_rec.attribute7;
l_sc_tbl(1).attribute8 := l_sc_select_rec.attribute8;
l_sc_tbl(1).attribute9 := l_sc_select_rec.attribute9;
l_sc_tbl(1).attribute10 := l_sc_select_rec.attribute10;
l_sc_tbl(1).attribute11 := l_sc_select_rec.attribute11;
l_sc_tbl(1).attribute12 := l_sc_select_rec.attribute12;
l_sc_tbl(1).attribute13 := l_sc_select_rec.attribute13;
l_sc_tbl(1).attribute14 := l_sc_select_rec.attribute14;
l_sc_tbl(1).attribute15 := l_sc_select_rec.attribute15;
FOR ll_select_rec IN
c_lead_line(l_sc_tbl(1).lead_line_id, l_sc_amount)
LOOP
l_ll_tbl_count := l_ll_tbl_count + 1;
l_ll_tbl(l_ll_tbl_count).last_update_date := ll_select_rec.last_update_date;
l_ll_tbl(l_ll_tbl_count).last_updated_by := FND_GLOBAL.user_id;
l_ll_tbl(l_ll_tbl_count).creation_Date := ll_select_rec.creation_Date;
l_ll_tbl(l_ll_tbl_count).created_by := ll_select_rec.created_by;
l_ll_tbl(l_ll_tbl_count).last_update_login := FND_GLOBAL.conc_login_id;
l_ll_tbl(l_ll_tbl_count).program_update_date := sysdate;
l_ll_tbl(l_ll_tbl_count).lead_id := ll_select_rec.lead_id;
l_ll_tbl(l_ll_tbl_count).lead_line_id := ll_select_rec.lead_line_id;
l_ll_tbl(l_ll_tbl_count).original_lead_line_id := ll_select_rec.original_lead_line_id;
l_ll_tbl(l_ll_tbl_count).interest_type_id := ll_select_rec.interest_type_id;
l_ll_tbl(l_ll_tbl_count).interest_status_code := ll_select_rec.interest_status_code;
l_ll_tbl(l_ll_tbl_count).primary_interest_code_id := ll_select_rec.primary_interest_code_id;
l_ll_tbl(l_ll_tbl_count).secondary_interest_code_id := ll_select_rec.secondary_interest_code_id;
l_ll_tbl(l_ll_tbl_count).inventory_item_id := ll_select_rec.inventory_item_id;
l_ll_tbl(l_ll_tbl_count).organization_id := ll_select_rec.organization_id;
l_ll_tbl(l_ll_tbl_count).uom_code := ll_select_rec.uom_code;
l_ll_tbl(l_ll_tbl_count).quantity := ll_select_rec.quantity;
l_ll_tbl(l_ll_tbl_count).ship_date := ll_select_rec.ship_date;
l_ll_tbl(l_ll_tbl_count).sales_stage_id := ll_select_rec.sales_stage_id;
l_ll_tbl(l_ll_tbl_count).win_probability := ll_select_rec.win_probability;
l_ll_tbl(l_ll_tbl_count).status_code := ll_select_rec.status_code;
l_ll_tbl(l_ll_tbl_count).decision_date := ll_select_rec.decision_date;
l_ll_tbl(l_ll_tbl_count).channel_code := ll_select_rec.channel_code;
l_ll_tbl(l_ll_tbl_count).price := ll_select_rec.price;
l_ll_tbl(l_ll_tbl_count).price_volume_margin := ll_select_rec.price_volume_margin;
l_ll_tbl(l_ll_tbl_count).quoted_line_flag := ll_select_rec.quoted_line_flag;
l_ll_tbl(l_ll_tbl_count).Source_Promotion_Id := ll_select_rec.Source_Promotion_Id;
l_ll_tbl(l_ll_tbl_count).forecast_date := ll_select_rec.forecast_date;
l_ll_tbl(l_ll_tbl_count).rolling_forecast_flag := ll_select_rec.rolling_forecast_flag;
l_ll_tbl(l_ll_tbl_count).Offer_Id := ll_select_rec.Offer_Id;
l_ll_tbl(l_ll_tbl_count).ORG_ID := ll_select_rec.ORG_ID;
l_ll_tbl(l_ll_tbl_count).product_category_id := ll_select_rec.product_category_id;
l_ll_tbl(l_ll_tbl_count).product_cat_set_id := ll_select_rec.product_cat_set_id;
l_ll_tbl(l_ll_tbl_count).attribute_category := ll_select_rec.attribute_category;
l_ll_tbl(l_ll_tbl_count).attribute1 := ll_select_rec.attribute1;
l_ll_tbl(l_ll_tbl_count).attribute2 := ll_select_rec.attribute2;
l_ll_tbl(l_ll_tbl_count).attribute3 := ll_select_rec.attribute3;
l_ll_tbl(l_ll_tbl_count).attribute4 := ll_select_rec.attribute4;
l_ll_tbl(l_ll_tbl_count).attribute5 := ll_select_rec.attribute5;
l_ll_tbl(l_ll_tbl_count).attribute6 := ll_select_rec.attribute6;
l_ll_tbl(l_ll_tbl_count).attribute7 := ll_select_rec.attribute7;
l_ll_tbl(l_ll_tbl_count).attribute8 := ll_select_rec.attribute8;
l_ll_tbl(l_ll_tbl_count).attribute9 := ll_select_rec.attribute9;
l_ll_tbl(l_ll_tbl_count).attribute10 := ll_select_rec.attribute10;
l_ll_tbl(l_ll_tbl_count).attribute11 := ll_select_rec.attribute11;
l_ll_tbl(l_ll_tbl_count).attribute12 := ll_select_rec.attribute12;
l_ll_tbl(l_ll_tbl_count).attribute13 := ll_select_rec.attribute13;
l_ll_tbl(l_ll_tbl_count).attribute14 := ll_select_rec.attribute14;
l_ll_tbl(l_ll_tbl_count).attribute15 := ll_select_rec.attribute15;
AS_OPPORTUNITY_PUB.Update_Opp_Lines(
p_api_version_number => 2.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => 90,
p_identity_salesforce_id => p_identity_sf_id,
p_line_tbl => l_ll_tbl,
p_header_rec => l_header_rec,
p_check_access_flag => 'N',
p_admin_flag => 'N',
p_admin_group_id => NULL,
p_partner_cont_party_id => NULL,
x_line_out_tbl => l_ll_out_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'End Update_sc_for_rep');
'In When others (Update_sc_for_rep). lead_id: '
|| p_lead_id || ' Exception SQlerr is : ' ||
substr(SQLERRM, 1, 1950));
End Update_sc_for_rep;
PROCEDURE Update_sc_for_rep_line (
p_lead_id IN NUMBER,
p_lead_line_id IN NUMBER,
p_sf_id IN NUMBER,
p_sg_id IN NUMBER,
p_credit_type_id IN NUMBER,
p_identity_sf_id IN NUMBER,
p_debug_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(256) :=
'asn.plsql.asn_mig_sales_credits_pvt.Update_sc_for_rep';
SELECT * FROM as_sales_credits
WHERE lead_id = p_lead_id
AND lead_line_id = p_lead_line_id
AND credit_type_id = p_credit_type_id
AND nvl(salesforce_id, -37) = nvl(p_sf_id, -37)
AND nvl(salesgroup_id, -37) = nvl(p_sg_id, -37)
ORDER BY lead_line_id;
l_sc_select_rec c_rep_quota_credits%ROWTYPE;
l_sc_next_select_rec c_rep_quota_credits%ROWTYPE;
SELECT *
FROM as_lead_lines
WHERE lead_line_id = p_lead_line_id AND total_amount <> p_sc_amount;
'Begin Update_sc_for_rep');
l_ll_tbl.DELETE;
FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
l_sc_select_rec := l_sc_next_select_rec;
FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
l_sc_select_rec.credit_amount;
(l_sc_select_rec.lead_line_id
<> l_sc_next_select_rec.lead_line_id) THEN
l_sc_tbl.DELETE;
l_sc_tbl(1).last_update_date := l_sc_select_rec.last_update_date;
l_sc_tbl(1).last_updated_by := FND_GLOBAL.user_id;
l_sc_tbl(1).creation_Date := l_sc_select_rec.creation_Date;
l_sc_tbl(1).last_update_login := FND_GLOBAL.conc_login_id;
l_sc_tbl(1).program_update_date := sysdate;
l_sc_tbl(1).sales_credit_id := l_sc_select_rec.sales_credit_id;
l_sc_tbl(1).original_sales_credit_id := l_sc_select_rec.original_sales_credit_id;
l_sc_tbl(1).lead_id := l_sc_select_rec.lead_id;
l_sc_tbl(1).lead_line_id := l_sc_select_rec.lead_line_id;
l_sc_tbl(1).salesforce_id := l_sc_select_rec.salesforce_id;
l_sc_tbl(1).person_id := l_sc_select_rec.person_id;
l_sc_tbl(1).salesgroup_id := l_sc_select_rec.salesgroup_id;
l_sc_tbl(1).partner_customer_id := l_sc_select_rec.partner_customer_id;
l_sc_tbl(1).partner_address_id := l_sc_select_rec.partner_address_id;
l_sc_tbl(1).revenue_amount := l_sc_select_rec.revenue_amount;
l_sc_tbl(1).revenue_percent := l_sc_select_rec.revenue_percent;
l_sc_tbl(1).quota_credit_amount := l_sc_select_rec.quota_credit_amount;
l_sc_tbl(1).quota_credit_percent := l_sc_select_rec.quota_credit_percent;
l_sc_tbl(1).MANAGER_REVIEW_FLAG := l_sc_select_rec.MANAGER_REVIEW_FLAG;
l_sc_tbl(1).MANAGER_REVIEW_DATE := l_sc_select_rec.MANAGER_REVIEW_DATE;
l_sc_tbl(1).credit_type_id := l_sc_select_rec.credit_type_id;
l_sc_tbl(1).attribute_category := l_sc_select_rec.attribute_category;
l_sc_tbl(1).attribute1 := l_sc_select_rec.attribute1;
l_sc_tbl(1).attribute2 := l_sc_select_rec.attribute2;
l_sc_tbl(1).attribute3 := l_sc_select_rec.attribute3;
l_sc_tbl(1).attribute4 := l_sc_select_rec.attribute4;
l_sc_tbl(1).attribute5 := l_sc_select_rec.attribute5;
l_sc_tbl(1).attribute6 := l_sc_select_rec.attribute6;
l_sc_tbl(1).attribute7 := l_sc_select_rec.attribute7;
l_sc_tbl(1).attribute8 := l_sc_select_rec.attribute8;
l_sc_tbl(1).attribute9 := l_sc_select_rec.attribute9;
l_sc_tbl(1).attribute10 := l_sc_select_rec.attribute10;
l_sc_tbl(1).attribute11 := l_sc_select_rec.attribute11;
l_sc_tbl(1).attribute12 := l_sc_select_rec.attribute12;
l_sc_tbl(1).attribute13 := l_sc_select_rec.attribute13;
l_sc_tbl(1).attribute14 := l_sc_select_rec.attribute14;
l_sc_tbl(1).attribute15 := l_sc_select_rec.attribute15;
FOR ll_select_rec IN
c_lead_line(l_sc_tbl(1).lead_line_id, l_sc_amount)
LOOP
l_ll_tbl_count := l_ll_tbl_count + 1;
l_ll_tbl(l_ll_tbl_count).last_update_date := ll_select_rec.last_update_date;
l_ll_tbl(l_ll_tbl_count).last_updated_by := FND_GLOBAL.user_id;
l_ll_tbl(l_ll_tbl_count).creation_Date := ll_select_rec.creation_Date;
l_ll_tbl(l_ll_tbl_count).created_by := ll_select_rec.created_by;
l_ll_tbl(l_ll_tbl_count).last_update_login := FND_GLOBAL.conc_login_id;
l_ll_tbl(l_ll_tbl_count).program_update_date := sysdate;
l_ll_tbl(l_ll_tbl_count).lead_id := ll_select_rec.lead_id;
l_ll_tbl(l_ll_tbl_count).lead_line_id := ll_select_rec.lead_line_id;
l_ll_tbl(l_ll_tbl_count).original_lead_line_id := ll_select_rec.original_lead_line_id;
l_ll_tbl(l_ll_tbl_count).interest_type_id := ll_select_rec.interest_type_id;
l_ll_tbl(l_ll_tbl_count).interest_status_code := ll_select_rec.interest_status_code;
l_ll_tbl(l_ll_tbl_count).primary_interest_code_id := ll_select_rec.primary_interest_code_id;
l_ll_tbl(l_ll_tbl_count).secondary_interest_code_id := ll_select_rec.secondary_interest_code_id;
l_ll_tbl(l_ll_tbl_count).inventory_item_id := ll_select_rec.inventory_item_id;
l_ll_tbl(l_ll_tbl_count).organization_id := ll_select_rec.organization_id;
l_ll_tbl(l_ll_tbl_count).uom_code := ll_select_rec.uom_code;
l_ll_tbl(l_ll_tbl_count).quantity := ll_select_rec.quantity;
l_ll_tbl(l_ll_tbl_count).ship_date := ll_select_rec.ship_date;
l_ll_tbl(l_ll_tbl_count).sales_stage_id := ll_select_rec.sales_stage_id;
l_ll_tbl(l_ll_tbl_count).win_probability := ll_select_rec.win_probability;
l_ll_tbl(l_ll_tbl_count).status_code := ll_select_rec.status_code;
l_ll_tbl(l_ll_tbl_count).decision_date := ll_select_rec.decision_date;
l_ll_tbl(l_ll_tbl_count).channel_code := ll_select_rec.channel_code;
l_ll_tbl(l_ll_tbl_count).price := ll_select_rec.price;
l_ll_tbl(l_ll_tbl_count).price_volume_margin := ll_select_rec.price_volume_margin;
l_ll_tbl(l_ll_tbl_count).quoted_line_flag := ll_select_rec.quoted_line_flag;
l_ll_tbl(l_ll_tbl_count).Source_Promotion_Id := ll_select_rec.Source_Promotion_Id;
l_ll_tbl(l_ll_tbl_count).forecast_date := ll_select_rec.forecast_date;
l_ll_tbl(l_ll_tbl_count).rolling_forecast_flag := ll_select_rec.rolling_forecast_flag;
l_ll_tbl(l_ll_tbl_count).Offer_Id := ll_select_rec.Offer_Id;
l_ll_tbl(l_ll_tbl_count).ORG_ID := ll_select_rec.ORG_ID;
l_ll_tbl(l_ll_tbl_count).product_category_id := ll_select_rec.product_category_id;
l_ll_tbl(l_ll_tbl_count).product_cat_set_id := ll_select_rec.product_cat_set_id;
l_ll_tbl(l_ll_tbl_count).attribute_category := ll_select_rec.attribute_category;
l_ll_tbl(l_ll_tbl_count).attribute1 := ll_select_rec.attribute1;
l_ll_tbl(l_ll_tbl_count).attribute2 := ll_select_rec.attribute2;
l_ll_tbl(l_ll_tbl_count).attribute3 := ll_select_rec.attribute3;
l_ll_tbl(l_ll_tbl_count).attribute4 := ll_select_rec.attribute4;
l_ll_tbl(l_ll_tbl_count).attribute5 := ll_select_rec.attribute5;
l_ll_tbl(l_ll_tbl_count).attribute6 := ll_select_rec.attribute6;
l_ll_tbl(l_ll_tbl_count).attribute7 := ll_select_rec.attribute7;
l_ll_tbl(l_ll_tbl_count).attribute8 := ll_select_rec.attribute8;
l_ll_tbl(l_ll_tbl_count).attribute9 := ll_select_rec.attribute9;
l_ll_tbl(l_ll_tbl_count).attribute10 := ll_select_rec.attribute10;
l_ll_tbl(l_ll_tbl_count).attribute11 := ll_select_rec.attribute11;
l_ll_tbl(l_ll_tbl_count).attribute12 := ll_select_rec.attribute12;
l_ll_tbl(l_ll_tbl_count).attribute13 := ll_select_rec.attribute13;
l_ll_tbl(l_ll_tbl_count).attribute14 := ll_select_rec.attribute14;
l_ll_tbl(l_ll_tbl_count).attribute15 := ll_select_rec.attribute15;
AS_OPPORTUNITY_PUB.Update_Opp_Lines(
p_api_version_number => 2.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => 90,
p_identity_salesforce_id => p_identity_sf_id,
p_line_tbl => l_ll_tbl,
p_header_rec => l_header_rec,
p_check_access_flag => 'N',
p_admin_flag => 'N',
p_admin_group_id => NULL,
p_partner_cont_party_id => NULL,
x_line_out_tbl => l_ll_out_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'End Update_sc_for_rep');
'In When others (Update_sc_for_rep). lead_id: '
|| p_lead_id || ' Exception SQlerr is : ' ||
substr(SQLERRM, 1, 1950));
End Update_sc_for_rep_line;
l_insert BOOLEAN;
SELECT customer_id
FROM AS_LEADS_ALL
WHERE lead_id = c_lead_id;
SELECT *
FROM AS_LEAD_LINES_ALL
WHERE lead_id = c_lead_id
AND lead_line_id = c_lead_line_id;
SELECT *
FROM AS_SALES_CREDITS
WHERE lead_id = c_lead_id
AND lead_line_id = c_lead_line_id
AND ( salesforce_id = c_salesforce_id and salesgroup_id = c_salesgroup_id and credit_type_id = p_forecast_credit_type_id )
AND rowNum < 2
UNION
SELECT *
FROM AS_SALES_CREDITS
WHERE lead_id = c_lead_id
AND lead_line_id = c_lead_line_id
AND credit_type_id <> p_forecast_credit_type_id ;
SELECT *
FROM AS_LEAD_COMP_PRODUCTS
WHERE lead_line_id = c_lead_line_id;
SELECT *
FROM AS_LEAD_DECISION_FACTORS
WHERE lead_line_id = c_lead_line_id;
AS_LEAD_LINES_PKG.Insert_Row(
px_LEAD_LINE_ID => l_LEAD_LINE_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => lr.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => lr.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => lr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => lr.PROGRAM_UPDATE_DATE,
p_LEAD_ID => p_lead_id,
p_INTEREST_TYPE_ID => lr.INTEREST_TYPE_ID,
p_PRIMARY_INTEREST_CODE_ID => lr.PRIMARY_INTEREST_CODE_ID,
p_SECONDARY_INTEREST_CODE_ID => lr.SECONDARY_INTEREST_CODE_ID,
p_INTEREST_STATUS_CODE => lr.INTEREST_STATUS_CODE,
p_INVENTORY_ITEM_ID => lr.INVENTORY_ITEM_ID,
p_ORGANIZATION_ID => lr.ORGANIZATION_ID,
p_UOM_CODE => lr.UOM_CODE,
p_QUANTITY => lr.QUANTITY,
p_TOTAL_AMOUNT => p_sales_credit_amount,
p_SALES_STAGE_ID => lr.SALES_STAGE_ID,
p_WIN_PROBABILITY => lr.WIN_PROBABILITY,
p_DECISION_DATE => lr.DECISION_DATE,
p_ORG_ID => lr.ORG_ID,
p_ATTRIBUTE_CATEGORY => lr.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => lr.ATTRIBUTE1,
p_ATTRIBUTE2 => lr.ATTRIBUTE2,
p_ATTRIBUTE3 => lr.ATTRIBUTE3,
p_ATTRIBUTE4 => lr.ATTRIBUTE4,
p_ATTRIBUTE5 => lr.ATTRIBUTE5,
p_ATTRIBUTE6 => lr.ATTRIBUTE6,
p_ATTRIBUTE7 => lr.ATTRIBUTE7,
p_ATTRIBUTE8 => lr.ATTRIBUTE8,
p_ATTRIBUTE9 => lr.ATTRIBUTE9,
p_ATTRIBUTE10 => lr.ATTRIBUTE10,
p_ATTRIBUTE11 => lr.ATTRIBUTE11,
p_ATTRIBUTE12 => lr.ATTRIBUTE12,
p_ATTRIBUTE13 => lr.ATTRIBUTE13,
p_ATTRIBUTE14 => lr.ATTRIBUTE14,
p_ATTRIBUTE15 => lr.ATTRIBUTE15,
p_STATUS_CODE => lr.STATUS_CODE,
p_CHANNEL_CODE => lr.CHANNEL_CODE,
p_QUOTED_LINE_FLAG => lr.QUOTED_LINE_FLAG,
p_PRICE => lr.PRICE,
p_PRICE_VOLUME_MARGIN => lr.PRICE_VOLUME_MARGIN,
p_SHIP_DATE => lr.SHIP_DATE,
p_FORECAST_DATE => lr.FORECAST_DATE,
p_ROLLING_FORECAST_FLAG => lr.ROLLING_FORECAST_FLAG,
p_SOURCE_PROMOTION_ID => lr.SOURCE_PROMOTION_ID,
p_OFFER_ID => lr.OFFER_ID,
p_PRODUCT_CATEGORY_ID => lr.PRODUCT_CATEGORY_ID,
p_PRODUCT_CAT_SET_ID => lr.PRODUCT_CAT_SET_ID);
'Private API: as_lead_lines_pkg.insert_row fail');
' Private API: as_lead_lines_pkg.insert_row '|| l_lead_line_id);
l_new_sales_credit_amount, 'ON-INSERT',
l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
l_sales_credit_rec.OPP_FORECAST_AMOUNT,
l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT);
AS_SALES_CREDITS_PKG.Insert_Row(
px_SALES_CREDIT_ID => l_SALES_CREDIT_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => scr.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => scr.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => scr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => scr.PROGRAM_UPDATE_DATE,
p_LEAD_ID => P_LEAD_ID,
p_LEAD_LINE_ID => l_LEAD_LINE_ID,
p_SALESFORCE_ID => scr.SALESFORCE_ID,
p_PERSON_ID => scr.PERSON_ID,
p_SALESGROUP_ID => scr.SALESGROUP_ID,
p_PARTNER_CUSTOMER_ID => scr.PARTNER_CUSTOMER_ID,
p_PARTNER_ADDRESS_ID => scr.PARTNER_ADDRESS_ID,
p_REVENUE_AMOUNT => scr.REVENUE_AMOUNT,
p_REVENUE_PERCENT => scr.REVENUE_PERCENT,
p_QUOTA_CREDIT_AMOUNT => scr.QUOTA_CREDIT_AMOUNT,
p_QUOTA_CREDIT_PERCENT => scr.QUOTA_CREDIT_PERCENT,
p_ATTRIBUTE_CATEGORY => scr.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => scr.ATTRIBUTE1,
p_ATTRIBUTE2 => scr.ATTRIBUTE2,
p_ATTRIBUTE3 => scr.ATTRIBUTE3,
p_ATTRIBUTE4 => scr.ATTRIBUTE4,
p_ATTRIBUTE5 => scr.ATTRIBUTE5,
p_ATTRIBUTE6 => scr.ATTRIBUTE6,
p_ATTRIBUTE7 => scr.ATTRIBUTE7,
p_ATTRIBUTE8 => scr.ATTRIBUTE8,
p_ATTRIBUTE9 => scr.ATTRIBUTE9,
p_ATTRIBUTE10 => scr.ATTRIBUTE10,
p_ATTRIBUTE11 => scr.ATTRIBUTE11,
p_ATTRIBUTE12 => scr.ATTRIBUTE12,
p_ATTRIBUTE13 => scr.ATTRIBUTE13,
p_ATTRIBUTE14 => scr.ATTRIBUTE14,
p_ATTRIBUTE15 => scr.ATTRIBUTE15,
p_MANAGER_REVIEW_FLAG => scr.MANAGER_REVIEW_FLAG,
p_MANAGER_REVIEW_DATE => scr.MANAGER_REVIEW_DATE,
p_ORIGINAL_SALES_CREDIT_ID => scr.ORIGINAL_SALES_CREDIT_ID,
p_CREDIT_PERCENT => 100,
p_CREDIT_AMOUNT => l_new_sales_credit_amount,
p_CREDIT_TYPE_ID => scr.CREDIT_TYPE_ID,
-- The following fields are not passed before ASNB
p_OPP_WORST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
p_OPP_FORECAST_AMOUNT => l_sales_credit_rec.OPP_FORECAST_AMOUNT,
p_OPP_BEST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT,
P_DEFAULTED_FROM_OWNER_FLAG =>scr.DEFAULTED_FROM_OWNER_FLAG -- Added for ASNB
);
'Private API: as_sales_credits_pkg.insert_row fail');
'Private API: as_sales_credits_pkg.insert_row '|| l_sales_credit_id);
AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row(
p_ATTRIBUTE15 => cpdr.ATTRIBUTE15,
p_ATTRIBUTE14 => cpdr.ATTRIBUTE14,
p_ATTRIBUTE13 => cpdr.ATTRIBUTE13,
p_ATTRIBUTE12 => cpdr.ATTRIBUTE12,
p_ATTRIBUTE11 => cpdr.ATTRIBUTE11,
p_ATTRIBUTE10 => cpdr.ATTRIBUTE10,
p_ATTRIBUTE9 => cpdr.ATTRIBUTE9,
p_ATTRIBUTE8 => cpdr.ATTRIBUTE8,
p_ATTRIBUTE7 => cpdr.ATTRIBUTE7,
p_ATTRIBUTE6 => cpdr.ATTRIBUTE6,
p_ATTRIBUTE4 => cpdr.ATTRIBUTE4,
p_ATTRIBUTE5 => cpdr.ATTRIBUTE5,
p_ATTRIBUTE2 => cpdr.ATTRIBUTE2,
p_ATTRIBUTE3 => cpdr.ATTRIBUTE3,
p_ATTRIBUTE1 => cpdr.ATTRIBUTE1,
p_ATTRIBUTE_CATEGORY => cpdr.ATTRIBUTE_CATEGORY,
p_PROGRAM_ID => cpdr.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => cpdr.PROGRAM_UPDATE_DATE,
p_PROGRAM_APPLICATION_ID => cpdr.PROGRAM_APPLICATION_ID,
p_REQUEST_ID => cpdr.REQUEST_ID,
p_WIN_LOSS_STATUS => cpdr.WIN_LOSS_STATUS,
p_COMPETITOR_PRODUCT_ID => cpdr.COMPETITOR_PRODUCT_ID,
p_LEAD_LINE_ID => l_LEAD_LINE_ID,
p_LEAD_ID => P_LEAD_ID,
px_LEAD_COMPETITOR_PROD_ID => l_LEAD_COMPETITOR_PROD_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE);
'Private API: as_lead_comp_products_pkg.insert_row fail');
'Private API: as_lead_comp_products_pkg.insert_row '|| l_lead_competitor_prod_id);
AS_LEAD_DECISION_FACTORS_PKG.Insert_Row(
p_ATTRIBUTE15 => dfcr.ATTRIBUTE15,
p_ATTRIBUTE14 => dfcr.ATTRIBUTE14,
p_ATTRIBUTE13 => dfcr.ATTRIBUTE13,
p_ATTRIBUTE12 => dfcr.ATTRIBUTE12,
p_ATTRIBUTE11 => dfcr.ATTRIBUTE11,
p_ATTRIBUTE10 => dfcr.ATTRIBUTE10,
p_ATTRIBUTE9 => dfcr.ATTRIBUTE9,
p_ATTRIBUTE8 => dfcr.ATTRIBUTE8,
p_ATTRIBUTE7 => dfcr.ATTRIBUTE7,
p_ATTRIBUTE6 => dfcr.ATTRIBUTE6,
p_ATTRIBUTE5 => dfcr.ATTRIBUTE5,
p_ATTRIBUTE4 => dfcr.ATTRIBUTE4,
p_ATTRIBUTE3 => dfcr.ATTRIBUTE3,
p_ATTRIBUTE2 => dfcr.ATTRIBUTE2,
p_ATTRIBUTE1 => dfcr.ATTRIBUTE1,
p_ATTRIBUTE_CATEGORY => dfcr.ATTRIBUTE_CATEGORY,
p_PROGRAM_UPDATE_DATE => dfcr.PROGRAM_UPDATE_DATE,
p_PROGRAM_ID => dfcr.PROGRAM_ID,
p_PROGRAM_APPLICATION_ID => dfcr.PROGRAM_APPLICATION_ID,
p_REQUEST_ID => dfcr.REQUEST_ID,
p_DECISION_RANK => dfcr.DECISION_RANK,
p_DECISION_PRIORITY_CODE => dfcr.DECISION_PRIORITY_CODE,
p_DECISION_FACTOR_CODE => dfcr.DECISION_FACTOR_CODE,
px_LEAD_DECISION_FACTOR_ID => l_LEAD_DECISION_FACTOR_ID,
p_LEAD_LINE_ID => l_LEAD_LINE_ID,
p_CREATE_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_CREATION_DATE => SYSDATE);
'Private API: as_lead_decision_factors_pkg.insert_row fail');
'Private API: as_lead_decision_factors_pkg.insert_row '|| l_lead_decision_factor_id );
CURSOR c1 IS SELECT as_leads_s.nextval FROM dual;
v_deleted_sfids NUMBER_TT;
v_deleted_sgids NUMBER_TT;
SELECT distinct lead_id
FROM as_sales_credits
WHERE lead_id BETWEEN p_start_id AND p_end_id;
SELECT lead_id ,lead_line_id from
(SELECT lead_id,lead_line_id,count(1) numofsalescredit ,
SUM(decode(partner_customer_id, NULL, 0, 1)) isPartnerCredit
FROM as_sales_credits
WHERE lead_id = p_lead_id
AND credit_type_id = p_credit_type_id
GROUP BY lead_id, lead_line_id
) inlinetab
where isPartnerCredit > 0 or numofsalescredit> 1 order by lead_line_id asc;
SELECT lead.description, lead.customer_id, lead.address_id, lead.owner_salesforce_id,
lead.owner_sales_group_id, lead.status ,lead.win_probability, status.win_loss_indicator,
status.forecast_rollup_flag ,status.OPP_OPEN_STATUS_FLAG
FROM as_leads_all lead, as_statuses_vl status
WHERE lead_id = p_lead_id
AND lead.status = status.status_code(+);
SELECT access_id, salesforce_id, sales_group_id, owner_flag
FROM as_accesses_all
WHERE lead_id = p_lead_id AND partner_customer_id IS NULL
AND partner_cont_party_id IS NULL
ORDER BY nvl(owner_flag, 'N') DESC,
nvl(team_leader_flag, 'N') DESC,
nvl(freeze_flag, 'N') DESC;
SELECT employee_person_id FROM as_salesforce_v
WHERE salesforce_id = p_salesforce_id;
SELECT salesforce_id FROM as_sales_credits
WHERE lead_id = p_lead_id AND credit_type_id = p_credit_type_id
AND partner_customer_id IS NOT NULL;
SELECT salesforce_id, salesgroup_id ,sum(credit_amount) credit_amount
FROM as_sales_credits
WHERE lead_id = p_lead_id
AND lead_line_id = p_lead_line_id
AND credit_type_id = p_credit_type_id
and exists (select 'x'
FROM as_sales_credits
WHERE lead_id = p_lead_id
AND lead_line_id = p_lead_line_id
AND credit_type_id = p_credit_type_id
GROUP BY lead_id, lead_line_id
HAVING count(*) > 1)
GROUP BY salesforce_id, salesgroup_id ;
SELECT salesforce_id, sales_group_id, employee_person_id, opp_open_status_flag
FROM as_sales_credits_denorm
WHERE lead_id = p_lead_id AND credit_type_id = p_credit_type_id
AND partner_customer_id IS NULL;
SELECT sc.lead_line_id, sum(sc.credit_percent) total_percent,
sum(sc.credit_amount) total_credit,
max(ll.total_amount) line_amount
FROM as_sales_credits sc, as_lead_lines ll
WHERE sc.lead_id = p_lead_id
AND sc.credit_type_id = p_credit_type_id
AND ll.lead_line_id(+) = sc.lead_line_id
GROUP BY sc.lead_line_id
HAVING (sum(sc.credit_percent) <> 100
OR sum(sc.credit_amount) <> max(ll.total_amount));
SELECT * FROM as_sales_lead_opportunity
WHERE opportunity_id = p_lead_id;
SELECT AS_ACCESSES_S.NEXTVAL
FROM SYS.DUAL;
SELECT DISTINCT opps.lead_id, opps.customer_id, opps.address_id,
ascr.salesforce_id, ascr.person_id,
ascr.SALESGROUP_ID
FROM as_leads_all opps, as_sales_credits ascr
WHERE opps.lead_id = ascr.lead_id
AND opps.lead_id = p_lead_id
--AND ascr.credit_type_id = p_credit_type_id --- both quota and non-revenue credit receivers should be in the sales team
AND NOT EXISTS (
SELECT 'x'
FROM as_accesses_all acc
WHERE opps.lead_id = acc.lead_id
AND acc.SALESFORCE_ID = ascr.SALESFORCE_ID
and NVL(acc.SALES_GROUP_ID,-99) = NVL(ascr.SALESGROUP_ID,-99));
FND_PROFILE.PUT('AS_ALLOW_UPDATE_FROZEN_OPP', 'Y');
update as_lead_lines_all
set forecast_date = NULL, rolling_forecast_flag = 'N' ,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
where lead_id = l_lead_id(i) and rolling_forecast_flag = 'Y';
update as_sales_Credits ascr
set CREDIT_PERCENT = 100 ,
CREDIT_AMOUNT = (select total_amount
from as_lead_lines oppl
where oppl.lead_id = ascr.lead_id
and oppl.lead_line_id = ascr.lead_line_id ),
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
where ascr.lead_id = l_lead_id(i) and NVL(CREDIT_PERCENT,0) <> 100
and CREDIT_TYPE_ID in
( select SALES_CREDIT_TYPE_ID
from oe_sales_credit_types
where QUOTA_FLAG = 'N');
DELETE FROM as_sales_credits where sales_credit_id IN
(SELECT sales_credit_id
FROM as_sales_credits ascr,
(
SELECT lead_id,lead_line_id,
salesforce_id,salesgroup_id,
credit_type_id,
max(sales_credit_id) maxid
FROM as_sales_credits ascr1
WHERE ascr1.lead_id = l_lead_id(i)
AND ascr1.credit_type_id in
( SELECT sales_credit_type_id
FROM oe_sales_credit_types
WHERE quota_flag = 'N')
GROUP BY lead_id,lead_line_id,salesforce_id,salesgroup_id,credit_type_id
HAVING COUNT(sales_credit_id) > 1
) duplines
WHERE ascr.lead_id = duplines.lead_id
AND ascr.lead_line_id = duplines.lead_line_id
AND ascr.salesforce_id = duplines.salesforce_id
AND ascr.salesgroup_id = duplines.salesgroup_id
AND ascr.credit_type_id = duplines.credit_type_id
AND ascr.SALES_CREDIT_ID <> maxid);
UPDATE as_accesses_all acc
SET acc.team_leader_flag = 'Y',
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE acc.lead_id is not null
AND acc.lead_id = l_lead_id(i)
AND nvl(acc.team_leader_flag,'N') <> 'Y'
AND (
EXISTS
( SELECT 1
FROM as_sales_credits asc1
WHERE asc1.lead_id = acc.lead_id
AND asc1.salesforce_id = acc.salesforce_id
AND asc1.salesgroup_id = acc.sales_group_id )
OR acc.owner_flag = 'Y');
-- delete 0% quota credits
FORALL I IN l_lead_id.first..l_lead_id.last
DELETE FROM as_sales_credits
WHERE lead_id = l_lead_id(i)
AND credit_type_id = l_forecast_credit_type_id
AND NVL(CREDIT_PERCENT,0) = 0 ;
UPDATE as_accesses_all
SET owner_flag = 'N' ,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE lead_id = l_lead_id(j) AND owner_flag = 'Y';
UPDATE as_accesses_all
SET owner_flag = 'Y', team_leader_flag = 'Y',
freeze_flag = 'Y',
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE lead_id = l_lead_id(j) AND
salesforce_id = l_org_owner_sf_id AND
nvl(sales_group_id, -37) = nvl(l_org_owner_sg_id, -37);
INSERT INTO
as_accesses_all
(
access_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,reassign_flag
,team_leader_flag
,customer_id
,salesforce_id
,person_id
,partner_customer_id
,lead_id
,sales_group_id
,partner_cont_party_id
,owner_flag
,created_by_tap_flag
,open_flag
,freeze_flag
,org_id
,object_version_number
)
VALUES(
AS_ACCESSES_S.nextval
,sysdate
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,NULL
,'Y'
,l_customer_id(j)
,l_org_owner_sf_id
,l_org_owner_person_id
,NULL
,l_lead_id(j)
,l_org_owner_sg_id
,NULL
,'Y'
,'N'
,l_open_flag
,'Y'
,NULL
,1
);
UPDATE AS_ACCESSES_ALL
SET owner_flag = 'Y', team_leader_flag = 'Y',
freeze_flag = 'Y',
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE access_id = l_access_id;
UPDATE as_leads_all
SET owner_salesforce_id = l_org_owner_sf_id,
owner_sales_group_id = l_org_owner_sg_id,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE lead_id = l_lead_id(j);
UPDATE as_sales_credits
SET salesforce_id = l_org_owner_sf_id,
salesgroup_id = l_org_owner_sg_id,
person_id = l_org_owner_person_id,
partner_customer_id = NULL, partner_address_id = NULL,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE lead_id = l_lead_id(j)
AND credit_type_id = l_forecast_credit_type_id
AND partner_customer_id IS NOT NULL;
Update_sc_for_rep_line (l_lead_id(j),multicredit_opps_rec.lead_line_id, l_first_sf_id, l_first_sg_id,
l_forecast_credit_type_id, l_org_owner_sf_id,
p_debug_flag, l_return_status, l_msg_count, l_msg_data);
INSERT INTO AS_ACCESSES_ALL
(ACCESS_ID,
ACCESS_TYPE,
SALESFORCE_ID,
SALES_GROUP_ID,
PERSON_ID,
CUSTOMER_ID,
ADDRESS_ID,
LEAD_ID,
FREEZE_FLAG,
REASSIGN_FLAG,
TEAM_LEADER_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
object_version_number,
OPEN_FLAG)
VALUES
(l_access_pk_id,
'X',
opp_rec.salesforce_id,
opp_rec.salesgroup_id,
opp_rec.person_id,
opp_rec.customer_id,
opp_rec.address_id,
opp_rec.lead_id,
'Y',
'N',
'Y',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
SYSDATE,
1.0,
l_open_status_flag(j));
UPDATE as_sales_credits
SET salesforce_id = l_org_owner_sf_id,
salesgroup_id = l_org_owner_sg_id,
person_id = l_org_owner_person_id,
partner_customer_id = NULL, partner_address_id = NULL,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
WHERE lead_id = l_lead_id(j)
AND credit_type_id <> l_forecast_credit_type_id
AND partner_customer_id IS NOT NULL;
update as_sales_Credits ascr
set DEFAULTED_FROM_OWNER_FLAG = 'Y',
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.conc_login_id
where ascr.lead_id = l_lead_id(i)
and NVL(DEFAULTED_FROM_OWNER_FLAG,'N') <> 'Y'
and (SALESFORCE_ID ,SALESGROUP_ID) in
(SELECT owner_salesforce_id,owner_sales_group_id
FROM as_leads_all ala
WHERE ala.lead_id = l_lead_id(i) )
and credit_type_id = l_forecast_credit_type_id;
DELETE FROM as_sales_credits where sales_credit_id IN
(SELECT sales_credit_id
FROM as_sales_credits ascr,
(
SELECT lead_id,lead_line_id,
salesforce_id,salesgroup_id,
credit_type_id,
max(sales_credit_id) maxid
FROM as_sales_credits ascr1
WHERE ascr1.lead_id = l_lead_id(i)
AND ascr1.credit_type_id in
( SELECT sales_credit_type_id
FROM oe_sales_credit_types
WHERE quota_flag = 'N')
GROUP BY lead_id,lead_line_id,salesforce_id,salesgroup_id,credit_type_id
HAVING COUNT(sales_credit_id) > 1
) duplines
WHERE ascr.lead_id = duplines.lead_id
AND ascr.lead_line_id = duplines.lead_line_id
AND ascr.salesforce_id = duplines.salesforce_id
AND ascr.salesgroup_id = duplines.salesgroup_id
AND ascr.credit_type_id = duplines.credit_type_id
AND ascr.SALES_CREDIT_ID <> maxid);