[Home] [Help]
PACKAGE: APPS.AP_OPEN_ITEMS_REVAL_PKG
Source
1 PACKAGE AP_OPEN_ITEMS_REVAL_PKG AUTHID CURRENT_USER AS
2 /* $Header: apopitrs.pls 120.5.12020000.2 2012/07/13 19:47:17 pgayen ship $ */
3
4 /* Report input parameters which should be same as token names
5 defined at concurrent program */
6 P_ORG_ID ap_system_parameters_all.org_id%type;
7 P_REVALUATION_PERIOD gl_periods.period_name%type;
8 P_DUE_DATE gl_period_statuses.end_date%type;
9 P_RATE_TYPE_LOOKUP_CODE ap_lookup_codes.lookup_code%type;
10 P_DAILY_RATE_TYPE gl_daily_conversion_types.conversion_type%type;
11 P_DAILY_RATE_DATE gl_daily_rates.conversion_date%type;
12 P_FROM_BALANCING_SEGMENT gl_code_combinations.segment1%type;
13 P_TO_BALACING_SEGMENT gl_code_combinations.segment1%type;
14 P_TRANSFER_TO_GL_ONLY varchar2(1);
15 P_CLEARED_ONLY varchar2(1);
16 P_DEBUG_SWITCH varchar2(1);
17 P_TRACE_SWITCH varchar2(1);
18
19 /* Additional parameters */
20 g_coa_id gl_ledgers.chart_of_accounts_id%type;
21 g_ledger_id gl_sets_of_books.set_of_books_id%type;
22 g_base_precision fnd_currencies_vl.precision%type;
23 g_base_min_acct_unit fnd_currencies_vl.minimum_accountable_unit%type;
24 g_daily_rate_information varchar2(1) := 'N';
25 g_daily_rate_error varchar2(1) := 'N';
26
27 /* Report displayed values*/
28 g_operating_unit_dsp hr_operating_units.name%type;
29 g_gl_name_dsp gl_sets_of_books.name%type;
30 g_rate_type_dsp ap_lookup_codes.displayed_field%type;
31 g_daily_rate_type_dsp gl_daily_conversion_types.user_conversion_type%type;
32 g_trans_to_gl_only_dsp fnd_lookups.meaning%type;
33 g_cleared_only_dsp fnd_lookups.meaning%type;
34 g_base_currency_code gl_sets_of_books.currency_code%type;
35 g_base_currency_desc fnd_currencies_vl.description%type;
36 g_revaluation_date gl_period_statuses.end_date%type;
37 /* Bug 14136626*/
38 g_eur_code gl_sets_of_books.currency_code%type;
39
40 /* Constants */
41 c_application_id constant number := 200;
42
43 /* xml query */
44 G_SQL_STATEMENT varchar2(32000) := '
45 select balancing_segment BALANCING_SEGMENT,
46 account_segment ACCOUNT_SEGMENT,
47 opit.code_combination_id,
48 account ACCOUNT,
49 &ACCOUNT_DESCRIPTION ACCOUNT_DESCRIPTION,
50 party_id PARTY_ID,
51 party_site_id PARTY_SITE_ID,
52 vendor_id VENDOR_ID,
53 party_name VENDOR_NAME,
54 vendor_number VENDOR_NUMBER,
55 vendor_site_id VENDOR_SITE_ID,
56 vendor_site_code VENDOR_SITE_CODE,
57 txn_id TXN_ID,
58 txn_number TXN_NUMBER,
59 txn_type_lookup_code TXN_TYPE_LOOKUP_CODE,
60 txn_date TXN_DATE,
61 txn_currency_code TXN_CURRENCY_CODE,
62 payment_currency_code PMT_CURRENCY_CODE,
63 round(TXN_BASE_EXCHANGE_RATE, 5) EXCHANGE_RATE,
64 payment_cross_rate PAYMENT_CROSS_RATE,
65 decode(revaluation_rate,
66 null, ''No Rate'',
67 round(revaluation_rate, 5)) REVALUATION_RATE,
68 payment_status_flag PAYMENT_STATUS_FLAG,
69 entered_amount ENTERED_AMOUNT,
70 accounted_amount ACCOUNTED_AMOUNT,
71 open_entered_amount OPEN_ENTERED_AMOUNT,
72 open_accounted_amount OPEN_ACCOUNTED_AMOUNT,
73 nvl(TO_CHAR(round(open_entered_amount * revaluation_rate, :g_base_precision)), ''*'') REVALUED_AMOUNT_DSP,
74 nvl(round(open_entered_amount * revaluation_rate, :g_base_precision), 0) REVALUED_AMOUNT,
75 case when revaluation_rate is null
76 or open_accounted_amount > round(open_entered_amount * revaluation_rate, :g_base_precision)
77 then open_accounted_amount
78 else round(open_entered_amount * revaluation_rate, :g_base_precision)
79 end OPEN_REVALUED_AMOUNT,
80 AP_OPEN_ITEMS_REVAL_PKG.get_due_Date(txn_id, txn_type_lookup_code) DUE_dATE
81 from(
82 $open_items_query$
83 ) opit
84 order by balancing_segment,
85 account_segment,
86 account,
87 party_name,
88 vendor_number,
89 vendor_site_code,
90 txn_number,
91 txn_type_lookup_code,
92 txn_date';
93
94 /* loacl procedure, can be usable only by the report */
95 /*Bug 14136626*/
96 function get_revaluation_rate(l_currency_code IN gl_sets_of_books.currency_code%type,
97 l_pay_cross_rate_type IN gl_sets_of_books.currency_code%type)
98 return number;
99
100 /* loacl procedure, can be usable only by the report */
101 function get_due_date(p_invoice_id IN number, p_type in varchar2)
102 return date;
103
104 /* Report triggers, which will be called from xml */
105 function before_report return boolean;
106
107 END AP_OPEN_ITEMS_REVAL_PKG;