[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM MRP_FLOW_DEMANDS
WHERE wip_entity_id IN ( SELECT wip_entity_id
FROM MRP_AD_FLOW_SCHDS
WHERE RN= i_RN);
lv_temp_sql_stmt := ' DELETE /*+ parallel(mfd) */ '
||' FROM MRP_FLOW_DEMANDS MFD '
||' WHERE EXISTS (SELECT 1 '
||' FROM MRP_SN_INV_COMPS MSIC '
||' WHERE MSIC.RN = :i_RN'
||' AND MSIC.BILL_SEQUENCE_ID = MFD.BILL_SEQUENCE_ID) ';
lv_temp_sql_stmt := ' SELECT count(*) '
||' FROM MRP_SN_FLOW_SCHDS '
||' WHERE RN >= :i_RN '
||' AND PLANNED_QUANTITY > QUANTITY_COMPLETED ';
lv_temp_sql_stmt := ' DELETE FROM MRP_FLOW_DEMANDS MFD '
||' WHERE EXISTS (SELECT 1 FROM MRP_SN_FLOW_SCHDS MFS '
||' WHERE MFS.RN >= :i_RN '
||' AND MFS.PLANNED_QUANTITY > MFS.QUANTITY_COMPLETED '
||' AND MFS.WIP_ENTITY_ID = MFD.WIP_ENTITY_ID) ';
g_wip_entity_id_arr.DELETE;
SELECT DISTINCT
BILL_SEQUENCE_ID
FROM MRP_SN_INV_COMPS
WHERE RN = i_RN_index
;
SELECT DISTINCT -- for performance we can remove distinct
wip_entity_id
FROM MRP_FLOW_DEMANDS
WHERE BILL_SEQUENCE_ID = i_bill_sequence_id;
' SELECT /*+ index(bic bom_inv_comps_sn_n1) */ DISTINCT'
||' BILL_SEQUENCE_ID '
||' FROM MRP_SN_INV_COMPS bic'
||' WHERE RN = '||i_RN;
SELECT
ORGANIZATION_ID,
PLANNED_QUANTITY,
PRIMARY_ITEM_ID,
QUANTITY_COMPLETED,
SCHEDULED_COMPLETION_DATE,
SCHEDULED_START_DATE,
WIP_ENTITY_ID,
i_RN
INTO
g_organization_id,
g_planned_quantity,
g_primary_item_id,
g_quantity_completed,
g_scheduled_completion_date,
g_scheduled_start_date,
g_wip_entity_id,
g_RN
FROM MRP_SN_FLOW_SCHDS
WHERE wip_entity_id = l_index
AND PLANNED_QUANTITY > QUANTITY_COMPLETED;
' SELECT'
||' ORGANIZATION_ID,'
||' PLANNED_QUANTITY,'
||' PRIMARY_ITEM_ID,'
||' QUANTITY_COMPLETED,'
||' SCHEDULED_COMPLETION_DATE,'
||' SCHEDULED_START_DATE,'
||' WIP_ENTITY_ID,'
|| i_RN
||' FROM MRP_SN_FLOW_SCHDS'
||' WHERE wip_entity_id = '||l_index
||' AND PLANNED_QUANTITY > QUANTITY_COMPLETED';
SELECT
ORGANIZATION_ID,
PLANNED_QUANTITY,
PRIMARY_ITEM_ID,
QUANTITY_COMPLETED,
SCHEDULED_COMPLETION_DATE,
SCHEDULED_START_DATE,
WIP_ENTITY_ID,
RN
FROM MRP_SN_FLOW_SCHDS
WHERE RN >= i_RN_index
AND PLANNED_QUANTITY > QUANTITY_COMPLETED;
' SELECT'
||' ORGANIZATION_ID,'
||' PLANNED_QUANTITY,'
||' PRIMARY_ITEM_ID,'
||' QUANTITY_COMPLETED,'
||' SCHEDULED_COMPLETION_DATE,'
||' SCHEDULED_START_DATE,'
||' WIP_ENTITY_ID,'
||' RN'
||' FROM MRP_SN_FLOW_SCHDS'
||' WHERE RN >= '||i_RN
||' AND PLANNED_QUANTITY > QUANTITY_COMPLETED';
Insert_Demands(parent_item,
l_level,
o_return_code,
o_error_message);
SELECT
bic.component_item_id,
bic.component_quantity
FROM
MRP_SN_BOMS bom,
MRP_SN_INV_COMPS bic
WHERE
bom.assembly_item_id = i_parent_item
AND bom.organization_id = g_organization_id
AND bom.alternate_bom_designator IS NULL --- primary bill
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND bic.effectivity_date < g_scheduled_completion_date
AND NVL(bic.disable_date, g_scheduled_completion_date + 1)
> g_scheduled_completion_date
AND bic.WIP_SUPPLY_TYPE = 6;
' SELECT'
||' bic.component_item_id,'
||' bic.component_quantity'
||' FROM'
||' MRP_SN_BOMS bom,'
||' MRP_SN_INV_COMPS bic,'
||' MTL_SYSTEM_ITEMS msi1,'
||' MTL_SYSTEM_ITEMS msi2'
||' WHERE'
||' bom.assembly_item_id = :inventory_item_id'
||' AND bom.organization_id = :organization_id'
||' AND msi1.inventory_item_id = bom.assembly_item_id'
||' AND msi1.organization_id = bom.organization_id'
||' AND bom.alternate_bom_designator IS NULL '
||' AND bic.bill_sequence_id = bom.common_bill_sequence_id'
||' AND bic.effectivity_date < :g_scheduled_completion_date '
||' AND NVL(bic.disable_date, :g_scheduled_completion_date + 1) '
||' > :g_scheduled_completion_date '
||' AND bic.WIP_SUPPLY_TYPE = 6'
||' AND msi2.inventory_item_id = bic.component_item_id'
||' AND msi2.organization_id = msi1.organization_id'
||' AND NOT (msi1.AUTO_CREATED_CONFIG_FLAG=''Y'' and msi1.base_item_id is NOT NULL'
||' and (msi2.BOM_ITEM_TYPE = 1 OR msi2.BOM_ITEM_TYPE = 2))';
PROCEDURE Insert_Demands(i_parent_items IN PARENT_ITEM_TYPE,
i_level IN NUMBER,
o_return_code OUT NOCOPY NUMBER,
o_error_message OUT NOCOPY VARCHAR2) IS
TYPE CurTyp IS REF CURSOR;
' SELECT :g_organization_id '
||' ,bic.component_quantity * :parent_planned_quantity,'
||' bic.Component_Item_ID, '
||' bic.component_quantity * :parent_quantity_completed,'
||' :g_scheduled_completion_date,'
||' :g_scheduled_start_date,'
||' :g_wip_entity_id,'
||' :i_level,'
||' bic.WIP_SUPPLY_TYPE, '
||' bom.bill_sequence_id,'
||' :g_RN '
||' FROM'
||' MRP_SN_BOMS bom,'
||' MRP_SN_INV_COMPS bic'
||' WHERE'
||' bom.assembly_item_id = :parent_inventory_item_id'
||' AND bom.organization_id = :g_organization_id'
||' AND bom.alternate_bom_designator IS NULL'
||' AND bic.bill_sequence_id = bom.common_bill_sequence_id'
||' AND bic.effectivity_date < :g_scheduled_completion_date'
||' AND NVL(bic.disable_date, :g_scheduled_completion_date + 1) '
||' > :g_scheduled_completion_date '
||' AND bic.wip_supply_type <> 6';
INSERT INTO MRP_FLOW_DEMANDS(
organization_id,
planned_quantity,
primary_item_id,
quantity_completed,
scheduled_completion_date,
scheduled_start_date,
wip_entity_id,
plan_level,
wip_supply_type,
bill_sequence_id, -- bill identifier
RN)
VALUES(
lv_organization_id_tab(k),
lv_planned_quantity_tab(k),
lv_primary_item_id_tab(k),
lv_quantity_completed_tab(k),
lv_scheduled_comp_date_tab(k),
lv_scheduled_start_date_tab(k),
lv_wip_entity_id_tab(k),
lv_plan_level_tab(k),
lv_wip_supply_type_tab(k),
lv_bill_sequence_id_tab(k),
lv_RN);
INSERT INTO MRP_FLOW_DEMANDS(
organization_id,
planned_quantity,
primary_item_id,
quantity_completed,
scheduled_completion_date,
scheduled_start_date,
wip_entity_id,
plan_level,
wip_supply_type,
bill_sequence_id, -- bill identifier
RN)
SELECT
g_organization_id,
bic.component_quantity * i_parent_items.planned_quantity(l_index),
bic.Component_Item_ID, -- i_parent_items.inventory_item_id(l_index)
bic.component_quantity * i_parent_items.quantity_completed(l_index),
g_scheduled_completion_date,
g_scheduled_start_date,
g_wip_entity_id,
i_level,
bic.WIP_SUPPLY_TYPE,
bom.bill_sequence_id,
g_RN
FROM
MRP_SN_BOMS bom,
MRP_SN_INV_COMPS bic
WHERE
bom.assembly_item_id = i_parent_items.inventory_item_id(l_index)
AND bom.organization_id = g_organization_id
AND bom.alternate_bom_designator IS NULL
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND bic.effectivity_date < g_scheduled_completion_date
AND NVL(bic.disable_date, g_scheduled_completion_date + 1)
> g_scheduled_completion_date
AND bic.wip_supply_type <> 6;
END Insert_Demands;