Wednesday, October 7, 2009
Tuesday, October 6, 2009
How to set the payment file out directory and payment file suffix for EFT payments in Payments Module
How to to specify the output location and the payment file suffix for EFT bank files. This is a fairly simple and common requirement for EFT bank files generated out of iPayments module in E-Business.
The following is the navigation path to set the same:
Oracle Payments Setup -> Payment Process Profiles -> Payment File Information section
The fields Outbound Payment File Prefix, Outbound Payment File Extension and Outbound Payment File Directory allow you to configure through setup
Monday, October 5, 2009
RE: How to use CUSTOM.PLL to change list of values in an Oracle applications form
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.
Sunday, October 4, 2009
Friday, October 2, 2009
Oracle Open World 2009
Oracle Open World 2009 is coming up in San Francisco in October
Looking at the hands-on events, some of the products in focus seem to be ODI - Oracle Data Integrator - which is going to be the tool for data integration from Oracle's viewpoint. It uses a principle of EL-T (Extract Load Transform) instead of ETL (Extract-Load-Transform). Big deal one might say, but since it is the future path, it would pay to listen.
Other areas in focus:
BI - OBIEE
SOA and Fusion Middleware
Join the Open World Linked In group
Looking at the hands-on events, some of the products in focus seem to be ODI - Oracle Data Integrator - which is going to be the tool for data integration from Oracle's viewpoint. It uses a principle of EL-T (Extract Load Transform) instead of ETL (Extract-Load-Transform). Big deal one might say, but since it is the future path, it would pay to listen.
Other areas in focus:
BI - OBIEE
SOA and Fusion Middleware
Join the Open World Linked In group
Thursday, October 1, 2009
Subscribe to:
Posts (Atom)
