[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Procedure Name : UPDATE_CONTACT_PREF |
| |
| Purpose : Updates jtf_fm_int_request_lines table for "DO NOT" contact |
| party IDs. |
*---------------------------------------------------------------------------------*/
PROCEDURE update_contact_pref ( p_request_id IN NUMBER )
IS
TYPE l_line_id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT c.request_line_id
FROM hz_contact_preferences b ,
jtf_fm_int_request_lines c
WHERE b.preference_code = 'DO_NOT'
AND b.contact_level_table = 'HZ_PARTIES'
AND b.contact_level_table_id = c.party_id
AND c.request_id = p_request_id
AND NVL(b.preference_start_date, SYSDATE -1) < SYSDATE
AND NVL(b.preference_end_date, SYSDATE+1) > SYSDATE ;
UPDATE jtf_fm_int_request_lines
SET contact_preference_flag = 'N',
enabled_flag = 'N'
WHERE request_line_id = l_line_id(i);
END update_contact_pref;
| Purpose : Updates jtf_fm_int_request_lines table for invalid email |
| addresses. |
*---------------------------------------------------------------------------------*/
PROCEDURE validate_email( p_request_id IN NUMBER )
IS
TYPE l_line_id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT c.request_line_id
FROM jtf_fm_int_request_lines c
WHERE c.request_id = p_request_id
AND (c.email_address IS NULL
OR INSTR(c.email_address,'@') < 1
OR INSTR(c.email_address, ':') > 0)
AND c.enabled_flag = 'Y';
UPDATE jtf_fm_int_request_lines
SET enabled_flag = 'N'
WHERE request_line_id = l_line_id(i);
| - Inserts record into jtf_fm_int_request_header table. |
| - Inserts record into jtf_fm_int_request_batches table. |
| - Creates partition and inserts records into |
| jtff_fm_int_request_lines table. |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE process_request( request_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Process_request';
l_col_insert VARCHAR2(3000);
l_select_cols VARCHAR2(3000);
l_insert_statement VARCHAR2(32767);
SELECT request, hist_req_id
INTO l_bind, l_request_id
FROM jtf_fm_request_history_all
WHERE hist_req_id = request_id;
SELECT file_data
INTO var
FROM fnd_lobs
WHERE file_id = l_query_id;
SELECT COUNT(ROWID)
INTO l_header_count
FROM jtf_fm_int_request_header
WHERE request_id = l_request_id;
INSERT INTO jtf_fm_int_request_header
( request_id
, group_id
, server_id
, submit_dt_tm
, processed_dt_tm
, priority
, source_code_id
, source_code
, object_id
, object_type
, outcome_desc
, user_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, object_version_number
, request_status
, template_id
, no_of_parameters
, email_format
, email_from_address
, email_reply_to_address
, sender_display_name
, user_history
, subject
, parameter1
, parameter2
, parameter3
, parameter4
, parameter5
, parameter6
, parameter7
, parameter8
, parameter9
, parameter10
, parameter11
, parameter12
, parameter13
, parameter14
, parameter15
, parameter16
, parameter17
, parameter18
, parameter19
, parameter20
, parameter21
, parameter22
, parameter23
, parameter24
, parameter25
, parameter26
, parameter27
, parameter28
, parameter29
, parameter30
, parameter31
, parameter32
, parameter33
, parameter34
, parameter35
, parameter36
, parameter37
, parameter38
, parameter39
, parameter40
, parameter41
, parameter42
, parameter43
, parameter44
, parameter45
, parameter46
, parameter47
, parameter48
, parameter49
, parameter50
, parameter51
, parameter52
, parameter53
, parameter54
, parameter55
, parameter56
, parameter57
, parameter58
, parameter59
, parameter60
, parameter61
, parameter62
, parameter63
, parameter64
, parameter65
, parameter66
, parameter67
, parameter68
, parameter69
, parameter70
, parameter71
, parameter72
, parameter73
, parameter74
, parameter75
, parameter76
, parameter77
, parameter78
, parameter79
, parameter80
, parameter81
, parameter82
, parameter83
, parameter84
, parameter85
, parameter86
, parameter87
, parameter88
, parameter89
, parameter90
, parameter91
, parameter92
, parameter93
, parameter94
, parameter95
, parameter96
, parameter97
, parameter98
, parameter99
, parameter100
)
SELECT
l_request_id
, group_id
, server_id
, submit_dt_tm
, processed_dt_tm
, priority
, source_code_id
, source_code
, object_id
, object_type
, NULL
, user_id
, SYSDATE
, last_updated_by
, SYSDATE
, created_by
, last_update_login
, 1
, 'NEW'
, template_id
, l_col_cnt
, l_email_body
, l_email_from_address
, l_email_reply_to_address
, l_sender_display_name
, l_user_history
, l_subject
, l_rec_tab(1).col_name
, l_rec_tab(2).col_name
, l_rec_tab(3).col_name
, l_rec_tab(4).col_name
, l_rec_tab(5).col_name
, l_rec_tab(6).col_name
, l_rec_tab(7).col_name
, l_rec_tab(8).col_name
, l_rec_tab(9).col_name
, l_rec_tab(10).col_name
, l_rec_tab(11).col_name
, l_rec_tab(12).col_name
, l_rec_tab(13).col_name
, l_rec_tab(14).col_name
, l_rec_tab(15).col_name
, l_rec_tab(16).col_name
, l_rec_tab(17).col_name
, l_rec_tab(18).col_name
, l_rec_tab(19).col_name
, l_rec_tab(20).col_name
, l_rec_tab(21).col_name
, l_rec_tab(22).col_name
, l_rec_tab(23).col_name
, l_rec_tab(24).col_name
, l_rec_tab(25).col_name
, l_rec_tab(26).col_name
, l_rec_tab(27).col_name
, l_rec_tab(28).col_name
, l_rec_tab(29).col_name
, l_rec_tab(30).col_name
, l_rec_tab(31).col_name
, l_rec_tab(32).col_name
, l_rec_tab(33).col_name
, l_rec_tab(34).col_name
, l_rec_tab(35).col_name
, l_rec_tab(36).col_name
, l_rec_tab(37).col_name
, l_rec_tab(38).col_name
, l_rec_tab(39).col_name
, l_rec_tab(40).col_name
, l_rec_tab(41).col_name
, l_rec_tab(42).col_name
, l_rec_tab(43).col_name
, l_rec_tab(44).col_name
, l_rec_tab(45).col_name
, l_rec_tab(46).col_name
, l_rec_tab(47).col_name
, l_rec_tab(48).col_name
, l_rec_tab(49).col_name
, l_rec_tab(50).col_name
, l_rec_tab(51).col_name
, l_rec_tab(52).col_name
, l_rec_tab(53).col_name
, l_rec_tab(54).col_name
, l_rec_tab(55).col_name
, l_rec_tab(56).col_name
, l_rec_tab(57).col_name
, l_rec_tab(58).col_name
, l_rec_tab(59).col_name
, l_rec_tab(60).col_name
, l_rec_tab(61).col_name
, l_rec_tab(62).col_name
, l_rec_tab(63).col_name
, l_rec_tab(64).col_name
, l_rec_tab(65).col_name
, l_rec_tab(66).col_name
, l_rec_tab(67).col_name
, l_rec_tab(68).col_name
, l_rec_tab(69).col_name
, l_rec_tab(70).col_name
, l_rec_tab(71).col_name
, l_rec_tab(72).col_name
, l_rec_tab(73).col_name
, l_rec_tab(74).col_name
, l_rec_tab(75).col_name
, l_rec_tab(76).col_name
, l_rec_tab(77).col_name
, l_rec_tab(78).col_name
, l_rec_tab(79).col_name
, l_rec_tab(80).col_name
, l_rec_tab(81).col_name
, l_rec_tab(82).col_name
, l_rec_tab(83).col_name
, l_rec_tab(84).col_name
, l_rec_tab(85).col_name
, l_rec_tab(86).col_name
, l_rec_tab(87).col_name
, l_rec_tab(88).col_name
, l_rec_tab(89).col_name
, l_rec_tab(90).col_name
, l_rec_tab(91).col_name
, l_rec_tab(92).col_name
, l_rec_tab(93).col_name
, l_rec_tab(94).col_name
, l_rec_tab(95).col_name
, l_rec_tab(96).col_name
, l_rec_tab(97).col_name
, l_rec_tab(98).col_name
, l_rec_tab(99).col_name
, l_rec_tab(100).col_name
FROM jtf_fm_request_history_all
WHERE hist_req_id = l_request_id;
l_col_insert := '';
l_col_insert := l_col_insert ||',col'||i ;
l_insert_statement := 'INSERT INTO jtf_fm_int_request_lines ( request_id, request_line_id, partition_id, batch_no, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, object_version_number, email_status, enabled_flag,
contact_preference_flag ';
l_select_cols :='SELECT ' || l_request_id ||', JTF_FM_INT_REQUEST_LINES_s.NEXTVAL, '|| l_partition_id ||',
CEIL(ROWNUM/ ' || l_batch_size || ' ),
SYSDATE,
1,
SYSDATE,
1,
1,
1,
'||''''||'AVAILABLE'||''''||','||
''''||'Y'||''''||','
||''''||'Y'||''''||',';
l_insert_statement := l_insert_statement || ', party_id';
l_select_cols := l_select_cols || 'UserQuery.customer_id, ';
l_insert_statement := l_insert_statement || ', party_name';
l_select_cols := l_select_cols || 'UserQuery.first_name,';
l_insert_statement := l_insert_statement || ', party_name';
l_select_cols := l_select_cols || 'UserQuery.last_name,';
l_insert_statement := l_insert_statement || ', party_name';
l_select_cols := l_select_cols || 'UserQuery.first_name || '' ''||UserQuery.last_name, ';
l_insert_statement := l_insert_statement;
l_select_cols := l_select_cols;
l_insert_statement := l_insert_statement || ', email_address';
l_select_cols := l_select_cols || 'UserQuery.emailaddress, ';
l_insert_statement := l_insert_statement || l_col_insert;
l_insert_statement := l_insert_statement || ') ' || l_select_cols || ' UserQuery.* from ('||l_fnd_query ||') UserQuery';
l_insert_statement := 'BEGIN ' || l_insert_statement || '; END;';
l_length := LENGTH(l_insert_statement);
EXECUTE IMMEDIATE l_insert_statement;
EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1);
EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2);
EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2),l_bind_value(3);
EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2),l_bind_value(3),l_bind_value(4);
EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2),l_bind_value(3),l_bind_value(4),l_bind_value(5);
INSERT INTO jtf_fm_int_request_batches
( request_id,
batch_id,
request_line_id_start,
request_line_id_end,
server_instance_id,
batch_status ,
last_update_date
)
SELECT
l_request_id,
d.batch_no,
MIN(d.request_line_id),
MAX(d.request_line_id),
'',
'AVAILABLE',
SYSDATE
FROM jtf_fm_int_request_lines d
WHERE d.request_id = l_request_id
GROUP BY d.batch_no;
update_contact_pref(l_request_id );
UPDATE jtf_fm_int_request_header c
SET c.request_status = 'AVAILABLE'
WHERE c.request_id = l_request_id ;
UPDATE jtf_fm_status_all c
SET c.request_status = 'IN_PROCESS'
WHERE c.request_id = l_request_id;
UPDATE jtf_fm_request_history_all c
SET c.outcome_code = 'IN_PROCESS'
WHERE c.hist_req_id = l_request_id;
| Procedure Name : UPDATE_LINES_STATUS_BULK |
| |
| Purpose : For a given set of lines id, this procedure updates the |
| jtf_fm_int_request_lines table and sets the email status to |
| the passed email status PL/SQL table. |
| - Updates jtf_fm_int_request_header - for request status |
| - Updates jtf_fm_status_all - for request status |
| - Updates jtf_fm_request_history_all - for outcome code |
| - Updates jtf_fm_email_stat - for email details |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE update_lines_status_bulk ( line_ids IN JTF_VARCHAR2_TABLE_100
, request_id IN NUMBER
, line_status IN JTF_VARCHAR2_TABLE_100
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_request_id NUMBER;
SELECT 'Y'
FROM jtf_fm_int_request_header c
WHERE c.request_id = l_request_id
AND c.request_status = 'READYTOLOG' ;
SELECT SUM(DECODE(c.contact_preference_flag,'N',1,0)) ,
SUM(DECODE(c.email_status,'NOTDELIVERED',1,0)) ,
SUM(DECODE(c.enabled_flag, 'N',1,0)),
COUNT(1)
FROM jtf_fm_int_request_lines c
WHERE c.request_id = l_request_id ;
UPDATE jtf_fm_int_request_lines
SET email_status = line_status(i)
WHERE request_line_id = line_ids(i);
UPDATE jtf_fm_int_request_header c
SET c.request_status = 'READYTOLOG'
WHERE c.request_id = l_request_id
AND NOT EXISTS ( SELECT '1'
FROM jtf_fm_int_request_lines a
WHERE c.request_id = a.request_id
AND a.email_status IN ('AVAILABLE', 'NEW','ASSIGNED')
AND a.enabled_flag = 'Y' );
UPDATE jtf_fm_status_all c
SET c.request_status = 'READYTOLOG',
c.LAST_UPDATE_DATE = SYSDATE,
c.jobs_processed = l_count
WHERE c.request_id = l_request_id;
UPDATE jtf_fm_request_history_all
SET outcome_code = 'READYTOLOG',
total_jobs = l_count,
process_dt_tm = SYSDATE,
processed_dt_tm = SYSDATE,
last_update_date = SYSDATE
WHERE hist_req_id = l_request_id;
UPDATE jtf_fm_email_stats
SET total = l_count,
sent = l_count - l_not_delivered ,
malformed = l_disabled_flag - l_contact_pref_disabled ,
bounced = 0,
opened = 0,
unsubscribed = 0,
do_not_contact = l_contact_pref_disabled
WHERE request_id = l_request_id ;
| Procedure Name : UPDATE_LINES_STATUS |
| |
| Purpose : For a given set of lines id, this procedure updates the |
| jtf_fm_int_request_lines table and sets the email status to |
| the passed email status PL/SQL table. |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE update_lines_status ( line_ids IN JTF_VARCHAR2_TABLE_100
, request_id IN NUMBER
, line_status IN VARCHAR2
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
FORALL i IN LINE_IDS.FIRST .. LINE_IDS.LAST
UPDATE jtf_fm_int_request_lines
SET email_status = line_status
WHERE request_line_id = line_ids(i);
| Procedure Name : UPDATE_INSTANCE_STATUS |
| |
| Purpose : - Updates jtf_fm_int_request_batches - for batch status |
| - Updates jtf_fm_int_request_header - for request status |
| - Updates jtf_fm_status_all - for request status |
| - Updates jtf_fm_request_history_all - for outcome code |
| - Updates jtf_fm_email_stat - for email details |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE update_instance_status( p_request_id IN NUMBER
, p_server_id IN NUMBER
, p_instance_id IN NUMBER
, p_status IN VARCHAR2
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_request_id NUMBER;
SELECT 'Y'
FROM jtf_fm_int_request_header c
WHERE c.request_id = l_request_id
AND c.request_status = 'READYTOLOG' ;
SELECT SUM(DECODE(c.CONTACT_PREFERENCE_FLAG,'N',1,0)) ,
SUM(DECODE(c.EMAIL_STATUS,'NOTDELIVERED',1,0)) ,
SUM(DECODE(c.enabled_flag, 'N',1,0)),
COUNT(1)
FROM jtf_fm_int_request_lines c
WHERE c.request_id = l_request_id ;
UPDATE jtf_fm_int_request_batches
SET batch_status = p_status,
last_update_date = SYSDATE
WHERE request_id = p_request_id;
UPDATE jtf_fm_int_request_header c
SET c.request_status = 'READYTOLOG'
WHERE c.request_id = p_request_id
AND NOT EXISTS ( SELECT '1'
FROM jtf_fm_int_request_batches a
WHERE c.request_id = a.request_id
AND a.batch_status IN ('AVAILABLE', 'NEW','ASSIGNED')
);
UPDATE jtf_fm_status_all c
SET c.request_status = 'READYTOLOG',
c.LAST_UPDATE_DATE = SYSDATE,
c.jobs_processed = l_count
WHERE c.request_id = l_request_id;
UPDATE jtf_fm_request_history_all
SET outcome_code = 'READYTOLOG',
total_jobs = l_count,
process_dt_tm = SYSDATE,
processed_dt_tm = SYSDATE,
last_update_date = SYSDATE
WHERE hist_req_id = l_request_id;
UPDATE jtf_fm_email_stats
SET total = l_count,
sent = l_count - l_not_delivered ,
malformed = l_disabled_flag - l_contact_pref_disabled ,
bounced = 0,
opened = 0,
unsubscribed = 0,
do_not_contact = l_contact_pref_disabled
WHERE request_id=l_request_id ;
| Purpose : - Updates jtf_fm_int_request_batches - for batch status |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE clean_up_instance( p_request_id IN NUMBER
, p_server_id IN NUMBER
, p_instance_id IN NUMBER
, P_commit IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2) IS
BEGIN
UPDATE jtf_fm_int_request_batches
SET batch_status = 'NEW',
last_update_date = SYSDATE
WHERE request_ID = p_request_id
AND server_instance_id = p_instance_id;
| Purpose : Selects from jtf_fm_int_request_lines , and |
| - Updates jtf_fm_int_request_header - for request status |
| - Updates jtf_fm_status_all - for request status |
| - Updates jtf_fm_request_history_all - for outcome code |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE get_next_request( p_server_id IN NUMBER
, p_instance_id IN NUMBER
, p_request_id OUT NOCOPY NUMBER
, p_template_id OUT NOCOPY NUMBER
, p_no_of_parameters OUT NOCOPY NUMBER
, p_email_format OUT NOCOPY VARCHAR2
, p_email_from_address OUT NOCOPY VARCHAR2
, p_email_reply_to_address OUT NOCOPY VARCHAR2
, p_sender_display_name OUT NOCOPY VARCHAR2
, p_subject OUT NOCOPY VARCHAR2
, p_parameter_table OUT NOCOPY JTF_VARCHAR2_TABLE_100
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_header IS
SELECT request_id,
template_id,
NO_OF_PARAMETERS,
EMAIL_FORMAT,
EMAIL_FROM_ADDRESS,
EMAIL_REPLY_TO_ADDRESS,
sender_display_name,
subject ,
parameter1,
parameter2,
parameter3,
parameter4,
parameter5,
parameter6,
parameter7,
parameter8,
parameter9,
parameter10,
parameter11,
parameter12,
parameter13,
parameter14,
parameter15,
parameter16,
parameter17,
parameter18,
parameter19,
parameter20,
parameter21,
parameter22,
parameter23,
parameter24,
parameter25,
parameter26,
parameter27,
parameter28,
parameter29,
parameter30,
parameter31,
parameter32,
parameter33,
parameter34,
parameter35,
parameter36,
parameter37,
parameter38,
parameter39,
parameter40,
parameter41,
parameter42,
parameter43,
parameter44,
parameter45,
parameter46,
parameter47,
parameter48,
parameter49,
parameter50,
parameter51,
parameter52,
parameter53,
parameter54,
parameter55,
parameter56,
parameter57,
parameter58,
parameter59,
parameter60,
parameter61,
parameter62,
parameter63,
parameter64,
parameter65,
parameter66,
parameter67,
parameter68,
parameter69,
parameter70,
parameter71,
parameter72,
parameter73,
parameter74,
parameter75,
parameter76,
parameter77,
parameter78,
parameter79,
parameter80,
parameter81,
parameter82,
parameter83,
parameter84,
parameter85,
parameter86,
parameter87,
parameter88,
parameter89,
parameter90,
parameter91,
parameter92,
parameter93,
parameter94,
parameter95,
parameter96,
parameter97,
parameter98,
parameter99,
parameter100
FROM jtf_fm_int_request_header
WHERE request_status IN ('AVAILABLE','INPROGRESS' )
AND server_id = p_server_id
ORDER BY priority, creation_date DESC;
UPDATE jtf_fm_int_request_header
SET request_status = 'INPROGRESS'
WHERE request_id = p_request_id
AND request_status = 'AVAILABLE';
UPDATE jtf_fm_status_all c
SET c.request_status = 'IN_PROCESS'
WHERE c.request_id = l_header_request_id
AND c.request_status <> 'READYTOLOG'
;
UPDATE jtf_fm_request_history_all c
SET c.outcome_code = 'IN_PROCESS'
WHERE c.hist_req_id = l_header_request_id
AND c.outcome_code <> 'READYTOLOG'
;
| Purpose : Selects from jtf_fm_int_request_lines , and |
| - Updates jtf_fm_int_request_batches - for request status |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE get_next_batch ( p_request_id IN NUMBER
, p_server_id IN NUMBER
, p_instance_id IN NUMBER
, P_commit IN VARCHAR2 := FND_API.G_FALSE
, p_line_ids OUT NOCOPY JTF_NUMBER_TABLE
, p_party_id OUT NOCOPY JTF_NUMBER_TABLE
, p_party_name OUT NOCOPY JTF_VARCHAR2_TABLE_200
, p_email_address OUT NOCOPY JTF_VARCHAR2_TABLE_200
, p_COL1 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL2 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL3 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL4 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL5 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL6 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL7 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL8 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL9 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL10 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL11 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL12 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL13 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL14 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL15 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL16 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL17 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL18 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL19 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL20 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL21 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL22 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL23 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL24 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL25 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL26 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL27 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL28 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL29 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL30 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL31 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL32 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL33 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL34 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL35 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL36 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL37 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL38 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL39 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL40 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL41 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL42 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL43 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL44 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL45 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL46 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL47 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL48 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL49 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL50 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL51 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL52 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL53 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL54 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL55 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL56 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL57 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL58 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL59 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL60 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL61 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL62 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL63 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL64 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL65 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL66 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL67 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL68 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL69 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL70 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL71 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL72 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL73 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL74 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL75 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL76 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL77 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL78 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL79 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL80 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL81 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL82 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL83 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL84 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL85 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL86 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL87 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL88 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL89 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL90 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL91 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL92 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL93 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL94 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL95 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL96 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL97 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL98 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL99 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, p_COL100 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
, x_no_of_rows OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR c_batch IS
SELECT rb.batch_id,
rb.request_line_id_start ,
rb.request_line_id_end
FROM jtf_fm_int_request_batches rb
WHERE rb.request_id = p_request_id
AND rb.batch_status = 'AVAILABLE' ;
UPDATE jtf_fm_int_request_batches rb
SET rb.batch_status = 'INPROGRESS' ,
last_update_date = SYSDATE,
rb.SERVER_INSTANCE_ID = p_instance_id
WHERE rb.request_id = p_request_id
AND rb.batch_status = 'AVAILABLE'
AND rb.batch_id = l_batch_id;
SELECT
rl.REQUEST_LINE_ID,
rl.PARTY_ID,
rl.PARTY_NAME,
rl.EMAIL_ADDRESS,
rl.COL1,
rl.COL2,
rl.COL3,
rl.COL4,
rl.COL5,
rl.COL6,
rl.COL7,
rl.COL8,
rl.COL9,
rl.COL10,
rl.COL11,
rl.COL12,
rl.COL13,
rl.COL14,
rl.COL15,
rl.COL16,
rl.COL17,
rl.COL18,
rl.COL19,
rl.COL20,
rl.COL21,
rl.COL22,
rl.COL23,
rl.COL24,
rl.COL25,
rl.COL26,
rl.COL27,
rl.COL28,
rl.COL29,
rl.COL30,
rl.COL31,
rl.COL32,
rl.COL33,
rl.COL34,
rl.COL35,
rl.COL36,
rl.COL37,
rl.COL38,
rl.COL39,
rl.COL40,
rl.COL41,
rl.COL42,
rl.COL43,
rl.COL44,
rl.COL45,
rl.COL46,
rl.COL47,
rl.COL48,
rl.COL49,
rl.COL50,
rl.COL51,
rl.COL52,
rl.COL53,
rl.COL54,
rl.COL55,
rl.COL56,
rl.COL57,
rl.COL58,
rl.COL59,
rl.COL60,
rl.COL61,
rl.COL62,
rl.COL63,
rl.COL64,
rl.COL65,
rl.COL66,
rl.COL67,
rl.COL68,
rl.COL69,
rl.COL70,
rl.COL71,
rl.COL72,
rl.COL73,
rl.COL74,
rl.COL75,
rl.COL76,
rl.COL77,
rl.COL78,
rl.COL79,
rl.COL80,
rl.COL81,
rl.COL82,
rl.COL83,
rl.COL84,
rl.COL85,
rl.COL86,
rl.COL87,
rl.COL88,
rl.COL89,
rl.COL90,
rl.COL91,
rl.COL92,
rl.COL93,
rl.COL94,
rl.COL95,
rl.COL96,
rl.COL97,
rl.COL98,
rl.COL99,
rl.COL100
BULK COLLECT INTO
p_line_ids,
p_party_id,
p_party_name,
p_email_address,
p_COL1,
p_COL2,
p_COL3,
p_COL4,
p_COL5,
p_COL6,
p_COL7,
p_COL8,
p_COL9,
p_COL10,
p_COL11,
p_COL12,
p_COL13,
p_COL14,
p_COL15,
p_COL16,
p_COL17,
p_COL18,
p_COL19,
p_COL20,
p_COL21,
p_COL22,
p_COL23,
p_COL24,
p_COL25,
p_COL26,
p_COL27,
p_COL28,
p_COL29,
p_COL30,
p_COL31,
p_COL32,
p_COL33,
p_COL34,
p_COL35,
p_COL36,
p_COL37,
p_COL38,
p_COL39,
p_COL40,
p_COL41,
p_COL42,
p_COL43,
p_COL44,
p_COL45,
p_COL46,
p_COL47,
p_COL48,
p_COL49,
p_COL50,
p_COL51,
p_COL52,
p_COL53,
p_COL54,
p_COL55,
p_COL56,
p_COL57,
p_COL58,
p_COL59,
p_COL60,
p_COL61,
p_COL62,
p_COL63,
p_COL64,
p_COL65,
p_COL66,
p_COL67,
p_COL68,
p_COL69,
p_COL70,
p_COL71,
p_COL72,
p_COL73,
p_COL74,
p_COL75,
p_COL76,
p_COL77,
p_COL78,
p_COL79,
p_COL80,
p_COL81,
p_COL82,
p_COL83,
p_COL84,
p_COL85,
p_COL86,
p_COL87,
p_COL88,
p_COL89,
p_COL90,
p_COL91,
p_COL92,
p_COL93,
p_COL94,
p_COL95,
p_COL96,
p_COL97,
p_COL98,
p_COL99,
p_COL100
FROM jtf_fm_int_request_lines rl
WHERE rl.request_id = p_request_id
AND rl.request_line_id BETWEEN l_request_line_id_start AND l_request_line_id_end ;
| Purpose : Selects from jtf_fm_int_request_lines , and |
| - Inserts into jtf_fm_processed table |
| - Inserts into jtf_fm_content_failures table |
| - Drops partition for jtf_fm_int_request_lines |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE move_request( p_request_id NUMBER
, x_log_interaction OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_history IS
SELECT USER_HISTORY
FROM jtf_fm_int_request_header
WHERE request_id = p_request_id ;
INSERT INTO jtf_fm_processed
(
request_id ,
job ,
party_id ,
party_name ,
email_address ,
outcome_code ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
email_status ,
col1 ,
col2 ,
col3 ,
col4 ,
col5 ,
col6 ,
col7 ,
col8 ,
col9 ,
col10 ,
col11 ,
col12 ,
col13 ,
col14 ,
col15 ,
col16 ,
col17 ,
col18 ,
col19 ,
col20 ,
col21 ,
col22 ,
col23 ,
col24 ,
col25 ,
col26 ,
col27 ,
col28 ,
col29 ,
col30 ,
col31 ,
col32 ,
col33 ,
col34 ,
col35 ,
col36 ,
col37 ,
col38 ,
col39 ,
col40 ,
col41 ,
col42 ,
col43 ,
col44 ,
col45 ,
col46 ,
col47 ,
col48 ,
col49 ,
col50 ,
col51 ,
col52 ,
col53 ,
col54 ,
col55 ,
col56 ,
col57 ,
col58 ,
col59 ,
col60 ,
col61 ,
col62 ,
col63 ,
col64 ,
col65 ,
col66 ,
col67 ,
col68 ,
col69 ,
col70 ,
col71 ,
col72 ,
col73 ,
col74 ,
col75 ,
col76 ,
col77 ,
col78 ,
col79 ,
col80 ,
col81 ,
col82 ,
col83 ,
col84 ,
col85 ,
col86 ,
col87 ,
col88 ,
col89 ,
col90 ,
col91 ,
col92 ,
col93 ,
col94 ,
col95 ,
col96 ,
col97 ,
col98 ,
col99 ,
col100 ,
partition_id )
SELECT
p_request_id,
rl.request_line_id,
rl.party_id ,
rl.party_name ,
rl.email_address ,
DECODE(enabled_flag, 'N','FAILURE',
DECODE(email_status,'NOTDELIVERED','FAILURE','SUCCESS')),
1,
SYSDATE,
1,
SYSDATE,
1,
DECODE(enabled_flag, 'N','NOT_SENT',
DECODE(email_status,'NOTDELIVERED','NOT_SENT','SENT')),
rl.col1 ,
rl.col2 ,
rl.col3 ,
rl.col4 ,
rl.col5 ,
rl.col6 ,
rl.col7 ,
rl.col8 ,
rl.col9 ,
rl.col10 ,
rl.col11 ,
rl.col12 ,
rl.col13 ,
rl.col14 ,
rl.col15 ,
rl.col16 ,
rl.col17 ,
rl.col18 ,
rl.col19 ,
rl.col20 ,
rl.col21 ,
rl.col22 ,
rl.col23 ,
rl.col24 ,
rl.col25 ,
rl.col26 ,
rl.col27 ,
rl.col28 ,
rl.col29 ,
rl.col30 ,
rl.col31 ,
rl.col32 ,
rl.col33 ,
rl.col34 ,
rl.col35 ,
rl.col36 ,
rl.col37 ,
rl.col38 ,
rl.col39 ,
rl.col40 ,
rl.col41 ,
rl.col42 ,
rl.col43 ,
rl.col44 ,
rl.col45 ,
rl.col46 ,
rl.col47 ,
rl.col48 ,
rl.col49 ,
rl.col50 ,
rl.col51 ,
rl.col52 ,
rl.col53 ,
rl.col54 ,
rl.col55 ,
rl.col56 ,
rl.col57 ,
rl.col58 ,
rl.col59 ,
rl.col60 ,
rl.col61 ,
rl.col62 ,
rl.col63 ,
rl.col64 ,
rl.col65 ,
rl.col66 ,
rl.col67 ,
rl.col68 ,
rl.col69 ,
rl.col70 ,
rl.col71 ,
rl.col72 ,
rl.col73 ,
rl.col74 ,
rl.col75 ,
rl.col76 ,
rl.col77 ,
rl.col78 ,
rl.col79 ,
rl.col80 ,
rl.col81 ,
rl.col82 ,
rl.col83 ,
rl.col84 ,
rl.col85 ,
rl.col86 ,
rl.col87 ,
rl.col88 ,
rl.col89 ,
rl.col90 ,
rl.col91 ,
rl.col92 ,
rl.col93 ,
rl.col94 ,
rl.col95 ,
rl.col96 ,
rl.col97 ,
rl.col98 ,
rl.col99 ,
rl.col100 ,
rl.partition_id
FROM jtf_fm_int_request_lines rl
WHERE rl.request_id = p_request_id;
INSERT INTO jtf_fm_content_failures
(
request_id,
content_number,
job,
media_type,
address,
failure,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
p_request_id,
0,
rl.request_line_id,
'EMAIL',
rl.email_address,
DECODE(rl.enabled_flag, 'N',DECODE (rl.contact_preference_flag,
'N','DO_NOT_CONTACT',
'MALFORMED'),'FAILURE'),
1,
SYSDATE,
1,
SYSDATE,
1
FROM jtf_fm_int_request_lines rl
WHERE rl.request_id = p_request_id
AND (rl.enabled_flag = 'N'
OR rl.email_status = 'NOTDELIVERED' );
| Purpose : Selects from jtf_fm_int_request_lines , and |
| - Truncates partition for jtf_fm_int_request_lines |
| - Deletes from jtf_fm_int_request_batches |
| - Deletes from jtf_fm_int_request_header |
| |
*---------------------------------------------------------------------------------*/
PROCEDURE clean_stalled_request ( p_request_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_lines_count NUMBER;
SELECT COUNT(ROWID)
INTO l_lines_count
FROM jtf_fm_int_request_lines
WHERE request_id = p_request_id
AND email_status = lc_status
;
SELECT partition_name
INTO l_partition_name
FROM jtf_fm_partition_x_request
WHERE request_id = p_request_id;
DELETE
FROM jtf_fm_int_request_batches
WHERE request_id = p_request_id
AND batch_status = lc_status
;
DELETE
FROM jtf_fm_int_request_header
WHERE request_id = p_request_id
;
SELECT partition_id
FROM jtf_fm_partition_x_request
WHERE partition_id = ( SELECT MIN(partition_id)
FROM jtf_fm_partition_x_request
WHERE request_id IS NULL
)
FOR UPDATE;
SELECT partition_id, request_id
INTO l_partition_id, l_request_id
FROM ( SELECT part.partition_id, part.request_id
FROM jtf_fm_partition_x_request part, jtf_fm_request_history_all history
WHERE part.request_id IS NOT NULL
AND part.request_id = history.hist_req_id
AND history.outcome_code NOT IN ('IN_PROGRESS', 'PAUSED')
ORDER BY request_id ASC )
WHERE ROWNUM < 2
;
UPDATE jtf_fm_partition_x_request
SET request_id = p_request_id
WHERE partition_id = p_partition_id
AND request_id IS NULL
;
UPDATE jtf_fm_partition_x_request
SET request_id = NULL
WHERE request_id = p_request_id
RETURNING partition_name INTO l_partition_name
;