1 PACKAGE BODY OKS_OKSNREPD_XMLP_PKG AS
2 /* $Header: OKSNREPDB.pls 120.2 2007/12/25 07:58:14 nchinnam noship $ */
3 FUNCTION CF_1FORMULA(CUSTOMER_PRODUCT_ID_v IN VARCHAR2) RETURN VARCHAR2 IS
4 L_CSI VARCHAR2(50);
5 CURSOR CUR_CSI IS
6 SELECT
7 CS.NAME
8 FROM
9 CS_SYSTEMS CS,
10 OKX_CUST_PROD_V CPV
11 WHERE CPV.CUSTOMER_PRODUCT_ID = CUSTOMER_PRODUCT_ID_v
12 AND CPV.SYSTEM_ID = CS.SYSTEM_ID;
13 BEGIN
14 FOR i_csi IN CUR_CSI LOOP
15 L_CSI := I_CSI.NAME;
16 END LOOP;
17 RETURN L_CSI;
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 L_CSI := NULL;
21 RETURN L_CSI;
22 WHEN OTHERS THEN
23 L_CSI := NULL;
24 RETURN L_CSI;
25 END CF_1FORMULA;
26
27 FUNCTION CF_2FORMULA(CUSTOMER_PRODUCT_ID_v IN VARCHAR2
28 ,ORGANIZATION_ID_v IN NUMBER) RETURN CHAR IS
29 L_PARTNO VARCHAR2(245);
30 CURSOR CUR_PARTNO IS
31 SELECT
32 MTL.SEGMENT1,
33 MTL.DESCRIPTION
34 FROM
35 MTL_SYSTEM_ITEMS_B MTL,
36 CS_CUSTOMER_PRODUCTS_ALL CP
37 WHERE CP.CUSTOMER_PRODUCT_ID = CUSTOMER_PRODUCT_ID_v
38 AND CP.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
39 AND MTL.ORGANIZATION_ID = ORGANIZATION_ID_v;
40 BEGIN
41 FOR i_partno IN CUR_PARTNO LOOP
42 L_PARTNO := I_PARTNO.SEGMENT1;
43 CP_DESCRIPTION := I_PARTNO.DESCRIPTION;
44 END LOOP;
45 RETURN L_PARTNO;
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 L_PARTNO := NULL;
49 RETURN L_PARTNO;
50 WHEN OTHERS THEN
51 L_PARTNO := NULL;
52 RETURN L_PARTNO;
53 END CF_2FORMULA;
54
55 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
56 BEGIN
57 RETURN (TRUE);
58 END BEFOREREPORT;
59
60 FUNCTION CF_SALESREP_NAMEFORMULA(SALESREP_ID_v IN VARCHAR2
61 ,ORG_ID_v IN NUMBER) RETURN CHAR IS
62 L_REP_NAME VARCHAR2(240);
63 BEGIN
64 SELECT
65 DISTINCT
66 NAME
67 INTO L_REP_NAME
68 FROM
69 JTF_RS_SALESREPS
70 WHERE SALESREP_ID = SALESREP_ID_v
71 AND ORG_ID = ORG_ID_v
72 AND ROWNUM < 2;
73 RETURN (L_REP_NAME);
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 L_REP_NAME := NULL;
77 RETURN (L_REP_NAME);
78 WHEN OTHERS THEN
79 L_REP_NAME := NULL;
80 RETURN (L_REP_NAME);
81 END CF_SALESREP_NAMEFORMULA;
82
83 FUNCTION AFTERPFORM RETURN BOOLEAN IS
84 X_ORG_ID VARCHAR2(800);
85 X_SALESREP VARCHAR2(800);
86 X_CUSTOMER_NAME VARCHAR2(800);
87 BEGIN
88 BEGIN
89 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
90 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
91 EXCEPTION
92 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
93 /*SRW.MESSAGE(1
94 ,'srw_init')*/NULL;
95 END;
96 IF P_ORG_ID IS NULL AND FND_PROFILE.VALUE('OKC_VIEW_K_BY_ORG') = 'Y' THEN
97 P_ORG_ID := FND_PROFILE.VALUE('ORG_ID');
98 END IF;
99 IF P_ORG_ID IS NOT NULL THEN
100 X_ORG_ID := ' and exp.org_id = :p_org_id';
101 P_ORG_ID_WHERE := X_ORG_ID;
102 END IF;
103 IF P_SALESREP IS NOT NULL THEN
104 X_SALESREP := ' and exp.salesrep_id like :p_salesrep ';
105 P_SALESREP_WHERE := X_SALESREP;
106 END IF;
107 IF P_CUSTOMER_NAME IS NOT NULL THEN
108 X_CUSTOMER_NAME := ' and exp.customer_name like :p_customer_name';
109 P_CUSTOMER_NAME_WHERE := X_CUSTOMER_NAME;
110 END IF;
111 IF P_FROM_END_DATE IS NOT NULL THEN
112 P_FROM_END_DATE_WHERE := ' and exp.product_end_date >= :P_from_end_date ';
113 END IF;
114 IF P_TO_END_DATE IS NOT NULL THEN
115 P_TO_END_DATE_WHERE := ' and exp.product_end_date <= :P_to_end_date ';
116 END IF;
117 IF P_CONTRACT_GROUP IS NOT NULL THEN
118 P_CONTRACT_GROUP_WHERE := ' and exp.contract_id in ( select INCLUDED_CHR_ID from okc_k_grpings
119 start with CGP_PARENT_ID = :p_contract_group
120 connect by CGP_PARENT_ID = PRIOR INCLUDED_CGP_ID ) ';
121 END IF;
122 RETURN (TRUE);
123 END AFTERPFORM;
124
125 FUNCTION AFTERREPORT RETURN BOOLEAN IS
126 BEGIN
127 BEGIN
128 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
129 EXCEPTION
130 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
131 /*SRW.MESSAGE(1
135 END AFTERREPORT;
132 ,'srw_exit')*/NULL;
133 END;
134 RETURN (TRUE);
136
137 FUNCTION CP_DESCRIPTION_P RETURN VARCHAR2 IS
138 BEGIN
139 RETURN CP_DESCRIPTION;
140 END CP_DESCRIPTION_P;
141
142 END OKS_OKSNREPD_XMLP_PKG;
143
[Home] [Help]