[Home] [Help]
2: /* $Header: hxcgnret.pkb 120.48.12020000.7 2013/03/27 08:37:00 asrajago ship $ */
3:
4: -- global package data type and variables
5: TYPE r_parameters IS RECORD (
6: p_process hxc_retrieval_processes.NAME%TYPE,
7: p_transaction_code hxc_transactions.transaction_code%TYPE,
8: p_start_date DATE,
9: p_end_date DATE,
10: p_incremental VARCHAR2 (1),
613: -- Bug 8888911
614: -- Cursor to pick up all application sets for this process.
615: CURSOR get_app_sets ( p_process VARCHAR2)
616: IS SELECT has.application_set_id
617: FROM hxc_retrieval_processes hrp,
618: hxc_application_set_comps_v has
619: WHERE hrp.name = DECODE(p_process,
620: 'Apply Schedule Rules','BEE Retrieval Process',
621: p_process)
2863: -- chk_retrieval_process
2864: --
2865: -- description
2866: -- This checks that the retrieval process passed in P_PROCESS exists
2867: -- in hxc retrieval_processes. If it does exists it returns the
2868: -- mapping id associated with the process. The mapping id is tested
2869: -- for null to determine if the processes exists
2870: --
2871: -- parameters
2873: -- p_retrieval_process_id - retrieval process id (OUT only)
2874: -- p_retrieval_tr_id - retrieval time recipient id (OUT only)
2875: -- p_mapping_id - mapping id (OUT only)
2876: PROCEDURE chk_retrieval_process (
2877: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
2878: p_retrieval_process_id IN OUT NOCOPY hxc_retrieval_processes.retrieval_process_id%TYPE,
2879: p_retrieval_tr_id IN OUT NOCOPY hxc_retrieval_processes.time_recipient_id%TYPE,
2880: p_mapping_id IN OUT NOCOPY hxc_mappings.mapping_id%TYPE
2881: )
2874: -- p_retrieval_tr_id - retrieval time recipient id (OUT only)
2875: -- p_mapping_id - mapping id (OUT only)
2876: PROCEDURE chk_retrieval_process (
2877: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
2878: p_retrieval_process_id IN OUT NOCOPY hxc_retrieval_processes.retrieval_process_id%TYPE,
2879: p_retrieval_tr_id IN OUT NOCOPY hxc_retrieval_processes.time_recipient_id%TYPE,
2880: p_mapping_id IN OUT NOCOPY hxc_mappings.mapping_id%TYPE
2881: )
2882: IS
2875: -- p_mapping_id - mapping id (OUT only)
2876: PROCEDURE chk_retrieval_process (
2877: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
2878: p_retrieval_process_id IN OUT NOCOPY hxc_retrieval_processes.retrieval_process_id%TYPE,
2879: p_retrieval_tr_id IN OUT NOCOPY hxc_retrieval_processes.time_recipient_id%TYPE,
2880: p_mapping_id IN OUT NOCOPY hxc_mappings.mapping_id%TYPE
2881: )
2882: IS
2883: --
2886: --
2887: CURSOR csr_get_otm_mapping
2888: IS
2889: SELECT -1, rtr.time_recipient_id, rtr.mapping_id
2890: FROM hxc_retrieval_processes rtr
2891: WHERE rtr.NAME = 'BEE Retrieval Process';
2892:
2893: CURSOR csr_chk_retrieval
2894: IS
2893: CURSOR csr_chk_retrieval
2894: IS
2895: SELECT rtr.retrieval_process_id, rtr.time_recipient_id,
2896: rtr.mapping_id
2897: FROM hxc_retrieval_processes rtr
2898: WHERE rtr.NAME = p_retrieval_process;
2899:
2900: --
2901: l_mapping_id NUMBER (15) := NULL;
6783: --
6784: --
6785: --
6786: -- parameters
6787: -- p_process - name of the process registered in hxc_retrieval_processes
6788: -- p_transaction_code - user defined transaction code used for rerun
6789: -- p_start_date - start date of the retrieval window
6790: -- p_end_date - end date of the retrieval window
6791: -- p_incremental - incremental (Y/N)
6796: -- (i.e. INclude or EXClude bld blks which do not completely
6797: -- fit in the date window)
6798: --
6799: PROCEDURE execute_retrieval_process (
6800: p_process IN hxc_retrieval_processes.NAME%TYPE,
6801: p_transaction_code IN VARCHAR2,
6802: p_start_date IN DATE DEFAULT NULL,
6803: p_end_date IN DATE DEFAULT NULL,
6804: p_incremental IN VARCHAR2 DEFAULT 'Y',
6832: l_since_date VARCHAR2 (100);
6833: l_proc VARCHAR2 (72)
6834: := g_package || 'execute_retrieval_process';
6835: l_mapping_id hxc_mappings.mapping_id%TYPE := NULL;
6836: l_ret_id hxc_retrieval_processes.retrieval_process_id%TYPE;
6837: e_retrieval_not_registered EXCEPTION;
6838: e_process_already_running EXCEPTION;
6839: l_where_clause_blk VARCHAR2 (2000);
6840: l_where_clause_att VARCHAR2 (2000);
7905: -- Parameters
7906: -- p_retrieval_process : The Retrieval Process Name
7907: -- p_retrieval_tr_id : The time recipient id of the application.
7908: FUNCTION get_valid_app_sets (
7909: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
7910: p_retrieval_tr_id NUMBER
7911: )
7912: RETURN VARCHAR2
7913: IS
8297: IF (g_params.p_process = 'Apply Schedule Rules')
8298: THEN
8299: SELECT rp.retrieval_process_id
8300: INTO l_ret_id
8301: FROM hxc_retrieval_processes rp
8302: WHERE rp.NAME = 'BEE Retrieval Process';
8303:
8304: -- check to see that the last retrieval completed normally
8305: hxc_generic_retrieval_utils.RECOVERY
8920: -- p_status - the status of the overall retrieval
8921: -- p_exception_description - exception description
8922: -- p_rollback - is the retrieval being rolled back
8923: PROCEDURE update_transaction_status (
8924: p_process hxc_retrieval_processes.NAME%TYPE,
8925: p_status hxc_transactions.status%TYPE,
8926: p_exception_description hxc_transactions.exception_description%TYPE,
8927: p_rollback BOOLEAN DEFAULT FALSE
8928: )
8927: p_rollback BOOLEAN DEFAULT FALSE
8928: )
8929: IS
8930: -- going to call the chk_retrieval_process procedure - do not need mapping id
8931: l_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
8932: l_mapping_id hxc_mappings.mapping_id%TYPE;
8933: l_tx_id hxc_transactions.transaction_id%TYPE;
8934: l_proc VARCHAR2 (72);
8935: l_time_max INTEGER;