[Home] [Help]
1879: BEGIN
1880: l_xst := 0;
1881: select resource_id
1882: into v_wljdi_resource_id_new(l_d)
1883: from wsm_copy_op_resources wcor
1884: where wcor.wip_entity_id = p_wip_entity_id
1885: and wcor.operation_seq_num = l_rtg_op_seq_num
1886: and wcor.resource_seq_num = v_wljdi_resource_seq_num(l_d);
1887: EXCEPTION
2446: begin
2447: l_stmt_num := 70.3;
2448: select resource_seq_num
2449: into l_res_seq_num
2450: from wsm_copy_op_resources wcor
2451: where wcor.wip_entity_id = p_wip_entity_id
2452: and wcor.organization_id = p_org_id
2453: and wcor.operation_seq_num = l_rtg_op_seq_num
2454: and wcor.resource_id = l_resource_id
3191:
3192:
3193: cursor c_wsm_rpl_grp is
3194: select resource_id
3195: from wsm_copy_op_resources
3196: where wip_entity_id = p_wip_entity_id
3197: and operation_seq_num = l_rtg_op_seq_num
3198: and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
3199: and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0)
3302: BEGIN
3303: l_xst := 0;
3304: select 1
3305: into l_xst
3306: from wsm_copy_op_resources wcor
3307: where wcor.wip_entity_id = p_wip_entity_id
3308: and wcor.operation_seq_num = l_rtg_op_seq_num
3309: and wcor.resource_id = l_resource_id
3310: and NVL(wcor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
3808: BEGIN
3809: l_xst := 0;
3810: select 1
3811: into l_xst
3812: from wsm_copy_op_resources wcor
3813: where wcor.wip_entity_id = p_wip_entity_id
3814: and wcor.operation_seq_num = l_rtg_op_seq_num
3815: and wcor.resource_seq_num = l_res_seq_num;
3816: EXCEPTION
4079:
4080: if l_res_seq_num is null then
4081: select nvl(max(resource_seq_num),0)+10
4082: into l_res_seq_num
4083: from wsm_copy_op_resources wor
4084: where wor.organization_id = p_org_id
4085: and wor.wip_entity_id = p_wip_entity_id
4086: and wor.operation_seq_num = l_rtg_op_seq_num;
4087: else
4088: l_xst := 0;
4089: begin
4090: select 1
4091: into l_xst
4092: from wsm_copy_op_resources wor
4093: where wor.organization_id = p_org_id
4094: and wor.wip_entity_id = p_wip_entity_id
4095: and wor.operation_seq_num = l_rtg_op_seq_num
4096: and wor.resource_seq_num =l_res_seq_num;
4112: select 'N'
4113: into p_v_res(l_i).reco_flag
4114: from dual
4115: where exists( select 1
4116: from wsm_copy_op_resources wcor
4117: where wcor.organization_id = p_org_id
4118: and wcor.wip_entity_id = p_wip_entity_id
4119: and wcor.operation_seq_num = l_rtg_op_seq_num
4120: and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
4132: select 'Y'
4133: into p_v_res(l_i).reco_flag
4134: from dual
4135: where exists( select 1
4136: from wsm_copy_op_resources wcor
4137: where wcor.organization_id = p_org_id
4138: and wcor.wip_entity_id = p_wip_entity_id
4139: and wcor.operation_seq_num = l_rtg_op_seq_num
4140: and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
4152: select 'N'
4153: into p_v_res(l_i).reco_flag
4154: from dual
4155: where exists( select 1
4156: from wsm_copy_op_resources wcor
4157: where wcor.organization_id = p_org_id
4158: and wcor.wip_entity_id = p_wip_entity_id
4159: and wcor.operation_seq_num = l_rtg_op_seq_num
4160: and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
4490: l_stmt_num := 5;
4491: BEGIN
4492: select resource_seq_num
4493: into l_res_seq_num
4494: from wsm_copy_op_resources WCOR
4495: where WCOR.wip_entity_id = p_wip_entity_id
4496: and WCOR.organization_id = p_org_id
4497: and WCOR.operation_seq_num = l_rtg_op_seq_num
4498: and WCOR.parent_resource_seq_num = p_parent_res_seq_num
4606: into l_res_sdate,
4607: l_res_edate,
4608: l_resource_id, -- this is used for validate instance_id
4609: l_res_recommended
4610: from wsm_copy_op_resources
4611: where wip_entity_id = p_wip_entity_id
4612: and organization_id = p_org_id
4613: and operation_seq_num = l_rtg_op_seq_num
4614: and resource_seq_num = l_res_seq_num;
4943: IF p_handle_wip_cur_op = 'N' THEN
4944: BEGIN
4945: select resource_seq_num
4946: into l_res_seq_num
4947: from wsm_copy_op_resources WCOR
4948: where WCOR.wip_entity_id = p_wip_entity_id
4949: and WCOR.organization_id = p_org_id
4950: and WCOR.operation_seq_num = l_rtg_op_seq_num
4951: and WCOR.parent_resource_seq_num = p_parent_res_seq_num
5037: l_res_recommended,
5038: l_the_sdate,
5039: l_the_edate
5040: from wsm_copy_operations wco,
5041: wsm_copy_op_resources wcor
5042: where wco.wip_entity_id = p_wip_entity_id
5043: and wco.operation_seq_num = l_rtg_op_seq_num
5044: and wcor.wip_entity_id = wco.wip_entity_id
5045: and wcor.operation_seq_num = wco.operation_seq_num
13211:
13212: l_setup_exists := 0;
13213: select count(*)
13214: into l_setup_exists
13215: from wsm_copy_op_resources wcor
13216: where wcor.organization_id = p_org_id
13217: and wcor.wip_entity_id = p_wip_entity_id
13218: and wcor.operation_seq_num = p_op_seq_num
13219: and wcor.parent_resource_seq_num = p_par_res_seq_num
13224: where wcori.organization_id = p_org_id
13225: and wcori.wip_entity_id = p_wip_entity_id
13226: and wcori.operation_seq_num = p_op_seq_num
13227: and wcori.resource_seq_num in (select resource_seq_num
13228: from wsm_copy_op_resources wcor
13229: where wcor.organization_id = p_org_id
13230: and wcor.wip_entity_id = p_wip_entity_id
13231: and wcor.operation_seq_num = p_op_seq_num
13232: and wcor.parent_resource_seq_num = p_par_res_seq_num
13237: where wcoru.organization_id = p_org_id
13238: and wcoru.wip_entity_id = p_wip_entity_id
13239: and wcoru.operation_seq_num = p_op_seq_num
13240: and wcoru.resource_seq_num in (select resource_seq_num
13241: from wsm_copy_op_resources wcor
13242: where wcor.organization_id = p_org_id
13243: and wcor.wip_entity_id = p_wip_entity_id
13244: and wcor.operation_seq_num = p_op_seq_num
13245: and wcor.parent_resource_seq_num = p_par_res_seq_num
13246: );
13247: /* delete the setup resources.... */
13248: l_stmt_num := 110;
13249:
13250: delete from wsm_copy_op_resources wcor
13251: where wcor.organization_id = p_org_id
13252: and wcor.wip_entity_id = p_wip_entity_id
13253: and wcor.operation_seq_num = p_op_seq_num
13254: and wcor.parent_resource_seq_num = p_par_res_seq_num;
13507: l_res_id := 0;
13508:
13509: select wcor.resource_id,wcor.recommended
13510: into l_res_id,l_res_recommended
13511: from wsm_copy_op_resources wcor
13512: where wcor.organization_id = p_org_id
13513: and wcor.wip_entity_id = p_wip_entity_id
13514: and wcor.operation_seq_num = p_rtg_op_seq_num
13515: and wcor.resource_seq_num = l_par_res_seq_num;
13566:
13567: begin
13568: select max(resource_seq_num)
13569: into l_max_resource_seq_num
13570: from wsm_copy_op_resources wcor
13571: where wcor.organization_id = p_org_id
13572: and wcor.wip_entity_id = p_wip_entity_id
13573: and wcor.operation_seq_num = p_rtg_op_seq_num;
13574:
13822: l_stmt_num := 180;
13823: begin
13824: select 1
13825: into l_dummy
13826: from wsm_copy_op_resources wcor
13827: where wcor.organization_id = p_org_id
13828: and wcor.wip_entity_id = p_wip_entity_id
13829: and wcor.operation_seq_num = p_rtg_op_seq_num
13830: and wcor.resource_seq_num = p_new_res_tbl(l_index).res_seq_num;
14521: l_stmt_num := 130;
14522: forall i in l_setup_index_tbl.first..l_setup_index_tbl.last
14523:
14524: /* issue the insert statement */
14525: insert into wsm_copy_op_resources
14526: (
14527: wip_entity_id,
14528: operation_seq_num,
14529: resource_seq_num,
15266: c_rpl_grp_num number) is
15267: select resource_id,
15268: replacement_group_num,
15269: resource_seq_num /* ST : Detailed Scheduling added.... */
15270: from wsm_copy_op_resources
15271: where wip_entity_id = c_wip_entity_id
15272: and operation_seq_num = c_rtg_op_seq_num
15273: and nvl(substitute_group_num, -1) = nvl(c_sub_grp_num, -1)
15274: and nvl(replacement_group_num, 0) <> nvl(c_rpl_grp_num, 0);
16314: end;
16315: l_temp :=0;
16316: -- This insert statement adds primary resources for standard operation.
16317:
16318: INSERT INTO WSM_COPY_OP_RESOURCES
16319: (wip_entity_id,
16320: operation_seq_num,
16321: resource_seq_num,
16322: organization_id,
16445: and wco.operation_seq_num = v_wljdi_routing_op_seq_num(l_d);
16446:
16447: -- This insert statement adds substitute resources for standard operation.
16448:
16449: INSERT INTO WSM_COPY_OP_RESOURCES
16450: (wip_entity_id,
16451: operation_seq_num,
16452: resource_seq_num,
16453: organization_id,
16567: null
16568: from wsm_copy_operations wco,
16569: bom_resources br,
16570: bom_std_sub_op_resources bsor,
16571: wsm_copy_op_resources wcor
16572: where bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
16573: and br.resource_id = bsor.resource_id
16574: and nvl(br.disable_date, SYSDATE + 1) > SYSDATE
16575: and wco.organization_id = p_org_id
16581: and bsor1.substitute_group_num = bsor.substitute_group_num )
16582: and wcor.wip_entity_id = wco.wip_entity_id
16583: and wcor.operation_seq_num = wco.operation_seq_num
16584: and wcor.resource_seq_num = (select max(resource_seq_num)
16585: from wsm_copy_op_resources wcor1
16586: where wip_entity_id = wco.wip_entity_id
16587: and operation_seq_num = wco.operation_seq_num);
16588:
16589: -- Create default Usages for the added primary resources having schedule flag other than 'No'.
16622: p_request_id,
16623: p_program_appl_id,
16624: p_program_id,
16625: p_prog_updt_date
16626: from wsm_copy_op_resources wcor
16627: where wcor.organization_id = p_org_id
16628: and wcor.wip_entity_id = p_wip_entity_id
16629: and wcor.operation_seq_num = v_wljdi_routing_op_seq_num(l_d)
16630: and wcor.recommended = 'Y'
17457: delete from wsm_copy_operations
17458: where wip_entity_id = p_wip_entity_id
17459: and operation_seq_num = l_rtg_op_seq_num;
17460:
17461: delete from wsm_copy_op_resources
17462: where wip_entity_id = p_wip_entity_id
17463: and operation_seq_num = l_rtg_op_seq_num;
17464:
17465: delete from wsm_copy_op_resource_instances
17745: and wcoru.operation_seq_num = l_rtg_op_seq_num
17746: and wcoru.resource_seq_num = l_resource_seq_num
17747: and wcoru.organization_id = p_org_id ;
17748:
17749: delete from wsm_copy_op_resources wcor
17750: where wcor.wip_entity_id = p_wip_entity_id
17751: and wcor.operation_seq_num = l_rtg_op_seq_num
17752: and wcor.resource_seq_num = l_resource_seq_num
17753: and wcor.organization_id = p_org_id;
18136: if p_error_code <> 0 then
18137: raise e_wljdi_common_error;
18138: end if;
18139:
18140: INSERT INTO WSM_COPY_OP_RESOURCES
18141: (wip_entity_id,
18142: operation_seq_num,
18143: resource_seq_num,
18144: organization_id,
19008: -- Update WCOR
19009: if(l_v_rtg_op_seq.first IS NOT NULL) then
19010: l_stmt_num := 270.1;
19011: forall i in l_v_rtg_op_seq.first..l_v_rtg_op_seq.last
19012: UPDATE WSM_COPY_OP_RESOURCES
19013: set reco_start_date = l_v_start_date(i),
19014: reco_completion_date = l_v_compl_date(i),
19015: recommended = NVL(l_v_recoflag(i), recommended),
19016:
19067: --and (operation_seq_num,
19068: -- resource_seq_num) in (
19069: -- select operation_seq_num,
19070: -- resource_seq_num
19071: -- from WSM_COPY_OP_RESOURCES
19072: -- where wip_entity_id = p_wip_entity_id
19073: -- and recommended <> 'Y' );
19074: l_stmt_num := 270.3;
19075: --delete WSM_COPY_OP_RESOURCE_INSTANCES
19077: --and (operation_seq_num,
19078: -- resource_seq_num) in (
19079: -- select operation_seq_num,
19080: -- resource_seq_num
19081: -- from WSM_COPY_OP_RESOURCES
19082: -- where wip_entity_id = p_wip_entity_id
19083: -- and recommended <> 'Y' );
19084:
19085: end if;