12: p_group_id IN NUMBER) AS
13:
14: BEGIN
15: IF (g_debug_level <= 2) THEN
16: cln_debug_pub.Add('-------- Entering procedure UPDATE_STATUS_FLAG --------',2);
17: cln_debug_pub.Add('p_msg_id : ' || p_msg_id, 2);
18: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
19: cln_debug_pub.Add('p_flag : ' || p_flag, 2);
20: cln_debug_pub.Add('p_err_msg : ' || p_err_msg, 2);
13:
14: BEGIN
15: IF (g_debug_level <= 2) THEN
16: cln_debug_pub.Add('-------- Entering procedure UPDATE_STATUS_FLAG --------',2);
17: cln_debug_pub.Add('p_msg_id : ' || p_msg_id, 2);
18: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
19: cln_debug_pub.Add('p_flag : ' || p_flag, 2);
20: cln_debug_pub.Add('p_err_msg : ' || p_err_msg, 2);
21: cln_debug_pub.Add('p_group_id : ' || p_group_id, 2);
14: BEGIN
15: IF (g_debug_level <= 2) THEN
16: cln_debug_pub.Add('-------- Entering procedure UPDATE_STATUS_FLAG --------',2);
17: cln_debug_pub.Add('p_msg_id : ' || p_msg_id, 2);
18: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
19: cln_debug_pub.Add('p_flag : ' || p_flag, 2);
20: cln_debug_pub.Add('p_err_msg : ' || p_err_msg, 2);
21: cln_debug_pub.Add('p_group_id : ' || p_group_id, 2);
22: END IF;
15: IF (g_debug_level <= 2) THEN
16: cln_debug_pub.Add('-------- Entering procedure UPDATE_STATUS_FLAG --------',2);
17: cln_debug_pub.Add('p_msg_id : ' || p_msg_id, 2);
18: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
19: cln_debug_pub.Add('p_flag : ' || p_flag, 2);
20: cln_debug_pub.Add('p_err_msg : ' || p_err_msg, 2);
21: cln_debug_pub.Add('p_group_id : ' || p_group_id, 2);
22: END IF;
23:
16: cln_debug_pub.Add('-------- Entering procedure UPDATE_STATUS_FLAG --------',2);
17: cln_debug_pub.Add('p_msg_id : ' || p_msg_id, 2);
18: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
19: cln_debug_pub.Add('p_flag : ' || p_flag, 2);
20: cln_debug_pub.Add('p_err_msg : ' || p_err_msg, 2);
21: cln_debug_pub.Add('p_group_id : ' || p_group_id, 2);
22: END IF;
23:
24: g_exception_tracking_msg := 'Updating M4R_WSM_DWIP_HDR_STAGING with the status' || p_flag;
17: cln_debug_pub.Add('p_msg_id : ' || p_msg_id, 2);
18: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
19: cln_debug_pub.Add('p_flag : ' || p_flag, 2);
20: cln_debug_pub.Add('p_err_msg : ' || p_err_msg, 2);
21: cln_debug_pub.Add('p_group_id : ' || p_group_id, 2);
22: END IF;
23:
24: g_exception_tracking_msg := 'Updating M4R_WSM_DWIP_HDR_STAGING with the status' || p_flag;
25:
28: WHERE hdr_id = p_hdr_id
29: AND msg_id = p_msg_id;
30:
31: IF (g_debug_level <= 2) THEN
32: cln_debug_pub.Add('-------- Exiting procedure UPDATE_STATUS_FLAG --------',2);
33: END IF;
34:
35: EXCEPTION
36: WHEN OTHERS THEN
39:
40: ROLLBACK;
41:
42: IF (g_debug_level <= 5) THEN
43: cln_debug_pub.Add('-------- Exception in procedure UPDATE_STATUS_FLAG --------',5);
44: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
45: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
46: END IF;
47:
40: ROLLBACK;
41:
42: IF (g_debug_level <= 5) THEN
43: cln_debug_pub.Add('-------- Exception in procedure UPDATE_STATUS_FLAG --------',5);
44: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
45: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
46: END IF;
47:
48: END UPDATE_STATUS_FLAG;
41:
42: IF (g_debug_level <= 5) THEN
43: cln_debug_pub.Add('-------- Exception in procedure UPDATE_STATUS_FLAG --------',5);
44: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
45: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
46: END IF;
47:
48: END UPDATE_STATUS_FLAG;
49:
58:
59: BEGIN
60:
61: IF (g_debug_level <= 2) THEN
62: cln_debug_pub.Add('-------- Entering procedure GET_BONUS_SCRAP_ACC_ID ------', 2);
63: cln_debug_pub.Add('p_tag_value : ' || p_tag_value, 2);
64: cln_debug_pub.Add('p_id_name : ' || p_id_name, 2);
65: END IF;
66:
59: BEGIN
60:
61: IF (g_debug_level <= 2) THEN
62: cln_debug_pub.Add('-------- Entering procedure GET_BONUS_SCRAP_ACC_ID ------', 2);
63: cln_debug_pub.Add('p_tag_value : ' || p_tag_value, 2);
64: cln_debug_pub.Add('p_id_name : ' || p_id_name, 2);
65: END IF;
66:
67: g_exception_tracking_msg := 'Calling the CLN_RN_UTILS.getTagParamValue procedure';
60:
61: IF (g_debug_level <= 2) THEN
62: cln_debug_pub.Add('-------- Entering procedure GET_BONUS_SCRAP_ACC_ID ------', 2);
63: cln_debug_pub.Add('p_tag_value : ' || p_tag_value, 2);
64: cln_debug_pub.Add('p_id_name : ' || p_id_name, 2);
65: END IF;
66:
67: g_exception_tracking_msg := 'Calling the CLN_RN_UTILS.getTagParamValue procedure';
68:
66:
67: g_exception_tracking_msg := 'Calling the CLN_RN_UTILS.getTagParamValue procedure';
68:
69: IF (g_debug_level <= 1) THEN
70: cln_debug_pub.Add(g_exception_tracking_msg,1);
71: END IF;
72:
73: IF p_id_name = 'SCRAP' THEN
74: CLN_RN_UTILS.getTagParamValue (p_tag_value,'ScrapAccountId',x_id_value);
76: CLN_RN_UTILS.getTagParamValue (p_tag_value,'BonusAccountId',x_id_value);
77: END IF;
78:
79: IF (g_debug_level <= 2) THEN
80: cln_debug_pub.Add('-------- Out of CLN_RN_UTILS.getTagParamValue procedure ------', 2);
81: cln_debug_pub.Add('x_id_value : ' || x_id_value, 2);
82: END IF;
83:
84: EXCEPTION
77: END IF;
78:
79: IF (g_debug_level <= 2) THEN
80: cln_debug_pub.Add('-------- Out of CLN_RN_UTILS.getTagParamValue procedure ------', 2);
81: cln_debug_pub.Add('x_id_value : ' || x_id_value, 2);
82: END IF;
83:
84: EXCEPTION
85: WHEN OTHERS THEN
86: g_error_code := SQLCODE;
87: g_errmsg := SQLERRM;
88:
89: IF (g_debug_level <= 5) THEN
90: cln_debug_pub.Add('-------- Exception in procedure GET_BONUS_SCRAP_ACC_ID------',5);
91: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
92: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
93: END IF;
94:
87: g_errmsg := SQLERRM;
88:
89: IF (g_debug_level <= 5) THEN
90: cln_debug_pub.Add('-------- Exception in procedure GET_BONUS_SCRAP_ACC_ID------',5);
91: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
92: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
93: END IF;
94:
95: END GET_BONUS_SCRAP_ACC_ID;
88:
89: IF (g_debug_level <= 5) THEN
90: cln_debug_pub.Add('-------- Exception in procedure GET_BONUS_SCRAP_ACC_ID------',5);
91: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
92: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
93: END IF;
94:
95: END GET_BONUS_SCRAP_ACC_ID;
96:
126:
127: BEGIN
128:
129: IF (g_debug_level <= 1) THEN
130: cln_debug_pub.Add('-------- Entering procedure GET_JOB_DETAILS ------', 2);
131: cln_debug_pub.Add('p_job_name : ' || p_job_name, 2);
132: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
133: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
134: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
127: BEGIN
128:
129: IF (g_debug_level <= 1) THEN
130: cln_debug_pub.Add('-------- Entering procedure GET_JOB_DETAILS ------', 2);
131: cln_debug_pub.Add('p_job_name : ' || p_job_name, 2);
132: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
133: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
134: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
135: END IF;
128:
129: IF (g_debug_level <= 1) THEN
130: cln_debug_pub.Add('-------- Entering procedure GET_JOB_DETAILS ------', 2);
131: cln_debug_pub.Add('p_job_name : ' || p_job_name, 2);
132: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
133: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
134: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
135: END IF;
136:
129: IF (g_debug_level <= 1) THEN
130: cln_debug_pub.Add('-------- Entering procedure GET_JOB_DETAILS ------', 2);
131: cln_debug_pub.Add('p_job_name : ' || p_job_name, 2);
132: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
133: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
134: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
135: END IF;
136:
137: g_exception_tracking_msg := 'Getting l_wip_entity_name from WIP_ENTITIES';
130: cln_debug_pub.Add('-------- Entering procedure GET_JOB_DETAILS ------', 2);
131: cln_debug_pub.Add('p_job_name : ' || p_job_name, 2);
132: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
133: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
134: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
135: END IF;
136:
137: g_exception_tracking_msg := 'Getting l_wip_entity_name from WIP_ENTITIES';
138:
142: WHERE WIP_ENTITY_NAME = p_job_name
143: AND ORGANIZATION_ID = p_org_id;
144:
145: IF (g_debug_level <= 1) THEN
146: cln_debug_pub.Add('x_wip_entity_id : '|| x_wip_entity_id,1);
147: END IF;
148:
149: g_exception_tracking_msg := 'Getting Job details from WIP_DISCRETE_JOBS';
150:
159:
160: x_return_code :='S';
161:
162: IF (g_debug_level <= 2) THEN
163: cln_debug_pub.Add('------- Exiting procedure GET_JOB_DETAILS ------ ', 2);
164: cln_debug_pub.Add('x_inventory_item_id : ' || x_inventory_item_id, 2);
165: cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
160: x_return_code :='S';
161:
162: IF (g_debug_level <= 2) THEN
163: cln_debug_pub.Add('------- Exiting procedure GET_JOB_DETAILS ------ ', 2);
164: cln_debug_pub.Add('x_inventory_item_id : ' || x_inventory_item_id, 2);
165: cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
161:
162: IF (g_debug_level <= 2) THEN
163: cln_debug_pub.Add('------- Exiting procedure GET_JOB_DETAILS ------ ', 2);
164: cln_debug_pub.Add('x_inventory_item_id : ' || x_inventory_item_id, 2);
165: cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
162: IF (g_debug_level <= 2) THEN
163: cln_debug_pub.Add('------- Exiting procedure GET_JOB_DETAILS ------ ', 2);
164: cln_debug_pub.Add('x_inventory_item_id : ' || x_inventory_item_id, 2);
165: cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
163: cln_debug_pub.Add('------- Exiting procedure GET_JOB_DETAILS ------ ', 2);
164: cln_debug_pub.Add('x_inventory_item_id : ' || x_inventory_item_id, 2);
165: cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
164: cln_debug_pub.Add('x_inventory_item_id : ' || x_inventory_item_id, 2);
165: cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
165: cln_debug_pub.Add('x_wip_entity_id : ' || x_wip_entity_id, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
166: cln_debug_pub.Add('x_wip_entity_name : ' || x_wip_entity_name, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
167: cln_debug_pub.Add('x_common_bom_seq_id : ' || x_common_bom_seq_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175: cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
168: cln_debug_pub.Add('x_common_rout_seq_id : ' || x_common_rout_seq_id, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175: cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
176: cln_debug_pub.Add('x_rout_rev_date : ' || x_rout_rev_date, 2);
169: cln_debug_pub.Add('x_bom_rev : ' || x_bom_rev, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175: cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
176: cln_debug_pub.Add('x_rout_rev_date : ' || x_rout_rev_date, 2);
177: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
170: cln_debug_pub.Add('x_rout_rev : ' || x_rout_rev, 2);
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175: cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
176: cln_debug_pub.Add('x_rout_rev_date : ' || x_rout_rev_date, 2);
177: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
178: END IF;
171: cln_debug_pub.Add('x_bom_rev_date : ' || x_bom_rev_date, 2);
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175: cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
176: cln_debug_pub.Add('x_rout_rev_date : ' || x_rout_rev_date, 2);
177: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
178: END IF;
179:
172: cln_debug_pub.Add('x_alt_bom : ' || x_alt_bom, 2);
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175: cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
176: cln_debug_pub.Add('x_rout_rev_date : ' || x_rout_rev_date, 2);
177: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
178: END IF;
179:
180: EXCEPTION
173: cln_debug_pub.Add('x_alt_rout : ' || x_alt_rout, 2);
174: cln_debug_pub.Add('x_comp_sub_inventory : ' || x_comp_sub_inventory, 2);
175: cln_debug_pub.Add('x_comp_locator_id : ' || x_comp_locator_id, 2);
176: cln_debug_pub.Add('x_rout_rev_date : ' || x_rout_rev_date, 2);
177: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
178: END IF;
179:
180: EXCEPTION
181: WHEN NO_DATA_FOUND THEN
183: g_errmsg := SQLERRM;
184: x_return_code :='F';
185:
186: IF (g_debug_level <= 5) THEN
187: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
188: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
189: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
190: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
191: END IF;
184: x_return_code :='F';
185:
186: IF (g_debug_level <= 5) THEN
187: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
188: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
189: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
190: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
191: END IF;
192:
185:
186: IF (g_debug_level <= 5) THEN
187: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
188: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
189: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
190: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
191: END IF;
192:
193: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
186: IF (g_debug_level <= 5) THEN
187: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
188: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
189: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
190: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
191: END IF;
192:
193: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
194: FND_MESSAGE.SET_TOKEN('JOB_NAME',p_job_name);
202: g_errmsg := SQLERRM;
203: x_return_code :='F';
204:
205: IF (g_debug_level <= 5) THEN
206: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
207: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
208: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
209: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
210: END IF;
203: x_return_code :='F';
204:
205: IF (g_debug_level <= 5) THEN
206: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
207: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
208: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
209: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
210: END IF;
211:
204:
205: IF (g_debug_level <= 5) THEN
206: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
207: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
208: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
209: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
210: END IF;
211:
212: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
205: IF (g_debug_level <= 5) THEN
206: cln_debug_pub.Add('-------- Exception in procedure GET_JOB_DETAILS -----',5);
207: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
208: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
209: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
210: END IF;
211:
212: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
213:
228:
229: BEGIN
230:
231: IF (g_debug_level <= 2) THEN
232: cln_debug_pub.Add('-------- Entering procedure GET_TP_DETAILS ------', 2);
233: cln_debug_pub.Add('p_tp_hdr_id : ' || p_tp_hdr_id, 2);
234: END IF;
235:
236: g_exception_tracking_msg := 'Querying ecx_tp_headers for Trading Partner Details';
229: BEGIN
230:
231: IF (g_debug_level <= 2) THEN
232: cln_debug_pub.Add('-------- Entering procedure GET_TP_DETAILS ------', 2);
233: cln_debug_pub.Add('p_tp_hdr_id : ' || p_tp_hdr_id, 2);
234: END IF;
235:
236: g_exception_tracking_msg := 'Querying ecx_tp_headers for Trading Partner Details';
237:
240: FROM ecx_tp_headers
241: WHERE tp_header_id = p_tp_hdr_id;
242:
243: IF (g_debug_level <= 2) THEN
244: cln_debug_pub.Add('------- Exiting procedure GET_TP_DETAILS ------ ', 2);
245: cln_debug_pub.Add('x_party_id : ' || x_party_id, 2);
246: cln_debug_pub.Add('x_party_site_id : ' || x_party_site_id, 2);
247: END IF;
248:
241: WHERE tp_header_id = p_tp_hdr_id;
242:
243: IF (g_debug_level <= 2) THEN
244: cln_debug_pub.Add('------- Exiting procedure GET_TP_DETAILS ------ ', 2);
245: cln_debug_pub.Add('x_party_id : ' || x_party_id, 2);
246: cln_debug_pub.Add('x_party_site_id : ' || x_party_site_id, 2);
247: END IF;
248:
249: EXCEPTION
242:
243: IF (g_debug_level <= 2) THEN
244: cln_debug_pub.Add('------- Exiting procedure GET_TP_DETAILS ------ ', 2);
245: cln_debug_pub.Add('x_party_id : ' || x_party_id, 2);
246: cln_debug_pub.Add('x_party_site_id : ' || x_party_site_id, 2);
247: END IF;
248:
249: EXCEPTION
250: WHEN OTHERS THEN
251: g_error_code := SQLCODE;
252: g_errmsg := SQLERRM;
253:
254: IF (g_debug_level <= 5) THEN
255: cln_debug_pub.Add('-------- Exception in procedure GET_TP_DETAILS------',5);
256: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
257: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
258: END IF;
259:
252: g_errmsg := SQLERRM;
253:
254: IF (g_debug_level <= 5) THEN
255: cln_debug_pub.Add('-------- Exception in procedure GET_TP_DETAILS------',5);
256: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
257: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
258: END IF;
259:
260: END GET_TP_DETAILS;
253:
254: IF (g_debug_level <= 5) THEN
255: cln_debug_pub.Add('-------- Exception in procedure GET_TP_DETAILS------',5);
256: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
257: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
258: END IF;
259:
260: END GET_TP_DETAILS;
261:
280:
281: BEGIN
282:
283: IF (g_debug_level <= 1) THEN
284: cln_debug_pub.Add('------- Entering procedure GET_DEPT_AND_OP_ID ------ ', 2);
285: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
286: cln_debug_pub.Add('p_prev_op_seq_num : ' || p_prev_op_seq_num, 2);
287: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
288: cln_debug_pub.Add('p_rout_seq_id : ' || p_rout_seq_id, 2);
281: BEGIN
282:
283: IF (g_debug_level <= 1) THEN
284: cln_debug_pub.Add('------- Entering procedure GET_DEPT_AND_OP_ID ------ ', 2);
285: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
286: cln_debug_pub.Add('p_prev_op_seq_num : ' || p_prev_op_seq_num, 2);
287: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
288: cln_debug_pub.Add('p_rout_seq_id : ' || p_rout_seq_id, 2);
289: END IF;
282:
283: IF (g_debug_level <= 1) THEN
284: cln_debug_pub.Add('------- Entering procedure GET_DEPT_AND_OP_ID ------ ', 2);
285: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
286: cln_debug_pub.Add('p_prev_op_seq_num : ' || p_prev_op_seq_num, 2);
287: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
288: cln_debug_pub.Add('p_rout_seq_id : ' || p_rout_seq_id, 2);
289: END IF;
290:
283: IF (g_debug_level <= 1) THEN
284: cln_debug_pub.Add('------- Entering procedure GET_DEPT_AND_OP_ID ------ ', 2);
285: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
286: cln_debug_pub.Add('p_prev_op_seq_num : ' || p_prev_op_seq_num, 2);
287: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
288: cln_debug_pub.Add('p_rout_seq_id : ' || p_rout_seq_id, 2);
289: END IF;
290:
291: g_exception_tracking_msg := 'Querying BOM_OPERATION_SEQUENCES for fm_values';
284: cln_debug_pub.Add('------- Entering procedure GET_DEPT_AND_OP_ID ------ ', 2);
285: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
286: cln_debug_pub.Add('p_prev_op_seq_num : ' || p_prev_op_seq_num, 2);
287: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
288: cln_debug_pub.Add('p_rout_seq_id : ' || p_rout_seq_id, 2);
289: END IF;
290:
291: g_exception_tracking_msg := 'Querying BOM_OPERATION_SEQUENCES for fm_values';
292:
314:
315: x_return_code :='S';
316:
317: IF (g_debug_level <= 2) THEN
318: cln_debug_pub.Add('------- Exiting procedure GET_DEPT_AND_OP_ID ------ ', 2);
319: cln_debug_pub.Add('x_fm_dept_id : ' || x_fm_dept_id, 2);
320: cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
315: x_return_code :='S';
316:
317: IF (g_debug_level <= 2) THEN
318: cln_debug_pub.Add('------- Exiting procedure GET_DEPT_AND_OP_ID ------ ', 2);
319: cln_debug_pub.Add('x_fm_dept_id : ' || x_fm_dept_id, 2);
320: cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323: cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
316:
317: IF (g_debug_level <= 2) THEN
318: cln_debug_pub.Add('------- Exiting procedure GET_DEPT_AND_OP_ID ------ ', 2);
319: cln_debug_pub.Add('x_fm_dept_id : ' || x_fm_dept_id, 2);
320: cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323: cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
324: cln_debug_pub.Add('x_to_op_seq_id : ' || x_to_op_seq_id, 2);
317: IF (g_debug_level <= 2) THEN
318: cln_debug_pub.Add('------- Exiting procedure GET_DEPT_AND_OP_ID ------ ', 2);
319: cln_debug_pub.Add('x_fm_dept_id : ' || x_fm_dept_id, 2);
320: cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323: cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
324: cln_debug_pub.Add('x_to_op_seq_id : ' || x_to_op_seq_id, 2);
325: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
318: cln_debug_pub.Add('------- Exiting procedure GET_DEPT_AND_OP_ID ------ ', 2);
319: cln_debug_pub.Add('x_fm_dept_id : ' || x_fm_dept_id, 2);
320: cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323: cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
324: cln_debug_pub.Add('x_to_op_seq_id : ' || x_to_op_seq_id, 2);
325: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
326: END IF;
319: cln_debug_pub.Add('x_fm_dept_id : ' || x_fm_dept_id, 2);
320: cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323: cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
324: cln_debug_pub.Add('x_to_op_seq_id : ' || x_to_op_seq_id, 2);
325: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
326: END IF;
327:
320: cln_debug_pub.Add('x_fm_std_op_id : ' || x_fm_std_op_id, 2);
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323: cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
324: cln_debug_pub.Add('x_to_op_seq_id : ' || x_to_op_seq_id, 2);
325: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
326: END IF;
327:
328:
321: cln_debug_pub.Add('x_fm_op_seq_id : ' || x_fm_op_seq_id, 2);
322: cln_debug_pub.Add('x_to_dept_id : ' || x_to_dept_id, 2);
323: cln_debug_pub.Add('x_to_std_op_id : ' || x_to_std_op_id, 2);
324: cln_debug_pub.Add('x_to_op_seq_id : ' || x_to_op_seq_id, 2);
325: cln_debug_pub.Add('x_return_code : ' || x_return_code, 2);
326: END IF;
327:
328:
329: EXCEPTION
332: g_errmsg := SQLERRM;
333: x_return_code :='F';
334:
335: IF (g_debug_level <= 5) THEN
336: cln_debug_pub.Add('-------- Exception in procedure GET_DEPT_AND_OP_ID -----',5);
337: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
338: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
339: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
340: END IF;
333: x_return_code :='F';
334:
335: IF (g_debug_level <= 5) THEN
336: cln_debug_pub.Add('-------- Exception in procedure GET_DEPT_AND_OP_ID -----',5);
337: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
338: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
339: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
340: END IF;
341:
334:
335: IF (g_debug_level <= 5) THEN
336: cln_debug_pub.Add('-------- Exception in procedure GET_DEPT_AND_OP_ID -----',5);
337: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
338: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
339: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
340: END IF;
341:
342: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
335: IF (g_debug_level <= 5) THEN
336: cln_debug_pub.Add('-------- Exception in procedure GET_DEPT_AND_OP_ID -----',5);
337: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
338: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
339: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
340: END IF;
341:
342: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
343:
349: g_errmsg := SQLERRM;
350: x_return_code :='F';
351:
352: IF (g_debug_level <= 5) THEN
353: cln_debug_pub.Add('------- Exception in procedure GET_DEPT_AND_OP_ID ------',5);
354: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
355: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
356: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
357: END IF;
350: x_return_code :='F';
351:
352: IF (g_debug_level <= 5) THEN
353: cln_debug_pub.Add('------- Exception in procedure GET_DEPT_AND_OP_ID ------',5);
354: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
355: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
356: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
357: END IF;
358:
351:
352: IF (g_debug_level <= 5) THEN
353: cln_debug_pub.Add('------- Exception in procedure GET_DEPT_AND_OP_ID ------',5);
354: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
355: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
356: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
357: END IF;
358:
359: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
352: IF (g_debug_level <= 5) THEN
353: cln_debug_pub.Add('------- Exception in procedure GET_DEPT_AND_OP_ID ------',5);
354: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
355: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
356: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
357: END IF;
358:
359: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
360:
380: x_int_err OUT NOCOPY VARCHAR2) AS
381:
382: BEGIN
383: IF (g_debug_level <= 2) THEN
384: cln_debug_pub.Add('------ Entering procedure GET_INV_ITEM_DETAILS ------', 2);
385: cln_debug_pub.Add('p_start_lot_item : ' || p_start_lot_item, 2);
386: cln_debug_pub.Add('p_prim_lot_item : ' || p_prim_lot_item, 2);
387: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
388: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
381:
382: BEGIN
383: IF (g_debug_level <= 2) THEN
384: cln_debug_pub.Add('------ Entering procedure GET_INV_ITEM_DETAILS ------', 2);
385: cln_debug_pub.Add('p_start_lot_item : ' || p_start_lot_item, 2);
386: cln_debug_pub.Add('p_prim_lot_item : ' || p_prim_lot_item, 2);
387: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
388: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
389: END IF;
382: BEGIN
383: IF (g_debug_level <= 2) THEN
384: cln_debug_pub.Add('------ Entering procedure GET_INV_ITEM_DETAILS ------', 2);
385: cln_debug_pub.Add('p_start_lot_item : ' || p_start_lot_item, 2);
386: cln_debug_pub.Add('p_prim_lot_item : ' || p_prim_lot_item, 2);
387: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
388: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
389: END IF;
390:
383: IF (g_debug_level <= 2) THEN
384: cln_debug_pub.Add('------ Entering procedure GET_INV_ITEM_DETAILS ------', 2);
385: cln_debug_pub.Add('p_start_lot_item : ' || p_start_lot_item, 2);
386: cln_debug_pub.Add('p_prim_lot_item : ' || p_prim_lot_item, 2);
387: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
388: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
389: END IF;
390:
391: IF p_start_lot_item IS NOT NULL THEN
384: cln_debug_pub.Add('------ Entering procedure GET_INV_ITEM_DETAILS ------', 2);
385: cln_debug_pub.Add('p_start_lot_item : ' || p_start_lot_item, 2);
386: cln_debug_pub.Add('p_prim_lot_item : ' || p_prim_lot_item, 2);
387: cln_debug_pub.Add('p_hdr_id : ' || p_hdr_id, 2);
388: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
389: END IF;
390:
391: IF p_start_lot_item IS NOT NULL THEN
392:
399: AND organization_id = p_org_id
400: AND inventory_item_status_code = 'Active';
401:
402: IF (g_debug_level <= 1) THEN
403: cln_debug_pub.Add('x_prev_inventory_item_id: ' || x_prev_inventory_item_id, 1);
404: END IF;
405: END IF;
406:
407: g_exception_tracking_msg := 'Querying mtl_system_items_kfv table for PRIMARY_ITEM_CODE';
413: AND organization_id = p_org_id
414: AND inventory_item_status_code = 'Active';
415:
416: IF (g_debug_level <= 1) THEN
417: cln_debug_pub.Add('x_inventory_item_id: ' || x_inventory_item_id, 1);
418: END IF;
419:
420: IF (g_debug_level <= 2) THEN
421: cln_debug_pub.Add('------- Exiting procedure GET_INV_ITEM_DETAILS ------ ', 2);
417: cln_debug_pub.Add('x_inventory_item_id: ' || x_inventory_item_id, 1);
418: END IF;
419:
420: IF (g_debug_level <= 2) THEN
421: cln_debug_pub.Add('------- Exiting procedure GET_INV_ITEM_DETAILS ------ ', 2);
422: END IF;
423:
424: EXCEPTION
425: WHEN NO_DATA_FOUND THEN
427: g_errmsg := SQLERRM;
428: x_return_code :='F';
429:
430: IF (g_debug_level <= 5) THEN
431: cln_debug_pub.Add('-------- Exception in procedure GET_INV_ITEM_DETAILS -----',5);
432: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
433: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
434: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
435: END IF;
428: x_return_code :='F';
429:
430: IF (g_debug_level <= 5) THEN
431: cln_debug_pub.Add('-------- Exception in procedure GET_INV_ITEM_DETAILS -----',5);
432: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
433: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
434: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
435: END IF;
436:
429:
430: IF (g_debug_level <= 5) THEN
431: cln_debug_pub.Add('-------- Exception in procedure GET_INV_ITEM_DETAILS -----',5);
432: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
433: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
434: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
435: END IF;
436:
437: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
430: IF (g_debug_level <= 5) THEN
431: cln_debug_pub.Add('-------- Exception in procedure GET_INV_ITEM_DETAILS -----',5);
432: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
433: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
434: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
435: END IF;
436:
437: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
438:
445: g_errmsg := SQLERRM;
446: x_return_code :='F';
447:
448: IF (g_debug_level <= 5) THEN
449: cln_debug_pub.Add('---- Exception in procedure GET_INV_ITEM_DETAILS ------',5);
450: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
451: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
452: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
453: END IF;
446: x_return_code :='F';
447:
448: IF (g_debug_level <= 5) THEN
449: cln_debug_pub.Add('---- Exception in procedure GET_INV_ITEM_DETAILS ------',5);
450: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
451: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
452: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
453: END IF;
454:
447:
448: IF (g_debug_level <= 5) THEN
449: cln_debug_pub.Add('---- Exception in procedure GET_INV_ITEM_DETAILS ------',5);
450: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
451: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
452: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
453: END IF;
454:
455: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
448: IF (g_debug_level <= 5) THEN
449: cln_debug_pub.Add('---- Exception in procedure GET_INV_ITEM_DETAILS ------',5);
450: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
451: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
452: cln_debug_pub.Add('x_return_code : ' || x_return_code, 5);
453: END IF;
454:
455: x_err_msg := g_exception_tracking_msg || ':' ||g_error_code || ' : ' || g_errmsg;
456:
474:
475: BEGIN
476:
477: IF (g_debug_level <= 1) THEN
478: cln_debug_pub.Add('------Entering procedure determine_process------', 2);
479: cln_debug_pub.Add('p_txn_type : ' || p_txn_type, 2);
480: cln_debug_pub.Add('p_lot_class_code : ' || p_lot_class_code, 2);
481: cln_debug_pub.Add('p_status_change_code : ' || p_status_change_code, 2);
482: cln_debug_pub.Add('p_prev_opn_seq_num : ' || p_prev_opn_seq_num, 2);
475: BEGIN
476:
477: IF (g_debug_level <= 1) THEN
478: cln_debug_pub.Add('------Entering procedure determine_process------', 2);
479: cln_debug_pub.Add('p_txn_type : ' || p_txn_type, 2);
480: cln_debug_pub.Add('p_lot_class_code : ' || p_lot_class_code, 2);
481: cln_debug_pub.Add('p_status_change_code : ' || p_status_change_code, 2);
482: cln_debug_pub.Add('p_prev_opn_seq_num : ' || p_prev_opn_seq_num, 2);
483: cln_debug_pub.Add('p_opn_seq_num : ' || p_opn_seq_num, 2);
476:
477: IF (g_debug_level <= 1) THEN
478: cln_debug_pub.Add('------Entering procedure determine_process------', 2);
479: cln_debug_pub.Add('p_txn_type : ' || p_txn_type, 2);
480: cln_debug_pub.Add('p_lot_class_code : ' || p_lot_class_code, 2);
481: cln_debug_pub.Add('p_status_change_code : ' || p_status_change_code, 2);
482: cln_debug_pub.Add('p_prev_opn_seq_num : ' || p_prev_opn_seq_num, 2);
483: cln_debug_pub.Add('p_opn_seq_num : ' || p_opn_seq_num, 2);
484: END IF;
477: IF (g_debug_level <= 1) THEN
478: cln_debug_pub.Add('------Entering procedure determine_process------', 2);
479: cln_debug_pub.Add('p_txn_type : ' || p_txn_type, 2);
480: cln_debug_pub.Add('p_lot_class_code : ' || p_lot_class_code, 2);
481: cln_debug_pub.Add('p_status_change_code : ' || p_status_change_code, 2);
482: cln_debug_pub.Add('p_prev_opn_seq_num : ' || p_prev_opn_seq_num, 2);
483: cln_debug_pub.Add('p_opn_seq_num : ' || p_opn_seq_num, 2);
484: END IF;
485:
478: cln_debug_pub.Add('------Entering procedure determine_process------', 2);
479: cln_debug_pub.Add('p_txn_type : ' || p_txn_type, 2);
480: cln_debug_pub.Add('p_lot_class_code : ' || p_lot_class_code, 2);
481: cln_debug_pub.Add('p_status_change_code : ' || p_status_change_code, 2);
482: cln_debug_pub.Add('p_prev_opn_seq_num : ' || p_prev_opn_seq_num, 2);
483: cln_debug_pub.Add('p_opn_seq_num : ' || p_opn_seq_num, 2);
484: END IF;
485:
486: IF p_txn_type= 'START' THEN
479: cln_debug_pub.Add('p_txn_type : ' || p_txn_type, 2);
480: cln_debug_pub.Add('p_lot_class_code : ' || p_lot_class_code, 2);
481: cln_debug_pub.Add('p_status_change_code : ' || p_status_change_code, 2);
482: cln_debug_pub.Add('p_prev_opn_seq_num : ' || p_prev_opn_seq_num, 2);
483: cln_debug_pub.Add('p_opn_seq_num : ' || p_opn_seq_num, 2);
484: END IF;
485:
486: IF p_txn_type= 'START' THEN
487: IF p_lot_class_code='START' OR p_lot_class_code='UNRELEASED' THEN
584: ELSE x_process_type := 'ERROR';
585: END IF;
586:
587: IF (g_debug_level <= 1) THEN
588: cln_debug_pub.Add('-------- Exiting procedure determine_process --------',2);
589: cln_debug_pub.Add('x_process_type : ' || x_process_type, 2);
590: END IF;
591:
592: EXCEPTION
585: END IF;
586:
587: IF (g_debug_level <= 1) THEN
588: cln_debug_pub.Add('-------- Exiting procedure determine_process --------',2);
589: cln_debug_pub.Add('x_process_type : ' || x_process_type, 2);
590: END IF;
591:
592: EXCEPTION
593: WHEN OTHERS THEN
595: g_errmsg := SQLERRM;
596: x_process_type := 'ERROR';
597:
598: IF (g_debug_level <= 5) THEN
599: cln_debug_pub.Add('----Exception in procedure determine_process------',5);
600: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
601: cln_debug_pub.Add('x_process_type: ' || x_process_type, 5);
602: END IF;
603:
596: x_process_type := 'ERROR';
597:
598: IF (g_debug_level <= 5) THEN
599: cln_debug_pub.Add('----Exception in procedure determine_process------',5);
600: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
601: cln_debug_pub.Add('x_process_type: ' || x_process_type, 5);
602: END IF;
603:
604: END DETERMINE_PROCESS_TYPE;
597:
598: IF (g_debug_level <= 5) THEN
599: cln_debug_pub.Add('----Exception in procedure determine_process------',5);
600: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
601: cln_debug_pub.Add('x_process_type: ' || x_process_type, 5);
602: END IF;
603:
604: END DETERMINE_PROCESS_TYPE;
605:
616:
617: BEGIN
618:
619: IF (g_debug_level <= 2) THEN
620: cln_debug_pub.Add('------- Entering procedure RAISE_CUSTOM_VALID_EVENT --------',2);
621: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
622: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
623: cln_debug_pub.Add('p_trx_if_id : '|| p_trx_if_id ,2);
624: cln_debug_pub.Add('p_process_type : '|| p_process_type ,2);
617: BEGIN
618:
619: IF (g_debug_level <= 2) THEN
620: cln_debug_pub.Add('------- Entering procedure RAISE_CUSTOM_VALID_EVENT --------',2);
621: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
622: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
623: cln_debug_pub.Add('p_trx_if_id : '|| p_trx_if_id ,2);
624: cln_debug_pub.Add('p_process_type : '|| p_process_type ,2);
625: END IF;
618:
619: IF (g_debug_level <= 2) THEN
620: cln_debug_pub.Add('------- Entering procedure RAISE_CUSTOM_VALID_EVENT --------',2);
621: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
622: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
623: cln_debug_pub.Add('p_trx_if_id : '|| p_trx_if_id ,2);
624: cln_debug_pub.Add('p_process_type : '|| p_process_type ,2);
625: END IF;
626:
619: IF (g_debug_level <= 2) THEN
620: cln_debug_pub.Add('------- Entering procedure RAISE_CUSTOM_VALID_EVENT --------',2);
621: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
622: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
623: cln_debug_pub.Add('p_trx_if_id : '|| p_trx_if_id ,2);
624: cln_debug_pub.Add('p_process_type : '|| p_process_type ,2);
625: END IF;
626:
627: WF_EVENT.AddParameterToList('INTERNAL_CONTROL_NUMBER', p_msg_id, l_parameters);
620: cln_debug_pub.Add('------- Entering procedure RAISE_CUSTOM_VALID_EVENT --------',2);
621: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
622: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
623: cln_debug_pub.Add('p_trx_if_id : '|| p_trx_if_id ,2);
624: cln_debug_pub.Add('p_process_type : '|| p_process_type ,2);
625: END IF;
626:
627: WF_EVENT.AddParameterToList('INTERNAL_CONTROL_NUMBER', p_msg_id, l_parameters);
628: WF_EVENT.AddParameterToList('PROCESS_TYPE', p_process_type, l_parameters);
632:
633: g_exception_tracking_msg := '------- Raising the event --------';
634:
635: IF (g_debug_level <= 2) THEN
636: cln_debug_pub.Add(g_exception_tracking_msg,2);
637: END IF;
638:
639: WF_EVENT.Raise('oracle.apps.m4r.wsm.distributewip.in.validate2','7B1 : ' ||p_hdr_id, NULL, l_parameters, NULL);
640:
638:
639: WF_EVENT.Raise('oracle.apps.m4r.wsm.distributewip.in.validate2','7B1 : ' ||p_hdr_id, NULL, l_parameters, NULL);
640:
641: IF (g_debug_level <= 2) THEN
642: cln_debug_pub.Add('------- Exiting procedure RAISE_CUSTOM_VALID_EVENT --------',2);
643: END IF;
644:
645: EXCEPTION
646: WHEN OTHERS THEN
647: g_error_code := SQLCODE;
648: g_errmsg := SQLERRM;
649:
650: IF (g_debug_level <= 5) THEN
651: cln_debug_pub.Add('------- Exception in procedure RAISE_CUSTOM_VALID_EVENT --------',5);
652: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
653: cln_debug_pub.Add('Error is - ' || g_error_code || ': ' || g_errmsg, 5);
654: END IF;
655:
648: g_errmsg := SQLERRM;
649:
650: IF (g_debug_level <= 5) THEN
651: cln_debug_pub.Add('------- Exception in procedure RAISE_CUSTOM_VALID_EVENT --------',5);
652: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
653: cln_debug_pub.Add('Error is - ' || g_error_code || ': ' || g_errmsg, 5);
654: END IF;
655:
656: END RAISE_CUSTOM_VALID_EVENT;
649:
650: IF (g_debug_level <= 5) THEN
651: cln_debug_pub.Add('------- Exception in procedure RAISE_CUSTOM_VALID_EVENT --------',5);
652: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
653: cln_debug_pub.Add('Error is - ' || g_error_code || ': ' || g_errmsg, 5);
654: END IF;
655:
656: END RAISE_CUSTOM_VALID_EVENT;
657:
671:
672: BEGIN
673:
674: IF (g_debug_level <= 2) THEN
675: cln_debug_pub.Add('-------- Entering procedure GET_INTRAOPERATION_STEP --------',2);
676: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
677: END IF;
678:
679: g_exception_tracking_msg := 'Querying wip_operations for l_op_seq_num';
672: BEGIN
673:
674: IF (g_debug_level <= 2) THEN
675: cln_debug_pub.Add('-------- Entering procedure GET_INTRAOPERATION_STEP --------',2);
676: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
677: END IF;
678:
679: g_exception_tracking_msg := 'Querying wip_operations for l_op_seq_num';
680:
706: x_qty := l_qty_RUN;
707: END IF ;
708:
709: IF (g_debug_level <= 1) THEN
710: cln_debug_pub.Add('-------- Exiting procedure GET_INTRAOPERATION_STEP --------',2);
711: cln_debug_pub.Add('x_intra_step : ' || x_intra_step, 2);
712: cln_debug_pub.Add('x_qty : ' || x_qty, 2);
713: cln_debug_pub.Add('x_op_seq_num : ' || x_op_seq_num, 2);
714: END IF;
707: END IF ;
708:
709: IF (g_debug_level <= 1) THEN
710: cln_debug_pub.Add('-------- Exiting procedure GET_INTRAOPERATION_STEP --------',2);
711: cln_debug_pub.Add('x_intra_step : ' || x_intra_step, 2);
712: cln_debug_pub.Add('x_qty : ' || x_qty, 2);
713: cln_debug_pub.Add('x_op_seq_num : ' || x_op_seq_num, 2);
714: END IF;
715:
708:
709: IF (g_debug_level <= 1) THEN
710: cln_debug_pub.Add('-------- Exiting procedure GET_INTRAOPERATION_STEP --------',2);
711: cln_debug_pub.Add('x_intra_step : ' || x_intra_step, 2);
712: cln_debug_pub.Add('x_qty : ' || x_qty, 2);
713: cln_debug_pub.Add('x_op_seq_num : ' || x_op_seq_num, 2);
714: END IF;
715:
716: EXCEPTION
709: IF (g_debug_level <= 1) THEN
710: cln_debug_pub.Add('-------- Exiting procedure GET_INTRAOPERATION_STEP --------',2);
711: cln_debug_pub.Add('x_intra_step : ' || x_intra_step, 2);
712: cln_debug_pub.Add('x_qty : ' || x_qty, 2);
713: cln_debug_pub.Add('x_op_seq_num : ' || x_op_seq_num, 2);
714: END IF;
715:
716: EXCEPTION
717: WHEN OTHERS THEN
718: g_error_code := SQLCODE;
719: g_errmsg := SQLERRM;
720:
721: IF (g_debug_level <= 5) THEN
722: cln_debug_pub.Add('------- Exception in procedure GET_INTRAOPERATION_STEP --------',5);
723: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
724: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
725: END IF;
726:
719: g_errmsg := SQLERRM;
720:
721: IF (g_debug_level <= 5) THEN
722: cln_debug_pub.Add('------- Exception in procedure GET_INTRAOPERATION_STEP --------',5);
723: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
724: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
725: END IF;
726:
727: END GET_INTRAOPERATION_STEP;
720:
721: IF (g_debug_level <= 5) THEN
722: cln_debug_pub.Add('------- Exception in procedure GET_INTRAOPERATION_STEP --------',5);
723: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
724: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
725: END IF;
726:
727: END GET_INTRAOPERATION_STEP;
728:
740:
741: BEGIN
742:
743: IF (g_debug_level <= 1) THEN
744: cln_debug_pub.Add('-------- Entering procedure GET_REPRESENTATIVE_FLAG --------',2);
745: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
746: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
747: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
748: cln_debug_pub.Add('p_lot_code : ' || p_lot_code, 2);
741: BEGIN
742:
743: IF (g_debug_level <= 1) THEN
744: cln_debug_pub.Add('-------- Entering procedure GET_REPRESENTATIVE_FLAG --------',2);
745: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
746: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
747: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
748: cln_debug_pub.Add('p_lot_code : ' || p_lot_code, 2);
749: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
742:
743: IF (g_debug_level <= 1) THEN
744: cln_debug_pub.Add('-------- Entering procedure GET_REPRESENTATIVE_FLAG --------',2);
745: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
746: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
747: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
748: cln_debug_pub.Add('p_lot_code : ' || p_lot_code, 2);
749: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
750: END IF;
743: IF (g_debug_level <= 1) THEN
744: cln_debug_pub.Add('-------- Entering procedure GET_REPRESENTATIVE_FLAG --------',2);
745: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
746: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
747: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
748: cln_debug_pub.Add('p_lot_code : ' || p_lot_code, 2);
749: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
750: END IF;
751:
744: cln_debug_pub.Add('-------- Entering procedure GET_REPRESENTATIVE_FLAG --------',2);
745: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
746: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
747: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
748: cln_debug_pub.Add('p_lot_code : ' || p_lot_code, 2);
749: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
750: END IF;
751:
752: g_exception_tracking_msg := 'Querying WIP_ENTITIES for x_prev_wip_entity_id';
745: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
746: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
747: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
748: cln_debug_pub.Add('p_lot_code : ' || p_lot_code, 2);
749: cln_debug_pub.Add('p_alt_rout : ' || p_alt_rout, 2);
750: END IF;
751:
752: g_exception_tracking_msg := 'Querying WIP_ENTITIES for x_prev_wip_entity_id';
753:
757: WHERE wip_entity_name = p_lot_number
758: AND ORGANIZATION_ID = p_org_id;
759:
760: IF (g_debug_level <= 1) THEN
761: cln_debug_pub.Add('x_prev_wip_entity_id : ' || x_prev_wip_entity_id, 1);
762: END IF;
763:
764: IF x_prev_wip_entity_id = p_wip_entity_id THEN -- if the prev lot already exists, then it
765: --implies tht all the lots r merged to this lot
773: x_rep_flag := NULL;
774: END IF;
775:
776: IF (g_debug_level <= 1) THEN
777: cln_debug_pub.Add('-------- Exiting procedure GET_REPRESENTATIVE_FLAG --------',2);
778: cln_debug_pub.Add('x_rep_flag : ' || x_rep_flag, 2);
779: cln_debug_pub.Add('x_prev_wip_entity_id : ' || x_prev_wip_entity_id, 2);
780: END IF;
781:
774: END IF;
775:
776: IF (g_debug_level <= 1) THEN
777: cln_debug_pub.Add('-------- Exiting procedure GET_REPRESENTATIVE_FLAG --------',2);
778: cln_debug_pub.Add('x_rep_flag : ' || x_rep_flag, 2);
779: cln_debug_pub.Add('x_prev_wip_entity_id : ' || x_prev_wip_entity_id, 2);
780: END IF;
781:
782: EXCEPTION
775:
776: IF (g_debug_level <= 1) THEN
777: cln_debug_pub.Add('-------- Exiting procedure GET_REPRESENTATIVE_FLAG --------',2);
778: cln_debug_pub.Add('x_rep_flag : ' || x_rep_flag, 2);
779: cln_debug_pub.Add('x_prev_wip_entity_id : ' || x_prev_wip_entity_id, 2);
780: END IF;
781:
782: EXCEPTION
783: WHEN OTHERS THEN
784: g_error_code := SQLCODE;
785: g_errmsg := SQLERRM;
786:
787: IF (g_debug_level <= 5) THEN
788: cln_debug_pub.Add('------- Exception in procedure GET_REPRESENTATIVE_FLAG --------',5);
789: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
790: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
791: END IF;
792:
785: g_errmsg := SQLERRM;
786:
787: IF (g_debug_level <= 5) THEN
788: cln_debug_pub.Add('------- Exception in procedure GET_REPRESENTATIVE_FLAG --------',5);
789: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
790: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
791: END IF;
792:
793: END GET_REPRESENTATIVE_FLAG;
786:
787: IF (g_debug_level <= 5) THEN
788: cln_debug_pub.Add('------- Exception in procedure GET_REPRESENTATIVE_FLAG --------',5);
789: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
790: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
791: END IF;
792:
793: END GET_REPRESENTATIVE_FLAG;
794:
806:
807: BEGIN
808:
809: IF (g_debug_level <= 2) THEN
810: cln_debug_pub.Add('Entering UPDATE_COLL_HIST procedure with parameters----', 2);
811: cln_debug_pub.Add('p_int_ctrl_num : '||p_int_ctrl_num, 2);
812: cln_debug_pub.Add('p_coll_hist_msg : '||p_coll_hist_msg, 2);
813: END IF;
814:
807: BEGIN
808:
809: IF (g_debug_level <= 2) THEN
810: cln_debug_pub.Add('Entering UPDATE_COLL_HIST procedure with parameters----', 2);
811: cln_debug_pub.Add('p_int_ctrl_num : '||p_int_ctrl_num, 2);
812: cln_debug_pub.Add('p_coll_hist_msg : '||p_coll_hist_msg, 2);
813: END IF;
814:
815: l_doc_number := '7B1 : ' || p_int_ctrl_num;
808:
809: IF (g_debug_level <= 2) THEN
810: cln_debug_pub.Add('Entering UPDATE_COLL_HIST procedure with parameters----', 2);
811: cln_debug_pub.Add('p_int_ctrl_num : '||p_int_ctrl_num, 2);
812: cln_debug_pub.Add('p_coll_hist_msg : '||p_coll_hist_msg, 2);
813: END IF;
814:
815: l_doc_number := '7B1 : ' || p_int_ctrl_num;
816:
814:
815: l_doc_number := '7B1 : ' || p_int_ctrl_num;
816:
817: IF (g_debug_level <= 1) THEN
818: cln_debug_pub.Add('l_doc_number : '|| l_doc_number,1);
819: END IF;
820:
821: l_update_cln_parameter_list := wf_parameter_list_t();
822:
825: WF_EVENT.AddParameterToList('COLLABORATION_POINT','APPS',l_update_cln_parameter_list);
826: WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_int_ctrl_num,l_update_cln_parameter_list);
827:
828: IF (g_debug_level <= 1) THEN
829: cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
830: cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
831: END IF;
832:
833: SELECT M4R_7B1_OSFM_S1.NEXTVAL
826: WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_int_ctrl_num,l_update_cln_parameter_list);
827:
828: IF (g_debug_level <= 1) THEN
829: cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
830: cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
831: END IF;
832:
833: SELECT M4R_7B1_OSFM_S1.NEXTVAL
834: INTO l_event_key
842:
843: x_resultout := 'S';
844:
845: IF (g_Debug_Level <= 2) THEN
846: cln_debug_pub.Add('----------- EXITING UPDATE_COLL_HIST ------------', 2);
847: cln_debug_pub.Add('x_resultout ' || x_resultout, 2);
848: END IF;
849:
850: EXCEPTION
843: x_resultout := 'S';
844:
845: IF (g_Debug_Level <= 2) THEN
846: cln_debug_pub.Add('----------- EXITING UPDATE_COLL_HIST ------------', 2);
847: cln_debug_pub.Add('x_resultout ' || x_resultout, 2);
848: END IF;
849:
850: EXCEPTION
851: WHEN OTHERS THEN
854:
855: x_resultout := 'F';
856:
857: IF (g_debug_level <= 5) THEN
858: cln_debug_pub.Add('------- Exception in procedure UPDATE_COLL_HIST --------',5);
859: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
860: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
861: cln_debug_pub.Add('x_resultout ' || x_resultout, 5);
862:
855: x_resultout := 'F';
856:
857: IF (g_debug_level <= 5) THEN
858: cln_debug_pub.Add('------- Exception in procedure UPDATE_COLL_HIST --------',5);
859: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
860: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
861: cln_debug_pub.Add('x_resultout ' || x_resultout, 5);
862:
863: END IF;
856:
857: IF (g_debug_level <= 5) THEN
858: cln_debug_pub.Add('------- Exception in procedure UPDATE_COLL_HIST --------',5);
859: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
860: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
861: cln_debug_pub.Add('x_resultout ' || x_resultout, 5);
862:
863: END IF;
864:
857: IF (g_debug_level <= 5) THEN
858: cln_debug_pub.Add('------- Exception in procedure UPDATE_COLL_HIST --------',5);
859: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
860: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
861: cln_debug_pub.Add('x_resultout ' || x_resultout, 5);
862:
863: END IF;
864:
865: END UPDATE_COLL_HIST;
883:
884: l_event_key := p_msg_id ||'.' || p_hdr_id;
885:
886: IF (g_debug_level <= 2) THEN
887: cln_debug_pub.Add('...Entering the procedure ADD_MSG_COLL_HIST with parameters...', 2);
888: cln_debug_pub.Add('Internal Control Number : '|| p_msg_id ,2);
889: cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
884: l_event_key := p_msg_id ||'.' || p_hdr_id;
885:
886: IF (g_debug_level <= 2) THEN
887: cln_debug_pub.Add('...Entering the procedure ADD_MSG_COLL_HIST with parameters...', 2);
888: cln_debug_pub.Add('Internal Control Number : '|| p_msg_id ,2);
889: cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
885:
886: IF (g_debug_level <= 2) THEN
887: cln_debug_pub.Add('...Entering the procedure ADD_MSG_COLL_HIST with parameters...', 2);
888: cln_debug_pub.Add('Internal Control Number : '|| p_msg_id ,2);
889: cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
886: IF (g_debug_level <= 2) THEN
887: cln_debug_pub.Add('...Entering the procedure ADD_MSG_COLL_HIST with parameters...', 2);
888: cln_debug_pub.Add('Internal Control Number : '|| p_msg_id ,2);
889: cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
894: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
887: cln_debug_pub.Add('...Entering the procedure ADD_MSG_COLL_HIST with parameters...', 2);
888: cln_debug_pub.Add('Internal Control Number : '|| p_msg_id ,2);
889: cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
894: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
895: cln_debug_pub.Add('p_err_string : '|| p_err_string ,2);
888: cln_debug_pub.Add('Internal Control Number : '|| p_msg_id ,2);
889: cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
894: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
895: cln_debug_pub.Add('p_err_string : '|| p_err_string ,2);
896: END IF;
889: cln_debug_pub.Add('p_ref_id2 -- Transaction type : '|| p_ref_id2 ,2); -- Transaction type
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
894: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
895: cln_debug_pub.Add('p_err_string : '|| p_err_string ,2);
896: END IF;
897:
890: cln_debug_pub.Add('p_ref_id3 -- Lot number : '|| p_ref_id3 ,2); -- Lot number
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
894: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
895: cln_debug_pub.Add('p_err_string : '|| p_err_string ,2);
896: END IF;
897:
898: l_parameter_list := wf_parameter_list_t();
891: cln_debug_pub.Add('p_ref_id4 -- Inventory Item : '|| p_ref_id4 ,2); -- Inventory Item
892: cln_debug_pub.Add('p_ref_id5 -- Quantity : '|| p_ref_id5 ,2); -- Quantity
893: cln_debug_pub.Add('p_hdr_id : '|| p_hdr_id ,2);
894: cln_debug_pub.Add('p_msg_id : '|| p_msg_id ,2);
895: cln_debug_pub.Add('p_err_string : '|| p_err_string ,2);
896: END IF;
897:
898: l_parameter_list := wf_parameter_list_t();
899: WF_EVENT.AddParameterToList('REFERENCE_ID1',l_event_key,l_parameter_list);
906:
907: g_exception_tracking_msg := 'Raising the ----oracle.apps.cln.ch.collaboration.addmessage----- event';
908:
909: IF (g_debug_level <= 2) THEN
910: cln_debug_pub.Add(g_exception_tracking_msg,2);
911: END IF;
912:
913: wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.addmessage',
914: p_event_key => l_event_key,
914: p_event_key => l_event_key,
915: p_parameters => l_parameter_list);
916:
917: IF (g_debug_level <= 2) THEN
918: cln_debug_pub.Add('Add Message event raised',2);
919: cln_debug_pub.Add('------ Exiting the procedure ADD_MSG_COLL_HIST ---- ', 2);
920: END IF;
921:
922: EXCEPTION
915: p_parameters => l_parameter_list);
916:
917: IF (g_debug_level <= 2) THEN
918: cln_debug_pub.Add('Add Message event raised',2);
919: cln_debug_pub.Add('------ Exiting the procedure ADD_MSG_COLL_HIST ---- ', 2);
920: END IF;
921:
922: EXCEPTION
923: WHEN OTHERS THEN
925: g_error_code := SQLCODE;
926: g_errmsg := SQLERRM;
927:
928: IF (g_debug_level <= 5) THEN
929: cln_debug_pub.Add('------- Exception in procedure ADD_MSG_COLL_HIST --------',5);
930: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
931: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
932: END IF;
933:
926: g_errmsg := SQLERRM;
927:
928: IF (g_debug_level <= 5) THEN
929: cln_debug_pub.Add('------- Exception in procedure ADD_MSG_COLL_HIST --------',5);
930: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
931: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
932: END IF;
933:
934: END ADD_MSG_COLL_HIST;
927:
928: IF (g_debug_level <= 5) THEN
929: cln_debug_pub.Add('------- Exception in procedure ADD_MSG_COLL_HIST --------',5);
930: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
931: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
932: END IF;
933:
934: END ADD_MSG_COLL_HIST;
935:
944:
945: BEGIN
946:
947: IF (g_debug_level <= 2) THEN
948: cln_debug_pub.Add('-------- Entering the procedure NOTIFY_TP ------', 2);
949: cln_debug_pub.Add('p_notif_code : '|| p_notif_code, 2);
950: cln_debug_pub.Add('p_notif_desc : '|| p_notif_desc, 2);
951: END IF;
952:
945: BEGIN
946:
947: IF (g_debug_level <= 2) THEN
948: cln_debug_pub.Add('-------- Entering the procedure NOTIFY_TP ------', 2);
949: cln_debug_pub.Add('p_notif_code : '|| p_notif_code, 2);
950: cln_debug_pub.Add('p_notif_desc : '|| p_notif_desc, 2);
951: END IF;
952:
953: g_exception_tracking_msg := 'Calling CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS procedure -------';
946:
947: IF (g_debug_level <= 2) THEN
948: cln_debug_pub.Add('-------- Entering the procedure NOTIFY_TP ------', 2);
949: cln_debug_pub.Add('p_notif_code : '|| p_notif_code, 2);
950: cln_debug_pub.Add('p_notif_desc : '|| p_notif_desc, 2);
951: END IF;
952:
953: g_exception_tracking_msg := 'Calling CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS procedure -------';
954:
952:
953: g_exception_tracking_msg := 'Calling CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS procedure -------';
954:
955: IF (g_debug_level <= 2) THEN
956: cln_debug_pub.Add(g_exception_tracking_msg, 2);
957: END IF;
958:
959: CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS( x_ret_code => x_return_code ,
960: x_ret_desc => x_return_desc,
966: p_coll_point => 'APPS',
967: p_int_con_no => g_intrl_cntrl_num);
968:
969: IF (g_debug_level <= 2) THEN
970: cln_debug_pub.Add('Exiting the ---- NOTIFY_TP ----- API with the below parameters...',2);
971: cln_debug_pub.Add('Return Code:'|| x_return_code, 2);
972: cln_debug_pub.Add('Return Description:'|| x_return_desc, 2);
973: END IF;
974:
967: p_int_con_no => g_intrl_cntrl_num);
968:
969: IF (g_debug_level <= 2) THEN
970: cln_debug_pub.Add('Exiting the ---- NOTIFY_TP ----- API with the below parameters...',2);
971: cln_debug_pub.Add('Return Code:'|| x_return_code, 2);
972: cln_debug_pub.Add('Return Description:'|| x_return_desc, 2);
973: END IF;
974:
975: EXCEPTION
968:
969: IF (g_debug_level <= 2) THEN
970: cln_debug_pub.Add('Exiting the ---- NOTIFY_TP ----- API with the below parameters...',2);
971: cln_debug_pub.Add('Return Code:'|| x_return_code, 2);
972: cln_debug_pub.Add('Return Description:'|| x_return_desc, 2);
973: END IF;
974:
975: EXCEPTION
976: WHEN OTHERS THEN
977: g_error_code := SQLCODE;
978: g_errmsg := SQLERRM;
979:
980: IF (g_debug_level <= 5) THEN
981: cln_debug_pub.Add('------- Exception in procedure NOTIFY_TP --------',5);
982: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
983: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
984: END IF;
985:
978: g_errmsg := SQLERRM;
979:
980: IF (g_debug_level <= 5) THEN
981: cln_debug_pub.Add('------- Exception in procedure NOTIFY_TP --------',5);
982: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
983: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
984: END IF;
985:
986: END NOTIFY_TP;
979:
980: IF (g_debug_level <= 5) THEN
981: cln_debug_pub.Add('------- Exception in procedure NOTIFY_TP --------',5);
982: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
983: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
984: END IF;
985:
986: END NOTIFY_TP;
987:
1002:
1003:
1004: BEGIN
1005: IF (g_debug_level <= 2) THEN
1006: cln_debug_pub.Add('-------- Entering the procedure PROCESS_NOTIFICATION --------', 2);
1007: cln_debug_pub.Add('p_itemtype : '|| p_itemtype, 2);
1008: cln_debug_pub.Add('p_itemkey : '|| p_itemkey, 2);
1009: cln_debug_pub.Add('p_actid : '|| p_actid, 2);
1010: cln_debug_pub.Add('p_funcmode : '|| p_funcmode, 2);
1003:
1004: BEGIN
1005: IF (g_debug_level <= 2) THEN
1006: cln_debug_pub.Add('-------- Entering the procedure PROCESS_NOTIFICATION --------', 2);
1007: cln_debug_pub.Add('p_itemtype : '|| p_itemtype, 2);
1008: cln_debug_pub.Add('p_itemkey : '|| p_itemkey, 2);
1009: cln_debug_pub.Add('p_actid : '|| p_actid, 2);
1010: cln_debug_pub.Add('p_funcmode : '|| p_funcmode, 2);
1011: END IF;
1004: BEGIN
1005: IF (g_debug_level <= 2) THEN
1006: cln_debug_pub.Add('-------- Entering the procedure PROCESS_NOTIFICATION --------', 2);
1007: cln_debug_pub.Add('p_itemtype : '|| p_itemtype, 2);
1008: cln_debug_pub.Add('p_itemkey : '|| p_itemkey, 2);
1009: cln_debug_pub.Add('p_actid : '|| p_actid, 2);
1010: cln_debug_pub.Add('p_funcmode : '|| p_funcmode, 2);
1011: END IF;
1012:
1005: IF (g_debug_level <= 2) THEN
1006: cln_debug_pub.Add('-------- Entering the procedure PROCESS_NOTIFICATION --------', 2);
1007: cln_debug_pub.Add('p_itemtype : '|| p_itemtype, 2);
1008: cln_debug_pub.Add('p_itemkey : '|| p_itemkey, 2);
1009: cln_debug_pub.Add('p_actid : '|| p_actid, 2);
1010: cln_debug_pub.Add('p_funcmode : '|| p_funcmode, 2);
1011: END IF;
1012:
1013: g_tp_frm_code := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER4');
1006: cln_debug_pub.Add('-------- Entering the procedure PROCESS_NOTIFICATION --------', 2);
1007: cln_debug_pub.Add('p_itemtype : '|| p_itemtype, 2);
1008: cln_debug_pub.Add('p_itemkey : '|| p_itemkey, 2);
1009: cln_debug_pub.Add('p_actid : '|| p_actid, 2);
1010: cln_debug_pub.Add('p_funcmode : '|| p_funcmode, 2);
1011: END IF;
1012:
1013: g_tp_frm_code := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER4');
1014: IF (g_debug_level <= 1) THEN
1011: END IF;
1012:
1013: g_tp_frm_code := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER4');
1014: IF (g_debug_level <= 1) THEN
1015: cln_debug_pub.Add('g_tp_frm_code : ' || g_tp_frm_code, 1);
1016: END IF;
1017:
1018: g_intrl_cntrl_num := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER2');
1019: IF (g_debug_level <= 1) THEN
1016: END IF;
1017:
1018: g_intrl_cntrl_num := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER2');
1019: IF (g_debug_level <= 1) THEN
1020: cln_debug_pub.Add('g_intrl_cntrl_num : ' || g_intrl_cntrl_num, 1);
1021: END IF;
1022:
1023: l_notif_code := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_CODE');
1024: IF (g_debug_level <= 1) THEN
1021: END IF;
1022:
1023: l_notif_code := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_CODE');
1024: IF (g_debug_level <= 1) THEN
1025: cln_debug_pub.Add('l_notif_code : ' || l_notif_code, 1);
1026: END IF;
1027:
1028: l_notif_desc := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_DESC');
1029: IF (g_debug_level <= 1) THEN
1026: END IF;
1027:
1028: l_notif_desc := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_DESC');
1029: IF (g_debug_level <= 1) THEN
1030: cln_debug_pub.Add('l_notif_desc : ' || l_notif_desc, 1);
1031: END IF;
1032:
1033: NOTIFY_TP (l_notif_code,l_notif_desc,l_return_code,l_return_desc);
1034:
1038: x_resultout := 'SUCCESS';
1039: END IF;
1040:
1041: IF (g_debug_level <= 2) THEN
1042: cln_debug_pub.Add('-------- Exiting the procedure PROCESS_NOTIFICATION --------', 2);
1043: cln_debug_pub.Add('x_resultout : ' || x_resultout, 2);
1044: END IF;
1045:
1046: EXCEPTION
1039: END IF;
1040:
1041: IF (g_debug_level <= 2) THEN
1042: cln_debug_pub.Add('-------- Exiting the procedure PROCESS_NOTIFICATION --------', 2);
1043: cln_debug_pub.Add('x_resultout : ' || x_resultout, 2);
1044: END IF;
1045:
1046: EXCEPTION
1047: WHEN OTHERS THEN
1051:
1052: x_resultout := 'ERROR';
1053:
1054: IF (g_debug_level <= 5) THEN
1055: cln_debug_pub.Add('------- Exception in procedure PROCESS_NOTIFICATION --------',5);
1056: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
1057: END IF;
1058:
1059: END PROCESS_NOTIFICATION;
1052: x_resultout := 'ERROR';
1053:
1054: IF (g_debug_level <= 5) THEN
1055: cln_debug_pub.Add('------- Exception in procedure PROCESS_NOTIFICATION --------',5);
1056: cln_debug_pub.Add('Error is - ' || g_error_code || ':' || g_errmsg, 5);
1057: END IF;
1058:
1059: END PROCESS_NOTIFICATION;
1060:
1123: l_errloop_cnt NUMBER;
1124: BEGIN
1125:
1126: IF (g_debug_level <= 2) THEN
1127: cln_debug_pub.Add('-------- Entering procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1128: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1129: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1130: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1131: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1124: BEGIN
1125:
1126: IF (g_debug_level <= 2) THEN
1127: cln_debug_pub.Add('-------- Entering procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1128: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1129: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1130: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1131: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1132: END IF;
1125:
1126: IF (g_debug_level <= 2) THEN
1127: cln_debug_pub.Add('-------- Entering procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1128: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1129: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1130: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1131: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1132: END IF;
1133:
1126: IF (g_debug_level <= 2) THEN
1127: cln_debug_pub.Add('-------- Entering procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1128: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1129: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1130: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1131: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1132: END IF;
1133:
1134: SELECT wsm_lot_move_txn_interface_s.NEXTVAL
1127: cln_debug_pub.Add('-------- Entering procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1128: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1129: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1130: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1131: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1132: END IF;
1133:
1134: SELECT wsm_lot_move_txn_interface_s.NEXTVAL
1135: INTO l_header_id
1143: INTO l_group_id
1144: FROM DUAL;
1145:
1146: IF (g_debug_level <= 1) THEN
1147: cln_debug_pub.Add('l_header_id : ' || l_header_id, 1);
1148: cln_debug_pub.Add('l_txn_id : ' || l_txn_id, 1);
1149: cln_debug_pub.Add('l_group_id : ' || l_group_id, 1);
1150: END IF;
1151:
1144: FROM DUAL;
1145:
1146: IF (g_debug_level <= 1) THEN
1147: cln_debug_pub.Add('l_header_id : ' || l_header_id, 1);
1148: cln_debug_pub.Add('l_txn_id : ' || l_txn_id, 1);
1149: cln_debug_pub.Add('l_group_id : ' || l_group_id, 1);
1150: END IF;
1151:
1152: IF (g_debug_level <= 2) THEN
1145:
1146: IF (g_debug_level <= 1) THEN
1147: cln_debug_pub.Add('l_header_id : ' || l_header_id, 1);
1148: cln_debug_pub.Add('l_txn_id : ' || l_txn_id, 1);
1149: cln_debug_pub.Add('l_group_id : ' || l_group_id, 1);
1150: END IF;
1151:
1152: IF (g_debug_level <= 2) THEN
1153: cln_debug_pub.Add('----- Calling GET_JOB_DETAILS with parameters ------------', 2);
1149: cln_debug_pub.Add('l_group_id : ' || l_group_id, 1);
1150: END IF;
1151:
1152: IF (g_debug_level <= 2) THEN
1153: cln_debug_pub.Add('----- Calling GET_JOB_DETAILS with parameters ------------', 2);
1154: END IF;
1155:
1156: GET_JOB_DETAILS ( p_hdr_rec.lot_number,
1157: p_hdr_rec.ALT_ROUTING_DESIGNATOR,
1212:
1213: l_jump_flag := 'N';
1214:
1215: IF (g_debug_level <= 1) THEN
1216: cln_debug_pub.Add('Not a Jump operation', 1);
1217: END IF;
1218:
1219: EXCEPTION
1220: WHEN NO_DATA_FOUND THEN
1221:
1222: l_jump_flag := 'Y';
1223:
1224: IF (g_debug_level <= 1) THEN
1225: cln_debug_pub.Add('l_jump_flag : '|| l_jump_flag, 1);
1226: END IF;
1227: END;
1228: END IF;
1229:
1237: WHERE standard_operation_id = l_to_std_op_id
1238: AND organization_id = p_org_id;
1239:
1240: IF (g_debug_level <= 1) THEN
1241: cln_debug_pub.Add('l_op_code : '|| l_op_code, 1);
1242: END IF;
1243: EXCEPTION
1244: WHEN NO_DATA_FOUND THEN
1245:
1243: EXCEPTION
1244: WHEN NO_DATA_FOUND THEN
1245:
1246: IF (g_debug_level <= 5) THEN
1247: cln_debug_pub.Add('Warning ---- No data found in query to find op_code ------',5);
1248: END IF;
1249: END;
1250:
1251: GET_INTRAOPERATION_STEP (l_wip_entity_id,l_fm_intra_op_step,l_avbl_qty,l_fm_op_seq_num);
1300: END IF;
1301: END IF;
1302:
1303: IF (g_debug_level <= 1) THEN
1304: cln_debug_pub.Add('l_trx_qty : '|| l_trx_qty, 1);
1305: cln_debug_pub.Add('l_to_intra_op : '|| l_to_intra_op, 1);
1306: END IF;
1307:
1308: SAVEPOINT before_insert;
1301: END IF;
1302:
1303: IF (g_debug_level <= 1) THEN
1304: cln_debug_pub.Add('l_trx_qty : '|| l_trx_qty, 1);
1305: cln_debug_pub.Add('l_to_intra_op : '|| l_to_intra_op, 1);
1306: END IF;
1307:
1308: SAVEPOINT before_insert;
1309:
1399: WHERE msg_id = p_hdr_rec.msg_id
1400: AND hdr_id = p_hdr_rec.hdr_id;
1401:
1402: IF (g_debug_level <= 1) THEN
1403: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
1404: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
1405: END IF;
1406:
1407: IF l_custom_valid_pass = 'FAIL' THEN
1400: AND hdr_id = p_hdr_rec.hdr_id;
1401:
1402: IF (g_debug_level <= 1) THEN
1403: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
1404: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
1405: END IF;
1406:
1407: IF l_custom_valid_pass = 'FAIL' THEN
1408:
1428:
1429: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,NULL);
1430:
1431: IF (g_debug_level <= 2) THEN
1432: cln_debug_pub.Add('-------- Calling WSMPLBMI.MoveTransaction procedure --------',2);
1433: END IF;
1434:
1435: WSMPLBMI.MoveTransaction (l_retcode, l_errbuf , l_group_id );
1436:
1434:
1435: WSMPLBMI.MoveTransaction (l_retcode, l_errbuf , l_group_id );
1436:
1437: IF (g_debug_level <= 2) THEN
1438: cln_debug_pub.Add('-------- Out of WSMPLBMI.MoveTransaction --------',2);
1439: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1440: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1441: END IF;
1442:
1435: WSMPLBMI.MoveTransaction (l_retcode, l_errbuf , l_group_id );
1436:
1437: IF (g_debug_level <= 2) THEN
1438: cln_debug_pub.Add('-------- Out of WSMPLBMI.MoveTransaction --------',2);
1439: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1440: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1441: END IF;
1442:
1443: BEGIN
1436:
1437: IF (g_debug_level <= 2) THEN
1438: cln_debug_pub.Add('-------- Out of WSMPLBMI.MoveTransaction --------',2);
1439: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1440: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1441: END IF;
1442:
1443: BEGIN
1444:
1452: FROM WSM_INTERFACE_ERRORS
1453: WHERE transaction_id = l_txn_id)
1454: LOOP
1455: IF (g_debug_level <= 1) THEN
1456: cln_debug_pub.Add('Loop error : ' || i.message, 1);
1457: END IF;
1458:
1459: l_errloop_cnt := l_errloop_cnt + 1;
1460:
1473: FROM WSM_INTERFACE_ERRORS
1474: WHERE transaction_id = l_txn_id;
1475:
1476: IF (g_debug_level <= 1) THEN
1477: cln_debug_pub.Add('l_err_msg1 : ' || l_err_msg1, 1);
1478: END IF;
1479: */
1480: g_exception_tracking_msg := 'Querying WSM_LOT_JOB_INTERFACE for Errors';
1481:
1484: FROM WSM_LOT_JOB_INTERFACE
1485: WHERE header_id = l_header_id;
1486:
1487: IF (g_debug_level <= 1) THEN
1488: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
1489: cln_debug_pub.Add('l_err_msg2 : ' || l_err_msg2, 1);
1490: END IF;
1491:
1492: l_err_flag := 'Y';
1485: WHERE header_id = l_header_id;
1486:
1487: IF (g_debug_level <= 1) THEN
1488: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
1489: cln_debug_pub.Add('l_err_msg2 : ' || l_err_msg2, 1);
1490: END IF;
1491:
1492: l_err_flag := 'Y';
1493:
1497:
1498: --l_err_flag := 'N';
1499:
1500: IF (g_debug_level <= 5) THEN
1501: cln_debug_pub.Add(g_exception_tracking_msg,5);
1502: cln_debug_pub.Add('----- No data found -----',5);
1503: cln_debug_pub.Add('l_err_flag : '|| l_err_flag,5);
1504: END IF;
1505: END;
1498: --l_err_flag := 'N';
1499:
1500: IF (g_debug_level <= 5) THEN
1501: cln_debug_pub.Add(g_exception_tracking_msg,5);
1502: cln_debug_pub.Add('----- No data found -----',5);
1503: cln_debug_pub.Add('l_err_flag : '|| l_err_flag,5);
1504: END IF;
1505: END;
1506:
1499:
1500: IF (g_debug_level <= 5) THEN
1501: cln_debug_pub.Add(g_exception_tracking_msg,5);
1502: cln_debug_pub.Add('----- No data found -----',5);
1503: cln_debug_pub.Add('l_err_flag : '|| l_err_flag,5);
1504: END IF;
1505: END;
1506:
1507:
1532:
1533: x_resultout := 'CONTINUE';
1534:
1535: IF (g_debug_level <= 2) THEN
1536: cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1537: cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
1538: END IF;
1539:
1540: EXCEPTION
1533: x_resultout := 'CONTINUE';
1534:
1535: IF (g_debug_level <= 2) THEN
1536: cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',2);
1537: cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
1538: END IF;
1539:
1540: EXCEPTION
1541: WHEN return_code_false THEN
1540: EXCEPTION
1541: WHEN return_code_false THEN
1542:
1543: IF (g_debug_level <= 5) THEN
1544: cln_debug_pub.Add('------- Exception in procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1545: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1546: END IF;
1547:
1548: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1541: WHEN return_code_false THEN
1542:
1543: IF (g_debug_level <= 5) THEN
1544: cln_debug_pub.Add('------- Exception in procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1545: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1546: END IF;
1547:
1548: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1549:
1559:
1560: x_resultout := 'FAILED';
1561:
1562: IF (g_debug_level <= 5) THEN
1563: cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1564: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
1565: END IF;
1566:
1567: WHEN OTHERS THEN
1560: x_resultout := 'FAILED';
1561:
1562: IF (g_debug_level <= 5) THEN
1563: cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1564: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
1565: END IF;
1566:
1567: WHEN OTHERS THEN
1568: g_error_code := SQLCODE;
1570:
1571: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
1572:
1573: IF (g_debug_level <= 5) THEN
1574: cln_debug_pub.Add('------- Exception in procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1575: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1576: END IF;
1577:
1578: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1571: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
1572:
1573: IF (g_debug_level <= 5) THEN
1574: cln_debug_pub.Add('------- Exception in procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1575: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1576: END IF;
1577:
1578: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1579:
1594:
1595: x_resultout := 'FAILED';
1596:
1597: IF (g_debug_level <= 5) THEN
1598: cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1599: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
1600: END IF;
1601:
1602: END JOB_SCRAP_COMPLETE_UNDO;
1595: x_resultout := 'FAILED';
1596:
1597: IF (g_debug_level <= 5) THEN
1598: cln_debug_pub.Add('-------- Exiting procedure JOB_SCRAP_COMPLETE_UNDO --------',5);
1599: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
1600: END IF;
1601:
1602: END JOB_SCRAP_COMPLETE_UNDO;
1603:
1640:
1641: BEGIN
1642:
1643: IF (g_debug_level <= 2) THEN
1644: cln_debug_pub.Add('-------- Entering procedure JOB_CREATE_OR_STATUS --------',2);
1645: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1646: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1647: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1648: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1641: BEGIN
1642:
1643: IF (g_debug_level <= 2) THEN
1644: cln_debug_pub.Add('-------- Entering procedure JOB_CREATE_OR_STATUS --------',2);
1645: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1646: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1647: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1648: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1649: END IF;
1642:
1643: IF (g_debug_level <= 2) THEN
1644: cln_debug_pub.Add('-------- Entering procedure JOB_CREATE_OR_STATUS --------',2);
1645: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1646: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1647: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1648: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1649: END IF;
1650:
1643: IF (g_debug_level <= 2) THEN
1644: cln_debug_pub.Add('-------- Entering procedure JOB_CREATE_OR_STATUS --------',2);
1645: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1646: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1647: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1648: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1649: END IF;
1650:
1651: SELECT wsm_lot_sm_ifc_header_s.NEXTVAL
1644: cln_debug_pub.Add('-------- Entering procedure JOB_CREATE_OR_STATUS --------',2);
1645: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
1646: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
1647: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
1648: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
1649: END IF;
1650:
1651: SELECT wsm_lot_sm_ifc_header_s.NEXTVAL
1652: INTO l_header_id
1667: ELSE l_mode := 1;
1668: END IF;
1669:
1670: IF (g_debug_level <= 1) THEN
1671: cln_debug_pub.Add('l_header_id : '|| l_header_id,1);
1672: cln_debug_pub.Add('l_group_id : '|| l_group_id,1);
1673: cln_debug_pub.Add('l_sch_method : '|| l_sch_method,1);
1674: cln_debug_pub.Add('l_mode : '|| l_mode,1);
1675: END IF;
1668: END IF;
1669:
1670: IF (g_debug_level <= 1) THEN
1671: cln_debug_pub.Add('l_header_id : '|| l_header_id,1);
1672: cln_debug_pub.Add('l_group_id : '|| l_group_id,1);
1673: cln_debug_pub.Add('l_sch_method : '|| l_sch_method,1);
1674: cln_debug_pub.Add('l_mode : '|| l_mode,1);
1675: END IF;
1676:
1669:
1670: IF (g_debug_level <= 1) THEN
1671: cln_debug_pub.Add('l_header_id : '|| l_header_id,1);
1672: cln_debug_pub.Add('l_group_id : '|| l_group_id,1);
1673: cln_debug_pub.Add('l_sch_method : '|| l_sch_method,1);
1674: cln_debug_pub.Add('l_mode : '|| l_mode,1);
1675: END IF;
1676:
1677: IF p_hdr_rec.transaction_type = 'HOLD' OR
1670: IF (g_debug_level <= 1) THEN
1671: cln_debug_pub.Add('l_header_id : '|| l_header_id,1);
1672: cln_debug_pub.Add('l_group_id : '|| l_group_id,1);
1673: cln_debug_pub.Add('l_sch_method : '|| l_sch_method,1);
1674: cln_debug_pub.Add('l_mode : '|| l_mode,1);
1675: END IF;
1676:
1677: IF p_hdr_rec.transaction_type = 'HOLD' OR
1678: (( p_hdr_rec.transaction_type = 'STATUS') AND (p_hdr_rec.status_change_code = 'HOLD'))THEN
1693: l_lot_class_code := p_qty_rec.lot_classification_code;
1694: END IF;
1695:
1696: IF (g_debug_level <= 1) THEN
1697: cln_debug_pub.Add('l_lot_class_code : ' ||l_lot_class_code,1);
1698: END IF;
1699:
1700: GET_INV_ITEM_DETAILS( p_hdr_rec.starting_lot_item_code,
1701: p_hdr_rec.primary_item_code,
1730: INTO l_source_line_id
1731: FROM DUAL;
1732:
1733: IF (g_debug_level <= 1) THEN
1734: cln_debug_pub.Add('l_comp_sub_inventory : '|| l_comp_sub_inventory,1);
1735: cln_debug_pub.Add('l_comp_locator_id : '|| l_comp_locator_id,1);
1736: cln_debug_pub.Add('l_trx_id : '|| l_trx_id,1);
1737: cln_debug_pub.Add('l_source_line_id : '|| l_source_line_id,1);
1738: END IF;
1731: FROM DUAL;
1732:
1733: IF (g_debug_level <= 1) THEN
1734: cln_debug_pub.Add('l_comp_sub_inventory : '|| l_comp_sub_inventory,1);
1735: cln_debug_pub.Add('l_comp_locator_id : '|| l_comp_locator_id,1);
1736: cln_debug_pub.Add('l_trx_id : '|| l_trx_id,1);
1737: cln_debug_pub.Add('l_source_line_id : '|| l_source_line_id,1);
1738: END IF;
1739:
1732:
1733: IF (g_debug_level <= 1) THEN
1734: cln_debug_pub.Add('l_comp_sub_inventory : '|| l_comp_sub_inventory,1);
1735: cln_debug_pub.Add('l_comp_locator_id : '|| l_comp_locator_id,1);
1736: cln_debug_pub.Add('l_trx_id : '|| l_trx_id,1);
1737: cln_debug_pub.Add('l_source_line_id : '|| l_source_line_id,1);
1738: END IF;
1739:
1740: SAVEPOINT before_insert;
1733: IF (g_debug_level <= 1) THEN
1734: cln_debug_pub.Add('l_comp_sub_inventory : '|| l_comp_sub_inventory,1);
1735: cln_debug_pub.Add('l_comp_locator_id : '|| l_comp_locator_id,1);
1736: cln_debug_pub.Add('l_trx_id : '|| l_trx_id,1);
1737: cln_debug_pub.Add('l_source_line_id : '|| l_source_line_id,1);
1738: END IF;
1739:
1740: SAVEPOINT before_insert;
1741:
1772:
1773: END IF; --p_process_type= 'JOB_CREATION' AND p_mode = 2
1774:
1775: IF (g_debug_level <= 1) THEN
1776: cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_LOTS_INTERFACE --------',1);
1777: END IF;
1778:
1779: INSERT
1780: INTO WSM_LOT_JOB_INTERFACE ( MODE_FLAG,
1831: l_sch_method,
1832: 'Y');
1833:
1834: IF (g_debug_level <= 1) THEN
1835: cln_debug_pub.Add('-------- Values successfully inserted into WSM_LOT_JOB_INTERFACE --------',1);
1836: END IF;
1837:
1838: wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'LOT_NUMBER',p_hdr_rec.lot_number);
1839: wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'INV_ITEM_CODE',p_hdr_rec.starting_lot_item_code);
1851: WHERE msg_id = p_hdr_rec.msg_id
1852: AND hdr_id = p_hdr_rec.hdr_id;
1853:
1854: IF (g_debug_level <= 1) THEN
1855: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
1856: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
1857: END IF;
1858:
1859: IF l_custom_valid_pass = 'FAIL' THEN
1852: AND hdr_id = p_hdr_rec.hdr_id;
1853:
1854: IF (g_debug_level <= 1) THEN
1855: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
1856: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
1857: END IF;
1858:
1859: IF l_custom_valid_pass = 'FAIL' THEN
1860:
1883:
1884: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_header_id);
1885:
1886: IF (g_debug_level <= 2) THEN
1887: cln_debug_pub.Add('-------- Calling WSMPLBJI.process_interface_rows --------',2);
1888: cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
1889: END IF;
1890:
1891: WSMPLBJI.process_interface_rows (l_retcode,l_errbuf,l_group_id);
1884: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_header_id);
1885:
1886: IF (g_debug_level <= 2) THEN
1887: cln_debug_pub.Add('-------- Calling WSMPLBJI.process_interface_rows --------',2);
1888: cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
1889: END IF;
1890:
1891: WSMPLBJI.process_interface_rows (l_retcode,l_errbuf,l_group_id);
1892:
1890:
1891: WSMPLBJI.process_interface_rows (l_retcode,l_errbuf,l_group_id);
1892:
1893: IF (g_debug_level <= 2) THEN
1894: cln_debug_pub.Add('-------- Out of WSMPLBJI.process_interface_rows --------',2);
1895: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1896: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1897: END IF;
1898: END IF;
1891: WSMPLBJI.process_interface_rows (l_retcode,l_errbuf,l_group_id);
1892:
1893: IF (g_debug_level <= 2) THEN
1894: cln_debug_pub.Add('-------- Out of WSMPLBJI.process_interface_rows --------',2);
1895: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1896: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1897: END IF;
1898: END IF;
1899:
1892:
1893: IF (g_debug_level <= 2) THEN
1894: cln_debug_pub.Add('-------- Out of WSMPLBJI.process_interface_rows --------',2);
1895: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
1896: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
1897: END IF;
1898: END IF;
1899:
1900: x_resultout := 'CONTINUE';
1899:
1900: x_resultout := 'CONTINUE';
1901:
1902: IF (g_debug_level <= 2) THEN
1903: cln_debug_pub.Add('-------- Exiting procedure JOB_CREATE_OR_STATUS --------',2);
1904: cln_debug_pub.Add('x_resultout : ' || x_resultout, 2);
1905: END IF;
1906:
1907: EXCEPTION
1900: x_resultout := 'CONTINUE';
1901:
1902: IF (g_debug_level <= 2) THEN
1903: cln_debug_pub.Add('-------- Exiting procedure JOB_CREATE_OR_STATUS --------',2);
1904: cln_debug_pub.Add('x_resultout : ' || x_resultout, 2);
1905: END IF;
1906:
1907: EXCEPTION
1908: WHEN return_code_false THEN
1907: EXCEPTION
1908: WHEN return_code_false THEN
1909:
1910: IF (g_debug_level <= 5) THEN
1911: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
1912: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1913: END IF;
1914:
1915: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1908: WHEN return_code_false THEN
1909:
1910: IF (g_debug_level <= 5) THEN
1911: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
1912: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1913: END IF;
1914:
1915: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
1916:
1932:
1933: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
1934:
1935: IF (g_debug_level <= 5) THEN
1936: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
1937: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1938: END IF;
1939:
1940: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_header_id);
1933: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
1934:
1935: IF (g_debug_level <= 5) THEN
1936: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
1937: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
1938: END IF;
1939:
1940: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_header_id);
1941:
2048:
2049: l_result_qty := 0;
2050:
2051: IF (g_debug_level <= 2) THEN
2052: cln_debug_pub.Add('-------- Entering procedure WIP_LOT_TXNS --------',2);
2053: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2054: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2055: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2056: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
2049: l_result_qty := 0;
2050:
2051: IF (g_debug_level <= 2) THEN
2052: cln_debug_pub.Add('-------- Entering procedure WIP_LOT_TXNS --------',2);
2053: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2054: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2055: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2056: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
2057: END IF;
2050:
2051: IF (g_debug_level <= 2) THEN
2052: cln_debug_pub.Add('-------- Entering procedure WIP_LOT_TXNS --------',2);
2053: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2054: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2055: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2056: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
2057: END IF;
2058:
2051: IF (g_debug_level <= 2) THEN
2052: cln_debug_pub.Add('-------- Entering procedure WIP_LOT_TXNS --------',2);
2053: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2054: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2055: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2056: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
2057: END IF;
2058:
2059: SELECT wsm_sm_txn_int_group_s.NEXTVAL
2052: cln_debug_pub.Add('-------- Entering procedure WIP_LOT_TXNS --------',2);
2053: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2054: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2055: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2056: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
2057: END IF;
2058:
2059: SELECT wsm_sm_txn_int_group_s.NEXTVAL
2060: INTO l_group_id
2070:
2071: SAVEPOINT before_insert;
2072:
2073: IF (g_debug_level <= 1) THEN
2074: cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
2075: cln_debug_pub.Add('l_header_id : ' || l_header_id, 2);
2076: cln_debug_pub.Add('l_trx_id : ' || l_trx_id, 2);
2077: END IF;
2078:
2071: SAVEPOINT before_insert;
2072:
2073: IF (g_debug_level <= 1) THEN
2074: cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
2075: cln_debug_pub.Add('l_header_id : ' || l_header_id, 2);
2076: cln_debug_pub.Add('l_trx_id : ' || l_trx_id, 2);
2077: END IF;
2078:
2079: INSERT
2072:
2073: IF (g_debug_level <= 1) THEN
2074: cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
2075: cln_debug_pub.Add('l_header_id : ' || l_header_id, 2);
2076: cln_debug_pub.Add('l_trx_id : ' || l_trx_id, 2);
2077: END IF;
2078:
2079: INSERT
2080: INTO WSM_SPLIT_MERGE_TXN_INTERFACE ( HEADER_ID,
2102: p_user_id);
2103:
2104:
2105: IF (g_debug_level <= 1) THEN
2106: cln_debug_pub.Add('-------- Values successfully inserted into WSM_SPLIT_MERGE_TXN_INTERFACE --------',1);
2107: END IF;
2108:
2109:
2110: IF p_process_type = 'WIP_MERGE' THEN
2117:
2118: g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2119:
2120: IF (g_debug_level <= 1) THEN
2121: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2122: END IF;
2123:
2124: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2125: l_err_msg :=FND_MESSAGE.GET;
2164:
2165: g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2166:
2167: IF (g_debug_level <= 1) THEN
2168: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2169: END IF;
2170:
2171: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2172: l_interface_err1 :=FND_MESSAGE.GET;
2227:
2228: l_result_qty := l_result_qty + l_rec.prev_lot_qty;
2229:
2230: IF (g_debug_level <= 1) THEN
2231: cln_debug_pub.Add('l_result_qty : ' || l_result_qty,1);
2232: cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE for Job '|| l_rec.prev_lot_number,1);
2233: END IF;
2234: END IF;
2235: END LOOP;
2228: l_result_qty := l_result_qty + l_rec.prev_lot_qty;
2229:
2230: IF (g_debug_level <= 1) THEN
2231: cln_debug_pub.Add('l_result_qty : ' || l_result_qty,1);
2232: cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE for Job '|| l_rec.prev_lot_number,1);
2233: END IF;
2234: END IF;
2235: END LOOP;
2236:
2247: AND we.ORGANIZATION_ID = p_org_id
2248: AND we.wip_entity_id = wd.wip_entity_id;
2249:
2250: IF (g_debug_level <= 1) THEN
2251: cln_debug_pub.Add('l_prev_wip_entity_id : ' || l_prev_wip_entity_id, 1);
2252: cln_debug_pub.Add('l_net_qty : ' || l_net_qty, 1);
2253: END IF;
2254: EXCEPTION
2255: WHEN NO_DATA_FOUND THEN
2248: AND we.wip_entity_id = wd.wip_entity_id;
2249:
2250: IF (g_debug_level <= 1) THEN
2251: cln_debug_pub.Add('l_prev_wip_entity_id : ' || l_prev_wip_entity_id, 1);
2252: cln_debug_pub.Add('l_net_qty : ' || l_net_qty, 1);
2253: END IF;
2254: EXCEPTION
2255: WHEN NO_DATA_FOUND THEN
2256:
2257: g_error_code := SQLCODE;
2258: g_errmsg := SQLERRM;
2259:
2260: IF (g_debug_level <= 5) THEN
2261: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
2262: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
2263: END IF;
2264:
2265: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
2258: g_errmsg := SQLERRM;
2259:
2260: IF (g_debug_level <= 5) THEN
2261: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
2262: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
2263: END IF;
2264:
2265: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
2266: FND_MESSAGE.SET_TOKEN('JOB_NAME',p_hdr_rec.prev_lot_number);
2275:
2276: g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE for hdr_id : '|| p_hdr_rec.hdr_id;
2277:
2278: IF (g_debug_level <= 1) THEN
2279: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2280: END IF;
2281:
2282: INSERT
2283: INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
2303: sysdate,
2304: p_user_id);
2305:
2306: IF (g_debug_level <= 2) THEN
2307: cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE --------',2);
2308: END IF;
2309:
2310: FOR l_rec IN M4R_7B1_WSM_WIP_SPLIT_C1(p_hdr_rec.msg_id,p_hdr_rec.prev_lot_number,p_hdr_rec.from_sub_inventory) LOOP
2311:
2316:
2317: g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2318:
2319: IF (g_debug_level <= 1) THEN
2320: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2321: END IF;
2322:
2323: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2324: l_err_msg :=FND_MESSAGE.GET;
2352:
2353: g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
2354:
2355: IF (g_debug_level <= 1) THEN
2356: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2357: END IF;
2358:
2359: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
2360: l_interface_err1 :=FND_MESSAGE.GET;
2375:
2376: g_exception_tracking_msg := 'Inserting values into WSM_RESULTING_JOBS_INTERFACE for hdr_id : '|| l_rec.hdr_id;
2377:
2378: IF (g_debug_level <= 1) THEN
2379: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2380: END IF;
2381:
2382: INSERT
2383: INTO WSM_RESULTING_JOBS_INTERFACE
2443:
2444: g_exception_tracking_msg := '-------- Values successfully inserted into WSM_RESULTING_JOBS_INTERFACE --------';
2445:
2446: IF (g_debug_level <= 1) THEN
2447: cln_debug_pub.Add(g_exception_tracking_msg,1);
2448: END IF;
2449:
2450: ELSIF p_process_type = 'JOB_UPDATE' THEN
2451:
2486: g_error_code := SQLCODE;
2487: g_errmsg := SQLERRM;
2488:
2489: IF (g_debug_level <= 5) THEN
2490: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
2491: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
2492: END IF;
2493:
2494: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
2487: g_errmsg := SQLERRM;
2488:
2489: IF (g_debug_level <= 5) THEN
2490: cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg, 5);
2491: cln_debug_pub.Add('Error is ' || g_error_code || ' : ' || g_errmsg, 5);
2492: END IF;
2493:
2494: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_NO_JOB');
2495: FND_MESSAGE.SET_TOKEN('JOB_NAME',l_job_name);
2500: RAISE return_code_false;
2501: END;
2502:
2503: IF (g_debug_level <= 1) THEN
2504: cln_debug_pub.Add('l_wip_entity_id : ' || l_wip_entity_id, 1);
2505: END IF;
2506:
2507: GET_INTRAOPERATION_STEP (l_wip_entity_id ,l_intra_step,l_avbl_qty,l_fm_op_seq_num);
2508:
2508:
2509: g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE';
2510:
2511: IF (g_debug_level <= 1) THEN
2512: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2513: END IF;
2514:
2515: INSERT
2516: INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
2538:
2539: g_exception_tracking_msg := '-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE --------';
2540:
2541: IF (g_debug_level <= 1) THEN
2542: cln_debug_pub.Add(g_exception_tracking_msg,1);
2543: END IF;
2544:
2545: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
2546:
2546:
2547: l_result_qty := p_qty_rec.lot_qty;
2548:
2549: IF (g_debug_level <= 1) THEN
2550: cln_debug_pub.Add('l_result_qty : ' || l_result_qty,1);
2551: END IF;
2552:
2553: END IF;
2554:
2573:
2574: GET_BONUS_SCRAP_ACC_ID(p_hdr_rec.additional_text,'BONUS',l_bonus_acc_id);
2575:
2576: IF (g_debug_level <= 1) THEN
2577: cln_debug_pub.Add('l_bonus_acc_id : ' || l_bonus_acc_id,1);
2578: END IF;
2579:
2580: g_exception_tracking_msg := 'Inserting values into WSM_RESULTING_JOBS_INTERFACE';
2581:
2579:
2580: g_exception_tracking_msg := 'Inserting values into WSM_RESULTING_JOBS_INTERFACE';
2581:
2582: IF (g_debug_level <= 1) THEN
2583: cln_debug_pub.Add(g_exception_tracking_msg, 1);
2584: END IF;
2585:
2586: INSERT
2587: INTO WSM_RESULTING_JOBS_INTERFACE
2642:
2643: g_exception_tracking_msg := '-------- Values successfully inserted into WSM_RESULTING_JOBS_INTERFACE --------';
2644:
2645: IF (g_debug_level <= 1) THEN
2646: cln_debug_pub.Add(g_exception_tracking_msg,1);
2647: END IF;
2648:
2649: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
2650:
2664: WHERE msg_id = p_hdr_rec.msg_id
2665: AND hdr_id = p_hdr_rec.hdr_id;
2666:
2667: IF (g_debug_level <= 1) THEN
2668: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
2669: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
2670: END IF;
2671:
2672: IF l_custom_valid_pass = 'FAIL' THEN
2665: AND hdr_id = p_hdr_rec.hdr_id;
2666:
2667: IF (g_debug_level <= 1) THEN
2668: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
2669: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
2670: END IF;
2671:
2672: IF l_custom_valid_pass = 'FAIL' THEN
2673:
2702:
2703: g_exception_tracking_msg := '-------- Calling WSMPLOAD.load --------';
2704:
2705: IF (g_debug_level <= 2) THEN
2706: cln_debug_pub.Add(g_exception_tracking_msg,2);
2707: cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
2708: END IF;
2709:
2710: WSMPLOAD.load(l_errbuf, l_retcode,'1', l_group_id );
2703: g_exception_tracking_msg := '-------- Calling WSMPLOAD.load --------';
2704:
2705: IF (g_debug_level <= 2) THEN
2706: cln_debug_pub.Add(g_exception_tracking_msg,2);
2707: cln_debug_pub.Add('l_group_id : ' || l_group_id, 2);
2708: END IF;
2709:
2710: WSMPLOAD.load(l_errbuf, l_retcode,'1', l_group_id );
2711:
2709:
2710: WSMPLOAD.load(l_errbuf, l_retcode,'1', l_group_id );
2711:
2712: IF (g_debug_level <= 2) THEN
2713: cln_debug_pub.Add('-------- Out of WSMPLOAD.load --------',2);
2714: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
2715: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
2716: END IF;
2717:
2710: WSMPLOAD.load(l_errbuf, l_retcode,'1', l_group_id );
2711:
2712: IF (g_debug_level <= 2) THEN
2713: cln_debug_pub.Add('-------- Out of WSMPLOAD.load --------',2);
2714: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
2715: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
2716: END IF;
2717:
2718: IF l_retcode = 0 THEN
2711:
2712: IF (g_debug_level <= 2) THEN
2713: cln_debug_pub.Add('-------- Out of WSMPLOAD.load --------',2);
2714: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
2715: cln_debug_pub.Add('l_errbuf : ' || l_errbuf, 2);
2716: END IF;
2717:
2718: IF l_retcode = 0 THEN
2719:
2738: FROM wsm_split_merge_txn_interface
2739: WHERE group_id = l_group_id))
2740: LOOP
2741: IF (g_debug_level <= 5) THEN
2742: cln_debug_pub.Add('Loop error : ' || i.message, 5);
2743: END IF;
2744:
2745: l_errloop_cnt := l_errloop_cnt + 1;
2746:
2755: FND_MESSAGE.SET_TOKEN('GRP_ID',l_group_id);
2756: l_interface_err := FND_MESSAGE.GET || l_interface_err;
2757:
2758: IF (g_debug_level <= 1) THEN
2759: cln_debug_pub.Add('l_interface_err : ' || l_interface_err, 1);
2760: END IF;
2761:
2762: UPDATE M4R_WSM_DWIP_HDR_STAGING
2763: SET status_flag ='E', error_message = l_errbuf || l_interface_err,group_id = l_group_id
2764: WHERE msg_id = p_hdr_rec.msg_id
2765: AND status_flag = 'R';
2766:
2767: IF (g_debug_level <= 1) THEN
2768: cln_debug_pub.Add('M4R_WSM_DWIP_HDR_STAGING updated', 1);
2769: END IF;
2770:
2771:
2772: ADD_MSG_COLL_HIST ( l_interface_err ,
2777: p_hdr_rec.hdr_id,
2778: p_hdr_rec.msg_id);
2779:
2780: IF (g_debug_level <= 1) THEN
2781: cln_debug_pub.Add('ADD_MSG_COLL_HIST returns', 1);
2782: END IF;
2783:
2784:
2785: wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
2784:
2785: wf_engine.SetItemAttrText('M4R7B1OI',p_item_key,'NOTIF_CODE','7B1_NOTIF2');
2786:
2787: IF (g_debug_level <= 1) THEN
2788: cln_debug_pub.Add('Item attribute NOTIF_CODE is set', 1);
2789: END IF;
2790:
2791: ELSE -- There are no errors found
2792:
2790:
2791: ELSE -- There are no errors found
2792:
2793: IF (g_debug_level <= 5) THEN
2794: cln_debug_pub.Add(g_exception_tracking_msg,5);
2795: cln_debug_pub.Add('----- No data found -----',5);
2796: END IF;
2797: l_err_flag := 'N';
2798:
2791: ELSE -- There are no errors found
2792:
2793: IF (g_debug_level <= 5) THEN
2794: cln_debug_pub.Add(g_exception_tracking_msg,5);
2795: cln_debug_pub.Add('----- No data found -----',5);
2796: END IF;
2797: l_err_flag := 'N';
2798:
2799: UPDATE M4R_WSM_DWIP_HDR_STAGING
2812: WHERE group_id = l_group_id);
2813:
2814:
2815: IF (g_debug_level <= 1) THEN
2816: cln_debug_pub.Add('l_interface_err : ' || l_interface_err, 1);
2817: END IF;
2818:
2819: UPDATE M4R_WSM_DWIP_HDR_STAGING
2820: SET status_flag ='E', error_message = l_errbuf || l_interface_err,group_id = l_group_id
2842: EXCEPTION
2843: WHEN NO_DATA_FOUND THEN
2844:
2845: IF (g_debug_level <= 5) THEN
2846: cln_debug_pub.Add(g_exception_tracking_msg,5);
2847: cln_debug_pub.Add('----- No data found -----',5);
2848: END IF;
2849:
2850: l_err_flag := 'N';
2843: WHEN NO_DATA_FOUND THEN
2844:
2845: IF (g_debug_level <= 5) THEN
2846: cln_debug_pub.Add(g_exception_tracking_msg,5);
2847: cln_debug_pub.Add('----- No data found -----',5);
2848: END IF;
2849:
2850: l_err_flag := 'N';
2851:
2861:
2862: x_resultout := 'CONTINUE';
2863:
2864: IF (g_debug_level <= 2) THEN
2865: cln_debug_pub.Add('-------- Exiting procedure WIP_LOT_TXNS --------',2);
2866: cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
2867: END IF;
2868:
2869: EXCEPTION
2862: x_resultout := 'CONTINUE';
2863:
2864: IF (g_debug_level <= 2) THEN
2865: cln_debug_pub.Add('-------- Exiting procedure WIP_LOT_TXNS --------',2);
2866: cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
2867: END IF;
2868:
2869: EXCEPTION
2870: WHEN return_code_false THEN
2869: EXCEPTION
2870: WHEN return_code_false THEN
2871:
2872: IF (g_debug_level <= 5) THEN
2873: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
2874: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2875: END IF;
2876:
2877: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
2870: WHEN return_code_false THEN
2871:
2872: IF (g_debug_level <= 5) THEN
2873: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
2874: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2875: END IF;
2876:
2877: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
2878:
2896:
2897: l_err_msg := g_exception_tracking_msg || ':' || l_interface_err;
2898:
2899: IF (g_debug_level <= 5) THEN
2900: cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
2901: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2902: END IF;
2903:
2904: ADD_MSG_COLL_HIST ( l_interface_err ,
2897: l_err_msg := g_exception_tracking_msg || ':' || l_interface_err;
2898:
2899: IF (g_debug_level <= 5) THEN
2900: cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
2901: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2902: END IF;
2903:
2904: ADD_MSG_COLL_HIST ( l_interface_err ,
2905: p_hdr_rec.transaction_type,
2919:
2920: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
2921:
2922: IF (g_debug_level <= 5) THEN
2923: cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
2924: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2925: END IF;
2926:
2927: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
2920: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
2921:
2922: IF (g_debug_level <= 5) THEN
2923: cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
2924: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
2925: END IF;
2926:
2927: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
2928:
2973:
2974: BEGIN
2975:
2976: IF (g_debug_level <= 2) THEN
2977: cln_debug_pub.Add('-------- Entering procedure INSERT_INV_REC --------',2);
2978: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2979: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2974: BEGIN
2975:
2976: IF (g_debug_level <= 2) THEN
2977: cln_debug_pub.Add('-------- Entering procedure INSERT_INV_REC --------',2);
2978: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2979: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2975:
2976: IF (g_debug_level <= 2) THEN
2977: cln_debug_pub.Add('-------- Entering procedure INSERT_INV_REC --------',2);
2978: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2979: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2976: IF (g_debug_level <= 2) THEN
2977: cln_debug_pub.Add('-------- Entering procedure INSERT_INV_REC --------',2);
2978: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2979: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2977: cln_debug_pub.Add('-------- Entering procedure INSERT_INV_REC --------',2);
2978: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2979: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2978: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
2979: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2979: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2980: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2981: cln_debug_pub.Add('p_inventory_item_id : ' || p_inventory_item_id, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2982: cln_debug_pub.Add('p_from_sub_inventory : ' || p_from_sub_inventory, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2983: cln_debug_pub.Add('p_to_sub_inventory : ' || p_to_sub_inventory, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2984: cln_debug_pub.Add('p_lot_qty : ' || p_lot_qty, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992: cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2985: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992: cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2993: cln_debug_pub.Add('p_wip_entity_name : ' || p_wip_entity_name, 2);
2986: cln_debug_pub.Add('p_lot_uom : ' || p_lot_uom, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992: cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2993: cln_debug_pub.Add('p_wip_entity_name : ' || p_wip_entity_name, 2);
2994: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
2987: cln_debug_pub.Add('p_trx_date : ' || p_trx_date, 2);
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992: cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2993: cln_debug_pub.Add('p_wip_entity_name : ' || p_wip_entity_name, 2);
2994: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
2995: END IF;
2988: cln_debug_pub.Add('p_lot_number : ' || p_lot_number, 2);
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992: cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2993: cln_debug_pub.Add('p_wip_entity_name : ' || p_wip_entity_name, 2);
2994: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
2995: END IF;
2996:
2989: cln_debug_pub.Add('p_op_seq_num : ' || p_op_seq_num, 2);
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992: cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2993: cln_debug_pub.Add('p_wip_entity_name : ' || p_wip_entity_name, 2);
2994: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
2995: END IF;
2996:
2997: g_exception_tracking_msg := 'Querying mtl_parameters for l_org_id';
2990: cln_debug_pub.Add('p_trx_bat_seq : ' || p_trx_bat_seq, 2);
2991: cln_debug_pub.Add('p_trx_if_id : ' || p_trx_if_id, 2);
2992: cln_debug_pub.Add('p_parent_id : ' || p_parent_id, 2);
2993: cln_debug_pub.Add('p_wip_entity_name : ' || p_wip_entity_name, 2);
2994: cln_debug_pub.Add('p_wip_entity_id : ' || p_wip_entity_id, 2);
2995: END IF;
2996:
2997: g_exception_tracking_msg := 'Querying mtl_parameters for l_org_id';
2998:
3001: FROM mtl_parameters
3002: WHERE organization_code = p_transfer_to_org;
3003:
3004: IF (g_debug_level <= 1) THEN
3005: cln_debug_pub.Add('transfer_to org_id : '|| l_org_id,1);
3006: END IF;
3007:
3008: g_exception_tracking_msg := 'Inserting values into MTL_TRANSACTIONS_INTERFACE';
3009:
3077: 2,
3078: 2);
3079:
3080: IF (g_debug_level <= 1) THEN
3081: cln_debug_pub.Add('-------- Values successfully inserted into MTL_TRANSACTIONS_INTERFACE --------',1);
3082: END IF;
3083:
3084: g_exception_tracking_msg := 'Inserting values into MTL_TRANSACTION_LOTS_INTERFACE';
3085:
3099: p_lot_number,
3100: p_lot_qty);
3101:
3102: IF (g_debug_level <= 2) THEN
3103: cln_debug_pub.Add('-------- Values successfully inserted into MTL_TRANSACTION_LOTS_INTERFACE --------',2);
3104: END IF;
3105:
3106: x_resultout := 'S';
3107:
3105:
3106: x_resultout := 'S';
3107:
3108: IF (g_debug_level <= 2) THEN
3109: cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3110: cln_debug_pub.Add('-------- Exiting procedure INSERT_INV_REC --------',2);
3111: END IF;
3112:
3113: EXCEPTION
3106: x_resultout := 'S';
3107:
3108: IF (g_debug_level <= 2) THEN
3109: cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3110: cln_debug_pub.Add('-------- Exiting procedure INSERT_INV_REC --------',2);
3111: END IF;
3112:
3113: EXCEPTION
3114: WHEN OTHERS THEN
3118:
3119: x_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
3120:
3121: IF (g_debug_level <= 5) THEN
3122: cln_debug_pub.Add('------- Exception in procedure INSERT_INV_REC --------',5);
3123: cln_debug_pub.Add('x_err_msg : '|| x_err_msg, 5);
3124: cln_debug_pub.Add('x_resultout : '|| x_resultout,5);
3125: END IF;
3126:
3119: x_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
3120:
3121: IF (g_debug_level <= 5) THEN
3122: cln_debug_pub.Add('------- Exception in procedure INSERT_INV_REC --------',5);
3123: cln_debug_pub.Add('x_err_msg : '|| x_err_msg, 5);
3124: cln_debug_pub.Add('x_resultout : '|| x_resultout,5);
3125: END IF;
3126:
3127:
3120:
3121: IF (g_debug_level <= 5) THEN
3122: cln_debug_pub.Add('------- Exception in procedure INSERT_INV_REC --------',5);
3123: cln_debug_pub.Add('x_err_msg : '|| x_err_msg, 5);
3124: cln_debug_pub.Add('x_resultout : '|| x_resultout,5);
3125: END IF;
3126:
3127:
3128: END INSERT_INV_REC;
3214:
3215: BEGIN
3216:
3217: IF (g_debug_level <= 2) THEN
3218: cln_debug_pub.Add('-------- Entering procedure INV_LOT_TXNS --------',2);
3219: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
3220: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
3221: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
3222: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
3215: BEGIN
3216:
3217: IF (g_debug_level <= 2) THEN
3218: cln_debug_pub.Add('-------- Entering procedure INV_LOT_TXNS --------',2);
3219: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
3220: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
3221: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
3222: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
3223: END IF;
3216:
3217: IF (g_debug_level <= 2) THEN
3218: cln_debug_pub.Add('-------- Entering procedure INV_LOT_TXNS --------',2);
3219: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
3220: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
3221: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
3222: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
3223: END IF;
3224:
3217: IF (g_debug_level <= 2) THEN
3218: cln_debug_pub.Add('-------- Entering procedure INV_LOT_TXNS --------',2);
3219: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
3220: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
3221: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
3222: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
3223: END IF;
3224:
3225: l_trx_bat_seq :=1;
3218: cln_debug_pub.Add('-------- Entering procedure INV_LOT_TXNS --------',2);
3219: cln_debug_pub.Add('p_process_type : ' || p_process_type, 2);
3220: cln_debug_pub.Add('p_user_id : ' || p_user_id, 2);
3221: cln_debug_pub.Add('p_org_id : ' || p_org_id, 2);
3222: cln_debug_pub.Add('p_item_key : ' || p_item_key, 2);
3223: END IF;
3224:
3225: l_trx_bat_seq :=1;
3226:
3234: INTO l_trx_hdr_id
3235: FROM DUAL;
3236:
3237: IF (g_debug_level <= 1) THEN
3238: cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3239: cln_debug_pub.Add('l_trx_hdr_id : ' || l_trx_hdr_id, 1);
3240: END IF;
3241:
3242: IF p_process_type = 'INV_SPLIT' THEN
3235: FROM DUAL;
3236:
3237: IF (g_debug_level <= 1) THEN
3238: cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3239: cln_debug_pub.Add('l_trx_hdr_id : ' || l_trx_hdr_id, 1);
3240: END IF;
3241:
3242: IF p_process_type = 'INV_SPLIT' THEN
3243:
3312:
3313: g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
3314:
3315: IF (g_debug_level <= 1) THEN
3316: cln_debug_pub.Add(g_exception_tracking_msg, 1);
3317: END IF;
3318:
3319: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3320: l_interface_err :=FND_MESSAGE.GET;
3340:
3341: l_trx_bat_seq := l_trx_bat_seq +1;
3342:
3343: IF (g_debug_level <= 1) THEN
3344: cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3345: cln_debug_pub.Add('l_trx_bat_seq : ' || l_trx_bat_seq, 1);
3346: END IF;
3347:
3348: GET_INV_ITEM_DETAILS( l_rec.starting_lot_item_code,
3341: l_trx_bat_seq := l_trx_bat_seq +1;
3342:
3343: IF (g_debug_level <= 1) THEN
3344: cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3345: cln_debug_pub.Add('l_trx_bat_seq : ' || l_trx_bat_seq, 1);
3346: END IF;
3347:
3348: GET_INV_ITEM_DETAILS( l_rec.starting_lot_item_code,
3349: l_rec.primary_item_code,
3474:
3475: g_exception_tracking_msg := 'This record has errors. So updating status of corresponding records to E';
3476:
3477: IF (g_debug_level <= 1) THEN
3478: cln_debug_pub.Add(g_exception_tracking_msg, 1);
3479: END IF;
3480:
3481: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_OTH_TXN_FAIL');
3482: l_interface_err :=FND_MESSAGE.GET;
3503: INTO l_trx_if_id
3504: FROM DUAL;
3505:
3506: IF (g_debug_level <= 1) THEN
3507: cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3508: cln_debug_pub.Add('l_trx_bat_seq : ' || l_trx_bat_seq, 1);
3509: cln_debug_pub.Add('l_lot_qty : ' || l_lot_qty, 1);
3510: END IF;
3511:
3504: FROM DUAL;
3505:
3506: IF (g_debug_level <= 1) THEN
3507: cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3508: cln_debug_pub.Add('l_trx_bat_seq : ' || l_trx_bat_seq, 1);
3509: cln_debug_pub.Add('l_lot_qty : ' || l_lot_qty, 1);
3510: END IF;
3511:
3512: GET_INV_ITEM_DETAILS( l_rec.starting_lot_item_code,
3505:
3506: IF (g_debug_level <= 1) THEN
3507: cln_debug_pub.Add('l_trx_if_id : ' || l_trx_if_id, 1);
3508: cln_debug_pub.Add('l_trx_bat_seq : ' || l_trx_bat_seq, 1);
3509: cln_debug_pub.Add('l_lot_qty : ' || l_lot_qty, 1);
3510: END IF;
3511:
3512: GET_INV_ITEM_DETAILS( l_rec.starting_lot_item_code,
3513: l_rec.primary_item_code,
3658:
3659: l_parent_id := l_trx_if_id;
3660:
3661: IF (g_debug_level <= 1) THEN
3662: cln_debug_pub.Add('l_parent_id : ' || l_parent_id, 1);
3663: END IF;
3664:
3665: l_lot_qty := -p_hdr_rec.prev_lot_qty;
3666:
3802: WHERE msg_id = p_hdr_rec.msg_id
3803: AND hdr_id = p_hdr_rec.hdr_id;
3804:
3805: IF (g_debug_level <= 1) THEN
3806: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
3807: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
3808: END IF;
3809:
3810: x_resultout := 'CONTINUE';
3803: AND hdr_id = p_hdr_rec.hdr_id;
3804:
3805: IF (g_debug_level <= 1) THEN
3806: cln_debug_pub.Add('l_custom_valid_pass : ' || l_custom_valid_pass, 1);
3807: cln_debug_pub.Add('l_custom_valid_err_msg : ' || l_custom_valid_err_msg, 1);
3808: END IF;
3809:
3810: x_resultout := 'CONTINUE';
3811:
3832:
3833: x_resultout := 'FAILED';
3834:
3835: IF (g_debug_level <= 2) THEN
3836: cln_debug_pub.Add('---- Out of ADD_MSG_COLL_HIST procedure ------',2);
3837: cln_debug_pub.Add('---- ROLLBACK Done ------',2);
3838: cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3839: END IF;
3840:
3833: x_resultout := 'FAILED';
3834:
3835: IF (g_debug_level <= 2) THEN
3836: cln_debug_pub.Add('---- Out of ADD_MSG_COLL_HIST procedure ------',2);
3837: cln_debug_pub.Add('---- ROLLBACK Done ------',2);
3838: cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3839: END IF;
3840:
3841: ELSE
3834:
3835: IF (g_debug_level <= 2) THEN
3836: cln_debug_pub.Add('---- Out of ADD_MSG_COLL_HIST procedure ------',2);
3837: cln_debug_pub.Add('---- ROLLBACK Done ------',2);
3838: cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3839: END IF;
3840:
3841: ELSE
3842: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_trx_if_id);
3841: ELSE
3842: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_trx_if_id);
3843:
3844: IF (g_debug_level <= 2) THEN
3845: cln_debug_pub.Add('-------- Calling of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3846: cln_debug_pub.Add('l_header_id : ' || l_trx_hdr_id, 2);
3847: END IF;
3848:
3849: l_return := INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS(107,NULL,fnd_api.g_false,NULL,l_retcode,
3842: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_trx_if_id);
3843:
3844: IF (g_debug_level <= 2) THEN
3845: cln_debug_pub.Add('-------- Calling of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3846: cln_debug_pub.Add('l_header_id : ' || l_trx_hdr_id, 2);
3847: END IF;
3848:
3849: l_return := INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS(107,NULL,fnd_api.g_false,NULL,l_retcode,
3850: l_msg_count,l_msg_data,l_trans_count,NULL,l_trx_hdr_id);
3849: l_return := INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS(107,NULL,fnd_api.g_false,NULL,l_retcode,
3850: l_msg_count,l_msg_data,l_trans_count,NULL,l_trx_hdr_id);
3851:
3852: IF (g_debug_level <= 2) THEN
3853: cln_debug_pub.Add('-------- Out of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3854: cln_debug_pub.Add('l_retcode : ' || l_return , 2);
3855: cln_debug_pub.Add('l_msg_data : ' || l_msg_data , 2);
3856: cln_debug_pub.Add('l_msg_count : ' || l_msg_count , 2);
3857: cln_debug_pub.Add('l_trans_count : ' || l_trans_count , 2);
3850: l_msg_count,l_msg_data,l_trans_count,NULL,l_trx_hdr_id);
3851:
3852: IF (g_debug_level <= 2) THEN
3853: cln_debug_pub.Add('-------- Out of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3854: cln_debug_pub.Add('l_retcode : ' || l_return , 2);
3855: cln_debug_pub.Add('l_msg_data : ' || l_msg_data , 2);
3856: cln_debug_pub.Add('l_msg_count : ' || l_msg_count , 2);
3857: cln_debug_pub.Add('l_trans_count : ' || l_trans_count , 2);
3858: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
3851:
3852: IF (g_debug_level <= 2) THEN
3853: cln_debug_pub.Add('-------- Out of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3854: cln_debug_pub.Add('l_retcode : ' || l_return , 2);
3855: cln_debug_pub.Add('l_msg_data : ' || l_msg_data , 2);
3856: cln_debug_pub.Add('l_msg_count : ' || l_msg_count , 2);
3857: cln_debug_pub.Add('l_trans_count : ' || l_trans_count , 2);
3858: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
3859: END IF;
3852: IF (g_debug_level <= 2) THEN
3853: cln_debug_pub.Add('-------- Out of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3854: cln_debug_pub.Add('l_retcode : ' || l_return , 2);
3855: cln_debug_pub.Add('l_msg_data : ' || l_msg_data , 2);
3856: cln_debug_pub.Add('l_msg_count : ' || l_msg_count , 2);
3857: cln_debug_pub.Add('l_trans_count : ' || l_trans_count , 2);
3858: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
3859: END IF;
3860:
3853: cln_debug_pub.Add('-------- Out of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTION --------',2);
3854: cln_debug_pub.Add('l_retcode : ' || l_return , 2);
3855: cln_debug_pub.Add('l_msg_data : ' || l_msg_data , 2);
3856: cln_debug_pub.Add('l_msg_count : ' || l_msg_count , 2);
3857: cln_debug_pub.Add('l_trans_count : ' || l_trans_count , 2);
3858: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
3859: END IF;
3860:
3861: BEGIN
3854: cln_debug_pub.Add('l_retcode : ' || l_return , 2);
3855: cln_debug_pub.Add('l_msg_data : ' || l_msg_data , 2);
3856: cln_debug_pub.Add('l_msg_count : ' || l_msg_count , 2);
3857: cln_debug_pub.Add('l_trans_count : ' || l_trans_count , 2);
3858: cln_debug_pub.Add('l_retcode : ' || l_retcode, 2);
3859: END IF;
3860:
3861: BEGIN
3862:
3867: FROM MTL_TRANSACTIONS_INTERFACE
3868: WHERE TRANSACTION_INTERFACE_ID = l_parent_id;
3869:
3870: IF (g_debug_level <= 1) THEN
3871: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
3872: cln_debug_pub.Add('l_err_code : ' || l_err_code, 1);
3873: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
3874: cln_debug_pub.Add('-------- Updating the status_flag ------',5);
3875: END IF;
3868: WHERE TRANSACTION_INTERFACE_ID = l_parent_id;
3869:
3870: IF (g_debug_level <= 1) THEN
3871: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
3872: cln_debug_pub.Add('l_err_code : ' || l_err_code, 1);
3873: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
3874: cln_debug_pub.Add('-------- Updating the status_flag ------',5);
3875: END IF;
3876:
3869:
3870: IF (g_debug_level <= 1) THEN
3871: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
3872: cln_debug_pub.Add('l_err_code : ' || l_err_code, 1);
3873: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
3874: cln_debug_pub.Add('-------- Updating the status_flag ------',5);
3875: END IF;
3876:
3877: l_err_msg := l_err_code||'.'||l_err_msg;
3870: IF (g_debug_level <= 1) THEN
3871: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
3872: cln_debug_pub.Add('l_err_code : ' || l_err_code, 1);
3873: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
3874: cln_debug_pub.Add('-------- Updating the status_flag ------',5);
3875: END IF;
3876:
3877: l_err_msg := l_err_code||'.'||l_err_msg;
3878:
3916:
3917: l_err_msg := 'Exception when '|| g_exception_tracking_msg || g_error_code || g_errmsg ;
3918:
3919: IF (g_debug_level <= 5) THEN
3920: cln_debug_pub.Add('Exception : '|| l_err_msg,5);
3921: END IF;
3922:
3923: IF (g_debug_level <= 1) THEN
3924: cln_debug_pub.Add('---- Updating the status_flag ------',5);
3920: cln_debug_pub.Add('Exception : '|| l_err_msg,5);
3921: END IF;
3922:
3923: IF (g_debug_level <= 1) THEN
3924: cln_debug_pub.Add('---- Updating the status_flag ------',5);
3925: END IF;
3926:
3927: UPDATE M4R_WSM_DWIP_HDR_STAGING
3928: SET status_flag = 'E',error_message = l_err_msg,group_id = l_trx_hdr_id
3966:
3967: END IF; -- l_retcode = 'S'
3968:
3969: IF (g_debug_level <= 2) THEN
3970: cln_debug_pub.Add('-------- Exiting procedure INV_LOT_TXNS --------',2);
3971: cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3972: END IF;
3973:
3974: EXCEPTION
3967: END IF; -- l_retcode = 'S'
3968:
3969: IF (g_debug_level <= 2) THEN
3970: cln_debug_pub.Add('-------- Exiting procedure INV_LOT_TXNS --------',2);
3971: cln_debug_pub.Add('x_resultout : '|| x_resultout,2);
3972: END IF;
3973:
3974: EXCEPTION
3975:
3975:
3976: WHEN return_code_false THEN
3977:
3978: IF (g_debug_level <= 5) THEN
3979: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
3980: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
3981: END IF;
3982:
3983: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
3976: WHEN return_code_false THEN
3977:
3978: IF (g_debug_level <= 5) THEN
3979: cln_debug_pub.Add('------- Exception in procedure JOB_CREATE_OR_STATUS --------',5);
3980: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
3981: END IF;
3982:
3983: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
3984:
4002:
4003: l_err_msg := g_exception_tracking_msg || ':' || l_interface_err;
4004:
4005: IF (g_debug_level <= 5) THEN
4006: cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
4007: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
4008: END IF;
4009:
4010: ADD_MSG_COLL_HIST ( l_interface_err ,
4003: l_err_msg := g_exception_tracking_msg || ':' || l_interface_err;
4004:
4005: IF (g_debug_level <= 5) THEN
4006: cln_debug_pub.Add('------- Exception in procedure WIP_LOT_TXNS --------',5);
4007: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
4008: END IF;
4009:
4010: ADD_MSG_COLL_HIST ( l_interface_err ,
4011: p_hdr_rec.transaction_type,
4026:
4027: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
4028:
4029: IF (g_debug_level <= 5) THEN
4030: cln_debug_pub.Add('------- Exception in procedure INV_LOT_TXNS --------',5);
4031: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 5);
4032: cln_debug_pub.Add('x_resultout : ' || x_resultout,5);
4033: END IF;
4034:
4027: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
4028:
4029: IF (g_debug_level <= 5) THEN
4030: cln_debug_pub.Add('------- Exception in procedure INV_LOT_TXNS --------',5);
4031: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 5);
4032: cln_debug_pub.Add('x_resultout : ' || x_resultout,5);
4033: END IF;
4034:
4035: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_trx_hdr_id);
4028:
4029: IF (g_debug_level <= 5) THEN
4030: cln_debug_pub.Add('------- Exception in procedure INV_LOT_TXNS --------',5);
4031: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 5);
4032: cln_debug_pub.Add('x_resultout : ' || x_resultout,5);
4033: END IF;
4034:
4035: UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_trx_hdr_id);
4036:
4094:
4095:
4096: BEGIN
4097: IF (g_debug_level <= 2) THEN
4098: cln_debug_pub.Add('--------ENTERING M4R_7B1_WSM_IN.PROCESS_STAGING procedure ------------', 2);
4099: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4100: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4101: cln_debug_pub.Add('actid : ' || p_actid, 2);
4102: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4095:
4096: BEGIN
4097: IF (g_debug_level <= 2) THEN
4098: cln_debug_pub.Add('--------ENTERING M4R_7B1_WSM_IN.PROCESS_STAGING procedure ------------', 2);
4099: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4100: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4101: cln_debug_pub.Add('actid : ' || p_actid, 2);
4102: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4103: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4096: BEGIN
4097: IF (g_debug_level <= 2) THEN
4098: cln_debug_pub.Add('--------ENTERING M4R_7B1_WSM_IN.PROCESS_STAGING procedure ------------', 2);
4099: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4100: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4101: cln_debug_pub.Add('actid : ' || p_actid, 2);
4102: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4103: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4104: END IF;
4097: IF (g_debug_level <= 2) THEN
4098: cln_debug_pub.Add('--------ENTERING M4R_7B1_WSM_IN.PROCESS_STAGING procedure ------------', 2);
4099: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4100: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4101: cln_debug_pub.Add('actid : ' || p_actid, 2);
4102: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4103: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4104: END IF;
4105:
4098: cln_debug_pub.Add('--------ENTERING M4R_7B1_WSM_IN.PROCESS_STAGING procedure ------------', 2);
4099: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4100: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4101: cln_debug_pub.Add('actid : ' || p_actid, 2);
4102: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4103: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4104: END IF;
4105:
4106: IF p_funcmode <> 'RUN' THEN
4099: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4100: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4101: cln_debug_pub.Add('actid : ' || p_actid, 2);
4102: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4103: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4104: END IF;
4105:
4106: IF p_funcmode <> 'RUN' THEN
4107: RETURN;
4108: END IF;
4109:
4110: l_msg_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PARAMETER2');
4111: IF (g_debug_level <= 1) THEN
4112: cln_debug_pub.Add('l_msg_id : ' || l_msg_id, 1);
4113: END IF;
4114:
4115: g_exception_tracking_msg := 'Querying M4R_WSM_DWIP_HDR_STAGING for the valid record';
4116:
4134:
4135: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
4136:
4137: IF (g_debug_level <= 5) THEN
4138: cln_debug_pub.Add('------- Exception in procedure PROCESS_STAGING --------',5);
4139: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
4140: END IF;
4141: END;
4142:
4135: l_err_msg := g_exception_tracking_msg ||':'||g_error_code||':'||g_errmsg;
4136:
4137: IF (g_debug_level <= 5) THEN
4138: cln_debug_pub.Add('------- Exception in procedure PROCESS_STAGING --------',5);
4139: cln_debug_pub.Add('l_err_msg : '|| l_err_msg, 5);
4140: END IF;
4141: END;
4142:
4143: IF l_row_exists = 'N' THEN
4148:
4149: g_exception_tracking_msg := 'Getting the global values';
4150:
4151: IF (g_debug_level <= 1) THEN
4152: cln_debug_pub.Add('------- Getting the global values -------' , 1);
4153: END IF;
4154:
4155: l_user_id := fnd_global.user_id();
4156:
4154:
4155: l_user_id := fnd_global.user_id();
4156:
4157: IF (g_debug_level <= 1) THEN
4158: cln_debug_pub.Add('l_user_id : ' || l_user_id, 1);
4159: END IF;
4160:
4161: wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'HEADER_ID',l_hdr_rec.hdr_id);
4162:
4169: FROM ORG_ORGANIZATION_DEFINITIONS
4170: WHERE organization_code = l_hdr_rec.TRANSFER_FROM_ORG;
4171:
4172: IF (g_debug_level <= 1) THEN
4173: cln_debug_pub.Add('l_org_id : ' || l_org_id, 1);
4174: END IF;
4175: EXCEPTION
4176: WHEN OTHERS THEN
4177: RAISE INVALID_ORG_EXCEPTION;
4195:
4196: FOR l_qty_rec IN M4R_7B1_WSM_LOT_QTY_STAGING_C1(l_hdr_rec.hdr_id) LOOP
4197:
4198: IF (g_debug_level <= 1) THEN
4199: cln_debug_pub.Add('----- Inside M4R_7B1_WSM_LOT_QTY_STAGING_C1 --------------------- ' , 1);
4200: cln_debug_pub.Add('l_qty_rec.lot_qty_id : ' || l_qty_rec.lot_qty_id, 1);
4201: cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202: cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203: cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4196: FOR l_qty_rec IN M4R_7B1_WSM_LOT_QTY_STAGING_C1(l_hdr_rec.hdr_id) LOOP
4197:
4198: IF (g_debug_level <= 1) THEN
4199: cln_debug_pub.Add('----- Inside M4R_7B1_WSM_LOT_QTY_STAGING_C1 --------------------- ' , 1);
4200: cln_debug_pub.Add('l_qty_rec.lot_qty_id : ' || l_qty_rec.lot_qty_id, 1);
4201: cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202: cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203: cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4204: cln_debug_pub.Add('l_qty_rec.prev_operation_seq_num : ' || l_hdr_rec.prev_operation_seq_num, 1);
4197:
4198: IF (g_debug_level <= 1) THEN
4199: cln_debug_pub.Add('----- Inside M4R_7B1_WSM_LOT_QTY_STAGING_C1 --------------------- ' , 1);
4200: cln_debug_pub.Add('l_qty_rec.lot_qty_id : ' || l_qty_rec.lot_qty_id, 1);
4201: cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202: cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203: cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4204: cln_debug_pub.Add('l_qty_rec.prev_operation_seq_num : ' || l_hdr_rec.prev_operation_seq_num, 1);
4205: cln_debug_pub.Add('l_qty_rec.operation_seq_num : ' || l_hdr_rec.operation_seq_num, 1);
4198: IF (g_debug_level <= 1) THEN
4199: cln_debug_pub.Add('----- Inside M4R_7B1_WSM_LOT_QTY_STAGING_C1 --------------------- ' , 1);
4200: cln_debug_pub.Add('l_qty_rec.lot_qty_id : ' || l_qty_rec.lot_qty_id, 1);
4201: cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202: cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203: cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4204: cln_debug_pub.Add('l_qty_rec.prev_operation_seq_num : ' || l_hdr_rec.prev_operation_seq_num, 1);
4205: cln_debug_pub.Add('l_qty_rec.operation_seq_num : ' || l_hdr_rec.operation_seq_num, 1);
4206: END IF;
4199: cln_debug_pub.Add('----- Inside M4R_7B1_WSM_LOT_QTY_STAGING_C1 --------------------- ' , 1);
4200: cln_debug_pub.Add('l_qty_rec.lot_qty_id : ' || l_qty_rec.lot_qty_id, 1);
4201: cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202: cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203: cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4204: cln_debug_pub.Add('l_qty_rec.prev_operation_seq_num : ' || l_hdr_rec.prev_operation_seq_num, 1);
4205: cln_debug_pub.Add('l_qty_rec.operation_seq_num : ' || l_hdr_rec.operation_seq_num, 1);
4206: END IF;
4207:
4200: cln_debug_pub.Add('l_qty_rec.lot_qty_id : ' || l_qty_rec.lot_qty_id, 1);
4201: cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202: cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203: cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4204: cln_debug_pub.Add('l_qty_rec.prev_operation_seq_num : ' || l_hdr_rec.prev_operation_seq_num, 1);
4205: cln_debug_pub.Add('l_qty_rec.operation_seq_num : ' || l_hdr_rec.operation_seq_num, 1);
4206: END IF;
4207:
4208: DETERMINE_PROCESS_TYPE( l_hdr_rec.transaction_type,
4201: cln_debug_pub.Add('----- Calling DETERMINE_PROCESS_TYPE with parameters ------------', 1);
4202: cln_debug_pub.Add('l_hdr_rec.transaction_type : ' || l_hdr_rec.transaction_type, 1);
4203: cln_debug_pub.Add('l_qty_rec.lot_classification_code : ' || l_qty_rec.lot_classification_code, 1);
4204: cln_debug_pub.Add('l_qty_rec.prev_operation_seq_num : ' || l_hdr_rec.prev_operation_seq_num, 1);
4205: cln_debug_pub.Add('l_qty_rec.operation_seq_num : ' || l_hdr_rec.operation_seq_num, 1);
4206: END IF;
4207:
4208: DETERMINE_PROCESS_TYPE( l_hdr_rec.transaction_type,
4209: l_qty_rec.lot_classification_code,
4303:
4304: END IF; -- l_row_exists = 'N'
4305:
4306: IF (g_debug_level <= 2) THEN
4307: cln_debug_pub.Add('-------- Exiting procedure PROCESS_STAGING ------ ' , 2);
4308: cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
4309: END IF;
4310:
4311: EXCEPTION
4304: END IF; -- l_row_exists = 'N'
4305:
4306: IF (g_debug_level <= 2) THEN
4307: cln_debug_pub.Add('-------- Exiting procedure PROCESS_STAGING ------ ' , 2);
4308: cln_debug_pub.Add('x_resultout : '|| x_resultout, 2);
4309: END IF;
4310:
4311: EXCEPTION
4312: WHEN INVALID_ORG_EXCEPTION THEN -- -- Bug 4727381, Issue c : Exception was not captured if Transfer From ORG ID was wrong.
4311: EXCEPTION
4312: WHEN INVALID_ORG_EXCEPTION THEN -- -- Bug 4727381, Issue c : Exception was not captured if Transfer From ORG ID was wrong.
4313:
4314: IF (g_debug_level <= 5) THEN
4315: cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4316: cln_debug_pub.Add('Invalid Transfer From ORG ID', 5);
4317: END IF;
4318:
4319: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_INVALID_FRM_ORG');
4312: WHEN INVALID_ORG_EXCEPTION THEN -- -- Bug 4727381, Issue c : Exception was not captured if Transfer From ORG ID was wrong.
4313:
4314: IF (g_debug_level <= 5) THEN
4315: cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4316: cln_debug_pub.Add('Invalid Transfer From ORG ID', 5);
4317: END IF;
4318:
4319: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_INVALID_FRM_ORG');
4320: FND_MESSAGE.SET_TOKEN('ORG_ID',l_hdr_rec.TRANSFER_FROM_ORG);
4341:
4342: x_resultout := 'FAILED';
4343:
4344: IF (g_debug_level <= 5) THEN
4345: cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4346: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4347: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4348: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
4349: END IF;
4342: x_resultout := 'FAILED';
4343:
4344: IF (g_debug_level <= 5) THEN
4345: cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4346: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4347: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4348: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
4349: END IF;
4350:
4343:
4344: IF (g_debug_level <= 5) THEN
4345: cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4346: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4347: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4348: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
4349: END IF;
4350:
4351: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
4344: IF (g_debug_level <= 5) THEN
4345: cln_debug_pub.Add('----Exception in procedure PROCESS_STAGING------',5);
4346: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4347: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4348: cln_debug_pub.Add('x_resultout : '|| x_resultout, 5);
4349: END IF;
4350:
4351: FND_MESSAGE.SET_NAME('CLN','M4R_7B1_OSFM_UNEXPECTED_ERR');
4352: FND_MESSAGE.SET_TOKEN('HDR_ID', l_hdr_rec.hdr_id);
4378: l_msg_id NUMBER;
4379:
4380: BEGIN
4381: IF (g_debug_level <= 2) THEN
4382: cln_debug_pub.Add('--------ENTERING CHECK_VALID_RECORDS ------------', 2);
4383: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4384: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4385: cln_debug_pub.Add('actid : ' || p_actid, 2);
4386: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4379:
4380: BEGIN
4381: IF (g_debug_level <= 2) THEN
4382: cln_debug_pub.Add('--------ENTERING CHECK_VALID_RECORDS ------------', 2);
4383: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4384: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4385: cln_debug_pub.Add('actid : ' || p_actid, 2);
4386: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4387: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4380: BEGIN
4381: IF (g_debug_level <= 2) THEN
4382: cln_debug_pub.Add('--------ENTERING CHECK_VALID_RECORDS ------------', 2);
4383: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4384: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4385: cln_debug_pub.Add('actid : ' || p_actid, 2);
4386: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4387: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4388: END IF;
4381: IF (g_debug_level <= 2) THEN
4382: cln_debug_pub.Add('--------ENTERING CHECK_VALID_RECORDS ------------', 2);
4383: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4384: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4385: cln_debug_pub.Add('actid : ' || p_actid, 2);
4386: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4387: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4388: END IF;
4389:
4382: cln_debug_pub.Add('--------ENTERING CHECK_VALID_RECORDS ------------', 2);
4383: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4384: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4385: cln_debug_pub.Add('actid : ' || p_actid, 2);
4386: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4387: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4388: END IF;
4389:
4390: l_msg_id := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'PARAMETER2');
4383: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4384: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4385: cln_debug_pub.Add('actid : ' || p_actid, 2);
4386: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4387: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4388: END IF;
4389:
4390: l_msg_id := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'PARAMETER2');
4391: IF (g_debug_level <= 1) THEN
4388: END IF;
4389:
4390: l_msg_id := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'PARAMETER2');
4391: IF (g_debug_level <= 1) THEN
4392: cln_debug_pub.Add('l_msg_id : ' || l_msg_id, 1);
4393: END IF;
4394:
4395: g_exception_tracking_msg := 'Querying M4R_WSM_DWIP_HDR_STAGING for valid records';
4396:
4409: WHERE MSG_ID = l_msg_id
4410: AND status_flag = 'V';
4411:
4412: IF (g_debug_level <= 2) THEN
4413: cln_debug_pub.Add('l_count_valid_rows : '|| l_count_valid_rows , 2);
4414: cln_debug_pub.Add('---- Exiting procedure CHECK_VALID_RECORDS ------ ' , 2);
4415: END IF;*/
4416:
4417: x_resultout := 'VALID_ROWS_EXIST';
4410: AND status_flag = 'V';
4411:
4412: IF (g_debug_level <= 2) THEN
4413: cln_debug_pub.Add('l_count_valid_rows : '|| l_count_valid_rows , 2);
4414: cln_debug_pub.Add('---- Exiting procedure CHECK_VALID_RECORDS ------ ' , 2);
4415: END IF;*/
4416:
4417: x_resultout := 'VALID_ROWS_EXIST';
4418:
4423:
4424: x_resultout := 'NO_VALID_ROWS';
4425:
4426: IF (g_debug_level <= 1) THEN
4427: cln_debug_pub.Add('----Exception in procedure CHECK_VALID_RECORDS ------',5);
4428: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4429: END IF;
4430:
4431: END CHECK_VALID_RECORDS;
4424: x_resultout := 'NO_VALID_ROWS';
4425:
4426: IF (g_debug_level <= 1) THEN
4427: cln_debug_pub.Add('----Exception in procedure CHECK_VALID_RECORDS ------',5);
4428: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4429: END IF;
4430:
4431: END CHECK_VALID_RECORDS;
4432:
4450:
4451: BEGIN
4452:
4453: IF (g_debug_level <= 2) THEN
4454: cln_debug_pub.Add('--------ENTERING CHECK_CP_IMPORT_STATUS ------------', 2);
4455: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4456: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4457: cln_debug_pub.Add('actid : ' || p_actid, 2);
4458: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4451: BEGIN
4452:
4453: IF (g_debug_level <= 2) THEN
4454: cln_debug_pub.Add('--------ENTERING CHECK_CP_IMPORT_STATUS ------------', 2);
4455: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4456: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4457: cln_debug_pub.Add('actid : ' || p_actid, 2);
4458: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4459: END IF;
4452:
4453: IF (g_debug_level <= 2) THEN
4454: cln_debug_pub.Add('--------ENTERING CHECK_CP_IMPORT_STATUS ------------', 2);
4455: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4456: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4457: cln_debug_pub.Add('actid : ' || p_actid, 2);
4458: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4459: END IF;
4460:
4453: IF (g_debug_level <= 2) THEN
4454: cln_debug_pub.Add('--------ENTERING CHECK_CP_IMPORT_STATUS ------------', 2);
4455: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4456: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4457: cln_debug_pub.Add('actid : ' || p_actid, 2);
4458: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4459: END IF;
4460:
4461: IF p_funcmode <> 'RUN' THEN
4454: cln_debug_pub.Add('--------ENTERING CHECK_CP_IMPORT_STATUS ------------', 2);
4455: cln_debug_pub.Add('itemtype : ' || p_itemtype, 2);
4456: cln_debug_pub.Add('itemkey : ' || p_itemkey, 2);
4457: cln_debug_pub.Add('actid : ' || p_actid, 2);
4458: cln_debug_pub.Add('funcmode : ' || p_funcmode, 2);
4459: END IF;
4460:
4461: IF p_funcmode <> 'RUN' THEN
4462: RETURN;
4463: END IF;
4464:
4465: l_msg_id := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'PARAMETER2');
4466: IF (g_debug_level <= 1) THEN
4467: cln_debug_pub.Add('l_msg_id : ' || l_msg_id, 1);
4468: END IF;
4469:
4470: l_exception_flag := 'N';
4471: l_waiting_rows := 0 ;
4489: l_waiting_rows := 0;
4490: END;
4491:
4492: IF (g_debug_level <= 1) THEN
4493: cln_debug_pub.Add('l_rec.hdr_id : ' || l_rec.hdr_id, 1);
4494: cln_debug_pub.Add('l_waiting_rows : ' || l_waiting_rows, 1);
4495: END IF;
4496:
4497: IF l_waiting_rows = 1 THEN
4490: END;
4491:
4492: IF (g_debug_level <= 1) THEN
4493: cln_debug_pub.Add('l_rec.hdr_id : ' || l_rec.hdr_id, 1);
4494: cln_debug_pub.Add('l_waiting_rows : ' || l_waiting_rows, 1);
4495: END IF;
4496:
4497: IF l_waiting_rows = 1 THEN
4498:
4505: FROM WSM_LOT_JOB_INTERFACE
4506: WHERE header_id = l_rec.group_id;
4507:
4508: IF (g_debug_level <= 1) THEN
4509: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
4510: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
4511: END IF;
4512:
4513: EXCEPTION
4506: WHERE header_id = l_rec.group_id;
4507:
4508: IF (g_debug_level <= 1) THEN
4509: cln_debug_pub.Add('l_interface_status : ' || l_interface_status, 1);
4510: cln_debug_pub.Add('l_err_msg : ' || l_err_msg, 1);
4511: END IF;
4512:
4513: EXCEPTION
4514: WHEN OTHERS THEN
4518: l_err_msg := 'Exception when '|| g_exception_tracking_msg || g_error_code || g_errmsg ;
4519: l_exception_flag := 'Y';
4520:
4521: IF (g_debug_level <= 5) THEN
4522: cln_debug_pub.Add('Exception : '|| l_err_msg,5);
4523: END IF;
4524:
4525: IF (g_debug_level <= 1) THEN
4526: cln_debug_pub.Add('---- Updating the status_flag ------',5);
4522: cln_debug_pub.Add('Exception : '|| l_err_msg,5);
4523: END IF;
4524:
4525: IF (g_debug_level <= 1) THEN
4526: cln_debug_pub.Add('---- Updating the status_flag ------',5);
4527: END IF;
4528:
4529: UPDATE M4R_WSM_DWIP_HDR_STAGING
4530: SET status_flag = 'E',error_message = l_err_msg
4558:
4559: wf_engine.SetItemAttrText('M4R7B1OI',p_itemkey,'NOTIF_CODE','7B1_NOTIF2');
4560:
4561: IF (g_debug_level <= 1) THEN
4562: cln_debug_pub.Add('---- Updating the status_flag ------',1);
4563: END IF;
4564:
4565: UPDATE M4R_WSM_DWIP_HDR_STAGING
4566: SET status_flag = 'E',error_message = l_err_msg
4592: ELSE -- l_waiting_rows = 1
4593:
4594: l_get_notif_code := wf_engine.GetItemAttrText(p_itemtype,p_itemkey,'NOTIF_CODE');
4595: IF (g_debug_level <= 1) THEN
4596: cln_debug_pub.Add('l_get_notif_code : ' || l_get_notif_code, 1);
4597: END IF;
4598:
4599: IF l_get_notif_code IS NULL THEN -- no err in the trx processing
4600:
4630:
4631: END IF;
4632:
4633: IF (g_debug_level <= 1) THEN
4634: cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
4635: cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
4636: END IF;
4637:
4638: SELECT M4R_7B1_OSFM_S1.NEXTVAL
4631: END IF;
4632:
4633: IF (g_debug_level <= 1) THEN
4634: cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
4635: cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
4636: END IF;
4637:
4638: SELECT M4R_7B1_OSFM_S1.NEXTVAL
4639: INTO l_event_key
4649:
4650: END IF;
4651:
4652: IF (g_debug_level <= 2) THEN
4653: cln_debug_pub.Add('------- EXITING CHECK_CP_IMPORT_STATUS ------------', 2);
4654: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4655: END IF;
4656:
4657:
4650: END IF;
4651:
4652: IF (g_debug_level <= 2) THEN
4653: cln_debug_pub.Add('------- EXITING CHECK_CP_IMPORT_STATUS ------------', 2);
4654: cln_debug_pub.Add('resultout : ' || x_resultout, 2);
4655: END IF;
4656:
4657:
4658: EXCEPTION
4662:
4663: -- x_resultout := 'FAILED';
4664:
4665: IF (g_debug_level <= 5) THEN
4666: cln_debug_pub.Add('-------- Exception in procedure CHECK_CP_IMPORT_STATUS ------',5);
4667: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4668: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4669: END IF;
4670:
4663: -- x_resultout := 'FAILED';
4664:
4665: IF (g_debug_level <= 5) THEN
4666: cln_debug_pub.Add('-------- Exception in procedure CHECK_CP_IMPORT_STATUS ------',5);
4667: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4668: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4669: END IF;
4670:
4671: END CHECK_CP_IMPORT_STATUS;
4664:
4665: IF (g_debug_level <= 5) THEN
4666: cln_debug_pub.Add('-------- Exception in procedure CHECK_CP_IMPORT_STATUS ------',5);
4667: cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg, 5);
4668: cln_debug_pub.Add('Error is ' || g_error_code || ':' || g_errmsg, 5);
4669: END IF;
4670:
4671: END CHECK_CP_IMPORT_STATUS;
4672: