[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT l.*, s.name
FROM cn_comm_lines_api_all l,
cn_salesreps s
WHERE comm_lines_api_id = p_comm_lines_api_id
AND l.salesrep_id = s.salesrep_id;
SELECT h.*,s.employee_number,s.name
FROM cn_commission_headers_all h,
cn_salesreps s
WHERE comm_lines_api_id = p_comm_lines_api_id
AND h.direct_salesrep_id = s.salesrep_id;
SELECT user_name
INTO l_adjusted_by
FROM fnd_user
WHERE user_id = fnd_profile.value('USER_ID');
x_loading_status := 'CN_INSERTED';
x_adj_tbl(l_tbl_count).last_updated_by := NVL(l_adj_tbl(i).last_updated_by,0);
x_adj_tbl(l_tbl_count).last_update_login := NVL(l_adj_tbl(i).last_update_login,0);
x_adj_tbl(l_tbl_count).last_update_date := l_adj_tbl(i).last_update_date;
x_adj_tbl(l_tbl_count).program_update_date := l_adj_tbl(i).program_update_date;
SELECT l.*,s.employee_number srp_employee_number,s.name,
clad.meaning adjust_status_disp,
clt.meaning trx_type_disp
FROM cn_comm_lines_api l,
cn_salesreps s,
cn_lookups clad,
cn_lookups clt
WHERE l.trx_type = clt.lookup_code(+)
AND clt.lookup_type (+)= 'TRX TYPES'
AND l.adjust_status = clad.lookup_code(+)
AND clad.lookup_type (+)= 'ADJUST_STATUS'
AND l.comm_lines_api_id = p_comm_lines_api_id
AND l.salesrep_id = s.salesrep_id
AND (adjust_status NOT IN ('FROZEN','REVERSAL') OR
adjust_status IS NULL)
AND trx_type NOT IN ('ITD','GRP','THR');
SELECT h.*,s.employee_number,s.name,
clad.meaning adjust_status_disp,
clt.meaning trx_type_disp
FROM cn_commission_headers h,
cn_salesreps s,
cn_lookups clad,
cn_lookups clt
WHERE h.trx_type = clt.lookup_code(+)
AND clt.lookup_type (+)= 'TRX TYPES'
AND h.adjust_status = clad.lookup_code(+)
AND clad.lookup_type (+)= 'ADJUST_STATUS'
AND h.comm_lines_api_id = p_comm_lines_api_id
AND h.direct_salesrep_id = s.salesrep_id
AND (adjust_status NOT IN ('FROZEN','REVERSAL') OR
adjust_status IS NULL)
AND trx_type NOT IN ('ITD','GRP','THR');
x_loading_status := 'CN_INSERTED';
SELECT name
INTO x_adj_tbl(1).comp_group_name
FROM cn_comp_groups
WHERE comp_group_id = l_cg_id
AND rownum < 2;
SELECT name
INTO x_adj_tbl(1).revenue_class_name
FROM cn_revenue_classes
WHERE revenue_class_id = l_rc_id
AND rownum < 2;
SELECT name
INTO x_adj_tbl(1).quota_name
FROM cn_quotas
WHERE quota_id = l_quota_id
AND rownum < 2;
SELECT name
INTO x_adj_tbl(1).role_name
FROM cn_roles
WHERE role_id = l_role_id
AND rownum < 2;
SELECT substrb(PARTY.PARTY_NAME,1,50),
CUST_ACCT.ACCOUNT_NUMBER
INTO x_adj_tbl(1).customer_name,
x_adj_tbl(1).customer_number
FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = l_cust_id
AND rownum < 2;
PROCEDURE insert_api_record(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
p_action IN VARCHAR2 DEFAULT NULL,
p_newtx_rec IN adj_rec_type,
x_api_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2) IS
--
l_api_name CONSTANT VARCHAR2(30) := 'insert_api_record';
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER := fnd_global.user_id;
l_last_update_login NUMBER := fnd_global.login_id;
SAVEPOINT insert_api_record;
x_loading_status := 'CN_INSERTED';
SELECT count(1)
INTO l_period_count
FROM cn_acc_period_statuses_v
WHERE trunc(p_newtx_rec.processed_date)
BETWEEN start_date AND end_date
AND period_status IN ('O','F')
AND org_id = p_newtx_rec.org_id;
SELECT period_id
INTO l_processed_period_id
FROM cn_acc_period_statuses_v
WHERE trunc(p_newtx_rec.processed_date)
BETWEEN start_date AND end_date
AND period_status IN ('O','F')
AND org_id = p_newtx_rec.org_id;
SELECT cn_comm_lines_api_s.NEXTVAL
INTO l_comm_lines_api_id
FROM SYS.DUAL;
cn_comm_lines_api_pkg.insert_row(l_api_rec);
ROLLBACK TO insert_api_record;
ROLLBACK TO insert_api_record;
ROLLBACK TO insert_api_record;
PROCEDURE call_mass_update (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
p_org_id IN NUMBER := FND_API.G_MISS_NUM,
p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
p_pr_date_to IN DATE := FND_API.G_MISS_DATE,
p_pr_date_from IN DATE := FND_API.G_MISS_DATE,
p_calc_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_adj_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_load_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_invoice_num IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_order_num IN NUMBER := FND_API.G_MISS_NUM,
p_srch_attr_rec IN adj_rec_type,
p_mass_adj_type IN VARCHAR2 DEFAULT NULL,
p_adj_rec IN adj_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2) IS
-- Local Variables
l_api_name CONSTANT VARCHAR2(30) := 'call_mass_update';
SAVEPOINT call_mass_update;
x_loading_status := 'CN_INSERTED';
and delete the records from cn_invoice_changes table based on
these invoices */
cn_invoice_changes_pvt.update_mass_invoices(
p_api_version => l_api_version,
p_salesrep_id => p_salesrep_id,
p_pr_date_to => p_pr_date_to,
p_pr_date_from => p_pr_date_from,
p_calc_status => p_calc_status,
p_invoice_num => p_invoice_num,
p_order_num => p_order_num,
p_srch_attr_rec => p_srch_attr_rec,
p_to_salesrep_id => p_adj_rec.direct_salesrep_id,
p_to_salesrep_number => p_adj_rec.direct_salesrep_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
x_existing_data => l_existing_data);
cn_adjustments_pkg.mass_update_values(
x_adj_data => l_adj_tbl,
x_adj_rec => l_adj_rec,
X_mass_adj_type => p_mass_adj_type,
X_proc_comp => l_proc_comp);
cn_invoice_changes_pvt.update_credit_memo(
p_api_version => l_api_version,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_to_salesrep_id => p_adj_rec.direct_salesrep_id,
p_to_salesrep_number => p_adj_rec.direct_salesrep_number,
p_called_from => 'MASS',
p_adjust_status => 'MASSADJ',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
ROLLBACK TO call_mass_update;
ROLLBACK TO call_mass_update;
ROLLBACK TO call_mass_update;
x_loading_status := 'CN_INSERTED';
SELECT comm_lines_api_id
FROM cn_comm_lines_api_all api
WHERE api.order_number = l_order_number
AND api.org_id = l_org_id
AND api.trx_type = 'ORD'
AND api.load_status NOT IN ('LOADED', 'FILTERED') -- vensrini Bug fix 4202682
AND (api.adjust_status NOT IN ('FROZEN','REVERSAL','SCA_PENDING') OR
api.adjust_status IS NULL)
UNION ALL
SELECT comm_lines_api_id
FROM cn_commission_headers_all ch
WHERE ch.order_number = l_order_number
AND ch.org_id = l_org_id
AND ch.trx_type = 'ORD'
AND (ch.adjust_status NOT IN ('FROZEN','REVERSAL') OR
ch.adjust_status IS NULL);
x_loading_status := 'CN_INSERTED';
cn_invoice_changes_pvt.update_invoice_changes(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_exist_data_check => 'Y',
p_new_data_check => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
x_loading_status := 'CN_UPDATE_INV_ERROR';
/*update_credit_credentials(
p_comm_lines_api_id,
l_terr_id,
p_org_id,
p_adjusted_by
);*/
SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
FROM CN_COMM_LINES_API
WHERE
COMM_LINES_API_ID = p_comm_lines_api_id
AND ORG_ID = p_org_id;
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => o_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
cn_invoice_changes_pvt.update_invoice_changes(
p_api_version => p_api_version,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_exist_data_check => 'N',
p_new_data_check => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
x_loading_status := 'CN_UPDATE_INV_ERROR';
cn_invoice_changes_pvt.update_credit_memo(
p_api_version => p_api_version,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_called_from => 'SPLIT',
p_adjust_status => 'SPLIT',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_CM_ERROR');
x_loading_status := 'CN_UPDATE_CM_ERROR';
SELECT count(order_number)
INTO l_deal_count
FROM cn_commission_headers_all
WHERE order_number = l_order_number
AND revenue_type = 'REVENUE'
AND org_id = l_org_id;
SELECT count(order_number)
INTO l_deal_count
FROM cn_comm_lines_api_all
WHERE order_number = l_order_number
AND revenue_type = 'REVENUE'
AND org_id = l_org_id;
SELECT count(invoice_number)
INTO l_deal_count
FROM cn_commission_headers_all
WHERE invoice_number = p_invoice_number
AND revenue_type = 'REVENUE'
AND org_id = l_org_id;
SELECT count(invoice_number)
INTO l_deal_count
FROM cn_comm_lines_api_all
WHERE invoice_number = p_invoice_number
AND revenue_type = 'REVENUE'
AND org_id = l_org_id;
SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
FROM CN_COMM_LINES_API
WHERE
COMM_LINES_API_ID = order_rec.comm_lines_api_id
AND ORG_ID = p_org_id;
/*update_credit_credentials(
order_rec.comm_lines_api_id,
l_terr_id,
p_org_id,
p_adjusted_by
);*/
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => o_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
/*update_credit_credentials(
l_deal_data_tbl(i).comm_lines_api_id,
l_terr_id,
p_org_id,
p_adjusted_by
);*/
SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
FROM CN_COMM_LINES_API
WHERE
COMM_LINES_API_ID = l_deal_data_tbl(j).comm_lines_api_id
AND ORG_ID = p_org_id;
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => o_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
cn_invoice_changes_pvt.update_invoice_changes(
p_api_version => p_api_version,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_exist_data_check => 'N',
p_new_data_check => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
x_loading_status := 'CN_UPDATE_INV_ERROR';
l_deal_data_tbl.DELETE;
SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
FROM CN_COMM_LINES_API
WHERE
COMM_LINES_API_ID = l_deal_data_tbl(j).comm_lines_api_id
AND ORG_ID = p_org_id;
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => o_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
/*update_credit_credentials(
l_deal_data_tbl(j).comm_lines_api_id,
l_terr_id,
p_org_id,
p_adjusted_by
);*/
SELECT *
FROM cn_adj_detail_lines_v
WHERE commission_header_id = p_header_id;
x_loading_status := 'CN_INSERTED';
SELECT cch.adj_comm_lines_api_id,re.resource_name name,
s.salesrep_number employee_number,
cch.processed_date,clt.meaning,cch.order_number,cch.booked_date,
cch.invoice_number,cch.invoice_date,cch.quantity,
cch.transaction_amount,cch.transaction_amount_orig
FROM cn_commission_headers cch,
jtf_rs_resource_extns_vl re,
jtf_rs_salesreps s,
cn_lookups clt,
cn_period_statuses cpsp
WHERE cch.direct_salesrep_id = s.salesrep_id
AND s.resource_id = re.resource_id
AND cch.processed_period_id = cpsp.period_id
AND cch.trx_type = clt.lookup_code(+)
AND clt.lookup_type (+)= 'TRX TYPES'
AND cch.comm_lines_api_id = p_adj_comm_lines_api_id;
SELECT ccla.adj_comm_lines_api_id,re.resource_name name,
s.salesrep_number employee_number,
ccla.processed_date,clt.meaning,ccla.order_number,ccla.booked_date,
ccla.invoice_number,ccla.invoice_date,ccla.quantity,
ccla.acctd_transaction_amount,ccla.transaction_amount
FROM cn_comm_lines_api ccla,
jtf_rs_resource_extns_vl re,
jtf_rs_salesreps s,
cn_lookups clt,
cn_period_statuses cpsp
WHERE ccla.salesrep_id = s.salesrep_id
AND s.resource_id = re.resource_id
AND ccla.processed_period_id = cpsp.period_id
AND ccla.trx_type = clt.lookup_code(+)
AND clt.lookup_type (+)= 'TRX TYPES'
AND nvl(CCLA.load_status,'X') <> 'LOADED'
AND ccla.comm_lines_api_id = p_adj_comm_lines_api_id;
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_INSERTED';
PROCEDURE update_api_record(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
p_newtx_rec IN adj_rec_type,
x_api_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2) IS
--
l_api_name CONSTANT VARCHAR2(30) := 'update_api_record';
SAVEPOINT update_api_record;
x_loading_status := 'CN_INSERTED';
cn_invoice_changes_pvt.update_invoice_changes(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_exist_data_check => 'Y',
p_new_data_check => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
x_loading_status := 'CN_UPDATE_INV_ERROR';
/*update_credit_credentials(
p_newtx_rec.comm_lines_api_id,
p_newtx_rec.terr_id,
p_newtx_rec.org_id,
get_adjusted_by
);*/
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => o_newtx_rec,
x_api_id => x_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
cn_invoice_changes_pvt.update_invoice_changes(
p_api_version => p_api_version,
p_validation_level => p_validation_level,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_exist_data_check => 'N',
p_new_data_check => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
x_loading_status := 'CN_UPDATE_INV_ERROR';
cn_invoice_changes_pvt.update_credit_memo(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_called_from => 'UPDATE',
p_adjust_status => 'MANUAL',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
x_loading_status := 'CN_INSERTED';