Attaching Tax PDFs to respective Parties using DB Object
Below steps include bulk attachment of files from a location to the respective parties,Step 1 - Create a staging table with file details,
DROP TABLE Stag_w9_party_det CASCADE CONSTRAINTS;CREATE TABLE Stag_w9_party_det
(
PDF_NAME VARCHAR2(200 BYTE),
TAX_PAYER_NAME VARCHAR2(200 BYTE),
TAX_PAYER_ID VARCHAR2(20 BYTE),
HAND_WRTN_NO VARCHAR2(15 BYTE),
STATUS VARCHAR2(15 BYTE),
STATUS_MSG VARCHAR2(400 BYTE),
PARTY_NAME VARCHAR2(360 BYTE) NOT NULL,
PARTY_NUMBER VARCHAR2(30 BYTE) NOT NULL
);
Step 2 - Load excel data to above staging table using SQL*Loader,
OPTIONS (SKIP=1, BINDSIZE=50000, DIRECT=FALSE, ERRORS=99999, ROWS=100000, READSIZE=65536)
load data
TRUNCATE
into table Stag_w9_party_det
fields terminated by ',' optionally enclosed by '"'
trailing nullcols (
PDF_NAME "trim(:PDF_NAME)",
TAX_PAYER_NAME "trim(:TAX_PAYER_NAME)",
TAX_PAYER_ID "trim(:TAX_PAYER_ID)",
HAND_WRTN_NO "regexp_replace( trim(:HAND_WRTN_NO), '[[:space:]]+', NULL)",
PARTY_NAME "trim(:PARTY_NAME)",
PARTY_NUMBER "regexp_replace( trim(:PARTY_NUMBER), '[[:space:]]+', NULL)"
)
Step 3 - Create a concurrent program upon the below procedure, and capture all the invalid parties and process all valid parties by attaching the Tax PDF Files to the respective parties
CREATE OR REPLACE PROCEDURE apps.w9fileattach (p_errbuff OUT VARCHAR2,
p_retcode OUT NUMBER,
v_user_name IN VARCHAR2,
v_resp_name IN VARCHAR2,
v_category_name IN VARCHAR2,
v_entity_name IN VARCHAR2
)
AS
/*###################################################################
File Name : apps.w9FileAttach
Description : Attaches the Tax pdf file to the corresponding Parties
/*################################################################### */
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_document_tl_id NUMBER;
l_media_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE;
l_description fnd_documents_tl.description%TYPE;
l_title fnd_documents_tl.title%TYPE;
l_filename fnd_documents_tl.file_name%TYPE;
l_seq_num NUMBER := NULL;
l_appl_id NUMBER;
l_resp_id NUMBER;
l_fnd_user_id NUMBER;
l_instance VARCHAR2 (20);
v_conc_req_id NUMBER;
l_dir_name VARCHAR2 (20);
l_short_datatype_id NUMBER;
x_blob BLOB;
fils BFILE;
blob_length INTEGER;
l_entity_name VARCHAR2 (100);
l_format VARCHAR2 (100);
l_concurrent_program_id VARCHAR2 (100);
l_application_id VARCHAR2 (100);
l_last_update_date VARCHAR2 (100);
l_file_content_type VARCHAR2 (100);
v_pty_id VARCHAR2 (100);
v_pty_no VARCHAR2 (100);
v_pty_nme VARCHAR2 (100);
v_tax_code VARCHAR2 (100);
v_pk2_val VARCHAR2 (20) DEFAULT NULL;
v_pk3_val VARCHAR2 (20) DEFAULT NULL;
v_pk4_val VARCHAR2 (20) DEFAULT NULL;
v_pk5_val VARCHAR2 (20) DEFAULT NULL;
v_doc_id NUMBER;
v_att_doc_id NUMBER;
l_pdf_name VARCHAR2 (100);
file_exists BOOLEAN := FALSE;
v_file_name_cnt NUMBER := NULL;
v_dir_cnt NUMBER := NULL;
v_pty_val NUMBER;
v_flag VARCHAR2 (1);
v_status VARCHAR2 (50) := NULL;
v_status_msg VARCHAR2 (400) := NULL;
CURSOR c1
IS
SELECT *
FROM Stag_w9_party_det
WHERE status IS NULL
ORDER BY pdf_name DESC;
BEGIN
BEGIN
SELECT instance_name
INTO l_instance
FROM v$instance;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (fnd_file.LOG,
'Error in Instance select statment - '
|| SQLERRM
);
END;
IF l_instance IN ('DEV', 'TEST', 'PREPROD')
THEN
SELECT COUNT (1)
INTO v_dir_cnt
FROM all_directories
WHERE directory_name = 'FILES_ATTACH_DIR'
AND directory_path =
'/interface_j/' || l_instance || '/incoming/W9_PARTIES';
ELSE
SELECT COUNT (1)
INTO v_dir_cnt
FROM all_directories
WHERE directory_name = 'FILES_ATTACH_DIR'
AND directory_path =
'/interface_i/' || l_instance || '/incoming/W9_PARTIES';
END IF;
IF v_dir_cnt = 0
THEN
fnd_file.put_line (fnd_file.LOG,
'==========Directory Creation....==========='
);
-- Directory Creation
IF l_instance IN ('DEV', 'TEST', 'PREPROD')
THEN
EXECUTE IMMEDIATE 'create or replace directory FILES_ATTACH_DIR AS
''/interface_j/'
|| l_instance
|| '/incoming/W9_PARTIES''';
ELSE
EXECUTE IMMEDIATE 'create or replace directory FILES_ATTACH_DIR AS
''/interface_i/'
|| l_instance
|| '/incoming/W9_PARTIES''';
END IF;
ELSE
fnd_file.put_line (fnd_file.LOG, 'Directory already created');
END IF;
fnd_file.put_line (fnd_file.LOG,
'==========User/Responsibility Details....==========='
);
-- User Details
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE UPPER (user_name) = UPPER (v_user_name);
SELECT application_id, responsibility_id
INTO l_appl_id, l_resp_id
FROM apps.fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (v_resp_name);
-- Category_id
SELECT category_id
INTO l_category_id
FROM fnd_document_categories_vl
WHERE user_name LIKE v_category_name;
fnd_file.put_line (fnd_file.LOG,
'Processing with Category Name - ' || v_category_name
);
fnd_file.put_line (fnd_file.output,
'Processing with Category Name - ' || v_category_name
);
-- Fetching and processing staging table data
FOR i IN c1
LOOP
fnd_file.put_line
(fnd_file.output,
'########################################################'
);
fnd_file.put_line (fnd_file.LOG,
'Processing party from staging table - '
|| i.party_name
);
v_flag := 'Y';
v_pty_val := NULL;
-- Validation Parties
IF v_flag = 'Y'
THEN
BEGIN
SELECT COUNT (1)
INTO v_pty_val
FROM hz_parties
WHERE UPPER (party_name) LIKE UPPER (i.party_name);
EXCEPTION
WHEN OTHERS
THEN
v_flag := 'N';
v_status := 'INVALID';
v_status_msg := 'Invalid Party Name';
END;
END IF;
v_pty_val := NULL;
IF v_flag = 'Y'
THEN
-- Validation
BEGIN
SELECT COUNT (1)
INTO v_pty_val
FROM hz_parties
WHERE ( UPPER (party_name) LIKE UPPER (i.party_name)
AND jgzz_fiscal_code IN (i.tax_payer_id)
);
EXCEPTION
WHEN OTHERS
THEN
v_flag := 'N';
v_status := 'INVALID';
v_status_msg := 'Invalid Party Name';
END;
END IF;
v_pty_val := NULL;
IF v_flag = 'Y'
THEN
-- Validation
BEGIN
SELECT COUNT (1)
INTO v_pty_val
FROM hz_parties
WHERE ( UPPER (party_name) LIKE UPPER (i.party_name)
AND jgzz_fiscal_code IN (i.tax_payer_id)
)
AND TRIM (REGEXP_REPLACE (party_number, '[[:space:]]+', NULL)) IN
--(i.party_number)
TRIM
(REGEXP_REPLACE
(i.party_number,
'[[:space:]]+',
NULL
)
);
EXCEPTION
WHEN OTHERS
THEN
v_flag := 'N';
v_status := 'INVALID';
v_status_msg := 'Invalid Party Name';
END;
END IF;
IF v_pty_val IS NULL
THEN
UPDATE Stag_w9_party_det
SET status = v_status,
status_msg = v_status_msg
WHERE party_name = i.party_name
AND party_number = i.party_number
AND tax_payer_id = i.tax_payer_id
AND pdf_name = i.pdf_name;
fnd_file.put_line
(fnd_file.LOG,
'Error through validation of parties for party - '
|| i.party_name
);
COMMIT;
ELSE
-- Fetching and processing Party Information from DB
FOR rec_mpty IN
--c2(i.party_name,i.tax_payer_id, i.party_number)
(SELECT party_id, party_number, party_name, jgzz_fiscal_code
FROM hz_parties
WHERE ( UPPER (party_name) LIKE UPPER (i.party_name)
AND jgzz_fiscal_code IN (i.tax_payer_id)
)
AND TRIM (REGEXP_REPLACE (party_number, '[[:space:]]+', NULL)) IN
--(i.party_number)
TRIM
(REGEXP_REPLACE
(i.party_number,
'[[:space:]]+',
NULL
)
))
LOOP
--v_cnt := 1;
v_pty_id := rec_mpty.party_id;
v_pty_no := rec_mpty.party_number;
v_pty_nme := rec_mpty.party_name;
v_tax_code := rec_mpty.jgzz_fiscal_code;
fnd_file.put_line (fnd_file.LOG,
'DB Processing Party Name - '
|| v_pty_nme
|| ' and Party Id - '
|| v_pty_id
);
fnd_file.put_line (fnd_file.output,
'DB Processing Party Name - '
|| v_pty_nme
|| ' and Party Id - '
|| v_pty_id
);
SELECT SUBSTR (i.pdf_name, 1, LENGTH (i.pdf_name) - 4)
--INSTR (i.pdf_name, '.', 1) - 1)
INTO l_pdf_name
FROM DUAL;
-- Fetching and processing PDF files as per table data
FOR k IN (SELECT l_pdf_name || '.pdf' l_pdf_name
FROM DUAL
UNION
SELECT l_pdf_name || ' (2).pdf' l_pdf_name
FROM DUAL
ORDER BY 1 DESC)
LOOP
l_filename := k.l_pdf_name;
fnd_file.put_line (fnd_file.output,
'Processing PDF Name - ' || l_filename
);
v_doc_id := NULL;
v_att_doc_id := NULL;
-- Validation to verify file name attachment
BEGIN
SELECT COUNT (DISTINCT file_name)
INTO v_file_name_cnt
FROM fnd_documents
WHERE document_id IN (
SELECT document_id
FROM fnd_attached_documents
WHERE pk1_value = v_pty_id
AND entity_name = v_entity_name
AND category_id = l_category_id)
AND category_id = l_category_id
AND file_name = l_filename;
EXCEPTION
WHEN OTHERS
THEN
v_file_name_cnt := 0;
END;
IF v_file_name_cnt >= 1
THEN
fnd_file.put_line (fnd_file.LOG,
'There exists "'
|| l_filename
|| '" already attached to the party - '
|| v_pty_nme
);
ELSE
fnd_file.put_line
(fnd_file.LOG,
'Attaching PDF to the Party with Name - '
|| l_filename
);
fnd_file.put_line (fnd_file.LOG,
'Attachment Process Started '
);
-- Creating Document Ids
SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;
SELECT fnd_documents_s.CURRVAL
INTO l_document_tl_id
FROM DUAL;
SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;
-- Seq Num
SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = v_pty_id AND entity_name = v_entity_name;
-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE NAME = 'FILE';
SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;
SELECT SUBSTR (i.pdf_name, LENGTH (i.pdf_name) - 2)
INTO l_format
FROM DUAL;
-- Condition to get file_content_type
IF UPPER (l_format) = 'PDF'
THEN
l_file_content_type := 'application/pdf';
ELSIF UPPER (l_format) = 'XLS'
THEN
l_file_content_type := 'application/vnd.ms-excel';
ELSE
l_file_content_type := 'application/pdf';
END IF;
fnd_file.put_line (fnd_file.LOG,
'File Content Type - '
|| l_file_content_type
);
-- Fetching the concurrent program details
SELECT MAX (request_id), concurrent_program_id
INTO v_conc_req_id, l_concurrent_program_id
FROM fnd_concurrent_requests
WHERE concurrent_program_id =
(SELECT concurrent_program_id
FROM fnd_concurrent_programs_vl
WHERE user_concurrent_program_name LIKE
'W9 Party Pdf Attachment Program')
GROUP BY concurrent_program_id;
SELECT SUBSTR (user_concurrent_program_name, 1, 75),
application_id, last_update_date
INTO l_description,
l_application_id, l_last_update_date
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_id = l_concurrent_program_id;
l_description := 'W9 - ' || v_pty_nme;
l_title := 'W9 - ' || v_pty_nme;
fnd_file.put_line (fnd_file.LOG,
'Description - ' || l_description
);
fnd_file.put_line (fnd_file.LOG, 'Title - ' || l_title);
l_dir_name := 'FILES_ATTACH_DIR'; -- Directory from which files to be read and attached
fnd_file.put_line (fnd_file.LOG,
'==========File...==========='
);
-- Reading and verifying the file across directory
-- fils := BFILENAME (l_dir_name, l_filename);
fils := BFILENAME (l_dir_name, l_filename);
file_exists := DBMS_LOB.fileexists (fils) = 1;
IF file_exists
THEN
-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);
fnd_file.put_line
(fnd_file.LOG,
'=========File read completed....==========='
);
-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.
INSERT INTO fnd_lobs
(file_id, file_name,
file_content_type, upload_date,
expiration_date, program_name, program_tag,
file_data, LANGUAGE, oracle_charset,
file_format
)
VALUES (l_media_id, l_filename,
l_file_content_type, SYSDATE,
NULL, 'FNDATTCH', NULL,
EMPTY_BLOB (), 'US', 'UTF8',
'binary'
)
RETURNING file_data
INTO x_blob;
fnd_file.put_line
(fnd_file.LOG,
'==========Record inserted in fnd_lob table....==========='
);
-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);
--fnd_file.put_line(fnd_file.LOG,'FND_LOBS Created with media_id - ' || l_media_id);
COMMIT;
-- This package allows user to share file across multiple orgs or restrict to single org
BEGIN
fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE
('LOGIN_ID'),
x_datatype_id => l_short_datatype_id,
x_security_id => NULL,
x_publish_flag => 'Y',
--This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 4,
x_usage_type => 'O',
x_language => 'US',
x_request_id => v_conc_req_id,
x_program_application_id => l_application_id,
x_program_id => l_concurrent_program_id,
x_program_update_date => l_last_update_date,
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id,
x_title => l_title
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while inserting in fnd_documents table and fnd_documents_tl table - '
|| SQLERRM
);
END;
fnd_file.put_line
(fnd_file.LOG,
'==========Record inserted in fnd_documents table and fnd_documents_tl table....==========='
);
BEGIN
fnd_attached_documents_pkg.insert_row
(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE
('LOGIN_ID'),
x_seq_num => l_seq_num,
x_entity_name => v_entity_name,
x_column1 => NULL,
x_pk1_value => v_pty_id,
x_pk2_value => v_pk2_val,
x_pk3_value => v_pk3_val,
x_pk4_value => v_pk4_val,
x_pk5_value => v_pk5_val,
x_automatically_added_flag => 'Y',
x_datatype_id => l_short_datatype_id,
x_category_id => l_category_id,
x_security_type => 4,
x_security_id => NULL,
x_publish_flag => 'Y',
x_language => 'US',
x_request_id => v_conc_req_id,
x_program_application_id => l_application_id,
x_program_id => l_concurrent_program_id,
x_program_update_date => l_last_update_date,
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id,
x_title => l_title
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while inserting in fnd_attached_documents table, fnd_documents table and fnd_documents_tl table - '
|| SQLERRM
);
END;
fnd_file.put_line
(fnd_file.LOG,
'==========Record inserted in fnd_attached_documents table, fnd_documents table and fnd_documents_tl table....==========='
);
-- Updating status as PDF attachment is completed
UPDATE Stag_w9_party_det
SET status = 'COMPLETED',
status_msg = 'PDFs Attached successfully'
WHERE party_name = i.party_name
AND party_number = i.party_number
AND tax_payer_id = i.tax_payer_id
AND pdf_name = l_filename;
fnd_file.put_line
(fnd_file.output,
'PDF Successfully attached to party - '
|| i.party_name
);
COMMIT;
ELSE
UPDATE Stag_w9_party_det
SET status = 'ERROR',
status_msg = 'PDF doesn''t exist in directory'
WHERE party_name = i.party_name
AND party_number = i.party_number
AND tax_payer_id = i.tax_payer_id
AND pdf_name = l_filename;
fnd_file.put_line (fnd_file.LOG,
l_filename
|| ' does not exist in directory'
);
COMMIT;
END IF;
END IF;
END LOOP;
END LOOP;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while processing the attachment process - Main'
|| SQLERRM
);
END;
/
No comments:
Post a Comment