[Home] [Help]
51: sql_stmt :=
52: 'DELETE FROM MRP_ATP_DETAILS_TEMP'||l_dynstring||
53: ' WHERE session_id = :x_session_id '||
54: ' and order_line_id in ( '||
55: ' select order_line_id from mrp_atp_schedule_temp'||l_dynstring||
56: ' where session_id = :x_session_id_1 '||
57: ' and status_flag = 1) '||
58: ' and record_type <> 3';
59:
182: IF x_dblink is not null and x_mode = REQUEST_MODE THEN
183:
184: --bug 3378648
185: --delete the old data if any; This data will exist in case of Global order promising
186: Delete mrp_atp_schedule_temp
187: where session_id = x_session_id
188: and status_flag in (1, 2, 99);
189: --and status_flag in (1, 99);
190:
191: --delete local detail data
192: delete mrp_atp_details_temp
193: where session_id = x_session_id;
194:
195: --transfer the date from source to dest mrp_atp_schedule_temp
196: MSC_ATP_UTILS.Transfer_scheduling_data(x_session_id, x_dblink, REQUEST_MODE);
197: END IF;
198: ---e_cto_rearch
199:
544: x_atp_rec.first_valid_ship_arrival_date, --bug 3328421
545: x_atp_rec.party_site_id, --2814895
546: x_atp_rec.part_of_set --4500382
547:
548: FROM mrp_atp_schedule_temp
549: WHERE session_id = x_session_id
550: AND status_flag = l_status_flag
551: AND NVL(mandatory_item_flag, 2) = 2
552: AND ORDER_LINE_ID = DECODE( x_mode, MSC_ATP_UTILS.RESULTS_MODE, ORDER_LINE_ID,
696: sql_stmt := sql_stmt || ', 2'; -- non-diagnostic for older sources
697: END IF;
698:
699: sql_stmt := sql_stmt ||
700: 'FROM mrp_atp_schedule_temp'||l_dynstring||'
701: WHERE session_id = :x_session_id';
702:
703: IF PG_DEBUG in ('Y', 'C') THEN
704: msc_sch_wb.atp_debug('get_from_temp_table: ' || 'sql_stmt ' || sql_stmt);
1193: p_err_message := substr(sqlerrm,1,100);
1194: return(INV_EXTATP_GRP.G_RETURN_ERROR);
1195: End Call_ATP_11;
1196:
1197: PROCEDURE extend_mast( mast_rec IN OUT NoCopy mrp_atp_utils.mrp_atp_schedule_temp_typ,
1198: x_ret_code OUT NoCopy varchar2,
1199: x_ret_status OUT NoCopy varchar2) IS
1200: BEGIN
1201: mast_rec.rowid_char.extend(1);
1231: END IF;
1232: END extend_mast;
1233:
1234:
1235: PROCEDURE trim_mast( mast_rec IN OUT NoCopy mrp_atp_utils.mrp_atp_schedule_temp_typ,
1236: x_ret_code OUT NoCopy varchar2,
1237: x_ret_status OUT NoCopy varchar2) IS
1238: BEGIN
1239: mast_rec.rowid_char.trim(1);
1870: l_status_flag NUMBER := 99; -- bug 2974324. Initialize l_status_flag to 99 here.
1871: l_sequence_number MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(); -- for bug 2974324.
1872: found NUMBER;
1873:
1874: mast_rec mrp_atp_utils.mrp_atp_schedule_temp_typ;
1875: mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
1876: TYPE mastcurtyp IS REF CURSOR;
1877: mast_cursor mastcurtyp;
1878: l_ret_code VARCHAR2(1);
1871: l_sequence_number MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(); -- for bug 2974324.
1872: found NUMBER;
1873:
1874: mast_rec mrp_atp_utils.mrp_atp_schedule_temp_typ;
1875: mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
1876: TYPE mastcurtyp IS REF CURSOR;
1877: mast_cursor mastcurtyp;
1878: l_ret_code VARCHAR2(1);
1879: l_ret_status VARCHAR2(100);
2739: IF PG_DEBUG in ('Y', 'C') THEN
2740: msc_sch_wb.atp_debug('Inside Update_Line_Item_Properties');
2741: msc_sch_wb.atp_debug('Update item properties');
2742: END IF;
2743: update mrp_atp_schedule_temp mast
2744: --bug 4078703: Populate atp_lead time as this lead time is required
2745: --on OM sales order lies to support misc. functionalities in inv and
2746: --other module. Here we populte it on top model line only. This value is populated
2747: -- option class and items in put_sch_data_resulst_mode procedure
2777:
2778: --3720018, this query will update old_source_organization_id and old_demand_class
2779: -- in case of atp inquiry for a scheduled line from sales order pad
2780: IF ( NVL(Action, -1) = 100 ) THEN
2781: update mrp_atp_schedule_temp mast
2782: set (mast.old_source_organization_id, mast.Old_Demand_Class )=
2783: (SELECT mast.Source_Organization_Id,
2784: NVL(mast.Old_Demand_Class, mast.demand_class)
2785: from oe_order_lines_all o
2804: msc_sch_wb.atp_debug('Inside Update_Line_Item_Properties_WS');
2805: msc_sch_wb.atp_debug('Update item properties');
2806: END IF;
2807:
2808: update mrp_atp_schedule_temp mast
2809: --bug 4078703: Populate atp_lead time as this lead time is required
2810: --on OM sales order lies to support misc. functionalities in inv and
2811: --other module. Here we populte it on top model line only. This value is populated
2812: -- option class and items in put_sch_data_resulst_mode procedure
2861:
2862: l_user_id := FND_GLOBAL.USER_ID;
2863: l_count := p_atp_rec.inventory_item_id.count;
2864: --- Delete Old Data
2865: Delete from mrp_atp_schedule_temp where session_id = p_session_id
2866: --bug 3378648: delete only ATP relevent data
2867: and status_flag in (1,2, 99);
2868:
2869: IF PG_DEBUG in ('Y', 'C') THEN
2886: END IF;
2887:
2888:
2889: FORALL j in 1..l_count
2890: INSERT INTO mrp_atp_schedule_temp
2891: (
2892: mdi_rowid,
2893: session_id,
2894: scenario_id,
3131: END IF;
3132:
3133: IF MSC_ATP_PVT.G_CALLING_MODULE <> 724 THEN
3134: /*
3135: update mrp_atp_schedule_temp mast
3136: set (atp_flag, atp_components_flag, bom_item_type, pick_components_flag, fixed_lt, variable_lt) =
3137: (Select msi.atp_flag,
3138: decode(MSC_ATP_PVT.G_INV_CTP, 5,
3139: --IF ATP flag for PTO model/ATO model is other than 'N' then we still go to destination
3175: msc_sch_wb.atp_debug('Check if OE flag has been turned on or not');
3176: END IF;
3177: select count(*)
3178: into l_count
3179: from mrp_atp_schedule_temp mast
3180: where mast.session_id = p_session_id
3181: --bug 3378648
3182: and status_flag = 99
3183: and mast.OE_FLAG = 'Y';
3187: END IF;
3188:
3189: IF l_count > 0 then
3190:
3191: update mrp_atp_schedule_temp mast
3192: set OE_FLAG =
3193: (Select decode(MSC_ATP_PVT.G_INV_CTP, 5, mast.OE_FLAG,
3194: decode( prha.interface_source_code, 'MRP', 'Y', 'MSC', 'Y', 'N'))
3195: from po_requisition_headers_all prha
3238: l_status_flag NUMBER := 99; -- bug 2974324. Initialize l_status_flag to 99 here.
3239: l_sequence_number MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(); -- for bug 2974324.
3240: found NUMBER;
3241:
3242: mast_rec mrp_atp_utils.mrp_atp_schedule_temp_typ;
3243: mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
3244: TYPE mastcurtyp IS REF CURSOR;
3245: mast_cursor mastcurtyp;
3246: l_ret_code VARCHAR2(1);
3239: l_sequence_number MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(); -- for bug 2974324.
3240: found NUMBER;
3241:
3242: mast_rec mrp_atp_utils.mrp_atp_schedule_temp_typ;
3243: mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
3244: TYPE mastcurtyp IS REF CURSOR;
3245: mast_cursor mastcurtyp;
3246: l_ret_code VARCHAR2(1);
3247: l_ret_status VARCHAR2(1000);
3285:
3286: FORALL j in 1..l_count
3287: ---bug 3295956: Merge two update sqls in 1. Update ship method on component lines from
3288: -- model line. Cascade info to components based on value of cascade_model_info_to_comp attribute
3289: UPDATE MRP_ATP_SCHEDULE_TEMP
3290: SET
3291:
3292: scenario_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3293: NVL(p_atp_rec.scenario_id(j), -1), scenario_id),
3420: msc_sch_wb.atp_debug('Number of included items := ' || l_atp_rec.inventory_item_id.count);
3421: END IF;
3422: l_count := l_atp_rec.inventory_item_id.count;
3423: FORALL j in 1..l_count
3424: INSERT INTO mrp_atp_schedule_temp
3425: (
3426: mdi_rowid,
3427: session_id,
3428: scenario_id,
3591: msc_sch_wb.atp_debug('Model is present, update model component data');
3592: END IF;
3593: --bug 3295956: cascade ship method and delivery lead time from model to components
3594: -- Cascade info from model to components based on cascade_model_info_to_comp attribute. This is for istore.
3595: /* Update mrp_atp_schedule_temp mast_1
3596: set (scheduled_ship_date, end_pegging_id, scheduled_arrival_date, status_flag,
3597: group_ship_date, group_arrival_date, plan_id, ship_method, delivery_lead_time,
3598: source_organization_id, error_code) =
3599: (select Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, scheduled_ship_date, null),
3606: Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, ship_method, null),
3607: Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, delivery_lead_time, null),
3608: Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, source_organization_id, null),
3609: decode(error_code, 150, 0, 61, 0, 0, 0, MSC_ATP_PVT.GROUPEL_ERROR)
3610: from mrp_atp_schedule_temp mast_2 where
3611: mast_2.session_id = p_session_id and
3612: mast_2.order_line_id = mast_1.ato_model_line_id and
3613: mast_2.source_organization_id = NVL(mast_1.source_organization_id, mast_2.source_organization_id)
3614: )
3622:
3623: ---update plan_id on msc_cto_bom and msc_cto_sources for 24x7
3624: update msc_cto_bom mcb
3625: set plan_id = (select plan_id
3626: from mrp_atp_schedule_temp mast
3627: where mast.session_id = p_session_id
3628: and mast.order_line_id = mcb.line_id
3629: )
3630: where mcb.session_id = p_session_id;
3634: END IF;
3635:
3636: update msc_cto_sources mcs
3637: set plan_id = (select plan_id
3638: from mrp_atp_schedule_temp mast
3639: where mast.session_id = p_session_id
3640: and mast.order_line_id = mcs.line_id
3641: )
3642: where mcs.session_id = p_session_id;
3698: /* bug 3378649: delete any data locally
3699: --delete any old data
3700: IF p_mode = RESULTS_MODE THEN
3701: l_sql_stmt :=
3702: 'DELETE FROM MRP_ATP_SCHEDULE_TEMP'||L_source_DBLINK||
3703: ' WHERE session_id = :p_session_id '||
3704: ' and status_flag in (1, 99, 2) ';
3705:
3706: EXECUTE IMMEDIATE l_sql_stmt USING p_session_id;
3708: IF PG_DEBUG in ('Y', 'C') THEN
3709: msc_sch_wb.atp_debug('deleting old data in local table');
3710: END IF;
3711:
3712: DELETE FROM MRP_ATP_SCHEDULE_TEMP
3713: WHERE session_id = p_session_id
3714: and status_flag in (1, 99, 2);
3715: END IF;
3716: */
3722: msc_sch_wb.atp_debug('l_tnsfer_sts_flag := ' || l_tnsfer_sts_flag);
3723: END IF;
3724:
3725: l_sql_stmt :=
3726: 'Insert into mrp_atp_schedule_temp' || L_source_DBLINK ||
3727: ' (mdi_rowid,
3728: session_id,
3729: scenario_id,
3730: sr_instance_id,
3974: part_of_set --4500382
3975: ';
3976: END IF;
3977:
3978: l_sql_stmt := l_sql_stmt || ' from MRP_ATP_SCHEDULE_TEMP' || l_dest_dblink ||
3979: ' where session_id = :p_session_id
3980: and status_flag = ' || l_tnsfer_sts_flag ;
3981:
3982: