[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_sca_insert_tbl_type cn_sca_insert_tbl_type;
SELECT DISTINCT sca_process_batch_id
FROM cn_sca_process_batches
WHERE logical_batch_id = x_logical_batch_id;
l_sql_stmt_count := 'SELECT count(1) FROM cn_sca_headers_interface cshi ';
'SELECT cshi.sca_headers_interface_id ' || 'FROM cn_sca_headers_interface CSHI ';
'SELECT sca_headers_interface_id FROM '
|| '(SELECT rownum row_number, sca_headers_interface_id FROM '
|| '('
|| l_sql_stmt_id
|| ')) sca_headers_table '
|| 'WHERE sca_headers_table.row_number IN '
|| l_sql_stmt_divider;
debugmsg(p_transaction_source || ': 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
, p_transaction_source
, 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
, p_transaction_source
, 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
, p_transaction_source
, p_logical_batch_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, p_org_id
);
SELECT cn_sca_logical_batches_s.NEXTVAL
INTO l_logical_batch_id
FROM SYS.DUAL;
SELECT COUNT(1)
INTO l_rule_count
FROM cn_sca_denorm_rules a
WHERE a.transaction_source = p_transaction_source AND a.org_id = p_org_id;
/* This procedure returns the appropiate where clause to select */
/* data from the table cn_comm_lines_api_all depending on run mode */
PROCEDURE get_where_clause(
p_start_date IN DATE
, p_end_date IN DATE
, p_org_id IN NUMBER
, p_run_mode IN VARCHAR2
, x_where_clause OUT NOCOPY VARCHAR2
, errbuf IN OUT NOCOPY VARCHAR2
, retcode IN OUT NOCOPY VARCHAR2
) IS
BEGIN
debugmsg('SCA : Start of get_where_clause');
/* only the collected txns are selected and not the ones generated by this process */
x_where_clause := x_where_clause || 'AND terr_id IS NULL ';
SELECT comm_lines_api_id
FROM cn_comm_lines_api_all
WHERE load_status NOT IN(''OBSOLETE'', ''FILTERED'')
AND adjust_status NOT IN(''FROZEN'', ''REVERSAL'')
START WITH COMM_LINES_API_ID IN (SELECT adj_comm_lines_api_id from cn_comm_lines_api_all
WHERE terr_id IN (
SELECT jcdt.terr_id
FROM jty_conc_req_summ jcrs, jty_changed_dea_terrs jcdt
WHERE jcrs.program_name = ''JTY_STAR''
AND jcrs.retcode = 0
AND jcrs.param1 = -1001
AND jcrs.param2 = ''DEA INCREMENTAL''
AND jcrs.request_id = jcdt.star_request_id)
AND processed_date BETWEEN '
|| 'to_date('''
|| TO_CHAR(p_start_date, 'dd/mm/yyyy')
|| ''',''dd/mm/yyyy:hh24:mi:ss'')'
|| ' and to_date('''
|| TO_CHAR(p_end_date, 'dd/mm/yyyy')||':23:59:59'
|| ''',''dd/mm/yyyy:hh24:mi:ss''))'
||'CONNECT BY PRIOR ADJ_COMM_LINES_API_ID = COMM_LINES_API_ID )';
/* donot select txns for which user has checked the "preserve credit override flag" to bypass crediting process */
x_where_clause := x_where_clause || 'AND (preserve_credit_override_flag = ''N'') ';
PROCEDURE update_txns_processed(errbuf IN OUT NOCOPY VARCHAR2, retcode IN OUT NOCOPY VARCHAR2
, p_worker_id IN NUMBER) IS
l_no_of_records NUMBER;
debugmsg('SCA : Start of update_txns_processed');
UPDATE CN_COMM_LINES_API_ALL CLA
SET LOAD_STATUS = 'CREDITED', ADJUST_STATUS = 'SCA_ALLOCATED'
WHERE COMM_LINES_API_ID IN
( SELECT /*+ cardinality(a,1) */ TRANS_OBJECT_ID
FROM (
select /*+ no_merge */ DISTINCT TRANS_OBJECT_ID
from JTF_TAE_1001_SC_WINNERS A
where A.WORKER_ID = p_worker_id
) A
WHERE EXISTS
(
select /*+ no_unest */ 1
from CN_COMM_LINES_API_ALL B
where B.ADJ_COMM_LINES_API_ID = A.TRANS_OBJECT_ID
AND B.TERR_ID IS NOT NULL
)
);
debugmsg('SCA : End of update_txns_processed');
debugmsg('SCA : Unexpected exception in update_txns_processed');
errbuf := 'CN_SCATM_TAE_PUB.update_txns_processed.others';
END update_txns_processed;
/* This procedure inserts credited txns into api table */
PROCEDURE insert_api_txns(
p_org_id IN NUMBER
, p_trans_object_id_tbl IN OUT NOCOPY g_trans_object_id_tbl_type
, p_salesrep_id_tbl IN OUT NOCOPY g_salesrep_id_tbl_type
, p_emp_no_tbl IN OUT NOCOPY g_emp_no_tbl_type
, p_role_id_tbl IN OUT NOCOPY g_role_id_tbl_type
, p_split_pctg_tbl IN OUT NOCOPY g_split_pctg_tbl_type
, p_rev_type_tbl IN OUT NOCOPY g_rev_type_tbl_type
, p_terr_id_tbl IN OUT NOCOPY g_terr_id_tbl_type
, p_terr_name_tbl IN OUT NOCOPY g_terr_name_tbl_type
, p_del_flag_tbl IN OUT NOCOPY g_del_flag_tbl_type
, errbuf IN OUT NOCOPY VARCHAR2
, retcode IN OUT NOCOPY VARCHAR2
) IS
l_no_of_records NUMBER;
debugmsg('SCA : Start of insert_api_txns');
debugmsg('SCA : Number of rows to be inserted : ' || l_no_of_records);
debugmsg('SCA : Start of insert_api_txns '||to_char(sysdate,'dd-mm-rrrr hh24:mi:ss'));
/* insert the credited transactions into api table */
/* process all the rows even if some of them fail */
-- g_sca_insert_tbl_type := cn_sca_insert_tbl_type(cn_sca_insert_rec_type(1,1,1,1,1,1,1,1,1));
INSERT INTO cn_comm_lines_api_all
(
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
, terr_id
, terr_name
, preserve_credit_override_flag -- to ensure this is not null
)
SELECT p_salesrep_id_tbl(i) -- parent.salesrep_id
, ccla.processed_date
, ccla.processed_period_id
, ROUND(NVL((ccla.transaction_amount * p_split_pctg_tbl(i)) / 100, 0), 2) -- parent.split_percentage
, 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
, g_user_id
, g_sysdate
, g_user_id
, g_sysdate
, g_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
, g_sysdate
, g_user_id
, p_rev_type_tbl(i) -- parent.revenue_type
, ccla.adjust_rollup_flag
, 'Created by TAE'
, ccla.adjust_status
, 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
, p_role_id_tbl(i) -- parent.role_id
, ccla.comp_group_id
, ccla.commission_amount
, p_emp_no_tbl(i) -- parent.employee_number
, ccla.reversal_flag
, ccla.reversal_header_id
, ccla.sales_channel
, ccla.object_version_number
, p_split_pctg_tbl(i) -- parent.split_percentage
, ccla.split_status
, ccla.org_id
, p_terr_id_tbl(i) -- parent.terr_id
, p_terr_name_tbl(i) -- parent.terr_name
, 'N' -- to ensure preserve_credit_override_flag is not null
FROM cn_comm_lines_api_all ccla
WHERE ccla.comm_lines_api_id = p_trans_object_id_tbl(i)
AND ccla.org_id = p_org_id
AND p_del_flag_tbl(i) <> 'Y';
debugmsg('SCA : End of insert_api_txns '||to_char(sysdate,'dd-mm-rrrr hh24:mi:ss'));
debugmsg('SCA : End of insert_api_txns');
debugmsg('SCA : Unexpected exception in insert_api_txns');
errbuf := 'CN_SCATM_TAE_PUB.insert_api_txns.others';
END insert_api_txns;
SELECT count(*)
INTO l_count
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.param1 = -1001
AND a.param2 = 'DEA INCREMENTAL'
AND a.retcode = 0;
SELECT count(*)
INTO l_count
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.param1 = -1001
AND a.retcode = 0
AND a.param2 = 'DATE EFFECTIVE'
AND request_date > ( SELECT MAX(request_date)
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.retcode = 0
AND a.param1 = -1001
AND a.param2 = 'DEA INCREMENTAL' );
/* insert the selected transactions from cn_comm_lines_api_all table */
/* to the interface table jtf_tae_1001_sc_dea_trans */
jty_assign_bulk_pub.collect_trans_data(
p_api_version_number => 1.0
, p_init_msg_list => fnd_api.g_false
, p_source_id => -1001
, p_trans_id => -1002
, p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM'
, p_mode => 'DATE EFFECTIVE'
, p_where => l_where_clause
, p_no_of_workers => g_num_workers
, p_percent_analyzed => 20
, -- this value can be either a profile option or a parameter to conc program
p_request_id => p_request_id
, -- request id of the concurrent program
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, errbuf => errbuf
, retcode => retcode
, p_oic_mode => 'CLEAR'
);
/* insert the selected transactions from cn_comm_lines_api_all table */
/* to the interface table jtf_tae_1001_sc_dea_trans */
jty_assign_bulk_pub.collect_trans_data(
p_api_version_number => 1.0
, p_init_msg_list => fnd_api.g_false
, p_source_id => -1001
, p_trans_id => -1002
, p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM'
, p_mode => 'DATE EFFECTIVE'
, p_where => l_where_clause
, p_no_of_workers => g_num_workers
, p_percent_analyzed => 20
, -- this value can be either a profile option or a parameter to conc program
p_request_id => p_request_id
, -- request id of the concurrent program
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, errbuf => errbuf
, retcode => retcode
, p_oic_mode => 'POST'
);
/* Cursor definition to select all winning resources from winners table */
OPEN c_credited_txn_cur
FOR 'SELECT /*+ leading(a) cardinality(a,100) */ a.trans_object_id, '
|| ' a.terr_id, '
|| ' c.name, '
|| ' d.salesrep_id, '
|| ' d.employee_number, '
|| ' a.role_id, '
|| ' ''N'', '
|| ' b.'
|| l_ffname_split_pctg
|| ', '
|| ' b.'
|| l_ffname_rev_type
|| ' '
|| 'FROM jtf_tae_1001_sc_winners a, '
|| ' jtf_terr_rsc_all b, '
|| ' jtf_terr_all c, '
|| ' cn_salesreps d '
|| 'WHERE a.terr_rsc_id = b.terr_rsc_id '
|| 'AND a.terr_id = c.terr_id '
|| 'AND a.resource_id = d.resource_id '
|| 'AND a.worker_id = '||p_worker_id;
/* and insert the records in the table cn_comm_lines_api_all */
LOOP
FETCH c_credited_txn_cur
BULK COLLECT INTO l_trans_object_id_tbl
, l_terr_id_tbl
, l_terr_name_tbl
, l_salesrep_id_tbl
, l_emp_no_tbl
, l_role_id_tbl
, l_del_flag_tbl
, l_split_pctg_tbl
, l_rev_type_tbl LIMIT g_fetch_limit;
/* insert the credited txns into api table */
insert_api_txns(
p_org_id => p_org_id
, p_trans_object_id_tbl => l_trans_object_id_tbl
, p_salesrep_id_tbl => l_salesrep_id_tbl
, p_emp_no_tbl => l_emp_no_tbl
, p_role_id_tbl => l_role_id_tbl
, p_split_pctg_tbl => l_split_pctg_tbl
, p_rev_type_tbl => l_rev_type_tbl
, p_terr_id_tbl => l_terr_id_tbl
, p_terr_name_tbl => l_terr_name_tbl
, p_del_flag_tbl => l_del_flag_tbl
, errbuf => errbuf
, retcode => retcode
);
debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns has failed');
debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns completed successfully');
INSERT INTO cn_comm_lines_api_all
(
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
, terr_id
, terr_name
)
SELECT ccla.salesrep_id
, ccla.processed_date
, ccla.processed_period_id
, -1 * NVL(ccla.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
, NULL
, NULL
, NULL
, ccla.rollup_date
, NULL
, ccla.source_doc_type
, g_user_id
, g_sysdate
, g_user_id
, g_sysdate
, g_login_id
, ccla.transaction_currency_code
, ccla.exchange_rate
, -1 * NVL(ccla.acctd_transaction_amount, 0)
, NULL
, NULL
, NULL
, -1 * 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
, 'Y'
, ccla.customer_id
, ccla.inventory_item_id
, ccla.order_number
, ccla.booked_date
, ccla.invoice_number
, ccla.invoice_date
, g_sysdate
, g_user_id
, ccla.revenue_type
, ccla.adjust_rollup_flag
, 'Created by TAE'
, 'REVERSAL'
, 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
, 'Y'
, ccha.commission_header_id
, ccla.sales_channel
, ccla.object_version_number
, ccla.split_pct
, ccla.split_status
, ccla.org_id
, ccla.terr_id
, ccla.terr_name
FROM cn_comm_lines_api ccla, cn_commission_headers_all ccha
WHERE ccla.ROWID = p_rowid_tbl(i)
AND ccha.comm_lines_api_id = ccla.comm_lines_api_id
AND (ccha.adjust_status NOT IN('FROZEN', 'REVERSAL')) --OR(adjust_status IS NULL))
AND ccha.trx_type NOT IN('ITD', 'GRP', 'THR');
/* update the corresponding records in commission_headers */
FORALL i IN p_api_id_tbl.FIRST .. p_api_id_tbl.LAST
UPDATE cn_commission_headers
SET adjust_status = 'FROZEN'
, reversal_header_id =
(SELECT commission_header_id
FROM cn_commission_headers_all
WHERE comm_lines_api_id = p_api_id_tbl(i)
AND (adjust_status NOT IN('FROZEN', 'REVERSAL'))-- OR(adjust_status IS NULL))
AND trx_type NOT IN('ITD', 'GRP', 'THR'))
, reversal_flag = 'Y'
, adjust_date = g_sysdate
, adjusted_by = g_user_id
, adjust_comments = 'Created by SCA'
, last_update_date = g_sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
WHERE comm_lines_api_id = p_api_id_tbl(i);
/* This procedure deletes the child transaction records */
/* from api table which have not been loaded for calculation */
PROCEDURE handle_unloaded_txns(
l_unloaded_txn_tbl IN OUT NOCOPY g_rowid_tbl_type
, p_rowid IN ROWID
, p_update_flag IN BOOLEAN
, errbuf IN OUT NOCOPY VARCHAR2
, retcode IN OUT NOCOPY VARCHAR2
) IS
l_no_of_records NUMBER;
/* "g_fetch_limit" or if the procedure is called exclusively to update the table */
IF (l_no_of_records > 0) THEN
IF ((l_no_of_records >= g_fetch_limit) OR(p_update_flag)) THEN
FORALL i IN l_unloaded_txn_tbl.FIRST .. l_unloaded_txn_tbl.LAST
DELETE cn_comm_lines_api_all
WHERE ROWID = l_unloaded_txn_tbl(i);
, p_update_flag IN BOOLEAN
, errbuf IN OUT NOCOPY VARCHAR2
, retcode IN OUT NOCOPY VARCHAR2
) IS
l_no_of_records NUMBER;
/* "g_fetch_limit" or if the procedure is called exclusively to update the table */
IF (l_no_of_records > 0) THEN
IF ((l_no_of_records >= g_fetch_limit) OR(p_update_flag)) THEN
api_negate_record(
p_api_id_tbl => l_loaded_txn_comid_tbl
, p_rowid_tbl => l_loaded_txn_rowid_tbl
, errbuf => errbuf
, retcode => retcode
);
SELECT ROWID
, comm_lines_api_id
, load_status
, salesrep_id
, transaction_amount
, role_id
, terr_id
, split_pct
, revenue_type
FROM cn_comm_lines_api_all
WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
AND adjust_status NOT IN('FROZEN', 'REVERSAL')
START WITH COMM_LINES_API_ID = p_api_id
CONNECT BY PRIOR COMM_LINES_API_ID = ADJ_COMM_LINES_API_ID;
SELECT count(*)
FROM cn_comm_lines_api_all
WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
AND salesrep_id= p_salesrep_id
AND transaction_amount = -1*p_transaction_amount
AND NVL(role_id, -1) = p_role_id
AND terr_id = p_terr_id
AND split_pct= p_split_pct
AND revenue_type =p_revenue_type
START WITH COMM_LINES_API_ID = p_api_id
CONNECT BY PRIOR COMM_LINES_API_ID = ADJ_COMM_LINES_API_ID;
FOR 'SELECT /*+ leading ( a ) cardinality ( a , 100 ) use_nl(a e.s e.re.b) */ d.rowid, '
|| ' d.comm_lines_api_id, '
|| ' a.terr_id, '
|| ' c.name, '
|| ' e.salesrep_id, '
|| ' e.employee_number, '
|| ' a.role_id, '
|| ' d.transaction_amount, '
|| ' b.'
|| l_ffname_split_pctg
|| ', '
|| ' b.'
|| l_ffname_rev_type
|| ', '
|| ' ''N'', '
|| ' count(*) over(partition by d.comm_lines_api_id) '
|| 'FROM jtf_tae_1001_sc_winners a, '
|| ' jtf_terr_rsc_all b, '
|| ' jtf_terr_all c, '
|| ' cn_comm_lines_api_all d, '
|| ' cn_salesreps e '
|| 'WHERE a.terr_rsc_id = b.terr_rsc_id '
|| 'AND a.terr_id = c.terr_id '
|| 'AND a.trans_object_id = d.comm_lines_api_id '
|| 'AND a.resource_id = e.resource_id '
|| 'AND a.worker_id = '||p_worker_id
|| 'ORDER BY d.comm_lines_api_id ';
/* and insert the records in the table cn_comm_lines_api_all */
LOOP
FETCH c_credited_txn_cur
BULK COLLECT INTO l_rowid_tbl
, l_api_id_tbl
, l_terr_id_tbl
, l_terr_name_tbl
, l_salesrep_id_tbl
, l_emp_no_tbl
, l_role_id_tbl
, l_txn_amt_tbl
, l_split_pctg_tbl
, l_rev_type_tbl
, l_del_flag_tbl
, l_no_of_credits_tbl LIMIT g_fetch_limit;
/* delete the child record from cn_comm_lines_api_all */
IF (l_child_load_status_tbl(i) <> 'LOADED') THEN
/* delete the row if it is not the same txn that we have processed */
IF (
(l_child_api_id_tbl(i) <> l_api_id_tbl(l_table_index))
AND (l_child_terr_id_tbl(i) IS NOT NULL) --IS NOT NULL
) THEN
/* start of code : logic used here is similar to used for loaded tansaction. Reference bug 7589796 */
l_match_found := FALSE;
/* if so, donot obsolete the child instead donot insert the new credited txn generated */
FOR j IN 1 .. l_no_of_credits_tbl(l_table_index) LOOP
l_temp_index := l_table_index +(j - 1);
/* update txn amt to -1 if user either has not specified anything for split pctg */
/* or has specified an invalid chaaracter (anything other than numbers) for it */
BEGIN
IF (l_split_pctg_tbl(l_temp_index) IS NULL) THEN
l_txn_amt := -1;
/* credited txn not to be inserted in the api table */
l_del_flag_tbl(l_temp_index) := 'Y';
, p_update_flag => FALSE
, errbuf => errbuf
, retcode => retcode
);
/* if so, donot obsolete the child instead donot insert the new credited txn generated */
FOR j IN 1 .. l_no_of_credits_tbl(l_table_index) LOOP
l_temp_index := l_table_index +(j - 1);
/* update txn amt to -1 if user either has not specified anything for split pctg */
/* or has specified an invalid chaaracter (anything other than numbers) for it */
BEGIN
IF (l_split_pctg_tbl(l_temp_index) IS NULL) THEN
l_txn_amt := -1;
/* credited txn not to be inserted in the api table */
--Modified the flow for bug 8538923
OPEN get_child_records_for_rev_txns (
l_child_api_id_tbl(i) ,
l_child_rev_type_tbl(i),
l_child_split_pctg_tbl(i),
l_child_terr_id_tbl(i),
nvl(l_child_role_id_tbl(i),-1),
l_child_txn_amt_tbl(i),
l_child_salesrep_id_tbl(i));
, p_update_flag => FALSE
, errbuf => errbuf
, retcode => retcode
);
p_rowid => NULL, p_update_flag => TRUE, errbuf => errbuf
, retcode => retcode);
, p_update_flag => TRUE
, errbuf => errbuf
, retcode => retcode
);
/* insert the credited txns into api table */
insert_api_txns(
p_org_id => p_org_id
, p_trans_object_id_tbl => l_api_id_tbl
, p_salesrep_id_tbl => l_salesrep_id_tbl
, p_emp_no_tbl => l_emp_no_tbl
, p_role_id_tbl => l_role_id_tbl
, p_split_pctg_tbl => l_split_pctg_tbl
, p_rev_type_tbl => l_rev_type_tbl
, p_terr_id_tbl => l_terr_id_tbl
, p_terr_name_tbl => l_terr_name_tbl
, p_del_flag_tbl => l_del_flag_tbl
, errbuf => errbuf
, retcode => retcode
);
debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns has failed');
debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns completed successfully');
SELECT COUNT(*)
INTO l_count
FROM cn_acc_period_statuses_v acc
WHERE TRUNC(l_start_date) BETWEEN TRUNC(acc.start_date) AND TRUNC(acc.end_date)
AND acc.period_status = 'O'
AND acc.org_id = p_org_id
AND ROWNUM = 1;
SELECT COUNT(*)
INTO l_count
FROM cn_acc_period_statuses_v acc
WHERE TRUNC(l_end_date) BETWEEN TRUNC(acc.start_date) AND TRUNC(acc.end_date)
AND acc.period_status = 'O'
AND acc.org_id = p_org_id
AND ROWNUM = 1;
SELECT TO_NUMBER(NVL(fnd_profile.value('CN_NUMBER_OF_WORKERS'),1)) INTO g_num_workers
FROM dual;
debugmsg('SCA : CN_SCATM_TAE_PUB.update_txns_processed has failed');
debugmsg('SCA : CN_SCATM_TAE_PUB.update_txns_processed has failed');
debugmsg('SCA : CN_SCATM_TAE_PUB.update_txns_processed completed successfully');
update_txns_processed(errbuf => errbuf, retcode => retcode,
p_worker_id => p_worker_id);
/* Get the criterion to select transactions from api table */
get_where_clause(
p_start_date => lp_start_date
, p_end_date => lp_end_date
, p_org_id => p_org_id
, p_run_mode => p_run_mode
, x_where_clause => l_where_clause
, errbuf => errbuf
, retcode => retcode
);
/* insert the selected transactions from cn_comm_lines_api_all table */
/* to the interface table jtf_tae_1001_sc_dea_trans */
jty_assign_bulk_pub.collect_trans_data(
p_api_version_number => 1.0
, p_init_msg_list => fnd_api.g_false
, p_source_id => -1001
, p_trans_id => -1002
, p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM'
, p_mode => 'DATE EFFECTIVE'
, p_where => l_where_clause
, p_no_of_workers => l_num_workers
, p_percent_analyzed => 20
, -- this value can be either a profile option or a parameter to conc program
p_request_id => p_request_id
, -- request id of the concurrent program
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, errbuf => errbuf
, retcode => retcode
, p_oic_mode => 'INSERT'
);
debugmsg('SCA : CN_SCATM_TAE_PUB.get_credited_txns for INSERT has failed');
debugmsg('SCA : CN_SCATM_TAE_PUB.batch_collect_txns with oic_mode INSERT completed successfully');
/* Get the criterion to select transactions from api table */
debugmsg('SCA : Populating data to WINNERS table for worker_id '||p_worker_id ||' and mode '||
p_oic_mode);
RETURN cn_sca_insert_tbl_type IS
BEGIN
RETURN g_sca_insert_tbl_type;