1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

"end-of-file on communication channel"

Discussion in 'SQL PL/SQL' started by shemo, Jun 25, 2015.

  1. shemo

    shemo Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    please advice as each time i try to run this query i get this error "end-of-file on communication channel", while, when i try to run them separetly they run successfully and generate an output.....
    also when i try to run them with the union but with the attribute condition it run successfully and generate an output as well


    select
    Rel_Inv.Release_NUM,
    (SELECT SUM((quantity - quantity_cancelled) * price_override)
    FROM Po_Line_Locations_All
    WHERE po_release_id = Rel_Inv.po_release_id) Release_Total_Amount,
    glcc.segment1 || '.' || glcc.segment2 || '.' ||
    glcc.segment3 || '.' || glcc.segment4 || '.' ||
    glcc.segment5 || '.' || glcc.segment6 || '.' ||
    glcc.segment7 || '.' || glcc.segment8 || '.' ||
    glcc.segment9 charge_account,

    HRL.ATTRIBUTE1,
    HRL.attribute4,
    hrl.location_code,
    po.from_header_id,
    po.from_type_lookup_code,
    pohq.segment1 Ref_No,
    'Quotation' Ref_Type,
    po.segment1 PO_NO,
    nvl(po.authorization_status,'Incomplete') Status,
    po.comments po_desc,
    initcap(PO.type_lookup_code) AS "PO Type",
    po.currency_code PO_currency_code,
    XXTE_PUR_PKG.get_po_amount(PO.Po_Header_Id)PO_AMOUNT,
    po.creation_date,
    pol.line_num,
    pol.item_description,
    inv.invoice_num,
    inv.invoice_amount,
    inv.invoice_date ,
    inv.invoice_currency_code,
    nvl(inv.amount_remaining,0) "REMAINING AMOUNT",

    PR_EMP.PR_NUM,
    PR_EMP.PR_Amount,
    'EGP' PR_Currency_Code,
    PR_EMP.PR_PREPARER,
    po.po_header_id


    from
    po_headers_all POHq,
    po_headers_all V,
    (SELECT distinct poh.from_header_id,
    poH.from_type_lookup_code,
    POH.SEGMENT1,
    poh.authorization_status,
    poh.comments,
    poH.type_lookup_code,
    poh.currency_code,
    pol.po_header_id,
    poh.creation_date,
    POD1.CODE_COMBINATION_ID,
    POD1.PO_DISTRIBUTION_ID,
    pll.line_location_id

    FROM PO_HEADERS_ALL POH,
    PO_DISTRIBUTIONS_all POD1,
    po_lines_all POL,
    PO_LINE_LOCATIONS_ALL pll,
    Hr_Locations_All HRL

    WHERE PLL.LINE_LOCATION_ID = POD1.LINE_LOCATION_ID(+)
    and pll.po_line_id = pol.po_line_id
    and pol.PO_HEADER_ID = poh.po_header_id
    AND HRL.LOCATION_ID = PLL.Ship_To_Location_Id
    AND poH.type_lookup_code in ('STANDARD', 'BLANKET', 'PLANNED')) PO,

    po_lines_all pol,
    Hr_Locations_All HRL,
    Po_Line_Locations_All PLL,

    (select invl.po_header_id,
    invl.po_line_id,
    inv.invoice_num,
    inv.invoice_id,
    inv.invoice_date,
    inv.invoice_currency_code,
    nvl(inv.INVOICE_AMOUNT,0) INVOICE_AMOUNT ,
    sum(nvl(apps.amount_remaining,0)) amount_remaining,
    nvl(inv.AMOUNT_PAID,0) AMOUNT_PAID
    from AP_INVOICES_ALL inv,
    AP_INVOICE_LINES_ALL invl,
    Ap_Payment_Schedules_All APPS
    where inv.invoice_id=invl.invoice_id(+)
    and inv.invoice_id=apps.invoice_id(+)
    and inv.cancelled_date is null
    group by invl.po_header_id,
    invl.po_line_id,
    inv.invoice_num,
    inv.invoice_id,
    inv.invoice_date,
    inv.invoice_currency_code,
    nvl(inv.INVOICE_AMOUNT,0) ,
    nvl(inv.AMOUNT_PAID,0) ) inv,

    (select PRH.SEGMENT1 PR_NUM,
    PRH.requisition_header_id,
    XXTE_PUR_PKG.GET_PR_AMOUNT(PRH.REQUISITION_HEADER_ID) PR_Amount,
    'EGP' PR_Currency_Code,
    PRH.PREPARER_ID,
    HRM.FULL_NAME PR_PREPARER

    from po_requisition_headers_all prh,
    PO_REQUISITION_LINES_ALL PRL,
    HR_EMPLOYEES HRM
    where
    PRH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID(+)
    AND HRM.EMPLOYEE_ID(+)= PRH.PREPARER_ID
    )PR_EMP,


    (select INVD.INVOICE_ID,
    POD.Po_Distribution_Id,
    POR.Po_Release_Id,
    POR.Release_Num,
    POR.Po_Header_Id
    from po.po_releases_all POR,
    Po_Distributions_All POD,
    ap_invoice_distributions_all INVD

    where POD.Po_Release_Id = POR.PO_RELEASE_ID(+)
    AND POD.Po_Distribution_Id(+) = INVD.Po_Distribution_Id
    AND INVD.Assets_Addition_Flag = 'U'
    AND INVD.Line_Type_Lookup_Code = 'ACCRUAL'

    ) Rel_Inv,
    GL_CODE_COMBINATIONS GLCC


    where
    pohq.type_lookup_code(+) = 'QUOTATION'
    and pohq.from_type_lookup_code(+) = 'RFQ'
    and v.type_lookup_code(+) = 'RFQ'
    and po.from_header_id = pohq.po_header_id(+)
    and po.from_type_lookup_code(+) = 'QUOTATION'
    and po.type_lookup_code in('STANDARD','BLANKET','PLANNED')
    and PR_EMP.requisition_header_id(+) = V.attribute1

    AND GLCC.CODE_COMBINATION_ID = PO.CODE_COMBINATION_ID

    AND Rel_Inv.Invoice_Id(+) = inv.invoice_id

    and V.po_header_id(+) = pohq.from_header_id
    and po.po_header_id(+)=pol.po_header_id
    and pol.po_header_id=inv.po_header_id(+)
    and pol.po_line_id=inv.po_line_id(+)
    and pll.po_line_id(+) = pol.po_line_id
    AND HRL.LOCATION_ID(+) = PLL.Ship_To_Location_Id

    --AND NVL(HRL.ATTRIBUTE1,HRL.Attribute4) =50869--50863 --50862 -- 50998 --50869 -- 50870 --50865 -- Responsibility ID
    and (select min(v.req_num)
    from po_distributions_inq_v v
    where v.po_header_id = po.po_header_id
    ) is null

    AND PO.PO_header_id =26300

    union

    SELECT distinct
    Rel_Inv.Release_NUM,
    (SELECT SUM((quantity - quantity_cancelled) * price_override)
    FROM Po_Line_Locations_All
    WHERE po_release_id = Rel_Inv.po_release_id) Release_Total_Amount,
    glcc.segment1 || '.' || glcc.segment2 || '.' ||
    glcc.segment3 || '.' || glcc.segment4 || '.' ||
    glcc.segment5 || '.' || glcc.segment6 || '.' ||
    glcc.segment7 || '.' || glcc.segment8 || '.' ||
    glcc.segment9 charge_account,


    PO.ATTRIBUTE1,
    PO.attribute4,
    po.location_code,
    po.from_header_id,
    po.from_type_lookup_code,
    Pr.SEGMENT1 Ref_No,
    decode(Pr.SEGMENT1,null,'','PR') Ref_Type,
    po.segment1 PO_NO,
    nvl(po.authorization_status,'Incomplete') Status,
    po.comments po_desc,
    po.po_type,
    po.currency_code PO_currency_code,
    XXTE_PUR_PKG.get_po_amount(PO.PO_HEADER_ID) PO_AMOUNT,
    po.creation_date,
    po.line_num,
    po.item_description,
    inv.invoice_num,
    inv.invoice_amount,
    inv.invoice_date,
    inv.invoice_currency_code,
    nvl(inv.amount_remaining,-1) "REMAINING AMOUNT",

    PR.segment1 PR_NUM,
    PR.amount PR_AMOUNT,
    PR.Currency_code PR_CURRENCY,
    pr.FULL_NAME PR_PREPARER,
    po.po_header_id



    FROM (SELECT distinct POH.VENDOR_ID,
    HRL.Attribute1,
    HRL.Attribute4,
    hrl.location_code ,
    poh.comments,
    POD1.REQ_HEADER_REFERENCE_NUM,
    poh.agent_id,
    poh.currency_code,
    pol.line_num,
    pol.item_description,
    pol.po_line_id,
    POD1.REQ_DISTRIBUTION_ID,
    POh.SHIP_TO_LOCATION_ID,
    poh.creation_date,
    pol.po_header_id,
    poh.authorization_status,
    initcap(POH.type_lookup_code) po_type,
    poh.approved_date,
    POH.SEGMENT1,
    PLL.LINE_LOCATION_ID,
    POH.type_lookup_code,
    poh.from_header_id,
    poh.from_type_lookup_code,
    POD1.CODE_COMBINATION_ID

    FROM PO_HEADERS_ALL POH,
    PO_DISTRIBUTIONS_all POD1,
    po_lines_all POL,
    PO_LINE_LOCATIONS_ALL pll,
    Hr_Locations_All HRL

    WHERE PLL.LINE_LOCATION_ID = POD1.LINE_LOCATION_ID(+)
    and pll.po_line_id = pol.po_line_id
    and pol.PO_HEADER_ID = poh.po_header_id
    AND HRL.LOCATION_ID = PLL.Ship_To_Location_Id
    AND poH.type_lookup_code in ('STANDARD','BLANKET','PLANNED')) PO,

    (select distinct PORD.DISTRIBUTION_ID,
    PORL.Cancel_Date,
    porh.preparer_id,
    porh.attribute1,
    pOrh.segment1,
    porh.authorization_status,
    porh.CREATION_DATE,
    porh.requisition_header_id,
    porl.line_num,
    hrm.full_name,
    porh.approved_date,
    porh.closed_code,

    porh.description,
    XXTE_PUR_PKG.GET_PR_AMOUNT(PORH.REQUISITION_HEADER_ID) Amount,
    'EGP' Currency_Code,
    PORL.Deliver_To_Location_Id ,
    PORH.REQUISITION_HEADER_ID ID
    from PO_REQUISITION_HEADERS_ALL PORH,
    PO_REQUISITION_LINES_ALL PORL,
    PO_REQ_DISTRIBUTIONS_ALL PORD,
    HR_EMPLOYEES HRM
    where PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
    AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID(+)
    AND PoRl.Cancel_Date is NULL
    AND HRM.EMPLOYEE_ID(+)= PoRh.PREPARER_ID
    ) pr,

    (select invl.po_header_id,
    invl.po_line_id,
    inv.invoice_num,
    inv.invoice_id,
    inv.invoice_date,
    inv.invoice_currency_code,
    nvl(inv.INVOICE_AMOUNT,0) INVOICE_AMOUNT ,
    sum(nvl(apps.amount_remaining,0)) amount_remaining,
    nvl(inv.AMOUNT_PAID,0) AMOUNT_PAID
    from AP_INVOICES_ALL inv,
    AP_INVOICE_LINES_ALL invl,
    AP_PAYMENT_SCHEDULES_all APPS
    where inv.invoice_id=invl.invoice_id(+)
    and inv.invoice_id=apps.invoice_id(+)
    and inv.cancelled_date is null
    group by invl.po_header_id,
    invl.po_line_id,
    inv.invoice_num,
    inv.invoice_id,
    inv.invoice_date,
    inv.invoice_currency_code,
    nvl(inv.INVOICE_AMOUNT,0) ,
    nvl(inv.AMOUNT_PAID,0) ) inv,

    (select INVD.INVOICE_ID,
    POD.Po_Distribution_Id,
    POR.Po_Release_Id,
    POR.Release_Num,
    POR.Po_Header_Id
    from po.po_releases_all POR,
    Po_Distributions_All POD,
    ap_invoice_distributions_all INVD

    where POD.Po_Release_Id = POR.PO_RELEASE_ID(+)
    AND POD.Po_Distribution_Id(+) = INVD.Po_Distribution_Id
    AND INVD.Assets_Addition_Flag = 'U'
    AND INVD.Line_Type_Lookup_Code = 'ACCRUAL'

    ) Rel_Inv,
    GL_CODE_COMBINATIONS GLCC


    WHERE (PO.REQ_DISTRIBUTION_ID = pr.DISTRIBUTION_ID(+))

    AND GLCC.CODE_COMBINATION_ID = PO.CODE_COMBINATION_ID

    AND Rel_Inv.Invoice_Id(+) = inv.invoice_id
    and nvl(po.from_type_lookup_code,'STANDARD') <> 'QUOTATION'
    and po.po_header_id=inv.po_header_id(+)
    and po.po_line_id=inv.po_line_id(+)
    --AND NVL(PO.ATTRIBUTE1,PO.Attribute4) =50869--50863 -- 50998 --50869-- 50870 -- 50865 -- Responsibility ID

    AND PO.PO_Header_ID= 26300
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your friendly, neighborhood DBA needs to help you with this one; end-of-file errors are usually generated by other errors reported in the alert log. The root cause will be one or more of these errors the alert log reports; fixing those will usually fix your end-of-file issue.