Reports2017-11-18T12:27:27+00:00

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
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
Detail Trace
 
LOV Oracle
Generate Trace
 
LOV Oracle
Batch ID
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept