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.
170 lines
5.2 KiB
Plaintext
170 lines
5.2 KiB
Plaintext
Select
|
|
PB.COMPANY
|
|
,PB.CONTRACT
|
|
,C150.ATTR_VALUE_ALPHA "Part Commodity"
|
|
,C034.ATTR_VALUE_ALPHA "Part Decoration"
|
|
,IP.TYPE_DESIGNATION "Type Designation"
|
|
,PB.CATALOG_NO
|
|
,PB.CATALOG_DESC "Part Description"
|
|
,PB.REAL_SHIP_DATE
|
|
,PB.ORDER_NO
|
|
,PB.LINE_NO
|
|
,PB.REL_NO
|
|
,PB.CUSTOMER_NO
|
|
,PB.NAME "Customer Name"
|
|
,PB.Qty
|
|
,C150L.ATTR_VALUE_ALPHA "Level Part Commodity"
|
|
,C034L.ATTR_VALUE_ALPHA "Level Part Decoration"
|
|
,"Level"
|
|
,IP2.TYPE_DESIGNATION "Level Part Type Designation"
|
|
,PB.Level_Part_No
|
|
,IP2.Description "Level Part Description"
|
|
,PB.Qty_Extended "Qty Extended"
|
|
|
|
|
|
From
|
|
(
|
|
Select
|
|
CS.COMPANY
|
|
,CO.CONTRACT
|
|
,CO.CATALOG_NO
|
|
,CO.CATALOG_DESC
|
|
,CO.REAL_SHIP_DATE
|
|
,CO.ORDER_NO
|
|
,CO.LINE_NO
|
|
,CO.REL_NO
|
|
,CO.CUSTOMER_NO
|
|
,CU.NAME
|
|
,DECODE(CO.CATALOG_TYPE_DB, 'INV', CO.QTY_SHIPPED, NULL) Qty
|
|
,CO.CATALOG_NO Level_Part_No
|
|
,DECODE(CO.CATALOG_TYPE_DB, 'INV', CO.QTY_SHIPPED, NULL) Qty_Extended
|
|
,0 as "Level"
|
|
|
|
From CUSTOMER_ORDER_LINE CO
|
|
inner join COMPANY_SITE CS on CS.CONTRACT=CO.CONTRACT and CO.STATE in ('Delivered','Partially Delivered','Invoiced/Closed')
|
|
inner join CUSTOMER_INFO CU on CU.CUSTOMER_ID=CO.CUSTOMER_NO
|
|
|
|
Where CS.COMPANY in '&Company' and CO.Contract like nvl ('&Contract','%') and CATALOG_NO like trim(nvl ('&Part_No','%')) and trunc(CO.REAL_SHIP_DATE) BETWEEN TO_DATE('&DATE_FROM','YYYY-MM-DD') AND TO_DATE ('&DATE_TO','YYYY-MM-DD')
|
|
and length(CO.CUSTOMER_NO)>5
|
|
|
|
|
|
UNION
|
|
|
|
|
|
|
|
Select
|
|
CS.COMPANY
|
|
,CO.CONTRACT
|
|
,CO.CATALOG_NO
|
|
,CO.CATALOG_DESC
|
|
,CO.REAL_SHIP_DATE
|
|
,CO.ORDER_NO
|
|
,CO.LINE_NO
|
|
,CO.REL_NO
|
|
,CO.CUSTOMER_NO
|
|
,CU.NAME
|
|
,DECODE(CO.CATALOG_TYPE_DB, 'INV', CO.QTY_SHIPPED, NULL) Qty
|
|
,ML.Component_Part Level_Part_No
|
|
,DECODE(CO.CATALOG_TYPE_DB, 'INV', CO.QTY_SHIPPED, NULL)*ML.Qty_Per_Extended Qty_Extended
|
|
,Level_ as "Level"
|
|
|
|
From CUSTOMER_ORDER_LINE CO
|
|
inner join COMPANY_SITE CS on CS.CONTRACT=CO.CONTRACT and CO.STATE in ('Delivered','Partially Delivered','Invoiced/Closed')
|
|
inner join CUSTOMER_INFO CU on CU.CUSTOMER_ID=CO.CUSTOMER_NO
|
|
inner join
|
|
(
|
|
Select
|
|
RC.contract
|
|
,RC.Root_Part_No
|
|
,IP.Description "Description_Root_Part_No"
|
|
,RC.Level_
|
|
,RC.PART_NO
|
|
,substr(RC.Chemin,2,100) Structure_Path
|
|
,RC.Component_Part
|
|
,RC.QTY_PER_ASSEMBLY
|
|
,RC.Qty_Per_Extended
|
|
|
|
from ifsapp.Inventory_Part IP
|
|
inner join
|
|
|
|
(
|
|
Select
|
|
MS.Contract
|
|
,CONNECT_BY_ROOT MS.PART_NO Root_Part_No
|
|
,LEVEL Level_
|
|
,MS.PART_NO
|
|
,MS.COMPONENT_PART
|
|
,SYS_CONNECT_BY_PATH(MS.PART_NO, '_') Chemin
|
|
,MS.QTY_PER_ASSEMBLY
|
|
,XMLQUERY ( ('1' || SYS_CONNECT_BY_PATH (MS.QTY_PER_ASSEMBLY, '*'))
|
|
RETURNING CONTENT
|
|
).getnumberval () AS Qty_Per_Extended
|
|
,A.Alternative_No
|
|
|
|
|
|
from
|
|
ifsapp.MANUF_STRUCTURE_cfv MS
|
|
inner join ifsapp.MANUF_STRUCT_ALTERNATE A
|
|
on MS.Contract=A.Contract
|
|
and MS.PART_NO=A.PART_NO and MS.ALTERNATIVE_NO=A.ALTERNATIVE_NO
|
|
and MS.ENG_CHG_LEVEL=A.ENG_CHG_LEVEL and MS.BOM_TYPE=A.BOM_TYPE
|
|
and A.STATE='Buildable'
|
|
inner join ifsapp.INVENTORY_PART IP on IP.Contract=MS.Contract and IP.Part_No=MS.Component_Part
|
|
inner join ifsapp.INVENTORY_PART_PLANNING IPP on IPP.Contract=MS.Contract and IPP.Part_No=MS.Component_Part
|
|
|
|
|
|
where MS.BOM_TYPE_DB='M'
|
|
and EFF_PHASE_OUT_DATE is null
|
|
and MS.Contract like nvl ('&Contract','%')
|
|
and CONNECT_BY_ROOT MS.PART_NO like nvl ('&Part_No','%')
|
|
and MS.ALTERNATIVE_NO ='*'
|
|
and IP.TYPE_CODE_DB in ('1','2')
|
|
and IP.PART_STATUS = 'A'
|
|
and IP.ACCOUNTING_GROUP in ('MFG','COMP')
|
|
and IPP.PLANNING_METHOD in ('A','G')
|
|
and MS.COMPONENT_PART not in ('1006628','1054478')
|
|
|
|
|
|
CONNECT BY
|
|
PRIOR MS.COMPONENT_PART=MS.PART_NO
|
|
and PRIOR MS.component_contract = MS.contract
|
|
and PRIOR MS.ALTERNATIVE_NO=MS.ALTERNATIVE_NO
|
|
and PRIOR A.STATE=A.STATE
|
|
)
|
|
RC on IP.Contract=RC.Contract and IP.PART_NO=RC.Root_Part_No
|
|
|
|
|
|
where IP.TYPE_CODE_DB in ('1','2') and IP.PART_STATUS = 'A' and IP.ACCOUNTING_GROUP in ('MFG','COMP')
|
|
) ML on ML.Contract=CO.Contract and ML.Root_Part_No=CO.Catalog_No
|
|
|
|
|
|
Where CS.COMPANY in '&Company' and CO.Contract like nvl ('&Contract','%') and CATALOG_NO like trim(nvl ('&Part_No','%')) and trunc(CO.REAL_SHIP_DATE) BETWEEN TO_DATE('&DATE_FROM','YYYY-MM-DD') AND TO_DATE ('&DATE_TO','YYYY-MM-DD')
|
|
and length(CO.CUSTOMER_NO)>5
|
|
|
|
|
|
) PB
|
|
|
|
inner join INVENTORY_PART IP on IP.Contract=PB.Contract and IP.Part_No=PB.CATALOG_No
|
|
inner join INVENTORY_PART IP2 on IP2.Contract=PB.Contract and IP2.Part_No=PB.Level_Part_No
|
|
|
|
|
|
OUTER APPLY (SELECT ATTR_VALUE_ALPHA FROM ifsapp.INVENTORY_PART_CHAR_ALL IPC WHERE CHARACTERISTIC_CODE='00150' and IPC.Part_No= PB.CATALOG_No AND IPC.CONTRACT=PB.CONTRACT) C150
|
|
|
|
OUTER APPLY (SELECT ATTR_VALUE_ALPHA FROM ifsapp.INVENTORY_PART_CHAR_ALL IPC WHERE CHARACTERISTIC_CODE='00150' and IPC.Part_No= PB.Level_Part_No AND IPC.CONTRACT=PB.CONTRACT) C150L
|
|
|
|
OUTER APPLY (SELECT ATTR_VALUE_ALPHA FROM ifsapp.INVENTORY_PART_CHAR_ALL IPC WHERE CHARACTERISTIC_CODE='00034' and IPC.Part_No= PB.CATALOG_No AND IPC.CONTRACT=PB.CONTRACT) C034
|
|
|
|
OUTER APPLY (SELECT ATTR_VALUE_ALPHA FROM ifsapp.INVENTORY_PART_CHAR_ALL IPC WHERE CHARACTERISTIC_CODE='00034' and IPC.Part_No= PB.Level_Part_No AND IPC.CONTRACT=PB.CONTRACT) C034L
|
|
|
|
Where
|
|
PB.Level_Part_No || '-' || "Level" not in ('1005100-2','1005100-3','1005100-4')
|
|
|
|
|
|
Order BY
|
|
Contract
|
|
,REAL_SHIP_DATE
|
|
,CUSTOMER_NO
|
|
,ORDER_NO
|
|
,LINE_NO
|
|
,REL_NO
|
|
,"Level" |