1. WO Information.
2. WO Operation
3. Project
4. Resource
5. GL Account
6. Asset
7. PTW Information
8. PO - If the WO is having PO.
/* Formatted on 11/18/2016 12:33:21 PM (QP5 v5.114.809.3010) */
SELECT a.ORGANIZATION_ID,
a.WIP_ENTITY_ID,
b.OPERATION_SEQ_NUM,
b.DESCRIPTION OP_DESC,
b.FIRST_UNIT_START_DATE OP_SCHEDULE_START_DATE,
b.LAST_UNIT_COMPLETION_DATE OP_SCHEDULE_END_DATE,
(b.LAST_UNIT_COMPLETION_DATE - b.FIRST_UNIT_START_DATE) * 24
WO_OP_Duration,
f.ACTUAL_START_DATE OP_ACTUAL_START_DATE,
f.ACTUAL_END_DATE OP_ACTUAL_END_DATE,
(a.SCHEDULED_COMPLETION_DATE - a.SCHEDULED_START_DATE) * 24
WO_Duration,
a.PROJECT_ID,
a.TASK_ID,
a.PM_SCHEDULE_ID,
a.STATUS_TYPE WO_STATUS_ID,
h.USER_DEFINED_STATUS_ID WO_USER_DEFINED_STATUS_ID,
a.CLASS_CODE WIP_Account,
a.MATERIAL_ACCOUNT MATERIAL_ACCOUNT_ID,
a.NOTIFICATION_REQUIRED Scaffolding_Required_Flag,
a.TAGOUT_REQUIRED Insulation_Required_Flag,
b.ATTRIBUTE2 Permit_Number,
b.ATTRIBUTE3 Permit_Type,
b.ATTRIBUTE4 PTW_Status,
b.ATTRIBUTE6 PTW_DATE,
c.RESOURCE_ID,
c.USAGE_RATE_OR_AMOUNT Quantity_Required,
g.EMPLOYEE_NUMBER,
g.FULL_NAME EMPLOYEE_NAME,
PO.PR_NUMBER,
PO.PR_LINE,
PO.PR_REQUEST_DATE,
PO.REQUESTER_NUMNER,
PO.REQUESTER_NAME,
PO.PR_STATUS,
PO.PO_NUMBER,
PO.PO_LINE,
PO.RELEASE_NUM,
PO.PO_CREATION_DATE,
PO.PO_STATUS,
PO.ITEM_ID,
PO.QUANTITY_REQUESTED,
PO.UNIT_PRICE,
PO.QUANTITY_ONHAND,
PO.QUANTITY_RECEIVED,
PO.DELIVERY_TO,
PO.DELIVERY_DATE,
PO.TOTAL_COST,
a.CREATION_DATE WO_CREATION_DATE,
b.CREATED_BY OP_CREATED_BY,
b.CREATION_DATE OP_CREATION_DATE,
b.DEPARTMENT_ID OP_DEPARTMENT_ID,
a.OWNING_DEPARTMENT WO_OWNING_DEPARTMENT,
cii.INSTANCE_ID ASSET_ID,
wr.WORK_REQUEST_ID WORK_REQUEST_ID,
PO.PO_RELEASE_STATUS
FROM WIP_DISCRETE_JOBS a,
WIP_OPERATIONS b,
WIP_OPERATION_RESOURCES c,
WIP_OP_RESOURCE_INSTANCES d,
BOM_RESOURCE_EMPLOYEES e,
CSI_ITEM_INSTANCES CII,
WIP_EAM_WORK_REQUESTS WR,
(SELECT person_id, full_name, EMPLOYEE_NUMBER
FROM PER_ALL_PEOPLE_F
WHERE TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE) g,
eam_work_order_details h,
(SELECT ACTUAL_START_DATE,
ACTUAL_END_DATE,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
LAST_UPDATE_DATE,
TRANSACTION_TYPE,
CREATION_DATE
FROM EAM_OP_COMPLETION_TXNS
WHERE (TRANSACTION_ID, WIP_ENTITY_ID, OPERATION_SEQ_NUM) IN
( SELECT MAX (TRANSACTION_ID) MAX_TRANSACTION,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM
FROM EAM_OP_COMPLETION_TXNS
WHERE EAM_OP_COMPLETION_TXNS.TRANSACTION_TYPE =
1
GROUP BY WIP_ENTITY_ID, OPERATION_SEQ_NUM)) f,
(SELECT porl.wip_entity_id,
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,
pol.line_num PO_LINE,
por.RELEASE_NUM,
por.authorization_status PO_RELEASE_STATUS,
-- (SELECT RELEASE_NUM
-- FROM po_releases_all
-- WHERE po_release_id = poll.po_release_id)
-- release_num,
poh.CREATION_DATE PO_CREATION_DATE,
poh.authorization_status PO_STATUS,
pol.item_id,
NVL (pol.quantity, poll.quantity) Quantity_requested,
pol.unit_price,
(SELECT SUM (mohq.TRANSACTION_QUANTITY)
FROM MTL_ONHAND_QUANTITIES mohq
WHERE pol.item_id = mohq.inventory_item_id
AND poll.SHIP_TO_ORGANIZATION_ID =
mohq.ORGANIZATION_ID)
QUANTITY_ONHAND,
poll.QUANTITY_RECEIVED,
--poll.SHIP_TO_LOCATION_ID,
(SELECT DESCRIPTION
FROM hr_locations
WHERE LOCATION_ID = poll.SHIP_TO_LOCATION_ID)
Delivery_To,
rsh.creation_date Delivery_Date,
(NVL (pol.unit_price, 0) * NVL (poll.QUANTITY_RECEIVED, 0))
TOTAL_COST
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
UNION ALL
SELECT a.WIP_ENTITY_ID,
a.OPERATION_SEQ_NUM,
c.REQUEST_NUMBER PR_MO_NUMBER,
b.line_number LINE_NUMBER,
c.CREATION_DATE REQUEST_DATE,
-- c.CREATED_BY,
g.EMPLOYEE_NUMBER,
g.FULL_NAME EMPLOYEE_NAME,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
AND LOOKUP_CODE = c.HEADER_STATUS)
STATUS,
NULL PO_NUMBER,
NULL PO_LINE,
NULL RELEASE_NUM,
NULL PO_RELEASE_STATUS,
NULL PO_CREATION_DATE,
NULL PO_STATUS,
b.inventory_item_id ITEM_ID,
b.quantity QUANTITY_REQUESTED,
d.ITEM_COST UNIT_PRICE,
(SELECT SUM (mohq.TRANSACTION_QUANTITY)
FROM MTL_ONHAND_QUANTITIES mohq
WHERE b.INVENTORY_ITEM_ID = mohq.inventory_item_id
AND b.ORGANIZATION_ID = mohq.ORGANIZATION_ID)
QUANTITY_ONHAND,
b.QUANTITY_DELIVERED QUANTITY_RECEIVED,
NULL DELIVERY_TO,
NULL DELIVERY_DATE,
NVL ( (b.quantity * d.ITEM_COST), 0) TOTAL_COST
FROM WIP_REQUIREMENT_OPERATIONS a,
mtl_txn_request_lines b,
mtl_txn_request_headers c,
cst_item_costs d,
(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 a.WIP_ENTITY_ID = b.TXN_SOURCE_ID(+)
AND a.inventory_item_id = b.inventory_item_id(+)
AND a.operation_seq_num = b.TXN_SOURCE_LINE_ID(+)
AND b.header_id = c.header_id
AND b.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID(+)
AND b.ORGANIZATION_ID = d.ORGANIZATION_ID(+)
AND c.CREATED_BY = g.person_id(+)) PO
WHERE a.WIP_ENTITY_ID = b.WIP_ENTITY_ID(+)
AND b.WIP_ENTITY_ID = c.WIP_ENTITY_ID(+)
AND b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM(+)
AND c.WIP_ENTITY_ID = d.WIP_ENTITY_ID(+)
AND c.OPERATION_SEQ_NUM = d.OPERATION_SEQ_NUM(+)
AND c.RESOURCE_SEQ_NUM = d.RESOURCE_SEQ_NUM(+)
AND d.instance_id = e.instance_id(+)
AND c.WIP_ENTITY_ID = f.WIP_ENTITY_ID(+)
AND c.OPERATION_SEQ_NUM = f.OPERATION_SEQ_NUM(+)
AND a.WIP_ENTITY_ID = h.WIP_ENTITY_ID(+)
AND e.PERSON_ID = g.PERSON_ID(+)
AND b.WIP_ENTITY_ID = po.WIP_ENTITY_ID(+)
AND b.OPERATION_SEQ_NUM = po.OPERATION_SEQ_NUM(+)
AND a.MAINTENANCE_OBJECT_ID = cii.INSTANCE_ID(+)
AND a.WIP_ENTITY_ID = wr.wip_entity_id(+)
-- AND b.DEPARTMENT_ID=63234
--AND a.WIP_ENTITY_ID=63234
No comments:
Post a Comment