JA India - Supplementary Transaction Details - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Supplementary Transaction Details Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSITR_XML
DB package: JA_JAINSITR_XMLP_PKG
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
Parameter Name SQL text Validation
Supplementary Type
 
LOV Oracle
Processed Date
 
LOV Oracle
Invoice NO
 
LOV Oracle
Customer Site
 
LOV Oracle
Customer
 
LOV Oracle