, , ,

Calling Stored procedure through Forms Personalization

Steps:
1. Personalize the form
2. Create an action of type “BuiltIn”
3. BuiltIn Type for Action should be “Execute a Procedure”
4. Argument should be as below
=’declare
v_field_value VARCHAR2(200) ;
begin
plsql_package.procedurenameHere ;
end’
or alternately
=’declare
v_field_value VARCHAR2(200) ;
begin
XX_PRC(”’||${item.PO_CONTROL_RULES.OBJECT_CODE_DISPLAYED_VALUE.value}||”’);
end’
Note the syntax, after =, entire declare begin end is within single quote.
Also, there is no semi colon after “end”
You can pass field values as
”’||${item.BLOCKNAME.FIELDNAME.value}||”’
e.g.
A simple way to understand is the begin end block should be enclosed in single quotes and the fields should be enclosed in double quotes.
=‘begin
test_procedure( “’||:GLOBAL.field||’”,”‘||${item.block.field.value}||’”);
end’
/by
, , ,

Submit the Concurrent Program from Backend

The following is the Sample Code to Submit the Concurrent Program from the backend.

Note:- This is the Concurrent Program, not the Request Set. To Submit the Request Set from the backend, We have different API.

I have already document, for submitting the request set from the backend in the Following URL.

http://oracleerpappsguide.blogspot.com/2011/09/registering-executable-concurrent.html

DECLARE
l_success NUMBER;
BEGIN
BEGIN

fnd_global.apps_initialize( user_id => 2572694, resp_id => 50407, resp_appl_id => 20003);

— If you are directly running from the database using the TOAD, SQL-NAVIGATOR or –SQL*PLUS etc. Then you need to Initialize the Apps. In this case use the above API to –Initialize the APPS. If you are using same code in some procedure and running directly
–from application then you don’t need to initalize.
–Then you can comment the above API.

l_success :=
fnd_request.submit_request
(‘XXAPP’, — Application Short name of the Concurrent Program.
‘XXPRO_RPT’, — Program Short Name.
‘Program For testing the backend Report’, — Description of the Program.
SYSDATE, — Submitted date. Always give the SYSDATE.
FALSE, — Always give the FLASE.
‘1234’ — Passing the Value to the First Parameter of the report.
);
COMMIT;

— Note:- In the above request Run, I have created the Report, which has one parameter.

IF l_success = 0
THEN
— fnd_file.put_line (fnd_file.LOG, ‘Request submission For this store FAILED’ );
DBMS_OUTPUT.PUT_LINE( ‘Request submission For this store FAILED’ );
ELSE
— fnd_file.put_line (fnd_file.LOG, ‘Request submission for this store SUCCESSFUL’);
DBMS_OUTPUT.PUT_LINE( ‘Request submission For this store SUCCESSFUL’ );
END IF;

—Note:- If you are running directly from database, use DBMS API to display. If you are 
— Running directly from Application, then Use the fnd_file API to write the message
— in the log file.

END;

/by
, , ,

Write message in Log or Out file

When ever, I have to create new package, I normally code a procedure to write any information in the log or Out files of the program.

The following is the Procedure which I use it in any package. This is standard in some companies coding. This save a lot of time and this makes easy to other people who goes through your program.

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : Procedure writes to the log file or output file
based on type.O=Output File, L=Log File
*************************************************************************/

BEGIN
IF p_type = ‘L’
THEN

fnd_file.put_line (fnd_file.log, p_message);

ELSIF p_type = ‘O’
THEN

fnd_file.put_line (fnd_file.output, p_message);

END IF;
END write;

The above write procedure can be used in other Procedure/Function in the package to write any information in the Log or Out files. 

PROCEDURE main(errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_par1 IN NUMBER
)
IS
v_errbuf VARCHAR2(1000) := NULL;
v_retcode NUMBER := 0;
v_file_name VARCHAR2(100);
BEGIN

v_retcode := 0;

v_file_name := fnd_profile.value(‘XYZ’);

IF v_file_name IS NULL
THEN
write(‘O’,’Profile XYZ is not defined or the value is not set’);
retcode := 2;
RETURN;
END IF;
END;

Note:- In the above Procedure, I am using the write Procedure and returning 2 for the retcode (0 – Complete, 1- Warning and 2 will be for Error).

Note:- This is one time process and you will realise, how much helpful it will be when ever you have to right something in log or out file.

More then information, this post is more of suggestion. Hope this post will help you make your code easy. 

/by
, , ,

Get the Modifier details of particular item (only at the Line Level)

When we are define Modifier in the OM module. We can define Modifier at the particular Item or all the Items in the Price List/Item Category etc.

Note:- Item Category is the Flex-field in the Inventory Module. In the Master Item define, we choose what item belongs to which Item Category.

The following query will give the Modifier Name and other details of Particular Item:-
————————————————————————————-

Note:- This is applicable only if the Item Number directly given at the Modifier Line Level.

SELECT distinct qlh.comments “Modifier Name”
,qqv.rule_name “Qualifier Group”
,ou.name “Store Id”
–,qlhv.name “Price list name”
,qms.product_attr_value “SKU”
,qms.list_line_no “Modifier Line No”
,qms.start_date_active “Start Date”
,qms.end_date_active “End Date”
,qms.arithmetic_operator_type “Application Method”
,qms.operand “Value”
,qms.product_precedence “Precedence”
,qms.incompatibility_grp “Incompatibility Group”
,qms.pricing_group_sequence “Bucket”
FROM 
qp_modifier_summary_v qms
, qp_list_headers_b qlh
,qp_list_headers_tl qlt
,qp_qualifiers_v qqv 
,mtl_system_items_b msi
,hr_all_organization_units ou
–,qp_list_headers_v qlhv 
WHERE 
qlh.list_header_id = qms.list_header_id 
–and qms.list_header_id=qlhv.list_header_id 
and qlh.list_header_id =qqv.list_header_id 
and to_char(msi.inventory_item_id)=qms.product_attr_val
AND ou.organization_id = msi.organization_id 
and to_char(ou.organization_id)= qqv.qualifier_attr_value 
and sysdate between qms.start_date_active and qms.end_date_active 
and qlt.LIST_HEADER_ID=qlh.LIST_HEADER_ID
AND exists
(select 1
from mtl_system_items_b a
where a.organization_id=(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
and to_char(a.inventory_item_id)=qms.product_attr_val 
and a.segment1 in(‘Your Item Name’))

/by
, , ,

To identify the items at Item Category level against active modifiers

When we are define Modifier in the OM module. We can define Modifier at the particular Item or all the Items in the Price List/Item Category etc.

Note:- Item Category is the Flex-field in the Inventory Module. In the Master Item define, we choose what item belongs to which Item Category.

The following query will give the Modifier Name and other details of Particular Item of Item Category:-
—————————————————————————————-

SELECT DISTINCT — qpa.list_header_id “Modifier Header ID”, 
qpa.list_line_id “Modifier Line Number”,
qlh.COMMENTS “Modifier Name (Description)”,
qll.start_date_active “Modifier Start Date”,
qll.end_date_active “Modifier End Date”,
b.segment1 “SKU”,
b.inventory_item_id “Inventory Item ID”,
qll.arithmetic_operator_type “Application Method”,
qll.operand “Value”, 
qll.product_precedence “Precedence”, 
qll.incompatibility_grp “Incompatibility Group”,
qll.pricing_group_sequence “Bucket”
FROM qp_pricing_attributes qpa,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_modifier_summary_v qll,
mtl_item_catalog_groups a,
mtl_system_items_b b,
mtl_descr_element_values c
–qp_qualifiers_v q
WHERE b.item_catalog_group_id = a.item_catalog_group_id
AND b.inventory_item_id = c.inventory_item_id
AND b.organization_id =
(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
AND c.element_sequence IN (’20’)
AND qq.list_header_id = qpa.list_header_id
AND qq.list_line_id = qpa.list_line_id
AND qq.qualifier_attribute = ‘QUALIFIER_ATTRIBUTE35’
AND qq.qualifier_attr_value =
a.segment1 || ‘-‘ || c.element_value 
AND qpa.product_attribute = ‘PRICING_ATTRIBUTE3’
AND qlh.list_header_id = qq.list_header_id
AND qpa.list_header_id = qll.list_header_id
AND qpa.list_line_id = qll.list_line_id
AND qll.list_header_id = qlh.list_header_id
AND qlh.active_flag = ‘Y’
AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND b.segment1 = ‘Your Item Name’

/by