pardeshirahul
asked on
How to add the filter
only when
mmt.transaction_type_id IN (31, 41) then
mmt.transaction_source_id= 2878
how to add this filter in the below query
SELECT mmt.inventory_item_id,
p_org_id,
p_date,
p_quarter,
msi.item_type,
SUM (primary_quantity)
FROM mtl_material_transactions mmt, mtl_system_items_b msi
WHERE mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.organization_id = p_org_id
AND mmt.transaction_type_id IN (35, 33, 62, 34,31, 41)
--and mmt.transaction_source_id= 2878
AND mmt.transaction_date BETWEEN TO_DATE ('01/01/' || p_date,
'MM/DD/YYYY')
AND TO_DATE (
'12/31/'
|| p_date
|| ' 23:59:59',
'MM/DD/YYYY HH24:MI:SS')
AND mmt.transaction_date BETWEEN (SELECT ADD_MONTHS (
LAST_DAY (
TO_DATE (
'01'
|| TO_CHAR (
p_quarter
* 3,
'09')
|| p_date,
'DDMMYYYY'))
+ 1,
-3)
"Begins"
FROM DUAL)
AND (SELECT LAST_DAY (
TO_DATE (
'01'
|| TO_CHAR (
p_quarter
* 3,
'09')
|| p_date,
'DDMMYYYY'))
+ 86399 / 86400
"Ends"
FROM DUAL)
AND msi.item_type = p_item_type
GROUP BY mmt.inventory_item_id,
p_org_id,
p_date,
p_quarter,
msi.item_type;
mmt.transaction_type_id IN (31, 41) then
mmt.transaction_source_id=
how to add this filter in the below query
SELECT mmt.inventory_item_id,
p_org_id,
p_date,
p_quarter,
msi.item_type,
SUM (primary_quantity)
FROM mtl_material_transactions mmt, mtl_system_items_b msi
WHERE mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.organization_id = p_org_id
AND mmt.transaction_type_id IN (35, 33, 62, 34,31, 41)
--and mmt.transaction_source_id=
AND mmt.transaction_date BETWEEN TO_DATE ('01/01/' || p_date,
'MM/DD/YYYY')
AND TO_DATE (
'12/31/'
|| p_date
|| ' 23:59:59',
'MM/DD/YYYY HH24:MI:SS')
AND mmt.transaction_date BETWEEN (SELECT ADD_MONTHS (
LAST_DAY (
TO_DATE (
'01'
|| TO_CHAR (
p_quarter
* 3,
'09')
|| p_date,
'DDMMYYYY'))
+ 1,
-3)
"Begins"
FROM DUAL)
AND (SELECT LAST_DAY (
TO_DATE (
'01'
|| TO_CHAR (
p_quarter
* 3,
'09')
|| p_date,
'DDMMYYYY'))
+ 86399 / 86400
"Ends"
FROM DUAL)
AND msi.item_type = p_item_type
GROUP BY mmt.inventory_item_id,
p_org_id,
p_date,
p_quarter,
msi.item_type;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You already have those types in the IN clause and the source_id is commented out in the whree clause:
...
AND mmt.transaction_type_id IN (35, 33, 62, 34,31, 41)
--and mmt.transaction_source_id=
...
What are you wanting to do? If you have added them and it isn't working, please clarify.