JA India - TDS Certificates (1)

SELECT
     api2.invoice_date
,     frmd.tds_invoice_amt
,     frmd.tds_tax_rate
,     apip.accounting_date
,     frmd.invoice_payment_id
,     api1.invoice_id
,     apc.bank_account_name 
,     frmd.certificate_num certificate_id2
,jatp.check_number CHQ1
,     jatp.check_number CHQ 
,     jatp.check_deposit_date "Check_Deposit_Date"
,     jatp.bank_name "Check_Deposit_Bank"
,     jatp.challan_no "Check_Challan_No"
,    apc.bank_account_id
,     frmd.invoice_amount INVOICE_AMT
,     frmd.tds_invoice_id, 
	JA_JAINITCR_XMLP_PKG.bank_branchformula(:C_CHQ, frmd.tds_invoice_id, :TDS_ID) bank_branch_name, 
	JA_JAINITCR_XMLP_PKG.cf_check_dep_dateformula(jatp.check_number) CF_check_dep_date, 
	JA_JAINITCR_XMLP_PKG.cf_tds_amtformula(frmd.tds_invoice_id, frmd.tds_invoice_amt) CF_tds_amt, 
	JA_JAINITCR_XMLP_PKG.cf_bsr_codeformula(apc.bank_account_id) CF_BSR_code,
	JA_JAINITCR_XMLP_PKG.CP_cess_amt_p CP_cess_amt,
	JA_JAINITCR_XMLP_PKG.CP_surcharge_amt_p CP_surcharge_amt,
	JA_JAINITCR_XMLP_PKG.CP_sh_cess_amt_p CP_sh_cess_amt,
	'X' dummy
FROM 
      (   select invoice_id,
          certificate_num,
          fin_yr,
          tax_authority_id,
          line_num,
          org_id,
          max(org_tan_num)      org_tan_num,
          max(tds_invoice_id)   tds_invoice_id,
          max(tds_inv_date)     tds_inv_date,
          max(tds_tax_rate)     tds_tax_rate,
          max(invoice_payment_id) invoice_payment_id,
          sum(taxable_basis)    invoice_amount,
          sum(TDS_INVOICE_AMT)       tds_invoice_amt
        from jai_ap_tds_f16_dtls_v frmd
        group by tax_authority_id, fin_yr, certificate_num, line_num, invoice_id,org_id
    ) frmd 
,     ap_invoices_all API1
,     ap_invoices_all API2
,     ap_invoice_payments_all APIP
,     ap_checks_all       apc 
,     jai_ap_tds_payments JATP 
WHERE  
      api1.invoice_id                = frmd.invoice_id 
AND   apip.invoice_payment_id = frmd.invoice_payment_id 
AND   api2.invoice_id                = frmd.tds_invoice_id 
AND   apc.check_id (+)	= apip.check_id
AND   apc.check_id      = jatp.check_id  
AND frmd.tds_inv_date = api2.invoice_date
AND frmd.fin_yr = :P_FIN_YEAR
AND frmd.Org_tan_num = :P_ORG_TAN_NUM
AND frmd.org_id = nvl(:p_organization_id, frmd.org_id ) 
AND frmd.tds_invoice_amt > 0 
 and frmd.certificate_num=:certificate_id
ORDER BY 1 ASC,6 ASC,8 ASC,15 ASC,7 ASC,2 ASC,14 ASC,16 ASC,9 ASC , api1.invoice_id