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.
88 lines
3.5 KiB
Plaintext
88 lines
3.5 KiB
Plaintext
Select H.Contract Site,
|
|
H.PART_NO,
|
|
IP.Description,
|
|
H.Stat_Year_NO || '-' || TO_CHAR(H.Stat_Period_No, '00') Period,
|
|
H.Beg_Balance Begenning_Balance,
|
|
H.count_abnormal_issues,
|
|
H.count_adjust,
|
|
H.count_issues,
|
|
H.count_issues_with_abnormal,
|
|
H.count_receipts,
|
|
H.mtd_abnormal_issues,
|
|
H.mtd_adjust,
|
|
H.mtd_issues,
|
|
H.mtd_issues_with_abnormal,
|
|
H.mtd_receipts,
|
|
H.qty_onhand "Qty on Hand",
|
|
IPP.SAFETY_STOCK "Safety Stock",
|
|
IPP.MIN_ORDER_QTY "Min Order Qty",
|
|
IPP.MAX_ORDER_QTY "Max Order Qty",
|
|
IPP.CF$_HU_QUANTITY "HU Qty",
|
|
IPP.CF$_MIN_SALES_QTY "Min Sales Qty",
|
|
IP.TYPE_DESIGNATION,
|
|
IP.ACCOUNTING_GROUP,
|
|
IP.PART_PRODUCT_CODE,
|
|
IP.PART_PRODUCT_FAMILY,
|
|
IP.PART_STATUS,
|
|
IP.TYPE_CODE,
|
|
PP.CF$_PURCHASE_PART_CAT_1,
|
|
PP.CF$_PURCHASE_PART_CAT_2
|
|
|
|
From Inventory_Part_period_hist H
|
|
inner join Inventory_Part_cfv IP
|
|
on H.Contract = IP.Contract
|
|
and H.Part_No = IP.Part_No
|
|
left join Purchase_Part_cfv PP
|
|
on H.Contract = PP.Contract
|
|
and H.Part_No = PP.Part_No
|
|
inner join INVENTORY_PART_PLANNING_cfv IPP
|
|
on H.Contract = IPP.Contract
|
|
and H.Part_No = IPP.Part_No
|
|
|
|
Where H.CONTRACT LIKE '%&Site%'
|
|
|
|
and (H.PART_NO LIKE NVL('&[-C--L]Part_No', '%') or
|
|
H.PART_NO in
|
|
(SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_
|
|
FROM (SELECT '&[-C--L]PART_NO' AS str FROM DUAL) d__
|
|
CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1))
|
|
|
|
and TO_DATE((H.Stat_Year_NO || '-' || TO_CHAR(H.Stat_Period_No, '00')),
|
|
'YYYY-MM') Between TO_DATE('&From_Year_Period', 'YYYY-MM') and
|
|
TO_DATE('&To_Year_Period', 'YYYY-MM')
|
|
|
|
and (IP.ACCOUNTING_GROUP LIKE NVL('&[-C--L]ACCOUNTING_GROUP', '%') or
|
|
IP.ACCOUNTING_GROUP in
|
|
(SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_
|
|
FROM (SELECT '&[-C--L]ACCOUNTING_GROUP' AS str FROM DUAL) d__
|
|
CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1))
|
|
|
|
and (IP.PART_PRODUCT_CODE LIKE NVL('&[-C--L]PART_PRODUCT_CODE', '%') or
|
|
IP.PART_PRODUCT_CODE in
|
|
(SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_
|
|
FROM (SELECT '&[-C--L]PART_PRODUCT_CODE' AS str FROM DUAL) d__
|
|
CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1))
|
|
|
|
and (IP.PART_PRODUCT_FAMILY LIKE NVL('&[-C--L]PART_PRODUCT_FAMILY', '%') or
|
|
IP.PART_PRODUCT_FAMILY in
|
|
(SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_
|
|
FROM (SELECT '&[-C--L]PART_PRODUCT_FAMILY' AS str FROM DUAL) d__
|
|
CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1))
|
|
|
|
and (PP.CF$_PURCHASE_PART_CAT_1 LIKE NVL('&[-C--L]PURCHASE_CAT1', '%') or
|
|
PP.CF$_PURCHASE_PART_CAT_1 in
|
|
(SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_
|
|
FROM (SELECT '&[-C--L]PURCHASE_CAT1' AS str FROM DUAL) d__
|
|
CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1) or
|
|
decode(PP.CF$_PURCHASE_PART_CAT_1, '', 'TRUE') = 'TRUE')
|
|
|
|
and (PP.CF$_PURCHASE_PART_CAT_2 LIKE NVL('&[-C--L]PURCHASE_CAT2', '%') or
|
|
PP.CF$_PURCHASE_PART_CAT_2 in
|
|
(SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_
|
|
FROM (SELECT '&[-C--L]PURCHASE_CAT2' AS str FROM DUAL) d__
|
|
CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1) or
|
|
decode(PP.CF$_PURCHASE_PART_CAT_2, '', 'TRUE') = 'TRUE')
|
|
|
|
Order By H.Part_No,
|
|
H.Stat_Year_NO || '-' || TO_CHAR(H.Stat_Period_No, '00')
|