[Home] [Help]
1846: l_xst := 0;
1847:
1848: select resource_id
1849: into v_wljdi_resource_id_new(l_d)
1850: from wip_operation_resources wor
1851: where wor.wip_entity_id = p_wip_entity_id
1852: and wor.operation_seq_num = l_job_op_seq_num
1853: and wor.resource_seq_num = v_wljdi_resource_seq_num(l_d);
1854: EXCEPTION
2460: begin
2461: l_stmt_num := 70.4;
2462: select resource_seq_num
2463: into l_res_seq_num
2464: from wip_operation_resources wor
2465: where wor.wip_entity_id = p_wip_entity_id
2466: and wor.organization_id = p_org_id
2467: and wor.operation_seq_num = l_job_op_seq_num
2468: and wor.resource_id = l_resource_id
3195: and parent_resource_seq_num IS NULL; -- ST : Detailed Scheduling.. Consider only the runtime resources
3196:
3197: cursor c_wip_rpl_grp is
3198: select resource_id
3199: from wip_operation_resources
3200: where wip_entity_id = p_wip_entity_id
3201: and operation_seq_num = l_job_op_seq_num
3202: and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
3203: and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0)
3268: into l_xst
3269: from dual
3270: where exists (
3271: select resource_id
3272: from wip_operation_resources wor
3273: where wor.wip_entity_id = p_wip_entity_id
3274: and wor.operation_seq_num = l_job_op_seq_num
3275: and wor.resource_id = l_resource_id
3276: and NVL(wor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
3685: l_xst := 0;
3686:
3687: select 1,applied_resource_units
3688: into l_xst,l_app_res_units
3689: from wip_operation_resources wor
3690: where wor.wip_entity_id = p_wip_entity_id
3691: and wor.operation_seq_num = l_job_op_seq_num
3692: and wor.resource_seq_num = l_res_seq_num;
3693:
3959: end;
3960: if l_res_seq_num is null then
3961: select greatest(nvl(max(wor.resource_seq_num),0),nvl(max(wsor.resource_seq_num),0))+10
3962: into l_res_seq_num
3963: from wip_operation_resources wor,
3964: wip_sub_operation_resources wsor
3965: where wor.organization_id = p_org_id
3966: and wor.wip_entity_id = p_wip_entity_id
3967: and wor.operation_seq_num = l_job_op_seq_num
3972: l_xst := 0;
3973: begin
3974: select 1
3975: into l_xst
3976: from wip_operation_resources wor
3977: where wor.organization_id = p_org_id
3978: and wor.wip_entity_id = p_wip_entity_id
3979: and wor.operation_seq_num = l_job_op_seq_num
3980: and wor.resource_seq_num =l_res_seq_num;
4013: select 'Y'
4014: into p_v_res(l_i).reco_flag
4015: from dual
4016: where exists( select 1
4017: from wip_operation_resources wor
4018: where wor.organization_id = p_org_id
4019: and wor.wip_entity_id = p_wip_entity_id
4020: and wor.operation_seq_num = l_job_op_seq_num
4021: and nvl(wor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
4032: select 'N'
4033: into p_v_res(l_i).reco_flag
4034: from dual
4035: where exists( select 1
4036: from wip_operation_resources wor
4037: where wor.organization_id = p_org_id
4038: and wor.wip_entity_id = p_wip_entity_id
4039: and wor.operation_seq_num = l_job_op_seq_num
4040: and nvl(wor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1));
4510:
4511: BEGIN
4512: select resource_seq_num
4513: into l_res_seq_num
4514: from wip_operation_resources WOR
4515: where WOR.wip_entity_id = p_wip_entity_id
4516: and WOR.organization_id = p_org_id
4517: and WOR.operation_seq_num = l_job_op_seq_num
4518: and WOR.parent_resource_seq = p_parent_res_seq_num
4637: into l_res_sdate,
4638: l_res_edate,
4639: l_resource_id, -- this is used for validate instance_id
4640: l_department_id /*Added Department Id for bug 5476647*/
4641: from wip_operation_resources wor, wip_operations wo
4642: where wor.wip_entity_id = p_wip_entity_id
4643: and wor.organization_id = p_org_id
4644: and wor.operation_seq_num = l_job_op_seq_num
4645: and wor.resource_seq_num = l_res_seq_num
4955: ELSE
4956: BEGIN
4957: select resource_seq_num
4958: into l_res_seq_num
4959: from wip_operation_resources WOR
4960: where WOR.wip_entity_id = p_wip_entity_id
4961: and WOR.organization_id = p_org_id
4962: and WOR.operation_seq_num = l_job_op_seq_num
4963: and WOR.parent_resource_seq = p_parent_res_seq_num
5176: select start_date,
5177: completion_date
5178: into l_the_sdate,
5179: l_the_edate
5180: from wip_operation_resources wor
5181: where wip_entity_id = p_wip_entity_id
5182: and organization_id = p_org_id
5183: and operation_seq_num = l_job_op_seq_num
5184: and resource_seq_num = l_res_seq_num;
10393: delete from wip_operations
10394: where wip_entity_id = v_wlji_wip_entity_id(v_idx);
10395: delete from wip_operation_yields
10396: where wip_entity_id = v_wlji_wip_entity_id(v_idx);
10397: delete from wip_operation_resources
10398: where wip_entity_id = v_wlji_wip_entity_id(v_idx);
10399: delete from wip_requirement_operations
10400: where wip_entity_id = v_wlji_wip_entity_id(v_idx);
10401: delete from wip_sub_operation_resources
10992: -- bug 3203505 change order with updating wro
10993: --Bug 5456260:In Option C,resource dates should not be
10994: --updated with the header dates.
10995: /*******************
10996: update wip_operation_resources
10997: set start_date = decode(l_txnexist,
10998: 0, nvl(v_wlji_fusd(v_idx), start_date), -- bug 3394520
10999: start_date),
11000: completion_date = decode(l_txnexist,
12985: l_setup_exists NUMBER := 0;
12986: l_res_costed number := 0;
12987: cursor c_setup_res(c_par_res_seq_num NUMBER) is
12988: select resource_id,resource_seq_num,operation_seq_num,applied_resource_units
12989: from wip_operation_resources wor
12990: where wor.organization_id = p_org_id
12991: and wor.wip_entity_id = p_wip_entity_id
12992: and wor.operation_seq_num = p_op_seq_num
12993: and wor.parent_resource_seq = c_par_res_seq_num;
13062: where wori.organization_id = p_org_id
13063: and wori.wip_entity_id = p_wip_entity_id
13064: and wori.operation_seq_num = p_op_seq_num
13065: and wori.RESOURCE_SEQ_NUM in (select resource_seq_num
13066: from wip_operation_resources wor
13067: where wor.organization_id = p_org_id
13068: and wor.wip_entity_id = p_wip_entity_id
13069: and wor.operation_seq_num = p_op_seq_num
13070: and wor.parent_resource_seq = p_par_res_seq_num
13077: where woru.organization_id = p_org_id
13078: and woru.wip_entity_id = p_wip_entity_id
13079: and woru.operation_seq_num = p_op_seq_num
13080: and woru.resource_seq_num in (select resource_seq_num
13081: from wip_operation_resources wor
13082: where wor.organization_id = p_org_id
13083: and wor.wip_entity_id = p_wip_entity_id
13084: and wor.operation_seq_num = p_op_seq_num
13085: and wor.parent_resource_seq = p_par_res_seq_num
13087:
13088: l_stmt_num := 70;
13089:
13090: /* delete the setup resources.... */
13091: delete from wip_operation_resources wor
13092: where wor.organization_id = p_org_id
13093: and wor.wip_entity_id = p_wip_entity_id
13094: and wor.operation_seq_num = p_op_seq_num
13095: and wor.parent_resource_seq = p_par_res_seq_num;
13242: e_res_not_batchable exception;
13243:
13244: cursor c_setup_res(c_par_res_seq_num NUMBER) is
13245: select resource_id,resource_seq_num,operation_seq_num
13246: from wip_operation_resources wor
13247: where wor.organization_id = p_org_id
13248: and wor.wip_entity_id = p_wip_entity_id
13249: and wor.operation_seq_num = p_job_op_seq_num
13250: and wor.parent_resource_seq = c_par_res_seq_num;
13257: begin
13258:
13259: select max(resource_seq_num)
13260: into l_res_num1
13261: from wip_operation_resources wor
13262: where wor.organization_id = p_org_id
13263: and wor.wip_entity_id = p_wip_entity_id
13264: and wor.operation_seq_num = p_job_op_seq_num;
13265:
13273:
13274: /* Indicates that there is no parent resource being added... ..... */
13275: /*
13276: Validations to be done...
13277: i) check if any parent resource exists ( consider only WIP_OPERATION_RESOURCES )
13278: ii) Check if any setup resources already exist....
13279: iii) Delete the setup resources...( if no cost incurred )
13280: */
13281:
13285: l_exists := 0;
13286:
13287: select 1
13288: into l_exists
13289: from wip_operation_resources wor
13290: where wor.organization_id = p_org_id
13291: and wor.wip_entity_id = p_wip_entity_id
13292: and wor.operation_seq_num = p_job_op_seq_num
13293: and resource_seq_num = l_par_res_seq_num;
13303: l_setup_exists := 0;
13304:
13305: select 1
13306: into l_setup_exists
13307: from wip_operation_resources wor
13308: where wor.organization_id = p_org_id
13309: and wor.wip_entity_id = p_wip_entity_id
13310: and wor.operation_seq_num = p_job_op_seq_num
13311: and wor.parent_resource_seq = l_par_res_seq_num
13349: end if;
13350: l_stmt_num := 70;
13351:
13352: /* delete the setup resources.... */
13353: delete from wip_operation_resources wor
13354: where wor.organization_id = p_org_id
13355: and wor.wip_entity_id = p_wip_entity_id
13356: and wor.operation_seq_num = p_job_op_seq_num
13357: and wor.parent_resource_seq = l_par_res_seq_num;
13632: /* check in WOR... */
13633: begin
13634: select 1
13635: into l_dummy
13636: from wip_operation_resources wor
13637: where wor.organization_id = p_org_id
13638: and wor.wip_entity_id = p_wip_entity_id
13639: and wor.operation_seq_num = p_job_op_seq_num
13640: and wor.resource_seq_num = p_new_res_tbl(l_index).res_seq_num;
14235:
14236: forall i in l_setup_index_tbl.first..l_setup_index_tbl.last
14237:
14238: /* issue the insert statement */
14239: insert into wip_operation_resources
14240: (
14241: wip_entity_id,
14242: operation_seq_num,
14243: resource_seq_num,
17178: if p_error_code <> 0 then
17179: raise e_wljdi_common_error;
17180: end if;
17181:
17182: delete from wip_operation_resources wor
17183: where wor.wip_entity_id = p_wip_entity_id
17184: and wor.operation_seq_num = l_job_op_seq_num
17185: and wor.resource_seq_num = l_resource_seq_num
17186: and wor.organization_id = p_org_id;
17343: end if;
17344:
17345: IF l_v_res(l_resource_id).reco_flag = 'Y' then
17346:
17347: insert into wip_operation_resources
17348: (wip_entity_id,
17349: operation_seq_num,
17350: resource_seq_num,
17351: organization_id,
18008: begin
18009: l_stmt_num := 250.3;
18010: select 0
18011: into l_swap
18012: from wip_operation_resources
18013: where wip_entity_id = p_wip_entity_id
18014: and operation_seq_num = l_job_op_seq_num
18015: and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
18016: and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0);
18072: l_stmt_num := 250.6;
18073: while(l_resource_id IS NOT NULL) -- bug 3736602 added loop
18074: loop
18075:
18076: UPDATE WIP_OPERATION_RESOURCES
18077: set start_date = NVL(l_v_res(l_resource_id).start_date,
18078: start_date),
18079: completion_date = NVL(l_v_res(l_resource_id).completion_date,
18080: completion_date),
20321: )
20322: /* ST bug fix 3493984 : end */
20323: or exists (
20324: select 'x'
20325: from wip_operation_resources
20326: where organization_id = p_organization_id
20327: and wip_entity_id = p_wip_entity_id
20328: and applied_resource_units <> 0));
20329:
20377: where organization_id = p_organization_id
20378: and wip_entity_id = p_wip_entity_id)
20379: or exists (
20380: select 'x'
20381: from wip_operation_resources
20382: where organization_id = p_organization_id
20383: and wip_entity_id = p_wip_entity_id
20384: and applied_resource_units <> 0));
20385: BEGIN