JA India - Generate Suggestive Supplementary Transactions - 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_JAINSTPR_XMLP_PKG.serial_fformula() serial_f,
	JA_JAINSTPR_XMLP_PKG.Serial_Number_p Serial_Number, 
	JA_JAINSTPR_XMLP_PKG.excise_taxformula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Excise, 
	JA_JAINSTPR_XMLP_PKG.cf_5formula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Sales, 
	JA_JAINSTPR_XMLP_PKG.cstformula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Cst, 
	JA_JAINSTPR_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      a.customer_site = loc.site_use_id      
and      d.customer_trx_id = b.customer_trx_id
and      a.process_date is null
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