[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:
1194:
1195: -- Get individual physical batch id
1196: CURSOR physical_batches(l_logical_batch_id NUMBER) IS
1197: SELECT DISTINCT physical_batch_id
1198: FROM cn_process_batches
1199: WHERE logical_batch_id = l_logical_batch_id
1200: AND status_code = 'IN_USE';
1201:
1202: physical_rec physical_batches%ROWTYPE;
1383: END IF;
1384: END IF;
1385:
1386:
1387: -- Insert into cn_process_batches for each srp-period
1388: -- Populate logical_batch_id. One logical_batch_id for one load
1389: -- Physical_batch_id is still empty, will be populated in Assign
1390:
1391:
1389: -- Physical_batch_id is still empty, will be populated in Assign
1390:
1391:
1392: -- sequence s2 is for logical batch id
1393: SELECT cn_process_batches_s2.NEXTVAL
1394: INTO l_logical_batch_id
1395: FROM sys.dual;
1396:
1397:
1434: ELSE
1435: FOR i IN l_srp_id_tbl.FIRST .. l_srp_id_tbl.LAST LOOP
1436:
1437: -- sequence s1 is for process batch id
1438: SELECT cn_process_batches_s1.NEXTVAL
1439: INTO l_process_batch_id
1440: FROM sys.dual;
1441: debugmsg('Loader : insert into cn_process_batches....');
1442: debugmsg('l_process_batch_id = ' || l_process_batch_id);
1437: -- sequence s1 is for process batch id
1438: SELECT cn_process_batches_s1.NEXTVAL
1439: INTO l_process_batch_id
1440: FROM sys.dual;
1441: debugmsg('Loader : insert into cn_process_batches....');
1442: debugmsg('l_process_batch_id = ' || l_process_batch_id);
1443: debugmsg('l_logical_batch_id = '||l_logical_batch_id);
1444: debugmsg('period_id = '||l_period_id_tbl(i) );
1445: debugmsg('start_date = '||l_start_date_tbl(i));
1455: --+
1456:
1457: l_process_rec_cnt := l_process_rec_cnt + 1;
1458:
1459: INSERT INTO cn_process_batches_all
1460: ( process_batch_id
1461: ,logical_batch_id
1462: ,srp_period_id
1463: ,period_id
1532: --+
1533: IF l_process_rec_cnt > 0 THEN
1534:
1535: -- Split the logical batch into smaller physical batches
1536: -- populate the physical_batch_id in cn_process_batches
1537:
1538:
1539:
1540: cn_transaction_load_pkg.assign(p_logical_batch_id => l_logical_batch_id, p_org_id => p_org_id);