/*******************************************************************
*PURPOSE: SQL Query to get PO Expense TAT Report( AP Report ) *
*AUTHOR: Samba Siva Chowdary ********************************************************************/
SELECT DISTINCT req.requisition,
req.ipr_approval_date,
req.ipr_creation_date,
req.ipr_number_of_days,
po.lpo_number,
po.vendor_name vendor_name,
po.lpo_creation_date,
po.lpo_approval_date,
po.lpo_no_of_days,
rsl.grn,
rsl.grn_receipt_date,
rsl.grn_approval_date,
rsl.grn_number_of_days,
ap.invoice_id,
ap.invoice_num,
ap.voucher_num,
ap.invoice_amount,
ap.invoice_received_date,
ap.invoice_currency_code,
ap.description,
ap.approval_status,
ap.invoice_nature,
ap.invoice_type,
ap.date_created,
ap.created_by,
ap.validated_by,
ap.update_date,
ap.last_update_date,
ap.attachment,
ap.date_paid,
ap.days_for_payment
FROM (SELECT prha.requisition_header_id,
prha.requisition_number requisition,
prda.distribution_id,
prha.approved_date ipr_approval_date,
prha.creation_date ipr_creation_date,
( prha.creation_date - prha.approved_date ) IPR_Number_of_Days
FROM por_requisition_headers_all PRHA,
fun_all_business_units_v FABUV,
por_requisition_lines_all PRLA,
por_req_distributions_all PRDA
WHERE 1 = 1
AND prha.req_bu_id = fabuv.bu_id
AND prha.requisition_header_id = prla.requisition_header_id
AND prda.requisition_line_id = prla.requisition_line_id) req,
(SELECT hou.name LPO_PRC_BU_NAME,
pha.prc_bu_id LPO_PRC_BU_ID,
pha.po_header_id,
( pha.creation_date - pha.approved_date ) lpo_no_of_days,
pha.approved_date lpo_approval_date,
pla.po_line_id,
plla.line_location_id,
pda.req_distribution_id,
pha.segment1 lpo_number,
pzv.vendor_name,
pha.currency_code currency,
pzv.vendor_name supplier,
To_char(pha.creation_date, 'MM/DD/RRRR') lpo_creation_date,
pla.quantity quantity,
pla.line_num purchase_order_line,
plla.shipment_num purchase_order_schedule
,
pla.item_id,
plla.ship_to_organization_id,
( gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
|| '.'
|| gcc.segment9
|| '.'
|| gcc.segment10 ) charge_account
FROM po_line_locations_all plla,
poz_suppliers_v pzv,
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
gl_code_combinations gcc,
hr_operating_units hou
WHERE 1 = 1
AND pha.prc_bu_id = hou.organization_id
AND pha.vendor_id = pzv.vendor_id
AND pda.po_header_id = pha.po_header_id
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pda.po_line_id
AND plla.po_line_id = pla.po_line_id
AND gcc.code_combination_id = pda.code_combination_id
AND pha.document_status IN ( 'OPEN', 'CLOSED', 'FINALLY CLOSED',
'CLOSED FOR RECEIVING',
'CLOSED FOR INVOICING' )) PO,
(SELECT rsl.shipment_line_id,
rsl.po_line_location_id,
rsl.to_organization_id,
rct.transaction_id,
rsh.receipt_num grn,
rsh.shipment_header_id,
rsh.creation_date grn_receipt_date,
rsh.creation_date grn_approval_date,
rsh.creation_date - rsh.creation_date grn_number_of_days,
fcd rct.po_header_id,
rct.po_line_id,
rct.po_distribution_id,
rct.transaction_date,
rsl.quantity_received received,
rsl.quantity_returned returned,
( rsl.quantity_received - ( rct.quantity ) ) corrected
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rct
WHERE 1 = 1
AND rct.shipment_line_id = rsl.shipment_line_id
AND rct.transaction_type = 'RECEIVE'
AND rsl.shipment_header_id = rsh.shipment_header_id) rsl,
(SELECT po_header_id,
invoice_id,
invoice_num,
voucher_num,
invoice_amount,
invoice_received_date,
invoice_currency_code,
description,
approval_status,
attribute1 Invoice_nature,
attribute2 Invoice_type,
creation_date date_created,
created_by,
created_by Validated_By,
last_update_date Update_Date,
last_update_date last_Update_Date,
CASE
WHEN (SELECT invoice_id
FROM fnd_attached_documents fad,
fnd_documents d
WHERE 1 = 1
AND fad.pk1_value = aia.invoice_id
AND fad.entity_name = 'AP_INVOICES'
AND fad.document_id = d.document_id)IS NULL THEN
'N'
ELSE 'Y'
END Attachment,
(SELECT creation_date
FROM ap_invoice_payments_all
WHERE invoice_id = aia.invoice_id
AND ROWNUM = 1) Date_Paid,
( aia.creation_date - (SELECT creation_date
FROM ap_invoice_payments_all
WHERE invoice_id = aia.invoice_id
AND ROWNUM = 1) ) days_for_payment
FROM ap_invoices_all aia) ap
WHERE 1 = 1
--po to req
AND po.req_distribution_id = req.distribution_id(+)
--Rct
AND rsl.po_header_id(+) = po.po_header_id
AND rsl.po_line_id (+) = po.po_line_id
AND rsl.po_line_location_id (+) = po.line_location_id
--INV AP
AND po.po_header_id = ap.po_header_id(+)
AND ( Coalesce(NULL, :P_PO_NUMBER) IS NULL
OR po.lpo_number IN ( :P_PO_NUMBER ) )
AND ( Coalesce(NULL, :P_PR_NUMBER) IS NULL
OR req.requisition IN ( :P_PR_NUMBER ) )
AND ( Coalesce(NULL, :P_INV_NUMBER) IS NULL
OR ap.invoice_num IN ( :P_INV_NUMBER ) )
AND ( Coalesce(NULL, :P_SUPPLIER) IS NULL
OR po.vendor_name IN ( :P_SUPPLIER ) )
AND ( Coalesce(NULL, :P_ENTITY) IS NULL
OR po.lpo_prc_bu_id IN ( :P_ENTITY ) )
No comments:
Post a Comment