JA India - Supplementary Transaction Details - draft

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