Hi,
I am going to explain you about the customization of Account Generation Processes/Account generator workflows in various modules in Oracle Applications.
For all modules the basic profile option to run account generator workflow is "Account Generator:Run in Debug Mode". So Set the profile "Account Generator:Run in Debug Mode" to "Yes"
Account Payables:
Workflow Name: Project Supplier Invoice Account Generation Process
Internal Name: PAAPINVW
If the new invoice is created in the AP module (Oracle Payables à Invoice à Entry à Invoices) with the Project details. OR Invoice has interfaced from the other module like i-expense with Project details, we should definitely customize the ‘Project Supplier Invoice Account Generation Process (PAAPINVW.wft)’ workflow to create account in distributions.
What will happen if the workflow is not customized?
You will get the error saying “APP-SQLAP-97734: The system can not generate distribution lines…..”. Please find the screen shot of the error below.
Error comes whenever you click on distributions button.
Solution:
Need to customize the workflow PAAPINVW, create a custom process where in remove all dummy default account generator functions and create/assign your accounting structure using custom functions OR we can use standard functions as well.
Find the below image for the Original Process “Generate default account (DEFAULT_ACCOUNT_GENERATION)”
Steps to customize the workflow:
- Copy the process and create another as it is
- Remove all Dummy default account generator functions
- Say if you have the accounting structure i.e code combination with 5 segments.
You need to assign values 5 times i.e you need to create 5 functions to assign each value.
- You can directly assign each value using “Assign Value to Segment” function in the “Standard Flexfield Workflow (FNDFFWK)”. You usually get the Standard Flexfield Workflow () along with “Standard (WFSTD)” workflow whenever you open the PAAPINVW workflow.
- Assign values in Node attributes of the function “Assign Value to Segement”
- OR if you have a lookup to get the values of segments, then use the function “Segment Lookup Set value” to get the lookup values and then assign the value to segment. Use Node attributes here as well.
- Use the standard function “Validate Code Combination” after assigning all segment values
- Look at the screenshot below for the better understanding
How to set values in ‘Node Attributes’
Double click on each function OR right click – Properties
Click on Node attributes tab and assign the values. If you are going to get values from lookups get lookup name and the source (Expenditure Operating Unit) is the source in the screen shot. Use this lookup value while assigning the value.
Where Expenditure Operating Unit is the standard item attribute, we can use values of standard item attributes to link in lookup value sets.
Node Attributes:
Lookup:
*** You can create your custom activities depends upon the customization
After customizing the workflow assign the custom process in accounting flexfiled processes.
1. Go to General ledger responsibility à Ctrl+L à Account Generation Processes à Find your custom accounting flexfiled structure
Change the Process name to your custom process where the item type is ‘Project Supplier Invoice Account Generation’
2. Set the profile option “PA: Allow Override of PA Distributions in AP/PO” to ‘No’.
To override using the custom account generation process which we have created
Even after all customizations if you still gets the token error, please refer the following notes in Oracle Metalink for support.
R12: APXINWKB: APP-SQLAP-97734 Error Message Displayed At Invoice Distributions Generation Time: Distributions Cannot Be Generated For Line Because &TOKEN2 (Doc ID 850743.1)
Projects Supplier Invoice Account Generation Error: APP-SQLAP-97734: The System Cannot Generate Distributions (Doc ID 823787.1)
R12: APXINWKB: APP-SQLAP-97734 WHEN ENTERING INVOICE DISTRIBUTION (Doc ID 1360580.1)
i-Expenses:
Workflow Name: Project Expense Report Account Generator
Internal Name: PAAPWEBX
Follow the same steps as above for customizing the workflow. Change the process in the accounting flexfiled structure in GL.
This workflow is going to fire/trigger whenever the user select the project while raising the expense in i-expenses module. After click on next the workflow triggers and creates the new/custom code combination before submitting the expense report.
Note: After submitting the expense report ‘Expenses (APEXP)’ workflow is going to trigger.
Project, Expenditure Orgainzation and task are going to enable in JSP page whenever we set theOIE profile options.
Ex: OIE:Enable Project Allocations
OIE:Enable Project Expenditure Organization
Note: A profile option OIE: Enable Project Allocations if set to "Yes with account allocations", then the Division and Cost center are enabled in Account Allocations tab of Expense Allocations Page of iExpenses. But with this if we update the Division this is not getting updated in the Expense report tables.
How to Build Accouting structure or Code combination id for Non-Project Expense Reports ?
If the user is not selected the project while raising the expense report, this workflow will not launch/trigger.
Then how will the accounting structure/code combination creates in non project expense reports? With our triggering the accounting generator workflow?
This is different concept altogether. In non project expense reports the accounting structure/code combination is going to build using the standard package (AP_WEB_CUS_ACCTG_PKG) with our customizations.
This package has two main procedure for this customization. Those are
ap_web_cus_acctg_pkg.getiscustombuildonly
If this function returns 1, then your custom code in PL/SQL ap_web_cus_acctg_pkgbuildaccount will be called.
ap_web_cus_acctg_pkg.buildaccount
This is where you actually build the default account for non-project expense line.
getiscustombuildonly API this must return 1 if you want your custom logic to kick-off.
If this API does not return 1, then iExpenses will use the person level default accounts from HRMS.
Sample code you can use is (from apps2fusion). You can find the real time code below.
FUNCTION buildaccount(p_report_header_id IN NUMBER
,p_report_line_id IN NUMBER
,p_employee_id IN NUMBER
,p_cost_center IN VARCHAR2
,p_exp_type_parameter_id IN NUMBER
,p_segments IN ap_oie_kff_segments_t
,p_ccid IN NUMBER
,p_build_mode IN VARCHAR2
,p_new_segments OUT NOCOPY ap_oie_kff_segments_t
,p_new_ccid OUT NOCOPY NUMBER
,p_return_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
l_account_array fnd_flex_ext.segmentarray;
BEGIN
p_new_ccid := 240694; --do not hard code in realiy, this is just sample
l_account_array(1) := 'FOCUST';
l_account_array(2) := 'CONSULTANCY';
l_account_array(3) := 'R12';
l_account_array(4) := '165122';
l_account_array(5) := '0';
l_account_array(6) := '0';
l_account_array(7) := '0';
p_new_segments := ap_oie_kff_segments_t('');
p_new_segments.EXTEND(l_account_array.COUNT);
FOR n_ctr IN 1 .. l_account_array.COUNT LOOP
p_new_segments(n_ctr) := l_account_array(n_ctr);
END LOOP;
RETURN TRUE;
END buildaccount;
Real time code:
Copy from here and paste it in your text pads.
FUNCTION GetIsCustomBuildOnly RETURN NUMBER
IS
BEGIN
-- if you want to enable custom builds
return 1;
-- if you do not want to enable custom builds
--return 0;
END;
FUNCTION BuildAccount(
p_report_header_id IN NUMBER,
p_report_line_id IN NUMBER,
p_employee_id IN NUMBER,
p_cost_center IN VARCHAR2,
p_exp_type_parameter_id IN NUMBER,
p_segments IN AP_OIE_KFF_SEGMENTS_T,
p_ccid IN NUMBER,
p_build_mode IN VARCHAR2,
p_new_segments OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
p_new_ccid OUT NOCOPY NUMBER,
p_return_error_message OUT NOCOPY VARCHAR2) RETURNBOOLEAN
IS
l_chart_of_accounts_id NUMBER := 0;
l_flex_segment_delimiter VARCHAR2(2) := '';
l_concatenated_segments varchar2(2000);
l_exp_line_acct_segs_array FND_FLEX_EXT.SEGMENTARRAY;
l_default_emp_segments FND_FLEX_EXT.SEGMENTARRAY;
l_FlexConcactenated AP_EXPENSE_REPORT_PARAMS.FLEX_CONCACTENATED%TYPE;
l_exp_type_template_array FND_FLEX_EXT.SEGMENTARRAY;
l_num_segments NUMBER:=NULL;
l_default_emp_ccid AP_WEB_DB_EXPRPT_PKG.expHdr_employeeCCID;
l_segment4 VARCHAR2(25) := NULL;
lc_flex_conc VARCHAR2(1000) := NULL;
lc_seg VARCHAR2(25) := NULL;
l_segment1 VARCHAR2(25) := NULL;
l_company_segment VARCHAR2(25) := NULL;
BEGIN
p_new_segments := AP_OIE_KFF_SEGMENTS_T('');
p_new_segments.extend(7);
-- Get Employee default CCID
IF (NOT AP_WEB_DB_EXPRPT_PKG.GetDefaultEmpCCID(
p_employee_id => p_employee_id,
p_default_emp_ccid => l_default_emp_ccid)) THEN
NULL;
END IF;
IF (l_default_emp_ccid is null) THEN
FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_MISSING_EMP_CCID');
RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
END IF;
--Get Chart of Accounts Id
IF (NOT AP_WEB_DB_EXPRPT_PKG.GetChartOfAccountsID(
p_employee_id => p_employee_id,
p_chart_of_accounts_id => l_chart_of_accounts_id)) THEN
NULL;
END IF;
--Get default employee segments
IF (l_default_emp_ccid IS NOT NULL) THEN
IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
'SQLGL',
'GL#',
l_chart_of_accounts_id,
l_default_emp_ccid,
l_num_segments,
l_default_emp_segments)) THEN
RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
NULL;
END IF; /* GET_SEGMENTS */
END IF;
IF (l_chart_of_accounts_id is null) THEN
FND_MESSAGE.Set_Name('SQLAP', 'OIE_MISS_CHART_OF_ACC_ID');
RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
END IF;
-- Get segment delimiter
l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
'SQLGL',
'GL#',
l_chart_of_accounts_id);
IF (l_flex_segment_delimiter IS NULL) THEN
FND_MSG_PUB.Add;
RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
END IF;
-- New code starts here
if (p_ccid is not null) then
IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL',
'GL#',
l_chart_of_accounts_id,
p_ccid,
l_num_segments,
l_exp_line_acct_segs_array)) THEN
-- return;
Null;
END IF;
elsif (p_segments is not null and p_segments.count > 0) then
IF (l_num_segments IS NULL) THEN
l_num_segments := p_segments.count;
END IF;
FOR i IN 1..l_num_segments LOOP
l_exp_line_acct_segs_array(i) := p_segments(i);
END LOOP;
end if /* p_ccid is not null or p_segments is not null */;
-----------------------------------------------------------------------------
if (p_exp_type_parameter_id is not null) then
IF (AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated(
p_parameter_id => p_exp_type_parameter_id,
p_FlexConcactenated => l_FlexConcactenated)) THEN
lc_flex_conc := TO_CHAR(l_FlexConcactenated);
-- Getting Account segment from the concactenated expense report template accounting structure
SELECT SUBSTR(lc_flex_conc,(INSTR(lc_flex_conc,l_flex_segment_delimiter,1,3)+1),(instr(lc_flex_conc,l_flex_segment_delimiter,1,4))-((instr(lc_flex_conc,l_flex_segment_delimiter,1,3)+1)))
INTO lc_seg
FROM DUAL;
SELECT SUBSTR(lc_flex_conc,1,(instr(lc_flex_conc,l_flex_segment_delimiter,1,1)-1))
INTO l_company_segment FROM DUAL;
-- l_segment4 := SUBSTR(l_FlexConcactenated,(INSTR(l_FlexConcactenated,l_flex_segment_delimiter,1,3)+1),(instr(l_FlexConcactenated,'-',1,4))-((instr(l_FlexConcactenated,'-',1,3)+1)));
-- INSERT INTO temp_rajesh VALUES ('l_segment4',l_segment4);
/* IF l_FlexConcactenated is not null THEN
l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated, l_flex_segment_delimiter, l_exp_type_template_array);
END IF; */
NULL;
END IF;
end if; /* p_exp_type_parameter_id is not null */
-----------------------------------------------------------------------------
-- Overlay the incoming segment values with the segment values
-- defined in expense type template IF the incoming segment value
-- is NULL.
/* FOR i IN 1..l_num_segments LOOP
-- If the incoming segment is not null, then keep this value, do nothing.
IF (p_segments IS NOT NULL AND
p_segments.EXISTS(i) AND
p_segments(i) IS NOT NULL) THEN
NULL;
ELSIF (l_exp_type_template_array is not null and
l_exp_type_template_array.count > 0 and
l_exp_type_template_array(i) IS NOT NULL) THEN
l_exp_line_acct_segs_array(i) := l_exp_type_template_array(i);
ELSE
INSERT INTO temp_rajesh VALUES ('ELSE-Check it',NULL);
END IF; -- l_exp_type_template_array(i) IS NOT NULL
END LOOP; -- 1..l_num_segments
*/
/* IMPORTANT NOTE:
Below values are hard code, coz workflow is failing in validation when we capture the same from select statement
This will work for all existing CTC elements
For other CTC elements the account will default to 420011 (which is wrong)
For new CTC elements add some more IF conditions like below */
IF l_company_segment = 'SSL' THEN
p_new_segments(1) := 'SSL';
ELSIF l_company_segment = 'SEL' THEN
p_new_segments(1) := 'SEL';
ELSIF l_company_segment = 'SSLDubai' THEN
p_new_segments(1) := 'SSLDubai';
ELSIF l_company_segment = 'TUI' THEN
p_new_segments(1) := 'TUI';
ELSIF l_company_segment = 'SITL' THEN
p_new_segments(1) := 'SITL';
ELSIF l_company_segment = 'SSLUS' THEN
p_new_segments(1) := 'SSLUS';
ELSIF l_company_segment = 'SSLUK' THEN
p_new_segments(1) := 'SSLUK';
ELSIF l_company_segment = 'SSGmbH' THEN
p_new_segments(1) := 'SSGmbH';
ELSIF l_company_segment = 'ODSi' THEN
p_new_segments(1) := 'ODSi';
ELSIF l_company_segment = 'SSLSING' THEN
p_new_segments(1) := 'SSLSING';
ELSE
p_new_segments(1) := 'SSL'; -- If the company not exists then use default value as SSL
END IF;
--p_new_segments(1) := l_default_emp_segments(1); --'SSL'; --p_segments(1); --'SSL';
p_new_segments(2) := l_default_emp_segments(2); --p_segments(2); --'000000';
p_new_segments(3) := l_default_emp_segments(3);--'PGERP'; --p_segments(3); --'PGERP';
IF lc_seg = '423002' THEN -- Medical Allowance
p_new_segments(4) := '423002';
ELSIF lc_seg = '420016' THEN -- Education Allowance
p_new_segments(4) := '420016';
ELSIF lc_seg = '441007' THEN -- Fuel Allowance
p_new_segments(4) := '441007';
ELSIF lc_seg = '423001' THEN -- LTA Allowance
p_new_segments(4) := '423001';
ELSIF lc_seg = '441006' THEN -- Car Allowance
p_new_segments(4) := '441006';
ELSIF lc_seg = '420011' THEN
p_new_segments(4) := '420011'; -- Local Deputation Allowance
ELSE
p_new_segments(4) := '423002';--NULL; -- Do not generate accounting combinate
END IF;
--p_new_segments(4) := l_segment4;--l_exp_line_acct_segs_array(4);--l_exp_type_template_array(4); --'423002'; --p_segments(4); --'
p_new_segments(5) := l_default_emp_segments(5); --'000000'; --p_segments(5); --'000000';
p_new_segments(6) := l_default_emp_segments(6); --'000'; --p_segments(6); --'000';
p_new_segments(7) := l_default_emp_segments(7); --'000000'; --p_segments(7); --'000000';
l_concatenated_segments := p_new_segments(1)||l_flex_segment_delimiter ||p_new_segments(2)||l_flex_segment_delimiter ||p_new_segments(3)||l_flex_segment_delimiter ||p_new_segments(4)||l_flex_segment_delimiter ||p_new_segments(5)||l_flex_segment_delimiter ||p_new_segments(6)||l_flex_segment_delimiter
||p_new_segments(7);
IF (FND_FLEX_KEYVAL.validate_segs('CREATE_COMBINATION',
'SQLGL',
'GL#',
l_chart_of_accounts_id,
l_concatenated_segments)) THEN --'SSL-BG1-PGERP-423002-000000-000-000000')) THEN
p_new_ccid := FND_FLEX_KEYVAL.combination_id;
ELSE
p_return_error_message := FND_FLEX_KEYVAL.error_message;
FND_MESSAGE.set_encoded(FND_FLEX_KEYVAL.encoded_error_message);
fnd_msg_pub.add();
END IF;
return TRUE;
--return FALSE;
END BuildAccount;
Thanks,
Rajesh
