[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_name
INTO l_adjusted_by
FROM fnd_user
WHERE user_id = fnd_profile.value('USER_ID');
SELECT org_id
INTO l_org_id
FROM cn_sca_process_batches
WHERE sca_process_batch_id = x_physical_batch_id;
SELECT distinct sca_process_batch_id
FROM cn_sca_process_batches
WHERE logical_batch_id = x_logical_batch_id;
-- If submission failed update the batch record and bail
IF l_temp_id = 0 THEN
--cn_debug.print_msg('conc disp submit failed',1);
'select count(distinct(source_id)) from cn_sca_headers_interface ';
'select count(distinct(source_id)) from cn_sca_headers_interface ';
'select count(distinct(order_number)) from cn_comm_lines_api ';
'select count(distinct(invoice_number)) from cn_comm_lines_api ';
'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';
'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';
'select distinct(source_id) from cn_sca_headers_interface ';
'select source_id from ' ||
'(select rownum row_number, source_id from ' ||
'(' || l_sql_stmt_id || ')) sca_table ' ||
'where sca_table.row_number in ' ||
l_sql_stmt_divider;
l_sql_stmt_id := 'select distinct(order_number) from cn_comm_lines_api ';
'select order_number from ' ||
'(select rownum row_number, order_number from ' ||
'(' || l_sql_stmt_id || ')) api_ord_table ' ||
'where api_ord_table.row_number in ' ||
l_sql_stmt_divider;
l_sql_stmt_id := 'select distinct(invoice_number) from cn_comm_lines_api ';
'select invoice_number from ' ||
'(select rownum row_number, invoice_number from ' ||
'(' || l_sql_stmt_id || ')) api_inv_table ' ||
'where api_inv_table.row_number in ' ||
l_sql_stmt_divider;
l_sql_stmt_id := 'select CSLI.sca_lines_interface_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';
'select sca_lines_interface_id from ' ||
'(select rownum row_number, sca_lines_interface_id from ' ||
'(' || l_sql_stmt_id || ')) sca_lines_table ' ||
'where sca_lines_table.row_number in '||
l_sql_stmt_divider;
l_sql_stmt_id := 'select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';
'select sca_lines_output_id from ' ||
'(select rownum row_number, sca_lines_output_id from ' ||
'(' || l_sql_stmt_id || ')) sca_output_table ' ||
'where sca_output_table.row_number in ' ||
l_sql_stmt_divider;
debugmsg(batch_type || ': Assign : Insert into CN_SCA_PROCESS_BATCHES ');
SELECT cn_sca_process_batches_s.NEXTVAL
INTO l_sca_process_batch_id
FROM sys.dual;
insert into CN_SCA_PROCESS_BATCHES
( sca_process_batch_id,
start_id,
end_id,
type,
logical_batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id)
values
( l_sca_process_batch_id,
l_start_id,
l_end_id,
batch_type,
p_logical_batch_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_org_id);
SELECT cn_sca_process_batches_s.NEXTVAL
INTO l_sca_process_batch_id
FROM sys.dual;
insert into CN_SCA_PROCESS_BATCHES
( sca_process_batch_id,
start_id,
end_id,
type,
logical_batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id)
values
( l_sca_process_batch_id,
l_start_id,
l_end_id,
batch_type,
p_logical_batch_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_org_id);
SELECT cn_sca_process_batches_s.NEXTVAL
INTO l_sca_process_batch_id
FROM sys.dual;
insert into CN_SCA_PROCESS_BATCHES
( sca_process_batch_id,
start_id,
end_id,
type,
logical_batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id)
values
( l_sca_process_batch_id,
l_id,
l_id,
batch_type,
p_logical_batch_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_org_id);
select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO
where CSLO.sca_headers_interface_id = CSHI.sca_headers_interface_id
and trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CSHI.process_status <> 'SCA_UNPROCESSED'
and CSHI.transaction_status = 'SCA_UNPROCESSED'
and CSLO.sca_lines_output_id between start_id and end_id;
select start_id, end_id into
l_start_id, l_end_id
from cn_sca_process_batches
where sca_process_batch_id = p_physical_batch_id;
INSERT into CN_COMM_LINES_API
( SALESREP_ID,
PROCESSED_DATE,
PROCESSED_PERIOD_ID,
TRANSACTION_AMOUNT,
TRX_TYPE,
REVENUE_CLASS_ID,
LOAD_STATUS,
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,
COMM_LINES_API_ID,
CONC_BATCH_ID,
PROCESS_BATCH_ID,
SALESREP_NUMBER,
ROLLUP_DATE,
SOURCE_DOC_ID,
SOURCE_DOC_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANSACTION_CURRENCY_CODE,
EXCHANGE_RATE,
ACCTD_TRANSACTION_AMOUNT,
TRX_ID,
TRX_LINE_ID,
TRX_SALES_LINE_ID,
QUANTITY,
SOURCE_TRX_NUMBER,
DISCOUNT_PERCENTAGE,
MARGIN_PERCENTAGE,
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,
ADJUST_DATE,
ADJUSTED_BY,
REVENUE_TYPE,
ADJUST_ROLLUP_FLAG,
ADJUST_COMMENTS,
ADJUST_STATUS,
LINE_NUMBER,
BILL_TO_ADDRESS_ID,
SHIP_TO_ADDRESS_ID,
BILL_TO_CONTACT_ID,
SHIP_TO_CONTACT_ID,
ADJ_COMM_LINES_API_ID,
PRE_DEFINED_RC_FLAG,
ROLLUP_FLAG,
FORECAST_ID,
UPSIDE_QUANTITY,
UPSIDE_AMOUNT,
UOM_CODE,
REASON_CODE,
TYPE,
PRE_PROCESSED_CODE,
QUOTA_ID,
SRP_PLAN_ASSIGN_ID,
ROLE_ID,
COMP_GROUP_ID,
COMMISSION_AMOUNT,
EMPLOYEE_NUMBER,
REVERSAL_FLAG,
REVERSAL_HEADER_ID,
SALES_CHANNEL,
OBJECT_VERSION_NUMBER,
SPLIT_PCT,
SPLIT_status,
ORG_ID)
(select
CS.SALESREP_ID,
CCLA.PROCESSED_DATE,
CCLA.PROCESSED_PERIOD_ID,
(CSLO.allocation_percentage/100) * NVL(CSHI.transaction_amount, 0),
CCLA.TRX_TYPE,
CCLA.REVENUE_CLASS_ID,
'UNLOADED',
CCLA.ATTRIBUTE_CATEGORY,
CCLA.ATTRIBUTE1,
CCLA.ATTRIBUTE2,
CCLA.ATTRIBUTE3,
CCLA.ATTRIBUTE4,
CCLA.ATTRIBUTE5,
CCLA.ATTRIBUTE6,
CCLA.ATTRIBUTE7,
CCLA.ATTRIBUTE8,
CCLA.ATTRIBUTE9,
CCLA.ATTRIBUTE10,
CCLA.ATTRIBUTE11,
CCLA.ATTRIBUTE12,
CCLA.ATTRIBUTE13,
CCLA.ATTRIBUTE14,
CCLA.ATTRIBUTE15,
CCLA.ATTRIBUTE16,
CCLA.ATTRIBUTE17,
CCLA.ATTRIBUTE18,
CCLA.ATTRIBUTE19,
CCLA.ATTRIBUTE20,
CCLA.ATTRIBUTE21,
CCLA.ATTRIBUTE22,
CCLA.ATTRIBUTE23,
CCLA.ATTRIBUTE24,
CCLA.ATTRIBUTE25,
CCLA.ATTRIBUTE26,
CCLA.ATTRIBUTE27,
CCLA.ATTRIBUTE28,
CCLA.ATTRIBUTE29,
CCLA.ATTRIBUTE30,
CCLA.ATTRIBUTE31,
CCLA.ATTRIBUTE32,
CCLA.ATTRIBUTE33,
CCLA.ATTRIBUTE34,
CCLA.ATTRIBUTE35,
CCLA.ATTRIBUTE36,
CCLA.ATTRIBUTE37,
CCLA.ATTRIBUTE38,
CCLA.ATTRIBUTE39,
CCLA.ATTRIBUTE40,
CCLA.ATTRIBUTE41,
CCLA.ATTRIBUTE42,
CCLA.ATTRIBUTE43,
CCLA.ATTRIBUTE44,
CCLA.ATTRIBUTE45,
CCLA.ATTRIBUTE46,
CCLA.ATTRIBUTE47,
CCLA.ATTRIBUTE48,
CCLA.ATTRIBUTE49,
CCLA.ATTRIBUTE50,
CCLA.ATTRIBUTE51,
CCLA.ATTRIBUTE52,
CCLA.ATTRIBUTE53,
CCLA.ATTRIBUTE54,
CCLA.ATTRIBUTE55,
CCLA.ATTRIBUTE56,
CCLA.ATTRIBUTE57,
CCLA.ATTRIBUTE58,
CCLA.ATTRIBUTE59,
CCLA.ATTRIBUTE60,
CCLA.ATTRIBUTE61,
CCLA.ATTRIBUTE62,
CCLA.ATTRIBUTE63,
CCLA.ATTRIBUTE64,
CCLA.ATTRIBUTE65,
CCLA.ATTRIBUTE66,
CCLA.ATTRIBUTE67,
CCLA.ATTRIBUTE68,
CCLA.ATTRIBUTE69,
CCLA.ATTRIBUTE70,
CCLA.ATTRIBUTE71,
CCLA.ATTRIBUTE72,
CCLA.ATTRIBUTE73,
CCLA.ATTRIBUTE74,
CCLA.ATTRIBUTE75,
CCLA.ATTRIBUTE76,
CCLA.ATTRIBUTE77,
CCLA.ATTRIBUTE78,
CCLA.ATTRIBUTE79,
CCLA.ATTRIBUTE80,
CCLA.ATTRIBUTE81,
CCLA.ATTRIBUTE82,
CCLA.ATTRIBUTE83,
CCLA.ATTRIBUTE84,
CCLA.ATTRIBUTE85,
CCLA.ATTRIBUTE86,
CCLA.ATTRIBUTE87,
CCLA.ATTRIBUTE88,
CCLA.ATTRIBUTE89,
CCLA.ATTRIBUTE90,
CCLA.ATTRIBUTE91,
CCLA.ATTRIBUTE92,
CCLA.ATTRIBUTE93,
CCLA.ATTRIBUTE94,
CCLA.ATTRIBUTE95,
CCLA.ATTRIBUTE96,
CCLA.ATTRIBUTE97,
CCLA.ATTRIBUTE98,
CCLA.ATTRIBUTE99,
CCLA.ATTRIBUTE100,
cn_comm_lines_api_s.NEXTVAL,
CCLA.CONC_BATCH_ID,
CCLA.PROCESS_BATCH_ID,
NULL,
CCLA.ROLLUP_DATE,
CCLA.SOURCE_DOC_ID,
CCLA.SOURCE_DOC_TYPE,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.login_id,
CCLA.TRANSACTION_CURRENCY_CODE,
CCLA.EXCHANGE_RATE,
NULL,
CCLA.TRX_ID,
CCLA.TRX_LINE_ID,
CCLA.TRX_SALES_LINE_ID,
CCLA.QUANTITY,
CCLA.SOURCE_TRX_NUMBER,
CCLA.DISCOUNT_PERCENTAGE,
CCLA.MARGIN_PERCENTAGE,
CCLA.SOURCE_TRX_ID,
CCLA.SOURCE_TRX_LINE_ID,
CCLA.SOURCE_TRX_SALES_LINE_ID,
CCLA.NEGATED_FLAG,
CCLA.CUSTOMER_ID,
CCLA.INVENTORY_ITEM_ID,
CCLA.ORDER_NUMBER,
CCLA.BOOKED_DATE,
CCLA.INVOICE_NUMBER,
CCLA.INVOICE_DATE,
SYSDATE,
l_adjusted_by,
CSLO.REVENUE_TYPE,
CCLA.ADJUST_ROLLUP_FLAG,
'Created by SCA',
'SCA_ALLOCATED',
CCLA.LINE_NUMBER,
CCLA.BILL_TO_ADDRESS_ID,
CCLA.SHIP_TO_ADDRESS_ID,
CCLA.BILL_TO_CONTACT_ID,
CCLA.SHIP_TO_CONTACT_ID,
CSLO.SOURCE_TRX_ID,
CCLA.PRE_DEFINED_RC_FLAG,
CCLA.ROLLUP_FLAG,
CCLA.FORECAST_ID,
CCLA.UPSIDE_QUANTITY,
CCLA.UPSIDE_AMOUNT,
CCLA.UOM_CODE,
CCLA.REASON_CODE,
CCLA.TYPE,
CCLA.PRE_PROCESSED_CODE,
CCLA.QUOTA_ID,
CCLA.SRP_PLAN_ASSIGN_ID,
CSLO.ROLE_ID,
CCLA.COMP_GROUP_ID,
CCLA.COMMISSION_AMOUNT,
CS.EMPLOYEE_NUMBER,
CCLA.REVERSAL_FLAG,
CCLA.REVERSAL_HEADER_ID,
CCLA.SALES_CHANNEL,
CCLA.OBJECT_VERSION_NUMBER,
CCLA.SPLIT_PCT,
CCLA.SPLIT_status,
ccla.org_id
from
cn_sca_lines_output CSLO, cn_salesreps CS, cn_comm_lines_api CCLA, cn_sca_headers_interface CSHI
where CS.resource_id = CSLO.resource_id -- added org_id join, since one resource can belong to more than one org
and cslo.org_id = cs.org_id
and ccla.org_id = cslo.org_id
and CCLA.comm_lines_api_id = CSLO.source_trx_id
and CSHI.sca_headers_interface_id = CSLO.sca_headers_interface_id
and CSLO.sca_lines_output_id = sca_lines(j));
select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CSHI.process_status <> 'SCA_UNPROCESSED'
and CSHI.transaction_status = 'SCA_UNPROCESSED'
and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
and CSLI.sca_lines_interface_id between start_id and end_id;
select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CSHI.process_status = 'NO RULE'
and CSHI.transaction_status = 'SCA_UNPROCESSED'
and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
and CSLI.sca_lines_interface_id between start_id and end_id;
select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CSHI.process_status = 'NOT ALLOCATED'
and CSHI.transaction_status = 'SCA_UNPROCESSED'
and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
and CSLI.sca_lines_interface_id between start_id and end_id;
select start_id, end_id into
l_start_id, l_end_id
from cn_sca_process_batches
where sca_process_batch_id = p_physical_batch_id;
debugmsg('Populate results back to API: Inserting Transactions into API for ''No Rules'', ''No Credit'' headers');
UPDATE cn_comm_lines_api api
SET load_status = 'OBSOLETE',
adjust_status = 'FROZEN',
adjust_date = sysdate,
adjusted_by = l_adjusted_by,
adjust_comments = 'Negated for SCA'
WHERE comm_lines_api_id = sca_lines(j);
INSERT into CN_COMM_LINES_API
( SALESREP_ID,
PROCESSED_DATE,
PROCESSED_PERIOD_ID,
TRANSACTION_AMOUNT,
TRX_TYPE,
REVENUE_CLASS_ID,
LOAD_STATUS,
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,
COMM_LINES_API_ID,
CONC_BATCH_ID,
PROCESS_BATCH_ID,
SALESREP_NUMBER,
ROLLUP_DATE,
SOURCE_DOC_ID,
SOURCE_DOC_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANSACTION_CURRENCY_CODE,
EXCHANGE_RATE,
ACCTD_TRANSACTION_AMOUNT,
TRX_ID,
TRX_LINE_ID,
TRX_SALES_LINE_ID,
QUANTITY,
SOURCE_TRX_NUMBER,
DISCOUNT_PERCENTAGE,
MARGIN_PERCENTAGE,
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,
ADJUST_DATE,
ADJUSTED_BY,
REVENUE_TYPE,
ADJUST_ROLLUP_FLAG,
ADJUST_COMMENTS,
ADJUST_STATUS,
LINE_NUMBER,
BILL_TO_ADDRESS_ID,
SHIP_TO_ADDRESS_ID,
BILL_TO_CONTACT_ID,
SHIP_TO_CONTACT_ID,
ADJ_COMM_LINES_API_ID,
PRE_DEFINED_RC_FLAG,
ROLLUP_FLAG,
FORECAST_ID,
UPSIDE_QUANTITY,
UPSIDE_AMOUNT,
UOM_CODE,
REASON_CODE,
TYPE,
PRE_PROCESSED_CODE,
QUOTA_ID,
SRP_PLAN_ASSIGN_ID,
ROLE_ID,
COMP_GROUP_ID,
COMMISSION_AMOUNT,
EMPLOYEE_NUMBER,
REVERSAL_FLAG,
REVERSAL_HEADER_ID,
SALES_CHANNEL,
OBJECT_VERSION_NUMBER,
SPLIT_PCT,
SPLIT_status,
org_id)
(select
CCLA.SALESREP_ID,
CCLA.PROCESSED_DATE,
CCLA.PROCESSED_PERIOD_ID,
CCLA.TRANSACTION_AMOUNT,
CCLA.TRX_TYPE,
CCLA.REVENUE_CLASS_ID,
'UNLOADED',
CCLA.ATTRIBUTE_CATEGORY,
CCLA.ATTRIBUTE1,
CCLA.ATTRIBUTE2,
CCLA.ATTRIBUTE3,
CCLA.ATTRIBUTE4,
CCLA.ATTRIBUTE5,
CCLA.ATTRIBUTE6,
CCLA.ATTRIBUTE7,
CCLA.ATTRIBUTE8,
CCLA.ATTRIBUTE9,
CCLA.ATTRIBUTE10,
CCLA.ATTRIBUTE11,
CCLA.ATTRIBUTE12,
CCLA.ATTRIBUTE13,
CCLA.ATTRIBUTE14,
CCLA.ATTRIBUTE15,
CCLA.ATTRIBUTE16,
CCLA.ATTRIBUTE17,
CCLA.ATTRIBUTE18,
CCLA.ATTRIBUTE19,
CCLA.ATTRIBUTE20,
CCLA.ATTRIBUTE21,
CCLA.ATTRIBUTE22,
CCLA.ATTRIBUTE23,
CCLA.ATTRIBUTE24,
CCLA.ATTRIBUTE25,
CCLA.ATTRIBUTE26,
CCLA.ATTRIBUTE27,
CCLA.ATTRIBUTE28,
CCLA.ATTRIBUTE29,
CCLA.ATTRIBUTE30,
CCLA.ATTRIBUTE31,
CCLA.ATTRIBUTE32,
CCLA.ATTRIBUTE33,
CCLA.ATTRIBUTE34,
CCLA.ATTRIBUTE35,
CCLA.ATTRIBUTE36,
CCLA.ATTRIBUTE37,
CCLA.ATTRIBUTE38,
CCLA.ATTRIBUTE39,
CCLA.ATTRIBUTE40,
CCLA.ATTRIBUTE41,
CCLA.ATTRIBUTE42,
CCLA.ATTRIBUTE43,
CCLA.ATTRIBUTE44,
CCLA.ATTRIBUTE45,
CCLA.ATTRIBUTE46,
CCLA.ATTRIBUTE47,
CCLA.ATTRIBUTE48,
CCLA.ATTRIBUTE49,
CCLA.ATTRIBUTE50,
CCLA.ATTRIBUTE51,
CCLA.ATTRIBUTE52,
CCLA.ATTRIBUTE53,
CCLA.ATTRIBUTE54,
CCLA.ATTRIBUTE55,
CCLA.ATTRIBUTE56,
CCLA.ATTRIBUTE57,
CCLA.ATTRIBUTE58,
CCLA.ATTRIBUTE59,
CCLA.ATTRIBUTE60,
CCLA.ATTRIBUTE61,
CCLA.ATTRIBUTE62,
CCLA.ATTRIBUTE63,
CCLA.ATTRIBUTE64,
CCLA.ATTRIBUTE65,
CCLA.ATTRIBUTE66,
CCLA.ATTRIBUTE67,
CCLA.ATTRIBUTE68,
CCLA.ATTRIBUTE69,
CCLA.ATTRIBUTE70,
CCLA.ATTRIBUTE71,
CCLA.ATTRIBUTE72,
CCLA.ATTRIBUTE73,
CCLA.ATTRIBUTE74,
CCLA.ATTRIBUTE75,
CCLA.ATTRIBUTE76,
CCLA.ATTRIBUTE77,
CCLA.ATTRIBUTE78,
CCLA.ATTRIBUTE79,
CCLA.ATTRIBUTE80,
CCLA.ATTRIBUTE81,
CCLA.ATTRIBUTE82,
CCLA.ATTRIBUTE83,
CCLA.ATTRIBUTE84,
CCLA.ATTRIBUTE85,
CCLA.ATTRIBUTE86,
CCLA.ATTRIBUTE87,
CCLA.ATTRIBUTE88,
CCLA.ATTRIBUTE89,
CCLA.ATTRIBUTE90,
CCLA.ATTRIBUTE91,
CCLA.ATTRIBUTE92,
CCLA.ATTRIBUTE93,
CCLA.ATTRIBUTE94,
CCLA.ATTRIBUTE95,
CCLA.ATTRIBUTE96,
CCLA.ATTRIBUTE97,
CCLA.ATTRIBUTE98,
CCLA.ATTRIBUTE99,
CCLA.ATTRIBUTE100,
cn_comm_lines_api_s.NEXTVAL,
CCLA.CONC_BATCH_ID,
CCLA.PROCESS_BATCH_ID,
NULL,
CCLA.ROLLUP_DATE,
CCLA.SOURCE_DOC_ID,
CCLA.SOURCE_DOC_TYPE,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.login_id,
CCLA.TRANSACTION_CURRENCY_CODE,
CCLA.EXCHANGE_RATE,
CCLA.ACCTD_TRANSACTION_AMOUNT,
CCLA.TRX_ID,
CCLA.TRX_LINE_ID,
CCLA.TRX_SALES_LINE_ID,
CCLA.QUANTITY,
CCLA.SOURCE_TRX_NUMBER,
CCLA.DISCOUNT_PERCENTAGE,
CCLA.MARGIN_PERCENTAGE,
CCLA.SOURCE_TRX_ID,
CCLA.SOURCE_TRX_LINE_ID,
CCLA.SOURCE_TRX_SALES_LINE_ID,
CCLA.NEGATED_FLAG,
CCLA.CUSTOMER_ID,
CCLA.INVENTORY_ITEM_ID,
CCLA.ORDER_NUMBER,
CCLA.BOOKED_DATE,
CCLA.INVOICE_NUMBER,
CCLA.INVOICE_DATE,
SYSDATE,
l_adjusted_by,
CCLA.REVENUE_TYPE,
CCLA.ADJUST_ROLLUP_FLAG,
'Created by SCA',
'SCA_NOT_ALLOCATED',
CCLA.LINE_NUMBER,
CCLA.BILL_TO_ADDRESS_ID,
CCLA.SHIP_TO_ADDRESS_ID,
CCLA.BILL_TO_CONTACT_ID,
CCLA.SHIP_TO_CONTACT_ID,
CCLA.COMM_LINES_API_ID,
CCLA.PRE_DEFINED_RC_FLAG,
CCLA.ROLLUP_FLAG,
CCLA.FORECAST_ID,
CCLA.UPSIDE_QUANTITY,
CCLA.UPSIDE_AMOUNT,
CCLA.UOM_CODE,
CCLA.REASON_CODE,
CCLA.TYPE,
CCLA.PRE_PROCESSED_CODE,
CCLA.QUOTA_ID,
CCLA.SRP_PLAN_ASSIGN_ID,
CCLA.ROLE_ID,
CCLA.COMP_GROUP_ID,
CCLA.COMMISSION_AMOUNT,
CCLA.EMPLOYEE_NUMBER,
CCLA.REVERSAL_FLAG,
CCLA.REVERSAL_HEADER_ID,
CCLA.SALES_CHANNEL,
CCLA.OBJECT_VERSION_NUMBER,
CCLA.SPLIT_PCT,
CCLA.SPLIT_status,
ccla.org_id
from
cn_comm_lines_api CCLA
where CCLA.comm_lines_api_id = sca_not_allocate_lines(j));
INSERT into CN_COMM_LINES_API
( SALESREP_ID,
PROCESSED_DATE,
PROCESSED_PERIOD_ID,
TRANSACTION_AMOUNT,
TRX_TYPE,
REVENUE_CLASS_ID,
LOAD_STATUS,
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,
COMM_LINES_API_ID,
CONC_BATCH_ID,
PROCESS_BATCH_ID,
SALESREP_NUMBER,
ROLLUP_DATE,
SOURCE_DOC_ID,
SOURCE_DOC_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANSACTION_CURRENCY_CODE,
EXCHANGE_RATE,
ACCTD_TRANSACTION_AMOUNT,
TRX_ID,
TRX_LINE_ID,
TRX_SALES_LINE_ID,
QUANTITY,
SOURCE_TRX_NUMBER,
DISCOUNT_PERCENTAGE,
MARGIN_PERCENTAGE,
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,
ADJUST_DATE,
ADJUSTED_BY,
REVENUE_TYPE,
ADJUST_ROLLUP_FLAG,
ADJUST_COMMENTS,
ADJUST_STATUS,
LINE_NUMBER,
BILL_TO_ADDRESS_ID,
SHIP_TO_ADDRESS_ID,
BILL_TO_CONTACT_ID,
SHIP_TO_CONTACT_ID,
ADJ_COMM_LINES_API_ID,
PRE_DEFINED_RC_FLAG,
ROLLUP_FLAG,
FORECAST_ID,
UPSIDE_QUANTITY,
UPSIDE_AMOUNT,
UOM_CODE,
REASON_CODE,
TYPE,
PRE_PROCESSED_CODE,
QUOTA_ID,
SRP_PLAN_ASSIGN_ID,
ROLE_ID,
COMP_GROUP_ID,
COMMISSION_AMOUNT,
EMPLOYEE_NUMBER,
REVERSAL_FLAG,
REVERSAL_HEADER_ID,
SALES_CHANNEL,
OBJECT_VERSION_NUMBER,
SPLIT_PCT,
SPLIT_status,
org_id)
(select
CCLA.SALESREP_ID,
CCLA.PROCESSED_DATE,
CCLA.PROCESSED_PERIOD_ID,
CCLA.TRANSACTION_AMOUNT,
CCLA.TRX_TYPE,
CCLA.REVENUE_CLASS_ID,
'UNLOADED',
CCLA.ATTRIBUTE_CATEGORY,
CCLA.ATTRIBUTE1,
CCLA.ATTRIBUTE2,
CCLA.ATTRIBUTE3,
CCLA.ATTRIBUTE4,
CCLA.ATTRIBUTE5,
CCLA.ATTRIBUTE6,
CCLA.ATTRIBUTE7,
CCLA.ATTRIBUTE8,
CCLA.ATTRIBUTE9,
CCLA.ATTRIBUTE10,
CCLA.ATTRIBUTE11,
CCLA.ATTRIBUTE12,
CCLA.ATTRIBUTE13,
CCLA.ATTRIBUTE14,
CCLA.ATTRIBUTE15,
CCLA.ATTRIBUTE16,
CCLA.ATTRIBUTE17,
CCLA.ATTRIBUTE18,
CCLA.ATTRIBUTE19,
CCLA.ATTRIBUTE20,
CCLA.ATTRIBUTE21,
CCLA.ATTRIBUTE22,
CCLA.ATTRIBUTE23,
CCLA.ATTRIBUTE24,
CCLA.ATTRIBUTE25,
CCLA.ATTRIBUTE26,
CCLA.ATTRIBUTE27,
CCLA.ATTRIBUTE28,
CCLA.ATTRIBUTE29,
CCLA.ATTRIBUTE30,
CCLA.ATTRIBUTE31,
CCLA.ATTRIBUTE32,
CCLA.ATTRIBUTE33,
CCLA.ATTRIBUTE34,
CCLA.ATTRIBUTE35,
CCLA.ATTRIBUTE36,
CCLA.ATTRIBUTE37,
CCLA.ATTRIBUTE38,
CCLA.ATTRIBUTE39,
CCLA.ATTRIBUTE40,
CCLA.ATTRIBUTE41,
CCLA.ATTRIBUTE42,
CCLA.ATTRIBUTE43,
CCLA.ATTRIBUTE44,
CCLA.ATTRIBUTE45,
CCLA.ATTRIBUTE46,
CCLA.ATTRIBUTE47,
CCLA.ATTRIBUTE48,
CCLA.ATTRIBUTE49,
CCLA.ATTRIBUTE50,
CCLA.ATTRIBUTE51,
CCLA.ATTRIBUTE52,
CCLA.ATTRIBUTE53,
CCLA.ATTRIBUTE54,
CCLA.ATTRIBUTE55,
CCLA.ATTRIBUTE56,
CCLA.ATTRIBUTE57,
CCLA.ATTRIBUTE58,
CCLA.ATTRIBUTE59,
CCLA.ATTRIBUTE60,
CCLA.ATTRIBUTE61,
CCLA.ATTRIBUTE62,
CCLA.ATTRIBUTE63,
CCLA.ATTRIBUTE64,
CCLA.ATTRIBUTE65,
CCLA.ATTRIBUTE66,
CCLA.ATTRIBUTE67,
CCLA.ATTRIBUTE68,
CCLA.ATTRIBUTE69,
CCLA.ATTRIBUTE70,
CCLA.ATTRIBUTE71,
CCLA.ATTRIBUTE72,
CCLA.ATTRIBUTE73,
CCLA.ATTRIBUTE74,
CCLA.ATTRIBUTE75,
CCLA.ATTRIBUTE76,
CCLA.ATTRIBUTE77,
CCLA.ATTRIBUTE78,
CCLA.ATTRIBUTE79,
CCLA.ATTRIBUTE80,
CCLA.ATTRIBUTE81,
CCLA.ATTRIBUTE82,
CCLA.ATTRIBUTE83,
CCLA.ATTRIBUTE84,
CCLA.ATTRIBUTE85,
CCLA.ATTRIBUTE86,
CCLA.ATTRIBUTE87,
CCLA.ATTRIBUTE88,
CCLA.ATTRIBUTE89,
CCLA.ATTRIBUTE90,
CCLA.ATTRIBUTE91,
CCLA.ATTRIBUTE92,
CCLA.ATTRIBUTE93,
CCLA.ATTRIBUTE94,
CCLA.ATTRIBUTE95,
CCLA.ATTRIBUTE96,
CCLA.ATTRIBUTE97,
CCLA.ATTRIBUTE98,
CCLA.ATTRIBUTE99,
CCLA.ATTRIBUTE100,
cn_comm_lines_api_s.NEXTVAL,
CCLA.CONC_BATCH_ID,
CCLA.PROCESS_BATCH_ID,
NULL,
CCLA.ROLLUP_DATE,
CCLA.SOURCE_DOC_ID,
CCLA.SOURCE_DOC_TYPE,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.login_id,
CCLA.TRANSACTION_CURRENCY_CODE,
CCLA.EXCHANGE_RATE,
CCLA.ACCTD_TRANSACTION_AMOUNT,
CCLA.TRX_ID,
CCLA.TRX_LINE_ID,
CCLA.TRX_SALES_LINE_ID,
CCLA.QUANTITY,
CCLA.SOURCE_TRX_NUMBER,
CCLA.DISCOUNT_PERCENTAGE,
CCLA.MARGIN_PERCENTAGE,
CCLA.SOURCE_TRX_ID,
CCLA.SOURCE_TRX_LINE_ID,
CCLA.SOURCE_TRX_SALES_LINE_ID,
CCLA.NEGATED_FLAG,
CCLA.CUSTOMER_ID,
CCLA.INVENTORY_ITEM_ID,
CCLA.ORDER_NUMBER,
CCLA.BOOKED_DATE,
CCLA.INVOICE_NUMBER,
CCLA.INVOICE_DATE,
SYSDATE,
l_adjusted_by,
CCLA.REVENUE_TYPE,
CCLA.ADJUST_ROLLUP_FLAG,
'Created by SCA',
'SCA_NO_RULE',
CCLA.LINE_NUMBER,
CCLA.BILL_TO_ADDRESS_ID,
CCLA.SHIP_TO_ADDRESS_ID,
CCLA.BILL_TO_CONTACT_ID,
CCLA.SHIP_TO_CONTACT_ID,
CCLA.COMM_LINES_API_ID,
CCLA.PRE_DEFINED_RC_FLAG,
CCLA.ROLLUP_FLAG,
CCLA.FORECAST_ID,
CCLA.UPSIDE_QUANTITY,
CCLA.UPSIDE_AMOUNT,
CCLA.UOM_CODE,
CCLA.REASON_CODE,
CCLA.TYPE,
CCLA.PRE_PROCESSED_CODE,
CCLA.QUOTA_ID,
CCLA.SRP_PLAN_ASSIGN_ID,
CCLA.ROLE_ID,
CCLA.COMP_GROUP_ID,
CCLA.COMMISSION_AMOUNT,
CCLA.EMPLOYEE_NUMBER,
CCLA.REVERSAL_FLAG,
CCLA.REVERSAL_HEADER_ID,
CCLA.SALES_CHANNEL,
CCLA.OBJECT_VERSION_NUMBER,
CCLA.SPLIT_PCT,
CCLA.SPLIT_status,
ccla.org_id
from
cn_comm_lines_api CCLA
where CCLA.comm_lines_api_id = sca_no_rule_lines(j));
CURSOR sca_update_headers_cur (p_start_date DATE, p_end_date DATE) IS
select CSHI.sca_headers_interface_id from cn_sca_headers_interface CSHI
where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CSHI.process_status <> 'SCA_UNPROCESSED'
and CSHI.transaction_status = 'SCA_UNPROCESSED'
AND cshi.org_id = p_org_id;
TYPE sca_update_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
sca_update_headers sca_update_headers_tbl;
SELECT cn_sca_logical_batches_s.NEXTVAL
INTO l_create_logical_batch_id
FROM sys.dual;
SELECT cn_sca_logical_batches_s.NEXTVAL
INTO l_negate_logical_batch_id
FROM sys.dual;
OPEN sca_update_headers_cur (p_start_date, p_end_date);
FETCH sca_update_headers_cur BULK COLLECT INTO sca_update_headers;
if (sca_update_headers.COUNT = 0) then
raise no_trx_lines;
FORALL j IN 1..sca_update_headers.COUNT
UPDATE cn_sca_headers_interface
SET transaction_status = 'SCA_POPULATED'
WHERE sca_headers_interface_id = sca_update_headers(j);
SELECT cn_sca_logical_batches_s.NEXTVAL
INTO l_logical_batch_id
FROM sys.dual;
SELECT cn_sca_logical_batches_s.NEXTVAL
INTO l_logical_batch_id
FROM sys.dual;
SELECT cn_sca_logical_batches_s.NEXTVAL
INTO l_logical_batch_id
FROM sys.dual;
select sca_mapping_status into
l_gen_status
from cn_repositories;
select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is not null
and CCLA.invoice_number between l_start_id and l_end_id
and exists
(SELECT 1
from cn_sca_headers_interface CSHI
where CSHI.transaction_status = 'ADJUSTED'
and CSHI.source_id = CCLA.invoice_number
and CSHI.source_type = 'INV');
select sca_headers_interface_id
from cn_sca_headers_interface
where source_type = 'INV'
and source_id in
(select invoice_number
from
(select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is not null
and CCLA.invoice_number between l_start_id and l_end_id) SCA_SOURCE_ID
where exists
(SELECT 1
FROM cn_comm_lines_api CCLA_ORIG
where CCLA_ORIG.adj_comm_lines_api_id in
(SELECT CSLI.source_trx_id
from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
and CSHI.source_id = SCA_SOURCE_ID.invoice_number
and CSHI.source_type = 'INV')
and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
(CCLA_ORIG.load_status = 'LOADED' and exists
(select 1 from cn_comm_lines_api CCLA
where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
and CCLA.adjust_status = 'REVERSAL'
and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
)))));
select sca_headers_interface_id
from cn_sca_headers_interface
where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
and source_type = 'INV'
and source_id in
(select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is not null
and CCLA.invoice_number between l_start_id and l_end_id);
select CSLI.source_trx_id
from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
where CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
and CSHI.source_type = 'INV'
and CSHI.source_id in
(select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is not null
and CCLA.invoice_number between l_start_id and l_end_id);
select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is null
and CCLA.order_number is not null
and CCLA.order_number between l_start_id and l_end_id
and exists
(SELECT 1
from cn_sca_headers_interface CSHI
where CSHI.transaction_status = 'ADJUSTED'
and CSHI.source_id = CCLA.order_number
and CSHI.source_type = 'ORD');
select sca_headers_interface_id
from cn_sca_headers_interface
where source_type = 'ORD'
and source_id in
(select order_number
from
(select distinct(order_number) order_number from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is null
and CCLA.order_number is not null
and CCLA.order_number between l_start_id and l_end_id) SCA_SOURCE_ID
where exists
(SELECT 1
FROM cn_comm_lines_api CCLA_ORIG
where CCLA_ORIG.adj_comm_lines_api_id in
(SELECT CSLI.source_trx_id
from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
and CSHI.source_id = SCA_SOURCE_ID.order_number
and CSHI.source_type = 'ORD')
and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
(CCLA_ORIG.load_status = 'LOADED' and exists
(select 1 from cn_comm_lines_api CCLA
where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
and CCLA.adjust_status = 'REVERSAL'
and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
)))));
select sca_headers_interface_id
from cn_sca_headers_interface
where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
and source_type = 'ORD'
and source_id in
(select distinct(order_number) order_number from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is null
and CCLA.order_number is not null
and CCLA.order_number between l_start_id and l_end_id);
select CSLI.source_trx_id
from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
where CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
and CSHI.source_type = 'ORD'
and CSHI.source_id in
(select distinct(order_number) order_number from cn_comm_lines_api CCLA
where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and CCLA.load_status = 'UNLOADED'
and ((CCLA.adjust_status is null) or
(CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
and CCLA.line_number is not null
and CCLA.invoice_number is null
and CCLA.order_number is not null
and CCLA.order_number between l_start_id and l_end_id);
select start_id, end_id, type into
l_start_id, l_end_id, l_batch_type
from cn_sca_process_batches
where sca_process_batch_id = p_physical_batch_id;
UPDATE cn_sca_headers_interface
SET transaction_status = 'ADJUSTED'
WHERE sca_headers_interface_id = sca_adjust_headers(j);
debugmsg('Check_comm_lines_api_adjusted : Update the adjust_status of transactions that are not eligible for SCA');
UPDATE cn_comm_lines_api
SET adjust_status = 'SCA_NOT_ELIGIBLE',
adjust_date = sysdate,
adjusted_by = l_adjusted_by,
adjust_comments = 'SCA Check'
WHERE comm_lines_api_id = api_adjust_lines(j);
debugmsg('Check_comm_lines_api_adjusted : Update the adjust_status of transactions that are not eligible for SCA');
UPDATE cn_comm_lines_api API
SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
adjust_status = DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),
adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
adjust_comments = DECODE(API.load_status, 'UNLOADED', 'SCA_ROLLBACK', API.adjust_comments)
WHERE adj_comm_lines_api_id = sca_rollback_lines(j);
INSERT into CN_COMM_LINES_API
( SALESREP_ID,
PROCESSED_DATE,
PROCESSED_PERIOD_ID,
TRANSACTION_AMOUNT,
TRX_TYPE,
REVENUE_CLASS_ID,
LOAD_STATUS,
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,
COMM_LINES_API_ID,
CONC_BATCH_ID,
PROCESS_BATCH_ID,
SALESREP_NUMBER,
ROLLUP_DATE,
SOURCE_DOC_ID,
SOURCE_DOC_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANSACTION_CURRENCY_CODE,
EXCHANGE_RATE,
ACCTD_TRANSACTION_AMOUNT,
TRX_ID,
TRX_LINE_ID,
TRX_SALES_LINE_ID,
QUANTITY,
SOURCE_TRX_NUMBER,
DISCOUNT_PERCENTAGE,
MARGIN_PERCENTAGE,
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,
ADJUST_DATE,
ADJUSTED_BY,
REVENUE_TYPE,
ADJUST_ROLLUP_FLAG,
ADJUST_COMMENTS,
ADJUST_STATUS,
LINE_NUMBER,
BILL_TO_ADDRESS_ID,
SHIP_TO_ADDRESS_ID,
BILL_TO_CONTACT_ID,
SHIP_TO_CONTACT_ID,
ADJ_COMM_LINES_API_ID,
PRE_DEFINED_RC_FLAG,
ROLLUP_FLAG,
FORECAST_ID,
UPSIDE_QUANTITY,
UPSIDE_AMOUNT,
UOM_CODE,
REASON_CODE,
TYPE,
PRE_PROCESSED_CODE,
QUOTA_ID,
SRP_PLAN_ASSIGN_ID,
ROLE_ID,
COMP_GROUP_ID,
COMMISSION_AMOUNT,
EMPLOYEE_NUMBER,
REVERSAL_FLAG,
REVERSAL_HEADER_ID,
SALES_CHANNEL,
OBJECT_VERSION_NUMBER,
SPLIT_PCT,
SPLIT_status,
org_id)
(select
CCH.DIRECT_SALESREP_ID,
CCH.PROCESSED_DATE,
CCH.PROCESSED_PERIOD_ID,
-1 * NVL(CCH.TRANSACTION_AMOUNT_ORIG, 0),
CCH.TRX_TYPE,
CCH.REVENUE_CLASS_ID,
'UNLOADED',
CCH.ATTRIBUTE_CATEGORY,
CCH.ATTRIBUTE1,
CCH.ATTRIBUTE2,
CCH.ATTRIBUTE3,
CCH.ATTRIBUTE4,
CCH.ATTRIBUTE5,
CCH.ATTRIBUTE6,
CCH.ATTRIBUTE7,
CCH.ATTRIBUTE8,
CCH.ATTRIBUTE9,
CCH.ATTRIBUTE10,
CCH.ATTRIBUTE11,
CCH.ATTRIBUTE12,
CCH.ATTRIBUTE13,
CCH.ATTRIBUTE14,
CCH.ATTRIBUTE15,
CCH.ATTRIBUTE16,
CCH.ATTRIBUTE17,
CCH.ATTRIBUTE18,
CCH.ATTRIBUTE19,
CCH.ATTRIBUTE20,
CCH.ATTRIBUTE21,
CCH.ATTRIBUTE22,
CCH.ATTRIBUTE23,
CCH.ATTRIBUTE24,
CCH.ATTRIBUTE25,
CCH.ATTRIBUTE26,
CCH.ATTRIBUTE27,
CCH.ATTRIBUTE28,
CCH.ATTRIBUTE29,
CCH.ATTRIBUTE30,
CCH.ATTRIBUTE31,
CCH.ATTRIBUTE32,
CCH.ATTRIBUTE33,
CCH.ATTRIBUTE34,
CCH.ATTRIBUTE35,
CCH.ATTRIBUTE36,
CCH.ATTRIBUTE37,
CCH.ATTRIBUTE38,
CCH.ATTRIBUTE39,
CCH.ATTRIBUTE40,
CCH.ATTRIBUTE41,
CCH.ATTRIBUTE42,
CCH.ATTRIBUTE43,
CCH.ATTRIBUTE44,
CCH.ATTRIBUTE45,
CCH.ATTRIBUTE46,
CCH.ATTRIBUTE47,
CCH.ATTRIBUTE48,
CCH.ATTRIBUTE49,
CCH.ATTRIBUTE50,
CCH.ATTRIBUTE51,
CCH.ATTRIBUTE52,
CCH.ATTRIBUTE53,
CCH.ATTRIBUTE54,
CCH.ATTRIBUTE55,
CCH.ATTRIBUTE56,
CCH.ATTRIBUTE57,
CCH.ATTRIBUTE58,
CCH.ATTRIBUTE59,
CCH.ATTRIBUTE60,
CCH.ATTRIBUTE61,
CCH.ATTRIBUTE62,
CCH.ATTRIBUTE63,
CCH.ATTRIBUTE64,
CCH.ATTRIBUTE65,
CCH.ATTRIBUTE66,
CCH.ATTRIBUTE67,
CCH.ATTRIBUTE68,
CCH.ATTRIBUTE69,
CCH.ATTRIBUTE70,
CCH.ATTRIBUTE71,
CCH.ATTRIBUTE72,
CCH.ATTRIBUTE73,
CCH.ATTRIBUTE74,
CCH.ATTRIBUTE75,
CCH.ATTRIBUTE76,
CCH.ATTRIBUTE77,
CCH.ATTRIBUTE78,
CCH.ATTRIBUTE79,
CCH.ATTRIBUTE80,
CCH.ATTRIBUTE81,
CCH.ATTRIBUTE82,
CCH.ATTRIBUTE83,
CCH.ATTRIBUTE84,
CCH.ATTRIBUTE85,
CCH.ATTRIBUTE86,
CCH.ATTRIBUTE87,
CCH.ATTRIBUTE88,
CCH.ATTRIBUTE89,
CCH.ATTRIBUTE90,
CCH.ATTRIBUTE91,
CCH.ATTRIBUTE92,
CCH.ATTRIBUTE93,
CCH.ATTRIBUTE94,
CCH.ATTRIBUTE95,
CCH.ATTRIBUTE96,
CCH.ATTRIBUTE97,
CCH.ATTRIBUTE98,
CCH.ATTRIBUTE99,
CCH.ATTRIBUTE100,
cn_comm_lines_api_s.NEXTVAL,
NULL,
NULL,
NULL,
CCH.ROLLUP_DATE,
NULL,
CCH.SOURCE_DOC_TYPE,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.login_id,
CCH.ORIG_CURRENCY_CODE,
CCH.EXCHANGE_RATE,
-1 * NVL(CCH.TRANSACTION_AMOUNT, 0),
NULL, -- CCH.TRX_ID,
NULL, -- CCH.TRX_LINE_ID,
NULL, -- CCH.TRX_SALES_LINE_ID,
-1 * CCH.QUANTITY,
CCH.SOURCE_TRX_NUMBER,
CCH.DISCOUNT_PERCENTAGE,
CCH.MARGIN_PERCENTAGE,
CCH.SOURCE_TRX_ID,
CCH.SOURCE_TRX_LINE_ID,
CCH.SOURCE_TRX_SALES_LINE_ID,
'Y',
CCH.CUSTOMER_ID,
CCH.INVENTORY_ITEM_ID,
CCH.ORDER_NUMBER,
CCH.BOOKED_DATE,
CCH.INVOICE_NUMBER,
CCH.INVOICE_DATE,
SYSDATE,
l_adjusted_by,
CCH.REVENUE_TYPE,
CCH.ADJUST_ROLLUP_FLAG,
'SCA_ROLLBACK',
'REVERSAL',
CCH.LINE_NUMBER,
CCH.BILL_TO_ADDRESS_ID,
CCH.SHIP_TO_ADDRESS_ID,
CCH.BILL_TO_CONTACT_ID,
CCH.SHIP_TO_CONTACT_ID,
CCH.COMM_LINES_API_ID,
NULL, -- CCH.PRE_DEFINED_RC_FLAG,
NULL, -- CCH.ROLLUP_FLAG,
CCH.FORECAST_ID,
CCH.UPSIDE_QUANTITY,
CCH.UPSIDE_AMOUNT,
CCH.UOM_CODE,
CCH.REASON_CODE,
CCH.TYPE,
CCH.PRE_PROCESSED_CODE,
CCH.QUOTA_ID,
CCH.SRP_PLAN_ASSIGN_ID,
CCH.ROLE_ID,
CCH.COMP_GROUP_ID,
CCH.COMMISSION_AMOUNT,
CS.EMPLOYEE_NUMBER,
'Y',
CCH.COMMISSION_HEADER_ID,
CCH.SALES_CHANNEL,
CCH.OBJECT_VERSION_NUMBER,
CCH.SPLIT_PCT,
CCH.SPLIT_status,
cch.org_id
FROM cn_commission_headers CCH, cn_salesreps CS
WHERE CCH.adj_comm_lines_api_id = sca_rollback_lines(j)
AND CS.salesrep_id = CCH.direct_salesrep_id
AND ((CCH.adjust_status IS NULL) or (CCH.adjust_status <> 'FROZEN')));
UPDATE cn_commission_headers CSH
SET adjust_status = 'FROZEN',
reversal_header_id = CSH.commission_header_id,
reversal_flag = 'Y',
adjust_date = sysdate,
adjusted_by = l_adjusted_by,
adjust_comments = 'SCA_ROLLBACK'
WHERE adj_comm_lines_api_id = sca_rollback_lines(j)
AND ((adjust_status IS NULL) or (adjust_status <> 'FROZEN'));
UPDATE cn_comm_lines_api
SET adjust_status = NULL,
load_status = 'UNLOADED',
adjust_date = sysdate,
adjusted_by = l_adjusted_by,
adjust_comments = 'SCA_ROLLBACK'
WHERE comm_lines_api_id = sca_rollback_lines(j);
DELETE FROM cn_sca_lines_interface
WHERE sca_headers_interface_id = sca_rollback_headers(j);
DELETE FROM cn_sca_lines_output
WHERE sca_headers_interface_id = sca_rollback_headers(j);
DELETE FROM cn_sca_headers_interface
WHERE sca_headers_interface_id = sca_rollback_headers(j);
select sca_headers_interface_id
from cn_sca_headers_interface
where source_type = l_trx_type
and source_id in
(select source_id
from
(select distinct(source_id) source_id
from cn_sca_headers_interface
where source_type = l_trx_type
and trunc(processed_date) between trunc(p_start_date) and trunc(p_end_date)
and ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
and source_id between l_start_id and l_end_id) SCA_SOURCE_ID
where exists
(SELECT 1
FROM cn_comm_lines_api CCLA_ORIG
where CCLA_ORIG.adj_comm_lines_api_id in
(SELECT CSLI.source_trx_id
from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
and CSHI.source_id = SCA_SOURCE_ID.source_id
and CSHI.source_type = l_trx_type)
and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
(CCLA_ORIG.load_status = 'LOADED' and exists
(select 1 from cn_comm_lines_api CCLA
where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
and CCLA.adjust_status = 'REVERSAL'
and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
)))));
select sca_headers_interface_id
from cn_sca_headers_interface
where source_type = l_trx_type
and trunc(processed_date) between trunc(p_start_date) and trunc(p_end_date)
and ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
and source_id between l_start_id and l_end_id;
select CSLI.source_trx_id
from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
where CSHI.source_type = l_trx_type
and trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
and CSHI.source_id between l_start_id and l_end_id
and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id;
select start_id, end_id, type into
l_start_id, l_end_id, l_batch_type
from cn_sca_process_batches
where sca_process_batch_id = p_physical_batch_id;
UPDATE cn_sca_headers_interface
SET transaction_status = 'ADJUSTED'
WHERE sca_headers_interface_id = sca_headers_adjust(j);
UPDATE cn_comm_lines_api API
SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
adjust_status = DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),
adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
adjust_comments = DECODE(API.load_status, 'UNLOADED', 'SCA_ROLLBACK', API.adjust_comments)
WHERE adj_comm_lines_api_id = sca_lines_rollback(j);
INSERT into CN_COMM_LINES_API
( SALESREP_ID,
PROCESSED_DATE,
PROCESSED_PERIOD_ID,
TRANSACTION_AMOUNT,
TRX_TYPE,
REVENUE_CLASS_ID,
LOAD_STATUS,
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,
COMM_LINES_API_ID,
CONC_BATCH_ID,
PROCESS_BATCH_ID,
SALESREP_NUMBER,
ROLLUP_DATE,
SOURCE_DOC_ID,
SOURCE_DOC_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANSACTION_CURRENCY_CODE,
EXCHANGE_RATE,
ACCTD_TRANSACTION_AMOUNT,
TRX_ID,
TRX_LINE_ID,
TRX_SALES_LINE_ID,
QUANTITY,
SOURCE_TRX_NUMBER,
DISCOUNT_PERCENTAGE,
MARGIN_PERCENTAGE,
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,
ADJUST_DATE,
ADJUSTED_BY,
REVENUE_TYPE,
ADJUST_ROLLUP_FLAG,
ADJUST_COMMENTS,
ADJUST_STATUS,
LINE_NUMBER,
BILL_TO_ADDRESS_ID,
SHIP_TO_ADDRESS_ID,
BILL_TO_CONTACT_ID,
SHIP_TO_CONTACT_ID,
ADJ_COMM_LINES_API_ID,
PRE_DEFINED_RC_FLAG,
ROLLUP_FLAG,
FORECAST_ID,
UPSIDE_QUANTITY,
UPSIDE_AMOUNT,
UOM_CODE,
REASON_CODE,
TYPE,
PRE_PROCESSED_CODE,
QUOTA_ID,
SRP_PLAN_ASSIGN_ID,
ROLE_ID,
COMP_GROUP_ID,
COMMISSION_AMOUNT,
EMPLOYEE_NUMBER,
REVERSAL_FLAG,
REVERSAL_HEADER_ID,
SALES_CHANNEL,
OBJECT_VERSION_NUMBER,
SPLIT_PCT,
SPLIT_status,
org_id)
(select
CCH.DIRECT_SALESREP_ID,
CCH.PROCESSED_DATE,
CCH.PROCESSED_PERIOD_ID,
-1 * NVL(CCH.TRANSACTION_AMOUNT_ORIG, 0),
CCH.TRX_TYPE,
CCH.REVENUE_CLASS_ID,
'UNLOADED',
CCH.ATTRIBUTE_CATEGORY,
CCH.ATTRIBUTE1,
CCH.ATTRIBUTE2,
CCH.ATTRIBUTE3,
CCH.ATTRIBUTE4,
CCH.ATTRIBUTE5,
CCH.ATTRIBUTE6,
CCH.ATTRIBUTE7,
CCH.ATTRIBUTE8,
CCH.ATTRIBUTE9,
CCH.ATTRIBUTE10,
CCH.ATTRIBUTE11,
CCH.ATTRIBUTE12,
CCH.ATTRIBUTE13,
CCH.ATTRIBUTE14,
CCH.ATTRIBUTE15,
CCH.ATTRIBUTE16,
CCH.ATTRIBUTE17,
CCH.ATTRIBUTE18,
CCH.ATTRIBUTE19,
CCH.ATTRIBUTE20,
CCH.ATTRIBUTE21,
CCH.ATTRIBUTE22,
CCH.ATTRIBUTE23,
CCH.ATTRIBUTE24,
CCH.ATTRIBUTE25,
CCH.ATTRIBUTE26,
CCH.ATTRIBUTE27,
CCH.ATTRIBUTE28,
CCH.ATTRIBUTE29,
CCH.ATTRIBUTE30,
CCH.ATTRIBUTE31,
CCH.ATTRIBUTE32,
CCH.ATTRIBUTE33,
CCH.ATTRIBUTE34,
CCH.ATTRIBUTE35,
CCH.ATTRIBUTE36,
CCH.ATTRIBUTE37,
CCH.ATTRIBUTE38,
CCH.ATTRIBUTE39,
CCH.ATTRIBUTE40,
CCH.ATTRIBUTE41,
CCH.ATTRIBUTE42,
CCH.ATTRIBUTE43,
CCH.ATTRIBUTE44,
CCH.ATTRIBUTE45,
CCH.ATTRIBUTE46,
CCH.ATTRIBUTE47,
CCH.ATTRIBUTE48,
CCH.ATTRIBUTE49,
CCH.ATTRIBUTE50,
CCH.ATTRIBUTE51,
CCH.ATTRIBUTE52,
CCH.ATTRIBUTE53,
CCH.ATTRIBUTE54,
CCH.ATTRIBUTE55,
CCH.ATTRIBUTE56,
CCH.ATTRIBUTE57,
CCH.ATTRIBUTE58,
CCH.ATTRIBUTE59,
CCH.ATTRIBUTE60,
CCH.ATTRIBUTE61,
CCH.ATTRIBUTE62,
CCH.ATTRIBUTE63,
CCH.ATTRIBUTE64,
CCH.ATTRIBUTE65,
CCH.ATTRIBUTE66,
CCH.ATTRIBUTE67,
CCH.ATTRIBUTE68,
CCH.ATTRIBUTE69,
CCH.ATTRIBUTE70,
CCH.ATTRIBUTE71,
CCH.ATTRIBUTE72,
CCH.ATTRIBUTE73,
CCH.ATTRIBUTE74,
CCH.ATTRIBUTE75,
CCH.ATTRIBUTE76,
CCH.ATTRIBUTE77,
CCH.ATTRIBUTE78,
CCH.ATTRIBUTE79,
CCH.ATTRIBUTE80,
CCH.ATTRIBUTE81,
CCH.ATTRIBUTE82,
CCH.ATTRIBUTE83,
CCH.ATTRIBUTE84,
CCH.ATTRIBUTE85,
CCH.ATTRIBUTE86,
CCH.ATTRIBUTE87,
CCH.ATTRIBUTE88,
CCH.ATTRIBUTE89,
CCH.ATTRIBUTE90,
CCH.ATTRIBUTE91,
CCH.ATTRIBUTE92,
CCH.ATTRIBUTE93,
CCH.ATTRIBUTE94,
CCH.ATTRIBUTE95,
CCH.ATTRIBUTE96,
CCH.ATTRIBUTE97,
CCH.ATTRIBUTE98,
CCH.ATTRIBUTE99,
CCH.ATTRIBUTE100,
cn_comm_lines_api_s.NEXTVAL,
NULL,
NULL,
NULL,
CCH.ROLLUP_DATE,
NULL,
CCH.SOURCE_DOC_TYPE,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.login_id,
CCH.ORIG_CURRENCY_CODE,
CCH.EXCHANGE_RATE,
-1 * NVL(CCH.TRANSACTION_AMOUNT, 0),
NULL, -- CCH.TRX_ID,
NULL, -- CCH.TRX_LINE_ID,
NULL, -- CCH.TRX_SALES_LINE_ID,
-1 * CCH.QUANTITY,
CCH.SOURCE_TRX_NUMBER,
CCH.DISCOUNT_PERCENTAGE,
CCH.MARGIN_PERCENTAGE,
CCH.SOURCE_TRX_ID,
CCH.SOURCE_TRX_LINE_ID,
CCH.SOURCE_TRX_SALES_LINE_ID,
'Y',
CCH.CUSTOMER_ID,
CCH.INVENTORY_ITEM_ID,
CCH.ORDER_NUMBER,
CCH.BOOKED_DATE,
CCH.INVOICE_NUMBER,
CCH.INVOICE_DATE,
SYSDATE,
l_adjusted_by,
CCH.REVENUE_TYPE,
CCH.ADJUST_ROLLUP_FLAG,
'SCA_ROLLBACK',
'REVERSAL',
CCH.LINE_NUMBER,
CCH.BILL_TO_ADDRESS_ID,
CCH.SHIP_TO_ADDRESS_ID,
CCH.BILL_TO_CONTACT_ID,
CCH.SHIP_TO_CONTACT_ID,
CCH.COMM_LINES_API_ID,
NULL, -- CCH.PRE_DEFINED_RC_FLAG,
NULL, -- CCH.ROLLUP_FLAG,
CCH.FORECAST_ID,
CCH.UPSIDE_QUANTITY,
CCH.UPSIDE_AMOUNT,
CCH.UOM_CODE,
CCH.REASON_CODE,
CCH.TYPE,
CCH.PRE_PROCESSED_CODE,
CCH.QUOTA_ID,
CCH.SRP_PLAN_ASSIGN_ID,
CCH.ROLE_ID,
CCH.COMP_GROUP_ID,
CCH.COMMISSION_AMOUNT,
CS.EMPLOYEE_NUMBER,
'Y',
CCH.COMMISSION_HEADER_ID,
CCH.SALES_CHANNEL,
CCH.OBJECT_VERSION_NUMBER,
CCH.SPLIT_PCT,
CCH.SPLIT_status,
cch.org_id
FROM cn_commission_headers CCH, cn_salesreps CS
WHERE CCH.adj_comm_lines_api_id = sca_lines_rollback(j)
AND CS.salesrep_id = CCH.direct_salesrep_id
AND ((CCH.adjust_status IS NULL) or (CCH.adjust_status <> 'FROZEN')));
UPDATE cn_commission_headers CSH
SET adjust_status = 'FROZEN',
reversal_header_id = CSH.commission_header_id,
reversal_flag = 'Y',
adjust_date = sysdate,
adjusted_by = l_adjusted_by,
adjust_comments = 'SCA_ROLLBACK'
WHERE adj_comm_lines_api_id = sca_lines_rollback(j)
AND ((adjust_status IS NULL) or (adjust_status <> 'FROZEN'));
UPDATE cn_comm_lines_api api
SET load_status = 'UNLOADED',
adjust_status = NULL,
adjust_date = sysdate,
adjusted_by = l_adjusted_by,
adjust_comments = 'SCA_ROLLBACK'
WHERE comm_lines_api_id = sca_lines_rollback(j);
DELETE FROM cn_sca_lines_output
where sca_headers_interface_id = sca_headers_rollback(j);
DELETE FROM cn_sca_lines_interface
where sca_headers_interface_id = sca_headers_rollback(j);
DELETE FROM cn_sca_headers_interface
WHERE sca_headers_interface_id = sca_headers_rollback(j);
l_org_id := p_org_id; -- replaced the able select statement with the assignment statement