1 PACKAGE BODY BOM_BOMRASSY_XMLP_PKG AS
2 /* $Header: BOMRASSYB.pls 120.0 2007/12/28 09:45:42 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 T_ORG_CODE_LIST INV_ORGHIERARCHY_PVT.ORGID_TBL_TYPE;
5 L_ORG_NAME VARCHAR2(60);
6 N NUMBER := 0;
7 BEGIN
8 BEGIN
9 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
10 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
11 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" NUM=":P_STRUCT_ASSY"
12 APPL_SHORT_NAME="INV" OUTPUT=":P_ASSEMBLY"
13 MODE="SELECT" DISPLAY="ALL" TABLEALIAS="item1"')*/NULL;
14 IF P_ITEM_FROM IS NOT NULL THEN
15 IF P_ITEM_TO IS NOT NULL THEN
16 NULL;
17 ELSE
18 NULL;
19 END IF;
20 ELSE
21 IF P_ITEM_TO IS NOT NULL THEN
22 NULL;
23 END IF;
24 END IF;
25 IF P_CATG_FROM IS NOT NULL THEN
26 IF P_CATG_TO IS NOT NULL THEN
27 NULL;
28 ELSE
29 NULL;
30 END IF;
31 ELSE
32 IF P_CATG_TO IS NOT NULL THEN
33 NULL;
34 END IF;
35 END IF;
36 IF P_ALL_ORGS = 1 THEN
37 SELECT
38 BOM_LISTS_S.NEXTVAL
39 INTO P_SEQUENCE_ID
40 FROM
41 DUAL;
42 FOR C1 IN (SELECT
43 ORGANIZATION_ID
44 FROM
45 MTL_PARAMETERS MP
46 WHERE MASTER_ORGANIZATION_ID = (
47 SELECT
48 MASTER_ORGANIZATION_ID
49 FROM
50 MTL_PARAMETERS
51 WHERE ORGANIZATION_ID = P_ORG_ID )
52 AND MP.ORGANIZATION_ID IN (
53 SELECT
54 ORGANIZATION_ID
55 FROM
56 ORG_ACCESS_VIEW
57 WHERE RESPONSIBILITY_ID = FND_PROFILE.VALUE('RESP_ID')
58 AND RESP_APPLICATION_ID = FND_PROFILE.VALUE('RESP_APPL_ID') )) LOOP
59 N := N + 1;
60 INSERT INTO BOM_LISTS
61 (ORGANIZATION_ID
62 ,SEQUENCE_ID
63 ,ALTERNATE_DESIGNATOR)
64 VALUES (C1.ORGANIZATION_ID
65 ,P_SEQUENCE_ID
66 ,C1.ORGANIZATION_ID);
67 T_ORG_CODE_LIST(N) := C1.ORGANIZATION_ID;
68 END LOOP;
69 ELSIF P_ALL_ORGS = 2 THEN
70 IF P_ORG_HIERARCHY IS NOT NULL THEN
71 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(P_ORG_HIERARCHY
72 ,P_ORG_ID
73 ,T_ORG_CODE_LIST);
74 SELECT
75 BOM_LISTS_S.NEXTVAL
76 INTO P_SEQUENCE_ID
77 FROM
78 DUAL;
79 FOR I IN T_ORG_CODE_LIST.FIRST .. T_ORG_CODE_LIST.LAST LOOP
80 INSERT INTO BOM_LISTS
81 (ORGANIZATION_ID
82 ,SEQUENCE_ID
83 ,ALTERNATE_DESIGNATOR)
84 VALUES (T_ORG_CODE_LIST(I)
85 ,P_SEQUENCE_ID
86 ,T_ORG_CODE_LIST(I));
87 END LOOP;
88 ELSIF P_ORG_HIERARCHY IS NULL THEN
89 SELECT
90 BOM_LISTS_S.NEXTVAL
91 INTO P_SEQUENCE_ID
92 FROM
93 DUAL;
94 INSERT INTO BOM_LISTS
95 (ORGANIZATION_ID
96 ,SEQUENCE_ID)
97 VALUES (P_ORG_ID
98 ,P_SEQUENCE_ID);
99 END IF;
100 ELSE
101 SELECT
102 BOM_LISTS_S.NEXTVAL
103 INTO P_SEQUENCE_ID
104 FROM
105 DUAL;
106 INSERT INTO BOM_LISTS
107 (ORGANIZATION_ID
108 ,SEQUENCE_ID)
109 VALUES (P_ORG_ID
110 ,P_SEQUENCE_ID);
111 END IF;
112 IF P_ALL_ORGS = 1 THEN
113 P_ALL_ORGS := 'Yes';
114 ELSE
115 P_ALL_ORGS := 'No';
116 END IF;
117 RETURN (TRUE);
118 EXCEPTION
119 WHEN OTHERS THEN
120 /*SRW.MESSAGE('2000'
121 ,SQLERRM)*/NULL;
122 RETURN (FALSE);
123 END;
124 RETURN (TRUE);
125 END BEFOREREPORT;
126
127 FUNCTION AFTERREPORT RETURN BOOLEAN IS
128 BEGIN
129 DELETE FROM BOM_LISTS
130 WHERE SEQUENCE_ID = P_SEQUENCE_ID;
131 COMMIT;
132 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
133 RETURN (TRUE);
134 END AFTERREPORT;
135
136 FUNCTION GET_COMMON_ORG(COMMON_ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
137 COMMON_ORG VARCHAR2(3);
138 BEGIN
139 IF COMMON_ORGANIZATION_ID IS NULL THEN
140 NULL;
141 ELSE
142 SELECT
143 ORGANIZATION_CODE
144 INTO COMMON_ORG
145 FROM
146 ORG_ORGANIZATION_DEFINITIONS
147 WHERE ORGANIZATION_ID = COMMON_ORGANIZATION_ID;
148 END IF;
149 RETURN (COMMON_ORG);
150 END GET_COMMON_ORG;
151
152 FUNCTION ORG_CODEFORMULA(ORG_ID IN NUMBER) RETURN CHAR IS
153 ORG_CODE VARCHAR2(3);
154 BEGIN
155 SELECT
156 ORGANIZATION_CODE
157 INTO ORG_CODE
158 FROM
159 ORG_ORGANIZATION_DEFINITIONS ORG
160 WHERE ORG.ORGANIZATION_ID = ORG_ID;
161 RETURN (ORG_CODE);
162 END ORG_CODEFORMULA;
163
164 END BOM_BOMRASSY_XMLP_PKG;
165