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
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