Query for CHART OF ACCOUNTS in Oracle EBS R12

 

Query for CHART OF ACCOUNTS in Oracle EBS R12

SELECT GCC.CODE_COMBINATION_ID,
       GCC.SEGMENT1||'.'||
       GCC.SEGMENT2||'.'||
       GCC.SEGMENT3||'.'||
       GCC.SEGMENT4||'.'||
       GCC.SEGMENT5||'.'||
       GCC.SEGMENT6||'.'||
       GCC.SEGMENT7||'.'||
       GCC.SEGMENT8,
       SUBSTR (
          APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
             GCC.CHART_OF_ACCOUNTS_ID,
             1,
             GCC.SEGMENT1),
          1,
          40)
          SEGMENT1_DESC
,
       SUBSTR (
          APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
             GCC.CHART_OF_ACCOUNTS_ID,
             2,
             GCC.SEGMENT2),
          1,
          40)
          SEGMENT2_DESC,
       DECODE (
          GCC.SEGMENT3,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                3,
                GCC.SEGMENT3),
             1,
             40))
          SEGMENT3_DESC,
       DECODE (
          GCC.SEGMENT4,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                4,
                GCC.SEGMENT4),
             1,
             40))
          SEGMENT4_DESC,
       DECODE (
          GCC.SEGMENT5,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                5,
                GCC.SEGMENT5),
             1,
             40))
          SEGMENT5_DESC,
       DECODE (
          GCC.SEGMENT6,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                6,
                GCC.SEGMENT6),
             1,
             40))
          SEGMENT6_DESC,
       DECODE (
          GCC.SEGMENT7,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                7,
                GCC.SEGMENT7),
             1,
             40))
          SEGMENT7_DESC,
       DECODE (
          GCC.SEGMENT9,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                8,
                GCC.SEGMENT8),
             1,
             40))
          SEGMENT8_DESC,
       GCC.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,
       GCC.ACCOUNT_TYPE
  FROM GL_CODE_COMBINATIONS GCC
  WHERE GCC.CODE_COMBINATION_ID = 2291

Query: Cancelled invoices

 

Query: Cancelled invoices

SELECT
distinct AIA.INVOICE_ID, aia.invoice_num, AIA.INVOICE_AMOUNT,
 AIA.INVOICE_TYPE_LOOKUP_CODE,
DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,
              AIA.INVOICE_AMOUNT,
     AIA.PAYMENT_STATUS_FLAG,              
    AIA.INVOICE_TYPE_LOOKUP_CODE),
                       'NEVER APPROVED', 'Never Validated',
                       'NEEDS REAPPROVAL', 'Needs Revalidation',
                       'APPROVED',  'Validated',
                       'CANCELLED',      'Cancelled',
                       'AVAILABLE PREPAYMENT', 'Available Prepayment',
                       'AVAILABLE', 'Available') INVOICE_STATUS
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_TYPE_LOOKUP_CODE = 'AWT'
 AND   AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,
                AIA.INVOICE_AMOUNT,
                AIA.PAYMENT_STATUS_FLAG,
                AIA.INVOICE_TYPE_LOOKUP_CODE) = 'NEVER APPROVED'

Query to get invoice details in Oracle ebs R12

select 

aps.vendor_name,

apss.vendor_site_code,

aia.invoice_num,     

DECODE(aia.PAYMENT_STATUS_FLAG,'N','UN-PAID','P','Partial Paid','Y','PAID') PAYMENT_STATUS_FLAG ,

aia.invoice_date,

aia.invoice_amount,

pha.segment1 po_number,

apt.name Term_name,     

aca.check_number,

aipa.amount payment_amount,

apsa.amount_remaining,

aipa.invoice_payment_type,

hou.name operating_unit

from apps.ap_invoices_all       aia,

ap_suppliers aps,

ap_supplier_sites_all apss,

po_headers_all pha,

ap_invoice_payments_all aipa,

ap_checks_all aca,

ap_payment_schedules_all apsa,

ap_terms apt,

hr_operating_units hou

where 1=1

and aia.vendor_id=aps.vendor_id

and aia.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID

AND aps.vendor_id=apss.VENDOR_ID

and aia.po_header_id=pha.po_header_id(+)

and aipa.invoice_id=aia.invoice_id

and aca.check_id=aipa.check_id

and apsa.invoice_id=aia.invoice_id

and apt.term_id=aia.terms_id

 and hou.organization_id=aia.org_id

--and aia.ORG_ID=:P_ORG_ID;

invoice detail query in oracle ebs R12

 

select ap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account

from ap_invoices_all ap,

ap_invoice_distributions_all aid,

po_vendors pv,

po_vendor_sites_all pvs,

fnd_lookup_values alc,

fnd_lookup_values alc1,

fnd_lookup_values alc2,

ap_terms at,

gl_code_combinations gcc

where alc.lookup_type='INVOICE TYPE'

and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE

and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'

and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE

and alc2.lookup_type='PAYMENT METHOD'

and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE

and ap.invoice_id=aid.invoice_id

and ap.org_id=:P_ORG_ID

and ap.vendor_id = pv.vendor_id

and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID

and pv.vendor_id=pvs.vendor_id

and ap.vendor_site_id=pvs.vendor_site_id

AR Receipt Final Query

Query for  AR Receipts

PURPOSE: THE VIEW AR_CASH_RECEIPTS_V IS USED IN THE RECEIPTS GATEWAY AS THE BASETABLE FOR THE RECEIPTS FORM

/* Formatted on 08-12-2022 13:53:30 */ SELECT /* PURPOSE: THE VIEW AR_CASH_RECEIPTS_V IS USED IN THE RECEIPTS GATEWAY AS THE BASETABLE FOR THE RECEIPTS FORM */       HOU.NAME OPERATING_UNIT,       --CR .ROWID ROW_ID,          CR.CASH_RECEIPT_ID CASH_RECEIPT_ID,          CRH_CURRENT.CASH_RECEIPT_HISTORY_ID CASH_RECEIPT_HISTORY_ID,          CR.AMOUNT AMOUNT,          CRH_CURRENT.ACCTD_AMOUNT FUNCTIONAL_AMOUNT,          CRH_CURRENT.AMOUNT NET_AMOUNT,          CR.CURRENCY_CODE CURRENCY_CODE,          CR.RECEIPT_NUMBER RECEIPT_NUMBER,          CR.RECEIPT_DATE RECEIPT_DATE,          CR.ANTICIPATED_CLEARING_DATE ANTICIPATED_CLEARING_DATE,          CR.ACTUAL_VALUE_DATE ACTUAL_VALUE_DATE,          CR.TYPE TYPE,          CR.STATUS RECEIPT_STATUS,          ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('CHECK_STATUS', CR.STATUS)             RECEIPT_STATUS_DSP,          CR.COMMENTS COMMENTS,          CR.MISC_PAYMENT_SOURCE MISC_PAYMENT_SOURCE,          CR.EXCHANGE_RATE EXCHANGE_RATE,          CR.EXCHANGE_DATE EXCHANGE_RATE_DATE,          CR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE,          GL_DCT.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP,          CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID,          CR.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER,          CR.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,          CR.CUSTOMER_RECEIPT_REFERENCE CUSTOMER_RECEIPT_REFERENCE,          REC_METHOD.NAME PAYMENT_METHOD_DSP,          REC_METHOD.PAYMENT_TYPE_CODE PAYMENT_TYPE_CODE,          CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID,          RC.NAME RECEIPT_CLASS_DSP,          RC.BILL_OF_EXCHANGE_FLAG BILL_OF_EXCHANGE_FLAG,          ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('RECEIPT_CREATION_METHOD',                                                 RC.CREATION_METHOD_CODE)             CREATION_METHOD_DSP,          RC.CREATION_METHOD_CODE CREATION_METHOD_CODE,          CR.PAY_FROM_CUSTOMER CUSTOMER_ID,          SUBSTRB (PARTY.PARTY_NAME, 1, 50) CUSTOMER_NAME,          CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER,          PARTY.JGZZ_FISCAL_CODE TAXPAYER_ID,          SITE_USES.LOCATION LOCATION,          CR.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID,          CR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID,          CR.CUSTOMER_BANK_BRANCH_ID CUSTOMER_BANK_BRANCH_ID,          DECODE (             NVL (FND_PROFILE.VALUE ('AR_MASK_BANK_ACCOUNT_NUMBERS'), 'F'),             'N',             CUST_BANK.BANK_ACCOUNT_NUM,             'F',             RPAD (SUBSTR (CUST_BANK.BANK_ACCOUNT_NUM, 1, 4),                   LENGTH (CUST_BANK.BANK_ACCOUNT_NUM),                   '*'),             'L',             LPAD (SUBSTR (CUST_BANK.BANK_ACCOUNT_NUM, -4),                   LENGTH (CUST_BANK.BANK_ACCOUNT_NUM),                   '*')          )             CUSTOMER_BANK_ACCOUNT,          CUST_BANK.BANK_ACCOUNT_NUM CUSTOMER_BANK_ACCOUNT_NUM,          NVL (CUST_BANK_BRANCH.BANK_NAME, CUST_BANK_BRANCH2.BANK_NAME)             CUSTOMER_BANK_NAME,          NVL (CUST_BANK_BRANCH.BANK_BRANCH_NAME,               CUST_BANK_BRANCH2.BANK_BRANCH_NAME)             CUSTOMER_BANK_BRANCH,          CRH_FIRST_POSTED.BATCH_ID BATCH_ID,          DECODE (RC.CREATION_METHOD_CODE, 'BR', BAT_BR.NAME, BAT.NAME)             BATCH_NAME                       ,          DIST_SET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET,          CR.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID,          CR.DEPOSIT_DATE DEPOSIT_DATE,          CR.REFERENCE_TYPE REFERENCE_TYPE,          CR.VAT_TAX_ID VAT_TAX_ID,          VAT.TAX_CODE TAX_CODE,          CR.TAX_RATE TAX_RATE,          VAT.TAX_ACCOUNT_ID,          VAT.VALIDATE_FLAG ADHOC_FLAG,          L_REF_TYPE.MEANING REFERENCE_TYPE_DSP,          CR.REFERENCE_ID REFERENCE_ID,          CR.REMITTANCE_BANK_ACCOUNT_ID REMITTANCE_BANK_ACCOUNT_ID,          REMIT_BANK.BANK_ACCOUNT_NUM REMIT_BANK_ACCOUNT,          REMIT_BANK_BRANCH.BANK_NAME REMIT_BANK_NAME,          REMIT_BANK_BRANCH.BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID,          REMIT_BANK_BRANCH.BANK_BRANCH_NAME REMIT_BANK_BRANCH,          REMIT_BANK.CURRENCY_CODE REMIT_BANK_CURRENCY,          CRH_CURRENT.FACTOR_DISCOUNT_AMOUNT FACTOR_DISCOUNT_AMOUNT,          PS.DUE_DATE MATURITY_DATE,          PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID,          ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('RECEIPT_CREATION_STATUS',                                                 CRH_CURRENT.STATUS)             STATE_DSP,          CRH_CURRENT.STATUS STATE,          CRH_CURRENT.GL_POSTED_DATE POSTED_DATE,          REC_TRX.NAME ACTIVITY,          REC_TRX.TAX_CODE_SOURCE TAX_CODE_SOURCE,          CR.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID,          CRH_CURRENT.GL_POSTED_DATE GL_POSTED_DATE,          CRH_CURRENT.POSTING_CONTROL_ID POSTING_CONTROL_ID,          CRH_FIRST_POSTED.GL_DATE GL_DATE,          CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,          CR.REVERSAL_DATE REVERSAL_DATE,          DECODE (CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.MEANING)             REVERSAL_CATEGORY_DSP,          CR.REVERSAL_CATEGORY REVERSAL_CATEGORY,          DECODE (CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.DESCRIPTION)             CATEGORY_DESCRIPTION,          CR.REVERSAL_COMMENTS REVERSAL_COMMENTS,          DECODE (CR.REVERSAL_REASON_CODE, NULL, NULL, L_REV_REASON.MEANING)             REVERSAL_REASON,          CR.REVERSAL_REASON_CODE REVERSAL_REASON_CODE,          DECODE (CR.REVERSAL_REASON_CODE,                  NULL, NULL,                  L_REV_REASON.DESCRIPTION)             REVERSAL_REASON_DESCRIPTION,          REM_BAT.NAME REMIT_BATCH,          REM_BAT.BATCH_ID REMIT_BATCH_ID,          NVL (CR.OVERRIDE_REMIT_ACCOUNT_FLAG, 'Y') OVERRIDE_REMIT_BANK,          NVL (- (PS.AMOUNT_APPLIED), 0) APPLIED_AMOUNT,          CR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID,          CR.PROGRAM_ID PROGRAM_ID,          CR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE,          DECODE (             NVL (CR.CONFIRMED_FLAG, 'Y'),             'Y',             DECODE (                CR.REVERSAL_DATE,                NULL,                DECODE (                   CRH_CURRENT.STATUS,                   'REVERSED',                   'N',                   DECODE (                      CRH_CURRENT.FACTOR_FLAG,                      'Y',                      DECODE (CRH_CURRENT.STATUS, 'RISK_ELIMINATED', 'N', 'Y'),                      DECODE (CRH_CURRENT.STATUS, 'CLEARED', 'N', 'Y')                   )                ),                'N'             ),             'N'          )             AT_RISK,          REM_BAT.REMIT_METHOD_CODE REMITTANCE_METHOD,          CR.ISSUER_NAME,          CR.ISSUE_DATE,          CR.ISSUER_BANK_BRANCH_ID,          NOTES_BANK.BANK_NAME,          NOTES_BANK.BANK_BRANCH_NAME,          CRH_CURRENT.NOTE_STATUS,          CRH_NOTE_STATUS.MEANING,          CRH_NOTE_STATUS.DESCRIPTION,          RC.NOTES_RECEIVABLE,          CR.PAYMENT_SERVER_ORDER_NUM,          CR.APPROVAL_CODE,          CR.ADDRESS_VERIFICATION_CODE,          PS.CONS_INV_ID,          CR.POSTMARK_DATE POSTMARK_DATE   FROM   AP_BANK_BRANCHES NOTES_BANK,          AP_BANK_ACCOUNTS_ALL REMIT_BANK,          AP_BANK_BRANCHES REMIT_BANK_BRANCH,          AP_BANK_BRANCHES CUST_BANK_BRANCH,          AP_BANK_BRANCHES CUST_BANK_BRANCH2,          AP_BANK_ACCOUNTS_ALL CUST_BANK,          AR_VAT_TAX VAT,          HZ_CUST_ACCOUNTS CUST,          HZ_PARTIES PARTY,          AR_RECEIPT_METHODS REC_METHOD,          AR_RECEIPT_CLASSES RC,          HZ_CUST_SITE_USES_ALL SITE_USES,          AR_LOOKUPS CRH_NOTE_STATUS,          AR_LOOKUPS L_REV_CAT,          AR_LOOKUPS L_REV_REASON,          AR_LOOKUPS L_REF_TYPE,          GL_DAILY_CONVERSION_TYPES GL_DCT,          AR_CASH_RECEIPT_HISTORY_ALL CRH_REM,          AR_BATCHES REM_BAT,          AR_RECEIVABLES_TRX_ALL REC_TRX,          AR_DISTRIBUTION_SETS_ALL DIST_SET,          AR_PAYMENT_SCHEDULES_ALL PS,          AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT,         /* CURRENT_RECORD */          AR_BATCHES_ALL BAT,          AR_BATCHES_ALL BAT_BR, /*  J Rautiainen BR Implementation */          AR_CASH_RECEIPTS_ALL CR,          AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED, /* FIRST_POSTED_RECORD */          HR_OPERATING_UNITS HOU  WHERE       CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+)          AND CUST.PARTY_ID = PARTY.PARTY_ID(+)          AND CRH_NOTE_STATUS.LOOKUP_TYPE(+) = 'AR_NOTE_STATUS'          AND CRH_NOTE_STATUS.LOOKUP_CODE(+) = CRH_CURRENT.NOTE_STATUS          AND NOTES_BANK.BANK_BRANCH_ID(+) = CR.ISSUER_BANK_BRANCH_ID          AND REMIT_BANK.BANK_ACCOUNT_ID(+) = CR.REMITTANCE_BANK_ACCOUNT_ID          AND REMIT_BANK.BANK_BRANCH_ID = REMIT_BANK_BRANCH.BANK_BRANCH_ID(+)          AND CUST_BANK.BANK_ACCOUNT_ID(+) = CR.CUSTOMER_BANK_ACCOUNT_ID          AND CUST_BANK.BANK_BRANCH_ID = CUST_BANK_BRANCH.BANK_BRANCH_ID(+)          AND CR.CUSTOMER_BANK_BRANCH_ID = CUST_BANK_BRANCH2.BANK_BRANCH_ID(+)          AND VAT.VAT_TAX_ID(+) = CR.VAT_TAX_ID          AND CR.RECEIPT_METHOD_ID = REC_METHOD.RECEIPT_METHOD_ID          AND REC_METHOD.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID          AND CR.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID(+)          AND CR.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID(+)          AND CR.DISTRIBUTION_SET_ID = DIST_SET.DISTRIBUTION_SET_ID(+)          AND L_REV_CAT.LOOKUP_TYPE(+) = 'REVERSAL_CATEGORY_TYPE'          AND L_REV_CAT.LOOKUP_CODE(+) = CR.REVERSAL_CATEGORY          AND L_REV_REASON.LOOKUP_TYPE(+) = 'CKAJST_REASON'          AND L_REV_REASON.LOOKUP_CODE(+) = CR.REVERSAL_REASON_CODE          AND L_REF_TYPE.LOOKUP_CODE(+) = CR.REFERENCE_TYPE          AND L_REF_TYPE.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'          AND GL_DCT.CONVERSION_TYPE(+) = CR.EXCHANGE_RATE_TYPE          AND CRH_REM.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID          AND NOT EXISTS                (SELECT   CASH_RECEIPT_HISTORY_ID                   FROM   AR_CASH_RECEIPT_HISTORY_ALL CRH3                  WHERE   CRH3.STATUS = 'REMITTED'                          AND CRH3.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID                          AND CRH3.CASH_RECEIPT_HISTORY_ID <                                CRH_REM.CASH_RECEIPT_HISTORY_ID)          AND CRH_REM.STATUS(+) = 'REMITTED'          AND CRH_REM.BATCH_ID = REM_BAT.BATCH_ID(+)          AND REM_BAT.TYPE(+) = 'REMITTANCE'          AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID          AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID          AND CRH_CURRENT.CURRENT_RECORD_FLAG = NVL ('Y', CR.RECEIPT_NUMBER)          AND CRH_FIRST_POSTED.BATCH_ID = BAT.BATCH_ID(+)          AND BAT.TYPE(+) = 'MANUAL'          AND CRH_FIRST_POSTED.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID          AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG(+) = 'Y'          AND CRH_FIRST_POSTED.BATCH_ID = BAT_BR.BATCH_ID(+)          AND BAT_BR.TYPE(+) = 'BR'          AND HOU.ORGANIZATION_ID=CR.ORG_ID          and HOU.ORGANIZATION_ID= &org_id          --AND TRUNC(CR.RECEIPT_DATE) BETWEEN '01-Jan-2019' AND '02-jan-2019'          AND TRUNC(CRH_FIRST_POSTED.GL_DATE) BETWEEN '01-Jan-2019' AND '02-jan-2019'          --and cr.STATUS not in ('REV', 'NSF');