Hi all,
I have a table with name SUPPLIES_A1 with multiple columns such as
WITH SUPPLIES_A1(REFERENCE_QUARTER, T_DATE, EOF_CODE, HOSP_NAME, CMP_NAME, QUANTITY, AMOUNT_1, AMOUNT_2) AS (
SELECT '20191' , TO_DATE('03/02/2020','DD/MM/YYYY'), '2345675', 'HOSP NAME 1', 'CMP NAME 1', 50, 400.56, 230.33 FROM DUAL UNION ALL
SELECT '20192' , TO_DATE('03/04/2020','DD/MM/YYYY'), '2345675', 'HOSP NAME 1', 'CMP NAME 1', 25, 200.38, 115.33 FROM DUAL UNION ALL
SELECT '20192' , TO_DATE('02/06/2020','DD/MM/YYYY'), '2345675', 'HOSP NAME 1', 'CMP NAME 2', 40, 100.21, 100.11 FROM DUAL UNION ALL
SELECT '20192' , TO_DATE('04/06/2020','DD/MM/YYYY'), '2345675', 'HOSP NAME 1', 'CMP NAME 2', 40, 100.21, 100.11 FROM DUAL
-- ...............
)
SELECT *
FROM TABLE_A1;
and the returns of the companies
WITH RETURNS_A1(REFERENCE_QUARTER, T_DATE, EOF_CODE, HOSP_NAME, CMP_NAME, QUANTITY, REST_QUANTITY, AMOUNT_1, REST_AMOUNT_1, AMOUNT_2, REST_AMOUNT_2) AS (
SELECT '20191' , TO_DATE('04/02/2020','DD/MM/YYYY'), '2345675', 'HOSP NAME 1', 'CMP NAME 1', 60, 60, 200.56, 200.56, 210.33, 210.33 FROM DUAL UNION ALL
SELECT '20191' , TO_DATE('01/03/2020','DD/MM/YYYY'), '2345675', 'HOSP NAME 1', 'CMP NAME 2', 10, 10, 100.56, 200.56, 310.33, 210.33 FROM DUAL
-- ...............
)
SELECT *
FROM RETURNS_A1;
I want to merge returns in supplies (same eof_code, hosp_name, cmp_name) running a procedure
for a quarter.
To be more specific,
I want for every row in RETURNS_A1 to check if the quantity is smaller than the corresponding in SUPPLIES_A1 (same eof_code, hosp_name, cmp_name)
and if it is smaller to update
SUPPLIES_A1.QUANTITY = SUPPLIES_A1.QUANTITY - V_QUANTITY
SUPPLIES_A1.AMOUNT_1 = SUPPLIES_A1.AMOUNT_1 - V_AMOUNT_1
SUPPLIES_A1.AMOUNT_2 = SUPPLIES_A1.AMOUNT_2 - V_AMOUNT_2
Then I should update the rest fields of RETURNS_A1 as
RETURNS_A1.REST_QUANTITY = RETURNS_A1.REST_QUANTITY - V_QUANTITY
RETURNS_A1.REST_AMOUNT_1 = RETURNS_A1.REST_AMOUNT_1 - V_AMOUNT_1
RETURNS_A1.REST_AMOUNT_2 = RETURNS_A1.REST_AMOUNT_2 - V_AMOUNT_2
If the quantity of the RETURNS_A1 is largest than the corresponding in SUPPLIES_A1 I update the quantity that exists in Supplies and the rest will remain in REST_QUANTITY for the next quarter.
If there are returns from previous quarter , I am trying to match them in new quarter of supplies.
Moreover, my supplies and returns do not have the same t_dates for matching so I get the oldest and continue with the newer ..
My trial is :
DECLARE
p_quarter HOSP_INVOICES.REFERENCE_QUARTER%TYPE := '20191';
v_index VARCHAR2(1000);
v_QuarterFirstDate DATE;
v_QuarterLastDate DATE;
v_year VARCHAR2(4);
i_Quarter NUMBER;
CURSOR CUR_RETURNS IS
SELECT *
FROM RETURNS_A1 ret
WHERE exists(SELECT null
FROM SUPPLIES_A1 data
WHERE data.reference_quarter = p_quarter
AND ret.eof_code = data.eof_code
AND ret.reference_quarter = data.reference_quarter
AND ret.hosp_name = data.hosp_name
AND ret.cmp_name = data.cmp_name )
AND ret.REST_QUANTITY > 0
AND ret.REST_AMOUNT_1 > 0
AND ret.tim_date <= v_QuarterLastDate
ORDER BY ret.tim_date;
CURSOR CUR_2 IS
SELECT data.hosp_name
, data.cmp_name
, data.EOF_CODE
, SUM(data.QUANTITY) AS SUM_TMP_QNT
, SUM(data.AMOUNT_1) AS SUM_TMP_AMOUNT_1
, SUM(data.AMOUNT_2) AS SUM_TMP_AMOUNT_2
FROM SUPPLIES_A1 data
WHERE EXISTS ( SELECT null
FROM RETURNS_A1 ret
WHERE ret.rest_quantity > 0
AND ret.tim_date <= v_QuarterLastDate
AND ret.eof_code = data.eof_code
AND ret.hosp_name = data.hosp_name
AND ret.cmp_name = data.cmp_name)
AND data.reference_quarter = p_quarter
GROUP BY data.hosp_name
, data.cmp_name
, data.eof_code;
TYPE REB_TP IS RECORD ( REST_QUANTITY SUPPLIES_A1.QUANTITY%TYPE
, REST_AMOUNT_1 SUPPLIES_A1.AMOUNT_1%TYPE
, REST_AMOUNT_2 SUPPLIES_A1.AMOUNT_2%TYPE );
TYPE REB_TABLE IS TABLE OF REB_TP INDEX BY VARCHAR2(1000);
REB_TAB REB_TABLE;
V_RETURN_QUANTITY NUMBER;
V_RETURN_AMOUNT_1 NUMBER;
V_RETURN_AMOUNT_2 NUMBER;
BEGIN
v_year := SUBSTR(p_Quarter, 1, 4);
i_Quarter := TO_NUMBER(SUBSTR(p_Quarter, 5));
v_QuarterFirstDate := ADD_MONTHS(TO_DATE(v_year || '01', 'yyyymm'), 3 * (i_Quarter - 1));
v_QuarterLastDate := ADD_MONTHS(v_QuarterFirstDate, 3) - 1;
--initialize TAB
FOR REC IN CUR_2 LOOP
v_index := REC.HOSP_NAME || '|' ||
REC.CMP_NAME || '|' ||
REC.EOF_CODE;
REB_TAB(v_index).REST_QUANTITY := REC.SUM_TMP_QNT;
REB_TAB(v_index).REST_AMOUNT_1 := REC.SUM_TMP_AMOUNT_1;
REB_TAB(v_index).REST_AMOUNT_2 := REC.SUM_TMP_AMOUNT_2;
END LOOP;
<<loop_returns>>
FOR rec_ret IN CUR_RETURNS LOOP
v_index := rec_ret.HOSP_NAME || '|' ||
rec_ret.CMP_NAME || '|' ||
rec_ret.EOF_CODE;
IF ( rec_ret.REST_QUANTITY <= REB_TAB(v_index).REST_QUANTITY
AND rec_ret.REST_AMOUNT_1 <= REB_TAB(v_index).REST_AMOUNT_1
AND rec_ret.REST_AMOUNT_2 <= REB_TAB(v_index).REST_AMOUNT_2) THEN
V_RETURN_QUANTITY := rec_ret.REST_QUANTITY;
V_RETURN_AMOUNT_1 := rec_ret.REST_AMOUNT_1;
V_RETURN_AMOUNT_2 := rec_ret.REST_AMOUNT_2;
UPDATE RETURNS_A1
SET REST_QUANTITY = 0,
REST_AMOUNT_1 = 0,
REST_AMOUNT_2 = 0
WHERE EOF_CODE = EOPYY_RETURNS_REC.EOF_CODE
AND t_date = EOPYY_RETURNS_REC.t_date
AND HOSP_NAME = EOPYY_RETURNS_REC.HOSP_NAME
AND cmp_name = EOPYY_RETURNS_REC.cmp_name;
commit;
END IF;
END loop_returns;
END;
/
i am so confused to implement this..
Any help , I appreciate it.
Thanks in advance