SELECT DISTINCT
rcta.trx_number invoice_number,
–gd.gl_date,
hp.party_name customer_name,
rctla.line_number,
rctla.description,
rctla.uom_code,
rctla.quantity_invoiced qty,
rctla.unit_selling_price unit_price,
rctla.revenue_amount amount,
(
SELECT
jtl.tax_rate_percentage
FROM
apps.jai_tax_lines jtl
WHERE
rcta.trx_number = jtl.trx_number
AND rctla.line_number = jtl.trx_line_number
AND jtl.entity_code = ‘TRANSACTIONS’
AND rcta.customer_trx_id = jtl.trx_id
AND rctla.customer_trx_line_id = jtl.trx_line_id
AND jtl.tax_rate_code = ‘STD-CGST’
) cgst_percentage,
(
SELECT
jtl.unround_tax_amt_trx_curr
FROM
apps.jai_tax_lines jtl
WHERE
rcta.trx_number = jtl.trx_number
AND rctla.line_number = jtl.trx_line_number
AND jtl.entity_code = ‘TRANSACTIONS’
AND rcta.customer_trx_id = jtl.trx_id
AND rctla.customer_trx_line_id = jtl.trx_line_id
and jtl.tax_rate_code = ‘STD-CGST’
) cgst_amount,
(
SELECT
jtl.tax_rate_percentage
FROM
apps.jai_tax_lines jtl
WHERE
rcta.trx_number = jtl.trx_number
AND rctla.line_number = jtl.trx_line_number
AND jtl.entity_code = ‘TRANSACTIONS’
AND rcta.customer_trx_id = jtl.trx_id
AND rctla.customer_trx_line_id = jtl.trx_line_id
AND jtl.tax_rate_code = ‘STD-SGST’
) sgst_percentage,
(
SELECT
jtl.unround_tax_amt_trx_curr
FROM
apps.jai_tax_lines jtl
WHERE
rcta.trx_number = jtl.trx_number
AND rctla.line_number = jtl.trx_line_number
AND jtl.entity_code = ‘TRANSACTIONS’
AND rcta.customer_trx_id = jtl.trx_id
AND rctla.customer_trx_line_id = jtl.trx_line_id
AND jtl.tax_rate_code = ‘STD-SGST’
) sgst_amount,
(
SELECT
jtl.tax_rate_percentage
FROM
apps.jai_tax_lines jtl
WHERE
rcta.trx_number = jtl.trx_number
AND rctla.line_number = jtl.trx_line_number
AND jtl.entity_code = ‘TRANSACTIONS’
AND rcta.customer_trx_id = jtl.trx_id
AND rctla.customer_trx_line_id = jtl.trx_line_id
AND jtl.tax_rate_code = ‘STD-IGST’
) igst_percentage,
(
SELECT
jtl.unround_tax_amt_trx_curr
FROM
apps.jai_tax_lines jtl
WHERE
rcta.trx_number = jtl.trx_number
AND rctla.line_number = jtl.trx_line_number
AND jtl.entity_code = ‘TRANSACTIONS’
AND rcta.customer_trx_id = jtl.trx_id
AND rctla.customer_trx_line_id = jtl.trx_line_id
AND jtl.tax_rate_code = ‘STD-IGST’
) igst_amount,
hcsua.location bill_to_location,
hl.address1 bt_address1,
hl.address2 bt_address2,
hl.address3 bt_address3,
hl.address4 bt_address4,
hl.city bt_city,
hl.postal_code bt_pincode,
hl.state bt_state
FROM
apps.ra_customer_trx_all rcta, /*apps.ra_cust_trx_line_gl_dist_all gd,*/
apps.hz_parties hp,
apps.hz_cust_accounts_all hcaa,
apps.ra_customer_trx_lines_all rctla,
apps.hz_cust_site_uses_all hcsua,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_party_sites hps,
apps.hz_locations hl
WHERE /*rcta.trx_number = ‘1704016192’
and*/
rcta.org_id = 379
/*and rcta.customer_trx_id = gd.customer_trx_id
and trunc(gd.gl_date) between ’01-JAN-21′ and ’31-JAN-21’*/
AND EXISTS (
SELECT
1
FROM
apps.ra_cust_trx_line_gl_dist_all gd
WHERE
rcta.customer_trx_id = gd.customer_trx_id
AND ‘REC’ = gd.account_class
AND ‘Y’ = gd.latest_rec_flag
AND trunc(gd.gl_date) BETWEEN ’01-JAN-21′ AND ’31-JAN-21′
)
AND rcta.bill_to_customer_id = hcaa.cust_account_id
AND hcaa.party_id = hp.party_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.line_type = ‘LINE’
AND rcta.bill_to_site_use_id = hcsua.site_use_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
ORDER BY
rcta.trx_number,
rctla.line_number
Recommended Posts
