Account Inquiry (总账与子账的关联 - Cost Management - WIP)

--Source:Cost Management,Category:WIP
Select ac.je_source,
       ac.je_category,
       gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
       gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 Account_Number,
       --gcc.start_date_active,
       --gcc.end_date_active,
       --gir.gl_sl_link_id,
       --xal.ae_header_id,
       --xal.ae_line_num,
       --xdl.source_distribution_id_num_1,
       ac.BATCH_NAME,
       ac.HEADER_NAME,
       ac.LINE_JE_LINE_NUM je_line_num,
       ac.line_accounted_dr,
       ac.line_accounted_cr,
       ac.currency_code,
       xal.accounting_date,
       xal.accounted_dr,
       xal.accounted_cr,
       wt.organization_id,
       msib.inventory_item_id,
       msib.segment1 item_num,
       wta.primary_quantity,
       wta.base_transaction_value,
       wta.reference_account,
       wta.transaction_id,
       wt.transaction_date
--,ac.*
  from (SELECT b.rowid BATCH_ROW_ID,
               b.je_batch_id JE_BATCH_ID,
               h.ledger_id LEDGER_ID,
               b.default_period_name PERIOD_NAME,
               ps.period_year PERIOD_YEAR,
               ps.period_num PERIOD_NUM,
               ps.effective_period_num EFFECTIVE_PERIOD_NUM,
               b.actual_flag ACTUAL_FLAG,
               b.status BATCH_STATUS,
               b.budgetary_control_status BUDGETARY_CONTROL_STATUS,
               b.name BATCH_NAME,
               b.description BATCH_DESCRIPTION,
               b.date_created BATCH_DATE_CREATED,
               b.posted_date BATCH_POSTED_DATE,
               b.ussgl_transaction_code BATCH_USSGL_TRANSACTION_CODE,
               b.control_total BATCH_CONTROL_TOTAL,
               b.running_total_dr BATCH_RUNNING_TOTAL_DR,
               b.running_total_cr BATCH_RUNNING_TOTAL_CR,
               decode(lr.relationship_type_code,
                      'BALANCE',
                      null,
                      b.running_total_accounted_dr) BATCH_RUN_TOTAL_ACCOUNTED_DR,
               decode(lr.relationship_type_code,
                      'BALANCE',
                      null,
                      b.running_total_accounted_cr) BATCH_RUN_TOTAL_ACCOUNTED_CR,
               b.status_verified BATCH_STATUS_VERIFIED,
               b.default_effective_date BATCH_EFFECTIVE_DATE,
               b.status_reset_flag BATCH_STATUS_RESET_FLAG,
               b.unique_date BATCH_UNIQUE_DATE,
               b.earliest_postable_date BATCH_EARLIEST_POSTABLE_DATE,
               b.packet_id PACKET_ID,
               b.unreservation_packet_id UNRESERVATION_PACKET_ID,
               b.posting_run_id POSTING_RUN_ID,
               b.request_id REQUEST_ID,
               b.ATTRIBUTE1 BATCH_ATTRIBUTE1,
               b.ATTRIBUTE2 BATCH_ATTRIBUTE2,
               b.ATTRIBUTE3 BATCH_ATTRIBUTE3,
               b.ATTRIBUTE4 BATCH_ATTRIBUTE4,
               b.ATTRIBUTE5 BATCH_ATTRIBUTE5,
               b.ATTRIBUTE6 BATCH_ATTRIBUTE6,
               b.ATTRIBUTE7 BATCH_ATTRIBUTE7,
               b.ATTRIBUTE8 BATCH_ATTRIBUTE8,
               b.ATTRIBUTE9 BATCH_ATTRIBUTE9,
               b.ATTRIBUTE10 BATCH_ATTRIBUTE10,
               b.CONTEXT BATCH_CONTEXT,
               b.CONTEXT2 BATCH_CONTEXT2,
               b.creation_date BATCH_CREATION_DATE,
               b.created_by BATCH_CREATED_BY,
               b.last_update_date BATCH_LAST_UPDATE_DATE,
               b.last_updated_by BATCH_LAST_UPDATED_BY,
               b.last_update_login BATCH_LAST_UPDATE_LOGIN,
               h.rowid HEADER_ROW_ID,
               h.je_header_id JE_HEADER_ID,
               h.name HEADER_NAME,
               h.je_source JE_SOURCE,
               h.je_category JE_CATEGORY,
               h.description HEADER_DESCRIPTION,
               h.external_reference EXTERNAL_REFERENCE,
               h.doc_sequence_id DOC_SEQUENCE_ID,
               h.doc_sequence_value DOC_SEQUENCE_VALUE,
               h.encumbrance_type_id ENCUMBRANCE_TYPE_ID,
               h.budget_version_id BUDGET_VERSION_ID,
               h.default_effective_date HEADER_EFFECTIVE_DATE,
               h.USSGL_TRANSACTION_CODE HEADER_USSGL_TRANSACTION_CODE,
               h.currency_code CURRENCY_CODE,
               h.currency_conversion_date CURRENCY_CONVERSION_DATE,
               h.currency_conversion_type CURRENCY_CONVERSION_TYPE,
               h.currency_conversion_rate CURRENCY_CONVERSION_RATE,
               h.accrual_rev_flag ACCRUAL_REV_FLAG,
               h.accrual_rev_period_name ACCRUAL_REV_PERIOD_NAME,
               h.accrual_rev_status ACCRUAL_REV_STATUS,
               h.control_total HEADER_CONTROL_TOTAL,
               h.running_total_dr HEADER_RUNNING_TOTAL_DR,
               h.running_total_cr HEADER_RUNNING_TOTAL_CR,
               decode(lr.relationship_type_code,
                      'BALANCE',
                      null,
                      h.running_total_accounted_dr) HEADER_RUN_TOTAL_ACCOUNTED_DR,
               decode(lr.relationship_type_code,
                      'BALANCE',
                      null,
                      h.RUNNING_TOTAL_ACCOUNTED_CR) HEADER_RUN_TOTAL_ACCOUNTED_CR,
               h.status HEADER_STATUS,
               h.date_created HEADER_DATE_CREATED,
               h.multi_bal_seg_flag MULTI_BAL_SEG_FLAG,
               h.conversion_flag CONVERSION_FLAG,
               h.balanced_je_flag BALANCED_JE_FLAG,
               h.balancing_segment_value BALANCING_SEGMENT_VALUE,
               h.from_recurring_header_id FROM_RECURRING_HEADER_ID,
               h.unique_date HEADER_UNIQUE_DATE,
               h.earliest_postable_date HEADER_EARLIEST_POSTABLE_DATE,
               h.posted_date HEADER_POSTED_DATE,
               h.accrual_rev_effective_date ACCRUAL_REV_EFFECTIVE_DATE,
               h.accrual_rev_je_header_id ACCRUAL_REV_JE_HEADER_ID,
               h.accrual_rev_change_sign_flag ACCRUAL_REV_CHANGE_SIGN_FLAG,
               h.parent_je_header_id PARENT_JE_HEADER_ID,
               h.ATTRIBUTE1 HEADER_ATTRIBUTE1,
               h.ATTRIBUTE2 HEADER_ATTRIBUTE2,
               h.ATTRIBUTE3 HEADER_ATTRIBUTE3,
               h.ATTRIBUTE4 HEADER_ATTRIBUTE4,
               h.ATTRIBUTE5 HEADER_ATTRIBUTE5,
               h.ATTRIBUTE6 HEADER_ATTRIBUTE6,
               h.ATTRIBUTE7 HEADER_ATTRIBUTE7,
               h.ATTRIBUTE8 HEADER_ATTRIBUTE8,
               h.ATTRIBUTE9 HEADER_ATTRIBUTE9,
               h.ATTRIBUTE10 HEADER_ATTRIBUTE10,
               h.CONTEXT HEADER_CONTEXT,
               h.CONTEXT2 HEADER_CONTEXT2,
               h.creation_date HEADER_CREATION_DATE,
               h.created_by HEADER_CREATED_BY,
               h.last_update_date HEADER_LAST_UPDATE_DATE,
               h.last_updated_by HEADER_LAST_UPDATED_BY,
               h.last_update_login HEADER_LAST_UPDATE_LOGIN,
               lines.rowid ROW_ID,
               lines.ledger_id LINE_LEDGER_ID,
               lines.je_line_num LINE_JE_LINE_NUM,
               lines.code_combination_id LINE_CODE_COMBINATION_ID,
               lines.period_name LINE_PERIOD_NAME,
               lines.effective_date LINE_EFFECTIVE_DATE,
               lines.entered_dr LINE_ENTERED_DR_NUM,
               lines.entered_cr LINE_ENTERED_CR_NUM,
               lines.entered_dr LINE_ENTERED_DR,
               lines.entered_cr LINE_ENTERED_CR,
               decode(lr.relationship_type_code,
                      'BALANCE',
                      null,
                      lines.accounted_dr) LINE_ACCOUNTED_DR,
               decode(lr.relationship_type_code,
                      'BALANCE',
                      null,
                      lines.accounted_cr) LINE_ACCOUNTED_CR,
               lines.description LINE_DESCRIPTION,
               lines.reference_1 LINE_REFERENCE_1,
               lines.reference_2 LINE_REFERENCE_2,
               lines.reference_3 LINE_REFERENCE_3,
               lines.reference_4 LINE_REFERENCE_4,
               lines.reference_5 LINE_REFERENCE_5,
               lines.reference_6 LINE_REFERENCE_6,
               lines.reference_7 LINE_REFERENCE_7,
               lines.reference_8 LINE_REFERENCE_8,
               lines.reference_9 LINE_REFERENCE_9,
               lines.reference_10 LINE_REFERENCE_10,
               lines.attribute1 LINE_ATTRIBUTE1,
               lines.attribute2 LINE_ATTRIBUTE2,
               lines.attribute3 LINE_ATTRIBUTE3,
               lines.attribute4 LINE_ATTRIBUTE4,
               lines.attribute5 LINE_ATTRIBUTE5,
               lines.attribute6 LINE_ATTRIBUTE6,
               lines.attribute7 LINE_ATTRIBUTE7,
               lines.attribute8 LINE_ATTRIBUTE8,
               lines.attribute9 LINE_ATTRIBUTE9,
               lines.attribute10 LINE_ATTRIBUTE10,
               lines.attribute11 LINE_ATTRIBUTE11,
               lines.attribute12 LINE_ATTRIBUTE12,
               lines.attribute13 LINE_ATTRIBUTE13,
               lines.attribute14 LINE_ATTRIBUTE14,
               lines.attribute15 LINE_ATTRIBUTE15,
               lines.attribute16 LINE_ATTRIBUTE16,
               lines.attribute17 LINE_ATTRIBUTE17,
               lines.attribute18 LINE_ATTRIBUTE18,
               lines.attribute19 LINE_ATTRIBUTE19,
               lines.attribute20 LINE_ATTRIBUTE20,
               lines.context LINE_CONTEXT,
               lines.context2 LINE_CONTEXT2,
               lines.context3 LINE_CONTEXT3,
               lines.context4 LINE_CONTEXT4,
               lines.invoice_date LINE_INVOICE_DATE,
               lines.tax_code LINE_TAX_CODE,
               lines.invoice_identifier LINE_INVOICE_IDENTIFIER,
               lines.invoice_amount LINE_INVOICE_AMOUNT,
               lines.no1 LINE_NO1,
               lines.stat_amount LINE_STAT_AMOUNT,
               lines.ignore_rate_flag LINE_IGNORE_RATE_FLAG,
               lines.ussgl_transaction_code LINE_USSGL_TRANSACTION_CODE,
               lines.subledger_doc_sequence_id SUBLEDGER_DOC_SEQUENCE_ID,
               lines.subledger_doc_sequence_value SUBLEDGER_DOC_SEQUENCE_VALUE,
               lines.creation_date CREATION_DATE,
               lines.created_by CREATED_BY,
               lines.last_update_date LAST_UPDATE_DATE,
               lines.last_updated_by LAST_UPDATED_BY,
               lines.last_update_login LAST_UPDATE_LOGIN,
               lr.target_ledger_name LEDGER_NAME,
               lr.target_currency_code LEDGER_CURRENCY
          FROM apps.gl_period_statuses      ps,
               apps.gl_je_lines             lines,
               apps.gl_je_headers           h,
               apps.gl_je_batches           b,
               apps.gl_ledger_relationships lr
         WHERE lr.source_ledger_id = lr.target_ledger_id
              /*   AND lr.target_currency_code =
                           decode(GLR03300_pkg.get_ledger_currency,
                                  'ALL123456789012345',
                                  lr.target_currency_code,
                                  GLR03300_pkg.get_ledger_currency)*/
           AND lr.application_id = 101
           AND b.average_journal_flag = 'N'
           AND b.je_batch_id = h.je_batch_id
              /*   AND b.actual_flag = decode(GLR03300_pkg.get_actual_flag,
                                                  'E',
                                                  decode(LR.relationship_type_code,
                                                         'BALANCE',
                                                         'X',
                                                         GLR03300_pkg.get_actual_flag),
                                                  GLR03300_pkg.get_actual_flag)*/
           AND h.je_header_id = lines.je_header_id
              /*   AND h.currency_code =
                           decode(GLR03300_PKG.get_entered_currency_code,
                                  'ALL123456789012345',
                                  h.currency_code,
                                  NULL,
                                  h.currency_code,
                                  GLR03300_pkg.get_entered_currency_code)*/
           AND h.ledger_id = lr.source_ledger_id
           AND lines.period_name = ps.period_name
           AND ps.ledger_id = lines.ledger_id
           AND ps.application_id = 101
              --AND h.ledger_id = 2041
              --and lines.code_combination_id = 5007
              --AND (nvl(ps.effective_period_num, 0) >= ((2008 * 10000) + 3))
              --and (nvl(ps.effective_period_num, 0) <= ((2008 * 10000) + 3))
           AND (h.currency_code != 'STAT')
           AND b.status || '' = 'P'
           AND b.actual_flag = 'A') Ac,
       apps.gl_code_combinations gcc,
       apps.GL_IMPORT_REFERENCES gir,
       apps.xla_ae_lines xal,
       apps.XLA_DISTRIBUTION_LINKS xdl,
       apps.WIP_TRANSACTION_ACCOUNTS wta,
       apps.WIP_TRANSACTIONS wt,
       apps.mtl_system_items_b msib
 where ac.line_code_combination_id = gcc.code_combination_id
   and gir.je_header_id(+) = ac.JE_HEADER_ID
   and gir.je_line_num(+) = ac.LINE_JE_LINE_NUM
   and gir.gl_sl_link_id = xal.gl_sl_link_id(+)
   and xal.ae_header_id = xdl.ae_header_id(+)
   and xal.ae_line_num = xdl.ae_line_num(+)
   and xdl.source_distribution_id_num_1 = wta.wip_sub_ledger_id(+)
   and wta.transaction_id = wt.transaction_id(+)
   and wt.primary_item_id = msib.inventory_item_id(+)
   and wt.organization_id = msib.organization_id(+)
   and ac.je_source = 'Cost Management'
   and ac.je_category = 'WIP'
   and ac.ledger_id = 2041 --- constant
   and ac.line_code_combination_id = 5007 --- code_combination_id
   AND (nvl(ac.effective_period_num, 0) >= ((2008 * 10000) + 3)) ---- gl_period 1 jan,2 feb....
   and (nvl(ac.effective_period_num, 0) <= ((2008 * 10000) + 3)) ---- gl_period