Please I need help adding 2 tables to already-built datasource in a PL?SQL procedure. This procedure output to a refcursor called from another procedure from another datalink schema.
objective.
The objective is the add 3 new columns from another table to already returned select columns on the refcursor. So these three coulmns will be added to the select statement. I thought the best way to do this is to UNION these 3 coulumn in another select statement as it will be seen in the code.
Issue
The issue is that after adding the new select statements, tables, where-clause following the existing logic, I get error message below that 2 tables does not exists (I think this error message is because of the new tables I have added. I think I have upset the "OPEN out_cursor FOR 'SELECT * FROM" at the end of the procedure. I might have put statement in the wrong place but I have been looking and couldn't work it out.
Apologies
I have to apologise upfront for the size of this package. My intention is that people like you that are more experience may be able to spot my mistake from the OPEN cursor for select statement.
Is there a way I can display the result of the "open cursor for select statement in this procedure?
I am grateful for your help.
In the code below, the bit I have added are
ssqlgf4
ssqlat4 VARCHAR2(400);
ssqlcolumns4 VARCHAR2(800);
ssqlwhere4 VARCHAR2(3000);
ssqlitemdescrgf4 VARCHAR2(100);
OPEN out_cursor FOR 'SELECT * FROM (' || ssqlcolumns1 || ssqlitemdescrgf1 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf1 || ssqlwhere1 || ' UNION ' || ssqlcolumns2 || ssqlitemdescrgf2 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf2 || ssqlwhere2 || ' UNION ' || ssqlcolumns3 || ssqlitemdescrgf3 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf3 || ssqlwhere3 || ' AND D.CODQOR = 0 ' || ' UNION ' || ssqlcolumns4 || ssqlitemdescrgf4 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf4 || ssqlwhere4 || ' UNION ' || ssqlcolumns1 || ssqlitemdescrat1 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat1 || ssqlwhere1 || ssqlsuperceeded || ' AND A.COALOR = D.CODLOR ' || ' union ' || ssqlcolumns2 || ssqlitemdescrat2 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat2 || ssqlwhere2 || ssqlsuperceeded || ' UNION ' || ssqlcolumns3 || ssqlitemdescrat3 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat3 || ssqlwhere3 || ' AND (D.CODQOR = 0 OR D.CODOBS = 1) ' || ' union ' || ssqlcolumns4 || ssqlitemdescrat4 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat4 || ssqlwhere4 || ssqlsuperceeded || ') SR, tab3316 t3316 WHERE t3316.t3316soc(+) = SR.soc AND t3316.t3316tcd(+) = sr.tcd AND (t3316.t3316ema < 2 OR sr.tcd IS NULL)';
If I comment out my "OPEN out_cursor FOR 'SELECT * FROM" and Run the original one currently commented out, it works.
I normally run this in a pl/sql by calling another procedure that calls this procedure. When it works, I get this output.
767804*28-JAN-21*69*83*20210127_17:48_CARRASCO REPARA*SR_2755177*J013586*28-JAN-21**5*43022536*12.7*1*213905*28-JAN-21*104767*29 / 01 / 2021*Control panel with Sensor*****
767804*28-JAN-21*69*83*20210127_17:48_CARRASCO REPARA*SR_2755177*J013586*28-JAN-21**5*43022536*12.7*1*213905*28-JAN-21*104767*29 / 01 / 2021*Control panel with Sensor*****
This is the procedure
PROCEDURE get_order_allocation2(in_cas_id IN VARCHAR2,
in_date_s IN DATE,
in_date_to IN DATE,
in_cus_ref_s IN VARCHAR2,
in_cas_n_s IN NUMBER,
in_company IN NUMBER,
in_status IN NUMBER,
in_article IN NUMBER,
in_languagecode IN NUMBER,
in_user_calling IN VARCHAR2,
out_cursor IN OUT gtech_order_management.genericcursor) IS
v_cus_ref VARCHAR2(64) := NULL;
v_date_from DATE := to_date('01/01/1900', 'DD/MM/YYYY');
v_date_to DATE := SYSDATE + 1;
v_language_touse_ord NUMBER;
ssqlgf1 VARCHAR2(400);
ssqlat1 VARCHAR2(400);
ssqlgf2 VARCHAR2(400);
ssqlat2 VARCHAR2(400);
ssqlgf3 VARCHAR2(400);
ssqlgf4 VARCHAR2(400);
ssqlat3 VARCHAR2(400);
ssqlat4 VARCHAR2(400);
ssqlcolumns1 VARCHAR2(800);
ssqlcolumns2 VARCHAR2(800);
ssqlcolumns3 VARCHAR2(800);
ssqlcolumns4 VARCHAR2(800);
ssqlwhere1 VARCHAR2(3000);
ssqlwhere2 VARCHAR2(3000);
ssqlwhere3 VARCHAR2(3000);
ssqlwhere4 VARCHAR2(3000);
ssqlitemdescrgf1 VARCHAR2(100);
ssqlitemdescrat1 VARCHAR2(100);
ssqlitemdescrgf2 VARCHAR2(100);
ssqlitemdescrat2 VARCHAR2(100);
ssqlitemdescrgf3 VARCHAR2(100);
ssqlitemdescrgf4 VARCHAR2(100);
ssqlitemdescrat3 VARCHAR2(100);
ssqlitemdescrat4 VARCHAR2(100);
ssqlsuperceeded VARCHAR2(3000);
v_gttype NUMBER;
BEGIN
IF (in_date_s IS NOT NULL) THEN
v_date_from := in_date_s;
END IF;
IF (in_date_to IS NOT NULL) THEN
v_date_to := in_date_to + 1;
END IF;
SELECT u.giastech_type INTO v_gttype FROM user_web u WHERE u.cas_id = in_user_calling;
BEGIN
SELECT t.t0307_1lin INTO v_language_touse_ord FROM tab0307_1 t WHERE t.t0307_1lin = in_languagecode;
EXCEPTION
WHEN OTHERS THEN
v_language_touse_ord := 99;
END;
v_cus_ref := '%' || TRIM(upper(in_cus_ref_s)) || '%';
ssqlcolumns1 := 'SELECT DISTINCT H.COHCDE "CAS_ORDER_NO",
H.COHSOC "SOC",
H.COHTCD "TCD",
H.COHDCD "ORDER_DATE",
H.COHCMQ "CUS_REF",
H.COHREF "CUS_REF2",
UPT.CAS_ID "CREATE_USER",
H.cohdcd "STATUS_DATE",
T.T0307_1DES "STATUS_DESCR",
NVL(T.T0307_1COD, 5) "STATUS",
D.CODITM "ITEM",
D.CODCNE "NSV",
NVL(A.COAAQT, 0) "QTY",
NULL "DEV",
NULL "DEVDATE",
NULL "STANLB",
NULL "STADBL",
UPT.COMPANY "COMPANY",
D.CODCMQ "LINEREF",
D.CODREF "LINEREF2",
NULL "TRACK_NO",
NULL "TRACK_ETA" ,
NULL "TRACK_URL",';
ssqlitemdescrgf1 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM, D.CODSOC, D.CODCOD, ''GF'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlitemdescrat1 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM, D.CODSOC, D.CODCOD, ''AT'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlcolumns2 := 'SELECT DISTINCT H.COHCDE "CAS_ORDER_NO",
H.COHSOC "SOC",
H.COHTCD "TCD",
H.COHDCD "ORDER_DATE",
H.COHCMQ "CUS_REF",
H.COHREF "CUS_REF2",
UPT.CAS_ID "CREATE_USER",
H.cohdcd "STATUS_DATE",
NULL "STATUS_DESCR",
0 "STATUS",
D.CODITM "ITEM",
D.CODCNE "NSV",
D.CODQOR "QTY",
DEV.DELNUM "DEV",
DEV.DELDDT "DEVDATE",
STAT.STANFA "STANLB",
TO_CHAR(STAT.STADFA, ''dd / mm / yyyy'') "STADBL",
UPT.COMPANY "COMPANY",
D.CODCMQ "LINEREF",
D.CODREF "LINEREF2",
NULL "TRACK_NO",
NULL "TRACK_ETA" ,
NULL "TRACK_URL",';
ssqlitemdescrgf2 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM,D.CODSOC,D.CODCOD, ''GF'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlitemdescrat2 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM,D.CODSOC,D.CODCOD, ''AT'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlcolumns4 := 'SELECT DISTINCT H.COHCDE "CAS_ORDER_NO",
H.COHSOC "SOC",
H.COHTCD "TCD",
H.COHDCD "ORDER_DATE",
H.COHCMQ "CUS_REF",
H.COHREF "CUS_REF2",
UPT.CAS_ID "CREATE_USER",
H.cohdcd "STATUS_DATE",
NULL "STATUS_DESCR",
5 "STATUS",
D.CODITM "ITEM",
D.CODCNE "NSV",
0 "QTY",
NULL "DEV",
NULL "DEVDATE",
NULL "STANLB",
NULL "STADBL",
UPT.COMPANY "COMPANY",
D.CODCMQ "LINEREF",
D.CODREF "LINEREF2",
NULL "TRACK_NO",
NULL "TRACK_ETA" ,
NULL "TRACK_URL",';
ssqlitemdescrgf4 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM, D.CODSOC, D.CODCOD, ''GF'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlitemdescrat4 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM, D.CODSOC, D.CODCOD, ''AT'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlcolumns3 := 'SELECT DISTINCT H.COHCDE "CAS_ORDER_NO",
H.COHSOC "SOC",
H.COHTCD "TCD",
H.COHDCD "ORDER_DATE",
H.COHCMQ "CUS_REF",
H.COHREF "CUS_REF2",
UPT.CAS_ID "CREATE_USER",
H.cohdcd "STATUS_DATE",
NULL "STATUS_DESCR",
5 "STATUS",
D.CODITM "ITEM",
D.CODCNE "NSV",
0 "QTY",
NULL "DEV",
NULL "DEVDATE",
NULL "STANLB",
NULL "STADBL",
UPT.COMPANY "COMPANY",
D.CODCMQ "LINEREF",
D.CODREF "LINEREF2",
NULL "TRACK_NO",
NULL "TRACK_ETA" ,
NULL "TRACK_URL",';
ssqlitemdescrgf3 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM, D.CODSOC, D.CODCOD, ''GF'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlitemdescrat3 := 'GTECH_ORDER_MANAGEMENT.getItemDescription(D.CODITM, D.CODSOC, D.CODCOD, ''AT'', ' || in_languagecode ||
') "ITEM_DESCR", ';
ssqlat1 := 'FROM
CUSTORDH_AT H,
CUSTORDA_AT A,
CUSTORDD_AT D,
TAB0307_1 T,
USER_PART_TYPE UPT';
ssqlat2 := 'FROM
CUSTORDH_AT H,
CUSTORDD_AT D,
DELIVERY_AT DEV,
STATIST_AT STAT,
USER_PART_TYPE UPT';
ssqlat4 := 'FROM
CUSTORDH H,
CUSTORDD D,
DELIVERY DEV,
STATIST STAT,
DELIVERY_AT_UDC UDC,
TMS_TRACKING TMS,
USER_PART_TYPE UPT';
ssqlat3 := 'FROM
CUSTORDH_AT H,
CUSTORDD_AT D,
USER_PART_TYPE UPT';
ssqlgf1 := ' FROM
CUSTORDH H,
CUSTORDA A,
CUSTORDD D,
TAB0307_1 T,
USER_PART_TYPE UPT';
ssqlgf2 := 'FROM
CUSTORDH H,
CUSTORDD D,
DELIVERY DEV,
STATIST STAT,
USER_PART_TYPE UPT';
ssqlgf4 := 'FROM
CUSTORDH H,
CUSTORDD D,
DELIVERY DEV,
STATIST STAT,
DELIVERY_AT_UDC UDC,
TMS_TRACKING TMS,
USER_PART_TYPE UPT';
ssqlgf3 := 'FROM
CUSTORDH H,
CUSTORDD D,
USER_PART_TYPE UPT';
ssqlwhere1 := ' WHERE H.COHSOC = D.CODSOC
AND H.COHRES = D.CODRES
AND H.COHCDE = D.CODCDE
AND A.COASTA = T.T0307_1COD(+)
AND A.COASOC = D.CODSOC
AND A.COARES = D.CODRES
AND A.COACOD = D.CODCOD
AND A.COACDE = D.CODCDE
AND A.COAITM = D.CODITM
AND A.COALNU = D.CODLNU
AND UPT.COMPANY = D.CODSOC
AND UPT.NETWORK = D.CODRES
AND UPT.CUSTOMER= D.CODCOD
AND T.T0307_1LIN(+) = ' || v_language_touse_ord;
ssqlwhere3 := ' WHERE H.COHSOC = D.CODSOC
AND H.COHRES = D.CODRES
AND H.COHCDE = D.CODCDE
AND UPT.COMPANY = D.CODSOC
AND UPT.NETWORK = D.CODRES
AND UPT.CUSTOMER= D.CODCOD';
ssqlsuperceeded := ' AND ((D.CODQOR = 0 AND (D.CODSOC, D.CODCDE, D.CODLOR, 1)
IN (SELECT D1.CODSOC, D1.CODCDE, D1.CODLOR, COUNT(*)
FROM CUSTORDD_AT D1
WHERE D1.CODSOC = D.CODSOC AND D1.CODCDE = D.CODCDE
GROUP BY D1.CODSOC, D1.CODCDE, D1.CODLOR)) OR
(D.CODQOR <> 0)) ';
IF (TRIM(in_cas_n_s) IS NOT NULL) THEN
ssqlwhere1 := ssqlwhere1 || ' AND H.COHCDE = ' || in_cas_n_s;
ssqlwhere3 := ssqlwhere3 || ' AND H.COHCDE = ' || in_cas_n_s;
END IF;
IF (TRIM(in_cus_ref_s) IS NOT NULL) THEN
ssqlwhere1 := ssqlwhere1 || ' AND (UPPER(H.cohcmq) like ''' || v_cus_ref || ''' or UPPER(H.cohref) like ''' || v_cus_ref ||
''' or UPPER(D.CODCMQ) like ''' || v_cus_ref || ''')';
ssqlwhere3 := ssqlwhere3 || ' AND (UPPER(H.cohcmq) like ''' || v_cus_ref || ''' or UPPER(H.cohref) like ''' || v_cus_ref ||
''' or UPPER(D.CODCMQ) like ''' || v_cus_ref || ''')';
END IF;
-- company code
IF (TRIM(in_company) IS NOT NULL) THEN
ssqlwhere1 := ssqlwhere1 || ' AND H.COHSOC = ' || in_company;
ssqlwhere3 := ssqlwhere3 || ' AND H.COHSOC = ' || in_company;
END IF;
ssqlwhere1 := ssqlwhere1 || ' AND H.cohdcd >= ''' || v_date_from || '''';
ssqlwhere1 := ssqlwhere1 || ' AND H.cohdcd <= ''' || v_date_to || '''';
ssqlwhere3 := ssqlwhere3 || ' AND H.cohdcd >= ''' || v_date_from || '''';
ssqlwhere3 := ssqlwhere3 || ' AND H.cohdcd <= ''' || v_date_to || '''';
-- stato allocazione
IF (in_status IS NOT NULL) THEN
IF (in_status = status_alloc_despatched) THEN
ssqlwhere1 := ssqlwhere1 || ' AND 1=2 ';
ELSIF (in_status = status_alloc_stock) THEN
ssqlwhere1 := ssqlwhere1 || ' AND A.COASTA IN(1,6) ';
ELSE
ssqlwhere1 := ssqlwhere1 || ' AND A.COASTA IN(5) ';
END IF;
END IF;
IF (in_article IS NOT NULL) THEN
ssqlwhere1 := ssqlwhere1 || ' AND D.CODITM = ' || in_article;
ssqlwhere3 := ssqlwhere3 || ' AND D.CODITM = ' || in_article;
END IF;
IF (TRIM(in_cas_id) IS NOT NULL) THEN
ssqlwhere1 := ssqlwhere1 || ' AND UPT.CAS_ID=''' || in_cas_id || '''';
ssqlwhere3 := ssqlwhere3 || ' AND UPT.CAS_ID=''' || in_cas_id || '''';
IF (v_gttype = user_type_agent) THEN
ssqlwhere1 := ssqlwhere1 || ' AND UPT.agent_id=''' || in_user_calling || '''';
ssqlwhere3 := ssqlwhere3 || ' AND UPT.agent_id=''' || in_user_calling || '''';
ELSIF (v_gttype = user_type_subsidiary) THEN
ssqlwhere1 := ssqlwhere1 || ' AND UPT.subsidiary_id=''' || in_user_calling || '''';
ssqlwhere3 := ssqlwhere3 || ' AND UPT.subsidiary_id=''' || in_user_calling || '''';
END IF;
ELSE
IF (v_gttype = user_type_agent) THEN
ssqlwhere1 := ssqlwhere1 || ' AND UPT.agent_id=''' || in_user_calling || '''';
ssqlwhere3 := ssqlwhere3 || ' AND UPT.agent_id=''' || in_user_calling || '''';
ELSIF (v_gttype = user_type_subsidiary) THEN
ssqlwhere1 := ssqlwhere1 || ' AND (UPT.subsidiary_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''')';
ssqlwhere3 := ssqlwhere3 || ' AND (UPT.subsidiary_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''')';
ELSE
DECLARE
v_member VARCHAR2(100);
BEGIN
SELECT u.cas_id
INTO v_member
FROM user_web u
WHERE u.web_master_user = in_user_calling AND
rownum = 1;
ssqlwhere1 := ssqlwhere1 || ' AND UPT.CAS_id IN(''' || in_user_calling || ''', ''' || v_member || ''')';
ssqlwhere3 := ssqlwhere3 || ' AND UPT.CAS_id IN(''' || in_user_calling || ''', ''' || v_member || ''')';
END;
END IF;
END IF;
ssqlwhere2 := ' WHERE H.COHSOC = D.CODSOC
AND H.COHRES = D.CODRES
AND H.COHCDE = D.CODCDE
AND DEV.DELSOC = D.CODSOC
AND DEV.DELRES = D.CODRES
AND DEV.DELCUS = D.CODCOD
AND DEV.DELCDE = D.CODCDE
AND DEV.DELART = D.CODITM
AND DEV.DELSOC = STAT.STASOC(+)
AND DEV.DELRES = STAT.STARES (+)
AND DEV.DELCUS = STAT.STACLI (+)
AND DEV.DELNUM = STAT.STANLB(+)
AND TRUNC(DEV.DELDDT) = STAT.STADBL(+)
AND DEV.DELCDE = STAT.STANOR (+)
AND UPT.COMPANY = D.CODSOC
AND UPT.NETWORK = D.CODRES
AND UPT.CUSTOMER= D.CODCOD';
IF (TRIM(in_cas_n_s) IS NOT NULL) THEN
ssqlwhere2 := ssqlwhere2 || ' AND H.COHCDE = ' || in_cas_n_s;
END IF;
-- company code
IF (TRIM(in_company) IS NOT NULL) THEN
ssqlwhere2 := ssqlwhere2 || ' AND D.CODSOC = ' || in_company;
END IF;
-- ITEM CODE
IF (in_article IS NOT NULL) THEN
ssqlwhere2 := ssqlwhere2 || ' AND D.CODITM = ' || in_article;
END IF;
ssqlwhere2 := ssqlwhere2 || ' AND H.cohdcd >= ''' || v_date_from || '''';
ssqlwhere2 := ssqlwhere2 || ' AND H.cohdcd <= ''' || v_date_to || '''';
IF (TRIM(in_cus_ref_s) IS NOT NULL) THEN
ssqlwhere2 := ssqlwhere2 || ' AND (UPPER(H.cohcmq) like ''' || v_cus_ref || ''' or UPPER(H.cohref) like ''' || v_cus_ref ||
''' or UPPER(D.CODCMQ) like ''' || v_cus_ref || ''')';
END IF;
IF (in_status IS NOT NULL) THEN
IF (in_status <> status_alloc_despatched) THEN
ssqlwhere2 := ssqlwhere2 || ' AND 1=2 ';
END IF;
END IF;
IF (TRIM(in_cas_id) IS NOT NULL) THEN
ssqlwhere2 := ssqlwhere2 || ' AND UPT.CAS_ID=''' || in_cas_id || '''';
ssqlwhere2 := ssqlwhere2 || ' AND (H.COHUTI=''' || in_cas_id || ''' OR H.COHUTI NOT LIKE ''J%'')';
IF (v_gttype = user_type_agent) THEN
ssqlwhere2 := ssqlwhere2 || ' AND (UPT.agent_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''') ';
ELSIF (v_gttype = user_type_subsidiary) THEN
ssqlwhere2 := ssqlwhere2 || ' AND UPT.subsidiary_id=''' || in_user_calling || '''';
END IF;
ELSE
ssqlwhere2 := ssqlwhere2 || ' AND H.COHUTI=''' || in_cas_id || '''';
IF (v_gttype = user_type_agent) THEN
ssqlwhere2 := ssqlwhere2 || ' AND (UPT.agent_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''') ';
ELSIF (v_gttype = user_type_subsidiary) THEN
ssqlwhere2 := ssqlwhere2 || ' AND (UPT.subsidiary_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''')';
ELSE
DECLARE
v_member VARCHAR2(100);
BEGIN
SELECT u.cas_id
INTO v_member
FROM user_web u
WHERE u.web_master_user = in_user_calling AND
rownum = 1;
ssqlwhere2 := ssqlwhere2 || ' AND UPT.CAS_id IN(''' || in_user_calling || ''', ''' || v_member || ''')';
END;
END IF;
ssqlwhere2 := ssqlwhere2 || ' AND (H.COHUTI NOT LIKE ''J%'')';
END IF;
ssqlwhere4 := ' WHERE H.COHSOC = D.CODSOC
AND H.COHRES = D.CODRES
AND H.COHCDE = D.CODCDE
AND DEV.DELSOC = D.CODSOC
AND DEV.DELRES = D.CODRES
AND DEV.DELCUS = D.CODCOD
AND DEV.DELCDE = D.CODCDE
AND DEV.DELART = D.CODITM
AND DEV.DELSOC = STAT.STASOC(+)
AND DEV.DELRES = STAT.STARES (+)
AND DEV.DELCUS = STAT.STACLI (+)
AND DEV.DELNUM = STAT.STANLB(+)
AND TRUNC(DEV.DELDDT) = STAT.STADBL(+)
AND DEV.DELCDE = STAT.STANOR (+)
AND UPT.COMPANY = D.CODSOC
AND UPT.NETWORK = D.CODRES
AND UPT.CUSTOMER= D.CODCOD
AND DEV.DELSOC = UDC.DELSOC
AND DEV.DELRES = UDC.DELRES
AND DEV.DELCUS = UDC.DELCUS
AND DEV.DELCDE = UDC.DELCDE
AND DEV.DELART = UDC.DELART
AND UDC.DELUDC = TMS.TMS_DELUDC';
IF (TRIM(in_cas_n_s) IS NOT NULL) THEN
ssqlwhere4 := ssqlwhere4 || ' AND H.COHCDE = ' || in_cas_n_s;
END IF;
-- company code
IF (TRIM(in_company) IS NOT NULL) THEN
ssqlwhere4 := ssqlwhere4 || ' AND D.CODSOC = ' || in_company;
END IF;
-- ITEM CODE
IF (in_article IS NOT NULL) THEN
ssqlwhere4 := ssqlwhere4 || ' AND D.CODITM = ' || in_article;
END IF;
ssqlwhere4 := ssqlwhere4 || ' AND H.cohdcd >= ''' || v_date_from || '''';
ssqlwhere4 := ssqlwhere4 || ' AND H.cohdcd <= ''' || v_date_to || '''';
IF (TRIM(in_cus_ref_s) IS NOT NULL) THEN
ssqlwhere4 := ssqlwhere4 || ' AND (UPPER(H.cohcmq) like ''' || v_cus_ref || ''' or UPPER(H.cohref) like ''' || v_cus_ref ||
''' or UPPER(D.CODCMQ) like ''' || v_cus_ref || ''')';
END IF;
IF (in_status IS NOT NULL) THEN
IF (in_status <> status_alloc_despatched) THEN
ssqlwhere4 := ssqlwhere4 || ' AND 1=2 ';
END IF;
END IF;
IF (TRIM(in_cas_id) IS NOT NULL) THEN
ssqlwhere4 := ssqlwhere4 || ' AND UPT.CAS_ID=''' || in_cas_id || '''';
ssqlwhere4 := ssqlwhere4 || ' AND (H.COHUTI=''' || in_cas_id || ''' OR H.COHUTI NOT LIKE ''J%'')';
IF (v_gttype = user_type_agent) THEN
ssqlwhere4 := ssqlwhere4 || ' AND (UPT.agent_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''') ';
ELSIF (v_gttype = user_type_subsidiary) THEN
ssqlwhere4 := ssqlwhere4 || ' AND UPT.subsidiary_id=''' || in_user_calling || '''';
END IF;
ELSE
ssqlwhere4 := ssqlwhere4 || ' AND H.COHUTI=''' || in_cas_id || '''';
IF (v_gttype = user_type_agent) THEN
ssqlwhere4 := ssqlwhere4 || ' AND (UPT.agent_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''') ';
ELSIF (v_gttype = user_type_subsidiary) THEN
ssqlwhere4 := ssqlwhere4 || ' AND (UPT.subsidiary_id=''' || in_user_calling || ''' or UPT.CAS_ID=''' || in_user_calling ||
''')';
ELSE
DECLARE
v_member VARCHAR2(100);
BEGIN
SELECT u.cas_id
INTO v_member
FROM user_web u
WHERE u.web_master_user = in_user_calling AND
rownum = 1;
ssqlwhere4 := ssqlwhere4 || ' AND UPT.CAS_id IN(''' || in_user_calling || ''', ''' || v_member || ''')';
END;
END IF;
ssqlwhere4 := ssqlwhere4 || ' AND (H.COHUTI NOT LIKE ''J%'')';
END IF;
-- OPEN out_cursor FOR 'SELECT * FROM (' || ssqlcolumns1 || ssqlitemdescrgf1 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf1 || ssqlwhere1 || ' UNION ' || ssqlcolumns2 || ssqlitemdescrgf2 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf2 || ssqlwhere2 || ' UNION ' || ssqlcolumns3 || ssqlitemdescrgf3 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf3 || ssqlwhere3 || ' AND D.CODQOR = 0 ' || ' UNION ' || ssqlcolumns1 || ssqlitemdescrat1 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat1 || ssqlwhere1 || ssqlsuperceeded || ' AND A.COALOR = D.CODLOR ' || ' union ' || ssqlcolumns2 || ssqlitemdescrat2 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat2 || ssqlwhere2 || ssqlsuperceeded || ' UNION ' || ssqlcolumns3 || ssqlitemdescrat3 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat3 || ssqlwhere3 || ' AND (D.CODQOR = 0 OR D.CODOBS = 1) ' || ssqlsuperceeded || ') SR, tab3316 t3316 WHERE t3316.t3316soc(+) = SR.soc AND t3316.t3316tcd(+) = sr.tcd AND (t3316.t3316ema < 2 OR sr.tcd IS NULL)';
OPEN out_cursor FOR 'SELECT * FROM (' || ssqlcolumns1 || ssqlitemdescrgf1 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf1 || ssqlwhere1 || ' UNION ' || ssqlcolumns2 || ssqlitemdescrgf2 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf2 || ssqlwhere2 || ' UNION ' || ssqlcolumns3 || ssqlitemdescrgf3 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf3 || ssqlwhere3 || ' AND D.CODQOR = 0 ' || ' UNION ' || ssqlcolumns4 || ssqlitemdescrgf4 || ' D.CODLNU "LNU", 0 "LOR", ''GF'' AS DATASOURCE ' || ssqlgf4 || ssqlwhere4 || ' UNION ' || ssqlcolumns1 || ssqlitemdescrat1 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat1 || ssqlwhere1 || ssqlsuperceeded || ' AND A.COALOR = D.CODLOR ' || ' union ' || ssqlcolumns2 || ssqlitemdescrat2 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat2 || ssqlwhere2 || ssqlsuperceeded || ' UNION ' || ssqlcolumns3 || ssqlitemdescrat3 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat3 || ssqlwhere3 || ' AND (D.CODQOR = 0 OR D.CODOBS = 1) ' || ' union ' || ssqlcolumns4 || ssqlitemdescrat4 || ' D.CODLNU "LNU", D.CODLOR "LOR", ''AT'' AS DATASOURCE ' || ssqlat4 || ssqlwhere4 || ssqlsuperceeded || ') SR, tab3316 t3316 WHERE t3316.t3316soc(+) = SR.soc AND t3316.t3316tcd(+) = sr.tcd AND (t3316.t3316ema < 2 OR sr.tcd IS NULL)';
END get_order_allocation2;