[Home] [Help]
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;
42:
43: TYPE g_salesrep_id_tbl_type IS TABLE OF cn_salesreps.salesrep_id%TYPE;
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;
42:
43: TYPE g_salesrep_id_tbl_type IS TABLE OF cn_salesreps.salesrep_id%TYPE;
44:
45: TYPE g_emp_no_tbl_type IS TABLE OF cn_salesreps.employee_number%TYPE;
43: TYPE g_salesrep_id_tbl_type IS TABLE OF cn_salesreps.salesrep_id%TYPE;
44:
45: TYPE g_emp_no_tbl_type IS TABLE OF cn_salesreps.employee_number%TYPE;
46:
47: TYPE g_role_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.role_id%TYPE;
48:
49: TYPE g_split_pctg_tbl_type IS TABLE OF jtf_terr_rsc_all.attribute1%TYPE;
50:
51: TYPE g_rev_type_tbl_type IS TABLE OF jtf_terr_rsc_all.attribute1%TYPE;
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: );
1514:
1515: debugmsg('SCA : jty_assign_bulk_pub.collect_trans_data completed successfully');
1516: debugmsg('SCA : Populating data to WINNERS table');
1517: /* this api will apply the rules to the transactions present in jtf_tae_1001_sc_dea_trans */
1518: /* and populate the winning salesreps for each transaction in the table jtf_tae_1001_sc_winners */
1519:
1520: FOR l_worker_id IN 1..l_num_workers
1521: LOOP
1522: jty_assign_bulk_pub.get_winners
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
1615: || ', '
1616: || ' b.'
1617: || l_ffname_rev_type
1618: || ' '
1619: || 'FROM jtf_tae_1001_sc_winners a, '
1620: || ' jtf_terr_rsc_all b, '
1621: || ' jtf_terr_all c, '
1622: || ' cn_salesreps d '
1623: || 'WHERE a.terr_rsc_id = b.terr_rsc_id '
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
2300: || l_ffname_rev_type
2301: || ', '
2302: || ' ''N'', '
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 '