ECC Discrete Manufacturing, Resources
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds information about resources.
Dataset Key: wip-ospdetails2
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Description: This data set holds information about resources.
Dataset Key: wip-ospdetails2
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select x.* from ( SELECT * FROM ( WITH lookup_details as (SELECT /*+ materialize */ meaning, lookup_code, language, lookup_type, security_group_id, view_application_id FROM fnd_lookup_values WHERE lookup_type IN ('WIP_JOB_STATUS','WIP_OPERATION_STATUS','WIP_PO_NOT_CREATED','SYS_YES_NO','BOM_RESOURCE_SCHEDULE_TYPE','BOM_RESOURCE_TYPE','CST_BASIS','BOM_AUTOCHARGE_TYPE') AND view_application_id = 700 AND security_group_id = 0 ) SELECT Ecc_Spec_Id, ecc_op_spec_id, Ecc_rsc_Spec_Id, job, job_name , job_scheduled_start_date, job_scheduled_completion_date, job_actual_completion_date, job_status, assembly , assembly_description, organization_id , organization_code , PO_RCV_LINK , organization_name , operation_code, projected_available_quantity, Standard_rate, shortage_quantity, replacement_group_num, substitute_group_num, activity, operation_sequence_number, department_description , planned_start_date , Planned_completion_date , actual_start_date , actual_completion_date , quantity_in_queue , quantity_in_run , quantity_in_queue+quantity_in_run inprogress_quantity, Quantity_in_to_move , Scrap_quantity , Rejected_Quantity , Completed_quantity , Operation_Description , Operation_progress , Department, resource_code , resource_description , resource_usage_rate , resource_uom , resource_required_units , resource_applied_units , clock_in_employee, language, resource_type , resource_basis_type, resource_open_qty , resource_assigned_units , resource_start_date , resource_completion_date , resource_start_date_t , resource_completion_date_t , resource_alternate , resource_sequence_number , scheduled_sequence_number , Rsr_actual_start_date , Rsr_actual_completion_date , auto_charge_flag , phantom_flag , operation_resource_batch , scheduled_flag , charge_type , utilization , efficiency , supplier , supplier_tbl , purchase_order_status , purchase_order_number , release_number , requisition_number , line_number , cancel_flag , need_by_date , promise_date , delayed_receipts, pending_quantity , quantity_ordered, quantity_delivered, quantity_cancelled, po_line_uom , line_item, osp_operation, starting_today, Completing_Today, starting_tomorrow, Completing_Tomorrow, starting_by, finishing_by, CASE WHEN job_status_type IN (3,6) THEN operation_start_delay ELSE 0 END operation_start_delay, CASE WHEN job_status_type IN (3,6) THEN operation_completion_delay ELSE 0 END operation_completion_delay, CASE WHEN job_status_type IN (3,6) THEN operation_delay_duration ELSE 0 END operation_delay_duration, CASE WHEN job_status_type IN (3,6) THEN operation_delayed ELSE 0 END operation_delayed, operation_status, op_status_flag OPERATION_STATUS_CODE, op_status_flag, CASE WHEN op_status_flag =1 AND job_status_type = 3 THEN 1 ELSE 0 END upcoming, CASE WHEN op_status_flag =1 AND job_status_type = 3 AND operation_start_delay > 0 THEN 1 ELSE 0 END Start_delay_flag, CASE WHEN op_status_flag =1 AND job_status_type = 3 AND operation_start_delay > 0 THEN 'YES' ELSE 'NO' END Start_delay, CASE WHEN (op_status_flag IN (2,3)) AND job_status_type = 3 AND operation_completion_delay > 0 THEN 1 ELSE 0 END completion_delay_flag, CASE WHEN (op_status_flag IN (2,3)) AND job_status_type = 3 AND operation_completion_delay > 0 THEN 1 ELSE 0 END Completion_delay, CASE WHEN op_status_flag = 2 THEN 'YES' ELSE 'NO' END Open, CASE WHEN op_status_flag =1 AND job_status_type = 3 THEN 'YES' ELSE 'NO' END upcoming_code, CASE WHEN delayed_receipts =1 THEN 'YES' ELSE 'NO' END po_receipt_delay, CASE WHEN job_status_type IN (3,6) AND operation_delay_duration > 0 THEN 0 WHEN job_status_type NOT IN (3,6) THEN 0 ELSE 1 END ontrack_flag, CASE WHEN job_status_type IN (3,6) AND operation_delay_duration > 0 THEN no_flag WHEN job_status_type NOT IN (3,6) THEN no_flag ELSE yes_flag END ontrack, buyer_name,autocharge_type FROM ( SELECT TO_CHAR (wdj.wip_entity_id ||'-' ||wo.operation_seq_num ||'-' ||br.resource_code ||'-' ||wor.replacement_group_num ||'-' ||wdj.organization_id ||'-' ||wip_osp.rec_key ||'-OSP') Ecc_Spec_Id, TO_CHAR (wdj.wip_entity_id ||'-' ||wo.operation_seq_num ||'-' ||wdj.organization_id ||'-OP') ecc_op_spec_id, (wdj.wip_entity_id ||'-' ||wo.operation_seq_num ||'-' ||br.resource_code ||'-' ||wdj.organization_id ||'-RSC') Ecc_rsc_Spec_Id, wo.wip_entity_id job, we.wip_entity_name job_name , status_lkp.meaning job_status, wor.replacement_group_num, wdj.scheduled_start_date job_scheduled_start_date , wdj.scheduled_completion_date job_scheduled_completion_date , wdj.date_completed job_actual_completion_date , WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly, WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) assembly_description, wdj.organization_id organization_id , wdj.status_type job_status_type, mp.organization_code organization_code , 'fwkhp_sswafunc.gif' as PO_RCV_LINK, yeslk.meaning yes_flag, nolk.meaning no_flag, haout.name organization_name , bso.operation_code, 0 projected_available_quantity, to_char(decode(wor.standard_rate_flag,1,yeslk.meaning,nolk.meaning)) Standard_rate, 0 shortage_quantity, wor.substitute_group_num substitute_group_num, ca.activity activity, wo.operation_seq_num operation_sequence_number, bd.description department_description , wo.first_unit_start_date planned_start_date , wo.last_unit_completion_date Planned_completion_date , wo.actual_start_date actual_start_date , wo.actual_completion_date actual_completion_date , NVL(wo.quantity_in_queue,0) quantity_in_queue , NVL(wo.quantity_running,0) quantity_in_run , NVL(wo.quantity_waiting_to_move,0) Quantity_in_to_move , to_number(NVL(wo.quantity_scrapped,0)) Scrap_quantity , to_number(NVL(wo.quantity_rejected,0)) Rejected_Quantity , NVL(wo.quantity_completed,0) Completed_quantity , wo.description Operation_Description , wo.progress_percentage Operation_progress , bd.department_code Department, br.resource_code resource_code , br.description resource_description , wor.usage_rate_or_amount resource_usage_rate , wor.uom_code resource_uom , ROUND(wor.usage_rate_or_amount*DECODE(wor.basis_type, 1,wdj.start_quantity-NVL(wo.cumulative_scrap_quantity,0),1),6) resource_required_units , wor.applied_resource_units resource_applied_units , (SELECT pap.full_name FROM per_all_people_f pap WHERE pap.person_id = wo.employee_id AND ROWNUM = 1 ) clock_in_employee, restype.language language, restype.meaning resource_type , resbasis.meaning resource_basis_type, ROUND((wor.usage_rate_or_amount *DECODE(wor.basis_type,1, wdj.start_quantity-NVL(wo.cumulative_scrap_quantity,0),1))-wor.applied_resource_units,6) resource_open_qty , ROUND(wor.assigned_units,6) resource_assigned_units , wor.start_date resource_start_date , wor.completion_date resource_completion_date , wor.start_date resource_start_date_t , wor.completion_date resource_completion_date_t , null resource_alternate , wor.resource_seq_num resource_sequence_number , wor.schedule_seq_num scheduled_sequence_number , wor.actual_start_date Rsr_actual_start_date , wor.actual_completion_date Rsr_actual_completion_date , TO_CHAR(DECODE(wor.autocharge_type,1,yeslk.meaning,nolk.meaning)) auto_charge_flag , TO_CHAR(DECODE(wor.phantom_flag,1,yeslk.meaning,nolk.meaning)) phantom_flag , wor.batch_id operation_resource_batch , ressch.meaning scheduled_flag , reschg.meaning charge_type , decode(bdr.utilization, NULL, 100, bdr.utilization * 100) utilization , decode(bdr.efficiency, NULL, 100, bdr.efficiency * 100) efficiency , NVL(wip_osp.vendor_name,nonelk.meaning) supplier , wip_osp.vendor_name supplier_tbl , wip_osp.approval_status_code purchase_order_status , wip_osp.po_number purchase_order_number , wip_osp.release_num release_number , wip_osp.req_number requisition_number , wip_osp.line_number line_number , DECODE(wip_osp.cancel_flag,'Y',1,2) cancel_flag , wip_osp.old_need_by_date need_by_date , wip_osp.old_promised_date promise_date , CASE WHEN wip_osp.po_number IS NOT NULL THEN CASE WHEN (NVL(wip_osp.old_promised_date,wip_osp.old_need_by_date) - SYSDATE) < 0 AND (NVL(wip_osp.old_promised_date,wip_osp.old_need_by_date) - SYSDATE) < 0 AND ((wip_osp.CANCEL_FLAG_PL IS NULL OR wip_osp.CANCEL_FLAG_PL = 'N') AND (wip_osp.QUANTITY_RECEIVED <(wip_osp.QUANTITY-wip_osp.QUANTITY_CANCELLED_PL)) AND NVL(wip_osp.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED') THEN 1 ELSE 0 END ELSE 0 END delayed_receipts, wip_osp.assembly_qty_pending pending_quantity , wip_osp.quantity_ordered, wip_osp.quantity_delivered, wip_osp.quantity_cancelled, wip_osp.assembly_primary_uom po_line_uom , wip_osp.osp_res_po_line_item line_item, wip_osp.buyer_name, CASE WHEN wip_osp.po_number IS NULL AND wip_osp.req_number IS NULL THEN nolk.meaning ELSE yeslk.meaning END osp_operation, CASE WHEN wip_osp.po_number IS NULL AND wip_osp.req_number IS NULL THEN nolk.lookup_code ELSE yeslk.lookup_code END osp_operation_flag, CASE WHEN TRUNC(wo.first_unit_start_date) = TRUNC(SYSDATE) THEN 1 ELSE 0 END starting_today, CASE WHEN TRUNC(wo.last_unit_completion_date) = TRUNC(SYSDATE) THEN 1 ELSE 0 END Completing_Today, CASE WHEN TRUNC(wo.first_unit_start_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END starting_tomorrow, CASE WHEN TRUNC(wo.last_unit_completion_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END Completing_Tomorrow, (wo.first_unit_start_date -SYSDATE) starting_by, (wo.last_unit_completion_date - SYSDATE ) finishing_by, CASE WHEN wo.actual_start_date IS NOT NULL AND wo.actual_start_date > wo.first_unit_start_date THEN CASE WHEN (wo.actual_start_date-wo.first_unit_start_date) > 0 THEN (wo.actual_start_date-wo.first_unit_start_date) ELSE 0 END WHEN wo.actual_start_date IS NULL AND (wo.first_unit_start_date) < sysdate AND NVL(wo.quantity_in_queue,0) = 0 AND NVL(wo.quantity_running,0) = 0 AND NVL(wo.quantity_completed,0) = 0 THEN CASE WHEN (sysdate-wo.first_unit_start_date) > 0 THEN (sysdate-wo.first_unit_start_date) ELSE 0 END ELSE 0 END operation_start_delay, CASE WHEN wo.actual_completion_date IS NOT NULL AND wo.actual_completion_date > (wo.last_unit_completion_date) THEN CASE WHEN (wo.actual_completion_date -wo.last_unit_completion_date) > 0 THEN (wo.actual_completion_date -wo.last_unit_completion_date) ELSE 0 END WHEN wo.actual_completion_date IS NULL AND (wo.last_unit_completion_date) < sysdate AND (wo.quantity_in_queue > 0 OR wo.quantity_running > 0) THEN CASE WHEN (sysdate-wo.last_unit_completion_date) > 0 THEN (sysdate-wo.last_unit_completion_date) ELSE 0 END ELSE 0 END operation_completion_delay, CASE WHEN wo.actual_completion_date IS NOT NULL AND wo.actual_completion_date > (wo.last_unit_completion_date ) THEN CASE WHEN (wo.actual_completion_date - (wo.last_unit_completion_date )) > 0 THEN (wo.actual_completion_date - (wo.last_unit_completion_date )) ELSE 0 END WHEN wo.actual_completion_date IS NULL AND (wo.last_unit_completion_date ) < sysdate AND (wo.quantity_in_queue > 0 OR wo.quantity_running > 0) THEN CASE WHEN (sysdate-(wo.last_unit_completion_date )) > 0 THEN (sysdate-(wo.last_unit_completion_date )) ELSE 0 END WHEN wo.actual_start_date IS NOT NULL AND wo.actual_start_date > (wo.first_unit_start_date ) THEN CASE WHEN (wo.actual_start_date-(wo.first_unit_start_date )) > 0 THEN (wo.actual_start_date-(wo.first_unit_start_date )) ELSE 0 END WHEN wo.actual_start_date IS NULL AND (wo.first_unit_start_date ) < sysdate AND NVL(wo.quantity_in_queue,0) = 0 AND NVL(wo.quantity_running,0) = 0 AND NVL(wo.quantity_completed,0) = 0 THEN CASE WHEN (sysdate-(wo.first_unit_start_date )) > 0 THEN (sysdate-(wo.first_unit_start_date )) ELSE 0 END ELSE 0 END operation_delay_duration, CASE WHEN wo.actual_completion_date IS NOT NULL AND wo.actual_completion_date > (wo.last_unit_completion_date ) THEN CASE WHEN (wo.actual_completion_date - (wo.last_unit_completion_date )) > 0 THEN 1 ELSE 0 END WHEN wo.actual_completion_date IS NULL AND (wo.last_unit_completion_date ) < sysdate AND (wo.quantity_in_queue > 0 OR wo.quantity_running > 0) THEN CASE WHEN (sysdate-(wo.last_unit_completion_date )) > 0 THEN 1 ELSE 0 END WHEN wo.actual_start_date IS NOT NULL AND wo.actual_start_date > (wo.first_unit_start_date ) THEN CASE WHEN (wo.actual_start_date-(wo.first_unit_start_date )) > 0 THEN 1 ELSE 0 END WHEN wo.actual_start_date IS NULL AND (wo.first_unit_start_date ) < sysdate AND NVL(wo.quantity_in_queue,0) = 0 AND NVL(wo.quantity_running,0) = 0 AND NVL(wo.quantity_completed,0) = 0 THEN CASE WHEN (sysdate-(wo.first_unit_start_date )) > 0 THEN 1 ELSE 0 END ELSE 0 END operation_delayed, CASE WHEN wo.quantity_completed >= (wdj.start_quantity-wo.cumulative_scrap_quantity) OR wdj.status_type in (4,5,7,12) THEN operation_com_lkp.meaning WHEN ((Wo.quantity_in_queue + wo.quantity_running > 0) OR (wo.date_last_moved is not null) OR ( wo.quantity_completed > 0 ) ) AND wdj.status_type in (3,6) THEN operation_run_lkp.meaning ELSE operation_ns_lkp.meaning END operation_status, CASE WHEN wo.quantity_completed >= (wdj.start_quantity-wo.cumulative_scrap_quantity) OR wdj.status_type in (4,5,7,12) THEN operation_com_lkp.lookup_code WHEN ( ( wo.quantity_in_queue + wo.quantity_running > 0 ) OR ( wo.date_last_moved IS NOT NULL ) OR ( wo.quantity_completed > 0 ) ) AND wdj.status_type IN (3,6) THEN operation_run_lkp.lookup_code ELSE operation_ns_lkp.lookup_code END op_status_flag, wor.autocharge_type FROM wip_entities we, wip_discrete_jobs wdj, bom_resources br, wip_operations wo, wip_operation_resources wor, bom_department_resources bdr, cst_activities ca, mtl_parameters mp, hr_all_organization_units_tl haout, ( SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_JOB_STATUS' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 ) status_lkp, (SELECT restype.meaning, restype.language, restype.lookup_code FROM lookup_details restype WHERE restype.lookup_type = 'BOM_RESOURCE_TYPE' AND restype.view_application_id = 700 AND restype.security_group_id = 0 )restype, (SELECT resbasis.meaning, resbasis.language, resbasis.lookup_code FROM lookup_details resbasis WHERE resbasis.lookup_type = 'CST_BASIS' AND resbasis.view_application_id = 700 AND resbasis.security_group_id = 0 )resbasis, (SELECT reschg.meaning, reschg.language , reschg.lookup_code FROM lookup_details reschg WHERE reschg.lookup_type = 'BOM_AUTOCHARGE_TYPE' AND reschg.view_application_id = 700 AND reschg.security_group_id = 0 )reschg, (SELECT ressch.meaning, ressch.language, ressch.lookup_code FROM lookup_details ressch WHERE ressch.lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE' AND ressch.view_application_id = 700 AND ressch.security_group_id = 0 )ressch, (SELECT f.meaning, f.lookup_code, f.language FROM lookup_details f WHERE f.lookup_type = 'WIP_OPERATION_STATUS' AND f.view_application_id = 700 AND f.security_group_id = 0 AND f.lookup_code = 1 ) operation_ns_lkp, (SELECT f.meaning, f.lookup_code, f.language FROM lookup_details f WHERE f.lookup_type = 'WIP_OPERATION_STATUS' AND f.view_application_id = 700 AND f.security_group_id = 0 AND f.lookup_code = 2 ) operation_run_lkp, (SELECT f.meaning, f.lookup_code, f.language FROM lookup_details f WHERE f.lookup_type = 'WIP_OPERATION_STATUS' AND f.view_application_id = 700 AND f.security_group_id = 0 AND f.lookup_code = 3 ) operation_com_lkp, (SELECT wip_osp1.rec_key, wip_osp1.approval_status_code, wip_osp1.po_number, wip_osp1.release_num, wip_osp1.req_number, wip_osp1.cancel_flag, wip_osp1.old_need_by_date, wip_osp1.old_promised_date, wip_osp1.CANCEL_FLAG_PL, wip_osp1.QUANTITY_RECEIVED, wip_osp1.QUANTITY, wip_osp1.QUANTITY_CANCELLED_PL, wip_osp1.CLOSED_CODE, wip_osp1.assembly_qty_pending, wip_osp1.quantity_ordered, wip_osp1.quantity_delivered, wip_osp1.quantity_cancelled, wip_osp1.assembly_primary_uom, wip_osp1.organization_id, wip_osp1.wip_entity_id, wip_osp1.operation_seq_num, wip_osp1.vendor_name, wip_osp1.po_item_number osp_res_po_line_item, wip_osp1.line_number, wip_osp1.resource_seq_num, wip_osp1.buyer_name FROM (SELECT /*+ leading ( pd pl ) index ( pd PO_DISTRIBUTIONS_N8 ) use(pd pl) */ ph.segment1 po_number, NULL release_num, ph.type_lookup_code po_req_type, ph.authorization_status approval_status_code, ps.need_by_date old_need_by_date, ps.promised_date old_promised_date, ps.CANCEL_FLAG CANCEL_FLAG_PL, ps.QUANTITY_RECEIVED QUANTITY_RECEIVED, ps.QUANTITY QUANTITY, ps.QUANTITY_CANCELLED QUANTITY_CANCELLED_PL, ps.CLOSED_CODE CLOSED_CODE, wip_osp.converttoprimarymoveqty(pl.item_id, pd.destination_organization_id, greatest(pd.quantity_ordered - NVL(pd.quantity_delivered, 0) - NVL(pd.quantity_cancelled, 0), 0), pl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id), (SELECT wor.usage_rate_or_amount FROM wip_operation_resources wor WHERE wor.wip_entity_id = pd.wip_entity_id AND NVL(wor.repetitive_schedule_id, -1) = NVL(pd.wip_repetitive_schedule_id, -1) AND wor.operation_seq_num = pd.wip_operation_seq_num AND wor.organization_id = pd.destination_organization_id AND wor.resource_seq_num = pd.wip_resource_seq_num )) assembly_qty_pending, pd.quantity_ordered, pd.quantity_delivered, pd.quantity_cancelled, pd.po_header_id ||'-'||pd.po_line_id ||'-'||pd.po_distribution_id rec_key, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id) assembly_primary_uom, pd.wip_entity_id wip_entity_id, pd.destination_organization_id organization_id, pd.wip_operation_seq_num operation_seq_num, pd.wip_resource_seq_num resource_seq_num, NULL req_number, ps.cancel_flag cancel_flag, pl.line_num line_number, po_v.vendor_name vendor_name, WIP_ECC_UTIL_PVT.get_concatenated_segments(pd.destination_organization_id,pl.item_id ) po_item_number, poag.agent_name buyer_name FROM wip_entities we, wip_discrete_jobs wdj, po_distributions_all pd, po_line_locations_all ps, po_lines_all pl, po_headers_all ph, po_document_types_all_b pdt, ap_suppliers po_v, po_agents_v poag WHERE pdt.document_type_code = 'PO' AND ph.type_lookup_code = 'STANDARD' AND ph.type_lookup_code = pdt.document_subtype AND ph.org_id = pdt.org_id AND pd.po_header_id = ph.po_header_id AND ph.agent_id = poag.agent_id AND pd.po_line_id = pl.po_line_id AND pd.line_location_id = ps.line_location_id AND pd.wip_entity_id = we.wip_entity_id AND we.entity_type IN (1,3) AND we.wip_entity_id = wdj.wip_entity_id AND we.organization_id = wdj.organization_id AND wdj.status_type IN (1,3,4,5,6,7,12,14,15) AND ph.vendor_id = po_v.vendor_id(+) UNION ALL SELECT /*+ leading ( pd pl ) index ( pd PO_DISTRIBUTIONS_N8 ) use_nl(pd pl) */ ph.segment1, pr.release_num, ph.type_lookup_code, pr.authorization_status, ps.need_by_date, ps.promised_date, ps.CANCEL_FLAG CANCLE_FLAG_PL, ps.QUANTITY_RECEIVED QUANTITY_RECEIVED, ps.QUANTITY QUANTITY, ps.QUANTITY_CANCELLED QUANTITY_CANCELLED_PL, ps.CLOSED_CODE CLOSED_CODE, wip_osp.converttoprimarymoveqty(pl.item_id, pd.destination_organization_id, greatest(pd.quantity_ordered - NVL(pd.quantity_delivered, 0) - NVL(pd.quantity_cancelled, 0),0), pl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id), (SELECT wor.usage_rate_or_amount FROM wip_operation_resources wor WHERE wor.wip_entity_id = pd.wip_entity_id AND NVL(wor.repetitive_schedule_id, -1) = NVL(pd.wip_repetitive_schedule_id, -1) AND wor.operation_seq_num = pd.wip_operation_seq_num AND wor.organization_id = pd.destination_organization_id AND wor.resource_seq_num = pd.wip_resource_seq_num )), pd.quantity_ordered, pd.quantity_delivered, pd.quantity_cancelled, pd.po_header_id ||'-'||pd.po_line_id|| '-'||pr.po_release_id ||'-'||pd.po_distribution_id rec_key, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id), pd.wip_entity_id, pd.destination_organization_id, pd.wip_operation_seq_num, pd.wip_resource_seq_num, NULL, ps.cancel_flag, pl.line_num, po_v.vendor_name, WIP_ECC_UTIL_PVT.get_concatenated_segments(pd.destination_organization_id,pl.item_id ), poag.agent_name buyer_name FROM wip_entities we, wip_discrete_jobs wdj, po_distributions_all pd, po_line_locations_all ps, po_lines_all pl, po_document_types_all_b pdt, po_headers_all ph, po_releases_all pr, ap_suppliers po_v, po_agents_v poag WHERE pdt.document_type_code = 'RELEASE' AND ph.type_lookup_code = 'BLANKET' AND pr.release_type = pdt.document_subtype AND pr.org_id = pdt.org_id AND pr.po_release_id = ps.po_release_id AND pr.po_header_id = ph.po_header_id AND pd.po_header_id = ph.po_header_id AND ph.agent_id = poag.agent_id AND pd.po_line_id = pl.po_line_id AND pd.line_location_id = ps.line_location_id AND pd.wip_entity_id = we.wip_entity_id AND we.entity_type IN (1,3) AND we.wip_entity_id = wdj.wip_entity_id AND we.organization_id = wdj.organization_id AND wdj.status_type IN (1,3,4,5,6,7,12,14,15) AND ph.vendor_id = po_v.vendor_id(+) UNION ALL SELECT /*+ leading (prl) index (prl PO_REQUISITION_LINES_N9) */ NULL, NULL, 'REQUISITION', prh.authorization_status, prl.need_by_date, to_date(NULL), prl.cancel_flag CANCLE_FLAG_PL, 0 QUANTITY_RECEIVED, prl.quantity QUANTITY, 0 QUANTITY_CANCELLED_PL, NULL CLOSED_CODE, wip_osp.converttoprimarymoveqty(prl.item_id, prl.destination_organization_id, prl.quantity, prl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id), (SELECT wor.usage_rate_or_amount FROM wip_operation_resources wor WHERE wor.wip_entity_id = prl.wip_entity_id AND NVL(wor.repetitive_schedule_id, -1) = NVL(prl.wip_repetitive_schedule_id, -1) AND wor.operation_seq_num = prl.wip_operation_seq_num AND wor.organization_id = prl.destination_organization_id AND wor.resource_seq_num = prl.wip_resource_seq_num )), prl.quantity quantity_ordered, 0 quantity_delivered, 0 quantity_cancelled, prl.requisition_header_id ||'-'||prl.requisition_line_id rec_key, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id), prl.wip_entity_id, prl.destination_organization_id, prl.wip_operation_seq_num, prl.wip_resource_seq_num, prh.segment1, prl.cancel_flag , prl.line_num, NVL(po_v.vendor_name,prl.suggested_vendor_name), WIP_ECC_UTIL_PVT.get_concatenated_segments(prl.destination_organization_id,prl.item_id ), NULL FROM wip_entities we, wip_discrete_jobs wdj, po_requisition_headers_all prh, po_requisition_lines_all prl, ap_suppliers po_v WHERE NOT EXISTS (SELECT 1 FROM po_line_locations_all pll WHERE prl.line_location_id = pll.line_location_id ) AND prl.requisition_header_id = prh.requisition_header_id AND prl.wip_entity_id = we.wip_entity_id AND we.entity_type IN (1,3) AND we.wip_entity_id = wdj.wip_entity_id AND we.organization_id = wdj.organization_id AND wdj.status_type IN (1,3,4,5,6,7,12,14,15) AND prl.vendor_id = po_v.vendor_id(+) ) wip_osp1 )wip_osp, bom_departments bd, bom_standard_operations bso, (SELECT nonelk.meaning, nonelk.lookup_code, nonelk.language FROM fnd_lookup_values nonelk WHERE nonelk.lookup_type = 'WIP_PO_NOT_CREATED' AND nonelk.view_application_id = 700 AND nonelk.lookup_code = '1' AND nonelk.security_group_id = 0 ) nonelk, (SELECT nolk.meaning, nolk.lookup_code, nolk.language FROM fnd_lookup_values nolk WHERE nolk.lookup_type = 'SYS_YES_NO' AND nolk.view_application_id = 700 AND nolk.lookup_code = '2' AND nolk.security_group_id = 0 ) nolk, (SELECT yeslk.meaning, yeslk.lookup_code, yeslk.language FROM fnd_lookup_values yeslk WHERE yeslk.lookup_type = 'SYS_YES_NO' AND yeslk.view_application_id = 700 AND yeslk.lookup_code = '1' AND yeslk.security_group_id = 0 ) yeslk WHERE we.wip_entity_id = wdj.wip_entity_id AND wdj.wip_entity_id = wo.wip_entity_id AND bd.department_id = wo.department_id AND mp.organization_id = wdj.organization_id AND haout.organization_id = mp.organization_id AND wo.standard_operation_id = bso.standard_operation_id(+) AND NVL(bso.operation_type,1) = 1 AND bso.line_id IS NULL AND wo.wip_entity_id = wor.wip_entity_id AND wo.operation_seq_num = wor.operation_seq_num AND wo.organization_id = wor.organization_id AND br.resource_id = wor.resource_id AND br.organization_id = wor.organization_id AND wo.department_id = bdr.department_id AND wor.resource_id = bdr.resource_id and wor.activity_id = ca.activity_id(+) AND br.resource_type = restype.lookup_code(+) AND NVL(wor.basis_type ,1) = resbasis.lookup_code(+) AND wor.autocharge_type = reschg.lookup_code(+) AND wor.scheduled_flag = ressch.lookup_code(+) AND wdj.status_type = status_lkp.lookup_code(+) AND wor.organization_id = wip_osp.organization_id(+) AND wor.wip_entity_id = wip_osp.wip_entity_id(+) AND wor.operation_seq_num = wip_osp.operation_seq_num(+) AND wor.resource_seq_num = wip_osp.resource_seq_num(+) /*AND wor.autocharge_type in (3,4)*/ AND NVL(bso.operation_type,1) = 1 AND NVL(reschg.language,restype.language) = NVL(ressch.language,restype.language) AND NVL(resbasis.language,restype.language) = NVL(ressch.language,restype.language) AND restype.language = NVL(ressch.language,restype.language) AND nonelk.language = status_lkp.language AND restype.language = nonelk.language AND haout.language = nonelk.language AND haout.language = yeslk.language AND haout.language = nolk.language AND NVL(reschg.language,restype.language) = NVL(operation_ns_lkp.language,restype.language) AND NVL(reschg.language,restype.language) = NVL(operation_com_lkp.language,restype.language) AND NVL(reschg.language,restype.language) = NVL(operation_run_lkp.language,restype.language) AND wdj.status_type IN (1,3,4,5,6,7,12,14,15) AND we.entity_type IN (1,3) AND (NVL(wdj.date_completed,wdj.scheduled_completion_date) >= SYSDATE - 30 OR wdj.status_type IN (3,6) OR (wdj.status_type = 1 AND wdj.scheduled_start_date <= SYSDATE + 7 )) )WHERE language in ('US') ) PIVOT ( MAX(RESOURCE_TYPE) AS RESOURCE_TYPE,MAX(OSP_OPERATION) AS OSP_OPERATION, MAX(RESOURCE_BASIS_TYPE) AS RESOURCE_BASIS_TYPE ,MAX(JOB_STATUS) AS JOB_STATUS, MAX(STANDARD_RATE) AS STANDARD_RATE , MAX(AUTO_CHARGE_FLAG) AS AUTO_CHARGE_FLAG ,MAX(PHANTOM_FLAG) AS PHANTOM_FLAG , MAX(SCHEDULED_FLAG) AS SCHEDULED_FLAG ,MAX(SUPPLIER) AS SUPPLIER,MAX(BUYER_NAME) AS BUYER_NAME, MAX(ONTRACK) AS ONTRACK ,MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME ,MAX(OPERATION_STATUS) AS OPERATION_STATUS, MAX(CHARGE_TYPE) AS CHARGE_TYPE FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |