[Home] [Help]
34:
35:
36: TYPE g_rowid_tbl_type IS TABLE OF ROWID;
37:
38: TYPE g_comm_lines_api_id_tbl_type IS TABLE OF cn_comm_lines_api_all.comm_lines_api_id%TYPE;
39:
40: TYPE g_trans_object_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.trans_object_id%TYPE;
41:
42: TYPE g_terr_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.terr_id%TYPE;
388: TYPE divider_type IS TABLE OF NUMBER;
389:
390: query_cur rc;
391: i NUMBER;
392: l_header_rec cn_comm_lines_api%ROWTYPE;
393: l_lines_output_id cn_sca_lines_output.sca_lines_output_id%TYPE;
394: l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
395: l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
396: l_source_id cn_sca_headers_interface.source_id%TYPE;
391: i NUMBER;
392: l_header_rec cn_comm_lines_api%ROWTYPE;
393: l_lines_output_id cn_sca_lines_output.sca_lines_output_id%TYPE;
394: l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
395: l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
396: l_source_id cn_sca_headers_interface.source_id%TYPE;
397: l_order_number cn_comm_lines_api.order_number%TYPE;
398: l_invoice_number cn_comm_lines_api.invoice_number%TYPE;
399: l_id NUMBER;
393: l_lines_output_id cn_sca_lines_output.sca_lines_output_id%TYPE;
394: l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
395: l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
396: l_source_id cn_sca_headers_interface.source_id%TYPE;
397: l_order_number cn_comm_lines_api.order_number%TYPE;
398: l_invoice_number cn_comm_lines_api.invoice_number%TYPE;
399: l_id NUMBER;
400: l_logical_batch_size NUMBER;
401: l_worker_num NUMBER;
394: l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
395: l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
396: l_source_id cn_sca_headers_interface.source_id%TYPE;
397: l_order_number cn_comm_lines_api.order_number%TYPE;
398: l_invoice_number cn_comm_lines_api.invoice_number%TYPE;
399: l_id NUMBER;
400: l_logical_batch_size NUMBER;
401: l_worker_num NUMBER;
402: l_physical_batch_size NUMBER;
920: /* Start of the new crediting process */
921: /**************************************/
922:
923: /* This procedure returns the appropiate where clause to select */
924: /* data from the table cn_comm_lines_api_all depending on run mode */
925: PROCEDURE get_where_clause(
926: p_start_date IN DATE
927: , p_end_date IN DATE
928: , p_org_id IN NUMBER
957:
958: IF( p_run_mode = 'INCREMENTAL' ) THEN
959: x_where_clause := x_where_clause || ' AND trans_object_id IN (
960: SELECT comm_lines_api_id
961: FROM cn_comm_lines_api_all
962: WHERE load_status NOT IN(''OBSOLETE'', ''FILTERED'')
963: AND adjust_status NOT IN(''FROZEN'', ''REVERSAL'')
964: START WITH COMM_LINES_API_ID IN (SELECT adj_comm_lines_api_id from cn_comm_lines_api_all
965: WHERE terr_id IN (
960: SELECT comm_lines_api_id
961: FROM cn_comm_lines_api_all
962: WHERE load_status NOT IN(''OBSOLETE'', ''FILTERED'')
963: AND adjust_status NOT IN(''FROZEN'', ''REVERSAL'')
964: START WITH COMM_LINES_API_ID IN (SELECT adj_comm_lines_api_id from cn_comm_lines_api_all
965: WHERE terr_id IN (
966: SELECT jcdt.terr_id
967: FROM jty_conc_req_summ jcrs, jty_changed_dea_terrs jcdt
968: WHERE jcrs.program_name = ''JTY_STAR''
1093:
1094: /* mark the transactions in the api table as CREDITED */
1095: /* for which territory manager has returned a valid credited txn */
1096:
1097: UPDATE CN_COMM_LINES_API_ALL CLA
1098: SET LOAD_STATUS = 'CREDITED', ADJUST_STATUS = 'SCA_ALLOCATED'
1099: WHERE COMM_LINES_API_ID IN
1100: ( SELECT /*+ cardinality(a,1) */ TRANS_OBJECT_ID
1101: FROM (
1105: ) A
1106: WHERE EXISTS
1107: (
1108: select /*+ no_unest */ 1
1109: from CN_COMM_LINES_API_ALL B
1110: where B.ADJ_COMM_LINES_API_ID = A.TRANS_OBJECT_ID
1111: AND B.TERR_ID IS NOT NULL
1112: )
1113: );
1170: -- , p_del_flag_tbl(i));
1171: -- END LOOP;
1172:
1173: FORALL i IN p_trans_object_id_tbl.FIRST .. p_trans_object_id_tbl.LAST SAVE EXCEPTIONS
1174: INSERT INTO cn_comm_lines_api_all
1175: (
1176: salesrep_id
1177: , processed_date
1178: , processed_period_id
1458: , ccla.attribute97
1459: , ccla.attribute98
1460: , ccla.attribute99
1461: , ccla.attribute100
1462: , cn_comm_lines_api_s.NEXTVAL
1463: , ccla.conc_batch_id
1464: , ccla.process_batch_id
1465: , NULL
1466: , ccla.rollup_date
1527: , ccla.org_id
1528: , p_terr_id_tbl(i) -- parent.terr_id
1529: , p_terr_name_tbl(i) -- parent.terr_name
1530: , 'N' -- to ensure preserve_credit_override_flag is not null
1531: FROM cn_comm_lines_api_all ccla
1532: WHERE ccla.comm_lines_api_id = p_trans_object_id_tbl(i)
1533: AND ccla.org_id = p_org_id
1534: AND p_del_flag_tbl(i) <> 'Y';
1535:
1653: , errbuf => errbuf
1654: , retcode => retcode
1655: );
1656:
1657: /* insert the selected transactions from cn_comm_lines_api_all table */
1658: /* to the interface table jtf_tae_1001_sc_dea_trans */
1659: jty_assign_bulk_pub.collect_trans_data(
1660: p_api_version_number => 1.0
1661: , p_init_msg_list => fnd_api.g_false
1760: THEN
1761: raise fnd_api.g_exc_error;
1762: END IF;
1763:
1764: /* insert the selected transactions from cn_comm_lines_api_all table */
1765: /* to the interface table jtf_tae_1001_sc_dea_trans */
1766: jty_assign_bulk_pub.collect_trans_data(
1767: p_api_version_number => 1.0
1768: , p_init_msg_list => fnd_api.g_false
1933: errbuf := 'CN_SCATM_TAE_PUB.get_credited_txns.others';
1934: END get_credited_txns;
1935:
1936: /* This procedure gets the winning salesreps, split percentages and revenue types from the */
1937: /* table jtf_tae_1001_sc_winners and create credited transactions in the table cn_comm_lines_api_all */
1938: PROCEDURE process_new_txns(
1939: p_org_id IN NUMBER
1940: , p_worker_id IN NUMBER
1941: , errbuf IN OUT NOCOPY VARCHAR2
2000: || 'AND a.resource_id = d.resource_id '
2001: || 'AND a.worker_id = '||p_worker_id;
2002:
2003: /* loop through the winning resources in batches , "g_fetch_limit" records per batch, */
2004: /* and insert the records in the table cn_comm_lines_api_all */
2005: LOOP
2006: FETCH c_credited_txn_cur
2007: BULK COLLECT INTO l_trans_object_id_tbl
2008: , l_terr_id_tbl
2065: END process_new_txns;
2066:
2067: /* This procedure does the following for txns that have been loaded for calc */
2068: /* -- obsolete the corresponding record in cn_commission_headers_all */
2069: /* -- create a reversal entry in cn_comm_lines_api_all */
2070: PROCEDURE api_negate_record(
2071: p_api_id_tbl IN OUT NOCOPY g_comm_lines_api_id_tbl_type
2072: , p_rowid_tbl IN OUT NOCOPY g_rowid_tbl_type
2073: , errbuf IN OUT NOCOPY VARCHAR2
2083: END IF;
2084:
2085: /* create the reversal entry in api table */
2086: FORALL i IN p_rowid_tbl.FIRST .. p_rowid_tbl.LAST
2087: INSERT INTO cn_comm_lines_api_all
2088: (
2089: salesrep_id
2090: , processed_date
2091: , processed_period_id
2370: , ccla.attribute97
2371: , ccla.attribute98
2372: , ccla.attribute99
2373: , ccla.attribute100
2374: , cn_comm_lines_api_s.NEXTVAL
2375: , NULL
2376: , NULL
2377: , NULL
2378: , ccla.rollup_date
2438: , ccla.split_status
2439: , ccla.org_id
2440: , ccla.terr_id
2441: , ccla.terr_name
2442: FROM cn_comm_lines_api ccla, cn_commission_headers_all ccha
2443: WHERE ccla.ROWID = p_rowid_tbl(i)
2444: AND ccha.comm_lines_api_id = ccla.comm_lines_api_id
2445: AND (ccha.adjust_status NOT IN('FROZEN', 'REVERSAL')) --OR(adjust_status IS NULL))
2446: AND ccha.trx_type NOT IN('ITD', 'GRP', 'THR');
2499: /* "g_fetch_limit" or if the procedure is called exclusively to update the table */
2500: IF (l_no_of_records > 0) THEN
2501: IF ((l_no_of_records >= g_fetch_limit) OR(p_update_flag)) THEN
2502: FORALL i IN l_unloaded_txn_tbl.FIRST .. l_unloaded_txn_tbl.LAST
2503: DELETE cn_comm_lines_api_all
2504: WHERE ROWID = l_unloaded_txn_tbl(i);
2505: l_unloaded_txn_tbl.TRIM(l_no_of_records);
2506: END IF;
2507: END IF;
2578: errbuf := 'CN_SCATM_TAE_PUB.handle_loaded_txns.others';
2579: END handle_loaded_txns;
2580:
2581: /* This procedure gets the winning salesreps, split percentages and revenue types from the */
2582: /* table jtf_tae_1001_sc_winners and create credited transactions in the table cn_comm_lines_api_all */
2583: PROCEDURE process_all_txns(
2584: p_org_id IN NUMBER
2585: , p_worker_id IN NUMBER
2586: , errbuf IN OUT NOCOPY VARCHAR2
2587: , retcode IN OUT NOCOPY VARCHAR2
2588: ) IS
2589: TYPE l_credited_txn_curtyp IS REF CURSOR;
2590:
2591: TYPE l_txn_amt_tbl_type IS TABLE OF cn_comm_lines_api_all.transaction_amount%TYPE;
2592:
2593: TYPE l_no_of_credits_tbl_type IS TABLE OF NUMBER;
2594:
2595: TYPE l_child_load_status_tbl_type IS TABLE OF cn_comm_lines_api_all.load_status%TYPE;
2591: TYPE l_txn_amt_tbl_type IS TABLE OF cn_comm_lines_api_all.transaction_amount%TYPE;
2592:
2593: TYPE l_no_of_credits_tbl_type IS TABLE OF NUMBER;
2594:
2595: TYPE l_child_load_status_tbl_type IS TABLE OF cn_comm_lines_api_all.load_status%TYPE;
2596:
2597: c_credited_txn_cur l_credited_txn_curtyp;
2598: l_ffname_split_pctg VARCHAR2(15);
2599: l_ffname_rev_type VARCHAR2(15);
2643: -- , role_id
2644: -- , terr_id
2645: -- , split_pct
2646: -- , revenue_type
2647: -- FROM cn_comm_lines_api_all
2648: -- WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
2649: -- AND adjust_status NOT IN('FROZEN', 'REVERSAL')
2650: -- AND comm_lines_api_id = p_api_id
2651: -- UNION ALL
2657: , role_id
2658: , terr_id
2659: , split_pct
2660: , revenue_type
2661: FROM cn_comm_lines_api_all
2662: WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
2663: AND adjust_status NOT IN('FROZEN', 'REVERSAL')
2664: START WITH COMM_LINES_API_ID = p_api_id
2665: CONNECT BY PRIOR COMM_LINES_API_ID = ADJ_COMM_LINES_API_ID;
2666:
2667: --Added the cursor below for bug 8538923
2668: CURSOR get_child_records_for_rev_txns (
2669: p_api_id NUMBER,
2670: p_revenue_type cn_comm_lines_api_all.REVENUE_TYPE%TYPE,
2671: p_split_pct cn_comm_lines_api_all.SPLIT_PCT%TYPE,
2672: p_terr_id cn_comm_lines_api_all.TERR_ID%TYPE,
2673: p_role_id cn_comm_lines_api_all.ROLE_ID%TYPE,
2674: p_transaction_amount cn_comm_lines_api_all.TRANSACTION_AMOUNT%TYPE,
2667: --Added the cursor below for bug 8538923
2668: CURSOR get_child_records_for_rev_txns (
2669: p_api_id NUMBER,
2670: p_revenue_type cn_comm_lines_api_all.REVENUE_TYPE%TYPE,
2671: p_split_pct cn_comm_lines_api_all.SPLIT_PCT%TYPE,
2672: p_terr_id cn_comm_lines_api_all.TERR_ID%TYPE,
2673: p_role_id cn_comm_lines_api_all.ROLE_ID%TYPE,
2674: p_transaction_amount cn_comm_lines_api_all.TRANSACTION_AMOUNT%TYPE,
2675: p_salesrep_id cn_comm_lines_api_all.SALESREP_ID%TYPE
2668: CURSOR get_child_records_for_rev_txns (
2669: p_api_id NUMBER,
2670: p_revenue_type cn_comm_lines_api_all.REVENUE_TYPE%TYPE,
2671: p_split_pct cn_comm_lines_api_all.SPLIT_PCT%TYPE,
2672: p_terr_id cn_comm_lines_api_all.TERR_ID%TYPE,
2673: p_role_id cn_comm_lines_api_all.ROLE_ID%TYPE,
2674: p_transaction_amount cn_comm_lines_api_all.TRANSACTION_AMOUNT%TYPE,
2675: p_salesrep_id cn_comm_lines_api_all.SALESREP_ID%TYPE
2676: ) IS
2669: p_api_id NUMBER,
2670: p_revenue_type cn_comm_lines_api_all.REVENUE_TYPE%TYPE,
2671: p_split_pct cn_comm_lines_api_all.SPLIT_PCT%TYPE,
2672: p_terr_id cn_comm_lines_api_all.TERR_ID%TYPE,
2673: p_role_id cn_comm_lines_api_all.ROLE_ID%TYPE,
2674: p_transaction_amount cn_comm_lines_api_all.TRANSACTION_AMOUNT%TYPE,
2675: p_salesrep_id cn_comm_lines_api_all.SALESREP_ID%TYPE
2676: ) IS
2677: SELECT count(*)
2670: p_revenue_type cn_comm_lines_api_all.REVENUE_TYPE%TYPE,
2671: p_split_pct cn_comm_lines_api_all.SPLIT_PCT%TYPE,
2672: p_terr_id cn_comm_lines_api_all.TERR_ID%TYPE,
2673: p_role_id cn_comm_lines_api_all.ROLE_ID%TYPE,
2674: p_transaction_amount cn_comm_lines_api_all.TRANSACTION_AMOUNT%TYPE,
2675: p_salesrep_id cn_comm_lines_api_all.SALESREP_ID%TYPE
2676: ) IS
2677: SELECT count(*)
2678: FROM cn_comm_lines_api_all
2671: p_split_pct cn_comm_lines_api_all.SPLIT_PCT%TYPE,
2672: p_terr_id cn_comm_lines_api_all.TERR_ID%TYPE,
2673: p_role_id cn_comm_lines_api_all.ROLE_ID%TYPE,
2674: p_transaction_amount cn_comm_lines_api_all.TRANSACTION_AMOUNT%TYPE,
2675: p_salesrep_id cn_comm_lines_api_all.SALESREP_ID%TYPE
2676: ) IS
2677: SELECT count(*)
2678: FROM cn_comm_lines_api_all
2679: WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
2674: p_transaction_amount cn_comm_lines_api_all.TRANSACTION_AMOUNT%TYPE,
2675: p_salesrep_id cn_comm_lines_api_all.SALESREP_ID%TYPE
2676: ) IS
2677: SELECT count(*)
2678: FROM cn_comm_lines_api_all
2679: WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
2680: AND salesrep_id= p_salesrep_id
2681: AND transaction_amount = -1*p_transaction_amount
2682: AND NVL(role_id, -1) = p_role_id
2732: || ' count(*) over(partition by d.comm_lines_api_id) '
2733: || 'FROM jtf_tae_1001_sc_winners a, '
2734: || ' jtf_terr_rsc_all b, '
2735: || ' jtf_terr_all c, '
2736: || ' cn_comm_lines_api_all d, '
2737: || ' cn_salesreps e '
2738: || 'WHERE a.terr_rsc_id = b.terr_rsc_id '
2739: || 'AND a.terr_id = c.terr_id '
2740: || 'AND a.trans_object_id = d.comm_lines_api_id '
2742: || 'AND a.worker_id = '||p_worker_id
2743: || 'ORDER BY d.comm_lines_api_id ';
2744:
2745: /* loop through the winning resources in batches , "g_fetch_limit" records per batch, */
2746: /* and insert the records in the table cn_comm_lines_api_all */
2747: LOOP
2748: FETCH c_credited_txn_cur
2749: BULK COLLECT INTO l_rowid_tbl
2750: , l_api_id_tbl
2849: FOR i IN l_child_rowid_tbl.FIRST .. l_child_rowid_tbl.LAST LOOP
2850: --debugmsg('SCA : Now processing child transaction with id : ' || l_child_api_id_tbl(i));
2851:
2852: /* if the child has not been loaded for calculation */
2853: /* delete the child record from cn_comm_lines_api_all */
2854: IF (l_child_load_status_tbl(i) <> 'LOADED') THEN
2855: /* delete the row if it is not the same txn that we have processed */
2856: IF (
2857: (l_child_api_id_tbl(i) <> l_api_id_tbl(l_table_index))
3443: END IF;
3444:
3445: debugmsg('SCA : CN_SCATM_TAE_PUB.get_where_clause completed successfully');
3446:
3447: /* insert the selected transactions from cn_comm_lines_api_all table */
3448: /* to the interface table jtf_tae_1001_sc_dea_trans */
3449: jty_assign_bulk_pub.collect_trans_data(
3450: p_api_version_number => 1.0
3451: , p_init_msg_list => fnd_api.g_false