API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Nov 15th, 2019
115
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.30 KB | None | 0 0
raw download clone embed print report
  1. /* CompareMonthlyPopup */
  2. public static final String COMPARE_MONTHLY_SQL = "select 'New item' as Matching, "
  3. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  4. + "m.receivableposttaxamount as CH_Receivable_Amount_SDR , 0 as System_Receivable_Amount_SDR , "
  5. + "m.receivableadjustments as CH_Receivable_Adj_SDR , 0 as System_Receivable_Adj_SDR , "
  6. + "m.payableposttaxamount as CH_Payable_Amount_SDR , 0 as System_Payable_Amount_SDR , "
  7. + "m.payableadjustments as CH_Payble_Adj_SDR , 0 as System_Payble_Adj_SDR , "
  8. + "abs(m.netsettlementamountcc) as CH_Net_Amount_SDR , 0 as System_Net_Amount_SDR , "
  9. + "m.settlementdirection as CH_Settlement_Direction , ' ' as System_Settlement_Direction , "
  10. + "m.settlementcurrency as CH_Settlement_Currency , ' ' as System_Settlement_Currency , "
  11. + "m.settlementcurrencyrate as CH_Settlement_Curr_Rate , 0 as System_Settlement_Curr_Rate , "
  12. + "abs(m.netsettlementamountsc) as CH_Net_Amount_SC , 0 as System_Net_Amount_SC "
  13. + "from roammonthlyitem m "
  14. + "where m.monthlyhead_id = ? "
  15. + "and m.settlementtype = 'Gross' "
  16. + "and ( transactingentity, partnertransactingentity, invoiceperiod, servicetype, settlementdirection ) not in "
  17. + "( select transactingentity, partnertransactingentity, invoiceperiod, servicetype, settlementdirection from roammonthlyfinal where nvl(settlementtype, 'Gross') ='Gross' ) "
  18. + "union "
  19. + "select 'New item' as Matching , "
  20. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  21. + "m.receivableposttaxamount as CH_Receivable_Amount_SDR , 0 as System_Receivable_Amount_SDR , "
  22. + "m.receivableadjustments as CH_Receivable_Adj_SDR , 0 as System_Receivable_Adj_SDR , "
  23. + "m.payableposttaxamount as CH_Payable_Amount_SDR , 0 as System_Payable_Amount_SDR , "
  24. + "m.payableadjustments as CH_Payble_Adj_SDR , 0 as System_Payble_Adj_SDR , "
  25. + "abs(m.netsettlementamountcc) as CH_Net_Amount_SDR , 0 as System_Net_Amount_SDR , "
  26. + "m.settlementdirection as CH_Settlement_Direction , ' ' as System_Settlement_Direction , "
  27. + "m.settlementcurrency as CH_Settlement_Currency , ' ' as System_Settlement_Currency , "
  28. + "m.settlementcurrencyrate as CH_Settlement_Curr_Rate , 0 as System_Settlement_Curr_Rate , "
  29. + "abs(m.netsettlementamountsc) as CH_Net_Amount_SC , 0 as System_Net_Amount_SC "
  30. + "from roammonthlyitem m "
  31. + "where m.monthlyhead_id = ? "
  32. + "and m.settlementtype = 'Bilateral' "
  33. + "and ( transactingentity, partnertransactingentity, invoiceperiod, servicetype ) not in "
  34. + "( select transactingentity, partnertransactingentity, invoiceperiod, servicetype from roammonthlyfinal where nvl(settlementtype, 'Bilateral') ='Bilateral' ) "
  35. + "union "
  36. + "select 'Closed in the system, open in clearing house' as Matching , "
  37. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  38. + "m.receivableposttaxamount as CH_Receivable_Amount_SDR , f.receivableposttaxamount as System_Receivable_Amount_SDR , "
  39. + "m.receivableadjustments as CH_Receivable_Adj_SDR , f.receivableadjustments as System_Receivable_Adj_SDR , "
  40. + "m.payableposttaxamount as CH_Payable_Amount_SDR , f.payableposttaxamount as System_Payable_Amount_SDR , "
  41. + "m.payableadjustments as CH_Payble_Adj_SDR , f.payableadjustments as System_Payble_Adj_SDR , "
  42. + "abs(m.netsettlementamountcc) as CH_Net_Amount_SDR , f.netsettlementamountcc as System_Net_Amount_SDR , "
  43. + "m.settlementdirection as CH_Settlement_Direction , f.settlementdirection as System_Settlement_Direction , "
  44. + "m.settlementcurrency as CH_Settlement_Currency , f.settlementcurrency as System_Settlement_Currency , "
  45. + "m.settlementcurrencyrate as CH_Settlement_Curr_Rate , f.settlementcurrencyrate as System_Settlement_Curr_Rate , "
  46. + "abs(m.netsettlementamountsc) as CH_Net_Amount_SC , f.netsettlementamountsc as System_Net_Amount_SC "
  47. + "from roammonthlyitem m, roammonthlyfinal f "
  48. + "where m.transactingentity = f.transactingentity "
  49. + "and m.partnertransactingentity = f.partnertransactingentity "
  50. + "and m.invoiceperiod = f.invoiceperiod "
  51. + "and m.servicetype = f.servicetype "
  52. + "and m.settlementdirection = f.settlementdirection "
  53. + "and m.settlementtype =nvl(f.settlementtype, m.settlementtype) "
  54. + "and m.settlementtype = 'Gross' "
  55. + "and m.monthlyhead_id = ? "
  56. + "and f.financialstatus = 'Closed' "
  57. + "union "
  58. + "select 'Closed in the system, open in clearing house' as Matching , "
  59. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  60. + "m.receivableposttaxamount as CH_Receivable_Amount_SDR , f.receivableposttaxamount as System_Receivable_Amount_SDR , "
  61. + "m.receivableadjustments as CH_Receivable_Adj_SDR , f.receivableadjustments as System_Receivable_Adj_SDR , "
  62. + "m.payableposttaxamount as CH_Payable_Amount_SDR , f.payableposttaxamount as System_Payable_Amount_SDR , "
  63. + "m.payableadjustments as CH_Payble_Adj_SDR , f.payableadjustments as System_Payble_Adj_SDR , "
  64. + "abs(m.netsettlementamountcc) as CH_Net_Amount_SDR , f.netsettlementamountcc as System_Net_Amount_SDR , "
  65. + "m.settlementdirection as CH_Settlement_Direction , f.settlementdirection as System_Settlement_Direction , "
  66. + "m.settlementcurrency as CH_Settlement_Currency , f.settlementcurrency as System_Settlement_Currency , "
  67. + "m.settlementcurrencyrate as CH_Settlement_Curr_Rate , f.settlementcurrencyrate as System_Settlement_Curr_Rate , "
  68. + "abs(m.netsettlementamountsc) as CH_Net_Amount_SC , f.netsettlementamountsc as System_Net_Amount_SC "
  69. + "from roammonthlyitem m, roammonthlyfinal f "
  70. + "where m.transactingentity = f.transactingentity "
  71. + "and m.partnertransactingentity = f.partnertransactingentity "
  72. + "and m.invoiceperiod = f.invoiceperiod "
  73. + "and m.servicetype = f.servicetype "
  74. + "and m.settlementdirection = f.settlementdirection "
  75. + "and m.settlementtype =nvl(f.settlementtype, m.settlementtype) "
  76. + "and m.settlementtype = 'Bilateral' "
  77. + "and m.monthlyhead_id = ? "
  78. + "and f.financialstatus = 'Closed' "
  79. + "union "
  80. + "select 'Open in System, Not in clearing house report' as Matching , "
  81. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  82. + "0 as CH_Receivable_Amount_SDR , m.receivableposttaxamount as System_Receivable_Amount_SDR , "
  83. + "0 as CH_Receivable_Adj_SDR , m.receivableadjustments as System_Receivable_Adj_SDR , "
  84. + "0 as CH_Payable_Amount_SDR , m.payableposttaxamount as System_Payable_Amount_SDR , "
  85. + "0 as CH_Payble_Adj_SDR , m.payableadjustments as System_Payble_Adj_SDR , "
  86. + "0 as CH_Net_Amount_SDR , abs(m.netsettlementamountcc) as System_Net_Amount_SDR , "
  87. + "' ' as CH_Settlement_Direction , m.settlementdirection as System_Settlement_Direction , "
  88. + "' ' as CH_Settlement_Currency , m.settlementcurrency as System_Settlement_Currency , "
  89. + "0 as CH_Settlement_Curr_Rate , m.settlementcurrencyrate as System_Settlement_Curr_Rate , "
  90. + "0 as CH_Net_Amount_SC , abs(m.netsettlementamountsc) as System_Net_Amount_SC "
  91. + "from roammonthlyfinal m "
  92. + "where ( transactingentity, partnertransactingentity, invoiceperiod, servicetype, settlementdirection ) not in "
  93. + "( select transactingentity, partnertransactingentity, invoiceperiod, servicetype, settlementdirection "
  94. + "from roammonthlyitem "
  95. + "where monthlyhead_id = ? "
  96. + "and settlementtype = 'Gross') "
  97. + "and m.financialstatus != 'Closed' "
  98. + "and nvl(m.settlementtype,'Gross') = 'Gross' "
  99. + "union "
  100. + "select 'Open in System, Not in clearing house report' as Matching , "
  101. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  102. + "0 as CH_Receivable_Amount_SDR , m.receivableposttaxamount as System_Receivable_Amount_SDR , "
  103. + "0 as CH_Receivable_Adj_SDR , m.receivableadjustments as System_Receivable_Adj_SDR , "
  104. + "0 as CH_Payable_Amount_SDR , m.payableposttaxamount as System_Payable_Amount_SDR , "
  105. + "0 as CH_Payble_Adj_SDR , m.payableadjustments as System_Payble_Adj_SDR , "
  106. + "0 as CH_Net_Amount_SDR , abs(m.netsettlementamountcc) as System_Net_Amount_SDR , "
  107. + "' ' as CH_Settlement_Direction , m.settlementdirection as System_Settlement_Direction , "
  108. + "' ' as CH_Settlement_Currency , m.settlementcurrency as System_Settlement_Currency , "
  109. + "0 as CH_Settlement_Curr_Rate , m.settlementcurrencyrate as System_Settlement_Curr_Rate , "
  110. + "0 as CH_Net_Amount_SC , abs(m.netsettlementamountsc) as System_Net_Amount_SC "
  111. + "from roammonthlyfinal m "
  112. + "where ( transactingentity, partnertransactingentity, invoiceperiod, servicetype ) not in "
  113. + "( select transactingentity, partnertransactingentity, invoiceperiod, servicetype "
  114. + "from roammonthlyitem "
  115. + "where monthlyhead_id = ? "
  116. + "and settlementtype = 'Bilateral') "
  117. + "and m.financialstatus != 'Closed' "
  118. + "and nvl(m.settlementtype,'Bilateral') = 'Bilateral' "
  119. + "union "
  120. + "select 'Existing item' as Matching , "
  121. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  122. + "m.receivableposttaxamount as CH_Receivable_Amount_SDR , f.receivableposttaxamount as System_Receivable_Amount_SDR , "
  123. + "m.receivableadjustments as CH_Receivable_Adj_SDR , f.receivableadjustments as System_Receivable_Adj_SDR , "
  124. + "m.payableposttaxamount as CH_Payable_Amount_SDR , f.payableposttaxamount as System_Payable_Amount_SDR , "
  125. + "m.payableadjustments as CH_Payble_Adj_SDR , f.payableadjustments as System_Payble_Adj_SDR , "
  126. + "abs(m.netsettlementamountcc) as CH_Net_Amount_SDR , f.netsettlementamountcc as System_Net_Amount_SDR , "
  127. + "m.settlementdirection as CH_Settlement_Direction , f.settlementdirection as System_Settlement_Direction , "
  128. + "m.settlementcurrency as CH_Settlement_Currency , f.settlementcurrency as System_Settlement_Currency , "
  129. + "m.settlementcurrencyrate as CH_Settlement_Curr_Rate , f.settlementcurrencyrate as System_Settlement_Curr_Rate , "
  130. + "abs(m.netsettlementamountsc) as CH_Net_Amount_SC , f.netsettlementamountsc as System_Net_Amount_SC "
  131. + "from roammonthlyitem m, roammonthlyfinal f "
  132. + "where m.transactingentity = f.transactingentity "
  133. + "and m.partnertransactingentity = f.partnertransactingentity "
  134. + "and m.invoiceperiod = f.invoiceperiod "
  135. + "and m.servicetype = f.servicetype "
  136. + "and m.settlementdirection = f.settlementdirection "
  137. + "and m.settlementtype =nvl(f.settlementtype, m.settlementtype) "
  138. + "and m.settlementtype = 'Gross' "
  139. + "and m.monthlyhead_id = ? "
  140. + "and f.financialstatus != 'Closed' "
  141. + "union "
  142. + "select 'Existing item' as Matching , "
  143. + "m.transactingentity as Franchise , m.partnertransactingentity as PLMN , m.invoiceperiod as Invoice_Period , m.servicetype as Service , m.settlementtype as Settlement_type , "
  144. + "m.receivableposttaxamount as CH_Receivable_Amount_SDR , f.receivableposttaxamount as System_Receivable_Amount_SDR , "
  145. + "m.receivableadjustments as CH_Receivable_Adj_SDR , f.receivableadjustments as System_Receivable_Adj_SDR , "
  146. + "m.payableposttaxamount as CH_Payable_Amount_SDR , f.payableposttaxamount as System_Payable_Amount_SDR , "
  147. + "m.payableadjustments as CH_Payble_Adj_SDR , f.payableadjustments as System_Payble_Adj_SDR , "
  148. + "abs(m.netsettlementamountcc) as CH_Net_Amount_SDR , f.netsettlementamountcc as System_Net_Amount_SDR , "
  149. + "m.settlementdirection as CH_Settlement_Direction , f.settlementdirection as System_Settlement_Direction , "
  150. + "m.settlementcurrency as CH_Settlement_Currency , f.settlementcurrency as System_Settlement_Currency , "
  151. + "m.settlementcurrencyrate as CH_Settlement_Curr_Rate , f.settlementcurrencyrate as System_Settlement_Curr_Rate , "
  152. + "abs(m.netsettlementamountsc) as CH_Net_Amount_SC , f.netsettlementamountsc as System_Net_Amount_SC "
  153. + "from roammonthlyitem m, roammonthlyfinal f " + "where m.transactingentity = f.transactingentity "
  154. + "and m.partnertransactingentity = f.partnertransactingentity " + "and m.invoiceperiod = f.invoiceperiod "
  155. + "and m.servicetype = f.servicetype " + "and m.settlementdirection = f.settlementdirection "
  156. + "and m.settlementtype =nvl(f.settlementtype, m.settlementtype) " + "and m.settlementtype = 'Bilateral' "
  157. + "and m.monthlyhead_id = ? " + "and f.financialstatus != 'Closed'";
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!