JA India - Generate Suggestive Supplementary Transactions - draft
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Generate Suggestive Supplementary Transactions (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSTPR_XML
DB package: JA_JAINSTPR_XMLP_PKG
Source: India - Generate Suggestive Supplementary Transactions (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSTPR_XML
DB package: JA_JAINSTPR_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_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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Batch ID | LOV Oracle | ||
Generate Trace | LOV Oracle | ||
Detail Trace | LOV Oracle |