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;
  • October 12, 2015 | 19 views