[Home] [Help]
472: api.split_pct,
473: api.split_status,
474: api.org_id
475: FROM
476: cn_comm_lines_api_all api
477: WHERE
478: api.load_status = 'UNLOADED' AND
479: api.processed_date >= TRUNC(p_start_date) AND
480: api.processed_date < (TRUNC(p_end_date) + 1) AND
816: api.split_pct,
817: api.split_status,
818: api.org_id
819: FROM
820: cn_comm_lines_api_all api
821: WHERE
822: api.load_status = 'UNLOADED' AND
823: api.processed_date >= TRUNC(p_start_date) AND
824: api.processed_date < (TRUNC(p_end_date) + 1) AND
834:
835: debugmsg('Loader : number of loaded trx = ' ||to_char(SQL%ROWCOUNT));
836:
837: IF (l_skip_credit_flag = 'Y') THEN
838: UPDATE cn_comm_lines_api_all api
839: SET load_Status = 'LOADED'
840: WHERE
841: api.load_status = 'UNLOADED' AND
842: api.processed_date >= TRUNC(p_start_date) AND
848: api.processed_date >= Trunc(batch.start_date) AND
849: api.processed_date < (Trunc(batch.end_date) + 1) AND
850: api.org_id = p_org_id;
851: ELSE
852: UPDATE cn_comm_lines_api_all api
853: SET load_Status = 'LOADED'
854: WHERE
855: api.load_status = 'UNLOADED' AND
856: api.processed_date >= TRUNC(p_start_date) AND
1013: -- Start of Comments
1014: -- API name : Load
1015: -- Type : Public.
1016: -- Pre-reqs : None.
1017: -- Usage : Used to load transactions from API table (CN_COMM_LINES_API)
1018: -- to HEADER table (CN_COMMISSION_HEADERS)
1019: -- Parameters :
1020: -- IN : p_api_version IN NUMBER Require
1021: -- : p_init_msg_list IN VARCHAR2 Optional
1030: -- : x_loading_status OUT VARCHAR2
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
1074:
1075: l_api_name CONSTANT VARCHAR2(30) := 'Load';
1076: l_api_version CONSTANT NUMBER := 1.0;
1077:
1078: TYPE l_emp_no_tbl_type IS TABLE OF cn_comm_lines_api.employee_number%TYPE;
1079: TYPE l_srp_id_tbl_type IS TABLE OF cn_comm_lines_api.salesrep_id%TYPE;
1080: TYPE l_period_id_tbl_type IS TABLE OF cn_acc_period_statuses_v.period_id%TYPE;
1081: TYPE l_start_date_tbl_type IS TABLE OF cn_acc_period_statuses_v.start_date%TYPE;
1082: TYPE l_end_date_tbl_type IS TABLE OF cn_acc_period_statuses_v.end_date%TYPE;
1075: l_api_name CONSTANT VARCHAR2(30) := 'Load';
1076: l_api_version CONSTANT NUMBER := 1.0;
1077:
1078: TYPE l_emp_no_tbl_type IS TABLE OF cn_comm_lines_api.employee_number%TYPE;
1079: TYPE l_srp_id_tbl_type IS TABLE OF cn_comm_lines_api.salesrep_id%TYPE;
1080: TYPE l_period_id_tbl_type IS TABLE OF cn_acc_period_statuses_v.period_id%TYPE;
1081: TYPE l_start_date_tbl_type IS TABLE OF cn_acc_period_statuses_v.start_date%TYPE;
1082: TYPE l_end_date_tbl_type IS TABLE OF cn_acc_period_statuses_v.end_date%TYPE;
1083: TYPE l_count_tbl_type IS TABLE OF NUMBER;
1138: acc.start_date start_date,
1139: acc.end_date end_date,
1140: count(*) trx_count
1141: FROM
1142: cn_comm_lines_api_all api,
1143: cn_acc_period_statuses_v acc
1144: WHERE
1145: api.load_status = 'UNLOADED' AND
1146: api.trx_type <> 'FORECAST' AND
1170: acc.start_date start_date,
1171: acc.end_date end_date,
1172: count(*) trx_count
1173: FROM
1174: cn_comm_lines_api_all api,
1175: cn_acc_period_statuses_v acc
1176: WHERE
1177: api.load_status = 'UNLOADED' AND
1178: api.trx_type <> 'FORECAST' AND
1726: WHEN no_valid_transactions THEN
1727: OPEN logical_batches(p_salesrep_id, p_start_date, p_end_date, p_org_id);
1728: ROLLBACK TO load_savepoint;
1729:
1730: UPDATE cn_comm_lines_api_all
1731: SET load_status = 'SALESREP ERROR'
1732: WHERE
1733: load_status = 'UNLOADED' AND
1734: trx_type <> 'FORECAST' AND