[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cn_process_batches_s3.NEXTVAL
INTO x_physical_batch_id
FROM SYS.DUAL;
UPDATE cn_process_batches
SET status_code = 'VOID'
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE logical_batch_id = p_logical_batch_id;
UPDATE cn_process_batches
SET status_code = 'VOID'
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE physical_batch_id = p_physical_batch_id;
SELECT salesrep_id
, SUM(sales_lines_total) srp_trx_count
FROM cn_process_batches
WHERE logical_batch_id = p_logical_batch_id AND status_code = 'IN_USE'
GROUP BY salesrep_id
ORDER BY salesrep_id DESC;
UPDATE cn_process_batches
SET physical_batch_id = x_physical_batch_id
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE salesrep_id = logical_rec.salesrep_id
AND logical_batch_id = p_logical_batch_id
AND status_code = 'IN_USE';
PROCEDURE update_error(x_physical_batch_id NUMBER) IS
l_user_id NUMBER(15) := fnd_global.user_id;
UPDATE cn_process_batches
SET status_code = 'ERROR'
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE physical_batch_id = x_physical_batch_id;
END update_error;
SELECT org_id INTO l_org_id
FROM cn_process_batches
WHERE physical_batch_id = x_physical_batch_id AND ROWNUM = 1;
SELECT DISTINCT physical_batch_id
FROM cn_process_batches
WHERE logical_batch_id = x_logical_batch_id AND status_code = 'IN_USE';
update_error(l_temp_phys_batch_id);
/* The following Updates do a check for */
/* no prior adjustment if profile option set to 'Y'*/
/*****************************************/
IF (cn_system_parameters.VALUE('CN_PRIOR_ADJUSTMENT', p_org_id) = 'N') THEN
DECLARE
x_latest_processed_date DATE;
SELECT NVL(latest_processed_date, TO_DATE('01/01/1900', 'DD/MM/YYYY'))
INTO x_latest_processed_date
FROM cn_repositories_all
WHERE org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - PRIOR ADJUSTMENT'
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND processed_date < x_latest_processed_date; */
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - PRIOR ADJUSTMENT'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND processed_date < x_latest_processed_date
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - PRIOR ADJUSTMENT'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND processed_date < x_latest_processed_date
AND org_id = p_org_id;
/* The following Updates do a check for */
/* failures in the foreign key references*/
/*****************************************/
-- Commented this query to fix bug# 1772128
/*
UPDATE cn_comm_lines_api SET load_status = 'ERROR - TRX_TYPE'
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND NOT EXISTS
(SELECT 1 FROM cn_lookups WHERE lookup_type = 'TRX TYPES'
AND lookup_code =
cn_comm_lines_api.trx_type); */
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - TRX_TYPE'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING') --OR adjust_status IS NULL)
AND org_id = p_org_id
AND NOT EXISTS(
SELECT 1
FROM cn_lookups
WHERE lookup_type = 'TRX TYPES'
AND lookup_code = cn_comm_lines_api_all.trx_type);
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - TRX_TYPE'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND org_id = p_org_id
AND NOT EXISTS(
SELECT 1
FROM cn_lookups
WHERE lookup_type = 'TRX TYPES'
AND lookup_code = cn_comm_lines_api_all.trx_type);
UPDATE cn_comm_lines_api SET load_status = 'ERROR - REVENUE_CLASS'
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND revenue_class_id IS NOT NULL
AND NOT EXISTS
(SELECT 1 FROM cn_revenue_classes
WHERE cn_revenue_classes.revenue_class_id =
cn_comm_lines_api.revenue_class_id); */
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - REVENUE_CLASS'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')--OR adjust_status IS NULL)
AND revenue_class_id IS NOT NULL
AND org_id = p_org_id
AND NOT EXISTS(
SELECT 1
FROM cn_revenue_classes
WHERE cn_revenue_classes.revenue_class_id =
cn_comm_lines_api_all.revenue_class_id);
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - REVENUE_CLASS'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND revenue_class_id IS NOT NULL
AND org_id = p_org_id
AND NOT EXISTS(
SELECT 1
FROM cn_revenue_classes
WHERE cn_revenue_classes.revenue_class_id =
cn_comm_lines_api_all.revenue_class_id);
UPDATE cn_comm_lines_api
SET acctd_transaction_amount = transaction_amount,
exchange_rate = 1
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND ((acctd_transaction_amount IS NULL) OR
(acctd_transaction_amount = transaction_amount))
AND exchange_rate IS NULL
AND trx_type <> 'FORECAST'
AND transaction_currency_code IS NOT NULL
AND transaction_currency_code = FunctionalCurrency; */
UPDATE cn_comm_lines_api_all
SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND acctd_transaction_amount IS NULL
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND org_id = p_org_id
AND transaction_currency_code = functionalcurrency;
UPDATE cn_comm_lines_api_all
SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND acctd_transaction_amount IS NULL
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND org_id = p_org_id
AND transaction_currency_code = functionalcurrency;
UPDATE cn_comm_lines_api SET load_status = 'ERROR - NO EXCH RATE GIVEN'
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND transaction_currency_code IS NOT NULL
AND exchange_rate IS NULL; */
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - NO EXCH RATE GIVEN'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND transaction_currency_code IS NOT NULL
AND exchange_rate IS NULL
-- Added to fix the above problem.
AND acctd_transaction_amount IS NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - NO EXCH RATE GIVEN'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND transaction_currency_code IS NOT NULL
AND exchange_rate IS NULL
-- Added to fix the above problem.
AND acctd_transaction_amount IS NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api SET load_status = 'ERROR - INCORRECT CONV GIVEN'
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND transaction_currency_code IS NULL
AND exchange_rate IS NULL
AND acctd_transaction_amount IS NOT NULL
AND acctd_transaction_amount <> transaction_amount; */
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - INCORRECT CONV GIVEN'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND transaction_currency_code IS NULL
AND exchange_rate IS NULL
AND acctd_transaction_amount IS NOT NULL
AND acctd_transaction_amount <> transaction_amount
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - INCORRECT CONV GIVEN'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND transaction_currency_code IS NULL
AND exchange_rate IS NULL
AND acctd_transaction_amount IS NOT NULL
AND acctd_transaction_amount <> transaction_amount
AND org_id = p_org_id;
UPDATE cn_comm_lines_api
SET acctd_transaction_amount = (transaction_amount * exchange_rate)
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND acctd_transaction_amount IS NULL
AND exchange_rate IS NOT NULL
AND transaction_currency_code IS NOT NULL; */
UPDATE cn_comm_lines_api_all
SET acctd_transaction_amount =(transaction_amount * exchange_rate)
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND acctd_transaction_amount IS NULL
AND exchange_rate IS NOT NULL
AND transaction_currency_code IS NOT NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET acctd_transaction_amount =(transaction_amount * exchange_rate)
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND acctd_transaction_amount IS NULL
AND exchange_rate IS NOT NULL
AND transaction_currency_code IS NOT NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api SET acctd_transaction_amount = transaction_amount,
transaction_currency_code = FunctionalCurrency, exchange_rate = 1
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND acctd_transaction_amount IS NULL
AND exchange_rate IS NULL
AND transaction_currency_code IS NULL; */
UPDATE cn_comm_lines_api_all
SET acctd_transaction_amount = transaction_amount
, transaction_currency_code = functionalcurrency
, exchange_rate = 1
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND acctd_transaction_amount IS NULL
AND exchange_rate IS NULL
AND transaction_currency_code IS NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET acctd_transaction_amount = transaction_amount
, transaction_currency_code = functionalcurrency
, exchange_rate = 1
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND acctd_transaction_amount IS NULL
AND exchange_rate IS NULL
AND transaction_currency_code IS NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
WHERE load_status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND acctd_transaction_amount IS NULL; */
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND acctd_transaction_amount IS NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND acctd_transaction_amount IS NULL
AND org_id = p_org_id;
UPDATE cn_comm_lines_api SET load_status = 'SALESREP ERROR'
WHERE load_Status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST'
AND NOT EXISTS (SELECT 1 FROM cn_salesreps
WHERE employee_number =
cn_comm_lines_api.employee_number); */
UPDATE cn_comm_lines_api_all
SET load_status = 'SALESREP ERROR'
WHERE load_status = 'UNLOADED'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND org_id = p_org_id
AND NOT EXISTS(SELECT 1
FROM cn_salesreps
WHERE employee_number = cn_comm_lines_api_all.employee_number);
UPDATE cn_comm_lines_api_all
SET load_status = 'SALESREP ERROR'
WHERE load_status = 'UNLOADED'
AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND org_id = p_org_id
AND NOT EXISTS(SELECT 1
FROM cn_salesreps
WHERE employee_number = cn_comm_lines_api_all.employee_number);
UPDATE cn_comm_lines_api SET load_status = 'PERIOD ERROR'
WHERE load_Status = 'UNLOADED'
AND Trunc(processed_date) >= Trunc(p_start_date)
AND Trunc(processed_date) <= Trunc(p_end_date)
AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
AND trx_type <> 'FORECAST';*/
UPDATE cn_comm_lines_api_all
SET load_status = 'PERIOD ERROR'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all
SET load_status = 'PERIOD ERROR'
WHERE load_status = 'UNLOADED'
AND processed_date >= TRUNC(p_start_date)
AND processed_date <(TRUNC(p_end_date) + 1)
AND salesrep_id = p_salesrep_id
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND org_id = p_org_id;
UPDATE cn_comm_lines_api_all api
SET api.load_status = 'UNLOADED'
WHERE api.trx_type <> 'FORECAST'
AND api.load_status IN(
'ERROR - PRIOR ADJUSTMENT'
, 'ERROR - TRX_TYPE'
, 'ERROR - REVENUE_CLASS'
, 'ERROR - NO EXCH RATE GIVEN'
, 'ERROR - INCORRECT CONV GIVEN'
, 'ERROR - CANNOT CONV/DEFAULT'
, 'SALESREP ERROR'
, 'PERIOD ERROR'
)
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND api.org_id = p_org_id;
UPDATE /*+ index(api, cn_comm_lines_api_f2)*/ cn_comm_lines_api_all api
SET api.salesrep_id =
(SELECT cs1.salesrep_id
FROM cn_salesreps cs1
WHERE cs1.employee_number = api.employee_number
AND cs1.org_id = api.org_id -- vensrini
AND cs1.org_id = p_org_id) -- vensrini
WHERE api.salesrep_id IS NULL
AND api.load_status = 'UNLOADED'
AND api.trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND EXISTS(
SELECT /*+ NO_UNNEST */ employee_number
FROM cn_salesreps cs
WHERE api.employee_number = cs.employee_number
AND cs.org_id = api.org_id -- vensrini
AND cs.org_id = p_org_id) -- vensrini
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND api.org_id = p_org_id;
UPDATE cn_comm_lines_api_all api
SET employee_number =
(SELECT employee_number
FROM cn_salesreps cs1
WHERE cs1.salesrep_id = api.salesrep_id
AND cs1.org_id = api.org_id -- vensrini
AND cs1.org_id = p_org_id) -- vensrini
WHERE employee_number IS NULL
AND load_status = 'UNLOADED'
AND trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND EXISTS(
SELECT /*+ NO_UNNEST*/ salesrep_id
FROM cn_salesreps cs
WHERE api.salesrep_id = cs.salesrep_id
AND cs.org_id = api.org_id -- vensrini
AND cs.org_id = p_org_id) -- vensrini
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND org_id = p_org_id;
UPDATE /*+ index(api, cn_comm_lines_api_f2)*/ cn_comm_lines_api_all api
SET api.load_status = 'SALESREP ERROR'
WHERE api.load_status = 'UNLOADED'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND api.salesrep_id IS NULL
AND api.employee_number IS NULL
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND api.org_id = p_org_id;
SELECT COUNT(*) INTO l_open_period
FROM cn_period_statuses_all
WHERE period_status = 'O'
AND org_id = p_org_id
AND (period_set_id, period_type_id) =
(SELECT period_set_id, period_type_id
FROM cn_repositories_all
WHERE org_id = p_org_id)
AND l_end_date BETWEEN start_date AND end_date;
SELECT cn_process_batches_s2.NEXTVAL INTO l_logical_batch_id FROM dual;
INSERT INTO cn_process_batches(
process_batch_id
, logical_batch_id
, srp_period_id
, period_id
, end_period_id
, start_date
, end_date
, salesrep_id
, sales_lines_total
, status_code
, process_batch_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, org_id
)
( SELECT cn_process_batches_s1.NEXTVAL
, l_logical_batch_id
, 1 -- a dummy value for a not null column
, batch.period_id -- Start Period Id
, batch.period_id -- End Period Id
, batch.start_date
, batch.end_date
, batch.salesrep_id
, batch.trx_count
, 'IN_USE' -- Status Code
, 'CREATED_BY_LOADER' -- Process Batch Type
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, fnd_global.conc_request_id
, fnd_global.prog_appl_id
, fnd_global.conc_program_id
, SYSDATE
, p_org_id
FROM (
SELECT api.employee_number employee_number
, api.salesrep_id salesrep_id
, acc.period_id period_id
, acc.start_date start_date
, acc.end_date end_date
, COUNT(*) trx_count
FROM cn_comm_lines_api api, cn_acc_period_statuses_v acc
WHERE api.load_status = 'UNLOADED'
AND api.trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING' )-- OR adjust_status IS NULL)
AND api.processed_date >= TRUNC(l_start_date)
AND api.processed_date <(TRUNC(l_end_date) + 1)
AND ((p_salesrep_id IS NULL) OR(api.salesrep_id = p_salesrep_id))
AND api.salesrep_id IS NOT NULL
AND api.processed_date >= acc.start_date
AND api.processed_date <(acc.end_date + 1)
AND ( l_skip_credit_flag = 'Y'
OR (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y') )
GROUP BY api.employee_number, api.salesrep_id, acc.period_id, acc.start_date, acc.end_date
) batch );
SELECT salesrep_id
, period_id
, start_date
, end_date
, sales_lines_total trx_count
FROM cn_process_batches
WHERE physical_batch_id = p_physical_batch_id AND status_code = 'IN_USE';
SELECT cn_commission_headers_s.NEXTVAL
INTO l_init_commission_header_id
FROM DUAL;
INSERT INTO cn_commission_headers
(
commission_header_id
, direct_salesrep_id
, processed_date
, processed_period_id
, rollup_date
, transaction_amount
, quantity
, discount_percentage
, margin_percentage
, orig_currency_code
, transaction_amount_orig
, trx_type
, status
, pre_processed_code
, comm_lines_api_id
, source_doc_type
, source_trx_number
, quota_id
, srp_plan_assign_id
, revenue_class_id
, role_id
, comp_group_id
, commission_amount
, reversal_flag
, reversal_header_id
, reason_code
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute16
, attribute17
, attribute18
, attribute19
, attribute20
, attribute21
, attribute22
, attribute23
, attribute24
, attribute25
, attribute26
, attribute27
, attribute28
, attribute29
, attribute30
, attribute31
, attribute32
, attribute33
, attribute34
, attribute35
, attribute36
, attribute37
, attribute38
, attribute39
, attribute40
, attribute41
, attribute42
, attribute43
, attribute44
, attribute45
, attribute46
, attribute47
, attribute48
, attribute49
, attribute50
, attribute51
, attribute52
, attribute53
, attribute54
, attribute55
, attribute56
, attribute57
, attribute58
, attribute59
, attribute60
, attribute61
, attribute62
, attribute63
, attribute64
, attribute65
, attribute66
, attribute67
, attribute68
, attribute69
, attribute70
, attribute71
, attribute72
, attribute73
, attribute74
, attribute75
, attribute76
, attribute77
, attribute78
, attribute79
, attribute80
, attribute81
, attribute82
, attribute83
, attribute84
, attribute85
, attribute86
, attribute87
, attribute88
, attribute89
, attribute90
, attribute91
, attribute92
, attribute93
, attribute94
, attribute95
, attribute96
, attribute97
, attribute98
, attribute99
, attribute100
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, exchange_rate
, forecast_id
, upside_quantity
, upside_amount
, uom_code
, source_trx_id
, source_trx_line_id
, source_trx_sales_line_id
, negated_flag
, customer_id
, inventory_item_id
, order_number
, booked_date
, invoice_number
, invoice_date
, bill_to_address_id
, ship_to_address_id
, bill_to_contact_id
, ship_to_contact_id
, adj_comm_lines_api_id
, adjust_date
, adjusted_by
, revenue_type
, adjust_rollup_flag
, adjust_comments
, adjust_status
, line_number
, TYPE
, sales_channel
, split_pct
, split_status
, org_id
) -- vensrini transaction load fix
(SELECT cn_commission_headers_s.NEXTVAL
, batch.salesrep_id
, TRUNC(api.processed_date)
, batch.period_id
, TRUNC(api.rollup_date)
, api.acctd_transaction_amount
, api.quantity
, api.discount_percentage
, api.margin_percentage
, api.transaction_currency_code
, api.transaction_amount
, api.trx_type
, 'COL'
, NVL(api.pre_processed_code, 'CRPC')
, api.comm_lines_api_id
, api.source_doc_type
, api.source_trx_number
, api.quota_id
, api.srp_plan_assign_id
, api.revenue_class_id
, api.role_id
, api.comp_group_id
, api.commission_amount
, api.reversal_flag
, api.reversal_header_id
, api.reason_code
, api.attribute_category
, api.attribute1
, api.attribute2
, api.attribute3
, api.attribute4
, api.attribute5
, api.attribute6
, api.attribute7
, api.attribute8
, api.attribute9
, api.attribute10
, api.attribute11
, api.attribute12
, api.attribute13
, api.attribute14
, api.attribute15
, api.attribute16
, api.attribute17
, api.attribute18
, api.attribute19
, api.attribute20
, api.attribute21
, api.attribute22
, api.attribute23
, api.attribute24
, api.attribute25
, api.attribute26
, api.attribute27
, api.attribute28
, api.attribute29
, api.attribute30
, api.attribute31
, api.attribute32
, api.attribute33
, api.attribute34
, api.attribute35
, api.attribute36
, api.attribute37
, api.attribute38
, api.attribute39
, api.attribute40
, api.attribute41
, api.attribute42
, api.attribute43
, api.attribute44
, api.attribute45
, api.attribute46
, api.attribute47
, api.attribute48
, api.attribute49
, api.attribute50
, api.attribute51
, api.attribute52
, api.attribute53
, api.attribute54
, api.attribute55
, api.attribute56
, api.attribute57
, api.attribute58
, api.attribute59
, api.attribute60
, api.attribute61
, api.attribute62
, api.attribute63
, api.attribute64
, api.attribute65
, api.attribute66
, api.attribute67
, api.attribute68
, api.attribute69
, api.attribute70
, api.attribute71
, api.attribute72
, api.attribute73
, api.attribute74
, api.attribute75
, api.attribute76
, api.attribute77
, api.attribute78
, api.attribute79
, api.attribute80
, api.attribute81
, api.attribute82
, api.attribute83
, api.attribute84
, api.attribute85
, api.attribute86
, api.attribute87
, api.attribute88
, api.attribute89
, api.attribute90
, api.attribute91
, api.attribute92
, api.attribute93
, api.attribute94
, api.attribute95
, api.attribute96
, api.attribute97
, api.attribute98
, api.attribute99
, api.attribute100
, SYSDATE
, api.last_updated_by
, api.last_update_login
, SYSDATE
, api.created_by
, api.exchange_rate
, api.forecast_id
, api.upside_quantity
, api.upside_amount
, api.uom_code
, api.source_trx_id
, api.source_trx_line_id
, api.source_trx_sales_line_id
, api.negated_flag
, api.customer_id
, api.inventory_item_id
, api.order_number
, api.booked_date
, api.invoice_number
, api.invoice_date
, api.bill_to_address_id
, api.ship_to_address_id
, api.bill_to_contact_id
, api.ship_to_contact_id
, api.adj_comm_lines_api_id
, api.adjust_date
, api.adjusted_by
, api.revenue_type
, api.adjust_rollup_flag
, api.adjust_comments
, NVL(api.adjust_status,'NEW')
, api.line_number
, api.TYPE
, api.sales_channel
, api.split_pct
, api.split_status
, api.org_id -- vensrini transaction load fix
FROM cn_comm_lines_api api
WHERE api.load_status = 'UNLOADED'
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND api.trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND api.salesrep_id = batch.salesrep_id
AND api.processed_date >= TRUNC(batch.start_date)
AND api.processed_date <(TRUNC(batch.end_date) + 1)
AND NOT EXISTS(SELECT 'this transaction has already been loaded'
FROM cn_commission_headers_all cmh
WHERE cmh.comm_lines_api_id = api.comm_lines_api_id));
INSERT INTO cn_commission_headers
(
commission_header_id
, direct_salesrep_id
, processed_date
, processed_period_id
, rollup_date
, transaction_amount
, quantity
, discount_percentage
, margin_percentage
, orig_currency_code
, transaction_amount_orig
, trx_type
, status
, pre_processed_code
, comm_lines_api_id
, source_doc_type
, source_trx_number
, quota_id
, srp_plan_assign_id
, revenue_class_id
, role_id
, comp_group_id
, commission_amount
, reversal_flag
, reversal_header_id
, reason_code
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute16
, attribute17
, attribute18
, attribute19
, attribute20
, attribute21
, attribute22
, attribute23
, attribute24
, attribute25
, attribute26
, attribute27
, attribute28
, attribute29
, attribute30
, attribute31
, attribute32
, attribute33
, attribute34
, attribute35
, attribute36
, attribute37
, attribute38
, attribute39
, attribute40
, attribute41
, attribute42
, attribute43
, attribute44
, attribute45
, attribute46
, attribute47
, attribute48
, attribute49
, attribute50
, attribute51
, attribute52
, attribute53
, attribute54
, attribute55
, attribute56
, attribute57
, attribute58
, attribute59
, attribute60
, attribute61
, attribute62
, attribute63
, attribute64
, attribute65
, attribute66
, attribute67
, attribute68
, attribute69
, attribute70
, attribute71
, attribute72
, attribute73
, attribute74
, attribute75
, attribute76
, attribute77
, attribute78
, attribute79
, attribute80
, attribute81
, attribute82
, attribute83
, attribute84
, attribute85
, attribute86
, attribute87
, attribute88
, attribute89
, attribute90
, attribute91
, attribute92
, attribute93
, attribute94
, attribute95
, attribute96
, attribute97
, attribute98
, attribute99
, attribute100
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, exchange_rate
, forecast_id
, upside_quantity
, upside_amount
, uom_code
, source_trx_id
, source_trx_line_id
, source_trx_sales_line_id
, negated_flag
, customer_id
, inventory_item_id
, order_number
, booked_date
, invoice_number
, invoice_date
, bill_to_address_id
, ship_to_address_id
, bill_to_contact_id
, ship_to_contact_id
, adj_comm_lines_api_id
, adjust_date
, adjusted_by
, revenue_type
, adjust_rollup_flag
, adjust_comments
, adjust_status
, line_number
, TYPE
, sales_channel
, split_pct
, split_status
, org_id
) -- vensrini transaction load fix
(SELECT cn_commission_headers_s.NEXTVAL
, batch.salesrep_id
, TRUNC(api.processed_date)
, batch.period_id
, TRUNC(api.rollup_date)
, api.acctd_transaction_amount
, api.quantity
, api.discount_percentage
, api.margin_percentage
, api.transaction_currency_code
, api.transaction_amount
, api.trx_type
, 'COL'
, NVL(api.pre_processed_code, 'CRPC')
, api.comm_lines_api_id
, api.source_doc_type
, api.source_trx_number
, api.quota_id
, api.srp_plan_assign_id
, api.revenue_class_id
, api.role_id
, api.comp_group_id
, api.commission_amount
, api.reversal_flag
, api.reversal_header_id
, api.reason_code
, api.attribute_category
, api.attribute1
, api.attribute2
, api.attribute3
, api.attribute4
, api.attribute5
, api.attribute6
, api.attribute7
, api.attribute8
, api.attribute9
, api.attribute10
, api.attribute11
, api.attribute12
, api.attribute13
, api.attribute14
, api.attribute15
, api.attribute16
, api.attribute17
, api.attribute18
, api.attribute19
, api.attribute20
, api.attribute21
, api.attribute22
, api.attribute23
, api.attribute24
, api.attribute25
, api.attribute26
, api.attribute27
, api.attribute28
, api.attribute29
, api.attribute30
, api.attribute31
, api.attribute32
, api.attribute33
, api.attribute34
, api.attribute35
, api.attribute36
, api.attribute37
, api.attribute38
, api.attribute39
, api.attribute40
, api.attribute41
, api.attribute42
, api.attribute43
, api.attribute44
, api.attribute45
, api.attribute46
, api.attribute47
, api.attribute48
, api.attribute49
, api.attribute50
, api.attribute51
, api.attribute52
, api.attribute53
, api.attribute54
, api.attribute55
, api.attribute56
, api.attribute57
, api.attribute58
, api.attribute59
, api.attribute60
, api.attribute61
, api.attribute62
, api.attribute63
, api.attribute64
, api.attribute65
, api.attribute66
, api.attribute67
, api.attribute68
, api.attribute69
, api.attribute70
, api.attribute71
, api.attribute72
, api.attribute73
, api.attribute74
, api.attribute75
, api.attribute76
, api.attribute77
, api.attribute78
, api.attribute79
, api.attribute80
, api.attribute81
, api.attribute82
, api.attribute83
, api.attribute84
, api.attribute85
, api.attribute86
, api.attribute87
, api.attribute88
, api.attribute89
, api.attribute90
, api.attribute91
, api.attribute92
, api.attribute93
, api.attribute94
, api.attribute95
, api.attribute96
, api.attribute97
, api.attribute98
, api.attribute99
, api.attribute100
, SYSDATE
, api.last_updated_by
, api.last_update_login
, SYSDATE
, api.created_by
, api.exchange_rate
, api.forecast_id
, api.upside_quantity
, api.upside_amount
, api.uom_code
, api.source_trx_id
, api.source_trx_line_id
, api.source_trx_sales_line_id
, api.negated_flag
, api.customer_id
, api.inventory_item_id
, api.order_number
, api.booked_date
, api.invoice_number
, api.invoice_date
, api.bill_to_address_id
, api.ship_to_address_id
, api.bill_to_contact_id
, api.ship_to_contact_id
, api.adj_comm_lines_api_id
, api.adjust_date
, api.adjusted_by
, api.revenue_type
, api.adjust_rollup_flag
, api.adjust_comments
, NVL(api.adjust_status,'NEW')
, api.line_number
, api.TYPE
, api.sales_channel
, api.split_pct
, api.split_status
, api.org_id -- vensrini transaction load fix
FROM cn_comm_lines_api api
WHERE api.load_status = 'UNLOADED'
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND api.trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
AND api.salesrep_id = batch.salesrep_id
AND api.processed_date >= TRUNC(batch.start_date)
AND api.processed_date <(TRUNC(batch.end_date) + 1)
AND (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y')
AND NOT EXISTS(SELECT 'this transaction has already been loaded'
FROM cn_commission_headers_all cmh
WHERE cmh.comm_lines_api_id = api.comm_lines_api_id));
UPDATE cn_comm_lines_api api
SET load_Status = 'LOADED'
WHERE
api.load_status = 'UNLOADED' AND
Trunc(api.processed_date) >= Trunc(p_start_date) AND
Trunc(api.processed_date) <= Trunc(p_end_date) AND
((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
api.trx_type <> 'FORECAST' AND
api.salesrep_id = batch.salesrep_id AND
Trunc(api.processed_date) >= Trunc(batch.start_date) AND
Trunc(api.processed_date) <= Trunc(batch.end_date); */
UPDATE cn_comm_lines_api api
SET load_status = 'LOADED'
WHERE api.load_status = 'UNLOADED'
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND api.trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING' )-- OR adjust_status IS NULL)
AND api.salesrep_id = batch.salesrep_id
AND api.processed_date >= TRUNC(batch.start_date)
AND api.processed_date <(TRUNC(batch.end_date) + 1);
UPDATE cn_comm_lines_api api
SET load_status = 'LOADED'
WHERE api.load_status = 'UNLOADED'
AND api.processed_date >= TRUNC(p_start_date)
AND api.processed_date <(TRUNC(p_end_date) + 1)
AND api.trx_type <> 'FORECAST'
AND (adjust_status <> 'SCA_PENDING' )-- OR adjust_status IS NULL)
AND api.salesrep_id = batch.salesrep_id
AND api.processed_date >= TRUNC(batch.start_date)
AND api.processed_date <(TRUNC(batch.end_date) + 1)
AND (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y');
SELECT cch.commission_header_id
, cch.reversal_flag
, cch.reversal_header_id
FROM cn_commission_headers cch
, (SELECT DISTINCT salesrep_id
FROM cn_process_batches
WHERE physical_batch_id = p_physical_batch_id AND status_code = 'IN_USE') pb
WHERE cch.direct_salesrep_id = pb.salesrep_id
AND cch.commission_header_id > l_init_commission_header_id;