[Home] [Help]
250:
251:
252: -- CLEAR_HISTORY_DATA
253: -- If chosen to regenerate all data and clear history data, deletes all information
254: -- already collected from HXC_RPT_TC_HIST_LOG and HXC_RPT_TC_DETAILS_ALL.
255:
256: PROCEDURE clear_history_data
257: AS
258:
259: BEGIN
260:
261: -- Public Procedure clear_history_data
262: -- Delete from HXC_RPT_TC_HIST_LOG
263: -- Delete from HXC_RPT_TC_DETAILS_ALL
264: -- Commit the changes.
265:
266: IF g_debug
267: THEN
269: END IF;
270:
271: DELETE FROM hxc_rpt_tc_hist_log;
272:
273: DELETE FROM hxc_rpt_tc_details_all;
274:
275: COMMIT;
276:
277: IF g_debug
318: BEGIN
319:
320: -- Public Procedure fetch_history_from_date
321: -- Look into HXC_RPT_TC_HIST_LOG to find out upto which details are
322: -- captured into HXC_RPT_TC_DETAILS_ALL for all the
323: -- resource_id-tc_start_time-tc_stop_time combinations loaded
324: -- right now into HXC_RPT_TC_RESOURCE_TEMP.
325: -- Update these values as history_from_date in HXC_RPT_TC_RESOURCE_TEMP
326: -- for corresponding resource_id-tc_start_time-tc_stop_time
475: bb.date_to,
476: bb.last_update_date,
477: bb.last_updated_by
478: FROM hxc_time_building_blocks bb,
479: hxc_rpt_tc_details_all det,
480: hxc_rpt_tc_resource_temp gt
481: WHERE bb.scope = 'DETAIL'
482: AND bb.time_building_block_id = det.detail_bb_id
483: AND bb.object_version_number = det.detail_bb_ovn
494: BEGIN
495:
496: -- Public Procedure update_last_touched_date
497: -- Used for those detail records that are already collected in
498: -- HXC_RPT_TC_DETAILS_ALL table.
499: -- For those records in HXC_RPT_TC_DETAILS_ALL which belong to
500: -- the timecard records in HXC_RPT_TC_RESOURCE_TEMP, get the
501: -- last_updated_date, last_updated_by, and date_to values
502: -- from HXC_TIME_BUILDING_BLOCKS table, if the date_to column
495:
496: -- Public Procedure update_last_touched_date
497: -- Used for those detail records that are already collected in
498: -- HXC_RPT_TC_DETAILS_ALL table.
499: -- For those records in HXC_RPT_TC_DETAILS_ALL which belong to
500: -- the timecard records in HXC_RPT_TC_RESOURCE_TEMP, get the
501: -- last_updated_date, last_updated_by, and date_to values
502: -- from HXC_TIME_BUILDING_BLOCKS table, if the date_to column
503: -- is different.
501: -- last_updated_date, last_updated_by, and date_to values
502: -- from HXC_TIME_BUILDING_BLOCKS table, if the date_to column
503: -- is different.
504: -- Update all such records with the last_updated_date, last_updated_by
505: -- and date_to columns in HXC_RPT_TC_DETAILS_ALL
506:
507:
508: IF g_debug
509: THEN
529:
530: IF bb_id_tab.COUNT > 0
531: THEN
532: FORALL i IN bb_id_tab.FIRST..bb_id_tab.LAST
533: UPDATE hxc_rpt_tc_details_all
534: SET last_update_date = update_date_tab(i),
535: last_updated_by = update_user_tab(i),
536: last_updated_by_user = NULL,
537: date_to = date_to_tab(i)
552: END update_last_touched_date;
553:
554:
555: -- UPDATE_TIMECARD_COMMENTS
556: -- For all the records recorded in a previous run in HXC_RPT_TC_DETAILS_ALL
557: -- updates the timecard comments, if they were changed.
558:
559:
560: PROCEDURE update_timecard_comments
565: comment_text,
566: detail_bb_id,
567: detail_bb_ovn
568: FROM hxc_time_building_blocks bb,
569: hxc_rpt_tc_details_all det,
570: hxc_rpt_tc_resource_temp gt
571: WHERE bb.time_building_block_id = det.tc_bb_id
572: AND bb.object_version_number = det.tc_bb_ovn
573: AND bb.comment_text IS NOT NULL
580:
581: BEGIN
582:
583: -- Public Procedure update_timecard_comments
584: -- For all the records previously loaded into HXC_RPT_TC_DETAILS_ALL
585: -- query from HXC_TIME_BUILDING_BLOCKS, their relevant timecard
586: -- comments.
587: -- Update HXC_RPT_TC_DETAILS_ALL with the comments picked up above.
588:
583: -- Public Procedure update_timecard_comments
584: -- For all the records previously loaded into HXC_RPT_TC_DETAILS_ALL
585: -- query from HXC_TIME_BUILDING_BLOCKS, their relevant timecard
586: -- comments.
587: -- Update HXC_RPT_TC_DETAILS_ALL with the comments picked up above.
588:
589: IF g_debug
590: THEN
591: hr_utility.trace('Starting update_timecard_comments');
608:
609: IF det_bb_tab.COUNT > 0
610: THEN
611: FORALL i IN det_bb_tab.FIRST..det_bb_tab.LAST
612: UPDATE hxc_rpt_tc_details_all
613: SET tc_comments = comment_tab(i)
614: WHERE detail_bb_id = det_bb_tab(i)
615: AND detail_bb_ovn = det_ovn_tab(i);
616: END IF;
635:
636:
637: -- LOAD_DETAIL_INFO
638: -- Now that all history records are processed, new detail information
639: -- is put into HXC_RPT_TC_DETAILS_ALL table.
640:
641:
642: PROCEDURE load_detail_info ( p_request_sysdate IN DATE)
643: AS
644:
645: BEGIN
646:
647: -- Public Procedure load_detail_info
648: -- Insert into HXC_RPT_TC_DETAILS_ALL detail information
649: -- and day information from HXC_TIME_BUILDING_BLOCKS
650: -- WHERE clauses are placed taking care that data is picked up
651: -- only from the history_from_date already recorded -- meaning
652: -- we need data that is not existing only. Anyways, at this
651: -- only from the history_from_date already recorded -- meaning
652: -- we need data that is not existing only. Anyways, at this
653: -- point, an already existing detail is picked up again, the request
654: -- will error out, as there is a primary key on detail bb id and ovn
655: -- in HXC_RPT_TC_DETAILS_ALL.
656:
657: IF g_debug
658: THEN
659: hr_utility.trace('load_detail_info begins '||p_request_sysdate);
658: THEN
659: hr_utility.trace('load_detail_info begins '||p_request_sysdate);
660: END IF;
661:
662: INSERT INTO hxc_rpt_tc_details_all
663: ( resource_id,
664: tc_start_time,
665: tc_stop_time,
666: tc_bb_id,
730: END load_detail_info;
731:
732:
733: -- POPULATE_ATTRIBUTES
734: -- For all the detail time loaded via this request into HXC_RPT_TC_DETAILS_ALL
735: -- populate the relevant (those visible to the user; no hidden values ) time attributes
736: -- from HXC_TIME_ATTRIBUTES.
737:
738:
799: ';
800: dynamic_cursor_where VARCHAR2(2000);
801: l_dynamic_cursor_where VARCHAR2(2000) := '
802: FROM hxc_rpt_tc_resource_temp gt,
803: hxc_rpt_tc_details_all det,
804: hxc_time_attribute_usages hau,
805: hxc_time_attributes ha
806: WHERE gt.tc_bb_id = det.tc_bb_id
807: AND gt.layout_id = curr_layout_id
837: dynamic_update VARCHAR2(2000);
838: l_dynamic_update VARCHAR2(2000) :=
839: ' IF det_bb_id_tab.COUNT > 0 THEN
840: FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
841: UPDATE hxc_rpt_tc_details_all
842: SET cla_reason = cla_reason_tab(i),
843: cla_comments = cla_comments_tab(i),
844: cla_type = cla_type_tab(i),';
845: dynamic_update_where VARCHAR2(1000);
939: -- MAX(DECODE(ATTRIBUTE_CATEGORY,'REASON',ha.ATTRIBUTE3)),
940: -- MAX(DECODE(ATTRIBUTE_CATEGORY,'APPROVAL',ha.Attribute10))
941: -- ,MAX(DECODE(ATTRIBUTE_CATEGORY,'Dummy Cost Context',ha.Attribute1))
942: -- FROM hxc_rpt_tc_resource_temp gt,
943: -- hxc_rpt_tc_details_all det,
944: -- hxc_time_attribute_usages hau,
945: -- hxc_time_attributes ha
946: -- WHERE gt.tc_bb_id = det.tc_bb_id
947: -- AND gt.layout_id = 7
964: -- display_val3;
965: -- CLOSE get_attributes;
966: -- IF det_bb_id_tab.COUNT > 0 THEN
967: -- FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
968: -- UPDATE hxc_rpt_tc_details_all
969: -- SET cla_reason = cla_reason_tab(i),
970: -- cla_comments = cla_comments_tab(i),
971: -- cla_type = cla_type_tab(i),
972: -- attribute1 = display_val1(i),
1020: -- * l_layout_fld_name
1021: -- For eg. Hours Type
1022: -- * l_layout_fld_rownum
1023: -- The rank in terms of layout_component_id. Plays a key role because
1024: -- this determines which of the 30 Attributes in HXC_RPT_TC_DETAILS_ALL
1025: -- are used for this component.
1026: --
1027: -- Inline comments are put in below for reference.
1028: --
1143: END populate_attributes;
1144:
1145:
1146: -- TRANSLATE_ATTRIBUTES
1147: -- Translates the attributes already loaded into HXC_RPT_TC_DETAILS_ALL table
1148: -- against the queries in HXC_RPT_LAYOUT_COMP_QUERIES
1149:
1150:
1151: PROCEDURE translate_attributes(p_layout_id NUMBER)
1160: l_curr_layout NUMBER(15);
1161:
1162: l_update_predicate VARCHAR2(4000) :=
1163: ' UPDATE /*+ INDEX(hx HXC_RPT_TC_DETAILS_FK2) */
1164: hxc_rpt_tc_details_all hx
1165: SET ';
1166:
1167: l_update_where VARCHAR2(4000) :=
1168: ' WHERE tc_bb_id IN ( SELECT tc_bb_id
1278:
1279: dynamic_where VARCHAR2(2000);
1280: l_dynamic_where VARCHAR2(2000) :=
1281: ' '' ''
1282: FROM hxc_rpt_tc_details_all det,
1283: hxc_rpt_tc_resource_temp gt,
1284: hxc_time_attribute_usages hau,
1285: hxc_time_attributes ha
1286: WHERE gt.tc_bb_id = det.tc_bb_id
1342: CLOSE get_alias_values;
1343: END LOOP;
1344:
1345: FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1346: UPDATE hxc_rpt_tc_details_all
1347: set attributeATTR_COL = ''ALIASNAME: ''||alias_value(i)
1348: WHERE detail_bb_id = det_bb_id_tab(i)
1349: AND detail_bb_ovn = det_bb_ovn_tab(i);
1350: END IF;
1395: -- Public Procedure translate_aliases
1396: -- Like Populate_attributes, this is also very much processing intensive.
1397: -- Creates a dynamic pl/sql block from the sql string bits and pieces,
1398: -- depending on the alias definition ids and executes the same
1399: -- loading the translated alias values into HXC_RPT_TC_DETAILS_ALL.
1400: -- Pasted below is a sample Pl/Sql block created in one of the test runs.
1401:
1402: -- -----------------------------
1403: --
1415: -- detail_bb_ovn,
1416: -- MAX(DECODE(bld_blk_info_type_id,1,LTRIM(ha.ATTRIBUTE_CATEGORY,'ELEMENT - '))),
1417: -- MAX(DECODE(bld_blk_info_type_id,201,ha.ATTRIBUTE1)),
1418: -- ' '
1419: -- FROM hxc_rpt_tc_details_all det,
1420: -- hxc_rpt_tc_resource_temp gt,
1421: -- hxc_time_attribute_usages hau,
1422: -- hxc_time_attributes ha
1423: -- WHERE gt.tc_bb_id = det.tc_bb_id
1460: -- alias_value(i);
1461: -- CLOSE get_alias_values;
1462: -- END LOOP;
1463: -- FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1464: -- UPDATE hxc_rpt_tc_details_all
1465: -- set attribute2 = 'Hours Type : '||alias_value(i)
1466: -- WHERE detail_bb_id = det_bb_id_tab(i)
1467: -- AND detail_bb_ovn = det_bb_ovn_tab(i);
1468: -- END IF;
1647: IS SELECT flv.meaning,
1648: rtrim(substr(flv.lookup_type,5,6),'_A'),
1649: det.detail_bb_id,
1650: det.detail_bb_ovn
1651: FROM hxc_rpt_tc_details_all det,
1652: hxc_rpt_tc_resource_temp gt,
1653: fnd_lookup_values flv
1654: WHERE gt.request_id = det.request_id
1655: AND gt.resource_id = det.resource_id
1671:
1672: BEGIN
1673:
1674: -- Public Procedure translate_cla_reasons
1675: -- Fetch all the lookup codes for CLA reasons from HXC_RPT_TC_DETAILS_ALL
1676: -- wherever they exist.
1677: -- Fetch the corresponding Lookup names from FND_LOOKUP_VALUES.
1678: -- Update HXC_RPT_TC_DETAILS_ALL with the relevant lookup names
1679:
1674: -- Public Procedure translate_cla_reasons
1675: -- Fetch all the lookup codes for CLA reasons from HXC_RPT_TC_DETAILS_ALL
1676: -- wherever they exist.
1677: -- Fetch the corresponding Lookup names from FND_LOOKUP_VALUES.
1678: -- Update HXC_RPT_TC_DETAILS_ALL with the relevant lookup names
1679:
1680: IF g_debug
1681: THEN
1682: hr_utility.trace('translate_cla_reasons');
1699:
1700: IF l_meaning_tab.COUNT > 0
1701: THEN
1702: FORALL i IN l_meaning_tab.FIRST..l_meaning_tab.LAST
1703: UPDATE hxc_rpt_tc_details_all
1704: SET cla_reason = l_meaning_tab(i)
1705: WHERE detail_bb_id = l_det_bb_id_tab(i)
1706: AND detail_bb_ovn = l_det_bb_ovn_tab(i)
1707: AND cla_type = l_type_tab(i) ;
1731:
1732:
1733: -- UPDATE_TRANSACTION_IDS
1734: -- Finds out the relevant transaction_ids and transaction_detail_ids for the
1735: -- chosen detail records from HXC_RPT_TC_DETAILS_ALL.
1736:
1737: PROCEDURE update_transaction_ids(p_record_save IN VARCHAR2)
1738: AS
1739:
1742: htd.transaction_id,
1743: htd.transaction_detail_id,
1744: det.detail_bb_id,
1745: det.detail_bb_ovn
1746: FROM hxc_rpt_tc_details_all det,
1747: hxc_rpt_tc_resource_temp gt,
1748: hxc_transaction_details htd,
1749: hxc_transactions ht
1750: WHERE gt.tc_start_time = det.tc_start_time
1758: AND htd.status = 'SUCCESS';
1759: --
1760: --
1761: -- The following complex cursor pulls out the records from
1762: -- hxc_rpt_tc_details_all, grouped by creation_date.
1763: --
1764: -- After the update above, the records would have transaction_id
1765: -- populated if they belong to a SUBMIT action. The inner query
1766: -- with DENSE_RANK would pull out these records with a
1799: ORDER BY creation_date) dense,
1800: det.resource_id,
1801: det.tc_start_time,
1802: det.tc_stop_time
1803: FROM hxc_rpt_tc_details_all det,
1804: hxc_rpt_tc_resource_temp temp
1805: WHERE temp.resource_id = det.resource_id
1806: AND temp.tc_start_time = det.tc_start_time
1807: AND temp.tc_stop_time = det.tc_stop_time
1830:
1831: BEGIN
1832:
1833: -- Public Procedure update_transaction_ids
1834: -- Joins HXC_RPT_TC_DETAILS_ALL against HXC_TRANSACTION_DETAILS,
1835: -- and HXC_TRANSACTIONS to pick up all the transactions and
1836: -- transaction_details for a successful deposit.
1837: -- Update HXC_RPT_TC_DETAILS_ALL with the corresponding values.
1838: -- If p_record_save is set to Y, we need to give pseudo transaction_ids
1833: -- Public Procedure update_transaction_ids
1834: -- Joins HXC_RPT_TC_DETAILS_ALL against HXC_TRANSACTION_DETAILS,
1835: -- and HXC_TRANSACTIONS to pick up all the transactions and
1836: -- transaction_details for a successful deposit.
1837: -- Update HXC_RPT_TC_DETAILS_ALL with the corresponding values.
1838: -- If p_record_save is set to Y, we need to give pseudo transaction_ids
1839: -- to the working status timecards. Do it in the following way.
1840:
1841: -- Note: This needs to be carried out only for Self Service time entry.
1939:
1940: IF det_bb_id_tab.COUNT > 0
1941: THEN
1942: FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1943: UPDATE hxc_rpt_tc_details_all
1944: SET transaction_id = det_trans_id_tab(i),
1945: transaction_detail_id = det_trans_detail_id_tab(i)
1946: WHERE detail_bb_id = det_bb_id_tab(i)
1947: AND detail_bb_ovn = det_bb_ovn_tab(i);
2028:
2029: IF creation_tab.COUNT > 0
2030: THEN
2031: FORALL i IN creation_tab.FIRST..creation_tab.LAST
2032: UPDATE hxc_rpt_tc_details_all
2033: SET transaction_id = trans_tab(i)+fac_tab(i)
2034: WHERE resource_id = res_id_tab(i)
2035: AND tc_start_time = start_timetab(i)
2036: AND tc_stop_time = stop_timetab(i)
2076: det.detail_bb_ovn,
2077: fnd.user_name||newline||'['||
2078: ppf.full_name||']'
2079: FROM hxc_rpt_tc_resource_temp gt,
2080: hxc_rpt_tc_details_all det,
2081: fnd_user fnd,
2082: per_all_people_f ppf
2083: WHERE gt.tc_bb_id = det.tc_bb_id
2084: AND gt.request_id = p_request_id
2098: -- Public Procedure translate_created_by
2099: -- Find out user_name for the corresponding user_ids from FND_USER
2100: -- and full_name from PER_ALL_PEOPLE_F for the employee_ids from
2101: -- FND_USER.
2102: -- Update HXC_RPT_TC_DETAILS_ALL with the corresponding values.
2103:
2104: IF g_debug
2105: THEN
2106: hr_utility.trace('translate_created_by');
2123:
2124: IF l_bb_id_tab.COUNT > 0
2125: THEN
2126: FORALL i IN l_bb_id_tab.FIRST..l_bb_id_tab.LAST
2127: UPDATE hxc_rpt_tc_details_all
2128: SET created_by_user = l_person_tab(i)
2129: WHERE detail_bb_id = l_bb_id_tab(i)
2130: AND detail_bb_ovn = l_bb_ovn_tab(i);
2131:
2162: det.detail_bb_ovn,
2163: fnd.user_name||newline||'['||
2164: ppf.full_name||']'
2165: FROM hxc_rpt_tc_resource_temp gt,
2166: hxc_rpt_tc_details_all det,
2167: fnd_user fnd,
2168: per_all_people_f ppf
2169: WHERE gt.tc_bb_id = det.tc_bb_id
2170: AND gt.request_id = p_request_id
2183: -- Public Procedure translate_last_updated_by
2184: -- Find out user_name for the corresponding user_ids from FND_USER
2185: -- and full_name from PER_ALL_PEOPLE_F for the employee_ids from
2186: -- FND_USER.
2187: -- Update HXC_RPT_TC_DETAILS_ALL with the corresponding values.
2188:
2189:
2190: IF g_debug
2191: THEN
2212:
2213: IF l_bb_id_tab.COUNT > 0
2214: THEN
2215: FORALL i IN l_bb_id_tab.FIRST..l_bb_id_tab.LAST
2216: UPDATE hxc_rpt_tc_details_all
2217: SET last_updated_by_user = l_person_tab(i)
2218: WHERE detail_bb_id = l_bb_id_tab(i)
2219: AND detail_bb_ovn = l_bb_ovn_tab(i);
2220:
2370: -- Get the request id and sysdates.
2371: -- Pass on the parameters to create the resources list ( comma separated, groups
2372: -- of 20).
2373: -- If chosen to delete history data and recreate, delete all info from
2374: -- HXC_RPT_TC_HIST_LOG and HXC_RPT_TC_DETAILS_ALL.
2375: -- For each valid list of resources picked, execute load_tc_level_info.
2376: -- Execute fetch_history_from_date to get the date from which history has to
2377: -- be considered for each timecard.
2378: -- If chosen to reuse history data, execute update_last_touched_date.
2376: -- Execute fetch_history_from_date to get the date from which history has to
2377: -- be considered for each timecard.
2378: -- If chosen to reuse history data, execute update_last_touched_date.
2379: -- Update the layout_ids for the timecards.
2380: -- Load the detail and day level info to HXC_RPT_TC_DETAILS_ALL.
2381: -- Update the timecard comments.
2382: -- Loop thru all the distinct layout_ids picked out.
2383: -- For each layout id
2384: -- * Populate the attributes for the details
2595: -- Public Procedure insert_queries
2596: -- Not used by Load Timecard Snapshot Request.
2597: -- Used by hxcldvo.lct to load records into HXC_RPT_LAYOUT_COMP_QUERIES
2598: -- Inserts the relevant layout information and column name of
2599: -- HXC_RPT_TC_DETAILS_ALL that carries a given component.
2600:
2601: OPEN get_comp_rank ( p_vo_name);
2602:
2603: FETCH get_comp_rank BULK COLLECT