[Home] [Help]
3057: OPEN ITEM_SEGMENTS;
3058: FETCH ITEM_SEGMENTS INTO l_item_segments;
3059: CLOSE ITEM_SEGMENTS;
3060:
3061: INSERT INTO mrp_form_query(
3062: query_id,
3063: last_update_date,
3064: last_updated_by,
3065: creation_date,
3112:
3113: PROCEDURE Calculate_Totals(p_query_id NUMBER) IS
3114: BEGIN
3115:
3116: INSERT INTO mrp_form_query(
3117: query_id,
3118: last_update_date,
3119: last_updated_by,
3120: creation_date,
3143: sum(number6),
3144: sum(number7),
3145: sum(number1) + sum(number2) + sum(number3) + sum(number4) +
3146: sum(number5) + sum(number6) + sum(number7)
3147: FROM mrp_form_query
3148: WHERE query_id = p_query_id
3149: AND number14 in (1,2,3)
3150: GROUP BY number14;
3151:
3165: l_first_bucket_client := trunc(p_first_bucket_date);
3166: END IF;
3167: --end bug 3783650
3168:
3169: SELECT mrp_form_query_s.nextval
3170: INTO l_query_id1
3171: FROM dual;
3172:
3173: x_return_status := FND_API.G_RET_STS_SUCCESS;
3172:
3173: x_return_status := FND_API.G_RET_STS_SUCCESS;
3174:
3175: FOR loop in 1..7 LOOP
3176: INSERT INTO mrp_form_query (
3177: query_id,
3178: last_update_date,
3179: last_updated_by,
3180: creation_date,
3216: FETCH flow_schedule_info
3217: INTO flow_activity_rec;
3218:
3219: -- ---------------------------------------
3220: -- Flush previous record to mrp_form_query
3221: -- ---------------------------------------
3222: IF ( (flow_schedule_info%NOTFOUND) OR
3223: (flow_activity_rec.item_id <> l_last_item_id))
3224: AND (l_last_item_id <> -1) THEN
4042: number7 - nvl(number6,0)
4043: FROM wip_lines wl,
4044: mtl_system_items_kfv kfv,
4045: wip_schedule_groups sg,
4046: mrp_form_query
4047: WHERE wl.line_id = number1
4048: AND wl.organization_id = number10
4049: AND kfv.inventory_item_id = number2
4050: AND kfv.organization_id = number10
4061: sum(number4 - number5),
4062: sum(nvl(number6,0)),
4063: sum(number7),
4064: sum(number7 - nvl(number6,0))
4065: FROM mrp_form_query
4066: WHERE query_id = l_query_id
4067: AND number1 = l_line_id
4068: AND number2 = l_item_id
4069: AND nvl(number3,-1) = nvl(l_schedule_group_id,-1);
5107: ,p_from_start_date IN DATE, p_from_end_date IN DATE
5108: ,p_to_start_date IN DATE, p_to_end_date IN DATE) IS --fix bug#3170105
5109:
5110: BEGIN
5111: SELECT mrp_form_query_s.nextval
5112: INTO p_query_id
5113: FROM DUAL;
5114:
5115: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5115: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5116: AND p_category_set_id IS NOT NULL THEN
5117:
5118: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5119: INSERT INTO mrp_form_query
5120: (query_id,
5121: last_update_date,
5122: last_updated_by,
5123: creation_date,
5190:
5191: ELSIF p_category_set_id IS NOT NULL THEN
5192:
5193: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5194: INSERT INTO mrp_form_query
5195: (query_id,
5196: last_update_date,
5197: last_updated_by,
5198: creation_date,
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 */
5262: INSERT INTO mrp_form_query
5263: (query_id,
5264: last_update_date,
5265: last_updated_by,
5266: creation_date,
5347: ,p_to_start_date IN DATE, p_to_end_date IN DATE) IS --fix bug#3170105
5348:
5349: BEGIN
5350:
5351: SELECT mrp_form_query_s.nextval
5352: INTO p_query_id
5353: FROM DUAL;
5354:
5355: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5355: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5356: AND p_category_set_id IS NOT NULL THEN
5357:
5358: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5359: INSERT INTO mrp_form_query
5360: (query_id,
5361: last_update_date,
5362: last_updated_by,
5363: creation_date,
5424:
5425: ELSIF p_category_set_id IS NOT NULL THEN
5426:
5427: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5428: INSERT INTO mrp_form_query
5429: (query_id,
5430: last_update_date,
5431: last_updated_by,
5432: creation_date,
5488:
5489: ELSE
5490:
5491: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5492: INSERT INTO mrp_form_query
5493: (query_id,
5494: last_update_date,
5495: last_updated_by,
5496: creation_date,
5582: l_to_upd_qty NUMBER := 0;
5583:
5584: CURSOR C1 IS
5585: SELECT rowid
5586: FROM mrp_form_query
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)
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
5596: SELECT planned_quantity
5597: FROM wip_flow_schedules
5610:
5611: IF C1%NOTFOUND THEN
5612:
5613: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5614: INSERT INTO mrp_form_query
5615: (query_id,
5616: last_update_date,
5617: last_updated_by,
5618: creation_date,
5665: OPEN C2;
5666: FETCH C2 INTO l_to_upd_qty;
5667: CLOSE C2;
5668:
5669: UPDATE mrp_form_query
5670: SET number6 = decode(number6,NULL,nvl(l_to_upd_qty,0),number6),
5671: number7 = decode(nvl(number7,0),0,nvl(l_to_upd_qty,0),number7) +
5672: p_quantity
5673: WHERE rowid = l_rowid;
5707: l_to_upd_qty NUMBER := 0;
5708:
5709: CURSOR C1 IS
5710: SELECT rowid
5711: FROM mrp_form_query
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)
5732:
5733: IF C1%NOTFOUND THEN
5734:
5735: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5736: INSERT INTO mrp_form_query
5737: (query_id,
5738: last_update_date,
5739: last_updated_by,
5740: creation_date,
5786: OPEN C2;
5787: FETCH C2 INTO l_to_upd_qty;
5788: CLOSE C2;
5789:
5790: UPDATE mrp_form_query
5791: SET number6 = decode(number6,NULL,nvl(l_to_upd_qty,0),number6),
5792: number7 =
5793: decode(nvl(number7,0),0,nvl(l_to_upd_qty,0),number7) + p_quantity
5794: WHERE rowid = l_rowid;
6169: MRP_UTIL.MRP_OUT(' '||l_out_message);
6170:
6171: /*-----------------------------------------------------+
6172: | Do setup for report by inserting existing records |
6173: | into mrp_form_query |
6174: +-----------------------------------------------------*/
6175:
6176: /* this part of code is moved from the buttom of the procedure to here,
6177: because after the schedules got rolled, there would be no flow schedules
6315: G_INTERMEDIATE_ROLL_FORWARDED;
6316: l_flow_schedule_rec.status := 1;
6317:
6318:
6319: -- Call procedure to update mrp_form_query for the report
6320: report_update_WOD(l_report_query_id,
6321: fs_report_rec.line_id,
6322: fs_report_rec.primary_item_id,
6323: fs_report_rec.schedule_group_id,
6382: NULL;
6383: ELSE
6384: IF -(l_update_variance) < (l_planned_quantity -
6385: l_quantity_completed) THEN
6386: -- Call procedure to update mrp_form_query for the report
6387: report_update_WOD(l_report_query_id,
6388: fs_report_rec.line_id,
6389: fs_report_rec.primary_item_id,
6390: fs_report_rec.schedule_group_id,
6412: END IF;
6413: l_update_variance := 0;
6414: ELSIF -(l_update_variance) >= (l_planned_quantity -
6415: l_quantity_completed) THEN
6416: -- Call procedure to update mrp_form_query for the report
6417: report_update_WOD(l_report_query_id,
6418: fs_report_rec.line_id,
6419: fs_report_rec.primary_item_id,
6420: fs_report_rec.schedule_group_id,
6494: l_planned_quantity;
6495: EXIT WHEN C5%NOTFOUND;
6496: IF -(l_update_variance) < (l_planned_quantity -
6497: l_quantity_completed) THEN
6498: -- Call procedure to update mrp_form_query for the report
6499: report_update_WOD(l_report_query_id,
6500: fs_report_rec.line_id,
6501: fs_report_rec.primary_item_id,
6502: fs_report_rec.schedule_group_id,
6524: END IF;
6525: l_update_variance := 0;
6526: ELSIF -(l_update_variance) >= (l_planned_quantity -
6527: l_quantity_completed) THEN
6528: -- Call procedure to update mrp_form_query for the report
6529: report_update_WOD(l_report_query_id,
6530: fs_report_rec.line_id,
6531: fs_report_rec.primary_item_id,
6532: fs_report_rec.schedule_group_id,
6867: p_organization_id)) THEN
6868: RAISE unexpected_error;
6869: END IF;
6870:
6871: -- Call procedure to update mrp_form_query for the report
6872: /** Bug 2558664 - passed old build sequence as report goes haywire
6873: if modified build sequence is passed **/
6874: report_update_WD(l_report_query_id,
6875: fs_report_rec.line_id,