Script to extract Expenses SLA Details

SELECT aerh.invoice_num "Report_Number", papf.full_name "Employee_Name",
       papf.employee_number "Emp_Number", hou.NAME "Operating_Unit",
       aerh.total "Amount", aerh.default_currency_code "Currency",
       (SELECT meaning
          FROM apps.fnd_lookup_values
         WHERE lookup_type = 'EXPENSE REPORT STATUS'
           AND LANGUAGE = 'US'
           AND lookup_code = aerh.expense_status_code) "Report_status",
       (SELECT user_name
          FROM apps.fnd_user
         WHERE user_id = aerh.last_audited_by) "Last_Audited_By",
       DECODE (aerh.audit_code,
               'PAPERLESS_AUDIT', DECODE (aerh.workflow_approved_flag,
                                          'N', 'Y',
                                          'M', 'Y',
                                          NULL, 'Y',
                                          'N'
                                         ),
               'RECEIPT_BASED', DECODE (aerh.workflow_approved_flag,
                                        'N', 'Y',
                                        'M', 'Y',
                                        NULL, 'Y',
                                        'N'
                                       ),
               'N'
              ) "Requires_Audit",
       NVL (TO_CHAR (aerh.report_submitted_date, 'DD-MON-YYYY'),
            TO_CHAR (aerh.creation_date, 'DD-MON-YYYY')
           ) "Report_submit_date",
       (SELECT TO_CHAR (NVL (an.last_update_date, an.entered_date),
                        'DD-MON-YYYY'
                       )
          FROM apps.ap_notes an
         WHERE an.source_object_code = 'OIE_EXPENSE_REPORT'
           AND an.source_object_id = aerh.report_header_id
           AND TO_CHAR (SUBSTR (an.notes_detail, 1, 24)) =
                                                    'Approver Action: Approve'
           AND ROWNUM = 1) "Claim_Approved_Date",
       (CASE
           WHEN aerh.expense_status_code = 'RESOLUTN'
              THEN TO_CHAR (aerh.last_update_date, 'DD-MON-YYYY')
           ELSE NULL
        END
       ) "Pending_Your_Resolution_Date",
       (SELECT TO_CHAR (NVL (an.last_update_date, an.entered_date),
                        'DD-MON-YYYY'
                       )
          FROM apps.ap_notes an
         WHERE an.source_object_code = 'OIE_EXPENSE_REPORT'
           AND an.source_object_id = aerh.report_header_id
           AND TO_CHAR (SUBSTR (an.notes_detail, 1, 15)) = 'Complete Audit.'
           AND ROWNUM = 1) "Audit_Complete_Date"
  FROM apps.ap_expense_report_headers_all aerh,
       apps.per_all_people_f papf,
       apps.hr_operating_units hou
 WHERE papf.person_id = aerh.employee_id
   AND hou.organization_id = aerh.org_id
   AND papf.current_employee_flag = 'Y'
   AND TRUNC (SYSDATE) BETWEEN TRUNC (PAPF.EFFECTIVE_START_DATE) AND TRUNC (PAPF.EFFECTIVE_END_DATE) 
   AND aerh.org_id = FND_PROFILE.VALUE('ORG_ID')

               
   

AP Web Report Workflow Status

SELECT 'WPAY' TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) - TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           TO_CHAR (h.report_header_id) report_header_id,
           h.invoice_num invoice_num, h.description description,
           p.full_name requestor, h.flex_concatenated district, h.total total,
           'Payables' current_approver
      FROM ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl
     WHERE p.person_id = h.employee_id
       AND gl.code_combination_id = h.employee_ccid
       AND h.workflow_approved_flag = 'M'
    UNION ALL
    SELECT 'WSYS' TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) - TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           wfi.item_key report_header_id, h.invoice_num invoice_num,
           h.description description, p.full_name requestor,
           h.flex_concatenated district, h.total total,
           DECODE (SUBSTR (wfi.assigned_user, 1, 3),
                   'FND', 'SYSADMIN:TO',
                   wfi.assigned_user
                  ) current_approver
      FROM wf_item_activity_statuses wfi,
           ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl
     WHERE wfi.item_key = TO_CHAR (h.report_header_id)
       AND h.employee_ccid = gl.code_combination_id(+)
       AND p.person_id = h.employee_id
       AND wfi.item_type = 'APEXP'
       AND wfi.activity_status = 'NOTIFIED'
       AND wfi.end_date IS NULL
       AND (   wfi.assigned_user = 'SYSADMIN'
            OR wfi.assigned_user LIKE 'FND_RESP%'
           )
    UNION ALL
    SELECT 'WTRM' TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) - TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           wfi.item_key report_header_id, h.invoice_num invoice_num,
           h.description description, p.full_name requestor,
           h.flex_concatenated district, h.total total,
           wfi.assigned_user current_approver
      FROM wf_item_activity_statuses wfi,
           ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl,
           per_assignments_x a
     WHERE wfi.item_key = TO_CHAR (h.report_header_id)
       AND gl.code_combination_id = h.employee_ccid
       AND p.person_id = h.employee_id
       AND p.person_id = a.person_id
       AND a.primary_flag = 'Y'
       AND wfi.item_type = 'APEXP'
       AND wfi.activity_status = 'NOTIFIED'
       AND wfi.end_date IS NULL
       AND wfi.assigned_user IS NOT NULL
       AND h.workflow_approved_flag IS NULL
       AND hr_person_type_usage_info.get_user_person_type (SYSDATE,
                                                           a.supervisor_id
                                                          ) LIKE
                                                                'Ex-employee%'
    UNION ALL
    SELECT 'SPAY' TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) - TRUNC (h.report_submitted_date)) days_old,
           NULL timeout_date, NULL manager_level,
           TO_CHAR (h.report_header_id) report_header_id,
           h.invoice_num invoice_num, h.description description,
           p.full_name requestor, h.flex_concatenated district, h.total total,
           DECODE (h.workflow_approved_flag,
                   'Y', 'Complete',
                   'A', 'Complete',
                   'M', 'Mgr Approved',
                   'S', 'Saved',
                   'R', 'Rejected',
                   'P', 'AP Approved',
                   NULL, 'Waiting',
                   NULL
                  ) current_approver
      FROM ap_expense_report_headers_all h,
           per_people_x p,
           gl_code_combinations gl
     WHERE p.person_id = h.employee_id
       AND gl.code_combination_id = h.employee_ccid
       AND h.invoice_num LIKE 'WEB%-1'
    UNION ALL
    SELECT 'WMGR' TYPE, gl.segment1 division, gl.segment3 dcc,
           gl.segment4 ccc, h.report_submitted_date creation_date,
           (TRUNC (SYSDATE) - TRUNC (h.report_submitted_date)) days_old,
           notif.timeout_date, notif.manager_level,
           wfi.item_key report_header_id, h.invoice_num invoice_num,
           h.description description, p.full_name requestor,
           h.flex_concatenated district, h.total total,
           DECODE (SUBSTR (wfi.assigned_user, 1, 3),
                   'FND', 'SYSADMIN:TO',
                   wfi.assigned_user
                  ) current_approver
      FROM wf_item_activity_statuses wfi,
           ap_expense_report_headers_all h,
           fnd_user u,
           per_people_x p,
           gl_code_combinations gl,
           (SELECT SUBSTR (CONTEXT, 7, 6) CONTEXT,
                   TRUNC (due_date) timeout_date, message_name,
                   DECODE (message_name,
                           'OIE_REQUEST_EMPLOYEE_APPROVAL', '0',                         
                           'TIMED-OUT'
                          ) manager_level
              FROM wf_notifications
             WHERE MESSAGE_TYPE = 'APEXP' AND status = 'OPEN') notif
     WHERE wfi.item_key = TO_CHAR (h.report_header_id)
       AND notif.CONTEXT = h.report_header_id
       AND gl.code_combination_id = h.employee_ccid
       AND wfi.assigned_user = u.user_name(+)
       AND p.person_id = h.employee_id
       AND wfi.item_type = 'APEXP'
       AND wfi.activity_status = 'NOTIFIED'
       AND wfi.end_date IS NULL
       AND (h.workflow_approved_flag IS NULL OR h.workflow_approved_flag = 'P'
           )
       AND wfi.assigned_user != 'SYSADMIN'
       AND SUBSTR (wfi.assigned_user, 1, 3) != 'FND'

Script to extract Goods Received Date for Suppliers along with Invoice and PO Information

select (select hou.name from apps.hr_operating_units hou
where organization_id = (select org_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
                fetch first 1 row only)) "Entity_OU",
       TO_CHAR(ap.creation_date,'DD-MON-YYYY') "Vendor_Creation_Date",
       fu.user_name "Vendor_Created_by",
       ap.VENDOR_NAME "Vendor_Name",
       ap.SEGMENT1 "Vendor_Num", 
       (CASE
       WHEN ap.END_DATE_ACTIVE is NULL
       THEN 'Active'
       WHEN ap.END_DATE_ACTIVE is NOT NULL AND trunc(ap.END_DATE_ACTIVE) <= TRUNC(SYSDATE)
       THEN 'Inactive'
       ELSE 'Inactive'
       END)  "Vendor_Status",
        (select  vendor_site_code from apps.ap_supplier_sites_all
        where vendor_site_id = (select vendor_site_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)) "Vendor_Site",
       (select to_char(invoice_date,'DD-MON-YYYY') from apps.ap_invoices_all
       where vendor_id = ap.vendor_id
       order by creation_date
fetch first 1 row only) "First_Invoice_Date",
   (select user_name from apps.fnd_user
   where user_id = (select created_by from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only))  "Invoice_Created_By",
 (select  invoice_num from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Number",
 (select  invoice_amount from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Amount",
 (select  INVOICE_CURRENCY_CODE from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Currency",
 (select  DESCRIPTION from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Description",
 (select  AP_INVOICES_PKG.GET_APPROVAL_STATUS(apa.invoice_id,
apa.invoice_amount,
apa.payment_status_flag,
apa.invoice_type_lookup_code) from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Status",
(SELECT distinct to_char(pha.creation_date, 'DD_MON-YYYY')     
  FROM po_headers_all pha
       ,po_distributions_all pda
       ,ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Creation_Date",
(SELECT distinct to_char(pha.APPROVED_DATE, 'DD_MON-YYYY')     
  FROM po_headers_all pha
       ,po_distributions_all pda
       ,ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Approval_Date",
   ( select user_name from apps.fnd_user where user_id =   (SELECT distinct pha.created_by     
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  )) "PO_Created_By",
(SELECT distinct pha.segment1   
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Number",
( select sum(NVL(unit_price,0) * NVL(quantity,0)) from apps.po_lines_all pla
where po_header_id =   (SELECT distinct pha.po_header_id 
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  )) "PO_Amount",
(SELECT distinct pha.CURRENCY_CODE   
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Currency",
(SELECT distinct pha.COMMENTS   
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Description"
from apps.ap_suppliers ap,
apps.fnd_user fu
where fu.user_id = ap.created_by

Vendor Ledger Report Query

SELECT flag,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency,

  SUM(entered_dr) entered_dr,

  SUM(entered_cr) entered_cr,

  SUM(accounted_dr) accounted_dr,

  SUM(accounted_cr) accounted_cr

FROM

  (SELECT 1 flag,

    ap.invoice_id invoice_id,

    aps.segment1 vendor_num,

    aps.vendor_name vendor_name,

    ap.VENDOR_SITE_ID vendor_site_id,

    apss.vendor_site_code vendor_site_code,

    apss.state state_code,

    gl.segment2 project_code,

    ap.invoice_type_lookup_code transaction_type,

    ap.invoice_num transaction_num,

    fdsc.name doc_category_name,

    ap.voucher_num document_num,

    (SELECT poh.segment1

    FROM po_headers_all poh,

      po_distributions_all pod,

      ap_invoice_distributions_all apd

    WHERE poh.po_header_id    =pod.po_header_id

    AND pod.po_distribution_id=apd.po_distribution_id

    AND apd.invoice_id        =ap.invoice_id

    AND rownum                =1

    ) po_number,

    ap.invoice_date transaction_date,

    ap.gl_date gl_date,

    DECODE(ap_invoices_pkg.get_posting_status( ap.invoice_id),'Y','Accounted','P','Partial','Unaccounted') status,

    ap.description description,

    ap.invoice_currency_code currency,

    DECODE(xl.accounting_class_code,'LIABILITY',NVL(xl.entered_dr,0),'PREPAID_EXPENSE',-1*xl.entered_cr,0) entered_dr,

    DECODE(xl.accounting_class_code,'LIABILITY',NVL(xl.entered_cr,0),0) entered_cr,

    DECODE(xl.accounting_class_code,'LIABILITY',NVL(xl.accounted_dr,0),'PREPAID_EXPENSE',-1*xl.accounted_cr,0) accounted_dr,

    DECODE(xl.accounting_class_code,'LIABILITY', NVL(xl.accounted_cr,0),0) accounted_cr

  FROM ap_invoices_all ap ,

    ap_suppliers aps,

    ap_supplier_sites_all apss,

    gl_code_combinations gl,

    FND_DOC_SEQUENCE_CATEGORIES FDSC,

    xla.xla_transaction_entities xte,

    xla_events xe,

    xla_ae_lines xl,

    xla_ae_headers xah

  WHERE ap_invoices_pkg.get_posting_status( ap.invoice_id) IN ('Y','P')

  AND ap.invoice_type_lookup_code                          <>'PREPAYMENT'

  AND gl.code_combination_id                                =ap.accts_pay_code_combination_id

  AND FDSC.CODE (+)                                         = ap.DOC_CATEGORY_CODE

  AND FDSC.table_name (+)                                   = 'AP_INVOICES_ALL'

  AND aps.VENDOR_ID                                         = ap.VENDOR_ID

  AND ap.VENDOR_SITE_ID                                     =apss.VENDOR_SITE_ID

  AND TRUNC(xah.accounting_date) BETWEEN :CP_GL_FROM_DATE AND :CP_GL_TO_DATE

  AND ap.org_id                   =:p_org_id

  AND ap.vendor_id                =:p_vendor_id

  AND source_id_int_1             =ap.invoice_id

  AND xte.SOURCE_ID_INT_1         =ap.invoice_id

  AND xte.ENTITY_CODE             ='AP_INVOICES'

  AND xe.entity_id                =xte.entity_id

  AND xe.event_id                 =xah.event_id

  AND xah.gl_transfer_status_code ='Y'

  AND xl.ae_header_id             =xah.ae_header_id

  AND xl.accounting_class_code   IN ('LIABILITY','PREPAID_EXPENSE')

  )

GROUP BY flag,

  invoice_id,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency

  

  UNION ALL

  

SELECT flag,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency,

  SUM(entered_dr) entered_dr,

  SUM(entered_cr) entered_cr,

  SUM(accounted_dr) accounted_dr,

  SUM(accounted_cr) accounted_cr

FROM

  (SELECT 2 flag,

    apc.check_id check_id,

    aps.segment1 vendor_num,

    aps.vendor_name vendor_name,

    -- null pan,

    apc.VENDOR_SITE_ID vendor_site_id,

    apss.vendor_site_code vendor_site_code,

    --NULL gstin,

    apss.state state_code,

    NULL project_code,

    'PAYMENT' transaction_type,

    TO_CHAR( apc.check_number) transaction_num,

    fdsc.name doc_category_name,

    TO_CHAR(apc.check_voucher_num) document_num,

    NULL po_number,

    apc.check_date transaction_date,

    xah.accounting_date gl_date,

    DECODE(xah.gl_transfer_status_code,'Y','Accounted','Unaccounted') status,

    apc.description description,

    apc.currency_code currency,

    NVL(xl.entered_dr,0) entered_dr,

    NVL(xl.entered_cr,0) entered_cr,

    NVL(xl.accounted_dr,0) accounted_dr,

    NVL(xl.accounted_cr,0) accounted_cr

  FROM ap_checks_all apc ,

    xla.xla_transaction_entities xte,

    xla_events xe,

    xla_ae_lines xl,

    xla_ae_headers xah,

    ap_suppliers aps,

    ap_supplier_sites_all apss,

    FND_DOC_SEQUENCE_CATEGORIES FDSC

  WHERE xah.gl_transfer_status_code ='Y'

  AND FDSC.CODE (+)                 = apc.DOC_CATEGORY_CODE

  AND FDSC.table_name (+)           = 'AP_CHECKS_ALL'

  AND xte.SOURCE_ID_INT_1           =apc.check_id

  AND xte.ENTITY_CODE               ='AP_PAYMENTS'

  AND xe.entity_id                  =xte.entity_id

  AND xe.event_id                   =xah.event_id

  AND xl.ae_header_id               =xah.ae_header_id

  AND xl.accounting_class_code      ='LIABILITY'

  AND aps.VENDOR_ID(+)              = apc.VENDOR_ID

  AND apc.VENDOR_SITE_ID            =apss.VENDOR_SITE_ID (+)

  AND TRUNC(xah.accounting_date) BETWEEN :CP_GL_FROM_DATE AND :CP_GL_TO_DATE

  AND apc.org_id    =:p_org_id

  AND apc.vendor_id =:p_vendor_id

  )

GROUP BY flag,

check_id,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency

  

ORDER BY vendor_num,

  vendor_site_code,

  gl_date,

  transaction_date;

Query for Supplier details with banks

SELECT         

                aps.vendor_name|| ' ~ '|| ass.vendor_site_code "Payee_Identifier"

                ,accts.ext_bank_account_id "Payee_Bank_Account_Identifier"

                ,bank.party_name "bank_name"

                ,branch.bank_branch_name "Branch_Name"

                ,accts.country_code "account_country_code"

                ,accts.bank_account_name "Account_Name"

                ,accts.bank_account_num "Account_Number"

                ,accts.currency_code "Account_Currency_Code"

                ,NULL "Allow_International_Payments"

                ,uses.start_date "Account_start_date"

                ,uses.end_date "Account_end_date"

                ,accts.iban "IBAN"

                ,accts.check_digits "check_digits"

                ,accts.bank_account_name_alt "Account_Alternate_Name"

                ,accts.bank_account_type "Bank_account_type"

                ,accts.account_suffix "Account_Suffix"

                ,accts.description "DESCRIPTION"

                ,accts.agency_location_code "Agency_Location_Code"

                ,accts.exchange_rate_agreement_num   "Exchange_Rate_Agreement_Number"

                ,accts.exchange_rate_agreement_type "Exchange_Rate_Agreement_Type"

                ,accts.exchange_rate "EXCHANGE_RATE"

                ,accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE"

           FROM  ap_suppliers aps

                ,ap_supplier_sites_all ass

                ,iby_pmt_instr_uses_all uses

                ,iby_external_payees_all payee

                ,iby_ext_bank_accounts accts

                ,hz_parties bank

                ,ce_bank_branches_v branch

          WHERE uses.instrument_type   = 'BANKACCOUNT'

            AND aps.vendor_id          = ass.vendor_id

            AND aps.party_id           = payee.payee_party_id

            AND payee.ext_payee_id     = uses.ext_pmt_party_id

            AND payee.payment_function = 'PAYABLES_DISB'

            AND uses.instrument_id     = accts.ext_bank_account_id

            AND accts.bank_id          = bank.party_id

            --AND accts.bank_id          = bankprofile.party_id

            AND accts.branch_id         = branch.branch_party_id

            AND aps.end_date_active  IS NULL

            AND ass.inactive_date    IS NULL