JA India - Total Sales - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Total Sales Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINTSLS_XML
DB package: JA_JAINTSLS_XMLP_PKG 
SELECT  
  hr_org.name  NAME
, hr_org.organization_id
, jain_hr_org.st_reg_no ST_NO
, jain_hr_org.cst_reg_no CST_NO
, jain_hr_org.vat_Reg_no VAT_REG_NO
, NVL(hr_org.internal_address_line,'NIL') INTADRS
, hr_loc.address_line_1 ||','||
  hr_loc.address_line_2 ||','||
  hr_loc.address_line_3 ||','||
  hr_loc.town_or_city  ADLIN1
, NVL(hr_loc.country,'NIL') COUNTRY
, hr_org.location_id LOCID
, hr_loc.location_id 
, cutx.trx_number
, cutx.trx_date
, cutxl.customer_trx_id  
, cutxl.customer_trx_line_id
, cutxl.sales_order_line order_line_id
, jcuad.st_reg_no
, jcuad.cst_reg_no
,jcuad.vat_Reg_no cust_vat_Reg_no	 
, cutxl.description
, rcu.party_name customer_name
, jitc.tax_type
, nvl(jrctaxl.tax_rate,0) TAX_RATE
, sum(jrctaxl.func_tax_amount)           
, jcutx.organization_id
, NVL(cutxl.extended_amount,0) line_amount  , 
sum(jrctl.total_amount)  total_amount
, jcutx.location_id LOC, 
	JA_JAINTSLS_XMLP_PKG.cf_1formula0013(cutxl.customer_trx_id) CF_GROSS_INV_AMT_1, 
	JA_JAINTSLS_XMLP_PKG.cf_trx_number_date_1formula(cutx.trx_number, cutx.trx_date) CF_TRX_NUMBER_DATE_1, 
	JA_JAINTSLS_XMLP_PKG.cf_1formula(NVL ( cutxl.extended_amount , 0 )) CF_LINE_AMOUNT_1, 
	JA_JAINTSLS_XMLP_PKG.cf_1formula0004(cutxl.customer_trx_line_id) CF_EXCISE_1, 
	JA_JAINTSLS_XMLP_PKG.cf_2formula(cutxl.customer_trx_line_id) CF_ADDL_OTHER_TAXES_1, 
	JA_JAINTSLS_XMLP_PKG.cf_vat_taxes_1(cutxl.customer_trx_line_id) CF_VAT_TAXES_1, 
	JA_JAINTSLS_XMLP_PKG.cf_1formula0005(cutxl.customer_trx_id, cutxl.sales_order_line, NVL ( cutxl.extended_amount , 0 )) CF_LINE_AMOUNT
FROM
  JAI_AR_TRXS  JCUTX
, ra_customer_trx_all CUTX
, hz_parties RCU, hz_cust_accounts hzca
, ra_customer_trx_lines_all CUTXL
, JAI_AR_TRX_LINES    JRCTL
, JAI_CMN_CUS_ADDRESSES JCUAD
, JAI_AR_TRX_TAX_LINES JRCTAXL
, JAI_CMN_TAXES_ALL JITC
, hr_organization_units HR_ORG	
, JAI_CMN_INVENTORY_ORGS JAIN_HR_ORG 
, hr_locations HR_LOC
, hz_cust_site_uses_all site
WHERE
    jcutx.organization_id  =:p_organization_id
AND (:p_location_id IS NULL OR jcutx.location_id  = :p_location_id)
AND cutx.customer_trx_id = cutxl.customer_trx_id 
AND cutx.customer_trx_id=jrctl.customer_trx_id
AND cutx.customer_trx_id=jcutx.customer_trx_id
AND jrctl.customer_trx_id=jcutx.customer_trx_id 
AND cutxl.customer_trx_line_id=jrctl.customer_trx_LINE_ID
AND jrctaxl.link_to_cust_trx_line_id =  cutxl.customer_trx_line_id
AND jrctaxl.link_to_cust_trx_line_id  =   jrctl.customer_trx_LINE_ID
AND cutx.org_id=cutxl.org_id
AND jitc.tax_id = jrctaxl.tax_id
AND (jitc.tax_type IN ('Sales Tax', 'CST')
OR upper(jitc.tax_type) IN  
    ( select tax_type
      from jai_regime_tax_types_v
      where regime_code = 'VAT'
    )
)
AND (cutx.complete_flag = 'Y'  OR cutx.complete_flag = 'y')
AND ( (:P_START_DATE IS NULL AND cutx.trx_date <= :LP_END_DATE) OR ( cutx.trx_date BETWEEN :P_START_DATE AND :LP_END_DATE) )
AND rcu.party_id = hzca.party_id
AND hzca.cust_account_id = cutx.bill_to_customer_id          
AND jcuad.customer_id (+) = cutx.bill_to_customer_id       
AND jain_hr_org.organization_id = jcutx.organization_id 
AND jain_hr_org.location_id = jcutx.location_id
AND hr_org.organization_id = jcutx.organization_id
AND hr_loc.location_id  = jcutx.location_id
AND jcuad.address_id = site.cust_acct_site_id
AND site.site_use_id = cutx.bill_to_site_use_id           
GROUP BY
CUTXL.CUSTOMER_TRX_ID,CUTXL.CUSTOMER_TRX_LINE_ID,
  hr_org.name
, hr_org.organization_id
, jain_hr_org.st_reg_no
, jain_hr_org.cst_reg_no
,jain_hr_org.vat_Reg_no
, NVL(hr_org.internal_address_line,'NIL')
, hr_loc.address_line_1 ||','||
  hr_loc.address_line_2 ||','||
  hr_loc.address_line_3 ||','||
  hr_loc.town_or_city
, NVL(hr_loc.country,'NIL')
, hr_org.location_id
, hr_loc.location_id 
, cutx.trx_number
, cutx.trx_date
, jcuad.st_reg_no
, jcuad.cst_reg_no
,jcuad.vat_reg_no
, cutxl.description
, rcu.party_name
, jrctaxl.tax_rate
, jcutx.organization_id
, cutxl.extended_amount
,jrctl.total_amount
, jcutx.location_id 
, jitc.tax_type 
,cutxl.sales_order_line
ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,6 ASC,7 ASC,8 ASC,9 ASC,5 ASC,24 ASC,10 ASC,27 ASC,11 ASC,12 ASC,16 ASC,20 ASC,18 ASC,17 ASC,19 ASC,26 ASC,25 ASC,14 ASC , cutx.trx_date
Parameter Name SQL text Validation
Organization 
 
 LOV Oracle
Location 
 
 LOV Oracle
Start Date 
 
 Date
End date 
 
 Date
Report Style 
 
 LOV Oracle
Download
Related Blitz Reports
JA India - ST Forms Customer - draft JA India - Commercial Invoice - draft JA India - Open Transactions Report for Prior-GST (XML Publisher) JA India - ARE1 Report- Not Supported: Reserved For Future Use JA India - AR3A Status - draft AR Customer Upload JA India - Generate Suggestive Supplementary Transactions - draft JA India - Supplementary Transaction Details - draft JA India - RG 23 D - draft
Blitz Report™

Blitz Report™ provides multiple benefits: