, ,

API – Creating Values in Independant and dependant Value Sets

declare
x varchar2(200);
v_msg varchar2(2000);
begin

fnd_global.apps_initialize (1090, 51007, 401);

  FND_FLEX_VAL_API.create_independent_vset_value
 (‘XX_IND_VSET’,’10’,’Inserted from API’,’Y’,sysdate,NULL,’N’,NULL,NULL,X);
    DBMS_OUTPUT.PUT_LINE(X);
exception
when others then
v_msg:=fnd_flex_val_api.message;
  DBMS_OUTPUT.PUT_LINE(v_msg);
end;
/

COMMIT


——    Inserting child dependent Value Set Values ——————————-

declare
x varchar2(200);
v_msg varchar2(2000);
begin

fnd_global.apps_initialize (1090, 51007, 401);


 FND_FLEX_VAL_API.create_dependent_vset_value
  (‘XX_DEP_VSET’,’10’,’10.1′,’Dependent Value inserted through API’,’Y’,sysdate,NULL,NULL,x);
    DBMS_OUTPUT.PUT_LINE(X);
exception
when others then
v_msg:=fnd_flex_val_api.message;
  DBMS_OUTPUT.PUT_LINE(v_msg);
end;
/

COMMIT;
/by
, ,

API – Customer Level Attribute Updation (Customer Class , Etc)

declare
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;

/by
, , ,

Query to find Parameters and Value Sets associated with a Concurrent Program

The following query will fetch the Parameter List and associated Value Sets of 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;
 

/by
, ,

Open Interface – GL Interface

CREATE OR REPLACE PROCEDURE XX_GL_INTERFACE IS
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;

/by
,

Verifying Form Personalizations

— 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;

/by