How to customize transaction type LOV on the transaction forms using form personalization?
Goal:
Customize transaction type list of value using form personalization.
Solution:
1. Download form ARXTWMAI.fmv from your application server
2. Open form using form builder and get sql query from record group “ARXTWTGW_TRANSACTION_TYPE”
OR use your custom sql query
3. Customize/Write SQL query according to your requirement
(For example: we want to show only invoice transaction type)
SELECT ctt.cust_trx_type_id cust_trx_type_id,
ctt.name name,
ctt.description description,
ctt.TYPE class,
arl_class.meaning class_meaning,
ctt.accounting_affect_flag open_receivables_flag,
ctt.post_to_gl post_to_gl_flag,
ctt.allow_freight_flag allow_freight_flag,
ctt.creation_sign creation_sign,
ctt.allow_overapplication_flag allow_overapplication_flag,
ctt.natural_application_only_flag natural_application_only_flag,
ctt.tax_calculation_flag tax_calculation_flag,
arl_status.meaning default_status,
arl_print.meaning default_printing_option,
rat.name default_term
FROM ar_lookups arl_print,
ar_lookups arl_status,
ar_lookups arl_class,
ra_terms rat,
ra_cust_trx_types ctt
WHERE ‘INVOICE_PRINT_OPTIONS’ = arl_print.lookup_type
AND ctt.default_printing_option = arl_print.lookup_code
AND ‘INVOICE_TRX_STATUS’ = arl_status.lookup_type
AND ctt.default_status = arl_status.lookup_code
AND ctt.default_term = rat.term_id(+)
AND ‘INV/CM’ = arl_class.lookup_type
AND ctt.TYPE = arl_class.lookup_code
AND ctt.TYPE =’INV’ — Added condition to show only invoice transaction type
AND NVL (:tgw_header.trx_date, TRUNC (SYSDATE)) BETWEEN start_date
AND NVL (
end_date,
NVL (
:tgw_header.trx_date,
TRUNC(SYSDATE)
)
)
AND ctt.TYPE IN (‘DEP’, ‘GUAR’, ‘INV’, ‘CM’, ‘DM’)
AND (NVL (:tgw_header.ctt_class, ctt.TYPE) = ctt.TYPE
OR ( :tgw_header.ctt_class IN (‘DM’, ‘INV’)
AND:tgw_header.row_id IS NOT NULL
AND ctt.TYPE IN (‘DM’, ‘INV’)))
AND (:tgw_header.invoicing_rule_id IS NULL
OR ctt.TYPE IN (‘INV’, ‘CM’))
AND (ctt.TYPE NOT IN (‘DEP’, ‘GUAR’)
OR ctt.accounting_affect_flag = ‘Y’)
AND ctt.post_to_gl =
DECODE (:tgw_header.rev_recog_run_flag,
‘Y’, :tgw_header.ctt_post_to_gl_flag,
ctt.post_to_gl)
AND (NVL (:tgw_header.complete_flag, ‘N’) = ‘N’
OR:tgw_header.ctt_class != ‘CM’
OR (ctt.accounting_affect_flag =
:tgw_header.ctt_open_receivables_flag))
AND NOT EXISTS
(SELECT ‘violates allow freight’
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = :tgw_header.customer_trx_id
AND ( (ctt.allow_freight_flag = ‘N’
AND ctl.line_type = ‘FREIGHT’)
OR (ctl.line_type = ‘CHARGES’
AND ctt.TYPE NOT IN (‘DM’, ‘CM’))))
AND NOT EXISTS
( SELECT ‘VIOLATES CREATION SIGN’
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = :tgw_header.customer_trx_id
GROUP BY ctt.creation_sign
HAVING DECODE (
SIGN (SUM (ctl.extended_amount)),
1,
DECODE (ctt.creation_sign,
‘P’, ‘Y’,
‘A’, ‘Y’,
‘N’),
-1,
DECODE (ctt.creation_sign,
‘N’, ‘Y’,
‘A’, ‘Y’,
‘N’),
‘Y’
) = ‘N’)
ORDER BY ctt.name
4. Go to Receivable Manager –> Transactions –> Help –> Diagnostics –> Custom Code –> Personalize
5. Enter required information on the “Condition Tab”
Enter responsibility name if you want to user this personalization at responsibility level
6. Go to “Actions” tab and create new record group “XX_RA_TRANSACTION_TYPE_RG” using your custom query
7. Assign new created record group “XX_RA_TRANSACTION_TYPE_RG” to existing LOV “ARXTWTGW_TRANSACTION_TYPE” and save your work using apply button.
Close personalization form.
8. Now Re-open transaction form and review transaction type list of value.
Now your custom LOV is ready to go.
Use FNDLOAD and move your form personalization from your Dev instance to Prod instance.
=========
Keywords
=========
Form Personalization, Restrict transaction type on the transaction form, LOV customization
Superb……… explanation with screenshots