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;