1 PACKAGE BODY OKS_OKSSUMRP_XMLP_PKG AS
2 /* $Header: OKSSUMRPB.pls 120.2 2007/12/25 08:01:44 nchinnam noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 apf boolean;
5 BEGIN
6 apf := afterpform;
7 IF P_OPERATING_UNIT IS NOT NULL THEN
8 MO_GLOBAL.SET_POLICY_CONTEXT('S'
9 ,P_OPERATING_UNIT);
10 END IF;
11 RETURN (TRUE);
12 END BEFOREREPORT;
13
14 FUNCTION CF_1FORMULA(REP_NAME1 IN VARCHAR2
15 ,ORG_ID IN NUMBER) RETURN VARCHAR2 IS
16 L_REP_NAME VARCHAR2(240) := NULL;
17 BEGIN
18 SELECT
19 DISTINCT
20 NAME
21 INTO L_REP_NAME
22 FROM
23 JTF_RS_SALESREPS
24 WHERE SALESREP_ID = REP_NAME1
25 AND ORG_ID = CF_1FORMULA.ORG_ID
26 AND ROWNUM < 2;
27 RETURN (L_REP_NAME);
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 L_REP_NAME := NULL;
31 RETURN (L_REP_NAME);
32 WHEN OTHERS THEN
33 L_REP_NAME := NULL;
34 RETURN (L_REP_NAME);
35 END CF_1FORMULA;
36
37 FUNCTION CF_LAST_INTRERACTIONFORMULA(CONTRACT_ID IN NUMBER) RETURN CHAR IS
38 V_LAST_INT_DATE DATE;
39 CURSOR C1 IS
40 SELECT
41 MAX(B.INT_END_DATE)
42 FROM
43 OKC_LAUNCH_COMMGRID_V B,
44 OKC_K_HEADERS_B A
45 WHERE A.ID = CONTRACT_ID
46 AND A.ID = B.CONTRACT_ID
47 AND ROWNUM < 2;
48 BEGIN
49 OPEN C1;
50 FETCH C1
51 INTO V_LAST_INT_DATE;
52 CLOSE C1;
53 RETURN (V_LAST_INT_DATE);
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 V_LAST_INT_DATE := NULL;
57 RETURN (V_LAST_INT_DATE);
58 WHEN OTHERS THEN
59 V_LAST_INT_DATE := NULL;
60 RETURN (V_LAST_INT_DATE);
61 END CF_LAST_INTRERACTIONFORMULA;
62
63 FUNCTION AFTERPFORM RETURN BOOLEAN IS
64 X_REP_NAME VARCHAR2(800);
65 X_CUSTOMER_NAME VARCHAR2(800);
66 X_CUSTOMER_NUMBER VARCHAR2(800);
67 X_ORG_ID VARCHAR2(800);
68 X_CURRENCY_CODE VARCHAR2(800);
69 BEGIN
70 BEGIN
71 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
72 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
73 EXCEPTION
74 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
75 /*SRW.MESSAGE(1
76 ,'srw_init')*/NULL;
77 END;
78 IF P_REP_NAME IS NOT NULL THEN
79 X_REP_NAME := ' and rep_name = :p_rep_name';
80 P_REP_NAME_WHERE := X_REP_NAME;
81 END IF;
82 IF P_CUSTOMER_NAME IS NOT NULL THEN
83 X_CUSTOMER_NAME := ' and customer_id = :p_customer_name';
84 P_CUSTOMER_NAME_WHERE := X_CUSTOMER_NAME;
85 END IF;
86 IF P_CUSTOMER_NUMBER IS NOT NULL THEN
87 X_CUSTOMER_NUMBER := ' and customer_id = :p_customer_number';
88 P_CUSTOMER_NUMBER_WHERE := X_CUSTOMER_NUMBER;
89 END IF;
90 IF P_CURRENCY_CODE IS NOT NULL THEN
91 X_CURRENCY_CODE := ' and currency_code = :p_currency_code';
92 P_CURRENCY_CODE_WHERE := X_CURRENCY_CODE;
93 END IF;
94 P_START_DATE_WHERE := ' ';
95 IF P_FROM_DATE IS NOT NULL THEN
96 P_START_DATE_WHERE := P_START_DATE_WHERE || ' and START_DATE >= :p_from_date ';
97 END IF;
98 IF P_TO_DATE IS NOT NULL THEN
99 P_START_DATE_WHERE := P_START_DATE_WHERE || ' and START_DATE <= :p_to_date ';
100 END IF;
101 IF P_CONTRACT_GROUP IS NOT NULL THEN
102 P_CONTRACT_GROUP_WHERE := ' and contract_id in ( select INCLUDED_CHR_ID from okc_k_grpings
103 start with CGP_PARENT_ID = :p_contract_group
104 connect by CGP_PARENT_ID = PRIOR INCLUDED_CGP_ID ) ';
105 END IF;
106 RETURN (TRUE);
107 END AFTERPFORM;
108
109 FUNCTION AFTERREPORT RETURN BOOLEAN IS
110 BEGIN
111 BEGIN
112 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
113 EXCEPTION
114 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
115 /*SRW.MESSAGE(1
116 ,'srw_exit')*/NULL;
117 END;
118 RETURN (TRUE);
119 END AFTERREPORT;
120
121 END OKS_OKSSUMRP_XMLP_PKG;
122