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
Source: India - Supplementary Transaction Details Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSITR_XML
DB package: JA_JAINSITR_XMLP_PKG
Run
JA India - Supplementary Transaction Details - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Customer |
|
LOV Oracle | |
Customer Site |
|
LOV Oracle | |
Invoice NO |
|
LOV Oracle | |
Processed Date |
|
LOV Oracle | |
Supplementary Type |
|
LOV Oracle |