/* Formatted on 5/20/2024 9:32:31 AM (QP5 v5.
391) */
SELECT PARENT PARENT_JOB,
JOB_NAME,
DESCRIPTION DESCRIPTION1,
SEQ SEQ,
TYPE TYPE,
COMPONENT_ITEM,
PIECE_MARK PIECE_MARK,
PROFILE PROFILE,
GRADE GRADE,
LENGTH LENGTH,
BATCH_QTY BATCH_QTY,
NEW_BATCH_QTY PKG_QTY,
PIECE_GALV_UNIT_WEIGHT PIECE_GALV_UNIT_WEIGHT,
ROUND (NEW_TOTAL_GALV_WEIGHT, 2) TOTAL_UNIT_WIEGHT
FROM XX_BAB_DISPATCH_PACKING_TBL
WHERE PARENT = :P_PARENT_JOB
UNION ALL
SELECT PARENT PARENT_JOB,
JOB_NAME,
[Link] DESCRIPTION1,
[Link] SEQ,
TYPE TYPE,
MSIB2.SEGMENT1 COMPONENT_ITEM,
(SELECT DISTINCT MDEV.ELEMENT_VALUE
FROM MTL_DESCR_ELEMENT_VALUES_V MDEV
WHERE BIC.COMPONENT_ITEM_ID =
MDEV.INVENTORY_ITEM_ID
AND ELEMENT_NAME = 'PERFIX')
|| ' '
|| (SELECT DISTINCT MDEV.ELEMENT_VALUE
FROM MTL_DESCR_ELEMENT_VALUES_V MDEV
WHERE BIC.COMPONENT_ITEM_ID =
MDEV.INVENTORY_ITEM_ID
AND ELEMENT_NAME = 'PIECE MARK')
PIECE_MARK,
(SELECT DISTINCT MDEV.ELEMENT_VALUE
FROM MTL_DESCR_ELEMENT_VALUES_V MDEV
WHERE BIC.COMPONENT_ITEM_ID =
MDEV.INVENTORY_ITEM_ID
AND ELEMENT_NAME = 'PROFILE')
PROFILE,
(SELECT DISTINCT MDEV.ELEMENT_VALUE
FROM MTL_DESCR_ELEMENT_VALUES_V MDEV
WHERE BIC.COMPONENT_ITEM_ID =
MDEV.INVENTORY_ITEM_ID
AND ELEMENT_NAME = 'GRADE')
GRADE,
(SELECT DISTINCT MDEV.ELEMENT_VALUE
FROM MTL_DESCR_ELEMENT_VALUES_V MDEV
WHERE BIC.COMPONENT_ITEM_ID =
MDEV.INVENTORY_ITEM_ID
AND ELEMENT_NAME = 'LENGTH')
LENGTH,
BIC.COMPONENT_QUANTITY BATCH_QTY,
NEW_BATCH_QTY PKG_QTY,
(SELECT TO_NUMBER (MEANING)
FROM fnd_lookup_values FLV
WHERE 1 = 1
AND FLV.lookup_type =
'KSA_ZINC_COATING'
AND [Link] = 'US'
AND FLV.LOOKUP_CODE = X.MIN_COAT
AND NVL (FLV.ENABLED_FLAG, 'N') = 'Y')
PIECE_GALV_UNIT_WEIGHT,
CASE
WHEN NVL (BATCH_QTY, 0) < 0
THEN
NVL (BATCH_QTY, 0) * -1
ELSE
NVL (BATCH_QTY, 0)
END
* ROUND (
NVL ((LENGTH * (SELECT DISTINCT MDEV.ELEMENT_VALUE
FROM MTL_DESCR_ELEMENT_VALUES_V MDEV
WHERE BIC.COMPONENT_ITEM_ID =
MDEV.INVENTORY_ITEM_ID
AND ELEMENT_NAME = 'UNIT WEIGHT')) / 1000,
0)
* (SELECT TO_NUMBER (MEANING)
FROM fnd_lookup_values FLV
WHERE 1 = 1
AND FLV.lookup_type =
'KSA_ZINC_COATING'
AND [Link] = 'US'
AND FLV.LOOKUP_CODE = X.MIN_COAT
AND NVL (FLV.ENABLED_FLAG, 'N') = 'Y'),
4) TOTAL_UNIT_WIEGHT
FROM BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BOM,
INV.MTL_SYSTEM_ITEMS_B MSIB,
INV.MTL_SYSTEM_ITEMS_B MSIB2,
XX_BAB_DISPATCH_PACKING_TBL X
WHERE X.COMPONENT_ITEM = MSIB.SEGMENT1
AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND (BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE >= SYSDATE - 1)
AND BOM.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND BIC.COMPONENT_ITEM_ID = MSIB2.INVENTORY_ITEM_ID(+)
AND BOM.ORGANIZATION_ID = MSIB2.ORGANIZATION_ID
AND BOM.ORGANIZATION_ID = 310
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
START WITH MSIB.SEGMENT1 = X.COMPONENT_ITEM --'AR3-109-WELDED ASSEMBLY'
CONNECT BY PRIOR BIC.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
ORDER BY 4