Receivables Useful Query - Oracle Concepts For You

Home » Ar_cash_receipts_all And Hz_cust_accounts » Receivables Useful Query - Oracle Concepts For You

Maybe your like

Thursday, 4 September 2014

Receivables Useful Query

1)develop a query to list customer detailscust name,cust no,profile class,primary bill_to_address(country,address,city,state,postal code),primary ship_to_address(country,address,city,state,po)/* Formatted on 9/4/2014 2:18:11 PM (QP5 v5.115.810.9015) */SELECT hp1.party_name, hp1.party_id, hca.account_number, hcpc.name, hcsua.site_use_code, hl.country || ' ' || hl.address1 || ' ' || hl.city || ' ' || hl.state || ' ' || hl.postal_code "Address"FROM hz_parties hp1, hz_cust_accounts hca, hz_party_sites hps, hz_cust_profile_classes hcpc, hz_customer_profiles hcp, hz_locations hl, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsuaWHERE 1 = 1 AND hca.account_number = 3896 AND hcp.profile_class_id = hcpc.profile_class_id(+) AND hca.cust_account_id = hcp.cust_account_id AND hp1.party_id = hca.party_id --and hca.cust_account_id=hcasa.cust_account_id AND hps.party_site_id = hcasa.party_site_id AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id AND hp1.party_id = hps.party_id AND hl.location_id = hps.location_id AND hcsua.primary_flag = 'Y' AND hcp.site_use_id IS NULL AND hcasa.org_id = 204;2)to extract customer invoice details,cust name,total invoice relevant to customercust no,cust name,date of invoice generated,amount of invoice,amount of invoice last paid by the customer,credit limit,balance amount unpaid/* Formatted on 9/4/2014 2:18:30 PM (QP5 v5.115.810.9015) */SELECT hca.account_number, hp.party_name, rcta.trx_date, (SELECT SUM (rctla.extended_amount) FROM ra_customer_trx_lines_all rctla WHERE rcta.customer_trx_id = rctla.customer_trx_id) "INVOICE AMOUNT", hcpa.overall_credit_limit, apsa.amount_due_remaining, acra.amountFROM hz_parties hp, hz_cust_accounts hca, hz_customer_profiles hcp, hz_cust_profile_amts hcpa, ra_customer_trx_all rcta--, ra_customer_trx_lines_all rctla , ar_cash_receipts_all acra, ar_receivable_applications_all araa, ar_payment_schedules_all apsaWHERE 1 = 1 AND hp.party_id = hca.party_id AND hca.cust_account_id = hcp.cust_account_id AND hcp.site_use_id IS NULL AND hcpa.cust_account_profile_id = hcp.cust_account_profile_id AND hcpa.currency_code = 'USD' AND rcta.sold_to_customer_id = hca.cust_account_id AND rcta.org_id = 204 --and rcta.customer_trx_id = rctla.customer_trx_id AND rcta.customer_trx_id = apsa.customer_trx_id AND araa.applied_customer_trx_id = apsa.customer_trx_id AND acra.cash_receipt_id = araa.cash_receipt_id AND rcta.trx_number = '11792' AND acra.cash_receipt_id = (SELECT MAX (cash_receipt_id) FROM ar_receivable_applications_all araa_2 WHERE araa_2.applied_customer_trx_id = apsa.customer_trx_id);3)to list all the invoices whicha r pending to b paid for more than 60 days from sysdatecust no,cust name,invoice no,date of invoice,amount,period of invoice pending for 30-60 days,period of invoice pending for 60-120 days,period of invoice pending for more than 120 days/* Formatted on 9/4/2014 2:20:12 PM (QP5 v5.115.810.9015) */SELECT hca.account_number, hp.party_name, rcta.trx_number, rcta.trx_date, SUM (rctla.extended_amount), CASE WHEN TRUNC (SYSDATE - rcta.trx_date) BETWEEN 30 AND 60 THEN rcta.trx_number END "Pending for 30 - 60 days", CASE WHEN TRUNC (SYSDATE - rcta.trx_date) BETWEEN 60 AND 120 THEN rcta.trx_number END "Pending for 60 - 120 days", CASE WHEN TRUNC (SYSDATE - rcta.trx_date) > 120 THEN rcta.trx_number END "Pending for more than 120 days"FROM hz_parties hp, hz_cust_accounts hca, ra_customer_trx_all rcta, ra_customer_trx_lines_all rctlaWHERE hp.party_id = hca.party_id AND rcta.org_id = 204 AND hca.cust_account_id = rcta.sold_to_customer_id AND rcta.customer_trx_id = rctla.customer_trx_id AND hca.account_number = 3896GROUP BY hca.account_number, hp.party_name, rcta.trx_number, rcta.trx_date;-4)to list all the invoices paid during past 6 monthscust no,cust name,invoice no,receipt no,date of invoive,date of payment,amount/* Formatted on 9/4/2014 2:20:24 PM (QP5 v5.115.810.9015) */SELECT hca.account_number, hp.party_name, rcta.trx_number, acra.receipt_number, rcta.trx_date, acra.creation_date, acra.amountFROM hz_parties hp, hz_cust_accounts hca, ra_customer_trx_all rcta--, ra_customer_trx_lines_all rctla , ar_cash_receipts_all acra, ar_payment_schedules_all apsa, ar_receivable_applications_all araa, ar_lookups alWHERE 1 = 1 AND hp.party_id = hca.party_id AND rcta.sold_to_customer_id = hca.cust_account_id AND rcta.org_id = 204 --and rcta.customer_trx_id = rctla.customer_trx_id AND rcta.customer_trx_id = apsa.customer_trx_id AND araa.applied_customer_trx_id = apsa.customer_trx_id AND acra.cash_receipt_id = araa.cash_receipt_id AND acra.status = al.lookup_code AND al.lookup_type = 'PAYMENT_TYPE' AND rcta.trx_number = '11792' AND TRUNC (MONTHS_BETWEEN (SYSDATE, araa.creation_date)) <= 6; Posted by at 14:21 Labels: AR_Query

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

  • ▼  2014 (713)
    • ▼  September (36)
      • How to programmatically apply hold on AP Invoices ...
      • Viewing General Ledger (GL) Daily Currency Convers...
      • Forms Personalization to add a Web Link in the Too...
      • Oracle Applications Security Rule
      • supplier liability account detail in oracle apps
      • FND User form user asianed responsibility Detail q...
      • Receivables Accounting Periods
      • How to hide the HRMS Assignment form in oracle apps
      • HZ party and account active inactive API in oracle...
      • Inactivate the Customer (OR) Party Account API (HZ...
      • To identify the form rule name
      • lead lag functions oracle sql
      • Oracle EBS R12.1.3 Submit Request Delivery Options...
      • Oracle EBS R12.1.3 Submit Request Delivery Options...
      • Oracle EBS R12.1.3 Submit Request Delivery Options...
      • Oracle EBS R12.1.3 Submit Request Delivery Options...
      • Oracle EBS R12.1.3 Submit Request Delivery Options...
      • Oracle EBS R12.1.3 Submit Request Delivery Options...
      • How to Generate Employee Number through Programmin...
      • /* Formatted on 9/11/2014 3:52:27 PM (QP5 v5.115....
      • Lov Process
      • Verifying Supplier Bank Account Details at all levels
      • From and Function with Responcibility attached the...
      • LOV Query
      • Custom Top/Application Creation R12
      • R12 Supplier/Site/Banking Drilldown
      • Receivables Useful Query
      • OM Useful Query
      • Payables Useful Query
      • Purchasing Useful Query
      • Inventory Useful Query
      • GL Useful Querys
      • AR to HZ Table Link
      • Chart of Accounts Implementation or Setup in Oracl...
      • AP Trail Balance Query
      • FND_PROFILE and FND_GLOBAL values

Tag » Ar_cash_receipts_all And Hz_cust_accounts