IGI CIS2007 Monthly Returns

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Construction Industry Scheme : Monthly Returns Report
Application: Public Sector Financials International
Source: IGI : CIS2007 Monthly Returns Report
Short Name: IGIPMTHR_XMLP
DB package: igi_cis_igipmthp_pkg
Run IGI CIS2007 Monthly Returns and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
(select look_1.meaning 
  from igi_lookups look_1
 where look_1.lookup_type = 'IGI_CIS2007_YES_NO' 
   and look_1.lookup_code = hdr.nil_return_flag) nil_return_flag,
(select look_1.meaning 
  from igi_lookups look_1
 where look_1.lookup_type = 'IGI_CIS2007_YES_NO' 
   and look_1.lookup_code = hdr.Employment_status_flag) Employment_status_flag,
   (select look_1.meaning 
  from igi_lookups look_1
 where look_1.lookup_type = 'IGI_CIS2007_YES_NO' 
   and look_1.lookup_code = hdr.subcont_verify_flag) subcont_verify_flag,
(select look_1.meaning 
  from igi_lookups look_1
 where look_1.lookup_type = 'IGI_CIS2007_YES_NO' 
   and look_1.lookup_code = hdr.information_correct_flag) information_correct_flag,
(select look_1.meaning 
  from igi_lookups look_1
 where look_1.lookup_type = 'IGI_CIS2007_YES_NO' 
   and look_1.lookup_code = hdr.inactivity_indicator) inactivity_indicator,
lines.vendor_name,
lines.trading_name,
lines.unique_tax_reference_num,
lines.national_insurance_number,
lines.company_registration_number,
lines.verification_number,
lines.vendor_id,
decode(nvl(lines.VERSION_NUM,1),1,
( Select
igi_cis_igipmthp_pkg.get_tax_status(to_char(IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(lines.vendor_id,null,null,null) ))
  from dual),lines.TAX_TREATMENT_STATUS)   tax_status,
&partselect
sum((nvl(pay.amount,0)  + nvl(pay.total_deductions,0))) total_payments,
sum(nvl(pay.material_cost,0)) material_cost,
sum(nvl(pay.labour_cost,0)) labour_cost,
sum(nvl(pay.total_deductions,0)) total_deductions,
sum(nvl(pay.total_deductions,0)) cis_tax 
/* COMMENTED BY VJ as CIS_TAX being used on RTF for deductions.
sum(nvl(pay.cis_tax,0)) cis_tax 
*/
FROM &tableclause
WHERE lines.header_id = hdr.header_id
AND pay.vendor_id(+) = lines.vendor_id
AND pay.header_id(+) = lines.header_id
&pwhereclause
group by 
hdr.nil_return_flag,
hdr.Employment_status_flag,
hdr.subcont_verify_flag,
hdr.information_correct_flag,
hdr.inactivity_indicator,
lines.vendor_name,
lines.trading_name,
lines.vendor_id,
lines.unique_tax_reference_num,
lines.national_insurance_number,
lines.company_registration_number,
lines.verification_number,
nvl(lines.VERSION_NUM,1),
lines.TAX_TREATMENT_STATUS
&partgroupby
&orderbyclause
Parameter Name SQL text Validation
Operating Unit
 
LOV
Period
 
LOV Oracle
Supplier From
 
LOV Oracle
Supplier To
 
LOV Oracle
Original/Duplicate
 
LOV Oracle
Sort By
 
LOV Oracle
Report Level
 
LOV Oracle
Amount Type
 
LOV Oracle
Blitz Report™