0 Replies Latest reply on Dec 19, 2018 9:45 AM by 1535112

    Post GST Tax Invoice Table

    1535112

      Hi all,

      I have a COGS custom report that is picking excise invoice details from jai_om_wsh_lines_all table, but this table has no record after July-17.

      What is the replacement table for this post GST?

       

      Below is the report query pre-GST:

       

      select customer_name,Customer_Type,Customer_Industry,application,pump_model_no,product_type,excise_invoice_date,excise_invoice_no,order_number,sales_order_type,

      sum(excise_quantity) excise_quantity,sum(excise_amount) excise_amount,rate,sum(material_cost)material_cost,sum(resource_cost)resource_cost,sum(osp_cost) osp_cost

      from

      (select

      arc.customer_name customer_name,

      hca.customer_class_code Customer_Type,

      arc.attribute1 Customer_Industry,

      oola.attribute10 application,

      oola.attribute5 pump_model_no,

      decode(oola.attribute2,'Yes','SPARES','No','PUMP') product_type,

      jowl.excise_invoice_date excise_invoice_date,

      jowl.excise_invoice_no excise_invoice_no,

      ooha.order_number order_number,

      ottl.name sales_order_type,

      sum(jowl.quantity)excise_quantity,

      sum(jowl.selling_price*jowl.quantity)excise_amount,

      ( select distinct nvl(exchange_rate,1)

        from ra_customer_trx_all

        where interface_header_attribute1=to_char(ooha.order_number)

      )rate,

      sum((select (round(actual_material+actual_material_overhead,2)) *jowl.quantity

           from cst_cg_cost_history_v

           where inventory_item_id=oola.inventory_item_id

           and trunc(transaction_date) between nvl(:from_date,jowl.excise_invoice_date) and nvl(:to_date,jowl.excise_invoice_date)

           and abs(primary_quantity)=jowl.quantity    

           and rownum=1

         ) )material_cost,

      sum((select sum(round(new_resource+new_overhead,2))*jowl.quantity

           from cst_cg_cost_history_v

           where inventory_item_id=oola.inventory_item_id

           and trunc(transaction_date) between nvl(:from_date,jowl.excise_invoice_date) and nvl(:to_date,jowl.excise_invoice_date)

           and abs(primary_quantity)=jowl.quantity

           and rownum=1

      ) )resource_cost,

      sum((select (round(new_outside_processing,2))*jowl.quantity

           from cst_cg_cost_history_v

           where inventory_item_id=oola.inventory_item_id

           and trunc(transaction_date) between nvl(:from_date,jowl.excise_invoice_date) and nvl(:to_date,jowl.excise_invoice_date)

           and abs(primary_quantity)=jowl.quantity

            and rownum=1

         ))osp_cost

      from

      oe_order_headers_all ooha, 

      oe_order_lines_all oola,

      jai_om_wsh_lines_all jowl,

      hz_cust_accounts hca,

      ar_customers arc,

      --hz_parties hp,

      hr_operating_units hou,

      oe_transaction_types_tl ottl,

      mtl_system_items_b msib

      where 1=1

      and ooha.header_id=oola.header_id

      and jowl.order_line_id=oola.line_id

      and ooha.sold_to_org_id=hca.cust_account_id

      --and hca.party_id=hp.party_id

      and hca.cust_account_id =arc.customer_id                

      and ooha.org_id=hou.organization_id

      and ooha.order_type_id=ottl.transaction_type_id

      and oola.inventory_item_id=msib.inventory_item_id

      and oola.ship_from_org_id=msib.organization_id

      and hou.name=nvl(:organization_name,hou.name)  ---'Pressurejet Systems Pvt. Ltd.'

      and trunc(jowl.excise_invoice_date) between nvl(:from_date,jowl.excise_invoice_date) and nvl(:to_date,jowl.excise_invoice_date)

      and ottl.name=nvl(:Sales_order_type,ottl.name)

      and oola.attribute2=nvl(:Type_of_product,oola.attribute2)

      and msib.segment1=nvl(:Pump_Model_No,msib.segment1)

      and oola.attribute10=nvl(:Application,oola.attribute10)

      and ooha.order_number=nvl(:sales_order_number,ooha.order_number)

      and arc.customer_name=nvl(:customer_name,arc.customer_name)

      group by       

      ooha.order_number,

      jowl.excise_invoice_date,

      jowl.excise_invoice_no,

      oola.attribute10,

      oola.attribute5,

      decode(oola.attribute2,'Yes','SPARES','No','PUMP'),

      arc.customer_name,

      hca.customer_class_code,

      arc.attribute1,

      ooha.order_number,oola.inventory_item_id,jowl.quantity,

      ottl.name

      order by 7,8

      )

      group by

      customer_name,Customer_Type,Customer_Industry,application,pump_model_no,product_type,excise_invoice_date,excise_invoice_no,order_number,sales_order_type,rate

      order by 7,8;

       

       

      Regards,

      Subhash

      +91-9110595100