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.
Monday, October 20, 2008
Saturday, October 11, 2008
Flexfields avilable for your Oracle Application Version
Flexfields avilable for your Oracle Application Version:-
-----------------------------------------------------------------
The following Query gives us, all the flexfields avilable for your Oracle Application version.
select faa.APPLICATION_NAME "Application Name", fif.id_flex_name "Flexfield Name", faa.description "Application Description"
from FND_ID_FLEXS fif, FND_APPLICATION_ALL_VIEW faa
WHERE fif.APPLICATION_ID = faa.APPLICATION_ID
order by faa.APPLICATION_NAME;
-----------------------------------------------------------------
The following Query gives us, all the flexfields avilable for your Oracle Application version.
select faa.APPLICATION_NAME "Application Name", fif.id_flex_name "Flexfield Name", faa.description "Application Description"
from FND_ID_FLEXS fif, FND_APPLICATION_ALL_VIEW faa
WHERE fif.APPLICATION_ID = faa.APPLICATION_ID
order by faa.APPLICATION_NAME;
FlexFields
FlexFields:-
-------------
Flexfields stores main Information of the Organizations. We have many flexfields in different modules.
Note:- The following Flexfields are avilable in the 11.5.8 Oracle Application Version.
-----
You may find more flexfield in the new Oracle apps versions.
To check all the Flexfield avilable for your Oracle Apps version, use the Flexfield Query
-------------
Flexfields stores main Information of the Organizations. We have many flexfields in different modules.
Note:- The following Flexfields are avilable in the 11.5.8 Oracle Application Version.
-----
You may find more flexfield in the new Oracle apps versions.
To check all the Flexfield avilable for your Oracle Apps version, use the Flexfield Query
| Application Name | Flex-field Name | Application Description |
| CRL Financials Assets | Super Group | CRL Financials - Assets |
| CRL Financials Assets | Group Asset | CRL Financials - Assets |
| Oracle Advanced Service Online | | Oracle Advanced Service Online |
| Oracle Advanced Service Online | AHL Operation | Oracle Advanced Service Online |
| Oracle Assets | Location Flexfield | Oracle Assets |
| Oracle Assets | Category Flexfield | Oracle Assets |
| Oracle Assets | Asset Key Flexfield | Oracle Assets |
| Oracle General Ledger | Accounting Flexfield | Oracle General Ledger |
| Oracle Human Resources | Soft Coded KeyFlexfield | Oracle Human Resources |
| Oracle Human Resources | Job Flexfield | Oracle Human Resources |
| Oracle Human Resources | Grade Flexfield | Oracle Human Resources |
| Oracle Human Resources | CAGR Flexfield | Oracle Human Resources |
| Oracle Human Resources | Competence Flexfield | Oracle Human Resources |
| Oracle Human Resources | Item Contexts Keyflex | Oracle Human Resources |
| Oracle Human Resources | Personal Analysis Flexfield | Oracle Human Resources |
| Oracle Human Resources | Position Flexfield | Oracle Human Resources |
| Oracle Inventory | System Items | Oracle Inventory |
| Oracle Inventory | Sales Orders | Oracle Inventory |
| Oracle Inventory | Item Categories | Oracle Inventory |
| Oracle Inventory | Account Aliases | Oracle Inventory |
| Oracle Inventory | Item Catalogs | Oracle Inventory |
| Oracle Inventory | ORACLE_SERVICE_ITEM_FLEXFIELD | Oracle Inventory |
| Oracle Inventory | Stock Locators | Oracle Inventory |
| Oracle Payroll | People Group Flexfield | Oracle Payroll |
| Oracle Payroll | Bank Details KeyFlexField | Oracle Payroll |
| Oracle Payroll | Cost Allocation Flexfield | Oracle Payroll |
| Oracle Public Sector Budgeting | PSB Position Flexfield | Oracle Public Sector Budgeting |
| Oracle Receivables | Territory Flexfield | Oracle Receivables |
| Oracle Receivables | Sales Tax Location Flexfield | Oracle Receivables |
| Oracle Receivables | ARTA-Receipt Prof Ident | Oracle Receivables |
| Oracle Training Administration | Training Resources | Oracle Training Administration |
Saturday, October 4, 2008
FORALL
FORALL Concept:-
---------------------
FORALL concept help us reducing the Iterations between PL/SQL Block and SQL Engine.
Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.
If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.
Example:-
CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
FROM employees WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
TYPE NumList IS TABLE OF NUMBER;
-- The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
-- SAVE EXCEPTIONS means don't stop if some INSERT fail.
FORALL i IN 1..deptnums.COUNT SAVE EXCEPTIONS
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
dbms_output.put_line('Dept '||deptnums(i)||': inserted '||
SQL%BULK_ROWCOUNT(i)||' records');
END LOOP;
dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN -- Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of INSERT statements that failed: ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
---------------------
FORALL concept help us reducing the Iterations between PL/SQL Block and SQL Engine.
Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.
If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.
Example:-
CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
FROM employees WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
TYPE NumList IS TABLE OF NUMBER;
-- The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
-- SAVE EXCEPTIONS means don't stop if some INSERT fail.
FORALL i IN 1..deptnums.COUNT SAVE EXCEPTIONS
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
dbms_output.put_line('Dept '||deptnums(i)||': inserted '||
SQL%BULK_ROWCOUNT(i)||' records');
END LOOP;
dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN -- Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of INSERT statements that failed: ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
BULK COLLECT
BULK COLLECT:-
-------------------
Bulk Collect concept help us reducing the Iterations between SQL Engine and PL/SQL Block.
Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine. And if we have some SELECT statement in the PL/SQL Block, then it will move from SQL Enginer to get the data from SQL Enginer to the PL/SQL Block. For the SELECT statement, we use the BULK COLLECT to record the Iteration from the SQL Enginer to the PL/SQL Block.
If I have to put my self in other word then, I should say, BULK COLLECT will process group of SELECT statements at one short.
Similarly for the DML Operations, we use FORALL Concept, please refer the FORALL Concept for more details.
Note:- This Example is based on the EMP table in the SCOTT schema. Here say, EMP table have 90 Records and if we do it in the Normal way (Without BULK COLLECT concept, then It will iterate SQL enginer to the PL/SQL Block for 90 Times). By using BULK COLLECT, we can completly reduce the Iterations.
In the following Example, I have limited to 10, so for 90 records, this will iterate for 9 times.
Example:-
-----------
declare
type array is table of number index by binary_integer;
l_data array;
cursor c is select empno from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
if ( c%notfound )
then
dbms_output.put_line
( 'Cursor returned NOT FOUND but array has ' || l_data.count
|| ' left to process' );
else
dbms_output.put_line
( 'We have ' || l_data.count
|| ' to process' );
end if;
exit when c%notfound;
end loop;
close c;
end;
Returning clause with the Bulk collect with the DML opeartions:-
---------------------------------------------------------------------------
DELETE FROM emp WHERE num = 30
RETURNING empno, ename BULK COLLECT INTO p_num, p_ename;
-------------------
Bulk Collect concept help us reducing the Iterations between SQL Engine and PL/SQL Block.
Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine. And if we have some SELECT statement in the PL/SQL Block, then it will move from SQL Enginer to get the data from SQL Enginer to the PL/SQL Block. For the SELECT statement, we use the BULK COLLECT to record the Iteration from the SQL Enginer to the PL/SQL Block.
If I have to put my self in other word then, I should say, BULK COLLECT will process group of SELECT statements at one short.
Similarly for the DML Operations, we use FORALL Concept, please refer the FORALL Concept for more details.
Note:- This Example is based on the EMP table in the SCOTT schema. Here say, EMP table have 90 Records and if we do it in the Normal way (Without BULK COLLECT concept, then It will iterate SQL enginer to the PL/SQL Block for 90 Times). By using BULK COLLECT, we can completly reduce the Iterations.
In the following Example, I have limited to 10, so for 90 records, this will iterate for 9 times.
Example:-
-----------
declare
type array is table of number index by binary_integer;
l_data array;
cursor c is select empno from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
if ( c%notfound )
then
dbms_output.put_line
( 'Cursor returned NOT FOUND but array has ' || l_data.count
|| ' left to process' );
else
dbms_output.put_line
( 'We have ' || l_data.count
|| ' to process' );
end if;
exit when c%notfound;
end loop;
close c;
end;
Returning clause with the Bulk collect with the DML opeartions:-
---------------------------------------------------------------------------
DELETE FROM emp WHERE num = 30
RETURNING empno, ename BULK COLLECT INTO p_num, p_ename;
Thursday, October 2, 2008
Concurrent program name based on the request ID
Concurrent program name based on the request ID:-
------------------------------------------------------------
With the following query, you can get the concurrent program name based on the Concurrent request ID
Select program from FND_CONC_REQ_SUMMARY_V
where request_id = 'Your request ID';
------------------------------------------------------------
With the following query, you can get the concurrent program name based on the Concurrent request ID
Select program from FND_CONC_REQ_SUMMARY_V
where request_id = 'Your request ID';
Operating Unit name based on the Operating Unit ID
Operating Unit name based on the Operating Unit ID:- --------------------------------------------------------------
In the following Query you can get Operating Unit Name based on the Operating Unit ID.
select Business_group_id, Organization_id, name,date_from,date_to, legal_entity_id, set_of_books_id from hr_operating_units where organization_id = 'your Operating Unit ID';
In the following Query you can get Operating Unit Name based on the Operating Unit ID.
select Business_group_id, Organization_id, name,date_from,date_to, legal_entity_id, set_of_books_id from hr_operating_units where organization_id = 'your Operating Unit ID';
INV Module
In this session you will find all the ready scripts avilable for the INV Module.
Modifier Header ID based on the Modifier Description
Item Catalog, Descriptive Element Value
Inventory code and Inventory Name/description
Department Number, Category Number and Cost of item
On-hand Quatity of one Particular Item in whole Organization
Get all the Assembly completion Lot Numbers
Get the Assembly Details
On-hand inventory information
Modifier Header ID based on the Modifier Description
Item Catalog, Descriptive Element Value
Inventory code and Inventory Name/description
Department Number, Category Number and Cost of item
On-hand Quatity of one Particular Item in whole Organization
Get all the Assembly completion Lot Numbers
Get the Assembly Details
On-hand inventory information
On-hand Quatity of one Particular Item in whole Organization
INV On-hand Quatity of one Particular Item in whole Organization:-
-----------------------------------------------------------------------------------
The following Query will On-hand Quatity of particular item in the whole Organization
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';
Example:-
Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;
If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.
select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;
-----------------------------------------------------------------------------------
The following Query will On-hand Quatity of particular item in the whole Organization
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';
Example:-
Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;
If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.
select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;
Department Number, Category Number and Cost of item based on the Item ID
Department Number, Category Number and Cost of item based on the Item ID:-
--------------------------------------------------------------------------------------------
The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 'Your Inventory Item ID'
and organization_id = 'Your Inventory Organization ID' and category_set_id = 1 and rownum = 1)
and cs.organization_id = 'Your Inventory Organization ID'
and cs.cost_type_id = 1
and cs.inventory_item_id = 'Your Inventory Item ID';
Example:-
-----------
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 122251
and organization_id = 22 and category_set_id = 1 and rownum = 1)
and cs.organization_id = 22
and cs.cost_type_id = 1
and cs.inventory_item_id = 122251;
--------------------------------------------------------------------------------------------
The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 'Your Inventory Item ID'
and organization_id = 'Your Inventory Organization ID' and category_set_id = 1 and rownum = 1)
and cs.organization_id = 'Your Inventory Organization ID'
and cs.cost_type_id = 1
and cs.inventory_item_id = 'Your Inventory Item ID';
Example:-
-----------
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 122251
and organization_id = 22 and category_set_id = 1 and rownum = 1)
and cs.organization_id = 22
and cs.cost_type_id = 1
and cs.inventory_item_id = 122251;
Get the Inventory code and Inventory Name/description
Get the Inventory code and Inventory Name/description:-
-------------------------------------------------------------------
Note:- Inventory Organization will be represented with the Organization_id column.
We many give some code and name to the Inventory. With the following Query we can know the Code and Name details of all the Inventories in the Organization.
SELECT distinct SUBSTR(loc.location_code, 1, 4) "Inventory Code"
,DECODE(SUBSTR(loc.tax_name, 1, 3)
,'Bay', 'H'
,'Zel', 'Z'
,'KMT', 'Z') || lpad(substr(loc.location_code, 1, 4), 4, '0')|| ' ' ||
SUBSTR(loc.address_line_2, 1, 30) "Inventory Name/Description",
ou.ORGANIZATION_ID "Inventory Organization ID"
FROM hr_locations_all loc,
hr_all_organization_units ou
WHERE loc.location_id = ou.location_id;
-------------------------------------------------------------------
Note:- Inventory Organization will be represented with the Organization_id column.
We many give some code and name to the Inventory. With the following Query we can know the Code and Name details of all the Inventories in the Organization.
SELECT distinct SUBSTR(loc.location_code, 1, 4) "Inventory Code"
,DECODE(SUBSTR(loc.tax_name, 1, 3)
,'Bay', 'H'
,'Zel', 'Z'
,'KMT', 'Z') || lpad(substr(loc.location_code, 1, 4), 4, '0')|| ' ' ||
SUBSTR(loc.address_line_2, 1, 30) "Inventory Name/Description",
ou.ORGANIZATION_ID "Inventory Organization ID"
FROM hr_locations_all loc,
hr_all_organization_units ou
WHERE loc.location_id = ou.location_id;
Item Catalog, Descriptive Element Value
Catalog and Descriptive element value on the Inventory Item ID of the Item:-
-----------------------------------------------------------------------------------------
Note:- The following Query can get multiple records, since one Item can assoiciate to many Inventory Organizations.
In the following Query, I have commented the organization_id condition and given the rownum = 1 condition.
Catalog is the Flexfield. The values in these also depends upon the Set-up part of the Catalog Flexfield.
MTL_DESCR_ELEMENT_VALUES stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
SELECT c.segment1 || '-' || a.element_value
FROM mtl_descr_element_values a
,mtl_descriptive_elements b
,mtl_item_catalog_groups c
,mtl_system_items_fvl d
WHERE a.element_name = b.element_name
AND a.inventory_item_id = d.inventory_item_id
-- AND d.organization_id = 'Your Organization ID'
AND b.item_catalog_group_id = d.item_catalog_group_id
AND b.item_catalog_group_id = c.item_catalog_group_id
AND a.element_name = b.element_name
AND a.element_name LIKE '%Vendor%'
AND a.inventory_item_id = 'Your Inventory Item ID'
AND ROWNUM = 1;
-----------------------------------------------------------------------------------------
Note:- The following Query can get multiple records, since one Item can assoiciate to many Inventory Organizations.
In the following Query, I have commented the organization_id condition and given the rownum = 1 condition.
Catalog is the Flexfield. The values in these also depends upon the Set-up part of the Catalog Flexfield.
MTL_DESCR_ELEMENT_VALUES stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
SELECT c.segment1 || '-' || a.element_value
FROM mtl_descr_element_values a
,mtl_descriptive_elements b
,mtl_item_catalog_groups c
,mtl_system_items_fvl d
WHERE a.element_name = b.element_name
AND a.inventory_item_id = d.inventory_item_id
-- AND d.organization_id = 'Your Organization ID'
AND b.item_catalog_group_id = d.item_catalog_group_id
AND b.item_catalog_group_id = c.item_catalog_group_id
AND a.element_name = b.element_name
AND a.element_name LIKE '%Vendor%'
AND a.inventory_item_id = 'Your Inventory Item ID'
AND ROWNUM = 1;
Modifier Header ID based on the Modifier Description
INV Modifier Header ID based on the Modifier Description:-
---------------------------------------------------------------------
The following Query would get the Modifier Header ID based on the Description given for that Modifier. In many places we use the Modifier Header ID to get other values related to that Modifier.
SELECT list_header_id "Modifier Header ID"
FROM qp_list_headers_b
WHERE trim(SUBSTR (comments, 1, 240)) = trim('ALLORACLETECH Description') AND ROWNUM = 1;
Get the Modifier Description Based on the Modifier ID
The following Query will get the Modifier Description given for the Modifier based on the Modifier Header ID.
SELECT SUBSTR (comments, 1, 240) "Modifier Description"
FROM qp_list_headers_b
WHERE list_header_id = 'Your modifier header ID' AND ROWNUM = 1;
---------------------------------------------------------------------
The following Query would get the Modifier Header ID based on the Description given for that Modifier. In many places we use the Modifier Header ID to get other values related to that Modifier.
SELECT list_header_id "Modifier Header ID"
FROM qp_list_headers_b
WHERE trim(SUBSTR (comments, 1, 240)) = trim('ALLORACLETECH Description') AND ROWNUM = 1;
Get the Modifier Description Based on the Modifier ID
The following Query will get the Modifier Description given for the Modifier based on the Modifier Header ID.
SELECT SUBSTR (comments, 1, 240) "Modifier Description"
FROM qp_list_headers_b
WHERE list_header_id = 'Your modifier header ID' AND ROWNUM = 1;
Subscribe to:
Posts (Atom)
