API tools faq
paste
Advertisement
Guest User

123123

a guest
Jan 18th, 2018
100
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.13 KB | None | 0 0
raw download clone embed print report
  1. SELECT A. *,
  2. '' PURCHASE_ORDER_NO_OST,
  3. '' PURCHASE_ORDER_NO_OST_SHM,
  4. TRIM (INVENTORY.MAIN_DESC) MAIN_DESC,
  5. INVENTORY.SHORT_DESC,
  6. INVENTORY.SERIES_1,
  7. INVENTORY.SERIES_2,
  8. INVENTORY.SERIES_3,
  9. INVENTORY.SERIES_4,
  10. TRIM (INVENTORY.REPORT_GROUPING_CODE) REPORT_GROUPING_CODE,
  11. TRIM (INVENTORY.INVENTORY_CLASS_CODE) INVENTORY_CLASS_CODE,
  12. TRIM (INVENTORY.INVENTORY_CATEGORY_CODE) INVENTORY_CATEGORY_CODE,
  13. TRIM (INVENTORY.INVENTORY_MODEL_CODE) INVENTORY_MODEL_CODE,
  14. INV_CAT.INVENTORY_CATEGORY_DESC,
  15. INV_CLASS.INVENTORY_CLASS_DESC,
  16. INVENTORY.UOM_CODE,
  17. UOM.UOM_DESC,
  18. TRIM (CUSTOMERCAT.CUSTOMER_CATEGORY_CODE) CUSTOMER_CATEGORY_CODE,
  19. TRIM (CUSTOMERCAT.CUSTOMER_CATEGORY_DESC) CUSTOMER_CATEGORY_DESC,
  20. TRIM (SPECVAL.SPEC_VALUE_DESC) SPEC_VALUE_DESC,
  21. TRIM (MODEL.INVENTORY_MODEL_DESC) INVENTORY_MODEL_DESC,
  22. TRIM (PARTY.PARTY_NAME) PARTY_NAME,
  23. TRIM (INDUSTRY.INDUSTRY_DESC) INDUSTRY_DESC,
  24. TRIM (CUSTOMER.INDUSTRY_CODE) INDUSTRY_CODE,
  25. TRIM (PARTY.COUNTRY_OF_INCORPORATION) COUNTRY_OF_INCORPORATION,
  26. TRIM (PARTY.PARTY_GROUP_CODE) PARTY_GROUP_CODE,
  27. TRIM (COUNTRY_OF_INCORPORATION.COUNTRY_NAME) COUNTRYCORPORATIONNAME,
  28. TRIM (COUNTRY_OF_INVENTORY.COUNTRY_NAME) ORIGIN_INVENTORY_COUNTRY_NAME
  29. FROM (SELECT TRIM (INVHDR.SOURCE_VOUCHER_NO) GRN_NO,
  30. TRIM (INVHDR.INVOICE_NO) INVOICE_NO,
  31. TRIM (INVHDR.PARTY_CODE) PARTY_CODE,
  32. TRIM (INVDET.INVENTORY_CODE) INVENTORY_CODE,
  33. INVDET.QTY,
  34. INVDET.PRE_TAX_EXTENDED_AMT,
  35. INVDET.PRE_TAX_EXTENDED_HOME_AMT,
  36. (LED.RECEIVED_COST) ACTUAL_COST,
  37. DATE (INVHDR.SUPPLIER_INVOICE_DATE) INVOICE_DATE,
  38. MONTH(INVHDR.SUPPLIER_INVOICE_DATE) INVOICEMONTH,
  39. YEAR(INVHDR.SUPPLIER_INVOICE_DATE) INVOICEYEAR,
  40. INVDET.LINE_ITEM_NO,
  41. INVHDR.CURRENCY_CODE,
  42. INVHDR.EXCH_RATE,
  43. LED.SOURCE_LOCATION_CODE,
  44. MFP.FINANCIAL_YEAR,
  45. MFP.FINANCIAL_PERIOD,
  46. INVHDR.PAYMENT_TERM_TENOR,
  47. DATE (POH.ORDER_DATE) ORDER_DATE,
  48. DATE (POSH.ETD_DATE) EST_SHM_DATE,
  49. DATE (POSH.ETA_DATE) ETA_DATE,
  50. DATE (LGIN.SHIPMENT_DATE_REQUESTED) SHIP_DATE_REQUESTED,
  51. DATE (LGIN.ARRIVAL_DATE_REQUESTED) ARR_DATE_REQ,
  52. DATE (LGIN.SHIPMENT_DATE_ACTUAL) SHIP_DATE_ACTUAL,
  53. DATE (LGIN.ARRIVAL_DATE_ACTUAL) ARR_DATE_ACTUAL,
  54. DATE (LGIN.GOODS_RECEIPT_DATE) GOODS_RECEIPT_DATE,
  55. INVDET.NO_OF_PACK,
  56. INVDET.PACK_SIZE_CODE,
  57. LGIN.LOADING_LOCATION_CODE,
  58. POH.PURCHASE_ORDER_NO,
  59. POH.REFERENCE_NO PO_REFERENCE_NO,
  60. POH.SUBJECT PO_SUBJECT
  61. FROM LG_AP_INV_HST_HDR INVHDR
  62. LEFT OUTER JOIN LG_AP_INV_HST_DET INVDET ON (INVHDR.INVOICE_NO = INVDET.INVOICE_NO)
  63. LEFT OUTER JOIN LG_IN_SHM_HST_DET LISHD ON (INVDET.SOURCE_VOUCHER_NO=LISHD.SHIPMENT_VOUCHER_NO AND INVDET.SOURCE_VOUCHER_LINE_ITEM_NO=LISHD.LINE_ITEM_NO)
  64. LEFT OUTER JOIN LG_IN_SHM_HST_HDR LISHH ON (LISHD.SHIPMENT_VOUCHER_NO = LISHH.SHIPMENT_VOUCHER_NO)
  65. LEFT OUTER JOIN PO_ORDER_OST_DET POOD ON (LISHH.SOURCE_VOUCHER_NO = POOD.PURCHASE_ORDER_NO)
  66. LEFT OUTER JOIN IC_INVENTORY_LEDGER LED ON (LED.VOUCHER_NO = INVHDR.SOURCE_VOUCHER_NO AND LED.LINE_ITEM_NO = INVDET.SOURCE_VOUCHER_LINE_ITEM_NO)
  67. LEFT OUTER JOIN MT_FINANCIAL_PERIOD MFP ON (INVHDR.SUPPLIER_INVOICE_DATE BETWEEN MFP.PERIOD_START_DATE AND MFP.PERIOD_CLOSING_DATE)
  68. LEFT OUTER JOIN LG_IN_SHM_HST_HDR LGIN ON (LGIN.SHIPMENT_VOUCHER_NO = INVHDR.SOURCE_VOUCHER_NO)
  69. LEFT OUTER JOIN PO_ORDER_OST_HDR POH ON (POH.PURCHASE_ORDER_NO = LGIN.SOURCE_VOUCHER_NO)
  70. LEFT OUTER JOIN PO_ORDER_OST_SHM_HDR POSH ON (POSH.PURCHASE_ORDER_NO = POH.PURCHASE_ORDER_NO AND POSH.SHIPMENT_NO = LGIN.SOURCE_VOUCHER_SHIPMENT_NO)
  71. WHERE INVHDR.SOURCE_VOUCHER_NO IS NOT NULL
  72. UNION
  73. SELECT TRIM (INVDET.SOURCE_VOUCHER_NO) GRN_NO,
  74. TRIM (INVHDR.INVOICE_NO) INVOICE_NO,
  75. TRIM (INVHDR.PARTY_CODE) PARTY_CODE,
  76. TRIM (INVDET.INVENTORY_CODE) INVENTORY_CODE,
  77. INVDET.QTY,
  78. INVDET.PRE_TAX_EXTENDED_AMT,
  79. INVDET.PRE_TAX_EXTENDED_HOME_AMT,
  80. (LED.RECEIVED_COST) ACTUAL_COST,
  81. DATE (INVHDR.SUPPLIER_INVOICE_DATE) INVOICE_DATE,
  82. MONTH(INVHDR.SUPPLIER_INVOICE_DATE) INVOICEMONTH,
  83. YEAR(INVHDR.SUPPLIER_INVOICE_DATE) INVOICEYEAR,
  84. INVDET.LINE_ITEM_NO,
  85. INVHDR.CURRENCY_CODE,
  86. INVHDR.EXCH_RATE,
  87. LED.SOURCE_LOCATION_CODE,
  88. MFP.FINANCIAL_YEAR,
  89. MFP.FINANCIAL_PERIOD,
  90. INVHDR.PAYMENT_TERM_TENOR,
  91. DATE (POH.ORDER_DATE) ORDER_DATE,
  92. DATE (POSH.ETD_DATE) EST_SHM_DATE,
  93. DATE (POSH.ETA_DATE) ETA_DATE,
  94. DATE (LGIN.SHIPMENT_DATE_REQUESTED) SHIP_DATE_REQUESTED,
  95. DATE (LGIN.ARRIVAL_DATE_REQUESTED) ARR_DATE_REQ,
  96. DATE (LGIN.SHIPMENT_DATE_ACTUAL) SHIP_DATE_ACTUAL,
  97. DATE (LGIN.ARRIVAL_DATE_ACTUAL) ARR_DATE_ACTUAL,
  98. DATE (LGIN.GOODS_RECEIPT_DATE) GOODS_RECEIPT_DATE,
  99. INVDET.NO_OF_PACK,
  100. INVDET.PACK_SIZE_CODE,
  101. LGIN.LOADING_LOCATION_CODE,
  102. POH.PURCHASE_ORDER_NO,
  103. POH.REFERENCE_NO PO_REFERENCE_NO,
  104. POH.SUBJECT PO_SUBJECT
  105. FROM LG_AP_INV_HST_HDR INVHDR
  106. LEFT OUTER JOIN LG_AP_INV_HST_DET INVDET ON (INVHDR.INVOICE_NO = INVDET.INVOICE_NO)
  107. LEFT OUTER JOIN LG_IN_SHM_HST_DET LISHD ON (INVDET.SOURCE_VOUCHER_NO=LISHD.SHIPMENT_VOUCHER_NO AND INVDET.SOURCE_VOUCHER_LINE_ITEM_NO=LISHD.LINE_ITEM_NO)
  108. LEFT OUTER JOIN LG_IN_SHM_HST_HDR LISHH ON (LISHD.SHIPMENT_VOUCHER_NO = LISHH.SHIPMENT_VOUCHER_NO)
  109. LEFT OUTER JOIN PO_ORDER_OST_DET POOD ON (LISHH.SOURCE_VOUCHER_NO = POOD.PURCHASE_ORDER_NO)
  110. LEFT OUTER JOIN IC_INVENTORY_LEDGER LED ON (LED.VOUCHER_NO = INVDET.SOURCE_VOUCHER_NO AND LED.LINE_ITEM_NO = INVDET.SOURCE_VOUCHER_LINE_ITEM_NO)
  111. LEFT OUTER JOIN MT_FINANCIAL_PERIOD MFP ON (INVHDR.SUPPLIER_INVOICE_DATE BETWEEN MFP.PERIOD_START_DATE AND MFP.PERIOD_CLOSING_DATE)
  112. LEFT OUTER JOIN LG_IN_SHM_HST_HDR LGIN ON (LGIN.SHIPMENT_VOUCHER_NO = INVHDR.SOURCE_VOUCHER_NO)
  113. LEFT OUTER JOIN PO_ORDER_OST_HDR POH ON (POH.PURCHASE_ORDER_NO = LGIN.SOURCE_VOUCHER_NO)
  114. LEFT OUTER JOIN PO_ORDER_OST_SHM_HDR POSH ON (POSH.PURCHASE_ORDER_NO = POH.PURCHASE_ORDER_NO AND POSH.SHIPMENT_NO = LGIN.SOURCE_VOUCHER_SHIPMENT_NO)
  115. WHERE INVHDR.SOURCE_VOUCHER_NO IS NULL
  116. UNION
  117. SELECT '' GRN_NO,
  118. TRIM (ARH.CREDIT_NOTE_NO) INVOICE_NO,
  119. TRIM (ARH.SUPPLIER_CODE) PARTY_CODE,
  120. TRIM (ARD.INVENTORY_CODE) INVENTORY_CODE,
  121. (-1*ARD.QTY) QTY,
  122. (-1*ARD.PRE_TAX_EXTENDED_AMT) PRE_TAX_EXTENDED_AMT,
  123. (-1*ARD.PRE_TAX_EXTENDED_HOME_AMT) PRE_TAX_EXTENDED_HOME_AMT,
  124. (-1*LED.ISSUED_COST) ACTUAL_COST,
  125. DATE (ARH.AGE_DATE) INVOICE_DATE,
  126. MONTH(AGE_DATE) INVOICEMONTH,
  127. YEAR(AGE_DATE) INVOICEYEAR,
  128. ARD.LINE_ITEM_NO,
  129. ARH.CURRENCY_CODE,
  130. ARH.EXCH_RATE,
  131. LED.SOURCE_LOCATION_CODE,
  132. ARH.YEAR_POSTED_TO FINANCIAL_YEAR,
  133. ARH.PERIOD_POSTED_TO FINANCIAL_PERIOD,
  134. 0 PAYMENT_TERM_TENOR,
  135. DATE ('1990-01-01 00:00:00') ORDER_DATE,
  136. DATE ('1990-01-01 00:00:00') EST_SHM_DATE,
  137. DATE ('1990-01-01 00:00:00') ETA_DATE,
  138. DATE ('1990-01-01 00:00:00') SHIP_DATE_REQUESTED,
  139. DATE ('1990-01-01 00:00:00') ARR_DATE_REQ,
  140. DATE ('1990-01-01 00:00:00') SHIP_DATE_ACTUAL,
  141. DATE ('1990-01-01 00:00:00') ARR_DATE_ACTUAL,
  142. DATE ('1990-01-01 00:00:00') GOODS_RECEIPT_DATE,
  143. ARD.NO_OF_PACK,
  144. ARD.PACK_SIZE_CODE,
  145. ARH.LOCATION_CODE,
  146. NULL PURCHASE_ORDER_NO,
  147. NULL PO_REFERENCE_NO,
  148. NULL PO_SUBJECT
  149. FROM AP_CRN_HST_HDR ARH
  150. LEFT OUTER JOIN AP_CRN_HST_DET ARD ON (ARH.CREDIT_NOTE_NO = ARD.CREDIT_NOTE_NO)
  151. LEFT OUTER JOIN IC_INVENTORY_LEDGER LED ON (LED.VOUCHER_NO = ARH.CREDIT_NOTE_NO AND LED.LINE_ITEM_NO = ARD.LINE_ITEM_NO)
  152. WHERE ARH.CREDIT_NOTE_TYPE != 'O') A
  153. LEFT OUTER JOIN MT_PARTY PARTY ON (PARTY.PARTY_CODE = A.PARTY_CODE)
  154. LEFT OUTER JOIN MT_INVENTORY INVENTORY ON (INVENTORY.INVENTORY_CODE = A.INVENTORY_CODE)
  155. LEFT OUTER JOIN MT_INVENTORY_CATEGORY INV_CAT ON (INVENTORY.INVENTORY_CATEGORY_CODE = INV_CAT.INVENTORY_CATEGORY_CODE)
  156. LEFT OUTER JOIN MT_INVENTORY_CLASS INV_CLASS ON (INVENTORY.INVENTORY_CLASS_CODE = INV_CLASS.INVENTORY_CLASS_CODE)
  157. LEFT OUTER JOIN MT_CUSTOMER CUSTOMER ON (CUSTOMER.CUSTOMER_CODE = A.PARTY_CODE)
  158. LEFT OUTER JOIN MT_CUSTOMER_CATEGORY CUSTOMERCAT ON (CUSTOMER.CUSTOMER_CATEGORY_CODE = CUSTOMERCAT.CUSTOMER_CATEGORY_CODE)
  159. LEFT OUTER JOIN MT_INDUSTRY INDUSTRY ON (INDUSTRY.INDUSTRY_CODE = CUSTOMER.INDUSTRY_CODE)
  160. LEFT OUTER JOIN MT_COUNTRY COUNTRY_OF_INCORPORATION ON (COUNTRY_OF_INCORPORATION.COUNTRY_CODE = PARTY.COUNTRY_OF_INCORPORATION)
  161. LEFT OUTER JOIN MT_COUNTRY COUNTRY_OF_INVENTORY ON (COUNTRY_OF_INVENTORY.COUNTRY_CODE = INVENTORY.COUNTRY_OF_ORIGIN)
  162. LEFT OUTER JOIN MT_INVENTORY_MODEL MODEL ON (INVENTORY.INVENTORY_MODEL_CODE = MODEL.INVENTORY_MODEL_CODE AND MODEL.INVENTORY_CATEGORY_CODE = INVENTORY.INVENTORY_CATEGORY_CODE)
  163. LEFT OUTER JOIN MT_UOM UOM ON (UOM.UOM_CODE = INVENTORY.UOM_CODE),MT_INVENTORY_MODEL_SPEC SPEC,MT_INVENTORY_SPECIFICATION SPECIFICATION,MT_INVENTORY_SPEC_VAL_MASTER SPECVAL
  164. WHERE (SPEC.INVENTORY_CATEGORY_CODE = MODEL.INVENTORY_CATEGORY_CODE AND SPEC.INVENTORY_MODEL_CODE = MODEL.INVENTORY_MODEL_CODE)
  165. AND (SPECIFICATION.INVENTORY_CODE = A.INVENTORY_CODE AND SPEC.INVENTORY_SPEC_CODE = SPECIFICATION.INVENTORY_SPEC_CODE)
  166. AND SPECVAL.SPEC_VALUE_CODE = SPECIFICATION.SPEC_VALUE_CODE
  167. AND SPECVAL.INVENTORY_SPEC_CODE = SPECIFICATION.INVENTORY_SPEC_CODE
  168. AND A.INVENTORY_CODE IS NOT NULL
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!