select a.lot_number,
a.inventory_item_id,
a.item_name item_name,
a.description,
sal_dtls.mon1_val,
sal_dtls.mon2_val,
sal_dtls.mon3_val,
sal_dtls.avg_sal,
(case when nvl(avg_sal,0)=0 --or avg_sal is null
then 'No Sale' else to_char(round(((case when a.nosale_count=0 then sum(value) else null end)/(case when sal_dtls.avg_sal=0 then 1 else nvl(sal_dtls.avg_sal,1)end)*24),2)) end) stock_days,
sum(transaction_quantity) transaction_quantity,
TRANSACTION_UOM,
case when a.nosale_count=0 then new_cost else 0 end new_cost,
case when a.nosale_count=0 then sum(value) else 0 end value,
sum(Q30) Q30,
case when nosale_count=0 then sum(V30) else 0 end V30,
sum(Q31to60) Q31to60,
case when nosale_count=0 then sum(V31to60) else 0 end V31to60,
sum(Q61to90) Q61to90,
case when nosale_count=0 then sum(V61to90) else 0 end V61to90,
sum(Q91to120) Q91to120,
case when nosale_count=0 then sum(V91to120) else 0 end V91to120,
sum(Q121to150) Q121to150,
case when nosale_count=0 then sum(V121to150) else 0 end V121to150,
sum(Q151to180) Q151to180,
case when nosale_count=0 then sum(V151to180) else 0 end V151to180,
sum(Q181to365) Q181to365,
case when nosale_count=0 then sum(V181to365) else 0 end V181to365,
sum(Q366to730) Q366to730,
case when nosale_count=0 then sum(V366to730) else 0 end V366to730,
sum(Q730) Q730,
case when nosale_count=0 then sum(V730) else 0 end V730,
a.organization_id,
a.organization_name,
a.subinventory_code,
a.date_received,
a.create_transaction_id,
a.attribute1,
a.short_code,
a.segment1,
a.segment2 ,
a.segment3,
a.segment4 ,
a.organization_code,
a.create_transaction_id,Inspection_status,
a.transaction_id mmt_transaction_id,
(select pjpb.period_name from apps.pure_jc_periods pjpa, apps.pure_jc_periods pjpb where trunc(sysdate) between pjpa.start_date and pjpa.end_date and to_number(substr(pjpb.period_name,4))=to_number(ltrim(pjpa.period_name,'JC-'))-3) cf_1,
(select pjpb.period_name from apps.pure_jc_periods pjpa, apps.pure_jc_periods pjpb where trunc(sysdate) between pjpa.start_date and pjpa.end_date and to_number(substr(pjpb.period_name,4))=to_number(ltrim(pjpa.period_name,'JC-'))-2) cf_2,
(select pjpb.period_name from apps.pure_jc_periods pjpa, apps.pure_jc_periods pjpb where trunc(sysdate) between pjpa.start_date and pjpa.end_date and to_number(substr(pjpb.period_name,4))=to_number(ltrim(pjpa.period_name,'JC-'))-1) cf_3,
(select mln.origination_date from apps.mtl_lot_numbers mln where mln.organization_id=a.organization_id and mln.inventory_item_id=a.inventory_item_id and mln.lot_number=a.lot_number) cp_grn_date,
(select mln.expiration_date from apps.mtl_lot_numbers mln where mln.organization_id=a.organization_id and mln.inventory_item_id=a.inventory_item_id and mln.lot_number=a.lot_number) cp_lot_exp_date,
(select max(pla.attribute11) from apps.po_lines_all pla, apps.po_line_locations_all plla, apps.rcv_lot_transactions rlt, apps.rcv_shipment_lines rsl, apps.rcv_transactions rt, apps.mtl_material_transactions mmt where pla.po_line_id=plla.po_line_id and pla.item_id=a.inventory_item_id and plla.ship_to_organization_id=a.organization_id and pla.item_id=rlt.item_id and rlt.lot_num=a.lot_number and rlt.shipment_line_id=rsl.shipment_line_id and pla.item_id=rsl.item_id and rsl.shipment_line_id=rt.shipment_line_id and rlt.shipment_line_id=rt.shipment_line_id and rlt.transaction_id=rt.transaction_id and rt.transaction_type='DELIVER' and pla.po_header_id=rt.po_header_id and pla.po_line_id=rt.po_line_id and rt.transaction_id=mmt.rcv_transaction_id and mmt.transaction_id=a.transaction_id) cp_purchase_type,
case when :user_id in (1,2) and nvl(:p_de_type,'N')='Y' and upper(a.segment2) like 'RAW%MAT%' then nvl(nvl((select max(msib.description) from apps.bom_bill_of_materials bbom, apps.bom_inventory_components bic, apps.mtl_system_items_b msib where bbom.organization_id=msib.organization_id and msib.organization_id=a.organization_id and bbom.assembly_item_id=a.inventory_item_id and bbom.bill_sequence_id=bic.bill_sequence_id and bic.component_item_id=msib.inventory_item_id and bic.attribute2='D'),(select max(msib.description) from apps.bom_bill_of_materials bbom, apps.bom_inventory_components bic, apps.mtl_system_items_b msib where bbom.organization_id=msib.organization_id and bbom.assembly_item_id=a.inventory_item_id and bbom.bill_sequence_id=bic.bill_sequence_id and bic.component_item_id=msib.inventory_item_id and bic.attribute2='D')),a.description) else a.description end cp_original_rm_item_desc
from
-- Roopa
(SELECT A.lot_number, --2391 135 585
(SELECT MIN (mmt.transaction_id)
FROM apps.mtl_material_transactions mmt,
apps.mtl_transaction_lot_numbers mtl,
apps.mtl_onhand_quantities moh
WHERE mmt.transaction_id = mtl.transaction_id
AND mmt.inventory_item_id = moh.inventory_item_id
AND mmt.organization_id = moh.organization_id
AND mtl.lot_number = moh.lot_number
AND mtl.product_code = 'RCV' -- Roopa
AND mmt.inventory_item_id = a.inventory_item_id
AND mmt.organization_id = a.organization_id
AND mtl.lot_number = a.lot_number) transaction_id,
A.inventory_item_id,
c.segment1 item_name,
c.description,
A.transaction_quantity,
B.TRANSACTION_UOM,
-- b.actual_cost as new_cost,
cic.item_cost new_cost,
A.transaction_quantity*cic.item_cost as value,
case when (trunc(sysdate) - (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id)))<=30 then A.transaction_quantity end as Q30,
case when (trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id)))<=30 then
(a.transaction_quantity * cic.item_cost) end as V30,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 31 and 60) then A.transaction_quantity end as Q31to60,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 31 and 60) then
(a.transaction_quantity * cic.item_cost) end as V31to60,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 61 and 90) then A.transaction_quantity end as Q61to90,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 61 and 90) then
(a.transaction_quantity * cic.item_cost) end as V61to90,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 91 and 120) then A.transaction_quantity end as Q91to120,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 91 and 120) then
(a.transaction_quantity * cic.item_cost) end as V91to120,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 121 and 150) then A.transaction_quantity end as Q121to150,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 121 and 150) then
(a.transaction_quantity * cic.item_cost) end as V121to150,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 151 and 180) then A.transaction_quantity end as Q151to180,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 151 and 180) then
(a.transaction_quantity * cic.item_cost) end as V151to180,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 181 and 365) then A.transaction_quantity end as Q181to365,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 181 and 365) then
(a.transaction_quantity * cic.item_cost) end as V181to365,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 366 and 730) then A.transaction_quantity end as Q366to730,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) between 366 and 730) then
(a.transaction_quantity * cic.item_cost) end as V366to730,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) >730) then A.transaction_quantity end as Q730,
case when ((trunc(sysdate)- (select trunc(nvl(ORIGINATION_DATE,TRANSACTION_DATE)) from apps.mtl_transaction_lot_numbers
where rownum = 1
and transaction_id = (select min(transaction_id) from apps.mtl_transaction_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id))) >730) then
(a.transaction_quantity * cic.item_cost) end as V730,
A.organization_id,
d.organization_name,
A.subinventory_code,
case when msi.ASSET_INVENTORY=2
then 1
else 0
end nosale_count,
A.date_received,
A.create_transaction_id,c.attribute1,hou.short_code,mc.segment1,
mc.segment2,mc.segment3,mc.segment4,d.organization_code,
(select ATTRIBUTE3 from apps.mtl_lot_numbers
where lot_number = a.lot_number
and inventory_item_id = a.inventory_item_id
and organization_id = a.organization_id)Inspection_status --added by sudha
FROM apps.mtl_onhand_quantities A,
apps.mtl_material_transactions B,
apps.mtl_system_items c,
apps.mtl_secondary_inventories msi,
apps.ORG_ORGANIZATION_DEFINITIONS d,
apps.hr_operating_units hou,
apps.mtl_item_categories mic,
apps.mtl_categories mc,
apps.mtl_category_sets mcs,
-- (SELECT b.transaction_date,a.ids,a.inventory_item_id, a.organization_id,
-- a.lot_number
-- FROM
-- (SELECT inventory_item_id, organization_id, lot_number,
-- MIN (ids) AS ids
-- FROM
-- (SELECT MIN (mmt.transaction_id) AS ids,
-- mmt.inventory_item_id, mmt.organization_id,
-- mtl.lot_number
-- FROM mtl_material_transactions mmt,
-- mtl_transaction_lot_numbers mtl,
-- mtl_onhand_quantities moh
-- WHERE mmt.transaction_id = mtl.transaction_id
-- AND mmt.inventory_item_id = moh.inventory_item_id
-- AND mmt.organization_id = moh.organization_id
-- AND mtl.lot_number = moh.lot_number
-- AND mtl.product_code = 'RCV' -- Roopa
-- GROUP BY mmt.inventory_item_id,
-- mmt.organization_id,
-- mtl.lot_number) a,
-- GROUP BY inventory_item_id, organization_id, lot_number) a,
-- mtl_material_transactions b
-- WHERE a.ids = b.transaction_id)DD,
--mtl_system_items_b msib,
apps.CST_ITEM_COSTS cic
WHERE a.inventory_item_id = b.inventory_item_id
and a.organization_id = b.organization_id
and a.subinventory_code = b.subinventory_code
and a.create_transaction_id = b.transaction_id
and b.inventory_item_id = c.inventory_item_id
and b.organization_id = c.organization_id
and c.enabled_flag='Y'
and msi.organization_id=a.organization_id
and msi.SECONDARY_INVENTORY_NAME=a.subinventory_code
and a.organization_id = d.organization_id
and d.operating_unit = hou.organization_id
and c.inventory_item_id = mic.inventory_item_id
and c.organization_id = mic.organization_id
and mic.category_id = mc.category_id
and mic.category_set_id=mcs.category_set_id
and mcs.structure_id=mc.STRUCTURE_ID
and mcs.CATEGORY_SET_NAME='Pure Item Category'
-- AND DD.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
-- AND DD.ORGANIZATION_ID = A.ORGANIZATION_ID
-- AND DD.LOT_NUMBER = A.LOT_NUMBER
--and dd.inventory_item_id = msib.inventory_item_id
--and dd.organization_id = msib.organization_id
--------------Added by vignesh---------------------------------
and 1=1
and cic.organization_id = a.organization_id
and cic.inventory_item_id = a.inventory_item_id
------------------------------------------------------
--and msib.segment1= NVL(:p_item, msib.segment1)
AND ( (:user_id = 1 AND nvl(mc.segment2,'N') = nvl(mc.segment2,'N'))
OR (:user_id = 2 AND NVL (mc.segment2, 'N') != 'Raw Material'))
) a,
--Roopa
(select
short_code,
organization_name,
segment1,
description,
sum(case when pivot in (1,2,3) then (value) else null end)/3 avg_sal,
sum(decode(pivot,3,value,null)) mon1_val,
sum(decode(pivot,2,value,null)) mon2_val,
sum(decode(pivot,1,value,null)) mon3_val from
(
select hou.short_code,ood.organization_name,msi.segment1,msi.description,pjp.period_name,
--dense_rank() over (order by to_number(substr(pjp.period_name,4)) desc) pivot,
dense_rank() over (order by to_number(ltrim(pjp.period_name,'JC-')) desc) pivot,
--sum(case when ola.line_category_code ='RETURN' then ola.ordered_quantity *-1 else ola.ordered_quantity end ) qty,
--sum(case when ola.line_category_code ='RETURN' then ola.unit_selling_price*-1 else ola.unit_selling_price end) value
--sum(
case when ola.line_category_code ='RETURN' then ((ola.ordered_quantity*ola.unit_selling_price)*-1)
else (ola.ordered_quantity * ola.unit_selling_price) end
-- )
value
from
apps.oe_order_headers_all oha,
apps.oe_order_lines_all ola,
apps.pure_jc_periods pjp,
apps.pure_jc_periods pjp1,
apps.mtl_system_items msi,
apps.org_organization_definitions ood,
apps.hr_operating_units hou,
apps.ar_customers arc,
apps.hz_customer_profiles hcp,
apps.ar_collectors ac
where 1 = 1
and oha.header_id = ola.header_id
and oha.cancelled_flag <>'Y'
and oha.org_id = hou.organization_id
and trunc(oha.ordered_date) < pjp1.start_date
and trunc(oha.ordered_date) between pjp.start_date and pjp.end_date
and pjp.start_date < pjp1.start_date
and trunc(sysdate) between pjp1.start_date and pjp1.end_date
and trunc(oha.ordered_date) between trunc(sysdate-120) and trunc(sysdate)
and ola.inventory_item_id = msi.inventory_item_id
and ola.ship_from_org_id = msi.organization_id
and ood.organization_id = msi.organization_id
and hou.organization_id = ood.operating_unit
and oha.sold_to_org_id = arc.customer_id
and hcp.site_use_id = oha.invoice_to_org_id
and hcp.cust_account_id = arc.customer_id
and hcp.collector_id = ac.collector_id
and hcp.status = 'A'
and ac.name <> 'GROUP COMPANY'
AND NOT EXISTS (
SELECT
rcta.*
FROM
apps.ra_customer_trx_all rcta,
apps.ra_customer_trx_lines_all rctla
WHERE 1 = 1
AND ola.header_id = oha.header_id
AND rctla.customer_trx_id = rcta.customer_trx_id
AND rctla.line_type = 'LINE'
AND TO_CHAR(oha.order_number) = rcta.interface_header_attribute1
AND oha.sold_to_org_id = rcta.sold_to_customer_id
AND oha.ship_from_org_id =
CASE WHEN rctla.interface_line_context = 'INTERCOMPANY' THEN rctla.interface_line_attribute3
ELSE rctla.interface_line_attribute10
END
--AND ola.line_id = rctla.interface_line_attribute6)
AND TO_CHAR(ola.line_id) = rctla.interface_line_attribute6)
--group by
--hou.short_code,ood.organization_name,msi.segment1,msi.description,pjp.period_name
union all
select hou.short_code,
ood.organization_name,
msi.segment1,
msi.description,
pjp.period_name,
dense_rank() over (order by to_number(ltrim(pjp.period_name,'JC-')) desc) pivot,
--dense_rank() over (order by to_number(substr(pjp.period_name,4)) desc) pivot,
-- sum (
CASE WHEN
rrt.type = 'CM'
THEN (rctla.quantity_invoiced *rctla.unit_selling_price)*-1
ELSE (rctla.quantity_invoiced *rctla.unit_selling_price)
END
-- )
value
from
apps.ra_customer_trx_all rcta,
apps.ra_customer_trx_lines_all rctla,
apps.pure_jc_periods pjp,
apps.pure_jc_periods pjp1,
apps.mtl_system_items msi,
apps.org_organization_definitions ood,
apps.hr_operating_units hou,
apps.ar_customers arc,
apps.hz_customer_profiles hcp,
apps.ra_cust_trx_types_all rrt,
apps.ar_collectors ac
where 1 = 1
and rrt.cust_trx_type_id = rcta.cust_trx_type_id
and rcta.customer_trx_id = rctla.customer_trx_id
and rcta.org_id = hou.organization_id
and trunc(rcta.trx_date) < pjp1.start_date
and trunc(rcta.trx_date) between pjp.start_date and pjp.end_date
and pjp.start_date < pjp1.start_date
and trunc(sysdate) between pjp1.start_date and pjp1.end_date
and trunc(rcta.trx_date) between trunc(sysdate-120) and trunc(sysdate)
and rctla.inventory_item_id = msi.inventory_item_id
and ood.operating_unit = hou.organization_id
and nvl(rctla.warehouse_id,(CASE WHEN rctla.interface_line_context = 'INTERCOMPANY' THEN rctla.interface_line_attribute3
ELSE rctla.interface_line_attribute10
END)) = msi.organization_id
and ood.organization_id = msi.organization_id
and rcta.bill_to_customer_id = arc.customer_id
and hcp.site_use_id = rcta.bill_to_site_use_id
and hcp.cust_account_id = arc.customer_id
and hcp.collector_id = ac.collector_id
and ac.name <> 'GROUP COMPANY'
and hcp.status = 'A'
AND rctla.line_type = 'LINE'
--group by
--hou.short_code,ood.organization_name,msi.segment1,msi.description,pjp.period_name
--order by period_name
) a
where 1= 1
--and short_code = 'PPC'
--and period_name ='JC-7'
--and organization_name ='PPC - Ponniammanmedu'
--and segment1='TRACMSBR00220005'
group by
short_code,
organization_name,
segment1,
description) sal_dtls
-- Roopa
where 1 = 1
and a.item_name = sal_dtls.segment1(+)
and a.short_code = sal_dtls.short_code(+)
--and inventory_item_id = 1456
/*AND a.inventory_item_id=95875*/
and a.organization_name = sal_dtls.organization_name(+)
group by a.lot_number,
a.inventory_item_id,
a.item_name,
a.description,
a.new_cost,
a.TRANSACTION_UOM,
a.organization_id,
a.organization_name,
a.subinventory_code,
a.nosale_count,
a.date_received,
a.create_transaction_id,
a.attribute1,
a.short_code,
a.segment1,
a.segment2,
a.segment3,
a.segment4,
sal_dtls.mon1_val,
sal_dtls.mon2_val,
sal_dtls.mon3_val,
sal_dtls.avg_sal,
a.organization_code,Inspection_status,
a.transaction_id -- Roopa |
