[Home] [Help]
PACKAGE BODY: APPS.FII_PA_UOM_CONV_F_C
Source
1 Package Body FII_PA_UOM_CONV_F_C AS
2 /* $Header: FIIPA12B.pls 120.1 2002/11/22 20:22:25 svermett ship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5
6 G_PUSH_DATE_RANGE1 Date:=Null;
7 G_PUSH_DATE_RANGE2 Date:=Null;
8 g_row_count Number:=0;
9 g_exception_msg varchar2(2000):=Null;
10
11 Procedure Push(Errbuf in out nocopy Varchar2,
12 Retcode in out nocopy Varchar2,
13 p_from_date IN Varchar2,
14 p_to_date IN Varchar2) IS
15
16 l_fact_name Varchar2(30) :='OPI_EDW_UOM_CONV_FSTG';
17 l_date1 Date:=Null;
18 l_date2 Date:=Null;
19 l_temp_date Date:=Null;
20 l_rows_inserted Number:=0;
21 l_duration Number:=0;
22 l_exception_msg Varchar2(2000):=Null;
23
24 -- -------------------------------------------
25 -- Put any additional developer variables here
26 -- -------------------------------------------
27
28 l_instance_code edw_local_instance.instance_code%type :=
29 edw_instance.get_code;
30 l_from_date date;
31 l_to_date date;
32
33 Begin
34
35 Errbuf :=NULL;
36 Retcode:=0;
37
38 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name)) THEN
39 errbuf := fnd_message.get;
40 raise_application_error(-20000,'Error in SETUP: ' || errbuf);
41 END IF;
42
43 -- Date processing
44
45 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
46 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
47
48 g_push_date_range1 := nvl(l_from_date,
49 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
50
51 g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
52
53 l_date1 := g_push_date_range1;
54 l_date2 := g_push_date_range2;
55
56 if g_debug_flag = 'Y' then
57 edw_log.put_line( 'The collection range is from '||
58 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
59 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
60 edw_log.put_line(' ');
61 end if;
62
63 -- -----------------------------------------------------------------------------
64 -- Start of Collection , Developer Customizable Section
65 -- -----------------------------------------------------------------------------
66
67 if g_debug_flag = 'Y' then
68 edw_log.put_line(' ');
69 edw_log.put_line('Pushing data');
70 end if;
71
72 l_temp_date := sysdate;
73
74 Insert Into OPI_EDW_UOM_CONV_FSTG@EDW_APPS_TO_WH
75 (
76 BASE_UOM,
77 CONVERSION_RATE,
78 EDW_BASE_UOM_FK,
79 EDW_CONVERSION_RATE,
80 EDW_UOM_FK,
81 INSTANCE_FK,
82 INVENTORY_ITEM_ID,
83 UOM,
84 UOM_CONV_PK,
85 USER_ATTRIBUTE1,
86 USER_ATTRIBUTE10,
87 USER_ATTRIBUTE11,
88 USER_ATTRIBUTE12,
89 USER_ATTRIBUTE13,
90 USER_ATTRIBUTE14,
91 USER_ATTRIBUTE15,
92 USER_ATTRIBUTE2,
93 USER_ATTRIBUTE3,
94 USER_ATTRIBUTE4,
95 USER_ATTRIBUTE5,
96 USER_ATTRIBUTE6,
97 USER_ATTRIBUTE7,
98 USER_ATTRIBUTE8,
99 USER_ATTRIBUTE9,
100 USER_FK1,
101 USER_FK2,
102 USER_FK3,
103 USER_FK4,
104 USER_FK5,
105 USER_MEASURE1,
106 USER_MEASURE2,
107 USER_MEASURE3,
108 USER_MEASURE4,
109 USER_MEASURE5,
110 OPERATION_CODE,
111 COLLECTION_STATUS,
112 CLASS_CONVERSION_FLAG
113 )
114 select
115 'PA-' || lookup_code, -- BASE_UOM,
116 1, -- CONVERSION_RATE,
117 null, -- EDW_BASE_UOM_FK,
118 null, -- EDW_CONVERSION_RATE,
119 null, -- EDW_UOM_FK,
120 NVL(l_instance_code,'NA_EDW'), -- INSTANCE_FK,
121 0, -- INVENTORY_ITEM_ID,
122 'PA-' || lookup_code, -- UOM,
123 'STANDARD-PA-' || lookup_code || '-' ||
124 l_instance_code, -- UOM_CONV_PK,
125 null, null, null, null, null, null, null,
126 null, null, null, null, null, null, null, null,
127 'NA_EDW',
128 'NA_EDW',
129 'NA_EDW',
130 'NA_EDW',
131 'NA_EDW',
132 null, null, null, null, null,
133 NULL,
134 'NOT-COLLECTIBLE',
135 'N'
136 from PA_LOOKUPS@APPS_TO_APPS
137 where lookup_type = 'UNIT'
138 and last_update_date between l_date1 and l_date2;
139
140 l_rows_inserted := nvl(sql%rowcount,0);
141 l_duration := sysdate - l_temp_date;
142
143 if g_debug_flag = 'Y' then
144 edw_log.put_line('Inserted ' || to_char(l_rows_inserted) ||
145 ' rows into the staging table');
146 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
147 edw_log.put_line(' ');
148 end if;
149
150 -- ---------------------------------------------------------------------------
151 -- END OF Collection , Developer Customizable Section
152 -- ---------------------------------------------------------------------------
153
154 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2);
155
156 Exception When others then
157
158 Errbuf:=sqlerrm;
159 Retcode:=sqlcode;
160 l_exception_msg := Retcode || ':' || Errbuf;
161 rollback;
162 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_date_range1, g_push_date_range2);
163 raise;
164 End;
165
166 End FII_PA_UOM_CONV_F_C;