[Home] [Help]
PACKAGE BODY: APPS.POS_SURVEY_INTEGRATION
Source
1 PACKAGE BODY pos_survey_integration AS
2 /* $Header: POSSURIB.pls 120.2 2005/12/07 18:25:48 abtrived noship $ */
3
4
5
6 PROCEDURE save_transaction
7 (p_flow_key IN VARCHAR2,
8 p_supplier_reg_id IN NUMBER,
9 p_vendor_id IN NUMBER,
10 p_survey_transaction_id IN NUMBER,
11 p_respondent_table_name IN VARCHAR2,
12 p_respondent_id IN NUMBER,
13 x_status OUT NOCOPY VARCHAR2,
14 x_msg OUT NOCOPY VARCHAR2,
15 p_map_id IN NUMBER default null
16 ) IS
17
18 CURSOR l_mapping_cur1 IS
19 SELECT mapping_id
20 FROM pos_supplier_mappings
21 WHERE supplier_reg_id = p_supplier_reg_id;
22
23 CURSOR l_mapping_cur2 IS
24 SELECT mapping_id
25 FROM pos_supplier_mappings
26 WHERE vendor_id = p_vendor_id;
27
28 l_mapping_id NUMBER;
29
30 CURSOR l_transaction_cur IS
31 SELECT transaction_id, supplier_update_flag
32 FROM pos_survey_transactions
33 WHERE supplier_mapping_id = l_mapping_id
34 AND survey_transaction_id = p_survey_transaction_id;
35
36 l_transaction_id NUMBER;
37 l_supplier_update_flag VARCHAR2(1);
38
39 BEGIN
40
41 x_msg := ' :txn_id = ' || p_survey_transaction_id;
42 x_status := '10';
43
44 IF p_respondent_table_name NOT IN ('FND_USER', 'HZ_PARTIES') THEN
45 x_status := 'E';
46 x_msg := 'POS_SURVEY_BAD_RESP_TABLE_NAME' || fnd_message.get('POS_SURVEY_BAD_RESP_TABLE_NAME');
47 RETURN;
48 END IF;
49
50 x_status := '20';
51
52
53 IF (p_flow_key = 'SUPPREG') THEN
54
55 /* abhi - for SUPPREG, data is not committed to DB, need to pass the mapping_id as param instead of getting from DB */
56
57 l_mapping_id := p_map_id;
58 x_status := '25';
59
60 ELSIF (p_flow_key = 'BUYERSUPPREG') THEN
61
62 OPEN l_mapping_cur1;
63 FETCH l_mapping_cur1 INTO l_mapping_id;
64 IF l_mapping_cur1%notfound THEN
65 CLOSE l_mapping_cur1;
66 x_status := 'E';
67 x_msg := 'POS_SURVEY_BAD_REG_ID' || fnd_message.get('POS_SURVEY_BAD_REG_ID');
68 RETURN;
69 END IF;
70 CLOSE l_mapping_cur1;
71 x_status := '30';
72
73 ELSIF p_flow_key IN ('SPMSUPPLIER', 'SPMBUYER') THEN
74
75 OPEN l_mapping_cur2;
76 FETCH l_mapping_cur2 INTO l_mapping_id;
77 IF l_mapping_cur2%notfound THEN
78 CLOSE l_mapping_cur2;
79 x_status := 'E';
80 x_msg := 'POS_SURVEY_BAD_VENDOR_ID' || fnd_message.get('POS_SURVEY_BAD_VENDOR_ID');
81 RETURN;
82 END IF;
83 CLOSE l_mapping_cur2;
84
85 x_status := '40';
86
87 ELSE
88
89 x_status := 'E';
90 x_msg := 'POS_SURVEY_BAD_FLOW_KEY' || fnd_message.get('POS_SURVEY_BAD_FLOW_KEY');
91 RETURN;
92
93 END IF;
94
95 x_status := '50';
96
97 OPEN l_transaction_cur;
98 FETCH l_transaction_cur INTO l_transaction_id, l_supplier_update_flag;
99 IF l_transaction_cur%notfound THEN
100 CLOSE l_transaction_cur;
101
102 -- need to insert
103 IF p_respondent_table_name = 'POS_CONTACT_REQUESTS' THEN
104 l_supplier_update_flag := 'Y';
105 ELSE
106 l_supplier_update_flag := 'N';
107 END IF;
108
109 x_status := '60';
110
111 INSERT INTO pos_survey_transactions
112 (transaction_id, supplier_mapping_id, survey_transaction_id, created_by,
113 creation_date, last_updated_by, last_update_date, last_update_login,
114 respondent_table_name, respondent_id, supplier_update_flag)
115 VALUES
116 (pos_survey_transactions_s.NEXTVAL, l_mapping_id, p_survey_transaction_id, fnd_global.user_id,
117 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
118 p_respondent_table_name, p_respondent_id, l_supplier_update_flag);
119
120 ELSE
121
122 CLOSE l_transaction_cur;
123
124 --need to update
125 IF ((l_supplier_update_flag IS NULL OR l_supplier_update_FLAG <> 'Y')
126 AND (p_respondent_table_name <> 'POS_CONTACT_REQUESTS')) THEN
127 l_supplier_update_flag := 'N';
128 ELSE
129 l_supplier_update_flag := 'Y';
130 END IF;
131
132 x_status := '70';
133
134 UPDATE pos_survey_transactions
135 SET last_updated_by = fnd_global.user_id,
136 last_update_date = sysdate,
137 last_update_login = fnd_global.login_id,
138 respondent_table_name = p_respondent_table_name,
139 respondent_id = p_respondent_id,
140 supplier_update_flag = l_supplier_update_flag
141 WHERE transaction_id = l_transaction_id;
142
143 END IF;
144
145 commit;
146 x_msg := 'Reached the very End' || x_msg;
147
148 END save_transaction;
149
150
151
152
153
154 END pos_survey_integration;