[Home] [Help]
33: g_fetch_limit NUMBER := 10000;
34:
35: TYPE g_rowid_tbl_type IS TABLE OF ROWID;
36:
37: TYPE g_comm_lines_api_id_tbl_type IS TABLE OF cn_comm_lines_api_all.comm_lines_api_id%TYPE;
38:
39: TYPE g_trans_object_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.trans_object_id%TYPE;
40:
41: TYPE g_terr_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.terr_id%TYPE;
842: /* Start of the new crediting process */
843: /**************************************/
844:
845: /* This procedure returns the appropiate where clause to select */
846: /* data from the table cn_comm_lines_api_all depending on run mode */
847: PROCEDURE get_where_clause(
848: p_start_date IN DATE
849: , p_end_date IN DATE
850: , p_org_id IN NUMBER
904: x_where_clause :=
905: x_where_clause
906: || 'AND NOT EXISTS ( '
907: || ' SELECT /*+ NO_UNNEST */ 1 '
908: || ' FROM cn_comm_lines_api_all '
909: || ' WHERE adj_comm_lines_api_id = trans_object_id )';
910: END IF;
911:
912: debugmsg('SCA : where clause : ' || x_where_clause);
1016: retcode := 0;
1017:
1018: /* mark the transactions in the api table as CREDITED */
1019: /* for which territory manager has returned a valid credited txn */
1020: UPDATE /*+ parallel(cla) */ cn_comm_lines_api_all cla
1021: SET load_status = 'CREDITED', adjust_status = 'SCA_ALLOCATED'
1022: WHERE comm_lines_api_id IN (
1023: SELECT /*+ parallel(a) leading(a) use_nl(b) cardinality(a,1) */ trans_object_id
1024: FROM jtf_tae_1001_sc_winners a, cn_comm_lines_api_all b
1020: UPDATE /*+ parallel(cla) */ cn_comm_lines_api_all cla
1021: SET load_status = 'CREDITED', adjust_status = 'SCA_ALLOCATED'
1022: WHERE comm_lines_api_id IN (
1023: SELECT /*+ parallel(a) leading(a) use_nl(b) cardinality(a,1) */ trans_object_id
1024: FROM jtf_tae_1001_sc_winners a, cn_comm_lines_api_all b
1025: WHERE b.adj_comm_lines_api_id = a.trans_object_id
1026: AND b.terr_id IS NOT NULL
1027: AND a.worker_id =p_worker_id
1028: );
1067: IF (l_no_of_records > 0) THEN
1068: /* insert the credited transactions into api table */
1069: /* process all the rows even if some of them fail */
1070: FORALL i IN p_trans_object_id_tbl.FIRST .. p_trans_object_id_tbl.LAST SAVE EXCEPTIONS
1071: INSERT INTO cn_comm_lines_api_all
1072: (
1073: salesrep_id
1074: , processed_date
1075: , processed_period_id
1422: , ccla.split_status
1423: , ccla.org_id
1424: , p_terr_id_tbl(i)
1425: , p_terr_name_tbl(i)
1426: FROM cn_comm_lines_api_all ccla
1427: WHERE ccla.comm_lines_api_id = p_trans_object_id_tbl(i)
1428: AND ccla.org_id = p_org_id
1429: AND p_del_flag_tbl(i) <> 'Y';
1430: END IF;
1483: THEN
1484: l_num_workers := 1;
1485: END IF;
1486:
1487: /* insert the selected transactions from cn_comm_lines_api_all table */
1488: /* to the interface table jtf_tae_1001_sc_dea_trans */
1489: jty_assign_bulk_pub.collect_trans_data
1490: (
1491: p_api_version_number => 1.0
1558: errbuf := 'CN_SCATM_TAE_PUB.get_credited_txns.others';
1559: END get_credited_txns;
1560:
1561: /* This procedure gets the winning salesreps, split percentages and revenue types from the */
1562: /* table jtf_tae_1001_sc_winners and create credited transactions in the table cn_comm_lines_api_all */
1563: PROCEDURE process_new_txns(
1564: p_org_id IN NUMBER
1565: , p_worker_id IN NUMBER
1566: , errbuf IN OUT NOCOPY VARCHAR2
1625: || 'AND a.resource_id = d.resource_id '
1626: || 'AND a.worker_id = '||p_worker_id;
1627:
1628: /* loop through the winning resources in batches , "g_fetch_limit" records per batch, */
1629: /* and insert the records in the table cn_comm_lines_api_all */
1630: LOOP
1631: FETCH c_credited_txn_cur
1632: BULK COLLECT INTO l_trans_object_id_tbl
1633: , l_terr_id_tbl
1690: END process_new_txns;
1691:
1692: /* This procedure does the following for txns that have been loaded for calc */
1693: /* -- obsolete the corresponding record in cn_commission_headers_all */
1694: /* -- create a reversal entry in cn_comm_lines_api_all */
1695: PROCEDURE api_negate_record(
1696: p_api_id_tbl IN OUT NOCOPY g_comm_lines_api_id_tbl_type
1697: , p_rowid_tbl IN OUT NOCOPY g_rowid_tbl_type
1698: , errbuf IN OUT NOCOPY VARCHAR2
1708: END IF;
1709:
1710: /* create the reversal entry in api table */
1711: FORALL i IN p_rowid_tbl.FIRST .. p_rowid_tbl.LAST
1712: INSERT INTO cn_comm_lines_api_all
1713: (
1714: salesrep_id
1715: , processed_date
1716: , processed_period_id
2124: /* "g_fetch_limit" or if the procedure is called exclusively to update the table */
2125: IF (l_no_of_records > 0) THEN
2126: IF ((l_no_of_records >= g_fetch_limit) OR(p_update_flag)) THEN
2127: FORALL i IN l_unloaded_txn_tbl.FIRST .. l_unloaded_txn_tbl.LAST
2128: DELETE cn_comm_lines_api_all
2129: WHERE ROWID = l_unloaded_txn_tbl(i);
2130: l_unloaded_txn_tbl.TRIM(l_no_of_records);
2131: END IF;
2132: END IF;
2203: errbuf := 'CN_SCATM_TAE_PUB.handle_loaded_txns.others';
2204: END handle_loaded_txns;
2205:
2206: /* This procedure gets the winning salesreps, split percentages and revenue types from the */
2207: /* table jtf_tae_1001_sc_winners and create credited transactions in the table cn_comm_lines_api_all */
2208: PROCEDURE process_all_txns(
2209: p_org_id IN NUMBER
2210: , p_worker_id IN NUMBER
2211: , errbuf IN OUT NOCOPY VARCHAR2
2212: , retcode IN OUT NOCOPY VARCHAR2
2213: ) IS
2214: TYPE l_credited_txn_curtyp IS REF CURSOR;
2215:
2216: TYPE l_txn_amt_tbl_type IS TABLE OF cn_comm_lines_api_all.transaction_amount%TYPE;
2217:
2218: TYPE l_no_of_credits_tbl_type IS TABLE OF NUMBER;
2219:
2220: TYPE l_child_load_status_tbl_type IS TABLE OF cn_comm_lines_api_all.load_status%TYPE;
2216: TYPE l_txn_amt_tbl_type IS TABLE OF cn_comm_lines_api_all.transaction_amount%TYPE;
2217:
2218: TYPE l_no_of_credits_tbl_type IS TABLE OF NUMBER;
2219:
2220: TYPE l_child_load_status_tbl_type IS TABLE OF cn_comm_lines_api_all.load_status%TYPE;
2221:
2222: c_credited_txn_cur l_credited_txn_curtyp;
2223: l_ffname_split_pctg VARCHAR2(15);
2224: l_ffname_rev_type VARCHAR2(15);
2303: || ' count(*) over(partition by d.comm_lines_api_id) '
2304: || 'FROM jtf_tae_1001_sc_winners a, '
2305: || ' jtf_terr_rsc_all b, '
2306: || ' jtf_terr_all c, '
2307: || ' cn_comm_lines_api_all d, '
2308: || ' cn_salesreps e '
2309: || 'WHERE a.terr_rsc_id = b.terr_rsc_id '
2310: || 'AND a.terr_id = c.terr_id '
2311: || 'AND a.trans_object_id = d.comm_lines_api_id '
2312: || 'AND a.resource_id = e.resource_id '
2313: || 'AND a.worker_id = '||p_worker_id;
2314:
2315: /* loop through the winning resources in batches , "g_fetch_limit" records per batch, */
2316: /* and insert the records in the table cn_comm_lines_api_all */
2317: LOOP
2318: FETCH c_credited_txn_cur
2319: BULK COLLECT INTO l_rowid_tbl
2320: , l_api_id_tbl
2419: , l_child_role_id_tbl
2420: , l_child_terr_id_tbl
2421: , l_child_split_pctg_tbl
2422: , l_child_rev_type_tbl
2423: FROM cn_comm_lines_api_all
2424: WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
2425: AND ((adjust_status IS NULL) OR(adjust_status NOT IN('FROZEN', 'REVERSAL')))
2426: START WITH comm_lines_api_id = l_api_id_tbl(l_table_index)
2427: CONNECT BY PRIOR comm_lines_api_id = adj_comm_lines_api_id;
2430: FOR i IN l_child_rowid_tbl.FIRST .. l_child_rowid_tbl.LAST LOOP
2431: debugmsg('SCA : Now processing child transaction with id : ' || l_child_api_id_tbl(i));
2432:
2433: /* if the child has not been loaded for calculation */
2434: /* delete the child record from cn_comm_lines_api_all */
2435: IF (l_child_load_status_tbl(i) <> 'LOADED') THEN
2436: /* delete the row if it is not the same txn that we have processed */
2437: IF (
2438: (l_child_api_id_tbl(i) <> l_api_id_tbl(l_table_index))