AIM: Component is used for Create the Requisition against the Blanket Purchase Agreement For different Locations.
SCRIPT:
CREATE OR REPLACE PACKAGE BODY APPS.XXTTK_BPO_PKG IS
PROCEDURE XXTTK_REQ ( ERRORBUF OUT VARCHAR2
,RETCODE OUT NUMBER
,P_ORG_ID IN OUT NUMBER) IS
CURSOR C1 IS SELECT
TRANSACTION_ID
,OPERATING_UNIT
,REQ_TYPE
,PREPARER
,LINE_TYPE
,ITEM
,DESCRIPTION
,QUANTITY
,DESTINATION_ORGANIZATION
,DELIVER_TO_LOCATION
,SOURCE_TYPE_CODE
,AUTHORIZATION_STATUS
,DELIVER_TO_REQUESTOR
,NEED_BY_DATE
,INTERFACE_SOURCE_CODE
,DESTINATION_TYPE_CODE
FROM XXTTK_REQ
WHERE ORG_ID = P_ORG_ID;
V_TRANSACTION_ID NUMBER;
V_ORG_ID NUMBER;
V_PROCESS_FLAG VARCHAR2(5);
V_ERROR_MESSAGE VARCHAR2(3500);
V_REQ_TYPE VARCHAR2(100);
V_PREPARER_ID NUMBER;
V_LINE_TYPE_ID number;
V_ITEM_ID NUMBER;
V_ITEM_DESCRIPTION VARCHAR2(500);
V_DESTINATION_ORGANIZATION NUMBER;
V_DELIVER_TO_LOCATION NUMBER;
V_REQ_SUR_TYPE VARCHAR2(100);
V_REQUESTOR_ID NUMBER;
V_COUNT NUMBER := 0;
BEGIN
DBMS_OUTPUT.put_line (‘PARAMETER : ORG_ID……’|| P_ORG_ID);
FOR VAR IN C1 LOOP
–=================
–validation start
–=============================
–validation for ORGANIZATION_ID
–============================
BEGIN
SELECT ORGANIZATION_ID
INTO V_ORG_ID
FROM HR_OPERATING_UNITS
WHERE NAME = VAR.OPERATING_UNIT;
IF
V_ORG_ID IS NOT NULL THEN
V_PROCESS_FLAG := ‘V’;
ELSE
V_PROCESS_FLAG := ‘E’;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line(‘NO DATA FOUND FOR THIS ORGANIZATION..’|| ‘ ‘||VAR.OPERATING_UNIT );
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘OPERATING UNIT NOT FOUND….’||VAR.OPERATING_UNIT;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line(‘MULTI ROWS RETURN THIS ORGANIZATION..’|| VAR.OPERATING_UNIT);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE :=V_ERROR_MESSAGE || ‘MULTIPLE ROWS RETURN ORG ID….’;
WHEN OTHERS
THEN DBMS_OUTPUT.put_line (‘NOTHING FOUND..’ || SQLCODE);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘ORG ID NOT FOUND….’||VAR.OPERATING_UNIT;
END;
–========================
–VALIDATTION FOR REQ_TYPE
–========================
BEGIN
SELECT LOOKUP_CODE
INTO V_REQ_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = ‘REQUISITION TYPE’
AND LOOKUP_CODE = VAR.REQ_TYPE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘NO DATA FOUND THIS REQUISITION TYPE..’||VAR.REQ_TYPE);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘REQUISITION TYPE NOT FOUND….’;
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line(‘MULTI ROWS RETURN THIS REQUISITION TYPE..’||VAR.REQ_TYPE);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘MULTIPLE ROWS RETURN REQUISITION TYPE….’||VAR.REQ_TYPE;
WHEN OTHERS THEN
dbms_output.put_line(‘NOT VALID REQUISITION TYPE..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘NOT VALID REQUISITION TYPE….’||VAR.REQ_TYPE;
END;
–========================
–VALIDATION FOR PREPARER
–======================
BEGIN
SELECT PERSON_ID
INTO V_PREPARER_ID
FROM PER_ALL_PEOPLE_F
WHERE FULL_NAME = VAR.PREPARER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’NO DATA FOUND THIS PREPARER..’||VAR.PREPARER);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘PREPARER NOT FOUND….’||VAR.PREPARER;
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’MULTI ROWS RETURN THIS PREPARER..’||VAR.PREPARER);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE :=’MULTIPLE ROWS RETURN PREPARER….’||VAR.PREPARER;
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’NOT VALID PREPARER..’||VAR.PREPARER);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘NOT VALID PREPARER….’||VAR.PREPARER;
END;
–============================
–VALIDATION FOR LINE_TYPE_ID
–============================
BEGIN
SELECT LINE_TYPE_ID
INTO V_LINE_TYPE_ID
FROM PO_LINE_TYPES
WHERE LINE_TYPE = VAR.LINE_TYPE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’NO DATA FOUND FOR THIS LINE_TYPE..’||VAR.PREPARER);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE :=’NOT FOUND THIS LINE_TYPE….’;
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’MULTI ROWS RETURN THIS LINE TYPE..’||VAR.PREPARER);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘MULTIPLE ROWS RETURN LINE TYPE….’||VAR.PREPARER;
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’NOT VALID LINE TYPE..’||VAR.PREPARER);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘NOT VALID PREPARER….’||VAR.PREPARER;
END;
–===================
–VALIDATION FOR ITEM
–===================
BEGIN
SELECT DISTINCT INVENTORY_ITEM_ID
INTO V_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 =VAR.ITEM;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘ITEM NOT FOUND FOR EXISTING ITEM’||’ ‘||VAR.ITEM);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘ITEM NOT FOUND….’;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘MULTIPLE ROWS RETURN FOR THIS ITEM’||VAR.ITEM);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘MULTIPLE ROWS RETURN….’||VAR.ITEM;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER ERROR’||VAR.ITEM);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := ‘OTHER ERROR CAPTURED….’||VAR.ITEM;
END;
–================================
–VALIDATION FOR ITEM DESCRIPTION
–================================
BEGIN
SELECT DISTINCT DESCRIPTION
INTO V_ITEM_DESCRIPTION
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 = VAR.ITEM;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO DATA FOUND FOR DESC..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’NO DATA FOR DESC…..’;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘MULTIPLLE ROWS RETURN…’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’MULTIPLE ROWS DATA FOR DESC…..’;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER ERROR CAP..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’OTHER…..’;
END;
–=========================
–VALIDATION FOR QUANTITY
–=======================
BEGIN
IF VAR.QUANTITY IS NULL
THEN
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE :=V_ERROR_MESSAGE || ‘QUANTITY CAN NOT BE NULL’;
DBMS_OUTPUT.put_line (‘QUANTITY CAN NOT BE NULL’);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER ERROR CAPTURED…….’);
END;
–===========================
–VALIDATION FRO NEED-BY-DATE
–===========================
BEGIN
IF VAR.NEED_BY_DATE > SYSDATE
THEN
V_PROCESS_FLAG := ‘V’;
ELSE
DBMS_OUTPUT.PUT_LINE(‘NEED-BY-DATE CAN NOT BE LESS THEN CREATION DATE !’||’ ‘||VAR.NEED_BY_DATE);
V_PROCESS_FLAG:= ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE || ‘NEED BY DATE CAN NOT BE LESS THEN CREATION DATE’|| VAR.NEED_BY_DATE;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER ERROR CAP..’);
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’OTHER…..’;
END;
–========================================
–VALLIDATION FOR DESTINATION_ORGANIZATION
—========================================
BEGIN
SELECT ORGANIZATION_ID
INTO V_DESTINATION_ORGANIZATION
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_NAME = VAR.DESTINATION_ORGANIZATION;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO DATA FOUND FOR DESTINATION_ORGANIZATION..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’NO DATA FOR DESTINATION_ORGANIZATION…..’;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘MULTIPLLE ROWS RETURN…’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’MULTIPLE ROWS DATA FOR DESTINATION_ORGANIZATION…..’;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER ERROR CAP..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’OTHER…..’;
END;
–====================================
–VVALIDATION FOR DESTINATION LOCATION
–====================================
BEGIN
SELECT LOCATION_ID
INTO V_DELIVER_TO_LOCATION
FROM HR_LOCATIONS
WHERE LOCATION_CODE = VAR.DELIVER_TO_LOCATION;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO DATA FOUND FOR DELIVER_TO_LOCATION..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’NO DATA FOR DELIVER_TO_LOCATION…..’;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘MULTIPLLE ROWS RETURN…’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’MULTIPLE ROWS DATA FOR DELIVER_TO_LOCATION…..’;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER ERROR CAP..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’OTHER…..’;
END;
–==========================
–VALIDATION FOR REQ SOURCE
–=========================
BEGIN
SELECT LOOKUP_CODE
INTO V_REQ_SUR_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = ‘REQUISITION SOURCE TYPE’
AND LOOKUP_CODE = VAR.SOURCE_TYPE_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO DATA FOUND FOR DESC..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’NO DATA FOR DESC…..’;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘MULTIPLLE ROWS RETURN…’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’MULTIPLE ROWS DATA FOR DESC…..’;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘OTHER ERROR CAP..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’OTHER…..’;
END ;
–==========================
— VALIDATION FOR REQUESTOR
–==========================
BEGIN
SELECT PERSON_ID
INTO V_REQUESTOR_ID
FROM PER_ALL_PEOPLE_F
WHERE FULL_NAME = VAR.DELIVER_TO_REQUESTOR;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’NO DATA FOUND THIS REQUESTOR..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’NOT FOUND REQUESTOR….’;
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’MULTI ROWS RETURN THIS REQUESTOR..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’MULTIPLE ROWS RETURN REQUESTOR….’;
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’NOT VALID REQUESTOR..’);
V_PROCESS_FLAG := ‘E’;
V_ERROR_MESSAGE := V_ERROR_MESSAGE||’NOT VALID REQUESTOR….’;
END;
–===========================
–UPDATE INTO STAGING TABLE
–==========================
BEGIN
IF V_PROCESS_FLAG = ‘V’ THEN
SELECT PO_REQUISITIONS_INTERFACE_S.NEXTVAL
INTO V_TRANSACTION_ID
FROM DUAL;
BEGIN
UPDATE XXTTK_REQ
SET VALIDATED_FLAG = V_PROCESS_FLAG
,ERROR_MESSAGE = ‘DATA SUCCESFULLY INSERTED INTO REQUISITION INTERFACE TABLE’
WHERE org_id= v_org_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(‘error while updating table …’|| SQLERRM);
END;
ELSIF V_PROCESS_FLAG = ‘E’
THEN
BEGIN
UPDATE XXTTK_REQ
SET VALIDATED_FLAG= V_PROCESS_FLAG,
ERROR_MESSAGE = V_ERROR_MESSAGE
WHERE org_id=v_org_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(‘error while updating table…..’|| SQLERRM);
DBMS_OUTPUT.put_line (‘validated flag E …’ || SQLERRM);
END;
END IF;
END;
–============================
–INSERT INTO INTERFACE TABLE
–============================
BEGIN
IF
V_PROCESS_FLAG = ‘V’ THEN
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(TRANSACTION_ID
,ORG_ID
,REQUISITION_TYPE
,PREPARER_ID
,LINE_TYPE_ID
,ITEM_ID
,ITEM_DESCRIPTION
,QUANTITY
,NEED_BY_DATE
,DESTINATION_ORGANIZATION_ID
,DELIVER_TO_LOCATION_ID
,SOURCE_TYPE_CODE
,AUTHORIZATION_STATUS
,DELIVER_TO_REQUESTOR_ID
,INTERFACE_SOURCE_CODE
,DESTINATION_TYPE_CODE
)
VALUES
(
V_TRANSACTION_ID
,V_ORG_ID
,V_REQ_TYPE
,V_PREPARER_ID
,V_LINE_TYPE_ID
,V_ITEM_ID
,V_ITEM_DESCRIPTION
,VAR.QUANTITY
,VAR.NEED_BY_DATE
,V_DESTINATION_ORGANIZATION
,V_DELIVER_TO_LOCATION
,V_REQ_SUR_TYPE
,VAR.AUTHORIZATION_STATUS
,V_REQUESTOR_ID
,VAR.INTERFACE_SOURCE_CODE
,VAR.DESTINATION_TYPE_CODE
);
V_COUNT := V_COUNT+sql%Rowcount;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘PROCES FLAG……’|| V_PROCESS_FLAG);
DBMS_OUTPUT.put_line (‘ERROR !!! DATA DATA NOT INSERTED INTO INTERFACE TABLE…’|| SQLERRM);
END;
END LOOP;
IF V_PROCESS_FLAG = ‘V’ THEN
DBMS_OUTPUT.put_line (‘REQUISITION INFORMATION INSERTED SUCESSFULY INTO INTERFACE TABLE….’);
DBMS_OUTPUT.PUT_LINE(‘NUMBER OF ROWS INSERTED’||’ ‘||V_COUNT);
END IF ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘NUMBER OF ROWS REJECTED’||V_COUNT);
COMMIT ;
END ;
END XXTTK_BPO_PKG ;
After create the Requisition we need to run the requisition Import program.
Parameter : Locations
Requisition for different locations:
After Create the requisition we need to Run the Create release Program on SRS Window
Check the Release NAV: Purchase Order > Purchase Order Summary
check The Status = Approved
Do drop a note by writing us at Dileep.dinesh@doyensys.com or use the comment section below to ask your questions
