1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
|
Select
gjl.ledger_id as SET_OF_BOOKS_ID
, gcc.segment1 as SOCIETE
, gcc.segment2 as PCL
, gcc.segment5 as RESP_BUD
, gcc.segment7 as NAT_BUD
, gcc.code_combination_id as CCID
, gcc.segment8 as PROJET_CCID
, (select FFVT2.DESCRIPTION
from FND_FLEX_VALUE_SETS FFVS2,
FND_FLEX_VALUES FFV2,
FND_FLEX_VALUES_TL FFVT2
where FFV2.FLEX_VALUE_SET_ID = FFVS2.FLEX_VALUE_SET_ID
AND FFVT2.FLEX_VALUE_ID = FFV2.FLEX_VALUE_ID
AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET'
AND ffv2.flex_value = gcc.segment8
AND FFVT2.LANGUAGE = 'F') as LIB_PROJET_CCID
, gjh.je_header_id as NUM_PIECE
, fl.nls_language as LANGUAGE
, decode ( gjh.actual_flag, 'A', 'REALISE', 'E', 'ENGAGE', 'BUDGET')
as TYPE_COMPTA
, gjl.period_name as PERIODE
, gjst.user_je_source_name as ORIGINE
, ori_achats.vendor_name
as FOURNISSEUR
, pha.SEGMENT1 as NUM_CDE
, Null
as NUM_FACT
,ori_achats.location_code as LIVRAISON
, gjh.doc_sequence_value as DOCUMENT
, gjh.name as DESCRIPTION
, nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
as DATE_VALEUR
, ORI_ACHATS.CATEGORIE as CATEGORIE
, ORI_ACHATS.NUM_DDI as NUM_DDI
, ORI_ACHATS.attribute1 as PROJET
, ORI_ACHATS.attribute2 as NAT_BUDGET
, nvl(ORI_ACHATS.attribute3, gcc.segment6)
as CENTRE_COUT
, ORI_ACHATS.attribute4 as RUBRIQUE_BUDGET
, to_char(ORI_ACHATS.creation_date,'DDMMYYYY')
as DATE_PO
, ORI_ACHATS.line_num as LINE_NUM
, ORI_ACHATS.item_description
as ITEM_DESCRIPTION
, to_char(ORI_ACHATS.approved_date,'DDMMYYYY')
as APPROVED_DATE
, substr(ORI_ACHATS.lib_prj,1,50)
as LIB_PROJET
, substr(ORI_ACHATS.lib_prj,51,5)
as CLARITY
, nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'B', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
as MONTANT_BUDGET
, nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'E', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
as MONTANT_ENGAGE
, nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'A', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
as MONTANT_REALISE
From gl_code_combinations gcc, gl_je_lines gjl, gl_je_headers gjh
, gl_je_sources_tl gjst, fnd_languages fl
, ( select pda1.po_header_id, pda1.po_distribution_id, pv1.vendor_name, hla1.location_code
,cat1.segment1 as CATEGORIE ,pha1.attribute2 as NUM_DDI
,pla1.attribute1 ,pla1.attribute2 ,pla1.attribute3 ,pla1.attribute4, pha1.creation_date
,pla1.line_num, pla1.item_description, pha1.approved_date,
( select FFVT2.DESCRIPTION from FND_FLEX_VALUE_SETS FFVS2
,FND_FLEX_VALUES FFV2
,FND_FLEX_VALUES_TL FFVT2
where FFV2.FLEX_VALUE_SET_ID = FFVS2.FLEX_VALUE_SET_ID
AND FFVT2.FLEX_VALUE_ID = FFV2.FLEX_VALUE_ID
AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET'
AND ffv2.flex_value = pla1.attribute1
AND FFVT2.LANGUAGE = 'F') LIB_PRJ
from po.po_headers_all pha1, po.po_distributions_all pda1, po.po_line_locations_all pll1
, AP.AP_SUPPLIERS pv1
, hr.hr_locations_all hla1, po.po_lines_all pla1
, inv.mtl_categories_b cat1
where pha1.po_header_id = pda1.po_header_id
and pll1.line_location_id = pda1.line_location_id
and hla1.location_id = nvl(pda1.deliver_to_location_id, pll1.ship_to_location_id)
and pv1.vendor_id = pha1.vendor_id
and pda1.po_line_id = pla1.po_line_id
and pla1.category_id = cat1.category_id) ORI_ACHATS
,xla.xla_ae_headers xah,XLA.XLA_TRANSACTION_ENTITIES XTE, xla.xla_ae_lines xal,
xla.XLA_DISTRIBUTION_LINKS XDL,PO.PO_HEADERS_ALL PHA,PO.PO_DISTRIBUTIONS_ALL PDA
Where 1=1
and ( to_char(ori_achats.po_header_id(+)) = PHA.PO_HEADER_ID
and to_char(ori_achats.po_distribution_id(+)) = PDA.PO_DISTRIBUTION_ID )
AND XTE.ENTITY_ID = XAH.ENTITY_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XAL.GL_SL_LINK_ID = gjl.GL_SL_LINK_ID
AND PHA.PO_HEADER_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_CODE = 'PURCHASE_ORDER'
AND XTE.APPLICATION_ID = 201
AND PDA.PO_DISTRIBUTION_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'PO_DISTRIBUTIONS_ALL'
and gjl.code_combination_id = gcc.code_combination_id
AND XAL.code_combination_id=gcc.code_combination_id
and gjh.je_header_id = gjl.je_header_id
and gjh.PERIOD_NAME = gjl.PERIOD_NAME
and gjst.je_source_name = gjh.je_source
and fl.language_code = gjst.language
and gjl.status = 'P'
and gjh.ledger_id=gjl.ledger_id
and gcc.template_id is null
group by gjl.ledger_id, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment5, gcc.segment7, gcc.code_combination_id
, gcc.segment8
, gjh.je_header_id, fl.nls_language, gjh.actual_flag, gjl.period_name, gjst.user_je_source_name
, ori_achats.vendor_name
, pha.SEGMENT1
, NULL
, ori_achats.location_code
, gjh.doc_sequence_value, gjh.name, nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
, ORI_ACHATS.CATEGORIE
, ORI_ACHATS.NUM_DDI
, ORI_ACHATS.attribute1
, ORI_ACHATS.attribute2
, nvl(ORI_ACHATS.attribute3, gcc.segment6)
, ORI_ACHATS.attribute4
, to_char(ORI_ACHATS.creation_date,'DDMMYYYY')
, ORI_ACHATS.line_num
, ORI_ACHATS.item_description
, to_char(ORI_ACHATS.approved_date,'DDMMYYYY')
, substr(ORI_ACHATS.lib_prj,1,50)
, substr(ORI_ACHATS.lib_prj,51,5)
having sum ( nvl(gjl.accounted_dr,0)+ nvl((gjl.accounted_cr*-1),0) ) !=0
UNION
Select
gjl.ledger_id as SET_OF_BOOKS_ID
, gcc.segment1 as SOCIETE
, gcc.segment2 as PCL
, gcc.segment5 as RESP_BUD
, gcc.segment7 as NAT_BUD
, gcc.code_combination_id as CCID
, gcc.segment8 as PROJET_CCID
, (select FFVT2.DESCRIPTION
from FND_FLEX_VALUE_SETS FFVS2,
FND_FLEX_VALUES FFV2,
FND_FLEX_VALUES_TL FFVT2
where FFV2.FLEX_VALUE_SET_ID = FFVS2.FLEX_VALUE_SET_ID
AND FFVT2.FLEX_VALUE_ID = FFV2.FLEX_VALUE_ID
AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET'
AND ffv2.flex_value = gcc.segment8
AND FFVT2.LANGUAGE = 'F') as LIB_PROJET_CCID
, gjh.je_header_id as NUM_PIECE
, fl.nls_language as LANGUAGE
, decode ( gjh.actual_flag, 'A', 'REALISE', 'E', 'ENGAGE', 'BUDGET')
as TYPE_COMPTA
, gjl.period_name as PERIODE
, gjst.user_je_source_name as ORIGINE
, nvl(ori_factures.vendor_name, aps.vendor_name)
as FOURNISSEUR
, ori_factures.segment1
as NUM_CDE
, AIA.INVOICE_NUM
as NUM_FACT
, ori_factures.location_code
as LIVRAISON
, gjh.doc_sequence_value as DOCUMENT
, gjh.name as DESCRIPTION
, nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
as DATE_VALEUR
, ORI_FACTURES.CATEGORIE as CATEGORIE
, ORI_FACTURES.NUM_DDI as NUM_DDI
, ORI_FACTURES.attribute1 as PROJET
, ORI_FACTURES.attribute2 as NAT_BUDGET
, ORI_FACTURES.attribute3
as CENTRE_COUT
, ORI_FACTURES.attribute4 as RUBRIQUE_BUDGET
, to_char(ORI_FACTURES.creation_date,'DDMMYYYY')
as DATE_PO
, ORI_FACTURES.line_num as LINE_NUM
, ORI_FACTURES.item_description
as ITEM_DESCRIPTION
, to_char(ORI_FACTURES.approved_date,'DDMMYYYY')
as APPROVED_DATE
, substr(ORI_FACTURES.lib_prj,1,50)
as LIB_PROJET
, substr(ORI_FACTURES.lib_prj,51,5)
as CLARITY
, nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'B', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
as MONTANT_BUDGET
, nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'E', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
as MONTANT_ENGAGE
, nvl ( sum ( to_number ( decode ( gjh.actual_flag, 'A', nvl(gjl.accounted_dr,0) + nvl((gjl.accounted_cr*-1),0) ) ) ), 0)
as MONTANT_REALISE
From gl_code_combinations gcc, gl_je_lines gjl, gl_je_headers gjh
, gl_je_sources_tl gjst, fnd_languages fl
, ( select aida2.INVOICE_DISTRIBUTION_ID, aida2.invoice_id, aida2.distribution_line_number, pha2.segment1, hla2.location_code
,cat2.segment1 as CATEGORIE ,pha2.attribute2 as NUM_DDI
,pla2.attribute1 ,pla2.attribute2 ,pla2.attribute3 ,pla2.attribute4, pha2.creation_date
,pla2.line_num, pla2.item_description, pha2.approved_date, pv2.vendor_name,
( select FFVT2.DESCRIPTION from FND_FLEX_VALUE_SETS FFVS2
,FND_FLEX_VALUES FFV2
,FND_FLEX_VALUES_TL FFVT2
where FFV2.FLEX_VALUE_SET_ID = FFVS2.FLEX_VALUE_SET_ID
AND FFVT2.FLEX_VALUE_ID = FFV2.FLEX_VALUE_ID
AND FFVS2.FLEX_VALUE_SET_NAME = 'FR00_PROJET'
AND ffv2.flex_value = pla2.attribute1
AND FFVT2.LANGUAGE = 'F') LIB_PRJ
from ap_invoice_distributions_all aida2
, po.po_headers_all pha2, po.po_distributions_all pda2, po.po_line_locations_all pll2
, hr.hr_locations_all hla2, po.po_lines_all pla2
--, inv.mtl_categories_b cat2, po.po_vendors pv2-- HABA v1.0.00 le 10/10/2018 => Remplacement de la table po_vendors par AP_SUPPLIERS
, inv.mtl_categories_b cat2, AP.AP_SUPPLIERS pv2
where pda2.po_distribution_id = aida2.po_distribution_id
AND pda2.org_id=aida2.org_id
and pha2.po_header_id = pda2.po_header_id
and pll2.line_location_id = pda2.line_location_id
and hla2.location_id = nvl(pda2.deliver_to_location_id, pll2.ship_to_location_id)
and pda2.po_line_id = pla2.po_line_id
and pla2.category_id = cat2.category_id
and pha2.vendor_id = pv2.vendor_id ) ORI_FACTURES
,xla.xla_ae_headers xah,XLA.XLA_TRANSACTION_ENTITIES XTE, xla.xla_ae_lines xal,
xla.XLA_DISTRIBUTION_LINKS XDL,AP.AP_INVOICES_ALL AIA,AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA, AP.AP_SUPPLIERS APS
Where 1=1
--AHAM Migration R12
and ( to_char(ori_factures.invoice_id(+)) = AIA.INVOICE_ID
and to_char(ori_factures.distribution_line_number(+)) = AIDA.distribution_line_number )
AND XTE.ENTITY_ID = XAH.ENTITY_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID = gjl.GL_SL_LINK_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XAL.SOURCE_TABLE = 'AP_INVOICE_DISTRIBUTIONS'
AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
AND XTE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND AIDA.INVOICE_DISTRIBUTION_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND AIA.vendor_id=APS.vendor_id
and gjl.code_combination_id = gcc.code_combination_id
AND XAL.code_combination_id=gcc.code_combination_id
and gjh.ledger_id=gjl.ledger_id
and gjh.PERIOD_NAME = gjl.PERIOD_NAME
and gjh.je_header_id = gjl.je_header_id
and gjst.je_source_name = gjh.je_source
and fl.language_code = gjst.language
and gjl.status = 'P'
and gcc.template_id is null
group by gjl.ledger_id, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment5, gcc.segment7, gcc.code_combination_id
, gcc.segment8
, gjh.je_header_id, fl.nls_language, gjh.actual_flag, gjl.period_name, gjst.user_je_source_name
, nvl(ori_factures.vendor_name, aps.vendor_name)
, ori_factures.segment1
, AIA.INVOICE_NUM
, ori_factures.location_code
, gjh.doc_sequence_value, gjh.name, nvl ( gjl.attribute14, to_char(gjh.default_effective_date, 'DDMMYYYY') )
, ORI_FACTURES.CATEGORIE
, ORI_FACTURES.NUM_DDI
, ORI_FACTURES.attribute1
, ORI_FACTURES.attribute2
, ORI_FACTURES.attribute3
, ORI_FACTURES.attribute4
, to_char(ORI_FACTURES.creation_date,'DDMMYYYY')
, ORI_FACTURES.line_num
, ORI_FACTURES.item_description
, to_char(ORI_FACTURES.approved_date,'DDMMYYYY')
, substr(ORI_FACTURES.lib_prj,1,50)
, substr(ORI_FACTURES.lib_prj,51,5)
having sum ( nvl(gjl.accounted_dr,0)+ nvl((gjl.accounted_cr*-1),0) ) !=0; |
Problème de performance sur une requête SQL
Répondre avec citation
Partager