[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM| This procedure will update all the information related to a batch |
REM| 1. This procedure will be called from GMPAPSNW/RS screen. |
REM| 2. Materail transactions are calculated by OPM(GME logic) |
REM| |
REM| PARAMETERS |
REM| p_batch_id - Batch ID |
REM| p_group_id - Group ID |
REM| p_header_id - Header ID |
REM| p_start_date - Batch Start Date |
REM| p_end_date - Batch Start Date |
REM| p_required_completion - Batch Completion Date |
REM| p_order_priority - Batch Order Priority |
REM| p_organization_id - Batch Organizaiton |
REM| p_eff_id - Batch Validity Rule ID |
REM| p_action_type - Batch type (1 = New, 3 = Reschedule) |
REM| p_creation_date - Batch Creation Date |
REM| p_user_id - User ID |
REM| p_login_id - Application Login ID |
REM| |
REM| AUTHOR |
REM| R Patangya Created 25-MAY-2003 |
REM| HISTORY |
REM| Enhancements (APS K -- R12): 10-DEC-2004 (B3710615) |
REM| A. Do not plan Resources in place of secondary resources |
REM| NOTE |
REM| Hard Link, complex routs and MTQ deos not affect the detail feedback. |
REM| |
REM+==========================================================================+
*/
PROCEDURE main_process(
p_batch_id IN NUMBER,
p_group_id IN NUMBER,
p_header_id IN NUMBER,
p_start_date IN DATE,
p_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,
p_action_type IN NUMBER,
p_creation_date IN DATE,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
return_msg OUT NOCOPY VARCHAR2,
return_status OUT NOCOPY NUMBER) IS
/* Local array definition */
TYPE ref_cursor_typ IS REF CURSOR;
bo_last_update NUMBER(20),
step_status NUMBER(8),
aps_oper_count NUMBER(16),
gme_oper_count NUMBER(16),
max_step_date DATE -- B5473156
);
batch_last_update DATE ;
batch_last_update := NULL ;
orig_last_update_date := p_creation_date;
SELECT firm_flag INTO t_firm_flag FROM gmp_aps_output_tbl
WHERE batch_id = p_batch_id
AND process_id = p_group_id
AND header_id = p_header_id ;
gmp_debug_message(' gmp_aps_writer failed at firm_flag selection ');
batch_last_update, lreturn_status) ; -- For R12.0
IF (batch_last_update > orig_last_update_date) AND
(p_action_type <> 1) THEN
fnd_message.set_name('GMP','GMP_BATCH_HEADER_CHANGED');
update_batch_header(
p_batch_id,
p_start_date,
p_end_date,
t_due_date, -- For R12.0
p_order_priority, -- For R12.0
t_batch_status,
t_firm_flag, -- B5897392
p_user_id,
p_login_id,
breturn_status);
operations_cursor := ' SELECT '
||' nvl(gbs.batchstep_id,0), '
||' gbs.batchstep_no, '
||' gbs.oprn_id, '
||' gad.operation_seq_num, '
||' gad.first_unit_start_date, '
||' gad.last_unit_completion_date, '
||' gbs.bo_last_update, '
||' gbs.step_status , '
||' gad.oper_count, '
||' gbs.oper_count, '
||' gad.Max_Step '
||' FROM '
||' ( SELECT '
||' b.operation_seq_num, '
||' b.first_unit_start_date, '
||' b.last_unit_completion_date, '
||' COUNT(distinct b.operation_seq_num) OVER (PARTITION BY '
||' b.parent_header_id, b.group_id) oper_count , '
||' max(b.last_unit_completion_date) OVER (PARTITION BY a.batch_id) Max_Step '
||' FROM gmp_aps_output_tbl a,'
||' gmp_aps_output_dtl b '
||' WHERE b.load_type = 3 '
||' AND b.parent_header_id = a.header_id '
||' AND b.group_id = a.process_id '
||' AND b.organization_id = a.organization_id ' -- For R12.0
||' AND a.process_id = :pgpr '
||' AND a.header_id = :phdr '
||' ) gad , '
||' ( SELECT batchstep_id, '
||' batchstep_no, '
||' oprn_id, '
||' DECODE(sign(:lup '
||' - last_update_date), 1,1,0,1,-1,-600) bo_last_update, '
||' step_status , '
||' COUNT(distinct batchstep_no) '
||' OVER (PARTITION BY batch_id) oper_count '
||' FROM gme_batch_steps '
-- B5714301, changed the position of operation count
-- ||' WHERE batch_id = :pbatch1 '
||' WHERE batchstep_id IN ( select batchstep_id from gme_batch_steps '
||' WHERE batch_id = :pbatch1 ) '
||' AND step_status in (1,2) '
||' AND delete_mark = 0 '
-- B5473156, This check is not required as per scenario in the bug
-- ||' AND (plan_cmplt_date > plan_start_date OR :patype = 1 )'
||' ) gbs '
||' WHERE gad.operation_seq_num = gbs.batchstep_no (+) '
||' ORDER BY gbs.batchstep_id, gad.operation_seq_num ' ;
p_header_id, orig_last_update_date, p_batch_id ;
fnd_message.set_name('GMP','GMP_OPER_DELETED');
e_msg := e_msg || ' Operation deleted.';
ELSIF (operation_tab(oper_cnt).bo_last_update < 0) AND
(operation_tab(oper_cnt).step_status = 1) THEN
-- If step is in pending and last update changed, We are not
-- Updating the batch
fnd_message.set_name('GMP','GMP_BATCH_STEP_CHANGED');
update_batch_steps(
p_batch_id,
operation_tab(oper_cnt).operation_seq_num,
operation_tab(oper_cnt).batchstep_id,
operation_tab(oper_cnt).first_unit_start_date,
operation_tab(oper_cnt).last_unit_completion_date,
operation_tab(oper_cnt).last_unit_completion_date, /* B5454215 */
p_user_id,
p_login_id,
sreturn_status);
update_batch_steps(
p_batch_id,
operation_tab(oper_cnt).operation_seq_num,
operation_tab(oper_cnt).batchstep_id,
operation_tab(oper_cnt).first_unit_start_date,
operation_tab(oper_cnt).last_unit_completion_date,
NULL, /* B5454215 */
p_user_id,
p_login_id,
sreturn_status);
fnd_message.set_name('GMP','GMP_STEP_UPDATE_FAILED');
e_msg := e_msg || ' Failed: Update to Step/Operation' ;
gmp_debug_message(' step to be updated not in pending status '||operation_tab(oper_cnt).operation_seq_num );
UPDATE gmp_aps_output_dtl
SET load_type = (load_type * -1)
WHERE operation_seq_num =
operation_tab(oper_cnt).operation_seq_num
AND wip_entity_id = p_batch_id
AND organization_id = p_organization_id -- For R12.0
AND group_id = p_group_id
AND parent_header_id = p_header_id ;
update_batch_header(
p_batch_id,
p_start_date,
t_max_step_date,
t_due_date, -- For R12.0
p_order_priority, -- For R12.0
t_batch_status,
t_firm_flag, -- B5897392
p_user_id,
p_login_id,
breturn_status);
fnd_message.set_name('GMP','GMP_STEP_UPDATE_FAILED');
e_msg := e_msg || ' Failed: Update to Batch End Date' ;
UPDATE GMP_APS_OUTPUT_DTL gad
SET attribute9 = ( SELECT gbr.batchstep_resource_id
FROM GME_BATCH_HEADER gbh,
GME_BATCH_STEPS gbs,
GME_BATCH_STEP_ACTIVITIES gba,
GME_BATCH_STEP_RESOURCES gbr,
CR_RSRC_DTL crd
WHERE gbh.batch_id = gbs.batch_id
AND gbs.batchstep_id = gba.batchstep_id
AND gbs.batchstep_id = gbr.batchstep_id
AND gba.batchstep_activity_id = gbr.batchstep_activity_id
AND gbr.resources = crd.resources
AND gbh.organization_id = crd.organization_id -- For R12.0
AND gbh.organization_id = gbr.organization_id -- For R12.0
AND gbr.prim_rsrc_ind <> 1
AND crd.resource_id = gad.resource_id_new
AND gbh.batch_id = gad.wip_entity_id
AND gbs.batchstep_no = gad.operation_seq_num
AND gba.sequence_dependent_ind = gad.schedule_seq_num )
WHERE gad.wip_entity_id = p_batch_id
AND gad.group_id = p_group_id
AND gad.parent_header_id = p_header_id
AND gad.organization_id = p_organization_id
AND gad.load_type = 1 ;
oper_rsrc_cursor := ' SELECT '
||' final.batchstep_no , '
||' final.schedule_seq_num , ' -- For R12.0
||' aps.resource_seq_num , '
||' final.batchstep_id , '
||' aps.organization_id, '
||' final.batchstep_activity_id , '
||' final.batchstep_resource_id , '
||' final.activity , '
||' aps.resources , '
||' aps.resource_id_new, '
||' final.resources , '
||' aps.uom_code, '
||' final.uom_code, '
||' aps.assigned_units , '
||' final.plan_rsrc_count, '
-- Alternate for primary resource For R12.0
||' (final.plan_rsrc_usage * '
||' NVL((SELECT cam.runtime_factor FROM cr_ares_mst cam '
||' WHERE cam.delete_mark = 0 '
||' AND nvl(aps.replacement_group_num,0) <> 0 '
||' AND final.prim_rsrc_ind = 1 '
||' AND final.resources <> aps.resources '
||' AND aps.attribute9 is null '
||' AND final.resources = cam.primary_resource '
||' AND aps.resources = cam.alternate_resource '
||' ),1 ) ), ' -- GME Resource Usage
||' aps.sequence_dependent_usage, ' -- For R12.0
||' aps.start_date, '
||' aps.completion_date, '
-- Select Min activity start date For R12.0
||' MIN(aps.act_start_date) OVER (PARTITION BY '
||' final.batchstep_activity_id), '
||' MAX(aps.completion_date) OVER (PARTITION BY '
||' final.batchstep_activity_id), '
||' aps.resource_hour, ' -- APS Resource Usage
-- Is Charge exists
||' ( SELECT count(*) from gmp_aps_output_dtl '
||' WHERE wip_entity_id = aps.wip_entity_id '
||' AND parent_header_id = aps.parent_header_id '
||' AND group_id = aps.group_id '
||' AND load_type = 10 '
||' AND operation_seq_num = aps.operation_seq_num '
||' AND schedule_seq_num = aps.schedule_seq_num '
||' AND resource_id_new = aps.resource_id_new ) Charges_present, '
||' final.scale_type, '
-- Only Use APS data if alternate resource factor is 1
||' SUM( '
||' DECODE(final.rsrc_count,1,0, '
||' DECODE( '
||' NVL((SELECT cam.runtime_factor FROM cr_ares_mst cam '
||' WHERE cam.delete_mark = 0 '
||' AND nvl(aps.replacement_group_num,0) <> 0 '
||' AND final.prim_rsrc_ind = 1 '
||' AND final.resources <> aps.resources '
||' AND aps.attribute9 is null '
||' AND final.resources = cam.primary_resource '
||' AND aps.resources = cam.alternate_resource ) '
||' ,1),1,final.Batch_rsrc_Avg,9) '
||' ) '
||' ) OVER '
||' (PARTITION BY final.batchstep_activity_id), ' ; -- aps or gme use
||' final.bsa_last_update, '
||' final.bsr_last_update, '
||' final.act_count , '
||' nvl(aps.activity_count,0) , '
||' final.rsrc_count , '
||' nvl(aps.rsrc_count,0), ' ;
||' SELECT '
||' gsa.batch_id, '
||' gsa.batchstep_id, '
||' gbs.batchstep_no, '
||' gsa.batchstep_activity_id, '
||' gsa.activity, '
||' gsa.offset_interval, '
||' nvl(gsa.sequence_dependent_ind,0) schedule_seq_num,' -- For R12.0
||' gsr.batchstep_resource_id , '
||' gsr.resources, '
||' gsr.scale_type, '
||' gsr.prim_rsrc_ind, '
||' gsr.plan_rsrc_usage, '
||' gsr.plan_rsrc_count, '
||' DECODE(sign(ceil(gsr.plan_rsrc_usage) - '
||' (AVG(ceil(gsr.plan_rsrc_usage)/gsr.plan_rsrc_count) '
||' OVER (PARTITION BY gsr.batchstep_activity_id)) '
||' ) ,0,0,1,1,-1,1 ) Batch_rsrc_Avg ,'
||' COUNT(distinct gbs.batchstep_no) '
||' OVER (PARTITION BY gsr.batch_id) oper_count, '
||' COUNT(distinct gsa.batchstep_activity_id) '
||' OVER (PARTITION BY gbs.batchstep_no) act_count, '
||' COUNT(gsr.resources) '
-- For R12.0
||' OVER (PARTITION BY gbs.batchstep_no, '
||' gsa.batchstep_activity_id, '
||' DECODE(crd.schedule_ind,1,1,2,1,0,1) ) rsrc_count, '
||' gsr.usage_um uom_code,'
||' DECODE(sign(:LUP1 '
||' - gsr.last_update_date), 1,1,0,1,-1,-500) bsr_last_update, '
||' DECODE(sign(:LUP2 '
||' - gsa.last_update_date), 1,1,0,1,-1,-500) bsa_last_update '
-- For R12.0
||' FROM gme_batch_step_activities gsa, '
||' gme_batch_steps gbs, '
||' gme_batch_step_resources gsr, '
||' cr_rsrc_dtl crd ' -- For R12.0
||' WHERE '
||' gsr.batch_id = gsa.batch_id '
||' AND gsr.batchstep_activity_id = gsa.batchstep_activity_id '
||' AND crd.resources = gsr.resources ' -- For R12.0
||' AND crd.organization_id = gsr.organization_id ' -- For R12.0
||' AND crd.delete_mark = 0 ' -- For R12.0
||' AND crd.schedule_ind <> 3 ' -- For R12.0
||' AND gsr.plan_rsrc_usage > 0 ' -- For R12.0
||' AND gsa.batch_id = :PBATCH1 '
||' AND gsa.delete_mark = 0 '
||' AND gbs.delete_mark = 0 '
-- bug: 8348916 vpedarla added condition to process only steps in pending status.
-- For records of steps in status other than pending, load_type will be negative and not allowed to process.
||' AND gbs.step_status = 1 '
||' AND gbs.batch_id = gsa.batch_id '
||' AND gsa.batchstep_id = gbs.batchstep_id '
||' ) final, '
||' ( '
||' SELECT gad.wip_entity_id, gad.organization_id, ' -- For R12.0
||' gad.parent_header_id , '
||' gad.group_id , '
||' gad.operation_seq_num, '
||' gad.resource_seq_num, '
||' gad.schedule_seq_num, ' -- For R12.0
||' gad.assigned_units , '
||' gad.resource_id_new , '
||' gad.resource_id_old , '
||' gad.attribute9, '
||' crd.resources , '
||' gad.uom_code , '
||' gad.replacement_group_num , '
||' gad.setup_id , ' -- For R12.0
||' gad.group_sequence_id , ' -- For R12.0
||' gad.group_sequence_number , ' -- For R12.0
||' gad.firm_flag , ' -- For R12.0
||' gad.start_date, '
||' gad.completion_date, '
-- For R12.0
||' DECODE(seq.start_date, NULL,gad.start_date, '
||' seq.start_Date) act_start_date, '
||' seq.start_date seq_start_date, '
||' seq.sequence_dependent_usage, '
||' nvl(fnd_number.canonical_to_number(gad.attribute1),0) resource_hour, '
/*sowsubra B4629277 - changed to_number to fnd_number.canonical_to_number*/
||' MAX(nvl(to_number(gad.attribute1),0)) '
||' OVER (PARTITION BY gad.operation_seq_num, '
||' gad.schedule_seq_num ) aps_max_usage, '
||' COUNT(distinct gad.operation_seq_num) '
||' OVER (PARTITION BY gad.wip_entity_id) oper_count, '
||' COUNT(distinct gad.schedule_seq_num) '
||' OVER (PARTITION BY gad.operation_seq_num) activity_count, '
||' ( COUNT(gad.resource_id_new) '
||' OVER (PARTITION BY gad.operation_seq_num, '
||' gad.schedule_seq_num ) '
||' - crd.delete_mark ) rsrc_count, '
||' gao.inventory_item_id ' -- Bug: 8616967 Vpedarla
||' FROM gmp_aps_output_dtl gad, '
||' gmp_aps_output_tbl gao, '
||' cr_rsrc_dtl crd, '
-- Sequence depdendency selection For R12.0
||' ( SELECT operation_seq_num, parent_seq_num, schedule_seq_num, '
||' wip_entity_id, resource_id_new , '
||' TO_NUMBER(attribute1) sequence_dependent_usage, '
||' MIN(start_date) OVER (PARTITION BY '
||' group_id, parent_header_id, schedule_seq_num) start_date '
||' FROM gmp_aps_output_dtl '
||' WHERE parent_header_id = :phdr2 '
||' AND group_id = :PGRP2 '
||' AND wip_entity_id = :PBATCH2 '
||' AND load_type = 1 '
||' AND parent_seq_num IS NOT NULL '
||' ) seq '
||' WHERE gad.parent_header_id = gao.header_id '
||' AND gad.group_id = gao.process_id '
||' AND gao.header_id = :PHDR3 '
||' AND gad.group_id = :PGRP3 '
||' AND gad.wip_entity_id = :PBATCH3 '
||' AND gao.batch_id = gad.wip_entity_id '
||' AND gad.load_type = 1 '
||' AND gad.parent_seq_num IS NULL ' -- For R12.0
||' AND gad.resource_id_new = crd.resource_id '
||' AND crd.organization_id = gao.organization_id '
-- For R12.0
||' AND gad.wip_entity_id = seq.wip_entity_id (+) '
||' AND gad.resource_id_new = seq.resource_id_new (+) '
||' AND gad.operation_seq_num = seq.operation_seq_num (+) '
||' AND gad.schedule_seq_num = seq.schedule_seq_num (+) '
||' ) APS '
||' WHERE ' ;
log_message(' orig_last_update_date -' ||to_char(orig_last_update_date,'dd-mm-yy hh24:mi:ss'));
OPEN cur_oper_rsrc FOR oper_rsrc_cursor USING orig_last_update_date,
orig_last_update_date, p_batch_id, p_header_id, p_group_id, p_batch_id,
p_header_id, p_group_id, p_batch_id ;
e_msg := e_msg || ' Activity updated.';
gmp_debug_message(' calling update_step_resources with usage '||rsrc_usg );
update_step_resources(
p_batch_id,
or_tab(j).organization_id, -- For R12.0,
or_tab(j).batchstep_resource_id,
rsrc_usg,
or_tab(j).sequence_dependent_usage, -- For R12.0
or_tab(j).gme_resource,
or_tab(j).aps_resource,
or_tab(j).start_date,
or_tab(j).completion_date,
or_tab(j).gme_uom_code ,
rsrc_cnt,
or_tab(j).aps_data_use,
or_tab(j).setup_id , -- For R12.0
or_tab(j).group_sequence_id , -- For R12.0
or_tab(j).group_sequence_number, -- For R12.0
or_tab(j).firm_flag , -- For R12.0
or_tab(j).scale_type, -- For R12.0
p_user_id,
p_login_id,
new_batchstep_resource_id,
rreturn_status );
gmp_debug_message(' calling update_step_resources with usage '||rsrc_usg );
update_step_resources(
p_batch_id,
or_tab(j).organization_id, -- For R12.0,
or_tab(j).batchstep_resource_id,
rsrc_usg,
or_tab(j).sequence_dependent_usage, -- For R12.0
or_tab(j).gme_resource,
or_tab(j).aps_resource,
or_tab(j).start_date,
or_tab(j).completion_date,
or_tab(j).gme_uom_code ,
rsrc_cnt,
or_tab(j).aps_data_use,
or_tab(j).setup_id , -- For R12.0
or_tab(j).group_sequence_id , -- For R12.0
or_tab(j).group_sequence_number, -- For R12.0
or_tab(j).firm_flag , -- For R12.0
or_tab(j).scale_type, -- For R12.0
p_user_id,
p_login_id,
new_batchstep_resource_id,
rreturn_status );
UPDATE GMP_APS_OUTPUT_DTL
SET attribute9 = new_batchstep_resource_id,
attribute10 = or_tab(j).APS_UOM_CODE
WHERE load_type IN (4,9) -- For R12.0
AND resource_id_new = or_tab(j).aps_resource_id
AND group_id = p_group_id
AND parent_header_id = p_header_id
AND operation_seq_num = or_tab(j).operation_seq_num
-- For R12.0
AND schedule_seq_num = or_tab(j).schedule_seq_num
-- PS Issue B6045398, PS engine is sending resource_seq_num NULL for laod_type = 1
AND ( ( resource_seq_num = NVL(or_tab(j).resource_seq_num,resource_seq_num)
AND parent_seq_num IS NULL )
OR
(
parent_seq_num = NVL(or_tab(j).resource_seq_num,parent_seq_num)
AND parent_seq_num IS NOT NULL )
) ;
UPDATE GMP_APS_OUTPUT_DTL
SET attribute9 = new_batchstep_resource_id,
attribute10 = or_tab(j).APS_UOM_CODE,
assigned_units = rsrc_cnt
WHERE load_type IN (4,9) -- For R12.0
AND resource_id_new = or_tab(j).aps_resource_id
AND group_id = p_group_id
AND parent_header_id = p_header_id
AND operation_seq_num = or_tab(j).operation_seq_num
-- For R12.0
AND schedule_seq_num = or_tab(j).schedule_seq_num
AND ( ( resource_seq_num = or_tab(j).resource_seq_num
AND parent_seq_num IS NULL )
OR
(
parent_seq_num = or_tab(j).resource_seq_num
AND parent_seq_num IS NOT NULL )
) ;
update_batch_activities(
p_batch_id,
act_tab(i).organization_id, -- For R12.0
act_tab(i).batchstep_id,
act_tab(i).batchstep_activity_id,
act_tab(i).start_date,
act_tab(i).end_date,
act_tab(i).uom_code,
p_user_id,
p_login_id,
areturn_status);
fnd_message.set_name('GMP','GMP_ACTIVITY_UPDATE_FAIL');
e_msg := e_msg || ' Update to Activities is failed' ;
UPDATE gmp_aps_output_dtl
SET load_type = (load_type * -1)
WHERE load_type = 4
AND group_id = p_group_id
AND parent_header_id = p_header_id
-- PS Issue, B6051303 Alternate resource Issue
AND (operation_Seq_num,nvl(parent_seq_num,resource_seq_num),
schedule_seq_num) IN
( SELECT b.operation_Seq_num,
nvl(b.parent_seq_num,b.resource_seq_num),
b.schedule_seq_num
FROM gmp_aps_output_dtl b
WHERE b.group_id = p_group_id
AND b.parent_header_id = p_header_id
AND b.load_type = 9 ) ;
rsrc_tran_cursor := ' SELECT '
||' gad.attribute9, ' -- Batchstep resource ID
||' gao.organization_id, ' -- For R12.0
||' gad.operation_seq_num, '
||' gad.schedule_seq_num, ' -- For R12.0
||' gad.resource_seq_num, '
||' gad.parent_seq_num, ' -- For R12.0
||' gad.resource_id_new, '
||' crd.resources, '
||' gad.attribute10, ' -- uom_code
||' gad.assigned_units, '
||' nvl(fnd_number.canonical_to_number(gad.attribute1),0) resource_hour, '
-- sowsubra B4629277 changed to_number to fnd_number.canonical_to_number
||' gad.start_date, '
||' gad.completion_date, '
||' gad.resource_instance_id , ' -- For R12.0
||' gme.USAGE_UM , ' --Bug: 8616967 Vpedarla
||' gao.inventory_item_id ' --Bug: 8616967 Vpedarla
||' FROM gmp_aps_output_dtl gad, '
||' gmp_aps_output_tbl gao, '
||' cr_rsrc_dtl crd , '
||' gme_batch_step_resources gme ' --Bug: 8616967 Vpedarla
||' WHERE '
||' gad.load_type in (4,9) '
||' AND gad.parent_header_id = gao.header_id '
||' AND gad.group_id = gao.process_id '
||' AND gad.wip_entity_id = gao.batch_id '
||' AND gao.process_id = :pgpr '
||' AND gao.header_id = :phdr '
||' AND gad.resource_id_new = crd.resource_id '
||' AND crd.organization_id = gao.organization_id ' -- For R12.0
||' AND nvl(to_number(gad.attribute9),0) > 0 ' -- batchstep_resource_id
||' AND nvl(fnd_number.canonical_to_number(gad.attribute1),0) > 0 '
-- sowsubra B4629277 changed to_number to fnd_number.canonical_to_number
||' AND gao.batch_id = :pbatch1 '
||' AND gme.batchstep_resource_id =gad.attribute9 ' ; --Bug: 8616967 Vpedarla
update_resource_transactions(
p_batch_id,
rsrc_tran_tab(rtran_cnt).batchstep_resource_id,
rsrc_tran_tab(rtran_cnt).organization_id, -- For R12.0
-- (rsrc_tran_tab(rtran_cnt).resource_hour/end_tran), -- bug: 8616967 vpedarla
rsrc_tran_tab(rtran_cnt).resource_hour,
rsrc_tran_tab(rtran_cnt).aps_resource,
rsrc_tran_tab(rtran_cnt).start_date,
rsrc_tran_tab(rtran_cnt).completion_date,
rsrc_tran_tab(rtran_cnt).gme_usage_uom ,
rsrc_tran_tab(rtran_cnt).resource_instance_id , -- For R12.0
t_seq_dep_ind, -- For R12.0
p_user_id,
p_login_id,
treturn_status );
fnd_message.set_name('GMP','GMP_RSRC_TRANS_UPDATE_FAIL');
e_msg := e_msg || ' Update to Resource Transaction is failed' ;
update_materails( p_batch_id,
p_organization_id,
mreturn_status) ;
fnd_message.set_name('GMP','GMP_MATL_UPDATE_FAIL');
e_msg := e_msg || ' Materail Update is failed' ;
Insert_charges( p_batch_id,
p_group_id ,
p_header_id,
xreturn_status) ;
fnd_message.set_name('GMP','GMP_RSRC_CHRGS_UPDATE_FAIL');
e_msg := e_msg || ' Charges Insert is failed' ;
UPDATE gmp_aps_output_tbl
SET processed_ind = 0
WHERE batch_id = p_batch_id
AND process_id = p_group_id
AND header_id = p_header_id ;
REM| update_step_resources |
REM| DESCRIPTION |
REM| This procedure will update the step resources plan start and end date|
REM| HISTORY |
REM| Rajesh Patangya |
REM| 22-MAR-2013 B16492884 Vijay induri |
REM+=========================================================================+
*/
PROCEDURE update_step_resources(
pbatch_id IN NUMBER,
porganization_id IN NUMBER, -- For R12.0
pstep_resource_id IN NUMBER,
prsrc_usage IN NUMBER,
psequence_dep_usage IN NUMBER, -- For R12.0
pgme_resource IN VARCHAR2,
paps_resource IN VARCHAR2,
pstart_date IN DATE,
pend_date IN DATE,
pbs_usage_uom IN VARCHAR2, -- Gme UOM code
passigned_unit IN NUMBER,
paps_data_use IN NUMBER,
psetup_id IN NUMBER, -- For R12.0
pgroup_sequence_id IN NUMBER, -- For R12.0
pgroup_sequence_number IN NUMBER, -- For R12.0
pfirm_flag IN NUMBER, -- For R12.0
pscale_type IN NUMBER, -- For R12.0
puser_id IN NUMBER,
plogin_id IN NUMBER,
pnew_act_res OUT NOCOPY NUMBER,
return_status OUT NOCOPY NUMBER )
IS
v_batch_id NUMBER ;
DELETE gme_resource_txns
WHERE doc_id = v_batch_id
AND resource_usage > 0
AND line_id= v_step_resource_id ;
UPDATE gme_batch_step_resources
SET
plan_start_date = pstart_date,
plan_cmplt_date = pend_date,
plan_rsrc_usage = prsrc_usage,
plan_rsrc_count = v_assigned_unit,
sequence_dependent_id = psetup_id , -- For R12.0
sequence_dependent_usage = psequence_dep_usage, -- For R12.0
group_sequence_id = pgroup_sequence_id , -- For R12.0
group_sequence_number = pgroup_sequence_number ,-- For R12.0
firm_type = pfirm_flag , -- For R12.0
scale_type = pscale_type , -- For R12.0
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_step_resource_id;
UPDATE gme_batch_step_resources
SET
plan_start_date = pstart_date,
plan_cmplt_date = pend_date,
sequence_dependent_id = psetup_id , -- For R12.0
sequence_dependent_usage = psequence_dep_usage, -- For R12.0
group_sequence_id = pgroup_sequence_id , -- For R12.0
group_sequence_number = pgroup_sequence_number ,-- For R12.0
firm_type = pfirm_flag , -- For R12.0
scale_type = pscale_type , -- For R12.0
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_step_resource_id;
DELETE gme_batch_step_resources
WHERE batchstep_resource_id = v_step_resource_id;
IF NOT GME_BATCH_STEP_RESOURCES_DBL.insert_row
(v_step_res_row, v_in_step_res_row) THEN
return_status := -5;
END IF; /* Delete NOTFOUND */
update_resource_transactions(
v_batch_id ,
pnew_act_res ,
v_organization_id, -- For R12.0
v_rsrc_usage ,
v_n_resources , -- alternate or auxillary
pstart_date ,
v_end_date ,
v_uom_code , -- Changed from 3 char to 4 character
NULL , -- Resource Instance Id
0 , -- Sequence Depdent Indicator
puser_id ,
plogin_id ,
tran_status );
fnd_message.set_name('GMP','GMP_RSRC_TRANS_UPDATE_FAIL');
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_step_resources');
e_msg := e_msg || ' Update Step Resources Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
END update_step_resources;
REM| update_resource_transactions |
REM| DESCRIPTION |
REM| This procedure will update the resource instance start and end date |
REM| HISTORY |
REM| Rajesh Patangya |
REM+=========================================================================+
*/
PROCEDURE update_resource_transactions(
pbatch_id IN NUMBER,
pbstep_rsrc_id IN NUMBER,
porganization_id IN NUMBER, -- For R12.0
prsrc_hour IN NUMBER,
paps_resource IN VARCHAR2,
pstart_date IN DATE,
pend_date IN DATE,
puom_code IN VARCHAR2,
prsrc_inst_id IN NUMBER, -- For R12.0 resource_instance_id
pseq_dep_ind IN NUMBER, -- For R12.0 sequence dependent
puser_id IN NUMBER,
plogin_id IN NUMBER,
return_status OUT NOCOPY NUMBER ) IS
v_in_trans_row gme_resource_txns%ROWTYPE; /* Added for NOCOPY */
SELECT DECODE(nvl(batch_type,0),0,'PROD',10,'FPO') into l_doc_type
FROM gme_batch_header where batch_id = pbatch_id ;
v_in_trans_row.last_update_date := SYSDATE;
v_in_trans_row.last_updated_by := puser_id;
v_in_trans_row.last_update_login := plogin_id;
v_in_trans_row.delete_mark := 0;
IF NOT gme_resource_txns_dbl.insert_row
(v_in_trans_row, v_trans_row) THEN
return_status := -1;
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_resource_transactions');
e_msg := e_msg || ' Update SResource Transaction Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
END update_resource_transactions;
REM| update_batch_activities |
REM| DESCRIPTION |
REM| This procedure will update the activity plan start and end date |
REM| HISTORY |
REM| Rajesh Patangya |
REM+=========================================================================+
*/
PROCEDURE update_batch_activities(
pbatch_id IN NUMBER,
porganization_id IN NUMBER, -- For R12.0
pstep_id IN NUMBER,
pactivity_id IN NUMBER,
pstart_date IN DATE,
pend_date IN DATE,
puom_hour IN VARCHAR2,
puser_id IN NUMBER,
plogin_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
v_activity_id NUMBER ;
SELECT
gsr.batchstep_resource_id, gsr.resources, gsr.plan_rsrc_count,
DECODE(crd.schedule_ind, 3, Decode(gsr.plan_rsrc_usage, 0, 0, gsr.plan_rsrc_usage), DECODE(gsr.plan_rsrc_usage, 0, 0, inv_convert.inv_um_convert(-1,38,
gsr.plan_rsrc_usage,u2.uom_code,u1.uom_code,NULL,NULL))) plan_rsrc_usage,
gsr.offset_interval,
gsr.plan_start_date,
gsr.plan_cmplt_date,
crd.schedule_ind
FROM
gme_batch_step_resources gsr,
cr_rsrc_dtl crd,
mtl_units_of_measure u1,
mtl_units_of_measure u2
WHERE
gsr.batchstep_activity_id = v_activity_id
AND crd.resources = gsr.resources -- For R12.0
AND crd.organization_id = v_organization_id -- For R12.0
AND gsr.organization_id = crd.organization_id -- For R12.0
AND crd.delete_mark = 0 -- For R12.0
AND u1.uom_code = gsr.usage_um
AND u2.uom_code = v_hour_uom
AND (gsr.plan_rsrc_usage = 0 OR
u1.uom_class <> u2.uom_class OR
crd.schedule_ind = 3 ); -- For R12.0
UPDATE gme_batch_step_activities
SET
plan_start_date = v_start_date,
plan_cmplt_date = v_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE batchstep_activity_id = v_activity_id;
UPDATE
gme_batch_step_resources
SET
plan_start_date = v_trn_start_date,
plan_cmplt_date = v_trn_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_zero_res_id;
DELETE gme_resource_txns
WHERE doc_id = v_batch_id
AND nvl(sequence_dependent_ind,0) > 0
AND line_id= v_zero_res_id ;
UPDATE
gme_batch_step_resources
SET
plan_start_date = v_trn_start_date,
plan_cmplt_date = v_trn_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_zero_res_id;
DELETE gme_resource_txns
WHERE doc_id = v_batch_id
AND nvl(sequence_dependent_ind,0) > 0
AND line_id= v_zero_res_id ;
UPDATE
gme_batch_step_resources
SET
plan_start_date = v_trn_start_date,
plan_cmplt_date = v_trn_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_zero_res_id;
UPDATE
gme_resource_txns
SET
start_date = v_trn_start_date,
end_date = v_trn_end_date,
trans_date = v_trn_start_date,
last_update_date = SYSDATE,
last_updated_by = puser_id,
instance_id = NULL,
delete_mark = 0,
sequence_dependent_ind = 0,
overrided_protected_ind = 'N',
last_update_login = plogin_id
WHERE
doc_id = v_batch_id
AND doc_type in ('PROD','FPO')
AND line_id = v_zero_res_id
AND completed_ind = 0
AND delete_mark = 0;
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_activities');
e_msg := e_msg || ' Update Step Activities Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
END update_batch_activities;
REM| update_materails |
REM| DESCRIPTION |
REM| This procedure will update the Materail deatails as per GME rules |
REM| HISTORY |
REM| Rajesh Patangya |
REM| If the item is associated to step and NOT having release_type of |
REM| Automatic (0) in the material detail then the step's plan_start_date |
REM| will be used for all ingredients (line_type= -1) and plan_cmplt_date |
REM| for all products and byproducts (line_type = 1 or 2). |
REM| If the item is not associated to step OR Item is associated to step and |
REM| having release_type of Automatic (0) in the material detail then the |
REM| batch's plan_start_date will be used for all ingredients (line_type= -1)|
REM| and plan_cmplt_date for all products and byproducts (line_type = 1 or 2)|
REM| |
REM+=========================================================================+
*/
PROCEDURE update_materails (
pbatch_id IN NUMBER,
porganization_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
v_batch_id NUMBER ;
SELECT gmd.material_detail_id, gmd.line_type
FROM gme_material_details gmd
WHERE gmd.batch_id = v_batch_id
AND gmd.organization_id = v_organization_id ;
gme_api_grp.update_material_date(
v_material_detail_id, -- p_material_detail_id,
NULL, -- p_material_date
m_return_status);
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_materails');
e_msg := e_msg || ' Update Materails Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
END update_materails;
REM| update_batch_steps |
REM| DESCRIPTION |
REM| This procedure will update the step plan start and end date |
REM| HISTORY |
REM| Rajesh Patangya |
REM| |
REM+=========================================================================+
*/
PROCEDURE update_batch_steps(
pbatch_id IN NUMBER,
pstep_no IN NUMBER,
pstep_id IN NUMBER,
pstart_date IN DATE,
pend_date IN DATE,
pdue_date IN DATE, -- B5454215
puser_id IN NUMBER,
plogin_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
v_plan_charges NUMBER ;
SELECT count(*) INTO v_plan_charges
FROM
gmp_aps_output_dtl gad,
gme_batch_steps gbs
WHERE gad.wip_entity_id = pbatch_id
AND gad.load_type = 10
AND gbs.batch_id = gad.wip_entity_id
AND gbs.batchstep_no = pstep_no
AND gbs.batchstep_no = gad.operation_seq_num
AND gbs.delete_mark = 0
AND gbs.step_status = 1 ;
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_steps');
UPDATE gme_batch_steps
SET plan_start_date = pstart_date,
plan_cmplt_date = pend_date,
due_date = pdue_date,
-- For R12.0
plan_charges = DECODE(step_status,1,v_plan_charges,plan_charges),
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE batch_id = pbatch_id
AND batchstep_no = pstep_no
AND batchstep_id = pstep_id ;
UPDATE gme_batch_steps
SET plan_start_date = pstart_date,
plan_cmplt_date = pend_date,
-- For R12.0
due_date = pdue_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE batch_id = pbatch_id
AND batchstep_no = pstep_no
AND batchstep_id = pstep_id ;
UPDATE gme_batch_steps
SET plan_start_date = pstart_date,
plan_cmplt_date = pend_date,
-- For R12.0
plan_charges = DECODE(step_status,1,v_plan_charges,plan_charges),
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE batch_id = pbatch_id
AND batchstep_no = pstep_no
AND batchstep_id = pstep_id ;
UPDATE gme_batch_steps
SET plan_start_date = pstart_date,
plan_cmplt_date = pend_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE batch_id = pbatch_id
AND batchstep_no = pstep_no
AND batchstep_id = pstep_id ;
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_steps');
e_msg := e_msg || ' Update Batch Step Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
END update_batch_steps;
REM| update_batch_header |
REM| DESCRIPTION |
REM| This procedure will update the batch plan start and end date |
REM| HISTORY |
REM| Rajesh Patangya |
REM| |
REM+=========================================================================+
*/
PROCEDURE update_batch_header(
pbatch_id IN NUMBER,
pstart_date IN DATE,
pend_date IN DATE,
preq_completion_date IN DATE, -- For R12.0
pord_priority IN NUMBER, -- For R12.0
pbatch_status IN NUMBER,
pfirm_flag IN NUMBER, -- B5897392
puser_id IN NUMBER,
plogin_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
v_batch_status NUMBER ;
UPDATE gme_batch_header
SET
plan_start_date = pstart_date,
plan_cmplt_date = pend_date,
due_date = NVL(preq_completion_date,gme_batch_header.due_date),
order_priority = NVL(pord_priority,gme_batch_header.order_priority),
firmed_ind = pfirm_flag, -- B5897392
last_update_date = SYSDATE,
last_updated_by = puser_id,
last_update_login = plogin_id
-- finite_scheduled_ind = 1 /*B5186781*/
WHERE batch_id = pbatch_id;
UPDATE gme_batch_header
SET
plan_cmplt_date = pend_date,
-- Vpedarla Bug: 8348883 added the below line to enable update of due date for batches in WIP status.
due_date = NVL(preq_completion_date,gme_batch_header.due_date),
order_priority = NVL(pord_priority,gme_batch_header.order_priority),
firmed_ind = pfirm_flag, -- B5897392
last_update_date = SYSDATE,
last_updated_by = puser_id,
last_update_login = plogin_id
-- finite_scheduled_ind = 1 /*B5186781*/
WHERE batch_id = pbatch_id;
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_header');
e_msg := e_msg || ' Update Batch Header Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
END update_batch_header;
REM| This procedure will select for update all of the batch details |
REM| except for the transactions. |
REM| HISTORY |
REM| Rajesh Patangya |
REM+=========================================================================+
*/
PROCEDURE lock_batch_details(
pbatch_id IN NUMBER,
pbatch_status OUT NOCOPY NUMBER,
pbatch_last_update OUT NOCOPY DATE,
return_status OUT NOCOPY NUMBER)
IS
l_batch_id NUMBER ;
l_batch_last_update DATE ;
/* lock the batch header being updated */
CURSOR lock_batch_header IS
SELECT
batch_id, batch_status, last_update_date
FROM
gme_batch_header
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
/* lock all of the batch steps for update */
CURSOR lock_batch_steps IS
SELECT
batch_id
FROM
gme_batch_steps
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
/* lock all of the batch step activities for update */
CURSOR lock_batch_activities IS
SELECT
batch_id
FROM
gme_batch_step_activities
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
/* lock all of the batch step resources for update */
CURSOR lock_batch_resources IS
SELECT
batch_id
FROM
gme_batch_step_resources
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
l_batch_last_update := NULL;
l_batch_last_update;
pbatch_last_update := l_batch_last_update;
REM| 1. Number of Operations(Insert/Update/Delete |
REM| 2. Number of Activities(Insert/Update/Delete |
REM| 3. Number of resources (Insert/Update/Delete |
REM| 4. Change Recipe/Validity Rule OR Routing/formula header |
REM| NOTE : |
REM| We are not validating materials overrides as per discussion |
REM| HISTORY |
REM| Rajesh Patangya |
REM| B3583620 - Rearranged the Group By clause and made unique in APS table |
REM| Kaushek B |
REM| B6407903 - Used TRUNC for all the date columns used in the structure_cursor|
REM+=========================================================================+
*/
PROCEDURE validate_structure (
pfmeff_id IN NUMBER,
porganization_id IN NUMBER, -- For R12.0
pgroup_id IN NUMBER,
pheader_id IN NUMBER,
struc_size OUT NOCOPY NUMBER,
return_status OUT NOCOPY NUMBER)
IS
/* Local array definition */
TYPE ref_cursor_typ IS REF CURSOR;
structure_cursor := ' SELECT opm.recipe_id, opm.formula_id, '
||' opm.routing_id, opm.routingstep_id, opm.routingstep_no, '
||' opm.oprn_id, opm.oprn_line_id, opm.oprn_no, opm.activity, '
||' opm.resource_seq_num, opm.recipe_change, opm.validity_rule_change,'
||' opm.formula_header_change, opm.formula_detail_change, '
||' opm.routing_header_change, '
||' opm.rtg_detail_change, opm.rtg_oper_change, '
||' opm.rtg_activity_change, opm.opm_resource_change, '
||' opm.opm_oper_sum, opm.opm_activity_sum,'
||' aps.operation_seq_num, aps.resource_seq_num, '
||' aps.aps_oper_sum, aps.aps_resource_sum, aps.aps_activity_sum '
||' FROM ( '
-- Find the count of routing operations, activity
-- find the routing detail change, operation change, activity change
||' SELECT recipe_id, formula_id, routing_id, routingstep_id, '
||' routingstep_no, oprn_id, oprn_line_id, activity, oprn_no , '
||' seq_dep_ind resource_seq_num, '
||' offset_interval, '
||' gr_last_date recipe_change,'
||' ffe_last_date validity_rule_change, '
||' ffm_last_date formula_header_change,'
||' fmd_last_date formula_detail_change,'
||' frh_last_date routing_header_change,'
||' SUM(frd_last_date) '
||' OVER (PARTITION BY routing_id) rtg_detail_change , '
||' SUM(fom_last_date) '
||' OVER (PARTITION BY routing_id) rtg_oper_change , '
||' SUM(goa_last_date) '
||' OVER (PARTITION BY routing_id) rtg_activity_change , '
||' opm_resource_change , '
||' opm_oper_sum, '
-- PS Issue B6045398, Activity count is incorrect
||' COUNT(unique oprn_line_id) OVER (PARTITION BY '
||' routing_id,oprn_id ) opm_activity_sum '
||' FROM ( '
||' SELECT gr.recipe_id, gr.formula_id, gr.routing_id, '
||' frd.routingstep_id, frd.routingstep_no, '
||' nvl(goa.sequence_dependent_ind,0) seq_dep_ind, g1.gen_lupd, '
||' goa.offset_interval, '
-- B5714301, changed the position of operation count
||' COUNT(unique frd.routingstep_no) OVER (PARTITION BY '
||' gr.routing_id) opm_oper_sum, '
-- Recipe/Validity Rule OR Routing/formula header changed
||' DECODE(sign(g1.gen_lupd '
||' - trunc(gr.last_update_date)), 1,1,0,1,-1,-600) gr_last_date, '
||' DECODE(sign(g1.gen_lupd '
||' - trunc(ffe.last_update_date)), 1,1,0,1,-1,-600) ffe_last_date,'
||' DECODE(sign(g1.gen_lupd '
||' - trunc(ffm.last_update_date)), 1,1,0,1,-1,-600) ffm_last_date, '
||' ( SELECT sum(DECODE(sign(gen_lupd'
||' - trunc(fmd.last_update_date)), 1,1,0,1,-1,-600))'
||' FROM fm_matl_dtl fmd '
||' WHERE fmd.formula_id = gr.formula_id) fmd_last_date,'
||' DECODE(sign(g1.gen_lupd '
||' - trunc(frh.last_update_date)), 1,1,0,1,-1,-600) frh_last_date, '
||' DECODE(sign(g1.gen_lupd '
||' - trunc(frd.last_update_date)), 1,1,0,1,-1,-600) frd_last_date, '
||' fom.oprn_id, fom.oprn_no,'
||' DECODE(sign(g1.gen_lupd '
||' - trunc(fom.last_update_date)), 1,1,0,1,-1,-600) fom_last_date, '
||' goa.oprn_line_id, goa.activity, '
||' DECODE(sign(g1.gen_lupd '
||' - trunc(goa.last_update_date)), 1,1,0,1,-1,-600) goa_last_date, '
-- Bug 12716557 - ORA-00932: Error Vkinduri
--||' nvl((SELECT SUM(DECODE(sign(g1.gen_lupd '
--||' - trunc(gor.last_update_date)), 1,1,0,1,-1,-600))'
||' nvl((SELECT SUM(DECODE(sign((SELECT creation_date gen_lupd '
||' FROM GMP_APS_OUTPUT_TBL WHERE process_id = :pgrp1 '
||' AND header_id = :phdr1) '
||' - trunc(gor.last_update_date)), 1,1,0,1,-1,-600))'
||' FROM'
||' gmd_operation_resources gor, '
||' cr_rsrc_dtl crd '
||' WHERE'
||' goa.oprn_line_id = gor.oprn_line_id '
||' AND crd.organization_id = :porgid ' -- For R12.0
||' AND crd.resources = gor.resources ' -- For R12.0
||' AND crd.delete_mark = 0 ' -- For R12.0
||' AND crd.schedule_ind <> 3 ' -- Do Not plan
/*
OPM is sending ZERO resource usages along with routing, if it is not a primary resource. APS is sending back these resources may be with usage or ZERO resource usage. We have to consider this resource for validation, but for final update
we have to use GME WAY to update this type of resource. This is only
applicable for NEW BATCH */
-- ||' AND gor.resource_usage > 0' -- Do Not plan R12
||' GROUP BY gor.oprn_line_id '
||' ),0) opm_resource_change'
||' FROM '
||' gmd_recipes_b gr, '
||' gmd_recipe_validity_rules ffe, '
||' fm_form_mst ffm, '
||' fm_rout_hdr frh, '
||' fm_rout_dtl frd , '
||' gmd_operations fom, '
||' gmd_operation_activities goa, '
-- B6051303, PS Issue
||' ( SELECT creation_date gen_lupd '
||' FROM GMP_APS_OUTPUT_TBL WHERE process_id = :pgrp1 '
||' AND header_id = :phdr1 ) g1 '
||' WHERE gr.recipe_id = ffe.recipe_id '
||' AND gr.routing_id = frh.routing_id '
||' AND gr.formula_id = ffm.formula_id '
||' AND frd.routing_id = gr.routing_id '
||' AND frd.oprn_id = fom.oprn_id '
||' AND fom.oprn_id = goa.oprn_id '
||' AND ffe.recipe_validity_rule_id = :eff1 '
||' ) '
||' WHERE opm_resource_change <> 0 '
||' ) OPM , '
||' ( '
-- Query will take count at operation, activity for resources
||' SELECT a.operation_seq_num, a.schedule_seq_num resource_seq_num, '
||' count(unique a.operation_seq_num) ' /* B3583620 */
||' OVER (PARTITION BY b.process_id, b.header_id) aps_oper_sum, '
||' count(unique a.schedule_seq_num) ' /* B3583620 */
||' OVER (PARTITION BY a.operation_seq_num ) aps_activity_sum, '
||' count(a.resource_id_new) '
||' OVER (PARTITION BY a.operation_seq_num, '
||' a.schedule_seq_num) aps_resource_sum '
||' FROM gmp_aps_output_dtl a, '
||' gmp_aps_output_tbl b '
||' WHERE a.parent_header_id = b.header_id '
||' AND a.group_id = b.process_id '
||' AND b.process_id = :pgrp2 '
||' AND b.header_id = :phdr2 '
||' AND b.effectivity_id = :eff2 '
||' AND a.load_type = 1 '
||' AND a.parent_seq_num IS NULL '
||' ) APS '
||' WHERE opm.resource_seq_num(+) = aps.resource_seq_num '
||' AND opm.routingstep_no(+) = aps.operation_seq_num '
||' AND opm.routingstep_no IS NOT NULL ' ;
e_msg := e_msg || ' Routing Opeartion/Activity deleted';
e_msg := e_msg || ' Routing resources added or deleted. Details of the batch will NOT be synchronized.' ;
e_msg := e_msg || ' Routing steps added or deleted. Details of the batch will NOT be synchronized.' ;
e_msg := e_msg || ' Routing activity added or deleted. Details of the batch will NOT be synchronized.' ;
SELECT to_char(sysdate,'DD-MON-RRRR HH24:MI:SS')
INTO cur_time FROM sys.dual ;
REM| update_activity_offsets (Bug # 3679906) |
REM| DESCRIPTION |
REM| This procedure is called by update_batches and also by the |
REM| new batch/reschedule forms to update the activity |
REM| offsets for each of the batch |
REM| HISTORY |
REM| Rajesh Patangya |
REM+=========================================================================+
*/
PROCEDURE update_activity_offsets ( batch_id IN NUMBER) IS
TYPE offset_rec IS RECORD(
batchstep_id NUMBER(20),
batchstep_no NUMBER(10),
batchstep_activity_id NUMBER(20),
activity VARCHAR2(16),
offset_interval NUMBER,
oprn_line_id NUMBER(20),
actual_usage NUMBER ,
start_date VARCHAR2(30),
completion_date VARCHAR2(30),
prev_act_offset NUMBER(15),
final_offset NUMBER
);
v_batch_cursor := ' SELECT batchstep_id '
||' batchstep_no, '
||' batchstep_activity_id activity_id, '
||' activity, '
||' offset_interval orig_offset, '
||' oprn_line_id, '
||' actual_usage, '
||' to_char(plan_start_date,'||''''||'DD-MON-YYYY HH24:MI:SS'||''''||') start_date, '
||' to_char(plan_cmplt_date,'||''''||'DD-MON-YYYY HH24:MI:SS'||''''||') completion_date, '
||' DECODE( sign(batchstep_no - NVL((lag(batchstep_no,1) over(order by batchstep_id)),0) '
||' ),-1,0,1,0,(lag(actual_usage,1) over(order by batchstep_id)) ) prev_act_offset, '
||' (SUM(actual_usage) '
||' OVER (PARTITION BY batchstep_no order by batchstep_no '
||' ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - actual_usage) final_offset '
||' FROM ( '
||' SELECT distinct '
||' gsa.batchstep_id, '
||' gbs.batchstep_no, '
||' gsa.batchstep_activity_id, '
||' gsa.activity, '
||' gsa.offset_interval, '
||' nvl(gsa.oprn_line_id,0) oprn_line_id, '
||' gsa.plan_start_date, '
||' gsa.plan_cmplt_date, '
||' DECODE(nvl(gsa.sequence_dependent_ind,0),1,1,0) , '
||' max((gsr.plan_rsrc_usage/gsr.plan_rsrc_count)) actual_usage '
||' FROM gme_batch_step_activities gsa,'
||' gme_batch_step_resources gsr,'
||' gme_batch_steps gbs '
||' WHERE gsa.batch_id = :p_batch_id '
||' AND gbs.batch_id = gsa.batch_id '
||' AND gsr.batch_id = gsa.batch_id '
||' AND gsa.delete_mark = 0 '
||' AND gbs.delete_mark = 0 '
||' AND gbs.step_status = 1 '
||' AND gsa.batchstep_id = gbs.batchstep_id '
||' AND gsr.batchstep_activity_id = gsa.batchstep_activity_id '
||' AND gsr.prim_rsrc_ind = 1 '
||' GROUP BY '
||' gsa.batchstep_id, '
||' gbs.batchstep_no, '
||' gsa.batchstep_activity_id, '
||' gsa.activity, '
||' gsa.offset_interval, '
||' gsa.oprn_line_id, '
||' gsa.plan_start_date, '
||' gsa.plan_cmplt_date, '
||' DECODE(nvl(gsa.sequence_dependent_ind,0),1,1,0) '
||' ORDER BY gbs.batchstep_no, '
||' DECODE(nvl(gsa.sequence_dependent_ind,0),1,1,0) DESC, '
||' gsa.offset_interval, gsa.activity, nvl(gsa.oprn_line_id,0) '
||' ) ';
UPDATE gme_batch_step_activities
SET offset_interval = offset_tab(p).final_offset
WHERE batch_id = v_batch_id
AND batchstep_id = offset_tab(p).batchstep_id
AND batchstep_activity_id = offset_tab(p).batchstep_activity_id
AND oprn_line_id = offset_tab(p).oprn_line_id;
fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_activity_offsets');
e_msg := e_msg || ' update_activity_offsets Failure '|| TO_CHAR(SQLCODE)
||': '||SQLERRM;
END update_activity_offsets;
REM| update_batches (Bug # 3679906) |
REM| DESCRIPTION |
REM| This procedure is called by the concurernt program for all the |
REM| batches to make GME batches in sync with APS suggestions |
REM| which in turn calls the update_activity_offsets to update activity |
REM| offsets in each btach once the APS engine has completed. |
REM| |
REM| HISTORY |
REM| Rajesh Patangya |
REM+=========================================================================+
*/
PROCEDURE update_batches IS
TYPE batch_fet_cur IS REF CURSOR;
v_batch_sql := ' SELECT batch_id FROM gme_batch_header '
|| ' WHERE delete_mark = 0 AND batch_status IN (1,2) ';
update_activity_offsets (m_batch_id);
END update_batches;
REM| Insert_charges |
REM| DESCRIPTION |
REM| This procedure Deletes/Inserts the pending step charges |
REM| HISTORY |
REM| Rajesh Patangya |
REM+=========================================================================+
*/
PROCEDURE insert_charges (
pbatch_id IN NUMBER,
pgroup_id IN NUMBER,
pheader_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
BEGIN
BEGIN
-- NOTE: The steps wll not be having activity Sequence Number ??
-- For WIP steps No deletes
DELETE from GME_BATCH_STEP_CHARGES
WHERE batch_id = pbatch_id
AND BATCHSTEP_ID IN ( SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = pbatch_id
AND step_status = 1 );
fnd_msg_pub.add_exc_msg('gmp_aps_writer','Insert_charges');
e_msg := e_msg || ' Delete Charge Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
INSERT INTO GME_BATCH_STEP_CHARGES
(
BATCH_ID,
BATCHSTEP_ID,
ACTIVITY_SEQUENCE_NUMBER,
RESOURCES,
CHARGE_NUMBER,
CHARGE_QUANTITY,
PLAN_START_DATE,
PLAN_CMPLT_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY
)
SELECT gad.wip_entity_id,
gbs.batchstep_id, -- Operation Id
gad.schedule_seq_num, -- Activity Number
crd.resources ,
gad.charge_number,
gad.required_quantity,
gad.start_date,
gad.completion_date,
gad.last_update_login,
gad.last_updated_by,
gad.last_update_date,
gad.creation_date,
gad.created_by
FROM gmp_aps_output_dtl gad,
gmp_aps_output_tbl gao,
gme_batch_steps gbs,
cr_rsrc_dtl crd
WHERE gad.parent_header_id = gao.header_id
AND gad.group_id = gao.process_id
AND gad.organization_id = gao.organization_id
AND gad.wip_entity_id = pbatch_id
AND gao.process_id = pgroup_id
AND gao.header_id = pheader_id
AND gao.batch_id = gad.wip_entity_id
AND gad.load_type = 10
AND gad.resource_id_new = crd.resource_id
AND gad.organization_id = crd.organization_id
AND gbs.batchstep_no = gad.operation_seq_num
AND gbs.batch_id = gao.batch_id
AND gbs.delete_mark = 0
AND gbs.step_status = 1 ; -- Pending steps
fnd_msg_pub.add_exc_msg('gmp_aps_writer','Insert_charges');
e_msg := e_msg || ' Insert_charges Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
END Insert_charges ;