, , , ,

Create directory in Oracle

Create directory in Oracle

create or replace directory foo_dir as ‘/tmp’;

Directories must be created if external tables are used.
Created directories are shown in either dba_directories or all_directories. There is no user_directories.

Privileges

When a «directory» has been created, the read and write object privileges can be granted on it:

create directory some_dir;
grant read, write on directory some_dir to micky_mouse;

An example

The following example shows how create directory and utl_file can be used to write text into a file:

create or replace directory dir_temp as ‘c:temp’;

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen(‘DIR_TEMP’, ‘something.txt’, ‘w’);
  utl_file.put_line(f, ‘line one: some text’);
  utl_file.put_line(f, ‘line two: more text’);
  utl_file.fclose(f);
end;
/

/by
,

Query to get the Number of Seconds or Minutes or Hours between 2 given days

Note:- I have prepared this query since I could not find any inbuilt functions or procedures in Oracle to get this. I had the requirement to treat 2 dates are same if differences is not more then 2 seconds.

For the Seconds:-
——————-

SELECT (‘Your first Date’-‘Your another Date’) * DECODE( UPPER(‘SS’)
, ‘SS’, 24*60*60 
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER(‘SS’)
, ‘SS’, 24*60*60
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

For Minutes:-
—————

SELECT (‘Your first Date’-‘Your another Date’) * DECODE( UPPER(‘MI’)
, ‘SS’, 24*60*60 
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER(‘MI’)
, ‘SS’, 24*60*60
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

For the Hours:-
—————–

SELECT (‘Your first Date’-‘Your another Date’) * DECODE( UPPER(‘HH’)
, ‘SS’, 24*60*60 
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER(‘HH’)
, ‘SS’, 24*60*60
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

This is the simple decode function made for all the above 3 requirements. I hope this will help you.
/by
,

Close Notifications

Lot of time we want to close the Notification which has come to the user.

They are many ways to do it. The following script can be use to Close the Notification.

/*********************************************
— Date ‘Current Date’
— Author JPREDDY
— Purpose : Close Notifications
**********************************************/
BEGIN 
wf_notification.RESPOND( 12504888, — Notification ID.
‘Close requested by on ‘||Sysdate, — Comments
‘IM12345’ ); — Incident Number. 
COMMIT;
END; 
/You can know the status of the Notification from the following Query.

select * from wf_notifications
where NOTIFICATION_ID=12504888

There is some equal-vent API which does this work.

wf_notification.close(12280094 — Notification ID
,’SYSADMIN’);
/by
, , ,

To identify the items at Pricelist 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.

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

SELECT DISTINCT qpa.list_header_id “Modifier Header ID”,
qlh.COMMENTS “Modifier Name (Description)”, 
qpa.list_line_id “Modifier Line ID”, 
qll.start_date_active “Modifier start date”,
qll.end_date_active “Modifier end date”,
qq.qualifier_attr_value “Price List ID”,
qllv.product_attr_value “Inventory Item ID”,
msi.segment1 “SKU”,
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,
mtl_system_items_b msi,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_list_lines_v qllv,
qp_modifier_summary_v qll
WHERE qpa.product_attribute = ‘PRICING_ATTRIBUTE3’
AND qq.qualifier_attribute = ‘QUALIFIER_ATTRIBUTE4’
AND qllv.product_attribute = ‘PRICING_ATTRIBUTE1’
AND qllv.product_attr_value = msi.inventory_item_id
AND qllv.list_header_id = qq.qualifier_attr_value
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 msi.segment1 = ‘Your Item Name’
AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND TRUNC (NVL (qllv.end_date_active, SYSDATE)) <= TRUNC (SYSDATE)
— AND rownum <=10

/by
, ,

Initialize Apps,

To run or execute the Oracle API from the back-end, we need to Initialize the Apps first.

We have API to Initialize the Apps.

To initialize apps using the API, we need to pass few IN parameter values.

The following is the script prepared to initialize apps based on the
Application, User-name and Responsibility name given.

Example:-

DECLARE
   l_appl_id             NUMBER;
   l_appl_name           VARCHAR2 (100) := 'PA';
   l_user_id             NUMBER;
   l_user_name           VARCHAR2 (100) := 'OPERATIONS';
   l_responsibility_id   NUMBER;
   l_resp_name           VARCHAR2 (200)
                                       := 'Projects, Vision Operations (USA)';
BEGIN

-- To get the Application ID of given Application.
   SELECT application_id
     INTO l_appl_id
     FROM fnd_application
    WHERE application_short_name = l_appl_name;

-- To get the User ID information of  given user
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

-- To get the Resp ID information of the given responsibility.
   SELECT responsibility_id
     INTO l_responsibility_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

--Initialixze the Application to use the API. 
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
END;

I Hope the above script would help understanding about the Initializing the Apps API.
/by