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_prevalidate_flag IN VARCHAR2 RPV - Re Pre-Validate | --
-- | Yes - Pre Validate | --
-- | 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;
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 for Headers
--======================================================
CURSOR lcu_rec_count(p_interface_status VARCHAR2) IS
SELECT count(*)
FROM SRI_po_requisitions_data
WHERE interface_status =(p_interface_status);
CURSOR lcu_openpo_req_trnsf(p_interface_status VARCHAR2)
IS
SELECT RECORD_ID
,REQ_NUMBER
,AGENT_NAME
,ITEM_NAME
,UOM_CODE
,QUANTITY
,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
,DESTINATION_ORGANIZATION
,DESTINATION_ORG_ID
,LOCATION_NAME
,location_id
,ORGANIZATION_NAME
,ORG_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
,NULL
,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
,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;
END LOOP;
END IF;
EXIT WHEN lcu_openpo_req_trnsf%NOTFOUND;
END LOOP;
CLOSE lcu_openpo_req_trnsf;
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;
END SRI_PO_REQ_CONV_PAK;
/
Show errors;
/
0 comments:
Post a Comment