[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRAIVA_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRAIVA_XMLP_PKG AS
2 /* $Header: CSTRAIVAB.pls 120.0.12020000.2 2012/07/11 13:13:29 vkatakam ship $ */
3 FUNCTION CF_ORDERFORMULA(CATEGORY IN VARCHAR2) RETURN CHAR IS
4 BEGIN
5 IF (P_SORT_OPTION = 1) THEN
6 RETURN NULL;
7 ELSE
8 RETURN CATEGORY;
9 END IF;
10 END CF_ORDERFORMULA;
11 FUNCTION CF_TOTQTYFORMULA(STKQTY IN NUMBER
12 ,RCVQTY IN NUMBER
13 ,INTQTY IN NUMBER) RETURN NUMBER IS
14 BEGIN
15 RETURN STKQTY + RCVQTY + INTQTY;
16 END CF_TOTQTYFORMULA;
17 FUNCTION CF_TOTVALFORMULA(STKVAL IN NUMBER
18 ,RCVVAL IN NUMBER
19 ,INTVAL IN NUMBER) RETURN NUMBER IS
20 BEGIN
21 RETURN STKVAL + RCVVAL + INTVAL;
22 END CF_TOTVALFORMULA;
23 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
24 L_FCN_CURRENCY VARCHAR2(15);
25 L_STMT_NUM NUMBER;
26 L_MSG_COUNT NUMBER;
27 L_MSG_DATA VARCHAR2(8000);
28 L_RETURN_STATUS VARCHAR2(1);
29 L_AS_OF_DATE VARCHAR2(30);
30 L_CST_INV_VAL EXCEPTION;
31 BEGIN
32 L_STMT_NUM := 0;
33 PV_EXCHANGE_RATE := FND_NUMBER.CANONICAL_TO_NUMBER(P_EXCHANGE_RATE_CHAR);
34 L_STMT_NUM := 1;
35
36 /*BUG14096363*/
37 IF P_SORT_OPTION = 1 THEN
38 p_order_by := ' order by cost_group,ITEM,category';
39 ELSE
40 p_order_by := ' order by cost_group,category,ITEM';
41 END IF;
42 /*BUG14096363*/
43
44 IF P_VIEW_COST <> 1 THEN
45 FND_MESSAGE.SET_NAME('null'
46 ,'null');
47 /*SRW.USER_EXIT('FND MESSAGE_DISPLAY')*/NULL;
48 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
49 END IF;
50 L_STMT_NUM := 5;
51 SELECT
52 PRIMARY_COST_METHOD
53 INTO P_COST_METHOD
54 FROM
55 MTL_PARAMETERS
56 WHERE ORGANIZATION_ID = P_ORG_ID;
57 L_STMT_NUM := 20;
58 SELECT
59 OOD.ORGANIZATION_NAME,
60 SOB.CURRENCY_CODE,
61 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
62 ,POWER(10
63 ,NVL(-FC.PRECISION
64 ,0))),
65 MCS.CATEGORY_SET_NAME,
66 LOOKUP1.MEANING,
67 LOOKUP2.MEANING,
68 LOOKUP3.MEANING,
69 LOOKUP4.MEANING,
70 LOOKUP5.MEANING,
71 CCT.COST_TYPE
72 INTO PV_ORGANIZATION_NAME,L_FCN_CURRENCY,PV_ROUND_UNIT,PV_CATEGORY_SET_NAME,PV_SORT_OPTION,PV_COST_GROUP_OPTION,PV_ZERO_COST,PV_EXP_ITEM,PV_EXP_SUBINV,P_COST_TYPE
73 FROM
74 ORG_ORGANIZATION_DEFINITIONS OOD,
75 CST_COST_TYPES CCT,
76 GL_SETS_OF_BOOKS SOB,
77 FND_CURRENCIES FC,
78 MTL_CATEGORY_SETS MCS,
79 MFG_LOOKUPS LOOKUP1,
80 MFG_LOOKUPS LOOKUP2,
81 MFG_LOOKUPS LOOKUP3,
82 MFG_LOOKUPS LOOKUP4,
83 MFG_LOOKUPS LOOKUP5
84 WHERE OOD.ORGANIZATION_ID = P_ORG_ID
85 AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
86 AND FC.CURRENCY_CODE = P_CURRENCY_CODE
87 AND MCS.CATEGORY_SET_ID = P_CATEGORY_SET
88 AND CCT.COST_TYPE_ID = P_COST_TYPE_ID
89 AND LOOKUP1.LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
90 AND LOOKUP1.LOOKUP_CODE = P_SORT_OPTION
91 AND LOOKUP2.LOOKUP_TYPE = 'CST_SRS_COST_GROUP_OPTION'
92 AND LOOKUP2.LOOKUP_CODE = P_COST_GROUP_OPTION_ID
93 AND LOOKUP3.LOOKUP_TYPE = 'SYS_YES_NO'
94 AND LOOKUP3.LOOKUP_CODE = P_ZERO_COST
95 AND LOOKUP4.LOOKUP_TYPE = 'SYS_YES_NO'
96 AND LOOKUP4.LOOKUP_CODE = P_EXP_ITEM
97 AND LOOKUP5.LOOKUP_TYPE = 'SYS_YES_NO'
98 AND LOOKUP5.LOOKUP_CODE = P_EXP_SUBINV;
99 L_STMT_NUM := 50;
100 IF L_FCN_CURRENCY = P_CURRENCY_CODE THEN
101 PV_CURRENCY_CODE := P_CURRENCY_CODE;
102 ELSE
103 PV_CURRENCY_CODE := P_CURRENCY_CODE || ' @ ' || TO_CHAR(ROUND(1 / PV_EXCHANGE_RATE
104 ,5)) || ' ' || L_FCN_CURRENCY;
105 END IF;
106 L_STMT_NUM := 60;
107 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
108 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
109 L_STMT_NUM := 70;
110 L_STMT_NUM := 80;
111 L_STMT_NUM := 90;
112 CST_INVENTORY_PUB.CALCULATE_INVENTORYVALUE(P_API_VERSION => 1.0
113 ,P_INIT_MSG_LIST => CST_UTILITY_PUB.GET_TRUE
114 ,P_ORGANIZATION_ID => P_ORG_ID
115 ,P_ONHAND_VALUE => 1
116 ,P_INTRANSIT_VALUE => 1
117 ,P_RECEIVING_VALUE => 1
118 ,P_VALUATION_DATE => TO_DATE(P_AS_OF_DATE
119 ,'YYYY/MM/DD HH24:MI:SS')
120 ,P_COST_TYPE_ID => P_COST_TYPE_ID
121 ,P_ITEM_FROM => P_ITEM_FROM
122 ,P_ITEM_TO => P_ITEM_TO
123 ,P_CATEGORY_SET_ID => P_CATEGORY_SET
124 ,P_CATEGORY_FROM => P_CAT_FROM
125 ,P_CATEGORY_TO => P_CAT_TO
126 ,P_COST_GROUP_FROM => P_CG
127 ,P_COST_GROUP_TO => P_CG
128 ,P_SUBINVENTORY_FROM => NULL
129 ,P_SUBINVENTORY_TO => NULL
130 ,P_QTY_BY_REVISION => NULL
131 ,P_ZERO_COST_ONLY => P_ZERO_COST
132 ,P_ZERO_QTY => NULL
133 ,P_EXPENSE_ITEM => P_EXP_ITEM
134 ,P_EXPENSE_SUB => P_EXP_SUBINV
135 ,P_UNVALUED_TXNS => 0
136 ,P_RECEIPT => 1
137 ,P_SHIPMENT => 1
138 ,P_OWN => 1
139 ,P_DETAIL => NULL
140 ,X_RETURN_STATUS => L_RETURN_STATUS
141 ,X_MSG_COUNT => L_MSG_COUNT
142 ,X_MSG_DATA => L_MSG_DATA);
143 L_STMT_NUM := 100;
144 IF L_RETURN_STATUS <> CST_UTILITY_PUB.GET_RET_STS_SUCCESS THEN
145 RAISE L_CST_INV_VAL;
146 END IF;
147 L_STMT_NUM := 110;
148 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
149 ,P_COUNT => L_MSG_COUNT
150 ,P_DATA => L_MSG_DATA);
151 L_STMT_NUM := 120;
152 IF L_MSG_COUNT > 0 THEN
153 FOR i IN 1 .. L_MSG_COUNT LOOP
154 L_MSG_DATA := FND_MSG_PUB.GET(I
155 ,CST_UTILITY_PUB.GET_FALSE);
156 FND_FILE.PUT_LINE(CST_UTILITY_PUB.GET_LOG
157 ,I || '-' || L_MSG_DATA);
158 END LOOP;
159 END IF;
160 SELECT
161 TO_CHAR(TO_DATE(P_AS_OF_DATE
162 ,'YYYY/MM/DD HH24:MI:SS')
163 ,'DD-MON-YYYY HH24:MI:SS')
164 INTO L_AS_OF_DATE
165 FROM
166 DUAL;
167 P_AS_OF_DATE1 := L_AS_OF_DATE;
168 /*SRW.MESSAGE(0
169 ,'BOM_CSTRAIVA_XMLP_PKG << ' || TO_CHAR(SYSDATE
170 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
171 RETURN TRUE;
172 EXCEPTION
173 WHEN OTHERS THEN
174 /*SRW.MESSAGE(999
175 ,L_STMT_NUM || ': ' || SQLERRM)*/NULL;
176 /*SRW.MESSAGE(999
177 ,'BOM_CSTRAIVA_XMLP_PKG >X ' || TO_CHAR(SYSDATE
178 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
179 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
180 ,P_COUNT => L_MSG_COUNT
181 ,P_DATA => L_MSG_DATA);
182 IF L_MSG_COUNT > 0 THEN
183 FOR i IN 1 .. L_MSG_COUNT LOOP
184 L_MSG_DATA := FND_MSG_PUB.GET(I
185 ,CST_UTILITY_PUB.GET_FALSE);
186 FND_FILE.PUT_LINE(CST_UTILITY_PUB.GET_LOG
187 ,I || '-' || L_MSG_DATA);
188 END LOOP;
189 END IF;
190 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
191 END BEFOREREPORT;
192 FUNCTION AFTERREPORT RETURN BOOLEAN IS
193 BEGIN
194 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
195 /*SRW.MESSAGE(0
196 ,'CSTRAIVR >> ' || TO_CHAR(SYSDATE
197 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
198 RETURN (TRUE);
199 END AFTERREPORT;
200 END BOM_CSTRAIVA_XMLP_PKG;
201