VIEW: APPS.PA_XLA_CCDL_LINES_V

Source

View Text - Preformatted

SELECT ccdl.acct_event_id, ccdl.expenditure_item_id, ccdl.line_num, imp.set_of_books_id, ccdl.line_type, ccdl.cc_dist_line_id, DECODE(NVL(CCDL.Acct_source_Code, 'UPG'), 'PA', DECODE(ccdl.dist_line_id_reversed, NULL, NULL, ccdl.dist_line_id_reversed), NULL), DECODE(ccdl.DIST_LINE_ID_REVERSED, NULL, 'N', DECODE(ccdl_orig.expenditure_item_id, ccdl.expenditure_item_id, 'Y', 'N')), ccdl.cross_charge_code, ccdl.reference_1, ccdl.reference_2, ccdl.reference_3, ccdl.denom_tp_currency_code, DECODE(NVL(CCDL.Acct_source_Code, 'UPG'), 'PA', DECODE(ccdl_orig.expenditure_item_id, ccdl.expenditure_item_id, CCDL_ORIG.LINE_NUM), NULL), decode(ccdl.line_type, 'BL', ccdl.dr_code_combination_id , NULL) , decode(ccdl.line_type, 'BL', ccdl.cr_code_combination_id , NULL) , decode(ccdl.line_type, 'PC', ccdl.dr_code_combination_id , NULL) , decode(ccdl.line_type, 'PC', ccdl.cr_code_combination_id , NULL) , DECODE(ccdl.DIST_LINE_ID_REVERSED, NULL, 'Y', DECODE(ccdl_orig.expenditure_item_id, ccdl.expenditure_item_id, 'Y', 'N')), decode(ccdl.line_type, 'BL', DECODE(ccdl.DIST_LINE_ID_REVERSED, NULL, ccdl.dr_code_combination_id, DECODE(ccdl_orig.expenditure_item_id, ccdl.expenditure_item_id, ccdl.dr_code_combination_id, PA_XLA_INTERFACE_PKG.Get_Sla_Ccid(275, ccdl_orig.Expenditure_Item_id, ccdl_orig.Line_Num, ccdl_orig.Line_Type, 'DEBIT', imp.set_of_books_id)) ), NULL) , decode(ccdl.line_type, 'BL', DECODE(ccdl.DIST_LINE_ID_REVERSED, NULL, ccdl.cr_code_combination_id, DECODE(ccdl_orig.expenditure_item_id, ccdl.expenditure_item_id, ccdl.cr_code_combination_id, PA_XLA_INTERFACE_PKG.Get_Sla_Ccid(275, ccdl_orig.Expenditure_Item_id, ccdl_orig.Line_Num, ccdl_orig.Line_Type, 'CREDIT', imp.set_of_books_id)) ), NULL) , decode(ccdl.line_type, 'PC', DECODE(ccdl.DIST_LINE_ID_REVERSED, NULL, ccdl.dr_code_combination_id, DECODE(ccdl_orig.expenditure_item_id, ccdl.expenditure_item_id, ccdl.dr_code_combination_id, PA_XLA_INTERFACE_PKG.Get_Sla_Ccid(275, ccdl_orig.Expenditure_Item_id, ccdl_orig.Line_Num, ccdl_orig.Line_Type, 'DEBIT', imp.set_of_books_id)) ), NULL) , decode(ccdl.line_type, 'PC', DECODE(ccdl.DIST_LINE_ID_REVERSED, NULL, ccdl.cr_code_combination_id, DECODE(ccdl_orig.expenditure_item_id, ccdl.expenditure_item_id, ccdl.cr_code_combination_id, PA_XLA_INTERFACE_PKG.Get_Sla_Ccid(275, ccdl_orig.Expenditure_Item_id, ccdl_orig.Line_Num, ccdl_orig.Line_Type, 'CREDIT', imp.set_of_books_id)) ), NULL) , ccdl.denom_transfer_price, ccdl.amount, ccdl.acct_tp_exchange_rate, ccdl.acct_tp_rate_date, ccdl.acct_tp_rate_type ,DECODE(NVL(ccdl.Acct_Source_Code, 'UPG'), 'UPG', 'Y', 'N') ,DECODE(ccdl.line_type, 'BL', 'RECEIVER_COST' , 'RECLASS_DESTINATION') ,DECODE(ccdl.line_type, 'BL', 'PROVIDER_COST' , 'RECLASS_SOURCE') from pa_cc_dist_lines_all ccdl ,pa_cc_dist_lines_all ccdl_orig ,pa_implementations_all imp WHERE imp.org_id = ccdl.org_id and ccdl_orig.CC_DIST_LINE_ID(+) = ccdl.DIST_LINE_ID_REVERSED

View Text - HTML Formatted

SELECT CCDL.ACCT_EVENT_ID
, CCDL.EXPENDITURE_ITEM_ID
, CCDL.LINE_NUM
, IMP.SET_OF_BOOKS_ID
, CCDL.LINE_TYPE
, CCDL.CC_DIST_LINE_ID
, DECODE(NVL(CCDL.ACCT_SOURCE_CODE
, 'UPG')
, 'PA'
, DECODE(CCDL.DIST_LINE_ID_REVERSED
, NULL
, NULL
, CCDL.DIST_LINE_ID_REVERSED)
, NULL)
, DECODE(CCDL.DIST_LINE_ID_REVERSED
, NULL
, 'N'
, DECODE(CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL.EXPENDITURE_ITEM_ID
, 'Y'
, 'N'))
, CCDL.CROSS_CHARGE_CODE
, CCDL.REFERENCE_1
, CCDL.REFERENCE_2
, CCDL.REFERENCE_3
, CCDL.DENOM_TP_CURRENCY_CODE
, DECODE(NVL(CCDL.ACCT_SOURCE_CODE
, 'UPG')
, 'PA'
, DECODE(CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL.EXPENDITURE_ITEM_ID
, CCDL_ORIG.LINE_NUM)
, NULL)
, DECODE(CCDL.LINE_TYPE
, 'BL'
, CCDL.DR_CODE_COMBINATION_ID
, NULL)
, DECODE(CCDL.LINE_TYPE
, 'BL'
, CCDL.CR_CODE_COMBINATION_ID
, NULL)
, DECODE(CCDL.LINE_TYPE
, 'PC'
, CCDL.DR_CODE_COMBINATION_ID
, NULL)
, DECODE(CCDL.LINE_TYPE
, 'PC'
, CCDL.CR_CODE_COMBINATION_ID
, NULL)
, DECODE(CCDL.DIST_LINE_ID_REVERSED
, NULL
, 'Y'
, DECODE(CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL.EXPENDITURE_ITEM_ID
, 'Y'
, 'N'))
, DECODE(CCDL.LINE_TYPE
, 'BL'
, DECODE(CCDL.DIST_LINE_ID_REVERSED
, NULL
, CCDL.DR_CODE_COMBINATION_ID
, DECODE(CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL.EXPENDITURE_ITEM_ID
, CCDL.DR_CODE_COMBINATION_ID
, PA_XLA_INTERFACE_PKG.GET_SLA_CCID(275
, CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL_ORIG.LINE_NUM
, CCDL_ORIG.LINE_TYPE
, 'DEBIT'
, IMP.SET_OF_BOOKS_ID)) )
, NULL)
, DECODE(CCDL.LINE_TYPE
, 'BL'
, DECODE(CCDL.DIST_LINE_ID_REVERSED
, NULL
, CCDL.CR_CODE_COMBINATION_ID
, DECODE(CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL.EXPENDITURE_ITEM_ID
, CCDL.CR_CODE_COMBINATION_ID
, PA_XLA_INTERFACE_PKG.GET_SLA_CCID(275
, CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL_ORIG.LINE_NUM
, CCDL_ORIG.LINE_TYPE
, 'CREDIT'
, IMP.SET_OF_BOOKS_ID)) )
, NULL)
, DECODE(CCDL.LINE_TYPE
, 'PC'
, DECODE(CCDL.DIST_LINE_ID_REVERSED
, NULL
, CCDL.DR_CODE_COMBINATION_ID
, DECODE(CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL.EXPENDITURE_ITEM_ID
, CCDL.DR_CODE_COMBINATION_ID
, PA_XLA_INTERFACE_PKG.GET_SLA_CCID(275
, CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL_ORIG.LINE_NUM
, CCDL_ORIG.LINE_TYPE
, 'DEBIT'
, IMP.SET_OF_BOOKS_ID)) )
, NULL)
, DECODE(CCDL.LINE_TYPE
, 'PC'
, DECODE(CCDL.DIST_LINE_ID_REVERSED
, NULL
, CCDL.CR_CODE_COMBINATION_ID
, DECODE(CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL.EXPENDITURE_ITEM_ID
, CCDL.CR_CODE_COMBINATION_ID
, PA_XLA_INTERFACE_PKG.GET_SLA_CCID(275
, CCDL_ORIG.EXPENDITURE_ITEM_ID
, CCDL_ORIG.LINE_NUM
, CCDL_ORIG.LINE_TYPE
, 'CREDIT'
, IMP.SET_OF_BOOKS_ID)) )
, NULL)
, CCDL.DENOM_TRANSFER_PRICE
, CCDL.AMOUNT
, CCDL.ACCT_TP_EXCHANGE_RATE
, CCDL.ACCT_TP_RATE_DATE
, CCDL.ACCT_TP_RATE_TYPE
, DECODE(NVL(CCDL.ACCT_SOURCE_CODE
, 'UPG')
, 'UPG'
, 'Y'
, 'N')
, DECODE(CCDL.LINE_TYPE
, 'BL'
, 'RECEIVER_COST'
, 'RECLASS_DESTINATION')
, DECODE(CCDL.LINE_TYPE
, 'BL'
, 'PROVIDER_COST'
, 'RECLASS_SOURCE')
FROM PA_CC_DIST_LINES_ALL CCDL
, PA_CC_DIST_LINES_ALL CCDL_ORIG
, PA_IMPLEMENTATIONS_ALL IMP
WHERE IMP.ORG_ID = CCDL.ORG_ID
AND CCDL_ORIG.CC_DIST_LINE_ID(+) = CCDL.DIST_LINE_ID_REVERSED