SELECT prh.segment1 ir_number,
prh.creation_date ir_date,
ppf.full_name requestor,
hl.location_code,
ood.organization_code destination_org_code,
ood.organization_name destination_org_name,
ood1.organization_code source_org_code,
ood1.organization_name source_org_name,
prh.authorization_status ir_status,
(select action_date from po_action_history
where object_type_code='REQUISITION' and action_code='APPROVE'
and employee_id=prh.preparer_id and object_id=prh.requisition_header_id ) ir_approved_date,
msi.segment1 item_code,
msi.description item_description,
prl.quantity,
prl.unit_meas_lookup_code,
ooh.order_number internal_so_number,
ooh.ordered_date sale_order_date,
(select distinct request_number
from wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
where wdd.source_header_id=ooh.header_id
and wdd.move_order_line_id=mtrl.line_id
and mtrl.header_id=mtrh.header_id)move_order_ref,
(select mtrh.creation_date
from wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
where wdd.source_header_id=ooh.header_id
and wdd.move_order_line_id=mtrl.line_id
and mtrl.header_id=mtrh.header_id
and rownum=1)transact_move_order_date,
(select global_attribute16
from wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
where wdd.source_header_id=ooh.header_id
and wdd.delivery_detail_id=wda.delivery_detail_id
and wda.delivery_id=wnd.delivery_id
and rownum=1)actual_dispatch_date,
(select rsh.receipt_num
from rcv_transactions rcv,
rcv_shipment_headers rsh
where rcv.requisition_line_id = prl.requisition_line_id
and rcv.shipment_header_id = rsh.shipment_header_id
and rownum=1)destination_org_mrn_number,
(select rsh.creation_date
from rcv_transactions rcv,
rcv_shipment_headers rsh
where rcv.requisition_line_id = prl.requisition_line_id
and rcv.shipment_header_id = rsh.shipment_header_id
and rownum=1)destination_org_mrn_date
-- mtrl.reference move_order_ref
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
per_all_people_f ppf,
hr_locations hl,
org_organization_definitions ood,
org_organization_definitions ood1,
mtl_system_items_b msi,
oe_order_headers_all ooh
-- oe_order_lines_all oola
WHERE 1=1
AND prh.segment1 = '10172000065'--111597--1043
AND prh.requisition_header_id = prl.requisition_header_id
AND prl.to_person_id = ppf.person_id
AND ppf.effective_end_date > sysdate
AND prl.deliver_to_location_id = hl.location_id(+)
AND prl.destination_organization_id = ood.organization_id
AND prl.source_organization_id = ood1.organization_id
AND prl.item_id = msi.inventory_item_id
AND prl.source_organization_id = msi.organization_id
AND prh.requisition_header_id = ooh.source_document_id
--AND oola.header_id = ooha.header_id(+)
Good Blog ! Always help whenever required any information just search here you will find everything.
ReplyDeleteyes, i find so, thank you all members(who are helping).
ReplyDelete