Item Classification

Query for check whether delivery no against the items were done the item classification are not.

Hi,

The below query will return delivery no against the item classification .
** Two parameters Organization_id and Delivery no
Once you entered the above parameter you will get ITEM_NAME , ITEM_FOLIO,ITEM_TARIFF,MODVATABLE,EXCISABLE,TRADABLE
In that above field you will get modvatable flag Y and EXCISABLE flag Y then items are classified with vat and excise.


/* Item Classification Query */
/* Formatted on 13-Apr-12 9:34:50 AM (QP5 v5.115.:P_ORG_ID0.9015) */
SELECT   DISTINCT a.segment1,
                  b.attribute_value item_folio,
                  c.attribute_value item_tariff,
                  d.attribute_value item_class,
                  e.attribute_value modvatable,
                  f.attribute_value excisable,
                  g.attribute_value tradable,
                  h.attribute_value applicable,
                  i.attribute_value item_class1,
                  j.attribute_value recoverable
  FROM   (SELECT   rgm.inventory_item_id, msi.segment1
            FROM   jai_rgm_itm_regns rgm, mtl_system_items_b msi
           WHERE       rgm.inventory_item_id = msi.inventory_item_id
                   AND rgm.organization_id = msi.organization_id
                   AND rgm.organization_id = :P_ORG_ID) a,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'EXCISE'
                   AND RGMATTR.attribute_code = 'ITEM FOLIO'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) b,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'EXCISE'
                   AND RGMATTR.attribute_code = 'ITEM TARIFF'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) c,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'EXCISE'
                   AND RGMATTR.attribute_code = 'ITEM CLASS'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) d,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'EXCISE'
                   AND RGMATTR.attribute_code = 'MODVATABLE'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) e,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'EXCISE'
                   AND RGMATTR.attribute_code = 'EXCISABLE'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) f,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'EXCISE'
                   AND RGMATTR.attribute_code = 'TRADABLE'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) g,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'VAT'
                   AND RGMATTR.attribute_code = 'APPLICABLE'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) h,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'VAT'
                   AND RGMATTR.attribute_code = 'ITEM CLASS'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) i,
         (SELECT   rgmattr.attribute_value, msi.inventory_item_id
            FROM   jai_rgm_itm_regns rgmitm,
                   jai_rgm_itm_tmpl_attrs rgmattr,
                   mtl_system_items_b msi
           WHERE       rgmitm.organization_id = :P_ORG_ID
                   AND rgmitm.rgm_item_regns_id = rgmattr.rgm_item_regns_id
                   AND rgmitm.regime_code = 'VAT'
                   AND RGMATTR.attribute_code = 'RECOVERABLE'
                   --and rgmitm.inventory_item_id='86517'
                   AND msi.inventory_item_id = rgmitm.inventory_item_id
                   AND msi.organization_id = rgmitm.organization_id) j
 WHERE       a.inventory_item_id = b.inventory_item_id(+)
         AND a.inventory_item_id = c.inventory_item_id(+)
         AND a.inventory_item_id = d.inventory_item_id(+)
         AND a.inventory_item_id = e.inventory_item_id(+)
         AND a.inventory_item_id = f.inventory_item_id(+)
         AND a.inventory_item_id = g.inventory_item_id(+)
         AND a.inventory_item_id = h.inventory_item_id(+)
         AND a.inventory_item_id = i.inventory_item_id(+)
         AND a.inventory_item_id = j.inventory_item_id(+)
         --and a.segment1='17500KSPB60ZB'
         AND a.inventory_item_id IN
                  (SELECT   inventory_item_id
                     FROM   wsh_delivery_details
                    WHERE   delivery_detail_id IN
                                  (SELECT   DELIVERY_DETAIL_ID
                                     FROM   wsh_delivery_assignments
                                    WHERE   delivery_id = :P_DELIVERY_ID))

Oracle Technical