-- PO and Requisition with EAM Work Order:
SELECT porl.wip_entity_id,
(SELECT we.wip_entity_name FROM WIP_ENTITIES we
WHERE porl.wip_entity_id = we.wip_entity_id) work_order,
-- pol.category_id,
(SELECT organization_code
FROM mtl_parameters WHERE organization_id= porl.destination_organization_id ) organization_code,
-- porl.wip_operation_seq_num OPERATION_SEQ_NUM,
porh.segment1 PR_NUMBER,
porl.line_num PR_LINE,
-- porh.creation_date PR_Request_date,
-- g.EMPLOYEE_NUMBER Requester_Numner,
-- g.FULL_NAME Requester_NAME,
-- porh.authorization_status PR_STATUS,
poh.segment1 PO_NUMBER,
NVL(POH.TYPE_LOOKUP_CODE,porh.type_lookup_code) PO_DOCUMENT_TYPE,
-- pol.line_num PO_LINE,
nvl(poh.CREATION_DATE,porh.CREATION_DATE) PO_CREATION_DATE,
NVL(poh.authorization_status,porh.authorization_status) PO_STATUS,
(SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id =nvl(pol.item_id,porl.item_id)
AND organization_id = NVL(pol.org_id,porl.destination_organization_id)) PO_LINE_ITEM,
(SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id =nvl(pol.item_id,porl.item_id)
AND organization_id = NVL(pol.org_id,porl.destination_organization_id)) PO_MATERIAL,
(SELECT description FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id =NVL(pol.item_id,porl.item_id)
AND organization_id = NVL(pol.org_id,porl.destination_organization_id)) PO_LINE_ITEM_DESCRIPTION,
NVL( pol.UNIT_MEAS_LOOKUP_CODE,porl.UNIT_MEAS_LOOKUP_CODE)PO_ORDER_LINE_UOM,
--
(SELECT mcst.category_set_name
FROM apps.mtl_item_categories MIC,
apps.mtl_category_sets_tl MCST
WHERE MCST.category_set_id=MIC.category_set_id
AND category_id = NVL(pol.category_id,porl.category_id)
AND mic.organization_id =NVL(pol.org_id,porl.destination_organization_id)
AND mic.inventory_item_id = NVL(pol.item_id,porl.item_id) ) Matrial_Group,
--
/* ( SELECT MCST.category_set_name
FROM apps.mtl_system_items_b MSB,
apps.mtl_categories_kfv MCK,
apps.mtl_item_categories MIC,
apps.mtl_category_sets_tl MCST
WHERE MIC.inventory_item_id = MSB.inventory_item_id
and MIC.category_id = MCK.category_id
and MSB.organization_id =MIC.organization_id
and MCST.category_set_id=MIC.category_set_id
-- and MCST.category_set_name = 'ALK Material Group'
and MSB.organization_id = porl.destination_organization_id
and MSB.inventory_item_id=porl.item_id
and MIC.category_id = porl.category_id) Matrial_Group_v2,*/
--
(SELECT concatenated_segments from gl_code_combinations_kfv
WHERE code_combination_id = pord.code_combination_id) Acct_Assignment_Cat,
NVL (pol.quantity, porl.quantity) Quantity_requested,
(SELECT location_code
FROM hr_locations_all
WHERE LOCATION_ID = NVL(poh.ship_to_location_id,porl.deliver_to_location_id)) Delivery_To_location,
-- rsh.creation_date Delivery_Date,
(SELECT segment1 FROM PO_VENDORS pv
WHERE vendor_id = NVL (poh.vendor_id,porl.vendor_id)) supplier_number,
(SELECT vendor_name FROM PO_VENDORS pv
WHERE vendor_id = NVL (poh.vendor_id,porl.vendor_id)) supplier_name,
NVL(pol.unit_price,porl.unit_price) Net_Price,
(NVL (porl.unit_price, 0) * NVL (porl.quantity, 0)) Net_Order_Value,
(SELECT DT.TITLE||'-'||DST.SHORT_TEXT AS "TITLE AND SHORT TEXT"
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_SHORT_TEXT DST
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DST.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'SHORT_TEXT'
AND ad.pk2_value (+) = porl.WIP_ENTITY_ID ) PO_Short_Text
FROM PO_REQUISITION_HEADERS_ALL porh,
PO_REQUISITION_LINES_ALL porl,
PO_REQ_DISTRIBUTIONS_ALL pord,
PO_DISTRIBUTIONS_ALL pod,
PO_LINES_ALL pol,
PO_HEADERS_ALL poh,
PO_LINE_LOCATIONS_ALL poll,
RCV_SHIPMENT_LINES rsl,
RCV_SHIPMENT_HEADERS rsh,
PO_RELEASES_ALL por,
(SELECT person_id, full_name, EMPLOYEE_NUMBER
FROM PER_ALL_PEOPLE_F
WHERE TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE) g
WHERE porh.requisition_header_id = porl.requisition_header_id(+)
AND porl.requisition_line_id = pord.requisition_line_id(+)
AND pord.distribution_id = pod.req_distribution_id(+)
AND pod.po_header_id = pol.po_header_id(+)
AND pod.po_line_id = pol.po_line_id(+)
AND pol.po_header_id = poh.po_header_id(+)
AND pod.po_header_id = poll.po_header_id(+)
AND pod.po_line_id = poll.po_line_id(+)
AND pod.line_location_id = poll.line_location_id(+)
AND pod.po_header_id = rsl.po_header_id(+)
AND pod.po_line_id = rsl.po_line_id(+)
AND pod.line_location_id = rsl.po_line_location_id(+)
AND rsl.shipment_header_id = rsh.shipment_header_id(+)
AND porl.to_person_id = g.person_id(+)
AND poll.po_release_id = por.po_release_id(+)
AND porl.wip_entity_id IS NOT NULL ;
-- AND porl.wip_entity_id = 967820
-- AND porl.wip_entity_id = 1021821;
-- AND porh.segment1 = '5500000282';
-- Material Transaction with Work Order in Inventory --(Direct Item)
--------------------------------------------------------------------------------------------
SELECT (SELECT we.wip_entity_name FROM WIP_ENTITIES we
WHERE we.WIP_ENTITY_ID = wro.WIP_ENTITY_ID) work_order,
(SELECT organization_code
FROM mtl_parameters WHERE organization_id= mtrh.organization_id) organization_code,
mtrh.REQUEST_NUMBER PR_MO_NUMBER,
msib.segment1 item_number,
msib.description item_description,
msib.item_type,
( SELECT MCST.category_set_name
FROM apps.MTL_SYSTEM_ITEMS_B MSB,
apps.MTL_CATEGORIES_KFV MCK,
apps.MTL_ITEM_CATEGORIES MIC,
apps.MTL_CATEGORY_SETS_TL MCST
WHERE MIC.inventory_item_id = MSB.inventory_item_id
AND MIC.category_id = MCK.category_id
AND MSB.organization_id =MIC.organization_id
AND MSB.organization_id = msib.organization_id
AND MCST.category_set_id=MIC.category_set_id
AND MCST.category_set_name = 'ALK Material Group'
AND MSB.inventory_item_id =msib.inventory_item_id) MATRIAL_GROUP,
cic.ITEM_COST STANDARD_COST,
msib.LIST_PRICE_PER_UNIT Price_UOM,
msib.PRIMARY_UNIT_OF_MEASURE,
'Standard' Price_control,
(SELECT abc_class_name
FROM MTL_ABC_CLASSES mac,
MTL_ABC_ASSIGNMENTS maa,
MTL_ABC_ASSIGNMENT_GROUPS MAAG
WHERE 1 =1
AND maa.inventory_item_id = msib.inventory_item_id
AND maa.assignment_group_id = maag.assignment_group_id
AND maa.abc_class_id = mac.abc_class_id
AND maag.organization_id = msib.organization_id
AND maa.creation_date >=
(SELECT MAX(maa.creation_date)
FROM MTL_ABC_ASSIGNMENTS maa,
MTL_ABC_ASSIGNMENT_GROUPS maag
WHERE 1 =1
AND maa.inventory_item_id = msib.inventory_item_id
AND maa.assignment_group_id = maag.assignment_group_id
AND maag.organization_id = msib.organization_id
)
) ABC_INDICATOR,
'XXXXX' VALUATION_CLASS,
'XXXXX' MRP_Type
FROM WIP_REQUIREMENT_OPERATIONS wro,
MTL_TXN_REQUEST_LINES mtrl,
MTL_TXN_REQUEST_HEADERS mtrh,
CST_ITEM_COSTS cic ,
MTL_SYSTEM_ITEMS_B msib
WHERE wro.WIP_ENTITY_ID = mtrl.TXN_SOURCE_ID(+)
AND wro.inventory_item_id = mtrl.inventory_item_id(+)
AND wro.operation_seq_num = mtrl.TXN_SOURCE_LINE_ID(+)
AND mtrl.header_id = mtrh.header_id
AND mtrl.INVENTORY_ITEM_ID = cic.INVENTORY_ITEM_ID(+)
AND mtrl.ORGANIZATION_ID = cic.ORGANIZATION_ID(+)
AND msib.ORGANIZATION_ID = mtrl.ORGANIZATION_ID
AND msib.INVENTORY_ITEM_ID = mtrl.INVENTORY_ITEM_ID
-- AND c.REQUEST_NUMBER='2410357'
AND wro.WIP_ENTITY_ID = 1021821;
EAM Work Order Details:
----------------------------------------
SELECT -- wdj.ORGANIZATION_ID,
(SELECT organization_code
FROM mtl_parameters WHERE organization_id= wdj.ORGANIZATION_ID) organization_code,
-- wdj.WIP_ENTITY_ID,
we.wip_entity_name work_order,
bd.department_code department,
bd.description department_description,
cii.instance_number asset_number,
wdj.description wo_description,
cii.instance_description asset_number_desc,
-- msibk.eam_item_type asset_type,
TRUNC(wdj.creation_date) WO_Creation_date,
TO_CHAR(wdj.scheduled_start_date,'DD-MON-YYYY') scheduled_start_date,
TO_CHAR(wdj.scheduled_completion_date,'DD-MON-YYYY') scheduled_completion_date,
ROUND((wdj.SCHEDULED_COMPLETION_DATE - wdj.SCHEDULED_START_DATE) * 24,3) WO_Duration,
(SELECT A2.MEANING
FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2
WHERE A2.LOOKUP_TYPE LIKE '%WIP_JOB_STATUS%'
AND TO_CHAR(A1.STATUS_TYPE)=A2.LOOKUP_CODE
AND a1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID
AND A2.LANGUAGE='US') WO_STATUS,
wdj.CLASS_CODE WIP_Account,
(SELECT A2.MEANING
FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2
WHERE A2.LOOKUP_TYPE ='WIP_EAM_ACTIVITY_PRIORITY'
AND TO_CHAR(A1.priority)=A2.LOOKUP_CODE
AND a1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID
AND A2.LANGUAGE='US') Work_Order_Priority,
(SELECT A2.MEANING
FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2
WHERE A2.LOOKUP_TYPE ='WIP_EAM_WORK_ORDER_TYPE'
AND TO_CHAR(A1.WORK_ORDER_TYPE)=A2.LOOKUP_CODE
AND a1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID
AND A2.LANGUAGE='US') WorkOrder_Type,
(SELECT A2.MEANING
FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2
WHERE A2.LOOKUP_TYPE ='MTL_EAM_ACTIVITY_TYPE'
AND TO_CHAR(A1.activity_type)=A2.LOOKUP_CODE
AND A2.LOOKUP_CODE = TO_CHAR(wdj.activity_type)
AND A1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID
AND A2.LANGUAGE='US') WorkOrder_Activity_Type,
wr.work_request_number,
(SELECT DT.TITLE||'-'||DLT.LONG_TEXT AS "TITLE AND LONG TEXT"
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_LONG_TEXT DLT
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'LONG_TEXT'
AND ad.pk2_value (+) = wdj.WIP_ENTITY_ID ) Work_Order_Attachment,
wepb.actual_cost
FROM WIP_DISCRETE_JOBS wdj,
WIP_ENTITIES we,
WIP_OPERATIONS wo,
BOM_DEPARTMENTS bd,
CSI_ITEM_INSTANCES cii,
WIP_EAM_WORK_REQUESTS wr,
MTL_SYSTEM_ITEMS_B_KFV msibk,
EAM_WORK_ORDER_DETAILS ewod,
( SELECT wepb.organization_id,
wepb.wip_entity_id,
NVL(SUM(wepb.actual_mat_cost)+SUM(wepb.actual_lab_cost)+SUM(wepb.actual_eqp_cost),0) actual_cost
FROM WIP_EAM_PERIOD_BALANCES wepb
WHERE
(wepb.period_name,wepb.period_set_name) IN
( SELECT
period_name,
TO_CHAR(NULL) period_set_name
FROM org_acct_periods oap
UNION ALL
SELECT
gp.period_name,
gp.period_set_name
FROM
gl_periods gp
WHERE gp.adjustment_period_flag='N'
)
GROUP BY
wepb.organization_id,
wepb.wip_entity_id
) wepb
WHERE 1=1
AND wdj.WIP_ENTITY_ID = wo.WIP_ENTITY_ID(+)
AND wdj.WIP_ENTITY_ID = ewod.WIP_ENTITY_ID(+)
AND wdj.MAINTENANCE_OBJECT_ID = cii.INSTANCE_ID(+)
AND wdj.WIP_ENTITY_ID = wr.wip_entity_id(+)
AND wdj.owning_department=bd.department_id(+)
AND NVL(wdj.asset_group_id,wdj.rebuild_item_id)=msibk.inventory_item_id(+)
AND wdj.organization_id=msibk.organization_id(+)
AND wdj.organization_id=we.organization_id
AND wdj.WIP_ENTITY_ID = we.WIP_ENTITY_ID
AND wdj.organization_id=wepb.organization_id(+)
AND wdj.wip_entity_id=wepb.wip_entity_id(+)
AND we.entity_type IN (6,7);
AND we.wip_entity_name='87034';
