1: PACKAGE BODY RCV_ROI_PARALLEL AS
2: /* $Header: RCVPGRPB.pls 120.0.12010000.2 2010/01/25 22:57:55 vthevark ship $ */
3:
4: -- Read the profile option that enables/disables the debug log
5: g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
6: PROCEDURE spawn_process(p_num_of_groups IN NUMBER,
7: p_req_ids OUT NOCOPY RCV_ROI_PARALLEL.reqid_list) IS
8: x_progress NUMBER;
9: /*
39:
40: v_req_id NUMBER;
41:
42: BEGIN
43: IF (g_asn_debug = 'Y') THEN
44: ASN_DEBUG.PUT_LINE('START SPAWNING PROCESS');
45: END IF;
46:
47: /*
40: v_req_id NUMBER;
41:
42: BEGIN
43: IF (g_asn_debug = 'Y') THEN
44: ASN_DEBUG.PUT_LINE('START SPAWNING PROCESS');
45: END IF;
46:
47: /*
48: OPEN c_header;
55: END LOOP;
56:
57: v_cur_rec := 0;
58:
59: IF (g_asn_debug = 'Y') THEN
60: ASN_DEBUG.PUT_LINE('Before Looping');
61: END IF;
62:
63: LOOP
56:
57: v_cur_rec := 0;
58:
59: IF (g_asn_debug = 'Y') THEN
60: ASN_DEBUG.PUT_LINE('Before Looping');
61: END IF;
62:
63: LOOP
64: FETCH c_header BULK COLLECT INTO v_h_int_id LIMIT v_num_per_fetch;
62:
63: LOOP
64: FETCH c_header BULK COLLECT INTO v_h_int_id LIMIT v_num_per_fetch;
65:
66: IF (g_asn_debug = 'Y') THEN
67: ASN_DEBUG.PUT_LINE('Number of rows fetched: ' || v_h_int_id.COUNT);
68: END IF;
69:
70: IF (v_h_int_id.COUNT = 0) THEN
63: LOOP
64: FETCH c_header BULK COLLECT INTO v_h_int_id LIMIT v_num_per_fetch;
65:
66: IF (g_asn_debug = 'Y') THEN
67: ASN_DEBUG.PUT_LINE('Number of rows fetched: ' || v_h_int_id.COUNT);
68: END IF;
69:
70: IF (v_h_int_id.COUNT = 0) THEN
71: EXIT;
70: IF (v_h_int_id.COUNT = 0) THEN
71: EXIT;
72: END IF;
73:
74: IF (g_asn_debug = 'Y') THEN
75: ASN_DEBUG.PUT_LINE('Still in...');
76: END IF;
77:
78: FOR j IN 1..v_h_int_id.COUNT LOOP
71: EXIT;
72: END IF;
73:
74: IF (g_asn_debug = 'Y') THEN
75: ASN_DEBUG.PUT_LINE('Still in...');
76: END IF;
77:
78: FOR j IN 1..v_h_int_id.COUNT LOOP
79: v_id_table(v_cur_rec + j) := v_h_int_id(j);
91: FOR i IN 1..v_id_table.COUNT LOOP
92: v_gid_table(i) := v_group_id(CEIL(i/v_id_table.COUNT*p_num_of_groups));
93: END LOOP;
94:
95: IF (g_asn_debug = 'Y') THEN
96: ASN_DEBUG.PUT_LINE('Before Bulk Update of RCV_HEADERS_INTERFACE');
97: END IF;
98:
99: FORALL k IN 1..v_id_table.COUNT
92: v_gid_table(i) := v_group_id(CEIL(i/v_id_table.COUNT*p_num_of_groups));
93: END LOOP;
94:
95: IF (g_asn_debug = 'Y') THEN
96: ASN_DEBUG.PUT_LINE('Before Bulk Update of RCV_HEADERS_INTERFACE');
97: END IF;
98:
99: FORALL k IN 1..v_id_table.COUNT
100: UPDATE rcv_headers_interface
100: UPDATE rcv_headers_interface
101: SET group_id = v_gid_table(k)
102: WHERE header_interface_id = v_id_table(k);
103:
104: IF (g_asn_debug = 'Y') THEN
105: ASN_DEBUG.PUT_LINE('Before Bulk Update of RCV_TRANSACTIONS_INTERFACE');
106: END IF;
107:
108: FORALL k IN 1..v_id_table.COUNT
101: SET group_id = v_gid_table(k)
102: WHERE header_interface_id = v_id_table(k);
103:
104: IF (g_asn_debug = 'Y') THEN
105: ASN_DEBUG.PUT_LINE('Before Bulk Update of RCV_TRANSACTIONS_INTERFACE');
106: END IF;
107:
108: FORALL k IN 1..v_id_table.COUNT
109: UPDATE rcv_transactions_interface
119: WHERE NVL(asn_type,'STD') in ('ASN','ASBN','STD')
120: AND processing_status_code in ('PENDING')
121: AND nvl(validation_flag,'N') = 'Y';
122:
123: IF (g_asn_debug = 'Y') THEN
124: ASN_DEBUG.PUT_LINE('Number of groups: ' || v_group_id.COUNT);
125: END IF;
126:
127: FOR k IN 1..v_group_id.COUNT LOOP
120: AND processing_status_code in ('PENDING')
121: AND nvl(validation_flag,'N') = 'Y';
122:
123: IF (g_asn_debug = 'Y') THEN
124: ASN_DEBUG.PUT_LINE('Number of groups: ' || v_group_id.COUNT);
125: END IF;
126:
127: FOR k IN 1..v_group_id.COUNT LOOP
128: v_req_id :=
154: NULL, NULL, NULL, NULL, NULL, NULL, NULL,
155: NULL, NULL, NULL, NULL, NULL, NULL, NULL,
156: NULL, NULL, NULL, NULL, NULL, NULL, NULL);
157:
158: IF (g_asn_debug = 'Y') THEN
159: ASN_DEBUG.PUT_LINE('Spawned worker ' || k);
160: END IF;
161:
162: p_req_ids(k) := v_req_id;
155: NULL, NULL, NULL, NULL, NULL, NULL, NULL,
156: NULL, NULL, NULL, NULL, NULL, NULL, NULL);
157:
158: IF (g_asn_debug = 'Y') THEN
159: ASN_DEBUG.PUT_LINE('Spawned worker ' || k);
160: END IF;
161:
162: p_req_ids(k) := v_req_id;
163: END LOOP;