[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gmp.process_id,
gmp.inventory_item_id,
mtl.segment1,
mtl.primary_uom_code,
gmp.organization_id,
gmp.batch_id,
gmp.effectivity_id,
gmp.plan_quantity,
gmp.plan_start_date,
gmp.plan_end_date,
nvl( gmp.required_completion_date,gmp.plan_end_date) , -- Vpedarla Bug: 8348883
-- gmp.required_completion_date, /* APS K */
gmp.order_priority,
gmp.firm_flag,
gmp.action_type,
gmp.processed_ind,
gmp.header_id,
gmp.scheduling_method,
gmp.rowid
FROM gmp_aps_output_tbl gmp,
mtl_system_items mtl
WHERE gmp.processed_ind > 0
AND gmp.process_id = nvl(c_process_id,gmp.process_id) /* vpedarla bug: 7258717 */
AND gmp.inventory_item_id = mtl.inventory_item_id
AND gmp.organization_id = mtl.organization_id
AND gmp.organization_id = nvl(c_organization_id,gmp.organization_id)
AND mtl.segment1 >= nvl(c_fitem_no,mtl.segment1)
AND mtl.segment1 <= nvl(c_titem_no,mtl.segment1)
AND gmp.plan_start_date >= nvl(c_fdate,gmp.plan_start_date)
AND gmp.plan_start_date <= nvl(c_tdate,gmp.plan_start_date)
AND gmp.action_type = 1
UNION ALL
SELECT gmp.process_id,
gmp.inventory_item_id,
to_char(NULL),
to_char(NULL),
gmp.organization_id,
gmp.batch_id,
gmp.effectivity_id,
gmp.plan_quantity,
gmp.plan_start_date,
gmp.plan_end_date,
nvl( gmp.required_completion_date,gmp.plan_end_date) , -- Vpedarla Bug: 8348883
-- gmp.required_completion_date, /* APS K */
gmp.order_priority,
gmp.firm_flag,
gmp.action_type,
gmp.processed_ind,
gmp.header_id,
gmp.scheduling_method,
gmp.rowid
FROM gmp_aps_output_tbl gmp
WHERE gmp.processed_ind > 0
AND gmp.process_id = nvl(c_process_id,gmp.process_id) /* vpedarla bug: 7258717 */
AND gmp.organization_id = nvl(c_organization_id,gmp.organization_id)
AND gmp.plan_start_date >= nvl(c_fdate,gmp.plan_start_date)
AND gmp.plan_start_date <= nvl(c_tdate,gmp.plan_start_date)
AND gmp.action_type <> 1 ;
l_updt_offsets := nvl(FND_PROFILE.VALUE('GMP_UPDATE_ACTIVITY_OFFSETS'),0);/*B5148802*/
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Profile Update activity offset is set');
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Profile Update activity offset is not set');
SELECT FPO_DOC_NUMBERING
INTO X_assignment_type
FROM gme_parameters
WHERE organization_id = get_data_rec.organization_id ;
SELECT BATCH_DOC_NUMBERING
INTO X_assignment_type
FROM gme_parameters
WHERE organization_id = get_data_rec.organization_id ;
gmp_aps_writer.update_activity_offsets (get_data_rec.batch_id);
UPDATE GME_BATCH_HEADER
SET firmed_ind = get_data_rec.firmed_ind
WHERE batch_id = get_data_rec.batch_id ;
gmp_aps_writer.update_activity_offsets (get_data_rec.batch_id);
FND_MESSAGE.SET_NAME('GMA','SY_NO_ROWS_SELECTED');
SELECT calendar_code,organization_code FROM mtl_parameters
WHERE organization_id = l_organization_id ;
SELECT msi.PRIMARY_UOM_CODE , gmd.detail_uom from
(select PRIMARY_UOM_CODE, inventory_item_id , organization_id from mtl_system_items) msi,
(select inventory_item_id , recipe_validity_rule_id , DETAIL_UOM from gmd_recipe_validity_rules ) gmd
where msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_inventory_item_id
AND msi.inventory_item_id = gmd.inventory_item_id
AND gmd.recipe_validity_rule_id = p_effectivity_id ;
l_gme_batch_header.update_inventory_ind := 0 ;
SELECT fixed_process_loss_ind
INTO l_fixed_process_loss_ind
FROM gme_parameters
WHERE organization_id = p_organization_id ;
select DECODE(l_profile,'N','F','Y','T') into x_use_workday_cal from dual;
UPDATE gmp_aps_output_tbl
SET processed_ind = 0 ,
batch_id = v_batch_id
WHERE rowid = p_rowid
AND processed_ind > 0
AND header_id = p_header_id;
UPDATE gmp_aps_output_dtl
SET wip_entity_id = v_batch_id
WHERE parent_header_id = p_header_id
AND group_id = p_process_id ;
SELECT gr.recipe_id, count(1)
FROM gmd_recipes_b gr,
gmd_recipe_validity_rules grv,
gmd_status_b gs
WHERE grv.validity_rule_status in ('700','900')
AND gr.recipe_id = grv.recipe_id
AND grv.validity_rule_status = gs.status_code
AND gs.status_type in ('700','900')
AND grv.delete_mark = 0
AND (( p_batch_type = 0
AND grv.recipe_use = 0 ) -- Production Use only
OR ( p_batch_type = 10
AND grv.recipe_use IN (0,1) )) -- Planning/Production Use only
AND grv.inventory_item_id = p_inventory_item_id
AND nvl(grv.organization_id,p_organization_id) = p_organization_id
AND grv.recipe_validity_rule_id = p_effectivity_id
AND grv.min_qty <= nvl(p_plan_quantity, grv.min_qty )
AND grv.max_qty >= nvl( p_plan_quantity , grv.max_qty )
AND trunc(grv.start_date) <= trunc(p_start_date) -- Falls within a data range
-- AND NVL(grv.end_date,(sysdate+8000)) >= trunc(p_end_date)
-- Bug: 8467054 Vpedarla commented the above where condition.
GROUP BY gr.recipe_id;
REM| update GME |
REM| |
REM| MODIFICATION HISTORY |
REM| 10/01/03 Sridhar Gidugu ----- created |
REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
REM+=======================================================================*/
PROCEDURE scheduling_details_create(
p_batch_id IN NUMBER,
p_process_id IN NUMBER,
p_header_id IN NUMBER,
p_plan_start_date IN DATE,
p_plan_end_date IN DATE,
p_required_completion IN DATE, -- For R12.0
p_order_priority IN NUMBER, -- For R12.0
p_organization_id IN NUMBER, -- For R12.0
p_eff_id IN NUMBER) IS
err_msg VARCHAR2(4000) ;
SELECT a.batch_no, a.batch_type, a.batch_status,
a.plan_start_date, a.plan_cmplt_date,
a.recipe_validity_rule_id, b.recipe_id
FROM gme_batch_header a,
gmd_recipe_validity_rules b
WHERE a.batch_id = p_batch_id
AND a.organization_id = p_organization_id
AND a.recipe_validity_rule_id = b.recipe_validity_rule_id
AND b.delete_mark = 0
AND b.recipe_use IN (0,1) ;
SELECT calendar_code, organization_code FROM mtl_parameters
WHERE organization_id = l_organization_id ;
l_update_due_date NUMBER; -- Vpedarla Bug: 8348883
SELECT msi.CONCATENATED_SEGMENTS , msi.inventory_item_id from
(select PRIMARY_UOM_CODE, inventory_item_id , organization_id , CONCATENATED_SEGMENTS from mtl_system_items_kfv) msi,
(select inventory_item_id , recipe_validity_rule_id , DETAIL_UOM from gmd_recipe_validity_rules ) gmd
where msi.organization_id = p_organization_id
AND msi.inventory_item_id = gmd.inventory_item_id
AND gmd.recipe_validity_rule_id = eff_id ;
l_update_due_date := nvl(FND_PROFILE.VALUE('GMP:UPDATE_DUE_DATE_FOR_BATCHES_RESCHEDULE_BY_ASCP'),0) ;
IF l_update_due_date = 0 THEN -- Vpedarla bug: 8348883
scheduling_details_resc(
p_batch_id,
p_process_id,
p_header_id,
p_start_date,
p_end_date,
NULL , -- For R12.0
p_order_priority , -- For R12.0
p_organization_id , -- For R12.0
l_recipe_validity_rule_id,
t_ret_code
);
SELECT DECODE(l_profile,'N','F','Y','T') INTO x_use_workday_cal FROM dual;
IF l_update_due_date = 1 THEN
FND_FILE.PUT_LINE ( FND_FILE.LOG,'due date update');
UPDATE gme_batch_header
SET due_date = p_required_completion
WHERE BATCH_ID = l_gme_batch_header.batch_id;
UPDATE gmp_aps_output_tbl
SET processed_ind = 0
WHERE rowid = p_rowid
AND header_id = p_header_id
AND processed_ind > 0
AND batch_id = p_batch_id ;
REM| update GME |
REM| |
REM| MODIFICATION HISTORY |
REM| 10/01/03 Sridhar Gidugu ----- created |
REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
REM+======================================================================*/
PROCEDURE scheduling_details_resc(
p_batch_id IN NUMBER,
p_process_id IN NUMBER,
p_header_id IN NUMBER,
p_plan_start_date IN DATE,
p_plan_end_date IN DATE,
p_required_completion IN DATE, -- For R12.0
p_order_priority IN NUMBER, -- For R12.0
p_organization_id IN NUMBER, -- For R12.0
p_eff_id IN NUMBER,
return_status OUT NOCOPY NUMBER) IS
err_msg VARCHAR2(3000) ;
SELECT gbh.batch_no, gbh.batch_status,
gbh.recipe_validity_rule_id,
mp.organization_code,
gbh.batch_type
FROM gme_batch_header gbh, mtl_parameters mp
WHERE gbh.batch_id = p_batch_id
AND gbh.organization_id = mp.organization_id
AND mp.organization_id = p_organization_id;
UPDATE gmp_aps_output_tbl
SET processed_ind = 0
WHERE rowid = p_rowid
AND processed_ind > 0 ;