[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(intransit_time,0)
FROM mtl_interorg_ship_methods
WHERE from_organization_id = p_tp_organization
AND to_organization_id = p_oem_organization
AND default_flag =1;
SELECT fixed_lead_time
, variable_lead_time
INTO
l_fixed_time
, l_var_time
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_tp_organization;
SELECT count(1)
INTO l_off_days
FROM bom_calendar_dates bcd
, mtl_parameters mp
WHERE bcd.calendar_code = mp.calendar_code
AND mp.organization_id = p_tp_organization
AND bcd.calendar_date BETWEEN TRUNC(l_start_date) AND TRUNC(p_need_by_date)
AND seq_num IS NULL;
SELECT bcd.seq_num,
bcd.prior_date
INTO l_seq_num,
l_prior_date
FROM bom_calendar_dates bcd,
mtl_parameters mp
WHERE bcd.calendar_code = mp.calendar_code
AND mp.organization_id = p_tp_organization
AND TRUNC(bcd.calendar_date) = TRUNC(l_start_date);
SELECT NVL(intransit_time,0)
FROM mtl_interorg_ship_methods
WHERE from_organization_id = p_tp_organization
AND to_organization_id = p_oem_organization
AND default_flag =1;
SELECT fixed_lead_time
, variable_lead_time
INTO
l_fixed_time
, l_var_time
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_tp_organization;
SELECT *
FROM jmf_subcontract_orders
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;
SELECT shikyu_component_id
, quantity
FROM jmf_shikyu_components
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;
SELECT wip_job_schedule_interface_s.nextval
INTO l_group_id
FROM DUAL;
SELECT wip_interface_s.nextval
INTO l_interface_id
FROM DUAL;
SELECT scheduled_start_date
INTO l_orig_start_date
FROM wip_discrete_jobs
WHERE wip_entity_id = l_subcontract_orders_rec.wip_entity_id;
INSERT INTO
WIP_JOB_SCHEDULE_INTERFACE
( bom_revision_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, created_by_name
, last_updated_by_name
, last_update_login
, wip_entity_id
, firm_planned_flag
, first_unit_start_date
, group_id
, job_name
, load_type
, organization_id
, primary_item_id
, process_phase
, process_status
, source_code
, start_quantity
, status_type
, project_id
, task_id
, allow_explosion
--, last_unit_completion_date
, net_quantity
, header_id
, interface_id
, scheduling_method
)
VALUES
( DECODE(p_action,'U',null,'D',null,l_start_date)
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.USER_NAME
, FND_GLOBAL.USER_NAME
, FND_GLOBAL.USER_ID
, DECODE(p_action,'U',l_subcontract_orders_rec.wip_entity_id,
'D',l_subcontract_orders_rec.wip_entity_id,null)
, 1
, DECODE(p_action,'C',l_start_date,l_orig_start_date)
, l_group_id
, l_group_id||l_subcontract_orders_rec.subcontract_po_shipment_id
, DECODE(p_action,'C',1,3)
, l_subcontract_orders_rec.tp_organization_id
, l_subcontract_orders_rec.osa_item_id
, 2
, 1
, 'INV'
, DECODE(p_action,'C',l_quantity,null)
, DECODE(p_action,'D',7,'U',1,3)
, l_subcontract_orders_rec.project_id
, l_subcontract_orders_rec.task_id
, 'Y'
-- , DECODE(p_action,'C',l_end_date,null)
, DECODE(p_action,'C',l_quantity,null)
, l_group_id
, DECODE(p_action,'C',l_interface_id,null)
, 1
);
UPDATE jmf_subcontract_orders
SET wip_entity_id = l_wip_entity_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE subcontract_po_shipment_id =
l_subcontract_orders_rec.subcontract_po_shipment_id;
SELECT wip_job_schedule_interface_s.nextval
INTO l_group_id
FROM DUAL;
INSERT INTO
WIP_JOB_SCHEDULE_INTERFACE
( bom_revision_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, created_by_name
, last_updated_by_name
, last_update_login
, wip_entity_id
, firm_planned_flag
, first_unit_start_date
, group_id
, job_name
, load_type
, organization_id
, primary_item_id
, process_phase
, process_status
, source_code
, start_quantity
, status_type
, project_id
, task_id
, allow_explosion
, last_unit_completion_date
, net_quantity
, header_id
, interface_id
, scheduling_method
)
VALUES
( l_start_date
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.USER_NAME
, FND_GLOBAL.USER_NAME
, FND_GLOBAL.USER_ID
, l_subcontract_orders_rec.wip_entity_id
, 1
, l_start_date
, l_group_id
, l_group_id||l_subcontract_orders_rec.subcontract_po_shipment_id
, 3
, l_subcontract_orders_rec.tp_organization_id
, l_subcontract_orders_rec.osa_item_id
, 2
, 1
, 'INV'
, l_quantity
, 3
, l_subcontract_orders_rec.project_id
, l_subcontract_orders_rec.task_id
, 'Y'
, l_end_date
, l_quantity
, l_group_id
, null
, 1
);
SELECT bc.component_quantity
INTO l_quantity
FROM bom_bill_of_materials bom
, bom_components_b bc
WHERE bom.bill_sequence_id = bc.bill_sequence_id
AND bc.operation_seq_num =1
AND bc.component_item_id = p_item_id
AND bom.organization_id = p_organization_id
AND sysdate BETWEEN (bc.effectivity_date)
AND (NVL(bc.disable_date,sysdate+1));
SELECT wro.required_quantity
INTO l_quantity
FROM wip_requirement_operations wro
, jmf_subcontract_orders jso
WHERE wro.wip_entity_id = jso.wip_entity_id
AND wro.inventory_item_id = p_item_id
AND wro.organization_id = jso.tp_organization_id
AND wro.organization_id = p_organization_id
AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;