XXGF_PO_AGENTS_LDR.ctl file code
OPTIONS (SKIP = 1)
LOAD DATA
INFILE *
APPEND
INTO TABLE xxabc_po_agents_stg
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
AGENT_NUMBER CHAR "LTRIM(RTRIM(:AGENT_NUMBER))",
FIRST_NAME CHAR "LTRIM(RTRIM(:FIRST_NAME))",
MIDDLE_NAMES CHAR "LTRIM(RTRIM(:MIDDLE_NAMES))",
LAST_NAME CHAR "LTRIM(RTRIM(:LAST_NAME))",
SHIP_TO_LOCATION CHAR "LTRIM(RTRIM(:SHIP_TO_LOCATION))",
CATEGORY_CODE CHAR "LTRIM(RTRIM(:CATEGORY_CODE))",
SOURCE CONSTANT "O"
)
XXABC_PO_BUYERS_PKG Validation Package code
CREATE OR REPLACE
PACKAGE XXABC_PO_BUYERS_PKG
AS
PROCEDURE buyers_valid(
errbuf OUT VARCHAR2 ,
retcode OUT NUMBER ,
p_row_num IN NUMBER);
PROCEDURE create_buyers(
errbuf OUT VARCHAR2 ,
retcode OUT VARCHAR2 ,
P_ROW_NUM IN NUMBER);
END XXABC_PO_BUYERS_PKG;
/
CREATE OR REPLACE
PACKAGE BODY XXABC_PO_BUYERS_PKG
AS
PROCEDURE buyers_valid(
errbuf OUT VARCHAR2 ,
retcode OUT NUMBER ,
p_row_num IN NUMBER)
AS
CURSOR lcu_buyers_valid
IS
SELECT AGENT_NUMBER ,
SHIP_TO_LOCATION ,
CATEGORY_CODE
FROM XXABC_PO_AGENTS_STG
WHERE 1 =1
AND (STATUS = 'N'
OR STATUS IS NULL)
AND rownum <= p_row_num;
lc_validate NUMBER;
lc_error_msg VARCHAR2(4000);
lc_error VARCHAR2(2000);
LN_REQUEST_ID NUMBER :=FND_GLOBAL.CONC_REQUEST_ID;
L_ERROR_FLAG VARCHAR2(1):='N';
L_CONC_REQ_ID NUMBER;
L_PERS_ID NUMBER;
BEGIN
l_conc_req_id := fnd_profile.value('CONC_REQUEST_ID');
FOR r_buyers_validate IN lcu_buyers_valid
LOOP
l_error_flag:='N';
BEGIN
UPDATE XXABC_PO_AGENTS_STG
SET PROGRAM_ID=L_CONC_REQ_ID
WHERE (STATUS ='N'
OR status IS NULL)
AND ROWNUM <= P_ROW_NUM ;
COMMIT;
END;
BEGIN
IF r_buyers_validate.AGENT_NUMBER IS NOT NULL THEN
BEGIN
SELECT person_id
INTO l_pers_id
FROM PER_PEOPLE_F
WHERE EMPLOYEE_NUMBER=R_BUYERS_VALIDATE.AGENT_NUMBER
AND sysdate BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
IF L_PERS_ID IS NOT NULL THEN
--l_error_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Employee exist :'||R_BUYERS_VALIDATE.AGENT_NUMBER);
SELECT COUNT(*) INTO LC_VALIDATE FROM PO_AGENTS_V WHERE AGENT_ID= L_PERS_ID;
IF LC_VALIDATE > 0 THEN
L_ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Buyer '||R_BUYERS_VALIDATE.AGENT_NUMBER||' already exists..');
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='E' ,
ERROR_MSG=ERROR_MSG
|| 'AGENT_ALREADY_EXIST;'
WHERE 1 =1
AND agent_number=r_buyers_validate.agent_number;
XXABC_CONV_ERROR_INSERT_PROC (P_CONVERSION_OBJECT =>'BUYERS', P_CONVERSION_PHASE =>'VALIDATION', P_ERROR_FIELD =>'AGENT_NUMBER', P_ERROR_FIELD_VALUE =>R_BUYERS_VALIDATE.AGENT_NUMBER, P_ERROR_MESSAGE =>' Buyer Already exists.', P_REFERENCE1 =>LN_REQUEST_ID, X_ERROR =>LC_ERROR);
ELSE
-- L_ERROR_FLAG :='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Buyer' || R_BUYERS_VALIDATE.AGENT_NUMBER ||' is valid');
/*UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='V'
WHERE 1 =1
AND AGENT_NUMBER=R_BUYERS_VALIDATE.AGENT_NUMBER;*/
END IF;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,' Employee Does Not exist :'||R_BUYERS_VALIDATE.AGENT_NUMBER);
l_error_flag:='Y';
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='E' ,
ERROR_MSG= ERROR_msg
|| 'INVALID_EMPLOYEE;'
WHERE 1 =1
AND AGENT_NUMBER=R_BUYERS_VALIDATE.AGENT_NUMBER;
XXABC_CONV_ERROR_INSERT_PROC (P_CONVERSION_OBJECT =>'BUYERS', P_CONVERSION_PHASE =>'VALIDATION', P_ERROR_FIELD =>'AGENT_NUMBER', P_ERROR_FIELD_VALUE =>r_buyers_validate.agent_number, P_ERROR_MESSAGE =>lc_error_msg, P_REFERENCE1 =>LN_REQUEST_ID, X_ERROR =>lc_error);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_error_flag:='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in Agent Number Validation'||SQLERRM);
XXABC_CONV_ERROR_INSERT_PROC (P_CONVERSION_OBJECT =>'BUYERS', P_CONVERSION_PHASE =>'VALIDATION', P_ERROR_FIELD =>'AGENT_NUMBER', P_ERROR_FIELD_VALUE =>r_buyers_validate.agent_number, P_ERROR_MESSAGE =>lc_error_msg, P_REFERENCE1 =>LN_REQUEST_ID, X_ERROR =>lc_error);
END;
END IF;
END;
BEGIN
/* Location Code Validate*/
IF r_buyers_validate.SHIP_TO_LOCATION IS NOT NULL THEN
BEGIN
SELECT COUNT(*)
INTO lc_validate
FROM hr_locations
WHERE 1 =1
AND LOCATION_CODE=R_BUYERS_VALIDATE.SHIP_TO_LOCATION;
IF LC_VALIDATE = 0 THEN
l_error_flag :='Y';
LC_ERROR_MSG :=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,R_BUYERS_VALIDATE.AGENT_NUMBER||' loacation Does Not exist :'||R_BUYERS_VALIDATE.SHIP_TO_LOCATION);
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='E' ,
error_msg=error_msg
||'INVALID_LOCATION;'
WHERE 1 =1
AND SHIP_TO_LOCATION=R_BUYERS_VALIDATE.SHIP_TO_LOCATION
AND agent_number =R_BUYERS_VALIDATE.agent_number;
XXABC_CONV_ERROR_INSERT_PROC (P_CONVERSION_OBJECT =>'BUYERS', P_CONVERSION_PHASE =>'VALIDATION', P_ERROR_FIELD =>'SHIP_TO_LOCATION', P_ERROR_FIELD_VALUE =>R_BUYERS_VALIDATE.SHIP_TO_LOCATION, P_ERROR_MESSAGE =>LC_ERROR_MSG, P_REFERENCE1 =>LN_REQUEST_ID, X_ERROR =>LC_ERROR);
ELSE
-- L_ERROR_FLAG:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,R_BUYERS_VALIDATE.AGENT_NUMBER||' loacation exist :'||R_BUYERS_VALIDATE.SHIP_TO_LOCATION);
END IF;
COMMIT;
END;
END IF;
END;
BEGIN
/* Category Code Validate*/
IF r_buyers_validate.category_code IS NOT NULL THEN
BEGIN
SELECT COUNT(*)
INTO lc_validate
FROM mtl_categories
WHERE 1=1
AND SEGMENT1
||DECODE(segment2,NULL,NULL,'-')
|| segment2
||DECODE(segment3,NULL,NULL,'-')
|| SEGMENT3
||DECODE(SEGMENT4,NULL,NULL,'-')
|| segment4
||DECODE(segment5,NULL,NULL,'-')
|| segment5 =R_BUYERS_VALIDATE.CATEGORY_CODE;
IF LC_VALIDATE =0 THEN
l_error_flag:='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,R_BUYERS_VALIDATE.AGENT_NUMBER||' Category Does Not exist :'||R_BUYERS_VALIDATE.CATEGORY_CODE||'.Default.Default');
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='E',
error_msg=error_msg
||'INVALID_CATEGORY_CODE'
WHERE 1 =1
AND agent_number =r_buyers_validate.agent_number
AND category_code=r_buyers_validate.category_code;
COMMIT;
XXABC_CONV_ERROR_INSERT_PROC (P_CONVERSION_OBJECT =>'BUYERS', P_CONVERSION_PHASE =>'VALIDATION', P_ERROR_FIELD =>'CATEGORY_CODE', P_ERROR_FIELD_VALUE =>R_BUYERS_VALIDATE.CATEGORY_CODE||'.Default.Default', P_ERROR_MESSAGE =>LC_ERROR_MSG, P_REFERENCE1 =>LN_REQUEST_ID, X_ERROR =>LC_ERROR);
ELSE
--l_error_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,R_BUYERS_VALIDATE.AGENT_NUMBER||' Category Code exist :'||R_BUYERS_VALIDATE.CATEGORY_CODE||'.Default.Default');
END IF;
END;
END IF;
END;
BEGIN
IF (L_ERROR_FLAG ='Y') THEN
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='E'
WHERE AGENT_NUMBER =R_BUYERS_VALIDATE.AGENT_NUMBER;
ELSE
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='V'
WHERE AGENT_NUMBER =R_BUYERS_VALIDATE.AGENT_NUMBER;
END iF;
END;
END LOOP;
END BUYERS_VALID;
PROCEDURE create_buyers(
errbuf OUT VARCHAR2 ,
retcode OUT VARCHAR2 ,
p_row_num IN NUMBER)
IS
CURSOR lcu_buyers
IS
SELECT *
FROM XXABC_PO_AGENTS_STG
WHERE 1 =1
AND STATUS = 'V'
AND rownum <= p_row_num;
ln_location_id NUMBER;
ln_category_id NUMBER;
l_rowid VARCHAR2(4000);
ln_agent_id NUMBER;
ln_user_id NUMBER:=FND_PROFILE.VALUE('USER_ID');
LN_LOGIN_ID NUMBER:=FND_PROFILE.VALUE('LOGIN_ID');
l_conc_req_id NUMBER;
BEGIN
l_conc_req_id := fnd_profile.value('CONC_REQUEST_ID');
BEGIN
UPDATE XXABC_PO_AGENTS_STG
SET PROGRAM_ID=L_CONC_REQ_ID
WHERE STATUS = 'V'
AND ROWNUM <= P_ROW_NUM ;
COMMIT;
END;
FOR r_buyers IN lcu_buyers
LOOP
/*Fetchs the Agent ID value*/
BEGIN
SELECT person_id
INTO LN_AGENT_ID
FROM PER_PEOPLE_F
WHERE EMPLOYEE_NUMBER=R_BUYERS.AGENT_NUMBER
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,r_buyers.agent_number||' Agent Id '||' Not Found');
ln_agent_id:=NULL;
END;
/*Fetchs the location value for given location code*/
BEGIN
--if r_buyers.SHIP_TO_LOCATION is not null then
SELECT location_id
INTO ln_location_id
FROM hr_locations
WHERE LOCATION_CODE=R_BUYERS.SHIP_TO_LOCATION;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,r_buyers.agent_number||' Location Code'||r_buyers.ship_to_location||' Not Found');
ln_location_id:=NULL;
END;
/*Fetchs the category value for given category code*/
BEGIN
--if r_buyers.category_code is not null then
SELECT category_id
INTO ln_category_id
FROM mtl_categories
WHERE 1=1
AND SEGMENT1
||DECODE(SEGMENT2,NULL,NULL,'-')
|| SEGMENT2
||DECODE(SEGMENT3,NULL,NULL,'-')
|| SEGMENT3
||DECODE(SEGMENT4,NULL,NULL,'-')
|| segment4
||DECODE(segment5,NULL,NULL,'-')
|| segment5=r_buyers.category_code
||'.Default.Default';
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,r_buyers.agent_number||' Category '||r_buyers.category_code||'.Default.Default'||' Not Found ..');
LN_CATEGORY_ID:=NULL;
END;
IF LN_AGENT_ID IS NOT NULL THEN
BEGIN
INSERT
INTO PO_AGENTS
(
AGENT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
LOCATION_ID ,
CATEGORY_ID ,
AUTHORIZATION_LIMIT ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE
)
VALUES
(
LN_AGENT_ID,
sysdate,
LN_USER_ID,
LN_LOGIN_ID,
SYSDATE,
LN_USER_ID,
LN_LOCATION_ID,
LN_CATEGORY_ID,
NULL,
NULL,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL
);
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='P'
WHERE 1 =1
AND AGENT_NUMBER=R_BUYERS.AGENT_NUMBER;
FND_FILE.PUT_LINE(FND_FILE.LOG,r_buyers.agent_number||' Buyer successfully created..');
EXCEPTION
WHEN OTHERS THEN
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='E',
error_msg=error_msg
|| 'BUYER_CREATION_EXCEPTION;'
WHERE 1 =1
AND AGENT_NUMBER=R_BUYERS.AGENT_NUMBER;
FND_FILE.PUT_LINE(FND_FILE.LOG,R_BUYERS.AGENT_NUMBER||' Error Occured While Creating Buyer'||SQLERRM);
END;
ELSE
UPDATE XXABC_PO_AGENTS_STG
SET STATUS ='E',
error_msg=error_msg
||'INVALID_EMPLOYEE_ID;'
WHERE 1 =1
AND AGENT_NUMBER=R_BUYERS.AGENT_NUMBER;
END IF;
END LOOP;
END CREATE_BUYERS;
END XXABC_PO_BUYERS_PKG;
/
No comments:
Post a Comment