SELECT DISTINCT PURCHASING_ORG,
buyer_name ,
y.full_name REQUESTER ,
project_number ,
po_number ,
po_line ,
po_creation_date ,
po_type ,
quantity_order ,
item_number ,
item_description ,
rec_qty ,
item_price ,
currency_code ,
uom ,
GSL_NUM ,
vendor_name ,
receipt_num ,
REQUISITION_DATE ,
need_by_date ,
promised_date ,
received_date ,
report_date ,
days_late_to_need ,
org ,
req_no ,
OTD ,
gl_account_type ,
z.full_name receiver
FROM
(SELECT hou.NAME PURCHASING_ORG ,
papf.full_name buyer_name ,
ppa.segment1 project_number ,
ph.segment1 po_number ,
pl.line_num po_line ,
ph.creation_date po_creation_date ,
ph.type_lookup_code po_type ,
pl.quantity quantity_order ,
POD.REQ_DISTRIBUTION_ID ,
prh.preparer_id ,
msib.segment1 item_number ,
pl.item_description ,
plla.quantity_received rec_qty ,
pl.unit_price item_price ,
ph.currency_code ,
pl.unit_meas_lookup_code uom ,
pv.segment1 GSL_NUM ,
pv.vendor_name ,
rsh.receipt_num ,
prh.creation_date REQUISITION_DATE,
plla.need_by_date ,
plla.promised_date ,
--plla.closed_for_receiving_date received_date,
rsh.creation_date received_date ,
sysdate report_date ,
ROUND(to_date(TO_CHAR(rsh.creation_date,'dd-mon-rr'),'dd-mon-rr') - to_date(TO_CHAR(plla.need_by_date,'dd-mon-rr'),'dd-mon-rr'),0) days_late_to_need,
ood.ORGANIZATION_CODE org ,
prh.segment1 req_no ,
CASE
WHEN ROUND(to_date(TO_CHAR(rsh.creation_date,'dd-mon-rr'),'dd-mon-rr') - to_date(TO_CHAR(plla.need_by_date,'dd-mon-rr'),'dd-mon-rr'),0) > 0
THEN 0
WHEN ROUND(to_date(TO_CHAR(rsh.creation_date,'dd-mon-rr'),'dd-mon-rr') - to_date(TO_CHAR(plla.need_by_date,'dd-mon-rr'),'dd-mon-rr'),0) <= 0
THEN 1
END OTD ,
gcc.account_type gl_account_type ,
rsh.EMPLOYEE_ID
FROM apps.po_headers_all ph ,
apps.po_lines_all pl ,
apps.per_all_people_f papf ,
apps.PO_DISTRIBUTIONS_ALL POD ,
apps.PO_REQ_DISTRIBUTIONS_ALL PRD ,
apps.PO_REQUISITION_LINES_ALL PRL ,
apps.PO_REQUISITION_HEADERS_ALL PRH,
apps.pa_projects_all ppa ,
apps.po_line_locations_all plla ,
apps.mtl_system_items_b msib ,
apps.po_vendors pv ,
apps.rcv_shipment_lines rsl ,
apps.rcv_shipment_headers rsh ,
apps.gl_code_combinations gcc ,
apps.HR_ORGANIZATION_UNITS HOU ,
apps.ORG_ORGANIZATION_DEFINITIONS OOD
WHERE ph.po_header_id = pl.po_header_id
AND ph.org_id =pl.org_id
AND ph.agent_id =papf.person_id
AND pl.po_line_id =pod.po_line_id
AND ph.po_header_id = pod.po_header_id
AND pod.req_distribution_id = prd.distribution_id(+)
AND prd.requisition_line_id = prl.requisition_line_id(+)
AND prl.requisition_header_id = prh.requisition_header_id(+)
AND pod.project_id = ppa.project_id(+)
AND ph.po_header_id = plla.po_header_id
AND pl.po_line_id =plla.po_line_id
AND pl.org_id = plla.org_id
AND pl.item_id = msib.inventory_item_id(+)
AND NVL(msib.organization_id,plla.ship_to_organization_id) =plla.ship_to_organization_id
AND ph.vendor_id = pv.vendor_id
AND pl.po_line_id =rsl.po_line_id
AND ph.po_header_id = rsl.po_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.receipt_num IS NOT NULL
AND pod.code_combination_id = gcc.code_combination_id
AND plla.ship_to_organization_id = ood.organization_id
AND ph.org_id =hou.ORGANIZATION_ID
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
-- and hou.name='OU_ENVS_BHA_US'
--and to_char(ph.creation_date,'dd-mon-rrrr') >'01-Sep-2012' and ph.creation_date <= '25-oct-2012'
--AND to_date(TO_CHAR(ph.creation_date,'dd-mon-rrrr') ,'dd-mon-rrrr') BETWEEN to_date('01-Sep-2012','dd-mon-rrrr') AND to_date('25-oct-2012','dd-mon-rrrr')
AND ph.org_id=237 --103,235,237,221
AND to_date(TO_CHAR(rsh.creation_date,'dd-mon-rrrr') ,'dd-mon-rrrr') BETWEEN to_date('16-Nov-2012','dd-mon-rrrr') AND to_date('22-Nov-2012','dd-mon-rrrr')
--AND ph.segment1 IN('700021650')--,'650002070','700019829','650001439')--'600001773','700020617','700019794','650001615','700019830','700019829')
) x ,
(SELECT person_id,
full_name
FROM apps.per_all_people_f
WHERE sysdate BETWEEN effective_start_date AND effective_end_date
) y ,
(SELECT person_id,
full_name
FROM apps.per_all_people_f
WHERE sysdate BETWEEN effective_start_date AND effective_end_date
) z
WHERE x.preparer_id = y.person_id(+)
AND x.employee_id = z.person_id
ORDER BY po_creation_date,
po_number ,
po_line;
No comments:
Post a Comment