Oracle Blogs

 To access the previous row in sql LAG function , subract from previous row or add or multiply or divide example : select *,sal,        sal - coalesce(lag(sal) over (order by EMPNO), 0) as diff from emp;

Below is a XSL example used to style or format a xml. <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">      <xsl:output method="xml" indent="yes"/>   <!--select="DATA_DS/INVOICE_NO" <== these are invoice field paths change them accordingly -->      <xsl:template match="/">     <Invoice>       <InvoiceNumber><xsl:value-of select="DATA_DS/INVOICE_NO"/></InvoiceNumber>              <InvoiceDetails>         <xsl:for-each select="DATA_DS/G_1">           <Detail>             <ProfileID><xsl:value-of select="PROFILEID"/></ProfileID>             <UBLVersionID><xsl:value-of select="UBLVERSIONID"/></UBLVersionID>   ...

Q1 for ar customer transaction lines  SELECT  distinct hca.account_number client_number, hp.party_name client_name, rctt.name TRX_TYPE1, rct.trx_number trx_number, rct.PURCHASE_ORDER, TO_CHAR(rct.creation_date,'DD-MON-YY') TRX_DATE, TO_CHAR(rctg.gl_date,'DD-MON-YY') GL_DATE, apsa.TAX_REMAINING VAT_AMOUNT, ROUND(NVL(XAL.ACCOUNTED_DR,0),2) DEBIT, ROUND(NVL(ACCOUNTED_CR,0),2)   CREDIT , ROUND((NVL(XAL.ACCOUNTED_DR,0)),2) - ROUND((NVL(ACCOUNTED_CR,0)),2) balance, xal.CURRENCY_CODE CURRENCY, rtt.name payment_term, TO_CHAR(apsa.due_date,'DD-MON-YY') DUE_DATE, xal.description DESCRIPTION FROM  XLA.XLA_AE_LINES  XAL, XLA.XLA_AE_HEADERS XAH, XLA.XLA_TRANSACTION_ENTITIES XTE, ra_customer_trx_all rct, ra_customer_trx_lines_all rcl, RA_CUST_TRX_LINE_GL_DIST_ALL rctg, ra_cust_trx_types_all rctt, ra_terms_tl rtt, ar_payment_schedules_all apsa, hz_cust_accounts hca, hz_parties hp WHERE 1=1 and rct.customer_trx_id = rcl.customer_trx_id and rct.CUST_TRX_TYPE_ID = rctt.CUST_...

 <?xml version="1.0" encoding="WINDOWS-1252" ?> <dataTemplate name ="APOPENINGBALANCE" description="AP OPENING BALANCE REPORT " Version="1.0" > <parameters> <parameter name="FROM_DATE" datatype="VARCHAR2"/> <parameter name="TO_DATE" datatype="VARCHAR2"/> </parameters>  <dataQuery>  <sqlStatement name="Q1"> <![CDATA[ SELECT  ROUND(SUM(NVL(XAL.ACCOUNTED_DR,0)),2) OPENING_DEBIT, ROUND(SUM(NVL(ACCOUNTED_CR,0)),2)   OPENING_CREDIT , ROUND(SUM(NVL(XAL.ACCOUNTED_DR,0)),2) - ROUND(SUM(NVL(ACCOUNTED_CR,0)),2)   OPENING_BALANCE FROM  XLA.XLA_AE_LINES  XAL, XLA.XLA_AE_HEADERS XAH, XLA.XLA_TRANSACTION_ENTITIES XTE, AP_INVOICES_ALL AI WHERE 1=1 AND XTE.SOURCE_ID_INT_1(+)=AI.INVOICE_ID AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID AND XAH.APPLICATION_ID = XAL.APPLICATION_ID AND XAL.APPLICATION_ID = XTE.APPLICATION_ID AND XAH.ENTITY_ID = XTE.ENTITY_ID AND AI...