1 package ap_period_close_pkg AUTHID CURRENT_USER as
2 /* $Header: apprdcls.pls 120.2.12010000.3 2009/01/15 14:06:58 njakkula noship $ */
3 /*------------------------------------------------------------------------------------------------------------------------*/
4 -- CONSTANTS
5 /*------------------------------------------------------------------------------------------------------------------------*/
6 G_ACTION_PERIOD_CLOSE constant varchar2(30) := 'PERIOD_CLOSE';
7 G_ACTION_SWEEP constant varchar2(30) := 'SWEEP';
8 G_ACTION_UTR constant varchar2(30) := 'UNACCT_TRX_REPORT';
9 G_ACTION_PCER constant varchar2(30) := 'PERIOD_CLOSE_EXCP_REPORT';
10
11 G_SRC_TAB_AP_INV_LINES_ALL constant varchar2(30) := 'AP_INVOICE_LINES_ALL';
12 G_SRC_TAB_AP_INV_DISTS_ALL constant varchar2(30) := 'AP_INVOICE_DISTRIBUTIONS_ALL';
13 G_SRC_TAB_AP_SELF_TAX_DIST_ALL constant varchar2(50) := 'AP_SELF_ASSESSED_TAX_DIST_ALL';
14 G_SRC_TAB_AP_PMT_HISTORY constant varchar2(30) := 'AP_PAYMENT_HISTORY';
15 G_SRC_TAB_AP_INV_PAYMENTS constant varchar2(30) := 'AP_INVOICE_PAYMENTS';
16 G_SRC_TAB_AP_PREPAY_HIST constant varchar2(30) := 'AP_PREPAY_HISTORY_ALL';
17 --Bug#7649020
18 G_SRC_TAB_XLA_AE_HEADERS constant varchar2(30) := 'XLA_AE_HEADERS';
19
20 G_SRC_TYP_LINES_WITHOUT_DISTS constant varchar2 (30) := 'LINES_WITHOUT_DISTS';
21 G_SRC_TYP_UNACCT_DISTS constant varchar2 (30) := 'UNACCT_DISTS';
22 G_SRC_TYP_UNACCT_PMT_HISTORY constant varchar2 (30) := 'UNACCT_PMT_HISTORY';
23 G_SRC_TYP_UNACCT_INV_PMTS constant varchar2 (30) := 'UNACCT_INV_PAYMENTS';
24 G_SRC_TYP_UNACCT_PREPAY_HIST constant varchar2 (30) := 'UNACCT_PREPAY_HIST';
25 --Bug#7649020
26 G_SRC_TYP_UNTRANSFERED_HEADERS constant varchar2 (30) := 'UNTRANSFERED_HEADERS';
27 G_SRC_TYP_OTHER_EXCPS constant varchar2 (30) := 'OTHER_EXCEPTIONS';
28
29 G_AP_APPLICATION_ID constant number := 200;
30 /*------------------------------------------------------------------------------------------------------------------------*/
31 -- GLOBAL DECLARATION
32 /*------------------------------------------------------------------------------------------------------------------------*/
33
34 g_ledger_id ap_system_parameters_all.set_of_books_id%type ;
35 g_org_id ap_system_parameters_all.org_id%type;
36 g_period_name gl_periods.period_name%type;
37 g_period_start_date gl_period_statuses.start_date%type;
38 g_period_end_date gl_period_statuses.end_date%type;
39 g_action varchar2 (30);
40 g_sweep_to_period gl_periods.period_name%type;
41 g_sweep_to_date gl_periods.start_date%type;
42 g_sweep_now varchar2 (1);
43 g_reporting_level number;
44 g_reporting_entity_id number (15);
45
46 g_ledger_name gl_sets_of_books.name%type;
47 g_cash_basis_flag gl_sets_of_books.sla_ledger_cash_basis_flag%type;
48
49 g_debug varchar2 (1) := 'N';
50 g_fetch_limit number := 1000; -- per Perf Team, this is std limit size.
51
52 g_orphan_message_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE := NULL;
53
54 -- Concurrent will pass date in canonical format. So we will capture
55 -- the date as passed by concurrent program in a varchar2 format and
56 -- will use canonical_to_date function to conver it to a date
57 g_start_date varchar2 (30);
58 g_end_date varchar2 (30);
59
60 cursor c_get_all_orgs
61 is
62 select hou.name operating_unit_name
63 , hou.organization_id org_id
64 , aspa.recon_accounting_flag
65 , aspa.when_to_account_pmt
66 , aspa.set_of_books_id
67 from hr_operating_units hou
68 ,ap_system_parameters_all aspa
69 where aspa.org_id = hou.organization_id
70 and ( ( g_ledger_id is not null and aspa.set_of_books_id = g_ledger_id)
71 and (g_org_id is null OR (g_org_id is not null and aspa.org_id = g_org_id))
72 )
73 and trunc(sysdate) between hou.date_from and nvl(hou.date_to,trunc(sysdate))
74 order by org_id desc;
75
76 /*------------------------------------------------------------------------------------------------------------------------*/
77 -- GLOBAL FUNCTIONS/PROCEDURES
78 /*------------------------------------------------------------------------------------------------------------------------*/
79
80 --
81 -- process_period
82 -- the main procedure to process period closing activity.
83 -- overall flow is as below:
84 -- 1. populate global variables to hold parameter values
85 -- 2. derive missing parameters values based on the available parameters
86 -- 3. populate ap_org_attributes_gt to hold all the orgs defined for a ledger
87 -- 4. validate action (PERIOD_CLOSE, SWEEP, UTR, PCER)
88 -- 5. populate the global temp table if action is not PERIOD_CLOSE
89 -- 6. based on action either SWEEP or run UTR or PCER reports
90 --
91
92 procedure process_period
93 ( p_ledger_id in number default null
94 ,p_org_id in number default null
95 ,p_period_name in varchar2 default null
96 ,p_period_start_date in date default null
97 ,p_period_end_date in date default null
98 ,p_sweep_to_period in varchar2 default null
99 ,p_action in varchar2
100 ,p_debug in varchar2 default 'N'
101 ,p_process_flag out nocopy varchar2
102 ,p_process_message out nocopy varchar2
103 );
104
105 --
106 -- before report trigger for XMLP reports. XMLP concurrent program will invoke
107 -- this method before executing the report queries in that particular data template
108 --
109 function before_report_apxpcer
110 return boolean;
111
112 function before_report_apxuatr
113 return boolean;
114
115 --
116 -- function to get name of reporting context which can be either a ledger name or operating unit name
117 --
118
119 function get_reporting_context
120 return varchar2;
121
122 function get_reporting_level_name
123 return varchar2;
124
125 --
126 -- Checks if all the operating units defined under ledger are accessible
127 -- p_process_flag = 'SS' indicates all the operating units for a ledger
128 -- are accessible
129 --
130
131 procedure check_orgs_for_ledger
132 (p_ledger_id in number
133 ,p_process_flag out nocopy varchar2
134 ,p_process_message out nocopy varchar2
135 );
136
137
138
139 end ap_period_close_pkg;