Retrieves all PO transaction quantities

Retrieves all PO transaction quantities:-
----------------------------------------------

Following Query would get all the PO Numbers, Item ID, Quantity Order, Received, Canceled etc. The same information can be found in the PO order form from the Front end Application.

SELECT
POL.ITEM_ID "Inventory Item ID",
POLL.SHIP_TO_LOCATION_ID "Ship to Location ID",
poll.quantity "Quantity",
poll.Quantity_received "Quantity Received",
poll.QUANTITY_cancelled "Quantity Canceled",
POH.SEGMENT1 "PO Order Number"
FROM po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id=pol.po_header_id
AND pol.po_line_id=poll.po_line_id
AND poll.po_header_id=pol.po_header_id
AND poh.authorization_status='APPROVED'
AND poh.type_lookup_code='STANDARD'
AND NVL(poh.cancel_flag,'N') ='N'
AND POL.ITEM_ID IS NOT NULL
AND NVL (poll.quantity, 0) > (NVL (Quantity_received,0) + NVL (Quantity_cancelled,0))

To identify the items at Pricelist level against active modifiers

To identify the items at Price-list 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

To identify the items at Item Category level against active modifiers

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

Note:- Item Category is the Flex-field in the Inventory Module. In the Master Item define, we choose what item belongs to which Item Category.

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

SELECT DISTINCT -- qpa.list_header_id "Modifier Header ID",
qpa.list_line_id "Modifier Line Number",
qlh.COMMENTS "Modifier Name (Description)",
qll.start_date_active "Modifier Start Date",
qll.end_date_active "Modifier End Date",
b.segment1 "SKU",
b.inventory_item_id "Inventory Item ID",
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,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_modifier_summary_v qll,
mtl_item_catalog_groups a,
mtl_system_items_b b,
mtl_descr_element_values c
--qp_qualifiers_v q
WHERE b.item_catalog_group_id = a.item_catalog_group_id
AND b.inventory_item_id = c.inventory_item_id
AND b.organization_id =
(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
AND c.element_sequence IN ('20')
AND qq.list_header_id = qpa.list_header_id
AND qq.list_line_id = qpa.list_line_id
AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE35'
AND qq.qualifier_attr_value =
a.segment1 || '-' || c.element_value
AND qpa.product_attribute = 'PRICING_ATTRIBUTE3'
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 SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND b.segment1 = 'Your Item Name'

Get the Modifier details of particular item (only at the Line Level)

Get the Modifier details of particular item (only at the Line Level) :-
------------------------------------------------------------------------------

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.

Note:- Item Category is the Flex-field in the Inventory Module. In the Master Item define, we choose what item belongs to which Item Category.

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

Note:- This is applicable only if the Item Number directly given at the Modifier Line Level.

SELECT distinct qlh.comments "Modifier Name"
,qqv.rule_name "Qualifier Group"
,ou.name "Store Id"
--,qlhv.name "Price list name"
,qms.product_attr_value "SKU"
,qms.list_line_no "Modifier Line No"
,qms.start_date_active "Start Date"
,qms.end_date_active "End Date"
,qms.arithmetic_operator_type "Application Method"
,qms.operand "Value"
,qms.product_precedence "Precedence"
,qms.incompatibility_grp "Incompatibility Group"
,qms.pricing_group_sequence "Bucket"
FROM
qp_modifier_summary_v qms
, qp_list_headers_b qlh
,qp_list_headers_tl qlt
,qp_qualifiers_v qqv
,mtl_system_items_b msi
,hr_all_organization_units ou
--,qp_list_headers_v qlhv
WHERE
qlh.list_header_id = qms.list_header_id
--and qms.list_header_id=qlhv.list_header_id
and qlh.list_header_id =qqv.list_header_id
and to_char(msi.inventory_item_id)=qms.product_attr_val
AND ou.organization_id = msi.organization_id
and to_char(ou.organization_id)= qqv.qualifier_attr_value
and sysdate between qms.start_date_active and qms.end_date_active
and qlt.LIST_HEADER_ID=qlh.LIST_HEADER_ID
AND exists
(select 1
from mtl_system_items_b a
where a.organization_id=(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
and to_char(a.inventory_item_id)=qms.product_attr_val
and a.segment1 in('Your Item Name'))

Write message in Log or Out file

Write message in Log or Out file:-
--------------------------------------

When ever, I have to create new package, I normally code a procedure to write any information in the log or Out files of the program.

The following is the Procedure which I use it in any package. This is standard in some companies coding. This save a lot of time and this makes easy to other people who goes through your program.

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : Procedure writes to the log file or output file
based on type.O=Output File, L=Log File
*************************************************************************/

BEGIN
IF p_type = 'L'
THEN


fnd_file.put_line (fnd_file.log, p_message);

ELSIF p_type = 'O'
THEN

fnd_file.put_line (fnd_file.output, p_message);


END IF;
END write;

The above write procedure can be used in other Procedure/Function in the package to write any information in the Log or Out files.

PROCEDURE main(errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_par1 IN NUMBER
)
IS
v_errbuf VARCHAR2(1000) := NULL;
v_retcode NUMBER := 0;
v_file_name VARCHAR2(100);
BEGIN

v_retcode := 0;

v_file_name := fnd_profile.value('XYZ');


IF v_file_name IS NULL
THEN
write('O','Profile XYZ is not defined or the value is not set');
retcode := 2;
RETURN;
END IF;
END;

Note:- In the above Procedure, I am using the write Procedure and returning 2 for the retcode (0 - Complete, 1- Warning and 2 will be for Error).

Note:- This is one time process and you will realise, how much helpful it will be when ever you have to right something in log or out file.

More then information, this post is more of suggestion. Hope this post will help you make your code easy.

Register the table in Application from backend

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', 'Phani');
end;

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

Submit the Concurrent Program from Backend

Submit the Concurrent Program from Backend:-
-------------------------------------------------------

The following is the Sample Code to Submit the Concurrent Program from the backend.

Note:- This is the Concurrent Program, not the Request Set. To Submit the Request Set from the backend, We have different API.

I have already document, for submitting the request set from the backend in the Following URL.

http://alloracletech.blogspot.com/2008/07/registering-from-backend.html

DECLARE
l_success NUMBER;
BEGIN
BEGIN

fnd_global.apps_initialize( user_id => 2572694, resp_id => 50407, resp_appl_id => 20003);

-- If you are directly running from the database using the TOAD, SQL-NAVIGATOR or --SQL*PLUS etc. Then you need to Initialize the Apps. In this case use the above API to --Initialize the APPS. If you are using same code in some procedure and running directly
--from application then you don't need to initalize.
--Then you can comment the above API.

l_success :=
fnd_request.submit_request
('XXAPP', -- Application Short name of the Concurrent Program.
'XXPRO_RPT', -- Program Short Name.
'Program For testing the backend Report', -- Description of the Program.
SYSDATE, -- Submitted date. Always give the SYSDATE.
FALSE, -- Always give the FLASE.
'1234' -- Passing the Value to the First Parameter of the report.
);
COMMIT;

-- Note:- In the above request Run, I have created the Report, which has one parameter.

IF l_success = 0
THEN
-- fnd_file.put_line (fnd_file.LOG, 'Request submission For this store FAILED' );
DBMS_OUTPUT.PUT_LINE( 'Request submission For this store FAILED' );
ELSE
-- fnd_file.put_line (fnd_file.LOG, 'Request submission for this store SUCCESSFUL');
DBMS_OUTPUT.PUT_LINE( 'Request submission For this store SUCCESSFUL' );
END IF;

--Note:- If you are running directly from database, use DBMS API to display. If you are
-- Running directly from Application, then Use the fnd_file API to write the message
-- in the log file.

END;

I hope the above information is helping you. For any Suggestion or issues with the above, please leave your comments. I will try to reply back as soon as possible.