[Home] [Help]
PACKAGE BODY: APPS.WIP_BIS_PERIOD_BAL_PKG
Source
1 PACKAGE BODY WIP_BIS_PERIOD_BAL_PKG as
2 /* $Header: wipbiitb.pls 115.2 2004/04/08 10:18:56 achandak ship $ */
3
4 PROCEDURE Refresh (
5 errbuf IN OUT NOCOPY VARCHAR2,
6 retcode IN OUT NOCOPY VARCHAR2
7 )
8 IS
9 l_wip_schema VARCHAR2(30);
10 BEGIN
11
12 BEGIN
13 select fou.oracle_username into l_wip_schema
14 from FND_PRODUCT_INSTALLATIONS FPI,
15 FND_ORACLE_USERID FOU,
16 FND_APPLICATION FA
17 where fpi.application_id = fa.application_id
18 and fpi.oracle_id = fou.oracle_id
19 and fa.application_short_name = 'WIP';
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN
22 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Could not find schema name for WIP'); -- Remove . for bug 3554853
23 retcode := 'Could not find schema name for WIP'; -- Remove . for bug 3554853
24 errbuf := 'Please make sure WIP schema is correctly installed.';
25 return;
26 WHEN OTHERS THEN
27 null;
28 END;
29
30 execute immediate 'truncate table ' || l_wip_schema || '.WIP_BIS_PERIOD_BALANCE';
31
32 insert /*+ APPEND */ into WIP_BIS_PERIOD_BALANCE
33 ( ORGANIZATION_ID,
34 ORGANIZATION_NAME,
35 LEGAL_ENTITY,
36 SCHEDULE_CLOSE_DATE,
37 CATEGORY_ID,
38 CONCATENATED_SEGMENTS,
39 PERIOD_SET_NAME,
40 PERIOD_NAME,
41 BALANCE
42 )
43 SELECT
44 WDJ.ORGANIZATION_ID ,
45 OOD.ORGANIZATION_NAME ,
46 OOD.LEGAL_ENTITY ,
47 OAP2.SCHEDULE_CLOSE_DATE ,
48 MIC.CATEGORY_ID ,
49 MCKFV.CONCATENATED_SEGMENTS ,
50 OAP2.PERIOD_SET_NAME ,
51 OAP2.PERIOD_NAME ,
52 SUM((NVL(WPB.TL_RESOURCE_IN,0)+ NVL(WPB.TL_OVERHEAD_IN,0) +NVL(WPB.TL_OUTSIDE_PROCESSING_IN,0)
53 + NVL(WPB.PL_MATERIAL_IN,0) +NVL(WPB.PL_RESOURCE_IN,0)+ NVL(WPB.PL_OVERHEAD_IN,0)
54 +NVL(WPB.PL_OUTSIDE_PROCESSING_IN,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_IN,0))
55 - (NVL(WPB.TL_RESOURCE_OUT,0) +NVL(WPB.TL_OVERHEAD_OUT,0)+ NVL(WPB.TL_OUTSIDE_PROCESSING_OUT,0)
56 + NVL(WPB.PL_MATERIAL_OUT,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_OUT,0)+NVL(WPB.PL_RESOURCE_OUT,0)
57 + NVL(WPB.PL_OVERHEAD_OUT,0) +NVL(WPB.PL_OUTSIDE_PROCESSING_OUT,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_OUT,0)
58 + NVL(WPB.TL_MATERIAL_OUT,0))
59 - (NVL(WPB.TL_MATERIAL_VAR,0)+NVL(WPB.TL_RESOURCE_VAR,0)+ NVL(WPB.TL_OVERHEAD_VAR,0)
60 + NVL(WPB.TL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.PL_MATERIAL_VAR,0)+NVL(WPB.PL_RESOURCE_VAR,0)
61 + NVL(WPB.PL_OVERHEAD_VAR,0)+NVL(WPB.PL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_VAR,0)
62 + NVL(WPB.PL_MATERIAL_OVERHEAD_VAR,0)))
63 FROM
64 ORG_ACCT_PERIODS OAP2 ,
65 ORG_ORGANIZATION_DEFINITIONS OOD ,
66 WIP_DISCRETE_JOBS WDJ ,
67 WIP_PERIOD_BALANCES WPB ,
68 ORG_ACCT_PERIODS OAP ,
69 MTL_CATEGORIES_KFV MCKFV ,
70 MTL_ITEM_CATEGORIES MIC ,
71 MTL_DEFAULT_CATEGORY_SETS MDCS
72 WHERE
73 WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
74 AND WPB.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
75 AND WPB.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
76 AND WPB.CLASS_TYPE <> 4
77 AND OAP.ACCT_PERIOD_ID = WPB.ACCT_PERIOD_ID
78 AND OAP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
79 AND OAP2.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
80 AND OAP2.SCHEDULE_CLOSE_DATE >= OAP.SCHEDULE_CLOSE_DATE
81 AND MIC.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
82 AND MIC.INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
83 AND MIC.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID
84 AND MCKFV.CATEGORY_ID = MIC.CATEGORY_ID
85 AND MDCS.FUNCTIONAL_AREA_ID = 1
86 GROUP BY
87 WDJ.ORGANIZATION_ID ,
88 OOD.ORGANIZATION_NAME ,
89 OOD.LEGAL_ENTITY ,
90 OAP2.PERIOD_SET_NAME ,
91 OAP2.PERIOD_NAME ,
92 OAP2.SCHEDULE_CLOSE_DATE ,
93 MIC.CATEGORY_ID,
94 MCKFV.CONCATENATED_SEGMENTS
95 UNION ALL
96 SELECT
97 WRI.ORGANIZATION_ID ,
98 OOD.ORGANIZATION_NAME ,
99 OOD.LEGAL_ENTITY ,
100 OAP2.SCHEDULE_CLOSE_DATE ,
101 MIC.CATEGORY_ID ,
102 MCKFV.CONCATENATED_SEGMENTS ,
103 OAP2.PERIOD_SET_NAME ,
104 OAP2.PERIOD_NAME ,
105 SUM((NVL(WPB.TL_RESOURCE_IN,0)+NVL(WPB.TL_OVERHEAD_IN,0)+ NVL(WPB.TL_OUTSIDE_PROCESSING_IN,0)
106 +NVL(WPB.PL_MATERIAL_IN,0)+ NVL(WPB.PL_RESOURCE_IN,0)+NVL(WPB.PL_OVERHEAD_IN,0)
107 + NVL(WPB.PL_OUTSIDE_PROCESSING_IN,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_IN,0))
108 - (NVL(WPB.TL_RESOURCE_OUT,0)+NVL(WPB.TL_OVERHEAD_OUT,0)+ NVL(WPB.TL_OUTSIDE_PROCESSING_OUT,0)
109 + NVL(WPB.PL_MATERIAL_OUT,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_OUT,0)+ NVL(WPB.PL_RESOURCE_OUT,0)
110 + NVL(WPB.PL_OVERHEAD_OUT,0)+ NVL(WPB.PL_OUTSIDE_PROCESSING_OUT,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_OUT,0)
111 + NVL(WPB.TL_MATERIAL_OUT,0))
112 - (NVL(WPB.TL_MATERIAL_VAR,0)+NVL(WPB.TL_RESOURCE_VAR,0)+ NVL(WPB.TL_OVERHEAD_VAR,0)
113 + NVL(WPB.TL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.PL_MATERIAL_VAR,0)+NVL(WPB.PL_RESOURCE_VAR,0)
114 + NVL(WPB.PL_OVERHEAD_VAR,0)+ NVL(WPB.PL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_VAR,0)
115 + NVL(WPB.PL_MATERIAL_OVERHEAD_VAR,0)) )
116 FROM
117 ORG_ACCT_PERIODS OAP2 ,
118 ORG_ORGANIZATION_DEFINITIONS OOD ,
119 WIP_REPETITIVE_SCHEDULES WRS ,
120 WIP_REPETITIVE_ITEMS WRI ,
121 WIP_PERIOD_BALANCES WPB ,
122 ORG_ACCT_PERIODS OAP ,
123 MTL_CATEGORIES_KFV MCKFV ,
124 MTL_ITEM_CATEGORIES MIC , MTL_DEFAULT_CATEGORY_SETS MDCS
125 WHERE
126 WRI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
127 AND WRS.WIP_ENTITY_ID = WRI.WIP_ENTITY_ID
128 AND WRS.LINE_ID = WRI.LINE_ID
129 AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
130 AND WPB.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
131 AND WPB.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
132 AND WPB.ORGANIZATION_ID = WRI.ORGANIZATION_ID
133 AND OAP.ACCT_PERIOD_ID = WPB.ACCT_PERIOD_ID
134 AND OAP.ORGANIZATION_ID = WRI.ORGANIZATION_ID
135 AND OAP2.ORGANIZATION_ID = WRI.ORGANIZATION_ID
136 AND OAP2.SCHEDULE_CLOSE_DATE >= OAP.SCHEDULE_CLOSE_DATE
137 AND MIC.ORGANIZATION_ID = WRI.ORGANIZATION_ID
138 AND MIC.INVENTORY_ITEM_ID = WRI.PRIMARY_ITEM_ID
139 AND MIC.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID
140 AND MCKFV.CATEGORY_ID = MIC.CATEGORY_ID
141 AND MDCS.FUNCTIONAL_AREA_ID = 1
142 GROUP BY
143 WRI.ORGANIZATION_ID ,
144 OOD.ORGANIZATION_NAME ,
145 OOD.LEGAL_ENTITY ,
146 OAP2.PERIOD_SET_NAME ,
147 OAP2.PERIOD_NAME ,
148 OAP2.SCHEDULE_CLOSE_DATE ,
149 MIC.CATEGORY_ID ,
150 MCKFV.CONCATENATED_SEGMENTS;
151
152 commit;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 retcode := SQLCODE;
157 errbuf := SQLERRM;
158 END Refresh;
159
160 END WIP_BIS_PERIOD_BAL_PKG;