BEGIN
CREATE TABLE XONT_QA_RESULTS AS SELECT * FROM QA_RESULTS;
CURSOR get_details IS
SELECT ROWID
,column1
,column2
,column3
,column4
,column5
,column6
,column7
,column8
,column9
,column10
,column11
,column12
FROM XONT_QA_RESULTS
WHERE NVL(process_flag, 'N') = 'N'
ORDER BY page_seq
,main_seq
,item_in_grp_Seq;
l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
l_plan_id QA_PLANS_V.plan_id%TYPE;
ln_request_id NUMBER;
l_collection_id NUMBER;
l_return BOOLEAN;
l_phase VARCHAR2(100);
l_status VARCHAR2(100);
l_return_status VARCHAR2(100);
l_dev_phase VARCHAR2(100);
l_dev_status VARCHAR2(100);
l_message VARCHAR2(100);
BEGIN
SELECT QA_COLLECTION_ID_S.NEXTVAL
INTO l_collection_id
FROM DUAL;
SELECT plan_id
INTO l_plan_id
FROM QA_PLANS_V
WHERE name = 'PLAN NAME1';
BEGIN
UPDATE XONT_QA_RESULTS
SET process_flag = 'D' -- Unprocessed Status where Attachment has not been uploaded
WHERE process_flag = 'P';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No existing "Processed" Data to be updated as D')
END;
FOR get_dtls IN get_details
LOOP
INSERT INTO qa_results_interface
transaction_interface_id
,organization_code
,plan_name
,process_status
,insert_type
,plan_id
,collection_id
,last_update_date
,last_updated_by
,qa_last_updated_by
,transaction_date
,character1
,character2
,character3
,character4
,character5
,character6
,character7
,character8
,character9
,character10
,character11
,character12
)
VALUES (
QA_RESULTS_UPDATE_HISTORY_S.nextval,
'OR1'
,'PLAN NAME1'
,1 -- process_status
,1 -- For Update
,l_plan_id--
,l_collection_id
,SYSDATE
,l_user_id
,l_user_id
,SYSDATE
, get_dtls.column1
, get_dtls.column2
, get_dtls.column3
, get_dtls.column4
,get_dtls.column5
,get_dtls.column6
,get_dtls.column7
,get_dtls.column8
,get_dtls.column9
,get_dtls.column10
,get_dtls.column11
,get_dtls.column12
);
UPDATE Xont_qa_results
SET process_flag = 'P'
WHERE column12 = get_dtls.COLUMN12
AND process_Flag = 'N';
END LOOP;
COMMIT;
ln_request_id :=
fnd_request.submit_request ('QA', -- application
'QLTTRAMB', -- program short name
'', -- description
'', -- start time
FALSE, -- sub request
'200',
'1',
l_user_id,
2, CHR (0));
COMMIT;
IF ln_request_id = 0
THEN
fnd_file.put_line(fnd_file.log,'failed in concurrent request submission');
ELSE
fnd_file.put_line(fnd_file.log, 'Submitted request for Import.');
END IF;
fnd_file.put_line(fnd_file.log, 'Completed process');
END;