[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
3186: l_org_id NUMBER;
3187:
3188: CURSOR l_chk_start_date_csr IS
3189: SELECT 1
3190: FROM DUAL
3191: WHERE EXISTS(SELECT 1
3192: FROM cn_acc_period_statuses_v
3193: WHERE period_status = 'O' AND org_id = l_org_id AND p_start_date >= start_date);
3194:
3193: WHERE period_status = 'O' AND org_id = l_org_id AND p_start_date >= start_date);
3194:
3195: CURSOR l_chk_end_date_csr IS
3196: SELECT 1
3197: FROM DUAL
3198: WHERE EXISTS(SELECT 1
3199: FROM cn_acc_period_statuses_v
3200: WHERE period_status = 'O' AND org_id = l_org_id AND p_end_date <= end_date);
3201:
3200: WHERE period_status = 'O' AND org_id = l_org_id AND p_end_date <= end_date);
3201:
3202: CURSOR l_batch_name_csr IS
3203: SELECT 1
3204: FROM DUAL
3205: WHERE EXISTS(SELECT 1
3206: FROM cn_calc_submission_batches_all
3207: WHERE NAME = p_batch_name AND org_id = l_org_id);
3208:
3966:
3967: BEGIN
3968: SELECT 1
3969: INTO l_temp
3970: FROM SYS.DUAL
3971: WHERE NOT EXISTS(SELECT 1
3972: FROM cn_process_batches_all
3973: WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
3974:
4140: ELSE
4141: BEGIN
4142: SELECT 1
4143: INTO l_temp
4144: FROM SYS.DUAL
4145: WHERE NOT EXISTS(SELECT 1
4146: FROM cn_process_batches_all
4147: WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
4148:
4338: )
4339: RETURN BOOLEAN IS
4340: CURSOR l_active_plan_csr IS
4341: SELECT 1
4342: FROM DUAL
4343: WHERE EXISTS(
4344: SELECT 1
4345: FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
4346: WHERE spa.salesrep_id = p_salesrep_id