[Home] [Help]
41: --
42: BEGIN
43: --
44: IF (l_debug <> -1) THEN
45: rlm_core_sv.dpush(k_SDEBUG,'ManageForecast');
46: rlm_core_sv.dlog(k_DEBUG,'x_InterfaceHeaderId',x_InterfaceHeaderId);
47: END IF;
48: --
49: x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
42: BEGIN
43: --
44: IF (l_debug <> -1) THEN
45: rlm_core_sv.dpush(k_SDEBUG,'ManageForecast');
46: rlm_core_sv.dlog(k_DEBUG,'x_InterfaceHeaderId',x_InterfaceHeaderId);
47: END IF;
48: --
49: x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
50: --
45: rlm_core_sv.dpush(k_SDEBUG,'ManageForecast');
46: rlm_core_sv.dlog(k_DEBUG,'x_InterfaceHeaderId',x_InterfaceHeaderId);
47: END IF;
48: --
49: x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
50: --
51: IF NOT rlm_dp_sv.CheckForecast(x_InterfaceHeaderId,x_Group_rec) THEN
52: --
53: IF (l_debug <> -1) THEN
50: --
51: IF NOT rlm_dp_sv.CheckForecast(x_InterfaceHeaderId,x_Group_rec) THEN
52: --
53: IF (l_debug <> -1) THEN
54: rlm_core_sv.dlog(k_DEBUG,'No Forecast processing');
55: END IF;
56: --
57: RAISE e_no_forecast;
58: --
72: --
73: IF NOT LockLines(v_SubGroup_rec, x_InterfaceHeaderId) THEN
74: --
75: IF (l_debug <> -1) THEN
76: rlm_core_sv.dlog(k_DEBUG,'RLM_LOCK_NOT_OBTAINED');
77: END IF;
78: --
79: RAISE e_lines_locked;
80: --
84: --
85:
86: IF (l_debug <> -1) THEN
87: --
88: rlm_core_sv.dlog(k_DEBUG,'customer_id' ,v_subgroup_rec.customer_id);
89: rlm_core_sv.dlog(k_DEBUG,'ship_to_customer_id',v_subgroup_rec.ship_to_customer_id);
90: rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
85:
86: IF (l_debug <> -1) THEN
87: --
88: rlm_core_sv.dlog(k_DEBUG,'customer_id' ,v_subgroup_rec.customer_id);
89: rlm_core_sv.dlog(k_DEBUG,'ship_to_customer_id',v_subgroup_rec.ship_to_customer_id);
90: rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
86: IF (l_debug <> -1) THEN
87: --
88: rlm_core_sv.dlog(k_DEBUG,'customer_id' ,v_subgroup_rec.customer_id);
89: rlm_core_sv.dlog(k_DEBUG,'ship_to_customer_id',v_subgroup_rec.ship_to_customer_id);
90: rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
87: --
88: rlm_core_sv.dlog(k_DEBUG,'customer_id' ,v_subgroup_rec.customer_id);
89: rlm_core_sv.dlog(k_DEBUG,'ship_to_customer_id',v_subgroup_rec.ship_to_customer_id);
90: rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95: rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
88: rlm_core_sv.dlog(k_DEBUG,'customer_id' ,v_subgroup_rec.customer_id);
89: rlm_core_sv.dlog(k_DEBUG,'ship_to_customer_id',v_subgroup_rec.ship_to_customer_id);
90: rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95: rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
96: rlm_core_sv.dlog(k_DEBUG,'inventory_item_id' ,v_subgroup_rec.inventory_item_id);
89: rlm_core_sv.dlog(k_DEBUG,'ship_to_customer_id',v_subgroup_rec.ship_to_customer_id);
90: rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95: rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
96: rlm_core_sv.dlog(k_DEBUG,'inventory_item_id' ,v_subgroup_rec.inventory_item_id);
97: rlm_core_sv.dlog(k_DEBUG,'industry_attribute15' ,v_subgroup_rec.industry_attribute15);
90: rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id' ,v_subgroup_rec.ship_from_org_id);
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95: rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
96: rlm_core_sv.dlog(k_DEBUG,'inventory_item_id' ,v_subgroup_rec.inventory_item_id);
97: rlm_core_sv.dlog(k_DEBUG,'industry_attribute15' ,v_subgroup_rec.industry_attribute15);
98: --
91: rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id' ,v_subgroup_rec.ship_to_address_id);
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95: rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
96: rlm_core_sv.dlog(k_DEBUG,'inventory_item_id' ,v_subgroup_rec.inventory_item_id);
97: rlm_core_sv.dlog(k_DEBUG,'industry_attribute15' ,v_subgroup_rec.industry_attribute15);
98: --
99: END IF;
92: rlm_core_sv.dlog(k_DEBUG,'ship_to_site_use_id' ,v_subgroup_rec.ship_to_site_use_id);
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95: rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
96: rlm_core_sv.dlog(k_DEBUG,'inventory_item_id' ,v_subgroup_rec.inventory_item_id);
97: rlm_core_sv.dlog(k_DEBUG,'industry_attribute15' ,v_subgroup_rec.industry_attribute15);
98: --
99: END IF;
100:
93: rlm_core_sv.dlog(k_DEBUG,'bill_to_address_id' ,v_subgroup_rec.bill_to_address_id);
94: rlm_core_sv.dlog(k_DEBUG,'bill_to_site_use_id' ,v_subgroup_rec.bill_to_site_use_id);
95: rlm_core_sv.dlog(k_DEBUG,'customer_item_id' ,v_subgroup_rec.customer_item_id);
96: rlm_core_sv.dlog(k_DEBUG,'inventory_item_id' ,v_subgroup_rec.inventory_item_id);
97: rlm_core_sv.dlog(k_DEBUG,'industry_attribute15' ,v_subgroup_rec.industry_attribute15);
98: --
99: END IF;
100:
101: RLM_TPA_SV.ManageGroupForecast(x_sched_rec,
117: CLOSE v_SubGroup_ref;
118:
119: IF (l_debug <> -1) THEN
120: --
121: rlm_core_sv.dlog(k_DEBUG,'before mrp_forecast_interface_pk api');
122: --
123: END IF;
124:
125: SELECT hsecs INTO l_start_time from v$timer; --Bugfix 12863728
127: IF mrp_forecast_interface_pk.mrp_forecast_interface(t_forecast,
128: t_designator) THEN
129: IF (l_debug <> -1) THEN
130: --
131: rlm_core_sv.dlog(k_DEBUG,'after mrp_forecast_interface_pk.
132: mrp_forecast_interface api');
133: --
134: END IF;
135: --
146: FOR v_counter IN 1..t_designator.COUNT LOOP
147:
148: IF (l_debug <> -1) THEN
149: --
150: rlm_core_sv.dlog(k_DEBUG,'Inserted new forecast for ', t_designator(v_counter).forecast_designator);
151: --
152: END IF;
153: END LOOP;
154: --
159: --
160: x_progress :='070';
161: --
162: IF (l_debug <> -1) THEN
163: rlm_core_sv.dlog(k_DEBUG,'MRP Forecast API Failed ');
164: END IF;
165: --
166:
167: FOR v_counter IN 1..t_designator.COUNT LOOP
186: x_InterfaceLineId => v_InterfaceLineId,
187: x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
188: x_ScheduleLineId => NULL,
189: x_Token1 => 'GROUP',
190: x_value1 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
191: rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
192: rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
193: x_Token2 => 'FORECAST_DESIGNATOR',
194: x_value2 => v_designator);
187: x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
188: x_ScheduleLineId => NULL,
189: x_Token1 => 'GROUP',
190: x_value1 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
191: rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
192: rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
193: x_Token2 => 'FORECAST_DESIGNATOR',
194: x_value2 => v_designator);
195: --
188: x_ScheduleLineId => NULL,
189: x_Token1 => 'GROUP',
190: x_value1 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
191: rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
192: rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
193: x_Token2 => 'FORECAST_DESIGNATOR',
194: x_value2 => v_designator);
195: --
196: RAISE e_group_error;
199: --
200: t_forecast.DELETE;
201: t_designator.DELETE;
202: --
203: --x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
204: --
205: IF (l_debug <> -1) THEN
206: rlm_core_sv.dpop(k_SDEBUG);
207: END IF;
202: --
203: --x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
204: --
205: IF (l_debug <> -1) THEN
206: rlm_core_sv.dpop(k_SDEBUG);
207: END IF;
208: --
209: EXCEPTION
210: --
211: WHEN e_Group_Error THEN
212: --
213: t_forecast.DELETE;
214: t_designator.DELETE;
215: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
216: --
217: IF (l_debug <> -1) THEN
218: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
219: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
214: t_designator.DELETE;
215: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
216: --
217: IF (l_debug <> -1) THEN
218: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
219: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
220: END IF;
221: --
222: WHEN e_lines_locked THEN
215: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
216: --
217: IF (l_debug <> -1) THEN
218: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
219: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
220: END IF;
221: --
222: WHEN e_lines_locked THEN
223: --
222: WHEN e_lines_locked THEN
223: --
224: t_forecast.DELETE;
225: t_designator.DELETE;
226: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
227: --
228: SELECT line_id
229: INTO v_InterfaceLineId
230: FROM rlm_interface_lines
244: x_Token1 => 'SCHED_REF',
245: x_value1 => x_sched_rec.schedule_reference_num);
246: --
247: IF (l_debug <> -1) THEN
248: rlm_core_sv.dpop(k_DEBUG,'lines are locked');
249: END IF;
250: --
251: WHEN NO_DATA_FOUND THEN
252: --
251: WHEN NO_DATA_FOUND THEN
252: --
253: t_forecast.DELETE;
254: t_designator.DELETE;
255: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
256: --
257: IF (l_debug <> -1) THEN
258: rlm_core_sv.dlog(k_DEBUG,'No data found in Interface headers for headerId',
259: x_InterfaceHeaderId);
254: t_designator.DELETE;
255: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
256: --
257: IF (l_debug <> -1) THEN
258: rlm_core_sv.dlog(k_DEBUG,'No data found in Interface headers for headerId',
259: x_InterfaceHeaderId);
260: rlm_core_sv.dpop(k_SDEBUG);
261: END IF;
262: --
256: --
257: IF (l_debug <> -1) THEN
258: rlm_core_sv.dlog(k_DEBUG,'No data found in Interface headers for headerId',
259: x_InterfaceHeaderId);
260: rlm_core_sv.dpop(k_SDEBUG);
261: END IF;
262: --
263: WHEN e_no_forecast THEN
264: --
262: --
263: WHEN e_no_forecast THEN
264: --
265: IF (l_debug <> -1) THEN
266: rlm_core_sv.dpop(k_SDEBUG,'ManageForecast: e_no_forecast');
267: END IF;
268: --
269: WHEN OTHERS THEN
270: --
269: WHEN OTHERS THEN
270: --
271: t_forecast.DELETE;
272: t_designator.DELETE;
273: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
274: --
275: rlm_message_sv.sql_error('rlm_forecast_sv.ManageForecast',x_progress);
276: --
277: IF (l_debug <> -1) THEN
274: --
275: rlm_message_sv.sql_error('rlm_forecast_sv.ManageForecast',x_progress);
276: --
277: IF (l_debug <> -1) THEN
278: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
279: END IF;
280: --
281: END ManageForecast;
282:
301: --
302: BEGIN
303: --
304: IF (l_debug <> -1) THEN
305: rlm_core_sv.dpush(k_SDEBUG,'ManageGroupForecast');
306: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
307: x_Group_rec.customer_id);
308: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
309: x_Group_rec.ship_from_org_id);
302: BEGIN
303: --
304: IF (l_debug <> -1) THEN
305: rlm_core_sv.dpush(k_SDEBUG,'ManageGroupForecast');
306: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
307: x_Group_rec.customer_id);
308: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
309: x_Group_rec.ship_from_org_id);
310: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
304: IF (l_debug <> -1) THEN
305: rlm_core_sv.dpush(k_SDEBUG,'ManageGroupForecast');
306: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
307: x_Group_rec.customer_id);
308: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
309: x_Group_rec.ship_from_org_id);
310: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
311: x_Group_rec.ship_to_site_use_id);
312: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
306: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
307: x_Group_rec.customer_id);
308: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
309: x_Group_rec.ship_from_org_id);
310: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
311: x_Group_rec.ship_to_site_use_id);
312: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
313: x_Group_rec.bill_to_site_use_id);
314: END IF;
308: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
309: x_Group_rec.ship_from_org_id);
310: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
311: x_Group_rec.ship_to_site_use_id);
312: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
313: x_Group_rec.bill_to_site_use_id);
314: END IF;
315: --
316: RLM_TPA_SV.GetDesignator(x_sched_rec,
326: -- Validate the forecast designator as it is not done in
327: -- validate demand
328: --
329: IF (l_debug <> -1) THEN
330: rlm_core_sv.dlog(k_DEBUG,'forecast designator',v_forecast_designator);
331: rlm_core_sv.dlog(k_DEBUG,'x_Sched_rec.schedule_purpose',
332: x_Sched_rec.schedule_purpose);
333: END IF;
334: --
327: -- validate demand
328: --
329: IF (l_debug <> -1) THEN
330: rlm_core_sv.dlog(k_DEBUG,'forecast designator',v_forecast_designator);
331: rlm_core_sv.dlog(k_DEBUG,'x_Sched_rec.schedule_purpose',
332: x_Sched_rec.schedule_purpose);
333: END IF;
334: --
335: --
337: --
338: IF v_forecast_designator IS NULL THEN
339: --
340: IF (l_debug <> -1) THEN
341: rlm_core_sv.dlog(k_DEBUG, 'Null Forecast Designator, raising group error');
342: END IF;
343: --
344: SELECT line_id
345: INTO v_InterfaceLineId
357: x_InterfaceLineId => v_InterfaceLineId,
358: x_ScheduleHeaderId => x_Sched_rec.schedule_header_id,
359: x_ScheduleLineId => NULL,
360: x_Token1=>'CUST',
361: x_Value1=> rlm_core_sv.get_customer_name(x_Sched_rec.customer_id));
362: --
363: RAISE e_Group_Error;
364: --
365: END IF;
366: --
367: IF x_Sched_rec.schedule_purpose = k_ADD THEN
368: --
369: IF (l_debug <> -1) THEN
370: rlm_core_sv.dlog(k_DEBUG,'No Lines in t_designator as schedule purpose = ADD');
371: END IF;
372: --
373: ELSIF x_Sched_rec.schedule_purpose IN (k_CANCEL,
374: k_DELETE,
384: x_designator(x_designator.COUNT).inventory_item_id :=
385: x_Group_rec.inventory_item_id;
386: --
387: IF (l_debug <> -1) THEN
388: rlm_core_sv.dlog(k_DEBUG,'x_designator.forecast_designator',
389: x_designator(x_designator.COUNT).forecast_designator);
390: rlm_core_sv.dlog(k_DEBUG,'x_designator.organization_id',
391: x_designator(x_designator.COUNT).organization_id);
392: rlm_core_sv.dlog(k_DEBUG,'x_designator.inventory_item_id',
386: --
387: IF (l_debug <> -1) THEN
388: rlm_core_sv.dlog(k_DEBUG,'x_designator.forecast_designator',
389: x_designator(x_designator.COUNT).forecast_designator);
390: rlm_core_sv.dlog(k_DEBUG,'x_designator.organization_id',
391: x_designator(x_designator.COUNT).organization_id);
392: rlm_core_sv.dlog(k_DEBUG,'x_designator.inventory_item_id',
393: x_designator(x_designator.COUNT).inventory_item_id);
394: END IF;
388: rlm_core_sv.dlog(k_DEBUG,'x_designator.forecast_designator',
389: x_designator(x_designator.COUNT).forecast_designator);
390: rlm_core_sv.dlog(k_DEBUG,'x_designator.organization_id',
391: x_designator(x_designator.COUNT).organization_id);
392: rlm_core_sv.dlog(k_DEBUG,'x_designator.inventory_item_id',
393: x_designator(x_designator.COUNT).inventory_item_id);
394: END IF;
395: --
396: END IF;
403: v_forecast_designator);
404: --
405: END IF;
406: --
407: x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
408: --
409: IF (l_debug <> -1) THEN
410: rlm_core_sv.dpop(k_DEBUG);
411: END IF;
406: --
407: x_ReturnStatus := rlm_core_sv.k_PROC_SUCCESS;
408: --
409: IF (l_debug <> -1) THEN
410: rlm_core_sv.dpop(k_DEBUG);
411: END IF;
412: --
413: EXCEPTION
414: WHEN e_Group_Error THEN
412: --
413: EXCEPTION
414: WHEN e_Group_Error THEN
415: --
416: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
417: --
418: IF (l_debug <> -1) THEN
419: rlm_core_sv.dlog(k_DEBUG,'ManageGroupForecast : progress',x_Progress);
420: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
415: --
416: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
417: --
418: IF (l_debug <> -1) THEN
419: rlm_core_sv.dlog(k_DEBUG,'ManageGroupForecast : progress',x_Progress);
420: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
421: END IF;
422: --
423: RAISE;
416: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
417: --
418: IF (l_debug <> -1) THEN
419: rlm_core_sv.dlog(k_DEBUG,'ManageGroupForecast : progress',x_Progress);
420: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
421: END IF;
422: --
423: RAISE;
424: --
423: RAISE;
424: --
425: WHEN OTHERS THEN
426: --
427: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
428: rlm_message_sv.sql_error('rlm_forecast_sv.ManageForecast',x_progress);
429: --
430: IF (l_debug <> -1) THEN
431: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
427: x_ReturnStatus := rlm_core_sv.k_PROC_ERROR;
428: rlm_message_sv.sql_error('rlm_forecast_sv.ManageForecast',x_progress);
429: --
430: IF (l_debug <> -1) THEN
431: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
432: END IF;
433: --
434: raise;
435: --
448:
449: BEGIN
450: --
451: IF (l_debug <> -1) THEN
452: rlm_core_sv.dpush(k_SDEBUG,'InitializeGroup');
453: END IF;
454: --
455: OPEN x_Group_ref FOR
456: SELECT rih.customer_id,
470: AND ril.industry_attribute15 = x_Group_rec.ship_from_org_id
471: AND ril.item_detail_type = k_MRP_FORECAST
472: AND ril.inventory_item_id = x_Group_rec.inventory_item_id
473: AND ril.ship_to_address_id = x_Group_rec.ship_to_address_id
474: AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
475: AND rih.org_id = ril.org_id
476: GROUP BY rih.customer_id,
477: ril.ship_from_org_id,
478: ril.ship_to_address_id,
488: ril.bill_to_site_use_id,
489: ril.customer_item_id;
490: --
491: IF (l_debug <> -1) THEN
492: rlm_core_sv.dpop(k_SDEBUG);
493: END IF;
494: --
495: EXCEPTION
496: WHEN OTHERS THEN
496: WHEN OTHERS THEN
497: rlm_message_sv.sql_error('RLM_FORECAST_SV.InitializeGroup',x_progress);
498: --
499: IF (l_debug <> -1) THEN
500: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
501: END IF;
502: --
503: raise e_group_error;
504:
517: x_progress VARCHAR2(3) := '010';
518: BEGIN
519: --
520: IF (l_debug <> -1) THEN
521: rlm_core_sv.dpush(k_SDEBUG,'FetchGroup');
522: END IF;
523: --
524: FETCH x_Group_ref INTO
525: x_Group_rec.customer_id,
535: --
536: IF x_Group_ref%NOTFOUND THEN
537: --
538: IF (l_debug <> -1) THEN
539: rlm_core_sv.dpop(k_SDEBUG, 'false');
540: END IF;
541: --
542: RETURN(FALSE);
543: --
543: --
544: ELSE
545: --
546: IF (l_debug <> -1) THEN
547: rlm_core_sv.dpop(k_SDEBUG, 'true');
548: END IF;
549: --
550: RETURN(TRUE);
551: --
555: WHEN OTHERS THEN
556: rlm_message_sv.sql_error('rlm_forecast_sv.FetchGroup',x_progress);
557: --
558: IF (l_debug <> -1) THEN
559: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
560: END IF;
561: --
562: raise;
563:
576: CURSOR c IS
577: SELECT *
578: FROM rlm_interface_headers
579: WHERE header_id = x_header_id
580: AND process_status = rlm_core_sv.k_PS_AVAILABLE
581: FOR UPDATE NOWAIT;
582:
583: BEGIN
584: --
582:
583: BEGIN
584: --
585: IF (l_debug <> -1) THEN
586: rlm_core_sv.dpush(k_SDEBUG,'LockHeaders');
587: rlm_core_sv.dlog(k_DEBUG,'Locking RLM_INTERFACE_HEADERS');
588: END IF;
589: --
590: OPEN c;
583: BEGIN
584: --
585: IF (l_debug <> -1) THEN
586: rlm_core_sv.dpush(k_SDEBUG,'LockHeaders');
587: rlm_core_sv.dlog(k_DEBUG,'Locking RLM_INTERFACE_HEADERS');
588: END IF;
589: --
590: OPEN c;
591: --
591: --
592: CLOSE c;
593: --
594: IF (l_debug <> -1) THEN
595: rlm_core_sv.dlog(k_DEBUG,'Returning True ');
596: rlm_core_sv.dpop(k_SDEBUG);
597: END IF;
598: --
599: RETURN TRUE;
592: CLOSE c;
593: --
594: IF (l_debug <> -1) THEN
595: rlm_core_sv.dlog(k_DEBUG,'Returning True ');
596: rlm_core_sv.dpop(k_SDEBUG);
597: END IF;
598: --
599: RETURN TRUE;
600: --
601: EXCEPTION
602: WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
603: --
604: IF (l_debug <> -1) THEN
605: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
606: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
607: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
608: END IF;
609: --
602: WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
603: --
604: IF (l_debug <> -1) THEN
605: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
606: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
607: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
608: END IF;
609: --
610: RETURN FALSE;
603: --
604: IF (l_debug <> -1) THEN
605: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
606: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
607: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
608: END IF;
609: --
610: RETURN FALSE;
611:
612: WHEN OTHERS THEN
613: rlm_message_sv.sql_error('rlm_forecast_sv.LockHeaders',x_progress);
614: --
615: IF (l_debug <> -1) THEN
616: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE from WHEN OTHERS ');
617: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
618: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
619: END IF;
620: --
613: rlm_message_sv.sql_error('rlm_forecast_sv.LockHeaders',x_progress);
614: --
615: IF (l_debug <> -1) THEN
616: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE from WHEN OTHERS ');
617: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
618: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
619: END IF;
620: --
621: RETURN FALSE;
614: --
615: IF (l_debug <> -1) THEN
616: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE from WHEN OTHERS ');
617: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
618: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
619: END IF;
620: --
621: RETURN FALSE;
622:
635:
636: BEGIN
637: --
638: IF (l_debug <> -1) THEN
639: rlm_core_sv.dpush(k_SDEBUG,'UpdateHeaderStatus');
640: rlm_core_sv.dlog(k_DEBUG,'UpdateHeaderStatus to ', x_status);
641: END IF;
642: --
643: UPDATE rlm_interface_headers
636: BEGIN
637: --
638: IF (l_debug <> -1) THEN
639: rlm_core_sv.dpush(k_SDEBUG,'UpdateHeaderStatus');
640: rlm_core_sv.dlog(k_DEBUG,'UpdateHeaderStatus to ', x_status);
641: END IF;
642: --
643: UPDATE rlm_interface_headers
644: SET process_status = x_Status
648: SET process_status = x_Status
649: WHERE header_id = x_ScheduleHeaderId;
650: --
651: IF (l_debug <> -1) THEN
652: rlm_core_sv.dpop(k_SDEBUG);
653: END IF;
654: --
655: EXCEPTION
656: --
658: --
659: rlm_message_sv.sql_error('rlm_forecast_sv.UpdateHeaderStatus',x_progress);
660: --
661: IF (l_debug <> -1) THEN
662: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
663: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
664: END IF;
665: --
666: RAISE ;
659: rlm_message_sv.sql_error('rlm_forecast_sv.UpdateHeaderStatus',x_progress);
660: --
661: IF (l_debug <> -1) THEN
662: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
663: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
664: END IF;
665: --
666: RAISE ;
667: --
686: AND ship_to_site_use_id = x_Group_rec.ship_to_site_use_id
687: AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
688: AND customer_item_id = x_Group_rec.customer_item_id
689: AND inventory_item_id = x_Group_rec.inventory_item_id
690: AND process_status = rlm_core_sv.k_PS_AVAILABLE
691: FOR UPDATE NOWAIT;
692:
693: BEGIN
694: --
692:
693: BEGIN
694: --
695: IF (l_debug <> -1) THEN
696: rlm_core_sv.dpush(k_SDEBUG,'LockLines');
697: rlm_core_sv.dlog(k_DEBUG,'Locking RLM_INTERFACE_LINES');
698: END IF;
699: --
700: OPEN c;
693: BEGIN
694: --
695: IF (l_debug <> -1) THEN
696: rlm_core_sv.dpush(k_SDEBUG,'LockLines');
697: rlm_core_sv.dlog(k_DEBUG,'Locking RLM_INTERFACE_LINES');
698: END IF;
699: --
700: OPEN c;
701: --
701: --
702: CLOSE c;
703: --
704: IF (l_debug <> -1) THEN
705: rlm_core_sv.dlog(k_DEBUG,'Returning True ');
706: rlm_core_sv.dpop(k_SDEBUG);
707: END IF;
708: --
709: RETURN TRUE;
702: CLOSE c;
703: --
704: IF (l_debug <> -1) THEN
705: rlm_core_sv.dlog(k_DEBUG,'Returning True ');
706: rlm_core_sv.dpop(k_SDEBUG);
707: END IF;
708: --
709: RETURN TRUE;
710: --
711: EXCEPTION
712: WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
713: --
714: IF (l_debug <> -1) THEN
715: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
716: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
717: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
718: END IF;
719: --
712: WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
713: --
714: IF (l_debug <> -1) THEN
715: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
716: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
717: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
718: END IF;
719: --
720: RETURN FALSE;
713: --
714: IF (l_debug <> -1) THEN
715: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE');
716: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
717: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
718: END IF;
719: --
720: RETURN FALSE;
721:
722: WHEN OTHERS THEN
723: rlm_message_sv.sql_error('rlm_forecast_sv.LockLines',x_progress);
724: --
725: IF (l_debug <> -1) THEN
726: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE OTHERS ');
727: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
728: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
729: END IF;
730: --
723: rlm_message_sv.sql_error('rlm_forecast_sv.LockLines',x_progress);
724: --
725: IF (l_debug <> -1) THEN
726: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE OTHERS ');
727: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
728: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
729: END IF;
730: --
731: RETURN FALSE;
724: --
725: IF (l_debug <> -1) THEN
726: rlm_core_sv.dlog(k_DEBUG,'Returning FALSE OTHERS ');
727: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
728: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
729: END IF;
730: --
731: RETURN FALSE;
732:
746:
747: BEGIN
748: --
749: IF (l_debug <> -1) THEN
750: rlm_core_sv.dpush(k_SDEBUG,'UpdateGroupStatus');
751: rlm_core_sv.dlog(k_DEBUG,'UpdateGroupStatus to ', x_status);
752: rlm_core_sv.dlog(k_DEBUG,'x_header_id ', x_header_id);
753: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id ',
754: x_Group_rec.ship_from_org_id);
747: BEGIN
748: --
749: IF (l_debug <> -1) THEN
750: rlm_core_sv.dpush(k_SDEBUG,'UpdateGroupStatus');
751: rlm_core_sv.dlog(k_DEBUG,'UpdateGroupStatus to ', x_status);
752: rlm_core_sv.dlog(k_DEBUG,'x_header_id ', x_header_id);
753: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id ',
754: x_Group_rec.ship_from_org_id);
755: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id ',
748: --
749: IF (l_debug <> -1) THEN
750: rlm_core_sv.dpush(k_SDEBUG,'UpdateGroupStatus');
751: rlm_core_sv.dlog(k_DEBUG,'UpdateGroupStatus to ', x_status);
752: rlm_core_sv.dlog(k_DEBUG,'x_header_id ', x_header_id);
753: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id ',
754: x_Group_rec.ship_from_org_id);
755: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id ',
756: x_Group_rec.ship_to_site_use_id);
749: IF (l_debug <> -1) THEN
750: rlm_core_sv.dpush(k_SDEBUG,'UpdateGroupStatus');
751: rlm_core_sv.dlog(k_DEBUG,'UpdateGroupStatus to ', x_status);
752: rlm_core_sv.dlog(k_DEBUG,'x_header_id ', x_header_id);
753: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id ',
754: x_Group_rec.ship_from_org_id);
755: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id ',
756: x_Group_rec.ship_to_site_use_id);
757: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id ',
751: rlm_core_sv.dlog(k_DEBUG,'UpdateGroupStatus to ', x_status);
752: rlm_core_sv.dlog(k_DEBUG,'x_header_id ', x_header_id);
753: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id ',
754: x_Group_rec.ship_from_org_id);
755: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id ',
756: x_Group_rec.ship_to_site_use_id);
757: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id ',
758: x_Group_rec.bill_to_site_use_id);
759: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_item_id ',
753: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id ',
754: x_Group_rec.ship_from_org_id);
755: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id ',
756: x_Group_rec.ship_to_site_use_id);
757: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id ',
758: x_Group_rec.bill_to_site_use_id);
759: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_item_id ',
760: x_Group_rec.customer_item_id);
761: rlm_core_sv.dlog(k_DEBUG,'x_ScheduleHeaderId ', x_ScheduleHeaderId);
755: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id ',
756: x_Group_rec.ship_to_site_use_id);
757: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id ',
758: x_Group_rec.bill_to_site_use_id);
759: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_item_id ',
760: x_Group_rec.customer_item_id);
761: rlm_core_sv.dlog(k_DEBUG,'x_ScheduleHeaderId ', x_ScheduleHeaderId);
762: rlm_core_sv.dlog(k_DEBUG,'x_UpdateLevel to ', x_UpdateLevel);
763: END IF;
757: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id ',
758: x_Group_rec.bill_to_site_use_id);
759: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_item_id ',
760: x_Group_rec.customer_item_id);
761: rlm_core_sv.dlog(k_DEBUG,'x_ScheduleHeaderId ', x_ScheduleHeaderId);
762: rlm_core_sv.dlog(k_DEBUG,'x_UpdateLevel to ', x_UpdateLevel);
763: END IF;
764: --
765: IF x_UpdateLevel <> 'GROUP' THEN
758: x_Group_rec.bill_to_site_use_id);
759: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_item_id ',
760: x_Group_rec.customer_item_id);
761: rlm_core_sv.dlog(k_DEBUG,'x_ScheduleHeaderId ', x_ScheduleHeaderId);
762: rlm_core_sv.dlog(k_DEBUG,'x_UpdateLevel to ', x_UpdateLevel);
763: END IF;
764: --
765: IF x_UpdateLevel <> 'GROUP' THEN
766: --
766: --
767: UPDATE rlm_interface_lines
768: SET process_status = x_Status
769: WHERE header_id = x_header_id
770: AND process_status = rlm_core_sv.k_PS_AVAILABLE
771: AND Item_detail_type = k_MRP_FORECAST;
772: --
773: UPDATE rlm_schedule_lines
774: SET process_status = x_Status
772: --
773: UPDATE rlm_schedule_lines
774: SET process_status = x_Status
775: WHERE header_id = x_ScheduleHeaderid
776: AND process_status in (rlm_core_sv.k_PS_AVAILABLE,
777: rlm_core_sv.k_PS_ERROR)
778: AND item_detail_type = k_MRP_FORECAST;
779: --
780: ELSE
773: UPDATE rlm_schedule_lines
774: SET process_status = x_Status
775: WHERE header_id = x_ScheduleHeaderid
776: AND process_status in (rlm_core_sv.k_PS_AVAILABLE,
777: rlm_core_sv.k_PS_ERROR)
778: AND item_detail_type = k_MRP_FORECAST;
779: --
780: ELSE
781: --
787: = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
788: AND nvl(bill_to_site_use_id,k_NNULL)
789: = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
790: AND inventory_item_id = x_Group_rec.inventory_item_id
791: AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
792: rlm_core_sv.k_PS_ERROR)
793: AND line_id IN ( select schedule_line_id
794: from rlm_interface_lines
795: WHERE header_id = x_header_id
788: AND nvl(bill_to_site_use_id,k_NNULL)
789: = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
790: AND inventory_item_id = x_Group_rec.inventory_item_id
791: AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
792: rlm_core_sv.k_PS_ERROR)
793: AND line_id IN ( select schedule_line_id
794: from rlm_interface_lines
795: WHERE header_id = x_header_id
796: AND ship_from_org_id = x_Group_rec.ship_from_org_id
798: = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
799: AND nvl(bill_to_site_use_id,k_NNULL)
800: = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
801: AND inventory_item_id = x_Group_rec.inventory_item_id
802: AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
803: rlm_core_sv.k_PS_ERROR)
804: AND item_detail_type = k_MRP_FORECAST);
805: --
806: IF (l_debug <> -1) THEN
799: AND nvl(bill_to_site_use_id,k_NNULL)
800: = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
801: AND inventory_item_id = x_Group_rec.inventory_item_id
802: AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
803: rlm_core_sv.k_PS_ERROR)
804: AND item_detail_type = k_MRP_FORECAST);
805: --
806: IF (l_debug <> -1) THEN
807: rlm_core_sv.dlog(k_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
803: rlm_core_sv.k_PS_ERROR)
804: AND item_detail_type = k_MRP_FORECAST);
805: --
806: IF (l_debug <> -1) THEN
807: rlm_core_sv.dlog(k_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
808: END IF;
809: --
810: UPDATE rlm_interface_lines
811: SET process_status = x_Status
815: = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
816: AND nvl(bill_to_site_use_id,k_NNULL)
817: = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
818: AND inventory_item_id = x_Group_rec.inventory_item_id
819: AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
820: rlm_core_sv.k_PS_ERROR)
821: AND item_detail_type = k_MRP_FORECAST;
822: --
823: IF (l_debug <> -1) THEN
816: AND nvl(bill_to_site_use_id,k_NNULL)
817: = nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
818: AND inventory_item_id = x_Group_rec.inventory_item_id
819: AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
820: rlm_core_sv.k_PS_ERROR)
821: AND item_detail_type = k_MRP_FORECAST;
822: --
823: IF (l_debug <> -1) THEN
824: rlm_core_sv.dlog(k_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
820: rlm_core_sv.k_PS_ERROR)
821: AND item_detail_type = k_MRP_FORECAST;
822: --
823: IF (l_debug <> -1) THEN
824: rlm_core_sv.dlog(k_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
825: END IF;
826: --
827: END IF;
828: --
826: --
827: END IF;
828: --
829: IF (l_debug <> -1) THEN
830: rlm_core_sv.dpop(k_SDEBUG);
831: END IF;
832: --
833: EXCEPTION
834: --
836: --
837: rlm_message_sv.sql_error('rlm_forecast_sv.UpdateGroupStatus',x_progress);
838: --
839: IF (l_debug <> -1) THEN
840: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
841: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
842: END IF;
843: --
844: RAISE ;
837: rlm_message_sv.sql_error('rlm_forecast_sv.UpdateGroupStatus',x_progress);
838: --
839: IF (l_debug <> -1) THEN
840: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
841: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHER - sql error');
842: END IF;
843: --
844: RAISE ;
845: --
859: --
860: BEGIN
861: --
862: IF (l_debug <> -1) THEN
863: rlm_core_sv.dpush(k_SDEBUG,'initialize_table');
864: END IF;
865: --
866: t_forecast.DELETE;
867: --
865: --
866: t_forecast.DELETE;
867: --
868: IF (l_debug <> -1) THEN
869: rlm_core_sv.dpop(k_SDEBUG);
870: END IF;
871: --
872: EXCEPTION
873: WHEN OTHERS THEN
873: WHEN OTHERS THEN
874: rlm_message_sv.sql_error ('RLM_forecast_sv.initialize_table', x_progress);
875: --
876: IF (l_debug <> -1) THEN
877: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHERS - sql error');
878: END IF;
879: --
880: raise;
881:
929: AND ril.bill_to_site_use_id IS NULL
930: AND ril.inventory_item_id = x_Group_rec.inventory_item_id
931: AND ril.customer_item_id = x_Group_rec.customer_item_id
932: AND ril.item_detail_type = k_MRP_FORECAST
933: AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
934: AND ril.request_date is NOT NULL --bug 2882311
935: AND ril.primary_quantity <> 0
936: --bug 1786492
937:
967: = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
968: AND ril.bill_to_site_use_id IS NULL
969: AND ril.inventory_item_id = x_Group_rec.inventory_item_id
970: AND ril.item_detail_type = k_MRP_FORECAST
971: AND ril.process_status = rlm_core_sv.k_PS_PROCESSED
972: AND ril.primary_quantity <> 0
973: AND x_Sched_rec.schedule_purpose <> k_ADD; --Bugfix 9688324
974:
975:
1005: AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
1006: AND ril.inventory_item_id = x_Group_rec.inventory_item_id
1007: AND ril.customer_item_id = x_Group_rec.customer_item_id
1008: AND ril.item_detail_type = k_MRP_FORECAST
1009: AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
1010: AND ril.request_date is NOT NULL --bug 2882311
1011: AND ril.primary_quantity <> 0
1012: --bug 1786492
1013:
1043: = nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
1044: AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
1045: AND ril.inventory_item_id = x_Group_rec.inventory_item_id
1046: AND ril.item_detail_type = k_MRP_FORECAST
1047: AND ril.process_status = rlm_core_sv.k_PS_PROCESSED
1048: AND ril.primary_quantity <> 0
1049: AND x_Sched_rec.schedule_purpose <> k_ADD; --Bugfix 9688324
1050:
1051: --
1057: --
1058: BEGIN
1059: --
1060: IF (l_debug <> -1) THEN
1061: rlm_core_sv.dpush(k_SDEBUG,'LoadForecast');
1062: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
1063: x_Group_rec.ship_from_org_id);
1064: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
1065: x_Group_rec.bill_to_site_use_id);
1058: BEGIN
1059: --
1060: IF (l_debug <> -1) THEN
1061: rlm_core_sv.dpush(k_SDEBUG,'LoadForecast');
1062: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
1063: x_Group_rec.ship_from_org_id);
1064: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
1065: x_Group_rec.bill_to_site_use_id);
1066: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
1060: IF (l_debug <> -1) THEN
1061: rlm_core_sv.dpush(k_SDEBUG,'LoadForecast');
1062: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
1063: x_Group_rec.ship_from_org_id);
1064: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
1065: x_Group_rec.bill_to_site_use_id);
1066: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
1067: x_Group_rec.ship_to_site_use_id);
1068: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
1062: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_from_org_id',
1063: x_Group_rec.ship_from_org_id);
1064: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
1065: x_Group_rec.bill_to_site_use_id);
1066: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
1067: x_Group_rec.ship_to_site_use_id);
1068: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
1069: x_Group_rec.customer_id);
1070: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.inventory_item_id',
1064: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.bill_to_site_use_id',
1065: x_Group_rec.bill_to_site_use_id);
1066: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
1067: x_Group_rec.ship_to_site_use_id);
1068: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
1069: x_Group_rec.customer_id);
1070: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.inventory_item_id',
1071: x_Group_rec.inventory_item_id);
1072: rlm_core_sv.dlog(k_DEBUG,'x_forecast_designator',
1066: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.ship_to_site_use_id',
1067: x_Group_rec.ship_to_site_use_id);
1068: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
1069: x_Group_rec.customer_id);
1070: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.inventory_item_id',
1071: x_Group_rec.inventory_item_id);
1072: rlm_core_sv.dlog(k_DEBUG,'x_forecast_designator',
1073: x_forecast_designator);
1074: rlm_core_sv.dlog(k_DEBUG,'k_MRP_FORECAST',
1068: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.customer_id',
1069: x_Group_rec.customer_id);
1070: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.inventory_item_id',
1071: x_Group_rec.inventory_item_id);
1072: rlm_core_sv.dlog(k_DEBUG,'x_forecast_designator',
1073: x_forecast_designator);
1074: rlm_core_sv.dlog(k_DEBUG,'k_MRP_FORECAST',
1075: k_MRP_FORECAST);
1076: rlm_core_sv.dlog(k_DEBUG,'rlm_core_sv.k_PS_AVAILABLE',
1070: rlm_core_sv.dlog(k_DEBUG,'x_Group_rec.inventory_item_id',
1071: x_Group_rec.inventory_item_id);
1072: rlm_core_sv.dlog(k_DEBUG,'x_forecast_designator',
1073: x_forecast_designator);
1074: rlm_core_sv.dlog(k_DEBUG,'k_MRP_FORECAST',
1075: k_MRP_FORECAST);
1076: rlm_core_sv.dlog(k_DEBUG,'rlm_core_sv.k_PS_AVAILABLE',
1077: rlm_core_sv.k_PS_AVAILABLE);
1078: END IF;
1072: rlm_core_sv.dlog(k_DEBUG,'x_forecast_designator',
1073: x_forecast_designator);
1074: rlm_core_sv.dlog(k_DEBUG,'k_MRP_FORECAST',
1075: k_MRP_FORECAST);
1076: rlm_core_sv.dlog(k_DEBUG,'rlm_core_sv.k_PS_AVAILABLE',
1077: rlm_core_sv.k_PS_AVAILABLE);
1078: END IF;
1079: --
1080: /*==============================================================================
1073: x_forecast_designator);
1074: rlm_core_sv.dlog(k_DEBUG,'k_MRP_FORECAST',
1075: k_MRP_FORECAST);
1076: rlm_core_sv.dlog(k_DEBUG,'rlm_core_sv.k_PS_AVAILABLE',
1077: rlm_core_sv.k_PS_AVAILABLE);
1078: END IF;
1079: --
1080: /*==============================================================================
1081: =======
1092: --
1093: IF (nvl(Recinfo.uom_code,'-99') <> nvl(Recinfo.primary_uom_code,'99')) THEN
1094: --
1095: IF (l_debug <> -1) THEN
1096: rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1097: rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1098: rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1099: END IF;
1100: --
1093: IF (nvl(Recinfo.uom_code,'-99') <> nvl(Recinfo.primary_uom_code,'99')) THEN
1094: --
1095: IF (l_debug <> -1) THEN
1096: rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1097: rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1098: rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1099: END IF;
1100: --
1101: Convert_UOM (Recinfo.uom_code,
1094: --
1095: IF (l_debug <> -1) THEN
1096: rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1097: rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1098: rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1099: END IF;
1100: --
1101: Convert_UOM (Recinfo.uom_code,
1102: Recinfo.primary_uom_code,
1104: Recinfo.inventory_item_id,
1105: Recinfo.organization_id);
1106: --
1107: IF (l_debug <> -1) THEN
1108: rlm_core_sv.dlog(k_DEBUG,'After conversion: Recinfo.quantity',Recinfo.quantity);
1109: END IF;
1110: --
1111: END IF;
1112: --
1156: t_forecast(index_cnt).task_id :=NULL;
1157: t_forecast(index_cnt).line_id :=NULL;
1158: --
1159: IF (l_debug <> -1) THEN
1160: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1161: t_forecast(index_cnt).source_line_id);
1162: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1163: t_forecast(index_cnt).inventory_item_id);
1164: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1158: --
1159: IF (l_debug <> -1) THEN
1160: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1161: t_forecast(index_cnt).source_line_id);
1162: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1163: t_forecast(index_cnt).inventory_item_id);
1164: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1165: t_forecast(index_cnt).forecast_designator);
1166: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1160: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1161: t_forecast(index_cnt).source_line_id);
1162: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1163: t_forecast(index_cnt).inventory_item_id);
1164: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1165: t_forecast(index_cnt).forecast_designator);
1166: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1167: t_forecast(index_cnt).organization_id);
1168: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1162: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1163: t_forecast(index_cnt).inventory_item_id);
1164: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1165: t_forecast(index_cnt).forecast_designator);
1166: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1167: t_forecast(index_cnt).organization_id);
1168: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1169: t_forecast(index_cnt).forecast_date);
1170: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1164: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1165: t_forecast(index_cnt).forecast_designator);
1166: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1167: t_forecast(index_cnt).organization_id);
1168: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1169: t_forecast(index_cnt).forecast_date);
1170: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1171: t_forecast(index_cnt).quantity);
1172: END IF;
1166: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1167: t_forecast(index_cnt).organization_id);
1168: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1169: t_forecast(index_cnt).forecast_date);
1170: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1171: t_forecast(index_cnt).quantity);
1172: END IF;
1173: --
1174: END LOOP;
1182: --
1183: IF (nvl(Recinfo.uom_code,'-99') <> nvl(Recinfo.primary_uom_code,'99')) THEN
1184: --
1185: IF (l_debug <> -1) THEN
1186: rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1187: rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1188: rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1189: END IF;
1190: --
1183: IF (nvl(Recinfo.uom_code,'-99') <> nvl(Recinfo.primary_uom_code,'99')) THEN
1184: --
1185: IF (l_debug <> -1) THEN
1186: rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1187: rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1188: rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1189: END IF;
1190: --
1191: Convert_UOM (Recinfo.uom_code,
1184: --
1185: IF (l_debug <> -1) THEN
1186: rlm_core_sv.dlog(k_DEBUG,'Recinfo.uom_code',Recinfo.uom_code);
1187: rlm_core_sv.dlog(k_DEBUG,'Recinfo.primary_uom_code',Recinfo.primary_uom_code);
1188: rlm_core_sv.dlog(k_DEBUG,'Before conversion: Recinfo.quantity',Recinfo.quantity);
1189: END IF;
1190: --
1191: Convert_UOM (Recinfo.uom_code,
1192: Recinfo.primary_uom_code,
1194: Recinfo.inventory_item_id,
1195: Recinfo.organization_id);
1196: --
1197: IF (l_debug <> -1) THEN
1198: rlm_core_sv.dlog(k_DEBUG,'After conversion: Recinfo.quantity',Recinfo.quantity);
1199: END IF;
1200: --
1201: END IF;
1202: --
1246: t_forecast(index_cnt).task_id :=NULL;
1247: t_forecast(index_cnt).line_id :=NULL;
1248: --
1249: IF (l_debug <> -1) THEN
1250: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1251: t_forecast(index_cnt).source_line_id);
1252: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1253: t_forecast(index_cnt).inventory_item_id);
1254: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1248: --
1249: IF (l_debug <> -1) THEN
1250: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1251: t_forecast(index_cnt).source_line_id);
1252: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1253: t_forecast(index_cnt).inventory_item_id);
1254: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1255: t_forecast(index_cnt).forecast_designator);
1256: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1250: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').source_line_id',
1251: t_forecast(index_cnt).source_line_id);
1252: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1253: t_forecast(index_cnt).inventory_item_id);
1254: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1255: t_forecast(index_cnt).forecast_designator);
1256: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1257: t_forecast(index_cnt).organization_id);
1258: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1252: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').inventory_item_id',
1253: t_forecast(index_cnt).inventory_item_id);
1254: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1255: t_forecast(index_cnt).forecast_designator);
1256: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1257: t_forecast(index_cnt).organization_id);
1258: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1259: t_forecast(index_cnt).forecast_date);
1260: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1254: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_designator',
1255: t_forecast(index_cnt).forecast_designator);
1256: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1257: t_forecast(index_cnt).organization_id);
1258: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1259: t_forecast(index_cnt).forecast_date);
1260: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1261: t_forecast(index_cnt).quantity);
1262: END IF;
1256: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').organization_id',
1257: t_forecast(index_cnt).organization_id);
1258: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').forecast_date',
1259: t_forecast(index_cnt).forecast_date);
1260: rlm_core_sv.dlog(k_DEBUG,'t_forecast(' || index_cnt || ').quantity',
1261: t_forecast(index_cnt).quantity);
1262: END IF;
1263: --
1264: END LOOP;
1265: --
1266: END IF;
1267: --
1268: IF (l_debug <> -1) THEN
1269: rlm_core_sv.dlog(k_DEBUG,'COUNT',index_cnt);
1270: rlm_core_sv.dpop(k_SDEBUG);
1271: END IF;
1272: --
1273: EXCEPTION
1266: END IF;
1267: --
1268: IF (l_debug <> -1) THEN
1269: rlm_core_sv.dlog(k_DEBUG,'COUNT',index_cnt);
1270: rlm_core_sv.dpop(k_SDEBUG);
1271: END IF;
1272: --
1273: EXCEPTION
1274: /* Bug 4176961 : UOM conversion */
1274: /* Bug 4176961 : UOM conversion */
1275: WHEN e_Group_Error THEN
1276: --
1277: IF (l_debug <> -1) THEN
1278: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
1279: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
1280: END IF;
1281: --
1282: raise;
1275: WHEN e_Group_Error THEN
1276: --
1277: IF (l_debug <> -1) THEN
1278: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
1279: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
1280: END IF;
1281: --
1282: raise;
1283:
1284: WHEN OTHERS THEN
1285: rlm_message_sv.sql_error ('RLM_forecast_sv.LoadForecast', x_progress);
1286: --
1287: IF (l_debug <> -1) THEN
1288: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHERS - sql error');
1289: END IF;
1290: --
1291: raise;
1292:
1312: --
1313: BEGIN
1314: --
1315: IF (l_debug <> -1) THEN
1316: rlm_core_sv.dpush(k_SDEBUG,'process_table');
1317: END IF;
1318: --
1319: v_line_error := FALSE;
1320: --
1322: --
1323: x_Progress := '020';
1324: --
1325: IF (l_debug <> -1) THEN
1326: rlm_core_sv.dlog(k_DEBUG,'process status of forecast lines',
1327: t_Forecast(v_Count).process_status);
1328: END IF;
1329: --
1330: IF t_Forecast(v_Count).process_status = 4 THEN
1332: x_Progress := '030';
1333: v_line_error := TRUE;
1334: --
1335: IF (l_debug <> -1) THEN
1336: rlm_core_sv.dlog(k_DEBUG,'RLM_FORECAST_FAILED',
1337: t_Forecast(v_Count).error_message);
1338: END IF;
1339: --
1340: rlm_message_sv.app_error(
1348: x_value1 => t_Forecast(v_Count).error_message,
1349: x_Token2 => 'QUANTITY',
1350: x_value2 => t_Forecast(v_Count).quantity,
1351: x_Token3 => 'GROUP',
1352: x_value3 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
1353: rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
1354: rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
1355: x_Token4 => 'REQ_DATE',
1356: x_value4 => t_Forecast(v_Count).forecast_date,
1349: x_Token2 => 'QUANTITY',
1350: x_value2 => t_Forecast(v_Count).quantity,
1351: x_Token3 => 'GROUP',
1352: x_value3 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
1353: rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
1354: rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
1355: x_Token4 => 'REQ_DATE',
1356: x_value4 => t_Forecast(v_Count).forecast_date,
1357: x_Token5 => 'START_DATE_TIME',
1350: x_value2 => t_Forecast(v_Count).quantity,
1351: x_Token3 => 'GROUP',
1352: x_value3 => rlm_core_sv.get_ship_from(x_Group_rec.ship_from_org_id)||'-'||
1353: rlm_core_sv.get_ship_to(x_Group_rec.ship_to_address_id)||'-'||
1354: rlm_core_sv.get_item_number(x_Group_rec.customer_item_id),
1355: x_Token4 => 'REQ_DATE',
1356: x_value4 => t_Forecast(v_Count).forecast_date,
1357: x_Token5 => 'START_DATE_TIME',
1358: x_value5 => to_date(t_Forecast(v_Count).attribute2,'YYYY/MM/DD HH24:MI:SS'),
1372: --
1373: IF v_line_error THEN
1374: --{
1375: IF (l_debug <> -1) THEN
1376: rlm_core_sv.dlog(k_DEBUG, 'At least one MRP line is in error, so fail entire group');
1377: END IF;
1378: --
1379: RAISE e_Group_error;
1380: --}
1380: --}
1381: END IF;
1382: --
1383: IF (l_debug <> -1) THEN
1384: rlm_core_sv.dpop(k_SDEBUG);
1385: END IF;
1386: --
1387: EXCEPTION
1388: --
1388: --
1389: WHEN e_Group_error THEN
1390: --
1391: IF (l_debug <> -1) THEN
1392: rlm_core_sv.dlog(k_DEBUG, 'x_Progress', x_Progress);
1393: rlm_core_sv.dpop(k_SDEBUG, 'e_Group_error');
1394: END IF;
1395: --
1396: RAISE;
1389: WHEN e_Group_error THEN
1390: --
1391: IF (l_debug <> -1) THEN
1392: rlm_core_sv.dlog(k_DEBUG, 'x_Progress', x_Progress);
1393: rlm_core_sv.dpop(k_SDEBUG, 'e_Group_error');
1394: END IF;
1395: --
1396: RAISE;
1397: --
1398: WHEN OTHERS THEN
1399: rlm_message_sv.sql_error ('RLM_forecast_sv.process_table', x_progress);
1400: --
1401: IF (l_debug <> -1) THEN
1402: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: OTHERS - sql error');
1403: END IF;
1404: --
1405: raise;
1406:
1429:
1430: BEGIN
1431: --
1432: IF (l_debug <> -1) THEN
1433: rlm_core_sv.dpush(K_SDEBUG, 'GetDesignator');
1434: rlm_core_sv.dlog(k_DEBUG, 'customer_id',x_customer_id);
1435: rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436: rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437: rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1430: BEGIN
1431: --
1432: IF (l_debug <> -1) THEN
1433: rlm_core_sv.dpush(K_SDEBUG, 'GetDesignator');
1434: rlm_core_sv.dlog(k_DEBUG, 'customer_id',x_customer_id);
1435: rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436: rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437: rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1438: rlm_core_sv.dlog(k_DEBUG, 'x_bill_site_id',x_bill_site_id);
1431: --
1432: IF (l_debug <> -1) THEN
1433: rlm_core_sv.dpush(K_SDEBUG, 'GetDesignator');
1434: rlm_core_sv.dlog(k_DEBUG, 'customer_id',x_customer_id);
1435: rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436: rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437: rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1438: rlm_core_sv.dlog(k_DEBUG, 'x_bill_site_id',x_bill_site_id);
1439: rlm_core_sv.dlog(k_DEBUG, 'x_bill_address_Id',x_bill_address_Id);
1432: IF (l_debug <> -1) THEN
1433: rlm_core_sv.dpush(K_SDEBUG, 'GetDesignator');
1434: rlm_core_sv.dlog(k_DEBUG, 'customer_id',x_customer_id);
1435: rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436: rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437: rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1438: rlm_core_sv.dlog(k_DEBUG, 'x_bill_site_id',x_bill_site_id);
1439: rlm_core_sv.dlog(k_DEBUG, 'x_bill_address_Id',x_bill_address_Id);
1440: END IF;
1433: rlm_core_sv.dpush(K_SDEBUG, 'GetDesignator');
1434: rlm_core_sv.dlog(k_DEBUG, 'customer_id',x_customer_id);
1435: rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436: rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437: rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1438: rlm_core_sv.dlog(k_DEBUG, 'x_bill_site_id',x_bill_site_id);
1439: rlm_core_sv.dlog(k_DEBUG, 'x_bill_address_Id',x_bill_address_Id);
1440: END IF;
1441:
1434: rlm_core_sv.dlog(k_DEBUG, 'customer_id',x_customer_id);
1435: rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436: rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437: rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1438: rlm_core_sv.dlog(k_DEBUG, 'x_bill_site_id',x_bill_site_id);
1439: rlm_core_sv.dlog(k_DEBUG, 'x_bill_address_Id',x_bill_address_Id);
1440: END IF;
1441:
1442: --Bugfix 8326871 Start
1435: rlm_core_sv.dlog(k_DEBUG, 'x_ship_to_customer_id',x_ship_to_customer_id);
1436: rlm_core_sv.dlog(k_DEBUG, 'ship_from_org_id',x_ShipFromOrgId);
1437: rlm_core_sv.dlog(k_DEBUG, 'x_Ship_Site_Id',x_Ship_Site_Id);
1438: rlm_core_sv.dlog(k_DEBUG, 'x_bill_site_id',x_bill_site_id);
1439: rlm_core_sv.dlog(k_DEBUG, 'x_bill_address_Id',x_bill_address_Id);
1440: END IF;
1441:
1442: --Bugfix 8326871 Start
1443:
1443:
1444: IF g_MRP_ListName IS NULL THEN
1445: fnd_profile.get('RLM_SELECTION_LIST',v_ListName);
1446: IF (l_debug <> -1) THEN
1447: rlm_core_sv.dlog(k_DEBUG,'v_ListName',v_ListName);
1448: END IF;
1449: g_MRP_ListName := v_ListName;
1450: ELSE
1451: IF (l_debug <> -1) THEN
1448: END IF;
1449: g_MRP_ListName := v_ListName;
1450: ELSE
1451: IF (l_debug <> -1) THEN
1452: rlm_core_sv.dlog(k_DEBUG,'g_MRP_ListName',g_MRP_ListName);
1453: END IF;
1454: v_ListName := g_MRP_ListName;
1455: END IF;
1456:
1455: END IF;
1456:
1457: --
1458: IF (l_debug <> -1) THEN
1459: rlm_core_sv.dlog(k_DEBUG,'Profile: RLM_SELECTION_LIST',v_ListName);
1460: END IF;
1461: --
1462: --Bugfix 8326871 End
1463: --
1472: EXCEPTION
1473: WHEN OTHERS THEN
1474: --
1475: IF (l_debug <> -1) THEN
1476: rlm_core_sv.dlog(k_DEBUG, 'error getting selection list',
1477: SUBSTR(SQLERRM,1,200));
1478: END IF;
1479: --
1480: END;
1483: v_ListName := V_ListNameTmp;
1484: END IF;
1485: --
1486: IF (l_debug <> -1) THEN
1487: rlm_core_sv.dlog(k_DEBUG,'v_ListName',v_ListName);
1488: END IF;
1489: --
1490: IF (x_bill_site_id IS NOT NULL) THEN
1491: --
1503: --
1504: v_bill_Site_Id := x_bill_site_id;
1505: --
1506: IF (l_debug <> -1) THEN
1507: rlm_core_sv.dlog(k_DEBUG, 'v_bill_address_Id',v_bill_address_Id);
1508: END IF;
1509: --
1510: ELSIF (x_bill_address_id IS NOT NULL) THEN
1511: --
1523: --
1524: v_bill_address_Id := x_bill_address_id;
1525: --
1526: IF (l_debug <> -1) THEN
1527: rlm_core_sv.dlog(k_DEBUG, 'v_bill_Site_Id',v_bill_Site_Id);
1528: END IF;
1529: --
1530: ELSE
1531: --
1532: v_bill_address_Id := null;
1533: v_bill_Site_Id := null;
1534: --
1535: IF (l_debug <> -1) THEN
1536: rlm_core_sv.dlog(k_DEBUG, 'v_bill_address_Id',v_bill_address_Id);
1537: rlm_core_sv.dlog(k_DEBUG, 'v_bill_Site_Id',v_bill_Site_Id);
1538: END IF;
1539: --
1540: END IF;
1533: v_bill_Site_Id := null;
1534: --
1535: IF (l_debug <> -1) THEN
1536: rlm_core_sv.dlog(k_DEBUG, 'v_bill_address_Id',v_bill_address_Id);
1537: rlm_core_sv.dlog(k_DEBUG, 'v_bill_Site_Id',v_bill_Site_Id);
1538: END IF;
1539: --
1540: END IF;
1541: --
1556: AND selection_list_type = 2
1557: AND selection_list_name = v_ListName);
1558: --
1559: IF (l_debug <> -1) THEN
1560: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator',SQL%ROWCOUNT);
1561: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1562: rlm_core_sv.dpop(K_SDEBUG);
1563: END IF;
1564: --
1557: AND selection_list_name = v_ListName);
1558: --
1559: IF (l_debug <> -1) THEN
1560: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator',SQL%ROWCOUNT);
1561: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1562: rlm_core_sv.dpop(K_SDEBUG);
1563: END IF;
1564: --
1565: EXCEPTION
1558: --
1559: IF (l_debug <> -1) THEN
1560: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator',SQL%ROWCOUNT);
1561: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1562: rlm_core_sv.dpop(K_SDEBUG);
1563: END IF;
1564: --
1565: EXCEPTION
1566: --
1583: AND selection_list_type = 2
1584: AND selection_list_name = v_ListName);
1585: --
1586: IF (l_debug <> -1) THEN
1587: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT/ST',SQL%ROWCOUNT);
1588: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1589: rlm_core_sv.dpop(K_SDEBUG);
1590: END IF;
1591: --
1584: AND selection_list_name = v_ListName);
1585: --
1586: IF (l_debug <> -1) THEN
1587: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT/ST',SQL%ROWCOUNT);
1588: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1589: rlm_core_sv.dpop(K_SDEBUG);
1590: END IF;
1591: --
1592: EXCEPTION
1585: --
1586: IF (l_debug <> -1) THEN
1587: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT/ST',SQL%ROWCOUNT);
1588: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1589: rlm_core_sv.dpop(K_SDEBUG);
1590: END IF;
1591: --
1592: EXCEPTION
1593: --
1594: /*2328087*/
1595: WHEN NO_DATA_FOUND THEN
1596: --
1597: IF (l_debug <> -1) THEN
1598: rlm_core_sv.dlog(k_DEBUG, 'No data found for forecast designator at ShiptoCT/ST',SQL%ROWCOUNT);
1599: END IF;
1600: --
1601: BEGIN
1602: --
1614: AND selection_list_type = 2
1615: AND selection_list_name = v_ListName);
1616: --
1617: IF (l_debug <> -1) THEN
1618: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1619: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1620: rlm_core_sv.dpop(K_SDEBUG);
1621: END IF;
1622: --
1615: AND selection_list_name = v_ListName);
1616: --
1617: IF (l_debug <> -1) THEN
1618: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1619: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1620: rlm_core_sv.dpop(K_SDEBUG);
1621: END IF;
1622: --
1623: EXCEPTION
1616: --
1617: IF (l_debug <> -1) THEN
1618: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1619: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1620: rlm_core_sv.dpop(K_SDEBUG);
1621: END IF;
1622: --
1623: EXCEPTION
1624: --
1624: --
1625: WHEN NO_DATA_FOUND THEN
1626: --
1627: IF (l_debug <> -1) THEN
1628: rlm_core_sv.dlog(k_DEBUG, 'No forecast designator for ship to
1629: customer',SQL%ROWCOUNT);
1630: END IF;
1631: --
1632: IF x_ship_to_customer_id is NULL OR
1634: --
1635: x_ForecastDesignator := NULL;
1636: --
1637: IF (l_debug <> -1) THEN
1638: rlm_core_sv.dpop(K_SDEBUG);
1639: END IF;
1640: --
1641: ELSE
1642: --
1656: AND selection_list_type = 2
1657: AND selection_list_name = v_ListName);
1658: --
1659: IF (l_debug <> -1) THEN
1660: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1661: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1662: rlm_core_sv.dpop(K_SDEBUG);
1663: END IF;
1664: --
1657: AND selection_list_name = v_ListName);
1658: --
1659: IF (l_debug <> -1) THEN
1660: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1661: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1662: rlm_core_sv.dpop(K_SDEBUG);
1663: END IF;
1664: --
1665: EXCEPTION
1658: --
1659: IF (l_debug <> -1) THEN
1660: rlm_core_sv.dlog(k_DEBUG, 'No of rows for forecast designator at CT',SQL%ROWCOUNT);
1661: rlm_core_sv.dlog(k_DEBUG, 'x_ForecastDesignator',x_ForecastDesignator);
1662: rlm_core_sv.dpop(K_SDEBUG);
1663: END IF;
1664: --
1665: EXCEPTION
1666: --
1668: --
1669: x_ForecastDesignator := NULL;
1670: --
1671: IF (l_debug <> -1) THEN
1672: rlm_core_sv.dpop(K_SDEBUG, 'No forecast Designator found for header level customer');
1673: END IF;
1674: --
1675: WHEN TOO_MANY_ROWS THEN
1676: --
1676: --
1677: x_ForecastDesignator := NULL;
1678: --
1679: IF (l_debug <> -1) THEN
1680: rlm_core_sv.dpop(K_SDEBUG, 'Too many rows found for
1681: header level customer');
1682: END IF;
1683: --
1684: END;
1689: --
1690: x_ForecastDesignator := NULL;
1691: --
1692: IF (l_debug <> -1) THEN
1693: rlm_core_sv.dpop(K_SDEBUG, 'Too many rows');
1694: END IF;
1695: --
1696: END;
1697: --
1699: --
1700: x_ForecastDesignator := NULL;
1701: --
1702: IF (l_debug <> -1) THEN
1703: rlm_core_sv.dpop(K_SDEBUG, 'Too many rows');
1704: END IF;
1705: --
1706: END;
1707: --
1709: --
1710: x_ForecastDesignator := NULL;
1711: --
1712: IF (l_debug <> -1) THEN
1713: rlm_core_sv.dpop(K_SDEBUG, 'Too many rows');
1714: END IF;
1715: --
1716: WHEN OTHERS THEN
1717: --
1717: --
1718: x_ForecastDesignator := NULL;
1719: --
1720: IF (l_debug <> -1) THEN
1721: rlm_core_sv.dpop(K_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1722: END IF;
1723: --
1724: raise;
1725: --
1746: --
1747: BEGIN
1748: --
1749: IF (l_debug <> -1) THEN
1750: rlm_core_sv.dpush(K_SDEBUG, 'EmptyForecast');
1751: END IF;
1752:
1753: --empty all the forecast lines for the designator
1754: x_designator(1).inventory_item_id := NULL;
1755: x_designator(1).forecast_designator := x_t_designator(x_t_designator.COUNT).forecast_designator;
1756: x_designator(1).organization_id:=x_t_designator(x_t_designator.COUNT).organization_id;
1757: --
1758: IF (l_debug <> -1) THEN
1759: rlm_core_sv.dlog(k_DEBUG,'before mrp_forecast_interface_pk api for REPLACE');
1760: END IF;
1761: --
1762: IF mrp_forecast_interface_pk.mrp_forecast_interface(x_forecast,
1763: x_designator) THEN
1766: x_t_designator(x_t_designator.COUNT).forecast_designator;
1767: g_designator_tab(g_designator_tab.count+1).organization_id :=
1768: x_t_designator(x_t_designator.COUNT).organization_id; --Bugfix 6817494
1769: IF (l_debug <> -1) THEN
1770: rlm_core_sv.dlog(k_DEBUG,'after mrp_forecast_interface_pk.mrp_forecast_interface api for REPLACE');
1771: rlm_core_sv.dlog(k_DEBUG,'Old forecast deleted for ', x_designator(1).forecast_designator);
1772: END IF;
1773:
1774: ELSE
1767: g_designator_tab(g_designator_tab.count+1).organization_id :=
1768: x_t_designator(x_t_designator.COUNT).organization_id; --Bugfix 6817494
1769: IF (l_debug <> -1) THEN
1770: rlm_core_sv.dlog(k_DEBUG,'after mrp_forecast_interface_pk.mrp_forecast_interface api for REPLACE');
1771: rlm_core_sv.dlog(k_DEBUG,'Old forecast deleted for ', x_designator(1).forecast_designator);
1772: END IF;
1773:
1774: ELSE
1775: --
1775: --
1776: x_progress :='060';
1777: --
1778: IF (l_debug <> -1) THEN
1779: rlm_core_sv.dlog(k_DEBUG,'MRP Forecast API Failed ');
1780: END IF;
1781: --
1782: SELECT line_id
1783: INTO v_InterfaceLineId
1804: --
1805: END IF;
1806: --
1807: IF (l_debug <> -1) THEN
1808: rlm_core_sv.dpop(K_SDEBUG);
1809: END IF;
1810: --
1811: EXCEPTION
1812: --
1812: --
1813: WHEN e_Group_Error THEN
1814: --
1815: IF (l_debug <> -1) THEN
1816: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
1817: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
1818: END IF;
1819: --
1820: raise;
1813: WHEN e_Group_Error THEN
1814: --
1815: IF (l_debug <> -1) THEN
1816: rlm_core_sv.dlog(k_DEBUG,'progress',x_Progress);
1817: rlm_core_sv.dpop(k_SDEBUG, 'GROUP ERROR');
1818: END IF;
1819: --
1820: raise;
1821: --
1821: --
1822: WHEN OTHERS THEN
1823: --
1824: IF (l_debug <> -1) THEN
1825: rlm_core_sv.dpop(K_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1826: END IF;
1827: --
1828: raise;
1829: --
1846: --
1847: BEGIN
1848: --
1849: IF (l_debug <> -1) THEN
1850: rlm_core_sv.dpush(k_SDEBUG,'ProcessReplaceAll');
1851: END IF;
1852: --
1853: -- count the no of mrp lines for the given header and status =5 /*2816086*/
1854: --
1892: --
1893: k_REPLACE_FLAG := FALSE;
1894: --
1895: IF (l_debug <> -1) THEN
1896: rlm_core_sv.dlog(k_DEBUG,'already deleted old forecast for designator', g_designator_tab(v_counter).designator);
1897: END IF;
1898: --
1899: EXIT;
1900: --
1930: --
1931: END IF; --check for v_mrp_count
1932: --
1933: IF (l_debug <> -1) THEN
1934: rlm_core_sv.dpop(k_SDEBUG,'ProcessReplaceAll');
1935: END IF;
1936: --
1937: EXCEPTION
1938: --
1938: --
1939: WHEN OTHERS THEN
1940: --
1941: IF (l_debug <> -1) THEN
1942: rlm_core_sv.dpop(K_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1943: END IF;
1944: --
1945: RAISE;
1946: --
1971: --
1972: BEGIN
1973: --
1974: IF (l_debug <> -1) THEN
1975: rlm_core_sv.dpush(k_SDEBUG,'GetTPContext');
1976: rlm_core_sv.dlog(k_DEBUG,'customer_id', x_sched_rec.customer_id);
1977: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_translator_code',
1978: x_sched_rec.ece_tp_translator_code);
1979: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_location_code_ext',
1972: BEGIN
1973: --
1974: IF (l_debug <> -1) THEN
1975: rlm_core_sv.dpush(k_SDEBUG,'GetTPContext');
1976: rlm_core_sv.dlog(k_DEBUG,'customer_id', x_sched_rec.customer_id);
1977: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_translator_code',
1978: x_sched_rec.ece_tp_translator_code);
1979: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_location_code_ext',
1980: x_sched_rec.ece_tp_location_code_ext);
1973: --
1974: IF (l_debug <> -1) THEN
1975: rlm_core_sv.dpush(k_SDEBUG,'GetTPContext');
1976: rlm_core_sv.dlog(k_DEBUG,'customer_id', x_sched_rec.customer_id);
1977: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_translator_code',
1978: x_sched_rec.ece_tp_translator_code);
1979: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_location_code_ext',
1980: x_sched_rec.ece_tp_location_code_ext);
1981: rlm_core_sv.dlog(k_DEBUG,'x_group_rec.ship_to_address_id',
1975: rlm_core_sv.dpush(k_SDEBUG,'GetTPContext');
1976: rlm_core_sv.dlog(k_DEBUG,'customer_id', x_sched_rec.customer_id);
1977: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_translator_code',
1978: x_sched_rec.ece_tp_translator_code);
1979: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_location_code_ext',
1980: x_sched_rec.ece_tp_location_code_ext);
1981: rlm_core_sv.dlog(k_DEBUG,'x_group_rec.ship_to_address_id',
1982: x_group_rec.ship_to_address_id);
1983: END IF;
1977: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_translator_code',
1978: x_sched_rec.ece_tp_translator_code);
1979: rlm_core_sv.dlog(k_DEBUG,'x_sched_rec.ece_tp_location_code_ext',
1980: x_sched_rec.ece_tp_location_code_ext);
1981: rlm_core_sv.dlog(k_DEBUG,'x_group_rec.ship_to_address_id',
1982: x_group_rec.ship_to_address_id);
1983: END IF;
1984: --
1985: IF x_sched_rec.ECE_TP_LOCATION_CODE_EXT is NOT NULL THEN
2021: --
2022: END IF;
2023: --
2024: IF (l_debug <> -1) THEN
2025: rlm_core_sv.dlog(k_DEBUG, 'customer_number', x_customer_number);
2026: rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2027: rlm_core_sv.dlog(k_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2028: rlm_core_sv.dlog(k_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2029: rlm_core_sv.dlog(k_DEBUG, 'x_tp_group_code',x_tp_group_code);
2022: END IF;
2023: --
2024: IF (l_debug <> -1) THEN
2025: rlm_core_sv.dlog(k_DEBUG, 'customer_number', x_customer_number);
2026: rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2027: rlm_core_sv.dlog(k_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2028: rlm_core_sv.dlog(k_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2029: rlm_core_sv.dlog(k_DEBUG, 'x_tp_group_code',x_tp_group_code);
2030: rlm_core_sv.dpop(k_SDEBUG);
2023: --
2024: IF (l_debug <> -1) THEN
2025: rlm_core_sv.dlog(k_DEBUG, 'customer_number', x_customer_number);
2026: rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2027: rlm_core_sv.dlog(k_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2028: rlm_core_sv.dlog(k_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2029: rlm_core_sv.dlog(k_DEBUG, 'x_tp_group_code',x_tp_group_code);
2030: rlm_core_sv.dpop(k_SDEBUG);
2031: END IF;
2024: IF (l_debug <> -1) THEN
2025: rlm_core_sv.dlog(k_DEBUG, 'customer_number', x_customer_number);
2026: rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2027: rlm_core_sv.dlog(k_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2028: rlm_core_sv.dlog(k_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2029: rlm_core_sv.dlog(k_DEBUG, 'x_tp_group_code',x_tp_group_code);
2030: rlm_core_sv.dpop(k_SDEBUG);
2031: END IF;
2032: --
2025: rlm_core_sv.dlog(k_DEBUG, 'customer_number', x_customer_number);
2026: rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2027: rlm_core_sv.dlog(k_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2028: rlm_core_sv.dlog(k_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2029: rlm_core_sv.dlog(k_DEBUG, 'x_tp_group_code',x_tp_group_code);
2030: rlm_core_sv.dpop(k_SDEBUG);
2031: END IF;
2032: --
2033: EXCEPTION
2026: rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2027: rlm_core_sv.dlog(k_DEBUG, 'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2028: rlm_core_sv.dlog(k_DEBUG, 'x_inter_ship_to_ece_locn_code', x_inter_ship_to_ece_locn_code);
2029: rlm_core_sv.dlog(k_DEBUG, 'x_tp_group_code',x_tp_group_code);
2030: rlm_core_sv.dpop(k_SDEBUG);
2031: END IF;
2032: --
2033: EXCEPTION
2034: --
2035: WHEN NO_DATA_FOUND THEN
2036: --
2037: x_customer_number := NULL;
2038: IF (l_debug <> -1) THEN
2039: rlm_core_sv.dlog(k_DEBUG, 'No data found for' , x_sched_rec.customer_id);
2040: rlm_core_sv.dpop(k_SDEBUG);
2041: END IF;
2042: --
2043: WHEN OTHERS THEN
2036: --
2037: x_customer_number := NULL;
2038: IF (l_debug <> -1) THEN
2039: rlm_core_sv.dlog(k_DEBUG, 'No data found for' , x_sched_rec.customer_id);
2040: rlm_core_sv.dpop(k_SDEBUG);
2041: END IF;
2042: --
2043: WHEN OTHERS THEN
2044: --
2043: WHEN OTHERS THEN
2044: --
2045: rlm_message_sv.sql_error('rlm_validatedemand_sv.GetTPContext',v_Progress);
2046: IF (l_debug <> -1) THEN
2047: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2048: END IF;
2049: raise;
2050:
2051: END GetTPContext;
2072: --
2073: BEGIN
2074: --
2075: IF (l_debug <> -1) THEN
2076: rlm_core_sv.dpush(k_SDEBUG,'Convert_UOM');
2077: rlm_core_sv.dlog(k_DEBUG,'from_uom',from_uom);
2078: rlm_core_sv.dlog(k_DEBUG,'to_uom',to_uom);
2079: rlm_core_sv.dlog(k_DEBUG,'quantity',quantity);
2080: rlm_core_sv.dlog(k_DEBUG,'p_item_id',p_item_id);
2073: BEGIN
2074: --
2075: IF (l_debug <> -1) THEN
2076: rlm_core_sv.dpush(k_SDEBUG,'Convert_UOM');
2077: rlm_core_sv.dlog(k_DEBUG,'from_uom',from_uom);
2078: rlm_core_sv.dlog(k_DEBUG,'to_uom',to_uom);
2079: rlm_core_sv.dlog(k_DEBUG,'quantity',quantity);
2080: rlm_core_sv.dlog(k_DEBUG,'p_item_id',p_item_id);
2081: rlm_core_sv.dlog(k_DEBUG,'p_org_id',p_org_id);
2074: --
2075: IF (l_debug <> -1) THEN
2076: rlm_core_sv.dpush(k_SDEBUG,'Convert_UOM');
2077: rlm_core_sv.dlog(k_DEBUG,'from_uom',from_uom);
2078: rlm_core_sv.dlog(k_DEBUG,'to_uom',to_uom);
2079: rlm_core_sv.dlog(k_DEBUG,'quantity',quantity);
2080: rlm_core_sv.dlog(k_DEBUG,'p_item_id',p_item_id);
2081: rlm_core_sv.dlog(k_DEBUG,'p_org_id',p_org_id);
2082: END IF;
2075: IF (l_debug <> -1) THEN
2076: rlm_core_sv.dpush(k_SDEBUG,'Convert_UOM');
2077: rlm_core_sv.dlog(k_DEBUG,'from_uom',from_uom);
2078: rlm_core_sv.dlog(k_DEBUG,'to_uom',to_uom);
2079: rlm_core_sv.dlog(k_DEBUG,'quantity',quantity);
2080: rlm_core_sv.dlog(k_DEBUG,'p_item_id',p_item_id);
2081: rlm_core_sv.dlog(k_DEBUG,'p_org_id',p_org_id);
2082: END IF;
2083: --
2076: rlm_core_sv.dpush(k_SDEBUG,'Convert_UOM');
2077: rlm_core_sv.dlog(k_DEBUG,'from_uom',from_uom);
2078: rlm_core_sv.dlog(k_DEBUG,'to_uom',to_uom);
2079: rlm_core_sv.dlog(k_DEBUG,'quantity',quantity);
2080: rlm_core_sv.dlog(k_DEBUG,'p_item_id',p_item_id);
2081: rlm_core_sv.dlog(k_DEBUG,'p_org_id',p_org_id);
2082: END IF;
2083: --
2084: IF to_uom IS NULL THEN
2077: rlm_core_sv.dlog(k_DEBUG,'from_uom',from_uom);
2078: rlm_core_sv.dlog(k_DEBUG,'to_uom',to_uom);
2079: rlm_core_sv.dlog(k_DEBUG,'quantity',quantity);
2080: rlm_core_sv.dlog(k_DEBUG,'p_item_id',p_item_id);
2081: rlm_core_sv.dlog(k_DEBUG,'p_org_id',p_org_id);
2082: END IF;
2083: --
2084: IF to_uom IS NULL THEN
2085: raise e_PrimaryCodeMissing;
2113: --
2114: quantity := result;
2115: --
2116: IF (l_debug <> -1) THEN
2117: rlm_core_sv.dlog(k_DEBUG,'result',result);
2118: rlm_core_sv.dpop(k_SDEBUG);
2119: END IF;
2120: --
2121: EXCEPTION
2114: quantity := result;
2115: --
2116: IF (l_debug <> -1) THEN
2117: rlm_core_sv.dlog(k_DEBUG,'result',result);
2118: rlm_core_sv.dpop(k_SDEBUG);
2119: END IF;
2120: --
2121: EXCEPTION
2122: --
2126: --
2127: OPEN c;
2128: FETCH c into v_item_number;
2129: --
2130: v_ShipFromOrgName := RLM_CORE_SV.get_ship_from(p_org_id);
2131: --
2132: rlm_message_sv.app_error(
2133: x_ExceptionLevel => rlm_message_sv.k_error_level,
2134: x_MessageName => 'RLM_ERROR_NO_PRIMARY_UOM',
2138: x_value2=> v_ShipFromOrgName);
2139: CLOSE c;
2140: --
2141: IF (l_debug <> -1) THEN
2142: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: RLM_ERROR_NO_PRIMARY_UOM');
2143: END IF;
2144: --
2145: EXCEPTION
2146: --
2146: --
2147: WHEN NO_DATA_FOUND THEN
2148: --
2149: IF (l_debug <> -1) THEN
2150: rlm_core_sv.dpop(K_SDEBUG, 'No data found');
2151: END IF;
2152: --
2153: END;
2154: --
2172: x_value3=> v_item_number);
2173: CLOSE c;
2174: --
2175: IF (l_debug <> -1) THEN
2176: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: RLM_UNDEF_UOM_CONVERSION');
2177: END IF;
2178: --
2179: EXCEPTION
2180: --
2180: --
2181: WHEN NO_DATA_FOUND THEN
2182: --
2183: IF (l_debug <> -1) THEN
2184: rlm_core_sv.dpop(K_SDEBUG, 'No data found');
2185: END IF;
2186: --
2187: END;
2188: --
2190: --
2191: WHEN OTHERS THEN
2192: --
2193: IF (l_debug <> -1) THEN
2194: rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2195: END IF;
2196: --
2197: raise;
2198: --