SELECT B.TRX_NUMBER,
A.TRX_DATE,
a.supp_inv_type,
d.total_amount Amount,
C.PARTY_NUMBER CUSTOMER_NUMBER,
C.PARTY_NAME CUSTOMER_NAME,
loc.location,
DECODE(SUPP_INV_TYPE,'DB','Debit Memo','CR','Credit Memo','SI','Supplementary Invoice'),
A.CUSTOMER_TRX_ID,
( (NVL(NEW_NET_SALES_AMT,0) -NVL( EXIST_NET_SALES_AMT,0)) +
(NVL(NEW_EXCISE_AMT,0) - NVL(exist_EXCISE_AMT,0)) + (NVL(NEW_OTHER_AMT,0) - NVL(exist_OTHER_AMT,0))) Total,
(NVL(NEW_NET_SALES_AMT,0) - NVL(EXIST_NET_SALES_AMT,0)) New_Sales,
JA_JAINSITR_XMLP_PKG.serial_fformula() serial_f,
JA_JAINSITR_XMLP_PKG.Serial_Number_p Serial_Number,
JA_JAINSITR_XMLP_PKG.excise_taxformula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Excise,
JA_JAINSITR_XMLP_PKG.cf_5formula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Sales,
JA_JAINSITR_XMLP_PKG.cstformula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Cst,
JA_JAINSITR_XMLP_PKG.others_formula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Others
FROM JAI_AR_SUP_HDRS_ALL A,
RA_CUSTOMER_TRX_ALL B,
HZ_PARTIES C, HZ_CUST_ACCOUNTS HZCA,
JAI_AR_TRXS d,
HZ_CUST_SITE_USES_ALL loc
WHERE B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
AND C.PARTY_ID = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID = A.CUSTOMER_ID
and HZCA.CUST_ACCOUNT_ID = NVL(:CUSTOMERS,HZCA.CUST_ACCOUNT_ID )
and a.customer_site = loc.site_use_id
AND trunc(A.PROCESS_DATE) =trunc( NVL(:PROCESS_DATE,A.PROCESS_DATE))
AND A.SUPP_INV_TYPE = NVL(decode(:SUPPLEMENTARY_TYPE,'Debit Memo','DB','Credit Memo','CR','Supplementary Inv','SI'),A.SUPP_INV_TYPE)
AND B.TRX_NUMBER = NVL(:INVOICE_NO,B.TRX_NUMBER)
and d.customer_trx_id = b.customer_trx_id
and a.customer_site = nvl(:site_use_id,a.customer_site)
--order by c.party_name ASC, a.trx_Date DESC
--ORDER BY 5 ASC,6 ASC,7 ASC,2 DESC,1 ASC,4 ASC , c.party_name ASC , a.trx_Date DESC
order by 5,6,7,2 desc, 1,4,8,11,10,9,3 |