, , ,

Purchase Orders Updated on previous day

You can use the following query to get all the Purchasing Orders upadetd on previous day in any Instance.

You can filter-out based on category set or Inventory Organization etc.
I have commented out category_set_id and Organization ID conditions in the where clause.
You can modify as per your requirement.

Note: – This kind of information would be required for the Organization (Management) for the tracking the PO’s created on daily bases.

Note:- There are some values I am getting from attribute columns. This values would vary in your Instance. These values depends on the DFF setup in your Instance.

SELECT mcb.segment1 “GMA”
, mcb.segment2 “Group”
, mcb.segment3 “Dept”
, mcb.segment4 “Cat” 
, poh.segment1 “PO”
, fu.user_name “PO Created by(User_id)”
, fu.description “PO Created by(Name)”
, fu2.user_name “Last Updated By(User id)”
, fu2.description “Last Updated By(Name)”
, vs.vendor_site_code
, vn.vendor_name
, hou1.name
, hrl1.location_code “Ship To”
, poh.authorization_status “PO status”
, msi.segment1 “SKU”
, msi.description “Item Description”
, pll.quantity
, pll.quantity_received
, pll.quantity_cancelled
, fu1.user_name “Cancelled by”
, pll.cancel_reason
, pll.cancel_date
, poh.creation_date
, pll.promised_date
, pll.need_by_date
, poh.last_update_date
,(SELECT DISTINCT rsh.attribute8 
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) “Appointment Number”
,(SELECT DISTINCT rsh.attribute9 
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) “Appointment Date”
,pol.unit_price “Unit Cost”
,(pol.unit_price*pol.quantity) “Ext Cost”
FROM 
po_headers_all poh
, po_lines_all pol
, po_line_locations_all pll
, mtl_system_items_b msi
, inv.mtl_item_categories mic
, inv.mtl_categories_b mcb
, fnd_user fu
, fnd_user fu2
, po_vendors vn
, po_vendor_sites_all vs
, hr_locations_all_tl hrl1
, hr_organization_units hou1
, fnd_user fu1
WHERE TRUNC(poh.last_update_date)=TRUNC(SYSDATE-1)
AND poh.type_lookup_code=’STANDARD’
–AND poh.attribute2 LIKE ‘M%’
AND poh.po_header_id=pol.po_header_id
AND pol.po_line_id=pll.po_line_id
AND pol.po_header_id=pll.po_header_id
AND pol.item_id=msi.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
— AND mic.category_set_id = 1
AND mcb.category_id = mic.category_id
–AND msi.organization_id=22
AND poh.created_by=fu.user_id
AND poh.vendor_id=vn.vendor_id
AND vs.vendor_id = poh.vendor_id
AND vs.vendor_site_id = poh.vendor_site_id
AND hrl1.location_id(+) = poh.ship_to_location_id
AND hrl1.LANGUAGE(+) = USERENV (‘LANG’)
AND hrl1.location_id=hou1.location_id
AND fu1.user_id(+)=pll.cancelled_by
AND poh.last_updated_by=fu2.user_id

/by
, ,

Register the table in Application from backend

Note:- Create this Procedure in APPS schema. This Procedure will register the table and it’s column in Applications.

Note:- The procedure has two IN paramters. First would would have the Application_Short_Name where you want to register the table and Second Paramter would have the table name you want to register.

Note:- Even through, if table is not created and we are trying to register some table name with this procedure, then it will register the table name but with no columns in it.

Note:- After registering the table, if you want to confirm it from Oracle Application Front end then, you can check in Application Developer Responsibility.

Navigation:-

Application Developer–>Application–>Database–>Table

Why you need to Register any table in Application?
A) It is important to know, why we need to register any table in the Oracle Application.
If you want to use any table in value set or Oracle Alerts, then you need to
register that table in the Application.

Note:- All we need to give the table name. Rest the values which are needed to register the table or Column in applications would be taken from database automatically. This saves lot of developer time.

I hope you find this post useful in your development work. For any issues or suggestion related to this post, please leave your comment for this post. I will try to reply as soon as possible.

CREATE OR REPLACE PROCEDURE proc_ad_ad
(ap_sname VARCHAR2, ptable_name VARCHAR2)
IS
CURSOR c1
IS
SELECT ROWNUM, column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = upper(ptable_name);

va c1%ROWTYPE;
BEGIN
ad_dd.register_table (ap_sname, ptable_name, ‘T’);

OPEN c1;

LOOP
FETCH c1
INTO va;

EXIT WHEN c1%NOTFOUND;
ad_dd.register_column (ap_sname,
ptable_name,
va.column_name,
va.ROWNUM,
va.data_type,
va.data_length,
‘Y’,
‘N’
);
DBMS_OUTPUT.put_line ( va.ROWNUM
‘ ‘
va.column_name
‘ ‘
va.data_type
‘ ‘
va.data_length
);
END LOOP;

CLOSE c1;

COMMIT;
END proc_ad_ad;

Example:-

create table phani (col1 varchar2(10), col2 number, col3 date);

begin
proc_ad_ad(‘AU’, ‘OracleERPApps’);
end;

In the above Example, we have created the table and trying to register the same table in Application using the Procedure. 

/by
, , , ,

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
, , ,

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