eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_FM_INT_REQUEST_PKG dependencies on JTF_FM_INT_REQUEST_LINES

Line 63: | Purpose : Updates jtf_fm_int_request_lines table for "DO NOT" contact |

59:
60: /*---------------------------------------------------------------------------------*
61: | Procedure Name : UPDATE_CONTACT_PREF |
62: | |
63: | Purpose : Updates jtf_fm_int_request_lines table for "DO NOT" contact |
64: | party IDs. |
65: *---------------------------------------------------------------------------------*/
66: PROCEDURE update_contact_pref ( p_request_id IN NUMBER )
67: IS

Line 73: jtf_fm_int_request_lines c

69: l_line_id l_line_id_table_type ;
70: CURSOR c_parties IS
71: SELECT c.request_line_id
72: FROM hz_contact_preferences b ,
73: jtf_fm_int_request_lines c
74: WHERE b.preference_code = 'DO_NOT'
75: AND b.contact_level_table = 'HZ_PARTIES'
76: AND b.contact_level_table_id = c.party_id
77: AND c.request_id = p_request_id

Line 86: UPDATE jtf_fm_int_request_lines

82: FETCH c_parties BULK COLLECT INTO l_line_id ;
83: CLOSE c_parties;
84:
85: FORALL i IN l_line_id.FIRST .. l_line_id.LAST
86: UPDATE jtf_fm_int_request_lines
87: SET contact_preference_flag = 'N',
88: enabled_flag = 'N'
89: WHERE request_line_id = l_line_id(i);
90: END update_contact_pref;

Line 96: | Purpose : Updates jtf_fm_int_request_lines table for invalid email |

92:
93: /*---------------------------------------------------------------------------------*
94: | Procedure Name : VALIDATE_EMAIL |
95: | |
96: | Purpose : Updates jtf_fm_int_request_lines table for invalid email |
97: | addresses. |
98: *---------------------------------------------------------------------------------*/
99: PROCEDURE validate_email( p_request_id IN NUMBER )
100: IS

Line 105: FROM jtf_fm_int_request_lines c

101: TYPE l_line_id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
102: l_line_id l_line_id_table_type ;
103: CURSOR c_lines IS
104: SELECT c.request_line_id
105: FROM jtf_fm_int_request_lines c
106: WHERE c.request_id = p_request_id
107: AND (c.email_address IS NULL
108: OR INSTR(c.email_address,'@') < 1
109: OR INSTR(c.email_address, ':') > 0)

Line 117: UPDATE jtf_fm_int_request_lines

113: FETCH c_lines BULK COLLECT INTO l_line_id ;
114: CLOSE c_lines;
115:
116: FORALL i IN l_line_id.FIRST .. l_line_id.LAST
117: UPDATE jtf_fm_int_request_lines
118: SET enabled_flag = 'N'
119: WHERE request_line_id = l_line_id(i);
120: END validate_email;
121:

Line 831: 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,

827: RAISE e_partition_not_found;
828: END IF;
829:
830: --static columns
831: 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,
832: created_by, last_update_login, object_version_number, email_status, enabled_flag,
833: contact_preference_flag ';
834:
835: --values for static columns

Line 836: l_select_cols :='SELECT ' || l_request_id ||', JTF_FM_INT_REQUEST_LINES_s.NEXTVAL, '|| l_partition_id ||',

832: created_by, last_update_login, object_version_number, email_status, enabled_flag,
833: contact_preference_flag ';
834:
835: --values for static columns
836: l_select_cols :='SELECT ' || l_request_id ||', JTF_FM_INT_REQUEST_LINES_s.NEXTVAL, '|| l_partition_id ||',
837: CEIL(ROWNUM/ ' || l_batch_size || ' ),
838: SYSDATE,
839: 1,
840: SYSDATE,

Line 891: --l_create_part := 'ALTER TABLE jtf_fm_int_request_lines ADD PARTITION p_'|| l_request_id || ' VALUES ('||l_request_id || ')';

887: l_length := LENGTH(l_insert_statement);
888: l_no_of_chunks := CEIL(l_length/255);
889:
890: --No need to create partition based upon the request_id
891: --l_create_part := 'ALTER TABLE jtf_fm_int_request_lines ADD PARTITION p_'|| l_request_id || ' VALUES ('||l_request_id || ')';
892: --EXECUTE IMMEDIATE l_create_part;
893:
894:
895: --Execute immediate requires the bind variables individually mentioned in the USING part.

Line 933: FROM jtf_fm_int_request_lines d

929: MAX(d.request_line_id),
930: '',
931: 'AVAILABLE',
932: SYSDATE
933: FROM jtf_fm_int_request_lines d
934: WHERE d.request_id = l_request_id
935: GROUP BY d.batch_no;
936:
937: COMMIT;

Line 1002: | jtf_fm_int_request_lines table and sets the email status to |

998: /*---------------------------------------------------------------------------------*
999: | Procedure Name : UPDATE_LINES_STATUS_BULK |
1000: | |
1001: | Purpose : For a given set of lines id, this procedure updates the |
1002: | jtf_fm_int_request_lines table and sets the email status to |
1003: | the passed email status PL/SQL table. |
1004: | - Updates jtf_fm_int_request_header - for request status |
1005: | - Updates jtf_fm_status_all - for request status |
1006: | - Updates jtf_fm_request_history_all - for outcome code |

Line 1034: FROM jtf_fm_int_request_lines c

1030: SELECT SUM(DECODE(c.contact_preference_flag,'N',1,0)) ,
1031: SUM(DECODE(c.email_status,'NOTDELIVERED',1,0)) ,
1032: SUM(DECODE(c.enabled_flag, 'N',1,0)),
1033: COUNT(1)
1034: FROM jtf_fm_int_request_lines c
1035: WHERE c.request_id = l_request_id ;
1036: BEGIN
1037: l_request_id := request_id;
1038:

Line 1040: UPDATE jtf_fm_int_request_lines

1036: BEGIN
1037: l_request_id := request_id;
1038:
1039: FORALL i IN LINE_IDS.FIRST .. LINE_IDS.LAST
1040: UPDATE jtf_fm_int_request_lines
1041: SET email_status = line_status(i)
1042: WHERE request_line_id = line_ids(i);
1043:
1044: UPDATE jtf_fm_int_request_header c

Line 1048: FROM jtf_fm_int_request_lines a

1044: UPDATE jtf_fm_int_request_header c
1045: SET c.request_status = 'READYTOLOG'
1046: WHERE c.request_id = l_request_id
1047: AND NOT EXISTS ( SELECT '1'
1048: FROM jtf_fm_int_request_lines a
1049: WHERE c.request_id = a.request_id
1050: AND a.email_status IN ('AVAILABLE', 'NEW','ASSIGNED')
1051: AND a.enabled_flag = 'Y' );
1052: OPEN c_processed;

Line 1146: | jtf_fm_int_request_lines table and sets the email status to |

1142: /*---------------------------------------------------------------------------------*
1143: | Procedure Name : UPDATE_LINES_STATUS |
1144: | |
1145: | Purpose : For a given set of lines id, this procedure updates the |
1146: | jtf_fm_int_request_lines table and sets the email status to |
1147: | the passed email status PL/SQL table. |
1148: | |
1149: *---------------------------------------------------------------------------------*/
1150: PROCEDURE update_lines_status ( line_ids IN JTF_VARCHAR2_TABLE_100

Line 1160: UPDATE jtf_fm_int_request_lines

1156: , x_msg_data OUT NOCOPY VARCHAR2
1157: ) IS
1158: BEGIN
1159: FORALL i IN LINE_IDS.FIRST .. LINE_IDS.LAST
1160: UPDATE jtf_fm_int_request_lines
1161: SET email_status = line_status
1162: WHERE request_line_id = line_ids(i);
1163:
1164: IF x_return_status = fnd_api.g_ret_sts_error

Line 1250: FROM jtf_fm_int_request_lines c

1246: SELECT SUM(DECODE(c.CONTACT_PREFERENCE_FLAG,'N',1,0)) ,
1247: SUM(DECODE(c.EMAIL_STATUS,'NOTDELIVERED',1,0)) ,
1248: SUM(DECODE(c.enabled_flag, 'N',1,0)),
1249: COUNT(1)
1250: FROM jtf_fm_int_request_lines c
1251: WHERE c.request_id = l_request_id ;
1252: BEGIN
1253: l_request_id := p_request_id;
1254:

Line 1438: | Purpose : Selects from jtf_fm_int_request_lines , and |

1434:
1435: /*---------------------------------------------------------------------------------*
1436: | Procedure Name : GET_NEXT_REQUEST |
1437: | |
1438: | Purpose : Selects from jtf_fm_int_request_lines , and |
1439: | - Updates jtf_fm_int_request_header - for request status |
1440: | - Updates jtf_fm_status_all - for request status |
1441: | - Updates jtf_fm_request_history_all - for outcome code |
1442: | |

Line 1722: | Purpose : Selects from jtf_fm_int_request_lines , and |

1718:
1719: /*---------------------------------------------------------------------------------*
1720: | Procedure Name : GET_NEXT_BATCH |
1721: | |
1722: | Purpose : Selects from jtf_fm_int_request_lines , and |
1723: | - Updates jtf_fm_int_request_batches - for request status |
1724: | |
1725: *---------------------------------------------------------------------------------*/
1726: PROCEDURE get_next_batch ( p_request_id IN NUMBER

Line 2295: FROM jtf_fm_int_request_lines rl

2291: p_COL97,
2292: p_COL98,
2293: p_COL99,
2294: p_COL100
2295: FROM jtf_fm_int_request_lines rl
2296: WHERE rl.request_id = p_request_id
2297: AND rl.request_line_id BETWEEN l_request_line_id_start AND l_request_line_id_end ;
2298:
2299: x_no_of_rows := SQL%ROWCOUNT;

Line 2346: | Purpose : Selects from jtf_fm_int_request_lines , and |

2342:
2343: /*---------------------------------------------------------------------------------*
2344: | Procedure Name : MOVE_REQUEST |
2345: | |
2346: | Purpose : Selects from jtf_fm_int_request_lines , and |
2347: | - Inserts into jtf_fm_processed table |
2348: | - Inserts into jtf_fm_content_failures table |
2349: | - Drops partition for jtf_fm_int_request_lines |
2350: | |

Line 2349: | - Drops partition for jtf_fm_int_request_lines |

2345: | |
2346: | Purpose : Selects from jtf_fm_int_request_lines , and |
2347: | - Inserts into jtf_fm_processed table |
2348: | - Inserts into jtf_fm_content_failures table |
2349: | - Drops partition for jtf_fm_int_request_lines |
2350: | |
2351: *---------------------------------------------------------------------------------*/
2352: PROCEDURE move_request( p_request_id NUMBER
2353: , x_log_interaction OUT NOCOPY VARCHAR2

Line 2600: FROM jtf_fm_int_request_lines rl

2596: rl.col98 ,
2597: rl.col99 ,
2598: rl.col100 ,
2599: rl.partition_id
2600: FROM jtf_fm_int_request_lines rl
2601: WHERE rl.request_id = p_request_id;
2602:
2603: COMMIT;
2604:

Line 2633: FROM jtf_fm_int_request_lines rl

2629: SYSDATE,
2630: 1,
2631: SYSDATE,
2632: 1
2633: FROM jtf_fm_int_request_lines rl
2634: WHERE rl.request_id = p_request_id
2635: AND (rl.enabled_flag = 'N'
2636: OR rl.email_status = 'NOTDELIVERED' );
2637:

Line 2654: | Purpose : Selects from jtf_fm_int_request_lines , and |

2650:
2651: /*---------------------------------------------------------------------------------*
2652: | Procedure Name : CLEAN_STALLED_REQUEST |
2653: | |
2654: | Purpose : Selects from jtf_fm_int_request_lines , and |
2655: | - Truncates partition for jtf_fm_int_request_lines |
2656: | - Deletes from jtf_fm_int_request_batches |
2657: | - Deletes from jtf_fm_int_request_header |
2658: | |

Line 2655: | - Truncates partition for jtf_fm_int_request_lines |

2651: /*---------------------------------------------------------------------------------*
2652: | Procedure Name : CLEAN_STALLED_REQUEST |
2653: | |
2654: | Purpose : Selects from jtf_fm_int_request_lines , and |
2655: | - Truncates partition for jtf_fm_int_request_lines |
2656: | - Deletes from jtf_fm_int_request_batches |
2657: | - Deletes from jtf_fm_int_request_header |
2658: | |
2659: *---------------------------------------------------------------------------------*/

Line 2670: FROM jtf_fm_int_request_lines

2666: BEGIN
2667: --Getting the count from lines
2668: SELECT COUNT(ROWID)
2669: INTO l_lines_count
2670: FROM jtf_fm_int_request_lines
2671: WHERE request_id = p_request_id
2672: AND email_status = lc_status
2673: ;
2674:

Line 2683: EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines TRUNCATE PARTITION '|| l_partition_name;

2679: INTO l_partition_name
2680: FROM jtf_fm_partition_x_request
2681: WHERE request_id = p_request_id;
2682:
2683: EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines TRUNCATE PARTITION '|| l_partition_name;
2684: END IF;
2685:
2686: --Deleting from batches table
2687: DELETE

Line 2825: EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines TRUNCATE PARTITION '|| l_partition_name;

2821: WHERE request_id = p_request_id
2822: RETURNING partition_name INTO l_partition_name
2823: ;
2824:
2825: EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines TRUNCATE PARTITION '|| l_partition_name;
2826:
2827: x_retcode := 'S';
2828:
2829: EXCEPTION