JA India - ST Forms Customer - draft

SELECT
      hdr.party_id                                      ,
      hdr.party_site_id                                 ,
      hdr.org_id                                        ,
      hdr.form_type                                     ,
      dtl.header_id                                     ,
      dtl.tax_id                                        ,
      dtl.tax_line_no                                   ,
      dtl.invoice_id                                    ,
      dtl.tax_target_amount                             ,
      dtl.order_flag                                    ,
      nvl(to_char(dtl.order_number),'N/A') ORDER_NUMBER ,
      dtl.trx_number                                    ,
      dtl.organization_id                               ,
      dtl.location_id                                   ,
      jtc.tax_rate                                      ,
      jtc.tax_type                                      ,
      ja_hr_org. st_reg_no                              ,
      ja_hr_org.cst_reg_no                              ,
      loc.address_line_1 || ',' || loc.address_line_2 || ',' || loc.address_line_3 LOC_ADDRESS ,
      loc.country                         COUNTRY       ,
      j_ra_ctl.excise_invoice_no                        ,
      j_ra_ctl.excise_invoice_date                      ,
      hzca.account_number                 CUSTOMER_NUM  ,
      hzca.cust_account_id                CUSTOMER_ID   ,
      hzca.account_name                   CUSTOMER_NAME ,
      jcuad.st_reg_no                     CUST_ST_REG_NO,
      jcuad.cst_reg_no                    CUST_CST_REG_NO   ,
      jcttl.tax_amount                    TAX_AMOUNT     ,
      hr_org.name                         ORGN_NAME      ,
      rac.trx_date                        INVOICE_DATE   ,
      j_ra_ctl.inventory_item_id          ITEM_ID        ,
      mtl.segment1                        ITEM_NAME      ,
      hzcsu.location                      PARTY_SITE_NAME   ,
      match.form_id                       FORM_ID           ,
      dtl.matched_amount                  MATCHED_AMOUNT    ,
      match.matched_amount                FORM_MATCHED_AMOUNT, 
	JA_JAINSTCR_XMLP_PKG.cf_order_dateformula(dtl.header_id, dtl.order_flag) CF_ORDER_DATE, 
	JA_JAINSTCR_XMLP_PKG.cf_invoice_amountformula(dtl.invoice_id) CF_Invoice_amount, 
	JA_JAINSTCR_XMLP_PKG.cf_form_numberformula(match.form_id) cf_form_number, 
	JA_JAINSTCR_XMLP_PKG.cf_form_dateformula(match.form_id) cf_form_date
FROM
      JAI_CMN_STFORM_HDRS_ALL     HDR      ,
      JAI_CMN_ST_FORM_DTLS        DTL      ,
      JAI_CMN_TAXES_ALL           JTC      ,
      JAI_CMN_INVENTORY_ORGS      JA_HR_ORG,
      HR_ORGANIZATION_UNITS       HR_ORG   ,
      HR_LOCATIONS                LOC      ,
      JAI_AR_TRX_LINES            J_RA_CTL ,
      HZ_PARTIES                  HZP      ,
      HZ_PARTY_SITES              HZPS     ,
      HZ_CUST_ACCOUNTS            HZCA     ,
      HZ_CUST_ACCT_SITES_ALL      HZCAS    ,
      HZ_CUST_SITE_USES_ALL       HZCSU    ,
      JAI_CMN_CUS_ADDRESSES       JCUAD    ,
      JAI_AR_TRX_TAX_LINES        JCTTL    ,
      RA_CUSTOMER_TRX_ALL         RAC      ,
      MTL_SYSTEM_ITEMS            MTL      ,
      JAI_CMN_ST_MATCH_DTLS       MATCH
WHERE hzp.party_id = hzca.party_id
  AND hzps.party_id = hzp.party_id                      
  AND hzps.party_site_id  =  hzcas.party_site_id        
  AND hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
  AND hzca.cust_account_id = hzcas.cust_account_id
  AND jcuad.customer_id = hzca.cust_account_id
  AND jcuad.address_id = hzcsu.cust_acct_site_id
  AND hzcsu.site_use_id = hdr.party_site_id
  AND hdr.st_hdr_id = dtl.st_hdr_id
  AND jtc.tax_id=dtl.tax_id
  AND dtl.issue_receipt_flag = 'R'
  AND hdr.party_type_flag = 'C'
  AND hdr.party_id = jcuad.customer_id
  AND dtl.invoice_id= j_ra_ctl.customer_trx_id
  AND j_ra_ctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id
  AND j_ra_ctl.customer_trx_line_id = dtl.line_id
  AND jtc.tax_id = jcttl.tax_id
  AND hr_org.organization_id =ja_hr_org.organization_id
  AND loc.location_id = ja_hr_org.location_id
  AND hr_org.organization_id = dtl.organization_id
  AND loc.location_id = dtl.location_id
  AND rac.customer_trx_id = j_ra_ctl.customer_trx_id
  AND mtl.inventory_item_id = j_ra_ctl.inventory_item_id
  AND mtl.organization_id = dtl.organization_id
  AND match.st_dtl_id (+)= dtl.st_dtl_id
  AND trunc(rac.trx_date) between :p_from_date and :p_to_date
  &p_where_clause
  ORDER BY 13 ASC,14 ASC,19 ASC,20 ASC,17 ASC,18 ASC,29 ASC,3 ASC,23 ASC,24 ASC,25 ASC,26 ASC,27 ASC,33 ASC,2 ASC,1 ASC,5 ASC,10 ASC,11 ASC,12 ASC,8 ASC,30 ASC,21 ASC,22 ASC,32 ASC,4 ASC,31 ASC,35 ASC,6 ASC,7 ASC,9 ASC,15 ASC,16 ASC,28 ASC , hr_org.name , hzca.cust_account_id , jcuad.st_reg_no , hdr.form_type , rac.trx_date