Projectwise PR to PO Details Report Query
Select MA.*,TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') SYS_DATE FROM
(SELECT MAIN.project_no,
MAIN.Project_name,
MAIN.PR_Number,
MAIN.PR_Creation_date,
MAIN.Item_Code,
MAIN.ITEM_DESCRIPTION,
MAIN.ORG_ID,
organization_id,
MAIN.PO_HEADER_ID,
MAIN.PO_line_ID,
MAIN.PO_Number,
MAIN.PO_Date,
MAIN.PO_Status, DECODE(MAIN.VENDOR_NAME,NULL,'ALL',MAIN.VENDOR_NAME,MAIN.VENDOR_NAME) VN,
MAIN.Vendor_Code,
MAIN.Vendor_name,
MAIN.UNIT_MEAS_LOOKUP_CODE,
MAIN.QUANTITY PR_QTY,
MAIN.PO_Quantity,
MAIN.QUANTITY_RECEIVED,
MAIN.QUANTITY_ACCEPTED,
MAIN.QUANTITY_REJECTED,
MAIN.QUANTITY_BILLED,
MAIN.Balance_Quantity,
MAIN.invoice_id,
MAIN.INVOICE_NUMBER,
MAIN.invoice_date,
MAIN.invoice_amount,
MAIN.INVOICE_DOC,
MAIN.INV_QTY,
MAIN.DOC_NO,
MAIN.payment_date,
MAIN.payment_amount
FROM
(
Select
(select SEGMENT1 from pa_projects_all where project_id = prd.project_id) Project_no,
(select NAME from pa_projects_all where project_id = prd.project_id) Project_name,
prh.segment1 pr_number,
prh.creation_date pr_creation_date,
(select REPLACE(REPLACE(REPLACE(REPLACE(msi.segment1,CHR(13),NULL),CHR(10),NULL),CHR(9),NULL),'"',null)
from mtl_system_items_b msi
where msi.inventory_item_id = prl.item_id AND msi.organization_id = poll.ship_to_organization_id) Item_Code,
prl.item_description,
prl.quantity,
poh.org_id,
poll.ship_to_organization_id organization_id,
poh.po_header_id,
pol.po_line_id,
poh.segment1 po_number,
poh.creation_date po_date,
DECODE(poh.authorization_status,NULL,'IN COMPLETE',poh.authorization_status) po_status,
(select segment1 from ap_suppliers where vendor_id=poh.vendor_id) Vendor_Code ,
(select vendor_name from ap_suppliers where vendor_id = poh.vendor_id) vendor_name,
pol.unit_meas_lookup_code,
poll.quantity po_quantity,
poll.quantity_received,
poll.quantity_accepted,
poll.quantity_rejected,
poll.quantity_billed,
--(poll.quantity - poll.QUANTITY_ACCEPTED) Balance_Quantity, comment by JG
(poll.quantity - decode(poll.quantity_accepted,0,poll.QUANTITY_BILLED,poll.quantity_accepted)) Balance_Quantity,
aia.invoice_id,
aia.invoice_num invoice_number,
aia.invoice_date invoice_date,
aia.invoice_amount invoice_amount,
aia.doc_sequence_value invoice_doc,
--(Select sum(quantity_invoiced) FROM ap_invoice_lines_all WHERE line_type_lookup_code ='ITEM'
-- AND org_id=aia.org_id AND invoice_id=aia.invoice_id AND PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID) INV_QTY,
invoice_qty INV_QTY,
(Select ac.doc_sequence_value FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id
AND aip.org_id = ac.org_id) doc_no,
(Select ac.check_date FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id
AND aip.org_id = ac.org_id) payment_date,
(Select ac.amount FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id
AND aip.org_id = ac.org_id) payment_amount
FROM
po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_req_distributions_all prd
,po_headers_all poh
,po_lines_all pol
,po_distributions_all pod
,po_line_locations_All poll
,(Select org_id,invoice_id,NVL(po_header_id,quick_po_header_id)po_header_id,invoice_num,invoice_date,invoice_amount,doc_sequence_value From ap_invoices_all) aia
,(Select org_id,invoice_id,po_header_id,po_line_id,sum(quantity_invoiced)invoice_qty From ap_invoice_lines_all Group by org_id,invoice_id,po_header_id,po_line_id) aila
,(Select org_id,invoice_id,check_id From apps.ap_invoice_payments_all) aip
WHERE
prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
and prd.distribution_id = pod.req_distribution_id
and poh.po_header_id = pod.po_header_id
and poh.po_header_id = pol.po_header_id
and poh.PO_HEADER_ID = poll.PO_HEADER_ID
and pol.PO_LINE_ID = pod.PO_LINE_ID
and pod.line_location_id = poll.line_location_id
and prh.authorization_status IN ('APPROVED','PRE-APPROVED')
AND aia.po_header_id(+) = poh.po_header_id
AND aia.org_id(+) = poh.org_id
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND pol.po_line_id = aila.po_line_id(+)
AND aia.invoice_id = aip.invoice_id
AND aia.org_id = aip.org_id
AND prd.project_id = NVL(:p_project_name,prd.project_id)
AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)
AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)
AND prl.item_id = NVL(:P_ITEM,prl.item_id)
UNION------------------------
SELECT
(select segment1 from pa_projects_all where project_id = prd.project_id) project_no,
(select name from pa_projects_all where project_id = prd.project_id) project_name,
prh.segment1 pr_number,
prh.creation_date pr_creation_date,
(select replace(replace(replace(replace(msi.segment1,chr(13),null),chr(10),null),chr(9),null),'"',null)
from mtl_system_items_b msi
where msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id
) item_code,
prl.item_description,
prl.quantity,
poh.org_id,
poll.ship_to_organization_id organization_id,
poh.po_header_id,
pol.po_line_id,
poh.segment1 po_number,
poh.creation_date po_date,
poh.authorization_status po_status,
(select segment1 from ap_suppliers where vendor_id=prl.vendor_id) vendor_code ,
(select vendor_name from ap_suppliers where vendor_id=prl.vendor_id) vendor_name,
pol.unit_meas_lookup_code,
poll.quantity po_quantity,
poll.quantity_received,
poll.quantity_accepted,
poll.quantity_rejected,
poll.quantity_billed,
(poll.quantity - poll.quantity_accepted) balance_quantity,
null invoice_id,
null invoice_number,
null invoice_date,
null invoice_amount,
null invoice_doc,
null inv_qty,
null doc_no,
null payment_date,
null payment_amount
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
po_line_locations_all poll,
po_lines_all pol,
po_headers_all poh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND poll.line_location_id(+) = prl.line_location_id
AND poll.po_header_id = poh.po_header_id(+)
AND poll.po_line_id = pol.po_line_id(+)
AND prh.authorization_status = 'APPROVED'
AND poll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND NVL (prl.cancel_flag, 'N') <> 'Y'
AND prd.project_id = NVL(:p_project_name,prd.project_id)
AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)
AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)
AND prl.item_id = NVL(:P_ITEM,prl.item_id)
)MAIN
WHERE 1=1--MAIN.PROJECT_NAME = DECODE(:P_PROJECT_NAME,NULL,PROJECT_NAME,:P_PROJECT_NAME)
--AND MAIN.PR_CREATION_DATE BETWEEN NVL(:P_START_DATE,PR_CREATION_DATE) AND NVL(:P_END_DATE,PR_CREATION_DATE)
--AND MAIN.PR_NUMBER = NVL(:P_REQ_NUM,MAIN.PR_NUMBER)
--AND MAIN.Item_Code = NVL(:P_ITEM,MAIN.Item_Code)
ORDER BY 1, 3,9,10) MA
WHERE MA.VN =NVL(:P_VENDOR_NAME,MA.VN)