API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 22nd, 2018
341
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 25.75 KB | None | 0 0
raw download clone embed print report
  1.  
  2. CREATE OR REPLACE FORCE EDITIONABLE VIEW "INTERFACE"."STEP_01_CA_DATA" ("ALLOCATEDDOCUMENT_ID", "GENERALLEDGERACCOUNT_ID", "CONTRACT_ID", "CONTRACTTYPE_ENUMID", "FLEETVEHICLE_ID", "BUSINESSUNIT_ID", "AMOUNT", "VATCODE_ENUMID", "ACCOUNTINGMODULEENUMID") AS
  3. select ca.allocateddocument_id
  4. , gla.generalledgeraccount_id
  5. , case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then null
  6. else (case when ctr_cc.contracttype_enumID=895 then ' 049999' else ctr_cc.reference end) end as contract_id
  7. --, ctr_cc.contract_id
  8. , ctr_cc.contracttype_enumid
  9. , fv_cc.fleetvehicle_id
  10. , bu_cc.businessunit_id
  11. , sum(ca.amount) as AMOUNT
  12. , ca.VATCODE_ENUMID
  13. , gla.ACCOUNTINGMODULEENUMID
  14. from "MILES".costallocation ca
  15. join "MILES".generalledgeraccount gla on gla.generalledgeraccount_id = ca.generalledgeraccount_id
  16. left outer join (select cacc.costallocation_id, cc0.contract_id, ct.contracttype_enumID, ct.reference from
  17. "MILES".costallcostcenter cacc
  18. join "MILES".costcenter cc0 on cc0.costcenter_id = cacc.costcenter_id and cc0.costcentertype_enumid = 436
  19. join "MILES".contract ct on ct.contract_id=cc0.contract_id) ctr_cc
  20. on ctr_cc.costallocation_id = ca.costallocation_id
  21. left outer join (select cacc.costallocation_id, cc0.fleetvehicle_id from
  22. "MILES".costallcostcenter cacc
  23. join "MILES".costcenter cc0 on cc0.costcenter_id = cacc.costcenter_id and cc0.costcentertype_enumid = 435) fv_cc
  24. on fv_cc.costallocation_id = ca.costallocation_id
  25. left outer join (select cacc.costallocation_id, cc0.businessunit_id from
  26. "MILES".costallcostcenter cacc
  27. join "MILES".costcenter cc0 on cc0.costcenter_id = cacc.costcenter_id and cc0.costcentertype_enumid = 437) bu_cc
  28. on bu_cc.costallocation_id = ca.costallocation_id
  29. where gla.GENERALLEDGERACCOUNT_ID<>401892 --20160720--
  30. group by ca.allocateddocument_id--,ca.costallocation_id
  31. , gla.generalledgeraccount_id
  32. , case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then null
  33. else (case when ctr_cc.contracttype_enumID=895 then ' 049999' else ctr_cc.reference end) end
  34. , ctr_cc.contracttype_enumid
  35. , fv_cc.fleetvehicle_id
  36. , bu_cc.businessunit_id
  37. , ca.VATCODE_ENUMID
  38. , gla.ACCOUNTINGMODULEENUMID
  39. HAVING SUM (CA.AMOUNT) != 0
  40.  
  41. union --20160720--
  42.  
  43. select ca.allocateddocument_id
  44. , gla.generalledgeraccount_id
  45. , case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then null
  46. else (case when ctr_cc.contracttype_enumID=895 then ' 049999' else ctr_cc.reference end) end as contract_id
  47. --, ctr_cc.contract_id
  48. , ctr_cc.contracttype_enumid
  49. , fv_cc.fleetvehicle_id
  50. , bu_cc.businessunit_id
  51. , sum(ca.amount) as AMOUNT
  52. , min(ca.VATCODE_ENUMID)
  53. , gla.ACCOUNTINGMODULEENUMID
  54. from "MILES".costallocation ca
  55. join "MILES".generalledgeraccount gla on gla.generalledgeraccount_id = ca.generalledgeraccount_id
  56. left outer join (select cacc.costallocation_id, cc0.contract_id, ct.contracttype_enumID, ct.reference from
  57. "MILES".costallcostcenter cacc
  58. join "MILES".costcenter cc0 on cc0.costcenter_id = cacc.costcenter_id and cc0.costcentertype_enumid = 436
  59. join "MILES".contract ct on ct.contract_id=cc0.contract_id) ctr_cc
  60. on ctr_cc.costallocation_id = ca.costallocation_id
  61. left outer join (select cacc.costallocation_id, cc0.fleetvehicle_id from
  62. "MILES".costallcostcenter cacc
  63. join "MILES".costcenter cc0 on cc0.costcenter_id = cacc.costcenter_id and cc0.costcentertype_enumid = 435) fv_cc
  64. on fv_cc.costallocation_id = ca.costallocation_id
  65. left outer join (select cacc.costallocation_id, cc0.businessunit_id from
  66. "MILES".costallcostcenter cacc
  67. join "MILES".costcenter cc0 on cc0.costcenter_id = cacc.costcenter_id and cc0.costcentertype_enumid = 437) bu_cc
  68. on bu_cc.costallocation_id = ca.costallocation_id
  69. where gla.GENERALLEDGERACCOUNT_ID=401892
  70. group by ca.allocateddocument_id--,ca.costallocation_id
  71. , gla.generalledgeraccount_id
  72. , case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then null
  73. else (case when ctr_cc.contracttype_enumID=895 then ' 049999' else ctr_cc.reference end) end
  74. , ctr_cc.contracttype_enumid
  75. , fv_cc.fleetvehicle_id
  76. , bu_cc.businessunit_id
  77. , gla.ACCOUNTINGMODULEENUMID
  78. HAVING SUM (CA.AMOUNT) != 0;
  79.  
  80. CREATE OR REPLACE FORCE EDITIONABLE VIEW "INTERFACE"."STEP_01_PA_DATA_IN" ("ALLOCATEDDOCUMENT_ID", "PAYABLE_ID", "PAYABLE_ICS", "PAYABLE_OI", "GENERALLEDGERACCOUNT_ID", "CONTRACT_ID", "CONTRACTTYPE_ENUMID", "FLEETVEHICLE_ID", "BUSINESSUNIT_ID", "AMOUNT", "ACCOUNTINGMODULEENUMID") AS
  81. Select ics.allocateddocument_id
  82. , p.payable_id
  83. , p.incomingcoststate_id as payable_ics
  84. , NULL as payable_oi
  85. , gla.generalledgeraccount_id
  86. , case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then null
  87. else (case when ctr_cc.contracttype_enumID=895 then ' 049999' else ctr_cc.reference end) end as contract_id
  88. , ctr_cc.contracttype_enumID
  89. , fv_cc.fleetvehicle_id
  90. , bu_cc.businessunit_id
  91. , p.amount
  92. , gla.ACCOUNTINGMODULEENUMID
  93. from "MILES".payable p
  94. join "MILES".incomingcoststate ics on ics.incomingcoststate_id = p.incomingcoststate_id
  95. join "MILES".generalledgeraccount gla on gla.generalledgeraccount_id = p.generalledgeraccount_id
  96. left outer join (select pcc.payable_id, cc0.contract_id, ct.contracttype_enumID, ct.reference from
  97. "MILES".payablecostcenter pcc
  98. join "MILES".costcenter cc0 on cc0.costcenter_id = pcc.costcenter_id and cc0.costcentertype_enumid = 436
  99. join "MILES".contract ct on ct.contract_id=cc0.contract_id) ctr_cc
  100. on ctr_cc.payable_id = p.payable_id
  101. left outer join (select pcc.payable_id, cc0.fleetvehicle_id from
  102. "MILES".payablecostcenter pcc
  103. join "MILES".costcenter cc0 on cc0.costcenter_id = pcc.costcenter_id and cc0.costcentertype_enumid = 435) fv_cc
  104. on fv_cc.payable_id = p.payable_id
  105. left outer join (select pcc.payable_id, cc0.businessunit_id from
  106. "MILES".payablecostcenter pcc
  107. join "MILES".costcenter cc0 on cc0.costcenter_id = pcc.costcenter_id and cc0.costcentertype_enumid = 437) bu_cc
  108. on bu_cc.payable_id = p.payable_id
  109. where p.payment_id is null;
  110.  
  111.  
  112. CREATE OR REPLACE FORCE EDITIONABLE VIEW "INTERFACE"."STEP_01_PA_DATA_OUT" ("ALLOCATEDDOCUMENT_ID", "PAYABLE_ID", "PAYABLE_ICS", "OUTGOINGINVOICE_ID", "GENERALLEDGERACCOUNT_ID", "CONTRACT_ID", "CONTRACTTYPE_ENUMID", "FLEETVEHICLE_ID", "BUSINESSUNIT_ID", "AMOUNT", "ACCOUNTINGMODULEENUMID") AS
  113. select oi.allocateddocument_id
  114. , p.payable_id
  115. , NULL as payable_ics
  116. , p.outgoinginvoice_id
  117. , gla.generalledgeraccount_id
  118. , null as contract_id
  119. , null as contracttype_enumID
  120. , null as fleetvehicle_id
  121. , null as businessunit_id
  122. , p.amount
  123. , gla.ACCOUNTINGMODULEENUMID
  124. from "MILES".payable p
  125. join "MILES".generalledgeraccount gla on gla.generalledgeraccount_id = p.generalledgeraccount_id
  126. join "MILES".outgoinginvoice oi on oi.outgoinginvoice_id = p.outgoinginvoice_id
  127. where p.payment_id is null;
  128.  
  129.  
  130. CREATE OR REPLACE FORCE EDITIONABLE VIEW "INTERFACE"."STEP_01_POSTDATE" ("ALLOCATEDDOCUMENT_ID", "TRANSITIONDATE") AS
  131. Select oi1.allocateddocument_id
  132. , lc1.transitiondate
  133. from "MILES".outgoinginvoice oi1
  134. inner join "MILES".relobject ro1 on ro1.object_id=oi1.outgoinginvoice_id
  135. inner join "MILES".lifecycle lc1 on lc1.lifecycle_id=ro1.lifecycle_id
  136. where lc1.transitionstate=614
  137. and ro1.sysrepobject_id=288
  138.  
  139. union
  140.  
  141. Select ics1.allocateddocument_id
  142. , lc2.transitiondate
  143. from "MILES".incomingcoststate ics1
  144. inner join "MILES".relobject ro2 on ro2.object_id=ics1.incomingcoststate_id
  145. inner join "MILES".lifecycle lc2 on lc2.lifecycle_id=ro2.lifecycle_id
  146. where lc2.transitionstate=614
  147. and ro2.sysrepobject_id=252;
  148.  
  149.  
  150.  
  151. CREATE OR REPLACE FORCE EDITIONABLE VIEW "INTERFACE"."INVOICES4SAP_BASE" ("DOCUMENTTYPE_ENUMID", "ALLOCATEDDOCUMENT_ID", "COSTALLOCATION_ID", "NDBTR_ACCOUNT", "NDBTR_BKDATE", "NDBTR_BKPERI", "NDBTR_AMOUNT", "NDBTR_CURREN", "NDBTR_SIGN", "NDBTR_DUDATE", "NDBTR_BKCODE", "NDBTR_BKREFE", "NDBTR_LIBELLE", "NDBTR_CTRNU", "NDBTR_VEHHNU", "NDBTR_CUSTNU", "NDBTR_PRODUC", "NDBTR_CHANN1", "NDBTR_CHANN2", "NDBTR_VAT", "NDBTR_SEQUEN", "NDBTR_PLATE", "NDBTR_ACCOUN_LIB", "NDBTR_THIRD_NAME", "NDBTR_BANK_REF", "NDBTR_COST_CENTER", "NDBTR_SUP_CODE_PAYM", "NDBTR_SUP_PAYMENTDELAY", "NDBTR_RAGR_CODE", "NDBTR_CREP_COST", "STARTCOSTPERIOD", "ENDCOSTPERIOD", "NDBTR_ACCOUNT_MODULE") AS
  152. select distinct
  153. SAP_RESULT.documenttype_enumid AS documenttype_enumid,
  154. SAP_RESULT.allocateddocument_id AS allocateddocument_id,
  155. SAP_RESULT.allocateddocument_id as costallocation_ID,
  156. SAP_RESULT.NDBTR_ACCOUNT AS NDBTR_ACCOUNT,
  157. SAP_RESULT.NDBTR_BKDATE AS NDBTR_BKDATE,
  158. SAP_RESULT.NDBTR_BKPERI AS NDBTR_BKPERI,
  159. sum(SAP_RESULT.NDBTR_AMOUNT) AS NDBTR_AMOUNT, --20160411--
  160. SAP_RESULT.NDBTR_CURREN AS NDBTR_CURREN,
  161. SAP_RESULT.NDBTR_SIGN AS NDBTR_SIGN,
  162. SAP_RESULT.NDBTR_DUDATE AS NDBTR_DUDATE,
  163. SAP_RESULT.NDBTR_BKCODE AS NDBTR_BKCODE,
  164. SAP_RESULT.NDBTR_BKREFE AS NDBTR_BKREFE,
  165. SAP_RESULT.NDBTR_LIBELLE AS NDBTR_LIBELLE,
  166. SAP_RESULT.NDBTR_CTRNU AS NDBTR_CTRNU,
  167. SAP_RESULT.NDBTR_VEHHNU AS NDBTR_VEHHNU,
  168. SAP_RESULT.NDBTR_CUSTNU AS NDBTR_CUSTNU,
  169. SAP_RESULT.NDBTR_PRODUC AS NDBTR_PRODUC,
  170. SAP_RESULT.NDBTR_CHANN1 AS NDBTR_CHANN1,
  171. SAP_RESULT.NDBTR_CHANN2 AS NDBTR_CHANN2,
  172. SAP_RESULT.NDBTR_VAT AS NDBTR_VAT,
  173. SAP_RESULT.NDBTR_SEQUEN AS NDBTR_SEQUEN,
  174. SAP_RESULT.NDBTR_PLATE AS NDBTR_PLATE,
  175. SAP_RESULT.NDBTR_ACCOUN_LIB AS NDBTR_ACCOUN_LIB,
  176. SAP_RESULT.NDBTR_THIRD_NAME AS NDBTR_THIRD_NAME,
  177. SAP_RESULT.NDBTR_BANK_REF AS NDBTR_BANK_REF,
  178. SAP_RESULT.NDBTR_COST_CENTER AS NDBTR_COST_CENTER,
  179. SAP_RESULT.NDBTR_SUP_CODE_PAYM AS NDBTR_SUP_CODE_PAYM,
  180. SAP_RESULT.NDBTR_SUP_PAYMENTDELAY AS NDBTR_SUP_PAYMENTDELAY,
  181. SAP_RESULT.NDBTR_RAGR_CODE AS NDBTR_RAGR_CODE,
  182. SAP_RESULT.NDBTR_CREP_COST AS NDBTR_CREP_COST,
  183. SAP_RESULT.STARTCOSTPERIOD as startcostperiod,
  184. SAP_RESULT.ENDCOSTPERIOD as endcostperiod,
  185. SAP_RESULT.NDBTR_ACCOUNT_MODULE AS NDBTR_ACCOUNT_MODULE
  186.  
  187. from (
  188. select distinct --20160115--
  189. ad.documenttype_enumid,
  190. ad.allocateddocument_id,
  191. --ca.costallocation_ID, --20160204--
  192. case when mq.type='R2C' or mq.type='R2D' then '4137' else --20160501--
  193. (case when gla.generalledgeraccount_id=402135
  194. then
  195. (case when CAST(cu.reference as INT)>10000000 then cu.reference else substr(cu.reference,3,6) end) --20160902--
  196. else
  197. (case when gla.generalledgeraccount_id=402142
  198. then su.reference
  199. else
  200. (case when gla.generalledgeraccount_id=401976 then '4510' else
  201. (case when gla.generalledgeraccount_id=401892 then'4136' else gla.account end)end)
  202. end)
  203. end) end as NDBTR_ACCOUNT, --20160501--
  204. case when ad.documenttype_enumID=1102 then ics.coststatedate else oi.invoicedate end as NDBTR_BKDATE, --20160623--
  205. case when ad.documenttype_enumID=1102 then postdate.transitiondate else oi.invoicedate end as NDBTR_BKPERI, --20160623--
  206. case when mq.type='R2C' or mq.type='R2D' then abs(mq.amount) else
  207. (case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then
  208. (case when mq.documenttype_enumID=1102 then abs(ics.coststateamount) else abs(oi.totalinvoiceamount) end) else
  209. abs(mq.amount) end) end as NDBTR_AMOUNT, --20160411--
  210. 'EUR' as NDBTR_CURREN,
  211. case when mq.type='R2C' then 'C' else
  212. (case when mq.type ='R2D' then 'D' else
  213. (case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then --receivable, payable--
  214. (case when mq.documenttype_enumID=1102 then --incoming invoice--
  215. (case when mq.ics_invoicetype_enumID=516 then 'C' else 'D' end) else --invoice--
  216. (case when mq.oi_invoicetype_enumID in (516,520) then 'D' else 'C' end) end) else
  217. (case when mq.amount < 0 then 'C' else 'D' end) end ) end) end as NDBTR_SIGN, --20160411--
  218. case when mq.incomingcoststate_id is null then oi.duedate else ics.duedate end as NDBTR_DUDATE, --20160411--
  219. substr(getenumml(ad.journaltype_enumid, 1), 1, 3) as NDBTR_BKCODE,
  220. CASE
  221. WHEN mq.incomingcoststate_id IS NULL
  222. THEN
  223. CASE
  224. WHEN LENGTH (OI.INVOICENR) > 10
  225. THEN
  226. SUBSTR (oi.invoicenr, 6)
  227. || TO_CHAR (ad.postdate, 'YYYYMM')
  228. ELSE
  229. SUBSTR (oi.invoicenr, 5)
  230. || TO_CHAR (ad.postdate, 'YYYYMM')
  231. END
  232. ELSE
  233. ics.invoicenumber
  234. END
  235. AS NDBTR_BKREFE, ---LDN MODIFICA PER DIGIT = 7 ---LDN MODIFICA PER DIGIT = 7
  236. cast(null as varchar2(20)) as NDBTR_LIBELLE,
  237. mq.contract_id as NDBTR_CTRNU, --20160407--
  238. case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then null else mq.fleetvehicle_id end as NDBTR_VEHHNU, --20160804--
  239. case when gla.ACCOUNTINGMODULEENUMID=2712 then su.reference else (
  240. case when mq.incomingcoststate_id is null then (
  241. case when CAST(cu.reference as INT)>10000000 then cu.reference else substr(cu.reference,3,6) end)
  242. else
  243. (case when CAST(cu2.reference as INT)>10000000 then cu2.reference else substr(cu2.reference,3,6) end)
  244. end )
  245. end as NDBTR_CUSTNU, --20160921--
  246. cast(null as varchar2(20)) as NDBTR_PRODUC, --20160905--
  247. cast(null as varchar2(20)) as NDBTR_CHANN1,
  248. cast(null as varchar2(20)) as NDBTR_CHANN2,
  249. case when mq.type='R2C' or mq.type='R2D' then 'R2' else d_vats.vatcode_sap end as NDBTR_VAT, --20160411--
  250. substr(getenumml(ad.journaltype_enumid, 1), 1, 3)||substr(ics.documentnr, 5) as NDBTR_SEQUEN,
  251. case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then null else fv.licenseplate end as NDBTR_PLATE, --20160407--
  252. substr(gla.description,8) as NDBTR_ACCOUN_LIB,
  253. case when ics.incomingcoststate_id is null then bp1.name else bp0.name end as NDBTR_THIRD_NAME,
  254. cast(null as varchar2(20)) as NDBTR_BANK_REF,
  255. cast(null as varchar2(20)) as NDBTR_COST_CENTER,
  256. case when ics.incomingcoststate_id is null then null else
  257. (select name from "MILES".sysenumeration where sysenumeration_id=d_ics.INVOICEREJECTION) end as NDBTR_SUP_CODE_PAYM, --20160115--
  258. case when ics.incomingcoststate_id is null then LPAD((oi.duedate - ad.postdate), 3, '0') else LPAD((ics.duedate-ics.coststatedate),3,'0') end as NDBTR_SUP_PAYMENTDELAY, --20160122--
  259. cast(null as varchar2(20)) as NDBTR_RAGR_CODE,
  260. cast(null as varchar2(20)) as NDBTR_CREP_COST,
  261. cast(null as varchar2(20)) as startcostperiod,
  262. cast(null as varchar2(20)) as endcostperiod,
  263. case when gla.ACCOUNTINGMODULEENUMID in (2713,2712) then 'Master' else gla.account end as NDBTR_ACCOUNT_MODULE --20160108--
  264.  
  265. from (
  266.  
  267. -- 1st BLOC > COST ALOOCATIONS > IN- and OUTGOING INVOICES --
  268. select distinct
  269. ad.lcompany_id as lcompany_id,
  270. ad.allocateddocument_id as allocateddocument_id,
  271. ad.documenttype_enumID, --20160407--
  272. oi.invoicetype_enumID as oi_invoicetype_enumID, --20160407--
  273. ics.invoicetype_enumID as ics_invoicetype_enumID, --20160407--
  274. '100' as type,
  275. oi.outgoinginvoice_id,
  276. ics.incomingcoststate_id,
  277. null,
  278. null as payable_id,
  279. ca_data.generalledgeraccount_id as generalledgeraccount_id,
  280. to_char(ca_data.contract_id) as contract_id,
  281. ca_data.fleetvehicle_id as fleetvehicle_id,
  282. ca_data.businessunit_id as businessunit_id,
  283. ca_data.amount,
  284. ca_data.vatcode_enumID, --20160501--
  285. ad.posted, --20160501--
  286. ad.transferred --20160501--
  287.  
  288. from
  289. "MILES".allocateddocument ad
  290. join "INTERFACE".STEP_01_CA_DATA ca_data on ca_data.allocateddocument_id = ad.allocateddocument_id
  291. left outer join "MILES".incomingcoststate ics on ics.allocateddocument_id = ad.allocateddocument_id
  292. left outer join "MILES".outgoinginvoice oi on oi.allocateddocument_id = ad.allocateddocument_id
  293.  
  294. where ad.documenttype_enumid in (1102,1103) --1102 = incoming invoice / 1103 = outgoing invoice
  295. --and ad.allocateddocument_id in (40331427)
  296.  
  297. UNION
  298.  
  299. -- 2ND BLOC > PAYABLES > INCOMING INVOICES --
  300. select
  301. ad.lcompany_id,
  302. ad.allocateddocument_id,
  303. ad.documenttype_enumID, --20160407--
  304. oi.invoicetype_enumID as oi_invoicetype_enumID, --20160407--
  305. ics.invoicetype_enumID as ics_invoicetype_enumID, --20160407--
  306. '200' as type,
  307. p_data.payable_oi,
  308. p_data.payable_ics,
  309. null,
  310. p_data.payable_id,
  311. p_data.generalledgeraccount_id,
  312. to_char(p_data.contract_id) as contract_id,
  313. p_data.fleetvehicle_id,
  314. p_data.businessunit_id,
  315. p_data.amount,
  316. null, --20160501--
  317. ad.posted, --20160501--
  318. ad.transferred --20160501--
  319.  
  320. from
  321. "MILES".allocateddocument ad
  322. join (
  323. select * from INTERFACE.STEP_01_PA_DATA_IN
  324. union
  325. Select * from INTERFACE.STEP_01_PA_DATA_OUT) p_data on p_data.allocateddocument_id = ad.allocateddocument_id
  326. left outer join "MILES".incomingcoststate ics on ics.allocateddocument_id = ad.allocateddocument_id and ics.incomingcoststate_id = p_data.payable_ics
  327. left outer join "MILES".outgoinginvoice oi on oi.allocateddocument_id = ad.allocateddocument_id and oi.outgoinginvoice_id = p_data.payable_oi
  328.  
  329. where ad.documenttype_enumid in (1102,1103) --1102 = incoming invoice / 1103 = outgoing invoice
  330. AND AD.AMOUNT != 0
  331.  
  332. UNION --code R2 C-- --20160411--
  333.  
  334. -- 3RD BLOC > IVA EXCEPTION > INCOMING INVOICES DEBIT --
  335. select
  336. ad.lcompany_id,
  337. ad.allocateddocument_id,
  338. ad.documenttype_enumID,
  339. null as oi_invoicetype_enumID,
  340. ics.invoicetype_enumID as ics_invoicetype_enumID,
  341. 'R2C' as type,
  342. r2c_data.payable_oi,
  343. r2c_data.payable_ics,
  344. null,
  345. r2c_data.payable_id,
  346. 401892 as r2c_generalledgeraccount_id,
  347. to_char(r2c_data.contract_id) as contract_id,
  348. r2c_data.fleetvehicle_id,
  349. r2c_data.businessunit_id,
  350. abs(ad.amount*0.22) as r2c_amount, --20160501--
  351. ca.VATCODE_ENUMID, --20160501--
  352. ad.posted, --20160501--
  353. ad.transferred --20160501--
  354.  
  355. from
  356. "MILES".allocateddocument ad
  357. join INTERFACE.STEP_01_PA_DATA_IN r2c_data on r2c_data.allocateddocument_id = ad.allocateddocument_id
  358. left outer join "MILES".incomingcoststate ics on ics.allocateddocument_id = ad.allocateddocument_id and ics.incomingcoststate_id = r2c_data.payable_ics
  359. left outer join "MILES".costallocation ca on ca.allocateddocument_id=ad.allocateddocument_id
  360.  
  361. where ad.documenttype_enumid = 1102 --1102 = incoming invoice / 1103 = outgoing invoice
  362. and ca.vatcode_enumID=400592
  363. AND AD.AMOUNT != 0
  364. --and ad.allocateddocument_id in (40331427)
  365.  
  366. UNION --code R2 D-- --20160411--
  367.  
  368. -- 4RD BLOC > IVA EXCEPTION > INCOMING INVOICES CREDIT --
  369. select
  370. ad.lcompany_id,
  371. ad.allocateddocument_id,
  372. ad.documenttype_enumID,
  373. null as oi_invoicetype_enumID,
  374. ics.invoicetype_enumID as ics_invoicetype_enumID,
  375. 'R2D' as type,
  376. r2d_data.payable_oi,
  377. r2d_data.payable_ics,
  378. null,
  379. r2d_data.payable_id,
  380. 401892 as r2d_generalledgeraccount_id,
  381. to_char(r2d_data.contract_id) as contract_id,
  382. r2d_data.fleetvehicle_id,
  383. r2d_data.businessunit_id,
  384. abs(ad.amount*0.22) as r2d_amount, --20160501--
  385. ca.VATCODE_ENUMID, --20160501--
  386. ad.posted, --20160501--
  387. ad.transferred --20160501--
  388.  
  389. from
  390. "MILES".allocateddocument ad
  391. join INTERFACE.STEP_01_PA_DATA_IN r2d_data on r2d_data.allocateddocument_id = ad.allocateddocument_id
  392. left outer join "MILES".incomingcoststate ics on ics.allocateddocument_id = ad.allocateddocument_id and ics.incomingcoststate_id = r2d_data.payable_ics
  393. left outer join "MILES".costallocation ca on ca.allocateddocument_id=ad.allocateddocument_id
  394.  
  395. where ad.documenttype_enumid = 1102 --1102 = incoming invoice / 1103 = outgoing invoice
  396. and ca.vatcode_enumID=400592
  397. AND AD.AMOUNT != 0
  398. --and ad.allocateddocument_id in (40331427)
  399.  
  400. order by allocateddocument_id desc, type desc) MQ
  401.  
  402. join "MILES".lcompany lc on lc.lcompany_id = mq.lcompany_id
  403. join "MILES".allocateddocument ad on ad.allocateddocument_id = mq.allocateddocument_id
  404. join "MILES".fiscalperiod fp on fp.fiscalperiod_id = ad.fiscalperiod_id
  405. join "MILES".generalledgeraccount gla on gla.generalledgeraccount_id = mq.generalledgeraccount_id
  406. left outer join "MILES".outgoinginvoice oi on oi.outgoinginvoice_id = mq.outgoinginvoice_id
  407. left outer join "MILES".incomingcoststate ics on ics.incomingcoststate_id = mq.incomingcoststate_id
  408. left outer join INTERFACE.STEP_01_POSTDATE POSTDATE on postdate.allocateddocument_id=mq.allocateddocument_id
  409. left outer join "MILES".d_incomingcoststate d_ics on d_ics.incomingcoststate_id = ics.incomingcoststate_id
  410. left outer join "MILES".payable p on p.payable_id = mq.payable_id
  411. left outer join "MILES".fleetvehicle fv on fv.fleetvehicle_id = mq.fleetvehicle_id
  412. left outer join "MILES".businessunit bu on bu.businessunit_id = mq.businessunit_id
  413. left outer join "MILES".businesspartner bp0 on ics.supplier_id = bp0.businesspartner_id
  414. left outer join "MILES".supplier su on su.businesspartner_ID=BP0.businesspartner_id --20160107--
  415. left outer join "MILES".businesspartner bp1 on oi.financialcustomer_id = bp1.businesspartner_id
  416. left outer join "MILES".customer cu on cu.businesspartner_ID=bp1.businesspartner_id --20160107--
  417. left outer join "MILES".vatsetting vats on vats.vatcode_enumid=mq.vatcode_enumid --20160501--
  418. left outer join "MILES".d_vatsetting d_vats on d_vats.vatsetting_id=vats.vatsetting_id
  419. left outer join "MILES".businesspartner bp2 on bp2.businessunit_id=mq.businessunit_id --20160407--
  420. left outer join "MILES".customer cu2 on cu2.businesspartner_ID=bp2.businesspartner_id --20160120--
  421.  
  422. where mq.posted=1
  423. and mq.transferred=0
  424. --and mq.allocateddocument_id in (40331427)
  425.  
  426. ) SAP_RESULT
  427.  
  428. group by --20160411--
  429. SAP_RESULT.documenttype_enumid, SAP_RESULT.allocateddocument_id, SAP_RESULT.NDBTR_ACCOUNT, SAP_RESULT.NDBTR_BKDATE,
  430. SAP_RESULT.NDBTR_BKPERI, SAP_RESULT.NDBTR_CURREN, SAP_RESULT.NDBTR_SIGN,
  431. SAP_RESULT.NDBTR_DUDATE, SAP_RESULT.NDBTR_BKCODE, SAP_RESULT.NDBTR_BKREFE, SAP_RESULT.NDBTR_LIBELLE, SAP_RESULT.NDBTR_CTRNU,
  432. SAP_RESULT.NDBTR_VEHHNU, SAP_RESULT.NDBTR_CUSTNU, SAP_RESULT.NDBTR_PRODUC,
  433. SAP_RESULT.NDBTR_CHANN1, SAP_RESULT.NDBTR_CHANN2, SAP_RESULT.NDBTR_VAT, SAP_RESULT.NDBTR_SEQUEN, SAP_RESULT.NDBTR_PLATE,
  434. SAP_RESULT.NDBTR_ACCOUN_LIB, SAP_RESULT.NDBTR_THIRD_NAME, SAP_RESULT.NDBTR_BANK_REF, SAP_RESULT.NDBTR_COST_CENTER, SAP_RESULT.NDBTR_SUP_CODE_PAYM,
  435. SAP_RESULT.NDBTR_SUP_PAYMENTDELAY, SAP_RESULT.NDBTR_RAGR_CODE, SAP_RESULT.NDBTR_CREP_COST, SAP_RESULT.STARTCOSTPERIOD, SAP_RESULT.ENDCOSTPERIOD,
  436. SAP_RESULT.NDBTR_ACCOUNT_MODULE
  437.  
  438. order by SAP_RESULT.NDBTR_BKDATE,SAP_RESULT.NDBTR_BKCODE, SAP_RESULT.NDBTR_BKREFE, SAP_RESULT.ndbtr_sign DESC, SAP_RESULT.ndbtr_ctrnu desc;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!