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!

Which supplier self-assessment flag is correct? (and why?)

Discussion in 'Oracle Financials' started by Morgan, May 30, 2012.

  1. Morgan

    Morgan Guest

    I am trying to create a report that shows, for all suppliers who have "Set for Self-Assessment" box checked, whether or not they have a regime code.

    I'm running into some issues identifying the suppliers that are set for self-assessment. Specifically, the zx_party_tax_profile table has a self-assessment flag check, but there’s also a self-assessment flag column in the zx_registrations table, and I’m having trouble figuring out the difference between them. There doesn’t seem to be any consistent correlation between the two, aside from the fact that the zx_registrations table never has a flag (Y or N) unless the zx_party_tax_profile table has one. However, there are plenty of examples of the opposite; not to mention the fact that the flags don’t always match when they are both populated.

    Could someone shed some light on this for me? I've attached a sample query I've put together.

    Thanks!

    Code (SQL):
    SELECT DISTINCT hp.party_id
                  , hp.party_number
                  , hp.party_name
                  , aps.vendor_id
                  , aps.vendor_name
                  , aps.vendor_name_alt
                  , zptp.party_tax_profile_id
                  , zptp.self_assess_flag       AS zptp_self_assess_flag
                  , zr.tax_regime_code
                  , zr.self_assess_flag         AS zr_self_assess_flag
                  , zr.registration_status_code
                  , zrulb.tax
                  , zrulb.tax_rule_code
                  , zrulb.enabled_flag
                  , zrulb.tax_rule_id
              FROM  ZX.zx_rules_b           zrulb
                  , ZX.zx_registrations     zr
                  , ZX.zx_party_tax_profile zptp
                  , AR.hz_parties           hp
                  , AP.ap_suppliers         aps
              WHERE hp.party_id = aps.party_id(+)
                AND hp.party_id = zptp.party_id(+)
                AND zptp.party_tax_profile_id = zr.party_tax_profile_id(+)
                AND zr.tax_regime_code = zrulb.tax_regime_code(+)
                AND (
                    zptp.self_assess_flag IS NOT NULL
                    OR  zr.self_assess_flag IS NOT NULL
                    )
          ORDER BY  3
                  , 14