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