[Home] [Help]
33: FUNCTION get_physical_batch_id RETURN NUMBER IS
34: x_physical_batch_id NUMBER;
35: BEGIN
36: -- sequence s3 is for physical batch id
37: SELECT cn_process_batches_s3.nextval
38: INTO x_physical_batch_id
39: FROM sys.dual;
40:
41: RETURN x_physical_batch_id;
54:
55: BEGIN
56: -- Giving the batch an 'ERROR' status prevents subsequent
57: -- physical processes picking it up.
58: UPDATE cn_process_batches
59: SET status_code = 'ERROR'
60: ,last_update_date = sysdate
61: ,last_update_login = l_login_id
62: ,last_updated_by = l_user_id
92: start_date,
93: end_date,
94: sales_lines_total trx_count
95: FROM
96: cn_process_batches
97: WHERE
98: physical_batch_id = p_physical_batch_id AND
99: status_code = 'IN_USE';
100:
872: CURSOR l_headers IS
873: SELECT cch.commission_header_id, cch.reversal_flag, cch.reversal_header_id
874: FROM cn_commission_headers cch,
875: (SELECT DISTINCT salesrep_id
876: FROM cn_process_batches
877: WHERE physical_batch_id = p_physical_batch_id
878: AND status_code = 'IN_USE') pb
879: WHERE cch.direct_salesrep_id = pb.salesrep_id
880: AND cch.commission_header_id > l_init_commission_header_id;
1031: -- Version : Current version 1.0
1032: -- Initial version 1.0
1033: -- Notes
1034: -- Description : This procedure loads trx from CN_COMM_LINES_API to
1035: -- CN_COMMISSION_HEADERS, update cn_process_batches,
1036: -- and perform trx classification, and trx rollup.
1037: --
1038: -- Special Notes : This public API will load trx sequentially instead
1039: -- of submitting concurrent process to load trx in parallel
1041: -- End of comments
1042:
1043:
1044: -- Call begin_batch to get process_audit_id
1045: -- Insert into cn_process_batches, populate logical_batch_id
1046: -- Call Assign : populate physical_batch_id
1047: -- Call Conc_dispatch : For each physical_batch_id call load_worker.
1048: -- Note that this public API will load trx sequentially instead of submitting
1049: -- concurrent process to load trx in parallel as the regular loader does
1118:
1119: -- get the number of valid transactions to load
1120: CURSOR valid_transactions (p_logical_batch_id NUMBER) IS
1121: SELECT salesrep_id, SUM(sales_lines_total) srp_trx_count
1122: FROM cn_process_batches
1123: WHERE logical_batch_id = p_logical_batch_id
1124: AND status_code = 'IN_USE'
1125: GROUP BY salesrep_id;
1126:
1192:
1193: -- Get individual physical batch id
1194: CURSOR physical_batches(l_logical_batch_id NUMBER) IS
1195: SELECT DISTINCT physical_batch_id
1196: FROM cn_process_batches
1197: WHERE logical_batch_id = l_logical_batch_id
1198: AND status_code = 'IN_USE';
1199:
1200: physical_rec physical_batches%ROWTYPE;
1381: END IF;
1382: END IF;
1383:
1384:
1385: -- Insert into cn_process_batches for each srp-period
1386: -- Populate logical_batch_id. One logical_batch_id for one load
1387: -- Physical_batch_id is still empty, will be populated in Assign
1388:
1389:
1387: -- Physical_batch_id is still empty, will be populated in Assign
1388:
1389:
1390: -- sequence s2 is for logical batch id
1391: SELECT cn_process_batches_s2.NEXTVAL
1392: INTO l_logical_batch_id
1393: FROM sys.dual;
1394:
1395:
1432: ELSE
1433: FOR i IN l_srp_id_tbl.FIRST .. l_srp_id_tbl.LAST LOOP
1434:
1435: -- sequence s1 is for process batch id
1436: SELECT cn_process_batches_s1.NEXTVAL
1437: INTO l_process_batch_id
1438: FROM sys.dual;
1439: debugmsg('Loader : insert into cn_process_batches....');
1440: debugmsg('l_process_batch_id = ' || l_process_batch_id);
1435: -- sequence s1 is for process batch id
1436: SELECT cn_process_batches_s1.NEXTVAL
1437: INTO l_process_batch_id
1438: FROM sys.dual;
1439: debugmsg('Loader : insert into cn_process_batches....');
1440: debugmsg('l_process_batch_id = ' || l_process_batch_id);
1441: debugmsg('l_logical_batch_id = '||l_logical_batch_id);
1442: debugmsg('period_id = '||l_period_id_tbl(i) );
1443: debugmsg('start_date = '||l_start_date_tbl(i));
1453: --+
1454:
1455: l_process_rec_cnt := l_process_rec_cnt + 1;
1456:
1457: INSERT INTO cn_process_batches_all
1458: ( process_batch_id
1459: ,logical_batch_id
1460: ,srp_period_id
1461: ,period_id
1530: --+
1531: IF l_process_rec_cnt > 0 THEN
1532:
1533: -- Split the logical batch into smaller physical batches
1534: -- populate the physical_batch_id in cn_process_batches
1535:
1536:
1537:
1538: cn_transaction_load_pkg.assign(p_logical_batch_id => l_logical_batch_id, p_org_id => p_org_id);