[Home] [Help]
2: /* $Header: hxcgnret.pkb 120.22.12010000.2 2008/08/05 12:02:39 ubhat 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),
2233: -- chk_retrieval_process
2234: --
2235: -- description
2236: -- This checks that the retrieval process passed in P_PROCESS exists
2237: -- in hxc retrieval_processes. If it does exists it returns the
2238: -- mapping id associated with the process. The mapping id is tested
2239: -- for null to determine if the processes exists
2240: --
2241: -- parameters
2243: -- p_retrieval_process_id - retrieval process id (OUT only)
2244: -- p_retrieval_tr_id - retrieval time recipient id (OUT only)
2245: -- p_mapping_id - mapping id (OUT only)
2246: PROCEDURE chk_retrieval_process (
2247: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
2248: p_retrieval_process_id IN OUT NOCOPY hxc_retrieval_processes.retrieval_process_id%TYPE,
2249: p_retrieval_tr_id IN OUT NOCOPY hxc_retrieval_processes.time_recipient_id%TYPE,
2250: p_mapping_id IN OUT NOCOPY hxc_mappings.mapping_id%TYPE
2251: )
2244: -- p_retrieval_tr_id - retrieval time recipient id (OUT only)
2245: -- p_mapping_id - mapping id (OUT only)
2246: PROCEDURE chk_retrieval_process (
2247: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
2248: p_retrieval_process_id IN OUT NOCOPY hxc_retrieval_processes.retrieval_process_id%TYPE,
2249: p_retrieval_tr_id IN OUT NOCOPY hxc_retrieval_processes.time_recipient_id%TYPE,
2250: p_mapping_id IN OUT NOCOPY hxc_mappings.mapping_id%TYPE
2251: )
2252: IS
2245: -- p_mapping_id - mapping id (OUT only)
2246: PROCEDURE chk_retrieval_process (
2247: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
2248: p_retrieval_process_id IN OUT NOCOPY hxc_retrieval_processes.retrieval_process_id%TYPE,
2249: p_retrieval_tr_id IN OUT NOCOPY hxc_retrieval_processes.time_recipient_id%TYPE,
2250: p_mapping_id IN OUT NOCOPY hxc_mappings.mapping_id%TYPE
2251: )
2252: IS
2253: --
2256: --
2257: CURSOR csr_get_otm_mapping
2258: IS
2259: SELECT -1, rtr.time_recipient_id, rtr.mapping_id
2260: FROM hxc_retrieval_processes rtr
2261: WHERE rtr.NAME = 'BEE Retrieval Process';
2262:
2263: CURSOR csr_chk_retrieval
2264: IS
2263: CURSOR csr_chk_retrieval
2264: IS
2265: SELECT rtr.retrieval_process_id, rtr.time_recipient_id,
2266: rtr.mapping_id
2267: FROM hxc_retrieval_processes rtr
2268: WHERE rtr.NAME = p_retrieval_process;
2269:
2270: --
2271: l_mapping_id NUMBER (15) := NULL;
6048: --
6049: --
6050: --
6051: -- parameters
6052: -- p_process - name of the process registered in hxc_retrieval_processes
6053: -- p_transaction_code - user defined transaction code used for rerun
6054: -- p_start_date - start date of the retrieval window
6055: -- p_end_date - end date of the retrieval window
6056: -- p_incremental - incremental (Y/N)
6061: -- (i.e. INclude or EXClude bld blks which do not completely
6062: -- fit in the date window)
6063: --
6064: PROCEDURE execute_retrieval_process (
6065: p_process IN hxc_retrieval_processes.NAME%TYPE,
6066: p_transaction_code IN VARCHAR2,
6067: p_start_date IN DATE DEFAULT NULL,
6068: p_end_date IN DATE DEFAULT NULL,
6069: p_incremental IN VARCHAR2 DEFAULT 'Y',
6097: l_since_date VARCHAR2 (100);
6098: l_proc VARCHAR2 (72)
6099: := g_package || 'execute_retrieval_process';
6100: l_mapping_id hxc_mappings.mapping_id%TYPE := NULL;
6101: l_ret_id hxc_retrieval_processes.retrieval_process_id%TYPE;
6102: e_retrieval_not_registered EXCEPTION;
6103: e_process_already_running EXCEPTION;
6104: l_where_clause_blk VARCHAR2 (2000);
6105: l_where_clause_att VARCHAR2 (2000);
7007: -- Parameters
7008: -- p_retrieval_process : The Retrieval Process Name
7009: -- p_retrieval_tr_id : The time recipient id of the application.
7010: FUNCTION get_valid_app_sets (
7011: p_retrieval_process hxc_retrieval_processes.NAME%TYPE,
7012: p_retrieval_tr_id NUMBER
7013: )
7014: RETURN VARCHAR2
7015: IS
7366: IF (g_params.p_process = 'Apply Schedule Rules')
7367: THEN
7368: SELECT rp.retrieval_process_id
7369: INTO l_ret_id
7370: FROM hxc_retrieval_processes rp
7371: WHERE rp.NAME = 'BEE Retrieval Process';
7372:
7373: -- check to see that the last retrieval completed normally
7374: hxc_generic_retrieval_utils.RECOVERY
7815: -- p_status - the status of the overall retrieval
7816: -- p_exception_description - exception description
7817: -- p_rollback - is the retrieval being rolled back
7818: PROCEDURE update_transaction_status (
7819: p_process hxc_retrieval_processes.NAME%TYPE,
7820: p_status hxc_transactions.status%TYPE,
7821: p_exception_description hxc_transactions.exception_description%TYPE,
7822: p_rollback BOOLEAN DEFAULT FALSE
7823: )
7822: p_rollback BOOLEAN DEFAULT FALSE
7823: )
7824: IS
7825: -- going to call the chk_retrieval_process procedure - do not need mapping id
7826: l_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
7827: l_mapping_id hxc_mappings.mapping_id%TYPE;
7828: l_tx_id hxc_transactions.transaction_id%TYPE;
7829: l_proc VARCHAR2 (72);
7830: l_time_max INTEGER;