[Home] [Help]
66: This could be improved with some kind of timeout and retry mechanism.
67:
68: Potential Issues
69: 1. Calling this program concurrently spawns many child processes ('runner')
70: where each runner will process an individual physical batch.
71: Each batch may reference more than one srp period and therefore more than
72: one period.
73: To maintain consistency between the srp_period and cn_period processing
74: statii the child process commits after setting both the srp and cn period
165: PRAGMA EXCEPTION_INIT(program_unit_missing, -6508);
166:
167: /* ----------------------------------------------------------------------------
168: | Global Cursor |
169: ----------------------------------------------------------------------------*/-- Get individual physical batch id's for the entire logical batch
170: -- no point joining to periods for the status because it may have
171: -- changed by the time we come to process the records
172: CURSOR physical_batches IS
173: SELECT a.physical_batch_id
315: , l_prog_appl_id
316: , l_conc_prog_id
317: , SYSDATE
318: , g_org_id
319: FROM SYS.DUAL
320: WHERE NOT EXISTS(
321: SELECT 1
322: FROM cn_hierarchy_nodes_all
323: WHERE dim_hierarchy_id = x_dim_hierarchy
678: , l_prog_appl_id
679: , l_conc_prog_id
680: , SYSDATE
681: , g_org_id
682: FROM DUAL
683: WHERE NOT EXISTS(
684: SELECT 1
685: FROM cn_process_batches_all
686: WHERE logical_batch_id = g_logical_batch_id
754: , l_prog_appl_id
755: , l_conc_prog_id
756: , SYSDATE
757: , g_org_id
758: FROM DUAL
759: WHERE NOT EXISTS(
760: SELECT 1
761: FROM cn_process_batches_all
762: WHERE logical_batch_id = g_logical_batch_id
824: PROCEDURE initialize_logical_batch(p_calc_sub_batch_id NUMBER) IS
825: BEGIN
826: SELECT cn_process_batches_s2.NEXTVAL
827: INTO g_logical_batch_id
828: FROM DUAL;
829:
830: UPDATE cn_calc_submission_batches_all
831: SET logical_batch_id = g_logical_batch_id
832: WHERE calc_sub_batch_id = p_calc_sub_batch_id
1727: x_physical_batch_id NUMBER;
1728: BEGIN
1729: SELECT cn_process_batches_s3.NEXTVAL
1730: INTO x_physical_batch_id
1731: FROM SYS.DUAL;
1732:
1733: RETURN x_physical_batch_id;
1734: EXCEPTION
1735: WHEN OTHERS THEN
3187: l_org_id NUMBER;
3188:
3189: CURSOR l_chk_start_date_csr IS
3190: SELECT 1
3191: FROM DUAL
3192: WHERE EXISTS(SELECT 1
3193: FROM cn_acc_period_statuses_v
3194: WHERE period_status = 'O' AND org_id = l_org_id AND p_start_date >= start_date);
3195:
3194: WHERE period_status = 'O' AND org_id = l_org_id AND p_start_date >= start_date);
3195:
3196: CURSOR l_chk_end_date_csr IS
3197: SELECT 1
3198: FROM DUAL
3199: WHERE EXISTS(SELECT 1
3200: FROM cn_acc_period_statuses_v
3201: WHERE period_status = 'O' AND org_id = l_org_id AND p_end_date <= end_date);
3202:
3201: WHERE period_status = 'O' AND org_id = l_org_id AND p_end_date <= end_date);
3202:
3203: CURSOR l_batch_name_csr IS
3204: SELECT 1
3205: FROM DUAL
3206: WHERE EXISTS(SELECT 1
3207: FROM cn_calc_submission_batches_all
3208: WHERE NAME = p_batch_name AND org_id = l_org_id);
3209:
3967:
3968: BEGIN
3969: SELECT 1
3970: INTO l_temp
3971: FROM SYS.DUAL
3972: WHERE NOT EXISTS(SELECT 1
3973: FROM cn_process_batches_all
3974: WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
3975:
4141: ELSE
4142: BEGIN
4143: SELECT 1
4144: INTO l_temp
4145: FROM SYS.DUAL
4146: WHERE NOT EXISTS(SELECT 1
4147: FROM cn_process_batches_all
4148: WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
4149:
4339: )
4340: RETURN BOOLEAN IS
4341: CURSOR l_active_plan_csr IS
4342: SELECT 1
4343: FROM DUAL
4344: WHERE EXISTS(
4345: SELECT 1
4346: FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
4347: WHERE spa.salesrep_id = p_salesrep_id