— To verify Personalization Rules SELECT ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
DECODE (ffcr.rule_type,
‘F’, ‘Form’,
‘A’, ‘Function’,
‘Other’
) “Level”,
ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
DECODE (ffcr.fire_in_enter_query,
‘Y’, ‘Both’,
‘N’, ‘Not in Enter-Query Mode’,
‘O’, ‘Only in Enter-Query Mode’,
‘Other’
) “Processing Mode”
FROM apps.fnd_form_custom_rules ffcr
WHERE ffcr.function_name = ‘ONT_OEXOEORD’
AND ffcr.form_name = ‘OEXOEORD’
ORDER BY ffcr.SEQUENCE;
— To verify Personalization Rules along with Scopes
SELECT ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
DECODE (ffcr.rule_type,
‘F’, ‘Form’,
‘A’, ‘Function’,
‘Other’
) “Level”,
ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
DECODE (ffcr.fire_in_enter_query,
‘Y’, ‘Both’,
‘N’, ‘Not in Enter-Query Mode’,
‘O’, ‘Only in Enter-Query Mode’,
‘Other’
) “Processing Mode”,
DECODE (ffcs.level_id,
’10’, ‘Industry’,
’20’, ‘Site’,
’30’, ‘Responsibility’,
’40’, ‘User’,
‘Other’
) “Context Level”,
ffcs.level_value “Context Value”
FROM apps.fnd_form_custom_rules ffcr, apps.fnd_form_custom_scopes ffcs
WHERE ffcr.function_name = ‘ONT_OEXOEORD’
AND ffcr.form_name = ‘OEXOEORD’
AND ffcr.ID = ffcs.rule_id(+)
ORDER BY ffcr.SEQUENCE, ffcs.level_id;
— To verify Personalization Rules along with Actions
SELECT ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
DECODE (ffcr.rule_type,
‘F’, ‘Form’,
‘A’, ‘Function’,
‘Other’
) “Level”,
ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
DECODE (ffcr.fire_in_enter_query,
‘Y’, ‘Both’,
‘N’, ‘Not in Enter-Query Mode’,
‘O’, ‘Only in Enter-Query Mode’,
‘Other’
) “Processing Mode”,
ffca.SEQUENCE “Action Seq”, ffca.action_type “Action Type”,
ffca.summary “Action Description”, ffca.LANGUAGE “Action Language”,
ffca.enabled “Action Enabled”, ffca.property_value,
ffca.argument_type, ffca.target_object, ffca.object_type,
ffca.folder_prompt_block, ffca.MESSAGE_TYPE, ffca.MESSAGE_TEXT,
ffca.builtin_type, ffca.builtin_arguments, ffca.property_name,
ffca.menu_entry, ffca.menu_label, ffca.menu_seperator,
ffca.menu_enabled_in, ffca.menu_action, ffca.menu_argument_long,
ffca.menu_argument_short, ffca.action_id,
ffca.request_application_id
FROM apps.fnd_form_custom_rules ffcr, apps.fnd_form_custom_actions ffca
WHERE ffcr.function_name = ‘ONT_OEXOEORD’
AND ffcr.form_name = ‘OEXOEORD’
AND ffcr.ID = ffca.rule_id(+)
ORDER BY ffcr.SEQUENCE, ffca.SEQUENCE;
API – Creating Values in Independant and dependant Value Sets
Share this:
API – Customer Level Attribute Updation (Customer Class , Etc)
p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
xio_p_object_version NUMBER;
x_msg_data VARCHAR2(2000);
v_account_id NUMBER;
V_OBJECT_VERSION NUMBER;
BEGIN
— FND_GLOBAL.APPS_INITIALIZE(<user_id>,<resp_id>,<resp_applicarion_id>);
— MO_GLOBAL.INIT(‘AR’);
— MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, <org_id>);
FND_GLOBAL.APPS_INITIALIZE(1535,50930,222);
MO_GLOBAL.INIT(‘AR’);
MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, 807);
BEGIN
SELECT –HCSU.SITE_USE_ID,
hcas.CUST_ACCOUNT_ID,
HCS.OBJECT_VERSION_NUMBER
INTO V_ACCOUNT_ID,
V_OBJECT_VERSION
FROM HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
hz_cust_accounts_all hcs
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID =HCSU.CUST_ACCT_SITE_ID
AND HCSU.SITE_USE_CODE = ‘BILL_TO’
AND HCS.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND ltrim(rtrim(upper(HP.PARTY_NAME)))= ltrim(rtrim(upper(‘Spinneys Mussafah Warehouse’)));
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR IS’||SUBSTR(SQLERRM,1,150));
END;
p_cust_account_rec.cust_account_id := V_account_ID; — Site USe to be updated
xio_p_object_version := V_OBJECT_VERSION; –xio_p_object_version := 1;
p_cust_account_rec.customer_class_code := ‘RULER’; — ‘DEALER’ , ‘FAMILY’ , ‘GENERAL’
hz_cust_account_v2pub.update_cust_account(
FND_API.G_FALSE,
p_cust_account_rec,
xio_p_object_version,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line(‘***************************’);
dbms_output.put_line(‘Output information ….’);
dbms_output.put_line(‘x_return_status: ‘||x_return_status);
dbms_output.put_line(‘x_msg_count: ‘||x_msg_count);
dbms_output.put_line(‘xio_p_object_version: ‘||xio_p_object_version);
dbms_output.put_line(‘x_msg_data: ‘||x_msg_data);
dbms_output.put_line(‘***************************’);
COMMIT;
END;
Share this:
Query to find Parameters and Value Sets associated with a Concurrent Program
SELECT
fcpl.user_concurrent_program_name “Concurrent Program Name”,
fcp.concurrent_program_name “Short Name”,
fdfcuv.column_seq_num “Column Seq Number”,
fdfcuv.end_user_column_name “Parameter Name”,
fdfcuv.form_left_prompt “Prompt”,
fdfcuv.enabled_flag ” Enabled Flag”,
fdfcuv.required_flag “Required Flag”,
fdfcuv.display_flag “Display Flag”,
fdfcuv.flex_value_set_id “Value Set Id”,
ffvs.flex_value_set_name “Value Set Name”,
flv.meaning “Default Type”,
fdfcuv.DEFAULT_VALUE “Default Value”
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE
fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fdfcuv.descriptive_flexfield_name = ‘$SRS$.’
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = ‘FLEX_DEFAULT_TYPE’
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV (‘LANG’)
AND flv.LANGUAGE(+) = USERENV (‘LANG’)
ORDER BY fdfcuv.column_seq_num;
Share this:
Open Interface – GL Interface
BEGIN
/*
SELECT * FROM GL_INTERFACE
MANDATORY FIELDS
STATUS
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG
USER_JE_CATEGORY_NAME
USER_JE_SOURCE_NAME
*/
— GL > JOURNALS > IMPORT > RUN
INSERT INTO GL_INTERFACE (
LEDGER_ID ,
STATUS ,
SET_OF_BOOKS_ID ,
USER_JE_SOURCE_NAME ,
USER_JE_CATEGORY_NAME ,
ACCOUNTING_DATE ,
CURRENCY_CODE ,
DATE_CREATED ,
CREATED_BY ,
ACTUAL_FLAG ,
— ENCUMBRANCE_TYPE_ID ,
— BUDGET_VERSION_ID ,
USER_CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
CURRENCY_CONVERSION_RATE ,
SEGMENT1 ,
SEGMENT2 ,
SEGMENT3 ,
SEGMENT4 ,
SEGMENT5 ,
SEGMENT6 ,
SEGMENT7 ,
SEGMENT8 ,
SEGMENT9 ,
ENTERED_DR ,
ENTERED_CR ,
ACCOUNTED_DR ,
ACCOUNTED_CR ,
PERIOD_NAME ,
REFERENCE1 ,
REFERENCE2 ,
REFERENCE4 ,
REFERENCE5
)
values
(
2021 , — SELECT * FROM GL_SETS_OF_BOOKS
‘Y’ , — i.STATUS
2021 , — SELECT * FROM GL_SETS_OF_BOOKS (Trading Companies SOB)
‘Manual’ , — SELECT * FROM GL_JE_SOURCES WHERE JE_SOURCE_NAME LIKE ‘Manual’
‘SSE Manual’ , — SELECT USER_JE_CATEGORY_NAME FROM GL_JE_CATEGORIES WHERE USER_JE_CATEGORY_NAME LIKE ‘SSE%’
SYSDATE , — i.ACCOUNTING_DATE
‘AED’ , — i.CURRENCY_CODE
sysdate , — DATE_CREATED
1090 , — fnd_global.user_id
‘A’ , — i.ACTUAL_FLAG — A Actual , B – Budget E – Encumbrance
— i.ENCUMBRANCE_TYPE_ID ,
— i.BUDGET_VERSION_ID ,
” , — i.USER_CURRENCY_CONVERSION_TYPE
” , — i.CURRENCY_CONVERSION_DATE
” , — i.CURRENCY_CONVERSION_RATE
’02’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT1 = 02
’01’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT2 = 01
’01’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT3 = 01
’05’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT4 = 05
’00’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT5 = 00
’00’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT6 = 00
’01’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT7 = 01
‘981100’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT8 = 981100
’00’ , — SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT9 = 00
2300 , — i.ENTERED_DR
2300 , — i.ENTERED_CR
2300 , — i.ACCOUNTED_DR
2300 , — i.ACCOUNTED_CR
‘JUN-10’ , — i.PERIOD_NAME (PERIOD SHOULD BE OPEN )
SYSDATE , — i.REFERENCE1
‘INSERTED BY CUSTOM GL INT’ , — i.REFERENCE2
‘INSERT’ , — i.REFERENCE4 ( REFERENCE4 it takes in JE NAME )
‘SSE’ — i.REFERENCE5
);
END;
begin
XX_GL_INTERFACE;
end;
Share this:
Verifying Form Personalizations
SELECT ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
DECODE (ffcr.rule_type,
‘F’, ‘Form’,
‘A’, ‘Function’,
‘Other’
) “Level”,
ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
DECODE (ffcr.fire_in_enter_query,
‘Y’, ‘Both’,
‘N’, ‘Not in Enter-Query Mode’,
‘O’, ‘Only in Enter-Query Mode’,
‘Other’
) “Processing Mode”
FROM apps.fnd_form_custom_rules ffcr
WHERE ffcr.function_name = ‘ONT_OEXOEORD’
AND ffcr.form_name = ‘OEXOEORD’
ORDER BY ffcr.SEQUENCE;
— To verify Personalization Rules along with Scopes
SELECT ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
DECODE (ffcr.rule_type,
‘F’, ‘Form’,
‘A’, ‘Function’,
‘Other’
) “Level”,
ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
DECODE (ffcr.fire_in_enter_query,
‘Y’, ‘Both’,
‘N’, ‘Not in Enter-Query Mode’,
‘O’, ‘Only in Enter-Query Mode’,
‘Other’
) “Processing Mode”,
DECODE (ffcs.level_id,
’10’, ‘Industry’,
’20’, ‘Site’,
’30’, ‘Responsibility’,
’40’, ‘User’,
‘Other’
) “Context Level”,
ffcs.level_value “Context Value”
FROM apps.fnd_form_custom_rules ffcr, apps.fnd_form_custom_scopes ffcs
WHERE ffcr.function_name = ‘ONT_OEXOEORD’
AND ffcr.form_name = ‘OEXOEORD’
AND ffcr.ID = ffcs.rule_id(+)
ORDER BY ffcr.SEQUENCE, ffcs.level_id;
— To verify Personalization Rules along with Actions
SELECT ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
DECODE (ffcr.rule_type,
‘F’, ‘Form’,
‘A’, ‘Function’,
‘Other’
) “Level”,
ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
DECODE (ffcr.fire_in_enter_query,
‘Y’, ‘Both’,
‘N’, ‘Not in Enter-Query Mode’,
‘O’, ‘Only in Enter-Query Mode’,
‘Other’
) “Processing Mode”,
ffca.SEQUENCE “Action Seq”, ffca.action_type “Action Type”,
ffca.summary “Action Description”, ffca.LANGUAGE “Action Language”,
ffca.enabled “Action Enabled”, ffca.property_value,
ffca.argument_type, ffca.target_object, ffca.object_type,
ffca.folder_prompt_block, ffca.MESSAGE_TYPE, ffca.MESSAGE_TEXT,
ffca.builtin_type, ffca.builtin_arguments, ffca.property_name,
ffca.menu_entry, ffca.menu_label, ffca.menu_seperator,
ffca.menu_enabled_in, ffca.menu_action, ffca.menu_argument_long,
ffca.menu_argument_short, ffca.action_id,
ffca.request_application_id
FROM apps.fnd_form_custom_rules ffcr, apps.fnd_form_custom_actions ffca
WHERE ffcr.function_name = ‘ONT_OEXOEORD’
AND ffcr.form_name = ‘OEXOEORD’
AND ffcr.ID = ffca.rule_id(+)
ORDER BY ffcr.SEQUENCE, ffca.SEQUENCE;
Share this: