[Home] [Help]
33: --
34: -- History
35: -- 05/05/2000 ptendulk created
36: -- 08/07/2000 ptendulk Commented the procedure as this one is moved to
37: -- AMS_Utility_Pvt .
38: -------------------------------------------------------------------------------
39: --PROCEDURE Write_Log
40: --( p_text IN VARCHAR2 := NULL)
41: --IS
136: -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
137: -- =============================================================================================
138: EXCEPTION
139: WHEN OTHERS THEN
140: AMS_Utility_Pvt.Debug_Message('Error in expire_inactive_party');
141: --
142: IF(c_old_party_id%ISOPEN)then
143: CLOSE c_old_party_id;
144: END IF;
244: -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
245: -- =============================================================================================
246: EXCEPTION
247: WHEN OTHERS THEN
248: AMS_Utility_Pvt.Debug_Message('Error in expire_changed_party');
249: --
250: IF(c_old_party_rec%ISOPEN)then
251: CLOSE c_old_party_rec;
252: END IF;
327: l_count NUMBER;
328:
329: BEGIN
330:
331: AMS_Utility_Pvt.Debug_Message('String : '||p_sql_str) ;
332: --
333: x_return_status := FND_API.G_RET_STS_SUCCESS;
334: OPEN c_party_id FOR p_sql_str;
335:
340:
341: OPEN c_party_count(l_party_id);
342: FETCH c_party_count INTO l_party_count;
343: CLOSE c_party_count;
344: AMS_Utility_Pvt.Debug_Message('Insert ');
345: --
346: IF l_party_count = 0 THEN -- new party is not in the table
347:
348: LOOP -- generate an unique ID for the record
357:
358: EXIT WHEN l_count = 0;
359:
360: END LOOP;
361: AMS_Utility_Pvt.Debug_Message('Insert ');
362: --
363: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
364: (
365: ams_party_market_segment_id
394: );
395:
396:
397: ELSE
398: AMS_Utility_Pvt.Debug_Message('Update ');
399: --
400: OPEN c_expire_party_count(l_party_id);
401: FETCH c_expire_party_count INTO l_expire_party_count;
402: CLOSE c_expire_party_count;
427: -- Following Exception block is added by ptendulk on May02-2000 to handle Insert/Update Exception
428: -- =============================================================================================
429: EXCEPTION
430: WHEN OTHERS THEN
431: AMS_Utility_Pvt.Debug_Message('Error in insert_new_party'||sqlerrm);
432: --
433: IF(c_party_count%ISOPEN)then
434: CLOSE c_party_count;
435: END IF;
498: x_return_status := FND_API.G_RET_STS_SUCCESS;
499:
500:
501:
502: AMS_Utility_PVT.debug_message(l_full_name||': PARSE SQL start');
503: --
504:
505: OPEN c_old_party_id;
506: LOOP
532: -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
533: -- =============================================================================================
534: EXCEPTION
535: WHEN OTHERS THEN
536: AMS_Utility_Pvt.Debug_Message('Error in expire_inactive_party');
537: --
538: IF(c_old_party_id%ISOPEN)then
539: CLOSE c_old_party_id;
540: END IF;
627: -- Following Exception block is added by ptendulk on May02-2000 to handle Update Exception
628: -- =============================================================================================
629: EXCEPTION
630: WHEN OTHERS THEN
631: AMS_Utility_Pvt.Debug_Message('Error in expire_changed_party');
632: --
633: IF(c_old_party_rec%ISOPEN)then
634: CLOSE c_old_party_rec;
635: END IF;
715: OPEN c_party_count(l_party_id);
716: FETCH c_party_count INTO l_party_count;
717: CLOSE c_party_count;
718:
719: AMS_Utility_Pvt.Debug_Message('Insert ');
720: --
721: IF l_party_count = 0 THEN -- new party is not in the table
722:
723: LOOP -- generate an unique ID for the record
733: EXIT WHEN l_count = 0;
734:
735: END LOOP;
736:
737: AMS_Utility_Pvt.Debug_Message('Insert ');
738: --
739: INSERT INTO AMS_PARTY_MARKET_SEGMENTS
740: (
741: ams_party_market_segment_id
768: );
769:
770:
771: ELSE
772: AMS_Utility_Pvt.Debug_Message('Update ');
773: --
774: OPEN c_expire_party_count(l_party_id);
775: FETCH c_expire_party_count INTO l_expire_party_count;
776: CLOSE c_expire_party_count;
802: -- Following Exception block is added by ptendulk on May02-2000 to handle Insert/Update Exception
803: -- =============================================================================================
804: EXCEPTION
805: WHEN OTHERS THEN
806: AMS_Utility_Pvt.Debug_Message('Error in insert_new_party'||sqlerrm);
807: --
808: IF(c_party_count%ISOPEN)then
809: CLOSE c_party_count;
810: END IF;
874: l_temp NUMBER;
875: l_party_cur NUMBER ;
876: l_dummy NUMBER ;
877: BEGIN
878: AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
879: --
880: IF p_type = 'NATIVE' THEN
881: -- Get the query in to VAriable
882: FOR i IN p_query.FIRST..p_query.LAST
1119: l_party_cur NUMBER ;
1120: l_dummy NUMBER ;
1121: BEGIN
1122:
1123: AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
1124: --
1125: IF p_type = 'NATIVE' THEN
1126: -- Get the query in to VAriable
1127: FOR i IN p_query.first..p_query.last
1354: -- WHERE workbook_name = wb_name
1355: -- AND workbook_owner_name = wb_owner ;
1356:
1357: BEGIN
1358: AMS_Utility_Pvt.Debug_Message('Start Loading ');
1359: --
1360: x_return_status := FND_API.G_RET_STS_SUCCESS;
1361:
1362: -- Check the cells If the p_cell_id is null then Refresh all the cells
1380: OPEN c_sql_id(l_cell_id);
1381: FETCH c_sql_id INTO l_wb_name,l_wb_owner,l_ws_name;
1382: CLOSE c_sql_id;
1383:
1384: AMS_Utility_Pvt.Debug_Message('WB : '||l_wb_name||'Owner : '||l_wb_owner);
1385: --
1386:
1387: Validate_Sql
1388: (p_workbook_name => l_wb_name ,
1404:
1405:
1406: IF l_mkt_seg_flag = 'Y' THEN
1407:
1408: AMS_Utility_Pvt.Debug_Message('Load MKt ');
1409: --
1410:
1411: Load_Mkt_Seg(l_cell_id,
1412: l_query,
1415: x_msg_count ,
1416: x_msg_data);
1417:
1418: ELSE
1419: AMS_Utility_Pvt.Debug_Message('Load Target ');
1420: --
1421: Load_Tgt_Seg(l_cell_id,
1422: l_query,
1423: l_type,
1455: OPEN c_sql_id(l_cell_id);
1456: FETCH c_sql_id INTO l_wb_name,l_wb_owner,l_ws_name;
1457: CLOSE c_sql_id;
1458:
1459: AMS_Utility_Pvt.Debug_Message('WB : '||l_wb_name||'Owner : '||l_wb_owner);
1460: --
1461:
1462: Validate_Sql
1463: (p_workbook_name => l_wb_name ,
1476: -- Load the Segments
1477:
1478: IF l_mkt_seg_flag = 'Y' THEN
1479:
1480: AMS_Utility_Pvt.Debug_Message('Load MKt ');
1481: --
1482:
1483: Load_Mkt_Seg(l_cell_id,
1484: l_query,
1487: x_msg_count ,
1488: x_msg_data);
1489:
1490: ELSE
1491: AMS_Utility_Pvt.Debug_Message('Load Target ');
1492: --
1493: Load_Tgt_Seg(l_cell_id,
1494: l_query,
1495: l_type,
1610: -- x_msg_count => l_msg_count ,
1611: -- x_msg_data => l_msg_data ) ;
1612:
1613: -- Write_log ;
1614: Ams_Utility_Pvt.Write_Conc_log ;
1615: IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
1616: retcode :=0;
1617: ELSE
1618: retcode :=1;
1759: l_counter NUMBER ;
1760: l_dummy NUMBER ;
1761: BEGIN
1762:
1763: AMS_Utility_PVT.debug_message(l_full_name||': start');
1764: --
1765:
1766: -- Initialize API return status to success
1767: l_return_status := FND_API.G_RET_STS_SUCCESS;
1800: x_overflow => l_overflow);
1801:
1802:
1803: IF (l_found = FND_API.G_FALSE) THEN
1804: AMS_Utility_PVT.debug_message(l_full_name||': Invalid SQL');
1805: --
1806: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1807: -- Invalid SQL for the Discoverer
1808: FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_WB');
1839: x_position => l_position,
1840: x_overflow => l_overflow);
1841:
1842: IF (l_found = FND_API.G_FALSE) THEN
1843: AMS_Utility_PVT.debug_message(l_full_name||': No party ID in the SQL');
1844: --
1845: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1846: -- Invalid SQL for the Discoverer
1847: FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_WB');
1850: CLOSE c_ws_name ;
1851: RAISE FND_API.G_EXC_ERROR;
1852: END IF;
1853: ELSIF(l_found = FND_API.G_TRUE)THEN
1854: AMS_Utility_PVT.debug_message(l_full_name||': Party ID in the SQL');
1855: --
1856:
1857: -- Get the sql string form "SELECT" to "FROM" into the string and
1858: OPEN c_till_from_sql(l_found_in_str) ;
1873: --Pass it to get the formated select clause till form
1874: Format_Party_Id(l_str,l_str) ;
1875:
1876:
1877: AMS_UTILITY_PVT.Debug_Message('Sql String : '||l_str);
1878: --
1879: -- So We Have String like 'Select AMS.Party_id FROM'
1880: -- Store it in PLSQL table as first row and then Store the other
1881: -- Rows of the sql as neft rows
1917: l_size := l_size + l_tmp_size ;
1918: END LOOP ;
1919:
1920: IF l_size < l_dbms_size THEN
1921: AMS_Utility_PVT.debug_message(l_full_name||': DBMS_SQL');
1922:
1923: x_sql_type := 'DBMS' ;
1924: ELSE
1925: AMS_Utility_PVT.debug_message(l_full_name||': NATIVE_SQL');
1921: AMS_Utility_PVT.debug_message(l_full_name||': DBMS_SQL');
1922:
1923: x_sql_type := 'DBMS' ;
1924: ELSE
1925: AMS_Utility_PVT.debug_message(l_full_name||': NATIVE_SQL');
1926:
1927: x_sql_type := 'NATIVE' ;
1928: END IF;
1929: x_query := l_workbook_sql ;
1927: x_sql_type := 'NATIVE' ;
1928: END IF;
1929: x_query := l_workbook_sql ;
1930:
1931: AMS_Utility_PVT.debug_message(l_full_name||': End');
1932:
1933: EXCEPTION
1934: WHEN FND_API.G_EXC_ERROR THEN
1935:
2004: x_msg_count => l_msg_count,
2005: x_msg_data => l_msg_data);
2006:
2007: -- Write_log ;
2008: Ams_Utility_Pvt.Write_Conc_log ;
2009: IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
2010: retcode :=0;
2011: ELSE
2012: retcode :=1;
2019: p_text IN VARCHAR2
2020: ) IS
2021: BEGIN
2022: IF G_DEBUG_LEVEL THEN
2023: Ams_Utility_pvt.Write_Conc_log (p_text);
2024: END IF;
2025: END write_conc_log;
2026:
2027: /*
2029: IS
2030: i number := 1;
2031: j number;
2032: BEGIN
2033: ams_utility_pvt.write_conc_log(p_text);
2034:
2035: j := length(p_text);
2036: while (i <= j) loop
2037: dbms_output.put_line(substr(p_text, i, 200));
2618: */
2619: BEGIN
2620: p_retcode := 0;
2621: FND_MSG_PUB.initialize;
2622: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': START for territory ' || p_terr_id);
2623: l_terr_id := p_terr_id;
2624: l_client_info := TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'), 1, 10));
2625: l_final_bind_vars := p_bind_vars;
2626: LOOP
2950:
2951: END IF;
2952:
2953:
2954: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Success for territory ' || p_terr_id);
2955:
2956: /* recursively generate party list for the territory's children
2957: passing in parent's qualifier directly so don't need to calculate again
2958: */
2979: END LOOP;
2980: EXCEPTION
2981: WHEN OTHERS THEN
2982: /* Let the master procdure handle exception */
2983: Ams_Utility_pvt.Write_Conc_log('Exception in get_party_territory ' || sqlerrm);
2984: p_retcode := 1;
2985: l_err_msg := 'Exception while generating parties for territory id=' || p_terr_id || ' - ' || sqlerrm;
2986: p_errbuf := l_err_msg;
2987: raise;
3038: SELECT AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
3039: FROM DUAL;
3040:
3041: BEGIN
3042: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Start buyinggroup_id=' || p_bg_id);
3043: p_errbuf := null;
3044: p_retcode := 0;
3045:
3046: -- delete all buying group records for this subject_id
3099:
3100: IF l_obj_list.count = 0 THEN
3101: -- return. Leaf node.
3102: p_obj_list := l_obj_list;
3103: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': END buyinggroup_id=' || p_bg_id);
3104: return;
3105: END IF;
3106:
3107: FOR I IN l_obj_list.FIRST .. l_obj_list.LAST LOOP
3223: );
3224: END IF;
3225:
3226: p_obj_list := l_all_obj_list;
3227: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': END buyinggroup_id=' || p_bg_id);
3228:
3229: EXCEPTION
3230: WHEN OTHERS THEN
3231: /* Let the master procdure handle exception */
3344: AND r2.directional_flag = NVL(p_direction_code, r2.directional_flag)
3345: );
3346:
3347: BEGIN
3348: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Start ');
3349: -- SAVEPOINT LOAD_PARTY_MARKET_QUALIFIER;
3350:
3351: errbuf := null;
3352: retcode := 0;
3434:
3435: --
3436: COMMIT;
3437: --
3438: Ams_Utility_pvt.Write_Conc_log(' ----- ');
3439: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Committed Buying Groups');
3440: Ams_Utility_pvt.Write_Conc_log(' ----- ');
3441:
3442: IF p_terr_id IS NOT NULL THEN
3435: --
3436: COMMIT;
3437: --
3438: Ams_Utility_pvt.Write_Conc_log(' ----- ');
3439: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Committed Buying Groups');
3440: Ams_Utility_pvt.Write_Conc_log(' ----- ');
3441:
3442: IF p_terr_id IS NOT NULL THEN
3443: generate_party_for_territory(errbuf, retcode, p_terr_id, 'Y', l_bind_vars);
3436: COMMIT;
3437: --
3438: Ams_Utility_pvt.Write_Conc_log(' ----- ');
3439: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Committed Buying Groups');
3440: Ams_Utility_pvt.Write_Conc_log(' ----- ');
3441:
3442: IF p_terr_id IS NOT NULL THEN
3443: generate_party_for_territory(errbuf, retcode, p_terr_id, 'Y', l_bind_vars);
3444: ELSE
3453: END LOOP;
3454: CLOSE c_get_all_territories;
3455: END IF;
3456:
3457: Ams_Utility_pvt.Write_Conc_log;
3458: COMMIT;
3459: EXCEPTION
3460: WHEN OTHERS THEN
3461: -- ROLLBACK TO LOAD_PARTY_MARKET_QUALIFIER;
3459: EXCEPTION
3460: WHEN OTHERS THEN
3461: -- ROLLBACK TO LOAD_PARTY_MARKET_QUALIFIER;
3462: retcode := 1;
3463: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Exception ' || sqlerrm);
3464: Ams_Utility_pvt.Write_Conc_log;
3465: END LOAD_PARTY_MARKET_QUALIFIER;
3466:
3467:
3460: WHEN OTHERS THEN
3461: -- ROLLBACK TO LOAD_PARTY_MARKET_QUALIFIER;
3462: retcode := 1;
3463: Ams_Utility_pvt.Write_Conc_log(l_full_name || ': Exception ' || sqlerrm);
3464: Ams_Utility_pvt.Write_Conc_log;
3465: END LOAD_PARTY_MARKET_QUALIFIER;
3466:
3467:
3468: END AMS_Party_Mkt_Seg_Loader_PVT;