[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_PRE_R12_RES_PKG
Source
1 PACKAGE BODY opi_dbi_pre_r12_res_pkg AS
2 /* $Header: OPIDREOB.pls 120.3 2006/04/28 13:52:49 julzhang noship $ */
3
4
5 /*=============================================
6 Package level Constants
7 =============================================*/
8 g_ok CONSTANT NUMBER(1) := 0;
9 g_warning CONSTANT NUMBER(1) := 1;
10 g_error CONSTANT NUMBER(1) := -1;
11
12 PRE_R12_OPM_SOURCE CONSTANT NUMBER := 3;
13
14
15 /*======================================================
16 This procedure extracts actual resource usage data
17 from the Pre-R12 data model into the staging table for
18 initial load. It is only called when the global start
19 date is before the R12 migration date.
20
21 Parameters:
22 - errbuf: error buffer
23 - retcode: return code
24 =======================================================*/
25 PROCEDURE pre_r12_opm_res_actual (errbuf IN OUT NOCOPY VARCHAR2,
26 retcode IN OUT NOCOPY VARCHAR2) IS
27
28 l_rowcount NUMBER;
29 g_hr_uom sy_uoms_mst.um_code%TYPE;
30
31 BEGIN
32
33 retcode := 0;
34 g_hr_uom := fnd_profile.value( 'BOM:HOUR_UOM_CODE');
35
36 bis_collection_utilities.put_line('Enter pre_r12_opm_res_actual() ' ||
37 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
38
39 INSERT /*+ append */ INTO opi_dbi_res_actual_stg (
40 resource_id,
41 organization_id,
42 transaction_date,
43 uom,
44 actual_qty_draft,
45 actual_qty,
46 actual_qty_g_draft,
47 actual_qty_g,
48 actual_val_b_draft,
49 actual_val_b,
50 source,
51 job_id,
52 job_type,
53 assembly_item_id,
54 department_id)
55 SELECT /*+ ORDERED */
56 rdtl.resource_id resource_id,
57 rdtl.organization_id organization_id,
58 TRUNC(rtran.trans_date) transaction_date,
59 rtran.trans_um uom,
60 0 actual_qty_draft,
61 sum(rtran.resource_usage * prod.cost_alloc) actual_qty,
62 0 actual_qty_g_draft,
63 sum(rtran.resource_usage * prod.cost_alloc * hruom.std_factor / ruom.std_factor) actual_qtg_g,
64 0 actual_val_b_draft,
65 sum(led.amount_base * led.debit_credit_sign) actual_val_b,
66 PRE_R12_opm_source source,
67 rtran.doc_id job_id,
68 4 job_type,
69 prod.inventory_item_id assembly_item_id,
70 rmst.resource_class department_id
71 FROM sy_uoms_mst hruom,
72 sy_uoms_mst ruom,
73 gme_resource_txns rtran,
74 cr_rsrc_dtl rdtl,
75 cr_rsrc_mst_b rmst,
76 gme_material_details prod,
77 gl_subr_led led
78 WHERE hruom.um_code = g_hr_uom
79 AND ruom.um_code = rtran.trans_um
80 AND rtran.completed_ind = 1
81 AND rdtl.orgn_code = rtran.orgn_code
82 AND rdtl.resources = rtran.resources
83 AND rmst.resources = rdtl.resources
84 AND prod.batch_id = rtran.doc_id
85 AND prod.line_type = 1
86 AND rtran.doc_id = led.doc_id -- new
87 AND rtran.line_id = led.line_id -- new
88 AND rtran.doc_type = led.doc_type -- new
89 AND rtran.trans_date = led.gl_trans_date --new
90 AND led.acct_ttl_type = 1530 --new (WIP)
91 AND led.sub_event_type = 50050 -- new (resource step ceritification)
92 GROUP BY
93 prod.inventory_item_id,
94 rtran.doc_id,
95 rdtl.resource_id,
96 rmst.resource_class,
97 rdtl.organization_id,
98 TRUNC(rtran.trans_date),
99 rtran.trans_um;
100
101 COMMIT;
102
103
104 l_rowcount := sql%rowcount;
105
106 bis_collection_utilities.put_line('From Pre R12 Data Model - OPM Resource Actual: ' ||
107 to_char(l_rowcount) || ' rows initially collected into staging table at '||
108 to_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
109
110 bis_collection_utilities.put_line('Exit pre_r12_opm_res_actual() ' ||
111 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
112 EXCEPTION WHEN OTHERS THEN
113 --{
114 errbuf:= Sqlerrm;
115 retcode:= SQLCODE;
116
117 ROLLBACK;
118
119 bis_collection_utilities.put_line('Exception in pre_r12_opm_res_actual ' || errbuf );
120 --}
121 END pre_r12_opm_res_actual;
122
123
124 END opi_dbi_pre_r12_res_pkg;