[Home] [Help]
3054: OPEN ITEM_SEGMENTS;
3055: FETCH ITEM_SEGMENTS INTO l_item_segments;
3056: CLOSE ITEM_SEGMENTS;
3057:
3058: INSERT INTO mrp_form_query(
3059: query_id,
3060: last_update_date,
3061: last_updated_by,
3062: creation_date,
3109:
3110: PROCEDURE Calculate_Totals(p_query_id NUMBER) IS
3111: BEGIN
3112:
3113: INSERT INTO mrp_form_query(
3114: query_id,
3115: last_update_date,
3116: last_updated_by,
3117: creation_date,
3140: sum(number6),
3141: sum(number7),
3142: sum(number1) + sum(number2) + sum(number3) + sum(number4) +
3143: sum(number5) + sum(number6) + sum(number7)
3144: FROM mrp_form_query
3145: WHERE query_id = p_query_id
3146: AND number14 in (1,2,3)
3147: GROUP BY number14;
3148:
3162: l_first_bucket_client := trunc(p_first_bucket_date);
3163: END IF;
3164: --end bug 3783650
3165:
3166: SELECT mrp_form_query_s.nextval
3167: INTO l_query_id1
3168: FROM dual;
3169:
3170: x_return_status := FND_API.G_RET_STS_SUCCESS;
3169:
3170: x_return_status := FND_API.G_RET_STS_SUCCESS;
3171:
3172: FOR loop in 1..7 LOOP
3173: INSERT INTO mrp_form_query (
3174: query_id,
3175: last_update_date,
3176: last_updated_by,
3177: creation_date,
3213: FETCH flow_schedule_info
3214: INTO flow_activity_rec;
3215:
3216: -- ---------------------------------------
3217: -- Flush previous record to mrp_form_query
3218: -- ---------------------------------------
3219: IF ( (flow_schedule_info%NOTFOUND) OR
3220: (flow_activity_rec.item_id <> l_last_item_id))
3221: AND (l_last_item_id <> -1) THEN
4039: number7 - nvl(number6,0)
4040: FROM wip_lines wl,
4041: mtl_system_items_kfv kfv,
4042: wip_schedule_groups sg,
4043: mrp_form_query
4044: WHERE wl.line_id = number1
4045: AND wl.organization_id = number10
4046: AND kfv.inventory_item_id = number2
4047: AND kfv.organization_id = number10
4058: sum(number4 - number5),
4059: sum(nvl(number6,0)),
4060: sum(number7),
4061: sum(number7 - nvl(number6,0))
4062: FROM mrp_form_query
4063: WHERE query_id = l_query_id
4064: AND number1 = l_line_id
4065: AND number2 = l_item_id
4066: AND nvl(number3,-1) = nvl(l_schedule_group_id,-1);
5104: ,p_from_start_date IN DATE, p_from_end_date IN DATE
5105: ,p_to_start_date IN DATE, p_to_end_date IN DATE) IS --fix bug#3170105
5106:
5107: BEGIN
5108: SELECT mrp_form_query_s.nextval
5109: INTO p_query_id
5110: FROM DUAL;
5111:
5112: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5112: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5113: AND p_category_set_id IS NOT NULL THEN
5114:
5115: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5116: INSERT INTO mrp_form_query
5117: (query_id,
5118: last_update_date,
5119: last_updated_by,
5120: creation_date,
5187:
5188: ELSIF p_category_set_id IS NOT NULL THEN
5189:
5190: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5191: INSERT INTO mrp_form_query
5192: (query_id,
5193: last_update_date,
5194: last_updated_by,
5195: creation_date,
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 */
5259: INSERT INTO mrp_form_query
5260: (query_id,
5261: last_update_date,
5262: last_updated_by,
5263: creation_date,
5344: ,p_to_start_date IN DATE, p_to_end_date IN DATE) IS --fix bug#3170105
5345:
5346: BEGIN
5347:
5348: SELECT mrp_form_query_s.nextval
5349: INTO p_query_id
5350: FROM DUAL;
5351:
5352: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5352: IF (p_from_category IS NOT NULL OR p_to_category IS NOT NULL)
5353: AND p_category_set_id IS NOT NULL THEN
5354:
5355: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5356: INSERT INTO mrp_form_query
5357: (query_id,
5358: last_update_date,
5359: last_updated_by,
5360: creation_date,
5421:
5422: ELSIF p_category_set_id IS NOT NULL THEN
5423:
5424: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5425: INSERT INTO mrp_form_query
5426: (query_id,
5427: last_update_date,
5428: last_updated_by,
5429: creation_date,
5485:
5486: ELSE
5487:
5488: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5489: INSERT INTO mrp_form_query
5490: (query_id,
5491: last_update_date,
5492: last_updated_by,
5493: creation_date,
5579: l_to_upd_qty NUMBER := 0;
5580:
5581: CURSOR C1 IS
5582: SELECT rowid
5583: FROM mrp_form_query
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)
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
5593: SELECT planned_quantity
5594: FROM wip_flow_schedules
5607:
5608: IF C1%NOTFOUND THEN
5609:
5610: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5611: INSERT INTO mrp_form_query
5612: (query_id,
5613: last_update_date,
5614: last_updated_by,
5615: creation_date,
5662: OPEN C2;
5663: FETCH C2 INTO l_to_upd_qty;
5664: CLOSE C2;
5665:
5666: UPDATE mrp_form_query
5667: SET number6 = decode(number6,NULL,nvl(l_to_upd_qty,0),number6),
5668: number7 = decode(nvl(number7,0),0,nvl(l_to_upd_qty,0),number7) +
5669: p_quantity
5670: WHERE rowid = l_rowid;
5704: l_to_upd_qty NUMBER := 0;
5705:
5706: CURSOR C1 IS
5707: SELECT rowid
5708: FROM mrp_form_query
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)
5729:
5730: IF C1%NOTFOUND THEN
5731:
5732: /* Bug 2998385: Added not exists from MTL_TRANSACTIONS_INTERFACE */
5733: INSERT INTO mrp_form_query
5734: (query_id,
5735: last_update_date,
5736: last_updated_by,
5737: creation_date,
5783: OPEN C2;
5784: FETCH C2 INTO l_to_upd_qty;
5785: CLOSE C2;
5786:
5787: UPDATE mrp_form_query
5788: SET number6 = decode(number6,NULL,nvl(l_to_upd_qty,0),number6),
5789: number7 =
5790: decode(nvl(number7,0),0,nvl(l_to_upd_qty,0),number7) + p_quantity
5791: WHERE rowid = l_rowid;
6166: MRP_UTIL.MRP_OUT(' '||l_out_message);
6167:
6168: /*-----------------------------------------------------+
6169: | Do setup for report by inserting existing records |
6170: | into mrp_form_query |
6171: +-----------------------------------------------------*/
6172:
6173: /* this part of code is moved from the buttom of the procedure to here,
6174: because after the schedules got rolled, there would be no flow schedules
6312: G_INTERMEDIATE_ROLL_FORWARDED;
6313: l_flow_schedule_rec.status := 1;
6314:
6315:
6316: -- Call procedure to update mrp_form_query for the report
6317: report_update_WOD(l_report_query_id,
6318: fs_report_rec.line_id,
6319: fs_report_rec.primary_item_id,
6320: fs_report_rec.schedule_group_id,
6379: NULL;
6380: ELSE
6381: IF -(l_update_variance) < (l_planned_quantity -
6382: l_quantity_completed) THEN
6383: -- Call procedure to update mrp_form_query for the report
6384: report_update_WOD(l_report_query_id,
6385: fs_report_rec.line_id,
6386: fs_report_rec.primary_item_id,
6387: fs_report_rec.schedule_group_id,
6409: END IF;
6410: l_update_variance := 0;
6411: ELSIF -(l_update_variance) >= (l_planned_quantity -
6412: l_quantity_completed) THEN
6413: -- Call procedure to update mrp_form_query for the report
6414: report_update_WOD(l_report_query_id,
6415: fs_report_rec.line_id,
6416: fs_report_rec.primary_item_id,
6417: fs_report_rec.schedule_group_id,
6491: l_planned_quantity;
6492: EXIT WHEN C5%NOTFOUND;
6493: IF -(l_update_variance) < (l_planned_quantity -
6494: l_quantity_completed) THEN
6495: -- Call procedure to update mrp_form_query for the report
6496: report_update_WOD(l_report_query_id,
6497: fs_report_rec.line_id,
6498: fs_report_rec.primary_item_id,
6499: fs_report_rec.schedule_group_id,
6521: END IF;
6522: l_update_variance := 0;
6523: ELSIF -(l_update_variance) >= (l_planned_quantity -
6524: l_quantity_completed) THEN
6525: -- Call procedure to update mrp_form_query for the report
6526: report_update_WOD(l_report_query_id,
6527: fs_report_rec.line_id,
6528: fs_report_rec.primary_item_id,
6529: fs_report_rec.schedule_group_id,
6864: p_organization_id)) THEN
6865: RAISE unexpected_error;
6866: END IF;
6867:
6868: -- Call procedure to update mrp_form_query for the report
6869: /** Bug 2558664 - passed old build sequence as report goes haywire
6870: if modified build sequence is passed **/
6871: report_update_WD(l_report_query_id,
6872: fs_report_rec.line_id,