LOV Changing Using Custom.pll

à Need to Create a new Procedure in the custom.pll

à Need to find the Standard Lov name form the application by placing the cursor on the particular field form the below navigation.

Help à Diagnostics à Properties à Item.

à Need to place the new sql query for "V_SQL_STRING" variable which needs to be replaced. 

---------------------------------------------------

Sample Procedure

----------------------------------------------------

PROCEDURE XXX_LINE_TAX_RATE_LOV IS

                v_lov_name varchar2(240):=  'LINE_TAX_RATE'; --'TAX_CLASSIFICATION_CODE'; --Name of LOV attached to form item

                v_group_id RecordGroup;

                v_sql_string VARCHAR2(32000);

                v_rgroup_name varchar2(240):= 'XXX_TAX_RATE_CODE';   --Name of new RecordGroup for this LOV

                v_lov      lov;

                errcode NUMBER:= 0;

               

BEGIN

                v_group_id := FIND_GROUP(v_rgroup_name);

               

                IF not ID_NULL(v_group_id) THEN

                DELETE_GROUP_ROW(v_group_id,ALL_ROWS);

                END IF;

               

                v_sql_string :=  'WITH rslt

     AS (SELECT NVL (C2.SEGMENT1, C1.SEGMENT1) segment

           FROM AP_INVOICES_ALL A,

                AP_INVOICE_LINES_ALL B,

                GL_CODE_COMBINATIONS C1,

                AP_INVOICE_DISTRIBUTIONS_ALL D,

                GL_CODE_COMBINATIONS C2

          WHERE     A.INVOICE_ID = B.INVOICE_ID

                AND NVL (B.DISCARDED_FLAG, ''N'') <> ''Y''

                AND B.DEFAULT_DIST_CCID = C1.CODE_COMBINATION_ID(+)

                AND A.INVOICE_ID = :INV_SUM_FOLDER.INVOICE_ID

                AND B.INVOICE_ID = D.INVOICE_ID(+)

                AND D.DIST_CODE_COMBINATION_ID = C2.CODE_COMBINATION_ID(+)

                AND B.LINE_TYPE_LOOKUP_CODE <> ''TAX'')

SELECT tr.tax_rate_code,

       tr.tax_rate_name,

       tr.tax_status_code,

       tr.tax_jurisdiction_code,

       tr.tax,

       tr.tax_regime_code,

       tr.percentage_rate

  FROM zx_sco_rates tr, ZX_ACCOUNTS ZXA, GL_CODE_COMBINATIONS GCC

WHERE     TR.TAX_RATE_ID = ZXA.TAX_ACCOUNT_ENTITY_ID

       AND ZXA.TAX_ACCOUNT_CCID = GCC.CODE_COMBINATION_ID

       AND TR.EFFECTIVE_TO IS NULL

       AND tr.tax_status_code = :line_sum_folder.tax_status_code

       AND tr.tax = :line_sum_folder.tax

       AND tr.tax_regime_code = :line_sum_folder.tax_regime_code

       AND ZXA.internal_organization_id = :inv_sum_folder.org_id

       AND TR.ACTIVE_FLAG = ''Y''

       AND tr.TAX_RATE_CODE = ''41-UK 17.5%''

       AND GCC.SEGMENT1 IN (SELECT segment FROM rslt

                            UNION

                            SELECT GCC.SEGMENT1

                              FROM DUAL

                             WHERE NOT EXISTS (SELECT 1 FROM rslt))' ;

                   

v_group_id := Find_Group(v_rgroup_name );

IF ID_NULL(v_group_id) THEN  

                                               

                v_group_id := Create_Group_From_Query( v_rgroup_name,v_sql_string);

               

END IF;

--errcode := Populate_Group(v_group_id);

v_lov    := find_lov(v_lov_name);

 set_lov_property(v_lov,group_name,v_rgroup_name);

IF errcode <> 0 THEN  --Show error if population errors

   FND_MESSAGE.debug('Error during populate_group : '||errcode);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

FND_MESSAGE.set_string('No data found');

RAISE FORM_TRIGGER_FAILURE;

WHEN OTHERS THEN

FND_MESSAGE.ERROR;

RAISE FORM_TRIGGER_FAILURE;

 

END;

------------------------------

à Once the procedure is created then we need to call the procedure in custom.pll from the appropriate trigger as per over requirement and Validations.

---------------------------------------------

Sample Code

--------------------------------------------

IF form_name = 'APXINWKB' then

IF block_name = 'LINE_SUM_FOLDER' THEN

   

     IF item_name = 'LINE_SUM_FOLDER.DESCRIPTION' THEN

         

          V_GET_VENDOR_TAX_LINE := GET_VENDOR_TAX_LINE(NAME_IN('INV_SUM_FOLDER.ATTRIBUTE13'));

     

      IF V_GET_VENDOR_TAX_LINE IS NOT NULL AND V_GET_VENDOR_TAX_LINE > 0 THEN

    

                       null;

       ELSIF NAME_IN('LINE_SUM_FOLDER.LINE_TYPE') = 'Tax' then 

          

            v_purchasing_entity := _entity_vendor_map(SUBSTR(NAME_IN('global.g_CODE_COMBINATION'),1,2),NAME_IN('LINE_SUM_FOLDER.ORG_ID'), v_XXX_pe_country);

                              

               IF v_purchasing_entity IS NOT NULL AND v_XXX_pe_country = SUBSTR(NAME_IN('INV_SUM_FOLDER.ATTRIBUTE13'),1,2) THEN

                                                                       

                       v_XXX_eu_line_tax_lov := FIND_GROUP('LINE_TAX_RATE');

                      

                       IF NOT  ID_NULL(v_XXX_eu_line_tax_lov) THEN

                                                                          

                          XXX_LINE_TAX_RATE_LOV;

 

                       END IF;

                      

                else

                         null;

    

                END IF;

         END IF;      

              

     END IF;

   END IF;

END IF;