Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE PAYMENT_COMISION AS
- PARTY_ID NUMBER(38,0);
- GENDER PARTY.GENDER%TYPE;
- ORG NVARCHAR2(2 CHAR);
- FECHA_APROBACION DATE;
- SALE_ID NUMBER(38,0);
- LEGACY_NUMBER NUMBER(38,0);
- CURSOR APROBADOS IS SELECT * FROM CLIENTES_INACTIVOS_APROBADOS;
- BEGIN
- FOR CLIENTE IN APROBADOS
- LOOP
- BEGIN
- --TRADUCCION DE GENERO
- IF CLIENTE.SEXO = 'M' THEN
- GENDER := 'MALE';
- ELSE
- GENDER := 'FEMALE';
- END IF;
- --TRADUCCION DE ORGANIZACION
- IF CLIENTE.ORGANIZACION = '1-GARBARINO S.A.I.C e I' THEN
- ORG := 'G';
- ELSE
- ORG := 'C';
- END IF;
- --CAST FECHA DE APROBACION
- FECHA_APROBACION := TRUNC(TO_TIMESTAMP(CLIENTE.FECHA_APROBACION, 'MM-DD-YYYY'));
- --OBTENCION DEL PARTY A PARTIR DEL DOCUMENTO,GENERO Y ORGANIZACION
- SELECT P.ID INTO PARTY_ID
- FROM TEK_COMMON.PARTY P
- JOIN TEK_CUSTOMER.CUSTOMER C
- ON P.ID = C.PARTY_ID
- WHERE P.ID_VALUE = CLIENTE.DOCUMENTO AND P.GENDER = GENDER AND C.ORG = ORG;
- --OBTENCION DE LA PRIMERA NVI SI COINCIDE SU FECHA DE APROBACION
- SELECT S.ID, S.LEGACY_NUMBER INTO SALE_ID, LEGACY_NUMBER
- FROM TEK_POS.SALE S
- WHERE S.CUSTOMER_PARTY_ID = PARTY_ID AND FECHA_APROBACION = TRUNC(S.BILL_DATE)
- ORDER BY S.CREATION_TIME;
- IF SALE_ID IS NOT NULL AND LEGACY_NUMBER IS NOT NULL THEN
- UPDATE CLIENTES_INACTIVOS_APROBADOS CI SET CI.SALE_ID=SALE_ID, CI.LEGACY_NUMBER=LEGACY_NUMBER WHERE CLIENTE.DOCUMENTO = CI.DOCUMENTO;
- END IF;
- EXCEPTION WHEN NO_DATA_FOUND THEN
- UPDATE CLIENTES_INACTIVOS_APROBADOS CI SET CI.SALE_ID=-1, CI.LEGACY_NUMBER=-1 WHERE CLIENTE.DOCUMENTO = CI.DOCUMENTO;
- WHEN TOO_MANY_ROWS THEN
- UPDATE CLIENTES_INACTIVOS_APROBADOS CI SET CI.SALE_ID=-2, CI.LEGACY_NUMBER=-2 WHERE CLIENTE.DOCUMENTO = CI.DOCUMENTO;
- END;
- END LOOP;
- NULL;
- END PAYMENT_COMISION;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement