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

Pages

Friday, 11 May 2018

AP Invoice Payment Audit Query In Oracle Apps

0 comments



SELECT   acv.payment_type,
         acv.bank_account_name,
         acv.check_stock_name document_name,
         acv.check_number document_num,
         acv.check_date payment_date,
         acv.amount,
         acv.currency_code,
         acv.payment_method,
         acv.check_status,
         acv.doc_sequence_value voucher_number,
         aip.INVOICE_NUM,
         aip.INVOICE_DATE,
         aip.INVOICE_AMOUNT,
         aip.AMOUNT,
         aip.AMOUNT_PAID,
         aip.DESCRIPTION
  FROM   AP_CHECKS_V acv, ap_invoice_payments_v aip
 WHERE   acv.check_id = aip.CHECK_ID --  and acv.check_number = 422063
         AND UPPER (aip.DESCRIPTION) LIKE '%REJECT%'

Continue reading >>

Profile Options in Oracle Apps

0 comments



Profile Options are used to determine the behavior of oracle application forms and programs. Each profile option can be set at 4 levels

Site:
 If you define profile option at site level it is valid for entire server.

Application:
If you define profile option at application level it is valid for particular application.

Responsibility:
If you define profile option at responsibility level it is Valid for only particular Responsibility.

Organization:
If you define profile option at organization level than it valid for particular organization only. The organization like Business Group and Operating Unit.

User:
If you define profile option at user level that it is valid for particular user.

Continue reading >>

Sunday, 8 April 2018

Query Cheque Stock Register Details

0 comments


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

Continue reading >>