-- View: lms_app.paymenttransdetails_view

-- DROP VIEW lms_app.paymenttransdetails_view;

CREATE OR REPLACE VIEW lms_app.paymenttransdetails_view
 AS
 SELECT paybase.due_date AS duedate,
    itemvendorbase.payment_amount AS amount,
    lineitem.payment_item_type AS lineitemtype,
    checkrequest.payment_status_f AS status,
    docidassoc.land_contract_id AS contractno,
    paygroup.payment_group_number AS paygroupno,
    paytypelkup.drop_down_display AS paygrptype,
    (((COALESCE(lineitemacct.resp_code, '    '::character varying)::text || '-'::text) || lineitemacct.major_code::text) || '-'::text) || lineitemacct.minor_code::text AS account,
    (itemvendorbase.vendor_number_c::text || '-'::text) || itemvendorbase.vendor_suffix_c::text AS vendorno,
    checkrequest.check_number AS checkno,
    paygroup.doc_sid_f AS docsid,
    paygroup.payment_group_sid_k AS paygroupsid,
    paybase.payment_sid_k AS paymentsid,
    paytypelkup.er_fp,
    paytypelkup.recoupable_yn,
    paybase.period_start AS periodstartdate,
    paybase.period_end AS periodenddate,
    docarea.currentarearespcode,
    checkrequest.sap_document_number AS sapdocumentnumber,
    sapvendor.sap_vendor_number AS sapvendornumber,
    itemvendorbase.currency_code,
    lineitemacct.sap_company_code AS sapcompanycode,
    lineitemacct.sap_gl_account AS sapglaccount,
    lineitemacct.sap_profit_center AS sapprofitcenter,
    lineitemacct.sap_cost_center AS sapcostcenter
   FROM lms_payment_group_base paygroup,
    lms_payment_type_lkup paytypelkup,
    lms_pay_base paybase,
    lms_payment_line_item_base lineitem,
    lms_payment_check_request checkrequest,
    lms_pay_line_item_acct_base lineitemacct,
    lms_doc_id_assoc docidassoc,
    doccurrentarea_view docarea,
    lms_payment_item_vendor_base itemvendorbase
     LEFT JOIN sap_to_legacy_view sapvendor ON (itemvendorbase.vendor_number_c::text || itemvendorbase.vendor_suffix_c::text) = sapvendor.legacy_vendor_number
  WHERE paytypelkup.payment_type_k::text = paygroup.payment_type_f::text AND paygroup.doc_sid_f::text = docidassoc.doc_sid_c::text AND paygroup.payment_group_sid_k::text = paybase.payment_group_sid_f::text AND paybase.payment_sid_k::text = itemvendorbase.payment_sid_c::text AND paybase.payment_sid_k::text = lineitem.payment_sid_c::text AND lineitem.line_item_seq_k = itemvendorbase.line_item_seq_c::numeric AND paybase.payment_sid_k::text = checkrequest.payment_sid_c::text AND checkrequest.vendor_number_c::text = itemvendorbase.vendor_number_c::text AND checkrequest.vendor_suffix_c::text = itemvendorbase.vendor_suffix_c::text AND paybase.payment_sid_k::text = lineitemacct.payment_sid_c::text AND lineitem.line_item_seq_k = lineitemacct.line_item_seq_c::numeric AND paygroup.doc_sid_f::text = docarea.contractsid::text;



