, , , , , , ,

Script to Get Concurrent Request Details

select
request_id, parent_request_id,
fcpt.user_concurrent_program_name Request_Name,
fcpt.user_concurrent_program_name program_name,
DECODE(fcr.phase_code,’C’,’Completed’,’I’, ‘Incactive’,’P’,’Pending’,’R’,’Running’) phase,
DECODE(fcr.status_code, ‘D’,’Cancelled’,’U’,’Disabled’,’E’,’Error’,’M’,’No Manager’,’R’,’Normal’,’I’, ‘Normal’,
‘C’,’Normal’,’H’,’On Hold’,’W’,’Paused’,’B’,’Resuming’,’P’,’Scheduled’,’Q’,’Standby’,’S’,
‘Suspended’,’X’,’Terminated’,’T’,’Terminating’,’A’,’Waiting’,’Z’,’Waiting’,’G’,’Warning’,’N/A’) status,
round((fcr.actual_completion_date – fcr.actual_start_date),3) * 1440 as Run_Time,
round(avg(round(to_number(actual_start_date – fcr.requested_start_date),3) * 1440),2) wait_time,
fu.User_Name Requestor,
fcr.argument_text parameters,
to_char (fcr.requested_start_date, ‘MM/DD HH24:mi:SS’) requested_start,
to_char(actual_start_date, ‘MM/DD/YY HH24:mi:SS’) ACT_START,
to_char(actual_completion_date, ‘MM/DD/YY HH24:mi:SS’) ACT_COMP,
fcr.completion_text
From
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
Where 1=1
— and fu.user_name = ‘JMOHANTY’
— and fcr.request_id = 1565261
— and fcpt.user_concurrent_program_name = ‘Autoinvoice Import Program’
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcpt.application_id
and fcr.requested_by = fu.user_id
and fcpt.language = ‘US’
and fcr.actual_start_date like sysdate
— and fcr.phase_code = ‘C’
— and hold_flag = ‘Y’
— and fcr.status_code = ‘C’
GROUP BY
request_id, parent_request_id, fcpt.user_concurrent_program_name,
fcr.requested_start_date, fu.User_Name, fcr.argument_text,
fcr.actual_completion_date, fcr.actual_start_date,
fcr.phase_code, fcr.status_code,fcr.resubmit_interval,fcr.completion_text,
fcr.resubmit_interval,fcr.resubmit_interval_unit_code,fcr.description
Order by 1 desc
/by
, , , , , , , , ,

Adding responsibility using fnd_user_pkg

Some times we don’t have the access to add the responsibility to the user using the the Create User form. So for this Oracle is having one API fnd_user_pkg.addresp which can do the job without using the Create User Form.

— R12 – FND – Script to add responsibility using fnd_user_pkg with validation

DECLARE

v_user_name VARCHAR2 (10) := ‘&Enter_User_Name’;
v_resp_name VARCHAR2 (50) := ‘&Enter_Existing_Responsibility_Name’;
v_req_resp_name VARCHAR2 (50) := ‘&Enter_required_Responsibility_Name’;
v_user_id NUMBER (10);
v_resp_id NUMBER (10);
v_appl_id NUMBER (10);
v_count NUMBER (10);
v_resp_app VARCHAR2 (50);
v_resp_key VARCHAR2 (50);
v_description VARCHAR2 (100);
RESULT BOOLEAN;

BEGIN

SELECT fu.user_id, frt.responsibility_id, frt.application_id
INTO v_user_id, v_resp_id, v_appl_id
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = ‘US’
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_resp_name;
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);

SELECT COUNT (*)
INTO v_count
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = ‘US’
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_req_resp_name;

IF v_count = 0 THEN

SELECT fa.application_short_name, frv.responsibility_key,
frv.description
INTO v_resp_app, v_resp_key,
v_description
FROM fnd_responsibility_vl frv, fnd_application fa
WHERE frv.application_id = fa.application_id
AND frv.responsibility_name = v_req_resp_name;

fnd_user_pkg.addresp (
username => v_user_name,
resp_app => v_resp_app,
resp_key => v_resp_key,
security_group => ‘STANDARD’,
description => v_description,
start_date => SYSDATE – 1,
end_date => NULL);

RESULT :=
fnd_profile.SAVE (x_name => ‘APPS_SSO_LOCAL_LOGIN’,
x_value => ‘BOTH’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘FND_CUSTOM_OA_DEFINTION’,
x_value => ‘Y’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘FND_DIAGNOSTICS’,
x_value => ‘Y’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘DIAGNOSTICS’,
x_value => ‘Y’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘FND_HIDE_DIAGNOSTICS’,
x_value => ‘N’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

DBMS_OUTPUT.put_line ( ‘The responsibility added to the user ‘
v_user_name
‘ is ‘
v_req_resp_name);

COMMIT;

ELSE

DBMS_OUTPUT.put_line
(‘The responsibility has already been added to the user’);

END IF;

END;

/by
, , , , , , , , ,

Use Of Pricing API (QP_PRICE_LIST_PUB.Process_Price_List)

Oracle Pricing provides the ability to allow for the import of large volumes of Price List data into the Pricing Tables.

The following two methods may be used to populate the price list.

A) QP: Bulk Import of Price List

B) Pricing API (QP_PRICE_LIST_PUB.Process_Price_List).

Price List Setup API.(QP_PRICE_LIST_PUB.Process_Price_List):
=================================================The Price List Setup package consists of entities to set up price lists.

The Price List Setup package QP_Price_List_PUB.Process_Price_List contains the following public record type and table of records entities:

  • Process_Price_List: QP_Price_List_PUB.Process_Price_List:.Takes two record types and six table types as input parameters. Use this API to insert, update, and delete price lists and to set up a price list for a given P_PRICE_LIST_REC record structure.

We can use the API in the following mentioned way:

  • Set up multiple price list lines by giving multiple price list line definitions in the P_ PRICE_LIST_LINE_TBL table structure.

  • Attach multiple qualifiers at the price list header level by giving multiple qualifiers in the P_QUALIFIERS_TBL table structure.

  • Attach multiple pricing attributes to price list lines by giving the pricing attributes in the P_PRICING_ATTR_TBL table structure.

  • Price_List_Rec_Type: Corresponds to the columns in the price list header tables QP_ LIST_HEADERS_B and QP_LIST_HEADERS_TL.

  • Price_List_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list header table QP_LIST_HEADERS_B, for example, Currency.

  • Price_List_Line_Rec_Type: Corresponds to columns in the price list line table and related modifiers tables QP_LIST_LINES and QP_RLTD_MODIFIERS.

  • Price_List_Line_Tbl_Type: Table of Price_List_Line_Rec_Type.

  • Price_List_Line_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list line table QP_LIST_LINES, for example, Price_By_Formula.

  • Price_List_Line_Val_Tbl_Type: Table of Price_List_Line_Val_Rec_Type.

  • Qualifiers_Rec_Type: Corresponds to the columns in the qualifier table QP_QUALIFIERS.

  • Qualifiers_Tbl_Type: Table of Qualifiers_Rec_Type.

  • Qualifiers_Val_Rec_Type: Made up of attributes that store the meaning of id or code columns in the qualifiers table QP_QUALIFIERS, for example, Qualifier_Rule.

  • Qualifiers_Val_Tbl_Type: Table of Qualifiers_Val_Rec_Type.

  • Pricing_Attr_Rec_Type: Corresponds to the columns in the pricing attributes table QP_ PRICING_ATTRIBUTES.

  • Pricing_Attr_Tbl_Type: Table of Pricing_Attr_Rec_Type.

  • Pricing_Attr_Val_Rec_Type: Attributes that store the meaning of id or code columns in the pricing attributes table QP_PRICING_ATTRIBUTES, for example, Accumulate.

  • Pricing_Attr_Val_Tbl_Type: Table of Pricing_Attr_Val_Rec_Type.

— R12 – OM – Script to insert Item into pricelist using qp_price_list_pub API
DECLARE

gpr_return_status VARCHAR2 (1) := NULL;
gpr_msg_count NUMBER := 0;
gpr_msg_data VARCHAR2 (2000);
gpr_price_list_rec qp_price_list_pub.price_list_rec_type;
gpr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
gpr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
gpr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
gpr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
gpr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
gpr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
gpr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
k NUMBER := 1;
j NUMBER := 1;
BEGIN
— INITIALIZATION REQUIRED FOR R12
mo_global.set_policy_context (‘S’, 308);
mo_global.init(‘ONT’);
fnd_global.apps_initialize (user_id => 2083,
resp_id => 21623,
resp_appl_id => 660);
gpr_price_list_rec.list_header_id := 33019; — Enter the list_header_id from qp_list_headers
gpr_price_list_rec.NAME := ‘TST_PRICE_LIST’; — Enter the price list name
gpr_price_list_rec.list_type_code := ‘PRL’;
gpr_price_list_rec.description := ‘TEST PRICE LIST’; –Enter the price list Description
gpr_price_list_rec.operation := qp_globals.g_opr_update;
k := 1; — create the price list line rec

gpr_price_list_line_tbl (k).list_header_id := 33019; — Enter the list_header_id from qp_list_headers
gpr_price_list_line_tbl (k).list_line_id := fnd_api.g_miss_num;
gpr_price_list_line_tbl (k).list_line_type_code := ‘PLL’;
gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
gpr_price_list_line_tbl (k).operand := 10; –Enter the Unit Price
gpr_price_list_line_tbl (k).arithmetic_operator := ‘UNIT_PRICE’;
j := 1;
gpr_pricing_attr_tbl (j).pricing_attribute_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl (j).list_line_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl (j).product_attribute_context := ‘ITEM’;
gpr_pricing_attr_tbl (j).product_attribute := ‘PRICING_ATTRIBUTE1’;
gpr_pricing_attr_tbl (j).product_attr_value := ‘102785’; — Enter the inventory_item_id
gpr_pricing_attr_tbl (j).product_uom_code := ‘EA’; — Enter the UOM
gpr_pricing_attr_tbl (j).excluder_flag := ‘N’;
gpr_pricing_attr_tbl (j).attribute_grouping_no := 1;
gpr_pricing_attr_tbl (j).price_list_line_index := 1;
gpr_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;

dbms_output.put_line(‘Calling qp_price_list_pub.process_price_list API to Enter Item Into Price List’);
dbms_output.put_line(‘=============================================’);
qp_price_list_pub.process_price_list
(p_api_version_number => 1,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => gpr_return_status,
x_msg_count => gpr_msg_count,
x_msg_data => gpr_msg_data,
p_price_list_rec => gpr_price_list_rec,
p_price_list_line_tbl => gpr_price_list_line_tbl,
p_pricing_attr_tbl => gpr_pricing_attr_tbl,
x_price_list_rec => ppr_price_list_rec,
x_price_list_val_rec => ppr_price_list_val_rec,
x_price_list_line_tbl => ppr_price_list_line_tbl,
x_price_list_line_val_tbl => ppr_price_list_line_val_tbl,
x_qualifiers_tbl => ppr_qualifiers_tbl,
x_qualifiers_val_tbl => ppr_qualifiers_val_tbl,
x_pricing_attr_tbl => ppr_pricing_attr_tbl,
x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl);
IF ppr_price_list_line_tbl.count > 0 THEN
FOR k in 1 .. ppr_price_list_line_tbl.count
LOOP
dbms_output.put_line(‘No Of Record Got Insterted=> ‘ k);
dbms_output.put_line(‘Return Status = ‘ ppr_price_list_line_tbl(k).return_status);
END LOOP;
END IF;
IF ppr_price_list_line_tbl(k).return_status = fnd_api.g_ret_sts_success THEN

Commit;
DBMS_OUTPUT.put_line (‘The Item has been successfully loaded into the price list’);
Else
Rollback;
DBMS_OUTPUT.put_line (‘The Item has not been loaded into the price list’);
end if;
FOR k in 1 .. gpr_msg_count
LOOP
gpr_msg_data := oe_msg_pub.get(
p_msg_index => k,
p_encoded => ‘F’);
dbms_output.put_line(‘The Error Message Due to which The Item has not been loaded to Price List ‘ k ‘ is: ‘ gpr_msg_data);
END LOOP;
END;
— R12 – OM-Script to delete item from Price list using QP_PRICE_LIST_PUB API
DECLARE

gpr_return_status VARCHAR2 (1) := NULL;
gpr_msg_count NUMBER := 0;
gpr_msg_data VARCHAR2 (2000);
gpr_price_list_rec qp_price_list_pub.price_list_rec_type;
gpr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
gpr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
gpr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
gpr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
gpr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
gpr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
gpr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
k NUMBER := 1;
j NUMBER := 1;

BEGIN

oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (5);
oe_msg_pub.initialize;

DBMS_OUTPUT.put_line ( ‘Debug File = ‘ oe_debug_pub.g_dir ‘/’ oe_debug_pub.g_file);
— setup the list_header rec for update

gpr_price_list_rec.list_header_id := 33019; — Price List Header Id (List_header_id)
gpr_price_list_rec.NAME := ‘TST_PRICE_LIST’; — Price List Name
gpr_price_list_rec.list_type_code := ‘PRL’;
gpr_price_list_rec.description := ‘TEST PRICE LIST’; — Price List Description
gpr_price_list_rec.operation := qp_globals.g_opr_update;
— delete the price list line rec

gpr_price_list_line_tbl (k).list_header_id := 33019; — Price List Header Id (List_header_id)
gpr_price_list_line_tbl (k).list_line_id := 2003808; — Price List Line Id (List_Line_id)
gpr_price_list_line_tbl (k).list_line_type_code := ‘PLL’;
gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_delete;
DBMS_OUTPUT.put_line(‘Calling qp_price_list_pub.process_price_list API to Delete Item From Price List’);
DBMS_OUTPUT.put_line(‘==============================================’);
qp_price_list_pub.process_price_list
(p_api_version_number => 1,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => gpr_return_status,
x_msg_count => gpr_msg_count,
x_msg_data => gpr_msg_data,
p_price_list_rec => gpr_price_list_rec,
p_price_list_line_tbl => gpr_price_list_line_tbl,
p_pricing_attr_tbl => gpr_pricing_attr_tbl,
x_price_list_rec => ppr_price_list_rec,
x_price_list_val_rec => ppr_price_list_val_rec,
x_price_list_line_tbl => ppr_price_list_line_tbl,
x_price_list_line_val_tbl => ppr_price_list_line_val_tbl,
x_qualifiers_tbl => ppr_qualifiers_tbl,
x_qualifiers_val_tbl => ppr_qualifiers_val_tbl,
x_pricing_attr_tbl => ppr_pricing_attr_tbl,
x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl);

IF ppr_price_list_line_tbl.COUNT > 0 THEN

FOR k IN 1 .. ppr_price_list_line_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line(‘No Of Record Got Deleted=> ‘ k);
DBMS_OUTPUT.put_line(‘Return Status = ‘ ppr_price_list_line_tbl(k).return_status);
END LOOP;
END IF;
IF gpr_return_status = fnd_api.g_ret_sts_success THEN
Commit;
DBMS_OUTPUT.put_line (‘The Item Has Been Successfully Deleted from The Price List Using API’);
Else
Rollback;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
FOR k IN 1 .. gpr_msg_count
LOOP
gpr_msg_data := oe_msg_pub.get (
p_msg_index => k,
p_encoded => ‘F’);
DBMS_OUTPUT.put_line (‘The Error Message’ k ‘ is: ‘ gpr_msg_data);
NULL;
END LOOP;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
gpr_return_status := fnd_api.g_ret_sts_error;
WHEN fnd_api.g_exc_unexpected_error THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
FOR k IN 1 .. gpr_msg_count LOOP
gpr_msg_data := oe_msg_pub.get (
p_msg_index => k,
p_encoded => ‘F’);
DBMS_OUTPUT.put_line (‘The Error Message’ k ‘ is: ‘ gpr_msg_data);
NULL;
END LOOP;
WHEN OTHERS THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
END;

/by
, , , , ,

Purchasing System Configuration

Normally while converting legacy systems to oracle, or implementing a new brand oracle ERP to a company. There are lot of steps and set ups involved for each and every module.

Let us consider for an example we are configuring the PURCHASING system, there are some specific setups which need to be be in place to have the PURCHASING module to work as expected.

Attached is the document which let you know the set up check list for the PURCHASING.

/by
, , , , , , , , , ,

Consigned POs not getting converted with the consigned flag checked through PDOI & Coming to the base tables with Incomplete Status

While trying to import the Consigned Purchase Orders using Standard Purchase Order Import Process, the Consigned Flag is not updated to Yes in the Purchase Order Shipment due to which the PO is coming to the system in INCOMPLETE status rather than APPROVED status.

If we will try to approve the PO in the form , then we will receive the following Error.

“Error: Line #1 Schedule # 1: The consigned setting on the shipment line does not match the consigned attribute on the ASL. The shipment line must be deleted and re-entered.”

The reason why the system is throwing the error while approving the PO is:

Approved Supplier List (ASL) for the item has the “Consigned from Supplier Flag” checked, while the corresponding PO Shipments does not have the Consigned Flag checked.
If the ASL / Item is consigned enabled, the Consigned Attributes must be passed on to the PO Shipment Line for the error to suppress

To Approve the PO, we need to perform either of the following Option.

– Deselect the “Consigned from Supplier” checkbox for the ASL if Consigned Inventory is not desired for the Supplier. (Check for the consigned_from_supplier_flag in po_asl_attributes table)

OR

– Ensure the Consigned flag is checked on the PO Shipment. (consigned_flag in po_line_locations_all should be set to Y).

What is the Solution if we need to import Consigned Purchase Orders in Bulk into the system.
We can not manully go & approve the PO one by one after taking care of the above 2 points.

As Per Oracle;

Is it possible to use the consigned flag while importing Purchase Orders ? — No, This functionality is not yet available.

Enhancement Request Bug 4261977 has been opened to request that this functionality and to be considered for inclusion in future versions of the application

Workaround which can be used to Import Consigned Purchase Orders:

  • Import the Purchase Order in Incomplete Status to the System using PDOI.
  • In the po_line_locations_all table, the consigned_flag should be NULL for these POs. We need to update this flag to Y. (update po_line_locations_all set consigned_flag = ‘Y’ where line_location_id = ***)
  • Need to call the PO approval API [po_reqapproval_init1.start_wf_process] so as to approve the PO.
/by