eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PA_PAXSIADJ_XMLP_PKG

Source


1 PACKAGE BODY PA_PAXSIADJ_XMLP_PKG AS
2 /* $Header: PAXSIADJB.pls 120.0 2008/01/02 12:17:55 krreddy noship $ */
3   function BeforeReport return boolean is
4   CURSOR C_GET_LEDGER_ID_CUR IS
5   SELECT set_of_books_id
6     FROM pa_implementations;
7   CURSOR C_GET_LEDGER_DETAILS_CUR(p_ledger_id NUMBER) IS
8   SELECT period_set_name
9        , currency_code
10     FROM gl_ledgers_public_v
11    WHERE ledger_id = p_ledger_id;
12   CURSOR C_GET_GL_PERIOD_START_DATE_CUR(p_period_set_name VARCHAR2, p_period_name VARCHAR2) IS
13   SELECT start_date
14     FROM gl_periods
15    WHERE period_set_name = p_period_set_name
16      AND period_name = p_period_name;
17   CURSOR C_GET_GL_PERIOD_END_DATE_CUR(p_period_set_name VARCHAR2, p_period_name VARCHAR2) IS
18   SELECT end_date
19     FROM gl_periods
20    WHERE period_set_name = p_period_set_name
21      AND period_name = p_period_name;
22 begin
23   /*SRW.USER_EXIT('FND SRWINIT');
24   srw.user_exit('FND GETPROFILE
25                  NAME="PA_DEBUG_MODE"
26                  FIELD=":p_debug_mode"
27                  PRINT_ERROR="N"');*/
28   IF p_debug_mode = 'Y' THEN
29 --    srw.message(10,'Getting Ledger ID');
30 NULL;
31   END IF;
32   OPEN C_GET_LEDGER_ID_CUR;
33   FETCH C_GET_LEDGER_ID_CUR INTO P_LEDGER_ID;
34   CLOSE C_GET_LEDGER_ID_CUR;
35   IF p_debug_mode = 'Y' THEN
36 --    srw.message(20,'Ledger ID : ' || :P_LEDGER_ID);
37 --    srw.message(30,'Getting Period Set Name and Currency Code');
38 NULL;
39   END IF;
40   OPEN C_GET_LEDGER_DETAILS_CUR(P_LEDGER_ID);
41   FETCH C_GET_LEDGER_DETAILS_CUR INTO P_PERIOD_SET_NAME,P_CURRENCY_CODE;
42   CLOSE C_GET_LEDGER_DETAILS_CUR;
43   IF p_debug_mode = 'Y' THEN
44 --    srw.message(40,'Period Set Name : ' || :P_PERIOD_SET_NAME);
45 --    srw.message(50,'Currency Code : ' || :P_CURRENCY_CODE);
46 NULL;
47   END IF;
48   IF P_FROM_GL_PERIOD IS NOT NULL THEN
49     IF p_debug_mode = 'Y' THEN
50 --      srw.message(60,'Getting Start Date of GL Period ' || :P_FROM_GL_PERIOD);
51 NULL;
52     END IF;
53     OPEN C_GET_GL_PERIOD_START_DATE_CUR(P_PERIOD_SET_NAME,P_FROM_GL_PERIOD);
54     FETCH C_GET_GL_PERIOD_START_DATE_CUR INTO P_FROM_GL_DATE;
55     CLOSE C_GET_GL_PERIOD_START_DATE_CUR;
56     IF p_debug_mode = 'Y' THEN
57 --      srw.message(70,'Start Date : ' || :P_FROM_GL_DATE);
58 NULL;
59     END IF;
60   END IF;
61   IF P_TO_GL_PERIOD IS NOT NULL THEN
62     IF p_debug_mode = 'Y' THEN
63 --      srw.message(80,'Getting End Date of GL Period ' || :P_TO_GL_PERIOD);
64 NULL;
65     END IF;
66     OPEN C_GET_GL_PERIOD_END_DATE_CUR(P_PERIOD_SET_NAME,P_TO_GL_PERIOD);
67     FETCH C_GET_GL_PERIOD_END_DATE_CUR INTO P_TO_GL_DATE;
68     CLOSE C_GET_GL_PERIOD_END_DATE_CUR;
69     IF p_debug_mode = 'Y' THEN
70 --      srw.message(90,'End Date : ' || :P_TO_GL_DATE);
71 NULL;
72     END IF;
73   END IF;
74   IF FND_GLOBAL.APPLICATION_SHORT_NAME = 'GMS' THEN
75     P_IS_GRANTS_INSTALLED := 'Y';
76     P_GMS_WHERE := ' AND pa_gms_api.vert_is_award_within_range( '
77                  || '       ei.expenditure_item_id '
78                  || '      ,''' || P_FROM_AWARD_NUMBER || ''' '
79                  || '      ,''' || P_TO_AWARD_NUMBER || ''' '
80                  || '      ) = ''Y'' ';
81  ELSE
82  P_GMS_WHERE := ' ';
83   END IF;
84   IF  P_FROM_GL_ACCOUNT IS NOT NULL
85   AND P_TO_GL_ACCOUNT IS NOT NULL THEN
86     IF p_debug_mode = 'Y' THEN
87 --      srw.message(100,'Deriving where condition using account range');
88 NULL;
89     END IF;
90 /*    srw.user_exit('FND FLEXSQL CODE="GL#"
91                    NUM=":P_COA_ID"
92                    APPL_SHORT_NAME="SQLGL"
93                    OUTPUT=":P_ACC_WHERE"
94                    TABLEALIAS="CC"
95                    MODE="WHERE"
96                    DISPLAY="ALL"
97                    OPERATOR="BETWEEN"
98                    OPERAND1=":P_FROM_GL_ACCOUNT"
99                    OPERAND2=":P_TO_GL_ACCOUNT"');
100 		   */
101     P_ACC_WHERE := ' AND ' || P_ACC_WHERE;
102     IF p_debug_mode = 'Y' THEN
103 --      srw.message(110,'Where condition formed : ' || :P_ACC_WHERE);
104 NULL;
105     END IF;
106   END IF;
107  /*If Adjustment Type is all adjustments then, no predicate is to be appended to the WHERE CLAUSE */
108   IF P_ADJUSTMENT_TYPE = 'ALL' THEN
109     P_INV_REV_ADJ_WHERE := ' ';
110     P_RCV_REV_ADJ_WHERE := ' ';
111     P_PAY_REV_ADJ_WHERE := ' ';
112     P_INV_PA_ADJ_WHERE  := ' ';
113     P_RCV_PA_ADJ_WHERE  := ' ';
114     P_PAY_PA_ADJ_WHERE  := ' ';
115   ELSIF P_ADJUSTMENT_TYPE = 'REV_ADJ' THEN
116     P_INV_REV_ADJ_WHERE :=
117                         ' AND ei.transaction_source IN (''AP VARIANCE'',''AP INVOICE'',''AP NRTAX'',''AP DISCOUNTS'',''AP ERV'''
118                      || '                              ,''INTERCOMPANY_AP_INVOICES'',''INTERPROJECT_AP_INVOICES'',''AP EXPENSE'')'
119                      || ' AND EXISTS (select NULL'
120                      || '               from pa_cost_distribution_lines cdl1'
121                      || '              where cdl1.expenditure_item_id = ei.expenditure_item_id'
122                      || '                and cdl1.line_num = 1'
123                      || '                and NVL(cdl1.reversed_flag,''N'') <> ''Y'')'
124                      || ' AND ei.net_zero_adjustment_flag = ''N'''
125                      || ' AND ei.transferred_from_exp_item_id IS NULL'
126                      || ' AND EXISTS (select NULL'
127                      || '               from pa_expenditure_items ei2'
128                      || '              where (ei2.document_header_id, ei2.document_distribution_id) IN'
129                      || '                    ( SELECT apdist2.invoice_id, apdist2.old_distribution_id'
130                      || '                        from ap_invoice_distributions apdist1,'
131                      || '                             ap_invoice_distributions apdist2 '
132                      || '                       where ei.document_distribution_id = apdist1.invoice_distribution_id'
133                      || '                         and NVL(apdist1.historical_flag,''N'') <> ''Y'''
134                      || '                         and apdist1.reversal_flag = ''Y'''
135                      || '                         and apdist1.parent_reversal_id = apdist2.invoice_distribution_id'
136                      || '                         and apdist2.old_distribution_id IS NOT NULL)'
137                      || '                and (   ei2.net_zero_adjustment_flag = ''Y'''
138                      || '                    OR EXISTS ( SELECT NULL'
139                      || '                                  FROM pa_cost_distribution_lines_all cdl2'
140                      || '                                 WHERE cdl2.expenditure_item_id = ei2.expenditure_item_id'
141                      || '                                   AND cdl2.line_num = 1'
142                      || '                                   AND cdl2.reversed_flag = ''Y''))) ';
143     P_RCV_REV_ADJ_WHERE :=
144                         ' AND ei.transaction_source IN (''PO RECEIPT'',''PO RECEIPT NRTAX'''
145                      || '                              ,''PO RECEIPT NRTAX PRICE ADJ'''
146                      || '                              ,''PO RECEIPT PRICE ADJ'')'
147                      || ' AND EXISTS (select NULL'
148                      || '               from pa_cost_distribution_lines cdl1'
149                      || '              where cdl1.expenditure_item_id = ei.expenditure_item_id'
150                      || '                and cdl1.line_num = 1'
151                      || '                and NVL(cdl1.reversed_flag,''N'') <> ''Y'')'
152                      || ' AND ei.net_zero_adjustment_flag = ''N'''
153                      || ' AND ei.transferred_from_exp_item_id IS NULL'
154                      || ' AND EXISTS (select NULL'
155                      || '               from pa_expenditure_items ei2'
156                      || '              where (ei2.document_header_id, ei2.document_distribution_id) IN'
157                      || '                    ( select rcv2.po_header_id, rcv2.transaction_id'
158                      || '                        from rcv_transactions rcv1'
159                      || '                           , rcv_transactions rcv2'
160                      || '                       where rcv1.transaction_id = ei.document_distribution_id'
161                      || '                         and rcv1.transaction_type in (''RETURN TO RECEIVING'',''RETURN TO VENDOR'',''CORRECT'')'
162                      || '                         and rcv1.parent_transaction_id = rcv2.transaction_id)'
163                      || '                and (   ei2.net_zero_adjustment_flag = ''Y'''
164                      || '                    OR EXISTS ( SELECT NULL'
165                      || '                                  FROM pa_cost_distribution_lines_all cdl2'
166                      || '                                 WHERE cdl2.expenditure_item_id = ei2.expenditure_item_id'
167                      || '                                   AND cdl2.line_num = 1'
168                      || '                                   AND cdl2.reversed_flag = ''Y''))) ';
169     P_PAY_REV_ADJ_WHERE := ' AND 1 = 2 ';
170     P_INV_PA_ADJ_WHERE  := ' ';
171     P_RCV_PA_ADJ_WHERE  := ' ';
172     P_PAY_PA_ADJ_WHERE  := ' ';
173   ELSIF P_ADJUSTMENT_TYPE = 'PA_ADJ' THEN
174     P_INV_REV_ADJ_WHERE := ' ';
175     P_RCV_REV_ADJ_WHERE := ' ';
176     P_PAY_REV_ADJ_WHERE := ' ';
177     P_INV_PA_ADJ_WHERE  := ' AND cdl.transfer_status_code <> ''V'' ';
178     P_RCV_PA_ADJ_WHERE  := ' AND cdl.transfer_status_code <> ''V'' ';
179     P_PAY_PA_ADJ_WHERE  := ' AND cdl.transfer_status_code <> ''V'' ';
180   END IF;
181 /* This will be appended to the ORDER BY clause based on the "Sort Order" parameter */
182   IF P_SORT_ORDER = 'AP' THEN
183     P_ORDER_BY := ', Invoice_Number '
184                 || ', Invoice_Line_Num '
185                 || ', Invoice_Dist_Line_Num '
186                 || ', AP_PO_Number '
187                 || ', AP_PO_Line_Num '
188                 || ', AP_PO_Dist_Num ';
189   ELSE
190     P_ORDER_BY := ', PO_Number '
191                 || ', PO_Line_Num '
192                 || ', PO_Dist_Num '
193                 || ', Invoice_Number '
194                 || ', Invoice_Line_Num '
195                 || ', Invoice_Dist_Line_Num ';
196   END IF;
197   return (TRUE);
198 end;
199   FUNCTION AFTERREPORT RETURN BOOLEAN IS
200   BEGIN
201     /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
202     RETURN (TRUE);
203   END AFTERREPORT;
204   FUNCTION CF_COMPANY_NAMEFORMULA RETURN CHAR IS
205     L_COMPANY_NAME VARCHAR2(30);
206     CURSOR C_GET_COMPANY_NAME_CUR IS
207       SELECT
208         GL.NAME
209       FROM
210         GL_LEDGERS_PUBLIC_V GL,
211         PA_IMPLEMENTATIONS IMP
212       WHERE GL.LEDGER_ID = IMP.SET_OF_BOOKS_ID;
213   BEGIN
214     OPEN C_GET_COMPANY_NAME_CUR;
215     FETCH C_GET_COMPANY_NAME_CUR
216      INTO L_COMPANY_NAME;
217     CLOSE C_GET_COMPANY_NAME_CUR;
218     RETURN L_COMPANY_NAME;
219   END CF_COMPANY_NAMEFORMULA;
220   FUNCTION CF_NO_DATA_FOUNDFORMULA(CS_COUNT IN NUMBER) RETURN CHAR IS
221     L_NO_DATA_FOUND VARCHAR2(80);
222     CURSOR C_GET_NO_DATA_FOUND_MSG_CUR IS
223       SELECT
224         MEANING
225       FROM
226         PA_LOOKUPS
227       WHERE LOOKUP_TYPE = 'MESSAGE'
228         AND LOOKUP_CODE = 'NO_DATA_FOUND';
229   BEGIN
230     IF CS_COUNT = 0 THEN
231       OPEN C_GET_NO_DATA_FOUND_MSG_CUR;
232       FETCH C_GET_NO_DATA_FOUND_MSG_CUR
233        INTO L_NO_DATA_FOUND;
234       CLOSE C_GET_NO_DATA_FOUND_MSG_CUR;
235     END IF;
236     RETURN L_NO_DATA_FOUND;
237   END CF_NO_DATA_FOUNDFORMULA;
238   FUNCTION CF_AWARD_NUMBERFORMULA(TRANSACTION_ID IN NUMBER) RETURN CHAR IS
239   BEGIN
240     RETURN PA_GMS_API.VERT_GET_AWARD_NUMBER(P_EXPENDITURE_ITEM_ID => TRANSACTION_ID);
241   END CF_AWARD_NUMBERFORMULA;
242 END PA_PAXSIADJ_XMLP_PKG;
243