Wednesday, August 10, 2016

Invoice amounts in Receivables

QUERY FOR GETTING THE 2ND MAXIMUM INVOICE AMOUNT
===============================================
select max(amt) secondmax  from (select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc)
where amt <
(select max(amt) a from (select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc));

QUERY FOR GETTING THE MAXIMUM  INVOICE AMOUNT
=============================================
(select max(amt) a from (select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc));


QUERY FOR GETTING THE MAXIMUM INVOICE AMOUNT AND ALL  IN DESCENDING ORDER
============================================================
select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc;

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...