/*--For CST-*/
SELECT distinct ood.organization_code,
msi.segment1 item_no,
jih.tax_category_list,
jct.tax_category_name,
jc.tax_name,
jtl.line_no
FROM mtl_system_items msi,
JAI_INV_ITM_TAXCTG_HDRS JIh,
JAI_INV_ITM_TAXCTG_DTLS jid,
jai_cmn_tax_ctgs_all jct,
JAI_CMN_TAX_CTGS_ALL jta,
JAI_CMN_TAX_CTG_LINES jtl,
JAI_CMN_TAXES_ALL jc,
org_organization_definitions ood
WHERE msi.organization_id = :org_id
AND msi.inventory_item_id = jId.inventory_item_id
AND MSI.ORGANIZATION_ID = JIh.ORGANIZATION_ID
AND jih.tax_category_list = jid.tax_category_list
AND jct.tax_category_id = JId.tax_category_id
AND jid.tax_category_id = jta.tax_category_id
AND jta.tax_category_id = jtl.tax_category_id
AND jtl.TAX_ID = jc.tax_id
AND ood.organization_id = msi.organization_id
AND JIh.tax_category_list like '%CST%'
AND msi.segment1 IN ('')
order by 2,3,6
-----------------------------------------------------------------------------------------------------------------
/*--For VAT--*/
SELECT distinct ood.organization_code,
msi.segment1 item_no,
jih.tax_category_list,
jct.tax_category_name,
jc.tax_name,
jtl.line_no
FROM mtl_system_items msi,
JAI_INV_ITM_TAXCTG_HDRS JIh,
JAI_INV_ITM_TAXCTG_DTLS jid,
jai_cmn_tax_ctgs_all jct,
JAI_CMN_TAX_CTGS_ALL jta,
JAI_CMN_TAX_CTG_LINES jtl,
JAI_CMN_TAXES_ALL jc,
org_organization_definitions ood
WHERE msi.organization_id = :org_id
AND msi.inventory_item_id = jId.inventory_item_id
AND MSI.ORGANIZATION_ID = JIh.ORGANIZATION_ID
AND jih.tax_category_list = jid.tax_category_list
AND jct.tax_category_id = JId.tax_category_id
AND jid.tax_category_id = jta.tax_category_id
AND jta.tax_category_id = jtl.tax_category_id
AND jtl.TAX_ID = jc.tax_id
AND ood.organization_id = msi.organization_id
AND JIh.tax_category_list like '%VAT%'
AND msi.segment1 IN ('')
order by 2,6 --vat
SELECT distinct ood.organization_code,
msi.segment1 item_no,
jih.tax_category_list,
jct.tax_category_name,
jc.tax_name,
jtl.line_no
FROM mtl_system_items msi,
JAI_INV_ITM_TAXCTG_HDRS JIh,
JAI_INV_ITM_TAXCTG_DTLS jid,
jai_cmn_tax_ctgs_all jct,
JAI_CMN_TAX_CTGS_ALL jta,
JAI_CMN_TAX_CTG_LINES jtl,
JAI_CMN_TAXES_ALL jc,
org_organization_definitions ood
WHERE msi.organization_id = :org_id
AND msi.inventory_item_id = jId.inventory_item_id
AND MSI.ORGANIZATION_ID = JIh.ORGANIZATION_ID
AND jih.tax_category_list = jid.tax_category_list
AND jct.tax_category_id = JId.tax_category_id
AND jid.tax_category_id = jta.tax_category_id
AND jta.tax_category_id = jtl.tax_category_id
AND jtl.TAX_ID = jc.tax_id
AND ood.organization_id = msi.organization_id
AND JIh.tax_category_list like '%CST%'
AND msi.segment1 IN ('')
order by 2,3,6
-----------------------------------------------------------------------------------------------------------------
/*--For VAT--*/
SELECT distinct ood.organization_code,
msi.segment1 item_no,
jih.tax_category_list,
jct.tax_category_name,
jc.tax_name,
jtl.line_no
FROM mtl_system_items msi,
JAI_INV_ITM_TAXCTG_HDRS JIh,
JAI_INV_ITM_TAXCTG_DTLS jid,
jai_cmn_tax_ctgs_all jct,
JAI_CMN_TAX_CTGS_ALL jta,
JAI_CMN_TAX_CTG_LINES jtl,
JAI_CMN_TAXES_ALL jc,
org_organization_definitions ood
WHERE msi.organization_id = :org_id
AND msi.inventory_item_id = jId.inventory_item_id
AND MSI.ORGANIZATION_ID = JIh.ORGANIZATION_ID
AND jih.tax_category_list = jid.tax_category_list
AND jct.tax_category_id = JId.tax_category_id
AND jid.tax_category_id = jta.tax_category_id
AND jta.tax_category_id = jtl.tax_category_id
AND jtl.TAX_ID = jc.tax_id
AND ood.organization_id = msi.organization_id
AND JIh.tax_category_list like '%VAT%'
AND msi.segment1 IN ('')
order by 2,6 --vat
No comments:
Post a Comment