[Home] [Help]
1874: BEGIN
1875: l_xst := 0;
1876: select resource_id
1877: into v_wljdi_resource_id_new(l_d)
1878: from wsm_copy_op_resources wcor
1879: where wcor.wip_entity_id = p_wip_entity_id
1880: and wcor.operation_seq_num = l_rtg_op_seq_num
1881: and wcor.resource_seq_num = v_wljdi_resource_seq_num(l_d);
1882: EXCEPTION
2441: begin
2442: l_stmt_num := 70.3;
2443: select resource_seq_num
2444: into l_res_seq_num
2445: from wsm_copy_op_resources wcor
2446: where wcor.wip_entity_id = p_wip_entity_id
2447: and wcor.organization_id = p_org_id
2448: and wcor.operation_seq_num = l_rtg_op_seq_num
2449: and wcor.resource_id = l_resource_id
3186:
3187:
3188: cursor c_wsm_rpl_grp is
3189: select resource_id
3190: from wsm_copy_op_resources
3191: where wip_entity_id = p_wip_entity_id
3192: and operation_seq_num = l_rtg_op_seq_num
3193: and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
3194: and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0)
3297: BEGIN
3298: l_xst := 0;
3299: select 1
3300: into l_xst
3301: from wsm_copy_op_resources wcor
3302: where wcor.wip_entity_id = p_wip_entity_id
3303: and wcor.operation_seq_num = l_rtg_op_seq_num
3304: and wcor.resource_id = l_resource_id
3305: and NVL(wcor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
3803: BEGIN
3804: l_xst := 0;
3805: select 1
3806: into l_xst
3807: from wsm_copy_op_resources wcor
3808: where wcor.wip_entity_id = p_wip_entity_id
3809: and wcor.operation_seq_num = l_rtg_op_seq_num
3810: and wcor.resource_seq_num = l_res_seq_num;
3811: EXCEPTION
4074:
4075: if l_res_seq_num is null then
4076: select nvl(max(resource_seq_num),0)+10
4077: into l_res_seq_num
4078: from wsm_copy_op_resources wor
4079: where wor.organization_id = p_org_id
4080: and wor.wip_entity_id = p_wip_entity_id
4081: and wor.operation_seq_num = l_rtg_op_seq_num;
4082: else
4083: l_xst := 0;
4084: begin
4085: select 1
4086: into l_xst
4087: from wsm_copy_op_resources wor
4088: where wor.organization_id = p_org_id
4089: and wor.wip_entity_id = p_wip_entity_id
4090: and wor.operation_seq_num = l_rtg_op_seq_num
4091: and wor.resource_seq_num =l_res_seq_num;
4107: select 'N'
4108: into p_v_res(l_i).reco_flag
4109: from dual
4110: where exists( select 1
4111: from wsm_copy_op_resources wcor
4112: where wcor.organization_id = p_org_id
4113: and wcor.wip_entity_id = p_wip_entity_id
4114: and wcor.operation_seq_num = l_rtg_op_seq_num
4115: and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
4127: select 'Y'
4128: into p_v_res(l_i).reco_flag
4129: from dual
4130: where exists( select 1
4131: from wsm_copy_op_resources wcor
4132: where wcor.organization_id = p_org_id
4133: and wcor.wip_entity_id = p_wip_entity_id
4134: and wcor.operation_seq_num = l_rtg_op_seq_num
4135: and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
4147: select 'N'
4148: into p_v_res(l_i).reco_flag
4149: from dual
4150: where exists( select 1
4151: from wsm_copy_op_resources wcor
4152: where wcor.organization_id = p_org_id
4153: and wcor.wip_entity_id = p_wip_entity_id
4154: and wcor.operation_seq_num = l_rtg_op_seq_num
4155: and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
4485: l_stmt_num := 5;
4486: BEGIN
4487: select resource_seq_num
4488: into l_res_seq_num
4489: from wsm_copy_op_resources WCOR
4490: where WCOR.wip_entity_id = p_wip_entity_id
4491: and WCOR.organization_id = p_org_id
4492: and WCOR.operation_seq_num = l_rtg_op_seq_num
4493: and WCOR.parent_resource_seq_num = p_parent_res_seq_num
4601: into l_res_sdate,
4602: l_res_edate,
4603: l_resource_id, -- this is used for validate instance_id
4604: l_res_recommended
4605: from wsm_copy_op_resources
4606: where wip_entity_id = p_wip_entity_id
4607: and organization_id = p_org_id
4608: and operation_seq_num = l_rtg_op_seq_num
4609: and resource_seq_num = l_res_seq_num;
4938: IF p_handle_wip_cur_op = 'N' THEN
4939: BEGIN
4940: select resource_seq_num
4941: into l_res_seq_num
4942: from wsm_copy_op_resources WCOR
4943: where WCOR.wip_entity_id = p_wip_entity_id
4944: and WCOR.organization_id = p_org_id
4945: and WCOR.operation_seq_num = l_rtg_op_seq_num
4946: and WCOR.parent_resource_seq_num = p_parent_res_seq_num
5032: l_res_recommended,
5033: l_the_sdate,
5034: l_the_edate
5035: from wsm_copy_operations wco,
5036: wsm_copy_op_resources wcor
5037: where wco.wip_entity_id = p_wip_entity_id
5038: and wco.operation_seq_num = l_rtg_op_seq_num
5039: and wcor.wip_entity_id = wco.wip_entity_id
5040: and wcor.operation_seq_num = wco.operation_seq_num
13107:
13108: l_setup_exists := 0;
13109: select count(*)
13110: into l_setup_exists
13111: from wsm_copy_op_resources wcor
13112: where wcor.organization_id = p_org_id
13113: and wcor.wip_entity_id = p_wip_entity_id
13114: and wcor.operation_seq_num = p_op_seq_num
13115: and wcor.parent_resource_seq_num = p_par_res_seq_num
13120: where wcori.organization_id = p_org_id
13121: and wcori.wip_entity_id = p_wip_entity_id
13122: and wcori.operation_seq_num = p_op_seq_num
13123: and wcori.resource_seq_num in (select resource_seq_num
13124: from wsm_copy_op_resources wcor
13125: where wcor.organization_id = p_org_id
13126: and wcor.wip_entity_id = p_wip_entity_id
13127: and wcor.operation_seq_num = p_op_seq_num
13128: and wcor.parent_resource_seq_num = p_par_res_seq_num
13133: where wcoru.organization_id = p_org_id
13134: and wcoru.wip_entity_id = p_wip_entity_id
13135: and wcoru.operation_seq_num = p_op_seq_num
13136: and wcoru.resource_seq_num in (select resource_seq_num
13137: from wsm_copy_op_resources wcor
13138: where wcor.organization_id = p_org_id
13139: and wcor.wip_entity_id = p_wip_entity_id
13140: and wcor.operation_seq_num = p_op_seq_num
13141: and wcor.parent_resource_seq_num = p_par_res_seq_num
13142: );
13143: /* delete the setup resources.... */
13144: l_stmt_num := 110;
13145:
13146: delete from wsm_copy_op_resources wcor
13147: where wcor.organization_id = p_org_id
13148: and wcor.wip_entity_id = p_wip_entity_id
13149: and wcor.operation_seq_num = p_op_seq_num
13150: and wcor.parent_resource_seq_num = p_par_res_seq_num;
13403: l_res_id := 0;
13404:
13405: select wcor.resource_id,wcor.recommended
13406: into l_res_id,l_res_recommended
13407: from wsm_copy_op_resources wcor
13408: where wcor.organization_id = p_org_id
13409: and wcor.wip_entity_id = p_wip_entity_id
13410: and wcor.operation_seq_num = p_rtg_op_seq_num
13411: and wcor.resource_seq_num = l_par_res_seq_num;
13462:
13463: begin
13464: select max(resource_seq_num)
13465: into l_max_resource_seq_num
13466: from wsm_copy_op_resources wcor
13467: where wcor.organization_id = p_org_id
13468: and wcor.wip_entity_id = p_wip_entity_id
13469: and wcor.operation_seq_num = p_rtg_op_seq_num;
13470:
13718: l_stmt_num := 180;
13719: begin
13720: select 1
13721: into l_dummy
13722: from wsm_copy_op_resources wcor
13723: where wcor.organization_id = p_org_id
13724: and wcor.wip_entity_id = p_wip_entity_id
13725: and wcor.operation_seq_num = p_rtg_op_seq_num
13726: and wcor.resource_seq_num = p_new_res_tbl(l_index).res_seq_num;
14417: l_stmt_num := 130;
14418: forall i in l_setup_index_tbl.first..l_setup_index_tbl.last
14419:
14420: /* issue the insert statement */
14421: insert into wsm_copy_op_resources
14422: (
14423: wip_entity_id,
14424: operation_seq_num,
14425: resource_seq_num,
15144: c_rpl_grp_num number) is
15145: select resource_id,
15146: replacement_group_num,
15147: resource_seq_num /* ST : Detailed Scheduling added.... */
15148: from wsm_copy_op_resources
15149: where wip_entity_id = c_wip_entity_id
15150: and operation_seq_num = c_rtg_op_seq_num
15151: and nvl(substitute_group_num, -1) = nvl(c_sub_grp_num, -1)
15152: and nvl(replacement_group_num, 0) <> nvl(c_rpl_grp_num, 0);
15938: end;
15939: l_temp :=0;
15940: -- This insert statement adds primary resources for standard operation.
15941:
15942: INSERT INTO WSM_COPY_OP_RESOURCES
15943: (wip_entity_id,
15944: operation_seq_num,
15945: resource_seq_num,
15946: organization_id,
16069: and wco.operation_seq_num = v_wljdi_routing_op_seq_num(l_d);
16070:
16071: -- This insert statement adds substitute resources for standard operation.
16072:
16073: INSERT INTO WSM_COPY_OP_RESOURCES
16074: (wip_entity_id,
16075: operation_seq_num,
16076: resource_seq_num,
16077: organization_id,
16191: null
16192: from wsm_copy_operations wco,
16193: bom_resources br,
16194: bom_std_sub_op_resources bsor,
16195: wsm_copy_op_resources wcor
16196: where bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
16197: and br.resource_id = bsor.resource_id
16198: and nvl(br.disable_date, SYSDATE + 1) > SYSDATE
16199: and wco.organization_id = p_org_id
16205: and bsor1.substitute_group_num = bsor.substitute_group_num )
16206: and wcor.wip_entity_id = wco.wip_entity_id
16207: and wcor.operation_seq_num = wco.operation_seq_num
16208: and wcor.resource_seq_num = (select max(resource_seq_num)
16209: from wsm_copy_op_resources wcor1
16210: where wip_entity_id = wco.wip_entity_id
16211: and operation_seq_num = wco.operation_seq_num);
16212:
16213: -- Create default Usages for the added primary resources having schedule flag other than 'No'.
16246: p_request_id,
16247: p_program_appl_id,
16248: p_program_id,
16249: p_prog_updt_date
16250: from wsm_copy_op_resources wcor
16251: where wcor.organization_id = p_org_id
16252: and wcor.wip_entity_id = p_wip_entity_id
16253: and wcor.operation_seq_num = v_wljdi_routing_op_seq_num(l_d)
16254: and wcor.recommended = 'Y'
17015: delete from wsm_copy_operations
17016: where wip_entity_id = p_wip_entity_id
17017: and operation_seq_num = l_rtg_op_seq_num;
17018:
17019: delete from wsm_copy_op_resources
17020: where wip_entity_id = p_wip_entity_id
17021: and operation_seq_num = l_rtg_op_seq_num;
17022:
17023: delete from wsm_copy_op_resource_instances
17266: and wcoru.operation_seq_num = l_rtg_op_seq_num
17267: and wcoru.resource_seq_num = l_resource_seq_num
17268: and wcoru.organization_id = p_org_id ;
17269:
17270: delete from wsm_copy_op_resources wcor
17271: where wcor.wip_entity_id = p_wip_entity_id
17272: and wcor.operation_seq_num = l_rtg_op_seq_num
17273: and wcor.resource_seq_num = l_resource_seq_num
17274: and wcor.organization_id = p_org_id;
17657: if p_error_code <> 0 then
17658: raise e_wljdi_common_error;
17659: end if;
17660:
17661: INSERT INTO WSM_COPY_OP_RESOURCES
17662: (wip_entity_id,
17663: operation_seq_num,
17664: resource_seq_num,
17665: organization_id,
18447: -- Update WCOR
18448: if(l_v_rtg_op_seq.first IS NOT NULL) then
18449: l_stmt_num := 270.1;
18450: forall i in l_v_rtg_op_seq.first..l_v_rtg_op_seq.last
18451: UPDATE WSM_COPY_OP_RESOURCES
18452: set reco_start_date = l_v_start_date(i),
18453: reco_completion_date = l_v_compl_date(i),
18454: recommended = NVL(l_v_recoflag(i), recommended),
18455:
18490: --and (operation_seq_num,
18491: -- resource_seq_num) in (
18492: -- select operation_seq_num,
18493: -- resource_seq_num
18494: -- from WSM_COPY_OP_RESOURCES
18495: -- where wip_entity_id = p_wip_entity_id
18496: -- and recommended <> 'Y' );
18497: l_stmt_num := 270.3;
18498: --delete WSM_COPY_OP_RESOURCE_INSTANCES
18500: --and (operation_seq_num,
18501: -- resource_seq_num) in (
18502: -- select operation_seq_num,
18503: -- resource_seq_num
18504: -- from WSM_COPY_OP_RESOURCES
18505: -- where wip_entity_id = p_wip_entity_id
18506: -- and recommended <> 'Y' );
18507:
18508: end if;