API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 31st, 2014
189
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 20.65 KB | None | 0 0
raw download clone embed print report
  1. -- noinspection SqlResolve
  2. IF exists(SELECT
  3. 1
  4. FROM sysObjects
  5. WHERE id = object_id('insertIntoP3ES')
  6. AND type = 'P')
  7. DROP PROCEDURE insertIntoP3ES
  8. GO
  9. CREATE PROCEDURE insertIntoP3ES(
  10. @BP3_REF CHAR(14),
  11. @BP3_TRSL CHAR(1),
  12. @BP3_PR_OUT CHAR(1),
  13. @BP3_BRNM CHAR(4),
  14. @BP3_FLMN CHAR(2),
  15. @EP3_PYT CHAR(3),
  16. @BP3_MT CHAR(1),
  17. @BP3_XCP CHAR(1),
  18. @BP3_APP CHAR(2),
  19. @BP3_DLP CHAR(3),
  20. @BP3_REFS VARCHAR(16),
  21. @BP3_REF_MUR VARCHAR(16),
  22. @BP3_PR_S CHAR(1),
  23. @BP3_PR_SYS CHAR(1),
  24. @BP3_PR_SEP CHAR(1),
  25. @BP3_PR_VIZ CHAR(1),
  26. @BP3_FL_EQ CHAR(1),
  27. @BP3_USID_A VARCHAR(12),
  28. @BP3_USID_R VARCHAR(12),
  29. @BP3_USID_V VARCHAR(12),
  30. @BP3_USID_N VARCHAR(12),
  31. @BP3_USID_U1 VARCHAR(12),
  32. @BP3_USID_U2 VARCHAR(12),
  33. @EP3_AMTE CHAR(1),
  34. @BP3_TRM CHAR(2),
  35. @BP3_DAT_KL DATETIME,
  36. @BP3_P_DAT_OD DATETIME,
  37. @BP3_P_CCY CHAR(3),
  38. @BP3_P_ACC VARCHAR(34),
  39. @BP3_P_AMT NUMERIC(15, 2),
  40. @BP3_P_NMT NUMERIC(15, 2),
  41. @BP3_R_ACC VARCHAR(34),
  42. @BP3_O_CA1 NVARCHAR(35),
  43. @BP3_O_CA2 NVARCHAR(35),
  44. @BP3_O_CA3 NVARCHAR(35),
  45. @BP3_O_CA4 NVARCHAR(35),
  46. @BP3_O_SWB CHAR(4),
  47. @BP3_O_CNA CHAR(2),
  48. @BP3_O_SWL CHAR(2),
  49. @BP3_O_SWR CHAR(3),
  50. @BP3_O_AD1 NVARCHAR(35),
  51. @BP3_O_AD2 NVARCHAR(35),
  52. @BP3_O_AD3 NVARCHAR(35),
  53. @BP3_O_AD4 NVARCHAR(35),
  54. @BP3_S_SESS CHAR(6),
  55. @BP3_S_SEQU CHAR(6),
  56. @BP3_S_SWB CHAR(4),
  57. @BP3_S_CNA CHAR(2),
  58. @BP3_S_SWL CHAR(2),
  59. @BP3_S_SWR CHAR(3),
  60. @BP3_R_SESS CHAR(6),
  61. @BP3_R_SEQU CHAR(6),
  62. @BP3_R_SWB CHAR(4),
  63. @BP3_R_CNA CHAR(2),
  64. @BP3_R_SWL CHAR(2),
  65. @BP3_R_SWR CHAR(3),
  66. @BP3_I_AC VARCHAR(34),
  67. @BP3_I_SWB CHAR(4),
  68. @BP3_I_CNA CHAR(2),
  69. @BP3_I_SWL CHAR(2),
  70. @BP3_I_SWR CHAR(3),
  71. @BP3_I_AD1 NVARCHAR(35),
  72. @BP3_I_AD2 NVARCHAR(35),
  73. @BP3_I_AD3 NVARCHAR(35),
  74. @BP3_I_AD4 NVARCHAR(35),
  75. @BP3_A_AC VARCHAR(34),
  76. @BP3_A_SWB CHAR(4),
  77. @BP3_A_CNA CHAR(2),
  78. @BP3_A_SWL CHAR(2),
  79. @BP3_A_SWR CHAR(3),
  80. @BP3_A_AD1 NVARCHAR(35),
  81. @BP3_A_AD2 NVARCHAR(35),
  82. @BP3_A_AD3 NVARCHAR(35),
  83. @BP3_A_AD4 NVARCHAR(35),
  84. @BP3_B_AC VARCHAR(34),
  85. @BP3_B_SWB CHAR(4),
  86. @BP3_B_CNA CHAR(2),
  87. @BP3_B_SWL CHAR(2),
  88. @BP3_B_SWR CHAR(3),
  89. @BP3_B_AD1 NVARCHAR(35),
  90. @BP3_B_AD2 NVARCHAR(35),
  91. @BP3_B_AD3 NVARCHAR(35),
  92. @BP3_B_AD4 NVARCHAR(35),
  93. @BP3_CHRG CHAR(3),
  94. @BP3_PD1 NVARCHAR(35),
  95. @BP3_PD2 NVARCHAR(35),
  96. @BP3_PD3 NVARCHAR(35),
  97. @BP3_PD4 NVARCHAR(35),
  98. @BP3_SR1 NVARCHAR(35),
  99. @BP3_SR2 NVARCHAR(35),
  100. @BP3_SR3 NVARCHAR(35),
  101. @BP3_SR4 NVARCHAR(35),
  102. @BP3_SR5 NVARCHAR(35),
  103. @BP3_SR6 NVARCHAR(35),
  104. @BP3_KOD_OP CHAR(4),
  105. @BP3_KOD_COU CHAR(3),
  106. @BP3_NAM_PAK VARCHAR(12),
  107. @BP3_NAM_KV VARCHAR(12),
  108. @BP3_HD_COPY CHAR(1),
  109. @BP3_FL_WAIT CHAR(1),
  110. @BON_CODE CHAR(1),
  111. @BP3_SUBK CHAR(3),
  112. @BP3_TIM_S CHAR(4),
  113. @BP3_TIM_R CHAR(4),
  114. @BP3_TIM_H CHAR(4),
  115. @BP3_IC1 VARCHAR(35),
  116. @BP3_TT CHAR(3),
  117. @BP3_N_CCY CHAR(3),
  118. @BP3_N_AMT NUMERIC(15, 2),
  119. @BP3_RTE VARCHAR(12),
  120. @BP3_U_SWB CHAR(4),
  121. @BP3_U_CNA CHAR(2),
  122. @BP3_U_SWL CHAR(2),
  123. @BP3_U_SWR CHAR(3),
  124. @BP3_U_AC VARCHAR(34),
  125. @BP3_3_AC VARCHAR(34),
  126. @BP3_3_SWB CHAR(4),
  127. @BP3_3_CNA CHAR(2),
  128. @BP3_3_SWL CHAR(2),
  129. @BP3_3_SWR CHAR(3),
  130. @BP3_3_AD1 NVARCHAR(35),
  131. @BP3_3_AD2 NVARCHAR(35),
  132. @BP3_3_AD3 NVARCHAR(35),
  133. @BP3_3_AD4 NVARCHAR(35),
  134. @BP3_S1_CCY CHAR(3),
  135. @BP3_S1_AMT NUMERIC(15, 2),
  136. @BP3_S2_CCY CHAR(3),
  137. @BP3_S2_AMT NUMERIC(15, 2),
  138. @BP3_R1_CCY CHAR(3),
  139. @BP3_R1_AMT NUMERIC(15, 2),
  140. @BP3_RR1 NVARCHAR(35),
  141. @BP3_RR2 NVARCHAR(35),
  142. @BP3_RR3 NVARCHAR(35),
  143. @BP3_COD_ER CHAR(4),
  144. @BP3_USID_ML VARCHAR(12),
  145. @BP3_SERVCODE CHAR(3),
  146. @BP3_COMMENT NVARCHAR(100),
  147. @BP3_DT_I DATETIME,
  148. @BP3_META CHAR(2),
  149. @BP3_OP_CODE CHAR(3),
  150. @BP3_FL_FEA CHAR(1),
  151. @BP3_FL_SNC CHAR(1),
  152. @BP3_FL_CSH CHAR(1),
  153. @BP3_B1_CCY CHAR(3),
  154. @BP3_B1_AMT NUMERIC(15, 2),
  155. @BP3_FL_SPC CHAR(1),
  156. @BP3_S_PHONE VARCHAR(20),
  157. @BP3_DESC NVARCHAR(500),
  158. @BP3_P_CUS_ID INT,
  159. @BP3_PG INT)
  160. AS
  161. BEGIN
  162. -- noinspection SqlResolve
  163. INSERT INTO dbo.P3ES (BP3_REF,
  164. BP3_TRSL,
  165. BP3_PR_OUT,
  166. BP3_BRNM,
  167. BP3_FLMN,
  168. EP3_PYT,
  169. BP3_MT,
  170. BP3_XCP,
  171. BP3_APP,
  172. BP3_DLP,
  173. BP3_REFS,
  174. BP3_REF_MUR,
  175. BP3_PR_S,
  176. BP3_PR_SYS,
  177. BP3_PR_SEP,
  178. BP3_PR_VIZ,
  179. BP3_FL_EQ,
  180. BP3_USID_A,
  181. BP3_USID_R,
  182. BP3_USID_V,
  183. BP3_USID_N,
  184. BP3_USID_U1,
  185. BP3_USID_U2,
  186. EP3_AMTE,
  187. BP3_TRM,
  188. BP3_DAT_KL,
  189. BP3_P_DAT_OD,
  190. bp3_p_ccy,
  191. BP3_P_ACC,
  192. bp3_p_amt,
  193. BP3_P_NMT,
  194. BP3_R_ACC,
  195. BP3_O_CA1,
  196. BP3_O_CA2,
  197. BP3_O_CA3,
  198. BP3_O_CA4,
  199. BP3_O_SWB,
  200. BP3_O_CNA,
  201. BP3_O_SWL,
  202. BP3_O_SWR,
  203. BP3_O_AD1,
  204. BP3_O_AD2,
  205. BP3_O_AD3,
  206. bp3_o_ad4,
  207. BP3_S_SESS,
  208. BP3_S_SEQU,
  209. BP3_S_SWB,
  210. BP3_S_CNA,
  211. BP3_S_SWL,
  212. BP3_S_SWR,
  213. BP3_R_SESS,
  214. BP3_R_SEQU,
  215. BP3_R_SWB,
  216. BP3_R_CNA,
  217. BP3_R_SWL,
  218. BP3_R_SWR,
  219. BP3_I_AC,
  220. BP3_I_SWB,
  221. BP3_I_CNA,
  222. BP3_I_SWL,
  223. BP3_I_SWR,
  224. BP3_I_AD1,
  225. BP3_I_AD2,
  226. BP3_I_AD3,
  227. BP3_I_AD4,
  228. BP3_A_AC,
  229. BP3_A_SWB,
  230. BP3_A_CNA,
  231. BP3_A_SWL,
  232. BP3_A_SWR,
  233. BP3_A_AD1,
  234. BP3_A_AD2,
  235. BP3_A_AD3,
  236. BP3_A_AD4,
  237. BP3_B_AC,
  238. BP3_B_SWB,
  239. BP3_B_CNA,
  240. BP3_B_SWL,
  241. BP3_B_SWR,
  242. BP3_B_AD1,
  243. BP3_B_AD2,
  244. BP3_B_AD3,
  245. BP3_B_AD4,
  246. bp3_chrg,
  247. BP3_PD1,
  248. BP3_PD2,
  249. BP3_PD3,
  250. BP3_PD4,
  251. BP3_SR1,
  252. BP3_SR2,
  253. BP3_SR3,
  254. BP3_SR4,
  255. BP3_SR5,
  256. BP3_SR6,
  257. BP3_KOD_OP,
  258. BP3_KOD_COU,
  259. BP3_NAM_PAK,
  260. BP3_NAM_KV,
  261. BP3_HD_COPY,
  262. BP3_FL_WAIT,
  263. BON_CODE,
  264. BP3_SUBK,
  265. BP3_TIM_S,
  266. BP3_TIM_R,
  267. BP3_TIM_H,
  268. BP3_IC1,
  269. BP3_TT,
  270. BP3_N_CCY,
  271. BP3_N_AMT,
  272. BP3_RTE,
  273. BP3_U_SWB,
  274. BP3_U_CNA,
  275. BP3_U_SWL,
  276. BP3_U_SWR,
  277. BP3_U_AC,
  278. BP3_3_AC,
  279. BP3_3_SWB,
  280. BP3_3_CNA,
  281. BP3_3_SWL,
  282. BP3_3_SWR,
  283. BP3_3_AD1,
  284. BP3_3_AD2,
  285. BP3_3_AD3,
  286. BP3_3_AD4,
  287. BP3_S1_CCY,
  288. BP3_S1_AMT,
  289. BP3_S2_CCY,
  290. BP3_S2_AMT,
  291. BP3_R1_CCY,
  292. BP3_R1_AMT,
  293. BP3_RR1,
  294. BP3_RR2,
  295. BP3_RR3,
  296. BP3_COD_ER,
  297. BP3_USID_ML,
  298. BP3_SERVCODE,
  299. BP3_COMMENT,
  300. BP3_DT_I,
  301. BP3_PG,
  302. BP3_META,
  303. BP3_OP_CODE,
  304. BP3_FL_FEA,
  305. BP3_FL_SNC,
  306. BP3_FL_CSH,
  307. BP3_B1_CCY,
  308. BP3_B1_AMT,
  309. BP3_FL_SPC,
  310. BP3_S_PHONE,
  311. BP3_DESC,
  312. BP3_P_CUS_ID) VALUES (@BP3_REF,
  313. isNull(@BP3_TRSL, ''),
  314. isNull(@BP3_PR_OUT, ''),
  315. isNull(@BP3_BRNM, ''),
  316. isNull(@BP3_FLMN, ''),
  317. isNull(@EP3_PYT, ''),
  318. isNull(@BP3_MT, ''),
  319. isNull(@BP3_XCP, ''),
  320. isNull(@BP3_APP, ''),
  321. isNull(@BP3_DLP, ''),
  322. isNull(@BP3_REFS, ''),
  323. isNull(@BP3_REF_MUR, ''),
  324. isNull(@BP3_PR_S, ''),
  325. isNull(@BP3_PR_SYS, ''),
  326. isNull(@BP3_PR_SEP, ''),
  327. isNull(@BP3_PR_VIZ, ''),
  328. isNull(@BP3_FL_EQ, ''),
  329. isNull(@BP3_USID_A, ''),
  330. isNull(@BP3_USID_R, ''),
  331. isNull(@BP3_USID_V, ''),
  332. isNull(@BP3_USID_N, ''),
  333. isNull(@BP3_USID_U1, ''),
  334. isNull(@BP3_USID_U2, ''),
  335. isNull(@EP3_AMTE, ''),
  336. isNull(@BP3_TRM, ''),
  337. isNull(convert(DATETIME, @BP3_DAT_KL), getDATE()),
  338. isNull(convert(DATETIME, @BP3_P_DAT_OD), getDate()),
  339. isNull(@bp3_p_ccy, ''),
  340. isNull(@BP3_P_ACC, ''),
  341. CONVERT(FLOAT, @bp3_p_amt),
  342. CONVERT(FLOAT, @BP3_P_NMT),
  343. isNull(@BP3_R_ACC, ''),
  344. isNull(@BP3_O_CA1, ''),
  345. isNull(@BP3_O_CA2, ''),
  346. isNull(@BP3_O_CA3, ''),
  347. isNull(@BP3_O_CA4, ''),
  348. isNull(@BP3_O_SWB, ''),
  349. isNull(@BP3_O_CNA, ''),
  350. isNull(@BP3_O_SWL, ''),
  351. isNull(@BP3_O_SWR, ''),
  352. isNull(@BP3_O_AD1, ''),
  353. isNull(@BP3_O_AD2, ''),
  354. isNull(@BP3_O_AD3, ''),
  355. isNull(@BP3_O_AD4, ''),
  356. isNull(@BP3_S_SESS, ''),
  357. isNull(@BP3_S_SEQU, ''),
  358. isNull(@BP3_S_SWB, ''),
  359. isNull(@BP3_S_CNA, ''),
  360. isNull(@BP3_S_SWL, ''),
  361. isNull(@BP3_S_SWR, ''),
  362. isNull(@BP3_R_SESS, ''),
  363. isNull(@BP3_R_SEQU, ''),
  364. isNull(@BP3_R_SWB, ''),
  365. isNull(@BP3_R_CNA, ''),
  366. isNull(@BP3_R_SWL, ''),
  367. isNull(@BP3_R_SWR, ''),
  368. isNull(@BP3_I_AC, ''),
  369. isNull(@BP3_I_SWB, ''),
  370. isNull(@BP3_I_CNA, ''),
  371. isNull(@BP3_I_SWL, ''),
  372. isNull(@BP3_I_SWR, ''),
  373. isNull(@BP3_I_AD1, ''),
  374. isNull(@BP3_I_AD2, ''),
  375. isNull(@BP3_I_AD3, ''),
  376. isNull(@BP3_I_AD4, ''),
  377. isNull(@BP3_A_AC, ''),
  378. isNull(@BP3_A_SWB, ''),
  379. isNull(@BP3_A_CNA, ''),
  380. isNull(@BP3_A_SWL, ''),
  381. isNull(@BP3_A_SWR, ''),
  382. isNull(@BP3_A_AD1, ''),
  383. isNull(@BP3_A_AD2, ''),
  384. isNull(@BP3_A_AD3, ''),
  385. isNull(@BP3_A_AD4, ''),
  386. isNull(@BP3_B_AC, ''),
  387. isNull(@BP3_B_SWB, ''),
  388. isNull(@BP3_B_CNA, ''),
  389. isNull(@BP3_B_SWL, ''),
  390. isNull(@BP3_B_SWR, ''),
  391. isNull(@BP3_B_AD1, ''),
  392. isNull(@BP3_B_AD2, ''),
  393. isNull(@BP3_B_AD3, ''),
  394. isNull(@BP3_B_AD4, ''),
  395. isNull(@BP3_CHRG, ''),
  396. isNull(@BP3_PD1, ''),
  397. isNull(@BP3_PD2, ''),
  398. isNull(@BP3_PD3, ''),
  399. isNull(@BP3_PD4, ''),
  400. isNull(@BP3_SR1, ''),
  401. isNull(@BP3_SR2, ''),
  402. isNull(@BP3_SR3, ''),
  403. isNull(@BP3_SR4, ''),
  404. isNull(@BP3_SR5, ''),
  405. isNull(@BP3_SR6, ''),
  406. isNull(@BP3_KOD_OP, ''),
  407. isNull(@BP3_KOD_COU, ''),
  408. isNull(@BP3_NAM_PAK, ''),
  409. isNull(@BP3_NAM_KV, ''),
  410. isNull(@BP3_HD_COPY, ''),
  411. isNull(@BP3_FL_WAIT, ''),
  412. isNull(@BON_CODE, ''),
  413. isNull(@BP3_SUBK, ''),
  414. isNull(@BP3_TIM_S, ''),
  415. isNull(@BP3_TIM_R, ''),
  416. isNull(@BP3_TIM_H, ''),
  417. isNull(@BP3_IC1, ''),
  418. isNull(@BP3_TT, ''),
  419. isNull(@BP3_N_CCY, ''),
  420. CONVERT(FLOAT, @BP3_N_AMT),
  421. isNull(@BP3_RTE, ''),
  422. isNull(@BP3_U_SWB, ''),
  423. isNull(@BP3_U_CNA, ''),
  424. isNull(@BP3_U_SWL, ''),
  425. isNull(@BP3_U_SWR, ''),
  426. isNull(@BP3_U_AC, ''),
  427. isNull(@BP3_3_AC, ''),
  428. isNull(@BP3_3_SWB, ''),
  429. isNull(@BP3_3_CNA, ''),
  430. isNull(@BP3_3_SWL, ''),
  431. isNull(@BP3_3_SWR, ''),
  432. isNull(@BP3_3_AD1, ''),
  433. isNull(@BP3_3_AD2, ''),
  434. isNull(@BP3_3_AD3, ''),
  435. isNull(@BP3_3_AD4, ''),
  436. isNull(@BP3_S1_CCY, ''),
  437. CONVERT(FLOAT, @BP3_S1_AMT),
  438. isNull(@BP3_S2_CCY, ''),
  439. CONVERT(FLOAT, @BP3_S2_AMT),
  440. isNull(@BP3_R1_CCY, ''),
  441. CONVERT(FLOAT, @BP3_R1_AMT),
  442. isNull(@BP3_RR1, ''),
  443. isNull(@BP3_RR2, ''),
  444. isNull(@BP3_RR3, ''),
  445. @BP3_COD_ER,
  446. @BP3_USID_ML,
  447. @BP3_SERVCODE,
  448. @BP3_COMMENT,
  449. isNull(convert(DATETIME, @BP3_DT_I), getDate()),
  450. @BP3_PG,
  451. @BP3_META,
  452. @BP3_OP_CODE,
  453. @BP3_FL_FEA,
  454. @BP3_FL_SNC,
  455. @BP3_FL_CSH,
  456. @BP3_B1_CCY,
  457. @BP3_B1_AMT,
  458. @BP3_FL_SPC,
  459. isNull(@BP3_S_PHONE, ''),
  460. isNull(@BP3_DESC, ''),
  461. @BP3_P_CUS_ID)
  462. END
  463. GO
  464. sp_procXMode 'insertIntoP3ES', 'anyMode'
  465. GO
  466. GRANT EXECUTE ON dbo.insertIntoP3ES TO jod_role
  467. GO
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!