[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Invoice_id , Invoice_num, Invoice_payment_id, Amount, Pmt_vch_number, Pmt_vch_amount,
Pmt_vch_description, Check_number, Check_date, Vendor_id, Vendor_name, Vendor_site_id,
Vendor_site_code, Group_id, Certificate_type, Certificate_number, Certificate_description,
Ni_number
FROM igi_cis_ci36_payments;
SELECT icatr1.certificate_type , icatr1.certificate_number ,
Substr(icatr1.comments,1,50) "comments", icatr1.ni_number
FROM igi_cis_awt_tax_rates icatr1
WHERE icatr1.vendor_id = P_vendor_id
AND icatr1.vendor_site_id = P_vendor_site_id
AND icatr1.tax_name = fnd_profile.value ('IGI_CIS_TAX_CODE')
AND TRUNC(NVL(P_check_date,icatr1.start_date))
BETWEEN TRUNC(icatr1.start_date)
AND nvl(TRUNC(icatr1.end_date),to_date('9999/12/31','YYYY/MM/DD'))
AND NVL(icatr1.priority, '-1') =
(SELECT NVL(MIN(icatr2.priority), '-1')
FROM igi_cis_awt_tax_rates icatr2
WHERE icatr2.vendor_id = P_vendor_id
AND icatr2.vendor_site_id = P_vendor_site_id
AND icatr2.tax_name = fnd_profile.value ('IGI_CIS_TAX_CODE')
AND TRUNC(NVL(P_check_date,icatr2.start_date))
BETWEEN TRUNC(icatr2.start_date)
AND nvl(TRUNC(icatr2.end_date),to_date('9999/12/31','YYYY/MM/DD')));
CURSOR cur_insert_into_extract (p_invoice_id number)
IS
SELECT
certificate_type,
vendor_name,
vendor_site_code,
certificate_number,
certificate_description,
ni_number,
pmt_vch_number,
pmt_vch_description,
SUM (DECODE (awt_group_id,icip.group_id,0,
NVL (DECODE (line_type_lookup_code, 'ITEM', aid.amount, 0), 0)))
material_amount,
SUM (NVL (DECODE (awt_group_id, icip.group_id, aid.amount,0),0)) labor_amount,
SUM (NVL (DECODE (line_type_lookup_code,'AWT',-aid.amount,0),0)) cis_amount,
(NVL (icip.Amount,0) - SUM (NVL (DECODE (line_type_lookup_code, 'TAX', aid.amount, 0), 0))) net_amount
FROM
ap_invoice_distributions aid,
igi_cis_ci36_payments icip
WHERE
aid.invoice_id = icip.invoice_id
AND aid.invoice_id = p_invoice_id
GROUP BY
certificate_type,
vendor_name,
vendor_site_code,
certificate_number,
certificate_description,
ni_number,
pmt_vch_number,
pmt_vch_description,
icip.Amount;
SELECT
ai.invoice_id,
ai.invoice_num,
icip.segment1,
icip.check_number,
icip.check_date,
icip.amount,
icip.address_line1,
icip.address_line2,
icip.address_line3,
icip.zip,
icip.pmt_vch_received_date
FROM
ap_invoices ai, igi_cis_ci36_payments icip
WHERE
ai.invoice_id = icip.invoice_id;
select count(*) from igi_cis_ci36_extract;
select count(*) from igi_cis_ci36_payments;
Debug(l_state_level, 'Extract_data',' No. of records selected for deletion' ||
' from igi_cis_ci36_extract : ' || l_count1);
Debug(l_state_level, 'Extract_data',' No. of records selected for deletion' ||
' from igi_cis_ci36_payments : ' || l_count2);
DELETE FROM igi_cis_ci36_extract;
DELETE FROM igi_cis_ci36_payments;
Debug(l_state_level, 'Extract_data',' all rows deleted ');
Debug(l_state_level, 'Extract_data',' Inserting records into igi_cis_ci36_payments Table ');
INSERT INTO igi_cis_ci36_payments (
INVOICE_PAYMENT_ID,
INVOICE_ID,
PMT_VCH_NUMBER,
PMT_VCH_AMOUNT,
PMT_VCH_DESCRIPTION,
AMOUNT,
PAYMENT_NUM,
PMT_VCH_RECEIVED_DATE,
INVOICE_NUM,
VENDOR_ID,
VENDOR_SITE_ID,
CHECK_NUMBER,
CHECK_DATE,
GROUP_ID
)
SELECT
icip.invoice_payment_id,
icip.invoice_id,
icip.pmt_vch_number,
icip.pmt_vch_amount,
icip.pmt_vch_description,
icip.amount,
icip.payment_num,
icip.pmt_vch_received_date,
ai.invoice_num,
ai.vendor_id,
ai.vendor_site_id,
ac.check_number,
ac.check_date,
aag.group_id
FROM
igi_cis_invoice_payments icip,
ap_invoices ai,
ap_awt_groups aag,
ap_checks ac
WHERE ai.payment_status_flag = 'Y'
AND ai.invoice_id = icip.invoice_id
AND ai.awt_group_id = aag.group_id
AND aag.name = igi_cis_get_profile.cis_tax_group
AND icip.check_id = ac.check_id
AND ac.void_date IS NULL
AND ai.vendor_id = nvl(x_vendor_id,ai.vendor_id)
AND TRUNC(NVL(ac.check_date,x_low_date)) BETWEEN TRUNC(x_low_date) AND TRUNC(x_high_date);
Debug(l_state_level, 'Extract_data',' No. of records inserted : ' || l_count1);
DELETE FROM igi_cis_ci36_payments a
WHERE EXISTS (SELECT 'x' FROM po_vendors b
WHERE b.vendor_id = a.vendor_id
AND nvl(b.enabled_flag,'N') <> 'Y');
Debug(l_state_level, 'Extract_data',' No. of records deleted : '
|| (nvl(l_count1,0) - nvl(l_count2,0)));
UPDATE igi_cis_ci36_payments a
SET (a.vendor_name,a.segment1) =
(SELECT vendor_name,segment1 FROM po_vendors
WHERE vendor_id = a.vendor_id);
DELETE FROM igi_cis_ci36_payments a
WHERE EXISTS (SELECT 'x' FROM po_vendor_sites b
WHERE b.vendor_site_id = a.vendor_site_id
AND ( nvl(b.allow_awt_flag,'N') <> 'Y'
OR b.awt_group_id <> a.group_id));
Debug(l_state_level, 'Extract_data',' No. of records deleted : '
|| (nvl(l_count1,0) - nvl(l_count2,0)));
UPDATE igi_cis_ci36_payments a
SET (a.vendor_site_code,a.address_line1,A.address_line2,A.address_line3,a.zip ) =
(SELECT vendor_site_code,address_line1,address_line2,address_line3,zip
FROM po_vendor_sites b
WHERE b.vendor_site_id = a.vendor_site_id );
UPDATE igi_cis_ci36_payments
SET certificate_type = l_certificate_type,
certificate_number = l_certificate_number,
certificate_description = l_certificate_description,
ni_number = l_ni_number
WHERE vendor_id = cur_cis_payments.vendor_id
AND vendor_site_id = cur_cis_payments.vendor_site_id
AND invoice_id = cur_cis_payments.invoice_id
AND invoice_payment_id = cur_cis_payments.invoice_payment_id
AND check_number = cur_cis_payments.check_number;
Debug(l_state_level, 'Extract_data',' Inserting certificate details in igi_cis_ci36_extract table ');
FOR I IN cur_insert_into_extract (J.invoice_id)
LOOP
INSERT INTO igi_cis_ci36_extract (
invoice_num,
Segment1,
check_number,
check_date,
amount,
address_line1,
address_line2,
address_line3,
zip,
pmt_vch_received_date,
certificate_type,
vendor_name,
vendor_site_code,
certificate_number,
certificate_description,
ni_number,
pmt_vch_number,
pmt_vch_description,
material_amount,
labor_amount,
cis_amount,
net_amount)
VALUES
(J.invoice_num,
J.Segment1,
J.check_number,
J.check_date,
J.amount,
J.address_line1,
J.address_line2,
J.address_line3,
J.zip,
J.pmt_vch_received_date,
I.certificate_type,
I.vendor_name,
I.vendor_site_code,
I.certificate_number,
I.certificate_description,
I.ni_number,
I.pmt_vch_number,
I.pmt_vch_description,
I.material_amount,
I.labor_amount,
I.cis_amount,
I.net_amount);
Debug(l_state_level, 'Extract_data',' No. of records Inserted into igi_cis_ci36_extract table : ' || l_count1);