[Home] [Help]
1596: --in the UPDATE statement below
1597: l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type := Convert_Miss_To_Null (p_flow_schedule_rec);
1598: BEGIN
1599:
1600: UPDATE WIP_FLOW_SCHEDULES
1601: SET ALTERNATE_BOM_DESIGNATOR = l_flow_schedule_rec.alternate_bom_designator
1602: , ALTERNATE_ROUTING_DESIGNATOR = l_flow_schedule_rec.alternate_routing_desig
1603: , ATTRIBUTE1 = l_flow_schedule_rec.attribute1
1604: , ATTRIBUTE10 = l_flow_schedule_rec.attribute10
1697: --in the INSERT statement below
1698: l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type := Convert_Miss_To_Null (p_flow_schedule_rec);
1699: BEGIN
1700:
1701: INSERT INTO WIP_FLOW_SCHEDULES
1702: ( ALTERNATE_BOM_DESIGNATOR
1703: , ALTERNATE_ROUTING_DESIGNATOR
1704: , ATTRIBUTE1
1705: , ATTRIBUTE10
1874: )
1875: IS
1876: BEGIN
1877:
1878: DELETE FROM WIP_FLOW_SCHEDULES
1879: WHERE WIP_ENTITY_ID = p_wip_entity_id
1880: ;
1881:
1882: EXCEPTION
2041: , l_flow_schedule_rec.task_id
2042: , l_flow_schedule_rec.wip_entity_id
2043: , l_flow_schedule_rec.end_item_unit_number
2044: , l_flow_schedule_rec.quantity_scrapped
2045: FROM WIP_FLOW_SCHEDULES
2046: WHERE WIP_ENTITY_ID = p_wip_entity_id
2047: ;
2048:
2049: RETURN l_flow_schedule_rec;
2214: , l_flow_schedule_rec.task_id
2215: , l_flow_schedule_rec.wip_entity_id
2216: , l_flow_schedule_rec.end_item_unit_number
2217: , l_flow_schedule_rec.quantity_scrapped
2218: FROM WIP_FLOW_SCHEDULES
2219: WHERE WIP_ENTITY_ID = p_flow_schedule_rec.wip_entity_id
2220: FOR UPDATE NOWAIT;
2221:
2222: -- Row locked. Compare IN attributes to DB attributes.
3292: BEGIN
3293:
3294: FND_MSG_PUB.Initialize;
3295:
3296: UPDATE wip_flow_schedules
3297: SET planned_quantity = planned_quantity + p_quantity
3298: WHERE wip_entity_id = p_wip_entity_id;
3299:
3300: x_return_status := FND_API.G_RET_STS_SUCCESS;
3338: p_quantity IN NUMBER ) IS
3339:
3340: BEGIN
3341:
3342: UPDATE wip_flow_schedules
3343: SET planned_quantity = planned_quantity + p_quantity
3344: WHERE wip_entity_id = p_wip_entity_id;
3345:
3346: EXCEPTION
3369: BEGIN
3370:
3371: FND_MSG_PUB.Initialize;
3372:
3373: DELETE FROM WIP_FLOW_SCHEDULES
3374: WHERE WIP_ENTITY_ID = p_wip_entity_id
3375: AND status = 1
3376: AND nvl(transacted_flag, 'N') = 'N'
3377: AND quantity_completed = 0
3640: --fix bug#3170105
3641: /* Bug 3267578: Added not exists from MTL_TRANSACTIONS_INTERFACE */
3642: l_sql_stmt :=
3643: 'SELECT wfs.wip_entity_id, wfs.demand_source_line, msi.replenish_to_order_flag, msi.build_in_wip_flag ' ||
3644: ' FROM wip_flow_schedules wfs, mtl_system_items msi ' ||
3645: ' WHERE wfs.primary_item_id = msi.inventory_item_id '||
3646: ' AND wfs.organization_id = msi.organization_id ' ||
3647: ' AND wfs.organization_id = to_char(:organization_id) ' ||
3648: l_where_clause ||
3687: wip_picking_pvt.cancel_allocations(l_wip_entity_id, 4, null, l_return_status, l_msg_data);
3688: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3689: MRP_UTIL.MRP_LOG(l_msg_data);
3690: ELSE
3691: DELETE FROM wip_flow_schedules
3692: WHERE wip_entity_id = l_wip_entity_id AND organization_id = p_organization_id;
3693:
3694: l_rows_processed := l_rows_processed + 1;
3695:
3895: CURSOR C1(p_line_id NUMBER, p_item_id NUMBER, p_schedule_group_id NUMBER,
3896: p_to_start_date DATE, p_to_end_date DATE) --fix bug#3170105
3897: IS
3898: SELECT nvl(sum(planned_quantity),0)
3899: FROM wip_flow_schedules
3900: WHERE organization_id = p_organization_id
3901: AND line_id = p_line_id
3902: AND primary_item_id = p_item_id
3903: AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
3910: p_to_start_date DATE, p_to_end_date DATE) --fix bug#3170105
3911: IS
3912: SELECT wip_entity_id,
3913: planned_quantity
3914: FROM wip_flow_schedules
3915: WHERE organization_id = p_organization_id
3916: AND line_id = p_line_id
3917: AND primary_item_id = p_item_id
3918: AND nvl(schedule_group_id,-1) = nvl(p_schedule_group_id,-1)
3927: SELECT wip_entity_id,
3928: scheduled_completion_date,
3929: planned_quantity,
3930: quantity_completed
3931: FROM wip_flow_schedules
3932: WHERE organization_id = p_organization_id
3933: AND line_id = p_line_id
3934: AND primary_item_id = p_item_id
3935: AND demand_source_header_id IS NULL
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
3957: AND demand_source_header_id IS NULL
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
3979: AND scheduled_completion_date BETWEEN
3986: IS
3987: SELECT wip_entity_id,
3988: scheduled_completion_date, --fix bug#3170105
3989: planned_quantity
3990: FROM wip_flow_schedules
3991: WHERE organization_id = p_organization_id
3992: AND line_id = p_line_id
3993: AND primary_item_id = p_item_id
3994: AND build_sequence IS NULL
4005: IS
4006: SELECT wip_entity_id,
4007: scheduled_completion_date, --fix bug#3170105
4008: planned_quantity
4009: FROM wip_flow_schedules
4010: WHERE organization_id = p_organization_id
4011: AND line_id = p_line_id
4012: AND primary_item_id = p_item_id
4013: AND planned_quantity > 0
4071: RETURN NUMBER IS
4072: CURSOR Get_Target_Max_Build_Seq(p_line_id NUMBER, p_target_date DATE)
4073: IS
4074: SELECT nvl(max(build_sequence),0)
4075: FROM wip_flow_schedules
4076: WHERE organization_id = p_organization_id
4077: AND line_id = p_line_id
4078: AND build_sequence is NOT NULL
4079: AND (ROLL_FORWARDED_FLAG <> G_INTERMEDIATE_ROLL_FORWARDED OR
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
4088: SELECT count(*)
4089: FROM wip_flow_schedules
4090: WHERE organization_id = p_organization_id
4091: AND line_id = p_line_id
4092: AND nvl(planned_quantity,0) > nvl(quantity_completed,0)
4093: /** Forward ported bug 3055939 */
4116: CURSOR Check_Schedule_Roll_Types
4117: (p_line_id NUMBER ,p_scheduled_completion_date DATE)
4118: IS
4119: SELECT count(distinct(nvl(roll_forwarded_flag,-1)))
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
4127: CURSOR Check_Schedule_Distinct_Type
4128: (p_line_id NUMBER ,p_scheduled_completion_date DATE)
4129: IS
4130: SELECT distinct(nvl(roll_forwarded_flag,-1))
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
4143: CURSOR Check_Last_Schedule
4144: (p_line_id NUMBER ,p_scheduled_completion_date DATE)
4145: IS
4146: SELECT MAX(scheduled_completion_date)
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
4336: 'SELECT fs.wip_entity_id ' ||
4337: ' FROM wip_lines line, '||
4338: l_cat_table_sql ||
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 ' ||
4493: ' sum(nvl(fs.quantity_completed,0)) ' ||
4494: ' FROM wip_lines line, '||
4495: l_cat_table_sql ||
4496: ' mtl_system_items_kfv msi, '||
4497: ' wip_flow_schedules fs ' ||
4498: ' WHERE fs.organization_id = to_char( :organization_id ) ' ||
4499: l_where_clause ||
4500: ' AND (nvl(fs.planned_quantity,0) - nvl(fs.quantity_completed,0)) <> 0 ' ||
4501: ' AND fs.scheduled_completion_date between ' ||
4697: ' fs.WIP_ENTITY_ID '||
4698: ' FROM wip_lines line, '||
4699: l_cat_table_sql ||
4700: ' mtl_system_items_kfv msi, '||
4701: ' wip_flow_schedules fs ' ||
4702: ' WHERE fs.organization_id = to_char(:organization_id) '||
4703: l_where_clause ||
4704: ' AND (nvl(fs.planned_quantity,0) - nvl(fs.quantity_completed,0)) > 0 ' ||
4705: ' AND fs.scheduled_completion_date between ' ||
4847:
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:
4891: fs_report_rec REPORT_REC_TYPE;
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:
5031: ' SELECT fs.wip_entity_id ' ||
5032: ' FROM wip_lines line, '||
5033: l_cat_table_sql ||
5034: ' mtl_system_items_kfv msi, '||
5035: ' wip_flow_schedules fs ' ||
5036: ' WHERE fs.organization_id = to_char(:organization_id) ' ||
5037: l_where_clause ||
5038: l_select ||
5039: ' AND fs.scheduled_completion_date between ' ||
5149: FROM wip_lines wl,
5150: mtl_system_items_kfv kfv,
5151: mtl_categories_kfv cat,
5152: mtl_item_categories mic,
5153: wip_flow_schedules fs1
5154: WHERE wl.line_id = fs1.line_id
5155: AND fs1.demand_source_header_id IS NULL
5156: -- AND nvl(fs1.closed,0) = 0
5157: AND wl.organization_id = fs1.organization_id
5223: fs1.organization_id
5224: FROM wip_lines wl,
5225: mtl_system_items_kfv kfv,
5226: mtl_item_categories mic,
5227: wip_flow_schedules fs1
5228: WHERE wl.line_id = fs1.line_id
5229: AND fs1.demand_source_header_id IS NULL
5230: -- AND nvl(fs1.closed,0) = 0
5231: AND wl.organization_id = fs1.organization_id
5290: 0,
5291: fs1.organization_id
5292: FROM wip_lines wl,
5293: mtl_system_items_kfv kfv,
5294: wip_flow_schedules fs1
5295: WHERE wl.line_id = fs1.line_id
5296: AND fs1.demand_source_header_id IS NULL
5297: -- AND nvl(fs1.closed,0) = 0
5298: AND wl.organization_id = fs1.organization_id
5389: FROM wip_lines wl,
5390: mtl_system_items_kfv kfv,
5391: mtl_categories_kfv cat,
5392: mtl_item_categories mic,
5393: wip_flow_schedules fs1
5394: WHERE wl.line_id = fs1.line_id
5395: AND wl.organization_id = fs1.organization_id
5396: AND wl.line_code
5397: BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
5457: NVL(fs1.build_sequence,0)
5458: FROM wip_lines wl,
5459: mtl_system_items_kfv kfv,
5460: mtl_item_categories mic,
5461: wip_flow_schedules fs1
5462: WHERE wl.line_id = fs1.line_id
5463: AND wl.organization_id = fs1.organization_id
5464: AND wl.line_code
5465: BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
5520: fs1.organization_id,
5521: NVL(fs1.build_sequence,0)
5522: FROM wip_lines wl,
5523: mtl_system_items_kfv kfv,
5524: wip_flow_schedules fs1
5525: WHERE wl.line_id = fs1.line_id
5526: AND wl.organization_id = fs1.organization_id
5527: AND wl.line_code
5528: BETWEEN NVL(p_from_line,wl.line_code) AND NVL(p_to_line,wl.line_code)
5590: --mrp form query dates are in client timezone
5591:
5592: CURSOR C2 IS
5593: SELECT planned_quantity
5594: FROM wip_flow_schedules
5595: WHERE line_id = p_line_id
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)
5642: 0,
5643: p_quantity,
5644: p_organization_id,
5645: NVL(p_build_sequence,0)
5646: FROM wip_flow_schedules fs
5647: WHERE fs.line_id = p_line_id
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)
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)
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
5768: WHERE fs.line_id = p_line_id
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)
5892: project_id,
5893: project_id,
5894: task_id,
5895: task_id
5896: FROM wip_flow_schedules
5897: WHERE wip_entity_id = p_wip_entity_id
5898: AND planned_quantity > quantity_completed;
5899:
5900: END relieve_rolled_schedules;
6583: END IF;
6584: END IF;
6585:
6586: END LOOP;
6587: UPDATE wip_flow_schedules set roll_forwarded_flag =
6588: G_ROLL_FORWARDED
6589: where roll_forwarded_flag =
6590: G_INTERMEDIATE_ROLL_FORWARDED;
6591: ELSIF p_spread_qty = 2 THEN
6900: SELECT auto_replenish,
6901: wip_entity_id
6902: INTO l_auto_replenish,
6903: l_wip_entity_id
6904: FROM wip_flow_schedules
6905: WHERE schedule_number = fs_report_rec.schedule_number
6906: AND organization_id = p_organization_id;
6907:
6908: /* Update the auto_replenish flag of new schedules with the old value,
6907:
6908: /* Update the auto_replenish flag of new schedules with the old value,
6909: if auto_replenish is not null for old flow schedule */
6910: IF l_auto_replenish IS NOT NULL THEN
6911: UPDATE wip_flow_schedules
6912: SET auto_replenish = l_auto_replenish
6913: WHERE schedule_number = l_x_flow_schedule_rec.schedule_number
6914: AND organization_id = p_organization_id;
6915:
6976: | forwarded to avoid bumping; |
6977: +-----------------------------------------------------------------*/
6978:
6979: /******************* Commented ********************
6980: UPDATE wip_flow_schedules set roll_forwarded_flag =
6981: G_ROLL_FORWARDED
6982: where roll_forwarded_flag =
6983: G_INTERMEDIATE_ROLL_FORWARDED;
6984: **************************************************/
6993: l_base_number := nvl(l_build_seq_counter(l_index).base_number,0);
6994: l_total_count := nvl(l_build_seq_counter(l_index).current_build_seq,0) - l_base_number;
6995: MRP_UTIL.MRP_LOG('Shift each BUILD_SEQ of existing eligible schedules by '||l_total_count);
6996:
6997: UPDATE wip_flow_schedules
6998: set BUILD_SEQUENCE = BUILD_SEQUENCE + l_total_count
6999: WHERE organization_id = p_organization_id
7000: AND line_id = l_index
7001: AND nvl(planned_quantity,0) > nvl(quantity_completed,0)
7008: | Update the newly forwarded schedules as ROLL FORWARDED; |
7009: | Deduct the base number from the build_sequence |
7010: +-----------------------------------------------------------------*/
7011:
7012: UPDATE wip_flow_schedules set
7013: roll_forwarded_flag = G_ROLL_FORWARDED,
7014: build_sequence = build_sequence - l_base_number
7015: where roll_forwarded_flag = G_INTERMEDIATE_ROLL_FORWARDED
7016: and line_id = l_index
7030: /* Added for project 'Roll Flow Schedules: Maintain Schedule Number'
7031: Logic to swap the new schedule number with old schedule number. */
7032: --First step is to change the schedule number of the rolled FS
7033: --to a temporaryones to avoid violating the unique contraints
7034: --on Schedule Number and Org ID and this is done for WIP_FLOW_SCHEDULES
7035: FORALL i IN 1..l_loop_count
7036: UPDATE wip_flow_schedules
7037: SET schedule_number=('?*?'||oldFSSchNum(i))
7038: WHERE wip_entity_id=oldFSWipId(i)
7032: --First step is to change the schedule number of the rolled FS
7033: --to a temporaryones to avoid violating the unique contraints
7034: --on Schedule Number and Org ID and this is done for WIP_FLOW_SCHEDULES
7035: FORALL i IN 1..l_loop_count
7036: UPDATE wip_flow_schedules
7037: SET schedule_number=('?*?'||oldFSSchNum(i))
7038: WHERE wip_entity_id=oldFSWipId(i)
7039: AND organization_id=p_organization_id;
7040:
7038: WHERE wip_entity_id=oldFSWipId(i)
7039: AND organization_id=p_organization_id;
7040:
7041: --Second Step is to Update the New FS created to use the old rolled FS
7042: --Schedule Number. This is need to be done for WIP_FLOW_SCHEDULES
7043: FORALL i IN 1..l_loop_count
7044: UPDATE wip_flow_schedules
7045: SET schedule_number=oldFSSchNum(i)
7046: WHERE wip_entity_id=newFSWipId(i)
7040:
7041: --Second Step is to Update the New FS created to use the old rolled FS
7042: --Schedule Number. This is need to be done for WIP_FLOW_SCHEDULES
7043: FORALL i IN 1..l_loop_count
7044: UPDATE wip_flow_schedules
7045: SET schedule_number=oldFSSchNum(i)
7046: WHERE wip_entity_id=newFSWipId(i)
7047: AND organization_id=p_organization_id;
7048:
7046: WHERE wip_entity_id=newFSWipId(i)
7047: AND organization_id=p_organization_id;
7048:
7049: --Third Step is to Update the Old rolled FS to use the Newly created FS
7050: --Schedule Number. This is need to be done for WIP_FLOW_SCHEDULES
7051: FORALL i IN 1..l_loop_count
7052: UPDATE wip_flow_schedules
7053: SET schedule_number=newFSSchNum(i)
7054: WHERE wip_entity_id=oldFSWipId(i)
7048:
7049: --Third Step is to Update the Old rolled FS to use the Newly created FS
7050: --Schedule Number. This is need to be done for WIP_FLOW_SCHEDULES
7051: FORALL i IN 1..l_loop_count
7052: UPDATE wip_flow_schedules
7053: SET schedule_number=newFSSchNum(i)
7054: WHERE wip_entity_id=oldFSWipId(i)
7055: AND organization_id=p_organization_id;
7056:
7090: IF l_rows > 0 THEN
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));
7468: IS
7469:
7470: CURSOR C1 IS
7471: SELECT wip_entity_id, schedule_number
7472: FROM wip_flow_schedules
7473: WHERE request_id = USERENV( 'SESSIONID' );
7474:
7475: CURSOR C2 IS
7476: SELECT sum(planned_quantity), demand_source_line
7473: WHERE request_id = USERENV( 'SESSIONID' );
7474:
7475: CURSOR C2 IS
7476: SELECT sum(planned_quantity), demand_source_line
7477: FROM wip_flow_schedules
7478: WHERE organization_id = p_org_id
7479: AND demand_source_header_id IS NULL
7480: AND request_id = USERENV('SESSIONID')
7481: GROUP BY demand_source_line;
7503: THEN
7504: l_schedule_number_out := NULL;
7505: l_error := WIP_FLOW_DERIVE.schedule_number(l_schedule_number_out);
7506: IF l_error = 1 THEN
7507: UPDATE wip_flow_schedules
7508: SET schedule_number = l_schedule_number_out
7509: WHERE wip_entity_id = l_wip_entity_id;
7510:
7511: UPDATE wip_entities
7530: WHERE transaction_id = l_trans_id;
7531: END LOOP;
7532: CLOSE C2;
7533:
7534: UPDATE wip_flow_schedules
7535: SET request_id = NULL,
7536: scheduled_flag = 1
7537: WHERE request_id = USERENV('SESSIONID');
7538:
7541: PROCEDURE Explode_New_Items
7542: IS
7543: CURSOR C1 IS
7544: SELECT distinct primary_item_id, organization_id, alternate_bom_designator
7545: FROM wip_flow_schedules
7546: WHERE request_id = USERENV('SESSIONID');
7547:
7548: l_item_id NUMBER;
7549: l_org_id NUMBER;
7857: (planned_quantity-quantity_completed)),
7858: decode(status,2,quantity_completed,
7859: planned_quantity))),0)
7860: INTO l_lines(l_index).fs_quantity
7861: FROM wip_flow_schedules
7862: WHERE primary_item_id = l_item_id
7863: AND demand_source_line = to_char(l_lines(l_index).line_id)
7864: AND demand_source_type = p_demand_source_type
7865: AND ((demand_source_delivery IS NULL) or
7900: SELECT nvl(sum(decode(nvl(p_use_open_quantity,'N'),'Y',decode(status,2,0,
7901: (planned_quantity-quantity_completed)),
7902: decode(status,2,quantity_completed,
7903: planned_quantity))),0)
7904: FROM wip_flow_schedules
7905: WHERE demand_source_line = p_demand_source_line
7906: AND demand_source_type = p_demand_source_type
7907: AND ((demand_source_delivery IS NULL)or(demand_source_delivery = p_demand_source_delivery));
7908:
8036: AND demand_source_line_id = p_line_id
8037: AND ( nvl(p_use_open_quantity,'N') = 'N'
8038: and (supply_source_header_id is NULL
8039: or not exists (select wip_entity_id
8040: from wip_flow_schedules
8041: where wip_entity_id = supply_source_header_id
8042: and demand_source_line = nvl(p_line_id,-1))
8043: )
8044: or nvl(p_use_open_quantity,'N') = 'Y') ; /*Bug 3042045 - do not check supply source for ATO items*/