Oracle EBS – Query to get Price list details for 5 Years

 Execute the below query into EBS database.
SELECT line, empty, pricing, item, from_date, TO_DATE, total, uom, attirubute,
org
FROM (SELECT ‘NL’ line, ” empty, ‘INTERCOMPANY PRICING’ pricing,
item.segment1 item,
(SELECT TO_CHAR (TRUNC (TO_DATE (SYSDATE + 20), ‘MON’),
‘DD-MON-YY’
)
FROM DUAL) from_date,
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
13
– TO_NUMBER
(TO_CHAR ( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL) TO_DATE,
ROUND ((COST.item_cost / uom.conversion_rate), 5) TEST,
DECODE
(item.planning_make_buy_code,
‘1’, (ROUND
( ( NVL (ROUND (( COST.item_cost
/ uom.conversion_rate
),
5
),
0
)
+ NVL
((SELECT SUM (NVL (description, 0))
FROM fnd_lookup_values_vl
WHERE lookup_type =
‘XXXX_PRICE_INCLUDE’
AND start_date_active =
(SELECT TO_CHAR
(TRUNC
(TO_DATE
( SYSDATE
+ 20
),
‘YEAR’
),
‘DD-MON-YY’
)
FROM DUAL)
AND end_date_active =
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
12
– TO_NUMBER
(TO_CHAR
( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL)
AND ( (tag =
(SELECT SUBSTR
(segment1,
1,
2
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
)
OR (tag =
(SELECT SUBSTR
(segment1,
1,
5
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
–V10
AND organization_id =
item.organization_id)
)
)),
0
)
+ NVL
((SELECT description
FROM fnd_lookup_values_vl
WHERE lookup_type =
‘XXXX_PRICE_INCLUDE1’
AND start_date_active =
(SELECT TO_CHAR
(TRUNC
(TO_DATE
( SYSDATE
+ 20
),
‘YEAR’
),
‘DD-MON-YY’
)
FROM DUAL)
AND end_date_active =
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
12
– TO_NUMBER
(TO_CHAR
( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL)
AND ( tag IN (
SELECT SUBSTR
(segment1,
1,
2
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
OR tag IN (
SELECT SUBSTR
(segment1,
1,
5
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
)),
0
)
)
* :p_percentage,
2
)
),
ROUND ( (COST.item_cost / uom.conversion_rate)
* :p_percentage,
2
)
) total, –V13 V14
‘EA’ uom, ‘0.00’ attirubute, ‘204’ org,
item.inventory_item_id –Added by raj on 25-May-2016
FROM mtl_system_items_b item,
mtl_item_categories cat,
mtl_categories_b NAME,
mtl_uom_conversions uom,
cst_item_costs COST,
mtl_onhand_quantities_detail onhand
WHERE item.organization_id = cat.organization_id
AND item.inventory_item_id = cat.inventory_item_id
AND cat.category_id = NAME.category_id
AND item.inventory_item_id = uom.inventory_item_id(+)
AND item.primary_unit_of_measure = uom.unit_of_measure
AND item.organization_id = COST.organization_id
AND item.inventory_item_id = COST.inventory_item_id(+)
AND item.organization_id = onhand.organization_id(+)
AND item.inventory_item_id = onhand.inventory_item_id(+)
AND item.organization_id = 204
AND NAME.structure_id = 101
AND item.inventory_item_status_code NOT IN
(‘Disabled’, ‘Inactive’, ‘NON-INV’)
AND COST.cost_type_id = 1
AND item.item_type = ‘FG’
AND NAME.segment1 ‘UNASSIGNED’
AND TRUNC (item.last_update_date) BETWEEN (‘1-JAN-2019′)
AND (’31-DEC-2023’) –V12
AND COST.item_cost > 0
AND item.segment1 NOT LIKE ‘EA%’
AND item.segment1 NOT LIKE ‘%PROTO’
AND item.segment1 NOT LIKE ‘%OLD’
AND item.primary_unit_of_measure = ‘Case’
GROUP BY TRUNC (item.last_update_date),
item.segment1,
item.description,
item.inventory_item_id,
item.inventory_item_status_code,
NAME.segment1,
COST.item_cost,
item.primary_unit_of_measure,
uom.conversion_rate,
item.planning_make_buy_code,
item.organization_id,
ROUND ((COST.item_cost / uom.conversion_rate), 5),
item.inventory_item_id
UNION
SELECT ‘NL’ line, ” empty, ‘EMPLOYEE PRICING’ pricing,
item.segment1 item,
(SELECT TO_CHAR (TRUNC (TO_DATE (SYSDATE + 20), ‘MON’),
‘DD-MON-YY’
)
FROM DUAL) from_date,
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
13
– TO_NUMBER
(TO_CHAR ( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL) TO_DATE,
ROUND ((COST.item_cost / uom.conversion_rate), 5) TEST,
DECODE
(item.planning_make_buy_code,
‘1’, (ROUND
( ( NVL (ROUND (( COST.item_cost
/ uom.conversion_rate
),
5
),
0
)
+ NVL
((SELECT SUM (NVL (description, 0))
FROM fnd_lookup_values_vl
WHERE lookup_type =
‘XXXX_PRICE_INCLUDE’
AND start_date_active =
(SELECT TO_CHAR
(TRUNC
(TO_DATE
( SYSDATE
+ 20
),
‘YEAR’
),
‘DD-MON-YY’
)
FROM DUAL)
AND end_date_active =
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
12
– TO_NUMBER
(TO_CHAR
( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL)
AND ( (tag =
(SELECT SUBSTR
(segment1,
1,
2
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
)
OR (tag =
(SELECT SUBSTR
(segment1,
1,
5
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
–V10
AND organization_id =
item.organization_id)
)
)),
0
)
+ NVL
((SELECT description
FROM fnd_lookup_values_vl
WHERE lookup_type =
‘XXXX_PRICE_INCLUDE1’
AND start_date_active =
(SELECT TO_CHAR
(TRUNC
(TO_DATE
( SYSDATE
+ 20
),
‘YEAR’
),
‘DD-MON-YY’
)
FROM DUAL)
AND end_date_active =
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
12
– TO_NUMBER
(TO_CHAR
( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL)
AND ( tag IN (
SELECT SUBSTR
(segment1,
1,
2
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
OR tag IN (
SELECT SUBSTR
(segment1,
1,
5
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
)),
0
)
)
* :p_percentage,
2
)
),
ROUND ( (COST.item_cost / uom.conversion_rate)
* :p_percentage,
2
)
)
* uom.conversion_rate total, –V13 V14
‘CA’ uom, ‘0.00’ attirubute, ‘204’ org,
item.inventory_item_id –Added by raj on 25-May-2016
FROM mtl_system_items_b item,
mtl_item_categories cat,
mtl_categories_b NAME,
mtl_uom_conversions uom,
cst_item_costs COST,
mtl_onhand_quantities_detail onhand
WHERE item.organization_id = cat.organization_id
AND item.inventory_item_id = cat.inventory_item_id
AND cat.category_id = NAME.category_id
AND item.inventory_item_id = uom.inventory_item_id(+)
AND item.primary_unit_of_measure = uom.unit_of_measure
AND item.organization_id = COST.organization_id
AND item.inventory_item_id = COST.inventory_item_id(+)
AND item.organization_id = onhand.organization_id(+)
AND item.inventory_item_id = onhand.inventory_item_id(+)
AND item.organization_id IN (204)
AND NAME.structure_id = 101
AND item.inventory_item_status_code NOT IN
(‘Disabled’, ‘Inactive’, ‘NON-INV’)
AND COST.cost_type_id = 1
AND item.item_type = ‘FG’
AND NAME.segment1 ‘UNASSIGNED’
AND TRUNC (item.last_update_date) BETWEEN (‘1-JAN-2019′)
AND (’31-DEC-2023’) –V12
AND COST.item_cost > 0
AND item.segment1 NOT LIKE ‘EA%’
AND item.segment1 NOT LIKE ‘%PROTO’
AND item.segment1 NOT LIKE ‘%OLD’
AND item.primary_unit_of_measure = ‘Case’
GROUP BY TRUNC (item.last_update_date),
item.segment1,
item.description,
item.inventory_item_id,
item.inventory_item_status_code,
NAME.segment1,
COST.item_cost,
item.primary_unit_of_measure,
uom.conversion_rate,
item.planning_make_buy_code,
item.organization_id,
ROUND ((COST.item_cost / uom.conversion_rate), 5),
item.inventory_item_id
UNION
SELECT ‘NL’ line, ” empty, ‘EMPLOYEE PRICING’ pricing,
‘EA’ || item.segment1 item,
(SELECT TO_CHAR (TRUNC (TO_DATE (SYSDATE + 20), ‘MON’),
‘DD-MON-YY’
)
FROM DUAL) from_date,
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
13
– TO_NUMBER
(TO_CHAR ( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL) TO_DATE,
ROUND ((COST.item_cost / uom.conversion_rate), 5) TEST,
DECODE
(item.planning_make_buy_code,
‘1’, (ROUND
( ( NVL (ROUND (( COST.item_cost
/ uom.conversion_rate
),
5
),
0
)
+ NVL
((SELECT SUM (NVL (description, 0))
FROM fnd_lookup_values_vl
WHERE lookup_type =
‘XXXX_PRICE_INCLUDE’
AND start_date_active =
(SELECT TO_CHAR
(TRUNC
(TO_DATE
( SYSDATE
+ 20
),
‘YEAR’
),
‘DD-MON-YY’
)
FROM DUAL)
AND end_date_active =
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
12
– TO_NUMBER
(TO_CHAR
( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL)
AND ( (tag =
(SELECT SUBSTR
(segment1,
1,
2
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
)
OR (tag =
(SELECT SUBSTR
(segment1,
1,
5
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
–V10
AND organization_id =
item.organization_id)
)
)),
0
)
+ NVL
((SELECT description
FROM fnd_lookup_values_vl
WHERE lookup_type =
‘XXXX_PRICE_INCLUDE1’
AND start_date_active =
(SELECT TO_CHAR
(TRUNC
(TO_DATE
( SYSDATE
+ 20
),
‘YEAR’
),
‘DD-MON-YY’
)
FROM DUAL)
AND end_date_active =
(SELECT TO_CHAR
(TRUNC
(LAST_DAY
(ADD_MONTHS
(SYSDATE,
12
– TO_NUMBER
(TO_CHAR
( SYSDATE
+ 20,
‘mm’
)
)
)
)
),
‘DD-MON-YY’
)
FROM DUAL)
AND ( tag IN (
SELECT SUBSTR
(segment1,
1,
2
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
OR tag IN (
SELECT SUBSTR
(segment1,
1,
5
)
FROM mtl_system_items_b
WHERE segment1 =
item.segment1
AND organization_id =
item.organization_id)
)),
0
)
)
* :p_percentage,
2
)
),
ROUND ( (COST.item_cost / uom.conversion_rate)
* :p_percentage,
2
)
) total, –V13 V14
‘EA’ uom, ‘0.00’ attirubute, ‘204’ org,
(SELECT inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = ‘EA’ || item.segment1
AND organization_id = 204) inventory_item_id
–Added by raj on 25-May-2016
FROM mtl_system_items_b item,
mtl_item_categories cat,
mtl_categories_b NAME,
mtl_uom_conversions uom,
cst_item_costs COST,
mtl_onhand_quantities_detail onhand
WHERE item.organization_id = cat.organization_id
AND item.inventory_item_id = cat.inventory_item_id
AND cat.category_id = NAME.category_id
AND item.inventory_item_id = uom.inventory_item_id(+)
AND item.primary_unit_of_measure = uom.unit_of_measure
AND item.organization_id = COST.organization_id
AND item.inventory_item_id = COST.inventory_item_id(+)
AND item.organization_id = onhand.organization_id(+)
AND item.inventory_item_id = onhand.inventory_item_id(+)
AND item.organization_id IN (204)
AND NAME.structure_id = 101
AND item.inventory_item_status_code NOT IN
(‘Disabled’, ‘Inactive’, ‘NON-INV’)
AND COST.cost_type_id = 1
AND item.item_type = ‘FG’
AND NAME.segment1 ‘UNASSIGNED’
AND COST.item_cost > 0
AND item.segment1 IN (
SELECT SUBSTR (segment1, 3)
FROM mtl_system_items_b
WHERE organization_id = 204
AND segment1 LIKE ‘EA%’
AND TRUNC (last_update_date) BETWEEN (‘1-JAN-2019′)
AND (’31-DEC-2023’))
AND item.primary_unit_of_measure = ‘Case’
GROUP BY TRUNC (item.last_update_date),
item.segment1,
item.description,
item.inventory_item_id,
item.inventory_item_status_code,
NAME.segment1,
COST.item_cost,
item.primary_unit_of_measure,
uom.conversion_rate,
item.planning_make_buy_code,
item.organization_id,
ROUND ((COST.item_cost / uom.conversion_rate), 5),
item.inventory_item_id