🔹 Join the OracleApps88 Telegram Group - Stay up to date with the latest on Oracle EBS R12 and Oracle Cloud/Fusion Applications. 📌 Telegram Group : https://t.me/OracleApps88

💡 Facing issues copying code/scripts or viewing posts? We're here to help!
📬 Contact on Telegram : https://t.me/apps88
📱 Telegram/WhatsApp: +91 905 957 4321
📧 Email Support: OracleApp88@Yahoo.com
Showing posts with label AR. Show all posts
Showing posts with label AR. Show all posts

Monday, July 14, 2025

AR FAQS - Accounts Receivable Interview Interview Questions and Answers

 Basic Concepts and Definitions

Q1: What is Accounts Receivable (AR)?

A: Accounts Receivable refers to the amount of money owed to a company by its customers for goods or services provided on credit. It is recorded as an asset on the balance sheet.

 

Q2: How do you record AR in financial statements?

A: AR is recorded as a current asset on the balance sheet. When the payment is received, it reduces the AR balance and increases the cash balance.

 

Q3: What is the difference between AR and AP?

A: Accounts Receivable (AR) is the money owed to a company by its customers, while Accounts Payable (AP) is the money a company owes to its suppliers or vendors.

 

Q4: What is the aging schedule in AR?

A: An aging schedule categorizes AR based on the length of time an invoice has been outstanding. Common categories are 0-30 days, 31-60 days, 61-90 days, and over 90 days.

 

Q5: What is the difference between billing and invoicing?

A: Billing is the process of requesting payment, often on a recurring basis, while invoicing is the act of creating and sending an itemized bill for goods or services rendered.

 

Process and Workflow

Q6: Describe the Accounts Receivable process.

A: The AR process involves invoicing customers, recording payments, reconciling accounts, managing customer disputes, and following up on overdue payments.

 

Q7: How do you handle late payments or overdue invoices?

A: I follow up with the customer via email or phone, send reminders, and escalate the issue if necessary. I also evaluate the need for adjusting payment terms or offering discounts to encourage prompt payment.

 

Q8: What are the steps involved in reconciling AR?

A: The steps include matching payments received to outstanding invoices, identifying discrepancies, investigating variances, and adjusting records as necessary.

 

Q9: How do you manage cash application in AR?

A: Cash application involves matching payments received with the correct customer accounts and invoices. I ensure that payments are applied accurately and promptly, and any discrepancies are resolved.

 

Q10: What is the role of credit management in AR?

A: Credit management involves assessing a customer's creditworthiness, setting credit limits, and monitoring credit terms to minimize the risk of non-payment.

 

Tools and Software

Q11: What AR software or tools have you used?

A: I have experience with ERP systems like SAP, Oracle, and QuickBooks. I am also familiar with AR automation tools and reporting software such as Microsoft Excel and Tableau.

 

Q12: How do you use Excel in AR management?

A: I use Excel for data analysis, creating aging reports, tracking payments, and performing reconciliations. Functions like VLOOKUP, pivot tables, and conditional formatting are particularly useful.

 

Q13: Have you worked with any automated AR systems?

A: Yes, I have experience with automated AR systems that streamline invoicing, collections, and reporting. Automation helps reduce errors and improves efficiency in managing receivables.

 

Q14: How do you ensure data accuracy in AR software?

A: Regular reconciliations, data validation checks, and proper documentation of transactions help ensure data accuracy. I also use automated alerts for any inconsistencies.

 

Q15: How do you handle system errors or discrepancies in AR?

A: I investigate the source of the error, consult with the IT department if necessary, and correct the discrepancy. If it’s a recurring issue, I work on improving the process or system configuration.

 

Reporting and Analysis

Q16: What reports are crucial for AR management?

A: Aging reports, cash flow forecasts, collection reports, and customer account statements are essential for monitoring and managing AR.

 

Q17: How do you analyze AR aging reports?

A: I review the aging report to identify overdue accounts, assess the risk of non-payment, and prioritize collection efforts. I also look for trends or patterns that may indicate underlying issues.

 

Q18: What KPIs do you track in AR?

A: Key performance indicators (KPIs) include Days Sales Outstanding (DSO), aging percentage, collection effectiveness index, and bad debt ratio.

 

Q19: How do you improve DSO (Days Sales Outstanding)?

A: Improving DSO involves faster invoicing, proactive collection efforts, offering early payment discounts, and regularly reviewing credit terms and customer payment behavior.

 

Q20: Can you explain how to calculate bad debt expense?

A: Bad debt expense is calculated based on historical data, either by using the percentage of sales method or the accounts receivable aging method, where a percentage of outstanding receivables is estimated to be uncollectible.

 

Problem-Solving and Scenarios

Q21: How do you handle a situation where a customer disputes an invoice?

A: I first verify the details of the invoice and gather supporting documentation. Then, I contact the customer to understand their concerns and work towards resolving the issue, either by adjusting the invoice or providing clarification.

 

Q22: Can you describe a time when you had to deal with a difficult customer regarding payment?

A: I had a customer who consistently delayed payments. I worked closely with them to understand their financial situation and negotiated a payment plan that worked for both parties, which eventually resolved the issue.

 

Q23: How do you handle a large volume of AR transactions?

A: I prioritize tasks based on due dates and amounts, use automation tools to streamline processes, and regularly review reports to ensure that everything is on track.

 

Q24: What steps do you take to reduce bad debts?

A: I conduct thorough credit checks before extending credit, monitor aging reports, follow up on overdue accounts promptly, and work with customers to set up payment plans if necessary.

 

Q25: How do you handle discrepancies between the AR ledger and the bank statement?

A: I perform a bank reconciliation by comparing the AR ledger to the bank statement, identifying discrepancies, and investigating any unmatched transactions. Corrections are made accordingly.

 

Compliance and Regulations

Q26: How do you ensure compliance with accounting standards in AR?

A: I stay updated on relevant accounting standards (e.g., IFRS, GAAP) and ensure that AR processes, including revenue recognition and bad debt provisioning, comply with these standards.

 

Q27: What is the role of internal controls in AR?

A: Internal controls in AR help prevent errors and fraud. These controls include segregation of duties, approval processes for credit terms, and regular audits of AR transactions.

 

Q28: How do you handle AR in a multi-currency environment?

A: I use the company’s ERP system to manage multi-currency transactions, ensuring that foreign exchange rates are accurately applied and that currency gains or losses are recorded appropriately.

 

Q29: What are the tax implications of AR?

A: Depending on the jurisdiction, AR can have tax implications such as sales tax or VAT. It’s important to ensure that the correct tax is applied to invoices and that tax returns reflect AR balances accurately.

 

Q30: How do you manage AR during an audit?

A: I ensure that all documentation, such as invoices, payment records, and reconciliations, is organized and readily available. I also prepare detailed reports and explain any discrepancies or adjustments during the audit process.

 

Teamwork and Collaboration

Q31: How do you collaborate with other departments to manage AR?

A: I work closely with the sales, credit, and finance teams to ensure that credit terms are appropriate, invoicing is accurate, and any disputes or payment issues are resolved efficiently.

 

Q32: How do you handle conflicts between the sales and AR teams?

A: I mediate discussions to ensure both sides understand each other’s priorities. For example, I work with sales to balance the need for customer satisfaction with the importance of timely payments.

 

Q33: Can you describe a time when you improved communication within the AR team?

A: In a previous role, I introduced regular team meetings and status reports to improve transparency and coordination. This helped reduce errors and ensured that everyone was on the same page.

 

Q34: How do you train new team members on AR processes?

A: I provide comprehensive training that covers the AR workflow, software, and reporting tools. I also pair new hires with experienced team members for hands-on learning.

 

Q35: How do you handle a high workload within the AR team?

A: I prioritize tasks, delegate responsibilities, and, if necessary, suggest hiring temporary help. I also look for ways to streamline processes to reduce manual work.

 

Strategic and Advanced Questions

Q36: How do you assess the creditworthiness of a customer?

A: I review the customer’s financial statements, credit scores, payment history, and industry reputation. I also assess their ability to meet payment terms based on their current financial situation.

 

Q37: What strategies do you use to optimize AR collections?

A: I implement proactive collection efforts, offer payment plans, negotiate early payment discounts, and maintain strong relationships with customers to encourage timely payments.

 

Q38: How do you handle AR during periods of economic downturn?

A: During economic downturns, I closely monitor AR aging, adjust credit terms, and work with customers to set up flexible payment plans. I also increase communication with customers to manage expectations.

 

Q39: How do you handle high-risk customers in AR?

A: I monitor high-risk customers more closely, set stricter credit limits, and require more frequent payment schedules or advance payments. I also review their accounts regularly for any signs of financial distress.

 

Q40: What is your approach to continuous improvement in AR?

A: I regularly review AR processes for inefficiencies, seek feedback from team members, and implement automation where possible. I also stay informed about industry best practices and emerging technologies.

 

9. Advanced Process and Technical Questions

Q41: How do you calculate Days Sales Outstanding (DSO), and what does it indicate?

A: DSO is calculated by dividing the total accounts receivable by the total credit sales and then multiplying by the number of days in the period. It indicates the average number of days it takes a company to collect payment after a sale. A lower DSO is preferred as it shows efficient collection.

 

Q42: What steps do you take to resolve unapplied cash in AR?

A: I review the payment details, match them with open invoices, communicate with the customer if needed, and apply the cash to the correct invoice. If necessary, I research further to find the correct allocation or escalate the issue.

 

Q43: How do you handle a situation where a customer consistently delays payments despite multiple follow-ups?

A: I assess the customer’s payment history, communicate directly to understand any challenges, renegotiate payment terms if needed, and possibly escalate the issue to the credit department or consider withholding further credit until the account is settled.

 

Q44: What is the impact of AR on the cash flow statement?

A: AR impacts the cash flow from operating activities. An increase in AR indicates that revenue is recorded but not yet collected in cash, which reduces cash flow. Conversely, a decrease in AR boosts cash flow as payments are collected.

 

Q45: How do you handle overpayments from customers?

A: I first verify the overpayment, then decide whether to apply the excess amount to future invoices, issue a refund, or hold the credit on the customer’s account, depending on the customer’s preference and company policy.

 

Q46: What is a chargeback in AR, and how do you handle it?

A: A chargeback is a return of funds to a customer, often due to disputes or errors. To handle it, I verify the validity of the chargeback, adjust the AR records, and work with the customer and internal departments to prevent future issues.

 

Q47: How do you manage AR for a company with multiple subsidiaries or locations?

A: I ensure that each subsidiary/location has a clear AR process and that records are consolidated accurately. I use an ERP system that supports multi-entity accounting and regularly reconcile intercompany transactions.

 

Q48: Can you explain the concept of factoring in AR?

A: Factoring is the sale of a company's receivables to a third party (factor) at a discount. This provides immediate cash flow, but at the cost of a fee. It’s commonly used when a company needs to improve liquidity quickly.

 

Q49: How do you handle intercompany transactions in AR?

A: I ensure that transactions between related entities are accurately recorded and reconciled. This involves eliminating intercompany balances during consolidation and ensuring that all entries are properly documented.

 

Q50: What is the significance of the Allowance for Doubtful Accounts, and how do you calculate it?

A: The Allowance for Doubtful Accounts is a contra-asset account that estimates the amount of receivables that may not be collected. It’s calculated based on historical data, industry standards, and specific customer circumstances using methods like percentage of sales or aging of receivables.

 

10. Customer and Dispute Management

Q51: How do you manage disputes regarding early payment discounts?

A: I verify the terms of the discount, check if the payment was made within the eligible period, and communicate with the customer to resolve any discrepancies. If the customer is not eligible, I provide a clear explanation and adjust the invoice accordingly.

 

Q52: What is your approach to resolving disputes that arise due to pricing discrepancies?

A: I review the original sales agreement and invoice details, communicate with the sales department, and discuss the issue with the customer. If a mistake was made, I correct the invoice; if not, I provide the customer with documentation to justify the pricing.

 

Q53: How do you handle situations where a customer claims they never received an invoice?

A: I first confirm that the invoice was sent and check the contact details. Then, I resend the invoice and follow up with the customer to ensure they receive it. I may also discuss alternative ways to send invoices, like electronic billing, to prevent future issues.

 

Q54: How do you prioritize collections from different customers?

A: I prioritize based on factors such as the aging of the receivables, the customer’s payment history, the amount owed, and the impact on cash flow. High-risk and high-value accounts usually get the most attention.

 

Q55: How do you handle a situation where a customer’s payment is consistently short?

A: I discuss the issue with the customer to understand why the payments are short. If it’s due to disputes or cash flow issues, I work to resolve the underlying problem. If it’s a recurring issue, I may adjust payment terms or escalate the matter.

 

Q56: What is your approach to managing customer disputes that escalate into legal issues?

A: I gather all relevant documentation, involve the legal department, and try to resolve the issue through negotiation. If litigation is necessary, I provide the legal team with all necessary support and continue to monitor the situation closely.

 

Q57: How do you maintain good relationships with customers while ensuring timely payments?

A: Clear communication, understanding customer needs, and being flexible within reason help maintain good relationships. I ensure that customers are aware of payment terms and deadlines, and I offer solutions like payment plans if needed.

 

Q58: How do you handle a customer who disputes a late fee?

A: I review the invoice and payment history to confirm whether the late fee is valid. If it is, I explain the reason for the fee to the customer. In some cases, I may offer to waive the fee as a goodwill gesture if the customer has a good payment history.

 

Q59: How do you deal with a customer who refuses to pay because of dissatisfaction with the product or service?

A: I coordinate with the sales or customer service department to address the customer’s concerns. If a resolution is reached, I negotiate a payment plan or settlement. If no resolution is possible, I may need to escalate the issue.

 

Q60: How do you handle partial payments from customers?

A: I apply the partial payment to the invoice and then follow up with the customer to arrange payment of the remaining balance. If necessary, I discuss the reason for the partial payment and work out a plan to collect the full amount.

 

11. Reporting and Analysis

Q61: How do you prepare an AR aging report, and what information does it provide?

A: An AR aging report categorizes receivables based on the length of time they have been outstanding. I generate the report using accounting software, which helps identify overdue accounts and prioritize collections.

 

Q62: How do you use AR reports to identify trends and improve collections?

A: I analyze aging reports, collection reports, and customer payment histories to identify patterns in late payments, high-risk customers, or inefficiencies in the collection process. This information helps me adjust strategies to improve collections.

 

Q63: What metrics do you monitor in AR to ensure efficiency?

A: Key metrics include Days Sales Outstanding (DSO), aging percentage, collection effectiveness index (CEI), bad debt ratio, and average days delinquent (ADD). These metrics provide insight into the efficiency of the AR process.

 

Q64: How do you calculate the Collection Effectiveness Index (CEI)?

A: CEI is calculated by dividing the total amount collected during a period by the total receivables at the beginning of the period plus new sales, and then multiplying by 100. It measures how effective the company is at collecting receivables.

 

Q65: How do you analyze customer payment behavior?

A: I review payment histories, aging reports, and customer account trends to identify any changes in payment behavior. I also consider external factors like industry conditions or economic factors that may influence behavior.

 

Q66: How do you create cash flow forecasts based on AR?

A: I use historical AR data, sales projections, and aging reports to estimate future cash inflows. I factor in expected payment delays and potential bad debts to create a realistic forecast.

 

Q67: How do you prepare an AR reconciliation report?

A: I compare the AR ledger with supporting documentation, such as invoices and bank statements, to ensure that all transactions are recorded correctly. Any discrepancies are investigated and corrected.

 

Q68: What strategies do you use to reduce bad debt expenses?

A: I implement thorough credit checks, monitor aging reports closely, follow up on overdue accounts promptly, and work with customers to set up payment plans if needed. I also review and adjust credit policies regularly.

 

Q69: How do you ensure accurate AR reporting at month-end close?

A: I ensure that all transactions are recorded, reconciliations are completed, and any discrepancies are resolved before closing the books. I also prepare detailed AR reports and review them for accuracy.

 

Q70: How do you report AR performance to management?

A: I provide management with regular reports that include key metrics (e.g., DSO, aging analysis), trends, and any issues affecting collections. I also make recommendations for improving AR performance.

 

12. Compliance and Regulations

Q71: How do you ensure compliance with Sarbanes-Oxley (SOX) requirements in AR?

A: I ensure that AR processes include proper internal controls, such as segregation of duties, authorization of transactions, and documentation of all actions. Regular audits and reviews also help maintain compliance.

 

Q72: How do you stay updated on changes in accounting standards that affect AR?

A: I regularly review updates from accounting bodies (e.g., FASB, IASB), attend webinars and training, and participate in industry forums to stay informed of any changes that affect AR.

 

Q73: What is the importance of internal controls in AR, and how do you implement them?

A: Internal controls help prevent errors and fraud in AR. I implement controls such as segregation of duties, authorization requirements, regular reconciliations, and automated alerts for unusual transactions.

 

Q74: How do you handle AR under different revenue recognition standards (e.g., ASC 606 or IFRS 15)?

A: I ensure that revenue is recognized when control of goods or services is transferred to the customer, as per the contract. This may require adjusting AR processes to align with performance obligations and timing of revenue recognition.

 

Q75: How do you manage AR for government contracts or grants?

A: I ensure compliance with government regulations, track contract-specific billing requirements, and maintain detailed documentation to support audits and compliance checks.

 

Q76: How do you handle AR in different tax jurisdictions?

A: I ensure that AR processes comply with local tax regulations, such as VAT or sales tax, by properly calculating and reporting taxes on invoices. I also work with tax professionals to ensure compliance in complex scenarios.

 

Q77: How do you handle AR for export sales?

A: I ensure that AR records reflect the correct exchange rates, taxes, and duties for export sales. I also monitor foreign receivables closely to manage any currency risk.

 

Q78: How do you manage AR in compliance with anti-money laundering (AML) regulations?

A: I implement controls to ensure that customer payments are legitimate, such as verifying customer identities, monitoring payment patterns, and reporting suspicious activities as required by AML regulations.

 

Q79: What are the implications of the General Data Protection Regulation (GDPR) on AR processes?

A: GDPR requires that customer data is handled securely and with consent. In AR, this means ensuring that customer payment information is stored securely, access is restricted, and customers are informed of their data rights.

 

Q80: How do you prepare for an AR audit?

A: I ensure that all AR records, such as invoices, payment receipts, and reconciliations, are organized and readily available. I also prepare reports and explanations for any significant discrepancies or adjustments.

 

13. Teamwork and Collaboration

Q81: How do you collaborate with the sales team to ensure accurate invoicing?

A: I maintain clear communication with the sales team to ensure that all necessary information, such as pricing, terms, and customer details, is accurate before invoicing. Regular meetings and cross-functional reviews help prevent errors.

 

Q82: How do you work with the finance team to ensure accurate AR reporting?

A: I collaborate with the finance team to ensure that AR data is accurately recorded in the general ledger and that any discrepancies are resolved before financial reports are generated.

 

Q83: How do you handle conflicts between AR and other departments?

A: I approach conflicts with a problem-solving mindset, seeking to understand the perspectives of all parties involved. I facilitate discussions to find a solution that aligns with both operational goals and financial objectives.

 

Q84: How do you manage communication between the AR team and customers?

A: I ensure that communication with customers is clear, professional, and timely. Regular follow-ups, consistent messaging, and addressing customer concerns promptly help maintain positive relationships.

 

Q85: How do you ensure that the AR team meets its targets and deadlines?

A: I set clear goals, track progress regularly, and provide the team with the necessary resources and support. I also encourage collaboration and continuous improvement to meet targets efficiently.

 

Q86: How do you handle training new AR team members?

A: I provide comprehensive training on AR processes, systems, and company policies. I also assign a mentor or buddy to help new hires adjust and encourage ongoing learning and development.

 

Q87: How do you handle a high workload within the AR team?

A: I prioritize tasks based on deadlines and importance, delegate responsibilities effectively, and look for ways to streamline processes. If necessary, I consider bringing in temporary help or redistributing tasks across the team.

 

Q88: How do you motivate the AR team to improve performance?

A: I set clear performance targets, provide regular feedback, and recognize achievements. I also create opportunities for team members to learn new skills and take on challenging projects.

 

Q89: How do you handle a situation where the AR team is understaffed?

A: I reassess the team’s workload, prioritize critical tasks, and seek temporary or additional support if necessary. I also communicate the situation to management and request resources to address the staffing shortfall.

 

Q90: How do you foster a culture of continuous improvement in the AR team?

A: I encourage the team to regularly review processes, suggest improvements, and adopt new technologies or methods. I also provide opportunities for professional development and learning.

 

14. Strategic and Advanced Questions

Q91: How do you assess and mitigate the risk of bad debts in AR?

A: I assess risk by reviewing customer credit scores, payment history, and industry conditions. To mitigate risk, I implement credit controls, monitor accounts closely, and negotiate payment plans for high-risk customers.

 

Q92: What strategies do you use to improve the efficiency of the AR process?

A: I look for opportunities to automate routine tasks, streamline workflows, and reduce manual errors. I also regularly review and update AR policies to reflect best practices and changing business needs.

 

Q93: How do you handle AR during a period of rapid company growth?

A: I scale AR processes to handle increased volume, invest in automation tools, and ensure that the team is adequately staffed and trained. I also monitor cash flow closely to manage any challenges that arise from rapid growth.

 

Q94: How do you manage AR in a volatile economic environment?

A: I closely monitor customer payment behavior, adjust credit terms as needed, and maintain strong communication with customers. I also work with management to implement strategies that protect cash flow, such as tightening credit policies or offering discounts for early payment.

 

Q95: How do you balance the need for strong collections with maintaining positive customer relationships?

A: I focus on clear communication and flexibility, working with customers to resolve issues while ensuring that payments are collected. Building trust and offering solutions that meet both parties’ needs is key to maintaining positive relationships.

 

Q96: How do you handle AR for a company that is expanding internationally?

A: I ensure that AR processes comply with local regulations, manage currency risk through proper hedging or currency adjustments, and establish clear communication with international customers.

 

Q97: How do you prepare AR for a company going through a merger or acquisition?

A: I ensure that AR records are accurate and up-to-date, prepare detailed reports on outstanding receivables, and work with the finance team to integrate AR processes with those of the acquiring or merging company.

 

Q98: How do you handle a situation where the company’s AR policy needs to be revised?

A: I assess the current policy, gather input from relevant stakeholders, and propose changes that address any issues or inefficiencies. I also ensure that the revised policy is communicated clearly to the AR team and implemented effectively.

 

Q99: How do you evaluate the effectiveness of the AR team?

A: I track performance metrics, such as DSO, collection rates, and accuracy of reporting, and provide regular feedback. I also conduct performance reviews and identify areas for improvement or professional development.

 

Q100: How do you align AR goals with the overall financial strategy of the company?

A: I ensure that AR goals support the company’s financial objectives, such as optimizing cash flow, reducing bad debt, and improving customer satisfaction. I also work closely with the finance team to align AR strategies with broader financial planning and decision-making.

Tuesday, July 1, 2025

AR - Oracle Fusion AR Customer Tables and Scripts

Base Tables Descriptions

HZ_PARTIES Foundational part of Oracle’s Trading Community
Architecture (TCA). Trading Partner can be on 3 types
•  Person (e.g., Jane Doe)
•  Organization (e.g., Oracle Corporation)
•  Group (e.g., World Wide Web Consortium)
HZ_PARTY_SITES Location details like the Address related to a party
HZ_CUST_ACCOUNTS contains details about customer accounts or business relationships that the deploying company has formed with parties classified as either Organizations or Individuals
HZ_LOCATIONS Postal Address, physical location of party site.
HZ_ORIG_SYSTEMS_B Source systems are systems that load data into the
HZ_ORIG_SYSTEMS_TL TCA Registry
HZ_ORIG_SYS_REFERENCES source system references details stored.
HZ_RELATIONSHIPS Relationship between the party and the contact person details.
HZ_CUST_ACCT_SITES_ALL Store Customer Site against the Oracle Business Unit.
HZ_CUST_SITE_USES_ALL Purpose of the Site either Bill-To , Ship-To , Dunning Letter.
HZ_CUST_ACCOUNT_ROLES A role or function that a party performs for a customer account.
HZ_CONTACT_POINTS Communication to the contact person either by Phone, Website URL, or Email.
HZ_GEOGRAPHIES Stores Geography detail and its Address format.


1. Customer Account and Addresses - Query to fetch Customer details at Account, Address, Sites, and Site Uses.

SELECT hca.cust_account_id,

       hca.party_id                    cust_acct_party_id,

       hca.account_number,

       hca.orig_system_reference       acct_orig_sys_ref,

       hp.party_number,

       hp.party_name,

       hp.party_type,

       hp.orig_system_reference        party_orig_sys_ref,

       hcas.cust_acct_site_id,

       hcas.party_site_id,

       hcas.orig_system_reference      acct_site_orig_sys_ref,

       hcas.status                     acct_site_status,

       hcas.set_id                     acct_site_set_id,

       hps.location_id,

       hps.party_id,

       hps.party_site_number,

       hl.orig_system_reference        loc_orig_sys_ref,

       hl.country,

       hl.address1,

       hl.address2,

       hl.address3,

       hl.address4,

       hl.city,

       hl.state,

       hl.postal_code,

       hcsu.site_use_id,

       hcsu.site_use_code,

       hcsu.status                     site_use_status,

       hcsu.location,

       hcsu.set_id                     site_user_set_id,

       acct_osr.orig_system            acct_osr_system,

       acct_osr.orig_system_reference  acct_osr_value,

       party_osr.orig_system           party_osr_system,

       party_osr.orig_system_reference party_osr_value,

       site_osr.orig_system            site_osr_system,

       site_osr.orig_system_reference  site_osr_value,

       loc_osr.orig_system             loc_osr_system,

       loc_osr.orig_system_reference   loc_osr_value

  FROM hz_cust_accounts       hca,

       hz_parties             hp,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       hz_locations           hl,

       hz_cust_site_uses_all  hcsu,

       hz_orig_sys_references acct_osr,

       hz_orig_sys_references party_osr,

       hz_orig_sys_references site_osr,

       hz_orig_sys_references loc_osr

 WHERE     hca.party_id = hp.party_id

       AND hca.cust_account_id = hcas.cust_account_id

       AND hcas.party_site_id = hps.party_site_id

       AND hps.location_id = hl.location_id

       AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

       AND acct_osr.owner_table_name = 'HZ_CUST_ACCOUNTS'

       AND acct_osr.owner_table_id = hca.cust_account_id

       AND party_osr.owner_table_name = 'HZ_PARTIES'

       AND party_osr.owner_table_id = hp.party_id

       AND site_osr.owner_table_name = 'HZ_CUST_ACCT_SITES_ALL'

       AND site_osr.owner_table_id = hcas.cust_acct_site_id

       AND loc_osr.owner_table_name = 'HZ_LOCATIONS'

       AND loc_osr.owner_table_id = hps.location_id

       AND hps.party_site_number = :p_site_number -- Query for Particular Customer Site

       AND hca.account_number = :p_acct_number -- Query for particular Customer Account Number


2.Customer Account and Contact Details - Query to fetch customer details in the account and the contact person details.

SELECT hca.CUST_ACCOUNT_ID,

       hca.PARTY_ID                    cust_acct_party_id,

       hca.ACCOUNT_NUMBER,

       hcas.CUST_ACCT_SITE_ID,

       hcas.PARTY_SITE_ID,

       hps.PARTY_SITE_NUMBER,

       hp.PARTY_NUMBER,

       hp.PARTY_NAME,

       hp.PARTY_TYPE,

       hp.party_id,

       hp.PERSON_FIRST_NAME,

       hp.PERSON_MIDDLE_NAME,

       hp.PERSON_LAST_NAME,

       party_osr.ORIG_SYSTEM           party_osr_system,

       party_osr.ORIG_SYSTEM_REFERENCE party_osr_value

  FROM hz_cust_accounts       hca,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       HZ_CUST_ACCOUNT_ROLES  HCAR,

       HZ_RELATIONSHIPS       HR,

       HZ_PARTIES             HP,

       HZ_ORIG_SYS_REFERENCES party_osr

 WHERE     1 = 1

       AND hca.CUST_ACCOUNT_ID = hcas.CUST_ACCOUNT_ID

       AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID

       AND hca.cust_account_id = hcar.cust_account_id

       AND hcas.cust_acct_site_id = hcar.cust_acct_site_id

       AND HCAR.RELATIONSHIP_ID = HR.RELATIONSHIP_ID

       AND HP.party_id = HR.Object_id

       AND HR.object_type = 'PERSON'

       AND party_osr.OWNER_TABLE_NAME = 'HZ_PARTIES'

       AND party_osr.OWNER_TABLE_ID = hp.PARTY_ID

       AND hps.PARTY_SITE_NUMBER = :P_SITE_NUMBER

       AND hca.account_number = :P_ACCT_NUMBER -- Query for Customer Account detail

       AND hp.PARTY_NAME = :P_PARTY_NAME    -- Query for Contact Person detail


3. Customer Account and Contact Point Details - Query to fetch customer details in the account, contact person and contact points details

SELECT hca.cust_account_id,

       hca.party_id           cust_acct_party_id,

       hca.account_number,

       hcas.cust_acct_site_id,

       hcas.party_site_id,

       hps.party_site_number,

       hp.party_number,

       hp.party_name,

       hp.party_type,

       hp.party_id,

       hp.person_first_name,

       hp.person_middle_name,

       hp.person_last_name,

       party_osr.orig_system  party_osr_system,

       party_osr.orig_system_reference                     party_osr_value,

       hcp_phone.contact_point_type                        phone_cp_type,

       hcp_phone.contact_point_id                          phone_cp_id,

       hcp_phone.status       phone_status,

       hcp_phone.primary_flag                              phone_primary_flag,

       hcp_phone.phone_area_code || hcp_phone.phone_number phone_no,

       hcp_email.contact_point_type                        email_cp_type,

       hcp_email.contact_point_id                          email_cp_id,

       hcp_email.status       email_status,

       hcp_email.primary_flag                              email_primary_type,

       hcp_email.email_address,

       phone_osr.orig_system  phone_osr_system,

       phone_osr.orig_system_reference                     phone_osr_value,

       email_osr.orig_system  email_osr_system,

       email_osr.orig_system_reference                     email_osr_value,

       cust_osr.orig_system   cust_osr_system,

       cust_osr.orig_system_reference                      cust_osr_value,

       site_osr.orig_system   site_osr_system,

       site_osr.orig_system_reference                      site_osr_value

  FROM hz_cust_accounts       hca,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       hz_cust_account_roles  hcar,

       hz_relationships       hr,

       hz_parties             hp,

       hz_contact_points      hcp_email,

       hz_contact_points      hcp_phone,

       hz_orig_sys_references party_osr,

       hz_orig_sys_references phone_osr,

       hz_orig_sys_references email_osr,

       hz_orig_sys_references cust_osr,

       hz_orig_sys_references site_osr

 WHERE     1 = 1

       AND hca.cust_account_id = hcas.cust_account_id

       AND hcas.party_site_id = hps.party_site_id

       AND hca.cust_account_id = hcar.cust_account_id

       AND hcas.cust_acct_site_id = hcar.cust_acct_site_id

       AND hcar.relationship_id = hr.relationship_id

       AND hp.party_id = hr.object_id

       AND hr.object_type = 'PERSON'

       AND hcp_email.owner_table_name(+) = 'HZ_PARTIES'

       AND hcp_email.owner_table_id(+) = hp.party_id

       AND hcp_email.contact_point_type(+) = 'EMAIL'

       AND hcp_phone.owner_table_name(+) = 'HZ_PARTIES'

       AND hcp_phone.owner_table_id(+) = hp.party_id

       AND hcp_phone.contact_point_type(+) = 'PHONE'

       AND party_osr.owner_table_name = 'HZ_PARTIES'

       AND party_osr.owner_table_id = hp.party_id

       AND phone_osr.owner_table_name(+) = 'HZ_CONTACT_POINTS'

       AND phone_osr.owner_table_id(+) = hcp_phone.contact_point_id

       AND email_osr.owner_table_name(+) = 'HZ_CONTACT_POINTS'

       AND email_osr.owner_table_id(+) = hcp_email.contact_point_id

       AND cust_osr.owner_table_name = 'HZ_PARTIES'

       AND cust_osr.owner_table_id = hca.party_id

       AND site_osr.owner_table_name = 'HZ_PARTY_SITES'

       AND site_osr.owner_table_id = hps.party_site_id

       AND hps.party_site_number = :p_site_number

       AND hca.account_number = :p_acct_number

       AND hp.party_name = :p_party_name

 

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect