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!

need to add prepayment column in below query

Discussion in 'Oracle SCM & Manufacturing' started by srikanthkota, Oct 7, 2011.

  1. srikanthkota

    srikanthkota Guest

    SELECT ASS.vendor_name supplier_name,
    TRUNC(AIP.accounting_date),
    ASS.segment1 supplier_number,
    ASSA.address_line1,
    ASSA.address_line2,
    ASSA.address_line3,
    ASSA.address_line4,
    ASSA.city,
    ASSA.state,
    ASSA.zip ,
    HOU.ATTRIBUTE6 phone_number,
    HOU.ATTRIBUTE7 fax_Number,
    HOU.ATTRIBUTE8 url,
    AIA.invoice_num,
    ACA.check_number check1,
    -- ACA.check_date,
    ACA.payment_method_code,
    TRUNC(AIA.invoice_date)invoice_date,
    AIA.invoice_amount,
    ACA.check_number,
    ACA.currency_code,
    ACA.check_date payment_date,
    NVL(AIP.discount_taken,0) discount ,
    NVL(AIP.amount,0) payment_amt,
    XFIV.name leg_entname,
    XFIV.ADDRESS_LINE_1 leg_add1,
    XFIV.ADDRESS_LINE_2 leg_add2,
    XFIV.ADDRESS_LINE_3 leg_add3,
    XFIV.TOWN_OR_CITY leg_city,
    XFIV.POSTAL_CODE leg_postalcode,
    iby1.payment_method_name
    -- (AIA.invoice_amount - AIP.amount) invoice_amount,
    -- APSA.amount_remaining invoice_amount,
    -- AIP.invoice_base_amount,
    FROM ap_suppliers ASS,
    ap_supplier_sites_all ASSA,
    ap_invoices_all AIA,
    ap_invoice_payments_all AIP,
    ap_payment_schedules_all APSA,
    ap_checks_all ACA,
    xle_firstparty_information_v XFIV,
    hr_all_organization_units HOU,
    gl_ledger_config_details GLCD,
    gl_ledgers GL ,
    iby_payment_methods_vl IBY1
    -- hr_operating_units HOU,
    -- ap_invoice_payment_history_v AIPHV,
    WHERE ASS.vendor_id = ASSA.vendor_id
    AND ASS.vendor_id = ACA.vendor_id
    AND ASSA.org_id = AIP.org_id
    AND ASSA.org_id = HOU.organization_id
    AND ASSA.org_id = AIA.org_id
    AND ASSA.vendor_site_id = AIA.vendor_site_id
    AND AIP.check_id = ACA.check_id
    AND AIP.org_id = ACA.org_id
    AND AIP.invoice_id = AIA.invoice_id
    AND AIP.invoice_id = APSA.invoice_id
    AND APSA.invoice_id = AIA.invoice_id
    AND AIP.org_id = APSA.org_id
    AND AIA.invoice_id = AIP.invoice_id
    AND AIA.org_id = AIP.org_id
    AND ACA.payment_method_code = IBY1.payment_method_code(+)
    AND AIA.legal_entity_id = XFIV.legal_entity_id
    AND AIP.org_id = HOU.organization_id
    AND GLCD.object_id = XFIV.legal_entity_id
    AND GLCD.configuration_id = GL.configuration_id
    AND GLCD.object_type_code = 'LEGAL_ENTITY'
    --Parameters--
    AND ASS.vendor_id = NVL:)P_SUPPLIER_NAME,ASS.vendor_id)
    AND ACA.check_number = NVL:)P_DOCUMENT_REF,ACA.check_number)
    AND HOU.organization_id = :p_ORG_ID
    --AND TRUNC(AIP.accounting_date) = TRUNC:)P_PAYMENT_DATE)
    AND TRUNC(AIP.accounting_date) BETWEEN NVL:)P_FROM_DATE,TRUNC(AIP.accounting_date)) AND NVL:)P_TO_DATE,TRUNC(AIP.accounting_date))
    AND GL.ledger_id = :p_LEDGER
    /* -- AND ASS.segment1 = :p_supplier_number
    -- AND ACA.check_date = :p_payment_date */
    GROUP BY ASS.vendor_name,
    trunc(AIP.accounting_date),
    ASS.segment1,
    ASSA.address_line1,
    ASSA.address_line2,
    ASSA.address_line3,
    ASSA.address_line4,
    ASSA.city,
    ASSA.state,
    ASSA.zip,
    hou.ATTRIBUTE6,
    hou.ATTRIBUTE7 ,
    hou.ATTRIBUTE8 ,
    aca.check_date,
    aca.payment_method_code,
    iby1.payment_method_name,
    AIA.invoice_num,
    AIA.invoice_date,
    AIA.invoice_amount,
    aca.check_number,
    ACA.currency_code,
    -- APSA.amount_remaining,
    AIP.discount_taken,
    AIP.amount,
    XFIV.name,
    XFIV.address_line_1,
    XFIV.address_line_2,
    XFIV.address_line_3,
    XFIV.town_or_city,
    XFIV.postal_code
    ORDER BY ASS.vendor_name,ACA.check_number, AIA.invoice_num
    --ACA.check_number,ACA.check_date,TRUNC(AIA.invoice_date)