الانتقال إلى المحتوى
View in the app

A better way to browse. Learn more.
مجموعة مستخدمي أوراكل العربية

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

عايزة احول ريبورت اوراكل الى Rdf فى Cash Management

  •
amal mahmoud
بواسطة amal mahmoud
في منتدى Oracle E-Business Suite

Featured Replies

بتاريخ:

السلام عليكم

اذا سمحتم عايزة احول ريبورت من ريبورتات الاوراكل الى rdf واحطه على ال xml publisher وانا معنديش خبرة جامده فى البيزنس الريبورت اسمه

Transaction Available For Bank Reconciliation

وعايزه اليوزر يدخل 2 parameter only  وهما bank account,bank statement وعايزاه يطلع الUNRECONCILE DATA ONLY

 

فممكن حد يساعدنى فى تظبيط الكويرى

AND THIS IS THE ORACLE QUERY

 SELECT   DECODE (

              sl.status,

              'EXTERNAL',

              'RECONCILED',

              DECODE (sr.reference_type, NULL, 'UNRECONCILED', 'RECONCILED')

           )

              C_STAT_TYPE,

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', TO_CHAR (sl.line_number),

                   sh.statement_number || '/' || sl.line_number)

              C_SORT,

           sh.statement_header_id C_STAT_HEADER_ID,

           sl.statement_line_id C_STAT_LINE_ID,

           sh.doc_sequence_value C_STAT_DOC_SEQ,

           aba.bank_account_id C_BANK_ACCOUNT_ID,

           l1.meaning C_SL_TYPE_DSP,

           sl.trx_type C_SL_TYPE,

           aba.bank_account_name C_BANK_ACCOUNT_NAME,

           aba.bank_account_num C_BANK_ACCOUNT_NUM,

           aba.currency_code C_BANK_ACC_CURRENCY,

           abb.bank_branch_name C_BANK_BRANCH_NAME,

           abb.bank_name C_BANK_NAME,

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', TO_NUMBER (TO_CHAR (sh.statement_date, 'J')),

                   aba.bank_account_id)

              C_ORDERING,

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', sl.line_number,

                   TO_NUMBER (TO_CHAR (sh.statement_date, 'J')))

              C_ORDER_BY,

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', sh.statement_number,

                   aba.bank_account_num)

              C_STAT_NUMBER,

           sl.line_number C_SL_NUMBER,

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', TO_CHAR (sl.trx_date, :C_DATEFORMAT),

                   sh.statement_number)

              C_SL_DATE,

           NVL (sl.amount, 0) C_SL_AMOUNT,

           DECODE (sl.trx_type,

                   'DEBIT', NVL (-sl.amount, 0),

                   'CREDIT', NVL (sl.amount, 0),

                   'MISC_DEBIT', NVL (-sl.amount, 0),

                   'MISC_CREDIT', NVL (sl.amount, 0),

                   'NSF', NVL (-sl.amount, 0),

                   'STOP', NVL (sl.amount, 0),

                   'REJECTED', NVL (-sl.amount, 0),

                   'SWEEP_IN', NVL (sl.amount, 0),

                   'SWEEP_OUT', NVL (-sl.amount, 0),

                   0)

              C_SL_SIGNED_AMOUNT,

           DECODE (sl.trx_type, 'DEBIT', NVL (sl.amount, 0), 0) C_SL_DR,

           DECODE (sl.trx_type, 'MISC_DEBIT', NVL (sl.amount, 0), 0)

              C_SL_MISC_DR,

           DECODE (sl.trx_type, 'CREDIT', NVL (sl.amount, 0), 0) C_SL_CR,

           DECODE (sl.trx_type, 'MISC_CREDIT', NVL (sl.amount, 0), 0)

              C_SL_MISC_CR,

           DECODE (sl.trx_type, 'NSF', NVL (sl.amount, 0), 0) C_SL_NSF,

           DECODE (sl.trx_type, 'STOP', NVL (sl.amount, 0), 0) C_SL_STOP,

           DECODE (sl.trx_type, 'REJECTED', NVL (sl.amount, 0), 0)

              C_SL_REJECTED,

           DECODE (sl.trx_type, 'SWEEP_IN', NVL (sl.amount, 0), 0) C_SL_SWP_IN,

           DECODE (sl.trx_type, 'SWEEP_OUT', NVL (sl.amount, 0), 0)

              C_SL_SWP_OUT,

           sl.status C_STAT_LINE_STATUS,

           l2.meaning C_STAT_LINE_STATUS_DSP,

           DECODE (

              sl.status,

              'ERROR',

              DECODE (sl.trx_type,

                      'CREDIT', NVL (sl.amount, 0),

                      'MISC_CREDIT', NVL (sl.amount, 0),

                      'SWEEP_IN', NVL (sl.amount, 0),

                      0),

              0

           )

              C_SL_CR_ERRORS,

           DECODE (

              sl.status,

              'ERROR',

              DECODE (sl.trx_type,

                      'DEBIT', sl.amount,

                      'MISC_DEBIT', NVL (sl.amount, 0),

                      'SWEEP_OUT', NVL (sl.amount, 0),

                      0),

              0

           )

              C_SL_DR_ERRORS,

           sl.bank_trx_number C_SL_TRX_NUMBER,

           sl.charges_amount C_SL_BANK_CHARGES,

           sh.control_end_balance C_STAT_CONTROL_END,

           sh.statement_date C_STAT_STATEMENT_DATE,

           --NVL(aba.asset_code_combination_id, -1)                C_BANK_CASH_CCID,

           SUM(NVL (

                  DECODE (

                     sl.trx_type,

                     'MISC_CREDIT',

                     DECODE (

                        DECODE (sr.reference_type,

                                'DM REVERSAL', 'RECEIPT',

                                sr.reference_type),

                        'PAYMENT',

                        NVL (-sr.amount, 0),

                        NVL (sr.amount, 0)

                     ),

                     'RECEIPT',

                     DECODE (

                        DECODE (sr.reference_type,

                                'DM REVERSAL', 'RECEIPT',

                                sr.reference_type),

                        'PAYMENT',

                        NVL (-sr.amount, 0),

                        NVL (sr.amount, 0)

                     ),

                     'CREDIT',

                     DECODE (

                        DECODE (sr.reference_type,

                                'DM REVERSAL', 'RECEIPT',

                                sr.reference_type),

                        'PAYMENT',

                        NVL (-sr.amount, 0),

                        NVL (sr.amount, 0)

                     ),

                     'MISC_DEBIT',

                     DECODE (

                        DECODE (sr.reference_type,

                                'DM REVERSAL', 'RECEIPT',

                                sr.reference_type),

                        'RECEIPT',

                        NVL (-sr.amount, 0),

                        NVL (sr.amount, 0)

                     ),

                     'PAYMENT',

                     DECODE (

                        DECODE (sr.reference_type,

                                'DM REVERSAL', 'RECEIPT',

                                sr.reference_type),

                        'RECEIPT',

                        NVL (-sr.amount, 0),

                        NVL (sr.amount, 0)

                     ),

                     'DEBIT',

                     DECODE (

                        DECODE (sr.reference_type,

                                'DM REVERSAL', 'RECEIPT',

                                sr.reference_type),

                        'RECEIPT',

                        NVL (-sr.amount, 0),

                        NVL (sr.amount, 0)

                     ),

                     NVL (sr.amount, 0)

                  ),

                  0

               ))

              C_SL_REC_AMOUNT,

           (DECODE (sl.status, 'EXTERNAL', 0, NVL (sl.amount, 0))

            - SUM(NVL (

                     DECODE (

                        sl.trx_type,

                        'MISC_CREDIT',

                        DECODE (

                           DECODE (sr.reference_type,

                                   'DM REVERSAL', 'RECEIPT',

                                   sr.reference_type),

                           'PAYMENT',

                           NVL (-sr.amount, 0),

                           NVL (sr.amount, 0)

                        ),

                        'RECEIPT',

                        DECODE (

                           DECODE (sr.reference_type,

                                   'DM REVERSAL', 'RECEIPT',

                                   sr.reference_type),

                           'PAYMENT',

                           NVL (-sr.amount, 0),

                           NVL (sr.amount, 0)

                        ),

                        'CREDIT',

                        DECODE (

                           DECODE (sr.reference_type,

                                   'DM REVERSAL', 'RECEIPT',

                                   sr.reference_type),

                           'PAYMENT',

                           NVL (-sr.amount, 0),

                           NVL (sr.amount, 0)

                        ),

                        'MISC_DEBIT',

                        DECODE (

                           DECODE (sr.reference_type,

                                   'DM REVERSAL', 'RECEIPT',

                                   sr.reference_type),

                           'RECEIPT',

                           NVL (-sr.amount, 0),

                           NVL (sr.amount, 0)

                        ),

                        'PAYMENT',

                        DECODE (

                           DECODE (sr.reference_type,

                                   'DM REVERSAL', 'RECEIPT',

                                   sr.reference_type),

                           'RECEIPT',

                           NVL (-sr.amount, 0),

                           NVL (sr.amount, 0)

                        ),

                        'DEBIT',

                        DECODE (

                           DECODE (sr.reference_type,

                                   'DM REVERSAL', 'RECEIPT',

                                   sr.reference_type),

                           'RECEIPT',

                           NVL (-sr.amount, 0),

                           NVL (sr.amount, 0)

                        ),

                        NVL (sr.amount, 0)

                     ),

                     0

                  )))

              C_SL_UNREC_AMOUNT

    FROM   ce_bank_branches_v abb,

           ce_bank_accts_gt_v aba,

           ce_lookups l1,

           ce_lookups l2,

           --ce_statement_reconciliations                 sr,

           ce_statement_reconcils_all sr,

           ce_statement_lines sl,

           ce_statement_headers sh

   WHERE       l1.lookup_type = 'BANK_TRX_TYPE'

           AND l1.lookup_code = sl.trx_type

           AND l2.lookup_type = 'STATEMENT_LINE_STATUS'

           AND l2.lookup_code = sl.status

           AND abb.branch_party_id = aba.bank_branch_id

           AND aba.bank_account_id =

                 NVL ( :P_BANK_ACCOUNT_ID, aba.bank_account_id)

           AND aba.bank_account_id = sh.bank_account_id

           AND sr.status_flag(+) = 'M'

           AND sr.current_record_flag(+) = 'Y'

           AND sr.statement_line_id(+) = sl.statement_line_id

           AND sl.statement_header_id = sh.statement_header_id

           AND sl.status LIKE

                 DECODE ( :P_STMT_LINE_STATUS,

                         'RECONCILED', :P_STMT_LINE_STATUS,

                         'UNRECONCILED', '%RECONCILED',

                         sl.status)

           AND &C_STAT_DATE_SQL_LEX

           AND &C_STAT_NUMBER_SQL_LEX

GROUP BY   DECODE (

              sl.status,

              'EXTERNAL',

              'RECONCILED',

              DECODE (sr.reference_type, NULL, 'UNRECONCILED', 'RECONCILED')

           ),

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', TO_CHAR (sl.line_number),

                   sh.statement_number || '/' || sl.line_number),

           sh.statement_header_id,

           sl.statement_line_id,

           sh.doc_sequence_value,

           aba.bank_account_id,

           l1.meaning,

           sl.trx_type,

           aba.bank_account_name,

           aba.bank_account_num,

           aba.currency_code,

           abb.bank_branch_name,

           abb.bank_name,

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', TO_NUMBER (TO_CHAR (sh.statement_date, 'J')),

                   aba.bank_account_id),

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', sl.line_number,

                   TO_NUMBER (TO_CHAR (sh.statement_date, 'J'))),

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', sh.statement_number,

                   aba.bank_account_num),

           sl.line_number,

           DECODE ( :P_STATEMENT_OR_ACCOUNT,

                   'STATEMENT', TO_CHAR (sl.trx_date, :C_DATEFORMAT),

                   sh.statement_number),

           NVL (sl.amount, 0),

           DECODE (sl.trx_type,

                   'DEBIT', NVL (-sl.amount, 0),

                   'CREDIT', NVL (sl.amount, 0),

                   'MISC_DEBIT', NVL (-sl.amount, 0),

                   'MISC_CREDIT', NVL (sl.amount, 0),

                   'NSF', NVL (-sl.amount, 0),

                   'STOP', NVL (sl.amount, 0),

                   'REJECTED', NVL (-sl.amount, 0),

                   'SWEEP_IN', NVL (sl.amount, 0),

                   'SWEEP_OUT', NVL (-sl.amount, 0),

                   0),

           DECODE (sl.trx_type, 'DEBIT', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'MISC_DEBIT', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'CREDIT', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'MISC_CREDIT', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'NSF', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'STOP', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'REJECTED', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'SWEEP_IN', NVL (sl.amount, 0), 0),

           DECODE (sl.trx_type, 'SWEEP_OUT', NVL (sl.amount, 0), 0),

           sl.status,

           l2.meaning,

           DECODE (

              sl.status,

              'ERROR',

              DECODE (sl.trx_type,

                      'CREDIT', NVL (sl.amount, 0),

                      'MISC_CREDIT', NVL (sl.amount, 0),

                      'SWEEP_IN', NVL (sl.amount, 0),

                      0),

              0

           ),

           DECODE (

              sl.status,

              'ERROR',

              DECODE (sl.trx_type,

                      'DEBIT', sl.amount,

                      'MISC_DEBIT', NVL (sl.amount, 0),

                      'SWEEP_OUT', NVL (sl.amount, 0),

                      0),

              0

           ),

           sl.bank_trx_number,

           sl.charges_amount,

           sh.control_end_balance,

           sh.statement_date

--NVL(aba.asset_code_combination_id, -1)

 

تم تعديل بواسطة amal mahmoud

  • اقتباس

    • انضم إلى المناقشة

    يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.

    زائر
    أضف رد على هذا الموضوع...
    عودة لقائمة الموضوعات

    برجاء الإنتباه

    بإستخدامك للموقع فأنت تتعهد بالموافقة على هذه البنود: سياسة الخصوصية

    أوافق

    Account

    Navigation

    البحث

    إعداد إشعارات المتصفح الفورية
    Chrome (Android)
    1. Tap the lock icon next to the address bar.
    2. Tap Permissions → Notifications.
    3. Adjust your preference.
    Chrome (Desktop)
    1. Click the padlock icon in the address bar.
    2. Select Site settings.
    3. Find Notifications and adjust your preference.