[Home] [Help]
3152:
3153: FND_MSG_PUB.Initialize;
3154:
3155: --start bug 3783650: TZ BOM Calendar bug 3832684
3156: IF flm_timezone.g_enabled THEN
3157: --fix bug#3170105
3158: l_first_bucket_client := flm_timezone.server_to_client(p_first_bucket_date);
3159: l_first_bucket_client := trunc(l_first_bucket_client);
3160: --end of fix bug#3170105
3154:
3155: --start bug 3783650: TZ BOM Calendar bug 3832684
3156: IF flm_timezone.g_enabled THEN
3157: --fix bug#3170105
3158: l_first_bucket_client := flm_timezone.server_to_client(p_first_bucket_date);
3159: l_first_bucket_client := trunc(l_first_bucket_client);
3160: --end of fix bug#3170105
3161: ELSE
3162: l_first_bucket_client := trunc(p_first_bucket_date);
3454:
3455: BEGIN
3456:
3457: --fix bug#3170105
3458: l_start_date := flm_timezone.client_to_server(
3459: fnd_date.canonical_to_date(p_start_date));
3460: l_end_date := flm_timezone.client_to_server(
3461: fnd_date.canonical_to_date(p_end_date));
3462: --end of fix bug#3170105
3456:
3457: --fix bug#3170105
3458: l_start_date := flm_timezone.client_to_server(
3459: fnd_date.canonical_to_date(p_start_date));
3460: l_end_date := flm_timezone.client_to_server(
3461: fnd_date.canonical_to_date(p_end_date));
3462: --end of fix bug#3170105
3463:
3464: -- Print report parameters
3937: AND scheduled_completion_date --fix bug#3170105
3938: BETWEEN p_to_start_date AND p_to_end_date+1-1/(24*60*60)
3939: UNION
3940: SELECT 0,
3941: flm_timezone.calendar_to_server(calendar_date), --TZ Bug 3832684
3942: 0,
3943: 0
3944: FROM mtl_parameters mp,
3945: bom_calendar_dates bom
3946: WHERE mp.organization_id = p_organization_id
3947: AND mp.calendar_exception_set_id = bom.exception_set_id
3948: AND mp.calendar_code = bom.calendar_code
3949: AND bom.seq_num IS NOT NULL
3950: AND bom.calendar_date between flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3951: AND flm_timezone.server_to_calendar(p_to_end_date)
3952: AND bom.calendar_date NOT IN (select flm_timezone.server_to_calendar(scheduled_completion_date)
3953: FROM wip_flow_schedules
3954: WHERE organization_id = p_organization_id
3947: AND mp.calendar_exception_set_id = bom.exception_set_id
3948: AND mp.calendar_code = bom.calendar_code
3949: AND bom.seq_num IS NOT NULL
3950: AND bom.calendar_date between flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3951: AND flm_timezone.server_to_calendar(p_to_end_date)
3952: AND bom.calendar_date NOT IN (select flm_timezone.server_to_calendar(scheduled_completion_date)
3953: FROM wip_flow_schedules
3954: WHERE organization_id = p_organization_id
3955: AND line_id = p_line_id
3948: AND mp.calendar_code = bom.calendar_code
3949: AND bom.seq_num IS NOT NULL
3950: AND bom.calendar_date between flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3951: AND flm_timezone.server_to_calendar(p_to_end_date)
3952: AND bom.calendar_date NOT IN (select flm_timezone.server_to_calendar(scheduled_completion_date)
3953: FROM wip_flow_schedules
3954: WHERE organization_id = p_organization_id
3955: AND line_id = p_line_id
3956: AND primary_item_id = p_item_id
3967: bom_calendar_dates bom
3968: WHERE mp.calendar_exception_set_id = bom.exception_set_id
3969: AND mp.calendar_code = bom.calendar_code
3970: AND mp.organization_id = p_organization_id
3971: AND bom.calendar_date BETWEEN flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3972: AND flm_timezone.server_to_calendar(p_to_end_date)
3973: AND ((bom.seq_num IS NOT NULL) OR
3974: (bom.calendar_date IN (SELECT flm_timezone.server_to_calendar(scheduled_completion_date)
3975: FROM wip_flow_schedules
3968: WHERE mp.calendar_exception_set_id = bom.exception_set_id
3969: AND mp.calendar_code = bom.calendar_code
3970: AND mp.organization_id = p_organization_id
3971: AND bom.calendar_date BETWEEN flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3972: AND flm_timezone.server_to_calendar(p_to_end_date)
3973: AND ((bom.seq_num IS NOT NULL) OR
3974: (bom.calendar_date IN (SELECT flm_timezone.server_to_calendar(scheduled_completion_date)
3975: FROM wip_flow_schedules
3976: WHERE organization_id = p_organization_id
3970: AND mp.organization_id = p_organization_id
3971: AND bom.calendar_date BETWEEN flm_timezone.server_to_calendar(p_to_start_date) --fix bug#3170105
3972: AND flm_timezone.server_to_calendar(p_to_end_date)
3973: AND ((bom.seq_num IS NOT NULL) OR
3974: (bom.calendar_date IN (SELECT flm_timezone.server_to_calendar(scheduled_completion_date)
3975: FROM wip_flow_schedules
3976: WHERE organization_id = p_organization_id
3977: AND line_id = p_line_id
3978: AND primary_item_id = p_item_id
3976: WHERE organization_id = p_organization_id
3977: AND line_id = p_line_id
3978: AND primary_item_id = p_item_id
3979: AND scheduled_completion_date BETWEEN
3980: flm_timezone.calendar_to_server(bom.calendar_date)
3981: AND flm_timezone.calendar_to_server(bom.calendar_date)+1-1/(24*60*60)
3982: AND demand_source_header_id IS NULL)));
3983:
3984: CURSOR C5(p_line_id NUMBER, p_item_id NUMBER,
3977: AND line_id = p_line_id
3978: AND primary_item_id = p_item_id
3979: AND scheduled_completion_date BETWEEN
3980: flm_timezone.calendar_to_server(bom.calendar_date)
3981: AND flm_timezone.calendar_to_server(bom.calendar_date)+1-1/(24*60*60)
3982: AND demand_source_header_id IS NULL)));
3983:
3984: CURSOR C5(p_line_id NUMBER, p_item_id NUMBER,
3985: p_to_start_date DATE, p_to_end_date DATE) --fix bug#3170105
4078: AND build_sequence is NOT NULL
4079: AND (ROLL_FORWARDED_FLAG <> G_INTERMEDIATE_ROLL_FORWARDED OR
4080: ROLL_FORWARDED_FLAG IS NULL) /*Bug 3019639*/
4081: /** Forward ported bug 3055939 */
4082: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_target_date) --fix bug#3170105
4083: AND scheduled_completion_date < flm_timezone.client00_in_server(p_target_date+1); --fix bug#3170105
4084:
4085: CURSOR Count_Orig_Schedules (p_line_id NUMBER,
4086: p_from_start_date DATE, p_from_end_date DATE) --fix bug#3170105)
4079: AND (ROLL_FORWARDED_FLAG <> G_INTERMEDIATE_ROLL_FORWARDED OR
4080: ROLL_FORWARDED_FLAG IS NULL) /*Bug 3019639*/
4081: /** Forward ported bug 3055939 */
4082: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_target_date) --fix bug#3170105
4083: AND scheduled_completion_date < flm_timezone.client00_in_server(p_target_date+1); --fix bug#3170105
4084:
4085: CURSOR Count_Orig_Schedules (p_line_id NUMBER,
4086: p_from_start_date DATE, p_from_end_date DATE) --fix bug#3170105)
4087: IS
4120: FROM wip_flow_schedules
4121: WHERE organization_id = p_organization_id
4122: AND line_id = p_line_id
4123: /** Forward ported bug 3055939 */
4124: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4125: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4126:
4127: CURSOR Check_Schedule_Distinct_Type
4128: (p_line_id NUMBER ,p_scheduled_completion_date DATE)
4121: WHERE organization_id = p_organization_id
4122: AND line_id = p_line_id
4123: /** Forward ported bug 3055939 */
4124: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4125: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4126:
4127: CURSOR Check_Schedule_Distinct_Type
4128: (p_line_id NUMBER ,p_scheduled_completion_date DATE)
4129: IS
4131: FROM wip_flow_schedules
4132: WHERE organization_id = p_organization_id
4133: AND line_id = p_line_id
4134: /** Forward ported bug 3055939 */
4135: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4136: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4137:
4138: CURSOR Line_Details(p_line_id NUMBER)
4139: IS SELECT START_TIME,MAXIMUM_RATE FROM wip_lines
4132: WHERE organization_id = p_organization_id
4133: AND line_id = p_line_id
4134: /** Forward ported bug 3055939 */
4135: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4136: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4137:
4138: CURSOR Line_Details(p_line_id NUMBER)
4139: IS SELECT START_TIME,MAXIMUM_RATE FROM wip_lines
4140: WHERE organization_id = p_organization_id
4147: FROM wip_flow_schedules
4148: WHERE organization_id = p_organization_id
4149: AND line_id = p_line_id
4150: /** Forward ported bug 3055939 */
4151: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4152: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4153:
4154: l_type_count NUMBER:=0;
4155: l_distinct_type NUMBER:=0;
4148: WHERE organization_id = p_organization_id
4149: AND line_id = p_line_id
4150: /** Forward ported bug 3055939 */
4151: AND scheduled_completion_date >= flm_timezone.client00_in_server(p_scheduled_completion_date) --fix bug#3170105
4152: AND scheduled_completion_date < flm_timezone.client00_in_server(p_scheduled_completion_date+1); --fix bug#3170105
4153:
4154: l_type_count NUMBER:=0;
4155: l_distinct_type NUMBER:=0;
4156: l_start_time NUMBER:=0;
4191: --TZ 3832684: Calculate completion date in Client TZ.
4192: --l_schedule_date and p_completion_date should be in Client00 time.
4193: --If p_completion_date is not in Client00 time, which means that there are
4194: --FS in the date, then l_time_flag=0 and thus, will not get into this IF flow.
4195: if flm_timezone.g_enabled then
4196: l_start_time := flm_timezone.g_client_start_time;
4197: l_schedule_date := flm_timezone.server_to_client(p_scheduled_completion_date);
4198: else
4199: l_schedule_date := p_scheduled_completion_date; --fix bug#3170105
4192: --l_schedule_date and p_completion_date should be in Client00 time.
4193: --If p_completion_date is not in Client00 time, which means that there are
4194: --FS in the date, then l_time_flag=0 and thus, will not get into this IF flow.
4195: if flm_timezone.g_enabled then
4196: l_start_time := flm_timezone.g_client_start_time;
4197: l_schedule_date := flm_timezone.server_to_client(p_scheduled_completion_date);
4198: else
4199: l_schedule_date := p_scheduled_completion_date; --fix bug#3170105
4200: end if;
4193: --If p_completion_date is not in Client00 time, which means that there are
4194: --FS in the date, then l_time_flag=0 and thus, will not get into this IF flow.
4195: if flm_timezone.g_enabled then
4196: l_start_time := flm_timezone.g_client_start_time;
4197: l_schedule_date := flm_timezone.server_to_client(p_scheduled_completion_date);
4198: else
4199: l_schedule_date := p_scheduled_completion_date; --fix bug#3170105
4200: end if;
4201:
4203: OPEN Check_Last_Schedule(p_line_id,p_scheduled_completion_date); --fix bug#3170105
4204: FETCH Check_Last_Schedule INTO l_last_schedule_date;
4205:
4206: --TZ 3832684: calculate completion date in Client TZ
4207: if flm_timezone.g_enabled then
4208: l_last_schedule_date := flm_timezone.server_to_client(l_last_schedule_date);
4209: end if;
4210:
4211: --TZ 3832684: better way to get the seconds in Client time
4204: FETCH Check_Last_Schedule INTO l_last_schedule_date;
4205:
4206: --TZ 3832684: calculate completion date in Client TZ
4207: if flm_timezone.g_enabled then
4208: l_last_schedule_date := flm_timezone.server_to_client(l_last_schedule_date);
4209: end if;
4210:
4211: --TZ 3832684: better way to get the seconds in Client time
4212: l_start_time := to_char(l_last_schedule_date,'SSSSS');
4225: MRP_UTIL.MRP_LOG('the client date after :'||to_char(l_schedule_date,'DD-MON-YYYY HH24:MI:SS'));
4226: --end of fix bug#3170105
4227:
4228: --TZ 3832684: conversion back to server to be passed back to caller
4229: if flm_timezone.g_enabled then
4230: l_schedule_date := flm_timezone.client_to_server(l_schedule_date);
4231: MRP_UTIL.MRP_LOG('the datetime after conv :'||to_char(l_schedule_date,'DD-MON-YY HH24:MI:SS'));
4232: end if;
4233:
4226: --end of fix bug#3170105
4227:
4228: --TZ 3832684: conversion back to server to be passed back to caller
4229: if flm_timezone.g_enabled then
4230: l_schedule_date := flm_timezone.client_to_server(l_schedule_date);
4231: MRP_UTIL.MRP_LOG('the datetime after conv :'||to_char(l_schedule_date,'DD-MON-YY HH24:MI:SS'));
4232: end if;
4233:
4234: CLOSE Line_Details;
4339: ' mtl_system_items_kfv msi, '||
4340: ' wip_flow_schedules fs ' ||
4341: ' WHERE fs.organization_id = to_char( :organization_id) ' ||
4342: l_where_clause ||
4343: ' AND fs.scheduled_completion_date < flm_timezone.sysdate00_in_server' ||
4344: ' and line.line_id = fs.line_id ' ||
4345: ' and line.organization_id = fs.organization_id '||
4346: ' and nvl(fs.status ,0) = 1 '||
4347: l_cat_sql ||
4848: l_log_message VARCHAR2(2000);
4849: BEGIN
4850:
4851: UPDATE WIP_FLOW_SCHEDULES SET
4852: status = 2 , date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
4853: WHERE wip_entity_id = p_wip_entity_id
4854: AND organization_id = p_organization_id;
4855:
4856: return TRUE;
4892: l_select VARCHAR2(100);
4893: BEGIN
4894:
4895: UPDATE WIP_FLOW_SCHEDULES SET
4896: status = 2 , date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
4897: WHERE schedule_number = p_schedule_number
4898: AND organization_id = p_organization_id;
4899:
4900: return TRUE;
5137: fs1.line_id,
5138: fs1.primary_item_id,
5139: null,
5140: --fs1.schedule_group_id,
5141: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5142: sum(fs1.planned_quantity),
5143: sum(fs1.quantity_completed),
5144: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5145: +floor(p_to_start_date-p_from_start_date),
5140: --fs1.schedule_group_id,
5141: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5142: sum(fs1.planned_quantity),
5143: sum(fs1.quantity_completed),
5144: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5145: +floor(p_to_start_date-p_from_start_date),
5146: NULL,
5147: 0,
5148: fs1.organization_id
5180: and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
5181: GROUP BY fs1.line_id,
5182: fs1.primary_item_id,
5183: --fs1.schedule_group_id,
5184: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
5185: --floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
5186: fs1.organization_id;
5187:
5188: ELSIF p_category_set_id IS NOT NULL THEN
5212: fs1.line_id,
5213: fs1.primary_item_id,
5214: null,
5215: --fs1.schedule_group_id,
5216: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5217: sum(fs1.planned_quantity),
5218: sum(fs1.quantity_completed),
5219: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5220: +floor(p_to_start_date-p_from_start_date),
5215: --fs1.schedule_group_id,
5216: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5217: sum(fs1.planned_quantity),
5218: sum(fs1.quantity_completed),
5219: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5220: +floor(p_to_start_date-p_from_start_date),
5221: NULL,
5222: 0,
5223: fs1.organization_id
5250: and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
5251: GROUP BY fs1.line_id,
5252: fs1.primary_item_id,
5253: --fs1.schedule_group_id,
5254: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
5255: --floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
5256: fs1.organization_id;
5257: ELSE
5258: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5280: fs1.line_id,
5281: fs1.primary_item_id,
5282: null,
5283: --fs1.schedule_group_id,
5284: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5285: sum(fs1.planned_quantity),
5286: sum(fs1.quantity_completed),
5287: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5288: +floor(p_to_start_date-p_from_start_date),
5283: --fs1.schedule_group_id,
5284: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5285: sum(fs1.planned_quantity),
5286: sum(fs1.quantity_completed),
5287: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)) --fix bug#3170105
5288: +floor(p_to_start_date-p_from_start_date),
5289: NULL,
5290: 0,
5291: fs1.organization_id
5314: and transaction_action_id in (1, 27, 30, 31, 32, 33, 34) )
5315: GROUP BY fs1.line_id,
5316: fs1.primary_item_id,
5317: --fs1.schedule_group_id,
5318: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)),--bug 3827600
5319: --floor(fs1.scheduled_completion_date-p_from_start_date), --fix bug#3170105
5320: fs1.organization_id;
5321: END IF;
5322:
5377: 1,
5378: fs1.line_id,
5379: fs1.primary_item_id,
5380: fs1.schedule_group_id,
5381: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5382: fs1.planned_quantity,
5383: fs1.quantity_completed,
5384: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5385: NULL,
5380: fs1.schedule_group_id,
5381: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5382: fs1.planned_quantity,
5383: fs1.quantity_completed,
5384: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5385: NULL,
5386: 0,
5387: fs1.organization_id,
5388: NVL(fs1.build_sequence,0)
5446: 1,
5447: fs1.line_id,
5448: fs1.primary_item_id,
5449: fs1.schedule_group_id,
5450: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5451: fs1.planned_quantity,
5452: fs1.quantity_completed,
5453: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5454: NULL,
5449: fs1.schedule_group_id,
5450: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5451: fs1.planned_quantity,
5452: fs1.quantity_completed,
5453: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5454: NULL,
5455: 0,
5456: fs1.organization_id,
5457: NVL(fs1.build_sequence,0)
5510: 1,
5511: fs1.line_id,
5512: fs1.primary_item_id,
5513: fs1.schedule_group_id,
5514: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5515: fs1.planned_quantity,
5516: fs1.quantity_completed,
5517: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5518: NULL,
5513: fs1.schedule_group_id,
5514: trunc(flm_timezone.server_to_client(fs1.scheduled_completion_date)), --fix bug#3170105
5515: fs1.planned_quantity,
5516: fs1.quantity_completed,
5517: flm_timezone.server_to_client(p_to_start_date), --fix bug#3170105
5518: NULL,
5519: 0,
5520: fs1.organization_id,
5521: NVL(fs1.build_sequence,0)
5584: WHERE query_id = p_query_id
5585: AND number1 = p_line_id
5586: AND number2 = p_item_id
5587: AND NVL(number3,-1) = NVL(p_schedule_group_id,-1)
5588: AND trunc(date2) = trunc(flm_timezone.server_to_client(p_completion_date)) --fix bug#3170105
5589: AND NVL(number11,0) = NVL(p_build_sequence,0);
5590: --mrp form query dates are in client timezone
5591:
5592: CURSOR C2 IS
5596: AND primary_item_id = p_item_id
5597: AND organization_id = p_organization_id
5598: AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5599: AND NVL(build_sequence,0) = NVL(p_build_sequence,0)
5600: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5601: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5602:
5603: BEGIN
5604:
5597: AND organization_id = p_organization_id
5598: AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5599: AND NVL(build_sequence,0) = NVL(p_build_sequence,0)
5600: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5601: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5602:
5603: BEGIN
5604:
5605: OPEN C1;
5633: 1,
5634: p_line_id,
5635: p_item_id,
5636: p_schedule_group_id,
5637: flm_timezone.server_to_client(p_completion_date)
5638: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5639: 0,
5640: 0,
5641: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5637: flm_timezone.server_to_client(p_completion_date)
5638: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5639: 0,
5640: 0,
5641: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5642: 0,
5643: p_quantity,
5644: p_organization_id,
5645: NVL(p_build_sequence,0)
5648: AND fs.primary_item_id = p_item_id
5649: AND fs.organization_id = p_organization_id
5650: AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5651: AND scheduled_completion_date
5652: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5653: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#3170105
5654: AND not exists ( select 1 from mtl_transactions_interface
5655: where transaction_source_id = fs.wip_entity_id
5656: and organization_id = fs.organization_id
5649: AND fs.organization_id = p_organization_id
5650: AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5651: AND scheduled_completion_date
5652: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5653: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#3170105
5654: AND not exists ( select 1 from mtl_transactions_interface
5655: where transaction_source_id = fs.wip_entity_id
5656: and organization_id = fs.organization_id
5657: and transaction_source_type_id = 5
5709: WHERE query_id = p_query_id
5710: AND number1 = p_line_id
5711: AND number2 = p_item_id
5712: --2 AND NVL(number3,-1) = NVL(p_schedule_group_id,-1)
5713: AND trunc(date2) = trunc(flm_timezone.server_to_client(p_completion_date)); --fix bug#3170105
5714:
5715: CURSOR C2 IS
5716: SELECT sum(planned_quantity)
5717: FROM wip_flow_schedules
5718: WHERE line_id = p_line_id
5719: AND primary_item_id = p_item_id
5720: AND organization_id = p_organization_id
5721: --3 AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5722: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5723: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5724:
5725: BEGIN
5726:
5719: AND primary_item_id = p_item_id
5720: AND organization_id = p_organization_id
5721: --3 AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5722: AND scheduled_completion_date between flm_timezone.client00_in_server(p_completion_date)
5723: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60); --fix bug#3170105
5724:
5725: BEGIN
5726:
5727: OPEN C1;
5755: p_line_id,
5756: p_item_id,
5757: null,
5758: --p_schedule_group_id,
5759: flm_timezone.server_to_client(p_completion_date)
5760: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5761: 0,
5762: 0,
5763: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5759: flm_timezone.server_to_client(p_completion_date)
5760: -floor(p_to_start_date-p_from_start_date), --fix bug#3170105
5761: 0,
5762: 0,
5763: flm_timezone.server_to_client(p_completion_date), --fix bug#3170105
5764: nvl(sum(planned_quantity),0),
5765: nvl(sum(planned_quantity),0) + p_quantity,
5766: p_organization_id
5767: FROM wip_flow_schedules fs
5769: AND fs.primary_item_id = p_item_id
5770: AND fs.organization_id = p_organization_id
5771: --4 AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5772: AND scheduled_completion_date
5773: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5774: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#317010
5775: AND not exists ( select 1 from mtl_transactions_interface
5776: where transaction_source_id = fs.wip_entity_id
5777: and organization_id = fs.organization_id
5770: AND fs.organization_id = p_organization_id
5771: --4 AND nvl(fs.schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
5772: AND scheduled_completion_date
5773: BETWEEN flm_timezone.client00_in_server(p_completion_date)
5774: AND flm_timezone.client00_in_server(p_completion_date)+1-1/(24*60*60) --fix bug#317010
5775: AND not exists ( select 1 from mtl_transactions_interface
5776: where transaction_source_id = fs.wip_entity_id
5777: and organization_id = fs.organization_id
5778: and transaction_source_type_id = 5
5900: END relieve_rolled_schedules;
5901:
5902: BEGIN --begin roll_flow_schedules
5903:
5904: flm_timezone.init_timezone(p_organization_id);--3827600
5905:
5906: -- Set the return code
5907: RETCODE := G_SUCCESS;
5908:
5906: -- Set the return code
5907: RETCODE := G_SUCCESS;
5908:
5909: --fix bug#3170105
5910: l_from_start_date := flm_timezone.client_to_server(
5911: fnd_date.canonical_to_date(p_from_start_date));
5912: l_from_end_date := flm_timezone.client_to_server(
5913: fnd_date.canonical_to_date(p_from_end_date));
5914: l_to_start_date := flm_timezone.client_to_server(
5908:
5909: --fix bug#3170105
5910: l_from_start_date := flm_timezone.client_to_server(
5911: fnd_date.canonical_to_date(p_from_start_date));
5912: l_from_end_date := flm_timezone.client_to_server(
5913: fnd_date.canonical_to_date(p_from_end_date));
5914: l_to_start_date := flm_timezone.client_to_server(
5915: fnd_date.canonical_to_date(p_to_start_date));
5916: l_to_end_date := flm_timezone.client_to_server(
5910: l_from_start_date := flm_timezone.client_to_server(
5911: fnd_date.canonical_to_date(p_from_start_date));
5912: l_from_end_date := flm_timezone.client_to_server(
5913: fnd_date.canonical_to_date(p_from_end_date));
5914: l_to_start_date := flm_timezone.client_to_server(
5915: fnd_date.canonical_to_date(p_to_start_date));
5916: l_to_end_date := flm_timezone.client_to_server(
5917: fnd_date.canonical_to_date(p_to_end_date));
5918: --end of fix bug#3170105
5912: l_from_end_date := flm_timezone.client_to_server(
5913: fnd_date.canonical_to_date(p_from_end_date));
5914: l_to_start_date := flm_timezone.client_to_server(
5915: fnd_date.canonical_to_date(p_to_start_date));
5916: l_to_end_date := flm_timezone.client_to_server(
5917: fnd_date.canonical_to_date(p_to_end_date));
5918: --end of fix bug#3170105
5919:
5920: --TZ BOM Calendar Bug 3832684
6082:
6083: --fix bug#3170105
6084: IF p_spread_qty <> 3 THEN
6085: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6086: 1, flm_timezone.server_to_calendar(l_to_start_date));
6087: --TZ BOM Calendar bug 3832684
6088: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_start_time);
6089:
6090: /**
6084: IF p_spread_qty <> 3 THEN
6085: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6086: 1, flm_timezone.server_to_calendar(l_to_start_date));
6087: --TZ BOM Calendar bug 3832684
6088: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_start_time);
6089:
6090: /**
6091: Bug 2213859 - l_to_start_date is compared with to_date(p_to_start_date, 'YYYY/MM/DD')
6092: instead of l_to_end_date
6090: /**
6091: Bug 2213859 - l_to_start_date is compared with to_date(p_to_start_date, 'YYYY/MM/DD')
6092: instead of l_to_end_date
6093: **/
6094: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_start_date) AND
6095: (p_spread_qty = 2 ) THEN
6096: FND_MESSAGE.set_name('MRP','MRP_TO_DATE_CHANGED');
6097: FND_MESSAGE.set_token('ORIG_DATE',to_char(l_to_start_date));
6098: FND_MESSAGE.set_token('NEW_DATE',to_char(l_temp_date));
6117: l_to_end_date := l_to_start_date;
6118: ELSE
6119: --fix bug#3170105
6120: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6121: 1, flm_timezone.server_to_calendar(l_to_end_date));
6122: --TZ BOM Calendar bug 3832684
6123: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_end_time);
6124:
6125: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_end_date) THEN
6119: --fix bug#3170105
6120: l_temp_date := mrp_calendar.next_work_day(p_organization_id,
6121: 1, flm_timezone.server_to_calendar(l_to_end_date));
6122: --TZ BOM Calendar bug 3832684
6123: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_end_time);
6124:
6125: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_end_date) THEN
6126: FND_MESSAGE.set_name('MRP','MRP_TO_DATE_CHANGED');
6127: FND_MESSAGE.set_token('ORIG_DATE',l_to_end_date);
6121: 1, flm_timezone.server_to_calendar(l_to_end_date));
6122: --TZ BOM Calendar bug 3832684
6123: l_temp_date := flm_timezone.calendar_to_server(l_temp_date,l_to_end_time);
6124:
6125: IF flm_timezone.server_to_calendar(l_temp_date) <> flm_timezone.server_to_calendar(l_to_end_date) THEN
6126: FND_MESSAGE.set_name('MRP','MRP_TO_DATE_CHANGED');
6127: FND_MESSAGE.set_token('ORIG_DATE',l_to_end_date);
6128: FND_MESSAGE.set_token('NEW_DATE',l_temp_date);
6129: l_log_message := FND_MESSAGE.get;
6735: -- Initialization of the Seq Counter table
6736: MRP_UTIL.MRP_LOG('Initialize the counter for line='||fs_report_rec.line_id);
6737: l_build_seq_counter(fs_report_rec.line_id).base_number
6738: := Get_Base_Number(fs_report_rec.line_id,
6739: flm_timezone.client00_in_server(l_flow_schedule_rec.scheduled_completion_date),
6740: l_from_start_date, l_from_end_date); --fix bug#3170105
6741: MRP_UTIL.MRP_LOG('Initialized base number='||
6742: l_build_seq_counter(fs_report_rec.line_id).base_number);
6743: l_build_seq_counter(fs_report_rec.line_id).current_build_seq :=
7091: dbms_sql.column_value(l_cursor, 1, l_flow_schedule_rec.wip_entity_id);
7092: END IF;
7093: EXIT WHEN l_rows = 0;
7094: UPDATE wip_flow_schedules SET status = 2,
7095: date_closed = flm_timezone.sysdate00_in_server --fix bug#3170105
7096: WHERE wip_entity_id = l_flow_schedule_rec.wip_entity_id;
7097: MRP_UTIL.MRP_LOG('Closed Schedule with wip_entity_id:'||to_char(
7098: l_flow_schedule_rec.wip_entity_id));
7099: END LOOP;