ENGINATICS
SYSTEM_ADMINISTRATOR
SYSADMIN
EEE75CEB4B096AD8E05362FB09051F8BY
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_SERIAL_STATUS','WIP_INTRAOPERATION_STEP','SERIAL_NUM_STATUS','WIP_SERIAL_YIELD')
AND view_application_id = 700
AND security_group_id = 0
),
wdj AS (SELECT /*+ materialize leading(wdj) */
wdj.*,
we.wip_entity_name,
we.entity_type
FROM
wip_discrete_jobs wdj , wip_entities we
WHERE
wdj.wip_entity_id = we.wip_entity_id
AND we.entity_type IN ( 1, 3 )
AND wdj.status_type IN (1,3,4,5,7,12,14,15 )
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 )) )
SELECT /*+ leading(wdj msn) no_merge(wdj) */
TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id ||'-'||msn.serial_number||'-SER') ecc_spec_id,
wdj.wip_entity_id job,
wdj.wip_entity_name job_name,
status_lkp.meaning job_status,
wdj.organization_id organization_id,
mp.organization_code organization_code,
CASE WHEN (SELECT SUM(CASE
WHEN autocharge_type IN(3,4)
THEN 1
ELSE 0
END)
FROM wip_operation_resources wor
WHERE wdj.wip_entity_id = wor.wip_entity_id
AND wdj.organization_id = wor.organization_id
) > 0 THEN 1 ELSE 2 END osp_operation_flag,
WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly ,
WIP_ECC_UTIL_PVT.get_primary_uom_code(wdj.organization_id,wdj.primary_item_id) assembly_uom ,
WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) assembly_description ,
(SELECT acat.concatenated_segments
FROM mtl_item_catalog_groups_b_kfv acat
WHERE acat.item_catalog_group_id = WIP_ECC_UTIL_PVT.get_item_catalog_group_id(wdj.organization_id,wdj.primary_item_id)
) assembly_catalog_grp,
(SELECT msi6.concatenated_segments
FROM mtl_system_items_vl msi6
WHERE msi6.inventory_item_id = WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.primary_item_id)
AND msi6.organization_id = wdj.organization_id
) assembly_base_model,
msn.serial_number ,
to_char(msn.serial_number
|| ' ('
|| wip_ecc_util_pvt.get_concatenated_segments(wdj.organization_id, wdj.primary_item_id)
|| ', '
|| mp.organization_code
|| ')') SERIAL_ITEM_ORG,
msn.wip_entity_id wip_entity_id ,
NULL parent_object_id,
msn.operation_seq_num operation_seq_num ,
msn.intraoperation_step_type intraoperation_step_type ,
msn.cycles_since_mark ,
msn.cycles_since_new ,
msn.cycles_since_overhaul ,
msn.cycles_since_repair ,
msn.cycles_since_visit ,
msn.number_of_repairs ,
msn.territory_code ,
msn.time_since_mark ,
msn.time_since_new ,
msn.time_since_overhaul ,
msn.time_since_repair ,
msn.time_since_visit ,
msn.gen_object_id GEN_OBJECT_ID,
wip_serial_status_code.meaning wip_serial_status,
wip_serial_status_code.lookup_code wip_serial_status_flag,
assembly_serial_op_step.meaning assembly_serial_op_step,
inv_serial_status_code.meaning inventory_serial_status,
inv_serial_status_code.lookup_code inv_serial_status_code,
inv_serial_status_code.language,
CASE
WHEN intraoperation_step_type IN (5)
THEN 'Scrap'
WHEN intraoperation_step_type IN (4)
THEN 'Reject'
ELSE 'In progress'
END serial_type,
(SELECT SUM(
CASE
WHEN fm_operation_seq_num > to_operation_seq_num
OR (fm_operation_seq_num = to_operation_seq_num
AND to_intraoperation_Step_type IN (1,2)
AND fm_intraoperation_Step_type IN (3,4,5))
THEN 1
ELSE 0
END )
FROM wip_move_transactions wmt,
wip_serial_move_transactions wsmt
WHERE wmt.organization_id = wdj.organization_id
AND wmt.to_operation_seq_num = msn.operation_seq_num
AND wmt.to_intraoperation_Step_type = msn.intraoperation_step_type
AND wmt.to_intraoperation_Step_type IN (4,5)
AND wsmt.transaction_id = wmt.transaction_id
AND wsmt.assembly_serial_number = msn.serial_number
AND wmt.wip_entity_id = msn.wip_entity_id
) rework_quantity,
CASE
WHEN intraoperation_step_type IN (5)
THEN scrap_lkp.meaning
WHEN intraoperation_step_type IN (4)
THEN reject_lkp.meaning
ELSE NULL
END yield
FROM mtl_serial_numbers msn,
mtl_parameters mp,
wdj wdj,
(SELECT
ml2.meaning,
ml2.language,
to_number(ml2.lookup_code) LOOKUP_CODE
FROM lookup_details ml2
WHERE ml2.lookup_type = 'WIP_SERIAL_STATUS'
AND ml2.view_application_id = 700
AND ml2.security_group_id = 0
) wip_serial_status_code , (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
ml3.meaning,
ml3.language,
ml3.lookup_code
FROM lookup_details ml3
WHERE ml3.lookup_type = 'WIP_INTRAOPERATION_STEP'
AND ml3.view_application_id = 700
AND ml3.security_group_id = 0
) assembly_serial_op_step ,
(SELECT
ml4.lookup_code,
ML4.MEANING,
ml4. language
FROM lookup_details ml4
WHERE ml4.lookup_type = 'SERIAL_NUM_STATUS'
AND ml4.view_application_id = 700
AND ml4.security_group_id = 0
) inv_serial_status_code ,
(SELECT
ml4.lookup_code,
ML4.MEANING,
ml4. language
FROM lookup_details ml4
WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD'
AND ml4.view_application_id = 700
AND ml4.security_group_id = 0
AND ml4.lookup_code =1
) scrap_lkp,
(SELECT
ml4.lookup_code,
ML4.MEANING,
ml4. language
FROM lookup_details ml4
WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD'
AND ml4.view_application_id = 700
AND ml4.security_group_id = 0
AND ml4.lookup_code =2
) reject_lkp
WHERE msn.wip_entity_id = wdj.wip_entity_id
AND wdj.organization_id = mp.organization_id
AND inv_serial_status_code.lookup_code(+) = msn.current_status
AND assembly_serial_op_step.lookup_code(+) = msn.intraoperation_step_type
AND wip_serial_status_code.lookup_code(+) = DECODE(msn.operation_seq_num, NULL, 1, 2)
AND wdj.status_type = status_lkp.lookup_code (+)
AND status_lkp.language = nvl(inv_serial_status_code.language, status_lkp.language)
AND status_lkp.language = nvl(assembly_serial_op_step.language, status_lkp.language)
AND status_lkp.language = nvl(wip_serial_status_code.language, status_lkp.language)
AND status_lkp.language = nvl(scrap_lkp.language, status_lkp.language)
AND status_lkp.language = nvl(reject_lkp.language, status_lkp.language)
AND wdj.primary_item_id = msn.inventory_item_id
UNION ALL SELECT ecc_spec_id,
job,
job_name,
job_status,
organization_id,
organization_code,
osp_operation_flag,
assembly ,
assembly_uom ,
assembly_description ,
assembly_catalog_grp,
assembly_base_model,
serial_number ,
to_char(serial_number
|| ' ('
|| assembly
|| ', '
|| organization_code
|| ')') SERIAL_ITEM_ORG,
wip_entity_id ,
parent_object_id,
operation_seq_num operation_seq_num ,
intraoperation_step_type intraoperation_step_type ,
cycles_since_mark ,
cycles_since_new ,
cycles_since_overhaul ,
cycles_since_repair ,
cycles_since_visit ,
number_of_repairs ,
territory_code ,
time_since_mark ,
time_since_new ,
time_since_overhaul ,
time_since_repair ,
time_since_visit ,
GEN_OBJECT_ID,
wip_serial_status ,
wip_serial_status_flag ,
assembly_serial_op_step ,
inventory_serial_status ,
inv_serial_status_code,
language,
serial_type,
rework_quantity,
CASE WHEN rework_quantity > 0 THEN rework_flag ELSE accepted_flag END yield
FROM (
SELECT /*+ leading(wdj mmt) no_merge(wdj) index(mmt MTL_MATERIAL_TRANSACTIONS_N2) */
TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id ||'-'||msn.serial_number||'-SER') ecc_spec_id,
wdj.wip_entity_id job,
wdj.wip_entity_name job_name,
wdj.organization_id organization_id,
mp.organization_code organization_code,
CASE WHEN (SELECT SUM(CASE
WHEN autocharge_type IN(3,4)
THEN 1
ELSE 0
END)
FROM wip_operation_resources wor
WHERE wdj.wip_entity_id = wor.wip_entity_id
AND wdj.organization_id = wor.organization_id
) > 0 THEN 1 ELSE 2 END osp_operation_flag,
WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly ,
WIP_ECC_UTIL_PVT.get_primary_uom_code(wdj.organization_id,wdj.primary_item_id) assembly_uom ,
WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) assembly_description ,
(SELECT acat.concatenated_segments
FROM mtl_item_catalog_groups_b_kfv acat
WHERE acat.item_catalog_group_id = WIP_ECC_UTIL_PVT.get_item_catalog_group_id(wdj.organization_id,wdj.primary_item_id)
) assembly_catalog_grp,
(SELECT msi6.concatenated_segments
FROM mtl_system_items_vl msi6
WHERE msi6.inventory_item_id = WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.primary_item_id)
AND msi6.organization_id = wdj.organization_id
) assembly_base_model,
msn.serial_number ,
wdj.wip_entity_id ,
msn.gen_object_id parent_object_id,
msn.operation_seq_num operation_seq_num ,
msn.intraoperation_step_type intraoperation_step_type ,
msn.cycles_since_mark ,
msn.cycles_since_new ,
msn.cycles_since_overhaul ,
msn.cycles_since_repair ,
msn.cycles_since_visit ,
msn.number_of_repairs ,
msn.territory_code ,
msn.time_since_mark ,
msn.time_since_new ,
msn.time_since_overhaul ,
msn.time_since_repair ,
msn.time_since_visit ,
msn.gen_object_id GEN_OBJECT_ID,
wip_serial_status.meaning wip_serial_status ,
3 wip_serial_status_flag ,
null assembly_serial_op_step ,
inv_serial_status.meaning inventory_serial_status ,
inv_serial_status.lookup_code inv_serial_status_code,
inv_serial_status.language,
'Completed' serial_type,
(SELECT SUM(
CASE
WHEN fm_operation_seq_num > to_operation_seq_num
OR (fm_operation_seq_num = to_operation_seq_num
AND to_intraoperation_Step_type IN (1,2)
AND fm_intraoperation_Step_type IN (3,4,5))
THEN 1
ELSE 0
END )
FROM wip_move_transactions wmt,
wip_serial_move_transactions wsmt
WHERE wmt.organization_id = wdj.organization_id
AND wmt.to_operation_seq_num = msn.operation_seq_num
AND wmt.to_intraoperation_Step_type = msn.intraoperation_step_type
AND wmt.to_intraoperation_Step_type IN (4,5)
AND wsmt.transaction_id = wmt.transaction_id
AND wsmt.assembly_serial_number = msn.serial_number
AND wmt.wip_entity_id = msn.wip_entity_id
) rework_quantity,
rework_lkp.meaning rework_flag,
accepted_lkp.meaning accepted_flag,
status_lkp.meaning job_status
FROM mtl_serial_numbers msn,
mtl_parameters mp ,
mtl_system_items_vl msi8,
/*mtl_object_genealogy mog,*/
mtl_material_transactions mmt,
mtl_unit_transactions mut,
wdj wdj,
(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
ml4.lookup_code,
ML4.MEANING,
ml4. language
FROM lookup_details ml4
WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD'
AND ml4.view_application_id = 700
AND ml4.security_group_id = 0
AND ml4.lookup_code =4
) accepted_lkp,
(SELECT
ml4.lookup_code,
ML4.MEANING,
ml4. language
FROM lookup_details ml4
WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD'
AND ml4.view_application_id = 700
AND ml4.security_group_id = 0
AND ml4.lookup_code =3
) rework_lkp ,
(SELECT
ml4.lookup_code,
ML4.LANGUAGE,
ml4.meaning
FROM lookup_details ml4
WHERE ml4.lookup_type = 'SERIAL_NUM_STATUS'
AND ml4.view_application_id = 700
AND ml4.security_group_id = 0
) inv_serial_status,
/* (SELECT
ml8.meaning,
ml8.lookup_code,
ML8.LANGUAGE
FROM lookup_details ml8
WHERE ml8.lookup_type = 'WIP_INTRAOPERATION_STEP'
AND ml8.view_application_id = 700
AND ml8.security_group_id = 0
) wip_intraoperation_step, */
(SELECT
ml6.meaning,
ml6.lookup_code,
ML6.LANGUAGE
FROM lookup_details ml6
WHERE ml6.lookup_type = 'WIP_SERIAL_STATUS'
AND ml6.view_application_id = 700
AND ml6.lookup_code = '3'
AND ml6.security_group_id = 0
) wip_serial_status
WHERE
/* msn.gen_object_id = mog.parent_object_id
AND we.gen_object_id = mog.object_id
AND mog.object_type = 5
AND mog.parent_object_type = 2
AND mog.end_date_active IS NULL */
mut.transaction_id = decode (msi8.lot_control_code,1, mmt.transaction_id,
( SELECT mtln.serial_transaction_id
from mtl_transaction_lot_numbers mtln
where mtln.transaction_id = mmt.transaction_id)
)
AND mmt.transaction_source_type_id = 5
AND mmt.organization_id = wdj.organization_id
AND mmt.transaction_source_id = wdj.wip_entity_id
AND mmt.transaction_action_id =31
AND mut.serial_number = msn.serial_number
AND mut.inventory_item_id = msn.inventory_item_id
AND wdj.wip_entity_id <> nvl(msn.wip_entity_id,-1)
AND wdj.entity_type IN (1,3)
/* AND wip_intraoperation_step.lookup_code(+) = msn.intraoperation_step_type */
AND inv_serial_status.lookup_code(+) = msn.current_status
AND status_lkp.language = nvl(inv_serial_status.language, status_lkp.language)
/* AND status_lkp.language = nvl(wip_intraoperation_step.language, status_lkp.language) */
AND status_lkp.language = nvl(wip_serial_status.language, status_lkp.language)
AND status_lkp.language = nvl(accepted_lkp.language, status_lkp.language)
AND status_lkp.language = nvl(rework_lkp.language, status_lkp.language)
AND wdj.primary_item_id = msn.inventory_item_id
AND wdj.status_type = status_lkp.lookup_code (+)
AND wdj.organization_id = msi8.organization_id(+)
AND wdj.primary_item_id = msi8.inventory_item_id(+)
AND wdj.organization_id = mp.organization_id
/* AND wdj.serialization_start_op IS NOT NULL */ )src WHERE SRC.LANGUAGE IN ('US') ) PIVOT ( MAX(WIP_SERIAL_STATUS) AS WIP_SERIAL_STATUS ,
MAX(INVENTORY_SERIAL_STATUS) AS INVENTORY_SERIAL_STATUS , MAX(YIELD) AS YIELD , MAX ( ASSEMBLY_SERIAL_OP_STEP
) AS ASSEMBLY_SERIAL_OP_STEP, MAX ( JOB_STATUS ) AS JOB_STATUS FOR LANGUAGE IN ('US' "US"))
) x
where
2=2
AR
ECC التصنيع التجميعي, الأرقام المسلسلة
Imported from Enterprise Command Center
Description: تحتوي مجموعة البيانات هذه على معلومات متعلقة بتفاصيل أوامر الشغل على مستوى الأرقام المسلسلة
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
D
ECC Diskrete Fertigung, Seriennummern
Imported from Enterprise Command Center
Description: Dieses DatenSet speichert Informationen über Fertigungsauftragsdetails auf Seriennummernebene
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
E
ECC Discrete Manufacturing, Números de Serie
Imported from Enterprise Command Center
Description: Juego de datos con información sobre detalles de nivel de número de serie para órdenes de trabajo
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
F
ECC Discrete Manufacturing, Numéros de série
Imported from Enterprise Command Center
Description: Ce jeu de données contient des infos de détail niveau numéros série pour les ordres de fabrication
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
I
ECC Produzione discreta, Numeri di serie
Imported from Enterprise Command Center
Description: Questa serie di dati contiene dettagli a livello di numero di serie per gli ordini di lavorazione
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
JA
ECC ショップ型製造, シリアル番号
Imported from Enterprise Command Center
Description: このデータ・セットには作業指示のシリアルレベルの詳細に関する情報が含まれています
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
KO
ECC Discrete Manufacturing, 일련번호
Imported from Enterprise Command Center
Description: 이 데이터 세트에는 작업 지시에 대한 일련번호 레벨 상세내역 정보가 포함되어 있습니다.
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
RU
ECC Дискретное производство, Серийные номера
Imported from Enterprise Command Center
Description: В этом наборе данных содержатся сведения о заказах на выполнение работ на уровне серийных номеров
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
S
ECC Diskret tillverkning, Serienummer
Imported from Enterprise Command Center
Description: Den här datauppsättningen innehåller information om detaljer på serienummernivå för arbetsorder.
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
TR
ECC Kesikli Üretim, Seri Numaraları
Imported from Enterprise Command Center
Description: Bu veri kümesinde iş emirleri için seri düzey detaylar hakkında bilgiler saklanır
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
US
ECC Discrete Manufacturing, Serial Numbers
Imported from Enterprise Command Center
Description: This data set holds information about serial-level details for work orders.
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
ZHS
ECC Discrete Manufacturing, 序列号
Imported from Enterprise Command Center
Description: 此数据集包含工作单的序列层详细信息
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Enterprise Command Center