[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select vendor_id
from po_vendors pov
where pov.cis_enabled_flag = 'Y'
-- And vendor_type_lookup_code in ('PARTNERSHIP','SOLETRADER','COMPANY','TRUST') bug 5620621
AND pov.cis_parent_vendor_id is null;*/
(SELECT audit_lines.vendor_id
FROM igi_cis_mth_ret_hdr_h audit_hdr,
igi_cis_mth_ret_lines_h audit_lines
WHERE audit_hdr.header_id = audit_lines.header_id
AND audit_hdr.request_status_code = 'C'
AND audit_hdr.period_name = p_in_period
UNION all
SELECT audit_lines_t.vendor_id
FROM igi_cis_mth_ret_hdr_t audit_hdr_t,
igi_cis_mth_ret_lines_t audit_lines_t
WHERE audit_hdr_t.header_id = audit_lines_t.header_id
AND audit_hdr_t.request_status_code = 'R'
AND audit_hdr_t.period_name = p_in_period)
AND upper(pov.vendor_name)
between upper(vendorFrom) and upper(vendorTo);*/
SELECT /*+ leading(ACA) */ pov1.vendor_id child_id,
decode(pov1.cis_parent_vendor_id,null, pov1.vendor_id,
decode(nvl(pov.cis_enabled_flag,'N'), 'N', pov1.vendor_id,pov1.cis_parent_vendor_id)) parent_id,
aia.invoice_id invoice_id,
aipa.invoice_payment_id payment_id,
aipa.amount invoice_payment_amount,
aipa.discount_taken discount_amount
FROM ap_invoices aia,
ap_invoice_payments aipa,
ap_checks aca,
AP_SUPPLIERS pov,
AP_SUPPLIERS pov1,
-- Bug 5647413 Start
ap_supplier_sites pvs
-- Bug 5647413 End
WHERE aia.invoice_id = aipa.invoice_id
AND aca.check_id = aipa.check_id
AND aca.void_date IS NULL
And aca.check_number is not null
-- Bug 5647413 Start
and pov.vendor_id(+) = pov1.cis_parent_vendor_id
and pvs.vendor_id = pov1.vendor_id
and (pov.cis_enabled_flag = 'Y' or pov1.cis_enabled_flag = 'Y')
and pvs.allow_awt_flag = 'Y'
and aia.vendor_site_id = pvs.vendor_site_id
-- Bug 5647413 End
--AND aia.invoice_type_lookup_code = 'STANDARD'
AND aia.vendor_id = pov1.vendor_id
AND trunc(aca.check_date) BETWEEN l_start_date AND l_end_date /*Added for bug 13028312*/
AND EXISTS( select 1 from ap_invoice_distributions aida
where aida.invoice_id = aia.invoice_id
and (aida.awt_group_id is not null OR aida.pay_awt_group_id is not null));
delete from IGI_CIS_MTH_RET_PAY_GT;
select min(vendor_name) , max(vendor_name)
into vendorFrom, vendorTo
From po_vendors;
log(C_STATE_LEVEL, l_procedure_name, 'Insertint into IGI_CIS_MTH_RET_PAY_GT');
insert into IGI_CIS_MTH_RET_PAY_GT(
VENDOR_ID,
CHILD_VENDOR_ID,
INVOICE_ID,
INVOICE_PAYMENT_ID,
AMOUNT,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX)
values
(all_payment_list(i).parent_id,
all_payment_list(i).child_id,
all_payment_list(i).invoice_id,
all_payment_list(i).payment_id,
--c2_rec_info.invoice_payment_amount,
l_temp_pay_amount, -- bug 5609552
l_lab_cost,
l_mat_cost,
l_awt_amnt,
all_payment_list(i).discount_amount,
l_cis_tax);
/*insert into IGI_CIS_MTH_RET_PAY_GT
values
(c1_rec_info.vendor_id,
c1_rec_info.vendor_id,
null,
null,
null);*/
Select invoice_amount invoice_amount
From ap_invoices
Where invoice_id = p_inv_id;
Select nvl(sum(amount),0) labour_cost
From ap_invoice_distributions
where line_type_lookup_code in ('ITEM' , 'ACCRUAL' , 'IPV' , 'ERV' , 'RETAINAGE', 'PREPAY')
-- and awt_group_id is not null
and IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(NULL,NULL,awt_group_id,pay_awt_group_id) is not null /* Bug 7218825 */
and invoice_id = p_inv_id;
Select nvl(sum(amount),0) material_cost
From ap_invoice_distributions
where line_type_lookup_code in ('ITEM' , 'ACCRUAL' , 'IPV' , 'ERV' , 'RETAINAGE', 'PREPAY')
-- and awt_group_id is null
and IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(NULL,NULL,awt_group_id,pay_awt_group_id) is null /* Bug 7218825 */
and invoice_id = p_inv_id;
Select nvl(sum(amount),0) vat_cost
From ap_invoice_distributions
-- where line_type_lookup_code = 'TAX'
where line_type_lookup_code IN ('TAX','REC_TAX','NONREC_TAX','TRV','TERV','TIPV') -- Bug 8464796
--and awt_group_id is null
and IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(NULL,NULL,awt_group_id,pay_awt_group_id) is null /* Bug 7218825 */
and invoice_id = p_inv_id;
Select nvl(sum(amount),0) ret_prepay_amt
From ap_invoice_distributions d,
ap_invoices_all i
where ((i.invoice_type_lookup_code NOT IN ('RETAINAGE RELEASE') AND
d.line_type_lookup_code IN ('RETAINAGE', 'PREPAY')) OR
(i.invoice_type_lookup_code IN ('RETAINAGE RELEASE') AND
d.line_type_lookup_code IN ('PREPAY')))
and i.invoice_id = p_inv_id
and i.invoice_id = d.invoice_id;
Select count(*) line_count
From ap_invoice_distributions
where line_type_lookup_code = 'AWT'
and invoice_id = p_inv_id
and awt_invoice_payment_id = p_inv_pay_id;
Select count(*) line_count
From ap_invoice_distributions
where line_type_lookup_code = 'AWT'
and invoice_id = p_inv_id
and awt_invoice_payment_id is null;
Select nvl(sum(amount),0) awt_amount
From ap_invoice_distributions
where line_type_lookup_code = 'AWT'
and invoice_id = p_inv_id
and awt_invoice_payment_id = p_inv_pay_id;
Select nvl(sum(amount),0) awt_amount
From ap_invoice_distributions
where line_type_lookup_code = 'AWT'
and invoice_id = p_inv_id
and awt_invoice_payment_id is null;
Select nvl(sum(amount),0) awt_amount
From ap_invoice_distributions
where line_type_lookup_code = 'AWT'
and invoice_id = p_inv_id
and awt_invoice_payment_id = p_inv_pay_id
and awt_tax_rate_id in (
select tax_rate_id
from ap_awt_tax_rates_all
where tax_name in (
select name from ap_tax_codes_all
where awt_vendor_id = fnd_profile.value('IGI_CIS2007_TAX_AUTHORITY')));
Select nvl(sum(amount),0) awt_amount
From ap_invoice_distributions
where line_type_lookup_code = 'AWT'
and invoice_id = p_inv_id
and awt_invoice_payment_id is null
and awt_tax_rate_id in (
select tax_rate_id
from ap_awt_tax_rates_all
where tax_name in (
select name from ap_tax_codes_all
where awt_vendor_id = fnd_profile.value('IGI_CIS2007_TAX_AUTHORITY')));
select vendor_id, child_vendor_id, invoice_id,
invoice_payment_id, amount, labour_cost, material_cost,
total_deductions, discount_amount from igi_cis_mth_ret_pay_gt;*/
Select UNIQUE_TAX_REFERENCE_NUM,ACCOUNTS_OFFICE_REFERENCE,
TAX_OFFICE_NUMBER,PAYE_REFERENCE,fnd_profile.value('ORG_ID') ORG_ID,
CIS_SENDER_ID
From AP_REPORTING_ENTITIES
Where UNIQUE_TAX_REFERENCE_NUM is not null;
select nvl(sum(decode(X.nil_return_flag,'Y',1,0)),0) nil_ret_count,
nvl(sum(decode(X.nil_return_flag,'N',1,0)),0) non_nil_ret_count
from
(Select hdr_h.Nil_return_flag nil_return_flag
from IGI_CIS_MTH_RET_HDR_H hdr_h
where hdr_h.period_name = p_period_name
and hdr_h.request_status_code = 'C') X;
select --:org_id ORG_ID,:header_id HEADER_ID,
pov.vendor_id VENDOR_ID,
-- Commented for bug 5671997 and add decode to get partnership name in case BT= partnership
--pov.vendor_name VENDOR_NAME,
decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_name,pov.vendor_name) VENDOR_NAME,
pov.vendor_type_lookup_code VENDOR_TYPE_LOOKUP_CODE,
pov.first_name FIRST_NAME,
pov.second_name SECOND_NAME,
pov.last_name LAST_NAME,
pov.salutation SALUTATION,
pov.trading_name TRADING_NAME,
pov.match_status_flag UNMATCHED_TAX_FLAG,
--pov.unique_tax_reference_num UNIQUE_TAX_REFERENCE_NUM,
decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_utr,
pov.unique_tax_reference_num)
UNIQUE_TAX_REFERENCE_NUM,
pov.company_registration_number COMPANY_REGISTRATION_NUMBER,
pov.national_insurance_number NATIONAL_INSURANCE_NUMBER,
pov.verification_number VERIFICATION_NUMBER,
sum(nvl(pay.amount, 0)) TOTAL_PAYMENTS,
sum(nvl(pay.TOTAL_DEDUCTIONS, 0)) TOTAL_DEDUCTIONS,
sum(nvl(pay.MATERIAL_COST, 0)) MATERIAL_COST,
sum(nvl(pay.LABOUR_COST, 0)) LABOUR_COST,
sum(nvl(pay.DISCOUNT_AMOUNT, 0)) DISCOUNT_AMOUNT,
sum(nvl(pay.CIS_TAX,0)) CIS_TAX
from AP_SUPPLIERS pov, IGI_CIS_MTH_RET_PAY_GT pay
where pov.vendor_id = pay.vendor_id
group by pov.vendor_id,
-- Commented for bug 5671997 and add decode to get partnership name in case BT= partnership
-- pov.vendor_name,
decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_name,pov.vendor_name),
pov.vendor_type_lookup_code,
pov.first_name,
pov.second_name,
pov.last_name,
pov.salutation,
pov.trading_name,
pov.match_status_flag,
--pov.unique_tax_reference_num,
decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_utr,
pov.unique_tax_reference_num),
pov.company_registration_number,
pov.national_insurance_number,
pov.verification_number
order by upper(VENDOR_NAME) asc;
Select vendors.vendor_id VENDOR_ID,
-- Commented for bug 5671997 and add decode to get partnership name in case BT= partnership
-- vendors.vendor_name VENDOR_NAME,
decode(vendors.vendor_type_lookup_code,'PARTNERSHIP',vendors.partnership_name,vendors.vendor_name) VENDOR_NAME,
vendors.vendor_type_lookup_code VENDOR_TYPE_LOOKUP_CODE,
vendors.first_name FIRST_NAME,
vendors.second_name SECOND_NAME,
vendors.last_name LAST_NAME,
vendors.salutation SALUTATION,
vendors.trading_name TRADING_NAME,
vendors.match_status_flag UNMATCHED_TAX_FLAG,
--vendors.unique_tax_reference_num UNIQUE_TAX_REFERENCE_NUM,
decode(vendors.vendor_type_lookup_code,'PARTNERSHIP',vendors.partnership_utr,
vendors.unique_tax_reference_num)
UNIQUE_TAX_REFERENCE_NUM,
vendors.company_registration_number COMPANY_REGISTRATION_NUMBER,
vendors.national_insurance_number NATIONAL_INSURANCE_NUMBER,
vendors.verification_number VERIFICATION_NUMBER,
0 TOTAL_PAYMENTS,
0 TOTAL_DEDUCTIONS,
0 MATERIAL_COST,
0 LABOUR_COST,
0 DISCOUNT_AMOUNT,
0 CIS_TAX
from AP_SUPPLIERS vendors
where vendors.cis_enabled_flag = 'Y'
--And vendor_type_lookup_code in ('PARTNERSHIP','SOLETRADER','COMPANY','TRUST') bug 5620621
and vendors.cis_parent_vendor_id is null
order by upper(VENDOR_NAME) asc;
select count(1) period_allowed
From AP_OTHER_PERIODS aop,
(SELECT decode(SIGN(to_number(to_char(sysdate, 'DD')) -6), -1, add_months(to_date(('05-' || to_char(sysdate, 'MM-YYYY')), 'DD-MM-YYYY'), 3),
add_months(to_date(('05-' || to_char(sysdate, 'MM-YYYY')),'DD-MM-YYYY'), 4)) end_date_criteria from dual) temp
where aop.period_type =
fnd_profile.value('IGI_CIS2007_CALENDAR')
and aop.period_year <= 2099
and aop.end_date between to_date('05-05-2007', 'DD-MM-YYYY')
AND
temp.end_date_criteria
and period_name = p_period_name;
select start_date,end_date
into l_period_start_date,l_period_end_date
from ap_other_periods
where period_type = fnd_profile.value('IGI_CIS2007_CALENDAR')
and period_name = p_period_name;
SELECT IGI_CIS_MTH_RET_HDR_T_S.nextval
INTO l_header_id
FROM dual;
log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_hdr_t');
insert into igi_cis_mth_ret_hdr_t(
HEADER_ID,
ORG_ID,
CIS_SENDER_ID,
TAX_OFFICE_NUMBER,
PAYE_REFERENCE,
REQUEST_ID,
REQUEST_STATUS_CODE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
UNIQUE_TAX_REFERENCE_NUM,
ACCOUNTS_OFFICE_REFERENCE,
PERIOD_NAME,
PERIOD_ENDING_DATE,
NIL_RETURN_FLAG,
EMPLOYMENT_STATUS_FLAG,
SUBCONT_VERIFY_FLAG,
INFORMATION_CORRECT_FLAG,
INACTIVITY_INDICATOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
values(
l_header_id,
l_org_id,
C_rep_entity_rec.cis_sender_id,
C_rep_entity_rec.tax_office_number,
C_rep_entity_rec.PAYE_REFERENCE,
FND_GLOBAL.CONC_REQUEST_ID(), -- REQUEST_ID
'P', -- REQUEST_STATUS_CODE
FND_GLOBAL.PROG_APPL_ID(), -- PROGRAM_APPLICATION_ID
FND_GLOBAL.CONC_PROGRAM_ID(), -- PROGRAM_ID
FND_GLOBAL.CONC_LOGIN_ID(), -- PROGRAM_LOGIN_ID
C_rep_entity_rec.UNIQUE_TAX_REFERENCE_NUM,
C_rep_entity_rec.ACCOUNTS_OFFICE_REFERENCE,
p_period_name,
l_period_end_date,
p_nil_return_flag,
p_emp_status_flag,
p_subcont_verify_flag,
p_info_crct_flag,
nvl(p_inact_indicat_flag,'N'),
sysdate,
FND_GLOBAL.USER_ID(),
FND_GLOBAL.LOGIN_ID(),
sysdate,
FND_GLOBAL.USER_ID()
);
log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_lines_t');
insert into igi_cis_mth_ret_lines_t(
HEADER_ID,
ORG_ID,
VENDOR_ID,
VENDOR_NAME,
VENDOR_TYPE_LOOKUP_CODE,
FIRST_NAME,
SECOND_NAME,
LAST_NAME,
SALUTATION,
TRADING_NAME,
UNMATCHED_TAX_FLAG,
UNIQUE_TAX_REFERENCE_NUM,
COMPANY_REGISTRATION_NUMBER,
NATIONAL_INSURANCE_NUMBER,
VERIFICATION_NUMBER,
TOTAL_PAYMENTS,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
values(
l_header_id,
l_org_id,
C_nil_ret_rec.VENDOR_ID,
C_nil_ret_rec.VENDOR_NAME,
C_nil_ret_rec.VENDOR_TYPE_LOOKUP_CODE,
C_nil_ret_rec.FIRST_NAME,
C_nil_ret_rec.SECOND_NAME,
C_nil_ret_rec.LAST_NAME,
C_nil_ret_rec.SALUTATION,
C_nil_ret_rec.TRADING_NAME,
C_nil_ret_rec.UNMATCHED_TAX_FLAG,
C_nil_ret_rec.UNIQUE_TAX_REFERENCE_NUM,
C_nil_ret_rec.COMPANY_REGISTRATION_NUMBER,
C_nil_ret_rec.NATIONAL_INSURANCE_NUMBER,
C_nil_ret_rec.VERIFICATION_NUMBER,
C_nil_ret_rec.TOTAL_PAYMENTS,
C_nil_ret_rec.LABOUR_COST,
C_nil_ret_rec.MATERIAL_COST,
C_nil_ret_rec.TOTAL_DEDUCTIONS,
C_nil_ret_rec.DISCOUNT_AMOUNT,
C_nil_ret_rec.CIS_TAX,
sysdate,
FND_GLOBAL.USER_ID(),
FND_GLOBAL.LOGIN_ID(),
sysdate,
FND_GLOBAL.USER_ID()
);
log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_lines_t');
insert into igi_cis_mth_ret_lines_t(
HEADER_ID,
ORG_ID,
VENDOR_ID,
VENDOR_NAME,
VENDOR_TYPE_LOOKUP_CODE,
FIRST_NAME,
SECOND_NAME,
LAST_NAME,
SALUTATION,
TRADING_NAME,
UNMATCHED_TAX_FLAG,
UNIQUE_TAX_REFERENCE_NUM,
COMPANY_REGISTRATION_NUMBER,
NATIONAL_INSURANCE_NUMBER,
VERIFICATION_NUMBER,
TOTAL_PAYMENTS,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
values(
l_header_id,
l_org_id,
C_n_nil_ret_rec.VENDOR_ID,
C_n_nil_ret_rec.VENDOR_NAME,
C_n_nil_ret_rec.VENDOR_TYPE_LOOKUP_CODE,
C_n_nil_ret_rec.FIRST_NAME,
C_n_nil_ret_rec.SECOND_NAME,
C_n_nil_ret_rec.LAST_NAME,
C_n_nil_ret_rec.SALUTATION,
C_n_nil_ret_rec.TRADING_NAME,
C_n_nil_ret_rec.UNMATCHED_TAX_FLAG,
C_n_nil_ret_rec.UNIQUE_TAX_REFERENCE_NUM,
C_n_nil_ret_rec.COMPANY_REGISTRATION_NUMBER,
C_n_nil_ret_rec.NATIONAL_INSURANCE_NUMBER,
C_n_nil_ret_rec.VERIFICATION_NUMBER,
C_n_nil_ret_rec.TOTAL_PAYMENTS,
C_n_nil_ret_rec.LABOUR_COST,
C_n_nil_ret_rec.MATERIAL_COST,
C_n_nil_ret_rec.TOTAL_DEDUCTIONS,
C_n_nil_ret_rec.DISCOUNT_AMOUNT,
C_n_nil_ret_rec.CIS_TAX,
sysdate,
FND_GLOBAL.USER_ID(),
FND_GLOBAL.LOGIN_ID(),
sysdate,
FND_GLOBAL.USER_ID()
);
log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_pay_t');
insert into igi_cis_mth_ret_pay_t
(
HEADER_ID,
ORG_ID,
VENDOR_ID,
CHILD_VENDOR_ID,
INVOICE_ID,
INVOICE_PAYMENT_ID,
AMOUNT,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX,--11699868
LAST_UPDATE_DATE,--date
LAST_UPDATED_BY, -- num
LAST_UPDATE_LOGIN,-- num
CREATION_DATE,--date
CREATED_BY --num
)
Select
l_header_id,
l_org_id,
VENDOR_ID,
CHILD_VENDOR_ID,
INVOICE_ID,
INVOICE_PAYMENT_ID,
AMOUNT,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX,--11699868
sysdate,
FND_GLOBAL.USER_ID(),
FND_GLOBAL.LOGIN_ID(),
sysdate,
FND_GLOBAL.USER_ID()
from igi_cis_mth_ret_pay_gt;
update IGI_CIS_MTH_RET_HDR_T
set --REQUEST_STATUS_CODE = 'C',
PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID(),
PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID(),
PROGRAM_LOGIN_ID = FND_GLOBAL.CONC_LOGIN_ID()
where HEADER_ID = l_prelim_hdr_id;
argument7 => 'Y', --delete temp
argument8 => 'S',
argument9 => p_mth_ret_amt_type, --amount type
argument10 => chr(0));
argument7 => 'Y', --delete temp
argument8 => 'S',
argument9 => p_mth_ret_amt_type, --amount type
argument10 => chr(0));
insert into igi_cis_mth_ret_hdr_h
(HEADER_ID,
ORG_ID,
CIS_SENDER_ID,
TAX_OFFICE_NUMBER,
PAYE_REFERENCE,
REQUEST_ID,
REQUEST_STATUS_CODE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
UNIQUE_TAX_REFERENCE_NUM,
ACCOUNTS_OFFICE_REFERENCE,
PERIOD_NAME,
PERIOD_ENDING_DATE,
NIL_RETURN_FLAG,
EMPLOYMENT_STATUS_FLAG,
SUBCONT_VERIFY_FLAG,
INFORMATION_CORRECT_FLAG,
INACTIVITY_INDICATOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
select HEADER_ID,
ORG_ID,
CIS_SENDER_ID,
TAX_OFFICE_NUMBER,
PAYE_REFERENCE,
REQUEST_ID,
p_request_status_code,--REQUEST_STATUS_CODE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
UNIQUE_TAX_REFERENCE_NUM,
ACCOUNTS_OFFICE_REFERENCE,
PERIOD_NAME,
PERIOD_ENDING_DATE,
NIL_RETURN_FLAG,
EMPLOYMENT_STATUS_FLAG,
SUBCONT_VERIFY_FLAG,
INFORMATION_CORRECT_FLAG,
INACTIVITY_INDICATOR,
sysdate, --LAST_UPDATE_DATE
FND_GLOBAL.USER_ID(),--LAST_UPDATED_BY
FND_GLOBAL.LOGIN_ID(),--LAST_UPDATE_LOGIN
sysdate, --CREATION_DATE
FND_GLOBAL.USER_ID() --CREATED_BY
from igi_cis_mth_ret_hdr_t
where HEADER_ID = p_header_id ;
insert into igi_cis_mth_ret_lines_h
(HEADER_ID,
ORG_ID,
VENDOR_ID,
VENDOR_NAME,
VENDOR_TYPE_LOOKUP_CODE,
FIRST_NAME,
SECOND_NAME,
LAST_NAME,
SALUTATION,
TRADING_NAME,
UNMATCHED_TAX_FLAG,
UNIQUE_TAX_REFERENCE_NUM,
COMPANY_REGISTRATION_NUMBER,
NATIONAL_INSURANCE_NUMBER,
VERIFICATION_NUMBER,
TOTAL_PAYMENTS,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
select HEADER_ID,
ORG_ID,
VENDOR_ID,
VENDOR_NAME,
VENDOR_TYPE_LOOKUP_CODE,
FIRST_NAME,
SECOND_NAME,
LAST_NAME,
SALUTATION,
TRADING_NAME,
UNMATCHED_TAX_FLAG,
UNIQUE_TAX_REFERENCE_NUM,
COMPANY_REGISTRATION_NUMBER,
NATIONAL_INSURANCE_NUMBER,
VERIFICATION_NUMBER,
TOTAL_PAYMENTS,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX,
sysdate, --LAST_UPDATE_DATE
FND_GLOBAL.USER_ID(),--LAST_UPDATED_BY
FND_GLOBAL.LOGIN_ID(),--LAST_UPDATE_LOGIN
sysdate, --CREATION_DATE
FND_GLOBAL.USER_ID() --CREATED_BY
from igi_cis_mth_ret_lines_t
where HEADER_ID = p_header_id ;
insert into igi_cis_mth_ret_pay_h
(HEADER_ID,
ORG_ID,
VENDOR_ID,
CHILD_VENDOR_ID,
INVOICE_ID,
INVOICE_PAYMENT_ID,
AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX)
Select HEADER_ID,
ORG_ID,
VENDOR_ID,
CHILD_VENDOR_ID,
INVOICE_ID,
INVOICE_PAYMENT_ID,
AMOUNT,
sysdate, --LAST_UPDATE_DATE
FND_GLOBAL.USER_ID(),--LAST_UPDATED_BY
FND_GLOBAL.LOGIN_ID(),--LAST_UPDATE_LOGIN
sysdate, --CREATION_DATE
FND_GLOBAL.USER_ID(), --CREATED_BY
LABOUR_COST,
MATERIAL_COST,
TOTAL_DEDUCTIONS,
DISCOUNT_AMOUNT,
CIS_TAX
from igi_cis_mth_ret_pay_t
where HEADER_ID = p_header_id;
delete from igi_cis_mth_ret_hdr_t where header_id = p_header_id;
delete from igi_cis_mth_ret_lines_t where header_id = p_header_id;
delete from igi_cis_mth_ret_pay_t where header_id = p_header_id;
/* PROCEDURE POST_REPORT_DELETE(p_request_id in number,
p_header_id in number)
is
l_phase VARCHAR2(100);
delete from igi_cis_mth_ret_hdr_t where header_id = p_header_id;
delete from igi_cis_mth_ret_lines_t where header_id = p_header_id;
delete from igi_cis_mth_ret_pay_t where header_id = p_header_id;
End POST_REPORT_DELETE;*/