You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
364 lines
19 KiB
SQL
364 lines
19 KiB
SQL
Select *
|
|
from (Select Customer_No,
|
|
"Customer Name",
|
|
Company,
|
|
Site,
|
|
E1.Part_No,
|
|
Label_1,
|
|
Label_2,
|
|
Description,
|
|
Status,
|
|
Cat1,
|
|
Cat2,
|
|
Decoration,
|
|
Inventory,
|
|
Incoming_Qty,
|
|
"Oldest Lot",
|
|
"Open Order",
|
|
Safety,
|
|
"SO Qty",
|
|
Qty_Not_Reserved "Qty Not Reserved",
|
|
Remaining_Issue "Rem_Issue",
|
|
PO "PO Qty",
|
|
Next_Receipt_Date "PO Next Receipt Date",
|
|
X_Ref,
|
|
"X Ref Description",
|
|
E1.Supersedes,
|
|
E1.Superseded_By,
|
|
Related_Supersedes,
|
|
Related_Superseded
|
|
from (Select D.Customer_No,
|
|
CI.Name "Customer Name",
|
|
D.Contract Site,
|
|
D.Company,
|
|
D.Part_No,
|
|
pc.Description,
|
|
Inventory_Part_Status_Par_API.Get_Description(i.part_status) Status,
|
|
D.Cat1,
|
|
Cat2.Cat2,
|
|
Deco.Decoration,
|
|
X.X_Ref,
|
|
X.CATALOG_DESC "X Ref Description",
|
|
Q.QTY Inventory,
|
|
Q.Qty - Q.Qty_Reserved Qty_Not_Reserved,
|
|
Incoming_Qty,
|
|
i.supersedes,
|
|
INVENTORY_PART_API.Get_Superseded_By(i.CONTRACT,
|
|
i.PART_NO) Superseded_By,
|
|
Q.Last_Receipt_Date "Oldest Lot",
|
|
CO.Open_Order "Open Order",
|
|
IPP.Safety_Stock Safety,
|
|
WO.QTY "SO Qty",
|
|
Remaining_Issue,
|
|
D.Label_1,
|
|
D.Label_2,
|
|
PO.PO,
|
|
PO.Next_Receipt_Date,
|
|
D.Parent,
|
|
ROW_NUMBER() over(Partition by D.Contract, D.Part_No Order By D.Cat1) as Row_Num
|
|
From (Select C.Company,
|
|
i.Contract,
|
|
i.Part_No Parent,
|
|
i.Part_No,
|
|
C6.Label_1,
|
|
C6.Label_2,
|
|
ID.Customer_No,
|
|
'FG' as Cat1
|
|
From Inventory_Part i
|
|
inner join COMPANY_SITE CS
|
|
on i.Contract = CS.Contract
|
|
inner join Company_Finance C
|
|
on C.Company = CS.Company
|
|
inner join (Select PART_NO,
|
|
CONTRACT,
|
|
ATTR_VALUE_ALPHA Customer_No
|
|
from INVENTORY_PART_CHAR_ALL
|
|
where CHARACTERISTIC_CODE = '00032'
|
|
and ATTR_VALUE_ALPHA = '&Customer_ID') ID
|
|
on i.Contract = ID.Contract
|
|
and i.Part_No = ID.Part_No
|
|
|
|
left join (Select ms.Part_No,
|
|
ms.Contract,
|
|
Min(COMPONENT_PART) Label_1,
|
|
Decode(Min(COMPONENT_PART),
|
|
Max(Component_Part),
|
|
'',
|
|
Max(Component_Part)) Label_2
|
|
From MANUF_STRUCTURE MS
|
|
inner join INVENTORY_PART_CHAR_ALL c
|
|
on ms.contract = c.contract
|
|
and ms.component_part = c.part_no
|
|
|
|
where MANUF_STRUCT_ALTERNATE_API.Get_State(ms.contract,
|
|
ms.part_no,
|
|
ms.eng_chg_level,
|
|
ms.bom_type,
|
|
ms.alternative_no) =
|
|
'Buildable'
|
|
and ms.ALTERNATIVE_NO = '*'
|
|
and ms.EFF_PHASE_OUT_DATE is null
|
|
and ms.BOM_TYPE_DB = 'M'
|
|
and c.CHARACTERISTIC_CODE = '00006'
|
|
and c.ATTR_VALUE_ALPHA in
|
|
('IML',
|
|
'PAPER INSERT',
|
|
'HEAT TRANSFER',
|
|
'PSL')
|
|
group by ms.Part_No, ms.Contract) C6
|
|
on i.contract = c6.contract
|
|
and i.part_no = c6.part_no
|
|
|
|
Where i.ENG_ATTRIBUTE in ('00008', '00010')
|
|
and i.PART_STATUS in ('A', 'D', 'Q')
|
|
|
|
UNION
|
|
|
|
Select A.Company,
|
|
A.Contract,
|
|
B.Part_No Parent,
|
|
B.Component_Part Part_No,
|
|
B.Component_Part Label_1,
|
|
'' as Label_2,
|
|
A.Customer_No,
|
|
'RM' as Cat1
|
|
from (Select C.Company,
|
|
i.Contract,
|
|
i.Part_No,
|
|
ID.Customer_No,
|
|
'FG' as Cat1
|
|
From Inventory_Part i
|
|
inner join COMPANY_SITE CS
|
|
on i.Contract = CS.Contract
|
|
inner join Company_Finance C
|
|
on C.Company = CS.Company
|
|
inner join (Select PART_NO,
|
|
CONTRACT,
|
|
ATTR_VALUE_ALPHA Customer_No
|
|
from INVENTORY_PART_CHAR_ALL
|
|
where CHARACTERISTIC_CODE =
|
|
'00032'
|
|
and ATTR_VALUE_ALPHA =
|
|
'&Customer_ID') ID
|
|
on i.Contract = ID.Contract
|
|
and i.Part_No = ID.Part_No
|
|
|
|
Where i.ENG_ATTRIBUTE in ('00008', '00010')
|
|
and i.PART_STATUS in ('A', 'D', 'Q')) A
|
|
|
|
inner join (Select Contract, Part_No, COMPONENT_PART
|
|
from MANUF_STRUCTURE M
|
|
Where M.BOM_TYPE_db = 'M'
|
|
and M.ALTERNATIVE_NO = '*'
|
|
and M.EFF_PHASE_OUT_DATE is null
|
|
and OPERATION_NO = '10'
|
|
and MANUF_STRUCT_ALTERNATE_API.Get_State(M.contract,
|
|
M.part_no,
|
|
M.eng_chg_level,
|
|
M.bom_type,
|
|
M.alternative_no) =
|
|
'Buildable') B
|
|
on A.Company = B.Contract
|
|
and A.Part_No = B.Part_No
|
|
|
|
inner join (Select Contract, PART_NO, ATTR_VALUE_ALPHA
|
|
from INVENTORY_PART_CHAR_ALL
|
|
where CHARACTERISTIC_CODE = '00006'
|
|
and ATTR_VALUE_ALPHA in
|
|
('IML',
|
|
'PAPER INSERT',
|
|
'HEAT TRANSFER',
|
|
'PSL')) C
|
|
on B.Contract = C.Contract
|
|
and B.Component_Part = C.Part_No
|
|
|
|
UNION
|
|
Select ic.Contract Company,
|
|
ic.Contract,
|
|
ic.Part_No Parent,
|
|
ic.Part_No,
|
|
'' as Label_1,
|
|
'' as Label_2,
|
|
ic.ATTR_VALUE_ALPHA Customer_No,
|
|
'XX' as Cat1
|
|
from INVENTORY_PART_CHAR_ALL ic
|
|
inner join Inventory_Part i
|
|
on ic.Contract = i.Contract
|
|
and ic.Part_No = i.Part_No
|
|
where CHARACTERISTIC_CODE = '00032'
|
|
and ATTR_VALUE_ALPHA = '&Customer_ID'
|
|
and i.Part_Status in ('A', 'D', 'Q')
|
|
|
|
) D
|
|
|
|
inner join CUSTOMER_INFO CI
|
|
on D.Customer_No = CI.Customer_ID
|
|
inner join Inventory_part i
|
|
on D.Contract = i.Contract
|
|
and D.Part_No = i.Part_No
|
|
inner join Part_Catalog pc
|
|
on D.Part_No = pc.Part_No
|
|
inner join Inventory_Part_Planning IPP
|
|
on D.Contract = IPP.Contract
|
|
and D.Part_NO = IPP.Part_No
|
|
|
|
left join (Select Contract,
|
|
Catalog_No,
|
|
CUSTOMER_PART_NO X_Ref,
|
|
CATALOG_DESC
|
|
from SALES_PART_CROSS_REFERENCE) x
|
|
on D.Contract = X.Contract
|
|
and D.Part_No = X.Catalog_No
|
|
|
|
left join (Select C.Part_No,
|
|
C.Contract,
|
|
sum(C.BUY_QTY_DUE - C.QTY_SHIPPED) Open_Order
|
|
From customer_order_join_cfv C
|
|
Where C.objstate in
|
|
('Released',
|
|
'Blocked',
|
|
'Reserved',
|
|
'Picked',
|
|
'PartiallyDelivered')
|
|
and C.Customer_No not like ('W%')
|
|
Group By C.Part_No, C.Contract) CO
|
|
on D.Part_No = CO.Part_No
|
|
and D.Contract = CO.Contract
|
|
|
|
left join (Select S.Contract,
|
|
S.Part_No,
|
|
sum(S.QTY_ONHAND) QTY,
|
|
sum(S.QTY_RESERVED) Qty_Reserved,
|
|
TO_CHAR(min(S.RECEIPT_DATE), 'yyyy-mm-dd') Last_Receipt_Date
|
|
From Inventory_part_in_stock_UIV S
|
|
inner join Inventory_Part i
|
|
on S.Contract = i.Contract
|
|
and S.Part_No = i.Part_No
|
|
Where S.QTY_ONHAND > 0
|
|
Group by S.Contract, S.Part_No) Q
|
|
on D.Part_NO = Q.Part_NO
|
|
and D.Contract = Q.Contract
|
|
|
|
left join (Select SOURCE_PART_NO,
|
|
CONTRACT,
|
|
sum(DISPATCH_SOURCE_QTY) Incoming_Qty
|
|
From RECEIVABLE_DISPATCH_ADV_LINE
|
|
Where STATE = 'Created'
|
|
Group by SOURCE_PART_NO, CONTRACT) r
|
|
on r.contract = d.contract
|
|
and r.SOURCE_PART_NO = d.part_no
|
|
|
|
left join (Select Contract,
|
|
Part_No,
|
|
sum(REMAINING_NET_SUPPLY_QTY) QTY
|
|
from Shop_Ord
|
|
where State in ('Released',
|
|
'Started',
|
|
'Parked',
|
|
'Reserved')
|
|
and order_code_db = 'M'
|
|
Group By Contract, Part_No) WO
|
|
on D.Contract = WO.Contract
|
|
and D.Part_No = WO.Part_No
|
|
|
|
left join (Select Contract,
|
|
Part_No,
|
|
sum(qty_remain_to_issue) Remaining_Issue
|
|
from SHOP_MATERIAL_ALLOC_UIV
|
|
where SOSTATE in ('Released',
|
|
'Started',
|
|
'Parked',
|
|
'Reserved')
|
|
and order_code_db = 'M'
|
|
Group By Contract, Part_No) SM
|
|
on D.Contract = SM.Contract
|
|
and D.Part_No = SM.Part_No
|
|
|
|
left join (Select distinct Contract,
|
|
Part_No,
|
|
CHARACTERISTIC_TEMPLATE_API.GET_DESCRIPTION(INVENTORY_PART_API.Get_Eng_Attribute(contract,
|
|
PART_NO)) Cat2
|
|
from INVENTORY_PART_CHAR_ALL) Cat2
|
|
on Cat2.Contract = D.Contract
|
|
and Cat2.Part_No = D.Part_No
|
|
|
|
left join (Select distinct Contract,
|
|
Part_No,
|
|
ATTR_VALUE_ALPHA Decoration
|
|
from INVENTORY_PART_CHAR_ALL
|
|
where CHARACTERISTIC_CODE = '00034') Deco
|
|
on Deco.Contract = D.Contract
|
|
and Deco.Part_No = D.Part_No
|
|
|
|
left join (SELECT Contract,
|
|
part_No,
|
|
sum(CF$_REM_QTY_RECEIVE) PO,
|
|
TO_CHAR(min(PLANNED_RECEIPT_DATE),
|
|
'yyyy-mm-dd') Next_Receipt_Date
|
|
FROM purchase_order_line_all_cfv
|
|
WHERE state in ('Released', 'Confirmed')
|
|
GROUP BY Contract, Part_No) PO
|
|
on PO.Contract = D.Contract
|
|
and PO.Part_No = D.Part_No
|
|
|
|
) E1
|
|
|
|
left join (Select Q1.*, PS2.Part_No Related_Supersedes
|
|
from (Select PS.Contract,
|
|
PS.Part_No,
|
|
PS.COMPONENT_PART,
|
|
IP.SUPERSEDES,
|
|
INVENTORY_PART_API.Get_Superseded_By(IP.CONTRACT,
|
|
IP.PART_NO) Superseded_By_Comp
|
|
From PROD_STRUCTURE PS
|
|
inner join Inventory_part_cfv IP
|
|
on PS.Contract = IP.Contract
|
|
and PS.COMPONENT_PART = IP.Part_No
|
|
Where PS.EFF_PHASE_OUT_DATE is null
|
|
and PS.BOM_TYPE_db = 'M'
|
|
and IP.CF$_PURCHASE_CAT2 in
|
|
('RM07A - IML Label',
|
|
'RM04P - CARDBOARD INSERT - NO COST',
|
|
'RM07B - PSL Label')) Q1
|
|
|
|
inner join PROD_STRUCTURE PS2
|
|
on Q1.Contract = PS2.Contract
|
|
and Q1.Superseded_By_Comp = PS2.Component_Part
|
|
|
|
where PS2.EFF_PHASE_OUT_DATE is null
|
|
and PS2.BOM_TYPE_db = 'M') S1
|
|
on E1.Part_No = S1.Part_No
|
|
and E1.Site = S1.Contract
|
|
|
|
left join (Select Q1.*, PS2.Part_No Related_Superseded
|
|
from (Select PS.Contract,
|
|
PS.Part_No,
|
|
PS.COMPONENT_PART,
|
|
IP.SUPERSEDES,
|
|
INVENTORY_PART_API.Get_Superseded_By(IP.CONTRACT,
|
|
IP.PART_NO) Superseded_By_Comp
|
|
|
|
from PROD_STRUCTURE PS
|
|
inner join Inventory_part_cfv IP
|
|
on PS.Contract = IP.Contract
|
|
and PS.COMPONENT_PART = IP.Part_No
|
|
|
|
Where PS.EFF_PHASE_OUT_DATE is null
|
|
and PS.BOM_TYPE_db = 'M'
|
|
and IP.CF$_PURCHASE_CAT2 in
|
|
('RM07A - IML Label',
|
|
'RM04P - CARDBOARD INSERT - NO COST',
|
|
'RM07B - PSL Label')) Q1
|
|
inner join PROD_STRUCTURE PS2
|
|
on Q1.Contract = PS2.Contract
|
|
and Q1.Supersedes = PS2.Component_Part
|
|
where PS2.EFF_PHASE_OUT_DATE is null
|
|
and PS2.BOM_TYPE_db = 'M') S2
|
|
on E1.Part_No = S2.Part_No
|
|
and E1.Site = S2.Contract
|
|
|
|
Where Row_Num = 1
|
|
and COMPANY LIKE NVL('&COMPANY', '%')
|
|
Order By Parent, Label_1, Cat1, Site)
|
|
|
|
-- Lov reference is removed due to limitations in IFS cloud |