141: CURSOR l_trxs_csr IS
142: SELECT ch.commission_header_id,
143: ch.pre_processed_code,
144: ch.revenue_class_id
145: FROM cn_commission_headers_all ch
146: WHERE ch.direct_salesrep_id IN (SELECT salesrep_id
147: FROM cn_process_batches_all pb
148: WHERE pb.physical_batch_id = p_physical_batch_id)
149: AND ch.processed_date BETWEEN l_ruleset.start_date AND l_ruleset.end_date
156: CURSOR l_trx_cls_cr IS
157: SELECT ch.commission_header_id,
158: ch.pre_processed_code,
159: ch.revenue_class_id
160: FROM cn_commission_headers_all ch
161: WHERE ch.direct_salesrep_id
162: IN ( SELECT salesrep_id
163: FROM cn_process_batches_all pb
164: WHERE pb.physical_batch_id = p_physical_batch_id)
180: CURSOR l_trx_roll_cr IS
181: SELECT ch.commission_header_id,
182: ch.pre_processed_code,
183: ch.revenue_class_id
184: FROM cn_commission_headers_all ch
185: WHERE ch.direct_salesrep_id IN (SELECT salesrep_id
186: FROM cn_process_batches_all pb
187: WHERE pb.physical_batch_id = p_physical_batch_id)
188: AND ch.processed_date BETWEEN l_ruleset.start_date AND l_ruleset.end_date
207: execute immediate l_stmt using OUT l_revenue_class_id, eachtrx.commission_header_id;
208:
209: IF (l_revenue_class_id IS NOT NULL) THEN
210: -- Find one revenue class for this transaction
211: UPDATE cn_commission_headers_all
212: SET status = 'CLS',
213: revenue_class_id = l_revenue_class_id,
214: last_update_date = sysdate,
215: last_updated_by = G_LAST_UPDATED_BY,
223: p_revert_state => 'XCLS');
224: END IF;
225: ELSE
226: -- Couldn't find revenue class for this transaction
227: UPDATE cn_commission_headers_all
228: SET status = 'XCLS',
229: revenue_class_id = NULL,
230: last_update_date = sysdate,
231: last_updated_by = G_LAST_UPDATED_BY,
248: IF (l_revenue_class_id IS NOT NULL) THEN
249: IF (l_revenue_class_id <> eachtrx.revenue_class_id) THEN
250:
251: -- Find one revenue class for this transaction
252: UPDATE cn_commission_headers_all
253: SET status = 'ROLL',
254: revenue_class_id = l_revenue_class_id,
255: last_update_date = sysdate,
256: last_updated_by = G_LAST_UPDATED_BY,
268:
269: END IF;
270: ELSE
271: -- Couldn't find revenue class for this transaction
272: UPDATE cn_commission_headers_all
273: SET status = 'XCLS',
274: revenue_class_id = NULL,
275: last_update_date = sysdate,
276: last_updated_by = G_LAST_UPDATED_BY,
298:
299: IF (l_revenue_class_id IS NOT NULL) THEN
300:
301: -- Find one revenue class for this transaction
302: UPDATE cn_commission_headers_all
303: SET status = 'CLS',
304: revenue_class_id = l_revenue_class_id,
305: last_update_date = sysdate,
306: last_updated_by = G_LAST_UPDATED_BY,
308: WHERE commission_header_id = l_transaction.commission_header_id;
309:
310: ELSE
311: -- Couldn't find revenue class for this transaction
312: UPDATE cn_commission_headers_all
313: SET status = 'XCLS',
314: revenue_class_id = NULL,
315: last_update_date = sysdate,
316: last_updated_by = G_LAST_UPDATED_BY,