SELECT
taxdet.invoice_id,
taxdet.invoice_amount,
taxdet.line_type_lookup_code,
taxdet.description,
taxdet.invoice_date,
taxdet.gl_date,
taxdet.supplier_site_name,
taxdet.supplier_name,
taxdet.supplier_number,
taxdet.party_state,
taxdet.supplier_gst_number,
taxdet.invoice_number,
taxdet.invoice_currency_code,
taxdet.related_invoice_number,
taxdet.line_number,
taxdet.rec_nonrec_flag,
taxdet.uom_code,
taxdet.quantity,
taxdet.unit_price,
taxdet.transaction_line_amount,
taxdet.taxable_line_amount,
taxdet.tax_type_code,
taxdet.hsn_code,
taxdet.sac_code,
taxdet.tax_rate,
taxdet.charge_account_ap,
taxdet.tax_natural_account,
taxdet.cancelled_date,
(
SELECT DISTINCT
region_2
FROM
hr_locations
WHERE
active_status = ‘A’
AND location_id = (
SELECT
MAX(ship_to_location_id)
FROM
ap_invoice_lines_all
WHERE
invoice_id = taxdet.invoice_id
AND ship_to_location_id IS NOT NULL
AND nvl(description,’N/A’) != ‘Round Off’
)
AND ship_to_site_flag = ‘Y’
GROUP BY
region_2
) location_state_code,
(
SELECT DISTINCT
a.lookup_code
FROM
fnd_lookup_values a,
hr_locations b
WHERE
a.lookup_type LIKE ‘INDIA_PO_GST_NUM%’
AND a.language = ‘US’
AND a.enabled_flag = ‘Y’
AND (
a.meaning = b.region_2
|| ‘-‘
|| substr(taxdet.charge_account_ap,1,instr(taxdet.charge_account_ap,’.’,1,1) – 1)
OR a.tag = b.region_2
|| ‘-‘
|| substr(taxdet.charge_account_ap,1,instr(taxdet.charge_account_ap,’.’,1,1) – 1)
)
AND b.active_status = ‘A’
AND b.ship_to_site_flag = ‘Y’
AND b.location_id = (
SELECT
MAX(ship_to_location_id)
FROM
ap_invoice_lines_all
WHERE
invoice_id = taxdet.invoice_id
AND ship_to_location_id IS NOT NULL
AND nvl(description,’N/A’) != ‘Round Off’
)
GROUP BY
lookup_code
) gst_number
FROM
(
SELECT DISTINCT
hou.name busines_unit,
legent.party_id,
legent.name legal_entity,
aia.invoice_num invoice_number,
aia.invoice_id,
aia.invoice_currency_code,
aila.line_type_lookup_code invoice_type,
aila.line_number test,
TO_CHAR(aila.line_number)
|| ‘-‘
|| TO_CHAR(aida.distribution_line_number) line_number,
aila.line_type_lookup_code,
nvl(aia.invoice_amount,0) invoice_amount,
TO_CHAR(aia.cancelled_date,’DD-Mon-YYYY’,’NLS_DATE_LANGUAGE = AMERICAN’) cancelled_date,
TO_CHAR(aia.invoice_date,’DD-Mon-YYYY’,’NLS_DATE_LANGUAGE = AMERICAN’) invoice_date,
TO_CHAR(aida.accounting_date,’DD-Mon-YYYY’,’NLS_DATE_LANGUAGE = AMERICAN’) gl_date,
aia.description,
nvl(aia.total_tax_amount,0) tax_amount,
aps.vendor_name supplier_name,
aps.segment1 supplier_number,
aps.vendor_type_lookup_code,
aila.unit_price,
aila.quantity_invoiced quantity,
aila.unit_meas_lookup_code uom_code,
(
SELECT
state
FROM
poz_supplier_sites_v
WHERE
vendor_id = aps.vendor_id
AND vendor_site_id = aia.vendor_site_id –AND ROWNUM = 1
) party_state,
(
SELECT
vendor_site_code
FROM
poz_supplier_sites_v
WHERE
vendor_id = aps.vendor_id
AND vendor_site_id = aia.vendor_site_id –AND ROWNUM = 1
) supplier_site_name,
aila.attribute7 hsn_code,
aila.attribute8 sac_code,
(
SELECT
SUM(nvl(amount,0) )
FROM
ap_invoice_lines_all
WHERE
invoice_id = aia.invoice_id
AND line_type_lookup_code = ‘ITEM’
) taxable_line_amount,
‘TBC’ line_total_gross,
aila.amount tax_line_amount,
DECODE(aila.line_type_lookup_code,’PREPAY’,NULL,DECODE(aila.line_type_lookup_code,’PREPAY’,NULL, (
SELECT
(cc.segment1
|| ‘.’
|| cc.segment2
|| ‘.’
|| cc.segment3
|| ‘.’
|| cc.segment4
|| ‘.’
|| cc.segment5
|| ‘.’
|| cc.segment6
|| ‘.’
|| cc.segment7
|| ‘.’
|| cc.segment8
|| ‘.’
|| cc.segment9)
FROM
xla_events xe,xla_distribution_links xdl,xla_transaction_entities xte,xla_ae_headers xah,xla_ae_lines xal,gl_code_combinations cc
WHERE
1 = 1
AND xte.entity_id = xah.entity_id
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND nvl(xte.source_id_int_1,-99) = aia.invoice_id
AND xte.application_id = 200
AND xte.entity_code = ‘AP_INVOICES’
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.application_id = xte.application_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = ‘AP_INV_DIST’
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND xdl.event_id = xe.event_id
AND xe.application_id = xte.application_id
AND xe.event_id = aida.accounting_event_id
AND aida.period_name = xah.period_name
AND aida.period_name = xal.period_name
AND nvl(xal.accounting_class_code,’1′) <> ‘LIABILITY’
AND cc.code_combination_id = xal.code_combination_id
AND cc.code_combination_id = xal.code_combination_id
AND aida.line_type_lookup_code <> ‘AWT’
) ) ) charge_account_ap,
nvl(aida.amount,0) transaction_line_amount,
aila.tax tax_type_code,
aila.tax_rate,
aida.dist_code_combination_id,
nvl(total_rec_tax_amount,0) total_rec_tax_amount,
nvl(total_nrec_tax_amount,0) total_nrec_tax_amount,
DECODE(aida.line_type_lookup_code,’REC_TAX’,’Recoverable’,’NONREC_TAX’,’Non Recoverable’) rec_nonrec_flag,
DECODE(aila.line_type_lookup_code,’PREPAY’,NULL,DECODE(aila.line_type_lookup_code,’PREPAY’,NULL, (
SELECT
(cc.segment1
|| ‘.’
|| cc.segment2
|| ‘.’
|| cc.segment3
|| ‘.’
|| cc.segment4
|| ‘.’
|| cc.segment5
|| ‘.’
|| cc.segment6
|| ‘.’
|| cc.segment7
|| ‘.’
|| cc.segment8
|| ‘.’
|| cc.segment9)
FROM
xla_events xe,xla_distribution_links xdl,xla_transaction_entities xte,xla_ae_headers xah,xla_ae_lines xal,gl_code_combinations cc
WHERE
1 = 1
AND xte.entity_id = xah.entity_id
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND nvl(xte.source_id_int_1,-99) = aia.invoice_id
AND xte.application_id = 200
AND xte.entity_code = ‘AP_INVOICES’
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.application_id = xte.application_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = ‘AP_INV_DIST’
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND xdl.event_id = xe.event_id
AND xe.application_id = xte.application_id
AND xe.event_id = aida.accounting_event_id
AND aida.period_name = xah.period_name
AND aida.period_name = xal.period_name
AND nvl(xal.accounting_class_code,’1′) <> ‘LIABILITY’
AND cc.code_combination_id = xal.code_combination_id
AND cc.code_combination_id = xal.code_combination_id
AND aida.line_type_lookup_code LIKE ‘%TAX%’
) ) ) tax_natural_account,
(
SELECT
zr.registration_number
FROM
zx_registrations zr,
zx_party_tax_profile zptp,
zx_regimes_b zb,
poz_supplier_sites_all_m pss
WHERE
1 = 1
AND zr.party_tax_profile_id = zptp.party_tax_profile_id
AND zptp.party_id = pss.party_site_id
AND zr.registration_status_code = ‘REGISTERED’
AND pss.vendor_site_id = aia.vendor_site_id
AND zb.regime_type_flag = ‘W’
AND ROWNUM < 2
AND zb.tax_regime_code = zr.tax_regime_code
AND SYSDATE BETWEEN nvl(zr.effective_from,SYSDATE) AND nvl(zr.effective_to,SYSDATE + 1)
) vendor_pan,
aila.description line_description,
(
SELECT DISTINCT
invoice_num
FROM
ap_invoices_all
WHERE
invoice_id = aia.tax_related_invoice_id
) related_invoice_number,
(
SELECT
zr.registration_number
FROM
zx_registrations zr,
zx_party_tax_profile zptp,
zx_regimes_b zb,
poz_supplier_sites_all_m pss
WHERE
1 = 1
AND zr.party_tax_profile_id = zptp.party_tax_profile_id
AND zptp.party_id = pss.party_site_id
AND zr.registration_status_code = ‘REGISTERED’
AND pss.vendor_site_id = aia.vendor_site_id
AND zb.regime_type_flag = ‘I’
AND ROWNUM < 2
AND zb.tax_regime_code = zr.tax_regime_code
AND SYSDATE BETWEEN nvl(zr.effective_from,SYSDATE) AND nvl(zr.effective_to,SYSDATE + 1)
) supplier_gst_number,
(
SELECT
SUM(nvl(amount,0) )
FROM
ap_invoice_lines_all
WHERE
invoice_id = aia.invoice_id
AND line_type_lookup_code IN (
‘ITEM’
)
) line_amount,
(
SELECT
SUM(nvl(amount,0) )
FROM
ap_invoice_lines_all
WHERE
invoice_id = aia.invoice_id
AND line_type_lookup_code IN (
‘TAX’
)
) taxable_amount
FROM
hr_operating_units hou,
xle_entity_profiles legent,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
poz_suppliers_v aps,
gl_code_combinations gcc
WHERE
1 = 1
AND hou.default_legal_context_id (+) = legent.legal_entity_id
AND aia.org_id = hou.organization_id
AND aia.invoice_id = aila.invoice_id
AND aia.vendor_id = aps.vendor_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aida.dist_code_combination_id = gcc.code_combination_id
AND aida.posted_flag = ‘Y’
AND aps.enabled_flag (+) = ‘Y’
AND aida.line_type_lookup_code NOT IN (
‘AWT’,
‘ACCURAL’
)
AND aila.line_type_lookup_code NOT IN (
‘MISCELLANEOUS’
)
AND nvl(aida.amount,0) <> 0
AND TO_CHAR(aida.accounting_date,’Mon-YY’,’NLS_DATE_LANGUAGE = AMERICAN’) =:p_period
AND EXISTS (
SELECT
1
FROM
xla_events xe,
xla_distribution_links xdl,
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations cc
WHERE
1 = 1
AND xte.entity_id = xah.entity_id
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND nvl(xte.source_id_int_1,-99) = aia.invoice_id
AND xte.application_id = 200
AND xte.entity_code = ‘AP_INVOICES’
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.application_id = xte.application_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = ‘AP_INV_DIST’
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND xdl.event_id = xe.event_id
AND xe.application_id = xte.application_id
AND xe.event_id = aida.accounting_event_id
AND aida.period_name = xah.period_name
AND aida.period_name = xal.period_name
AND nvl(xal.accounting_class_code,’1′) <> ‘LIABILITY’
AND cc.code_combination_id = xal.code_combination_id
AND cc.segment1 = nvl(:p_entity_code,cc.segment1)
)
ORDER BY
aia.invoice_num,
aila.line_number
) taxdet
Recent Posts