Creating journal import table to use instead of GL_INTERFACE table. This gave me the option of running Journal Import on multiple tables without the fear of table lock. I have given the code below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
| DECLAREbulk_errors EXCEPTION;PRAGMA EXCEPTION_INIT (bulk_errors, -17594);TYPE t_int_record_type IS TABLE OF gl_interface%ROWTYPE INDEX BY BINARY_INTEGER;P_INT_RECORDS t_int_record_type;v_sob_id NUMBER;v_run_id NUMBER;v_group_id NUMBER;v_request_id NUMBER;v_request_status BOOLEAN;v_phase VARCHAR2 (100);v_status VARCHAR2 (100);v_dev_phase VARCHAR2 (10);v_dev_status VARCHAR2 (100);v_message VARCHAR2 (100);BEGIN -- Create the custom gl_interface table gl_journal_import_pkg.create_table (table_name => 'GL_CUSTOM_INTERFACE', TABLESPACE => NULL, physical_attributes => NULL, create_n1_index => TRUE, n1_tablespace => NULL, n1_physical_attributes => NULL, create_n2_index => TRUE, n2_tablespace => NULL, n2_physical_attributes => NULL ); -- Process all the records to be inserted into General Ledger -- Populate the records into the collection P_INT_RECORDS -- Now insert the collection into the custom gl_interface table FORALL i IN 1..<span class="skimlinks-unlinked">P_INT_RECORDS.COUNT</span> SAVE EXCEPTIONS INSERT INTO GL_CUSTOM_INTERFACE VALUES p_int_records(i); error_count := SQL%<span class="skimlinks-unlinked">BULK_EXCEPTIONS.COUNT</span>; FND_FILE.PUT_LINE(<span class="skimlinks-unlinked">FND_FILE.LOG</span>, ' The Count of No. Rows Inserted in GL_CUSTOM_INTERFACE is = ' || TO_CHAR(SQL%ROWCOUNT) ); error_count := SQL%<span class="skimlinks-unlinked">BULK_EXCEPTIONS.COUNT</span>; TRACE('LOG', ' The Count of No. Rows Inserted in GL_CUSTOM_INTERFACE for is = ' || TO_CHAR(SQL%ROWCOUNT) ); -- Populate the GL_INTERFACE_CONTROL table gl_journal_import_pkg.populate_interface_control (user_je_source_name => 'NEW_SOURCE', -- As per the source of the journal lines GROUP_ID => v_group_id, -- As per the group id of the journal lines set_of_books_id => v_sob_id, -- As per the SOB id of the journal lines interface_run_id => v_run_id, -- The value will be returned from the API table_name => 'GL_CUSTOM_INTERFACE', -- We can pass a parameter for table name processed_data_action => NULL ); -- processed_data_action permissible values are given below -- gl_journal_import_pkg.save_data - Keeps the data in the table after journal import has finished successfully -- gl_journal_import_pkg.delete_data - Deletes the data after journal import has finished successfully -- gl_journal_import_pkg.drop_table - Drops the custom interface table after journal import successfully -- NULL - Same as gl_journal_import_pkg.delete_dataCOMMIT;-- Execute Journal Import-- We are passing the run_id to ensure that the lines-- are picked up from GL_CUSTOM_INTERFACE table.-- Journal Import looks up the GL_INTERFACE_CONTROL-- table with the run id to identify the journal lines. v_request_id := fnd_request.submit_request (application => 'SQLGL', -- application short name program => 'GLLEZL', -- program short name description => NULL, -- program name start_time => NULL, -- start date sub_request => FALSE, -- sub-request argument1 => v_run_id, -- interface run id argument2 => v_sob_id, -- set of books id argument3 => 'Y', -- error to suspense flag argument4 => NULL, -- from accounting date argument5 => NULL, -- to accounting date argument6 => 'N', -- create summary flag argument7 => 'O' -- import desc flex flag ); COMMIT; -- If Journal Import is triggered off then a valid request id -- will be returned else the request id returned will be 0 if v_request_id > 0 THEN -- Wait for Journal Import to complete v_request_status := fnd_concurrent.wait_for_request (request_id => v_request_id, interval => 30, max_wait => 0, phase => v_phase, status => v_status, dev_phase => v_dev_phase, dev_status => v_dev_status, message => v_message ); -- Check for the status of the Journal Import request after completion -- If the program completed successfully then the status will be 0 -- in fnd_concurrent_requests table SELECT DECODE (status_code, 'C', 0, 1) INTO v_status FROM fnd_concurrent_requests WHERE request_id = v_request_id; -- Drop the custom interface table after Journal Import has been successful gl_journal_import_pkg.drop_table(table_name => 'GL_CUSTOM_INTERFACE'); else dbms_output.put_line('GL Import did not run'); end if;END;END; |
In this example I have illustrated 1 custom interface table. For my conversion project I had used 8 custom interface tables to execute journal import. In addition PL/SQL tables for data processing and cleaning had helped further reduce the time to less than 2 hours from 10 hours initially without multiple tables and collections.
0 comments