Pages

Friday, January 31, 2014

To get item tax category for both cst and vat

/*--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

No comments:

Post a Comment