PACKAGE BODY: APPS.OKS_OKSCOBKS_XMLP_PKG

Source

1 PACKAGE BODY OKS_OKSCOBKS_XMLP_PKG AS
2 /* $Header: OKSCOBKSB.pls 120.2 2007/12/25 07:56:23 nchinnam noship $ */
3   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4    apf boolean;
5   BEGIN
6     apf := AFTERPFORM;
7     RETURN (TRUE);
8   END BEFOREREPORT;
9 
10   FUNCTION AFTERPFORM RETURN BOOLEAN IS
11     X_SALESREP VARCHAR2(800);
12     X_ORG VARCHAR2(800);
13     X_VALUE VARCHAR2(800);
14     L_DEFAULT_ORDER_BY VARCHAR2(800);
15     L_DEFAULT_ORDER_BY1 VARCHAR2(800);
16   BEGIN
17     BEGIN
18       P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
19       /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
20     EXCEPTION
21       WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
22         /*SRW.MESSAGE(10
23                    ,'srw_init')*/NULL;
24     END;
25     /*SRW.MESSAGE(15
26                ,'After srw_init')*/NULL;
27     IF P_ORG IS NULL AND FND_PROFILE.VALUE('OKC_VIEW_K_BY_ORG') = 'Y' THEN
28       P_ORG := FND_PROFILE.VALUE('ORG_ID');
29     END IF;
30     IF P_ORG IS NOT NULL THEN
31       X_ORG := ' and org.organization_id = :p_org';
32       P_ORG_WHERE := X_ORG;
33     END IF;
34     IF P_SALESREP IS NOT NULL THEN
35       P_SALESREP_CHAR := TO_CHAR(P_SALESREP);
36       X_SALESREP := ' and contact.object1_id1 =  :p_salesrep_char';
37       P_SALESREP_WHERE := X_SALESREP;
38     END IF;
39     IF P_VALUE IS NOT NULL THEN
40       X_VALUE := ' and okc_hdr.estimated_amount > :p_value';
41       P_VALUE_WHERE := X_VALUE;
42     END IF;
43     P_APPROVED_DATE_WHERE := ' ';
44     IF P_DATE_FROM IS NOT NULL THEN
45       P_APPROVED_DATE_WHERE := P_APPROVED_DATE_WHERE || ' and okc_hdr.date_approved  >= :p_date_from';
46     END IF;
47     IF P_DATE_TO IS NOT NULL THEN
48       P_APPROVED_DATE_WHERE := P_APPROVED_DATE_WHERE || ' and okc_hdr.date_approved  <= ( (TRUNC(:p_date_to)+1) - (1/(24*60*60)) )';
49     END IF;
50     P_START_DATE_WHERE := ' ';
51     IF P_START_DATE_FROM IS NOT NULL THEN
52       P_START_DATE_WHERE := P_START_DATE_WHERE || ' and okc_hdr.start_date  >= :p_start_date_from';
53     END IF;
54     IF P_START_DATE_TO IS NOT NULL THEN
55       P_START_DATE_WHERE := P_START_DATE_WHERE || ' and okc_hdr.start_date  <= ( (TRUNC(:p_start_date_to)+1) - (1/(24*60*60)) )';
56     END IF;
57     IF P_CONTRACT_GROUP IS NOT NULL THEN
58       P_CONTRACT_GROUP_FROM := ' ,  ( select INCLUDED_CHR_ID
59                                                                   from okc_k_grpings
60                                                                   start with INCLUDED_CHR_ID IN
61                                                                          ( select /*+ cardinality (b,1) */ id
62                                                                            from okc_k_headers_b b
63                                                                            where start_date between :P_START_DATE_FROM and :P_START_DATE_TO
64                                                                            and scs_code in ( ''SERVICE'' , ''WARRANTY'' ) )
65                                                                 and CGP_PARENT_ID = :p_contract_group
66                                                                 connect by CGP_PARENT_ID = PRIOR INCLUDED_CGP_ID ) cgrp ';
67       P_CONTRACT_GROUP_WHERE := ' and okc_hdr.ID = cgrp.included_chr_id ';
68     END IF;
69     L_DEFAULT_ORDER_BY := ' ORDER BY salesrep.name, okc_hdr.currency_code';
70     L_DEFAULT_ORDER_BY1 := ' ORDER BY bill_txn.trx_number,bill_txn.trx_date, trx_amount';
71     IF P_ORDER_BY = 'CUSTOMER NAME' THEN
72       P_ORDER_BY_ORDER := L_DEFAULT_ORDER_BY || ', hzp.party_name';
73     ELSIF P_ORDER_BY = 'DATE APPROVED' THEN
74       P_ORDER_BY_ORDER := L_DEFAULT_ORDER_BY || ', okc_hdr.date_approved';
75     ELSIF P_ORDER_BY = 'SERVICE CONTRACT' THEN
76       P_ORDER_BY_ORDER := L_DEFAULT_ORDER_BY || ', okc_hdr.contract_number,okc_hdr.contract_number_modifier';
77     ELSIF P_ORDER_BY = 'MODIFIER' THEN
78       P_ORDER_BY_ORDER := L_DEFAULT_ORDER_BY || ', okc_hdr.contract_number_modifier';
79     ELSIF P_ORDER_BY = 'CONTRACT VALUE' THEN
80       P_ORDER_BY_ORDER := L_DEFAULT_ORDER_BY || ', okc_hdr.estimated_amount';
81     ELSIF P_ORDER_BY = 'START DATE' THEN
82       P_ORDER_BY_ORDER := L_DEFAULT_ORDER_BY || ', okc_hdr.start_date';
83     ELSIF P_ORDER_BY = 'INVOICE NUMBER' THEN
84       P_ORDER_BY_ORDER1 := ' ORDER BY bill_txn.trx_number';
85     ELSIF P_ORDER_BY = 'INVOICE DATE' THEN
86       P_ORDER_BY_ORDER1 := ' ORDER BY bill_txn.trx_date';
87     ELSIF P_ORDER_BY = 'INVOICE VALUE' THEN
88       P_ORDER_BY_ORDER1 := 'ORDER BY trx_amount';
89     ELSE
90       P_ORDER_BY_ORDER := L_DEFAULT_ORDER_BY || ',hzp.party_name,okc_hdr.contract_number,okc_hdr.contract_number_modifier,' || 'okc_hdr.start_date,okc_hdr.date_approved,okc_hdr.estimated_amount ';
91       P_ORDER_BY_ORDER1 := L_DEFAULT_ORDER_BY1;
92     END IF;
93     RETURN (TRUE);
94   END AFTERPFORM;
95 
96   FUNCTION AFTERREPORT RETURN BOOLEAN IS
97   BEGIN
98     BEGIN
99       /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
100     EXCEPTION
101       WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
102         /*SRW.MESSAGE(1
103                    ,'srw_exit')*/NULL;
104     END;
105     RETURN (TRUE);
106   END AFTERREPORT;
107 
108 END OKS_OKSCOBKS_XMLP_PKG;
109