Thursday 14 July 2016

Query  to get the details of invoice with tax information's


select ctx.customer_trx_id,
       case
          when (    hrloc.address_line_1 is not null
                and (   hrloc.address_line_2 is not null
                     or hrloc.address_line_3 is not null
                     or hrloc.town_or_city is not null
                     or hrloc.country is not null
                     or hrloc.postal_code is not null
                    )
                and substr (hrloc.address_line_1, -1, 1) != ','
               )
             then hrloc.address_line_1 || ','
          else hrloc.address_line_1
       end address_line_1,
       case
          when (    hrloc.address_line_2 is not null
                and (   hrloc.address_line_3 is not null
                     or hrloc.town_or_city is not null
                     or hrloc.country is not null
                     or hrloc.postal_code is not null
                    )
                and substr (hrloc.address_line_2, -1, 1) != ','
               )
             then hrloc.address_line_2 || ','
          else hrloc.address_line_2
       end address_line_2,
       case
          when (    hrloc.address_line_3 is not null
                and (   hrloc.town_or_city is not null
                     or hrloc.country is not null
                     or hrloc.postal_code is not null
                    )
                and substr (hrloc.address_line_3, -1, 1) != ','
               )
             then hrloc.address_line_3 || ','
          else hrloc.address_line_3
       end address_line_3,
       case
          when (    hrloc.town_or_city is not null
                and (hrloc.country is not null
                     or hrloc.postal_code is not null
                    )
                and substr (hrloc.town_or_city, -1, 1) != ','
               )
             then hrloc.town_or_city || ','
          else hrloc.town_or_city
       end town_or_city,
       case
          when (    hrloc.country is not null
                and hrloc.postal_code is not null
                and substr (hrloc.country, -1, 1) != ','
               )
             then hrloc.country || ','
          else hrloc.country
       end country,
       hrloc.postal_code, rac_ship_party.party_name ship_to_customer_name,
       rac_ship.account_number ship_to_customer_num,
       replace (raa_ship_loc.address1, '–', '-') ship_to_address1,
       replace (raa_ship_loc.address2, '–', '-') ship_to_address2,
       replace (raa_ship_loc.address3, '–', '-') ship_to_address3,
       replace (raa_ship_loc.address4, '–', '-') ship_to_address4,
       raa_ship_loc.city ship_to_city,
       raa_ship_loc.postal_code ship_to_postal_code,
       raa_ship_loc.state ship_to_state, raa_ship_loc.county ship_to_country,
       decode
           (substrb (raco_ship_party.person_last_name, 1, 50),
            null, substrb (raco_ship_party.person_first_name, 1, 40),
               substrb (raco_ship_party.person_last_name, 1, 50)
            || ', '
            || substrb (raco_ship_party.person_first_name, 1, 40)
           ) ship_to_contact_name,
       rac_bill_party.party_name bill_to_customer_name,
       rac_bill.account_number bill_to_customer_num,
       replace (raa_bill_loc.address1, '–', '-') bill_to_address1,
       replace (raa_bill_loc.address2, '–', '-') bill_to_address2,
       replace (raa_bill_loc.address3, '–', '-') bill_to_address3,
       replace (raa_bill_loc.address4, '–', '-') bill_to_address4,
       raa_bill_loc.city bill_to_city,
       raa_bill_loc.postal_code bill_to_postal_code,
       raa_bill_loc.state bill_to_state, raa_bill_loc.county bill_to_country,
       decode
           (substrb (raco_bill_party.person_last_name, 1, 50),
            null, substrb (raco_bill_party.person_first_name, 1, 40),
               substrb (raco_bill_party.person_last_name, 1, 50)
            || ', '
            || substrb (raco_bill_party.person_first_name, 1, 40)
           ) bill_to_contact_name,
       ctx.trx_number invoice_number, ctx.trx_date invoice_date,
       ctx.interface_header_attribute10 po_no,
       ctx.interface_header_attribute8 call_order_date,
       ctx.interface_header_attribute13 ho_oe_no,
       ctx.interface_header_attribute14 branch_oe_no, rat.name payment_terms,
       arpt_sql_func_util.get_first_real_due_date
                                               (ctx.customer_trx_id,
                                                ctx.term_id,
                                                ctx.trx_date
                                               ) due_date,
       jicl.line_number, upper (jicl.description) description,
       jicl.quantity invoice_qty, jicl.unit_selling_price unit_price,
       jicl.line_amount invoice_amt, ja_hou.vat_reg_no, ja_hou.cst_reg_no,
       hraou.attribute5 cin_no, ctx.comments,
       org_df.organization_name org_name, org_df.organization_id org_id,
       lkp.meaning accounting_rule_type, rctrl.rule_start_date,
       case
          when upper (lkp.meaning) like '%VARIABLE%'
             then add_months
                            (rctrl.rule_start_date,
                             rctrl.accounting_rule_duration
                            )
          else null
       end as rule_last_date,
       ctx.invoice_currency_code inv_cur_code
  from ra_customer_trx_all ctx,
       ra_customer_trx_lines_all rctrl,
       ja_in_ra_customer_trx jitx,
       ja_in_hr_organization_units ja_hou,
       org_organization_definitions org_df,
       hr_locations hrloc,
       hr_all_organization_units hraou,
       hz_cust_accounts rac_ship,
       hz_parties rac_ship_party,
       hz_cust_site_uses_all su_ship,
       hz_cust_acct_sites_all raa_ship,
       hz_party_sites raa_ship_ps,
       hz_locations raa_ship_loc,
       hz_parties raco_ship_party,
       hz_relationships raco_ship_rel,
       hz_cust_account_roles raco_ship,
       hz_cust_accounts rac_bill,
       hz_parties rac_bill_party,
       hz_cust_site_uses_all su_bill,
       hz_locations raa_bill_loc,
       hz_cust_acct_sites_all raa_bill,
       hz_party_sites raa_bill_ps,
       hz_parties raco_bill_party,
       hz_relationships raco_bill_rel,
       hz_cust_account_roles raco_bill,
       ra_terms rat,
       ja_in_ra_customer_trx_lines jicl,
       ja_in_customer_addresses jaddr,
       ra_customers rcus,
       ra_rules rul,
       fnd_lookup_values lkp
 where ctx.customer_trx_id = :p_customer_trx_id
   and rctrl.customer_trx_id = ctx.customer_trx_id
   and rctrl.line_type = 'LINE'
   and jicl.customer_trx_line_id = rctrl.customer_trx_line_id
   and ctx.customer_trx_id = jicl.customer_trx_id
   and ctx.trx_number = jitx.trx_number
   and jitx.location_id = hrloc.location_id
   and hraou.organization_id = org_df.legal_entity
   and ctx.ship_to_customer_id = rac_ship.cust_account_id(+)
   and rac_ship.party_id = rac_ship_party.party_id(+)
   and ctx.ship_to_site_use_id = su_ship.site_use_id(+)
   and su_ship.cust_acct_site_id = raa_ship.cust_acct_site_id(+)
   and raa_ship.party_site_id = raa_ship_ps.party_site_id(+)
   and raa_ship_ps.location_id = raa_ship_loc.location_id(+)
   and ctx.ship_to_contact_id = raco_ship.cust_account_role_id(+)
   and raco_ship.party_id = raco_ship_rel.party_id(+)
   and raco_ship_rel.subject_table_name(+) = 'HZ_PARTIES'
   and raco_ship_rel.object_table_name(+) = 'HZ_PARTIES'
   and raco_ship_rel.directional_flag(+) = 'F'
   and raco_ship.role_type(+) = 'CONTACT'
   and raco_ship_rel.subject_id = raco_ship_party.party_id(+)
   and ctx.bill_to_customer_id = rac_bill.cust_account_id
   and rac_bill.party_id = rac_bill_party.party_id
   and ctx.bill_to_site_use_id = su_bill.site_use_id
   and su_bill.cust_acct_site_id = raa_bill.cust_acct_site_id
   and raa_bill.party_site_id = raa_bill_ps.party_site_id
   and raa_bill_loc.location_id = raa_bill_ps.location_id
   and ctx.bill_to_contact_id = raco_bill.cust_account_role_id(+)
   and raco_bill.party_id = raco_bill_rel.party_id(+)
   and raco_bill_rel.subject_table_name(+) = 'HZ_PARTIES'
   and raco_bill_rel.object_table_name(+) = 'HZ_PARTIES'
   and raco_bill_rel.directional_flag(+) = 'F'
   and raco_bill.role_type(+) = 'CONTACT'
   and raco_bill_rel.subject_id = raco_bill_party.party_id(+)
   and ctx.term_id = rat.term_id(+)
   and rcus.customer_id = jaddr.customer_id(+)
   and ctx.bill_to_customer_id = rcus.customer_id
   and rctrl.accounting_rule_id = rul.rule_id(+)
   and lkp.lookup_type(+) = 'RULE_TYPE'
   and lkp.lookup_code(+) = rul.type
   and jitx.organization_id = ja_hou.organization_id
   and jitx.location_id = ja_hou.location_id
   and ja_hou.organization_id = org_df.organization_id
 
 
/* Invoice Tax Information */

select   ctx.customer_trx_id, jicl.line_number, jitxl.tax_id,
         upper (jitc.tax_name) tax_name, sum (jitxl.tax_amount) tax_amount
    from ra_customer_trx_all ctx,
         ja_in_ra_customer_trx jitx,
         ja_in_ra_cust_trx_tax_lines jitxl,
         ja_in_tax_codes jitc,
         ja_in_ra_customer_trx_lines jicl
   where ctx.customer_trx_id = :p_customer_trx_id
     and ctx.trx_number = jitx.trx_number
     and ctx.customer_trx_id = jicl.customer_trx_id
     and jicl.customer_trx_line_id = jitxl.link_to_cust_trx_line_id(+)
     and jitc.tax_id = jitxl.tax_id
     and ctx.customer_trx_id = jitx.customer_trx_id
     and jitc.end_date is null
group by ctx.customer_trx_id,
         jicl.line_number,
         jitxl.tax_id,
         jitc.tax_name,
         jitc.tax_type
order by upper (jitc.tax_type);


--By    Eswaramoorthi M

No comments:

Post a Comment