[Home] [Help]
66: for c_cat_set_id_csr in cat_set_id_csr loop
67: g_org_comp_calc_rec.category_set_id := c_cat_set_id_csr.attribute_value_code;
68: end loop;
69:
70: l_row_seq_num := wip_ws_util.get_multival_pref_seq(
71: g_pref_id_comp_short, g_pref_level_id_site, g_pref_val_mast_org_att, to_char(p_org_id));
72:
73: if(l_row_seq_num is null) then
74: x_pref_exists := 'N';
75: return;
76: end if;
77:
78: g_org_comp_calc_rec.org_id := p_org_id;
79: g_org_comp_calc_rec.shortage_calc_level := wip_ws_util.get_multival_pref_val_code(
80: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_calclevel_att);
81: g_org_comp_calc_rec.inc_expected_rcpts := wip_ws_util.get_multival_pref_val_code(
82: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_rcpts_att);
83: g_org_comp_calc_rec.inc_released_jobs := wip_ws_util.get_multival_pref_val_code(
77:
78: g_org_comp_calc_rec.org_id := p_org_id;
79: g_org_comp_calc_rec.shortage_calc_level := wip_ws_util.get_multival_pref_val_code(
80: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_calclevel_att);
81: g_org_comp_calc_rec.inc_expected_rcpts := wip_ws_util.get_multival_pref_val_code(
82: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_rcpts_att);
83: g_org_comp_calc_rec.inc_released_jobs := wip_ws_util.get_multival_pref_val_code(
84: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_release_att);
85: g_org_comp_calc_rec.inc_unreleased_jobs := wip_ws_util.get_multival_pref_val_code(
79: g_org_comp_calc_rec.shortage_calc_level := wip_ws_util.get_multival_pref_val_code(
80: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_calclevel_att);
81: g_org_comp_calc_rec.inc_expected_rcpts := wip_ws_util.get_multival_pref_val_code(
82: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_rcpts_att);
83: g_org_comp_calc_rec.inc_released_jobs := wip_ws_util.get_multival_pref_val_code(
84: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_release_att);
85: g_org_comp_calc_rec.inc_unreleased_jobs := wip_ws_util.get_multival_pref_val_code(
86: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_unreleased_att);
87: g_org_comp_calc_rec.inc_onhold_jobs := wip_ws_util.get_multival_pref_val_code(
81: g_org_comp_calc_rec.inc_expected_rcpts := wip_ws_util.get_multival_pref_val_code(
82: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_rcpts_att);
83: g_org_comp_calc_rec.inc_released_jobs := wip_ws_util.get_multival_pref_val_code(
84: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_release_att);
85: g_org_comp_calc_rec.inc_unreleased_jobs := wip_ws_util.get_multival_pref_val_code(
86: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_unreleased_att);
87: g_org_comp_calc_rec.inc_onhold_jobs := wip_ws_util.get_multival_pref_val_code(
88: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_onhold_att);
89:
83: g_org_comp_calc_rec.inc_released_jobs := wip_ws_util.get_multival_pref_val_code(
84: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_release_att);
85: g_org_comp_calc_rec.inc_unreleased_jobs := wip_ws_util.get_multival_pref_val_code(
86: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_unreleased_att);
87: g_org_comp_calc_rec.inc_onhold_jobs := wip_ws_util.get_multival_pref_val_code(
88: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_onhold_att);
89:
90: if(g_org_comp_calc_rec.inc_expected_rcpts = g_pref_val_calclevel_org) then
91: g_org_comp_calc_rec.supply_cutoff_hr := wip_ws_util.get_multival_pref_val_code(
87: g_org_comp_calc_rec.inc_onhold_jobs := wip_ws_util.get_multival_pref_val_code(
88: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_onhold_att);
89:
90: if(g_org_comp_calc_rec.inc_expected_rcpts = g_pref_val_calclevel_org) then
91: g_org_comp_calc_rec.supply_cutoff_hr := wip_ws_util.get_multival_pref_val_code(
92: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_cutoff_hr_att );
93: g_org_comp_calc_rec.supply_cutoff_min := wip_ws_util.get_multival_pref_val_code(
94: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_cutoff_min_att );
95: g_org_comp_calc_rec.supply_cutoff_time_in_sec := get_time_in_secs(g_org_comp_calc_rec.supply_cutoff_hr, g_org_comp_calc_rec.supply_cutoff_min, 0);
89:
90: if(g_org_comp_calc_rec.inc_expected_rcpts = g_pref_val_calclevel_org) then
91: g_org_comp_calc_rec.supply_cutoff_hr := wip_ws_util.get_multival_pref_val_code(
92: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_cutoff_hr_att );
93: g_org_comp_calc_rec.supply_cutoff_min := wip_ws_util.get_multival_pref_val_code(
94: g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_cutoff_min_att );
95: g_org_comp_calc_rec.supply_cutoff_time_in_sec := get_time_in_secs(g_org_comp_calc_rec.supply_cutoff_hr, g_org_comp_calc_rec.supply_cutoff_min, 0);
96: end if;
97:
100: --User can setup three types of preferences
101: --preference attribute "type", possible values are: 1(All), 2(Item), 3(category)
102: --first find the org seq number
103:
104: l_dtl_row_seq_num := wip_ws_util.get_multival_pref_seq(
105: g_pref_id_comp_short, g_pref_level_id_site, g_pref_val_dtl_org_att, to_char(p_org_id));
106:
107:
108: l_comp_calc_type := wip_ws_util.get_multival_pref_val_code(
104: l_dtl_row_seq_num := wip_ws_util.get_multival_pref_seq(
105: g_pref_id_comp_short, g_pref_level_id_site, g_pref_val_dtl_org_att, to_char(p_org_id));
106:
107:
108: l_comp_calc_type := wip_ws_util.get_multival_pref_val_code(
109: g_pref_id_comp_short, g_pref_level_id_site, l_dtl_row_seq_num, g_pref_val_inc_onhold_att);
110: */
111:
112: /*
146: l_first_work_day DATE;
147: l_last_shift_end_time DATE;
148: CURSOR shift_time_csr IS
149: select bsd.seq_num || '.' || bsd.shift_num shift_id,
150: wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) from_date,
151: wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) to_date,
152: t.shift_num,
153: bsd.seq_num,
154: wip_ws_util.get_shift_info_for_display(mp.organization_id, bsd.seq_num, t.shift_num) as display
147: l_last_shift_end_time DATE;
148: CURSOR shift_time_csr IS
149: select bsd.seq_num || '.' || bsd.shift_num shift_id,
150: wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) from_date,
151: wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) to_date,
152: t.shift_num,
153: bsd.seq_num,
154: wip_ws_util.get_shift_info_for_display(mp.organization_id, bsd.seq_num, t.shift_num) as display
155: from mtl_parameters mp, bom_shift_dates bsd,
150: wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) from_date,
151: wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) to_date,
152: t.shift_num,
153: bsd.seq_num,
154: wip_ws_util.get_shift_info_for_display(mp.organization_id, bsd.seq_num, t.shift_num) as display
155: from mtl_parameters mp, bom_shift_dates bsd,
156: (select bst.calendar_code,
157: bst.shift_num,
158: min(bst.from_time) from_time,
164: and bsd.calendar_code = t.calendar_code
165: and bsd.shift_num = t.shift_num
166: and bsd.exception_set_id = -1
167: and bsd.shift_date + t.to_time / (24*60*60) > sysdate
168: and bsd.shift_date between l_first_work_day and wip_ws_util.get_next_date(
169: wip_ws_util.get_next_date(l_first_work_day))
170: and bsd.seq_num is not null;
171:
172: BEGIN
165: and bsd.shift_num = t.shift_num
166: and bsd.exception_set_id = -1
167: and bsd.shift_date + t.to_time / (24*60*60) > sysdate
168: and bsd.shift_date between l_first_work_day and wip_ws_util.get_next_date(
169: wip_ws_util.get_next_date(l_first_work_day))
170: and bsd.seq_num is not null;
171:
172: BEGIN
173: --:1 - Org_id
171:
172: BEGIN
173: --:1 - Org_id
174: --:2 - null (code will assume sysdate)
175: l_first_work_day := wip_ws_util.get_first_workday(p_org_id, null, null);
176:
177: for c_shift_time_csr in shift_time_csr loop
178: l_last_shift_end_time := c_shift_time_csr.to_date;
179: end loop;
283: and wpv2.attribute_name = g_pref_val_dtl_org_att
284: and wpv2.attribute_value_code = to_char(p_org_id)));
285:
286: BEGIN
287: wip_ws_util.trace_log('WIPWSSHB:get_pref_comp_id:Execution cursor to get item ids');
288: for c_pref_itm_csr in pref_itm_csr loop
289: if comp_string is not null then
290: comp_string := comp_string || ',';
291: end if;
298: if(comp_string is null) then
299: comp_string := '249';
300: end if;
301: */
302: wip_ws_util.trace_log('WIPWSSHB:get_pref_comp_id: item id string='||comp_string);
303: return comp_string;
304:
305: END get_pref_comp_id;
306:
339: where msi.organization_id = p_org_id
340: and msi.inventory_item_id = p_component_id;
341:
342: BEGIN
343: wip_ws_util.trace_log('WIPWSSHB.get_subinv_component_onhand: Begin '||
344: '; p_org_id '||p_org_id||
345: '; p_subinv_code '||p_subinv_code||
346: '; p_component_id '||p_component_id);
347:
407:
408: --call to clear the in memory cache
409: inv_quantity_tree_pub.clear_quantity_cache;
410:
411: wip_ws_util.trace_log('WIPWSSHB.get_subinv_component_onhand: '||
412: '; x_msg_count '||x_msg_count||
413: '; x_msg_data '||x_msg_data||
414: '; x_qoh '||x_qoh||
415: '; x_rqoh '||x_rqoh||
510: l_temp_where VARCHAR2(4000);
511: l_all_clause VARCHAR2(240);
512:
513: BEGIN
514: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: Entered' );
515: l_job_status_clause := ' and wdj.status_type in ('|| get_pref_job_statuses() || ')';
516: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_status_clause='||l_job_status_clause );
517: l_job_type_clause := ' and wdj.job_type in ('||get_job_types() || ')';
518: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_type_clause='||l_job_type_clause );
512:
513: BEGIN
514: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: Entered' );
515: l_job_status_clause := ' and wdj.status_type in ('|| get_pref_job_statuses() || ')';
516: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_status_clause='||l_job_status_clause );
517: l_job_type_clause := ' and wdj.job_type in ('||get_job_types() || ')';
518: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_type_clause='||l_job_type_clause );
519:
520: l_sql := l_sql || 'select distinct wro.inventory_item_id, ';
514: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: Entered' );
515: l_job_status_clause := ' and wdj.status_type in ('|| get_pref_job_statuses() || ')';
516: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_status_clause='||l_job_status_clause );
517: l_job_type_clause := ' and wdj.job_type in ('||get_job_types() || ')';
518: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_job_type_clause='||l_job_type_clause );
519:
520: l_sql := l_sql || 'select distinct wro.inventory_item_id, ';
521: l_sql := l_sql || ' decode(wro.supply_subinventory, null, ';
522: l_sql := l_sql || ' (decode(msi.wip_supply_subinventory, null, wp.default_pull_supply_subinv, msi.wip_supply_subinventory)),';
608: l_sql := l_sql || l_temp_where;
609:
610: l_sql := l_sql || ' order by inventory_item_id ';
611:
612: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: l_sql='||l_sql );
613:
614: l_cursor := dbms_sql.open_cursor;
615: dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
616: dbms_sql.define_column(l_cursor, 1, l_inv_item_id);
642: LOOP
643: EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
644: dbms_sql.column_value(l_cursor, 1, l_inv_item_id);
645: dbms_sql.column_value(l_cursor, 2, l_subinv_code);
646: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: component='||l_inv_item_id||', subinv_code='||l_subinv_code );
647: if(l_inv_item_id <> l_old_inv_item_id) then
648: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: calling orgonhand for component='||l_inv_item_id );
649: l_comp_avail := get_org_component_onhand(p_org_id, l_inv_item_id);
650: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from orgcomponent onhand, component ='||l_inv_item_id||', onhand='||l_comp_avail );
644: dbms_sql.column_value(l_cursor, 1, l_inv_item_id);
645: dbms_sql.column_value(l_cursor, 2, l_subinv_code);
646: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: component='||l_inv_item_id||', subinv_code='||l_subinv_code );
647: if(l_inv_item_id <> l_old_inv_item_id) then
648: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: calling orgonhand for component='||l_inv_item_id );
649: l_comp_avail := get_org_component_onhand(p_org_id, l_inv_item_id);
650: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from orgcomponent onhand, component ='||l_inv_item_id||', onhand='||l_comp_avail );
651: insert_critical_component(p_org_id, l_inv_item_id, null, null, l_comp_avail);
652: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id||', onhand='||l_comp_avail );
646: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: component='||l_inv_item_id||', subinv_code='||l_subinv_code );
647: if(l_inv_item_id <> l_old_inv_item_id) then
648: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: calling orgonhand for component='||l_inv_item_id );
649: l_comp_avail := get_org_component_onhand(p_org_id, l_inv_item_id);
650: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from orgcomponent onhand, component ='||l_inv_item_id||', onhand='||l_comp_avail );
651: insert_critical_component(p_org_id, l_inv_item_id, null, null, l_comp_avail);
652: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id||', onhand='||l_comp_avail );
653: end if;
654:
648: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: calling orgonhand for component='||l_inv_item_id );
649: l_comp_avail := get_org_component_onhand(p_org_id, l_inv_item_id);
650: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from orgcomponent onhand, component ='||l_inv_item_id||', onhand='||l_comp_avail );
651: insert_critical_component(p_org_id, l_inv_item_id, null, null, l_comp_avail);
652: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id||', onhand='||l_comp_avail );
653: end if;
654:
655: if(g_org_comp_calc_rec.shortage_calc_level = 2) then
656: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, calling subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code);
652: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id||', onhand='||l_comp_avail );
653: end if;
654:
655: if(g_org_comp_calc_rec.shortage_calc_level = 2) then
656: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, calling subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code);
657: l_comp_avail := get_subinv_component_onhand(p_org_id, l_subinv_code, l_inv_item_id);
658: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code||', subinv onhand='||l_comp_avail);
659: insert_critical_component(p_org_id, l_inv_item_id, l_subinv_code, null, l_comp_avail);
660: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id);
654:
655: if(g_org_comp_calc_rec.shortage_calc_level = 2) then
656: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, calling subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code);
657: l_comp_avail := get_subinv_component_onhand(p_org_id, l_subinv_code, l_inv_item_id);
658: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code||', subinv onhand='||l_comp_avail);
659: insert_critical_component(p_org_id, l_inv_item_id, l_subinv_code, null, l_comp_avail);
660: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id);
661: end if;
662: l_old_inv_item_id := l_inv_item_id;
656: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, calling subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code);
657: l_comp_avail := get_subinv_component_onhand(p_org_id, l_subinv_code, l_inv_item_id);
658: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from subinv onhand, org='||p_org_id||', item ='||l_inv_item_id||', subinv='||l_subinv_code||', subinv onhand='||l_comp_avail);
659: insert_critical_component(p_org_id, l_inv_item_id, l_subinv_code, null, l_comp_avail);
660: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id);
661: end if;
662: l_old_inv_item_id := l_inv_item_id;
663: END LOOP;
664: dbms_sql.close_cursor(l_cursor);
693: l_return_status VARCHAR2(1);
694: l_return_code NUMBER;
695: i NUMBER;
696: BEGIN
697: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:Entered');
698: l_job_status_clause := 'and wdj.status_type in ('|| get_pref_job_statuses() || ')';
699: l_job_type_clause := 'and wdj.job_type in ('||get_job_types() || ')';
700:
701: l_sql := l_sql || 'SELECT wo.organization_id, ';
719: l_sql := l_sql || 'ORDER BY wo.first_unit_start_date, ';
720: l_sql := l_sql || ' wdj.scheduled_start_date, ';
721: l_sql := l_sql || ' wo.operation_seq_num ';
722:
723: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:l_sql='||l_sql);
724:
725: l_cursor := dbms_sql.open_cursor;
726: dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
727: dbms_sql.define_column(l_cursor, 1, l_org_id);
749: dbms_sql.column_value(l_cursor, 6, l_op_sch_qty);
750: dbms_sql.column_value(l_cursor, 7, l_op_start_qty);
751: dbms_sql.column_value(l_cursor, 8, l_op_open_qty);
752:
753: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:Adding job op, l_org_id='||l_org_id||',l_wip_ent_id='||l_wip_ent_id||
754: ',l_op_seq_num='||l_op_seq_num||',l_dept_id='||l_dept_id||',l_op_fusd='||l_op_fusd||',l_op_start_qty='||l_op_start_qty||
755: ',l_op_open_qty='||l_op_open_qty||',l_op_sch_qty='||l_op_sch_qty);
756:
757: g_wip_job_op_tbl(i).ORGANIZATION_ID := l_org_id;
767: dbms_sql.close_cursor(l_cursor);
768:
769: --call custom hook procedure to reorder operations if necessary
770: begin
771: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:Calling custom procedure for reordering operations');
772: wip_ws_custom.reorder_ops_for_shortage(g_wip_job_op_tbl, l_return_status, l_return_code);
773: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:back from custom procedure for reordering operations with status='||l_return_status);
774:
775: if(l_return_status <> 'S') then
769: --call custom hook procedure to reorder operations if necessary
770: begin
771: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:Calling custom procedure for reordering operations');
772: wip_ws_custom.reorder_ops_for_shortage(g_wip_job_op_tbl, l_return_status, l_return_code);
773: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_ops:back from custom procedure for reordering operations with status='||l_return_status);
774:
775: if(l_return_status <> 'S') then
776: raise fnd_api.g_exc_unexpected_error;
777: end if;
829: l_qty_open NUMBER;
830: l_yield NUMBER;
831: BEGIN
832:
833: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:Entered');
834:
835: l_sql := l_sql || 'select wro.inventory_item_id, ';
836: l_sql := l_sql || ' decode(wro.supply_subinventory, null, ';
837: l_sql := l_sql || ' (decode(msi.wip_supply_subinventory, null, wp.default_pull_supply_subinv, msi.wip_supply_subinventory)),';
857: l_sql := l_sql || ' and msi.inventory_item_id = wro.inventory_item_id ';
858:
859: /*
860: l_cat_ids := get_pref_comp_cat(p_org_id);
861: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_cat_ids='||l_cat_ids);
862: l_item_ids := get_pref_comp_id(p_org_id);
863: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_item_ids='||l_item_ids);
864:
865: if(l_cat_ids is not null) then
859: /*
860: l_cat_ids := get_pref_comp_cat(p_org_id);
861: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_cat_ids='||l_cat_ids);
862: l_item_ids := get_pref_comp_id(p_org_id);
863: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_item_ids='||l_item_ids);
864:
865: if(l_cat_ids is not null) then
866: l_cat_clause := ' and exists (select inventory_item_id ' ||
867: ' from mtl_item_categories ' ||
878: end if;
879: */
880:
881: l_cat_ids := get_pref_comp_cat(p_org_id);
882: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_cat_ids='||l_cat_ids);
883: l_item_ids := get_pref_comp_id(p_org_id);
884: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_item_ids='||l_item_ids);
885:
886: if(is_all_component_selected(p_org_id)) then
880:
881: l_cat_ids := get_pref_comp_cat(p_org_id);
882: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_cat_ids='||l_cat_ids);
883: l_item_ids := get_pref_comp_id(p_org_id);
884: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_item_ids='||l_item_ids);
885:
886: if(is_all_component_selected(p_org_id)) then
887: l_all_clause := ' 1=1 ';
888: end if;
953:
954:
955:
956:
957: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_sql='||l_sql);
958:
959: IF (g_wip_job_op_tbl.COUNT > 0) THEN
960: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:g_wip_job_op_tbl.count>0');
961: FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
956:
957: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:l_sql='||l_sql);
958:
959: IF (g_wip_job_op_tbl.COUNT > 0) THEN
960: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:g_wip_job_op_tbl.count>0');
961: FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
962: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:Entered in for loop for org_id='||g_wip_job_op_tbl(i).ORGANIZATION_ID||
963: ',wip_ent_id='||g_wip_job_op_tbl(i).WIP_ENTITY_ID||',op_seq_num='||g_wip_job_op_tbl(i).OPERATION_SEQ_NUM);
964:
958:
959: IF (g_wip_job_op_tbl.COUNT > 0) THEN
960: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:g_wip_job_op_tbl.count>0');
961: FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
962: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:Entered in for loop for org_id='||g_wip_job_op_tbl(i).ORGANIZATION_ID||
963: ',wip_ent_id='||g_wip_job_op_tbl(i).WIP_ENTITY_ID||',op_seq_num='||g_wip_job_op_tbl(i).OPERATION_SEQ_NUM);
964:
965:
966: l_cursor := dbms_sql.open_cursor;
998: dbms_sql.bind_variable(l_cursor, ':pref_val_dtl_org_att3', g_pref_val_dtl_org_att);
999:
1000: end if;
1001:
1002: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 10 - before dbms_sql.execute');
1003: l_dummy := dbms_sql.execute(l_cursor);
1004: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 20 - after dbms_sql.execute');
1005: LOOP
1006: EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
1000: end if;
1001:
1002: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 10 - before dbms_sql.execute');
1003: l_dummy := dbms_sql.execute(l_cursor);
1004: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 20 - after dbms_sql.execute');
1005: LOOP
1006: EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
1007: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 50');
1008: dbms_sql.column_value(l_cursor, 1, l_inv_item_id);
1003: l_dummy := dbms_sql.execute(l_cursor);
1004: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 20 - after dbms_sql.execute');
1005: LOOP
1006: EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
1007: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components: point 50');
1008: dbms_sql.column_value(l_cursor, 1, l_inv_item_id);
1009: dbms_sql.column_value(l_cursor, 2, l_subinv_code);
1010: dbms_sql.column_value(l_cursor, 3, l_req_qty);
1011: dbms_sql.column_value(l_cursor, 4, l_qty_issued);
1015: dbms_sql.column_value(l_cursor, 8, l_wip_supply_type);
1016: dbms_sql.column_value(l_cursor, 9, l_uom_code);
1017: dbms_sql.column_value(l_cursor, 10, l_yield);
1018:
1019: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:add_critical_component=l_inv_item_id='||l_inv_item_id||
1020: ',l_qpa='||l_qpa||',l_req_qty='||l_req_qty||',l_qty_issued='||l_qty_issued||
1021: ',l_op_open_qty='||g_wip_job_op_tbl(i).OPEN_QTY||
1022: ',l_qty_allocated='||l_qty_allocated||',l_comp_open_qty='||to_char((g_wip_job_op_tbl(i).OPEN_QTY * l_qpa) - l_qty_issued - l_qty_allocated));
1023:
1048: g_wip_job_critical_comp_tbl(j).PRIMARY_UOM_CODE := l_uom_code;
1049:
1050: --add another record for subinv if preference is set
1051: if(g_org_comp_calc_rec.shortage_calc_level = 2) then
1052: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_components:add_critical_component=l_inv_item_id='||l_inv_item_id||', l_subinv='||l_subinv_code);
1053: j := g_wip_job_critical_comp_tbl.LAST;
1054: if (j is NULL) then j:= 0; end if; j := j+1;
1055: g_wip_job_critical_comp_tbl(j).ORGANIZATION_ID := p_org_id;
1056: g_wip_job_critical_comp_tbl(j).WIP_ENTITY_ID := g_wip_job_op_tbl(i).WIP_ENTITY_ID;
1167: select organization_id, resource_id, department_id from wip_ws_critical_res_temp;
1168:
1169:
1170: BEGIN
1171: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_job_critical_resources:Entered ');
1172:
1173: IF (g_wip_job_op_tbl.COUNT > 0) THEN
1174: FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
1175: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_job_critical_resources: Check critical resources in operation ');
1171: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_job_critical_resources:Entered ');
1172:
1173: IF (g_wip_job_op_tbl.COUNT > 0) THEN
1174: FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
1175: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_job_critical_resources: Check critical resources in operation ');
1176: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Entered in operation for loop for org_id='||g_wip_job_op_tbl(i).ORGANIZATION_ID||
1177: ',wip_ent_id='||g_wip_job_op_tbl(i).WIP_ENTITY_ID||',op_seq_num='||g_wip_job_op_tbl(i).OPERATION_SEQ_NUM);
1178: prev_res_id := null;
1179: FOR c_res_req_csr in res_req_csr(p_org_id,g_wip_job_op_tbl(i).WIP_ENTITY_ID, g_wip_job_op_tbl(i).OPERATION_SEQ_NUM) LOOP
1172:
1173: IF (g_wip_job_op_tbl.COUNT > 0) THEN
1174: FOR i in g_wip_job_op_tbl.FIRST .. g_wip_job_op_tbl.LAST LOOP
1175: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_job_critical_resources: Check critical resources in operation ');
1176: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Entered in operation for loop for org_id='||g_wip_job_op_tbl(i).ORGANIZATION_ID||
1177: ',wip_ent_id='||g_wip_job_op_tbl(i).WIP_ENTITY_ID||',op_seq_num='||g_wip_job_op_tbl(i).OPERATION_SEQ_NUM);
1178: prev_res_id := null;
1179: FOR c_res_req_csr in res_req_csr(p_org_id,g_wip_job_op_tbl(i).WIP_ENTITY_ID, g_wip_job_op_tbl(i).OPERATION_SEQ_NUM) LOOP
1180: IF(c_res_req_csr.resource_id <> nvl(prev_res_id , -1)) THEN
1178: prev_res_id := null;
1179: FOR c_res_req_csr in res_req_csr(p_org_id,g_wip_job_op_tbl(i).WIP_ENTITY_ID, g_wip_job_op_tbl(i).OPERATION_SEQ_NUM) LOOP
1180: IF(c_res_req_csr.resource_id <> nvl(prev_res_id , -1)) THEN
1181: prev_res_id := c_res_req_csr.resource_id;
1182: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Entered in resource for loop for resource id='||c_res_req_csr.RESOURCE_ID||
1183: ',qty_open='||c_res_req_csr.open_quantity);
1184:
1185: j := g_wip_job_critical_res_tbl.LAST;
1186: if (j is NULL) then j:=0; end if; j:=j+1;
1202: g_wip_job_critical_res_tbl(j).REQUIRED_QTY := l_res_req;
1203: if(g_wip_job_critical_res_tbl(j).DATE_REQUIRED < sysdate) then
1204: l_req_date := sysdate;
1205: end if;
1206: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:calling wip_ws_util.retrive_first_shift');
1207: --get shift id for each resource
1208: wip_ws_util.retrieve_first_shift(
1209: p_org_id => g_wip_job_critical_res_tbl(j).ORGANIZATION_ID,
1210: p_dept_id => g_wip_job_critical_res_tbl(j).DEPARTMENT_ID,
1204: l_req_date := sysdate;
1205: end if;
1206: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:calling wip_ws_util.retrive_first_shift');
1207: --get shift id for each resource
1208: wip_ws_util.retrieve_first_shift(
1209: p_org_id => g_wip_job_critical_res_tbl(j).ORGANIZATION_ID,
1210: p_dept_id => g_wip_job_critical_res_tbl(j).DEPARTMENT_ID,
1211: p_resource_id => g_wip_job_critical_res_tbl(j).RESOURCE_ID ,
1212: p_date => l_req_date,
1216: x_shift_end_date => l_shift_end_date,
1217: x_shift_string => l_shift_string
1218: );
1219:
1220: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:back from retrieve_first_shift with shift_num='||
1221: l_shift_num||',shift_seq='||l_shift_seq);
1222:
1223: g_wip_job_critical_res_tbl(j).SHIFT_NUM := l_shift_num;
1224: g_wip_job_critical_res_tbl(j).SHIFT_SEQ := l_shift_seq;
1242: END LOOP;
1243: END LOOP;
1244: END IF;
1245:
1246: wip_ws_util.trace_log( 'Printing critical job op resources');
1247: IF (g_wip_job_critical_res_tbl.COUNT > 0) THEN
1248: FOR j in g_wip_job_critical_res_tbl.FIRST .. g_wip_job_critical_res_tbl.LAST LOOP
1249: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Critical Resource:'||
1250: 'org_id='||g_wip_job_critical_res_tbl(j).ORGANIZATION_ID ||
1245:
1246: wip_ws_util.trace_log( 'Printing critical job op resources');
1247: IF (g_wip_job_critical_res_tbl.COUNT > 0) THEN
1248: FOR j in g_wip_job_critical_res_tbl.FIRST .. g_wip_job_critical_res_tbl.LAST LOOP
1249: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Critical Resource:'||
1250: 'org_id='||g_wip_job_critical_res_tbl(j).ORGANIZATION_ID ||
1251: ',wip_ent_id='||g_wip_job_critical_res_tbl(j).WIP_ENTITY_ID ||
1252: ',op_seq_num='||g_wip_job_critical_res_tbl(j).OPERATION_SEQ_NUM ||
1253: 'res_id,='||g_wip_job_critical_res_tbl(j).RESOURCE_ID ||
1261: ',shift_seq='||g_wip_job_critical_res_tbl(j).SHIFT_SEQ);
1262: END LOOP;
1263: END IF;
1264:
1265: wip_ws_util.trace_log( 'Printing critical resources');
1266: for c_critical_res_csr in critical_res_csr loop
1267: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Critical Resource in temp table:'||
1268: 'org_id='||c_critical_res_csr.ORGANIZATION_ID ||
1269: ',res_id='||c_critical_res_csr.RESOURCE_ID ||
1263: END IF;
1264:
1265: wip_ws_util.trace_log( 'Printing critical resources');
1266: for c_critical_res_csr in critical_res_csr loop
1267: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_job_critical_resources:Critical Resource in temp table:'||
1268: 'org_id='||c_critical_res_csr.ORGANIZATION_ID ||
1269: ',res_id='||c_critical_res_csr.RESOURCE_ID ||
1270: ',dept_id='||c_critical_res_csr.DEPARTMENT_ID);
1271:
1302: l_qty NUMBER := 0;
1303: BEGIN
1304: for c_job_csr in job_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1305: l_qty := l_qty + (c_job_csr.item_qty - nvl(c_job_csr.reservation_qty,0));
1306: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_wip_supply: '||
1307: 'c_job_csr.wip_entity_id = '||c_job_csr.wip_entity_id||
1308: 'c_job_csr.item_qty = '||c_job_csr.item_qty||
1309: 'c_job_csr.reservation_qty = '||c_job_csr.reservation_qty);
1310: end loop;
1336: l_qty NUMBER := 0;
1337: BEGIN
1338: for c_flow_sched_csr in flow_sched_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1339: l_qty := c_flow_sched_csr.item_qty;
1340: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_flow_supply: '||
1341: 'flow_sched_csr.wip_entity_id = '||c_flow_sched_csr.wip_entity_id||
1342: 'flow_sched_csr.item_qty = '||c_flow_sched_csr.item_qty);
1343: end loop;
1344: if (l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1373: l_qty NUMBER := 0;
1374: BEGIN
1375: for c_wip_negreq_csr in wip_negreq_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1376: l_qty := c_wip_negreq_csr.item_qty;
1377: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_wip_negreq_supply: '||
1378: 'wip_negreq_csr.item_qty = '||c_wip_negreq_csr.item_qty);
1379: end loop;
1380: if (l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1381: return l_qty;
1424: l_qty NUMBER := 0;
1425: BEGIN
1426: for c_rep_sched_csr in rep_sched_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1427: l_qty := c_rep_sched_csr.item_qty;
1428: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_rep_sch_supply: '||
1429: 'rep_sched_csr.item_qty = '||c_rep_sched_csr.item_qty);
1430: end loop;
1431: if (l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1432: return l_qty;
1468: l_qty NUMBER := 0;
1469: BEGIN
1470: for c_po_csr in po_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1471: l_qty := l_qty + (nvl(c_po_csr.item_qty,0) - nvl(c_po_csr.reservation_qty,0));
1472: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_po_supply: '||
1473: 'po_csr.PO_HEADER_ID = '||c_po_csr.PO_HEADER_ID||
1474: 'po_csr.item_qty = '||c_po_csr.item_qty||
1475: 'po_csr.reservation_qty = '||c_po_csr.reservation_qty);
1476: end loop;
1516: l_qty NUMBER := 0;
1517: BEGIN
1518: for c_req_csr in req_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1519: l_qty := l_qty + (c_req_csr.item_qty - nvl(c_req_csr.reservation_qty,0));
1520: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_req_supply: '||
1521: 'req_csr.requisition_line_id = '||c_req_csr.requisition_line_id||
1522: 'req_csr.REQUISITION_HEADER_ID = '||c_req_csr.REQUISITION_HEADER_ID||
1523: 'req_csr.item_qty = '||c_req_csr.item_qty||
1524: 'req_csr.reservation_qty = '||c_req_csr.reservation_qty);
1564: l_qty NUMBER := 0;
1565: BEGIN
1566: for c_intransit_ship_csr in intransit_ship_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1567: l_qty := l_qty + (c_intransit_ship_csr.item_qty - nvl(c_intransit_ship_csr.reservation_qty,0));
1568: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_intransit_ship_supply: '||
1569: 'intransit_ship_csr.requisition_line_id = '||c_intransit_ship_csr.requisition_line_id||
1570: 'intransit_ship_csr.REQUISITION_HEADER_ID = '||c_intransit_ship_csr.REQUISITION_HEADER_ID||
1571: 'intransit_ship_csr.item_qty = '||c_intransit_ship_csr.item_qty||
1572: 'intransit_ship_csr.reservation_qty = '||c_intransit_ship_csr.reservation_qty);
1634: l_qty NUMBER := 0;
1635: BEGIN
1636: for c_intransit_receipt_csr in intransit_receipt_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1637: l_qty := l_qty + (c_intransit_receipt_csr.item_qty - nvl(c_intransit_receipt_csr.reservation_qty,0));
1638: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_intransit_receipt_supply: '||
1639: 'c_intransit_receipt_csr.item_qty = '||c_intransit_receipt_csr.item_qty||
1640: 'c_intransit_receipt_csr.reservation_qty = '||c_intransit_receipt_csr.reservation_qty);
1641: end loop;
1642: if(l_qty < 0 or l_qty is null) then l_qty := 0; end if;
1676: l_qty NUMBER := 0;
1677: BEGIN
1678: for c_po_rcv_csr in po_rcv_csr(p_org_id, p_inv_item_id, p_rcpt_date) loop
1679: l_qty := l_qty + (c_po_rcv_csr.item_qty - nvl(c_po_rcv_csr.reservation_qty,0));
1680: wip_ws_util.trace_log('WIP_WS_SHORTAGE:get_po_rcv_supply: '||
1681: 'c_po_rcv_csr.PO_HEADER_ID = '||c_po_rcv_csr.PO_HEADER_ID||
1682: 'c_po_rcv_csr.item_qty = '||c_po_rcv_csr.item_qty||
1683: 'c_po_rcv_csr.reservation_qty = '||c_po_rcv_csr.reservation_qty);
1684: end loop;
1706: req_supply NUMBER := 0;
1707: intransit_receipt_supply NUMBER := 0;
1708: po_rcv_supply NUMBER := 0;
1709: BEGIN
1710: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: '||
1711: 'p_org_id = '||p_org_id||
1712: 'p_inv_item_id = '||p_inv_item_id||
1713: 'p_rcpt_date = '||p_rcpt_date);
1714:
1712: 'p_inv_item_id = '||p_inv_item_id||
1713: 'p_rcpt_date = '||p_rcpt_date);
1714:
1715: wip_supply := get_wip_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1716: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: wip_supply = '||wip_supply);
1717: neg_wip_supply := get_wip_negreq_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1718: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: neg_wip_supply = '||neg_wip_supply);
1719: flow_supply := get_flow_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1720: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: flow_supply = '||flow_supply);
1714:
1715: wip_supply := get_wip_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1716: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: wip_supply = '||wip_supply);
1717: neg_wip_supply := get_wip_negreq_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1718: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: neg_wip_supply = '||neg_wip_supply);
1719: flow_supply := get_flow_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1720: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: flow_supply = '||flow_supply);
1721: rep_sch_supply := get_rep_sch_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1722: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: rep_sch_supply = '||rep_sch_supply);
1716: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: wip_supply = '||wip_supply);
1717: neg_wip_supply := get_wip_negreq_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1718: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: neg_wip_supply = '||neg_wip_supply);
1719: flow_supply := get_flow_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1720: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: flow_supply = '||flow_supply);
1721: rep_sch_supply := get_rep_sch_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1722: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: rep_sch_supply = '||rep_sch_supply);
1723: po_supply := get_po_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1724: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_supply = '||po_supply);
1718: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: neg_wip_supply = '||neg_wip_supply);
1719: flow_supply := get_flow_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1720: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: flow_supply = '||flow_supply);
1721: rep_sch_supply := get_rep_sch_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1722: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: rep_sch_supply = '||rep_sch_supply);
1723: po_supply := get_po_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1724: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_supply = '||po_supply);
1725: intransit_ship_supply := get_intransit_ship_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1726: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_ship_supply = '||intransit_ship_supply);
1720: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: flow_supply = '||flow_supply);
1721: rep_sch_supply := get_rep_sch_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1722: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: rep_sch_supply = '||rep_sch_supply);
1723: po_supply := get_po_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1724: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_supply = '||po_supply);
1725: intransit_ship_supply := get_intransit_ship_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1726: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_ship_supply = '||intransit_ship_supply);
1727: req_supply := get_req_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1728: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: req_supply = '||req_supply);
1722: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: rep_sch_supply = '||rep_sch_supply);
1723: po_supply := get_po_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1724: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_supply = '||po_supply);
1725: intransit_ship_supply := get_intransit_ship_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1726: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_ship_supply = '||intransit_ship_supply);
1727: req_supply := get_req_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1728: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: req_supply = '||req_supply);
1729: intransit_receipt_supply := get_intransit_receipt_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1730: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_receipt_supply = '||intransit_receipt_supply);
1724: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_supply = '||po_supply);
1725: intransit_ship_supply := get_intransit_ship_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1726: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_ship_supply = '||intransit_ship_supply);
1727: req_supply := get_req_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1728: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: req_supply = '||req_supply);
1729: intransit_receipt_supply := get_intransit_receipt_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1730: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_receipt_supply = '||intransit_receipt_supply);
1731: po_rcv_supply := get_po_rcv_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1732: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_rcv_supply = '||po_rcv_supply);
1726: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_ship_supply = '||intransit_ship_supply);
1727: req_supply := get_req_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1728: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: req_supply = '||req_supply);
1729: intransit_receipt_supply := get_intransit_receipt_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1730: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_receipt_supply = '||intransit_receipt_supply);
1731: po_rcv_supply := get_po_rcv_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1732: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_rcv_supply = '||po_rcv_supply);
1733:
1734: return (wip_supply + neg_wip_supply + flow_supply + rep_sch_supply + po_supply +
1728: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: req_supply = '||req_supply);
1729: intransit_receipt_supply := get_intransit_receipt_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1730: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: intransit_receipt_supply = '||intransit_receipt_supply);
1731: po_rcv_supply := get_po_rcv_supply(p_org_id, p_inv_item_id, p_rcpt_date);
1732: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts: po_rcv_supply = '||po_rcv_supply);
1733:
1734: return (wip_supply + neg_wip_supply + flow_supply + rep_sch_supply + po_supply +
1735: intransit_ship_supply + req_supply + intransit_receipt_supply + po_rcv_supply);
1736:
1749: and supply_subinventory is null;
1750: l_rcpt NUMBER;
1751:
1752: BEGIN
1753: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_expected_receipts:Entered');
1754: for c_comp in comp LOOP
1755: l_rcpt := calc_expected_receipts(p_org_id, c_comp.inventory_item_id, p_rcpt_date);
1756: if(nvl(l_rcpt, -1) < 0) then l_rcpt := 0; end if;
1757: update wip_ws_critical_comp_temp
1790: * shortage table
1791: */
1792: PROCEDURE insert_components IS
1793: BEGIN
1794: wip_ws_util.log_time('insert_components: Inserting component shortage records');
1795: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:Entered: Number of records to insert='||g_wip_job_critical_comp_tbl.COUNT);
1796: --FOR i in 1..g_wip_job_critical_comp_tbl.COUNT LOOP
1797: IF(g_wip_job_critical_comp_tbl.COUNT > 0) THEN
1798: FOR i in g_wip_job_critical_comp_tbl.FIRST..g_wip_job_critical_comp_tbl.LAST LOOP
1791: */
1792: PROCEDURE insert_components IS
1793: BEGIN
1794: wip_ws_util.log_time('insert_components: Inserting component shortage records');
1795: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:Entered: Number of records to insert='||g_wip_job_critical_comp_tbl.COUNT);
1796: --FOR i in 1..g_wip_job_critical_comp_tbl.COUNT LOOP
1797: IF(g_wip_job_critical_comp_tbl.COUNT > 0) THEN
1798: FOR i in g_wip_job_critical_comp_tbl.FIRST..g_wip_job_critical_comp_tbl.LAST LOOP
1799: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:inv_item='||g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID||
1795: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:Entered: Number of records to insert='||g_wip_job_critical_comp_tbl.COUNT);
1796: --FOR i in 1..g_wip_job_critical_comp_tbl.COUNT LOOP
1797: IF(g_wip_job_critical_comp_tbl.COUNT > 0) THEN
1798: FOR i in g_wip_job_critical_comp_tbl.FIRST..g_wip_job_critical_comp_tbl.LAST LOOP
1799: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:inv_item='||g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID||
1800: ',org_id='||g_wip_job_critical_comp_tbl(i).ORGANIZATION_ID||
1801: ',wip_entity_id='||g_wip_job_critical_comp_tbl(i).WIP_ENTITY_ID||
1802: ',operation_seq_num='||g_wip_job_critical_comp_tbl(i).OPERATION_SEQ_NUM);
1803: insert into wip_ws_comp_shortage(
1856: g_prog_run_date
1857: );
1858: END LOOP;
1859: END IF;
1860: wip_ws_util.log_time('insert_components: Done with inserting components');
1861:
1862: END insert_components;
1863:
1864:
1867: * shortage table
1868: */
1869: PROCEDURE insert_resources IS
1870: BEGIN
1871: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:Entered: Number of records to insert='||g_wip_job_critical_res_tbl.COUNT);
1872: wip_ws_util.log_time('insert_resources: Inserting resource shortage records');
1873: --FOR i in 1..g_wip_job_critical_res_tbl.COUNT LOOP
1874: IF(g_wip_job_critical_res_tbl.COUNT > 0) THEN
1875: FOR i in g_wip_job_critical_res_tbl.FIRST..g_wip_job_critical_res_tbl.LAST LOOP
1868: */
1869: PROCEDURE insert_resources IS
1870: BEGIN
1871: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:Entered: Number of records to insert='||g_wip_job_critical_res_tbl.COUNT);
1872: wip_ws_util.log_time('insert_resources: Inserting resource shortage records');
1873: --FOR i in 1..g_wip_job_critical_res_tbl.COUNT LOOP
1874: IF(g_wip_job_critical_res_tbl.COUNT > 0) THEN
1875: FOR i in g_wip_job_critical_res_tbl.FIRST..g_wip_job_critical_res_tbl.LAST LOOP
1876: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:resource'||g_wip_job_critical_res_tbl(i).RESOURCE_ID||
1872: wip_ws_util.log_time('insert_resources: Inserting resource shortage records');
1873: --FOR i in 1..g_wip_job_critical_res_tbl.COUNT LOOP
1874: IF(g_wip_job_critical_res_tbl.COUNT > 0) THEN
1875: FOR i in g_wip_job_critical_res_tbl.FIRST..g_wip_job_critical_res_tbl.LAST LOOP
1876: wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:resource'||g_wip_job_critical_res_tbl(i).RESOURCE_ID||
1877: ',org_id='||g_wip_job_critical_res_tbl(i).ORGANIZATION_ID||
1878: ',wip_entity_id='||g_wip_job_critical_res_tbl(i).WIP_ENTITY_ID||
1879: ',operation_seq_num='||g_wip_job_critical_res_tbl(i).OPERATION_SEQ_NUM);
1880:
1928: g_prog_run_date
1929: );
1930: END LOOP;
1931: END IF;
1932: wip_ws_util.log_time('insert_resources: Done with resource insertion');
1933: /*
1934: exception when others then
1935: null;
1936: */
1941: * Delete all component records from comp shortage table for a given org
1942: */
1943: PROCEDURE delete_components (p_org_id NUMBER) IS
1944: BEGIN
1945: wip_ws_util.log_time('delete_components: Starting to delete org components');
1946: delete from wip_ws_comp_shortage
1947: where organization_id = p_org_id;
1948: wip_ws_util.log_time('delete_components: Done with deleting org components');
1949: END delete_components;
1944: BEGIN
1945: wip_ws_util.log_time('delete_components: Starting to delete org components');
1946: delete from wip_ws_comp_shortage
1947: where organization_id = p_org_id;
1948: wip_ws_util.log_time('delete_components: Done with deleting org components');
1949: END delete_components;
1950:
1951:
1952: /*
1953: * Delete all resource records from res shortage table for a given org
1954: */
1955: PROCEDURE delete_resources(p_org_id NUMBER) IS
1956: BEGIN
1957: wip_ws_util.log_time('delete_resources: Starting to delete org resources');
1958: delete from wip_ws_res_shortage
1959: where organization_id = p_org_id;
1960: wip_ws_util.log_time('delete_resources: Done with deleting org resources');
1961: END delete_resources;
1956: BEGIN
1957: wip_ws_util.log_time('delete_resources: Starting to delete org resources');
1958: delete from wip_ws_res_shortage
1959: where organization_id = p_org_id;
1960: wip_ws_util.log_time('delete_resources: Done with deleting org resources');
1961: END delete_resources;
1962:
1963:
1964: /*
1966: * and populate the newly calculted data present in pl/sql tables
1967: */
1968: PROCEDURE write_db(p_org_id NUMBER) IS
1969: BEGIN
1970: wip_ws_util.log_time('write_db: Entering write_db');
1971: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Entered');
1972: delete_components(p_org_id);
1973: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_components');
1974: insert_components;
1967: */
1968: PROCEDURE write_db(p_org_id NUMBER) IS
1969: BEGIN
1970: wip_ws_util.log_time('write_db: Entering write_db');
1971: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Entered');
1972: delete_components(p_org_id);
1973: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_components');
1974: insert_components;
1975: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with insert_components');
1969: BEGIN
1970: wip_ws_util.log_time('write_db: Entering write_db');
1971: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Entered');
1972: delete_components(p_org_id);
1973: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_components');
1974: insert_components;
1975: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with insert_components');
1976: delete_resources(p_org_id);
1977: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_resources');
1971: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Entered');
1972: delete_components(p_org_id);
1973: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_components');
1974: insert_components;
1975: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with insert_components');
1976: delete_resources(p_org_id);
1977: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_resources');
1978: insert_resources;
1979:
1973: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_components');
1974: insert_components;
1975: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with insert_components');
1976: delete_resources(p_org_id);
1977: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_resources');
1978: insert_resources;
1979:
1980: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Finished');
1981: wip_ws_util.log_time('write_db: Done with write_db');
1976: delete_resources(p_org_id);
1977: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_resources');
1978: insert_resources;
1979:
1980: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Finished');
1981: wip_ws_util.log_time('write_db: Done with write_db');
1982: END write_db;
1983:
1984:
1977: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_resources');
1978: insert_resources;
1979:
1980: wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:Finished');
1981: wip_ws_util.log_time('write_db: Done with write_db');
1982: END write_db;
1983:
1984:
1985: /*
2012: and department_id = p_dept_id
2013: and resource_id = p_res_id;
2014:
2015: BEGIN
2016: wip_ws_util.log_time('calc_res_shortage: Entering calc_res_shortage');
2017: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Entered');
2018: get_job_critical_resources(p_org_id, g_period_end_time);
2019: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Done with get_job_critical_resources ');
2020:
2013: and resource_id = p_res_id;
2014:
2015: BEGIN
2016: wip_ws_util.log_time('calc_res_shortage: Entering calc_res_shortage');
2017: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Entered');
2018: get_job_critical_resources(p_org_id, g_period_end_time);
2019: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Done with get_job_critical_resources ');
2020:
2021: IF(g_wip_job_critical_res_tbl.COUNT > 0) THEN
2015: BEGIN
2016: wip_ws_util.log_time('calc_res_shortage: Entering calc_res_shortage');
2017: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Entered');
2018: get_job_critical_resources(p_org_id, g_period_end_time);
2019: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Done with get_job_critical_resources ');
2020:
2021: IF(g_wip_job_critical_res_tbl.COUNT > 0) THEN
2022: FOR i in g_wip_job_critical_res_tbl.FIRST .. g_wip_job_critical_res_tbl.LAST LOOP
2023: current_res_req_date := g_wip_job_critical_res_tbl(i).DATE_REQUIRED;
2027: if(current_res_req_date < sysdate) then
2028: current_res_req_date := sysdate;
2029: end if;
2030:
2031: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 20: Enter loop for: '||
2032: 'wip_ent_id='||g_wip_job_critical_res_tbl(i).WIP_ENTITY_ID||
2033: ',op_seq_num='||g_wip_job_critical_res_tbl(i).OPERATION_SEQ_NUM||
2034: ',dept_id='||g_wip_job_critical_res_tbl(i).DEPARTMENT_ID||
2035: ',res_id='||g_wip_job_critical_res_tbl(i).RESOURCE_ID
2041: l_res_avail_shift := c_critical_res_csr.resource_shift_num;
2042: l_res_project_avail_qty := c_critical_res_csr.projected_avail_qty;
2043: l_res_onhand_qty := c_critical_res_csr.onhand_qty;
2044:
2045: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 30: Found resource in temp table: '||
2046: 'l_res_avail_date='||l_res_avail_date||
2047: ',l_res_avail_shift='||l_res_avail_shift||
2048: ',l_res_project_avail_qty='||l_res_project_avail_qty||
2049: ',l_res_onhand_qty='||l_res_onhand_qty
2059: g_wip_job_critical_res_tbl(i).RESOURCE_ID,
2060: g_wip_job_critical_res_tbl(i).SHIFT_SEQ,
2061: g_wip_job_critical_res_tbl(i).SHIFT_NUM);
2062:
2063: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 40: resource accessed first time:'||
2064: 'l_res_onhand_qty='||l_res_onhand_qty
2065: );
2066: end if;
2067:
2070: l_res_avail_shift = g_wip_job_critical_res_tbl(i).SHIFT_NUM) then
2071: --found a job resource working in the same day and shift as critical res record
2072: l_res_onhand_qty := l_res_project_avail_qty;
2073:
2074: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 50: resource working in same day and shift as critical res found'||
2075: 'l_res_onhand_qty='||l_res_onhand_qty
2076: );
2077:
2078: else
2084: g_wip_job_critical_res_tbl(i).RESOURCE_ID,
2085: g_wip_job_critical_res_tbl(i).SHIFT_SEQ,
2086: g_wip_job_critical_res_tbl(i).SHIFT_NUM);
2087:
2088: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 60: resource working in difference day or shift as critical res found'||
2089: 'l_res_onhand_qty='||l_res_onhand_qty
2090: );
2091:
2092: end if;
2097: l_res_shortage := g_wip_job_critical_res_tbl(i).QUANTITY_OPEN - l_res_onhand_qty;
2098: end if;
2099: l_res_remain_qty := l_res_onhand_qty - g_wip_job_critical_res_tbl(i).QUANTITY_OPEN;
2100:
2101: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 70:'||
2102: ',l_res_onhand_qty='||l_res_onhand_qty||
2103: ',l_res_shortage='||l_res_shortage||
2104: ',l_res_remain_qty='||l_res_remain_qty
2105: );
2111: --g_wip_job_critical_res_tbl(i).RESOURCE_PROJ_AVAIL := l_res_remain_qty;
2112: g_wip_job_critical_res_tbl(i).RESOURCE_PROJ_AVAIL := l_res_onhand_qty;
2113: g_wip_job_critical_res_tbl(i).RESOURCE_SHORTAGE := l_res_shortage;
2114:
2115: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 80:Calling update in temp table');
2116:
2117: --update critical resource record with availability info
2118: update_res_shift_avail(p_org_id, g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
2119: g_wip_job_critical_res_tbl(i).RESOURCE_ID, trunc(current_res_req_date),
2118: update_res_shift_avail(p_org_id, g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
2119: g_wip_job_critical_res_tbl(i).RESOURCE_ID, trunc(current_res_req_date),
2120: g_wip_job_critical_res_tbl(i).SHIFT_NUM, l_res_onhand_qty, l_res_remain_qty);
2121:
2122: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 90:Done update in temp table');
2123: END LOOP;
2124: END IF;
2125: END calc_res_shortage;
2126:
2155: and nvl(supply_subinventory, 'NULL') = nvl(l_supply_subinv, 'NULL');
2156:
2157: BEGIN
2158: --this is the main procedure responsible for calculating component shortages
2159: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Entered');
2160:
2161: get_pref_critical_components (p_org_id, g_period_end_time);
2162: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_pref_critical_components');
2163: get_job_ops(p_org_id, g_period_end_time);
2158: --this is the main procedure responsible for calculating component shortages
2159: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Entered');
2160:
2161: get_pref_critical_components (p_org_id, g_period_end_time);
2162: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_pref_critical_components');
2163: get_job_ops(p_org_id, g_period_end_time);
2164: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_ops');
2165: get_job_critical_components(p_org_id, g_period_end_time);
2166: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_critical_components');
2160:
2161: get_pref_critical_components (p_org_id, g_period_end_time);
2162: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_pref_critical_components');
2163: get_job_ops(p_org_id, g_period_end_time);
2164: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_ops');
2165: get_job_critical_components(p_org_id, g_period_end_time);
2166: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_critical_components');
2167:
2168: previous_jobop_comp_start_time := null;
2162: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_pref_critical_components');
2163: get_job_ops(p_org_id, g_period_end_time);
2164: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_ops');
2165: get_job_critical_components(p_org_id, g_period_end_time);
2166: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Returned from get_job_critical_components');
2167:
2168: previous_jobop_comp_start_time := null;
2169: current_jobop_comp_start_time := null;
2170: IF(g_wip_job_critical_comp_tbl.COUNT > 0) THEN
2168: previous_jobop_comp_start_time := null;
2169: current_jobop_comp_start_time := null;
2170: IF(g_wip_job_critical_comp_tbl.COUNT > 0) THEN
2171: FOR i in g_wip_job_critical_comp_tbl.FIRST .. g_wip_job_critical_comp_tbl.LAST LOOP
2172: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:job_critical_comp_loop point1, inv_item_id='||
2173: g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID||
2174: ',date_req='||g_wip_job_critical_comp_tbl(i).DATE_REQUIRED||
2175: ',subinv='||g_wip_job_critical_comp_tbl(i).SUPPLY_SUBINVENOTRY||
2176: ',quantity_open='||g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN);
2176: ',quantity_open='||g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN);
2177:
2178: current_jobop_comp_start_time := g_wip_job_critical_comp_tbl(i).DATE_REQUIRED;
2179: if(g_org_comp_calc_rec.inc_expected_rcpts = 1) then
2180: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, inc exp receipt=1');
2181: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_date='||to_char(previous_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2182: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_time='||get_time_in_secs(previous_jobop_comp_start_time));
2183: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_date='||to_char(current_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2184: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_time='||get_time_in_secs(current_jobop_comp_start_time));
2177:
2178: current_jobop_comp_start_time := g_wip_job_critical_comp_tbl(i).DATE_REQUIRED;
2179: if(g_org_comp_calc_rec.inc_expected_rcpts = 1) then
2180: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, inc exp receipt=1');
2181: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_date='||to_char(previous_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2182: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_time='||get_time_in_secs(previous_jobop_comp_start_time));
2183: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_date='||to_char(current_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2184: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_time='||get_time_in_secs(current_jobop_comp_start_time));
2185: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, supply_cutoff_time='||g_org_comp_calc_rec.supply_cutoff_time_in_sec);
2178: current_jobop_comp_start_time := g_wip_job_critical_comp_tbl(i).DATE_REQUIRED;
2179: if(g_org_comp_calc_rec.inc_expected_rcpts = 1) then
2180: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, inc exp receipt=1');
2181: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_date='||to_char(previous_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2182: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_time='||get_time_in_secs(previous_jobop_comp_start_time));
2183: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_date='||to_char(current_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2184: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_time='||get_time_in_secs(current_jobop_comp_start_time));
2185: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, supply_cutoff_time='||g_org_comp_calc_rec.supply_cutoff_time_in_sec);
2186:
2179: if(g_org_comp_calc_rec.inc_expected_rcpts = 1) then
2180: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, inc exp receipt=1');
2181: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_date='||to_char(previous_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2182: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_time='||get_time_in_secs(previous_jobop_comp_start_time));
2183: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_date='||to_char(current_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2184: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_time='||get_time_in_secs(current_jobop_comp_start_time));
2185: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, supply_cutoff_time='||g_org_comp_calc_rec.supply_cutoff_time_in_sec);
2186:
2187: --previous job op and current job op are on same day
2180: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, inc exp receipt=1');
2181: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_date='||to_char(previous_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2182: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_time='||get_time_in_secs(previous_jobop_comp_start_time));
2183: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_date='||to_char(current_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2184: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_time='||get_time_in_secs(current_jobop_comp_start_time));
2185: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, supply_cutoff_time='||g_org_comp_calc_rec.supply_cutoff_time_in_sec);
2186:
2187: --previous job op and current job op are on same day
2188: if(trunc(previous_jobop_comp_start_time) = trunc(current_jobop_comp_start_time)) then
2181: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_date='||to_char(previous_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2182: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, previous_jobop_comp_start_time='||get_time_in_secs(previous_jobop_comp_start_time));
2183: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_date='||to_char(current_jobop_comp_start_time, 'DD-MON-YYYY HH24:MI:SS'));
2184: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, current_jobop_comp_start_time='||get_time_in_secs(current_jobop_comp_start_time));
2185: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, supply_cutoff_time='||g_org_comp_calc_rec.supply_cutoff_time_in_sec);
2186:
2187: --previous job op and current job op are on same day
2188: if(trunc(previous_jobop_comp_start_time) = trunc(current_jobop_comp_start_time)) then
2189: if(get_time_in_secs(previous_jobop_comp_start_time) < g_org_comp_calc_rec.supply_cutoff_time_in_sec AND
2187: --previous job op and current job op are on same day
2188: if(trunc(previous_jobop_comp_start_time) = trunc(current_jobop_comp_start_time)) then
2189: if(get_time_in_secs(previous_jobop_comp_start_time) < g_org_comp_calc_rec.supply_cutoff_time_in_sec AND
2190: get_time_in_secs(current_jobop_comp_start_time) >= g_org_comp_calc_rec.supply_cutoff_time_in_sec) then
2191: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, include expected receipt code called for same day');
2192: calc_expected_receipts(p_org_id, trunc(current_jobop_comp_start_time));
2193: end if;
2194: end if;
2195:
2195:
2196: --no previous job op and current job start time is past rcpt time, should happen for first jobop in list only
2197: if(previous_jobop_comp_start_time is null) then
2198: if (get_time_in_secs(current_jobop_comp_start_time) >= g_org_comp_calc_rec.supply_cutoff_time_in_sec) then
2199: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, include expected receipt code called for prev day = null');
2200: calc_expected_receipts(p_org_id, trunc(current_jobop_comp_start_time));
2201: end if;
2202: end if;
2203:
2203:
2204: --previous job op was on previous day and current job op is current day, this would be executed for first job of day only
2205: if (trunc(previous_jobop_comp_start_time) < trunc(current_jobop_comp_start_time)) then
2206: if (get_time_in_secs(current_jobop_comp_start_time) >= g_org_comp_calc_rec.supply_cutoff_time_in_sec) then
2207: wip_ws_util.trace_log('WIP_WS_SHORTAGE:ks_debug, include expected receipt code called for first job of day');
2208: calc_expected_receipts(p_org_id, trunc(current_jobop_comp_start_time));
2209: end if;
2210: end if;
2211:
2212: end if;
2213: l_inv_item_id := g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID;
2214: l_supply_subinv := g_wip_job_critical_comp_tbl(i).SUPPLY_SUBINVENOTRY;
2215:
2216: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:opening critical comp avail csr');
2217: for c_critical_comp_csr in critical_comp_csr loop
2218: l_item_project_avail_qty := c_critical_comp_csr.projected_avail_qty;
2219: l_item_onhand_qty := c_critical_comp_csr.onhand_qty;
2220: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:projected_avail='||l_item_project_avail_qty||', onhand='||l_item_onhand_qty||', rowid='||c_critical_comp_csr.rowid);
2216: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:opening critical comp avail csr');
2217: for c_critical_comp_csr in critical_comp_csr loop
2218: l_item_project_avail_qty := c_critical_comp_csr.projected_avail_qty;
2219: l_item_onhand_qty := c_critical_comp_csr.onhand_qty;
2220: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:projected_avail='||l_item_project_avail_qty||', onhand='||l_item_onhand_qty||', rowid='||c_critical_comp_csr.rowid);
2221: end loop;
2222:
2223: if(l_item_project_avail_qty < 0) then l_item_project_avail_qty := 0; end if;
2224: g_wip_job_critical_comp_tbl(i).ONHAND_QTY := l_item_onhand_qty;
2238: if(l_item_project_avail_qty < 0 ) then
2239: l_item_project_avail_qty := 0;
2240: end if;
2241: end if;
2242: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage: after consumption projected_avail='||l_item_project_avail_qty||', onhand='||l_item_onhand_qty);
2243:
2244: update wip_ws_critical_comp_temp
2245: set projected_avail_qty = l_item_project_avail_qty
2246: where organization_id = p_org_id
2249:
2250: previous_jobop_comp_start_time := current_jobop_comp_start_time;
2251: END LOOP;
2252: END IF;
2253: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Point 100, total critical comp to be inserted='||g_wip_job_critical_comp_tbl.COUNT);
2254:
2255: END calc_comp_shortage;
2256:
2257:
2280:
2281: BEGIN
2282: retcode := 0;
2283:
2284: wip_ws_util.trace_log('WIPWSSHB:calc_shortage: setting up savepoint WIP_SHORT_CALC_START');
2285: SAVEPOINT WIP_SHORT_CALC_START;
2286: wip_ws_util.trace_log('WIPWSSHB:calc_shortage: savepoint WIP_SHORT_CALC_START successful');
2287:
2288: if (g_logLevel <= wip_constants.trace_logging) then
2282: retcode := 0;
2283:
2284: wip_ws_util.trace_log('WIPWSSHB:calc_shortage: setting up savepoint WIP_SHORT_CALC_START');
2285: SAVEPOINT WIP_SHORT_CALC_START;
2286: wip_ws_util.trace_log('WIPWSSHB:calc_shortage: savepoint WIP_SHORT_CALC_START successful');
2287:
2288: if (g_logLevel <= wip_constants.trace_logging) then
2289: l_params(1).paramName := 'p_org_id';
2290: l_params(1).paramValue := p_org_id;
2295: raise fnd_api.g_exc_unexpected_error;
2296: end if;
2297: end if;
2298:
2299: l_concurrent_count := wip_ws_util.get_no_of_running_concurrent(
2300: p_program_application_id => fnd_global.prog_appl_id,
2301: p_concurrent_program_id => fnd_global.conc_program_id,
2302: p_org_id => p_org_id);
2303:
2301: p_concurrent_program_id => fnd_global.conc_program_id,
2302: p_org_id => p_org_id);
2303:
2304: if l_concurrent_count > 1 then
2305: wip_ws_util.log_for_duplicate_concurrent (
2306: p_org_id => p_org_id,
2307: p_program_name => 'Component Shortage');
2308: l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Errors encountered in calculation program, please check the log file.');
2309: return;
2308: l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Errors encountered in calculation program, please check the log file.');
2309: return;
2310: end if;
2311:
2312: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:calling get_org_comp_calc_param');
2313: get_org_comp_calc_param(p_org_id, l_pref_exists);
2314: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage: returned from get_org_comp_calc_param with '||l_pref_exists);
2315: if(l_pref_exists = 'N') then
2316: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:No Preference exists for this organization');
2310: end if;
2311:
2312: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:calling get_org_comp_calc_param');
2313: get_org_comp_calc_param(p_org_id, l_pref_exists);
2314: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage: returned from get_org_comp_calc_param with '||l_pref_exists);
2315: if(l_pref_exists = 'N') then
2316: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:No Preference exists for this organization');
2317: fnd_message.set_name('WIP','WIP_WS_SHORTAGE_NOPREF');
2318: raise FND_API.G_EXC_ERROR;
2312: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:calling get_org_comp_calc_param');
2313: get_org_comp_calc_param(p_org_id, l_pref_exists);
2314: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage: returned from get_org_comp_calc_param with '||l_pref_exists);
2315: if(l_pref_exists = 'N') then
2316: wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_shortage:No Preference exists for this organization');
2317: fnd_message.set_name('WIP','WIP_WS_SHORTAGE_NOPREF');
2318: raise FND_API.G_EXC_ERROR;
2319: end if;
2320:
2317: fnd_message.set_name('WIP','WIP_WS_SHORTAGE_NOPREF');
2318: raise FND_API.G_EXC_ERROR;
2319: end if;
2320:
2321: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage: calling get_period_end_time' );
2322: g_period_end_time := get_period_end_time(p_org_id);
2323: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage:g_period_end_time='||to_char(g_period_end_time));
2324:
2325:
2319: end if;
2320:
2321: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage: calling get_period_end_time' );
2322: g_period_end_time := get_period_end_time(p_org_id);
2323: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage:g_period_end_time='||to_char(g_period_end_time));
2324:
2325:
2326: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2327: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2322: g_period_end_time := get_period_end_time(p_org_id);
2323: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage:g_period_end_time='||to_char(g_period_end_time));
2324:
2325:
2326: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2327: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2328: calc_comp_shortage (p_org_id);
2329: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2330: wip_ws_util.trace_log('WWIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2323: wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:calc_shortage:g_period_end_time='||to_char(g_period_end_time));
2324:
2325:
2326: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2327: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2328: calc_comp_shortage (p_org_id);
2329: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2330: wip_ws_util.trace_log('WWIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2331:
2325:
2326: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2327: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2328: calc_comp_shortage (p_org_id);
2329: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2330: wip_ws_util.trace_log('WWIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2331:
2332: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2333: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2326: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2327: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_comp_shortage');
2328: calc_comp_shortage (p_org_id);
2329: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2330: wip_ws_util.trace_log('WWIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2331:
2332: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2333: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2334: calc_res_shortage (p_org_id);
2328: calc_comp_shortage (p_org_id);
2329: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2330: wip_ws_util.trace_log('WWIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2331:
2332: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2333: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2334: calc_res_shortage (p_org_id);
2335: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2336: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2329: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2330: wip_ws_util.trace_log('WWIPWSSHB:calc_shortage:Done with calc_comp_shortage');
2331:
2332: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2333: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2334: calc_res_shortage (p_org_id);
2335: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2336: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2337:
2331:
2332: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2333: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2334: calc_res_shortage (p_org_id);
2335: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2336: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2337:
2338: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling write_db');
2339: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling write_db');
2332: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2333: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling calc_res_shortage');
2334: calc_res_shortage (p_org_id);
2335: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2336: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2337:
2338: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling write_db');
2339: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling write_db');
2340: write_db (p_org_id);
2334: calc_res_shortage (p_org_id);
2335: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2336: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2337:
2338: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling write_db');
2339: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling write_db');
2340: write_db (p_org_id);
2341: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with write_db');
2342: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with write_db');
2335: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2336: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with calc_res_shortage');
2337:
2338: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling write_db');
2339: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling write_db');
2340: write_db (p_org_id);
2341: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with write_db');
2342: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with write_db');
2343: commit;
2337:
2338: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling write_db');
2339: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling write_db');
2340: write_db (p_org_id);
2341: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with write_db');
2342: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with write_db');
2343: commit;
2344: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with db commit');
2345: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with db commit');
2338: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Calling write_db');
2339: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Calling write_db');
2340: write_db (p_org_id);
2341: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with write_db');
2342: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with write_db');
2343: commit;
2344: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with db commit');
2345: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with db commit');
2346:
2340: write_db (p_org_id);
2341: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with write_db');
2342: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with write_db');
2343: commit;
2344: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with db commit');
2345: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with db commit');
2346:
2347: if (g_logLevel <= wip_constants.trace_logging) then
2348: wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',
2341: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with write_db');
2342: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with write_db');
2343: commit;
2344: wip_ws_util.log_time('WIPWSSHB:calc_shortage:Done with db commit');
2345: wip_ws_util.trace_log('WIPWSSHB:calc_shortage:Done with db commit');
2346:
2347: if (g_logLevel <= wip_constants.trace_logging) then
2348: wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',
2349: p_procReturnStatus => retcode,
2352: end if;
2353:
2354: EXCEPTION
2355: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2356: wip_ws_util.trace_log('WIPWSSHB:calc_shortage: Exception: Unexpected error');
2357: ROLLBACK TO WIP_SHORT_CALC_START;
2358: retcode := 2; -- End with error
2359: fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
2360: fnd_message.set_token('ERROR_TEXT', 'wip_ws_shortage.calc_shortage: ' || SQLERRM);
2367: end if;
2368:
2369: WHEN FND_API.G_EXC_ERROR THEN
2370: retcode := 1;
2371: wip_ws_util.trace_log('WIPWSSHB:calc_shortage: Exception: Expected Error');
2372: ROLLBACK TO WIP_SHORT_CALC_START;
2373: --bug 6756693 Get the message and write it
2374: errbuf := fnd_message.get;
2375: fnd_file.put_line(fnd_file.log, errbuf);
2381: x_returnStatus => l_returnStatus);
2382: end if;
2383:
2384: WHEN OTHERS THEN
2385: wip_ws_util.trace_log('WIPWSSHB:calc_shortage: Others Exception: '|| SQLERRM);
2386: ROLLBACK TO WIP_SHORT_CALC_START;
2387: retcode := 2; --End with error
2388: if (g_logLevel <= wip_constants.trace_logging) then
2389: wip_logger.exitPoint(p_procName => 'WIP_WS_SHORTAGE.calc_shortage',