[Home] [Help]
35: /* Fixed bug 5500805. We should not copy parent_seq_num to resource_seq_num but
36: we need to derive it from the parent resource of this setup resource.
37: This query works for both res_usage and res_instance_usage */
38: select resource_seq_num into l_res_seq
39: from wip_operation_resources
40: where organization_id = p_organization_id
41: and wip_entity_id = p_wip_entity_id
42: and operation_seq_num = cur_row.operation_seq_num
43: and resource_id = cur_row.resource_id_new
183: and resource_seq_num = wjdi.resource_seq_num
184: and instance_id = wjdi.resource_instance_id))
185: or (load_type = wip_job_details.wip_res_usage
186: and not exists (select 1
187: from wip_operation_resources
188: where wip_entity_id = wjdi.wip_entity_id
189: and organization_id = wjdi.organization_id
190: and operation_seq_num = wjdi.operation_seq_num
191: and resource_seq_num = wjdi.resource_seq_num)));
225: and resource_seq_num = wjdi.resource_seq_num
226: and instance_id = wjdi.resource_instance_id))
227: or (load_type = wip_job_details.wip_res_usage
228: and not exists (select 1
229: from wip_operation_resources
230: where wip_entity_id = wjdi.wip_entity_id
231: and organization_id = wjdi.organization_id
232: and operation_seq_num = wjdi.operation_seq_num
233: and resource_seq_num = wjdi.resource_seq_num)));
254: -- 1.Start date of usage is greater than or equal to start date of the corresponding resource or instance.
255: -- 2.Start and end dates of usage is in between start and end dates of corresponding resource or instance.
256: or start_date > completion_date
257: or exists (select 1
258: from wip_operation_resources wor,wip_op_resource_instances wori
259: where wor.wip_entity_id = p_wip_entity_id
260: and wor.operation_seq_num = wjdi.operation_seq_num
261: and wor.resource_seq_num = wjdi.resource_seq_num
262: and wor.wip_entity_id = wori.wip_entity_id(+)
303: -- 1.Start date of usage is greater than or equal to start date of the corresponding resource or instance.
304: -- 2.Start and end dates of usage is in between start and end dates of corresponding resource or instance.
305: or start_date > completion_date
306: or exists (select 1
307: from wip_operation_resources wor,wip_op_resource_instances wori
308: where wor.wip_entity_id = p_wip_entity_id
309: and wor.operation_seq_num = wjdi.operation_seq_num
310: and wor.resource_seq_num = wjdi.resource_seq_num
311: and wor.wip_entity_id = wori.wip_entity_id(+)
517: and organization_id = p_organization_id
518: and load_type = wip_job_details.wip_res_usage
519: and substitution_type = wip_job_details.wip_add
520: and ( not exists (select 1
521: from wip_job_dtls_interface wjdi2, wip_operation_resources wor
522: where wjdi2.group_id = wjdi.group_id
523: and wjdi2.wip_entity_id = p_wip_entity_id
524: and wjdi2.organization_id = p_organization_id
525: and wjdi2.operation_seq_num = wjdi.operation_seq_num
531: and wor.operation_seq_num = wjdi2.operation_seq_num
532: and wor.resource_seq_num = wjdi2.resource_seq_num
533: and wor.start_date = wjdi2.start_date)
534: or not exists (select 1
535: from wip_job_dtls_interface wjdi2, wip_operation_resources wor
536: where wjdi2.group_id = wjdi.group_id
537: and wjdi2.wip_entity_id = p_wip_entity_id
538: and wjdi2.organization_id = p_organization_id
539: and wjdi2.operation_seq_num = wjdi.operation_seq_num
573: and organization_id = p_organization_id
574: and load_type = wip_job_details.wip_res_usage
575: and substitution_type = wip_job_details.wip_add
576: and ( not exists (select 1
577: from wip_job_dtls_interface wjdi2, wip_operation_resources wor
578: where wjdi2.group_id = wjdi.group_id
579: and wjdi2.wip_entity_id = p_wip_entity_id
580: and wjdi2.organization_id = p_organization_id
581: and wjdi2.operation_seq_num = wjdi.operation_seq_num
587: and wor.operation_seq_num = wjdi2.operation_seq_num
588: and wor.resource_seq_num = wjdi2.resource_seq_num
589: and wor.start_date = wjdi2.start_date)
590: or not exists (select 1
591: from wip_job_dtls_interface wjdi2, wip_operation_resources wor
592: where wjdi2.group_id = wjdi.group_id
593: and wjdi2.wip_entity_id = p_wip_entity_id
594: and wjdi2.organization_id = p_organization_id
595: and wjdi2.operation_seq_num = wjdi.operation_seq_num