FUNCTION createRotationStatement(p_project IN varchar2 DEFAULT NULL,
p_whse IN varchar2 DEFAULT NULL,
p_ower IN varchar2 DEFAULT NULL,
p_outbkey IN varchar2 DEFAULT NULL,
p_rowno IN varchar2 DEFAULT NULL
)
RETURN varchar2 IS
v_orderClause varchar2(32767) := NULL;
BEGIN
r_orderRoute := getRoute(p_project, p_whse, p_ower, p_outbkey, p_rowno);
IF (r_orderRoute.e_skurotation1 IS NOT NULL) AND
(r_orderRoute.e_rotation1 IS NOT NULL) THEN
IF r_orderRoute.e_rotation1 = 'LIFO' THEN
r_orderRoute.e_rotation1 := 'DESC';
ELSE
r_orderRoute.e_rotation1 := 'ASC';
END IF;
-- v_orderClause := v_orderClause||' DECODE(UPPER('''||r_orderRoute.e_skurotation1||'''),''LOT'',le.lot,le.'||r_orderRoute.e_skurotation1||') '||r_orderRoute.e_rotation1||', ';
v_orderClause := v_orderClause || ' DECODE(UPPER(''' ||
r_orderRoute.e_skurotation1 || '''),''LOT'',le.lot,
''LOT08'',TO_CHAR(le.LOT08,''yyyy-mm-dd''),
''LOT09'',TO_CHAR(le.LOT09,''yyyy-mm-dd''),
''LOT10'',TO_CHAR(le.LOT10,''yyyy-mm-dd''),
''LOT16'',TO_CHAR(le.LOT16,''yyyy-mm-dd''),
''REC_DATE'',TO_CHAR(inv.REC_DATE,''yyyy-mm-dd''),
le.LOT) ' || r_orderRoute.e_rotation1 || ', ';
END IF;
IF (r_orderRoute.e_skurotation2 IS NOT NULL) AND
(r_orderRoute.e_rotation2 IS NOT NULL) THEN
IF r_orderRoute.e_rotation2 = 'LIFO' THEN
r_orderRoute.e_rotation2 := 'DESC';
ELSE
r_orderRoute.e_rotation2 := 'ASC';
END IF;
-- v_orderClause := v_orderClause||' DECODE(UPPER('''||r_orderRoute.e_skurotation2||'''),''LOT'',le.lot,le.'||r_orderRoute.e_skurotation2||') '||r_orderRoute.e_rotation2||', ';
v_orderClause := v_orderClause || ' DECODE(UPPER(''' ||
r_orderRoute.e_skurotation2 || '''),''LOT'',le.lot,
''LOT08'',TO_CHAR(le.LOT08,''yyyy-mm-dd''),
''LOT09'',TO_CHAR(le.LOT09,''yyyy-mm-dd''),
''LOT10'',TO_CHAR(le.LOT10,''yyyy-mm-dd''),
''LOT16'',TO_CHAR(le.LOT16,''yyyy-mm-dd''),
''REC_DATE'',TO_CHAR(inv.REC_DATE,''yyyy-mm-dd''),
le.LOT) ' || r_orderRoute.e_rotation2 || ', ';
END IF;
IF (r_orderRoute.e_skurotation3 IS NOT NULL) AND
(r_orderRoute.e_rotation3 IS NOT NULL) THEN
IF r_orderRoute.e_rotation3 = 'LIFO' THEN
r_orderRoute.e_rotation3 := 'DESC';
ELSE
r_orderRoute.e_rotation3 := 'ASC';
END IF;
-- v_orderClause := v_orderClause||' DECODE(UPPER('''||r_orderRoute.e_skurotation3||'''),''LOT'',le.lot,le.'||r_orderRoute.e_skurotation3||') '||r_orderRoute.e_rotation3||', ';
v_orderClause := v_orderClause || ' DECODE(UPPER(''' ||
r_orderRoute.e_skurotation3 || '''),''LOT'',le.lot,
''LOT08'',TO_CHAR(le.LOT08,''yyyy-mm-dd''),
''LOT09'',TO_CHAR(le.LOT09,''yyyy-mm-dd''),
''LOT10'',TO_CHAR(le.LOT10,''yyyy-mm-dd''),
''LOT16'',TO_CHAR(le.LOT16,''yyyy-mm-dd''),
''REC_DATE'',TO_CHAR(inv.REC_DATE,''yyyy-mm-dd''),
le.LOT) ' || r_orderRoute.e_rotation3 || ', ';
END IF;
DBMS_OUTPUT.PUT_LINE('YYYYY:' || v_orderClause);
RETURN SUBSTR(TRIM(v_orderClause), 1, LENGTH(TRIM(v_orderClause)) - 1);
END createRotationStatement;