Thursday, November 17, 2011

GL JOURNAL LINES WITH AP SOURCE REFERENCE FIELDS

/* GL JOURNAL LINES WITH AP SOURCE REFERENCE FIELDS
(Tested on Vision 11.5.10.2  June 2007 ) */
SELECT SOB.SHORT_NAME"BOOK"
,    GLS.USER_JE_SOURCE_NAME"SOURCE"
,    GLC.USER_JE_CATEGORY_NAME"CATEGORY"
,    GJB.NAME"BATCH NAME"
,    GJH.NAME"JOURNAL NAME"
,    GJH.CURRENCY_CODE"CURRENCY"
,    GJL.JE_LINE_NUM"JRNL LINE#"
,    GJL.EFFECTIVE_DATE"ACCOUNTING DATE"
,    GJH.PERIOD_NAME"PERIOD"
,    GJH.DATE_CREATED"CREATED DATE"
,    GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
       ||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 "ACCOUNT COMBINATION"
,    GJL.ENTERED_DR
,    GJL.ENTERED_CR
,    GJL.ACCOUNTED_DR
,    GJL.ACCOUNTED_CR
,    GJH.CURRENCY_CONVERSION_RATE"CONV RATE"
,    GJH.CURRENCY_CONVERSION_DATE"CONV DATE"
,    GJH.CURRENCY_CONVERSION_TYPE"CONV TYPE"
,    GJL.DESCRIPTION
,      GJL.REFERENCE_1"AP VAND NAME"
,      GJL.REFERENCE_2"AP INV_ID"
,      GJL.REFERENCE_3"AP INV LINE#CHEQUEID"
,      GJL.REFERENCE_4"AP PAYDOC#"
,      GJL.REFERENCE_5"AP INVOICE #"
,      GJL.REFERENCE_6"AP ACCOUNTING TYPE"
,      GJL.REFERENCE_7"AP SOURCE ID"
,      GJL.REFERENCE_8"AP NA"
,      GJL.REFERENCE_9"AP DOCUMENT ID"
,      GJL.REFERENCE_10"AP LINE TYPE"
FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH, GL_JE_LINES GJL,GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB,
  GL_JE_SOURCES GLS, GL_JE_CATEGORIES GLC
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJH.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND  GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GLS.USER_JE_SOURCE_NAME = 'Payables'
and GJH.PERIOD_NAME = 'JUL-04'
--and sob.set_of_books_id = 87
order by 1,2,3,4,5,7

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect