SHARE
TWEET

rptpaymentfix

a guest Oct 22nd, 2019 87 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE etracs254_dangcaganbak.zzzzrptledger_payment_bak AS
  2. SELECT
  3. *
  4. FROM etracs254_dangcaganbak.rptledger_payment
  5. WHERE objid NOT IN (SELECT objid FROM etracs254_dangcagan.`rptledger_payment`);
  6.  
  7. INSERT INTO etracs254_dangcagan.rptpayment(
  8.   objid,
  9.   TYPE,
  10.   refid,
  11.   reftype,
  12.   receiptid,
  13.   receiptno,
  14.   receiptdate,
  15.   paidby_name,
  16.   paidby_address,
  17.   postedby,
  18.   postedbytitle,
  19.   dtposted,
  20.   fromyear,
  21.   fromqtr,
  22.   toyear,
  23.   toqtr,
  24.   amount,
  25.   collectingagency,
  26.   voided
  27. )
  28. SELECT
  29.   objid,
  30.   TYPE,
  31.   rptledgerid AS refid,
  32.   'rptledger' AS reftype,
  33.   receiptid,
  34.   receiptno,
  35.   receiptdate,
  36.   paidby_name,
  37.   paidby_address,
  38.   postedby,
  39.   postedbytitle,
  40.   dtposted,
  41.   fromyear,
  42.   fromqtr,
  43.   toyear,
  44.   toqtr,
  45.   amount,
  46.   collectingagency,
  47.   voided
  48. FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak;
  49.  
  50.  
  51.  
  52.  
  53. INSERT INTO etracs254_dangcagan.rptpayment_item(
  54.   objid,
  55.   parentid,
  56.   rptledgerfaasid,
  57.   YEAR,
  58.   qtr,
  59.   revtype,
  60.   revperiod,
  61.   amount,
  62.   interest,
  63.   discount,
  64.   partialled,
  65.   priority
  66. )
  67. SELECT
  68.   CONCAT(objid, '-basic') AS objid,
  69.   parentid,
  70.   rptledgerfaasid,
  71.   YEAR,
  72.   qtr,
  73.   'basic' AS revtype,
  74.   revperiod,
  75.   basic AS amount,
  76.   basicint AS interest,
  77.   basicdisc AS discount,
  78.   partialled,
  79.   10000 AS priority
  80. FROM etracs254_dangcaganbak.rptledger_payment_item
  81. WHERE parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
  82. AND rptledgerfaasid IS NOT NULL
  83. AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);
  84.  
  85.  
  86.  
  87.  
  88.  
  89. INSERT INTO etracs254_dangcagan.rptpayment_item(
  90.   objid,
  91.   parentid,
  92.   rptledgerfaasid,
  93.   YEAR,
  94.   qtr,
  95.   revtype,
  96.   revperiod,
  97.   amount,
  98.   interest,
  99.   discount,
  100.   partialled,
  101.   priority
  102. )
  103. SELECT
  104.   CONCAT(objid, '-sef') AS objid,
  105.   parentid,
  106.   rptledgerfaasid,
  107.   YEAR,
  108.   qtr,
  109.   'sef' AS revtype,
  110.   revperiod,
  111.   sef AS amount,
  112.   sefint AS interest,
  113.   sefdisc AS discount,
  114.   partialled,
  115.   10000 AS priority
  116. FROM etracs254_dangcaganbak.rptledger_payment_item
  117. WHERE parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
  118. AND rptledgerfaasid IS NOT NULL
  119. AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);
  120.  
  121.  
  122. INSERT INTO etracs254_dangcagan.rptpayment_item(
  123.   objid,
  124.   parentid,
  125.   rptledgerfaasid,
  126.   YEAR,
  127.   qtr,
  128.   revtype,
  129.   revperiod,
  130.   amount,
  131.   interest,
  132.   discount,
  133.   partialled,
  134.   priority
  135. )
  136. SELECT
  137.   CONCAT(objid, '-sh') AS objid,
  138.   parentid,
  139.   rptledgerfaasid,
  140.   YEAR,
  141.   qtr,
  142.   'sh' AS revtype,
  143.   revperiod,
  144.   sh AS amount,
  145.   shint AS interest,
  146.   shdisc AS discount,
  147.   partialled,
  148.   100 AS priority
  149. FROM etracs254_dangcaganbak.rptledger_payment_item
  150. WHERE sh > 0
  151. AND parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
  152. AND rptledgerfaasid IS NOT NULL
  153. AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);
  154.  
  155.  
  156.  
  157.  
  158. INSERT INTO etracs254_dangcagan.rptpayment_item(
  159.   objid,
  160.   parentid,
  161.   rptledgerfaasid,
  162.   YEAR,
  163.   qtr,
  164.   revtype,
  165.   revperiod,
  166.   amount,
  167.   interest,
  168.   discount,
  169.   partialled,
  170.   priority
  171. )
  172. SELECT
  173.   CONCAT(objid, '-firecode') AS objid,
  174.   parentid,
  175.   rptledgerfaasid,
  176.   YEAR,
  177.   qtr,
  178.   'firecode' AS revtype,
  179.   revperiod,
  180.   firecode AS amount,
  181.   0 AS interest,
  182.   0 AS discount,
  183.   partialled,
  184.   50 AS priority
  185. FROM etracs254_dangcaganbak.rptledger_payment_item
  186. WHERE firecode > 0
  187. AND parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
  188. AND rptledgerfaasid IS NOT NULL
  189. AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);
  190.  
  191.  
  192.  
  193. INSERT INTO etracs254_dangcagan.rptpayment_item(
  194.   objid,
  195.   parentid,
  196.   rptledgerfaasid,
  197.   YEAR,
  198.   qtr,
  199.   revtype,
  200.   revperiod,
  201.   amount,
  202.   interest,
  203.   discount,
  204.   partialled,
  205.   priority
  206. )
  207. SELECT
  208.   CONCAT(objid, '-basicidle') AS objid,
  209.   parentid,
  210.   rptledgerfaasid,
  211.   YEAR,
  212.   qtr,
  213.   'basicidle' AS revtype,
  214.   revperiod,
  215.   basicidle AS amount,
  216.   basicidleint AS interest,
  217.   basicidledisc AS discount,
  218.   partialled,
  219.   200 AS priority
  220. FROM etracs254_dangcaganbak.rptledger_payment_item
  221. WHERE basicidle > 0
  222. AND parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
  223. AND rptledgerfaasid IS NOT NULL
  224. AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);
  225.  
  226.  
  227.  
  228.  
  229. UPDATE etracs254_dangcagan.rptpayment SET TYPE = 'online' WHERE TYPE = 'rptonline'
  230. ;
  231. UPDATE etracs254_dangcagan.rptpayment SET TYPE = 'manual' WHERE TYPE = 'rptmanual'
  232. ;
  233. UPDATE etracs254_dangcagan.rptpayment SET TYPE = 'compromise' WHERE TYPE = 'rptcompromise'
  234. ;
  235.  
  236. -- output ani i.butang sa notepad
  237. SELECT * FROM etracs254_dangcagan.rptpayment WHERE objid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak);
  238. SELECT * FROM etracs254_dangcagan.rptpayment_item WHERE parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak);
RAW Paste Data
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
 
Top