SELECT TO_CHAR (trx_date, 'DD-MON-YYYY') ar_posting_date,
TO_CHAR (jatt.excise_invoice_date, 'DD-MON-YYYY') excise_invoice_date,
rcta.trx_number ar_trx_number, flv.meaning document_type,
jatt.excise_invoice_no, hca.account_number cust_bill_to,
hp.party_name cust_name, rctla.extended_amount basic_amt,
(SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'EXCISE'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
excise_amt, (SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt,
jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'CST'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
sales_tax_amt, ( (SELECT NVL (SUM (extended_amount), 0)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
- (SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'TCS'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
) tcs_base_amt,
(SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'TCS'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
tcs_amt,
(SELECT NVL (SUM (extended_amount), 0)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id) tot_inv_amt,
(SELECT pan_no
FROM jai_cmn_cus_addresses
WHERE customer_id = rcta.bill_to_customer_id
AND ROWNUM = 1) party_pan_no,
NULL challan_no,
(SELECT tax_descr
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'TCS'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)tax_desc,
NULL tcs_pay_voucher_no
FROM ra_customer_trx_all rcta, ra_cust_trx_types_all rctt,
fnd_lookup_values flv, ra_customer_trx_lines_all rctla,
jai_ar_trx_lines jatt, hz_cust_accounts hca,
hz_parties hp
WHERE rcta.cust_trx_type_id = rctt.cust_trx_type_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND flv.lookup_type = 'TRX TYPES'
AND rctt.TYPE = flv.lookup_code
AND rctla.line_type = 'LINE'
AND jatt.customer_trx_id = rcta.customer_trx_id
AND jatt.customer_trx_line_id = rctla.customer_trx_line_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND jatt.excise_invoice_no IS NOT NULL
AND TO_CHAR (trx_date, 'DD-MON-YYYY') BETWEEN NVL (TO_CHAR (:p_from_date,
'DD-MON-YYYY'
),
TO_DATE (SYSDATE,
'DD-MON-YYYY'
)
) AND NVL (TO_CHAR (:p_to_date, 'DD-MON-YYYY' ), TO_DATE (SYSDATE, 'DD-MON-YYYY' ) )
AND hca.cust_account_id = NVL (:p_cust_name, hca.cust_account_id)
TO_CHAR (jatt.excise_invoice_date, 'DD-MON-YYYY') excise_invoice_date,
rcta.trx_number ar_trx_number, flv.meaning document_type,
jatt.excise_invoice_no, hca.account_number cust_bill_to,
hp.party_name cust_name, rctla.extended_amount basic_amt,
(SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'EXCISE'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
excise_amt, (SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt,
jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'CST'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
sales_tax_amt, ( (SELECT NVL (SUM (extended_amount), 0)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
- (SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'TCS'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
) tcs_base_amt,
(SELECT NVL (jattt.tax_amount, 0)
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'TCS'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
tcs_amt,
(SELECT NVL (SUM (extended_amount), 0)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id) tot_inv_amt,
(SELECT pan_no
FROM jai_cmn_cus_addresses
WHERE customer_id = rcta.bill_to_customer_id
AND ROWNUM = 1) party_pan_no,
NULL challan_no,
(SELECT tax_descr
FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
WHERE jattt.tax_id = jcta.tax_id
AND UPPER (jcta.tax_type) = 'TCS'
AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)tax_desc,
NULL tcs_pay_voucher_no
FROM ra_customer_trx_all rcta, ra_cust_trx_types_all rctt,
fnd_lookup_values flv, ra_customer_trx_lines_all rctla,
jai_ar_trx_lines jatt, hz_cust_accounts hca,
hz_parties hp
WHERE rcta.cust_trx_type_id = rctt.cust_trx_type_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND flv.lookup_type = 'TRX TYPES'
AND rctt.TYPE = flv.lookup_code
AND rctla.line_type = 'LINE'
AND jatt.customer_trx_id = rcta.customer_trx_id
AND jatt.customer_trx_line_id = rctla.customer_trx_line_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND jatt.excise_invoice_no IS NOT NULL
AND TO_CHAR (trx_date, 'DD-MON-YYYY') BETWEEN NVL (TO_CHAR (:p_from_date,
'DD-MON-YYYY'
),
TO_DATE (SYSDATE,
'DD-MON-YYYY'
)
) AND NVL (TO_CHAR (:p_to_date, 'DD-MON-YYYY' ), TO_DATE (SYSDATE, 'DD-MON-YYYY' ) )
AND hca.cust_account_id = NVL (:p_cust_name, hca.cust_account_id)
No comments:
Post a Comment