[Home] [Help]
4: -- Declaring global variables.
5: --
6: g_session_id NUMBER;
7: g_transaction_id NUMBER;
8: g_debug boolean := hr_utility.debug_enabled;
9: g_package varchar2(72) := 'pqh_ss_print';
10: g_effective_date DATE;
11:
12: --
262: -- loop thru the params table and set the value
263: FOR i IN NVL(params_table.FIRST,0)..NVL(params_table.LAST,-1) LOOP
264: IF UPPER(params_table(i).param_name) = UPPER(where_clause_param_values.column_name) THEN
265: params_table(i).param_value := where_clause_param_values.column_value;
266: --hr_utility.set_location('Name ' || params_table(i).param_name || ' value ' || params_table(i).param_value,8);
267: EXIT;
268: END IF;
269: END LOOP;
270: END IF;
949: l_reason varchar2(100):=null;
950: l_proc VARCHAR2(72):= 'get_change_reason';
951: --
952: BEGIN
953: hr_utility.set_location('Leaving: ' || l_proc,10);
954: --
955: open csr_change_reason ;
956: FETCH csr_change_reason into l_reason;
957: close csr_change_reason;
955: open csr_change_reason ;
956: FETCH csr_change_reason into l_reason;
957: close csr_change_reason;
958: --
959: hr_utility.set_location('Leaving: ' || l_proc,20);
960:
961: return l_reason;
962: END get_change_reason;
963: --
972: Begin
973: --
974: l_proc := g_package || 'purge_temp_data';
975:
976: hr_utility.set_location('Leaving: ' || l_proc,10);
977:
978: Delete
979: from pqh_ss_print_label_temp
980: where trunc(creation_date) < p_effective_date;
979: from pqh_ss_print_label_temp
980: where trunc(creation_date) < p_effective_date;
981: -- Issuing autonomous commit
982: COMMIT;
983: hr_utility.set_location('Leaving: ' || l_proc,20);
984: --
985: EXCEPTION
986: WHEN OTHERS THEN ROLLBACK;
987: --
996: l_col_val VARCHAR2(8000) := null;
997: l_col_type VARCHAR2(1) := null;
998: BEGIN
999:
1000: hr_utility.set_location('Entering:'||l_proc, 5);
1001: hr_utility.set_location('Col Name : '||p_column_name, 6);
1002:
1003: IF NVL(params_table.COUNT,0) <> 0 THEN
1004: -- loop thru the params table and get the value
997: l_col_type VARCHAR2(1) := null;
998: BEGIN
999:
1000: hr_utility.set_location('Entering:'||l_proc, 5);
1001: hr_utility.set_location('Col Name : '||p_column_name, 6);
1002:
1003: IF NVL(params_table.COUNT,0) <> 0 THEN
1004: -- loop thru the params table and get the value
1005: FOR i IN NVL(params_table.FIRST,0)..NVL(params_table.LAST,-1) LOOP
1010: l_col_val := ' fnd_date.canonical_to_date(''' || l_col_val || ''')';
1011: ELSIF l_col_type = 'V' THEN
1012: l_col_val := '''' || l_col_val || '''';
1013: END IF;
1014: hr_utility.set_location('Name ' || p_column_name || ' value ' || l_col_val,8);
1015: EXIT;
1016: -- exit the loop as the column is found
1017: END IF;
1018: END LOOP;
1031: l_key_val VARCHAR2(8000);
1032: l_where_out pqh_table_route.where_clause%TYPE;
1033: l_atom_length number;
1034: BEGIN
1035: hr_utility.set_location('Entering:'||l_proc, 5);
1036: PQH_REFRESH_DATA.parse_string(
1037: p_string_in => p_where_clause_in,
1038: p_atomics_list_out => l_atoms_tab,
1039: p_num_atomics_out => l_no_atoms
1044: l_atom_length := LENGTH(TRIM(l_atoms_tab(table_row)));
1045: IF substr(NVL (l_atoms_tab(table_row), 'NULL') ,1,1) = '<' and l_atom_length > 2 THEN
1046: l_key_column := substr(l_atoms_tab(table_row),2,(l_atom_length - 2)) ;
1047: l_key_val := get_value_from_params_table(p_column_name => l_key_column);
1048: hr_utility.set_location(l_key_column||' = '||l_key_val,15);
1049: -- if value is null pass it null
1050: -- replace the param with the actual value
1051: l_atoms_tab(table_row) := l_key_val;
1052: END IF;
1076: p_group_per_in_ler_id in number,
1077: p_group_plan_id in number, p_lf_evt_ocrd_dt
1078: in date)is
1079: begin
1080: hr_utility.set_location('Entering populate_cwb_params',1);
1081: --Setting the where clause params in params table
1082: params_table(1).param_name := 'P_GROUP_PER_IN_LER_ID';
1083: params_table(1).param_value := p_group_per_in_ler_id;
1084: params_table(1).param_data_type := 'N';
1089:
1090: params_table(3).param_name := 'P_LIFE_EVENT_DATE';
1091: params_table(3).param_value := p_lf_evt_ocrd_dt;
1092: params_table(3).param_data_type := 'V';
1093: hr_utility.set_location('Leaving populate_cwb_params',2);
1094: end populate_cwb_params;
1095:
1096: /*----------------------------------------------------------------------------------
1097: POPULATE_DATA_FROM_FF: Common routine to execute the fast formula defined for the
1236: AND att.master_table_route_id=p_table_route_id
1237: order by att.attribute_id;
1238:
1239: begin
1240: hr_utility.set_location('Entering populate_data_from_query',1);
1241: -- populate_params_table(p_Transaction_Id ,p_effective_date );
1242: --Loop starts for each table_route
1243: For table_route_rslt IN table_routes LOOP
1244: -- columns are retrieved corresponding to a document tags for the table_route_id
1241: -- populate_params_table(p_Transaction_Id ,p_effective_date );
1242: --Loop starts for each table_route
1243: For table_route_rslt IN table_routes LOOP
1244: -- columns are retrieved corresponding to a document tags for the table_route_id
1245: hr_utility.set_location('Table Route Id'||table_route_rslt.table_rt_id,11);
1246: For column_name_r IN column_tag_names(table_route_rslt.table_rt_id) Loop
1247: IF l_no_cols > 0 THEN
1248: columnval:=columnval||',' ;
1249: columntag:=columntag||',' ;
1291: columntag := null;
1292: sql_query := null;
1293: l_statement := null;
1294: l_where_clause_out := null;
1295: hr_utility.set_location('Query execution generated Error: '||sqlerrm,10);
1296:
1297: End;
1298: END Loop;
1299: --
1296:
1297: End;
1298: END Loop;
1299: --
1300: hr_utility.set_location('Leaving populate_data_from_query',1);
1301: end;
1302:
1303:
1304: /* ----------------------------------------------------------------------------------
1331: BEGIN
1332: --
1333: -- Create a save point
1334: --
1335: hr_utility.set_location('Entering: ' || l_proc,10);
1336: SAVEPOINT POPULATE_TEMP_DATA;
1337: g_effective_date := p_effective_date;
1338: g_transaction_id := p_transaction_id;
1339:
1349: l_pdf_short_name := p_doc_short_name;
1350:
1351:
1352:
1353: hr_utility.set_location('Effective Date' || p_effective_date,12);
1354: hr_utility.set_location('Document Short Name' || l_pdf_short_name,12);
1355:
1356: select userenv('sessionid') into p_session_id from dual;
1357:
1350:
1351:
1352:
1353: hr_utility.set_location('Effective Date' || p_effective_date,12);
1354: hr_utility.set_location('Document Short Name' || l_pdf_short_name,12);
1355:
1356: select userenv('sessionid') into p_session_id from dual;
1357:
1358:
1360:
1361:
1362: IF l_approval_type = 'PRE' or l_approval_type = 'N/A' THEN
1363:
1364: hr_utility.set_location('Pre approval type data insertion',15);
1365: --
1366: -- Retrieve data from transaction tables
1367: --
1368: BEGIN
1398: AND att.enable_flag='Y'
1399: AND att.master_table_route_id=ptr.table_route_id
1400: AND att.COLUMN_NAME = atv.name
1401: AND ptr.from_clause=steps.api_name;
1402: hr_utility.set_location('Data Inserted',18);
1403: Exception
1404: when others then
1405: null;
1406: End;
1410: ELSIF l_approval_type = 'POST' THEN
1411: --
1412: -- Retrieve data from History Tables
1413: --
1414: hr_utility.set_location('Post approval type data insertion',15);
1415: BEGIN
1416: INSERT INTO
1417: pqh_ss_print_data(session_id,transaction_id,name,value,enable_flag)
1418: SELECT userenv('sessionid'),p_transaction_id,
1448: AND att.enable_flag='Y'
1449: AND att.master_table_route_id =ptr.table_route_id
1450: AND att.COLUMN_NAME = atv.name
1451: AND ptr.from_clause=steps.api_name ;
1452: hr_utility.set_location('Data Inserted',18);
1453: Exception
1454: when others then
1455: null;
1456: End;
1482: --
1483: -- Check is there any record in pqh_ss_print_data , if not insert one dummy row , so that
1484: -- while opening pdf file , xdo will not error out .
1485: --
1486: hr_utility.set_location('Leaving: '|| l_proc ,20);
1487: EXCEPTION
1488:
1489: WHEN DUP_VAL_ON_INDEX THEN
1490: NULL;
1489: WHEN DUP_VAL_ON_INDEX THEN
1490: NULL;
1491: WHEN OTHERS THEN
1492: NULL;
1493: hr_utility.set_location('Leaving with Other exception '|| l_proc ,20);
1494: --
1495: End populate_temp_data;
1496:
1497: /* ----------------------------------------------------------------------------------