[Home] [Help]
3155:
3156: FND_MSG_PUB.Initialize;
3157:
3158: --start bug 3783650: TZ BOM Calendar bug 3832684
3159: IF flm_timezone.g_enabled THEN
3160: --fix bug#3170105
3161: l_first_bucket_client := flm_timezone.server_to_client(p_first_bucket_date);
3162: l_first_bucket_client := trunc(l_first_bucket_client);
3163: --end of fix bug#3170105
3157:
3158: --start bug 3783650: TZ BOM Calendar bug 3832684
3159: IF flm_timezone.g_enabled THEN
3160: --fix bug#3170105
3161: l_first_bucket_client := flm_timezone.server_to_client(p_first_bucket_date);
3162: l_first_bucket_client := trunc(l_first_bucket_client);
3163: --end of fix bug#3170105
3164: ELSE
3165: l_first_bucket_client := trunc(p_first_bucket_date);
3457:
3458: BEGIN
3459:
3460: --fix bug#3170105
3461: l_start_date := flm_timezone.client_to_server(
3462: fnd_date.canonical_to_date(p_start_date));
3463: l_end_date := flm_timezone.client_to_server(
3464: fnd_date.canonical_to_date(p_end_date));
3465: --end of fix bug#3170105
3459:
3460: --fix bug#3170105
3461: l_start_date := flm_timezone.client_to_server(
3462: fnd_date.canonical_to_date(p_start_date));
3463: l_end_date := flm_timezone.client_to_server(
3464: fnd_date.canonical_to_date(p_end_date));
3465: --end of fix bug#3170105
3466:
3467: -- Print report parameters
3940: AND scheduled_completion_date --fix bug#3170105
3941: BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60)
3942: UNION
3943: SELECT 0,
3944: flm_timezone.calendar_to_server(calendar_date), --TZ Bug 3832684
3945: 0,
3946: 0
3947: FROM mtl_parameters mp,
3948: bom_calendar_dates bom
3949: WHERE mp.organization_id = p_organization_id
3950: AND mp.calendar_exception_set_id = bom.exception_set_id
3951: AND mp.calendar_code = bom.calendar_code
3952: AND bom.seq_num IS NOT NULL
3953: AND bom.calendar_date between flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3954: AND flm_timezone.server_to_calendar(p_to_end_date)
3955: AND bom.calendar_date NOT IN (select flm_timezone.server_to_calendar(scheduled_completion_date)
3956: FROM wip_flow_schedules
3957: WHERE organization_id = p_organization_id
3950: AND mp.calendar_exception_set_id = bom.exception_set_id
3951: AND mp.calendar_code = bom.calendar_code
3952: AND bom.seq_num IS NOT NULL
3953: AND bom.calendar_date between flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3954: AND flm_timezone.server_to_calendar(p_to_end_date)
3955: AND bom.calendar_date NOT IN (select flm_timezone.server_to_calendar(scheduled_completion_date)
3956: FROM wip_flow_schedules
3957: WHERE organization_id = p_organization_id
3958: AND line_id = p_line_id
3951: AND mp.calendar_code = bom.calendar_code
3952: AND bom.seq_num IS NOT NULL
3953: AND bom.calendar_date between flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3954: AND flm_timezone.server_to_calendar(p_to_end_date)
3955: AND bom.calendar_date NOT IN (select flm_timezone.server_to_calendar(scheduled_completion_date)
3956: FROM wip_flow_schedules
3957: WHERE organization_id = p_organization_id
3958: AND line_id = p_line_id
3959: AND primary_item_id = p_item_id
3970: bom_calendar_dates bom
3971: WHERE mp.calendar_exception_set_id = bom.exception_set_id
3972: AND mp.calendar_code = bom.calendar_code
3973: AND mp.organization_id = p_organization_id
3974: AND bom.calendar_date BETWEEN flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3975: AND flm_timezone.server_to_calendar(p_to_end_date)
3976: AND ((bom.seq_num IS NOT NULL) OR
3977: (bom.calendar_date IN (SELECT flm_timezone.server_to_calendar(scheduled_completion_date)
3978: FROM wip_flow_schedules
3971: WHERE mp.calendar_exception_set_id = bom.exception_set_id
3972: AND mp.calendar_code = bom.calendar_code
3973: AND mp.organization_id = p_organization_id
3974: AND bom.calendar_date BETWEEN flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3975: AND flm_timezone.server_to_calendar(p_to_end_date)
3976: AND ((bom.seq_num IS NOT NULL) OR
3977: (bom.calendar_date IN (SELECT flm_timezone.server_to_calendar(scheduled_completion_date)
3978: FROM wip_flow_schedules
3979: WHERE organization_id = p_organization_id
3973: AND mp.organization_id = p_organization_id
3974: AND bom.calendar_date BETWEEN flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3975: AND flm_timezone.server_to_calendar(p_to_end_date)
3976: AND ((bom.seq_num IS NOT NULL) OR
3977: (bom.calendar_date IN (SELECT flm_timezone.server_to_calendar(scheduled_completion_date)
3978: FROM wip_flow_schedules
3979: WHERE organization_id = p_organization_id
3980: AND line_id = p_line_id
3981: AND primary_item_id = p_item_id
3979: WHERE organization_id = p_organization_id
3980: AND line_id = p_line_id
3981: AND primary_item_id = p_item_id
3982: AND scheduled_completion_date BETWEEN
3983: flm_timezone.calendar_to_server(bom.calendar_date)
3984: AND flm_timezone.calendar_to_server(bom.calendar_date)+1-1/(24*60*60)
3985: AND demand_source_header_id IS NULL)));
3986:
3987: CURSOR C5(p_line_id NUMBER, p_item_id NUMBER,
3980: AND line_id = p_line_id
3981: AND primary_item_id = p_item_id
3982: AND scheduled_completion_date BETWEEN
3983: flm_timezone.calendar_to_server(bom.calendar_date)
3984: AND flm_timezone.calendar_to_server(bom.calendar_date)+1-1/(24*60*60)
3985: AND demand_source_header_id IS NULL)));
3986:
3987: CURSOR C5(p_line_id NUMBER, p_item_id NUMBER,
3988: p_to_start_date DATE, p_to_end_date DATE) --fix bug#3170105
4081: AND build_sequence is NOT NULL
4082: AND (ROLL_FORWARDED_FLAG <> G_INTERMEDIATE_ROLL_FORWARDED OR
4083: ROLL_FORWARDED_FLAG IS NULL) /*Bug 3019639*/
4084: /** Forward ported bug 3055939 */
4085: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_target_date) --fix bug#3170105
4086: AND scheduled_completion_date < flm_timezone.client00_in_server(p_target_date+1); --fix bug#3170105
4087:
4088: CURSOR Count_Orig_Schedules (p_line_id NUMBER,
4089: p_from_start_date DATE, p_from_end_date DATE) --fix bug#3170105)
4082: AND (ROLL_FORWARDED_FLAG <> G_INTERMEDIATE_ROLL_FORWARDED OR
4083: ROLL_FORWARDED_FLAG IS NULL) /*Bug 3019639*/
4084: /** Forward ported bug 3055939 */
4085: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_target_date) --fix bug#3170105
4086: AND scheduled_completion_date < flm_timezone.client00_in_server(p_target_date+1); --fix bug#3170105
4087:
4088: CURSOR Count_Orig_Schedules (p_line_id NUMBER,
4089: p_from_start_date DATE, p_from_end_date DATE) --fix bug#3170105)
4090: IS
4123: FROM wip_flow_schedules
4124: WHERE organization_id = p_organization_id
4125: AND line_id = p_line_id
4126: /** Forward ported bug 3055939 */
4127: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4128: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4129:
4130: CURSOR Check_Schedule_Distinct_Type
4131: (p_line_id NUMBER ,p_scheduled_completion_date DATE)
4124: WHERE organization_id = p_organization_id
4125: AND line_id = p_line_id
4126: /** Forward ported bug 3055939 */
4127: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4128: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4129:
4130: CURSOR Check_Schedule_Distinct_Type
4131: (p_line_id NUMBER ,p_scheduled_completion_date DATE)
4132: IS
4134: FROM wip_flow_schedules
4135: WHERE organization_id = p_organization_id
4136: AND line_id = p_line_id
4137: /** Forward ported bug 3055939 */
4138: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4139: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4140:
4141: CURSOR Line_Details(p_line_id NUMBER)
4142: IS SELECT START_TIME,MAXIMUM_RATE FROM wip_lines
4135: WHERE organization_id = p_organization_id
4136: AND line_id = p_line_id
4137: /** Forward ported bug 3055939 */
4138: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4139: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4140:
4141: CURSOR Line_Details(p_line_id NUMBER)
4142: IS SELECT START_TIME,MAXIMUM_RATE FROM wip_lines
4143: WHERE organization_id = p_organization_id
4150: FROM wip_flow_schedules
4151: WHERE organization_id = p_organization_id
4152: AND line_id = p_line_id
4153: /** Forward ported bug 3055939 */
4154: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4155: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4156:
4157: l_type_count NUMBER:=0;
4158: l_distinct_type NUMBER:=0;
4151: WHERE organization_id = p_organization_id
4152: AND line_id = p_line_id
4153: /** Forward ported bug 3055939 */
4154: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4155: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4156:
4157: l_type_count NUMBER:=0;
4158: l_distinct_type NUMBER:=0;
4159: l_start_time NUMBER:=0;
4194: --TZ 3832684: Calculate completion date in Client TZ.
4195: --l_schedule_date and p_completion_date should be in Client00 time.
4196: --If p_completion_date is not in Client00 time, which means that there are
4197: --FS in the date, then l_time_flag=0 and thus, will not get into this IF flow.
4198: if flm_timezone.g_enabled then
4199: l_start_time := flm_timezone.g_client_start_time;
4200: l_schedule_date := flm_timezone.server_to_client(p_scheduled_completion_date);
4201: else
4202: l_schedule_date := p_scheduled_completion_date; --fix bug#3170105
4195: --l_schedule_date and p_completion_date should be in Client00 time.
4196: --If p_completion_date is not in Client00 time, which means that there are
4197: --FS in the date, then l_time_flag=0 and thus, will not get into this IF flow.
4198: if flm_timezone.g_enabled then
4199: l_start_time := flm_timezone.g_client_start_time;
4200: l_schedule_date := flm_timezone.server_to_client(p_scheduled_completion_date);
4201: else
4202: l_schedule_date := p_scheduled_completion_date; --fix bug#3170105
4203: end if;
4196: --If p_completion_date is not in Client00 time, which means that there are
4197: --FS in the date, then l_time_flag=0 and thus, will not get into this IF flow.
4198: if flm_timezone.g_enabled then
4199: l_start_time := flm_timezone.g_client_start_time;
4200: l_schedule_date := flm_timezone.server_to_client(p_scheduled_completion_date);
4201: else
4202: l_schedule_date := p_scheduled_completion_date; --fix bug#3170105
4203: end if;
4204:
4206: OPEN Check_Last_Schedule(p_line_id,p_scheduled_completion_date); --fix bug#3170105
4207: FETCH Check_Last_Schedule INTO l_last_schedule_date;
4208:
4209: --TZ 3832684: calculate completion date in Client TZ
4210: if flm_timezone.g_enabled then
4211: l_last_schedule_date := flm_timezone.server_to_client(l_last_schedule_date);
4212: end if;
4213:
4214: --TZ 3832684: better way to get the seconds in Client time
4207: FETCH Check_Last_Schedule INTO l_last_schedule_date;
4208:
4209: --TZ 3832684: calculate completion date in Client TZ
4210: if flm_timezone.g_enabled then
4211: l_last_schedule_date := flm_timezone.server_to_client(l_last_schedule_date);
4212: end if;
4213:
4214: --TZ 3832684: better way to get the seconds in Client time
4215: l_start_time := to_char(l_last_schedule_date,'SSSSS');
4228: MRP_UTIL.MRP_LOG('the client date after :'||to_char(l_schedule_date,'DD-MON-YYYY HH24:MI:SS'));
4229: --end of fix bug#3170105
4230:
4231: --TZ 3832684: conversion back to server to be passed back to caller
4232: if flm_timezone.g_enabled then
4233: l_schedule_date := flm_timezone.client_to_server(l_schedule_date);
4234: MRP_UTIL.MRP_LOG('the datetime after conv :'||to_char(l_schedule_date,'DD-MON-YY HH24:MI:SS'));
4235: end if;
4236:
4229: --end of fix bug#3170105
4230:
4231: --TZ 3832684: conversion back to server to be passed back to caller
4232: if flm_timezone.g_enabled then
4233: l_schedule_date := flm_timezone.client_to_server(l_schedule_date);
4234: MRP_UTIL.MRP_LOG('the datetime after conv :'||to_char(l_schedule_date,'DD-MON-YY HH24:MI:SS'));
4235: end if;
4236:
4237: CLOSE Line_Details;
4342: ' mtl_system_items_kfv msi, '||
4343: ' wip_flow_schedules fs ' ||
4344: ' WHERE fs.organization_id = to_char( :organization_id) ' ||
4345: l_where_clause ||
4346: ' AND fs.scheduled_completion_date < flm_timezone.sysdate00_in_server' ||
4347: ' and line.line_id = fs.line_id ' ||
4348: ' and line.organization_id = fs.organization_id '||
4349: ' and nvl(fs.status ,0) = 1 '||
4350: l_cat_sql ||
4851: l_log_message VARCHAR2(2000);
4852: BEGIN
4853:
4854: UPDATE WIP_FLOW_SCHEDULES SET
4855: status = 2 , date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
4856: WHERE wip_entity_id = p_wip_entity_id
4857: AND organization_id = p_organization_id;
4858:
4859: return TRUE;
4895: l_select VARCHAR2(100);
4896: BEGIN
4897:
4898: UPDATE WIP_FLOW_SCHEDULES SET
4899: status = 2 , date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
4900: WHERE schedule_number = p_schedule_number
4901: AND organization_id = p_organization_id;
4902:
4903: return TRUE;
5140: fs1.line_id,
5141: fs1.primary_item_id,
5142: null,
5143: --fs1.schedule_group_id,
5144: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5145: sum(fs1.planned_quantity),
5146: sum(fs1.quantity_completed),
5147: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5148: +floor(p_to_start_date-p_from_start_date),
5143: --fs1.schedule_group_id,
5144: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5145: sum(fs1.planned_quantity),
5146: sum(fs1.quantity_completed),
5147: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5148: +floor(p_to_start_date-p_from_start_date),
5149: NULL,
5150: 0,
5151: fs1.organization_id
5183: and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
5184: GROUP BY fs1.line_id,
5185: fs1.primary_item_id,
5186: --fs1.schedule_group_id,
5187: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
5188: --floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
5189: fs1.organization_id;
5190:
5191: ELSIF p_category_set_id IS NOT NULL THEN
5215: fs1.line_id,
5216: fs1.primary_item_id,
5217: null,
5218: --fs1.schedule_group_id,
5219: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5220: sum(fs1.planned_quantity),
5221: sum(fs1.quantity_completed),
5222: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5223: +floor(p_to_start_date-p_from_start_date),
5218: --fs1.schedule_group_id,
5219: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5220: sum(fs1.planned_quantity),
5221: sum(fs1.quantity_completed),
5222: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5223: +floor(p_to_start_date-p_from_start_date),
5224: NULL,
5225: 0,
5226: fs1.organization_id
5253: and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
5254: GROUP BY fs1.line_id,
5255: fs1.primary_item_id,
5256: --fs1.schedule_group_id,
5257: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
5258: --floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
5259: fs1.organization_id;
5260: ELSE
5261: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5283: fs1.line_id,
5284: fs1.primary_item_id,
5285: null,
5286: --fs1.schedule_group_id,
5287: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5288: sum(fs1.planned_quantity),
5289: sum(fs1.quantity_completed),
5290: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5291: +floor(p_to_start_date-p_from_start_date),
5286: --fs1.schedule_group_id,
5287: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5288: sum(fs1.planned_quantity),
5289: sum(fs1.quantity_completed),
5290: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5291: +floor(p_to_start_date-p_from_start_date),
5292: NULL,
5293: 0,
5294: fs1.organization_id
5317: and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
5318: GROUP BY fs1.line_id,
5319: fs1.primary_item_id,
5320: --fs1.schedule_group_id,
5321: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
5322: --floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
5323: fs1.organization_id;
5324: END IF;
5325:
5380: 1,
5381: fs1.line_id,
5382: fs1.primary_item_id,
5383: fs1.schedule_group_id,
5384: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5385: fs1.planned_quantity,
5386: fs1.quantity_completed,
5387: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5388: NULL,
5383: fs1.schedule_group_id,
5384: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5385: fs1.planned_quantity,
5386: fs1.quantity_completed,
5387: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5388: NULL,
5389: 0,
5390: fs1.organization_id,
5391: NVL(fs1.build_sequence,0)
5449: 1,
5450: fs1.line_id,
5451: fs1.primary_item_id,
5452: fs1.schedule_group_id,
5453: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5454: fs1.planned_quantity,
5455: fs1.quantity_completed,
5456: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5457: NULL,
5452: fs1.schedule_group_id,
5453: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5454: fs1.planned_quantity,
5455: fs1.quantity_completed,
5456: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5457: NULL,
5458: 0,
5459: fs1.organization_id,
5460: NVL(fs1.build_sequence,0)
5513: 1,
5514: fs1.line_id,
5515: fs1.primary_item_id,
5516: fs1.schedule_group_id,
5517: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5518: fs1.planned_quantity,
5519: fs1.quantity_completed,
5520: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5521: NULL,
5516: fs1.schedule_group_id,
5517: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5518: fs1.planned_quantity,
5519: fs1.quantity_completed,
5520: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5521: NULL,
5522: 0,
5523: fs1.organization_id,
5524: NVL(fs1.build_sequence,0)
5587: WHERE query_id = p_query_id
5588: AND number1 = p_line_id
5589: AND number2 = p_item_id
5590: AND NVL(number3,-1) = NVL(p_schedule_group_id,-1)
5591: AND trunc(date2) = trunc(flm_timezone.server_to_client(p_completion_date)) --fix bug#3170105
5592: AND NVL(number11,0) = NVL(p_build_sequence,0);
5593: --mrp form query dates are in client timezone
5594:
5595: CURSOR C2 IS
5599: AND primary_item_id = p_item_id
5600: AND organization_id = p_organization_id
5601: AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5602: AND NVL(build_sequence,0) = NVL(p_build_sequence,0)
5603: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5604: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5605:
5606: BEGIN
5607:
5600: AND organization_id = p_organization_id
5601: AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5602: AND NVL(build_sequence,0) = NVL(p_build_sequence,0)
5603: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5604: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5605:
5606: BEGIN
5607:
5608: OPEN C1;
5636: 1,
5637: p_line_id,
5638: p_item_id,
5639: p_schedule_group_id,
5640: flm_timezone.server_to_client(p_completion_date)
5641: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5642: 0,
5643: 0,
5644: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5640: flm_timezone.server_to_client(p_completion_date)
5641: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5642: 0,
5643: 0,
5644: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5645: 0,
5646: p_quantity,
5647: p_organization_id,
5648: NVL(p_build_sequence,0)
5651: AND fs.primary_item_id = p_item_id
5652: AND fs.organization_id = p_organization_id
5653: AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5654: AND scheduled_completion_date
5655: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5656: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#3170105
5657: AND not exists ( select 1 from mtl_transactions_interface
5658: where transaction_source_id = fs.wip_entity_id
5659: and organization_id = fs.organization_id
5652: AND fs.organization_id = p_organization_id
5653: AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5654: AND scheduled_completion_date
5655: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5656: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#3170105
5657: AND not exists ( select 1 from mtl_transactions_interface
5658: where transaction_source_id = fs.wip_entity_id
5659: and organization_id = fs.organization_id
5660: and transaction_source_type_id = 5
5712: WHERE query_id = p_query_id
5713: AND number1 = p_line_id
5714: AND number2 = p_item_id
5715: --2 AND NVL(number3,-1) = NVL(p_schedule_group_id,-1)
5716: AND trunc(date2) = trunc(flm_timezone.server_to_client(p_completion_date)); --fix bug#3170105
5717:
5718: CURSOR C2 IS
5719: SELECT sum(planned_quantity)
5720: FROM wip_flow_schedules
5721: WHERE line_id = p_line_id
5722: AND primary_item_id = p_item_id
5723: AND organization_id = p_organization_id
5724: --3 AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5725: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5726: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5727:
5728: BEGIN
5729:
5722: AND primary_item_id = p_item_id
5723: AND organization_id = p_organization_id
5724: --3 AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5725: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5726: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5727:
5728: BEGIN
5729:
5730: OPEN C1;
5758: p_line_id,
5759: p_item_id,
5760: null,
5761: --p_schedule_group_id,
5762: flm_timezone.server_to_client(p_completion_date)
5763: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5764: 0,
5765: 0,
5766: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5762: flm_timezone.server_to_client(p_completion_date)
5763: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5764: 0,
5765: 0,
5766: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5767: nvl(sum(planned_quantity),0),
5768: nvl(sum(planned_quantity),0) + p_quantity,
5769: p_organization_id
5770: FROM wip_flow_schedules fs
5772: AND fs.primary_item_id = p_item_id
5773: AND fs.organization_id = p_organization_id
5774: --4 AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5775: AND scheduled_completion_date
5776: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5777: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#317010
5778: AND not exists ( select 1 from mtl_transactions_interface
5779: where transaction_source_id = fs.wip_entity_id
5780: and organization_id = fs.organization_id
5773: AND fs.organization_id = p_organization_id
5774: --4 AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5775: AND scheduled_completion_date
5776: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5777: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#317010
5778: AND not exists ( select 1 from mtl_transactions_interface
5779: where transaction_source_id = fs.wip_entity_id
5780: and organization_id = fs.organization_id
5781: and transaction_source_type_id = 5
5903: END relieve_rolled_schedules;
5904:
5905: BEGIN --begin roll_flow_schedules
5906:
5907: flm_timezone.init_timezone(p_organization_id);--3827600
5908:
5909: -- Set the return code
5910: RETCODE := G_SUCCESS;
5911:
5909: -- Set the return code
5910: RETCODE := G_SUCCESS;
5911:
5912: --fix bug#3170105
5913: l_from_start_date := flm_timezone.client_to_server(
5914: fnd_date.canonical_to_date(p_from_start_date));
5915: l_from_end_date := flm_timezone.client_to_server(
5916: fnd_date.canonical_to_date(p_from_end_date));
5917: l_to_start_date := flm_timezone.client_to_server(
5911:
5912: --fix bug#3170105
5913: l_from_start_date := flm_timezone.client_to_server(
5914: fnd_date.canonical_to_date(p_from_start_date));
5915: l_from_end_date := flm_timezone.client_to_server(
5916: fnd_date.canonical_to_date(p_from_end_date));
5917: l_to_start_date := flm_timezone.client_to_server(
5918: fnd_date.canonical_to_date(p_to_start_date));
5919: l_to_end_date := flm_timezone.client_to_server(
5913: l_from_start_date := flm_timezone.client_to_server(
5914: fnd_date.canonical_to_date(p_from_start_date));
5915: l_from_end_date := flm_timezone.client_to_server(
5916: fnd_date.canonical_to_date(p_from_end_date));
5917: l_to_start_date := flm_timezone.client_to_server(
5918: fnd_date.canonical_to_date(p_to_start_date));
5919: l_to_end_date := flm_timezone.client_to_server(
5920: fnd_date.canonical_to_date(p_to_end_date));
5921: --end of fix bug#3170105
5915: l_from_end_date := flm_timezone.client_to_server(
5916: fnd_date.canonical_to_date(p_from_end_date));
5917: l_to_start_date := flm_timezone.client_to_server(
5918: fnd_date.canonical_to_date(p_to_start_date));
5919: l_to_end_date := flm_timezone.client_to_server(
5920: fnd_date.canonical_to_date(p_to_end_date));
5921: --end of fix bug#3170105
5922:
5923: --TZ BOM Calendar Bug 3832684
6085:
6086: --fix bug#3170105
6087: IF p_spread_qty <> 3 THEN
6088: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6089: 1, flm_timezone.server_to_calendar(l_to_start_date));
6090: --TZ BOM Calendar bug 3832684
6091: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_start_time);
6092:
6093: /**
6087: IF p_spread_qty <> 3 THEN
6088: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6089: 1, flm_timezone.server_to_calendar(l_to_start_date));
6090: --TZ BOM Calendar bug 3832684
6091: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_start_time);
6092:
6093: /**
6094: Bug 2213859 - l_to_start_date is compared with to_date(p_to_start_date, 'YYYY/MM/DD')
6095: instead of l_to_end_date
6093: /**
6094: Bug 2213859 - l_to_start_date is compared with to_date(p_to_start_date, 'YYYY/MM/DD')
6095: instead of l_to_end_date
6096: **/
6097: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_start_date) AND
6098: (p_spread_qty = 2 ) THEN
6099: FND_MESSAGE.set_name('MRP','MRP_TO_DATE_CHANGED');
6100: FND_MESSAGE.set_token('ORIG_DATE',to_char(l_to_start_date));
6101: FND_MESSAGE.set_token('NEW_DATE',to_char(l_temp_date));
6120: l_to_end_date := l_to_start_date;
6121: ELSE
6122: --fix bug#3170105
6123: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6124: 1, flm_timezone.server_to_calendar(l_to_end_date));
6125: --TZ BOM Calendar bug 3832684
6126: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_end_time);
6127:
6128: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_end_date) THEN
6122: --fix bug#3170105
6123: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6124: 1, flm_timezone.server_to_calendar(l_to_end_date));
6125: --TZ BOM Calendar bug 3832684
6126: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_end_time);
6127:
6128: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_end_date) THEN
6129: FND_MESSAGE.set_name('MRP','MRP_TO_DATE_CHANGED');
6130: FND_MESSAGE.set_token('ORIG_DATE',l_to_end_date);
6124: 1, flm_timezone.server_to_calendar(l_to_end_date));
6125: --TZ BOM Calendar bug 3832684
6126: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_end_time);
6127:
6128: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_end_date) THEN
6129: FND_MESSAGE.set_name('MRP','MRP_TO_DATE_CHANGED');
6130: FND_MESSAGE.set_token('ORIG_DATE',l_to_end_date);
6131: FND_MESSAGE.set_token('NEW_DATE',l_temp_date);
6132: l_log_message := FND_MESSAGE.get;
6738: -- Initialization of the Seq Counter table
6739: MRP_UTIL.MRP_LOG('Initialize the counter for line='||fs_report_rec.line_id);
6740: l_build_seq_counter(fs_report_rec.line_id).base_number
6741: := Get_Base_Number(fs_report_rec.line_id,
6742: flm_timezone.client00_in_server(l_flow_schedule_rec.scheduled_completion_date),
6743: l_from_start_date, l_from_end_date); --fix bug#3170105
6744: MRP_UTIL.MRP_LOG('Initialized base number='||
6745: l_build_seq_counter(fs_report_rec.line_id).base_number);
6746: l_build_seq_counter(fs_report_rec.line_id).current_build_seq :=
7116: dbms_sql.column_value(l_cursor, 1, l_flow_schedule_rec.wip_entity_id);
7117: END IF;
7118: EXIT WHEN l_rows = 0;
7119: UPDATE wip_flow_schedules SET status = 2,
7120: date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
7121: WHERE wip_entity_id = l_flow_schedule_rec.wip_entity_id;
7122: MRP_UTIL.MRP_LOG('Closed Schedule with wip_entity_id:'||to_char(
7123: l_flow_schedule_rec.wip_entity_id));
7124: END LOOP;