[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 ';
/* 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 IS NULL OR preserve_credit_override_flag <> ''Y'') ';
/* only the txns which are not processed previously by crediting process are selected in NEW mode */
x_where_clause :=
x_where_clause
|| 'AND NOT EXISTS ( '
|| ' SELECT /*+ NO_UNNEST */ 1 '
|| ' FROM cn_comm_lines_api_all '
|| ' WHERE adj_comm_lines_api_id = trans_object_id )';
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 /*+ parallel(cla) */ cn_comm_lines_api_all cla
SET load_status = 'CREDITED', adjust_status = 'SCA_ALLOCATED'
WHERE comm_lines_api_id IN (
SELECT /*+ parallel(a) leading(a) use_nl(b) cardinality(a,1) */ trans_object_id
FROM jtf_tae_1001_sc_winners a, cn_comm_lines_api_all b
WHERE b.adj_comm_lines_api_id = a.trans_object_id
AND b.terr_id IS NOT NULL
AND a.worker_id =p_worker_id
);
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);
/* insert the credited transactions into api table */
/* process all the rows even if some of them fail */
FORALL i IN p_trans_object_id_tbl.FIRST .. p_trans_object_id_tbl.LAST SAVE EXCEPTIONS
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 p_salesrep_id_tbl(i)
, ccla.processed_date
, ccla.processed_period_id
, ROUND(NVL((ccla.transaction_amount * p_split_pctg_tbl(i)) / 100, 0), 2)
, 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)
, 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)
, ccla.comp_group_id
, ccla.commission_amount
, p_emp_no_tbl(i)
, ccla.reversal_flag
, ccla.reversal_header_id
, ccla.sales_channel
, ccla.object_version_number
, p_split_pctg_tbl(i)
, ccla.split_status
, ccla.org_id
, p_terr_id_tbl(i)
, p_terr_name_tbl(i)
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');
debugmsg('SCA : Unexpected exception in insert_api_txns');
errbuf := 'CN_SCATM_TAE_PUB.insert_api_txns.others';
END insert_api_txns;
SELECT TO_NUMBER(NVL(fnd_profile.value('CN_NUMBER_OF_WORKERS'),1))
INTO l_num_workers
FROM dual;
/* 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 => p_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
);
/* Cursor definition to select all winning resources from winners table */
OPEN c_credited_txn_cur
FOR 'SELECT 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(ccha.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
);
FOR 'SELECT 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;
/* 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;
SELECT ROWID
, comm_lines_api_id
, load_status
, salesrep_id
, transaction_amount
, role_id
, terr_id
, split_pct
, revenue_type
BULK COLLECT INTO l_child_rowid_tbl
, l_child_api_id_tbl
, l_child_load_status_tbl
, l_child_salesrep_id_tbl
, l_child_txn_amt_tbl
, l_child_role_id_tbl
, l_child_terr_id_tbl
, l_child_split_pctg_tbl
, l_child_rev_type_tbl
FROM cn_comm_lines_api_all
WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
AND ((adjust_status IS NULL) OR(adjust_status NOT IN('FROZEN', 'REVERSAL')))
START WITH comm_lines_api_id = l_api_id_tbl(l_table_index)
CONNECT BY PRIOR comm_lines_api_id = adj_comm_lines_api_id;
/* 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)
) 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 */
l_del_flag_tbl(l_temp_index) := 'Y';
, 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;
/* Get the criterion to select transactions from api table */
get_where_clause(
p_start_date => l_start_date
, p_end_date => l_end_date
, p_org_id => p_org_id
, p_run_mode => p_run_mode
, x_where_clause => l_where_clause
, errbuf => errbuf
, retcode => retcode
);
SELECT TO_NUMBER(NVL(fnd_profile.value('CN_NUMBER_OF_WORKERS'),1)) INTO l_num_workers
FROM dual;
/* update the txns processed in api table */
-- update_txns_processed(errbuf => errbuf, retcode => retcode);
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);