[Home] [Help]
38:
39: DELETE FROM jty_webadi_qual_header jwh
40: WHERE NOT EXISTS
41: (SELECT 1
42: FROM jty_webadi_oth_terr_intf jwot
43: WHERE jwot.user_sequence = jwh.user_sequence) ;
44:
45:
46: INSERT
166: fnd_message.set_name('JTF', 'JTY_OTH_TERR_GEO_TYPE');
167: x_errbuf := fnd_message.GET();
168: ELSE
169: INSERT
170: INTO jty_webadi_oth_terr_intf(interface_type, org_id, usage_id, user_id, user_sequence,
171: qual1_value1, qual2_value1, qual3_value1, qual4_value1, qual5_value1, qual6_value1,
172: qual7_value1, qual8_value1, qual9_value1, qual10_value1, qual11_value1, qual12_value1,
173: qual13_value1, qual14_value1, qual15_value1, qual16_value1, qual17_value1, qual18_value1,
174: qual19_value1, qual20_value1, qual21_value1, qual22_value1, qual23_value1, qual24_value1,
411:
412: IF p_terr_rec.terr_id.COUNT > 0 THEN
413: l_header := 'TERR';
414: forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST INSERT
415: INTO jty_webadi_oth_terr_intf(interface_type, action_flag, header, user_sequence, user_id, org_id,
416: org_name, usage_id, terr_id, terr_name, terr_type_name, terr_type_id, rank, num_winners,
417: terr_start_date, terr_end_date, parent_terr_id, hierarchy, attribute_category, attribute1,
418: attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
419: attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
432:
433: l_header := 'QUAL';
434:
435: forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST INSERT
436: INTO jty_webadi_oth_terr_intf(interface_type, action_flag, header, user_sequence, user_id,
437: org_id, org_name, usage_id, terr_id, terr_name, terr_type_name, terr_type_id, rank,
438: num_winners, terr_start_date, terr_end_date, parent_terr_id, hierarchy, creation_date,
439: created_by, last_update_date, last_updated_by, last_update_login, row_id, qual1_value_id,
440: qual1_value1, qual1_value2, qual1_value3, qual2_value_id, qual2_value1, qual2_value2,
1338: jtr.attribute15)
1339: terr_rsc;
1340:
1341: forall i IN p_terr_rec.terr_id.FIRST .. p_terr_rec.terr_id.LAST
1342: INSERT INTO jty_webadi_oth_terr_intf(interface_type, user_sequence, terr_rsc_id, action_flag,
1343: header, user_id, org_id, org_name, usage_id, parent_terr_id, terr_id, terr_name,
1344: hierarchy, creation_date, created_by, last_update_date, last_updated_by, last_update_login)
1345: SELECT jwr.interface_type,
1346: jwr.user_sequence,
1657: l_mode := nvl(p_mode, 'NODE');
1658: l_view := nvl(p_view, 'TERR');
1659:
1660: BEGIN
1661: SELECT jty_webadi_oth_terr_intf_s.nextval
1662: INTO l_seq
1663: FROM dual;
1664: -- remove existing old data which is older than 3 days
1665: --Following query changed for bug 8734322 to stop webadi territory tables to grow in size because earlier where condition
1664: -- remove existing old data which is older than 3 days
1665: --Following query changed for bug 8734322 to stop webadi territory tables to grow in size because earlier where condition
1666: -- checks for user id and if that user never comes for second time then that data remains in table
1667:
1668: DELETE FROM jty_webadi_oth_terr_intf
1669: WHERE
1670: -- user_id = p_user_id AND
1671: creation_date <= sysdate -3;
1672:
1672:
1673: DELETE FROM jty_webadi_resources jwr
1674: WHERE NOT EXISTS
1675: (SELECT 1
1676: FROM jty_webadi_oth_terr_intf jwot
1677: WHERE jwot.user_sequence = jwr.user_sequence)
1678: ;
1679:
1680: COMMIT;