MERGE INTO XXTWC_FMS_PICK_PLAN_LINE plan_line
USING (SELECT PICK_PLAN_IFACE_ID,
(SELECT ORGANIZATION_CODE
FROM XXTWC_LOV_ORGANIZATION_V
WHERE ORGANIZATION_CODE =
PACKING_HOUSE
AND ROWNUM = 1)
PACKING_HOUSE,
(SELECT LOOKUP_CODE
FROM XXTWC_LOV_VARIETY_V
WHERE lookup_code = VARIETY
AND LOOKUP_TYPE_ENABLED_FLAG = 'Y'
AND ROWNUM = 1)
VARIETY,
(SELECT LOOKUP_CODE
FROM XXTWC_LOV_DESIGNATION_V
WHERE MEANING = DESIGNATION
AND LOOKUP_TYPE_ENABLED_FLAG = 'Y'
AND ROWNUM = 1)
DESIGNATION,
(SELECT LOOKUP_CODE
FROM XXTWC_LOV_COUNTRY_OF_ORIGIN_V
WHERE MEANING = COUNTRY_OF_ORIGIN
AND LOOKUP_TYPE_ENABLED_FLAG = 'Y'
AND ROWNUM = 1)
COUNTRY_OF_ORIGIN,
GROWING_REGION,
PICK_PLAN_DATE,
PICK_UOM,
PICK_QUANTITY,
MIN_SIZE_OF_FRUIT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
FROM XXTWC_FMS_PICK_PLAN_IFACE
WHERE PICK_PLAN_IFACE_ID =
pick_lines_cur.PICK_PLAN_IFACE_ID) line_iface
ON ( plan_line.PACKING_HOUSE =
line_iface.PACKING_HOUSE
AND plan_line.VARIETY = line_iface.VARIETY
AND plan_line.DESIGNATION = line_iface.DESIGNATION
AND plan_line.COUNTRY_OF_ORIGIN =
line_iface.COUNTRY_OF_ORIGIN
AND plan_line.GROWING_REGION =
line_iface.GROWING_REGION
AND TRUNC (plan_line.PICK_PLAN_DATE) =
TRUNC (
TO_DATE (line_iface.PICK_PLAN_DATE,
'MM/DD/YYYY')))
WHEN MATCHED
THEN
UPDATE SET
plan_line.PICK_UOM = line_iface.PICK_UOM,
plan_line.PICK_QUANTITY = line_iface.PICK_QUANTITY,
plan_line.MIN_SIZE_OF_FRUIT =
line_iface.MIN_SIZE_OF_FRUIT,
plan_line.PICK_PLAN_IFACE_ID =
line_iface.PICK_PLAN_IFACE_ID,
plan_line.PICK_PLAN_IFACE_SOURCE =
lv_pick_plan_iface_source,
plan_line.LAST_UPDATE_DATE = line_iface.CREATION_DATE,
plan_line.LAST_UPDATED_BY = line_iface.LAST_UPDATED_BY
WHEN NOT MATCHED
THEN
INSERT (plan_line.PICK_PLAN_LINE_ID,
plan_line.PACKING_HOUSE,
plan_line.PACKING_HOUSE_ID,
plan_line.VARIETY,
plan_line.DESIGNATION,
plan_line.COUNTRY_OF_ORIGIN,
plan_line.GROWING_REGION,
plan_line.ITEM_NUMBER,
plan_line.PICK_PLAN_DATE,
plan_line.PICK_UOM,
plan_line.PICK_QUANTITY,
plan_line.MIN_SIZE_OF_FRUIT,
plan_line.PICK_PLAN_IFACE_ID,
plan_line.PICK_PLAN_IFACE_SOURCE,
plan_line.ATTRIBUTE1,
plan_line.ATTRIBUTE2,
plan_line.ATTRIBUTE3,
plan_line.ATTRIBUTE4,
plan_line.ATTRIBUTE5,
plan_line.ATTRIBUTE6,
plan_line.ATTRIBUTE7,
plan_line.ATTRIBUTE8,
plan_line.ATTRIBUTE9,
plan_line.ATTRIBUTE10,
plan_line.CREATION_DATE,
plan_line.CREATED_BY,
plan_line.LAST_UPDATE_DATE,
plan_line.LAST_UPDATED_BY)
VALUES (
XXTWC_FMS_PICK_PLAN_LINE_S.NEXTVAL,
line_iface.PACKING_HOUSE,
lv_packing_house_id, --line_iface.PACKING_HOUSE_ID ,
line_iface.VARIETY,
line_iface.DESIGNATION,
line_iface.COUNTRY_OF_ORIGIN,
line_iface.GROWING_REGION,
lv_item_number,
TO_DATE (line_iface.PICK_PLAN_DATE,
'MM/DD/YYYY'),
line_iface.PICK_UOM,
line_iface.PICK_QUANTITY,
line_iface.MIN_SIZE_OF_FRUIT,
line_iface.PICK_PLAN_IFACE_ID,
lv_pick_plan_iface_source,
line_iface.ATTRIBUTE1,
line_iface.ATTRIBUTE2,
line_iface.ATTRIBUTE3,
line_iface.ATTRIBUTE4,
line_iface.ATTRIBUTE5,
line_iface.ATTRIBUTE6,
line_iface.ATTRIBUTE7,
line_iface.ATTRIBUTE8,
line_iface.ATTRIBUTE9,
line_iface.ATTRIBUTE10,
SYSDATE,
line_iface.CREATED_BY,
SYSDATE,
line_iface.LAST_UPDATED_BY);
