Learn Oracle ebs R12: November 2022

API to create and update sale order price list in oracle apps

In this post , we are going to write a PLSQL code and oracle API to Create and update sale order price list in oracle apps. Using this Below script you are Able to Create and Update Sale Orders Prices List in Oracle Apps.

CREATE OR REPLACE PROCEDURE APPS.xx_pricelist ( p_list_header_id IN NUMBER, p_operand IN NUMBER, p_product_attr_value IN VARCHAR2, p_uom IN VARCHAR2 ) IS v_return_status VARCHAR2 (1) := NULL; v_msg_count NUMBER := 0; v_msg_data VARCHAR2 (2000); v_price_list_rec qp_price_list_pub.price_list_rec_type; v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; v_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; BEGIN v_price_list_rec.list_header_id := p_list_header_id; v_price_list_rec.list_type_code := 'PRL'; v_price_list_rec.operation := qp_globals.g_opr_update; v_price_list_line_tbl (1).list_header_id := p_list_header_id; v_price_list_line_tbl (1).list_line_id := fnd_api.g_miss_num; v_price_list_line_tbl (1).list_line_type_code := 'PLL'; v_price_list_line_tbl (1).operation := qp_globals.g_opr_create; v_price_list_line_tbl (1).operand := p_operand; v_price_list_line_tbl (1).arithmetic_operator := 'UNIT_PRICE'; -- v_price_list_line_tbl (1).start_date_active := '05-OCT-2011'; -- v_price_list_line_tbl (1).start_date_active := '05-OCT-2011'; -- v_price_list_line_tbl (1).organization_id := NULL; v_pricing_attr_tbl (1).pricing_attribute_id := fnd_api.g_miss_num; v_pricing_attr_tbl (1).list_line_id := fnd_api.g_miss_num; ---- v_pricing_attr_tbl (1).product_attribute_context := 'Item'; -- v_pricing_attr_tbl (1).product_attribute := 'Item Number'; v_pricing_attr_tbl (1).product_attribute_context := 'ITEM'; v_pricing_attr_tbl (1).product_attribute := 'PRICING_ATTRIBUTE1'; v_pricing_attr_tbl (1).product_attr_value := p_product_attr_value; v_pricing_attr_tbl (1).product_uom_code := p_uom; v_pricing_attr_tbl (1).excluder_flag := 'N'; v_pricing_attr_tbl (1).attribute_grouping_no := 1; v_pricing_attr_tbl (1).price_list_line_index := 1; v_pricing_attr_tbl (1).operation := qp_globals.g_opr_create; DBMS_OUTPUT.put_line ('Calling API to insert Price List'); qp_price_list_pub.process_price_list (p_api_version_number => 1, p_init_msg_list => fnd_api.g_true, p_return_values => fnd_api.g_false, p_commit => fnd_api.g_false, x_return_status => v_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, p_price_list_rec => v_price_list_rec, p_price_list_line_tbl => v_price_list_line_tbl, p_pricing_attr_tbl => v_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_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, x_price_list_line_val_tbl => ppr_price_list_line_val_tbl ); IF v_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull'); ELSE DBMS_OUTPUT.put_line ('The Item loading into the price list Failed'); ROLLBACK; FOR i IN 1 .. v_msg_count LOOP v_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F'); DBMS_OUTPUT.put_line (i || ') ' || v_msg_data); END LOOP; END IF; END; /

After this you need to create one custom table

CREATE TABLE APPS.XXPRICE_LST_TBL ( ORG_ID NUMBER, PRICE_LIST VARCHAR2(400 BYTE), ITEM_CODE VARCHAR2(400 BYTE), ATTR_VALUE NUMBER, STATUS VARCHAR2(40 BYTE), ERR_MSG VARCHAR2(400 BYTE), ENTRY_ID NUMBER )

This table will store all information of Sale order item and which price list will be assigned and what rate will be provided to this item. So upload all the price list data in this staging table.

Then you need to create this final procedure to load the price list data from staging table to oracle standard price list base tables.

CREATE OR REPLACE PROCEDURE APPS.xx_plist_proc IS CURSOR plist_info_stg IS SELECT * FROM XXPRICE_LST_TBL WHERE NVL (status, 'N') <> 'P'; v_status VARCHAR2 (1); v_err_msg VARCHAR2 (100); v_item_id NUMBER; v_primary_uom_code VARCHAR2 (3); v_list_header_id NUMBER; BEGIN FOR z1 IN plist_info_stg LOOP v_status:= 'P'; v_err_msg := null; BEGIN SELECT inventory_item_id, primary_uom_code INTO v_item_id, v_primary_uom_code FROM mtl_system_items_b msi WHERE segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5 = z1.item_code AND msi.organization_id = z1.org_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_status := 'E'; v_err_msg := 'Inventory Item ID not found'; END; BEGIN SELECT list_header_id INTO v_list_header_id FROM qp_list_headers_tl WHERE NAME = z1.price_list; EXCEPTION WHEN NO_DATA_FOUND THEN v_status := 'E'; v_err_msg := 'Price list ID not found'; END; IF v_item_id IS NOT NULL AND v_primary_uom_code IS NOT NULL AND v_list_header_id IS NOT NULL THEN xx_pricelist (v_list_header_id, to_number(z1.attr_value), v_item_id, v_primary_uom_code ); END IF; UPDATE XXPRICE_LST_TBL SET status= v_status, err_msg = v_err_msg WHERE entry_id = z1.entry_id; COMMIT; END LOOP; END; /

After this just execute this above procedure and all the data will be uploaded in Oracle.

Query to find Legal Entity and its child organizations

The following Query will return the Legal Entity and its Child Organizations, with Org id, org code, org name, operating unit name, set of books, Ledger, legal entity name:

Select a.organization_id, a.organization_code, a.organization_name name,
a.operating_unit, b.name OU, a.set_of_books_id,d.name LEDGER,
a.legal_entity,c.name LE_NAME
From apps. ORG_ORGANIZATION_DEFINITIONS a,
apps. HR_OPERATING_UNITS b,
apps. xle_entity_profiles c,
apps. gl_ledgers d
Where a.operating_unit=b.organization_id
AND c.legal_entity_id=a.legal_entity
AND d.ledger_id=a.set_of_books_id
--and a.organization_id = a.legal_entity
and b.organization_id in (24157);

Queries to find Module wise Last transaction in Oracle Apps R12

Need to find the last transaction of a module for License renewal purpose.

Inventory

The following SQL statement return the date and the organization name:

select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name
from mtl_material_transactions a, HR_ORGANIZATION_UNITS b
where a.organization_id= b.organization_id
and a.org_id in (&org_id is required) GROUP BY b.name ) GROUP BY name;

iProcurement

The following SQL statement return the date and the organization name:

select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from po_requisition_headers_all a, HR_OPERATING_UNITS b where a.org_id in (&org_id is required) and apps_source_code = 'POR' AND AUTHORIZATION_STATUS = 'APPROVED' AND TYPE_LOOKUP_CODE = 'PURCHASE' and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name;

Oracle Sourcing for Oracle Purchasing

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from PON_AUCTION_HEADERS_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and aution_status = 'ACTIVE'
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle Project Costing

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from PA_TRANSACTION_INTERFACE_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and aution_status = 'ACTIVE'
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle Cash Management

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from ar_cash_receipts_all a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle Accounts Payable

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from AP_INVOICES_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle Fixed Assets

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from FA_BOOK_CONTROLS a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle Order Management

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from oe_order_headers_all a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle iExpense

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from AP_EXPENSE_REPORT_HEADERS_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle Procurement

The following SQL statement return the date and the organization name:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from PO_HEADERS_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

Oracle ebs R12 password reset from frontend

logon to Oracle Apps

from Menu

Security >> Users >> Define

search for user

Press <F11> enter user name

Press <Ctrl + F11> to execute query

the user info is displayed

in password field enter new password and press <Tab>

the cursor will be in password field enter new password again

now save the record .

Oracle EBS Active Users/Employee Details and their Active responsibilities find Query

The following SQL statement return the user name, employee name, email address, responsibility, user responsibility start and end dates:

SELECT fu.user_name, PAPF.FIRST_NAME||PAPF.LAST_NAME Employee_Name,
fu.EMAIL_ADDRESS User_Attached_Email, papf.email_address Emp_attached_eamil,
frv.responsibility_name, frv.responsibility_key, TO_CHAR (furgd.start_date, ‘DD-MON-YYYY’) “User_Resp_START_DATE”, TO_CHAR (furgd.end_date, ‘DD-MON-YYYY’) “User_resp_END_DATE”, fu.start_date User_start_date, fu.end_date User_End_date,
frv.start_date resp_start_date, frv.End_date resp_End_date, PAPF.effective_start_date Emp_start_date,
PAPF.EFFECTIVE_END_DATE Emp_end_date
FROM apps.fnd_user fu, apps.fnd_user_resp_groups_direct furgd,
apps.fnd_responsibility_vl frv, APPS.PER_ALL_PEOPLE_F PAPF
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frv.responsibility_id
and upper(frv.RESPONSIBILITY_NAME) like ‘%RESPONSIBILITY NAME IN CAPS%’
and fu.EMPLOYEE_ID =PAPF.PERSON_ID(+) — IF USER IS AN EMPLOYEE THEN EMPLOYEE DETAILS
AND SYSDATE BETWEEN nvl(PAPF.effective_start_date,sysdate) AND nvl(PAPF.EFFECTIVE_END_DATE,sysdate) — EMP ACTIVE CONDITION
AND SYSDATE BETWEEN nvl(furgd.start_date,sysdate) AND nvl(furgd.end_date,sysdate) — USER RESPONSIBILITIES ACTIVE CONDITION
AND SYSDATE BETWEEN nvl(fu.start_date,sysdate) AND nvl(fu.end_date,sysdate) — USER ACTIVE CONDITION
AND SYSDATE BETWEEN nvl(frv.start_date,sysdate) AND nvl(frv.end_date,sysdate) — RESPONSIBILITY ACTIVE CONDITION
order by fu.user_name,frv.responsibility_name;

Search This Blog

API to create and update sale order price list in oracle apps

API to create and update sale order price list in oracle apps

After this you need to create one custom table

CREATE TABLE APPS.XXPRICE_LST_TBL ( ORG_ID NUMBER, PRICE_LIST VARCHAR2(400 BYTE), ITEM_CODE VARCHAR2(400 BYTE), ATTR_VALUE NUMBER, STATUS VARCHAR2(40 BYTE), ERR_MSG VARCHAR2(400 BYTE), ENTRY_ID NUMBER )

After this just execute this above procedure and all the data will be uploaded in Oracle.

Query to find Legal Entity and its child organizations

Query to find Legal Entity and its child organizations

Queries to find Module wise Last transaction in Oracle Apps R12

Inventory

iProcurement

Oracle Sourcing for Oracle Purchasing

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from PON_AUCTION_HEADERS_ALL a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and aution_status = 'ACTIVE' and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle Project Costing

The following SQL statement return the date and the organization name:

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from PA_TRANSACTION_INTERFACE_ALL a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and aution_status = 'ACTIVE' and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle Cash Management

The following SQL statement return the date and the organization name:

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from ar_cash_receipts_all a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle Accounts Payable

The following SQL statement return the date and the organization name:

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from AP_INVOICES_ALL a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle Fixed Assets

The following SQL statement return the date and the organization name:

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from FA_BOOK_CONTROLS a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle Order Management

The following SQL statement return the date and the organization name:

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from oe_order_headers_all a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle iExpense

The following SQL statement return the date and the organization name:

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from AP_EXPENSE_REPORT_HEADERS_ALL a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle Procurement

The following SQL statement return the date and the organization name:

select max(last_update_date), namefrom (select max(a.last_update_date) last_update_date, b.name from PO_HEADERS_ALL a, HR_ORGANIZATION_UNITS bwhere a.org_id in (&org_id) and a.org_id= b.organization_idGROUP BY b.name) GROUP BY name;

Oracle ebs R12 password reset from frontend

Oracle ebs R12 password reset from frontend

Oracle EBS Active Users/Employee Details and their Active responsibilities find Query

Oracle EBS Active Users/Employee Details and their Active responsibilities find Query

The following SQL statement return the user name, employee name, email address, responsibility, user responsibility start and end dates:

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from PON_AUCTION_HEADERS_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and aution_status = 'ACTIVE'
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from PA_TRANSACTION_INTERFACE_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and aution_status = 'ACTIVE'
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from ar_cash_receipts_all a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from AP_INVOICES_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from FA_BOOK_CONTROLS a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from oe_order_headers_all a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from AP_EXPENSE_REPORT_HEADERS_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;

select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from PO_HEADERS_ALL a, HR_ORGANIZATION_UNITS b
where a.org_id in (&org_id)
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;