1 PACKAGE BODY GCS_RP_UTILITY_PKG AS
2
3 --Public Procedure and Function Definitions
4
5 --
6 -- Procedure
7 -- create_entry_lines
8 -- Purpose
9 -- Generated SQL statement to insert data into gcs_entry_lines from gcs_entries_gt
10 --
11 -- Arguments
12 -- p_entry_id: entry identifier
13 -- p_row_count: #of rows inserted
14 --
15 PROCEDURE create_entry_lines (p_entry_id IN NUMBER,
16 p_offset_flag IN VARCHAR2,
17 p_row_count IN OUT NOCOPY NUMBER)
18 IS
19 l_elimtb_y_n VARCHAR2(1) := 'Y';
20 BEGIN
21
22 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
23 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs_rp_utility_pkg.begin', null);
24 end if;
25
26 begin
27 select 'N'
28 into l_elimtb_y_n
29 from gcs_entries_gt geg
30 where formula_text NOT LIKE '%ELIMTB%'
31 and rownum < 2;
32 exception when others then l_elimtb_y_n := 'Y';
33 end;
34
35 if (l_elimtb_y_n = 'N') then
36 insert into gcs_entry_lines
37 ( entry_id,
38 company_cost_center_org_id,
39 intercompany_id,
40 ytd_debit_balance_e,
41 ytd_credit_balance_e,
42 ytd_balance_e,
43 creation_date,
44 created_by,
45 last_updated_by,
46 last_update_date,
47 last_update_login
48 )
49 SELECT p_entry_id,
50 min(geg.tgt_company_cost_center_org_id),
51 min(geg.tgt_intercompany_id),
52 sum(decode(sign(geg.output_amount), 1,
53 geg.output_amount, 0)),
54 sum(decode(sign(geg.output_amount), -1,
55 -1 *geg.output_amount, 0)),
56 sum(geg.output_amount),
57 sysdate,
58 fnd_global.user_id,
59 fnd_global.user_id,
60 sysdate,
61 fnd_global.login_id
62 FROM gcs_entries_gt geg
63 GROUP BY geg.tgt_line_item_id
64 ;
65
66 --check number of rows inserted
67 p_row_count := SQL%ROWCOUNT;
68
69 --insert rows if the offset flag was used
70 if (p_offset_flag = 'Y') then
71 insert into gcs_entry_lines
72 ( entry_id,
73 company_cost_center_org_id,
74 intercompany_id,
75 ytd_debit_balance_e,
76 ytd_credit_balance_e,
77 ytd_balance_e,
78 creation_date,
79 created_by,
80 last_updated_by,
81 last_update_date,
82 last_update_login
83 )
84 SELECT p_entry_id,
85 min(geg.tgt_company_cost_center_org_id),
86 min(geg.tgt_intercompany_id),
87 sum(decode(sign(geg.output_amount), -1,
88 -1 * geg.output_amount, 0)),
89 sum(decode(sign(geg.output_amount), 1,
90 geg.output_amount, 0)),
91 -1 * sum(geg.output_amount),
92 sysdate,
93 fnd_global.user_id,
94 fnd_global.user_id,
95 sysdate,
96 fnd_global.login_id
97 FROM gcs_entries_gt geg
98 GROUP BY geg.off_line_item_id
99 ;
100 end if; --p_offset_flag = Y
101 else
102 insert into gcs_entry_lines
103 ( entry_id,
104 company_cost_center_org_id,
105 intercompany_id,
106 ytd_debit_balance_e,
107 ytd_credit_balance_e,
108 ytd_balance_e,
109 creation_date,
110 created_by,
111 last_updated_by,
112 last_update_date,
113 last_update_login
114 )
115 SELECT p_entry_id,
116 geg.src_company_cost_center_org_id,
117 min(geg.tgt_intercompany_id),
118 sum(decode(sign(geg.output_amount), 1,
119 geg.output_amount, 0)),
120 sum(decode(sign(geg.output_amount), -1,
121 -1 *geg.output_amount, 0)),
122 sum(geg.output_amount),
123 sysdate,
124 fnd_global.user_id,
125 fnd_global.user_id,
126 sysdate,
127 fnd_global.login_id
128 FROM gcs_entries_gt geg
129 GROUP BY geg.src_company_cost_center_org_id, geg.tgt_line_item_id
130 ;
131
132 --check number of rows inserted
133 p_row_count := SQL%ROWCOUNT;
134
135 --insert rows if the offset flag was used
136 if (p_offset_flag = 'Y') then
137 insert into gcs_entry_lines
138 ( entry_id,
139 company_cost_center_org_id,
140 intercompany_id,
141 ytd_debit_balance_e,
142 ytd_credit_balance_e,
143 ytd_balance_e,
144 creation_date,
145 created_by,
146 last_updated_by,
147 last_update_date,
148 last_update_login
149 )
150 SELECT p_entry_id,
151 geg.src_company_cost_center_org_id,
152 min(geg.tgt_intercompany_id),
153 sum(decode(sign(geg.output_amount), -1,
154 -1 * geg.output_amount, 0)),
155 sum(decode(sign(geg.output_amount), 1,
156 geg.output_amount, 0)),
157 -1 * sum(geg.output_amount),
158 sysdate,
159 fnd_global.user_id,
160 fnd_global.user_id,
161 sysdate,
162 fnd_global.login_id
163 FROM gcs_entries_gt geg
164 GROUP BY geg.src_company_cost_center_org_id, geg.off_line_item_id
165 ;
166 end if; --p_offset_flag = Y
167 end if;
168
169 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
170 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs_rp_utility_pkg.end', null);
171 end if;
172
173 end create_entry_lines;
174 END GCS_RP_UTILITY_PKG;
|
|
|