1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Urgent Help [po_accrual_reconcile_temp]

Discussion in 'Oracle Apps Technical' started by RIAZ, May 20, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    hi,
    I am running Oracle Purchasing Report: "Accrual Reconcile Report" and it is giving output but not in proper format so i need to to run the report's query in TOAD and get the results in excel.

    BUT when I tried to fetch data from the query used in above mentioned report, it is not giving output :(

    After further inquiry i got that "po_accrual_reconcile_temp" table have no data.


    Help me in this regard... its really very urgent. would be very thankful to you.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Riaz, could you post the report's query. we can have a look as to why it is not giving an output.
     
  3. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Code (SQL):

    SELECT    gcc.segment1
           || '-'
           || gcc.segment2
           || '-'
           || gcc.segment3
           || '-'
           || gcc.segment4
           || '-'
           || gcc.segment5 AS gl_account,
              msi.segment1
           || '-'
           || msi.segment2
           || '-'
           || msi.segment3
           || '-'
           || msi.segment4 AS item_code,
           NVL (msi.description, pol.item_description) AS description,
           par.po_num AS po_num, par.po_line_num AS po_line_num,
           par.vendor_name AS vendor_name,
           par.accrual_code AS accrual_transaction,
           plu2.displayed_field AS SOURCE, mp.organization_code AS org,
           par.transaction_date AS transaction_date,
           ROUND (par.transaction_quantity, :p_qty_precision) AS transaction_qty,
           par.transaction_unit_price AS unit_price,
           par.transaction_amount AS transaction_amount,
           par.write_off_flag AS write_off_flag,
           par.currency_code AS entered_currency_code
      FROM po_accrual_reconcile_temp par,
           mtl_system_items msi,
           gl_code_combinations gcc,
           mtl_parameters mp,
           po_lookup_codes plu2,
           po_lines pol
     WHERE par.inventory_item_id = msi.inventory_item_id(+)
       AND par.po_line_id = pol.po_line_id(+)
       AND par.item_master_organization_id = msi.organization_id(+)
       AND gcc.code_combination_id = par.accrual_account_id
       AND mp.organization_id = par.transaction_organization_id
       AND plu2.lookup_type = 'ACCRUAL TRX SOURCE'
       AND plu2.lookup_code = par.transaction_source_code
       AND par.transaction_date <= :p_date
       AND NOT (:p_written_off = 'N' AND par.write_off_flag = 'Y')
       AND 'Y' =
              DECODE
                 (:p_zero_balance,
                  'Y', 'Y',
                  'N', DECODE
                      (SIGN (ABS (par.net_po_line_amount) - :p_amt_tolerance),
                       1, 'Y',
                       DECODE (SIGN (  ABS (ROUND (NVL (par.net_po_line_quantity,
                                                        0
                                                       ),
                                                   :p_qty_pres
                                                  )
                                           )
                                     - :p_qty_tolerance
                                    ),
                               1, 'Y',
                               'N'
                              )
                      )
                 )
     
     
  4. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hmm well there is nothing wrong with the query. I assume you are on 11.5.10 right? well there is an existing BUG with the Accrual Reconciliation Report after application of Patch 5109317. Is that Patch applied?

    If yes you would need to apply another patch. Refer to Metalink Note 422077.1 for more info on this issue.
     
  5. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Thanks for your prompt reply but this doesn't help.

    I got the solution and sharing with you.

    In-fact the table "po_accrual_reconcile_temp" is a view and this view is using the table
    "po_accrual_reconcile_temp_all" and this is temporary table.

    To populate this time we need to run "ACCRUAL RECONCILE REBUILD" report.

    After that i can use "po_accrual_reconcile_temp_all" :)
     
  6. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hmmm i am glad it worked out... didn't notice that po_accrual_reconcile_temp is a view. So apparently po_accrual_reconcile_temp_all is populated when ACCRUAL RECONCILE REBUILD is run. Thanks for sharing the solution. It helps folks who may later be reading.