BI Publisher: How to create Multisheet in Single Excel file using Excel Template (Doc ID 1352000.1)

Posted on March 31, 2022 by santosh kumar adda oracleapps

BI Publisher: How to create Multisheet in Single Excel file using Excel Template (Doc ID 1352000.1)

In this Document

Here are the steps required to achieve that:

Applies to:

BI Publisher (formerly XML Publisher) – Version 10.1.3.4.1 to 11.1.1.5.0 [Release 10.1 to 11.1] Information in this document applies to any platform. Checked for relevance on 05-MAR-2013

Goal

This document has been consolidated into Note 1546377.1. Please reference Note 1546377.1 for the latest updates.

How to create Excel Template in BI Publisher that allows splitting the report data into Multiple Sheets in the Same Single Excel file ?

Solution

The following example will generate a Single Excel file with Multiple Sheet, each sheet will display every Department with its allocated Employees in that department.
Attached to the Document the following sample files used in the example below:

Attachments:
1- Excel Template (EmpsbyDeptConditionalBorder.xls)
2- Sample Data (EmpDeptData.xml)
3- Data Template (EmpDept_DataTemplate.xml)

Here are the steps required to achieve that:

First step to create the data model that will generate the data

Note: In the example data template is being used as data source, you can generate the data in other way as you need.

1- Create new Report in BI Publisher Enterprise and its Data Model is based on Data Template that extract the data from 2 tables from HR schema (Employees & Departments):

You will notice that the data template contains 2 queries:

Q1: Retrieve the employees data based on the department id returned from Q2.
Q2: Retrieve the departments data and some other data like number of employees, total salaries, average salary, minimum & maximum salary.

After that it comes the XML data structure for XML data file that will be generated and it has 2 groups:

G_DEPT: For Departments
G_EMP: For Rmployees

So the XML Data file will look like the following:

<?xml version=”1.0″ encoding=”UTF-8″?> <EMPLOYESS> <LIST_G_DEPT> <G_DEPT> <DEPARTMENT_ID>10</DEPARTMENT_ID> <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> <TOTAL_EMPS>1</TOTAL_EMPS> <TOTAL_SALARY>4400</TOTAL_SALARY> <AVG_SALARY>4400</AVG_SALARY> <MAX_SALARY>4400</MAX_SALARY> <MIN_SALARY>4400</MIN_SALARY> <LIST_G_EMP> <G_EMP> <EMPLOYEE_ID>200</EMPLOYEE_ID> <EMP_NAME>Jennifer Whalen</EMP_NAME> <EMAIL>JWHALEN</EMAIL> <PHONE_NUMBER>515.123.4444</PHONE_NUMBER> <HIRE_DATE>1987-09-17T00:00:00.000+10:00</HIRE_DATE> <SALARY>4400</SALARY> </G_EMP> </LIST_G_EMP> </G_DEPT> <G_DEPT> <DEPARTMENT_ID>20</DEPARTMENT_ID> <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> <TOTAL_EMPS>2</TOTAL_EMPS> <TOTAL_SALARY>19000</TOTAL_SALARY> <AVG_SALARY>9500</AVG_SALARY> <MAX_SALARY>13000</MAX_SALARY> <MIN_SALARY>6000</MIN_SALARY> <LIST_G_EMP> <G_EMP> <EMPLOYEE_ID>201</EMPLOYEE_ID> <EMP_NAME>Michael Hartstein</EMP_NAME> <EMAIL>MHARTSTE</EMAIL> <PHONE_NUMBER>515.123.5555</PHONE_NUMBER> <HIRE_DATE>1996-02-17T00:00:00.000+11:00</HIRE_DATE> <SALARY>13000</SALARY> </G_EMP> <G_EMP> <EMPLOYEE_ID>202</EMPLOYEE_ID> <EMP_NAME>Pat Fay</EMP_NAME> <EMAIL>PFAY</EMAIL> <PHONE_NUMBER>603.123.6666</PHONE_NUMBER> <HIRE_DATE>1997-08-17T00:00:00.000+10:00</HIRE_DATE> <SALARY>6000</SALARY> </G_EMP> </LIST_G_EMP> …..

The following steps are to create the EXCEL template:

2- Create a blank Excel file and save it as “Excel 97-2003 Workbook binary format (*.xls)”
3- The Template layout will be like the following (figure 1):

As in RTF template we have to define the fields and groups that will be mapped to the XML data file.
To do that in the excel template we do what we call “Assigning BI Publisher defined names” to each cell and group that will be mapped to the data. Those defined names must start with a prefix XDO_ .
The cells must be named according to the following format:

Data elements: XDO_?element_name?
where
XDO_ is the required prefix
and
?element_name? is either:
The XML tag name from your data delimited by “?” OR A unique name that you will use to map a derived value to the cell
For example: XDO_?EMPLOYEE_ID?

Data groups: XDO_GROUP_?group_name?
where
XDO_GROUP_ is the required prefix
and
?group_name? is either:
The XML tag name for the parent element in your XML data delimited by “?”. OR A unique name that you will use to define a derived grouping logic
For example: XDO_GROUP_?DEPT?

Note that the question mark delimiter, the group_name, and the element_name are case sensitive.

4- Click the cell in the Excel worksheet.
5- Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: A8.
6- In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?EMP_NAME?
7- Press Enter.
The following figure shows the defined name for the Employee Name field entered in the Name box:

8- Repeat for each of the following data fields: DEPARTMENT_NAME, EMPLOYEE_ID, EMAIL, PHONE_NUMBER, and SALARY.

Tips:
If you navigate out of the Name box without pressing Enter, the name you entered will not be maintained.
You cannot edit the Name box while you are editing the cell contents.
The name cannot be more than 255 characters in length.

9- After you have entered all the fields, you can review the names and make any corrections or edits using the Name Manager feature of Excel. Access the Name Manager from the Formulas tab in Excel as shown:

10- After you have named all the cells for this example, the Name Manager dialog will appear as shown:

11- Now it is time to create the group(s) for the report in the excel template: Highlight the cells that make up the group. In this example the cells are A8 – E8
12- Click the Name box at the left end of the formula bar and enter the name using the XDO_GROUP_ prefix and the tag name for the group from your data. In this example: XDO_GROUP_?EMPS?
13- Press Enter.
The following figure shows the XDO_GROUP_ defined named entered for the Employees group. Note that just the row of employee data is highlighted. Do not highlight the headers. Note also that the total cell XDO_?TOTAL_SALARY? is not highlighted

14- To define the department group, include the department name cell and all the employee fields beneath it (A5-E9) as shown in the following figure:

Enter the name for this group as: XDO_GROUP_?DEPT? to match the group in the data. Note that the XDO_?TOTAL_SALARY? cell is included in the department group to ensure it repeats at the department level.

The following steps are very important which are for preparing and creating XDO_METADATA Sheet in the template:

15- The following figure shows the default or the initial XDO_METADATA sheet that any excel sheet should have:

16- Create a new sheet in your Excel Workbook and name it “XDO_METADATA”
17- Create the header section by entering the following variable names in column A, one per row, starting with row 1:

Version ARU-dbdrv Extractor Version Template Code Template Type Preprocess XSLT File Last Modified Date Last Modified By

18- Skip a row and enter “Data Constraints” in column A of row 10
19- In the header region, for the variable “Template Type” enter the value: TYPE_EXCEL_TEMPLATE

Now doing the steps to splitting the Report into Multiple Sheets:

20- This example will: – Create a new worksheet for each department – Name each worksheet the name of the department with the number of employees in the department, for example: Sales-21.
21- In the XDO_METADATA sheet we need to do the following:

Use the this set of commands to define the logic to split the report data into multiple sheets:

Use XDO_SHEET_? to define the logic by which to split the data onto a new sheet.
Use XDO_SHEET_NAME_? to specify the naming convention for each sheet.

Column A Entry	Column B Entry	Column C Entry
XDO_SHEET_?	<?xsl_evaluation to split the data?> Example: <?.//G_DEPT?>	n/a
XDO_SHEET_NAME_?	<?xsl_expression to name the sheet?> Example: <?concat(.//DEPARTMENT_NAME,’-‘,count(.//EMP_NAME))?>	(Optional) <?original sheet name?> Example: <?Sheet3?>

Now the XDO_METADATA sheet should look like the following figure:

XDO_SHEET_? must refer to an existing high-level node in your XML data. The example <?.//G_DEPT?> will create a new sheet for each occurrence of <G_DEPT> in the data.
If your data is flat you cannot use this command unless you first preprocess the data to create the desired hierarchy. To preprocess the data, define the transformation in an XSLT file, then specify this file in the Preprocess XSLT File field of the header section of the XDO _METADATA sheet.

We are done, Now Test the Template:

22- If you have installed the Template Builder for Excel, the BI Publisher tab will appear on the ribbon menu as shown in the following figure:

23- Click Sample XML. You will be prompted to select the sample data file.
24- Click Preview.
The sample data will be applied to your template and the output document will be opened in a new workbook.
The following figure shows the generated report. Each department data now displays on its own sheet, which shows the naming convention specified:

Stay Connected with us for more BI Publisher updates through the following channels:
1- Youtube 2- Twitter 3- Linkedin 4- Facebook
If you have any questions regarding BI Publisher or would like to read more updated articles about BI Publisher from Oracle BI Publisher Experts within Oracle please visit the following communities:
Oracle BI Publisher Blog http://blogs.oracle.com/xmlpublisher/
Oracle BI Publisher Forum https://forums.oracle.com/forums/forum.jspa?forumID=245
My Oracle Support – BI Publisher Community https://community.oracle.com/community/support/oracle_e-business_suite/bi_publisher

Posted in XML Publisher | Leave a comment

Bank API in Oracle apps R12 (Banks, Branch, Accounts) with Code and link details

Posted on July 30, 2021 by santosh kumar adda oracleapps

Bank API in Oracle apps R12 (Banks, Branch, Accounts) with Code and link details

http://appselangovan.blogspot.com/2012/03/bank-api-in-oracle-apps.html

— Bank API in Oracle apps R12 (Banks, Branch, Accounts)

Rem ===================================================================

CE_BANK_PUB.CREATE_BANK API to Create an Internal Bank in R12===================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_country_code VARCHAR2 (200);

p_bank_name VARCHAR2 (200);

p_bank_number VARCHAR2 (200);

p_alternate_bank_name VARCHAR2 (200);

p_short_bank_name VARCHAR2 (200);

p_description VARCHAR2 (200);

p_tax_payer_id VARCHAR2 (200);

p_tax_registration_number VARCHAR2 (200);

x_bank_id NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_count NUMBER;

BEGIN

p_init_msg_list := fnd_api.g_true;

p_country_code := ‘US’;

p_bank_name := ‘Test Bank’;

p_bank_number := ‘14258’;

p_alternate_bank_name := ‘Alternate Test Bank’;

p_short_bank_name := ‘IDFC’;

p_description := ‘Test Bank Creation API’;

CE_BANK_PUB.CREATE_BANK

(p_init_msg_list => p_init_msg_list,

p_country_code => p_country_code,

p_bank_name => p_bank_name,

p_bank_number => p_bank_number,

p_alternate_bank_name => p_alternate_bank_name,

p_short_bank_name => p_short_bank_name,

p_description => p_description,

x_bank_id => x_bank_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line (‘BANK_ID/PARTY_ID = ‘ || x_bank_id);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.put_line (‘x_msg_data ‘ || x_msg_data);

ELSIF x_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (‘Message’ || p_count || ‘ —‘ || x_msg_data);

END LOOP;

END IF;

END;

Rem ===================================================================

Rem API to Update an Internal Bank in R12 (CE_BANK_PUB.UPDATE_BANK)====================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_bank_id NUMBER;

p_bank_name VARCHAR2 (200);

p_bank_number VARCHAR2 (200);

p_alternate_bank_name VARCHAR2 (200);

p_short_bank_name VARCHAR2 (200);

p_description VARCHAR2 (200);

p_object_version_number NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_count NUMBER;

BEGIN

p_init_msg_list := fnd_api.g_true;

p_bank_id := 1147;

p_bank_name := ‘test bank2’;

p_bank_number := 3933;

p_alternate_bank_name := ‘Alternate Test Bank2’;

p_short_bank_name := ‘IDF’;

p_description := ‘Test Bank2 Description’;

p_object_version_number := 1;

CE_BANK_PUB.UPDATE_BANK

(p_init_msg_list => p_init_msg_list,

p_bank_id => p_bank_id,

p_bank_name => p_bank_name,

p_bank_number => p_bank_number,

p_alternate_bank_name => p_alternate_bank_name,

p_short_bank_name => p_short_bank_name,

p_description => p_description,

p_object_version_number => p_object_version_number,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line ( ‘P_OBJECT_VERSION_NUMBER = ‘

|| p_object_version_number

);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.put_line (‘x_msg_data ‘ || x_msg_data);

ELSIF x_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (‘Message’ || p_count || ‘ —‘ || x_msg_data);

END LOOP;

END IF;

END;

Rem ================================================================================

Rem API to Check Existence of an Internal Bank R12 (CE_BANK_PUB.CHECK_BANK_EXIST)================================================================================

DECLARE

p_country_code VARCHAR2 (200);

p_bank_name VARCHAR2 (200);

p_bank_number VARCHAR2 (200);

x_bank_id NUMBER;

x_end_date DATE;

BEGIN

p_country_code := ‘US’;

p_bank_name := ‘Test Bank’;

p_bank_number := 14258;

ce_bank_pub.check_bank_exist (p_country_code => p_country_code,

p_bank_name => p_bank_name,

p_bank_number => p_bank_number,

x_bank_id => x_bank_id,

x_end_date => x_end_date

);

DBMS_OUTPUT.put_line ( ‘BANK_ID/PARTY_ID = ‘

|| NVL (TO_CHAR (x_bank_id), ‘NOT EXISTS’)

);

DBMS_OUTPUT.put_line ( ‘END_DATE = ‘

|| NVL (TO_CHAR (x_end_date), ‘NOT EXISTS’)

);

END;

Rem ================================================================================

Rem API to End Date an Internal Bank in R12 – CE_BANK_PUB.SET_BANK_END_DATE================================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_bank_id NUMBER;

p_end_date DATE;

p_object_version_number NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

BEGIN

p_init_msg_list := fnd_api.g_true;

p_bank_id := 119147;

p_end_date := SYSDATE + 360;

p_object_version_number := 1;

CE_BANK_PUB.SET_BANK_END_DATE

(p_init_msg_list => p_init_msg_list,

p_bank_id => p_bank_id,

p_end_date => p_end_date,

p_object_version_number => p_object_version_number,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line ( ‘P_OBJECT_VERSION_NUMBER = ‘

|| p_object_version_number

);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

END;

Rem ======================

Rem BANK BRANCH APIS======================

Rem ================================================================================

Rem API to Create an Internal Bank Branch R12 (CE_BANK_PUB.CREATE_BANK_BRANCH)================================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_bank_id NUMBER;

p_branch_name VARCHAR2 (200);

p_branch_number VARCHAR2 (200);

p_branch_type VARCHAR2 (200);

p_alternate_branch_name VARCHAR2 (200);

p_description VARCHAR2 (200);

p_rfc_identifier VARCHAR2 (200);

x_branch_id NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_count NUMBER;

BEGIN

p_init_msg_list := fnd_api.g_true;

p_bank_id := 219143; — PARTY_ID of the BANK

p_branch_name := ‘Test Branch’;

p_alternate_branch_name := ‘Alternate Branch Name’;

p_description := ‘TEST Branch description’;

— branch number/Routing Transit Number is subjected to

— the check digit validation

p_branch_number := 111000025;

— Validated against AR_LOOKUPS with lookup_type = BANK_BRANCH_TYPE

p_branch_type := ‘ABA’;

— Validated against CE_LOOKUPS with lookup_type = RFC_IDENTIFIER

p_rfc_identifier := ‘AFC’;

CE_BANK_PUB.CREATE_BANK_BRANCH

(p_init_msg_list => p_init_msg_list,

p_bank_id => p_bank_id,

p_branch_name => p_branch_name,

p_branch_number => p_branch_number,

p_branch_type => p_branch_type,

p_alternate_branch_name => p_alternate_branch_name,

p_description => p_description,

p_rfc_identifier => p_rfc_identifier,

x_branch_id => x_branch_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line (‘BRANCH_ID/PARTY_ID = ‘ || x_branch_id);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.put_line (‘x_msg_data ‘ || x_msg_data);

ELSIF x_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (‘Message’ || p_count || ‘ —‘ || x_msg_data);

END LOOP;

END IF;

END;

Rem ================================================================================

Rem API to Update an Internal Bank Branch R12 (CE_BANK_PUB.UPDATE_BANK_BRANCH)================================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_branch_id NUMBER;

p_branch_name VARCHAR2 (200);

p_branch_number VARCHAR2 (200);

p_branch_type VARCHAR2 (200);

p_alternate_branch_name VARCHAR2 (200);

p_description VARCHAR2 (200);

p_country_validate VARCHAR2 (200);

p_bch_object_version_number NUMBER;

p_typ_object_version_number NUMBER;

p_rfc_object_version_number NUMBER;

p_eft_object_version_number NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_count NUMBER;

BEGIN

p_init_msg_list := fnd_api.g_true;

p_branch_id := 219154;

p_branch_number := 111000025;

p_branch_type := ‘ABA’;

p_branch_name := ‘Test Branch’;

p_alternate_branch_name := ‘updated Alternate Branch Name’;

p_description := ‘TEST Branch description’;

p_country_validate := NULL;

p_bch_object_version_number := 1;

CE_BANK_PUB.UPDATE_BANK_BRANCH

(p_init_msg_list => p_init_msg_list,

p_branch_id => p_branch_id,

p_branch_name => p_branch_name,

p_branch_number => p_branch_number,

p_branch_type => p_branch_type,

p_alternate_branch_name => p_alternate_branch_name,

p_description => p_description,

p_country_validate => p_country_validate,

p_bch_object_version_number => p_bch_object_version_number,

p_typ_object_version_number => p_typ_object_version_number,

p_rfc_object_version_number => p_rfc_object_version_number,

p_eft_object_version_number => p_eft_object_version_number,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line ( ‘P_BCH_OBJECT_VERSION_NUMBER = ‘

|| p_bch_object_version_number

);

DBMS_OUTPUT.put_line ( ‘P_TYP_OBJECT_VERSION_NUMBER = ‘

|| p_typ_object_version_number

);

DBMS_OUTPUT.put_line ( ‘P_RFC_OBJECT_VERSION_NUMBER = ‘

|| p_rfc_object_version_number

);

DBMS_OUTPUT.put_line ( ‘P_EFT_OBJECT_VERSION_NUMBER = ‘

|| p_eft_object_version_number

);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.put_line (‘x_msg_data ‘ || x_msg_data);

ELSIF x_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (‘Message’ || p_count || ‘ —‘ || x_msg_data);

END LOOP;

END IF;

END;

Rem ================================================================================

Rem CE_BANK_PUB.CHECK_BRANCH_EXISTS – API to check existence of an Internal Bank Branch in R12================================================================================

DECLARE

p_bank_id NUMBER;

p_branch_name VARCHAR2 (200);

p_branch_number VARCHAR2 (200);

x_branch_id NUMBER;

x_end_date DATE;

BEGIN

p_bank_id := 1245;

p_branch_name := ‘Test Branch’;

p_branch_number := ‘1452789’;

CE_BANK_PUB.CHECK_BRANCH_EXIST

(p_bank_id => p_bank_id,

p_branch_name => p_branch_name,

p_branch_number => p_branch_number,

x_branch_id => x_branch_id,

x_end_date => x_end_date

);

DBMS_OUTPUT.put_line (‘X_BRANCH_ID = ‘ || x_branch_id);

DBMS_OUTPUT.put_line (‘X_END_DATE = ‘ || x_end_date);

END;

Rem =================================================================================

Rem API to End Date an Internal Bank Branch in R12 – CE_BANK_PUB.SET_BANK_BRANCH_END_DATE=================================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_branch_id NUMBER;

p_end_date DATE;

p_object_version_number NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

BEGIN

p_init_msg_list := fnd_api.g_true;

p_branch_id := 72141; — CE_BANK_BRANCHES_V.BRANCH_PARTY_ID

p_end_date := SYSDATE + 360;

p_object_version_number := 1;

CE_BANK_PUB.SET_BANK_BRANCH_END_DATE

(p_init_msg_list => p_init_msg_list,

p_branch_id => p_branch_id,

p_end_date => p_end_date,

p_object_version_number => p_object_version_number,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line ( ‘P_OBJECT_VERSION_NUMBER = ‘

|| p_object_version_number

);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

END;

Rem ====================

Rem BANK ACCOUNT APIS ====================

Rem =================================================================================

Rem API to Create an Internal Bank Account in R12 CE_BANK_PUB.CREATE_BANK_ACCT================================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_acct_rec apps.ce_bank_pub.bankacct_rec_type;

x_acct_id NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_count NUMBER;

BEGIN

p_init_msg_list := NULL;

— HZ_PARTIES.PARTY_ID BANK BRANCH

p_acct_rec.branch_id := 8056;

— HZ_PARTIES.PARTY_ID BANK

p_acct_rec.bank_id := 8042;

— HZ_PARTIES.PARTY_ID ORGANIZATION

p_acct_rec.account_owner_org_id := 23273;

— HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION

p_acct_rec.account_owner_party_id := 2041;

p_acct_rec.account_classification := ‘INTERNAL’;

p_acct_rec.bank_account_name := ‘Test Bank Accunt’;

p_acct_rec.bank_account_num := 14256789;

p_acct_rec.currency := ‘USD’;

p_acct_rec.start_date := SYSDATE;

p_acct_rec.end_date := NULL;

CE_BANK_PUB.CREATE_BANK_ACCT

(p_init_msg_list => p_init_msg_list,

p_acct_rec => p_acct_rec,

x_acct_id => x_acct_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line (‘X_ACCT_ID = ‘ || x_acct_id);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.put_line (‘x_msg_data ‘ || x_msg_data);

ELSIF x_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (‘Message’ || p_count || ‘ —‘ || x_msg_data);

END LOOP;

END IF;

END;

Rem ==========================================================================================

Rem API to update an Internal Bank Account in Oracle Apps R12 (CE_BANK_PUB.UPDATE_BANK_ACCT)==========================================================================================

DECLARE

p_init_msg_list VARCHAR2 (200);

p_acct_rec apps.ce_bank_pub.bankacct_rec_type;

p_object_version_number NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_count NUMBER;

BEGIN

p_init_msg_list := NULL;

p_acct_rec.bank_account_id := 41001;

— HZ_PARTIES.PARTY_ID BANK BRANCH

p_acct_rec.branch_id := 8056;

— HZ_PARTIES.PARTY_ID BANK

p_acct_rec.bank_id := 8042;

— HZ_PARTIES.PARTY_ID ORGANIZATION

p_acct_rec.account_owner_org_id := 23273;

— HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION

p_acct_rec.account_owner_party_id := 2041;

p_acct_rec.account_classification := ‘INTERNAL’;

p_acct_rec.bank_account_name := ‘Test Bank Accunt’;

p_acct_rec.bank_account_num := 18256889;

p_acct_rec.currency := ‘USD’;

p_acct_rec.start_date := SYSDATE;

p_acct_rec.end_date := NULL;

p_object_version_number := 1;

CE_BANK_PUB.UPDATE_BANK_ACCT

(p_init_msg_list => p_init_msg_list,

p_acct_rec => p_acct_rec,

p_object_version_number => p_object_version_number,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line ( ‘P_OBJECT_VERSION_NUMBER = ‘

|| p_object_version_number

);

DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);

DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || x_msg_count);

DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.put_line (‘x_msg_data ‘ || x_msg_data);

ELSIF x_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (‘Message’ || p_count || ‘ —‘ || x_msg_data);

END LOOP;

END IF;

END;

Bank API in Oracle apps R12 (Banks, Branch, Accounts)

Posted in Payables | Leave a comment

How to set up and use Bank Statement Loader – 161872.1.doc

Posted on July 23, 2021 by santosh kumar adda oracleapps

How to set up and use Bank Statement Loader – 161872.1.doc

How to set up and use Bank Statement Loader [ID 161872.1]

	Modified 12-APR-2012 Type BULLETIN Status PUBLISHED

PURPOSE

——-

This document will assist you in setting up and using the Bank Statement Loader

functionality in Release 11i and Release 12. It will even take you through the process

of creating your own data file for testing purposes.

SCOPE & APPLICATION

——————-

If the user is already in possession of a valid data file, then these steps

can be followed by a casual user. However, the creation of a new test data

file should only be attempted only by an accomplished user. These procedures

only apply to Release 11i and Release 12.

Set Up and Use Bank Statement Loader

————————————

There are several different formats which can be used when utilizing the Bank

Statement Loader. For purposes of example, this document uses the BAI2 format.

There are several areas which must be setup before you will be able to use the

Bank Statement Loader successfully:

Step 1 – Bank Account Setup

—————————

Create your bank and bank account in Accounts Payable. Note your bank account

number and branch name. In our example, we will use the following:

Bank: Bank of America

Branch: New York

Account number: 10271-17621-619

Step 2 – Bank Transaction Codes

——————————-

Setup your Bank Transaction Codes in Cash Management. Find the bank account

number that you are using, and enter the transaction codes provided by your

bank. These differ from bank to bank, and thus are client specific. In our

example, we will use the following:

Type Code Description Transaction Source

Payment 100 Regular Payment AP Payments

Payment 130 EFT/Wire Payment AP Payments

Note: If the customer has Accounts Receivable, they may also have Receipt

Type Bank Transaction Codes to define the same way.

Step 3 – Bank Statement Mapping

——————————-

Set up your Bank Statement Mapping in Cash Management. It is a good idea to

copy the seeded mappings to your own so that you can modify it freely. When

you enter the form, it will prompt you to find a mapping. Cancel that dialog.

When the find window disappears, give your new format a name and description.

Use the existing control file and supply the desired date format. Define the

precision and choose the appropriate format type. Then click on Populate and

save.

This will copy the default mapping to your new name. You are then free to

modify it as you wish. For our example, no changes were made to the default

mapping.

Step 4 – Creating the BAI2 Data File

————————————

If you already have a valid format data file from your bank, you can skip this

step completely. However, if you want to create a sample BAI2 data file for

testing purposes, follow the instructions in this step.

This is perhaps the most difficult step in the setup. A wrongly placed comma

or incorrectly placed data value can wreak havoc on the Bank Statement Loader

and cause a failure.

You will want to use a text editor like vi or notepad. If you use Microsoft

Word or another word processor, you will have to Save As Plain Text. If you

create the file on your PC, remember to FTP it as ASCII when you move it to the

server.

There are some mandatory header and footer records in a BAI2 data file, between

which are your actual detail (or line level) records. Each record is described

in as much detail as is needed to get the program to work.

Record 01 – Mandatory

———————

This should be your first record in the file. It should be of the following

format:

01,<bank originator ID>,<bank customer ID>,<file creation date>,

<file creation time>,<file identification number>,<physical record length>,

<block size>,<version number>/

For example, our file will use the following:

01,121345678,7777777,011031,1431,1431,80,1,2/

This means the file is from Bank 121345678 for its customer 7777777, and was

created on 31-OCT-2001 at 2:31 PM. It has 80 characters per record, 1 record

per block and is BAI2 format (as denoted by the version number 2).

Record 02 – Mandatory

———————

This should be the second record in the file. It should be of the following

format:

02,<bank customer ID>,<bank originator ID>,<group status>,<as of date>,

<as of time>,<currency code>,<as of date modifier>/

The group status can be 1 for “update”, 2 for “deletion”, 3 for “correction”,

or 4 for “test only”.

For example, our file will use the following values:

02,7777777,121345678,1,011022,0000,USD,/

This means we have received an update type file with USD transactions through

22-OCT-01 at midnight.

Record 03 – Mandatory

———————

This should be the third record in the file. It should be of the following

format:

03,<bank account number>,<currency code>,<type code>,<sign><amount>,

<item count>,<funds type>/

With the last 5 fields being repeated as many times as needed for each

type code. Note that there is no delimiter between the sign and amount fields.

For example, our file will use the following values:

03,10271-17621-619,USD,400,1153083,4,/

This denotes that for our USD bank account number 10271-17621-619, we have 4

transactions that total $11530.83.

Record 16 – Mandatory, multiple occurrences

Record 88 – Optional, multiple occurrences

——————————————-

The record type 16 will likely have many records, which should be the

fourth and subsequent records. Each will be of the following format:

16,<transaction code>,<amount>,<funds type>,<bank ref #>,<customer ref #>,

<text>/

In our example below, this is a regular payment (transaction code 100) for

$769.95 (because precision is 2), which has a value date of 20-OCT-2001 (as

per fields 3 and 4). Once imported, we will see “Bank Reference Text” in the

Agent field, “26446” in the Invoice field, and “Office supplies” in the

Description field.

16,100,76995,V,011020,,Bank Reference Text,26446,Office Supplies/

You will have one record for each payment. Sometimes, the text for a record

type 16 will get really long. That is when you use an 88 record, or overflow

record. For example:

16,100,1574543,V,011020,,A long amount of Text,Also a long amount of Text,

Even still more Text/

could be broken up into 2 separate records such as:

16,100,1574543,V,011020,,A long amount of Text,Also a long amount of Text

88, Even still more Text/

Record 49 – Mandatory

———————

This record should follow the last 16 record for the given bank account. It

should be of the following format:

49,<account control total>,<number of records for account>/

The account control total sums all the amounts in records 03, 16 and 88 and

includes an amount sign. The record count for the account is for all records

up to and including the 49 record itself.

For our example, the record would look like:

49,2306166,8/

Record 98 – Mandatory

———————

This record follows the last 49 record. It should be of the following format:

98,<group control total>,<number of accounts>,<number of records in group>/

Group control total is the sum of all control totals in 49 records for this

group. The number of accounts will be the same as the number of 03 records

in the file.

For our example, the record would look like:

98,2306166,1/

Record 99 – Mandatory

———————

This will be the last record in your file. It should be of the following

format:

99,<file control total>,<number of groups>,<number of records>/

File control total is the sum of all group control totals in 98 records.

The number of groups should match the number of 02 records.

For our example, the record would look like:

99,2306166,1,10/

Here is resulting data file in entirety:

01,121345678,7777777,011031,1431,1431,80,1,2/

02,7777777,121345678,1,011022,0000,USD,/

03,10271-17621-619,USD,400,1153083,4,/

16,100,76995,V,011020,,Bank Reference Text,26446,Office Supplies/

16,100,812213,V,011015,,323532,A39599,Travel expenses/

16,100,242500,V,011017,,5434634N,46400-333,Rent expense/

16,100,21375,V,011019,,264,83832,Team Building Event/

49,2306166,8/

98,2306166,1/

99,2306166,1,10/

Concurrent programs to Run

————————–

You have the option of running the concurrent programs in a variety of ways.

When testing, it is recommended to run each program one at a time, so that you

can see where in the process you are in case an error occurs. Once you have

the setup completed, you can run all of the programs in one step.

Bank Statement Loader

The required parameters to this job are the following:

Process Option – Choose “Load”.

Mapping Name – Pick the one you created in the Bank Statement Mapping section,

or if you used a standard one, pick that.

Data File Name – This is whatever you named your data file. Typical convention

is to use the .dat extension. Example: bofa123101.dat

Directory Path – If you placed your data file in the $CE_TOP/bin directory,

leave this parameter blank. Otherwise, you must provide the entire directory

path to your data file. Do NOT use any environment variables in your pathname.

Bad example: $CE_TOP/out/bofa123101.dat

Good example: /amer/oracle/crmus01/crmus01appl/ce/11.5.0/out/ bofa123101.dat

Display Debug – Defaults to “N”, but it is recommended to set it to “Y” to aid

in debugging issues.

This job will kick off three additional concurrent programs:

Run SQL*Loader- <format name> – This program takes the data from your data file

and loads it into the CE_STMT_INT_TMP table. This program has no output, but

you can see on the last page of the log file how many records got loaded and

how many got rejected.

Load Bank Statement Data – This program takes the data from the

CE_STMT_INT_TMP table and loads it into the CE_STATEMENT_HEADERS_INTERFACE and

CE_STATEMENT_LINES_INTERFACE tables. There is no output for this program, and

the log file is useless in debugging.

Bank Statement Loader Execution Report – This program provides some information

about what the previous programs did. It has an output report, which has

minimal value. Occasionally, you will get a good error message or warning,

but typically it reports everything is fine even if there was an issue. The

log file is not helpful in debugging issues.

Bank Statement Import

The required parameters to this job are the following:

Bank Branch Name – Provide the bank branch name that you setup in the Bank

Account Setup section.

GL Date – Although this parameter is not marked as required, the import will

often fail if you do not provide a value for this parameter. The date must in

an open period in both AP and AR.

Note: If you have multiple files loaded, but only want to import one of them,

use the Statement Date or Statement Number range parameters to limit the import

job.

If successful, this program moves records from the

CE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables into

the CE_STATEMENT_HEADERS and CE_STATEMENT_LINES tables.

This concurrent program will launch one other concurrent program:

AutoReconciliation Execution Report – Although misleadingly named, this report

is useful. It will show exceptions which may have occurred during the import.

AutoReconciliation

The required parameters to this job are the following:

Bank Branch Name – Provide the bank branch name that you set up in the Bank

Account section.

GL Date – Although this parameter is not marked as required, the import will

often fail if you do not provide a value for this parameter. The date must in

an open period in both AP and AR.

Note: If you have multiple files loaded, but only want to import one of

them, use the Statement Date or Statement Number range parameters to limit

the import job.

If successful, this program will reconcile the imported bank statement lines

to outstanding AP and AR transactions. If unsuccessful, it will mark the bank

statement line with an error and allow you to manually reconcile the

transaction.

This concurrent program will launch one other concurrent program:

AutoReconciliation Execution Report – This time this same report shows

exceptions in matching up the imported bank statement transactions with the

existing AP and AR transactions in the system. It gives descriptive reasons

why the line was not able to be reconciled automatically.

Posted in Cash Management | Leave a comment

Terminology often used in Oracle Applications: Terminology often used in Oracle Applications

Posted on December 30, 2020 by santosh kumar adda oracleapps

Terminology often used in Oracle Applications: Terminology often used in Oracle Applications:

Invoice
Receipt
Customer
Vendor
Buyer
Supplier
Purchase Order
Requisition
ACH: Account Clearance House
Sales Order
Pack Slip
Pick Slip
Drop Ship
Back Order
ASN: Advance Shipping Notice
ASBN: Advance Shipping Billing Notice
ATP: Available to Promise
Lot/Serial Number
DFF: Descriptive Flex Fields
KFF: Key Flex Fields
Value Sets
Organization
Business Unit
Multi Org
Folders
WHO Columns
Oracle Reports
Oracle Form
Workflow Builder
Toad
SQL Developer
SQL Navigator
Discoverer Reports
XML/BI Publisher
ADI: Application Desktop Integrator
Winscp
Putty

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers. The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications.Harry keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES. Harry gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes. Fruit Mart is called CUSTOMER and Harry maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers. Say the fruits have been shipped to the customer Fruit Mart. Harry now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments. Harry’s fruit business has begun to improve and has attracted more and more customers. As a result, Harry decided to buy a cold storage unit where he could stock more fruits. At the end of every month, Harry pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps. As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, which help the business men to keep track of everything. Oracle Applications is not a single application, but is a collection of integrated applications. Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, Fixed Assets etc. Oracle Accounts Payables handles all the payments to the vendors.Order Management helps you collect all the information that your customers order.Oracle Human Resources helps maintain the Employee information, helps run paychecks etc. For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment, it is transferred to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss. This is the very basic explanation just to give an idea of the flow in ERP for the beginners.

There is a lot more in Oracle applications.

Oracle Fixed Assets :- All the equipment, shops, warehouses, computers can be termed as ASSETS and they are managed by Oracle Fixed Assets.

Oracle General Ledger receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc.

Oracle Receivables help you collect the money for the orders that are delivered to the customers.

Oracle Inventory deals with the items you maintain in stock, warehouse etc.

Oracle Purchasing handles all the requisitions and purchase orders to the vendors.

Here is a high level business use of various modules:

Each application is referred to as a module and has it own functionality trying to serve a business purpose.

One such tool is Oracle E-Business Suite.

At the end of every month, Harry prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.

In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY. Due to increase in customers, Harry needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES.

This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.

Harry’s customer, Fruit Mart pays him in installments (partial payments). So Harry has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Harry receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Harry to find how much Fruit Mart has paid to him and how much has to be paid yet.

the order is referred to as SALES ORDER.

In Oracle Apps, bags and kgs are referred to as UOM (unit of measure),

Example: – Say Harry is the owner of a wholesale fruit shop.

Story

Posted in Other IMP | Leave a comment

Oracle Reports FAQ’s

Posted on December 30, 2020 by santosh kumar adda oracleapps

Oracle Reports FAQ’s

1.What is SRW Package?

Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors

Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views , etc.,

SRW.SET_FIELD_NUM

SRW. SET_FIELD_CHAR

SRW. SET FIELD _DATE

2.What are Lexical Parameters and bind parameters?

Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)

What is User Parameters?

A parameter, which is created by user. For to restrict values with where clause in select statement.

Data type, width, input mask, initial value, validation trigger, list of values

We can use Lovs in use in user parameter with static and Dynamic Select Statement.

What is System Parameters: These are built-in parameters by corporation.

BACKGROUND: Is whether the report should run in the foreground or the background.

COPIES Is the number of report copies that should be made when the report is printed.

CURRENCY Is the symbol for the currency indicator (e.g., “$”).

DECIMAL Is the symbol for the decimal indicator (e.g., “.”).

DESFORMAT Is the definition of the output device’s format (e.g., landscape mode for a printer). This

parameter is used when running a report in a character-mode environment, and when

sending a bitmap report to a file (e.g. to create PDF or HTML output).

DESNAME Is the name of the output device (e.g., the file name, printer’s name, mail userid).

DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or

screen using PostScript format).

MODE Is whether the report should run in character mode or bitmap.

ORIENTATION Is the print direction for the report (landscape, portrait, default).

PRINTJOB Is whether the Print Job dialog box should appear before the report is run.

THOUSANDS Is the symbol for the thousand’s indicator (e.g., “,”).

How many Types of Reports available in Reports

Tabular form-like form – letter Group left

Group above matrix Matrix with group Mailing label

Matrix Report: Simple, Group above, Nested

Simple Matrix Report required 4 groups

1.Cross Product Group

Row and Column Group
Cell Group
Cell column is the source of a cross product summary that becomes the cell content.

Frames: 1.Repeating frame for rows (down direction)

2.Repeating frame for columns (Across)

3.Matrix object the intersection of the two repeating frames

6.What Types of Triggers are Available in Reports.

Report level Triggers
Data Model Triggers
Layout Model TriggersBefore parameter form: If u want take parameters passed to the report and manipulate them so that they appear differently in the parameter form., this is where modification can be done for ex: when u want pass a deptno but show the dname selected , use a before parameter form trigger.Between pages: This Trigger fires before all pages except first page one. It will not fire after the last page of a report. If a report only has one page it will not fire at all. You can use this trigger to send specific control to the change the paper orientation or to do double sided printingData Model Triggers Layout Model Triggers Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogetherTo format a column based on certain criteria for exampleii) To format the Sal column with a Dollar($) prefix.8. What is Data Model? 9. What is Layout model? 10 What is Livepreviewer? To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size ,move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.TitleRulersToolbarTool palette11. What is Parameter Form The Parameter Form view is the work area in which you define the format of the report’s Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).12. What is Query? 13. What is Group? 14 What is Repeating Frame? Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model The repeating frame prints (is fired) once for each record of the group.A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.n more easily administer SQLn share datasources with other applications, such as Form Buildern encapsulate logic within a subprogram16. What is Template? Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Furthermore, if you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.
n increase control and security
n avoid the use of lexical parameters in your reports
You base a query on a ref cursor when you want to:
15. What is Reference Cursor?
Ans: Repeating frames surround all of the fields that are created for a group’s columns.
Ans: Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.
Ans: Parameters are variables for report that users can change at runtime immediately prior to the execution of the report. You can use system parameters to specify aspects of report execution, such as the output format, printer name , mailed or number of copies. We can also create own parameters through sql or Pl/sql at runtime.
Status bar
Style bar
Grid
Viewing region
Access
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual, or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
Layout Model is to Physically arrange Data model group objects on the Report. The Report Editor’s Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.
Data Model is logically group of the Report Objects through query and Data model tools . Once query is compiled report automatically generates group. The queries build the groups ant then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor’s Data Model view enables you to define and modify the data model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.
iii) To format Date formats….etc
i) To format the max(Sal) for particular department.
For Headings, for repeating frames, for field, for boilerplate object
7. What is Format triggers.
Formula Column, Group Filter, Parameter values
After report: This trigger fires the report has printed or in the case of a screen report, after the report is closed following viewing. This trigger can be used to update a global variable if u r returning the number of pages in a report. It is also used to delete temporary table used to print the report
After parameter form & Before Report: These two triggers are fired one after the other. No event occurs in between them. However the way the way that the reports product behaves when the triggers fail is quite different. If the After Parameter trigger fails the report will be put back into the parameter form. It’s useful to place code here to check whether values in your parameter form are valid. Even though the Before Report trigger is executed before the query runs, if this trigger fails it won’t fail until reports tries to display the first page of the report. This means that even if something goes wrong in the before report trigger (meaning that you may not want to run the query at all) It will run anyway
Report Level Triggers

Confine mode

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders “stretch” when child objects are moved against them.

Off: parent borders remain fixed when child objects are moved against them.

What is Page Break?

Ans: To limit the records per page.

19 What is Page Protector?

Ans: The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to Yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Ex: if you set yes, the object information print another page.

Print Condition

The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.

20 What is Print Direction?

Ans: The Print Direction property is the direction in which successive instances of the repeating frame appear.

21 What is Vertical and Horizental Elacity

Ans: The Horizontal Elasticity property is how the horizontal size of the object will change at runtime to accommodate the objects or data within it:

22.What is Place holder Columns?

Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it

Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.

Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

23 What is Formula Column?

Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

24 What is Summary columns?

Ans: A summary column performs a computation on another column’s data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

25 What is Boilerplate?

Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_

Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.

26 What is Data Link

When we join multiple quires in a report the join condition is stored in the data link section

Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link’s Property Palette) that will be added to the child query’s SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.

27 What is filter and Group Filter

28.What is Query Builder

Ans: it’s a gui tool to build a query in Report Wizard, Data Wizard or Data model.

29 What is Break Column?

Ans: We can break a column through data model , it Display once for a group

30.How do u call Report From form?

Ans: RUN_PRODUCT and RUN_REPORT_OBJECT

HOW CAN U CREATE TWO FORMATS

USING DISTRIBUTION WE CAN CREATE DIFFERENT FORMATS

45 HOW TO DISPLY ONE RECORD PER PAGE ( WHICH PROPERTY WE SHOULD SET)

Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.

In Data model Layout , Group Property Through Filter Type & No of records to display

Property, Values are First, last, pl/sql

What is Header ,Body, Trailer, and Footer in Reports

Header: The header consist of on e or more pages that are printed before report proper. The type of

Information you might want to print title of the page ,company logo and address or chart the

Summarizes the report.

Trailer: The trailer consists of one or more pages that print after the report itself, usually used for nothing more than an end of report blank page, but also used for a report summary or chart.

Body: The body is where all the main report objects are placed

Margin: the report layout only governs the part of the pages designated for the main data portion of the report. The margins are can be used to specify page headers and page footers.

what are Executable file definitions in Reports

Report Builder (RWBLD60.EXE)

n Reports Runtime (RWRUN60.EXE)

n Reports Convert (RWCON60.EXE)

n Reports Background Engine (RWRBE60.EXE)

n Reports Server (RWMTS60.EXE)

n Reports Web Cartridge (RWOWS60.DLL)

n Reports CGI (RWCGI60.EXE)

n Reports Queue Manager (RWRQM60.EXE)

n Reports Launcher (RWSXC60.EXE)

n Reports ActiveX Control (RWSXA60.OCX)

What are the Non_query fields?

Aggregated Information, Calculated information, A string Function

Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?

You can. If you highlight a bunch of objects and then right click and select “properties..”, Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select “Common properties” from the “Tools” menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.

How do I change the printed value of a field at runtime?

Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it’s objects value.

That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure.

The syntax is SRW.Set_Field_char (0,) and the output of the object that the current trigger is attached to will be replaced by .

There are also SRW.set_fileld_num and SRW.set_field_date for numeric or date fields. While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals

Report Bursting

The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting.

Additional Layout:

Additional layout created for to different format using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.

System Variables as Source Field In Layout Editor

Ans: Current date, Page Number, Panel number, Physical Page Number, Total Pages,

Total Panels, Total Physical Pages.

Link File : Is a special type of boilerplate, that doesn’t have to remain constant for each report run

The type of file contents, can be Text, Image, CGM, Oracle drawing format, or image URL

Source filename :the name of the file the u want link to the report Through import Image from

Posted in Other IMP | Leave a comment

Sample CUSTOM Library Code To Customize Applications (Doc ID 744065.1)

Posted on December 30, 2020 by santosh kumar adda oracleapps

In this Document

How to switch off the custom code at the run time?

References

Applies to:

Oracle Order Management – Version 11.5.10.0 to 12.2.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.

Purpose

This note provides examples of using CUSTOM Library to enhance the functioning of Oracle Application forms so that customers can meet their business needs and to understand the usage of custom library, please DO NOT try to attempt simulation since this may affect standard functionality.

Scope

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Details

Based on Fields

How to make ‘Customer PO’ a Mandatory field?

For example to have customer PO number as a mandatory field

if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘OEXOEORD’) then
app_item_property2.set_property(‘ORDER.CUST_PO_NUMBER’,REQUIRED, PROPERTY_ON);

end if;
end if;

Result:

How to restrict cases for Custom PO field?

For example restricting Lower case in Customer PO field

if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘OEXOEORD’) then
app_item_property2.set_property(‘ORDER.CUST_PO_NUMBER’,CASE_RESTRICTION, UPPERCASE);
end if;
end if;

Result:

How to change the background color for the field ‘Payment Terms’?

For example how to Change the background color to Fuchsia(r255g0b255)

if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘OEXOEORD’) then
app_item_property2.set_property(‘ORDER.TERMS’,BACKGROUND_COLOR,’r255g0b255′);
end if;
end if;

Result:

For setting background color as Cyan
Cyan – r0g255b255

Result:

Colors are created by combination of red, green, blue.
The color can be derived from http://www.geodyssey.com/galileo/namedcolors.html

How to change the prompt for a field?

For Changing the prompt of field Order Number on the Release Sales Order form

if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘WSHFRREL’ AND block_name = ‘RELEASE’) then
app_item_property2.set_property (‘RELEASE.ORDER_NUMBER’, prompt_text, ‘Sales Order Number’);
end if;
end if;

Result:

How to make a field non editable field but the values needs to be defaulted?

For disabling the field

if (event_name = ‘WHEN-NEW-ITEM-INSTANCE’) then
if (form_name = ‘OEXOEORD’ AND block_name = ‘ORDER’) then
COPY (‘Business World’, ‘ORDER.SOLD_TO’);
VALIDATE (item_scope);
app_item_property2.set_property (‘ORDER.SOLD_TO’, enabled, property_false);
end if;
end if;

The Customer Name field is grayed out but the value is passed by using Copy function.
Result:

Based on Tab

Hiding Tab

How to hide Lines Tab in Sales Order form?

my_tab_page_id TAB_PAGE;
begin
if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘OEXOEORD’) then
my_tab_page_id := FIND_TAB_PAGE(‘ORDER_REGIONS.LINE_ITEMS’);
SET_TAB_PAGE_PROPERTY(my_tab_page_id,VISIBLE,property_FALSE);
end if;
end if;

Result:

Renaming Tab

How to rename ‘Lines’ tab to XX Items tab?

Result:

Based on Button

How to hide Book Order Button?

For example to hide Book button on the Sales Order Form

if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘OEXOEORD’) then
app_item_property2.set_property(‘ORDER_CONTROL.BOOK_ORDER’,displayed, PROPERTY_false);
end if;
end if;

Result:

How to Rename Book Order Button?

if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘OEXOEORD’) then
app_item_property2.set_property(‘ORDER_CONTROL.BOOK_ORDER’,Label,’Please confirm’);
end if;
end if;

Result:

Based on Blocks

How to make a block read-only ?

if (event_name = ‘WHEN-NEW-FORM-INSTANCE’) then
if (form_name = ‘OEXOEORD’and block_name = ‘ORDER’) then
set_block_property(block_name, insert_allowed,property_false);
end if;
end if;

Result:

Validations

How to ensure user enters not more than 3 characters in ‘Customer PO’ field and exits the field ?

if (form_name = ‘OEXOEORD’) then
if LENGTH(name_in(‘ORDER.CUST_PO_NUMBER’))>3
and
GET_ITEM_PROPERTY(‘ORDER.CUST_PO_NUMBER’,UPDATE_COLUMN) = ‘TRUE’
then
V_REC_NUM := name_in(‘SYSTEM.CURSOR_RECORD’);
SET_RECORD_PROPERTY(V_REC_NUM,’INV_SUM_FOLDER’,STATUS,NEW_STATUS);
fnd_message.set_name(‘FND’,’PO Number must be <= 3 characters’);
fnd_message.Error;
RAISE FORM_TRIGGER_FAILURE;
end if;
end if;

Result:

Based on Users

How to prevent a particular user from entering an Odd quantity for a particular item?

b := fnd_profile.VALUE (‘user_id’);
if (b = ‘1008697’) then
if (event_name = ‘WHEN-VALIDATE-RECORD’) then
if (form_name = ‘OEXOEORD’ AND block_name = ‘LINE’) then
if (NAME_IN (‘LINE.ORDERED_ITEM_DSP’) = ‘AS54888’) then
if (MOD (NAME_IN (‘LINE.ORDERED_QUANTITY’), 2) = 0) then
fnd_message.set_string (‘Even quantities for ‘ ||NAME_IN(LINE.ORDERED_ITEM_DSP)|| ‘ not allowed’);
fnd_message.show ();
RAISE form_trigger_failure;
end if;
fnd_message.set_string(‘Entered quantity is Odd — so no problem’);
fnd_message.show();
end if;
end if;
end if;
end if;

Result:

On entering even number

on entering odd number. Navigation to next line allowed only on entering odd number

Zoom

How to enable Zoom for a particular form?

For example to open the Onhand Quantity from Item Description field in lines tab of Sales Order form

To enable the Zoom function

FUNCTION zoom_available
RETURN BOOLEAN
IS
form_name VARCHAR2 (30) := NAME_IN (‘system.current_form’);
block_name VARCHAR2 (30) := NAME_IN (‘system.cursor_block’);
BEGIN
if (form_name = ‘OEXOEORD’ AND block_name = ‘LINE’) then
RETURN TRUE;
else
RETURN FALSE;
end if;
END zoom_available;

Following code helps to Onhand Quantity Form and to pass the item name to Onhand Quantity from Sales Order Form and navigate to Item field while clicking the Zoom button.

procedure event(event_name varchar2) is
param_to_pass1 VARCHAR2 (255);
b varchar2(20);
begin
if (event_name = ‘ZOOM’) then
if (form_name = ‘OEXOEORD’ AND block_name = ‘LINE’) then
param_to_pass1 := NAME_IN (‘LINE.ORDERED_ITEM_DSP’);
fnd_function.EXECUTE (function_name => ‘INV_INVMATWB’,
open_flag => ‘Y’,
session_flag => ‘Y’,
other_params => ‘ITEMS=”‘ || param_to_pass1 || ‘”‘ );
end if;
end if;
if (event_name = ‘WHEN-NEW-RECORD-INSTANCE’) then
if (form_name = ‘INVMATWB’ AND block_name = ‘MATERIAL_QF’) then
b := fnd_profile.VALUE (‘user_name’);
fnd_message.set_string (NAME_IN (‘parameter.ITEMS’)||’is entered by user’ ||b);
fnd_message.show ();
GO_ITEM (‘MATERIAL_QF.ITEM’);
COPY (NAME_IN (‘parameter.ITEMS’), (‘MATERIAL_QF.ITEM’));
VALIDATE (item_scope);
END IF;
end event;

Result:
At header level zoom button is not active

At line level zoom button will be active

After entering an item, click the zoom button to open the Onhand Quantity Form, the form will ask for organization to be selected

Item name will be passed to Onhand Quantity Form, The message will be displayed mentioning the item and the user name passed by the user.

The onhand quantity form will be opened with item description and cursor will navigate to Item field in Onhand Quantity Form.

Special

How to enable a Special button?

Below is an example showing the menu button from sales order form

a menuitem;
Begin
a := FIND_MENU_ITEM (‘SPECIAL.SPECIAL15’);
if (event_name = ‘WHEN-NEW-BLOCK-INSTANCE’) then
if (form_name = ‘OEXOEORD’ AND block_name = ‘LINE’) then
app_special2.instantiate (‘SPECIAL15’, ‘Query Form’);
SET_MENU_ITEM_PROPERTY (a, displayed, property_true);
SET_MENU_ITEM_PROPERTY (a, enabled, property_true);
end if;
end if;
if (event_name = ‘SPECIAL15’) then
if (form_name = ‘INVIDITM’) then
fnd_function.EXECUTE (function_name => ‘INV_INVMATWB’,
open_flag => ‘Y’,
session_flag => ‘Y’ );
end if;
end if;

Result:

How to switch off the custom code at the run time?

This is similar to switching off the custom code using Help –> Diagnostics –> Custom –> Custom Off
Note: Switching off customization means that Codes written in custom library and for personalization are switched off. This does not switch off Custom triggers or Custom code written on the standard forms.

A sample code for switch off customization:

IF event_name = ‘WHEN-NEW-FORM-INSTANCE’ THEN
IF fnd_profile.value(‘USER_ID’) =1318 THEN
copy ( ‘OFF’ , ‘GLOBAL.APP_CUSTOM_MODE’ ) ;
ELSE
copy ( ‘NORMAL’ , ‘GLOBAL.APP_CUSTOM_MODE’ ) ;
END IF ;
END IF ;

Still Have Questions?
To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Support Order Management EBS Community.

Let us know your feedback!
We hope you find this information useful. Our desire is to provide the right information when you need it. Please let us know how we are doing. To provide feedback on this note:
1. Select the “Rate this document” link. Depending on how you are viewing the note, you will find the link on the:
a) bottom left when viewing the note in a separate window.
b) upper right when viewing the note in the My Oracle Support window.
2. In the resulting pop-up box, enter your comments and ratings for the document.
3. Select Send Rating.

References

NOTE:743389.1 – Order Management Testcase Repository Library
NOTE:744069.1 – Sample Testcase For Using Form Personalization In Oracle Applications
NOTE:438922.1 – Order Management Suite – Usage of Custom Library CUSTOM.pll

Posted in Custom.pll | Leave a comment

API to Create Misc. Receipt in Oracle EBS R12.1.3

Posted on December 30, 2020 by santosh kumar adda oracleapps

CREATE OR REPLACE PROCEDURE APPS.XXMISC_RECEIPT_API

–##############################################################################–

–# #–

–# Procedure Name : XXMISC_RECEIPT_API #–

–# #–

–# Description It will create Misc Recept in Vision Oracle Receivabel EBS R12 #–

–# 1. #–

–# 2. #–

–# 3. #–

–# 4. #–

–# 5. #–

–# #–

–# Initial Verion Created By Last_Update_Date #–

–# ————– ———- —————- #–

–# 1.0 Santosh 28-NOV-2014 #–

–# #–

–##############################################################################–

–DECLARE

l_return_status VARCHAR2(1);

l_msg_count NUMBER;

l_msg_data VARCHAR2(240);

l_cash_receipt_id NUMBER;

p_count number := 0;

l_receipt_number varchar(10);

BEGIN

–##############################################

— 1) Set the applications context

–##############################################

mo_global.init(‘AR’);

mo_global.set_policy_context(‘S’,‘204’);

fnd_global.apps_initialize(1011902, 50559, 222,0);

l_receipt_number := ‘1234567’;

AR_RECEIPT_API_PUB.CREATE_MISC

( p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_TRUE,

p_validation_level => FND_API.G_VALID_LEVEL_FULL,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

–*********RECEIPT AMOUNT***********

p_amount => 4560.00,

–*********RECEIPT DATE ***********

p_receipt_date => ’22-JUL-2011′,

–*********RECEIPT GL DATE ***********

p_gl_date => ’22-JUL-2011′,

–*********RECEIPT METHOD ***********

p_receipt_method_id => 1001,

–*********RECEIVABLE ACTIVITY***********

p_activity => ‘Interest Income’,

p_misc_receipt_id => l_cash_receipt_id ,

p_receipt_number => l_receipt_number);

–##############################################

— 3) Review the API output

–##############################################

dbms_output.put_line(‘Status ‘ || l_return_status);

dbms_output.put_line(‘Message count ‘ || l_msg_count);

dbms_output.put_line(‘Cash Receipt ID ‘ || l_cash_receipt_id );

if l_msg_count = 1 Then

dbms_output.put_line(‘l_msg_data ‘|| l_msg_data);

elsif l_msg_count > 1 Then

loop

p_count := p_count + 1;

l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);

if l_msg_data is NULL Then

exit;

end if;

dbms_output.put_line(‘Message ‘ || p_count ||‘. ‘||l_msg_data);

end loop;

end if;

END;

Posted in IMP Scripts , Procedure and Packages by santosh | Leave a comment

package to create Concurrent Program API’s Custom Package and procedure

Posted on December 30, 2020 by santosh kumar adda oracleapps

CREATE OR REPLACE PACKAGE APPS.XXXXALL_CONPRG_DETAIL

procedure xxcreate_cpexecuitable(

–===============================================

–Create Concurrent Program Executable FROM BACK END

–===============================================

v_executable varchar2,

v_application varchar2,

v_short_name varchar2,

v_execution_method varchar2,

v_execution_file_name varchar2);

—###################################

PROCEDURE xxdelete_cpexecuitable(

/*–===================================================

TO DELETE CONCURRENT PROGRAM EXECUTABLE FROM BACK END

–===================================================*/

v_executable_short_name varchar2,

v_application varchar2);

procedure XXCREATE_CONCURRENT_PROGRAM(

–===========================================

–Create Concurrent Program Define FROM BACK END

–===========================================

V_PROGRAME VARCHAR2,

V_APPLICATION VARCHAR2,

V_ENABLED VARCHAR2,

V_SHORT_NAME VARCHAR2,

V_EXECUTABLE_SHORT_NAME VARCHAR2,

V_EXECUTABLE_APPLICATION VARCHAR2,

V_STYLE VARCHAR2,

V_USE_IN_SRS VARCHAR2,

V_OUTPUT_TYPE VARCHAR2

);

procedure XXDELETE_CONCURRENT_PROGRAM(

v_program_short_name varchar2,v_application varchar2);

procedure XXCREATE_CON_PROG_PARAMETOR(

–===============================================

–Creating Parameters For Concurrent Program

–===============================================

V_program_short_name VARCHAR2,

V_application VARCHAR2,

V_sequence NUMBER,

V_parameter VARCHAR2,

V_description VARCHAR2,

V_enabled VARCHAR2,

V_value_set VARCHAR2,

V_display_size NUMBER,

V_description_size NUMBER,

V_concat_des_size NUMBER,

V_prompt VARCHAR2,

V_token VARCHAR2);

procedure XXDELETE_CON_PROG_PARAMETOR(

/* –===============================================

TO DELETE PARAMETER FROM BACK END

–===============================================*/

V_program_short_name VARCHAR2,

V_application VARCHAR2,

V_parameter VARCHAR2 );

procedure XXADD_CP_TO_REQST_GROUP(

/*–===============================================

— Add To The Request Group

–===============================================*/

V_program_short_name VARCHAR2,

V_prog_application VARCHAR2,

V_request_group VARCHAR2,

V_grp_appl VARCHAR2

);

procedure XXREMOV_CP_FROM_REQST_GROUP(

/*–===============================================

TO REMOVE PROGRAM FROM REQUEST GROUP

–===============================================*/

V_program_short_name VARCHAR2,

V_prg_appl VARCHAR2,

V_reqst_grp VARCHAR2,

V_grp_appl VARCHAR2);

PROCEDURE xxsubmit_xml_cp_4m_bkend (

v_template_appl_name VARCHAR2,

v_template_code VARCHAR2,

v_template_language VARCHAR2,

v_template_territory VARCHAR2,

v_output_format VARCHAR2,

v_application VARCHAR2,

v_program VARCHAR2,

v_description VARCHAR2,

v_start_time VARCHAR2,

v_sub_request BOOLEAN := FALSE,

v_argument1 VARCHAR2 DEFAULT CHR (0),

v_argument2 VARCHAR2 DEFAULT CHR (0),

v_argument3 VARCHAR2 DEFAULT CHR (0),

v_argument4 VARCHAR2 DEFAULT CHR (0),

v_argument5 VARCHAR2 DEFAULT CHR (0)

);

END;

—————————————————————-

——package body

—————————————————————-

CREATE OR REPLACE PACKAGE BODY APPS.XXXXALL_CONPRG_DETAIL

procedure xxcreate_cpexecuitable(

–===============================================

–Create Concurrent Program Executable FROM BACK END

–===============================================

v_executable varchar2,

v_application varchar2,

v_short_name varchar2,

v_execution_method varchar2,

v_execution_file_name varchar2)

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

commit;

fnd_program.EXECUTABLE(executable => v_executable , — ‘XXPRDBANKOVERDRAFT’ ,

application => v_application , — ‘PFC Records’,

short_name => v_short_name, — ‘XXPRDBANKOVERDRAFT’,

execution_method => v_execution_method, –‘Oracle Reports’,

execution_file_name => v_execution_file_name); — ‘XXPRDBANKOVERDRAFT’

commit;

end;

PROCEDURE xxdelete_cpexecuitable(

/*–===================================================

TO DELETE CONCURRENT PROGRAM EXECUTABLE FROM BACK END

–===================================================*/

v_executable_short_name varchar2,

v_application varchar2)

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

FND_PROGRAM.DELETE_EXECUTABLE(executable_short_name => v_executable_short_name, — ‘XXPRD_PTL_MREPT’,

application => v_application — ‘PFC Records’

);

commit;

end;

procedure XXCREATE_CONCURRENT_PROGRAM(

–===========================================

–Create Concurrent Program Define FROM BACK END

–===========================================

V_PROGRAME VARCHAR2,

V_APPLICATION VARCHAR2,

V_ENABLED VARCHAR2,

V_SHORT_NAME VARCHAR2,

V_EXECUTABLE_SHORT_NAME VARCHAR2,

V_EXECUTABLE_APPLICATION VARCHAR2,

V_STYLE VARCHAR2,

V_USE_IN_SRS VARCHAR2,

V_OUTPUT_TYPE VARCHAR2

)

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

fnd_program.register(program => V_PROGRAME, — ‘Auto Posting MIS Report’,

application => V_APPLICATION,— ‘PFC Records’,

enabled => V_ENABLED, — ‘Y’,

short_name => V_SHORT_NAME, –‘XXPRDAUTOPOSTMIS’,

executable_short_name => V_EXECUTABLE_SHORT_NAME, — ‘XXPRDAUTOPOSTMIS’,

executable_application => V_EXECUTABLE_APPLICATION, — ‘PFC Records’,

style => V_STYLE, — ‘A4’,

use_in_srs => V_USE_IN_SRS, — ‘Y’,

output_type => V_OUTPUT_TYPE –‘XML’

);

commit;

end;

procedure XXDELETE_CONCURRENT_PROGRAM(

v_program_short_name varchar2,v_application varchar2) is

/* –===============================================

TO DELETE CONCURRENT PROGRAM FROM BACK END

–===============================================*/

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

FND_PROGRAM.DELETE_PROGRAM(program_short_name => v_program_short_name, –‘XXPRD_PTL_MREPT’,

application => v_application — ‘PFC Records’

);

commit;

end;

procedure XXCREATE_CON_PROG_PARAMETOR(

–===============================================

–Creating Parameters For Concurrent Program

–===============================================

V_program_short_name VARCHAR2,

V_application VARCHAR2,

V_sequence NUMBER,

V_parameter VARCHAR2,

V_description VARCHAR2,

V_enabled VARCHAR2,

V_value_set VARCHAR2,

V_display_size NUMBER,

V_description_size NUMBER,

V_concat_des_size NUMBER,

V_prompt VARCHAR2,

V_token VARCHAR2)

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

FND_PROGRAM.parameter(

program_short_name => V_program_short_name, –‘XXC_AJ_ORDER1’,

application => V_application, — ‘Order Management’,

sequence => V_sequence, — 5,

parameter => V_parameter, — ‘P_Order_from’,

description => V_description, — ‘asdfghjklmnopqrstuvxyz123245’,

enabled => V_enabled, — ‘Y’,

value_set => V_value_set, — ‘XDO_15_DIGIT_NUMBER’,

display_size => V_display_size, — 25,

description_size => V_description_size, — 50,

concatenated_description_size => V_concat_des_size, — 30,

prompt => V_prompt, — ‘P_order_from’,

token => V_token –‘P_ORDER1’

);

commit;

end;

procedure XXDELETE_CON_PROG_PARAMETOR(

/* –===============================================

TO DELETE PARAMETER FROM BACK END

–===============================================*/

V_program_short_name VARCHAR2,

V_application VARCHAR2,

V_parameter VARCHAR2 )

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

FND_PROGRAM.DELETE_PARAMETER(

program_short_name => V_program_short_name, –‘XXC_AJ_ORDER’,

application =>V_application,— ‘Order Management’,

parameter =>V_parameter –‘P_Order_to’);

);

commit;

end;

procedure XXADD_CP_TO_REQST_GROUP(

/*–===============================================

— Add To The Request Group

–===============================================*/

V_program_short_name VARCHAR2,

V_prog_application VARCHAR2,

V_request_group VARCHAR2,

V_grp_appl VARCHAR2

)

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

fnd_program.ADD_TO_GROUP (program_short_name => V_program_short_name, — ‘XXPRDAUTOPOSTMIS’,

program_application => V_prog_application, — ‘PFC Records’,

request_group => V_request_group, — ‘GL Concurrent Program Group’,– ‘OM Concurrent Programs’,

group_application => V_grp_appl — ‘General Ledger’

);

commit;

end;

procedure XXREMOV_CP_FROM_REQST_GROUP(

/*–===============================================

TO REMOVE PROGRAM FROM REQUEST GROUP

–===============================================*/

V_program_short_name VARCHAR2,

V_prg_appl VARCHAR2,

V_reqst_grp VARCHAR2,

V_grp_appl VARCHAR2)

begin

FND_GLOBAL.APPS_INITIALIZE(user_id =>0,resp_id=>20434 ,resp_appl_id=>101); — userid,responcibility,application detail

fnd_program.REMOVE_FROM_GROUP(program_short_name => V_program_short_name, — ‘ORDER_DETAILS’,

program_application => V_prg_appl, –‘Order Management’,

request_group => V_reqst_grp, — ‘OM Concurrent Programs’,

group_application => V_grp_appl — ‘Order Management’

);

commit;

end;

PROCEDURE xxsubmit_xml_cp_4m_bkend (

v_template_appl_name VARCHAR2,

v_template_code VARCHAR2,

v_template_language VARCHAR2,

v_template_territory VARCHAR2,

v_output_format VARCHAR2,

v_application VARCHAR2,

v_program VARCHAR2,

v_description VARCHAR2,

v_start_time VARCHAR2,

v_sub_request BOOLEAN := FALSE,

v_argument1 VARCHAR2 DEFAULT CHR (0),

v_argument2 VARCHAR2 DEFAULT CHR (0),

v_argument3 VARCHAR2 DEFAULT CHR (0),

v_argument4 VARCHAR2 DEFAULT CHR (0),

v_argument5 VARCHAR2 DEFAULT CHR (0)

)

v_return BOOLEAN;

v_request NUMBER;

BEGIN

fnd_global.apps_initialize (user_id => 2836,

resp_id => 20434,

resp_appl_id => 101

);

— santosh userid,responcibility,application detail

v_return :=

fnd_request.add_layout (template_appl_name => v_template_appl_name,

template_code => v_template_code,

template_language => v_template_language,

template_territory => v_template_territory,

output_format => v_output_format

);

v_request :=

fnd_request.submit_request (application => v_application,

program => v_program,

description => v_description,

start_time => v_start_time,

sub_request => v_sub_request,

argument1 => v_argument1,

argument2 => v_argument2,

argument3 => v_argument3,

argument4 => v_argument4,

argument5 => v_argument5

);

DBMS_OUTPUT.put_line (‘Request Number’ || v_request);

END;

Posted in IMP Scripts , Procedure and Packages by santosh | Leave a comment

OFFICE Bank Statement Sample File Inserted notepad file for Cash Management Process upload

Posted on December 30, 2020 by santosh kumar adda oracleapps

OFFICE Bank Statement Sample File Inserted notepad file for Cash Management Process upload

	Simi on XML Tags (very important xml…
	VIMAL KUMAR IYER on XML Tags (very important xml…
	Jacob on XML Tags (very important xml…
	T.V.Surendra on XML Tags (very important xml…
	Ramana on XML Tags (very important xml…

santoshkumaradda

BI Publisher: How to create Multisheet in Single Excel file using Excel Template (Doc ID 1352000.1)

Applies to:

Goal

Solution

Here are the steps required to achieve that:

Bank API in Oracle apps R12 (Banks, Branch, Accounts) with Code and link details

How to set up and use Bank Statement Loader – 161872.1.doc

Terminology often used in Oracle Applications: Terminology often used in Oracle Applications

Oracle Reports FAQ’s

Oracle Reports FAQ’s

Oracle Reports FAQ’s

Confine mode

26 What is Data Link

27 What is filter and Group Filter

Sample CUSTOM Library Code To Customize Applications (Doc ID 744065.1)

Applies to:

Purpose

Scope

Details

Based on Fields

Based on Tab

Based on Button

Based on Blocks

Validations

Based on Users

Zoom

Special

How to switch off the custom code at the run time?

References

API to Create Misc. Receipt in Oracle EBS R12.1.3

package to create Concurrent Program API’s Custom Package and procedure

OFFICE Bank Statement Sample File Inserted notepad file for Cash Management Process upload

Green Chain Hyderabad R12 Cash mangement white paper

Categories

Archives

Blogroll

Recent Comments