Oracle Application: July 2018

Sunday, 8 July 2018

Oracle GL account Code Query Based on Security Profile

SELECT c.flex_value, ffvt.DESCRIPTION
FROM fnd_flex_value_rule_usages a,
fnd_flex_value_rule_lines b,
fnd_flex_values c,
fnd_flex_value_sets ffvs,
fnd_flex_values_tl ffvt
WHERE a.flex_value_rule_id = b.flex_value_rule_id
AND a.flex_value_set_id = b.flex_value_set_id
AND b.flex_value_set_id = c.flex_value_set_id
AND c.flex_value BETWEEN b.flex_value_low AND b.flex_value_high
AND b.include_exclude_indicator = 'I'
AND c.summary_flag <> 'Y'
AND c.ENABLED_FLAG = 'Y'
AND c.END_DATE_ACTIVE IS NULL
AND TO_CHAR (a.flex_value_set_id) =
(select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name LIKE 'AG_ACCOUNT') --valuelistof(nq_session.gl_sec_account_valuesets____ebs)
AND ffvs.flex_value_set_id = c.flex_value_set_id
AND c.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV ('LANG')
AND flex_value_set_name LIKE 'AG_ACCOUNT' --- Add your Flexfield Valueset name of Company for that segment
AND TO_CHAR (a.responsibility_id) = fnd_profile.VALUE ('RESP_ID') --'52489' --valuelistof(nq_session.gl_sec_ebs_resp_id)
AND a.application_id = fnd_profile.VALUE ('RESP_APPL_ID')

UNION

SELECT
ffv.flex_value ,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_ACCOUNT' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
AND ffv.end_date_active is null
AND 0=(select count(1) from fnd_flex_value_rule_usages a,fnd_flex_value_rule_lines b
where
a.FLEX_VALUE_RULE_ID=b.FLEX_VALUE_RULE_ID
and a.flex_value_set_id=b.flex_value_set_id
and a.flex_value_set_id =(select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name LIKE 'AG_ACCOUNT')
AND TO_CHAR (a.responsibility_id) =fnd_profile.VALUE ('RESP_ID')
AND a.application_id = fnd_profile.VALUE ('RESP_APPL_ID'))
;

Oracle Gl Balancing Segment Query based on Organization Profile

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt,
gl_ledgers gl,
hr_operating_units hou,
gl_code_combinations_kfv gcck
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Circle/Branch' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
AND ffv.flex_value=gcck.segment1
AND gl.ledger_id = hou.set_of_books_id
AND gl.RET_EARN_CODE_COMBINATION_ID =
gcck.CODE_COMBINATION_ID
and hou.organization_id=fnd_profile.VALUE('ORG_ID')

Order by ffv.flex_value
;

Oracle GL segment 1-8 LOV Query

-----Segment1- Value Set For GL Account-----

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt,
gl_ledgers gl,
hr_operating_units hou,
gl_code_combinations_kfv gcck
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Circle/Branch' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
AND ffv.flex_value=gcck.segment1
AND gl.ledger_id = hou.set_of_books_id
AND gl.RET_EARN_CODE_COMBINATION_ID =
gcck.CODE_COMBINATION_ID
and hou.organization_id=fnd_profile.VALUE('ORG_ID')

Order by ffv.flex_value
;

--------segment2-----------

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Major/Minor Program'
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;

--------segment3-----------

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Circle/Branch/Dep/Section/Unit' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;

--------segment4-----Dynamic based on Security Profile------

SELECT c.flex_value, ffvt.DESCRIPTION
FROM fnd_flex_value_rule_usages a,
fnd_flex_value_rule_lines b,
fnd_flex_values c,
fnd_flex_value_sets ffvs,
fnd_flex_values_tl ffvt
WHERE a.flex_value_rule_id = b.flex_value_rule_id
AND a.flex_value_set_id = b.flex_value_set_id
AND b.flex_value_set_id = c.flex_value_set_id
AND c.flex_value BETWEEN b.flex_value_low AND b.flex_value_high
AND b.include_exclude_indicator = 'I'
AND c.summary_flag <> 'Y'
AND c.ENABLED_FLAG = 'Y'
AND c.END_DATE_ACTIVE IS NULL
AND TO_CHAR (a.flex_value_set_id) =
(select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name LIKE 'AG_ACCOUNT') --valuelistof(nq_session.gl_sec_account_valuesets____ebs)
AND ffvs.flex_value_set_id = c.flex_value_set_id
AND c.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV ('LANG')
AND flex_value_set_name LIKE 'AG_ACCOUNT' --- Add your Flexfield Valueset name of Company for that segment
AND TO_CHAR (a.responsibility_id) = fnd_profile.VALUE ('RESP_ID') --'52489' --valuelistof(nq_session.gl_sec_ebs_resp_id)
AND a.application_id = fnd_profile.VALUE ('RESP_APPL_ID')

UNION

SELECT
ffv.flex_value ,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_ACCOUNT' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
AND ffv.end_date_active is null
AND 0=(select count(1) from fnd_flex_value_rule_usages a,fnd_flex_value_rule_lines b
where
a.FLEX_VALUE_RULE_ID=b.FLEX_VALUE_RULE_ID
and a.flex_value_set_id=b.flex_value_set_id
and a.flex_value_set_id =(select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name LIKE 'AG_ACCOUNT')
AND TO_CHAR (a.responsibility_id) =fnd_profile.VALUE ('RESP_ID')
AND a.application_id = fnd_profile.VALUE ('RESP_APPL_ID'))
;

--------segment5-----------

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Circle/Branch' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;

--------segment6-----------

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Future 1' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;

--------segment7-----------

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Future 2' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;

--------segment8-----------

SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Future 3' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;

Oracle Tax Rate Code Query

SELECT DISTINCT tax_rate_code
FROM ZX_RATES_B a, ZX_REGIMES_B b
WHERE a.tax_regime_code = b.tax_regime_code
AND ( a.effective_to IS NULL
OR TRUNC (a.effective_to) >= TRUNC (SYSDATE))
AND ( b.effective_to IS NULL
OR TRUNC (b.effective_to) >= TRUNC (SYSDATE));

Oracle Supplier Based On Organization Profile Query

SELECT DISTINCT a.Vendor_name,
a.segment1,
a.vendor_id,
b.vendor_site_id,
b.vendor_site_code,
c.name
FROM ap_suppliers a, ap_supplier_sites_all b, hr_operating_units c
WHERE a.vendor_id = b.vendor_id AND b.org_id = c.organization_id
AND c.organization_id=fnd_profile.VALUE('ORG_ID');

Oracle Web ADI Defaulting Flagged Row Issue

Web ADI Defaulting Flagged Row Issue

Requirement: Sometimes the client wants to default selection of flagged rows option, instead of select all rows.

Navigation: Application Developer Responsibility > Application > Function

Query Custom Function which you define for web ADI

Goto Form Tab

Parameter: bne:page=BneCreateDoc&bne:integrator= XXAPINV6_XINTG&bne:rows=FLAGGED

Add defaulting parameter like shown above.

Oracle Web ADI Invalid Integrator Key Issue

Web ADI Invalid Integrator Key Issue

Following Error Comes When User try to open Web ADI from Different EBS User Login.

Solution:

Navigation : Desktop Integrator Manager > Select Integrator > Goto Integrator Tab

Step 1: Select Check Box: Display in Create document Page

Step 2: Add Function under Security Rules

1) BNE_ADI_CREATE_DOCUMENT (Many Time Required to add ‘BNE_CREATE_DOCUMENT’)

2) XXAPINVFN1 (Function which is used for assign Web ADI to Specific responsibility)

Oracle Retrieve Application Password for specific User Query

Solution 1:

SELECT usr.user_name,

get_pwd.decrypt

((SELECT (SELECT get_pwd.decrypt

(fnd_web_sec.get_guest_username_pwd,

usertable.encrypted_foundation_password

)

FROM DUAL) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name =

(SELECT SUBSTR

(fnd_web_sec.get_guest_username_pwd,

1,

INSTR

(fnd_web_sec.get_guest_username_pwd,

'/'

)

- 1

)

FROM DUAL)),

usr.encrypted_user_password

) PASSWORD

FROM fnd_user usr

WHERE usr.user_name = 'FIN_ADMIN2';

Solution 2:

Package Specification:
CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;

END get_pwd;

Package Body:

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

END get_pwd;

Query to execute:

SELECT usr.user_name,
get_pwd.decrypt
((SELECT (SELECT get_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = '&USER_NAME';

Oracle Workflow Compilation Steps

-----Workflow Compilation Command----

Step 1: Upload Workflow file respective Top for customized component it will be custome top.
-----------------------
Seeded Workflow Files Location
You can also download seeded workflow definition files directly from below directory-

$<Application_TOP>/patch/115/import/<LANG>

For example- Account Payables workflow file can be found at-
$AP_TOP/patch/115/import/US

Step 2: Set Environment
------------------------
cd /appl/atulorc/apps/apps_st/appl/

ls *.env

. APPSatuldb_testof.env

Step 3: Based on requirement we have to upgrade/upload and force compilation command used.
-------------------------------------------------------------------------------------------
WFLOAD Command to upload Oracle Workflow

To upgrade-- WFLOAD apps/pwd 0 Y UPGRADE file.wft

To upload-- WFLOAD apps/pwd 0 Y UPLOAD file.wft

To force-- WFLOAD apps/pwd 0 Y FORCE file.wft

To download-- WFLOAD apps/pwd 0 Y DOWNLOAD file.wft ITEMTYPE1 [ITEMTYPE2 ...ITEMTYPEN]

For Example-
WFLOAD apps/passed123@DEVL 0 Y UPLOAD APEXP_TEMP.wft

Oracle Form Compilation Steps

Step 1:Goto APPL Directory.

cd /appl/atulorc/apps/apps_st/appl/

Step 2: Set Environment Variable.

ls *.env

. APPSatuldb_testof.env

Setp 3: Goto AU_TOP

cd /appl/atulorc/apps/apps_st/appl/au/12.0.0/forms/US

Upload form on following path

/appl/atulorc/apps/apps_st/appl/au/12.0.0/forms/US

upload .fmb here

Step 4: Execute below command

Compilation Command;

frmcmp_batch $AU_TOP/forms/US/XX_VENDOR.fmb Userid=apps/welcome output_file=$XXCUS_TOP/forms/US/XX_VENDOR.fmx

Oracle Web ADI Form Error: Desktop Integration Manager is not a valid responsibility for the current user. Please contact your System Administrator.

Error: Desktop Integration Manager is not a valid responsibility for the current user. Please contact your System Administrator.

Online Link: http://mandeeporacle.blogspot.com/2014/05/xxx-is-not-valid-responsibility-for.html

This is happening because middle tiers has yet to pick the change in assigned responsibility. To resolve this issue we need to clear the middle tier cache.

Step 1:Navigate to Functional administrator>Core Service >Caching Framework > Global Configuration

Step 2: Click on Clear All Cache

Step 3: Try It again.

Oracle Receivable Active Memo Line Query

Background:

Run the following SQL Query to get all active AR Memo Lines.

Navigation: Receivables Manager > Setup > Transactions > Memo Lines

=> Set the Org context first.
begin
mo_global.set_policy_context('S',<org_id>);
end;
/

Script:

SELECT hou.name operating_unit,
amlv.name,
amlv.description,
amlv.line_type type,
amlv.tax_code tax_classification,
amlv.uom_code unit_of_measure,
amlv.unit_std_price unit_list_price,
gcck.concatenated_segments revenue_account,
amlv.start_date,
amlv.end_date
FROM ar_memo_lines_vl amlv,
gl_code_combinations_kfv gcck,
hr_operating_units hou
WHERE end_date IS NULL
AND hou.organization_id = amlv.org_id
AND amlv.GL_ID_REV = gcck.CODE_COMBINATION_ID;

Oracle Convert Number to Word Function In RDF

CREATE OR REPLACE FUNCTION XX_CONVERT_NUM_TO_WORDS (p_numeral NUMBER)
RETURN VARCHAR2
IS
-- Function to convert the number to words
c_zero ap_lookup_codes.displayed_field%TYPE;
c_thousand ap_lookup_codes.displayed_field%TYPE;
c_million ap_lookup_codes.displayed_field%TYPE;
c_billion ap_lookup_codes.displayed_field%TYPE;
number_too_large EXCEPTION;
numeral NUMBER := ROUND(ABS (p_numeral),2);
max_digit INTEGER := 12; -- for numbers less than a trillion
number_text VARCHAR2 (1000) := '';
billion_seg VARCHAR2 (25);
million_seg VARCHAR2 (25);
thousand_seg VARCHAR2 (25);
units_seg VARCHAR2 (25);
billion_lookup VARCHAR2 (80);
million_lookup VARCHAR2 (80);
thousand_lookup VARCHAR2 (80);
units_lookup VARCHAR2 (80);
session_language fnd_languages.nls_language%TYPE;
thousand NUMBER := POWER (10, 3);
million NUMBER := POWER (10, 6);
billion NUMBER := POWER (10, 9);
dec_seg VARCHAR2 (25); -- For decimals
dec_lookup VARCHAR2 (80);
BEGIN
IF numeral >= POWER (10, max_digit)
THEN
RAISE number_too_large;
END IF;

IF numeral = 0
THEN
SELECT ' ' || displayed_field || ' '
INTO c_zero
FROM ap_lookup_codes
WHERE lookup_code = 'ZERO';

RETURN (c_zero);
END IF;

billion_seg := TO_CHAR (TRUNC (numeral / billion));
numeral := numeral - (TRUNC (numeral / billion) * billion);
million_seg := TO_CHAR (TRUNC (numeral / million));
numeral := numeral - (TRUNC (numeral / million) * million);
thousand_seg := TO_CHAR (TRUNC (numeral / thousand));
units_seg := TO_CHAR (TRUNC (MOD (numeral, thousand)));
numeral := numeral - ((TO_NUMBER (thousand_seg) * thousand) + TRUNC (MOD (numeral, thousand)));

SELECT ' ' || lc1.displayed_field || ' '
, ' ' || lc2.displayed_field || ' '
, ' ' || lc3.displayed_field || ' '
, ' ' || lc4.displayed_field
, lc5.description
, lc6.description
, lc7.description
, lc8.description
INTO c_billion
, c_million
, c_thousand
, c_zero
, billion_lookup
, million_lookup
, thousand_lookup
, units_lookup
FROM ap_lookup_codes lc1,
ap_lookup_codes lc2,
ap_lookup_codes lc3,
ap_lookup_codes lc4,
ap_lookup_codes lc5,
ap_lookup_codes lc6,
ap_lookup_codes lc7,
ap_lookup_codes lc8
WHERE lc1.lookup_code = 'BILLION'
AND lc1.lookup_type = 'NLS TRANSLATION'
AND lc2.lookup_code = 'MILLION'
AND lc2.lookup_type = 'NLS TRANSLATION'
AND lc3.lookup_code = 'THOUSAND'
AND lc3.lookup_type = 'NLS TRANSLATION'
AND lc4.lookup_code = 'ZERO'
AND lc4.lookup_type = 'NLS TRANSLATION'
AND lc5.lookup_code = billion_seg
AND lc5.lookup_type = 'NUMBERS'
AND lc6.lookup_code = million_seg
AND lc6.lookup_type = 'NUMBERS'
AND lc7.lookup_code = thousand_seg
AND lc7.lookup_type = 'NUMBERS'
AND lc8.lookup_code = units_seg
AND lc8.lookup_type = 'NUMBERS';

SELECT SUBSTR (USERENV ('LANGUAGE'), 1, INSTR (USERENV ('LANGUAGE'), '_') - 1)
INTO session_language
FROM DUAL;

IF (session_language = 'FRENCH' OR session_language = 'CANADIAN FRENCH') AND thousand_seg = '1'
THEN
thousand_lookup := NULL;
END IF;

IF billion_seg <> '0'
THEN
number_text := number_text || billion_lookup || c_billion;
IF (session_language <> 'AMERICAN') and (million_seg <> '0')
THEN
number_text := number_text || ' و ';
END IF;
END IF;

IF million_seg <> '0'
THEN
-- Added on 26-mar-09
IF (session_language <> 'AMERICAN')
THEN
IF (million_seg = '1')
THEN
number_text := number_text || ' ' || 'مليون';
ELSIF (million_seg = '2')
THEN
number_text := number_text || ' ' || 'مليونان';
ELSIF (TO_NUMBER (million_seg) BETWEEN 3 AND 10)
THEN
number_text := number_text || million_lookup || ' ' ||'مليون';
ELSE -- above 10
number_text := number_text || million_lookup || ' ' || 'مليون';
END IF;

IF (session_language <> 'AMERICAN') and (thousand_seg <> '0') then
number_text := number_text || ' و ';
END IF;

ELSE
number_text := number_text || million_lookup || c_million;
END IF;
END IF;

IF thousand_seg <> '0'
THEN
-- Added on 26-mar-09
IF (session_language <> 'AMERICAN')
THEN

IF (thousand_seg = '1')
THEN
number_text := number_text || ' ' || 'الف';
ELSIF (thousand_seg = '2')
THEN
number_text := number_text || ' ' || 'الفي';
ELSIF (TO_NUMBER (thousand_seg) BETWEEN 3 AND 10)
THEN
number_text := number_text || thousand_lookup || ' ' || 'الاف';
ELSE -- above 10
number_text := number_text || thousand_lookup || ' ' || 'الف';
END IF;

IF (units_seg <> '0') and (session_language <> 'AMERICAN') then
number_text := number_text || ' و ';
END IF;

ELSE
number_text := number_text || thousand_lookup || c_thousand;
END IF;
END IF;

IF units_seg <> '0'
THEN
number_text := number_text||units_lookup;
END IF;

IF (session_language <> 'AMERICAN')
THEN
IF (billion_seg <> '0') OR (million_seg <> '0') OR (thousand_seg <> '0') OR (units_seg <> '0')
THEN
number_text := number_text || ' ' || 'درهم';
END IF;
ELSE
number_text := number_text || ' ' || 'Dirhams';
END IF;

-- For decimal points
IF numeral > 0
THEN
dec_seg := TO_CHAR (numeral * 100);

SELECT lc9.description
INTO dec_lookup
FROM ap_lookup_codes lc9
WHERE lc9.lookup_code = dec_seg AND lc9.lookup_type = 'NUMBERS';
/*elsif numeral=0 then
select lc9.description
into dec_lookup
from ap_lookup_codes lc9
where lc9.lookup_code = '0'
and lc9.lookup_type = 'NUMBERS';*/ -- commented on 26-mar-09
ELSE
dec_lookup := '';
END IF;

IF dec_lookup IS NOT NULL
THEN -- added if 26-mar-09
IF (session_language <> 'AMERICAN')
THEN
number_text := number_text || ' و ' || dec_lookup || ' فلس';
ELSE
number_text := number_text || ' And ' || dec_lookup || ' Fils';
END IF;
ELSE
number_text := number_text;
END IF;

number_text := LTRIM (number_text);
number_text := UPPER (SUBSTR (number_text, 1, 1)) || RTRIM (SUBSTR (number_text, 2, LENGTH (number_text)));
RETURN (number_text);
EXCEPTION
WHEN number_too_large
THEN
RETURN (SQLERRM);
WHEN OTHERS
THEN
RETURN (SQLERRM);
END;

/

Oracle Report Builder Library Error: REP-0756 Warning Unable to find PLSQL library Solution

/*----------------------------------------------------------------
Error 1: REP-0756 Warning Unable to find PLSQL library 'fadolif'.

Error 2: REP-0756 Warning Unable to find PLSQL library 'inv'.
----------------------------------------------------------------*/

step 1: Check on Report Builder Setup Path:

C:\DevSuiteHome_2\reports\templates

Error Message PLL file available or not.

Step 2: If Error File not available than download specific required .PLL file from following FTP server path:

FTP Path: AU_TOP pl/sql

/u02/applvat/vat/apps/apps_st/appl/au/12.0.0/plsql

Then I transferred that file to C:\DevSuiteHome_2\reports\templates

It's Work Fine

Oracle Web ADI Integrator - Layout - Mapping - Content Query

-----------Integrtor-----------
select integrator_code, application_short_name
from apps.bne_integrators_vl bnv
,apps.fnd_application_vl fav
where bnv.user_name like 'XX Patty Cash AP Invoice%'
and fav.application_id = bnv.application_id;

---------layout------------
select layout_code,user_name
from apps.bne_layouts_vl
where user_name like 'XX Patty Cash AP Invoice%'
; -- XXPATTYCASHAPINVOICELAYOUT

---------Mapping---------
select mapping_code
from apps.bne_mappings_vl
where integrator_code = 'XXAPINV4_XINTG';

-----------Content -------------
select content_code
from apps.bne_contents_vl
where integrator_code = 'XXAPINV4_XINTG';

Oracle Web ADI Interface Column Datatype Update Query

After Web ADI Interface has been defined we can not change Attribute datatype which is extracted from PLSQL procedure. Sometime required to update Interface attribute datatype from backend. Following Query is useful.

-----Data Type----------
Number - 1,
varchar - 2,
date - 3

-------Query 1----------

update bne_interface_cols_b
set
data_type=3
where
interface_col_name like 'P_ATTRIBUTE_LINE4' and INTERFACE_CODE='XXAPINVCREATE_XINTG_INTF1';

--------Query2---------------

update bne_attributes
set ATTRIBUTE2='VARCHAR2'
where
ATTRIBUTE1 like 'P_SEGMENT%' and
ATTRIBUTE_CODE like 'XXAPINV3_XINTG_UPL1%'
;

Many case following solution not work, so you have to find another workaround.

Oracle Web ADI Error: Web ADI Invalid Integrator Key

Error: Web ADi Invalid Integrator Key

For Specific user level, we have to set the following profile option 'YES', If user not having System Administrator Responsibility

Solution: 'BNE Allow No Security Rule' as Yes at the User level

Oracle First and Last Date of current year & previous year query

–> First Day of Current Year:

SELECT TRUNC (SYSDATE , ‘YEAR’) FROM DUAL;

–> Last Day of Current Year:

SELECT ADD_MONTHS(TRUNC (SYSDATE ,’YEAR’),12)-1 FROM DUAL;

–> First Day of Previous Year:

SELECT ADD_MONTHS (TRUNC (SYSDATE,’YEAR’), -12) FROM DUAL;

–> Last Day of Previous Year:

SELECT ADD_MONTHS (TRUNC (SYSDATE, ‘YEAR’), -1 ) +30 FROM DUAL;

Oracle profile option required to enable OAF Page Personalization

---------------------------------
Profile Name -- Value
---------------------------------

System Administrator Responsibility > Profile > System

1) FND: Personalization Region Link Enabled - Yes

2) Personalize Self-Service Defn - Yes

3) Disable Self-Service Personal - No

Oracle Web ADI Important Tables and Download and Upload FNDLOAD Commands

Technical Information

Integrator
BNE_INTEGRATORS_B
BNE_INTEGRATORS_TL
BNE_INTEGRATORS_VL

Layout
BNE_LAYOUTS_B
BNE_LAYOUTS_TL
BNE_LAYOUTS_VL

Layout Columns
BNE_LAYOUT_COLS
BNE_LAYOUT_COLS_V

Mapping
BNE_MAPPINGS_B
BNE_MAPPINGS_TL
BNE_MAPPINGS_VL

Content
BNE_CONTENTS_B
BNE_CONTENTS_TL
BNE_CONTENTS_VL

Interface
BNE_INTERFACES_B
BNE_INTERFACES_TL
BNE_INTERFACES_VL
Sql Qury to get Package and View name used in Integrator

SELECT BIT.USER_NAME WEB_ADI, BA.ATTRIBUTE2 "PACKAGE", BCT.USER_NAME "VIEW"
FROM BNE_INTEGRATORS_TL BIT,
BNE_ATTRIBUTES BA,
BNE_CONTENTS_TL BCT
WHERE substr(BIT.INTEGRATOR_CODE,1,length(BIT.INTEGRATOR_CODE)-5)||'_P0_ATT' = BA.ATTRIBUTE_CODE
AND BIT.APPLICATION_ID = BCT.APPLICATION_ID
AND BIT.LANGUAGE = 'US'
AND BCT.LANGUAGE = 'US'
AND BA.ATTRIBUTE1 = 'PROCEDURE'
AND BIT.APPLICATION_ID = BA.APPLICATION_ID
AND BCT.CONTENT_CODE = substr(BIT.INTEGRATOR_CODE,1,length(BIT.INTEGRATOR_CODE)-5)||'_CNT'
AND BIT.USER_NAME LIKE 'XX Test Create'
order by BIT.USER_NAME;
FNDLOAD command for WEB ADI
Integrator
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bneint.lct
GENERAL_223_INTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN=“XXCUST“
INTEGRATOR_CODE="GENERAL_223_INTG“

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bneint.lct
GENERAL_223_INTG.ldt

Layout
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnelay.lct
XX_TEST_CREATE_LAYOUT.ldt BNE_LAYOUTS LAYOUT_ASN=“XXCUST“
LAYOUT_CODE=" XX_TEST_CREATE_LAYOUT“

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnelay.lct
XX_TEST_CREATE_LAYOUT.ldt

Mapping
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnemap.lct
XX_HR_86_MAP.ldt BNE_MAPPINGS MAPPING_ASN="PER" MAPPING_CODE="HR_86_MAP“

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnemap.lct
XX_HR_86_MAP.ldt

Content
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnecont.lct
GENERAL_223_CNT.ldt BNE_CONTENTS CONTENT_ASN=“XXCUST“
CONTENT_CODE=" GENERAL_223_CNT“

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnecont.lct
GENERAL_223_CNT.ldt

Oracle Tax Regime and Operating Unit Join Query

select
distinct a.tax_regime_code,c.name,c.* from ZX_RATES_B a,zx_accounts b, hr_operating_units c
where a.tax_rate_id=b.tax_account_entity_id
and b.internal_organization_id=c.organization_id;

Oracle Database Version Query

SELECT * FROM V$INSTANCE;
SELECT * FROM V$VERSION;
select * from PRODUCT_COMPONENT_VERSION;

------Database Plateform Version Query------

declare
l_version varchar2(255);
l_compatibility varchar2(255);
begin
dbms_utility.db_version( l_version, l_compatibility );
dbms_output.put_line( l_version );
dbms_output.put_line( l_compatibility );
end;

select dbms_utility.port_string from dual;

Oracle Credit-Debit Invoice Reference Invoice No Query

SELECT
SUBSTR (SYS_CONNECT_BY_PATH (trx_number , ','), 2) CM_DM_REF_INV_NO
--- INTO v_OU_VAT_REG_NO
FROM (SELECT rcta2.trx_number,ROW_NUMBER () OVER (ORDER BY rcta2.trx_number ) rn, COUNT (*) OVER () cnt
FROM RA_CUSTOMER_TRX_ALL rcta1,
RA_CUSTOMER_TRX_ALL rcta2,
AR_RECEIVABLE_APPLICATIONS_ALL araa
WHERE --rcta1.trx_number = CM_DM_IINV_NO
rcta1.customer_trx_id=CM_DM_CUST_TRX_ID
AND araa.status = 'APP' --applied
AND araa.display = 'Y' --Not UNapplied
AND rcta1.customer_trx_id = araa.customer_trx_id
AND rcta2.customer_trx_id = araa.APPLIED_CUSTOMER_TRX_ID
ORDER BY apply_date ASC
)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

Oracle Item Category Extract Query

select
msi.segment1 Item_Code, msi.DESCRIPTION Item_Desc,
mcs.CATEGORY_SET_NAME, mck.CONCATENATED_SEGMENTS,
mck.SEGMENT1, mck.SEGMENT2, mck.SEGMENT3, mck.SEGMENT4, mck.SEGMENT5,
mck.SEGMENT6, mck.SEGMENT7, mck.SEGMENT8, mck.SEGMENT9, mck.SEGMENT10,
mck.SEGMENT11, mck.SEGMENT12, mck.SEGMENT13, mck.SEGMENT14, mck.SEGMENT15,
mck.SEGMENT16, mck.SEGMENT17, mck.SEGMENT18, mck.SEGMENT19, mck.SEGMENT20
from mtl_system_items_b msi, mtl_item_categories mic, MTL_CATEGORIES_KFV MCK, MTL_CATEGORY_SETS_TL mcs
where msi.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID and msi.ORGANIZATION_ID = mic.ORGANIZATION_ID
and mic.CATEGORY_ID = mck.CATEGORY_ID
and mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_ID and mcs.LANGUAGE = userenv('Lang')
and msi.ORGANIZATION_ID = :P_Org_id

Oracle Supplier Master Extract Query

select
pv.vendor_name Supplier_name,
pv.segment1 Supplier_num,
pvs.vendor_site_code Supplier_site_name,
(select party_site_number from hz_party_sites where party_site_id = pvs.party_site_id) site_num,
(select territory_short_name from fnd_territories_vl ft where pvs.country = ft.territory_code) site_country,
pvs.address_line1 site_address_line1,
pvs.address_line2 site_address_line2,
pvs.address_line3 site_address_line3,
pvs.address_line4 site_address_line4,
pvs.city site_city,
pvs.county site_county,
pvs.state site_state,
pvs.zip site_Postal_code,
hou.name BU_NAME ,
ship_loc.location_code ship_to_location_name,
ship_loc.address_line_1 ship_address_line_1,
ship_loc.address_line_2 ship_address_line_2,
ship_loc.address_line_3 ship_address_line_3,
ship_loc.town_or_city ship_to_city,
ship_loc.postal_code ship_to_postal_code,
(select territory_short_name from fnd_territories_vl ft where ship_loc.country = ft.territory_code) ship_to_country,
bill_loc.location_code bill_to_location_name,
bill_loc.address_line_1 bill_address_line_1,
bill_loc.address_line_2 bill_address_line_2,
bill_loc.address_line_3 bill_address_line_3,
bill_loc.town_or_city bill_to_city,
bill_loc.postal_code bill_to_postal_code,
(select territory_short_name from fnd_territories_vl ft where bill_loc.country = ft.territory_code) bill_to_country
from apps.po_vendors pv,
apps.ap_supplier_sites_all pvs,
apps.hr_operating_units hou,
hr_locations bill_loc,
hr_locations ship_loc
where pv.vendor_id = pvs.vendor_id
and pvs.org_id = hou.organization_id
and pvs.bill_to_location_id = bill_loc.location_id(+)
and pvs.ship_to_location_id = ship_loc.location_id(+)
-- and pvs.org_id = 43
and ((pv.end_date_active is null) or (pv.end_date_active > trunc(sysdate)))
and ((pvs.inactive_date is null) or (pvs.inactive_date > trunc(sysdate)))
order by pv.vendor_id,pvs.vendor_site_id

Oracle Customer Master Extract Query

SELECT GLL.NAME Ledger_name,
HP.PARTY_NAME CUSTOMER_NAME,
HP.PARTY_NUMBER CUSTOMER_NUMBER,
HCA.ACCOUNT_NUMBER Customer_account_number,
hps.party_site_name,
hps.party_site_number,
(select TERRITORY_SHORT_NAME from FND_TERRITORIES_TL where TERRITORY_CODE =hl.COUNTRY and language = 'US') site_COUNTRY,
HL.ADDRESS1,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
hl.county,
hl.state,
hl.POSTAL_CODE,
HROU.NAME OPERATING_UNIT,
hps.party_site_name,
hl.city site_name,
HCSUB.SITE_USE_CODE BILL_TO_CODE,
HCSUB.PRIMARY_FLAG BILL_TO_FLAG,
HCSUB.LOCATION BILL_TO_LOCATION,
HCSUS.SITE_USE_CODE SHIP_TO_CODE,
HCSUS.PRIMARY_FLAG SHIP_TO_FLAG,
HCSUS.LOCATION SHIP_TO_LOCATION
FROM APPS.HZ_PARTIES HP,
GL_LEDGERS GLL,
APPS.HZ_PARTY_SITES HPS,
APPS.HZ_LOCATIONS HL,
APPS.HZ_CUST_ACCOUNTS_ALL HCA,
APPS.HZ_CUST_ACCT_SITES_ALL HCSA,
APPS.HZ_CUST_SITE_USES_ALL HCSUS,
HZ_CUST_SITE_USES_ALL HCSUB,
HR_OPERATING_UNITS HROU
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HROU.SET_OF_BOOKS_ID = GLL.LEDGER_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HCSA.ORG_ID = HROU.ORGANIZATION_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSA.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
AND HCSUS.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID
AND HCSUB.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
AND HCSUS.SITE_USE_CODE(+) = 'SHIP_TO'
AND HCSUB.SITE_USE_CODE(+) = 'BILL_TO'
-- and hp.party_name like 'BAY%SHORE%'
-- AND GLL.NAME LIKE '%MCC%'

Oracle MTL_TRANSACTION_INTERFACE - Locking Issue Resolve Query

UPDATE mtl_transactions_interface
SET
process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = 1,
error_code = NULL,
error_explanation = NULL
WHERE
process_flag IN (1,3);

Oracle Scheduled Request Query

SELECT DISTINCT frl.responsibility_name,
fu.user_name,
fcr.request_id,
(SELECT meaning
FROM apps.fnd_lookups
WHERE lookup_type='CP_PHASE_CODE'
AND lookup_code =fcr.phase_code
) Phase,
(SELECT meaning
FROM apps.fnd_lookups
WHERE lookup_type='CP_STATUS_CODE'
AND lookup_code =fcr.status_code
) Status,
fcs.program,
to_date(fcr.requested_start_date,'DD-MM-RRRR hh24:mi:ss') Start_Date,
fcr.resubmit_interval
||' '
||fcr.resubmit_interval_unit_code Resubmit_Interval,
NVL2(fcr.resubmit_interval,'PERIODICALLY',NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS','ONCE')) schedule_type,
fcs.argument_text
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_responsibility_tl frl,
apps.fnd_user fu,
apps.fnd_conc_req_summary_v fcs
WHERE fcr.phase_code ='P'
AND fcr.request_id = fcs.request_id
AND frl.language ='US'
AND fcr.requested_by =fu.user_id
AND fcr.responsibility_id =frl.responsibility_id
AND fcr.status_code IN ('P','Q','I')
AND fcp.language ='US'
AND fcp.source_lang ='US'
AND (NVL(fcr.request_type, 'X') != 'S')
AND fcr.concurrent_program_id =fcp.concurrent_program_id
AND fcr.requested_start_date >= SYSDATE
AND fcs.program like 'PHCC%BCG%'
--AND to_date(fcr.requested_start_date,'DD-MM-RRRR hh24:mi:ss') BETWEEN NVL(to_date(:p_from_date,'DD-MM-RRRR hh24:mi:ss'),fcr.requested_start_date) AND NVL(to_date(:p_to_date,'DD-MM-RRRR hh24:mi:ss'),fcr.requested_start_date)
ORDER BY program DESC

Oracle Concurrent Request Status Query

SELECT DISTINCT fcp.user_concurrent_program_name,
fcp.concurrent_program_name,
fcr.request_id,
fcr.request_date,
flv.meaning status,
fcr.status_code,
fcr.completion_text,
fcr.logfile_name,
fcr.outfile_name,
fcr.argument_text
FROM apps.fnd_concurrent_programs_vl fcp,
apps.fnd_concurrent_requests fcr,
apps.fnd_lookup_values flv
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
AND trunc(fcr.last_update_date) = trunc(SYSDATE)
AND flv.lookup_code = fcr.status_code
AND flv.lookup_type = 'CP_STATUS_CODE'
AND flv.language = 'US'
AND fcr.request_id=25628277
ORDER BY fcr.request_date,
fcr.request_id DESC;

Oracle Application Short Name Query

SELECT fa.application_id "Application ID",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath"
FROM fnd_application fa,
fnd_application_tl fat
WHERE fa.application_id = fat.application_id
AND fat.language = USERENV('LANG')
AND fat.application_name in ('Receivables','Purchasing','Payables','General Ledger','CUSTOM_APPLICATION_NAME') -- <change it>
ORDER BY fat.application_name;