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