AR_Receipt_Final_Qry - Oracle Concepts For You

Home » Ar_cash_receipt_history_all Status » AR_Receipt_Final_Qry - Oracle Concepts For You

Maybe your like

Tuesday, 25 October 2011

AR_Receipt_Final_Qry

/* Formatted on 14-08-2011 13:53:30 (QP5 v5.115.810.9015) */ 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 /* 20-APR-2000 J Rautiainen BR Implementation */ , 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, /* 20-APR-2000 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 CR.CASH_RECEIPT_ID=581552 AND CR.SET_OF_BOOKS_ID = 5 AND TRUNC(CR.RECEIPT_DATE) BETWEEN '01-Apr-2011' AND '31-jul-2011' --and cr.STATUS not in ('REV', 'NSF') Posted by at 12:15

No comments:

Post a Comment

Newer Post Older Post Home Subscribe to: Post Comments (Atom)

About Me

My photo Elangovan Ragavan இளங்கோவன் ராகவன் எண்ணிய முடிதல் வேண்டும், நல்லவே யெண்ணல் வேண்டும்; திண்ணிய நெஞ்சம் வேண்டும், தெளிந்த நல் லறிவு வேண்டும்; பண்ணிய பாவமெல்லாம் பரிதி முன் பனியே போல, நண்ணிய நின்மு னிங்கு நசித்திட வேண்டும் அன்னாய்! View my complete profile

Oracle GRC

Oracle GRC Oracle Advanced Controls 11g Applications Certified Implementation Specialist

Followers

Live Viewers

Total Pageviews

Labels

  • .lst file (1)
  • About Page in OAF (3)
  • ADF (2)
  • Advanced Collections (1)
  • Advanced PL/SQL (18)
  • AIGS (10)
  • AIM Document (3)
  • Alert (7)
  • AME (17)
  • AOL (6)
  • AP Functional Accounting (17)
  • AP Payments (6)
  • AP_Module (84)
  • APEX (70)
  • API'S / INTREFACES (77)
  • Application Short Name (2)
  • approved supplier list (2)
  • APPS (11)
  • AR Functional (10)
  • AR_Query (72)
  • Back-to-Back Order Cycle (2)
  • Bank Query (34)
  • BOM (2)
  • Cash Management (8)
  • concurrent program (49)
  • Contorl file ctl (7)
  • Contorl file SQL *Loader (4)
  • Current Login Users (1)
  • custom.pll (7)
  • Customer (13)
  • Data Extraction (57)
  • Data Model (2)
  • Date Format (7)
  • DBA (114)
  • DELETE_QUERY (3)
  • DFF (7)
  • Documents (1)
  • DROP SHIP Order Flow (1)
  • Dummy Table (1)
  • EAM (16)
  • elango (6)
  • ERROR (28)
  • Executable (21)
  • FA (18)
  • FlexFields (8)
  • FND LOG (1)
  • FND_LOAD (13)
  • form personalization (52)
  • Forms (23)
  • FSG Report (1)
  • FUNCTIONAL (130)
  • gl (84)
  • GL Accounting (12)
  • GL Flow (3)
  • Global (1)
  • GRC (31)
  • GRC Function (1)
  • GRC Gertification (1)
  • GRC Screen (13)
  • GRC Tech (20)
  • HRMS (87)
  • Hyperion (7)
  • HZ TO ORG (1)
  • ICX - Oracle Self-Service Web Applications (2)
  • iExpenses (8)
  • IF (1)
  • India Localization (5)
  • Inventory_Details (69)
  • iprocurement (4)
  • ITEM_TEMPLATE (1)
  • java (8)
  • Legal entity (1)
  • Lexical & Bind (2)
  • LINKS IN ALL TABLES (6)
  • Lookup value (1)
  • Materialized View (2)
  • MO:Security Profile (2)
  • Multi-Org (24)
  • My Favorited Links (2)
  • MY FAVOURITES (2)
  • O2C CYCLE (28)
  • OBIEE (74)
  • ODI (1)
  • OM (23)
  • OM Setup (9)
  • Oracle (2)
  • ORACLE -Orbital Relational Analytical Computing Logical Equation (5)
  • Oracle Certification (1)
  • Oracle Discoverer (4)
  • Oracle Interview Questions (45)
  • Oracle Pricing (9)
  • Oracle Property Manager (5)
  • Oracle Setup Steps (1)
  • Oracle Standard Reports (25)
  • org (5)
  • OUM Methodology (1)
  • Outlook (1)
  • Output type in Oracle (1)
  • P2P Cycle (17)
  • Password in EBS (3)
  • Payroll (2)
  • Personalize OAF Pages (16)
  • PL/SQL (53)
  • PO (83)
  • Printer Assign process (1)
  • profile (10)
  • Projects (58)
  • Pure Silence for Life... :) (1)
  • QUERY (3)
  • R12 (3)
  • RDF report (5)
  • Receipt (1)
  • Reconciled Payments (2)
  • REPORT (15)
  • REPORT NAMES (1)
  • REPORT_QUERY (12)
  • request group (2)
  • Request Set (3)
  • Reset the po (2)
  • Responsibility (24)
  • RICE (1)
  • Row count (1)
  • Shell Script (6)
  • Shortcut Key (1)
  • SLA (13)
  • SOB (3)
  • SQL (98)
  • SRW.user_exit (2)
  • SUB_QUERY (1)
  • Supplier (29)
  • sysadmin (17)
  • Tables (71)
  • TAX (17)
  • TCA R12 (7)
  • technical (4)
  • Time Formets (3)
  • TOAD (3)
  • Trace (3)
  • UPDATE QUERY (20)
  • User Hooks (1)
  • USER_RES_MENU (14)
  • Valueset (18)
  • Videos (6)
  • VPD in Oracle (1)
  • Web ADI in Oracle (4)
  • Windows XP (1)
  • WIP (2)
  • Workflow (5)
  • XLA_SUB_LEDGER (20)
  • xml (91)

Blog Archive

  • ▼  2011 (239)
    • ▼  October (78)
      • JobCode_Cost_
      • Item_Updation_Qry
      • Create_Ap_Invoice
      • Cost Centre querie
      • THRESHOLD_FROM_DATA_UPDATION_QRY
      • PO_REQ_UPDATE_QRY
      • PO_DELIVERY_TERMS_UPD_QRY_v1.0
      • PO_DELIVERY_TERMS_UPD_QRY
      • PAYMENT_TERMS_UPDATION_R12
      • ITEM_INSPECTION_REQ_UPD_QRY_18SEP11
      • ITEM_INSPECTION_REQ_UPD_QRY
      • OPM_LOT_UPDATE
      • ITEM_INSPECTION_REQ_UPD_NEW_QRY
      • DOC_SEQUENCE_QRY
      • Tax_catagure
      • Receipt_Count_R12
      • R12_USERS
      • R12_User_Responsibility
      • R12_Tax
      • R12_Suppliers
      • R12_Responsibility_With_Funtion
      • R12 Projects
      • R12_profile_options_query
      • R12_PO_Query_v1.0
      • R12_PO_Query
      • R12 IMO Query
      • R12 Customer
      • R12_Chart_of_Accounts
      • R12_Buyers
      • R12_AR_Invoice_Data
      • R12_Ap_Invoice to Po Vendors
      • R12 Sub Inventories
      • R12 Imo_Subinv_Ohq_Qry
      • R12 GL Natural Acct
      • PO RECEIPT WITH TAX
      • po_receipt_with_accounting
      • R12_PO_RECEIPT_TXN_Qry
      • GL_SEGMENT_WITH_BY ORG
      • 11i_Bank_Details
      • Supplier_Sites
      • Supplier_Master
      • Supplier_Contact
      • req_loc_tax_details
      • req_header
      • req_distribution
      • Receipt_Txn_Qry
      • Receipt_Header_v1_0
      • Receipt_Header
      • receipt_duplicate_query
      • R12_Responsibilitys_query
      • AP_Prepayment_Pay_Inv_Lines
      • AP_Prepayment_Inv_Hdr
      • po_tax_with_item_name _quryin11i
      • po_requisition
      • PO_RECEIPTS_TAX_QRY
      • PO_RECEIPT_TXN_Final_QRY
      • PO_RECEIPT_TAX_FINAL_QRY
      • PO_RECEIPT_HEADER_Final_QRY
      • po_query
      • PO_Loc_Tax_Final
      • po_loc_tax_details_v1
      • po_loc_tax_details_v1.1
      • po_lines_with_PR_NUMBER
      • po_line
      • po_header_v1
      • po_distribution_v1
      • OPEN_PO_TAX_DTLS_QRY
      • Invoice_Header_query
      • AR_Receipt_Final_Qry
      • Ar_Receipt_Dist_Qry
      • AR_LINE
      • AR Invoice Tax
      • AR_HEADERS_QOERY
      • AP_INVOICE_PAYMENT_QUERY
      • INVOICE_ISSUE_query
      • Recipet information 11i & R12
      • Oracle Apps Tables changes from 11i to R12
      • Reset The PO

Tag » Ar_cash_receipt_history_all Status