AS
/*****************************************************************************************
/* Header: Package body creation script for converting <Object Name> Created <Date>
/*
/* Author: <_________>
/*
/* Description: Contains functions/ procedures used converting <Objec Name>
/*
/*
/* Revision History:
/*
/* Date Prepared By Description of Revision
/*
/* <________> <___________> Created
/* <________> <___________> Revised
/*
/*=======================================================================================*/
-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : MAIN | --
-- | | --
-- |DESCRIPTION : Executes different procedures based on the flags passed by the conc program| --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |------------- ---- -------------- ---------------------------------------- | --
-- | errbuf OUT VARCHAR2 Will Store/return the error message | --
-- | retcode OUT NUMBWER Will return the status of the procedure | --
-- | | --
-- | p_load_data_flag IN VARCHAR2 Yes - Load Data | --
-- | No - No | --
-- | | --
-- | p_transform_data_flag IN VARCHAR2 RT - Re Transform Data | --
-- | Yes - Transform Data | --
-- | No - No | --
-- | | --
-- | p_validate_flag IN VARCHAR2 RV - Re Validate | --
-- | Yes - Validate | --
-- | No - No | --
-- | | --
-- | p_process_data_flag IN VARCHAR2 Yes - Process data | --
-- | No - No | --
-- | | --
-- |-----------------------------------------------------------------------------------------+ --
PROCEDURE main(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
-- p_prevalid_Status_flag IN VARCHAR2,
p_trans_Status_flag IN VARCHAR2,
p_validate_data_flag IN VARCHAR2,
p_process_data_flag IN VARCHAR2
)
IS
BEGIN
IF p_trans_status_flag = 'Yes' THEN
transform_data(gc_newrecord_flag);
ELSIF p_trans_status_flag = 'RT' THEN
transform_data(gc_trnsf_error_flag);
END IF;
IF p_validate_data_flag = 'Yes' THEN
validate_data(gc_trnsf_flag);
ELSIF p_validate_data_flag = 'RV' THEN
validate_data(gc_validation_error_flag);
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.log,'MAIN-ERR :> '||SQLERRM ||', '||SQLCODE);
FND_FILE.PUT_LINE(FND_FILE.log,' --------- Procedure MAIN Exit ---------');
FND_FILE.PUT_LINE(FND_FILE.log,RPAD(' ',80,' '));
FND_FILE.PUT_LINE(FND_FILE.log,RPAD('*',80,'*'));
END main;
-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : transform_data | --
-- | | --
-- |DESCRIPTION : Transforms the data with predefined transformation rules and sets the | --
-- | status 'T' for transformed | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |------------- ---- -------------- ---------------------------------------- | --
-- | p_transform_data_flag IN VARCHAR2 Y/RT | --
-- |-----------------------------------------------------------------------------------------+ --
PROCEDURE transform_data(p_interface_status_flag IN VARCHAR2)
IS
ln_trnsf_rec_cnt NUMBER:=0;
ln_trnsf_rec_line_cnt NUMBER:=0;
ln_progress NUMBER:=-1;
ln_count NUMBER:=0;
--======================================================
-- Cursor to count the to be processed records
--======================================================
CURSOR lcu_rec_count(p_interface_status VARCHAR2) IS
SELECT count(*)
FROM SRI_po_requisitions_data
WHERE interface_status =(p_interface_status);
--====================================
-- Cursor to count the failed records
--====================================
CURSOR lcu_rec_fail(p_interface_status VARCHAR2,p_request_id NUMBER) IS
SELECT count(*)
FROM SRI_po_requisitions_data
WHERE interface_status=p_interface_status
AND request_id=p_request_id;
--====================================
-- Cursor to get the data from Req Data table
--====================================
CURSOR lcu_openpo_req_trnsf(p_interface_status VARCHAR2)
IS
SELECT RECORD_ID
,REQ_NUMBER
,AGENT_NAME
,ITEM_NAME
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,DESTINATION_ORGANIZATION
,LOCATION_NAME
,ORGANIZATION_NAME
,NEED_BY_DATE
,OBJECT_NAME
,REQUEST_ID
,LAST_UPDATED_DATE
,LAST_UPDATED_BY
,interface_status
,error_code
,error_message
,program_id
,program_appl_id
,program_update_date
FROM po.SRI_po_requisitions_data
WHERE interface_status=p_interface_status;
TYPE openpo_req_trnsf_tbl_type IS TABLE OF lcu_openpo_req_trnsf%ROWTYPE
INDEX BY BINARY_INTEGER;
lt_openpo_req_trnsf_tab openpo_req_trnsf_tbl_type;
BEGIN
ln_trnsf_rec_cnt :=0;
OPEN lcu_rec_count(p_interface_status_flag);
FETCH lcu_rec_count
INTO ln_trnsf_rec_cnt;
CLOSE lcu_rec_count;
apps.FND_FILE.PUT_LINE(FND_FILE.log,' ------ Procedure TRANSFORM_DATA ------');
apps.FND_FILE.PUT_LINE(FND_FILE.log,' ');
apps.FND_FILE.PUT_LINE(FND_FILE.log,' ------ Procedure TRANSFORM_DATA ------');
apps.FND_FILE.PUT_LINE(FND_FILE.log,' ');
apps.FND_FILE.PUT_LINE(FND_FILE.log,' Number of Records Tobe Transformed For Headers:-> ' || ln_trnsf_rec_cnt);
BEGIN
OPEN lcu_openpo_req_trnsf(p_interface_status_flag);
LOOP
ln_progress := 1;
lt_openpo_req_trnsf_tab.DELETE;
gn_bulk_error := 0;
FETCH lcu_openpo_req_trnsf
BULK COLLECT INTO lt_openpo_req_trnsf_tab LIMIT gn_bulk_limit;
apps.FND_FILE.PUT_LINE(FND_FILE.log,' Cursor lcu_openpo_header_trnsf fetched :-> '||lt_openpo_req_trnsf_tab.COUNT);
IF lt_openpo_req_trnsf_tab.COUNT > 0 THEN
FOR i IN lt_openpo_req_trnsf_tab.FIRST .. lt_openpo_req_trnsf_tab.COUNT
LOOP
lt_openpo_req_trnsf_tab(i).interface_Status := gc_newrecord_flag ;
lt_openpo_req_trnsf_tab(i).error_code := NULL;
lt_openpo_req_trnsf_tab(i).error_message := 'ERR-TR>';
-- Apply Transformation Rules
NULL;
IF lt_openpo_req_trnsf_tab(i).interface_Status <> gc_trnsf_error_flag THEN
lt_openpo_req_trnsf_tab(i).interface_Status := gc_trnsf_flag;
lt_openpo_req_trnsf_tab(i).error_code := NULL;
lt_openpo_req_trnsf_tab(i).error_message := 'Data Transformation Completed!';
END IF;
IF lt_openpo_req_trnsf_tab(i).interface_Status = gc_trnsf_flag THEN
BEGIN
ln_progress := 2;
INSERT INTO apps.SRI_po_requisitions_stg(RECORD_ID
,REQ_NUMBER
,AGENT_NAME
,AGENT_ID
,ITEM_NAME
,ITEM_ID
,UOM
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,DESTINATION_ORGANIZATION
,DESTINATION_ORG_ID
,LOCATION_NAME
,location_id
,ORGANIZATION_NAME
,ORG_ID
,CHARGE_ACC_ID
,NEED_BY_DATE
,error_message
,error_code
,interface_status
,OBJECT_NAME
,REQUEST_ID
,LAST_UPDATED_DATE
,LAST_UPDATED_BY
,program_id
,program_application_id
,program_update_date
)
VALUES ( lt_openpo_req_trnsf_tab(i).RECORD_ID
,lt_openpo_req_trnsf_tab(i).REQ_NUMBER
,lt_openpo_req_trnsf_tab(i).AGENT_NAME
,NULL
,lt_openpo_req_trnsf_tab(i).ITEM_NAME
,NULL
,NULL
,lt_openpo_req_trnsf_tab(i).UOM_CODE
,lt_openpo_req_trnsf_tab(i).QUANTITY
,lt_openpo_req_trnsf_tab(i).UNIT_PRICE
,lt_openpo_req_trnsf_tab(i).DESTINATION_ORGANIZATION
,NULL
,lt_openpo_req_trnsf_tab(i).LOCATION_NAME
,NULL
,lt_openpo_req_trnsf_tab(i).ORGANIZATION_NAME
,NULL
,NULL
,lt_openpo_req_trnsf_tab(i).NEED_BY_DATE
,lt_openpo_req_trnsf_tab(i).error_message
,lt_openpo_req_trnsf_tab(i).error_code
,lt_openpo_req_trnsf_tab(i).interface_status
,lt_openpo_req_trnsf_tab(i).OBJECT_NAME
,gn_request_id
,gd_current_date
,gn_user_id
,gn_program_id
,gn_prog_appl_id
,gd_current_date
);
END;
--NULL;
END IF;
UPDATE SRI_po_requisitions_data
SET interface_status = lt_openpo_req_trnsf_tab(i).interface_Status
, error_message = lt_openpo_req_trnsf_tab(i).error_message
, error_code = lt_openpo_req_trnsf_tab(i).error_code
, request_id = gn_request_id
, last_updated_date = gd_current_date
, last_updated_by = gn_user_id
, program_id = gn_program_id
, program_appl_id = gn_prog_appl_id
, program_update_date = gd_current_date
WHERE record_id = lt_openpo_req_trnsf_tab(i).record_id;
END LOOP;
END IF;
/* BEGIN
ln_progress :=3;
FORALL i IN lt_openpo_req_trnsf_tab.FIRST .. lt_openpo_req_trnsf_tab.COUNT SAVE EXCEPTIONS
UPDATE SRI_po_requisitions_data
SET interface_status = lt_openpo_req_trnsf_tab(i).interface_Status
, error_message = lt_openpo_req_trnsf_tab(i).error_message
, error_code = lt_openpo_req_trnsf_tab(i).error_code
, request_id = gn_request_id
, last_updated_date = gd_current_date
, last_updated_by = gn_user_id
, program_id = gn_program_id
, program_appl_id = gn_prog_appl_id
, program_update_date = gd_current_date
WHERE record_id = lt_openpo_req_trnsf_tab(i).record_id;
EXCEPTION
WHEN OTHERS THEN
gn_bulk_error := SQL%BULK_EXCEPTIONS.COUNT;
apps.FND_FILE.PUT_LINE(FND_FILE.log,'PREVAL-RULE-ERR @'||ln_progress||' No Of Error Records While Updating SRI_po_requisitions_stg: '
||gn_bulk_error);
FOR i IN 1..gn_bulk_error
LOOP
apps.FND_FILE.PUT_LINE(FND_FILE.log,'Error# '||i||' at iterration# '|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
apps.FND_FILE.PUT_LINE(FND_FILE.log,'Error Message is '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
--RAISE;
END; */
EXIT WHEN lcu_openpo_req_trnsf%NOTFOUND;
END LOOP;
CLOSE lcu_openpo_req_trnsf;
ln_count :=0;
OPEN lcu_rec_fail(gc_trnsf_error_flag, gn_request_id);
FETCH lcu_rec_fail
INTO ln_count;
CLOSE lcu_rec_fail;
ln_trnsf_rec_cnt := ln_trnsf_rec_cnt - ln_count;
apps.FND_FILE.PUT_LINE(FND_FILE.log,' Number of Records Transformed for headers :-> ' || ln_trnsf_rec_cnt);
apps.FND_FILE.PUT_LINE(FND_FILE.log,' Number of Records Failed for headers :-> ' || ln_count);
END;
EXCEPTION WHEN OTHERS THEN
apps.FND_FILE.PUT_LINE(FND_FILE.log,'TRNSF-ERR @'||ln_progress|| ' with ' ||SQLERRM||', '||SQLCODE);
apps.FND_FILE.PUT_LINE(FND_FILE.log,' ');
apps.FND_FILE.PUT_LINE(FND_FILE.log,' ------ Procedure TRANSFORM_DATA Exit ------');
apps.FND_FILE.PUT_LINE(FND_FILE.log,' ');
END transform_data;
-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : validate_data | --
-- | | --
-- |DESCRIPTION : Validates Data transformed data and sets the flag to 'V' for valid and 'E' | --
-- | for Error | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |------------- ---- -------------- ---------------------------------------- | --
-- | p_validate_data_flag IN VARCHAR2 Y/RV | --
-- |-----------------------------------------------------------------------------------------+ --
PROCEDURE validate_data(p_validate_data_flag IN VARCHAR2)
IS
ln_count NUMBER:=0;
ln_valid_line_count NUMBER:=0;
ln_valid_count NUMBER:=0;
ln_progress NUMBER:=-1;
ln_org_id NUMBER:=NULL;
ln_buyer_id NUMBER:=NULL;
ln_destination_organization_id VARCHAR2(100);
ln_charge_id NUMBER:=NULL;
lc_uom_code VARCHAR2(10);
ln_item_id NUMBER:=NULL;
lc_ou_name VARCHAR2(50);
ln_bg_id NUMBER:=NULL;
--=======================
--Cursor to Validate OU
--=======================
CURSOR lcu_ou(p_ou_name VARCHAR2)
IS
SELECT name, organization_id,business_group_id
FROM hr_operating_units
WHERE UPPER(name) = UPPER(TRIM(p_ou_name));
--==============================
--Cursor to Validate Buyer
--==============================
CURSOR lcu_buyer(p_buyer_name VARCHAR2)
IS
SELECT pa.agent_id
FROM apps.po_agents pa,
apps.per_all_people_f pap
WHERE pap.person_id = pa.agent_id
AND UPPER(pap.full_name) = UPPER(p_buyer_name)
AND NVL(effective_end_date,SYSDATE+1) >= SYSDATE;
--===================================
--Cursor to validate Item
--==================================
CURSOR lcu_product_val(p_product_value VARCHAR2,p_org_code NUMBER)
IS
SELECT inventory_item_id
FROM apps.mtl_system_items_vl msi
WHERE UPPER(segment1) = UPPER(TRIM(p_product_value))
AND purchasing_item_flag='Y'
AND organization_id = p_org_code ; --(select DISTINCT master_organization_id from apps.MTL_PARAMETERS WHERE );
--===================================
--Cursor to validate Item
--==================================
CURSOR lcu_desc_Org(p_desc_code VARCHAR2)
IS
SELECT DISTINCT ORGANIZATION_ID,MATERIAL_OVERHEAD_ACCOUNT
FROM MTL_PARAMETERS
WHERE ORGANIZATION_CODE=p_desc_code;
--============================
--Cursor to Validate UOM_CODE
--============================
CURSOR lcu_uom_code(p_uom_code VARCHAR2)
IS
SELECT uom_code
FROM apps.mtl_units_of_measure_vl
WHERE UPPER(uom_code) = UPPER(TRIM(p_uom_code));
--===================================
--Cursor to get failed records count for headers
--==================================
CURSOR lcu_fail_cnt(p_status_flag VARCHAR2, p_request_id NUMBER)
IS
SELECT COUNT(1)
FROM SRI_po_requisitions_stg
WHERE interface_status =p_status_flag
AND request_id = p_request_id;
--================================
--Cursor to Fecth the Header Data
--================================
CURSOR lcu_po_Req_data IS
SELECT RECORD_ID
,REQ_NUMBER
,AGENT_NAME
,AGENT_ID
,ITEM_NAME
,ITEM_ID
,UOM
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,DESTINATION_ORGANIZATION
,DESTINATION_ORG_ID
,LOCATION_NAME
,location_id
,ORGANIZATION_NAME
,ORG_ID
,CHARGE_ACC_ID
,NEED_BY_DATE
,error_message
,error_code
,interface_status
,OBJECT_NAME
,REQUEST_ID
,LAST_UPDATED_DATE
,LAST_UPDATED_BY
,program_id
,program_application_id
,program_update_date
FROM SRI_po_requisitions_stg
WHERE interface_Status = p_validate_data_flag
ORDER BY record_id ASC;
TYPE per_po_req_tbl_typ IS TABLE OF lcu_po_Req_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per_po_req_vld_typ per_po_req_tbl_typ;
BEGIN
ln_progress :=1;
FND_FILE.PUT_LINE(FND_FILE.log,' ------- Procedure VALIDATE_PURCHASE_REQUISITIONS_DATA -------');
FND_FILE.PUT_LINE(FND_FILE.log,RPAD(' ',80,' '));
FND_FILE.PUT_LINE(FND_FILE.log,' ------- Procedure VALIDATE_PURCHASE_REQUISITIONS_DATA -------');
FND_FILE.PUT_LINE(FND_FILE.log,RPAD(' ',80,' '));
OPEN lcu_po_Req_data;
LOOP
lt_per_po_req_vld_typ.DELETE;
FETCH lcu_po_Req_data BULK COLLECT INTO lt_per_po_req_vld_typ LIMIT gn_bulk_limit;
FND_FILE.PUT_LINE(FND_FILE.log,' Purchase Orders Headers fetched for Validation :->'||lt_per_po_req_vld_typ.COUNT);
IF lt_per_po_req_vld_typ.COUNT > 0 THEN
FOR i IN 1..lt_per_po_req_vld_typ.COUNT
LOOP
lt_per_po_req_vld_typ(i).error_code := 'EC';
lt_per_po_req_vld_typ(i).error_message := 'ERR-VL>';
ln_org_id :=NULL;
ln_buyer_id :=NULL;
ln_item_id :=null;
ln_destination_organization_id := null;
ln_charge_id :=null;
lc_uom_code :=null;
-- Validating Operating Unit
IF lt_per_po_req_vld_typ(i).ORGANIZATION_NAME IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ001';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'OperatingUnit Is Null';
ELSE
OPEN lcu_ou(lt_per_po_req_vld_typ(i).ORGANIZATION_NAME);
FETCH lcu_ou INTO lc_ou_name, ln_org_id,ln_bg_id;
CLOSE lcu_ou;
IF lc_ou_name IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ002';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'OperatingUnit Is Invalid';
ELSE
lt_per_po_req_vld_typ(i).org_id := ln_org_id;
--sri_conv_util_pkg.write_log('operating unit validation completed succfully');
END IF;
END IF;
IF lt_per_po_req_vld_typ(i).agent_name IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ003';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Buyer is null';
ELSE
OPEN lcu_buyer(lt_per_po_req_vld_typ(i).agent_name);
FETCH lcu_buyer INTO ln_buyer_id;
CLOSE lcu_buyer;
IF ln_buyer_id IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ004';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Buyer is Invalid';
ELSE
lt_per_po_req_vld_typ(i).agent_id:=ln_buyer_id;
END IF;
END IF;
----- Organization Code ---
IF lt_per_po_req_vld_typ(i).destination_organization IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ005';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Buyer is null';
ELSE
OPEN lcu_desc_Org(lt_per_po_req_vld_typ(i).destination_organization);
FETCH lcu_desc_Org INTO ln_destination_organization_id,ln_charge_id;
CLOSE lcu_desc_Org;
IF ln_destination_organization_id IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ006';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Organization ID is Invalid';
ELSE
lt_per_po_req_vld_typ(i).destination_org_id:=ln_destination_organization_id;
END IF;
IF ln_charge_id IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ007';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Organization ID is Invalid';
ELSE
lt_per_po_req_vld_typ(i).charge_acc_id:=ln_charge_id;
END IF;
END IF;
--- Item Validation ------
IF lt_per_po_req_vld_typ(i).ITEM_NAME IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ008';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Item Null';
ELSE
OPEN lcu_product_val(lt_per_po_req_vld_typ(i).item_name,ln_destination_organization_id);
FETCH lcu_product_val INTO ln_item_id;
CLOSE lcu_product_val;
IF ln_item_id IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ009';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Invalid Item';
ELSE
lt_per_po_req_vld_typ(i).item_id:=ln_item_id;
END IF;
END IF;
-- Validating UOM Code
IF lt_per_po_req_vld_typ(i).uom IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ010';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||' UOM Null';
ELSE
OPEN lcu_uom_code(lt_per_po_req_vld_typ(i).uom);
FETCH lcu_uom_code INTO lc_uom_code;
CLOSE lcu_uom_code;
IF lc_uom_code IS NULL THEN
lt_per_po_req_vld_typ(i).interface_status := gc_validation_error_flag;
lt_per_po_req_vld_typ(i).error_code := lt_per_po_req_vld_typ(i).error_code||', '||'POREQ011';
lt_per_po_req_vld_typ(i).error_message := lt_per_po_req_vld_typ(i).error_message||', '||'Invalid UOM';
ELSE
lt_per_po_req_vld_typ(i).uom_code:=lc_uom_code;
END IF;
END IF;
BEGIN
ln_progress :=2;
--FORALL i IN lt_per_po_req_vld_typ.FIRST .. lt_per_po_req_vld_typ.COUNT SAVE EXCEPTIONS
UPDATE SRI_po_requisitions_stg
SET interface_status = lt_per_po_req_vld_typ(i).interface_status
,error_message = lt_per_po_req_vld_typ(i).error_message
,error_code = lt_per_po_req_vld_typ(i).error_code
,agent_id = lt_per_po_req_vld_typ(i).agent_id
,item_id = lt_per_po_req_vld_typ(i).item_id
,destination_org_id = lt_per_po_req_vld_typ(i).destination_org_id
,org_id = lt_per_po_req_vld_typ(i).org_id
,charge_acc_id = lt_per_po_req_vld_typ(i).charge_acc_id
,uom_code = lt_per_po_req_vld_typ(i).uom_code
,request_id = gn_request_id
,last_updated_date = gd_current_date
,last_updated_by = gn_user_id
,program_id = gn_program_id
,program_application_id = gn_prog_appl_id
,program_update_date = gd_current_date
WHERE record_id = lt_per_po_req_vld_typ(i).record_id;
EXCEPTION
WHEN OTHERS THEN
--gn_bulk_error := SQL%BULK_EXCEPTIONS.COUNT;
apps.FND_FILE.PUT_LINE(FND_FILE.log,'VALIDATE-ERR @'||ln_progress||' No Of Error Records While Updating sripo_bpapo_headers_stg: '); --||gn_bulk_error);
/*FOR i IN 1..gn_bulk_error
LOOP
apps.sri_conv_util_pkg.write_log('Error# '||i||' at iterration# '|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
apps.sri_conv_util_pkg.write_log('Error Message is '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP; */
END;
END LOOP;
END IF;
EXIT WHEN lcu_po_Req_data%NOTFOUND;
END LOOP;
CLOSE lcu_po_Req_data;
--=================================
--Cursor to get Error records count for headers
--=================================
OPEN lcu_fail_cnt(gc_validation_error_flag, gn_request_id);
FETCH lcu_fail_cnt INTO ln_count;
CLOSE lcu_fail_cnt;
ln_valid_count := ln_valid_count-ln_count;
FND_FILE.PUT_LINE(FND_FILE.log,' Number Of Records Processed For Headers :-> '||ln_valid_count);
FND_FILE.PUT_LINE(FND_FILE.log,' Number Of Records Errored For Headers :-> '||ln_count);
FND_FILE.PUT_LINE(FND_FILE.log,' ');
FND_FILE.PUT_LINE(FND_FILE.log,' ------- Procedure Validate Requistions Data Exit -------');
FND_FILE.PUT_LINE(FND_FILE.log,' ');
END validate_data;
-- |-----------------------------------------------------------------------------------------+ --
-- |PROCEDURE : load_interface_data | --
-- | | --
-- |DESCRIPTION : Procedure will call the API to load to base from staging tables | --
-- | | --
-- |PARAMETERS : | --
-- | | --
-- |NAME MODE TYPE DESCRIPTION | --
-- |------------- ---- -------------- ---------------------------------------- | --
-- | | --
-- |-----------------------------------------------------------------------------------------+ --
PROCEDURE load_interface_data(p_interface_flag VARCHAR2)
IS
ln_progress NUMBER:=-1;
ln_count NUMBER;
lc_Source_code VARCHAR2(20) := 'OPTIMIZA'
--============================
--Cursor to get records count for headers
--============================
CURSOR lcu_load_cnt(p_interface_flag varchar2)
IS
SELECT COUNT(1)
FROM SRI_po_requisitions_stg
WHERE interface_status=p_interface_flag;
--============================================
--Cursor to Fecth the data to Process Headers
--============================================
CURSOR lcu_po_req_data(p_interface_flag VARCHAR2)
IS
SELECT RECORD_ID
,REQ_NUMBER
,AGENT_NAME
,AGENT_ID
,ITEM_NAME
,ITEM_ID
,UOM
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,DESTINATION_ORGANIZATION
,DESTINATION_ORG_ID
,LOCATION_NAME
,location_id
,ORGANIZATION_NAME
,ORG_ID
,CHARGE_ACC_ID
,NEED_BY_DATE
,error_message
,error_code
,interface_status
,OBJECT_NAME
,REQUEST_ID
,LAST_UPDATED_DATE
,LAST_UPDATED_BY
,program_id
,program_application_id
,program_update_date
FROM SRI_po_requisitions_stg
WHERE interface_Status = p_interface_flag;
TYPE per_po_req_tbl_typ IS TABLE OF lcu_po_req_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per_po_req_vld_typ per_po_req_tbl_typ;
BEGIN
ln_progress:=1;
FND_FILE.PUT_LINE(FND_FILE.log, ' -------- Procedure LOAD_VALID_DATA_TO_INTERFACE --------');
FND_FILE.PUT_LINE(FND_FILE.log, RPAD(' ',80,' '));
FND_FILE.PUT_LINE(FND_FILE.log, ' -------- Procedure LOAD_VALID_DATA_TO_INTERFACE --------');
FND_FILE.PUT_LINE(FND_FILE.log, RPAD(' ',80,' '));
--========================================
--Cursor to get Tobe Loaded records count for headers
--=======================================
OPEN lcu_load_cnt(p_interface_flag);
FETCH lcu_load_cnt INTO ln_count;
CLOSE lcu_load_cnt;
FND_FILE.PUT_LINE(FND_FILE.log,' Number Of Records ToBe Loaded For Headers :-> '||ln_count);
OPEN lcu_po_req_data(p_interface_flag);
LOOP
lt_per_po_req_vld_typ.DELETE;
FETCH lcu_headers_data BULK COLLECT INTO lt_per_po_req_vld_typ LIMIT gn_bulk_limit;
sri_conv_util_pkg.write_log(' Cursor lcu_headers_data Headers fetched :-> '||lt_per_po_req_vld_typ.COUNT);
IF lt_per_po_req_vld_typ.COUNT > 0 THEN
ln_progress :=2;
FOR i IN lt_per_po_req_vld_typ.FIRST .. lt_per_po_req_vld_typ.COUNT
LOOP
BEGIN
INSERT INTO apps.po_headers_interface( req_number_segment1
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CREATION_DATE
,CREATED_BY
,INTERFACE_SOURCE_CODE
,SOURCE_TYPE_CODE
,REQUISITION_TYPE
,DESTINATION_TYPE_CODE
,QUANTITY
,UNIT_PRICE
,AUTHORIZATION_STATUS
,PREPARER_ID
,AUTOSOURCE_FLAG
,ITEM_ID
,UOM_CODE
,DESTINATION_ORGANIZATION_ID
,DELIVER_TO_LOCATION_ID
,DELIVER_TO_REQUESTOR_ID
,NEED_BY_DATE
,GL_DATE
,CHARGE_ACCOUNT_ID
,ORG_ID
--,CATEGORY_ID
)
VALUES(lt_per_po_req_vld_typ(i).REQ_NUMBER
,apps.FND_GLOBAL.user_id
,gd_current_date
,gd_current_date
,apps.FND_GLOBAL.user_id
,apps.FND_GLOBAL.login_id
,lc_Source_code
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,lt_per_po_req_vld_typ(i).QUANTITY
,lt_per_po_req_vld_typ(i).UNIT_PRICE
,'APPROVED'
,lt_per_po_req_vld_typ(i).AGENT_ID
,'P'
,lt_per_po_req_vld_typ(i).ITEM_ID
,lt_per_po_req_vld_typ(i).UOM_CODE
,lt_per_po_req_vld_typ(i).DESTINATION_ORG_ID
,lt_per_po_req_vld_typ(i).location_id
,lt_per_po_req_vld_typ(i).AGENT_ID
,lt_per_po_req_vld_typ(i).NEED_BY_DATE
,gd_current_date
,lt_per_po_req_vld_typ(i).CHARGE_ACC_ID
,lt_per_po_req_vld_typ(i).ORG_ID
);
lt_per_po_req_vld_typ(i).interface_status := gc_process_flag;
EXCEPTION
WHEN OTHERS THEN
gn_bulk_error := SQL%BULK_EXCEPTIONS.COUNT;
apps.sri_conv_util_pkg.write_log('LOAD-REP-ERR @'||ln_progress||' No Of Error Records While Inserting po_headers_interface: '
||gn_bulk_error);
FOR i IN 1..gn_bulk_error
LOOP
apps.FND_FILE.PUT_LINE(FND_FILE.log, 'Error# '||i||' at iterration# '|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
apps.FND_FILE.PUT_LINE(FND_FILE.log, 'Error Message is '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
BEGIN
--FORALL i IN lt_per_poheader_vld_typ.FIRST .. lt_per_poheader_vld_typ.COUNT SAVE EXCEPTIONS
UPDATE sripo_bpapo_headers_stg
SET interface_status = lt_per_po_req_vld_typ(i).interface_status
, error_message = lt_per_po_req_vld_typ(i).error_message
, error_code = lt_per_po_req_vld_typ(i).error_code
, request_id = gn_request_id
, last_update_date = gd_current_date
, last_updated_by = gn_user_id
, program_id = gn_program_id
, program_application_id = gn_prog_appl_id
, program_update_date = gd_current_date
WHERE record_id = lt_per_po_req_vld_typ(i).record_id;
EXCEPTION
WHEN OTHERS THEN
gn_bulk_error := SQL%BULK_EXCEPTIONS.COUNT;
APPS.FND_FILE.PUT_LINE(FND_FILE.log,'VALIDATE-ERR @'||ln_progress||' No Of Error Records While Updating sripo_bpapo_headers_stg: '); --||gn_bulk_error);
FOR i IN 1..gn_bulk_error
LOOP
APPS.FND_FILE.PUT_LINE(FND_FILE.log,'Error# '||i||' at iterration# '|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
APPS.FND_FILE.PUT_LINE(FND_FILE.log,'Error Message is '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
END LOOP;
--END;
END IF;
EXIT WHEN lcu_po_req_data%NOTFOUND;
END LOOP;
CLOSE lcu_po_req_data;
END;
END SRI_PO_REQ_CONV_PAK;
/
Show errors;
/
0 comments:
Post a Comment