0 Replies Latest reply: Oct 7, 2013 5:23 PM by CSK RSS

    Oracle Report Failed with Signal 11 Error

    CSK

      Hi,

       

      I am modifying a query in one of the Report Groups,when try to run my report with New query I am getting Signal 11 error.

       

      Original query is working fine but when run the report with modified query it is error with Signal 11.

       

      Note :Modified query is not giving any error when running in toad or while compiling in reports.

       

      Can anyone please assist what is wrong with my query.

       

       

       

      Actual Query:


      Select

      wnd.delivery_id

      ,nvl(wdd.ship_to_contact_id,-999)  ship_to_contact_id

      ,oola.line_id so_line_id

      ,msi.inventory_item_id

      ,oola.header_id so_header_id

      ,wdd.delivery_detail_id

      ,wdd.attribute4 pallet_no

      ,to_number(wdd.attribute1) num_of_cartons

      ,msi.segment1

      ,msi.description

      ,msi.primary_uom_code UM

      ,msi.concatenated_segments

      ,oola.user_item_description user_desc

      ,wdd.requested_quantity qty

      ,ooha.cust_po_number customer_po

      ,ooha.order_number so_num

      ,oola.line_number so_line_num

      ,ooha.order_number so_num

      ,oola.line_number so_line_num

      ,hca.cust_account_id                         bill_to_cust_acc_id

      ,hcasa.cust_acct_site_id                 bill_to_cust_acc_site

      ,xsf_haou.attribute7                         cust_item_ref

      ,(select packing_instructions

           from   oe_order_lines_all

           where  line_id = oola.line_id

           and    packing_instructions is not null

           and rownum < 2)  line_level_ins

      ,(select attribute10

                from wsh_delivery_details

               where delivery_detail_id = wdd.delivery_detail_id

                 and attribute10 is not null

                 and rownum < 2) dimension1

      ,(select ftt.territory_short_name

                                 from wsh_delivery_details w,

                                     fnd_territories_tl ftt

                               where w.delivery_detail_id=wdd.delivery_detail_id

                                 and ftt.territory_code = w.attribute11

                                 and w.attribute11 is not null

                                 and ftt.language = 'US'

                                 and rownum < 2)  country1               

      from

      oe_order_headers_all ooha

      ,oe_order_lines_all oola

      ,mtl_system_items_kfv msi

      ,wsh_delivery_details wdd

      ,wsh_delivery_assignments wda

      ,wsh_new_deliveries wnd

      ,hz_cust_site_uses_all                   hcsua

      , hz_cust_acct_sites_all                  hcasa

      , hz_cust_accounts                         hca

      , hr_all_organization_units                sf_haou

      ,xx_addl_attribute_info                 xsf_haou

      where wnd.delivery_id= wda.delivery_id

      and wnd.delivery_id  = :p_delivery_id

      and wda.delivery_detail_id = wdd.delivery_detail_id

      and wdd.source_code='OE'

      and wdd.source_line_id= oola.line_id

      and oola.header_id= ooha.header_id

      and oola.inventory_item_id= msi.inventory_item_id

      and oola.ship_from_org_id= msi.organization_id

      and nvl(msi.inventory_item_flag,'Y')<>'N'

      and hcsua.site_use_id =oola.invoice_to_org_id

      and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

      and hcasa.cust_account_id= hca.cust_account_id

      and oola.org_id= sf_haou.organization_id

      and sf_haou.organization_id = xsf_haou.pk_value_1(+)

      and sf_haou.attribute_category = xsf_haou.attribute_category(+)

       

       

       

      Modified Query



      Select

      wnd.delivery_id

      ,nvl(wdd.ship_to_contact_id,-999)  ship_to_contact_id

      ,oola.line_id so_line_id

      ,msi.inventory_item_id

      ,oola.header_id so_header_id

      ,wdd.delivery_detail_id

      ,wdd.attribute4 pallet_no

      ,to_number(wdd.attribute1) num_of_cartons

      ,msi.segment1

      ,msi.description

      ,msi.primary_uom_code UM

      ,msi.concatenated_segments

      ,oola.user_item_description user_desc

      ,wdd.requested_quantity qty

      ,ooha.cust_po_number customer_po

      ,ooha.order_number so_num

      ,(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number) so_line_num

      ,ooha.order_number so_num

      ,oola.line_number so_line_num

      ,hca.cust_account_id                         bill_to_cust_acc_id

      ,hcasa.cust_acct_site_id                 bill_to_cust_acc_site

      ,xsf_haou.attribute7                         cust_item_ref

      ,(select packing_instructions

           from   oe_order_lines_all

           where  line_id = oola.line_id

           and    packing_instructions is not null

           and rownum < 2)  line_level_ins

      ,(select attribute10

                from wsh_delivery_details

               where delivery_detail_id = wdd.delivery_detail_id

                 and attribute10 is not null

                 and rownum < 2) dimension1

      ,(select ftt.territory_short_name

                                 from wsh_delivery_details w,

                                     fnd_territories_tl ftt

                               where w.delivery_detail_id=wdd.delivery_detail_id

                                 and ftt.territory_code = w.attribute11

                                 and w.attribute11 is not null

                                 and ftt.language = 'US'

                                 and rownum < 2)  country1               

      from

      oe_order_headers_all ooha

      ,oe_order_lines_all oola

      ,mtl_system_items_kfv msi

      ,wsh_delivery_details wdd

      ,wsh_delivery_assignments wda

      ,wsh_new_deliveries wnd

      ,hz_cust_site_uses_all                   hcsua

      , hz_cust_acct_sites_all                  hcasa

      , hz_cust_accounts                         hca

      , hr_all_organization_units                sf_haou

      ,xx_addl_attribute_info                 xsf_haou

      where wnd.delivery_id= wda.delivery_id

      and wnd.delivery_id  = :p_delivery_id

      and wda.delivery_detail_id = wdd.delivery_detail_id

      and wdd.source_code='OE'

      and wdd.source_line_id= oola.line_id

      --and oola.line_id = wdd.source_line_id(+)

      --and oola.item_type_code IN ('OPTION','STANDARD')

      and oola.header_id= ooha.header_id

      and oola.inventory_item_id= msi.inventory_item_id

      and oola.ship_from_org_id= msi.organization_id

      and nvl(msi.inventory_item_flag,'Y')<>'N'

      and hcsua.site_use_id =oola.invoice_to_org_id

      and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

      and hcasa.cust_account_id= hca.cust_account_id

      and oola.org_id= sf_haou.organization_id

      and sf_haou.organization_id = xsf_haou.pk_value_1(+)

      and sf_haou.attribute_category = xsf_haou.attribute_category(+)

      UNION

      Select

      wnd.delivery_id

      ,-999

      ,oola.line_id so_line_id

      ,msi.inventory_item_id

      ,oola.header_id so_header_id

      ,null

      ,null

      ,null

      ,msi.segment1

      ,msi.description

      ,msi.primary_uom_code UM

      ,msi.concatenated_segments

      ,oola.user_item_description user_desc

      ,oola.ordered_quantity qty

      ,ooha.cust_po_number customer_po

      ,ooha.order_number so_num

      ,(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number) so_line_num

      ,ooha.order_number so_num

      ,oola.line_number so_line_num

      ,hca.cust_account_id                         bill_to_cust_acc_id

      ,hcasa.cust_acct_site_id                 bill_to_cust_acc_site

      ,xsf_haou.attribute7                         cust_item_ref

      ,(select packing_instructions

           from   oe_order_lines_all

           where  line_id = oola.line_id

           and    packing_instructions is not null

           and rownum < 2)  line_level_ins

      ,NULL

      ,NULL

      from

      oe_order_headers_all ooha

      ,oe_order_lines_all oola

      ,mtl_system_items_kfv msi

      ,wsh_new_deliveries wnd

      ,hz_cust_site_uses_all                   hcsua

      , hz_cust_acct_sites_all                  hcasa

      , hz_cust_accounts                         hca

      , hr_all_organization_units                sf_haou

      ,xx_addl_attribute_info                 xsf_haou

      where wnd.delivery_id  = :p_delivery_id

      and oola.item_type_code IN ('OPTION','STANDARD')

      and wnd.source_header_id= ooha.header_id

      and ooha.header_id=oola.header_id

      and oola.inventory_item_id= msi.inventory_item_id

      and oola.ship_from_org_id= msi.organization_id

      and nvl(msi.inventory_item_flag,'N')='N'

      and hcsua.site_use_id =oola.invoice_to_org_id

      and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

      and hcasa.cust_account_id= hca.cust_account_id

      and oola.org_id= sf_haou.organization_id

      and sf_haou.organization_id = xsf_haou.pk_value_1(+)

      and sf_haou.attribute_category = xsf_haou.attribute_category(+)

      and EXISTS

      (SELECT 1

      FROM   wsh_new_deliveries wnd

      ,      wsh_delivery_assignments wda

      ,      wsh_delivery_details wdd

      ,      oe_order_lines_all   ool

      WHERE  wnd.delivery_id = wda.delivery_id

        AND  wda.delivery_detail_id= wdd.delivery_detail_id

        AND  wdd.source_line_id = ool.line_id

        AND  wnd.delivery_id = :p_delivery_id

        AND  ool.top_model_line_id = oola.top_model_line_id)