OracleApps

Item Orderability Rule in Order Management

Item orderability is a new feature that Oracle introduced in order management release 12.1. Based on these rules, a user can restrict items that can be entered on sales order by manual or EDI, XML, Etc.

Setups

Ø Define an Orderability Rule

Ø Set the Profile OM: Use Materialized View for Items LoV

Ø Run Refresh Order Management Materialized Views

Step 1) Define an Orderability Rule

Navigate to OM>Setup>Rules>Item Orderability

# Select an Operating Unit

# you can set this rule at item level or Category Level

# Check Enabled to enable this rule

# If Generally available is ticked, then item is available to all except the criteria mentioned in rule.

and if Generally Available is not ticked then item is restricted to all, except the criteria mentioned in the rule.

Here in above example, the item is available to all customers except to "w_Enterprise Builders"

# Rule Level

There are 11 Levels at which you can set the item Orderability Rule,

CUSTOMER
CUSTOMER CLASS
CUSTOMER CATEGORY
END CUSTOMER
ORDER TYPE
SALES CHANNEL
SALES PERSON
SHIP TO LOCATION
BILL TO LOCATION
DELIVER TO LOCATION
REGIONS

Step 2) Set the Profile OM: Use Materialized View for Items LoV

If this profile option is set to "Yes", then sales order form will display the items which order not restricted by Item Orderability Rules

If this is set to NO, then system display all items but give a note when saving the sales order.

Its better to set this profile option as Yes

Step 3) Run "Refresh Order Management Materialized Views"

If the profile OM: Use Materialized View for Items LoV is set to Yes, the concurrent request "Refresh Order Management Materialized Views" needs to be run in order to refresh OM materialized views OE_ITEMS_ORD_MV, OE_ITEMS_MV.

The concurrent request needs to be run after changes are made to the item master, cross reference, Customer Items or to the item orderability rules, to ensure that the item list of values displays the latest items on the order entry form, based on orderability rules.

Script to find Oracle API's for any module

select substr(a.OWNER,1,20)

, substr(a.NAME,1,30)

, substr(a.TYPE,1,20)

, substr(u.status,1,10) Stat

, u.last_ddl_time

, substr(text,1,80) Description

from dba_source a, dba_objects u

WHERE 2=2

and u.object_name = a.name

and a.text like '%Header%'

and a.type = u.object_type

and a.name like 'AP_%API%' –- Checking for AP Related APIs

order by

a.owner, a.name

Oracle Apps Stuff & Imp Queries

http://oracleebspro.blogspot.com/2013/04/important-apps-queries.html

Thanks to Girish... :-)

Oracle Apps API's

API'S
=====
Oracle AP Invoice API's
Oracle AP Supplier API's
Oracle External Bank Payment API's
Oracle FND User API's
Oracle HRMS API's

https://blogs.oracle.com/prajkumar/entry/oracle_hrms_apis

Thanks to Punith RajKumar... :-)

Following query gives Operating Unit Information and corresponding Inventory Orgs related information as well

SELECT hou.NAME operating_unit_name,
hou.short_code,
hou.organization_id operating_unit_id,
hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code,
ood.organization_id Inv_organization_id,
ood.chart_of_accounts_id
FROM hr_operating_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC

Send a mail using Oracle's Utility UTL_SMTP

Below is the sample code to send a mail using Oracle's Utility UTL_SMTP.....
create or replace procedure xxsammail as
    L_recipient VARCHAR2 (80) := 'vvydhyal@cisco.com';
    L_from VARCHAR2 (80) := 'vvydhyal@cisco.com';
    L_mail_host VARCHAR2 (30) := 'mailman.cisco.com';
    L_subject VARCHAR2 (80) := 'This is a Test Mail';
    L_mail_conn UTL_SMTP.connection;
begin
     L_mail_conn := UTL_SMTP.open_connection (L_mail_host, 25);
     UTL_SMTP.helo (L_mail_conn, L_mail_host);
     UTL_SMTP.mail (L_mail_conn, L_from);
     UTL_SMTP.rcpt (L_mail_conn, L_recipient);
     --UTL_SMTP.DATA (L_mail_conn,'Hellllooooo');
     utl_smtp.data(L_mail_conn,'From: Oracle Database'||utl_tcp.crlf ||
                                          'To: '||L_recipient||utl_tcp.crlf||
                                          'Subject: '||L_subject||utl_tcp.crlf||L_message);
      UTL_SMTP.quit (L_mail_conn);
exception
    when others then
         dbms_output.put_line('Error : ' ||sqlerrm);
end;

--------------------================================
begin
    xxsammail;
exception
    when others then
        dbms_output.put_line('Error : ' ||sqlerrm);
end;

Multicolumn Subqueries

We can compare elements having multiple values with the set of elements. This is called a multicolumn subquery.

CREATE TABLE EMPDEP(EMPNO NUMBER, DEPTNO NUMBER,SALARY NUMBER);

CREATE TABLE EMPCITY(EMPNO NUMBER, DEPTNO NUMBER,CITY VARCHAR2(30));

INSERT INTO EMPDEP VALUES (1,10,1000);

INSERT INTO EMPDEP VALUES (2,20,2000);

INSERT INTO EMPDEP VALUES (3,30,3000);

INSERT INTO EMPCITY VALUES (1,10,'NAGPUR');

INSERT INTO EMPCITY VALUES (2,20,'DELHI');

INSERT INTO EMPCITY VALUES (3,30,'NAGPUR');

SELECT * FROM EMPDEP

EMPNO DEPTNO SALARY
1 10 1000
2 20 2000
3 30 3000

SELECT * FROM EMPCITY

EMPNO DEPTNO CITY
1 10 NAGPUR
2 20 DELHI
3 30 NAGPUR

SELECT E1.EMPNO, E1.DEPTNO,E1.SALARY
FROM EMPDEP E1 WHERE (E1.EMPNO,E1.DEPTNO)
IN (SELECT E2.EMPNO,E2.DEPTNO FROM EMPCITY E2 WHERE CITY = 'NAGPUR')

EMPNO DEPTNO SALARY
1 10 1000
3 30 3000