Sunday, March 20, 2016

Oracle Receivable Query for AR Sales TAX

BEGIN
  FND_CLIENT_INFO.SET_ORG_CONTEXT(204);
END;

SELECT
 asp.tax_registration_number,
 cta.trx_number,
 cta.gd_gl_date,
 cta.rac_ship_to_customer_name,
 cta.ctt_type_name,
 SELECT sum(RCTLA.NET_EXTENDED_AMOUNT)
 FROM RA_CUSTOMER_TRX_LINES_V RCTLA
 HERE RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
  AND RCTLA.LINE_TYPE in ('LINE','TAX')  ) total,
 SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE
 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,
      AR_VAT_TAX_ALL_B AVTAB
 WHERE  AVTAB.TAX_CODE='VAT20'
 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID
 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
 AND    RCTLA.LINE_TYPE='LINE')VAT20_AMOUNT,
 (SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE*AVTAB.TAX_RATE/100
 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,
      AR_VAT_TAX_ALL_B AVTAB
 WHERE  AVTAB.TAX_CODE='VAT20'
 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID
 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
 AND    RCTLA.LINE_TYPE='LINE') VAT20,
 (SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE
 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,
      AR_VAT_TAX_ALL_B AVTAB
 WHERE  AVTAB.TAX_CODE='VAT'
 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID
 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
 AND    RCTLA.LINE_TYPE='LINE') VAT0_AMOUNT,
 (SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE*AVTAB.TAX_RATE/100
 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,
      AR_VAT_TAX_ALL_B AVTAB
 WHERE  AVTAB.TAX_CODE='VAT'
 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID
 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
 AND    RCTLA.LINE_TYPE='LINE') VAT0,
 (SELECT SUM(RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE)
 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,
      AR_VAT_TAX_ALL_B AVTAB
 WHERE  AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID
 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
 AND    RCTLA.LINE_TYPE='LINE') LINE_TOTAL,
 (SELECT SUM(RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE*AVTAB.TAX_RATE/100)
 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,
      AR_VAT_TAX_ALL_B AVTAB
 WHERE  AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID
 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
 AND    RCTLA.LINE_TYPE='LINE') VAT_TOTAL

FROM ra_customer_trx_v rcta,
     ar_system_parameters asp
WHERE rcta.trx_number='11794'

AND   rcta.of_organization_id=asp.org_id

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect