Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 8 April 2018

Query Cheque Stock Register Details



SELECT   TO_CHAR (aca.creation_date, 'DD-MON-YYYY') CREATION_DATE,
           INT_BANK_NAME,
           INT_BANK_ACCOUNT_NUMBER,
           aca.check_number,
           cpd.payment_document_name,
           cpd.first_available_document_num Rec_bal_from,
           cpd.last_available_document_number Rec_bal_to,
           (SELECT   MIN (check_number)
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              Open_bal_from,
           cpd.last_available_document_number Open_bal_to,
           (SELECT   MIN (check_number)
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              issue_bal_from,
           (SELECT   MAX (check_number)
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              ISSUE_BAL_TO,
           (SELECT   MAX (check_number) + 1
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              CLOSE_BAL_FROM,
           cpd.last_available_document_number clsoe_bal_to,
           DECODE (aca.STATUS_LOOKUP_CODE, 'VOIDED', 'Yes', 'No') VOIDED,
           ipa.payment_date,
           PAYEE_SUPPLIER_NUMBER,
           (SELECT   vendor_name
              FROM   ap_suppliers
             WHERE   vendor_id = ipa.INV_PAYEE_SUPPLIER_ID)
              vendor_name,
           (SELECT   vendor_site_code
              FROM   ap_supplier_sites_all
             WHERE   vendor_id = ipa.INV_PAYEE_SUPPLIER_ID
                     AND vendor_site_id = ipa.SUPPLIER_SITE_ID)
              site_code,
           ipa.PAYMENT_METHOD_CODE,
           aca.void_date gl_date
    FROM   ap_checks_All aca, iby_payments_all ipa, ce_payment_documents cpd
   WHERE       ipa.paper_document_number = aca.check_number
           AND ipa.PAYMENT_ID = aca.PAYMENT_ID
           AND aca.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
ORDER BY   aca.CREATION_DATE,
           INT_BANK_NAME,
           INT_BANK_ACCOUNT_NAME,
           CHECK_NUMBER

Share this article :

0 comments:

Post a Comment