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" ?>
<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