The client had a requirement to add a filter to the memo line LOV on the AR Transactions form
We tried to achieve this using Forms Personalization, which is the best way to go about implementing this requirement.
However, there is a restriction in Forms Personalization which does not let you create a record group with a SQL query more than 4000 characters in length.
Therefore, we went for the next best option – CUSTOM.PLL
To the uninitiated, CUSTOM.PLL is a library which allows you to introduce extensions to Oracle Applications Forms behaviour.
The steps to implement the requirement are given below:
· Create a new library procedure called XXARXTWMAI.pll with one procedure “Custom_memo_line_lov”. The code is shown below, it essentially will create a new record group with the new query, then find the LOV and attach the new record group to it. It will also try and populate the record group with records.
PROCEDURE custom_memo_line_lov IS
rg_id RECORDGROUP;
rg_name VARCHAR2(100) := 'XXAR_TLIN_MEMO_LINE';
v_sql_string VARCHAR2(32000);
errcode NUMBER;
l_error_message VARCHAR2(200);
the_rowcount NUMBER;
v_lov lov;
BEGIN
/**
The SQL String is taken from standard AR_TLIN_MEMO_LINE record group
and we have just added the line
--AND aml.attribute1 = :tgw_header.ctt_type_name--
to filter and show only memo lines related to the transaction type selected
**/
v_sql_string := 'SELECT aml.memo_line_id, aml.description "aml_description", aml.NAME,
al.meaning, aml.line_type,
DECODE (aml.line_type, ''TAX'', 0, ''FREIGHT'', 0, '''') quantity,
DECODE
(:parameter.art_class,
''CM'', :tlin_lines.uom_code,
DECODE (aml.line_type,
''TAX'', '''',
''FREIGHT'', '''',
''CHARGES'', '''',
DECODE (SIGN ( TRUNC (SYSDATE)
- NVL (TRUNC (uom.disable_date),
TRUNC (SYSDATE)
)
),
-1, NVL (uom.uom_code, :tlin_lines.uom_code),
0, NVL (uom.uom_code, :tlin_lines.uom_code),
1, :tlin_lines.uom_code
)
)
) uom_code,
DECODE
(:parameter.art_class,
''CM'', :tlin_lines.uom_unit_of_measure_name,
DECODE (aml.line_type,
''TAX'', '''',
''FREIGHT'', '''',
''CHARGES'', '''',
DECODE (SIGN ( TRUNC (SYSDATE)
- NVL (TRUNC (uom.disable_date),
TRUNC (SYSDATE)
)
),
-1, NVL (uom.unit_of_measure,
:tlin_lines.uom_unit_of_measure_name
),
0, NVL (uom.unit_of_measure,
:tlin_lines.uom_unit_of_measure_name
),
1, :tlin_lines.uom_unit_of_measure_name
)
)
) unit_of_measure,
DECODE (aml.line_type,
''TAX'', DECODE (:parameter.art_class, ''CM'', '''', 0),
''FREIGHT'', DECODE (:parameter.art_class, ''CM'', '''', 0),
aml.unit_std_price / NVL (:parameter.art_exchange_rate, 1)
) unit_std_price,
/* unit selling price (same as unit standard price): */
DECODE (aml.line_type,
''TAX'', 0,
''FREIGHT'', 0,
aml.unit_std_price / NVL (:parameter.art_exchange_rate, 1)
) unit_selling_price,
DECODE (aml.line_type, ''TAX'', 0, ''FREIGHT'', 0, '''') extended_amount,
DECODE
(:parameter.art_class,
''CM'', :tlin_lines.accounting_rule_id,
DECODE (:tlin_lines.rr_accounting_rule_name,
NULL, DECODE (:parameter.art_invoicing_rule_id,
NULL, :tlin_lines.accounting_rule_id,
DECODE (rr.status,
''A'', rr.rule_id,
:tlin_lines.accounting_rule_id
)
),
:tlin_lines.accounting_rule_id
)
) accounting_rule_id,
DECODE
(:parameter.art_class,
''CM'', :tlin_lines.rr_accounting_rule_name,
DECODE (:tlin_lines.rr_accounting_rule_name,
NULL, DECODE (:parameter.art_invoicing_rule_id,
NULL, :tlin_lines.rr_accounting_rule_name,
DECODE (rr.status,
''A'', rr.NAME,
:tlin_lines.rr_accounting_rule_name
)
),
:tlin_lines.rr_accounting_rule_name
)
) accounting_rule_name,
DECODE
(:parameter.art_class,
''CM'', :tlin_lines.accounting_rule_duration,
DECODE (:tlin_lines.rr_accounting_rule_name,
NULL, DECODE (:parameter.art_invoicing_rule_id,
NULL, :tlin_lines.accounting_rule_duration,DECODE
(rr.status,
''A'', DECODE (rr.TYPE,
''ACC_DUR'', 1,
''A'', rr.occurrences
),
:tlin_lines.accounting_rule_duration
)
),
:tlin_lines.accounting_rule_duration
)
) accounting_rule_duration,
DECODE
(:parameter.art_class,
''CM'', :tlin_lines.rr_frequency,
DECODE (:tlin_lines.rr_accounting_rule_name,
NULL, DECODE (:parameter.art_invoicing_rule_id,
NULL, :tlin_lines.rr_frequency,
DECODE (rr.status,
''A'', rr.frequency,
:tlin_lines.rr_frequency
)
),
:tlin_lines.rr_frequency
)
) rr_frequency,
DECODE
(:parameter.art_class,
''CM'', :tlin_lines.rr_accounting_rule_type,
DECODE (:tlin_lines.rr_accounting_rule_name,
NULL, DECODE (:parameter.art_invoicing_rule_id,
NULL, :tlin_lines.rr_accounting_rule_type,
DECODE (rr.status,
''A'', rr.TYPE,
:tlin_lines.rr_accounting_rule_type
)
),
:tlin_lines.rr_accounting_rule_type
)
) accounting_rule_type,
DECODE
(:parameter.art_class,
''CM'', TO_DATE (:tlin_lines.rule_start_date, ''DD-MON-RRRR''),
DECODE
(:tlin_lines.rr_accounting_rule_name,
NULL, DECODE
(:parameter.art_invoicing_rule_id,
NULL, TO_DATE (:tlin_lines.rule_start_date,
''DD-MON-RRRR''),
DECODE (rr.status,
''A'', DECODE
(rr.frequency,
''SPECIFIC'', MIN (rs.rule_date),
DECODE
(:parameter.art_invoicing_rule_id,
-2, TO_DATE
(:parameter.art_trx_date,
''DD-MON-RRRR''
),
-3, SYSDATE
)
),
TO_DATE (:tlin_lines.rule_start_date,
''DD-MON-RRRR''
)
)
),
TO_DATE (:tlin_lines.rule_start_date, ''DD-MON-RRRR'')
)
) rule_start_date
FROM ar_memo_lines aml,
ar_lookups al,
mtl_units_of_measure uom,
ra_rules rr,
ra_rule_schedules rs
WHERE al.lookup_type = ''STD_LINE_TYPE''
AND al.lookup_code = aml.line_type
AND aml.uom_code = uom.uom_code(+)
AND aml.accounting_rule_id = rr.rule_id(+)
AND rr.rule_id = rs.rule_id(+)
AND :tlin_lines.inventory_item_id IS NULL
AND aml.line_type IN
(''CHARGES'', ''FREIGHT'', ''LINE'', ''TAX'') /* freight is only allowed if no header freight was entered and if allow freight = yes */
AND ( aml.line_type != ''FREIGHT'' OR ( aml.line_type = ''FREIGHT''
AND :parameter.art_allow_frt_flag = ''Y''
AND NOT EXISTS (
SELECT ''header freight exists''
FROM ra_customer_trx_lines frt
WHERE customer_trx_id = :parameter.art_customer_trx_id
AND line_type = ''FREIGHT''
AND link_to_cust_trx_line_id IS NULL)
)
) /* may pick charges line for dms and cms only */
AND ( aml.line_type != ''CHARGES''
OR ( aml.line_type = ''CHARGES''
AND :parameter.art_class IN (''DM'', ''CM'')
)
) /* if profile ar_allow_manual_tax_lines is no do not show */ /* tax memo lines */
AND ( aml.line_type != ''TAX''
OR ( aml.line_type = ''TAX''
AND :ar_world.ar_allow_manual_tax_lines = ''Y''
)
) /* if update, show only memo lines of the current type */
AND aml.line_type =
DECODE
(:tlin_lines.customer_trx_line_id,
'''', aml.line_type,
NVL (:tlin_lines.ml_memo_line_type, ''LINE'')
) /* if chargeback, you can choose only line_type line */
AND aml.line_type =
DECODE (:parameter.art_class,
''CB'', ''LINE'',
aml.line_type
)
AND :parameter.art_trx_date BETWEEN NVL (TRUNC (aml.start_date),
:parameter.art_trx_date
)
AND NVL (TRUNC (aml.end_date),
:parameter.art_trx_date
)
AND aml.attribute1 = :tgw_header.ctt_type_name
GROUP BY aml.memo_line_id,
al.meaning,
aml.NAME,
aml.description,
aml.line_type,
uom.disable_date,
uom.uom_code,
uom.unit_of_measure,
aml.unit_std_price,
rr.rule_id,
rr.status,
rr.NAME,
rr.TYPE,
rr.occurrences,
rr.frequency
ORDER BY al.meaning, aml.NAME';
/** Make sure group doesn't already exist */
rg_id := Find_Group( rg_name );
/* ** If it does not exist, create it */
IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query
( rg_name,
v_sql_string
);
END IF;
/* ** Populate the record group */
errcode := Populate_Group( rg_id );
-- the_rowcount := Get_Group_Row_Count( rg_id );
/* **Change the LOV record group */
v_lov := find_lov('AR_TLIN_MEMO_LINE');
IF get_lov_property(v_lov,group_name) = 'AR_TLIN_MEMO_LINE'
THEN
set_lov_property(v_lov,group_name,rg_name);
END IF;
END;
· Extract the CUSTOM.pll from the $AU_TOP/resource folder on the application/web tier
· Attach the library XXARXTWMAI.pll with portable option (don’t include directory path)
· Write code in the “event” procedure to call the custom procedure:
On WHEN-NEW_ITEM_INSTANCE event for the ARXTWMAI form, TLIN_LINES block and DESCRIPTION item, call the procedure custom_memo_line_lov;
To compile the custom.pll, use the command as below
frmcmp Module=CUSTOM.pll Userid=$apps_user/$apps_pwd Module_Type=LIBRARY Logon=YES Output_File=CUSTOM.plx Batch=YES Compile_All=SPECIAL
Also remember that for custom.pll changes to take effect, it is advisable to logout and login to the applications.
2 comments:
Hi , I have tried the above approach , made similar changes to the where clause of the query. When i checked the same in my application , i get a message saying that unable to read the values from the LOV. Can you please let me know if i am missing anything here.
Thanks ,
Naga.
Thanks for your post. It was very helpful and i have achieved my requirement without any issues.Once again thanks for you knowledge share.
Regards,
Sandeep
Post a Comment