Saturday, March 19, 2016

How to view Customer Bank Account Details

Goal:
How can we see customer bank account assignment

Solution:
Payables side we can search supplier bank account by going to 'Search Supplier Bank Account Assignment' form. But receivables side we don't have that form.

Here i have created XML report to show the customer bank account details.

Steps:

1. Create a package Spec and Body
2. Define Executable
3. Define Program
4. Assign Concurrent Program to request group
5. Run this Program to get XML format
6. Define RTF Template
7. Define Data Definition
8. Define Template


1. Create a package Spec and Body:

Create Spec:

CREATE OR REPLACE PACKAGE XXXX_CUST_BANK_DETILAS
AS
   PROCEDURE REPORT (  errbuf            OUT VARCHAR2,
                                              retcode           OUT VARCHAR2,
                                              p_account_number  IN     NUMBER);
END XXXX_CUST_BANK_DETILAS;

Create Body:

CREATE OR REPLACE PACKAGE BODY XXIF_CUST_BANK_DETILAS
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_account_number   IN     NUMBER)
   IS
      l_qryCtx      DBMS_XMLGEN.ctxHandle;
      l_query       VARCHAR2 (32000);
      l_length      NUMBER (10);
      l_xmlstr      VARCHAR2 (32000);
      l_offset      NUMBER (10) := 32000;
      l_retrieved   NUMBER (10) := 0;
      l_result      CLOB;
      l_no_rows     NUMBER;
   BEGIN
      l_query := 'select IEBA.bank_account_num,
       HP.Party_name,
       CBBV.bank_name,
       CBBV.bank_branch_name,
       CBBV.branch_number,
       IEBA.bank_account_name,
       IEBA.iban,
       IEBA.bank_account_name_alt
from   IBY_EXT_BANK_ACCOUNTS IEBA,
       IBY_ACCOUNT_OWNERS IAO,
       hz_parties HP,
       ce_bank_branches_v CBBV
where  bank_account_num='|| p_account_number ||'
and    IEBA.ext_bank_account_id= IAO.ext_bank_account_id
and    IAO.Account_owner_party_id=HP.party_id
and    IEBA.bank_id = CBBV.bank_party_id
and    IEBA.branch_id = CBBV.branch_party_id';

      l_qryCtx := DBMS_XMLGEN.newContext (l_query);

      -- set rowset tag to PRODUCTS and row tag to PRO_DETAILS
      DBMS_XMLGEN.setRowSetTag (l_qryCtx, 'PRODUCTS');
      DBMS_XMLGEN.setRowTag (l_qryCtx, 'PRO_DETAILS');

      -- now get the result
      l_result := DBMS_XMLGEN.getXML (l_qryCtx);
      l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
      FND_FILE.put_line (FND_FILE.LOG, 'No of rows processed= ' || l_no_rows);

      l_length := NVL (DBMS_LOB.getlength (l_result), 0);
      FND_FILE.put_line (FND_FILE.LOG, 'Length= ' || l_length);

      LOOP
         EXIT WHEN l_length = l_retrieved;

         IF (l_length - l_retrieved) < 32000
         THEN
            SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

            l_retrieved := l_length;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         ELSE
            SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
              INTO l_xmlstr
              FROM DUAL;

            l_retrieved := l_retrieved + l_offset;
            fnd_file.put (fnd_file.output, l_xmlstr);
         END IF;
      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);
   EXCEPTION
      WHEN OTHERS THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
         raise_application_error (-20001, 'Error in procedure XXIF_CUST_BANK_DETILAS.report');
   END REPORT;
END XXIF_CUST_BANK_DETILAS;

Compile spec and body.

2. Define Executable:

Navigation: System Administrator --> Concurrent --> Program --> Executable

  
Enter the below information

Executable: XXXCBD
Short Name: XXXCBD
Application: Receivables
Execution Method:  PL/SQL Stored Procedure
Execution File Name: XXXX_CUST_BANK_DETILAS ( This is package Name which we have defined earlier step)


Save your work.

3. Define Program:

 Navigation: System Administrator --> Concurrent --> Program -->Define


Enter following information

Program: XXX Customer Bank Account Assignment Detail Report
Short Name: XXXCBAD
Application: Receivables
Executable Name: XXXCBD
Output Format: XML

Click on Parameters tab


Enter the following information:

Seq: 10
Parameter: Customer Bank Account Num
Description: Customer Bank Account Num
Value Set: 15 Number


Save your work.

4. Assign Concurrent Program to request group:

Navigation: System Administrator --> Concurrent --> Program -->Define

Query with Receivables All and add newly created program



5. Run this Program to get XML format:

Go to receivables
View --> Request --> Submit New Request --> Single Request
Enter program name and give bank account number as a parameter

Save the XML file

Below is the output file

<?xml version="1.0" ?>
- <PRODUCTS>
- <PRO_DETAILS>
  <BANK_ACCOUNT_NUM>1234567</BANK_ACCOUNT_NUM>
  <PARTY_NAME>XXXXXX</PARTY_NAME>
  <BANK_NAME>XXXXXX</BANK_NAME>
  <BANK_BRANCH_NAME>XXXXXX</BANK_BRANCH_NAME>
  <BRANCH_NUMBER>11111</BRANCH_NUMBER>
  <BANK_ACCOUNT_NAME>12345678912345</BANK_ACCOUNT_NAME>
  <IBAN>IE12AIBK12345678912345</IBAN>
  <BANK_ACCOUNT_NAME_ALT>ABCDEF</BANK_ACCOUNT_NAME_ALT>
  </PRO_DETAILS>
- 

6. Define RTF Template:

Pre-requisite : Install XML Publisher Desktop
After installation following Menus & Toolbars gets added to the MS Word.


 Load XML file data which we have saved earlier XML file


Once we load the data we should get following confirmation.


Using the Table Wizard as below to create the 'Table Report Format' with the columns of demo_products


Final output is


7. Define Data Definition:

Navigation: XML Publisher Administrator --> Home --> Data Definitions



Click on Crate Data Definition button
Enter the following information

Name:  XXX Customer Bank Account Assignment Details Report DD
Code:  The code should be exactly same as concurrent program short name
Application: Receivables
Description: Data Definition for XXX Customer Bank Account Assignment Details Report



Click on Apply button.


8. Define Template:

Navigation: XML Publisher Administrator --> Home --> Template


Click on Create Template


Enter the following information

Name: XXXX Customer Bank Account Templates
Code: XXXCBDT
Application: Receivables
Data Definition: XXX Customer Bank Account Assigment Details Report DD
Type: RTF
Default Output Type: Excel
File: Attached the RTF file which we have defined earlier step
Language: English


Click on Apply.

Now run the Program

Output should be like below

















No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...